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: 11


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: 23


	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	to_char(to_date(csh.statement_date,'YYYY/MM/DD'),'J')
		BETWEEN NVL(to_char(to_date(p_statement_date_from,'YYYY/MM/DD'),'J'),1)
		AND NVL(to_char(to_date(p_statement_date_to,'YYYY/MM/DD'),'J'),3442447)
	AND 	NVL(csh.statement_complete_flag,'N') = 'N';

Line: 47


	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: 182


  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: 229


  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: 290


  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: 295


  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: 366


         SELECT statement_header_id
         FROM ce_statement_headers
         WHERE bank_account_id = p_ap_bank_account_id;

Line: 371


   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: 392


     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: 562


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

Line: 567


        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: 576


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

Line: 583


	    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: 591


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

Line: 617


	      -- Read in all the lines on the statement for the selected bank
	      -- account.
	      --
    		cep_standard.debug('>>CE_ZBA_DEAL_GENERATION.Opening line_cursor');

Line: 646


		select count(*)
		into   rec_status
		--from   ce_statement_reconciliations

		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: 662


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

Line: 669


                       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: 678


                         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: 733


  			  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: 763


			    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: 781


			    FND_MSG_PUB.delete_msg(1);

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_NO_CASHPOOL_MATCH');

Line: 791


		    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: 809


	      CE_ZBA_DEAL_INF_PKG.delete_row(
		  CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
		  error_statement_line_id);

Line: 812


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

Line: 818


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

Line: 820


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

Line: 827


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

Line: 829


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