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_I_BGT_FACTS_PKG dependencies on BIM_I_BUDGET_FACTS

Line 164: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts load starts at:'||sysdate);

160: x_return_status := FND_API.G_RET_STS_SUCCESS;
161:
162: -- Debug Message
163: -- AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the populate procedure');
164: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts load starts at:'||sysdate);
165:
166: l_global_start_date :=BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE();
167:
168: /* THIS CODE REPLACES THE GET_LAST_REFRESH_PERIOD TO GET_LAST_REFRESH_DATES */

Line 193: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: First Load');

189: RAISE FND_API.G_EXC_ERROR;
190: END IF;
191:
192: l_load_type := 'FIRST_LOAD';
193: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: First Load');
194:
195: FIRST_LOAD(p_start_date => greatest(l_global_start_date,p_start_date)
196: ,p_end_date => sysdate
197: ,p_api_version_number => l_api_version_number

Line 236: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Incremental Load');

232: RAISE FND_API.G_EXC_ERROR;
233: END IF;
234:
235: l_load_type := 'SUBSEQUENT_LOAD';
236: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Incremental Load');
237: /*SUB_LOAD(p_start_date => l_end_date+1/86400
238: ,p_end_date => sysdate
239: ,p_api_version_number => l_api_version_number
240: ,p_init_msg_list => FND_API.G_FALSE

Line 271: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts Concurrent Program Succesfully Completed');

267: COMMIT WORK;
268: END IF;
269:
270: COMMIT;
271: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Facts Concurrent Program Succesfully Completed');
272:
273: -- Standard call to get message count and if count is 1, get message info.
274: FND_MSG_PUB.Count_And_Get
275: (p_count => x_msg_count,

Line 382: --dbms_output.put_line('BIM_I_BUDGET_FACTS: Running the First Load '||sqlerrm(sqlcode));

378:
379: -- Initialize API return status to SUCCESS
380: x_return_status := FND_API.G_RET_STS_SUCCESS;
381:
382: --dbms_output.put_line('BIM_I_BUDGET_FACTS: Running the First Load '||sqlerrm(sqlcode));
383:
384: -- The below four commands are necessary for the purpose of the parallel insertion */
385: BEGIN
386: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';

Line 389: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';

385: BEGIN
386: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
387: --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
388: --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
389: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
390: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
391:
392:
393: /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */

Line 390: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';

386: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
387: --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
388: --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
389: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
390: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
391:
392:
393: /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
394: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');

Line 394: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');

390: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
391:
392:
393: /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
394: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');
395: BIM_UTL_PKG.drop_index('BIM_I_BUDGET_FACTS');
396: /* End of Code for dropping the existing indexes */
397: EXCEPTION when others then
398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));

Line 395: BIM_UTL_PKG.drop_index('BIM_I_BUDGET_FACTS');

391:
392:
393: /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
394: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');
395: BIM_UTL_PKG.drop_index('BIM_I_BUDGET_FACTS');
396: /* End of Code for dropping the existing indexes */
397: EXCEPTION when others then
398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));
399: --dbms_output.put_line('error first:'||sqlerrm(sqlcode));

Line 398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));

394: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Drop index before inserting.');
395: BIM_UTL_PKG.drop_index('BIM_I_BUDGET_FACTS');
396: /* End of Code for dropping the existing indexes */
397: EXCEPTION when others then
398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));
399: --dbms_output.put_line('error first:'||sqlerrm(sqlcode));
400: END;
401: l_table_name :='BIM_I_BUDGET_FACTS';
402: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';

Line 401: l_table_name :='BIM_I_BUDGET_FACTS';

397: EXCEPTION when others then
398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));
399: --dbms_output.put_line('error first:'||sqlerrm(sqlcode));
400: END;
401: l_table_name :='BIM_I_BUDGET_FACTS';
402: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
403: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
404: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
405: -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');

Line 402: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';

398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: error:'||sqlerrm(sqlcode));
399: --dbms_output.put_line('error first:'||sqlerrm(sqlcode));
400: END;
401: l_table_name :='BIM_I_BUDGET_FACTS';
402: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
403: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
404: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
405: -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
406: INSERT /*+ append parallel */

Line 404: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');

400: END;
401: l_table_name :='BIM_I_BUDGET_FACTS';
402: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
403: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
404: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
405: -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
406: INSERT /*+ append parallel */
407: INTO BIM_I_BUDGET_FACTS_STG CDF(
408: creation_date

Line 407: INTO BIM_I_BUDGET_FACTS_STG CDF(

403: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
404: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:First insert into table BIM_I_BUDGET_FACTS_STG');
405: -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
406: INSERT /*+ append parallel */
407: INTO BIM_I_BUDGET_FACTS_STG CDF(
408: creation_date
409: ,last_update_date
410: ,created_by
411: ,last_updated_by

Line 1398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');

1394: metric_type,
1395: conversion_rate_s
1396: )inner;
1397: commit;
1398: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
1399: --insert into bim_i_mkt_rates
1400: INSERT /*+ append parallel */
1401: INTO BIM_I_BGT_RATES BRT(tc_code,
1402: trx_date,

Line 1411: from bim_i_budget_facts_stg);

1407: FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
1408: FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
1409: FROM (select distinct from_currency from_currency,
1410: transaction_create_date transaction_create_date
1411: from bim_i_budget_facts_stg);
1412: commit;
1413: l_check_missing_rate := Check_Missing_Rates (p_start_date);
1414: if (l_check_missing_rate = -1) then
1415: BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );

Line 1416: l_stmt := 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_stg';

1412: commit;
1413: l_check_missing_rate := Check_Missing_Rates (p_start_date);
1414: if (l_check_missing_rate = -1) then
1415: BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
1416: l_stmt := 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_stg';
1417: EXECUTE IMMEDIATE l_stmt;
1418: commit;
1419: x_return_status := FND_API.G_RET_STS_ERROR;
1420: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;

Line 1423: --BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserted '||SQL%COUNT);

1419: x_return_status := FND_API.G_RET_STS_ERROR;
1420: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421: end if;
1422:
1423: --BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserted '||SQL%COUNT);
1424: EXECUTE IMMEDIATE 'COMMIT';
1425: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 20';
1426:
1427: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';

Line 1425: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 20';

1421: end if;
1422:
1423: --BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserted '||SQL%COUNT);
1424: EXECUTE IMMEDIATE 'COMMIT';
1425: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 20';
1426:
1427: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';
1428: BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
1429:

Line 1427: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';

1423: --BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserted '||SQL%COUNT);
1424: EXECUTE IMMEDIATE 'COMMIT';
1425: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 20';
1426:
1427: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';
1428: BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
1429:
1430: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');
1431: INSERT /*+ append parallel */

Line 1430: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');

1426:
1427: EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.BIM_I_BUDGET_FACTS';
1428: BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
1429:
1430: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');
1431: INSERT /*+ append parallel */
1432: INTO BIM_I_BUDGET_FACTS CDF(
1433: creation_date
1434: ,last_update_date

Line 1432: INTO BIM_I_BUDGET_FACTS CDF(

1428: BIS_COLLECTION_UTILITIES.deleteLogForObject('BUDGET_FACTS');
1429:
1430: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Insert into BIM_I_BUDGET_FACTS');
1431: INSERT /*+ append parallel */
1432: INTO BIM_I_BUDGET_FACTS CDF(
1433: creation_date
1434: ,last_update_date
1435: ,created_by
1436: ,last_updated_by

Line 1524: FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt

1520: inner.committed*sec_conversion_rate,
1521: inner.utilized*sec_conversion_rate,
1522: inner.accrual*sec_conversion_rate,
1523: inner.paid*sec_conversion_rate
1524: FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
1525: where inner.from_currency = rt.tc_code
1526: and inner.transaction_create_date= rt.trx_date;
1527: commit;
1528: --dbms_output.put_line('b4 put into history');

Line 1530: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,

1526: and inner.transaction_create_date= rt.trx_date;
1527: commit;
1528: --dbms_output.put_line('b4 put into history');
1529: -- Analyze the daily facts table
1530: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
1531: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1532:
1533: -- Make entry in the history table
1534: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Wrapup');

Line 1534: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Wrapup');

1530: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
1531: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1532:
1533: -- Make entry in the history table
1534: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS: Wrapup');
1535: BEGIN
1536: IF (Not BIS_COLLECTION_UTILITIES.setup('BUDGET_FACTS')) THEN
1537: RAISE FND_API.G_EXC_ERROR;
1538: return;

Line 1556: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before create index');

1552: );
1553: RAISE FND_API.G_EXC_ERROR;
1554: END;
1555:
1556: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before create index');
1557:
1558: BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');
1559: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1560: --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);

Line 1558: BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');

1554: END;
1555:
1556: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before create index');
1557:
1558: BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');
1559: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1560: --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
1561: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After create index');
1562: /*fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');

Line 1560: --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);

1556: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before create index');
1557:
1558: BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');
1559: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1560: --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
1561: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After create index');
1562: /*fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
1563: fnd_message.set_token('program_name', 'Budget first load', FALSE);
1564: fnd_file.put_line(fnd_file.log,fnd_message.get);*/

Line 1561: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After create index');

1557:
1558: BIM_UTL_PKG.CREATE_INDEX('BIM_I_BUDGET_FACTS');
1559: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1560: --ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
1561: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After create index');
1562: /*fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
1563: fnd_message.set_token('program_name', 'Budget first load', FALSE);
1564: fnd_file.put_line(fnd_file.log,fnd_message.get);*/
1565: EXCEPTION

Line 1575: ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));

1571: p_count => x_msg_count,
1572: p_data => x_msg_data
1573: );
1574:
1575: ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1576: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1577: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578: -- Standard call to get message count and if count=1, get the message
1579: FND_msg_PUB.Count_And_Get (

Line 1584: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Unexpected'||sqlerrm(sqlcode));

1580: --p_encoded => FND_API.G_FALSE,
1581: p_count => x_msg_count,
1582: p_data => x_msg_data
1583: );
1584: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Unexpected'||sqlerrm(sqlcode));
1585:
1586: WHEN OTHERS THEN
1587: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588: IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)

Line 1599: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:IN OTHERS EXCEPTION'||sqlerrm(sqlcode));

1595: -- p_encoded => FND_API.G_FALSE,
1596: p_count => x_msg_count,
1597: p_data => x_msg_data
1598: );
1599: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:IN OTHERS EXCEPTION'||sqlerrm(sqlcode));
1600: --end;
1601: END FIRST_LOAD;
1602: --------------------------------------------------------------------------------------------------
1603: -- This procedure will execute when data is loaded for subsequent time.

Line 1670: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';

1666: BEGIN
1667: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
1668: --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
1669: --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
1670: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
1671: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
1672: EXCEPTION
1673: when others then
1674: --dbms_output.put_line('inside sub load:'||sqlerrm(sqlcode));

Line 1671: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';

1667: EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
1668: --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
1669: --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
1670: --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_BUDGET_FACTS nologging ';
1671: -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_BUDGET_FACTS_s CACHE 1000 ';
1672: EXCEPTION
1673: when others then
1674: --dbms_output.put_line('inside sub load:'||sqlerrm(sqlcode));
1675: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');

Line 1678: DELETE from bim_i_budget_facts where transaction_create_date>= p_start_date and metric_type is not null;

1674: --dbms_output.put_line('inside sub load:'||sqlerrm(sqlcode));
1675: l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1676: END;
1677: BEGIN
1678: DELETE from bim_i_budget_facts where transaction_create_date>= p_start_date and metric_type is not null;
1679: COMMIT;
1680: EXCEPTION
1681: when others then
1682: BIS_COLLECTION_UTILITIES.log('BIM_I_budget_facts: Error in deleting data:'|| sqlerrm(sqlcode));

Line 1682: BIS_COLLECTION_UTILITIES.log('BIM_I_budget_facts: Error in deleting data:'|| sqlerrm(sqlcode));

1678: DELETE from bim_i_budget_facts where transaction_create_date>= p_start_date and metric_type is not null;
1679: COMMIT;
1680: EXCEPTION
1681: when others then
1682: BIS_COLLECTION_UTILITIES.log('BIM_I_budget_facts: Error in deleting data:'|| sqlerrm(sqlcode));
1683: --dbms_output.put_line('error inserting:'||sqlerrm(sqlcode));
1684: END;
1685: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
1686: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';

Line 1685: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';

1681: when others then
1682: BIS_COLLECTION_UTILITIES.log('BIM_I_budget_facts: Error in deleting data:'|| sqlerrm(sqlcode));
1683: --dbms_output.put_line('error inserting:'||sqlerrm(sqlcode));
1684: END;
1685: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
1686: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
1687: --dbms_output.put_line('right b4 inserting');
1688: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental load start');
1689: INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(

Line 1688: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental load start');

1684: END;
1685: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
1686: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
1687: --dbms_output.put_line('right b4 inserting');
1688: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental load start');
1689: INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(
1690: creation_date
1691: ,last_update_date
1692: ,created_by

Line 1689: INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(

1685: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BUDGET_FACTS_STG';
1686: EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_BGT_RATES';
1687: --dbms_output.put_line('right b4 inserting');
1688: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental load start');
1689: INSERT INTO BIM_I_BUDGET_FACTS_STG CDF(
1690: creation_date
1691: ,last_update_date
1692: ,created_by
1693: ,last_updated_by

Line 1867: AND not exists (select 1 from bim_i_budget_facts a

1863: or ( ad.start_date_active between p_start_date and p_end_date
1864: AND ad.status_date 1865: AND ad.parent_fund_id is null
1866: AND ad.status_code in ('ACTIVE','CLOSED','CANCELLED')
1867: AND not exists (select 1 from bim_i_budget_facts a
1868: where a.fund_id = ad.fund_id
1869: and a.metric_type= 'ORIGINAL_BUDGET')
1870: UNION ALL --transfer_in
1871: SELECT ad.fund_id fund_id,

Line 2674: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');

2670: metric_type,
2671: conversion_rate_s
2672: )inner;
2673:
2674: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Inserting into BIM_I_BGT_RATES');
2675: --insert into bim_i_mkt_rates
2676: INSERT
2677: INTO BIM_I_BGT_RATES BRT(tc_code,
2678: trx_date,

Line 2687: from bim_i_budget_facts_stg);

2683: FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
2684: FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
2685: FROM (select distinct from_currency from_currency,
2686: transaction_create_date transaction_create_date
2687: from bim_i_budget_facts_stg);
2688:
2689: l_check_missing_rate := Check_Missing_Rates (p_start_date);
2690: if (l_check_missing_rate = -1) then
2691: DELETE from BIM_I_BUDGET_FACTS_stg where transaction_create_date>= p_start_date;

Line 2691: DELETE from BIM_I_BUDGET_FACTS_stg where transaction_create_date>= p_start_date;

2687: from bim_i_budget_facts_stg);
2688:
2689: l_check_missing_rate := Check_Missing_Rates (p_start_date);
2690: if (l_check_missing_rate = -1) then
2691: DELETE from BIM_I_BUDGET_FACTS_stg where transaction_create_date>= p_start_date;
2692: commit;
2693: x_return_status := FND_API.G_RET_STS_ERROR;
2694: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2695: end if;

Line 2696: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental Load:after calling checking_missing_rates');

2692: commit;
2693: x_return_status := FND_API.G_RET_STS_ERROR;
2694: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2695: end if;
2696: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental Load:after calling checking_missing_rates');
2697:
2698: --insert into facts table
2699: INSERT /*+ append parallel */
2700: INTO BIM_I_BUDGET_FACTS CDF(

Line 2700: INTO BIM_I_BUDGET_FACTS CDF(

2696: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Incremental Load:after calling checking_missing_rates');
2697:
2698: --insert into facts table
2699: INSERT /*+ append parallel */
2700: INTO BIM_I_BUDGET_FACTS CDF(
2701: creation_date
2702: ,last_update_date
2703: ,created_by
2704: ,last_updated_by

Line 2792: FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt

2788: inner.committed*sec_conversion_rate,
2789: inner.utilized*sec_conversion_rate,
2790: inner.accrual*sec_conversion_rate,
2791: inner.paid*sec_conversion_rate
2792: FROM bim_i_budget_facts_stg inner, bim_i_bgt_rates rt
2793: where inner.from_currency = rt.tc_code
2794: and inner.transaction_create_date= rt.trx_date;
2795:
2796: -- Analyze the daily facts table

Line 2797: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,

2793: where inner.from_currency = rt.tc_code
2794: and inner.transaction_create_date= rt.trx_date;
2795:
2796: -- Analyze the daily facts table
2797: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
2798: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2799:
2800: --dbms_output.put_line('b4 inserting log');
2801: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before Insert into log.');

Line 2801: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before Insert into log.');

2797: DBMS_STATS.gather_table_stats('BIM','BIM_I_BUDGET_FACTS', estimate_percent => 5,
2798: degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2799:
2800: --dbms_output.put_line('b4 inserting log');
2801: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:Before Insert into log.');
2802: BEGIN
2803: IF (Not BIS_COLLECTION_UTILITIES.setup('BUDGET_FACTS')) THEN
2804: RAISE FND_API.G_EXC_ERROR;
2805: return;

Line 2821: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After Insert into log.');

2817: p_period_to =>sysdate
2818: );
2819: RAISE FND_API.G_EXC_ERROR;
2820: END;
2821: BIS_COLLECTION_UTILITIES.log('BIM_I_BUDGET_FACTS:After Insert into log.');
2822: EXCEPTION
2823: WHEN FND_API.G_EXC_ERROR THEN
2824: x_return_status := FND_API.G_RET_STS_ERROR;
2825: -- Standard call to get message count and if count=1, get the message

Line 2844: ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));

2840: p_count => x_msg_count,
2841: p_data => x_msg_data
2842: );
2843:
2844: ams_utility_pvt.write_conc_log('BIM_I_BUDGET_FACTS:SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
2845: BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
2846: WHEN OTHERS THEN
2847:
2848: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;