ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 7F9E3C97A9C63299E0530100007F4ED4Y select xxen_util.meaning(pda.destination_type_code,'DESTINATION TYPE',201) destination_type, x.ledger, x.operating_unit, x.organization_code org_code, x.period_name, x.accounting_line_type, xxen_util.meaning(rt.transaction_type,'RCV TRANSACTION TYPE',201) transaction_type, xxen_util.client_time(rt.transaction_date) transaction_date, xxen_util.meaning(rsh.receipt_source_code,'SHIPMENT SOURCE TYPE',201) source, rsh.receipt_num receipt, (select aps.vendor_name from ap_suppliers aps where rt.vendor_id=aps.vendor_id) supplier, pha.segment1 po_number, pla.line_num po_line, plla.shipment_num po_shipment, pda.po_distribution_id, &segment_columns gcck1.concatenated_segments account, gcck2.concatenated_segments charge_account, (select we.wip_entity_name from wip_entities we where pda.wip_entity_id=we.wip_entity_id) wip_job, msiv.concatenated_segments item_number, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type, x.quantity, x.uom, nvl(rsl.packing_slip,rsh.packing_slip) packing_slip, rt.currency_code currency, rt.currency_conversion_rate rate, rt.currency_conversion_date rate_date, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where rt.currency_conversion_type=gdct.conversion_type) rate_type, rt.rma_reference rma_number, (select wlpn.license_plate_number from wms_license_plate_numbers wlpn where rt.lpn_id=wlpn.lpn_id) lpn, (select wlpn.license_plate_number from wms_license_plate_numbers wlpn where rt.transfer_lpn_id=wlpn.lpn_id) transfer_lpn, rt.subinventory, (select max(papf.full_name) keep (dense_rank last order by papf.effective_start_date) from per_all_people_f papf where rt.employee_id=papf.person_id) receiver, rsh.bill_of_lading, rsh.shipment_num shipment, rt.comments, x.gl_date, xxen_util.meaning(x.accounting_class_code,'XLA_ACCOUNTING_CLASS',602) accounting_class, x.accounted_dr, x.accounted_cr, x.amount from ( select y.*, sum(y.amount) over (partition by y.organization_id,y.period_name,y.po_distribution_id,y.code_combination_id) net_amount from ( select mp.organization_code, nvl(gl.short_name,gl.name) ledger, haouv.name operating_unit, z.*, nvl(z.accounted_dr,0)-nvl(z.accounted_cr,0) amount from ( select rt.organization_id, xah.ledger_id, xah.period_name, rrsl.accounting_line_type, rt.transaction_id, to_number(rrsl.reference3) po_distribution_id, xal.code_combination_id, xah.accounting_date gl_date, xal.accounting_class_code, xal.accounted_dr, xal.accounted_cr, rae.transaction_quantity quantity, rae.transaction_unit_of_measure uom from mtl_parameters mp, rcv_parameters rp, rcv_receiving_sub_ledger rrsl, rcv_accounting_events rae, rcv_transactions rt, xla_distribution_links xdl, xla_ae_lines xal, xla_ae_headers xah where 1=1 and mp.organization_id=rp.organization_id and rp.receiving_account_id=rrsl.code_combination_id and rrsl.accounting_line_type<>'Accrual' and rrsl.accounting_event_id=rae.accounting_event_id(+) and rrsl.rcv_transaction_id=rt.transaction_id and rp.organization_id=rt.organization_id and rt.source_document_code not in ('RMA','REQ') and rrsl.rcv_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.source_distribution_type='RCV_RECEIVING_SUB_LEDGER' and xdl.application_id=707 and xdl.ae_header_id=xal.ae_header_id and xdl.ae_line_num=xal.ae_line_num and xal.application_id=707 and rrsl.code_combination_id=xal.code_combination_id and xal.ae_header_id=xah.ae_header_id and xah.application_id=707 union all select wta.organization_id, xah.ledger_id, xah.period_name, xxen_util.meaning(wta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, rt.transaction_id, rt.po_distribution_id, xal.code_combination_id, xah.accounting_date gl_date, xal.accounting_class_code, xal.accounted_dr, xal.accounted_cr, rt.quantity, rt.unit_of_measure uom from mtl_parameters mp, rcv_parameters rp, wip_transaction_accounts wta, wip_transactions wt, rcv_transactions rt, xla_distribution_links xdl, xla_ae_lines xal, xla_ae_headers xah where 2=2 and mp.organization_id=rp.organization_id and rp.organization_id=wta.organization_id and rp.receiving_account_id=wta.reference_account and wta.accounting_line_type in (4,5) and --osp deliveries can carry resource absorption (4) instead of receiving inspection (5) on the receiving account wta.transaction_id=wt.transaction_id and wt.rcv_transaction_id=rt.transaction_id and wta.wip_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.source_distribution_type='WIP_TRANSACTION_ACCOUNTS' and xdl.application_id=707 and xdl.ae_header_id=xal.ae_header_id and xdl.ae_line_num=xal.ae_line_num and xal.application_id=707 and wta.reference_account=xal.code_combination_id and xal.ae_header_id=xah.ae_header_id and xah.application_id=707 union all select mta.organization_id, xah.ledger_id, xah.period_name, xxen_util.meaning(mta.accounting_line_type,'CST_ACCOUNTING_LINE_TYPE',700) accounting_line_type, rt.transaction_id, rt.po_distribution_id, xal.code_combination_id, xah.accounting_date gl_date, xal.accounting_class_code, xal.accounted_dr, xal.accounted_cr, rt.quantity, rt.unit_of_measure uom from mtl_parameters mp, rcv_parameters rp, mtl_transaction_accounts mta, mtl_material_transactions mmt, rcv_transactions rt, xla_distribution_links xdl, xla_ae_lines xal, xla_ae_headers xah where 3=3 and mp.organization_id=rp.organization_id and rp.organization_id=mta.organization_id and rp.receiving_account_id=mta.reference_account and mta.accounting_line_type=5 and mta.transaction_id=mmt.transaction_id and mmt.transaction_source_type_id=1 and mmt.rcv_transaction_id=rt.transaction_id and mta.inv_sub_ledger_id=xdl.source_distribution_id_num_1 and xdl.source_distribution_type='MTL_TRANSACTION_ACCOUNTS' and xdl.application_id=707 and xdl.ae_header_id=xal.ae_header_id and xdl.ae_line_num=xal.ae_line_num and xal.application_id=707 and mta.reference_account=xal.code_combination_id and xal.ae_header_id=xah.ae_header_id and xah.application_id=707 ) z, mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units_vl haouv, gl_ledgers gl where 4=4 and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and z.organization_id=mp.organization_id and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and to_number(hoi.org_information3)=haouv.organization_id and to_number(hoi.org_information1)=gl.ledger_id and z.ledger_id=gl.ledger_id ) y ) x, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pda, po_headers_all pha, po_lines_all pla, po_line_locations_all plla, mtl_system_items_vl msiv, gl_code_combinations_kfv gcck1, gl_code_combinations_kfv gcck2 where 5=5 and x.transaction_id=rt.transaction_id and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and x.po_distribution_id=pda.po_distribution_id and pda.po_header_id=pha.po_header_id and pda.po_line_id=pla.po_line_id and pda.line_location_id=plla.line_location_id and rsl.item_id=msiv.inventory_item_id(+) and rsl.to_organization_id=msiv.organization_id(+) and x.code_combination_id=gcck1.code_combination_id and pda.code_combination_id=gcck2.code_combination_id order by x.organization_code, x.period_name, pha.segment1, pla.line_num, pda.po_distribution_id, rt.transaction_date, rt.transaction_id Organization security from the Blitz Report organization access: inventory organizations, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. The operating unit and ledger lists show those of the accessible inventory organizations. US CAC Receiving Activity Summary Especially for companies who do not use separate inspection receipt processes, this report nets the purchase order receipts into receiving against the deliveries into stock and WIP outside processing (OSP), using the Subledger Accounting entries on the organization's receiving account for the entered transaction date range. Each row is one subledger journal line on the receiving account, with the receiving transaction details shown in the Receiving Transactions form. With Unbalanced Distributions Only set to Yes, only PO distributions whose net amount per period, organization and receiving account is not zero are listed, so receipts and deliveries that offset each other drop out. Quantity and UOM are the receiving transaction's quantity and unit of measure. A receipt or return against several PO distributions shows each distribution's share of the quantity. Differences may be due to the initial receipt occurring in the prior month, not running Create Accounting in the current month, the goods never delivered into stock or OSP, or the delivery not processed by Create Accounting. Template Summary by PO Distribution sums the amount per PO distribution and account. Parameters: ========== Transaction Date From: enter the starting transaction date (mandatory). Defaults to the current period starting date. Transaction Date To: enter the ending transaction date (mandatory). Defaults to the current period ending date. Organization Code: enter the specific inventory organization(s) you wish to report (optional). Defaults to your session's organization code. Operating Unit: enter the specific operating unit(s) you wish to report (optional). Ledger: enter the specific ledger(s) you wish to report (optional). /* +=============================================================================+ -- | Copyright 2010 - 2024 Douglas Volz Consulting, Inc. -- | All rights reserved. -- | Permission to use this code is granted provided the original author is -- | acknowledged. No warranties, express or otherwise is included in this permission. | -- +=============================================================================+ -- | -- | Original Author: Douglas Volz (doug@volzconsulting.com) -- | -- | Program Name: xxx_rcv_activity_rept.sql -- | -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 6 Jan 2010 Douglas Volz Initial Coding -- | 1.1 05 Apr 2010 Douglas Volz Added PO number, PO Distribution id to query -- | 1.2 25 Oct 2017 Douglas Volz Now use the receiving parameter Receiving ccid -- | and added transaction source. -- | 1.3 13 Mar 2018 Douglas Volz Added Ledger parameter -- | 1.4 08 Jul 2022 Douglas Volz Multi-language tables for item master. -- | 1.5 24 Jun 2024 Douglas Volz Remove tabs, reinstall parameters and inventory org access controls. -- | 1.6 16 Oct 2026 Andy Haack One row per subledger journal line with the receiving transaction columns. -- | 1.7 21 Oct 2026 Mrugesh Poojary Quantity and UOM columns. -- +=============================================================================+*/ Enginatics R12 only &segment_columns 1=1 2=2 3=3 4=4 5=5 1 -100 &segment_columns select decode(rowgen.column_value,1,'gcck1.','gcck2.')||lower(fifsv.application_column_name)||' "'||decode(rowgen.column_value,1,'Rcvg ','Chg ')||substr(fifsv.form_left_prompt,1,21)||'",' text from fnd_id_flex_structures_vl fifsv0, fnd_id_flex_segments_vl fifsv, table(xxen_util.rowgen(2)) rowgen where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts and fifsv0.application_id=fifsv.application_id and fifsv0.id_flex_code=fifsv.id_flex_code and fifsv0.id_flex_num=fifsv.id_flex_num and fifsv.enabled_flag='Y' order by rowgen.column_value, fifsv.segment_num LOV GL Chart of Accounts (inv org related) 8E2FF36EDEC879D2E0530100007F1FF2 select fifsv.id_flex_structure_name value, fifsv.id_flex_structure_code description from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) > sysdate) order by fifsv.id_flex_structure_name select fifsv.id_flex_structure_name from fnd_id_flex_structures_vl fifsv where fifsv.id_flex_code='GL#' and fifsv.application_id=101 and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate) and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id = nvl(fnd_profile.value('MFG_ORGANIZATION_ID'),ood.organization_id)) Y US Chart of Accounts 2 10 1=1 rt.transaction_date>=:p_trx_date_from and rrsl.transaction_date>=:p_trx_date_from Date select max(oap.period_start_date) from org_acct_periods oap, mtl_parameters mp where oap.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id Y US Transaction Date From 3 2=2 wta.transaction_date>=:p_trx_date_from and wt.transaction_date>=:p_trx_date_from US Transaction Date From 4 3=3 mta.transaction_date>=:p_trx_date_from and mmt.transaction_date>=:p_trx_date_from US Transaction Date From 5 20 1=1 rt.transaction_date<:p_trx_date_to+1 and rrsl.transaction_date<:p_trx_date_to+1 Date select max(oap.schedule_close_date) from org_acct_periods oap, mtl_parameters mp where oap.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id Y US Transaction Date To 6 2=2 wta.transaction_date<:p_trx_date_to+1 and wt.transaction_date<:p_trx_date_to+1 US Transaction Date To 7 3=3 mta.transaction_date<:p_trx_date_to+1 and mmt.transaction_date<:p_trx_date_to+1 US Transaction Date To 8 25 1=1 mp.organization_code=:p_org_code LOV INV Organization Code (excluding master) 91D3167A33531204E053BB6B63585EE3 select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) 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 ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and nvl(ood.disable_date,sysdate)>=sysdate 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 9 2=2 mp.organization_code=:p_org_code US Organization Code 10 3=3 mp.organization_code=:p_org_code US Organization Code 11 27 4=4 haouv.name=:p_operating_unit LOV INV Operating Unit 9B9627743E84607DE053BB6B635805FB select haouv.name value, 'Operating Unit' description from hr_all_organization_units_vl haouv where haouv.organization_id in ( select to_number(hoi.org_information3) from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where mp.organization_id<>mp.master_organization_id and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') and mp.organization_id=hoi.organization_id and hoi.org_information_context='Accounting Information' and (:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and hoi.organization_id=haou.organization_id and nvl(haou.date_to,sysdate)>=sysdate ) order by 1 US Operating Unit 12 30 4=4 gl.name=:p_ledger LOV GL Ledger (inv org related) 8E2FF36EDF3A79D2E0530100007F1FF2 select gl.name value, fifsv.id_flex_structure_name||': '||gl.description description from gl_ledgers gl, fnd_id_flex_structures_vl fifsv where gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)>=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV')) and (:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and gl.chart_of_accounts_id=fifsv.id_flex_num and fifsv.id_flex_code='GL#' and fifsv.application_id=101 order by fifsv.id_flex_structure_name, gl.name US Ledger 13 35 5=5 round(x.net_amount,3)<>0 LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y Y US Unbalanced Distributions Only Yes lists only PO distributions whose receipts and deliveries do not net to zero on the receiving account. No lists all receiving transactions. 620B4719CB29E7EBAFD6389E756561DB Pivot by Org by PO Pivot by Destination Type, Ledger, Operating Unit, Org Code and Purchase Order. MFG 26 ACCOUNT 50 ACCOUNTED_CR 49 ACCOUNTED_DR 48 ACCOUNTING_CLASS 6 ACCOUNTING_LINE_TYPE 51 AMOUNT 44 BILL_OF_LADING 27 CHARGE_ACCOUNT 46 COMMENTS 35 CURRENCY 23 Chg Account 21 Chg Company 22 Chg Department 25 Chg Product 24 Chg Sub-Account 1 DESTINATION_TYPE 47 GL_DATE 30 ITEM_DESCRIPTION 29 ITEM_NUMBER 31 ITEM_TYPE 2 LEDGER 40 LPN 3 OPERATING_UNIT 4 ORG_CODE 34 PACKING_SLIP 5 PERIOD_NAME 15 PO_DISTRIBUTION_ID 13 PO_LINE 12 PO_NUMBER 14 PO_SHIPMENT 32 QUANTITY 36 RATE 37 RATE_DATE 38 RATE_TYPE 10 RECEIPT 43 RECEIVER 39 RMA_NUMBER 18 Rcvg Account 16 Rcvg Company 17 Rcvg Department 20 Rcvg Product 19 Rcvg Sub-Account 45 SHIPMENT 9 SOURCE 42 SUBINVENTORY 11 SUPPLIER 8 TRANSACTION_DATE 7 TRANSACTION_TYPE 41 TRANSFER_LPN 33 UOM 28 WIP_JOB AMOUNT VALUE 1 SUM DESTINATION_TYPE ROW 1 LEDGER ROW 2 OPERATING_UNIT ROW 3 ORG_CODE ROW 4 PO_NUMBER ROW 5 S Site AF1441574FA748FEA5465DA55B4D9863 Summary by PO Distribution Net amount per PO distribution and receiving account. ENGINATICS 9 ACCOUNT 15 AMOUNT SUM 10 CHARGE_ACCOUNT 1 DESTINATION_TYPE 13 ITEM_DESCRIPTION 12 ITEM_NUMBER 14 ITEM_TYPE 2 LEDGER 3 OPERATING_UNIT 4 ORG_CODE 5 PERIOD_NAME 8 PO_DISTRIBUTION_ID 7 PO_LINE 6 PO_NUMBER 11 WIP_JOB S Site 620B4719CB29E7EBAFD6389E756561DB