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.BIL_BI_PIPELINE_F_PKG dependencies on BIL_BI_PIPEC_F

Line 645: FROM BIL_BI_PIPEC_F f

641: f.OPEN_AMT_WEEK_S,
642: f.OPEN_AMT_PERIOD_S,
643: f.OPEN_AMT_Quarter_S,
644: f.OPEN_AMT_YEAR_S
645: FROM BIL_BI_PIPEC_F f
646: WHERE f.snap_date < p_curr_coll_start
647: and f.snap_date IN (
648: SELECT report_date from fii_time_day
649: where report_date=week_end_date

Line 663: DELETE FROM BIL_BI_PIPEC_F

659: );
660:
661: commit;
662:
663: DELETE FROM BIL_BI_PIPEC_F
664: where snap_date < p_curr_coll_start;
665: commit;
666:
667: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN

Line 1361: BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPEC_F');

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');
1365: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_DENLOG_STG');

Line 1364: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');

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');
1368: ELSE

Line 1538: FROM bil_bi_pipec_f;

1534: -- Added by TR (to populate the correct period_to and period_from dates in Attribute
1535: -- columns of bis_refresh_log)
1536: SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
1537: INTO l_period_from
1538: FROM bil_bi_pipec_f;
1539:
1540: SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
1541: INTO l_period_to
1542: FROM bil_bi_pipec_f;

Line 1542: FROM bil_bi_pipec_f;

1538: FROM bil_bi_pipec_f;
1539:
1540: SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
1541: INTO l_period_to
1542: FROM bil_bi_pipec_f;
1543:
1544:
1545:
1546: IF l_curr_coll_start between l_failure_from+1 and l_failure_to THEN

Line 1605: 'BIL_BI_PIPEC_F',

1601: VALUES
1602: (
1603: g_request_id,
1604: g_program_id,
1605: 'BIL_BI_PIPEC_F',
1606: 'SUCCESS',
1607: g_program_start,
1608: l_curr_coll_start,
1609: l_failure_to,

Line 1645: 'BIL_BI_PIPEC_F',

1641: VALUES
1642: (
1643: g_request_id,
1644: g_program_id,
1645: 'BIL_BI_PIPEC_F',
1646: 'SUCCESS',
1647: g_program_start,
1648: l_failure_from,
1649: l_failure_to,

Line 1681: trunc(fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_PIPEC_F')));

1677:
1678: -- resume during gap fill is handled by reading l_last_run after resume
1679:
1680: l_last_run :=
1681: trunc(fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_PIPEC_F')));
1682: l_last_fact_run :=
1683: trunc(fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_OPDTL_F')));
1684:
1685: l_date := l_last_fact_run; -- fact could be run on previous day or the same day

Line 1774: bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_PIPEC_F');

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
1778: Build_Baseline(l_start_date);

Line 1832: FROM bil_bi_pipec_f;

1828:
1829: -- get the first day in the current pipeline table to see if we need to delete
1830: SELECT MIN(snap_date)
1831: into l_min_curr_date
1832: FROM bil_bi_pipec_f;
1833:
1834: IF l_min_curr_date < l_curr_coll_start THEN
1835: DELETE_FROM_CURR(l_curr_coll_start);
1836: END IF;

Line 1872: DELETE FROM bil_bi_pipec_f WHERE snap_date between l_last_run+1 and l_date-1;

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;
1873: commit;
1874:
1875: IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1876: bil_bi_util_collection_pkg.writeLog

Line 1880: p_msg => 'Deleted '|| sql%rowcount ||' from BIL_BI_PIPEC_F table for gap between' || (l_last_run+1) ||

1876: bil_bi_util_collection_pkg.writeLog
1877: (
1878: p_log_level => fnd_log.LEVEL_EVENT,
1879: p_module => g_pkg || l_proc ,
1880: p_msg => 'Deleted '|| sql%rowcount ||' from BIL_BI_PIPEC_F table for gap between' || (l_last_run+1) ||
1881: ' and ' || (l_date-1));
1882: END IF;
1883:
1884:

Line 2053: FROM bil_bi_pipec_f;

2049: -- Added by TR (to populate the correct period_to and period_from dates in Attribute
2050: -- columns of bis_refresh_log)
2051: SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
2052: INTO l_period_from
2053: FROM bil_bi_pipec_f;
2054:
2055: SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
2056: INTO l_period_to
2057: FROM bil_bi_pipec_f;

Line 2057: FROM bil_bi_pipec_f;

2053: FROM bil_bi_pipec_f;
2054:
2055: SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
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:

Line 2060: --Insert into bis_refresh_log for both tables: bil_bi_pipeline_f and bil_bi_pipec_f

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: (

Line 2123: 'BIL_BI_PIPEC_F',

2119: VALUES
2120: (
2121: g_request_id,
2122: g_program_id,
2123: 'BIL_BI_PIPEC_F',
2124: 'SUCCESS',
2125: g_program_start,
2126: l_curr_coll_start,
2127: l_date-1,

Line 2168: 'BIL_BI_PIPEC_F',

2164: VALUES
2165: (
2166: g_request_id,
2167: g_program_id,
2168: 'BIL_BI_PIPEC_F',
2169: 'SUCCESS',
2170: g_program_start,
2171: l_last_run+1,
2172: l_date-1,

Line 2203: DELETE FROM bil_bi_pipec_f WHERE snap_date = l_date;

2199: -- regular snapshot
2200:
2201: -- delete the existing snapshot, since could be multiple runs anyway in the same day
2202:
2203: DELETE FROM bil_bi_pipec_f WHERE snap_date = l_date;
2204: commit;
2205:
2206:
2207: -- get the first day in the current pipeline table to see if we need to delete

Line 2210: FROM bil_bi_pipec_f;

2206:
2207: -- get the first day in the current pipeline table to see if we need to delete
2208: SELECT MIN(snap_date)
2209: into l_min_curr_date
2210: FROM bil_bi_pipec_f;
2211:
2212: --delete from current pipeline table if data is older than 3 weeks
2213: IF l_min_curr_date < l_curr_coll_start THEN
2214: DELETE_FROM_CURR(l_curr_coll_start);

Line 2233: p_msg => 'Deleted '|| sql%rowcount ||' rows from BIL_BI_PIPEC_F for '||l_date

2229: bil_bi_util_collection_pkg.writeLog
2230: (
2231: p_log_level => fnd_log.LEVEL_EVENT,
2232: p_module => g_pkg || l_proc ,
2233: p_msg => 'Deleted '|| sql%rowcount ||' rows from BIL_BI_PIPEC_F for '||l_date
2234: );
2235: END IF;
2236:
2237:

Line 2291: INSERT /+ append parallel(f) /INTO bil_bi_pipec_f f

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
2289: -- this can be achieved by using the bil_bi_time table:
2290:
2291: INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
2292: (
2293: sales_group_id,
2294: salesrep_id,
2295: created_by ,

Line 2684: FROM BIL_BI_PIPEC_F f

2680: null prvyr_open_amt_wk_s ,
2681: null prvyr_open_amt_PRD_s ,
2682: null prvyr_open_amt_qtr_s ,
2683: null prvyr_open_amt_yr_s
2684: FROM BIL_BI_PIPEC_F f
2685: where snap_date = l_sd_lwk
2686: )
2687: GROUP BY
2688: sales_group_id,

Line 2709: p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_PIPEC_F table from BIL_BI_OPDTL_F for date'

2705: bil_bi_util_collection_pkg.writeLog
2706: (
2707: p_log_level => fnd_log.LEVEL_EVENT,
2708: p_module => g_pkg || l_proc ,
2709: p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_PIPEC_F table from BIL_BI_OPDTL_F for date'
2710: || l_date
2711: );
2712: END IF;
2713:

Line 2723: FROM bil_bi_pipec_f;

2719: -- l_date );
2720:
2721: SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
2722: INTO l_period_from
2723: FROM bil_bi_pipec_f;
2724:
2725:
2726: SELECT
2727: TO_CHAR(MAX(snap_date),'DD-MM-YYYY')

Line 2730: FROM bil_bi_pipec_f;

2726: SELECT
2727: TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
2728: INTO
2729: l_period_to
2730: FROM bil_bi_pipec_f;
2731:
2732:
2733: INSERT INTO bis_refresh_log
2734: (

Line 2756: 'BIL_BI_PIPEC_F',

2752: VALUES
2753: (
2754: g_request_id,
2755: g_program_id,
2756: 'BIL_BI_PIPEC_F',
2757: 'SUCCESS',
2758: g_program_start,
2759: l_date,
2760: l_date,

Line 3614: INSERT /+ append parallel(f) /INTO bil_bi_pipec_f f

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
3612: -- this can be achieved by using the bil_bi_time table:
3613:
3614: INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
3615: (
3616: sales_group_id,
3617: salesrep_id,
3618: created_by ,

Line 4028: FROM BIL_BI_PIPEC_F f

4024: null prvyr_open_amt_wk_s ,
4025: null prvyr_open_amt_PRD_s ,
4026: null prvyr_open_amt_qtr_s ,
4027: null prvyr_open_amt_yr_s
4028: FROM BIL_BI_PIPEC_F f
4029: where snap_date = l_sd_lwk
4030: )
4031: GROUP BY
4032: sales_group_id,

Line 5797: --insert into bil_bi_pipec_f

5793:
5794:
5795:
5796: ELSE
5797: --insert into bil_bi_pipec_f
5798:
5799: Insert_into_curr_sumry(l_cur_date, l_week, l_period
5800: , l_qtr, l_year, l_min_date_id, l_max_date_id);
5801:

Line 6103: --insert into bil_bi_pipec_f

6099:
6100:
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 ...

Line 6430: INSERT /+ append parallel(f) /INTO bil_bi_pipec_f f

6426: -- this can be achieved by using the bil_bi_time table:
6427:
6428:
6429:
6430: INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
6431: (
6432: sales_group_id,
6433: salesrep_id,
6434: created_by ,

Line 6846: FROM BIL_BI_PIPEC_F f

6842: null prvyr_open_amt_wk_s ,
6843: null prvyr_open_amt_PRD_s ,
6844: null prvyr_open_amt_qtr_s ,
6845: null prvyr_open_amt_yr_s
6846: FROM BIL_BI_PIPEC_F f
6847: where snap_date = l_sd_lwk
6848: )
6849: GROUP BY
6850: sales_group_id,