INV Intercompany Invoice Reconciliation

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
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
    ,