[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT proposed_transfer_id,
sub_account_id,
conc_account_id,
transfer_amount,
cashpool_id
FROM ce_proposed_transfers
WHERE status = c_run_id;
SELECT nvl(effective_date_from,to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS')),
nvl(effective_date_to,to_date(p_as_of_date,'YYYY/MM/DD HH24:MI:SS'))
INTO l_effective_date_from, l_effective_date_to
FROM ce_cashpools
WHERE cashpool_id = p_transfer.cashpool_id;
DELETE ce_proposed_transfers
WHERE proposed_transfer_id = p_transfer.proposed_transfer_id;
DELETE ce_proposed_transfers
WHERE proposed_transfer_id = p_transfer.proposed_transfer_id;
DELETE FROM ce_proposed_transfers
WHERE status = p_run_id;
SELECT authorization_bat
INTO l_authorization_bat
FROM ce_cashpools
WHERE cashpool_id = X_cashpool_id;
CE_ZBA_DEAL_INF_PKG.insert_row (
CE_ZBA_DEAL_GENERATION.csh_statement_header_id,
CE_ZBA_DEAL_GENERATION.csl_statement_line_id,
'CE_INVALID_CASHPOOL_FOR_XTR');
SELECT decode(nvl(single_conc_account_flag,'Y'),'Y',conc_account_id, fund_conc_account_id),
currency_code
INTO l_conc_account_id,
l_currency_code
FROM ce_cashpools
WHERE cashpool_id = X_cashpool_id;
SELECT trx_type INTO l_trx_type
FROM ce_statement_lines
WHERE statement_line_id=X_statement_line_id;
SELECT tst.transaction_sub_type_name
INTO l_trx_subtype
FROM ce_trxns_subtype_codes tst, ce_cashpools cp
WHERE cp.trxn_subtype_code_id = tst.trxn_subtype_code_id(+)
AND cp.cashpool_id = X_cashpool_id;
SELECT account_owner_org_id
INTO l_from_le_id
FROM ce_bank_accounts
WHERE bank_account_id = X_from_bank_account_id;
SELECT account_owner_org_id
INTO l_to_le_id
FROM ce_bank_accounts
WHERE bank_account_id = X_to_bank_account_id;
INSERT INTO ce_leveling_messages(
/*01*/ leveling_message_id,
/*02*/ request_id,
/*03*/ sub_account_id,
/*04*/ conc_account_id,
/*05*/ transfer_amount,
/*06*/ message_name,
/*07*/ message_text,
/*08*/ deal_type,
/*09*/ deal_no,
/*10*/ trx_number,
/*11*/ offset_deal_no,
/*12*/ offset_trx_number,
/*13*/ success_flag,
/*14*/ cashpool_id,
/*15*/ created_by,
/*16*/ creation_date,
/*17*/ last_updated_by,
/*18*/ last_update_date,
/*19*/ last_update_login
) VALUES (
/*01*/ CE_LEVELING_MESSAGES_S.nextval,
/*02*/ nvl(X_request_id,-1),
/*03*/ l_party_account_id,
/*04*/ l_company_account_id,
/*05*/ X_amount,
/*06*/ l_message_name,
/*07*/ l_message_text,
/*08*/ l_deal_type,
/*09*/ l_deal_no,
/*10*/ l_trx_number,
/*11*/ l_offset_deal_no,
/*12*/ l_offset_trx_number,
/*13*/ l_success_flag,
/*14*/ X_cashpool_id,
/*15*/ nvl(fnd_global.user_id,-1),
/*16*/ sysdate,
/*17*/ nvl(fnd_global.user_id,-1),
/*18*/ sysdate,
/*19*/ nvl(fnd_global.user_id,-1)
);
FND_MSG_PUB.delete_msg(1);
UPDATE ce_cashpools
SET parent_cashpool_id = p_parent_cashpool_id
WHERE cashpool_id = p_cashpool_id;
INSERT INTO ce_cashpool_sub_accts(
/*01*/cashpool_sub_acct_id,
/*02*/cashpool_id,
/*03*/type,
/*04*/account_id,
/*05*/party_code,
/*06*/legal_entity_id,
/*07*/nested_parent_pool_id,
/*08*/created_by,
/*09*/creation_date,
/*10*/last_updated_by,
/*11*/last_update_date,
/*12*/last_update_login)
SELECT
/*01*/CE_CASHPOOL_SUB_ACCTS_S.nextval,
/*02*/p_parent_cashpool_id,
/*03*/'NEST',
/*04*/sub.account_id,
/*05*/sub.party_code,
/*06*/sub.legal_entity_id,
/*07*/sub.cashpool_id,
/*08*/nvl(fnd_global.user_id,-1),
/*09*/sysdate,
/*10*/nvl(fnd_global.user_id,-1),
/*11*/sysdate,
/*12*/nvl(fnd_global.user_id,-1)
FROM ce_cashpool_sub_accts sub
WHERE sub.cashpool_id = p_cashpool_id
AND sub.type <> 'POOL';
| Delete_Sub_Accounts |
| |
| DESCRIPTION |
| This procedure deletes the sub-accounts of a cash pool |
| prior to re-populating |
| |
| CALLED BY |
| OA Controller Classes |
| REQUIRES |
| |
| HISTORY |
| 14-SEP-2004 Created Sunil Poonen |
--------------------------------------------------------------------- */
PROCEDURE Delete_Sub_Accounts(p_cashpool_id NUMBER) IS
BEGIN
UPDATE ce_cashpools
SET parent_cashpool_id = null
WHERE parent_cashpool_id = p_cashpool_id;
DELETE ce_cashpool_sub_accts
WHERE cashpool_id = p_cashpool_id;
END Delete_Sub_Accounts;
| Update_Parent_Nested_Accounts |
| |
| DESCRIPTION |
| This procedure updates the parent cash pool's nested |
| sub accounts of the current child cash pool to reflect |
| any changes to the current child cash pool's sub accounts |
| |
| CALLED BY |
| OA Controller Classes |
| REQUIRES |
| |
| HISTORY |
| 11-JAN-2005 Created Sunil Poonen |
---------------------------------------------------------------------*/
PROCEDURE Update_Parent_Nested_Accounts(
p_cashpool_id NUMBER,
p_parent_cashpool_id NUMBER
) IS
BEGIN
DELETE FROM ce_cashpool_sub_accts
WHERE cashpool_id = p_parent_cashpool_id
AND nested_parent_pool_id = p_cashpool_id;
INSERT INTO ce_cashpool_sub_accts(
/*01*/cashpool_sub_acct_id,
/*02*/cashpool_id,
/*03*/type,
/*04*/account_id,
/*05*/party_code,
/*06*/legal_entity_id,
/*07*/nested_parent_pool_id,
/*08*/created_by,
/*09*/creation_date,
/*10*/last_updated_by,
/*11*/last_update_date,
/*12*/last_update_login)
SELECT
/*01*/CE_CASHPOOL_SUB_ACCTS_S.nextval,
/*02*/p_parent_cashpool_id,
/*03*/'NEST',
/*04*/sub.account_id,
/*05*/sub.party_code,
/*06*/sub.legal_entity_id,
/*07*/sub.cashpool_id,
/*08*/nvl(fnd_global.user_id,-1),
/*09*/sysdate,
/*10*/nvl(fnd_global.user_id,-1),
/*11*/sysdate,
/*12*/nvl(fnd_global.user_id,-1)
FROM ce_cashpool_sub_accts sub
WHERE sub.cashpool_id = p_cashpool_id
AND sub.type <> 'POOL';
END Update_Parent_Nested_Accounts;
UPDATE ce_bank_accounts
SET min_target_balance = nvl(p_min_target_balance,to_number(null)),
max_target_balance = nvl(p_max_target_balance,to_number(null)),
cashpool_min_payment_amt = nvl(p_min_payment_amt,0),
cashpool_min_receipt_amt = nvl(p_min_receipt_amt,0),
cashpool_round_factor = to_number(nvl(p_round_factor,'0')),
cashpool_round_rule = nvl(p_round_rule,'R')
WHERE bank_account_id = p_bank_account_id;
UPDATE ce_bank_accounts
SET pool_payment_method_code = p_payment_method_code,
pool_bank_charge_bearer_code = p_bank_charge_bearer_code,
pool_payment_reason_code = p_payment_reason_code,
pool_payment_reason_comments = p_payment_reason_comments,
pool_remittance_message1 = p_remittance_message1,
pool_remittance_message2 = p_remittance_message2,
pool_remittance_message3 = p_remittance_message3
WHERE bank_account_id = p_bank_account_id;
| Update_Bank_Account_Id |
| |
| DESCRIPTION |
| This procedure updates the bank account id in ce_cashpools |
| and ce_cashpool_sub_accts when a bank account that is also a |
| cash pool sub-account is "linked" or "unlinked" with AP |
| |
| CALLED BY |
| OA Controller Classes |
| REQUIRES |
| |
| HISTORY |
| 15-JUN-2004 Created Sunil Poonen |
---------------------------------------------------------------------*/
PROCEDURE Update_Bank_Account_Id(
p_old_bank_account_id NUMBER,
p_new_bank_account_id NUMBER
) IS
BEGIN
UPDATE ce_cashpool_sub_accts
SET account_id = p_new_bank_account_id
WHERE account_id = p_old_bank_account_id
AND type <> 'POOL';
UPDATE ce_cashpool_sub_accts
SET conc_account_id = p_new_bank_account_id
WHERE conc_account_id = p_old_bank_account_id;
UPDATE ce_cashpool_sub_accts
SET inv_conc_account_id = p_new_bank_account_id
WHERE inv_conc_account_id = p_old_bank_account_id;
UPDATE ce_cashpool_sub_accts
SET fund_conc_account_id = p_new_bank_account_id
WHERE fund_conc_account_id = p_old_bank_account_id;
UPDATE ce_cashpools
SET conc_account_id = p_new_bank_account_id
WHERE conc_account_id = p_old_bank_account_id;
UPDATE ce_cashpools
SET inv_conc_account_id = p_new_bank_account_id
WHERE inv_conc_account_id = p_old_bank_account_id;
UPDATE ce_cashpools
SET fund_conc_account_id = p_new_bank_account_id
WHERE fund_conc_account_id = p_old_bank_account_id;
UPDATE ce_cp_worksheet_lines
SET bank_account_id = p_new_bank_account_id
WHERE bank_account_id = p_old_bank_account_id;
UPDATE ce_forecast_rows
SET bank_account_id = p_new_bank_account_id
WHERE bank_account_id = p_old_bank_account_id;
END Update_Bank_Account_Id;
SELECT DISTINCT cps. account_id -- Bug 14512843: Added DISTINCT
INTO l_offset_bank_account_id
FROM ce_bank_accounts cba, ce_cashpools cp, ce_cashpool_sub_accts cps
WHERE cba.bank_account_id in(cp.conc_account_id, -- Bug 14512483 Modified clause
cp.inv_conc_account_id,
cp.fund_conc_account_id)
AND cp.cashpool_id = cps.cashpool_id
AND cps.type in ('ACCT','CONC','INV','FUND') /* 14140123 */
AND cps.account_id IN
(SELECT bank_account_id
FROM ce_bank_accounts
WHERE bank_account_num = p_offset_bank_account_num);
SELECT cashpool_id, type
INTO l_cashpool_id, l_type
FROM ce_cashpool_sub_accts
WHERE type in ('ACCT','CONC','INV','FUND') -- Bug 14512483: Added FUND
AND account_id = p_header_bank_account_id
AND cashpool_id in
(SELECT cashpool_id
FROM ce_cashpools
WHERE sweeps_flag = 'Y'
AND NVL(effective_date_to,p_trx_date) >= p_trx_date);
SELECT type
INTO l_offset_type
FROM ce_cashpool_sub_accts
WHERE cashpool_id = l_cashpool_id
AND account_id = l_offset_bank_account_id
AND type in ('ACCT','CONC','FUND','INV'); -- Bug 14512843: Added INV
SELECT cashpool_id, type
INTO l_cashpool_id, l_type
FROM ce_cashpool_sub_accts
WHERE type in ('ACCT','CONC','FUND','INV') -- Bug 14512843: Added INV
AND account_id = p_header_bank_account_id
AND cashpool_id in
(SELECT cashpool_id
FROM ce_cashpools
WHERE sweeps_flag = 'Y'
AND nvl(effective_date_to,p_trx_date) >= p_trx_date);
SELECT type
INTO l_offset_type
FROM ce_cashpool_sub_accts
WHERE cashpool_id = l_cashpool_id
AND account_id = l_offset_bank_account_id
AND type in ('ACCT','CONC','INV','FUND'); -- Bug 14512843: Added FUND