ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E52E91375DC42DEAE0530100007F9E83Y select x.ledger, x.operating_unit, x.org_code, x.period_name, &column_segments aps.vendor_name supplier, (select he.full_name from hr_employees he where pha.agent_id=he.employee_id) buyer, x.item_number, nvl(x.item_description,pla.item_description) item_description, xxen_util.meaning(x.item_type,'ITEM_TYPE',3) item_type, misv.inventory_item_status_code_tl item_status, xxen_util.meaning(x.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy_code, &category_columns xxen_util.meaning(x.destination_type_code,'DESTINATION TYPE',201) destination_type, pha.segment1 po_number, pla.line_num po_line, xxen_util.meaning(coalesce(plla.closed_code,pla.closed_code,pha.closed_code),'DOCUMENT STATE',201) po_line_status, pra.release_num po_release, ppa.segment1 project_number, ppa.name project_name, (select /*+ no_unnest*/ max(prha.segment1) from po_requisition_lines_all prla, po_requisition_headers_all prha where x.po_line_location_id=prla.line_location_id and prla.requisition_header_id=prha.requisition_header_id) requisition_number, (select /*+ no_unnest*/ max(he.full_name) from po_requisition_lines_all prla, po_requisition_headers_all prha, hr_employees he where x.po_line_location_id=prla.line_location_id and prla.requisition_header_id=prha.requisition_header_id and prha.preparer_id=he.employee_id) requestor, (select /*+ no_unnest*/ max(he.email_address) from po_requisition_lines_all prla, po_requisition_headers_all prha, hr_employees he where x.po_line_location_id=prla.line_location_id and prla.requisition_header_id=prha.requisition_header_id and prha.preparer_id=he.employee_id) requestor_email, x.receipt_num receipt_number, x.transaction_date earliest_receipt_date, case when sysdate-x.transaction_date<31 then '30 days' when sysdate-x.transaction_date<61 then '60 days' when sysdate-x.transaction_date<91 then '90 days' when sysdate-x.transaction_date<121 then '120 days' when sysdate-x.transaction_date<151 then '150 days' when sysdate-x.transaction_date<181 then '180 days' else 'Over 180 days' end aging_date, &p_show_wip_osp x.primary_uom_code uom_code, x.quantity onhand_quantity, x.currency_code, x.amount onhand_value, x.adjustment_amount, x.amount+x.adjustment_amount total_onhand_value from ( select nvl(gl.short_name,gl.name) ledger, haouv_ou.name operating_unit, mp.organization_code org_code, oap.period_name, gl.currency_code, n.organization_id, n.inventory_item_id, msiv.concatenated_segments item_number, msiv.description item_description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, decode(:p_product_line_accounting,'Y',nvl((select gcc2.code_combination_id from gl_code_combinations gcc_item, gl_code_combinations gcc, gl_code_combinations gcc2, fnd_lookups fl where fl.lookup_type='INV_ITEM_ATTRIBUTES' and fl.meaning=:p_item_master_account_type and decode(fl.lookup_code,'SALES_ACCOUNT',msiv.sales_account,'COST_OF_SALES_ACCOUNT',msiv.cost_of_sales_account,'EXPENSE_ACCOUNT',msiv.expense_account)=gcc_item.code_combination_id and rp.receiving_account_id=gcc.code_combination_id and &gcc_product_line_where_clause gcc.chart_of_accounts_id=gcc2.chart_of_accounts_id),rp.receiving_account_id),rp.receiving_account_id) receiving_account_id, n.destination_type_code, n.po_header_id, n.po_line_id, n.po_line_location_id, n.po_release_id, n.project_id, n.receipt_num, (select /*+ no_unnest*/ min(rta.transaction_date) from rcv_shipment_headers rsha, rcv_transactions rta where n.shipment_header_id=rsha.shipment_header_id and n.receipt_num=rsha.receipt_num and rsha.shipment_header_id=rta.shipment_header_id and n.organization_id=rta.organization_id) transaction_date, n.wip_entity_id, n.bom_resource_id, sum(n.quantity) quantity, sum(n.amount) amount, sum(n.adjustment_amount) adjustment_amount from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou, hr_all_organization_units_vl haouv_ou, gl_ledgers gl, org_acct_periods oap, rcv_parameters rp, ( select rs.rcv_transaction_id transaction_id, rs.to_organization_id organization_id, rs.item_id inventory_item_id, rs.destination_type_code, rs.po_header_id, rs.po_line_id, rs.po_line_location_id, rs.po_release_id, rs.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, round(rs.to_org_primary_quantity,3) quantity, rs.amount, 0 adjustment_amount from ( select ms.to_organization_id, ms.item_id, ms.destination_type_code, ms.po_header_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id, ms.po_release_id, ms.shipment_header_id, ms.shipment_line_id, ms.rcv_transaction_id, ms.to_org_primary_quantity, round(ms.to_org_primary_quantity*round(sum(case when nvl(rt_correct.po_unit_price,nvl(rt_deliver.po_unit_price,0))<>nvl(rae.unit_price,0) then nvl(plla.price_override,nvl(rae.unit_price,0)) else nvl(rae.unit_price,0) end*nvl(rae.currency_conversion_rate,1)*rae.source_doc_quantity)/sum(rae.primary_quantity),8),2) amount from mtl_supply ms, po_line_locations_all plla, ( select x.parent_transaction_id, x.organization_id, x.child_transaction_id from ( select rt.transaction_id parent_transaction_id, rt.organization_id, connect_by_root rt.transaction_id child_transaction_id, connect_by_isleaf isleaf from rcv_transactions rt connect by prior rt.parent_transaction_id=rt.transaction_id start with rt.transaction_id in (select ms.rcv_transaction_id from mtl_supply ms where ms.supply_type_code='RECEIVING' and 5=5) and nvl(rt.consigned_flag,'N')='N' and 2=2 ) x where x.isleaf=1 ) rt, rcv_accounting_events rae, rcv_transactions rt_deliver, rcv_transactions rt_correct where ms.rcv_transaction_id=rt.child_transaction_id and ms.to_organization_id=rt.organization_id and rt.parent_transaction_id=rae.rcv_transaction_id and rt.organization_id=rae.organization_id and ms.po_line_location_id=plla.line_location_id(+) and ms.rcv_transaction_id=rt_deliver.parent_transaction_id(+) and rt_deliver.transaction_type(+)='DELIVER' and rt_deliver.transaction_id=rt_correct.parent_transaction_id(+) and rt_correct.transaction_type(+)='CORRECT' group by ms.to_organization_id, ms.item_id, ms.destination_type_code, ms.po_header_id, ms.po_line_id, ms.po_line_location_id, ms.po_distribution_id, ms.po_release_id, ms.shipment_header_id, ms.shipment_line_id, ms.rcv_transaction_id, ms.to_org_primary_quantity ) rs, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pda where rs.shipment_header_id=rsh.shipment_header_id and rs.shipment_line_id=rsl.shipment_line_id and rsl.source_document_code<>'REQ' and rs.po_distribution_id=pda.po_distribution_id(+) union all select distinct rt.transaction_id, rae.organization_id, rae.inventory_item_id, pda.destination_type_code, rae.po_header_id, rae.po_line_id, rae.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, round(case when rt.transaction_type in ('RECEIVE','MATCH') or rt.transaction_type='CORRECT' and rt_parent.transaction_type='RECEIVE' then -rt.primary_quantity when rt.transaction_type='RETURN TO VENDOR' or rt.transaction_type='CORRECT' and rt_parent.transaction_type='RETURN TO VENDOR' then rt.primary_quantity else 0 end,3) quantity, round(round(decode(sign(nvl(rrsl.accounted_dr,0)-nvl(rrsl.accounted_cr,0)),1,-abs(rae.primary_quantity),-1,abs(rae.primary_quantity),rae.primary_quantity),3)*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)*rt.source_doc_quantity/rt.primary_quantity,2) amount, 0 adjustment_amount from org_acct_periods oap, rcv_transactions rt, rcv_transactions rt_parent, rcv_accounting_events rae, rcv_receiving_sub_ledger rrsl, po_distributions_all pda, rcv_shipment_headers rsh, rcv_shipment_lines rsl where 2=2 and 3=3 and oap.organization_id=rt.organization_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.transaction_type<>'DELIVER' and rt.parent_transaction_id=rt_parent.transaction_id(+) and rt.organization_id=rt_parent.organization_id(+) and rt.transaction_id=rae.rcv_transaction_id and rae.accounting_event_id=rrsl.accounting_event_id and rt.transaction_id=rrsl.rcv_transaction_id and rrsl.accounting_line_type in ('Clearing','Receiving Inspection') and rrsl.transaction_date>=oap.schedule_close_date+1 and rrsl.reference3=pda.po_distribution_id and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id union all select rt.transaction_id, mmt.organization_id, rsl.item_id inventory_item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, round(sum(mmt.primary_quantity),3) quantity, round(sum(mmt.primary_quantity*rt.source_doc_quantity/rt.primary_quantity*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)),2) amount, 0 adjustment_amount from org_acct_periods oap, mtl_material_transactions mmt, rcv_parameters rp, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pda where 2=2 and 3=3 and oap.organization_id=mmt.organization_id and mmt.transaction_date>=oap.schedule_close_date+1 and mmt.transaction_source_type_id=1 and mmt.organization_id=rp.organization_id and mmt.distribution_account_id=rp.receiving_account_id and mmt.rcv_transaction_id=rt.transaction_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rt.po_line_location_id=pda.line_location_id and nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id group by rt.transaction_id, mmt.organization_id, rsl.item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id union all select rt.transaction_id, wta.organization_id, rsl.item_id inventory_item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, round(case when rt.transaction_type='DELIVER' or rt.transaction_type='CORRECT' and rt_parent.transaction_type='DELIVER' then rt.primary_quantity when rt.transaction_type='RETURN TO RECEIVING' or rt.transaction_type='CORRECT' and rt_parent.transaction_type in ('RETURN TO RECEIVING','MATCH') then -rt.primary_quantity else 0 end,3) quantity, round(-sum(wta.base_transaction_value),2) amount, 0 adjustment_amount from org_acct_periods oap, wip_transaction_accounts wta, rcv_parameters rp, wip_transactions wt, rcv_transactions rt, rcv_transactions rt_parent, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pda where 2=2 and 3=3 and oap.organization_id=wta.organization_id and wta.transaction_date>=oap.schedule_close_date+1 and wta.accounting_line_type in (4,5) and wta.organization_id=rp.organization_id and wta.reference_account=rp.receiving_account_id and wta.transaction_id=wt.transaction_id and wt.transaction_date>=oap.schedule_close_date+1 and wt.rcv_transaction_id=rt.transaction_id and wt.organization_id=rt.organization_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.parent_transaction_id=rt_parent.transaction_id(+) and rt.organization_id=rt_parent.organization_id(+) and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rt.po_line_location_id=pda.line_location_id and nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id and pda.destination_type_code<>'EXPENSE' and wta.wip_entity_id=pda.wip_entity_id group by rt.transaction_id, rt.transaction_type, rt_parent.transaction_type, rt.primary_quantity, wta.organization_id, rsl.item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id union all select rt.transaction_id, wcti.organization_id, rsl.item_id inventory_item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, round(sum(wcti.primary_quantity),3) quantity, round(sum(wcti.primary_quantity*rt.source_doc_quantity/rt.primary_quantity*nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)),2) amount, 0 adjustment_amount from org_acct_periods oap, wip_cost_txn_interface wcti, rcv_parameters rp, rcv_transactions rt, rcv_shipment_headers rsh, rcv_shipment_lines rsl, po_distributions_all pda where 2=2 and 3=3 and oap.organization_id=wcti.organization_id and wcti.transaction_date>=oap.schedule_close_date+1 and wcti.transaction_type=3 and wcti.organization_id=rp.organization_id and wcti.receiving_account_id=rp.receiving_account_id and wcti.rcv_transaction_id=rt.transaction_id and wcti.organization_id=rt.organization_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id and rt.po_line_location_id=pda.line_location_id and nvl(rsl.po_distribution_id,pda.po_distribution_id)=pda.po_distribution_id and pda.destination_type_code<>'EXPENSE' and wcti.wip_entity_id=pda.wip_entity_id group by rt.transaction_id, wcti.organization_id, rsl.item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id union all select rt.transaction_id, rt.organization_id, rsl.item_id inventory_item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, 0 quantity, round(-sum(nvl(rrsl.accounted_dr,0)-nvl(rrsl.accounted_cr,0)),2) amount, 0 adjustment_amount from org_acct_periods oap, rcv_transactions rt, rcv_parameters rp, rcv_receiving_sub_ledger rrsl, po_distributions_all pda, rcv_shipment_headers rsh, rcv_shipment_lines rsl where 2=2 and 3=3 and oap.organization_id=rt.organization_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.transaction_type='DELIVER' and rt.organization_id=rp.organization_id and rt.transaction_id=rrsl.rcv_transaction_id and rrsl.accounting_line_type in ('Clearing','Receiving Inspection') and rrsl.transaction_date>=oap.schedule_close_date+1 and rrsl.code_combination_id=rp.receiving_account_id and rrsl.reference3=pda.po_distribution_id and rt.po_line_location_id=pda.line_location_id and pda.destination_type_code<>'EXPENSE' and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id group by rt.transaction_id, rt.organization_id, rsl.item_id, pda.destination_type_code, rt.po_header_id, rt.po_line_id, rt.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id union all select distinct rt.transaction_id, rae.organization_id, rae.inventory_item_id, pda.destination_type_code, rae.po_header_id, rae.po_line_id, rae.po_line_location_id, rsl.po_release_id, rsh.shipment_header_id, pda.project_id, rsh.receipt_num, pda.wip_entity_id, pda.bom_resource_id, 0 quantity, 0 amount, round(case when rt.transaction_type in ('RECEIVE','MATCH') or rt.transaction_type='CORRECT' and rt_parent.transaction_type='RECEIVE' then -rt.primary_quantity when rt.transaction_type='RETURN TO VENDOR' or rt.transaction_type='CORRECT' and rt_parent.transaction_type='RETURN TO VENDOR' then rt.primary_quantity else 0 end* (nvl(rt_deliver.po_unit_price,0)*nvl(rt_deliver.currency_conversion_rate,1)*rt_deliver.source_doc_quantity/rt_deliver.primary_quantity-nvl(rt.po_unit_price,0)*nvl(rt.currency_conversion_rate,1)*rt.source_doc_quantity/rt.primary_quantity),2) adjustment_amount from org_acct_periods oap, rcv_transactions rt, rcv_transactions rt_parent, rcv_transactions rt_deliver, rcv_parameters rp, rcv_accounting_events rae, rcv_receiving_sub_ledger rrsl, po_distributions_all pda, rcv_shipment_headers rsh, rcv_shipment_lines rsl where 2=2 and 3=3 and oap.organization_id=rt.organization_id and rt.transaction_date>=oap.schedule_close_date+1 and rt.transaction_type<>'DELIVER' and rt.parent_transaction_id=rt_parent.transaction_id(+) and rt.organization_id=rt_parent.organization_id(+) and rt.transaction_id=rt_deliver.parent_transaction_id and rt_deliver.transaction_type='DELIVER' and rt_deliver.po_unit_price<>rt.po_unit_price and rt_deliver.transaction_date>=oap.schedule_close_date+1 and rt.organization_id=rp.organization_id and rp.retroprice_adj_account_id is null and rt.transaction_id=rae.rcv_transaction_id and rae.accounting_event_id=rrsl.accounting_event_id and rt.transaction_id=rrsl.rcv_transaction_id and rrsl.accounting_line_type in ('Clearing','Receiving Inspection') and rrsl.transaction_date>=oap.schedule_close_date+1 and rrsl.reference3=pda.po_distribution_id and rt.shipment_header_id=rsh.shipment_header_id and rt.shipment_line_id=rsl.shipment_line_id ) n, mtl_system_items_vl msiv where 1=1 and 3=3 and 4=4 and 6=6 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 hoi.organization_id=haou.organization_id and sysdate<nvl(haou.date_to,sysdate+1) and to_number(hoi.org_information3)=haouv_ou.organization_id and to_number(hoi.org_information1)=gl.ledger_id and mp.organization_id=oap.organization_id and mp.organization_id=rp.organization_id and mp.organization_id=n.organization_id and n.organization_id=msiv.organization_id(+) and n.inventory_item_id=msiv.inventory_item_id(+) group by gl.short_name, gl.name, haouv_ou.name, mp.organization_code, oap.period_name, gl.currency_code, n.organization_id, n.inventory_item_id, msiv.concatenated_segments, msiv.description, msiv.item_type, msiv.inventory_item_status_code, msiv.planning_make_buy_code, msiv.primary_uom_code, msiv.sales_account, msiv.cost_of_sales_account, msiv.expense_account, rp.receiving_account_id, n.destination_type_code, n.po_header_id, n.po_line_id, n.po_line_location_id, n.po_release_id, n.project_id, n.receipt_num, n.shipment_header_id, n.wip_entity_id, n.bom_resource_id having abs(sum(nvl(n.quantity,0)+nvl(n.amount,0)))>.01 ) x, mtl_item_status_vl misv, po_headers_all pha, po_lines_all pla, po_line_locations_all plla, po_releases_all pra, ap_suppliers aps, pa_projects_all ppa, gl_code_combinations gcc where x.inventory_item_status_code=misv.inventory_item_status_code(+) and x.po_header_id=pha.po_header_id and x.po_line_id=pla.po_line_id and x.po_line_location_id=plla.line_location_id and x.po_release_id=pra.po_release_id(+) and pha.vendor_id=aps.vendor_id and x.project_id=ppa.project_id(+) and x.receiving_account_id=gcc.code_combination_id(+) order by x.ledger, x.operating_unit, x.org_code, supplier, x.item_number, po_number, po_line, po_release, earliest_receipt_date Rebuilt to the Blitz Report SQL standards: one aggregation instead of three, the Item Number parameter no longer fails with ORA-00904, receipts without an item are listed with the purchase order line description. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security. #,##0.00000;[Red](#,##0.00000) US CAC Receiving Value (Period-End) Report to show receiving value for all locations, as of the end of an accounting period. You may run this report for open or closed accounting periods. With parameters to display or not display WIP outside processing information (WIP job, status, close date and OSP resource code). If run for a prior period the report automatically rolls back the quantities and values to the prior period's period end date. In addition, for any receipt with an item number, this report displays Inventory, Shop Floor (Outside Processing) and Expense destination types. Note: The Adjustment Amount column show differences between the PO unit price for the Receipt and Deliver transactions, which currently is not included in the receiving accounting entries. Reconciliation Notes to Oracle Reports: The Oracle All Inventory Values Report does not display Shop Floor (Outside Processing) or Expense destination types. If the PO line is closed, the Oracle Receiving Value Report does not display Expense destination types, even if the receipt is still in receiving and has not been delivered. /* +=============================================================================+ -- | Copyright 2009 - 2025 Douglas Volz Consulting, Inc. and Enginatics GmbH -- | All rights reserved. -- | Version Modified on Modified by Description -- | ======= =========== ============== ========================================= -- | 1.0 25 Nov 2009 Douglas Volz Created initial Report for client -- | based on xxx_inv_value.sql -- | 1.36 10 Aug 2022 Douglas Volz Performance improvements for main query. Allow expense -- | receipts with items, add PO line status column. -- | 1.37 12 Aug 2022 Douglas Volz Add parameter to Show WIP OSP columns and remove extra -- | join to the item master table. -- | 1.38 16 Aug 2022 Douglas Volz Fixes for quantities for Correction transactions and -- | for when the PO unit price is zero. -- | 1.39 02 Jun 2025 Douglas Volz Removed tabs. -- | 1.40 05 Jun 2025 Douglas Volz Added section 1.6 for PO unit price differences between -- | Piyush Khandelwal the PO Receipt and the PO Deliver transactions. Put -- | this into an "Adjustment Amount" column, as this is -- | included in the Oracle Receiving Value Report but is -- | not recorded on any Receiving Accounting entry. -- | Also added in inventory org access, XXEN G/L and OU security profiles. -- | 1.44 23 Oct 2026 Andy Haack Rebuilt to the Blitz Report SQL standards. -- +=============================================================================+*/ Enginatics &category_columns &column_segments &gcc_product_line_where_clause &p_show_wip_osp 1=1 2=2 3=3 4=4 5=5 6=6 :p_item_master_account_type :p_product_line_accounting 1 -10 &column_segments select 'gcc.'||lower(fifsv.application_column_name)||' "'||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||'",' text from (select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by 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=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID')) Y US Chart of Accounts 2 10 3=3 oap.period_name=:p_period_name LOV INV Period 8E2FF36EDF2279D2E0530100007F1FF2 select distinct oap.period_name value, max(oap.period_year||'-'||oap.period_num||', '||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,'P',2,'N',decode(oap.summarized_flag,'N',65,66),4),3),'MTL_ACCT_PERIOD_STATUS',700)||' ('||oap.period_start_date||' - '||oap.schedule_close_date||')') over (partition by oap.period_name) description, max(oap.period_start_date) over (partition by oap.period_name) period_start_date, max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num from gl_ledgers gl, org_organization_definitions ood, mtl_parameters mp, (select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap where oap.period_start_date<=sysdate and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and gl.period_set_name=oap.period_set_name and gl.ledger_id=ood.set_of_books_id and ood.organization_id=oap.organization_id order by period_start_date desc, effective_period_num desc, oap.period_name select distinct oap.period_name value from org_acct_periods oap, (select max(oap.schedule_close_date) default_date from org_acct_periods oap, org_organization_definitions ood where ood.organization_id = oap.organization_id and oap.period_start_date<=sysdate and nvl(ood.disable_date, sysdate + 1) > sysdate) max where oap.schedule_close_date = max.default_date Y US Period Name 3 20 &p_show_wip_osp (select we.wip_entity_name from wip_entities we where x.wip_entity_id=we.wip_entity_id) wip_job, (select xxen_util.meaning(wdj.status_type,'WIP_JOB_STATUS',700) from wip_discrete_jobs wdj where x.wip_entity_id=wdj.wip_entity_id) job_status, (select wdj.date_closed from wip_discrete_jobs wdj where x.wip_entity_id=wdj.wip_entity_id) job_close_date, (select br.resource_code from bom_resources br where x.bom_resource_id=br.resource_id) osp_resource, 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 N Y US Show WIP Outside Processing 4 30 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11)) US Category Set 1 5 40 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5)) US Category Set 2 6 50 &category_columns select xxen_util.item_category_columns(p_category_set_name=>'<parameter_value>', p_table_alias=>'x') sql_text from dual LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 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 xxen_util.previous_parameter_value(:parameter_id) US Category Set 3 7 60 6=6 msiv.concatenated_segments=:p_item_number LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 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 xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 Number 8 70 2=2 rt.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:p_org_code) LOV custom 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 ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 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 4=4 mp.organization_code=:p_org_code US Organization Code 10 5=5 ms.to_organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:p_org_code) US Organization Code 11 80 1=1 haouv_ou.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 90 1=1 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 100 :p_product_line_accounting 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 N Y US Product Line Accounting 14 110 :p_item_master_account_type LOV custom select fl.meaning value, fl.description description from fnd_lookups fl where fl.lookup_type = 'INV_ITEM_ATTRIBUTES' and fl.lookup_code in ('SALES_ACCOUNT', 'COST_OF_SALES_ACCOUNT', 'EXPENSE_ACCOUNT') and :$flex$.product_line_accounting = 'Y' Cost Of Goods Sold Account US Item Master Account Type 15 120 &gcc_product_line_where_clause select 'gcc'||decode(fifsv.form_left_prompt,:product_segment,'_item')||'.'||lower(fifsv.application_column_name)||'=gcc2.'||lower(fifsv.application_column_name)||' and' text from fnd_id_flex_segments_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and fifsv.enabled_flag='Y' and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:chart_of_accounts) order by fifsv.segment_num LOV custom select fifsv.form_left_prompt value, fifsv.application_column_name description from fnd_id_flex_segments_vl fifsv where :$flex$.product_line_accounting = 'Y' and fifsv.application_id=101 and fifsv.id_flex_code='GL#' and (fifsv.application_id, fifsv.id_flex_code, fifsv.id_flex_num, fifsv.application_column_name) not in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type in ('GL_BALANCING','GL_INTERCOMPANY','GL_ACCOUNT') and fsav.attribute_value='Y') and fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code='GL#' and fifsv0.id_flex_structure_name=:$flex$.chart_of_accounts) order by fifsv.segment_num Sub-Account US Product Line Segment 3B72B6E6AC3F2320B850B955F21B2414 Pivot by Org Y MFG 34 AGING_DATE 7 Account 11 BUYER 37 CURRENCY_CODE 20 Class 5 Company 22 DESTINATION_TYPE 6 Department 33 EARLIEST_RECEIPT_DATE 19 Family 13 ITEM_DESCRIPTION 12 ITEM_NUMBER 15 ITEM_STATUS 14 ITEM_TYPE 21 Inv.Items Description -34.0037 JOB_CLOSE_DATE -34.0036 JOB_STATUS 1 LEDGER 16 MAKE_BUY_CODE 36 ONHAND_QUANTITY 38 ONHAND_VALUE 2 OPERATING_UNIT 3 ORG_CODE -34.0038 OSP_RESOURCE 4 PERIOD_NAME 24 PO_LINE 25 PO_LINE_STATUS 23 PO_NUMBER 26 PO_RELEASE 28 PROJECT_NAME 27 PROJECT_NUMBER 9 Product 17 Product Cat 18 Product Description 32 RECEIPT_NUMBER 30 REQUESTOR 31 REQUESTOR_EMAIL 29 REQUISITION_NUMBER 10 SUPPLIER 8 Sub-Account 35 UOM_CODE -34.0035 WIP_JOB CURRENCY_CODE ROW 4 LEDGER ROW 1 ONHAND_QUANTITY VALUE 1 SUM ONHAND_VALUE VALUE 2 SUM OPERATING_UNIT ROW 2 ORG_CODE ROW 3 S Site 3B72B6E6AC3F2320B850B955F21B2414