APPS.XTR_NOTIONAL_BANK_ACCOUNTS SQL Statements

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

Line: 14

 *     If an existing cash pool is being updated, then the corresponding
 *     dummy bank account in xtr_bank_accounts will be updated.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *     p_cashpool_id                  	Cashpool ID of the Notional Cash Pool
 *					that is being created or updated.
 *     p_bank_account_id		Bank Account ID from CE_BANK_ACCOUNTS
 *					of the Concentration Account of the
 *					Cashpool.
 *   IN/OUT:
 *     x_return_status                  Return status after the call. The
 *                                      status can be Y for success or N for
 *					error.
 *   OUT:
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   01-26-2005    Rajesh Jose        	o Created.
 *
 */

-- R12 Modified the reference to AP_BANK_ACCOUNTS and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
PROCEDURE modify_xtr_bank_accounts(
	p_cashpool_id		IN	CE_CASHPOOLS.CASHPOOL_ID%TYPE,
	p_bank_account_id	IN	CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
	x_return_status		IN OUT NOCOPY VARCHAR2)
IS
 cursor cashpool_exists IS
	SELECT 	cashpool_id
	FROM	xtr_bank_accounts
	WHERE 	cashpool_id = p_cashpool_id
	AND	setoff_account_yn = 'Y';

Line: 56

		update_xtr_bank_account(p_cashpool_id, p_bank_account_id,
			x_return_status);

Line: 82

 *					that is being created or updated.
 *     p_bank_account_id		Bank Account ID from CE_BANK_ACCOUNTS
 *					of the Concentration Account of the
 *					Cashpool.
 *   IN/OUT:
 *     x_return_status                  Return status after the call. The
 *                                      status can be Y for success or N for
 *					error.
 *   OUT:
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   01-26-2005    Rajesh Jose        	o Created.
 */
-- R12 Modified the reference to AP_BANK_ACCOUNTS_ALL and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
PROCEDURE create_xtr_bank_account(
	p_cashpool_id		IN	CE_CASHPOOLS.CASHPOOL_ID%TYPE,
	p_bank_account_id	IN	CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
	x_return_status		IN OUT NOCOPY VARCHAR2)
IS
 cursor	account_details IS
	SELECT	bank_code, party_code, currency, year_calc_type,
		portfolio_code, interest_calculation_basis,
		rounding_type, day_count_type, code_combination_id
	FROM	XTR_BANK_ACCOUNTS
	WHERE	ce_bank_account_id
			= p_bank_account_id;

Line: 114

	SELECT  account_number
	FROM	XTR_BANK_ACCOUNTS
	WHERE	account_number = p_acct_number
	AND	party_code = p_party_code;

Line: 120

	SELECT	substrb(name,1,80), substrb(UPPER(name),1,20)
	FROM	ce_cashpools
	WHERE	cashpool_id = p_cashpool_id;

Line: 125

	SELECT	dealer_code
	FROM	xtr_dealer_codes
	WHERE	user_id = FND_GLOBAL.USER_ID;

Line: 145

 l_updated_by		XTR_DEALER_CODES.DEALER_CODE%TYPE;

Line: 174

	l_updated_by := l_created_by;

Line: 181

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,19)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 193

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,18)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 205

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,17)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 217

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,16)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 229

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,15)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 242

				SELECT	REPLACE(
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					rpad(
					l_new_acct_number, 20, l_dummy_char),
					substrb
					(rpad
					(l_new_acct_number,20, l_dummy_char),
					1,14)||l_counter)
				INTO	l_new_acct_number
				FROM	DUAL;

Line: 260

		INSERT INTO XTR_BANK_ACCOUNTS(
			account_number, setoff_account_yn,
			bank_code, party_code, bank_short_code,
			currency,  location, street, year_calc_type,
			portfolio_code, interest_calculation_basis,
			rounding_type, day_count_type,
			code_combination_id, pricing_model,
			authorised, party_type, cashpool_id,
			created_by, created_on,
			updated_by, updated_on)
		VALUES
			(l_new_acct_number, 'Y',
		 	l_bank_code, l_party_code, l_bank_short_code,
		 	l_currency, 'SETOFF ACCOUNT', 'SETOFF ACCOUNT',
		 	l_year_calc_type,
		 	l_portfolio_code, l_int_calc_basis,
		 	l_rounding_type, l_day_count_type,
		 	l_code_combination_id, 'NO_REVAL',
		 	'Y', 'C', p_cashpool_id,
		 	nvl(l_created_by, FND_GLOBAL.user_id), sysdate,
		 	nvl(l_updated_by, FND_GLOBAL.user_id), sysdate);

Line: 291

 * FUNCTION update_xtr_bank_account
 *
 * DESCRIPTION
 *     Updates the dummy account in XTR_BANK_ACCOUNTS
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *     p_cashpool_id                  	Cashpool ID of the Notional Cash Pool
 *					that is being created or updated.
 *     p_bank_account_id		Bank Account ID from CE_BANK_ACCOUNTS
 *					of the Concentration Account of the
 *					Cashpool.
 *   IN/OUT:
 *     x_return_status                  Return status after the call. The
 *                                      status can be Y for success or N for
 *					error.
 *   OUT:
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   01-26-2005    Rajesh Jose        	o Created.
 */
-- R12 Modified the reference to AP_BANK_ACCOUNTS and AP_BANK_ACCOUNT_ID to CE_BANK_ACCOUNTS and CE_BANK_ACCOUNT_ID
PROCEDURE update_xtr_bank_account(
	p_cashpool_id		IN	CE_CASHPOOLS.CASHPOOL_ID%TYPE,
	p_bank_account_id	IN	CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
	x_return_status		IN OUT NOCOPY VARCHAR2)
IS

 cursor	account_changed IS
	SELECT 	conc_account_id
	FROM	CE_CASHPOOLS
	WHERE	cashpool_id = p_cashpool_id;

Line: 330

	SELECT	xba.bank_code, xba.year_calc_type,
		xba.interest_calculation_basis, xba.rounding_type,
		xba.day_count_type, xba.code_combination_id,
		xpin.set_of_books_id
	FROM	XTR_BANK_ACCOUNTS xba, XTR_PARTY_INFO xpin
	WHERE	ce_bank_account_id =
			p_bank_account_id
	AND	xpin.party_code = xba.party_code
	AND	xpin.party_type = 'C';

Line: 341

	SELECT	dealer_code
	FROM	xtr_dealer_codes
	WHERE	user_id = FND_GLOBAL.USER_ID;

Line: 358

 l_updated_by		XTR_DEALER_CODES.DEALER_CODE%TYPE;

Line: 376

		SELECT	set_of_books_id
		INTO	l_orig_set_of_books_id
		FROM	XTR_PARTY_INFO xpin, XTR_BANK_ACCOUNTS xba
		WHERE	xpin.party_code = xba.party_code
		AND	ce_bank_account_id
			= l_conc_account_id
		AND	xpin.party_type = 'C';

Line: 391

		INTO	l_updated_by;

Line: 396

			UPDATE	XTR_BANK_ACCOUNTS
			SET	bank_code = l_bank_code,
				year_calc_type = l_year_calc_type,
				interest_calculation_basis = l_int_calc_basis,
				rounding_type = l_rounding_type,
				day_count_type = l_day_count_type,
				code_combination_id = l_code_combination_id,
				updated_by = nvl(l_updated_by,
						FND_GLOBAL.user_id),
				updated_on = sysdate
			WHERE	cashpool_id = p_cashpool_id
			AND	setoff_account_yn = 'Y';

Line: 415

			UPDATE	XTR_BANK_ACCOUNTS
			SET	bank_code = l_bank_code,
				year_calc_type = l_year_calc_type,
				interest_calculation_basis = l_int_calc_basis,
				rounding_type = l_rounding_type,
				day_count_type = l_day_count_type,
				updated_by = nvl(l_updated_by,
						FND_GLOBAL.user_id),
				updated_on = sysdate
			WHERE	cashpool_id = p_cashpool_id
			AND	setoff_account_yn = 'Y';

Line: 434

END update_xtr_bank_account;