[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_BOOKINGS
Source
1 PACKAGE BODY PJI_PMV_BOOKINGS AS
2 /* $Header: PJIRF01B.pls 120.5 2005/10/11 18:32:03 appldev noship $ */
3
4 /*
5 ** ----------------------------------------------------------
6 ** Function: Get_SQL_PJI_REP_PBO1
7 ** This function returns sql statement generated by the base
8 ** engine api for the report PJI_REP_PBO1.
9 ** ----------------------------------------------------------
10 */
11 PROCEDURE Get_SQL_PJI_REP_PBO1(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
12 , x_PMV_Sql OUT NOCOPY VARCHAR2
13 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
14 IS
15 l_Err_Message VARCHAR2(3200);
16 BEGIN
17 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
18 P_SELECT_LIST => 'FACT.ORIGINAL_FUNDINGS_COUNT "PJI_REP_MSR_1",
19 FACT.ADDITIONAL_FUNDINGS_COUNT "PJI_REP_MSR_2",
20 FACT.TOTAL_BOOKINGS_COUNT "PJI_REP_MSR_7",
21 FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_3",
22 FACT.ADDITIONAL_FUNDINGS_AMOUNT "PJI_REP_MSR_4",
23 FACT.ADJUSTMENT_FUNDINGS_AMOUNT "PJI_REP_MSR_14",
24 FACT.CANCELLED_FUNDINGS_AMOUNT "PJI_REP_MSR_15",
25 FACT.TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_8",
26 FACT.PRIOR_TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_9",
27 FACT.TOTAL_FUND_CHANGE_PERCENT "PJI_REP_MSR_10",
28 FACT.PRIOR_ORIG_FUNDINGS_AMOUNT "PJI_REP_MSR_5",
29 FACT.PRIOR_ADTL_FUNDINGS_AMOUNT "PJI_REP_MSR_6",
30 FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_11",
31 FACT.ADDITIONAL_FUNDINGS_AMOUNT "PJI_REP_MSR_12",
32 FACT.TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_13",
33 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
34 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
35 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
36 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
37 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
38 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
39 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
40 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
41 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
42 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
43 , P_SQL_STATEMENT => x_PMV_Sql
44 , P_PMV_OUTPUT => x_PMV_Output
45 , P_REGION_CODE => 'PJI_REP_PBO1'
46 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS.PLSQLDriver_BOOKINGS'
47 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
48 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
49 ', <<CURRENCY+FII_CURRENCIES>>'||
50 ', <<AS_OF_DATE>>'||
51 ', <<PERIOD_TYPE>>'||
52 ', <<VIEW_BY>>'||
53 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
54 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
55 END Get_SQL_PJI_REP_PBO1;
56
57 /*
58 ** ----------------------------------------------------------
59 ** Procedure: Get_SQL_PJI_REP_PBO2
60 ** This procedure returns sql statement generated by the base
61 ** engine api and view by as a OUT NOCOPY parameter for the report
62 ** PJI_REP_PBO2.
63 ** ----------------------------------------------------------
64 */
65 PROCEDURE Get_SQL_PJI_REP_PBO2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
66 , x_PMV_Sql OUT NOCOPY VARCHAR2
67 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
68 IS
69 l_Err_Message VARCHAR2(3200);
70 BEGIN
71 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
72 P_SELECT_LIST =>'FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_1",
73 FACT.PRIOR_ORIG_FUNDINGS_AMOUNT "PJI_REP_MSR_2",
74 FACT.ORIG_FUND_CHANGE_PERCENT "PJI_REP_MSR_3",
75 FACT.TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_4",
76 FACT.PRIOR_TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_5",
77 FACT.TOTAL_FUND_CHANGE_PERCENT "PJI_REP_MSR_6",
78 FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_7",
79 FACT.TOTAL_FUNDINGS_AMOUNT "PJI_REP_MSR_8"'
80 , P_SQL_STATEMENT => x_PMV_Sql
81 , P_PMV_OUTPUT => x_PMV_Output
82 , P_REGION_CODE => 'PJI_REP_PBO2'
83 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS.PLSQLDriver_BOOKINGS'
84 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
85 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
86 ', <<CURRENCY+FII_CURRENCIES>>'||
87 ', <<AS_OF_DATE>>'||
88 ', <<PERIOD_TYPE>>'||
89 ', <<VIEW_BY>>'||
90 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
91 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
92 END Get_SQL_PJI_REP_PBO2;
93
94 /*
95 ** ----------------------------------------------------------
96 ** Procedure: Get_SQL_PJI_REP_PBO3
97 ** This procedure returns sql statement generated by the base
98 ** engine api and view by as a OUT NOCOPY parameter for the report
99 ** PJI_REP_PBO3.
100 ** ----------------------------------------------------------
101 */
102 PROCEDURE Get_SQL_PJI_REP_PBO3(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
103 , x_PMV_Sql OUT NOCOPY VARCHAR2
104 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
105 IS
106 l_Err_Message VARCHAR2(3200);
107 BEGIN
108 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
109 P_SELECT_LIST => 'FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_1",
110 FACT.ORIGINAL_FUNDINGS_COUNT "PJI_REP_MSR_2",
111 FACT.ORIGINAL_AVG_BOOKINGS "PJI_REP_MSR_3",
112 FACT.ADDITIONAL_FUNDINGS_AMOUNT "PJI_REP_MSR_4",
113 FACT.ADDITIONAL_FUNDINGS_COUNT "PJI_REP_MSR_5",
114 FACT.ADDITIONAL_AVG_BOOKINGS "PJI_REP_MSR_6",
115 FACT.ORIGINAL_FUNDINGS_AMOUNT "PJI_REP_MSR_7",
116 FACT.ADDITIONAL_FUNDINGS_AMOUNT "PJI_REP_MSR_8"'
117 , P_SQL_STATEMENT => x_PMV_Sql
118 , P_PMV_OUTPUT => x_PMV_Output
119 , P_REGION_CODE => 'PJI_REP_PBO3'
120 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS.PLSQLDriver_BOOKINGS'
121 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
122 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
123 ', <<CURRENCY+FII_CURRENCIES>>'||
124 ', <<AS_OF_DATE>>'||
125 ', <<PERIOD_TYPE>>'||
126 ', <<VIEW_BY>>'||
127 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
128 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
129 END Get_SQL_PJI_REP_PBO3;
130
131 /*
132 ** ----------------------------------------------------------
133 ** Function: PLSQLDriver_Bookings
134 ** This table function is called from select statement
135 ** generated by PJI engine. The function returns pl/sql table
136 ** of records which have to be displayed in the pmv report.
137 ** Following are the reports to which this function caters:
138 ** 1. PJI_REP_PBO1 - Project Bookings Summary
139 ** 2. PJI_REP_PBO2 - Project Bookings Trend
140 ** 3. PJI_REP_PBO3 - Project Bookings Source Summary
141 ** ----------------------------------------------------------
142 */
143 FUNCTION PLSQLDriver_Bookings(
144 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
145 , p_Organization IN VARCHAR2
146 , p_Currency_Type IN VARCHAR2
147 , p_As_Of_Date IN NUMBER
148 , p_Period_Type IN VARCHAR2
149 , p_View_BY IN VARCHAR2
150 , p_Classifications IN VARCHAR2 DEFAULT NULL
151 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
152 )RETURN PJI_AC_BOOKINGS_TBL
153 IS
154 PRAGMA AUTONOMOUS_TRANSACTION;
155 l_Total_AC_Bookings_Tab PJI_AC_BOOKINGS_TBL:=PJI_AC_BOOKINGS_TBL();
156 l_Top_Organization_Name VARCHAR2(240);
157 l_Top_Org_Index NUMBER:=0;
158
159 l_Orig_Fund_Count NUMBER:=0;
160 l_Addi_Fund_Count NUMBER:=0;
161 l_Orig_Fund_Amount NUMBER:=0;
162 l_Addi_Fund_Amount NUMBER:=0;
163 l_Adju_Fund_Amount NUMBER:=0;
164 l_Canc_Fund_Amount NUMBER:=0;
165 l_Total_Fund_Amount NUMBER:=0;
166 l_Pr_Orig_Fund_Amount NUMBER:=0;
167 l_Pr_Addi_Fund_Amount NUMBER:=0;
168 l_Pr_Adju_Fund_Amount NUMBER:=0;
169 l_Pr_Canc_Fund_Amount NUMBER:=0;
170 l_Pr_Total_Fund_Amount NUMBER:=0;
171
172 l_TO_Orig_Fund_Count NUMBER:=0;
173 l_TO_Addi_Fund_Count NUMBER:=0;
174 l_TO_Orig_Fund_Amount NUMBER:=0;
175 l_TO_Addi_Fund_Amount NUMBER:=0;
176 l_TO_Adju_Fund_Amount NUMBER:=0;
177 l_TO_Canc_Fund_Amount NUMBER:=0;
178 l_TO_Total_Fund_Amount NUMBER:=0;
179 l_TO_Pr_Total_Fund_Amount NUMBER:=0;
180 l_curr_record_type_id NUMBER:= 1;
181
182 BEGIN
183
184 /*
185 ** Place a call to all the parse API's which parse the
186 ** parameters passed by PMV and populate all the
187 ** temporary tables.
188 */
189 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit_IDS=>p_Operating_Unit, p_View_BY=>p_View_BY);
190 PJI_PMV_ENGINE.Convert_Organization(p_Top_Organization_ID=>p_Organization,
191 p_View_BY=>p_View_BY,
192 p_Top_Organization_Name => l_Top_Organization_Name);
193 PJI_PMV_ENGINE.Convert_Time(p_As_Of_Date=>p_As_Of_Date, p_Period_Type=>p_Period_Type, p_View_BY=>p_View_By, p_Parse_Prior=>'Y', p_Report_Type=>NULL, p_Comparator=>NULL, p_Parse_ITD=>NULL);
194
195 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
196
197 /*
198 ** Determine the fact tables you choose to run the database
199 ** query on ( this step is what we call manual query re-write).
200 */
201
202 IF PJI_PMV_ENGINE.Convert_Classification(p_Classification_ID=>p_Classifications, p_Class_Code_IDS=>p_Class_Codes, p_View_BY=>p_View_BY) = 'N' THEN
203
204 /*
205 ** Code the SQL statement for all of the following conditions
206 ** 1. Current Year
207 ** 2. Prior Year
208 ** 3. SQL to generate rows with zero's for the view by dimension
209 ** Bulk-Collect the output into a pl/sql table to be returned to
210 ** pmv.
211 */
212
213 SELECT PJI_AC_BOOKINGS(
214 ORG_ID
215 , ORGANIZATION_ID
216 , TIME_ID
217 , TIME_KEY
218 , PROJECT_CLASS_ID
219 , SUM( ORIGINAL_FUNDINGS_COUNT )
220 , SUM( ADDITIONAL_FUNDINGS_COUNT )
221 , SUM( TOTAL_FUNDING_COUNT )
222 , SUM( ORIGINAL_FUNDINGS_AMOUNT )
223 , SUM( ADDITIONAL_FUNDINGS_AMOUNT )
224 , SUM( ADJUSTMENT_FUNDINGS_AMOUNT )
225 , -SUM( CANCELLED_FUNDINGS_AMOUNT )
226 , SUM( ORIGINAL_FUNDINGS_AMOUNT
227 + ADDITIONAL_FUNDINGS_AMOUNT
228 + ADJUSTMENT_FUNDINGS_AMOUNT
229 + CANCELLED_FUNDINGS_AMOUNT )
230 , SUM( PRIOR_ORIG_FUNDINGS_AMOUNT )
231 , SUM( PRIOR_ADTL_FUNDINGS_AMOUNT )
232 , SUM( PRIOR_ADJ_FUNDINGS_AMOUNT )
233 , -SUM( PRIOR_CAN_FUNDINGS_AMOUNT )
234 , SUM( PRIOR_ORIG_FUNDINGS_AMOUNT
235 + PRIOR_ADTL_FUNDINGS_AMOUNT
236 + PRIOR_ADJ_FUNDINGS_AMOUNT
237 + PRIOR_CAN_FUNDINGS_AMOUNT )
238 , 0
239 , 0
240 , 0
241 , 0
242 , 0
243 , 0
244 , 0
245 , 0
246 , 0
247 , 0
248 , 0
249 , 0
250 , 0
251 , 0 )
252 BULK COLLECT INTO l_Total_AC_Bookings_Tab
253 FROM
254 ( SELECT /*+ ORDERED */
255 HOU.NAME ORG_ID
256 , HORG.NAME ORGANIZATION_ID
257 , TIME.NAME TIME_ID
258 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
259 , '-1' PROJECT_CLASS_ID
260 , INITIAL_FUNDING_COUNT ORIGINAL_FUNDINGS_COUNT
261 , ADDITIONAL_FUNDING_COUNT ADDITIONAL_FUNDINGS_COUNT
262 , INITIAL_FUNDING_COUNT
263 +ADDITIONAL_FUNDING_COUNT TOTAL_FUNDING_COUNT
264 , INITIAL_FUNDING_AMOUNT ORIGINAL_FUNDINGS_AMOUNT
265 , ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_FUNDINGS_AMOUNT
266 , FUNDING_ADJUSTMENT_AMOUNT ADJUSTMENT_FUNDINGS_AMOUNT
267 , CANCELLED_FUNDING_AMOUNT CANCELLED_FUNDINGS_AMOUNT
268 , 0 PRIOR_ORIG_FUNDINGS_AMOUNT
269 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
270 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
271 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
272 , 0
273 , 0
274 , 0
275 , 0
276 , 0
277 , 0
278 , 0
279 , 0
280 , 0
281 , 0
282 , 0
283 , 0
284 , 0
285 , 0
286 FROM
287 PJI_PMV_TIME_DIM_TMP TIME
288 , PJI_PMV_ORGZ_DIM_TMP HORG
289 , PJI_AC_ORGO_F_MV FCT
290 , PJI_PMV_ORG_DIM_TMP HOU
291 WHERE
292 FCT.ORG_ID = HOU.ID
293 AND FCT.ORGANIZATION_ID = HORG.ID
294 AND FCT.TIME_ID = TIME.ID
295 AND TIME.ID IS NOT NULL
296 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
297 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
298 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
299 UNION ALL
300 SELECT /*+ ORDERED */
301 HOU.NAME ORG_ID
302 , HORG.NAME ORGANIZATION_ID
303 , TIME.NAME TIME_ID
304 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
305 , '-1' PROJECT_CLASS_ID
306 , 0 ORIGINAL_FUNDINGS_COUNT
307 , 0 ADDITIONAL_FUNDINGS_COUNT
308 , 0 TOTAL_FUNDING_COUNT
309 , 0 ORIGINAL_FUNDINGS_AMOUNT
310 , 0 ADDITIONAL_FUNDINGS_AMOUNT
311 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
312 , 0 CANCELLED_FUNDINGS_AMOUNT
313 , INITIAL_FUNDING_AMOUNT PRIOR_ORIG_FUNDINGS_AMOUNT
314 , ADDITIONAL_FUNDING_AMOUNT PRIOR_ADTL_FUNDINGS_AMOUNT
315 , FUNDING_ADJUSTMENT_AMOUNT PRIOR_ADJ_FUNDINGS_AMOUNT
316 , CANCELLED_FUNDING_AMOUNT PRIOR_CAN_FUNDINGS_AMOUNT
317 , 0
318 , 0
319 , 0
320 , 0
321 , 0
322 , 0
323 , 0
324 , 0
325 , 0
326 , 0
327 , 0
328 , 0
329 , 0
330 , 0
331 FROM
332 PJI_PMV_TIME_DIM_TMP TIME
333 , PJI_PMV_ORGZ_DIM_TMP HORG
334 , PJI_AC_ORGO_F_MV FCT
335 , PJI_PMV_ORG_DIM_TMP HOU
336 WHERE
337 FCT.ORG_ID = HOU.ID
338 AND FCT.ORGANIZATION_ID = HORG.ID
339 AND FCT.TIME_ID = TIME.PRIOR_ID
340 AND TIME.PRIOR_ID IS NOT NULL
341 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
342 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
343 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
344 UNION ALL
345 SELECT NAME ORG_ID
346 , '-1' ORGANIZATION_ID
347 , '-1' TIME_ID
348 , -1 TIME_KEY
349 , '-1' PROJECT_CLASS_ID
350 , 0 ORIGINAL_FUNDINGS_COUNT
351 , 0 ADDITIONAL_FUNDINGS_COUNT
352 , 0 TOTAL_FUNDING_COUNT
353 , 0 ORIGINAL_FUNDINGS_AMOUNT
354 , 0 ADDITIONAL_FUNDINGS_AMOUNT
355 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
356 , 0 CANCELLED_FUNDINGS_AMOUNT
357 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
358 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
359 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
360 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
361 , 0
362 , 0
363 , 0
364 , 0
365 , 0
366 , 0
367 , 0
368 , 0
369 , 0
373 , 0
370 , 0
371 , 0
372 , 0
374 , 0
375 FROM PJI_PMV_ORG_DIM_TMP
376 WHERE NAME <> '-1'
377 UNION ALL
378 SELECT '-1' ORG_ID
379 , NAME ORGANIZATION_ID
380 , '-1' TIME_ID
381 , -1 TIME_KEY
382 , '-1' PROJECT_CLASS_ID
383 , 0 ORIGINAL_FUNDINGS_COUNT
384 , 0 ADDITIONAL_FUNDINGS_COUNT
385 , 0 TOTAL_FUNDING_COUNT
386 , 0 ORIGINAL_FUNDINGS_AMOUNT
387 , 0 ADDITIONAL_FUNDINGS_AMOUNT
388 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
389 , 0 CANCELLED_FUNDINGS_AMOUNT
390 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
391 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
392 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
393 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
394 , 0
395 , 0
396 , 0
397 , 0
398 , 0
399 , 0
400 , 0
401 , 0
402 , 0
403 , 0
404 , 0
405 , 0
406 , 0
407 , 0
408 FROM PJI_PMV_ORGZ_DIM_TMP
409 WHERE NAME <> '-1'
410 UNION ALL
411 SELECT '-1' ORG_ID
412 , '-1' ORGANIZATION_ID
413 , NAME TIME_ID
414 , ORDER_BY_ID TIME_KEY
415 , '-1' PROJECT_CLASS_ID
416 , 0 ORIGINAL_FUNDINGS_COUNT
417 , 0 ADDITIONAL_FUNDINGS_COUNT
418 , 0 TOTAL_FUNDING_COUNT
419 , 0 ORIGINAL_FUNDINGS_AMOUNT
420 , 0 ADDITIONAL_FUNDINGS_AMOUNT
421 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
422 , 0 CANCELLED_FUNDINGS_AMOUNT
423 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
424 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
425 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
426 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
427 , 0
428 , 0
429 , 0
430 , 0
431 , 0
432 , 0
433 , 0
434 , 0
435 , 0
436 , 0
437 , 0
438 , 0
439 , 0
440 , 0
441 FROM PJI_PMV_TIME_DIM_TMP
442 WHERE NAME <> '-1')
443 GROUP BY
444 ORG_ID
445 , ORGANIZATION_ID
446 , TIME_KEY
447 , TIME_ID
448 , PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
449 ELSE
450 /*
451 ** Code the SQL statement for all of the following conditions
452 ** 1. Current Year
453 ** 2. Prior Year
454 ** 3. SQL to generate rows with zero's for the view by dimension
455 */
456
457 SELECT PJI_AC_BOOKINGS( ORG_ID
458 , ORGANIZATION_ID
459 , TIME_ID
460 , TIME_KEY
461 , PROJECT_CLASS_ID
462 , SUM( ORIGINAL_FUNDINGS_COUNT )
463 , SUM( ADDITIONAL_FUNDINGS_COUNT )
464 , SUM( TOTAL_FUNDING_COUNT )
465 , SUM( ORIGINAL_FUNDINGS_AMOUNT )
466 , SUM( ADDITIONAL_FUNDINGS_AMOUNT )
467 , SUM( ADJUSTMENT_FUNDINGS_AMOUNT )
468 , -SUM( CANCELLED_FUNDINGS_AMOUNT )
469 , SUM( ORIGINAL_FUNDINGS_AMOUNT
470 + ADDITIONAL_FUNDINGS_AMOUNT
471 + ADJUSTMENT_FUNDINGS_AMOUNT
472 + CANCELLED_FUNDINGS_AMOUNT )
473 , SUM( PRIOR_ORIG_FUNDINGS_AMOUNT )
474 , SUM( PRIOR_ADTL_FUNDINGS_AMOUNT )
475 , SUM( PRIOR_ADJ_FUNDINGS_AMOUNT )
476 , -SUM( PRIOR_CAN_FUNDINGS_AMOUNT )
477 , SUM( PRIOR_ORIG_FUNDINGS_AMOUNT
478 + PRIOR_ADTL_FUNDINGS_AMOUNT
479 + PRIOR_ADJ_FUNDINGS_AMOUNT
480 + PRIOR_CAN_FUNDINGS_AMOUNT )
481 , 0
482 , 0
483 , 0
484 , 0
485 , 0
486 , 0
487 , 0
488 , 0
489 , 0
490 , 0
491 , 0
492 , 0
493 , 0
494 , 0 )
495 BULK COLLECT INTO l_Total_AC_Bookings_Tab
496 FROM
497 ( SELECT /*+ ORDERED */
498 HOU.NAME ORG_ID
499 , HORG.NAME ORGANIZATION_ID
500 , TIME.NAME TIME_ID
501 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
502 , CLS.NAME PROJECT_CLASS_ID
503 , INITIAL_FUNDING_COUNT ORIGINAL_FUNDINGS_COUNT
504 , ADDITIONAL_FUNDING_COUNT ADDITIONAL_FUNDINGS_COUNT
505 , INITIAL_FUNDING_COUNT
506 + ADDITIONAL_FUNDING_COUNT TOTAL_FUNDING_COUNT
507 , INITIAL_FUNDING_AMOUNT ORIGINAL_FUNDINGS_AMOUNT
508 , ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_FUNDINGS_AMOUNT
509 , FUNDING_ADJUSTMENT_AMOUNT ADJUSTMENT_FUNDINGS_AMOUNT
510 , CANCELLED_FUNDING_AMOUNT CANCELLED_FUNDINGS_AMOUNT
511 , 0 PRIOR_ORIG_FUNDINGS_AMOUNT
512 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
513 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
514 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
515 , 0
516 , 0
517 , 0
518 , 0
519 , 0
520 , 0
521 , 0
522 , 0
523 , 0
524 , 0
528 , 0
525 , 0
526 , 0
527 , 0
529 FROM
530 PJI_PMV_TIME_DIM_TMP TIME
531 , PJI_PMV_ORGZ_DIM_TMP HORG
532 , PJI_PMV_CLS_DIM_TMP CLS
533 , PJI_AC_CLSO_F_MV FCT
534 , PJI_PMV_ORG_DIM_TMP HOU
535 WHERE
536 FCT.PROJECT_ORG_ID = HOU.ID
537 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
538 AND FCT.TIME_ID = TIME.ID
539 AND TIME.ID IS NOT NULL
540 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
541 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
542 AND FCT.PROJECT_CLASS_ID = CLS.ID
543 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
544 UNION ALL
545 SELECT /*+ ORDERED */
546 HOU.NAME ORG_ID
547 , HORG.NAME ORGANIZATION_ID
548 , TIME.NAME TIME_ID
549 , DECODE(p_View_BY,'TM',TIME.ORDER_BY_ID,-1) TIME_KEY
550 , CLS.NAME PROJECT_CLASS_ID
551 , 0 ORIGINAL_FUNDINGS_COUNT
552 , 0 ADDITIONAL_FUNDINGS_COUNT
553 , 0 TOTAL_FUNDING_COUNT
554 , 0 ORIGINAL_FUNDINGS_AMOUNT
555 , 0 ADDITIONAL_FUNDINGS_AMOUNT
556 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
557 , 0 CANCELLED_FUNDINGS_AMOUNT
558 , INITIAL_FUNDING_AMOUNT PRIOR_ORIG_FUNDINGS_AMOUNT
559 , ADDITIONAL_FUNDING_AMOUNT PRIOR_ADTL_FUNDINGS_AMOUNT
560 , FUNDING_ADJUSTMENT_AMOUNT PRIOR_ADJ_FUNDINGS_AMOUNT
561 , CANCELLED_FUNDING_AMOUNT PRIOR_CAN_FUNDINGS_AMOUNT
562 , 0
563 , 0
564 , 0
565 , 0
566 , 0
567 , 0
568 , 0
569 , 0
570 , 0
571 , 0
572 , 0
573 , 0
574 , 0
575 , 0
576 FROM
577 PJI_PMV_TIME_DIM_TMP TIME
578 , PJI_PMV_ORGZ_DIM_TMP HORG
579 , PJI_PMV_CLS_DIM_TMP CLS
580 , PJI_AC_CLSO_F_MV FCT
581 , PJI_PMV_ORG_DIM_TMP HOU
582 WHERE
583 FCT.PROJECT_ORG_ID = HOU.ID
584 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
585 AND FCT.TIME_ID = TIME.PRIOR_ID
586 AND TIME.PRIOR_ID IS NOT NULL
587 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
588 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
589 AND FCT.PROJECT_CLASS_ID = CLS.ID
590 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
591 UNION ALL
592 SELECT NAME ORG_ID
593 , '-1' ORGANIZATION_ID
594 , '-1' TIME_ID
595 , -1 TIME_KEY
596 , '-1' PROJECT_CLASS_ID
597 , 0 ORIGINAL_FUNDINGS_COUNT
598 , 0 ADDITIONAL_FUNDINGS_COUNT
599 , 0 TOTAL_FUNDING_COUNT
600 , 0 ORIGINAL_FUNDINGS_AMOUNT
601 , 0 ADDITIONAL_FUNDINGS_AMOUNT
602 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
603 , 0 CANCELLED_FUNDINGS_AMOUNT
604 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
605 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
606 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
607 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
608 , 0
609 , 0
610 , 0
611 , 0
612 , 0
613 , 0
614 , 0
615 , 0
616 , 0
617 , 0
618 , 0
619 , 0
620 , 0
621 , 0
622 FROM PJI_PMV_ORG_DIM_TMP
623 WHERE NAME <> '-1'
624 UNION ALL
625 SELECT '-1' ORG_ID
626 , NAME ORGANIZATION_ID
627 , '-1' TIME_ID
628 , -1 TIME_KEY
629 , '-1' PROJECT_CLASS_ID
630 , 0 ORIGINAL_FUNDINGS_COUNT
631 , 0 ADDITIONAL_FUNDINGS_COUNT
632 , 0 TOTAL_FUNDING_COUNT
633 , 0 ORIGINAL_FUNDINGS_AMOUNT
634 , 0 ADDITIONAL_FUNDINGS_AMOUNT
635 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
636 , 0 CANCELLED_FUNDINGS_AMOUNT
637 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
638 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
639 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
640 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
641 , 0
642 , 0
643 , 0
644 , 0
645 , 0
646 , 0
647 , 0
648 , 0
649 , 0
650 , 0
651 , 0
652 , 0
653 , 0
654 , 0
655 FROM PJI_PMV_ORGZ_DIM_TMP
656 WHERE NAME <> '-1'
657 UNION ALL
658 SELECT '-1' ORG_ID
659 , '-1' ORGANIZATION_ID
660 , NAME TIME_ID
661 , ORDER_BY_ID TIME_KEY
662 , '-1' PROJECT_CLASS_ID
663 , 0 ORIGINAL_FUNDINGS_COUNT
664 , 0 ADDITIONAL_FUNDINGS_COUNT
665 , 0 TOTAL_FUNDING_COUNT
666 , 0 ORIGINAL_FUNDINGS_AMOUNT
667 , 0 ADDITIONAL_FUNDINGS_AMOUNT
668 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
669 , 0 CANCELLED_FUNDINGS_AMOUNT
670 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
671 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
672 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
676 , 0
673 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
674 , 0
675 , 0
677 , 0
678 , 0
679 , 0
680 , 0
681 , 0
682 , 0
683 , 0
684 , 0
685 , 0
686 , 0
687 , 0
688 FROM PJI_PMV_TIME_DIM_TMP
689 WHERE NAME <> '-1'
690 UNION ALL
691 SELECT '-1' ORG_ID
692 , '-1' ORGANIZATION_ID
693 , '-1' TIME_ID
694 , -1 TIME_KEY
695 , NAME PROJECT_CLASS_ID
696 , 0 ORIGINAL_FUNDINGS_COUNT
697 , 0 ADDITIONAL_FUNDINGS_COUNT
698 , 0 TOTAL_FUNDING_COUNT
699 , 0 ORIGINAL_FUNDINGS_AMOUNT
700 , 0 ADDITIONAL_FUNDINGS_AMOUNT
701 , 0 ADJUSTMENT_FUNDINGS_AMOUNT
702 , 0 CANCELLED_FUNDINGS_AMOUNT
703 , 0 PRIOR_ORGI_FUNDINGS_AMOUNT
704 , 0 PRIOR_ADTL_FUNDINGS_AMOUNT
705 , 0 PRIOR_ADJ_FUNDINGS_AMOUNT
706 , 0 PRIOR_CAN_FUNDINGS_AMOUNT
707 , 0
708 , 0
709 , 0
710 , 0
711 , 0
712 , 0
713 , 0
714 , 0
715 , 0
716 , 0
717 , 0
718 , 0
719 , 0
720 , 0
721 FROM PJI_PMV_CLS_DIM_TMP
722 WHERE NAME <> '-1') FACT
723 GROUP BY
724 ORG_ID
725 , ORGANIZATION_ID
726 , TIME_KEY
727 , TIME_ID
728 , PROJECT_CLASS_ID ORDER BY TIME_KEY ASC;
729 END IF;
730
731 FOR i in 1..l_Total_AC_Bookings_Tab.COUNT
732 LOOP
733 IF p_View_By = 'OG' THEN
734 IF l_Total_AC_Bookings_Tab(i).ORGANIZATION_ID = l_Top_Organization_Name THEN
735 l_Top_Org_Index:=i;
736
737 l_TO_Orig_Fund_Count := NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_COUNT,0);
738 l_TO_Addi_Fund_Count := NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_COUNT,0);
739 l_TO_Orig_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_AMOUNT,0);
740 l_TO_Addi_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_AMOUNT,0);
741 l_TO_Adju_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).ADJUSTMENT_FUNDINGS_AMOUNT,0);
742 l_TO_Canc_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).CANCELLED_FUNDINGS_AMOUNT,0);
743 l_TO_Total_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).TOTAL_FUNDINGS_AMOUNT,0);
744 l_TO_Pr_Total_Fund_Amount := NVL(l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT,0);
745
746 ELSE
747 l_Orig_Fund_Count := l_Orig_Fund_Count
748 + NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_COUNT,0);
749 l_Addi_Fund_Count :=l_Addi_Fund_Count
750 + NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_COUNT,0);
751 l_Orig_Fund_Amount :=l_Orig_Fund_Amount
752 + NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_AMOUNT,0);
753 l_Addi_Fund_Amount :=l_Addi_Fund_Amount
754 + NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_AMOUNT,0);
755 l_Adju_Fund_Amount :=l_Adju_Fund_Amount
756 + NVL(l_Total_AC_Bookings_Tab(i).ADJUSTMENT_FUNDINGS_AMOUNT,0);
757 l_Canc_Fund_Amount :=l_Canc_Fund_Amount
758 + NVL(l_Total_AC_Bookings_Tab(i).CANCELLED_FUNDINGS_AMOUNT,0);
759 l_Total_Fund_Amount :=l_Total_Fund_Amount
760 + NVL(l_Total_AC_Bookings_Tab(i).TOTAL_FUNDINGS_AMOUNT,0);
761 l_Pr_Orig_Fund_Amount :=l_Pr_Orig_Fund_Amount
762 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ORIG_FUNDINGS_AMOUNT,0);
763 l_Pr_Addi_Fund_Amount :=l_Pr_Addi_Fund_Amount
764 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ADTL_FUNDINGS_AMOUNT,0);
765 l_Pr_Adju_Fund_Amount :=l_Pr_Adju_Fund_Amount
766 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ADJ_FUNDINGS_AMOUNT,0);
767 l_Pr_Canc_Fund_Amount :=l_Pr_Canc_Fund_Amount
768 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_CAN_FUNDINGS_AMOUNT,0);
769 l_Pr_Total_Fund_Amount:=l_Pr_Total_Fund_Amount
770 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT,0);
771 END IF;
772 ELSE
773
774 l_Orig_Fund_Count := l_Orig_Fund_Count
775 + NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_COUNT,0);
776 l_Addi_Fund_Count :=l_Addi_Fund_Count
777 + NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_COUNT,0);
778 l_Orig_Fund_Amount :=l_Orig_Fund_Amount
779 + NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_AMOUNT,0);
780 l_Addi_Fund_Amount :=l_Addi_Fund_Amount
781 + NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_AMOUNT,0);
782 l_Adju_Fund_Amount :=l_Adju_Fund_Amount
783 + NVL(l_Total_AC_Bookings_Tab(i).ADJUSTMENT_FUNDINGS_AMOUNT,0);
784 l_Canc_Fund_Amount :=l_Canc_Fund_Amount
785 + NVL(l_Total_AC_Bookings_Tab(i).CANCELLED_FUNDINGS_AMOUNT,0);
786 l_Total_Fund_Amount :=l_Total_Fund_Amount
787 + NVL(l_Total_AC_Bookings_Tab(i).TOTAL_FUNDINGS_AMOUNT,0);
788 l_Pr_Orig_Fund_Amount :=l_Pr_Orig_Fund_Amount
789 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ORIG_FUNDINGS_AMOUNT,0);
790 l_Pr_Addi_Fund_Amount :=l_Pr_Addi_Fund_Amount
791 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ADTL_FUNDINGS_AMOUNT,0);
795 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_CAN_FUNDINGS_AMOUNT,0);
792 l_Pr_Adju_Fund_Amount :=l_Pr_Adju_Fund_Amount
793 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ADJ_FUNDINGS_AMOUNT,0);
794 l_Pr_Canc_Fund_Amount :=l_Pr_Canc_Fund_Amount
796 l_Pr_Total_Fund_Amount:=l_Pr_Total_Fund_Amount
797 + NVL(l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT,0);
798 END IF;
799
800 END LOOP;
801
802 IF p_View_By = 'OG' THEN
803
804 l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_COUNT
805 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_COUNT,0)
806 -l_Orig_Fund_Count;
807
808 l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_COUNT
809 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_COUNT,0)
810 -l_Addi_Fund_Count;
811
812 l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_AMOUNT
813 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_AMOUNT,0)
814 -l_Orig_Fund_Amount;
815
816 l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_AMOUNT
817 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_AMOUNT,0)
818 -l_Addi_Fund_Amount;
819
820 l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADJUSTMENT_FUNDINGS_AMOUNT
821 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADJUSTMENT_FUNDINGS_AMOUNT,0)
822 -l_Adju_Fund_Amount;
823
824 l_Total_AC_Bookings_Tab(l_Top_Org_Index).CANCELLED_FUNDINGS_AMOUNT
825 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).CANCELLED_FUNDINGS_AMOUNT,0)
826 -l_Canc_Fund_Amount;
827
828 l_Total_AC_Bookings_Tab(l_Top_Org_Index).TOTAL_FUNDINGS_AMOUNT
829 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).TOTAL_FUNDINGS_AMOUNT,0)
830 -l_Total_Fund_Amount;
831
832 l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ORIG_FUNDINGS_AMOUNT
833 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ORIG_FUNDINGS_AMOUNT,0)
834 -l_Pr_Orig_Fund_Amount;
835
836 l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ADTL_FUNDINGS_AMOUNT
837 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ADTL_FUNDINGS_AMOUNT,0)
838 -l_Pr_Addi_Fund_Amount;
839
840 l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ADJ_FUNDINGS_AMOUNT
841 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_ADJ_FUNDINGS_AMOUNT,0)
842 -l_Pr_Adju_Fund_Amount;
843
844 l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_CAN_FUNDINGS_AMOUNT
845 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_CAN_FUNDINGS_AMOUNT,0)
846 -l_Pr_Canc_Fund_Amount;
847
848 l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_TOTAL_FUNDINGS_AMOUNT
849 := NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_TOTAL_FUNDINGS_AMOUNT,0)
850 -l_Pr_Total_Fund_Amount;
851
852 IF
853 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_COUNT, 0) =0 AND
854 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_COUNT, 0) =0 AND
855 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ORIGINAL_FUNDINGS_AMOUNT, 0) =0 AND
856 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADDITIONAL_FUNDINGS_AMOUNT, 0) =0 AND
857 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).ADJUSTMENT_FUNDINGS_AMOUNT, 0) =0 AND
858 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).CANCELLED_FUNDINGS_AMOUNT, 0) =0 AND
859 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).TOTAL_FUNDINGS_AMOUNT, 0) =0 AND
860 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).PRIOR_TOTAL_FUNDINGS_AMOUNT,0) =0 AND
861 NVL(l_Total_AC_Bookings_Tab(l_Top_Org_Index).TOTAL_FUND_CHANGE_PERCENT, 0) =0
862 THEN
863 l_Total_AC_Bookings_Tab.DELETE(l_Top_Org_Index);
864 END IF;
865
866 l_Orig_Fund_Count :=l_TO_Orig_Fund_Count;
867 l_Addi_Fund_Count :=l_TO_Addi_Fund_Count;
868 l_Orig_Fund_Amount :=l_TO_Orig_Fund_Amount;
869 l_Addi_Fund_Amount :=l_TO_Addi_Fund_Amount;
870 l_Adju_Fund_Amount :=l_TO_Adju_Fund_Amount;
871 l_Canc_Fund_Amount :=l_TO_Canc_Fund_Amount;
872 l_Total_Fund_Amount :=l_TO_Total_Fund_Amount;
873 l_Pr_Total_Fund_Amount :=l_TO_Pr_Total_Fund_Amount;
874
875 END IF;
876
877 /*
878 ** Calculations moved from AK to PLSQL code
879 */
880
881 IF l_Total_AC_Bookings_Tab.COUNT > 0 THEN
882 FOR i in l_Total_AC_Bookings_Tab.FIRST..l_Total_AC_Bookings_Tab.LAST
883 LOOP
884 /* PBO1*/
885 IF l_Total_AC_Bookings_Tab.EXISTS(i) THEN
886 IF NVL(l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT, 0)=0 THEN
887 l_Total_AC_Bookings_Tab(i).TOTAL_FUND_CHANGE_PERCENT:= NULL;
888 ELSE
889 l_Total_AC_Bookings_Tab(i).TOTAL_FUND_CHANGE_PERCENT:=
890 (l_Total_AC_Bookings_Tab(i).TOTAL_FUNDINGS_AMOUNT
891 -l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT)*100
892 /ABS(l_Total_AC_Bookings_Tab(i).PRIOR_TOTAL_FUNDINGS_AMOUNT);
893 END IF;
894 /* PBO2*/
895 IF NVL(l_Total_AC_Bookings_Tab(i).PRIOR_ORIG_FUNDINGS_AMOUNT, 0)=0 THEN
896 l_Total_AC_Bookings_Tab(i).ORIG_FUND_CHANGE_PERCENT :=NULL;
897 ELSE
898 l_Total_AC_Bookings_Tab(i).ORIG_FUND_CHANGE_PERCENT:=
899 (l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_AMOUNT-
900 l_Total_AC_Bookings_Tab(i).PRIOR_ORIG_FUNDINGS_AMOUNT)*100
901 /ABS(l_Total_AC_Bookings_Tab(i).PRIOR_ORIG_FUNDINGS_AMOUNT);
902 END IF;
903 /*PBO3*/
904 IF NVL(l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_COUNT, 0)=0 THEN
905 l_Total_AC_Bookings_Tab(i).ORIGINAL_AVG_BOOKINGS :=NULL;
906 ELSE l_Total_AC_Bookings_Tab(i).ORIGINAL_AVG_BOOKINGS:=
907 l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_AMOUNT/
908 l_Total_AC_Bookings_Tab(i).ORIGINAL_FUNDINGS_COUNT;
909 END IF;
910
911 IF NVL(l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_COUNT, 0)=0 THEN
912 l_Total_AC_Bookings_Tab(i).ADDITIONAL_AVG_BOOKINGS :=NULL;
913 ELSE l_Total_AC_Bookings_Tab(i).ADDITIONAL_AVG_BOOKINGS:=
914 l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_AMOUNT/
915 l_Total_AC_Bookings_Tab(i).ADDITIONAL_FUNDINGS_COUNT;
916 END IF;
917 END IF;
918 END LOOP;
919 END IF;
920
921 /*
922 ** Totals
923 */
924
925 IF l_Total_AC_Bookings_Tab.COUNT > 0 THEN
926 FOR i in l_Total_AC_Bookings_Tab.FIRST..l_Total_AC_Bookings_Tab.LAST
927 LOOP
928 IF l_Total_AC_Bookings_Tab.EXISTS(i) THEN
929 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_1 := l_Orig_Fund_Count;
930 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_2 := l_Addi_Fund_Count;
931 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_3 := l_Orig_Fund_Count
932 + l_Addi_Fund_Count;
933 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_4 := l_Orig_Fund_Amount;
934 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_5 := l_Addi_Fund_Amount;
935 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_6 := l_Adju_Fund_Amount;
936 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_7 := l_Canc_Fund_Amount;
937 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_8 := l_Total_Fund_Amount;
938 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_9 := l_Pr_Total_Fund_Amount;
939
940 IF NVL(l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_9, 0)=0 THEN
941 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_10:= NULL;
942 ELSE
943 l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_10:=
944 (l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_8
945 -l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_9)*100
946 /ABS(l_Total_AC_Bookings_Tab(i).PJI_REP_TOTAL_9);
947 END IF;
948 END IF;
949 END LOOP;
950 END IF;
951
952 /*
953 ** Return the bulk collected table back to pmv.
954 */
955
956 COMMIT;
957 RETURN l_Total_AC_Bookings_Tab;
958 END PLSQLDriver_BOOKINGS;
959 END PJI_PMV_BOOKINGS;
960