INV Intercompany Invoice Reconciliation

➜ Back to Library
Description
Categories: Enginatics
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
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            (+