1 PACKAGE BODY PJI_PMV_ENGINE AS
2 /* $Header: PJIRX01B.pls 120.7.12010000.2 2008/08/08 10:41:56 arbandyo ship $ */
3
4 g_SQL_Error_Msg VARCHAR2(3200);
5 p_PA_DEBUG_MODE VARCHAR2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
6
7
8 /*
9 ** Internal functions...
10 */
11
12 /*
13 ** ----------------------------------------------------------
14 ** Function: Write2FWKLog
15 ** The function writes debug statements to a output table.
16 ** This procedure would be replaced with the actual one later.
17 ** ----------------------------------------------------------
18 */
19 G_Module_Name VARCHAR2(100) := 'Module Name not set.';
20 G_No_Rolling_Weeks NUMBER;
21 Procedure Write2FWKLog( p_Message VARCHAR2
22 , p_Module VARCHAR2 DEFAULT NULL
23 , p_Level NUMBER DEFAULT 1)
24 IS
25 BEGIN
26 IF p_Module IS NOT NULL THEN
27 G_Module_Name:=p_Module;
28 END IF;
29 PJI_UTILS.Write2SSWALOG(p_Message, p_Level, G_Module_Name);
30 COMMIT;
31 END Write2FWKLog;
32
33 /*
34 ** ----------------------------------------------------------
35 ** Function: Convert_AS_OF_DATE
36 ** The function returns hierarchy version id defined in
37 ** in PJI_SYSTEM_SETTINGS
38 ** ----------------------------------------------------------
39 */
40
41 Function Convert_AS_OF_DATE(p_As_Of_Date NUMBER
42 , p_Period_Type VARCHAR2
43 , p_Comparator VARCHAR2) RETURN NUMBER
44 IS
45 l_Calendar_Id NUMBER;
46 BEGIN
47 IF p_PA_DEBUG_MODE = 'Y' THEN
48 Write2FWKLog('Entering Convert_AS_OF_DATE...','Convert_AS_OF_DATE');
49 Write2FWKLog('p_Comparator is '||p_Comparator||', p_Period_Type is '||p_Period_Type||' and value for AS_OF_DATE is '||p_As_Of_Date);
50 END IF;
51
52 IF p_Period_Type LIKE '%PA%' THEN
53 l_Calendar_Id:=G_PA_Calendar_ID;
54 IF p_PA_DEBUG_MODE = 'Y' THEN
55 Write2FWKLog('PA calender is selected.');
56 END IF;
57 ELSIF p_Period_Type LIKE '%CAL%' THEN
58 l_Calendar_Id:=G_GL_Calendar_ID;
59 IF p_PA_DEBUG_MODE = 'Y' THEN
60 Write2FWKLog('GL calender is selected.');
61 END IF;
62 END IF;
63
64 IF l_Calendar_Id IS NULL THEN
65 CASE p_Comparator
66 WHEN 'SEQUENTIAL' THEN
67 IF p_Period_Type LIKE '%YEAR%' THEN
68 RETURN to_char(FII_TIME_API.ent_sd_lyr_end(to_date(p_As_Of_Date,'j')),'j');
69 ELSIF p_Period_Type LIKE '%QTR%' THEN
70 RETURN to_char(FII_TIME_API.ent_sd_pqtr_end(to_date(p_As_Of_Date,'j')),'j');
71 ELSIF p_Period_Type LIKE '%PERIOD%' THEN
72 RETURN to_char(FII_TIME_API.ent_sd_pper_end(to_date(p_As_Of_Date,'j')),'j');
73 ELSIF p_Period_Type LIKE '%WEEK%' THEN
74 RETURN to_char(FII_TIME_API.sd_pwk(to_date(p_As_Of_Date,'j')),'j');
75 END IF;
76 WHEN 'YEARLY' THEN
77 IF p_Period_Type LIKE '%YEAR%' THEN
78 RETURN to_char(FII_TIME_API.ent_sd_lyr_end(to_date(p_As_Of_Date,'j')),'j');
79 ELSIF p_Period_Type LIKE '%QTR%' THEN
80 RETURN to_char(FII_TIME_API.ent_sd_lysqtr_end(to_date(p_As_Of_Date,'j')),'j');
81 ELSIF p_Period_Type LIKE '%PERIOD%' THEN
82 RETURN to_char(FII_TIME_API.ent_sd_lysper_end(to_date(p_As_Of_Date,'j')),'j');
83 ELSIF p_Period_Type LIKE '%WEEK%' THEN
84 RETURN to_char(FII_TIME_API.sd_lyswk(to_date(p_As_Of_Date,'j')),'j');
85 END IF;
86 END CASE;
87 ELSE
88 CASE p_Comparator
89 WHEN 'SEQUENTIAL' THEN
90 IF p_Period_Type LIKE '%YEAR%' THEN
91 RETURN to_char(FII_TIME_API.cal_sd_lyr_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
92 ELSIF p_Period_Type LIKE '%QTR%' THEN
93 RETURN to_char(FII_TIME_API.cal_sd_pqtr_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
94 ELSIF p_Period_Type LIKE '%PERIOD%' THEN
95 RETURN to_char(FII_TIME_API.cal_sd_pper_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
96 END IF;
97 WHEN 'YEARLY' THEN
98 IF p_Period_Type LIKE '%YEAR%' THEN
99 RETURN to_char(FII_TIME_API.cal_sd_lyr_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
100 ELSIF p_Period_Type LIKE '%QTR%' THEN
101 RETURN to_char(FII_TIME_API.cal_sd_lysqtr_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
102 ELSIF p_Period_Type LIKE '%PERIOD%' THEN
103 RETURN to_char(FII_TIME_API.cal_sd_lysper_end(to_date(p_As_Of_Date,'j'), l_Calendar_ID),'j');
104 END IF;
105 END CASE;
106 END IF;
107 IF p_PA_DEBUG_MODE = 'Y' THEN
108 Write2FWKLog('Exiting Convert_AS_OF_DATE...');
109 END IF;
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 RETURN NULL;
113 WHEN OTHERS THEN
114 g_SQL_Error_Msg:=SQLERRM();
115 IF p_PA_DEBUG_MODE = 'Y' THEN
116 Write2FWKLog(g_SQL_Error_Msg, 3);
117 END IF;
118 RAISE;
119 END Convert_AS_OF_DATE;
120
121 /*
122 ** ----------------------------------------------------------
123 ** Function: Get_Hierarchy_Version_ID
124 ** The function returns hierarchy version id defined in
125 ** in PJI_SYSTEM_SETTINGS
126 ** ----------------------------------------------------------
127 */
128 Function Get_Hierarchy_Version_ID
129 RETURN NUMBER IS
130 l_Org_Structure_Version_ID PJI_SYSTEM_SETTINGS.Org_Structure_Version_ID%TYPE;
131 BEGIN
132 IF p_PA_DEBUG_MODE = 'Y' THEN
133 Write2FWKLog('Entering Get_Hierarchy_Version_ID...','Get_Hierarchy_Version_ID');
134 END IF;
135 IF l_Org_Structure_Version_ID IS NULL THEN
136 BEGIN
137 SELECT NVL(org_structure_version_id, -1)
138 INTO l_Org_Structure_Version_ID
139 FROM PJI_SYSTEM_SETTINGS;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 RETURN -1;
143 WHEN OTHERS THEN
144 g_SQL_Error_Msg:=SQLERRM();
145 IF p_PA_DEBUG_MODE = 'Y' THEN
146 Write2FWKLog(g_SQL_Error_Msg, 3);
147 END IF;
148 RAISE;
149 END;
150 END IF;
151 IF p_PA_DEBUG_MODE = 'Y' THEN
152 Write2FWKLog('Exiting Get_Hierarchy_Version_ID...');
153 END IF;
154 RETURN l_Org_Structure_Version_ID;
155 END Get_Hierarchy_Version_ID;
156
157 /*
158 ** ----------------------------------------------------------
159 ** Function: Decode_IDS
160 ** The function returns de-constructs the parameters passed
161 ** by pmv report.
162 ** ----------------------------------------------------------
163 */
164 Function Decode_IDS(p_Buffer VARCHAR2) RETURN SYSTEM.pa_num_tbl_type
165 AS
166 l_Buffer VARCHAR2(150);
167 i NUMBER:=1;
168 j NUMBER:=0;
169 k NUMBER:=1;
170 l_Dim_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
171 BEGIN
172 IF p_PA_DEBUG_MODE = 'Y' THEN
173 Write2FWKLog('Entering Decode_IDS...','Decode_IDS');
174 END IF;
175 j:=INSTR(p_Buffer,',',i);
176 WHILE (j<>0)
177 LOOP
178 l_Buffer:=SUBSTR(p_Buffer,i,j-i);
179 l_Dim_List_Tab.EXTEND;
180 l_Dim_List_Tab(k):=TO_NUMBER(l_Buffer);
181 i:=j+1;
182 j:=INSTR(p_Buffer,',',i);
183 k:=k+1;
184 END LOOP;
185 l_Buffer:=SUBSTR(p_Buffer,i,length(p_Buffer)+1-i);
186 IF l_Buffer IS NOT NULL THEN
187 l_Dim_List_Tab.EXTEND;
188 l_Dim_List_Tab(k):=TO_NUMBER(l_Buffer);
189 END IF;
190 IF p_PA_DEBUG_MODE = 'Y' THEN
191 Write2FWKLog('Exiting Decode_IDS...');
192 END IF;
193 RETURN l_Dim_List_Tab;
194 EXCEPTION
195 WHEN OTHERS THEN
196 g_SQL_Error_Msg:=SQLERRM();
197 IF p_PA_DEBUG_MODE = 'Y' THEN
198 Write2FWKLog(g_SQL_Error_Msg, 3);
199 END IF;
200 RAISE;
201 END Decode_IDS;
202
203 /*
204 ** ----------------------------------------------------------
205 ** Function: Decode_VARS
206 ** The function returns de-constructs the parameters passed
207 ** by pmv report.Diff between Decode_IDS and Decode_VARS is
208 ** in type num and varchar.
209 ** ----------------------------------------------------------
210 */
211
212 Function Decode_VARS(p_Buffer VARCHAR2) RETURN SYSTEM.pa_varchar2_240_tbl_type
213 AS
214 l_Buffer VARCHAR2(150);
215 i NUMBER:=1;
216 j NUMBER:=0;
217 k NUMBER:=1;
218 l_Dim_List_Tab SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
219 BEGIN
220 IF p_PA_DEBUG_MODE = 'Y' THEN
221 Write2FWKLog('Entering Decode_VARS...','Decode_VARS');
222 END IF;
223 j:=INSTR(p_Buffer,',',i);
224 WHILE (j<>0)
225 LOOP
226 l_Buffer:=SUBSTR(p_Buffer,i,j-i);
227 l_Dim_List_Tab.EXTEND;
228 l_Dim_List_Tab(k):= l_Buffer ;
229 i:=j+1;
230 j:=INSTR(p_Buffer,',',i);
231 k:=k+1;
232 END LOOP;
233 l_Buffer:=SUBSTR(p_Buffer,i,length(p_Buffer)+1-i);
234 IF l_Buffer IS NOT NULL THEN
235 l_Dim_List_Tab.EXTEND;
236 l_Dim_List_Tab(k):= l_Buffer;
237 END IF;
238 IF p_PA_DEBUG_MODE = 'Y' THEN
239 Write2FWKLog('Exiting Decode_VARS...');
240 END IF;
241 RETURN l_Dim_List_Tab;
242 EXCEPTION
243 WHEN OTHERS THEN
244 g_SQL_Error_Msg:=SQLERRM();
245 IF p_PA_DEBUG_MODE = 'Y' THEN
246 Write2FWKLog(g_SQL_Error_Msg, 3);
247 END IF;
248 RAISE;
249 END Decode_VARS;
250
251 /*
252 ** Conversion API's Section.
253 ** =========================
254 ** To facilitate the CBO to use the star query transformation
255 ** and to develop simple and scalable code, it was decided
256 ** that we would create set of temporary tables which would
257 ** hold the subset of lookup values based on parameters
258 ** selected in PMV.
259 ** These Convert_* API's would be called from the table
260 ** functions.
261 */
262
263 /*
264 ** ----------------------------------------------------------
265 ** Function: Convert_ViewBY
266 ** The function translates the value of view by passed by pmv
267 ** to short variations understood by all the other convert
268 ** API's.
269 ** Bug# 2589267: This fix is provided for addressing the
270 ** scalability issues with literal strings in the sql
271 ** statements.
272 ** ----------------------------------------------------------
273 */
274 Function Convert_ViewBY(p_View_BY VARCHAR2) RETURN VARCHAR2
275 AS
276 BEGIN
277 IF p_PA_DEBUG_MODE = 'Y' THEN
278 Write2FWKLog('Entering Convert_ViewBY...','Convert_ViewBY');
279 END IF;
280
281 IF p_View_BY LIKE 'TIME%' THEN
282 RETURN 'TM';
283 END IF;
284
285 CASE p_View_BY
286 WHEN 'ORGANIZATION+PJI_ORGANIZATIONS' THEN RETURN 'OG';
287 WHEN 'ORGANIZATION+FII_OPERATING_UNITS' THEN RETURN 'OU';
288 WHEN 'PROJECT WORK TYPE+PJI_UTIL_CATEGORIES' THEN RETURN 'UC';
289 WHEN 'PROJECT WORK TYPE+PJI_WORK_TYPES' THEN RETURN 'WT';
290 WHEN 'JOB+JOB' THEN RETURN 'JB';
291 WHEN 'PROJECT JOB LEVEL+PJI_JOB_LEVELS' THEN RETURN 'JL';
292 WHEN 'PROJECT CLASSIFICATION+CLASS_CODE' THEN RETURN 'CC';
293
294 WHEN 'PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES' THEN RETURN 'EC';
295 WHEN 'PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES' THEN RETURN 'ET' ;
296 WHEN 'PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES' THEN RETURN 'RC' ;
297 WHEN 'PROJECT REVENUE CATEGORY+PJI_EXP_EVT_TYPES' THEN RETURN 'RT' ;
298 ELSE RETURN 'XX';
299 END CASE;
300 IF p_PA_DEBUG_MODE = 'Y' THEN
301 Write2FWKLog('Exiting Convert_ViewBY...');
302 END IF;
303 END Convert_ViewBY;
304
305 /*
306 ** ----------------------------------------------------------
307 ** Function: Convert_Currency_Code
308 ** The function translates the value of currency code passed
309 ** to currency type stored in the PJI fact tables.
310 ** Bug# 2589267: This fix is provided for addressing the
311 ** scalability issues with literal strings in the sql
312 ** statements.
313 ** ----------------------------------------------------------
314 */
315
316 Function Convert_Currency_Code(p_Currency_Code VARCHAR2) RETURN VARCHAR2
317 AS
318 BEGIN
319 IF p_PA_DEBUG_MODE = 'Y' THEN
320 Write2FWKLog('Entering Convert_Currency_Code...','Convert_Currency_Code');
321 END IF;
322 IF p_Currency_Code = 'FII_GLOBAL1' THEN
323 IF p_PA_DEBUG_MODE = 'Y' THEN
324 Write2FWKLog('Global Currency Code is selected.');
325 END IF;
326 RETURN 'G';
327
328 ELSIF p_Currency_Code = 'FII_GLOBAL2' THEN
329 IF p_PA_DEBUG_MODE = 'Y' THEN
330 Write2FWKLog('Second Global Currency Code is selected.');
331 END IF;
332 RETURN 'G2';
333 ELSE
334 IF p_PA_DEBUG_MODE = 'Y' THEN
335 Write2FWKLog('Functional Currency Code is selected.');
336 END IF;
337 RETURN 'F';
338 END IF;
339 IF p_PA_DEBUG_MODE = 'Y' THEN
340 Write2FWKLog('Exiting Convert_Currency_Code...');
341 END IF;
342 END Convert_Currency_Code;
343
344 /*
345 ** ----------------------------------------------------------
346 ** Function: Convert_Currency_Record_Type
347 ** The function translates the value of currency type
348 ** to currency record type.
349 ** ----------------------------------------------------------
350 */
351 Function Convert_Currency_Record_Type(p_Currency_Type VARCHAR2) RETURN NUMBER
352 AS
353 BEGIN
354 IF p_PA_DEBUG_MODE = 'Y' THEN
355 Write2FWKLog('Entering Convert_Currency_Record_Type...','Convert_Currency_Record_Type');
356 END IF;
357 IF p_Currency_Type = 'G' THEN
358 IF p_PA_DEBUG_MODE = 'Y' THEN
359 Write2FWKLog('Global Currency Code is selected.');
360 END IF;
361 RETURN 1;
362
363 ELSIF p_Currency_Type = 'G2' THEN
364 IF p_PA_DEBUG_MODE = 'Y' THEN
365 Write2FWKLog('Second Global Currency Code is selected.');
366 END IF;
367 RETURN 2;
368
369 ELSIF p_Currency_Type = 'F' THEN
370 IF p_PA_DEBUG_MODE = 'Y' THEN
371 Write2FWKLog('Functional Currency Code is selected.');
372 END IF;
373 RETURN 4;
374
375 ELSIF p_Currency_Type = 'P' THEN
376 IF p_PA_DEBUG_MODE = 'Y' THEN
377 Write2FWKLog('Project Currency Code is selected.');
378 END IF;
379 RETURN 8;
380
381 ELSE
382 IF p_PA_DEBUG_MODE = 'Y' THEN
383 Write2FWKLog('Global Currency Code will be used by default.');
384 END IF;
385 RETURN 1;
386 END IF;
387 IF p_PA_DEBUG_MODE = 'Y' THEN
388 Write2FWKLog('Exiting Convert_Currency_Record_Type...');
389 END IF;
390 END Convert_Currency_Record_Type;
391
392
393 /*
394 ** ----------------------------------------------------------
395 ** Function: Convert_Classification
396 ** The function inserts all the valid class codes specified in
397 ** the pmv report into a session specific temporary table.
398 ** The function return 'Y' if the lower level fact (Class)
399 ** needs to be joined to.
400 ** Bug# 2491237: For ensuring that project types are always
401 ** secured by Operating Unit. It is mandatory that all programs
402 ** calling the convert api should first place a call to the
403 ** Convert_Operating_Unit API before calling this API.
404 ** ----------------------------------------------------------
405 */
406
407 Function Convert_Classification(p_Classification_ID VARCHAR2 DEFAULT NULL
408 , p_Class_Code_IDS VARCHAR2 DEFAULT NULL
409 , p_View_BY VARCHAR2) RETURN VARCHAR2
410 AS
411 l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
412 l_Parse_Status VARCHAR2(1);
413 BEGIN
414 IF p_PA_DEBUG_MODE = 'Y' THEN
415 Write2FWKLog('Entering Convert_Classification...','Convert_Classification');
416 END IF;
417
418 DELETE PJI_PMV_CLS_DIM_TMP;
419 IF p_Classification_ID IS NOT NULL THEN
420 IF p_PA_DEBUG_MODE = 'Y' THEN
421 Write2FWKLog('Classification IS NOT NULL...');
422 END IF;
423 l_Parse_Status:='Y';
424 IF p_Class_Code_IDS IS NOT NULL THEN
425 l_Dimension_List_Tab:=Decode_IDS(p_Class_Code_IDS);
426 ELSIF p_Classification_ID IS NOT NULL THEN
427 l_Dimension_List_Tab := NULL;
428 END IF;
429 IF l_Dimension_List_Tab IS NULL THEN
430 IF p_Classification_ID = '$PROJECT_TYPE$ALL' THEN
431 IF p_PA_DEBUG_MODE = 'Y' THEN
432 Write2FWKLog('All Project Types are selected.');
433 END IF;
434 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
435 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
436 FROM PJI_CLASS_CODES CLS
437 , PA_PROJECT_TYPES_ALL PJT
438 , PJI_PMV_ORG_DIM_TMP ORG
439 WHERE PJT.ORG_ID = ORG.ID
440 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
441 AND CLS.RECORD_TYPE = 'T';
442 ELSIF p_Classification_ID LIKE '$PROJECT_TYPE$%' THEN
443 IF p_PA_DEBUG_MODE = 'Y' THEN
444 Write2FWKLog('All '||p_Classification_ID||' Project Type is selected.');
445 END IF;
446 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
447 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
448 FROM PJI_CLASS_CODES CLS
449 , PA_PROJECT_TYPES_ALL PJT
450 , PJI_PMV_ORG_DIM_TMP ORG
451 WHERE PJT.ORG_ID = ORG.ID
452 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
453 AND CLS.CLASS_CATEGORY = p_Classification_ID;
454 ELSE
455 IF p_PA_DEBUG_MODE = 'Y' THEN
456 Write2FWKLog('All class codes for '||p_Classification_ID||' Project type or Classification are selected.');
457 END IF;
458 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
459 SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
460 FROM PJI_CLASS_CODES CLS
461 WHERE CLS.CLASS_CATEGORY = p_Classification_ID;
462 END IF;
463 ELSIF l_Dimension_List_Tab IS NOT NULL THEN
464 IF p_PA_DEBUG_MODE = 'Y' THEN
465 Write2FWKLog('Selected list of Project type or Classification '||p_Classification_ID||' are selected.');
466 END IF;
467 IF p_Classification_ID = '$PROJECT_TYPE$ALL' THEN
468 /*
469 ** Following portion of the code is commented
470 ** because of a bug in db (2596577).
471 */
472 /*
473 FORALL i IN 1..l_Dimension_List_Tab.Last
474 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
475 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
476 FROM PJI_CLASS_CODES CLS
477 , PA_PROJECT_TYPES_ALL PJT
478 , PJI_PMV_ORG_DIM_TMP ORG
479 WHERE PJT.ORG_ID = ORG.ID
480 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
481 AND CLS.RECORD_TYPE = 'T'
482 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
483 */
484 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
485 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
486 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
487 FROM PJI_CLASS_CODES CLS
488 , PA_PROJECT_TYPES_ALL PJT
489 , PJI_PMV_ORG_DIM_TMP ORG
490 WHERE PJT.ORG_ID = ORG.ID
491 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
492 AND CLS.RECORD_TYPE = 'T'
493 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
494 END LOOP;
495 ELSIF p_Classification_ID LIKE '$PROJECT_TYPE$%' THEN
496 /*
497 ** Following portion of the code is commented
498 ** because of a bug in db (2596577).
499 */
500 /*
501 FORALL i IN 1..l_Dimension_List_Tab.Last
502 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
503 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
504 FROM PJI_CLASS_CODES CLS
505 , PA_PROJECT_TYPES_ALL PJT
506 , PJI_PMV_ORG_DIM_TMP ORG
507 WHERE PJT.ORG_ID = ORG.ID
508 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
509 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
510 */
511 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
512 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
513 SELECT DISTINCT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
514 FROM PJI_CLASS_CODES CLS
515 , PA_PROJECT_TYPES_ALL PJT
516 , PJI_PMV_ORG_DIM_TMP ORG
517 WHERE PJT.ORG_ID = ORG.ID
518 AND CLS.CLASS_CODE = PJT.PROJECT_TYPE
519 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
520 END LOOP;
521 ELSE
522 /*
523 ** Following portion of the code is commented
524 ** because of a bug in db (2596577).
525 */
526 /*
527 FORALL i IN 1..l_Dimension_List_Tab.Last
528 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
529 SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
530 FROM PJI_CLASS_CODES CLS
531 WHERE CLS.CLASS_CATEGORY = p_Classification_ID
532 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
533 */
534 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
535 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
536 SELECT CLS.CLASS_ID, DECODE(p_View_BY,'CC',CLS.CLASS_CODE,'-1')
537 FROM PJI_CLASS_CODES CLS
538 WHERE CLS.CLASS_CATEGORY = p_Classification_ID
539 AND CLS.CLASS_ID = l_Dimension_List_Tab(i);
540 END LOOP;
541 END IF;
542 END IF;
543 ELSIF p_View_BY = 'CC' AND p_Classification_ID IS NULL THEN
544 IF p_PA_DEBUG_MODE = 'Y' THEN
545 Write2FWKLog('A class code was selected without corresponding category.');
546 END IF;
547 l_Parse_Status:='Y';
548 INSERT INTO PJI_PMV_CLS_DIM_TMP (ID, NAME)
549 SELECT -1, '-1'
550 FROM SYS.DUAL; -- REMOVE THIS COMMENT WHEN THE ISSUE WITH DUAL IS RESOLVED.
551 ELSE
552 IF p_PA_DEBUG_MODE = 'Y' THEN
553 Write2FWKLog('No need to go the class fact.');
554 END IF;
555 l_Parse_Status:='N';
556 END IF;
557 IF p_PA_DEBUG_MODE = 'Y' THEN
558 Write2FWKLog('Exiting Convert_Classification...');
559 END IF;
560 RETURN l_Parse_Status;
561 EXCEPTION
562 WHEN OTHERS THEN
563 g_SQL_Error_Msg:=SQLERRM();
564 IF p_PA_DEBUG_MODE = 'Y' THEN
565 Write2FWKLog(g_SQL_Error_Msg, 3);
566 END IF;
567 RAISE;
568 END Convert_Classification;
569
570
571 /*
572 ** ----------------------------------------------------------
573 ** Function: Convert_Event_Revenue_Type
574 ** The function inserts all the valid expenditure/event types specified in
575 ** the pmv report into a session specific temporary table.
576 ** The function return 'Y' if the lower level fact (expenditure/event types)
577 ** needs to be joined to.
578 ** ----------------------------------------------------------
579 */
580 Function Convert_Event_Revenue_Type(p_Revenue_Category VARCHAR2 DEFAULT NULL
581 , p_Revenue_Type_IDS VARCHAR2 DEFAULT NULL
582 , p_View_BY VARCHAR2) RETURN VARCHAR2
583 AS
584 l_RCate_Dimension_List_Tab SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
585 l_RType_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
586 l_RC_Parsed_Flag VARCHAR2(1);
587 l_RT_Parsed_Flag VARCHAR2(1):='N';
588 BEGIN
589 IF p_PA_DEBUG_MODE = 'Y' THEN
590 Write2FWKLog('Entering Convert_Event_Revenue_Type...','Convert_Event_Revenue_Type');
591 END IF;
592
593 DELETE PJI_PMV_EC_RC_DIM_TMP where record_type = 'RC';
594 DELETE PJI_PMV_ET_RT_DIM_TMP where record_type = 'RT';
595
596 IF p_Revenue_Category IS NOT NULL THEN
597 l_RCate_Dimension_List_Tab:=Decode_VARS(p_Revenue_Category);
598 ELSE
599 l_RCate_Dimension_List_Tab := NULL;
600 END IF;
601
602
603 IF p_Revenue_Type_IDS IS NOT NULL THEN
604 l_RType_Dimension_List_Tab:=Decode_IDS(p_Revenue_Type_IDS);
605 ELSE
606 l_RType_Dimension_List_Tab := NULL;
607 END IF;
608
609 IF l_RCate_Dimension_List_Tab IS NULL AND p_View_BY = 'RC' THEN
610 IF p_PA_DEBUG_MODE = 'Y' THEN
611 Write2FWKLog('View BY RC and RC is not specified.');
612 Write2FWKLog('step6','step6');
613 END IF;
614 INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
615 SELECT ID, VALUE,'RC'
616 FROM pji_revenue_categories_v ;
617 l_RC_Parsed_Flag:='Y';
618 ELSIF l_RCate_Dimension_List_Tab IS NOT NULL THEN
619 IF p_PA_DEBUG_MODE = 'Y' THEN
620 Write2FWKLog('RC is specified.');
621 END IF;
622 FOR i IN 1..l_RCate_Dimension_List_Tab.Last LOOP
623 INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
624 SELECT ID, DECODE(p_View_By, 'RC', VALUE, '-1'),'RC'
625 FROM pji_revenue_categories_v
626 WHERE ID = l_RCate_Dimension_List_Tab(i);
627 END LOOP;
628 l_RC_Parsed_Flag:='Y';
629 END IF;
630
631
632 IF l_RType_Dimension_List_Tab IS NULL THEN
633 IF p_PA_DEBUG_MODE = 'Y' THEN
634 Write2FWKLog('RT is not specified.');
635 END IF;
636 IF l_RC_Parsed_Flag IS NULL AND p_View_BY = 'RT' THEN
637 IF p_PA_DEBUG_MODE = 'Y' THEN
638 Write2FWKLog('Creating RT rows, View BY RT and RC is not specified.');
639 END IF;
640
641 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
642 SELECT ID, VALUE,'RT'
643 FROM pji_exp_evt_types_v ;
644 l_RT_Parsed_Flag:='Y';
645 ELSIF l_RC_Parsed_Flag IS NOT NULL THEN
646 IF p_PA_DEBUG_MODE = 'Y' THEN
647 Write2FWKLog('Creating RT rows, RC is specified.');
648 END IF;
649 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
650 SELECT rtyp.id, DECODE(p_View_BY,'RC',usrx.name,'RT',rtyp.value,'-1'),'RT'
651 FROM pji_exp_evt_types_v rtyp
652 , PJI_PMV_EC_RC_DIM_TMP usrx
653 WHERE rtyp.revenue_category_code = usrx.id
654 and usrx.record_type = 'RC';
655 l_RT_Parsed_Flag:='Y';
656 END IF;
657 ELSIF l_RType_Dimension_List_Tab IS NOT NULL THEN
658 IF p_PA_DEBUG_MODE = 'Y' THEN
659 Write2FWKLog('RT is specified.');
660 END IF;
661 IF l_RC_Parsed_Flag IS NULL THEN
662 IF p_PA_DEBUG_MODE = 'Y' THEN
663 Write2FWKLog('Creating RT rows, RC is not specified.');
664 END IF;
665 FOR i IN 1..l_RType_Dimension_List_Tab.Last LOOP
666 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
667 SELECT id, DECODE(p_View_BY,'RT',value,'-1'),'RT'
668 FROM pji_exp_evt_types_v
669 WHERE
670 id = l_RType_Dimension_List_Tab(i);
671 END LOOP;
672 l_RT_Parsed_Flag:='Y';
673 ELSIF l_RC_Parsed_Flag IS NOT NULL THEN
674 IF p_PA_DEBUG_MODE = 'Y' THEN
675 Write2FWKLog('Creating RT rows, RC is specified.');
676 END IF;
677 FOR i IN 1..l_RType_Dimension_List_Tab.Last LOOP
678 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
679 SELECT rtyp.id, DECODE(p_View_BY,'RC',usrx.name,'RT',rtyp.value,'-1'),'RT'
680 FROM pji_exp_evt_types_v rtyp
681 , PJI_PMV_EC_RC_DIM_TMP usrx
682 WHERE rtyp.id = l_RType_Dimension_List_Tab(i)
683 AND rtyp.revenue_category_code = usrx.id
684 AND usrx.record_type = 'RC';
685 END LOOP;
686 l_RT_Parsed_Flag:='Y';
687 END IF;
688 END IF;
689 IF p_PA_DEBUG_MODE = 'Y' THEN
690 Write2FWKLog('Exiting Convert_Event_Revenue_Type...');
691 END IF;
692 RETURN l_RT_Parsed_Flag;
693 EXCEPTION
694 WHEN OTHERS THEN
695 g_SQL_Error_Msg:=SQLERRM();
696 IF p_PA_DEBUG_MODE = 'Y' THEN
697 Write2FWKLog(g_SQL_Error_Msg, 3);
698 END IF;
699 RAISE;
700 END Convert_Event_Revenue_Type;
701
702 Function Convert_Work_Type(p_Work_Type_IDS VARCHAR2 DEFAULT NULL
703 , p_View_BY VARCHAR2) RETURN VARCHAR2
704 AS
705 l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
706 l_Parse_Status VARCHAR2(1);
707 BEGIN
708 IF p_PA_DEBUG_MODE = 'Y' THEN
709 Write2FWKLog('Entering Convert_Work_Type...','Convert_Work_Type');
710 END IF;
711
712 DELETE PJI_PMV_WT_DIM_TMP;
713
714 IF p_View_BY NOT IN ('WT') AND p_Work_Type_IDS IS NULL THEN
715 l_Parse_Status := 'N';
716 ELSE
717 l_Parse_Status:='Y';
718
719 IF p_Work_Type_IDS IS NOT NULL THEN
720 l_Dimension_List_Tab:=Decode_IDS(p_Work_Type_IDS);
721 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
722 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
723 SELECT WT.ID, DECODE(p_View_BY,'WT',WT.VALUE,'-1')
724 FROM PJI_WORK_TYPES_V WT
725 WHERE WT.ID = l_Dimension_List_Tab(i);
726 END LOOP;
727 ELSE
728 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
729 SELECT WT.ID, WT.VALUE
730 FROM PJI_WORK_TYPES_V WT ;
731 END IF;
732
733 END IF;
734 RETURN l_Parse_Status;
735 EXCEPTION
736 WHEN OTHERS THEN
737 g_SQL_Error_Msg:=SQLERRM();
738 IF p_PA_DEBUG_MODE = 'Y' THEN
739 Write2FWKLog(g_SQL_Error_Msg, 3);
740 END IF;
741 RAISE;
742
743 END Convert_Work_Type;
744
745 /*
746 ** Function: Convert_Expenditure_Type
747 ** The function inserts all the valid expenditure types specified in
748 ** the pmv report into a session specific temporary table.
749 ** The function return 'Y' if the lower level fact (expenditure type)
750 ** needs to be joined to.
751 ** ----------------------------------------------------------
752 */
753 Function Convert_Expenditure_Type(p_Expenditure_Category VARCHAR2 DEFAULT NULL
754 , p_Expenditure_Type_IDS VARCHAR2 DEFAULT NULL
755 , p_View_BY VARCHAR2) RETURN VARCHAR2
756 AS
757 l_ECate_Dimension_List_Tab SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
758 l_EType_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
759 l_EC_Parsed_Flag VARCHAR2(1);
760 l_ET_Parsed_Flag VARCHAR2(1):='N';
761 BEGIN
762 IF p_PA_DEBUG_MODE = 'Y' THEN
763 Write2FWKLog('Entering Convert_Expenditure_Category...','Convert_Expenditure_Category');
764 END IF;
765
766 DELETE PJI_PMV_ET_RT_DIM_TMP where record_type = 'ET';
767 DELETE PJI_PMV_EC_RC_DIM_TMP where record_type = 'EC';
768
769 IF p_Expenditure_Category IS NOT NULL THEN
770 l_ECate_Dimension_List_Tab:=Decode_VARS(p_Expenditure_Category);
771 ELSE
772 l_ECate_Dimension_List_Tab := NULL;
773 END IF;
774
775
776 IF p_Expenditure_Type_IDS IS NOT NULL THEN
777 l_EType_Dimension_List_Tab:=Decode_IDS(p_Expenditure_Type_IDS);
778 ELSE
779 l_EType_Dimension_List_Tab := NULL;
780 END IF;
781
782 IF l_ECate_Dimension_List_Tab IS NULL AND p_View_BY = 'EC' THEN
783 IF p_PA_DEBUG_MODE = 'Y' THEN
784 Write2FWKLog('View BY EC and EC is not specified.');
785 END IF;
786 INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
787 SELECT EXPENDITURE_CATEGORY, EXPENDITURE_CATEGORY,'EC'
788 FROM PA_EXPENDITURE_CATEGORIES ;
789 l_EC_Parsed_Flag:='Y';
790 ELSIF l_ECate_Dimension_List_Tab IS NOT NULL THEN
791 IF p_PA_DEBUG_MODE = 'Y' THEN
792 Write2FWKLog('EC is specified.');
793 END IF;
794 FOR i IN 1..l_ECate_Dimension_List_Tab.Last LOOP
795 INSERT INTO PJI_PMV_EC_RC_DIM_TMP (ID, NAME,RECORD_TYPE)
796 SELECT EXPENDITURE_CATEGORY, DECODE(p_View_By, 'EC', EXPENDITURE_CATEGORY, '-1'),'EC'
797 FROM PA_EXPENDITURE_CATEGORIES
798 WHERE EXPENDITURE_CATEGORY_ID = to_number(l_ECate_Dimension_List_Tab(i));
799 /* Changed the where clause to expenditure_category_id as l_ECate_Dimension_List_Tab
800 stores expenditure_category_id in varchar form. Added for bug 6836176 */
801 END LOOP;
802 l_EC_Parsed_Flag:='Y';
803 END IF;
804
805
806 IF l_EType_Dimension_List_Tab IS NULL THEN
807 IF p_PA_DEBUG_MODE = 'Y' THEN
808 Write2FWKLog('ET is not specified.');
809 END IF;
810 IF l_EC_Parsed_Flag IS NULL AND p_View_BY = 'ET' THEN
811 IF p_PA_DEBUG_MODE = 'Y' THEN
812 Write2FWKLog('Creating ET rows, View BY ET and EC is not specified.');
813 END IF;
814
815 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
816 SELECT EXPENDITURE_TYPE_ID, EXPENDITURE_TYPE,'ET'
817 FROM PA_EXPENDITURE_TYPES ;
818 l_ET_Parsed_Flag:='Y';
819 ELSIF l_EC_Parsed_Flag IS NOT NULL THEN
820 IF p_PA_DEBUG_MODE = 'Y' THEN
821 Write2FWKLog('Creating ET rows, EC is specified.');
822 END IF;
823 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
824 SELECT etyp.expenditure_type_id,
825
826 DECODE(p_View_BY,'EC',usrx.name,'ET',etyp.expenditure_type,'-1'),'ET'
827 FROM pa_expenditure_types etyp
828 , PJI_PMV_EC_RC_DIM_TMP usrx
829 WHERE etyp.expenditure_category = usrx.id
830 and usrx.record_type = 'EC';
831 l_ET_Parsed_Flag:='Y';
832 END IF;
833 ELSIF l_EType_Dimension_List_Tab IS NOT NULL THEN
834 IF p_PA_DEBUG_MODE = 'Y' THEN
835 Write2FWKLog('ET is specified.');
836 END IF;
837 IF l_EC_Parsed_Flag IS NULL THEN
838 IF p_PA_DEBUG_MODE = 'Y' THEN
839 Write2FWKLog('Creating ET rows, EC is not specified.');
840 END IF;
841 FOR i IN 1..l_EType_Dimension_List_Tab.Last LOOP
842 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
843 SELECT expenditure_type_id, DECODE(p_View_BY,'ET',expenditure_type,'-1'),'ET'
844 FROM pa_expenditure_types
845 WHERE
846 expenditure_type_id = l_EType_Dimension_List_Tab(i);
847 END LOOP;
848 l_ET_Parsed_Flag:='Y';
849 ELSIF l_EC_Parsed_Flag IS NOT NULL THEN
850 IF p_PA_DEBUG_MODE = 'Y' THEN
851 Write2FWKLog('Creating ET rows, EC is specified.');
852 END IF;
853 FOR i IN 1..l_EType_Dimension_List_Tab.Last LOOP
854 INSERT INTO PJI_PMV_ET_RT_DIM_TMP (ID, NAME,RECORD_TYPE)
855 SELECT etyp.expenditure_type_id,
856
857 DECODE(p_View_BY,'EC',usrx.name,'ET',etyp.expenditure_type,'-1'),'ET'
858 FROM pa_expenditure_types etyp
859 , PJI_PMV_EC_RC_DIM_TMP usrx
860 WHERE etyp.expenditure_type_id = l_EType_Dimension_List_Tab(i)
861 AND etyp.expenditure_category = usrx.id
862 AND usrx.record_type = 'EC';
863 END LOOP;
864 l_ET_Parsed_Flag:='Y';
865 END IF;
866 END IF;
867 IF p_PA_DEBUG_MODE = 'Y' THEN
868 Write2FWKLog('Exiting Convert_Expenditure_Type...');
869 END IF;
870 RETURN l_ET_Parsed_Flag;
871 EXCEPTION
872 WHEN OTHERS THEN
873 g_SQL_Error_Msg:=SQLERRM();
874 IF p_PA_DEBUG_MODE = 'Y' THEN
875 Write2FWKLog(g_SQL_Error_Msg, 3);
876 END IF;
877 RAISE;
878 END Convert_Expenditure_Type;
879
880 /*
881 ** ----------------------------------------------------------
882 ** Function: Convert_Util_Category
883 ** The function inserts all the valid work types specified in
884 ** the pmv report into a session specific temporary table.
885 ** The function return 'Y' if the lower level fact (work type)
886 ** needs to be joined to.
887 ** ----------------------------------------------------------
888 */
889 Function Convert_Util_Category(p_Work_Type_IDS VARCHAR2 DEFAULT NULL
890 , p_Util_Category_IDS VARCHAR2 DEFAULT NULL
891 , p_View_BY VARCHAR2) RETURN VARCHAR2
892 AS
893 l_Util_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
894 l_WType_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
895 l_UC_Parsed_Flag VARCHAR2(1);
896 l_WT_Parsed_Flag VARCHAR2(1):='N';
897 BEGIN
898 IF p_PA_DEBUG_MODE = 'Y' THEN
899 Write2FWKLog('Entering Convert_Util_Category...','Convert_Util_Category');
900 END IF;
901
902 DELETE PJI_PMV_WT_DIM_TMP;
903 DELETE PJI_PMV_UC_DIM_TMP;
904
905 IF p_Util_Category_IDS IS NOT NULL THEN
906 l_Util_Dimension_List_Tab:=Decode_IDS(p_Util_Category_IDS);
907 ELSE
908 l_Util_Dimension_List_Tab := NULL;
909 END IF;
910
911
912 IF p_Work_Type_IDS IS NOT NULL THEN
913 l_WType_Dimension_List_Tab:=Decode_IDS(p_Work_Type_IDS);
914 ELSE
915 l_WType_Dimension_List_Tab := NULL;
916 END IF;
917
918 IF l_Util_Dimension_List_Tab IS NULL AND p_View_BY = 'UC' THEN
919 IF p_PA_DEBUG_MODE = 'Y' THEN
920 Write2FWKLog('View BY UC and UC is not specified.');
921 END IF;
922 INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
923 SELECT util_category_id, name
924 FROM pa_util_categories_tl
925 WHERE
926 LANGUAGE = USERENV('LANG');
927 l_UC_Parsed_Flag:='Y';
928 ELSIF l_Util_Dimension_List_Tab IS NOT NULL THEN
929 IF p_PA_DEBUG_MODE = 'Y' THEN
930 Write2FWKLog('UC is specified.');
931 END IF;
932 /*
933 ** Following portion of the code is commented
934 ** because of a bug in db (2596577).
935 */
936 /*
937 FORALL i IN 1..l_Util_Dimension_List_Tab.Last
938 INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
939 SELECT util_category_id, DECODE(p_View_By, 'UC', name , '-1')
940 FROM pa_util_categories_tl
941 WHERE
942 LANGUAGE = USERENV('LANG')
943 AND util_category_id = l_Util_Dimension_List_Tab(i);
944 */
945 FOR i IN 1..l_Util_Dimension_List_Tab.Last LOOP
946 INSERT INTO PJI_PMV_UC_DIM_TMP (ID, NAME)
947 SELECT util_category_id, DECODE(p_View_By, 'UC', name , '-1')
948 FROM pa_util_categories_tl
949 WHERE
950 LANGUAGE = USERENV('LANG')
951 AND util_category_id = l_Util_Dimension_List_Tab(i);
952 END LOOP;
953 l_UC_Parsed_Flag:='Y';
954 END IF;
955
956
957 IF l_WType_Dimension_List_Tab IS NULL THEN
958 IF p_PA_DEBUG_MODE = 'Y' THEN
959 Write2FWKLog('WT is not specified.');
960 END IF;
961 IF l_UC_Parsed_Flag IS NULL AND p_View_BY = 'WT' THEN
962 IF p_PA_DEBUG_MODE = 'Y' THEN
963 Write2FWKLog('Creating WT rows, View BY WT and UC is not specified.');
964 END IF;
965 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
966 SELECT work_type_id, name
967 FROM pa_work_types_tl
968 WHERE
969 LANGUAGE = USERENV('LANG');
970 l_WT_Parsed_Flag:='Y';
971 ELSIF l_UC_Parsed_Flag IS NOT NULL THEN
972 IF p_PA_DEBUG_MODE = 'Y' THEN
973 Write2FWKLog('Creating WT rows, UC is specified.');
974 END IF;
975 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
976 SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
977 FROM pa_work_types_tl orig_tl
978 , pa_work_types_b orig
979 , pji_pmv_uc_dim_tmp usrx
980 WHERE
981 LANGUAGE = USERENV('LANG')
982 AND orig.work_type_id = orig_tl.work_type_id
983 AND orig.org_util_category_id = usrx.id;
984 l_WT_Parsed_Flag:='Y';
985 END IF;
986 ELSIF l_WType_Dimension_List_Tab IS NOT NULL THEN
987 IF p_PA_DEBUG_MODE = 'Y' THEN
988 Write2FWKLog('WT is specified.');
989 END IF;
990 IF l_UC_Parsed_Flag IS NULL THEN
991 IF p_PA_DEBUG_MODE = 'Y' THEN
992 Write2FWKLog('Creating WT rows, UC is not specified.');
993 END IF;
994 /*
995 ** Following portion of the code is commented
996 ** because of a bug in db (2596577).
997 */
998 /*
999 FORALL i IN 1..l_WType_Dimension_List_Tab.Last
1000 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
1001 SELECT work_type_id, DECODE(p_View_BY,'WT',name,'-1')
1002 FROM pa_work_types_tl
1003 WHERE
1004 LANGUAGE = USERENV('LANG')
1005 AND work_type_id = l_WType_Dimension_List_Tab(i);
1006 */
1007 FOR i IN 1..l_WType_Dimension_List_Tab.Last LOOP
1008 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
1009 SELECT work_type_id, DECODE(p_View_BY,'WT',name,'-1')
1010 FROM pa_work_types_tl
1011 WHERE
1012 LANGUAGE = USERENV('LANG')
1013 AND work_type_id = l_WType_Dimension_List_Tab(i);
1014 END LOOP;
1015 l_WT_Parsed_Flag:='Y';
1016 ELSIF l_UC_Parsed_Flag IS NOT NULL THEN
1017 IF p_PA_DEBUG_MODE = 'Y' THEN
1018 Write2FWKLog('Creating WT rows, UC is specified.');
1019 END IF;
1020 /*
1021 ** Following portion of the code is commented
1022 ** because of a bug in db (2596577).
1023 */
1024 /*
1025 FORALL i IN 1..l_WType_Dimension_List_Tab.Last
1026 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
1027 SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
1028 FROM pa_work_types_tl orig_tl
1029 , pa_work_types_b orig
1030 , pji_pmv_uc_dim_tmp usrx
1031 WHERE
1032 LANGUAGE = USERENV('LANG')
1033 AND orig.work_type_id = l_WType_Dimension_List_Tab(i)
1034 AND orig.work_type_id = orig_tl.work_type_id
1035 AND orig.org_util_category_id = usrx.id;
1036 */
1037 FOR i IN 1..l_WType_Dimension_List_Tab.Last LOOP
1038 INSERT INTO PJI_PMV_WT_DIM_TMP (ID, NAME)
1039 SELECT orig.work_type_id, DECODE(p_View_BY,'UC',usrx.name,'WT',orig_tl.name,'-1')
1040 FROM pa_work_types_tl orig_tl
1041 , pa_work_types_b orig
1042 , pji_pmv_uc_dim_tmp usrx
1043 WHERE
1044 LANGUAGE = USERENV('LANG')
1045 AND orig.work_type_id = l_WType_Dimension_List_Tab(i)
1046 AND orig.work_type_id = orig_tl.work_type_id
1047 AND orig.org_util_category_id = usrx.id;
1048 END LOOP;
1049 l_WT_Parsed_Flag:='Y';
1050 END IF;
1051 END IF;
1052 IF p_PA_DEBUG_MODE = 'Y' THEN
1053 Write2FWKLog('Exiting Convert_Util_Category...');
1054 END IF;
1055 RETURN l_WT_Parsed_Flag;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 g_SQL_Error_Msg:=SQLERRM();
1059 IF p_PA_DEBUG_MODE = 'Y' THEN
1060 Write2FWKLog(g_SQL_Error_Msg, 3);
1061 END IF;
1062 RAISE;
1063 END Convert_Util_Category;
1064
1065 /*
1066 ** ----------------------------------------------------------
1067 ** Function: Convert_Job_Level
1068 ** The function inserts all the valid Job Levels specified in
1069 ** the pmv report into a session specific temporary table.
1070 ** The function return 'Y' if the lower level fact (job)
1071 ** needs to be joined to.
1072 ** ----------------------------------------------------------
1073 */
1074
1075 Function Convert_Job_Level(p_Job_IDS VARCHAR2 DEFAULT NULL
1076 , p_Job_Level_IDS VARCHAR2 DEFAULT NULL
1077 , p_View_BY VARCHAR2) RETURN VARCHAR2
1078 AS
1079 l_Job_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1080 l_JLevel_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1081 l_JL_Parsed_Flag VARCHAR2(1);
1082 l_JB_Parsed_Flag VARCHAR2(1):='N';
1083 BEGIN
1084 IF p_PA_DEBUG_MODE = 'Y' THEN
1085 Write2FWKLog('Entering Convert_Job_Level...','Convert_Job_Level');
1086 END IF;
1087
1088 DELETE PJI_PMV_JB_DIM_TMP;
1089 DELETE PJI_PMV_JL_DIM_TMP;
1090
1091 IF p_Job_Level_IDS IS NOT NULL THEN
1092 l_JLevel_Dimension_List_Tab:=Decode_IDS(p_Job_Level_IDS);
1093 ELSE
1094 l_JLevel_Dimension_List_Tab := NULL;
1095 END IF;
1096
1097 IF p_Job_IDS IS NOT NULL THEN
1098 l_Job_Dimension_List_Tab:=Decode_IDS(p_Job_IDS);
1099 ELSE
1100 l_Job_Dimension_List_Tab := NULL;
1101 END IF;
1102
1103 IF l_JLevel_Dimension_List_Tab IS NULL AND p_View_BY = 'JL' THEN
1104 IF p_PA_DEBUG_MODE = 'Y' THEN
1105 Write2FWKLog('View BY JL and JL is not specified.');
1106 END IF;
1107 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1108 SELECT ID, VALUE
1109 FROM PJI_JOB_LEVELS_V;
1110 l_JL_Parsed_Flag:='Y';
1111 ELSIF l_JLevel_Dimension_List_Tab IS NOT NULL THEN
1112 IF p_PA_DEBUG_MODE = 'Y' THEN
1113 Write2FWKLog('JL is specified.');
1114 END IF;
1115 /*
1116 ** Following portion of the code is commented
1117 ** because of a bug in db (2596577).
1118 */
1119 /*
1120 FORALL i IN 1..l_JLevel_Dimension_List_Tab.Last
1121 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1122 SELECT ID,DECODE(p_View_By, 'JL', VALUE , '-1')
1123 FROM PJI_JOB_LEVELS_V
1124 WHERE ID = l_JLevel_Dimension_List_Tab(i);
1125 */
1126 FOR i IN 1..l_JLevel_Dimension_List_Tab.Last LOOP
1127 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1128 SELECT ID,DECODE(p_View_By, 'JL', VALUE , '-1')
1129 FROM PJI_JOB_LEVELS_V
1130 WHERE ID = l_JLevel_Dimension_List_Tab(i);
1131 END LOOP;
1132 l_JL_Parsed_Flag:='Y';
1133 END IF;
1134
1135
1136 IF l_Job_Dimension_List_Tab IS NULL THEN
1137 IF p_PA_DEBUG_MODE = 'Y' THEN
1138 Write2FWKLog('View BY JB is not specified.');
1139 END IF;
1140 IF l_JL_Parsed_Flag IS NULL AND p_View_BY = 'JB' THEN
1141 IF p_PA_DEBUG_MODE = 'Y' THEN
1142 Write2FWKLog('Creating JB rows, View BY JB and JL is not specified.');
1143 END IF;
1144 INSERT INTO PJI_PMV_JB_DIM_TMP (ID, NAME)
1145 SELECT ID, VALUE
1146 FROM PJI_JOBS_V;
1147 l_JB_Parsed_Flag:='Y';
1148 ELSIF l_JL_Parsed_Flag IS NOT NULL THEN
1149 IF p_PA_DEBUG_MODE = 'Y' THEN
1150 Write2FWKLog('Creating JB rows, JL is specified.');
1151 END IF;
1152 INSERT INTO PJI_PMV_JB_DIM_TMP (ID, NAME)
1153 SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
1154 FROM PJI_JOBS_V ORIG
1155 , PJI_PMV_JL_DIM_TMP USRX
1156 WHERE ORIG.JOB_LEVEL=USRX.ID;
1157 l_JB_Parsed_Flag:='Y';
1158 END IF;
1159 ELSIF l_Job_Dimension_List_Tab IS NOT NULL THEN
1160 IF p_PA_DEBUG_MODE = 'Y' THEN
1161 Write2FWKLog('View BY JB is specified.');
1162 END IF;
1163 IF l_JL_Parsed_Flag IS NULL THEN
1164 Write2FWKLog('Creating JB rows, JL is not specified.');
1165 /*
1166 ** Following portion of the code is commented
1167 ** because of a bug in db (2596577).
1168 */
1169 /*
1170 FORALL i IN 1..l_Job_Dimension_List_Tab.Last
1171 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1172 SELECT ID, DECODE(p_View_BY,'JL',VALUE,'-1')
1173 FROM PJI_JOBS_V
1174 WHERE ID = l_Job_Dimension_List_Tab(i);
1175 */
1176 FOR i IN 1..l_Job_Dimension_List_Tab.Last LOOP
1177 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1178 SELECT ID, DECODE(p_View_BY,'JL',VALUE,'-1')
1179 FROM PJI_JOBS_V
1180 WHERE ID = l_Job_Dimension_List_Tab(i);
1181 END LOOP;
1182 l_JB_Parsed_Flag:='Y';
1183 ELSIF l_JL_Parsed_Flag IS NOT NULL THEN
1184 IF p_PA_DEBUG_MODE = 'Y' THEN
1185 Write2FWKLog('Creating JB rows, JL is specified.');
1186 END IF;
1187 /*
1188 ** Following portion of the code is commented
1189 ** because of a bug in db (2596577).
1190 */
1191 /*
1192 FORALL i IN 1..l_Job_Dimension_List_Tab.Last
1193 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1194 SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
1195 FROM PJI_JOBS_V ORIG
1196 , PJI_PMV_JL_DIM_TMP USRX
1197 WHERE ORIG.ID=l_Job_Dimension_List_Tab(i)
1198 AND ORIG.JOB_LEVEL=USRX.ID;
1199 */
1200 FOR i IN 1..l_Job_Dimension_List_Tab.Last LOOP
1201 INSERT INTO PJI_PMV_JL_DIM_TMP (ID, NAME)
1202 SELECT ORIG.ID, DECODE(p_View_BY,'JL',USRX.NAME,'JB',ORIG.VALUE,'-1')
1203 FROM PJI_JOBS_V ORIG
1204 , PJI_PMV_JL_DIM_TMP USRX
1205 WHERE ORIG.ID=l_Job_Dimension_List_Tab(i)
1206 AND ORIG.JOB_LEVEL=USRX.ID;
1207 END LOOP;
1208 l_JB_Parsed_Flag:='Y';
1209 END IF;
1210 END IF;
1211 IF p_PA_DEBUG_MODE = 'Y' THEN
1212 Write2FWKLog('Exiting Convert_Job_Level...');
1213 END IF;
1214
1215 RETURN l_JB_Parsed_Flag;
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 g_SQL_Error_Msg:=SQLERRM();
1219 IF p_PA_DEBUG_MODE = 'Y' THEN
1220 Write2FWKLog(g_SQL_Error_Msg, 3);
1221 END IF;
1222 RAISE;
1223 END Convert_Job_Level;
1224
1225 --Bug 4599990. This procedure will insert into PJI_PMV_ORGZ_DIM_TMP all the organizations in the PJI
1226 --reporting organization hierarchy that fall under the organization selected in the user assignment
1227 --of the person logged in. This is a private procedure and will be called by all the
1228 --convert_organization procedures in this package.
1229 PROCEDURE insert_user_assignment_orgz
1230 IS
1231 l_user_id NUMBER;
1232 l_temp NUMBER;
1233 BEGIN
1234
1235 IF p_PA_DEBUG_MODE = 'Y' THEN
1236 Write2FWKLog('Entering insert_user_assignment_orgz...');
1237 END IF;
1238 l_user_id := fnd_global.user_id;
1239 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1240 SELECT sub_organization_id,
1241 org.name
1242 FROM pji_org_denorm orgd,
1243 hr_all_organization_units_tl org,
1244 fnd_user fnd,
1245 per_all_assignments_f per
1246 WHERE org.language = USERENV('LANG')
1247 AND fnd.user_id=l_user_id
1248 AND fnd.employee_id=per.person_id
1249 AND per.primary_flag='Y'
1250 AND (SYSDATE BETWEEN per.effective_start_Date AND NVL(per.effective_end_date, SYSDATE + 1))
1251 AND orgd.sub_organization_id = org.organization_id
1252 AND orgd.organization_id = per.organization_id
1253 AND orgd.sub_organization_level-orgd.organization_level=1;
1254
1255 l_temp :=SQL%ROWCOUNT;
1256
1257 IF p_PA_DEBUG_MODE = 'Y' THEN
1258 Write2FWKLog('Leaving insert_user_assignment_orgz . Inserted rows'||l_temp);
1259 END IF;
1260
1261 END insert_user_assignment_orgz;
1262
1263 /*
1264 ** ----------------------------------------------------------
1265 ** Procedure: Convert_Organization
1266 ** The function inserts immediate sub organizations (the user
1267 ** has access to below the selected organization) into a
1268 ** session specific temporary table.
1269 ** ----------------------------------------------------------
1270 */
1271
1272 Procedure Convert_Organization(p_Top_Organization_ID NUMBER
1273 , p_View_BY VARCHAR2
1274 , p_Top_Organization_Name OUT NOCOPY VARCHAR2)
1275 AS
1276 l_Organization_Name VARCHAR2(240);
1277 l_Security_Profile_ID NUMBER:=fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
1278 l_View_All_Org_Flag VARCHAR2(30);
1279
1280 --Bug 4599990.
1281 l_top_organization_id per_security_profiles.organization_id%TYPE;
1282 BEGIN
1283 IF p_PA_DEBUG_MODE = 'Y' THEN
1284 Write2FWKLog('Entering Convert_Organization...','Convert_Organization (for Viewby reports)');
1285 END IF;
1286
1287 DELETE PJI_PMV_ORGZ_DIM_TMP;
1288
1289
1290 IF p_View_BY = 'OG' THEN
1291 IF l_Security_Profile_ID IS NOT NULL THEN
1292 IF p_PA_DEBUG_MODE = 'Y' THEN
1293 Write2FWKLog('Before checking if view_all_organizations_flag is set...');
1294 END IF;
1295 BEGIN
1296 --Bug 4599990. Selected other relavant data from the per_security_profiles.
1297 SELECT view_all_organizations_flag,
1298 organization_id
1299 INTO l_View_All_Org_Flag,
1300 l_top_organization_id
1301 FROM per_security_profiles
1302 WHERE security_profile_id = l_Security_Profile_ID;
1303 IF p_PA_DEBUG_MODE = 'Y' THEN
1304 Write2FWKLog('view_all_organizations_flag is set...');
1305 END IF;
1306 EXCEPTION
1307 WHEN NO_DATA_FOUND THEN
1308 NULL;
1309 END;
1310 IF p_PA_DEBUG_MODE = 'Y' THEN
1311 Write2FWKLog('After checking if view_all_organizations_flag is set...');
1312 END IF;
1313 END IF;
1314 IF l_View_All_Org_Flag = 'Y' THEN
1315 IF p_PA_DEBUG_MODE = 'Y' THEN
1316 Write2FWKLog('Before insert of immediate sub-org organizations (w/o security)...');
1317 END IF;
1318 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1319 SELECT sub_organization_id
1320 , org.name
1321 FROM pji_org_denorm orgd
1322 , hr_all_organization_units_tl org
1323 WHERE 1=1
1324 AND org.language = USERENV('LANG')
1325 AND orgd.sub_organization_id = org.organization_id
1326 AND orgd.sub_organization_level-orgd.organization_level=1
1327 AND orgd.organization_id = p_Top_Organization_ID;
1328 IF p_PA_DEBUG_MODE = 'Y' THEN
1329 Write2FWKLog('After insert of immediate sub-org organizations (w/o security)...');
1330 END IF;
1331 ELSIF l_View_All_Org_Flag = 'N' THEN
1332 IF p_PA_DEBUG_MODE = 'Y' THEN
1333 Write2FWKLog('Before insert of immediate sub-org organizations...');
1334 END IF;
1335 --Bug 4599990. Insert if the top org is entered for the security profile.
1336 IF l_top_organization_id IS NOT NULL THEN
1337
1338 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1339 SELECT sub_organization_id
1340 , org.name
1341 FROM pji_org_denorm orgd
1342 , per_organization_list sec
1343 , hr_all_organization_units_tl org
1344 WHERE 1=1
1345 AND org.language = USERENV('LANG')
1346 AND sec.security_profile_id = l_Security_Profile_ID
1347 AND orgd.sub_organization_id = org.organization_id
1348 AND orgd.sub_organization_id = sec.organization_id
1349 AND orgd.organization_id = p_Top_Organization_ID
1350 AND orgd.sub_organization_level-orgd.organization_level=1;
1351
1352 --Bug 4599990. Insert the organizations in the hierarchy with the org selected in the
1353 --user assignment as top org.
1354 ELSE
1355
1356 insert_user_assignment_orgz;
1357
1358 END IF;
1359
1360 IF p_PA_DEBUG_MODE = 'Y' THEN
1361 Write2FWKLog('After insert of immediate sub-org organizations...');
1362 END IF;
1363 END IF;
1364 END IF;
1365
1366 IF p_PA_DEBUG_MODE = 'Y' THEN
1367 Write2FWKLog('Before selecting the organization name...');
1368 END IF;
1369
1370 SELECT name
1371 INTO l_Organization_Name
1372 FROM hr_all_organization_units_tl
1373 WHERE organization_id = p_Top_Organization_ID
1374 AND language = USERENV('LANG');
1375
1376 IF p_PA_DEBUG_MODE = 'Y' THEN
1377 Write2FWKLog('After selecting the organization name...');
1378 END IF;
1379
1380 p_Top_Organization_Name:=l_Organization_Name;
1381
1382 IF p_PA_DEBUG_MODE = 'Y' THEN
1383 Write2FWKLog('Before insert of current organization...');
1384 END IF;
1385
1386
1387 IF p_View_BY = 'OG' THEN
1388 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1389 VALUES (p_Top_Organization_ID, l_Organization_Name);
1390 ELSE
1391 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1392 VALUES (p_Top_Organization_ID, -1);
1393 END IF;
1394
1395
1396 IF p_PA_DEBUG_MODE = 'Y' THEN
1397 Write2FWKLog('After insert of current organization...');
1398 Write2FWKLog('Exiting Convert_Organization...');
1399 END IF;
1400
1401 EXCEPTION
1402 WHEN OTHERS THEN
1403 g_SQL_Error_Msg:=SQLERRM();
1404 IF p_PA_DEBUG_MODE = 'Y' THEN
1405 Write2FWKLog(g_SQL_Error_Msg, 3);
1406 END IF;
1407 RAISE;
1408 END Convert_Organization;
1409
1410 /*
1411 ** ----------------------------------------------------------
1412 ** Procedure: Convert_Organization
1413 ** The function inserts all the organizations (the user has
1414 ** access to below the selected organization) into a session
1415 ** specific temporary table.
1416 ** ----------------------------------------------------------
1417 */
1418
1419 Procedure Convert_Organization(p_Top_Organization_ID NUMBER
1420 , p_View_BY VARCHAR2)
1421 AS
1422 l_Security_Profile_ID NUMBER:=fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
1423 l_View_All_Org_Flag VARCHAR2(30);
1424 --Bug 4599990.
1425 l_top_organization_id per_security_profiles.organization_id%TYPE;
1426 BEGIN
1427 IF p_PA_DEBUG_MODE = 'Y' THEN
1428 Write2FWKLog('Entering Convert_Organization...','Convert_Organization (for detail reports)');
1429 END IF;
1430
1431 DELETE PJI_PMV_ORGZ_DIM_TMP;
1432
1433 IF p_PA_DEBUG_MODE = 'Y' THEN
1434 Write2FWKLog('Before insert of rollup organization...');
1435 END IF;
1436
1437 IF (p_View_BY = 'OG') THEN
1438 IF l_Security_Profile_ID IS NOT NULL THEN
1439 IF p_PA_DEBUG_MODE = 'Y' THEN
1440 Write2FWKLog('Before checking if view_all_organizations_flag is set...');
1441 END IF;
1442 BEGIN
1443 --Bug 4599990. Selected other relavant data from the per_security_profiles.
1444 SELECT view_all_organizations_flag,
1445 organization_id
1446 INTO l_View_All_Org_Flag,
1447 l_top_organization_id
1448 FROM per_security_profiles
1449 WHERE security_profile_id = l_Security_Profile_ID;
1450 IF p_PA_DEBUG_MODE = 'Y' THEN
1451 Write2FWKLog('view_all_organizations_flag is set...');
1452 END IF;
1453 EXCEPTION
1454 WHEN NO_DATA_FOUND THEN
1455 NULL;
1456 END;
1457 IF p_PA_DEBUG_MODE = 'Y' THEN
1458 Write2FWKLog('After checking if view_all_organizations_flag is set...');
1459 END IF;
1460 END IF;
1461
1462 IF l_View_All_Org_Flag = 'Y' THEN
1463 IF p_PA_DEBUG_MODE = 'Y' THEN
1464 Write2FWKLog('Before insert of all sub-org organizations (w/o security)...');
1465 END IF;
1466 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1467 SELECT subro_organization_id
1468 , name
1469 FROM hri_cs_orghro_v orgd
1470 , hr_all_organization_units_tl org
1471 , pji_system_settings pjist
1472 WHERE 1=1
1473 AND pjist.setting_id = 1
1474 AND orgd.org_hierarchy_version_id = pjist.org_structure_version_id
1475 AND orgd.sub_organization_id = org.organization_id
1476 AND org.language = USERENV('LANG')
1477 AND orgd.sup_organization_id = p_Top_Organization_ID
1478 AND orgd.sub_org_absolute_level-orgd.sup_org_absolute_level=1;
1479 IF p_PA_DEBUG_MODE = 'Y' THEN
1480 Write2FWKLog('After insert of all sub-org organizations (w/o security)...');
1481 END IF;
1482 ELSIF l_View_All_Org_Flag = 'N' THEN
1483 IF p_PA_DEBUG_MODE = 'Y' THEN
1484 Write2FWKLog('Before insert of all sub-org organizations...');
1485 END IF;
1486
1487 --Bug 4599990. Insert if the top org is entered for the security profile.
1488 IF l_top_organization_id IS NOT NULL THEN
1489
1490 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1491 SELECT subro_organization_id
1492 , name
1493 FROM hri_cs_orghro_v orgd
1494 , hr_all_organization_units_tl org
1495 , per_organization_list sec
1496 , pji_system_settings pjist
1497 WHERE 1=1
1498 AND pjist.setting_id = 1
1499 AND sec.security_profile_id = l_Security_Profile_ID
1500 AND orgd.subro_organization_id = sec.organization_id
1501 AND orgd.org_hierarchy_version_id = pjist.org_structure_version_id
1502 AND orgd.sub_organization_id = org.organization_id
1503 AND org.language = USERENV('LANG')
1504 AND orgd.sup_organization_id = p_Top_Organization_ID
1505 AND orgd.sub_org_absolute_level-orgd.sup_org_absolute_level=1;
1506
1507 --Bug 4599990. Insert the organizations in the hierarchy with the org selected in the
1508 --user assignment as top org.
1509 ELSE
1510
1511 insert_user_assignment_orgz;
1512
1513 END IF;
1514 IF p_PA_DEBUG_MODE = 'Y' THEN
1515 Write2FWKLog('After insert of all sub-org organizations...');
1516 END IF;
1517 END IF;
1518 IF p_PA_DEBUG_MODE = 'Y' THEN
1519 Write2FWKLog('After insert of rollup organization...');
1520 Write2FWKLog('Before insert of current organization...');
1521 END IF;
1522
1523
1524 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1525 SELECT organization_id, name
1526 FROM hr_all_organization_units_tl
1527 WHERE organization_id = p_Top_Organization_ID
1528 AND language = USERENV('LANG');
1529
1530 IF p_PA_DEBUG_MODE = 'Y' THEN
1531 Write2FWKLog('After insert of current organization...');
1532 END IF;
1533 ELSE
1534 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1535 VALUES (p_Top_Organization_ID,'-1');
1536 IF p_PA_DEBUG_MODE = 'Y' THEN
1537 Write2FWKLog('After insert of rollup organization...');
1538 END IF;
1539 END IF;
1540 IF p_PA_DEBUG_MODE = 'Y' THEN
1541 Write2FWKLog('Exiting Convert_Organization...');
1542 END IF;
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 g_SQL_Error_Msg:=SQLERRM();
1546 IF p_PA_DEBUG_MODE = 'Y' THEN
1547 Write2FWKLog(g_SQL_Error_Msg, 3);
1548 END IF;
1549 RAISE;
1550 END Convert_Organization;
1551
1552 /*
1553 ** ----------------------------------------------------------
1554 ** Procedure: Convert_Organization
1555 ** The function inserts all the organizations (the user has
1556 ** access to) below the selected organization into a session
1557 ** specific temporary table. Provided for facilitating
1558 ** discoverer reporting.
1559 ** ----------------------------------------------------------
1560 */
1561
1562 Procedure Convert_Organization(p_Top_Organization_ID NUMBER DEFAULT NULL)
1563 AS
1564 l_Security_Profile_ID NUMBER:=fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL');
1565 l_View_All_Org_Flag VARCHAR2(30);
1566 l_Top_Organization_ID NUMBER:=p_Top_Organization_ID;
1567
1568 --Bug 4599990.
1569 l_sec_top_org_id per_security_profiles.organization_id%TYPE;
1570
1571 BEGIN
1572 IF p_PA_DEBUG_MODE = 'Y' THEN
1573 Write2FWKLog('Entering Convert_Organization...','Convert_Organization (for discover reports)');
1574 END IF;
1575
1576 DELETE PJI_PMV_ORGZ_DIM_TMP;
1577
1578 IF p_PA_DEBUG_MODE = 'Y' THEN
1579 Write2FWKLog('Before insert of all sub organizations...');
1580 END IF;
1581
1582 IF l_Top_Organization_ID IS NULL THEN
1583 BEGIN
1584 --Bug 5086074 , passing extra parameter to check code conditionally for discoverer implementation
1585 --PJI_PMV_DFLT_PARAMS_PVT.InitEnvironment;
1586 --l_Top_Organization_ID:=PJI_PMV_DFLT_PARAMS_PVT.Derive_Organization_ID;
1587 PJI_PMV_DFLT_PARAMS_PVT.InitEnvironment('ConvertOrg');
1588 l_Top_Organization_ID:=PJI_PMV_DFLT_PARAMS_PVT.Derive_Organization_ID('ConvertOrg');
1589 END;
1590 END IF;
1591 IF l_Security_Profile_ID IS NOT NULL THEN
1592 IF p_PA_DEBUG_MODE = 'Y' THEN
1593 Write2FWKLog('Before checking if view_all_organizations_flag is set...');
1594 END IF;
1595 BEGIN
1596 --Bug 4599990.Selected other relavant data from the per_security_profiles.
1597 SELECT view_all_organizations_flag,
1598 organization_id
1599 INTO l_View_All_Org_Flag,
1600 l_sec_top_org_id
1601 FROM per_security_profiles
1602 WHERE security_profile_id = l_Security_Profile_ID;
1603 IF p_PA_DEBUG_MODE = 'Y' THEN
1604 Write2FWKLog('view_all_organizations_flag is set...');
1605 END IF;
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 NULL;
1609 END;
1610 IF p_PA_DEBUG_MODE = 'Y' THEN
1611 Write2FWKLog('After checking if view_all_organizations_flag is set...');
1612 END IF;
1613 END IF;
1614
1615 IF l_View_All_Org_Flag = 'Y' THEN
1616 IF p_PA_DEBUG_MODE = 'Y' THEN
1617 Write2FWKLog('Before insert of all sub-org organizations (w/o security)...');
1618 END IF;
1619 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1620 SELECT org.organization_id, org.name
1621 FROM pji_org_denorm denorm
1622 , hr_all_organization_units_tl org
1623 WHERE
1624 denorm.organization_id = l_Top_Organization_ID
1625 AND org.organization_id = denorm.sub_organization_id
1626 AND org.language = USERENV('LANG');
1627
1628 IF p_PA_DEBUG_MODE = 'Y' THEN
1629 Write2FWKLog('After insert of all sub-org organizations (w/o security)...');
1630 END IF;
1631 ELSIF l_View_All_Org_Flag = 'N' THEN
1632 IF p_PA_DEBUG_MODE = 'Y' THEN
1633 Write2FWKLog('Before insert of all sub-org organizations...');
1634 END IF;
1635
1636 --Bug 4599990. Insert if the top org is entered for the security profile.
1637 IF l_sec_top_org_id IS NOT NULL THEN
1638
1639 INSERT INTO PJI_PMV_ORGZ_DIM_TMP (ID, NAME)
1640 SELECT org.organization_id, org.name
1641 FROM pji_org_denorm denorm
1642 , hr_all_organization_units_tl org
1643 , per_organization_list seclist
1644 WHERE
1645 denorm.organization_id = l_Top_Organization_ID
1646 AND org.organization_id = denorm.sub_organization_id
1647 AND seclist.security_profile_id = l_Security_Profile_ID
1648 AND seclist.organization_id = denorm.sub_organization_id
1649 AND org.language = USERENV('LANG');
1650
1651 --Bug 4599990. Insert the organizations in the hierarchy with the org selected in the
1652 --user assignment as top org.
1653 ELSE
1654
1655 insert_user_assignment_orgz;
1656
1657 END IF;
1658 IF p_PA_DEBUG_MODE = 'Y' THEN
1659 Write2FWKLog('After insert of all sub-org organizations...');
1660 END IF;
1661 END IF;
1662
1663 IF p_PA_DEBUG_MODE = 'Y' THEN
1664 Write2FWKLog('After insert of sub organizations...');
1665 Write2FWKLog('Exiting Convert_Organization...');
1666 END IF;
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 g_SQL_Error_Msg:=SQLERRM();
1671 IF p_PA_DEBUG_MODE = 'Y' THEN
1672 Write2FWKLog(g_SQL_Error_Msg, 3);
1673 END IF;
1674 RAISE;
1675 END Convert_Organization;
1676
1677 /*
1678 ** ----------------------------------------------------------
1679 ** Procedure: Convert_Operating_Unit
1680 ** The function inserts all the operating units (the user has
1681 ** access to) into a session specific temporary table.
1682 ** Additionally this procedure also caches the calender_id's
1683 ** for the selected operating unit. These caches values are
1684 ** used the convert time apis further.
1685 ** This makes it imperative that this api call should always
1686 ** precede the call to the Convert_Time API's.
1687 ** ----------------------------------------------------------
1688 */
1689
1690 Procedure Convert_Operating_Unit(p_Operating_Unit_IDS VARCHAR2 DEFAULT NULL
1691 , p_View_BY VARCHAR2)
1692 AS
1693 l_Security_Profile_ID NUMBER:=fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL');
1694 l_View_All_Org_Flag VARCHAR2(30);
1695 l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1696 BEGIN
1697 IF p_PA_DEBUG_MODE = 'Y' THEN
1698 Write2FWKLog('Entering Convert_Operating_Unit...','Convert_Operating_Unit');
1699 END IF;
1700
1701 /*
1702 ** Unconditionally reset the values of the global
1703 ** variables because the Convert_Time API's use
1704 ** these cached values to populate the table.
1705 */
1706 G_GL_Calendar_ID:=NULL;
1707 G_PA_Calendar_ID:=NULL;
1708
1709 DELETE PJI_PMV_ORG_DIM_TMP;
1710
1711 IF p_Operating_Unit_IDS IS NOT NULL THEN
1712 IF p_PA_DEBUG_MODE = 'Y' THEN
1713 Write2FWKLog('OU is Specified.');
1714 END IF;
1715 l_Dimension_List_Tab:=Decode_IDS(p_Operating_Unit_IDS);
1716 IF l_Dimension_List_Tab.COUNT > 0 THEN
1717 IF p_PA_DEBUG_MODE = 'Y' THEN
1718 Write2FWKLog('Caching calendar information.');
1719 END IF;
1720 SELECT gl_calendar_id
1721 , pa_calendar_id
1722 INTO
1723 G_GL_Calendar_ID
1724 , G_PA_Calendar_ID
1725 FROM PJI_ORG_EXTR_INFO
1726 WHERE org_id = l_Dimension_List_Tab(1);
1727 END IF;
1728 ELSE
1729 l_Dimension_List_Tab := NULL;
1730 END IF;
1731
1732
1733 IF l_Security_Profile_ID IS NOT NULL THEN
1734 IF p_PA_DEBUG_MODE = 'Y' THEN
1735 Write2FWKLog('Before checking if view_all_organizations_flag is set...');
1736 END IF;
1737 BEGIN
1738 SELECT view_all_organizations_flag
1739 INTO l_View_All_Org_Flag
1740 FROM per_security_profiles
1741 WHERE security_profile_id = l_Security_Profile_ID;
1742 IF p_PA_DEBUG_MODE = 'Y' THEN
1743 Write2FWKLog('view_all_organizations_flag is set...');
1744 END IF;
1745 EXCEPTION
1746 WHEN NO_DATA_FOUND THEN
1747 NULL;
1748 END;
1749 IF p_PA_DEBUG_MODE = 'Y' THEN
1750 Write2FWKLog('After checking if view_all_organizations_flag is set...');
1751 END IF;
1752 END IF;
1753
1754 IF l_Dimension_List_Tab IS NULL THEN
1755 IF p_PA_DEBUG_MODE = 'Y' THEN
1756 Write2FWKLog('OU Array is empty.');
1757 END IF;
1758 IF l_View_All_Org_Flag = 'Y' THEN
1759 IF p_PA_DEBUG_MODE = 'Y' THEN
1760 Write2FWKLog('Before insert of operating units (w/o security)...');
1761 END IF;
1762 INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
1763 SELECT paimp.org_id
1764 , decode(p_View_By,'OU',org.name,'-1')
1765 FROM pa_implementations_all paimp
1766 , hr_all_organization_units_tl org
1767 WHERE 1=1
1768 AND org.language = USERENV('LANG')
1769 AND paimp.org_id = org.organization_id;
1770 IF p_PA_DEBUG_MODE = 'Y' THEN
1771 Write2FWKLog('After insert of operating units (w/o security)...');
1772 END IF;
1773 ELSIF l_View_All_Org_Flag = 'N' THEN
1774 IF p_PA_DEBUG_MODE = 'Y' THEN
1775 Write2FWKLog('Before insert of operating units...');
1776 END IF;
1777 INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
1778 SELECT paimp.org_id
1779 , decode(p_View_By,'OU',org.name,'-1')
1780 FROM pa_implementations_all paimp
1781 , hr_all_organization_units_tl org
1782 , per_organization_list sec
1783 WHERE 1=1
1784 AND org.language = USERENV('LANG')
1785 AND sec.organization_id= paimp.org_id
1786 AND sec.security_profile_id = l_Security_Profile_ID
1787 AND paimp.org_id = org.organization_id;
1788 IF p_PA_DEBUG_MODE = 'Y' THEN
1789 Write2FWKLog('After insert of operating units...');
1790 END IF;
1791 END IF;
1792 ELSIF l_Dimension_List_Tab IS NOT NULL THEN
1793 IF p_PA_DEBUG_MODE = 'Y' THEN
1794 Write2FWKLog('OU Array is not empty.');
1795 END IF;
1796 /*
1797 ** Following portion of the code is commented
1798 ** because of a bug in db (2596577).
1799 */
1800 /*
1801 FORALL i IN 1..l_Dimension_List_Tab.Last
1802 INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
1803 SELECT organization_id, DECODE(p_View_BY,'OU',name,'-1')
1804 FROM hr_all_organization_units_tl
1805 WHERE
1806 organization_id=l_Dimension_List_Tab(i)
1807 AND language = USERENV('LANG');
1808 */
1809 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
1810 INSERT INTO PJI_PMV_ORG_DIM_TMP (ID, NAME)
1811 SELECT organization_id, DECODE(p_View_BY,'OU',name,'-1')
1812 FROM hr_all_organization_units_tl
1813 WHERE
1814 organization_id=l_Dimension_List_Tab(i)
1815 AND language = USERENV('LANG');
1816 END LOOP;
1817 END IF;
1818 IF p_PA_DEBUG_MODE = 'Y' THEN
1819 Write2FWKLog('Exiting Convert_Operating_Unit...');
1820 END IF;
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 g_SQL_Error_Msg:=SQLERRM();
1825 IF p_PA_DEBUG_MODE = 'Y' THEN
1826 Write2FWKLog(g_SQL_Error_Msg, 3);
1827 END IF;
1828 RAISE;
1829 END Convert_Operating_Unit;
1830
1831 /*
1832 ** ----------------------------------------------------------
1833 ** Procedure: Convert_Project
1834 ** The function inserts all the projects the user has selected
1835 ** as parameters in the pmv report to a session specific
1836 ** temporary table.
1837 ** ----------------------------------------------------------
1838 */
1839
1840 Procedure Convert_Project(p_Project_IDS VARCHAR2 DEFAULT NULL
1841 , p_View_BY VARCHAR2)
1842 AS
1843 l_Dimension_List_Tab SYSTEM.pa_num_tbl_type := SYSTEM.pa_num_tbl_type();
1844 BEGIN
1845 IF p_PA_DEBUG_MODE = 'Y' THEN
1846 Write2FWKLog('Entering Convert_Project...','Convert_Project');
1847 END IF;
1848
1849 DELETE PJI_PMV_PRJ_DIM_TMP;
1850
1851 IF p_Project_IDS IS NOT NULL THEN
1852 l_Dimension_List_Tab:=Decode_IDS(p_Project_IDS);
1853 ELSE
1854 l_Dimension_List_Tab := NULL;
1855 END IF;
1856
1857 /*
1858 ** Commented the below portion of code till
1859 ** decision has been made to support the
1860 ** projects in the context of class category
1861 ** and class codes.
1862 */
1863 /*
1864 IF l_Dimension_List_Tab IS NULL THEN
1865 IF p_PA_DEBUG_MODE = 'Y' THEN
1866 Write2FWKLog('Creating ALL PJs, PJ array is empty');
1867 END IF;
1868 INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
1869 SELECT ID, DECODE(p_View_BY,'PJ',VALUE,'-1')
1870 FROM PJI_PROJECTS_V;
1871 */
1872 IF l_Dimension_List_Tab IS NOT NULL THEN
1873 IF p_PA_DEBUG_MODE = 'Y' THEN
1874 Write2FWKLog('Creating Selected PJs, PJ array is not empty');
1875 END IF;
1876 /*
1877 ** Following portion of the code is commented
1878 ** because of a bug in db (2596577).
1879 */
1880 /*
1881 FORALL i IN 1..l_Dimension_List_Tab.Last
1882 INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
1883 SELECT ID, DECODE(p_View_BY,'OU',VALUE,'-1')
1884 FROM PJI_PROJECTS_V
1885 WHERE
1886 ID=l_Dimension_List_Tab(i);
1887 */
1888 FOR i IN 1..l_Dimension_List_Tab.Last LOOP
1889 INSERT INTO PJI_PMV_PRJ_DIM_TMP (ID, NAME)
1890 SELECT ID, DECODE(p_View_BY,'OU',VALUE,'-1')
1891 FROM PJI_PROJECTS_V
1892 WHERE
1893 ID=l_Dimension_List_Tab(i);
1894 END LOOP;
1895 END IF;
1896 IF p_PA_DEBUG_MODE = 'Y' THEN
1897 Write2FWKLog('Exiting Convert_Project...');
1898 END IF;
1899 EXCEPTION
1900 WHEN OTHERS THEN
1901 g_SQL_Error_Msg:=SQLERRM();
1902 IF p_PA_DEBUG_MODE = 'Y' THEN
1903 Write2FWKLog(g_SQL_Error_Msg, 3);
1904 END IF;
1905 RAISE;
1906 END Convert_Project;
1907
1908 /*
1909 ** Conversion TIME API's Section.
1910 ** =========================
1911 ** The time API's .. to write the comments here
1912 */
1913
1914 /*
1915 ** Convert_Time API's for Non View BY TIME
1916 */
1917
1918 /*
1919 ** ----------------------------------------------------------
1920 ** Procedure: Convert_NViewBY_AS_OF_DATE
1921 ** The procedure creates time records based on an as of date
1922 ** when the view by dimension is not time. The API caters
1923 ** to additional logic based on following parameters:
1924 ** Full Period Flag: If the flag is passed as 'Y', the API
1925 ** creates additional time records for accomodating full
1926 ** period amounts for reporting budget data.
1927 ** Parse Prior: If the flag is passed as 'Y', the API stamps
1928 ** prior_id in the time tables.
1929 ** Calendar ID: Specifies the calendar id for fiscal time
1930 ** periods.
1931 ** Default Period Name: User defined constants to be inserted
1932 ** into name column of the time temporary table.
1933 ** Default Period ID: User defined constants to be inserted
1934 ** into order_by_id column of the time temporary table.
1935 ** ----------------------------------------------------------
1936 */
1937
1938 Procedure Convert_NViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
1939 , p_Period_Type VARCHAR2
1940 , p_Parse_Prior VARCHAR2 DEFAULT NULL
1941 , p_Full_Period_Flag VARCHAR2 DEFAULT NULL
1942 , p_Calendar_ID NUMBER DEFAULT NULL
1943 , p_Default_Period_Name VARCHAR2 DEFAULT NULL
1944 , p_Default_Period_ID NUMBER DEFAULT NULL)
1945 IS
1946 l_Period_Id NUMBER;
1947 l_Week_ID NUMBER;
1948 l_Qtr_Id NUMBER;
1949 l_Year_Id NUMBER;
1950 l_Prior_Period_Id NUMBER;
1951
1952 l_Default_Period_Name VARCHAR2(30);
1953 l_Default_Period_ID NUMBER;
1954
1955 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date,'j');
1956 l_Prior_As_Of_Date DATE;
1957
1958 l_Period_Type VARCHAR2(150):=p_Period_Type;
1959 l_Level NUMBER;
1960 l_IS_GL_Flag VARCHAR2(1);
1961
1962 l_Calendar_Id NUMBER;
1963 l_Calendar_Type_Sum VARCHAR2(1);
1964 l_Week_Start_Date DATE;
1965 BEGIN
1966 IF p_PA_DEBUG_MODE = 'Y' THEN
1967 Write2FWKLog('Entering Convert_NViewBY_AS_OF_DATE...','Convert_NViewBY_AS_OF_DATE');
1968 END IF;
1969
1970 IF p_Parse_Prior IS NOT NULL THEN
1971 BEGIN
1972 IF p_PA_DEBUG_MODE = 'Y' THEN
1973 Write2FWKLog('Determining Prior Year...');
1974 END IF;
1975 -- Note: Please confirm this change with dima.
1976 l_Prior_As_Of_Date:=TO_DATE(Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, 'YEARLY'),'j');
1977 EXCEPTION
1978 WHEN NO_DATA_FOUND THEN
1979 IF p_PA_DEBUG_MODE = 'Y' THEN
1980 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
1981 END IF;
1982 END;
1983 END IF;
1984
1985 IF p_PA_DEBUG_MODE = 'Y' THEN
1986 Write2FWKLog('Done with computing Prior Year...');
1987 END IF;
1988
1989 IF p_Default_Period_Name IS NOT NULL THEN
1990 l_Default_Period_Name:=p_Default_Period_Name;
1991 ELSE
1992 l_Default_Period_Name:='-1';
1993 END IF;
1994
1995 IF p_Default_Period_ID IS NOT NULL THEN
1996 l_Default_Period_ID:=p_Default_Period_ID;
1997 ELSE
1998 l_Default_Period_ID:=-1;
1999 END IF;
2000
2001 IF l_Period_Type LIKE '%PA%' THEN
2002 IF p_Calendar_Id IS NULL THEN
2003 l_Calendar_Id:=G_PA_Calendar_ID;
2004 ELSE
2005 l_Calendar_Id:=p_Calendar_ID;
2006 END IF;
2007 l_IS_GL_Flag:='Y';
2008 l_Calendar_Type_Sum:='P';
2009 IF p_PA_DEBUG_MODE = 'Y' THEN
2010 Write2FWKLog('PA calender is selected.');
2011 END IF;
2012 ELSIF p_Period_Type LIKE '%CAL%' THEN
2013 IF p_Calendar_Id IS NULL THEN
2014 l_Calendar_Id:=G_GL_Calendar_ID;
2015 ELSE
2016 l_Calendar_Id:=p_Calendar_ID;
2017 END IF;
2018 l_IS_GL_Flag:='Y';
2019 l_Calendar_Type_Sum:='G';
2020 IF p_PA_DEBUG_MODE = 'Y' THEN
2021 Write2FWKLog('GL calender is selected.');
2022 END IF;
2023 ELSE
2024 l_Calendar_Id:=-1;
2025 l_Calendar_Type_Sum:='E';
2026 IF p_PA_DEBUG_MODE = 'Y' THEN
2027 Write2FWKLog('Ent calender is selected.');
2028 END IF;
2029 END IF;
2030
2031 IF p_Full_Period_Flag IS NOT NULL OR l_Period_Type = 'PJI_TIME_PA_PERIOD' THEN
2032 IF l_IS_GL_Flag IS NULL THEN
2033 IF p_PA_DEBUG_MODE = 'Y' THEN
2034 Write2FWKLog('Selecting from FII_TIME_DAY.');
2035 END IF;
2036 SELECT
2037 ent_period_id, week_id, week_start_date, ent_qtr_id, ent_year_id
2038 INTO
2039 l_Period_Id, l_Week_Id, l_Week_Start_Date, l_Qtr_Id, l_Year_Id
2040 FROM fii_time_day
2041 WHERE
2042 report_date = l_As_Of_Date;
2043 ELSE
2044 IF p_PA_DEBUG_MODE = 'Y' THEN
2045 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
2046 END IF;
2047 SELECT
2048 cal_period_id, cal_qtr_id, cal_year_id
2049 INTO
2050 l_Period_Id, l_Qtr_Id, l_Year_Id
2051 FROM fii_time_cal_day_mv
2052 WHERE
2053 report_date = l_As_Of_Date
2054 AND calendar_id = l_Calendar_Id;
2055 END IF;
2056 END IF;
2057
2058 IF p_Period_Type = 'PJI_TIME_PA_PERIOD' AND p_Parse_Prior = 'Y' AND l_Prior_As_Of_Date IS NOT NULL THEN
2059 IF p_PA_DEBUG_MODE = 'Y' THEN
2060 Write2FWKLog('Selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
2061 END IF;
2062
2063 SELECT cal_period_id
2064 INTO l_Prior_Period_Id
2065 FROM fii_time_cal_day_mv
2066 WHERE 1 = 1
2067 AND report_date = l_Prior_As_Of_Date
2068 AND calendar_id = l_Calendar_Id;
2069
2070 IF p_PA_DEBUG_MODE = 'Y' THEN
2071 Write2FWKLog('Done selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
2072 END IF;
2073 END IF;
2074
2075 CASE p_Period_Type
2076 WHEN 'ITD' THEN l_Level:=1143;
2077 WHEN 'FII_TIME_ENT_YEAR' THEN l_Level:=119;
2078 WHEN 'FII_TIME_CAL_YEAR' THEN l_Level:=119;
2079 WHEN 'FII_TIME_ENT_QTR' THEN l_Level:=55;
2080 WHEN 'FII_TIME_CAL_QTR' THEN l_Level:=55;
2081 WHEN 'FII_TIME_ENT_PERIOD' THEN l_Level:=23;
2082 WHEN 'FII_TIME_CAL_PERIOD' THEN l_Level:=23;
2083 WHEN 'PJI_TIME_PA_PERIOD' THEN l_Level:=23;
2084 WHEN 'FII_TIME_WEEK' THEN l_Level:=11;
2085 WHEN 'FII_ROLLING_WEEK' THEN l_Level:=0;
2086 ELSE NULL;
2087 END CASE;
2088
2089 IF p_Period_Type <> 'PJI_TIME_PA_PERIOD' THEN
2090 IF p_PA_DEBUG_MODE = 'Y' THEN
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
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
2108 AND bitand(record_type_id,l_Level) = record_type_id
2109 AND calendar_id = l_Calendar_Id;
2110 END IF;
2111
2112 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
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
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
2130 AND bitand(record_type_id,l_Level) = record_type_id
2131 AND calendar_id = l_Calendar_Id;
2132 END IF;
2133 END IF;
2134 IF p_PA_DEBUG_MODE = 'Y' THEN
2135 Write2FWKLog('Done generating records for period types other than PA Period.');
2136 END IF;
2137 ELSE
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
2147 AND calendar_id = l_Calendar_Id
2148 AND report_date<=l_As_Of_Date;
2149
2150 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
2151
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
2161 AND calendar_id = l_Calendar_Id
2162 AND report_date<=l_Prior_As_Of_Date;
2163
2164 END IF;
2165
2166 IF p_PA_DEBUG_MODE = 'Y' THEN
2167 Write2FWKLog('Done generating records for PA Period period type.');
2168 END IF;
2169 END IF;
2170 IF p_Full_Period_Flag IS NOT NULL THEN
2171 IF p_PA_DEBUG_MODE = 'Y' THEN
2172 Write2FWKLog('Full Period Flag is set.');
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
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
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
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)
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)
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)
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)
2225 ||SUBSTR(LPAD(l_Week_ID,11,'0'),8)
2226 , l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
2227 END CASE;
2228 END IF;
2229 END IF;
2230 IF p_PA_DEBUG_MODE = 'Y' THEN
2231 Write2FWKLog('Full Period entries created.');
2232 ENd IF;
2233 IF p_Period_Type = 'FII_ROLLING_WEEK' AND l_Level= 0 THEN
2234 IF G_No_Rolling_Weeks IS NULL THEN
2235 BEGIN
2236 SELECT rolling_weeks
2237 INTO G_No_Rolling_Weeks
2238 FROM pji_system_settings;
2239 EXCEPTION
2240 WHEN NO_DATA_FOUND THEN
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);
2259 END IF;
2260 END IF;
2261 END IF;
2262
2263 IF p_PA_DEBUG_MODE = 'Y' THEN
2264 Write2FWKLog('Exiting Convert_NViewBY_AS_OF_DATE...');
2265 END IF;
2266 EXCEPTION
2267 WHEN NO_DATA_FOUND THEN
2268 NULL;
2269 WHEN OTHERS THEN
2270 g_SQL_Error_Msg:=SQLERRM();
2271 Write2FWKLog(g_SQL_Error_Msg, 3);
2272 RAISE;
2273 END Convert_NViewBY_AS_OF_DATE;
2274
2275 /*
2276 ** ----------------------------------------------------------
2277 ** Procedure: Convert_NFViewBY_AS_OF_DATE
2278 ** The procedure creates time records based on an as of date
2279 ** when the view by dimension is not time. The API caters
2280 ** to additional logic based on following parameters:
2281 ** Full Period Flag: If the flag is passed as 'Y', the API
2282 ** creates additional time records for accomodating full
2283 ** period amounts for reporting budget data.
2284 ** Parse Prior: If the flag is passed as 'Y', the API stamps
2285 ** prior_id in the time tables.
2286 ** Calendar ID: Specifies the calendar id for fiscal time
2287 ** periods.
2288 ** Default Period Name: User defined constants to be inserted
2289 ** into name column of the time temporary table.
2290 ** Default Period ID: User defined constants to be inserted
2291 ** into order_by_id column of the time temporary table.
2292 ** ----------------------------------------------------------
2293 */
2294
2295 Procedure Convert_NFViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
2296 , p_Period_Type VARCHAR2
2297 , p_Parse_Prior VARCHAR2 DEFAULT NULL
2298 , p_Full_Period_Flag VARCHAR2 DEFAULT NULL
2299 , p_Calendar_ID NUMBER DEFAULT NULL
2300 , p_Default_Period_Name VARCHAR2 DEFAULT NULL
2301 , p_Default_Period_ID NUMBER DEFAULT NULL)
2302 IS
2303 l_Period_Id NUMBER;
2304 l_Week_ID NUMBER;
2305 l_Qtr_Id NUMBER;
2306 l_Year_Id NUMBER;
2307 l_Prior_Period_Id NUMBER;
2308
2309 l_Period_Start_Date DATE;
2310 l_Qtr_Start_Date DATE;
2311 l_Year_Start_Date DATE;
2312
2313 l_Period_End_Date DATE;
2314 l_Qtr_End_Date DATE;
2315 l_Year_End_Date DATE;
2316
2317 l_Prior_Period_Start_Date DATE;
2318 l_Prior_Qtr_Start_Date DATE;
2319 l_Prior_Year_Start_Date DATE;
2320
2321 l_Prior_Period_End_Date DATE;
2322 l_Prior_Qtr_End_Date DATE;
2323 l_Prior_Year_End_Date DATE;
2324
2325 l_Default_Period_Name VARCHAR2(30);
2326 l_Default_Period_ID NUMBER;
2327
2328
2329 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date,'j');
2330 l_Prior_As_Of_Date DATE;
2331
2332 l_Period_Type VARCHAR2(150):=p_Period_Type;
2333 l_Level NUMBER;
2334 l_IS_GL_Flag VARCHAR2(1);
2335
2336 l_Calendar_Id NUMBER;
2337 l_Calendar_Type_Sum VARCHAR2(1);
2338 l_Calendar_Type_Day VARCHAR2(1);
2339 l_Week_Start_Date DATE;
2340 BEGIN
2341 IF p_PA_DEBUG_MODE = 'Y' THEN
2342 Write2FWKLog('Entering Convert_NViewBY_AS_OF_DATE...','Convert_NViewBY_AS_OF_DATE');
2343 END IF;
2344
2345 IF p_Parse_Prior IS NOT NULL THEN
2346 BEGIN
2347 IF p_PA_DEBUG_MODE = 'Y' THEN
2348 Write2FWKLog('Determining Prior Year...');
2349 END IF;
2350 -- Note: Please confirm this change with dima.
2351 l_Prior_As_Of_Date:=TO_DATE(Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, 'YEARLY'),'j');
2352 EXCEPTION
2353 WHEN NO_DATA_FOUND THEN
2354 IF p_PA_DEBUG_MODE = 'Y' THEN
2355 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
2356 END IF;
2357 END;
2358 END IF;
2359
2360 IF p_PA_DEBUG_MODE = 'Y' THEN
2361 Write2FWKLog('Done with computing Prior Year...');
2362 END IF;
2363
2364 IF p_Default_Period_Name IS NOT NULL THEN
2365 l_Default_Period_Name:=p_Default_Period_Name;
2366 ELSE
2367 l_Default_Period_Name:='-1';
2368 END IF;
2369
2370 IF p_Default_Period_ID IS NOT NULL THEN
2371 l_Default_Period_ID:=p_Default_Period_ID;
2372 ELSE
2373 l_Default_Period_ID:=-1;
2374 END IF;
2375
2376 IF l_Period_Type LIKE '%PA%' THEN
2377 IF p_Calendar_Id IS NULL THEN
2378 l_Calendar_Id:=G_PA_Calendar_ID;
2379 ELSE
2380 l_Calendar_Id:=p_Calendar_ID;
2381 END IF;
2382 l_IS_GL_Flag:='Y';
2383 l_Calendar_Type_Sum:='P';
2384 l_Calendar_Type_Day:='P';
2385 IF p_PA_DEBUG_MODE = 'Y' THEN
2386 Write2FWKLog('PA calender is selected.');
2387 END IF;
2388 ELSIF p_Period_Type LIKE '%CAL%' THEN
2389 IF p_Calendar_Id IS NULL THEN
2390 l_Calendar_Id:=G_GL_Calendar_ID;
2391 ELSE
2392 l_Calendar_Id:=p_Calendar_ID;
2393 END IF;
2394 l_IS_GL_Flag:='Y';
2395 l_Calendar_Type_Sum:='G';
2396 l_Calendar_Type_Day:='C';
2397 IF p_PA_DEBUG_MODE = 'Y' THEN
2398 Write2FWKLog('GL calender is selected.');
2399 END IF;
2400 ELSE
2401 l_Calendar_Id:=-1;
2402 l_Calendar_Type_Sum:='E';
2403 l_Calendar_Type_Day:='C';
2404 IF p_PA_DEBUG_MODE = 'Y' THEN
2405 Write2FWKLog('Ent calender is selected.');
2406 END IF;
2407 END IF;
2408
2409 IF l_IS_GL_Flag IS NULL THEN
2410 IF p_PA_DEBUG_MODE = 'Y' THEN
2411 Write2FWKLog('Selecting from FII_TIME_DAY.');
2412 END IF;
2413
2414 SELECT period.start_date period_start_date
2415 ,qtr.start_date qtr_start_date
2416 ,year.start_date year_start_date
2417 ,period.end_date period_end_date
2418 ,qtr.end_date qtr_end_date
2419 ,year.end_date year_end_date
2420 ,day.ent_period_id period_id
2421 ,day.week_id week_id
2422 ,day.ent_qtr_id qtr_id
2423 ,day.ent_year_id year_id
2424 INTO
2425 l_Period_Start_Date
2426 ,l_Qtr_Start_Date
2427 ,l_Year_Start_Date
2428 ,l_Period_End_Date
2429 ,l_Qtr_End_Date
2430 ,l_Year_End_Date
2431 ,l_Period_Id
2432 ,l_Week_ID
2433 ,l_Qtr_Id
2434 ,l_Year_Id
2435 FROM fii_time_day day
2436 , fii_time_ent_period period
2437 , fii_time_ent_qtr qtr
2438 , fii_time_ent_year year
2439 WHERE 1=1
2440 AND day.report_date = l_As_Of_Date
2441 AND period.ent_period_id = day.ent_period_id
2442 AND qtr.ent_qtr_id = day.ent_qtr_id
2443 AND year.ent_year_id = day.ent_year_id;
2444 ELSE
2445 IF p_PA_DEBUG_MODE = 'Y' THEN
2446 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
2447 END IF;
2448 SELECT period.start_date period_start_date
2449 ,qtr.start_date qtr_start_date
2450 ,year.start_date year_start_date
2451 ,period.end_date period_end_date
2452 ,qtr.end_date qtr_end_date
2453 ,year.end_date year_end_date
2454 ,day.cal_period_id period_id
2455 ,day.cal_qtr_id qtr_id
2456 ,day.cal_year_id year_id
2457 INTO
2458 l_Period_Start_Date
2459 ,l_Qtr_Start_Date
2460 ,l_Year_Start_Date
2461 ,l_Period_End_Date
2462 ,l_Qtr_End_Date
2463 ,l_Year_End_Date
2464 ,l_Period_Id
2465 ,l_Qtr_Id
2466 ,l_Year_Id
2467 FROM fii_time_cal_day_mv day
2468 , fii_time_cal_period period
2469 , fii_time_cal_qtr qtr
2470 , fii_time_cal_year year
2471 WHERE 1=1
2472 AND day.report_date = l_As_Of_Date
2473 AND period.cal_period_id = day.cal_period_id
2474 AND qtr.cal_qtr_id = day.cal_qtr_id
2475 AND year.cal_year_id = day.cal_year_id
2476 AND day.calendar_id = l_Calendar_Id;
2477 END IF;
2478
2479 IF p_Parse_Prior = 'Y' THEN
2480 IF l_IS_GL_Flag IS NULL THEN
2481 IF p_PA_DEBUG_MODE = 'Y' THEN
2482 Write2FWKLog('Selecting from FII_TIME_DAY.');
2483 END IF;
2484
2485 SELECT period.start_date period_start_date
2486 ,qtr.start_date qtr_start_date
2487 ,year.start_date year_start_date
2488 ,period.end_date period_end_date
2489 ,qtr.end_date qtr_end_date
2490 ,year.end_date year_end_date
2491 INTO
2492 l_Prior_Period_Start_Date
2493 ,l_Prior_Qtr_Start_Date
2494 ,l_Prior_Year_Start_Date
2495 ,l_Prior_Period_End_Date
2496 ,l_Prior_Qtr_End_Date
2497 ,l_Prior_Year_End_Date
2498 FROM fii_time_day day
2499 , fii_time_ent_period period
2500 , fii_time_ent_qtr qtr
2501 , fii_time_ent_year year
2502 WHERE 1=1
2503 AND day.report_date = l_Prior_As_Of_Date
2504 AND period.ent_period_id = day.ent_period_id
2505 AND qtr.ent_qtr_id = day.ent_qtr_id
2506 AND year.ent_year_id = day.ent_year_id;
2507 ELSE
2508 IF p_PA_DEBUG_MODE = 'Y' THEN
2509 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
2510 END IF;
2511 SELECT period.start_date period_start_date
2512 ,qtr.start_date qtr_start_date
2513 ,year.start_date year_start_date
2514 ,period.end_date period_end_date
2515 ,qtr.end_date qtr_end_date
2516 ,year.end_date year_end_date
2517 INTO
2518 l_Prior_Period_Start_Date
2519 ,l_Prior_Qtr_Start_Date
2520 ,l_Prior_Year_Start_Date
2521 ,l_Prior_Period_End_Date
2522 ,l_Prior_Qtr_End_Date
2523 ,l_Prior_Year_End_Date
2524 FROM fii_time_cal_day_mv day
2525 , fii_time_cal_period period
2526 , fii_time_cal_qtr qtr
2527 , fii_time_cal_year year
2528 WHERE 1=1
2529 AND day.report_date = l_Prior_As_Of_Date
2530 AND period.cal_period_id = day.cal_period_id
2531 AND qtr.cal_qtr_id = day.cal_qtr_id
2532 AND year.cal_year_id = day.cal_year_id
2533 AND day.calendar_id = l_Calendar_Id;
2534 END IF;
2535 END IF;
2536
2537 CASE p_Period_Type
2538 WHEN 'ITD' THEN l_Level:=1143;
2539 WHEN 'FII_TIME_ENT_YEAR' THEN l_Level:=119;
2540 WHEN 'FII_TIME_CAL_YEAR' THEN l_Level:=119;
2541 WHEN 'FII_TIME_ENT_QTR' THEN l_Level:=55;
2542 WHEN 'FII_TIME_CAL_QTR' THEN l_Level:=55;
2543 WHEN 'FII_TIME_ENT_PERIOD' THEN l_Level:=23;
2544 WHEN 'FII_TIME_CAL_PERIOD' THEN l_Level:=23;
2545 WHEN 'PJI_TIME_PA_PERIOD' THEN l_Level:=23;
2546 WHEN 'FII_TIME_WEEK' THEN l_Level:=11;
2547 ELSE NULL;
2548 END CASE;
2549
2550 IF p_PA_DEBUG_MODE = 'Y' THEN
2551 Write2FWKLog('Generating records for period types other than PA Period.');
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
2561 report_date>=l_As_Of_Date
2562 AND calendar_id = l_calendar_id
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
2572 start_date>=l_As_Of_Date
2573 AND calendar_id = l_calendar_id
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
2583 start_date>=l_As_Of_Date
2584 AND calendar_id = l_calendar_id
2585 AND end_date<=l_year_end_date;
2586 END IF;
2587 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
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
2597 report_date>=l_prior_As_Of_Date
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
2607 start_date>=l_prior_As_Of_Date
2608 AND calendar_id = l_calendar_id
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
2618 start_date>=l_prior_As_Of_Date
2619 AND calendar_id = l_calendar_id
2620 AND end_date<=l_prior_year_end_date;
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
2630 report_date>=l_As_Of_Date
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
2640 start_date>=l_As_Of_Date
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
2650 start_date>=l_As_Of_Date
2651 AND end_date<=l_year_end_date;
2652 END IF;
2653 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
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
2663 report_date>=l_prior_As_Of_Date
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
2673 start_date>=l_prior_As_Of_Date
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
2683 start_date>=l_prior_As_Of_Date
2684 AND end_date<=l_prior_year_end_date;
2685 END IF;
2686 IF p_PA_DEBUG_MODE = 'Y' THEN
2687 Write2FWKLog('Done generating records for for enterprise period types.');
2688 END IF;
2689 END IF;
2690 END IF;
2691 IF p_Full_Period_Flag IS NOT NULL THEN
2692 IF p_PA_DEBUG_MODE = 'Y' THEN
2693 Write2FWKLog('Full Period Flag is set.');
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
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
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
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)
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)
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)
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)
2746 ||SUBSTR(LPAD(l_Week_ID,11,'0'),8)
2747 , l_Default_Period_Name, l_Default_Period_ID, 2, 16, 'E');
2748 END CASE;
2749 END IF;
2750 END IF;
2751 IF p_PA_DEBUG_MODE = 'Y' THEN
2752 Write2FWKLog('Full Period entries created.');
2753 END IF;
2754 END IF;
2755
2756 IF p_PA_DEBUG_MODE = 'Y' THEN
2757 Write2FWKLog('Exiting Convert_NFViewBY_AS_OF_DATE...');
2758 END IF;
2759 EXCEPTION
2760 WHEN NO_DATA_FOUND THEN
2761 NULL;
2762 WHEN OTHERS THEN
2763 g_SQL_Error_Msg:=SQLERRM();
2764 Write2FWKLog(g_SQL_Error_Msg, 3);
2765 RAISE;
2766 END Convert_NFViewBY_AS_OF_DATE;
2767
2768 /*
2769 ** ----------------------------------------------------------
2770 ** Procedure: Convert_DBI_NViewBY_AS_OF_DATE
2771 ** The procedure creates time records for dbi reports based
2772 ** on an as of date when the view by dimension is not time.
2773 ** The API caters to additional logic based on following
2774 ** parameters:
2775 ** Full Period Flag: If the flag is passed as 'Y', the API
2776 ** creates additional time records for accomodating full
2777 ** period amounts for reporting budget data.
2778 ** ----------------------------------------------------------
2779 */
2780 Procedure Convert_DBI_NViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
2781 , p_Period_Type VARCHAR2
2782 , p_Comparator VARCHAR2
2783 , p_Full_Period_Flag VARCHAR2 DEFAULT NULL)
2784 IS
2785 l_Period_Id NUMBER;
2786 l_Week_ID NUMBER;
2787 l_Qtr_Id NUMBER;
2788 l_Year_Id NUMBER;
2789
2790 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date,'j');
2791
2792 l_Period_Type VARCHAR2(150):=p_Period_Type;
2793 l_Level NUMBER;
2794 l_IS_GL_Flag VARCHAR2(1);
2795
2796
2797 l_Calendar_Id NUMBER;
2798 l_Calendar_Type_Sum VARCHAR2(1);
2799 BEGIN
2800 IF p_PA_DEBUG_MODE = 'Y' THEN
2801 Write2FWKLog('Entering Convert_DBI_NViewBY_AS_OF_DATE...','Convert_DBI_NViewBY_AS_OF_DATE');
2802 END IF;
2803
2804 IF l_Period_Type LIKE '%PA%' THEN
2805 l_Calendar_Id:=G_PA_Calendar_ID;
2806 l_IS_GL_Flag:='Y';
2807 l_Calendar_Type_Sum:='P';
2808 IF p_PA_DEBUG_MODE = 'Y' THEN
2809 Write2FWKLog('PA calender is selected.');
2810 END IF;
2811 ELSIF p_Period_Type LIKE '%CAL%' THEN
2812 l_Calendar_Id:=G_GL_Calendar_ID;
2813 l_IS_GL_Flag:='Y';
2814 l_Calendar_Type_Sum:='G';
2815 IF p_PA_DEBUG_MODE = 'Y' THEN
2816 Write2FWKLog('GL calender is selected.');
2817 END IF;
2818 ELSE
2819 l_Calendar_Id:=-1;
2820 l_Calendar_Type_Sum:='E';
2821 IF p_PA_DEBUG_MODE = 'Y' THEN
2822 Write2FWKLog('Ent calender is selected.');
2823 END IF;
2824 END IF;
2825
2826 IF p_Full_Period_Flag IS NOT NULL OR l_Period_Type = 'PJI_TIME_PA_PERIOD' THEN
2827 IF l_IS_GL_Flag IS NULL THEN
2828 IF p_PA_DEBUG_MODE = 'Y' THEN
2829 Write2FWKLog('Selecting from FII_TIME_DAY.');
2830 END IF;
2831 SELECT
2832 ent_period_id, week_id, ent_qtr_id, ent_year_id
2833 INTO
2834 l_Period_Id, l_Week_Id, l_Qtr_Id, l_Year_Id
2835 FROM fii_time_day
2836 WHERE
2837 report_date = l_As_Of_Date;
2838 ELSE
2839 IF p_PA_DEBUG_MODE = 'Y' THEN
2840 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
2841 END IF;
2842 SELECT
2843 cal_period_id, cal_qtr_id, cal_year_id
2844 INTO
2845 l_Period_Id, l_Qtr_Id, l_Year_Id
2846 FROM fii_time_cal_day_mv
2847 WHERE
2848 report_date = l_As_Of_Date
2849 AND calendar_id = l_Calendar_Id;
2850 END IF;
2851 END IF;
2852
2853 CASE p_Period_Type
2854 WHEN 'FII_TIME_ENT_YEAR' THEN l_Level:=119;
2855 WHEN 'FII_TIME_CAL_YEAR' THEN l_Level:=119;
2856 WHEN 'FII_TIME_ENT_QTR' THEN l_Level:=55;
2857 WHEN 'FII_TIME_CAL_QTR' THEN l_Level:=55;
2858 WHEN 'FII_TIME_ENT_PERIOD' THEN l_Level:=23;
2859 WHEN 'FII_TIME_CAL_PERIOD' THEN l_Level:=23;
2860 WHEN 'PJI_TIME_PA_PERIOD' THEN l_Level:=23;
2861 WHEN 'FII_TIME_WEEK' THEN l_Level:=11;
2862 ELSE NULL;
2863 END CASE;
2864
2865 IF p_Period_Type <> 'PJI_TIME_PA_PERIOD' THEN
2866 IF p_PA_DEBUG_MODE = 'Y' THEN
2867 Write2FWKLog('Generating records for period types other than PA Period.');
2868 END IF;
2869
2870 IF l_IS_GL_Flag IS NOT NULL THEN
2871 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2872 (ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2873 SELECT time_id, '-1', period_type_id, 1 , calendar_type
2874 FROM fii_time_cal_rpt_struct
2875 WHERE report_date = l_As_Of_Date
2876 AND bitand(record_type_id,l_Level) = record_type_id
2877 AND calendar_id = l_Calendar_Id;
2878 ELSE
2879 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2880 (ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2881 SELECT time_id, '-1', period_type_id, 1 , calendar_type
2882 FROM fii_time_rpt_struct
2883 WHERE report_date = l_As_Of_Date
2884 AND bitand(record_type_id,l_Level) = record_type_id
2885 AND calendar_id = l_Calendar_Id;
2886 END IF;
2887
2888 IF p_PA_DEBUG_MODE = 'Y' THEN
2889 Write2FWKLog('Done generating records for period types other than PA Period.');
2890 END IF;
2891 ELSE
2892 IF p_PA_DEBUG_MODE = 'Y' THEN
2893 Write2FWKLog('Generating records for PA Period period type.');
2894 END IF;
2895
2896 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2897 (ID, NAME, PERIOD_TYPE, AMOUNT_TYPE, CALENDAR_TYPE)
2898 SELECT report_date_julian
2899 , '-1', 1, 1, 'P' FROM fii_time_cal_day_mv
2900 WHERE cal_period_id = l_Period_Id
2901 AND calendar_id = l_Calendar_Id
2902 AND report_date<=l_As_Of_Date;
2903
2904 IF p_PA_DEBUG_MODE = 'Y' THEN
2905 Write2FWKLog('Done generating records for PA Period period type.');
2906 END IF;
2907 END IF;
2908
2909 IF p_Full_Period_Flag IS NOT NULL THEN
2910 IF p_PA_DEBUG_MODE = 'Y' THEN
2911 Write2FWKLog('Full Period Flag is set.');
2912 END IF;
2913 CASE l_Level
2914 WHEN 119 THEN
2915 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2916 (ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2917 VALUES (l_Year_ID, '-1', 2, 128, l_Calendar_Type_Sum);
2918 WHEN 55 THEN
2919 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2920 (ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2921 VALUES (l_Qtr_ID, '-1', 2, 64, l_Calendar_Type_Sum);
2922 WHEN 23 THEN
2923 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2924 (ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2925 VALUES (l_Period_ID, '-1', 2, 32, l_Calendar_Type_Sum);
2926 WHEN 11 THEN
2927 INSERT INTO PJI_PMV_TCMP_DIM_TMP
2928 (ID, NAME, AMOUNT_TYPE, PERIOD_TYPE, CALENDAR_TYPE)
2929 VALUES (l_Week_ID, '-1', 2, 16, 'E');
2930 END CASE;
2931
2932 IF p_PA_DEBUG_MODE = 'Y' THEN
2933 Write2FWKLog('Full Period entries created.');
2934 ENd IF;
2935 END IF;
2936
2937 IF p_PA_DEBUG_MODE = 'Y' THEN
2938 Write2FWKLog('Exiting Convert_DBI_NViewBY_AS_OF_DATE...');
2939 END IF;
2940 EXCEPTION
2941 WHEN NO_DATA_FOUND THEN
2942 NULL;
2943 WHEN OTHERS THEN
2944 g_SQL_Error_Msg:=SQLERRM();
2945 IF p_PA_DEBUG_MODE = 'Y' THEN
2946 Write2FWKLog(g_SQL_Error_Msg, 3);
2947 END IF;
2948 RAISE;
2949 END Convert_DBI_NViewBY_AS_OF_DATE;
2950
2951 /*
2952 ** ----------------------------------------------------------
2953 ** Procedure: Convert_ITD_NViewBY_AS_OF_DATE
2954 ** The procedure creates time records for ITD reports based
2955 ** on an as of date when the view by dimension is not time.
2956 ** The API caters to additional logic based on following
2957 ** parameters:
2958 ** Parse Prior: If the flag is passed as 'Y', the API stamps
2959 ** prior_id in the time tables.
2960 ** Comparator: For DBI reports this api is called again with
2961 ** Comparator set as 'D' and a different as_of_date
2962 ** (based on comparator choosen in the PMV report).
2963 ** ----------------------------------------------------------
2964 */
2965 Procedure Convert_ITD_NViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
2966 , p_Period_Type VARCHAR2
2967 , p_Parse_Prior VARCHAR2 DEFAULT NULL
2968 , p_Comparator VARCHAR2 DEFAULT 'I'
2969 , p_Calendar_ID NUMBER DEFAULT NULL)
2970 IS
2971 l_Period_Id NUMBER;
2972 l_Week_ID NUMBER;
2973 l_Qtr_Id NUMBER;
2974 l_Year_Id NUMBER;
2975 l_Prior_Period_Id NUMBER;
2976
2977 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date,'j');
2978 l_Prior_As_Of_Date DATE;
2979
2980 l_Period_Type VARCHAR2(150):=p_Period_Type;
2981 l_Level NUMBER;
2982 l_IS_GL_Flag VARCHAR2(1);
2983
2984 l_Calendar_Id NUMBER;
2985 BEGIN
2986 IF p_PA_DEBUG_MODE = 'Y' THEN
2987 Write2FWKLog('Entering Convert_ITD_NViewBY_AS_OF_DATE...','Convert_ITD_NViewBY_AS_OF_DATE');
2988 END IF;
2989
2990 IF p_Parse_Prior IS NOT NULL THEN
2991 BEGIN
2992 IF p_PA_DEBUG_MODE = 'Y' THEN
2993 Write2FWKLog('Determining Prior Year...');
2994 END IF;
2995 -- Note: Please confirm this change with dima.
2996 l_Prior_As_Of_Date:=TO_DATE(Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, 'YEARLY'),'j');
2997 EXCEPTION
2998 WHEN NO_DATA_FOUND THEN
2999 IF p_PA_DEBUG_MODE = 'Y' THEN
3000 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
3001 END IF;
3002 END;
3003 END IF;
3004
3005 IF p_PA_DEBUG_MODE = 'Y' THEN
3006 Write2FWKLog('Done with computing Prior Year...');
3007 END IF;
3008
3009 IF l_Period_Type LIKE '%PA%' THEN
3010 l_Calendar_Id:=G_PA_Calendar_ID;
3011 l_IS_GL_Flag:='Y';
3012 IF p_PA_DEBUG_MODE = 'Y' THEN
3013 Write2FWKLog('PA calender is selected.');
3014 END IF;
3015 ELSIF p_Period_Type LIKE '%CAL%' THEN
3016 l_Calendar_Id:=G_GL_Calendar_ID;
3017 l_IS_GL_Flag:='Y';
3018 IF p_PA_DEBUG_MODE = 'Y' THEN
3019 Write2FWKLog('GL calender is selected.');
3020 END IF;
3021 ELSE
3022 l_Calendar_Id:=-1;
3023 IF p_PA_DEBUG_MODE = 'Y' THEN
3024 Write2FWKLog('Ent calender is selected.');
3025 END IF;
3026 END IF;
3027
3028 IF l_Period_Type = 'PJI_TIME_PA_PERIOD' THEN
3029 IF p_PA_DEBUG_MODE = 'Y' THEN
3030 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
3031 END IF;
3032 SELECT cal_period_id
3033 INTO l_Period_Id
3034 FROM fii_time_cal_day_mv
3035 WHERE
3036 report_date = l_As_Of_Date
3037 AND calendar_id = l_Calendar_Id;
3038 END IF;
3039
3040 IF p_Period_Type = 'PJI_TIME_PA_PERIOD' AND p_Parse_Prior = 'Y' AND l_Prior_As_Of_Date IS NOT NULL THEN
3041 IF p_PA_DEBUG_MODE = 'Y' THEN
3042 Write2FWKLog('Selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
3043 END IF;
3044
3045 SELECT cal_period_id
3046 INTO l_Prior_Period_Id
3047 FROM fii_time_cal_day_mv
3048 WHERE 1 = 1
3049 AND report_date = l_Prior_As_Of_Date
3050 AND calendar_id = l_Calendar_Id;
3051
3052 IF p_PA_DEBUG_MODE = 'Y' THEN
3053 Write2FWKLog('Done selecting Prior Period ID from FII_TIME_CAL_DAY_MV for PA Period Type.');
3054 END IF;
3055 END IF;
3056
3057 IF p_Period_Type <> 'PJI_TIME_PA_PERIOD' THEN
3058 IF p_PA_DEBUG_MODE = 'Y' THEN
3059 Write2FWKLog('Generating records for period types other than PA Period.');
3060 END IF;
3061
3062 IF l_IS_GL_Flag IS NOT NULL THEN
3063 INSERT INTO PJI_PMV_ITD_DIM_TMP
3064 (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3065 SELECT time_id, '-1' , '-1' , period_type_id, p_Comparator, calendar_type
3066 FROM fii_time_cal_rpt_struct
3067 WHERE report_date = l_As_Of_Date
3068 AND bitand(record_type_id,1143) = record_type_id
3069 AND calendar_id = l_Calendar_Id;
3070 ELSE
3071 INSERT INTO PJI_PMV_ITD_DIM_TMP
3072 (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3073 SELECT time_id, '-1' , '-1' , period_type_id, p_Comparator, calendar_type
3074 FROM fii_time_rpt_struct
3075 WHERE report_date = l_As_Of_Date
3076 AND bitand(record_type_id,1143) = record_type_id
3077 AND calendar_id = l_Calendar_Id;
3078 END IF;
3079
3080 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
3081 IF p_PA_DEBUG_MODE = 'Y' THEN
3082 Write2FWKLog('Generating prior id records for period types other than PA Period.');
3083 END IF;
3084 IF l_IS_GL_Flag IS NOT NULL THEN
3085 INSERT INTO PJI_PMV_ITD_DIM_TMP
3086 (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3087 SELECT time_id, '-1', '-1', period_type_id, p_Comparator, calendar_type
3088 FROM fii_time_cal_rpt_struct
3089 WHERE report_date = l_Prior_As_Of_Date
3090 AND bitand(record_type_id,1143) = record_type_id
3091 AND calendar_id = l_Calendar_Id;
3092 ELSE
3093 INSERT INTO PJI_PMV_ITD_DIM_TMP
3094 (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3095 SELECT time_id, '-1', '-1', period_type_id, p_Comparator, calendar_type
3096 FROM fii_time_rpt_struct
3097 WHERE report_date = l_Prior_As_Of_Date
3098 AND bitand(record_type_id,1143) = record_type_id
3099 AND calendar_id = l_Calendar_Id;
3100 END IF;
3101 END IF;
3102 IF p_PA_DEBUG_MODE = 'Y' THEN
3103 Write2FWKLog('Done generating records for period types other than PA Period.');
3104 END IF;
3105 ELSE
3106 IF p_PA_DEBUG_MODE = 'Y' THEN
3107 Write2FWKLog('Generating records for PA Period period type.');
3108 END IF;
3109
3110 INSERT INTO PJI_PMV_ITD_DIM_TMP
3111 (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3112 SELECT report_date_julian
3113 , '-1', '-1', 1, p_Comparator, 'P' FROM fii_time_cal_day_mv
3114 WHERE cal_period_id = l_Period_Id
3115 AND calendar_id = l_Calendar_Id
3116 AND report_date<=l_As_Of_Date;
3117
3118 INSERT INTO PJI_PMV_ITD_DIM_TMP
3119 (ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3120 SELECT time_id, '-1', '-1', period_type_id, p_Comparator, 'P'
3121 FROM fii_time_cal_rpt_struct
3122 WHERE report_date = l_As_Of_Date
3123 AND bitand(record_type_id,1143) = record_type_id
3124 AND calendar_id = l_Calendar_Id
3125 AND period_type_id > 16;
3126
3127 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
3128
3129 IF p_PA_DEBUG_MODE = 'Y' THEN
3130 Write2FWKLog('Generating prior id records for PA Period period type.');
3131 END IF;
3132
3133 INSERT INTO PJI_PMV_ITD_DIM_TMP
3134 (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3135 SELECT report_date_julian
3136 , '-1', '-1', 1, p_Comparator, 'P' FROM fii_time_cal_day_mv
3137 WHERE cal_period_id = l_Prior_Period_Id
3138 AND calendar_id = l_Calendar_Id
3139 AND report_date<=l_Prior_As_Of_Date;
3140
3141 INSERT INTO PJI_PMV_ITD_DIM_TMP
3142 (PRIOR_ID, NAME, ORDER_BY_ID, PERIOD_TYPE, COMPARATOR_TYPE, CALENDAR_TYPE)
3143 SELECT time_id, '-1', '-1', period_type_id, p_Comparator, 'P'
3144 FROM fii_time_cal_rpt_struct
3145 WHERE report_date = l_As_Of_Date
3146 AND bitand(record_type_id,1143) = record_type_id
3147 AND calendar_id = l_Calendar_Id
3148 AND period_type_id > 16;
3149
3150 END IF;
3151
3152 IF p_PA_DEBUG_MODE = 'Y' THEN
3153 Write2FWKLog('Done generating records for PA Period period type.');
3154 END IF;
3155 END IF;
3156 IF p_PA_DEBUG_MODE = 'Y' THEN
3157 Write2FWKLog('Exiting Convert_ITD_NViewBY_AS_OF_DATE...');
3158 END IF;
3159 EXCEPTION
3160 WHEN NO_DATA_FOUND THEN
3161 NULL;
3162 WHEN OTHERS THEN
3163 g_SQL_Error_Msg:=SQLERRM();
3164 Write2FWKLog(g_SQL_Error_Msg, 3);
3165 RAISE;
3166 END Convert_ITD_NViewBY_AS_OF_DATE;
3167
3168 /*
3169 ** Convert_Time API's for View BY TIME
3170 */
3171
3172 /*
3173 ** ----------------------------------------------------------
3174 ** Procedure: Convert_ViewBY_AS_OF_DATE
3175 ** The procedure creates time records for pmv reports when
3176 ** time is selected as the viewby dimension.
3177 ** The API caters to additional logic based on following
3178 ** parameters:
3179 ** Parse Prior: If the flag is passed as 'Y', the API stamps
3180 ** prior_id in the time tables.
3181 ** Report Type: If a value is passed then the time records are
3182 ** bound by year(fiscal/enterprize).
3183 ** ----------------------------------------------------------
3184 */
3185 Procedure Convert_ViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
3186 , p_Period_Type VARCHAR2
3187 , p_Report_Type VARCHAR2
3188 , p_Parse_Prior VARCHAR2 DEFAULT NULL
3189 , p_Full_Period_Flag VARCHAR2 DEFAULT NULL)
3190 AS
3191 l_Start_Time DATE;
3192 l_End_Time DATE;
3193
3194 l_Week_ID NUMBER;
3195 l_Week_Name VARCHAR2(150);
3196 l_Week_Start_Date DATE;
3197 l_Period_ID NUMBER;
3198 l_Period_Name VARCHAR2(150);
3199 l_Period_Start_Date DATE;
3200 l_Qtr_ID NUMBER;
3201 l_Qtr_Name VARCHAR2(150);
3202 l_Qtr_Start_Date DATE;
3203 l_Year_ID NUMBER;
3204 l_Year_Name VARCHAR2(150);
3205 l_Year_Start_Date DATE;
3206
3207 l_Level NUMBER;
3208 l_Def_View_BY VARCHAR2(150);
3209 l_Def_View_BY_ID NUMBER;
3210 l_Calendar_ID NUMBER;
3211 l_Calendar_Type_Sum VARCHAR2(1);
3212
3213
3214 l_IS_GL_Flag VARCHAR2(1);
3215
3216 l_Period_Type VARCHAR2(150):=p_Period_Type;
3217 l_As_Of_Date DATE := TO_DATE(p_As_Of_Date,'j');
3218 l_Prior_As_Of_Date DATE;
3219 l_Prior_Period_Id NUMBER;
3220 BEGIN
3221 IF p_PA_DEBUG_MODE = 'Y' THEN
3222 Write2FWKLog('Entering Convert_ViewBY_AS_OF_DATE...','Convert_ViewBY_AS_OF_DATE');
3223 END IF;
3224
3225 IF p_Parse_Prior IS NOT NULL THEN
3226 BEGIN
3227 IF p_PA_DEBUG_MODE = 'Y' THEN
3228 Write2FWKLog('Determining Prior Year...');
3229 END IF;
3230 -- Note: Please confirm this change with dima.
3231 l_Prior_As_Of_Date:=TO_DATE(Convert_AS_OF_DATE(p_As_Of_Date, l_Period_Type, 'YEARLY'),'j');
3232 EXCEPTION
3233 WHEN NO_DATA_FOUND THEN
3234 IF p_PA_DEBUG_MODE = 'Y' THEN
3235 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
3236 END IF;
3237 END;
3238 END IF;
3239
3240 IF p_PA_DEBUG_MODE = 'Y' THEN
3241 Write2FWKLog('Done with computing as of date for Prior Year...');
3242 END IF;
3243
3244 IF l_Period_Type LIKE '%PA%' THEN
3245 l_Calendar_Id:=G_PA_Calendar_ID;
3246 l_IS_GL_Flag:='Y';
3247 l_Calendar_Type_Sum:='P';
3248 IF p_PA_DEBUG_MODE = 'Y' THEN
3249 Write2FWKLog('PA calender is selected.');
3250 END IF;
3251 ELSIF l_Period_Type LIKE '%CAL%' THEN
3252 l_Calendar_Id:=G_GL_Calendar_ID;
3253 l_IS_GL_Flag:='Y';
3254 l_Calendar_Type_Sum:='G';
3255 IF p_PA_DEBUG_MODE = 'Y' THEN
3256 Write2FWKLog('GL calender is selected.');
3257 END IF;
3258 ELSE
3259 l_Calendar_Type_Sum:='E';
3260 l_Calendar_Id:=-1;
3261 IF p_PA_DEBUG_MODE = 'Y' THEN
3262 Write2FWKLog('Ent calender is selected.');
3263 END IF;
3264 END IF;
3265
3266 IF l_IS_GL_Flag IS NULL THEN
3267 IF p_PA_DEBUG_MODE = 'Y' THEN
3268 Write2FWKLog('Selecting from FII_TIME_DAY.');
3269 END IF;
3270 SELECT
3271 day.ent_period_id
3272 , prd.name
3273 , day.ent_period_start_date
3274 , day.week_id
3275 , wek.name
3276 , day.week_start_date
3277 , day.ent_qtr_id
3278 , qtr.name
3279 , day.ent_qtr_start_date
3280 , day.ent_year_id
3281 , yer.name
3282 , day.ent_year_start_date
3283 INTO
3284 l_Period_Id
3285 , l_Period_Name
3286 , l_Period_Start_Date
3287 , l_Week_Id
3288 , l_Week_Name
3289 , l_Week_Start_Date
3290 , l_Qtr_Id
3291 , l_Qtr_Name
3292 , l_Qtr_Start_Date
3293 , l_Year_Id
3294 , l_Year_Name
3295 , l_Year_Start_Date
3296 FROM fii_time_day day
3297 , fii_time_week wek
3298 , fii_time_ent_period prd
3299 , fii_time_ent_qtr qtr
3300 , fii_time_ent_year yer
3301 WHERE
3302 report_date = l_As_Of_Date
3303 AND wek.week_id = day.week_id
3304 AND prd.ent_period_id = day.ent_period_id
3305 AND qtr.ent_qtr_id = day.ent_qtr_id
3306 AND yer.ent_year_id = day.ent_year_id;
3307 ELSE
3308 IF p_PA_DEBUG_MODE = 'Y' THEN
3309 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV.');
3310 END IF;
3311 SELECT
3312 day.cal_period_id
3313 , prd.name
3314 , day.cal_period_start_date
3315 , day.cal_qtr_id
3316 , qtr.name
3317 , day.cal_qtr_start_date
3318 , day.cal_year_id
3319 , yer.name
3320 , day.cal_year_start_date
3321 INTO
3322 l_Period_Id
3323 , l_Period_Name
3324 , l_Period_Start_Date
3325 , l_Qtr_Id
3326 , l_Qtr_Name
3327 , l_Qtr_Start_Date
3328 , l_Year_Id
3329 , l_Year_Name
3330 , l_Year_Start_Date
3331 FROM fii_time_cal_day_mv day
3332 , fii_time_cal_period prd
3333 , fii_time_cal_qtr qtr
3334 , fii_time_cal_year yer
3335 WHERE
3336 report_date = l_As_Of_Date
3337 AND day.calendar_id = l_Calendar_Id
3338 AND prd.cal_period_id = day.cal_period_id
3339 AND qtr.cal_qtr_id = day.cal_qtr_id
3340 AND yer.cal_year_id = day.cal_year_id;
3341 END IF;
3342
3343 IF p_PA_DEBUG_MODE = 'Y' THEN
3344 Write2FWKLog('Done selecting from FII_TIME_???.');
3345 END IF;
3346
3347 IF p_Report_Type IS NOT NULL THEN
3348 l_End_Time:=l_Year_Start_Date-1;
3349 ELSIF l_Period_Type = 'FII_TIME_ENT_YEAR' OR l_Period_Type = 'FII_TIME_CAL_YEAR' THEN
3350 l_End_Time:=TO_DATE(Convert_AS_OF_DATE(Convert_AS_OF_DATE(p_As_Of_Date, l_Period_Type, 'YEARLY'), l_Period_Type, 'YEARLY'),'j');
3351 IF l_End_Time IS NULL THEN
3352 l_End_Time:=PJI_UTILS.GET_EXTRACTION_START_DATE;
3353 END IF;
3354 ELSIF l_Prior_As_Of_Date IS NOT NULL THEN
3355 l_End_Time:=l_Prior_As_Of_Date;
3356 ELSE
3357 l_End_Time:=TO_DATE(Convert_AS_OF_DATE(p_As_Of_Date, l_Period_Type, 'YEARLY'),'j');
3358 IF l_End_Time IS NULL THEN
3359 l_End_Time:=PJI_UTILS.GET_EXTRACTION_START_DATE;
3360 END IF;
3361 END IF;
3362
3363 IF ( l_Period_Type = 'FII_TIME_WEEK' OR l_Period_Type = 'PJI_TIME_PA_PERIOD') AND l_End_Time < (l_As_Of_Date)-91 THEN
3364 l_End_Time:=(l_As_Of_Date)-91;
3365 END IF;
3366
3367 IF p_PA_DEBUG_MODE = 'Y' THEN
3368 Write2FWKLog('Determining the l_Level, l_Start_Time, l_Def_View_BY, l_Def_View_BY_ID.');
3369 END IF;
3370
3371 CASE l_Period_Type
3372 WHEN 'FII_TIME_ENT_YEAR' THEN
3373 l_Level:=119;
3374 l_Start_Time:=l_Year_Start_Date;
3375 l_Def_View_BY:=l_Year_Name;
3376 l_Def_View_BY_ID:=l_Year_ID;
3377 WHEN 'FII_TIME_CAL_YEAR' THEN
3378 l_Level:=119;
3379 l_Start_Time:=l_Year_Start_Date;
3380 l_Def_View_BY:=l_Year_Name;
3381 l_Def_View_BY_ID:=l_Year_ID;
3382 WHEN 'FII_TIME_ENT_QTR' THEN
3383 l_Level:=55;
3384 l_Start_Time:=l_Qtr_Start_Date;
3385 l_Def_View_BY:=l_Qtr_Name;
3386 l_Def_View_BY_ID:=l_Qtr_ID;
3387 WHEN 'FII_TIME_CAL_QTR' THEN
3388 l_Level:=55;
3389 l_Start_Time:=l_Qtr_Start_Date;
3390 l_Def_View_BY:=l_Qtr_Name;
3391 l_Def_View_BY_ID:=l_Qtr_ID;
3392 WHEN 'FII_TIME_ENT_PERIOD' THEN
3393 l_Level:=23;
3394 l_Start_Time:=l_Period_Start_Date;
3395 l_Def_View_BY:=l_Period_Name;
3396 l_Def_View_BY_ID:=l_Period_ID;
3397 WHEN 'FII_TIME_CAL_PERIOD' THEN
3398 l_Level:=23;
3399 l_Start_Time:=l_Period_Start_Date;
3400 l_Def_View_BY:=l_Period_Name;
3401 l_Def_View_BY_ID:=l_Period_ID;
3402 WHEN 'PJI_TIME_PA_PERIOD' THEN
3403 l_Level:=23;
3404 l_Start_Time:=l_Period_Start_Date;
3405 l_Def_View_BY:=l_Period_Name;
3406 l_Def_View_BY_ID:=l_Period_ID;
3407 WHEN 'FII_TIME_WEEK' THEN l_Level:=11;
3408 l_Start_Time:=l_Week_Start_Date;
3409 l_Def_View_BY:=l_Week_Name;
3410 l_Def_View_BY_ID:=l_Week_ID;
3411 ELSE NULL;
3412 END CASE;
3413
3414 IF p_PA_DEBUG_MODE = 'Y' THEN
3415 Write2FWKLog('l_Level : '||l_Level);
3416 Write2FWKLog('l_Start_Time : '||l_Start_Time);
3417 Write2FWKLog('l_End_Time : '||l_End_Time);
3418 Write2FWKLog('l_Def_View_BY : '||l_Def_View_BY);
3419 Write2FWKLog('l_Def_View_BY_ID : '||l_Def_View_BY_ID);
3420 END IF;
3421
3422 IF l_Period_Type <> 'PJI_TIME_PA_PERIOD' THEN
3423 IF p_PA_DEBUG_MODE = 'Y' THEN
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
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
3441 AND bitand(record_type_id,l_Level) = record_type_id
3442 AND calendar_id = l_Calendar_Id;
3443 END IF;
3444
3445 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
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
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
3463 AND bitand(record_type_id,l_Level) = record_type_id
3464 AND calendar_id = l_Calendar_Id;
3465 END IF;
3466 END IF;
3467 IF p_PA_DEBUG_MODE = 'Y' THEN
3468 Write2FWKLog('Done generating records for period types other than PA Period.');
3469 END IF;
3470 ELSE
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
3480 AND calendar_id = l_Calendar_Id
3481 AND report_date<=l_As_Of_Date;
3482
3483 IF p_Parse_Prior IS NOT NULL AND l_Prior_As_Of_Date IS NOT NULL THEN
3484
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
3494 AND calendar_id = l_Calendar_Id
3495 AND report_date<=l_Prior_As_Of_Date;
3496
3497 END IF;
3498
3499 IF p_PA_DEBUG_MODE = 'Y' THEN
3500 Write2FWKLog('Done generating records for PA Period period type.');
3501 END IF;
3502 END IF;
3503
3504 IF p_Full_Period_Flag IS NOT NULL THEN
3505 IF p_PA_DEBUG_MODE = 'Y' THEN
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
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
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
3527 IF p_PA_DEBUG_MODE = 'Y' THEN
3528 Write2FWKLog('Full Period entries created.');
3529 ENd IF;
3530 END IF;
3531
3532 CASE l_Period_Type
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
3542 AND start_date < l_Start_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
3552 AND start_date < l_Start_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
3562 AND start_date < l_Start_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
3572 AND start_date < l_Start_Time
3573 AND calendar_id = l_Calendar_Id;
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
3583 AND start_date < l_Start_Time
3584 AND calendar_id = l_Calendar_Id;
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
3594 AND start_date < l_Start_Time
3595 AND calendar_id = l_Calendar_Id;
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
3605 AND start_date < l_Start_Time
3606 AND calendar_id = l_Calendar_Id;
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
3616 AND start_date < l_Start_Time;
3617 END CASE;
3618
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)
3628 WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
3629 END)
3630 WHERE AMOUNT_TYPE = 2
3631 OR AMOUNT_TYPE IS NULL;
3632 IF p_PA_DEBUG_MODE = 'Y' THEN
3633 Write2FWKLog('Prior ID is updated.');
3634 END IF;
3635 END IF;
3636 IF p_PA_DEBUG_MODE = 'Y' THEN
3637 Write2FWKLog('Exiting Convert_ViewBY_AS_OF_DATE...');
3638 END IF;
3639 EXCEPTION
3640 WHEN OTHERS THEN
3641 g_SQL_Error_Msg:=SQLERRM();
3642 IF p_PA_DEBUG_MODE = 'Y' THEN
3643 Write2FWKLog(g_SQL_Error_Msg, 3);
3644 END IF;
3645 RAISE;
3646 END Convert_ViewBY_AS_OF_DATE;
3647
3648 Procedure Convert_ITD_ViewBY_AS_OF_DATE(p_As_Of_Date NUMBER
3649 , p_Period_Type VARCHAR2
3650 , p_Parse_Prior VARCHAR2 DEFAULT NULL)
3651 AS
3652 l_As_Of_Date DATE;
3653 l_Level NUMBER;
3654 BEGIN
3655 IF p_PA_DEBUG_MODE = 'Y' THEN
3656 Write2FWKLog('Entering Convert_ITD_ViewBY_AS_OF_DATE...','Convert_ITD_ViewBY_AS_OF_DATE');
3657 Write2FWKLog('Determining the period beyond which ITD has to be generated.');
3658 END IF;
3659
3660 CASE p_Period_Type
3661 WHEN 'FII_TIME_ENT_YEAR' THEN
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;
3734 END CASE;
3735
3736 IF p_PA_DEBUG_MODE = 'Y' THEN
3737 Write2FWKLog('Done determining the period beyond which ITD has to be generated.');
3738 Write2FWKLog('l_Level :'||l_Level);
3739 Write2FWKLog('l_As_Of_Date :'||l_As_Of_Date);
3740 Write2FWKLog('Before Calling Convert_ITD_NViewBY_AS_OF_DATE...');
3741 END IF;
3742
3743 Convert_ITD_NViewBY_AS_OF_DATE(p_As_Of_Date=>TO_CHAR(l_As_Of_Date,'j')
3744 , p_Period_Type=>p_Period_Type
3745 , p_Parse_Prior=>p_Parse_Prior
3746 , p_Comparator=>NULL
3747 , p_Calendar_ID=>NULL);
3748 IF p_PA_DEBUG_MODE = 'Y' THEN
3749 Write2FWKLog('After Calling Convert_ITD_NViewBY_AS_OF_DATE...');
3750 Write2FWKLog('Exiting Convert_ITD_ViewBY_AS_OF_DATE...');
3751 END IF;
3752 EXCEPTION
3753 WHEN OTHERS THEN
3754 g_SQL_Error_Msg:=SQLERRM();
3755 IF p_PA_DEBUG_MODE = 'Y' THEN
3756 Write2FWKLog(g_SQL_Error_Msg, 3);
3757 END IF;
3758 RAISE;
3759 END Convert_ITD_ViewBY_AS_OF_DATE;
3760
3761 /*
3762 ** ----------------------------------------------------------
3763 ** Procedure: Convert_Time
3764 ** The procedure creates time records for non as_of_date
3765 ** based reports. Currently, this api supports only
3766 ** availability reports.
3767 ** ----------------------------------------------------------
3768 */
3769 Procedure Convert_Time(p_From_Time_ID NUMBER
3770 , p_To_Time_ID NUMBER
3771 , p_Period_Type VARCHAR2
3772 , p_View_BY VARCHAR2
3773 , p_Parse_Prior VARCHAR2 DEFAULT NULL)
3774 AS
3775 l_Hierarchy_Version_ID NUMBER:=Get_Hierarchy_Version_ID;
3776 l_Calendar_Id NUMBER;
3777 l_Start_Date DATE:=TO_DATE(p_From_Time_ID,'j');
3778 l_End_Date DATE:=TO_DATE(p_To_Time_ID,'j');
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.');
3788 ELSIF p_Period_Type LIKE '%CAL%' THEN
3789 l_Calendar_Id:=G_GL_Calendar_ID;
3790 Write2FWKLog('GL calender is selected.');
3791 END IF;
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;
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;
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;
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
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
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
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
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;
3854 ELSE
3855 NULL;
3856 END CASE;
3857
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)
3867 WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
3868 END)
3869 WHERE period_type<>1;
3870 Write2FWKLog('Prior ID is updated.');
3871 END IF;
3872 Write2FWKLog('Exiting Convert_Time...');
3873 EXCEPTION
3874 WHEN OTHERS THEN
3875 g_SQL_Error_Msg:=SQLERRM();
3876 Write2FWKLog(g_SQL_Error_Msg, 3);
3877 RAISE;
3878 END Convert_Time;
3879
3880 /*
3881 ** ----------------------------------------------------------
3882 ** Procedure: Convert_Time
3883 ** This API is a public api exposed to the pmv report
3884 ** developers. The API determines the various time records
3885 ** to be created and calls the appropriate time api's.
3886 ** ----------------------------------------------------------
3887 */
3888 Procedure Convert_Time(p_As_Of_Date NUMBER
3889 , p_Period_Type VARCHAR2
3890 , p_View_BY VARCHAR2
3891 , p_Parse_Prior VARCHAR2 DEFAULT NULL
3892 , p_Report_Type VARCHAR2 DEFAULT NULL
3893 , p_Comparator VARCHAR2 DEFAULT NULL
3894 , p_Parse_ITD VARCHAR2 DEFAULT NULL
3895 , p_Full_Period_Flag VARCHAR2 DEFAULT NULL)
3896 AS
3897 l_As_Of_Date NUMBER;
3898 BEGIN
3899
3900 IF p_PA_DEBUG_MODE = 'Y' THEN
3901 Write2FWKLog('Entering Convert_Time...','Convert_Time');
3902
3903 Write2FWKLog('Parameters passed by table function :');
3904 Write2FWKLog(' p_As_Of_Date: '||p_As_Of_Date);
3905 Write2FWKLog(' p_Period_Type: '||p_Period_Type);
3906 Write2FWKLog(' p_View_BY: '||p_View_BY);
3907 Write2FWKLog(' p_Parse_Prior: '||p_Parse_Prior);
3908 Write2FWKLog(' p_Report_Type: '||p_Report_Type);
3909 Write2FWKLog(' p_Comparator: '||p_Comparator);
3910 Write2FWKLog(' p_Parse_ITD: '||p_Parse_ITD);
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;
3920 IF p_Report_Type = 'DBI' THEN
3921 DELETE PJI_PMV_TCMP_DIM_TMP;
3922 END IF;
3923
3924 IF p_PA_DEBUG_MODE = 'Y' THEN
3925 Write2FWKLog('Done clearing time temporary tables...');
3926 END IF;
3927
3928 IF p_As_Of_Date IS NOT NULL AND p_Report_Type = 'DBI' THEN
3929 IF p_PA_DEBUG_MODE = 'Y' THEN
3930 Write2FWKLog('Derive the as_of_date for DBI reports.');
3931 END IF;
3932 l_As_Of_Date:=Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Comparator);
3933 IF p_PA_DEBUG_MODE = 'Y' THEN
3934 Write2FWKLog('Derived the as_of_date: '||l_As_Of_Date||' for DBI reports.');
3935 END IF;
3936 END IF;
3937
3938 IF p_View_BY = 'TM' THEN
3939 IF p_PA_DEBUG_MODE = 'Y' THEN
3940 Write2FWKLog('Calling Convert_ViewBY_AS_OF_DATE as Viewby dimension is time.');
3941 END IF;
3942 IF p_Report_Type = 'FISCAL' THEN
3943 IF p_PA_DEBUG_MODE = 'Y' THEN
3944 Write2FWKLog('The time dimension values should be restricted by fiscal/enterprize year.');
3945 END IF;
3946 Convert_ViewBY_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Report_Type, p_Parse_Prior, p_Full_Period_Flag);
3947 ELSE
3948 IF p_PA_DEBUG_MODE = 'Y' THEN
3949 Write2FWKLog('The time dimension values should not be restricted by fiscal/enterprize year.');
3950 END IF;
3951 Convert_ViewBY_AS_OF_DATE(p_As_Of_Date, p_Period_Type, NULL, p_Parse_Prior, p_Full_Period_Flag);
3952 END IF;
3953 ELSE
3954 IF p_PA_DEBUG_MODE = 'Y' THEN
3955 Write2FWKLog('Calling Convert_NViewBY_AS_OF_DATE as time is not the current Viewby dimension.');
3956 END IF;
3957 Convert_NViewBY_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Parse_Prior, p_Full_Period_Flag);
3958 END IF;
3959
3960 IF p_PA_DEBUG_MODE = 'Y' THEN
3961 Write2FWKLog('Done calling Convert_?ViewBY_AS_OF_DATE.');
3962 END IF;
3963
3964 IF p_Parse_ITD = 'Y' AND p_View_BY = 'TM' THEN
3965 IF p_PA_DEBUG_MODE = 'Y' THEN
3966 Write2FWKLog('Calling Convert_ITD_ViewBY_AS_OF_DATE.');
3967 END IF;
3968 Convert_ITD_ViewBY_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Parse_Prior);
3969 IF p_PA_DEBUG_MODE = 'Y' THEN
3970 Write2FWKLog('Done calling Convert_ITD_ViewBY_AS_OF_DATE.');
3971 END IF;
3972 ELSIF p_Parse_ITD = 'Y' THEN
3973 IF p_PA_DEBUG_MODE = 'Y' THEN
3974 Write2FWKLog('Calling Convert_ITD_NViewBY_AS_OF_DATE.');
3975 END IF;
3976 Convert_ITD_NViewBY_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Parse_Prior,'I');
3977 IF p_Report_Type = 'DBI' THEN
3978 IF p_PA_DEBUG_MODE = 'Y' THEN
3979 Write2FWKLog('Calling Convert_ITD_NViewBY_AS_OF_DATE with DBI as of date.');
3980 END IF;
3981 Convert_ITD_NViewBY_AS_OF_DATE(l_As_Of_Date, p_Period_Type, p_Parse_Prior,'D');
3982 END IF;
3983 IF p_PA_DEBUG_MODE = 'Y' THEN
3984 Write2FWKLog('Done calling Convert_ITD_NViewBY_AS_OF_DATE.');
3985 END IF;
3986 END IF;
3987
3988
3989 IF p_Report_Type = 'DBI' THEN
3990 IF p_PA_DEBUG_MODE = 'Y' THEN
3991 Write2FWKLog('Calling Convert_DBI_NViewBY_AS_OF_DATE.');
3992 END IF;
3993 Convert_DBI_NViewBY_AS_OF_DATE(l_As_Of_Date, p_Period_Type, p_Comparator, p_Full_Period_Flag);
3994 IF p_PA_DEBUG_MODE = 'Y' THEN
3995 Write2FWKLog('Done calling Convert_DBI_NViewBY_AS_OF_DATE.');
3996 END IF;
3997 END IF;
3998
3999 IF p_PA_DEBUG_MODE = 'Y' THEN
4000 Write2FWKLog('Exiting Convert_Time...');
4001 END IF;
4002
4003 EXCEPTION
4004 WHEN OTHERS THEN
4005 g_SQL_Error_Msg:=SQLERRM();
4006 IF p_PA_DEBUG_MODE = 'Y' THEN
4007 Write2FWKLog(g_SQL_Error_Msg, 3);
4008 END IF;
4009 RAISE;
4010 END Convert_Time;
4011
4012 /*
4013 ** Time API's specifically catering to resource team.
4014 */
4015
4016
4017 /* -----------------------------------------------------------
4018 ** Procedure: Convert_Expected_Time
4019 ** The procedure creates time records based on an as of date
4020 ** when the view by dimension is not time based on the period
4021 ** type (whether WTD, PTD, QTD, YTD, or ITD). This API
4022 ** distinguish the time records which contain the Actual
4023 ** or Scheduled Amount Type based on the last summarized date.
4024 ** It also caters to additional logic based on following:
4025 ** Parse Prior: If the flag is passed as 'Y', the API stamps
4026 ** prior_id in the time tables.
4027 **
4028 ** History:
4029 ** 20-JUN-02 Adzilah Abd. Created
4030 ** -----------------------------------------------------------*/
4031
4032 PROCEDURE Convert_Expected_Time(p_as_of_date NUMBER
4033 ,p_period_type VARCHAR2
4034 ,p_parse_prior VARCHAR2 DEFAULT NULL)
4035 IS
4036 l_period_id NUMBER;
4037 l_period_start_date DATE;
4038 l_qtr_id NUMBER;
4039 l_qtr_start_date DATE;
4040 l_year_id NUMBER;
4041 l_year_start_date DATE;
4042 l_week_id NUMBER;
4043 l_week_start_date DATE;
4044
4045 l_period_id_tmp NUMBER;
4046 l_period_start_date_tmp DATE;
4047 l_qtr_id_tmp NUMBER;
4048 l_qtr_start_date_tmp DATE;
4049 l_year_id_tmp NUMBER;
4050 l_year_start_date_tmp DATE;
4051 l_week_id_tmp NUMBER;
4052 l_week_start_date_tmp DATE;
4053
4054 l_period_id_s NUMBER;
4055 l_period_start_date_s DATE;
4056 l_qtr_id_s NUMBER;
4057 l_qtr_start_date_s DATE;
4058 l_year_id_s NUMBER;
4059 l_year_start_date_s DATE;
4060 l_week_id_s NUMBER;
4061 l_week_start_date_s DATE;
4062
4063 l_level NUMBER;
4064 l_is_GL VARCHAR2(1);
4065 l_period_type VARCHAR2(150) := p_period_type;
4066 l_summ_date DATE;
4067 l_prior_summ_date DATE;
4068 l_as_of_date DATE := TO_DATE(p_as_of_date, 'J');
4069 l_prior_as_of_date DATE;
4070 l_process VARCHAR2(1) := 'N';
4071 l_amount_type NUMBER;
4072 l_date DATE;
4073 l_prior_date DATE;
4074 l_calendar_id NUMBER;
4075
4076 l_calendar_type_day VARCHAR2(1);
4077 l_calendar_type_sum VARCHAR2(1);
4078
4079 BEGIN
4080
4081 Write2FWKLog('Entering Convert_Expected_Time...','Convert_Expected_Time');
4082 BEGIN
4083 Write2FWKLog('Determining Prior Year...');
4084 l_Prior_As_Of_Date:=FII_TIME_API.ent_sd_lyr_beg(l_As_Of_Date);
4085 EXCEPTION
4086 WHEN NO_DATA_FOUND THEN
4087 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
4088 l_Prior_As_Of_Date:=PJI_UTILS.GET_EXTRACTION_START_DATE;
4089 END;
4090 Write2FWKLog('Done with Prior Year...');
4091
4092 Write2FWKLog('Retrieving the last summarized date value');
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;
4102
4103 BEGIN
4104 Write2FWKLog('Determining Prior Year...');
4105 l_prior_summ_date:=FII_TIME_API.ent_sd_lyr_beg(l_summ_date);
4106 EXCEPTION
4107 WHEN NO_DATA_FOUND THEN
4108 Write2FWKLog('Unable to determine Prior Year, hence defaulting it...');
4109 l_prior_summ_date:=PJI_UTILS.GET_EXTRACTION_START_DATE;
4110 END;
4111
4112 Write2FWKLog('Done with Prior Year...');
4113
4114 Write2FWKLog('The last PRIOR summarized date is ' || to_char(l_prior_summ_date, 'YYYY/MM/DD'));
4115
4116 -- get the appropriate calendar id
4117 IF l_period_type LIKE '%PA%' THEN
4118 l_calendar_id := G_PA_Calendar_ID;
4119 l_is_GL := 'Y';
4120 l_calendar_type_day := 'P';
4121 l_calendar_type_sum := 'P';
4122 Write2FWKLog('PA calendar is selected.');
4123
4124 ELSIF l_period_type LIKE '%CAL%' THEN
4125 l_calendar_id := G_GL_Calendar_ID;
4126 l_is_GL := 'Y';
4127 l_calendar_type_day := 'C';
4128 l_calendar_type_sum := 'G';
4129 Write2FWKLog('GL calendar is selected.');
4130
4131 ELSE
4132 l_calendar_type_day := 'C';
4133 l_calendar_type_sum := 'E';
4134 Write2FWKLog('ENT calendar is selected.');
4135 END IF;
4136
4137 Write2FWKLog('The calendar Id value = ' || l_calendar_id);
4138
4139 -- get all the id, and start_date of the period, week,
4140 -- quarter, year for the as_of_date and the last
4141 -- summarized date
4142
4143 IF l_is_GL IS NULL THEN
4144 Write2FWKLog('Selecting from FII_TIME_DAY for as_of_date and last_summ_date');
4145
4146 SELECT ent_period_id, ent_period_start_date,
4147 ent_qtr_id, ent_qtr_start_date,
4148 ent_year_id, ent_year_start_date,
4149 week_id, week_start_date
4150 INTO l_period_id, l_period_start_date,
4151 l_qtr_id, l_qtr_start_date,
4152 l_year_id, l_year_start_date,
4153 l_week_id, l_week_start_date
4154 FROM fii_time_day
4155 WHERE report_date = l_as_of_date;
4156
4157 SELECT ent_period_id, ent_period_start_date,
4158 ent_qtr_id, ent_qtr_start_date,
4159 ent_year_id, ent_year_start_date,
4160 week_id, week_start_date
4161 INTO l_period_id_s, l_period_start_date_s,
4162 l_qtr_id_s, l_qtr_start_date_s,
4163 l_year_id_s, l_year_start_date_s,
4164 l_week_id_s, l_week_start_date_s
4165 FROM fii_time_day
4166 WHERE report_date = l_summ_date;
4167
4168 ELSE
4169 Write2FWKLog('Selecting from FII_TIME_CAL_DAY_MV for as_of_date and last_summ_date');
4170
4171 SELECT cal_period_id, cal_period_start_date,
4172 cal_qtr_id, cal_qtr_start_date,
4173 cal_year_id, cal_year_start_date
4174 INTO l_period_id, l_period_start_date,
4175 l_qtr_id, l_qtr_start_date,
4176 l_year_id, l_year_start_date
4177 FROM fii_time_cal_day_mv
4178 WHERE report_date = l_as_of_date
4179 AND calendar_id = l_calendar_id;
4180
4181 SELECT cal_period_id, cal_period_start_date,
4182 cal_qtr_id, cal_qtr_start_date,
4183 cal_year_id, cal_year_start_date
4184 INTO l_period_id_s, l_period_start_date_s,
4185 l_qtr_id_s, l_qtr_start_date_s,
4186 l_year_id_s, l_year_start_date_s
4187 FROM fii_time_cal_day_mv
4188 WHERE report_date = l_summ_date
4189 AND calendar_id = l_calendar_id;
4190
4191 END IF;
4192
4193
4194 -- set the level depending on the period_type
4195 CASE p_period_type
4196 WHEN 'ITD' THEN l_level:=128;
4197 WHEN 'FII_TIME_ENT_YEAR' THEN l_level:=64;
4198 WHEN 'FII_TIME_CAL_YEAR' THEN l_level:=64;
4199 WHEN 'FII_TIME_ENT_QTR' THEN l_level:=32;
4200 WHEN 'FII_TIME_CAL_QTR' THEN l_level:=32;
4201 WHEN 'FII_TIME_ENT_PERIOD' THEN l_level:=1;
4202 WHEN 'FII_TIME_CAL_PERIOD' THEN l_level:=1;
4203 WHEN 'PJI_TIME_PA_PERIOD' THEN l_level:=1;
4204 WHEN 'FII_TIME_WEEK' THEN l_level:=0;
4205 ELSE NULL;
4206 END CASE;
4207 Write2FWKLog('The Period_Type level value = ' || l_level);
4208
4209
4210 ---------------------------------------------------------------
4211 -- This if statement determines whether the last summarized
4212 -- date is out of the time window of the as of date and
4213 -- the period type chosen.
4214 ---------------------------------------------------------------
4215
4216 IF ((l_level=128) or
4217 (l_level=64 and l_year_id = l_year_id_s) or
4218 (l_level=32 and l_qtr_id = l_qtr_id_s) or
4219 (l_level=1 and l_period_id = l_period_id_s) or
4220 (l_level=0 and l_week_id = l_week_id_s)) and
4221 (l_summ_date < l_as_of_date) THEN
4222
4223 -- The last summarized date is within the time window.
4224 -- Need further processing.
4225 -- Set the tmp variables to the values found of the
4226 -- last summarized date.
4227 Write2FWKLog('Last summarized date within time window, amount type is Actuals');
4228
4229 l_process := 'Y';
4230 l_period_id_tmp := l_period_id_s;
4231 l_period_start_date_tmp := l_period_start_date_s;
4232 l_qtr_id_tmp := l_qtr_id_s;
4233 l_qtr_start_date_tmp := l_qtr_start_date_s;
4234 l_year_id_tmp := l_year_id_s;
4235 l_year_start_date_tmp := l_year_start_date_s;
4236 l_week_id_tmp := l_week_id_s;
4237 l_week_start_date_tmp := l_week_start_date_s;
4238 l_amount_type := 0;
4239 l_date := l_summ_date;
4240 l_prior_date := l_prior_summ_date;
4241
4242 ELSE
4243
4244 -- The last summarized date is out of the time window
4245 -- Set the tmp variables to the values found of the
4246 -- as_of_date.
4247
4248 l_period_id_tmp := l_period_id;
4249 l_period_start_date_tmp := l_period_start_date;
4250 l_qtr_id_tmp := l_qtr_id;
4251 l_qtr_start_date_tmp := l_qtr_start_date;
4252 l_year_id_tmp := l_year_id;
4253 l_year_start_date_tmp := l_year_start_date;
4254 l_week_id_tmp := l_week_id;
4255 l_week_start_date_tmp := l_week_start_date;
4256 l_date := l_as_of_date;
4257 l_prior_date := l_prior_as_of_date;
4258
4259 IF (l_summ_date > l_as_of_date) THEN
4260 Write2FWKLog('Last summarized date out of time window, amount type is Actuals');
4261 l_amount_type := 0; /* actuals */
4262 ELSE
4263 Write2FWKLog('Last summarized date out of time window, amount type is Scheduled');
4264 l_amount_type := 1; /* scheduled */
4265 END IF;
4266 END IF;
4267
4268 Write2FWKLog('The value of l_process variable = ' || l_process);
4269
4270
4271 /* Week Level (WTD) */
4272 -- if it is week to date, just get the days to the beginning
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)
4291 AND report_date <= l_prior_date;
4292 END IF;
4293
4294 END IF;
4295
4296
4297 /* Period or Month Level (PTD) */
4298 -- get all the days that are less than the l_date (either
4299 -- the last summarized date or the as_of_date), with the
4300 -- same period_id
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)
4319 AND report_date <= l_prior_date;
4320 END IF;
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
4330 AND calendar_id = l_calendar_id
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)
4340 AND calendar_id = l_calendar_id
4341 AND report_date <= l_prior_date;
4342 END IF;
4343
4344 END IF;
4345 END IF;
4346
4347
4348 /* Quarter Level (QTD) */
4349 -- get all the months that are less than the period start date
4350 -- with the same quarter id
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
4360 AND start_date < l_period_start_date_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
4370 AND calendar_id = l_calendar_id
4371 AND start_date < l_period_start_date_tmp;
4372 END IF;
4373 END IF;
4374
4375
4376 /* Year Level (YTD) */
4377 -- get all the quarters that are less than the quarter start date
4378 -- with the same year id
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
4388 AND start_date < l_qtr_start_date_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
4398 AND calendar_id = l_calendar_id
4399 AND start_date < l_qtr_start_date_tmp;
4400 END IF;
4401 END IF;
4402
4403
4404 /* ITD Level */
4405 -- get all the years that are less than year start date
4406 -- of the last summarized date or the as_of_date (already set
4407 -- in the tmp variable from the earlier logic)
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
4426 AND start_date < l_year_start_date_tmp;
4427 END IF;
4428 END IF;
4429
4430
4431 --------------------------------------------------------------
4432 -- Will continue processing only for the following case:
4433 -- That the last summarized date is within the time window of
4434 -- the as_of_date and the period_type chosen
4435 --------------------------------------------------------------
4436
4437 IF l_process = 'Y' THEN
4438
4439 -- When l_process is Y, then the amount type will always be 1 (scheduled).
4440 -- This is when the summarized date is less than the as of date value.
4441 -- The following code will process the difference between summarized date
4442 -- and the as of date values.
4443 l_amount_type := 1;
4444 Write2FWKLog('Continue processing for the Scheduled Amount Type');
4445 Write2FWKLog('Process remaining time period differences between Last_Summ_Date and As_Of_Date');
4446
4447 /* Week Level (WTD) */
4448 -- if it is week to date, just get the days from the summarized date
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
4458 AND report_date > l_summ_date
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)
4468 AND report_date > l_prior_summ_date
4469 AND report_date <= l_prior_as_of_date;
4470 END IF;
4471
4472 END IF;
4473
4474
4475 IF l_level >=1 THEN
4476
4477 -- When the dates are in the same period/month, this code gets the
4478 -- time_id between those two dates.
4479 IF l_period_id = l_period_id_s THEN
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
4489 AND report_date > l_summ_date
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)
4499 AND report_date > l_prior_summ_date
4500 AND report_date <= l_prior_as_of_date;
4501
4502 END IF;
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
4512 AND calendar_id = l_calendar_id
4513 AND report_date > l_summ_date
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)
4523 AND calendar_id = l_calendar_id
4524 AND report_date > l_prior_summ_date
4525 AND report_date <= l_prior_as_of_date;
4526 END IF;
4527
4528 END IF;
4529
4530 ELSE
4531 -- When the dates are not in the same period/month, this code gets the
4532 -- time_id which is greater than the summarized date within its period_id
4533 -- AND the time_id which is less than the as_of_date within its period_id
4534 -- (will only get executed when the period_type > 1)
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)
4553 AND report_date > l_prior_summ_date;
4554 END IF;
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)
4573 AND report_date <= l_prior_as_of_date;
4574 END IF;
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
4584 AND calendar_id = l_calendar_id
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)
4594 AND calendar_id = l_calendar_id
4595 AND report_date > l_prior_summ_date;
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
4605 AND calendar_id = l_calendar_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)
4615 AND calendar_id = l_calendar_id
4616 AND report_date <= l_prior_as_of_date;
4617 END IF;
4618
4619 END IF;
4620
4621 END IF;
4622
4623 END IF; /* level = 1 */
4624
4625
4626
4627 IF l_level >= 32 THEN
4628
4629 -- When the dates are in the same quarter, this code gets the
4630 -- time_id of the periods/months between those two dates's
4631 -- period start dates.
4632 IF l_qtr_id = l_qtr_id_s THEN
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
4642 AND start_date < l_period_start_date
4643 AND start_date > l_period_start_date_s;
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
4653 AND calendar_id = l_calendar_id
4654 AND start_date < l_period_start_date
4655 AND start_date > l_period_start_date_s;
4656
4657 END IF;
4658
4659 ELSE
4660 -- When the dates are not in the same quarter, this code gets the time_id of
4661 -- the periods which is greater than the summarized date's period_start_date
4662 -- within its quarter_id AND the time_id which is less than the as_of_date's
4663 -- period_start_date within its quarter_id
4664 -- (will only get executed when the period_type > 32)
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
4683 AND start_date < l_period_start_date;
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
4693 AND calendar_id = l_calendar_id
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
4703 AND calendar_id = l_calendar_id
4704 AND start_date < l_period_start_date;
4705
4706 END IF;
4707
4708 END IF;
4709
4710 END IF; /* level = 32 */
4711
4712
4713 IF l_level >= 64 THEN
4714
4715 -- When the dates are in the same year, this code gets the
4716 -- time_id of the quarters between those two dates's quarter
4717 -- start_dates.
4718 IF l_year_id = l_year_id_s THEN
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
4728 AND start_date < l_qtr_start_date
4729 AND start_date > l_qtr_start_date_s;
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
4739 AND calendar_id = l_calendar_id
4740 AND start_date < l_qtr_start_date
4741 AND start_date > l_qtr_start_date_s;
4742
4743 END IF;
4744
4745 ELSE
4746 -- When the dates are not in the same year, this code gets the time_id of
4747 -- the quarters which is greater than the summarized date's quarter_start_date
4748 -- within its year_id AND the time_id which is less than the as_of_date's
4749 -- quarter_start_date within its year_id
4750 -- (will only get executed when the period_type > 64)
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
4769 AND start_date < l_qtr_start_date;
4770
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
4780 AND calendar_id = l_calendar_id
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
4790 AND calendar_id = l_calendar_id
4791 AND start_date < l_qtr_start_date;
4792
4793 END IF;
4794
4795 END IF;
4796
4797 END IF; /* level = 64 */
4798
4799
4800 IF l_level >= 128 THEN
4801
4802 -- When the level is 128, this code gets the time_ids
4803 -- of the years between those two dates's year start_dates.
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
4813 AND start_date > l_year_start_date_s;
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
4823 AND start_date < l_year_start_date
4824 AND start_date > l_year_start_date_s;
4825 END IF;
4826
4827 END IF; /* level = 128 */
4828
4829 END IF; /* l_process */
4830
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)
4840 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)
4841 WHEN 16 THEN SUBSTR(LPAD(ID,11,'0'),1,3)||TO_CHAR(SUBSTR(LPAD(ID,11,'0'),4,4)-1)||SUBSTR(LPAD(ID,11,'0'),8)
4842 END)
4843 WHERE period_type<>1;
4844
4845 Write2FWKLog('Prior ID is updated.');
4846 END IF;
4847
4848 Write2FWKLog('Exiting Convert_Expected_Time...');
4849
4850 EXCEPTION
4851 WHEN OTHERS THEN
4852 g_SQL_Error_Msg:=SQLERRM();
4853 Write2FWKLog(g_SQL_Error_Msg, 3);
4854 RAISE;
4855
4856 END Convert_Expected_Time;
4857
4858
4859 /*
4860 ** The following API is coded by jeff.
4861 */
4862 Procedure Convert_Time_AVL_Trend(p_AS_OF_DATE NUMBER)
4863 AS
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
4873 INTO l_Week_Id,
4874 l_Week_Name,
4875 l_End_Date
4876 FROM fii_time_day day
4877 , fii_time_week wek
4878 WHERE
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
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
4895 WHERE end_date>l_End_Date
4896 ORDER BY 1 ASC)
4897 WHERE ROWNUM < 13;
4898 END Convert_Time_AVL_Trend;
4899
4900 /*
4901 ** ----------------------------------------------------------
4902 ** Procedure: Init
4903 ** This procedure caches the dimension and measure defination
4904 ** for the given region. This procedure has to be called
4905 ** before any other program units are called.
4906 ** ----------------------------------------------------------
4907 */
4908 Procedure Init (p_Region_Code IN VARCHAR2)
4909 IS
4910 BEGIN
4911 IF p_PA_DEBUG_MODE = 'Y' THEN
4912 Write2FWKLog('Entering Init...','Init');
4913 END IF;
4914
4915 IF p_Region_code IS NOT NULL THEN
4916 IF p_PA_DEBUG_MODE = 'Y' THEN
4917 Write2FWKLog('Before bulk collecting dimensions meta data for the specified report...');
4918 END IF;
4919
4920 SELECT attribute_code attribute_code
4921 , attribute2 dimension_level
4922 , attribute3 base_column
4923 , attribute15 view_by_table
4924 BULK COLLECT INTO
4925 G_dimension_codes_tab
4926 , G_dimension_level_tab
4927 , G_dim_base_column_tab
4928 , G_view_by_table_tab
4929 FROM
4930 AK_REGION_ITEMS
4931 WHERE region_code = p_Region_Code
4932 AND region_application_id = 1292
4933 AND attribute1 IS NOT NULL;
4934
4935 IF p_PA_DEBUG_MODE = 'Y' THEN
4936 Write2FWKLog('After bulk collecting dimensions meta data for the specified report...');
4937 Write2FWKLog('Before bulk collecting measure meta data for the specified report...');
4938 END IF;
4939
4940 SELECT attribute_code attribute_code
4941 , attribute3 base_column
4942 , attribute4 attribute4
4943 , attribute9 aggregation
4944 BULK COLLECT INTO
4945 G_attribute_code_tab
4946 , G_msr_base_column_tab
4947 , G_attribute4_tab
4948 , G_aggregation_tab
4949 FROM
4950 AK_REGION_ITEMS
4951 WHERE region_code = p_Region_Code
4952 AND region_application_id = 1292
4953 AND attribute9 IS NOT NULL
4954 ORDER BY display_sequence;
4955
4956 IF p_PA_DEBUG_MODE = 'Y' THEN
4957 Write2FWKLog('After bulk collecting measure meta data for the specified report...');
4958 END IF;
4959
4960 END IF;
4961 IF p_PA_DEBUG_MODE = 'Y' THEN
4962 Write2FWKLog('Exiting Init...');
4963 END IF;
4964 EXCEPTION
4965 WHEN OTHERS THEN
4966 g_SQL_Error_Msg:=SQLERRM();
4967 IF p_PA_DEBUG_MODE = 'Y' THEN
4968 Write2FWKLog(g_SQL_Error_Msg, 3);
4969 END IF;
4970 RAISE;
4971 END Init;
4972
4973 /*
4974 ** ----------------------------------------------------------
4975 ** Function: Get_ViewBY_Base_Column
4976 ** This Function traverses thru the cached copy of dimensions
4977 ** and returns the view by columns name. The function also
4978 ** caches the view by information into global variables.
4979 ** Therefore, care has to be taken to not to reference these
4980 ** global variables without calling this api.
4981 ** ----------------------------------------------------------
4982 */
4983 /*
4984 Function Get_ViewBY_Base_Column(p_view_by IN VARCHAR2) RETURN VARCHAR2
4985 IS
4986 BEGIN
4987 Write2FWKLog('Entering Get_ViewBY_Base_Column...','Get_ViewBY_Base_Column');
4988 FOR i IN G_dimension_level_tab.first..G_dimension_level_tab.last LOOP
4989 IF (G_dimension_level_tab(i) = p_view_by) THEN
4990 G_ViewBY:=G_dimension_level_tab(i);
4991 G_ViewBY_Column_Name:=G_dim_base_column_tab(i);
4992 G_ViewBY_Table_Name:=G_view_by_table_tab(i);
4993 Write2FWKLog('G_ViewBY :'||G_ViewBY);
4994 Write2FWKLog('G_ViewBY_Column_Name :'||G_ViewBY_Column_Name);
4995 Write2FWKLog('G_ViewBY_Table_Name :'||G_ViewBY_Table_Name);
4996 END IF;
4997 END LOOP;
4998 RETURN G_ViewBY_Column_Name;
4999 Write2FWKLog('Exiting Get_ViewBY_Base_Column...');
5000 EXCEPTION
5001 WHEN OTHERS THEN
5002 g_SQL_Error_Msg:=SQLERRM();
5003 Write2FWKLog(g_SQL_Error_Msg, 3);
5004 RAISE;
5005 END Get_ViewBY_Base_Column;
5006 */
5007
5008 /* New Revision
5009 ** ----------------------------------------------------------
5010 ** Function: Get_ViewBY_Base_Column
5011 ** This Function traverses thru the cached copy of dimensions
5012 ** and returns the view by columns name. The function also
5013 ** caches the view by information into global variables.
5014 ** Therefore, care has to be taken to not to reference these
5015 ** global variables without calling this api.
5016 ** ----------------------------------------------------------
5017 */
5018
5019 Function Get_ViewBY_Base_Column(p_view_by IN VARCHAR2) RETURN VARCHAR2
5020 IS
5021 BEGIN
5022 IF p_PA_DEBUG_MODE = 'Y' THEN
5023 Write2FWKLog('Entering Get_ViewBY_Base_Column...'||p_view_by,'Get_ViewBY_Base_Column'||p_view_by);
5024 END IF;
5025
5026 G_ViewBY := p_view_by;
5027 CASE p_view_by
5028 WHEN 'ORGANIZATION+PJI_ORGANIZATIONS' THEN
5029 G_ViewBY_Column_Name := 'ORGANIZATION_ID';
5030 G_ViewBY_Table_Name := 'PJI_ORGANIZATIONS_V';
5031 WHEN 'PROJECT CLASSIFICATION+CLASS_CODE' THEN
5032 G_ViewBY_Column_Name := 'PROJECT_CLASS_ID';
5033 G_ViewBY_Table_Name := 'PJI_CLASS_CODES_V';
5034 WHEN 'PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES' THEN
5035 G_ViewBY_Column_Name := 'EXPENDITURE_CATEGORY';
5036 G_ViewBY_Table_Name := 'PJI_EXP_CATEGORIES_V';
5037 WHEN 'PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES' THEN
5038 G_ViewBY_Column_Name := 'EXPENDITURE_TYPE_ID';
5039 G_ViewBY_Table_Name := 'PJI_EXP_TYPES_V';
5040 WHEN 'PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES' THEN
5041 G_ViewBY_Column_Name := 'REVENUE_CATEGORY';
5042 G_ViewBY_Table_Name := 'PJI_REVENUE_CATEGORIES_V';
5043 WHEN 'PROJECT REVENUE CATEGORY+PJI_EXP_EVT_TYPES' THEN
5044 G_ViewBY_Column_Name := 'REVENUE_TYPE_ID';
5045 G_ViewBY_Table_Name := 'PJI_EXP_EVT_TYPES_V';
5046 WHEN 'PROJECT JOB LEVEL+PJI_JOB_LEVELS' THEN
5047 G_ViewBY_Column_Name := 'JOB_LEVEL_ID';
5048 G_ViewBY_Table_Name := 'PJI_JOB_LEVELS_V';
5049 WHEN 'PROJECT WORK TYPE+PJI_UTIL_CATEGORIES' THEN
5050 G_ViewBY_Column_Name := 'UTIL_CATEGORY_ID';
5051 G_ViewBY_Table_Name := 'PJI_UTIL_CATEGORIES_V';
5052 WHEN 'PROJECT WORK TYPE+PJI_WORK_TYPES' THEN
5053 G_ViewBY_Column_Name := 'WORK_TYPE_ID';
5054 G_ViewBY_Table_Name := 'PJI_WORK_TYPES_V';
5055 WHEN 'PROJECT+PJI_PROJECTS' THEN
5056 G_ViewBY_Column_Name := 'PROJECT_NAME';
5057 G_ViewBY_Table_Name := 'PJI_PROJECTS_V';
5058 WHEN 'TIME+FII_TIME_CAL_PERIOD' THEN
5059 G_ViewBY_Column_Name := 'TIME_ID';
5060 G_ViewBY_Table_Name := 'FII_TIME_CAL_PERIOD_V';
5061 WHEN 'TIME+FII_TIME_CAL_QTR' THEN
5062 G_ViewBY_Column_Name := 'TIME_ID';
5063 G_ViewBY_Table_Name := 'FII_TIME_CAL_QTR_V';
5064 WHEN 'TIME+FII_TIME_CAL_YEAR' THEN
5065 G_ViewBY_Column_Name := 'TIME_ID';
5066 G_ViewBY_Table_Name := 'FII_TIME_CAL_YEAR_V';
5067 WHEN 'TIME+FII_TIME_ENT_PERIOD' THEN
5068 G_ViewBY_Column_Name := 'TIME_ID';
5069 G_ViewBY_Table_Name := 'FII_TIME_ENT_PERIOD_V';
5070 WHEN 'TIME+FII_TIME_ENT_QTR' THEN
5071 G_ViewBY_Column_Name := 'TIME_ID';
5072 G_ViewBY_Table_Name := 'FII_TIME_ENT_QTR_V';
5073 WHEN 'TIME+FII_TIME_ENT_YEAR' THEN
5074 G_ViewBY_Column_Name := 'TIME_ID';
5075 G_ViewBY_Table_Name := 'FII_TIME_ENT_YEAR_V';
5076 WHEN 'TIME+FII_TIME_WEEK' THEN
5077 G_ViewBY_Column_Name := 'TIME_ID';
5078 G_ViewBY_Table_Name := 'FII_TIME_WEEK_V';
5079 WHEN 'TIME+PJI_TIME_PA_PERIOD' THEN
5080 G_ViewBY_Column_Name := 'TIME_ID';
5081 G_ViewBY_Table_Name := 'PJI_TIME_PA_PERIOD_V';
5082 END CASE;
5083
5084 IF p_PA_DEBUG_MODE = 'Y' THEN
5085 Write2FWKLog('G_ViewBY :'||G_ViewBY);
5086 Write2FWKLog('G_ViewBY_Column_Name :'||G_ViewBY_Column_Name);
5087 Write2FWKLog('G_ViewBY_Table_Name :'||G_ViewBY_Table_Name);
5088 Write2FWKLog('Exiting Get_ViewBY_Base_Column...');
5089 END IF;
5090
5091 RETURN G_ViewBY_Column_Name;
5092 EXCEPTION
5093 WHEN OTHERS THEN
5094 g_SQL_Error_Msg:=SQLERRM();
5095 IF p_PA_DEBUG_MODE = 'Y' THEN
5096 Write2FWKLog(g_SQL_Error_Msg, 3);
5097 END IF;
5098 RAISE;
5099 END Get_ViewBY_Base_Column;
5100
5101
5102 /*
5103 ** ----------------------------------------------------------
5104 ** Function: Construct_SELECT_Clause
5105 ** This Function constructs the select statement to be
5106 ** returned to PMV. All unknown variables (at this point of
5107 ** time) are replaced with patterns. These patterns are
5108 ** later replaced with actual values in the Generate_SQL API.
5109 ** ----------------------------------------------------------
5110 */
5111 Function Construct_SELECT_Clause(p_View_BY IN VARCHAR2)
5112 RETURN VARCHAR2 IS
5113 l_Buffer VARCHAR2(200);
5114 l_ViewBY_Indentifier VARCHAR2(1);
5115 l_Select_List VARCHAR2(3200):=' SELECT ';
5116 BEGIN
5117 IF p_PA_DEBUG_MODE = 'Y' THEN
5118 Write2FWKLog('Entering Construct_Select_Clause...','Construct_Select_Clause');
5119 END IF;
5120
5121 IF p_View_BY IS NOT NULL THEN
5122 IF p_PA_DEBUG_MODE = 'Y' THEN
5123 Write2FWKLog('Determining Viewby base column...');
5124 END IF;
5125 l_Buffer:=Get_ViewBY_Base_Column(p_View_BY);
5126 IF p_PA_DEBUG_MODE = 'Y' THEN
5127 Write2FWKLog('Done determining Viewby base column. Value : '||l_Buffer);
5128 END IF;
5129 END IF;
5130
5131 IF l_Buffer IS NOT NULL THEN
5132 l_ViewBY_Indentifier:='Y';
5133 l_Select_List:=l_Select_List||'FACT.'||l_Buffer||' "VIEWBY" ';
5134 ELSE
5135 l_Select_List:=l_Select_List||'1 "CONSTANT" ';
5136 END IF;
5137
5138 FOR i IN G_attribute_code_tab.first..G_attribute_code_tab.last LOOP
5139 IF (G_attribute_code_tab(i) = 'VIEWBY' AND l_ViewBY_Indentifier IS NOT NULL ) THEN
5140 NULL;
5141 ELSE
5142 IF G_attribute_code_tab(i) LIKE 'PJI_REP_URL%' THEN
5143 l_Buffer:=', FACT.'||G_attribute_code_tab(i)||' ';
5144 ELSIF G_attribute_code_tab(i) LIKE 'PJI_REP_TOTAL%' THEN
5145 l_Buffer:=', FACT.'||G_attribute4_tab(i)||' "'||G_attribute_code_tab(i)||'"';
5146 ELSE
5147 l_Buffer:=', FACT.'||G_msr_base_column_tab(i)||' ';
5148 END IF;
5149 IF G_attribute_code_tab(i) NOT LIKE 'PJI_REP_TOTAL%' THEN
5150 l_Select_List:=l_Select_List||l_Buffer||' "'||G_attribute_code_tab(i)||'"';
5151 ELSE
5152 l_Select_List:=l_Select_List||l_Buffer;
5153 END IF;
5154 END IF;
5155 END LOOP;
5156 l_Select_List:=l_Select_List||' FROM TABLE(<<DEV_PL/SQL_EXTENSION>>(<<PL/SQL PARAMS>>)) FACT ';
5157
5158 IF (p_View_BY NOT LIKE '%TIME%' OR l_ViewBY_Indentifier IS NULL) THEN
5159 IF p_PA_DEBUG_MODE = 'Y' THEN
5160 Write2FWKLog('Generating the order by clause.');
5161 END IF;
5162 l_Select_List:=l_Select_List||' &ORDER_BY_CLAUSE ';
5163 END IF;
5164
5165 IF p_PA_DEBUG_MODE = 'Y' THEN
5166 Write2FWKLog('l_Select_List :'||l_Select_List);
5167 Write2FWKLog('Exiting Construct_Select_Clause...');
5168 END IF;
5169
5170 RETURN l_Select_List;
5171 EXCEPTION
5172 WHEN OTHERS THEN
5173 g_SQL_Error_Msg:=SQLERRM();
5174 IF p_PA_DEBUG_MODE = 'Y' THEN
5175 Write2FWKLog(g_SQL_Error_Msg, 3);
5176 END IF;
5177 RAISE;
5178 END Construct_Select_Clause;
5179
5180 /* New Revision
5181 ** ----------------------------------------------------------
5182 ** Function: Construct_SELECT_Clause
5183 ** This Function constructs the select statement to be
5184 ** returned to PMV. All unknown variables (at this point of
5185 ** time) are replaced with patterns. These patterns are
5186 ** later replaced with actual values in the Generate_SQL API.
5187 ** ----------------------------------------------------------
5188 */
5189 Function Construct_SELECT_Clause(p_View_BY IN VARCHAR2, p_Select_List IN VARCHAR2)
5190 RETURN VARCHAR2 IS
5191 l_Buffer VARCHAR2(200);
5192 l_ViewBY_Indentifier VARCHAR2(1);
5193 l_Select_List VARCHAR2(3200):=' SELECT ';
5194 BEGIN
5195 IF p_PA_DEBUG_MODE = 'Y' THEN
5196 Write2FWKLog('Entering Construct_Select_Clause...','Construct_Select_Clause');
5197 END IF;
5198
5199 IF p_View_BY IS NOT NULL THEN
5200 IF p_PA_DEBUG_MODE = 'Y' THEN
5201 Write2FWKLog('Determining Viewby base column...');
5202 END IF;
5203 l_Buffer:=Get_ViewBY_Base_Column(p_View_BY);
5204 IF p_PA_DEBUG_MODE = 'Y' THEN
5205 Write2FWKLog('Done determining Viewby base column. Value : '||l_Buffer);
5206 END IF;
5207 END IF;
5208
5209 IF l_Buffer IS NOT NULL THEN
5210 l_ViewBY_Indentifier:='Y';
5211 l_Select_List:=l_Select_List||'FACT.'||l_Buffer||' "VIEWBY" ';
5212 ELSE
5213 l_Select_List:=l_Select_List||'1 "CONSTANT" ';
5214 END IF;
5215
5216 l_Select_List:=l_Select_List||', '||p_Select_List;
5217
5218 l_Select_List:=l_Select_List||' FROM TABLE(<<DEV_PL/SQL_EXTENSION>>(<<PL/SQL PARAMS>>)) FACT ';
5219
5220 IF (p_View_BY NOT LIKE '%TIME%' OR l_ViewBY_Indentifier IS NULL) THEN
5221 IF p_PA_DEBUG_MODE = 'Y' THEN
5222 Write2FWKLog('Generating the order by clause.');
5223 END IF;
5224 l_Select_List:=l_Select_List||' &ORDER_BY_CLAUSE ';
5225 END IF;
5226
5227 IF p_PA_DEBUG_MODE = 'Y' THEN
5228 Write2FWKLog('l_Select_List :'||l_Select_List);
5229 Write2FWKLog('Exiting Construct_Select_Clause...');
5230 END IF;
5231
5232 RETURN l_Select_List;
5233
5234 EXCEPTION
5235 WHEN OTHERS THEN
5236 g_SQL_Error_Msg:=SQLERRM();
5237 IF p_PA_DEBUG_MODE = 'Y' THEN
5238 Write2FWKLog(g_SQL_Error_Msg, 3);
5239 END IF;
5240 RAISE;
5241 END Construct_Select_Clause;
5242
5243 /*
5244 ** ----------------------------------------------------------
5245 ** Function: Generate_SQL
5246 ** This Function generates the select statement based on the
5247 ** parameters selected by the user.
5248 ** ----------------------------------------------------------
5249 */
5250 Procedure Generate_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
5251 , p_SQL_Statement IN OUT NOCOPY VARCHAR2
5252 , p_PMV_Output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
5253 , p_Region_Code IN VARCHAR2
5254 , p_PLSQL_Driver IN VARCHAR2
5255 , p_PLSQL_Driver_Params IN VARCHAR2
5256 )
5257 IS
5258 l_Sql_Statement VARCHAR2(3200);
5259 l_PMV_Output BIS_QUERY_ATTRIBUTES_TBL:=BIS_QUERY_ATTRIBUTES_TBL();
5260 l_PMV_Rec BIS_QUERY_ATTRIBUTES;
5261 l_PMV_Rec_Ctr NUMBER:=1;
5262 l_Exists_Flag VARCHAR2(1):='N';
5263 l_Period_Type_Found VARCHAR2(1);
5264 l_View_BY_Found VARCHAR2(1);
5265
5266 l_View_BY VARCHAR2(150);
5267 l_Report_Name VARCHAR2(150):=p_Region_Code;
5268 l_PLSQL_Driver VARCHAR2(150):=p_PLSQL_Driver;
5269 l_PLSQL_Driver_Params VARCHAR2(1000):=p_PLSQL_Driver_Params;
5270
5271 l_Report_Parameters VARCHAR2(3000);
5272 l_Substitute_Var VARCHAR2(150);
5273 l_View_BY_Pattern VARCHAR2(3);
5274 l_Parameter_Pattern VARCHAR2(150);
5275
5276 BEGIN
5277 IF p_PA_DEBUG_MODE = 'Y' THEN
5278 PJI_UTILS.RESET_SSWA_SESSION_CACHE;
5279 Write2FWKLog('Entering Generate_SQL...','Generate_SQL');
5280 END IF;
5281
5282 l_PMV_Rec:=BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
5283
5284 IF p_PA_DEBUG_MODE = 'Y' THEN
5285 Write2FWKLog('Before Calling Init...','Generate_SQL');
5286 END IF;
5287 Init(l_Report_Name);
5288 IF p_PA_DEBUG_MODE = 'Y' THEN
5289 Write2FWKLog('After Calling Init...','Generate_SQL');
5290 END IF;
5291
5292 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
5293 IF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
5294 IF p_PA_DEBUG_MODE = 'Y' THEN
5295 Write2FWKLog('User specified the view by for the report. ViewBY :'||p_page_parameter_tbl(i).parameter_value,'Generate_SQL');
5296 END IF;
5297 l_View_By := p_page_parameter_tbl(i).parameter_value;
5298 IF p_PA_DEBUG_MODE = 'Y' THEN
5299 Write2FWKLog('l_ViewBY :'||l_View_By,'Generate_SQL');
5300 END IF;
5301 END IF;
5302 END LOOP;
5303
5304 IF p_PA_DEBUG_MODE = 'Y' THEN
5305 Write2FWKLog('Before calling Construct_SELECT_Clause.','Generate_SQL');
5306 END IF;
5307 l_Sql_Statement:=Construct_SELECT_Clause(l_View_BY);
5308 IF p_PA_DEBUG_MODE = 'Y' THEN
5309 Write2FWKLog('After calling Construct_SELECT_Clause.','Generate_SQL');
5310 END IF;
5311
5312 IF l_View_BY IS NOT NULL THEN
5313 l_PMV_Rec.attribute_name:=BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
5314 l_PMV_Rec.attribute_value:=l_View_BY;
5315 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
5316
5317 l_PMV_Output.EXTEND();
5318 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5319
5320 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5321 END IF;
5322
5323 l_Sql_Statement:=REPLACE(l_Sql_Statement,'<<DEV_PL/SQL_EXTENSION>>',l_PLSQL_Driver);
5324
5325 IF p_PA_DEBUG_MODE = 'Y' THEN
5326 Write2FWKLog('Before parsing all the parameters.','Generate_SQL');
5327 END IF;
5328 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
5329
5330 l_Report_Parameters:=NULL;
5331 l_Substitute_Var:=NULL;
5332 l_Parameter_Pattern:=p_page_parameter_tbl(i).parameter_name;
5333
5334 IF p_page_parameter_tbl(i).parameter_name LIKE 'TIME+%FROM' AND p_page_parameter_tbl(i).parameter_name NOT LIKE 'TIME+%PFROM' THEN
5335 l_Parameter_Pattern:='START_TIME';
5336 l_Substitute_Var:='PJI_START_TIME';
5337 l_Report_Parameters:=TO_CHAR(p_page_parameter_tbl(i).period_date,'j');
5338 ELSIF p_page_parameter_tbl(i).parameter_name LIKE 'TIME+%TO' AND p_page_parameter_tbl(i).parameter_name NOT LIKE 'TIME+%PTO' THEN
5339 l_Parameter_Pattern:='END_TIME';
5340 l_Substitute_Var:='PJI_END_TIME';
5341 l_Report_Parameters:=TO_CHAR(p_page_parameter_tbl(i).period_date,'j');
5342 ELSIF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
5343 l_Substitute_Var:='PJI_PERIOD_TYPE';
5344 l_Period_Type_Found:='Y';
5345 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
5346 l_Substitute_Var:='PJI_TIME_COMPARISON_TYPE';
5347 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
5348 l_Substitute_Var:='PJI_VIEW_BY';
5349 l_Report_Parameters:=Convert_ViewBY(p_page_parameter_tbl(i).parameter_value);
5350 l_View_BY_Found:='Y';
5351 ELSIF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
5352 l_Substitute_Var:='PJI_AS_OF_DATE';
5353 l_Report_Parameters:=TO_CHAR(TO_DATE(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY'),'j');
5354 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_DAYS+AVAILABILITY_DAYS' THEN
5355 l_Substitute_Var:='PJI_REP_DIM_32';
5356 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD' THEN
5357 l_Substitute_Var:='PJI_REP_DIM_28';
5358 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_TYPE+AVAILABILITY_TYPE' THEN
5359 l_Substitute_Var:='PJI_REP_DIM_29';
5360 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
5361 l_Substitute_Var:='PJI_REP_DIM_27';
5362 l_Report_Parameters:=Convert_Currency_Code(REPLACE(p_page_parameter_tbl(i).parameter_id,''''));
5363 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
5364 l_Substitute_Var:='PJI_REP_DIM_01';
5365 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+PJI_ORGANIZATIONS' THEN
5366 l_Substitute_Var:='PJI_REP_DIM_02';
5367 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT CLASSIFICATION+CLASS_CATEGORY' THEN
5368 l_Substitute_Var:='PJI_REP_DIM_25';
5369 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT CLASSIFICATION+CLASS_CODE' THEN
5370 l_Substitute_Var:='PJI_REP_DIM_26';
5371 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT JOB LEVEL+PJI_JOB_LEVELS' THEN
5372 l_Substitute_Var:='PJI_REP_DIM_24';
5373 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT WORK TYPE+PJI_UTIL_CATEGORIES' THEN
5374 l_Substitute_Var:='PJI_REP_DIM_21';
5375 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT WORK TYPE+PJI_WORK_TYPES' THEN
5376 l_Substitute_Var:='PJI_REP_DIM_22';
5377 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT+PJI_PROJECTS' THEN
5378 l_Substitute_Var:='PJI_REP_DIM_31';
5379 ELSIF p_page_parameter_tbl(i).parameter_name = 'REV_AT_RISK_FLAG+REV_AT_RISK_FLAG' THEN
5380 l_Substitute_Var:='PJI_REP_DIM_33';
5381
5382 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES' THEN
5383 l_Substitute_Var:='PJI_REP_DIM_34';
5384 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES' THEN
5385 l_Substitute_Var:='PJI_REP_DIM_35';
5386 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES' THEN
5387 l_Substitute_Var:='PJI_REP_DIM_36';
5388 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT REVENUE CATEGORY+PJI_EXP_EVT_TYPES' THEN
5389 l_Substitute_Var:='PJI_REP_DIM_37';
5390 END IF;
5391
5392 IF l_Report_Parameters IS NULL THEN
5393 IF p_PA_DEBUG_MODE = 'Y' THEN
5394 Write2FWKLog('l_Report_Parameters is not set.','Generate_SQL');
5395 END IF;
5396 l_Report_Parameters:=REPLACE(p_page_parameter_tbl(i).parameter_id,'''');
5397 IF p_PA_DEBUG_MODE = 'Y' THEN
5398 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5399 END IF;
5400 END IF;
5401
5402 IF l_Report_Parameters IS NULL OR UPPER(p_page_parameter_tbl(i).parameter_value) = 'ALL' THEN
5403 IF p_PA_DEBUG_MODE = 'Y' THEN
5404 Write2FWKLog('l_Report_Parameters is not set in the report or defaulted to all.','Generate_SQL');
5405 END IF;
5406 /*
5407 ** Added the extra check to default currency when it is specified as 'All'
5408 */
5409 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
5410 l_Report_Parameters:='G';
5411 ELSE
5412 l_Report_Parameters:=NULL;
5413 END IF;
5414 IF p_PA_DEBUG_MODE = 'Y' THEN
5415 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5416 END IF;
5417 END IF;
5418
5419 IF p_PA_DEBUG_MODE = 'Y' THEN
5420 Write2FWKLog('l_Parameter_Name :'||p_page_parameter_tbl(i).parameter_name,'Generate_SQL');
5421 Write2FWKLog('l_Parameter_Pattern :'||l_Parameter_Pattern,'Generate_SQL');
5422 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5423 Write2FWKLog('l_Substitute_Var :'||l_Substitute_Var,'Generate_SQL');
5424 Write2FWKLog('Before replacing the driver params.','Generate_SQL');
5425 Write2FWKLog('l_PLSQL_Driver_Params :'||l_PLSQL_Driver_Params,'Generate_SQL');
5426 END IF;
5427
5428
5429 IF l_Substitute_Var LIKE 'PJI%' AND l_Substitute_Var IS NOT NULL THEN
5430 l_Exists_Flag:='N';
5431
5432 FOR j in 1..l_PMV_Output.COUNT LOOP
5433 IF l_PMV_Output(j).attribute_name = ':'||l_Substitute_Var THEN
5434 l_Exists_Flag:='Y';
5435 END IF;
5436 END LOOP;
5437
5438 IF l_Exists_Flag = 'N' THEN
5439 l_PMV_Rec.attribute_name:=':'||l_Substitute_Var;
5440 l_PMV_Rec.attribute_value:=l_Report_Parameters;
5441 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5442
5443 IF l_Substitute_Var = 'PJI_AS_OF_DATE' THEN
5444 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
5445 ELSE
5446 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5447 END IF;
5448
5449 l_PMV_Output.EXTEND();
5450 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5451 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5452 END IF;
5453
5454 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<'||l_Parameter_Pattern||'>>',':'||l_Substitute_Var);
5455 IF p_PA_DEBUG_MODE = 'Y' THEN
5456 Write2FWKLog('After replacing the driver params.','Generate_SQL');
5457 Write2FWKLog('l_PLSQL_Driver_Params :'||l_PLSQL_Driver_Params,'Generate_SQL');
5458 END IF;
5459 END IF;
5460 END LOOP;
5461
5462 IF l_View_By IS NULL AND l_View_BY_Found IS NULL THEN
5463 IF p_PA_DEBUG_MODE = 'Y' THEN
5464 Write2FWKLog('No View BY is specified. Hence substituting the view by with a dummy value.','Generate_SQL');
5465 END IF;
5466 l_PMV_Rec.attribute_name:=':PJI_VIEW_BY';
5467 l_PMV_Rec.attribute_value:='XX';
5468 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5469 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5470 l_PMV_Output.EXTEND();
5471 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5472 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5473 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<VIEW_BY>>',':PJI_VIEW_BY');
5474 IF p_PA_DEBUG_MODE = 'Y' THEN
5475 Write2FWKLog('Done with substituting the view by with a dummy value.','Generate_SQL');
5476 END IF;
5477 ELSIF l_View_By LIKE 'TIME+%TIME%' AND l_Period_Type_Found IS NULL THEN
5478 IF p_PA_DEBUG_MODE = 'Y' THEN
5479 Write2FWKLog('No Period Type is specified (Availability Trend Option).','Generate_SQL');
5480 Write2FWKLog('Defaulting the period type to view by time dimension.','Generate_SQL');
5481 END IF;
5482 l_PMV_Rec.attribute_name:=':PJI_PERIOD_TYPE';
5483 l_PMV_Rec.attribute_value:=SUBSTR(l_View_By,INSTR(l_View_By,'+')+1);
5484 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5485 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5486 l_PMV_Output.EXTEND();
5487 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5488 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5489 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<PERIOD_TYPE>>',':PJI_PERIOD_TYPE');
5490 END IF;
5491
5492 /*
5493 ** The following portion of the code is commented.
5494 ** Please uncomment it for debugging purposes only.
5495 */
5496
5497 IF p_PA_DEBUG_MODE = 'Y' THEN
5498 FOR i in 1..l_PMV_Output.LAST loop
5499 Write2FWKLog(l_PMV_Output(i).attribute_name||' - '||l_PMV_Output(i).attribute_value||' - '||l_PMV_Output(i).attribute_type||' - '||l_PMV_Output(i).attribute_data_type, 'Check');
5500 END LOOP;
5501 END IF;
5502
5503 IF p_PA_DEBUG_MODE = 'Y' THEN
5504 Write2FWKLog('After parsing all the parameters.','Generate_SQL');
5505 Write2FWKLog('Before replacing the dimension patterns with NULL values.','Generate_SQL');
5506 END IF;
5507
5508 FOR i IN G_dimension_level_tab.first..G_dimension_level_tab.last LOOP
5509 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<'||G_dimension_level_tab(i)||'>>','NULL');
5510 END LOOP;
5511
5512 IF p_PA_DEBUG_MODE = 'Y' THEN
5513 Write2FWKLog('After replacing the dimension patterns with NULL values.','Generate_SQL');
5514 Write2FWKLog('Before replacing the <<PL/SQL PARAMS>> pattern with actual values.','Generate_SQL');
5515 END IF;
5516 l_Sql_Statement:=REPLACE(l_Sql_Statement,'<<PL/SQL PARAMS>>',l_PLSQL_Driver_Params);
5517
5518 IF p_PA_DEBUG_MODE = 'Y' THEN
5519 Write2FWKLog('After replacing the <<PL/SQL PARAMS>> pattern with actual values.','Generate_SQL');
5520 Write2FWKLog('Finally, the SQL Statement :'||l_Sql_Statement,'Generate_SQL');
5521 Write2FWKLog('Exiting Generate_SQL...','Generate_SQL');
5522 END IF;
5523
5524 p_Sql_Statement:=l_Sql_Statement;
5525 p_PMV_Output:=l_PMV_Output;
5526 EXCEPTION
5527 WHEN OTHERS THEN
5528 g_SQL_Error_Msg:=SQLERRM();
5529 IF p_PA_DEBUG_MODE = 'Y' THEN
5530 Write2FWKLog(g_SQL_Error_Msg, 3);
5531 END IF;
5532 RAISE;
5533 END Generate_SQL;
5534
5535 /* New Version of Generate_SQL
5536 ** ----------------------------------------------------------
5537 ** Function: Generate_SQL
5538 ** This Function generates the select statement based on the
5539 ** parameters selected by the user.
5540 ** ----------------------------------------------------------
5541 */
5542 Procedure Generate_SQL(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
5543 , p_Select_List IN VARCHAR2
5544 , p_SQL_Statement IN OUT NOCOPY VARCHAR2
5545 , p_PMV_Output IN OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
5546 , p_Region_Code IN VARCHAR2
5547 , p_PLSQL_Driver IN VARCHAR2
5548 , p_PLSQL_Driver_Params IN VARCHAR2
5549 )
5550 IS
5551 l_Sql_Statement VARCHAR2(3200);
5552 l_PMV_Output BIS_QUERY_ATTRIBUTES_TBL:=BIS_QUERY_ATTRIBUTES_TBL();
5553 l_PMV_Rec BIS_QUERY_ATTRIBUTES;
5554 l_PMV_Rec_Ctr NUMBER:=1;
5555 l_Exists_Flag VARCHAR2(1):='N';
5556 l_Period_Type_Found VARCHAR2(1);
5557 l_View_BY_Found VARCHAR2(1);
5558
5559 l_View_BY VARCHAR2(150);
5560 l_Report_Name VARCHAR2(150):=p_Region_Code;
5561 l_PLSQL_Driver VARCHAR2(150):=p_PLSQL_Driver;
5562 l_PLSQL_Driver_Params VARCHAR2(1000):=p_PLSQL_Driver_Params;
5563
5564 l_Report_Parameters VARCHAR2(3000);
5565 l_Substitute_Var VARCHAR2(150);
5566 l_View_BY_Pattern VARCHAR2(3);
5567 l_Parameter_Pattern VARCHAR2(150);
5568
5569 l_Start_Char_Pos NUMBER:=0;
5570
5571 BEGIN
5572 IF p_PA_DEBUG_MODE = 'Y' THEN
5573 PJI_UTILS.RESET_SSWA_SESSION_CACHE;
5574 Write2FWKLog('Entering Generate_SQL...','Generate_SQL');
5575 END IF;
5576
5577 l_PMV_Rec:=BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
5578
5579 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
5580 IF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
5581 IF p_PA_DEBUG_MODE = 'Y' THEN
5582 Write2FWKLog('User specified the view by for the report. ViewBY :'||p_page_parameter_tbl(i).parameter_value,'Generate_SQL');
5583 END IF;
5584 l_View_By := p_page_parameter_tbl(i).parameter_value;
5585 IF p_PA_DEBUG_MODE = 'Y' THEN
5586 Write2FWKLog('l_ViewBY :'||l_View_By,'Generate_SQL');
5587 END IF;
5588 END IF;
5589 END LOOP;
5590
5591 IF p_PA_DEBUG_MODE = 'Y' THEN
5592 Write2FWKLog('Before calling Construct_SELECT_Clause.','Generate_SQL');
5593 END IF;
5594 l_Sql_Statement:=Construct_SELECT_Clause(l_View_BY, p_Select_List);
5595
5596 IF p_PA_DEBUG_MODE = 'Y' THEN
5597 Write2FWKLog('After calling Construct_SELECT_Clause.','Generate_SQL');
5598 END IF;
5599
5600 IF l_View_BY IS NOT NULL THEN
5601 l_PMV_Rec.attribute_name:=BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
5602 l_PMV_Rec.attribute_value:=l_View_BY;
5603 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
5604
5605 l_PMV_Output.EXTEND();
5606 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5607
5608 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5609 END IF;
5610
5611 l_Sql_Statement:=REPLACE(l_Sql_Statement,'<<DEV_PL/SQL_EXTENSION>>',l_PLSQL_Driver);
5612
5613 IF p_PA_DEBUG_MODE = 'Y' THEN
5614 Write2FWKLog('Before parsing all the parameters.','Generate_SQL');
5615 END IF;
5616 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
5617
5618 l_Report_Parameters:=NULL;
5619 l_Substitute_Var:=NULL;
5620 l_Parameter_Pattern:=p_page_parameter_tbl(i).parameter_name;
5621
5622 IF p_page_parameter_tbl(i).parameter_name LIKE 'TIME+%FROM' AND p_page_parameter_tbl(i).parameter_name NOT LIKE 'TIME+%PFROM' THEN
5623 l_Parameter_Pattern:='START_TIME';
5624 l_Substitute_Var:='PJI_START_TIME';
5625 l_Report_Parameters:=TO_CHAR(p_page_parameter_tbl(i).period_date,'j');
5626 ELSIF p_page_parameter_tbl(i).parameter_name LIKE 'TIME+%TO' AND p_page_parameter_tbl(i).parameter_name NOT LIKE 'TIME+%PTO' THEN
5627 l_Parameter_Pattern:='END_TIME';
5628 l_Substitute_Var:='PJI_END_TIME';
5629 l_Report_Parameters:=TO_CHAR(p_page_parameter_tbl(i).period_date,'j');
5630 ELSIF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
5631 IF l_View_BY LIKE 'TIME+%TIME%' THEN
5632 /*
5633 ** Reset the value of view by to period type
5634 ** for trend reports (viewby time).
5635 */
5636 l_PMV_Output(1).attribute_value := 'TIME+'||p_page_parameter_tbl(i).parameter_value;
5637 END IF;
5638 l_Substitute_Var:='PJI_PERIOD_TYPE';
5639 l_Period_Type_Found:='Y';
5640 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
5641 l_Substitute_Var:='PJI_TIME_COMPARISON_TYPE';
5642 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
5643 l_Substitute_Var:='PJI_VIEW_BY';
5644 l_Report_Parameters:=Convert_ViewBY(p_page_parameter_tbl(i).parameter_value);
5645 l_View_BY_Found:='Y';
5646 ELSIF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
5647 l_Substitute_Var:='PJI_AS_OF_DATE';
5648 l_Report_Parameters:=TO_CHAR(TO_DATE(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY'),'j');
5649 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_DAYS+AVAILABILITY_DAYS' THEN
5650 l_Substitute_Var:='PJI_REP_DIM_32';
5651 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD' THEN
5652 l_Substitute_Var:='PJI_REP_DIM_28';
5653 ELSIF p_page_parameter_tbl(i).parameter_name = 'AVAILABILITY_TYPE+AVAILABILITY_TYPE' THEN
5654 l_Substitute_Var:='PJI_REP_DIM_29';
5655 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
5656 l_Substitute_Var:='PJI_REP_DIM_27';
5657 l_Report_Parameters:=Convert_Currency_Code(REPLACE(p_page_parameter_tbl(i).parameter_id,''''));
5658 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
5659 l_Substitute_Var:='PJI_REP_DIM_01';
5660 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+PJI_ORGANIZATIONS' THEN
5661 l_Substitute_Var:='PJI_REP_DIM_02';
5662 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT CLASSIFICATION+CLASS_CATEGORY' THEN
5663 l_Substitute_Var:='PJI_REP_DIM_25';
5664 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT CLASSIFICATION+CLASS_CODE' THEN
5665 l_Substitute_Var:='PJI_REP_DIM_26';
5666 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT JOB LEVEL+PJI_JOB_LEVELS' THEN
5667 l_Substitute_Var:='PJI_REP_DIM_24';
5668 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT WORK TYPE+PJI_UTIL_CATEGORIES' THEN
5669 l_Substitute_Var:='PJI_REP_DIM_21';
5670 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT WORK TYPE+PJI_WORK_TYPES' THEN
5671 l_Substitute_Var:='PJI_REP_DIM_22';
5672 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT+PJI_PROJECTS' THEN
5673 l_Substitute_Var:='PJI_REP_DIM_31';
5674 ELSIF p_page_parameter_tbl(i).parameter_name = 'REV_AT_RISK_FLAG+REV_AT_RISK_FLAG' THEN
5675 l_Substitute_Var:='PJI_REP_DIM_33';
5676
5677
5678 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES' THEN
5679 l_Substitute_Var:='PJI_REP_DIM_34';
5680 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES' THEN
5681 l_Substitute_Var:='PJI_REP_DIM_35';
5682 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES' THEN
5683 l_Substitute_Var:='PJI_REP_DIM_36';
5684 ELSIF p_page_parameter_tbl(i).parameter_name = 'PROJECT REVENUE CATEGORY+PJI_EXP_EVT_TYPES' THEN
5685 l_Substitute_Var:='PJI_REP_DIM_37';
5686 END IF;
5687
5688 IF l_Report_Parameters IS NULL THEN
5689 IF p_PA_DEBUG_MODE = 'Y' THEN
5690 Write2FWKLog('l_Report_Parameters is not set.','Generate_SQL');
5691 END IF;
5692 l_Report_Parameters:=REPLACE(p_page_parameter_tbl(i).parameter_id,'''');
5693 IF p_PA_DEBUG_MODE = 'Y' THEN
5694 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5695 END IF;
5696 END IF;
5697
5698 IF l_Report_Parameters IS NULL OR UPPER(p_page_parameter_tbl(i).parameter_value) = 'ALL' THEN
5699 IF p_PA_DEBUG_MODE = 'Y' THEN
5700 Write2FWKLog('l_Report_Parameters is not set in the report or defaulted to all.','Generate_SQL');
5701 END IF;
5702 /*
5703 ** Added the extra check to default currency when it is specified as 'All'
5704 */
5705 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
5706 l_Report_Parameters:='G';
5707 ELSE
5708 l_Report_Parameters:=NULL;
5709 END IF;
5710 IF p_PA_DEBUG_MODE = 'Y' THEN
5711 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5712 END IF;
5713 END IF;
5714
5715 IF p_PA_DEBUG_MODE = 'Y' THEN
5716 Write2FWKLog('l_Parameter_Name :'||p_page_parameter_tbl(i).parameter_name,'Generate_SQL');
5717 Write2FWKLog('l_Parameter_Pattern :'||l_Parameter_Pattern,'Generate_SQL');
5718 Write2FWKLog('l_Report_Parameters :'||l_Report_Parameters,'Generate_SQL');
5719 Write2FWKLog('l_Substitute_Var :'||l_Substitute_Var,'Generate_SQL');
5720 Write2FWKLog('Before replacing the driver params.','Generate_SQL');
5721 Write2FWKLog('l_PLSQL_Driver_Params :'||l_PLSQL_Driver_Params,'Generate_SQL');
5722 END IF;
5723
5724 IF l_Substitute_Var LIKE 'PJI%' AND l_Substitute_Var IS NOT NULL THEN
5725 l_Exists_Flag:='N';
5726
5727 FOR j in 1..l_PMV_Output.COUNT LOOP
5728 IF l_PMV_Output(j).attribute_name = ':'||l_Substitute_Var THEN
5729 l_Exists_Flag:='Y';
5730 END IF;
5731 END LOOP;
5732
5733 IF l_Exists_Flag = 'N' THEN
5734 l_PMV_Rec.attribute_name:=':'||l_Substitute_Var;
5735 l_PMV_Rec.attribute_value:=l_Report_Parameters;
5736 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5737
5738 IF l_Substitute_Var = 'PJI_AS_OF_DATE' THEN
5739 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
5740 ELSE
5741 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5742 END IF;
5743
5744 l_PMV_Output.EXTEND();
5745 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5746 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5747 END IF;
5748
5749 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<'||l_Parameter_Pattern||'>>',':'||l_Substitute_Var);
5750 IF p_PA_DEBUG_MODE = 'Y' THEN
5751 Write2FWKLog('After replacing the driver params.','Generate_SQL');
5752 Write2FWKLog('l_PLSQL_Driver_Params :'||l_PLSQL_Driver_Params,'Generate_SQL');
5753 END IF;
5754 END IF;
5755 END LOOP;
5756
5757 IF l_View_By IS NULL AND l_View_BY_Found IS NULL THEN
5758 IF p_PA_DEBUG_MODE = 'Y' THEN
5759 Write2FWKLog('No View BY is specified. Hence substituting the view by with a dummy value.','Generate_SQL');
5760 END IF;
5761 l_PMV_Rec.attribute_name:=':PJI_VIEW_BY';
5762 l_PMV_Rec.attribute_value:='XX';
5763 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5764 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5765 l_PMV_Output.EXTEND();
5766 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5767 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5768 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<VIEW_BY>>',':PJI_VIEW_BY');
5769 IF p_PA_DEBUG_MODE = 'Y' THEN
5770 Write2FWKLog('Done with substituting the view by with a dummy value.','Generate_SQL');
5771 END IF;
5772 ELSIF l_View_By LIKE 'TIME+%TIME%' AND l_Period_Type_Found IS NULL THEN
5773 IF p_PA_DEBUG_MODE = 'Y' THEN
5774 Write2FWKLog('No Period Type is specified (Availability Trend Option).','Generate_SQL');
5775 Write2FWKLog('Defaulting the period type to view by time dimension.','Generate_SQL');
5776 END IF;
5777 l_PMV_Rec.attribute_name:=':PJI_PERIOD_TYPE';
5778 l_PMV_Rec.attribute_value:=SUBSTR(l_View_By,INSTR(l_View_By,'+')+1);
5779 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
5780 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
5781 l_PMV_Output.EXTEND();
5782 l_PMV_Output(l_PMV_Rec_Ctr):=l_PMV_Rec;
5783 l_PMV_Rec_Ctr:=l_PMV_Rec_Ctr+1;
5784 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,'<<PERIOD_TYPE>>',':PJI_PERIOD_TYPE');
5785 END IF;
5786
5787 /*
5788 ** The following portion of the code is commented.
5789 ** Please uncomment it for debugging purposes only.
5790 */
5791
5792 IF p_PA_DEBUG_MODE = 'Y' THEN
5793 FOR i in 1..l_PMV_Output.LAST loop
5794 Write2FWKLog(l_PMV_Output(i).attribute_name||' - '||l_PMV_Output(i).attribute_value||' - '||l_PMV_Output(i).attribute_type||' - '||l_PMV_Output(i).attribute_data_type, 'Check');
5795 END LOOP;
5796 END IF;
5797
5798 IF p_PA_DEBUG_MODE = 'Y' THEN
5799 Write2FWKLog('After parsing all the parameters.','Generate_SQL');
5800 Write2FWKLog('Before replacing the dimension patterns with NULL values.','Generate_SQL');
5801 END IF;
5802
5803 l_Start_Char_Pos := INSTR(l_PLSQL_Driver_Params,'<<');
5804
5805 IF l_Start_Char_Pos>0 THEN
5806 WHILE l_Start_Char_Pos>0 LOOP
5807 l_PLSQL_Driver_Params:=REPLACE(l_PLSQL_Driver_Params,SUBSTR(l_PLSQL_Driver_Params,l_Start_Char_Pos,INSTR(l_PLSQL_Driver_Params,'>>')-l_Start_Char_Pos+2),'NULL');
5808 l_Start_Char_Pos := INSTR(l_PLSQL_Driver_Params,'<<');
5809 END LOOP;
5810 END IF;
5811
5812 IF p_PA_DEBUG_MODE = 'Y' THEN
5813 Write2FWKLog('After replacing the dimension patterns with NULL values.','Generate_SQL');
5814 Write2FWKLog('Before replacing the <<PL/SQL PARAMS>> pattern with actual values.','Generate_SQL');
5815 END IF;
5816
5817 l_Sql_Statement:=REPLACE(l_Sql_Statement,'<<PL/SQL PARAMS>>',l_PLSQL_Driver_Params);
5818
5819 IF p_PA_DEBUG_MODE = 'Y' THEN
5820 Write2FWKLog('After replacing the <<PL/SQL PARAMS>> pattern with actual values.','Generate_SQL');
5821 Write2FWKLog('Finally, the SQL Statement :'||l_Sql_Statement,'Generate_SQL');
5822 Write2FWKLog('Exiting Generate_SQL...','Generate_SQL');
5823 END IF;
5824
5825 p_Sql_Statement:=l_Sql_Statement;
5826 p_PMV_Output:=l_PMV_Output;
5827 EXCEPTION
5828 WHEN OTHERS THEN
5829 g_SQL_Error_Msg:=SQLERRM();
5830 IF p_PA_DEBUG_MODE = 'Y' THEN
5831 Write2FWKLog(g_SQL_Error_Msg, 3);
5832 END IF;
5833 RAISE;
5834 END Generate_SQL;
5835
5836 END PJI_PMV_ENGINE;
[Home] [Help]