[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ba.bank_account_id,
ba.account_owner_org_id
FROM ce_bank_accounts ba
WHERE
ba.bank_branch_id = p_bank_branch_id
AND ba.bank_account_id = NVL(p_bank_account_id, ba.bank_account_id);
SELECT
csh.statement_header_id
FROM
ce_bank_accounts ba,
ce_statement_headers csh
WHERE
ba.bank_account_id = NVL(p_bank_account_id,ba.bank_account_id)
AND csh.bank_account_id = ba.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 csh.statement_date BETWEEN
NVL(p_statement_date_from,csh.statement_date) AND
NVL(p_statement_date_to,csh.statement_date);
SELECT
sl.rowid,
sl.statement_line_id,
sl.trx_code_id,
sl.amount,
sl.status,
sl.currency_code,
NVL(ba.currency_code, sh.currency_code),
sh.statement_date,
sh.gl_date,
ba.currency_code,
sl.effective_date,
sl.trx_date,
sl.trx_type,
sl.original_amount,
sl.exchange_rate_type,
sl.exchange_rate,
sl.exchange_rate_date,
--sl.je_status_flag,
sl.trx_text,
sh.statement_header_id,
sh.bank_account_id,
jem.gl_account_ccid,
jem.search_string_txt,
jem.reference_txt,
--cc.asset_code_combination_id,
sl.bank_trx_number,
sl.bank_account_text,
sl.customer_text,
sl.cashflow_id,
-- ba.asset_code_combination_id
ba.multi_currency_allowed_flag,
jem.trxn_subtype_code_id
FROM
ce_statement_lines sl,
ce_statement_headers sh,
-- ap_bank_accounts ba,
ce_bank_accounts ba,
ce_je_mappings_v jem
--ce_bank_acct_uses_all use,
--ce_gl_accounts_ccid cc
WHERE
sh.statement_header_id = p_statement_header_id AND
sl.statement_header_id = sh.statement_header_id AND
NVL(sh.statement_complete_flag,'N') = 'N' AND
ba.bank_account_id = sh.bank_account_id AND
-- ba.account_type = 'INTERNAL' AND
jem.bank_account_id = sh.bank_account_id AND
--cc.bank_acct_use_id = use.bank_acct_use_id AND
--use.bank_account_id = sh.bank_account_id AND
--use.ce_use_enabled_flag = 'Y' AND
sl.trx_code = jem.trx_code AND
sl.status = 'UNRECONCILED' AND
--NVL(sl.je_status_flag,'S') <> 'C' AND
(sl.trx_text like jem.search_string_txt OR
jem.search_string_txt is null)
ORDER BY
sl.statement_line_id, jem.trx_code_id, jem.search_string_txt;
SELECT count(*)
INTO l_count
FROM gl_period_statuses glp,
ce_system_parameters sys
WHERE glp.set_of_books_id = sys.set_of_books_id
AND sys.legal_entity_id = CE_JE_CREATION.ba_legal_entity_id
AND glp.closing_status in ('O','F')
AND glp.application_id = 101
AND glp.adjustment_period_flag = 'N'
AND to_char(p_accounting_date,'YYYY/MM/DD') BETWEEN
to_char(glp.start_date,'YYYY/MM/DD') AND to_char(glp.end_date,'YYYY/MM/DD');
SELECT count(1)
INTO l_count
FROM
gl_code_combinations
WHERE
code_combination_id = p_gl_account_ccid;
| This procedure inserts data into CE_CASHFLOWS table |
| |
| HISTORY |
| 29-JUL-2004 xxwang Created |
--------------------------------------------------------------------- */
PROCEDURE Populate_CF_table (x_cashflow_id OUT NOCOPY NUMBER) IS
x_rowid VARCHAR2(1000);
CE_CASHFLOWS_PKG.insert_row (
x_rowid,
x_cashflow_id,
CE_JE_CREATION.cf_ledger_id,
CE_JE_CREATION.cf_legal_entity_id,
CE_JE_CREATION.cf_bank_account_id,
CE_JE_CREATION.cf_direction,
CE_JE_CREATION.cf_currency_code,
CE_JE_CREATION.cf_cashflow_date,
CE_JE_CREATION.cf_cashflow_amount,
CE_JE_CREATION.cf_base_amount,
CE_JE_CREATION.cf_description,
null, -- cashflow_exchange_rate
null, -- cashflow_exchange_date
null, -- cashflow_exchange_rate_type
CE_JE_CREATION.cf_trxn_reference_number,
CE_JE_CREATION.cf_bank_trxn_number,
CE_JE_CREATION.cf_source_trxn_type,
CE_JE_CREATION.cf_source_trxn_subtype_code_id,
CE_JE_CREATION.cf_statement_line_id,
CE_JE_CREATION.cf_actual_value_date,
null, -- counterparty_party_id
null, -- counterparty_bank_account_id
CE_JE_CREATION.cf_offset_ccid,
CE_JE_CREATION.cf_status_code,
CE_JE_CREATION.cf_cleared_date,
CE_JE_CREATION.cf_cleared_amount,
CE_JE_CREATION.cf_cleared_exchange_rate,
CE_JE_CREATION.cf_cleared_exchange_date,
CE_JE_CREATION.cf_cleared_exchange_rate_type,
null, -- clearing_charges_amount
null, -- clearing_error_amount
null, -- cleared_by_flag
CE_JE_CREATION.cf_reference_text,
CE_JE_CREATION.cf_bank_account_text,
CE_JE_CREATION.cf_customer_text,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.user_id,-1));
SELECT 1
INTO l_dummy
FROM
ce_statement_lines
WHERE rowid = CE_JE_CREATION.csl_rowid
FOR UPDATE OF je_status_flag NOWAIT;
| Update_statement_line
| DESCRIPTION
| This procedure updates the cashflow_id
| |
| HISTORY |
| 16-SEP-2004 Shaik Vali Created |
--------------------------------------------------------------------- */
PROCEDURE Update_statement_line(p_statement_line_id IN NUMBER,
p_cashflow_id IN NUMBER,
p_je_status_flag IN VARCHAR2) IS
BEGIN
UPDATE ce_statement_lines
SET cashflow_id = p_cashflow_id,
je_status_flag = p_je_status_flag
WHERE statement_line_id = p_statement_line_id;
END Update_statement_line;
| 5)update the statement line |
| |
| HISTORY |
| 21-SEP-2004 Shaik Vali Created |
--------------------------------------------------------------------- */
PROCEDURE Process_statement_line IS
l_result VARCHAR2(50);
Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
l_cashflow_id,'S');
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'DUMMY',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
null,'E');
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_INVALID_GL_ACCOUNT',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
null,'E');
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_NOT_MULTI_CURR',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
null,'E');
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_MISSING_USER_RATE',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
null,'E');
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_INVALID_CLEARED_DATE',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
SELECT sob.currency_code,
sys.bsc_exchange_date_type,
sys.cashflow_exchange_rate_type,
--xle.ledger_id
sys.set_of_books_id
INTO
CE_JE_CREATION.sys_currency_code,
CE_JE_CREATION.sys_exchange_rate_date,
CE_JE_CREATION.sys_exchange_rate_type,
CE_JE_CREATION.sys_sob_id
FROM
ce_system_parameters sys, -- change to base table per BH's request
gl_sets_of_books sob,
ce_bank_accounts ba
--xle_fp_ou_ledger_v xle
WHERE
sys.set_of_books_id = sob.set_of_books_id
AND sys.legal_entity_id = ba.account_owner_org_id
AND ba.bank_account_id = l_bank_account_id;
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_NO_BA_LE_IN_SYS',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);
CE_JE_CREATION_ERRORS_PKG.insert_row(
CE_JE_CREATION.csh_statement_header_id,
CE_JE_CREATION.csl_statement_line_id,
'CE_LINE_LOCKED',
NVL(FND_GLOBAL.user_id,-1),
sysdate,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
g_request_id);