[Home] [Help]
1: PACKAGE BODY BIL_BI_PIPELINE_F_PKG AS
2: /*$Header: bilbpipb.pls 120.32.12010000.3 2008/12/02 05:44:53 annsrini ship $*/
3:
4: G_global_start_date DATE;
5: G_request_id NUMBER;
566:
567: -- by joining to fii_time_day, we ensure that only end of period records
568: -- are inserted into hist pipe fact
569:
570: INSERT into bil_bi_pipeline_f f(
571: SALES_GROUP_ID,
572: SALESREP_ID,
573: CREATED_BY ,
574: CREATION_DATE,
1080: G_program_id := 0;
1081: G_user_id := 0;
1082: G_login_id := 0;
1083: g_row_num := 0;
1084: g_pkg := 'bil.patch.115.sql.bil_bi_pipeline_f_pkg.';
1085:
1086: G_request_id := 0;
1087: G_appl_id := 0;
1088: G_program_id := 0;
1088: G_program_id := 0;
1089: G_user_id := 0;
1090: G_login_id := 0;
1091: g_row_num := 0;
1092: g_pkg := 'bil.patch.115.sql.bil_bi_pipeline_f_pkg.';
1093:
1094: G_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1095: G_appl_id := FND_GLOBAL.PROG_APPL_ID();
1096: G_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
1106: l_int_date_format1:='MM/DD/YYYY HH24:MI:SS';
1107: l_resume_flag:= 'N';
1108: l_sysdate := sysdate;
1109:
1110: l_valid_setup := BIS_COLLECTION_UTILITIES.SETUP('BIL_BI_PIPELINE_F');
1111:
1112:
1113:
1114:
1356: p_module => g_pkg || l_proc ,
1357: p_msg => 'Truncating'
1358: );
1359: END IF;
1360: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPELINE_F');
1361: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPEC_F');
1362: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
1363: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_F');
1364: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');
1359: END IF;
1360: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPELINE_F');
1361: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPEC_F');
1362: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_STG');
1363: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_F');
1364: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');
1365: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');
1366: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_TIME');
1367: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_DENLOG_TMP');
1381: );
1382: END IF;
1383:
1384: IF (l_count > 0) THEN
1385: get_last_failure_periods('BIL_BI_PIPELINE_F', l_failure_from, l_failure_to);
1386: l_number_of_runs := get_number_of_runs('BIL_BI_PIPELINE_F');
1387:
1388: IF(l_number_of_runs <=1 ) then
1389: l_mode := 'INIT';
1382: END IF;
1383:
1384: IF (l_count > 0) THEN
1385: get_last_failure_periods('BIL_BI_PIPELINE_F', l_failure_from, l_failure_to);
1386: l_number_of_runs := get_number_of_runs('BIL_BI_PIPELINE_F');
1387:
1388: IF(l_number_of_runs <=1 ) then
1389: l_mode := 'INIT';
1390: ELSE
1565: VALUES
1566: (
1567: g_request_id,
1568: g_program_id,
1569: 'BIL_BI_PIPELINE_F',
1570: 'SUCCESS',
1571: g_program_start,
1572: l_failure_from,
1573: l_curr_coll_start-1,
1769:
1770: -- truncate table here since there is a possibibility the build base line was
1771: -- successful on the 1st run and the rest failed.
1772:
1773: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPELINE_F');
1774: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');
1775:
1776:
1777: -- setup baseline on the global start date
1808: Insert_Into_Summary('INIT');
1809:
1810: -- incrementally build up the rest of the days
1811: Gap_Fill(l_start_date+1, l_date-1, l_curr_coll_start, 'INIT', l_small_gap) ;
1812: bil_bi_util_collection_pkg.analyze_table('BIL_BI_PIPELINE_F',TRUE, 10, 'GLOBAL');
1813: Else
1814: RAISE G_INVALID_DIM;
1815: END IF; -- errcheck ok
1816:
1856: END IF;
1857:
1858: -- gap exists
1859:
1860: DELETE FROM bil_bi_pipeline_f WHERE snap_date between l_last_run+1 and l_date-1;
1861: commit;
1862:
1863: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1864: bil_bi_util_collection_pkg.writeLog
1864: bil_bi_util_collection_pkg.writeLog
1865: (
1866: p_log_level => fnd_log.LEVEL_EVENT,
1867: p_module => g_pkg || l_proc ,
1868: p_msg => 'Deleted '|| sql%rowcount ||' from BIL_BI_PIPELINE_F table for gap between' || (l_last_run+1) ||
1869: ' and ' || (l_date-1));
1870: END IF;
1871:
1872: DELETE FROM bil_bi_pipec_f WHERE snap_date between l_last_run+1 and l_date-1;
2056: INTO l_period_to
2057: FROM bil_bi_pipec_f;
2058:
2059: IF l_curr_coll_start BETWEEN l_last_run+2 and l_date-1 THEN
2060: --Insert into bis_refresh_log for both tables: bil_bi_pipeline_f and bil_bi_pipec_f
2061:
2062:
2063: INSERT INTO bis_refresh_log
2064: (
2080: VALUES
2081: (
2082: g_request_id,
2083: g_program_id,
2084: 'BIL_BI_PIPELINE_F',
2085: 'SUCCESS',
2086: g_program_start,
2087: l_last_run+1,
2088: l_curr_coll_start-1,
2280: NULL;
2281: END;
2282:
2283:
2284: --also insert prior amounts from bil_bi_pipeline_f
2285: --since we may not have data for the same day last week/quarter/period/year in the hist. table
2286: --we need to get the end of the week/quarter ...
2287: --if the same day last year is closer to the end of a certain week/quarter/period
2288: --than to the end of te year, get the closest date
2617: decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
2618: decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
2619: decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
2620: decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
2621: FROM BIL_BI_PIPELINE_F f
2622: where snap_date in (l_sd_lwk_end, l_sd_lper_end
2623: ,l_sd_lqtr_end, l_sd_lyr_end)
2624: UNION ALL
2625: SELECT sales_group_id,
3603: NULL;
3604: END;
3605:
3606:
3607: --also insert prior amounts from bil_bi_pipeline_f
3608: --since we may not have data for the same day last week/quarter/period/year in the hist. table
3609: --we need to get the end of the week/quarter ...
3610: --if the same day last year is closer to the end of a certain week/quarter/period
3611: --than to the end of te year, get the closest date
3961: decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
3962: decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
3963: decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
3964: decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
3965: FROM BIL_BI_PIPELINE_F f
3966: where snap_date in (l_sd_lwk_end, l_sd_lper_end
3967: ,l_sd_lqtr_end, l_sd_lyr_end)
3968: UNION ALL
3969: SELECT sales_group_id,
4080:
4081:
4082:
4083: IF(p_mode = 'INIT') THEN
4084: INSERT /*+ append parallel(f) */ into bil_bi_pipeline_f f(
4085: SALES_GROUP_ID,
4086: SALESREP_ID,
4087: CREATED_BY ,
4088: CREATION_DATE,
4245: THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL, stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
4246: is not null ;
4247:
4248: ELSE
4249: INSERT into bil_bi_pipeline_f f(
4250: SALES_GROUP_ID,
4251: SALESREP_ID,
4252: CREATED_BY ,
4253: CREATION_DATE,
4418: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
4419: bil_bi_util_collection_pkg.writeLog(
4420: p_log_level => fnd_log.LEVEL_EVENT,
4421: p_module => g_pkg || l_proc ,
4422: p_msg => 'Inserted '|| g_row_num||' into BIL_BI_PIPELINE_F table from BIL_BI_PIPELINE_STG ');
4423: END IF;
4424:
4425:
4426:
5611: IF l_pipe_tbl_type = 'HIST' THEN
5612: -- parallel hints are disabled since they actually offer less performance in testing
5613: -- for this query
5614:
5615: INSERT /*+ append */INTO bil_bi_pipeline_f f
5616: (
5617: sales_group_id,
5618: salesrep_id,
5619: created_by ,
5916: END IF; --gap smaller than 3 days
5917:
5918: IF l_pipe_tbl_type = 'HIST' THEN
5919:
5920: INSERT INTO bil_bi_pipeline_f f
5921:
5922: (
5923: sales_group_id,
5924: salesrep_id,
6101:
6102: ELSE
6103: --insert into bil_bi_pipec_f
6104:
6105: --also insert prior amounts from bil_bi_pipeline_f
6106: --since we may not have data for the same day last week/quarter/period/year in the hist. table
6107: --we need to get the end of the week/quarter ...
6108: --if the same day last year is closer to the end of a certain week/quarter/period
6109: --than to the end of te year, get the closest date
6417:
6418: END;
6419:
6420:
6421: --also insert prior amounts from bil_bi_pipeline_f
6422: --since we may not have data for the same day last week/quarter/period/year in the hist. table
6423: --we need to get the end of the week/quarter ...
6424: --if the same day last year is closer to the end of a certain week/quarter/period
6425: --than to the end of te year, get the closest date
6779: decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
6780: decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
6781: decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
6782: decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
6783: FROM BIL_BI_PIPELINE_F f
6784: where snap_date in (l_sd_lwk_end, l_sd_lper_end
6785: ,l_sd_lqtr_end, l_sd_lyr_end)
6786: UNION ALL
6787: SELECT sales_group_id,
6884:
6885: END Ins_Into_CurrSum_SmGap;
6886:
6887:
6888: END BIL_BI_PIPELINE_F_PKG;