The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max (deal_number) from XTR_DEAL_DATE_AMOUNTS;
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;
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;
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;
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;
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;
l_updated_flag VARCHAR2(1);
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;
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;
SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
INTO l_cf_req_details_id
FROM DUAL;
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);
insert_transaction_details(l_cf_req_details_id, sysdate,
'', 0, '', 0, 0, 'N',
l_message_name);
SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
INTO l_cf_req_details_id
FROM DUAL;
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);
insert_transaction_details(l_cf_req_details_id, sysdate,
'', 0, '', 0, 0, 'N',
l_message_name);
SELECT XTR_CFLOW_REQUEST_DETAILS_S.NEXTVAL
INTO l_cf_req_details_id
FROM DUAL;
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);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
l_updated_flag = 'Y') THEN
OPEN check_journal(l_batch_id);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
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;
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);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
l_updated_flag = 'Y') THEN
OPEN check_journal(l_batch_id);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
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;
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);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
l_updated_flag = 'Y') THEN
OPEN check_journal(l_batch_id);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
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;
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);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
l_updated_flag = 'Y') THEN
OPEN check_journal(l_batch_id);
l_updated_flag := 'Y';
l_updated_flag := 'N';
'XTR_CFLOW_NOT_UPDATED';
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;
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);
* 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);
* 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);
END XTR_UPDATE_SETTLEMENT_ACCOUNTS;