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.20.12020000.2 2013/01/17 06:25:21 rtumati ship $ | */
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 := nvl(p_ledger_balance,0);

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
260: l_xtr_bank_balances_rec.one_day_float := p_one_day_float;

Line 257: l_xtr_bank_balances_rec.statement_balance := nvl(p_ledger_balance,0);

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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 := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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 := nvl(p_available_balance,0);

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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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 := nvl(p_ledger_balance,0);
258: l_xtr_bank_balances_rec.balance_adjustment := (nvl(p_interest_calculated_balance,0) - nvl(p_ledger_balance,0));
259: l_xtr_bank_balances_rec.balance_cflow := nvl(p_available_balance,0);
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 531: INSERT INTO XTR_BANK_BALANCES

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

Line 599: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

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

Line 606: FROM xtr_bank_balances

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

Line 613: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;

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

Line 625: UPDATE xtr_bank_balances SET

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

Line 659: ( p_balance_rec IN xtr_bank_balances%ROWTYPE,

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

Line 666: l_ce_bank_acct_bal_id xtr_bank_balances.ce_bank_account_balance_id%TYPE;

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

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

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

Line 698: FROM xtr_bank_balances

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

Line 707: FROM xtr_bank_balances

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

Line 727: DELETE FROM XTR_BANK_BALANCES

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

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

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

Line 796: p_account_number IN xtr_bank_balances.account_number%TYPE,

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

Line 797: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

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

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

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

Line 831: FROM XTR_BANK_BALANCES_V A,

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

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

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

Line 1020: p_account_number IN xtr_bank_balances.account_number%TYPE,

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

Line 1021: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

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

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

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

Line 1033: FROM XTR_BANK_BALANCES

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

Line 1038: l_balance_rec XTR_BANK_BALANCES%ROWTYPE;

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

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

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

Line 1138: , p_account_number IN xtr_bank_balances.account_number%TYPE

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

Line 1140: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

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

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

Line 1158: xtr_bank_balances bb, xtr_bank_accounts ba,

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

Line 1186: xtr_bank_balances bb

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

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

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

Line 1251: , p_account_number IN xtr_bank_balances.account_number%TYPE

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

Line 1253: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

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

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

Line 1255: , p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE

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

Line 1265: l_balance_cflow xtr_bank_balances.balance_cflow%TYPE;

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

Line 1284: xtr_bank_balances bb,xtr_bank_accounts ba

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

Line 1298: xtr_bank_balances bb,xtr_bank_accounts ba,

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

Line 1313: xtr_bank_balances bb

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

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

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

Line 1385: p_account_number IN xtr_bank_balances.account_number%TYPE,

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

Line 1386: p_balance_date IN xtr_bank_balances.balance_date%TYPE,

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

Line 1398: l_bal_date xtr_bank_balances.balance_date%TYPE;

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

Line 1399: l_accum_int_cfwd xtr_bank_balances.accum_int_cfwd%TYPE;

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

Line 1428: FROM XTR_BANK_BALANCES_V

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

Line 1437: FROM XTR_BANK_BALANCES

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

Line 1441: XTR_BANK_BALANCES

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

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

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

Line 1531: p_account_number IN xtr_bank_balances.account_number%TYPE,

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

Line 1532: p_balance_date IN xtr_bank_balances.balance_date%TYPE)

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

Line 1537: FROM XTR_BANK_BALANCES a

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

Line 1544: FROM XTR_BANK_BALANCES a

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

Line 1548: FROM xtr_bank_balances b

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

Line 1588: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

Line 1646: ( p_company_code IN xtr_bank_balances.company_code%TYPE

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

Line 1647: , p_account_number IN xtr_bank_balances.account_number%TYPE

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

Line 1649: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

Line 1660: xtr_bank_balances bb,xtr_bank_accounts ba,

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

Line 1675: xtr_bank_balances bb,xtr_bank_accounts ba

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

Line 1708: ( p_company_code IN xtr_bank_balances.company_code%TYPE

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

Line 1709: , p_account_number IN xtr_bank_balances.account_number%TYPE

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

Line 1711: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

Line 1721: xtr_bank_balances bb, xtr_bank_accounts ba,

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

Line 1751: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

Line 1780: , p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

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

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

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

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

Line 1941: FROM XTR_BANK_BALANCES

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

Line 1948: FROM xtr_bank_balances

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

Line 1984: UPDATE xtr_bank_balances

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

Line 2000: UPDATE xtr_bank_balances

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

Line 2050: from xtr_bank_balances

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

Line 2120: from xtr_bank_balances

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

Line 2124: from xtr_bank_balances

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

Line 2148: ,p_balance_date IN xtr_bank_balances.balance_date%TYPE

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

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

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

Line 2151: ,p_balance_cflow IN xtr_bank_balances.balance_cflow%TYPE

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

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

2225:
2226: -- bug 9742850 starts
2227:
2228: PROCEDURE VALIDATE_OD_LIMIT_BALANCE
2229: ( p_company_code IN xtr_bank_balances.company_code%TYPE,
2230: p_branch_id IN NUMBER,
2231: p_bank_accont_id IN NUMBER,
2232: p_ledger_balance IN NUMBER,
2233: x_return_status OUT NOCOPY VARCHAR2,