INV Intercompany by AP Invoice Number Diagnostics

➜ Back to Library
Description
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 NameSQL textValidation
AP Intercompany Invoice Number
 
Char
Download
Related Blitz Reports
INV Intercompany Invoice Reconciliation GL Account Analysis GL Account Distribution Analysis XXSJ Report VAT Register JA India - Open Transactions Report for Prior-GST (XML Publisher) SAM: GL Account Analysis (Drilldown) (with inventory and WIP) GL Account Analysis (Drilldown) (with inventory and WIP) COPY OF: TB GL Account Analysis (Drilldown)v3 TB GL Account Analysis (Drilldown)v3
Blitz Report™