[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_PROFITABILITY_TREND
Source
1 PACKAGE BODY PJI_PMV_PROFITABILITY_TREND AS
2 /* $Header: PJIRF05B.pls 120.4 2005/10/11 18:10:45 appldev noship $ */
3
4 /*
5 ** ----------------------------------------------------------
6 ** Procedure: Get_SQL_PJI_REP_PP5
7 ** This procedure returns sql statement generated by the base
8 ** engine api and view by as a out parameter for the report
9 ** PJI_REP_PP5.
10 ** ----------------------------------------------------------
11 */
12 PROCEDURE Get_SQL_PJI_REP_PP5(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 BEGIN
17 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
18 , P_SELECT_LIST =>
19 ' FACT.REVENUE "PJI_REP_MSR_1"
20 , FACT.PY_REVENUE "PJI_REP_MSR_2"
21 , FACT.REV_CHANGE_PERCENT "PJI_REP_MSR_3"
22 , FACT.MARGIN_PERCENT "PJI_REP_MSR_4"
23 , FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_5"
24 , FACT.MAR_CHANGE_PERCENT "PJI_REP_MSR_6"
25 , FACT.MARGIN "PJI_REP_MSR_8"
26 , FACT.PY_MARGIN "PJI_REP_MSR_9"
27 , FACT.PY_REVENUE "PJI_REP_MSR_11"
28 , FACT.REVENUE "PJI_REP_MSR_10"
29 , FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_12"
30 , FACT.MARGIN_PERCENT "PJI_REP_MSR_13" '
31 , P_SQL_STATEMENT => x_PMV_Sql
32 , P_PMV_OUTPUT => x_PMV_Output
33 , P_REGION_CODE => 'PJI_REP_PP5'
34 , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
35 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
36 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
37 ', <<CURRENCY+FII_CURRENCIES>>'||
38 ', <<AS_OF_DATE>>'||
39 ', <<PERIOD_TYPE>>'||
40 ', <<VIEW_BY>>'||
41 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
42 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
43 ', NULL'||
44 ', NULL'||
45 ', NULL'||
46 ', <<PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES>>'||
47 ', <<PROJECT REVENUE CATEGORY+CLASS_CODE>>'||
48 ', NULL'
49 );
50 END Get_SQL_PJI_REP_PP5;
51
52 /*
53 ** ----------------------------------------------------------
54 ** Procedure: Get_SQL_PJI_REP_PP6
55 ** This procedure returns sql statement generated by the base
56 ** engine api and view by as a out parameter for the report
57 ** PJI_REP_PP6.
58 ** ----------------------------------------------------------
59 */
60 PROCEDURE Get_SQL_PJI_REP_PP6(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
61 , x_PMV_Sql OUT NOCOPY VARCHAR2
62 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
63 IS
64 l_PMV_Rec BIS_QUERY_ATTRIBUTES;
65 BEGIN
66 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
67 , P_SELECT_LIST =>
68 ' FACT.REVENUE "PJI_REP_MSR_1"
69 , FACT.PY_REVENUE "PJI_REP_MSR_2"
70 , FACT.REV_CHANGE_PERCENT "PJI_REP_MSR_3"
71 , FACT.MARGIN_PERCENT "PJI_REP_MSR_4"
72 , FACT.PY_MARGIN_PERCENT "PJI_REP_MSR_5"
73 , FACT.MAR_CHANGE_PERCENT "PJI_REP_MSR_6"
74 , FACT.MARGIN "PJI_REP_MSR_8"
75 , FACT.PY_MARGIN "PJI_REP_MSR_9"
76 , FACT.REVENUE "PJI_REP_MSR_10"
77 , FACT.MARGIN_PERCENT "PJI_REP_MSR_11" '
78 , P_SQL_STATEMENT => x_PMV_Sql
79 , P_PMV_OUTPUT => x_PMV_Output
80 , P_REGION_CODE => 'PJI_REP_PP6'
81 , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
82 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
83 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
84 ', <<CURRENCY+FII_CURRENCIES>>'||
85 ', <<AS_OF_DATE>>'||
86 ', <<PERIOD_TYPE>>'||
87 ', <<VIEW_BY>>'||
88 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
89 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
90 ', :PJI_EXTRA_BND_01'||
91 ', NULL'||
92 ', NULL'||
93 ', <<PROJECT REVENUE CATEGORY+PJI_REVENUE_CATEGORIES>>'||
94 ', <<PROJECT REVENUE CATEGORY+CLASS_CODE>>'||
95 ', NULL'
96 );
97
98 l_PMV_Rec:=BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
99 l_PMV_Rec.attribute_name:=':PJI_EXTRA_BND_01';
100 l_PMV_Rec.attribute_value:='FISCAL';
101 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
102 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
103
104 x_PMV_Output.EXTEND();
105 x_PMV_Output(x_PMV_Output.COUNT):=l_PMV_Rec;
106
107 END Get_SQL_PJI_REP_PP6;
108
109 /*
110 ** ----------------------------------------------------------
111 ** Procedure: Get_SQL_PJI_REP_PP7
112 ** This procedure returns sql statement generated by the base
113 ** engine api and view by as a out parameter for the report
114 ** PJI_REP_PP7.
115 ** ----------------------------------------------------------
116 */
117 PROCEDURE Get_SQL_PJI_REP_PP7(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
118 , x_PMV_Sql OUT NOCOPY VARCHAR2
119 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
120 IS
121 BEGIN
122 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
123 , P_SQL_STATEMENT => x_PMV_Sql
124 , P_SELECT_LIST =>
125 ' FACT.COST "PJI_REP_MSR_1"
126 , FACT.PY_COST "PJI_REP_MSR_2"
127 , FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
128 , FACT.COST "PJI_REP_MSR_4" '
129 , P_PMV_OUTPUT => x_PMV_Output
130 , P_REGION_CODE => 'PJI_REP_PP7'
131 , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
132 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
133 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
134 ', <<CURRENCY+FII_CURRENCIES>>'||
135 ', <<AS_OF_DATE>>'||
136 ', <<PERIOD_TYPE>>'||
137 ', <<VIEW_BY>>'||
138 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
139 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
140 ', NULL'||
141 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES>>'||
142 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES>>'||
143 ', NULL'||
144 ', NULL'||
145 ', <<PROJECT WORK TYPE+PJI_WORK_TYPES>> '
146 );
147 END Get_SQL_PJI_REP_PP7;
148
149 /*
150 ** ----------------------------------------------------------
151 ** Procedure: Get_SQL_PJI_REP_PP8
152 ** This procedure returns sql statement generated by the base
153 ** engine api and view by as a out parameter for the report
154 ** PJI_REP_PP8.
155 ** ----------------------------------------------------------
156 */
157 PROCEDURE Get_SQL_PJI_REP_PP8(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
158 , x_PMV_Sql OUT NOCOPY VARCHAR2
159 , x_PMV_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
160 IS
161 l_PMV_Rec BIS_QUERY_ATTRIBUTES;
162 BEGIN
163 PJI_PMV_ENGINE.Generate_SQL(P_PAGE_PARAMETER_TBL => p_page_parameter_tbl
164 , P_SQL_STATEMENT => x_PMV_Sql
165 , P_SELECT_LIST =>
166 ' FACT.COST "PJI_REP_MSR_1"
167 , FACT.PY_COST "PJI_REP_MSR_2"
168 , FACT.CST_CHANGE_PERCENT "PJI_REP_MSR_3"
169 , FACT.COST "PJI_REP_MSR_4" '
170 , P_PMV_OUTPUT => x_PMV_Output
171 , P_REGION_CODE => 'PJI_REP_PP8'
172 , P_PLSQL_DRIVER => 'PJI_PMV_PROFITABILITY_TREND.PLSQLDriver_PJI_REP_PP5'
173 , P_PLSQL_DRIVER_PARAMS => ' <<ORGANIZATION+FII_OPERATING_UNITS>>'||
174 ', <<ORGANIZATION+PJI_ORGANIZATIONS>>'||
175 ', <<CURRENCY+FII_CURRENCIES>>'||
176 ', <<AS_OF_DATE>>'||
177 ', <<PERIOD_TYPE>>'||
178 ', <<VIEW_BY>>'||
179 ', <<PROJECT CLASSIFICATION+CLASS_CATEGORY>>'||
180 ', <<PROJECT CLASSIFICATION+CLASS_CODE>>'||
181 ', :PJI_EXTRA_BND_01'||
182 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_CATEGORIES>>'||
183 ', <<PROJECT EXPENDITURE TYPE+PJI_EXP_TYPES>>'||
184 ', NULL'||
185 ', NULL'||
186 ', <<PROJECT WORK TYPE+PJI_WORK_TYPES>> '
187 );
188
189 l_PMV_Rec:=BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
190 l_PMV_Rec.attribute_name:=':PJI_EXTRA_BND_01';
191 l_PMV_Rec.attribute_value:='FISCAL';
192 l_PMV_Rec.attribute_type:=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
193 l_PMV_Rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
194
195 x_PMV_Output.EXTEND();
196 x_PMV_Output(x_PMV_Output.COUNT):=l_PMV_Rec;
197 END Get_SQL_PJI_REP_PP8;
198
199 /*
200 ** ----------------------------------------------------------
201 ** Function: PLSQLDriver_PJI_REP_PP5
202 ** This table function is called from select statement
203 ** generated by PJI engine. The function returns pl/sql table
204 ** of records which have to be displayed in the pmv report.
205 ** Following are the reports to which this function caters:
206 ** 1. PJI_REP_PP5 - Project Profitability Trend
207 ** 2. PJI_REP_PP6 - Project Profitability Cumulative Trend
208 ** 3. PJI_REP_PP7 - Project Cost Trend
209 ** 4. PJI_REP_PP8 - Project Cost Cumulative Trend
210 ** ----------------------------------------------------------
211 */
212 FUNCTION PLSQLDriver_PJI_REP_PP5(
213 p_Operating_Unit IN VARCHAR2 DEFAULT NULL
214 , p_Organization IN VARCHAR2
215 , p_Currency_Type IN VARCHAR2
216 , p_As_Of_Date IN NUMBER
217 , p_Period_Type IN VARCHAR2
218 , p_View_BY IN VARCHAR2
219 , p_Classifications IN VARCHAR2 DEFAULT NULL
220 , p_Class_Codes IN VARCHAR2 DEFAULT NULL
221 , p_Report_Type IN VARCHAR2 DEFAULT NULL
222
223 , p_Expenditure_Category IN VARCHAR2 DEFAULT NULL
224 , p_Expenditure_Type IN VARCHAR2 DEFAULT NULL
225 , p_Revenue_Category IN VARCHAR2 DEFAULT NULL
226 , p_Revenue_Type IN VARCHAR2 DEFAULT NULL
227 , p_Work_Type IN VARCHAR2 DEFAULT NULL
228
229 )RETURN PJI_REP_PP5_TBL
230 IS
231 PRAGMA AUTONOMOUS_TRANSACTION;
232 l_Total_Prj_Profitablity_Tab PJI_REP_PP5_TBL:=PJI_REP_PP5_TBL();
233 l_Parse_Class_Codes VARCHAR2(1);
234 l_Report_Cost_Type VARCHAR2(2);
235
236 l_Revenue NUMBER:=0;
237 l_Cost NUMBER:=0;
238 l_Margin NUMBER:=0;
239 l_Margin_Percent NUMBER:=0;
240
241 l_PY_Revenue NUMBER:=0;
242 l_PY_Cost NUMBER:=0;
243 l_PY_Margin NUMBER:=0;
244 l_PY_Margin_Percent NUMBER:=0;
245
246 l_Rev_Change_Percent NUMBER:=0;
247 l_Cst_Change_Percent NUMBER:=0;
248 l_Mar_Change_Percent NUMBER:=0;
249
250 l_Total_Revenue NUMBER:=0;
251 l_Total_Cost NUMBER:=0;
252 l_Total_Margin NUMBER:=0;
253
254 l_Total_PY_Revenue NUMBER:=0;
255 l_Total_PY_Cost NUMBER:=0;
256 l_Total_PY_Margin NUMBER:=0;
257
258 l_Top_Organization_Name VARCHAR2(240);
259
260 l_Convert_Classification VARCHAR2(1);
261 l_Convert_Expenditure_Type VARCHAR2(1);
262 l_Convert_Event_Revenue_Type VARCHAR2(1);
263 l_Convert_Work_Type VARCHAR2(1);
264
265 l_curr_record_type_id NUMBER:= 1;
266
267 BEGIN
268
269 BEGIN
270 SELECT report_cost_type
271 INTO l_Report_Cost_Type
272 FROM pji_system_settings;
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 l_Report_Cost_Type:='RC';
276 END;
277
278 /*
279 ** Place a call to all the parse API's which parse the
280 ** parameters passed by PMV and populate all the
281 ** temporary tables.
282 */
283 PJI_PMV_ENGINE.Convert_Operating_Unit(p_Operating_Unit_IDS=>p_Operating_Unit, p_View_BY=>p_View_BY);
284 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);
285 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);
286
287 l_Convert_Classification := PJI_PMV_ENGINE.Convert_Classification(p_Classifications, p_Class_Codes, p_View_BY);
288 l_Convert_Expenditure_Type := PJI_PMV_ENGINE.Convert_Expenditure_Type(p_Expenditure_Category, p_Expenditure_Type, p_View_BY);
289 l_Convert_Event_Revenue_Type := PJI_PMV_ENGINE.Convert_Event_Revenue_Type(p_Revenue_Category, p_Revenue_Type, p_View_BY );
290 l_Convert_Work_Type := PJI_PMV_ENGINE.Convert_Work_Type(p_Work_Type, p_View_BY);
291 l_curr_record_type_id:=PJI_PMV_ENGINE.Convert_Currency_Record_Type(p_Currency_Type);
292
293 /*
294 ** Determine the fact tables you choose to run the database
295 ** query on ( this step is what we call manual query re-write).
296 */
297
298
299 /*
300 ** Code the SQL statement for all of the following conditions
301 ** 1. Current Year
302 ** 2. Prior Year
303 ** 3. SQL to generate rows with zero's for the view by dimension
304 ** Bulk-Collect the output into a pl/sql table to be returned to
305 ** pmv.
306 */
307
308 /*
309 ** ORG Processing ---------------------------------------------------+
310 */
311
312
313 /* ----------------------------- Case 1 truth table ------------------------------------ */
314
315 IF (l_Convert_Classification = 'N')
316 and (l_Convert_Expenditure_Type = 'N')
317 and (l_Convert_Event_Revenue_Type = 'N')
318 and (l_Convert_Work_Type = 'N')
319 THEN
320
321
322 SELECT PJI_REP_PP5(
323 TIME_ID
324 , SUM( REVENUE )
325 , SUM( COST )
326 , SUM( REVENUE-COST )
327 , SUM( PY_REVENUE )
328 , SUM( PY_COST )
329 , SUM( PY_REVENUE-PY_COST )
330 , 0, 0, 0, 0, 0)
331 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
332 FROM (
333 SELECT /*+ ORDERED */
334 TIME.name time_id
335 , TIME.order_by_id time_key
336 , revenue revenue
337 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
338 , 0 py_revenue
339 , 0 py_cost
340 FROM
341 pji_pmv_time_dim_tmp TIME
342 , pji_pmv_orgz_dim_tmp HORG
343 , pji_fp_orgo_f_mv FCT
344 , pji_pmv_org_dim_tmp HOU
345 WHERE
346 FCT.org_id = HOU.id
347 AND FCT.organization_id = HORG.id
348 AND FCT.time_id = TIME.id
349 AND TIME.id IS NOT NULL
350 AND FCT.period_type_id = TIME.period_type
351 AND FCT.calendar_type = TIME.calendar_type
352 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
353 UNION ALL
354 SELECT /*+ ORDERED */
355 TIME.name time_id
356 , TIME.order_by_id time_key
357 , 0 revenue
358 , 0 cost
359 , revenue py_revenue
360 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
361 FROM
362 pji_pmv_time_dim_tmp TIME
363 , pji_pmv_orgz_dim_tmp HORG
364 , pji_fp_orgo_f_mv FCT
365 , pji_pmv_org_dim_tmp HOU
366 WHERE
367 FCT.org_id = HOU.id
368 AND FCT.organization_id = HORG.id
369 AND FCT.time_id = TIME.prior_id
370 AND TIME.prior_id IS NOT NULL
371 AND FCT.period_type_id = TIME.period_type
372 AND FCT.calendar_type = TIME.calendar_type
373 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
374 UNION ALL
375 SELECT
376 name time_id
377 , order_by_id time_key
378 , 0 revenue
379 , 0 cost
380 , 0 py_revenue
381 , 0 py_cost
382 FROM pji_pmv_time_dim_tmp
383 WHERE name <> '-1')
384 GROUP BY
385 TIME_KEY
386 , TIME_ID ORDER BY TIME_KEY ASC;
387
388 /*
389 ** -- PROJECT CLASSIFICATION Processing ---------------------------------------------------+
390 */
391
392
393 /* ----------------------------------- Case 2 truth table ------------------------------------- */
394
395 ELSIF (l_Convert_Expenditure_Type = 'N')
396 and (l_Convert_Event_Revenue_Type = 'N')
397 and (l_Convert_Work_Type = 'N')
398
399 THEN
400
401
402 SELECT PJI_REP_PP5(
403 TIME_ID
404 , SUM( REVENUE )
405 , SUM( COST )
406 , SUM( REVENUE-COST )
407 , SUM( PY_REVENUE )
408 , SUM( PY_COST )
409 , SUM( PY_REVENUE-PY_COST )
410 , 0, 0, 0, 0, 0)
411 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
412 FROM (
413 SELECT /*+ ORDERED */
414 TIME.name time_id
415 , TIME.order_by_id time_key
416 , revenue revenue
417 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
418 , 0 py_revenue
419 , 0 py_cost
420 FROM
421 pji_pmv_time_dim_tmp TIME
422 , pji_pmv_orgz_dim_tmp HORG
423 , pji_pmv_cls_dim_tmp CLS
424 , pji_fp_clso_f_mv FCT
425 , pji_pmv_org_dim_tmp HOU
426 WHERE
427 FCT.project_org_id = HOU.id
428 AND FCT.project_organization_id = HORG.id
429 AND FCT.time_id = TIME.id
430 AND TIME.id IS NOT NULL
431 AND FCT.period_type_id = TIME.period_type
432 AND FCT.calendar_type = TIME.calendar_type
433 AND FCT.project_class_id = CLS.id
434 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
435 UNION ALL
436 SELECT /*+ ORDERED */
437 TIME.name time_id
438 , TIME.order_by_id time_key
439 , 0 revenue
440 , 0 cost
441 , revenue py_revenue
442 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
443 FROM
444 pji_pmv_time_dim_tmp TIME
445 , pji_pmv_orgz_dim_tmp HORG
446 , pji_pmv_cls_dim_tmp CLS
447 , pji_fp_clso_f_mv FCT
448 , pji_pmv_org_dim_tmp HOU
449 WHERE
450 FCT.project_org_id = HOU.id
451 AND FCT.project_organization_id = HORG.id
452 AND FCT.time_id = TIME.prior_id
453 AND TIME.prior_id IS NOT NULL
454 AND FCT.period_type_id = TIME.period_type
455 AND FCT.calendar_type = TIME.calendar_type
456 AND FCT.project_class_id = CLS.id
457 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
458 UNION ALL
459 SELECT
460 name time_id
461 , order_by_id time_key
462 , 0 revenue
463 , 0 cost
464 , 0 py_revenue
465 , 0 py_cost
466 FROM pji_pmv_time_dim_tmp
467 WHERE name <> '-1')
468 GROUP BY
469 TIME_KEY
470 , TIME_ID ORDER BY TIME_KEY ASC;
471
472 /*
473 ** Expenditure or Revenue Type Processing ---------------------------------------------------+
474 */
475
476 /* ----------------------------------- Case 3 truth table ------------------------------------- */
477
478 ELSIF (l_Convert_Classification = 'N')
479 and (l_Convert_Work_Type = 'N')
480 THEN
481 SELECT PJI_REP_PP5(
482 TIME_ID
483 , SUM( REVENUE )
484 , SUM( COST )
485 , SUM( REVENUE-COST )
486 , SUM( PY_REVENUE )
487 , SUM( PY_COST )
488 , SUM( PY_REVENUE-PY_COST )
489 , 0, 0, 0, 0, 0)
490 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
491 FROM (
492 SELECT /*+ ORDERED */
493 TIME.name time_id
494 , TIME.order_by_id time_key
495 , revenue revenue
496 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
497 , 0 py_revenue
498 , 0 py_cost
499 FROM
500 pji_pmv_time_dim_tmp TIME
501 , pji_pmv_orgz_dim_tmp HORG
502 , pji_pmv_et_rt_dim_tmp ET
503 , PJI_FP_ORGO_ET_F_MV FCT
504 , pji_pmv_org_dim_tmp HOU
505 WHERE
506 FCT.org_id = HOU.id
507 AND FCT.organization_id = HORG.id
508 AND FCT.time_id = TIME.id
509 AND TIME.id IS NOT NULL
510 AND FCT.period_type_id = TIME.period_type
511 AND FCT.calendar_type = TIME.calendar_type
512 AND FCT.EXP_EVT_TYPE_ID = ET.ID
513 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
514
515 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
516 UNION ALL
517 SELECT /*+ ORDERED */
518 TIME.name time_id
519 , TIME.order_by_id time_key
520 , 0 revenue
521 , 0 cost
522 , revenue py_revenue
523 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
524 FROM
525 pji_pmv_time_dim_tmp TIME
526 , pji_pmv_orgz_dim_tmp HORG
527 , pji_pmv_et_rt_dim_tmp ET
528 , PJI_FP_ORGO_ET_F_MV FCT
529 , pji_pmv_org_dim_tmp HOU
530 WHERE
531 FCT.org_id = HOU.id
532 AND FCT.organization_id = HORG.id
533 AND FCT.time_id = TIME.prior_id
534 AND TIME.prior_id IS NOT NULL
535 AND FCT.period_type_id = TIME.period_type
536 AND FCT.calendar_type = TIME.calendar_type
537 AND FCT.EXP_EVT_TYPE_ID = ET.ID
538 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
539 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
540 UNION ALL
541 SELECT
542 name time_id
543 , order_by_id time_key
544 , 0 revenue
545 , 0 cost
546 , 0 py_revenue
547 , 0 py_cost
548 FROM pji_pmv_time_dim_tmp
549 WHERE name <> '-1')
550 GROUP BY
551 TIME_KEY
552 , TIME_ID ORDER BY TIME_KEY ASC;
553
554 /*
555 ** Work Type Processing ---------------------------------------------------+
556 */
557
558 /* ----------------------------------- Case 4 truth table ------------------------------------- */
559
560 ELSIF (l_Convert_Classification = 'N')
561 and (l_Convert_Expenditure_Type = 'N')
562 and (l_Convert_Event_Revenue_Type = 'N')
563 THEN
564 SELECT PJI_REP_PP5(
565 TIME_ID
566 , SUM( REVENUE )
567 , SUM( COST )
568 , SUM( REVENUE-COST )
569 , SUM( PY_REVENUE )
570 , SUM( PY_COST )
571 , SUM( PY_REVENUE-PY_COST )
572 , 0, 0, 0, 0, 0)
573 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
574 FROM (
575 SELECT /*+ ORDERED */
576 TIME.name time_id
577 , TIME.order_by_id time_key
578 , null revenue
579 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
580 , 0 py_revenue
581 , 0 py_cost
582 FROM
583 pji_pmv_time_dim_tmp TIME
584 , pji_pmv_orgz_dim_tmp HORG
585 , pji_pmv_wt_dim_tmp WT
586 , PJI_FP_ORGO_ET_WT_F_MV FCT
587 , pji_pmv_org_dim_tmp HOU
588 WHERE
589 FCT.org_id = HOU.id
590 AND FCT.organization_id = HORG.id
591 AND FCT.time_id = TIME.id
592 AND TIME.id IS NOT NULL
593 AND FCT.period_type_id = TIME.period_type
594 AND FCT.calendar_type = TIME.calendar_type
595 AND FCT.work_type_id = WT.id
596 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
597 UNION ALL
598 SELECT /*+ ORDERED */
599 TIME.name time_id
600 , TIME.order_by_id time_key
601 , 0 revenue
602 , 0 cost
603 , null py_revenue
604 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
605 FROM
606 pji_pmv_time_dim_tmp TIME
607 , pji_pmv_orgz_dim_tmp HORG
608 , pji_pmv_wt_dim_tmp WT
609 , PJI_FP_ORGO_ET_WT_F_MV FCT
610 , pji_pmv_org_dim_tmp HOU
611 WHERE
612 FCT.org_id = HOU.id
613 AND FCT.organization_id = HORG.id
614 AND FCT.time_id = TIME.prior_id
615 AND TIME.prior_id IS NOT NULL
616 AND FCT.period_type_id = TIME.period_type
617 AND FCT.calendar_type = TIME.calendar_type
618 AND FCT.work_type_id = WT.id
619 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
620 UNION ALL
621 SELECT
622 name time_id
623 , order_by_id time_key
624 , 0 revenue
625 , 0 cost
626 , 0 py_revenue
627 , 0 py_cost
628 FROM pji_pmv_time_dim_tmp
629 WHERE name <> '-1')
630 GROUP BY
631 TIME_KEY
632 , TIME_ID ORDER BY TIME_KEY ASC;
633
634 /*
635 ** Project classification and Expenditure or Revenue Type Processing ---------------------------------+
636 */
637
638 /* ----------------------------------- Case 5 truth table ------------------------------------- */
639
640 ELSIF (l_Convert_Work_Type = 'N')
641 THEN
642 SELECT PJI_REP_PP5(
643 TIME_ID
644 , SUM( REVENUE )
645 , SUM( COST )
646 , SUM( REVENUE-COST )
647 , SUM( PY_REVENUE )
648 , SUM( PY_COST )
649 , SUM( PY_REVENUE-PY_COST )
650 , 0, 0, 0, 0, 0)
651 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
652 FROM (
653 SELECT /*+ ORDERED */
654 TIME.name time_id
655 , TIME.order_by_id time_key
656 , revenue revenue
657 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
658 , 0 py_revenue
659 , 0 py_cost
660 FROM
661 pji_pmv_time_dim_tmp TIME
662 , pji_pmv_orgz_dim_tmp HORG
663 , pji_pmv_cls_dim_tmp CLS
664 , pji_pmv_et_rt_dim_tmp ET
665 , PJI_FP_CLSO_ET_F_MV FCT
666 , pji_pmv_org_dim_tmp HOU
667 WHERE
668 FCT.project_org_id = HOU.id
669 AND FCT.project_organization_id = HORG.id
670 AND FCT.time_id = TIME.id
671 AND TIME.id IS NOT NULL
672 AND FCT.period_type_id = TIME.period_type
673 AND FCT.calendar_type = TIME.calendar_type
674 AND FCT.project_class_id = CLS.id
675 AND FCT.EXP_EVT_TYPE_ID = ET.ID
676 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
677 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
678 UNION ALL
679 SELECT /*+ ORDERED */
680 TIME.name time_id
681 , TIME.order_by_id time_key
682 , 0 revenue
683 , 0 cost
684 , revenue py_revenue
685 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
686 FROM
687 pji_pmv_time_dim_tmp TIME
688 , pji_pmv_orgz_dim_tmp HORG
689 , pji_pmv_cls_dim_tmp CLS
690 , pji_pmv_et_rt_dim_tmp ET
691 , PJI_FP_CLSO_ET_F_MV FCT
692 , pji_pmv_org_dim_tmp HOU
693 WHERE
694 FCT.project_org_id = HOU.id
695 AND FCT.project_organization_id = HORG.id
696 AND FCT.time_id = TIME.prior_id
697 AND TIME.prior_id IS NOT NULL
698 AND FCT.period_type_id = TIME.period_type
699 AND FCT.calendar_type = TIME.calendar_type
700 AND FCT.project_class_id = CLS.id
701 AND FCT.EXP_EVT_TYPE_ID = ET.ID
702 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
703 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
704 UNION ALL
705 SELECT
706 name time_id
707 , order_by_id time_key
708 , 0 revenue
709 , 0 cost
710 , 0 py_revenue
711 , 0 py_cost
712 FROM pji_pmv_time_dim_tmp
713 WHERE name <> '-1')
714 GROUP BY
715 TIME_KEY
716 , TIME_ID ORDER BY TIME_KEY ASC;
717
718 /*
719 ** Expenditure or Revenue Type and Work Type Processing -----------------------------------------+
720 */
721
722 /* ----------------------------------- Case 6 truth table ------------------------------------- */
723
724 ELSIF (l_Convert_Classification = 'N')
725 THEN
726
727 SELECT PJI_REP_PP5(
728 TIME_ID
729 , SUM( REVENUE )
730 , SUM( COST )
731 , SUM( REVENUE-COST )
732 , SUM( PY_REVENUE )
733 , SUM( PY_COST )
734 , SUM( PY_REVENUE-PY_COST )
735 , 0, 0, 0, 0, 0)
736 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
737 FROM (
738 SELECT /*+ ORDERED */
739 TIME.name time_id
740 , TIME.order_by_id time_key
741 , null revenue
742 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
743 , 0 py_revenue
744 , 0 py_cost
745 FROM
746 pji_pmv_time_dim_tmp TIME
747 , pji_pmv_orgz_dim_tmp HORG
748 , pji_pmv_et_rt_dim_tmp ET
749 , pji_pmv_wt_dim_tmp WT
750 , PJI_FP_ORGO_ET_WT_F_MV FCT
751 , pji_pmv_org_dim_tmp HOU
752 WHERE
753 FCT.org_id = HOU.id
754 AND FCT.organization_id = HORG.id
755 AND FCT.time_id = TIME.id
756 AND TIME.id IS NOT NULL
757 AND FCT.period_type_id = TIME.period_type
758 AND FCT.calendar_type = TIME.calendar_type
759 AND FCT.EXP_EVT_TYPE_ID = ET.ID
760 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
761 AND FCT.WORK_TYPE_ID = WT.ID
762 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
763 UNION ALL
764 SELECT /*+ ORDERED */
765 TIME.name time_id
766 , TIME.order_by_id time_key
767 , 0 revenue
768 , 0 cost
769 , null py_revenue
770 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
771 FROM
772 pji_pmv_time_dim_tmp TIME
773 , pji_pmv_orgz_dim_tmp HORG
774 , pji_pmv_et_rt_dim_tmp ET
775 , pji_pmv_wt_dim_tmp WT
776 , PJI_FP_ORGO_ET_WT_F_MV FCT
777 , pji_pmv_org_dim_tmp HOU
778 WHERE
779 FCT.org_id = HOU.id
780 AND FCT.organization_id = HORG.id
781 AND FCT.time_id = TIME.prior_id
782 AND TIME.prior_id IS NOT NULL
783 AND FCT.period_type_id = TIME.period_type
784 AND FCT.calendar_type = TIME.calendar_type
785 AND FCT.EXP_EVT_TYPE_ID = ET.ID
786 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
787 AND FCT.WORK_TYPE_ID = WT.ID
788 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
789 UNION ALL
790 SELECT
791 name time_id
792 , order_by_id time_key
793 , 0 revenue
794 , 0 cost
795 , 0 py_revenue
796 , 0 py_cost
797 FROM pji_pmv_time_dim_tmp
798 WHERE name <> '-1')
799 GROUP BY
800 TIME_KEY
801 , TIME_ID ORDER BY TIME_KEY ASC;
802
803 /*
804 ** Project classification and Work Type Processing -----------------------------------------------+
805 */
806
807 /* ----------------------------------- Case 7 truth table ------------------------------------- */
808
809 ELSIF (l_Convert_Expenditure_Type = 'N')
810 and (l_Convert_Event_Revenue_Type = 'N')
811 THEN
812
813 SELECT PJI_REP_PP5(
814 TIME_ID
815 , SUM( REVENUE )
816 , SUM( COST )
817 , SUM( REVENUE-COST )
818 , SUM( PY_REVENUE )
819 , SUM( PY_COST )
820 , SUM( PY_REVENUE-PY_COST )
821 , 0, 0, 0, 0, 0)
822 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
823 FROM (
824 SELECT /*+ ORDERED */
825 TIME.name time_id
826 , TIME.order_by_id time_key
827 , null revenue
828 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
829 , 0 py_revenue
830 , 0 py_cost
831 FROM
832 pji_pmv_time_dim_tmp TIME
833 , pji_pmv_orgz_dim_tmp HORG
834 , pji_pmv_cls_dim_tmp CLS
835 , pji_pmv_wt_dim_tmp WT
836 , PJI_FP_CLSO_ET_WT_F_MV FCT
837 , pji_pmv_org_dim_tmp HOU
838 WHERE
839 FCT.project_org_id = HOU.id
840 AND FCT.project_organization_id = HORG.id
841 AND FCT.time_id = TIME.id
842 AND TIME.id IS NOT NULL
843 AND FCT.period_type_id = TIME.period_type
844 AND FCT.calendar_type = TIME.calendar_type
845 AND FCT.project_class_id = CLS.id
846 AND FCT.WORK_TYPE_ID = WT.ID
847 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
848 UNION ALL
849 SELECT /*+ ORDERED */
850 TIME.name time_id
851 , TIME.order_by_id time_key
852 , 0 revenue
853 , 0 cost
854 , null py_revenue
855 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
856 FROM
857 pji_pmv_time_dim_tmp TIME
858 , pji_pmv_orgz_dim_tmp HORG
859 , pji_pmv_cls_dim_tmp CLS
860 , pji_pmv_wt_dim_tmp WT
861 , PJI_FP_CLSO_ET_WT_F_MV FCT
862 , pji_pmv_org_dim_tmp HOU
863 WHERE
864 FCT.project_org_id = HOU.id
865 AND FCT.project_organization_id = HORG.id
866 AND FCT.time_id = TIME.prior_id
867 AND TIME.prior_id IS NOT NULL
868 AND FCT.period_type_id = TIME.period_type
869 AND FCT.calendar_type = TIME.calendar_type
870 AND FCT.project_class_id = CLS.id
871 AND FCT.WORK_TYPE_ID = WT.ID
872 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
873 UNION ALL
874 SELECT
875 name time_id
876 , order_by_id time_key
877 , 0 revenue
878 , 0 cost
879 , 0 py_revenue
880 , 0 py_cost
881 FROM pji_pmv_time_dim_tmp
882 WHERE name <> '-1')
883 GROUP BY
884 TIME_KEY
885 , TIME_ID ORDER BY TIME_KEY ASC;
886
887 /*
888 ** Project classification and Expenditure or Revenue Type and Work Type Processing ----------------+
889 */
890
891 /* ----------------------------------- Case 8 truth table ------------------------------------- */
892
893 ELSE
894 SELECT PJI_REP_PP5(
895 TIME_ID
896 , SUM( REVENUE )
897 , SUM( COST )
898 , SUM( REVENUE-COST )
899 , SUM( PY_REVENUE )
900 , SUM( PY_COST )
901 , SUM( PY_REVENUE-PY_COST )
902 , 0, 0, 0, 0, 0)
903 BULK COLLECT INTO l_Total_Prj_Profitablity_Tab
904 FROM (
905 SELECT /*+ ORDERED */
906 TIME.name time_id
907 , TIME.order_by_id time_key
908 , null revenue
909 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) cost
910 , 0 py_revenue
911 , 0 py_cost
912 FROM
913 pji_pmv_time_dim_tmp TIME
914 , pji_pmv_orgz_dim_tmp HORG
915 , pji_pmv_cls_dim_tmp CLS
916 , pji_pmv_et_rt_dim_tmp ET
917 , pji_pmv_wt_dim_tmp WT
918 , PJI_FP_CLSO_ET_WT_F_MV FCT
919 , pji_pmv_org_dim_tmp HOU
920 WHERE
921 FCT.project_org_id = HOU.id
922 AND FCT.project_organization_id = HORG.id
923 AND FCT.time_id = TIME.id
924 AND TIME.id IS NOT NULL
925 AND FCT.period_type_id = TIME.period_type
926 AND FCT.calendar_type = TIME.calendar_type
927 AND FCT.project_class_id = CLS.id
928 AND FCT.EXP_EVT_TYPE_ID = ET.ID
929 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
930 AND FCT.WORK_TYPE_ID = WT.ID
931 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
932 UNION ALL
933 SELECT /*+ ORDERED */
934 TIME.name time_id
935 , TIME.order_by_id time_key
936 , 0 revenue
937 , 0 cost
938 , null py_revenue
939 , DECODE(l_Report_Cost_Type,'RC', raw_cost,'BC', burdened_cost) py_cost
940 FROM
941 pji_pmv_time_dim_tmp TIME
942 , pji_pmv_orgz_dim_tmp HORG
943 , pji_pmv_cls_dim_tmp CLS
944 , pji_pmv_et_rt_dim_tmp ET
945 , pji_pmv_wt_dim_tmp WT
946 , PJI_FP_CLSO_ET_WT_F_MV FCT
947 , pji_pmv_org_dim_tmp HOU
948 WHERE
949 FCT.project_org_id = HOU.id
950 AND FCT.project_organization_id = HORG.id
951 AND FCT.time_id = TIME.prior_id
952 AND TIME.prior_id IS NOT NULL
953 AND FCT.period_type_id = TIME.period_type
954 AND FCT.calendar_type = TIME.calendar_type
955 AND FCT.project_class_id = CLS.id
956 AND FCT.EXP_EVT_TYPE_ID = ET.ID
957 AND ET.record_type = decode(l_Convert_Expenditure_Type,'Y','ET',l_Convert_Event_Revenue_Type,'Y','RT')
958 AND FCT.WORK_TYPE_ID = WT.ID
959 AND bitand(fct.curr_record_type_id, l_curr_record_type_id) = l_curr_record_type_id
960 UNION ALL
961 SELECT
962 name time_id
963 , order_by_id time_key
964 , 0 revenue
965 , 0 cost
966 , 0 py_revenue
967 , 0 py_cost
968 FROM pji_pmv_time_dim_tmp
969 WHERE name <> '-1')
970 GROUP BY
971 TIME_KEY
972 , TIME_ID ORDER BY TIME_KEY ASC;
973
974
975 END IF;
976
977 FOR i in 1..l_Total_Prj_Profitablity_Tab.COUNT
978 LOOP
979 IF p_Report_Type = 'FISCAL' THEN
980 l_Revenue:=l_Revenue+l_Total_Prj_Profitablity_Tab(i).REVENUE;
981 l_Cost:=l_Cost+l_Total_Prj_Profitablity_Tab(i).COST;
982 l_Margin:=l_Margin+l_Total_Prj_Profitablity_Tab(i).MARGIN;
983 l_PY_Revenue:=l_PY_Revenue+l_Total_Prj_Profitablity_Tab(i).PY_REVENUE;
984 l_PY_Cost:=l_PY_Cost+l_Total_Prj_Profitablity_Tab(i).PY_COST;
985 l_PY_Margin:=l_PY_Margin+l_Total_Prj_Profitablity_Tab(i).PY_MARGIN;
986
987 l_Total_Prj_Profitablity_Tab(i).REVENUE:=l_Revenue;
988 l_Total_Prj_Profitablity_Tab(i).COST:=l_Cost;
989 l_Total_Prj_Profitablity_Tab(i).MARGIN:=l_Margin;
990 l_Total_Prj_Profitablity_Tab(i).PY_REVENUE:=l_PY_Revenue;
991 l_Total_Prj_Profitablity_Tab(i).PY_COST:=l_PY_Cost;
992 l_Total_Prj_Profitablity_Tab(i).PY_MARGIN:=l_PY_Margin;
993 END IF;
994
995 IF NVL(l_Total_Prj_Profitablity_Tab(i).REVENUE,0) <> 0 THEN
996 l_Total_Prj_Profitablity_Tab(i).MARGIN_PERCENT := 100*
997 (l_Total_Prj_Profitablity_Tab(i).MARGIN/l_Total_Prj_Profitablity_Tab(i).REVENUE);
998 ELSE
999 l_Total_Prj_Profitablity_Tab(i).MARGIN_PERCENT := NULL;
1000 END IF;
1001 IF NVL(l_Total_Prj_Profitablity_Tab(i).PY_REVENUE,0) <> 0 THEN
1002 l_Total_Prj_Profitablity_Tab(i).PY_MARGIN_PERCENT := 100*
1003 (l_Total_Prj_Profitablity_Tab(i).PY_MARGIN/l_Total_Prj_Profitablity_Tab(i).PY_REVENUE);
1004 l_Total_Prj_Profitablity_Tab(i).REV_CHANGE_PERCENT := 100*
1005 ((l_Total_Prj_Profitablity_Tab(i).REVENUE-l_Total_Prj_Profitablity_Tab(i).PY_REVENUE)
1006 /ABS(l_Total_Prj_Profitablity_Tab(i).PY_REVENUE));
1007 ELSE
1008 l_Total_Prj_Profitablity_Tab(i).PY_MARGIN_PERCENT := NULL;
1009 l_Total_Prj_Profitablity_Tab(i).REV_CHANGE_PERCENT := NULL;
1010 END IF;
1011 IF NVL(l_Total_Prj_Profitablity_Tab(i).PY_COST,0) <> 0 THEN
1012 l_Total_Prj_Profitablity_Tab(i).CST_CHANGE_PERCENT := 100*
1013 ((l_Total_Prj_Profitablity_Tab(i).COST-l_Total_Prj_Profitablity_Tab(i).PY_COST)
1014 /ABS(l_Total_Prj_Profitablity_Tab(i).PY_COST));
1015 ELSE
1016 l_Total_Prj_Profitablity_Tab(i).CST_CHANGE_PERCENT := NULL;
1017 END IF;
1018
1019 l_Total_Prj_Profitablity_Tab(i).MAR_CHANGE_PERCENT :=
1020 l_Total_Prj_Profitablity_Tab(i).MARGIN_PERCENT-l_Total_Prj_Profitablity_Tab(i).PY_MARGIN_PERCENT;
1021
1022 /*
1023 ** The below portion of the code is commented
1024 ** because the trend reports donot have totals.
1025 */
1026 /*
1027 l_Total_Revenue := l_Total_Revenue + NVL(l_Total_Prj_Profitablity_Tab(i).REVENUE, 0);
1028 l_Total_Cost := l_Total_Cost + NVL(l_Total_Prj_Profitablity_Tab(i).COST, 0);
1029 l_Total_Margin := l_Total_Margin + NVL(l_Total_Prj_Profitablity_Tab(i).MARGIN, 0);
1030 l_Total_PY_Revenue := l_Total_PY_Revenue + NVL(l_Total_Prj_Profitablity_Tab(i).PY_REVENUE, 0);
1031 l_Total_PY_Cost := l_Total_PY_Cost + NVL(l_Total_Prj_Profitablity_Tab(i).PY_COST, 0);
1032 l_Total_PY_Margin := l_Total_PY_Margin + NVL(l_Total_Prj_Profitablity_Tab(i).PY_MARGIN, 0);
1033 */
1034 END LOOP;
1035
1036 /*
1037 ** Return the bulk collected table back to pmv.
1038 */
1039
1040 COMMIT;
1041 RETURN l_Total_Prj_Profitablity_Tab;
1042
1043 END PLSQLDriver_PJI_REP_PP5;
1044
1045
1046
1047 END PJI_PMV_PROFITABILITY_TREND;