AP Invoices with PO, Intercompany and SLA Details

➜ Back to Library
Description
Categories: Enginatics
Repository: Github Columns: Ledger, Operating Unit, Vendor Name, Vendor Num, Vendor Site Code, Invoice Source, Invoice Num, Invoice Date, Currency, Invoice Amount ...
AP Invoices with PO, Intercompany and SLA Details 
with ap_inv as -- ap invoice data
  ( select
      gl.name                          ledger
    , haouv.name                       operating_unit
    , asup.vendor_name
    , asup.segment1                    vendor_num
    , assa.vendor_site_code
    , aia.source
    , aia.invoice_id
    , 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
    , msi.item
    , aila.quantity_invoiced           line_qty_invoiced
    , nvl(mufm.uom_code
         ,aila.unit_meas_lookup_code)  uom
    , aila.unit_price
    , aila.amount                      line_amount
    , aila.description                 line_description
    , aida.invoice_distribution_id
    , aida.distribution_line_number    dist_line_number
    , aida.quantity_invoiced           dist_qty_invoiced
    , aida.amount                      dist_amount
    , aida.accounting_date             dist_accounting_date
    , aida.period_name                 dist_period
    , aida.description                 dist_description
    , nvl(aida.dist_match_type
         ,aila.match_type)             dist_match_type
    , case aia.source
      when 'Intercompany'
      then (select min(rsh.receipt_num)
            from   mtl_material_transactions mmt1
               ,   mtl_material_transactions mmt2
               ,   rcv_transactions          rt
               ,   rcv_shipment_headers      rsh
            where
                   mmt1.transaction_id          = to_number(aida.reference_2)
            and    mmt2.transfer_transaction_id = mmt1.transaction_id
            and    rt.transaction_id            = mmt2.rcv_transaction_id
            and    rsh.shipment_header_id       = rt.shipment_header_id
           )
      else (select rsh.receipt_num
            from   rcv_transactions             rt
               ,   rcv_shipment_headers         rsh
            where  rt.transaction_id       = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id)
            and  rsh.shipment_header_id  = rt.shipment_header_id
           )
      end                              dist_matched_receipt_num
    , aida.set_of_books_id             dist_ledger_id
    , aida.accounting_event_id         dist_accounting_event_id
    , nvl(aida.po_distribution_id,aila.po_distribution_id)  po_distribution_id
    , decode(aia.source,'Intercompany',to_number(aia.reference_1),null)          customer_trx_id
    , decode(aia.source,'Intercompany',to_number(aila.reference_1),null)         customer_trx_line_id
    , case aia.source
      when 'Intercompany'
      then (select min(mmt2.transaction_id)
            from   mtl_material_transactions mmt1
               ,   mtl_material_transactions mmt2
            where
                   mmt1.transaction_id = to_number(aida.reference_2)
            and    mmt2.transfer_transaction_id = mmt1.transaction_id
           )
      else (select min(mmt.transaction_id)
            from   rcv_transactions             rt1
               ,   rcv_transactions             rt2
               ,   mtl_material_transactions    mmt
            where  rt1.transaction_id       = nvl(aida.rcv_transaction_id,aila.rcv_transaction_id)
              and  rt2.shipment_header_id   = rt1.shipment_header_id
              and  rt2.shipment_line_id     = rt1.shipment_line_id
              and  rt2.transaction_type     = 'DELIVER'
              and  mmt.rcv_transaction_id   = rt2.transaction_id
           )
      end                              sla_inv_transaction_id
    from
      ap_invoices_all              aia
    , ap_invoice_lines_all         aila
    , ap_invoice_distributions_all aida
    , ap_suppliers                 asup
    , ap_supplier_sites_all        assa
    , hr_all_organization_units_vl haouv
    , gl_ledgers                   gl
    , mtl_units_of_measure         mufm
    , (select
         fspa.org_id
       , msik.inventory_item_id
       , msik.concatenated_segments item
       from
         financials_system_params_all fspa
       , mtl_system_items_kfv         msik
       where
           msik.organization_id = fspa.inventory_organization_id
       )                           msi
    where
        aila.invoice_id                  = aia.invoice_id
    and aila.line_type_lookup_code       = 'ITEM'
    and nvl(aila.discarded_flag,'N')     = 'N'
    and aida.invoice_id (+) = aila.invoice_id
    and aida.invoice_line_number  (+)  = aila.line_number
    and aida.line_type_lookup_code  (+)  = 'ITEM'
    and aia.vendor_id                    = asup.vendor_id
    and aia.vendor_site_id               = assa.vendor_site_id
    and haouv.organization_id            = aia.org_id
    and gl.ledger_id                     = aia.set_of_books_id
    and mufm.unit_of_measure         (+) = aila.unit_meas_lookup_code
    and msi.inventory_item_id        (+) = aila.inventory_item_id
    and msi.org_id                   (+) = aila.org_id
  )
, po as
  ( select
      pda.po_distribution_id
    , haouv.name                                       po_operating_unit
    , ( select
          prha.segment1 po_requisition
        from
          po_req_distributions_all         prda
        , po_requisition_lines_all         prla
        , po_requisition_headers_all       prha
        where
            prda.requisition_line_id   = prla.requisition_line_id
        and prla.requisition_header_id = prha.requisition_header_id
        and prda.distribution_id       = pda.req_distribution_id
      )                                                po_requisition_num
    , pha.segment1                                     po_num
    , pra.release_num                                  po_release_num
    , pha.creation_date                                po_creation_date
    , pra.release_date                                 po_release_date
    , pla.line_num || '.' || plla.shipment_num         po_line_shipment_num
    , msi.item                                         item
    , plla.quantity  - nvl(plla.quantity_cancelled,0)  po_shipment_qty_ordered
    , plla.quantity_accepted                           po_shipment_qty_accepted
    , plla.quantity_rejected                           po_shipment_qty_rejected
    , plla.quantity_billed                             po_shipment_qty_billed
    , nvl( mufm.uom_code, plla.unit_meas_lookup_code)  po_uom
    , pda.distribution_num                             po_dist_num
    , pda.quantity_ordered
        - nvl(pda.quantity_cancelled,0)                po_dist_qty_ordered
    , pda.quantity_delivered                           po_dist_qty_delivered
    , pda.quantity_billed                              po_dist_qty_billed
    , mp1.organization_code                            po_ship_to_organization
    , mp2.organization_code                            po_destination_organization
    , ( select
          listagg(rsh.receipt_num,', ') within group (order by rsh.receipt_num)
        from
          (select distinct rsh.receipt_num
           from rcv_transactions      rt
           , rcv_shipment_headers  rsh
           where
               rsh.shipment_header_id = rt.shipment_header_id
           and rt.po_header_id        = pda.po_header_id
           and rt.po_line_id          = pda.po_line_id
           and rt.po_line_location_id = pda.line_location_id
           and nvl(rt.po_distribution_id,pda.po_distribution_id)  
                                      = pda.po_distribution_id
          ) rsh
      )                                                po_dist_receipt_nums
    from
      po_distributions_all         pda
    , po_line_locations_all        plla
    , po_lines_all                 pla
    , po_headers_all               pha
    , po_releases_all              pra
    , mtl_units_of_measure         mufm
    , mtl_parameters               mp1
    , mtl_parameters               mp2
    , hr_all_organization_units_vl haouv
    , (select
         fspa.org_id
       , msik.inventory_item_id
       , msik.concatenated_segments item
       from
         financials_system_params_all fspa
       , mtl_system_items_kfv         msik
       where
           msik.organization_id = fspa.inventory_organization_id
       )                           msi
    where
        pda.po_header_id          = plla.po_header_id
    and pda.po_line_id            = plla.po_line_id
    and pda.line_location_id      = plla.line_location_id
    and plla.po_header_id         = pla.po_header_id
    and plla.po_line_id           = pla.po_line_id
    and pla.po_header_id          = pha.po_header_id
    and haouv.organization_id     = pha.org_id
    and pra.po_release_id     (+) = plla.po_release_id
    and msi.inventory_item_id (+) = pla.item_id
    and msi.org_id            (+) = pla.org_id
    and mufm.unit_of_measure  (+) = plla.unit_meas_lookup_code
    and mp1.organization_id   (+) = plla.ship_to_organization_id
    and mp2.organization_id   (+) = pda.destination_organization_id
  )
&l_ico_table
&l_ap_sla_table
&l_inv_sla_table
-- *******************************************
-- ** Main Query Starts Here
-- *******************************************
select
  ap_inv.ledger                         ledger
, ap_inv.operating_unit                 operating_unit
, ap_inv.vendor_name                    vendor_name
, ap_inv.vendor_num                     vendor_num
, ap_inv.vendor_site_code               vendor_site_code
, ap_inv.source                         invoice_source
, ap_inv.invoice_num                    invoice_num
, ap_inv.invoice_date                   invoice_date
, ap_inv.invoice_currency_code          currency
, ap_inv.invoice_amount                 invoice_amount
, ap_inv.total_tax_amount               invoice_tax_amoumt
, ap_inv.amount_paid                    invoice_paid_amount
, ap_inv.exchange_rate                  exchange_rate
, ap_inv.exchange_rate_type             exchange_rate_type
, ap_inv.exchange_date                  exchange_rate_date
, ap_inv.line_number                    invoice_line_num
&l_item_sel
, ap_inv.line_qty_invoiced              invoice_line_qty
, ap_inv.uom                            uom
, ap_inv.unit_price                     unit_price
, ap_inv.line_amount                    invoice_line_amount
, ap_inv.line_description               line_description
, ap_inv.dist_line_number               invoice_dist_line_num
, ap_inv.dist_qty_invoiced              invoice_dist_qty
, ap_inv.dist_amount                    invoice_dist_amount
, ap_inv.dist_accounting_date           accounting_date
, ap_inv.dist_period                    period_name
, ap_inv.dist_description               dist_description
, ap_inv.dist_match_type                match_type
, ap_inv.dist_matched_receipt_num       matched_receipt_num
--
&l_ap_sla_sel
&l_inv_sla_sel
--
, po.po_operating_unit
, po.po_requisition_num
, po.po_num
, po.po_release_num
, po.po_creation_date
, po.po_release_date
, po.po_line_shipment_num
, po.po_shipment_qty_ordered
, po.po_shipment_qty_accepted
, po.po_shipment_qty_rejected
, po.po_shipment_qty_billed
, po.po_uom
, po.po_dist_num
, po.po_dist_qty_ordered
, po.po_dist_qty_delivered
, po.po_dist_qty_billed
, po.po_ship_to_organization
, po.po_destination_organization
, po.po_dist_receipt_nums
--
&l_ico_sel
from
  ap_inv
, po
&l_ico_fr
&l_ap_sla_fr
&l_inv_sla_fr
where
  1=1
and po.po_distribution_id         (+) = ap_inv.po_distribution_id
&l_ico_join
&l_ap_sla_join
&l_inv_sla_join
order by 
  ap_inv.ledger
, ap_inv.operating_unit
, trunc(ap_inv.invoice_date)
, ap_inv.invoice_num
, ap_inv.line_number
, ap_inv.dist_line_number
Parameter Name SQL text Validation
Operating Unit 
ap_inv.operating_unit = :p_ou
 LOV
Invoice Number 
ap_inv.invoice_num = :p_invoice_num
 LOV
Ledger 
ap_inv.ledger = :p_ledger
 LOV
Supplier 
ap_inv.vendor_name = :p_vendor_name
 LOV
Invoice Source 
ap_inv.source = :p_inv_source
 LOV
Supplier Site 
ap_inv.vendor_site_code = :p_vendor_site
 LOV
Invoice Date From 
ap_inv.invoice_date >= :p_inv_date_from
 Date
Invoice Date To 
ap_inv.invoice_date < :p_inv_date_to + 1
 Date
Show AP SLA Accounting 
, ap_sla as -- sla accounting for ap invoice
  (
   select
     xdl.source_distribution_id_num_1
                                sla_ap_inv_dist_id
   , xal.ledger_id              sla_ledger_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
     xla_distribution_links       xdl
   , xla_ae_lines                 xal
   , gl_code_combinations_kfv     gcck
   where
       xdl.source_distribution_type      = 'AP_INV_DIST'
   and xal.ae_header_id                  = xdl.ae_header_id
   and xal.ae_line_num                   = xdl.ae_line_num
   and xal.accounting_class_code         = 'ITEM EXPENSE'
   and gcck.code_combination_id          = xal.code_combination_id
  )
 LOV Oracle
Show INV SLA Accounting 
, inv_sla 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.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
  )
, opm_sla 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.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.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
  )
 LOV Oracle
Show Intercompany Details 
, ico as
  ( select
      rctla.customer_trx_id
    , rctla.customer_trx_line_id
    , haouv.name                                             source_operating_unit
    , case rctla.interface_line_context
      when 'GLOBAL_PROCUREMENT'
      then 'Purchase Order'
      else case mmt.transaction_source_type_id
           when 8
           then 'Internal Sales Order'
           else 'Sales Order'
           end
      end                                                    source_document
    , nvl( ooha.ordered_date
         , pha.creation_date)                                source_document_date
    , nvl( to_char(ooha.order_number)
         , pha.segment1)                                     source_document_num
    , nvl( oe_order_misc_pub.get_concat_line_number(oola.line_id)
         , pla.line_num || '.' || plla.shipment_num)         source_document_line_num
    , nvl( oola.ordered_quantity
         , plla.quantity  - nvl(plla.quantity_cancelled,0))  source_document_qty
    , nvl( oola.order_quantity_uom
         , nvl( mufm.uom_code, plla.unit_meas_lookup_code))  source_document_uom
    , case ooha.order_source_id
      when 10 then ooha.orig_sys_document_ref
      else null
      end                                                    int_requisition_num
    --
    , 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
    , msik.concatenated_segments item
    , 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
    , mtl_material_transactions    mmt
    , oe_order_lines_all           oola
    , oe_order_headers_all         ooha
    , rcv_transactions             rt
    , rcv_shipment_headers         rsh
    , po_headers_all               pha
    , po_lines_all                 pla
    , po_line_locations_all        plla
    , mtl_system_items_kfv         msik
    , mtl_units_of_measure         mufm
    , hr_all_organization_units_vl haouv
    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 haouv.organization_id          = rcta.org_id
    and mmt.transaction_id             = to_number(rctla.interface_line_attribute7)
    and msik.organization_id           = mmt.organization_id
    and msik.inventory_item_id         = mmt.inventory_item_id
    and oola.line_id               (+) = decode(rctla.interface_line_context,'INTERCOMPANY',mmt.trx_source_line_id,-1)
    and ooha.header_id             (+) = oola.header_id
    and rt.transaction_id          (+) = mmt.rcv_transaction_id
    and rsh.shipment_header_id     (+) = rt.shipment_header_id
    and pha.po_header_id           (+) = rt.po_header_id
    and plla.line_location_id      (+) = rt.po_line_location_id
    and pla.po_line_id             (+) = rt.po_line_id
    and mufm.unit_of_measure       (+) = plla.unit_meas_lookup_code
  )
 LOV Oracle
Show AP SLA Accounting 
, ap_sla.sla_accounted_dr               ap_sla_accounted_dr
, ap_sla.sla_accounted_cr               ap_sla_accounted_cr
, ap_sla.sla_account                    ap_sla_account
Show AP SLA Accounting 
, ap_sla
Show AP SLA Accounting 
and ap_sla.sla_ap_inv_dist_id     (+) = ap_inv.invoice_distribution_id
and ap_sla.sla_ledger_id          (+) = ap_inv.dist_ledger_id
and ap_sla.sla_event_id           (+) = ap_inv.dist_accounting_event_id
Show INV SLA Accounting 
, nvl(inv_sla.sla_accounting_class
     ,opm_sla.sla_accounting_class)     inv_sla_accounting_class
, case when inv_sla.sla_event_id is not null
  then inv_sla.sla_accounted_dr
  else opm_sla.sla_accounted_dr
  end                                   inv_sla_accounted_dr
, case when inv_sla.sla_event_id is not null
  then inv_sla.sla_accounted_cr
  else opm_sla.sla_accounted_cr
  end                                   inv_sla_accounted_cr
, case when inv_sla.sla_event_id is not null
  then inv_sla.sla_account
  else opm_sla.sla_account
  end                                   inv_sla_account
Show INV SLA Accounting 
, inv_sla
, opm_sla
Show INV SLA Accounting 
and inv_sla.transaction_id        (+) = ap_inv.sla_inv_transaction_id
and opm_sla.transaction_id        (+) = ap_inv.sla_inv_transaction_id
Show Intercompany Details 
, ico.source_operating_unit                           ico_selling_procuring_ou
, ico.customer_name                                   ico_ar_customer_name
, ico.customer_num                                    ico_ar_customer_num
, ico.customer_location                               ico_ar_customer_location
, ico.trx_number                                      ico_ar_inv_num
, ico.trx_date                                        ico_ar_inv_date
, ico.invoice_currency_code                           ico_ar_inv_curr
, ico.status                                          ico_ar_inv_status
, ico.amount_due_original                             ico_ar_inv_total_amount
, ico.tax_original                                    ico_ar_inv_tax_amount
, ico.exchange_rate                                   ico_ar_inv_exchange_rate
, (select gdct.user_conversion_type from gl_daily_conversion_types gdct where ico.exchange_rate_type=gdct.conversion_type) ico_ar_inv_exchange_type
, ico.exchange_date                                   ico_ar_inv_exchange_date
, ico.line_number                                     ico_ar_inv_line_num
, ico.quantity_invoiced                               ico_ar_inv_line_qty
, ico.uom_code                                        ico_ar_inv_line_uom
, ico.unit_selling_price                              ico_ar_inv_line_unit_price
, ico.extended_amount                                 ico_ar_inv_line_amount
, ico.source_document                                 ico_source_document_type
, ico.source_document_date                            ico_source_document_date
, ico.source_document_num                             ico_source_document_num
, ico.source_document_line_num                        ico_source_document_line_num
, ico.source_document_qty                             ico_source_document_qty
, ico.source_document_uom                             ico_source_document_uom
, ico.int_requisition_num                             ico_source_int_requisition_num
Show Intercompany Details 
, ico
Show Intercompany Details 
and ico.customer_trx_id           (+) = ap_inv.customer_trx_id
and ico.customer_trx_line_id      (+) = ap_inv.customer_trx_line_id
Show Intercompany Details 
, nvl(nvl(ap_inv.item,po.item)
     ,ico.item)                         item
Show Intercompany Details 
, nvl(ap_inv.item,po.item)  item
Download
Related Blitz Reports
PO Receiving Transactions Pipeline PO Receiving Transactions Register PO Receipt Adjustments PO Purchase Price Variance PO Invoice Price Variance PO Headers and Lines AP Matching Detail - draft PO Receiving Exceptions PO Headers and Lines 11i
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: