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

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

Line: 7


    SELECT a.trx_id                     cash_receipt_history_id,
           a.cash_receipt_id            cash_receipt_id,
           a.trx_type                   trx_type,
           a.trx_date                   trx_date,
           a.status                     status,
           a.bank_account_amount        ba_amount,
           a.amount                     amount,
 	   --a.receipt_gl_date		receipt_gl_date,
	   a.gl_date			receipt_gl_date,
           a.exchange_rate_date         exchange_rate_date,
           a.exchange_rate_type         exchange_rate_type,
	   a.seq_id			seq_id
    --FROM   ce_222_txn_for_batch_v a
    FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id = rbatch_id
    AND    nvl(a.status, 'REMITTED') <> 'REVERSED'
    AND    a.application_id = 222
    AND    NVL(a.reconciled_status_flag, 'N') = 'N';

Line: 28


    SELECT a.trx_id                     cash_receipt_history_id,
           a.cash_receipt_id            cash_receipt_id,
           a.trx_type                   trx_type,
           a.trx_date                   trx_date,
           a.status                     status,
           a.bank_account_amount        ba_amount,
           a.amount                     amount,
 	   a.receipt_gl_date		receipt_gl_date,
           a.exchange_rate_date         exchange_rate_date,
           a.exchange_rate_type         exchange_rate_type,
           a.org_id
    FROM   ce_222_txn_for_batch_v a
    --FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id = rbatch_id
    AND    nvl(a.status, 'REMITTED') <> 'REVERSED';

Line: 48


    SELECT a.trx_id			check_id,
	   a.status			status_lookup_code,
	   'PAYMENT'			batch_trx_type,
	   a.cash_receipt_id		batch_app_id,
	   a.seq_id			seq_id
    --FROM   ce_200_transactions_v a
    FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id =  pbatch_id
    AND    nvl(a.status, 'NEGOTIABLE') <> 'VOIDED'
    AND    a.application_id = 200
    AND    NVL(a.reconciled_status_flag, 'N') = 'N'
    AND    EXISTS ( SELECT 1
                    FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
		    WHERE ACA.CHECK_ID   =a.trx_id
		      AND ACA.PAYMENT_INSTRUCTION_ID  = pbatch_id
		      AND IPA.PAYMENT_INSTRUCTION_ID (+)  = pbatch_id -- Bug # 8353600 Added Outer Join (+)
		      AND IPA.PAYMENT_ID (+) = ACA.PAYMENT_ID -- Bug # 8353600 Added Outer Join (+)
		      AND NVL(IPA.LOGICAL_GROUP_REFERENCE,'N') = NVL(pgroup_id,NVL(IPA.LOGICAL_GROUP_REFERENCE,'N')))
    UNION ALL
    SELECT a.trx_id,
	   a.status,
	   'CASHFLOW',
	   673,
	   a.seq_id
    --FROM   ce_260_cf_transactions_v a
    FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id =  pbatch_id
    AND    nvl(a.status, 'CANCELED') <> 'CANCELED'
    AND    a.application_id = 261
    AND    NVL(a.reconciled_status_flag, 'N') = 'N'
    AND    pgroup_id is null ;         -- FOR SEPA ER 6700007

Line: 83


    SELECT a.trx_id			check_id,
	   a.status			status_lookup_code,
	   'PAYMENT'			batch_trx_type,
	   a.cash_receipt_id		batch_app_id,
	   a.org_id			org_id,
	   a.legal_entity_id		legal_entity_id
    FROM   ce_200_transactions_v a
    --FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id =  pbatch_id
    AND    nvl(a.status, 'NEGOTIABLE') <> 'VOIDED'
  -- FOR SEPA ER 6700007
    AND    EXISTS ( SELECT 1
                    FROM iby_payments_all IPA ,AP_CHECKS_ALL ACA
		    WHERE ACA.CHECK_ID   =a.trx_id
		      AND ACA.PAYMENT_INSTRUCTION_ID  = pbatch_id
		      AND IPA.PAYMENT_INSTRUCTION_ID (+) = pbatch_id -- Bug # 8353600 Added Outer Join (+)
		      AND IPA.PAYMENT_ID (+) = ACA.PAYMENT_ID -- Bug # 8353600 Added Outer Join (+)
		      AND NVL(IPA.LOGICAL_GROUP_REFERENCE,'N') = NVL(pgroup_id,NVL(IPA.LOGICAL_GROUP_REFERENCE,'N')))
    --AND    a.application_id = 200
    --AND    NVL(a.reconciled_status_flag, 'N') = 'N'
    UNION ALL
    SELECT a.trx_id,
	   a.status,
	   'CASHFLOW',
	   673,
	   a.org_id,
	   a.legal_entity_id
    FROM   ce_260_cf_transactions_v a
    --FROM   ce_available_transactions_tmp a
    WHERE  a.batch_id =  pbatch_id
    AND    nvl(a.status, 'CANCELED') <> 'CANCELED'
    AND    pgroup_id is null ;         -- FOR SEPA ER 6700007

Line: 118


  CURSOR C_STATEMENT_LINE_SEQ IS SELECT ce_statement_lines_s.nextval from sys.dual;

Line: 187


  SELECT crh.status
  INTO   x_status
  FROM   ce_statement_recon_gt_v rec, --ce_statement_reconcils_all rec,
         ar_cash_receipt_history_all      crh,
         ar_cash_receipts_all             cr
  WHERE  rec.current_record_flag = 'Y'                          AND
         rec.status_flag = 'M'                                  AND
         rec.reference_type = 'RECEIPT'                         AND
         rec.reference_id   = crh.cash_receipt_history_id       AND
         crh.cash_receipt_id = cr.cash_receipt_id               AND
         cr.cash_receipt_id = cr_id;

Line: 225


|       update_line_unreconciled                                        |
|  DESCRIPTION                                                          |
|       Checks if the statement line is fully unreconciled              |
|       and updates the status accordingly                              |
 --------------------------------------------------------------------- */
PROCEDURE update_line_unreconciled (X_statement_line_id NUMBER) IS
   c_count_reconciled  NUMBER;

Line: 234


  	cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.update_line_unreconciled');

Line: 237


  SELECT count(*)
  INTO c_count_reconciled
  FROM CE_STATEMENT_RECONCILS_ALL
  WHERE statement_line_id = X_statement_line_id
  AND  current_record_flag = 'Y'
  AND  status_flag = 'M';

Line: 245


    CE_AUTO_BANK_CLEAR.update_line_status(X_statement_line_id,'UNRECONCILED');

Line: 248


  	cep_standard.debug('<


Line: 253

    	cep_standard.debug('EXCEPTION:  CE_AUTO_BANK_CLEAR1.update_line_unreconciled');

Line: 256

END update_line_unreconciled;

Line: 262

|       Inserts records into CE_STATEMENT_LINES .                       |
 --------------------------------------------------------------------- */
PROCEDURE create_statement_line IS
BEGIN
  IF l_DEBUG in ('Y', 'C') THEN
    cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.create_statement_line');

Line: 269

  CE_STAT_LINES_DML_PKG.Insert_Row(
        X_Row_Id                => CE_AUTO_BANK_MATCH.csl_rowid,
        X_statement_line_id     => CE_AUTO_BANK_MATCH.csl_statement_line_id,
        X_statement_header_id   => CE_AUTO_BANK_MATCH.csh_statement_header_id,
        X_line_number           => CE_AUTO_BANK_MATCH.csl_line_number,
        X_trx_date              => CE_AUTO_BANK_MATCH.csl_trx_date,
        X_trx_type              => CE_AUTO_BANK_MATCH.csl_trx_type,
        X_trx_status            => CE_AUTO_BANK_MATCH.trx_status,
        X_trx_code				=> NULL,
        X_effective_date        => CE_AUTO_BANK_MATCH.csl_effective_date,
        X_bank_trx_number       => CE_AUTO_BANK_MATCH.csl_bank_trx_number,
        X_trx_text              => NULL,
        X_customer_text         => NULL,
        X_invoice_text          => NULL,
        X_bank_account_text     => NULL,
        X_amount                => CE_AUTO_BANK_MATCH.csl_amount,
        X_charges_amount        => CE_AUTO_BANK_MATCH.csl_charges_amount,
        X_status                => 'RECONCILED',
        X_created_by            => NVL(FND_GLOBAL.user_id,-1),
        X_creation_date         => sysdate,
        X_last_updated_by       => NVL(FND_GLOBAL.user_id,-1),
        X_last_update_date      => sysdate,
        X_currency_code         => CE_AUTO_BANK_MATCH.csl_currency_code,
        X_original_amount       => CE_AUTO_BANK_MATCH.csl_original_amount,
        X_exchange_rate         => CE_AUTO_BANK_MATCH.csl_exchange_rate,
        X_exchange_rate_type    => CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
        X_exchange_rate_date    => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
        X_attribute_category    => NULL,
        X_attribute1            => NULL,
        X_attribute2            => NULL,
        X_attribute3            => NULL,
        X_attribute4            => NULL,
        X_attribute5            => NULL,
        X_attribute6            => NULL,
        X_attribute7            => NULL,
        X_attribute8            => NULL,
        X_attribute9            => NULL,
        X_attribute10           => NULL,
        X_attribute11           => NULL,
        X_attribute12           => NULL,
        X_attribute13           => NULL,
        X_attribute14           => NULL,
        X_attribute15           => NULL,
	-- 5916290: GDF Changes
        X_global_att_category   => NULL,
        X_global_attribute1     => NULL,
        X_global_attribute2     => NULL,
        X_global_attribute3     => NULL,
        X_global_attribute4     => NULL,
        X_global_attribute5     => NULL,
        X_global_attribute6     => NULL,
        X_global_attribute7     => NULL,
        X_global_attribute8     => NULL,
        X_global_attribute9     => NULL,
        X_global_attribute10    => NULL,
        X_global_attribute11    => NULL,
        X_global_attribute12    => NULL,
        X_global_attribute13    => NULL,
        X_global_attribute14    => NULL,
        X_global_attribute15    => NULL,
        X_global_attribute16    => NULL,
        X_global_attribute17    => NULL,
        X_global_attribute18    => NULL,
        X_global_attribute19    => NULL,
        X_global_attribute20    => NULL
		);

Line: 342

|       insert_reconciliation                                           |
|  DESCRIPTION                                                          |
|       Inserts records into CE_STATEMENT_RECONCILIATIONS.              |
 --------------------------------------------------------------------- */
PROCEDURE insert_reconciliation (
        Y_statement_line_id     NUMBER   ,
        Y_cleared_trx_type      VARCHAR2,
        Y_cleared_trx_id        NUMBER,
        Y_ar_cash_receipt_id    NUMBER,
        Y_reference_status      VARCHAR2,
        Y_auto_reconciled_flag  VARCHAR2,
        Y_status_flag           VARCHAR2,
        Y_amount                NUMBER  )   IS
  Y_rowid       VARCHAR2(100);

Line: 360

  	cep_standard.debug('>>CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 375

  	cep_standard.debug('call CE_STATEMENT_RECONS_PKG.insert_row cestmreb');

Line: 379

    CE_STATEMENT_RECONS_PKG.insert_row(
        X_row_id                => Y_rowid,
        X_statement_line_id     => Y_statement_line_id,
        X_reference_type        => Y_cleared_trx_type,
        X_reference_id          => Y_cleared_trx_id,
        X_je_header_id          => Y_ar_cash_receipt_id,
        X_org_id                => NULL,
        X_legal_entity_id       => NULL,
        X_reference_status      => Y_reference_status,
		X_amount                => Y_amount,
        X_status_flag           => Y_status_flag,
        X_action_flag           => 'C',
        X_current_record_flag   => 'Y',
        X_auto_reconciled_flag  => Y_auto_reconciled_flag,
        X_created_by            => nvl(FND_GLOBAL.user_id,-1),
        X_creation_date         => sysdate,
        X_last_updated_by       => nvl(FND_GLOBAL.user_id,-1),
        X_last_update_date      => sysdate,
        X_request_id            => nvl(FND_GLOBAL.conc_request_id,-1),
        X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
        X_program_id            => nvl(FND_GLOBAL.conc_program_id,-1),
        X_program_update_date   => sysdate);

Line: 403

      CE_STATEMENT_RECONS_PKG.insert_row(
        X_row_id                => Y_rowid,
        X_statement_line_id     => Y_statement_line_id,
        X_reference_type        => Y_cleared_trx_type,
        X_reference_id          => Y_cleared_trx_id,
        X_org_id                => null,
        X_legal_entity_id       => null,
        X_reference_status      => Y_reference_status,
        X_amount                => Y_amount,
        X_status_flag           => Y_status_flag,
        X_action_flag           => 'C',
        X_current_record_flag   => 'Y',
        X_auto_reconciled_flag  => Y_auto_reconciled_flag,
        X_created_by            => nvl(FND_GLOBAL.user_id,-1),
        X_creation_date         => sysdate,
        X_last_updated_by       => nvl(FND_GLOBAL.user_id,-1),
        X_last_update_date      => sysdate,
        X_request_id            => nvl(FND_GLOBAL.conc_request_id,-1),
        X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
        X_program_id            => nvl(FND_GLOBAL.conc_program_id,-1),
        X_program_update_date   => sysdate);

Line: 425

      CE_STATEMENT_RECONS_PKG.insert_row(
        X_row_id                => Y_rowid,
        X_statement_line_id     => Y_statement_line_id,
        X_reference_type        => Y_cleared_trx_type,
        X_reference_id          => Y_cleared_trx_id,
        X_org_id                => Y_org_id,
        X_legal_entity_id       => Y_legal_entity_id,
        X_reference_status      => Y_reference_status,
        X_amount                => Y_amount,
        X_status_flag           => Y_status_flag,
        X_action_flag           => 'C',
        X_current_record_flag   => 'Y',
        X_auto_reconciled_flag  => Y_auto_reconciled_flag,
        X_created_by            => nvl(FND_GLOBAL.user_id,-1),
        X_creation_date         => sysdate,
        X_last_updated_by       => nvl(FND_GLOBAL.user_id,-1),
        X_last_update_date      => sysdate,
        X_request_id            => nvl(FND_GLOBAL.conc_request_id,-1),
        X_program_application_id =>nvl(FND_GLOBAL.prog_appl_id,-1),
        X_program_id            => nvl(FND_GLOBAL.conc_program_id,-1),
        X_program_update_date   => sysdate);

Line: 450

  	cep_standard.debug('end call CE_STATEMENT_RECONS_PKG.insert_row');

Line: 455

     	cep_standard.debug('insert_reconciliation: ' || 'Insert CE_ABR_NSF_INFO warning.');

Line: 457

     CE_RECONCILIATION_ERRORS_PKG.insert_row(
	CE_AUTO_BANK_MATCH.csh_statement_header_id,
        CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NSF_INFO');

Line: 464

     	cep_standard.debug('insert_reconciliation: ' || 'Insert CE_TRX_DATE_CLEARED_DATE warning.');

Line: 466

     CE_RECONCILIATION_ERRORS_PKG.insert_row(
	CE_AUTO_BANK_MATCH.csh_statement_header_id,
        CE_AUTO_BANK_MATCH.csl_statement_line_id,
	'CE_TRX_DATE_CLEARED_DATE');

Line: 473

  	cep_standard.debug('<

Line: 478

    	cep_standard.debug('EXCEPTION: CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 481

END insert_reconciliation;

Line: 630

      update ce_available_transactions_tmp
      set    reconciled_status_flag = 'Y'
      where  seq_id = l_gt_seq_id;

Line: 756

      CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type      =>receipt_type,
        Y_cleared_trx_id        =>receipt_history_id,
        Y_ar_cash_receipt_id    =>receipt_id,
        Y_reference_status      =>receipt_status,
        Y_auto_reconciled_flag  =>auto_reconcile_flag,
        Y_status_flag           =>'M',
	Y_amount		=> amount_to_clear);

Line: 768

      CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');

Line: 913

      CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type      =>receipt_type,
        Y_cleared_trx_id        =>receipt_history_id,
        Y_ar_cash_receipt_id    =>receipt_id,
        Y_reference_status      =>receipt_status,
        Y_auto_reconciled_flag  =>auto_reconcile_flag,
        Y_status_flag           =>'M',
	Y_amount		=> amount_to_clear);

Line: 929

      SELECT h.statement_number || '/' || to_char(l.line_number)
      INTO   X_trx_number
      FROM   CE_STATEMENT_HEADERS h,
           CE_STATEMENT_LINES l
      WHERE  h.statement_header_id = l.statement_header_id AND
           l.statement_line_id   = X_statement_line_id;

Line: 1042

	SELECT 	catv.trx_id,
		catv.bank_account_amount,
                catv.seq_id
      --FROM 	ce_801_EFT_transactions_v catv
      FROM      ce_available_transactions_tmp catv
      WHERE       upper(LTrim(catv.batch_name)) =   /*12733547 added ltrim on batch number*/
		    upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
      AND	catv.trx_date = CE_AUTO_BANK_MATCH.csl_trx_date
      AND	catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
      AND		nvl(catv.status, 'C') <> 'V'
	and nvl(catv.batch_id, 0) = nvl(CE_AUTO_BANK_MATCH.trx_group,0)
      AND	catv.application_id = 802 -- for payroll eft 802 is application id bug 7242853
      AND	NVL(catv.reconciled_status_flag, 'N') = 'N';

Line: 1117

          update ce_available_transactions_tmp
          set    reconciled_status_flag = 'Y'
          where  seq_id = l_gt_seq_id;

Line: 1137

           p_last_updated_by    => NVL(FND_GLOBAL.user_id,-1),
           p_last_update_login  => NVL(FND_GLOBAL.user_id,-1),
           p_created_by         => NVL(FND_GLOBAL.user_id,-1) );

Line: 1150

  	cep_standard.debug('reconcile_pay_eft: call CE_AUTO_BANK_CLEAR1.insert_reconciliation ');

Line: 1153

      CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type      => cleared_trx_type,
        Y_cleared_trx_id        => cleared_trx_id,
        Y_ar_cash_receipt_id    => null,
        Y_reference_status      => null,
        Y_auto_reconciled_flag  => auto_reconciled_flag,
        Y_status_flag           => 'M',
        Y_amount                =>  amount_to_clear);

Line: 1164

          CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');

Line: 1169

		cep_standard.debug('reconcile_pay_eft: ' || '<<< End CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 1295

        CE_RECONCILIATION_ERRORS_PKG.insert_row(
	CE_AUTO_BANK_MATCH.csh_statement_header_id,
	CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_DOC_SEQUENCE_ERR');

Line: 1318

    select mo_global.GET_CURRENT_ORG_ID
    into current_org_id
    from dual;

Line: 1356

    SELECT  REMIT_BANK_ACCT_USE_ID
    INTO    X_REMIT_BANK_ACCT_USE_ID
    FROM   ar_receipt_methods rm,
           ar_receipt_method_accounts rma,
	   ce_bank_acct_uses cba
    WHERE
           rm.receipt_method_id = X_receipt_method_id
    AND    rma.receipt_method_id = rm.receipt_method_id
    AND    cba.bank_acct_use_id = rma.remit_bank_acct_use_id
    AND    cba.ar_use_enable_flag = 'Y'
    AND    cba.bank_account_id = X_bank_account_id;

Line: 1373

      CE_RECONCILIATION_ERRORS_PKG.insert_row(
	   CE_AUTO_BANK_MATCH.csh_statement_header_id,
	   CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_TEMP_AR_METHOD_ORG_INVALID');

Line: 1421

    SELECT      arh.status, arh.cash_receipt_history_id
    INTO        l_status, cash_receipt_history_id
    FROM        ar_cash_receipt_history_all arh --ar_cash_receipt_history arh
    WHERE       arh.cash_receipt_id = X_cr_id   AND
                arh.current_record_flag = 'Y';

Line: 1459

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type      => 'MISC',
        Y_cleared_trx_id        => cash_receipt_history_id,
        Y_ar_cash_receipt_id    => X_cr_id,
        Y_reference_status      => NULL,
        Y_auto_reconciled_flag  => 'N',
        Y_status_flag           => 'M',
	Y_amount                => X_bank_account_amount);

Line: 1583

          update ce_available_transactions_tmp
          set    reconciled_status_flag = 'Y'
          where  seq_id = l_gt_seq_id;

Line: 1592

 	cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 1594

      CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type              => batch_trx_type, -- 'PAYMENT',
        Y_cleared_trx_id                => check_id,
        Y_ar_cash_receipt_id            => NULL,
        Y_reference_status              => check_status,
        Y_auto_reconciled_flag          => auto_reconciled_flag,
        Y_status_flag                   => 'M');

Line: 1604

          CE_AUTO_BANK_MATCH.update_gt_reconciled_status (l_gt_seq_id, 'Y');

Line: 1623

 	cep_standard.debug('call CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 1627

      CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_cleared_trx_type              => batch_trx_type, -- 'PAYMENT',
        Y_cleared_trx_id                => check_id,
        Y_ar_cash_receipt_id            => NULL,
        Y_reference_status              => check_status,
        Y_auto_reconciled_flag          => auto_reconciled_flag,
        Y_status_flag                   => 'M');

Line: 1697

  	X_LAST_UPDATE_DATE      => sysdate,
  	X_LAST_UPDATED_BY       => NVL(FND_GLOBAL.user_id,-1),
  	X_LAST_UPDATE_LOGIN     => NVL(FND_GLOBAL.user_id,-1),
  	X_CREATED_BY            => NVL(FND_GLOBAL.user_id,-1),
  	X_CREATION_DATE         => sysdate,
  	X_PROGRAM_UPDATE_DATE   => sysdate,
  	X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
  	X_PROGRAM_ID            => NVL(FND_GLOBAL.conc_program_id,-1),
  	X_REQUEST_ID            => NVL(FND_GLOBAL.conc_request_id,-1),
  	X_CALLING_SEQUENCE      => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch '
        );

Line: 1731

        P_LAST_UPDATE_DATE        => sysdate,
        P_LAST_UPDATED_BY         => NVL(FND_GLOBAL.user_id,-1),
        P_LAST_UPDATE_LOGIN       => NVL(FND_GLOBAL.user_id,-1),
        P_CREATED_BY              => NVL(FND_GLOBAL.user_id,-1),
        P_CREATION_DATE           => sysdate,
        P_PROGRAM_UPDATE_DATE     => sysdate,
        P_PROGRAM_APPLICATION_ID  => NVL(FND_GLOBAL.prog_appl_id,-1),
        P_PROGRAM_ID              => NVL(FND_GLOBAL.conc_program_id,-1),
        P_REQUEST_ID              => NVL(FND_GLOBAL.conc_request_id,-1),
        P_CALLING_SEQUENCE        => 'CE_AUTO_BANK_CLEAR1.reconcile_pbatch',
	P_LOGICAL_GROUP_REFERENCE => pgroup_id);-- FOR SEPA ER 6700007

Line: 1749

       	cep_standard.debug('update ce_statement_reconcils_all');

Line: 1752

    update ce_statement_reconcils_all r
    set    amount =
	(select DECODE(c.currency_code,
                X_bank_currency_code, c.cleared_amount,
                nvl(c.cleared_base_amount, c.cleared_amount*CE_AUTO_BANK_CLEAR.G_exchange_rate))
         from ap_checks_all c
         where c.check_id = r.reference_id)
    where  statement_line_id = CE_AUTO_BANK_MATCH.csl_statement_line_id
    and    reference_type = 'PAYMENT'
    and    status_flag = 'M'
    and    current_record_flag = 'Y'
    and    amount is null;

Line: 1900

	  	  cep_standard.debug('call ARP_CASHBOOK.update_actual_value_date');

Line: 1903

	      ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
				to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));

Line: 1996

             X_LAST_UPDATE_DATE      => sysdate,
             X_LAST_UPDATED_BY       => NVL(FND_GLOBAL.user_id,-1),
             X_LAST_UPDATE_LOGIN     => NVL(FND_GLOBAL.user_id,-1),
             X_CREATED_BY            => NVL(FND_GLOBAL.user_id,-1),
             X_CREATION_DATE         => sysdate,
             X_PROGRAM_UPDATE_DATE   => sysdate,
             X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
             X_PROGRAM_ID            => NVL(FND_GLOBAL.conc_program_id,-1),
             X_REQUEST_ID            => NVL(FND_GLOBAL.conc_request_id,-1),
             X_CALLING_SEQUENCE      => 'CE_AUTO_BANK_CLEAR1.reconcile_stmt '
           );

Line: 2029

           p_last_updated_by    => NVL(FND_GLOBAL.user_id,-1),
           p_last_update_login  => NVL(FND_GLOBAL.user_id,-1),
           p_created_by         => NVL(FND_GLOBAL.user_id,-1) );

Line: 2065

       	cep_standard.debug('update ce_statement_reconcils_all');

Line: 2068

    update ce_statement_reconcils_all
    set current_record_flag = 'N'
    where statement_line_id in
          (x_trx_id, CE_AUTO_BANK_MATCH.csl_statement_line_id)
    and reference_type = tx_type
    and nvl(current_record_flag, 'N') = 'Y'
    and nvl(request_id, -999) <> nvl(FND_GLOBAL.conc_request_id,-1);

Line: 2076

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
           Y_statement_line_id     => x_trx_id,
           Y_cleared_trx_type      => 'STATEMENT',
           Y_cleared_trx_id        => CE_AUTO_BANK_MATCH.csl_statement_line_id,
           Y_ar_cash_receipt_id    => to_number(NULL),
           Y_reference_status      => reference_status,
           Y_auto_reconciled_flag  => auto_reconciled_flag,
           Y_status_flag           => 'M',
           Y_amount                => x_trx_amount );

Line: 2086

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
           Y_statement_line_id     => CE_AUTO_BANK_MATCH.csl_statement_line_id,
           Y_cleared_trx_type      => 'STATEMENT',
           Y_cleared_trx_id        => x_trx_id,
           Y_ar_cash_receipt_id    => to_number(NULL),
           Y_reference_status      => reference_status,
           Y_auto_reconciled_flag  => auto_reconciled_flag,
           Y_status_flag           => 'M',
           Y_amount                => x_trx_amount2);

Line: 2123

         CE_AUTO_BANK_CLEAR1.insert_reconciliation (
           Y_statement_line_id     => CE_AUTO_BANK_MATCH.csl_statement_line_id,
           Y_cleared_trx_type      => cleared_trx_type,
           Y_cleared_trx_id        => cleared_trx_id,
           Y_ar_cash_receipt_id    => ar_cash_receipt_id,
           Y_reference_status      => reference_status,
           Y_auto_reconciled_flag  => auto_reconciled_flag,
           Y_status_flag           => 'M',
           Y_amount                => x_trx_amount3);

Line: 2135

       		cep_standard.debug('update ce_statement_reconcils_all');

Line: 2138

	   update ce_statement_reconcils_all
           set    statement_line_Id = CE_AUTO_BANK_MATCH.csl_statement_line_id,
                  amount = x_trx_amount3
                  --amount = CE_AUTO_BANK_MATCH.corr_csl_amount
           where  statement_line_id = x_trx_id
           and    reference_type <> 'STATEMENT'
           and    reference_id = CE_AUTO_BANK_MATCH.trx_id
           and    nvl(request_id,-999) = nvl(FND_GLOBAL.conc_request_id,-1);

Line: 2166

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_statement_line_id     => CE_AUTO_BANK_MATCH.trx_id,
        Y_cleared_trx_type      => 'STATEMENT',
        Y_cleared_trx_id        => CE_AUTO_BANK_MATCH.csl_statement_line_id,
        Y_ar_cash_receipt_id    => ar_cash_receipt_id,
        Y_reference_status      => reference_status,
        Y_auto_reconciled_flag  => auto_reconciled_flag,
        Y_status_flag           => 'M',
        Y_amount                => CE_AUTO_BANK_MATCH.csl_amount);

Line: 2176

    CE_AUTO_BANK_CLEAR.update_line_status(
           CE_AUTO_BANK_MATCH.trx_id,'RECONCILED');

Line: 2179

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
        Y_statement_line_id     => CE_AUTO_BANK_MATCH.csl_statement_line_id,
        Y_cleared_trx_type      => 'STATEMENT',
        Y_cleared_trx_id        => CE_AUTO_BANK_MATCH.trx_id,
        Y_ar_cash_receipt_id    => ar_cash_receipt_id,
        Y_reference_status      => reference_status,
        Y_auto_reconciled_flag  => auto_reconciled_flag,
        Y_status_flag           => 'M',
        Y_amount                => x_trx_amount2);

Line: 2189

    CE_AUTO_BANK_CLEAR.update_line_status(
	CE_AUTO_BANK_MATCH.csl_statement_line_id, 'RECONCILED');

Line: 2368

            FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');

Line: 2422

                p_last_updated_by    => NVL(FND_GLOBAL.user_id,-1),
                p_last_update_login  => NVL(FND_GLOBAL.user_id,-1),
                p_created_by         => NVL(FND_GLOBAL.user_id,-1) );

Line: 2494

                    X_LAST_UPDATE_DATE      => sysdate,
                    X_LAST_UPDATED_BY       => NVL(FND_GLOBAL.user_id,-1),
                    X_LAST_UPDATE_LOGIN     => NVL(FND_GLOBAL.user_id,-1),
                    X_CREATED_BY            => NVL(FND_GLOBAL.user_id,-1),
                    X_CREATION_DATE         => sysdate,
                    X_PROGRAM_UPDATE_DATE   => sysdate,
                    X_PROGRAM_APPLICATION_ID=> NVL(FND_GLOBAL.prog_appl_id,-1),
                    X_PROGRAM_ID            => NVL(FND_GLOBAL.conc_program_id,-1),
                    X_REQUEST_ID            => NVL(FND_GLOBAL.conc_request_id,-1),
                    X_CALLING_SEQUENCE      => 'CE_AUTO_BANK_CLEAR1.reconcile_trx ');

Line: 2553

                            select name, gl_id_rec, cust_trx_type_id
                            into   cust_trx_type, cc_id, cust_trx_type_id
                            from   ra_cust_trx_types
                            where  type = 'DM'
                            and    post_to_gl = 'Y'
                            and    accounting_affect_flag = 'Y'
                            and    tax_calculation_flag = 'N'
                            and    rownum = 1
                            order by name, cust_trx_type_id;

Line: 2633

                            cep_standard.debug('reconcile_trx: call ARP_CASHBOOK.update_actual_value_date');

Line: 2634

                            ARP_CASHBOOK.update_actual_value_date(to_number(ar_cash_receipt_id),
                            to_date(to_char(CE_AUTO_BANK_MATCH.csl_effective_date,'YYYY/MM/DD'),'YYYY/MM/DD'));

Line: 2690

        cep_standard.debug('reconcile_trx: calling CE_AUTO_BANK_CLEAR1.insert_reconciliation');

Line: 2692

        CE_AUTO_BANK_CLEAR1.insert_reconciliation (
            Y_cleared_trx_type      => cleared_trx_type,
            Y_cleared_trx_id        => cleared_trx_id,
            Y_ar_cash_receipt_id    => ar_cash_receipt_id,
            Y_reference_status      => reference_status,
            Y_auto_reconciled_flag  => auto_reconciled_flag,
            Y_status_flag           => 'M',
            Y_amount                => amount_cleared);

Line: 2796

    select mo_global.GET_CURRENT_ORG_ID
    into current_org_id
    from dual;

Line: 2812

  select mo_global.GET_CURRENT_ORG_ID
  into current_org_id
  from dual;

Line: 2844

        X_LAST_UPDATE_DATE      => sysdate,
        X_LAST_UPDATED_BY       => NVL(FND_GLOBAL.user_id,-1),
        X_LAST_UPDATE_LOGIN     => NVL(FND_GLOBAL.user_id,-1),
        X_CREATED_BY            => NVL(FND_GLOBAL.user_id,-1),
        X_CREATION_DATE         => sysdate,
        X_PROGRAM_UPDATE_DATE   => to_date(NULL),
        X_PROGRAM_APPLICATION_ID=> to_number(NULL),
        X_PROGRAM_ID            => to_number(NULL),
        X_REQUEST_ID            => to_number(NULL),
        X_CALLING_SEQUENCE      => 'CE_AUTO_BANK_CLEAR1.unclear_process '
        );

Line: 2859

	-- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
	CE_AUTO_BANK_MATCH.update_gt_reconciled_status(200, trx_id, 'N');

Line: 2864

    SELECT NVL(arh.current_record_flag, 'N')
    INTO   p_current_record_flag
    FROM   ar_cash_receipt_history_all arh
    WHERE  arh.cash_receipt_history_id = trx_id;

Line: 2888

      update ce_available_transactions_tmp
      set    reconciled_status_flag = 'N'
      where  application_id = 222
      and    trx_id = trx_id
      and    status <> 'REVERSED';

Line: 2901

    CE_AUTO_BANK_MATCH.update_gt_reconciled_status(999, trx_id, 'N');

Line: 2914

	-- after unreconcile, update the reconciled_status of this trx in the GT table to 'N'
        CE_AUTO_BANK_MATCH.update_gt_reconciled_status(185, trx_id, 'N');

Line: 2922

          FND_MESSAGE.set_name( 'CE','CE_XTR_UPDATE_FAILED');

Line: 2953

    CE_AUTO_BANK_MATCH.update_gt_reconciled_status(261, trx_id, 'N');

Line: 2984

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
	Y_statement_line_id	=> trx_id,
        Y_cleared_trx_type      => clearing_trx_type,
        Y_cleared_trx_id        => CE_AUTO_BANK_MATCH.csl_statement_line_id,
        Y_ar_cash_receipt_id    => cash_receipt_id,
        Y_reference_status      => status,
        Y_auto_reconciled_flag  => 'N',
        Y_status_flag           => 'U');

Line: 2993

    CE_AUTO_BANK_CLEAR1.update_line_unreconciled(trx_id);

Line: 2996

    CE_AUTO_BANK_MATCH.update_gt_reconciled_status(260, trx_id, 'N');

Line: 3008

      CE_AUTO_BANK_MATCH.update_gt_reconciled_status(801, trx_id, 'N');

Line: 3010

      CE_AUTO_BANK_MATCH.update_gt_reconciled_status(802, trx_id, 'N');

Line: 3026

    CE_AUTO_BANK_CLEAR1.insert_reconciliation (
	Y_statement_line_id	=> CE_AUTO_BANK_MATCH.csl_statement_line_id,
	Y_cleared_trx_type	=> clearing_trx_type,
	Y_cleared_trx_id	=> trx_id,
	Y_ar_cash_receipt_id	=> cash_receipt_id,
	Y_reference_status	=> status,
	Y_auto_reconciled_flag	=> 'N',
	Y_status_flag		=> 'U');

Line: 3036

  	cep_standard.debug('call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');

Line: 3039

    CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);

Line: 3042

  	cep_standard.debug('end call CE_AUTO_BANK_CLEAR1.update_line_unreconciled');

Line: 3046

    CE_AUTO_BANK_CLEAR1.update_line_unreconciled(stmt_line_id);

Line: 3100

    insert_stmt  VARCHAR2(3000);

Line: 3105

    insert_stmt := 'INSERT INTO ce_available_transactions_tmp ' ||
	'(ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, ' ||
	'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, '||
	'TRX_ID, TRX_TYPE, TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, ' ||
	'CURRENCY_CODE, AMOUNT, BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, ' ||
	'GL_DATE, STATUS_DSP, STATUS, DESCRIPTION, TRX_DATE, CLEARED_DATE, ' ||
	'MATURITY_DATE, EXCHANGE_RATE_DATE, EXCHANGE_RATE_TYPE, ' ||
	'USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, BANK_CHARGES, BANK_ERRORS, '||
	'BATCH_NAME, BATCH_ID, AGENT_NAME, CUSTOMER_NAME, PAYMENT_METHOD, '||
	'VENDOR_NAME, CUSTOMER_ID, SUPPLIER_ID, REFERENCE_TYPE_DSP, '||
	'REFERENCE_TYPE, REFERENCE_ID, ACTUAL_AMOUNT_CLEARED, CREATION_DATE, '||
	'CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, '||
	'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
	'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
	'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
	'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
	'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
	'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID) ' ||
	'SELECT ROW_ID, MULTI_SELECT, BANK_ACCOUNT_ID, BANK_ACCOUNT_NAME, '||
	'BANK_ACCOUNT_NUM, BANK_NAME, BANK_BRANCH_NAME, TRX_ID, TRX_TYPE, ' ||
	'TYPE_MEANING, TRX_NUMBER, CHECK_NUMBER, CURRENCY_CODE, AMOUNT, '||
	'BANK_ACCOUNT_AMOUNT, AMOUNT_CLEARED, GL_DATE, STATUS_DSP, STATUS, ' ||
	'null, TRX_DATE, CLEARED_DATE, MATURITY_DATE, EXCHANGE_RATE_DATE, ' ||
	'EXCHANGE_RATE_TYPE, USER_EXCHANGE_RATE_TYPE, EXCHANGE_RATE, ' ||
	'BANK_CHARGES, BANK_ERRORS, BATCH_NAME, BATCH_ID, AGENT_NAME, ' ||
	'CUSTOMER_NAME, PAYMENT_METHOD, VENDOR_NAME, CUSTOMER_ID, ' ||
	'SUPPLIER_ID, REFERENCE_TYPE_DSP, REFERENCE_TYPE, REFERENCE_ID, ' ||
	'ACTUAL_AMOUNT_CLEARED, CREATION_DATE, CREATED_BY, ' ||
	'LAST_UPDATE_DATE, LAST_UPDATED_BY, REMITTANCE_NUMBER, ' ||
	'CASH_RECEIPT_ID, APPLICATION_ID, COUNT_CLEARED, BANK_CURRENCY_CODE, '||
	'TRX_CURRENCY_TYPE, CODE_COMBINATION_ID, PERIOD_NAME, '||
	'JOURNAL_ENTRY_NAME, DOCUMENT_NUMBER, JOURNAL_ENTRY_LINE_NUMBER, '||
	'CLEARING_TRX_TYPE, JOURNAL_CATEGORY, BASE_AMOUNT, RECEIPT_CLASS_ID, '||
	'RECEIPT_METHOD_ID, RECEIPT_CLASS_NAME, DEPOSIT_DATE, VALUE_DATE, ' ||
	'REVERSED_RECEIPT_FLAG, LEGAL_ENTITY_ID, ORG_ID FROM ' || X_table ||
	' WHERE ' || X_where_clause;

Line: 3143

		 insert_stmt,
		 DBMS_SQL.v7);