[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT aba.bank_account_id,
aba.ACCOUNT_OWNER_ORG_ID,
nvl(aba.XTR_USE_ALLOWED_FLAG,'N'),
nvl(aba.PAY_USE_ALLOWED_FLAG,'N')
--FROM ce_bank_accounts_v aba
FROM ce_bank_accts_gt_v aba
WHERE aba.bank_branch_id = p_bank_branch_id
AND aba.bank_account_id = NVL(p_bank_account_id, aba.bank_account_id);
SELECT sh.rowid,
sh.statement_number,
sh.bank_account_num,
sh.check_digits,
sh.control_begin_balance,
sh.control_end_balance,
sh.cashflow_balance,
sh.int_calc_balance,
sh.average_close_ledger_mtd,
sh.average_close_ledger_ytd,
sh.average_close_available_mtd,
sh.average_close_available_ytd,
sh.one_day_float,
sh.two_day_float,
sh.intra_day_flag,
sh.subsidiary_flag,
sh.control_total_dr,
sh.control_total_cr,
sh.control_dr_line_count,
sh.control_cr_line_count,
sh.control_line_count,
sh.attribute_category,
sh.attribute1,
sh.attribute2,
sh.attribute3,
sh.attribute4,
sh.attribute5,
sh.attribute6,
sh.attribute7,
sh.attribute8,
sh.attribute9,
sh.attribute10,
sh.attribute11,
sh.attribute12,
sh.attribute13,
sh.attribute14,
sh.attribute15,
sh.statement_date,
sh.bank_branch_name,
sh.bank_name,
sh.bank_branch_name,
sh.currency_code,
--sh.org_id,
rsh.statement_number,
ba.bank_account_name,
ba.currency_code,
ba.check_digits
FROM ce_statement_headers rsh,
ce_statement_headers_int sh,
ce_bank_accts_gt_v ba --ce_bank_accounts_v ba
WHERE rsh.statement_number(+) = sh.statement_number
AND rsh.bank_account_id(+) = p_bank_account_id
AND NVL(sh.record_status_flag, 'I') <> 'T'
AND sh.statement_number
BETWEEN NVL(p_statement_number_from,sh.statement_number)
AND NVL(p_statement_number_to,sh.statement_number)
AND to_char(sh.statement_date,'J')
BETWEEN NVL(to_char(p_statement_date_from,'J'),1)
AND NVL(to_char(p_statement_date_to,'J'),3442447)
AND sh.bank_account_num = ba.bank_account_num
AND ba.bank_account_id = NVL(p_bank_account_id,ba.bank_account_id)
AND EXISTS (
select null
from ce_bank_branches_v bb
where bb.branch_party_id = ba.bank_branch_id
and bb.bank_name = nvl(sh.bank_name, bb.bank_name)
and bb.bank_branch_name =
nvl(sh.bank_branch_name, bb.bank_branch_name))
ORDER BY sh.bank_account_num, sh.statement_number;
SELECT distinct l.rowid,
l.line_number,
l.amount,
l.trx_code,
l.user_exchange_rate_type,
l.currency_code,
l.exchange_rate_date,
l.trx_date,
/* commented for bug 7531187
NVL(DECODE(ctc.trx_type,'DEBIT', l.amount,
'MISC_DEBIT', l.amount,
'NSF', l.amount,
'REJECTED', l.amount, 0),0),
DECODE(ctc.trx_type,'DEBIT', 1,
'MISC_DEBIT', 1,
'NSF', 1,
'REJECTED', 1, 0),
NVL(DECODE(ctc.trx_type,'CREDIT', l.amount,
'MISC_CREDIT', l.amount,
'STOP', l.amount, 0),0),
DECODE(ctc.trx_type,'CREDIT', 1,
'MISC_CREDIT', 1,
'STOP', 1, 0),
*/
-- Added Sweep In and Sweep out in the above commented code - bug 7531187
NVL(DECODE(ctc.trx_type,'DEBIT', l.amount,
'MISC_DEBIT', l.amount,
'NSF', l.amount,
'SWEEP_OUT', l.amount,
'REJECTED', l.amount, 0),0),
DECODE(ctc.trx_type,'DEBIT', 1,
'MISC_DEBIT', 1,
'NSF', 1,
'SWEEP_OUT', 1,
'REJECTED', 1, 0),
NVL(DECODE(ctc.trx_type,'CREDIT', l.amount,
'MISC_CREDIT', l.amount,
'SWEEP_IN', l.amount,
'STOP', l.amount, 0),0),
DECODE(ctc.trx_type,'CREDIT', 1,
'MISC_CREDIT', 1,
'SWEEP_IN', 1,
'STOP', 1, 0),
ctc.transaction_code_id,--for bug 7194081 --null, --bug 5665539 l.trx_code, --ctc.transaction_code_id,
null, --ctc.start_date
null, --ctc.end_date
gt.conversion_type,
gt.user_conversion_type,
curr.currency_code
FROM fnd_currencies curr,
gl_daily_conversion_types gt,
ce_transaction_codes ctc,
ce_statement_lines_interface l
WHERE curr.currency_code(+) = l.currency_code
AND gt.user_conversion_type(+) = l.user_exchange_rate_type
AND ctc.trx_code(+) = l.trx_code
AND ctc.bank_account_id(+) = CE_AUTO_BANK_IMPORT.G_bank_account_id
-- Code added for bug 7531187
AND nvl(ctc.Reconciliation_Sequence,1) = (SELECT nvl(min(ctc2.Reconciliation_Sequence),1)
FROM ce_Transaction_Codes ctc2
WHERE ctc2.Bank_Account_Id (+) =CE_AUTO_BANK_IMPORT.G_bank_account_id
AND ctc2.trx_Code (+) = l.trx_Code)
-- End of Code for bug 7531187
AND l.statement_number = p_statement_number
AND l.bank_account_num = p_bank_account_num
ORDER BY l.line_number;
CE_HEADER_INTERFACE_ERRORS_PKG.insert_row(CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num, error_name);
CE_LINE_INTERFACE_ERRORS_PKG.insert_row(
x_rowid,
CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num,
line_number,
error_name);
SELECT 1
INTO l_return
FROM CE_BANK_ACCT_BALANCES
WHERE BANK_ACCOUNT_ID = p_bank_account_id
AND BALANCE_DATE = trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date)
AND NVL(LEDGER_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,0)
AND NVL(AVAILABLE_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,0)
AND NVL(VALUE_DATED_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,0);
update_header_status('E');
update_header_status('E');
DELETE FROM ce_statement_headers_int sh
WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
DELETE FROM ce_statement_lines_interface sl
WHERE sl.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
AND sl.statement_number = CE_AUTO_BANK_IMPORT.G_cshi_statement_number;
INSERT INTO ce_arch_interface_lines
(bank_account_num,
statement_number,
line_number,
trx_date,
trx_code,
effective_date,
trx_text,
invoice_text,
amount,
charges_amount,
currency_code,
user_exchange_rate_type,
exchange_rate_date,
exchange_rate,
original_amount,
bank_trx_number,
customer_text,
bank_account_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
SELECT bank_account_num,
statement_number,
line_number,
trx_date,
trx_code,
effective_date,
trx_text,
invoice_text,
amount,
charges_amount,
currency_code,
user_exchange_rate_type,
exchange_rate_date,
exchange_rate,
original_amount,
bank_trx_number,
customer_text,
bank_account_text,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM ce_statement_lines_interface
WHERE statement_number = CE_AUTO_BANK_IMPORT.G_cshi_statement_number
AND bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num;
INSERT INTO ce_arch_interface_headers
(statement_number,
bank_account_num,
statement_date,
check_digits,
bank_name,
bank_branch_name,
control_begin_balance,
control_end_balance,
cashflow_balance,
int_calc_balance,
average_close_ledger_mtd,
average_close_ledger_ytd,
average_close_available_mtd,
average_close_available_ytd,
one_day_float,
two_day_float,
intra_day_flag,
control_total_dr,
control_total_cr,
control_dr_line_count,
control_cr_line_count,
control_line_count,
record_status_flag,
currency_code,
created_by,
creation_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_updated_by,
last_update_date)
--org_id)
SELECT statement_number,
bank_account_num,
statement_date,
check_digits,
bank_name,
bank_branch_name,
control_begin_balance,
control_end_balance,
cashflow_balance,
int_calc_balance,
average_close_ledger_mtd,
average_close_ledger_ytd,
average_close_available_mtd,
average_close_available_ytd,
one_day_float,
two_day_float,
intra_day_flag,
control_total_dr,
control_total_cr,
control_dr_line_count,
control_cr_line_count,
control_line_count,
record_status_flag,
currency_code,
created_by,
creation_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
last_updated_by,
last_update_date
--org_id
FROM ce_statement_headers_int
WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
SELECT statement_number
INTO X_statement_number
FROM ce_statement_headers_int
WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid
FOR UPDATE OF statement_number NOWAIT;
SELECT amount
INTO csli_amount
FROM ce_statement_lines_interface
WHERE rowid = csli_rowid
FOR UPDATE OF bank_account_num NOWAIT;
INSERT INTO ce_intra_stmt_lines
(statement_line_id,
statement_header_id,
line_number,
trx_date,
trx_type,
trx_code_id,
effective_date,
bank_trx_number,
trx_text,
customer_text,
invoice_text,
bank_account_text,
amount,
charges_amount,
status,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
original_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
SELECT ce_intra_stmt_lines_s.nextval,
G_cshi_statement_header_id,
csli.line_number,
csli.trx_date,
ctc.trx_type,
ctc.transaction_code_id,
NVL(csli.effective_date,csli.trx_date+NVL(ctc.float_days,0)),
csli.bank_trx_number,
csli.trx_text,
csli.customer_text,
csli.invoice_text,
csli.bank_account_text,
csli.amount,
csli.charges_amount,
'',
csli.currency_code,
null,
csli.exchange_rate_date,
csli.exchange_rate,
csli.original_amount,
NVL(FND_GLOBAL.user_id, -1),
sysdate,
NVL(FND_GLOBAL.user_id, -1),
sysdate,
csli.attribute_category,
csli.attribute1,
csli.attribute2,
csli.attribute3,
csli.attribute4,
csli.attribute5,
csli.attribute6,
csli.attribute7,
csli.attribute8,
csli.attribute9,
csli.attribute10,
csli.attribute11,
csli.attribute12,
csli.attribute13,
csli.attribute14,
csli.attribute15
FROM ce_transaction_codes ctc,
ce_statement_lines_interface csli,
ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
WHERE ctc.trx_code(+) = csli.trx_code
AND NVL(ctc.bank_account_id,aba.bank_account_id) =
aba.bank_account_id
AND csli.statement_number = G_cshi_statement_number
AND csli.bank_account_num = aba.bank_account_num
AND aba.bank_account_id = G_bank_account_id;
INSERT INTO ce_statement_lines
(statement_line_id,
statement_header_id,
line_number,
trx_date,
trx_type,
trx_code, --trx_code_id,
effective_date,
bank_trx_number,
trx_text,
customer_text,
invoice_text,
bank_account_text,
amount,
charges_amount,
status,
currency_code,
exchange_rate_type,
exchange_rate_date,
exchange_rate,
original_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
SELECT ce_statement_lines_s.nextval,
CE_AUTO_BANK_IMPORT.G_cshi_statement_header_id,
csli.line_number,
csli.trx_date,
(select distinct ctc.trx_type
from ce_transaction_codes ctc
where ctc.trx_code(+) = csli.trx_code
AND NVL(ctc.bank_account_id,aba.bank_account_id) =
aba.bank_account_id),
csli.trx_code, --ctc.transaction_code_id,
--NVL(csli.effective_date,csli.trx_date+NVL(ctc.float_days,0)),
NVL(csli.effective_date,csli.trx_date),
csli.bank_trx_number,
csli.trx_text,
csli.customer_text,
csli.invoice_text,
csli.bank_account_text,
csli.amount,
csli.charges_amount,
'UNRECONCILED',
csli.currency_code,
decode(gl_currency_api.is_fixed_rate(
CE_AUTO_BANK_REC.G_functional_currency,
nvl(csli.currency_code,aba.currency_code),
nvl(csli.exchange_rate_date,csli.trx_date)),
'Y', decode(gdct.conversion_type, NULL,NULL,
decode(nvl(csli.currency_code,aba.currency_code),
CE_AUTO_BANK_REC.G_functional_currency,
gdct.conversion_type,'EMU FIXED')),
gdct.conversion_type),
csli.exchange_rate_date,
csli.exchange_rate,
csli.original_amount,
NVL(FND_GLOBAL.user_id, -1),
sysdate,
NVL(FND_GLOBAL.user_id, -1),
sysdate,
csli.attribute_category,
csli.attribute1,
csli.attribute2,
csli.attribute3,
csli.attribute4,
csli.attribute5,
csli.attribute6,
csli.attribute7,
csli.attribute8,
csli.attribute9,
csli.attribute10,
csli.attribute11,
csli.attribute12,
csli.attribute13,
csli.attribute14,
csli.attribute15
FROM --ce_transaction_codes ctc,
gl_daily_conversion_types gdct,
ce_statement_lines_interface csli,
ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
WHERE gdct.user_conversion_type(+) = csli.user_exchange_rate_type
--AND ctc.trx_code(+) = csli.trx_code
--AND NVL(ctc.bank_account_id,aba.bank_account_id) =
-- aba.bank_account_id
AND csli.statement_number =
CE_AUTO_BANK_IMPORT.G_cshi_statement_number
AND csli.bank_account_num = aba.bank_account_num
AND aba.bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id;
update_header_status('E');
DELETE FROM CE_INTRA_STMT_LINES
WHERE statement_header_id in
(select statement_header_id
from ce_intra_stmt_headers
where statement_number = G_cshi_statement_number
and bank_account_id in
(select bank_account_id from ce_bank_accounts_v
where bank_account_num = G_cshi_bank_account_num));
DELETE FROM CE_INTRA_STMT_HEADERS
WHERE statement_number = G_cshi_statement_number
AND bank_account_id in
(select bank_account_id from ce_bank_accounts_v
where bank_account_num = G_cshi_bank_account_num);
select ce_intra_stmt_headers_s.nextval
into G_cshi_statement_header_id
from sys.dual;
INSERT INTO CE_INTRA_STMT_HEADERS (
statement_header_id,
bank_account_id,
statement_number,
statement_date,
check_digits,
control_begin_balance,
control_end_balance,
cashflow_balance,
int_calc_balance,
one_day_float,
two_day_float,
control_total_dr,
control_total_cr,
control_dr_line_count,
control_cr_line_count,
doc_sequence_id,
doc_sequence_value,
created_by,
creation_date,
last_updated_by,
last_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
auto_loaded_flag,
statement_complete_flag,
gl_date)
--org_id)
VALUES
(G_cshi_statement_header_id,
aba_bank_account_id,
G_cshi_statement_number,
G_cshi_statement_date,
G_cshi_check_digits,
G_cshi_control_begin_balance,
G_cshi_control_end_balance,
G_cshi_cashflow_balance,
G_cshi_int_calc_balance,
G_cshi_one_day_float,
G_cshi_two_day_float,
G_cshi_control_total_dr,
G_cshi_control_total_cr,
G_cshi_control_dr_line_count,
G_cshi_control_cr_line_count,
l_doc_seq_id,
l_doc_seq_value,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
G_cshi_attribute_category,
G_cshi_attribute1,
G_cshi_attribute2,
G_cshi_attribute3,
G_cshi_attribute4,
G_cshi_attribute5,
G_cshi_attribute6,
G_cshi_attribute7,
G_cshi_attribute8,
G_cshi_attribute9,
G_cshi_attribute10,
G_cshi_attribute11,
G_cshi_attribute12,
G_cshi_attribute13,
G_cshi_attribute14,
G_cshi_attribute15,
'Y',
'N',
null);
update_header_status('T');
select count(1)
into x_bal_count
from ce_bank_acct_balances
where bank_account_id = aba_bank_account_id
and balance_date = trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date);
x_flag:='YI'; --balance already exist when inserting through loader
CE_STAT_HDRS_DML_PKG.insert_row (
X_rowid => x_temp_rowid,
X_statement_header_id => CE_AUTO_BANK_IMPORT.G_cshi_statement_header_id,
X_bank_account_id => aba_bank_account_id,
X_statement_number => CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
X_statement_date => trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date),
X_check_digits => CE_AUTO_BANK_IMPORT.G_cshi_check_digits,
X_control_begin_balance => CE_AUTO_BANK_IMPORT.G_cshi_control_begin_balance,
X_control_end_balance => CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
X_cashflow_balance => CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
X_int_calc_balance => CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
X_one_day_float => CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
X_two_day_float => CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
X_control_total_dr => CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr,
X_control_total_cr => CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr,
X_control_dr_line_count => CE_AUTO_BANK_IMPORT.G_cshi_control_dr_line_count,
X_control_cr_line_count => CE_AUTO_BANK_IMPORT.G_cshi_control_cr_line_count,
X_doc_sequence_id => l_doc_seq_id,
X_doc_sequence_value => l_doc_seq_value,
X_created_by => NVL(FND_GLOBAL.user_id,-1),
X_creation_date => sysdate,
X_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
X_last_update_date => sysdate,
X_attribute_category => CE_AUTO_BANK_IMPORT.G_cshi_attribute_category ,
X_attribute1 => CE_AUTO_BANK_IMPORT.G_cshi_attribute1,
X_attribute2 => CE_AUTO_BANK_IMPORT.G_cshi_attribute2,
X_attribute3 => CE_AUTO_BANK_IMPORT.G_cshi_attribute3,
X_attribute4 => CE_AUTO_BANK_IMPORT.G_cshi_attribute4,
X_attribute5 => CE_AUTO_BANK_IMPORT.G_cshi_attribute5,
X_attribute6 => CE_AUTO_BANK_IMPORT.G_cshi_attribute6,
X_attribute7 => CE_AUTO_BANK_IMPORT.G_cshi_attribute7,
X_attribute8 => CE_AUTO_BANK_IMPORT.G_cshi_attribute8,
X_attribute9 => CE_AUTO_BANK_IMPORT.G_cshi_attribute9,
X_attribute10 => CE_AUTO_BANK_IMPORT.G_cshi_attribute10,
X_attribute11 => CE_AUTO_BANK_IMPORT.G_cshi_attribute11,
X_attribute12 => CE_AUTO_BANK_IMPORT.G_cshi_attribute12,
X_attribute13 => CE_AUTO_BANK_IMPORT.G_cshi_attribute13,
X_attribute14 => CE_AUTO_BANK_IMPORT.G_cshi_attribute14,
X_attribute15 => CE_AUTO_BANK_IMPORT.G_cshi_attribute15,
X_auto_loaded_flag => 'Y',
X_statement_complete_flag => 'N',
X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
X_balance_flag => x_flag,
X_average_close_ledger_mtd => CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd,
X_average_close_ledger_ytd => CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd,
X_average_close_available_mtd => CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd,
X_average_close_available_ytd => CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd,
-- 5916290 : GDF Changes
X_bank_acct_balance_id => NULL,
X_global_att_category => NULL,
X_global_attribute1 => NULL,
X_global_attribute2 => NULL,
X_global_attribute3 => NULL,
X_global_attribute4 => NULL,
X_global_attribute5 => NULL,
X_global_attribute6 => NULL,
X_global_attribute7 => NULL,
X_global_attribute8 => NULL,
X_global_attribute9 => NULL,
X_global_attribute10 => NULL,
X_global_attribute11 => NULL,
X_global_attribute12 => NULL,
X_global_attribute13 => NULL,
X_global_attribute14 => NULL,
X_global_attribute15 => NULL,
X_global_attribute16 => NULL,
X_global_attribute17 => NULL,
X_global_attribute18 => NULL,
X_global_attribute19 => NULL,
X_global_attribute20 => NULL
);
SELECT CE_BANK_ACCT_BALANCES_S.nextval
INTO X_bank_acct_balance_id
FROM SYS.dual;
INSERT INTO CE_BANK_ACCT_BALANCES
(bank_acct_balance_id,
bank_account_id,
balance_date,
ledger_balance,
available_balance,
value_dated_balance,
one_day_float,
two_day_float,
average_close_ledger_mtd,
average_close_ledger_ytd,
average_close_available_mtd,
average_close_available_ytd,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number)
values
(X_bank_acct_balance_id,
aba_bank_account_id,
trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date),
CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd,
CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd,
CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd,
CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd,
sysdate,
NVL(FND_GLOBAL.user_id,-1),
sysdate,
NVL(FND_GLOBAL.user_id,-1),
NVL(FND_GLOBAL.user_id,-1),
'1');
update_header_status('T');
update_header_status('E');
| update_header_status |
| |
| DESCRIPTION |
| Errors have been found within the statement header or lines. |
| The statement header record status is updated with 'ERROR'. |
| |
| CALLED BY |
| import_process |
| |
| REQUIRES |
| p_status Status to be updated |
| 'T'ransferred |
| 'E'rror |
--------------------------------------------------------------------- */
PROCEDURE update_header_status(p_status VARCHAR2) IS
BEGIN
IF l_DEBUG in ('Y', 'C') THEN
cep_standard.debug('>>CE_AUTO_BANK_IMPORT.update_header_status');
UPDATE ce_statement_headers_int
SET record_status_flag = p_status
WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
cep_standard.debug('<
cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.update_header_status');
END update_header_status;
SELECT aba.bank_account_id
INTO aba_bank_account_id
FROM ap_bank_branches abb,
ap_bank_accounts aba
WHERE aba.bank_branch_id = abb.bank_branch_id
AND abb.bank_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_name, abb.bank_name)
AND abb.bank_branch_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,abb.bank_branch_name)
AND aba.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
AND aba.account_type = 'INTERNAL';
SELECT count(*)
INTO trx_code_count
FROM ce_transaction_codes
WHERE bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id;
SELECT aba.account_classification
INTO account_type
FROM ce_bank_branches_v abb,
ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
WHERE aba.bank_branch_id = abb.branch_party_id
AND abb.bank_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_name, abb.bank_name)
AND abb.bank_branch_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,abb.bank_branch_name)
AND aba.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
AND aba.bank_account_id = aba_bank_account_id;
SELECT g.currency_code,
NVL(s.interface_purge_flag,'N'),
NVL(s.interface_archive_flag,'N')
INTO CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_REC.G_interface_purge_flag ,
CE_AUTO_BANK_REC.G_interface_archive_flag
FROM CE_SYSTEM_PARAMETERS_ALL s,
GL_SETS_OF_BOOKS g,
ce_bank_accts_gt_v ba --ce_bank_accounts_v ba
WHERE ba.bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id
and ba.ACCOUNT_OWNER_ORG_ID = s.legal_entity_id
and s.set_of_books_id = g.set_of_books_id;
select ACCOUNT_OWNER_ORG_ID
into X_le_id
from ce_BANK_ACCOUNTS
where BANK_ACCOUNT_ID = aba_bank_account_id; */
CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
CE_AUTO_BANK_REC.G_set_of_books_id,
CE_AUTO_BANK_REC.G_cashbook_begin_date,
CE_AUTO_BANK_REC.G_show_cleared_flag,
CE_AUTO_BANK_REC.G_show_void_payment_flag,
CE_AUTO_BANK_REC.G_line_autocreation_flag,
CE_AUTO_BANK_REC.G_interface_purge_flag,
CE_AUTO_BANK_REC.G_interface_archive_flag,
CE_AUTO_BANK_REC.G_lines_per_commit,
CE_AUTO_BANK_REC.G_functional_currency,
CE_AUTO_BANK_REC.G_sob_short_name,
CE_AUTO_BANK_REC.G_account_period_type,
CE_AUTO_BANK_REC.G_user_exchange_rate_type,
CE_AUTO_BANK_REC.G_chart_of_accounts_id,
CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID
);
CE_HEADER_INTERFACE_ERRORS_PKG.delete_row(
CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num );
CE_LINE_INTERFACE_ERRORS_PKG.delete_row(
CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num, NULL);
update_header_status('E');