INV Intercompany by AP Invoice Number Diagnostics

➜ Back to Library
Description
Run INV Intercompany by AP Invoice Number Diagnostics and other Oracle EBS reports with Blitz Report™ on our demo environment
select
10 seq,
           'Record Type'
|| '|' ||  'aia.source'
|| '|' ||  'aia.reference_1'
|| '|' ||  'aila.reference_1'
|| '|' ||  'aila.reference_2'
|| '|' ||  'aia.invoice_id'
|| '|' ||  'asup.vendor_name'
|| '|' ||  'asup.segment1'
|| '|' ||  'assa.vendor_site_code'
|| '|' ||  'aila.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'
|| '|' ||  'aila.line_type_lookup_code'
|| '|' ||  'aila.discarded_flag'
|| '|' ||  'aila.quantity_invoiced'
|| '|' ||  'aila.unit_meas_lookup_code'
|| '|' ||  'aila.unit_price'
|| '|' ||  'aila.amount' ico_data
from
  dual
union all
select
20 seq,
           'Record Type'
|| '|' || 'rctla.interface_line_context'
|| '|' || 'rctla.interface_line_attribute6'
|| '|' || 'rctla.interface_line_attribute7'
|| '|' || 'rctla.customer_trx_id'
|| '|' || 'rctla.customer_trx_line_id'
|| '|' || 'rctla.line_type'
|| '|' || 'hp.party_name'
|| '|' || 'hca.account_number'
|| '|' || 'hcsua.location'
|| '|' || 'apsa.trx_number'
|| '|' || 'apsa.trx_date'
|| '|' || 'apsa.invoice_currency_code'
|| '|' || 'apsa.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' ico_data
from
  dual
union all
select
30 seq,
           'Record Type'
|| '|' || 'mmt.transaction_id'
|| '|' || 'mmt.transfer_transaction_id'
|| '|' || 'mmt.parent_transaction_id'
|| '|' || 'mmt.transaction_source_type_id'
|| '|' || 'mmt.transaction_source_id'
|| '|' || 'mmt.transaction_source_name'
|| '|' || '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.source_code'
|| '|' || 'mmt.source_line_id'
|| '|' || 'mmt.trx_source_line_id'
|| '|' || 'mmt.logical_trx_type_code'
|| '|' || 'mmt.trx_flow_header_id'
|| '|' || 'mmt.logical_transactions_created'
|| '|' || 'mmt.logical_transaction'
|| '|' || 'Profile ICO for Internal Order'
|| '|' || 'OE Order Ref'
|| '|' || 'PO Order Ref'
|| '|' || 'mip1'
|| '|' || 'mip2'
|| '|' || 'mip3'  ico_data
from
  dual
union all
select
11 seq,
           'AP'
|| '|' ||  aia.source
|| '|' ||  aia.reference_1
|| '|' ||  aila.reference_1
|| '|' ||  aila.reference_2
|| '|' ||  aia.invoice_id
|| '|' ||  asup.vendor_name
|| '|' ||  asup.segment1
|| '|' ||  assa.vendor_site_code
|| '|' ||  aila.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
|| '|' ||  aila.line_type_lookup_code
|| '|' ||  aila.discarded_flag
|| '|' ||  aila.quantity_invoiced
|| '|' ||  aila.unit_meas_lookup_code
|| '|' ||  aila.unit_price
|| '|' ||  aila.amount ico_data
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.invoice_num                      = :invoice_num
union all
select
21 seq,
           'AR'
|| '|' || rctla.interface_line_context
|| '|' || rctla.interface_line_attribute6
|| '|' || rctla.interface_line_attribute7
|| '|' || rctla.customer_trx_id
|| '|' || rctla.customer_trx_line_id
|| '|' || rctla.line_type
|| '|' || hp.party_name
|| '|' || hca.account_number
|| '|' || hcsua.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
|| '|' || 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  ico_data
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
    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 (rctla.customer_trx_id,rctla.customer_trx_line_id) IN
    (select to_number(aia.reference_1),to_number(aila.reference_1)
     from   ap_invoices_all        aia
     ,      ap_invoice_lines_all   aila
     where
         aila.invoice_id                      = aia.invoice_id
         and aia.invoice_num                  = :invoice_num
         and aia.source                       = 'Intercompany'
         and aila.line_type_lookup_code       = 'ITEM'
    )
union all
select
31 seq,
           'INV'
|| '|' || mmt.transaction_id
|| '|' || mmt.transfer_transaction_id
|| '|' || mmt.parent_transaction_id
|| '|' || mmt.transaction_source_type_id
|| '|' || mmt.transaction_source_id
|| '|' || mmt.transaction_source_name
|| '|' || 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.source_code
|| '|' || mmt.source_line_id
|| '|' || mmt.trx_source_line_id
|| '|' || mmt.logical_trx_type_code
|| '|' || mmt.trx_flow_header_id
|| '|' || mmt.logical_transactions_created
|| '|' || mmt.logical_transaction
|| '|' || fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER')
|| '|' ||
  (select
     ooha.source_document_type_id || ':' || ooha.order_number || ':' || oe_order_misc_pub.get_concat_line_number(oola.line_id)
   from
     oe_order_lines_all   oola
   , oe_order_headers_all ooha
   where
       oola.header_id = ooha.header_id
   and oola.line_id   = mmt.trx_source_line_id
  )
|| '|' ||
  (select
     pha.segment1
   from
     po_headers_all pha
   where
       pha.po_header_id = mmt.transaction_source_id
  )
|| '|' ||
  (select
     count(*)
   from
     hr_organization_information  hoi
   , oe_order_lines_all            oola
   , mtl_intercompany_parameters   mip
   where oola.line_id               = mmt.trx_source_line_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
  )
|| '|' ||
  (select
     count(*)
   from
     hr_organization_information  hoi1
   , hr_organization_information  hoi2
   , mtl_intercompany_parameters   mip
   where
       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)
  )
|| '|' ||
  (select
     count(*) || ':' || min(mtfh.flow_type) || ':' || max(mtfh.flow_type)
   from
     hr_organization_information  hoi1
   , hr_organization_information  hoi2
   , mtl_transaction_flow_headers mtfh
   , mtl_intercompany_parameters   mip
   where
       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 mip.ship_organization_id             = to_number(hoi1.org_information3)
   and mip.sell_organization_id             = to_number(hoi2.org_information3)
  ) ico_data
from
  mtl_material_transactions    mmt
where
    mmt.transaction_id in
    ( select
        to_number(rctla.interface_line_attribute7)
      from
        ra_customer_trx_lines_all    rctla
      , ra_customer_trx_all          rcta
      , ar_payment_schedules_all     apsa
      where
          rcta.customer_trx_id           = rctla.customer_trx_id
      and apsa.customer_trx_id           = rctla.customer_trx_id
      and (rctla.customer_trx_id,rctla.customer_trx_line_id) IN
          (select to_number(aia.reference_1),to_number(aila.reference_1)
           from   ap_invoices_all        aia
           ,      ap_invoice_lines_all   aila
           where
               aila.invoice_id                      = aia.invoice_id
           and aia.invoice_num                  = :invoice_num
           and aia.source                       = 'Intercompany'
           and aila.line_type_lookup_code       = 'ITEM'
          )
    )
order by 1
Parameter Name SQL text Validation
AP Intercompany Invoice Number 
 
 Char
Download
Related Blitz Reports
INV Intercompany Invoice Reconciliation XXSJ Report VAT Register GL Account Analysis (Distributions) GL Account Analysis JA India - Open Transactions Report for Prior-GST (XML Publisher) GL Account Analysis (Distributions) 11g GL Account Analysis 11g AP Invoices and Lines AR Customers and Sites
Blitz Report™

Blitz Report™ provides multiple benefits: