[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_BACKLOG
Source
1 PACKAGE BODY PJI_PMV_BACKLOG AS
2 /* $Header: PJIRF03B.pls 120.5 2005/10/11 18:23:16 appldev noship $ */
3
4 PROCEDURE Get_SQL_PJI_REP_PB1(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
5 , x_PMV_Sql OUT NOCOPY VARCHAR2
6 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7 IS
8 l_Err_Message VARCHAR2(3200);
9 l_PMV_Sql VARCHAR2(3200);
10 BEGIN
11 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
12 P_SELECT_LIST =>'FACT.BACKLOG_NOT_STARTED "PJI_REP_MSR_1",
13 FACT.ACTIVE_BACKLOG "PJI_REP_MSR_2",
14 FACT.DORMANT_BACKLOG "PJI_REP_MSR_3",
15 FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_4",
16 FACT.PRIOR_TOTAL_ENDING_BACKLOG "PJI_REP_MSR_5",
17 FACT.CHANGE_PERCENTAGE "PJI_REP_MSR_6",
18 FACT.TOTAL_BOOKINGS_ITD "PJI_REP_MSR_7",
19 FACT.BACKLOG_PERCENT_OF_BOOKINGS "PJI_REP_MSR_8",
20 FACT.LOST_BACKLOG "PJI_REP_MSR_9",
21 FACT.REVENUE_AT_RISK "PJI_REP_MSR_10",
22 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
23 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
24 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
25 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
26 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
27 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
28 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
29 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
30 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
31 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
32 , P_SQL_STATEMENT => x_PMV_Sql
33 , P_PMV_OUTPUT => x_PMV_Output
34 , P_REGION_CODE => 'PJI_REP_PB1'
35 , P_PLSQL_DRIVER => 'PJI_PMV_BACKLOG.PLSQLDriver_PB1'
36 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
37 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
38 ', <<CURRENCY+FII_CURRENCIES>>'||
39 ', <<AS_OF_DATE>>'||
40 ', <<PERIOD_TYPE>>'||
41 ', <<VIEW_BY>>'||
42 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
43 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
44 END Get_SQL_PJI_REP_PB1;
45
46 PROCEDURE Get_SQL_PJI_REP_PB2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
47 , x_PMV_Sql OUT NOCOPY VARCHAR2
48 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
49 IS
50 l_Err_Message VARCHAR2(3200);
51 l_PMV_Sql VARCHAR2(3200);
52 BEGIN
53 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
54 P_SELECT_LIST => 'FACT.BEGINNING_BACKLOG "PJI_REP_MSR_1",
55 FACT.ORIGINAL_BOOKINGS "PJI_REP_MSR_2",
56 FACT.ADDITIONAL_BOOKINGS "PJI_REP_MSR_3",
57 FACT.BOOKINGS_ADJUSTMENTS "PJI_REP_MSR_4",
58 FACT.CANCELLATIONS "PJI_REP_MSR_5",
59 FACT.TOTAL_NET_BOOKINGS "PJI_REP_MSR_6",
60 FACT.ACCRUED_REVENUE "PJI_REP_MSR_7",
61 FACT.ENDING_REVENUE_AT_RISK "PJI_REP_MSR_14",
62 FACT.ENDING_LOST_BACKLOG "PJI_REP_MSR_8",
63 FACT.ENDING_BACKLOG "PJI_REP_MSR_9",
64 FACT.PRIOR_YEAR "PJI_REP_MSR_10",
65 FACT.ENDING_BACKLOG "PJI_REP_MSR_13",
66 FACT.CHANGE "PJI_REP_MSR_11"'
67 , P_SQL_STATEMENT => x_PMV_Sql
68 , P_PMV_OUTPUT => x_PMV_Output
69 , P_REGION_CODE => 'PJI_REP_PB2'
70 , P_PLSQL_DRIVER => 'PJI_PMV_BACKLOG.PLSQLDriver_PB2'
71 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
72 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
73 ', <<CURRENCY+FII_CURRENCIES>>'||
74 ', <<AS_OF_DATE>>'||
75 ', <<PERIOD_TYPE>>'||
76 ', <<VIEW_BY>>'||
77 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
78 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
79 END Get_SQL_PJI_REP_PB2;
80
81 --**********************************************************************
82 -- Project Backlog Summary - PB1
83 --**********************************************************************
84
85 FUNCTION PLSQLDriver_PB1(
86 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
87 , p_Organization IN VARCHAR2
88 , p_Currency_Type IN VARCHAR2
89 , p_As_Of_Date IN NUMBER
90 , p_Period_Type IN VARCHAR2
91 , p_View_BY IN VARCHAR2
92 , p_Classifications IN VARCHAR2 DEFAULT NULL
93 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
94 )RETURN PJI_REP_PB1_TBL
95 IS
96 PRAGMA AUTONOMOUS_TRANSACTION;
97
98 l_Total_AC_Backlog_Tab PJI_REP_PB1_TBL:=PJI_REP_PB1_TBL();
99 l_Parse_Class_Codes VARCHAR2(1);
100
101 l_Top_Org_Index NUMBER:=0;
102 l_Top_Organization_Name VARCHAR2(240);
103
104 /* Variables for TOTALS calculation*/
105
106 l_Backlog_Not_Started NUMBER:=0;
107 l_Active_Backlog NUMBER:=0;
108 l_Dormant_Backlog NUMBER:=0;
109 l_Total_Ending_Backlog NUMBER:=0;
110 l_Prior_Total_Ending_Backlog NUMBER:=0;
111 l_Total_Bookings_Itd NUMBER:=0;
112 l_Lost_Backlog NUMBER:=0;
113 l_Revenue_At_Risk NUMBER:=0;
114 l_Change_Percentage NUMBER:=0;
115 l_Backlog_Percent_Of_Bookings NUMBER:=0;
116
117 l_curr_record_type_id NUMBER:= 1;
118
119 x_Backlog_Not_Started NUMBER:=0;
120 x_Active_Backlog NUMBER:=0;
121 x_Dormant_Backlog NUMBER:=0;
122 x_Total_Ending_Backlog NUMBER:=0;
123 x_Prior_Total_Ending_Backlog NUMBER:=0;
124 x_Total_Bookings_Itd NUMBER:=0;
125 x_Lost_Backlog NUMBER:=0;
126 x_Revenue_At_Risk NUMBER:=0;
127 x_Change_Percentage NUMBER:=0;
128
129
130 BEGIN
131 /*
132 ** Place a call to all the parse API's which parse the
133 ** parameters passed by PMV and populate all the
134 ** temporary tables.
135 */
136 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit, p_View_BY);
137 PJI_PMV_ENGINE.Convert_Organization(P_TOP_ORGANIZATION_ID => p_Organization,
138 P_VIEW_BY => p_View_BY,
139 p_Top_Organization_Name => l_Top_Organization_Name);
140 PJI_PMV_ENGINE.Convert_Time(p_As_Of_Date, p_Period_Type, p_View_By, 'Y', NULL, NULL,'Y');
141
142 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
143
144 /*
145 ** Determine the fact tables you choose to run the database
146 ** query on ( this step is what we call manual query re-write).
147 */
148 IF PJI_PMV_ENGINE.Convert_Classification(p_Classifications, p_Class_Codes, p_View_BY) = 'N' THEN
149 /*
150 ** Code the SQL statement for all of the following conditions
151 ** 1. Current Year
152 ** 2. Prior Year
153 ** 3. SQL to generate rows with zero's for the view by dimension
154 ** Bulk-Collect the output into a pl/sql table to be returned to
155 ** pmv.
156 */
157 SELECT PJI_REP_PB1( ORG_ID
158 , ORGANIZATION_ID
159 , TIME_ID
160 , TIME_KEY
161 , PROJECT_CLASS_ID
162 , SUM( BACKLOG_NOT_STARTED )
163 , SUM( ACTIVE_BACKLOG )
164 , SUM( DORMANT_BACKLOG )
165 , SUM( TOTAL_ENDING_BACKLOG )
166 , SUM( PRIOR_TOTAL_ENDING_BACKLOG )
167 , 0
168 , SUM( TOTAL_BOOKINGS_ITD )
169 , 0
170 , SUM( LOST_BACKLOG )
171 , SUM( REVENUE_AT_RISK )
172 , 0
173 , 0
174 , 0
175 , 0
176 , 0
177 , 0
178 , 0
179 , 0
180 , 0
181 , 0)
182 BULK COLLECT INTO l_Total_AC_Backlog_Tab
183 FROM
184 ( SELECT /*+ ORDERED */
185 HOU.NAME ORG_ID
186 , HORG.NAME ORGANIZATION_ID
187 , TIME.NAME TIME_ID
188 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
189 , '-1' PROJECT_CLASS_ID
190 , DORMANT_BACKLOG_START BACKLOG_NOT_STARTED
191 , ACTIVE_BACKLOG
192 , DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG
193 , DORMANT_BACKLOG_INACTIV
194 + ACTIVE_BACKLOG
195 + DORMANT_BACKLOG_START TOTAL_ENDING_BACKLOG
196 , 0 PRIOR_TOTAL_ENDING_BACKLOG
197 , 0 CHANGE_PERCENTAGE
198 , INITIAL_FUNDING_AMOUNT
199 + ADDITIONAL_FUNDING_AMOUNT
200 + FUNDING_ADJUSTMENT_AMOUNT
201 + CANCELLED_FUNDING_AMOUNT TOTAL_BOOKINGS_ITD
202 , 0 BACKLOG_PERCENT_OF_BOOKINGS
203 , LOST_BACKLOG LOST_BACKLOG
204 , REVENUE_AT_RISK
205 , 0
206 , 0
207 , 0
208 , 0
209 , 0
210 , 0
211 , 0
212 , 0
213 , 0
214 , 0
215 FROM
216 PJI_PMV_ITD_DIM_TMP TIME
217 , PJI_PMV_ORGZ_DIM_TMP HORG
218 , PJI_AC_ORGO_F_MV FCT
219 , PJI_PMV_ORG_DIM_TMP HOU
220 WHERE
221 FCT.ORG_ID = HOU.ID
222 AND FCT.ORGANIZATION_ID = HORG.ID
223 AND FCT.TIME_ID = TIME.ID
224 AND TIME.ID IS NOT NULL
225 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
226 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
227 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
228 UNION ALL
229 SELECT /*+ ORDERED */
230 HOU.NAME ORG_ID
231 , HORG.NAME ORGANIZATION_ID
232 , TIME.NAME TIME_ID
233 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
234 , '-1' PROJECT_CLASS_ID
235 , 0 BACKLOG_NOT_STARTED
236 , 0 ACTIVE_BACKLOG
237 , 0 DORMANT_BACKLOG
238 , 0 TOTAL_ENDING_BACKLOG
239 , DORMANT_BACKLOG_INACTIV
240 + ACTIVE_BACKLOG
241 + DORMANT_BACKLOG_START PRIOR_TOTAL_ENDING_BACKLOG
242 , 0 CHANGE_PERCENTAGE
243 , 0 TOTAL_BOOKINGS_ITD
244 , 0 BACKLOG_PERCENT_OF_BOOKINGS
245 , 0 LOST_BACKLOG
246 , 0 REVENUE_AT_RISK
247 , 0
248 , 0
249 , 0
250 , 0
251 , 0
252 , 0
253 , 0
254 , 0
255 , 0
256 , 0
257 FROM
258 PJI_PMV_ITD_DIM_TMP TIME
259 , PJI_PMV_ORGZ_DIM_TMP HORG
260 , PJI_AC_ORGO_F_MV FCT
261 , PJI_PMV_ORG_DIM_TMP HOU
262 WHERE
263 FCT.ORG_ID = HOU.ID
264 AND FCT.ORGANIZATION_ID = HORG.ID
265 AND FCT.TIME_ID = TIME.PRIOR_ID
266 AND TIME.PRIOR_ID IS NOT NULL
267 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
268 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
269 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
270 UNION ALL
271 SELECT NAME ORG_ID
272 , '-1' ORGANIZATION_ID
273 , '-1' TIME_ID
274 , -1 TIME_KEY
275 , '-1' PROJECT_CLASS_ID
276 , 0 BACKLOG_NOT_STARTED
277 , 0 ACTIVE_BACKLOG
278 , 0 DORMANT_BACKLOG
279 , 0 TOTAL_ENDING_BACKLOG
280 , 0 PRIOR_TOTAL_ENDING_BACKLOG
281 , 0 CHANGE_PERCENTAGE
282 , 0 TOTAL_BOOKINGS_ITD
283 , 0 BACKLOG_PERCENT_OF_BOOKINGS
284 , 0 LOST_BACKLOG
285 , 0 REVENUE_AT_RISK
286 , 0
287 , 0
288 , 0
289 , 0
290 , 0
291 , 0
292 , 0
293 , 0
294 , 0
295 , 0
296 FROM PJI_PMV_ORG_DIM_TMP
297 WHERE NAME <> '-1'
298 UNION ALL
299 SELECT '-1' ORG_ID
300 , NAME ORGANIZATION_ID
301 , '-1' TIME_ID
302 , -1 TIME_KEY
303 , '-1' PROJECT_CLASS_ID
304 , 0 BACKLOG_NOT_STARTED
305 , 0 ACTIVE_BACKLOG
306 , 0 DORMANT_BACKLOG
307 , 0 TOTAL_ENDING_BACKLOG
308 , 0 PRIOR_TOTAL_ENDING_BACKLOG
309 , 0 CHANGE_PERCENTAGE
310 , 0 TOTAL_BOOKINGS_ITD
311 , 0 BACKLOG_PERCENT_OF_BOOKINGS
312 , 0 LOST_BACKLOG
313 , 0 REVENUE_AT_RISK
314 , 0
315 , 0
316 , 0
317 , 0
318 , 0
319 , 0
320 , 0
321 , 0
322 , 0
323 , 0
324 FROM PJI_PMV_ORGZ_DIM_TMP
325 WHERE NAME <> '-1'
326 UNION ALL
327 SELECT '-1' ORG_ID
328 , '-1' ORGANIZATION_ID
329 , NAME TIME_ID
330 , ID TIME_KEY
331 , '-1' PROJECT_CLASS_ID
332 , 0 BACKLOG_NOT_STARTED
333 , 0 ACTIVE_BACKLOG
334 , 0 DORMANT_BACKLOG
335 , 0 TOTAL_ENDING_BACKLOG
336 , 0 PRIOR_TOTAL_ENDING_BACKLOG
337 , 0 CHANGE_PERCENTAGE
338 , 0 TOTAL_BOOKINGS_ITD
339 , 0 BACKLOG_PERCENT_OF_BOOKINGS
340 , 0 LOST_BACKLOG
341 , 0 REVENUE_AT_RISK
342 , 0
343 , 0
344 , 0
345 , 0
346 , 0
347 , 0
348 , 0
349 , 0
350 , 0
351 , 0
352 FROM PJI_PMV_TIME_DIM_TMP
353 WHERE NAME <> '-1')
354 GROUP BY
355 ORG_ID
356 , ORGANIZATION_ID
357 , TIME_KEY
358 , TIME_ID
359 , PROJECT_CLASS_ID;
360 ELSE
361 /*
362 ** Code the SQL statement for all of the following conditions
363 ** 1. Current Year
364 ** 2. Prior Year
365 ** 3. SQL to generate rows with zero's for the view by dimension
366 */
367 SELECT PJI_REP_PB1
368 ( ORG_ID
369 , ORGANIZATION_ID
370 , TIME_ID
371 , TIME_KEY
372 , PROJECT_CLASS_ID
373 , SUM( BACKLOG_NOT_STARTED )
374 , SUM( ACTIVE_BACKLOG )
375 , SUM( DORMANT_BACKLOG )
376 , SUM( TOTAL_ENDING_BACKLOG )
377 , SUM( PRIOR_TOTAL_ENDING_BACKLOG )
378 , 0
379 , SUM( TOTAL_BOOKINGS_ITD )
380 , 0
381 , SUM( LOST_BACKLOG )
382 , SUM( REVENUE_AT_RISK )
383 , 0
384 , 0
385 , 0
386 , 0
387 , 0
388 , 0
389 , 0
390 , 0
391 , 0
392 , 0 )
393 BULK COLLECT INTO l_Total_AC_Backlog_Tab
394 FROM
395 ( SELECT /*+ ORDERED */
396 HOU.NAME ORG_ID
397 , HORG.NAME ORGANIZATION_ID
398 , TIME.NAME TIME_ID
399 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
400 , CLS.NAME PROJECT_CLASS_ID
401 , DORMANT_BACKLOG_START BACKLOG_NOT_STARTED
402 , ACTIVE_BACKLOG
403 , DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG
404 , DORMANT_BACKLOG_INACTIV
405 + ACTIVE_BACKLOG
406 + DORMANT_BACKLOG_START TOTAL_ENDING_BACKLOG
407 , 0 PRIOR_TOTAL_ENDING_BACKLOG
408 , 0 CHANGE_PERCENTAGE
409 , INITIAL_FUNDING_AMOUNT
410 + ADDITIONAL_FUNDING_AMOUNT
411 + FUNDING_ADJUSTMENT_AMOUNT
412 + CANCELLED_FUNDING_AMOUNT TOTAL_BOOKINGS_ITD
416 , 0
413 , 0 BACKLOG_PERCENT_OF_BOOKINGS
414 , LOST_BACKLOG LOST_BACKLOG
415 , REVENUE_AT_RISK
417 , 0
418 , 0
419 , 0
420 , 0
421 , 0
422 , 0
423 , 0
424 , 0
425 , 0
426 FROM PJI_PMV_ITD_DIM_TMP TIME
427 , PJI_PMV_ORGZ_DIM_TMP HORG
428 , PJI_PMV_CLS_DIM_TMP CLS
429 , PJI_AC_CLSO_F_MV FCT
430 , PJI_PMV_ORG_DIM_TMP HOU
431 WHERE
432 FCT.PROJECT_ORG_ID = HOU.ID
433 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
434 AND FCT.TIME_ID = TIME.ID
435 AND TIME.ID IS NOT NULL
436 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
437 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
438 AND FCT.PROJECT_CLASS_ID = CLS.ID
439 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
440 UNION ALL
441 SELECT /*+ ORDERED */
442 HOU.NAME ORG_ID
443 , HORG.NAME ORGANIZATION_ID
444 , TIME.NAME TIME_ID
445 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
446 , CLS.NAME PROJECT_CLASS_ID
447 , 0 BACKLOG_NOT_STARTED
448 , 0 ACTIVE_BACKLOG
449 , 0 DORMANT_BACKLOG
450 , 0 TOTAL_ENDING_BACKLOG
451 , DORMANT_BACKLOG_INACTIV
452 + ACTIVE_BACKLOG
453 + DORMANT_BACKLOG_START PRIOR_TOTAL_ENDING_BACKLOG
454 , 0 CHANGE_PERCENTAGE
455 , 0 TOTAL_BOOKINGS_ITD
456 , 0 BACKLOG_PERCENT_OF_BOOKINGS
457 , 0 LOST_BACKLOG
458 , 0 REVENUE_AT_RISK
459 , 0
460 , 0
461 , 0
462 , 0
463 , 0
464 , 0
465 , 0
466 , 0
467 , 0
468 , 0
469 FROM
470 PJI_PMV_ITD_DIM_TMP TIME
471 , PJI_PMV_ORGZ_DIM_TMP HORG
472 , PJI_PMV_CLS_DIM_TMP CLS
473 , PJI_AC_CLSO_F_MV FCT
474 , PJI_PMV_ORG_DIM_TMP HOU
475 WHERE
476 FCT.PROJECT_ORG_ID = HOU.ID
477 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
478 AND FCT.TIME_ID = TIME.PRIOR_ID
479 AND TIME.PRIOR_ID IS NOT NULL
480 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
481 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
482 AND FCT.PROJECT_CLASS_ID = CLS.ID
483 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
484 UNION ALL
485 SELECT NAME ORG_ID
486 , '-1' ORGANIZATION_ID
487 , '-1' TIME_ID
488 , -1 TIME_KEY
489 , '-1' PROJECT_CLASS_ID
490 , 0 BACKLOG_NOT_STARTED
491 , 0 ACTIVE_BACKLOG
492 , 0 DORMANT_BACKLOG
493 , 0 TOTAL_ENDING_BACKLOG
494 , 0 PRIOR_TOTAL_ENDING_BACKLOG
495 , 0 CHANGE_PERCENTAGE
496 , 0 TOTAL_BOOKINGS_ITD
497 , 0 BACKLOG_PERCENT_OF_BOOKINGS
498 , 0 LOST_BACKLOG
499 , 0 REVENUE_AT_RISK
500 , 0
501 , 0
502 , 0
503 , 0
504 , 0
505 , 0
506 , 0
507 , 0
508 , 0
509 , 0
510 FROM PJI_PMV_ORG_DIM_TMP
511 WHERE NAME <> '-1'
512 UNION ALL
513 SELECT '-1' ORG_ID
514 , NAME ORGANIZATION_ID
515 , '-1' TIME_ID
516 , -1 TIME_KEY
517 , '-1' PROJECT_CLASS_ID
518 , 0 BACKLOG_NOT_STARTED
519 , 0 ACTIVE_BACKLOG
520 , 0 DORMANT_BACKLOG
521 , 0 TOTAL_ENDING_BACKLOG
522 , 0 PRIOR_TOTAL_ENDING_BACKLOG
523 , 0 CHANGE_PERCENTAGE
524 , 0 TOTAL_BOOKINGS_ITD
525 , 0 BACKLOG_PERCENT_OF_BOOKINGS
526 , 0 LOST_BACKLOG
527 , 0 REVENUE_AT_RISK
528 , 0
529 , 0
530 , 0
531 , 0
532 , 0
533 , 0
534 , 0
535 , 0
536 , 0
537 , 0
538 FROM PJI_PMV_ORGZ_DIM_TMP
539 WHERE NAME <> '-1'
540 UNION ALL
541 SELECT '-1' ORG_ID
542 , '-1' ORGANIZATION_ID
543 , NAME TIME_ID
544 , ID TIME_KEY
545 , '-1' PROJECT_CLASS_ID
546 , 0 BACKLOG_NOT_STARTED
547 , 0 ACTIVE_BACKLOG
548 , 0 DORMANT_BACKLOG
549 , 0 TOTAL_ENDING_BACKLOG
550 , 0 PRIOR_TOTAL_ENDING_BACKLOG
551 , 0 CHANGE_PERCENTAGE
552 , 0 TOTAL_BOOKINGS_ITD
553 , 0 BACKLOG_PERCENT_OF_BOOKINGS
554 , 0 LOST_BACKLOG
555 , 0 REVENUE_AT_RISK
556 , 0
557 , 0
558 , 0
559 , 0
560 , 0
561 , 0
562 , 0
563 , 0
564 , 0
565 , 0
566 FROM PJI_PMV_TIME_DIM_TMP
567 WHERE NAME <> '-1'
568 UNION ALL
569 SELECT '-1' ORG_ID
570 , '-1' ORGANIZATION_ID
571 , '-1' TIME_ID
575 , 0 ACTIVE_BACKLOG
572 , -1 TIME_KEY
573 , NAME PROJECT_CLASS_ID
574 , 0 BACKLOG_NOT_STARTED
576 , 0 DORMANT_BACKLOG
577 , 0 TOTAL_ENDING_BACKLOG
578 , 0 PRIOR_TOTAL_ENDING_BACKLOG
579 , 0 CHANGE_PERCENTAGE
580 , 0 TOTAL_BOOKINGS_ITD
581 , 0 BACKLOG_PERCENT_OF_BOOKINGS
582 , 0 LOST_BACKLOG
583 , 0 REVENUE_AT_RISK
584 , 0
585 , 0
586 , 0
587 , 0
588 , 0
589 , 0
590 , 0
591 , 0
592 , 0
593 , 0
594 FROM PJI_PMV_CLS_DIM_TMP
595 WHERE NAME <> '-1') FACT
596 GROUP BY
597 ORG_ID
598 , ORGANIZATION_ID
599 , TIME_KEY
600 , TIME_ID
601 , PROJECT_CLASS_ID;
602 END IF;
603
604 FOR i in 1..l_Total_AC_Backlog_Tab.COUNT
605 LOOP
606 IF p_View_By = 'OG' THEN
607 IF l_Total_AC_Backlog_Tab(i).ORGANIZATION_ID = l_Top_Organization_Name THEN
608 l_Top_Org_Index:=i;
609
610 x_Backlog_Not_Started :=NVL(l_Total_AC_Backlog_Tab(i).BACKLOG_NOT_STARTED,0);
611 x_Active_Backlog :=NVL(l_Total_AC_Backlog_Tab(i).ACTIVE_BACKLOG,0);
612 x_Dormant_Backlog :=NVL(l_Total_AC_Backlog_Tab(i).DORMANT_BACKLOG,0);
613 x_Total_Ending_Backlog :=NVL(l_Total_AC_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
614 x_Prior_Total_Ending_Backlog :=NVL(l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
615 x_Total_Bookings_Itd :=NVL(l_Total_AC_Backlog_Tab(i).TOTAL_BOOKINGS_ITD,0);
616 x_Lost_Backlog :=NVL(l_Total_AC_Backlog_Tab(i).LOST_BACKLOG,0);
617 x_Revenue_At_Risk :=NVL(l_Total_AC_Backlog_Tab(i).REVENUE_AT_RISK,0);
618
619
620 ELSE
621
622 l_Backlog_Not_Started :=l_Backlog_Not_Started +
623 NVL(l_Total_AC_Backlog_Tab(i).BACKLOG_NOT_STARTED,0);
624 l_Active_Backlog :=l_Active_Backlog +
625 NVL(l_Total_AC_Backlog_Tab(i).ACTIVE_BACKLOG,0);
626 l_Dormant_Backlog :=l_Dormant_Backlog +
627 NVL(l_Total_AC_Backlog_Tab(i).DORMANT_BACKLOG,0);
628 l_Total_Ending_Backlog :=l_Total_Ending_Backlog +
629 NVL(l_Total_AC_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
630 l_Prior_Total_Ending_Backlog:=l_Prior_Total_Ending_Backlog +
631 NVL(l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
632 l_Total_Bookings_Itd :=l_Total_Bookings_Itd +
633 NVL(l_Total_AC_Backlog_Tab(i).TOTAL_BOOKINGS_ITD,0);
634 l_Lost_Backlog :=l_Lost_Backlog +
635 NVL(l_Total_AC_Backlog_Tab(i).LOST_BACKLOG,0);
636 l_Revenue_At_Risk :=l_Revenue_At_Risk +
637 NVL(l_Total_AC_Backlog_Tab(i).REVENUE_AT_RISK,0);
638 END IF;
639 ELSE
640 l_Backlog_Not_Started :=l_Backlog_Not_Started +
641 NVL(l_Total_AC_Backlog_Tab(i).BACKLOG_NOT_STARTED,0);
642 l_Active_Backlog :=l_Active_Backlog +
643 NVL(l_Total_AC_Backlog_Tab(i).ACTIVE_BACKLOG,0);
644 l_Dormant_Backlog :=l_Dormant_Backlog +
645 NVL(l_Total_AC_Backlog_Tab(i).DORMANT_BACKLOG,0);
646 l_Total_Ending_Backlog :=l_Total_Ending_Backlog +
647 NVL(l_Total_AC_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
648 l_Prior_Total_Ending_Backlog:=l_Prior_Total_Ending_Backlog +
649 NVL(l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
650 l_Total_Bookings_Itd :=l_Total_Bookings_Itd +
651 NVL(l_Total_AC_Backlog_Tab(i).TOTAL_BOOKINGS_ITD,0);
652 l_Lost_Backlog :=l_Lost_Backlog +
653 NVL(l_Total_AC_Backlog_Tab(i).LOST_BACKLOG,0);
654 l_Revenue_At_Risk :=l_Revenue_At_Risk +
655 NVL(l_Total_AC_Backlog_Tab(i).REVENUE_AT_RISK,0);
656 END IF;
657
658 IF NVL(l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG, 0)=0 THEN
659 l_Total_AC_Backlog_Tab(i).CHANGE_PERCENTAGE:= NULL;
660 ELSE
661 l_Total_AC_Backlog_Tab(i).CHANGE_PERCENTAGE:=
662 (l_Total_AC_Backlog_Tab(i).TOTAL_ENDING_BACKLOG
663 -l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG)*100
664 /ABS(l_Total_AC_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG);
665 END IF;
666 IF NVL(l_Total_AC_Backlog_Tab(i).TOTAL_BOOKINGS_ITD, 0)=0 THEN
667 l_Total_AC_Backlog_Tab(i).BACKLOG_PERCENT_OF_BOOKINGS :=NULL;
668 ELSE
669 l_Total_AC_Backlog_Tab(i).BACKLOG_PERCENT_OF_BOOKINGS:=
670 l_Total_AC_Backlog_Tab(i).TOTAL_ENDING_BACKLOG*100
671 /l_Total_AC_Backlog_Tab(i).TOTAL_BOOKINGS_ITD;
672 END IF;
673 END LOOP;
674
675 IF p_View_by ='OG' THEN
676 l_Total_AC_Backlog_Tab(l_Top_Org_Index).BACKLOG_NOT_STARTED
677 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).BACKLOG_NOT_STARTED,0)
678 - l_Backlog_Not_Started;
679 l_Total_AC_Backlog_Tab(l_Top_Org_Index).ACTIVE_BACKLOG
683 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).DORMANT_BACKLOG,0)
680 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).ACTIVE_BACKLOG,0)
681 - l_Active_Backlog;
682 l_Total_AC_Backlog_Tab(l_Top_Org_Index).DORMANT_BACKLOG
684 - l_Dormant_Backlog;
685 l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG
686 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG,0)
687 - l_Total_Ending_Backlog;
688 l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG
689 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG,0)
690 - l_Prior_Total_Ending_Backlog;
691 l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS_ITD
692 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS_ITD,0)
693 - l_Total_Bookings_Itd;
694 l_Total_AC_Backlog_Tab(l_Top_Org_Index).LOST_BACKLOG
695 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).LOST_BACKLOG,0)
696 - l_Lost_Backlog;
697 l_Total_AC_Backlog_Tab(l_Top_Org_Index).REVENUE_AT_RISK
698 :=NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).REVENUE_AT_RISK,0)
699 - l_Revenue_At_Risk;
700
701 IF NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG, 0)=0 THEN
702 l_Total_AC_Backlog_Tab(l_Top_Org_Index).CHANGE_PERCENTAGE:= NULL;
703 ELSE
704 l_Total_AC_Backlog_Tab(l_Top_Org_Index).CHANGE_PERCENTAGE:=
705 (l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG
706 -l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG)*100
707 /ABS(l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG);
708 END IF;
709 IF NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS_ITD, 0)=0 THEN
710 l_Total_AC_Backlog_Tab(l_Top_Org_Index).BACKLOG_PERCENT_OF_BOOKINGS :=NULL;
711 ELSE
712 l_Total_AC_Backlog_Tab(l_Top_Org_Index).BACKLOG_PERCENT_OF_BOOKINGS:=
713 l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG*100
714 /l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS_ITD;
715 END IF;
716
717 IF
718 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).BACKLOG_NOT_STARTED,0)=0 AND
719 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).ACTIVE_BACKLOG,0)=0 AND
720 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).DORMANT_BACKLOG,0)=0 AND
721 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG,0)=0 AND
722 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG,0)=0 AND
723 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS_ITD,0)=0 AND
724 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).LOST_BACKLOG,0)=0 AND
725 NVL(l_Total_AC_Backlog_Tab(l_Top_Org_Index).REVENUE_AT_RISK,0)=0
726
727 THEN
728 l_Total_AC_Backlog_Tab.DELETE(l_Top_Org_Index);
729 END IF;
730
731 l_Backlog_Not_Started :=x_Backlog_Not_Started;
732 l_Active_Backlog :=x_Active_Backlog;
733 l_Dormant_Backlog :=x_Dormant_Backlog;
734 l_Total_Ending_Backlog :=x_Total_Ending_Backlog;
735 l_Prior_Total_Ending_Backlog :=x_Prior_Total_Ending_Backlog;
736 l_Total_Bookings_Itd :=x_Total_Bookings_Itd;
737 l_Lost_Backlog :=x_Lost_Backlog;
738 l_Revenue_At_Risk :=x_Revenue_At_Risk;
739
740 END IF;
741
742
743
744 IF l_Total_AC_Backlog_Tab.COUNT >0 THEN
745 FOR i in l_Total_AC_Backlog_Tab.FIRST..l_Total_AC_Backlog_Tab.LAST
746 LOOP
747 IF l_Total_AC_Backlog_Tab.EXISTS(i) THEN
748 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_1:= l_Backlog_Not_Started;
749 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_2:= l_Active_Backlog;
750 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_3:= l_Dormant_Backlog;
751 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_4:= l_Total_Ending_Backlog;
752 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_5:= l_Prior_Total_Ending_Backlog;
753 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_7:= l_Total_Bookings_Itd;
754 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_9:= l_Lost_Backlog;
755 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_10:=l_Revenue_At_Risk;
756
757 IF l_Prior_Total_Ending_Backlog = 0 THEN
758 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_6 :=NULL;
759 ELSE
760 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_6:=
761 (l_Total_Ending_Backlog - l_Prior_Total_Ending_Backlog)*100
762 /ABS(l_Prior_Total_Ending_Backlog);
763 END IF;
764
765 IF l_Total_Bookings_Itd = 0 THEN
766 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_8 :=NULL;
767 ELSE
768 l_Total_AC_Backlog_Tab(i).PJI_REP_TOTAL_8 :=
769 (l_Total_Ending_Backlog)*100/(l_Total_Bookings_Itd);
770 END IF;
771 END IF;
772 END LOOP;
773 END IF;
774 /*
775 ** Return the bulk collected table back to pmv.
776 */
777 COMMIT;
778 RETURN l_Total_AC_Backlog_Tab;
779 END PLSQLDriver_PB1;
780
781
782 --**********************************************************************
783 -- Project Backlog Trend - PB2
787 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
784 --**********************************************************************
785
786 FUNCTION PLSQLDriver_PB2 (
788 , p_Organization IN VARCHAR2
789 , p_Currency_Type IN VARCHAR2
790 , p_As_Of_Date IN NUMBER
791 , p_Period_Type IN VARCHAR2
792 , p_View_BY IN VARCHAR2
793 , p_Classifications IN VARCHAR2 DEFAULT NULL
794 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
795 ) RETURN PJI_REP_PB2_TBL
796 IS
797 PRAGMA AUTONOMOUS_TRANSACTION;
798 l_Backlog_Trend_Tab PJI_REP_PB2_TBL:=PJI_REP_PB2_TBL();
799 l_Parse_Class_Codes VARCHAR2(1);
800 l_Ending_Backlog_itd NUMBER;
801 l_Ending_Lost_Backlog_itd NUMBER;
802 l_Ending_Revenue_at_Risk_itd NUMBER;
803 l_Ending_Prior_Backlog_itd NUMBER;
804
805 l_Top_Organization_Name VARCHAR2(240);
806 l_curr_record_type_id NUMBER:= 1;
807
808 BEGIN
809 /*
810 ** Place a call to all the parse API's which parse the
811 ** parameters passed by PMV and populate all the
812 ** temporary tables.
813 */
814 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit, p_View_BY);
815 PJI_PMV_ENGINE.Convert_Organization(p_Top_Organization_ID=>p_Organization, p_View_BY=>p_View_BY, p_Top_Organization_Name=>l_Top_Organization_Name);
816 PJI_PMV_ENGINE.Convert_Time(p_As_Of_Date, p_Period_Type, p_View_By, 'Y', NULL, NULL, 'Y');
817
818 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
819 /*
820 ** Determine the fact tables you choose to run the database
821 ** query on ( this step is what we call manual query re-write).
822 */
823 IF PJI_PMV_ENGINE.Convert_Classification(p_Classifications, p_Class_Codes, p_View_BY) = 'N' THEN
824 /*
825 ** Code the SQL statement for all of the following conditions
826 ** 1. Current Year
827 ** 2. Prior Year
828 ** 3. SQL to generate rows with zero's for the view by dimension
829 ** Bulk-Collect the output into a pl/sql table to be returned to
830 ** pmv.
831 */
832 SELECT PJI_REP_PB2( ORG_ID
833 , ORGANIZATION_ID
834 , TIME_ID
835 , TIME_KEY
836 , PROJECT_CLASS_ID
837 , 0
838 , SUM( ORIGINAL_BOOKINGS )
839 , SUM( ADDITIONAL_BOOKINGS )
840 , SUM( BOOKINGS_ADJUSTMENTS )
841 , -SUM( CANCELLATIONS )
842 , SUM( TOTAL_NET_BOOKINGS )
843 , SUM( ACCRUED_REVENUE )
844 , SUM( PRIOR_YEAR )
845 , SUM( ENDING_LOST_BACKLOG )
846 , SUM( ENDING_BACKLOG )
847 , SUM( ENDING_REVENUE_AT_RISK)
848 , SUM( LOST_BACKLOG )
849 , SUM( BACKLOG )
850 , SUM( REVENUE_AT_RISK )
851 , 0 )
852 BULK COLLECT INTO l_Backlog_Trend_Tab
853 FROM
854 ( SELECT /*+ ORDERED */
855 HOU.NAME ORG_ID
856 , HORG.NAME ORGANIZATION_ID
857 , TIME.NAME TIME_ID
858 , DECODE(p_View_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY
859 , '-1' PROJECT_CLASS_ID
860 , 0
861 , INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS
862 , ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS
863 , FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS
864 , CANCELLED_FUNDING_AMOUNT CANCELLATIONS
865 , INITIAL_FUNDING_AMOUNT
866 + ADDITIONAL_FUNDING_AMOUNT
867 + FUNDING_ADJUSTMENT_AMOUNT
868 + CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS
869 , REVENUE ACCRUED_REVENUE
870 , 0 PRIOR_YEAR
871 , 0 ENDING_LOST_BACKLOG
872 , 0 ENDING_BACKLOG
873 , 0 ENDING_REVENUE_AT_RISK
874 , LOST_BACKLOG
875 , DORMANT_BACKLOG_INACTIV
876 + ACTIVE_BACKLOG
877 + DORMANT_BACKLOG_START BACKLOG
878 , REVENUE_AT_RISK REVENUE_AT_RISK
879 , 0
880 FROM
881 PJI_PMV_TIME_DIM_TMP TIME
882 , PJI_PMV_ORGZ_DIM_TMP HORG
883 , PJI_AC_ORGO_F_MV FCT
884 , PJI_PMV_ORG_DIM_TMP HOU
885 WHERE
886 FCT.ORG_ID = HOU.ID
887 AND FCT.ORGANIZATION_ID = HORG.ID
888 AND FCT.TIME_ID = TIME.ID
889 AND TIME.ID IS NOT NULL
890 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
891 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
892 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
893 UNION ALL
894 SELECT /*+ ORDERED */
895 HOU.NAME ORG_ID
896 , HORG.NAME ORGANIZATION_ID
897 , TIME.NAME TIME_ID
898 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
899 , '-1' PROJECT_CLASS_ID
900 , 0
901 , 0 ORIGINAL_BOOKINGS
902 , 0 ADDITIONAL_BOOKINGS
903 , 0 BOOKINGS_ADJUSTMENTS
904 , 0 CANCELLATIONS
905 , 0 TOTAL_NET_BOOKINGS
906 , 0 ACCRUED_REVENUE
907 , DORMANT_BACKLOG_INACTIV
908 + ACTIVE_BACKLOG
909 + DORMANT_BACKLOG_START PRIOR_YEAR
910 , 0 ENDING_LOST_BACKLOG
914 , 0 BACKLOG
911 , 0 ENDING_BACKLOG
912 , 0 ENDING_REVENUE_AT_RISK
913 , 0 LOST_BACKLOG
915 , 0 REVENUE_AT_RISK
916 , 0
917 FROM
918 PJI_PMV_TIME_DIM_TMP TIME
919 , PJI_PMV_ORGZ_DIM_TMP HORG
920 , PJI_AC_ORGO_F_MV FCT
921 , PJI_PMV_ORG_DIM_TMP HOU
922
923 WHERE
924 FCT.ORG_ID = HOU.ID
925 AND FCT.ORGANIZATION_ID = HORG.ID
926 AND FCT.TIME_ID = TIME.PRIOR_ID
927 AND TIME.PRIOR_ID IS NOT NULL
928 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
929 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
930 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
931 UNION ALL
932 SELECT '-1' ORG_ID
933 , '-1' ORGANIZATION_ID
934 , NAME TIME_ID
935 , ORDER_BY_ID TIME_KEY
936 , '-1' PROJECT_CLASS_ID
937 , 0
938 , 0 ORIGINAL_BOOKINGS
939 , 0 ADDITIONAL_BOOKINGS
940 , 0 BOOKINGS_ADJUSTMENTS
941 , 0 CANCELLATIONS
942 , 0 TOTAL_NET_BOOKINGS
943 , 0 ACCRUED_REVENUE
944 , 0 PRIOR_YEAR
945 , 0 ENDING_LOST_BACKLOG
946 , 0 ENDING_BACKLOG
947 , 0 ENDING_REVENUE_AT_RISK
948 , 0 LOST_BACKLOG
949 , 0 BACKLOG
950 , 0 REVENUE_AT_RISK
951 , 0
952 FROM PJI_PMV_TIME_DIM_TMP
953 WHERE NAME <> '-1')
954 GROUP BY
955 ORG_ID
956 , ORGANIZATION_ID
957 , TIME_KEY
958 , TIME_ID
959 , PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
960
961 SELECT /*+ ORDERED */
962 SUM( DORMANT_BACKLOG_INACTIV
963 + ACTIVE_BACKLOG
964 + DORMANT_BACKLOG_START ), SUM(LOST_BACKLOG), SUM(REVENUE_AT_RISK)
965 INTO l_Ending_Backlog_itd, l_Ending_Lost_Backlog_itd, l_Ending_Revenue_at_Risk_itd
966 FROM PJI_PMV_ITD_DIM_TMP TIME
967 , PJI_PMV_ORGZ_DIM_TMP HORG
968 , PJI_AC_ORGO_F_MV FCT
969 , PJI_PMV_ORG_DIM_TMP HOU
970 WHERE
971 FCT.ORG_ID = HOU.ID
972 AND FCT.ORGANIZATION_ID = HORG.ID
973 AND FCT.TIME_ID = TIME.ID
974 AND TIME.ID IS NOT NULL
975 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
976 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
977 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id;
978 SELECT /*+ ORDERED */ SUM( DORMANT_BACKLOG_INACTIV
979 + ACTIVE_BACKLOG
980 + DORMANT_BACKLOG_START )
981 INTO l_Ending_Prior_Backlog_itd
982 FROM PJI_PMV_ITD_DIM_TMP TIME
983 , PJI_PMV_ORGZ_DIM_TMP HORG
984 , PJI_AC_ORGO_F_MV FCT
985 , PJI_PMV_ORG_DIM_TMP HOU
986 WHERE
987 FCT.ORG_ID = HOU.ID
988 AND FCT.ORGANIZATION_ID = HORG.ID
989 AND FCT.TIME_ID = TIME.PRIOR_ID
990 AND TIME.PRIOR_ID IS NOT NULL
991 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
992 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
993 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id;
994 ELSE
995 /*
996 ** Code the SQL statement for all of the following conditions
997 ** 1. Current Year
998 ** 2. Prior Year
999 ** 3. SQL to generate rows with zero's for the view by dimension
1000 */
1001 SELECT PJI_REP_PB2( ORG_ID
1002 , ORGANIZATION_ID
1003 , TIME_ID
1004 , TIME_KEY
1005 , PROJECT_CLASS_ID
1006 , 0
1007 , SUM( ORIGINAL_BOOKINGS )
1008 , SUM( ADDITIONAL_BOOKINGS )
1009 , SUM( BOOKINGS_ADJUSTMENTS )
1010 , -SUM( CANCELLATIONS )
1011 , SUM( TOTAL_NET_BOOKINGS )
1012 , SUM( ACCRUED_REVENUE )
1013 , SUM( PRIOR_YEAR )
1014 , SUM( ENDING_LOST_BACKLOG )
1015 , SUM( ENDING_BACKLOG )
1016 , SUM( ENDING_REVENUE_AT_RISK)
1017 , SUM( LOST_BACKLOG )
1018 , SUM( BACKLOG )
1019 , SUM( REVENUE_AT_RISK )
1020 , 0 )
1021 BULK COLLECT INTO l_Backlog_Trend_Tab
1022 FROM
1023 ( SELECT /*+ ORDERED */
1024 HOU.NAME ORG_ID
1025 , HORG.NAME ORGANIZATION_ID
1026 , TIME.NAME TIME_ID
1027 , DECODE(p_View_BY, 'TM',TIME.ORDER_BY_ID, -1) TIME_KEY
1028 , CLS.NAME PROJECT_CLASS_ID
1029 , 0
1030 , INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS
1031 , ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS
1032 , FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS
1033 , CANCELLED_FUNDING_AMOUNT CANCELLATIONS
1034 , INITIAL_FUNDING_AMOUNT
1035 + ADDITIONAL_FUNDING_AMOUNT
1036 + FUNDING_ADJUSTMENT_AMOUNT
1037 + CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS
1038 , REVENUE ACCRUED_REVENUE
1039 , 0 PRIOR_YEAR
1040 , 0 ENDING_LOST_BACKLOG
1041 , 0 ENDING_BACKLOG
1042 , 0 ENDING_REVENUE_AT_RISK
1043 , LOST_BACKLOG
1044 , DORMANT_BACKLOG_INACTIV
1048 , 0
1045 + ACTIVE_BACKLOG
1046 + DORMANT_BACKLOG_START BACKLOG
1047 , REVENUE_AT_RISK REVENUE_AT_RISK
1049 FROM
1050 PJI_PMV_TIME_DIM_TMP TIME
1051 ,PJI_PMV_ORGZ_DIM_TMP HORG
1052 , PJI_PMV_CLS_DIM_TMP CLS
1053 , PJI_AC_CLSO_F_MV FCT
1054 , PJI_PMV_ORG_DIM_TMP HOU
1055
1056 WHERE
1057 FCT.PROJECT_ORG_ID = HOU.ID
1058 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1059 AND FCT.TIME_ID = TIME.ID
1060 AND TIME.ID IS NOT NULL
1061 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1062 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1063 AND FCT.PROJECT_CLASS_ID = CLS.ID
1064 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1065 UNION ALL
1066 SELECT /*+ ORDERED */
1067 HOU.NAME ORG_ID
1068 , HORG.NAME ORGANIZATION_ID
1069 , TIME.NAME TIME_ID
1070 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
1071 , '-1' PROJECT_CLASS_ID
1072 , 0
1073 , 0 ORIGINAL_BOOKINGS
1074 , 0 ADDITIONAL_BOOKINGS
1075 , 0 BOOKINGS_ADJUSTMENTS
1076 , 0 CANCELLATIONS
1077 , 0 TOTAL_NET_BOOKINGS
1078 , 0 ACCRUED_REVENUE
1079 , DORMANT_BACKLOG_INACTIV
1080 + ACTIVE_BACKLOG
1081 + DORMANT_BACKLOG_START PRIOR_YEAR
1082 , 0 ENDING_LOST_BACKLOG
1083 , 0 ENDING_BACKLOG
1084 , 0 ENDING_REVENUE_AT_RISK
1085 , 0 LOST_BACKLOG
1086 , 0 BACKLOG
1087 , 0 REVENUE_AT_RISK
1088 , 0
1089 FROM
1090 PJI_PMV_TIME_DIM_TMP TIME
1091 , PJI_PMV_ORGZ_DIM_TMP HORG
1092 , PJI_PMV_CLS_DIM_TMP CLS
1093 , PJI_AC_CLSO_F_MV FCT
1094 , PJI_PMV_ORG_DIM_TMP HOU
1095 WHERE
1096 FCT.PROJECT_ORG_ID = HOU.ID
1097 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1098 AND FCT.TIME_ID = TIME.PRIOR_ID
1099 AND TIME.PRIOR_ID IS NOT NULL
1100 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1101 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1102 AND FCT.PROJECT_CLASS_ID = CLS.ID
1103 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1104 UNION ALL
1105 SELECT '-1' ORG_ID
1106 , '-1' ORGANIZATION_ID
1107 , NAME TIME_ID
1108 , ORDER_BY_ID TIME_KEY
1109 , '-1' PROJECT_CLASS_ID
1110 , 0
1111 , 0 ORIGINAL_BOOKINGS
1112 , 0 ADDITIONAL_BOOKINGS
1113 , 0 BOOKINGS_ADJUSTMENTS
1114 , 0 CANCELLATIONS
1115 , 0 TOTAL_NET_BOOKINGS
1116 , 0 ACCRUED_REVENUE
1117 , 0 PRIOR_YEAR
1118 , 0 ENDING_LOST_BACKLOG
1119 , 0 ENDING_BACKLOG
1120 , 0 ENDING_REVENUE_AT_RISK
1121 , 0 LOST_BACKLOG
1122 , 0 BACKLOG
1123 , 0 REVENUE_AT_RISK
1124 , 0
1125 FROM PJI_PMV_TIME_DIM_TMP
1126 WHERE NAME <> '-1'
1127 ) FACT
1128 GROUP BY
1129 ORG_ID
1130 , ORGANIZATION_ID
1131 , TIME_KEY
1132 , TIME_ID
1133 , PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
1134
1135 SELECT /*+ ORDERED */ SUM( DORMANT_BACKLOG_INACTIV
1136 + ACTIVE_BACKLOG
1137 + DORMANT_BACKLOG_START), SUM(LOST_BACKLOG), SUM(REVENUE_AT_RISK)
1138 INTO l_Ending_Backlog_itd,
1139 l_Ending_Lost_Backlog_itd,
1140 l_Ending_Revenue_at_Risk_itd
1141 FROM
1142 PJI_PMV_ITD_DIM_TMP TIME
1143 , PJI_PMV_ORGZ_DIM_TMP HORG
1144 , PJI_PMV_CLS_DIM_TMP CLS
1145 , PJI_AC_CLSO_F_MV FCT
1146 , PJI_PMV_ORG_DIM_TMP HOU
1147 WHERE
1148 FCT.PROJECT_ORG_ID = HOU.ID
1149 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1150 AND FCT.TIME_ID = TIME.ID
1151 AND TIME.ID IS NOT NULL
1152 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1153 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1154 AND FCT.PROJECT_CLASS_ID = CLS.ID
1155 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id;
1156 SELECT /*+ ORDERED */
1157 SUM( DORMANT_BACKLOG_INACTIV
1158 + ACTIVE_BACKLOG
1159 + DORMANT_BACKLOG_START)
1160 INTO l_Ending_Prior_Backlog_itd
1161 FROM
1162 PJI_PMV_ITD_DIM_TMP TIME
1163 , PJI_PMV_ORGZ_DIM_TMP HORG
1164 , PJI_PMV_CLS_DIM_TMP CLS
1165 , PJI_AC_CLSO_F_MV FCT
1166 , PJI_PMV_ORG_DIM_TMP HOU
1167
1168 WHERE
1169 FCT.PROJECT_ORG_ID = HOU.ID
1170 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1171 AND FCT.TIME_ID = TIME.PRIOR_ID
1172 AND TIME.PRIOR_ID IS NOT NULL
1173 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1174 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1175 AND FCT.PROJECT_CLASS_ID = CLS.ID
1176 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id;
1177 END IF;
1178
1179 FOR i IN l_Backlog_Trend_Tab.FIRST..l_Backlog_Trend_Tab.LAST
1180 LOOP
1181
1182 IF i=1 THEN l_Backlog_Trend_Tab(i).ENDING_BACKLOG := l_Backlog_Trend_Tab(i).BACKLOG + nvl(l_Ending_Backlog_itd,0);
1183 l_Backlog_Trend_Tab(i).PRIOR_YEAR := l_Backlog_Trend_Tab(i).PRIOR_YEAR + nvl(l_Ending_Prior_Backlog_itd,0);
1184 l_Backlog_Trend_Tab(i).ENDING_LOST_BACKLOG := l_Backlog_Trend_Tab(i).LOST_BACKLOG + nvl(l_Ending_Lost_Backlog_itd,0);
1185 l_Backlog_Trend_Tab(i).ENDING_REVENUE_AT_RISK := l_Backlog_Trend_Tab(i).REVENUE_AT_RISK + nvl(l_Ending_Revenue_at_Risk_itd,0);
1186
1187 ELSE
1188 l_Backlog_Trend_Tab(i).ENDING_BACKLOG :=
1189 l_Backlog_Trend_Tab(i-1).ENDING_BACKLOG
1190 + l_Backlog_Trend_Tab(i).BACKLOG;
1191 l_Backlog_Trend_Tab(i).PRIOR_YEAR :=
1192 l_Backlog_Trend_Tab(i-1).PRIOR_YEAR
1193 + l_Backlog_Trend_Tab(i).PRIOR_YEAR;
1194 l_Backlog_Trend_Tab(i).ENDING_LOST_BACKLOG :=
1195 l_Backlog_Trend_Tab(i-1).ENDING_LOST_BACKLOG
1196 + l_Backlog_Trend_Tab(1).LOST_BACKLOG;
1197 l_Backlog_Trend_Tab(i).ENDING_REVENUE_AT_RISK :=
1198 l_Backlog_Trend_Tab(i-1).ENDING_REVENUE_AT_RISK
1199 + l_Backlog_Trend_Tab(i).REVENUE_AT_RISK;
1200
1201 END IF;
1202 END LOOP;
1203
1204 FOR i in 1..l_Backlog_Trend_Tab.COUNT
1205 LOOP
1206 l_Backlog_Trend_Tab(i).BEGINNING_BACKLOG :=
1207 l_Backlog_Trend_Tab(i).ENDING_BACKLOG-
1208 l_Backlog_Trend_Tab(i).BACKLOG;
1209 IF
1210 l_Backlog_Trend_Tab(i).PRIOR_YEAR=0 THEN
1211 l_Backlog_Trend_Tab(i).CHANGE :=NULL;
1212 ELSE
1213 l_Backlog_Trend_Tab(i).CHANGE :=
1214 (l_Backlog_Trend_Tab(i).ENDING_BACKLOG - l_Backlog_Trend_Tab(i).PRIOR_YEAR)*100/
1215 ABS(l_Backlog_Trend_Tab(i).PRIOR_YEAR);
1216 END IF;
1217 END LOOP;
1218
1219 /*
1220 ** Return the bulk collected table back to pmv.
1221 */
1222 COMMIT;
1223 RETURN l_Backlog_Trend_Tab;
1224 END PLSQLDriver_PB2;
1225
1226 END;
1227