ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5FADCA87364B4F428FA86F5CA364636EY select haouv.name operating_unit, mp.organization_code, clat.transaction_date, msiv.concatenated_segments item, msiv.description item_description, ish.ship_num shipment_number, isl.ship_line_num shipment_line, pha.segment1 po_number, pla.line_num po_line, rsh.receipt_num, rt.primary_quantity receipt_quantity, rt.primary_unit_of_measure uom, gl.currency_code, clat.prior_landed_cost prior_unit_landed_cost, clat.new_landed_cost new_unit_landed_cost, clat.new_landed_cost-clat.prior_landed_cost unit_landed_cost_change, decode(row_number() over (partition by clat.transaction_id order by rae.accounting_event_id, rrsl.rcv_sub_ledger_id),1,(clat.new_landed_cost-clat.prior_landed_cost)*rt.primary_quantity) adjustment_value, (select xetv.name from rcv_accounting_event_types raet, xla_event_types_vl xetv where rae.event_type_id=raet.event_type_id and raet.event_type_name=xetv.event_type_code and xetv.application_id=707) event_type, rae.primary_quantity event_quantity, rrsl.accounting_date, rrsl.period_name, rrsl.accounting_line_type, gcck.concatenated_segments account, xxen_util.segments_description(gcck.code_combination_id) account_description, rrsl.accounted_dr, rrsl.accounted_cr, nvl(rrsl.accounted_dr,0)-nvl(rrsl.accounted_cr,0) accounted_net, clat.transaction_id from cst_lc_adj_transactions clat, rcv_transactions rt, rcv_shipment_headers rsh, inl_ship_lines_all isl, inl_ship_headers_all ish, po_lines_all pla, po_headers_all pha, hr_all_organization_units_vl haouv, mtl_parameters mp, org_organization_definitions ood, gl_ledgers gl, mtl_system_items_vl msiv, rcv_accounting_events rae, rcv_receiving_sub_ledger rrsl, gl_code_combinations_kfv gcck where 1=1 and clat.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 clat.rcv_transaction_id=rt.transaction_id and rt.shipment_header_id=rsh.shipment_header_id and rt.lcm_shipment_line_id=isl.ship_line_id(+) and isl.ship_header_id=ish.ship_header_id(+) and rt.po_line_id=pla.po_line_id(+) and rt.po_header_id=pha.po_header_id(+) and ood.operating_unit=haouv.organization_id and clat.organization_id=mp.organization_id and clat.organization_id=ood.organization_id and ood.set_of_books_id=gl.ledger_id and clat.inventory_item_id=msiv.inventory_item_id and clat.organization_id=msiv.organization_id and clat.transaction_id=rae.event_source_id(+) and rae.event_source(+)='LC_ADJUSTMENTS' and rae.accounting_event_id=rrsl.accounting_event_id(+) and rrsl.code_combination_id=gcck.code_combination_id(+) order by haouv.name, mp.organization_code, clat.transaction_date, clat.transaction_id, rae.accounting_event_id, rrsl.rcv_sub_ledger_id Initial version US INL Landed Cost Adjustments Landed cost adjustments processed by Cost Management, with the receiving accounting they generated. One row per accounting line of each adjustment: the adjustment of the receipt (Receiving Inspection against Landed Cost Absorption) and, for delivered quantities, the adjustment of the delivery (Purchase Price Variance in a standard cost organization, otherwise the inventory or expense account). Adjustment Value, the unit landed cost change times the receipt quantity, is shown on the first row of each adjustment only, so that it can be summed. Adjustments still pending or in error in the interface are listed by CAC Interface Error Summary. Enginatics R12 only 1=1 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 clat.transaction_date>=:transaction_date_from Date US Transaction Date From Landed cost adjustment date from. 4 40 1=1 clat.transaction_date<:transaction_date_to+1 Date last_day(:$flex$.transaction_date_from) US Transaction Date To Landed cost adjustment date to. 5 50 1=1 ish.ship_num=:shipment_number LOV custom Y select ish.ship_num value, mp.organization_code||', '||fnd_date.date_to_displaydate(ish.ship_date) description from inl_ship_headers_all ish, mtl_parameters mp where ish.simulation_id is null and ish.organization_id=mp.organization_id and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y') 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) order by ish.ship_date desc, ish.ship_num US Shipment Number Landed cost shipment number. 6 60 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 of the receipt. 7 70 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 adjusted.