Tuesday, 3 January 2017

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 (    :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

By 
Deepak J
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)