This page contains Oracle Apps Queries which are frequently used by developers or consultants in day to day life. I will be adding more queries over here. Suggestions are welcome.
APIs
Get the Status [AP_INVOICES_UTILITY_PKG]
Payable APIs/Interface
How payment information is stored in R12? ( Query to find payments in R12)
AP Invoice Insert ,Update and Delete API
Oracle AP Invoice APIs
Audit Table in AP
A Design Pattern for Oracle eBusiness Audit Trail Reports with XML Publisher
Voucher_num in ap_invoices_all
If Sequential Numbering for invoices is set, voucher number is stored in the field DOC_SEQUENCE_VALUE on AP_INVOICES_ALL. In the Invoice
Inquiry form, the system constructs a dynamic query depending on the value for profile option, Sequential Numbering. If profile option, Sequential Numbering, is 'Partial' or 'Always', the dynamic query looks at DOC_SEQUENCE_VALUE.
If profile option, Sequential Numbering is not set, the dynamic query looks at VOUCHER_NUM.
Invoice inquiry needs to be done based on the following logic
a. When the sequential numbering profile option set to "Partially Used/Always Used" then system search with the column DOC_SEQUENCE_VALUE.
b. When the sequential numbering profile option set to "Not Used" then system searches with the column VOUCHER_NUM
If Sequential Numbering for invoices is set, voucher number is stored in the field DOC_SEQUENCE_VALUE on AP_INVOICES_ALL. In the Invoice
Inquiry form, the system constructs a dynamic query depending on the value for profile option, Sequential Numbering. If profile option, Sequential Numbering, is 'Partial' or 'Always', the dynamic query looks at DOC_SEQUENCE_VALUE.
If profile option, Sequential Numbering is not set, the dynamic query looks at VOUCHER_NUM.Inquiry form, the system constructs a dynamic query depending on the value for profile option, Sequential Numbering. If profile option, Sequential Numbering, is 'Partial' or 'Always', the dynamic query looks at DOC_SEQUENCE_VALUE.
Invoice inquiry needs to be done based on the following logic
a. When the sequential numbering profile option set to "Partially Used/Always Used" then system search with the column DOC_SEQUENCE_VALUE.
b. When the sequential numbering profile option set to "Not Used" then system searches with the column VOUCHER_NUM
PAYABLE QUERIES
Invoice Inquiry (Functional)
UNPAID AP Invoice for Payments
XLA_TRAIL_BALANCES
EXEC MO_GLOBAL.INIT('SQLAP');
SELECT aia.org_id, hou.NAME operating_unit, aba.batch_name, aba.batch_date,
aia.invoice_type_lookup_code, pv.segment1 vendor_number,
pv.vendor_name, aia.invoice_num, aia.invoice_date, aia.gl_date,
aia.description, aia.payment_status_flag, aia.invoice_currency_code,
aia.exchange_rate, aia.exchange_rate_type, aia.invoice_amount,
aia.amount_paid, aia.base_amount functional_amount,
aia.external_bank_account_id, ieba.bank_account_number,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status_lookup_code,
NVL
((SELECT alc4.displayed_field
FROM ap_lookup_codes alc4
WHERE ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = alc4.lookup_code
AND alc4.lookup_type = ('NLS TRANSLATION')
AND ROWNUM = 1),
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
)
) approval_status_display,
aia.payment_method_code, aiph.document_number, aiph.check_date,
aiph.accounting_date, aiph.void, aiph.amount
FROM ap_invoices_all aia,
ap_batches_all aba,
hr_operating_units hou,
po_vendors pv,
iby_external_bank_accounts_v ieba,
ap_invoice_payment_history_v aiph
WHERE 1 = 1
AND aba.batch_id = aia.batch_id
AND hou.organization_id = aia.org_id
AND aia.vendor_id = pv.vendor_id
AND aia.external_bank_account_id = ieba.ext_bank_account_id(+)
AND aiph.invoice_id(+) = aia.invoice_id
AND aiph.org_id(+) = aia.org_id
-- AND aia.invoice_date BETWEEN :p_start_mon AND :p_end_mon
AND TO_CHAR (aia.invoice_date, 'MON-RRRR') = :p_start_mon
ORDER BY hou.NAME, aia.invoice_date, aba.batch_name, aia.invoice_num
EXEC MO_GLOBAL.INIT('SQLAP');
SELECT aia.org_id, hou.NAME operating_unit, aba.batch_name, aba.batch_date,
aia.invoice_type_lookup_code, pv.segment1 vendor_number,
pv.vendor_name, aia.invoice_num, aia.invoice_date, aia.gl_date,
aia.description, aia.payment_status_flag, aia.invoice_currency_code,
aia.exchange_rate, aia.exchange_rate_type, aia.invoice_amount,
aia.amount_paid, aia.base_amount functional_amount,
aia.external_bank_account_id, ieba.bank_account_number,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status_lookup_code,
NVL
((SELECT alc4.displayed_field
FROM ap_lookup_codes alc4
WHERE ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = alc4.lookup_code
AND alc4.lookup_type = ('NLS TRANSLATION')
AND ROWNUM = 1),
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
)
) approval_status_display,
aia.payment_method_code, aiph.document_number, aiph.check_date,
aiph.accounting_date, aiph.void, aiph.amount
FROM ap_invoices_all aia,
ap_batches_all aba,
hr_operating_units hou,
po_vendors pv,
iby_external_bank_accounts_v ieba,
ap_invoice_payment_history_v aiph
WHERE 1 = 1
AND aba.batch_id = aia.batch_id
AND hou.organization_id = aia.org_id
AND aia.vendor_id = pv.vendor_id
AND aia.external_bank_account_id = ieba.ext_bank_account_id(+)
AND aiph.invoice_id(+) = aia.invoice_id
AND aiph.org_id(+) = aia.org_id
-- AND aia.invoice_date BETWEEN :p_start_mon AND :p_end_mon
AND TO_CHAR (aia.invoice_date, 'MON-RRRR') = :p_start_mon
ORDER BY hou.NAME, aia.invoice_date, aba.batch_name, aia.invoice_num
SELECT aia.org_id, hou.NAME operating_unit, aba.batch_name, aba.batch_date,
aia.invoice_type_lookup_code, pv.segment1 vendor_number,
pv.vendor_name, aia.invoice_num, aia.invoice_date, aia.gl_date,
aia.description, aia.payment_status_flag, aia.invoice_currency_code,
aia.exchange_rate, aia.exchange_rate_type, aia.invoice_amount,
aia.amount_paid, aia.base_amount functional_amount,
aia.external_bank_account_id, ieba.bank_account_number,
ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) approval_status_lookup_code,
NVL
((SELECT alc4.displayed_field
FROM ap_lookup_codes alc4
WHERE ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
) = alc4.lookup_code
AND alc4.lookup_type = ('NLS TRANSLATION')
AND ROWNUM = 1),
INITCAP
(ap_invoices_pkg.get_approval_status
(aia.invoice_id,
aia.invoice_amount,
aia.payment_status_flag,
aia.invoice_type_lookup_code
)
)
) approval_status_display,
aia.payment_method_code, aiph.document_number, aiph.check_date,
aiph.accounting_date, aiph.void, aiph.amount
FROM ap_invoices_all aia,
ap_batches_all aba,
hr_operating_units hou,
po_vendors pv,
iby_external_bank_accounts_v ieba,
ap_invoice_payment_history_v aiph
WHERE 1 = 1
AND aba.batch_id = aia.batch_id
AND hou.organization_id = aia.org_id
AND aia.vendor_id = pv.vendor_id
AND aia.external_bank_account_id = ieba.ext_bank_account_id(+)
AND aiph.invoice_id(+) = aia.invoice_id
AND aiph.org_id(+) = aia.org_id
-- AND aia.invoice_date BETWEEN :p_start_mon AND :p_end_mon
AND TO_CHAR (aia.invoice_date, 'MON-RRRR') = :p_start_mon
ORDER BY hou.NAME, aia.invoice_date, aba.batch_name, aia.invoice_num
BANK QUERY
INTERNAL BANK QUERY
Payables Table: AP_SUPPLIER_SITES_ALL
AP Suppliers in R12
Migrating Reports to Release 12 - Co-Existing & Obsolete Table details:
Query to find the AP SUPPLIERS and HZ Parties -- Oracle Apps
Useful Supplier Queries
Supplier Bank Information
R12 Supplier Bank Accounts
Supplier Bank Account Details
Query to list Supplier Bank, Bank Branch, Bank Account information
R12 Supplier/Site/Banking Drilldown
Supplier Bank Query
INVOICE VOUCHER WITHOUT XLA TABLES
SELECT doc_sequence_value, accounting_date, head_desc, created_by,
accounting_event_id, document_number, currency_code, ae_line_number,
code_combination_id, curr, description, entered_dr, accounted_dr,
entered_cr, accounted_cr, ac, codedescription, reversal_flag
FROM (SELECT ap.doc_sequence_value, ap.gl_date accounting_date,
ap.description head_desc, ap.created_by,
NULL accounting_event_id,
TO_NUMBER (ap.doc_sequence_value) document_number,
ap.invoice_currency_code currency_code, NULL ae_line_number,
ap.accts_pay_code_combination_id code_combination_id,
'AED' curr, NULL description, NULL entered_dr,
NULL accounted_dr, ap.invoice_amount entered_cr,
NVL (ap.base_amount, ap.invoice_amount) accounted_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
NULL reversal_flag
FROM ap_invoices_all ap, gl_code_combinations gcc
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND fnd_date.string_to_date (ap.gl_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
-------------------
UNION
-------------------
SELECT ap.doc_sequence_value, aid.accounting_date,
ap.description head_desc, ap.created_by,
aid.accounting_event_id,
TO_NUMBER (ap.doc_sequence_value) document_number,
ap.invoice_currency_code currency_code,
aid.distribution_line_number ae_line_number,
aid.dist_code_combination_id code_combination_id, 'AED' curr,
aid.description, aid.amount entered_dr,
NVL (aid.base_amount, aid.amount) accounted_dr,
NULL entered_cr, NULL accounted_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
aid.reversal_flag
FROM ap_invoices_all ap,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid,
gl_code_combinations gcc
WHERE ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND api.line_number = aid.invoice_line_number
AND aid.dist_code_combination_id = gcc.code_combination_id
AND NVL (aid.reversal_flag, 'N') = 'N'
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR'))
------------------- WHERE doc_sequence_value = 8089
ORDER BY doc_sequence_value, accounted_dr, accounted_cr
-----------
PAYMENT VOUCHER WITHOUT XLA TABLES
SELECT check_id, invoice_id, invoice_num, status_lookup_code,
accounting_date, ACCOUNT, codedescription, description,
currency_code, entered_dr, accounted_dr, entered_cr, accounted_cr
FROM (SELECT ac.check_id, NULL invoice_id, NULL invoice_num,
ac.status_lookup_code, NULL accounting_date,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT,
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
ac.description, ac.currency_code, NULL entered_dr,
NULL accounted_dr, ac.amount entered_cr,
NVL (ac.base_amount, ac.amount) accounted_cr
FROM ap_checks_all ac,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau,
gl_code_combinations gcc
WHERE ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
AND cbau.bank_account_id = cba.bank_account_id(+)
AND cba.asset_code_combination_id = gcc.code_combination_id
--AND ac.check_id = 22026
-------------------
UNION
-------------------
SELECT ac.check_id, aip.invoice_id, ap.invoice_num,
ac.status_lookup_code, aip.accounting_date,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
ap.description, ac.currency_code, aip.amount entered_dr,
NVL (aip.invoice_base_amount, aip.amount) accounted_dr,
NULL entered_cr, NULL accounted_cr
FROM ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoices_all ap,
gl_code_combinations gcc
WHERE ac.check_id = aip.check_id
AND aip.invoice_id = ap.invoice_id
AND aip.accts_pay_code_combination_id = gcc.code_combination_id
-- AND aip.check_id = 22026
)
WHERE NVL (entered_dr, 0) + NVL (entered_cr, 0) <> 0
-- AND check_id = 12001 --22026
AND status_lookup_code NOT IN ('VOIDED')
ORDER BY check_id, invoice_num, accounted_dr, accounted_cr
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
SELECT doc_sequence_value, accounting_date, head_desc, created_by,
accounting_event_id, document_number, currency_code, ae_line_number,
code_combination_id, curr, description, entered_dr, accounted_dr,
entered_cr, accounted_cr, ac, codedescription, reversal_flag
FROM (SELECT ap.doc_sequence_value, ap.gl_date accounting_date,
ap.description head_desc, ap.created_by,
NULL accounting_event_id,
TO_NUMBER (ap.doc_sequence_value) document_number,
ap.invoice_currency_code currency_code, NULL ae_line_number,
ap.accts_pay_code_combination_id code_combination_id,
'AED' curr, NULL description, NULL entered_dr,
NULL accounted_dr, ap.invoice_amount entered_cr,
NVL (ap.base_amount, ap.invoice_amount) accounted_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
NULL reversal_flag
FROM ap_invoices_all ap, gl_code_combinations gcc
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND fnd_date.string_to_date (ap.gl_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
-------------------
UNION
-------------------
SELECT ap.doc_sequence_value, aid.accounting_date,
ap.description head_desc, ap.created_by,
aid.accounting_event_id,
TO_NUMBER (ap.doc_sequence_value) document_number,
ap.invoice_currency_code currency_code,
aid.distribution_line_number ae_line_number,
aid.dist_code_combination_id code_combination_id, 'AED' curr,
aid.description, aid.amount entered_dr,
NVL (aid.base_amount, aid.amount) accounted_dr,
NULL entered_cr, NULL accounted_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
aid.reversal_flag
FROM ap_invoices_all ap,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid,
gl_code_combinations gcc
WHERE ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND api.line_number = aid.invoice_line_number
AND aid.dist_code_combination_id = gcc.code_combination_id
AND NVL (aid.reversal_flag, 'N') = 'N'
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR'))
------------------- WHERE doc_sequence_value = 8089
ORDER BY doc_sequence_value, accounted_dr, accounted_cr
-----------
PAYMENT VOUCHER WITHOUT XLA TABLES
SELECT check_id, invoice_id, invoice_num, status_lookup_code,
accounting_date, ACCOUNT, codedescription, description,
currency_code, entered_dr, accounted_dr, entered_cr, accounted_cr
FROM (SELECT ac.check_id, NULL invoice_id, NULL invoice_num,
ac.status_lookup_code, NULL accounting_date,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT,
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
ac.description, ac.currency_code, NULL entered_dr,
NULL accounted_dr, ac.amount entered_cr,
NVL (ac.base_amount, ac.amount) accounted_cr
FROM ap_checks_all ac,
ce_bank_accounts cba,
ce_bank_acct_uses_all cbau,
gl_code_combinations gcc
WHERE ac.ce_bank_acct_use_id = cbau.bank_acct_use_id(+)
AND cbau.bank_account_id = cba.bank_account_id(+)
AND cba.asset_code_combination_id = gcc.code_combination_id
--AND ac.check_id = 22026
-------------------
UNION
-------------------
SELECT ac.check_id, aip.invoice_id, ap.invoice_num,
ac.status_lookup_code, aip.accounting_date,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
ap.description, ac.currency_code, aip.amount entered_dr,
NVL (aip.invoice_base_amount, aip.amount) accounted_dr,
NULL entered_cr, NULL accounted_cr
FROM ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoices_all ap,
gl_code_combinations gcc
WHERE ac.check_id = aip.check_id
AND aip.invoice_id = ap.invoice_id
AND aip.accts_pay_code_combination_id = gcc.code_combination_id
-- AND aip.check_id = 22026
)
WHERE NVL (entered_dr, 0) + NVL (entered_cr, 0) <> 0
-- AND check_id = 12001 --22026
AND status_lookup_code NOT IN ('VOIDED')
ORDER BY check_id, invoice_num, accounted_dr, accounted_cr
Query to Link AP Invoices, Payments, GL and SLA Tables: R12 AP GL XLA
XLA Query to AP_INVOICES, AP_PAYMENTS and Others
Query-1
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gcc.concatenated_segments gl_account, gjh.default_effective_date,
je_source, gjh.je_category,
--
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gled.ledger_id, gled.NAME ledger_name, gjh.je_header_id,
gjl.je_line_num, gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
---
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Payable Records)-------------
AND gjh.je_source = 'Payables'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
Query-2
/* Formatted on 2017/10/09 11:18 (Formatter Plus v4.8.8) */
----AP_PAYMENTS--------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
cpd.payment_doc_category payment_document_name,
aca.bank_account_name internal_bank_acc_name,
cbb.country internet_bank_country, cbb.bank_branch_type,
cbb.bank_branch_name internal_bank_branch_name,
cbb.bank_branch_number internal_branch_number,
cbb.bank_name internal_bank_name, aca.vendor_name supplier_name,
aca.vendor_site_code, --ass.country,
asp.vendor_type_lookup_code supplier_category_site_level,
aca.external_bank_account_id, ieba.bank_name supplier_bank_name,
ieba.bank_branch_name supplier_branch_name,
ieba.bank_account_name supplier_bank_account_name,
ieba.bank_account_number supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
ap_checks_all aca,
ce_payment_documents cpd,
ce_bank_accounts cba,
cefv_bank_branches cbb,
ap_suppliers asp,
iby_ext_bank_accounts_v ieba
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Payable with 'Payments', 'Reconciled Payments' Records)-------------
AND gjh.je_source = 'Payables'
AND gjh.je_category IN ('Payments', 'Reconciled Payments')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.balance_type_code = 'A'
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.application_id = 200
-------------(Ap_checks_all , ap_invoice_payments link)
AND TO_CHAR (aca.check_id) = NVL (xte.source_id_int_1, (-99))
-- AND aca.check_id = apha.check_id
-- AND apha.accounting_event_id = xah.event_id
AND cpd.payment_document_id(+) = aca.payment_document_id
AND cpd.internal_bank_account_id = cba.bank_account_id(+)
AND cba.bank_branch_id = cbb.bank_branch_id(+)
---
AND aca.vendor_id = asp.vendor_id(+)
--
AND aca.external_bank_account_id = ieba.ext_bank_account_id(+)
-- AND aca.check_id = 2123058
-----------------------------------------------------------------------------
UNION
-----------------------------------------------------------------------------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
cpd.payment_doc_category payment_document_name,
aca.bank_account_name internal_bank_acc_name,
cbb.country internet_bank_country, cbb.bank_branch_type,
cbb.bank_branch_name internal_bank_branch_name,
cbb.bank_branch_number internal_branch_number,
cbb.bank_name internal_bank_name, aca.vendor_name supplier_name,
aca.vendor_site_code, --ass.country,
asp.vendor_type_lookup_code supplier_category_site_level,
aca.external_bank_account_id, ieba.bank_name supplier_bank_name,
ieba.bank_branch_name supplier_branch_name,
ieba.bank_account_name supplier_bank_account_name,
ieba.bank_account_number supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_distribution_links xdl,
ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia,
ap_invoice_payments_all aipa,
----
iby_docs_payable_all idpa,
iby_payments_all ip,
ap_checks_all aca,
ce_payment_documents cpd,
ce_bank_accounts cba,
cefv_bank_branches cbb,
ap_suppliers asp,
iby_ext_bank_accounts_v ieba
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Payable with 'Payments', 'Reconciled Payments' Records)-------------
AND gjh.je_source = 'Payables'
-- AND gjh.je_category IN ('Payments', 'Reconciled Payments')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.balance_type_code = 'A'
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
--------------------(actual)
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
--
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND aida.accounting_event_id = xdl.event_id
--
AND aila.line_number = aida.invoice_line_number
AND aila.invoice_id = aida.invoice_id
--
AND aia.invoice_id = aila.invoice_id
--
AND aipa.invoice_id(+) = aia.invoice_id
------------------
AND idpa.calling_app_doc_ref_number(+) = aia.invoice_num
AND idpa.calling_app_doc_unique_ref2(+) = aia.invoice_id
--
AND idpa.payment_id = ip.payment_id(+)
--
AND aca.payment_id(+) = ip.payment_id
AND cpd.payment_document_id(+) = aca.payment_document_id
AND cpd.internal_bank_account_id = cba.bank_account_id(+)
AND cba.bank_branch_id = cbb.bank_branch_id(+)
AND aca.vendor_id = asp.vendor_id(+)
--
AND aca.external_bank_account_id = ieba.ext_bank_account_id(+)
--------------------------------------------------------------------------
UNION
--------------------------------------------------------------------------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
NULL payment_document_name, NULL internal_bank_acc_name,
NULL internet_bank_country, NULL bank_branch_type,
NULL internal_bank_branch_name, NULL internal_branch_number,
NULL internal_bank_name, NULL supplier_name, NULL vendor_site_code,
--ass.country,
NULL supplier_category_site_level, NULL external_bank_account_id,
NULL supplier_bank_name, NULL supplier_branch_name,
NULL supplier_bank_account_name, NULL supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
---
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Payable Records)-------------
AND gjh.je_source = 'Payables'
AND gjh.je_category IN ('Other', 'Manual')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gcc.concatenated_segments gl_account, gjh.default_effective_date,
je_source, gjh.je_category,
--
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gled.ledger_id, gled.NAME ledger_name, gjh.je_header_id,
gjl.je_line_num, gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
---
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Payable Records)-------------
AND gjh.je_source = 'Payables'
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
Query-2
/* Formatted on 2017/10/09 11:18 (Formatter Plus v4.8.8) */
----AP_PAYMENTS--------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
cpd.payment_doc_category payment_document_name,
aca.bank_account_name internal_bank_acc_name,
cbb.country internet_bank_country, cbb.bank_branch_type,
cbb.bank_branch_name internal_bank_branch_name,
cbb.bank_branch_number internal_branch_number,
cbb.bank_name internal_bank_name, aca.vendor_name supplier_name,
aca.vendor_site_code, --ass.country,
asp.vendor_type_lookup_code supplier_category_site_level,
aca.external_bank_account_id, ieba.bank_name supplier_bank_name,
ieba.bank_branch_name supplier_branch_name,
ieba.bank_account_name supplier_bank_account_name,
ieba.bank_account_number supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte,
ap_checks_all aca,
ce_payment_documents cpd,
ce_bank_accounts cba,
cefv_bank_branches cbb,
ap_suppliers asp,
iby_ext_bank_accounts_v ieba
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Payable with 'Payments', 'Reconciled Payments' Records)-------------
AND gjh.je_source = 'Payables'
AND gjh.je_category IN ('Payments', 'Reconciled Payments')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.balance_type_code = 'A'
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.application_id = 200
-------------(Ap_checks_all , ap_invoice_payments link)
AND TO_CHAR (aca.check_id) = NVL (xte.source_id_int_1, (-99))
-- AND aca.check_id = apha.check_id
-- AND apha.accounting_event_id = xah.event_id
AND cpd.payment_document_id(+) = aca.payment_document_id
AND cpd.internal_bank_account_id = cba.bank_account_id(+)
AND cba.bank_branch_id = cbb.bank_branch_id(+)
---
AND aca.vendor_id = asp.vendor_id(+)
--
AND aca.external_bank_account_id = ieba.ext_bank_account_id(+)
-- AND aca.check_id = 2123058
-----------------------------------------------------------------------------
UNION
-----------------------------------------------------------------------------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
cpd.payment_doc_category payment_document_name,
aca.bank_account_name internal_bank_acc_name,
cbb.country internet_bank_country, cbb.bank_branch_type,
cbb.bank_branch_name internal_bank_branch_name,
cbb.bank_branch_number internal_branch_number,
cbb.bank_name internal_bank_name, aca.vendor_name supplier_name,
aca.vendor_site_code, --ass.country,
asp.vendor_type_lookup_code supplier_category_site_level,
aca.external_bank_account_id, ieba.bank_name supplier_bank_name,
ieba.bank_branch_name supplier_branch_name,
ieba.bank_account_name supplier_bank_account_name,
ieba.bank_account_number supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_distribution_links xdl,
ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia,
ap_invoice_payments_all aipa,
----
iby_docs_payable_all idpa,
iby_payments_all ip,
ap_checks_all aca,
ce_payment_documents cpd,
ce_bank_accounts cba,
cefv_bank_branches cbb,
ap_suppliers asp,
iby_ext_bank_accounts_v ieba
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Payable with 'Payments', 'Reconciled Payments' Records)-------------
AND gjh.je_source = 'Payables'
-- AND gjh.je_category IN ('Payments', 'Reconciled Payments')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
AND gjl.status = 'P'
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
AND xah.balance_type_code = 'A'
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
--------------------(actual)
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
--
AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
AND aida.accounting_event_id = xdl.event_id
--
AND aila.line_number = aida.invoice_line_number
AND aila.invoice_id = aida.invoice_id
--
AND aia.invoice_id = aila.invoice_id
--
AND aipa.invoice_id(+) = aia.invoice_id
------------------
AND idpa.calling_app_doc_ref_number(+) = aia.invoice_num
AND idpa.calling_app_doc_unique_ref2(+) = aia.invoice_id
--
AND idpa.payment_id = ip.payment_id(+)
--
AND aca.payment_id(+) = ip.payment_id
AND cpd.payment_document_id(+) = aca.payment_document_id
AND cpd.internal_bank_account_id = cba.bank_account_id(+)
AND cba.bank_branch_id = cbb.bank_branch_id(+)
AND aca.vendor_id = asp.vendor_id(+)
--
AND aca.external_bank_account_id = ieba.ext_bank_account_id(+)
--------------------------------------------------------------------------
UNION
--------------------------------------------------------------------------
SELECT /*+ INDEX (gjh GL_JE_HEADERS_U1) INDEX(gjl GL_JE_LINES_U1) */
gled.NAME ledger_name,
gcc_gjl.concatenated_segments source_gl_combination,
gl_flexfields_pkg.get_concat_description
(gcc_gjl.chart_of_accounts_id,
gcc_gjl.code_combination_id
) source_acc_description,
NULL payment_document_name, NULL internal_bank_acc_name,
NULL internet_bank_country, NULL bank_branch_type,
NULL internal_bank_branch_name, NULL internal_branch_number,
NULL internal_bank_name, NULL supplier_name, NULL vendor_site_code,
--ass.country,
NULL supplier_category_site_level, NULL external_bank_account_id,
NULL supplier_bank_name, NULL supplier_branch_name,
NULL supplier_bank_account_name, NULL supplier_bank_account_number,
gjh.currency_code transaction_currency, je_source, gjh.je_category,
gjh.currency_code trasnaction_currency_code, gjl.entered_dr,
gjl.entered_cr,
(NVL (gjl.entered_dr, 0) - NVL (gjl.entered_cr, 0)) entered_amount,
gjl.accounted_dr, gjl.accounted_cr,
(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0)
) accounted_amount,
gjb.NAME batch_name, gjh.NAME journal_name
FROM gl.gl_je_headers gjh,
gl.gl_je_lines gjl,
gl_je_batches gjb,
gl_ledgers gled,
gl_periods gp,
gl_code_combinations_kfv gcc_gjl,
---
gl.gl_import_references ir,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
xla.xla_events xe,
xla.xla_transaction_entities xte
---
WHERE 1 = 1
-------------(Currency Code 'STAT' will not display from JL_JE_HEADERS)
AND gjh.currency_code NOT IN ('STAT')
-------------(Only Manual and Others Records will display)
-- AND gjh.je_source IN ('Manual', 'Others')
-------------(Only Posted Flag = 'P FROM GL_JE_HEADERS)
AND gjh.status = 'P'
-------------(Only Actual Flag = 'A' FROM JL_GE_HEADERS)
AND gjh.actual_flag = 'A'
-------------(Only Payable Records)-------------
AND gjh.je_source = 'Payables'
AND gjh.je_category IN ('Other', 'Manual')
-------------(link between gl_ledger and ge_je_headers ))
AND gled.ledger_id = gjh.ledger_id
-------------(link between gl_je_batches and jl_je_headers - Mandatory)-----------
AND gjb.je_batch_id(+) = gjh.je_batch_id
-------------(link between GL_JE_HEADERS and GL_PERIODS)
AND gp.period_name = gjh.period_name
-------------(link between GL_LEDGERS and GL_PERIODS)
AND gp.period_set_name = gled.period_set_name
-------------(link between gl_je_headers and gl_je_lines)
AND gjh.je_header_id = gjl.je_header_id
-------------(link between gl_je_lines and gl_code_combinations_kfv)
AND gjl.code_combination_id = gcc_gjl.code_combination_id
-------------(Condition of gjh.ledger_id - Mandatory)-------------
AND gjh.ledger_id = NVL (:p_ledger_id, gjh.ledger_id)
-------------(segment3 between p_from_account and :p_to_account )--(p_from_account and :p_to_account --Optional)--------
AND (TO_NUMBER (gcc_gjl.segment3) BETWEEN :p_from_account AND :p_to_account)
-------------(Condition of Period - Mandatory)-----
AND gp.period_name = :p_period_name
-------------(gl.gl_import_references ir, gl.gl_je_lines gjl)
AND ir.je_header_id = gjh.je_header_id
AND ir.je_line_num = gjl.je_line_num
-------------(link between xla_ae_lines,gl_import_references )
AND xal.gl_sl_link_id = ir.gl_sl_link_id
AND xal.gl_sl_link_table = ir.gl_sl_link_table
-------------(link between xla_ae_headers, xla_ae_lines)
AND xah.ae_header_id = xal.ae_header_id
AND xah.application_id = xal.application_id
-------------(Only balance_type_code = 'A' FROM XLA_AE_HEADERS)
AND xah.balance_type_code = 'A'
-----------(link between xla_ae_header and xla_events)---------
AND xah.event_id = xe.event_id
AND xah.application_id = xe.application_id
-------------(link between xla_events and xla_entities_transaction)
AND xe.entity_id = xte.entity_id
AND xe.application_id = xte.application_id
XLA Queries to AP Invoices
Find AP invoice Data in AP, XLA and GL tables by using Invoice_ID
Subledger-Ledger Linkage (Accounts Payables)
AP to GL Link with different Sub ledger source
Payment data in Sub-Ledger Accounting (XLA) - R12
How Payable Invoices Data is stored in Sub-ledger Accounting of Oracle Apps R12? (Oracle Sub-Ledger Accounting, Oracle Payables)
AP TO GL - PREPAY QUERY (Good One)
Query - 1
SELECT doc_sequence_value, ae_header_id, document_type, accounting_date,
gl_transfer_flag, head_desc, created_by, event_type, document_number,
currency_code, ae_line_id, ae_line_number, ae_line_type_code,
code_combination_id, curr, description,
SUM (unrounded_accounted_dr) accounted_dr,
SUM (unrounded_accounted_cr) accounted_cr,
SUM (unrounded_entered_dr) entered_dr,
SUM (unrounded_entered_cr) entered_cr, ac, codedescription, reversal_flag
FROM (SELECT xah.doc_sequence_value, xah.ae_header_id,
xah.je_category_name document_type, xah.accounting_date,
xah.gl_transfer_status_code gl_transfer_flag,
xah.description head_desc, xah.created_by,
xah.event_type_code event_type,
TO_NUMBER (xah.doc_sequence_value) document_number,
xal.currency_code, xal.gl_sl_link_id ae_line_id,
xal.ae_line_num ae_line_number,
xal.accounting_class_code ae_line_type_code,
xal.code_combination_id, xal.currency_code curr,
xal.accounted_dr, xal.accounted_cr, xal.description,
xal.entered_dr, xal.entered_cr, xdl.unrounded_accounted_dr,
xdl.unrounded_accounted_cr, xdl.unrounded_entered_dr,
xdl.unrounded_entered_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2')
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
aid.reversal_flag
FROM xla_ae_headers xah,
xla_distribution_links xdl,
ap_invoice_distributions_all aid,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE xdl.ae_header_id = xah.ae_header_id
---
AND xdl.source_distribution_id_num_1 =
aid.invoice_distribution_id
AND aid.accounting_event_id = xdl.event_id
---
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
---
AND xal.code_combination_id = gcc.code_combination_id
--
AND fnd_date.string_to_date (xah.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
AND aid.reversal_flag = 'N'
AND xah.doc_sequence_value = 8090 ------ test parameter
)
GROUP BY doc_sequence_value,
ae_header_id,
document_type,
accounting_date,
gl_transfer_flag,
head_desc,
created_by,
event_type,
document_number,
currency_code,
ae_line_id,
ae_line_number,
ae_line_type_code,
code_combination_id,
curr,
description,
ac,
codedescription,
reversal_flag
ORDER BY doc_sequence_value, document_type, accounted_dr, accounted_cr
--------------------------------------------------------------------
Query - 2
SELECT aid.accounting_event_id, xdl.event_id, xdl.*
FROM xla_ae_headers xah,
xla_distribution_links xdl,
ap_invoice_distributions aid,
xla_ae_lines xal WHERE xdl.ae_header_id = xah.ae_header_id
---
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND aid.accounting_event_id = xdl.event_id
---
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
---
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
AND aid.reversal_flag = 'N'
AND xah.doc_sequence_value = :p_doc_sequence_value;
---------------------------------------------------------------------------------
Query - 3
SELECT xld.*
FROM xla_ae_headers xeh, xla_distribution_links xld
WHERE xld.ae_header_id = xeh.ae_header_id
AND xld.source_distribution_type = 'AP_INV_DIST'
AND xeh.balance_type_code = 'A'
AND xeh.je_category_name = 'Purchase Invoices'
AND xeh.doc_sequence_value = :p_doc_sequence_value;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query - 4 SELECT DISTINCT ai.invoice_id, xel.*,
fnd_flex_ext.get_segs ('SQLGL',
'GL#',
'50577',
xel.code_combination_id
) "Account"
FROM xla_ae_lines xel,
xla_ae_headers xeh,
ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xel.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND xeh.je_category_name = 'Purchase Invoices'
AND xeh.balance_type_code = 'A'
AND ai.invoice_id = :p_invoice_id
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xeh.entity_id
ORDER BY xel.ae_header_id, xel.ae_line_num ASC;
---------------------------------------------------------------------
SELECT ah.ae_header_id, ah.je_category_name document_type,
ah.accounting_date, ah.gl_transfer_status_code gl_transfer_flag,
ah.description head_desc, ah.created_by,
ah.event_type_code event_type,
TO_NUMBER (ah.doc_sequence_value) document_number, al.currency_code,
al.gl_sl_link_id ae_line_id, al.ae_line_num ae_line_number,
al.accounting_class_code ae_line_type_code, al.code_combination_id,
al.currency_code curr, al.accounted_dr, al.accounted_cr,
al.description, al.entered_dr, al.entered_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description (101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment3, 'SEGMENT3')
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment4, 'SEGMENT4')
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment5, 'SEGMENT5')
codedescription,
ad.reversal_flag
FROM apps.gl_je_headers h,
apps.gl_je_lines jl,
apps.gl_code_combinations c,
apps.gl_import_references r,
apps.xla_ae_lines al,
apps.xla_ae_headers ah,
apps.xla_distribution_links l,
apps.ap_invoices_all i,
apps.ap_invoice_distributions_all ad,
apps.ap_suppliers s,
apps.xla_events e,
apps.xla_transaction_entities te,
----
gl_code_combinations gcc ----
WHERE ah.doc_sequence_value = 8007
-- AND c.code_combination_id = 6429 AND jl.description != 'GB VAT - STANDARD TAX'
AND ad.line_type_lookup_code = 'ITEM'
AND jl.je_header_id = h.je_header_id
AND jl.code_combination_id = c.code_combination_id
AND al.gl_sl_link_id = r.gl_sl_link_id
AND al.ae_header_id = ah.ae_header_id
AND al.application_id = ah.application_id
AND ah.application_id = e.application_id
AND ah.event_id = e.event_id
AND e.application_id = te.application_id(+)
AND e.entity_id = te.entity_id(+)
AND r.je_header_id = jl.je_header_id
AND r.je_line_num = jl.je_line_num
AND l.ae_header_id = al.ae_header_id
AND l.ae_line_num = al.ae_line_num
AND l.applied_to_source_id_num_1 = i.invoice_id
AND l.source_distribution_id_num_1 = ad.invoice_distribution_id
AND ad.invoice_id = i.invoice_id
AND i.vendor_id = s.vendor_id
------------------ AND al.code_combination_id = gcc.code_combination_id
AND fnd_date.string_to_date (ah.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND l.source_distribution_type = 'AP_INV_DIST'
AND ah.balance_type_code = 'A'
AND ah.je_category_name = 'Purchase Invoices'
AND ad.reversal_flag = 'N'
------------------ ORDER BY ah.doc_sequence_value,
ah.je_category_name,
al.accounted_dr,
al.accounted_cr
---------------------------------------------------------------------------------
Query -5
---Query on the Account View from the Invoice Form
SELECT *
FROM (SELECT /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0') opt_param('hash_join_enabled','false') */
l.code_combination_id,
fnd_flex_ext.get_segs ('SQLGL',
'GL#',
gl.chart_of_accounts_id,
l.code_combination_id
) ACCOUNT,
xla_oa_functions_pkg.get_ccid_description
(gl.chart_of_accounts_id,
l.code_combination_id
) account_description,
l.accounting_class_code,
NVL (lk7.meaning, l.accounting_class_code) accounting_class,
NULL completion_acct_seq_name, h.completion_acct_seq_value,
h.completion_acct_seq_version_id completion_acct_seq_ver_id,
NULL completion_acct_seq_ver_name, h.balance_type_code,
NULL balance_type, h.budget_version_id, bud.budget_name,
h.completed_date, l.accounted_dr, l.accounted_cr,
l.created_by, NULL created_by_user, l.creation_date,
l.currency_code, l.description line_description,
h.doc_sequence_id, NULL doc_sequence_name,
h.doc_sequence_value, l.encumbrance_type_id,
NULL encumbrance_type, l.entered_dr, l.entered_cr,
et.event_class_code, ec.NAME event_class,
ue.user_name event_created_by_user,
e.creation_date event_creation_date, e.event_date,
et.NAME event_type, e.event_number,
l.currency_conversion_date, l.currency_conversion_rate,
l.currency_conversion_type, NULL user_conversion_type,
DECODE (gl.enable_budgetary_control_flag,
'N', NULL,
lk5.meaning
) funds_status,
NVL
(h.funds_status_code,
DECODE (gl.enable_budgetary_control_flag,
'REQUIRED', NULL
)
) funds_status_code,
h.accounting_date, l.gl_sl_link_id, l.gl_sl_link_table,
h.period_name, h.description header_description,
h.je_category_name, NULL user_je_cat_name, h.ae_header_id,
NULL journal_entry_name, gl.currency_code ledger_currency,
h.ledger_id, gl.NAME ledger_name, te.legal_entity_id,
le.NAME legal_entity_name,
le.legal_entity_identifier legal_entity_taxpayer_id,
l.ae_line_num, h.product_rule_type_code,
NULL prod_rule_type_dsp, h.product_rule_code,
NULL prod_rule_name,
h.product_rule_version prod_rule_version, l.jgzz_recon_ref,
h.reference_date, NULL close_acct_seq_name,
h.close_acct_seq_value,
h.close_acct_seq_version_id close_acct_seq_ver_id,
NULL close_acct_seq_ver_name, l.statistical_amount,
h.event_type_code, h.accounting_entry_status_code,
NULL ae_status, h.accounting_entry_type_code, NULL ae_type,
DECODE (l.party_type_code, 'C', l.party_id) customer_id,
NULL customer_name, NULL customer_number,
NULL customer_taxpayer_id,
DECODE (l.party_type_code,
'C', l.party_site_id
) customer_site_id,
NULL customer_site_name, l.party_type_code,
NULL party_type_dsp, h.gl_transfer_date,
h.gl_transfer_status_code, NULL gl_transfer_status,
l.ussgl_transaction_code, e.created_by event_created_by,
te.transaction_number, et.entity_code, NULL batch_name,
h.application_id, app.application_name, te.source_id_int_1,
te.source_id_int_2, te.source_id_int_3, te.source_id_int_4,
te.source_id_char_1, te.source_id_char_2,
te.source_id_char_3, te.source_id_char_4, h.event_id,
te.security_id_int_1, te.security_id_int_2,
te.security_id_int_3, te.security_id_char_1,
te.security_id_char_2, te.security_id_char_3,
te.valuation_method, NULL supplier_name,
NULL supplier_number, NULL supplier_taxpayer_id,
NULL supplier_site_name,
DECODE (l.party_type_code, 'S', l.party_id) supplier_id,
DECODE (l.party_type_code,
'S', l.party_site_id
) supplier_site_id,
'N' populate_hdr_flag, 'N' populate_line_flag,
'N' populate_gl_flag, 'N' populate_seq_flag,
'N' populate_supp_flag, 'N' populate_cust_flag,
NULL balancing_seg, NULL management_seg,
NULL cost_center_seg, NULL natural_acct_seg,
h.parent_ae_header_id, h.parent_ae_line_num,
te.ledger_id trx_ledger_id, l.displayed_line_number,
h.upg_batch_id
FROM xla_ae_headers h,
xla_ae_lines l,
xla_events e,
xla_transaction_entities te,
xla_gl_ledgers_v gl,
gl_budget_versions bud,
xle_entity_profiles le,
fnd_user ue,
fnd_application_vl app,
xla_event_types_tl et,
xla_event_classes_tl ec,
xla_lookups lk5,
xla_lookups lk7 WHERE gl.ledger_id = h.ledger_id
AND bud.budget_version_id(+) = h.budget_version_id
AND le.legal_entity_id(+) = te.legal_entity_id
AND ue.user_id = e.created_by
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 = h.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 = h.event_type_code
AND et.LANGUAGE = USERENV ('LANG')
AND app.application_id = h.application_id
AND et.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND lk5.lookup_code = NVL (h.funds_status_code, 'REQUIRED')
AND lk5.lookup_type = 'XLA_FUNDS_STATUS'
AND lk7.lookup_code(+) = l.accounting_class_code
AND lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
AND ( NVL (NVL (accounted_cr, accounted_dr), 0) <> 0
OR fnd_profile.VALUE ('XLA_SHOW_ZERO_AMT_JRNL') = 'Y'
)) qrslt
WHERE ( application_id = :1
AND entity_code = :2
AND trx_ledger_id = :3
AND NVL (source_id_int_1, -99) = :4
AND balance_type_code = 'A'
)
ORDER BY ACCOUNT
XLA Queries to AP Payments
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
R12: Join GL to AP via XLA Tables
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
R12: Join GL to AP via XLA Tables
Query 1
SELECT aipa.reversal_flag, h.event_id, l.ae_line_num, h.ae_header_id,
h.je_category_name, h.accounting_date, h.gl_transfer_status_code,
h.event_type_code, h.doc_sequence_id, l.currency_code,
l.accounting_class_code, l.code_combination_id,
l.entered_dr org_entered_dr, l.accounted_dr org_accounted_dr,
l.entered_cr org_entered_cr, l.accounted_cr org_accounted_cr,
aipa.amount amt_paid,
DECODE (aipa.amount,
l.entered_dr, l.entered_dr,
(l.entered_dr - aipa.amount)
) entered_dr,
DECODE (aipa.amount,
l.accounted_dr, l.entered_dr,
(l.accounted_dr - aipa.amount)
) accounted_dr,
DECODE (aipa.amount,
l.entered_cr, l.entered_cr,
(l.entered_cr - aipa.amount)
) entered_cr,
DECODE (aipa.amount,
l.accounted_cr, l.entered_cr,
(l.accounted_cr - aipa.amount)
) accounted_cr,
h.doc_sequence_value,
--l.reference4 voucher_number,
ent.transaction_number voucher_number,
l.accounting_class_code TYPE,
gl.segment2
|| '.'
|| gl.segment3
|| '.'
|| gl.segment4
|| '.'
|| gl.segment5 ACCOUNT,
l.source_id, ent.source_id_int_1, ent.security_id_int_1
FROM xla_ae_headers h,
xla_ae_lines l,
gl.gl_code_combinations gl,
xla_transaction_entities ent,
---ap.ap_checks_all ac,
ap_invoice_payments_all aipa
---WHERE h.ae_header_id = l.ae_header_id
AND ent.entity_id = h.entity_id
AND h.accounting_date >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND h.je_category_name IN ('Payments', 'Reconciled Payments')
AND NVL (l.entered_dr, 0) + NVL (l.entered_cr, 0) <> 0
AND gl.code_combination_id = l.code_combination_id
AND l.accounting_class_code NOT IN ('CASH_CLEARING')
------------
AND NVL (ent.source_id_int_1, (-99)) = TO_CHAR (ac.check_id)
AND ac.check_id = aipa.check_id
------------
AND source_id_int_1 = :p_source_id_int_1 -- Check_id
AND NOT (accounting_class_code = 'LIABILITY' AND entered_cr IS NOT NULL)
ORDER BY h.event_id, l.accounted_dr, l.accounted_cr, l.ae_line_num;
---------------------------------------------------------------------
Query - 2
SELECT /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */
DISTINCT xah.event_id, xal.ae_line_num, xah.ae_header_id,
xah.je_category_name, xah.accounting_date,
xah.gl_transfer_status_code, xah.event_type_code,
xah.doc_sequence_id, xal.currency_code,
xal.accounting_class_code, xal.code_combination_id,
xal.entered_dr org_entered_dr,
xal.accounted_dr org_accounted_dr,
xal.entered_cr org_entered_cr,
xal.accounted_cr org_accounted_cr,
aipa.amount amt_paid,
CASE
WHEN aipa.amount != xal.entered_dr
THEN (xal.entered_dr - aipa.amount)
ELSE xal.entered_dr
END entered_dr,
CASE
WHEN aipa.amount != xal.accounted_dr
THEN (xal.accounted_dr - aipa.amount)
ELSE xal.accounted_dr
END accounted_dr, CASE
WHEN aipa.amount != xal.entered_cr
THEN (xal.entered_cr - aipa.amount)
ELSE xal.entered_cr
END entered_cr,
CASE
WHEN aipa.amount != xal.accounted_cr
THEN (xal.accounted_cr - aipa.amount
)
ELSE xal.accounted_cr
END accounted_cr,
NVL ((xal.entered_dr - aipa.amount), 0) + NVL ((xal.entered_cr - aipa.amount), 0) sum_dr_cr,
xah.doc_sequence_value,
--l.reference4 voucher_number,
xte.transaction_number voucher_number,
xal.accounting_class_code TYPE,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT,
xal.source_id, xte.source_id_int_1,
xte.security_id_int_1
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla.xla_transaction_entities xte,
ap.ap_checks_all ac,
gl_code_combinations glcc,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
xla_distribution_links xdl,
ap_payment_hist_dists aphd,
---
gl_code_combinations gcc
WHERE 1 = 1
AND aipa.accounting_event_id = xah.event_id
AND NVL (xte.source_id_int_1, (-99)) =
TO_CHAR (ac.check_id)
AND xal.code_combination_id = glcc.code_combination_id
AND xal.ae_header_id = xah.ae_header_id
AND xte.application_id = xah.application_id -- AND xte.application_id = 200
-- AND xte.ledger_id = 1001
AND xte.entity_id = xah.entity_id
AND ac.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aila.line_number = 1
--AND glcc.segment3 = '123456789'
--AND aida.distribution_line_number = 1
AND aipa.invoice_payment_id = aphd.invoice_payment_id
AND xdl.source_distribution_type = 'AP_PMT_DIST'
AND xdl.source_distribution_id_num_1 =aphd.payment_hist_dist_id
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.application_id = xal.application_id
-------------
AND gcc.code_combination_id = xal.code_combination_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xal.accounting_class_code != 'CASH_CLEARING'
AND xal.accounting_class_code = 'LIABILITY'
AND xte.source_id_int_1 = :p_source_id_int_1 -- Check_id
-----------------------------------------------------------
INVOICE PAYMENT STATUS Query
SELECT invoice_num, invoice_amount, amount_paid, invoice_currency_code,
alc2.displayed_field payment_status, invoice_date
FROM ap_invoices_all ai, ap_lookup_codes alc2
WHERE alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND invoice_num IN (SELECT cn_number
FROM oic_benef_details);
------------------------------------------------------------
Subledger-Ledger Linkage (Accounts Payables)
AP to GL Link with different Sub ledger source
Payment data in Sub-Ledger Accounting (XLA) - R12
How Payable Invoices Data is stored in Sub-ledger Accounting of Oracle Apps R12? (Oracle Sub-Ledger Accounting, Oracle Payables)
AP TO GL - PREPAY QUERY (Good One)
Query - 1
SELECT doc_sequence_value, ae_header_id, document_type, accounting_date,
gl_transfer_flag, head_desc, created_by, event_type, document_number,
currency_code, ae_line_id, ae_line_number, ae_line_type_code,
code_combination_id, curr, description,
SUM (unrounded_accounted_dr) accounted_dr,
SUM (unrounded_accounted_cr) accounted_cr,
SUM (unrounded_entered_dr) entered_dr,
SUM (unrounded_entered_cr) entered_cr, ac, codedescription, reversal_flag
FROM (SELECT xah.doc_sequence_value, xah.ae_header_id,
xah.je_category_name document_type, xah.accounting_date,
xah.gl_transfer_status_code gl_transfer_flag,
xah.description head_desc, xah.created_by,
xah.event_type_code event_type,
TO_NUMBER (xah.doc_sequence_value) document_number,
xal.currency_code, xal.gl_sl_link_id ae_line_id,
xal.ae_line_num ae_line_number,
xal.accounting_class_code ae_line_type_code,
xal.code_combination_id, xal.currency_code curr,
xal.accounted_dr, xal.accounted_cr, xal.description,
xal.entered_dr, xal.entered_cr, xdl.unrounded_accounted_dr,
xdl.unrounded_accounted_cr, xdl.unrounded_entered_dr,
xdl.unrounded_entered_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description
(101,
gcc.segment2,
'SEGMENT2')
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment3,
'SEGMENT3'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment4,
'SEGMENT4'
)
|| '-'
|| reb_functions_pkg.get_description (101,
gcc.segment5,
'SEGMENT5'
) codedescription,
aid.reversal_flag
FROM xla_ae_headers xah,
xla_distribution_links xdl,
ap_invoice_distributions_all aid,
xla_ae_lines xal,
gl_code_combinations gcc
WHERE xdl.ae_header_id = xah.ae_header_id
---
AND xdl.source_distribution_id_num_1 =
aid.invoice_distribution_id
AND aid.accounting_event_id = xdl.event_id
---
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
---
AND xal.code_combination_id = gcc.code_combination_id
--
AND fnd_date.string_to_date (xah.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
AND aid.reversal_flag = 'N'
AND xah.doc_sequence_value = 8090 ------ test parameter
)
GROUP BY doc_sequence_value,
ae_header_id,
document_type,
accounting_date,
gl_transfer_flag,
head_desc,
created_by,
event_type,
document_number,
currency_code,
ae_line_id,
ae_line_number,
ae_line_type_code,
code_combination_id,
curr,
description,
ac,
codedescription,
reversal_flag
ORDER BY doc_sequence_value, document_type, accounted_dr, accounted_cr
--------------------------------------------------------------------
Query - 2
SELECT aid.accounting_event_id, xdl.event_id, xdl.*
FROM xla_ae_headers xah,
xla_distribution_links xdl,
ap_invoice_distributions aid,
xla_ae_lines xal WHERE xdl.ae_header_id = xah.ae_header_id
---
AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
AND aid.accounting_event_id = xdl.event_id
---
AND xah.ae_header_id = xal.ae_header_id
AND xal.ae_line_num = xdl.ae_line_num
---
AND xdl.source_distribution_type = 'AP_INV_DIST'
AND xah.balance_type_code = 'A'
AND xah.je_category_name = 'Purchase Invoices'
AND aid.reversal_flag = 'N'
AND xah.doc_sequence_value = :p_doc_sequence_value;
---------------------------------------------------------------------------------
Query - 3
SELECT xld.*
FROM xla_ae_headers xeh, xla_distribution_links xld
WHERE xld.ae_header_id = xeh.ae_header_id
AND xld.source_distribution_type = 'AP_INV_DIST'
AND xeh.balance_type_code = 'A'
AND xeh.je_category_name = 'Purchase Invoices'
AND xeh.doc_sequence_value = :p_doc_sequence_value;
-----------------------------------------------------------------------------------------------------------------------------------------------------
Query - 4 SELECT DISTINCT ai.invoice_id, xel.*,
fnd_flex_ext.get_segs ('SQLGL',
'GL#',
'50577',
xel.code_combination_id
) "Account"
FROM xla_ae_lines xel,
xla_ae_headers xeh,
ap_invoices_all ai,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xel.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND xeh.je_category_name = 'Purchase Invoices'
AND xeh.balance_type_code = 'A'
AND ai.invoice_id = :p_invoice_id
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_INVOICES'
AND xte.source_id_int_1 = ai.invoice_id
AND xte.entity_id = xeh.entity_id
ORDER BY xel.ae_header_id, xel.ae_line_num ASC;
---------------------------------------------------------------------
SELECT ah.ae_header_id, ah.je_category_name document_type,
ah.accounting_date, ah.gl_transfer_status_code gl_transfer_flag,
ah.description head_desc, ah.created_by,
ah.event_type_code event_type,
TO_NUMBER (ah.doc_sequence_value) document_number, al.currency_code,
al.gl_sl_link_id ae_line_id, al.ae_line_num ae_line_number,
al.accounting_class_code ae_line_type_code, al.code_combination_id,
al.currency_code curr, al.accounted_dr, al.accounted_cr,
al.description, al.entered_dr, al.entered_cr,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ac,
--(Change the Code of REB_functions_pkg.get_description)---
reb_functions_pkg.get_description (101,
gcc.segment2,
'SEGMENT2'
)
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment3, 'SEGMENT3')
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment4, 'SEGMENT4')
|| '-'
|| reb_functions_pkg.get_description (101, gcc.segment5, 'SEGMENT5')
codedescription,
ad.reversal_flag
FROM apps.gl_je_headers h,
apps.gl_je_lines jl,
apps.gl_code_combinations c,
apps.gl_import_references r,
apps.xla_ae_lines al,
apps.xla_ae_headers ah,
apps.xla_distribution_links l,
apps.ap_invoices_all i,
apps.ap_invoice_distributions_all ad,
apps.ap_suppliers s,
apps.xla_events e,
apps.xla_transaction_entities te,
----
gl_code_combinations gcc ----
WHERE ah.doc_sequence_value = 8007
-- AND c.code_combination_id = 6429 AND jl.description != 'GB VAT - STANDARD TAX'
AND ad.line_type_lookup_code = 'ITEM'
AND jl.je_header_id = h.je_header_id
AND jl.code_combination_id = c.code_combination_id
AND al.gl_sl_link_id = r.gl_sl_link_id
AND al.ae_header_id = ah.ae_header_id
AND al.application_id = ah.application_id
AND ah.application_id = e.application_id
AND ah.event_id = e.event_id
AND e.application_id = te.application_id(+)
AND e.entity_id = te.entity_id(+)
AND r.je_header_id = jl.je_header_id
AND r.je_line_num = jl.je_line_num
AND l.ae_header_id = al.ae_header_id
AND l.ae_line_num = al.ae_line_num
AND l.applied_to_source_id_num_1 = i.invoice_id
AND l.source_distribution_id_num_1 = ad.invoice_distribution_id
AND ad.invoice_id = i.invoice_id
AND i.vendor_id = s.vendor_id
------------------ AND al.code_combination_id = gcc.code_combination_id
AND fnd_date.string_to_date (ah.accounting_date, 'DD-MON-RRRR') >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND l.source_distribution_type = 'AP_INV_DIST'
AND ah.balance_type_code = 'A'
AND ah.je_category_name = 'Purchase Invoices'
AND ad.reversal_flag = 'N'
------------------ ORDER BY ah.doc_sequence_value,
ah.je_category_name,
al.accounted_dr,
al.accounted_cr
---------------------------------------------------------------------------------
Query -5
---Query on the Account View from the Invoice Form
SELECT *
FROM (SELECT /*+ OPTIMIZER_FEATURES_ENABLE('9.2.0') opt_param('hash_join_enabled','false') */
l.code_combination_id,
fnd_flex_ext.get_segs ('SQLGL',
'GL#',
gl.chart_of_accounts_id,
l.code_combination_id
) ACCOUNT,
xla_oa_functions_pkg.get_ccid_description
(gl.chart_of_accounts_id,
l.code_combination_id
) account_description,
l.accounting_class_code,
NVL (lk7.meaning, l.accounting_class_code) accounting_class,
NULL completion_acct_seq_name, h.completion_acct_seq_value,
h.completion_acct_seq_version_id completion_acct_seq_ver_id,
NULL completion_acct_seq_ver_name, h.balance_type_code,
NULL balance_type, h.budget_version_id, bud.budget_name,
h.completed_date, l.accounted_dr, l.accounted_cr,
l.created_by, NULL created_by_user, l.creation_date,
l.currency_code, l.description line_description,
h.doc_sequence_id, NULL doc_sequence_name,
h.doc_sequence_value, l.encumbrance_type_id,
NULL encumbrance_type, l.entered_dr, l.entered_cr,
et.event_class_code, ec.NAME event_class,
ue.user_name event_created_by_user,
e.creation_date event_creation_date, e.event_date,
et.NAME event_type, e.event_number,
l.currency_conversion_date, l.currency_conversion_rate,
l.currency_conversion_type, NULL user_conversion_type,
DECODE (gl.enable_budgetary_control_flag,
'N', NULL,
lk5.meaning
) funds_status,
NVL
(h.funds_status_code,
DECODE (gl.enable_budgetary_control_flag,
'REQUIRED', NULL
)
) funds_status_code,
h.accounting_date, l.gl_sl_link_id, l.gl_sl_link_table,
h.period_name, h.description header_description,
h.je_category_name, NULL user_je_cat_name, h.ae_header_id,
NULL journal_entry_name, gl.currency_code ledger_currency,
h.ledger_id, gl.NAME ledger_name, te.legal_entity_id,
le.NAME legal_entity_name,
le.legal_entity_identifier legal_entity_taxpayer_id,
l.ae_line_num, h.product_rule_type_code,
NULL prod_rule_type_dsp, h.product_rule_code,
NULL prod_rule_name,
h.product_rule_version prod_rule_version, l.jgzz_recon_ref,
h.reference_date, NULL close_acct_seq_name,
h.close_acct_seq_value,
h.close_acct_seq_version_id close_acct_seq_ver_id,
NULL close_acct_seq_ver_name, l.statistical_amount,
h.event_type_code, h.accounting_entry_status_code,
NULL ae_status, h.accounting_entry_type_code, NULL ae_type,
DECODE (l.party_type_code, 'C', l.party_id) customer_id,
NULL customer_name, NULL customer_number,
NULL customer_taxpayer_id,
DECODE (l.party_type_code,
'C', l.party_site_id
) customer_site_id,
NULL customer_site_name, l.party_type_code,
NULL party_type_dsp, h.gl_transfer_date,
h.gl_transfer_status_code, NULL gl_transfer_status,
l.ussgl_transaction_code, e.created_by event_created_by,
te.transaction_number, et.entity_code, NULL batch_name,
h.application_id, app.application_name, te.source_id_int_1,
te.source_id_int_2, te.source_id_int_3, te.source_id_int_4,
te.source_id_char_1, te.source_id_char_2,
te.source_id_char_3, te.source_id_char_4, h.event_id,
te.security_id_int_1, te.security_id_int_2,
te.security_id_int_3, te.security_id_char_1,
te.security_id_char_2, te.security_id_char_3,
te.valuation_method, NULL supplier_name,
NULL supplier_number, NULL supplier_taxpayer_id,
NULL supplier_site_name,
DECODE (l.party_type_code, 'S', l.party_id) supplier_id,
DECODE (l.party_type_code,
'S', l.party_site_id
) supplier_site_id,
'N' populate_hdr_flag, 'N' populate_line_flag,
'N' populate_gl_flag, 'N' populate_seq_flag,
'N' populate_supp_flag, 'N' populate_cust_flag,
NULL balancing_seg, NULL management_seg,
NULL cost_center_seg, NULL natural_acct_seg,
h.parent_ae_header_id, h.parent_ae_line_num,
te.ledger_id trx_ledger_id, l.displayed_line_number,
h.upg_batch_id
FROM xla_ae_headers h,
xla_ae_lines l,
xla_events e,
xla_transaction_entities te,
xla_gl_ledgers_v gl,
gl_budget_versions bud,
xle_entity_profiles le,
fnd_user ue,
fnd_application_vl app,
xla_event_types_tl et,
xla_event_classes_tl ec,
xla_lookups lk5,
xla_lookups lk7 WHERE gl.ledger_id = h.ledger_id
AND bud.budget_version_id(+) = h.budget_version_id
AND le.legal_entity_id(+) = te.legal_entity_id
AND ue.user_id = e.created_by
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 = h.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 = h.event_type_code
AND et.LANGUAGE = USERENV ('LANG')
AND app.application_id = h.application_id
AND et.application_id = te.application_id
AND te.application_id = h.application_id
AND te.entity_id = h.entity_id
AND e.event_id = h.event_id
AND e.application_id = h.application_id
AND h.ae_header_id = l.ae_header_id
AND h.application_id = l.application_id
AND lk5.lookup_code = NVL (h.funds_status_code, 'REQUIRED')
AND lk5.lookup_type = 'XLA_FUNDS_STATUS'
AND lk7.lookup_code(+) = l.accounting_class_code
AND lk7.lookup_type(+) = 'XLA_ACCOUNTING_CLASS'
AND ( NVL (NVL (accounted_cr, accounted_dr), 0) <> 0
OR fnd_profile.VALUE ('XLA_SHOW_ZERO_AMT_JRNL') = 'Y'
)) qrslt
WHERE ( application_id = :1
AND entity_code = :2
AND trx_ledger_id = :3
AND NVL (source_id_int_1, -99) = :4
AND balance_type_code = 'A'
)
ORDER BY ACCOUNT
XLA Queries to AP Payments
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
R12: Join GL to AP via XLA Tables
Query to Link AP Payments, GL and SLA Tables: R12 AP GL XLA
R12: Join GL to AP via XLA Tables
Query 1
SELECT aipa.reversal_flag, h.event_id, l.ae_line_num, h.ae_header_id,
h.je_category_name, h.accounting_date, h.gl_transfer_status_code,
h.event_type_code, h.doc_sequence_id, l.currency_code,
l.accounting_class_code, l.code_combination_id,
l.entered_dr org_entered_dr, l.accounted_dr org_accounted_dr,
l.entered_cr org_entered_cr, l.accounted_cr org_accounted_cr,
aipa.amount amt_paid,
DECODE (aipa.amount,
l.entered_dr, l.entered_dr,
(l.entered_dr - aipa.amount)
) entered_dr,
DECODE (aipa.amount,
l.accounted_dr, l.entered_dr,
(l.accounted_dr - aipa.amount)
) accounted_dr,
DECODE (aipa.amount,
l.entered_cr, l.entered_cr,
(l.entered_cr - aipa.amount)
) entered_cr,
DECODE (aipa.amount,
l.accounted_cr, l.entered_cr,
(l.accounted_cr - aipa.amount)
) accounted_cr,
h.doc_sequence_value,
--l.reference4 voucher_number,
ent.transaction_number voucher_number,
l.accounting_class_code TYPE,
gl.segment2
|| '.'
|| gl.segment3
|| '.'
|| gl.segment4
|| '.'
|| gl.segment5 ACCOUNT,
l.source_id, ent.source_id_int_1, ent.security_id_int_1
FROM xla_ae_headers h,
xla_ae_lines l,
gl.gl_code_combinations gl,
xla_transaction_entities ent,
---ap.ap_checks_all ac,
ap_invoice_payments_all aipa
---WHERE h.ae_header_id = l.ae_header_id
AND ent.entity_id = h.entity_id
AND h.accounting_date >
fnd_date.string_to_date ('31-DEC-2010', 'DD-MON-RRRR')
AND h.je_category_name IN ('Payments', 'Reconciled Payments')
AND NVL (l.entered_dr, 0) + NVL (l.entered_cr, 0) <> 0
AND gl.code_combination_id = l.code_combination_id
AND l.accounting_class_code NOT IN ('CASH_CLEARING')
------------
AND NVL (ent.source_id_int_1, (-99)) = TO_CHAR (ac.check_id)
AND ac.check_id = aipa.check_id
------------
AND source_id_int_1 = :p_source_id_int_1 -- Check_id
AND NOT (accounting_class_code = 'LIABILITY' AND entered_cr IS NOT NULL)
ORDER BY h.event_id, l.accounted_dr, l.accounted_cr, l.ae_line_num;
---------------------------------------------------------------------
Query - 2
SELECT /*+ Index(xdl XLA_DISTRIBUTION_LINKS_N3) */
DISTINCT xah.event_id, xal.ae_line_num, xah.ae_header_id,
xah.je_category_name, xah.accounting_date,
xah.gl_transfer_status_code, xah.event_type_code,
xah.doc_sequence_id, xal.currency_code,
xal.accounting_class_code, xal.code_combination_id,
xal.entered_dr org_entered_dr,
xal.accounted_dr org_accounted_dr,
xal.entered_cr org_entered_cr,
xal.accounted_cr org_accounted_cr,
aipa.amount amt_paid,
CASE
WHEN aipa.amount != xal.entered_dr
THEN (xal.entered_dr - aipa.amount)
ELSE xal.entered_dr
END entered_dr,
CASE
WHEN aipa.amount != xal.accounted_dr
THEN (xal.accounted_dr - aipa.amount)
ELSE xal.accounted_dr
END accounted_dr, CASE
WHEN aipa.amount != xal.entered_cr
THEN (xal.entered_cr - aipa.amount)
ELSE xal.entered_cr
END entered_cr,
CASE
WHEN aipa.amount != xal.accounted_cr
THEN (xal.accounted_cr - aipa.amount
)
ELSE xal.accounted_cr
END accounted_cr,
NVL ((xal.entered_dr - aipa.amount), 0) + NVL ((xal.entered_cr - aipa.amount), 0) sum_dr_cr,
xah.doc_sequence_value,
--l.reference4 voucher_number,
xte.transaction_number voucher_number,
xal.accounting_class_code TYPE,
gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5 ACCOUNT,
xal.source_id, xte.source_id_int_1,
xte.security_id_int_1
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla.xla_transaction_entities xte,
ap.ap_checks_all ac,
gl_code_combinations glcc,
ap_invoice_payments_all aipa,
ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
xla_distribution_links xdl,
ap_payment_hist_dists aphd,
---
gl_code_combinations gcc
WHERE 1 = 1
AND aipa.accounting_event_id = xah.event_id
AND NVL (xte.source_id_int_1, (-99)) =
TO_CHAR (ac.check_id)
AND xal.code_combination_id = glcc.code_combination_id
AND xal.ae_header_id = xah.ae_header_id
AND xte.application_id = xah.application_id -- AND xte.application_id = 200
-- AND xte.ledger_id = 1001
AND xte.entity_id = xah.entity_id
AND ac.check_id = aipa.check_id
AND aipa.invoice_id = aia.invoice_id
AND aia.invoice_id = aila.invoice_id
AND aila.invoice_id = aida.invoice_id
AND aila.line_number = aida.invoice_line_number
AND aila.line_number = 1
--AND glcc.segment3 = '123456789'
--AND aida.distribution_line_number = 1
AND aipa.invoice_payment_id = aphd.invoice_payment_id
AND xdl.source_distribution_type = 'AP_PMT_DIST'
AND xdl.source_distribution_id_num_1 =aphd.payment_hist_dist_id
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.application_id = xal.application_id
-------------
AND gcc.code_combination_id = xal.code_combination_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xal.accounting_class_code != 'CASH_CLEARING'
AND xal.accounting_class_code = 'LIABILITY'
AND xte.source_id_int_1 = :p_source_id_int_1 -- Check_id
-----------------------------------------------------------
INVOICE PAYMENT STATUS Query
SELECT invoice_num, invoice_amount, amount_paid, invoice_currency_code,
alc2.displayed_field payment_status, invoice_date
FROM ap_invoices_all ai, ap_lookup_codes alc2
WHERE alc2.lookup_type(+) = 'INVOICE PAYMENT STATUS'
AND alc2.lookup_code(+) = ai.payment_status_flag
AND invoice_num IN (SELECT cn_number
FROM oic_benef_details);
------------------------------------------------------------
INVOICE and PAYMENT Period Wise Query
SELECT gcc.concatenated_segments gl_account, ap.gl_date,
'Payables' payable_source, ap.invoice_id, ap.invoice_currency_code,
ap.invoice_amount, ap.exchange_rate, ap.base_amount, ap.vendor_id,
aps.segment1 vendor_number, aps.vendor_name, ap.invoice_num,
ac.check_id, ac.check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_payments_all aip,
ap_checks_all ac
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
---
AND aip.invoice_id(+) = ap.invoice_id
AND aip.check_id = ac.check_id(+)
---
AND fnd_date.string_to_date (ap.gl_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND invoice_num = '5001111'
==========================================
/* Formatted on 2017/03/29 11:59 (Formatter Plus v4.8.8) */
-------------------------
--INVOICE
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Invoice' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND aid.org_id = api.org_id
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.org_id = :p_org_id
----------------------------
--PRE PAYMENT HEADER
UNION
----------------------------
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Pre Payments Head' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE aid.dist_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.invoice_type_lookup_code = 'PREPAYMENT'
AND ap.org_id = :p_org_id
----------------------------
--PRE PAYMENT DIST
UNION
-----------------------------
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Pre Payments Dist' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE aid.dist_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.invoice_type_lookup_code != 'PREPAYMENT'
AND aid.line_type_lookup_code = 'PREPAY'
AND ap.org_id = :p_org_id
----------------------------
--CHECKS
UNION
----------------------------
SELECT gcc.concatenated_segments gl_account, ac.check_date,
NULL invoice_distribution_id, aip.accounting_date gl_date,
'Payables' payable_source, ac.doc_category_code category_code,
ac.check_id, ac.currency_code, aip.amount,
NVL (aip.exchange_rate, 1) exchange_rate,
(aip.amount * NVL (aip.exchange_rate, 1)) base_amount, ac.vendor_id,
aps.segment1 vendor_number, ac.vendor_name, ai.invoice_num,
ac.check_number, ac.org_id,
hr_general.decode_organization (ac.org_id) operating_unit_name,
aip.period_name, 'Checks' payable_lines
FROM ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoices_all ai,
gl_code_combinations_kfv gcc,
ap_suppliers aps
WHERE 1 = 1
AND ac.check_id = aip.check_id
AND aip.invoice_id = ai.invoice_id
AND ai.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ac.vendor_id(+)
AND fnd_date.string_to_date (aip.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ac.org_id = :p_org_id
SELECT gcc.concatenated_segments gl_account, ap.gl_date,
'Payables' payable_source, ap.invoice_id, ap.invoice_currency_code,
ap.invoice_amount, ap.exchange_rate, ap.base_amount, ap.vendor_id,
aps.segment1 vendor_number, aps.vendor_name, ap.invoice_num,
ac.check_id, ac.check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_payments_all aip,
ap_checks_all ac
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
---
AND aip.invoice_id(+) = ap.invoice_id
AND aip.check_id = ac.check_id(+)
---
AND fnd_date.string_to_date (ap.gl_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND invoice_num = '5001111'
==========================================
/* Formatted on 2017/03/29 11:59 (Formatter Plus v4.8.8) */
-------------------------
--INVOICE
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Invoice' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE ap.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND aid.org_id = api.org_id
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.org_id = :p_org_id
----------------------------
--PRE PAYMENT HEADER
UNION
----------------------------
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Pre Payments Head' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE aid.dist_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.invoice_type_lookup_code = 'PREPAYMENT'
AND ap.org_id = :p_org_id
----------------------------
--PRE PAYMENT DIST
UNION
-----------------------------
SELECT /*+ INDEX (aid AP_INVOICE_DISTRIBUTIONS_N33)*/
gcc.concatenated_segments gl_account, ap.invoice_date,
aid.invoice_distribution_id, aid.accounting_date,
'Payables' payable_source, ap.invoice_type_lookup_code category_code,
ap.invoice_id, ap.invoice_currency_code currency_code, aid.amount,
NVL (ap.exchange_rate, 1) exchange_rate,
ROUND (aid.amount * NVL (ap.exchange_rate, 1), 2) base_amount,
ap.vendor_id, aps.segment1 vendor_number, aps.vendor_name,
ap.invoice_num, NULL check_number, ap.org_id,
hr_general.decode_organization (ap.org_id) operating_unit_name,
:p_period, 'Pre Payments Dist' payable_lines
FROM ap_invoices_all ap,
gl_code_combinations_kfv gcc,
ap_suppliers aps,
ap_supplier_sites_all apss,
ap_invoice_lines_all api,
ap_invoice_distributions_all aid
WHERE aid.dist_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ap.vendor_id(+)
-- AND apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id(+) = ap.vendor_site_id
----------------
AND ap.invoice_id = api.invoice_id
AND aid.invoice_id = api.invoice_id
AND aid.invoice_line_number = api.line_number
AND fnd_date.string_to_date (aid.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ap.invoice_type_lookup_code != 'PREPAYMENT'
AND aid.line_type_lookup_code = 'PREPAY'
AND ap.org_id = :p_org_id
----------------------------
--CHECKS
UNION
----------------------------
SELECT gcc.concatenated_segments gl_account, ac.check_date,
NULL invoice_distribution_id, aip.accounting_date gl_date,
'Payables' payable_source, ac.doc_category_code category_code,
ac.check_id, ac.currency_code, aip.amount,
NVL (aip.exchange_rate, 1) exchange_rate,
(aip.amount * NVL (aip.exchange_rate, 1)) base_amount, ac.vendor_id,
aps.segment1 vendor_number, ac.vendor_name, ai.invoice_num,
ac.check_number, ac.org_id,
hr_general.decode_organization (ac.org_id) operating_unit_name,
aip.period_name, 'Checks' payable_lines
FROM ap_checks_all ac,
ap_invoice_payments_all aip,
ap_invoices_all ai,
gl_code_combinations_kfv gcc,
ap_suppliers aps
WHERE 1 = 1
AND ac.check_id = aip.check_id
AND aip.invoice_id = ai.invoice_id
AND ai.accts_pay_code_combination_id = gcc.code_combination_id
AND aps.vendor_id = ac.vendor_id(+)
AND fnd_date.string_to_date (aip.accounting_date, 'DD-MON-RR')
BETWEEN fnd_date.string_to_date ('01-' || :p_period, 'DD-MON-RR')
AND LAST_DAY (fnd_date.string_to_date ('01-' || :p_period,
'DD-MON-RR'
)
)
AND ac.org_id = :p_org_id
Supplier Query
Supplier Query
WITH with_supp_contact AS
(SELECT hr.subject_id, asco.party_site_id, asco.org_party_site_id, hpc.party_name contact_person,
hpr.primary_phone_country_code cnt_cntry, hpr.primary_phone_area_code cnt_area,
hpr.primary_phone_number cnt_phone, asco.vendor_contact_id, asco.inactive_date, hpcp.contact_point_id,
hpcp.contact_point_type, hpcp.owner_table_name, hpcp.owner_table_id, hpcp.contact_point_type, hpcp.status,
hpcp.primary_flag, hpcp.email_address, hpcp.phone_area_code, hpcp.phone_country_code, hpcp.phone_number,
hpcp.phone_country_code, hpcp.phone_number, hpcp.phone_line_type, hpcp.raw_phone_number
FROM hz_relationships hr,
ap_supplier_contacts asco,
hz_org_contacts hoc,
hz_parties hpc,
hz_parties hpr,
hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
-- AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
-- AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND asco.inactive_date IS NULL
-- AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type = 'PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES')
SELECT DISTINCT asu.segment1 acc_code, asu.vendor_name acc_name, assa.creation_date open_date, assa.vendor_site_code,
apt.NAME credit_period, NULL credit_limit,
( assa.address_line1
|| ', '
|| assa.address_line2
|| ', '
-- || assa.address_line3
|| ', '
|| assa.city
|| ', '
|| assa.state
-- || ', '
-- || assa.zip
|| ', '
|| assa.province
|| ','
|| ft.territory_short_name
) address,
assa.area_code || assa.phone acc_phone, assa.fax_area_code || assa.fax acc_fax, assa.email_address,
NULL web_site, wspc.contact_person, NULL salesman, NULL mobile_no, assa.area_code country_code,
NULL cur_code, zr.registration_number vat_trn
FROM ap_suppliers asu,
ap_terms_tl apt,
ap_supplier_sites_all assa,
with_supp_contact wspc,
zx_party_tax_profile zptp,
zx_registrations zr,
fnd_territories_tl ft
WHERE assa.org_id = :p_org_id
AND apt.term_id = assa.terms_id
AND apt.LANGUAGE = USERENV ('LANG')
AND asu.vendor_id = assa.vendor_id
---(Contact information)----------------------
-- AND wspc.subject_id (+)= asu.party_id
AND wspc.org_party_site_id(+) = assa.party_site_id
-------------------------------------------
----------(Tax Information)----------------
AND zptp.party_id(+) = assa.party_site_id
AND zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id(+) = zptp.party_tax_profile_id
AND zr.tax_regime_code(+) = 'UAE_TAX_REGIME'
AND zr.effective_to(+) IS NULL
--------------------------
AND assa.country = ft.territory_code(+)
AND ft.LANGUAGE(+) = USERENV ('LANG')
Supplier Query
WITH with_supp_contact AS(SELECT hr.subject_id, asco.party_site_id, asco.org_party_site_id, hpc.party_name contact_person,
hpr.primary_phone_country_code cnt_cntry, hpr.primary_phone_area_code cnt_area,
hpr.primary_phone_number cnt_phone, asco.vendor_contact_id, asco.inactive_date, hpcp.contact_point_id,
hpcp.contact_point_type, hpcp.owner_table_name, hpcp.owner_table_id, hpcp.contact_point_type, hpcp.status,
hpcp.primary_flag, hpcp.email_address, hpcp.phone_area_code, hpcp.phone_country_code, hpcp.phone_number,
hpcp.phone_country_code, hpcp.phone_number, hpcp.phone_line_type, hpcp.raw_phone_number
FROM hz_relationships hr,
ap_supplier_contacts asco,
hz_org_contacts hoc,
hz_parties hpc,
hz_parties hpr,
hz_contact_points hpcp
WHERE hoc.party_relationship_id = hr.relationship_id
-- AND hr.subject_id = asu.party_id
AND hr.relationship_code = 'CONTACT'
AND hr.object_table_name = 'HZ_PARTIES'
-- AND asu.vendor_id = assa.vendor_id
AND hr.object_id = hpc.party_id
AND hr.party_id = hpr.party_id
AND asco.relationship_id = hoc.party_relationship_id
AND asco.inactive_date IS NULL
-- AND assa.party_site_id = asco.org_party_site_id
AND hpr.party_type = 'PARTY_RELATIONSHIP'
AND hpr.party_id = hpcp.owner_table_id
AND hpcp.owner_table_name = 'HZ_PARTIES')
SELECT DISTINCT asu.segment1 acc_code, asu.vendor_name acc_name, assa.creation_date open_date, assa.vendor_site_code,
apt.NAME credit_period, NULL credit_limit,
( assa.address_line1
|| ', '
|| assa.address_line2
|| ', '
-- || assa.address_line3
|| ', '
|| assa.city
|| ', '
|| assa.state
-- || ', '
-- || assa.zip
|| ', '
|| assa.province
|| ','
|| ft.territory_short_name
) address,
assa.area_code || assa.phone acc_phone, assa.fax_area_code || assa.fax acc_fax, assa.email_address,
NULL web_site, wspc.contact_person, NULL salesman, NULL mobile_no, assa.area_code country_code,
NULL cur_code, zr.registration_number vat_trn
FROM ap_suppliers asu,
ap_terms_tl apt,
ap_supplier_sites_all assa,
with_supp_contact wspc,
zx_party_tax_profile zptp,
zx_registrations zr,
fnd_territories_tl ft
WHERE assa.org_id = :p_org_id
AND apt.term_id = assa.terms_id
AND apt.LANGUAGE = USERENV ('LANG')
AND asu.vendor_id = assa.vendor_id
---(Contact information)----------------------
-- AND wspc.subject_id (+)= asu.party_id
AND wspc.org_party_site_id(+) = assa.party_site_id
-------------------------------------------
----------(Tax Information)----------------
AND zptp.party_id(+) = assa.party_site_id
AND zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
AND zr.party_tax_profile_id(+) = zptp.party_tax_profile_id
AND zr.tax_regime_code(+) = 'UAE_TAX_REGIME'
AND zr.effective_to(+) IS NULL
--------------------------
AND assa.country = ft.territory_code(+)
AND ft.LANGUAGE(+) = USERENV ('LANG')
ReplyDeleteThanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Supply Chain Management Cloud . Actually, I was looking for the same information on internet for
Oracle Fusion HCM Interview Questions and Answers and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject.