[Home] [Help]
PACKAGE BODY: APPS.PA_REP_UTIL_SCREEN
Source
1 PACKAGE BODY PA_REP_UTIL_SCREEN AS
2 /* $Header: PARRSCRB.pls 120.3 2007/02/06 09:56:44 dthakker ship $ */
3
4
5 /*
6 * Procedures.
7 */
8
9 PROCEDURE poplt_screen_tmp_table(
10 p_Organization_ID IN NUMBER
11 , p_Manager_ID IN NUMBER
12 , p_Period_Type IN VARCHAR2
13 , p_Period_Year IN NUMBER
14 , p_Period_Quarter IN NUMBER
15 , p_Period_Name IN VARCHAR2
16 , p_Global_Week_End_Date IN DATE
17 , p_Assignment_Status IN VARCHAR2
18 , p_Show_Percentage_By IN VARCHAR2
19 , p_Utilization_Method IN VARCHAR2
20 , p_Utilization_Category_Id IN NUMBER
21 , p_Calling_Mode IN VARCHAR2
22 )
23 IS
24 BEGIN
25
26 delete from PA_REP_UTIL_SCREEN_TMP;
27
28
29 /*
30 * BEGINNING of Case 1 for U2
31 * GE view
32 */
33 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe) THEN
34
35 INSERT INTO PA_REP_UTIL_SCREEN_TMP (
36 Organization_id
37 , Person_id
38 , Resource_id
39 , Resource_Name
40 , Resource_Type
41 , Resource_Type_Code
42 , Calling_Mode
43 , Job_Level
44 , Actuals_Capacity
45 , Actuals_hours
46 , Actuals_Weighted_hours
47 , Actuals_Weighted_hours_P
48 , Actuals_Cap_OR_Tot_Hrs
49 , Forecast_Capacity
50 , Forecast_hours
51 , Forecast_Weighted_hours
52 , Forecast_Weighted_hours_P
53 , Forecast_Cap_OR_Tot_Hrs
54 )
55 SELECT
56 DECODE(p_calling_mode
57 , 'ORGMGR', paobj.expenditure_organization_id
58 , 'RESMGR', NULL
59 ) AS ORGANIZATION_ID
60 , resdnorm.person_id AS PERSON_ID
61 , resdnorm.resource_id AS RESOURCE_ID
62 , max(resdnorm.resource_name) AS RESOURCE_NAME
63 , max(lkup.meaning) AS RESOURCE_TYPE
64 , max(resdnorm.resource_type) AS RESOURCE_TYPE_CODE
65 , p_Calling_Mode AS CALLING_MODE
66 , max(resdnorm.resource_job_level) AS JOB_LEVEL
67 /*
68 * Field below is for ACTUALS_CAPACITY
69 */
70 ,DECODE( p_Utilization_Category_ID, 0,(
71 DECODE(
72 sign(
73 sum(
74 DecodE(paobj.balance_type_code
75 , PA_REP_UTIL_GLOB.GetBalTypeActuals
76 , Decode(summbal.amount_type_id
77 , 9, NVL(summbal.period_balance,0)
78 , 0)
79 , 0))
80 -sum(
81 DecodE(paobj.balance_type_code
82 , PA_REP_UTIL_GLOB.GetBalTypeActuals
83 , Decode(summbal.amount_type_id
84 , 10, NVL(summbal.period_balance,0)
85 , 0)
86 , 0)))
87 , 1,
88 (sum(
89 DecodE(paobj.balance_type_code
90 , PA_REP_UTIL_GLOB.GetBalTypeActuals
91 , Decode(summbal.amount_type_id
92 , 9, NVL(summbal.period_balance,0)
93 , 0)
94 , 0))
95 -sum(
96 DecodE(paobj.balance_type_code
97 , PA_REP_UTIL_GLOB.GetBalTypeActuals
98 , Decode(summbal.amount_type_id
99 , 10, NVL(summbal.period_balance,0)
100 , 0)
101 , 0)))
102 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
103 PA_REP_UTIL_GLOB.GetOrgId
104 , PA_REP_UTIL_GLOB.GetBalTypeActuals
105 , max(paobj.person_id)
106 , max(summbal.version_id)
107 , max(summbal.period_type)
108 , max(summbal.period_set_name)
109 , max(summbal.period_name)
110 , max(summbal.global_exp_period_end_date)
111 , 1
112 , 9
113 , 10
114 , p_Show_Percentage_By
115 , p_organization_id)) AS ACTUALS_CAPACITY
116 /*
117 * Field below is for ACTUALS_HOURS
118 */
119 , sum(
120 DECODE(paobj.balance_type_code
121 , PA_REP_UTIL_GLOB.GetBalTypeActuals
122 , Decode(summbal.amount_type_id
123 , 1, NVL(summbal.period_balance,0)
124 , 0)
125 , 0)) AS ACTUALS_HOURS
126 /*
127 * Field below is for ACTUALS_WEIGHTED_HOURS
128 */
129 , sum(
130 DECODE(paobj.balance_type_code
131 , PA_REP_UTIL_GLOB.GetBalTypeActuals
132 , Decode(p_utilization_method
133 , 'ORGANIZATION'
134 , decode(summbal.amount_type_id
135 , 2, NVL(summbal.period_balance,0)
136 , 0)
137 , 'RESOURCE'
138 , decode(summbal.amount_type_id
139 , 3, NVL(summbal.period_balance,0)
140 , 0)
141 )
142 , 0)) AS ACTUALS_WEIGHTED_HOURS
143 /*
144 * Field below is for ACTUALS_WEIGHTED_HOURS_P
145 */
146 ,ROUND(NVL(sum(
147 DECODE(paobj.balance_type_code
148 , PA_REP_UTIL_GLOB.GetBalTypeActuals
149 , Decode(p_utilization_method
150 , 'ORGANIZATION'
151 , decode(summbal.amount_type_id
152 , 2, NVL(summbal.period_balance,0)
153 , 0)
154 , 'RESOURCE'
155 , decode(summbal.amount_type_id
156 , 3, NVL(summbal.period_balance,0)
157 , 0)
158 )
159 , 0))*100/
160 DECODE(p_Utilization_Category_Id
161 ,0 , DECODE(p_Show_Percentage_By
162 , 'CAPACITY'
163 , DECODE(
164 sign(
165 sum(
166 DecodE(paobj.balance_type_code
167 , PA_REP_UTIL_GLOB.GetBalTypeActuals
168 , Decode(summbal.amount_type_id
169 , 9, NVL(summbal.period_balance,0)
170 , 0)
171 , 0))
172 -sum(
173 DecodE(paobj.balance_type_code
174 , PA_REP_UTIL_GLOB.GetBalTypeActuals
175 , Decode(summbal.amount_type_id
176 , 10, NVL(summbal.period_balance,0)
177 , 0)
178 , 0)))
179 , 1,
180 (sum(
181 DecodE(paobj.balance_type_code
182 , PA_REP_UTIL_GLOB.GetBalTypeActuals
183 , Decode(summbal.amount_type_id
184 , 9, NVL(summbal.period_balance,0)
185 , 0)
186 , 0))
187 -sum(
188 DecodE(paobj.balance_type_code
189 , PA_REP_UTIL_GLOB.GetBalTypeActuals
190 , Decode(summbal.amount_type_id
191 , 10, NVL(summbal.period_balance,0)
192 , 0)
193 , 0)))
194 ,1)
195 , 'TOTAL_WORKED_HOURS'
196 , DECODE(
197 sign(
198 sum(
199 DecodE(paobj.balance_type_code
200 , PA_REP_UTIL_GLOB.GetBalTypeActuals
201 , Decode(summbal.amount_type_id
202 , 1, NVL(summbal.period_balance,0)
203 , 0)
204 , 0)))
205 , 1, sum(
206 DecodE(paobj.balance_type_code
207 , PA_REP_UTIL_GLOB.GetBalTypeActuals
208 , Decode(summbal.amount_type_id
209 , 1, NVL(summbal.period_balance,0)
210 , 0)
211 , 0))
212 , 1)
213 )
214 , PA_REP_UTIL_SCREEN.calculate_capacity(
215 PA_REP_UTIL_GLOB.GetOrgId
216 , PA_REP_UTIL_GLOB.GetBalTypeActuals
217 , max(paobj.person_id)
218 , max(summbal.version_id)
219 , max(summbal.period_type)
220 , max(summbal.period_set_name)
221 , max(summbal.period_name)
222 , max(summbal.global_exp_period_end_date)
223 , 1
224 , 9
225 , 10
226 , p_Show_Percentage_By)
227 )
228 , -9999) -- finished NVL
229 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding and concatenation
230 /*
231 * Field below is for ACTUALS_CAP_OR_TOT_HRS
232 */
233 , DECODE(p_Utilization_Category_Id
234 ,0 , DECODE(p_Show_Percentage_By
235 , 'CAPACITY'
236 , DECODE(
237 sign(
238 sum(
239 DecodE(paobj.balance_type_code
240 , PA_REP_UTIL_GLOB.GetBalTypeActuals
241 , Decode(summbal.amount_type_id
242 , 9, NVL(summbal.period_balance,0)
243 , 0)
244 , 0))
245 -sum(
246 DecodE(paobj.balance_type_code
247 , PA_REP_UTIL_GLOB.GetBalTypeActuals
248 , Decode(summbal.amount_type_id
249 , 10, NVL(summbal.period_balance,0)
250 , 0)
251 , 0)))
252 , 1,
253 (sum(
254 DecodE(paobj.balance_type_code
255 , PA_REP_UTIL_GLOB.GetBalTypeActuals
256 , Decode(summbal.amount_type_id
257 , 9, NVL(summbal.period_balance,0)
258 , 0)
259 , 0))
260 -sum(
261 DecodE(paobj.balance_type_code
262 , PA_REP_UTIL_GLOB.GetBalTypeActuals
263 , Decode(summbal.amount_type_id
264 , 10, NVL(summbal.period_balance,0)
265 , 0)
266 , 0)))
267 ,1)
268 , 'TOTAL_WORKED_HOURS'
269 , DECODE(
270 sign(
271 sum(
272 DecodE(paobj.balance_type_code
273 , PA_REP_UTIL_GLOB.GetBalTypeActuals
274 , Decode(summbal.amount_type_id
275 , 1, NVL(summbal.period_balance,0)
276 , 0)
277 , 0)))
278 , 1, sum(
279 DecodE(paobj.balance_type_code
280 , PA_REP_UTIL_GLOB.GetBalTypeActuals
281 , Decode(summbal.amount_type_id
282 , 1, NVL(summbal.period_balance,0)
283 , 0)
284 , 0))
285 , 1)
286 )
287 , PA_REP_UTIL_SCREEN.calculate_capacity(
288 PA_REP_UTIL_GLOB.GetOrgId
289 , PA_REP_UTIL_GLOB.GetBalTypeActuals
290 , max(paobj.person_id)
291 , max(summbal.version_id)
292 , max(summbal.period_type)
293 , max(summbal.period_set_name)
294 , max(summbal.period_name)
295 , max(summbal.global_exp_period_end_date)
296 , 1
297 , 9
298 , 10
299 , p_Show_Percentage_By)
300 ) AS ACTUALS_CAP_OR_TOT_HRS
301 /*
302 * Field below is for FORECAST_CAPACITY
303 */
304 ,DECODE( p_Utilization_Category_ID, 0,(
305 DECODE(
306 sign(
307 sum(
308 DecodE(paobj.balance_type_code
309 , PA_REP_UTIL_GLOB.GetBalTypeForecast
310 , Decode(summbal.amount_type_id
311 , 9, NVL(summbal.period_balance,0)
312 , 0)
313 , 0))
314 -sum(
315 DecodE(paobj.balance_type_code
316 , PA_REP_UTIL_GLOB.GetBalTypeForecast
317 , Decode(summbal.amount_type_id
318 , 10, NVL(summbal.period_balance,0)
319 , 0)
320 , 0)))
321 , 1,
322 (sum(
323 DecodE(paobj.balance_type_code
324 , PA_REP_UTIL_GLOB.GetBalTypeForecast
325 , Decode(summbal.amount_type_id
326 , 9, NVL(summbal.period_balance,0)
327 , 0)
328 , 0))
329 -sum(
330 DecodE(paobj.balance_type_code
331 , PA_REP_UTIL_GLOB.GetBalTypeForecast
332 , Decode(summbal.amount_type_id
333 , 10, NVL(summbal.period_balance,0)
334 , 0)
335 , 0)))
336 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
337 PA_REP_UTIL_GLOB.GetOrgId
338 , PA_REP_UTIL_GLOB.GetBalTypeForecast
339 , max(paobj.person_id)
340 , max(summbal.version_id)
341 , max(summbal.period_type)
342 , max(summbal.period_set_name)
343 , max(summbal.period_name)
344 , max(summbal.global_exp_period_end_date)
345 , 1
346 , 9
347 , 10
348 , p_Show_Percentage_By
349 , p_organization_id)) AS FORECAST_CAPACITY
350 /*
351 * Field below is for FORECAST_HOURS
352 */
353 , sum(
354 DECODE(paobj.balance_type_code
355 , PA_REP_UTIL_GLOB.GetBalTypeForecast
356 , Decode(p_Assignment_Status
357 , 'ALL'
358 , decode(summbal.amount_type_id
359 , 1, NVL(summbal.period_balance,0)
360 , 0)
361 , 'PROVISIONAL'
362 , decode(summbal.amount_type_id
363 , 4, NVL(summbal.period_balance,0)
364 , 0)
365 , 'CONFIRMED'
366 , decode(summbal.amount_type_id
367 , 1, NVL(summbal.period_balance,0)
368 , 0)
369 , 0)
370 , 0))
371 -sum(
372 DECODE(paobj.balance_type_code
373 , PA_REP_UTIL_GLOB.GetBalTypeForecast
374 , Decode(p_Assignment_Status
375 , 'CONFIRMED'
376 , decode(summbal.amount_type_id
377 , 4, NVL(summbal.period_balance,0)
378 , 0)
379 , 0)
380 , 0)) AS FORECAST_HOURS
381 /*
382 * Field below is for FORECAST_WEIGHTED_HOURS
383 */
384 , (sum(
385 DECODE(paobj.balance_type_code
386 , PA_REP_UTIL_GLOB.GetBalTypeForecast
387 , DecodE(p_Assignment_Status
388 , 'ALL'
389 , Decode(p_Utilization_Method
390 , 'ORGANIZATION'
391 , decode(summbal.amount_type_id
392 , 2, NVL(summbal.period_balance,0)
393 , 0)
394 , 'RESOURCE'
395 , decode(summbal.amount_type_id
396 , 3, NVL(summbal.period_balance,0)
397 , 0))
398 , 'PROVISIONAL'
399 , Decode(p_Utilization_Method
400 , 'ORGANIZATION'
401 , decode(summbal.amount_type_id
402 , 5, NVL(summbal.period_balance,0)
403 , 0)
404 , 'RESOURCE'
405 , decode(summbal.amount_type_id
406 , 6, NVL(summbal.period_balance,0)
407 , 0))
408 , 'CONFIRMED'
409 , Decode(p_Utilization_Method
410 , 'ORGANIZATION'
411 , decode(summbal.amount_type_id
412 , 2, NVL(summbal.period_balance,0)
413 , 0)
414 , 'RESOURCE'
415 , decode(summbal.amount_type_id
416 , 3, NVL(summbal.period_balance,0)
417 , 0))
418 )
419 , 0))
420 -sum(
421 DECODE(paobj.balance_type_code
422 , PA_REP_UTIL_GLOB.GetBalTypeForecast
423 , Decode(p_Assignment_Status
424 , 'CONFIRMED'
425 , Decode(p_Utilization_Method
426 , 'ORGANIZATION'
427 , decode(summbal.amount_type_id
428 , 5, NVL(summbal.period_balance,0)
429 , 0)
430 , 'RESOURCE'
431 , decode(summbal.amount_type_id
432 , 6, NVL(summbal.period_balance,0)
433 , 0))
434 , 0)
435 , 0))) AS FORECAST_WEIGHTED_HOURS
436 /*
437 * Field below is for FORECAST_WEIGHTED_HOURS_P
438 */
439 ,ROUND(NVL(
440 (sum(
441 DECODE(paobj.balance_type_code
442 , PA_REP_UTIL_GLOB.GetBalTypeForecast
443 , DecodE(p_Assignment_Status
444 , 'ALL'
445 , Decode(p_Utilization_Method
446 , 'ORGANIZATION'
447 , decode(summbal.amount_type_id
448 , 2, NVL(summbal.period_balance,0)
449 , 0)
450 , 'RESOURCE'
451 , decode(summbal.amount_type_id
452 , 3, NVL(summbal.period_balance,0)
453 , 0))
454 , 'PROVISIONAL'
455 , Decode(p_Utilization_Method
456 , 'ORGANIZATION'
457 , decode(summbal.amount_type_id
458 , 5, NVL(summbal.period_balance,0)
459 , 0)
460 , 'RESOURCE'
461 , decode(summbal.amount_type_id
462 , 6, NVL(summbal.period_balance,0)
463 , 0))
464 , 'CONFIRMED'
465 , Decode(p_Utilization_Method
466 , 'ORGANIZATION'
467 , decode(summbal.amount_type_id
468 , 2, NVL(summbal.period_balance,0)
469 , 0)
470 , 'RESOURCE'
471 , decode(summbal.amount_type_id
472 , 3, NVL(summbal.period_balance,0)
473 , 0))
474 )
475 , 0))
476 -sum(
477 DECODE(paobj.balance_type_code
478 , PA_REP_UTIL_GLOB.GetBalTypeForecast
479 , Decode(p_Assignment_Status
480 , 'CONFIRMED'
481 , Decode(p_Utilization_Method
482 , 'ORGANIZATION'
483 , decode(summbal.amount_type_id
484 , 5, NVL(summbal.period_balance,0)
485 , 0)
486 , 'RESOURCE'
487 , decode(summbal.amount_type_id
488 , 6, NVL(summbal.period_balance,0)
489 , 0))
490 , 0)
491 , 0)))
492 *100/
493 DECODE(p_Utilization_Category_Id
494 ,0 , DECODE(p_Show_Percentage_By
495 , 'CAPACITY'
496 , DECODE(
497 sign(
498 sum(
499 DecodE(paobj.balance_type_code
500 , PA_REP_UTIL_GLOB.GetBalTypeForecast
501 , Decode(summbal.amount_type_id
502 , 9, NVL(summbal.period_balance,0)
503 , 0)
504 , 0))
505 -sum(
506 DecodE(paobj.balance_type_code
507 , PA_REP_UTIL_GLOB.GetBalTypeForecast
508 , Decode(summbal.amount_type_id
509 , 10, NVL(summbal.period_balance,0)
510 , 0)
511 , 0)))
512 , 1,
513 (sum(
514 DecodE(paobj.balance_type_code
515 , PA_REP_UTIL_GLOB.GetBalTypeForecast
516 , Decode(summbal.amount_type_id
517 , 9, NVL(summbal.period_balance,0)
518 , 0)
519 , 0))
520 -sum(
521 DecodE(paobj.balance_type_code
522 , PA_REP_UTIL_GLOB.GetBalTypeForecast
523 , Decode(summbal.amount_type_id
524 , 10, NVL(summbal.period_balance,0)
525 , 0)
526 , 0)))
527 ,1)
528 , 'TOTAL_WORKED_HOURS'
529 , DECODE(
530 sign(
531 sum(
532 DecodE(paobj.balance_type_code
533 , PA_REP_UTIL_GLOB.GetBalTypeForecast
534 , Decode(summbal.amount_type_id
535 , 1, NVL(summbal.period_balance,0)
536 , 0)
537 , 0)))
538 , 1, sum(
539 DecodE(paobj.balance_type_code
540 , PA_REP_UTIL_GLOB.GetBalTypeForecast
541 , Decode(summbal.amount_type_id
542 , 1, NVL(summbal.period_balance,0)
543 , 0)
544 , 0))
545 , 1)
546 )
547 , PA_REP_UTIL_SCREEN.calculate_capacity(
548 PA_REP_UTIL_GLOB.GetOrgId
549 , PA_REP_UTIL_GLOB.GetBalTypeForecast
550 , max(paobj.person_id)
551 , max(summbal.version_id)
552 , max(summbal.period_type)
553 , max(summbal.period_set_name)
554 , max(summbal.period_name)
555 , max(summbal.global_exp_period_end_date)
556 , 1
557 , 9
558 , 10
559 , p_Show_Percentage_By)
560 )
561 , -9999) -- finished NVL
562 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding
563 /*
564 * Field below is for FORECAST_CAP_OR_TOT_HRS
565 */
566 , DECODE(p_Utilization_Category_Id
567 ,0 , DECODE(p_Show_Percentage_By
568 , 'CAPACITY'
569 , DECODE(
570 sign(
571 sum(
572 DecodE(paobj.balance_type_code
573 , PA_REP_UTIL_GLOB.GetBalTypeForecast
574 , Decode(summbal.amount_type_id
575 , 9, NVL(summbal.period_balance,0)
576 , 0)
577 , 0))
578 -sum(
579 DecodE(paobj.balance_type_code
580 , PA_REP_UTIL_GLOB.GetBalTypeForecast
581 , Decode(summbal.amount_type_id
582 , 10, NVL(summbal.period_balance,0)
583 , 0)
584 , 0)))
585 , 1,
586 (sum(
587 DecodE(paobj.balance_type_code
588 , PA_REP_UTIL_GLOB.GetBalTypeForecast
589 , Decode(summbal.amount_type_id
590 , 9, NVL(summbal.period_balance,0)
591 , 0)
592 , 0))
593 -sum(
594 DecodE(paobj.balance_type_code
595 , PA_REP_UTIL_GLOB.GetBalTypeForecast
596 , Decode(summbal.amount_type_id
597 , 10, NVL(summbal.period_balance,0)
598 , 0)
599 , 0)))
600 ,1)
601 , 'TOTAL_WORKED_HOURS'
602 , DECODE(
603 sign(
604 sum(
605 DecodE(paobj.balance_type_code
606 , PA_REP_UTIL_GLOB.GetBalTypeForecast
607 , Decode(summbal.amount_type_id
608 , 1, NVL(summbal.period_balance,0)
609 , 0)
610 , 0)))
611 , 1, sum(
612 DecodE(paobj.balance_type_code
613 , PA_REP_UTIL_GLOB.GetBalTypeForecast
614 , Decode(summbal.amount_type_id
615 , 1, NVL(summbal.period_balance,0)
616 , 0)
617 , 0))
618 , 1)
619 )
620 , PA_REP_UTIL_SCREEN.calculate_capacity(
621 PA_REP_UTIL_GLOB.GetOrgId
622 , PA_REP_UTIL_GLOB.GetBalTypeForecast
623 , max(paobj.person_id)
624 , max(summbal.version_id)
625 , max(summbal.period_type)
626 , max(summbal.period_set_name)
627 , max(summbal.period_name)
628 , max(summbal.global_exp_period_end_date)
629 , 1
630 , 9
631 , 10
632 , p_Show_Percentage_By)
633 ) AS FORECAST_CAP_OR_TOT_HRS
634 from
635 PA_Summ_Balances summbal
636 , PA_Objects paobj
637 , pa_resources_denorm resdnorm
638 , pa_lookups lkup
639 where
640 lkup.lookup_type = 'PERSON_TYPE'
641 AND lkup.lookup_code = 'EMPLOYEE'
642 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
643 AND (
644 (summbal.global_exp_period_end_date-6 between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,summbal.global_exp_period_end_date-6))+0.99999
645 and p_calling_mode = 'ORGMGR')
646 OR
647 /* Bug 2003821: start */
648 (
649 (
650 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,summbal.global_exp_period_end_date-6))+0.99999
651 and (summbal.global_exp_period_end_date-6) <= sysdate) /* Added for Bug 2325539 */
652 OR
653 (summbal.global_exp_period_end_date-6 between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,summbal.global_exp_period_end_date-6))+0.99999
654 and (summbal.global_exp_period_end_date-6) > sysdate)
655 )
656 and p_calling_mode = 'RESMGR'
657 )
658 )
659 /* Bug 2003821: end */
660 AND summbal.object_id = paobj.object_id
661 AND summbal.version_id = -1
662 AND summbal.period_type = p_Period_Type
663 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetDummy
664 AND summbal.period_name = PA_REP_UTIL_GLOB.GetDummy
665 AND summbal.global_exp_period_end_date = p_Global_Week_End_Date
666 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
667 , 2 /* G_RES_WTDHRS_ORG_C */
668 , 3 /* G_RES_WTDHRS_PEOPLE_C */
669 , 4 /* G_RES_PRVHRS_C */
670 , 5 /* G_RES_PRVWTDHRS_ORG_C */
671 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
672 , 9 /* G_RES_CAP_C */
673 ,10 /* G_RES_REDUCEDCAP_C */
674 )
675 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
676 , 0 , PA_REP_UTIL_GLOB.GetObjectTypeRes
677 , Decode(p_Utilization_Method
678 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
679 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
680 AND paobj.object_type_code = summbal.object_type_code
681 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
682 AND paobj.project_org_id = -1
683 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
684 AND paobj.project_organization_id = -1
685 AND paobj.project_id = -1
686 AND paobj.task_id = -1
687 AND paobj.person_id = resdnorm.person_id
688 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
689 AND paobj.assignment_id = -1
690 AND paobj.work_type_id = -1
691 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
692 , 0, -1
693 , Decode(p_Utilization_Method
694 , 'ORGANIZATION', p_Utilization_Category_Id
695 , 'RESOURCE', -1))
696 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
697 , 0, -1
698 , Decode(p_Utilization_Method
699 , 'ORGANIZATION', -1
700 , 'RESOURCE', p_Utilization_Category_Id))
701 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
702 group by
703 DECODE(p_calling_mode
704 , 'ORGMGR', paobj.expenditure_organization_id
705 , 'RESMGR', NULL
706 )
707 , resdnorm.person_id
708 , resdnorm.resource_id
709 ;
710 END IF;
711 /*
712 * END of Case 1 for U2
713 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe
714 */
715 /*
716 * BEGINNING of Case 2 for U2
717 * GL or PA view
718 */
719 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGl OR
720 p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypePa) THEN
721
722 INSERT INTO PA_REP_UTIL_SCREEN_TMP (
723 Organization_id
724 , Person_id
725 , Resource_id
726 , Resource_Name
727 , Resource_Type
728 , Resource_Type_Code
729 , Calling_Mode
730 , Job_Level
731 , Actuals_Capacity
732 , Actuals_hours
733 , Actuals_Weighted_hours
734 , Actuals_Weighted_hours_P
735 , Actuals_Cap_OR_Tot_Hrs
736 , Forecast_Capacity
737 , Forecast_hours
738 , Forecast_Weighted_hours
739 , Forecast_Weighted_hours_P
740 , Forecast_Cap_OR_Tot_Hrs
741 )
742 SELECT
743 DECODE(p_calling_mode
744 , 'ORGMGR', paobj.expenditure_organization_id
745 , 'RESMGR', NULL
746 ) AS ORGANIZATION_ID
747 , resdnorm.person_id AS PERSON_ID
748 , resdnorm.resource_id AS RESOURCE_ID
749 , max(resdnorm.resource_name) AS RESOURCE_NAME
750 , max(lkup.meaning) AS RESOURCE_TYPE
751 , max(resdnorm.resource_type) AS RESOURCE_TYPE_CODE
752 , p_Calling_Mode AS CALLING_MODE
753 , max(resdnorm.resource_job_level) AS JOB_LEVEL
754 /*
755 * Field below is for ACTUALS_CAPACITY
756 */
757 ,DECODE( p_Utilization_Category_ID, 0,(
758 DECODE(
759 sign(
760 sum(
761 DecodE(paobj.balance_type_code
762 , PA_REP_UTIL_GLOB.GetBalTypeActuals
763 , Decode(summbal.amount_type_id
764 , 9, NVL(summbal.period_balance,0)
765 , 0)
766 , 0))
767 -sum(
768 DecodE(paobj.balance_type_code
769 , PA_REP_UTIL_GLOB.GetBalTypeActuals
770 , Decode(summbal.amount_type_id
771 , 10, NVL(summbal.period_balance,0)
772 , 0)
773 , 0)))
774 , 1,
775 (sum(
776 DecodE(paobj.balance_type_code
777 , PA_REP_UTIL_GLOB.GetBalTypeActuals
778 , Decode(summbal.amount_type_id
779 , 9, NVL(summbal.period_balance,0)
780 , 0)
781 , 0))
782 -sum(
783 DecodE(paobj.balance_type_code
784 , PA_REP_UTIL_GLOB.GetBalTypeActuals
785 , Decode(summbal.amount_type_id
786 , 10, NVL(summbal.period_balance,0)
787 , 0)
788 , 0)))
789 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
790 PA_REP_UTIL_GLOB.GetOrgId
791 , PA_REP_UTIL_GLOB.GetBalTypeActuals
792 , max(paobj.person_id)
793 , max(summbal.version_id)
794 , max(summbal.period_type)
795 , max(summbal.period_set_name)
796 , max(summbal.period_name)
797 , max(summbal.global_exp_period_end_date)
798 , 1
799 , 9
800 , 10
801 , p_Show_Percentage_By
802 , p_organization_id)) AS ACTUALS_CAPACITY
803
804 /*
805 * Field below is for ACTUALS_HOURS
806 */
807 , sum(
808 DECODE(paobj.balance_type_code
809 , PA_REP_UTIL_GLOB.GetBalTypeActuals
810 , Decode(summbal.amount_type_id
811 , 1, NVL(summbal.period_balance,0)
812 , 0)
813 , 0)) AS ACTUALS_HOURS
814 /*
815 * Field below is for ACTUALS_WEIGHTED_HOURS
816 */
817 , sum(
818 DECODE(paobj.balance_type_code
819 , PA_REP_UTIL_GLOB.GetBalTypeActuals
820 , Decode(p_utilization_method
821 , 'ORGANIZATION'
822 , decode(summbal.amount_type_id
823 , 2, NVL(summbal.period_balance,0)
824 , 0)
825 , 'RESOURCE'
826 , decode(summbal.amount_type_id
827 , 3, NVL(summbal.period_balance,0)
828 , 0)
829 )
830 , 0)) AS ACTUALS_WEIGHTED_HOURS
831 /*
832 * Field below is for ACTUALS_WEIGHTED_HOURS_P
833 */
834 ,ROUND(NVL(sum(
835 DECODE(paobj.balance_type_code
836 , PA_REP_UTIL_GLOB.GetBalTypeActuals
837 , Decode(p_utilization_method
838 , 'ORGANIZATION'
839 , decode(summbal.amount_type_id
840 , 2, NVL(summbal.period_balance,0)
841 , 0)
842 , 'RESOURCE'
843 , decode(summbal.amount_type_id
844 , 3, NVL(summbal.period_balance,0)
845 , 0)
846 )
847 , 0))*100/
848 DECODE(p_Utilization_Category_Id
849 ,0 , DECODE(p_Show_Percentage_By
850 , 'CAPACITY'
851 , DECODE(
852 sign(
853 sum(
854 DecodE(paobj.balance_type_code
855 , PA_REP_UTIL_GLOB.GetBalTypeActuals
856 , Decode(summbal.amount_type_id
857 , 9, NVL(summbal.period_balance,0)
858 , 0)
859 , 0))
860 -sum(
861 DecodE(paobj.balance_type_code
862 , PA_REP_UTIL_GLOB.GetBalTypeActuals
863 , Decode(summbal.amount_type_id
864 , 10, NVL(summbal.period_balance,0)
865 , 0)
866 , 0)))
867 , 1,
868 (sum(
869 DecodE(paobj.balance_type_code
870 , PA_REP_UTIL_GLOB.GetBalTypeActuals
871 , Decode(summbal.amount_type_id
872 , 9, NVL(summbal.period_balance,0)
873 , 0)
874 , 0))
875 -sum(
876 DecodE(paobj.balance_type_code
877 , PA_REP_UTIL_GLOB.GetBalTypeActuals
878 , Decode(summbal.amount_type_id
879 , 10, NVL(summbal.period_balance,0)
880 , 0)
881 , 0)))
882 ,1)
883 , 'TOTAL_WORKED_HOURS'
884 , DECODE(
885 sign(
886 sum(
887 DecodE(paobj.balance_type_code
888 , PA_REP_UTIL_GLOB.GetBalTypeActuals
889 , Decode(summbal.amount_type_id
890 , 1, NVL(summbal.period_balance,0)
891 , 0)
892 , 0)))
893 , 1, sum(
894 DecodE(paobj.balance_type_code
895 , PA_REP_UTIL_GLOB.GetBalTypeActuals
896 , Decode(summbal.amount_type_id
897 , 1, NVL(summbal.period_balance,0)
898 , 0)
899 , 0))
900 , 1)
901 )
902 , PA_REP_UTIL_SCREEN.calculate_capacity(
903 PA_REP_UTIL_GLOB.GetOrgId
904 , PA_REP_UTIL_GLOB.GetBalTypeActuals
905 , max(paobj.person_id)
906 , max(summbal.version_id)
907 , max(summbal.period_type)
908 , max(summbal.period_set_name)
909 , max(summbal.period_name)
910 , max(summbal.global_exp_period_end_date)
911 , 1
912 , 9
913 , 10
914 , p_Show_Percentage_By)
915 )
916 , -9999) -- finished NVL
917 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding
918 /*
919 * Field below is for ACTUALS_CAP_OR_TOT_HRS
920 */
921 , DECODE(p_Utilization_Category_Id
922 ,0 , DECODE(p_Show_Percentage_By
923 , 'CAPACITY'
924 , DECODE(
925 sign(
926 sum(
927 DecodE(paobj.balance_type_code
928 , PA_REP_UTIL_GLOB.GetBalTypeActuals
929 , Decode(summbal.amount_type_id
930 , 9, NVL(summbal.period_balance,0)
931 , 0)
932 , 0))
933 -sum(
934 DecodE(paobj.balance_type_code
935 , PA_REP_UTIL_GLOB.GetBalTypeActuals
936 , Decode(summbal.amount_type_id
937 , 10, NVL(summbal.period_balance,0)
938 , 0)
939 , 0)))
940 , 1,
941 (sum(
942 DecodE(paobj.balance_type_code
943 , PA_REP_UTIL_GLOB.GetBalTypeActuals
944 , Decode(summbal.amount_type_id
945 , 9, NVL(summbal.period_balance,0)
946 , 0)
947 , 0))
948 -sum(
949 DecodE(paobj.balance_type_code
950 , PA_REP_UTIL_GLOB.GetBalTypeActuals
951 , Decode(summbal.amount_type_id
952 , 10, NVL(summbal.period_balance,0)
953 , 0)
954 , 0)))
955 ,1)
956 , 'TOTAL_WORKED_HOURS'
957 , DECODE(
958 sign(
959 sum(
960 DecodE(paobj.balance_type_code
961 , PA_REP_UTIL_GLOB.GetBalTypeActuals
962 , Decode(summbal.amount_type_id
963 , 1, NVL(summbal.period_balance,0)
964 , 0)
965 , 0)))
966 , 1, sum(
967 DecodE(paobj.balance_type_code
968 , PA_REP_UTIL_GLOB.GetBalTypeActuals
969 , Decode(summbal.amount_type_id
970 , 1, NVL(summbal.period_balance,0)
971 , 0)
972 , 0))
973 , 1)
974 )
975 , PA_REP_UTIL_SCREEN.calculate_capacity(
976 PA_REP_UTIL_GLOB.GetOrgId
977 , PA_REP_UTIL_GLOB.GetBalTypeActuals
978 , max(paobj.person_id)
979 , max(summbal.version_id)
980 , max(summbal.period_type)
981 , max(summbal.period_set_name)
982 , max(summbal.period_name)
983 , max(summbal.global_exp_period_end_date)
984 , 1
985 , 9
986 , 10
987 , p_Show_Percentage_By)
988 ) AS ACTUALS_CAP_OR_TOT_HRS
989 /*
990 * Field below is for FORECAST_CAPACITY
991 */
992 ,DECODE( p_Utilization_Category_ID, 0,(
993 DECODE(
994 sign(
995 sum(
996 DecodE(paobj.balance_type_code
997 , PA_REP_UTIL_GLOB.GetBalTypeForecast
998 , Decode(summbal.amount_type_id
999 , 9, NVL(summbal.period_balance,0)
1000 , 0)
1001 , 0))
1002 -sum(
1003 DecodE(paobj.balance_type_code
1004 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1005 , Decode(summbal.amount_type_id
1006 , 10, NVL(summbal.period_balance,0)
1007 , 0)
1008 , 0)))
1009 , 1,
1010 (sum(
1011 DecodE(paobj.balance_type_code
1012 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1013 , Decode(summbal.amount_type_id
1014 , 9, NVL(summbal.period_balance,0)
1015 , 0)
1016 , 0))
1017 -sum(
1018 DecodE(paobj.balance_type_code
1019 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1020 , Decode(summbal.amount_type_id
1021 , 10, NVL(summbal.period_balance,0)
1022 , 0)
1023 , 0)))
1024 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1025 PA_REP_UTIL_GLOB.GetOrgId
1026 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1027 , max(paobj.person_id)
1028 , max(summbal.version_id)
1029 , max(summbal.period_type)
1030 , max(summbal.period_set_name)
1031 , max(summbal.period_name)
1032 , max(summbal.global_exp_period_end_date)
1033 , 1
1034 , 9
1035 , 10
1036 , p_Show_Percentage_By
1037 , p_organization_id)) AS FORECAST_CAPACITY
1038 /*
1039 * Field below is for FORECAST_HOURS
1040 */
1041 , sum(
1042 DECODE(paobj.balance_type_code
1043 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1044 , Decode(p_Assignment_Status
1045 , 'ALL'
1046 , decode(summbal.amount_type_id
1047 , 1, NVL(summbal.period_balance,0)
1048 , 0)
1049 , 'PROVISIONAL'
1050 , decode(summbal.amount_type_id
1051 , 4, NVL(summbal.period_balance,0)
1052 , 0)
1053 , 'CONFIRMED'
1054 , decode(summbal.amount_type_id
1055 , 1, NVL(summbal.period_balance,0)
1056 , 0)
1057 , 0)
1058 , 0))
1059 -sum(
1060 DECODE(paobj.balance_type_code
1061 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1062 , Decode(p_Assignment_Status
1063 , 'CONFIRMED'
1064 , decode(summbal.amount_type_id
1065 , 4, NVL(summbal.period_balance,0)
1066 , 0)
1067 , 0)
1068 , 0)) AS FORECAST_HOURS
1069 /*
1070 * Field below is for FORECAST_WEIGHTED_HOURS
1071 */
1072 , (sum(
1073 DECODE(paobj.balance_type_code
1074 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1075 , DecodE(p_Assignment_Status
1076 , 'ALL'
1077 , Decode(p_Utilization_Method
1078 , 'ORGANIZATION'
1079 , decode(summbal.amount_type_id
1080 , 2, NVL(summbal.period_balance,0)
1081 , 0)
1082 , 'RESOURCE'
1083 , decode(summbal.amount_type_id
1084 , 3, NVL(summbal.period_balance,0)
1085 , 0))
1086 , 'PROVISIONAL'
1087 , Decode(p_Utilization_Method
1088 , 'ORGANIZATION'
1089 , decode(summbal.amount_type_id
1090 , 5, NVL(summbal.period_balance,0)
1091 , 0)
1092 , 'RESOURCE'
1093 , decode(summbal.amount_type_id
1094 , 6, NVL(summbal.period_balance,0)
1095 , 0))
1096 , 'CONFIRMED'
1097 , Decode(p_Utilization_Method
1098 , 'ORGANIZATION'
1099 , decode(summbal.amount_type_id
1100 , 2, NVL(summbal.period_balance,0)
1101 , 0)
1102 , 'RESOURCE'
1103 , decode(summbal.amount_type_id
1104 , 3, NVL(summbal.period_balance,0)
1105 , 0))
1106 )
1107 , 0))
1108 -sum(
1109 DECODE(paobj.balance_type_code
1110 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1111 , Decode(p_Assignment_Status
1112 , 'CONFIRMED'
1113 , Decode(p_Utilization_Method
1114 , 'ORGANIZATION'
1115 , decode(summbal.amount_type_id
1116 , 5, NVL(summbal.period_balance,0)
1117 , 0)
1118 , 'RESOURCE'
1119 , decode(summbal.amount_type_id
1120 , 6, NVL(summbal.period_balance,0)
1121 , 0))
1122 , 0)
1123 , 0))) AS FORECAST_WEIGHTED_HOURS
1124 /*
1125 * Field below is for FORECAST_WEIGHTED_HOURS_P
1126 */
1127 ,ROUND(NVL(
1128 (sum(
1129 DECODE(paobj.balance_type_code
1130 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1131 , DecodE(p_Assignment_Status
1132 , 'ALL'
1133 , Decode(p_Utilization_Method
1134 , 'ORGANIZATION'
1135 , decode(summbal.amount_type_id
1136 , 2, NVL(summbal.period_balance,0)
1137 , 0)
1138 , 'RESOURCE'
1139 , decode(summbal.amount_type_id
1140 , 3, NVL(summbal.period_balance,0)
1141 , 0))
1142 , 'PROVISIONAL'
1143 , Decode(p_Utilization_Method
1144 , 'ORGANIZATION'
1145 , decode(summbal.amount_type_id
1146 , 5, NVL(summbal.period_balance,0)
1147 , 0)
1148 , 'RESOURCE'
1149 , decode(summbal.amount_type_id
1150 , 6, NVL(summbal.period_balance,0)
1151 , 0))
1152 , 'CONFIRMED'
1153 , Decode(p_Utilization_Method
1154 , 'ORGANIZATION'
1155 , decode(summbal.amount_type_id
1156 , 2, NVL(summbal.period_balance,0)
1157 , 0)
1158 , 'RESOURCE'
1159 , decode(summbal.amount_type_id
1160 , 3, NVL(summbal.period_balance,0)
1161 , 0))
1162 )
1163 , 0))
1164 -sum(
1165 DECODE(paobj.balance_type_code
1166 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1167 , Decode(p_Assignment_Status
1168 , 'CONFIRMED'
1169 , Decode(p_Utilization_Method
1170 , 'ORGANIZATION'
1171 , decode(summbal.amount_type_id
1172 , 5, NVL(summbal.period_balance,0)
1173 , 0)
1174 , 'RESOURCE'
1175 , decode(summbal.amount_type_id
1176 , 6, NVL(summbal.period_balance,0)
1177 , 0))
1178 , 0)
1179 , 0)))
1180 *100/
1181 DECODE(p_Utilization_Category_Id
1182 ,0 , DECODE(p_Show_Percentage_By
1183 , 'CAPACITY'
1184 , DECODE(
1185 sign(
1186 sum(
1187 DecodE(paobj.balance_type_code
1188 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1189 , Decode(summbal.amount_type_id
1190 , 9, NVL(summbal.period_balance,0)
1191 , 0)
1192 , 0))
1193 -sum(
1194 DecodE(paobj.balance_type_code
1195 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1196 , Decode(summbal.amount_type_id
1197 , 10, NVL(summbal.period_balance,0)
1198 , 0)
1199 , 0)))
1200 , 1,
1201 (sum(
1202 DecodE(paobj.balance_type_code
1203 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1204 , Decode(summbal.amount_type_id
1205 , 9, NVL(summbal.period_balance,0)
1206 , 0)
1207 , 0))
1208 -sum(
1209 DecodE(paobj.balance_type_code
1210 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1211 , Decode(summbal.amount_type_id
1212 , 10, NVL(summbal.period_balance,0)
1213 , 0)
1214 , 0)))
1215 ,1)
1216 , 'TOTAL_WORKED_HOURS'
1217 , DECODE(
1218 sign(
1219 sum(
1220 DecodE(paobj.balance_type_code
1221 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1222 , Decode(summbal.amount_type_id
1223 , 1, NVL(summbal.period_balance,0)
1224 , 0)
1225 , 0)))
1226 , 1, sum(
1227 DecodE(paobj.balance_type_code
1228 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1229 , Decode(summbal.amount_type_id
1230 , 1, NVL(summbal.period_balance,0)
1231 , 0)
1232 , 0))
1233 , 1)
1234 )
1235 , PA_REP_UTIL_SCREEN.calculate_capacity(
1236 PA_REP_UTIL_GLOB.GetOrgId
1237 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1238 , max(paobj.person_id)
1239 , max(summbal.version_id)
1240 , max(summbal.period_type)
1241 , max(summbal.period_set_name)
1242 , max(summbal.period_name)
1243 , max(summbal.global_exp_period_end_date)
1244 , 1
1245 , 9
1246 , 10
1247 , p_Show_Percentage_By)
1248 )
1249 , -9999) -- finished NVL
1250 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding and concatenation
1251 /*
1252 * Field below is for FORECAST_CAP_OR_TOT_HRS
1253 */
1254 , DECODE(p_Utilization_Category_Id
1255 ,0 , DECODE(p_Show_Percentage_By
1256 , 'CAPACITY'
1257 , DECODE(
1258 sign(
1259 sum(
1260 DecodE(paobj.balance_type_code
1261 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1262 , Decode(summbal.amount_type_id
1263 , 9, NVL(summbal.period_balance,0)
1264 , 0)
1265 , 0))
1266 -sum(
1267 DecodE(paobj.balance_type_code
1268 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1269 , Decode(summbal.amount_type_id
1270 , 10, NVL(summbal.period_balance,0)
1271 , 0)
1272 , 0)))
1273 , 1,
1274 (sum(
1275 DecodE(paobj.balance_type_code
1276 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1277 , Decode(summbal.amount_type_id
1278 , 9, NVL(summbal.period_balance,0)
1279 , 0)
1280 , 0))
1281 -sum(
1282 DecodE(paobj.balance_type_code
1283 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1284 , Decode(summbal.amount_type_id
1285 , 10, NVL(summbal.period_balance,0)
1286 , 0)
1287 , 0)))
1288 ,1)
1289 , 'TOTAL_WORKED_HOURS'
1290 , DECODE(
1291 sign(
1292 sum(
1293 DecodE(paobj.balance_type_code
1294 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1295 , Decode(summbal.amount_type_id
1296 , 1, NVL(summbal.period_balance,0)
1297 , 0)
1298 , 0)))
1299 , 1, sum(
1300 DecodE(paobj.balance_type_code
1301 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1302 , Decode(summbal.amount_type_id
1303 , 1, NVL(summbal.period_balance,0)
1304 , 0)
1305 , 0))
1306 , 1)
1307 )
1308 , PA_REP_UTIL_SCREEN.calculate_capacity(
1309 PA_REP_UTIL_GLOB.GetOrgId
1310 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1311 , max(paobj.person_id)
1312 , max(summbal.version_id)
1313 , max(summbal.period_type)
1314 , max(summbal.period_set_name)
1315 , max(summbal.period_name)
1316 , max(summbal.global_exp_period_end_date)
1317 , 1
1318 , 9
1319 , 10
1320 , p_Show_Percentage_By)
1321 ) AS FORECAST_CAP_OR_TOT_HRS
1322 from
1323 PA_Summ_Balances summbal
1324 , PA_Objects paobj
1325 , pa_resources_denorm resdnorm
1326 , gl_periods glprd
1327 , pa_lookups lkup
1328 where
1329 lkup.lookup_type = 'PERSON_TYPE'
1330 AND lkup.lookup_code = 'EMPLOYEE'
1331 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
1332 AND (
1333 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.start_date))+0.99999
1334 and p_calling_mode = 'ORGMGR')
1335 OR
1336 /* Bug 2003821: start */
1337 (
1338 (
1339 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,trunc(sysdate)))+0.99999
1340 and glprd.start_date <=sysdate) /* Added for Bug 2325539 */
1341 OR
1342 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.start_date))+0.99999
1343 and glprd.start_date > sysdate)
1344 )
1345 and p_calling_mode = 'RESMGR'
1346 )
1347 )
1348 /* Bug 2003821: end */
1349 AND glprd.period_set_name = summbal.period_set_name
1350 AND glprd.period_name = summbal.period_name
1351 AND summbal.object_id = paobj.object_id
1352 AND summbal.version_id = -1
1353 AND summbal.period_type = p_Period_Type
1354 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
1355 AND summbal.period_name = p_Period_Name
1356 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
1357 , 2 /* G_RES_WTDHRS_ORG_C */
1358 , 3 /* G_RES_WTDHRS_PEOPLE_C */
1359 , 4 /* G_RES_PRVHRS_C */
1360 , 5 /* G_RES_PRVWTDHRS_ORG_C */
1361 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
1362 , 9 /* G_RES_CAP_C */
1363 ,10 /* G_RES_REDUCEDCAP_C */
1364 )
1365 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
1366 , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
1367 , Decode(p_Utilization_Method
1368 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
1369 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
1370 AND paobj.object_type_code = summbal.object_type_code
1371 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
1372 AND paobj.project_org_id = -1
1373 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
1374 AND paobj.project_organization_id = -1
1375 AND paobj.project_id = -1
1376 AND paobj.task_id = -1
1377 AND paobj.person_id = resdnorm.person_id
1378 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
1379 AND paobj.assignment_id = -1
1380 AND paobj.work_type_id = -1
1381 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
1382 , 0, -1
1383 , Decode(p_Utilization_Method
1384 , 'ORGANIZATION', p_Utilization_Category_Id
1385 , 'RESOURCE', -1))
1386 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
1387 , 0, -1
1388 , Decode(p_Utilization_Method
1389 , 'ORGANIZATION', -1
1390 , 'RESOURCE', p_Utilization_Category_Id))
1391 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
1392 group by
1393 DECODE(p_calling_mode
1394 , 'ORGMGR', paobj.expenditure_organization_id
1395 , 'RESMGR', NULL
1396 )
1397 , resdnorm.person_id
1398 , resdnorm.resource_id
1399 ;
1400 END IF;
1401 /*
1402 * END of Case 2 for U2
1403 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGl or Pa
1404 */
1405 /*
1406 * BEGINNING of Case 3 for U2
1407 * QR view
1408 */
1409 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr) THEN
1410
1411 INSERT INTO PA_REP_UTIL_SCREEN_TMP (
1412 Organization_id
1413 , Person_id
1414 , Resource_id
1415 , Resource_Name
1416 , Resource_Type
1417 , Resource_Type_Code
1418 , Calling_Mode
1419 , Job_Level
1420 , Actuals_Capacity
1421 , Actuals_hours
1422 , Actuals_Weighted_hours
1423 , Actuals_Weighted_hours_P
1424 , Actuals_Cap_OR_Tot_Hrs
1425 , Forecast_Capacity
1426 , Forecast_hours
1427 , Forecast_Weighted_hours
1428 , Forecast_Weighted_hours_P
1429 , Forecast_Cap_OR_Tot_Hrs
1430 )
1431 SELECT
1432 DECODE(p_calling_mode
1433 , 'ORGMGR', paobj.expenditure_organization_id
1434 , 'RESMGR', NULL
1435 ) AS ORGANIZATION_ID
1436 , resdnorm.person_id AS PERSON_ID
1437 , resdnorm.resource_id AS RESOURCE_ID
1438 , max(resdnorm.resource_name) AS RESOURCE_NAME
1439 , max(lkup.meaning) AS RESOURCE_TYPE
1440 , max(resdnorm.resource_type) AS RESOURCE_TYPE_CODE
1441 , p_Calling_Mode AS CALLING_MODE
1442 , max(resdnorm.resource_job_level) AS JOB_LEVEL
1443 /*
1444 * Field below is for ACTUALS_CAPACITY
1445 */
1446 ,DECODE( p_Utilization_Category_ID, 0,(
1447 DECODE(
1448 sign(
1449 sum(
1450 DecodE(paobj.balance_type_code
1451 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1452 , Decode(summbal.amount_type_id
1453 , 9, NVL(summbal.period_balance,0)
1454 , 0)
1455 , 0))
1456 -sum(
1457 DecodE(paobj.balance_type_code
1458 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1459 , Decode(summbal.amount_type_id
1460 , 10, NVL(summbal.period_balance,0)
1461 , 0)
1462 , 0)))
1463 , 1,
1464 (sum(
1465 DecodE(paobj.balance_type_code
1466 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1467 , Decode(summbal.amount_type_id
1468 , 9, NVL(summbal.period_balance,0)
1469 , 0)
1470 , 0))
1471 -sum(
1472 DecodE(paobj.balance_type_code
1473 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1474 , Decode(summbal.amount_type_id
1475 , 10, NVL(summbal.period_balance,0)
1476 , 0)
1477 , 0)))
1478 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1479 PA_REP_UTIL_GLOB.GetOrgId
1480 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1481 , max(paobj.person_id)
1482 , max(summbal.version_id)
1483 , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1484 , null
1485 , null
1486 , null
1487 , 1
1488 , 9
1489 , 10
1490 , p_Show_Percentage_By
1491 , p_organization_id
1492 , max(summbal.period_year)
1493 , max(quarter_or_month_number))) AS ACTUALS_CAPACITY
1494 /*
1495 * Field below is for ACTUALS_HOURS
1496 */
1497 , sum(
1498 DECODE(paobj.balance_type_code
1499 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1500 , Decode(summbal.amount_type_id
1501 , 1, NVL(summbal.period_balance,0)
1502 , 0)
1503 , 0)) AS ACTUALS_HOURS
1504 /*
1505 * Field below is for ACTUALS_WEIGHTED_HOURS
1506 */
1507 , sum(
1508 DECODE(paobj.balance_type_code
1509 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1510 , Decode(p_utilization_method
1511 , 'ORGANIZATION'
1512 , decode(summbal.amount_type_id
1513 , 2, NVL(summbal.period_balance,0)
1514 , 0)
1515 , 'RESOURCE'
1516 , decode(summbal.amount_type_id
1517 , 3, NVL(summbal.period_balance,0)
1518 , 0)
1519 )
1520 , 0)) AS ACTUALS_WEIGHTED_HOURS
1521 /*
1522 * Field below is for ACTUALS_WEIGHTED_HOURS_P
1523 */
1524 ,ROUND(NVL(sum(
1525 DECODE(paobj.balance_type_code
1526 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1527 , Decode(p_utilization_method
1528 , 'ORGANIZATION'
1529 , decode(summbal.amount_type_id
1530 , 2, NVL(summbal.period_balance,0)
1531 , 0)
1532 , 'RESOURCE'
1533 , decode(summbal.amount_type_id
1534 , 3, NVL(summbal.period_balance,0)
1535 , 0)
1536 )
1537 , 0))*100/
1538 DECODE(p_Utilization_Category_Id
1539 ,0 , DECODE(p_Show_Percentage_By
1540 , 'CAPACITY'
1541 , DECODE(
1542 sign(
1543 sum(
1544 DecodE(paobj.balance_type_code
1545 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1546 , Decode(summbal.amount_type_id
1547 , 9, NVL(summbal.period_balance,0)
1548 , 0)
1549 , 0))
1550 -sum(
1551 DecodE(paobj.balance_type_code
1552 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1553 , Decode(summbal.amount_type_id
1554 , 10, NVL(summbal.period_balance,0)
1555 , 0)
1556 , 0)))
1557 , 1,
1558 (sum(
1559 DecodE(paobj.balance_type_code
1560 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1561 , Decode(summbal.amount_type_id
1562 , 9, NVL(summbal.period_balance,0)
1563 , 0)
1564 , 0))
1565 -sum(
1566 DecodE(paobj.balance_type_code
1567 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1568 , Decode(summbal.amount_type_id
1569 , 10, NVL(summbal.period_balance,0)
1570 , 0)
1571 , 0)))
1572 ,1)
1573 , 'TOTAL_WORKED_HOURS'
1574 , DECODE(
1575 sign(
1576 sum(
1577 DecodE(paobj.balance_type_code
1578 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1579 , Decode(summbal.amount_type_id
1580 , 1, NVL(summbal.period_balance,0)
1581 , 0)
1582 , 0)))
1583 , 1, sum(
1584 DecodE(paobj.balance_type_code
1585 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1586 , Decode(summbal.amount_type_id
1587 , 1, NVL(summbal.period_balance,0)
1588 , 0)
1589 , 0))
1590 , 1)
1591 )
1592 , PA_REP_UTIL_SCREEN.calculate_capacity(
1593 PA_REP_UTIL_GLOB.GetOrgId
1594 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1595 , max(paobj.person_id)
1596 , max(summbal.version_id)
1597 , max(summbal.period_type)
1598 , max(summbal.period_set_name)
1599 , max(summbal.period_name)
1600 , max(summbal.global_exp_period_end_date)
1601 , 1
1602 , 9
1603 , 10
1604 , p_Show_Percentage_By)
1605 )
1606 , -9999) -- finished NVL
1607 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding and concatenation
1608 /*
1609 * Field below is for ACTUALS_CAP_OR_TOT_HRS
1610 */
1611 , DECODE(p_Utilization_Category_Id
1612 ,0 , DECODE(p_Show_Percentage_By
1613 , 'CAPACITY'
1614 , DECODE(
1615 sign(
1616 sum(
1617 DecodE(paobj.balance_type_code
1618 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1619 , Decode(summbal.amount_type_id
1620 , 9, NVL(summbal.period_balance,0)
1621 , 0)
1622 , 0))
1623 -sum(
1624 DecodE(paobj.balance_type_code
1625 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1626 , Decode(summbal.amount_type_id
1627 , 10, NVL(summbal.period_balance,0)
1628 , 0)
1629 , 0)))
1630 , 1,
1631 (sum(
1632 DecodE(paobj.balance_type_code
1633 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1634 , Decode(summbal.amount_type_id
1635 , 9, NVL(summbal.period_balance,0)
1636 , 0)
1637 , 0))
1638 -sum(
1639 DecodE(paobj.balance_type_code
1640 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1641 , Decode(summbal.amount_type_id
1642 , 10, NVL(summbal.period_balance,0)
1643 , 0)
1644 , 0)))
1645 ,1)
1646 , 'TOTAL_WORKED_HOURS'
1647 , DECODE(
1648 sign(
1649 sum(
1650 DecodE(paobj.balance_type_code
1651 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1652 , Decode(summbal.amount_type_id
1653 , 1, NVL(summbal.period_balance,0)
1654 , 0)
1655 , 0)))
1656 , 1, sum(
1657 DecodE(paobj.balance_type_code
1658 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1659 , Decode(summbal.amount_type_id
1660 , 1, NVL(summbal.period_balance,0)
1661 , 0)
1662 , 0))
1663 , 1)
1664 )
1665 , PA_REP_UTIL_SCREEN.calculate_capacity(
1666 PA_REP_UTIL_GLOB.GetOrgId
1667 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1668 , max(paobj.person_id)
1669 , max(summbal.version_id)
1670 , max(summbal.period_type)
1671 , max(summbal.period_set_name)
1672 , max(summbal.period_name)
1673 , max(summbal.global_exp_period_end_date)
1674 , 1
1675 , 9
1676 , 10
1677 , p_Show_Percentage_By)
1678 ) AS ACTUALS_CAP_OR_TOT_HRS
1679 /*
1680 * Field below is for FORECAST_CAPACITY
1681 */
1682 ,DECODE( p_Utilization_Category_ID, 0,(
1683 DECODE(
1684 sign(
1685 sum(
1686 DecodE(paobj.balance_type_code
1687 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1688 , Decode(summbal.amount_type_id
1689 , 9, NVL(summbal.period_balance,0)
1690 , 0)
1691 , 0))
1692 -sum(
1693 DecodE(paobj.balance_type_code
1694 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1695 , Decode(summbal.amount_type_id
1696 , 10, NVL(summbal.period_balance,0)
1697 , 0)
1698 , 0)))
1699 , 1,
1700 (sum(
1701 DecodE(paobj.balance_type_code
1702 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1703 , Decode(summbal.amount_type_id
1704 , 9, NVL(summbal.period_balance,0)
1705 , 0)
1706 , 0))
1707 -sum(
1708 DecodE(paobj.balance_type_code
1709 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1710 , Decode(summbal.amount_type_id
1711 , 10, NVL(summbal.period_balance,0)
1712 , 0)
1713 , 0)))
1714 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1715 PA_REP_UTIL_GLOB.GetOrgId
1716 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1717 , max(paobj.person_id)
1718 , max(summbal.version_id)
1719 , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1720 , null
1721 , null
1722 , null
1723 , 1
1724 , 9
1725 , 10
1726 , p_Show_Percentage_By
1727 , p_organization_id
1728 , max(summbal.period_year)
1729 , max(quarter_or_month_number))) AS FORECAST_CAPACITY
1730 /*
1731 * Field below is for FORECAST_HOURS
1732 */
1733 , sum(
1734 DECODE(paobj.balance_type_code
1735 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1736 , Decode(p_Assignment_Status
1737 , 'ALL'
1738 , decode(summbal.amount_type_id
1739 , 1, NVL(summbal.period_balance,0)
1740 , 0)
1741 , 'PROVISIONAL'
1742 , decode(summbal.amount_type_id
1743 , 4, NVL(summbal.period_balance,0)
1744 , 0)
1745 , 'CONFIRMED'
1746 , decode(summbal.amount_type_id
1747 , 1, NVL(summbal.period_balance,0)
1748 , 0)
1749 , 0)
1750 , 0))
1751 -sum(
1752 DECODE(paobj.balance_type_code
1753 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1754 , Decode(p_Assignment_Status
1755 , 'CONFIRMED'
1756 , decode(summbal.amount_type_id
1757 , 4, NVL(summbal.period_balance,0)
1758 , 0)
1759 , 0)
1760 , 0)) AS FORECAST_HOURS
1761 /*
1762 * Field below is for FORECAST_WEIGHTED_HOURS
1763 */
1764 , (sum(
1765 DECODE(paobj.balance_type_code
1766 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1767 , DecodE(p_Assignment_Status
1768 , 'ALL'
1769 , Decode(p_Utilization_Method
1770 , 'ORGANIZATION'
1771 , decode(summbal.amount_type_id
1772 , 2, NVL(summbal.period_balance,0)
1773 , 0)
1774 , 'RESOURCE'
1775 , decode(summbal.amount_type_id
1776 , 3, NVL(summbal.period_balance,0)
1777 , 0))
1778 , 'PROVISIONAL'
1779 , Decode(p_Utilization_Method
1780 , 'ORGANIZATION'
1781 , decode(summbal.amount_type_id
1782 , 5, NVL(summbal.period_balance,0)
1783 , 0)
1784 , 'RESOURCE'
1785 , decode(summbal.amount_type_id
1786 , 6, NVL(summbal.period_balance,0)
1787 , 0))
1788 , 'CONFIRMED'
1789 , Decode(p_Utilization_Method
1790 , 'ORGANIZATION'
1791 , decode(summbal.amount_type_id
1792 , 2, NVL(summbal.period_balance,0)
1793 , 0)
1794 , 'RESOURCE'
1795 , decode(summbal.amount_type_id
1796 , 3, NVL(summbal.period_balance,0)
1797 , 0))
1798 )
1799 , 0))
1800 -sum(
1801 DECODE(paobj.balance_type_code
1802 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1803 , Decode(p_Assignment_Status
1804 , 'CONFIRMED'
1805 , Decode(p_Utilization_Method
1806 , 'ORGANIZATION'
1807 , decode(summbal.amount_type_id
1808 , 5, NVL(summbal.period_balance,0)
1809 , 0)
1810 , 'RESOURCE'
1811 , decode(summbal.amount_type_id
1812 , 6, NVL(summbal.period_balance,0)
1813 , 0))
1814 , 0)
1815 , 0))) AS FORECAST_WEIGHTED_HOURS
1816 /*
1817 * Field below is for FORECAST_WEIGHTED_HOURS_P
1818 */
1819 ,ROUND(NVL(
1820 (sum(
1821 DECODE(paobj.balance_type_code
1822 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1823 , DecodE(p_Assignment_Status
1824 , 'ALL'
1825 , Decode(p_Utilization_Method
1826 , 'ORGANIZATION'
1827 , decode(summbal.amount_type_id
1828 , 2, NVL(summbal.period_balance,0)
1829 , 0)
1830 , 'RESOURCE'
1831 , decode(summbal.amount_type_id
1832 , 3, NVL(summbal.period_balance,0)
1833 , 0))
1834 , 'PROVISIONAL'
1835 , Decode(p_Utilization_Method
1836 , 'ORGANIZATION'
1837 , decode(summbal.amount_type_id
1838 , 5, NVL(summbal.period_balance,0)
1839 , 0)
1840 , 'RESOURCE'
1841 , decode(summbal.amount_type_id
1842 , 6, NVL(summbal.period_balance,0)
1843 , 0))
1844 , 'CONFIRMED'
1845 , Decode(p_Utilization_Method
1846 , 'ORGANIZATION'
1847 , decode(summbal.amount_type_id
1848 , 2, NVL(summbal.period_balance,0)
1849 , 0)
1850 , 'RESOURCE'
1851 , decode(summbal.amount_type_id
1852 , 3, NVL(summbal.period_balance,0)
1853 , 0))
1854 )
1855 , 0))
1856 -sum(
1857 DECODE(paobj.balance_type_code
1858 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1859 , Decode(p_Assignment_Status
1860 , 'CONFIRMED'
1861 , Decode(p_Utilization_Method
1862 , 'ORGANIZATION'
1863 , decode(summbal.amount_type_id
1864 , 5, NVL(summbal.period_balance,0)
1865 , 0)
1866 , 'RESOURCE'
1867 , decode(summbal.amount_type_id
1868 , 6, NVL(summbal.period_balance,0)
1869 , 0))
1870 , 0)
1871 , 0)))
1872 *100/
1873 DECODE(p_Utilization_Category_Id
1874 ,0 , DECODE(p_Show_Percentage_By
1875 , 'CAPACITY'
1876 , DECODE(
1877 sign(
1878 sum(
1879 DecodE(paobj.balance_type_code
1880 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1881 , Decode(summbal.amount_type_id
1882 , 9, NVL(summbal.period_balance,0)
1883 , 0)
1884 , 0))
1885 -sum(
1886 DecodE(paobj.balance_type_code
1887 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1888 , Decode(summbal.amount_type_id
1889 , 10, NVL(summbal.period_balance,0)
1890 , 0)
1891 , 0)))
1892 , 1,
1893 (sum(
1894 DecodE(paobj.balance_type_code
1895 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1896 , Decode(summbal.amount_type_id
1897 , 9, NVL(summbal.period_balance,0)
1898 , 0)
1899 , 0))
1900 -sum(
1901 DecodE(paobj.balance_type_code
1902 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1903 , Decode(summbal.amount_type_id
1904 , 10, NVL(summbal.period_balance,0)
1905 , 0)
1906 , 0)))
1907 ,1)
1908 , 'TOTAL_WORKED_HOURS'
1909 , DECODE(
1910 sign(
1911 sum(
1912 DecodE(paobj.balance_type_code
1913 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1914 , Decode(summbal.amount_type_id
1915 , 1, NVL(summbal.period_balance,0)
1916 , 0)
1917 , 0)))
1918 , 1, sum(
1919 DecodE(paobj.balance_type_code
1920 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1921 , Decode(summbal.amount_type_id
1922 , 1, NVL(summbal.period_balance,0)
1923 , 0)
1924 , 0))
1925 , 1)
1926 )
1927 , PA_REP_UTIL_SCREEN.calculate_capacity(
1928 PA_REP_UTIL_GLOB.GetOrgId
1929 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1930 , max(paobj.person_id)
1931 , max(summbal.version_id)
1932 , max(summbal.period_type)
1933 , max(summbal.period_set_name)
1934 , max(summbal.period_name)
1935 , max(summbal.global_exp_period_end_date)
1936 , 1
1937 , 9
1938 , 10
1939 , p_Show_Percentage_By)
1940 )
1941 , -9999) -- finished NVL
1942 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding and concatenation
1943 /*
1944 * Field below is for FORECAST_CAP_OR_TOT_HRS
1945 */
1946 , DECODE(p_Utilization_Category_Id
1947 ,0 , DECODE(p_Show_Percentage_By
1948 , 'CAPACITY'
1949 , DECODE(
1950 sign(
1951 sum(
1952 DecodE(paobj.balance_type_code
1953 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1954 , Decode(summbal.amount_type_id
1955 , 9, NVL(summbal.period_balance,0)
1956 , 0)
1957 , 0))
1958 -sum(
1959 DecodE(paobj.balance_type_code
1960 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1961 , Decode(summbal.amount_type_id
1962 , 10, NVL(summbal.period_balance,0)
1963 , 0)
1964 , 0)))
1965 , 1,
1966 (sum(
1967 DecodE(paobj.balance_type_code
1968 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1969 , Decode(summbal.amount_type_id
1970 , 9, NVL(summbal.period_balance,0)
1971 , 0)
1972 , 0))
1973 -sum(
1974 DecodE(paobj.balance_type_code
1975 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1976 , Decode(summbal.amount_type_id
1977 , 10, NVL(summbal.period_balance,0)
1978 , 0)
1979 , 0)))
1980 ,1)
1981 , 'TOTAL_WORKED_HOURS'
1982 , DECODE(
1983 sign(
1984 sum(
1985 DecodE(paobj.balance_type_code
1986 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1987 , Decode(summbal.amount_type_id
1988 , 1, NVL(summbal.period_balance,0)
1989 , 0)
1990 , 0)))
1991 , 1, sum(
1992 DecodE(paobj.balance_type_code
1993 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1994 , Decode(summbal.amount_type_id
1995 , 1, NVL(summbal.period_balance,0)
1996 , 0)
1997 , 0))
1998 , 1)
1999 )
2000 , PA_REP_UTIL_SCREEN.calculate_capacity(
2001 PA_REP_UTIL_GLOB.GetOrgId
2002 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2003 , max(paobj.person_id)
2004 , max(summbal.version_id)
2005 , max(summbal.period_type)
2006 , max(summbal.period_set_name)
2007 , max(summbal.period_name)
2008 , max(summbal.global_exp_period_end_date)
2009 , 1
2010 , 9
2011 , 10
2012 , p_Show_Percentage_By)
2013 ) AS FORECAST_CAP_OR_TOT_HRS
2014 from
2015 PA_Summ_Balances summbal
2016 , PA_Objects paobj
2017 , pa_resources_denorm resdnorm
2018 , gl_periods glprd
2019 , pa_lookups lkup
2020 where
2021 lkup.lookup_type = 'PERSON_TYPE'
2022 AND lkup.lookup_code = 'EMPLOYEE'
2023 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
2024 AND (
2025 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2026 and p_calling_mode = 'ORGMGR')
2027 OR
2028 /* Bug 2003821: start */
2029 (
2030 (
2031 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,trunc(sysdate)))+0.99999
2032 and glprd.start_date <=sysdate) /* Added for Bug 2325539 */
2033 OR
2034 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2035 and glprd.start_date > sysdate)
2036 )
2037 and p_calling_mode = 'RESMGR'
2038 )
2039 )
2040 /* Bug 2003821: end */
2041 AND glprd.period_set_name = summbal.period_set_name
2042 AND glprd.period_name = summbal.period_name
2043 AND summbal.object_id = paobj.object_id
2044 AND summbal.version_id = -1
2045 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2046 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2047 AND summbal.period_year = p_Period_Year
2048 AND summbal.quarter_or_month_number = p_Period_Quarter
2049 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2050 , 2 /* G_RES_WTDHRS_ORG_C */
2051 , 3 /* G_RES_WTDHRS_PEOPLE_C */
2052 , 4 /* G_RES_PRVHRS_C */
2053 , 5 /* G_RES_PRVWTDHRS_ORG_C */
2054 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
2055 , 9 /* G_RES_CAP_C */
2056 ,10 /* G_RES_REDUCEDCAP_C */
2057 )
2058 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2059 , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2060 , Decode(p_Utilization_Method
2061 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2062 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2063 AND paobj.object_type_code = summbal.object_type_code
2064 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2065 AND paobj.project_org_id = -1
2066 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2067 AND paobj.project_organization_id = -1
2068 AND paobj.project_id = -1
2069 AND paobj.task_id = -1
2070 AND paobj.person_id = resdnorm.person_id
2071 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
2072 AND paobj.assignment_id = -1
2073 AND paobj.work_type_id = -1
2074 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
2075 , 0, -1
2076 , Decode(p_Utilization_Method
2077 , 'ORGANIZATION', p_Utilization_Category_Id
2078 , 'RESOURCE', -1))
2079 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
2080 , 0, -1
2081 , Decode(p_Utilization_Method
2082 , 'ORGANIZATION', -1
2083 , 'RESOURCE', p_Utilization_Category_Id))
2084 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2085 group by
2086 DECODE(p_calling_mode
2087 , 'ORGMGR', paobj.expenditure_organization_id
2088 , 'RESMGR', NULL
2089 )
2090 , resdnorm.person_id
2091 , resdnorm.resource_id
2092 ;
2093 END IF;
2094 /*
2095 * END of Case 3 for U2
2096 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr
2097 */
2098 /*
2099 * BEGINNING of Case 4 for U2
2100 * YR view
2101 */
2102 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
2103
2104 INSERT INTO PA_REP_UTIL_SCREEN_TMP (
2105 Organization_id
2106 , Person_id
2107 , Resource_id
2108 , Resource_Name
2109 , Resource_Type
2110 , Resource_Type_Code
2111 , Calling_Mode
2112 , Job_Level
2113 , Actuals_Capacity
2114 , Actuals_hours
2115 , Actuals_Weighted_hours
2116 , Actuals_Weighted_hours_P
2117 , Actuals_Cap_OR_Tot_Hrs
2118 , Forecast_Capacity
2119 , Forecast_hours
2120 , Forecast_Weighted_hours
2121 , Forecast_Weighted_hours_P
2122 , Forecast_Cap_OR_Tot_Hrs
2123 )
2124 SELECT
2125 DECODE(p_calling_mode
2126 , 'ORGMGR', paobj.expenditure_organization_id
2127 , 'RESMGR', NULL
2128 ) AS ORGANIZATION_ID
2129 , resdnorm.person_id AS PERSON_ID
2130 , resdnorm.resource_id AS RESOURCE_ID
2131 , max(resdnorm.resource_name) AS RESOURCE_NAME
2132 , max(lkup.meaning) AS RESOURCE_TYPE
2133 , max(resdnorm.resource_type) AS RESOURCE_TYPE_CODE
2134 , p_Calling_Mode AS CALLING_MODE
2135 , max(resdnorm.resource_job_level) AS JOB_LEVEL
2136 /*
2137 * Field below is for ACTUALS_CAPACITY
2138 */
2139 ,DECODE( p_Utilization_Category_ID, 0,(
2140 DECODE(
2141 sign(
2142 sum(
2143 DecodE(paobj.balance_type_code
2144 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2145 , Decode(summbal.amount_type_id
2146 , 9, NVL(summbal.period_balance,0)
2147 , 0)
2148 , 0))
2149 -sum(
2150 DecodE(paobj.balance_type_code
2151 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2152 , Decode(summbal.amount_type_id
2153 , 10, NVL(summbal.period_balance,0)
2154 , 0)
2155 , 0)))
2156 , 1,
2157 (sum(
2158 DecodE(paobj.balance_type_code
2159 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2160 , Decode(summbal.amount_type_id
2161 , 9, NVL(summbal.period_balance,0)
2162 , 0)
2163 , 0))
2164 -sum(
2165 DecodE(paobj.balance_type_code
2166 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2167 , Decode(summbal.amount_type_id
2168 , 10, NVL(summbal.period_balance,0)
2169 , 0)
2170 , 0)))
2171 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2172 PA_REP_UTIL_GLOB.GetOrgId
2173 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2174 , max(paobj.person_id)
2175 , max(summbal.version_id)
2176 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2177 , null
2178 , null
2179 , null
2180 , 1
2181 , 9
2182 , 10
2183 , p_Show_Percentage_By
2184 , p_organization_id
2185 , max(summbal.period_year))) AS ACTUALS_CAPACITY
2186 /*
2187 * Field below is for ACTUALS_HOURS
2188 */
2189 , sum(
2190 DECODE(paobj.balance_type_code
2191 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2192 , Decode(summbal.amount_type_id
2193 , 1, NVL(summbal.period_balance,0)
2194 , 0)
2195 , 0)) AS ACTUALS_HOURS
2196 /*
2197 * Field below is for ACTUALS_WEIGHTED_HOURS
2198 */
2199 , sum(
2200 DECODE(paobj.balance_type_code
2201 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2202 , Decode(p_utilization_method
2203 , 'ORGANIZATION'
2204 , decode(summbal.amount_type_id
2205 , 2, NVL(summbal.period_balance,0)
2206 , 0)
2207 , 'RESOURCE'
2208 , decode(summbal.amount_type_id
2209 , 3, NVL(summbal.period_balance,0)
2210 , 0)
2211 )
2212 , 0)) AS ACTUALS_WEIGHTED_HOURS
2213 /*
2214 * Field below is for ACTUALS_WEIGHTED_HOURS_P
2215 */
2216 ,ROUND(NVL(sum(
2217 DECODE(paobj.balance_type_code
2218 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2219 , Decode(p_utilization_method
2220 , 'ORGANIZATION'
2221 , decode(summbal.amount_type_id
2222 , 2, NVL(summbal.period_balance,0)
2223 , 0)
2224 , 'RESOURCE'
2225 , decode(summbal.amount_type_id
2226 , 3, NVL(summbal.period_balance,0)
2227 , 0)
2228 )
2229 , 0))*100/
2230 DECODE(p_Utilization_Category_Id
2231 ,0 , DECODE(p_Show_Percentage_By
2232 , 'CAPACITY'
2233 , DECODE(
2234 sign(
2235 sum(
2236 DecodE(paobj.balance_type_code
2237 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2238 , Decode(summbal.amount_type_id
2239 , 9, NVL(summbal.period_balance,0)
2240 , 0)
2241 , 0))
2242 -sum(
2243 DecodE(paobj.balance_type_code
2244 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2245 , Decode(summbal.amount_type_id
2246 , 10, NVL(summbal.period_balance,0)
2247 , 0)
2248 , 0)))
2249 , 1,
2250 (sum(
2251 DecodE(paobj.balance_type_code
2252 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2253 , Decode(summbal.amount_type_id
2254 , 9, NVL(summbal.period_balance,0)
2255 , 0)
2256 , 0))
2257 -sum(
2258 DecodE(paobj.balance_type_code
2259 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2260 , Decode(summbal.amount_type_id
2261 , 10, NVL(summbal.period_balance,0)
2262 , 0)
2263 , 0)))
2264 ,1)
2265 , 'TOTAL_WORKED_HOURS'
2266 , DECODE(
2267 sign(
2268 sum(
2269 DecodE(paobj.balance_type_code
2270 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2271 , Decode(summbal.amount_type_id
2272 , 1, NVL(summbal.period_balance,0)
2273 , 0)
2274 , 0)))
2275 , 1, sum(
2276 DecodE(paobj.balance_type_code
2277 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2278 , Decode(summbal.amount_type_id
2279 , 1, NVL(summbal.period_balance,0)
2280 , 0)
2281 , 0))
2282 , 1)
2283 )
2284 , PA_REP_UTIL_SCREEN.calculate_capacity(
2285 PA_REP_UTIL_GLOB.GetOrgId
2286 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2287 , max(paobj.person_id)
2288 , max(summbal.version_id)
2289 , max(summbal.period_type)
2290 , max(summbal.period_set_name)
2291 , max(summbal.period_name)
2292 , max(summbal.global_exp_period_end_date)
2293 , 1
2294 , 9
2295 , 10
2296 , p_Show_Percentage_By)
2297 )
2298 , -9999) -- finished NVL
2299 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding and concatenation
2300 /*
2301 * Field below is for ACTUALS_CAP_OR_TOT_HRS
2302 */
2303 , DECODE(p_Utilization_Category_Id
2304 ,0 , DECODE(p_Show_Percentage_By
2305 , 'CAPACITY'
2306 , DECODE(
2307 sign(
2308 sum(
2309 DecodE(paobj.balance_type_code
2310 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2311 , Decode(summbal.amount_type_id
2312 , 9, NVL(summbal.period_balance,0)
2313 , 0)
2314 , 0))
2315 -sum(
2316 DecodE(paobj.balance_type_code
2317 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2318 , Decode(summbal.amount_type_id
2319 , 10, NVL(summbal.period_balance,0)
2320 , 0)
2321 , 0)))
2322 , 1,
2323 (sum(
2324 DecodE(paobj.balance_type_code
2325 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2326 , Decode(summbal.amount_type_id
2327 , 9, NVL(summbal.period_balance,0)
2328 , 0)
2329 , 0))
2330 -sum(
2331 DecodE(paobj.balance_type_code
2332 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2333 , Decode(summbal.amount_type_id
2334 , 10, NVL(summbal.period_balance,0)
2335 , 0)
2336 , 0)))
2337 ,1)
2338 , 'TOTAL_WORKED_HOURS'
2339 , DECODE(
2340 sign(
2341 sum(
2342 DecodE(paobj.balance_type_code
2343 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2344 , Decode(summbal.amount_type_id
2345 , 1, NVL(summbal.period_balance,0)
2346 , 0)
2347 , 0)))
2348 , 1, sum(
2349 DecodE(paobj.balance_type_code
2350 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2351 , Decode(summbal.amount_type_id
2352 , 1, NVL(summbal.period_balance,0)
2353 , 0)
2354 , 0))
2355 , 1)
2356 )
2357 , PA_REP_UTIL_SCREEN.calculate_capacity(
2358 PA_REP_UTIL_GLOB.GetOrgId
2359 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2360 , max(paobj.person_id)
2361 , max(summbal.version_id)
2362 , max(summbal.period_type)
2363 , max(summbal.period_set_name)
2364 , max(summbal.period_name)
2365 , max(summbal.global_exp_period_end_date)
2366 , 1
2367 , 9
2368 , 10
2369 , p_Show_Percentage_By)
2370 ) AS ACTUALS_CAP_OR_TOT_HRS
2371 /*
2372 * Field below is for FORECAST_CAPACITY
2373 */
2374 ,DECODE( p_Utilization_Category_ID, 0,(
2375 DECODE(
2376 sign(
2377 sum(
2378 DecodE(paobj.balance_type_code
2379 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2380 , Decode(summbal.amount_type_id
2381 , 9, NVL(summbal.period_balance,0)
2382 , 0)
2383 , 0))
2384 -sum(
2385 DecodE(paobj.balance_type_code
2386 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2387 , Decode(summbal.amount_type_id
2388 , 10, NVL(summbal.period_balance,0)
2389 , 0)
2390 , 0)))
2391 , 1,
2392 (sum(
2393 DecodE(paobj.balance_type_code
2394 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2395 , Decode(summbal.amount_type_id
2396 , 9, NVL(summbal.period_balance,0)
2397 , 0)
2398 , 0))
2399 -sum(
2400 DecodE(paobj.balance_type_code
2401 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2402 , Decode(summbal.amount_type_id
2403 , 10, NVL(summbal.period_balance,0)
2404 , 0)
2405 , 0)))
2406 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2407 PA_REP_UTIL_GLOB.GetOrgId
2408 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2409 , max(paobj.person_id)
2410 , max(summbal.version_id)
2411 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2412 , null
2413 , null
2414 , null
2415 , 1
2416 , 9
2417 , 10
2418 , p_Show_Percentage_By
2419 , p_organization_id
2420 , max(summbal.period_year))) AS FORECAST_CAPACITY
2421 /*
2422 * Field below is for FORECAST_HOURS
2423 */
2424 , sum(
2425 DECODE(paobj.balance_type_code
2426 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2427 , Decode(p_Assignment_Status
2428 , 'ALL'
2429 , decode(summbal.amount_type_id
2430 , 1, NVL(summbal.period_balance,0)
2431 , 0)
2432 , 'PROVISIONAL'
2433 , decode(summbal.amount_type_id
2434 , 4, NVL(summbal.period_balance,0)
2435 , 0)
2436 , 'CONFIRMED'
2437 , decode(summbal.amount_type_id
2438 , 1, NVL(summbal.period_balance,0)
2439 , 0)
2440 , 0)
2441 , 0))
2442 -sum(
2443 DECODE(paobj.balance_type_code
2444 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2445 , Decode(p_Assignment_Status
2446 , 'CONFIRMED'
2447 , decode(summbal.amount_type_id
2448 , 4, NVL(summbal.period_balance,0)
2449 , 0)
2450 , 0)
2451 , 0)) AS FORECAST_HOURS
2452 /*
2453 * Field below is for FORECAST_WEIGHTED_HOURS
2454 */
2455 , (sum(
2456 DECODE(paobj.balance_type_code
2457 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2458 , DecodE(p_Assignment_Status
2459 , 'ALL'
2460 , Decode(p_Utilization_Method
2461 , 'ORGANIZATION'
2462 , decode(summbal.amount_type_id
2463 , 2, NVL(summbal.period_balance,0)
2464 , 0)
2465 , 'RESOURCE'
2466 , decode(summbal.amount_type_id
2467 , 3, NVL(summbal.period_balance,0)
2468 , 0))
2469 , 'PROVISIONAL'
2470 , Decode(p_Utilization_Method
2471 , 'ORGANIZATION'
2472 , decode(summbal.amount_type_id
2473 , 5, NVL(summbal.period_balance,0)
2474 , 0)
2475 , 'RESOURCE'
2476 , decode(summbal.amount_type_id
2477 , 6, NVL(summbal.period_balance,0)
2478 , 0))
2479 , 'CONFIRMED'
2480 , Decode(p_Utilization_Method
2481 , 'ORGANIZATION'
2482 , decode(summbal.amount_type_id
2483 , 2, NVL(summbal.period_balance,0)
2484 , 0)
2485 , 'RESOURCE'
2486 , decode(summbal.amount_type_id
2487 , 3, NVL(summbal.period_balance,0)
2488 , 0))
2489 )
2490 , 0))
2491 -sum(
2492 DECODE(paobj.balance_type_code
2493 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2494 , Decode(p_Assignment_Status
2495 , 'CONFIRMED'
2496 , Decode(p_Utilization_Method
2497 , 'ORGANIZATION'
2498 , decode(summbal.amount_type_id
2499 , 5, NVL(summbal.period_balance,0)
2500 , 0)
2501 , 'RESOURCE'
2502 , decode(summbal.amount_type_id
2503 , 6, NVL(summbal.period_balance,0)
2504 , 0))
2505 , 0)
2506 , 0))) AS FORECAST_WEIGHTED_HOURS
2507 /*
2508 * Field below is for FORECAST_WEIGHTED_HOURS_P
2509 */
2510 ,ROUND(NVL(
2511 (sum(
2512 DECODE(paobj.balance_type_code
2513 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2514 , DecodE(p_Assignment_Status
2515 , 'ALL'
2516 , Decode(p_Utilization_Method
2517 , 'ORGANIZATION'
2518 , decode(summbal.amount_type_id
2519 , 2, NVL(summbal.period_balance,0)
2520 , 0)
2521 , 'RESOURCE'
2522 , decode(summbal.amount_type_id
2523 , 3, NVL(summbal.period_balance,0)
2524 , 0))
2525 , 'PROVISIONAL'
2526 , Decode(p_Utilization_Method
2527 , 'ORGANIZATION'
2528 , decode(summbal.amount_type_id
2529 , 5, NVL(summbal.period_balance,0)
2530 , 0)
2531 , 'RESOURCE'
2532 , decode(summbal.amount_type_id
2533 , 6, NVL(summbal.period_balance,0)
2534 , 0))
2535 , 'CONFIRMED'
2536 , Decode(p_Utilization_Method
2537 , 'ORGANIZATION'
2538 , decode(summbal.amount_type_id
2539 , 2, NVL(summbal.period_balance,0)
2540 , 0)
2541 , 'RESOURCE'
2542 , decode(summbal.amount_type_id
2543 , 3, NVL(summbal.period_balance,0)
2544 , 0))
2545 )
2546 , 0))
2547 -sum(
2548 DECODE(paobj.balance_type_code
2549 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2550 , Decode(p_Assignment_Status
2551 , 'CONFIRMED'
2552 , Decode(p_Utilization_Method
2553 , 'ORGANIZATION'
2554 , decode(summbal.amount_type_id
2555 , 5, NVL(summbal.period_balance,0)
2556 , 0)
2557 , 'RESOURCE'
2558 , decode(summbal.amount_type_id
2559 , 6, NVL(summbal.period_balance,0)
2560 , 0))
2561 , 0)
2562 , 0)))
2563 *100/
2564 DECODE(p_Utilization_Category_Id
2565 ,0 , DECODE(p_Show_Percentage_By
2566 , 'CAPACITY'
2567 , DECODE(
2568 sign(
2569 sum(
2570 DecodE(paobj.balance_type_code
2571 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2572 , Decode(summbal.amount_type_id
2573 , 9, NVL(summbal.period_balance,0)
2574 , 0)
2575 , 0))
2576 -sum(
2577 DecodE(paobj.balance_type_code
2578 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2579 , Decode(summbal.amount_type_id
2580 , 10, NVL(summbal.period_balance,0)
2581 , 0)
2582 , 0)))
2583 , 1,
2584 (sum(
2585 DecodE(paobj.balance_type_code
2586 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2587 , Decode(summbal.amount_type_id
2588 , 9, NVL(summbal.period_balance,0)
2589 , 0)
2590 , 0))
2591 -sum(
2592 DecodE(paobj.balance_type_code
2593 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2594 , Decode(summbal.amount_type_id
2595 , 10, NVL(summbal.period_balance,0)
2596 , 0)
2597 , 0)))
2598 ,1)
2599 , 'TOTAL_WORKED_HOURS'
2600 , DECODE(
2601 sign(
2602 sum(
2603 DecodE(paobj.balance_type_code
2604 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2605 , Decode(summbal.amount_type_id
2606 , 1, NVL(summbal.period_balance,0)
2607 , 0)
2608 , 0)))
2609 , 1, sum(
2610 DecodE(paobj.balance_type_code
2611 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2612 , Decode(summbal.amount_type_id
2613 , 1, NVL(summbal.period_balance,0)
2614 , 0)
2615 , 0))
2616 , 1)
2617 )
2618 , PA_REP_UTIL_SCREEN.calculate_capacity(
2619 PA_REP_UTIL_GLOB.GetOrgId
2620 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2621 , max(paobj.person_id)
2622 , max(summbal.version_id)
2623 , max(summbal.period_type)
2624 , max(summbal.period_set_name)
2625 , max(summbal.period_name)
2626 , max(summbal.global_exp_period_end_date)
2627 , 1
2628 , 9
2629 , 10
2630 , p_Show_Percentage_By)
2631 )
2632 , -9999) -- finished NVL
2633 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding and concatenation
2634 /*
2635 * Field below is for FORECAST_CAP_OR_TOT_HRS
2636 */
2637 , DECODE(p_Utilization_Category_Id
2638 ,0 , DECODE(p_Show_Percentage_By
2639 , 'CAPACITY'
2640 , DECODE(
2641 sign(
2642 sum(
2643 DecodE(paobj.balance_type_code
2644 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2645 , Decode(summbal.amount_type_id
2646 , 9, NVL(summbal.period_balance,0)
2647 , 0)
2648 , 0))
2649 -sum(
2650 DecodE(paobj.balance_type_code
2651 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2652 , Decode(summbal.amount_type_id
2653 , 10, NVL(summbal.period_balance,0)
2654 , 0)
2655 , 0)))
2656 , 1,
2657 (sum(
2658 DecodE(paobj.balance_type_code
2659 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2660 , Decode(summbal.amount_type_id
2661 , 9, NVL(summbal.period_balance,0)
2662 , 0)
2663 , 0))
2664 -sum(
2665 DecodE(paobj.balance_type_code
2666 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2667 , Decode(summbal.amount_type_id
2668 , 10, NVL(summbal.period_balance,0)
2669 , 0)
2670 , 0)))
2671 ,1)
2672 , 'TOTAL_WORKED_HOURS'
2673 , DECODE(
2674 sign(
2675 sum(
2676 DecodE(paobj.balance_type_code
2677 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2678 , Decode(summbal.amount_type_id
2679 , 1, NVL(summbal.period_balance,0)
2680 , 0)
2681 , 0)))
2682 , 1, sum(
2683 DecodE(paobj.balance_type_code
2684 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2685 , Decode(summbal.amount_type_id
2686 , 1, NVL(summbal.period_balance,0)
2687 , 0)
2688 , 0))
2689 , 1)
2690 )
2691 , PA_REP_UTIL_SCREEN.calculate_capacity(
2692 PA_REP_UTIL_GLOB.GetOrgId
2693 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2694 , max(paobj.person_id)
2695 , max(summbal.version_id)
2696 , max(summbal.period_type)
2697 , max(summbal.period_set_name)
2698 , max(summbal.period_name)
2699 , max(summbal.global_exp_period_end_date)
2700 , 1
2701 , 9
2702 , 10
2703 , p_Show_Percentage_By)
2704 ) AS FORECAST_CAP_OR_TOT_HRS
2705 from
2706 PA_Summ_Balances summbal
2707 , PA_Objects paobj
2708 , pa_resources_denorm resdnorm
2709 , gl_periods glprd
2710 , pa_lookups lkup
2711 where
2712 lkup.lookup_type = 'PERSON_TYPE'
2713 AND lkup.lookup_code = 'EMPLOYEE'
2714 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
2715 AND (
2716 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2717 and p_calling_mode = 'ORGMGR')
2718 OR
2719 /* Bug 2003821: start */
2720 (
2721 (
2722 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,trunc(sysdate)))+0.99999
2723 and glprd.start_date <=sysdate) /* Added for Bug 2325539 */
2724 OR
2725 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2726 and glprd.start_date > sysdate)
2727 )
2728 and p_calling_mode = 'RESMGR'
2729 )
2730 )
2731 /* Bug 2003821: end */
2732 AND glprd.period_set_name = summbal.period_set_name
2733 AND glprd.period_name = summbal.period_name
2734 AND summbal.object_id = paobj.object_id
2735 AND summbal.version_id = -1
2736 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2737 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2738 AND summbal.period_year = p_Period_Year
2739 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2740 , 2 /* G_RES_WTDHRS_ORG_C */
2741 , 3 /* G_RES_WTDHRS_PEOPLE_C */
2742 , 4 /* G_RES_PRVHRS_C */
2743 , 5 /* G_RES_PRVWTDHRS_ORG_C */
2744 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
2745 , 9 /* G_RES_CAP_C */
2746 ,10 /* G_RES_REDUCEDCAP_C */
2747 )
2748 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2749 , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2750 , Decode(p_Utilization_Method
2751 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2752 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2753 AND paobj.object_type_code = summbal.object_type_code
2754 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2755 AND paobj.project_org_id = -1
2756 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2757 AND paobj.project_organization_id = -1
2758 AND paobj.project_id = -1
2759 AND paobj.task_id = -1
2760 AND paobj.person_id = resdnorm.person_id
2761 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
2762 AND paobj.assignment_id = -1
2763 AND paobj.work_type_id = -1
2764 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
2765 , 0, -1
2766 , Decode(p_Utilization_Method
2767 , 'ORGANIZATION', p_Utilization_Category_Id
2768 , 'RESOURCE', -1))
2769 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
2770 , 0, -1
2771 , Decode(p_Utilization_Method
2772 , 'ORGANIZATION', -1
2773 , 'RESOURCE', p_Utilization_Category_Id))
2774 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2775 group by
2776 DECODE(p_calling_mode
2777 , 'ORGMGR', paobj.expenditure_organization_id
2778 , 'RESMGR', NULL
2779 )
2780 , resdnorm.person_id
2781 , resdnorm.resource_id
2782 ;
2783 END IF;
2784 /*
2785 * END of Case 4 for U2
2786 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr
2787 */
2788
2789
2790 /*
2791 * Bug 1633069
2792 * Now to update the resource_type_code, added through bug 1633069,
2793 * to identify the resources reporting to the select manager who are also
2794 * in turn managers of other resources. The above select put the value of
2795 * of EMPLOYEE for all the records now check against pa_resources_denorm
2796 * to update the resource_type_code to MANAGERS appropriately.
2797 */
2798
2799 Update PA_REP_UTIL_SCREEN_TMP tmp
2800 Set (resource_type,resource_type_code) = (select lkup2.meaning,lkup2.lookup_code
2801 from pa_lookups lkup2
2802 where lkup2.lookup_type='PERSON_TYPE'
2803 and lkup2.lookup_code = 'MANAGER')
2804 Where exists (select prd.Person_id
2805 from pa_resources_denorm prd
2806 where prd.manager_id = tmp.person_id)
2807 ;
2808
2809
2810 EXCEPTION
2811
2812 WHEN OTHERS THEN
2813 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_REP_UTIL_SCREEN.poplt_screen_tmp_table'
2814 , p_procedure_name => PA_DEBUG.G_Err_Stack);
2815 RAISE;
2816
2817
2818 END poplt_screen_tmp_table; /* End of Procedure poplt_screen_tmp_table */
2819
2820 /*
2821 * Functions.
2822 */
2823
2824 /*
2825 * The function calculate_capacity is needed for the cases when
2826 * p_Utilization_Category_Id <> 0, ie the object_type_code is not RES. In
2827 * such cases the select will calculate the capacity from the current record
2828 * which is incorrect since capacity should ALWAYS be calculated using the
2829 * RES record. Thus for cases when the object_type_code <> RES the decode
2830 * has been coded such that the function calculate_capacity would be called.
2831 */
2832
2833 FUNCTION calculate_capacity(
2834 p_ORG_ID IN NUMBER
2835 , p_Balance_Type_Code IN VARCHAR2
2836 , p_Entity_ID IN NUMBER
2837 , p_Version_ID IN NUMBER
2838 , p_Period_Type IN VARCHAR2
2839 , p_Period_Set_Name IN VARCHAR2
2840 , p_Period_Name IN VARCHAR2
2841 , p_Global_Exp_Period_End_Date IN DATE
2842 , p_Amount_ID_Resource_Hours IN NUMBER
2843 , p_Amount_ID_Capacity IN NUMBER
2844 , p_Amount_ID_Reduced_Capacity IN NUMBER
2845 , p_Show_Percentage_By IN VARCHAR2
2846 , p_Organization_Id IN NUMBER DEFAULT NULL
2847 , p_Period_Year IN NUMBER DEFAULT NULL
2848 , p_Quarter_Or_Month_Number IN NUMBER DEFAULT NULL
2849 )
2850 RETURN NUMBER
2851 IS
2852 v_total_hours NUMBER := 0;
2853 v_derived_cap NUMBER := 0;
2854 v_raw_cap NUMBER := 0;
2855 v_raw_reduced_cap NUMBER := 0;
2856 v_amount_type_id NUMBER := 0;
2857 v_period_balance NUMBER := 0;
2858 TYPE t_cap_rec IS REF CURSOR;
2859 c_cap_rec t_cap_rec;
2860 BEGIN
2861 IF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeQr THEN
2862 open c_cap_rec for
2863 select
2864 summbal2.amount_type_id AS amount_type_id
2865 , sum(summbal2.period_balance) AS period_balance
2866 from
2867 PA_Summ_Balances summbal2
2868 , PA_Objects paobj2
2869 where
2870 paobj2.Balance_Type_Code = p_Balance_Type_Code
2871 AND paobj2.expenditure_org_id = p_ORG_ID
2872 AND summbal2.version_id = p_Version_ID
2873 AND (( summbal2.object_type_code =
2874 PA_REP_UTIL_GLOB.GetObjectTypeRes
2875 AND paobj2.person_id = p_Entity_ID
2876 AND paobj2.expenditure_organization_id =
2877 nvl(p_organization_id,paobj2.expenditure_organization_id))
2878 OR
2879 ( summbal2.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg
2880 AND paobj2.expenditure_organization_id = p_Entity_ID)
2881 )
2882 AND summbal2.object_id = paobj2.object_id
2883 AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2884 AND period_year = p_Period_Year
2885 AND quarter_or_month_number = p_Quarter_Or_Month_Number
2886 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2887 group by
2888 summbal2.amount_type_id;
2889
2890 ELSIF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeYr THEN
2891 open c_cap_rec for
2892 select
2893 summbal2.amount_type_id AS amount_type_id
2894 , sum(summbal2.period_balance) AS period_balance
2895 from
2896 PA_Summ_Balances summbal2
2897 , PA_Objects paobj2
2898 where
2899 paobj2.Balance_Type_Code = p_Balance_Type_Code
2900 AND paobj2.expenditure_org_id = p_ORG_ID
2901 AND summbal2.version_id = p_Version_ID
2902 AND (( summbal2.object_type_code =
2903 PA_REP_UTIL_GLOB.GetObjectTypeRes
2904 AND paobj2.person_id = p_Entity_ID
2905 AND paobj2.expenditure_organization_id =
2906 nvl(p_organization_id,paobj2.expenditure_organization_id))
2907 OR
2908 ( summbal2.object_type_code =
2909 PA_REP_UTIL_GLOB.GetObjectTypeOrg
2910 AND paobj2.expenditure_organization_id = p_Entity_ID)
2911 )
2912 AND summbal2.object_id = paobj2.object_id
2913 AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2914 AND period_year = p_Period_Year
2915 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2916 group by
2917 summbal2.amount_type_id;
2918 ELSE
2919 open c_cap_rec for
2920 select
2921 summbal2.amount_type_id AS amount_type_id
2922 , sum(summbal2.period_balance) AS period_balance
2923 from
2924 PA_Summ_Balances summbal2
2925 , PA_Objects paobj2
2926 where
2927 paobj2.Balance_Type_Code = p_Balance_Type_Code
2928 AND paobj2.expenditure_org_id = p_ORG_ID
2929 AND summbal2.version_id = p_Version_ID
2930 AND (( summbal2.object_type_code =
2931 PA_REP_UTIL_GLOB.GetObjectTypeRes
2932 AND paobj2.person_id = p_Entity_ID
2933 AND paobj2.expenditure_organization_id =
2934 nvl(p_organization_id,paobj2.expenditure_organization_id))
2935 OR
2936 ( summbal2.object_type_code =
2937 PA_REP_UTIL_GLOB.GetObjectTypeOrg
2938 AND paobj2.expenditure_organization_id = p_Entity_ID)
2939 )
2940 AND summbal2.object_id = paobj2.object_id
2941 AND summbal2.period_type = p_Period_Type
2942 AND summbal2.period_set_name = p_Period_Set_Name
2943 AND summbal2.period_name = p_Period_Name
2944 AND summbal2.global_exp_period_end_date =
2945 p_Global_Exp_Period_End_Date
2946 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2947 group by
2948 summbal2.amount_type_id;
2949
2950 END IF;
2951 LOOP
2952 fetch c_cap_rec into v_amount_type_id,v_period_balance;
2953 IF (v_amount_type_id=p_Amount_ID_Capacity) THEN
2954 v_raw_cap := v_period_balance;
2955 ELSIF (v_amount_type_id=p_Amount_ID_Reduced_Capacity) THEN
2956 v_raw_reduced_cap := v_period_balance;
2957 ELSIF (v_amount_type_id=p_Amount_ID_Resource_Hours) THEN
2958 v_total_hours := v_period_balance;
2959 END IF;
2960 EXIT WHEN c_cap_rec%NOTFOUND;
2961 END LOOP;
2962
2963 v_derived_cap := NVL(v_raw_cap,0) - NVL(v_raw_reduced_cap,0);
2964
2965 IF NVL(v_derived_cap,-1) <= 0 THEN
2966 v_derived_cap := 1;
2967 END IF;
2968
2969 IF NVL(v_total_hours,-1) <= 0 THEN
2970 v_total_hours := 1;
2971 END IF;
2972
2973 IF (p_Show_Percentage_By='CAPACITY') THEN
2974 return v_derived_cap;
2975 ELSE
2976 return v_total_hours;
2977 END IF;
2978
2979 END calculate_capacity;
2980
2981 PROCEDURE poplt_u1_screen_tmp_table(
2982 p_Organization_ID IN NUMBER
2983 , p_Period_Type IN VARCHAR2
2984 , p_Period_Year IN VARCHAR2
2985 , p_Period_Quarter IN VARCHAR2
2986 , p_Period_Name IN VARCHAR2
2987 , p_Global_Week_End_Date IN VARCHAR2
2988 , p_Show_Percentage_By IN VARCHAR2
2989 )
2990 IS
2991 BEGIN
2992
2993 delete from PA_REP_UTIL_SCR_U1_TMP;
2994
2995 /* */
2996 /* Case 1 for GE view */
2997 /* */
2998 PA_REP_UTIL_GLOB.SetU1Params(p_organization_id,p_period_type,p_period_name,to_number(p_period_year));
2999 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe) THEN
3000 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3001 (
3002 title_name
3003 , title_code
3004 , exp_organization_id
3005 , exp_sub_organization_id
3006 , exp_sub_organization_name
3007 , emp_head_count
3008 , others_head_count
3009 , actuals_capacity
3010 , actuals_hours
3011 , actuals_weighted_hours
3012 , actuals_utilization
3013 , forecast_capacity
3014 , forecast_hours
3015 , forecast_weighted_hours
3016 , forecast_utilization
3017 , period_year
3018 , period_month
3019 , exp_end_date )
3020 SELECT
3021 title_name
3022 , title_code
3023 , exp_organization_id
3024 , exp_sub_organization_id
3025 , exp_sub_organization_name
3026 , emp_head_count
3027 , others_head_count
3028 , actuals_capacity
3029 , actuals_hours
3030 , actuals_weighted_hours
3031 , actuals_utilization
3032 , forecast_capacity
3033 , forecast_hours
3034 , forecast_weighted_hours
3035 , forecast_utilization
3036 , period_year
3037 , period_month
3038 , exp_end_date from PA_REP_UTIL_ORG_GE_V;
3039
3040 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3041 p_period_type
3042 , p_Global_Week_End_Date -- in lieu of p_period_name
3043 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3044 , p_period_year
3045 );
3046 /* */
3047 /* Case 2 for GL view */
3048 /* */
3049 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGl) THEN
3050 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3051 (
3052 title_name
3053 , title_code
3054 , exp_organization_id
3055 , exp_sub_organization_id
3056 , exp_sub_organization_name
3057 , emp_head_count
3058 , others_head_count
3059 , actuals_capacity
3060 , actuals_hours
3061 , actuals_weighted_hours
3062 , actuals_utilization
3063 , forecast_capacity
3064 , forecast_hours
3065 , forecast_weighted_hours
3066 , forecast_utilization
3067 , period_set_name
3068 , period_year
3069 , period_quarter
3070 , period_name
3071 , period_num)
3072 SELECT
3073 title_name
3074 , title_code
3075 , exp_organization_id
3076 , exp_sub_organization_id
3077 , exp_sub_organization_name
3078 , emp_head_count
3079 , others_head_count
3080 , actuals_capacity
3081 , actuals_hours
3082 , actuals_weighted_hours
3083 , actuals_utilization
3084 , forecast_capacity
3085 , forecast_hours
3086 , forecast_weighted_hours
3087 , forecast_utilization
3088 , period_set_name
3089 , period_year
3090 , period_quarter
3091 , period_name
3092 , period_num
3093 from PA_REP_UTIL_ORG_GL_V;
3094
3095 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3096 p_period_type
3097 , p_period_name
3098 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3099 , p_period_year
3100 );
3101 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypePa) THEN
3102 /* */
3103 /* Case 3 for PA view */
3104 /* */
3105 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3106 (
3107 title_name
3108 , title_code
3109 , exp_organization_id
3110 , exp_sub_organization_id
3111 , exp_sub_organization_name
3112 , emp_head_count
3113 , others_head_count
3114 , actuals_capacity
3115 , actuals_hours
3116 , actuals_weighted_hours
3117 , actuals_utilization
3118 , forecast_capacity
3119 , forecast_hours
3120 , forecast_weighted_hours
3121 , forecast_utilization
3122 , period_set_name
3123 , period_year
3124 , period_quarter
3125 , period_name
3126 , period_num)
3127 SELECT
3128 title_name
3129 , title_code
3130 , exp_organization_id
3131 , exp_sub_organization_id
3132 , exp_sub_organization_name
3133 , emp_head_count
3134 , others_head_count
3135 , actuals_capacity
3136 , actuals_hours
3137 , actuals_weighted_hours
3138 , actuals_utilization
3139 , forecast_capacity
3140 , forecast_hours
3141 , forecast_weighted_hours
3142 , forecast_utilization
3143 , period_set_name
3144 , period_year
3145 , period_quarter
3146 , period_name
3147 , period_num
3148 from PA_REP_UTIL_ORG_PA_V;
3149
3150 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3151 p_period_type
3152 , p_period_name
3153 , null
3154 , p_period_year
3155 );
3156 /* */
3157 /* Case 4 for YR view */
3158 /* */
3159 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
3160 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3161 (
3162 title_name
3163 , title_code
3164 , exp_organization_id
3165 , exp_sub_organization_id
3166 , exp_sub_organization_name
3167 , emp_head_count
3168 , others_head_count
3169 , actuals_capacity
3170 , actuals_hours
3171 , actuals_weighted_hours
3172 , actuals_utilization
3173 , forecast_capacity
3174 , forecast_hours
3175 , forecast_weighted_hours
3176 , forecast_utilization
3177 , period_set_name
3178 , period_year)
3179 SELECT
3180 title_name
3181 , title_code
3182 , exp_organization_id
3183 , exp_sub_organization_id
3184 , exp_sub_organization_name
3185 , emp_head_count
3186 , others_head_count
3187 , actuals_capacity
3188 , actuals_hours
3189 , actuals_weighted_hours
3190 , actuals_utilization
3191 , forecast_capacity
3192 , forecast_hours
3193 , forecast_weighted_hours
3194 , forecast_utilization
3195 , period_set_name
3196 , period_year from PA_REP_UTIL_ORG_YR_V;
3197
3198 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3199 p_period_type
3200 , p_period_name
3201 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3202 , p_period_year
3203 );
3204 /* */
3205 /* Case 5 for QR view */
3206 /* */
3207 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr) THEN
3208 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3209 (
3210 title_name
3211 , title_code
3212 , exp_organization_id
3213 , exp_sub_organization_id
3214 , exp_sub_organization_name
3215 , emp_head_count
3216 , others_head_count
3217 , actuals_capacity
3218 , actuals_hours
3219 , actuals_weighted_hours
3220 , actuals_utilization
3221 , forecast_capacity
3222 , forecast_hours
3223 , forecast_weighted_hours
3224 , forecast_utilization
3225 , period_set_name
3226 , period_year
3227 , period_quarter )
3228 SELECT
3229 title_name
3230 , title_code
3231 , exp_organization_id
3232 , exp_sub_organization_id
3233 , exp_sub_organization_name
3234 , emp_head_count
3235 , others_head_count
3236 , actuals_capacity
3237 , actuals_hours
3238 , actuals_weighted_hours
3239 , actuals_utilization
3240 , forecast_capacity
3241 , forecast_hours
3242 , forecast_weighted_hours
3243 , forecast_utilization
3244 , period_set_name
3245 , period_year
3246 , period_quarter from PA_REP_UTIL_ORG_QR_V
3247 WHERE
3248 period_quarter = p_period_quarter;
3249
3250 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3251 p_period_type
3252 , p_period_quarter -- in lieu of p_period_name
3253 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3254 , p_period_year
3255 );
3256 END IF;
3257
3258 /* Commented for bug 5680366
3259 UPDATE PA_REP_UTIL_SCR_U1_TMP U1
3260 Set (emp_head_count, others_head_count) =
3261 (Select HC.emp_headcount,0
3262 From PA_RES_EMP_HCOUNT_V HC
3263 Where U1.exp_sub_organization_id = HC.organization_id
3264 And DECODE(U1.exp_sub_organization_id
3265 , p_organization_id, U1.title_code
3266 , HC.headcount_code)
3267 = HC.headcount_code
3268 );
3269 */
3270 COMMIT;
3271 END poplt_u1_screen_tmp_table;
3272 END PA_REP_UTIL_SCREEN;