[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_BOOKINGS_BACKLOG
Source
1 PACKAGE BODY PJI_PMV_BOOKINGS_BACKLOG AS
2 /* $Header: PJIRF02B.pls 120.6 2005/10/31 17:00:02 appldev noship $ */
3
4 /*
5 ** ----------------------------------------------------------
6 ** Procedure: Get_SQL_PJI_REP_PBB1
7 ** This procedure returns sql statement generated by the base
8 ** engine api for the report PJI_REP_PBB1.
9 ** ----------------------------------------------------------
10 */
11 PROCEDURE Get_SQL_PJI_REP_PBB1(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 l_PMV_Sql VARCHAR2(3200);
17 BEGIN
18 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
19 P_SELECT_LIST =>
20 'FACT.TOTAL_BOOKINGS "PJI_REP_MSR_1",
21 FACT.BOOKINGS_CHANGE "PJI_REP_MSR_2",
22 FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_3",
23 FACT.BACKLOG_CHANGE "PJI_REP_MSR_4",
24 FACT.BTB_RATIO "PJI_REP_MSR_5",
25 FACT.BTB_CHANGE "PJI_REP_MSR_6",
26 FACT.PRIOR_TOTAL_BOOKINGS "PJI_REP_MSR_7",
27 FACT.PRIOR_TOTAL_ENDING_BACKLOG "PJI_REP_MSR_8",
28 FACT.BTB_TOTAL_BOOKINGS "PJI_REP_MSR_9",
29 FACT.BTB_REVENUE "PJI_REP_MSR_10",
30 FACT.PRIOR_BTB_TOTAL_BOOKINGS "PJI_REP_MSR_11",
31 FACT.PRIOR_BTB_REVENUE "PJI_REP_MSR_12",
32 FACT.TOTAL_BOOKINGS "PJI_REP_MSR_13",
33 FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_14",
34 FACT.PRIOR_YEAR_BTB_RATIO "PJI_REP_MSR_16",
35 FACT.CURRENT_YEAR_BTB_RATIO "PJI_REP_MSR_15",
36 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
37 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
38 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
39 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
40 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
41 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
42 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
43 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
44 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_13",
45 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_14",
46 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_15",
47 FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_16"'
48 , P_SQL_STATEMENT => x_PMV_Sql
49 , P_PMV_OUTPUT => x_PMV_Output
50 , P_REGION_CODE => 'PJI_REP_PBB1'
51 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB1'
52 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
53 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
54 ', <<CURRENCY+FII_CURRENCIES>>'||
55 ', <<AS_OF_DATE>>'||
56 ', <<PERIOD_TYPE>>'||
57 ', <<VIEW_BY>>'||
58 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
59 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
60 ', <<TIME_COMPARISON_TYPE>>');
61 END Get_SQL_PJI_REP_PBB1;
62
63 /*
64 ** ----------------------------------------------------------
65 ** Procedure: Get_SQL_PJI_REP_PBB2
66 ** This procedure returns sql statement generated by the base
67 ** engine api for the report PJI_REP_PBB2.
68 ** ----------------------------------------------------------
69 */
70 PROCEDURE Get_SQL_PJI_REP_PBB2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
71 , x_PMV_Sql OUT NOCOPY VARCHAR2
72 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
73 IS
74 l_Err_Message VARCHAR2(3200);
75 l_PMV_Sql VARCHAR2(3200);
76 BEGIN
77 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
78 P_SELECT_LIST => 'FACT.PROJECT_ID "PJI_REP_MSR_28",
79 FACT.PROJECT_NAME "VIEWBY",
80 FACT.PROJECT_NUMBER "PJI_REP_MSR_2",
81 FACT.URL_PARAMETERS01 "PJI_REP_MSR_27",
82 FACT.URL_PARAMETERS01 "PJI_REP_MSR_30",
83 FACT.PRIMARY_CUSTOMER "PJI_REP_MSR_3",
84 FACT.PROJECT_TYPE "PJI_REP_MSR_4",
85 FACT.ORGANIZATION "PJI_REP_MSR_5",
86 FACT.PROJECT_MANAGER "PJI_REP_MSR_6",
87 FACT.PROJECT_START_DATE "PJI_REP_MSR_7",
88 FACT.XTD_ORIG_BKNG_AMT "PJI_REP_MSR_8",
89 FACT.XTD_ADDT_BKNG_AMT "PJI_REP_MSR_9",
90 FACT.XTD_TOTAL_BOOKINGS "PJI_REP_MSR_10",
91 FACT.ITD_TOTAL_BOOKINGS "PJI_REP_MSR_11",
92 FACT.BACKLOG_NOT_STARTED "PJI_REP_MSR_12",
93 FACT.ACTIVE_BACKLOG "PJI_REP_MSR_13",
94 FACT.DORMANT_BACKLOG "PJI_REP_MSR_14",
95 FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_15",
96 FACT.PROJECT_CLOSE_DATE "PJI_REP_MSR_16",
97 FACT.LOST_BACKLOG "PJI_REP_MSR_17",
98 FACT.REVENUE_AT_RISK "PJI_REP_MSR_18",
99 FACT.XTD_ADJT_BKNG_AMT "PJI_REP_MSR_19",
100 FACT.XTD_CANC_BKNG_AMT "PJI_REP_MSR_20",
101 FACT.XTD_REVENUE "PJI_REP_MSR_21",
102 FACT.ITD_ORIG_BKNG_AMT "PJI_REP_MSR_22",
103 FACT.ITD_ADDT_BKNG_AMT "PJI_REP_MSR_23",
104 FACT.ITD_ADJT_BKNG_AMT "PJI_REP_MSR_24",
105 FACT.ITD_CANC_BKNG_AMT "PJI_REP_MSR_25",
106 FACT.ITD_REVENUE "PJI_REP_MSR_26",
107 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
108 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
109 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
110 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
111 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
112 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
113 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
114 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
115 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
116 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
117 , P_SQL_STATEMENT => x_PMV_Sql
118 , P_PMV_OUTPUT => x_PMV_Output
119 , P_REGION_CODE => 'PJI_REP_PBB2'
120 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB2'
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 ', <<REV_AT_RISK_FLAG+REV_AT_RISK_FLAG>>');
130 END Get_SQL_PJI_REP_PBB2;
131
132 /*
133 ** ----------------------------------------------------------
134 ** Procedure: Get_SQL_PJI_REP_PBB3
135 ** This procedure returns sql statement generated by the base
136 ** engine api for the report PJI_REP_PBB3.
137 ** ----------------------------------------------------------
138 */
139 PROCEDURE Get_SQL_PJI_REP_PBB3(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
140 , x_PMV_Sql OUT NOCOPY VARCHAR2
141 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
142 IS
143 l_Err_Message VARCHAR2(3200);
144 l_PMV_Sql VARCHAR2(3200);
145 BEGIN
146 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
147 P_SELECT_LIST => 'FACT.BACKLOG "PJI_REP_MSR_1",
148 FACT.ORIGINAL_BOOKINGS "PJI_REP_MSR_2",
149 FACT.ADDITIONAL_BOOKINGS "PJI_REP_MSR_3",
150 FACT.BOOKINGS_ADJUSTMENTS "PJI_REP_MSR_4",
151 FACT.CANCELLATIONS "PJI_REP_MSR_5",
152 FACT.TOTAL_NET_BOOKINGS "PJI_REP_MSR_6",
153 FACT.ACCRUED_REVENUE "PJI_REP_MSR_7",
154 FACT.REVENUE_AT_RISK_ITD "PJI_REP_MSR_8",
155 FACT.LOST_BACKLOG_ITD "PJI_REP_MSR_9",
156 FACT.BACKLOG_ITD "PJI_REP_MSR_10",
157 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
158 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
159 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
160 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
161 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
162 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
163 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
164 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
165 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
166 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"'
167 , P_SQL_STATEMENT => x_PMV_Sql
168 , P_PMV_OUTPUT => x_PMV_Output
169 , P_REGION_CODE => 'PJI_REP_PBB3'
170 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PBB3'
171 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
172 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
173 ', <<CURRENCY+FII_CURRENCIES>>'||
174 ', <<AS_OF_DATE>>'||
175 ', <<PERIOD_TYPE>>'||
176 ', <<VIEW_BY>>'||
177 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
178 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> ');
179 END Get_SQL_PJI_REP_PBB3;
180
181 /*
182 ** ----------------------------------------------------------
183 ** Procedure: Get_SQL_PJI_REP_PBB4
184 ** This procedure returns sql statement generated by the base
185 ** engine api for the report PJI_REP_PBB4.
186 ** ----------------------------------------------------------
187 */
188 PROCEDURE Get_SQL_PJI_REP_PBB4(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
189 , x_PMV_Sql OUT NOCOPY VARCHAR2
190 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
191 IS
192 l_Err_Message VARCHAR2(3200);
193 l_PMV_Sql VARCHAR2(3200);
194 BEGIN
195 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl,
196 P_SELECT_LIST => 'FACT.PROJECT_ID "PJI_REP_MSR_18",
197 FACT.PROJECT_NAME "VIEWBY",
198 FACT.PROJECT_NUMBER "PJI_REP_MSR_2",
199 FACT.URL_PARAMETERS01 "PJI_REP_MSR_20",
200 FACT.URL_PARAMETERS01 "PJI_REP_MSR_30",
201 FACT.PRIMARY_CUSTOMER "PJI_REP_MSR_3",
202 FACT.PROJECT_TYPE "PJI_REP_MSR_4",
203 FACT.ORGANIZATION "PJI_REP_MSR_5",
204 FACT.PROJECT_MANAGER "PJI_REP_MSR_6",
205 FACT.PROJECT_START_DATE "PJI_REP_MSR_7",
206 FACT.BEGINNING_BACKLOG "PJI_REP_MSR_8",
207 FACT.XTD_ORIG_BKNG_AMT "PJI_REP_MSR_9",
208 FACT.XTD_ADDT_BKNG_AMT "PJI_REP_MSR_10",
209 FACT.XTD_ADJT_BKNG_AMT "PJI_REP_MSR_11",
210 FACT.XTD_CANC_BKNG_AMT "PJI_REP_MSR_12",
211 FACT.XTD_TOTAL_BOOKINGS "PJI_REP_MSR_13",
212 FACT.XTD_REVENUE "PJI_REP_MSR_14",
213 FACT.REVENUE_AT_RISK "PJI_REP_MSR_15",
214 FACT.LOST_BACKLOG "PJI_REP_MSR_16",
215 FACT.TOTAL_ENDING_BACKLOG "PJI_REP_MSR_17",
216 FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_14",
217 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
218 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
219 FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11",
220 FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12",
221 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
222 FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_13",
223 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10",
224 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
225 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8"'
226 , P_SQL_STATEMENT => x_PMV_Sql
227 , P_PMV_OUTPUT => x_PMV_Output
228 , P_REGION_CODE => 'PJI_REP_PBB4'
229 , P_PLSQL_DRIVER => 'PJI_PMV_BOOKINGS_BACKLOG.PLSQLDriver_PJI_REP_PBB2'
230 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
231 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
232 ', <<CURRENCY+FII_CURRENCIES>>'||
233 ', <<AS_OF_DATE>>'||
234 ', <<PERIOD_TYPE>>'||
235 ', <<VIEW_BY>>'||
236 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
237 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
238 ', ''N''');
239 END Get_SQL_PJI_REP_PBB4;
240
241 /*
242 ** ----------------------------------------------------------
243 ** Function: PLSQLDriver_PJI_REP_PBB1
244 ** This function is called from select statement
248 ** 1. PJI_REP_PBB1 - Project Bookings & Backlog Summary
245 ** generated by PJI engine. The function returns PL/SQL table
246 ** of records which have to be displayed in the pmv report.
247 ** Following are the reports to which this function caters:
249 ** ----------------------------------------------------------
250 */
251 FUNCTION PLSQLDriver_PJI_REP_PBB1(
252 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
253 , p_Organization IN VARCHAR2
254 , p_Currency_Type IN VARCHAR2
255 , p_As_Of_Date IN NUMBER
256 , p_Period_Type IN VARCHAR2
257 , p_View_BY IN VARCHAR2
258 , p_Classifications IN VARCHAR2 DEFAULT NULL
259 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
260 , p_Comparator_Type IN VARCHAR2 DEFAULT NULL
261 )RETURN PJI_REP_PBB1_TBL
262 IS
263 PRAGMA AUTONOMOUS_TRANSACTION;
264 l_Total_Bookings_Backlog_Tab PJI_REP_PBB1_TBL:=PJI_REP_PBB1_TBL();
265 l_Parse_Class_Codes VARCHAR2(1);
266 l_BTB_C_As_Of_Date NUMBER;
267 l_BTB_End_Date NUMBER;
268 l_BTB_C_End_Date NUMBER;
269 l_BTB_Days NUMBER;
270 l_Calendar_Type VARCHAR2(1);
271
272 l_Top_Organization_Name VARCHAR2(240);
273 l_Top_Org_Index NUMBER:=0;
274
275 x_Total_Bookings NUMBER:=0;
276 x_Prior_Total_Bookings NUMBER:=0;
277 x_Total_Ending_Backlog NUMBER:=0;
278 x_Prior_Total_Ending_Backlog NUMBER:=0;
279 x_BTB_Total_Bookings NUMBER:=0;
280 x_BTB_Revenue NUMBER:=0;
281 x_Prior_BTB_Total_Bookings NUMBER:=0;
282 x_Prior_BTB_Revenue NUMBER:=0;
283
284 l_Total_Bookings NUMBER:=0;
285 l_Prior_Total_Bookings NUMBER:=0;
286 l_Total_Ending_Backlog NUMBER:=0;
287 l_Prior_Total_Ending_Backlog NUMBER:=0;
288 l_BTB_Total_Bookings NUMBER:=0;
289 l_BTB_Revenue NUMBER:=0;
290 l_Prior_BTB_Total_Bookings NUMBER:=0;
291 l_Prior_BTB_Revenue NUMBER:=0;
292 l_curr_record_type_id NUMBER:= 1;
293
294
295 BEGIN
296
297 /*
298 ** Call the Convert_AS_OF_DATE api to derive the
299 ** as of date as per comparator type selected
300 ** in the pmv report.
301 */
302 l_BTB_C_As_Of_Date :=PJI_PMV_ENGINE.Convert_AS_OF_DATE(p_As_Of_Date, p_Period_Type, p_Comparator_Type);
303 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
304
305 /*
306 ** Place a call to all the parse API's which parse the
307 ** parameters passed by PMV and populate all the
308 ** temporary tables.
309 */
310 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit_IDS=>p_Operating_Unit, p_View_BY=>p_View_BY);
311 PJI_PMV_ENGINE.Convert_Organization(p_Top_Organization_ID=>p_Organization,
312 p_View_BY=>p_View_BY,
313 p_Top_Organization_Name =>l_Top_Organization_Name);
314 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=>'N', p_Report_Type=>'DBI', p_Comparator=>p_Comparator_Type, p_Parse_ITD=>'Y');
315
316 /*
317 ** Determine the lag days between book to bill based on
318 ** PJI setup.
319 */
320
321 BEGIN
322 SELECT NVL(book_to_bill_days,0)
323 INTO l_BTB_Days
324 FROM
325 pji_system_settings;
326 EXCEPTION
327 WHEN OTHERS THEN
328 l_BTB_Days:=0;
329 END;
330
331 IF p_Period_Type LIKE '%PA%' THEN
332 l_Calendar_Type:='P';
333 ELSE
334 l_Calendar_Type:='C';
335 END IF;
336
337 /*
338 ** Determine the fact tables you choose to run the database
339 ** query on ( this step is what we call manual query re-write).
340 */
341
342 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
343
344 /*
345 ** Code the SQL statement for all of the following conditions
346 ** 1. Current Year
347 ** 2. Prior Year
348 ** 3. SQL to generate rows with zero's for the view by dimension
349 ** Bulk-Collect the output into a pl/sql table to be returned to
350 ** pmv.
351 */
352
353 SELECT PJI_REP_PBB1( ORG_ID
354 , ORGANIZATION_ID
355 , TIME_ID
356 , TIME_KEY
357 , PROJECT_CLASS_ID
358 , SUM( TOTAL_BOOKINGS )
359 , SUM( PRIOR_TOTAL_BOOKINGS )
360 , 0
361 , SUM( TOTAL_ENDING_BACKLOG )
362 , SUM( PRIOR_TOTAL_ENDING_BACKLOG )
363 , 0
364 , SUM( BTB_TOTAL_BOOKINGS )
365 , SUM( BTB_REVENUE )
366 , 0
367 , SUM( PRIOR_BTB_TOTAL_BOOKINGS )
368 , SUM( PRIOR_BTB_REVENUE )
369 , 0
370 , 0
371 , 0
372 , 0
373 , 0
374 , 0
375 , 0
376 , 0
377 , 0,0,0,0,0)
378 BULK COLLECT INTO l_Total_Bookings_Backlog_Tab
379 FROM
380 ( SELECT /*+ ORDERED */
381 HOU.name org_id
382 , HORG.name organization_id
383 , TIME.name time_id
384 , -1 time_key
385 , '-1' project_class_id
386 , (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount + FCT.cancelled_funding_amount) total_bookings
387 , 0 prior_total_bookings
388 , 0
389 , 0 total_ending_backlog
390 , 0 prior_total_ending_backlog
394 , 0
391 , 0
392 , 0 btb_total_bookings
393 , 0 btb_revenue
395 , 0 prior_btb_total_bookings
396 , 0 prior_btb_revenue
397 FROM pji_pmv_time_dim_tmp TIME
398 , pji_pmv_orgz_dim_tmp HORG
399 , pji_ac_orgo_f_mv FCT
400 , pji_pmv_org_dim_tmp HOU
401 WHERE
402 FCT.org_id = HOU.id
403 AND FCT.organization_id = HORG.id
404 AND FCT.time_id = TIME.id
405 AND TIME.id IS NOT NULL
406 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
407 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
408 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
409 UNION ALL
410 SELECT /*+ ORDERED */
411 HOU.name org_id
412 , HORG.name organization_id
413 , TIME.name time_id
414 , -1 time_key
415 , '-1' project_class_id
416 , 0 total_bookings
417 , (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount +FCT.cancelled_funding_amount) prior_total_bookings
418 , 0
419 , 0 total_ending_backlog
420 , 0 prior_total_ending_backlog
421 , 0
422 , 0 btb_total_bookings
423 , 0 btb_revenue
424 , 0
425 , 0 prior_btb_total_bookings
426 , 0 prior_btb_revenue
427 FROM pji_pmv_tcmp_dim_tmp TIME
428 , pji_pmv_orgz_dim_tmp HORG
429 , pji_ac_orgo_f_mv FCT
430 , pji_pmv_org_dim_tmp HOU
431 WHERE
432 FCT.org_id = HOU.id
433 AND FCT.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 bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
439 UNION ALL
440 SELECT /*+ ORDERED */
441 HOU.name org_id
442 , HORG.name organization_id
443 , TIME.name time_id
444 , -1 time_key
445 , '-1' project_class_id
446 , 0 total_bookings
447 , 0 prior_total_bookings
448 , 0
449 , (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) total_ending_backlog
450 , 0 prior_total_ending_backlog
451 , 0
452 , 0 btb_total_bookings
453 , 0 btb_revenue
454 , 0
455 , 0 prior_btb_total_bookings
456 , 0 prior_btb_revenue
457 FROM pji_pmv_itd_dim_tmp TIME
458 , pji_pmv_orgz_dim_tmp HORG
459 , pji_ac_orgo_f_mv FCT
460 , pji_pmv_org_dim_tmp HOU
461 WHERE
462 FCT.org_id = HOU.id
463 AND FCT.organization_id = HORG.id
464 AND FCT.time_id = TIME.id
465 AND TIME.id IS NOT NULL
466 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
467 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
468 AND TIME.comparator_type = 'I'
469 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
470 UNION ALL
471 SELECT /*+ ORDERED */
472 HOU.name org_id
473 , HORG.name organization_id
474 , TIME.name time_id
475 , -1 time_key
476 , '-1' project_class_id
477 , 0 total_bookings
478 , 0 prior_total_bookings
479 , 0
480 , 0 total_ending_backlog
481 , (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) prior_total_ending_backlog
482 , 0
483 , 0 btb_total_bookings
484 , 0 btb_revenue
485 , 0
486 , 0 prior_btb_total_bookings
487 , 0 prior_btb_revenue
488 FROM pji_pmv_itd_dim_tmp TIME
489 , pji_pmv_orgz_dim_tmp HORG
490 , pji_ac_orgo_f_mv FCT
491 , pji_pmv_org_dim_tmp HOU
492 WHERE
493 FCT.org_id = HOU.id
494 AND FCT.organization_id = HORG.id
495 AND FCT.time_id = TIME.id
496 AND TIME.id IS NOT NULL
497 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
498 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
499 AND TIME.comparator_type = 'D'
500 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
501 UNION ALL
502 SELECT /*+ ORDERED */
503 HOU.name org_id
504 , HORG.name organization_id
505 , '-1' time_id
506 , -1 time_key
507 , '-1' project_class_id
508 , 0 total_bookings
509 , 0 prior_total_bookings
510 , 0
511 , 0 total_ending_backlog
512 , 0 prior_total_ending_backlog
513 , 0
514 , FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount btb_total_bookings
515 , FCT.revenue btb_revenue
516 , 0
517 , 0 prior_btb_total_bookings
518 , 0 prior_btb_revenue
519 FROM pji_pmv_orgz_dim_tmp HORG
520 , pji_ac_orgo_f_mv FCT
521 , pji_pmv_org_dim_tmp HOU
522 WHERE
523 FCT.org_id = HOU.id
524 AND FCT.organization_id = HORG.id
525 AND FCT.time_id BETWEEN p_As_Of_Date-l_BTB_Days AND p_As_Of_Date
526 AND FCT.PERIOD_TYPE_ID = 1
527 AND FCT.CALENDAR_TYPE = l_Calendar_Type
528 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
529 UNION ALL
530 SELECT /*+ ORDERED */
531 HOU.name org_id
532 , HORG.name organization_id
533 , '-1' time_id
534 , -1 time_key
535 , '-1' project_class_id
536 , 0 total_bookings
537 , 0 prior_total_bookings
538 , 0
539 , 0 total_ending_backlog
543 , 0 btb_revenue
540 , 0 prior_total_ending_backlog
541 , 0
542 , 0 btb_total_bookings
544 , 0
545 , FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount prior_btb_total_bookings
546 , FCT.revenue prior_btb_revenue
547 FROM pji_pmv_orgz_dim_tmp HORG
548 , pji_ac_orgo_f_mv FCT
549 , pji_pmv_org_dim_tmp HOU
550
551 WHERE
552 FCT.org_id = HOU.id
553 AND FCT.organization_id = HORG.id
554 AND FCT.time_id BETWEEN l_BTB_C_As_Of_Date-l_BTB_Days AND l_BTB_C_As_Of_Date
555 AND FCT.PERIOD_TYPE_ID = 1
556 AND FCT.CALENDAR_TYPE = l_Calendar_Type
557 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
558 UNION ALL
559 SELECT name org_id
560 , '-1' organization_id
561 , '-1' time_id
562 , -1 time_key
563 , '-1' project_class_id
564 , 0 total_bookings
565 , 0 prior_total_bookings
566 , 0
567 , 0 total_ending_backlog
568 , 0 prior_total_ending_backlog
569 , 0
570 , 0 btb_total_bookings
571 , 0 btb_revenue
572 , 0
573 , 0 prior_btb_total_bookings
574 , 0 prior_btb_revenue
575 FROM pji_pmv_org_dim_tmp
576 WHERE name <> '-1'
577 UNION ALL
578 SELECT '-1' org_id
579 , name organization_id
580 , '-1' time_id
581 , -1 time_key
582 , '-1' project_class_id
583 , 0 total_bookings
584 , 0 prior_total_bookings
585 , 0
586 , 0 total_ending_backlog
587 , 0 prior_total_ending_backlog
588 , 0
589 , 0 btb_total_bookings
590 , 0 btb_revenue
591 , 0
592 , 0 prior_btb_total_bookings
593 , 0 prior_btb_revenue
594 FROM pji_pmv_orgz_dim_tmp
595 WHERE name <> '-1'
596 UNION ALL
597 SELECT '-1' org_id
598 , '-1' organization_id
599 , name time_id
600 , order_by_id time_key
601 , '-1' project_class_id
602 , 0 total_bookings
603 , 0 prior_total_bookings
604 , 0
605 , 0 total_ending_backlog
606 , 0 prior_total_ending_backlog
607 , 0
608 , 0 btb_total_bookings
609 , 0 btb_revenue
610 , 0
611 , 0 prior_btb_total_bookings
612 , 0 prior_btb_revenue
613 FROM pji_pmv_time_dim_tmp
614 WHERE name <> '-1')
615 GROUP BY
616 ORG_ID
617 , ORGANIZATION_ID
618 , TIME_KEY
619 , TIME_ID
620 , PROJECT_CLASS_ID;
621 ELSE
622 /*
623 ** Code the SQL statement for all of the following conditions
624 ** 1. Current Year
625 ** 2. Prior Year
626 ** 3. SQL to generate rows with zero's for the view by dimension
627 */
628
629 SELECT PJI_REP_PBB1( ORG_ID
630 , ORGANIZATION_ID
631 , TIME_ID
632 , TIME_KEY
633 , PROJECT_CLASS_ID
634 , SUM( TOTAL_BOOKINGS )
635 , SUM( PRIOR_TOTAL_BOOKINGS )
636 , 0
637 , SUM( TOTAL_ENDING_BACKLOG )
638 , SUM( PRIOR_TOTAL_ENDING_BACKLOG )
639 , 0
640 , SUM( BTB_TOTAL_BOOKINGS )
641 , SUM( BTB_REVENUE )
642 , 0
643 , SUM( PRIOR_BTB_TOTAL_BOOKINGS )
644 , SUM( PRIOR_BTB_REVENUE )
645 , 0
646 , 0
647 , 0
648 , 0
649 , 0
650 , 0
651 , 0
652 , 0
653 , 0,0,0,0,0)
654 BULK COLLECT INTO l_Total_Bookings_Backlog_Tab
655 FROM
656 ( SELECT /*+ ORDERED */
657 HOU.name org_id
658 , HORG.name organization_id
659 , TIME.name time_id
660 , -1 time_key
661 , CLS.name project_class_id
662 , (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount) total_bookings
663 , 0 prior_total_bookings
664 , 0
665 , 0 total_ending_backlog
666 , 0 prior_total_ending_backlog
667 , 0
668 , 0 btb_total_bookings
669 , 0 btb_revenue
670 , 0
671 , 0 prior_btb_total_bookings
672 , 0 prior_btb_revenue
673 FROM pji_pmv_time_dim_tmp TIME
674 , pji_pmv_orgz_dim_tmp HORG
675 , pji_pmv_cls_dim_tmp CLS
676 , pji_ac_clso_f_mv FCT
677 , pji_pmv_org_dim_tmp HOU
678 WHERE
679 FCT.project_org_id = HOU.id
680 AND FCT.project_organization_id = HORG.id
681 AND FCT.time_id = TIME.id
682 AND TIME.id IS NOT NULL
683 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
684 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
685 AND FCT.project_class_id = CLS.id
686 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
687 UNION ALL
688 SELECT /*+ ORDERED */
689 HOU.name org_id
690 , HORG.name organization_id
691 , TIME.name time_id
692 , -1 time_key
696 , 0
693 , CLS.name project_class_id
694 , 0 total_bookings
695 , (FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount) prior_total_bookings
697 , 0 total_ending_backlog
698 , 0 prior_total_ending_backlog
699 , 0
700 , 0 btb_total_bookings
701 , 0 btb_revenue
702 , 0
703 , 0 prior_btb_total_bookings
704 , 0 prior_btb_revenue
705 FROM pji_pmv_tcmp_dim_tmp TIME
706 , pji_pmv_orgz_dim_tmp HORG
707 , pji_pmv_cls_dim_tmp CLS
708 , pji_ac_clso_f_mv FCT
709 , pji_pmv_org_dim_tmp HOU
710 WHERE
711 FCT.project_org_id = HOU.id
712 AND FCT.project_organization_id = HORG.id
713 AND FCT.time_id = TIME.id
714 AND TIME.id IS NOT NULL
715 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
716 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
717 AND FCT.project_class_id = CLS.id
718 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
719 UNION ALL
720 SELECT /*+ ORDERED */
721 HOU.name org_id
722 , HORG.name organization_id
723 , TIME.name time_id
724 , -1 time_key
725 , CLS.name project_class_id
726 , 0 total_bookings
727 , 0 prior_total_bookings
728 , 0
729 , (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) total_ending_backlog
730 , 0 prior_total_ending_backlog
731 , 0
732 , 0 btb_total_bookings
733 , 0 btb_revenue
734 , 0
735 , 0 prior_btb_total_bookings
736 , 0 prior_btb_revenue
737 FROM pji_pmv_itd_dim_tmp TIME
738 , pji_pmv_orgz_dim_tmp HORG
739 , pji_pmv_cls_dim_tmp CLS
740 , pji_ac_clso_f_mv FCT
741 , pji_pmv_org_dim_tmp HOU
742 WHERE
743 FCT.project_org_id = HOU.id
744 AND FCT.project_organization_id = HORG.id
745 AND FCT.time_id = TIME.id
746 AND TIME.id IS NOT NULL
747 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
748 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
749 AND TIME.comparator_type = 'I'
750 AND FCT.project_class_id = CLS.id
751 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
752 UNION ALL
753 SELECT /*+ ORDERED */
754 HOU.name org_id
755 , HORG.name organization_id
756 , TIME.name time_id
757 , -1 time_key
758 , CLS.name project_class_id
759 , 0 total_bookings
760 , 0 prior_total_bookings
761 , 0
762 , 0 total_ending_backlog
763 , (FCT.dormant_backlog_start+FCT.active_backlog+FCT.dormant_backlog_inactiv) prior_total_ending_backlog
764 , 0
765 , 0 btb_total_bookings
766 , 0 btb_revenue
767 , 0
768 , 0 prior_btb_total_bookings
769 , 0 prior_btb_revenue
770 FROM pji_pmv_itd_dim_tmp TIME
771 , pji_pmv_orgz_dim_tmp HORG
772 , pji_pmv_cls_dim_tmp CLS
773 , pji_ac_clso_f_mv FCT
774 , pji_pmv_org_dim_tmp HOU
775 WHERE
776 FCT.project_org_id = HOU.id
777 AND FCT.project_organization_id = HORG.id
778 AND FCT.time_id = TIME.id
779 AND TIME.id IS NOT NULL
780 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
781 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
782 AND TIME.comparator_type = 'D'
783 AND FCT.project_class_id = CLS.id
784 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
785 UNION ALL
786 SELECT /*+ ORDERED */
787 HOU.name org_id
788 , HORG.name organization_id
789 , '-1' time_id
790 , -1 time_key
791 , CLS.name project_class_id
792 , 0 total_bookings
793 , 0 prior_total_bookings
794 , 0
795 , 0 total_ending_backlog
796 , 0 prior_total_ending_backlog
797 , 0
798 , FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount btb_total_bookings
799 , FCT.revenue btb_revenue
800 , 0
801 , 0 prior_btb_total_bookings
802 , 0 prior_btb_revenue
803 FROM pji_pmv_orgz_dim_tmp HORG
804 , pji_pmv_cls_dim_tmp CLS
805 , pji_ac_clso_f_mv FCT
806 , pji_pmv_org_dim_tmp HOU
807 WHERE
808 FCT.project_org_id = HOU.id
809 AND FCT.project_organization_id = HORG.id
810 AND FCT.time_id BETWEEN p_As_Of_Date-l_BTB_Days AND p_As_Of_Date
811 AND FCT.PERIOD_TYPE_ID = 1
812 AND FCT.CALENDAR_TYPE = l_Calendar_Type
813 AND FCT.project_class_id = CLS.id
814 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
815 UNION ALL
816 SELECT /*+ ORDERED */
817 HOU.name org_id
818 , HORG.name organization_id
819 , '-1' time_id
820 , -1 time_key
821 , CLS.name project_class_id
822 , 0 total_bookings
823 , 0 prior_total_bookings
824 , 0
825 , 0 total_ending_backlog
826 , 0 prior_total_ending_backlog
827 , 0
828 , 0 btb_total_bookings
829 , 0 btb_revenue
830 , 0
831 , FCT.initial_funding_amount+FCT.additional_funding_amount+FCT.funding_adjustment_amount+FCT.cancelled_funding_amount prior_btb_total_bookings
832 , FCT.revenue prior_btb_revenue
833 FROM pji_pmv_orgz_dim_tmp HORG
834 , pji_pmv_cls_dim_tmp CLS
835 , pji_ac_clso_f_mv FCT
839 AND FCT.project_organization_id = HORG.id
836 , pji_pmv_org_dim_tmp HOU
837 WHERE
838 FCT.project_org_id = HOU.id
840 AND FCT.time_id BETWEEN l_BTB_C_As_Of_Date-l_BTB_Days AND l_BTB_C_As_Of_Date
841 AND FCT.PERIOD_TYPE_ID = 1
842 AND FCT.CALENDAR_TYPE = l_Calendar_Type
843 AND FCT.project_class_id = CLS.id
844 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
845 UNION ALL
846 SELECT name org_id
847 , '-1' organization_id
848 , '-1' time_id
849 , -1 time_key
850 , '-1' project_class_id
851 , 0 total_bookings
852 , 0 prior_total_bookings
853 , 0
854 , 0 total_ending_backlog
855 , 0 prior_total_ending_backlog
856 , 0
857 , 0 btb_total_bookings
858 , 0 btb_revenue
859 , 0
860 , 0 prior_btb_total_bookings
861 , 0 prior_btb_revenue
862 FROM pji_pmv_org_dim_tmp
863 WHERE name <> '-1'
864 UNION ALL
865 SELECT '-1' org_id
866 , name organization_id
867 , '-1' time_id
868 , -1 time_key
869 , '-1' project_class_id
870 , 0 total_bookings
871 , 0 prior_total_bookings
872 , 0
873 , 0 total_ending_backlog
874 , 0 prior_total_ending_backlog
875 , 0
876 , 0 btb_total_bookings
877 , 0 btb_revenue
878 , 0
879 , 0 prior_btb_total_bookings
880 , 0 prior_btb_revenue
881 FROM pji_pmv_orgz_dim_tmp
882 WHERE name <> '-1'
883 UNION ALL
884 SELECT '-1' org_id
885 , '-1' organization_id
886 , name time_id
887 , order_by_id time_key
888 , '-1' project_class_id
889 , 0 total_bookings
890 , 0 prior_total_bookings
891 , 0
892 , 0 total_ending_backlog
893 , 0 prior_total_ending_backlog
894 , 0
895 , 0 btb_total_bookings
896 , 0 btb_revenue
897 , 0
898 , 0 prior_btb_total_bookings
899 , 0 prior_btb_revenue
900 FROM pji_pmv_time_dim_tmp
901 WHERE name <> '-1'
902 UNION ALL
903 SELECT '-1' ORG_ID
904 ,'-1' ORGANIZATION_ID
905 ,'-1' TIME_ID
906 , -1 TIME_KEY
907 , NAME PROJECT_CLASS_ID
908 , 0 TOTAL_BOOKINGS
909 , 0 PRIOR_TOTAL_BOOKINGS
910 , 0
911 , 0 TOTAL_ENDING_BACKLOG
912 , 0 PRIOR_TOTAL_ENDING_BACKLOG
913 , 0
914 , 0 BTB_TOTAL_BOOKINGS
915 , 0 BTB_REVENUE
916 , 0
917 , 0 PRIOR_BTB_TOTAL_BOOKINGS
918 , 0 PRIOR_BTB_REVENUE
919 FROM PJI_PMV_CLS_DIM_TMP
920 WHERE NAME <> '-1'
921 )
922 GROUP BY
923 ORG_ID
924 , ORGANIZATION_ID
925 , TIME_KEY
926 , TIME_ID
927 , PROJECT_CLASS_ID;
928 END IF;
929
930 FOR i in 1..l_Total_Bookings_Backlog_Tab.COUNT
931 LOOP
932 IF p_View_By = 'OG' THEN
933 IF l_Total_Bookings_Backlog_Tab(i).ORGANIZATION_ID = l_Top_Organization_Name THEN
934 l_Top_Org_Index:=i;
935
936 l_Total_Bookings := NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_BOOKINGS,0);
937 l_Prior_Total_Bookings := NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS,0);
938 l_Total_Ending_Backlog := NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
939 l_Prior_Total_Ending_Backlog := NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
940 l_BTB_Total_Bookings := NVL(l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS,0);
941 l_BTB_Revenue := NVL(l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE,0);
942 l_Prior_BTB_Total_Bookings := NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_TOTAL_BOOKINGS,0);
943 l_Prior_BTB_Revenue :=NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE,0);
944
945 ELSE
946 x_Total_Bookings :=x_Total_Bookings
947 + NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_BOOKINGS,0);
948 x_Prior_Total_Bookings :=x_Prior_Total_Bookings
949 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS,0);
950 x_Total_Ending_Backlog :=x_Total_Ending_Backlog
954 x_BTB_Total_Bookings :=x_BTB_Total_Bookings
951 + NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
952 x_Prior_Total_Ending_Backlog:=x_Prior_Total_Ending_Backlog
953 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
955 + NVL(l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS,0);
956 x_BTB_Revenue :=x_BTB_Revenue
957 + NVL(l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE,0);
958 x_Prior_BTB_Total_Bookings :=x_Prior_BTB_Total_Bookings
959 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_TOTAL_BOOKINGS,0);
960 x_Prior_BTB_Revenue :=x_Prior_BTB_Revenue
961 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE,0);
962 END IF;
963 ELSE
964 x_Total_Bookings :=x_Total_Bookings
965 + NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_BOOKINGS,0);
966 x_Prior_Total_Bookings :=x_Prior_Total_Bookings
967 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS,0);
968 x_Total_Ending_Backlog :=x_Total_Ending_Backlog
969 + NVL(l_Total_Bookings_Backlog_Tab(i).TOTAL_ENDING_BACKLOG,0);
970 x_Prior_Total_Ending_Backlog:=x_Prior_Total_Ending_Backlog
971 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG,0);
972 x_BTB_Total_Bookings :=x_BTB_Total_Bookings
973 + NVL(l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS,0);
974 x_BTB_Revenue :=x_BTB_Revenue
975 + NVL(l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE,0);
976 x_Prior_BTB_Total_Bookings :=x_Prior_BTB_Total_Bookings
977 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_TOTAL_BOOKINGS,0);
978 x_Prior_BTB_Revenue :=x_Prior_BTB_Revenue
979 + NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE,0);
980 END IF;
981
982 IF NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS, 0)=0 THEN
983 l_Total_Bookings_Backlog_Tab(i).BOOKINGS_CHANGE:= NULL;
984 ELSE
985 l_Total_Bookings_Backlog_Tab(i).BOOKINGS_CHANGE:=
986 ((l_Total_Bookings_Backlog_Tab(i).TOTAL_BOOKINGS
987 -l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS)
988 /ABS(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_BOOKINGS))*100;
989 END IF;
990
991 IF
992 NVL(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG, 0)=0 THEN
993 l_Total_Bookings_Backlog_Tab(i).BACKLOG_CHANGE :=NULL;
994 ELSE
995 l_Total_Bookings_Backlog_Tab(i).BACKLOG_CHANGE :=
996 ((l_Total_Bookings_Backlog_Tab(i).TOTAL_ENDING_BACKLOG
997 -l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG)
998 /ABS(l_Total_Bookings_Backlog_Tab(i).PRIOR_TOTAL_ENDING_BACKLOG))*100;
999 END IF;
1000
1001
1002
1003 IF
1004 l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE=0 THEN
1005 l_Total_Bookings_Backlog_Tab(i).BTB_RATIO :=NULL;
1006 l_Total_Bookings_Backlog_Tab(i).CURRENT_YEAR_BTB_RATIO:=NULL;
1007 ELSE
1008 l_Total_Bookings_Backlog_Tab(i).BTB_RATIO :=
1009 l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS/
1010 l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE;
1011 l_Total_Bookings_Backlog_Tab(i).CURRENT_YEAR_BTB_RATIO:=
1012 l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS/
1013 l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE;
1014
1015 END IF;
1016 IF
1017 l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE =0 THEN
1018 l_Total_Bookings_Backlog_Tab(i).PRIOR_YEAR_BTB_RATIO:= NULL;
1019 ELSE
1020 l_Total_Bookings_Backlog_Tab(i).PRIOR_YEAR_BTB_RATIO:=
1021 l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_TOTAL_BOOKINGS/
1022 l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE;
1023 END IF;
1024
1025 IF
1026 NVL(l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE, 0)=0
1027 OR l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE=0 THEN
1028 l_Total_Bookings_Backlog_Tab(i).BTB_CHANGE :=NULL;
1029 ELSE
1030 l_Total_Bookings_Backlog_Tab(i).BTB_CHANGE :=
1031 (l_Total_Bookings_Backlog_Tab(i).BTB_TOTAL_BOOKINGS/
1032 l_Total_Bookings_Backlog_Tab(i).BTB_REVENUE) -
1033 (l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_TOTAL_BOOKINGS/
1034 l_Total_Bookings_Backlog_Tab(i).PRIOR_BTB_REVENUE);
1035 END IF;
1036 END LOOP;
1037
1038 IF p_View_By='OG' THEN
1039 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS
1040 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS,0)
1041 - x_Total_Bookings;
1042 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS
1043 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS,0)
1044 - x_Prior_Total_Bookings;
1045 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG
1046 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG,0)
1050 - x_Prior_Total_Ending_Backlog;
1047 - x_Total_Ending_Backlog;
1048 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG
1049 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG, 0)
1051 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS
1052 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS,0)
1053 - x_BTB_Total_Bookings;
1054 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE
1055 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE,0)
1056 - x_BTB_Revenue;
1057 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_TOTAL_BOOKINGS
1058 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_TOTAL_BOOKINGS,0)
1059 - x_Prior_BTB_Total_Bookings;
1060 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE
1061 := NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE,0)
1062 - x_Prior_BTB_Revenue;
1063
1064 IF NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS, 0)=0 THEN
1065 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BOOKINGS_CHANGE:= NULL;
1066 ELSE
1067 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BOOKINGS_CHANGE:=
1068 ((l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS
1069 -l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS)
1070 /ABS(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS))*100;
1071 END IF;
1072
1073 IF
1074 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG, 0)=0 THEN
1075 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BACKLOG_CHANGE :=NULL;
1076 ELSE
1077 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BACKLOG_CHANGE :=
1078 ((l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG
1079 -l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG)
1080 /ABS(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG))*100;
1081 END IF;
1082
1083 IF
1084 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE=0 THEN
1085 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_RATIO :=NULL;
1086 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).CURRENT_YEAR_BTB_RATIO:=NULL;
1087 ELSE
1088 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_RATIO :=
1089 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS/
1090 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE;
1091 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).CURRENT_YEAR_BTB_RATIO:=
1092 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS/
1093 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE;
1094
1095 END IF;
1096 IF
1097 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE =0 THEN
1098 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_YEAR_BTB_RATIO:= NULL;
1099 ELSE
1100 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_YEAR_BTB_RATIO:=
1101 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_TOTAL_BOOKINGS/
1102 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE;
1103 END IF;
1104
1105 IF
1106 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE, 0)=0
1107 OR l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE=0 THEN
1108 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_CHANGE :=NULL;
1109 ELSE
1110 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_CHANGE :=
1111 (l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS/
1112 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE) -
1113 (l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_TOTAL_BOOKINGS/
1114 l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE);
1115 END IF;
1116
1117 IF
1118 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_BOOKINGS, 0)=0 AND
1119 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_BOOKINGS, 0)=0 AND
1120 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).TOTAL_ENDING_BACKLOG, 0)=0 AND
1121 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_TOTAL_ENDING_BACKLOG, 0)=0 AND
1122 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_TOTAL_BOOKINGS, 0)=0 AND
1123 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).BTB_REVENUE, 0) =0 AND
1124 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_TOTAL_BOOKINGS, 0)=0 AND
1125 NVL(l_Total_Bookings_Backlog_Tab(l_Top_Org_Index).PRIOR_BTB_REVENUE, 0)=0
1126 THEN
1127 l_Total_Bookings_Backlog_Tab.DELETE(l_Top_Org_Index);
1128 END IF;
1129
1130 x_Total_Bookings := l_Total_Bookings;
1131 x_Prior_Total_Bookings := l_Prior_Total_Bookings;
1132 x_Total_Ending_Backlog := l_Total_Ending_Backlog;
1133 x_Prior_Total_Ending_Backlog := l_Prior_Total_Ending_Backlog;
1134 x_BTB_Total_Bookings := l_BTB_Total_Bookings;
1135 x_BTB_Revenue := l_BTB_Revenue;
1136 x_Prior_BTB_Total_Bookings := l_Prior_BTB_Total_Bookings;
1137 x_Prior_BTB_Revenue := l_Prior_BTB_Revenue;
1138
1139 END IF;
1140
1141 /*
1145
1142 ** Calculating Grand Total based on the values in
1143 ** the bulk collected pl/sql table.
1144 */
1146 IF l_Total_Bookings_Backlog_Tab.COUNT > 0 THEN
1147 FOR i IN l_Total_Bookings_Backlog_Tab.FIRST..l_Total_Bookings_Backlog_Tab.LAST
1148 LOOP
1149 IF l_Total_Bookings_Backlog_Tab.EXISTS(i) THEN
1150
1151 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_1:=x_Total_Bookings;
1152 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_7:=x_Prior_Total_Bookings;
1153 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_3:=x_Total_Ending_Backlog;
1154 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_8:=x_Prior_Total_Ending_Backlog;
1155
1156 IF NVL(x_Prior_BTB_Revenue,0) > 0 THEN
1157 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_16:=
1158 x_Prior_BTB_Total_Bookings/x_Prior_BTB_Revenue;
1159
1160 ELSE
1161 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_16:=NULL;
1162 END IF;
1163
1164 IF x_Prior_Total_Bookings <> 0 AND x_Prior_Total_Bookings IS NOT NULL THEN
1165 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_2:=
1166 (x_Total_Bookings - x_Prior_Total_Bookings)*100/abs(x_Prior_Total_Bookings);
1167 ELSE l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_2:=NULL;
1168 END IF;
1169
1170 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_3:=x_Total_Ending_Backlog;
1171
1172 IF
1173 x_Prior_Total_Ending_Backlog<>0 AND x_Prior_Total_Ending_Backlog IS NOT NULL THEN
1174 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_4:=
1175 (x_Total_Ending_Backlog - x_Prior_Total_Ending_Backlog)*100
1176 /abs(x_Prior_Total_Ending_Backlog);
1177 ELSE
1178 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_4:=NULL;
1179
1180 END IF;
1181
1182 IF x_BTB_Revenue >0 AND x_BTB_Revenue IS NOT NULL THEN
1183 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_5:=
1184 x_BTB_Total_Bookings/x_BTB_Revenue;
1185 ELSE
1186 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_5:=NULL;
1187 END IF;
1188
1189 IF NVL(x_Prior_BTB_Revenue,0) > 0 AND NVL(x_BTB_Revenue, 0) >0 THEN
1190 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_6:=
1191 x_BTB_Total_Bookings/x_BTB_Revenue -
1192 x_Prior_BTB_Total_Bookings/x_Prior_BTB_Revenue;
1193 ELSE
1194 l_Total_Bookings_Backlog_Tab(i).PJI_REP_TOTAL_6:=NULL;
1195 END IF;
1196
1197
1198 END IF;
1199 END LOOP;
1200 END IF;
1201 /*
1202 ** Return the bulk collected table back to pmv.
1203 */
1204
1205 COMMIT;
1206 RETURN l_Total_Bookings_Backlog_Tab;
1207 END PLSQLDriver_PJI_REP_PBB1;
1208
1209 /*
1210 ** ----------------------------------------------------------
1211 ** FUNCTION: PLSQLDRIVER_PJI_REP_PBB2
1212 ** THIS TABLE FUNCTION IS CALLED FROM SELECT STATEMENT
1213 ** GENERATED BY PJI ENGINE. THE FUNCTION RETURNS PL/SQL TABLE
1214 ** OF RECORDS WHICH HAVE TO BE DISPLAYED IN THE PMV REPORT.
1215 ** FOLLOWING ARE THE REPORTS TO WHICH THIS FUNCTION CATERS:
1216 ** 1. PJI_REP_PBB2 - PROJECT BOOKINGS & BACKLOG SUMMARY
1217 ** 2. PJI_REP_PBB4 - PROJECT BOOKINGS & BACKLOG SUMMARY
1218 ** ----------------------------------------------------------
1219 */
1220
1221 FUNCTION PLSQLDRIVER_PJI_REP_PBB2 (
1222 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
1223 P_ORGANIZATION IN VARCHAR2,
1224 P_CURRENCY_TYPE IN VARCHAR2,
1225 P_AS_OF_DATE IN NUMBER,
1226 P_PERIOD_TYPE IN VARCHAR2,
1227 P_VIEW_BY IN VARCHAR2,
1228 P_CLASSIFICATIONS IN VARCHAR2 DEFAULT NULL,
1229 P_CLASS_CODES IN VARCHAR2 DEFAULT NULL,
1230 P_RUN_REVENUE_AT_RISK IN VARCHAR2 DEFAULT 'N'
1231 )
1232 RETURN PJI_REP_PBB2_TBL
1233 IS
1234 PRAGMA AUTONOMOUS_TRANSACTION;
1235 L_TOTAL_BOOKINGS_BACKLOG_TAB PJI_REP_PBB2_TBL := PJI_REP_PBB2_TBL ();
1236 X_TOTAL_BOOKINGS_BACKLOG_TAB PJI_REP_PBB2_TBL := PJI_REP_PBB2_TBL ();
1237 L_PARSE_CLASS_CODES VARCHAR2 (1);
1238 L_REORG_ROWCOUNT NUMBER;
1239 L_ACTUAL_ROWCOUNT NUMBER;
1240
1241
1242 l_beginning_backlog NUMBER:=0;
1243 l_total_ending_backlog NUMBER:=0;
1244 l_backlog_not_started NUMBER:=0;
1245 l_active_backlog NUMBER:=0;
1246 l_dormant_backlog NUMBER:=0;
1247 l_xtd_orig_bkng_amt NUMBER:=0;
1248 l_xtd_addt_bkng_amt NUMBER:=0;
1249 l_xtd_adjt_bkng_amt NUMBER:=0;
1250 l_xtd_canc_bkng_amt NUMBER:=0;
1251 l_total_bookings_amount NUMBER:=0;
1252 l_total_bookings_amount_itd NUMBER:=0;
1253 l_xtd_revenue NUMBER:=0;
1254 l_lost_backlog NUMBER:=0;
1255 l_revenue_at_risk NUMBER:=0;
1256 l_curr_record_type_id NUMBER := 1;
1257
1258 BEGIN
1259 /*
1260 ** PLACE A CALL TO ALL THE PARSE API'S WHICH PARSE THE
1261 ** PARAMETERS PASSED BY PMV AND POPULATE ALL THE
1262 ** TEMPORARY TABLES.
1263 */
1264 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (
1265 P_OPERATING_UNIT_IDS=> P_OPERATING_UNIT,
1266 P_VIEW_BY=> P_VIEW_BY
1267 );
1268 PJI_PMV_ENGINE.CONVERT_ORGANIZATION (
1269 P_TOP_ORGANIZATION_ID=> P_ORGANIZATION,
1270 P_VIEW_BY=> P_VIEW_BY
1274 P_PERIOD_TYPE=> P_PERIOD_TYPE,
1271 );
1272 PJI_PMV_ENGINE.CONVERT_TIME (
1273 P_AS_OF_DATE=> P_AS_OF_DATE,
1275 P_VIEW_BY=> P_VIEW_BY,
1276 P_PARSE_PRIOR=> 'N',
1277 P_REPORT_TYPE=> NULL,
1278 P_COMPARATOR=> NULL,
1279 P_PARSE_ITD=> 'Y'
1280 );
1281
1282
1283 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
1284
1285
1286 /*
1287 ** DETERMINE THE FACT TABLES YOU CHOOSE TO RUN THE DATABASE
1288 ** QUERY ON ( THIS STEP IS WHAT WE CALL MANUAL QUERY RE-WRITE).
1289 */
1290
1291 IF PJI_PMV_ENGINE.CONVERT_CLASSIFICATION (
1292 P_CLASSIFICATIONS,
1293 P_CLASS_CODES,
1294 P_VIEW_BY
1295 ) = 'N'
1296 THEN
1297 /*
1298 ** CODE THE SQL STATEMENT FOR ALL OF THE FOLLOWING CONDITIONS
1299 ** 1. CURRENT YEAR
1300 ** 2. PRIOR YEAR
1301 ** 3. SQL TO GENERATE ROWS WITH ZERO'S FOR THE VIEW BY DIMENSION
1302 ** BULK-COLLECT THE OUTPUT INTO A PL/SQL TABLE TO BE RETURNED TO
1303 ** PMV.
1304 */
1305
1306 SELECT PJI_REP_PBB2 (PROJECT_ID,
1307 PROJECT_NAME,
1308 PROJECT_NUMBER,
1309 PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_Of_Date,p_Period_Type),
1310 PRIMARY_CUSTOMER,
1311 PROJECT_TYPE,
1312 ORGANIZATION,
1313 PROJECT_MANAGER,
1314 TRUNC (PROJECT_START_DATE),
1315 TRUNC (PROJECT_CLOSE_DATE),
1316 SUM (ORIGINAL_BOOKINGS_AMOUNT),
1317 SUM (ADDITIONAL_BOOKINGS_AMOUNT),
1318 SUM (ADJUSTMENT_BOOKINGS_AMOUNT),
1319 -SUM (CANCELLED_BOOKINGS_AMOUNT),
1320 SUM (REVENUE),
1321 0,
1322 SUM (ITD_ORIGINAL_BOOKINGS_AMOUNT),
1323 SUM (ITD_ADDITIONAL_BOOKINGS_AMOUNT),
1324 SUM (ITD_ADJUSTMENT_BOOKINGS_AMOUNT),
1325 -SUM (ITD_CANCELLED_BOOKINGS_AMOUNT),
1326 SUM (ITD_REVENUE),
1327 0,
1328 SUM (BACKLOG_NOT_STARTED),
1329 SUM (ACTIVE_BACKLOG),
1330 SUM (DORMANT_BACKLOG),
1331 SUM (BEGINNING_BACKLOG),
1332 SUM (LOST_BACKLOG),
1333 SUM (
1334 BACKLOG_NOT_STARTED
1335 + ACTIVE_BACKLOG
1336 + DORMANT_BACKLOG),
1337 SUM (REVENUE_AT_RISK),
1338 0,
1339 0,
1340 0,
1341 0,
1342 0,
1343 0,
1344 0,
1345 0,
1346 0,
1347 0,
1348 0,
1349 0,
1350 0,
1351 0)
1352 BULK COLLECT INTO L_TOTAL_BOOKINGS_BACKLOG_TAB
1353 FROM (SELECT /*+ NO_MERGE(FCT) */
1354 FCT.PROJECT_ID,
1355 NULL PROJECT_NAME,
1356 FCT.PROJECT_ID PROJECT_NUMBER,
1357 NULL PRIMARY_CUSTOMER,
1358 NULL PROJECT_TYPE,
1359 FCT.ORGANIZATION_ID ORGANIZATION,
1360 NULL PROJECT_MANAGER,
1361 NULL PROJECT_START_DATE,
1362 NULL PROJECT_CLOSE_DATE,
1363 FCT.ORIGINAL_BOOKINGS_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
1364 FCT.ADDITIONAL_BOOKINGS_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
1365 FCT.ADJUSTMENT_BOOKINGS_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
1366 FCT.CANCELLED_BOOKINGS_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
1367 FCT.REVENUE REVENUE,
1368 0,
1369 FCT.ITD_ORIGINAL_BOOKINGS_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
1370 FCT.ITD_ADDITIONAL_BOOKINGS_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1371 FCT.ITD_ADJUSTMENT_BOOKINGS_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1372 FCT.ITD_CANCELLED_BOOKINGS_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
1373 FCT.ITD_REVENUE ITD_REVENUE,
1374 0,
1375 FCT.BACKLOG_NOT_STARTED BACKLOG_NOT_STARTED,
1376 FCT.DORMANT_BACKLOG DORMANT_BACKLOG,
1377 FCT.ACTIVE_BACKLOG ACTIVE_BACKLOG,
1378 FCT.BEGINNING_BACKLOG BEGINNING_BACKLOG,
1379 FCT.LOST_BACKLOG LOST_BACKLOG,
1380 FCT.REVENUE_AT_RISK REVENUE_AT_RISK,
1381 0,
1382 0,
1383 0,
1384 0,
1385 0,
1386 0,
1390 0,
1387 0,
1388 0,
1389 0,
1391 0,
1392 0,
1393 0,
1394 0
1395 FROM (SELECT /*+ ORDERED */
1396 FCT.PROJECT_ID PROJECT_ID,
1397 FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
1398 FCT.INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
1399 FCT.ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
1400 FCT.FUNDING_ADJUSTMENT_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
1401 FCT.CANCELLED_FUNDING_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
1402 FCT.REVENUE REVENUE,
1403 0,
1404 0 ITD_ORIGINAL_BOOKINGS_AMOUNT,
1405 0 ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1406 0 ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1407 0 ITD_CANCELLED_BOOKINGS_AMOUNT,
1408 0 ITD_REVENUE,
1409 0,
1410 0 BACKLOG_NOT_STARTED,
1411 0 DORMANT_BACKLOG,
1412 0 ACTIVE_BACKLOG,
1413 -(NVL(DORMANT_BACKLOG_START
1414 + DORMANT_BACKLOG_INACTIV
1415 + ACTIVE_BACKLOG, 0)) BEGINNING_BACKLOG,
1416 0 LOST_BACKLOG,
1417 0 REVENUE_AT_RISK,
1418 0,
1419 0,
1420 0,
1421 0,
1422 0,
1423 0,
1424 0,
1425 0,
1426 0,
1427 0,
1428 0,
1429 0,
1430 0,
1431 0
1432 FROM PJI_PMV_TIME_DIM_TMP TIME,
1433 PJI_PMV_ORGZ_DIM_TMP TORG,
1434 PJI_AC_PROJ_F FCT,
1435 PJI_PMV_ORG_DIM_TMP TOU
1436 WHERE FCT.PROJECT_ORG_ID = TOU.ID
1437 AND FCT.TIME_ID = TIME.ID
1438 AND TIME.ID IS NOT NULL
1439 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1440 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1441 AND FCT.PROJECT_ORGANIZATION_ID = TORG.ID
1442 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1443 UNION ALL
1444 SELECT /*+ ORDERED */
1445 FCT.PROJECT_ID PROJECT_ID,
1446 FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
1447 0 ORIGINAL_BOOKINGS_AMOUNT,
1448 0 ADDITIONAL_BOOKINGS_AMOUNT,
1449 0 ADJUSTMENT_BOOKINGS_AMOUNT,
1450 0 CANCELLED_BOOKINGS_AMOUNT,
1451 0 REVENUE,
1452 0,
1453 FCT.INITIAL_FUNDING_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
1454 FCT.ADDITIONAL_FUNDING_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1455 FCT.FUNDING_ADJUSTMENT_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1456 FCT.CANCELLED_FUNDING_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
1457 FCT.REVENUE ITD_REVENUE,
1458 0,
1459 DORMANT_BACKLOG_START BACKLOG_NOT_STARTED,
1460 DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG,
1461 ACTIVE_BACKLOG ACTIVE_BACKLOG,
1462 NVL(DORMANT_BACKLOG_START
1463 + DORMANT_BACKLOG_INACTIV
1467 0,
1464 + ACTIVE_BACKLOG,0) BEGINNING_BACKLOG,
1465 LOST_BACKLOG LOST_BACKLOG,
1466 REVENUE_AT_RISK REVENUE_AT_RISK,
1468 0,
1469 0,
1470 0,
1471 0,
1472 0,
1473 0,
1474 0,
1475 0,
1476 0,
1477 0,
1478 0,
1479 0,
1480 0
1481 FROM PJI_PMV_ITD_DIM_TMP TIME,
1482 PJI_PMV_ORGZ_DIM_TMP TORG,
1483 PJI_AC_PROJ_F FCT,
1484 PJI_PMV_ORG_DIM_TMP TOU
1485 WHERE FCT.PROJECT_ORG_ID = TOU.ID
1486 AND FCT.TIME_ID = TIME.ID
1487 AND TIME.ID IS NOT NULL
1488 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1489 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1490 AND FCT.PROJECT_ORGANIZATION_ID = TORG.ID
1491 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id) FCT
1492 WHERE 1 = 1) FCT
1493 GROUP BY PROJECT_ID,
1494 PROJECT_NAME,
1495 PROJECT_NUMBER,
1496 PRIMARY_CUSTOMER,
1497 PROJECT_TYPE,
1498 ORGANIZATION,
1499 PROJECT_MANAGER,
1500 PROJECT_START_DATE,
1501 PROJECT_CLOSE_DATE;
1502 ELSE
1503 BEGIN
1504 DELETE pji_pmv_prj_dim_tmp;
1505 INSERT INTO pji_pmv_prj_dim_tmp (id, name)
1506 SELECT DISTINCT prj.project_id, '-1' name
1507 FROM
1508 pji_project_classes PJM
1509 , pji_pmv_cls_dim_tmp PTM
1510 , pji_pmv_orgz_dim_tmp org
1511 , pa_projects_all prj
1512 WHERE
1513 pjm.project_class_id = ptm.id
1514 AND prj.project_id = pjm.project_id
1515 AND prj.carrying_out_organization_id = org.ID;
1516 END;
1517
1518 SELECT PJI_REP_PBB2 (PROJECT_ID,
1519 PROJECT_NAME,
1520 PROJECT_NUMBER,
1521 PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_Of_Date,p_Period_Type),
1522 PRIMARY_CUSTOMER,
1523 PROJECT_TYPE,
1524 ORGANIZATION,
1525 PROJECT_MANAGER,
1526 TRUNC (PROJECT_START_DATE),
1527 TRUNC (PROJECT_CLOSE_DATE),
1528 SUM (ORIGINAL_BOOKINGS_AMOUNT),
1529 SUM (ADDITIONAL_BOOKINGS_AMOUNT),
1530 SUM (ADJUSTMENT_BOOKINGS_AMOUNT),
1531 -SUM (CANCELLED_BOOKINGS_AMOUNT),
1532 SUM (REVENUE),
1533 0,
1534 SUM (ITD_ORIGINAL_BOOKINGS_AMOUNT),
1535 SUM (ITD_ADDITIONAL_BOOKINGS_AMOUNT),
1536 SUM (ITD_ADJUSTMENT_BOOKINGS_AMOUNT),
1537 -SUM (ITD_CANCELLED_BOOKINGS_AMOUNT),
1538 SUM (ITD_REVENUE),
1539 0,
1540 SUM (BACKLOG_NOT_STARTED),
1541 SUM (ACTIVE_BACKLOG),
1542 SUM (DORMANT_BACKLOG),
1543 SUM (BEGINNING_BACKLOG),
1544 SUM (LOST_BACKLOG),
1545 SUM (
1546 BACKLOG_NOT_STARTED
1547 + ACTIVE_BACKLOG
1548 + DORMANT_BACKLOG
1549 ),
1550 SUM (REVENUE_AT_RISK),
1551 0,
1552 0,
1556 0,
1553 0,
1554 0,
1555 0,
1557 0,
1558 0,
1559 0,
1560 0,
1561 0,
1562 0,
1563 0,
1564 0 )
1565 BULK COLLECT INTO L_TOTAL_BOOKINGS_BACKLOG_TAB
1566 FROM (SELECT /*+ NO_MERGE(FCT) */
1567 FCT.PROJECT_ID,
1568 NULL PROJECT_NAME,
1569 FCT.PROJECT_ID PROJECT_NUMBER,
1570 NULL PRIMARY_CUSTOMER,
1571 NULL PROJECT_TYPE,
1572 FCT.ORGANIZATION_ID ORGANIZATION,
1573 NULL PROJECT_MANAGER,
1574 NULL PROJECT_START_DATE,
1575 NULL PROJECT_CLOSE_DATE,
1576 FCT.ORIGINAL_BOOKINGS_AMOUNT ORIGINAL_BOOKINGS_AMOUNT,
1577 FCT.ADDITIONAL_BOOKINGS_AMOUNT ADDITIONAL_BOOKINGS_AMOUNT,
1578 FCT.ADJUSTMENT_BOOKINGS_AMOUNT ADJUSTMENT_BOOKINGS_AMOUNT,
1579 FCT.CANCELLED_BOOKINGS_AMOUNT CANCELLED_BOOKINGS_AMOUNT,
1580 FCT.REVENUE REVENUE,
1581 0,
1582 FCT.ITD_ORIGINAL_BOOKINGS_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
1583 FCT.ITD_ADDITIONAL_BOOKINGS_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1584 FCT.ITD_ADJUSTMENT_BOOKINGS_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1585 FCT.ITD_CANCELLED_BOOKINGS_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
1586 FCT.ITD_REVENUE ITD_REVENUE,
1587 0,
1588 FCT.BACKLOG_NOT_STARTED BACKLOG_NOT_STARTED,
1589 FCT.DORMANT_BACKLOG DORMANT_BACKLOG,
1590 FCT.ACTIVE_BACKLOG ACTIVE_BACKLOG,
1591 FCT.BEGINNING_BACKLOG BEGINNING_BACKLOG,
1592 FCT.LOST_BACKLOG LOST_BACKLOG,
1593 FCT.REVENUE_AT_RISK REVENUE_AT_RISK,
1594 0,
1595 0,
1596 0,
1597 0,
1598 0,
1599 0,
1600 0,
1601 0,
1602 0,
1603 0,
1604 0,
1605 0,
1606 0,
1607 0
1608 FROM (SELECT /*+ ORDERED */
1609 FCT.PROJECT_ID PROJECT_ID,
1610 FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
1611 FCT.INITIAL_FUNDING_AMOUNT
1612 ORIGINAL_BOOKINGS_AMOUNT,
1613 FCT.ADDITIONAL_FUNDING_AMOUNT
1614 ADDITIONAL_BOOKINGS_AMOUNT,
1615 FCT.FUNDING_ADJUSTMENT_AMOUNT
1616 ADJUSTMENT_BOOKINGS_AMOUNT,
1617 FCT.CANCELLED_FUNDING_AMOUNT
1618 CANCELLED_BOOKINGS_AMOUNT,
1619 FCT.REVENUE REVENUE,
1620 0,
1621 0 ITD_ORIGINAL_BOOKINGS_AMOUNT,
1622 0 ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1623 0 ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1624 0 ITD_CANCELLED_BOOKINGS_AMOUNT,
1625 0 ITD_REVENUE,
1626 0,
1627 0 BACKLOG_NOT_STARTED,
1628 0 DORMANT_BACKLOG,
1629 0 ACTIVE_BACKLOG,
1630 -(NVL(DORMANT_BACKLOG_START
1631 + DORMANT_BACKLOG_INACTIV
1632 + ACTIVE_BACKLOG, 0)) BEGINNING_BACKLOG,
1633 0 LOST_BACKLOG,
1634 0 REVENUE_AT_RISK,
1635 0,
1636 0,
1637 0,
1638 0,
1639 0,
1640 0,
1641 0,
1642 0,
1643 0,
1644 0,
1645 0,
1646 0,
1647 0,
1648 0
1649 FROM PJI_PMV_TIME_DIM_TMP TIME,
1650 PJI_PMV_PRJ_DIM_TMP TPRJ,
1651 PJI_AC_PROJ_F FCT,
1652 PJI_PMV_ORG_DIM_TMP TOU
1653 WHERE FCT.PROJECT_ORG_ID = TOU.ID
1654 AND FCT.TIME_ID = TIME.ID
1655 AND TIME.ID IS NOT NULL
1656 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1657 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1661 SELECT /*+ ORDERED */
1658 AND FCT.PROJECT_ID = TPRJ.ID
1659 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1660 UNION ALL
1662 FCT.PROJECT_ID PROJECT_ID,
1663 FCT.PROJECT_ORGANIZATION_ID ORGANIZATION_ID,
1664 0 ORIGINAL_BOOKINGS_AMOUNT,
1665 0 ADDITIONAL_BOOKINGS_AMOUNT,
1666 0 ADJUSTMENT_BOOKINGS_AMOUNT,
1667 0 CANCELLED_BOOKINGS_AMOUNT,
1668 0 REVENUE,
1669 0 XTD_TOTAL_BOOKINGS,
1670 FCT.INITIAL_FUNDING_AMOUNT ITD_ORIGINAL_BOOKINGS_AMOUNT,
1671 FCT.ADDITIONAL_FUNDING_AMOUNT ITD_ADDITIONAL_BOOKINGS_AMOUNT,
1672 FCT.FUNDING_ADJUSTMENT_AMOUNT ITD_ADJUSTMENT_BOOKINGS_AMOUNT,
1673 FCT.CANCELLED_FUNDING_AMOUNT ITD_CANCELLED_BOOKINGS_AMOUNT,
1674 FCT.REVENUE ITD_REVENUE,
1675 0,
1676 DORMANT_BACKLOG_START BACKLOG_NOT_STARTED,
1677 DORMANT_BACKLOG_INACTIV DORMANT_BACKLOG,
1678 ACTIVE_BACKLOG ACTIVE_BACKLOG,
1679 NVL(DORMANT_BACKLOG_START
1680 + DORMANT_BACKLOG_INACTIV
1681 + ACTIVE_BACKLOG, 0) BEGINNING_BACKLOG,
1682 LOST_BACKLOG LOST_BACKLOG,
1683 REVENUE_AT_RISK REVENUE_AT_RISK,
1684 0,
1685 0,
1686 0,
1687 0,
1688 0,
1689 0,
1690 0,
1691 0,
1692 0,
1693 0,
1694 0,
1695 0,
1696 0,
1697 0
1698 FROM PJI_PMV_ITD_DIM_TMP TIME,
1699 PJI_PMV_PRJ_DIM_TMP TPRJ,
1700 PJI_AC_PROJ_F FCT,
1701 PJI_PMV_ORG_DIM_TMP TOU
1702 WHERE FCT.PROJECT_ORG_ID = TOU.ID
1703 AND FCT.TIME_ID = TIME.ID
1704 AND TIME.ID IS NOT NULL
1705 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1706 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1707 AND FCT.PROJECT_ID = TPRJ.ID
1708 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id) FCT
1709 WHERE 1 = 1) FCT
1710 GROUP BY PROJECT_ID,
1714 PROJECT_TYPE,
1711 PROJECT_NAME,
1712 PROJECT_NUMBER,
1713 PRIMARY_CUSTOMER,
1715 ORGANIZATION,
1716 PROJECT_MANAGER,
1717 PROJECT_START_DATE,
1718 PROJECT_CLOSE_DATE;
1719 END IF;
1720
1721 /*
1722 ** IF THE REPORT IS REQUESTED FOR REVENUE AT RISK
1723 ** RECORDS ONLY, DELETE THE RECORDS WITH REVENUE AT RISK
1724 ** LESS THAN EQUAL TO ZERO.
1725 ** THE PL/SQL APPROACH WAS CHOOSEN BECAUSE OF PERFORMANCE
1726 ** REASONS.
1727 */
1728 IF P_RUN_REVENUE_AT_RISK = 'Y'
1729 THEN
1730 IF L_TOTAL_BOOKINGS_BACKLOG_TAB.COUNT > 0
1731 THEN
1732 FOR I IN
1733 L_TOTAL_BOOKINGS_BACKLOG_TAB.FIRST .. L_TOTAL_BOOKINGS_BACKLOG_TAB.LAST
1734 LOOP
1735 IF L_TOTAL_BOOKINGS_BACKLOG_TAB (I).REVENUE_AT_RISK <= 0
1736 THEN
1737 L_TOTAL_BOOKINGS_BACKLOG_TAB.DELETE (I);
1738 END IF;
1739 END LOOP;
1740 END IF;
1741 END IF;
1742
1743 /*
1744 ** THE FOLLOWING LOGIC IS CODED AS PL/SQL WOULD
1745 ** NOT RE-ORGANIZE THE TABLE OF RECORDS
1746 ** AFTER THE DELETE OPERATION.
1747 */
1748 L_ACTUAL_ROWCOUNT := L_TOTAL_BOOKINGS_BACKLOG_TAB.FIRST;
1749 L_REORG_ROWCOUNT := 1;
1750
1751 WHILE L_ACTUAL_ROWCOUNT <= L_TOTAL_BOOKINGS_BACKLOG_TAB.LAST
1752 LOOP
1753 X_TOTAL_BOOKINGS_BACKLOG_TAB.EXTEND;
1754 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT) :=
1755 L_TOTAL_BOOKINGS_BACKLOG_TAB (L_ACTUAL_ROWCOUNT);
1756 /*
1757 ** PLACE A CALL TO THE PA API'S TO GET THE PRIMARY
1758 ** CUSTOMER NAME AND PROJECT MANAGER NAME.
1759 */
1760 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PRIMARY_CUSTOMER :=
1761 PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME (
1762 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER
1763 );
1764 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_MANAGER :=
1765 PA_PROJECTS_MAINT_UTILS.GET_PROJECT_MANAGER_NAME (
1766 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER
1767 );
1768
1769 /*
1770 ** FETCH THE PROJECT ATTRIBUTES.
1771 */
1772
1773 SELECT NAME,
1774 SEGMENT1,
1775 PROJECT_TYPE,
1776 START_DATE,
1777 CLOSED_DATE
1778 INTO X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NAME,
1779 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER,
1780 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_TYPE,
1781 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_START_DATE,
1782 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_CLOSE_DATE
1783 FROM PA_PROJECTS_ALL
1784 WHERE PROJECT_ID =
1785 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).PROJECT_NUMBER;
1786
1787 /*
1788 ** FETCH THE ORGANIZATION NAME.
1789 */
1790 SELECT NAME
1791 INTO X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).ORGANIZATION
1792 FROM HR_ALL_ORGANIZATION_UNITS_TL
1793 WHERE LANGUAGE = USERENV ('LANG')
1794 AND ORGANIZATION_ID =
1795 X_TOTAL_BOOKINGS_BACKLOG_TAB (L_REORG_ROWCOUNT).ORGANIZATION;
1796
1797 /*
1798 ** INCREMENT ALL THE COUNTER VARIABLES.
1799 */
1800 L_ACTUAL_ROWCOUNT :=
1801 L_TOTAL_BOOKINGS_BACKLOG_TAB.NEXT (L_ACTUAL_ROWCOUNT);
1802 L_REORG_ROWCOUNT := L_REORG_ROWCOUNT
1803 + 1;
1804 END LOOP;
1805
1806 FOR i in 1..X_TOTAL_BOOKINGS_BACKLOG_TAB.COUNT
1807 LOOP
1808 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_TOTAL_BOOKINGS:=
1809 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ORIG_BKNG_AMT
1810 + X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ADDT_BKNG_AMT
1811 + X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ADJT_BKNG_AMT
1812 - X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_CANC_BKNG_AMT;
1813
1814 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_TOTAL_BOOKINGS:=
1815 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_ORIG_BKNG_AMT
1816 + X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_ADDT_BKNG_AMT
1820
1817 + X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_ADJT_BKNG_AMT
1818 - X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_CANC_BKNG_AMT;
1819 END LOOP;
1821 /*
1822 ** Calculation of Grand Totals
1823 */
1824
1825 FOR i in 1..X_TOTAL_BOOKINGS_BACKLOG_TAB.COUNT
1826 LOOP
1827 l_beginning_backlog:=l_beginning_backlog
1828 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).BEGINNING_BACKLOG,0);
1829 l_xtd_orig_bkng_amt :=l_xtd_orig_bkng_amt
1830 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ORIG_BKNG_AMT,0);
1831 l_xtd_addt_bkng_amt :=l_xtd_addt_bkng_amt
1832 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ADDT_BKNG_AMT,0);
1833 l_xtd_adjt_bkng_amt:= l_xtd_adjt_bkng_amt
1834 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_ADJT_BKNG_AMT,0);
1835 l_xtd_canc_bkng_amt:=l_xtd_canc_bkng_amt
1836 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_CANC_BKNG_AMT,0);
1837 l_xtd_revenue := l_xtd_revenue
1838 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_REVENUE,0);
1839 l_total_bookings_amount :=l_total_bookings_amount
1840 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).XTD_TOTAL_BOOKINGS,0);
1841 l_total_bookings_amount_itd :=l_total_bookings_amount_itd
1842 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ITD_TOTAL_BOOKINGS,0);
1843 l_backlog_not_started :=l_backlog_not_started
1844 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).BACKLOG_NOT_STARTED,0);
1845 l_active_backlog :=l_active_backlog
1846 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).ACTIVE_BACKLOG,0);
1847 l_dormant_backlog :=l_dormant_backlog
1848 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).DORMANT_BACKLOG,0);
1849 l_total_ending_backlog :=l_total_ending_backlog
1850 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).TOTAL_ENDING_BACKLOG,0);
1851 l_lost_backlog :=l_lost_backlog
1852 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).LOST_BACKLOG,0);
1853 l_revenue_at_risk :=l_revenue_at_risk
1854 + NVL(X_TOTAL_BOOKINGS_BACKLOG_TAB(i).REVENUE_AT_RISK,0);
1855 END LOOP;
1856
1857 FOR i in 1..X_TOTAL_BOOKINGS_BACKLOG_TAB.COUNT
1858 LOOP
1859 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_1:=l_xtd_orig_bkng_amt;
1860 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_2:=l_xtd_addt_bkng_amt;
1861 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_3:=l_total_bookings_amount;
1862 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_4:=l_total_bookings_amount_itd;
1863 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_5:=l_backlog_not_started;
1864 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_6:=l_active_backlog;
1865 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_7:=l_dormant_backlog;
1866 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_8:=l_total_ending_backlog;
1867 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_9:=l_lost_backlog;
1868 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_10:=l_revenue_at_risk;
1869
1870 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_11:=l_xtd_adjt_bkng_amt;
1871 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_12:=l_xtd_canc_bkng_amt;
1872 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_13:=l_xtd_revenue;
1873 X_TOTAL_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_14:=l_beginning_backlog;
1874
1875 END LOOP;
1876 COMMIT;
1877 RETURN X_TOTAL_BOOKINGS_BACKLOG_TAB;
1878 END PLSQLDRIVER_PJI_REP_PBB2;
1879
1880 /*
1881 ** ----------------------------------------------------------
1882 ** FUNCTION: PLSQLDRIVER_PJI_REP_PBB3
1883 ** THIS TABLE FUNCTION IS CALLED FROM SELECT STATEMENT
1884 ** GENERATED BY PJI ENGINE. THE FUNCTION RETURNS PL/SQL TABLE
1885 ** OF RECORDS WHICH HAVE TO BE DISPLAYED IN THE PMV REPORT.
1886 ** FOLLOWING ARE THE REPORTS TO WHICH THIS FUNCTION CATERS:
1887 ** 1. PJI_REP_PBB3 - PROJECT BOOKINGS ACTIVITY
1888 ** ----------------------------------------------------------
1889 */
1890
1891 FUNCTION PLSQLDRIVER_PBB3 (
1892 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
1893 P_ORGANIZATION IN VARCHAR2,
1894 P_CURRENCY_TYPE IN VARCHAR2,
1895 P_AS_OF_DATE IN NUMBER,
1896 P_PERIOD_TYPE IN VARCHAR2,
1897 P_VIEW_BY IN VARCHAR2,
1898 P_CLASSIFICATIONS IN VARCHAR2 DEFAULT NULL,
1899 P_CLASS_CODES IN VARCHAR2 DEFAULT NULL
1900 )
1901 RETURN PJI_REP_PBB3_TBL
1902 IS
1903 PRAGMA AUTONOMOUS_TRANSACTION;
1904 L_AC_BOOKINGS_BACKLOG_TAB PJI_REP_PBB3_TBL := PJI_REP_PBB3_TBL ();
1905 L_PARSE_CLASS_CODES VARCHAR2 (1);
1906
1907 l_Top_Organization_Name VARCHAR2(240);
1908 l_Top_Org_Index NUMBER:=0;
1909
1910 l_Original_Bookings NUMBER:=0;
1911 l_Additional_Bookings NUMBER:=0;
1912 l_Bookings_Adjustments NUMBER:=0;
1913 l_Cancellations NUMBER:=0;
1914 l_Total_Net_Bookings NUMBER:=0;
1915 l_Accrued_Revenue NUMBER:=0;
1916 l_Lost_Backlog NUMBER:=0;
1917 l_Lost_Backlog_Itd NUMBER:=0;
1921 l_Revenue_At_Risk_Itd NUMBER:=0;
1918 l_Backlog NUMBER:=0;
1919 l_Backlog_Itd NUMBER:=0;
1920 l_Revenue_At_Risk NUMBER:=0;
1922
1923 l_TO_Original_Bookings NUMBER:=0;
1924 l_TO_Additional_Bookings NUMBER:=0;
1925 l_TO_Bookings_Adjustments NUMBER:=0;
1926 l_TO_Cancellations NUMBER:=0;
1927 l_TO_Total_Net_Bookings NUMBER:=0;
1928 l_TO_Accrued_Revenue NUMBER:=0;
1929 l_TO_Lost_Backlog NUMBER:=0;
1930 l_TO_Lost_Backlog_Itd NUMBER:=0;
1931 l_TO_Backlog NUMBER:=0;
1932 l_TO_Backlog_Itd NUMBER:=0;
1933 l_TO_Revenue_At_Risk NUMBER:=0;
1934 l_TO_Revenue_At_Risk_Itd NUMBER:=0;
1935 l_curr_record_type_id NUMBER := 1;
1936
1937 BEGIN
1938 /*
1939 ** PLACE A CALL TO ALL THE PARSE API'S WHICH PARSE THE
1940 ** PARAMETERS PASSED BY PMV AND POPULATE ALL THE
1941 ** TEMPORARY TABLES.
1942 */
1943
1944
1945 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT, P_VIEW_BY);
1946 PJI_PMV_ENGINE.Convert_Organization(P_TOP_ORGANIZATION_ID => p_Organization,
1947 P_VIEW_BY => p_View_BY,
1948 p_Top_Organization_Name => l_Top_Organization_Name);
1949 PJI_PMV_ENGINE.CONVERT_TIME (
1950 P_AS_OF_DATE,
1951 P_PERIOD_TYPE,
1952 P_VIEW_BY,
1953 'Y',
1954 NULL,
1955 NULL,
1956 'Y'
1957 );
1958
1959 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
1960
1961 /*
1962 ** DETERMINE THE FACT TABLES YOU CHOOSE TO RUN THE DATABASE
1963 ** QUERY ON ( THIS STEP IS WHAT WE CALL MANUAL QUERY RE-WRITE).
1964 */
1965 IF PJI_PMV_ENGINE.CONVERT_CLASSIFICATION (
1966 P_CLASSIFICATIONS,
1967 P_CLASS_CODES,
1968 P_VIEW_BY
1969 ) = 'N'
1970 THEN
1971 /*
1972 ** CODE THE SQL STATEMENT FOR ALL OF THE FOLLOWING CONDITIONS
1973 ** 1. CURRENT YEAR
1974 ** 2. PRIOR YEAR
1975 ** 3. SQL TO GENERATE ROWS WITH ZERO'S FOR THE VIEW BY DIMENSION
1976 ** BULK-COLLECT THE OUTPUT INTO A PL/SQL TABLE TO BE RETURNED TO
1977 ** PMV.
1978 */
1979 SELECT PJI_REP_PBB3 (
1980 ORG_ID,
1981 ORGANIZATION_ID,
1982 TIME_ID,
1983 TIME_KEY,
1984 PROJECT_CLASS_ID,
1985 SUM (ORIGINAL_BOOKINGS),
1986 SUM (ADDITIONAL_BOOKINGS),
1987 SUM (BOOKINGS_ADJUSTMENTS),
1988 -SUM (CANCELLATIONS),
1989 SUM (TOTAL_NET_BOOKINGS),
1990 SUM (ACCRUED_REVENUE),
1991 SUM (LOST_BACKLOG),
1992 SUM (LOST_BACKLOG_ITD),
1993 SUM (BACKLOG),
1994 SUM (BACKLOG_ITD),
1995 SUM (REVENUE_AT_RISK),
1996 SUM (REVENUE_AT_RISK_ITD),
1997 0,
1998 0,
1999 0,
2000 0,
2001 0,
2002 0,
2003 0,
2004 0,
2005 0,
2006 0 )
2007 BULK COLLECT INTO L_AC_BOOKINGS_BACKLOG_TAB
2008 FROM (SELECT /*+ ORDERED */
2009 HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
2010 TIME.NAME TIME_ID,
2011 DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
2012 '-1' PROJECT_CLASS_ID,
2013 INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS,
2014 ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS,
2015 FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS,
2016 CANCELLED_FUNDING_AMOUNT CANCELLATIONS,
2017 INITIAL_FUNDING_AMOUNT
2018 + ADDITIONAL_FUNDING_AMOUNT
2019 + FUNDING_ADJUSTMENT_AMOUNT
2020 + CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS,
2021 REVENUE ACCRUED_REVENUE, LOST_BACKLOG LOST_BACKLOG,
2022 0 LOST_BACKLOG_ITD,
2023 DORMANT_BACKLOG_START
2024 + DORMANT_BACKLOG_INACTIV
2025 + ACTIVE_BACKLOG BACKLOG,
2026 0 BACKLOG_ITD, REVENUE_AT_RISK REVENUE_AT_RISK,
2027 0 REVENUE_AT_RISK_ITD,
2028 0,
2029 0,
2030 0,
2031 0,
2032 0,
2033 0,
2034 0,
2035 0,
2036 0,
2037 0
2038 FROM PJI_PMV_TIME_DIM_TMP TIME,
2042 WHERE FCT.ORG_ID = HOU.ID
2039 PJI_PMV_ORGZ_DIM_TMP HORG,
2040 PJI_AC_ORGO_F_MV FCT,
2041 PJI_PMV_ORG_DIM_TMP HOU
2043 AND FCT.ORGANIZATION_ID = HORG.ID
2044 AND FCT.TIME_ID = TIME.ID
2045 AND TIME.ID IS NOT NULL
2046 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
2047 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
2048 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2049 UNION ALL
2050 SELECT /*+ ORDERED */
2051 HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
2052 TIME.NAME TIME_ID,
2053 DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
2054 '-1' PROJECT_CLASS_ID, 0 ORIGINAL_BOOKINGS,
2055 0 ADDITIONAL_BOOKINGS, 0 BOOKINGS_ADJUSTMENTS,
2056 0 CANCELLATIONS, 0 TOTAL_NET_BOOKINGS,
2057 0 ACCRUED_REVENUE, 0 LOST_BACKLOG,
2058 LOST_BACKLOG LOST_BACKLOG_ITD, 0 BACKLOG,
2059 DORMANT_BACKLOG_START
2060 + DORMANT_BACKLOG_INACTIV
2061 + ACTIVE_BACKLOG BACKLOG_ITD,
2062 0 REVENUE_AT_RISK,
2063 REVENUE_AT_RISK REVENUE_AT_RISK_ITD,
2064 0,
2065 0,
2066 0,
2067 0,
2068 0,
2069 0,
2070 0,
2071 0,
2072 0,
2073 0
2074 FROM PJI_PMV_ITD_DIM_TMP TIME,
2075 PJI_PMV_ORGZ_DIM_TMP HORG,
2076 PJI_AC_ORGO_F_MV FCT,
2077 PJI_PMV_ORG_DIM_TMP HOU
2078 WHERE FCT.ORG_ID = HOU.ID
2079 AND FCT.ORGANIZATION_ID = HORG.ID
2080 AND FCT.TIME_ID = TIME.ID
2081 AND TIME.ID IS NOT NULL
2082 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
2083 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
2084 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2085 UNION ALL
2086 SELECT NAME ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
2087 -1 TIME_KEY, '-1' PROJECT_CLASS_ID,
2088 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2089 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2090 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2091 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2092 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2093 0 REVENUE_AT_RISK_ITD,
2094 0,0,0,0,0,0,0,0,0,0
2095 FROM PJI_PMV_ORG_DIM_TMP
2096 WHERE NAME <> '-1'
2097 UNION ALL
2098 SELECT '-1' ORG_ID, NAME ORGANIZATION_ID, '-1' TIME_ID,
2099 -1 TIME_KEY, '-1' PROJECT_CLASS_ID,
2100 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2101 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2102 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2103 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2104 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2105 0 REVENUE_AT_RISK_ITD,
2106 0,0,0,0,0,0,0,0,0,0
2107 FROM PJI_PMV_ORGZ_DIM_TMP
2108 WHERE NAME <> '-1'
2109 UNION ALL
2110 SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, NAME TIME_ID,
2111 ID TIME_KEY, '-1' PROJECT_CLASS_ID,
2112 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2113 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2114 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2115 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2116 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2117 0 REVENUE_AT_RISK_ITD,
2118 0,0,0,0,0,0,0,0,0,0
2119 FROM PJI_PMV_TIME_DIM_TMP
2120 WHERE NAME <> '-1')
2121 GROUP BY ORG_ID, ORGANIZATION_ID, TIME_KEY, TIME_ID, PROJECT_CLASS_ID;
2122 ELSE
2123 /*
2124 ** CODE THE SQL STATEMENT FOR ALL OF THE FOLLOWING CONDITIONS
2125 ** 1. CURRENT YEAR
2126 ** 2. PRIOR YEAR
2127 ** 3. SQL TO GENERATE ROWS WITH ZERO'S FOR THE VIEW BY DIMENSION
2128 */
2129 SELECT PJI_REP_PBB3 (
2130 ORG_ID,
2131 ORGANIZATION_ID,
2132 TIME_ID,
2133 TIME_KEY,
2134 PROJECT_CLASS_ID,
2135 SUM (ORIGINAL_BOOKINGS),
2136 SUM (ADDITIONAL_BOOKINGS),
2137 SUM (BOOKINGS_ADJUSTMENTS),
2138 -SUM (CANCELLATIONS),
2139 SUM (TOTAL_NET_BOOKINGS),
2140 SUM (ACCRUED_REVENUE),
2141 SUM (LOST_BACKLOG),
2142 SUM (LOST_BACKLOG_ITD),
2146 SUM (REVENUE_AT_RISK_ITD),
2143 SUM (BACKLOG),
2144 SUM (BACKLOG_ITD),
2145 SUM (REVENUE_AT_RISK),
2147 0,
2148 0,
2149 0,
2150 0,
2151 0,
2152 0,
2153 0,
2154 0,
2155 0,
2156 0 )
2157 BULK COLLECT INTO L_AC_BOOKINGS_BACKLOG_TAB
2158 FROM (SELECT /*+ ORDERED */
2159 HOU.NAME ORG_ID,
2160 HORG.NAME ORGANIZATION_ID,
2161 TIME.NAME TIME_ID,
2162 DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
2163 CLS.NAME PROJECT_CLASS_ID,
2164 INITIAL_FUNDING_AMOUNT ORIGINAL_BOOKINGS,
2165 ADDITIONAL_FUNDING_AMOUNT ADDITIONAL_BOOKINGS,
2166 FUNDING_ADJUSTMENT_AMOUNT BOOKINGS_ADJUSTMENTS,
2167 CANCELLED_FUNDING_AMOUNT CANCELLATIONS,
2168 INITIAL_FUNDING_AMOUNT
2169 + ADDITIONAL_FUNDING_AMOUNT
2170 + FUNDING_ADJUSTMENT_AMOUNT
2171 + CANCELLED_FUNDING_AMOUNT TOTAL_NET_BOOKINGS,
2172 REVENUE ACCRUED_REVENUE, LOST_BACKLOG LOST_BACKLOG,
2173 0 LOST_BACKLOG_ITD,
2174 DORMANT_BACKLOG_START
2175 + DORMANT_BACKLOG_INACTIV
2176 + ACTIVE_BACKLOG BACKLOG,
2177 0 BACKLOG_ITD, REVENUE_AT_RISK REVENUE_AT_RISK,
2178 0 REVENUE_AT_RISK_ITD,
2179 0,
2180 0,
2181 0,
2182 0,
2183 0,
2184 0,
2185 0,
2186 0,
2187 0,
2188 0
2189 FROM PJI_PMV_TIME_DIM_TMP TIME,
2190 PJI_PMV_ORGZ_DIM_TMP HORG,
2191 PJI_PMV_CLS_DIM_TMP CLS,
2192 PJI_AC_CLSO_F_MV FCT,
2193 PJI_PMV_ORG_DIM_TMP HOU
2194 WHERE FCT.PROJECT_ORG_ID = HOU.ID
2195 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
2196 AND FCT.TIME_ID = TIME.ID
2197 AND TIME.ID IS NOT NULL
2198 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
2199 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
2200 AND FCT.PROJECT_CLASS_ID = CLS.ID
2201 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2202 UNION ALL
2203 SELECT /*+ ORDERED */
2204 HOU.NAME ORG_ID, HORG.NAME ORGANIZATION_ID,
2205 TIME.NAME TIME_ID,
2206 DECODE (P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY,
2207 CLS.NAME PROJECT_CLASS_ID, 0 ORIGINAL_BOOKINGS,
2208 0 ADDITIONAL_BOOKINGS, 0 BOOKINGS_ADJUSTMENTS,
2209 0 CANCELLATIONS, 0 TOTAL_NET_BOOKINGS,
2210 0 ACCRUED_REVENUE, 0 LOST_BACKLOG,
2211 LOST_BACKLOG LOST_BACKLOG_ITD, 0 BACKLOG,
2212 DORMANT_BACKLOG_START
2213 + DORMANT_BACKLOG_INACTIV
2214 + ACTIVE_BACKLOG BACKLOG_ITD,
2215 0 REVENUE_AT_RISK,
2216 REVENUE_AT_RISK REVENUE_AT_RISK_ITD,
2217 0,
2218 0,
2219 0,
2220 0,
2221 0,
2222 0,
2223 0,
2224 0,
2225 0,
2226 0
2227 FROM PJI_PMV_ITD_DIM_TMP TIME,
2228 PJI_PMV_ORGZ_DIM_TMP HORG,
2229 PJI_PMV_CLS_DIM_TMP CLS,
2230 PJI_AC_CLSO_F_MV FCT,
2231 PJI_PMV_ORG_DIM_TMP HOU
2232 WHERE FCT.PROJECT_ORG_ID = HOU.ID
2236 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
2233 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
2234 AND FCT.TIME_ID = TIME.ID
2235 AND TIME.ID IS NOT NULL
2237 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
2238 AND FCT.PROJECT_CLASS_ID = CLS.ID
2239 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2240 UNION ALL
2241 SELECT NAME ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
2242 -1 TIME_KEY, '-1' PROJECT_CLASS_ID,
2243 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2244 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2245 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2246 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2247 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2248 0 REVENUE_AT_RISK_ITD,
2249 0,
2250 0,
2251 0,
2252 0,
2253 0,
2254 0,
2255 0,
2256 0,
2257 0,
2258 0
2259 FROM PJI_PMV_ORG_DIM_TMP
2260 WHERE NAME <> '-1'
2261 UNION ALL
2262 SELECT '-1' ORG_ID, NAME ORGANIZATION_ID, '-1' TIME_ID,
2263 -1 TIME_KEY, '-1' PROJECT_CLASS_ID,
2264 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2265 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2266 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2267 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2268 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2269 0 REVENUE_AT_RISK_ITD,
2270 0,
2271 0,
2272 0,
2273 0,
2274 0,
2275 0,
2276 0,
2277 0,
2278 0,
2279 0
2280 FROM PJI_PMV_ORGZ_DIM_TMP
2281 WHERE NAME <> '-1'
2282 UNION ALL
2283 SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, NAME TIME_ID,
2284 ID TIME_KEY, '-1' PROJECT_CLASS_ID,
2285 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2286 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2287 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2288 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2289 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2290 0 REVENUE_AT_RISK_ITD,
2291 0,
2292 0,
2293 0,
2294 0,
2295 0,
2296 0,
2297 0,
2298 0,
2299 0,
2300 0
2301 FROM PJI_PMV_TIME_DIM_TMP
2302 WHERE NAME <> '-1'
2303 UNION ALL
2304 SELECT '-1' ORG_ID, '-1' ORGANIZATION_ID, '-1' TIME_ID,
2305 -1 TIME_KEY, NAME PROJECT_CLASS_ID,
2306 0 ORIGINAL_BOOKINGS, 0 ADDITIONAL_BOOKINGS,
2307 0 BOOKINGS_ADJUSTMENTS, 0 CANCELLATIONS,
2308 0 TOTAL_NET_BOOKINGS, 0 ACCRUED_REVENUE,
2309 0 LOST_BACKLOG, 0 LOST_BACKLOG_ITD, 0 BACKLOG,
2310 0 BACKLOG_ITD, 0 REVENUE_AT_RISK,
2311 0 REVENUE_AT_RISK_ITD,
2312 0,
2313 0,
2314 0,
2315 0,
2316 0,
2317 0,
2318 0,
2319 0,
2320 0,
2321 0
2322 FROM PJI_PMV_CLS_DIM_TMP
2323 WHERE NAME <> '-1') FACT
2324 GROUP BY ORG_ID, ORGANIZATION_ID, TIME_KEY, TIME_ID, PROJECT_CLASS_ID;
2325 END IF;
2326
2327 FOR i in 1..l_ac_bookings_backlog_tab.COUNT
2328 LOOP
2329 l_ac_bookings_backlog_tab(i).BACKLOG:=
2330 NVL(l_ac_bookings_backlog_tab(i).BACKLOG_ITD,0)
2331 - NVL(l_ac_bookings_backlog_tab(i).BACKLOG,0);
2332 l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK:=
2333 NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK_ITD,0)
2334 - NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK,0);
2335 l_ac_bookings_backlog_tab(i).LOST_BACKLOG:=
2336 NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG_ITD,0)
2337 - NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG,0);
2338
2339 IF p_View_By = 'OG' THEN
2340 IF l_ac_bookings_backlog_tab(i).ORGANIZATION_ID = l_Top_Organization_Name THEN
2341 l_Top_Org_Index:=i;
2342
2343 l_TO_Original_Bookings := NVL(l_ac_bookings_backlog_tab(i).ORIGINAL_BOOKINGS,0);
2344 l_TO_Additional_Bookings := NVL(l_ac_bookings_backlog_tab(i).ADDITIONAL_BOOKINGS,0);
2345 l_TO_Bookings_Adjustments := NVL(l_ac_bookings_backlog_tab(i).BOOKINGS_ADJUSTMENTS,0);
2346 l_TO_Cancellations := NVL(l_ac_bookings_backlog_tab(i).CANCELLATIONS,0);
2347 l_TO_Total_Net_Bookings := NVL(l_ac_bookings_backlog_tab(i).TOTAL_NET_BOOKINGS,0);
2348 l_TO_Accrued_Revenue := NVL(l_ac_bookings_backlog_tab(i).ACCRUED_REVENUE,0);
2349 l_TO_Lost_Backlog := NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG,0);
2350 l_TO_Lost_Backlog_Itd := NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG_ITD,0);
2351 l_TO_Backlog := NVL(l_ac_bookings_backlog_tab(i).BACKLOG,0);
2352 l_TO_Backlog_Itd := NVL(l_ac_bookings_backlog_tab(i).BACKLOG_ITD,0);
2353 l_TO_Revenue_At_Risk := NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK,0);
2354 l_TO_Revenue_At_Risk_Itd := NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK_ITD,0);
2355
2356 ELSE
2357 l_Original_Bookings :=l_Original_Bookings
2361 l_Bookings_Adjustments := l_Bookings_Adjustments
2358 + NVL(l_ac_bookings_backlog_tab(i).ORIGINAL_BOOKINGS,0);
2359 l_Additional_Bookings := l_Additional_Bookings
2360 + NVL(l_ac_bookings_backlog_tab(i).ADDITIONAL_BOOKINGS,0);
2362 + NVL(l_ac_bookings_backlog_tab(i).BOOKINGS_ADJUSTMENTS,0);
2363 l_Cancellations := l_Cancellations
2364 + NVL(l_ac_bookings_backlog_tab(i).CANCELLATIONS,0);
2365 l_Total_Net_Bookings := l_Total_Net_Bookings
2366 + NVL(l_ac_bookings_backlog_tab(i).TOTAL_NET_BOOKINGS,0);
2367 l_Accrued_Revenue := l_Accrued_Revenue
2368 + NVL(l_ac_bookings_backlog_tab(i).ACCRUED_REVENUE,0);
2369 l_Lost_Backlog := l_Lost_Backlog
2370 + NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG,0);
2371 l_Lost_Backlog_Itd := l_Lost_Backlog_Itd
2372 + NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG_ITD,0);
2373 l_Backlog := l_Backlog
2374 + NVL(l_ac_bookings_backlog_tab(i).BACKLOG,0);
2375 l_Backlog_Itd := l_Backlog_Itd
2376 + NVL(l_ac_bookings_backlog_tab(i).BACKLOG_ITD,0);
2377 l_Revenue_At_Risk := l_Revenue_At_Risk
2378 + NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK,0);
2379 l_Revenue_At_Risk_Itd := l_Revenue_At_Risk_Itd
2380 + NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK_ITD,0);
2381 END IF;
2382 ELSE
2383 l_Original_Bookings :=l_Original_Bookings
2384 + NVL(l_ac_bookings_backlog_tab(i).ORIGINAL_BOOKINGS,0);
2385 l_Additional_Bookings := l_Additional_Bookings
2386 + NVL(l_ac_bookings_backlog_tab(i).ADDITIONAL_BOOKINGS,0);
2387 l_Bookings_Adjustments := l_Bookings_Adjustments
2388 + NVL(l_ac_bookings_backlog_tab(i).BOOKINGS_ADJUSTMENTS,0);
2389 l_Cancellations := l_Cancellations
2390 + NVL(l_ac_bookings_backlog_tab(i).CANCELLATIONS,0);
2391 l_Total_Net_Bookings := l_Total_Net_Bookings
2392 + NVL(l_ac_bookings_backlog_tab(i).TOTAL_NET_BOOKINGS,0);
2393 l_Accrued_Revenue := l_Accrued_Revenue
2394 + NVL(l_ac_bookings_backlog_tab(i).ACCRUED_REVENUE,0);
2395 l_Lost_Backlog := l_Lost_Backlog
2396 + NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG,0);
2397 l_Lost_Backlog_Itd := l_Lost_Backlog_Itd
2398 + NVL(l_ac_bookings_backlog_tab(i).LOST_BACKLOG_ITD,0);
2399 l_Backlog := l_Backlog
2400 + NVL(l_ac_bookings_backlog_tab(i).BACKLOG,0);
2401 l_Backlog_Itd := l_Backlog_Itd
2402 + NVL(l_ac_bookings_backlog_tab(i).BACKLOG_ITD,0);
2403 l_Revenue_At_Risk := l_Revenue_At_Risk
2404 + NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK,0);
2405 l_Revenue_At_Risk_Itd := l_Revenue_At_Risk_Itd
2406 + NVL(l_ac_bookings_backlog_tab(i).REVENUE_AT_RISK_ITD,0);
2407 END IF;
2408 END LOOP;
2409
2410 IF p_View_By = 'OG' THEN
2411
2412 l_ac_bookings_backlog_tab(l_Top_Org_Index).ORIGINAL_BOOKINGS
2413 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ORIGINAL_BOOKINGS,0)
2414 - l_Original_Bookings;
2415 l_ac_bookings_backlog_tab(l_Top_Org_Index).ADDITIONAL_BOOKINGS
2416 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ADDITIONAL_BOOKINGS,0)
2417 - l_Additional_Bookings;
2418 l_ac_bookings_backlog_tab(l_Top_Org_Index).BOOKINGS_ADJUSTMENTS
2419 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BOOKINGS_ADJUSTMENTS,0)
2420 - l_Bookings_Adjustments;
2421 l_ac_bookings_backlog_tab(l_Top_Org_Index).CANCELLATIONS
2422 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).CANCELLATIONS,0)
2423 - l_Cancellations;
2424 l_ac_bookings_backlog_tab(l_Top_Org_Index).TOTAL_NET_BOOKINGS
2425 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).TOTAL_NET_BOOKINGS,0)
2426 - l_Total_Net_Bookings;
2427 l_ac_bookings_backlog_tab(l_Top_Org_Index).ACCRUED_REVENUE
2428 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ACCRUED_REVENUE,0)
2429 - l_Accrued_Revenue ;
2430 l_ac_bookings_backlog_tab(l_Top_Org_Index).LOST_BACKLOG
2431 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).LOST_BACKLOG,0)
2432 - l_Lost_Backlog;
2433 l_ac_bookings_backlog_tab(l_Top_Org_Index).LOST_BACKLOG_ITD
2434 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).LOST_BACKLOG_ITD,0)
2435 - l_Lost_Backlog_Itd;
2436 l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG
2437 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG,0)
2438 - l_Backlog;
2439 l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG_ITD
2440 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG_ITD,0)
2441 - l_Backlog_Itd;
2442 l_ac_bookings_backlog_tab(l_Top_Org_Index).REVENUE_AT_RISK
2443 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).REVENUE_AT_RISK,0)
2444 - l_Revenue_At_Risk;
2445 l_ac_bookings_backlog_tab(l_Top_Org_Index).REVENUE_AT_RISK_ITD
2446 :=NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).REVENUE_AT_RISK_ITD,0)
2447 - l_Revenue_At_Risk_Itd;
2448
2449 IF
2450 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG,0)=0 AND
2451 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ORIGINAL_BOOKINGS,0)=0 AND
2452 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ADDITIONAL_BOOKINGS,0)=0 AND
2453 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BOOKINGS_ADJUSTMENTS,0)=0 AND
2454 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).CANCELLATIONS,0)=0 AND
2455 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).TOTAL_NET_BOOKINGS,0)=0 AND
2456 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).ACCRUED_REVENUE,0)=0 AND
2457 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).REVENUE_AT_RISK_ITD,0)=0 AND
2458 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).LOST_BACKLOG_ITD,0)=0 AND
2459 NVL(l_ac_bookings_backlog_tab(l_Top_Org_Index).BACKLOG_ITD,0)=0
2460 THEN
2461 l_ac_bookings_backlog_tab.DELETE(l_Top_Org_Index);
2462 END IF;
2463
2464 l_Original_Bookings :=l_TO_Original_Bookings;
2465 l_Additional_Bookings :=l_TO_Additional_Bookings;
2466 l_Bookings_Adjustments :=l_TO_Bookings_Adjustments;
2467 l_Cancellations :=l_TO_Cancellations;
2468 l_Total_Net_Bookings :=l_TO_Total_Net_Bookings;
2469 l_Accrued_Revenue :=l_TO_Accrued_Revenue;
2470 l_Lost_Backlog_Itd :=l_TO_Lost_Backlog_Itd;
2471 l_Backlog :=l_TO_Backlog;
2472 l_Backlog_Itd :=l_TO_Backlog_Itd;
2473 l_Revenue_At_Risk_Itd :=l_TO_Revenue_At_Risk_Itd;
2474
2475 END IF;
2476
2477 IF L_AC_BOOKINGS_BACKLOG_TAB.COUNT > 0 THEN
2478 FOR I IN L_AC_BOOKINGS_BACKLOG_TAB.FIRST..L_AC_BOOKINGS_BACKLOG_TAB.LAST
2479 LOOP
2480 IF L_AC_BOOKINGS_BACKLOG_TAB.EXISTS(i) THEN
2481 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_1:=l_Backlog;
2482 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_2:=l_Original_Bookings;
2483 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_3:=l_Additional_Bookings;
2484 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_4:=l_Bookings_Adjustments;
2485 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_5:=l_Cancellations;
2486 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_6:=l_Total_Net_Bookings;
2487 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_7:=l_Accrued_Revenue;
2488 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_8:=l_Revenue_At_Risk_Itd;
2489 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_9:=l_Lost_Backlog_Itd;
2490 L_AC_BOOKINGS_BACKLOG_TAB(i).PJI_REP_TOTAL_10:=l_Backlog_Itd;
2491 END IF;
2492 END LOOP;
2493 END IF;
2494 COMMIT;
2495
2496 RETURN L_AC_BOOKINGS_BACKLOG_TAB;
2497 END PLSQLDRIVER_PBB3;
2498
2499 END PJI_PMV_BOOKINGS_BACKLOG;