Payables AccountingEntries ReportDescriptionTherep…

Payables Accounting
Entries Report
Description
The
report lists in detail any transactions that have been accounted with error and
all entries that could not be transferred to the general ledger interface. When
a transaction is accounted with errors, use the Update Accounting Entries
window to update any invalid accounts
/* 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 (