ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 163F8ABCDD114BCB88F1081025833F81Y select haouv.name operating_unit, mp.organization_code, hp.party_name charging_party, pcfv.name charge_type, ish.ship_num shipment_number, ish.ship_date shipment_date, xxen_util.meaning(ish.ship_status_code,'INL_SHIP_STATUSES',0) shipment_status, rsh.receipt_num, (select min(rt.transaction_date) from rcv_transactions rt where rsl.shipment_line_id=rt.shipment_line_id and rt.transaction_type='RECEIVE') receipt_date, trunc(:as_of_date)-trunc(ish.ship_date) age_days, case when trunc(:as_of_date)-trunc(ish.ship_date)<=30 then '0-30' when trunc(:as_of_date)-trunc(ish.ship_date)<=60 then '31-60' when trunc(:as_of_date)-trunc(ish.ship_date)<=90 then '61-90' when trunc(:as_of_date)-trunc(ish.ship_date)<=180 then '91-180' else '181+' end aging_bucket, isl.ship_line_num shipment_line, aps.vendor_name supplier, pha.segment1 po_number, pla.line_num po_line, msiv.concatenated_segments item, msiv.description item_description, isl.primary_qty quantity, isl.primary_uom_code uom, gl.currency_code, x.current_amt estimated_amount, x.estimated_amt original_estimated_amount, xxen_util.yes(isl.closed_for_matching_flag) closed_for_matching, xxen_util.yes(ish.pending_matching_flag) shipment_pending_matching from ( select ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id) ship_line_id, icl.charge_line_type_id, 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, max(icl.party_id) keep (dense_rank last order by ia.adjustment_num) party_id from inl_ship_headers_all ish, inl_allocations ia, inl_ship_lines_all isl, inl_charge_lines icl 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.from_parent_table_name='INL_CHARGE_LINES' and ia.ship_line_id=isl.ship_line_id and ia.from_parent_table_id=icl.charge_line_id group by ia.ship_header_id, nvl(isl.parent_ship_line_id,isl.ship_line_id), icl.charge_line_type_id ) x, 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, pon_cost_factors_vl pcfv, hz_parties hp where 3=3 and x.current_amt<>0 and (x.ship_line_id,x.charge_line_type_id) not in ( select im.to_parent_table_id, im.charge_line_type_id from inl_matches im where im.ship_header_id=x.ship_header_id and im.match_type_code='CHARGE' and im.to_parent_table_name='INL_SHIP_LINES' ) and (nvl(isl.closed_for_matching_flag,'N')='N' or :include_closed_for_matching='Y') and x.ship_header_id=ish.ship_header_id and x.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(+) and x.charge_line_type_id=pcfv.price_element_type_id(+) and x.party_id=hp.party_id(+) order by haouv.name, mp.organization_code, hp.party_name, pcfv.name, ish.ship_date, ish.ship_num, isl.ship_line_num Initial version US INL Pending Actual Charges Estimated landed cost charges per shipment line, charge type and charging party that no AP invoice has been matched to yet, aged by shipment date at the As of Date. It lists the freight, duty and brokerage invoices still to be received and the accrual exposure they carry. Estimated Amount is the current estimate in functional currency, including estimate changes made after the shipment was submitted, and Original Estimated Amount the first calculation. A charge type stops being pending on a receipt line once any invoice of that type is matched to it, whatever the amount. Shipment lines closed for matching are excluded unless Include Closed for Matching is set. Enginatics R12 only 1=1 2=2 3=3 :as_of_date :include_closed_for_matching 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 :as_of_date Date sysdate Y US As of Date Date the age of the pending charges is calculated at. 4 40 3=3 trunc(:as_of_date)-trunc(ish.ship_date)>=:minimum_age_days Number US Minimum Age Days Show only charges of shipments at least this many days old. 5 50 1=1 ish.ship_date>=:shipment_date_from Date US Shipment Date From Landed cost shipment date from. 6 60 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. 7 70 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. 8 80 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. 9 90 3=3 pcfv.name=:charge_type LOV custom Y select pcfv.name value, pcfv.price_element_code description from pon_cost_factors_vl pcfv where pcfv.price_element_type_id in (select icl.charge_line_type_id from inl_charge_lines icl) order by pcfv.name US Charge Type Landed cost charge type (Purchasing cost factor). 10 100 3=3 hp.party_name=:charging_party LOV custom Y select distinct hp.party_name value, hp.party_number description from hz_parties hp where hp.party_id in (select icl.party_id from inl_charge_lines icl) order by hp.party_name US Charging Party Third party charging the cost, such as the carrier or customs broker. 11 110 :include_closed_for_matching LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Include Closed for Matching Include shipment lines closed for matching.