eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CE_ZBA_DEAL_GENERATION SQL Statements

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

Line: 10


        SELECT  cba.bank_account_id
        FROM    ce_bank_accounts cba
        WHERE   cba.bank_branch_id = p_bank_branch_id
        AND     cba.bank_account_id = NVL(p_bank_account_id, cba.bank_account_id)
        ORDER BY cba.bank_account_name;

Line: 24


        SELECT  csh.statement_header_id,
                csh.statement_number,
                csh.statement_date,
                csh.check_digits,
                csh.gl_date,
                cba.currency_code,
                cba.multi_currency_allowed_flag,
                cba.check_digits,
                csh.rowid,
                NVL(csh.statement_complete_flag,'N'),
                csh.org_id
        FROM    ce_bank_accounts cba,
                ce_statement_headers csh
        WHERE   cba.bank_account_id = NVL(p_bank_account_id,cba.bank_account_id)
        AND     cba.bank_account_id = csh.bank_account_id
        AND     csh.statement_number BETWEEN
                        NVL(p_statement_number_from,csh.statement_number)
                    AND NVL(p_statement_number_to,csh.statement_number)
        AND     csh.statement_date BETWEEN -- Bug 10102381 : Added
                        NVL(p_statement_date_from,csh.statement_date) -- Bug 11686554 Removed to_date
                    AND NVL(p_statement_date_to,csh.statement_date)   -- Bug 11686554 Removed to_date
        AND     NVL(csh.statement_complete_flag,'N') = 'N';

Line: 48


        SELECT  sl.rowid,
                sl.statement_line_id,
                cd.receivables_trx_id,
                cd.receipt_method_id,
                cd.create_misc_trx_flag,
                cd.matching_against,
                cd.correction_method,
                rm.name,
                sl.exchange_rate_type,
                sl.exchange_rate_date,
                sl.exchange_rate,
                sl.currency_code,
                sl.trx_type,
                decode(cd.PAYROLL_PAYMENT_FORMAT_ID,
                            null,   NVL(cd.reconcile_flag,'X'),
                            decode(cd.reconcile_flag, 'PAY',  'PAY_EFT',
                                                      NVL(cd.reconcile_flag,'X'))),
                'NONE',
                NULL,
                NULL,
                sl.original_amount,
                ppt.payment_type_name
        FROM    pay_payment_types ppt,
                ar_receipt_methods rm,
                ce_transaction_codes cd,
                ce_statement_lines sl
        WHERE   rm.receipt_method_id(+)      = cd.receipt_method_id
        AND     cd.transaction_code_id(+)    = sl.trx_code_id
        AND     cd.payroll_payment_format_id = ppt.payment_type_id (+)
        AND     csh_statement_date
                    between nvl(cd.start_date, csh_statement_date)
                    and     nvl(cd.end_date, csh_statement_date)
        AND     sl.status                    = 'UNRECONCILED'
        AND     sl.statement_header_id       = csh_statement_header_id
        AND     sl.trx_type                 IN ('SWEEP_IN', 'SWEEP_OUT')
        ORDER BY
            DECODE(sl.trx_type, 'NSF', 5, 'REJECTED', 5,
                decode(nvl(cd.matching_against,'MISC'), 'MISC', 3, 'MS', 2, 1)),
            DECODE(nvl(cd.matching_against,'MISC'), 'MISC', 0,
                to_char(sl.trx_date, 'J')) DESC;

Line: 170


    SELECT  statement_header_id
    INTO    x_statement_header_id
    FROM    ce_statement_headers
    WHERE   rowid = CE_ZBA_DEAL_GENERATION.csh_rowid
    FOR UPDATE OF statement_header_id NOWAIT;

Line: 216


    SELECT  statement_line_id,
            trx_date,
            trx_type,
            trx_code_id,
            bank_trx_number,
            invoice_text,
            bank_account_text,
            amount,
            NVL(charges_amount,0),
            currency_code,
            line_number,
            customer_text,
            effective_date,
            original_amount
    INTO    CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
            CE_ZBA_DEAL_GENERATION.csl_trx_date,
            CE_ZBA_DEAL_GENERATION.csl_trx_type,
            CE_ZBA_DEAL_GENERATION.csl_trx_code_id,
            CE_ZBA_DEAL_GENERATION.csl_bank_trx_number,
            CE_ZBA_DEAL_GENERATION.csl_invoice_text,
            CE_ZBA_DEAL_GENERATION.csl_bank_account_text,
            CE_ZBA_DEAL_GENERATION.csl_amount,
            CE_ZBA_DEAL_GENERATION.csl_charges_amount,
            CE_ZBA_DEAL_GENERATION.csl_currency_code,
            CE_ZBA_DEAL_GENERATION.csl_line_number,
            CE_ZBA_DEAL_GENERATION.csl_customer_text,
            CE_ZBA_DEAL_GENERATION.csl_effective_date,
            CE_ZBA_DEAL_GENERATION.csl_original_amount
    FROM    ce_statement_lines
    WHERE   rowid = CE_ZBA_DEAL_GENERATION.csl_rowid
    FOR UPDATE OF status NOWAIT;

Line: 275


    SELECT min(line_number)
    INTO   min_statement_line_num
    FROM   ce_statement_lines
    WHERE  statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;

Line: 280


    SELECT statement_line_id
    INTO   min_statement_line
    FROM   ce_statement_lines
    WHERE  line_number = min_statement_line_num
    AND     statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id;

Line: 345


        SELECT  statement_header_id
        FROM    ce_statement_headers
        WHERE   bank_account_id = p_ap_bank_account_id;

Line: 350


    SELECT count(1)
    INTO   code_row_count
    FROM   ce_transaction_codes
    WHERE  bank_account_id = p_ap_bank_account_id
    AND    trx_type in ('SWEEP_IN', 'SWEEP_OUT');

Line: 372


            SELECT count(1)
            INTO   line_row_count
            FROM   ce_statement_lines
            WHERE  statement_header_id = p_statement_header_id
            AND    trx_type in ('SWEEP_IN', 'SWEEP_OUT');

Line: 553


      CE_ZBA_DEAL_INF_PKG.delete_row(
          CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
          to_number(NULL));

Line: 558


      select count(1)
      into row_count
      from ce_statement_lines
      where statement_header_id = CE_ZBA_DEAL_GENERATION.csh_statement_header_id
      and trx_type in ('SWEEP_IN', 'SWEEP_OUT');

Line: 568


        CE_ZBA_DEAL_INF_PKG.insert_row(
            CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
            to_number(NULL),
            'CE_NO_SWEEP_STMT_LINE');

Line: 578


          SELECT  count(1)
          INTO    l_count
          FROM    ce_bank_accounts ba, ce_bank_acct_uses_all bau
          WHERE   ba.bank_account_id = CE_ZBA_DEAL_GENERATION.csh_bank_account_id
          AND     bau.bank_account_id = ba.bank_account_id
          AND     ba.xtr_use_allowed_flag = 'Y'
          AND     bau.authorized_flag = 'Y';

Line: 589


            CE_ZBA_DEAL_INF_PKG.insert_row (
                CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                to_number(NULL),
                'CE_XTR_INVALID_ACCT');

Line: 642


                select count(*)
                into   rec_status
                from   ce_statement_reconcils_all
                where  statement_line_id =  CE_ZBA_DEAL_GENERATION.csl_statement_line_id
                and    nvl(status_flag, 'U') = 'M'
                and    nvl(current_record_flag, 'Y') = 'Y';

Line: 656


                  CE_ZBA_DEAL_INF_PKG.delete_row(
                    CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                    CE_ZBA_DEAL_GENERATION.csl_statement_line_id);

Line: 666


                      CE_ZBA_DEAL_INF_PKG.insert_row (
                      CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                      CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                      'CE_ZBA_ZERO_AMOUNT');

Line: 676


                      CE_ZBA_DEAL_INF_PKG.insert_row (
                          CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                          CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                          'CE_ZBA_DIFF_CURRENCY');

Line: 686


                      SELECT bank_account_text
                      INTO   l_bank_acct_text
                      FROM   ce_statement_lines
                      WHERE  statement_line_id = CE_ZBA_DEAL_GENERATION.csl_statement_line_id;

Line: 691


                      SELECT cps. account_id
                      INTO   l_dst_bank_acct_id
                      FROM   ce_bank_accounts  cba,
                             ce_cashpools cp,
                             CE_CASHPOOL_SUB_ACCTS cps
                      WHERE  cba.bank_account_id = cp.conc_account_id
                      AND    cp.cashpool_id = cps.cashpool_id
                      AND    cps.type in ('ACCT','CONC','INV','FUND') /* bug 14140123 */
                      AND    cps.account_id IN (SELECT bank_account_id
                      FROM   ce_bank_accounts
                      WHERE  bank_account_num = l_bank_acct_text);

Line: 740


                        SELECT  TYPE
                        INTO    L_ACCT_TYPE
                        FROM    CE_CASHPOOL_SUB_ACCTS
                        WHERE   CASHPOOL_ID = X_CASHPOOL_ID
                        AND     ACCOUNT_ID =  CE_ZBA_DEAL_GENERATION.csh_bank_account_id;

Line: 746


                        SELECT  TYPE
                        INTO    L_OFFSET_ACCT_TYPE
                        FROM    CE_CASHPOOL_SUB_ACCTS
                        WHERE   CASHPOOL_ID = X_CASHPOOL_ID
                        AND     ACCOUNT_ID =  X_OFFSET_BANK_ACCOUNT_ID;

Line: 761


                            CE_ZBA_DEAL_INF_PKG.insert_row (
                                CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                'CE_ZBA_NO_FUND_SWEEP_IN');

Line: 769


                            CE_ZBA_DEAL_INF_PKG.insert_row (
                                CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                'CE_ZBA_NO_INV_SWEEP_OUT');

Line: 777


                            CE_ZBA_DEAL_INF_PKG.insert_row (
                                CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                'CE_ZBA_NO_FUND_SWEEP_IN_SUB');

Line: 785


                            CE_ZBA_DEAL_INF_PKG.insert_row (
                                CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                'CE_ZBA_NO_INV_SWEEP_OUT_SUB');

Line: 824


                                INSERT INTO CE_ZBA_DEAL_MESSAGES(
                                    application_short_name,
                                    statement_header_id,
                                    statement_line_id,
                                    creation_date,
                                    created_by,
                                    deal_type,
                                    deal_num,
                                    transaction_num,
                                    cashpool_id,
                                    cashflows_created_flag,
                                    offset_deal_num,
                                    offset_transaction_num,
                                    deal_status_flag)
                                    VALUES (
                                    'CE',
                                    CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                    CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                    sysdate,
                                    NVL(FND_GLOBAL.user_id,-1),
                                    l_deal_type,
                                    l_deal_num,
                                    l_transaction_num,
                                    CE_ZBA_DEAL_GENERATION.p_cashpool_id,
                                    l_cashflows_created_flag,
                                    l_offset_deal_num,
                                    l_offset_transaction_num,
                                    'Y');

Line: 857


                                        INSERT INTO CE_ZBA_DEAL_MESSAGES(
                                            application_short_name,
                                            statement_header_id,
                                            statement_line_id,
                                            message_name,
                                            creation_date,
                                            created_by,
                                            deal_status_flag,
                                            cashpool_id)
                                            VALUES (
                                            'CE',
                                            CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                                            CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                                            FND_MSG_PUB.get(1, FND_API.G_FALSE),
                                            sysdate,
                                            NVL(FND_GLOBAL.user_id,-1),
                                            'N',
                                            CE_ZBA_DEAL_GENERATION.p_cashpool_id);

Line: 875


                                        FND_MSG_PUB.delete_msg(1);

Line: 882


                        CE_ZBA_DEAL_INF_PKG.insert_row(
                            CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                            CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                            'CE_NO_CASHPOOL_MATCH');

Line: 890


                      CE_ZBA_DEAL_INF_PKG.insert_row(
                        CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                        CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                        'CE_BAT_INVALID_BACCNTS');

Line: 898


                    CE_ZBA_DEAL_INF_PKG.insert_row(
                        CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                        CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
                        'CE_LINE_LOCKED');

Line: 917


              CE_ZBA_DEAL_INF_PKG.delete_row(
                  CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                  error_statement_line_id);

Line: 920


              CE_ZBA_DEAL_INF_PKG.insert_row(
                  CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
                  error_statement_line_id,
                  'CE_STATEMENT_COMPLETED');

Line: 927


            CE_ZBA_DEAL_INF_PKG.delete_row(CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));

Line: 928


            CE_ZBA_DEAL_INF_PKG.insert_row(
              CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
              to_number(NULL),
              'CE_LOCK_STATEMENT_HEADER_ERR');

Line: 936


          CE_ZBA_DEAL_INF_PKG.delete_row(
              CE_ZBA_DEAL_GENERATION.csh_statement_header_id, to_number(NULL));

Line: 938


          CE_ZBA_DEAL_INF_PKG.insert_row(
              CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
              to_number(NULL),
              'CE_CHECK_DIGITS');