[Home] [Help]
74: ---------------------------------------------------------------------
75: -- PROCEDURE
76: -- update_balance
77: -- NOTE: This procedure will be called only once within FUND_FIRST_LOAD
78: -- It inserts data into bim_r_fund_balance, which is a daily balance
79: -- table. It gathers data from bim_r_fund_daily_facts,
80: -- and bim_r_fdsp_daily_facts. Calculates balance based on data from
81: -- those two tables.
82: ---------------------------------------------------------------------
89: /*cursor balance_cur is to get all the fund_id, and all the
90: transaction date excluding the minimum date. */
91: CURSOR balance_cur IS
92: SELECT fund_id,week_date
93: FROM bim_r_fund_balance a
94: WHERE exists --week_date not in
95: ( SELECT /*+ index_desc(b,bim_r_fund_balance_n6) */ null
96: FROM bim_r_fund_balance b
97: WHERE b.fund_id = a.fund_id
91: CURSOR balance_cur IS
92: SELECT fund_id,week_date
93: FROM bim_r_fund_balance a
94: WHERE exists --week_date not in
95: ( SELECT /*+ index_desc(b,bim_r_fund_balance_n6) */ null
96: FROM bim_r_fund_balance b
97: WHERE b.fund_id = a.fund_id
98: AND b.week_date < a.week_date )
99: order by fund_id, week_date ;
92: SELECT fund_id,week_date
93: FROM bim_r_fund_balance a
94: WHERE exists --week_date not in
95: ( SELECT /*+ index_desc(b,bim_r_fund_balance_n6) */ null
96: FROM bim_r_fund_balance b
97: WHERE b.fund_id = a.fund_id
98: AND b.week_date < a.week_date )
99: order by fund_id, week_date ;
100:
143: fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
144: fnd_file.put_line(fnd_file.log,fnd_message.get);
145: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';
146: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';
147: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
148: exception
149: WHEN OTHERS THEN
150: ams_utility_pvt.write_conc_log('error alter tablebalance:'||sqlerrm(sqlcode));
151: --dbms_output.put_line('error alter table balance'||sqlerrm(sqlcode));
150: ams_utility_pvt.write_conc_log('error alter tablebalance:'||sqlerrm(sqlcode));
151: --dbms_output.put_line('error alter table balance'||sqlerrm(sqlcode));
152: END;
153:
154: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fund_balance';
155: --dbms_output.put_line('inside update balance');
156:
157: --Insert all the fund_id, and transaction date to start with
158: INSERT into BIM_R_FUND_BALANCE (fund_id, week_date,fis_month, fis_qtr, fis_year)
154: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fund_balance';
155: --dbms_output.put_line('inside update balance');
156:
157: --Insert all the fund_id, and transaction date to start with
158: INSERT into BIM_R_FUND_BALANCE (fund_id, week_date,fis_month, fis_qtr, fis_year)
159: SELECT fund_id,transaction_create_date,fis_month, fis_qtr, fis_year
160: FROM bim_r_fund_daily_facts;
161:
162:
199: null;
200: END;
201:
202: BEGIN
203: EXECUTE IMMEDIATE 'drop index bim_r_fund_balance_n6';
204: EXCEPTION
205: WHEN OTHERS THEN
206: null;
207: END;
206: null;
207: END;
208:
209: BEGIN
210: EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
211: bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
212: EXCEPTION
213: WHEN OTHERS THEN
214: null;
207: END;
208:
209: BEGIN
210: EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
211: bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
212: EXCEPTION
213: WHEN OTHERS THEN
214: null;
215: END;
214: null;
215: END;
216:
217: BEGIN
218: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_BALANCE', estimate_percent => 5,
219: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
220: END;
221:
222: --Update the record of mininum date for each fund
221:
222: --Update the record of mininum date for each fund
223: For l in min_date LOOP
224: BEGIN
225: UPDATE BIM_R_FUND_BALANCE a
226: SET current_available = l.available,
227: past_available = l.original,
228: commited =l.commited,
229: commited_sum=l.commited_sum,
244: /* Update in loop: for each fund, each date, populate the balance,
245: commited, utilized, planned, paid */
246: FOR x in balance_cur LOOP
247: BEGIN
248: UPDATE bim_r_fund_balance fb
249: SET (past_available,current_available,commited,commited_sum,
250: current_balance, utilized,planned, paid)
251: =(
252: select /*+ use_nl(maxdate ba) */
258: nvl(fdsp.utilized_amt,0),
259: nvl(fdsp.planned, 0),
260: nvl(fdsp.paid,0)
261: from (select max(week_date) max_date
262: from bim_r_fund_balance
263: where fund_id = x.fund_id
264: and week_date < x.week_date) maxdate,
265: bim_r_fund_balance ba,
266: bim_r_fund_daily_facts a,
261: from (select max(week_date) max_date
262: from bim_r_fund_balance
263: where fund_id = x.fund_id
264: and week_date < x.week_date) maxdate,
265: bim_r_fund_balance ba,
266: bim_r_fund_daily_facts a,
267: (select fund_id fund_id,
268: SUM(nvl(commited_amt,0)) commited_amt,
269: SUM(nvl(planned_amt,0)) planned,
306: ---------------------------------------------------------------------
307: -- PROCEDURE
308: -- update_sub_balance
309: -- NOTE: This procedure will be called every time when FUND_SUB_LOAD
310: -- get executed. It inserts more data into bim_r_fund_balance.
311: -- It includes the new balance for existing fund, as well as for
312: -- new funds, which didn't have entry in bim_r_fund_balance.
313: ---------------------------------------------------------------------
314: PROCEDURE update_sub_balance(p_start_date DATE, p_end_date DATE)
308: -- update_sub_balance
309: -- NOTE: This procedure will be called every time when FUND_SUB_LOAD
310: -- get executed. It inserts more data into bim_r_fund_balance.
311: -- It includes the new balance for existing fund, as well as for
312: -- new funds, which didn't have entry in bim_r_fund_balance.
313: ---------------------------------------------------------------------
314: PROCEDURE update_sub_balance(p_start_date DATE, p_end_date DATE)
315: IS
316: l_oldid NUMBER:=0;
316: l_oldid NUMBER:=0;
317: l_index_tablespace varchar2(100);
318:
319: /* cursor fund_cur is to get the new funds, and the transaction
320: date which don't have entry in bim_r_fund_balance */
321: CURSOR fund_cur IS
322: SELECT distinct w.fund_id, w.transaction_create_date
323: FROM bim_r_fund_daily_facts w
324: WHERE not exists( select 1
321: CURSOR fund_cur IS
322: SELECT distinct w.fund_id, w.transaction_create_date
323: FROM bim_r_fund_daily_facts w
324: WHERE not exists( select 1
325: from bim_r_fund_balance b
326: where b.fund_id = w.fund_id
327: and b.week_date = w.transaction_create_date)
328: order by w.fund_id, w.transaction_create_date;
329:
327: and b.week_date = w.transaction_create_date)
328: order by w.fund_id, w.transaction_create_date;
329:
330: /* cursor fund_cur_delta is to get the exsiting funds, and its maximum
331: date entry in bim_r_fund_balance, so that new balance will be calcuated
332: based on it. */
333: CURSOR fund_cur_delta(p_date DATE) IS
334: SELECT distinct fund_id fund_id, max(week_date) mdate
335: FROM bim_r_fund_balance
331: date entry in bim_r_fund_balance, so that new balance will be calcuated
332: based on it. */
333: CURSOR fund_cur_delta(p_date DATE) IS
334: SELECT distinct fund_id fund_id, max(week_date) mdate
335: FROM bim_r_fund_balance
336: WHERE week_date
338:
339: l_status VARCHAR2(5);
348: fnd_file.put_line(fnd_file.log,fnd_message.get);
349: BEGIN
350: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';
351: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';
352: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
353: EXCEPTION
354: WHEN OTHERS THEN
355: FND_FILE.put_line(fnd_file.log,'error alter table in balance'||sqlerrm(sqlcode));
356: END;
394: null;
395: END;
396:
397: BEGIN
398: EXECUTE IMMEDIATE 'drop index bim_r_fund_balance_n6';
399: EXCEPTION
400: WHEN OTHERS THEN
401: null;
402: END;
401: null;
402: END;
403:
404: BEGIN
405: EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
406: bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
407: EXCEPTION
408: WHEN OTHERS THEN
409: null;
402: END;
403:
404: BEGIN
405: EXECUTE IMMEDIATE 'create index bim_r_fund_balance_n6 on
406: bim_r_fund_balance(fund_id,week_date) tablespace '||l_index_tablespace||' compute statistics';
407: EXCEPTION
408: WHEN OTHERS THEN
409: null;
410: END;
411:
412: --populate balance for existing funds.
413: BEGIN
414: FOR x in fund_cur_delta(p_start_date) LOOP
415: INSERT INTO bim_r_fund_balance(
416: FUND_ID
417: , WEEK_DATE
418: , CURRENT_AVAILABLE
419: , PAST_AVAILABLE
439: nvl(fdsp.paid,0),
440: a.fis_month,
441: a.fis_qtr,
442: a.fis_year
443: FROM bim_r_fund_balance ba,
444: bim_r_fund_daily_facts a,
445: (SELECT fund_id fund_id,
446: SUM(nvl(commited_amt,0)) commited_amt,
447: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
464: WHEN OTHERS THEN
465: FND_FILE.put_line(fnd_file.log,'error updateing delta balance'||sqlerrm(sqlcode));
466: END;
467:
468: --Insert new funds which don't have entries in bim_r_fund_balance.
469: BEGIN
470: FOR x in fund_cur LOOP
471: -- This is to populate the initial balance for each fund.
472: IF (x.fund_id <>l_oldid) THEN
469: BEGIN
470: FOR x in fund_cur LOOP
471: -- This is to populate the initial balance for each fund.
472: IF (x.fund_id <>l_oldid) THEN
473: INSERT INTO bim_r_fund_balance(
474: FUND_ID
475: , WEEK_DATE
476: , CURRENT_AVAILABLE
477: , PAST_AVAILABLE
513: AND a.transaction_create_date = x.transaction_create_date
514: AND fdsp.fund_id(+) = a.fund_id
515: AND fdsp.week_date(+)=a.transaction_create_date;
516: ELSE --to populate the balance for subsequent dates.
517: INSERT INTO bim_r_fund_balance(
518: FUND_ID
519: , WEEK_DATE
520: , PAST_AVAILABLE
521: , CURRENT_AVAILABLE
541: a.fis_month,
542: a.fis_qtr,
543: a.fis_year
544: FROM (select max(week_date) max_date
545: from bim_r_fund_balance
546: where fund_id = x.fund_id
547: and week_date < x.transaction_create_date) maxdate,
548: bim_r_fund_balance ba,
549: bim_r_fund_daily_facts a,
544: FROM (select max(week_date) max_date
545: from bim_r_fund_balance
546: where fund_id = x.fund_id
547: and week_date < x.transaction_create_date) maxdate,
548: bim_r_fund_balance ba,
549: bim_r_fund_daily_facts a,
550: (select fund_id fund_id,
551: sum(nvl(commited_amt,0)) commited_amt,
552: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
2472: -- For performance reasons.
2473: BEGIN
2474: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts noparallel';
2475: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts noparallel';
2476: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
2477: EXCEPTION
2478: WHEN OTHERS THEN
2479: null;
2480: END;
4337: -- for performance reasons
4338: BEGIN
4339: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts noparallel';
4340: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts noparallel';
4341: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_balance noparallel';
4342: EXCEPTION
4343: WHEN OTHERS THEN
4344: null;
4345: END;