INV Intercompany Invoice Reconciliation

Description
Categories: Enginatics
Repository: Github Columns: Shipping/Procuring Ou (Ar), Receiving Ou (Ap), Ordered Qty, Inv Transacted Qty, Ico Invoiced Qty, Ico Qty Diff, Ar Ico Line Amount, Ap Ico Line Amount, Ico Amount Diff, Source Document ...
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
    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
    &lp_include_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
    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
    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
    &lp_include_int_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
    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)
    &lp_include_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
    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)
    &lp_include_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
    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
    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
  )
, 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
    , 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
    from
      ap_invoices_all              aia
    , ap_invoice_lines_all         aila
    , ap_suppliers                 asup
    , ap_supplier_sites_all        assa
    where
        aila.invoice_id                      = aia.invoice_id
    and aia.vendor_id                        = asup.vendor_id
    and aia.vendor_site_id                   = assa.vendor_site_id
    and aia.source                       = 'Intercompany'
    and aila.line_type_lookup_code       = 'ITEM'
    and nvl(aila.discarded_flag,'N')     = 'N'
  )
, ap_intf as -- ap interface data
  (
    select
      aii.reference_1     customer_trx_id
    , aili.reference_1    customer_trx_line_id
    , aii.invoice_id
    , aii.status
    , aii.request_id
    , listagg(air1.reject_lookup_code,chr(10))
      within group (order by air1.reject_lookup_code)
      over (partition by aii.reference_1,aili.reference_1,aii.invoice_id,aii.status,aii.request_id) error_h
    , listagg(air2.reject_lookup_code,chr(10))
      within group (order by air2.reject_lookup_code)
      over (partition by aii.reference_1,aili.reference_1,aii.invoice_id,aii.status,aii.request_id) error_l
    from
      ap_invoices_interface      aii
    , ap_invoice_lines_interface aili
    , (select distinct
         parent_table
       , parent_id
       , reject_lookup_code
       from
         ap_interface_rejections
       where
         parent_table = 'AP_INVOICES_INTERFACE'
       )    air1
    , (select distinct
         parent_table
       , parent_id
       , reject_lookup_code
       from
         ap_interface_rejections
       where
         parent_table = 'AP_INVOICE_LINES_INTERFACE'
       )    air2
    where
        aii.invoice_id           = aili.invoice_id
    and air1.parent_id       (+) = aii.invoice_id
    and air2.parent_id       (+) = aili.invoice_line_id
    and aii.source       = 'Intercompany'
    and aii.status      != 'PROCESSED'
  )
&l_ap_sla_tables
&l_inv_sla_tables
-- ******************************************************
-- main query start here
-- ******************************************************
select
   &lp_select_columns1
   &lp_select_columns2
   &lp_sla_columns
from
  mmt                          mmt   -- inventory material transaction
, mtl_trx_types_view           mttv
, mtl_system_items_kfv         msik
, org_organization_definitions ood1
, org_organization_definitions ood2
, hr_all_organization_units    haou1
, hr_all_organization_units    haou2
, ar_intf                      ar_intf  -- ar invoice interface lines
, ar_ico                       ar_ico   -- ar ico invoices
, ap_intf                      ap_intf  -- ap invoice interface
, ap_ico                       ap_ico   -- ap ico invoices
&lp_sla_from
where
-- inventory
    mmt.transaction_type_id                = mttv.transaction_type_id
and mmt.organization_id                    = msik.organization_id
and mmt.inventory_item_id                  = msik.inventory_item_id
and mmt.organization_id                    = ood1.organization_id
and mmt.transfer_organization_id           = ood2.organization_id (+)
and mmt.shipping_ou_id                     = haou1.organization_id
and mmt.selling_ou_id                      = haou2.organization_id
-- ar interface
and ar_intf.interface_line_context     (+) = mmt.source_line_context
and ar_intf.interface_line_attribute6  (+) = to_char(mmt.source_line_id)
and ar_intf.interface_line_attribute7  (+) = to_char(mmt.transaction_id)
-- ar invoice
and ar_ico.interface_line_context      (+) = mmt.source_line_context
and ar_ico.interface_line_attribute6   (+) = to_char(mmt.source_line_id)
and ar_ico.interface_line_attribute7   (+) = to_char(mmt.transaction_id)
-- ap interface
and ap_intf.customer_trx_id            (+) = to_char(ar_ico.customer_trx_id)
and ap_intf.customer_trx_line_id       (+) = to_char(ar_ico.customer_trx_line_id)
-- ap invoice
and ap_ico.customer_trx_id             (+) = to_char(ar_ico.customer_trx_id)
and ap_ico.customer_trx_line_id        (+) = to_char(ar_ico.customer_trx_line_id)
-- sla accounting
&lp_sla_joins
--
and 1=1
&lp_group_by_columns
order by
  haou1.name
, haou2.name
, ar_ico.trx_date
, ar_ico.trx_number
Parameter Name SQL text Validation
Shipping/Procuring Operating Unit
haou1.name = :p_ship_ou
LOV
Receiving Operating Unit
haou2.name = :p_sell_ou
LOV
Quantity Variance only
nvl(mmt.source_document_qty,0) - nvl(ar_ico.quantity_invoiced,0) != 0
LOV
Amount Variance only
nvl(ar_ico.extended_amount,0) - nvl(ap_ico.line_amount,0) != 0
LOV
Report Detail
  haou1.name                                          "Shipping/Procuring OU (AR)"
, haou2.name                                          "Receiving OU (AP)"
, mmt.source_document_qty                             ordered_qty
, -mmt.transaction_quantity                           inv_transacted_qty
, ar_ico.quantity_invoiced                            ico_invoiced_qty
, nvl(mmt.source_document_qty,0) - nvl(ar_ico.quantity_invoiced,0) ico_qty_diff
, ar_ico.extended_amount                              ar_ico_line_amount
, ap_ico.line_amount                                  ap_ico_line_amount
, nvl(ar_ico.extended_amount,0) - nvl(ap_ico.line_amount,0) ico_amount_diff
, mmt.source_document
, mmt.source_document_date
, mmt.source_document_num
, mmt.source_document_line_num                        "Source Line/Shipment Num"
, mmt.source_document_qty
, (select muot.unit_of_measure_tl from mtl_units_of_measure_tl muot where mmt.source_document_uom=muot.uom_code and muot.language=userenv('lang')) source_document_uom
, mmt.receipt_number
, msik.concatenated_segments                          item
, msik.description item_description
, ood1.organization_code                               shipping_warehouse
, ood1.organization_name shipping_warehouse_name
, ood2.organization_code                               receiving_warehouse
, ood2.organization_name receiving_warehouse_name
, mmt.transaction_id                                  inv_trx_id
, mttv.transaction_source_type_name                   inv_trx_source
, xxen_util.meaning(mmt.transaction_action_id,'MTL_TRANSACTION_ACTION',700) inv_action
, mttv.transaction_type_name                          inv_trx_type
, mmt.transaction_date                                inv_trx_date
, mmt.transaction_quantity                            inv_trx_qty
, (select muot.unit_of_measure_tl from mtl_units_of_measure_tl muot where mmt.transaction_uom=muot.uom_code and muot.language=userenv('lang')) inv_trx_uom
, mmt.primary_quantity                                inv_primary_qty
, mmt.transaction_cost                                inv_trx_cost
, mmt.actual_cost                                     inv_actual_cost
, mmt.transfer_price                                  inv_transfer_price
, mmt.currency_code                                   inv_currency
, mmt.currency_conversion_rate                        inv_exchange_rate
, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where mmt.currency_conversion_type=gdct.conversion_type) inv_exchange_type
, mmt.currency_conversion_date                        inv_exchange_date
, xxen_util.meaning(mmt.costed_flag,'YES_NO',0) inv_costed_flag
, xxen_util.meaning(mmt.invoiced_flag,'YES_NO',0) inv_invoiced_flag
LOV
Shipment/Receiving Date From
mmt.transaction_date >= :p_date_fr
Date
Shipment/Receiving Date To
mmt.transaction_date < :p_date_to + 1
Date
Intercompany Source
and 1=1
LOV
Report Detail
  haou1.name                                          "Shipping/Procuring OU (AR)"
, haou2.name                                          "Receiving OU (AP)"
, sum(ar_ico.extended_amount)                         ar_ico_line_amount
, sum(ap_ico.line_amount)                             ap_ico_line_amount
, sum(nvl(ar_ico.extended_amount,0) - nvl(ap_ico.line_amount,0))
                                                      ico_amount_diff
, mmt.source_document
, mmt.source_document_date
, mmt.source_document_num
--
, ar_ico.customer_name                                ar_ico_customer_name
, ar_ico.customer_num                                 ar_ico_customer_num
, ar_ico.customer_location                            ar_ico_customer_location
, ar_ico.trx_number                                   ar_ico_inv_num
, ar_ico.trx_date                                     ar_ico_inv_date
, ar_ico.invoice_currency_code                        ar_ico_inv_curr
, ar_ico.amount_due_original                          ar_ico_inv_total_amount
, ar_ico.tax_original                                 ar_ico_inv_tax_amount
, sum(ar_ico.extended_amount)                         ar_ico_inv_line_amount
--
, ap_ico.vendor_name                                  ap_ico_vendor_name
, ap_ico.vendor_num                                   ap_ico_vendor_num
, ap_ico.vendor_site_code                             ap_ico_vendor_site
, ap_ico.invoice_num                                  ap_ico_inv_num
, ap_ico.invoice_date                                 ap_ico_inv_date
, ap_ico.invoice_currency_code                        ap_ico_inv_curr
, ap_ico.invoice_amount                               ap_ico_inv_total_amount
, ap_ico.total_tax_amount                             ap_ico_inv_tax_amount
, sum(ap_ico.line_amount)                             ap_ico_inv_line_amount
Report Detail
group by
  haou1.name
, haou2.name
, mmt.source_document
, mmt.source_document_date
, mmt.source_document_num
--
, ar_ico.customer_name
, ar_ico.customer_num
, ar_ico.customer_location
, ar_ico.trx_number
, ar_ico.trx_date
, ar_ico.invoice_currency_code
, ar_ico.amount_due_original
, ar_ico.tax_original
--
, ap_ico.vendor_name
, ap_ico.vendor_num
, ap_ico.vendor_site_code
, ap_ico.invoice_num
, ap_ico.invoice_date
, ap_ico.invoice_currency_code
, ap_ico.invoice_amount
, ap_ico.total_tax_amount
Report Detail
select null from dual
Report Detail
select null from dual
Report Detail
, ar_ico.customer_name                                ar_ico_customer_name
, ar_ico.customer_num                                 ar_ico_customer_num
, ar_ico.customer_location                            ar_ico_customer_location
, ar_ico.trx_number                                   ar_ico_inv_num
, ar_ico.trx_date                                     ar_ico_inv_date
, ar_ico.invoice_currency_code                        ar_ico_inv_curr
, ar_ico.status                                       ar_ico_inv_status
, ar_ico.amount_due_original                          ar_ico_inv_total_amount
, ar_ico.tax_original                                 ar_ico_inv_tax_amount
, ar_ico.exchange_rate                                ar_ico_inv_exchange_rate
, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where ar_ico.exchange_rate_type=gdct.conversion_type) ar_ico_inv_exchange_type
, ar_ico.exchange_date                                ar_ico_inv_exchange_date
, ar_ico.line_number                                  ar_ico_inv_line_num
, ar_ico.quantity_invoiced                            ar_ico_inv_line_qty
, ar_ico.uom_code                                     ar_ico_inv_line_uom
, ar_ico.unit_selling_price                           ar_ico_inv_line_unit_price
, ar_ico.extended_amount                              ar_ico_inv_line_amount
--
, ap_ico.vendor_name                                  ap_ico_vendor_name
, ap_ico.vendor_num                                   ap_ico_vendor_num
, ap_ico.vendor_site_code                             ap_ico_vendor_site
, ap_ico.invoice_num                                  ap_ico_inv_num
, ap_ico.invoice_date                                 ap_ico_inv_date
, ap_ico.invoice_currency_code                        ap_ico_inv_curr
, ap_ico.invoice_amount                               ap_ico_inv_total_amount
, ap_ico.total_tax_amount                             ap_ico_inv_tax_amount
, ap_ico.exchange_rate                                ap_ico_inv_exchange_rate
, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where ap_ico.exchange_rate_type=gdct.conversion_type) ap_ico_inv_exchange_type
, ap_ico.exchange_date                                ap_ico_inv_exchange_date
, ap_ico.line_number                                  ap_ico_inv_line_num
, ap_ico.quantity_invoiced                            ap_ico_inv_qty
, ap_ico.unit_meas_lookup_code                        ap_ico_inv_uom
, ap_ico.unit_price                                   ap_ico_inv_line_unit_price
, ap_ico.line_amount                                  ap_ico_inv_line_amount
--
, case nvl(ar_ico.customer_trx_id,-99)
    when -99
    then case nvl(ar_intf.interface_line_attribute6,'??')
         when '??' then 'NOT POPULATED'
         else case nvl(ar_intf.request_id,-99)
              when -99 then 'UNPROCESSED'
              else nvl(ar_intf.interface_status,'ERROR')
              end
         end
    else null
  end                                                 ar_interface_status
, case nvl(ar_ico.customer_trx_id,-99)
    when -99
    then ar_intf.error
    else null
  end                                                 ar_interface_error
--
, case nvl(ap_ico.invoice_id,-99)
    when -99
    then case nvl(ap_intf.invoice_id,-99)
         when -99 then 'NOT POPULATED'
         else case nvl(ap_intf.request_id,-99)
              when -99 then 'UNPROCESSED'
              else ap_intf.status
              end
         end
    else null
  end                                                 ap_interface_status
, case nvl(ap_ico.invoice_id,-99)
    when -99
    then ap_intf.error_h ||
         nvl2(ap_intf.error_h || ap_intf.error_l,chr(10),null) ||
         ap_intf.error_l
    else null
  end                                                 ap_interface_error
Report Detail
, sla3.sla_accounted_dr             ap_sla_accounted_dr
, sla3.sla_accounted_cr             ap_sla_accounted_cr
, sla3.sla_account                  ap_sla_account
, nvl(sla1.sla_accounting_class
     ,sla2.sla_accounting_class)      inv_sla_accounting_class
, case when sla1.sla_event_id is not null
  then sla1.sla_accounted_dr
  else sla2.sla_accounted_dr
  end                                 inv_sla_accounted_dr
, case when sla1.sla_event_id is not null
  then sla1.sla_accounted_cr
  else sla2.sla_accounted_cr
  end                                 inv_sla_accounted_cr
, case when sla1.sla_event_id is not null
  then sla1.sla_account
  else sla2.sla_account
  end                                 inv_sla_account
Report Detail
, sla1 as -- sla accounting for inventory transaction discrete
  (
    select
      mta.transaction_id
    , xdl.event_id               sla_event_id
    , xal.accounting_class_code  sla_accounting_class
    , xal.accounted_dr           sla_accounted_dr
    , xal.accounted_cr           sla_accounted_cr
    , gcck.concatenated_segments sla_account
    from
      mtl_transaction_accounts     mta
    , xla_distribution_links       xdl
    , xla_ae_lines                 xal
    , gl_ledgers                   gl
    , gl_code_combinations_kfv     gcck
    where
       mta.accounting_line_type              != 1 -- inventory valuation
    and xdl.source_distribution_type          = 'MTL_TRANSACTION_ACCOUNTS'
    and xdl.source_distribution_id_num_1      = mta.inv_sub_ledger_id
    and xal.ae_header_id                      = xdl.ae_header_id
    and xal.ae_line_num                       = xdl.ae_line_num
   and xal.application_id                = xdl.application_id
    and xal.accounting_class_code            != 'INVENTORY_VALUATION'
    and gl.ledger_id                          = xal.ledger_id
    and gl.ledger_category_code               = 'PRIMARY'
    and gcck.code_combination_id              = xal.code_combination_id
  )
, sla2 as -- sla accounting for inventory transaction opm
  (
   select
     gxeh.transaction_id
   , xdl.event_id               sla_event_id
   , xal.accounting_class_code  sla_accounting_class
   , xal.accounted_dr           sla_accounted_dr
   , xal.accounted_cr           sla_accounted_cr
   , gcck.concatenated_segments sla_account
   from
     gmf_xla_extract_headers      gxeh
   , gmf_xla_extract_lines        gxel
   , xla_distribution_links       xdl
   , xla_ae_lines                 xal
   , gl_ledgers                   gl
   , gl_code_combinations_kfv     gcck
   where
       gxel.header_id                    = gxeh.header_id
   and gxel.event_id                     = gxeh.event_id
   and gxel.journal_line_type           != 'INV'
   and xdl.source_distribution_type      = gxeh.entity_code
   and xdl.source_distribution_id_num_1  = gxel.line_id
   and xdl.event_id                      = gxeh.event_id
   and xal.ae_header_id                  = xdl.ae_header_id
   and xal.ae_line_num                   = xdl.ae_line_num
   and xal.application_id                = xdl.application_id
   and xal.accounting_class_code        != 'INVENTORY_VALUATION'
   and gl.ledger_id                      = xal.ledger_id
   and gl.ledger_category_code           = 'PRIMARY'
   and gcck.code_combination_id          = xal.code_combination_id
  )