[Home] [Help]
2091: Write2FWKLog('Generating records for period types other than PA Period.');
2092: END IF;
2093:
2094: IF l_IS_GL_Flag IS NOT NULL THEN
2095: INSERT INTO PJI_PMV_TIME_DIM_TMP
2096: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2097: SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
2098: FROM fii_time_cal_rpt_struct
2099: WHERE report_date = l_As_Of_Date
2099: WHERE report_date = l_As_Of_Date
2100: AND bitand(record_type_id,l_Level) = record_type_id
2101: AND calendar_id = l_Calendar_Id;
2102: ELSE
2103: INSERT INTO PJI_PMV_TIME_DIM_TMP
2104: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2105: SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
2106: FROM fii_time_rpt_struct
2107: WHERE report_date = l_As_Of_Date
2113: IF p_PA_DEBUG_MODE = 'Y' THEN
2114: Write2FWKLog('Generating prior id records for period types other than PA Period.');
2115: END IF;
2116: IF l_IS_GL_Flag IS NOT NULL THEN
2117: INSERT INTO PJI_PMV_TIME_DIM_TMP
2118: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2119: SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1, calendar_type
2120: FROM fii_time_cal_rpt_struct
2121: WHERE report_date = l_Prior_As_Of_Date
2121: WHERE report_date = l_Prior_As_Of_Date
2122: AND bitand(record_type_id,l_Level) = record_type_id
2123: AND calendar_id = l_Calendar_Id;
2124: ELSE
2125: INSERT INTO PJI_PMV_TIME_DIM_TMP
2126: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2127: SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1, calendar_type
2128: FROM fii_time_rpt_struct
2129: WHERE report_date = l_Prior_As_Of_Date
2138: IF p_PA_DEBUG_MODE = 'Y' THEN
2139: Write2FWKLog('Generating records for PA Period period type.');
2140: END IF;
2141:
2142: INSERT INTO PJI_PMV_TIME_DIM_TMP
2143: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2144: SELECT report_date_julian
2145: , l_Default_Period_Name, l_Default_Period_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
2146: WHERE cal_period_id = l_Period_Id
2152: IF p_PA_DEBUG_MODE = 'Y' THEN
2153: Write2FWKLog('Generating prior id records for PA Period period type.');
2154: END IF;
2155:
2156: INSERT INTO PJI_PMV_TIME_DIM_TMP
2157: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2158: SELECT report_date_julian
2159: ,l_Default_Period_Name, l_Default_Period_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
2160: WHERE cal_period_id = l_Prior_Period_Id
2173: END IF;
2174: IF l_Level <> 0 THEN
2175: CASE l_Level
2176: WHEN 119 THEN
2177: INSERT INTO PJI_PMV_TIME_DIM_TMP
2178: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2179: VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2180: WHEN 55 THEN
2181: INSERT INTO PJI_PMV_TIME_DIM_TMP
2177: INSERT INTO PJI_PMV_TIME_DIM_TMP
2178: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2179: VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2180: WHEN 55 THEN
2181: INSERT INTO PJI_PMV_TIME_DIM_TMP
2182: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2183: VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2184: WHEN 23 THEN
2185: INSERT INTO PJI_PMV_TIME_DIM_TMP
2181: INSERT INTO PJI_PMV_TIME_DIM_TMP
2182: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2183: VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2184: WHEN 23 THEN
2185: INSERT INTO PJI_PMV_TIME_DIM_TMP
2186: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2187: VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2188: WHEN 11 THEN
2189: INSERT INTO PJI_PMV_TIME_DIM_TMP
2185: INSERT INTO PJI_PMV_TIME_DIM_TMP
2186: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2187: VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2188: WHEN 11 THEN
2189: INSERT INTO PJI_PMV_TIME_DIM_TMP
2190: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2191: VALUES (l_Week_ID,l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
2192: END CASE;
2193:
2193:
2194: IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
2195: CASE l_Level
2196: WHEN 119 THEN
2197: INSERT INTO PJI_PMV_TIME_DIM_TMP
2198: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2199: VALUES (
2200: SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
2201: ||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
2200: SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
2201: ||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
2202: , l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2203: WHEN 55 THEN
2204: INSERT INTO PJI_PMV_TIME_DIM_TMP
2205: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2206: VALUES (
2207: SUBSTR(LPAD(l_Qtr_ID,8,'0'),1,3)
2208: ||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
2208: ||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
2209: ||SUBSTR(LPAD(l_Qtr_ID,8,'0'),8)
2210: , l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2211: WHEN 23 THEN
2212: INSERT INTO PJI_PMV_TIME_DIM_TMP
2213: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2214: VALUES (
2215: SUBSTR(LPAD(l_Period_ID,10,'0'),1,3)
2216: ||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
2216: ||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
2217: ||SUBSTR(LPAD(l_Period_ID,10,'0'),8)
2218: , l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2219: WHEN 11 THEN
2220: INSERT INTO PJI_PMV_TIME_DIM_TMP
2221: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2222: VALUES (
2223: SUBSTR(LPAD(l_Week_ID,11,'0'),1,3)
2224: ||TO_CHAR(SUBSTR(LPAD(l_Week_ID,11,'0'),4,4)-1)
2241: G_No_Rolling_Weeks:=5;
2242: END;
2243: END IF;
2244: l_Level:=11;
2245: INSERT INTO PJI_PMV_TIME_DIM_TMP
2246: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2247: SELECT time_id, l_Default_Period_Name, l_Default_Period_ID, period_type_id, 1 , calendar_type
2248: FROM fii_time_rpt_struct
2249: WHERE report_date = l_As_Of_Date
2250: AND bitand(record_type_id,l_Level) = record_type_id
2251: AND calendar_id = l_Calendar_Id;
2252:
2253: IF p_Full_Period_Flag IS NOT NULL THEN
2254: INSERT INTO PJI_PMV_TIME_DIM_TMP
2255: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2256: SELECT week_id, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E' FROM fii_time_week WHERE
2257: week_id >= l_Week_ID
2258: AND end_date <= (l_Week_Start_Date)+(G_No_Rolling_Weeks*7);
2552: END IF;
2553:
2554: IF l_IS_GL_Flag IS NOT NULL THEN
2555: IF (l_period_start_date <> l_as_of_date OR p_Period_Type LIKE '%PERIOD%') AND l_Level >= 11 THEN
2556: INSERT INTO PJI_PMV_TIME_DIM_TMP
2557: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2558: SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
2559: FROM fii_time_cal_day_mv
2560: WHERE
2563: AND report_date<=l_period_end_date;
2564: END IF;
2565:
2566: IF (l_qtr_start_date <> l_as_of_date OR p_Period_Type LIKE '%QTR%') AND l_Level >= 55 THEN
2567: INSERT INTO PJI_PMV_TIME_DIM_TMP
2568: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2569: SELECT cal_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
2570: FROM fii_time_cal_period
2571: WHERE
2574: AND end_date<=l_qtr_end_date;
2575: END IF;
2576:
2577: IF (l_year_start_date <> l_as_of_date OR p_Period_Type LIKE '%YEAR%') AND l_Level >= 119 THEN
2578: INSERT INTO PJI_PMV_TIME_DIM_TMP
2579: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2580: SELECT cal_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
2581: FROM fii_time_cal_qtr
2582: WHERE
2588: IF p_PA_DEBUG_MODE = 'Y' THEN
2589: Write2FWKLog('Generating prior id records for fiscal period types.');
2590: END IF;
2591: IF (l_prior_period_start_date <> l_prior_as_of_date OR p_Period_Type LIKE '%PERIOD%') AND l_Level >= 11 THEN
2592: INSERT INTO PJI_PMV_TIME_DIM_TMP
2593: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2594: SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
2595: FROM fii_time_cal_day_mv
2596: WHERE
2598: AND calendar_id = l_calendar_id
2599: AND report_date<=l_prior_period_end_date;
2600: END IF;
2601: IF (l_prior_qtr_start_date <> l_prior_as_of_date OR p_Period_Type LIKE '%QTR%') AND l_Level >= 55 THEN
2602: INSERT INTO PJI_PMV_TIME_DIM_TMP
2603: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2604: SELECT cal_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
2605: FROM fii_time_cal_period
2606: WHERE
2609: AND end_date<=l_prior_qtr_end_date;
2610: END IF;
2611:
2612: IF (l_prior_year_start_date <> l_prior_as_of_date OR p_Period_Type LIKE '%YEAR%') AND l_Level >= 119 THEN
2613: INSERT INTO PJI_PMV_TIME_DIM_TMP
2614: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2615: SELECT cal_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
2616: FROM fii_time_cal_qtr
2617: WHERE
2621: END IF;
2622: END IF;
2623: ELSE
2624: IF (l_period_start_date <> l_as_of_date OR p_Period_Type LIKE '%PERIOD%') AND l_Level >= 11 THEN
2625: INSERT INTO PJI_PMV_TIME_DIM_TMP
2626: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2627: SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
2628: FROM fii_time_day
2629: WHERE
2631: AND report_date<=l_period_end_date;
2632: END IF;
2633:
2634: IF (l_qtr_start_date <> l_as_of_date OR p_Period_Type LIKE '%QTR%') AND l_Level >= 55 THEN
2635: INSERT INTO PJI_PMV_TIME_DIM_TMP
2636: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2637: SELECT ent_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
2638: FROM fii_time_ent_period
2639: WHERE
2641: AND end_date<=l_qtr_end_date;
2642: END IF;
2643:
2644: IF (l_year_start_date <> l_as_of_date OR p_Period_Type LIKE '%YEAR%') AND l_Level >= 119 THEN
2645: INSERT INTO PJI_PMV_TIME_DIM_TMP
2646: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2647: SELECT ent_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
2648: FROM fii_time_ent_qtr
2649: WHERE
2654: IF p_PA_DEBUG_MODE = 'Y' THEN
2655: Write2FWKLog('Generating prior id records for enterprise period types.');
2656: END IF;
2657: IF (l_prior_period_start_date <> l_prior_as_of_date OR p_Period_Type LIKE '%PERIOD%') AND l_Level >= 11 THEN
2658: INSERT INTO PJI_PMV_TIME_DIM_TMP
2659: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2660: SELECT report_date_julian, l_Default_Period_Name, l_Default_Period_ID, 1, 0, l_Calendar_Type_Day
2661: FROM fii_time_day
2662: WHERE
2664: AND report_date<=l_Prior_period_end_date;
2665: END IF;
2666:
2667: IF (l_prior_qtr_start_date <> l_prior_as_of_date OR p_Period_Type LIKE '%QTR%') AND l_Level >= 55 THEN
2668: INSERT INTO PJI_PMV_TIME_DIM_TMP
2669: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2670: SELECT ent_period_id, l_Default_Period_Name, l_Default_Period_ID, 32, 0, l_Calendar_Type_Sum
2671: FROM fii_time_ent_period
2672: WHERE
2674: AND end_date<=l_prior_qtr_end_date;
2675: END IF;
2676:
2677: IF (l_Prior_year_start_date <> l_Prior_as_of_date OR p_Period_Type LIKE '%YEAR%') AND l_Level >= 119 THEN
2678: INSERT INTO PJI_PMV_TIME_DIM_TMP
2679: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2680: SELECT ent_qtr_id, l_Default_Period_Name, l_Default_Period_ID, 64, 0, l_Calendar_Type_Sum
2681: FROM fii_time_ent_qtr
2682: WHERE
2694: END IF;
2695: IF l_Level <> -1 THEN
2696: CASE l_Level
2697: WHEN 119 THEN
2698: INSERT INTO PJI_PMV_TIME_DIM_TMP
2699: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2700: VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2701: WHEN 55 THEN
2702: INSERT INTO PJI_PMV_TIME_DIM_TMP
2698: INSERT INTO PJI_PMV_TIME_DIM_TMP
2699: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2700: VALUES (l_Year_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2701: WHEN 55 THEN
2702: INSERT INTO PJI_PMV_TIME_DIM_TMP
2703: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2704: VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2705: WHEN 23 THEN
2706: INSERT INTO PJI_PMV_TIME_DIM_TMP
2702: INSERT INTO PJI_PMV_TIME_DIM_TMP
2703: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2704: VALUES (l_Qtr_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2705: WHEN 23 THEN
2706: INSERT INTO PJI_PMV_TIME_DIM_TMP
2707: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2708: VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2709: WHEN 11 THEN
2710: INSERT INTO PJI_PMV_TIME_DIM_TMP
2706: INSERT INTO PJI_PMV_TIME_DIM_TMP
2707: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2708: VALUES (l_Period_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2709: WHEN 11 THEN
2710: INSERT INTO PJI_PMV_TIME_DIM_TMP
2711: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2712: VALUES (l_Week_ID, l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
2713: END CASE;
2714:
2714:
2715: IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
2716: CASE l_Level
2717: WHEN 119 THEN
2718: INSERT INTO PJI_PMV_TIME_DIM_TMP
2719: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2720: VALUES (
2721: SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
2722: ||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
2721: SUBSTR(LPAD(l_Year_ID,7,'0'),1,3)
2722: ||TO_CHAR(SUBSTR(LPAD(l_Year_ID,7,'0'),4,4)-1)
2723: , l_Default_Period_Name, l_Default_Period_ID, 2, 128, l_Calendar_Type_Sum);
2724: WHEN 55 THEN
2725: INSERT INTO PJI_PMV_TIME_DIM_TMP
2726: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2727: VALUES (
2728: SUBSTR(LPAD(l_Qtr_ID,8,'0'),1,3)
2729: ||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
2729: ||TO_CHAR(SUBSTR(LPAD(l_Qtr_ID,8,'0'),4,4)-1)
2730: ||SUBSTR(LPAD(l_Qtr_ID,8,'0'),8)
2731: , l_Default_Period_Name, l_Default_Period_ID, 2, 64, l_Calendar_Type_Sum);
2732: WHEN 23 THEN
2733: INSERT INTO PJI_PMV_TIME_DIM_TMP
2734: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2735: VALUES (
2736: SUBSTR(LPAD(l_Period_ID,10,'0'),1,3)
2737: ||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
2737: ||TO_CHAR(SUBSTR(LPAD(l_Period_ID,10,'0'),4,4)-1)
2738: ||SUBSTR(LPAD(l_Period_ID,10,'0'),8)
2739: , l_Default_Period_Name, l_Default_Period_ID, 2, 32, l_Calendar_Type_Sum);
2740: WHEN 11 THEN
2741: INSERT INTO PJI_PMV_TIME_DIM_TMP
2742: (PRIOR_ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2743: VALUES (
2744: SUBSTR(LPAD(l_Week_ID,11,'0'),1,3)
2745: ||TO_CHAR(SUBSTR(LPAD(l_Week_ID,11,'0'),4,4)-1)
3424: Write2FWKLog('Generating records for period types other than PA Period.');
3425: END IF;
3426:
3427: IF l_IS_GL_Flag IS NOT NULL THEN
3428: INSERT INTO PJI_PMV_TIME_DIM_TMP
3429: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3430: SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1 , calendar_type
3431: FROM fii_time_cal_rpt_struct
3432: WHERE report_date = l_As_Of_Date
3432: WHERE report_date = l_As_Of_Date
3433: AND bitand(record_type_id,l_Level) = record_type_id
3434: AND calendar_id = l_Calendar_Id;
3435: ELSE
3436: INSERT INTO PJI_PMV_TIME_DIM_TMP
3437: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3438: SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1 , calendar_type
3439: FROM fii_time_rpt_struct
3440: WHERE report_date = l_As_Of_Date
3446: IF p_PA_DEBUG_MODE = 'Y' THEN
3447: Write2FWKLog('Generating prior id records for period types other than PA Period.');
3448: END IF;
3449: IF l_IS_GL_Flag IS NOT NULL THEN
3450: INSERT INTO PJI_PMV_TIME_DIM_TMP
3451: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3452: SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1, calendar_type
3453: FROM fii_time_cal_rpt_struct
3454: WHERE report_date = l_Prior_As_Of_Date
3454: WHERE report_date = l_Prior_As_Of_Date
3455: AND bitand(record_type_id,l_Level) = record_type_id
3456: AND calendar_id = l_Calendar_Id;
3457: ELSE
3458: INSERT INTO PJI_PMV_TIME_DIM_TMP
3459: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3460: SELECT time_id, l_Def_View_BY, l_Def_View_BY_ID, period_type_id, 1, calendar_type
3461: FROM fii_time_rpt_struct
3462: WHERE report_date = l_Prior_As_Of_Date
3471: IF p_PA_DEBUG_MODE = 'Y' THEN
3472: Write2FWKLog('Generating records for PA Period period type.');
3473: END IF;
3474:
3475: INSERT INTO PJI_PMV_TIME_DIM_TMP
3476: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3477: SELECT report_date_julian
3478: , l_Def_View_BY, l_Def_View_BY_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
3479: WHERE cal_period_id = l_Period_Id
3485: IF p_PA_DEBUG_MODE = 'Y' THEN
3486: Write2FWKLog('Generating prior id records for PA Period period type.');
3487: END IF;
3488:
3489: INSERT INTO PJI_PMV_TIME_DIM_TMP
3490: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
3491: SELECT report_date_julian
3492: , l_Def_View_BY, l_Def_View_BY_ID, 1, 1, 'P' FROM fii_time_cal_day_mv
3493: WHERE cal_period_id = l_Prior_Period_Id
3506: Write2FWKLog('Full Period Flag is set.');
3507: END IF;
3508: CASE l_Level
3509: WHEN 119 THEN
3510: INSERT INTO PJI_PMV_TIME_DIM_TMP
3511: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3512: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 128, l_Calendar_Type_Sum);
3513: WHEN 55 THEN
3514: INSERT INTO PJI_PMV_TIME_DIM_TMP
3510: INSERT INTO PJI_PMV_TIME_DIM_TMP
3511: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3512: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 128, l_Calendar_Type_Sum);
3513: WHEN 55 THEN
3514: INSERT INTO PJI_PMV_TIME_DIM_TMP
3515: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3516: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 64, l_Calendar_Type_Sum);
3517: WHEN 23 THEN
3518: INSERT INTO PJI_PMV_TIME_DIM_TMP
3514: INSERT INTO PJI_PMV_TIME_DIM_TMP
3515: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3516: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 64, l_Calendar_Type_Sum);
3517: WHEN 23 THEN
3518: INSERT INTO PJI_PMV_TIME_DIM_TMP
3519: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3520: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 32, l_Calendar_Type_Sum);
3521: WHEN 11 THEN
3522: INSERT INTO PJI_PMV_TIME_DIM_TMP
3518: INSERT INTO PJI_PMV_TIME_DIM_TMP
3519: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3520: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 32, l_Calendar_Type_Sum);
3521: WHEN 11 THEN
3522: INSERT INTO PJI_PMV_TIME_DIM_TMP
3523: (ID, NAME, ORDER_BY_ID, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
3524: VALUES (l_Def_View_BY_ID, l_Def_View_BY, l_Def_View_BY_ID, 2, 16, 'E');
3525: END CASE;
3526:
3533: WHEN 'FII_TIME_ENT_YEAR' THEN
3534: IF p_PA_DEBUG_MODE = 'Y' THEN
3535: Write2FWKLog('Creating time records for enterprize year.');
3536: END IF;
3537: INSERT INTO PJI_PMV_TIME_DIM_TMP
3538: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3539: SELECT ent_year_id, name, ent_year_id, 128, 'E'
3540: FROM fii_time_ent_year
3541: WHERE start_date > l_End_Time
3543: WHEN 'FII_TIME_ENT_QTR' THEN
3544: IF p_PA_DEBUG_MODE = 'Y' THEN
3545: Write2FWKLog('Creating time records for enterprize quarter.');
3546: END IF;
3547: INSERT INTO PJI_PMV_TIME_DIM_TMP
3548: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3549: SELECT ent_qtr_id, name, ent_qtr_id, 64, 'E'
3550: FROM fii_time_ent_qtr
3551: WHERE start_date > l_End_Time
3553: WHEN 'FII_TIME_ENT_PERIOD' THEN
3554: IF p_PA_DEBUG_MODE = 'Y' THEN
3555: Write2FWKLog('Creating time records for enterprize period.');
3556: END IF;
3557: INSERT INTO PJI_PMV_TIME_DIM_TMP
3558: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3559: SELECT ent_period_id, name, ent_period_id, 32, 'E'
3560: FROM fii_time_ent_period
3561: WHERE start_date > l_End_Time
3563: WHEN 'FII_TIME_CAL_YEAR' THEN
3564: IF p_PA_DEBUG_MODE = 'Y' THEN
3565: Write2FWKLog('Creating time records for fiscal year.');
3566: END IF;
3567: INSERT INTO PJI_PMV_TIME_DIM_TMP
3568: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3569: SELECT cal_year_id, name, cal_year_id, 128, 'G'
3570: FROM fii_time_cal_year
3571: WHERE start_date > l_End_Time
3574: WHEN 'FII_TIME_CAL_QTR' THEN
3575: IF p_PA_DEBUG_MODE = 'Y' THEN
3576: Write2FWKLog('Creating time records for fiscal quarter.');
3577: END IF;
3578: INSERT INTO PJI_PMV_TIME_DIM_TMP
3579: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3580: SELECT cal_qtr_id, name, cal_qtr_id, 64, 'G'
3581: FROM fii_time_cal_qtr
3582: WHERE start_date > l_End_Time
3585: WHEN 'FII_TIME_CAL_PERIOD' THEN
3586: IF p_PA_DEBUG_MODE = 'Y' THEN
3587: Write2FWKLog('Creating time records for fiscal period.');
3588: END IF;
3589: INSERT INTO PJI_PMV_TIME_DIM_TMP
3590: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3591: SELECT cal_period_id, name, cal_period_id, 32, 'G'
3592: FROM fii_time_cal_period
3593: WHERE start_date > l_End_Time
3596: WHEN 'PJI_TIME_PA_PERIOD' THEN
3597: IF p_PA_DEBUG_MODE = 'Y' THEN
3598: Write2FWKLog('Creating time records for pa period.');
3599: END IF;
3600: INSERT INTO PJI_PMV_TIME_DIM_TMP
3601: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3602: SELECT cal_period_id, name, cal_period_id, 32, 'P'
3603: FROM fii_time_cal_period
3604: WHERE start_date > l_End_Time
3607: WHEN 'FII_TIME_WEEK' THEN
3608: IF p_PA_DEBUG_MODE = 'Y' THEN
3609: Write2FWKLog('Creating time records for enterprize week.');
3610: END IF;
3611: INSERT INTO PJI_PMV_TIME_DIM_TMP
3612: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
3613: SELECT week_id, name, week_id, 16, 'E'
3614: FROM fii_time_week
3615: WHERE start_date > l_End_Time
3619: IF p_Parse_Prior IS NOT NULL THEN
3620: IF p_PA_DEBUG_MODE = 'Y' THEN
3621: Write2FWKLog('Parse Prior is set.');
3622: END IF;
3623: UPDATE PJI_PMV_TIME_DIM_TMP
3624: SET PRIOR_ID = (CASE period_type
3625: WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
3626: WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
3627: WHEN 32 THEN SUBSTR(LPAD(ID,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,10,'0'),4,4)-1)||SUBSTR(LPAD(ID,10,'0'),8)
3662: l_Level:=128;
3663: SELECT MIN(TIME.start_date)-1
3664: INTO l_As_Of_Date
3665: FROM
3666: pji_pmv_time_dim_tmp TTMP,
3667: fii_time_ent_year TIME
3668: WHERE TTMP.period_type = l_Level
3669: AND TTMP.id = TIME.ent_year_id;
3670: WHEN 'FII_TIME_CAL_YEAR' THEN
3671: l_Level:=128;
3672: SELECT MIN(TIME.start_date)-1
3673: INTO l_As_Of_Date
3674: FROM
3675: pji_pmv_time_dim_tmp TTMP,
3676: fii_time_cal_year TIME
3677: WHERE TTMP.period_type = l_Level
3678: AND TTMP.id = TIME.cal_year_id;
3679: WHEN 'FII_TIME_ENT_QTR' THEN
3680: l_Level:=64;
3681: SELECT MIN(TIME.start_date)-1
3682: INTO l_As_Of_Date
3683: FROM
3684: pji_pmv_time_dim_tmp TTMP,
3685: fii_time_ent_qtr TIME
3686: WHERE TTMP.period_type = l_Level
3687: AND TTMP.id = TIME.ent_qtr_id;
3688: WHEN 'FII_TIME_CAL_QTR' THEN
3689: l_Level:=64;
3690: SELECT MIN(TIME.start_date)-1
3691: INTO l_As_Of_Date
3692: FROM
3693: pji_pmv_time_dim_tmp TTMP,
3694: fii_time_cal_qtr TIME
3695: WHERE TTMP.period_type = l_Level
3696: AND TTMP.id = TIME.cal_qtr_id;
3697: WHEN 'FII_TIME_ENT_PERIOD' THEN
3698: l_Level:=32;
3699: SELECT MIN(TIME.start_date)-1
3700: INTO l_As_Of_Date
3701: FROM
3702: pji_pmv_time_dim_tmp TTMP,
3703: fii_time_ent_period TIME
3704: WHERE TTMP.period_type = l_Level
3705: AND TTMP.id = TIME.ent_period_id;
3706: WHEN 'FII_TIME_CAL_PERIOD' THEN
3707: l_Level:=32;
3708: SELECT MIN(TIME.start_date)-1
3709: INTO l_As_Of_Date
3710: FROM
3711: pji_pmv_time_dim_tmp TTMP,
3712: fii_time_cal_period TIME
3713: WHERE TTMP.period_type = l_Level
3714: AND TTMP.id = TIME.cal_period_id;
3715: WHEN 'PJI_TIME_PA_PERIOD' THEN
3716: l_Level:=32;
3717: SELECT MIN(TIME.start_date)-1
3718: INTO l_As_Of_Date
3719: FROM
3720: pji_pmv_time_dim_tmp TTMP,
3721: fii_time_cal_period TIME
3722: WHERE TTMP.period_type = l_Level
3723: AND TTMP.id = TIME.cal_period_id;
3724: WHEN 'FII_TIME_WEEK' THEN
3725: l_Level:=16;
3726: SELECT MIN(TIME.start_date)-1
3727: INTO l_As_Of_Date
3728: FROM
3729: pji_pmv_time_dim_tmp TTMP,
3730: fii_time_week TIME
3731: WHERE TTMP.period_type = l_Level
3732: AND TTMP.id = TIME.week_id;
3733: ELSE NULL;
3779: l_Period_Type VARCHAR2(150):=p_Period_Type;
3780: BEGIN
3781: Write2FWKLog('Entering Convert_Time...','Convert_Time');
3782:
3783: DELETE PJI_PMV_TIME_DIM_TMP;
3784:
3785: IF p_Period_Type LIKE '%PA%' THEN
3786: l_Calendar_Id:=G_PA_Calendar_ID;
3787: Write2FWKLog('PA calender is selected.');
3792:
3793: CASE p_Period_Type
3794: WHEN 'FII_TIME_ENT_YEAR' THEN
3795: Write2FWKLog('Creating time records for enterprize year.');
3796: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3797: SELECT ENT_YEAR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 128, ENT_YEAR_ID, NULL, 'E'
3798: FROM FII_TIME_ENT_YEAR
3799: WHERE l_Start_Date <= end_date
3800: AND l_End_Date >=start_date;
3799: WHERE l_Start_Date <= end_date
3800: AND l_End_Date >=start_date;
3801: WHEN 'FII_TIME_ENT_QTR' THEN
3802: Write2FWKLog('Creating time records for enterprize quarter.');
3803: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3804: SELECT ENT_QTR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 64, ENT_QTR_ID, NULL, 'E'
3805: FROM FII_TIME_ENT_QTR
3806: WHERE l_Start_Date <= end_date
3807: AND l_End_Date >=start_date;
3806: WHERE l_Start_Date <= end_date
3807: AND l_End_Date >=start_date;
3808: WHEN 'FII_TIME_ENT_PERIOD' THEN
3809: Write2FWKLog('Creating time records for enterprize period.');
3810: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3811: SELECT ENT_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, ENT_PERIOD_ID, NULL, 'E'
3812: FROM FII_TIME_ENT_PERIOD
3813: WHERE l_Start_Date <= end_date
3814: AND l_End_Date >=start_date;
3813: WHERE l_Start_Date <= end_date
3814: AND l_End_Date >=start_date;
3815: WHEN 'FII_TIME_CAL_YEAR' THEN
3816: Write2FWKLog('Creating time records for fiscal year.');
3817: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3818: SELECT CAL_YEAR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 128, CAL_YEAR_ID, NULL, 'G'
3819: FROM FII_TIME_CAL_YEAR
3820: WHERE l_Start_Date <= end_date
3821: AND l_End_Date >=start_date
3821: AND l_End_Date >=start_date
3822: AND calendar_id = l_Calendar_ID;
3823: WHEN 'FII_TIME_CAL_QTR' THEN
3824: Write2FWKLog('Creating time records for fiscal quarter.');
3825: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3826: SELECT CAL_QTR_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 64, CAL_QTR_ID, NULL, 'G'
3827: FROM FII_TIME_CAL_QTR
3828: WHERE l_Start_Date <= end_date
3829: AND l_End_Date >=start_date
3829: AND l_End_Date >=start_date
3830: AND calendar_id = l_Calendar_ID;
3831: WHEN 'FII_TIME_CAL_PERIOD' THEN
3832: Write2FWKLog('Creating time records for fiscal period.');
3833: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3834: SELECT CAL_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, CAL_PERIOD_ID, NULL, 'G'
3835: FROM FII_TIME_CAL_PERIOD
3836: WHERE l_Start_Date <= end_date
3837: AND l_End_Date >=start_date
3837: AND l_End_Date >=start_date
3838: AND calendar_id = l_Calendar_ID;
3839: WHEN 'PJI_TIME_PA_PERIOD' THEN
3840: Write2FWKLog('Creating time records for pa period.');
3841: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3842: SELECT CAL_PERIOD_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 32, CAL_PERIOD_ID, NULL, 'G'
3843: FROM FII_TIME_CAL_PERIOD
3844: WHERE l_Start_Date <= end_date
3845: AND l_End_Date >=start_date
3845: AND l_End_Date >=start_date
3846: AND calendar_id = l_Calendar_ID;
3847: WHEN 'FII_TIME_WEEK' THEN
3848: Write2FWKLog('Creating time records for week.');
3849: INSERT INTO PJI_PMV_TIME_DIM_TMP (ID, PRIOR_ID, NAME, PERIOD_TYPE, ORDER_BY_ID, AMOUNT_TYPE, CALENDAR_TYPE)
3850: SELECT WEEK_ID, NULL, DECODE(p_View_BY,'TM',NAME,'-1'), 16, WEEK_ID, NULL, 'E'
3851: FROM FII_TIME_WEEK
3852: WHERE l_Start_Date <= end_date
3853: AND l_End_Date >=start_date;
3858: Write2FWKLog('Done creating time records.');
3859:
3860: IF p_Parse_Prior IS NOT NULL THEN
3861: Write2FWKLog('Parse Prior is set.');
3862: UPDATE PJI_PMV_TIME_DIM_TMP
3863: SET PRIOR_ID = (CASE period_type
3864: WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
3865: WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
3866: WHEN 32 THEN SUBSTR(LPAD(ID,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,10,'0'),4,4)-1)||SUBSTR(LPAD(ID,10,'0'),8)
3911: Write2FWKLog(' p_Full_Period_Flag: '||p_Full_Period_Flag);
3912: Write2FWKLog('Clearing time temporary tables...');
3913: END IF;
3914:
3915: DELETE PJI_PMV_TIME_DIM_TMP;
3916:
3917: IF p_Parse_ITD IS NOT NULL THEN
3918: DELETE PJI_PMV_ITD_DIM_TMP;
3919: END IF;
4093:
4094: l_summ_date := trunc(to_date(PJI_UTILS.GET_PARAMETER('LAST_FM_EXTR_DATE'),'YYYY/MM/DD'));
4095: Write2FWKLog('The last summarized date is ' || to_char(l_summ_date, 'YYYY/MM/DD'));
4096:
4097: DELETE PJI_PMV_TIME_DIM_TMP;
4098:
4099: if(l_summ_date is null) then
4100: return;
4101: end if;
4273: -- of the week from the date
4274: -- Week is only applicable to Enterprise Week
4275: IF l_level = 0 THEN
4276: Write2FWKLog('Creating day slice for the given week.');
4277: INSERT INTO PJI_PMV_TIME_DIM_TMP
4278: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4279: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4280: FROM fii_time_day, dual
4281: WHERE week_id = l_week_id_tmp
4282: AND report_date <= l_date;
4283:
4284: IF p_parse_prior IS NOT NULL THEN
4285: Write2FWKLog('Creating day slice for the prior year week.');
4286: INSERT INTO PJI_PMV_TIME_DIM_TMP
4287: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4288: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4289: FROM fii_time_day
4290: WHERE week_id = SUBSTR(LPAD(l_week_id_tmp,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_week_id_tmp,11,'0'),4,4)-1)||SUBSTR(LPAD(l_week_id_tmp,11,'0'),8)
4301: IF l_level >= 1 THEN
4302: IF l_is_GL IS NULL THEN
4303: Write2FWKLog('Creating day slice for the given period - Enterprise Cal.');
4304:
4305: INSERT INTO PJI_PMV_TIME_DIM_TMP
4306: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4307: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4308: FROM fii_time_day, dual
4309: WHERE ent_period_id = l_period_id_tmp
4310: AND report_date <= l_date;
4311:
4312: IF p_parse_prior IS NOT NULL THEN
4313: Write2FWKLog('Creating day slice for the prior year period - Enterprise Cal.');
4314: INSERT INTO PJI_PMV_TIME_DIM_TMP
4315: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4316: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4317: FROM fii_time_day
4318: WHERE ent_period_id = SUBSTR(LPAD(l_period_id_tmp,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_tmp,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_tmp,10,'0'),8)
4321:
4322: ELSIF l_is_GL IS NOT NULL THEN
4323: Write2FWKLog('Creating day slice for the given period - Fiscal Cal.');
4324:
4325: INSERT INTO PJI_PMV_TIME_DIM_TMP
4326: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4327: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4328: FROM fii_time_cal_day_mv, dual
4329: WHERE cal_period_id = l_period_id_tmp
4331: AND report_date <= l_date;
4332:
4333: IF p_parse_prior IS NOT NULL THEN
4334: Write2FWKLog('Creating day slice for the prior year period - Fiscal Cal.');
4335: INSERT INTO PJI_PMV_TIME_DIM_TMP
4336: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4337: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4338: FROM fii_time_cal_day_mv
4339: WHERE cal_period_id = SUBSTR(LPAD(l_period_id_tmp,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_tmp,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_tmp,10,'0'),8)
4351: IF l_level >= 32 THEN
4352: IF l_is_GL IS NULL THEN
4353: Write2FWKLog('Creating period slice for the given quarter - Enterprise Cal.');
4354:
4355: INSERT INTO PJI_PMV_TIME_DIM_TMP
4356: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4357: SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4358: FROM fii_time_ent_period, dual
4359: WHERE ent_qtr_id = l_qtr_id_tmp
4361:
4362: ELSIF l_is_GL IS NOT NULL THEN
4363: Write2FWKLog('Creating period slice for the given quarter - Fiscal Cal.');
4364:
4365: INSERT INTO PJI_PMV_TIME_DIM_TMP
4366: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4367: SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4368: FROM fii_time_cal_period, dual
4369: WHERE cal_qtr_id = l_qtr_id_tmp
4379: IF l_level >= 64 THEN
4380: IF l_is_GL IS NULL THEN
4381: Write2FWKLog('Creating quarter slice for the given year - Enterprise Cal.');
4382:
4383: INSERT INTO PJI_PMV_TIME_DIM_TMP
4384: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4385: SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4386: FROM fii_time_ent_qtr, dual
4387: WHERE ent_year_id = l_year_id_tmp
4389:
4390: ELSIF l_is_GL IS NOT NULL THEN
4391: Write2FWKLog('Creating quarter slice for the given year - Fiscal Cal.');
4392:
4393: INSERT INTO PJI_PMV_TIME_DIM_TMP
4394: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4395: SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4396: FROM fii_time_cal_qtr, dual
4397: WHERE cal_year_id = l_year_id_tmp
4408: IF l_level >= 128 THEN
4409: IF l_is_GL IS NULL THEN
4410: Write2FWKLog('Creating year slice for all previous years - Enterprise Cal.');
4411:
4412: INSERT INTO PJI_PMV_TIME_DIM_TMP
4413: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4414: SELECT ent_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
4415: FROM fii_time_ent_year, dual
4416: WHERE start_date < l_year_start_date_tmp;
4417:
4418: ELSIF l_is_GL IS NOT NULL THEN
4419: Write2FWKLog('Creating year slice for all previous years - Fiscal Cal.');
4420:
4421: INSERT INTO PJI_PMV_TIME_DIM_TMP
4422: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4423: SELECT cal_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
4424: FROM fii_time_cal_year, dual
4425: WHERE calendar_id = l_calendar_id
4449: -- to the as_of_date values
4450: -- Week is only applicable to Enterprise Week
4451: IF l_level = 0 THEN
4452: Write2FWKLog('Creating day slice for the given week between the two dates - scheduled');
4453: INSERT INTO PJI_PMV_TIME_DIM_TMP
4454: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4455: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4456: FROM fii_time_day, dual
4457: WHERE week_id = l_week_id
4459: AND report_date <= l_as_of_date;
4460:
4461: IF p_parse_prior IS NOT NULL THEN
4462: Write2FWKLog('Creating day slice for the prior year week between the two dates - scheduled');
4463: INSERT INTO PJI_PMV_TIME_DIM_TMP
4464: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4465: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4466: FROM fii_time_day
4467: WHERE week_id = SUBSTR(LPAD(l_week_id,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_week_id,11,'0'),4,4)-1)||SUBSTR(LPAD(l_week_id,11,'0'),8)
4480:
4481: IF l_is_GL IS NULL THEN
4482: Write2FWKLog('Creating day slice for between the two dates - Enterprise Cal - scheduled');
4483:
4484: INSERT INTO PJI_PMV_TIME_DIM_TMP
4485: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4486: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4487: FROM fii_time_day, dual
4488: WHERE ent_period_id = l_period_id
4490: AND report_date <= l_as_of_date;
4491:
4492: IF p_parse_prior IS NOT NULL THEN
4493: Write2FWKLog('Creating day slice for the prior year period between two_dates - Enterprise Cal - scheduled');
4494: INSERT INTO PJI_PMV_TIME_DIM_TMP
4495: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4496: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4497: FROM fii_time_day
4498: WHERE ent_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
4503:
4504: ELSIF l_is_GL IS NOT NULL THEN
4505: Write2FWKLog('Creating day slice for between the two dates - Fiscal Cal - scheduled');
4506:
4507: INSERT INTO PJI_PMV_TIME_DIM_TMP
4508: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4509: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4510: FROM fii_time_cal_day_mv, dual
4511: WHERE cal_period_id = l_period_id
4514: AND report_date <= l_as_of_date;
4515:
4516: IF p_parse_prior IS NOT NULL THEN
4517: Write2FWKLog('Creating day slice for the prior year period between the two dates - Fiscal Cal - scheduled.');
4518: INSERT INTO PJI_PMV_TIME_DIM_TMP
4519: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4520: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4521: FROM fii_time_cal_day_mv
4522: WHERE cal_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
4535:
4536: IF l_is_GL IS NULL THEN
4537: Write2FWKLog('Creating day slice for the last_summ_date period - Enterprise Cal - scheduled');
4538:
4539: INSERT INTO PJI_PMV_TIME_DIM_TMP
4540: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4541: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4542: FROM fii_time_day, dual
4543: WHERE ent_period_id = l_period_id_s
4544: AND report_date > l_summ_date;
4545:
4546: IF p_parse_prior IS NOT NULL THEN
4547: Write2FWKLog('Creating day slice for the prior year period of last_summ_date - Enterprise Cal - scheduled');
4548: INSERT INTO PJI_PMV_TIME_DIM_TMP
4549: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4550: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4551: FROM fii_time_day
4552: WHERE ent_period_id = SUBSTR(LPAD(l_period_id_s,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_s,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_s,10,'0'),8)
4555:
4556:
4557: Write2FWKLog('Creating day slice for the as_of_date period - Enterprise Cal - scheduled');
4558:
4559: INSERT INTO PJI_PMV_TIME_DIM_TMP
4560: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4561: SELECT report_date_julian, '-1', '-1', 1 , l_amount_type, l_calendar_type_day
4562: FROM fii_time_day, dual
4563: WHERE ent_period_id = l_period_id
4564: AND report_date <= l_as_of_date;
4565:
4566: IF p_parse_prior IS NOT NULL THEN
4567: Write2FWKLog('Creating day slice for the prior year period of as_of_date - Enterprise Cal - scheduled');
4568: INSERT INTO PJI_PMV_TIME_DIM_TMP
4569: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4570: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4571: FROM fii_time_day
4572: WHERE ent_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
4575:
4576: ELSIF l_is_GL IS NOT NULL THEN
4577: Write2FWKLog('Creating day slice for the last_summ_date period - Fiscal Cal - scheduled');
4578:
4579: INSERT INTO PJI_PMV_TIME_DIM_TMP
4580: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4581: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4582: FROM fii_time_cal_day_mv, dual
4583: WHERE cal_period_id = l_period_id_s
4585: AND report_date > l_summ_date;
4586:
4587: IF p_parse_prior IS NOT NULL THEN
4588: Write2FWKLog('Creating day slice for the prior year period of last_summ_date - Fiscal Cal - scheduled');
4589: INSERT INTO PJI_PMV_TIME_DIM_TMP
4590: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4591: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4592: FROM fii_time_cal_day_mv
4593: WHERE cal_period_id = SUBSTR(LPAD(l_period_id_s,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id_s,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id_s,10,'0'),8)
4596: END IF;
4597:
4598: Write2FWKLog('Creating day slice for the as_of_date period - Fiscal Cal - scheduled');
4599:
4600: INSERT INTO PJI_PMV_TIME_DIM_TMP
4601: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4602: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4603: FROM fii_time_cal_day_mv, dual
4604: WHERE cal_period_id = l_period_id
4606: AND report_date <= l_as_of_date;
4607:
4608: IF p_parse_prior IS NOT NULL THEN
4609: Write2FWKLog('Creating day slice for the prior year period of as_of_date - Fiscal Cal - scheduled');
4610: INSERT INTO PJI_PMV_TIME_DIM_TMP
4611: (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4612: SELECT report_date_julian, '-1', '-1', 1, l_amount_type, l_calendar_type_day
4613: FROM fii_time_cal_day_mv
4614: WHERE cal_period_id = SUBSTR(LPAD(l_period_id,10,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(l_period_id,10,'0'),4,4)-1)||SUBSTR(LPAD(l_period_id,10,'0'),8)
4633:
4634: IF l_is_GL IS NULL THEN
4635: Write2FWKLog('Creating period slice for between the two dates periods - Enterprise Cal - scheduled');
4636:
4637: INSERT INTO PJI_PMV_TIME_DIM_TMP
4638: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4639: SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4640: FROM fii_time_ent_period, dual
4641: WHERE ent_qtr_id = l_qtr_id
4644:
4645: ELSIF l_is_GL IS NOT NULL THEN
4646: Write2FWKLog('Creating period slice for between the two dates periods - Fiscal Cal - scheduled');
4647:
4648: INSERT INTO PJI_PMV_TIME_DIM_TMP
4649: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4650: SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4651: FROM fii_time_cal_period, dual
4652: WHERE cal_qtr_id = l_qtr_id_s
4665:
4666: IF l_is_GL IS NULL THEN
4667: Write2FWKLog('Creating period slice for the last_summ_date quarter - Enterprise Cal - scheduled');
4668:
4669: INSERT INTO PJI_PMV_TIME_DIM_TMP
4670: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4671: SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4672: FROM fii_time_ent_period, dual
4673: WHERE ent_qtr_id = l_qtr_id_s
4674: AND start_date > l_period_start_date_s;
4675:
4676: Write2FWKLog('Creating period slice for the as_of_date quarter - Enterprise Cal - scheduled');
4677:
4678: INSERT INTO PJI_PMV_TIME_DIM_TMP
4679: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4680: SELECT ent_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4681: FROM fii_time_ent_period, dual
4682: WHERE ent_qtr_id = l_qtr_id
4684:
4685: ELSIF l_is_GL IS NOT NULL THEN
4686: Write2FWKLog('Creating period slice for the last_summ_date quarter - Fiscal Cal - scheduled');
4687:
4688: INSERT INTO PJI_PMV_TIME_DIM_TMP
4689: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4690: SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4691: FROM fii_time_cal_period, dual
4692: WHERE cal_qtr_id = l_qtr_id_s
4694: AND start_date > l_period_start_date_s;
4695:
4696: Write2FWKLog('Creating period slice for the as_of_date quarter - Fiscal Cal - scheduled');
4697:
4698: INSERT INTO PJI_PMV_TIME_DIM_TMP
4699: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4700: SELECT cal_period_id, '-1', '-1', 32 , l_amount_type, l_calendar_type_sum
4701: FROM fii_time_cal_period, dual
4702: WHERE cal_qtr_id = l_qtr_id
4719:
4720: IF l_is_GL IS NULL THEN
4721: Write2FWKLog('Creating quarter slice for between the two dates quarters - Enterprise Cal - scheduled');
4722:
4723: INSERT INTO PJI_PMV_TIME_DIM_TMP
4724: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4725: SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4726: FROM fii_time_ent_qtr, dual
4727: WHERE ent_year_id = l_year_id
4730:
4731: ELSIF l_is_GL IS NOT NULL THEN
4732: Write2FWKLog('Creating quarter slice for between the two dates quarters - Fiscal Cal - scheduled');
4733:
4734: INSERT INTO PJI_PMV_TIME_DIM_TMP
4735: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4736: SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4737: FROM fii_time_cal_qtr, dual
4738: WHERE cal_year_id = l_year_id
4751:
4752: IF l_is_GL IS NULL THEN
4753: Write2FWKLog('Creating quarter slice for the last_summ_date year - Enterprise Cal - scheduled');
4754:
4755: INSERT INTO PJI_PMV_TIME_DIM_TMP
4756: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4757: SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4758: FROM fii_time_ent_qtr, dual
4759: WHERE ent_year_id = l_year_id_s
4760: AND start_date > l_qtr_start_date_s;
4761:
4762: Write2FWKLog('Creating quarter slice for the as_of_date year - Enterprise Cal - scheduled');
4763:
4764: INSERT INTO PJI_PMV_TIME_DIM_TMP
4765: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4766: SELECT ent_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4767: FROM fii_time_ent_qtr, dual
4768: WHERE ent_year_id = l_year_id
4771:
4772: ELSIF l_is_GL IS NOT NULL THEN
4773: Write2FWKLog('Creating quarter slice for the last_summ_date year - Fiscal Cal - scheduled');
4774:
4775: INSERT INTO PJI_PMV_TIME_DIM_TMP
4776: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4777: SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4778: FROM fii_time_cal_qtr, dual
4779: WHERE cal_year_id = l_year_id_s
4781: AND start_date > l_qtr_start_date_s;
4782:
4783: Write2FWKLog('Creating quarter slice for the as_of_date year - Fiscal Cal - scheduled');
4784:
4785: INSERT INTO PJI_PMV_TIME_DIM_TMP
4786: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4787: SELECT cal_qtr_id, '-1', '-1', 64 , l_amount_type, l_calendar_type_sum
4788: FROM fii_time_cal_qtr, dual
4789: WHERE cal_year_id = l_year_id
4804:
4805: IF l_is_GL IS NULL THEN
4806: Write2FWKLog('Creating year slice for between the two dates years - Enterprise Cal - scheduled');
4807:
4808: INSERT INTO PJI_PMV_TIME_DIM_TMP
4809: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4810: SELECT ent_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
4811: FROM fii_time_ent_year, dual
4812: WHERE start_date < l_year_start_date
4814:
4815: ELSIF l_is_GL IS NOT NULL THEN
4816: Write2FWKLog('Creating year slice for between the two dates years - Fiscal Cal - scheduled');
4817:
4818: INSERT INTO PJI_PMV_TIME_DIM_TMP
4819: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
4820: SELECT cal_year_id, '-1', '-1', 128 , l_amount_type, l_calendar_type_sum
4821: FROM fii_time_cal_year, dual
4822: WHERE calendar_id = l_calendar_id
4831:
4832: IF p_parse_prior IS NOT NULL THEN
4833:
4834: Write2FWKLog('Parse Prior is set.');
4835: UPDATE PJI_PMV_TIME_DIM_TMP
4836: SET PRIOR_ID =
4837: (CASE period_type
4838: WHEN 128 THEN SUBSTR(LPAD(ID,7,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,7,'0'),4,4)-1)
4839: WHEN 64 THEN SUBSTR(LPAD(ID,8,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,8,'0'),4,4)-1)||SUBSTR(LPAD(ID,8,'0'),8)
4864: l_week_id NUMBER;
4865: l_Week_Name VARCHAR(100);
4866: l_End_Date DATE;
4867: BEGIN
4868: DELETE PJI_PMV_TIME_DIM_TMP;
4869: SELECT
4870: day.week_id
4871: , wek.name
4872: , wek.end_date
4879: report_date = to_date(p_as_of_date,'j') -- As Of Date
4880: AND wek.week_id = day.week_id;
4881:
4882:
4883: INSERT INTO PJI_PMV_TIME_DIM_TMP
4884: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
4885: SELECT report_date_julian
4886: , l_Week_Name, l_Week_Id, 1, 'C' FROM fii_time_day
4887: WHERE week_id = l_Week_Id
4886: , l_Week_Name, l_Week_Id, 1, 'C' FROM fii_time_day
4887: WHERE week_id = l_Week_Id
4888: AND report_date>=to_date(p_as_of_date,'j');
4889:
4890: INSERT INTO PJI_PMV_TIME_DIM_TMP
4891: (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, CALENDAR_TYPE)
4892: SELECT id, name, id, 16, 'C'
4893: FROM (
4894: SELECT week_id id,name name FROM fii_time_week