AP Invoices with PO, Intercompany and SLA Details

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