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_FDSP_DAILY_FACTS

Line 80: -- and bim_r_fdsp_daily_facts. Calculates balance based on data from

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
84: IS

Line 123: FROM bim_r_fdsp_daily_facts fdsp,

119: SUM(nvl(fdsp.commited_amt,0)) commited_amt,
120: SUM(nvl(fdsp.standard_discount,0)+nvl(fdsp.market_expense,0)+nvl(fdsp.accrual,0)) utilized_amt,
121: SUM(nvl(fdsp.planned_amt,0)) planned,
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

Line 146: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';

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
150: ams_utility_pvt.write_conc_log('error alter tablebalance:'||sqlerrm(sqlcode));

Line 194: 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts

190: END;
191:
192: BEGIN
193: EXECUTE IMMEDIATE
194: 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts
195: (fund_id,
196: transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
197: EXCEPTION
198: WHEN OTHERS THEN

Line 273: from bim_r_fdsp_daily_facts

269: SUM(nvl(planned_amt,0)) planned,
270: SUM(nvl(paid_amt,0)) paid,
271: SUM(nvl(standard_discount,0)+nvl(market_expense,0)+nvl(accrual,0)) utilized_amt,
272: transaction_create_date week_date
273: from bim_r_fdsp_daily_facts
274: WHERE fund_id = x.fund_id
275: AND transaction_create_date = x.week_date
276: group by fund_id, transaction_create_date) fdsp
277: where a.fund_id =x.fund_id

Line 351: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts noparallel';

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
355: FND_FILE.put_line(fnd_file.log,'error alter table in balance'||sqlerrm(sqlcode));

Line 389: 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts

385: END;
386:
387: BEGIN
388: EXECUTE IMMEDIATE
389: 'create index bim_r_fdsp_facts_n6 on bim_r_fdsp_daily_facts
390: (fund_id,
391: transaction_create_date) tablespace '||l_index_tablespace||' compute statistics';
392: EXCEPTION
393: WHEN OTHERS THEN

Line 452: FROM bim_r_fdsp_daily_facts

448: nvl(market_expense,0)) utilized,
449: SUM(nvl(planned_amt,0)) planned,
450: SUM(nvl(paid_amt,0)) paid,
451: transaction_create_date week_date
452: FROM bim_r_fdsp_daily_facts
453: WHERE fund_id = x.fund_id
454: AND transaction_create_date between p_start_date and p_end_date+0.99999
455: GROUP BY fund_id, transaction_create_date) fdsp
456: WHERE a.fund_id =x.fund_id

Line 508: FROM bim_r_fdsp_daily_facts

504: nvl(market_expense,0)) utilized,
505: SUM(nvl(planned_amt,0)) planned,
506: SUM(nvl(paid_amt,0)) paid,
507: transaction_create_date week_date
508: FROM bim_r_fdsp_daily_facts
509: WHERE fund_id = x.fund_id
510: AND transaction_create_date = x.transaction_create_date
511: GROUP BY fund_id, transaction_create_date) fdsp
512: WHERE a.fund_id =x.fund_id

Line 557: from bim_r_fdsp_daily_facts

553: nvl(market_expense,0)) utilized,
554: SUM(nvl(planned_amt,0)) planned,
555: SUM(nvl(paid_amt,0)) paid,
556: transaction_create_date week_date
557: from bim_r_fdsp_daily_facts
558: where fund_id = x.fund_id
559: and transaction_create_date = x.transaction_create_date
560: group by fund_id, transaction_create_date) fdsp
561: WHERE a.fund_id =x.fund_id

Line 976: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';

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';
979:
980: l_table_name :='bim_r_fund_daily_facts';

Line 1540: /* Start inserting into 'bim_r_fdsp_daily_facts' */

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;
1539: END;
1540: /* Start inserting into 'bim_r_fdsp_daily_facts' */
1541: l_table_name :='bim_r_fdsp_daily_facts';
1542: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1543: fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
1544: fnd_file.put_line(fnd_file.log,fnd_message.get);

Line 1541: l_table_name :='bim_r_fdsp_daily_facts';

1537: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1538: FND_MSG_PUB.Add;
1539: END;
1540: /* Start inserting into 'bim_r_fdsp_daily_facts' */
1541: l_table_name :='bim_r_fdsp_daily_facts';
1542: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1543: fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
1544: fnd_file.put_line(fnd_file.log,fnd_message.get);
1545: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';

Line 1545: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';

1541: l_table_name :='bim_r_fdsp_daily_facts';
1542: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1543: fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
1544: fnd_file.put_line(fnd_file.log,fnd_message.get);
1545: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';
1546: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_load';
1547: /* bim_r_fdsp_load is a intermediate table which holds commited amount, planned amount etc for all objects */
1548: BEGIN
1549: INSERT /*+ append parallel(bfl,p_para_num) */

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

1675: EXECUTE IMMEDIATE 'COMMIT';
1676: EXCEPTION
1677: WHEN OTHERS THEN
1678: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1679: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1680: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1681: x_return_status := FND_API.G_RET_STS_ERROR;
1682: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1683: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 1680: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

1676: EXCEPTION
1677: WHEN OTHERS THEN
1678: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1679: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1680: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1681: x_return_status := FND_API.G_RET_STS_ERROR;
1682: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1683: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1684: FND_MSG_PUB.Add;

Line 1691: /* First insert: insert into bim_r_fdsp_daily_facts for the funds whose active

1687: -- dbms_output.put_line('inside fist inserting into fdsp daily');
1688: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1689: fnd_message.set_token('table_name', l_table_name, FALSE);
1690: fnd_file.put_line(fnd_file.log,fnd_message.get);
1691: /* First insert: insert into bim_r_fdsp_daily_facts for the funds whose active
1692: date between p_start_date and p_end_date. Fund transactions are: commited, planned, utilized
1693: etc. The measures are on object level. Object are like campaigns, events, offers, etc. */
1694: BEGIN
1695: INSERT /*+ append parallel(fdf,p_para_num) */

Line 1696: INTO bim_r_fdsp_daily_facts fdf(

1692: date between p_start_date and p_end_date. Fund transactions are: commited, planned, utilized
1693: etc. The measures are on object level. Object are like campaigns, events, offers, etc. */
1694: BEGIN
1695: INSERT /*+ append parallel(fdf,p_para_num) */
1696: INTO bim_r_fdsp_daily_facts fdf(
1697: spend_transaction_id
1698: ,creation_date
1699: ,last_update_date
1700: ,created_by

Line 1722: bim_r_fdsp_daily_facts_s.nextval,

1718: ,fis_qtr
1719: ,fis_year
1720: )
1721: SELECT /*+ parallel(inner, p_para_num) */
1722: bim_r_fdsp_daily_facts_s.nextval,
1723: sysdate,
1724: sysdate,
1725: l_user_id,
1726: l_user_id,

Line 1879: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

1875: AND AU.transaction_create_date (+) BETWEEN TRUNC(AD.trdate) AND TRUNC(AD.trdate) + 0.99999
1876: ) inner;
1877: l_count :=l_count +SQL%ROWCOUNT;
1878: EXECUTE IMMEDIATE 'COMMIT';
1879: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1880: EXCEPTION
1881: WHEN OTHERS THEN
1882: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1883: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));

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

1879: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1880: EXCEPTION
1881: WHEN OTHERS THEN
1882: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1883: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1884: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1887: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 1884: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

1880: EXCEPTION
1881: WHEN OTHERS THEN
1882: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
1883: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
1884: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
1885: x_return_status := FND_API.G_RET_STS_ERROR;
1886: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
1887: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
1888: FND_MSG_PUB.Add;

Line 1896: INSERT INTO bim_r_fdsp_daily_facts fdf(

1892: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
1893: fnd_message.set_token('table_name', l_table_name, FALSE);
1894: fnd_file.put_line(fnd_file.log,fnd_message.get);
1895: BEGIN
1896: INSERT INTO bim_r_fdsp_daily_facts fdf(
1897: spend_transaction_id
1898: ,creation_date
1899: ,last_update_date
1900: ,created_by

Line 1922: bim_r_fdsp_daily_facts_s.nextval,

1918: ,fis_qtr
1919: ,fis_year
1920: )
1921: SELECT
1922: bim_r_fdsp_daily_facts_s.nextval,
1923: sysdate,
1924: sysdate,
1925: l_user_id,
1926: l_user_id,

Line 2077: --dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));

2073: EXECUTE IMMEDIATE 'commit';
2074: EXCEPTION
2075: WHEN others THEN
2076: FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
2077: --dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
2078: x_return_status := FND_API.G_RET_STS_ERROR;
2079: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
2080: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
2081: 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 2190: FROM bim_r_fdsp_daily_facts fdsp

2186: fis_month fis_month,
2187: fis_qtr fis_qtr,
2188: fis_year fis_year,
2189: transaction_create_date transaction_create_date
2190: FROM bim_r_fdsp_daily_facts fdsp
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

Line 2406: FROM bim_r_fdsp_daily_facts

2402: ,SUM(commited_amt) commited_amt
2403: ,SUM(planned_amt) planned_amt
2404: ,SUM(paid_amt) paid_amt
2405: ,load_date load_date
2406: FROM bim_r_fdsp_daily_facts
2407: GROUP BY load_date
2408: ,business_unit_id
2409: ,object_id
2410: ,object_type

Line 2490: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,

2486: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,
2487: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2488: END;
2489: BEGIN
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,

Line 2574: EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_fdsp_daily_facts nologging';

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';
2577:
2578: /* First insert: Insert transactions happened between p_start_date and p_end_date. */

Line 3293: l_table_name :='bim_r_fdsp_daily_facts';

3289: FND_MSG_PUB.Add;
3290: END;
3291:
3292: /* Inserting into 'bim_r_fdsp_load' all the objects information */
3293: l_table_name :='bim_r_fdsp_daily_facts';
3294: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3295: fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
3296: fnd_file.put_line(fnd_file.log,fnd_message.get);
3297: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';

Line 3297: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';

3293: l_table_name :='bim_r_fdsp_daily_facts';
3294: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3295: fnd_message.set_token('table_name', 'bim_r_fdsp_load', FALSE);
3296: fnd_file.put_line(fnd_file.log,fnd_message.get);
3297: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 1000';
3298: EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_fdsp_load';
3299: BEGIN
3300: INSERT /*+ append parallel(bfl,p_para_num) */
3301: INTO bim_r_fdsp_load bfl(

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

3425: EXECUTE IMMEDIATE 'COMMIT';
3426: EXCEPTION
3427: WHEN OTHERS THEN
3428: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
3429: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3430: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3431: x_return_status := FND_API.G_RET_STS_ERROR;
3432: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3433: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 3430: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

3426: EXCEPTION
3427: WHEN OTHERS THEN
3428: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily'||sqlerrm(sqlcode));
3429: --dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3430: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3431: x_return_status := FND_API.G_RET_STS_ERROR;
3432: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3433: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3434: FND_MSG_PUB.Add;

Line 3438: /* First insert:Insert into 'bim_r_fdsp_daily_facts' the transactions happend between

3434: FND_MSG_PUB.Add;
3435: END ; --end of insertion into bim_r_fdsp_load.
3436:
3437: --dbms_output.put_line('inside fist inserting into fdsp daily');
3438: /* First insert:Insert into 'bim_r_fdsp_daily_facts' the transactions happend between
3439: p_start_date and p_end_date */
3440: fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
3441: fnd_message.set_token('table_name', l_table_name, FALSE);
3442: fnd_file.put_line(fnd_file.log,fnd_message.get);

Line 3445: INTO bim_r_fdsp_daily_facts fdf(

3441: fnd_message.set_token('table_name', l_table_name, FALSE);
3442: fnd_file.put_line(fnd_file.log,fnd_message.get);
3443: BEGIN
3444: INSERT /*+ append parallel(fdf,p_para_num) */
3445: INTO bim_r_fdsp_daily_facts fdf(
3446: spend_transaction_id
3447: ,creation_date
3448: ,last_update_date
3449: ,created_by

Line 3471: bim_r_fdsp_daily_facts_s.nextval,

3467: ,fis_qtr
3468: ,fis_year
3469: )
3470: SELECT /*+ parallel(inner, p_para_num) */
3471: bim_r_fdsp_daily_facts_s.nextval,
3472: sysdate,
3473: sysdate,
3474: l_user_id,
3475: l_user_id,

Line 3622: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

3618: AND AU.fund_id = AD.fund_id
3619: ) inner;
3620: l_count :=l_count +SQL%ROWCOUNT;
3621: EXECUTE IMMEDIATE 'COMMIT';
3622: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3623: EXCEPTION
3624: WHEN OTHERS THEN
3625: FND_FILE.put_line(fnd_file.log,'first insert:error insert fdsp daily'||sqlerrm(sqlcode));
3626: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));

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

3622: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3623: EXCEPTION
3624: WHEN OTHERS THEN
3625: FND_FILE.put_line(fnd_file.log,'first insert:error insert fdsp daily'||sqlerrm(sqlcode));
3626: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3627: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3628: x_return_status := FND_API.G_RET_STS_ERROR;
3629: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3630: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);

Line 3627: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';

3623: EXCEPTION
3624: WHEN OTHERS THEN
3625: FND_FILE.put_line(fnd_file.log,'first insert:error insert fdsp daily'||sqlerrm(sqlcode));
3626: -- dbms_output.put_line('error inserting INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3627: EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_fdsp_daily_facts_s CACHE 20';
3628: x_return_status := FND_API.G_RET_STS_ERROR;
3629: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3630: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3631: FND_MSG_PUB.Add;

Line 3640: INTO bim_r_fdsp_daily_facts fdf(

3636: fnd_message.set_token('table_name', l_table_name, FALSE);
3637: fnd_file.put_line(fnd_file.log,fnd_message.get);
3638: BEGIN
3639: INSERT /*+ append parallel(fdf,p_para_num) */
3640: INTO bim_r_fdsp_daily_facts fdf(
3641: spend_transaction_id
3642: ,creation_date
3643: ,last_update_date
3644: ,created_by

Line 3666: bim_r_fdsp_daily_facts_s.nextval,

3662: ,fis_qtr
3663: ,fis_year
3664: )
3665: SELECT /*+ parallel(inner, p_para_num) */
3666: bim_r_fdsp_daily_facts_s.nextval,
3667: sysdate,
3668: sysdate,
3669: l_user_id,
3670: l_user_id,

Line 3821: -- dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));

3817: EXECUTE IMMEDIATE 'commit';
3818: EXCEPTION
3819: WHEN others THEN
3820: FND_FILE.put_line(fnd_file.log,'error insert extras into fdsp daily'||sqlerrm(sqlcode));
3821: -- dbms_output.put_line('error inserting extra INTO bim_r_fdsp_daily_facts'||sqlerrm(sqlcode));
3822: x_return_status := FND_API.G_RET_STS_ERROR;
3823: FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
3824: FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
3825: FND_MSG_PUB.Add;

Line 3835: INTO bim_r_fdsp_daily_facts fdf(

3831: fnd_message.set_token('table_name', l_table_name, FALSE);
3832: fnd_file.put_line(fnd_file.log,fnd_message.get);
3833: BEGIN
3834: INSERT /*+ append parallel(fdf,p_para_num) */
3835: INTO bim_r_fdsp_daily_facts fdf(
3836: spend_transaction_id
3837: ,creation_date
3838: ,last_update_date
3839: ,created_by

Line 3861: bim_r_fdsp_daily_facts_s.nextval,

3857: ,fis_qtr
3858: ,fis_year
3859: )
3860: SELECT /*+ parallel(inner, p_para_num) */
3861: bim_r_fdsp_daily_facts_s.nextval,
3862: sysdate,
3863: sysdate,
3864: l_user_id,
3865: l_user_id,

Line 3897: FROM bim_r_fdsp_daily_facts a,

3893: ,BIM_SET_OF_BOOKS.get_fiscal_year(inner.trdate,204)
3894: FROM (
3895: SELECT distinct a.fund_id fund_id,
3896: TRUNC(b.trdate) trdate
3897: FROM bim_r_fdsp_daily_facts a,
3898: ozf_funds_all_b f,
3899: bim_intl_dates b
3900: WHERE b.trdate between greatest(p_start_datel, f.start_date_active)
3901: and least(p_end_datel, nvl(f.end_date_active,p_end_datel))

Line 3904: from bim_r_fdsp_daily_facts c

3900: WHERE b.trdate between greatest(p_start_datel, f.start_date_active)
3901: and least(p_end_datel, nvl(f.end_date_active,p_end_datel))
3902: and f.fund_id = a.fund_id
3903: and (a.fund_id, TRUNC(b.trdate)) not in (select c.fund_id, c.transaction_create_date
3904: from bim_r_fdsp_daily_facts c
3905: where c.fund_id = a.fund_id
3906: and c.transaction_create_date = TRUNC(b.trdate)) )inner;
3907: l_count :=l_count+SQL%ROWCOUNT;
3908: -- dbms_output.put_line('missing fdsp:l_count'||l_count);

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 3921: but not in bim_r_fdsp_daily_facts. */

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);
3925: BEGIN --insert into fdsp for balancing

Line 3926: l_table_name :='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);
3925: BEGIN --insert into fdsp for balancing
3926: l_table_name :='bim_r_fdsp_daily_facts';
3927: INSERT /*+ append parallel(fdf,p_para_num) */
3928: INTO bim_r_fdsp_daily_facts fdf(
3929: spend_transaction_id
3930: ,creation_date

Line 3928: INTO bim_r_fdsp_daily_facts fdf(

3924: fnd_file.put_line(fnd_file.log,fnd_message.get);
3925: BEGIN --insert into fdsp for balancing
3926: l_table_name :='bim_r_fdsp_daily_facts';
3927: INSERT /*+ append parallel(fdf,p_para_num) */
3928: INTO bim_r_fdsp_daily_facts fdf(
3929: spend_transaction_id
3930: ,creation_date
3931: ,last_update_date
3932: ,created_by

Line 3954: bim_r_fdsp_daily_facts_s.nextval,

3950: ,fis_qtr
3951: ,fis_year
3952: )
3953: SELECT /*+ parallel(inner, p_para_num) */
3954: bim_r_fdsp_daily_facts_s.nextval,
3955: sysdate,
3956: sysdate,
3957: l_user_id,
3958: l_user_id,

Line 3977: FROM bim_r_fdsp_daily_facts b1,

3973: b1.object_type,
3974: b1.fis_month,
3975: b1.fis_qtr,
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

Line 3983: from bim_r_fdsp_daily_facts

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
3985: and transaction_create_date is not null
3986: )) b2
3987: WHERE b1.fund_id = b2.fund_id

Line 3993: -- dbms_output.put_line('error inserting bim_r_fdsp_daily_facts for balancing'||sqlerrm(sqlcode));

3989: EXECUTE IMMEDIATE 'commit';
3990: EXCEPTION
3991: WHEN OTHERS THEN
3992: FND_FILE.put_line(fnd_file.log,'error insert fdsp daily for balancing'||sqlerrm(sqlcode));
3993: -- dbms_output.put_line('error inserting bim_r_fdsp_daily_facts for balancing'||sqlerrm(sqlcode));
3994: x_return_status := FND_API.G_RET_STS_ERROR;
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;

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 4088: FROM bim_r_fdsp_daily_facts fdsp

4084: fis_month fis_month,
4085: fis_qtr fis_qtr,
4086: fis_year fis_year,
4087: transaction_create_date transaction_create_date
4088: FROM bim_r_fdsp_daily_facts fdsp
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

Line 4318: FROM bim_r_fdsp_daily_facts

4314: ,SUM(commited_amt) commited_amt
4315: ,SUM(planned_amt) planned_amt
4316: ,SUM(paid_amt) paid_amt
4317: ,load_date load_date
4318: FROM bim_r_fdsp_daily_facts
4319: GROUP BY load_date
4320: ,business_unit_id
4321: ,object_id
4322: ,object_type

Line 4357: DBMS_STATS.gather_table_stats('BIM','BIM_R_FDSP_DAILY_FACTS', estimate_percent => 5,

4353: DBMS_STATS.gather_table_stats('BIM','BIM_R_FUND_WEEKLY_FACTS', estimate_percent => 5,
4354: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4355: END;
4356: BEGIN
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,