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.BIM_FUND_FACTS dependencies on BIM_R_FUND_DAILY_FACTS

Line 79: -- table. It gathers data from bim_r_fund_daily_facts,

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: ---------------------------------------------------------------------
83: PROCEDURE update_balance

Line 115: FROM bim_r_fund_daily_facts b ,

111: nvl(b.original_budget,0)+nvl(b.transfer_in,0)-nvl(b.transfer_out,0)-nvl(c.commited_amt,0) balance,
112: nvl(c.utilized_amt,0) utilized,
113: nvl(c.planned,0) planned,
114: nvl(c.paid,0) paid
115: FROM bim_r_fund_daily_facts b ,
116: (SELECT
117: c1.fund_id fund_id,
118: c1.week_date week_date,
119: SUM(nvl(fdsp.commited_amt,0)) commited_amt,

Line 126: FROM bim_r_fund_daily_facts

122: SUM(nvl(fdsp.paid_amt,0)) paid
123: FROM bim_r_fdsp_daily_facts fdsp,
124: (SELECT fund_id fund_id,
125: MIN(transaction_create_date) week_date
126: FROM bim_r_fund_daily_facts
127: GROUP BY fund_id) c1
128: WHERE fdsp.fund_id(+) = c1.fund_id
129: AND fdsp.transaction_create_date(+) = c1.week_date
130: GROUP BY c1.fund_id, c1.week_date ) c

Line 145: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';

141: begin
142: fnd_message.set_name('BIM','BIM_R_PROC_START');
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

Line 160: FROM bim_r_fund_daily_facts;

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:
163: select i.index_tablespace into l_index_tablespace
164: from fnd_product_installations i, fnd_application a

Line 177: 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts

173: END;
174:
175: BEGIN
176: EXECUTE IMMEDIATE
177: 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts
178: (fund_id,
179: transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
180: EXCEPTION
181: WHEN OTHERS THEN

Line 266: bim_r_fund_daily_facts a,

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,
270: SUM(nvl(paid_amt,0)) paid,

Line 323: FROM bim_r_fund_daily_facts w

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
325: from bim_r_fund_balance b
326: where b.fund_id = w.fund_id
327: and b.week_date = w.transaction_create_date)

Line 350: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts noparallel';

346: fnd_message.set_name('BIM','BIM_R_PROC_START');
347: fnd_message.set_token('proc_name', 'UPDATE_SUB_BALANCE', FALSE);
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

Line 372: 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts

368: END;
369:
370: BEGIN
371: EXECUTE IMMEDIATE
372: 'create index bim_r_fund_facts_n6 on bim_r_fund_daily_facts
373: (fund_id,
374: transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
375: EXCEPTION
376: WHEN OTHERS THEN

Line 444: bim_r_fund_daily_facts a,

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)+
448: nvl(market_expense,0)) utilized,

Line 500: FROM bim_r_fund_daily_facts a,

496: nvl(fdsp.paid,0),
497: a.fis_month,
498: a.fis_qtr,
499: a.fis_year
500: FROM bim_r_fund_daily_facts a,
501: (SELECT fund_id fund_id,
502: SUM(nvl(commited_amt,0)) commited_amt,
503: SUM(nvl(standard_discount,0)+nvl(accrual,0)+
504: nvl(market_expense,0)) utilized,

Line 549: bim_r_fund_daily_facts a,

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)+
553: nvl(market_expense,0)) utilized,

Line 831: -- and p_end_date will be inserted into bim_r_fund_daily_facts,

827: -- PROCEDURE
828: -- FUND_FIRST_LOAD
829: -- NOTE This procedure will be executed when load data for first time.
830: -- Transactions for funds whose active date during p_start_date
831: -- and p_end_date will be inserted into bim_r_fund_daily_facts,
832: -- and transactions happened before fund active data are also captured.
833: ---------------------------------------------------------------------
834: PROCEDURE FUND_FIRST_LOAD(
835: p_start_datel DATE,

Line 974: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';

970:
971: BEGIN
972:
973: --for performance reasons
974: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
975: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
976: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
977: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
978: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';

Line 978: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';

974: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
975: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
976: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
977: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
978: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
979:
980: l_table_name :='bim_r_fund_daily_facts';
981: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
982: fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);

Line 980: l_table_name :='bim_r_fund_daily_facts';

976: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
977: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
978: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
979:
980: l_table_name :='bim_r_fund_daily_facts';
981: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
982: fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);
983: fnd_file.put_line(fnd_file.log,fnd_message.get);
984: /* First insert: Insert all the transactions: transfer in/out for funds whose active date between p_start_date and p_end_date */

Line 982: fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);

978: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
979:
980: l_table_name :='bim_r_fund_daily_facts';
981: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
982: fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);
983: fnd_file.put_line(fnd_file.log,fnd_message.get);
984: /* First insert: Insert all the transactions: transfer in/out for funds whose active date between p_start_date and p_end_date */
985: INSERT /*+ append parallel(fdf,p_para_num) */
986: INTO bim_r_fund_daily_facts fdf(

Line 986: INTO bim_r_fund_daily_facts fdf(

982: fnd_message.set_token('TABLE_NAME','bim_r_fund_daily_facts', FALSE);
983: fnd_file.put_line(fnd_file.log,fnd_message.get);
984: /* First insert: Insert all the transactions: transfer in/out for funds whose active date between p_start_date and p_end_date */
985: INSERT /*+ append parallel(fdf,p_para_num) */
986: INTO bim_r_fund_daily_facts fdf(
987: fund_transaction_id
988: ,creation_date
989: ,last_update_date
990: ,created_by

Line 1020: bim_r_fund_daily_facts_s.nextval,

1016: ,fis_qtr
1017: ,fis_year
1018: ,business_unit_id)
1019: SELECT /*+ parallel(inner, p_para_num) */
1020: bim_r_fund_daily_facts_s.nextval,
1021: sysdate,
1022: sysdate,
1023: l_user_id,
1024: l_user_id,

Line 1295: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';

1291: original_budget
1292: )inner;
1293: l_count:=l_count+SQL%ROWCOUNT;
1294: EXECUTE IMMEDIATE 'COMMIT';
1295: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1296: EXCEPTION
1297: WHEN others THEN
1298: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1299: x_return_status := FND_API.G_RET_STS_ERROR;

Line 1298: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';

1294: EXECUTE IMMEDIATE 'COMMIT';
1295: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1296: EXCEPTION
1297: WHEN others THEN
1298: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
1299: x_return_status := FND_API.G_RET_STS_ERROR;
1300: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1301: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1302: FND_MSG_PUB.Add;

Line 1310: fnd_message.set_token('table_name', 'bim_r_fund_daily_facts', FALSE);

1306: /*Second insert: more records which transact before active date */
1307: BEGIN
1308: -- dbms_output.put_line('inserting extra');
1309: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1310: fnd_message.set_token('table_name', 'bim_r_fund_daily_facts', FALSE);
1311: fnd_file.put_line(fnd_file.log,fnd_message.get);
1312: INSERT
1313: INTO bim_r_fund_daily_facts fdf(
1314: fund_transaction_id

Line 1313: INTO bim_r_fund_daily_facts fdf(

1309: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1310: fnd_message.set_token('table_name', 'bim_r_fund_daily_facts', FALSE);
1311: fnd_file.put_line(fnd_file.log,fnd_message.get);
1312: INSERT
1313: INTO bim_r_fund_daily_facts fdf(
1314: fund_transaction_id
1315: ,creation_date
1316: ,last_update_date
1317: ,created_by

Line 1348: bim_r_fund_daily_facts_s.nextval,

1344: ,fis_year
1345: ,business_unit_id)
1346: -- ,security_group_id)
1347: SELECT
1348: bim_r_fund_daily_facts_s.nextval,
1349: sysdate,
1350: sysdate,
1351: l_user_id,
1352: l_user_id,

Line 1534: --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));

1530: --End inserting transactions happened before start_date
1531: EXCEPTION
1532: WHEN others THEN
1533: FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
1534: --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
1535: x_return_status := FND_API.G_RET_STS_ERROR;
1536: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1537: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1538: FND_MSG_PUB.Add;

Line 2098: /* There are discrete dates in bim_r_fund_daily_facts and bim_r_fdsp_daily_facts,because of

2094: x_msg_data,
2095: x_return_status) ;
2096: --END IF;
2097:
2098: /* There are discrete dates in bim_r_fund_daily_facts and bim_r_fdsp_daily_facts,because of
2099: the pre-approvals. So, we want to make them have the same dates by inserting into each
2100: table the dates in one table but not in another table for the same funds.*/
2101: BEGIN
2102: -- dbms_output.put_line('balancing');

Line 2104: /* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts

2100: table the dates in one table but not in another table for the same funds.*/
2101: BEGIN
2102: -- dbms_output.put_line('balancing');
2103:
2104: /* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts
2105: but not in bim_r_fund_daily_facts. */
2106: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2107: fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);
2108: fnd_file.put_line(fnd_file.log,fnd_message.get);

Line 2105: but not in bim_r_fund_daily_facts. */

2101: BEGIN
2102: -- dbms_output.put_line('balancing');
2103:
2104: /* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts
2105: but not in bim_r_fund_daily_facts. */
2106: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2107: fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);
2108: fnd_file.put_line(fnd_file.log,fnd_message.get);
2109: INSERT into bim_r_fund_daily_facts(

Line 2107: fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);

2103:
2104: /* Insert into bim_r_fund_daily_facts the dates which are in bim_r_fdsp_daily_facts
2105: but not in bim_r_fund_daily_facts. */
2106: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2107: fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);
2108: fnd_file.put_line(fnd_file.log,fnd_message.get);
2109: INSERT into bim_r_fund_daily_facts(
2110: fund_transaction_id
2111: ,creation_date

Line 2109: INSERT into bim_r_fund_daily_facts(

2105: but not in bim_r_fund_daily_facts. */
2106: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2107: fnd_message.set_token('table_name', 'BIM_R_FUND_DAILY_FACTS', FALSE);
2108: fnd_file.put_line(fnd_file.log,fnd_message.get);
2109: INSERT into bim_r_fund_daily_facts(
2110: fund_transaction_id
2111: ,creation_date
2112: ,last_update_date
2113: ,created_by

Line 2143: bim_r_fund_daily_facts_s.nextval,

2139: ,fis_qtr
2140: ,fis_year
2141: ,business_unit_id)
2142: SELECT
2143: bim_r_fund_daily_facts_s.nextval,
2144: sysdate,
2145: sysdate,
2146: l_user_id,
2147: l_user_id,

Line 2195: from bim_r_fund_daily_facts b1

2191: WHERE fund_id is not null
2192: AND transaction_create_date is not null
2193: AND (fund_id, transaction_create_date) not in
2194: ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
2195: from bim_r_fund_daily_facts b1
2196: )) b2
2197: WHERE b2.fund_id = a.fund_id;
2198: EXECUTE IMMEDIATE 'commit';
2199: EXCEPTION

Line 2304: FROM bim_r_fund_daily_facts

2300: ,SUM(transfer_out) transfer_out
2301: ,SUM(holdback_amt) holdback_amt
2302: ,currency_code_fc currency_code_fc
2303: ,load_date load_date
2304: FROM bim_r_fund_daily_facts
2305: GROUP BY
2306: fund_id
2307: ,load_date
2308: ,parent_fund_id

Line 2482: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,

2478: WHEN OTHERS THEN
2479: null;
2480: END;
2481: BEGIN
2482: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,
2483: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2484: END;
2485: BEGIN
2486: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,

Line 2565: l_table_name :='bim_r_fund_daily_facts';

2561: l_end_date :=trunc(sysdate-1);
2562: ELSE
2563: l_end_date :=p_end_datel;
2564: END IF;
2565: l_table_name :='bim_r_fund_daily_facts';
2566: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2567: fnd_message.set_token('table_name', l_table_name, FALSE);
2568: fnd_file.put_line(fnd_file.log,fnd_message.get);
2569: --dbms_output.put_line('b4 first inserting into fund daily');

Line 2572: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';

2568: fnd_file.put_line(fnd_file.log,fnd_message.get);
2569: --dbms_output.put_line('b4 first inserting into fund daily');
2570:
2571: BEGIN -- Alter table for performance reasons
2572: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
2573: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
2574: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
2575: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
2576: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';

Line 2576: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';

2572: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_daily_facts nologging';
2573: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fund_weekly_facts nologging';
2574: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';
2575: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_weekly_facts nologging';
2576: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
2577:
2578: /* First insert: Insert transactions happened between p_start_date and p_end_date. */
2579: INSERT /*+ append parallel(fdf,p_para_num) */
2580: INTO bim_r_fund_daily_facts fdf(

Line 2580: INTO bim_r_fund_daily_facts fdf(

2576: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 1000';
2577:
2578: /* First insert: Insert transactions happened between p_start_date and p_end_date. */
2579: INSERT /*+ append parallel(fdf,p_para_num) */
2580: INTO bim_r_fund_daily_facts fdf(
2581: fund_transaction_id
2582: ,creation_date
2583: ,last_update_date
2584: ,created_by

Line 2615: bim_r_fund_daily_facts_s.nextval,

2611: ,fis_year
2612: ,business_unit_id)
2613: --,security_group_id)
2614: SELECT /*+ parallel(inner, p_para_num) */
2615: bim_r_fund_daily_facts_s.nextval,
2616: sysdate,
2617: sysdate,
2618: l_user_id,
2619: l_user_id,

Line 2795: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';

2791: original_budget
2792: )inner;
2793: l_count:=l_count+SQL%ROWCOUNT;
2794: EXECUTE IMMEDIATE 'COMMIT';
2795: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2796: EXCEPTION
2797: WHEN others THEN
2798: FND_FILE.put_line(fnd_file.log,'error insert into fund_daily'||sqlerrm(sqlcode));
2799: --dbms_output.put_line('error inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));

Line 2799: --dbms_output.put_line('error inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));

2795: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2796: EXCEPTION
2797: WHEN others THEN
2798: FND_FILE.put_line(fnd_file.log,'error insert into fund_daily'||sqlerrm(sqlcode));
2799: --dbms_output.put_line('error inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
2800: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2801: x_return_status := FND_API.G_RET_STS_ERROR;
2802: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2803: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 2800: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';

2796: EXCEPTION
2797: WHEN others THEN
2798: FND_FILE.put_line(fnd_file.log,'error insert into fund_daily'||sqlerrm(sqlcode));
2799: --dbms_output.put_line('error inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
2800: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fund_daily_facts_s CACHE 20';
2801: x_return_status := FND_API.G_RET_STS_ERROR;
2802: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2803: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2804: FND_MSG_PUB.Add;

Line 2814: INTO bim_r_fund_daily_facts fdf(

2810: fnd_message.set_token('table_name', l_table_name, FALSE);
2811: fnd_file.put_line(fnd_file.log,fnd_message.get);
2812: BEGIN
2813: INSERT /*+ append parallel(fdf,p_para_num) */
2814: INTO bim_r_fund_daily_facts fdf(
2815: fund_transaction_id
2816: ,creation_date
2817: ,last_update_date
2818: ,created_by

Line 2849: bim_r_fund_daily_facts_s.nextval,

2845: ,fis_year
2846: ,business_unit_id)
2847: --,security_group_id)
2848: SELECT /*+ parallel(inner, p_para_num) */
2849: bim_r_fund_daily_facts_s.nextval,
2850: sysdate,
2851: sysdate,
2852: l_user_id,
2853: l_user_id,

Line 3035: --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));

3031: --End inserting transactions happened before start_date
3032: EXCEPTION
3033: WHEN others THEN
3034: FND_FILE.put_line(fnd_file.log,'error insert into fund_daily for transactions happened b4 start date'||sqlerrm(sqlcode));
3035: --dbms_output.put_line('error looping inserting INTO bim_r_fund_daily_facts'||sqlerrm(sqlcode));
3036: x_return_status := FND_API.G_RET_STS_ERROR;
3037: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3038: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3039: FND_MSG_PUB.Add;

Line 3049: INTO bim_r_fund_daily_facts fdf(

3045: fnd_message.set_token('table_name', l_table_name, FALSE);
3046: fnd_file.put_line(fnd_file.log,fnd_message.get);
3047: BEGIN
3048: INSERT /*+ append parallel(fdf,p_para_num) */
3049: INTO bim_r_fund_daily_facts fdf(
3050: fund_transaction_id
3051: ,creation_date
3052: ,last_update_date
3053: ,created_by

Line 3084: bim_r_fund_daily_facts_s.nextval,

3080: ,fis_year
3081: ,business_unit_id)
3082: --,security_group_id)
3083: SELECT /*+ parallel(inner, p_para_num) */
3084: bim_r_fund_daily_facts_s.nextval,
3085: sysdate,
3086: sysdate,
3087: l_user_id,
3088: l_user_id,

Line 3144: FROM bim_r_fund_daily_facts a,

3140: a.set_of_books_id set_of_books_id,
3141: a.currency_code_fc currency_code_fc,
3142: a.original_budget original_budget,
3143: trunc(b.trdate) trdate
3144: FROM bim_r_fund_daily_facts a,
3145: bim_intl_dates b
3146: WHERE b.trdate between p_start_datel and p_end_datel+0.99999
3147: AND b.trdate between a.start_date and nvl(a.end_date,p_end_datel)+0.99999
3148: AND (a.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date

Line 3149: from bim_r_fund_daily_facts c

3145: bim_intl_dates b
3146: WHERE b.trdate between p_start_datel and p_end_datel+0.99999
3147: AND b.trdate between a.start_date and nvl(a.end_date,p_end_datel)+0.99999
3148: AND (a.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3149: from bim_r_fund_daily_facts c
3150: where c.fund_id = a.fund_id
3151: and c.transaction_create_date = trunc(b.trdate))) inner;
3152: l_count :=l_count +SQL%ROWCOUNT;
3153: EXECUTE IMMEDIATE 'commit';

Line 3172: INTO bim_r_fund_daily_facts fdf(

3168: fnd_message.set_token('table_name', l_table_name, FALSE);
3169: fnd_file.put_line(fnd_file.log,fnd_message.get);
3170: BEGIN
3171: INSERT /*+ append parallel(fdf,p_para_num) */
3172: INTO bim_r_fund_daily_facts fdf(
3173: fund_transaction_id
3174: ,creation_date
3175: ,last_update_date
3176: ,created_by

Line 3207: bim_r_fund_daily_facts_s.nextval,

3203: ,fis_year
3204: ,business_unit_id)
3205: --,security_group_id)
3206: SELECT /*+ parallel(inner, p_para_num) */
3207: bim_r_fund_daily_facts_s.nextval,
3208: sysdate,
3209: sysdate,
3210: l_user_id,
3211: l_user_id,

Line 3277: from bim_r_fund_daily_facts c

3273: AND ad.start_date_active between p_start_datel and p_end_datel
3274: AND b.trdate between p_start_datel and p_end_datel+0.99999
3275: AND b.trdate between ad.start_date_active and nvl(ad.end_date_active,p_end_datel)+0.99999
3276: AND (ad.fund_id, trunc(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3277: from bim_r_fund_daily_facts c
3278: where c.fund_id = ad.fund_id
3279: and c.transaction_create_date = trunc(b.trdate))) inner;
3280: l_count :=l_count +SQL%ROWCOUNT;
3281: EXECUTE IMMEDIATE 'commit';

Line 3920: /* Insert into bim_r_fdsp_daily_facts the dates which are in bim_r_fund_daily_facts

3916: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3917: FND_MSG_PUB.Add;
3918: END;
3919:
3920: /* Insert into bim_r_fdsp_daily_facts the dates which are in bim_r_fund_daily_facts
3921: but not in bim_r_fdsp_daily_facts. */
3922: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3923: fnd_message.set_token('table_name', l_table_name, FALSE);
3924: fnd_file.put_line(fnd_file.log,fnd_message.get);

Line 3980: FROM bim_r_fund_daily_facts fd

3976: b1.fis_year
3977: FROM bim_r_fdsp_daily_facts b1,
3978: (SELECT distinct fund_id fund_id,
3979: transaction_create_date transaction_create_date
3980: FROM bim_r_fund_daily_facts fd
3981: WHERE (fund_id, transaction_create_date) not in
3982: ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
3983: from bim_r_fdsp_daily_facts
3984: where fund_id is not null

Line 3999: --Insert dummy records into bim_r_fund_daily_facts for balancing

3995: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3996: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3997: FND_MSG_PUB.Add;
3998: END;
3999: --Insert dummy records into bim_r_fund_daily_facts for balancing
4000: /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
4001: but not in bim_r_fund_daily_facts */
4002: BEGIN
4003: l_table_name :='bim_r_fund_daily_facts';

Line 4000: /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts

3996: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3997: FND_MSG_PUB.Add;
3998: END;
3999: --Insert dummy records into bim_r_fund_daily_facts for balancing
4000: /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
4001: but not in bim_r_fund_daily_facts */
4002: BEGIN
4003: l_table_name :='bim_r_fund_daily_facts';
4004: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');

Line 4001: but not in bim_r_fund_daily_facts */

3997: FND_MSG_PUB.Add;
3998: END;
3999: --Insert dummy records into bim_r_fund_daily_facts for balancing
4000: /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
4001: but not in bim_r_fund_daily_facts */
4002: BEGIN
4003: l_table_name :='bim_r_fund_daily_facts';
4004: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4005: fnd_message.set_token('table_name', l_table_name, FALSE);

Line 4003: l_table_name :='bim_r_fund_daily_facts';

3999: --Insert dummy records into bim_r_fund_daily_facts for balancing
4000: /* Insert into bim_r_fund_daily_facts dates which are in bim_r_fdsp_daily_facts
4001: but not in bim_r_fund_daily_facts */
4002: BEGIN
4003: l_table_name :='bim_r_fund_daily_facts';
4004: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4005: fnd_message.set_token('table_name', l_table_name, FALSE);
4006: fnd_file.put_line(fnd_file.log,fnd_message.get);
4007: INSERT into bim_r_fund_daily_facts(

Line 4007: INSERT into bim_r_fund_daily_facts(

4003: l_table_name :='bim_r_fund_daily_facts';
4004: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4005: fnd_message.set_token('table_name', l_table_name, FALSE);
4006: fnd_file.put_line(fnd_file.log,fnd_message.get);
4007: INSERT into bim_r_fund_daily_facts(
4008: fund_transaction_id
4009: ,creation_date
4010: ,last_update_date
4011: ,created_by

Line 4041: bim_r_fund_daily_facts_s.nextval,

4037: ,fis_qtr
4038: ,fis_year
4039: ,business_unit_id)
4040: SELECT
4041: bim_r_fund_daily_facts_s.nextval,
4042: sysdate,
4043: sysdate,
4044: l_user_id,
4045: l_user_id,

Line 4093: from bim_r_fund_daily_facts b1

4089: WHERE fund_id is not null
4090: AND transaction_create_date is not null
4091: AND (fund_id, transaction_create_date) not in
4092: ( SELECT /*+ hash_aj */ fund_id, transaction_create_date
4093: from bim_r_fund_daily_facts b1
4094: )) b2
4095: WHERE b2.fund_id = a.fund_id;
4096: EXECUTE IMMEDIATE 'commit';
4097: EXCEPTION

Line 4213: FROM bim_r_fund_daily_facts

4209: ,SUM(transfer_out) transfer_out
4210: ,SUM(holdback_amt) holdback_amt
4211: ,currency_code_fc currency_code_fc
4212: ,load_date load_date
4213: FROM bim_r_fund_daily_facts
4214: GROUP BY
4215: fund_id
4216: ,load_date
4217: ,parent_fund_id

Line 4349: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,

4345: END;
4346:
4347: --Analysis tables for better performance
4348: BEGIN
4349: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_DAILY_FACTS', estimate_percent => 5,
4350: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4351: END;
4352: BEGIN
4353: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,