Oracle EBS query to get AP Invoice SLA Details

Oracle EBS query to get AP Invoice SLA Details
Oracle apps query to find Payables invoice and XLA table details
ap_invoices_all and xla_ae_headers join
R12 payables and subledger accounting join

--AP invoice and SLA details
SELECT hou.name                                    operating_unit,
       aia.invoice_num,
       aia.invoice_currency_code,
       aia.creation_date,
       aia.invoice_date,
       aps.vendor_name,
       aia.invoice_amount,
       NVL (lk7.meaning, al.accounting_class_code) accounting_class,
       al.accounted_dr,
       al.accounted_cr,
       al.currency_code,
       al.description                              line_description,
       al.entered_dr,
       al.entered_cr,
       et.event_class_code,
       gcc.concatenated_segments                   invoice_line_account,
       ah.accounting_date,
       ah.period_name,
       ah.description                              header_description,
       ah.event_id,
       al.ae_header_id,
       al.entered_dr,
       al.entered_cr,
       al.code_combination_id,
       ah.*
  FROM apps.ap_invoices_all          aia,
       apps.ap_suppliers             aps,
       apps.gl_code_combinations_kfv gcc,
       apps.hr_operating_units       hou,
       apps.ap_supplier_sites_all    apss,
       apps.xla_ae_lines             al,
       apps.xla_ae_headers           ah,
       apps.xla_lookups              lk5,
       apps.xla_lookups              lk7,
       xla.xla_events                e,
       xla.xla_transaction_entities  te,
       xla.xla_event_types_tl        et,
       xla.xla_event_classes_tl      ec
 WHERE     aia.vendor_id = apss.vendor_id
       AND aps.vendor_id = apss.vendor_id
       AND aia.vendor_site_id = apss.vendor_site_id
       AND al.code_combination_id = gcc.code_combination_id
       AND hou.organization_id = aia.org_id
       AND aia.org_id = '101'
       AND aia.set_of_books_id = ah.ledger_id
       AND al.ae_header_id = ah.ae_header_id
       AND al.ledger_id = ah.ledger_id
       AND al.application_id = ah.application_id
       AND aia.invoice_num = '123456'    --Enter Invoice number
       AND NVL (NVL (al.accounted_cr, al.accounted_dr), 0) <> 0
       AND lk5.lookup_code = NVL (ah.funds_status_code, 'REQUIRED')
       AND lk5.lookup_type = 'XLA_FUNDS_STATUS'
       AND lk7.lookup_code(+) = al.accounting_class_code
       AND lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
       AND ec.application_id = et.application_id
       AND ec.entity_code = et.entity_code
       AND ec.event_class_code = et.event_class_code
       AND ec.language = USERENV ('LANG')
       AND et.application_id = ah.application_id
       AND et.entity_code = te.entity_code
       AND e.entity_id = te.entity_id
       AND e.application_id = te.application_id
       AND e.application_id = et.application_id
       AND e.event_type_code = et.event_type_code
       AND et.event_type_code = ah.event_type_code
       AND et.language = USERENV ('LANG')
       AND et.application_id = te.application_id
       AND te.application_id = ah.application_id
       AND aia.invoice_id = te.source_id_int_1
       AND te.entity_id = ah.entity_id
       AND e.event_id = ah.event_id
       AND e.application_id = ah.application_id
       AND ah.ae_header_id = al.ae_header_id
       AND ah.application_id = al.application_id;

Related posts:

• PTD and YTD balances query in Oracle R12
• Query to find all Expense report entry delegations
• Query to find vacation rules in Oracle R12
• View accounting query in oracle apps R12
• Query to get AR invoice SLA and GL details
• Accounting entries not transferred to the General Ledger
• Query to find all APIs of Oracle Apps modules
• R12 AR cash receipt SLA and GL details query
• Oracle apps AR Applications and SLA details
• Oracle EBS query to get AP Invoice SLA Details
• Oracle EBS query to get AR Invoice SLA Details
• Oracle AR Remit to address query
• Oracle Apps iExpense credit card details query
• Query to get customer tax registration details in R12
• Purchase Requisition Vertex debug xml query in Oracle EBS
• Purchase Order Vertex debug xml query in Oracle EBS
• AP Invoice Vertex debug xml query in Oracle EBS
• AR Invoice Vertex debug xml query in Oracle EBS
• Query to find AP invoice workflow item key in oracle apps
• Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
• Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
• Query to find Sales Orders that are shipped but not invoiced
• Query to get Customer Bank Account Details
• Query to get description of GL Code Combination
• Query to find Purchase Order and Invoice Details
• Query to find AP Invoices matched with receipts
• Query to find Supplier Bank Details
• Query to find Credit Memos on an AP invoice
• Query to find the AP invoices that are applied to multiple Purchase Orders
• AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
• Query to find shipset and fulfilment set

Upload your own post and refer it anywhere anytime: