INV Intercompany Invoice Reconciliation
Description
Categories: Enginatics
Repository: Github
Repository: Github
Intercompany invoice reconciliation for inventory transactions, including shipping and receiving organizations, ordered, transacted and invoiced quantities, amounts and possible discrepancies.
It also includes all intercompany Receivables (AR) and Payables (AP) invoice details. Optionally includes the Inventory and Intercompany AP SLA Accounting
It also includes all intercompany Receivables (AR) and Payables (AP) invoice details. Optionally includes the Inventory and Intercompany AP SLA Accounting
Run
INV Intercompany Invoice Reconciliation and other Oracle EBS reports with Blitz Report™ on our demo environment
with mmt as -- driving inventory transactions for intercompany ( select 'Sales Order' source_document , ooha.ordered_date source_document_date , to_char(ooha.order_number) source_document_num , oe_order_misc_pub.get_concat_line_number(oola.line_id) source_document_line_num , oola.ordered_quantity source_document_qty , oola.order_quantity_uom source_document_uom , 'INTERCOMPANY' source_line_context , null receipt_number , oola.line_id source_line_id , to_number(hoi.org_information3) shipping_ou_id , oola.org_id selling_ou_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_date , mmt.organization_id , mmt.transfer_organization_id , mmt.inventory_item_id , mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , mmt.transaction_cost , mmt.actual_cost , mmt.transfer_price , mmt.currency_code , mmt.currency_conversion_rate , mmt.currency_conversion_type , mmt.currency_conversion_date , mmt.costed_flag , mmt.invoiced_flag , mmt.transaction_id sla_inv_transaction_id , ( select distinct listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id) from mtl_item_categories_v micv where micv.category_set_id = :p_category_set_id and micv.organization_id = mmt.organization_id and micv.inventory_item_id = mmt.inventory_item_id ) &l_category_set_name , null requisition_number , to_number(null) requisition_line , to_number(null) requisition_unit_price , to_number(null) requisition_requested_qty , to_number(null) requisition_delivered_qty , to_number(null) requisition_delivered_amount , to_number(null) requisition_shipped_qty , to_number(null) requisition_shipped_amount , to_number(null) requisition_qty_variance , to_number(null) requisition_cost_variance from mtl_material_transactions mmt , hr_organization_information hoi , oe_order_lines_all oola , oe_order_headers_all ooha , mtl_intercompany_parameters mip where mmt.transaction_source_type_id in (2,12) -- sales order/rma and mmt.transaction_action_id in (1,27) -- issue from stores/receipt into stores and mmt.logical_transactions_created is null and oola.line_id = mmt.trx_source_line_id and ooha.header_id = oola.header_id and hoi.organization_id = mmt.organization_id and hoi.org_information_context = 'Accounting Information' and mip.ship_organization_id = to_number(hoi.org_information3) and mip.sell_organization_id = oola.org_id and nvl(:p_ico_source,'Sales Order') = 'Sales Order' union select 'Internal Sales Order' source_document , ooha.ordered_date source_document_date , to_char(ooha.order_number) source_document_num , oe_order_misc_pub.get_concat_line_number(oola.line_id) source_document_line_num , oola.ordered_quantity source_document_qty , oola.order_quantity_uom source_document_uom , 'INTERCOMPANY' source_line_context , rsh.receipt_num receipt_number , oola.line_id source_line_id , to_number(hoi1.org_information3) shipping_ou_id , to_number(hoi2.org_information3) selling_ou_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_date , mmt.organization_id , mmt.transfer_organization_id , mmt.inventory_item_id , mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , mmt.transaction_cost , mmt.actual_cost , mmt.transfer_price , mmt.currency_code , mmt.currency_conversion_rate , mmt.currency_conversion_type , mmt.currency_conversion_date , mmt.costed_flag , mmt.invoiced_flag , mmt2.transaction_id sla_inv_transaction_id , ( select distinct listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id) from mtl_item_categories_v micv where micv.category_set_id = :p_category_set_id and micv.organization_id = mmt.organization_id and micv.inventory_item_id = mmt.inventory_item_id ) &l_category_set_name , prha.segment1 requisition_number , prla.line_num requisition_line , prla.unit_price requisition_unit_price , (prla.quantity - nvl(prla.quantity_cancelled,0)) requisition_requested_qty , prla.quantity_delivered requisition_delivered_qty , prla.quantity_delivered * prla.unit_price requisition_delivered_amount , (select sum(oola2.shipped_quantity) from oe_order_lines_all oola2 where oola2.order_source_id = pspa.order_source_id and oola2.orig_sys_document_ref = prha.segment1 and oola2.orig_sys_line_ref = prla.line_num and oola2.source_document_id = prla.requisition_header_id and oola2.source_document_line_id = prla.requisition_line_id and oola2.shipped_quantity is not null and prla.quantity_delivered > 0 ) requisition_shipped_qty , (select sum(oola2.shipped_quantity * nvl(oola2.unit_selling_price,0)) from oe_order_lines_all oola2 where oola2.order_source_id = pspa.order_source_id and oola2.orig_sys_document_ref = prha.segment1 and oola2.orig_sys_line_ref = prla.line_num and oola2.source_document_id = prla.requisition_header_id and oola2.source_document_line_id = prla.requisition_line_id and oola2.shipped_quantity is not null and prla.quantity_delivered > 0 ) requisition_shipped_amount , (select sum(oola2.shipped_quantity) - prla.quantity_delivered from oe_order_lines_all oola2 where oola2.order_source_id = pspa.order_source_id and oola2.orig_sys_document_ref = prha.segment1 and oola2.orig_sys_line_ref = prla.line_num and oola2.source_document_id = prla.requisition_header_id and oola2.source_document_line_id = prla.requisition_line_id and oola2.shipped_quantity is not null and prla.quantity_delivered > 0 ) requisition_qty_variance , (select sum(oola2.shipped_quantity * nvl(oola2.unit_selling_price,0)) - (prla.quantity_delivered * nvl(prla.unit_price,0)) from oe_order_lines_all oola2 where oola2.order_source_id = pspa.order_source_id and oola2.orig_sys_document_ref = prha.segment1 and oola2.orig_sys_line_ref = prla.line_num and oola2.source_document_id = prla.requisition_header_id and oola2.source_document_line_id = prla.requisition_line_id and oola2.shipped_quantity is not null and prla.quantity_delivered > 0 ) requisition_cost_variance from mtl_material_transactions mmt , oe_order_lines_all oola , oe_order_headers_all ooha , hr_organization_information hoi1 , hr_organization_information hoi2 , mtl_intercompany_parameters mip , rcv_transactions rt , rcv_shipment_headers rsh , mtl_material_transactions mmt2 , po_requisition_headers_all prha , po_requisition_lines_all prla , po_system_parameters_all pspa where mmt.transaction_source_type_id in (8) -- internal order and mmt.transaction_action_id in (21) -- intransit shipment and oola.line_id = mmt.trx_source_line_id and ooha.header_id = oola.header_id and hoi1.organization_id = mmt.organization_id and hoi1.org_information_context = 'Accounting Information' and hoi2.organization_id = mmt.transfer_organization_id and hoi2.org_information_context = 'Accounting Information' and mip.ship_organization_id = to_number(hoi1.org_information3) and mip.sell_organization_id = to_number(hoi2.org_information3) and fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1 and mmt2.transfer_transaction_id (+) = mmt.transaction_id -- mmt2 = intransit receiving transaction and rt.transaction_id (+) = mmt2.rcv_transaction_id and rsh.shipment_header_id (+) = rt.shipment_header_id and prla.requisition_header_id (+) = oola.source_document_id and prla.requisition_line_id (+) = oola.source_document_line_id and prha.requisition_header_id (+) = prla.requisition_header_id and pspa.org_id (+) = prha.org_id and nvl(:p_ico_source,'Internal Sales Order') = 'Internal Sales Order' union select 'Sales Order' source_document , ooha.ordered_date source_document_date , to_char(ooha.order_number) source_document_num , oe_order_misc_pub.get_concat_line_number(oola.line_id) source_document_line_num , oola.ordered_quantity source_document_qty , oola.order_quantity_uom source_document_uom , 'INTERCOMPANY' source_line_context , null receipt_number , oola.line_id source_line_id , to_number(hoi1.org_information3) shipping_ou_id , to_number(hoi2.org_information3) selling_ou_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_date , mmt.organization_id , mmt.transfer_organization_id , mmt.inventory_item_id , mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , mmt.transaction_cost , mmt.actual_cost , mmt.transfer_price , mmt.currency_code , mmt.currency_conversion_rate , mmt.currency_conversion_type , mmt.currency_conversion_date , mmt.costed_flag , mmt.invoiced_flag , mmt.transaction_id sla_inv_transaction_id , ( select distinct listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id) from mtl_item_categories_v micv where micv.category_set_id = :p_category_set_id and micv.organization_id = mmt.organization_id and micv.inventory_item_id = mmt.inventory_item_id ) &l_category_set_name , null requisition_number , to_number(null) requisition_line , to_number(null) requisition_unit_price , to_number(null) requisition_requested_qty , to_number(null) requisition_delivered_qty , to_number(null) requisition_delivered_amount , to_number(null) requisition_shipped_qty , to_number(null) requisition_shipped_amount , to_number(null) requisition_qty_variance , to_number(null) requisition_cost_variance from mtl_material_transactions mmt , oe_order_lines_all oola , oe_order_headers_all ooha , hr_organization_information hoi1 , hr_organization_information hoi2 , mtl_transaction_flow_headers mtfh , mtl_intercompany_parameters mip where mmt.transaction_source_type_id in (13) -- inventory and mmt.transaction_action_id in (9,14) -- logical sales order issue/logical sales order receipt and mmt.logical_trx_type_code in (2,5) and oola.line_id = mmt.trx_source_line_id and ooha.header_id = oola.header_id and hoi1.organization_id = mmt.organization_id and hoi1.org_information_context = 'Accounting Information' and hoi2.organization_id = mmt.transfer_organization_id and hoi2.org_information_context = 'Accounting Information' and mtfh.header_id = mmt.trx_flow_header_id and mtfh.flow_type = 1 and mip.ship_organization_id = to_number(hoi1.org_information3) and mip.sell_organization_id = to_number(hoi2.org_information3) and nvl(:p_ico_source,'Sales Order') = 'Sales Order' union select 'Purchase Order' source_document , pha.creation_date source_document_date , pha.segment1 source_document_num , pla.line_num || '.' || plla.shipment_num source_document_line_num , plla.quantity - nvl(plla.quantity_cancelled,0) source_document_qty , nvl( ( select mufm.uom_code from mtl_units_of_measure mufm where mufm.unit_of_measure = plla.unit_meas_lookup_code and rownum=1 ) , plla.unit_meas_lookup_code ) source_document_uom , 'GLOBAL_PROCUREMENT' source_line_context , rsh.receipt_num receipt_number , plla.line_location_id source_line_id , to_number(hoi1.org_information3) shipping_ou_id , to_number(hoi2.org_information3) selling_ou_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_action_id , mmt.transaction_date , mmt.organization_id , mmt.transfer_organization_id , mmt.inventory_item_id , mmt.transaction_quantity , mmt.transaction_uom , mmt.primary_quantity , mmt.transaction_cost , mmt.actual_cost , mmt.transfer_price , mmt.currency_code , mmt.currency_conversion_rate , mmt.currency_conversion_type , mmt.currency_conversion_date , mmt.costed_flag , mmt.invoiced_flag , mmt.transaction_id sla_inv_transaction_id , ( select distinct listagg(micv.category_concat_segs,', ') within group (order by micv.category_concat_segs) over (partition by micv.inventory_item_id,micv.organization_id) from mtl_item_categories_v micv where micv.category_set_id = :p_category_set_id and micv.organization_id = mmt.organization_id and micv.inventory_item_id = mmt.inventory_item_id ) &l_category_set_name , null requisition_number , to_number(null) requisition_line , to_number(null) requisition_unit_price , to_number(null) requisition_requested_qty , to_number(null) requisition_delivered_qty , to_number(null) requisition_delivered_amount , to_number(null) requisition_shipped_qty , to_number(null) requisition_shipped_amount , to_number(null) requisition_qty_variance , to_number(null) requisition_cost_variance from mtl_material_transactions mmt , po_headers_all pha , rcv_transactions rt , rcv_shipment_headers rsh , po_line_locations_all plla , po_lines_all pla , hr_organization_information hoi1 , hr_organization_information hoi2 , mtl_transaction_flow_headers mtfh , mtl_intercompany_parameters mip where mmt.transaction_source_type_id in (13) -- invenory and mmt.transaction_action_id in (9,14) -- logical sales order issue/logical sales order receipt and mmt.logical_trx_type_code in (1,3) and pha.po_header_id = mmt.transaction_source_id and rt.transaction_id (+) = mmt.rcv_transaction_id and rsh.shipment_header_id (+) = rt.shipment_header_id and plla.line_location_id (+ |