eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.XTR_REPLICATE_BANK_BALANCES dependencies on XTR_BANK_BALANCES

Line 4: -- To replicate the data from CE tables to xtr_bank_balances table

1: PACKAGE BODY XTR_REPLICATE_BANK_BALANCES AS
2: /* | $Header: xtrbbalb.pls 120.17 2006/08/25 15:57:23 csutaria noship $ | */
3: --
4: -- To replicate the data from CE tables to xtr_bank_balances table
5: --
6: --
7: -- Purpose: This package will insert/delete/update the bank balances
8: -- from CE tables to the xtr_bank_balances table.

Line 8: -- from CE tables to the xtr_bank_balances table.

4: -- To replicate the data from CE tables to xtr_bank_balances table
5: --
6: --
7: -- Purpose: This package will insert/delete/update the bank balances
8: -- from CE tables to the xtr_bank_balances table.
9:
10: -- replicate_bank_account is the main procedure through which the
11: -- insert/delete/update procedures will be called.
12: --

Line 21: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

17:
18:
19:
20: PROCEDURE REPLICATE_BANK_BALANCE
21: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
22: p_action_flag IN VARCHAR2,
23: x_return_status OUT NOCOPY VARCHAR2,
24: x_msg_count OUT NOCOPY NUMBER,
25: x_msg_data OUT NOCOPY VARCHAR2)

Line 31: FROM XTR_BANK_BALANCES

27:
28: CURSOR C_BALANCE_DETAILS IS
29: SELECT company_code,account_number,balance_date,statement_balance
30: ,balance_adjustment,balance_cflow,ce_bank_account_balance_id
31: FROM XTR_BANK_BALANCES
32: WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_balance_rec.ce_bank_account_balance_id;
33:
34:
35:

Line 37: l_balance_rec XTR_BANK_BALANCES%ROWTYPE;

33:
34:
35:
36: l_balance_date_updated BOOLEAN;
37: l_balance_rec XTR_BANK_BALANCES%ROWTYPE;
38:
39:
40: BEGIN
41: l_balance_date_updated := FALSE;

Line 207: ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,

203:
204: END REPLICATE_BANK_BALANCE;
205:
206: PROCEDURE REPLICATE_BANK_BALANCE
207: ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,
208: p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,
209: p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,
210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,

Line 208: p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,

204: END REPLICATE_BANK_BALANCE;
205:
206: PROCEDURE REPLICATE_BANK_BALANCE
207: ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,
208: p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,
209: p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,
210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212: p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,

Line 209: p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,

205:
206: PROCEDURE REPLICATE_BANK_BALANCE
207: ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,
208: p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,
209: p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,
210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212: p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,
213: p_interest_calculated_balance IN CE_BANK_ACCT_BALANCES.value_dated_balance%TYPE,

Line 210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,

206: PROCEDURE REPLICATE_BANK_BALANCE
207: ( p_ce_bank_account_balance_id IN XTR_BANK_BALANCES.ce_bank_account_balance_id%TYPE,
208: p_company_code IN XTR_BANK_BALANCES.company_code%TYPE,
209: p_account_number IN XTR_BANK_BALANCES.account_number%TYPE,
210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212: p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,
213: p_interest_calculated_balance IN CE_BANK_ACCT_BALANCES.value_dated_balance%TYPE,
214: p_one_day_float IN XTR_BANK_BALANCES.one_day_float%TYPE,

Line 214: p_one_day_float IN XTR_BANK_BALANCES.one_day_float%TYPE,

210: p_balance_date IN XTR_BANK_BALANCES.balance_date%TYPE,
211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212: p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,
213: p_interest_calculated_balance IN CE_BANK_ACCT_BALANCES.value_dated_balance%TYPE,
214: p_one_day_float IN XTR_BANK_BALANCES.one_day_float%TYPE,
215: p_two_day_float IN XTR_BANK_BALANCES.two_day_float%TYPE,
216: p_action_flag IN varchar2,
217: x_return_status OUT NOCOPY VARCHAR2,
218: x_msg_count OUT NOCOPY NUMBER,

Line 215: p_two_day_float IN XTR_BANK_BALANCES.two_day_float%TYPE,

211: p_ledger_balance IN CE_BANK_ACCT_BALANCES.ledger_balance%TYPE,
212: p_available_balance IN CE_BANK_ACCT_BALANCES.available_balance%TYPE,
213: p_interest_calculated_balance IN CE_BANK_ACCT_BALANCES.value_dated_balance%TYPE,
214: p_one_day_float IN XTR_BANK_BALANCES.one_day_float%TYPE,
215: p_two_day_float IN XTR_BANK_BALANCES.two_day_float%TYPE,
216: p_action_flag IN varchar2,
217: x_return_status OUT NOCOPY VARCHAR2,
218: x_msg_count OUT NOCOPY NUMBER,
219: x_msg_data OUT NOCOPY VARCHAR2) IS

Line 221: l_xtr_bank_balances_rec XTR_BANK_BALANCES%ROWTYPE;

217: x_return_status OUT NOCOPY VARCHAR2,
218: x_msg_count OUT NOCOPY NUMBER,
219: x_msg_data OUT NOCOPY VARCHAR2) IS
220:
221: l_xtr_bank_balances_rec XTR_BANK_BALANCES%ROWTYPE;
222: l_bank_account_id CE_BANK_ACCT_USES_ALL.BANK_ACCOUNT_ID%TYPE;
223: l_cashpool_id CE_CASHPOOLS.CASHPOOL_ID%TYPE;
224: l_conc_account_id CE_CASHPOOL_SUB_ACCTS.ACCOUNT_ID%TYPE;
225:

Line 253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;

249:
250:
251: BEGIN
252:
253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;

Line 254: l_xtr_bank_balances_rec.company_code := p_company_code;

250:
251: BEGIN
252:
253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);

Line 255: l_xtr_bank_balances_rec.account_number := p_account_number;

251: BEGIN
252:
253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;

Line 256: l_xtr_bank_balances_rec.balance_date := p_balance_date;

252:
253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;

Line 257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;

253: l_xtr_bank_balances_rec.ce_bank_account_balance_id := p_ce_bank_account_balance_id;
254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;

Line 258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);

254: l_xtr_bank_balances_rec.company_code := p_company_code;
255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;
262:

Line 259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;

255: l_xtr_bank_balances_rec.account_number := p_account_number;
256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;
262:
263:

Line 260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;

256: l_xtr_bank_balances_rec.balance_date := p_balance_date;
257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;
262:
263:
264: -- added for notional bank accounts

Line 261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;

257: l_xtr_bank_balances_rec.statement_balance := p_ledger_balance;
258: l_xtr_bank_balances_rec.balance_adjustment := (p_interest_calculated_balance - p_ledger_balance);
259: l_xtr_bank_balances_rec.balance_cflow := p_available_balance;
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;
261: l_xtr_bank_balances_rec.two_day_float := p_two_day_float;
262:
263:
264: -- added for notional bank accounts
265:

Line 271: REPLICATE_BANK_BALANCE( l_xtr_bank_balances_rec,p_action_flag

267: fetch c_bank_account_id into l_bank_account_id;
268:
269: if c_bank_account_id%found then
270:
271: REPLICATE_BANK_BALANCE( l_xtr_bank_balances_rec,p_action_flag
272: ,x_return_status,x_msg_count,x_msg_data);
273:
274:
275: else

Line 316: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

312:
313:
314:
315: PROCEDURE INSERT_BANK_BALANCE
316: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
317: x_return_status IN OUT NOCOPY VARCHAR2
318: )
319: IS
320:

Line 321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;

317: x_return_status IN OUT NOCOPY VARCHAR2
318: )
319: IS
320:
321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;

Line 322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;

318: )
319: IS
320:
321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;

Line 323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;

319: IS
320:
321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;

Line 324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;

320:
321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;
328:

Line 325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;

321: l_prv_date XTR_BANK_BALANCES.balance_date%TYPE;
322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;
328:
329:

Line 326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;

322: l_prv_rate XTR_BANK_BALANCES.interest_rate%TYPE;
323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;
328:
329:
330: l_ccy xtr_bank_accounts.currency%TYPE;

Line 327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;

323: l_prv_bal XTR_BANK_BALANCES.balance_cflow%TYPE;
324: l_int_bf XTR_BANK_BALANCES.ACCUM_INT_CFWD%TYPE;
325: l_prv_accrual_int XTR_BANK_BALANCES.accrual_interest%TYPE;
326: l_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
327: l_prv_rounding_type XTR_BANK_BALANCES.rounding_type%TYPE;
328:
329:
330: l_ccy xtr_bank_accounts.currency%TYPE;
331: l_portfolio_code xtr_bank_accounts.portfolio_code%TYPE;

Line 342: l_prv_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;

338: l_round_factor XTR_MASTER_CURRENCIES_V.ROUNDING_FACTOR%TYPE;
339: l_yr_basis XTR_MASTER_CURRENCIES_V.YEAR_BASIS%TYPE;
340: l_hce_rate XTR_MASTER_CURRENCIES_V.HCE_RATE%TYPE;
341:
342: l_prv_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
343: l_oldest_date XTR_BANK_BALANCES.balance_date%TYPE;
344:
345: l_first_trans_flag VARCHAR2(1);
346: l_invest_limit_code ce_bank_acct_uses_all.investment_limit_code%TYPE;

Line 343: l_oldest_date XTR_BANK_BALANCES.balance_date%TYPE;

339: l_yr_basis XTR_MASTER_CURRENCIES_V.YEAR_BASIS%TYPE;
340: l_hce_rate XTR_MASTER_CURRENCIES_V.HCE_RATE%TYPE;
341:
342: l_prv_prv_day_count_type XTR_BANK_BALANCES.day_count_type%TYPE;
343: l_oldest_date XTR_BANK_BALANCES.balance_date%TYPE;
344:
345: l_first_trans_flag VARCHAR2(1);
346: l_invest_limit_code ce_bank_acct_uses_all.investment_limit_code%TYPE;
347: l_fund_limit_code ce_bank_acct_uses_all.funding_limit_code%TYPE;

Line 365: FROM XTR_BANK_BALANCES a

361: CURSOR PREV_DETAILS IS
362: SELECT a.BALANCE_DATE,NVL(a.STATEMENT_BALANCE,0)+NVL(a.BALANCE_ADJUSTMENT,0),a.ACCUM_INT_CFWD,
363: a.INTEREST_RATE,a.accrual_interest,
364: a.rounding_type, day_count_type
365: FROM XTR_BANK_BALANCES a
366: WHERE a.ACCOUNT_NUMBER = p_balance_rec.account_number
367: AND a.COMPANY_CODE = p_balance_rec.company_code
368: AND a.BALANCE_DATE = (SELECT max(b.BALANCE_DATE)
369: FROM XTR_BANK_BALANCES b

Line 369: FROM XTR_BANK_BALANCES b

365: FROM XTR_BANK_BALANCES a
366: WHERE a.ACCOUNT_NUMBER = p_balance_rec.account_number
367: AND a.COMPANY_CODE = p_balance_rec.company_code
368: AND a.BALANCE_DATE = (SELECT max(b.BALANCE_DATE)
369: FROM XTR_BANK_BALANCES b
370: WHERE b.ACCOUNT_NUMBER = p_balance_rec.account_number
371: AND b.COMPANY_CODE = p_balance_rec.company_code);
372:
373:

Line 403: FROM xtr_bank_balances a

399:
400: -- Getting the oldest balacne date for that bank account
401: CURSOR oldest_date IS
402: SELECT MIN(a.balance_date)
403: FROM xtr_bank_balances a
404: WHERE a.account_number = p_balance_rec.account_number
405: AND a.COMPANY_CODE = p_balance_rec.company_code;
406:
407:

Line 410: FROM xtr_bank_balances a

406:
407:
408: CURSOR PRV_PRV_DETAILS IS
409: SELECT a.day_count_type
410: FROM xtr_bank_balances a
411: WHERE a.account_number = p_balance_rec.account_number
412: AND a.COMPANY_CODE = p_balance_rec.company_code
413: AND a.balance_date = (SELECT max(b.BALANCE_DATE)
414: FROM XTR_BANK_BALANCES b

Line 414: FROM XTR_BANK_BALANCES b

410: FROM xtr_bank_balances a
411: WHERE a.account_number = p_balance_rec.account_number
412: AND a.COMPANY_CODE = p_balance_rec.company_code
413: AND a.balance_date = (SELECT max(b.BALANCE_DATE)
414: FROM XTR_BANK_BALANCES b
415: WHERE b.ACCOUNT_NUMBER = p_balance_rec.account_number
416: AND b.COMPANY_CODE = p_balance_rec.company_code
417: AND b.balance_date < l_prv_date);
418:

Line 533: INSERT INTO XTR_BANK_BALANCES

529: IF l_new_rate IS NULL THEN
530: l_new_rate := 0;
531: END IF;
532:
533: INSERT INTO XTR_BANK_BALANCES
534: ( company_code
535: ,account_number
536: ,balance_date
537: ,no_of_days

Line 601: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

597:
598:
599:
600: PROCEDURE UPDATE_BANK_BALANCE
601: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
602: x_return_status IN OUT NOCOPY VARCHAR2
603: )
604: IS
605:

Line 608: FROM xtr_bank_balances

604: IS
605:
606: CURSOR c_chk_lock IS
607: SELECT ce_bank_account_balance_id
608: FROM xtr_bank_balances
609: WHERE company_code = p_balance_rec.company_code
610: AND account_number = p_balance_rec.account_number
611: AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
612: FOR UPDATE NOWAIT;

Line 615: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;

611: AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
612: FOR UPDATE NOWAIT;
613:
614:
615: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
616:
617:
618: BEGIN
619: --x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;

Line 627: UPDATE xtr_bank_balances SET

623: IF c_chk_lock%FOUND THEN
624:
625: CLOSE c_chk_lock;
626:
627: UPDATE xtr_bank_balances SET
628: statement_balance = nvl(p_balance_rec.statement_balance,0)
629: ,balance_cflow = nvl(p_balance_rec.balance_cflow,0)
630: ,one_day_float = nvl(p_balance_rec.one_day_float,0)
631: ,two_day_float = nvl(p_balance_rec.two_day_float,0)

Line 661: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

657:
658:
659:
660: PROCEDURE DELETE_BANK_BALANCE
661: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,
662: x_return_status IN OUT NOCOPY VARCHAR2
663: )
664: IS
665:

Line 668: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;

664: IS
665:
666: l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
667: l_exists VARCHAR2(1);
668: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;
669: l_ccy xtr_bank_accounts.currency%TYPE;
670:
671: -- Finding the subsidiary for the company
672: CURSOR C_CROSS_REF IS

Line 697: -- Checking the lock on the xtr_bank_balances table

693: AND CURRENCY = l_ccy
694: AND COMPANY_CODE = nvl(l_cross_ref,p_balance_rec.company_code)
695: AND AMOUNT_DATE = p_balance_rec.balance_date;
696:
697: -- Checking the lock on the xtr_bank_balances table
698: CURSOR c_chk_acct_lock IS
699: SELECT ce_bank_account_balance_id
700: FROM xtr_bank_balances
701: WHERE company_code = p_balance_rec.company_code

Line 700: FROM xtr_bank_balances

696:
697: -- Checking the lock on the xtr_bank_balances table
698: CURSOR c_chk_acct_lock IS
699: SELECT ce_bank_account_balance_id
700: FROM xtr_bank_balances
701: WHERE company_code = p_balance_rec.company_code
702: AND account_number = p_balance_rec.account_number
703: AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
704: FOR UPDATE NOWAIT;

Line 709: FROM xtr_bank_balances

705:
706: -- Checking the lock on the xtr_deal_date_amounts table
707: CURSOR c_chk_dda_lock IS
708: SELECT ce_bank_account_balance_id
709: FROM xtr_bank_balances
710: WHERE company_code = p_balance_rec.company_code
711: AND account_number = p_balance_rec.account_number
712: AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id
713: FOR UPDATE NOWAIT;

Line 729: DELETE FROM XTR_BANK_BALANCES

725: OPEN C_CROSS_REF;
726: FETCH C_CROSS_REF INTO l_cross_ref;
727: CLOSE C_CROSS_REF;
728:
729: DELETE FROM XTR_BANK_BALANCES
730: WHERE company_code = p_balance_rec.company_code
731: AND account_number = p_balance_rec.account_number
732: AND balance_date = p_balance_rec.balance_date
733: AND ce_bank_account_balance_id = p_balance_rec.ce_bank_account_balance_id;

Line 797: ( p_company_code IN xtr_bank_balances.company_code%TYPE,

793:
794:
795:
796: PROCEDURE VALIDATE_BANK_BALANCE
797: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
798: p_account_number IN xtr_bank_balances.account_number%TYPE,
799: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
800: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
801: p_interest_calc_balance IN NUMBER,

Line 798: p_account_number IN xtr_bank_balances.account_number%TYPE,

794:
795:
796: PROCEDURE VALIDATE_BANK_BALANCE
797: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
798: p_account_number IN xtr_bank_balances.account_number%TYPE,
799: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
800: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
801: p_interest_calc_balance IN NUMBER,
802: p_available_balance IN NUMBER,

Line 799: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

795:
796: PROCEDURE VALIDATE_BANK_BALANCE
797: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
798: p_account_number IN xtr_bank_balances.account_number%TYPE,
799: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
800: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
801: p_interest_calc_balance IN NUMBER,
802: p_available_balance IN NUMBER,
803: p_action_flag IN VARCHAR2,

Line 800: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,

796: PROCEDURE VALIDATE_BANK_BALANCE
797: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
798: p_account_number IN xtr_bank_balances.account_number%TYPE,
799: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
800: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL,
801: p_interest_calc_balance IN NUMBER,
802: p_available_balance IN NUMBER,
803: p_action_flag IN VARCHAR2,
804: x_return_status OUT NOCOPY VARCHAR2

Line 833: FROM XTR_BANK_BALANCES_V A,

829:
830: -- Check whether the balance does not already exist for
831: CURSOR C_DUPLICATE_DATE IS
832: SELECT 1
833: FROM XTR_BANK_BALANCES_V A,
834: XTR_BANK_ACCOUNTS_V B
835: WHERE A.BALANCE_DATE = p_balance_date
836: AND A.ACCOUNT_NUMBER = B.ACCOUNT_NUMBER
837: AND A.COMPANY_CODE = B.PARTY_CODE

Line 1021: ( p_company_code IN xtr_bank_balances.company_code%TYPE,

1017: END VALIDATE_BANK_BALANCE;
1018:
1019:
1020: PROCEDURE VALIDATE_BANK_BALANCE
1021: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1022: p_account_number IN xtr_bank_balances.account_number%TYPE,
1023: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1024: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1025: p_interest_calc_balance IN NUMBER,

Line 1022: p_account_number IN xtr_bank_balances.account_number%TYPE,

1018:
1019:
1020: PROCEDURE VALIDATE_BANK_BALANCE
1021: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1022: p_account_number IN xtr_bank_balances.account_number%TYPE,
1023: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1024: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1025: p_interest_calc_balance IN NUMBER,
1026: p_available_balance in NUMBER,

Line 1023: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

1019:
1020: PROCEDURE VALIDATE_BANK_BALANCE
1021: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1022: p_account_number IN xtr_bank_balances.account_number%TYPE,
1023: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1024: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1025: p_interest_calc_balance IN NUMBER,
1026: p_available_balance in NUMBER,
1027: p_action_flag IN VARCHAR2,

Line 1024: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,

1020: PROCEDURE VALIDATE_BANK_BALANCE
1021: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1022: p_account_number IN xtr_bank_balances.account_number%TYPE,
1023: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1024: p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null,
1025: p_interest_calc_balance IN NUMBER,
1026: p_available_balance in NUMBER,
1027: p_action_flag IN VARCHAR2,
1028: x_return_status OUT NOCOPY VARCHAR2,

Line 1035: FROM XTR_BANK_BALANCES

1031:
1032: CURSOR C_BALANCE_DETAILS IS
1033: SELECT company_code,account_number,balance_date,statement_balance
1034: ,balance_adjustment,balance_cflow,ce_bank_account_balance_id
1035: FROM XTR_BANK_BALANCES
1036: WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_ce_bank_account_balance_id;
1037:
1038:
1039: l_balance_date_updated BOOLEAN;

Line 1040: l_balance_rec XTR_BANK_BALANCES%ROWTYPE;

1036: WHERE CE_BANK_ACCOUNT_BALANCE_ID = p_ce_bank_account_balance_id;
1037:
1038:
1039: l_balance_date_updated BOOLEAN;
1040: l_balance_rec XTR_BANK_BALANCES%ROWTYPE;
1041:
1042: BEGIN
1043:
1044: x_return_status := FND_API.G_RET_STS_SUCCESS;

Line 1139: FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE

1135:
1136:
1137:
1138:
1139: FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1140: , p_account_number IN xtr_bank_balances.account_number%TYPE
1141: , p_currency IN xtr_bank_accounts.currency%TYPE
1142: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1143: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL

Line 1140: , p_account_number IN xtr_bank_balances.account_number%TYPE

1136:
1137:
1138:
1139: FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1140: , p_account_number IN xtr_bank_balances.account_number%TYPE
1141: , p_currency IN xtr_bank_accounts.currency%TYPE
1142: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1143: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1144: , p_interest_calc_balance IN NUMBER

Line 1142: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1138:
1139: FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1140: , p_account_number IN xtr_bank_balances.account_number%TYPE
1141: , p_currency IN xtr_bank_accounts.currency%TYPE
1142: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1143: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1144: , p_interest_calc_balance IN NUMBER
1145: , p_action_flag IN VARCHAR2
1146: , p_val_type IN VARCHAR2)

Line 1143: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL

1139: FUNCTION CHK_ACCRUAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1140: , p_account_number IN xtr_bank_balances.account_number%TYPE
1141: , p_currency IN xtr_bank_accounts.currency%TYPE
1142: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1143: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1144: , p_interest_calc_balance IN NUMBER
1145: , p_action_flag IN VARCHAR2
1146: , p_val_type IN VARCHAR2)
1147: RETURN VARCHAR2

Line 1160: xtr_bank_balances bb, xtr_bank_accounts ba,

1156: -- Check whether the accruals have been run for that particular deal
1157: CURSOR c_accrl_deal IS
1158: SELECT max(period_to)
1159: FROM
1160: xtr_bank_balances bb, xtr_bank_accounts ba,
1161: xtr_deal_date_amounts dd, xtr_accrls_amort aa
1162: WHERE bb.company_code = p_company_code
1163: AND bb.account_number = p_account_number
1164: AND bb.company_code = ba.party_code

Line 1188: xtr_bank_balances bb

1184: -- Getting the interest calc amount from the database for the updated record
1185: CURSOR c_accrl_amount IS
1186: SELECT BALANCE_ADJUSTMENT + STATEMENT_BALANCE
1187: FROM
1188: xtr_bank_balances bb
1189: WHERE
1190: bb.company_code = p_company_code
1191: AND bb.account_number = p_account_number
1192: AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;

Line 1252: FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE

1248:
1249: -- This function checks whether the revaluations have been run at the time of
1250: -- insertion/updation/deletion
1251:
1252: FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1253: , p_account_number IN xtr_bank_balances.account_number%TYPE
1254: , p_currency IN xtr_bank_accounts.currency%TYPE
1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL

Line 1253: , p_account_number IN xtr_bank_balances.account_number%TYPE

1249: -- This function checks whether the revaluations have been run at the time of
1250: -- insertion/updation/deletion
1251:
1252: FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1253: , p_account_number IN xtr_bank_balances.account_number%TYPE
1254: , p_currency IN xtr_bank_accounts.currency%TYPE
1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1257: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE

Line 1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1251:
1252: FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1253: , p_account_number IN xtr_bank_balances.account_number%TYPE
1254: , p_currency IN xtr_bank_accounts.currency%TYPE
1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1257: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
1258: , p_action_flag IN VARCHAR2
1259: , p_val_type IN VARCHAR2 )

Line 1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL

1252: FUNCTION CHK_REVAL ( p_company_code IN xtr_bank_balances.company_code%TYPE
1253: , p_account_number IN xtr_bank_balances.account_number%TYPE
1254: , p_currency IN xtr_bank_accounts.currency%TYPE
1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1257: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
1258: , p_action_flag IN VARCHAR2
1259: , p_val_type IN VARCHAR2 )
1260: RETURN VARCHAR2

Line 1257: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE

1253: , p_account_number IN xtr_bank_balances.account_number%TYPE
1254: , p_currency IN xtr_bank_accounts.currency%TYPE
1255: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1256: , p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE DEFAULT NULL
1257: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
1258: , p_action_flag IN VARCHAR2
1259: , p_val_type IN VARCHAR2 )
1260: RETURN VARCHAR2
1261: IS

Line 1267: l_balance_cflow xtr_bank_balances.balance_cflow%TYPE;

1263: l_error VARCHAR2(50);
1264: l_reval_deal_date DATE;
1265: l_reval_batch_date DATE;
1266: l_reval_delete_date DATE;
1267: l_balance_cflow xtr_bank_balances.balance_cflow%TYPE;
1268:
1269:
1270: -- Check whether the revaluations have been run for period greater than the
1271: -- balance date entered by the user

Line 1286: xtr_bank_balances bb,xtr_bank_accounts ba

1282:
1283: CURSOR c_deal_delete IS
1284: SELECT max(bb.balance_date)
1285: FROM
1286: xtr_bank_balances bb,xtr_bank_accounts ba
1287: WHERE
1288: bb.company_code = p_company_code
1289: AND bb.account_number = p_account_number
1290: AND bb.company_code = ba.party_code

Line 1300: xtr_bank_balances bb,xtr_bank_accounts ba,

1296: -- Check whether the revaluations have been run for that particular deal
1297: CURSOR c_reval_deal IS
1298: SELECT max(period_to)
1299: FROM
1300: xtr_bank_balances bb,xtr_bank_accounts ba,
1301: xtr_revaluation_details rd
1302: WHERE
1303: bb.company_code = p_company_code
1304: AND bb.account_number = p_account_number

Line 1315: xtr_bank_balances bb

1311: -- Getting the cashflow amount from the database for the updated record
1312: CURSOR c_reval_amount IS
1313: SELECT BALANCE_CFLOW
1314: FROM
1315: xtr_bank_balances bb
1316: WHERE
1317: bb.company_code = p_company_code
1318: AND bb.account_number = p_account_number
1319: AND ce_bank_account_balance_id = p_ce_bank_account_balance_id;

Line 1386: ( p_company_code IN xtr_bank_balances.company_code%TYPE,

1382:
1383:
1384:
1385: PROCEDURE UPDATE_BANK_ACCOUNT
1386: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1387: p_account_number IN xtr_bank_balances.account_number%TYPE,
1388: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1389: p_action_flag IN VARCHAR2,
1390: x_return_status OUT NOCOPY VARCHAR2

Line 1387: p_account_number IN xtr_bank_balances.account_number%TYPE,

1383:
1384:
1385: PROCEDURE UPDATE_BANK_ACCOUNT
1386: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1387: p_account_number IN xtr_bank_balances.account_number%TYPE,
1388: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1389: p_action_flag IN VARCHAR2,
1390: x_return_status OUT NOCOPY VARCHAR2
1391: )

Line 1388: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

1384:
1385: PROCEDURE UPDATE_BANK_ACCOUNT
1386: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1387: p_account_number IN xtr_bank_balances.account_number%TYPE,
1388: p_balance_date IN xtr_bank_balances.balance_date%TYPE,
1389: p_action_flag IN VARCHAR2,
1390: x_return_status OUT NOCOPY VARCHAR2
1391: )
1392: IS

Line 1400: l_bal_date xtr_bank_balances.balance_date%TYPE;

1396: l_bank_code xtr_bank_accounts.bank_code%TYPE;
1397: l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
1398: l_bal_exists VARCHAR2(10);
1399: l_dummy_num VARCHAR2(1);
1400: l_bal_date xtr_bank_balances.balance_date%TYPE;
1401: l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;
1402:
1403: CURSOR C_SUBSIDIARY IS
1404: SELECT CROSS_REF_TO_OTHER_PARTY

Line 1401: l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;

1397: l_cross_ref xtr_party_info.cross_ref_to_other_party%TYPE;
1398: l_bal_exists VARCHAR2(10);
1399: l_dummy_num VARCHAR2(1);
1400: l_bal_date xtr_bank_balances.balance_date%TYPE;
1401: l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;
1402:
1403: CURSOR C_SUBSIDIARY IS
1404: SELECT CROSS_REF_TO_OTHER_PARTY
1405: FROM XTR_PARTIES_V

Line 1430: FROM XTR_BANK_BALANCES_V

1426: -- is deleting the balance
1427:
1428: CURSOR C_BAL_DATE IS
1429: SELECT max(BALANCE_DATE)
1430: FROM XTR_BANK_BALANCES_V
1431: WHERE ACCOUNT_NUMBER = p_account_number
1432: AND COMPANY_CODE = p_company_code
1433: AND BALANCE_DATE < p_balance_date;
1434:

Line 1439: FROM XTR_BANK_BALANCES

1435:
1436: --
1437: CURSOR C_ACCUM_INT IS
1438: SELECT ACCUM_INT_CFWD
1439: FROM XTR_BANK_BALANCES
1440: WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1441: AND COMPANY_CODE = P_COMPANY_CODE
1442: AND BALANCE_DATE = (SELECT MAX(BALANCE_DATE) FROM
1443: XTR_BANK_BALANCES

Line 1443: XTR_BANK_BALANCES

1439: FROM XTR_BANK_BALANCES
1440: WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1441: AND COMPANY_CODE = P_COMPANY_CODE
1442: AND BALANCE_DATE = (SELECT MAX(BALANCE_DATE) FROM
1443: XTR_BANK_BALANCES
1444: WHERE ACCOUNT_NUMBER = P_ACCOUNT_NUMBER
1445: AND COMPANY_CODE = P_COMPANY_CODE);
1446:
1447: BEGIN

Line 1532: FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,

1528: insertion/updation/deletion
1529:
1530: */
1531:
1532: FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1533: p_account_number IN xtr_bank_balances.account_number%TYPE,
1534: p_balance_date IN xtr_bank_balances.balance_date%TYPE)
1535: RETURN BOOLEAN IS
1536:

Line 1533: p_account_number IN xtr_bank_balances.account_number%TYPE,

1529:
1530: */
1531:
1532: FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1533: p_account_number IN xtr_bank_balances.account_number%TYPE,
1534: p_balance_date IN xtr_bank_balances.balance_date%TYPE)
1535: RETURN BOOLEAN IS
1536:
1537: CURSOR c_chk_bal IS

Line 1534: p_balance_date IN xtr_bank_balances.balance_date%TYPE)

1530: */
1531:
1532: FUNCTION CHK_ROUNDING_CHANGE ( p_company_code IN xtr_bank_balances.company_code%TYPE,
1533: p_account_number IN xtr_bank_balances.account_number%TYPE,
1534: p_balance_date IN xtr_bank_balances.balance_date%TYPE)
1535: RETURN BOOLEAN IS
1536:
1537: CURSOR c_chk_bal IS
1538: SELECT count(balance_date)

Line 1539: FROM XTR_BANK_BALANCES a

1535: RETURN BOOLEAN IS
1536:
1537: CURSOR c_chk_bal IS
1538: SELECT count(balance_date)
1539: FROM XTR_BANK_BALANCES a
1540: WHERE a.ACCOUNT_NUMBER = p_account_number
1541: AND a.COMPANY_CODE = p_company_code;
1542:
1543:

Line 1546: FROM XTR_BANK_BALANCES a

1542:
1543:
1544: CURSOR c_chk_type IS
1545: SELECT COUNT(DISTINCT ROUNDING_TYPE||'-'||DAY_COUNT_TYPE)
1546: FROM XTR_BANK_BALANCES a
1547: WHERE a.ACCOUNT_NUMBER = p_account_number
1548: AND a.COMPANY_CODE = p_company_code
1549: AND a.BALANCE_DATE >= (SELECT max(balance_date)
1550: FROM xtr_bank_balances b

Line 1550: FROM xtr_bank_balances b

1546: FROM XTR_BANK_BALANCES a
1547: WHERE a.ACCOUNT_NUMBER = p_account_number
1548: AND a.COMPANY_CODE = p_company_code
1549: AND a.BALANCE_DATE >= (SELECT max(balance_date)
1550: FROM xtr_bank_balances b
1551: WHERE b.account_number = p_account_number
1552: AND b.company_code = p_company_code
1553: AND b.balance_date < p_balance_date);
1554:

Line 1590: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1586: */
1587:
1588: FUNCTION CHK_INTEREST_SETTLED
1589: ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1590: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1591: )return VARCHAR2 is
1592:
1593: l_return VARCHAR2(2);
1594: l_dummy VARCHAR2(1);

Line 1648: ( p_company_code IN xtr_bank_balances.company_code%TYPE

1644: -- This function will be called by CE at the time when the balabce page is rendered
1645: -- to enable/disable the available balance
1646:
1647: FUNCTION CHK_REVAL_ON_RENDER
1648: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1649: , p_account_number IN xtr_bank_balances.account_number%TYPE
1650: , p_currency IN xtr_bank_accounts.currency%TYPE
1651: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1652: )RETURN BOOLEAN

Line 1649: , p_account_number IN xtr_bank_balances.account_number%TYPE

1645: -- to enable/disable the available balance
1646:
1647: FUNCTION CHK_REVAL_ON_RENDER
1648: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1649: , p_account_number IN xtr_bank_balances.account_number%TYPE
1650: , p_currency IN xtr_bank_accounts.currency%TYPE
1651: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1652: )RETURN BOOLEAN
1653: IS

Line 1651: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1647: FUNCTION CHK_REVAL_ON_RENDER
1648: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1649: , p_account_number IN xtr_bank_balances.account_number%TYPE
1650: , p_currency IN xtr_bank_accounts.currency%TYPE
1651: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1652: )RETURN BOOLEAN
1653: IS
1654:
1655: l_reval_deal_date DATE;

Line 1662: xtr_bank_balances bb,xtr_bank_accounts ba,

1658: -- Check whether the revaluations have been run for that particular deal
1659: CURSOR c_reval_deal IS
1660: SELECT max(period_to)
1661: FROM
1662: xtr_bank_balances bb,xtr_bank_accounts ba,
1663: xtr_revaluation_details rd
1664: WHERE
1665: bb.company_code = p_company_code
1666: AND bb.account_number = p_account_number

Line 1677: xtr_bank_balances bb,xtr_bank_accounts ba

1673:
1674: CURSOR c_deal_delete IS
1675: SELECT max(bb.balance_date)
1676: FROM
1677: xtr_bank_balances bb,xtr_bank_accounts ba
1678: WHERE
1679: bb.company_code = p_company_code
1680: AND bb.account_number = p_account_number
1681: AND bb.company_code = ba.party_code

Line 1710: ( p_company_code IN xtr_bank_balances.company_code%TYPE

1706:
1707: -- This function will be called by CE at the time when the balabce page is rendered
1708: -- to enable/disable the interest calculated balance
1709: FUNCTION CHK_ACCRUAL_ON_RENDER
1710: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1711: , p_account_number IN xtr_bank_balances.account_number%TYPE
1712: , p_currency IN xtr_bank_accounts.currency%TYPE
1713: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1714: )RETURN BOOLEAN

Line 1711: , p_account_number IN xtr_bank_balances.account_number%TYPE

1707: -- This function will be called by CE at the time when the balabce page is rendered
1708: -- to enable/disable the interest calculated balance
1709: FUNCTION CHK_ACCRUAL_ON_RENDER
1710: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1711: , p_account_number IN xtr_bank_balances.account_number%TYPE
1712: , p_currency IN xtr_bank_accounts.currency%TYPE
1713: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1714: )RETURN BOOLEAN
1715: IS

Line 1713: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1709: FUNCTION CHK_ACCRUAL_ON_RENDER
1710: ( p_company_code IN xtr_bank_balances.company_code%TYPE
1711: , p_account_number IN xtr_bank_balances.account_number%TYPE
1712: , p_currency IN xtr_bank_accounts.currency%TYPE
1713: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1714: )RETURN BOOLEAN
1715: IS
1716:
1717: l_accrual_deal_date DATE;

Line 1723: xtr_bank_balances bb, xtr_bank_accounts ba,

1719: -- Check whether the accruals have been run for that particular deal
1720: CURSOR c_accrl_deal IS
1721: SELECT max(period_to)
1722: FROM
1723: xtr_bank_balances bb, xtr_bank_accounts ba,
1724: xtr_deal_date_amounts dd, xtr_accrls_amort aa
1725: WHERE bb.company_code = p_company_code
1726: AND bb.account_number = p_account_number
1727: AND bb.company_code = ba.party_code

Line 1753: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1749: END CHK_ACCRUAL_ON_RENDER;
1750:
1751: FUNCTION CHK_ACCRUAL_ON_RENDER
1752: ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1753: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1754: )return VARCHAR2 is
1755: l_return VARCHAR2(2);
1756: l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1757: l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;

Line 1782: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

1778:
1779:
1780: FUNCTION CHK_REVAL_ON_RENDER
1781: ( p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
1782: , p_balance_date IN xtr_bank_balances.balance_date%TYPE
1783: )return VARCHAR2 is
1784: l_return VARCHAR2(2);
1785: l_company_code XTR_BANK_ACCOUNTS.party_code%TYPE;
1786: l_account_number XTR_BANK_ACCOUNTS.account_number%TYPE;

Line 1814: p_balance_date IN xtr_bank_balances.balance_date%TYPE DEFAULT NULL

1810:
1811: PROCEDURE LOG_ERR_MSG
1812: ( p_error_code IN VARCHAR2,
1813: p_field_name IN VARCHAR2 DEFAULT NULL,
1814: p_balance_date IN xtr_bank_balances.balance_date%TYPE DEFAULT NULL
1815: )
1816: IS
1817:
1818: BEGIN

Line 1905: xtr_bank_balances IF THE same has been updated IN THE xtr_bank_accounts TABLE

1901: END LOG_ERR_MSG;
1902:
1903:
1904: /* This procedure updates the rounding_type and the day_count_type in the
1905: xtr_bank_balances IF THE same has been updated IN THE xtr_bank_accounts TABLE
1906:
1907: This will be called FORM xtr_replicate_bank_accounts API WHEN THE rounding TYPE/
1908: day_count_type has been updated IN THE interest schedules page OR THE
1909: schedule has been changed BY THE USER */

Line 1943: FROM XTR_BANK_BALANCES

1939: -- Gets the maximum balance date and the minimum balance date for that
1940: -- company and account
1941: CURSOR c_bal_date IS
1942: SELECT max(balance_date), min(balance_date)
1943: FROM XTR_BANK_BALANCES
1944: WHERE company_code = l_party_code
1945: AND account_number = l_account_number;
1946:
1947: -- Checking for record lock

Line 1950: FROM xtr_bank_balances

1946:
1947: -- Checking for record lock
1948: CURSOR c_chk_lock IS
1949: SELECT day_count_type
1950: FROM xtr_bank_balances
1951: WHERE company_code = l_party_code
1952: AND account_number = l_account_number
1953: FOR UPDATE NOWAIT;
1954:

Line 1986: UPDATE xtr_bank_balances

1982: CLOSE c_chk_lock;
1983:
1984: IF l_batch_error IS NULL THEN
1985:
1986: UPDATE xtr_bank_balances
1987: SET rounding_type = p_rounding_type,
1988: day_count_type = p_day_count_type
1989: WHERE company_code =l_party_code
1990: AND account_number = l_account_number;

Line 2002: UPDATE xtr_bank_balances

1998:
1999:
2000: ELSIF l_batch_error IS NOT NULL AND l_acc_status IS NOT NULL THEN
2001:
2002: UPDATE xtr_bank_balances
2003: SET rounding_type = p_rounding_type,
2004: day_count_type = p_day_count_type
2005: WHERE company_code =l_party_code
2006: AND account_number = l_account_number

Line 2052: from xtr_bank_balances

2048:
2049: -- Settlement Check cursor
2050: cursor CHK_SETTLE is
2051: select 'Y'
2052: from xtr_bank_balances
2053: where account_number = p_ACCOUNT_NUMBER
2054: and nvl(interest_settled,0) <> 0;
2055:
2056: -- Accrual Check cursor

Line 2122: from xtr_bank_balances

2118: l_acc_status varchar2(20);
2119:
2120: cursor CHK_ACC_INT is
2121: select accum_int_cfwd
2122: from xtr_bank_balances
2123: where account_number = p_ACCOUNT_NUMBER
2124: and company_code = p_PARTY_CODE
2125: and balance_date = (select max(balance_date)
2126: from xtr_bank_balances

Line 2126: from xtr_bank_balances

2122: from xtr_bank_balances
2123: where account_number = p_ACCOUNT_NUMBER
2124: and company_code = p_PARTY_CODE
2125: and balance_date = (select max(balance_date)
2126: from xtr_bank_balances
2127: where account_number = p_Account_Number
2128: and company_code = p_PARTY_CODE);
2129:
2130: BEGIN

Line 2150: ,p_balance_date IN xtr_bank_balances.balance_date%TYPE

2146:
2147:
2148: PROCEDURE CHK_ACCRUAL_REVAL_WARNINGS
2149: (p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
2150: ,p_balance_date IN xtr_bank_balances.balance_date%TYPE
2151: ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null
2152: ,p_interest_calc_balance IN NUMBER
2153: ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
2154: ,p_action_flag IN VARCHAR2

Line 2151: ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null

2147:
2148: PROCEDURE CHK_ACCRUAL_REVAL_WARNINGS
2149: (p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
2150: ,p_balance_date IN xtr_bank_balances.balance_date%TYPE
2151: ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null
2152: ,p_interest_calc_balance IN NUMBER
2153: ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
2154: ,p_action_flag IN VARCHAR2
2155: ,x_return_status OUT NOCOPY VARCHAR2

Line 2153: ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE

2149: (p_ce_bank_account_id IN xtr_bank_accounts.ce_bank_account_id%TYPE
2150: ,p_balance_date IN xtr_bank_balances.balance_date%TYPE
2151: ,p_ce_bank_account_balance_id IN xtr_bank_balances.ce_bank_account_balance_id%TYPE default null
2152: ,p_interest_calc_balance IN NUMBER
2153: ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE
2154: ,p_action_flag IN VARCHAR2
2155: ,x_return_status OUT NOCOPY VARCHAR2
2156: ,x_msg_count OUT NOCOPY NUMBER
2157: ,x_msg_data OUT NOCOPY VARCHAR2 )IS