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 (+) = rt.po_line_location_id and pla.po_line_id (+) = rt.po_line_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 = 2 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,'Purchase Order') = 'Purchase Order' ) , ar_ico as ( select rctla.interface_line_context , rctla.interface_line_attribute6 , rctla.interface_line_attribute7 , rctla.customer_trx_id , rctla.customer_trx_line_id , hp.party_name customer_name , hca.account_number customer_num , hcsua.location customer_location , apsa.trx_number , apsa.trx_date , apsa.invoice_currency_code , case apsa.status when 'OP' then 'Open' when 'CL' then 'Closed' else apsa.status end status , apsa.amount_due_original , apsa.tax_original , apsa.exchange_rate , apsa.exchange_rate_type , apsa.exchange_date , rctla.line_number , rctla.quantity_invoiced , rctla.uom_code , rctla.unit_selling_price , rctla.extended_amount , zl.tax tax_type , zl.tax_rate_code tax_rate_code , zl.tax_rate tax_rate from ra_customer_trx_lines_all rctla , ra_customer_trx_all rcta , ar_payment_schedules_all apsa , hz_cust_accounts hca , hz_parties hp , hz_cust_site_uses_all hcsua , ( select distinct zl1.trx_id , zl1.trx_line_id , listagg (zl1.tax,'/ ') within group (order by zl1.tax) over (partition by zl1.trx_id,zl1.trx_line_id) tax , listagg (zl1.tax_rate_code,'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate_code , listagg (to_char(zl1.tax_rate),'/ ') within group (order by zl1.tax, zl1.tax_rate_code) over (partition by zl1.trx_id,zl1.trx_line_id) tax_rate from (select distinct zl2.trx_id , zl2.trx_line_id , zl2.tax , zl2.tax_rate_code , zl2.tax_rate from zx_lines zl2 where zl2.application_id = 222 and zl2.entity_code = 'TRANSACTIONS' and zl2.trx_level_type = 'LINE' ) zl1 ) zl where rctla.interface_line_context in ('INTERCOMPANY','GLOBAL_PROCUREMENT') and rctla.line_type = 'LINE' and rcta.customer_trx_id = rctla.customer_trx_id and apsa.customer_trx_id = rctla.customer_trx_id and hca.cust_account_id = rcta.bill_to_customer_id and hp.party_id = hca.party_id and hcsua.site_use_id = rcta.bill_to_site_use_id and zl.trx_id (+) = rctla.customer_trx_id and zl.trx_line_id (+) = rctla.customer_trx_line_id ) , ar_intf as -- ar interface data ( select rila.interface_line_context , rila.interface_line_attribute6 , rila.interface_line_attribute7 , rila.interface_line_id , rila.interface_status , rila.request_id , listagg(riea.message_text,chr(10)) within group (order by riea.message_text) over (partition by rila.interface_line_context,rila.interface_line_attribute6,rila.interface_line_attribute7,rila.interface_line_id,rila.interface_status,rila.request_id) error from ra_interface_lines_all rila , (select distinct interface_line_id , message_text from ra_interface_errors_all ) riea where rila.interface_line_id = riea.interface_line_id (+) and nvl(rila.interface_status,'U') != 'P' ) , ap_ico as -- ap invoice data ( select aia.reference_1 customer_trx_id , aila.reference_1 customer_trx_line_id , aia.invoice_id , asup.vendor_name , asup.segment1 vendor_num , assa.vendor_site_code , aia.invoice_num , aia.invoice_date , aia.invoice_currency_code , aia.invoice_amount , aia.total_tax_amount , aia.amount_paid , aia.exchange_rate , aia.exchange_rate_type , aia.exchange_date , aila.line_number , xxen_util.meaning(aila.line_source,'LINE SOURCE',200) line_source , decode(aila.discarded_flag,'Y','Y',null) line_discarded_flag , aila.quantity_invoiced , nvl( ( select mufm.uom_code from mtl_units_of_measure mufm where mufm.unit_of_measure = aila.unit_meas_lookup_code and rownum=1 ) , aila.unit_meas_lookup_code ) unit_meas_lookup_code , aila.unit_price , aila.amount line_amount , zl.tax tax_type , zl.tax_rate_code tax_rate_code , zl.tax_rate tax_rate from ap_invoices_all aia , |