ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN BF97A1149A684DA09F8206B4A6353247Y select y.*, nvl(y.projected_duty,0)+nvl(y.additional_tariff,0) total_projected_duty, y.open_value+nvl(y.projected_duty,0)+nvl(y.additional_tariff,0)+nvl(y.simulated_other_charges,0) projected_landed_cost from ( select x.*, coalesce(x.simulated_duty,x.open_value*x.historical_duty_rate_percent/100) projected_duty, x.open_value*:additional_tariff_percent/100 additional_tariff from ( select haouv.name operating_unit, mp.organization_code, aps.vendor_name supplier, pha.segment1 po_number, pla.line_num po_line, plla.shipment_num po_shipment, nvl(plla.promised_date,plla.need_by_date) promised_date, msiv.concatenated_segments item, msiv.description item_description, ftv.territory_short_name country_of_origin, ( select max(mck.concatenated_segments) from mtl_category_sets_vl mcsv, mtl_item_categories mic, mtl_categories_kfv mck where mcsv.category_set_name=:tariff_code_category_set and mcsv.category_set_id=mic.category_set_id and pla.item_id=mic.inventory_item_id and plla.ship_to_organization_id=mic.organization_id and mic.category_id=mck.category_id ) tariff_code, plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0) open_quantity, pla.unit_meas_lookup_code uom, pha.currency_code po_currency, plla.price_override unit_price, gl.currency_code, (plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0))*plla.price_override*nvl(pha.rate,1) open_value, s.simulation_date, s.duty_per_unit*(plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0)) simulated_duty, s.other_charges_per_unit*(plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0)) simulated_other_charges, round(100*s.duty_per_unit/nullif(s.item_value_per_unit,0),2) simulated_duty_rate_percent, round(100*h.duty/nullif(h.item_value,0),2) historical_duty_rate_percent, h.shipment_count historical_shipments from po_headers_all pha, po_lines_all pla, po_line_locations_all plla, hr_all_organization_units_vl haouv, mtl_parameters mp, org_organization_definitions ood, gl_ledgers gl, mtl_system_items_vl msiv, ap_suppliers aps, fnd_territories_vl ftv, ( select isl.ship_line_source_id line_location_id, max(isim.creation_date) simulation_date, sum(case when ia.from_parent_table_name='INL_SHIP_LINES' then ia.allocation_amt end)/nullif(max(isl.primary_qty),0) item_value_per_unit, sum(case when ia.from_parent_table_name='INL_CHARGE_LINES' and 4=4 then ia.allocation_amt end)/nullif(max(isl.primary_qty),0) duty_per_unit, nvl(sum(case when ia.from_parent_table_name<>'INL_SHIP_LINES' then ia.allocation_amt end),0)/nullif(max(isl.primary_qty),0)-nvl(sum(case when ia.from_parent_table_name='INL_CHARGE_LINES' and 4=4 then ia.allocation_amt end),0)/nullif(max(isl.primary_qty),0) other_charges_per_unit from ( select isim.*, max(isim.version_num) over (partition by isim.parent_table_name, isim.parent_table_id) max_version_num from inl_simulations isim where isim.parent_table_name='PO_HEADERS' ) isim, inl_ship_headers_all ish, inl_ship_lines_all isl, inl_allocations ia, inl_charge_lines icl, pon_cost_factors_vl pcfv where isim.version_num=isim.max_version_num and isim.simulation_id=ish.simulation_id and ish.ship_header_id=isl.ship_header_id and isl.ship_line_src_type_code='PO' and isl.parent_ship_line_id is null and isl.ship_header_id=ia.ship_header_id and isl.ship_line_id=ia.ship_line_id and ia.adjustment_num=ish.adjustment_num and ia.landed_cost_flag='Y' and decode(ia.from_parent_table_name,'INL_CHARGE_LINES',ia.from_parent_table_id)=icl.charge_line_id(+) and icl.charge_line_type_id=pcfv.price_element_type_id(+) group by isl.ship_line_source_id ) s, ( select plla.line_location_id, y.shipment_count, y.item_value, y.duty from po_line_locations_all plla, po_lines_all pla, ( select ish.organization_id, isl.inventory_item_id, nvl(rsl.country_of_origin_code,plla.country_of_origin_code) country_of_origin_code, count(distinct ish.ship_header_id) shipment_count, sum(case when ia.from_parent_table_name='INL_SHIP_LINES' then ia.allocation_amt end) item_value, sum(case when ia.from_parent_table_name='INL_CHARGE_LINES' and 4=4 then ia.allocation_amt end) duty from inl_ship_headers_all ish, inl_allocations ia, inl_ship_lines_all isl, inl_ship_lines_all isl0, po_line_locations_all plla, rcv_shipment_lines rsl, inl_charge_lines icl, pon_cost_factors_vl pcfv where ish.simulation_id is null and ish.ship_date>=:history_date_from and ish.ship_header_id=ia.ship_header_id and ia.adjustment_num=ish.adjustment_num and ia.landed_cost_flag='Y' and ia.ship_line_id=isl.ship_line_id and nvl(isl.parent_ship_line_id,isl.ship_line_id)=isl0.ship_line_id and decode(isl0.ship_line_src_type_code,'PO',isl0.ship_line_source_id)=plla.line_location_id(+) and isl0.ship_line_id=rsl.lcm_shipment_line_id(+) and decode(ia.from_parent_table_name,'INL_CHARGE_LINES',ia.from_parent_table_id)=icl.charge_line_id(+) and icl.charge_line_type_id=pcfv.price_element_type_id(+) group by ish.organization_id, isl.inventory_item_id, nvl(rsl.country_of_origin_code,plla.country_of_origin_code) ) y where plla.lcm_flag='Y' and plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0)>0 and plla.po_line_id=pla.po_line_id and plla.ship_to_organization_id=y.organization_id and pla.item_id=y.inventory_item_id and plla.country_of_origin_code=y.country_of_origin_code ) h where 1=1 and plla.lcm_flag='Y' and plla.shipment_type in ('STANDARD','BLANKET','SCHEDULED') and pha.authorization_status='APPROVED' and nvl(plla.cancel_flag,'N')='N' and nvl(plla.closed_code,'OPEN') in ('OPEN','CLOSED FOR INVOICE') and plla.quantity-nvl(plla.quantity_received,0)-nvl(plla.quantity_cancelled,0)>0 and plla.ship_to_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 pha.po_header_id=pla.po_header_id and pla.po_line_id=plla.po_line_id and pha.org_id=haouv.organization_id and plla.ship_to_organization_id=mp.organization_id and plla.ship_to_organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and pla.item_id=msiv.inventory_item_id and plla.ship_to_organization_id=msiv.organization_id and pha.vendor_id=aps.vendor_id and plla.country_of_origin_code=ftv.territory_code(+) and plla.line_location_id=s.line_location_id(+) and plla.line_location_id=h.line_location_id(+) ) x ) y where 5=5 order by y.operating_unit, y.organization_code, y.supplier, y.po_number, y.po_line, y.po_shipment Initial version US INL Tariff Exposure on Open POs Open quantity of approved PO shipments enabled for Landed Cost Management, with simulated and historical duty, to project the tariff cost of goods not received yet. Simulated Duty comes from the purchase order's latest landed cost simulation, scaled to the open quantity. Historical Duty Rate is the duty to item value ratio of the shipments of the same item, organization and country of origin since History Date From. Projected Duty is the simulated duty, else the open value at the historical rate. Additional Tariff Percent applies a what-if tariff to the open value, for example a newly announced rate for one country of origin. Amounts are in functional currency at the PO exchange rate. Enginatics R12 only 1=1 4=4 5=5 :additional_tariff_percent :history_date_from :tariff_code_category_set 1 10 1=1 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 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 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 purchase order. 4 40 1=1 pha.segment1=:po_number LOV custom Y select pha.segment1 value, aps.vendor_name description from po_headers_all pha, ap_suppliers aps where pha.po_header_id in (select plla.po_header_id from po_line_locations_all plla where plla.lcm_flag='Y') and pha.vendor_id=aps.vendor_id order by pha.segment1 US PO Number Purchase order with landed cost enabled shipments. 5 50 1=1 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 ordered. 6 60 1=1 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 on the PO shipment. 7 70 4=4 pcfv.name=:duty_charge_type LOV custom Y select pcfv.name value, pcfv.price_element_code description from pon_cost_factors_vl pcfv order by pcfv.name select pcfv.name from pon_cost_factors_vl pcfv where upper(pcfv.price_element_code) in ('DUTY','DUTIES','TARIFF','CUSTOMS_DUTY','CUSTOMS DUTY') Y US Duty Charge Type Charge types counted as duty or tariff. Select several with the multiple values option. 8 80 :tariff_code_category_set LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name select mcsv.category_set_name from mtl_category_sets_vl mcsv where regexp_like(mcsv.category_set_name,'tariff|hts|harmoni','i') US Tariff Code Category Set Item category set holding the HTS or tariff codes of the items. 9 90 5=5 y.tariff_code=:tariff_code LOV custom Y select mcv.category_concat_segs value, mcv.description from mtl_category_sets_vl mcsv, mtl_categories_v mcv where mcsv.category_set_name=:$flex$.tariff_code_category_set and mcsv.structure_id=mcv.structure_id order by mcv.category_concat_segs US Tariff Code HTS or tariff code, from the Tariff Code Category Set. 10 100 :history_date_from Date add_months(trunc(sysdate),-12) Y US History Date From Start date of the landed cost shipments the historical duty rate is calculated from. 11 110 :additional_tariff_percent Number US Additional Tariff Percent What-if tariff rate applied to the open value in addition to the projected duty.