DBA Data[Home] [Help]

APPS.XTR_UPDATE_SETTLEMENT_ACCOUNTS SQL Statements

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

Line: 78

select max (deal_number) from XTR_DEAL_DATE_AMOUNTS;
Line: 86

SELECT 	deal_date_amount_id, deal_number,
	amount_date, amount_type, cashflow_amount,
	settle, batch_id, transaction_number, deal_type
FROM xtr_deal_date_amounts
WHERE account_no = p_current_bank_account
AND amount_date >= p_date
AND amount_date <= p_default_end_date
AND deal_type = p_deal_type
AND company_code = p_company_code
AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
Line: 103

SELECT 	deal_date_amount_id, deal_number,
	amount_date, amount_type, cashflow_amount,
	settle, batch_id, transaction_number, deal_type
FROM xtr_deal_date_amounts
WHERE account_no = p_current_bank_account
AND amount_date >= p_date
AND amount_date <= p_default_end_date
AND company_code = p_company_code
AND deal_type NOT IN ('CA', 'IAC')
AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
Line: 120

SELECT deal_date_amount_id, deal_number,
	amount_date, amount_type, cashflow_amount,
	settle, batch_id, transaction_number, deal_type
FROM xtr_deal_date_amounts
WHERE cparty_account_no = p_current_bank_account
AND amount_date >= p_date
AND amount_date <= p_default_end_date
AND company_code = p_company_code
AND deal_type NOT IN ('CA', 'IAC')
AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
Line: 137

SELECT 	deal_date_amount_id, deal_number,
	amount_date, amount_type, cashflow_amount,
	settle, batch_id, transaction_number, deal_type
FROM xtr_deal_date_amounts
WHERE cparty_account_no = p_current_bank_account
AND amount_date >= p_date
AND amount_date <= p_default_end_date
AND deal_type = p_deal_type
AND company_code = p_company_code
AND deal_number BETWEEN p_int_deal_no_from AND p_int_deal_no_to;
Line: 149

SELECT 	'Y'
FROM 	xtr_batches b, xtr_batch_events e
WHERE 	b.batch_id  = e.batch_id
AND	e.event_code = 'JRNLGN'
AND	b.batch_id = p_batch_id;
Line: 174

l_updated_flag		VARCHAR2(1);
Line: 200

 SELECT xban.currency, xpv.party_type
 INTO	l_current_currency, l_party_type
 FROM 	xtr_bank_accounts xban, xtr_parties_v xpv
 WHERE  xban.account_number = p_current_bank_account
 AND	xban.party_code = p_party_code
 AND	xpv.party_code = xban.party_code;
Line: 207

 SELECT	currency, authorised
 INTO	l_new_currency, l_new_authorized
 FROM	xtr_bank_accounts
 WHERE	account_number = p_new_bank_account
 AND	party_code = p_party_code;
Line: 214

	SELECT 	XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
	INTO	l_cf_req_details_id
	FROM	DUAL;
Line: 217

	insert_request_details( l_cf_req_details_id,
			l_request_id, p_party_code,
			p_current_bank_account, p_new_bank_account,
			p_deal_type, p_deal_number_from,
			p_deal_number_to, p_int_start_date,
			p_int_end_date, p_include_journalized);
Line: 224

	insert_transaction_details(l_cf_req_details_id, sysdate,
			'', 0, '', 0, 0, 'N',
			l_message_name);
Line: 236

		SELECT 	XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
		INTO	l_cf_req_details_id
		FROM	DUAL;
Line: 239

		insert_request_details( l_cf_req_details_id,
				l_request_id, p_party_code,
				p_current_bank_account, p_new_bank_account,
				p_deal_type, p_deal_number_from,
				p_deal_number_to, p_int_start_date,
				p_int_end_date, p_include_journalized);
Line: 246

		insert_transaction_details(l_cf_req_details_id, sysdate,
				'', 0, '', 0, 0, 'N',
				l_message_name);
Line: 258

	SELECT	XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
	INTO	l_cf_req_details_id
	FROM	DUAL;
Line: 261

	insert_request_details(l_cf_req_details_id, l_request_id,
				p_party_code, p_current_bank_account,
				p_new_bank_account, p_deal_type,
				p_deal_number_from, p_deal_number_to,
				p_int_start_date, p_int_end_date,
				p_include_journalized);
Line: 298

				l_updated_flag := 'Y';
Line: 301

					l_updated_flag := 'N';
Line: 303

						'XTR_CFLOW_NOT_UPDATED';
Line: 306

				    l_updated_flag = 'Y') THEN
					OPEN check_journal(l_batch_id);
Line: 314

						   l_updated_flag := 'Y';
Line: 318

						   l_updated_flag := 'N';
Line: 320

							'XTR_CFLOW_NOT_UPDATED';
Line: 324

				IF l_updated_flag = 'Y' THEN
					UPDATE xtr_deal_date_amounts
					set account_no = p_new_bank_account
					where deal_date_amount_id =
						l_deal_date_amount_id;
Line: 331

				insert_transaction_details(
					l_cf_req_details_id,
					l_amount_date,
					l_amount_type,
					l_cashflow_amount,
					l_deal_type,
					l_deal_number,
					l_transaction_number,
					l_updated_flag,
					l_message_name);
Line: 368

				l_updated_flag := 'Y';
Line: 371

					l_updated_flag := 'N';
Line: 373

						'XTR_CFLOW_NOT_UPDATED';
Line: 376

				    l_updated_flag = 'Y') THEN
					OPEN check_journal(l_batch_id);
Line: 384

						   l_updated_flag := 'Y';
Line: 388

						   l_updated_flag := 'N';
Line: 390

							'XTR_CFLOW_NOT_UPDATED';
Line: 394

				IF l_updated_flag = 'Y' THEN
					UPDATE xtr_deal_date_amounts
					set account_no = p_new_bank_account
					where deal_date_amount_id =
						l_deal_date_amount_id;
Line: 401

				insert_transaction_details(
					l_cf_req_details_id,
					l_amount_date,
					l_amount_type,
					l_cashflow_amount,
					l_deal_type,
					l_deal_number,
					l_transaction_number,
					l_updated_flag,
					l_message_name);
Line: 451

					l_updated_flag := 'Y';
Line: 454

						l_updated_flag := 'N';
Line: 456

							'XTR_CFLOW_NOT_UPDATED';
Line: 459

				    	    l_updated_flag = 'Y') THEN
					   OPEN check_journal(l_batch_id);
Line: 467

						   l_updated_flag := 'Y';
Line: 471

						   l_updated_flag := 'N';
Line: 473

							'XTR_CFLOW_NOT_UPDATED';
Line: 477

					IF l_updated_flag = 'Y' THEN
						Update XTR_DEAL_DATE_AMOUNTS
						Set cparty_account_no =
							p_new_bank_account
						Where deal_date_amount_id =
							l_deal_date_amount_id;
Line: 486

					insert_transaction_details(
						l_cf_req_details_id,
						l_amount_date,
						l_amount_type,
						l_cashflow_amount,
						l_deal_type,
						l_deal_number,
						l_transaction_number,
						l_updated_flag,
						l_message_name);
Line: 538

					l_updated_flag := 'Y';
Line: 541

						l_updated_flag := 'N';
Line: 543

							'XTR_CFLOW_NOT_UPDATED';
Line: 546

				    	    l_updated_flag = 'Y') THEN
					   OPEN check_journal(l_batch_id);
Line: 554

						   l_updated_flag := 'Y';
Line: 558

						   l_updated_flag := 'N';
Line: 560

							'XTR_CFLOW_NOT_UPDATED';
Line: 564

					IF l_updated_flag = 'Y' THEN
						Update XTR_DEAL_DATE_AMOUNTS
						Set cparty_account_no =
							p_new_bank_account
						Where deal_date_amount_id =
							l_deal_date_amount_id;
Line: 573

					insert_transaction_details(
						l_cf_req_details_id,
						l_amount_date,
						l_amount_type,
						l_cashflow_amount,
						l_deal_type,
						l_deal_number,
						l_transaction_number,
						l_updated_flag,
						l_message_name);
Line: 610

 * PROCEDURE insert_request_details
 *
 * DESCRIPTION
 *     	Inserts request details into XTR_CFLOW_REQUEST_DETAILS table so that
 *	the execution report can be run at any time.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *	p_cashflow_request_details_id	The identifier for the record.
 *	p_request_id			The Request ID of the Concurrent
 *					Request that was run to update the
 *					Settlement Accounts.
 *     	p_party_code                  	Party Code Parameter from the request.
 *     	p_current_bank_account		Current Bank Account Number Parameter
 *					from the request.
 *	p_new_bank_account		New Bank Account Number Parameter
 *					from request.
 *	p_start_date			Start Date Parameter from the request.
 *	p_end_date			End Date Parameter from the request.
 *	p_deal_type			Deal Type Parameter from the request.
 *	p_deal_number_from		Deal Number From Parameter from the
 *					request.
 *	p_deal_number_to		Deal Number To Parameter from the
 *					request.
 *	p_inc_journalized		Include Journalized Transactions
 *					Parameter from the request.
 *   IN/OUT:
 *   OUT:
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *
 *   06-Jun-2005    Rajesh Jose        	o Created.
 */
PROCEDURE Insert_Request_Details(
	p_cashflow_request_details_id IN
		XTR_CFLOW_REQUEST_DETAILS.CASHFLOW_REQUEST_DETAILS_ID%TYPE,
	p_request_id 		IN	NUMBER,
	p_party_code 		IN	XTR_PARTY_INFO.PARTY_CODE%TYPE,
	p_current_bank_account 	IN	XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
	p_new_bank_account 	IN	XTR_BANK_ACCOUNTS.ACCOUNT_NUMBER%TYPE,
	p_deal_type 		IN	XTR_DEALS.DEAL_TYPE%TYPE,
	p_deal_number_from 	IN	XTR_DEALS.DEAL_NO%TYPE,
	p_deal_number_to 	IN	XTR_DEALS.DEAL_NO%TYPE,
	p_start_date 		IN	DATE,
	p_end_date 		IN	DATE,
	p_inc_journalized 	IN
			XTR_CFLOW_REQUEST_DETAILS.INCLUDE_JOURNALIZED_FLAG%TYPE)
IS
BEGIN
	INSERT INTO XTR_CFLOW_REQUEST_DETAILS
	(cashflow_request_details_id, request_id, party_code,
	 account_no_from, account_no_to, deal_type,
	 deal_number_from, deal_number_to, starting_cflow_date,
	 ending_cflow_date, include_journalized_flag,
	 created_by, creation_date, last_updated_by, last_update_date,
	 last_update_login)
	VALUES
	(p_cashflow_request_details_id, p_request_id, p_party_code,
	 p_current_bank_account, p_new_bank_account, p_deal_type,
	 p_deal_number_from, p_deal_number_to, p_start_date,
	 p_end_date, p_inc_journalized,
	 G_user_id, G_create_date, G_user_id, G_create_date,
	 G_user_id);
Line: 681

 * PROCEDURE insert_transaction_details
 *
 * DESCRIPTION
 *	Inserts details of transactions updated by the concurrent request
 *	so that the Execution Report shows accurate data at any point of time.
 *
 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
 *
 * ARGUMENTS
 *   IN:
 *	p_cashflow_request_details_id	Foreign key reference from
 *					XTR_CFLOW_REQUEST_DETAILS.
 *	p_amount_date			The amount date of the cashflow.
 *	p_amount_type			The type of cashflow.
 *	p_cashflow_amount		The amount of cash flow.
 *	p_deal_type			The Deal Type for the record.
 *	p_deal_number			The Deal Number for the record.
 *	p_transaction_number		The Transaction Number for the record.
 *	p_updated_flag			Flag indicating whether the record was
 *					updated or not.
 *	p_message_name			Message to be shown if record was not
 *					updated. Conveys the reason why the
 *					record was not updated.
 *   IN/OUT:
 *   OUT:
 *
 * NOTES
 *
 * MODIFICATION HISTORY
 *   06-Jun-2005    Rajesh Jose        	o Created.
 *
 */

PROCEDURE Insert_Transaction_Details(
	p_cashflow_request_details_id 	IN
		XTR_CFLOW_REQUEST_DETAILS.CASHFLOW_REQUEST_DETAILS_ID%TYPE,
	p_amount_date		IN	DATE,
	p_amount_type		IN	XTR_DEAL_DATE_AMOUNTS.AMOUNT_TYPE%TYPE,
	p_cashflow_amount	IN
				XTR_DEAL_DATE_AMOUNTS.CASHFLOW_AMOUNT%TYPE,
	p_deal_type		IN	XTR_DEAL_DATE_AMOUNTS.DEAL_TYPE%TYPE,
	p_deal_number		IN	XTR_DEALS.DEAL_NO%TYPE,
	p_transaction_number	IN
				XTR_DEAL_DATE_AMOUNTS.TRANSACTION_NUMBER%TYPE,
	p_updated_flag		IN	VARCHAR2,
	p_message_name		IN	VARCHAR2)
IS
BEGIN
	INSERT INTO XTR_CFLOW_UPDATED_RECORDS
	(cashflow_request_details_id, amount_date, amount_type,
	 cashflow_amount, deal_type, deal_number,
	 transaction_number, updated_flag, message_name,
	 created_by, creation_date, last_updated_by, last_update_date,
	 last_update_login)
	VALUES
	(p_cashflow_request_details_id, p_amount_date, p_amount_type,
	 p_cashflow_amount, p_deal_type, p_deal_number,
	 p_transaction_number, p_updated_flag, p_message_name,
	 G_user_id, G_create_date, G_user_id, G_create_date,
	 G_user_id);
Line: 744

END XTR_UPDATE_SETTLEMENT_ACCOUNTS;