APPS.PSA_BC_XLA_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 57

PROCEDURE psa_acctg_errors_insert(psa_events IN psa_events_table, psa_acctg_errors IN psa_acctg_errors_table) IS
PRAGMA AUTONOMOUS_TRANSACTION;

Line: 61

   l_path_name := g_path_name||'.psa_acctg_errors_insert';

Line: 63

   psa_utils.debug_other_string(g_state_level,l_path_name, 'BEGIN of procedure psa_acctg_errors_insert');

Line: 66

       DELETE FROM psa_bc_accounting_errors
       WHERE event_id = psa_events(i);

Line: 68

   psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows deleted from psa_bc_accounting_erros: '||SQL%ROWCOUNT);

Line: 71

       INSERT INTO psa_bc_accounting_errors VALUES psa_acctg_errors(j);

Line: 72

   psa_utils.debug_other_string(g_state_level,l_path_name,'No of rows inserted into psa_bc_accounting_erros: '||SQL%ROWCOUNT);

Line: 75

   psa_utils.debug_other_string(g_state_level,l_path_name, 'END of procedure psa_acctg_errors_insert');

Line: 77

END psa_acctg_errors_insert;

Line: 141

      SELECT XE.entity_id entity_id
      FROM XLA_ENTITY_EVENTS_V XE,PSA_BC_XLA_EVENTS_GT BCE
      WHERE XE.event_id = BCE.event_id
      AND   XE.event_status_code <> 'P'
      AND   XE.application_id = p_application_id
      GROUP BY xe.entity_id;

Line: 154

      SELECT nvl(sum(decode(upper(result_code),'FATAL',1)),0)  status_fatal_count,
      sum(decode(upper(result_code),'XLA_ERROR',1)) status_xla_err_count,
      sum(decode(upper(result_code),'FAIL',1)) status_fail_count,
      sum(decode(upper(result_code),'PARTIAL',1)) status_partial_count,
      sum(decode(upper(result_code),'ADVISORY',1)) status_advisory_count,
      nvl(sum(decode(upper(result_code),'SUCCESS',1)),0) status_success_count,
      nvl(sum(decode(upper(result_code),'XLA_NO_JOURNAL',1)),0) status_nojournal_count
      FROM PSA_BC_XLA_EVENTS_GT;

Line: 164

      SELECT 'Y'
      FROM  PSA_BC_XLA_EVENTS_GT a
      WHERE not exists (SELECT 'x'
                        FROM   xla_ae_headers b
                        WHERE  b.event_id = a.event_id);

Line: 173

      SELECT *
      FROM   psa_bc_xla_events_gt;

Line: 177

      SELECT event_id
      FROM   psa_bc_xla_events_gt;

Line: 181

      SELECT xla_evnt.EVENT_ID,
             xla_evnt.ENTITY_ID,
             xla_evnt.APPLICATION_ID,
             xla_err.AE_HEADER_ID,
             xla_err.AE_LINE_NUM,
             xla_evnt.TRANSACTION_DATE,
             fnd_mesg.MESSAGE_NUMBER,
             fnd_mesg.MESSAGE_NAME,
             xla_err.ENCODED_MSG,
             xla_err.ERROR_SOURCE_CODE,
             xla_evnt.LEDGER_ID,
             xla_evnt.LEGAL_ENTITY_ID,
             xla_evnt.transaction_number DOCUMENT_REFERENCE,
             NULL BATCH_REFERENCE,
             to_char(xla_evnt.event_id) LINE_REFERENCE,
             SYSDATE CREATION_DATE,
             'Y' XLA_ERROR_FLAG
      FROM   psa_bc_xla_events_gt psa_evnt,
             xla_events_gt xla_evnt,
             xla_accounting_errors xla_err,
             fnd_new_messages fnd_mesg
      WHERE  psa_evnt.event_id = xla_evnt.event_id
        AND  xla_evnt.event_id = xla_err.event_id
        AND  fnd_mesg.application_id = 602
        AND  DECODE(xla_err.message_number, 0, -99, xla_err.message_number) = fnd_mesg.message_number (+)
        AND  userenv('LANG') =  fnd_mesg.language_code (+);

Line: 357

        SELECT count(*) INTO l_count
        FROM psa_bc_xla_events_gt;

Line: 381

        SELECT count(*) INTO l_count
        FROM psa_bc_xla_events_gt;

Line: 399

        UPDATE psa_bc_xla_events_gt
        SET result_code = 'XLA_UNPROCESSED';

Line: 401

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);

Line: 414

        DELETE from XLA_ACCT_PROG_EVENTS_GT;

Line: 415

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from XLA_ACCT_PROG_EVENTS_GT table before insertion: ' || SQL%ROWCOUNT );

Line: 416

        DELETE from xla_ae_headers_gt;

Line: 417

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_headers_gt table before insertion: ' || SQL%ROWCOUNT);

Line: 418

        DELETE from xla_ae_lines_gt;

Line: 419

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_ae_lines_gt table before insertion: ' || SQL%ROWCOUNT);

Line: 420

        DELETE from xla_validation_lines_gt;

Line: 421

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_validation_lines_gt table before insertion: ' || SQL%ROWCOUNT);

Line: 422

        DELETE from xla_evt_class_orders_gt;

Line: 423

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from xla_evt_class_orders_gt; table before insertion: ' || SQL%ROWCOUNT);

Line: 425

        DELETE from psa_option_details_gt;

Line: 426

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_option_details_gt table before insertion: ' || SQL%ROWCOUNT);

Line: 427

        DELETE from psa_bc_alloc_gt;

Line: 428

        psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows deleted from psa_bc_alloc_gt table before insertion: ' || SQL%ROWCOUNT);

Line: 434

        INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
        SELECT event_id FROM psa_bc_xla_events_gt;

Line: 436

        psa_utils.debug_other_string(g_state_level,l_path_name, 'Number of rows in inserted into XLA_ACCT_PROG_EVENTS_GT table: ' || SQL%ROWCOUNT );

Line: 481

         psa_acctg_errors_insert(l_psa_events,l_psa_acctg_errors);

Line: 491

               UPDATE psa_bc_xla_events_gt
               SET result_code = 'XLA_ERROR';

Line: 493

               psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);

Line: 505

                 UPDATE psa_bc_xla_events_gt
                 SET result_code = 'XLA_NO_JOURNAL'
                 WHERE result_code = 'XLA_UNPROCESSED';

Line: 508

                 psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);

Line: 519

                      UPDATE psa_bc_xla_events_gt
                      SET result_code = 'XLA_ERROR';

Line: 521

                      psa_utils.debug_other_string(g_state_level,l_path_name,'Number of rows updated of psa_bc_xla_events_gt: ' || SQL%ROWCOUNT);

Line: 707

            SELECT '1'
            FROM    xla_transaction_entities xte,
                    xla_ae_headers           xah,
                    xla_distribution_links   xdl,
                    xla_events               xe
            WHERE   xte.application_id      = cp_appl_id
                AND xte.entity_code         = cp_entity_code
                AND xte.source_id_int_1     = cp_source_id_int_1
                AND xte.entity_id           = xah.entity_id
                AND xah.event_id            = xdl.event_id
                AND xdl.source_distribution_type    = cp_source_dist_type
                AND xdl.source_distribution_id_num_1 = cp_source_dist_id_num_1
                AND xah.event_id            = xe.event_id
                AND xe.budgetary_control_flag = 'Y';

Line: 727

            SELECT xal.entered_cr, xal.accounted_cr
            FROM    xla_transaction_entities xte,
                    xla_ae_headers           xah,
                    xla_ae_lines             xal,
                    xla_events               xe
            WHERE   xte.application_id      = cp_appl_id
                AND xte.entity_code         = cp_entity_code
                AND xte.source_id_int_1     = cp_source_id_int_1
                AND xte.entity_id           = xah.entity_id
                AND xah.event_id            = xe.event_id
                AND xal.ae_header_id        = xah.ae_header_id
                AND xe.budgetary_control_flag = 'Y'
                AND xal.accounting_class_code = 'PURCHASE_ORDER'
                AND xal.accounting_date      > cp_r12_live_date;

Line: 761

        SELECT min(creation_date) INTO l_dist_creation_date
        FROM po_req_distributions_all
        WHERE distribution_id = p_distribution_id;

Line: 765

        SELECT min(creation_date) INTO l_dist_creation_date
        FROM po_distributions_all
        WHERE po_distribution_id = p_distribution_id;

Line: 769

        SELECT min(creation_date) INTO l_dist_creation_date
        FROM ap_invoice_distributions_all
        WHERE invoice_distribution_id = p_distribution_id;

Line: 1073

  DELETE FROM XLA_AE_HEADERS_GT;

Line: 1074

    psa_utils.debug_other_string(g_state_level,l_path_name, '1 XLA_AE_HEADERS_GT : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1075

  DELETE FROM XLA_AE_LINES_GT;

Line: 1076

    psa_utils.debug_other_string(g_state_level,l_path_name, '2 XLA_AE_LINES_GT : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1077

  DELETE FROM XLA_VALIDATION_HDRS_GT;

Line: 1078

psa_utils.debug_other_string(g_state_level,l_path_name, '3 XLA_VALIDATION_HDRS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1079

  DELETE FROM XLA_VALIDATION_LINES_GT;

Line: 1080

    psa_utils.debug_other_string(g_state_level,l_path_name, '4 XLA_VALIDATION_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1081

  DELETE FROM XLA_BAL_CTRL_CTRBS_GT;

Line: 1082

psa_utils.debug_other_string(g_state_level,l_path_name, '5 XLA_BAL_CTRL_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1083

  DELETE FROM XLA_BAL_PERIOD_STATS_GT;

Line: 1084

    psa_utils.debug_other_string(g_state_level,l_path_name, '6 XLA_BAL_PERIOD_STATS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1085

  DELETE FROM XLA_BAL_RECREATE_GT;

Line: 1087

XLA_BAL_RECREATE_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1088

  DELETE FROM XLA_BAL_ANACRI_LINES_GT;

Line: 1090

XLA_BAL_ANACRI_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1091

  DELETE FROM XLA_BAL_ANACRI_CTRBS_GT;

Line: 1093

XLA_BAL_ANACRI_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1094

  DELETE FROM XLA_BAL_SYNCHRONIZE_GT;

Line: 1096

XLA_BAL_SYNCHRONIZE_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1097

  DELETE FROM XLA_BAL_STATUSES_GT;

Line: 1099

XLA_BAL_STATUSES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1100

  DELETE FROM XLA_BAL_CTRL_LINES_GT;

Line: 1102

XLA_BAL_CTRL_LINES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1103

  DELETE FROM XLA_EVENTS_GT;

Line: 1105

: Deleted Row count :'||SQL%ROWCOUNT);

Line: 1106

  DELETE FROM XLA_EVT_CLASS_SOURCES_GT;

Line: 1108

XLA_EVT_CLASS_SOURCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1109

  DELETE FROM XLA_EVT_CLASS_ORDERS_GT;

Line: 1111

XLA_EVT_CLASS_ORDERS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1112

  DELETE FROM XLA_TAB_ERRORS_GT;

Line: 1114

XLA_TAB_ERRORS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1115

  DELETE FROM XLA_SEQ_JE_HEADERS_GT;

Line: 1117

XLA_SEQ_JE_HEADERS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1118

  DELETE FROM XLA_TAB_NEW_CCIDS_GT;

Line: 1120

XLA_TAB_NEW_CCIDS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1121

  DELETE FROM XLA_EXTRACT_OBJECTS_GT;

Line: 1123

XLA_EXTRACT_OBJECTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1124

  DELETE FROM XLA_REFERENCE_OBJECTS_GT;

Line: 1126

XLA_REFERENCE_OBJECTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1127

  DELETE FROM XLA_TRANSACTION_ACCTS_GT;

Line: 1129

XLA_TRANSACTION_ACCTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1130

  DELETE FROM XLA_UPG_LINE_CRITERIA_GT;

Line: 1132

XLA_UPG_LINE_CRITERIA_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1133

  DELETE FROM XLA_TRIAL_BALANCES_GT;

Line: 1135

XLA_TRIAL_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1136

  DELETE FROM XLA_ACCT_PROG_EVENTS_GT;

Line: 1138

XLA_ACCT_PROG_EVENTS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1139

  DELETE FROM XLA_ACCT_PROG_DOCS_GT;

Line: 1141

XLA_ACCT_PROG_DOCS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1142

  DELETE FROM XLA_MERGE_SEG_MAPS_GT;

Line: 1144

XLA_MERGE_SEG_MAPS_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1145

  DELETE FROM XLA_EVENTS_INT_GT;

Line: 1147

XLA_EVENTS_INT_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1148

  DELETE FROM XLA_REPORT_BALANCES_GT;

Line: 1150

XLA_REPORT_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1151

  DELETE FROM XLA_TB_BALANCES_GT;

Line: 1153

XLA_TB_BALANCES_GT    : Deleted Row count :'||SQL%ROWCOUNT);

Line: 1154

  DELETE FROM XLA_BAL_AC_CTRBS_GT;

Line: 1156

XLA_BAL_AC_CTRBS_GT    : Deleted Row count :'||SQL%ROWCOUNT);