ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 63675B183CE24A299AF2289D405FAF41Y select y.*, y.unit_landed_cost-y.previous_unit_landed_cost unit_landed_cost_change, round(100*(y.unit_landed_cost-y.previous_unit_landed_cost)/nullif(y.previous_unit_landed_cost,0),2) unit_landed_cost_change_pct from ( select haouv.name operating_unit, mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, ish.ship_date shipment_date, ish.ship_num shipment_number, isl.ship_line_num shipment_line, aps.vendor_name supplier, (select ftv.territory_short_name from fnd_territories_vl ftv where nvl(rsl.country_of_origin_code,plla.country_of_origin_code)=ftv.territory_code) country_of_origin, pha.segment1 po_number, pla.line_num po_line, isl.primary_qty quantity, isl.primary_uom_code uom, pha.currency_code po_currency, plla.price_override po_unit_price, gl.currency_code, z.item_current/nullif(isl.primary_qty,0) unit_item_cost, z.charges_current/nullif(isl.primary_qty,0) unit_charges, z.taxes_current/nullif(isl.primary_qty,0) unit_taxes, (z.item_estimated+z.charges_estimated+z.taxes_estimated)/nullif(isl.primary_qty,0) estimated_unit_landed_cost, (z.item_current+z.charges_current+z.taxes_current)/nullif(isl.primary_qty,0) unit_landed_cost, lag((z.item_current+z.charges_current+z.taxes_current)/nullif(isl.primary_qty,0)) over (partition by ish.organization_id, isl.inventory_item_id order by ish.ship_date, ish.ship_num, isl.ship_line_num) previous_unit_landed_cost, round(100*(z.charges_current+z.taxes_current)/nullif(z.item_current,0),2) landed_cost_uplift_percent, z.item_current+z.charges_current+z.taxes_current landed_cost from ( select ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id) ship_line_id, sum(case when ia.adjustment_num=0 and ia.from_parent_table_name='INL_SHIP_LINES' then ia.allocation_amt else 0 end) item_estimated, sum(case when ia.adjustment_num=0 and ia.from_parent_table_name='INL_CHARGE_LINES' then ia.allocation_amt else 0 end) charges_estimated, sum(case when ia.adjustment_num=0 and ia.from_parent_table_name='INL_TAX_LINES' then ia.allocation_amt else 0 end) taxes_estimated, sum(case when ia.adjustment_num=ish.adjustment_num and ia.from_parent_table_name='INL_SHIP_LINES' then ia.allocation_amt else 0 end) item_current, sum(case when ia.adjustment_num=ish.adjustment_num and ia.from_parent_table_name='INL_CHARGE_LINES' then ia.allocation_amt else 0 end) charges_current, sum(case when ia.adjustment_num=ish.adjustment_num and ia.from_parent_table_name='INL_TAX_LINES' then ia.allocation_amt else 0 end) taxes_current from inl_ship_headers_all ish, inl_allocations ia, inl_ship_lines_all isl where 1=1 and 2=2 and ish.simulation_id is null and ish.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and ish.ship_header_id=ia.ship_header_id and ia.adjustment_num in (0,ish.adjustment_num) and ia.landed_cost_flag='Y' and ia.ship_line_id=isl.ship_line_id group by ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id) ) z, inl_ship_headers_all ish, inl_ship_lines_all isl, inl_ship_line_groups islg, hr_all_organization_units_vl haouv, mtl_parameters mp, org_organization_definitions ood, gl_ledgers gl, mtl_system_items_vl msiv, ap_suppliers aps, po_line_locations_all plla, po_lines_all pla, po_headers_all pha, rcv_shipment_lines rsl where 3=3 and z.ship_header_id=ish.ship_header_id and z.ship_line_id=isl.ship_line_id and isl.ship_line_group_id=islg.ship_line_group_id and ish.org_id=haouv.organization_id and ish.organization_id=mp.organization_id and ish.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and isl.inventory_item_id=msiv.inventory_item_id and ish.organization_id=msiv.organization_id and islg.party_id=aps.party_id(+) and decode(isl.ship_line_src_type_code,'PO',isl.ship_line_source_id)=plla.line_location_id(+) and plla.po_line_id=pla.po_line_id(+) and plla.po_header_id=pha.po_header_id(+) and isl.ship_line_id=rsl.lcm_shipment_line_id(+) ) y order by y.operating_unit, y.organization_code, y.item, y.shipment_date, y.shipment_number, y.shipment_line Initial version US INL Landed Unit Cost Trend Unit landed cost per item over its landed cost shipments, broken down into item cost, charges and taxes, with the change against the item's previous shipment in the same organization. Unit amounts are the current landed cost allocations in functional currency divided by the shipment line's primary quantity, Estimated Unit Landed Cost the first calculation. The previous shipment is taken from the shipments within the parameter selection. Enginatics R12 only 1=1 2=2 3=3 1 10 1=1 ish.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit) LOV HR Operating Unit 8E2FF36EDEB979D2E0530100007F1FF2 Y select hou.name value, null description from hr_operating_units hou where sysdate between hou.date_from and nvl(hou.date_to,sysdate) and (:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hou.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') order by hou.name US Operating Unit Operating unit of the landed cost shipment. 2 20 1=1 ish.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code) LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') US Organization Code Inventory organization receiving the goods. 3 30 1=1 ish.ship_date>=:shipment_date_from Date US Shipment Date From Landed cost shipment date from. 4 40 1=1 ish.ship_date<:shipment_date_to+1 Date last_day(:$flex$.shipment_date_from) US Shipment Date To Landed cost shipment date to. 5 50 2=2 isl.ship_line_group_id in (select islg.ship_line_group_id from inl_ship_line_groups islg, ap_suppliers aps where islg.party_id=aps.party_id and aps.vendor_name=:supplier) LOV AP Supplier B9847D20A0E4742FE0538931640A6379 Y Y select aps.vendor_name value, aps.segment1 description from ap_suppliers aps where (:$flex$.operating_unit is null or aps.vendor_id in (select assa.vendor_id from hr_all_organization_units_vl haouv, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=assa.org_id)) and (:$flex$.organization_code is null or aps.vendor_id in (select assa.vendor_id from org_organization_definitions ood, ap_supplier_sites_all assa where xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y' and ood.operating_unit=assa.org_id)) order by aps.vendor_name, aps.vendor_id US Supplier Supplier of the goods. 6 60 2=2 isl.inventory_item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv where ish.organization_id=msiv.organization_id and msiv.concatenated_segments=:item) LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item Item received on the shipment line. 7 70 3=3 nvl(rsl.country_of_origin_code,plla.country_of_origin_code) in (select ftv.territory_code from fnd_territories_vl ftv where ftv.territory_short_name=:country_of_origin) LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 Y select ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name US Country of Origin Country of origin of the goods, from the receipt line, else the PO shipment.