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