APPS.CE_PURGE_TRX_CODES_XML SQL Statements

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

Line: 30

            SELECT SUBSTR(BNK.party_name,0,50) INTO g_bank_name
            FROM hz_parties BNK WHERE BNK.party_id = p_bank_id;

Line: 40

            SELECT SUBSTR(BRN.party_name,0,50) INTO g_branch_name
            FROM hz_parties BRN WHERE BRN.party_id = p_bank_branch_id;

Line: 50

            SELECT CBA.bank_account_num INTO g_account_num
            FROM ce_bank_accounts CBA
            WHERE CBA.bank_account_id = p_bank_acct_id;

Line: 61

            SELECT substr(LKP.meaning,0,50) INTO g_account_type
            FROM ce_lookups LKP
            WHERE LKP.lookup_type = 'BANK_ACCOUNT_TYPE'
              AND LKP.lookup_code = p_acct_type;

Line: 73

            SELECT substr(LKP.meaning,0,50) INTO g_process_option
            FROM ce_lookups LKP
            WHERE LKP.lookup_type = 'PURGE_TXCD_PROCESS_OPTION'
              AND LKP.lookup_code = p_process_option;

Line: 89

   |    This procedures deletes the bank transaction codes setup for        |
   |    a set of bank accounts.                                             |
   +========================================================================*/
    PROCEDURE purge_trx_codes
    IS
        l_conc_request_id   NUMBER(15);

Line: 108

        INSERT INTO ce_trx_codes_util_gt (
            status,
            request_id,
            trx_code_id,
            trx_code,
            bank_account_id,
            description,
            trx_type
        )
        SELECT  'DELETED',
                l_conc_request_id,
                CTC.transaction_code_id,
                CTC.trx_code,
                CTC.bank_account_id,
                CTC.description,
                CTC.trx_type
         FROM    CE_TRANSACTION_CODES CTC, CE_BANK_ACCOUNTS_V CBA
        WHERE   -- fetch codes as per input parameters
                DECODE(p_process_option, 'COPIED', CTC.request_id, 'ALL', 1) =
                DECODE(p_process_option, 'COPIED', NVL(p_request_id, CTC.request_id), 'ALL', 1)
            AND  CTC.bank_account_id = CBA.bank_account_id
            AND  CBA.bank_account_id = NVL(p_bank_acct_id, CBA.bank_account_id)
            AND  CBA.bank_branch_id = NVL(p_bank_branch_id, CBA.bank_branch_id)
            AND  CBA.bank_id = NVL(p_bank_id, CBA.bank_id)
            AND ((CBA.bank_account_type IS NULL AND p_acct_type IS NULL)
                OR CBA.bank_account_type = NVL(p_acct_type, CBA.bank_account_type))
            -- code should not be used in bank statement lines
            AND NOT EXISTS (
                SELECT NULL
                FROM ce_statement_lines CSL, ce_statement_headers CSH
                WHERE CSL.statement_header_id = CSH.statement_header_id
                AND  CSH.bank_account_id = CTC.bank_account_id
                AND  CSL.trx_type = CTC.trx_type
                AND  CSL.trx_code = CTC.trx_code)
            -- code should not be used in archived lines
            AND NOT EXISTS (
                SELECT NULL
                FROM ce_arch_lines CAL
                WHERE CAL.trx_code_id = CTC.transaction_code_id)
            -- code should not be used in statment cashflow mappings
            AND NOT EXISTS (
                SELECT NULL
                FROM ce_je_mappings JEM
                WHERE JEM.TRX_CODE_ID = CTC.TRANSACTION_CODE_ID
            )
            -- 8892534: code should not be used in intra-day statements
            AND NOT EXISTS (
                SELECT NULL
                FROM ce_intra_stmt_lines ISL
                WHERE ISL.trx_code_id = CTC.transaction_code_id
            )
            -- 8892534: code should not be used in archived intra-day statements
            AND NOT EXISTS (
                SELECT NULL
                FROM ce_arch_intra_lines ASL
                WHERE ASL.trx_code_id = CTC.transaction_code_id
            );

Line: 167

        DELETE ce_transaction_codes CTC
        WHERE EXISTS(
            SELECT 1
            FROM ce_trx_codes_util_gt GT
            WHERE GT.trx_code_id = CTC.transaction_code_id);

Line: 198

        SELECT count(*)
        INTO G_DELETE_COUNT
        FROM ce_trx_codes_util_gt GT
        WHERE GT.request_id = FND_GLOBAL.conc_request_id;

Line: 203

        debug_log('g_delete_count::'||g_delete_count);

Line: 227

        DELETE ce_trx_codes_util_gt;