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_CP_PRIORDAY SQL Statements

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

Line: 59


    SELECT WBA.bank_account_id,
           WBA.currency_code,
           OPEN.statement_date
    FROM   CE_CP_OPEN_BAL_V		OPEN,
           CE_CP_WS_BA_V		WBA
    WHERE  WBA.worksheet_header_id = p_ws_id
    AND    WBA.bank_account_id = OPEN.bank_account_id
    AND    OPEN.statement_date < CE_CP_PRIORDAY.G_as_of_date
    AND    OPEN.next_stmt_date >= CE_CP_PRIORDAY.G_as_of_date;

Line: 70


    SELECT bank_account_id,
	   currency_code,
           statement_date
    FROM   CE_CP_OPEN_BAL_V
    WHERE  statement_date < CE_CP_PRIORDAY.G_as_of_date
    AND    next_stmt_date >= CE_CP_PRIORDAY.G_as_of_date;

Line: 78


    SELECT worksheet_header_id
    FROM   CE_CP_WORKSHEET_HEADERS
    WHERE  pd_flag = 'Y'
    AND    (CE_CP_PRIORDAY.G_worksheet_header_id is null
           OR worksheet_header_id = CE_CP_PRIORDAY.G_worksheet_header_id);

Line: 93


      DELETE CE_CP_PRIORDAY_BALANCES
      WHERE  worksheet_header_id = -1
      AND    as_of_date <= trunc(sysdate);

Line: 99


      DELETE CE_CP_PRIORDAY_BALANCES
      WHERE  worksheet_header_id = -1
      AND    as_of_date = CE_CP_PRIORDAY.G_as_of_date;

Line: 116


      INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'HEAD',
          0,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 144


    SELECT -sum(decode(currency_code, l_currency_code, amount, base_amount))
    INTO   tmp_balance
    FROM   CE_AP_FC_PAYMENTS_V
    WHERE  bank_account_id = l_bank_account_id
    AND    nvl(actual_value_date, nvl(anticipated_value_date,
		nvl(maturity_date, payment_date))) > l_statement_date
    AND    nvl(actual_value_date, nvl(anticipated_value_date,
		nvl(maturity_date, payment_date)))
		< CE_CP_PRIORDAY.G_as_of_date;

Line: 156


        INSERT INTO CE_CP_PRIORDAY_BALANCES
            (worksheet_header_id,
            as_of_date,
            bank_account_id,
            balance_date,
            source_type,
            balance,
            last_update_date,
            last_updated_by,
            last_update_login,
            creation_date,
            created_by)
          VALUES (-1,
            CE_CP_PRIORDAY.G_as_of_date,
            l_bank_account_id,
            l_statement_date,
            'APP',
            tmp_balance,
            trunc(sysdate),
            -1,
            -1,
            trunc(sysdate),
            -1);

Line: 185


    SELECT sum(decode(currency_code, l_currency_code, amount, base_amount))
    INTO   tmp_balance
    FROM   CE_AR_FC_RECEIPTS_V
    WHERE  bank_account_id = l_bank_account_id
    AND	  cash_activity_date > l_statement_date
    AND    cash_Activity_date < CE_CP_PRIORDAY.G_as_of_date;

Line: 194


        INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'ARR',
          tmp_balance,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 223


    SELECT sum(amount)
    INTO   tmp_balance
    FROM   CE_XTR_CASHFLOWS_V
    WHERE  bank_account_id = l_bank_account_id
    AND    trx_date > l_statement_date
    AND    trx_date < CE_CP_PRIORDAY.G_as_of_date;

Line: 232


        INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'XTR',
          tmp_balance,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 261


    SELECT -sum(decode(currency_code, l_currency_code, amount, base_amount))
    INTO   tmp_balance
    FROM   CE_PAY_FC_PAYROLL_V
    WHERE  bank_account_id = l_bank_account_id
    AND    trx_date > l_statement_date
    AND    trx_date < CE_CP_PRIORDAY.G_as_of_date;

Line: 270


        INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'PAY',
          tmp_balance,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 297


    SELECT sum(decode(currency_code, l_currency_code, cashflow_amount, base_amount))
    INTO   tmp_balance
    FROM   CE_CE_CASHFLOWS_V
    WHERE  cash_activity_date > l_statement_date
    AND    cash_activity_date < CE_CP_PRIORDAY.G_as_of_date
    AND	   source_trxn_type <> 'STMT'
    AND	   cashflow_direction = 'RECEIPT';

Line: 307


        INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'CEI',
          tmp_balance,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 336


    SELECT sum(decode(currency_code, l_currency_code, cashflow_amount, base_amount))
    INTO   tmp_balance
    FROM   CE_CE_CASHFLOWS_V
    WHERE  cash_activity_date > l_statement_date
    AND    cash_activity_date < CE_CP_PRIORDAY.G_as_of_date
    AND	   source_trxn_type <> 'STMT'
    AND	   cashflow_direction = 'PAYMENT';

Line: 346


        INSERT INTO CE_CP_PRIORDAY_BALANCES
          (worksheet_header_id,
          as_of_date,
          bank_account_id,
          balance_date,
          source_type,
          balance,
          last_update_date,
          last_updated_by,
          last_update_login,
          creation_date,
          created_by)
        VALUES (-1,
          CE_CP_PRIORDAY.G_as_of_date,
          l_bank_account_id,
          l_statement_date,
          'CEO',
          tmp_balance,
          trunc(sysdate),
          -1,
          -1,
          trunc(sysdate),
          -1);

Line: 390


      DELETE CE_CP_PRIORDAY_BALANCES
      WHERE  worksheet_header_id = l_ws_id
      AND    as_of_date <= trunc(sysdate);

Line: 394


      DELETE CE_CP_PRIORDAY_BALANCES
      WHERE  worksheet_header_id = l_ws_id
      AND    as_of_date = CE_CP_PRIORDAY.G_as_of_date;

Line: 412


      SELECT -sum(decode(AP.currency_code, l_currency_code,
		AP.amount, AP.base_amount))
      INTO   tmp_balance
      FROM   CE_AP_FC_PAYMENTS_V AP,
	     CE_CP_WORKSHEET_LINES WSL
      WHERE  WSL.worksheet_header_id = l_ws_id
      AND    WSL.source_type = 'APP'
      AND    WSL.include_flag = 'Y'
      AND    WSL.cut_off_days is not null
      AND    AP.bank_account_id = l_bank_account_id
      AND    AP.status = 'NEGOTIABLE'
      AND    nvl(AP.actual_value_date, nvl(AP.anticipated_value_date,
		nvl(AP.maturity_date, AP.payment_date)))
		> l_statement_date - WSL.cut_off_days
      AND    nvl(AP.actual_value_date, nvl(AP.anticipated_value_date,
		nvl(AP.maturity_date, AP.payment_date)))
		<= l_statement_date
      AND    (WSL.payment_method is null OR
		AP.payment_method = WSL.payment_method);

Line: 434


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'APPOD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 462


      SELECT	sum(decode(ARR.currency_code, l_currency_code,
			ARR.amount, ARR.base_amount))
      INTO	tmp_balance
      FROM	CE_AR_FC_RECEIPTS_V	ARR,
		CE_CP_WORKSHEET_LINES	WSL
      WHERE	WSL.worksheet_header_id = l_ws_id
      AND	WSL.source_type = 'ARR'
      AND	WSL.include_flag = 'Y'
      AND	WSL.cut_off_days is not null
      AND       ARR.bank_account_id = l_bank_account_id
      AND       ARR.status not in ('CLEARED', 'RISK_ELIMINATED')
      AND	ARR.cash_activity_date 	> l_statement_date - WSL.cut_off_days
      AND	ARR.cash_activity_date	<= l_statement_date
      AND	(WSL.receipt_method_id is null OR
			ARR.receipt_method_id = WSL.receipt_method_id);

Line: 481


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'ARROD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 509


      SELECT	sum(XTR.amount)
      INTO	tmp_balance
      FROM	CE_CP_WORKSHEET_LINES	WSL,
		CE_XTR_CASHFLOWS_V	XTR
      WHERE	WSL.worksheet_header_id = l_ws_id
      AND	WSL.source_type = 'XTI'
      AND	WSL.include_flag = 'Y'
      AND	WSL.cut_off_days is not null
      AND	XTR.bank_account_id = l_bank_account_id
      AND	XTR.amount >= 0
      AND	XTR.reconciled_reference is null
      AND	XTR.trx_date > l_statement_date - WSL.cut_off_days
      AND	XTR.trx_date <= l_statement_date
      AND	(WSL.trx_type is null OR XTR.deal_type = WSL.trx_type)
      AND	(WSL.indicative_flag = 'Y' OR NOT (XTR.dda_deal_type = 'EXP'
			AND XTR.dda_deal_subtype = 'INDIC'));

Line: 528


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'XTIOD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 556


      SELECT	sum(XTR.amount)
      INTO	tmp_balance
      FROM	CE_CP_WORKSHEET_LINES	WSL,
		CE_XTR_CASHFLOWS_V	XTR
      WHERE	WSL.worksheet_header_id = l_ws_id
      AND	WSL.source_type = 'XTO'
      AND	WSL.include_flag = 'Y'
      AND	WSL.cut_off_days is not null
      AND	XTR.bank_account_id = l_bank_account_id
      AND	XTR.amount < 0
      AND	XTR.reconciled_reference is null
      AND	XTR.trx_date > l_statement_date - WSL.cut_off_days
      AND	XTR.trx_date <= l_statement_date
      AND	(WSL.trx_type is null OR XTR.deal_type = WSL.trx_type)
      AND	(WSL.indicative_flag = 'Y' OR NOT (XTR.dda_deal_type = 'EXP'
			AND XTR.dda_deal_subtype = 'INDIC'));

Line: 575


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'XTOOD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 605


      SELECT -sum(decode(CEI.currency_code, l_currency_code,
		CEI.cashflow_amount, CEI.base_amount))
      INTO   tmp_balance
      FROM   CE_CE_CASHFLOWS_V CEI,
	     CE_CP_WORKSHEET_LINES WSL
      WHERE  WSL.worksheet_header_id = l_ws_id
      AND    WSL.source_type = 'CEI'
      AND    WSL.include_flag = 'Y'
      AND    WSL.cut_off_days is not null
      AND    CEI.cashflow_bank_account_id = l_bank_account_id
      AND    CEI.cashflow_status = 'CREATED'
      AND    CEI.cash_activity_date
		> l_statement_date - WSL.cut_off_days
      AND    CEI.cash_activity_date
		<= l_statement_date
      AND    (WSL.trxn_subtype_code_id is null OR
		CEI.trxn_subtype_code_id = WSL.trxn_subtype_code_id)
      AND    CEI.cashflow_direction = 'RECEIPT'
      AND    CEI.source_trxn_type <> 'STMT';

Line: 628


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'CEIOD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 661


      SELECT -sum(decode(CEO.currency_code, l_currency_code,
		CEO.cashflow_amount, CEO.base_amount))
      INTO   tmp_balance
      FROM   CE_CE_CASHFLOWS_V CEO,
	     CE_CP_WORKSHEET_LINES WSL
      WHERE  WSL.worksheet_header_id = l_ws_id
      AND    WSL.source_type = 'CEO'
      AND    WSL.include_flag = 'Y'
      AND    WSL.cut_off_days is not null
      AND    CEO.cashflow_bank_account_id = l_bank_account_id
      AND    CEO.cashflow_status = 'CREATED'
      AND    CEO.cash_activity_date
		> l_statement_date - WSL.cut_off_days
      AND    CEO.cash_activity_date
		<= l_statement_date
      AND    (WSL.trxn_subtype_code_id is null OR
		CEO.trxn_subtype_code_id = WSL.trxn_subtype_code_id)
      AND    CEO.cashflow_direction = 'PAYMENT'
      AND    CEO.source_trxn_type <> 'STMT';

Line: 683


          INSERT INTO CE_CP_PRIORDAY_BALANCES
	    (worksheet_header_id,
	    as_of_date,
	    bank_account_id,
            balance_date,
	    source_type,
	    balance,
	    last_update_date,
	    last_updated_by,
	    last_update_login,
	    creation_date,
	    created_by)
          VALUES (l_ws_id,
	    CE_CP_PRIORDAY.G_as_of_date,
	    l_bank_account_id,
            l_statement_date,
	    'CEOOD',
	    tmp_balance,
	    trunc(sysdate),
	    -1,
	    -1,
	    trunc(sysdate),
	    -1);

Line: 715


      INSERT INTO CE_CP_PRIORDAY_BALANCES
	(worksheet_header_id,
	as_of_date,
	bank_account_id,
        balance_date,
	source_type,
	balance,
	last_update_date,
	last_updated_by,
	last_update_login,
	creation_date,
	created_by)
      VALUES (l_ws_id,
	CE_CP_PRIORDAY.G_as_of_date,
	-1,
        trunc(CE_CP_PRIORDAY.G_as_of_date),
	'HEAD',
	0,
	trunc(sysdate),
	-1,
	-1,
	trunc(sysdate),
	-1);