[Home] [Help]
136: 32 as period_type,
137: 1 as amount_type,
138: p_calendar_type as calendar_type
139: FROM pji_time_mv
140: WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
141: AND calendar_id = p_calendar_id
142: AND sequence between g_curr_period_seq-g_backward_periods
143: and g_curr_period_seq+g_forward_periods;
144:
149: SET pmv.PRIOR_ID =
150: ( SELECT period_id as prior_id
151: FROM pji_time_mv lower
152: WHERE
153: lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
154: AND lower.calendar_id = p_calendar_id
155: AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
156: and g_prev_yr_period_seq+g_forward_periods
157: AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
186: 1 as sequence
187: FROM pji_time_mv
188: WHERE period_id = g_curr_period_id
189: and calendar_id = p_calendar_id
190: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
191: UNION ALL
192: select period_id as id,
193: null as prior_id,
194: quarter_name as name,
223: l_prev_year_id
224: FROM pji_time_mv
225: WHERE sequence = g_prev_yr_period_seq
226: and calendar_id = p_calendar_id
227: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
228:
229: UPDATE PJI_PMV_TIME_DIM_TMP pmv
230: SET pmv.PRIOR_ID = l_prev_period_id
231: where order_by_id = 1;
280:
281: delete from pji_pmv_time_dim_tmp;
282:
283: IF (p_calendar_type <> 'E') THEN
284: l_period_type := 'FII_TIME_CAL_PERIOD';
285: ELSE
286: l_period_type := 'FII_TIME_ENT_PERIOD';
287: END IF;
288:
312: 32 as period_type,
313: DECODE(sign(sequence-g_last_summ_pd_seq),1,0,-1,1,0,1) as amount_type,
314: p_calendar_type as calendar_type
315: FROM pji_time_mv
316: WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
317: AND calendar_id = p_calendar_id
318: AND sequence between g_curr_period_seq-g_backward_periods
319: and g_curr_period_seq+g_forward_periods;
320:
325: SET pmv.PRIOR_ID =
326: ( SELECT period_id as prior_id
327: FROM pji_time_mv lower
328: WHERE
329: lower.period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
330: AND lower.calendar_id = p_calendar_id
331: AND lower.sequence between g_prev_yr_period_seq-g_backward_periods
332: and g_prev_yr_period_seq+g_forward_periods
333: AND pmv.order_by_id = lower.sequence-g_prev_yr_period_seq
444: p_calendar_type as calendar_type
445: FROM pji_time_mv
446: WHERE period_id = g_curr_period_id
447: and calendar_id = p_calendar_id
448: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
449: ELSE
450: --Call the API to insert daily records
451:
452: PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
486: FROM
487: pji_time_mv pt
488: where quarter_id = g_curr_quarter_id
489: and calendar_id = p_calendar_id
490: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
491:
492: FOR i in l_curr_period_id_tbl.FIRST.. l_curr_period_id_tbl.LAST
493: LOOP
494: IF g_last_summ_period_id = l_curr_period_id_tbl(i) THEN
516: p_calendar_type as calendar_type
517: FROM pji_time_mv
518: WHERE quarter_id = g_curr_quarter_id
519: and calendar_id = p_calendar_id
520: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
521: ELSE
522: --Call the API to insert daily records
523:
524: PJI_PMV_ENGINE.Convert_NViewBY_AS_OF_DATE
590: l_prev_year_id
591: FROM pji_time_mv
592: WHERE sequence = g_prev_yr_period_seq
593: and calendar_id = p_calendar_id
594: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
595:
596: INSERT INTO PJI_PMV_TIME_DIM_TMP
597: (
598: ID,
733: fii_time_cal_day_mv day
734: ,pji_time_mv pmv
735: WHERE
736: report_date = g_last_summ_date
737: and pmv.period_type = 'FII_TIME_CAL_PERIOD' /* Added this condition for bug 4312361 */
738: AND day.cal_period_id = pmv.period_id
739: and pmv.calendar_id = l_calendar_id
740: and day.calendar_id = pmv.calendar_id;
741: END IF;
743: --Get the sequence for the last summarized date period
744: SELECT sequence
745: INTO g_last_summ_pd_seq
746: FROM pji_time_mv
747: WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
748: AND period_id = g_last_summ_period_id
749: AND calendar_id = l_calendar_id;
750:
751: --Get the sequence and other information for the current selected period
763: g_curr_quarter_name,
764: g_curr_year_id,
765: g_curr_year_name
766: FROM pji_time_mv
767: WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
768: AND period_id = p_period_id
769: AND calendar_id = l_calendar_id;
770:
771: --Get the corresponding period from the last year
790: ELSE
791: --Get start date of current period
792: SELECT start_date
793: INTO g_curr_yr_pd_start_date
794: FROM fii_time_cal_period
795: WHERE cal_period_id = p_period_id
796: AND calendar_id = l_calendar_id;
797:
798: --Get corresponding date in the last year
811: --Get the sequence for the last year corresponding period
812: SELECT NVL(sequence,0)
813: INTO g_prev_yr_period_seq
814: FROM pji_time_mv
815: WHERE period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD')
816: AND period_id = g_prev_yr_period_id
817: AND calendar_id = l_calendar_id;
818:
819: --Populate the time tables
961: l_prev_year_name
962: FROM pji_time_mv
963: WHERE sequence = g_prev_yr_period_seq
964: and calendar_id = l_calendar_id
965: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
966:
967: l_curr_yr_act_utilz_label := g_curr_year_name || ' ' || l_act_utilz_label;
968: l_curr_yr_sched_utilz_label := g_curr_year_name || ' ' || l_sched_utilz_label;
969: l_prev_yr_act_utilz_label := l_prev_year_name || ' ' || l_act_utilz_label;
1083: FROM
1084: pji_time_mv pt
1085: WHERE year_id = g_curr_year_id
1086: and calendar_id = l_calendar_id
1087: and period_type = DECODE(p_calendar_type,'E','FII_TIME_ENT_PERIOD', 'FII_TIME_CAL_PERIOD');
1088:
1089: --Populate the time tables
1090: IF (
1091: g_last_summ_pd_seq > g_curr_yr_max_sequence
1296: period_name,
1297: period_start_date
1298: FROM pji_time_mv
1299: WHERE calendar_id = l_calendar_id
1300: and period_type = 'FII_TIME_CAL_PERIOD'
1301: and year_id in
1302: (
1303: SELECT year_id
1304: FROM pji_time_mv
1445: (SELECT DECODE(SIGN(TRUNC(SYSDATE) - MAX(period_end_date)), 1, MAX(period_end_date), TRUNC(SYSDATE))
1446: FROM pji_time_mv
1447: WHERE 1=1
1448: AND calendar_id = l_calendar_id
1449: AND period_type = 'FII_TIME_CAL_PERIOD') BETWEEN period_start_date and period_end_date
1450: AND calendar_id = l_calendar_id
1451: AND period_type = 'FII_TIME_CAL_PERIOD';
1452: END IF;
1453: -- Bug Ref : 7010273
1447: WHERE 1=1
1448: AND calendar_id = l_calendar_id
1449: AND period_type = 'FII_TIME_CAL_PERIOD') BETWEEN period_start_date and period_end_date
1450: AND calendar_id = l_calendar_id
1451: AND period_type = 'FII_TIME_CAL_PERIOD';
1452: END IF;
1453: -- Bug Ref : 7010273
1454: EXCEPTION
1455: WHEN NO_DATA_FOUND THEN