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';
979:
980: l_table_name :='bim_r_fund_daily_facts';
981: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2332: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2333: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2334: FND_MSG_PUB.Add;
2335: END;
2336: /* Insert into bim_r_fdsp_weekly_facts. */
2337: BEGIN
2338: l_table_name :='bim_r_fdsp_weekly_facts';
2339: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2340: fnd_message.set_token('table_name', l_table_name, FALSE);
2334: FND_MSG_PUB.Add;
2335: END;
2336: /* Insert into bim_r_fdsp_weekly_facts. */
2337: BEGIN
2338: l_table_name :='bim_r_fdsp_weekly_facts';
2339: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2340: fnd_message.set_token('table_name', l_table_name, FALSE);
2341: fnd_file.put_line(fnd_file.log,fnd_message.get);
2342: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
2338: l_table_name :='bim_r_fdsp_weekly_facts';
2339: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
2340: fnd_message.set_token('table_name', l_table_name, FALSE);
2341: fnd_file.put_line(fnd_file.log,fnd_message.get);
2342: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
2343: INSERT /*+ append parallel(fwf,p_para_num) */
2344: INTO bim_r_fdsp_weekly_facts fwf(
2345: spend_transaction_id
2346: ,creation_date
2340: fnd_message.set_token('table_name', l_table_name, FALSE);
2341: fnd_file.put_line(fnd_file.log,fnd_message.get);
2342: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
2343: INSERT /*+ append parallel(fwf,p_para_num) */
2344: INTO bim_r_fdsp_weekly_facts fwf(
2345: spend_transaction_id
2346: ,creation_date
2347: ,last_update_date
2348: ,created_by
2366: ,fis_qtr
2367: ,fis_year
2368: )
2369: SELECT /*+ parallel(inner.p_para_num) */
2370: bim_r_fdsp_weekly_facts_s.nextval
2371: ,sysdate
2372: ,sysdate
2373: ,l_user_id
2374: ,l_user_id
2410: ,object_type
2411: ,fund_id
2412: ,util_org_id) inner;
2413: EXECUTE IMMEDIATE 'commit';
2414: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2415: EXCEPTION
2416: when others THEN
2417: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
2418: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
2414: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2415: EXCEPTION
2416: when others THEN
2417: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
2418: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
2419: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2420: x_return_status := FND_API.G_RET_STS_ERROR;
2421: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2422: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2415: EXCEPTION
2416: when others THEN
2417: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
2418: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
2419: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
2420: x_return_status := FND_API.G_RET_STS_ERROR;
2421: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2422: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2423: FND_MSG_PUB.Add;
2471:
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;
2490: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,
2491: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2492: END;
2493: BEGIN
2494: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_WEEKLY_FACTS', estimate_percent => 5,
2495: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2496: END;
2497: fnd_message.set_name('BIM','BIM_R_CALL_PROC');
2498: fnd_message.set_token('proc_name', 'UPDATE_BALANCE', FALSE);
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';
2577:
2578: /* First insert: Insert transactions happened between p_start_date and p_end_date. */
2579: INSERT /*+ append parallel(fdf,p_para_num) */
4242: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4243: FND_MSG_PUB.Add;
4244: END;
4245: BEGIN
4246: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_weekly_facts';
4247: l_table_name :='bim_r_fdsp_weekly_facts';
4248: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4249: fnd_message.set_token('table_name', l_table_name, FALSE);
4250: fnd_file.put_line(fnd_file.log,fnd_message.get);
4243: FND_MSG_PUB.Add;
4244: END;
4245: BEGIN
4246: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_weekly_facts';
4247: l_table_name :='bim_r_fdsp_weekly_facts';
4248: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4249: fnd_message.set_token('table_name', l_table_name, FALSE);
4250: fnd_file.put_line(fnd_file.log,fnd_message.get);
4251: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
4247: l_table_name :='bim_r_fdsp_weekly_facts';
4248: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
4249: fnd_message.set_token('table_name', l_table_name, FALSE);
4250: fnd_file.put_line(fnd_file.log,fnd_message.get);
4251: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
4252: --dbms_output.put_line('inside fist inserting into fdsp weekly');
4253:
4254: --Insert into bim_r_fdsp_weekly_facts
4255: INSERT /*+ append parallel(fwf,p_para_num) */
4250: fnd_file.put_line(fnd_file.log,fnd_message.get);
4251: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 1000';
4252: --dbms_output.put_line('inside fist inserting into fdsp weekly');
4253:
4254: --Insert into bim_r_fdsp_weekly_facts
4255: INSERT /*+ append parallel(fwf,p_para_num) */
4256: INTO bim_r_fdsp_weekly_facts fwf(
4257: spend_transaction_id
4258: ,creation_date
4252: --dbms_output.put_line('inside fist inserting into fdsp weekly');
4253:
4254: --Insert into bim_r_fdsp_weekly_facts
4255: INSERT /*+ append parallel(fwf,p_para_num) */
4256: INTO bim_r_fdsp_weekly_facts fwf(
4257: spend_transaction_id
4258: ,creation_date
4259: ,last_update_date
4260: ,created_by
4278: ,fis_qtr
4279: ,fis_year
4280: )
4281: SELECT /*+ parallel(inner.p_para_num) */
4282: bim_r_fdsp_weekly_facts_s.nextval
4283: ,sysdate
4284: ,sysdate
4285: ,l_user_id
4286: ,l_user_id
4322: ,object_type
4323: ,fund_id
4324: ,util_org_id) inner;
4325: EXECUTE IMMEDIATE 'commit';
4326: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4327: EXCEPTION
4328: when others THEN
4329: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
4330: --dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
4326: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4327: EXCEPTION
4328: when others THEN
4329: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
4330: --dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
4331: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4332: x_return_status := FND_API.G_RET_STS_ERROR;
4333: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4334: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4327: EXCEPTION
4328: when others THEN
4329: FND_FILE.put_line(fnd_file.log,'error insert fdsp_weekly'||sqlerrm(sqlcode));
4330: --dbms_output.put_line('error inserting INTO bim_r_fdsp_weekly_facts'||sqlerrm(sqlcode));
4331: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_weekly_facts_s CACHE 20';
4332: x_return_status := FND_API.G_RET_STS_ERROR;
4333: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
4334: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
4335: FND_MSG_PUB.Add;
4336: 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;
4357: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,
4358: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4359: END;
4360: BEGIN
4361: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_WEEKLY_FACTS', estimate_percent => 5,
4362: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4363: END;
4364:
4365: --call update subsequent balance