ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 402F45E8B4D54249BEC7BC0BD28F5C69Y select y.* from ( select haouv.name operating_unit, mp.organization_code, ish.ship_num shipment_number, ish.ship_date shipment_date, 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, ( 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 isl.inventory_item_id=mic.inventory_item_id and ish.organization_id=mic.organization_id and mic.category_id=mck.category_id ) tariff_code, pha.segment1 po_number, pla.line_num po_line, rsh.receipt_num, msiv.concatenated_segments item, msiv.description item_description, isl.primary_qty quantity, isl.primary_uom_code uom, gl.currency_code, z.item_value, z.duty_estimated, z.duty, z.duty-z.duty_estimated duty_variance, round(100*z.duty/nullif(z.item_value,0),2) duty_rate_percent, z.duty/nullif(isl.primary_qty,0) duty_per_unit, xxen_util.yes(z.duty_matched) duty_matched, z.other_charges, z.taxes, z.item_value+z.duty+z.other_charges+z.taxes landed_cost, (z.item_value+z.duty+z.other_charges+z.taxes)/nullif(isl.primary_qty,0) unit_landed_cost, round(100*(z.duty+z.other_charges+z.taxes)/nullif(z.item_value,0),2) landed_cost_uplift_percent from ( select x.ship_header_id, x.ship_line_id, sum(decode(x.component_type,'ITEM',x.current_amt,0)) item_value, sum(case when x.component_type='CHARGE' and 4=4 then x.estimated_amt else 0 end) duty_estimated, sum(case when x.component_type='CHARGE' and 4=4 then x.current_amt else 0 end) duty, max(case when x.component_type='CHARGE' and 4=4 then x.matched end) duty_matched, sum(decode(x.component_type,'CHARGE',x.current_amt,0))-sum(case when x.component_type='CHARGE' and 4=4 then x.current_amt else 0 end) other_charges, sum(decode(x.component_type,'TAX',x.current_amt,0)) taxes from ( select w.*, case when w.charge_line_type_id in ( select im.charge_line_type_id from inl_matches im where w.ship_header_id=im.ship_header_id and w.ship_line_id=im.to_parent_table_id and im.match_type_code='CHARGE' and im.to_parent_table_name='INL_SHIP_LINES' ) then 'Y' end matched from ( select ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id) ship_line_id, decode(ia.from_parent_table_name,'INL_SHIP_LINES','ITEM','INL_CHARGE_LINES','CHARGE','INL_TAX_LINES','TAX') component_type, icl.charge_line_type_id, pcfv.name charge_type, sum(decode(ia.adjustment_num,0,ia.allocation_amt,0)) estimated_amt, sum(decode(ia.adjustment_num,ish.adjustment_num,ia.allocation_amt,0)) current_amt from inl_ship_headers_all ish, inl_allocations ia, inl_ship_lines_all isl, inl_charge_lines icl, pon_cost_factors_vl pcfv 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 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 ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id), ia.from_parent_table_name, icl.charge_line_type_id, pcfv.name ) w ) x group by x.ship_header_id, x.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, rcv_shipment_headers rsh 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(+) and rsl.shipment_header_id=rsh.shipment_header_id(+) ) y where 5=5 order by y.operating_unit, y.organization_code, y.shipment_date, y.shipment_number, y.shipment_line Initial version US INL Duty and Tariff Analysis Duty, other charges and taxes per landed cost shipment line, with tariff code, country of origin and supplier, to analyse duty rates and the landed cost uplift on the item value. The charge types counted as duty are selected in the Duty Charge Type parameter, all other charges are Other Charges. Amounts are the current landed cost allocations in functional currency, Duty Estimated the first calculation. LCM allocates a charge to the shipment lines by the charge type's allocation basis, so an actual duty invoice matched to the shipment is spread over its lines, and the duty rate per line reflects that allocation rather than the customs entry line. Oracle has no standard HTS field: the tariff code is the item's category in the Tariff Code Category Set, an item category set holding the HTS codes. Enginatics R12 only 1=1 2=2 3=3 4=4 5=5 :tariff_code_category_set 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. 8 80 4=4 x.charge_type=: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. 9 90 :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. 10 100 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.