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 FII_TIME_DAY

Line 447: FII_TIME_DAY time,

443: to_number(to_char(GREATEST(time.ent_year_end_date,time2.week_end_date), 'J'))
444: INTO
445: x_day, x_week, x_period, x_qtr, x_year, x_min_date_id, x_max_date_id
446: FROM
447: FII_TIME_DAY time,
448: FII_TIME_DAY time1,
449: FII_TIME_DAY time2
450: WHERE
451: time.report_date = TRUNC(p_date)

Line 448: FII_TIME_DAY time1,

444: INTO
445: x_day, x_week, x_period, x_qtr, x_year, x_min_date_id, x_max_date_id
446: FROM
447: FII_TIME_DAY time,
448: FII_TIME_DAY time1,
449: FII_TIME_DAY time2
450: WHERE
451: time.report_date = TRUNC(p_date)
452: AND time1.report_date = time.ent_year_start_date

Line 449: FII_TIME_DAY time2

445: x_day, x_week, x_period, x_qtr, x_year, x_min_date_id, x_max_date_id
446: FROM
447: FII_TIME_DAY time,
448: FII_TIME_DAY time1,
449: FII_TIME_DAY time2
450: WHERE
451: time.report_date = TRUNC(p_date)
452: AND time1.report_date = time.ent_year_start_date
453: AND time2.report_date = time.ent_year_end_date ;

Line 506: from fii_time_day

502:
503:
504: SELECT week_end_date, ent_period_end_date, ent_qtr_end_date, ent_year_end_date
505: INTO l_week_end, l_period_end , l_quarter_end, l_year_end
506: from fii_time_day
507: where report_date = p_date;
508:
509: IF p_date = l_week_end OR p_date = l_period_end OR p_date = l_quarter_end
510: OR p_date = l_year_end THEN

Line 567: -- by joining to fii_time_day, we ensure that only end of period records

563:
564:
565:
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,

Line 648: SELECT report_date from fii_time_day

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
650: UNION
651: SELECT report_date from fii_time_day
652: where report_date = ent_period_end_date

Line 651: SELECT report_date from fii_time_day

647: and f.snap_date IN (
648: SELECT report_date from fii_time_day
649: where report_date=week_end_date
650: UNION
651: SELECT report_date from fii_time_day
652: where report_date = ent_period_end_date
653: UNION
654: SELECT report_date from fii_time_day
655: where report_date = ent_qtr_end_date

Line 654: SELECT report_date from fii_time_day

650: UNION
651: SELECT report_date from fii_time_day
652: where report_date = ent_period_end_date
653: UNION
654: SELECT report_date from fii_time_day
655: where report_date = ent_qtr_end_date
656: UNION
657: SELECT report_date from fii_time_day
658: where report_date =ent_year_end_date

Line 657: SELECT report_date from fii_time_day

653: UNION
654: SELECT report_date from fii_time_day
655: where report_date = ent_qtr_end_date
656: UNION
657: SELECT report_date from fii_time_day
658: where report_date =ent_year_end_date
659: );
660:
661: commit;

Line 719: from fii_time_day time1

715: ,CASE WHEN week_end_date > ent_period_end_date then ent_period_end_date
716: WHEN week_end_date > ent_qtr_end_date then ent_qtr_end_date
717: WHEN week_end_date > ent_year_end_date then ent_year_end_date
718: ELSE week_end_date END end_date
719: from fii_time_day time1
720: where (time1.report_date between p_start_date and p_end_date
721: and time1.report_date=time1.week_start_date)
722: OR
723: (time1.report_date between p_start_date and p_end_date

Line 2252: from fii_time_day

2248: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lwk, IN l_date;
2249:
2250: SELECT week_end_date
2251: INTO l_sd_lwk_end
2252: from fii_time_day
2253: where report_date=l_sd_lwk;
2254:
2255: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pper_end(:2); END;';
2256: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN l_date;

Line 2260: from fii_time_day

2256: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN l_date;
2257:
2258: SELECT LEAST(week_end_date, ent_period_end_date)
2259: INTO l_sd_lper_end
2260: from fii_time_day
2261: where report_date=l_sd_lper;
2262:
2263: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pqtr_end(:2); END;';
2264: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN l_date;

Line 2268: from fii_time_day

2264: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN l_date;
2265:
2266: SELECT LEAST(week_end_date, ent_qtr_end_date)
2267: INTO l_sd_lqtr_end
2268: from fii_time_day
2269: where report_date=l_sd_lqtr;
2270:
2271: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_lyr_end(:2); END;';
2272: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN l_date;

Line 2276: from fii_time_day

2272: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN l_date;
2273:
2274: SELECT LEAST(week_end_date, ent_year_end_date)
2275: INTO l_sd_lyr_end
2276: from fii_time_day
2277: where report_date=l_sd_lyr;
2278:
2279: EXCEPTION WHEN NO_DATA_FOUND THEN
2280: NULL;

Line 2541: fii_time_day time

2537: null prvyr_open_amt_yr_s
2538:
2539: FROM
2540: bil_bi_opdtl_f fact,
2541: fii_time_day time
2542: WHERE
2543: fact.OPTY_CLOSE_TIME_ID = time.report_date_julian
2544: and forecast_rollup_flag = 'Y'
2545: and fact.OPTY_CLOSE_TIME_ID between l_min_date_id and l_max_date_id

Line 3575: from fii_time_day

3571: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lwk, IN p_date;
3572:
3573: SELECT week_end_date
3574: INTO l_sd_lwk_end
3575: from fii_time_day
3576: where report_date=l_sd_lwk;
3577:
3578: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pper_end(:2); END;';
3579: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN p_date;

Line 3583: from fii_time_day

3579: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN p_date;
3580:
3581: SELECT LEAST(week_end_date, ent_period_end_date)
3582: INTO l_sd_lper_end
3583: from fii_time_day
3584: where report_date=l_sd_lper;
3585:
3586: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pqtr_end(:2); END;';
3587: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN p_date;

Line 3591: from fii_time_day

3587: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN p_date;
3588:
3589: SELECT LEAST(week_end_date, ent_qtr_end_date)
3590: INTO l_sd_lqtr_end
3591: from fii_time_day
3592: where report_date=l_sd_lqtr;
3593:
3594: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_lyr_end(:2); END;';
3595: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN p_date;

Line 3599: from fii_time_day

3595: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN p_date;
3596:
3597: SELECT LEAST(week_end_date, ent_year_end_date)
3598: INTO l_sd_lyr_end
3599: from fii_time_day
3600: where report_date=l_sd_lyr;
3601:
3602: EXCEPTION WHEN NO_DATA_FOUND THEN
3603: NULL;

Line 3883: fii_time_day time

3879: null prvyr_open_amt_yr_s
3880:
3881: FROM
3882: bil_bi_pipeline_stg stg,
3883: fii_time_day time
3884: WHERE stg.effective_date = TIME.report_date
3885: AND forecast_rollup_flag = 'Y'
3886: AND TIME.report_date_julian >= p_min_date_id AND TIME.report_date_julian+0 <= p_max_date_id
3887: GROUP BY

Line 4221: fii_time_day time,

4217: THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
4218: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4219: FROM
4220: bil_bi_pipeline_stg stg,
4221: fii_time_day time,
4222: fii_time_day time1
4223: WHERE
4224: stg.snap_date = time1.report_date
4225: and stg.PRIM_CONVERSION_RATE > 0

Line 4222: fii_time_day time1

4218: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4219: FROM
4220: bil_bi_pipeline_stg stg,
4221: fii_time_day time,
4222: fii_time_day time1
4223: WHERE
4224: stg.snap_date = time1.report_date
4225: and stg.PRIM_CONVERSION_RATE > 0
4226: and stg.conversion_rate_s>0

Line 4386: fii_time_day time,

4382: THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
4383: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4384: FROM
4385: bil_bi_pipeline_stg stg,
4386: fii_time_day time,
4387: fii_time_day time1
4388: WHERE
4389: stg.snap_date = time1.report_date
4390: and stg.PRIM_CONVERSION_RATE > 0

Line 4387: fii_time_day time1

4383: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
4384: FROM
4385: bil_bi_pipeline_stg stg,
4386: fii_time_day time,
4387: fii_time_day time1
4388: WHERE
4389: stg.snap_date = time1.report_date
4390: and stg.PRIM_CONVERSION_RATE > 0
4391: and stg.conversion_rate_s>0

Line 4488: from fii_time_day

4484:
4485:
4486: select greatest(week_end_date, ent_year_end_date)
4487: into l_max_decision_end
4488: from fii_time_day
4489: where end_date = p_start_date;
4490: --- base line query , note can't just produce baseline for x and x year, but for x on forward
4491:
4492: SELECT report_date,

Line 4497: fii_time_day

4493: LEAST(ent_year_start_date, week_start_date) start_date,
4494: GREATEST(ent_year_end_date,week_end_date) end_date
4495: into l_report_date, l_report_start, l_report_end
4496: FROM
4497: fii_time_day
4498: WHERE report_date = trunc(p_start_date);
4499:
4500:
4501:

Line 4586: -- fii_time_day

4582: -- SELECT report_date,
4583: -- LEAST(ent_year_start_date, week_start_date) start_date,
4584: -- GREATEST(ent_year_end_date,week_end_date) end_date
4585: -- FROM
4586: -- fii_time_day
4587: -- WHERE report_date = trunc(p_start_date)
4588: --) gapdays
4589: WHERE leadlog1.last_update_date < l_report_date+1
4590: GROUP BY lead_id, --gapdays.report_date, gapdays.start_date, gapdays.end_date

Line 4706: from fii_time_day

4702:
4703: BEGIN
4704: select least(week_start_date, ent_year_start_date)
4705: into l_min_decision_start
4706: from fii_time_day
4707: where start_date = p_start_date;
4708:
4709: select greatest(week_end_date, ent_year_end_date)
4710: into l_max_decision_end

Line 4711: from fii_time_day

4707: where start_date = p_start_date;
4708:
4709: select greatest(week_end_date, ent_year_end_date)
4710: into l_max_decision_end
4711: from fii_time_day
4712: where end_date = p_end_date;
4713:
4714: EXCEPTION
4715: WHEN OTHERS THEN

Line 5428: FROM fii_time_day day

5424:
5425: CURSOR c1(p_curr_coll_start DATE, p_start_date DATE, p_end_date DATE) IS
5426: select report_date, tbl FROM
5427: (SELECT report_date, 'CURR' tbl
5428: FROM fii_time_day day
5429: WHERE report_date BETWEEN decode(greatest(p_start_date, p_curr_coll_start), p_start_date,
5430: p_start_date, p_curr_coll_start)
5431: AND p_end_date
5432: UNION ALL

Line 5480: FII_TIME_DAY time,

5476: to_number(to_char(GREATEST(time.ent_year_end_date,time2.week_end_date), 'J'))
5477: INTO
5478: l_day, l_week, l_period, l_qtr, l_year, l_min_date_id, l_max_date_id
5479: FROM
5480: FII_TIME_DAY time,
5481: FII_TIME_DAY time1,
5482: FII_TIME_DAY time2
5483:
5484: WHERE

Line 5481: FII_TIME_DAY time1,

5477: INTO
5478: l_day, l_week, l_period, l_qtr, l_year, l_min_date_id, l_max_date_id
5479: FROM
5480: FII_TIME_DAY time,
5481: FII_TIME_DAY time1,
5482: FII_TIME_DAY time2
5483:
5484: WHERE
5485: time.report_date = l_cur_date

Line 5482: FII_TIME_DAY time2

5478: l_day, l_week, l_period, l_qtr, l_year, l_min_date_id, l_max_date_id
5479: FROM
5480: FII_TIME_DAY time,
5481: FII_TIME_DAY time1,
5482: FII_TIME_DAY time2
5483:
5484: WHERE
5485: time.report_date = l_cur_date
5486: AND time1.report_date = time.ent_year_start_date

Line 5769: fii_time_day time

5765: THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
5766: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
5767: FROM
5768: bil_bi_pipeline_stg stg,
5769: fii_time_day time
5770:
5771: WHERE stg.effective_date = TIME.report_date
5772: AND forecast_rollup_flag = 'Y'
5773: AND TIME.report_date_julian BETWEEN l_min_date_id AND l_max_date_id

Line 6075: fii_time_day time

6071: THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
6072: stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
6073: FROM
6074: bil_bi_pipeline_stg stg,
6075: fii_time_day time
6076: WHERE stg.effective_date = TIME.report_date
6077:
6078: AND forecast_rollup_flag = 'Y'
6079: AND TIME.report_date_julian BETWEEN l_min_date_id AND l_max_date_id

Line 6257: , fii_time_day day

6253: GREATEST(year.end_date,week.end_date) end_date
6254: FROM
6255: fii_time_ent_year year
6256: , fii_time_week week
6257: , fii_time_day day
6258: WHERE report_date between p_start_date and p_end_date
6259: AND day.week_id = week.week_id
6260: AND day.ent_year_id = year.ent_year_id
6261: ) gapdays

Line 6381: from fii_time_day

6377: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lwk, IN p_date;
6378:
6379: SELECT week_end_date
6380: INTO l_sd_lwk_end
6381: from fii_time_day
6382: where report_date=l_sd_lwk;
6383:
6384: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pper_end(:2); END;';
6385: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN p_date;

Line 6389: from fii_time_day

6385: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lper, IN p_date;
6386:
6387: SELECT LEAST(week_end_date, ent_period_end_date)
6388: INTO l_sd_lper_end
6389: from fii_time_day
6390: where report_date=l_sd_lper;
6391:
6392: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_pqtr_end(:2); END;';
6393: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN p_date;

Line 6397: from fii_time_day

6393: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lqtr, IN p_date;
6394:
6395: SELECT LEAST(week_end_date, ent_qtr_end_date)
6396: INTO l_sd_lqtr_end
6397: from fii_time_day
6398: where report_date=l_sd_lqtr;
6399:
6400: l_dynamic_sql := 'BEGIN :1 := fii_time_api.ent_sd_lyr_end(:2); END;';
6401: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN p_date;

Line 6405: from fii_time_day

6401: EXECUTE IMMEDIATE l_dynamic_sql USING OUT l_sd_lyr, IN p_date;
6402:
6403: SELECT LEAST(week_end_date, ent_year_end_date)
6404: INTO l_sd_lyr_end
6405: from fii_time_day
6406: where report_date=l_sd_lyr;
6407:
6408: EXCEPTION WHEN NO_DATA_FOUND THEN
6409: NULL;

Line 6700: fii_time_day time

6696: null prvyr_open_amt_yr_s
6697:
6698: FROM
6699: bil_bi_pipeline_stg stg,
6700: fii_time_day time
6701: WHERE stg.effective_date = TIME.report_date
6702: AND forecast_rollup_flag = 'Y'
6703: AND TIME.report_date_julian>= p_min_date_id AND TIME.report_date_julian+0<=p_max_date_id
6704: AND stg.snap_date = p_date