Introduction
The report lists in detail any transactions that have been accounted for with error and all entries that could not be transferred to the general ledger interface. When a transaction is considered with errors, use the Update Accounting Entries window to update any invalid accounts
Script To Payables Accounting Entries Report.
/* Query 1 */
SELECT aeh.set_of_books_id set_of_books_id_excp, COUNT
(*) count_excp_data_ace
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aeh.set_of_books_id = :p_set_of_books_id
AND aeh.gl_transfer_run_id = -1
AND aeh.gl_transfer_flag = ‘N’
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_journal_category = ‘A’ AND aeh.ae_category = aeh.ae_category)
OR ( :p_journal_category = ‘Purchase Invoices’
AND aeh.ae_category = ‘Purchase Invoices’
)
OR (:p_journal_category = ‘Payments’ AND aeh.ae_category = ‘Payments’
)
OR ( :p_journal_category = ‘Reconciled Payments’
AND aeh.ae_category = ‘Reconciled Payments’
)
)
AND ( aeh.accounting_error_code IS NOT NULL
OR EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
)
GROUP BY AEH.Set_of_Books_ID
/* Query 2 */
SELECT aeh.set_of_books_id set_of_books_id_excp,
COUNT (*) count_excp_data_glte
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aeh.set_of_books_id = :p_set_of_books_id
AND aeh.gl_transfer_run_id = -1
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_journal_category = ‘A’ AND aeh.ae_category = aeh.ae_category
)
OR ( :p_journal_category = ‘Purchase Invoices’
AND aeh.ae_category = ‘Purchase Invoices’
)
OR (:p_journal_category = ‘Payments’
AND aeh.ae_category = ‘Payments’
)
OR ( :p_journal_category = ‘Reconciled Payments’
AND aeh.ae_category = ‘Reconciled Payments’
)
)
AND aeh.gl_transfer_flag = ‘E’
GROUP BY aeh.set_of_books_id
/* Query 3 */
SELECT *
–&P_FLEXDATA C_FLEXDATA
FROM GL_CODE_COMBINATIONS
WHERE CHART_OF_ACCOUNTS_ID = :C_CHART_OF_ACCOUNTS_ID
/* Query 4 */
SELECT decode(lc.lookup_code,’Y’,’E’,’N’) main_gl_transfer_flag
FROM fnd_lookups lc
WHERE lc.lookup_type = ‘YES_NO’
ORDER BY decode(lc.lookup_code,’N’,1,2)
/* Query 5 */
SELECT aeh.set_of_books_id set_of_books_id_i,
glc.user_je_category_name journal_category_i,
aae.accounting_date accounting_date_i,
alc.displayed_field event_type_i,
DECODE (:sort_by_alternate,
‘Y’, (NVL ((UPPER (pv.vendor_name_alt)),
(UPPER (pv.vendor_name)
)
)
),
UPPER (pv.vendor_name)
) supplier_name_i1,
pv.vendor_name supplier_name_i, ai.invoice_num document_number_i,
DECODE (ai.doc_sequence_id,
NULL, ai.voucher_num,
ai.doc_sequence_value
) voucher_num_i,
ael.ae_line_number line_num_i, alc2.displayed_field line_type_i,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_i,
ael.currency_code currency_i, ael.entered_dr entered_dr_i,
ael.entered_cr entered_cr_i, ael.accounted_dr accounted_dr_i,
ael.accounted_cr accounted_cr_i, fl.meaning gl_transfer_flag_i
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
po_vendors pv,
ap_invoices_all ai,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
fnd_lookups fl
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ai.invoice_id
AND ai.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND fl.lookup_type = ‘YES_NO’
AND fl.lookup_code = aeh.gl_transfer_flag
AND aeh.ae_category = ‘Purchase Invoices’
AND :p_journal_category IN (‘A’, ‘Purchase Invoices’)
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’
AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
ORDER BY accounting_date_i,
aae.event_type_code,
supplier_name_i1,
aae.source_id,
line_num_i
/* Query 6 */
SELECT glc.user_je_category_name journal_category_is,
ael.accounted_dr accounted_dr_is, ael.accounted_cr accounted_cr_is
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
po_vendors pv,
ap_invoices_all ai,
gl_je_categories glc
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ai.invoice_id
AND ai.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND :p_journal_category IN (‘A’, ‘Purchase Invoices’)
AND aeh.set_of_books_id = :p_set_of_books_id
AND aeh.ae_category = ‘Purchase Invoices’
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’ AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
/* Query 7 */
SELECT aeh.set_of_books_id set_of_books_ie,
glc.user_je_category_name journal_category_ie,
aae.accounting_date accounting_date_ie,
alc.displayed_field event_type_ie, pv.vendor_name supplier_name_ie,
ai.invoice_num document_number_ie,
DECODE (ai.doc_sequence_id,
NULL, ai.voucher_num,
ai.doc_sequence_value
) voucher_num_ie,
ael.ae_line_number line_num_ie, alc2.displayed_field line_type_ie,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_ie,
ael.currency_code currency_ie, ael.entered_dr entered_dr_ie,
ael.entered_cr entered_cr_ie, ael.accounted_dr accounted_dr_ie,
ael.accounted_cr accounted_cr_ie,
DECODE (aeh.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc3.displayed_field
) header_exception_ie,
DECODE (aeh.gl_transfer_flag,
‘N’, DECODE (ael.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc5.displayed_field
),
alc6.displayed_field
) line_exception_ie,
aeh.gl_transfer_flag gl_transfer_flag_ie
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
po_vendors pv,
ap_invoices_all ai,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
ap_lookup_codes alc3,
ap_lookup_codes alc4,
ap_lookup_codes alc5,
ap_lookup_codes alc6
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ai.invoice_id
AND ai.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND alc3.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc3.lookup_code(+) = aeh.accounting_error_code
AND alc4.lookup_type = ‘NLS TRANSLATION’
AND alc4.lookup_code = ‘FATAL ERROR’
AND alc5.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc5.lookup_code(+) = ael.accounting_error_code
AND alc6.lookup_type(+) = ‘POSTING EXCEPTIONS’
AND alc6.lookup_code(+) = ael.gl_transfer_error_code
AND aeh.ae_category = ‘Purchase Invoices’
AND :p_journal_category IN (‘A’, ‘Purchase Invoices’)
AND aeh.gl_transfer_run_id = -1
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( ( aeh.accounting_error_code IS NOT NULL
OR EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
)
OR aeh.gl_transfer_flag = ‘E’
)
ORDER BY DECODE (aeh.gl_transfer_flag, ‘N’, 1, 2),
accounting_date_ie,
event_type_ie,
DECODE (:sort_by_alternate,
‘Y’, pv.vendor_name_alt,
supplier_name_ie
),
document_number_ie,
line_num_ie
/* Query 8 */
SELECT aeh.set_of_books_id set_of_books_id_p,
glc.user_je_category_name journal_cateogry_p,
ac.bank_account_name bank_account_p, acs.NAME payment_document_p,
aae.accounting_date accounting_date_p,
alc.displayed_field event_type_p,
DECODE (:sort_by_alternate,
‘Y’, (NVL ((UPPER (pv.vendor_name_alt)),
(UPPER (pv.vendor_name)
)
)
),
UPPER (pv.vendor_name)
) supplier_name_p1,
pv.vendor_name supplier_name_p, ac.check_number document_number_p,
DECODE (ac.doc_category_code,
NULL, ac.check_voucher_num,
ac.doc_sequence_value
) voucher_num_p,
ael.ae_line_number line_num_p, alc2.displayed_field line_type_p,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_p,
ael.currency_code currency_p, ael.entered_dr entered_dr_p,
ael.entered_cr entered_cr, ael.accounted_dr accounted_dr_p,
ael.accounted_cr accounted_cr_p, fl.meaning gl_transfer_flag_p
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
po_vendors pv,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
fnd_lookups fl
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id(+)
AND ac.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND fl.lookup_type = ‘YES_NO’
AND fl.lookup_code = aeh.gl_transfer_flag
AND aeh.ae_category = ‘Payments’
AND :p_journal_category IN (‘A’, ‘Payments’)
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’
AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
ORDER BY accounting_date_p,
aae.event_type_code,
supplier_name_p1,
aae.source_id,
line_num_p
/* Query 9 */
SELECT glc.user_je_category_name journal_category_ps,
ael.accounted_dr accounted_dr_ps, ael.accounted_cr accounted_cr_ps,
ac.bank_account_name bank_account_ps
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
gl_je_categories glc
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id(+)
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND aeh.ae_category = ‘Payments’
AND :p_journal_category IN (‘A’, ‘Payments’)
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’ AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
/* Query 10 */
SELECT aeh.set_of_books_id set_of_books_id_pe,
glc.user_je_category_name journal_category_pe,
ac.bank_account_name bank_account_pe, acs.NAME payment_document_pe,
aae.accounting_date accounting_date_pe,
alc.displayed_field event_type_pe, pv.vendor_name supplier_name_pe,
ac.check_number document_number_pe,
DECODE (ac.doc_category_code,
NULL, ac.check_voucher_num,
ac.doc_sequence_value
) voucher_num_pe,
ael.ae_line_number line_num_pe, alc2.displayed_field line_type_pe,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_pe,
ael.currency_code currency_pe, ael.entered_dr entered_dr_pe,
ael.entered_cr entered_cr_pe, ael.accounted_dr accounted_dr_pe,
ael.accounted_cr accounted_cr_pe,
DECODE (aeh.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc3.displayed_field
) header_exception_pe,
DECODE (aeh.gl_transfer_flag,
‘N’, DECODE (ael.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc5.displayed_field
),
alc6.displayed_field
) line_exception_pe,
aeh.gl_transfer_flag gl_transfer_flag_pe
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
po_vendors pv,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
ap_lookup_codes alc3,
ap_lookup_codes alc4,
ap_lookup_codes alc5,
ap_lookup_codes alc6
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id
AND ac.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND alc3.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc3.lookup_code(+) = aeh.accounting_error_code
AND alc4.lookup_type = ‘NLS TRANSLATION’
AND alc4.lookup_code = ‘FATAL ERROR’
AND alc5.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc5.lookup_code(+) = ael.accounting_error_code
AND alc6.lookup_type(+) = ‘POSTING EXCEPTIONS’
AND alc6.lookup_code(+) = ael.gl_transfer_error_code
AND aeh.ae_category = ‘Payments’
AND :p_journal_category IN (‘A’, ‘Payments’)
AND aeh.gl_transfer_run_id = -1
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( ( aeh.accounting_error_code IS NOT NULL
OR EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
)
OR aeh.gl_transfer_flag = ‘E’
)
ORDER BY DECODE (aeh.gl_transfer_flag, ‘N’, 1, 2),
accounting_date_pe,
event_type_pe,
DECODE (:sort_by_alternate,
‘Y’, pv.vendor_name_alt,
supplier_name_pe
),
document_number_pe,
line_num_pe
/* Query 11 */
SELECT aeh.set_of_books_id set_of_books_id_r,
glc.user_je_category_name journal_cateogry_r,
ac.bank_account_name bank_account_r, acs.NAME payment_document_r,
aae.accounting_date accounting_date_r,
alc.displayed_field event_type_r, pv.vendor_name supplier_name_r,
DECODE (:sort_by_alternate,
‘Y’, (NVL ((UPPER (pv.vendor_name_alt)),
(UPPER (pv.vendor_name)
)
)
),
UPPER (pv.vendor_name)
) supplier_name_r1,
ac.check_number document_number_r,
DECODE (ac.doc_category_code,
NULL, ac.check_voucher_num,
ac.doc_sequence_value
) voucher_num_r,
ael.ae_line_number line_num_r, alc2.displayed_field line_type_r,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_r,
ael.currency_code currency_r, ael.entered_dr entered_dr_r,
ael.entered_cr entered_cr_r, ael.accounted_dr accounted_dr_r,
ael.accounted_cr accounted_cr_r, fl.meaning gl_transfer_flag_r
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
po_vendors pv,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
fnd_lookups fl
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id(+)
AND ac.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND fl.lookup_type = ‘YES_NO’
AND fl.lookup_code = aeh.gl_transfer_flag
AND aeh.ae_category = ‘Reconciled Payments’
AND :p_journal_category IN (‘A’, ‘Reconciled Payments’)
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’
AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
ORDER BY accounting_date_r,
aae.event_type_code,
supplier_name_r1,
aae.source_id,
line_num_r
/* Query 13 */
SELECT glc.user_je_category_name journal_category_rs,
ael.accounted_dr accounted_dr_rs, ael.accounted_cr accounted_cr_rs,
ac.bank_account_name bank_account_rs
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
gl_je_categories glc
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND aeh.ae_category = ‘Reconciled Payments’
AND :p_journal_category IN (‘A’, ‘Reconciled Payments’)
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( (:p_gl_trans_status = ‘TRANSFERRED’ AND aeh.gl_transfer_flag = ‘Y’
)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED’
AND aeh.gl_transfer_flag = ‘N’
)
OR (:p_gl_trans_status = ‘ALL’ AND aeh.gl_transfer_flag <> ‘E’)
OR ( :p_gl_trans_status = ‘NOT TRANSFERRED DUE TO ERROR’
AND aeh.gl_transfer_flag = ‘Z’
)
)
AND aeh.accounting_error_code IS NULL
AND NOT EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
/* Query 4 */
SELECT aeh.set_of_books_id set_of_books_id_rpe,
glc.user_je_category_name journal_category_rpe,
ac.bank_account_name bank_account_rpe, acs.NAME payment_document_rpe,
aae.accounting_date accounting_date_rpe,
alc.displayed_field event_type_rpe, pv.vendor_name supplier_name_rpe,
ac.check_number document_number_rpe,
DECODE (ac.doc_category_code,
NULL, ac.check_voucher_num,
ac.doc_sequence_value
) voucher_num_rpe,
ael.ae_line_number line_num_rpe, alc2.displayed_field line_type_rpe,
ap_utilities_pkg.get_charge_account
(ael.code_combination_id,
:p_chart_of_accounts_id,
‘APXAEREP’
) account_rpe,
ael.currency_code currency_rpe, ael.entered_dr entered_dr_rpe,
ael.entered_cr entered_cr_rpe, ael.accounted_dr accounted_dr_rpe,
ael.accounted_cr accounted_cr_rpe,
DECODE (aeh.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc3.displayed_field
) header_exception_rpe,
DECODE (aeh.gl_transfer_flag,
‘N’, DECODE (ael.accounting_error_code,
NULL, NULL,
‘DEBIT DOES NOT EQUAL CREDIT’, alc4.displayed_field,
alc5.displayed_field
),
alc6.displayed_field
) line_exception_rpe,
aeh.gl_transfer_flag gl_transfer_flag_rpe
FROM ap_accounting_events_all aae,
ap_ae_headers_all aeh,
ap_ae_lines_all ael,
ap_check_stocks_all acs,
ap_checks_all ac,
po_vendors pv,
gl_je_categories glc,
ap_lookup_codes alc,
ap_lookup_codes alc2,
ap_lookup_codes alc3,
ap_lookup_codes alc4,
ap_lookup_codes alc5,
ap_lookup_codes alc6
WHERE aae.accounting_event_id = aeh.accounting_event_id
AND aeh.ae_header_id = ael.ae_header_id
AND aae.source_id = ac.check_id
AND ac.check_stock_id = acs.check_stock_id
AND ac.vendor_id = pv.vendor_id
AND glc.je_category_name = aeh.ae_category
AND aeh.set_of_books_id = :p_set_of_books_id
AND alc.lookup_type = ‘EVENT TYPE’
AND alc.lookup_code = aae.event_type_code
AND alc2.lookup_type = ‘AE LINE TYPE’
AND alc2.lookup_code = ael.ae_line_type_code
AND alc3.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc3.lookup_code(+) = aeh.accounting_error_code
AND alc4.lookup_type = ‘NLS TRANSLATION’
AND alc4.lookup_code = ‘FATAL ERROR’
AND alc5.lookup_type(+) = ‘ACCOUNTING ERROR TYPE’
AND alc5.lookup_code(+) = ael.accounting_error_code
AND alc6.lookup_type(+) = ‘POSTING EXCEPTIONS’
AND alc6.lookup_code(+) = ael.gl_transfer_error_code
AND aeh.ae_category = ‘Reconciled Payments’
AND :p_journal_category IN (‘A’, ‘Reconciled Payments’)
AND aeh.gl_transfer_run_id = -1
AND aae.accounting_date BETWEEN :p_start_date AND :p_end_date
AND aae.request_id =
DECODE (:p_rep_for_conc_process,
‘ACCOUNTING_PROCESS’, (NVL (:p_process_id, aae.request_id)),
aae.request_id
)
AND aeh.gl_transfer_run_id =
DECODE (:p_rep_for_conc_process,
‘TRANSFER_TO_GL’, (NVL (:p_process_id,
aeh.gl_transfer_run_id
)),
aeh.gl_transfer_run_id
)
AND ( ( aeh.accounting_error_code IS NOT NULL
OR EXISTS (
SELECT ‘There is some exception at the line level’
FROM ap_ae_lines_all ael1
WHERE ael1.ae_header_id = aeh.ae_header_id
AND ael1.accounting_error_code IS NOT NULL)
)
OR aeh.gl_transfer_flag = ‘E’
)
ORDER BY DECODE (aeh.gl_transfer_flag, ‘N’, 1, 2),
accounting_date_rpe,
event_type_rpe,
DECODE (:sort_by_alternate,
‘Y’, pv.vendor_name_alt,
supplier_name_rpe
),
document_number_rpe,
line_num_rpe
What we expect in the script.
This script helps us to comprehend how payables accounting Entries Report. Couple of tables which is being used in this query are ap_accounting_events_all,ap_ae_headers_all,ap_ae_lines_all,ap_check_stocks_all,ap_checks_all,po_vendors,gl_je_categories,ap_lookup_codes,ap_lookup_codes,ap_lookup_codes alc3
Summary
This Post described the script payables accounting Entries Report in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
