[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_COST
Source
1 PACKAGE BODY PJI_PMV_COST AS
2 /* $Header: PJIRF06B.pls 120.7 2006/01/24 16:53:53 appldev noship $ */
3
4
5 G_Report_Cost_Type VARCHAR2(2);
6
7
8 /*
9 ** Contract Projects Cost Summary
10 */
11
12 PROCEDURE GET_SQL_PJI_REP_PC10(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
13 , x_PMV_Sql OUT NOCOPY VARCHAR2
14 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
15 IS
16 l_Err_Message VARCHAR2(3200);
17 l_PMV_Sql VARCHAR2(3200);
18 BEGIN
19
20 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
21 , P_SELECT_LIST =>'
22 COST "PJI_REP_MSR_1"
23 , COST "PJI_REP_MSR_19"
24 , CT_COST "PJI_REP_MSR_2"
25 , COST_CHANGE_PRCNT "PJI_REP_MSR_3"
26 , BILL_COST "PJI_REP_MSR_4"
27 , BILL_COST "PJI_REP_MSR_20"
28 , CT_BILL_COST "PJI_REP_MSR_5"
29 , BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
30 , BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
31 , BILL_PRCNT_OF_COST "PJI_REP_MSR_23"
32 , CT_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
33 , PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
34 , BUDGET "PJI_REP_MSR_10"
35 , CT_BUDGET "PJI_REP_MSR_11"
36 , BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
37 , BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
38 , BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_21"
39 , CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
40 , CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_22"
41 , CHANGE "PJI_REP_MSR_15"
42 , NON_BILL_COST "PJI_REP_MSR_16"
43 , NON_BILL_COST "PJI_REP_MSR_24"
44 , CT_NON_BILL_COST "PJI_REP_MSR_17"
45 , NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"
46 , FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
47 , FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
48 , FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
49 , FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"
50 , FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5"
51 , FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6"
52 , FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7"
53 , FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8"
54 , FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9"
55 , FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10"
56 , FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11"
57 , FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12"
58 , FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_13"
59 , FACT.PJI_REP_TOTAL_14 "PJI_REP_TOTAL_14"
60 , FACT.PJI_REP_TOTAL_15 "PJI_REP_TOTAL_15"
61 , FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_16"
62 , FACT.PJI_REP_TOTAL_17 "PJI_REP_TOTAL_17"
63 , FACT.PJI_REP_TOTAL_18 "PJI_REP_TOTAL_18"
64 , FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_19"
65 , FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_20"
66 , FACT.PJI_REP_TOTAL_13 "PJI_REP_TOTAL_21"
67 , FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_23"
68 , FACT.PJI_REP_TOTAL_16 "PJI_REP_TOTAL_24"'
69 , P_SQL_STATEMENT => x_PMV_Sql
70 , P_PMV_OUTPUT => x_PMV_Output
71 , p_Region_Code => 'PJI_REP_PC10'
72 , p_PLSQL_Driver => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC10'
73 , p_PLSQL_Driver_Params =>
74 ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
75 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
76 ', <<CURRENCY+FII_CURRENCIES>>'||
77 ', <<AS_OF_DATE>>'||
78 ', <<TIME_COMPARISON_TYPE>>'||
79 ', <<PERIOD_TYPE>>'||
80 ', <<VIEW_BY>>'||
81 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
82 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> '||
83 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES>> '||
84 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES>> '||
85 ', <<PROJECT WORK TYPE+PJI_WORK_TYPES>> '
86 );
87
88 END GET_SQL_PJI_REP_PC10;
89
90
91 /*
92 ** Contract Projects Cost Trend
93 */
94
95 PROCEDURE Get_SQL_PJI_REP_PC11 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
96 , x_PMV_Sql OUT NOCOPY VARCHAR2
97 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
98 IS
99 BEGIN
100 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
101 , P_SQL_STATEMENT => x_PMV_Sql
102 , P_SELECT_LIST =>
103 'FACT.COST "PJI_REP_MSR_1"
104 ,FACT.COST "PJI_REP_MSR_19"
105 ,FACT.ct_COST "PJI_REP_MSR_2"
106 ,FACT.ct_COST "PJI_REP_MSR_20"
107 ,FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
108 ,FACT.BILL_COST "PJI_REP_MSR_4"
109 ,FACT.BILL_COST "PJI_REP_MSR_21"
110 ,FACT.ct_BILL_COST "PJI_REP_MSR_5"
111 ,FACT.ct_BILL_COST "PJI_REP_MSR_22"
112 ,FACT.BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
113 ,FACT.BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
114 ,FACT.ct_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
115 ,FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
116 ,FACT.BUDGET "PJI_REP_MSR_10"
117 ,FACT.ct_BUDGET "PJI_REP_MSR_11"
118 ,FACT.BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
119 ,FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
120 ,FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_23"
121 ,FACT.ct_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
122 ,FACT.ct_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_24"
123 ,FACT.CHANGE "PJI_REP_MSR_15"
124 ,FACT.NON_BILL_COST "PJI_REP_MSR_16"
125 ,FACT.ct_NON_BILL_COST "PJI_REP_MSR_17"
126 ,FACT.NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"'
127 , P_PMV_OUTPUT => x_PMV_Output
128 , P_REGION_CODE => 'PJI_REP_PC11'
129 , P_PLSQL_DRIVER => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC11'
130 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
131 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
132 ', <<CURRENCY+FII_CURRENCIES>>'||
133 ', <<AS_OF_DATE>>'||
134 ', <<PERIOD_TYPE>>'||
135 ', <<VIEW_BY>>'||
136 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
137 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
138 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES>> '||
139 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES>> '||
140 ', <<PROJECT WORK TYPE+PJI_WORK_TYPES>> ');
141
142 END Get_SQL_PJI_REP_PC11;
143
144
145 /*
146 ** Contract Project Cost Detail
147 */
148
149
150 PROCEDURE GET_SQL_PJI_REP_PC13(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
151 , x_PMV_Sql OUT NOCOPY VARCHAR2
152 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
153 IS
154 l_Err_Message VARCHAR2(3200);
155 l_PMV_Sql VARCHAR2(3200);
156 BEGIN
157
158 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
159 , P_SELECT_LIST =>
160 ' FACT.PROJECT_ID "PJI_REP_MSR_18"
161 , FACT.PROJECT_NAME "VIEWBY"
162 , FACT.PROJECT_NUMBER "PJI_REP_MSR_2"
163 , FACT.URL_PARAMETERS01 "PJI_REP_MSR_10"
164 , FACT.URL_PARAMETERS01 "PJI_REP_MSR_30"
165 , FACT.PRIMARY_CUSTOMER_NAME "PJI_REP_MSR_3"
166 , FACT.PROJECT_TYPE "PJI_REP_MSR_4"
167 , FACT.ORGANIZATION_NAME "PJI_REP_MSR_5"
168 , FACT.PERSON_MANAGER_NAME "PJI_REP_MSR_6"
169 , FACT.COST "PJI_REP_MSR_8"
170 , FACT.BILL_COST "PJI_REP_MSR_16"
171 , FACT.BILL_COST_PRCNT_OF_COST "PJI_REP_MSR_17"
172 , FACT.BUDGET "PJI_REP_MSR_12"
173 , FACT.BILL_PRCNT_OF_BUDGET_COST "PJI_REP_MSR_13"
174 , FACT.NON_BILL_COST "PJI_REP_MSR_14"
175 , FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1"
176 , FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2"
177 , FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3"
178 , FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4"
179 , FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5"
180 , FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6" '
181 , P_SQL_STATEMENT => x_PMV_Sql
182 , P_PMV_OUTPUT => x_PMV_Output
183 , p_Region_Code => 'PJI_REP_PC13'
184 , p_PLSQL_Driver => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC13'
185 , p_PLSQL_Driver_Params => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
186 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
187 ', <<CURRENCY+FII_CURRENCIES>>'||
188 ', <<AS_OF_DATE>>'||
189 ', <<PERIOD_TYPE>>'||
190 ', <<VIEW_BY>>'||
191 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
192 ', <<PROJECT CLASSIFICATION+CLASS_CODE>> '||
193 ', <<PROJECT+PJI_PROJECTS>> '||
194 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES>> '||
195 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES>> '||
196 ', <<PROJECT WORK TYPE+PJI_WORK_TYPES>> '
197 );
198 END GET_SQL_PJI_REP_PC13;
199
200 /*
201 ** Capital Project Cost Cumulative Trend
202 */
203
204
205 PROCEDURE Get_SQL_PJI_REP_PC12(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
206 , x_PMV_Sql OUT NOCOPY VARCHAR2
207 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
208 IS
209 l_PMV_Rec BIS_QUERY_ATTRIBUTES;
210 BEGIN
211 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
212 , P_SELECT_LIST =>
213 ' FACT.COST "PJI_REP_MSR_1"
214 , FACT.COST "PJI_REP_MSR_19"
215 , FACT.CT_COST "PJI_REP_MSR_2"
216 , FACT.CT_COST "PJI_REP_MSR_20"
217 , FACT.COST_CHANGE_PRCNT "PJI_REP_MSR_3"
218 , FACT.BILL_COST "PJI_REP_MSR_4"
219 , FACT.BILL_COST "PJI_REP_MSR_21"
220 , FACT.CT_BILL_COST "PJI_REP_MSR_5"
221 , FACT.CT_BILL_COST "PJI_REP_MSR_22"
222 , FACT.BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_6"
223 , FACT.BILL_PRCNT_OF_COST "PJI_REP_MSR_7"
224 , FACT.CT_BILL_PRCNT_OF_COST "PJI_REP_MSR_8"
225 , FACT.PRCNT_OF_COST_CHANGE "PJI_REP_MSR_9"
226 , FACT.BUDGET "PJI_REP_MSR_10"
227 , FACT.CT_BUDGET "PJI_REP_MSR_11"
228 , FACT.BUDGET_CHANGE_PRCNT "PJI_REP_MSR_12"
229 , FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_13"
230 , FACT.BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_23"
231 , FACT.CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_14"
232 , FACT.CT_BILL_COST_PRCNT_OF_BUDGET "PJI_REP_MSR_24"
233 , FACT.CHANGE "PJI_REP_MSR_15"
234 , FACT.NON_BILL_COST "PJI_REP_MSR_16"
235 , FACT.CT_Non_Bill_Cost "PJI_REP_MSR_17"
236 , FACT.NON_BILL_COST_CHANGE_PRCNT "PJI_REP_MSR_18"'
237 , P_SQL_STATEMENT => x_PMV_Sql
238 , P_PMV_OUTPUT => x_PMV_Output
239 , P_REGION_CODE => 'PJI_REP_PC12'
240 , P_PLSQL_DRIVER => 'PJI_PMV_COST.PLSQLDriver_PJI_REP_PC11'
241 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
242 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
243 ', <<CURRENCY+FII_CURRENCIES>>'||
244 ', <<AS_OF_DATE>>'||
245 ', <<PERIOD_TYPE>>'||
246 ', <<VIEW_BY>>'||
247 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
248 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
249 ', :PJI_EXTRA_BND_01'||
250 ', NULL '||
251 ', NULL '||
252 ', NULL ');
253
254 l_PMV_Rec:=BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
255 l_PMV_Rec.attribute_name:=':PJI_EXTRA_BND_01';
256 l_PMV_Rec.attribute_value:='FISCAL';
257 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
258 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
259
260 x_PMV_Output.EXTEND();
261 x_PMV_Output(x_PMV_Output.COUNT):=l_PMV_Rec;
262
263 END Get_SQL_PJI_REP_PC12;
264
265 /* -------------------------------------------------------------+
266 ** -- PLSQL DRIVERS
270 FUNCTION PLSQLDriver_PJI_REP_PC10(
267 */ -------------------------------------------------------------+
268
269
271 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
272 , p_Organization IN VARCHAR2
273 , p_Currency_Type IN VARCHAR2
274 , p_As_of_Date IN NUMBER
275 , p_Time_Comparison_Type IN VARCHAR2
276 , p_Period_Type IN VARCHAR2
277 , p_View_BY IN VARCHAR2
278 , p_Classifications IN VARCHAR2 DEFAULT NULL
279 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
280 , p_Expenditure_Category IN VARCHAR2 DEFAULT NULL
281 , p_Expenditure_Type IN VARCHAR2 DEFAULT NULL
282 , p_Work_Type IN VARCHAR2 DEFAULT NULL
283 ) RETURN PJI_REP_PC10_TBL
284 IS
285 PRAGMA AUTONOMOUS_TRANSACTION;
286 /*
287 ** -- Local Variable Declaration
288 */
289
290 l_Cost NUMBER:=0;
291 l_CT_Cost NUMBER:=0;
292 l_Bill_Cost NUMBER:=0;
293 l_CT_Bill_Cost NUMBER:=0;
294 l_Budget NUMBER:=0;
295 l_CT_Budget NUMBER:=0;
296 l_Non_Bill_Cost NUMBER:=0;
297 l_CT_Non_Bill_Cost NUMBER:=0;
298
299 l_TO_Cost NUMBER:=0;
300 l_TO_CT_Cost NUMBER:=0;
301 l_TO_Bill_Cost NUMBER:=0;
302 l_TO_CT_Bill_Cost NUMBER:=0;
303 l_TO_Budget NUMBER:=0;
304 l_TO_CT_Budget NUMBER:=0;
305 l_TO_Non_Bill_Cost NUMBER:=0;
306 l_TO_CT_Non_Bill_Cost NUMBER:=0;
307
308 l_Top_Org_Index NUMBER;
309 l_Top_Organization_Name VARCHAR2(240);
310
311 l_Convert_Classification VARCHAR2(1);
312 l_Convert_Expenditure_Type VARCHAR2(1);
313 l_Convert_Work_Type VARCHAR2(1);
314 l_curr_record_type_id NUMBER:= 1;
315
316 /*
317 ** -- PL/SQL Declaration
318 */
319 l_lines_tab PJI_REP_PC10_TBL := PJI_REP_PC10_TBL();
320
321 BEGIN
322 BEGIN
323 SELECT report_cost_type
324 INTO G_Report_Cost_Type
325 FROM pji_system_settings;
326 EXCEPTION
327 WHEN NO_DATA_FOUND THEN
328 G_Report_Cost_Type:='RC';
329 END;
330
331 /*
332 ** Place a call to all the parse API's which parse the
333 ** parameters passed by PMV and populate all the
334 ** temporary tables.
335 */
336
337 PJI_PMV_ENGINE.Convert_Operating_Unit(P_OPERATING_UNIT_IDS => p_Operating_Unit
338 , P_VIEW_BY => p_View_BY);
339
340 PJI_PMV_ENGINE.Convert_Organization(p_TOP_ORGANIZATION_ID => p_Organization
341 , p_VIEW_BY => p_View_BY
342 , p_Top_Organization_Name => l_Top_Organization_Name);
343
344 PJI_PMV_ENGINE.Convert_Time(P_AS_OF_DATE => p_As_of_Date
345 , P_PERIOD_TYPE => p_Period_Type
346 , P_VIEW_BY => p_View_By
347 , P_PARSE_PRIOR => NULL
348 , P_REPORT_TYPE => 'DBI'
349 , P_COMPARATOR => p_Time_Comparison_Type
350 , P_PARSE_ITD => NULL
351 , P_FULL_PERIOD_FLAG => 'Y'
352 );
353 /*
354 ** -- Conditionally Execute ORG, CLASS, EXPENDITURE_TYPE, WORK_TYPE Processing --------------------------------+
355 */
356
357 l_Convert_Classification := PJI_PMV_ENGINE.Convert_Classification
358 (p_Classifications, p_Class_Codes, p_View_BY);
359
360 l_Convert_Expenditure_Type := PJI_PMV_ENGINE.Convert_Expenditure_Type
361 (p_Expenditure_Category, p_Expenditure_Type, p_View_BY);
362
363 l_Convert_Work_Type := PJI_PMV_ENGINE.Convert_Work_Type
364 (p_Work_Type, p_View_BY);
365
366 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
367
368 /*
369 ** ORGANIZATION Processing: No parameter other than Organization is specified
370 */
371
372 IF l_Convert_Classification = 'N'
373 AND l_Convert_Expenditure_Type = 'N'
374 AND l_Convert_Work_Type = 'N'
375 THEN
376 SELECT PJI_REP_PC10
377 ( ORG_ID
378 , ORGANIZATION_ID
379 , PROJECT_CLASS_ID
380 , EXPENDITURE_CATEGORY
381 , EXPENDITURE_TYPE_ID
382 , WORK_TYPE_ID
383 , SUM ( COST )
384 , SUM ( CT_COST )
385 , NULL
386 , SUM ( BILL_COST )
387 , SUM ( CT_BILL_COST )
388 , NULL
389 , NULL
390 , NULL
391 , NULL
392 , SUM ( BUDGET )
393 , SUM ( CT_BUDGET )
394 , NULL
395 , NULL
396 , NULL
397 , NULL
401 , NULL , NULL , NULL , NULL , NULL, NULL
398 , SUM ( NON_BILL_COST )
399 , SUM ( CT_NON_BILL_COST )
400 , NULL
402 , NULL , NULL , NULL , NULL , NULL, NULL
403 , NULL , NULL , NULL , NULL , NULL, NULL )
404 BULK COLLECT INTO l_lines_tab
405 FROM
406 ( SELECT /*+ ORDERED */
407 HOU.NAME AS ORG_ID
408 , HORG.NAME AS ORGANIZATION_ID
409 , '-1' AS PROJECT_CLASS_ID
410 , '-1' as EXPENDITURE_CATEGORY
411 , '-1' as EXPENDITURE_TYPE_ID
412 , '-1' as WORK_TYPE_ID
413 , DECODE(TIME.amount_type,1,
414 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
415 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
416 , 0 CT_COST
417 , DECODE(TIME.amount_type,1,
418 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
419 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
420 , 0 CT_BILL_COST
421 , DECODE(TIME.amount_type,2,
422 DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
423 'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS BUDGET
424 , 0 CT_BUDGET
425 , DECODE(TIME.amount_type,1,
426 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
427 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
428 , 0 CT_NON_BILL_COST
429 FROM
430 PJI_PMV_TIME_DIM_TMP TIME
431 , PJI_PMV_ORGZ_DIM_TMP HORG
432 , PJI_FP_ORGO_F_MV FCT
433 , PJI_PMV_ORG_DIM_TMP HOU
434 WHERE
435 fct.org_id = hou.id
436 AND FCT.ORGANIZATION_ID = HORG.ID
437 AND FCT.TIME_ID = TIME.ID
438 AND TIME.ID IS NOT NULL
439 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
440 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
441 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
442 UNION ALL -- PRIOR Actuals
443 SELECT /*+ ORDERED */
444 HOU.NAME AS ORG_ID
445 , HORG.NAME AS ORGANIZATION_ID
446 , '-1' AS PROJECT_CLASS_ID
447 , '-1' as EXPENDITURE_CATEGORY
448 , '-1' as EXPENDITURE_TYPE_ID
449 , '-1' as WORK_TYPE_ID
450 , 0 AS COST
451 , DECODE(TIME.amount_type,1,
452 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
453 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
454 , 0 AS BILL_COST
455 , DECODE(TIME.amount_type,1,
456 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
457 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
458 , 0 AS BUDGET
459 , DECODE(TIME.amount_type,2,
460 DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
461 'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS CT_BUDGET
462 , 0 AS NON_BILL_COST
463 , DECODE(TIME.amount_type,1,
467 PJI_PMV_TCMP_DIM_TMP TIME
464 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
465 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
466 FROM
468 , PJI_PMV_ORGZ_DIM_TMP HORG
469 , PJI_FP_ORGO_F_MV fct
470 , PJI_PMV_ORG_DIM_TMP HOU
471 WHERE
472 fct.org_id = hou.id
473 AND FCT.ORGANIZATION_ID = HORG.ID
474 AND FCT.TIME_ID = TIME.ID
475 AND TIME.ID IS NOT NULL
476 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
477 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
478 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
479 UNION ALL -- FORCE Creation of Org rows
480 SELECT HOU.NAME AS ORG_ID
481 , '-1' AS ORGANIZATION_ID
482 , '-1' AS PROJECT_CLASS_ID
483 , '-1' AS EXPENDITURE_CATEGORY
484 , '-1' AS EXPENDITURE_TYPE_ID
485 , '-1' AS WORK_TYPE_ID
486 , 0 AS COST
487 , 0 AS CT_COST
488 , 0 AS BILL_COST
489 , 0 AS CT_BILL_COST
490 , 0 AS BUDGET
491 , 0 AS CT_BUDGET
492 , 0 AS NON_BILL_COST
493 , 0 AS CT_NON_BILL_COST
494 FROM PJI_PMV_ORG_DIM_TMP HOU
495 WHERE HOU.NAME <> '-1'
496 UNION ALL -- FORCE Creation of Organization Rows
497 SELECT '-1' AS ORG_ID
498 , HORG.NAME AS ORGANIZATION_ID
499 , '-1' AS PROJECT_CLASS_ID
500 , '-1' as EXPENDITURE_CATEGORY
501 , '-1' as EXPENDITURE_TYPE_ID
502 , '-1' as WORK_TYPE_ID
503 , 0 AS COST
504 , 0 AS CT_COST
505 , 0 AS BILL_COST
506 , 0 AS CT_BILL_COST
507 , 0 AS BUDGET
508 , 0 AS CT_BUDGET
509 , 0 AS NON_BILL_COST
510 , 0 AS CT_NON_BILL_COST
511 FROM PJI_PMV_ORGZ_DIM_TMP HORG
512 WHERE HORG.NAME <> '-1'
513 ) WHERE 1=1
514 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
515 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
516
517 ELSIF l_Convert_Classification = 'Y'
518 AND l_Convert_Expenditure_Type = 'N'
519 AND l_Convert_Work_Type = 'N'
520 THEN
521 SELECT PJI_REP_PC10
522 ( ORG_ID
523 , ORGANIZATION_ID
524 , PROJECT_CLASS_ID
525 , EXPENDITURE_CATEGORY
526 , EXPENDITURE_TYPE_ID
527 , WORK_TYPE_ID
528 , SUM ( COST )
529 , SUM ( CT_COST )
530 , NULL
531 , SUM ( BILL_COST )
532 , SUM ( CT_BILL_COST )
533 , NULL
534 , NULL
535 , NULL
536 , NULL
537 , SUM ( BUDGET )
538 , SUM ( CT_BUDGET )
539 , NULL
540 , NULL
541 , NULL
542 , NULL
543 , SUM ( NON_BILL_COST )
544 , SUM ( CT_NON_BILL_COST )
545 , NULL
546 , NULL , NULL , NULL , NULL , NULL, NULL
547 , NULL , NULL , NULL , NULL , NULL, NULL
548 , NULL , NULL , NULL , NULL , NULL, NULL )
549 BULK COLLECT INTO l_lines_tab
550 FROM
551 ( SELECT /*+ ORDERED */
552 HOU.NAME AS ORG_ID
553 , HORG.NAME AS ORGANIZATION_ID
554 , CLS.NAME AS PROJECT_CLASS_ID
555 , '-1' as EXPENDITURE_CATEGORY
556 , '-1' as EXPENDITURE_TYPE_ID
557 , '-1' as WORK_TYPE_ID
558 , DECODE(TIME.amount_type,1,
559 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
560 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
561 , 0 CT_COST
562 , DECODE(TIME.amount_type,1,
563 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
564 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
565 , 0 CT_BILL_COST
566 , DECODE(TIME.amount_type,2,
567 DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
568 'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS BUDGET
569 , 0 CT_BUDGET
570 , DECODE(TIME.amount_type,1,
571 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
572 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
573 , 0 CT_NON_BILL_COST
574 FROM
575 PJI_PMV_TIME_DIM_TMP TIME
579 , PJI_PMV_ORG_DIM_TMP HOU
576 , PJI_PMV_ORGZ_DIM_TMP HORG
577 , PJI_PMV_CLS_DIM_TMP CLS
578 , PJI_FP_CLSO_F_MV FCT
580 WHERE
581 FCT.PROJECT_ORG_ID = HOU.ID
582 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
583 AND FCT.TIME_ID = TIME.ID
584 AND TIME.ID IS NOT NULL
585 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
586 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
587 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
588 AND FCT.PROJECT_CLASS_ID = CLS.ID
589 UNION ALL -- PRIOR Actuals
590 SELECT /*+ ORDERED */
591 HOU.NAME AS ORG_ID
592 , HORG.NAME AS ORGANIZATION_ID
593 , CLS.NAME AS PROJECT_CLASS_ID
594 , '-1' AS EXPENDITURE_CATEGORY
595 , '-1' AS EXPENDITURE_TYPE_ID
596 , '-1' AS WORK_TYPE_ID
597 , 0 AS COST
598 , DECODE(TIME.amount_type,1,
599 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
600 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
601 , 0 AS BILL_COST
602 , DECODE(TIME.amount_type,1,
603 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
604 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
605 , 0 AS BUDGET
606 , DECODE(TIME.amount_type,2,
607 DECODE(G_Report_Cost_Type, 'BC', FCT.CON_CURR_BGT_BRDN_COST,
608 'RC', FCT.CON_CURR_BGT_RAW_COST, 0),0) AS CT_BUDGET
609 , 0 AS NON_BILL_COST
610 , DECODE(TIME.amount_type,1,
611 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
612 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
613 FROM
614 PJI_PMV_TCMP_DIM_TMP TIME
615 , PJI_PMV_ORGZ_DIM_TMP HORG
616 , PJI_PMV_CLS_DIM_TMP CLS
617 , PJI_FP_CLSO_F_MV FCT
618 , PJI_PMV_ORG_DIM_TMP HOU
619 WHERE
620 FCT.PROJECT_ORG_ID = HOU.ID
621 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
622 AND FCT.TIME_ID = TIME.ID
623 AND TIME.ID IS NOT NULL
624 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
625 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
626 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
627 AND FCT.PROJECT_CLASS_ID = CLS.ID
628 UNION ALL -- FORCE Creation of Org rows
629 SELECT HOU.NAME AS ORG_ID
630 , '-1' AS ORGANIZATION_ID
631 , '-1' AS PROJECT_CLASS_ID
632 , '-1' AS EXPENDITURE_CATEGORY
633 , '-1' AS EXPENDITURE_TYPE_ID
634 , '-1' AS WORK_TYPE_ID
635 , 0 AS COST
636 , 0 AS CT_COST
637 , 0 AS BILL_COST
638 , 0 AS CT_BILL_COST
639 , 0 AS BUDGET
640 , 0 AS CT_BUDGET
641 , 0 AS NON_BILL_COST
642 , 0 AS CT_NON_BILL_COST
643 FROM PJI_PMV_ORG_DIM_TMP HOU
644 WHERE HOU.NAME <> '-1'
645 UNION ALL -- FORCE Creation of Organization Rows
646 SELECT '-1' AS ORG_ID
647 , HORG.NAME AS ORGANIZATION_ID
648 , '-1' AS PROJECT_CLASS_ID
649 , '-1' as EXPENDITURE_CATEGORY
650 , '-1' as EXPENDITURE_TYPE_ID
651 , '-1' as WORK_TYPE_ID
652 , 0 AS COST
653 , 0 AS CT_COST
654 , 0 AS BILL_COST
655 , 0 AS CT_BILL_COST
656 , 0 AS BUDGET
657 , 0 AS CT_BUDGET
658 , 0 AS NON_BILL_COST
659 , 0 AS CT_NON_BILL_COST
660 FROM PJI_PMV_ORGZ_DIM_TMP HORG
661 WHERE HORG.NAME <> '-1'
662 UNION ALL
663 SELECT '-1' AS ORG_ID
664 , '-1' AS ORGANIZATION_ID
665 , CLS.NAME AS PROJECT_CLASS_ID
666 , '-1' AS EXPENDITURE_CATEGORY
667 , '-1' AS EXPENDITURE_TYPE_ID
668 , '-1' AS WORK_TYPE_ID
669 , 0 AS COST
670 , 0 AS CT_COST
671 , 0 AS BILL_COST
672 , 0 AS CT_BILL_COST
673 , 0 AS BUDGET
674 , 0 AS CT_BUDGET
675 , 0 AS NON_BILL_COST
676 , 0 AS CT_NON_BILL_COST
677 FROM PJI_PMV_CLS_DIM_TMP CLS
678 WHERE CLS.NAME <> '-1'
679 ) WHERE 1=1
680 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
681 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
685 ** Only Organization and Expenditure Category/Type is specified
682
683 /*
684 ** ORGANIZATION AND EXPENDITURE CATEGORY/TYPE Processing:
686 */
687
688 ELSIF l_Convert_Classification = 'N'
689 AND l_Convert_Expenditure_Type = 'Y'
690 AND l_Convert_Work_Type = 'N'
691 THEN
692 SELECT PJI_REP_PC10
693 ( ORG_ID
694 , ORGANIZATION_ID
695 , PROJECT_CLASS_ID
696 , EXPENDITURE_CATEGORY
697 , EXPENDITURE_TYPE_ID
698 , WORK_TYPE_ID
699 , SUM ( COST )
700 , SUM ( CT_COST )
701 , NULL
702 , SUM ( BILL_COST )
703 , SUM ( CT_BILL_COST )
704 , NULL
705 , NULL
706 , NULL
707 , NULL
708 , SUM ( BUDGET )
709 , SUM ( CT_BUDGET )
710 , NULL
711 , NULL
712 , NULL
713 , NULL
714 , SUM ( NON_BILL_COST )
715 , SUM ( CT_NON_BILL_COST )
716 , NULL
717 , NULL , NULL , NULL , NULL , NULL, NULL
718 , NULL , NULL , NULL , NULL , NULL, NULL
719 , NULL , NULL , NULL , NULL , NULL, NULL )
720 BULK COLLECT INTO l_lines_tab
721 FROM
722 ( SELECT /*+ ORDERED */
723 HOU.NAME AS ORG_ID
724 , HORG.NAME AS ORGANIZATION_ID
725 , '-1' AS PROJECT_CLASS_ID
726 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
727 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
728 , '-1' as WORK_TYPE_ID
729 , DECODE(TIME.amount_type,1,
730 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
731 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
732 , 0 CT_COST
733 , DECODE(TIME.amount_type,1,
734 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
735 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
736 , 0 CT_BILL_COST
737 , NULL AS BUDGET
738 , NULL AS CT_BUDGET
739 , DECODE(TIME.amount_type,1,
740 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
741 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
742 , 0 CT_NON_BILL_COST
743 FROM
744 PJI_PMV_TIME_DIM_TMP TIME
745 , PJI_PMV_ORGZ_DIM_TMP HORG
746 , PJI_PMV_ET_RT_DIM_TMP ET
747 , PJI_FP_ORGO_ET_F_MV FCT
748 , PJI_PMV_ORG_DIM_TMP HOU
749 WHERE
750 FCT.ORG_ID = HOU.ID
751 AND FCT.ORGANIZATION_ID = HORG.ID
752 AND FCT.TIME_ID = TIME.ID
753 AND TIME.ID IS NOT NULL
754 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
755 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
756 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
757 AND EXP_EVT_TYPE_ID= ET.ID
758 and ET.record_type = 'ET'
759 UNION ALL -- PRIOR Actuals
760 SELECT /*+ ORDERED */
761 HOU.NAME AS ORG_ID
762 , HORG.NAME AS ORGANIZATION_ID
763 , '-1' AS PROJECT_CLASS_ID
764 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
765 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
766 , '-1' AS WORK_TYPE_ID
767 , 0 AS COST
768 , DECODE(TIME.amount_type,1,
769 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
770 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
771 , 0 AS BILL_COST
772 , DECODE(TIME.amount_type,1,
773 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
774 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
775 , NULL AS BUDGET
776 , NULL AS CT_BUDGET
777 , 0 AS NON_BILL_COST
778 , DECODE(TIME.amount_type,1,
779 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
780 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
781 FROM
782 PJI_PMV_TCMP_DIM_TMP TIME
783 , PJI_PMV_ORGZ_DIM_TMP HORG
784 , PJI_PMV_ET_RT_DIM_TMP ET
785 , PJI_FP_ORGO_ET_F_MV FCT
786 , PJI_PMV_ORG_DIM_TMP HOU
787 WHERE
788 FCT.ORG_ID = HOU.ID
789 AND FCT.ORGANIZATION_ID = HORG.ID
790 AND FCT.TIME_ID = TIME.ID
791 AND TIME.ID IS NOT NULL
792 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
793 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
797 UNION ALL -- FORCE Creation of Org rows
794 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
795 AND FCT.EXP_EVT_TYPE_ID = ET.ID
796 and ET.record_type = 'ET'
798 SELECT HOU.NAME AS ORG_ID
799 , '-1' AS ORGANIZATION_ID
800 , '-1' AS PROJECT_CLASS_ID
801 , '-1' AS EXPENDITURE_CATEGORY
802 , '-1' AS EXPENDITURE_TYPE_ID
803 , '-1' AS WORK_TYPE_ID
804 , 0 AS COST
805 , 0 AS CT_COST
806 , 0 AS BILL_COST
807 , 0 AS CT_BILL_COST
808 , 0 AS BUDGET
809 , 0 AS CT_BUDGET
810 , 0 AS NON_BILL_COST
811 , 0 AS CT_NON_BILL_COST
812 FROM PJI_PMV_ORG_DIM_TMP HOU
813 WHERE HOU.NAME <> '-1'
814 UNION ALL -- FORCE Creation of Organization Rows
815 SELECT '-1' AS ORG_ID
816 , HORG.NAME AS ORGANIZATION_ID
817 , '-1' AS PROJECT_CLASS_ID
818 , '-1' as EXPENDITURE_CATEGORY
819 , '-1' as EXPENDITURE_TYPE_ID
820 , '-1' as WORK_TYPE_ID
821 , 0 AS COST
822 , 0 AS CT_COST
823 , 0 AS BILL_COST
824 , 0 AS CT_BILL_COST
825 , 0 AS BUDGET
826 , 0 AS CT_BUDGET
827 , 0 AS NON_BILL_COST
828 , 0 AS CT_NON_BILL_COST
829 FROM PJI_PMV_ORGZ_DIM_TMP HORG
830 WHERE HORG.NAME <> '-1'
831 UNION ALL
832 SELECT '-1' AS ORG_ID
833 , '-1' AS ORGANIZATION_ID
834 , '-1' AS PROJECT_CLASS_ID
835 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
836 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
837 , '-1' AS WORK_TYPE_ID
838 , 0 AS COST
839 , 0 AS CT_COST
840 , 0 AS BILL_COST
841 , 0 AS CT_BILL_COST
842 , 0 AS BUDGET
843 , 0 AS CT_BUDGET
844 , 0 AS NON_BILL_COST
845 , 0 AS CT_NON_BILL_COST
846 FROM PJI_PMV_ET_RT_DIM_TMP ET
847 WHERE ET.NAME <> '-1'
848 ) WHERE 1=1
849 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
850 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
851
852 /*
853 ** ORGANIZATION AND WORK TYPE Processing:
854 ** Only Organization and Work Type is specified
855 */
856
857 ELSIF l_Convert_Classification = 'N'
858 AND l_Convert_Expenditure_Type = 'N'
859 AND l_Convert_Work_Type = 'Y'
860 THEN
861 SELECT PJI_REP_PC10
862 ( ORG_ID
863 , ORGANIZATION_ID
864 , PROJECT_CLASS_ID
865 , EXPENDITURE_CATEGORY
866 , EXPENDITURE_TYPE_ID
867 , WORK_TYPE_ID
868 , SUM ( COST )
869 , SUM ( CT_COST )
870 , NULL
871 , SUM ( BILL_COST )
872 , SUM ( CT_BILL_COST )
873 , NULL
874 , NULL
875 , NULL
876 , NULL
877 , SUM ( BUDGET )
878 , SUM ( CT_BUDGET )
879 , NULL
880 , NULL
881 , NULL
882 , NULL
883 , SUM ( NON_BILL_COST )
884 , SUM ( CT_NON_BILL_COST )
885 , NULL
886 , NULL , NULL , NULL , NULL , NULL, NULL
887 , NULL , NULL , NULL , NULL , NULL, NULL
888 , NULL , NULL , NULL , NULL , NULL, NULL )
889 BULK COLLECT INTO l_lines_tab
890 FROM
891 ( SELECT /*+ ORDERED */
892 HOU.NAME AS ORG_ID
893 , HORG.NAME AS ORGANIZATION_ID
894 , '-1' AS PROJECT_CLASS_ID
895 , '-1' AS EXPENDITURE_CATEGORY
896 , '-1' AS EXPENDITURE_TYPE_ID
897 , WT.NAME AS WORK_TYPE_ID
898 , DECODE(TIME.amount_type,1,
899 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
900 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
901 , 0 CT_COST
902 , DECODE(TIME.amount_type,1,
903 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
904 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
905 , 0 CT_BILL_COST
906 , NULL AS BUDGET
907 , NULL AS CT_BUDGET
911 , 0 CT_NON_BILL_COST
908 , DECODE(TIME.amount_type,1,
909 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
910 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
912 FROM
913 PJI_PMV_TIME_DIM_TMP TIME
914 , PJI_PMV_ORGZ_DIM_TMP HORG
915 , PJI_PMV_WT_DIM_TMP WT
916 , PJI_FP_ORGO_ET_WT_F_MV FCT
917 , PJI_PMV_ORG_DIM_TMP HOU
918 WHERE
919 FCT.ORG_ID = HOU.ID
920 AND FCT.ORGANIZATION_ID = HORG.ID
921 AND FCT.TIME_ID = TIME.ID
922 AND TIME.ID IS NOT NULL
923 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
924 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
925 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
926 AND WORK_TYPE_ID= WT.ID
927 UNION ALL -- PRIOR Actuals
928 SELECT /*+ ORDERED */
929 HOU.NAME AS ORG_ID
930 , HORG.NAME AS ORGANIZATION_ID
931 , '-1' AS PROJECT_CLASS_ID
932 , '-1' AS EXPENDITURE_CATEGORY
933 , '-1' AS EXPENDITURE_TYPE_ID
934 , WT.NAME AS WORK_TYPE_ID
935 , 0 AS COST
936 , DECODE(TIME.amount_type,1,
937 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
938 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
939 , 0 AS BILL_COST
940 , DECODE(TIME.amount_type,1,
941 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
942 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
943 , NULL AS BUDGET
944 , NULL AS CT_BUDGET
945 , 0 AS NON_BILL_COST
946 , DECODE(TIME.amount_type,1,
947 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
948 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
949 FROM
950 PJI_PMV_TCMP_DIM_TMP TIME
951 , PJI_PMV_ORGZ_DIM_TMP HORG
952 , PJI_PMV_WT_DIM_TMP WT
953 , PJI_FP_ORGO_ET_WT_F_MV FCT
954 , PJI_PMV_ORG_DIM_TMP HOU
955 WHERE
956 FCT.ORG_ID = HOU.ID
957 AND FCT.ORGANIZATION_ID = HORG.ID
958 AND FCT.TIME_ID = TIME.ID
959 AND TIME.ID IS NOT NULL
960 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
961 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
962 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
963 AND FCT.WORK_TYPE_ID = WT.ID
964 UNION ALL -- FORCE Creation of Org rows
965 SELECT HOU.NAME AS ORG_ID
966 , '-1' AS ORGANIZATION_ID
967 , '-1' AS PROJECT_CLASS_ID
968 , '-1' AS EXPENDITURE_CATEGORY
969 , '-1' AS EXPENDITURE_TYPE_ID
970 , '-1' AS WORK_TYPE_ID
971 , 0 AS COST
972 , 0 AS CT_COST
973 , 0 AS BILL_COST
974 , 0 AS CT_BILL_COST
975 , 0 AS BUDGET
976 , 0 AS CT_BUDGET
977 , 0 AS NON_BILL_COST
978 , 0 AS CT_NON_BILL_COST
979 FROM PJI_PMV_ORG_DIM_TMP HOU
980 WHERE HOU.NAME <> '-1'
981 UNION ALL -- FORCE Creation of Organization Rows
982 SELECT '-1' AS ORG_ID
983 , HORG.NAME AS ORGANIZATION_ID
984 , '-1' AS PROJECT_CLASS_ID
985 , '-1' as EXPENDITURE_CATEGORY
986 , '-1' as EXPENDITURE_TYPE_ID
987 , '-1' as WORK_TYPE_ID
988 , 0 AS COST
989 , 0 AS CT_COST
990 , 0 AS BILL_COST
991 , 0 AS CT_BILL_COST
992 , 0 AS BUDGET
993 , 0 AS CT_BUDGET
994 , 0 AS NON_BILL_COST
995 , 0 AS CT_NON_BILL_COST
996 FROM PJI_PMV_ORGZ_DIM_TMP HORG
997 WHERE HORG.NAME <> '-1'
998 UNION ALL
999 SELECT '-1' AS ORG_ID
1000 , '-1' AS ORGANIZATION_ID
1001 , '-1' AS PROJECT_CLASS_ID
1002 , '-1' AS EXPENDITURE_CATEGORY
1003 , '-1' AS EXPENDITURE_TYPE_ID
1004 , WT.NAME AS WORK_TYPE_ID
1005 , 0 AS COST
1006 , 0 AS CT_COST
1007 , 0 AS BILL_COST
1008 , 0 AS CT_BILL_COST
1009 , 0 AS BUDGET
1010 , 0 AS CT_BUDGET
1011 , 0 AS NON_BILL_COST
1012 , 0 AS CT_NON_BILL_COST
1013 FROM PJI_PMV_WT_DIM_TMP WT
1014 WHERE WT.NAME <> '-1'
1015 ) WHERE 1=1
1019
1016 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
1017 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
1018
1020 /*
1021 ** ORGANIZATION, CLASSIFICATION AND EXPENDITURE CATEGORY/TYPE Processing:
1022 ** Only Organization, Classification and Expenditure Category/Type is specified
1023 */
1024
1025 ELSIF l_Convert_Classification = 'Y'
1026 AND l_Convert_Expenditure_Type = 'Y'
1027 AND l_Convert_Work_Type = 'N'
1028 THEN
1029 SELECT PJI_REP_PC10
1030 ( ORG_ID
1031 , ORGANIZATION_ID
1032 , PROJECT_CLASS_ID
1033 , EXPENDITURE_CATEGORY
1034 , EXPENDITURE_TYPE_ID
1035 , WORK_TYPE_ID
1036 , SUM ( COST )
1037 , SUM ( CT_COST )
1038 , NULL
1039 , SUM ( BILL_COST )
1040 , SUM ( CT_BILL_COST )
1041 , NULL
1042 , NULL
1043 , NULL
1044 , NULL
1045 , SUM ( BUDGET )
1046 , SUM ( CT_BUDGET )
1047 , NULL
1048 , NULL
1049 , NULL
1050 , NULL
1051 , SUM ( NON_BILL_COST )
1052 , SUM ( CT_NON_BILL_COST )
1053 , NULL
1054 , NULL , NULL , NULL , NULL , NULL, NULL
1055 , NULL , NULL , NULL , NULL , NULL, NULL
1056 , NULL , NULL , NULL , NULL , NULL, NULL )
1057 BULK COLLECT INTO l_lines_tab
1058 FROM
1059 ( SELECT /*+ ORDERED */
1060 HOU.NAME AS ORG_ID
1061 , HORG.NAME AS ORGANIZATION_ID
1062 , CLS.NAME AS PROJECT_CLASS_ID
1063 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1064 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1065 , '-1' AS WORK_TYPE_ID
1066 , DECODE(TIME.amount_type,1,
1067 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1068 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
1069 , 0 CT_COST
1070 , DECODE(TIME.amount_type,1,
1071 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1072 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
1073 , 0 CT_BILL_COST
1074 , NULL AS BUDGET
1075 , NULL AS CT_BUDGET
1076 , DECODE(TIME.amount_type,1,
1077 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1078 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
1079 , 0 CT_NON_BILL_COST
1080 FROM
1081 PJI_PMV_TIME_DIM_TMP TIME
1082 , PJI_PMV_ORGZ_DIM_TMP HORG
1083 , PJI_PMV_ET_RT_DIM_TMP ET
1084 , PJI_FP_CLSO_ET_F_MV FCT
1085 , PJI_PMV_CLS_DIM_TMP CLS
1086 , PJI_PMV_ORG_DIM_TMP HOU
1087 WHERE
1088 FCT.PROJECT_ORG_ID = HOU.ID
1089 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1090 AND FCT.TIME_ID = TIME.ID
1091 AND TIME.ID IS NOT NULL
1092 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1093 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1094 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1095 AND PROJECT_CLASS_ID= CLS.ID
1096 and FCT.EXP_EVT_TYPE_ID = ET.ID
1097 and ET.record_type = 'ET'
1098 UNION ALL -- PRIOR Actuals
1099 SELECT /*+ ORDERED */
1100 HOU.NAME AS ORG_ID
1101 , HORG.NAME AS ORGANIZATION_ID
1102 , CLS.NAME AS PROJECT_CLASS_ID
1103 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1104 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1105 , '-1' AS WORK_TYPE_ID
1106 , 0 AS COST
1107 , DECODE(TIME.amount_type,1,
1108 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1112 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1109 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
1110 , 0 AS BILL_COST
1111 , DECODE(TIME.amount_type,1,
1113 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
1114 , NULL AS BUDGET
1115 , NULL AS CT_BUDGET
1116 , 0 AS NON_BILL_COST
1117 , DECODE(TIME.amount_type,1,
1118 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1119 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
1120 FROM
1121 PJI_PMV_TCMP_DIM_TMP TIME
1122 , PJI_PMV_ORGZ_DIM_TMP HORG
1123 , PJI_PMV_ET_RT_DIM_TMP ET
1124 , PJI_FP_CLSO_ET_F_MV FCT
1125 , PJI_PMV_CLS_DIM_TMP CLS
1126 , PJI_PMV_ORG_DIM_TMP HOU
1127 WHERE
1128 FCT.PROJECT_ORG_ID = HOU.ID
1129 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1130 AND FCT.TIME_ID = TIME.ID
1131 AND TIME.ID IS NOT NULL
1132 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1133 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1134 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1135 AND PROJECT_CLASS_ID = CLS.ID
1136 and FCT.EXP_EVT_TYPE_ID = ET.ID
1137 and ET.record_type = 'ET'
1138 UNION ALL -- FORCE Creation of Org rows
1139 SELECT HOU.NAME AS ORG_ID
1140 , '-1' AS ORGANIZATION_ID
1141 , '-1' AS PROJECT_CLASS_ID
1142 , '-1' AS EXPENDITURE_CATEGORY
1143 , '-1' AS EXPENDITURE_TYPE_ID
1144 , '-1' AS WORK_TYPE_ID
1145 , 0 AS COST
1146 , 0 AS CT_COST
1147 , 0 AS BILL_COST
1148 , 0 AS CT_BILL_COST
1149 , 0 AS BUDGET
1150 , 0 AS CT_BUDGET
1151 , 0 AS NON_BILL_COST
1152 , 0 AS CT_NON_BILL_COST
1153 FROM PJI_PMV_ORG_DIM_TMP HOU
1154 WHERE HOU.NAME <> '-1'
1155 UNION ALL -- FORCE Creation of Organization Rows
1156 SELECT '-1' AS ORG_ID
1157 , HORG.NAME AS ORGANIZATION_ID
1158 , '-1' AS PROJECT_CLASS_ID
1159 , '-1' as EXPENDITURE_CATEGORY
1160 , '-1' as EXPENDITURE_TYPE_ID
1161 , '-1' as WORK_TYPE_ID
1162 , 0 AS COST
1163 , 0 AS CT_COST
1164 , 0 AS BILL_COST
1165 , 0 AS CT_BILL_COST
1166 , 0 AS BUDGET
1167 , 0 AS CT_BUDGET
1168 , 0 AS NON_BILL_COST
1169 , 0 AS CT_NON_BILL_COST
1170 FROM PJI_PMV_ORGZ_DIM_TMP HORG
1171 WHERE HORG.NAME <> '-1'
1172 UNION ALL
1173 SELECT '-1' AS ORG_ID
1174 , '-1' AS ORGANIZATION_ID
1175 , CLS.NAME AS PROJECT_CLASS_ID
1176 , '-1' AS EXPENDITURE_CATEGORY
1177 , '-1' AS EXPENDITURE_TYPE_ID
1178 , '-1' AS WORK_TYPE_ID
1179 , 0 AS COST
1180 , 0 AS CT_COST
1181 , 0 AS BILL_COST
1182 , 0 AS CT_BILL_COST
1183 , 0 AS BUDGET
1184 , 0 AS CT_BUDGET
1185 , 0 AS NON_BILL_COST
1186 , 0 AS CT_NON_BILL_COST
1187 FROM PJI_PMV_CLS_DIM_TMP CLS
1188 WHERE CLS.NAME <> '-1'
1189 UNION ALL
1190 SELECT '-1' AS ORG_ID
1191 , '-1' AS ORGANIZATION_ID
1192 , '-1' AS PROJECT_CLASS_ID
1193 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1194 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1195 , '-1' AS WORK_TYPE_ID
1196 , 0 AS COST
1197 , 0 AS CT_COST
1198 , 0 AS BILL_COST
1199 , 0 AS CT_BILL_COST
1200 , 0 AS BUDGET
1201 , 0 AS CT_BUDGET
1202 , 0 AS NON_BILL_COST
1203 , 0 AS CT_NON_BILL_COST
1204 FROM PJI_PMV_ET_RT_DIM_TMP ET
1205 WHERE ET.NAME <> '-1'
1206 ) WHERE 1=1
1207 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
1208 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
1209
1210
1211
1212 ELSIF l_Convert_Classification = 'N'
1213 AND l_Convert_Expenditure_Type = 'Y'
1214 AND l_Convert_Work_Type = 'Y'
1215 THEN
1216 SELECT PJI_REP_PC10
1217 ( ORG_ID
1218 , ORGANIZATION_ID
1219 , PROJECT_CLASS_ID
1220 , EXPENDITURE_CATEGORY
1221 , EXPENDITURE_TYPE_ID
1222 , WORK_TYPE_ID
1223 , SUM ( COST )
1224 , SUM ( CT_COST )
1225 , NULL
1226 , SUM ( BILL_COST )
1227 , SUM ( CT_BILL_COST )
1228 , NULL
1229 , NULL
1233 , SUM ( CT_BUDGET )
1230 , NULL
1231 , NULL
1232 , SUM ( BUDGET )
1234 , NULL
1235 , NULL
1236 , NULL
1237 , NULL
1238 , SUM ( NON_BILL_COST )
1239 , SUM ( CT_NON_BILL_COST )
1240 , NULL
1241 , NULL , NULL , NULL , NULL , NULL, NULL
1242 , NULL , NULL , NULL , NULL , NULL, NULL
1243 , NULL , NULL , NULL , NULL , NULL, NULL )
1244 BULK COLLECT INTO l_lines_tab
1245 FROM
1246 ( SELECT /*+ ORDERED */
1247 HOU.NAME AS ORG_ID
1248 , HORG.NAME AS ORGANIZATION_ID
1249 , '-1' AS PROJECT_CLASS_ID
1250 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1251 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1252 , WT.NAME AS WORK_TYPE_ID
1253 , DECODE(TIME.amount_type,1,
1254 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1255 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
1256 , 0 CT_COST
1257 , DECODE(TIME.amount_type,1,
1258 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1259 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
1260 , 0 CT_BILL_COST
1261 , NULL AS BUDGET
1262 , NULL AS CT_BUDGET
1263 , DECODE(TIME.amount_type,1,
1264 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1265 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
1266 , 0 CT_NON_BILL_COST
1267 FROM
1268 PJI_PMV_TIME_DIM_TMP TIME
1269 , PJI_PMV_ORGZ_DIM_TMP HORG
1270 , PJI_PMV_ET_RT_DIM_TMP ET
1271 , PJI_FP_ORGO_ET_WT_F_MV FCT
1272 , PJI_PMV_WT_DIM_TMP WT
1273 , PJI_PMV_ORG_DIM_TMP HOU
1274 WHERE
1275 FCT.ORG_ID = HOU.ID
1276 AND FCT.ORGANIZATION_ID = HORG.ID
1277 AND FCT.TIME_ID = TIME.ID
1278 AND TIME.ID IS NOT NULL
1279 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1280 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1281 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1282 AND WORK_TYPE_ID= WT.ID
1283 and FCT.EXP_EVT_TYPE_ID = ET.ID
1284 and ET.record_type = 'ET'
1285 UNION ALL -- PRIOR Actuals
1286 SELECT /*+ ORDERED */
1287 HOU.NAME AS ORG_ID
1288 , HORG.NAME AS ORGANIZATION_ID
1289 , '-1' AS PROJECT_CLASS_ID
1290 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1291 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1292 , WT.NAME AS WORK_TYPE_ID
1293 , 0 AS COST
1294 , DECODE(TIME.amount_type,1,
1295 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1296 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
1297 , 0 AS BILL_COST
1298 , DECODE(TIME.amount_type,1,
1302 , NULL AS CT_BUDGET
1299 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1300 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
1301 , NULL AS BUDGET
1303 , 0 AS NON_BILL_COST
1304 , DECODE(TIME.amount_type,1,
1305 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1306 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
1307 FROM
1308 PJI_PMV_TCMP_DIM_TMP TIME
1309 , PJI_PMV_ORGZ_DIM_TMP HORG
1310 , PJI_PMV_ET_RT_DIM_TMP ET
1311 , PJI_FP_ORGO_ET_WT_F_MV FCT
1312 , PJI_PMV_WT_DIM_TMP WT
1313 , PJI_PMV_ORG_DIM_TMP HOU
1314 WHERE
1315 FCT.ORG_ID = HOU.ID
1316 AND FCT.ORGANIZATION_ID = HORG.ID
1317 AND FCT.TIME_ID = TIME.ID
1318 AND TIME.ID IS NOT NULL
1319 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1320 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1321 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1322 AND WORK_TYPE_ID = WT.ID
1323 and FCT.EXP_EVT_TYPE_ID = ET.ID
1324 and ET.record_type = 'ET'
1325 UNION ALL -- FORCE Creation of Org rows
1326 SELECT HOU.NAME AS ORG_ID
1327 , '-1' AS ORGANIZATION_ID
1328 , '-1' AS PROJECT_CLASS_ID
1329 , '-1' AS EXPENDITURE_CATEGORY
1330 , '-1' AS EXPENDITURE_TYPE_ID
1331 , '-1' AS WORK_TYPE_ID
1332 , 0 AS COST
1333 , 0 AS CT_COST
1334 , 0 AS BILL_COST
1335 , 0 AS CT_BILL_COST
1336 , 0 AS BUDGET
1337 , 0 AS CT_BUDGET
1338 , 0 AS NON_BILL_COST
1339 , 0 AS CT_NON_BILL_COST
1340 FROM PJI_PMV_ORG_DIM_TMP HOU
1341 WHERE HOU.NAME <> '-1'
1342 UNION ALL -- FORCE Creation of Organization Rows
1343 SELECT '-1' AS ORG_ID
1344 , HORG.NAME AS ORGANIZATION_ID
1345 , '-1' AS PROJECT_CLASS_ID
1346 , '-1' as EXPENDITURE_CATEGORY
1347 , '-1' as EXPENDITURE_TYPE_ID
1348 , '-1' as WORK_TYPE_ID
1349 , 0 AS COST
1350 , 0 AS CT_COST
1351 , 0 AS BILL_COST
1352 , 0 AS CT_BILL_COST
1353 , 0 AS BUDGET
1354 , 0 AS CT_BUDGET
1355 , 0 AS NON_BILL_COST
1356 , 0 AS CT_NON_BILL_COST
1357 FROM PJI_PMV_ORGZ_DIM_TMP HORG
1358 WHERE HORG.NAME <> '-1'
1359 UNION ALL
1360 SELECT '-1' AS ORG_ID
1361 , '-1' AS ORGANIZATION_ID
1362 , '-1' AS PROJECT_CLASS_ID
1363 , '-1' AS EXPENDITURE_CATEGORY
1364 , '-1' AS EXPENDITURE_TYPE_ID
1365 , WT.NAME AS WORK_TYPE_ID
1366 , 0 AS COST
1367 , 0 AS CT_COST
1368 , 0 AS BILL_COST
1369 , 0 AS CT_BILL_COST
1370 , 0 AS BUDGET
1371 , 0 AS CT_BUDGET
1372 , 0 AS NON_BILL_COST
1373 , 0 AS CT_NON_BILL_COST
1374 FROM PJI_PMV_WT_DIM_TMP WT
1375 WHERE WT.NAME <> '-1'
1376 UNION ALL
1377 SELECT '-1' AS ORG_ID
1378 , '-1' AS ORGANIZATION_ID
1379 , '-1' AS PROJECT_CLASS_ID
1380 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1381 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1382 , '-1' AS WORK_TYPE_ID
1383 , 0 AS COST
1384 , 0 AS CT_COST
1385 , 0 AS BILL_COST
1386 , 0 AS CT_BILL_COST
1387 , 0 AS BUDGET
1388 , 0 AS CT_BUDGET
1389 , 0 AS NON_BILL_COST
1390 , 0 AS CT_NON_BILL_COST
1391 FROM PJI_PMV_ET_RT_DIM_TMP ET
1392 WHERE ET.NAME <> '-1'
1393 ) WHERE 1=1
1394 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
1395 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
1396
1397 ELSIF l_Convert_Classification = 'Y'
1398 AND l_Convert_Expenditure_Type = 'N'
1399 AND l_Convert_Work_Type = 'Y'
1400 THEN
1401 SELECT PJI_REP_PC10
1402 ( ORG_ID
1403 , ORGANIZATION_ID
1404 , PROJECT_CLASS_ID
1405 , EXPENDITURE_CATEGORY
1406 , EXPENDITURE_TYPE_ID
1407 , WORK_TYPE_ID
1408 , SUM ( COST )
1409 , SUM ( CT_COST )
1410 , NULL
1411 , SUM ( BILL_COST )
1412 , SUM ( CT_BILL_COST )
1413 , NULL
1414 , NULL
1415 , NULL
1416 , NULL
1417 , SUM ( BUDGET )
1418 , SUM ( CT_BUDGET )
1419 , NULL
1420 , NULL
1421 , NULL
1422 , NULL
1426 , NULL , NULL , NULL , NULL , NULL, NULL
1423 , SUM ( NON_BILL_COST )
1424 , SUM ( CT_NON_BILL_COST )
1425 , NULL
1427 , NULL , NULL , NULL , NULL , NULL, NULL
1428 , NULL , NULL , NULL , NULL , NULL, NULL )
1429 BULK COLLECT INTO l_lines_tab
1430 FROM
1431 ( SELECT /*+ ORDERED */
1432 HOU.NAME AS ORG_ID
1433 , HORG.NAME AS ORGANIZATION_ID
1434 , CLS.NAME AS PROJECT_CLASS_ID
1435 , '-1' AS EXPENDITURE_CATEGORY
1436 , '-1' AS EXPENDITURE_TYPE_ID
1437 , WT.NAME AS WORK_TYPE_ID
1438 , DECODE(TIME.amount_type,1,
1439 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1440 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
1441 , 0 CT_COST
1442 , DECODE(TIME.amount_type,1,
1443 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1444 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
1445 , 0 CT_BILL_COST
1446 , NULL AS BUDGET
1447 , NULL AS CT_BUDGET
1448 , DECODE(TIME.amount_type,1,
1449 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1450 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
1451 , 0 CT_NON_BILL_COST
1452 FROM
1453 PJI_PMV_TIME_DIM_TMP TIME
1454 , PJI_PMV_ORGZ_DIM_TMP HORG
1455 , PJI_PMV_CLS_DIM_TMP CLS
1456 , PJI_FP_CLSO_ET_WT_F_MV FCT
1457 , PJI_PMV_WT_DIM_TMP WT
1458 , PJI_PMV_ORG_DIM_TMP HOU
1459 WHERE
1460 FCT.PROJECT_ORG_ID = HOU.ID
1461 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1462 AND FCT.TIME_ID = TIME.ID
1463 AND TIME.ID IS NOT NULL
1464 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1465 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1466 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1467 AND WORK_TYPE_ID= WT.ID
1468 and FCT.PROJECT_CLASS_ID = CLS.ID
1469 UNION ALL -- PRIOR Actuals
1470 SELECT /*+ ORDERED */
1471 HOU.NAME AS ORG_ID
1472 , HORG.NAME AS ORGANIZATION_ID
1473 , CLS.NAME AS PROJECT_CLASS_ID
1474 , '-1' AS EXPENDITURE_CATEGORY
1475 , '-1' AS EXPENDITURE_TYPE_ID
1476 , WT.NAME AS WORK_TYPE_ID
1480 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
1477 , 0 AS COST
1478 , DECODE(TIME.amount_type,1,
1479 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1481 , 0 AS BILL_COST
1482 , DECODE(TIME.amount_type,1,
1483 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1484 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
1485 , NULL AS BUDGET
1486 , NULL AS CT_BUDGET
1487 , 0 AS NON_BILL_COST
1488 , DECODE(TIME.amount_type,1,
1489 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1490 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
1491 FROM
1492 PJI_PMV_TCMP_DIM_TMP TIME
1493 , PJI_PMV_ORGZ_DIM_TMP HORG
1494 , PJI_PMV_CLS_DIM_TMP CLS
1495 , PJI_FP_CLSO_ET_WT_F_MV FCT
1496 , PJI_PMV_WT_DIM_TMP WT
1497 , PJI_PMV_ORG_DIM_TMP HOU
1498 WHERE
1499 FCT.PROJECT_ORG_ID = HOU.ID
1500 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1501 AND FCT.TIME_ID = TIME.ID
1502 AND TIME.ID IS NOT NULL
1503 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1504 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1505 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1506 AND WORK_TYPE_ID = WT.ID
1507 and FCT.PROJECT_CLASS_ID = CLS.ID
1508 UNION ALL -- FORCE Creation of Org rows
1509 SELECT HOU.NAME AS ORG_ID
1510 , '-1' AS ORGANIZATION_ID
1511 , '-1' AS PROJECT_CLASS_ID
1512 , '-1' AS EXPENDITURE_CATEGORY
1513 , '-1' AS EXPENDITURE_TYPE_ID
1514 , '-1' AS WORK_TYPE_ID
1515 , 0 AS COST
1516 , 0 AS CT_COST
1517 , 0 AS BILL_COST
1518 , 0 AS CT_BILL_COST
1519 , 0 AS BUDGET
1520 , 0 AS CT_BUDGET
1521 , 0 AS NON_BILL_COST
1522 , 0 AS CT_NON_BILL_COST
1523 FROM PJI_PMV_ORG_DIM_TMP HOU
1524 WHERE HOU.NAME <> '-1'
1525 UNION ALL -- FORCE Creation of Organization Rows
1526 SELECT '-1' AS ORG_ID
1527 , HORG.NAME AS ORGANIZATION_ID
1528 , '-1' AS PROJECT_CLASS_ID
1529 , '-1' as EXPENDITURE_CATEGORY
1530 , '-1' as EXPENDITURE_TYPE_ID
1531 , '-1' as WORK_TYPE_ID
1532 , 0 AS COST
1533 , 0 AS CT_COST
1534 , 0 AS BILL_COST
1535 , 0 AS CT_BILL_COST
1536 , 0 AS BUDGET
1537 , 0 AS CT_BUDGET
1538 , 0 AS NON_BILL_COST
1539 , 0 AS CT_NON_BILL_COST
1540 FROM PJI_PMV_ORGZ_DIM_TMP HORG
1541 WHERE HORG.NAME <> '-1'
1542 UNION ALL
1543 SELECT '-1' AS ORG_ID
1544 , '-1' AS ORGANIZATION_ID
1545 , '-1' AS PROJECT_CLASS_ID
1546 , '-1' AS EXPENDITURE_CATEGORY
1547 , '-1' AS EXPENDITURE_TYPE_ID
1548 , WT.NAME AS WORK_TYPE_ID
1549 , 0 AS COST
1550 , 0 AS CT_COST
1551 , 0 AS BILL_COST
1552 , 0 AS CT_BILL_COST
1553 , 0 AS BUDGET
1554 , 0 AS CT_BUDGET
1555 , 0 AS NON_BILL_COST
1556 , 0 AS CT_NON_BILL_COST
1557 FROM PJI_PMV_WT_DIM_TMP WT
1558 WHERE WT.NAME <> '-1'
1559 UNION ALL
1560 SELECT '-1' AS ORG_ID
1561 , '-1' AS ORGANIZATION_ID
1562 , CLS.NAME AS PROJECT_CLASS_ID
1563 , '-1' AS EXPENDITURE_CATEGORY
1564 , '-1' AS EXPENDITURE_TYPE_ID
1565 , '-1' AS WORK_TYPE_ID
1566 , 0 AS COST
1567 , 0 AS CT_COST
1568 , 0 AS BILL_COST
1569 , 0 AS CT_BILL_COST
1570 , 0 AS BUDGET
1571 , 0 AS CT_BUDGET
1572 , 0 AS NON_BILL_COST
1573 , 0 AS CT_NON_BILL_COST
1574 FROM PJI_PMV_CLS_DIM_TMP CLS
1575 WHERE CLS.NAME <> '-1'
1576 ) WHERE 1=1
1577 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
1578 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
1579 ELSE
1580
1581 SELECT PJI_REP_PC10
1582 ( ORG_ID
1583 , ORGANIZATION_ID
1584 , PROJECT_CLASS_ID
1585 , EXPENDITURE_CATEGORY
1586 , EXPENDITURE_TYPE_ID
1587 , WORK_TYPE_ID
1588 , SUM ( COST )
1589 , SUM ( CT_COST )
1590 , NULL
1591 , SUM ( BILL_COST )
1592 , SUM ( CT_BILL_COST )
1593 , NULL
1594 , NULL
1595 , NULL
1596 , NULL
1597 , SUM ( BUDGET )
1598 , SUM ( CT_BUDGET )
1599 , NULL
1603 , SUM ( NON_BILL_COST )
1600 , NULL
1601 , NULL
1602 , NULL
1604 , SUM ( CT_NON_BILL_COST )
1605 , NULL
1606 , NULL , NULL , NULL , NULL , NULL, NULL
1607 , NULL , NULL , NULL , NULL , NULL, NULL
1608 , NULL , NULL , NULL , NULL , NULL, NULL )
1609 BULK COLLECT INTO l_lines_tab
1610 FROM
1611 ( SELECT /*+ ORDERED */
1612 HOU.NAME AS ORG_ID
1613 , HORG.NAME AS ORGANIZATION_ID
1614 , CLS.NAME AS PROJECT_CLASS_ID
1615 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1616 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1617 , WT.NAME AS WORK_TYPE_ID
1618 , DECODE(TIME.amount_type,1,
1619 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1620 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS COST
1621 , 0 CT_COST
1622 , DECODE(TIME.amount_type,1,
1623 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1624 'RC', FCT.BILL_RAW_COST, 0),0) AS BILL_COST
1625 , 0 CT_BILL_COST
1626 , NULL AS BUDGET
1627 , NULL AS CT_BUDGET
1628 , DECODE(TIME.amount_type,1,
1629 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1630 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS NON_BILL_COST
1631 , 0 CT_NON_BILL_COST
1632 FROM
1633 PJI_PMV_TIME_DIM_TMP TIME
1634 , PJI_PMV_ORGZ_DIM_TMP HORG
1635 , PJI_PMV_ET_RT_DIM_TMP ET
1636 , PJI_FP_CLSO_ET_WT_F_MV FCT
1637 , PJI_PMV_CLS_DIM_TMP CLS
1638 , PJI_PMV_WT_DIM_TMP WT
1639 , PJI_PMV_ORG_DIM_TMP HOU
1640 WHERE
1641 FCT.PROJECT_ORG_ID = HOU.ID
1642 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1643 AND FCT.TIME_ID = TIME.ID
1644 AND TIME.ID IS NOT NULL
1645 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1646 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1647 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1648 AND WORK_TYPE_ID= WT.ID
1649 and FCT.PROJECT_CLASS_ID = CLS.ID
1650 and FCT.EXP_EVT_TYPE_ID = ET.ID
1651 and ET.record_type = 'ET'
1652 UNION ALL -- PRIOR Actuals
1653 SELECT /*+ ORDERED */
1654 HOU.NAME AS ORG_ID
1655 , HORG.NAME AS ORGANIZATION_ID
1656 , CLS.NAME AS PROJECT_CLASS_ID
1657 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1658 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1659 , WT.NAME AS WORK_TYPE_ID
1660 , 0 AS COST
1661 , DECODE(TIME.amount_type,1,
1662 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST,
1663 'RC', FCT.CONTRACT_RAW_COST, 0),0) AS CT_COST
1664 , 0 AS BILL_COST
1665 , DECODE(TIME.amount_type,1,
1666 DECODE(G_Report_Cost_Type, 'BC', FCT.BILL_BURDENED_COST,
1667 'RC', FCT.BILL_RAW_COST, 0),0) AS CT_BILL_COST
1668 , NULL AS BUDGET
1669 , NULL AS CT_BUDGET
1670 , 0 AS NON_BILL_COST
1671 , DECODE(TIME.amount_type,1,
1672 DECODE(G_Report_Cost_Type, 'BC', FCT.CONTRACT_BRDN_COST - FCT.BILL_BURDENED_COST,
1673 'RC', FCT.CONTRACT_RAW_COST - FCT.BILL_RAW_COST,0), 0) AS CT_NON_BILL_COST
1674 FROM
1675 PJI_PMV_TCMP_DIM_TMP TIME
1676 , PJI_PMV_ORGZ_DIM_TMP HORG
1677 , PJI_PMV_ET_RT_DIM_TMP ET
1678 , PJI_FP_CLSO_ET_WT_F_MV FCT
1679 , PJI_PMV_CLS_DIM_TMP CLS
1680 , PJI_PMV_WT_DIM_TMP WT
1681 , PJI_PMV_ORG_DIM_TMP HOU
1682 WHERE
1683 FCT.PROJECT_ORG_ID = HOU.ID
1684 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
1685 AND FCT.TIME_ID = TIME.ID
1686 AND TIME.ID IS NOT NULL
1687 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1688 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1689 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
1690 AND WORK_TYPE_ID = WT.ID
1691 and FCT.PROJECT_CLASS_ID = CLS.ID
1692 and FCT.EXP_EVT_TYPE_ID = ET.ID
1693 and ET.record_type = 'ET'
1694 UNION ALL -- FORCE Creation of Org rows
1695 SELECT HOU.NAME AS ORG_ID
1696 , '-1' AS ORGANIZATION_ID
1697 , '-1' AS PROJECT_CLASS_ID
1698 , '-1' AS EXPENDITURE_CATEGORY
1699 , '-1' AS EXPENDITURE_TYPE_ID
1700 , '-1' AS WORK_TYPE_ID
1701 , 0 AS COST
1702 , 0 AS CT_COST
1703 , 0 AS BILL_COST
1704 , 0 AS CT_BILL_COST
1705 , 0 AS BUDGET
1706 , 0 AS CT_BUDGET
1710 WHERE HOU.NAME <> '-1'
1707 , 0 AS NON_BILL_COST
1708 , 0 AS CT_NON_BILL_COST
1709 FROM PJI_PMV_ORG_DIM_TMP HOU
1711 UNION ALL -- FORCE Creation of Organization Rows
1712 SELECT '-1' AS ORG_ID
1713 , HORG.NAME AS ORGANIZATION_ID
1714 , '-1' AS PROJECT_CLASS_ID
1715 , '-1' as EXPENDITURE_CATEGORY
1716 , '-1' as EXPENDITURE_TYPE_ID
1717 , '-1' as WORK_TYPE_ID
1718 , 0 AS COST
1719 , 0 AS CT_COST
1720 , 0 AS BILL_COST
1721 , 0 AS CT_BILL_COST
1722 , 0 AS BUDGET
1723 , 0 AS CT_BUDGET
1724 , 0 AS NON_BILL_COST
1725 , 0 AS CT_NON_BILL_COST
1726 FROM PJI_PMV_ORGZ_DIM_TMP HORG
1727 WHERE HORG.NAME <> '-1'
1728 UNION ALL
1729 SELECT '-1' AS ORG_ID
1730 , '-1' AS ORGANIZATION_ID
1731 , '-1' AS PROJECT_CLASS_ID
1732 , decode(p_view_by, 'EC', ET.name, '-1') AS EXPENDITURE_CATEGORY
1733 , decode(p_view_by, 'ET', ET.name, '-1') AS EXPENDITURE_TYPE_ID
1734 , '-1' AS WORK_TYPE_ID
1735 , 0 AS COST
1736 , 0 AS CT_COST
1737 , 0 AS BILL_COST
1738 , 0 AS CT_BILL_COST
1739 , 0 AS BUDGET
1740 , 0 AS CT_BUDGET
1741 , 0 AS NON_BILL_COST
1742 , 0 AS CT_NON_BILL_COST
1743 FROM PJI_PMV_ET_RT_DIM_TMP ET
1744 WHERE ET.NAME <> '-1'
1745 UNION ALL
1746 SELECT '-1' AS ORG_ID
1747 , '-1' AS ORGANIZATION_ID
1748 , '-1' AS PROJECT_CLASS_ID
1749 , '-1' AS EXPENDITURE_CATEGORY
1750 , '-1' AS EXPENDITURE_TYPE_ID
1751 , WT.NAME AS WORK_TYPE_ID
1752 , 0 AS COST
1753 , 0 AS CT_COST
1754 , 0 AS BILL_COST
1755 , 0 AS CT_BILL_COST
1756 , 0 AS BUDGET
1757 , 0 AS CT_BUDGET
1758 , 0 AS NON_BILL_COST
1759 , 0 AS CT_NON_BILL_COST
1760 FROM PJI_PMV_WT_DIM_TMP WT
1761 WHERE WT.NAME <> '-1'
1762
1763 UNION ALL
1764 SELECT '-1' AS ORG_ID
1765 , '-1' AS ORGANIZATION_ID
1766 , CLS.NAME AS PROJECT_CLASS_ID
1767 , '-1' AS EXPENDITURE_CATEGORY
1768 , '-1' AS EXPENDITURE_TYPE_ID
1769 , '-1' AS WORK_TYPE_ID
1770 , 0 AS COST
1771 , 0 AS CT_COST
1772 , 0 AS BILL_COST
1773 , 0 AS CT_BILL_COST
1774 , 0 AS BUDGET
1775 , 0 AS CT_BUDGET
1776 , 0 AS NON_BILL_COST
1777 , 0 AS CT_NON_BILL_COST
1778 FROM PJI_PMV_CLS_DIM_TMP CLS
1779 WHERE CLS.NAME <> '-1'
1780 ) WHERE 1=1
1781 GROUP BY ORG_ID, ORGANIZATION_ID, PROJECT_CLASS_ID, EXPENDITURE_CATEGORY,
1782 EXPENDITURE_TYPE_ID,WORK_TYPE_ID;
1783
1784 END IF;
1785
1786 FOR i in 1..l_lines_tab.COUNT
1787 LOOP
1788 IF p_View_By = 'OG' THEN
1789 IF l_lines_tab(i).ORGANIZATION_ID = l_Top_Organization_Name THEN
1790 l_Top_Org_Index:=i;
1791
1792 l_TO_Cost := NVL(l_lines_tab(i).COST,0);
1793 l_TO_CT_Cost := NVL(l_lines_tab(i).CT_COST,0);
1794 l_TO_Bill_Cost := NVL(l_lines_tab(i).BILL_COST,0);
1795 l_TO_CT_Bill_Cost := NVL(l_lines_tab(i).CT_BILL_COST,0);
1796 l_TO_Budget := NVL(l_lines_tab(i).BUDGET,0);
1797 l_TO_CT_Budget := NVL(l_lines_tab(i).CT_BUDGET,0);
1798 l_TO_Non_Bill_Cost := NVL(l_lines_tab(i).NON_BILL_COST,0);
1799 l_TO_CT_Non_Bill_Cost := NVL(l_lines_tab(i).CT_NON_BILL_COST,0);
1800
1801 ELSE
1802
1803 l_Cost:=l_Cost + NVL(l_lines_tab(i).COST,0);
1804 l_CT_Cost:=l_CT_Cost + NVL(l_lines_tab(i).CT_COST,0);
1805
1806 l_Bill_Cost :=l_Bill_Cost + NVL(l_lines_tab(i).BILL_COST,0);
1807 l_CT_Bill_Cost :=l_CT_Bill_Cost + NVL(l_lines_tab(i).CT_BILL_COST,0);
1808
1809 l_Budget :=l_Budget + NVL(l_lines_tab(i).BUDGET,0);
1810 l_CT_Budget :=l_CT_Budget + NVL(l_lines_tab(i).CT_BUDGET,0);
1811
1812 l_Non_Bill_Cost :=l_Non_Bill_Cost + NVL(l_lines_tab(i).NON_BILL_COST,0);
1813 l_CT_Non_Bill_Cost :=l_CT_Non_Bill_Cost + NVL(l_lines_tab(i).CT_NON_BILL_COST,0);
1814
1815
1816 END IF;
1817 ELSE
1818 l_Cost :=l_Cost + NVL(l_lines_tab(i).COST,0);
1819 l_CT_Cost :=l_CT_Cost + NVL(l_lines_tab(i).CT_COST,0);
1820 l_Bill_Cost :=l_Bill_Cost + NVL(l_lines_tab(i).BILL_COST,0);
1821 l_CT_Bill_Cost :=l_CT_Bill_Cost + NVL(l_lines_tab(i).CT_BILL_COST,0);
1822 l_Budget :=l_Budget + NVL(l_lines_tab(i).BUDGET,0);
1823 l_CT_Budget :=l_CT_Budget + NVL(l_lines_tab(i).CT_BUDGET,0);
1824 l_Non_Bill_Cost :=l_Non_Bill_Cost + NVL(l_lines_tab(i).NON_BILL_COST,0);
1825 l_CT_Non_Bill_Cost :=l_CT_Non_Bill_Cost + NVL(l_lines_tab(i).CT_NON_BILL_COST,0);
1826
1827 END IF;
1828
1829 IF NVL(l_lines_tab(i).CT_COST, 0) <> 0 THEN
1830 l_lines_tab(i).COST_CHANGE_PRCNT := 100 * (l_lines_tab(i).COST -
1831 l_lines_tab(i).CT_COST) / ABS( l_lines_tab(i).CT_COST);
1832 ELSE
1833 l_lines_tab(i).COST_CHANGE_PRCNT := NULL;
1837 l_lines_tab(i).BILL_COST_CHANGE_PRCNT := 100 * (l_lines_tab(i).BILL_COST -
1834 END IF;
1835
1836 IF NVL(l_lines_tab(i).CT_BILL_COST, 0) <> 0 THEN
1838 l_lines_tab(i).CT_BILL_COST) / ABS( l_lines_tab(i).CT_BILL_COST);
1839 ELSE
1840 l_lines_tab(i).BILL_COST_CHANGE_PRCNT := NULL;
1841 END IF;
1842
1843 IF NVL(l_lines_tab(i).COST, 0) <> 0 THEN
1844 l_lines_tab(i).BILL_PRCNT_OF_COST := 100 * (l_lines_tab(i).BILL_COST) /
1845 ABS( l_lines_tab(i).COST);
1846 ELSE
1847 l_lines_tab(i).BILL_PRCNT_OF_COST := NULL;
1848 END IF;
1849
1850 IF NVL(l_lines_tab(i).CT_COST, 0) <> 0 THEN
1851 l_lines_tab(i).CT_BILL_PRCNT_OF_COST := 100 * (l_lines_tab(i).CT_BILL_COST) /
1852 ABS( l_lines_tab(i).CT_COST);
1853 ELSE
1854 l_lines_tab(i).CT_BILL_PRCNT_OF_COST := NULL;
1855 END IF;
1856
1857
1858 l_lines_tab(i).PRCNT_OF_COST_CHANGE := l_lines_tab(i).BILL_PRCNT_OF_COST -
1859 l_lines_tab(i).CT_BILL_PRCNT_OF_COST ;
1860
1861
1862 IF NVL(l_lines_tab(i).CT_BUDGET, 0) <> 0 THEN
1863 l_lines_tab(i).BUDGET_CHANGE_PRCNT := 100 * (l_lines_tab(i).BUDGET -
1864 l_lines_tab(i).CT_BUDGET) / ABS( l_lines_tab(i).CT_BUDGET);
1865 ELSE
1866 l_lines_tab(i).BUDGET_CHANGE_PRCNT := NULL;
1867 END IF;
1868
1869 IF NVL(l_lines_tab(i).BUDGET, 0) <> 0 THEN
1870 l_lines_tab(i).BILL_COST_PRCNT_OF_BUDGET := 100 *
1871 (l_lines_tab(i).BILL_COST) / ABS( l_lines_tab(i).BUDGET);
1872 ELSE
1873 l_lines_tab(i).BILL_COST_PRCNT_OF_BUDGET := NULL;
1874 END IF;
1875
1876 IF NVL(l_lines_tab(i).CT_BUDGET, 0) <> 0 THEN
1877 l_lines_tab(i).CT_BILL_COST_PRCNT_OF_BUDGET := 100 *
1878 (l_lines_tab(i).CT_BILL_COST) / ABS( l_lines_tab(i).CT_BUDGET);
1879 ELSE
1880 l_lines_tab(i).CT_BILL_COST_PRCNT_OF_BUDGET := NULL;
1881 END IF;
1882
1883 l_lines_tab(i).CHANGE :=
1884 l_lines_tab(i).BILL_COST_PRCNT_OF_BUDGET - l_lines_tab(i).CT_BILL_COST_PRCNT_OF_BUDGET;
1885
1886
1887 IF NVL(l_lines_tab(i).CT_NON_BILL_COST, 0) <> 0 THEN
1888 l_lines_tab(i).NON_BILL_COST_CHANGE_PRCNT := 100 * (l_lines_tab(i).NON_BILL_COST -
1889 l_lines_tab(i).CT_NON_BILL_COST) / ABS( l_lines_tab(i).CT_NON_BILL_COST);
1890 ELSE
1891 l_lines_tab(i).NON_BILL_COST_CHANGE_PRCNT := NULL;
1892 END IF;
1893 END LOOP;
1894
1895
1896 IF p_View_By = 'OG' THEN
1897
1898 l_lines_tab(l_Top_Org_Index).COST
1899 :=NVL(l_lines_tab(l_Top_Org_Index).COST,0)-l_Cost;
1900 l_lines_tab(l_Top_Org_Index).CT_COST
1901 :=NVL(l_lines_tab(l_Top_Org_Index).CT_COST,0)-l_CT_Cost;
1902
1903 l_lines_tab(l_Top_Org_Index).BILL_COST
1904 :=NVL(l_lines_tab(l_Top_Org_Index).BILL_COST,0)-l_Bill_Cost;
1905 l_lines_tab(l_Top_Org_Index).CT_BILL_COST
1906 :=NVL(l_lines_tab(l_Top_Org_Index).CT_BILL_COST,0)-l_CT_Bill_Cost;
1907
1908 l_lines_tab(l_Top_Org_Index).BUDGET
1909 :=NVL(l_lines_tab(l_Top_Org_Index).BUDGET,0)-l_Budget;
1910 l_lines_tab(l_Top_Org_Index).CT_BUDGET
1911 :=NVL(l_lines_tab(l_Top_Org_Index).CT_BUDGET,0)-l_CT_Budget;
1912
1913 l_lines_tab(l_Top_Org_Index).NON_BILL_COST
1914 :=NVL(l_lines_tab(l_Top_Org_Index).NON_BILL_COST,0)-l_Non_Bill_Cost;
1915 l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST
1916 :=NVL(l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST,0)-l_CT_Non_Bill_Cost;
1917
1918
1919 -- 1, 4 *************************************************************************************--
1920 IF NVL(l_lines_tab(l_Top_Org_Index).CT_COST, 0) <> 0 THEN
1921 l_lines_tab(l_Top_Org_Index).COST_CHANGE_PRCNT := 100 * (l_lines_tab(l_Top_Org_Index).COST -
1922 l_lines_tab(l_Top_Org_Index).CT_COST) / ABS( l_lines_tab(l_Top_Org_Index).CT_COST);
1923
1924 l_lines_tab(l_Top_Org_Index).CT_BILL_PRCNT_OF_COST := 100 * (l_lines_tab(l_Top_Org_Index).CT_BILL_COST) /
1925 ABS( l_lines_tab(l_Top_Org_Index).CT_COST);
1926
1927 ELSE
1928 l_lines_tab(l_Top_Org_Index).COST_CHANGE_PRCNT := NULL;
1929 l_lines_tab(l_Top_Org_Index).CT_BILL_PRCNT_OF_COST := NULL;
1930
1931 END IF;
1932 -- 2 ************************************************************************************--
1933 IF NVL(l_lines_tab(l_Top_Org_Index).CT_BILL_COST, 0) <> 0 THEN
1934 l_lines_tab(l_Top_Org_Index).BILL_COST_CHANGE_PRCNT := 100 * (l_lines_tab(l_Top_Org_Index).BILL_COST -
1935 l_lines_tab(l_Top_Org_Index).CT_BILL_COST) / ABS( l_lines_tab(l_Top_Org_Index).CT_BILL_COST);
1936 ELSE
1937 l_lines_tab(l_Top_Org_Index).BILL_COST_CHANGE_PRCNT := NULL;
1938 END IF;
1939 -- 3 *************************************************************************************--
1940 IF NVL(l_lines_tab(l_Top_Org_Index).COST, 0) <> 0 THEN
1941 l_lines_tab(l_Top_Org_Index).BILL_PRCNT_OF_COST := 100 * (l_lines_tab(l_Top_Org_Index).BILL_COST) /
1942 ABS( l_lines_tab(l_Top_Org_Index).COST);
1943 ELSE
1944 l_lines_tab(l_Top_Org_Index).BILL_PRCNT_OF_COST := NULL;
1945 END IF;
1946 -- 5 *************************************************************************************--
1947
1948 l_lines_tab(l_Top_Org_Index).PRCNT_OF_COST_CHANGE := l_lines_tab(l_Top_Org_Index).BILL_PRCNT_OF_COST -
1949 l_lines_tab(l_Top_Org_Index).CT_BILL_PRCNT_OF_COST ;
1950
1951 -- 6, 8 *************************************************************************************--
1955
1952 IF NVL(l_lines_tab(l_Top_Org_Index).CT_BUDGET, 0) <> 0 THEN
1953 l_lines_tab(l_Top_Org_Index).BUDGET_CHANGE_PRCNT := 100 * (l_lines_tab(l_Top_Org_Index).BUDGET -
1954 l_lines_tab(l_Top_Org_Index).CT_BUDGET) / ABS( l_lines_tab(l_Top_Org_Index).CT_BUDGET);
1956 l_lines_tab(l_Top_Org_Index).CT_BILL_COST_PRCNT_OF_BUDGET := 100 *
1957 (l_lines_tab(l_Top_Org_Index).CT_BILL_COST) / ABS( l_lines_tab(l_Top_Org_Index).CT_BUDGET);
1958
1959 ELSE
1960 l_lines_tab(l_Top_Org_Index).BUDGET_CHANGE_PRCNT := NULL;
1961 l_lines_tab(l_Top_Org_Index).BILL_COST_PRCNT_OF_BUDGET := NULL;
1962
1963 END IF;
1964 -- 7 *************************************************************************************--
1965 IF NVL(l_lines_tab(l_Top_Org_Index).BUDGET, 0) <> 0 THEN
1966 l_lines_tab(l_Top_Org_Index).BILL_COST_PRCNT_OF_BUDGET := 100 *
1967 (l_lines_tab(l_Top_Org_Index).BILL_COST) / ABS( l_lines_tab(l_Top_Org_Index).BUDGET);
1968 ELSE
1969 l_lines_tab(l_Top_Org_Index).BILL_COST_PRCNT_OF_BUDGET := NULL;
1970 END IF;
1971 -- 9 *************************************************************************************--
1972 l_lines_tab(l_Top_Org_Index).CHANGE :=
1973 l_lines_tab(l_Top_Org_Index).BILL_COST_PRCNT_OF_BUDGET - l_lines_tab(l_Top_Org_Index).CT_BILL_COST_PRCNT_OF_BUDGET;
1974 -- 10 *************************************************************************************--
1975 IF NVL(l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST, 0) <> 0 THEN
1976 l_lines_tab(l_Top_Org_Index).NON_BILL_COST_CHANGE_PRCNT := 100 * (l_lines_tab(l_Top_Org_Index).NON_BILL_COST -
1977 l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST) / ABS( l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST);
1978 ELSE
1979 l_lines_tab(l_Top_Org_Index).NON_BILL_COST_CHANGE_PRCNT := NULL;
1980 END IF;
1981 --*************************************************************************************--
1982
1983 IF NVL( l_lines_tab(l_Top_Org_Index).COST, 0 ) = 0
1984 AND NVL( l_lines_tab(l_Top_Org_Index).CT_COST, 0 ) = 0
1985 AND NVL( l_lines_tab(l_Top_Org_Index).BILL_COST, 0 ) = 0
1986 AND NVL( l_lines_tab(l_Top_Org_Index).CT_BILL_COST, 0 ) = 0
1987 AND NVL( l_lines_tab(l_Top_Org_Index).BUDGET, 0 ) = 0
1988 AND NVL( l_lines_tab(l_Top_Org_Index).CT_BUDGET, 0 ) = 0
1989 AND NVL( l_lines_tab(l_Top_Org_Index).NON_BILL_COST, 0 ) = 0
1990 AND NVL( l_lines_tab(l_Top_Org_Index).CT_NON_BILL_COST, 0 ) = 0
1991 THEN
1992 l_lines_tab.DELETE(l_Top_Org_Index);
1993 END IF;
1994
1995 l_Cost:=l_TO_Cost;
1996 l_CT_Cost:=l_TO_CT_Cost;
1997
1998 l_Bill_Cost:=l_TO_Bill_Cost;
1999 l_CT_Bill_Cost:=l_TO_CT_Bill_Cost;
2000
2001 l_Budget:=l_To_Budget;
2002 l_CT_Budget:=l_TO_CT_Budget;
2003
2004 l_Non_Bill_Cost:=l_TO_Non_Bill_Cost;
2005 l_CT_Non_Bill_Cost:=l_TO_CT_Non_Bill_Cost;
2006
2007 END IF;
2008
2009 IF l_lines_tab.COUNT > 0 THEN
2010 FOR i in l_lines_tab.FIRST..l_lines_tab.LAST
2011 LOOP
2012 IF l_lines_tab.EXISTS(i) THEN
2013 l_lines_tab(i).PJI_REP_TOTAL_1:=l_Cost;
2014 l_lines_tab(i).PJI_REP_TOTAL_2:=l_CT_Cost;
2015
2016 IF NVL(l_CT_Cost, 0) <> 0 THEN
2017 l_lines_tab(i).PJI_REP_TOTAL_3
2018 :=(l_Cost-l_CT_Cost)*100/ABS(l_CT_Cost);
2019 ELSE
2020 l_lines_tab(i).PJI_REP_TOTAL_3:=NULL;
2021 END IF;
2022
2023 l_lines_tab(i).PJI_REP_TOTAL_4:=l_Bill_Cost;
2024 l_lines_tab(i).PJI_REP_TOTAL_5:=l_CT_Bill_Cost;
2025
2026 IF NVL(l_CT_Bill_Cost, 0) <> 0 THEN
2027 l_lines_tab(i).PJI_REP_TOTAL_6
2028 :=(l_Bill_Cost-l_CT_Bill_Cost)*100/ABS(l_CT_Bill_Cost);
2029 ELSE
2030 l_lines_tab(i).PJI_REP_TOTAL_6:=NULL;
2031 END IF;
2032
2033 IF NVL(l_Bill_Cost, 0) <> 0 THEN
2034 l_lines_tab(i).PJI_REP_TOTAL_7
2035 :=(l_Bill_Cost)*100/ABS(l_Cost);
2036 ELSE
2037 l_lines_tab(i).PJI_REP_TOTAL_7:=NULL;
2038 END IF;
2039
2040 IF NVL(l_CT_Bill_Cost, 0) <> 0 THEN
2041 l_lines_tab(i).PJI_REP_TOTAL_8
2042 :=(l_CT_Bill_Cost)*100/ABS(l_CT_Cost);
2043 ELSE
2044 l_lines_tab(i).PJI_REP_TOTAL_8:=NULL;
2045 END IF;
2046
2047 l_lines_tab(i).PJI_REP_TOTAL_9:= l_lines_tab(i).PJI_REP_TOTAL_7 -
2048 l_lines_tab(i).PJI_REP_TOTAL_8;
2049
2050 l_lines_tab(i).PJI_REP_TOTAL_10:= l_Budget;
2051 l_lines_tab(i).PJI_REP_TOTAL_11:= l_CT_Budget;
2052
2053 IF NVL(l_CT_Budget, 0) <> 0 THEN
2054 l_lines_tab(i).PJI_REP_TOTAL_12
2055 :=(l_Budget-l_CT_Budget)*100/ABS(l_CT_Budget);
2056 ELSE
2057 l_lines_tab(i).PJI_REP_TOTAL_12:=NULL;
2058 END IF;
2059
2060
2061 IF NVL(l_Budget, 0) <> 0 THEN
2062 l_lines_tab(i).PJI_REP_TOTAL_13
2063 :=(l_Bill_Cost)*100/ABS(l_Budget);
2064 ELSE
2065 l_lines_tab(i).PJI_REP_TOTAL_13:=NULL;
2066 END IF;
2067
2068
2069 IF NVL(l_CT_Budget, 0) <> 0 THEN
2070 l_lines_tab(i).PJI_REP_TOTAL_14
2071 :=(l_CT_Bill_Cost)*100/ABS(l_CT_Budget);
2072 ELSE
2073 l_lines_tab(i).PJI_REP_TOTAL_14:=NULL;
2074 END IF;
2075
2076
2077 l_lines_tab(i).PJI_REP_TOTAL_15:=l_lines_tab(i).PJI_REP_TOTAL_13 -
2078 l_lines_tab(i).PJI_REP_TOTAL_14;
2079
2080 l_lines_tab(i).PJI_REP_TOTAL_16:=l_Non_Bill_Cost;
2081 l_lines_tab(i).PJI_REP_TOTAL_17:=l_CT_Non_Bill_Cost;
2082
2083 IF NVL(l_CT_Non_Bill_Cost, 0) <> 0 THEN
2087 l_lines_tab(i).PJI_REP_TOTAL_18:=NULL;
2084 l_lines_tab(i).PJI_REP_TOTAL_18
2085 :=(l_Non_Bill_Cost-l_CT_Non_Bill_Cost)*100/ABS(l_CT_Non_Bill_Cost);
2086 ELSE
2088 END IF;
2089
2090
2091 END IF;
2092 END LOOP;
2093 END IF;
2094
2095 /*
2096 ** ---------------------------------------------------+
2097 ** -- Return the bulk collected table back to pmv.-+
2098 ** ---------------------------------------------------+
2099 */
2100 COMMIT;
2101 RETURN l_lines_tab;
2102 END PLSQLDriver_PJI_REP_PC10;
2103
2104
2105 /*
2106 ** Contract Projects Trend Report and Contract Cumulative Trend Report
2107 */
2108
2109 FUNCTION PLSQLDriver_PJI_REP_PC11(
2110 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
2111 , p_Organization IN VARCHAR2
2112 , p_Currency_Type IN VARCHAR2
2113 , p_As_Of_Date IN NUMBER
2114 , p_Period_Type IN VARCHAR2
2115 , p_View_BY IN VARCHAR2
2116 , p_Classifications IN VARCHAR2 DEFAULT NULL
2117 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
2118 , p_Report_Type IN VARCHAR2 DEFAULT NULL
2119 , p_Expenditure_Category IN VARCHAR2 DEFAULT NULL
2120 , p_Expenditure_Type IN VARCHAR2 DEFAULT NULL
2121 , p_Work_Type IN VARCHAR2 DEFAULT NULL
2122 )RETURN PJI_REP_PC11_TBL
2123 IS
2124 PRAGMA AUTONOMOUS_TRANSACTION;
2125
2126 l_Project_Cost_Trend_Tab PJI_REP_PC11_TBL:=PJI_REP_PC11_TBL();
2127
2128 l_Parse_Class_Codes VARCHAR2(1);
2129 l_Report_Cost_Type VARCHAR2(2);
2130
2131 l_Cost NUMBER:=0;
2132 l_CT_Cost NUMBER:=0;
2133 l_Cost_Change_Prcnt NUMBER:=0;
2134
2135 l_Bill_Cost NUMBER:=0;
2136 l_CT_Bill_Cost NUMBER:=0;
2137 l_Bill_Cost_Change_Prcnt NUMBER:=0;
2138
2139 l_Bill_Prcnt_Of_Cost NUMBER:=0;
2140 l_CT_Bill_Prcnt_Of_Cost NUMBER:=0;
2141 l_Prcnt_Of_Cost_Change NUMBER:=0;
2142
2143 l_Budget NUMBER:=0;
2144 l_CT_Budget NUMBER:=0;
2145 l_Budget_Change_Prcnt NUMBER:=0;
2146
2147 l_Bill_Cost_Prcnt_Of_Budget NUMBER:=0;
2148 l_CT_Bill_Cost_Prcnt_Of_Budget NUMBER:=0;
2149 l_Change NUMBER:=0;
2150
2151 l_Non_Bill_Cost NUMBER:=0;
2152 l_CT_Non_Bill_Cost NUMBER:=0;
2153 l_Non_Bill_Cost_Change_Prcnt NUMBER:=0;
2154
2155 l_Top_Organization_Name VARCHAR2(240);
2156
2157 l_Convert_Classification VARCHAR2(1);
2158 l_Convert_Expenditure_Type VARCHAR2(1);
2159 l_Convert_Work_Type VARCHAR2(1);
2160 l_curr_record_type_id NUMBER:= 1;
2161
2162 BEGIN
2163 BEGIN
2164 SELECT report_cost_type
2165 INTO l_Report_Cost_Type
2166 FROM pji_system_settings;
2167 EXCEPTION
2168 WHEN NO_DATA_FOUND THEN
2169 l_Report_Cost_Type:='RC';
2170 END;
2171
2172 /*
2173 ** Place a call to all the parse API's which parse the
2174 ** parameters passed by PMV and populate all the
2175 ** temporary tables.
2176 */
2177 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit_IDS=>p_Operating_Unit, p_View_BY=>p_View_BY);
2178 PJI_PMV_ENGINE.Convert_Organization(p_Top_Organization_ID=>p_Organization, p_View_BY=>p_View_BY, p_Top_Organization_Name=>l_Top_Organization_Name);
2179 PJI_PMV_ENGINE.Convert_Time(p_As_Of_Date => p_As_Of_Date,
2180 p_Period_Type => p_Period_Type,
2181 p_View_BY => p_View_BY,
2182 p_Parse_Prior => 'Y',
2183 p_Report_Type => p_Report_Type,
2184 p_Comparator => NULL,
2185 p_Parse_ITD => NULL,
2186 p_Full_Period_Flag => 'Y');
2187
2188
2189 l_Convert_Classification := PJI_PMV_ENGINE.Convert_Classification
2190 (p_Classifications, p_Class_Codes, p_View_BY);
2191
2192 l_Convert_Expenditure_Type := PJI_PMV_ENGINE.Convert_Expenditure_Type
2193 (p_Expenditure_Category, p_Expenditure_Type, p_View_BY);
2197 /*
2194 l_Convert_Work_Type := PJI_PMV_ENGINE.Convert_Work_Type(p_Work_Type, p_View_BY);
2195
2196 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
2198 ** ORGANIZATION Processing: No parameter other than Organization is specified
2199 */
2200
2201 IF (l_Convert_Classification ='N')
2202 AND (l_Convert_Expenditure_Type = 'N')
2203 AND (l_Convert_Work_Type = 'N')
2204 THEN
2205 SELECT PJI_REP_PC11(
2206 TIME_ID
2207 , SUM( COST )
2208 , SUM( CT_COST )
2209 , NULL
2210 , SUM( BILL_COST )
2211 , SUM( CT_BILL_COST )
2212 , NULL
2213 , NULL
2214 , NULL
2215 , NULL
2216 , SUM ( BUDGET )
2217 , SUM ( CT_BUDGET )
2218 , NULL
2219 , NULL
2220 , NULL
2221 , NULL
2222 , SUM( NON_BILL_COST )
2223 , SUM( CT_Non_Bill_Cost )
2224 , NULL)
2225 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2226 FROM (
2227 SELECT /*+ ORDERED */
2228 TIME.name time_id
2229 , TIME.order_by_id time_key
2230 , DECODE(NVL(TIME.amount_type,1),1,
2231 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost
2232 ,'BC', contract_brdn_cost),0) cost
2233 , 0 CT_COST
2234 , DECODE(NVL(TIME.amount_type,1),1,
2235 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2236 'BC', bill_burdened_cost),0) bill_cost
2237 , 0 CT_BILL_COST
2238 , DECODE(NVL(TIME.amount_type,2),2,
2239 DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
2240 'BC', con_curr_bgt_brdn_cost),0) budget
2241 , 0 CT_BUDGET
2242 , DECODE(NVL(TIME.amount_type,1),1,
2243 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2244 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2245 , 0 CT_Non_Bill_Cost
2246 FROM
2247 PJI_PMV_TIME_DIM_TMP TIME
2248 , PJI_PMV_ORGZ_DIM_TMP HORG
2249 , PJI_FP_ORGO_F_MV FCT
2250 , PJI_PMV_ORG_DIM_TMP HOU
2251 WHERE
2252 FCT.org_id = HOU.id
2253 AND FCT.organization_id = HORG.id
2254 AND FCT.time_id = TIME.id
2255 AND TIME.id IS NOT NULL
2256 AND FCT.period_type_id = TIME.period_type
2257 AND FCT.calendar_type = TIME.calendar_type
2258 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2259 UNION ALL
2260 SELECT /*+ ORDERED */
2261 TIME.name time_id
2262 , TIME.order_by_id time_key
2263 , 0 cost
2264 , DECODE(NVL(TIME.amount_type,1),1,
2265 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
2266 , 0 bill_cost
2267 , DECODE(NVL(TIME.amount_type,1),1,
2268 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2269 'BC', bill_burdened_cost),0) CT_BILL_COST
2270 , 0 budget
2271 , DECODE(NVL(TIME.amount_type,2),2,
2272 DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
2273 'BC', con_curr_bgt_brdn_cost),0) CT_BUDGET
2274 , 0 non_bill_cost
2275 , DECODE(NVL(TIME.amount_type,1),1,
2276 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2277 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2278 FROM
2279 PJI_PMV_TIME_DIM_TMP TIME
2280 , PJI_PMV_ORGZ_DIM_TMP HORG
2281 , PJI_FP_ORGO_F_MV FCT
2282 , PJI_PMV_ORG_DIM_TMP HOU
2283 WHERE
2284 FCT.org_id = HOU.id
2285 AND FCT.organization_id = HORG.id
2286 AND FCT.time_id = TIME.prior_id
2287 AND TIME.prior_id IS NOT NULL
2288 AND FCT.period_type_id = TIME.period_type
2289 AND FCT.calendar_type = TIME.calendar_type
2290 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2291 UNION ALL
2292 SELECT
2293 name time_id
2294 , order_by_id time_key
2295 , 0 cost
2296 , 0 CT_COST
2297 , 0 bill_cost
2298 , 0 CT_BILL_COST
2299 , 0 budget
2300 , 0 CT_BUDGET
2301 , 0 non_bill_cost
2302 , 0 CT_Non_Bill_Cost
2303 FROM pji_pmv_time_dim_tmp
2304 WHERE name <> '-1')
2305 GROUP BY
2306 TIME_KEY
2307 , TIME_ID ORDER BY TIME_KEY ASC;
2308
2309 /*
2310 ** ORGANIZATION AND CLASSIFICATION Processing:
2311 ** Only Organization and Classification is specified
2312 */
2313
2314 ELSIF
2315 (l_Convert_Classification ='Y')
2316 AND (l_Convert_Expenditure_Type = 'N')
2317 AND (l_Convert_Work_Type = 'N')
2318 THEN
2319 SELECT PJI_REP_PC11(
2320 TIME_ID
2321 , SUM( COST )
2322 , SUM( CT_COST )
2323 , NULL
2324 , SUM( BILL_COST )
2325 , SUM( CT_BILL_COST )
2326 , NULL
2327 , NULL
2328 , NULL
2329 , NULL
2330 , SUM ( BUDGET )
2334 , NULL
2331 , SUM ( CT_BUDGET )
2332 , NULL
2333 , NULL
2335 , NULL
2336 , SUM( NON_BILL_COST )
2337 , SUM( CT_Non_Bill_Cost )
2338 , NULL)
2339 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2340 FROM (
2341 SELECT /*+ ORDERED */
2342 TIME.name time_id
2343 , TIME.order_by_id time_key
2344 , DECODE(NVL(TIME.amount_type,1),1,
2345 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
2346 , 0 CT_COST
2347 , DECODE(NVL(TIME.amount_type,1),1,
2348 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2349 'BC', bill_burdened_cost),0) bill_cost
2350 , 0 CT_BILL_COST
2351 , DECODE(NVL(TIME.amount_type,2),2,
2352 DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
2353 'BC', con_curr_bgt_brdn_cost),0) budget
2354 , 0 CT_BUDGET
2355 , DECODE(NVL(TIME.amount_type,1),1,
2356 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2357 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2358 , 0 CT_Non_Bill_Cost
2359 FROM
2360 PJI_PMV_TIME_DIM_TMP TIME
2361 , PJI_PMV_ORGZ_DIM_TMP HORG
2362 , PJI_PMV_CLS_DIM_TMP CLS
2363 , PJI_FP_CLSO_F_MV FCT
2364 , PJI_PMV_ORG_DIM_TMP HOU
2365 WHERE
2366 FCT.PROJECT_ORG_ID = HOU.id
2367 AND FCT.PROJECT_organization_id = HORG.id
2368 AND FCT.time_id = TIME.id
2369 AND TIME.id IS NOT NULL
2370 AND FCT.period_type_id = TIME.period_type
2371 AND FCT.calendar_type = TIME.calendar_type
2372 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2373 AND FCT.PROJECT_CLASS_ID = CLS.ID
2374 UNION ALL
2375 SELECT /*+ ORDERED */
2376 TIME.name time_id
2377 , TIME.order_by_id time_key
2378 , 0 cost
2379 , DECODE(NVL(TIME.amount_type,1),1,
2380 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
2381 , 0 bill_cost
2382 , DECODE(NVL(TIME.amount_type,1),1,
2383 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2384 'BC', bill_burdened_cost),0) CT_BILL_COST
2385 , 0 budget
2386 , DECODE(NVL(TIME.amount_type,2),2,
2387 DECODE(l_Report_Cost_Type,'RC', con_curr_bgt_raw_cost,
2388 'BC', con_curr_bgt_brdn_cost),0) CT_BUDGET
2389 , 0 non_bill_cost
2390 , DECODE(NVL(TIME.amount_type,1),1,
2391 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2392 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2393 FROM
2394 PJI_PMV_TIME_DIM_TMP TIME
2395 , PJI_PMV_ORGZ_DIM_TMP HORG
2396 , PJI_PMV_CLS_DIM_TMP CLS
2397 , PJI_FP_CLSO_F_MV FCT
2398 , PJI_PMV_ORG_DIM_TMP HOU
2399 WHERE
2400 FCT.PROJECT_org_id = HOU.id
2401 AND FCT.PROJECT_organization_id = HORG.id
2402 AND FCT.time_id = TIME.prior_id
2403 AND TIME.prior_id IS NOT NULL
2404 AND FCT.period_type_id = TIME.period_type
2405 AND FCT.calendar_type = TIME.calendar_type
2406 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2407 AND FCT.PROJECT_CLASS_ID = CLS.ID
2408 UNION ALL
2409 SELECT
2410 name time_id
2411 , order_by_id time_key
2412 , 0 cost
2413 , 0 CT_COST
2414 , 0 bill_cost
2415 , 0 CT_BILL_COST
2416 , 0 budget
2417 , 0 CT_BUDGET
2418 , 0 non_bill_cost
2419 , 0 CT_Non_Bill_Cost
2420 FROM pji_pmv_time_dim_tmp
2421 WHERE name <> '-1')
2422 GROUP BY
2423 TIME_KEY
2424 , TIME_ID ORDER BY TIME_KEY ASC;
2425
2426 /*
2427 ** ORGANIZATION AND EXPENDITURE CATEGORY/TYPE Processing:
2428 ** Only Organization and Expenditure Category/Type is specified
2429 */
2430
2431 ELSIF
2432 (l_Convert_Classification ='N')
2433 AND (l_Convert_Expenditure_Type = 'Y')
2434 AND (l_Convert_Work_Type = 'N')
2435 THEN
2436
2437 SELECT PJI_REP_PC11(
2438 TIME_ID
2439 , SUM( COST )
2440 , SUM( CT_COST )
2441 , NULL
2442 , SUM( BILL_COST )
2443 , SUM( CT_BILL_COST )
2444 , NULL
2445 , NULL
2446 , NULL
2447 , NULL
2448 , SUM( BUDGET )
2449 , SUM( CT_BUDGET )
2450 , NULL
2451 , NULL
2452 , NULL
2453 , NULL
2454 , SUM( NON_BILL_COST )
2455 , SUM( CT_Non_Bill_Cost )
2456 , NULL)
2457 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2458 FROM (
2459 SELECT /*+ ORDERED */
2460 TIME.name time_id
2461 , TIME.order_by_id time_key
2462 , DECODE(NVL(TIME.amount_type,1),1,
2466 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2463 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
2464 , 0 CT_COST
2465 , DECODE(NVL(TIME.amount_type,1),1,
2467 'BC', bill_burdened_cost),0) bill_cost
2468 , 0 CT_BILL_COST
2469 , NULL AS budget
2470 , NULL AS ct_budget
2471 , DECODE(NVL(TIME.amount_type,1),1,
2472 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2473 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2474 , 0 CT_Non_Bill_Cost
2475 FROM
2476 PJI_PMV_TIME_DIM_TMP TIME
2477 , PJI_PMV_ORGZ_DIM_TMP HORG
2478 , PJI_PMV_ET_RT_DIM_TMP ET
2479 , PJI_FP_ORGO_ET_F_MV FCT
2480 , PJI_PMV_ORG_DIM_TMP HOU
2481 WHERE
2482 FCT.org_id = HOU.id
2483 AND FCT.organization_id = HORG.id
2484 AND FCT.time_id = TIME.id
2485 AND TIME.id IS NOT NULL
2486 AND FCT.period_type_id = TIME.period_type
2487 AND FCT.calendar_type = TIME.calendar_type
2488 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2489 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2490 AND ET.record_type = 'ET'
2491 UNION ALL
2492 SELECT /*+ ORDERED */
2493 TIME.name time_id
2494 , TIME.order_by_id time_key
2495 , 0 cost
2496 , DECODE(NVL(TIME.amount_type,1),1,
2497 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
2498 , 0 bill_cost
2499 , DECODE(NVL(TIME.amount_type,1),1,
2500 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2501 'BC', bill_burdened_cost),0) CT_BILL_COST
2502 , NULL AS budget
2503 , NULL AS CT_BUDGET
2504 , 0 non_bill_cost
2505 , DECODE(NVL(TIME.amount_type,1),1,
2506 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2507 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2508 FROM
2509 PJI_PMV_TIME_DIM_TMP TIME
2510 , PJI_PMV_ORGZ_DIM_TMP HORG
2511 , PJI_PMV_ET_RT_DIM_TMP ET
2512 , PJI_FP_ORGO_ET_F_MV FCT
2513 , PJI_PMV_ORG_DIM_TMP HOU
2514 WHERE
2515 FCT.org_id = HOU.id
2516 AND FCT.organization_id = HORG.id
2517 AND FCT.time_id = TIME.prior_id
2518 AND TIME.prior_id IS NOT NULL
2519 AND FCT.period_type_id = TIME.period_type
2520 AND FCT.calendar_type = TIME.calendar_type
2521 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2522 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2523 AND ET.record_type = 'ET'
2524 UNION ALL
2525 SELECT
2526 name time_id
2527 , order_by_id time_key
2528 , 0 cost
2529 , 0 CT_COST
2530 , 0 bill_cost
2531 , 0 CT_BILL_COST
2532 , NULL AS budget
2533 , NULL AS CT_BUDGET
2534 , 0 non_bill_cost
2535 , 0 CT_Non_Bill_Cost
2536 FROM pji_pmv_time_dim_tmp
2537 WHERE name <> '-1')
2538 GROUP BY
2539 TIME_KEY
2540 , TIME_ID ORDER BY TIME_KEY ASC;
2541
2542 /*
2543 ** ORGANIZATION AND WORK TYPE Processing:
2544 ** Only Organization and Work Type is specified
2545 */
2546
2547 ELSIF
2548 (l_Convert_Classification ='N')
2549 AND (l_Convert_Expenditure_Type = 'N')
2550 AND (l_Convert_Work_Type = 'Y')
2551 THEN
2552
2553 SELECT PJI_REP_PC11(
2554 TIME_ID
2555 , SUM( COST )
2556 , SUM( CT_COST )
2557 , NULL
2558 , SUM( BILL_COST )
2559 , SUM( CT_BILL_COST )
2560 , NULL
2561 , NULL
2562 , NULL
2563 , NULL
2564 , SUM( BUDGET )
2565 , SUM( CT_BUDGET )
2566 , NULL
2567 , NULL
2568 , NULL
2569 , NULL
2570 , SUM( NON_BILL_COST )
2571 , SUM( CT_Non_Bill_Cost )
2572 , NULL)
2573 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2574 FROM (
2575 SELECT /*+ ORDERED */
2576 TIME.name time_id
2577 , TIME.order_by_id time_key
2578 , DECODE(NVL(TIME.amount_type,1),1,
2579 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
2580 , 0 CT_COST
2581 , DECODE(NVL(TIME.amount_type,1),1,
2582 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2583 'BC', bill_burdened_cost),0) bill_cost
2587 , DECODE(NVL(TIME.amount_type,1),1,
2584 , 0 CT_BILL_COST
2585 , NULL AS budget
2586 , NULL AS CT_BUDGET
2588 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2589 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2590 , 0 CT_Non_Bill_Cost
2591 FROM
2592 PJI_PMV_TIME_DIM_TMP TIME
2593 , PJI_PMV_ORGZ_DIM_TMP HORG
2594 , PJI_PMV_WT_DIM_TMP WT
2595 , PJI_FP_ORGO_ET_WT_F_MV FCT
2596 , PJI_PMV_ORG_DIM_TMP HOU
2597 WHERE
2598 FCT.org_id = HOU.id
2599 AND FCT.organization_id = HORG.id
2600 AND FCT.time_id = TIME.id
2601 AND TIME.id IS NOT NULL
2602 AND FCT.period_type_id = TIME.period_type
2603 AND FCT.calendar_type = TIME.calendar_type
2604 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2605 AND FCT.WORK_TYPE_ID = WT.ID
2606 UNION ALL
2607 SELECT /*+ ORDERED */
2608 TIME.name time_id
2609 , TIME.order_by_id time_key
2610 , 0 cost
2611 , DECODE(NVL(TIME.amount_type,1),1,
2612 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,
2613 'BC', contract_brdn_cost),0) CT_COST
2614 , 0 bill_cost
2615 , DECODE(NVL(TIME.amount_type,1),1,
2616 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2617 'BC', bill_burdened_cost),0) CT_BILL_COST
2618 , NULL AS budget
2619 , NULL AS CT_BUDGET
2620 , 0 non_bill_cost
2621 , DECODE(NVL(TIME.amount_type,1),1,
2622 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2623 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2624 FROM
2625 PJI_PMV_TIME_DIM_TMP TIME
2626 , PJI_PMV_ORGZ_DIM_TMP HORG
2627 , PJI_PMV_WT_DIM_TMP WT
2628 , PJI_FP_ORGO_ET_WT_F_MV FCT
2629 , PJI_PMV_ORG_DIM_TMP HOU
2630 WHERE
2631 FCT.org_id = HOU.id
2632 AND FCT.organization_id = HORG.id
2633 AND FCT.time_id = TIME.prior_id
2634 AND TIME.prior_id IS NOT NULL
2635 AND FCT.period_type_id = TIME.period_type
2636 AND FCT.calendar_type = TIME.calendar_type
2637 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2638 AND FCT.WORK_TYPE_ID = WT.ID
2639 UNION ALL
2640 SELECT
2641 name time_id
2642 , order_by_id time_key
2643 , 0 cost
2644 , 0 CT_COST
2645 , 0 bill_cost
2646 , 0 CT_BILL_COST
2647 , NULL AS budget
2648 , NULL AS CT_BUDGET
2649 , 0 non_bill_cost
2650 , 0 CT_Non_Bill_Cost
2651 FROM pji_pmv_time_dim_tmp
2652 WHERE name <> '-1')
2653 GROUP BY
2654 TIME_KEY
2655 , TIME_ID ORDER BY TIME_KEY ASC;
2656
2657
2658 /*
2659 ** ORGANIZATION, CLASSIFICATION AND EXPENDITURE CATEGORY/TYPE Processing:
2660 ** Only Organization, Classification and Expenditure Category/Type is specified
2661 */
2662
2663 ELSIF
2664 (l_Convert_Classification ='Y')
2665 AND (l_Convert_Expenditure_Type = 'Y')
2666 AND (l_Convert_Work_Type = 'N')
2667 THEN
2668 SELECT PJI_REP_PC11(
2669 TIME_ID
2670 , SUM( COST )
2671 , SUM( CT_COST )
2672 , NULL
2673 , SUM( BILL_COST )
2674 , SUM( CT_BILL_COST )
2675 , NULL
2676 , NULL
2677 , NULL
2678 , NULL
2679 , SUM( BUDGET )
2680 , SUM( CT_BUDGET )
2681 , NULL
2682 , NULL
2683 , NULL
2684 , NULL
2685 , SUM( NON_BILL_COST )
2686 , SUM( CT_Non_Bill_Cost )
2687 , NULL)
2688 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2689 FROM (
2690 SELECT /*+ ORDERED */
2691 TIME.name time_id
2692 , TIME.order_by_id time_key
2693 , DECODE(NVL(TIME.amount_type,1),1,
2694 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
2695 , 0 CT_COST
2696 , DECODE(NVL(TIME.amount_type,1),1,
2697 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2698 'BC', bill_burdened_cost),0) bill_cost
2699 , 0 CT_BILL_COST
2700 , NULL AS budget
2701 , NULL AS CT_BUDGET
2702 , DECODE(NVL(TIME.amount_type,1),1,
2703 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2704 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2705 , 0 CT_Non_Bill_Cost
2706 FROM
2707 PJI_PMV_TIME_DIM_TMP TIME
2708 , PJI_PMV_ORGZ_DIM_TMP HORG
2709 , PJI_PMV_ET_RT_DIM_TMP ET
2710 , PJI_FP_CLSO_ET_F_MV FCT
2711 , PJI_PMV_CLS_DIM_TMP CLS
2712 , PJI_PMV_ORG_DIM_TMP HOU
2713 WHERE
2714 FCT.PROJECT_org_id = HOU.id
2718 AND FCT.period_type_id = TIME.period_type
2715 AND FCT.PROJECT_organization_id = HORG.id
2716 AND FCT.time_id = TIME.id
2717 AND TIME.id IS NOT NULL
2719 AND FCT.calendar_type = TIME.calendar_type
2720 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2721 AND FCT.PROJECT_CLASS_ID = CLS.ID
2722 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2723 AND ET.record_type = 'ET'
2724 UNION ALL
2725 SELECT /*+ ORDERED */
2726 TIME.name time_id
2727 , TIME.order_by_id time_key
2728 , 0 cost
2729 , DECODE(NVL(TIME.amount_type,1),1,
2730 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,
2731 'BC', contract_brdn_cost),0) CT_COST
2732 , 0 bill_cost
2733 , DECODE(NVL(TIME.amount_type,1),1,
2734 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2735 'BC', bill_burdened_cost),0) CT_BILL_COST
2736 , NULL AS budget
2737 , NULL AS CT_BUDGET
2738 , 0 non_bill_cost
2739 , DECODE(NVL(TIME.amount_type,1),1,
2740 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2741 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2742 FROM
2743 PJI_PMV_TIME_DIM_TMP TIME
2744 , PJI_PMV_ORGZ_DIM_TMP HORG
2745 , PJI_PMV_ET_RT_DIM_TMP ET
2746 , PJI_FP_CLSO_ET_F_MV FCT
2747 , PJI_PMV_CLS_DIM_TMP CLS
2748 , PJI_PMV_ORG_DIM_TMP HOU
2749 WHERE
2750 FCT.PROJECT_org_id = HOU.id
2751 AND FCT.PROJECT_organization_id = HORG.id
2752 AND FCT.time_id = TIME.prior_id
2753 AND TIME.prior_id IS NOT NULL
2754 AND FCT.period_type_id = TIME.period_type
2755 AND FCT.calendar_type = TIME.calendar_type
2756 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2757 AND FCT.PROJECT_CLASS_ID = CLS.ID
2758 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2759 AND ET.record_type = 'ET'
2760 UNION ALL
2761 SELECT
2762 name time_id
2763 , order_by_id time_key
2764 , 0 cost
2765 , 0 CT_COST
2766 , 0 bill_cost
2767 , 0 CT_BILL_COST
2768 , NULL AS budget
2769 , NULL AS CT_BUDGET
2770 , 0 non_bill_cost
2771 , 0 CT_Non_Bill_Cost
2772 FROM pji_pmv_time_dim_tmp
2773 WHERE name <> '-1')
2774 GROUP BY
2775 TIME_KEY
2776 , TIME_ID ORDER BY TIME_KEY ASC;
2777
2778 /*
2779 ** ORGANIZATION, EXPENDITURE CATEGORY/TYPE AND WORK TYPE Processing:
2780 ** Only Organization, Expenditure Category/Type and Work Type is specified
2781 */
2782
2783 ELSIF
2784 (l_Convert_Classification ='N')
2785 AND (l_Convert_Expenditure_Type = 'Y')
2786 AND (l_Convert_Work_Type = 'Y')
2787 THEN
2788 SELECT PJI_REP_PC11(
2789 TIME_ID
2790 , SUM( COST )
2791 , SUM( CT_COST )
2792 , NULL
2793 , SUM( BILL_COST )
2794 , SUM( CT_BILL_COST )
2795 , NULL
2796 , NULL
2797 , NULL
2798 , NULL
2799 , SUM( BUDGET )
2800 , SUM( CT_BUDGET )
2801 , NULL
2802 , NULL
2803 , NULL
2804 , NULL
2805 , SUM( NON_BILL_COST )
2806 , SUM( CT_Non_Bill_Cost )
2807 , NULL)
2808 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2809 FROM (
2810 SELECT /*+ ORDERED */
2811 TIME.name time_id
2812 , TIME.order_by_id time_key
2813 , DECODE(NVL(TIME.amount_type,1),1,
2814 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost,
2815 'BC', contract_brdn_cost),0) cost
2816 , 0 CT_COST
2817 , DECODE(NVL(TIME.amount_type,1),1,
2818 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2819 'BC', bill_burdened_cost),0) bill_cost
2820 , 0 CT_BILL_COST
2821 , NULL AS budget
2822 , NULL AS CT_BUDGET
2823 , DECODE(NVL(TIME.amount_type,1),1,
2824 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2825 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2826 , 0 CT_Non_Bill_Cost
2827 FROM
2828 PJI_PMV_TIME_DIM_TMP TIME
2829 , PJI_PMV_ORGZ_DIM_TMP HORG
2830 , PJI_PMV_ET_RT_DIM_TMP ET
2831 , PJI_FP_ORGO_ET_WT_F_MV FCT
2832 , PJI_PMV_WT_DIM_TMP WT
2833 , PJI_PMV_ORG_DIM_TMP HOU
2834 WHERE
2835 FCT.org_id = HOU.id
2836 AND FCT.organization_id = HORG.id
2837 AND FCT.time_id = TIME.id
2838 AND TIME.id IS NOT NULL
2839 AND FCT.period_type_id = TIME.period_type
2840 AND FCT.calendar_type = TIME.calendar_type
2841 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2842 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2843 AND FCT.WORK_TYPE_ID = WT.ID
2844 and ET.record_type = 'ET'
2845 UNION ALL
2846 SELECT /*+ ORDERED */
2847 TIME.name time_id
2851 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
2848 , TIME.order_by_id time_key
2849 , 0 cost
2850 , DECODE(NVL(TIME.amount_type,1),1,
2852 , 0 bill_cost
2853 , DECODE(NVL(TIME.amount_type,1),1,
2854 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2855 'BC', bill_burdened_cost),0) CT_BILL_COST
2856 , NULL AS budget
2857 , NULL AS CT_BUDGET
2858 , 0 non_bill_cost
2859 , DECODE(NVL(TIME.amount_type,1),1,
2860 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2861 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2862 FROM
2863 PJI_PMV_TIME_DIM_TMP TIME
2864 , PJI_PMV_ORGZ_DIM_TMP HORG
2865 , PJI_PMV_ET_RT_DIM_TMP ET
2866 , PJI_FP_ORGO_ET_WT_F_MV FCT
2867 , PJI_PMV_WT_DIM_TMP WT
2868 , PJI_PMV_ORG_DIM_TMP HOU
2869 WHERE
2870 FCT.org_id = HOU.id
2871 AND FCT.organization_id = HORG.id
2872 AND FCT.time_id = TIME.prior_id
2873 AND TIME.prior_id IS NOT NULL
2874 AND FCT.period_type_id = TIME.period_type
2875 AND FCT.calendar_type = TIME.calendar_type
2876 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2877 AND FCT.EXP_EVT_TYPE_ID = ET.ID
2878 AND FCT.WORK_TYPE_ID = WT.ID
2879 and ET.record_type = 'ET'
2880 UNION ALL
2881 SELECT
2882 name time_id
2883 , order_by_id time_key
2884 , 0 cost
2885 , 0 CT_COST
2886 , 0 bill_cost
2887 , 0 CT_BILL_COST
2888 , NULL AS budget
2889 , NULL AS CT_BUDGET
2890 , 0 non_bill_cost
2891 , 0 CT_Non_Bill_Cost
2892 FROM pji_pmv_time_dim_tmp
2893 WHERE name <> '-1')
2894 GROUP BY
2895 TIME_KEY
2896 , TIME_ID ORDER BY TIME_KEY ASC;
2897
2898 /*
2899 ** ORGANIZATION, CLASSIFICATION AND WORK TYPE Processing:
2900 ** Only Organization, Classification and Work Type is specified
2901 */
2902
2903 ELSIF
2904 (l_Convert_Classification ='Y')
2905 AND (l_Convert_Expenditure_Type = 'N')
2906 AND (l_Convert_Work_Type = 'Y')
2907 THEN
2908 SELECT PJI_REP_PC11(
2909 TIME_ID
2910 , SUM( COST )
2911 , SUM( CT_COST )
2912 , NULL
2913 , SUM( BILL_COST )
2914 , SUM( CT_BILL_COST )
2915 , NULL
2916 , NULL
2917 , NULL
2918 , NULL
2919 , SUM( BUDGET )
2920 , SUM( CT_BUDGET )
2921 , NULL
2922 , NULL
2923 , NULL
2924 , NULL
2925 , SUM( NON_BILL_COST )
2926 , SUM( CT_Non_Bill_Cost )
2927 , NULL)
2928 BULK COLLECT INTO l_Project_Cost_Trend_Tab
2929 FROM (
2930 SELECT /*+ ORDERED */
2931 TIME.name time_id
2932 , TIME.order_by_id time_key
2933 , DECODE(NVL(TIME.amount_type,1),1,
2934 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
2935 , 0 CT_COST
2936 , DECODE(NVL(TIME.amount_type,1),1,
2937 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2938 'BC', bill_burdened_cost),0) bill_cost
2939 , 0 CT_BILL_COST
2940 , NULL AS budget
2941 , NULL AS CT_BUDGET
2942 , DECODE(NVL(TIME.amount_type,1),1,
2943 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2944 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
2945 , 0 CT_Non_Bill_Cost
2946 FROM
2947 PJI_PMV_TIME_DIM_TMP TIME
2948 , PJI_PMV_ORGZ_DIM_TMP HORG
2949 , PJI_PMV_CLS_DIM_TMP CLS
2950 , PJI_FP_CLSO_ET_WT_F_MV FCT
2951 , PJI_PMV_WT_DIM_TMP WT
2952 , PJI_PMV_ORG_DIM_TMP HOU
2953 WHERE
2954 FCT.PROJECT_org_id = HOU.id
2955 AND FCT.PROJECT_organization_id = HORG.id
2956 AND FCT.time_id = TIME.id
2957 AND TIME.id IS NOT NULL
2958 AND FCT.period_type_id = TIME.period_type
2959 AND FCT.calendar_type = TIME.calendar_type
2960 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2961 AND FCT.PROJECT_CLASS_ID = CLS.ID
2962 AND FCT.WORK_TYPE_ID = WT.ID
2963 UNION ALL
2964 SELECT /*+ ORDERED */
2965 TIME.name time_id
2966 , TIME.order_by_id time_key
2967 , 0 cost
2968 , DECODE(NVL(TIME.amount_type,1),1,
2969 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
2970 , 0 bill_cost
2971 , DECODE(NVL(TIME.amount_type,1),1,
2972 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
2973 'BC', bill_burdened_cost),0) CT_BILL_COST
2974 , NULL AS budget
2975 , NULL AS CT_BUDGET
2976 , 0 non_bill_cost
2977 , DECODE(NVL(TIME.amount_type,1),1,
2978 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
2982 , PJI_PMV_ORGZ_DIM_TMP HORG
2979 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
2980 FROM
2981 PJI_PMV_TIME_DIM_TMP TIME
2983 , PJI_PMV_CLS_DIM_TMP CLS
2984 , PJI_FP_CLSO_ET_WT_F_MV FCT
2985 , PJI_PMV_WT_DIM_TMP WT
2986 , PJI_PMV_ORG_DIM_TMP HOU
2987 WHERE
2988 FCT.PROJECT_org_id = HOU.id
2989 AND FCT.PROJECT_organization_id = HORG.id
2990 AND FCT.time_id = TIME.prior_id
2991 AND TIME.prior_id IS NOT NULL
2992 AND FCT.period_type_id = TIME.period_type
2993 AND FCT.calendar_type = TIME.calendar_type
2994 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
2995 AND FCT.PROJECT_CLASS_ID = CLS.ID
2996 AND FCT.WORK_TYPE_ID = WT.ID
2997 UNION ALL
2998 SELECT
2999 name time_id
3000 , order_by_id time_key
3001 , 0 cost
3002 , 0 CT_COST
3003 , 0 bill_cost
3004 , 0 CT_BILL_COST
3005 , NULL AS budget
3006 , NULL AS CT_BUDGET
3007 , 0 non_bill_cost
3008 , 0 CT_Non_Bill_Cost
3009 FROM pji_pmv_time_dim_tmp
3010 WHERE name <> '-1')
3011 GROUP BY
3012 TIME_KEY
3013 , TIME_ID ORDER BY TIME_KEY ASC;
3014
3015
3016 /*
3017 ** ORGANIZATION, CLASSIFICATION, EXPENDITURE CATEGORY/TYPE AND WORK TYPE Processing:
3018 ** All Parameters specified: Organization, Classification, Expenditure Category/Type
3019 ** and Work Type is specified
3020 */
3021
3022 ELSE
3023 SELECT PJI_REP_PC11(
3024 TIME_ID
3025 , SUM( COST )
3026 , SUM( CT_COST )
3027 , NULL
3028 , SUM( BILL_COST )
3029 , SUM( CT_BILL_COST )
3030 , NULL
3031 , NULL
3032 , NULL
3033 , NULL
3034 , SUM( BUDGET )
3035 , SUM( CT_BUDGET )
3036 , NULL
3037 , NULL
3038 , NULL
3039 , NULL
3040 , SUM( NON_BILL_COST )
3041 , SUM( CT_Non_Bill_Cost )
3042 , NULL)
3043 BULK COLLECT INTO l_Project_Cost_Trend_Tab
3044 FROM (
3045 SELECT /*+ ORDERED */
3046 TIME.name time_id
3047 , TIME.order_by_id time_key
3048 , DECODE(NVL(TIME.amount_type,1),1,
3049 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) cost
3050 , 0 CT_COST
3051 , DECODE(NVL(TIME.amount_type,1),1,
3052 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
3053 'BC', bill_burdened_cost),0) bill_cost
3054 , 0 CT_BILL_COST
3055 , NULL AS budget
3056 , NULL AS CT_BUDGET
3057 , DECODE(NVL(TIME.amount_type,1),1,
3058 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
3059 'BC', contract_brdn_cost - bill_burdened_cost),0) non_bill_cost
3060 , 0 CT_Non_Bill_Cost
3061 FROM
3062 PJI_PMV_TIME_DIM_TMP TIME
3063 , PJI_PMV_ORGZ_DIM_TMP HORG
3064 , PJI_PMV_ET_RT_DIM_TMP ET
3065 , PJI_FP_CLSO_ET_WT_F_MV FCT
3066 , PJI_PMV_CLS_DIM_TMP CLS
3067 , PJI_PMV_WT_DIM_TMP WT
3068 , PJI_PMV_ORG_DIM_TMP HOU
3069 WHERE
3070 FCT.PROJECT_org_id = HOU.id
3071 AND FCT.PROJECT_organization_id = HORG.id
3072 AND FCT.time_id = TIME.id
3073 AND TIME.id IS NOT NULL
3074 AND FCT.period_type_id = TIME.period_type
3075 AND FCT.calendar_type = TIME.calendar_type
3076 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3077 AND FCT.PROJECT_CLASS_ID = CLS.ID
3078 AND FCT.WORK_TYPE_ID = WT.ID
3079 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3080 AND ET.record_type = 'ET'
3081 UNION ALL
3082 SELECT /*+ ORDERED */
3083 TIME.name time_id
3084 , TIME.order_by_id time_key
3085 , 0 cost
3086 , DECODE(NVL(TIME.amount_type,1),1,
3087 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost ,'BC', contract_brdn_cost),0) CT_COST
3088 , 0 bill_cost
3089 , DECODE(NVL(TIME.amount_type,1),1,
3090 DECODE(l_Report_Cost_Type,'RC', bill_raw_cost,
3091 'BC', bill_burdened_cost),0) CT_BILL_COST
3092 , NULL AS budget
3093 , NULL AS CT_BUDGET
3094 , 0 non_bill_cost
3095 , DECODE(NVL(TIME.amount_type,1),1,
3096 DECODE(l_Report_Cost_Type,'RC', contract_raw_cost - bill_raw_cost,
3097 'BC', contract_brdn_cost - bill_burdened_cost),0) CT_Non_Bill_Cost
3098 FROM
3099 PJI_PMV_TIME_DIM_TMP TIME
3100 , PJI_PMV_ORGZ_DIM_TMP HORG
3101 , PJI_PMV_ET_RT_DIM_TMP ET
3102 , PJI_FP_CLSO_ET_WT_F_MV FCT
3103 , PJI_PMV_CLS_DIM_TMP CLS
3104 , PJI_PMV_WT_DIM_TMP WT
3105 , PJI_PMV_ORG_DIM_TMP HOU
3106 WHERE
3107 FCT.PROJECT_org_id = HOU.id
3108 AND FCT.PROJECT_organization_id = HORG.id
3109 AND FCT.time_id = TIME.prior_id
3110 AND TIME.prior_id IS NOT NULL
3111 AND FCT.period_type_id = TIME.period_type
3115 AND FCT.WORK_TYPE_ID = WT.ID
3112 AND FCT.calendar_type = TIME.calendar_type
3113 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3114 AND FCT.PROJECT_CLASS_ID = CLS.ID
3116 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3117 AND ET.record_type = 'ET'
3118 UNION ALL
3119 SELECT
3120 name time_id
3121 , order_by_id time_key
3122 , 0 cost
3123 , 0 CT_COST
3124 , 0 bill_cost
3125 , 0 CT_BILL_COST
3126 , NULL AS budget
3127 , NULL AS CT_BUDGET
3128 , 0 non_bill_cost
3129 , 0 CT_Non_Bill_Cost
3130 FROM pji_pmv_time_dim_tmp
3131 WHERE name <> '-1')
3132 GROUP BY
3133 TIME_KEY
3134 , TIME_ID ORDER BY TIME_KEY ASC;
3135
3136 END IF;
3137
3138 FOR i in 1..l_Project_Cost_Trend_Tab.COUNT
3139 LOOP
3140 IF p_Report_Type = 'FISCAL' THEN
3141 l_Cost := l_Cost + l_Project_Cost_Trend_Tab(i).COST;
3142 l_CT_COST := l_CT_COST + l_Project_Cost_Trend_Tab(i).CT_COST;
3143 l_Bill_Cost := l_Bill_Cost + l_Project_Cost_Trend_Tab(i).BILL_COST;
3144 l_CT_BILL_COST := l_CT_BILL_COST + l_Project_Cost_Trend_Tab(i).CT_BILL_COST;
3145 l_Budget := l_Budget + l_Project_Cost_Trend_Tab(i).BUDGET;
3146 l_CT_BUDGET := l_CT_BUDGET + l_Project_Cost_Trend_Tab(i).CT_BUDGET;
3147 l_Non_Bill_Cost := l_Non_Bill_Cost + l_Project_Cost_Trend_Tab(i).NON_BILL_COST;
3148 l_CT_Non_Bill_Cost := l_CT_Non_Bill_Cost + l_Project_Cost_Trend_Tab(i).CT_NON_BILL_COST;
3149
3150 l_Project_Cost_Trend_Tab(i).COST :=l_Cost;
3151 l_Project_Cost_Trend_Tab(i).CT_COST :=l_CT_COST;
3152 l_Project_Cost_Trend_Tab(i).BILL_COST :=l_Bill_Cost;
3153 l_Project_Cost_Trend_Tab(i).CT_BILL_COST :=l_CT_BILL_COST;
3154 l_Project_Cost_Trend_Tab(i).BUDGET :=l_Budget;
3155 l_Project_Cost_Trend_Tab(i).CT_BUDGET :=l_CT_BUDGET;
3156 l_Project_Cost_Trend_Tab(i).NON_BILL_COST :=l_Non_Bill_Cost;
3157 l_Project_Cost_Trend_Tab(i).CT_NON_BILL_COST :=l_CT_Non_Bill_Cost;
3158
3159
3160 END IF;
3161
3162 IF NVL(l_Project_Cost_Trend_Tab(i).CT_COST,0) <> 0 THEN
3163 l_Project_Cost_Trend_Tab(i).COST_CHANGE_PRCNT := 100*
3164 ((l_Project_Cost_Trend_Tab(i).COST-l_Project_Cost_Trend_Tab(i).CT_COST)
3165 /ABS(l_Project_Cost_Trend_Tab(i).CT_COST));
3166 ELSE
3167 l_Project_Cost_Trend_Tab(i).COST_CHANGE_PRCNT := NULL;
3168 END IF;
3169
3170 IF NVL(l_Project_Cost_Trend_Tab(i).CT_BILL_COST,0) <> 0 THEN
3171 l_Project_Cost_Trend_Tab(i).BILL_COST_CHANGE_PRCNT := 100*
3172 ((l_Project_Cost_Trend_Tab(i).BILL_COST-l_Project_Cost_Trend_Tab(i).CT_BILL_COST)
3173 /ABS(l_Project_Cost_Trend_Tab(i).CT_BILL_COST));
3174 ELSE
3175 l_Project_Cost_Trend_Tab(i).BILL_COST_CHANGE_PRCNT := NULL;
3176 END IF;
3177
3178
3179 IF NVL(l_Project_Cost_Trend_Tab(i).COST,0) <> 0 THEN
3180 l_Project_Cost_Trend_Tab(i).BILL_PRCNT_OF_COST := 100*
3181 ((l_Project_Cost_Trend_Tab(i).BILL_COST)
3182 /ABS(l_Project_Cost_Trend_Tab(i).COST));
3183 ELSE
3184 l_Project_Cost_Trend_Tab(i).BILL_PRCNT_OF_COST := NULL;
3185 END IF;
3186
3187 IF NVL(l_Project_Cost_Trend_Tab(i).CT_COST,0) <> 0 THEN
3188 l_Project_Cost_Trend_Tab(i).CT_BILL_PRCNT_OF_COST := 100*
3189 ((l_Project_Cost_Trend_Tab(i).CT_BILL_COST)
3190 /ABS(l_Project_Cost_Trend_Tab(i).CT_COST));
3191 ELSE
3192 l_Project_Cost_Trend_Tab(i).CT_BILL_PRCNT_OF_COST := NULL;
3193 END IF;
3194
3195 l_Project_Cost_Trend_Tab(i).PRCNT_OF_COST_CHANGE :=
3196 l_Project_Cost_Trend_Tab(i).BILL_PRCNT_OF_COST
3197 - l_Project_Cost_Trend_Tab(i).CT_BILL_PRCNT_OF_COST;
3198
3199 IF NVL(l_Project_Cost_Trend_Tab(i).CT_BUDGET,0) <> 0 THEN
3200 l_Project_Cost_Trend_Tab(i).BUDGET_CHANGE_PRCNT := 100*
3201 ((l_Project_Cost_Trend_Tab(i).BUDGET -
3202 l_Project_Cost_Trend_Tab(i).CT_BUDGET)
3203 /ABS(l_Project_Cost_Trend_Tab(i).CT_BUDGET));
3204 ELSE
3205 l_Project_Cost_Trend_Tab(i).BUDGET_CHANGE_PRCNT := NULL;
3206 END IF;
3207
3208 IF NVL(l_Project_Cost_Trend_Tab(i).BUDGET,0) <> 0 THEN
3209 l_Project_Cost_Trend_Tab(i).BILL_COST_PRCNT_OF_BUDGET := 100*
3210 ((l_Project_Cost_Trend_Tab(i).BILL_COST)
3211 /ABS(l_Project_Cost_Trend_Tab(i).BUDGET));
3212 ELSE
3213 l_Project_Cost_Trend_Tab(i).BILL_COST_PRCNT_OF_BUDGET := NULL;
3214 END IF;
3215
3216 IF NVL(l_Project_Cost_Trend_Tab(i).CT_BUDGET,0) <> 0 THEN
3217 l_Project_Cost_Trend_Tab(i).CT_BILL_COST_PRCNT_OF_BUDGET := 100*
3218 ((l_Project_Cost_Trend_Tab(i).CT_BILL_COST)
3219 /ABS(l_Project_Cost_Trend_Tab(i).CT_BUDGET));
3220 ELSE
3221 l_Project_Cost_Trend_Tab(i).CT_BILL_COST_PRCNT_OF_BUDGET := NULL;
3222 END IF;
3223
3224 l_Project_Cost_Trend_Tab(i).CHANGE :=
3225 l_Project_Cost_Trend_Tab(i).BILL_COST_PRCNT_OF_BUDGET
3226 - l_Project_Cost_Trend_Tab(i).CT_BILL_COST_PRCNT_OF_BUDGET;
3227
3228 IF NVL(l_Project_Cost_Trend_Tab(i).CT_Non_Bill_Cost,0) <> 0 THEN
3229 l_Project_Cost_Trend_Tab(i).NON_BILL_COST_CHANGE_PRCNT := 100*
3230 ((l_Project_Cost_Trend_Tab(i).NON_BILL_COST -
3231 l_Project_Cost_Trend_Tab(i).CT_Non_Bill_Cost)
3232 /ABS(l_Project_Cost_Trend_Tab(i).CT_Non_Bill_Cost));
3236
3233 ELSE
3234 l_Project_Cost_Trend_Tab(i).NON_BILL_COST_CHANGE_PRCNT := NULL;
3235 END IF;
3237 /*
3238 ** The below portion of the code is commented
3239 ** because the trend reports donot have totals.
3240 */
3241 /*
3242 l_Total_Revenue := l_Total_Revenue + NVL(l_Total_Prj_Profitablity_Tab(i).REVENUE, 0);
3243 l_Total_Cost := l_Total_Cost + NVL(l_Total_Prj_Profitablity_Tab(i).COST, 0);
3244 l_Total_Margin := l_Total_Margin + NVL(l_Total_Prj_Profitablity_Tab(i).MARGIN, 0);
3245 l_Total_CT_Revenue := l_Total_CT_Revenue + NVL(l_Total_Prj_Profitablity_Tab(i).CT_REVENUE, 0);
3246 l_Total_CT_COST := l_Total_CT_COST + NVL(l_Total_Prj_Profitablity_Tab(i).CT_COST, 0);
3247 l_Total_CT_Margin := l_Total_CT_Margin + NVL(l_Total_Prj_Profitablity_Tab(i).CT_MARGIN, 0);
3248 */
3249 END LOOP;
3250
3251 /*
3252 ** Return the bulk collected table back to pmv.
3253 */
3254
3255 COMMIT;
3256 RETURN l_Project_Cost_Trend_Tab;
3257
3258 END PLSQLDriver_PJI_REP_PC11;
3259
3260
3261
3262 /*********************************************************************************
3263 **********************************************************************************
3264 **
3265 **
3266 ** Projects Contract Cost Detail Report
3267 **
3268 **
3269 **********************************************************************************
3270 **********************************************************************************
3271 */
3272
3273
3274 FUNCTION PLSQLDriver_PJI_REP_PC13(
3275 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
3276 , p_Organization IN VARCHAR2
3277 , p_Currency_Type IN VARCHAR2
3278 , p_As_of_Date IN NUMBER
3279 , p_Period_Type IN VARCHAR2
3280 , p_View_BY IN VARCHAR2
3281 , p_Classifications IN VARCHAR2 DEFAULT NULL
3282 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
3283 , p_Project_IDS IN VARCHAR2 DEFAULT NULL
3284 , p_Expenditure_Category IN VARCHAR2 DEFAULT NULL
3285 , p_Expenditure_Type IN VARCHAR2 DEFAULT NULL
3286 , p_Work_Type IN VARCHAR2 DEFAULT NULL
3287 ) RETURN PJI_REP_PC13_TBL
3288 IS
3289
3290 PRAGMA AUTONOMOUS_TRANSACTION;
3291
3292 /*
3293 ** PL/SQL Declaration
3294 */
3295 l_detail_tab PJI_REP_PC13_TBL := PJI_REP_PC13_TBL();
3296
3297 l_Cost NUMBER := 0;
3298 l_Bill_Cost NUMBER := 0;
3299 l_Bill_Cost_Prcnt_Of_Cost NUMBER := 0;
3300 l_Budget NUMBER := 0;
3301 l_Bill_Cost_Prcnt_Of_Budget NUMBER := 0;
3302 l_Non_Bill_Cost NUMBER := 0;
3303
3304 l_Convert_Classification VARCHAR2(1);
3305 l_Convert_Expenditure_Type VARCHAR2(1);
3306 l_Convert_Work_Type VARCHAR2(1);
3307 l_curr_record_type_id NUMBER:= 1;
3308
3309 BEGIN
3310
3311 BEGIN
3312 SELECT report_cost_type
3313 INTO G_Report_Cost_Type
3314 FROM pji_system_settings;
3315 EXCEPTION
3316 WHEN NO_DATA_FOUND THEN
3317 G_Report_Cost_Type:='RC';
3318 END;
3319
3320 PJI_PMV_ENGINE.Convert_Operating_Unit(P_OPERATING_UNIT_IDS => p_Operating_Unit
3321 , P_VIEW_BY => p_View_BY
3322 );
3323
3324 PJI_PMV_ENGINE.Convert_Organization(P_TOP_ORGANIZATION_ID => p_Organization
3325 , P_VIEW_BY => p_View_BY
3326 );
3327
3328 PJI_PMV_ENGINE.Convert_Time(P_AS_OF_DATE => p_As_of_Date
3329 , P_PERIOD_TYPE => p_Period_Type
3330 , P_VIEW_BY => p_View_By
3331 , P_PARSE_PRIOR => NULL
3332 , P_PARSE_ITD => NULL
3333 , P_FULL_PERIOD_FLAG => 'Y'
3334 );
3335
3336 l_Convert_Classification :=
3337 PJI_PMV_ENGINE.Convert_Classification(p_Classifications, p_Class_Codes, p_View_BY);
3338 l_Convert_Expenditure_Type :=
3339 PJI_PMV_ENGINE.Convert_Expenditure_Type(p_Expenditure_Category, p_Expenditure_Type, p_View_BY);
3340 l_Convert_Work_Type :=
3341 PJI_PMV_ENGINE.Convert_Work_Type(p_Work_Type, p_View_BY);
3342
3343 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
3344
3345 IF p_Project_IDS IS NULL THEN
3346
3347 /*
3348 BEGIN
3349 DELETE pji_pmv_prj_dim_tmp;
3350
3351 INSERT INTO pji_pmv_prj_dim_tmp (id, name)
3352 SELECT DISTINCT prj.project_id, '-1' name
3353 FROM
3354 pji_project_classes PJM
3355 , pji_pmv_cls_dim_tmp PTM
3356 , pji_pmv_orgz_dim_tmp org
3357 , pa_projects_all prj
3358 WHERE
3359 pjm.project_class_id = ptm.id
3360 AND prj.project_id = pjm.project_id
3361 AND prj.carrying_out_organization_id = org.ID;
3362 END;
3363
3364 */
3365
3366 BEGIN
3367 DELETE pji_pmv_prj_dim_tmp;
3368
3369 IF p_Classifications IS NOT NULL THEN
3370 INSERT INTO pji_pmv_prj_dim_tmp (id, name)
3371 SELECT DISTINCT prj.project_id, '-1' name
3372 FROM
3373 pji_project_classes PJM
3374 , (SELECT project_id
3375 FROM pji_project_classes
3376 WHERE class_category = '$PROJECT_TYPE$CONTRACT') PJC
3377 , pji_pmv_cls_dim_tmp PTM
3378 , pji_pmv_orgz_dim_tmp org
3382 AND prj.project_id = pjc.project_id
3379 , pa_projects_all prj
3380 WHERE 1=1
3381 AND pjm.project_class_id = ptm.id
3383 AND prj.project_id = pjm.project_id
3384 AND prj.carrying_out_organization_id = org.ID;
3385 ELSE
3386 INSERT INTO pji_pmv_prj_dim_tmp (id, name)
3387 SELECT DISTINCT prj.project_id, '-1' name
3388 FROM
3389 pji_project_classes PJM
3390 , pji_pmv_orgz_dim_tmp org
3391 , pa_projects_all prj
3392 WHERE
3393 prj.project_id = pjm.project_id
3394 AND prj.carrying_out_organization_id = org.ID
3395 AND pjm.class_category = '$PROJECT_TYPE$CONTRACT';
3396 END IF;
3397 END;
3398 ELSE
3399 PJI_PMV_ENGINE.Convert_Project(P_PROJECT_IDS=>p_Project_IDS
3400 , P_VIEW_BY =>p_View_BY);
3401 END IF;
3402 /*
3403 ** ORG Processing ---------------------------------------------------+
3404 */
3405 IF (l_Convert_Classification = 'N')
3406 and (l_Convert_Expenditure_Type = 'N')
3407 and (l_Convert_Work_Type = 'N')
3408 THEN
3409 SELECT PJI_REP_PC13 (PROJECT_ID
3410 , NULL
3411 , NULL
3412 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3413 , NULL
3414 , NULL
3415 , ORGANIZATION_ID
3416 , NULL
3417 , SUM (COST)
3418 , SUM (BILL_COST)
3419 , NULL
3420 , SUM (BUDGET)
3421 , NULL
3422 , SUM(NON_BILL_COST)
3423 , 0, 0, 0, 0, 0, 0)
3424 BULK COLLECT INTO l_detail_tab
3425 FROM
3426 (SELECT /*+ ORDERED */
3427 FCT.PROJECT_ID AS PROJECT_ID
3428 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3429 , DECODE(TIME.amount_type,1,
3430 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3431 'RC', fct.raw_cost, 0), 0) AS COST
3432 , DECODE(TIME.amount_type,1,
3433 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3434 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3435 , DECODE(NVL(TIME.amount_type,2),2,
3436 DECODE(G_Report_Cost_Type, 'BC', fct.CURR_BGT_BURDENED_COST,
3437 'RC', fct.CURR_BGT_RAW_COST, 0), 0) AS BUDGET
3438 , DECODE(TIME.amount_type,1,
3439 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3440 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3441
3442 FROM PJI_PMV_TIME_DIM_TMP TIME
3443 , PJI_PMV_PRJ_DIM_TMP PRJ
3444 , PJI_PMV_ORGZ_DIM_TMP HORG
3445 , PJI_FP_PROJ_F FCT
3446 , PJI_PMV_ORG_DIM_TMP HOU
3447 WHERE 1=1
3448 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3449 AND FCT.PROJECT_ID = PRJ.ID
3450 AND FCT.PROJECT_ORG_ID = HOU.ID
3451 AND FCT.PROJECT_ORGANIZATION_ID = HORG.ID
3452 AND FCT.TIME_ID = TIME.ID
3453 AND TIME.ID IS NOT NULL
3454 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3455 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3456 ) FCT
3457 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3458
3459 /*
3460 ** -- CLASS Processing ---------------------------------------------------+
3461 */
3462 ELSIF (l_Convert_Classification = 'Y')
3463 and (l_Convert_Expenditure_Type = 'N')
3464 and (l_Convert_Work_Type = 'N')
3465 THEN
3466 SELECT PJI_REP_PC13 (PROJECT_ID
3467 , NULL
3468 , NULL
3469 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3470 , NULL
3471 , NULL
3472 , ORGANIZATION_ID
3473 , NULL
3474 , SUM (COST)
3475 , SUM (BILL_COST)
3476 , NULL
3477 , SUM (BUDGET)
3478 , NULL
3479 , SUM(NON_BILL_COST)
3480 , 0, 0, 0, 0, 0, 0)
3481 BULK COLLECT INTO l_detail_tab
3482 FROM
3483 (SELECT /*+ ORDERED */
3484 FCT.PROJECT_ID AS PROJECT_ID
3485 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3486 , DECODE(TIME.amount_type,1,
3487 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3488 'RC', fct.raw_cost, 0), 0) AS COST
3489 , DECODE(TIME.amount_type,1,
3490 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3491 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3492 , DECODE(NVL(TIME.amount_type,2),2,
3493 DECODE(G_Report_Cost_Type, 'BC', fct.CURR_BGT_BURDENED_COST,
3494 'RC', fct.CURR_BGT_RAW_COST, 0), 0) AS BUDGET
3495 , DECODE(TIME.amount_type,1,
3496 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3497 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3498
3499 FROM PJI_PMV_TIME_DIM_TMP TIME
3500 , PJI_PMV_PRJ_DIM_TMP PRJ
3501 , PJI_FP_PROJ_F FCT
3502 , PJI_PMV_ORG_DIM_TMP HOU
3503 WHERE 1=1
3504 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3505 AND FCT.PROJECT_ID = PRJ.ID
3506 AND FCT.PROJECT_ORG_ID = HOU.ID
3507 AND FCT.TIME_ID = TIME.ID
3511 ) FCT
3508 AND TIME.ID IS NOT NULL
3509 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3510 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3512 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3513
3514
3515
3516 /*
3517 **
3518 ** Expenditure Type Processing
3519 **
3520 */
3521
3522 ELSIF (l_Convert_Classification = 'N')
3523 and (l_Convert_Expenditure_Type = 'Y')
3524 and (l_Convert_Work_Type = 'N')
3525 THEN
3526 SELECT PJI_REP_PC13 (PROJECT_ID
3527 , NULL
3528 , NULL
3529 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3530 , NULL
3531 , NULL
3532 , ORGANIZATION_ID
3533 , NULL
3534 , SUM (COST)
3535 , SUM (BILL_COST)
3536 , NULL
3537 , SUM (BUDGET)
3538 , NULL
3539 , SUM(NON_BILL_COST)
3540 , 0, 0, 0, 0, 0, 0)
3541 BULK COLLECT INTO l_detail_tab
3542 FROM
3543 (SELECT /*+ ORDERED */
3544 FCT.PROJECT_ID AS PROJECT_ID
3545 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3546 , DECODE(TIME.amount_type,1,
3547 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3548 'RC', fct.raw_cost, 0), 0) AS COST
3549 , DECODE(TIME.amount_type,1,
3550 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3551 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3552 , NULL AS BUDGET
3553 , DECODE(TIME.amount_type,1,
3554 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3555 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3556
3557 FROM PJI_PMV_TIME_DIM_TMP TIME
3558 , PJI_PMV_PRJ_DIM_TMP PRJ
3559 , PJI_PMV_ET_RT_DIM_TMP ET
3560 , PJI_FP_PROJ_ET_F FCT
3561 , PJI_PMV_ORG_DIM_TMP HOU
3562 WHERE 1=1
3563 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3564 AND FCT.PROJECT_ID = PRJ.ID
3565 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3566 AND ET.record_type ='ET'
3567 AND FCT.PROJECT_ORG_ID = HOU.ID
3568 AND FCT.TIME_ID = TIME.ID
3569 AND TIME.ID IS NOT NULL
3570 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3571 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3572 ) FCT
3573 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3574 /*
3575 **
3576 ** Work Type Processing
3577 **
3578 */
3579
3580 ELSIF (l_Convert_Classification = 'N')
3581 and (l_Convert_Expenditure_Type = 'N')
3582 and (l_Convert_Work_Type = 'Y')
3583 THEN
3584
3585 SELECT PJI_REP_PC13 (PROJECT_ID
3586 , NULL
3587 , NULL
3588 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3589 , NULL
3590 , NULL
3591 , ORGANIZATION_ID
3592 , NULL
3593 , SUM (COST)
3594 , SUM (BILL_COST)
3595 , NULL
3596 , SUM (BUDGET)
3597 , NULL
3598 , SUM(NON_BILL_COST)
3599 , 0, 0, 0, 0, 0, 0)
3600 BULK COLLECT INTO l_detail_tab
3601 FROM
3602 (SELECT /*+ ORDERED */
3603 FCT.PROJECT_ID AS PROJECT_ID
3604 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3605 , DECODE(TIME.amount_type,1,
3606 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3607 'RC', fct.raw_cost, 0), 0) AS COST
3608 , DECODE(TIME.amount_type,1,
3609 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3610 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3611 , NULL AS BUDGET
3612 , DECODE(TIME.amount_type,1,
3613 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3614 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3615
3616 FROM PJI_PMV_TIME_DIM_TMP TIME
3617 , PJI_PMV_PRJ_DIM_TMP PRJ
3618 , PJI_PMV_WT_DIM_TMP WT
3619 , PJI_FP_PROJ_ET_WT_F FCT
3620 , PJI_PMV_ORG_DIM_TMP HOU
3621 WHERE 1=1
3622 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3623 AND FCT.PROJECT_ID = PRJ.ID
3624 AND FCT.WORK_TYPE_ID = WT.ID
3625 AND FCT.PROJECT_ORG_ID = HOU.ID
3626 AND FCT.TIME_ID = TIME.ID
3627 AND TIME.ID IS NOT NULL
3628 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3629 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3630 ) FCT
3631 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3632
3633 /*
3634 **
3635 ** Classification and Expenditure Type Processing
3636 **
3637 */
3638
3639 ELSIF (l_Convert_Classification = 'Y')
3640 and (l_Convert_Expenditure_Type = 'Y')
3641 and (l_Convert_Work_Type = 'N')
3642 THEN
3643
3644 SELECT PJI_REP_PC13 (PROJECT_ID
3645 , NULL
3646 , NULL
3647 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3648 , NULL
3649 , NULL
3650 , ORGANIZATION_ID
3651 , NULL
3652 , SUM (COST)
3653 , SUM (BILL_COST)
3654 , NULL
3655 , SUM (BUDGET)
3656 , NULL
3657 , SUM(NON_BILL_COST)
3658 , 0, 0, 0, 0, 0, 0)
3659 BULK COLLECT INTO l_detail_tab
3660 FROM
3664 , DECODE(TIME.amount_type,1,
3661 (SELECT /*+ ORDERED */
3662 FCT.PROJECT_ID AS PROJECT_ID
3663 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3665 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3666 'RC', fct.raw_cost, 0), 0) AS COST
3667 , DECODE(TIME.amount_type,1,
3668 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3669 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3670 , NULL AS BUDGET
3671 , DECODE(TIME.amount_type,1,
3672 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3673 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3674
3675 FROM PJI_PMV_TIME_DIM_TMP TIME
3676 , PJI_PMV_PRJ_DIM_TMP PRJ
3677 , PJI_PMV_ET_RT_DIM_TMP ET
3678 , PJI_FP_PROJ_ET_F FCT
3679 , PJI_PMV_ORG_DIM_TMP HOU
3680 WHERE 1=1
3681 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3682 AND FCT.PROJECT_ID = PRJ.ID
3683 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3684 AND ET.record_type ='ET'
3685 AND FCT.PROJECT_ORG_ID = HOU.ID
3686 AND FCT.TIME_ID = TIME.ID
3687 AND TIME.ID IS NOT NULL
3688 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3689 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3690 ) FCT
3691 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3692 /*
3693 **
3694 ** Expenditure Type and Work Type Processing
3695 **
3696 */
3697
3698 ELSIF (l_Convert_Classification = 'N')
3699 and (l_Convert_Expenditure_Type = 'Y')
3700 and (l_Convert_Work_Type = 'Y')
3701 THEN
3702
3703 SELECT PJI_REP_PC13 (PROJECT_ID
3704 , NULL
3705 , NULL
3706 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3707 , NULL
3708 , NULL
3709 , ORGANIZATION_ID
3710 , NULL
3711 , SUM (COST)
3712 , SUM (BILL_COST)
3713 , NULL
3714 , SUM (BUDGET)
3715 , NULL
3716 , SUM(NON_BILL_COST)
3717 , 0, 0, 0, 0, 0, 0)
3718 BULK COLLECT INTO l_detail_tab
3719 FROM
3720 (SELECT /*+ ORDERED */
3721 FCT.PROJECT_ID AS PROJECT_ID
3722 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3723 , DECODE(TIME.amount_type,1,
3724 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3725 'RC', fct.raw_cost, 0), 0) AS COST
3726 , DECODE(TIME.amount_type,1,
3727 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3728 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3729 , NULL AS BUDGET
3730 , DECODE(TIME.amount_type,1,
3731 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3732 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3733
3734 FROM PJI_PMV_TIME_DIM_TMP TIME
3735 , PJI_PMV_PRJ_DIM_TMP PRJ
3736 , PJI_PMV_ET_RT_DIM_TMP ET
3737 , PJI_PMV_WT_DIM_TMP WT
3738 , PJI_FP_PROJ_ET_WT_F FCT
3739 , PJI_PMV_ORG_DIM_TMP HOU
3740 WHERE 1=1
3741 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3742 AND FCT.PROJECT_ID = PRJ.ID
3743 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3744 AND ET.record_type ='ET'
3745 AND FCT.WORK_TYPE_ID = WT.ID
3746 AND FCT.PROJECT_ORG_ID = HOU.ID
3747 AND FCT.TIME_ID = TIME.ID
3748 AND TIME.ID IS NOT NULL
3749 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3750 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3751 ) FCT
3752 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3753 /*
3754 **
3755 ** Classification and Work Type Processing
3756 **
3757 */
3758
3759 ELSIF (l_Convert_Classification = 'Y')
3760 and (l_Convert_Expenditure_Type = 'N')
3761 and (l_Convert_Work_Type = 'Y')
3762 THEN SELECT PJI_REP_PC13 (PROJECT_ID
3763 , NULL
3764 , NULL
3765 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3766 , NULL
3767 , NULL
3768 , ORGANIZATION_ID
3769 , NULL
3770 , SUM (COST)
3771 , SUM (BILL_COST)
3772 , NULL
3773 , SUM (BUDGET)
3774 , NULL
3775 , SUM(NON_BILL_COST)
3776 , 0, 0, 0, 0, 0, 0)
3777 BULK COLLECT INTO l_detail_tab
3778 FROM
3779 (SELECT /*+ ORDERED */
3780 FCT.PROJECT_ID AS PROJECT_ID
3781 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3782 , DECODE(TIME.amount_type,1,
3783 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3784 'RC', fct.raw_cost, 0), 0) AS COST
3785 , DECODE(TIME.amount_type,1,
3786 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3787 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3788 , NULL AS BUDGET
3789 , DECODE(TIME.amount_type,1,
3790 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3791 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3792
3793 FROM PJI_PMV_TIME_DIM_TMP TIME
3794 , PJI_PMV_PRJ_DIM_TMP PRJ
3798 WHERE 1=1
3795 , PJI_PMV_WT_DIM_TMP WT
3796 , PJI_FP_PROJ_ET_WT_F FCT
3797 , PJI_PMV_ORG_DIM_TMP HOU
3799 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3800 AND FCT.PROJECT_ID = PRJ.ID
3801 AND FCT.WORK_TYPE_ID = WT.ID
3802 AND FCT.PROJECT_ORG_ID = HOU.ID
3803 AND FCT.TIME_ID = TIME.ID
3804 AND TIME.ID IS NOT NULL
3805 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3806 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3807 ) FCT
3808 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3809
3810 ELSE
3811
3812 SELECT PJI_REP_PC13 (PROJECT_ID
3813 , NULL
3814 , NULL
3815 , PJI_PMV_UTIL.Drill_To_Proj_Perf_URL(PROJECT_ID, l_curr_record_type_id, p_As_of_Date,p_Period_Type)
3816 , NULL
3817 , NULL
3818 , ORGANIZATION_ID
3819 , NULL
3820 , SUM (COST)
3821 , SUM (BILL_COST)
3822 , NULL
3823 , SUM (BUDGET)
3824 , NULL
3825 , SUM(NON_BILL_COST)
3826 , 0, 0, 0, 0, 0, 0)
3827 BULK COLLECT INTO l_detail_tab
3828 FROM
3829 (SELECT /*+ ORDERED */
3830 FCT.PROJECT_ID AS PROJECT_ID
3831 , FCT.PROJECT_ORGANIZATION_ID AS ORGANIZATION_ID
3832 , DECODE(TIME.amount_type,1,
3833 DECODE(G_Report_Cost_Type, 'BC', fct.burdened_cost,
3834 'RC', fct.raw_cost, 0), 0) AS COST
3835 , DECODE(TIME.amount_type,1,
3836 DECODE(G_Report_Cost_Type, 'BC', fct.bill_burdened_cost,
3837 'RC', fct.bill_raw_cost, 0), 0) AS BILL_COST
3838 , NULL AS BUDGET
3839 , DECODE(TIME.amount_type,1,
3840 DECODE(G_Report_Cost_Type, 'BC', fct.BURDENED_COST - fct.bill_burdened_cost,
3841 'RC', fct.RAW_COST - fct.bill_raw_cost, 0), 0) AS NON_BILL_COST
3842
3843 FROM PJI_PMV_TIME_DIM_TMP TIME
3844 , PJI_PMV_PRJ_DIM_TMP PRJ
3845 , PJI_PMV_ET_RT_DIM_TMP ET
3846 , PJI_PMV_WT_DIM_TMP WT
3847 , PJI_FP_PROJ_ET_WT_F FCT
3848 , PJI_PMV_ORG_DIM_TMP HOU
3849 WHERE 1=1
3850 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
3851 AND FCT.PROJECT_ID = PRJ.ID
3852 AND FCT.EXP_EVT_TYPE_ID = ET.ID
3853 AND ET.record_type ='ET'
3854 AND FCT.WORK_TYPE_ID = WT.ID
3855 AND FCT.PROJECT_ORG_ID = HOU.ID
3856 AND FCT.TIME_ID = TIME.ID
3857 AND TIME.ID IS NOT NULL
3858 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
3859 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
3860 ) FCT
3861 GROUP BY PROJECT_ID,ORGANIZATION_ID;
3862 END IF;
3863
3864 FOR i IN 1..l_detail_tab.COUNT
3865 LOOP
3866 /*
3867 ** FETCH THE PRIMARY CUSTOMER NAME AND PROJECT MANAGER NAME.
3868 */
3869
3870 l_detail_tab(i).PRIMARY_CUSTOMER_NAME:=PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME(l_detail_tab(i).PROJECT_ID);
3871 l_detail_tab(i).PERSON_MANAGER_NAME:=PA_PROJECTS_MAINT_UTILS.GET_PROJECT_MANAGER_NAME(l_detail_tab(i).PROJECT_ID);
3872
3873
3874 /*
3875 ** FETCH THE PROJECT ATTRIBUTES.
3876 */
3877 SELECT NAME
3878 , SEGMENT1
3879 , PROJECT_TYPE
3880 INTO l_detail_tab(i).PROJECT_NAME
3881 , l_detail_tab(i).PROJECT_NUMBER
3882 , l_detail_tab(i).PROJECT_TYPE
3883 FROM PA_PROJECTS_ALL
3884 WHERE PROJECT_ID = l_detail_tab(i).PROJECT_ID;
3885
3886
3887 /*
3888 ** FETCH THE ORGANIZATION NAME.
3889 */
3890 SELECT NAME
3891 INTO l_detail_tab(i).ORGANIZATION_NAME
3892 FROM HR_ALL_ORGANIZATION_UNITS_TL
3893 WHERE LANGUAGE = USERENV ('LANG')
3894 AND ORGANIZATION_ID = l_detail_tab(i).ORGANIZATION_NAME;
3895
3896
3897 IF NVL(l_detail_tab(i).COST, 0) <> 0 THEN
3898 l_detail_tab(i).BILL_COST_PRCNT_OF_COST := 100*((l_detail_tab(i).BILL_COST)/ABS(l_detail_tab(i).COST));
3899 ELSE
3900 l_detail_tab(i).BILL_COST_PRCNT_OF_COST := NULL;
3901 END IF;
3902
3903 IF NVL(l_detail_tab(i).BUDGET, 0) <> 0 THEN
3904 l_detail_tab(i).BILL_PRCNT_OF_BUDGET_COST := 100*((l_detail_tab(i).BILL_COST)/ABS(l_detail_tab(i).BUDGET));
3905 ELSE
3906 l_detail_tab(i).BILL_PRCNT_OF_BUDGET_COST := NULL;
3907 END IF;
3908
3909 l_Cost := l_Cost + NVL(l_detail_tab(i).COST , 0);
3910 l_Bill_Cost := l_Bill_Cost + NVL(l_detail_tab(i).BILL_COST, 0);
3911 l_Budget := l_Budget + NVL(l_detail_tab(i).BUDGET , 0);
3912 l_Non_Bill_Cost := l_Non_Bill_Cost + NVL(l_detail_tab(i).NON_BILL_COST , 0);
3913
3914 END LOOP;
3915
3916 FOR i IN 1..l_detail_tab.COUNT
3917
3918 LOOP
3919 l_detail_tab(i).PJI_REP_TOTAL_1:=l_Cost;
3920 l_detail_tab(i).PJI_REP_TOTAL_2:=l_Bill_Cost;
3921 l_detail_tab(i).PJI_REP_TOTAL_4:=l_Budget;
3922 l_detail_tab(i).PJI_REP_TOTAL_6:=l_Non_Bill_Cost;
3923
3924 IF NVL(l_detail_tab(i).PJI_REP_TOTAL_1, 0) <> 0 THEN
3925 l_detail_tab(i).PJI_REP_TOTAL_3 := 100*((l_detail_tab(i).PJI_REP_TOTAL_2)/ABS(l_detail_tab(i).PJI_REP_TOTAL_1));
3926 ELSE
3927 l_detail_tab(i).PJI_REP_TOTAL_3 := NULL;
3928 END IF;
3929
3930 IF NVL(l_detail_tab(i).PJI_REP_TOTAL_4, 0) <> 0 THEN
3931 l_detail_tab(i).PJI_REP_TOTAL_5 := 100*((l_detail_tab(i).PJI_REP_TOTAL_2)/ABS(l_detail_tab(i).PJI_REP_TOTAL_4));
3932 ELSE
3933 l_detail_tab(i).PJI_REP_TOTAL_5 := NULL;
3934 END IF;
3935
3936 END LOOP;
3937
3938 /*
3939 ** Return the bulk collected table back to pmv.
3940 */
3941
3942 COMMIT;
3943
3944 RETURN l_detail_tab;
3945
3946
3947 END PLSQLDriver_PJI_REP_PC13;
3948
3949
3950 END PJI_PMV_COST;