[Home] [Help]
PACKAGE BODY: APPS.PA_REP_UTIL_SCREEN
Source
1 PACKAGE BODY PA_REP_UTIL_SCREEN AS
2 /* $Header: PARRSCRB.pls 120.3.12010000.2 2009/05/29 12:53:00 nisinha 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 , PA_REP_UTIL_GLOB.GetPeriodTypeQr --Bug 8528649 Start changes for Quarter
1598 , null
1599 , null
1600 , null
1601 , 1
1602 , 9
1603 , 10
1604 , p_Show_Percentage_By
1605 , p_Organization_ID
1606 , p_Period_Year
1607 , p_Period_Quarter) --Bug 8528649 End changes for Quarter
1608 )
1609 , -9999) -- finished NVL
1610 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding and concatenation
1611 /*
1612 * Field below is for ACTUALS_CAP_OR_TOT_HRS
1613 */
1614 , DECODE(p_Utilization_Category_Id
1615 ,0 , DECODE(p_Show_Percentage_By
1616 , 'CAPACITY'
1617 , DECODE(
1618 sign(
1619 sum(
1620 DecodE(paobj.balance_type_code
1621 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1622 , Decode(summbal.amount_type_id
1623 , 9, NVL(summbal.period_balance,0)
1624 , 0)
1625 , 0))
1626 -sum(
1627 DecodE(paobj.balance_type_code
1628 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1629 , Decode(summbal.amount_type_id
1630 , 10, NVL(summbal.period_balance,0)
1631 , 0)
1632 , 0)))
1633 , 1,
1634 (sum(
1635 DecodE(paobj.balance_type_code
1636 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1637 , Decode(summbal.amount_type_id
1638 , 9, NVL(summbal.period_balance,0)
1639 , 0)
1640 , 0))
1641 -sum(
1642 DecodE(paobj.balance_type_code
1643 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1644 , Decode(summbal.amount_type_id
1645 , 10, NVL(summbal.period_balance,0)
1646 , 0)
1647 , 0)))
1648 ,1)
1649 , 'TOTAL_WORKED_HOURS'
1650 , DECODE(
1651 sign(
1652 sum(
1653 DecodE(paobj.balance_type_code
1654 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1655 , Decode(summbal.amount_type_id
1656 , 1, NVL(summbal.period_balance,0)
1657 , 0)
1658 , 0)))
1659 , 1, sum(
1660 DecodE(paobj.balance_type_code
1661 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1662 , Decode(summbal.amount_type_id
1663 , 1, NVL(summbal.period_balance,0)
1664 , 0)
1665 , 0))
1666 , 1)
1667 )
1668 , PA_REP_UTIL_SCREEN.calculate_capacity(
1669 PA_REP_UTIL_GLOB.GetOrgId
1670 , PA_REP_UTIL_GLOB.GetBalTypeActuals
1671 , max(paobj.person_id)
1672 , max(summbal.version_id)
1673 , PA_REP_UTIL_GLOB.GetPeriodTypeQr --Bug 8528649 Start changes for Quarter
1674 , null
1675 , null
1676 , null
1677 , 1
1678 , 9
1679 , 10
1680 , p_Show_Percentage_By
1681 , p_Organization_ID
1682 , p_Period_Year
1683 , p_Period_Quarter) --Bug 8528649 End changes for Quarter
1684 ) AS ACTUALS_CAP_OR_TOT_HRS
1685 /*
1686 * Field below is for FORECAST_CAPACITY
1687 */
1688 ,DECODE( p_Utilization_Category_ID, 0,(
1689 DECODE(
1690 sign(
1691 sum(
1692 DecodE(paobj.balance_type_code
1693 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1694 , Decode(summbal.amount_type_id
1695 , 9, NVL(summbal.period_balance,0)
1696 , 0)
1697 , 0))
1698 -sum(
1699 DecodE(paobj.balance_type_code
1700 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1701 , Decode(summbal.amount_type_id
1702 , 10, NVL(summbal.period_balance,0)
1703 , 0)
1704 , 0)))
1705 , 1,
1706 (sum(
1707 DecodE(paobj.balance_type_code
1708 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1709 , Decode(summbal.amount_type_id
1710 , 9, NVL(summbal.period_balance,0)
1711 , 0)
1712 , 0))
1713 -sum(
1714 DecodE(paobj.balance_type_code
1715 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1716 , Decode(summbal.amount_type_id
1717 , 10, NVL(summbal.period_balance,0)
1718 , 0)
1719 , 0)))
1720 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1721 PA_REP_UTIL_GLOB.GetOrgId
1722 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1723 , max(paobj.person_id)
1724 , max(summbal.version_id)
1725 , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1726 , null
1727 , null
1728 , null
1729 , 1
1730 , 9
1731 , 10
1732 , p_Show_Percentage_By
1733 , p_organization_id
1734 , max(summbal.period_year)
1735 , max(quarter_or_month_number))) AS FORECAST_CAPACITY
1736 /*
1737 * Field below is for FORECAST_HOURS
1738 */
1739 , sum(
1740 DECODE(paobj.balance_type_code
1741 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1742 , Decode(p_Assignment_Status
1743 , 'ALL'
1744 , decode(summbal.amount_type_id
1745 , 1, NVL(summbal.period_balance,0)
1746 , 0)
1747 , 'PROVISIONAL'
1748 , decode(summbal.amount_type_id
1749 , 4, NVL(summbal.period_balance,0)
1750 , 0)
1751 , 'CONFIRMED'
1752 , decode(summbal.amount_type_id
1753 , 1, NVL(summbal.period_balance,0)
1754 , 0)
1755 , 0)
1756 , 0))
1757 -sum(
1758 DECODE(paobj.balance_type_code
1759 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1760 , Decode(p_Assignment_Status
1761 , 'CONFIRMED'
1762 , decode(summbal.amount_type_id
1763 , 4, NVL(summbal.period_balance,0)
1764 , 0)
1765 , 0)
1766 , 0)) AS FORECAST_HOURS
1767 /*
1768 * Field below is for FORECAST_WEIGHTED_HOURS
1769 */
1770 , (sum(
1771 DECODE(paobj.balance_type_code
1772 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1773 , DecodE(p_Assignment_Status
1774 , 'ALL'
1775 , Decode(p_Utilization_Method
1776 , 'ORGANIZATION'
1777 , decode(summbal.amount_type_id
1778 , 2, NVL(summbal.period_balance,0)
1779 , 0)
1780 , 'RESOURCE'
1781 , decode(summbal.amount_type_id
1782 , 3, NVL(summbal.period_balance,0)
1783 , 0))
1784 , 'PROVISIONAL'
1785 , Decode(p_Utilization_Method
1786 , 'ORGANIZATION'
1787 , decode(summbal.amount_type_id
1788 , 5, NVL(summbal.period_balance,0)
1789 , 0)
1790 , 'RESOURCE'
1791 , decode(summbal.amount_type_id
1792 , 6, NVL(summbal.period_balance,0)
1793 , 0))
1794 , 'CONFIRMED'
1795 , Decode(p_Utilization_Method
1796 , 'ORGANIZATION'
1797 , decode(summbal.amount_type_id
1798 , 2, NVL(summbal.period_balance,0)
1799 , 0)
1800 , 'RESOURCE'
1801 , decode(summbal.amount_type_id
1802 , 3, NVL(summbal.period_balance,0)
1803 , 0))
1804 )
1805 , 0))
1806 -sum(
1807 DECODE(paobj.balance_type_code
1808 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1809 , Decode(p_Assignment_Status
1810 , 'CONFIRMED'
1811 , Decode(p_Utilization_Method
1812 , 'ORGANIZATION'
1813 , decode(summbal.amount_type_id
1814 , 5, NVL(summbal.period_balance,0)
1815 , 0)
1816 , 'RESOURCE'
1817 , decode(summbal.amount_type_id
1818 , 6, NVL(summbal.period_balance,0)
1819 , 0))
1820 , 0)
1821 , 0))) AS FORECAST_WEIGHTED_HOURS
1822 /*
1823 * Field below is for FORECAST_WEIGHTED_HOURS_P
1824 */
1825 ,ROUND(NVL(
1826 (sum(
1827 DECODE(paobj.balance_type_code
1828 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1829 , DecodE(p_Assignment_Status
1830 , 'ALL'
1831 , Decode(p_Utilization_Method
1832 , 'ORGANIZATION'
1833 , decode(summbal.amount_type_id
1834 , 2, NVL(summbal.period_balance,0)
1835 , 0)
1836 , 'RESOURCE'
1837 , decode(summbal.amount_type_id
1838 , 3, NVL(summbal.period_balance,0)
1839 , 0))
1840 , 'PROVISIONAL'
1841 , Decode(p_Utilization_Method
1842 , 'ORGANIZATION'
1843 , decode(summbal.amount_type_id
1844 , 5, NVL(summbal.period_balance,0)
1845 , 0)
1846 , 'RESOURCE'
1847 , decode(summbal.amount_type_id
1848 , 6, NVL(summbal.period_balance,0)
1849 , 0))
1850 , 'CONFIRMED'
1851 , Decode(p_Utilization_Method
1852 , 'ORGANIZATION'
1853 , decode(summbal.amount_type_id
1854 , 2, NVL(summbal.period_balance,0)
1855 , 0)
1856 , 'RESOURCE'
1857 , decode(summbal.amount_type_id
1858 , 3, NVL(summbal.period_balance,0)
1859 , 0))
1860 )
1861 , 0))
1862 -sum(
1863 DECODE(paobj.balance_type_code
1864 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1865 , Decode(p_Assignment_Status
1866 , 'CONFIRMED'
1867 , Decode(p_Utilization_Method
1868 , 'ORGANIZATION'
1869 , decode(summbal.amount_type_id
1870 , 5, NVL(summbal.period_balance,0)
1871 , 0)
1872 , 'RESOURCE'
1873 , decode(summbal.amount_type_id
1874 , 6, NVL(summbal.period_balance,0)
1875 , 0))
1876 , 0)
1877 , 0)))
1878 *100/
1879 DECODE(p_Utilization_Category_Id
1880 ,0 , DECODE(p_Show_Percentage_By
1881 , 'CAPACITY'
1882 , DECODE(
1883 sign(
1884 sum(
1885 DecodE(paobj.balance_type_code
1886 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1887 , Decode(summbal.amount_type_id
1888 , 9, NVL(summbal.period_balance,0)
1889 , 0)
1890 , 0))
1891 -sum(
1892 DecodE(paobj.balance_type_code
1893 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1894 , Decode(summbal.amount_type_id
1895 , 10, NVL(summbal.period_balance,0)
1896 , 0)
1897 , 0)))
1898 , 1,
1899 (sum(
1900 DecodE(paobj.balance_type_code
1901 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1902 , Decode(summbal.amount_type_id
1903 , 9, NVL(summbal.period_balance,0)
1904 , 0)
1905 , 0))
1906 -sum(
1907 DecodE(paobj.balance_type_code
1908 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1909 , Decode(summbal.amount_type_id
1910 , 10, NVL(summbal.period_balance,0)
1911 , 0)
1912 , 0)))
1913 ,1)
1914 , 'TOTAL_WORKED_HOURS'
1915 , DECODE(
1916 sign(
1917 sum(
1918 DecodE(paobj.balance_type_code
1919 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1920 , Decode(summbal.amount_type_id
1921 , 1, NVL(summbal.period_balance,0)
1922 , 0)
1923 , 0)))
1924 , 1, sum(
1925 DecodE(paobj.balance_type_code
1926 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1927 , Decode(summbal.amount_type_id
1928 , 1, NVL(summbal.period_balance,0)
1929 , 0)
1930 , 0))
1931 , 1)
1932 )
1933 , PA_REP_UTIL_SCREEN.calculate_capacity(
1934 PA_REP_UTIL_GLOB.GetOrgId
1935 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1936 , max(paobj.person_id)
1937 , max(summbal.version_id)
1938 , PA_REP_UTIL_GLOB.GetPeriodTypeQr --Bug 8528649 Start changes for Quarter
1939 , null
1940 , null
1941 , null
1942 , 1
1943 , 9
1944 , 10
1945 , p_Show_Percentage_By
1946 , p_Organization_ID
1947 , p_Period_Year
1948 , p_Period_Quarter) --Bug 8528649 End changes for Quarter
1949 )
1950 , -9999) -- finished NVL
1951 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding and concatenation
1952 /*
1953 * Field below is for FORECAST_CAP_OR_TOT_HRS
1954 */
1955 , DECODE(p_Utilization_Category_Id
1956 ,0 , DECODE(p_Show_Percentage_By
1957 , 'CAPACITY'
1958 , DECODE(
1959 sign(
1960 sum(
1961 DecodE(paobj.balance_type_code
1962 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1963 , Decode(summbal.amount_type_id
1964 , 9, NVL(summbal.period_balance,0)
1965 , 0)
1966 , 0))
1967 -sum(
1968 DecodE(paobj.balance_type_code
1969 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1970 , Decode(summbal.amount_type_id
1971 , 10, NVL(summbal.period_balance,0)
1972 , 0)
1973 , 0)))
1974 , 1,
1975 (sum(
1976 DecodE(paobj.balance_type_code
1977 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1978 , Decode(summbal.amount_type_id
1979 , 9, NVL(summbal.period_balance,0)
1980 , 0)
1981 , 0))
1982 -sum(
1983 DecodE(paobj.balance_type_code
1984 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1985 , Decode(summbal.amount_type_id
1986 , 10, NVL(summbal.period_balance,0)
1987 , 0)
1988 , 0)))
1989 ,1)
1990 , 'TOTAL_WORKED_HOURS'
1991 , DECODE(
1992 sign(
1993 sum(
1994 DecodE(paobj.balance_type_code
1995 , PA_REP_UTIL_GLOB.GetBalTypeForecast
1996 , Decode(summbal.amount_type_id
1997 , 1, NVL(summbal.period_balance,0)
1998 , 0)
1999 , 0)))
2000 , 1, sum(
2001 DecodE(paobj.balance_type_code
2002 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2003 , Decode(summbal.amount_type_id
2004 , 1, NVL(summbal.period_balance,0)
2005 , 0)
2006 , 0))
2007 , 1)
2008 )
2009 , PA_REP_UTIL_SCREEN.calculate_capacity(
2010 PA_REP_UTIL_GLOB.GetOrgId
2011 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2012 , max(paobj.person_id)
2013 , max(summbal.version_id)
2014 , PA_REP_UTIL_GLOB.GetPeriodTypeQr --Bug 8528649 Start changes for Quarter
2015 , null
2016 , null
2017 , null
2018 , 1
2019 , 9
2020 , 10
2021 , p_Show_Percentage_By
2022 , p_Organization_ID
2023 , p_Period_Year
2024 , p_Period_Quarter) --Bug 8528649 End changes for Quarter
2025 ) AS FORECAST_CAP_OR_TOT_HRS
2026 from
2027 PA_Summ_Balances summbal
2028 , PA_Objects paobj
2029 , pa_resources_denorm resdnorm
2030 , gl_periods glprd
2031 , pa_lookups lkup
2032 where
2033 lkup.lookup_type = 'PERSON_TYPE'
2034 AND lkup.lookup_code = 'EMPLOYEE'
2035 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
2036 AND (
2037 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2038 and p_calling_mode = 'ORGMGR')
2039 OR
2040 /* Bug 2003821: start */
2041 (
2042 (
2043 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,trunc(sysdate)))+0.99999
2044 and glprd.start_date <=sysdate) /* Added for Bug 2325539 */
2045 OR
2046 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2047 and glprd.start_date > sysdate)
2048 )
2049 and p_calling_mode = 'RESMGR'
2050 )
2051 )
2052 /* Bug 2003821: end */
2053 AND glprd.period_set_name = summbal.period_set_name
2054 AND glprd.period_name = summbal.period_name
2055 AND summbal.object_id = paobj.object_id
2056 AND summbal.version_id = -1
2057 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2058 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2059 AND summbal.period_year = p_Period_Year
2060 AND summbal.quarter_or_month_number = p_Period_Quarter
2061 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2062 , 2 /* G_RES_WTDHRS_ORG_C */
2063 , 3 /* G_RES_WTDHRS_PEOPLE_C */
2064 , 4 /* G_RES_PRVHRS_C */
2065 , 5 /* G_RES_PRVWTDHRS_ORG_C */
2066 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
2067 , 9 /* G_RES_CAP_C */
2068 ,10 /* G_RES_REDUCEDCAP_C */
2069 )
2070 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2071 , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2072 , Decode(p_Utilization_Method
2073 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2074 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2075 AND paobj.object_type_code = summbal.object_type_code
2076 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2077 AND paobj.project_org_id = -1
2078 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2079 AND paobj.project_organization_id = -1
2080 AND paobj.project_id = -1
2081 AND paobj.task_id = -1
2082 AND paobj.person_id = resdnorm.person_id
2083 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
2084 AND paobj.assignment_id = -1
2085 AND paobj.work_type_id = -1
2086 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
2087 , 0, -1
2088 , Decode(p_Utilization_Method
2089 , 'ORGANIZATION', p_Utilization_Category_Id
2090 , 'RESOURCE', -1))
2091 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
2092 , 0, -1
2093 , Decode(p_Utilization_Method
2094 , 'ORGANIZATION', -1
2095 , 'RESOURCE', p_Utilization_Category_Id))
2096 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2097 group by
2098 DECODE(p_calling_mode
2099 , 'ORGMGR', paobj.expenditure_organization_id
2100 , 'RESMGR', NULL
2101 )
2102 , resdnorm.person_id
2103 , resdnorm.resource_id
2104 ;
2105 END IF;
2106
2107 /*
2108 * END of Case 3 for U2
2109 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr
2110 */
2111 /*
2112 * BEGINNING of Case 4 for U2
2113 * YR view
2114 */
2115 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
2116
2117 INSERT INTO PA_REP_UTIL_SCREEN_TMP (
2118 Organization_id
2119 , Person_id
2120 , Resource_id
2121 , Resource_Name
2122 , Resource_Type
2123 , Resource_Type_Code
2124 , Calling_Mode
2125 , Job_Level
2126 , Actuals_Capacity
2127 , Actuals_hours
2128 , Actuals_Weighted_hours
2129 , Actuals_Weighted_hours_P
2130 , Actuals_Cap_OR_Tot_Hrs
2131 , Forecast_Capacity
2132 , Forecast_hours
2133 , Forecast_Weighted_hours
2134 , Forecast_Weighted_hours_P
2135 , Forecast_Cap_OR_Tot_Hrs
2136 )
2137 SELECT
2138 DECODE(p_calling_mode
2139 , 'ORGMGR', paobj.expenditure_organization_id
2140 , 'RESMGR', NULL
2141 ) AS ORGANIZATION_ID
2142 , resdnorm.person_id AS PERSON_ID
2143 , resdnorm.resource_id AS RESOURCE_ID
2144 , max(resdnorm.resource_name) AS RESOURCE_NAME
2145 , max(lkup.meaning) AS RESOURCE_TYPE
2146 , max(resdnorm.resource_type) AS RESOURCE_TYPE_CODE
2147 , p_Calling_Mode AS CALLING_MODE
2148 , max(resdnorm.resource_job_level) AS JOB_LEVEL
2149 /*
2150 * Field below is for ACTUALS_CAPACITY
2151 */
2152 ,DECODE( p_Utilization_Category_ID, 0,(
2153 DECODE(
2154 sign(
2155 sum(
2156 DecodE(paobj.balance_type_code
2157 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2158 , Decode(summbal.amount_type_id
2159 , 9, NVL(summbal.period_balance,0)
2160 , 0)
2161 , 0))
2162 -sum(
2163 DecodE(paobj.balance_type_code
2164 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2165 , Decode(summbal.amount_type_id
2166 , 10, NVL(summbal.period_balance,0)
2167 , 0)
2168 , 0)))
2169 , 1,
2170 (sum(
2171 DecodE(paobj.balance_type_code
2172 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2173 , Decode(summbal.amount_type_id
2174 , 9, NVL(summbal.period_balance,0)
2175 , 0)
2176 , 0))
2177 -sum(
2178 DecodE(paobj.balance_type_code
2179 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2180 , Decode(summbal.amount_type_id
2181 , 10, NVL(summbal.period_balance,0)
2182 , 0)
2183 , 0)))
2184 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2185 PA_REP_UTIL_GLOB.GetOrgId
2186 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2187 , max(paobj.person_id)
2188 , max(summbal.version_id)
2189 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2190 , null
2191 , null
2192 , null
2193 , 1
2194 , 9
2195 , 10
2196 , p_Show_Percentage_By
2197 , p_organization_id
2198 , max(summbal.period_year))) AS ACTUALS_CAPACITY
2199 /*
2200 * Field below is for ACTUALS_HOURS
2201 */
2202 , sum(
2203 DECODE(paobj.balance_type_code
2204 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2205 , Decode(summbal.amount_type_id
2206 , 1, NVL(summbal.period_balance,0)
2207 , 0)
2208 , 0)) AS ACTUALS_HOURS
2209 /*
2210 * Field below is for ACTUALS_WEIGHTED_HOURS
2211 */
2212 , sum(
2213 DECODE(paobj.balance_type_code
2214 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2215 , Decode(p_utilization_method
2216 , 'ORGANIZATION'
2217 , decode(summbal.amount_type_id
2218 , 2, NVL(summbal.period_balance,0)
2219 , 0)
2220 , 'RESOURCE'
2221 , decode(summbal.amount_type_id
2222 , 3, NVL(summbal.period_balance,0)
2223 , 0)
2224 )
2225 , 0)) AS ACTUALS_WEIGHTED_HOURS
2226 /*
2227 * Field below is for ACTUALS_WEIGHTED_HOURS_P
2228 */
2229 ,ROUND(NVL(sum(
2230 DECODE(paobj.balance_type_code
2231 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2232 , Decode(p_utilization_method
2233 , 'ORGANIZATION'
2234 , decode(summbal.amount_type_id
2235 , 2, NVL(summbal.period_balance,0)
2236 , 0)
2237 , 'RESOURCE'
2238 , decode(summbal.amount_type_id
2239 , 3, NVL(summbal.period_balance,0)
2240 , 0)
2241 )
2242 , 0))*100/
2243 DECODE(p_Utilization_Category_Id
2244 ,0 , DECODE(p_Show_Percentage_By
2245 , 'CAPACITY'
2246 , DECODE(
2247 sign(
2248 sum(
2249 DecodE(paobj.balance_type_code
2250 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2251 , Decode(summbal.amount_type_id
2252 , 9, NVL(summbal.period_balance,0)
2253 , 0)
2254 , 0))
2255 -sum(
2256 DecodE(paobj.balance_type_code
2257 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2258 , Decode(summbal.amount_type_id
2259 , 10, NVL(summbal.period_balance,0)
2260 , 0)
2261 , 0)))
2262 , 1,
2263 (sum(
2264 DecodE(paobj.balance_type_code
2265 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2266 , Decode(summbal.amount_type_id
2267 , 9, NVL(summbal.period_balance,0)
2268 , 0)
2269 , 0))
2270 -sum(
2271 DecodE(paobj.balance_type_code
2272 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2273 , Decode(summbal.amount_type_id
2274 , 10, NVL(summbal.period_balance,0)
2275 , 0)
2276 , 0)))
2277 ,1)
2278 , 'TOTAL_WORKED_HOURS'
2279 , DECODE(
2280 sign(
2281 sum(
2282 DecodE(paobj.balance_type_code
2283 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2284 , Decode(summbal.amount_type_id
2285 , 1, NVL(summbal.period_balance,0)
2286 , 0)
2287 , 0)))
2288 , 1, sum(
2289 DecodE(paobj.balance_type_code
2290 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2291 , Decode(summbal.amount_type_id
2292 , 1, NVL(summbal.period_balance,0)
2293 , 0)
2294 , 0))
2295 , 1)
2296 )
2297 , PA_REP_UTIL_SCREEN.calculate_capacity(
2298 PA_REP_UTIL_GLOB.GetOrgId
2299 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2300 , max(paobj.person_id)
2301 , max(summbal.version_id) /*8528649 changes for actuals_weighted_hours_p*/
2302 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2303 , NULL
2304 , NULL
2305 , NULL
2306 , 1
2307 , 9
2308 , 10
2309 , p_Show_Percentage_By
2310 , p_organization_id
2311 , p_period_year)
2312 )
2313 , -9999) -- finished NVL
2314 , 0) AS ACTUALS_WEIGHTED_HOURS_P -- finished rounding and concatenation
2315 /*
2316 * Field below is for ACTUALS_CAP_OR_TOT_HRS
2317 */
2318 , DECODE(p_Utilization_Category_Id
2319 ,0 , DECODE(p_Show_Percentage_By
2320 , 'CAPACITY'
2321 , DECODE(
2322 sign(
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 (sum(
2339 DecodE(paobj.balance_type_code
2340 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2341 , Decode(summbal.amount_type_id
2342 , 9, NVL(summbal.period_balance,0)
2343 , 0)
2344 , 0))
2345 -sum(
2346 DecodE(paobj.balance_type_code
2347 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2348 , Decode(summbal.amount_type_id
2349 , 10, NVL(summbal.period_balance,0)
2350 , 0)
2351 , 0)))
2352 ,1)
2353 , 'TOTAL_WORKED_HOURS'
2354 , DECODE(
2355 sign(
2356 sum(
2357 DecodE(paobj.balance_type_code
2358 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2359 , Decode(summbal.amount_type_id
2360 , 1, NVL(summbal.period_balance,0)
2361 , 0)
2362 , 0)))
2363 , 1, sum(
2364 DecodE(paobj.balance_type_code
2365 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2366 , Decode(summbal.amount_type_id
2367 , 1, NVL(summbal.period_balance,0)
2368 , 0)
2369 , 0))
2370 , 1)
2371 )
2372 , PA_REP_UTIL_SCREEN.calculate_capacity(
2373 PA_REP_UTIL_GLOB.GetOrgId
2374 , PA_REP_UTIL_GLOB.GetBalTypeActuals
2375 , max(paobj.person_id)
2376 , max(summbal.version_id) /*8528649 changes for ACTUALS_CAP_OR_TOT_HRS*/
2377 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2378 , NULL
2379 , NULL
2380 , NULL
2381 , 1
2382 , 9
2383 , 10
2384 , p_Show_Percentage_By
2385 , p_organization_id
2386 ,p_period_year)
2387 ) AS ACTUALS_CAP_OR_TOT_HRS
2388 /*
2389 * Field below is for FORECAST_CAPACITY
2390 */
2391 ,DECODE( p_Utilization_Category_ID, 0,(
2392 DECODE(
2393 sign(
2394 sum(
2395 DecodE(paobj.balance_type_code
2396 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2397 , Decode(summbal.amount_type_id
2398 , 9, NVL(summbal.period_balance,0)
2399 , 0)
2400 , 0))
2401 -sum(
2402 DecodE(paobj.balance_type_code
2403 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2404 , Decode(summbal.amount_type_id
2405 , 10, NVL(summbal.period_balance,0)
2406 , 0)
2407 , 0)))
2408 , 1,
2409 (sum(
2410 DecodE(paobj.balance_type_code
2411 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2412 , Decode(summbal.amount_type_id
2413 , 9, NVL(summbal.period_balance,0)
2414 , 0)
2415 , 0))
2416 -sum(
2417 DecodE(paobj.balance_type_code
2418 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2419 , Decode(summbal.amount_type_id
2420 , 10, NVL(summbal.period_balance,0)
2421 , 0)
2422 , 0)))
2423 ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2424 PA_REP_UTIL_GLOB.GetOrgId
2425 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2426 , max(paobj.person_id)
2427 , max(summbal.version_id)
2428 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2429 , null
2430 , null
2431 , null
2432 , 1
2433 , 9
2434 , 10
2435 , p_Show_Percentage_By
2436 , p_organization_id
2437 , max(summbal.period_year))) AS FORECAST_CAPACITY
2438 /*
2439 * Field below is for FORECAST_HOURS
2440 */
2441 , sum(
2442 DECODE(paobj.balance_type_code
2443 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2444 , Decode(p_Assignment_Status
2445 , 'ALL'
2446 , decode(summbal.amount_type_id
2447 , 1, NVL(summbal.period_balance,0)
2448 , 0)
2449 , 'PROVISIONAL'
2450 , decode(summbal.amount_type_id
2451 , 4, NVL(summbal.period_balance,0)
2452 , 0)
2453 , 'CONFIRMED'
2454 , decode(summbal.amount_type_id
2455 , 1, NVL(summbal.period_balance,0)
2456 , 0)
2457 , 0)
2458 , 0))
2459 -sum(
2460 DECODE(paobj.balance_type_code
2461 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2462 , Decode(p_Assignment_Status
2463 , 'CONFIRMED'
2464 , decode(summbal.amount_type_id
2465 , 4, NVL(summbal.period_balance,0)
2466 , 0)
2467 , 0)
2468 , 0)) AS FORECAST_HOURS
2469 /*
2470 * Field below is for FORECAST_WEIGHTED_HOURS
2471 */
2472 , (sum(
2473 DECODE(paobj.balance_type_code
2474 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2475 , DecodE(p_Assignment_Status
2476 , 'ALL'
2477 , Decode(p_Utilization_Method
2478 , 'ORGANIZATION'
2479 , decode(summbal.amount_type_id
2480 , 2, NVL(summbal.period_balance,0)
2481 , 0)
2482 , 'RESOURCE'
2483 , decode(summbal.amount_type_id
2484 , 3, NVL(summbal.period_balance,0)
2485 , 0))
2486 , 'PROVISIONAL'
2487 , Decode(p_Utilization_Method
2488 , 'ORGANIZATION'
2489 , decode(summbal.amount_type_id
2490 , 5, NVL(summbal.period_balance,0)
2491 , 0)
2492 , 'RESOURCE'
2493 , decode(summbal.amount_type_id
2494 , 6, NVL(summbal.period_balance,0)
2495 , 0))
2496 , 'CONFIRMED'
2497 , Decode(p_Utilization_Method
2498 , 'ORGANIZATION'
2499 , decode(summbal.amount_type_id
2500 , 2, NVL(summbal.period_balance,0)
2501 , 0)
2502 , 'RESOURCE'
2503 , decode(summbal.amount_type_id
2504 , 3, NVL(summbal.period_balance,0)
2505 , 0))
2506 )
2507 , 0))
2508 -sum(
2509 DECODE(paobj.balance_type_code
2510 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2511 , Decode(p_Assignment_Status
2512 , 'CONFIRMED'
2513 , Decode(p_Utilization_Method
2514 , 'ORGANIZATION'
2515 , decode(summbal.amount_type_id
2516 , 5, NVL(summbal.period_balance,0)
2517 , 0)
2518 , 'RESOURCE'
2519 , decode(summbal.amount_type_id
2520 , 6, NVL(summbal.period_balance,0)
2521 , 0))
2522 , 0)
2523 , 0))) AS FORECAST_WEIGHTED_HOURS
2524 /*
2525 * Field below is for FORECAST_WEIGHTED_HOURS_P
2526 */
2527 ,ROUND(NVL(
2528 (sum(
2529 DECODE(paobj.balance_type_code
2530 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2531 , DecodE(p_Assignment_Status
2532 , 'ALL'
2533 , Decode(p_Utilization_Method
2534 , 'ORGANIZATION'
2535 , decode(summbal.amount_type_id
2536 , 2, NVL(summbal.period_balance,0)
2537 , 0)
2538 , 'RESOURCE'
2539 , decode(summbal.amount_type_id
2540 , 3, NVL(summbal.period_balance,0)
2541 , 0))
2542 , 'PROVISIONAL'
2543 , Decode(p_Utilization_Method
2544 , 'ORGANIZATION'
2545 , decode(summbal.amount_type_id
2546 , 5, NVL(summbal.period_balance,0)
2547 , 0)
2548 , 'RESOURCE'
2549 , decode(summbal.amount_type_id
2550 , 6, NVL(summbal.period_balance,0)
2551 , 0))
2552 , 'CONFIRMED'
2553 , Decode(p_Utilization_Method
2554 , 'ORGANIZATION'
2555 , decode(summbal.amount_type_id
2556 , 2, NVL(summbal.period_balance,0)
2557 , 0)
2558 , 'RESOURCE'
2559 , decode(summbal.amount_type_id
2560 , 3, NVL(summbal.period_balance,0)
2561 , 0))
2562 )
2563 , 0))
2564 -sum(
2565 DECODE(paobj.balance_type_code
2566 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2567 , Decode(p_Assignment_Status
2568 , 'CONFIRMED'
2569 , Decode(p_Utilization_Method
2570 , 'ORGANIZATION'
2571 , decode(summbal.amount_type_id
2572 , 5, NVL(summbal.period_balance,0)
2573 , 0)
2574 , 'RESOURCE'
2575 , decode(summbal.amount_type_id
2576 , 6, NVL(summbal.period_balance,0)
2577 , 0))
2578 , 0)
2579 , 0)))
2580 *100/
2581 DECODE(p_Utilization_Category_Id
2582 ,0 , DECODE(p_Show_Percentage_By
2583 , 'CAPACITY'
2584 , DECODE(
2585 sign(
2586 sum(
2587 DecodE(paobj.balance_type_code
2588 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2589 , Decode(summbal.amount_type_id
2590 , 9, NVL(summbal.period_balance,0)
2591 , 0)
2592 , 0))
2593 -sum(
2594 DecodE(paobj.balance_type_code
2595 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2596 , Decode(summbal.amount_type_id
2597 , 10, NVL(summbal.period_balance,0)
2598 , 0)
2599 , 0)))
2600 , 1,
2601 (sum(
2602 DecodE(paobj.balance_type_code
2603 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2604 , Decode(summbal.amount_type_id
2605 , 9, NVL(summbal.period_balance,0)
2606 , 0)
2607 , 0))
2608 -sum(
2609 DecodE(paobj.balance_type_code
2610 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2611 , Decode(summbal.amount_type_id
2612 , 10, NVL(summbal.period_balance,0)
2613 , 0)
2614 , 0)))
2615 ,1)
2616 , 'TOTAL_WORKED_HOURS'
2617 , DECODE(
2618 sign(
2619 sum(
2620 DecodE(paobj.balance_type_code
2621 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2622 , Decode(summbal.amount_type_id
2623 , 1, NVL(summbal.period_balance,0)
2624 , 0)
2625 , 0)))
2626 , 1, sum(
2627 DecodE(paobj.balance_type_code
2628 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2629 , Decode(summbal.amount_type_id
2630 , 1, NVL(summbal.period_balance,0)
2631 , 0)
2632 , 0))
2633 , 1)
2634 )
2635 , PA_REP_UTIL_SCREEN.calculate_capacity(
2636 PA_REP_UTIL_GLOB.GetOrgId
2637 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2638 , max(paobj.person_id)
2639 , max(summbal.version_id) /*8528649 changes for FORECAST_WEIGHTED_HOURS_P*/
2640 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2641 , NULL
2642 , NULL
2643 , NULL
2644 , 1
2645 , 9
2646 , 10
2647 , p_Show_Percentage_By
2648 , p_organization_id
2649 , p_period_year)
2650 )
2651 , -9999) -- finished NVL
2652 , 0) AS FORECAST_WEIGHTED_HOURS_P -- finished rounding and concatenation
2653 /*
2654 * Field below is for FORECAST_CAP_OR_TOT_HRS
2655 */
2656 , DECODE(p_Utilization_Category_Id
2657 ,0 , DECODE(p_Show_Percentage_By
2658 , 'CAPACITY'
2659 , DECODE(
2660 sign(
2661 sum(
2662 DecodE(paobj.balance_type_code
2663 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2664 , Decode(summbal.amount_type_id
2665 , 9, NVL(summbal.period_balance,0)
2666 , 0)
2667 , 0))
2668 -sum(
2669 DecodE(paobj.balance_type_code
2670 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2671 , Decode(summbal.amount_type_id
2672 , 10, NVL(summbal.period_balance,0)
2673 , 0)
2674 , 0)))
2675 , 1,
2676 (sum(
2677 DecodE(paobj.balance_type_code
2678 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2679 , Decode(summbal.amount_type_id
2680 , 9, NVL(summbal.period_balance,0)
2681 , 0)
2682 , 0))
2683 -sum(
2684 DecodE(paobj.balance_type_code
2685 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2686 , Decode(summbal.amount_type_id
2687 , 10, NVL(summbal.period_balance,0)
2688 , 0)
2689 , 0)))
2690 ,1)
2691 , 'TOTAL_WORKED_HOURS'
2692 , DECODE(
2693 sign(
2694 sum(
2695 DecodE(paobj.balance_type_code
2696 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2697 , Decode(summbal.amount_type_id
2698 , 1, NVL(summbal.period_balance,0)
2699 , 0)
2700 , 0)))
2701 , 1, sum(
2702 DecodE(paobj.balance_type_code
2703 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2704 , Decode(summbal.amount_type_id
2705 , 1, NVL(summbal.period_balance,0)
2706 , 0)
2707 , 0))
2708 , 1)
2709 )
2710 , PA_REP_UTIL_SCREEN.calculate_capacity(
2711 PA_REP_UTIL_GLOB.GetOrgId
2712 , PA_REP_UTIL_GLOB.GetBalTypeForecast
2713 , max(paobj.person_id)
2714 , max(summbal.version_id) /*8528649 changes for forecast_cap_or_tot_hrs*/
2715 , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2716 , NULL
2717 , NULL
2718 , NULL
2719 , 1
2720 , 9
2721 , 10
2722 , p_Show_Percentage_By
2723 , p_organization_id
2724 , p_period_year)
2725 ) AS FORECAST_CAP_OR_TOT_HRS
2726 from
2727 PA_Summ_Balances summbal
2728 , PA_Objects paobj
2729 , pa_resources_denorm resdnorm
2730 , gl_periods glprd
2731 , pa_lookups lkup
2732 where
2733 lkup.lookup_type = 'PERSON_TYPE'
2734 AND lkup.lookup_code = 'EMPLOYEE'
2735 AND NVL(resdnorm.manager_id,-1) = NVL(p_manager_id,NVL(resdnorm.manager_id,-1))
2736 AND (
2737 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2738 and p_calling_mode = 'ORGMGR')
2739 OR
2740 /* Bug 2003821: start */
2741 (
2742 (
2743 (trunc(SYSDATE) between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,trunc(sysdate)))+0.99999
2744 and glprd.start_date <=sysdate) /* Added for Bug 2325539 */
2745 OR
2746 (glprd.start_date between trunc(resdnorm.resource_effective_start_date) and trunc(NVL(resdnorm.resource_effective_end_date,glprd.end_date))+0.99999
2747 and glprd.start_date > sysdate)
2748 )
2749 and p_calling_mode = 'RESMGR'
2750 )
2751 )
2752 /* Bug 2003821: end */
2753 AND glprd.period_set_name = summbal.period_set_name
2754 AND glprd.period_name = summbal.period_name
2755 AND summbal.object_id = paobj.object_id
2756 AND summbal.version_id = -1
2757 AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2758 AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2759 AND summbal.period_year = p_Period_Year
2760 AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2761 , 2 /* G_RES_WTDHRS_ORG_C */
2762 , 3 /* G_RES_WTDHRS_PEOPLE_C */
2763 , 4 /* G_RES_PRVHRS_C */
2764 , 5 /* G_RES_PRVWTDHRS_ORG_C */
2765 , 6 /* G_RES_PRVWTDHRS_PEOPLE_C */
2766 , 9 /* G_RES_CAP_C */
2767 ,10 /* G_RES_REDUCEDCAP_C */
2768 )
2769 AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2770 , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2771 , Decode(p_Utilization_Method
2772 , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2773 , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2774 AND paobj.object_type_code = summbal.object_type_code
2775 AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2776 AND paobj.project_org_id = -1
2777 AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2778 AND paobj.project_organization_id = -1
2779 AND paobj.project_id = -1
2780 AND paobj.task_id = -1
2781 AND paobj.person_id = resdnorm.person_id
2782 AND resdnorm.utilization_flag = 'Y' /* Added for Bug#2765050 */
2783 AND paobj.assignment_id = -1
2784 AND paobj.work_type_id = -1
2785 AND paobj.org_util_category_id = DECODE(p_Utilization_Category_Id
2786 , 0, -1
2787 , Decode(p_Utilization_Method
2788 , 'ORGANIZATION', p_Utilization_Category_Id
2789 , 'RESOURCE', -1))
2790 AND paobj.res_util_category_id = DECODE(p_Utilization_Category_Id
2791 , 0, -1
2792 , Decode(p_Utilization_Method
2793 , 'ORGANIZATION', -1
2794 , 'RESOURCE', p_Utilization_Category_Id))
2795 AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2796 group by
2797 DECODE(p_calling_mode
2798 , 'ORGMGR', paobj.expenditure_organization_id
2799 , 'RESMGR', NULL
2800 )
2801 , resdnorm.person_id
2802 , resdnorm.resource_id
2803 ;
2804 END IF;
2805
2806
2807 /*
2808 * END of Case 4 for U2
2809 * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr
2810 */
2811
2812
2813 /*
2814 * Bug 1633069
2815 * Now to update the resource_type_code, added through bug 1633069,
2816 * to identify the resources reporting to the select manager who are also
2817 * in turn managers of other resources. The above select put the value of
2818 * of EMPLOYEE for all the records now check against pa_resources_denorm
2819 * to update the resource_type_code to MANAGERS appropriately.
2820 */
2821
2822 Update PA_REP_UTIL_SCREEN_TMP tmp
2823 Set (resource_type,resource_type_code) = (select lkup2.meaning,lkup2.lookup_code
2824 from pa_lookups lkup2
2825 where lkup2.lookup_type='PERSON_TYPE'
2826 and lkup2.lookup_code = 'MANAGER')
2827 Where exists (select prd.Person_id
2828 from pa_resources_denorm prd
2829 where prd.manager_id = tmp.person_id)
2830 ;
2831
2832
2833 EXCEPTION
2834
2835 WHEN OTHERS THEN
2836 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_REP_UTIL_SCREEN.poplt_screen_tmp_table'
2837 , p_procedure_name => PA_DEBUG.G_Err_Stack);
2838 RAISE;
2839
2840
2841 END poplt_screen_tmp_table; /* End of Procedure poplt_screen_tmp_table */
2842
2843 /*
2844 * Functions.
2845 */
2846
2847 /*
2848 * The function calculate_capacity is needed for the cases when
2849 * p_Utilization_Category_Id <> 0, ie the object_type_code is not RES. In
2850 * such cases the select will calculate the capacity from the current record
2851 * which is incorrect since capacity should ALWAYS be calculated using the
2852 * RES record. Thus for cases when the object_type_code <> RES the decode
2853 * has been coded such that the function calculate_capacity would be called.
2854 */
2855
2856 FUNCTION calculate_capacity(
2857 p_ORG_ID IN NUMBER
2858 , p_Balance_Type_Code IN VARCHAR2
2859 , p_Entity_ID IN NUMBER
2860 , p_Version_ID IN NUMBER
2861 , p_Period_Type IN VARCHAR2
2862 , p_Period_Set_Name IN VARCHAR2
2863 , p_Period_Name IN VARCHAR2
2864 , p_Global_Exp_Period_End_Date IN DATE
2865 , p_Amount_ID_Resource_Hours IN NUMBER
2866 , p_Amount_ID_Capacity IN NUMBER
2867 , p_Amount_ID_Reduced_Capacity IN NUMBER
2868 , p_Show_Percentage_By IN VARCHAR2
2869 , p_Organization_Id IN NUMBER DEFAULT NULL
2870 , p_Period_Year IN NUMBER DEFAULT NULL
2871 , p_Quarter_Or_Month_Number IN NUMBER DEFAULT NULL
2872 )
2873 RETURN NUMBER
2874 IS
2875 v_total_hours NUMBER := 0;
2876 v_derived_cap NUMBER := 0;
2877 v_raw_cap NUMBER := 0;
2878 v_raw_reduced_cap NUMBER := 0;
2879 v_amount_type_id NUMBER := 0;
2880 v_period_balance NUMBER := 0;
2881 TYPE t_cap_rec IS REF CURSOR;
2882 c_cap_rec t_cap_rec;
2883 BEGIN
2884 IF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeQr THEN
2885 open c_cap_rec for
2886 select
2887 summbal2.amount_type_id AS amount_type_id
2888 , sum(summbal2.period_balance) AS period_balance
2889 from
2890 PA_Summ_Balances summbal2
2891 , PA_Objects paobj2
2892 where
2893 paobj2.Balance_Type_Code = p_Balance_Type_Code
2894 AND paobj2.expenditure_org_id = p_ORG_ID
2895 AND summbal2.version_id = p_Version_ID
2896 AND (( summbal2.object_type_code =
2897 PA_REP_UTIL_GLOB.GetObjectTypeRes
2898 AND paobj2.person_id = p_Entity_ID
2899 AND paobj2.expenditure_organization_id =
2900 nvl(p_organization_id,paobj2.expenditure_organization_id))
2901 OR
2902 ( summbal2.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg
2903 AND paobj2.expenditure_organization_id = p_Entity_ID)
2904 )
2905 AND summbal2.object_id = paobj2.object_id
2906 AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2907 AND period_year = p_Period_Year
2908 AND quarter_or_month_number = p_Quarter_Or_Month_Number
2909 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2910 group by
2911 summbal2.amount_type_id;
2912
2913 ELSIF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeYr THEN
2914 open c_cap_rec for
2915 select
2916 summbal2.amount_type_id AS amount_type_id
2917 , sum(summbal2.period_balance) AS period_balance
2918 from
2919 PA_Summ_Balances summbal2
2920 , PA_Objects paobj2
2921 where
2922 paobj2.Balance_Type_Code = p_Balance_Type_Code
2923 AND paobj2.expenditure_org_id = p_ORG_ID
2924 AND summbal2.version_id = p_Version_ID
2925 AND (( summbal2.object_type_code =
2926 PA_REP_UTIL_GLOB.GetObjectTypeRes
2927 AND paobj2.person_id = p_Entity_ID
2928 AND paobj2.expenditure_organization_id =
2929 nvl(p_organization_id,paobj2.expenditure_organization_id))
2930 OR
2931 ( summbal2.object_type_code =
2932 PA_REP_UTIL_GLOB.GetObjectTypeOrg
2933 AND paobj2.expenditure_organization_id = p_Entity_ID)
2934 )
2935 AND summbal2.object_id = paobj2.object_id
2936 AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2937 AND period_year = p_Period_Year
2938 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2939 group by
2940 summbal2.amount_type_id;
2941 ELSE
2942 open c_cap_rec for
2943 select
2944 summbal2.amount_type_id AS amount_type_id
2945 , sum(summbal2.period_balance) AS period_balance
2946 from
2947 PA_Summ_Balances summbal2
2948 , PA_Objects paobj2
2949 where
2950 paobj2.Balance_Type_Code = p_Balance_Type_Code
2951 AND paobj2.expenditure_org_id = p_ORG_ID
2952 AND summbal2.version_id = p_Version_ID
2953 AND (( summbal2.object_type_code =
2954 PA_REP_UTIL_GLOB.GetObjectTypeRes
2955 AND paobj2.person_id = p_Entity_ID
2956 AND paobj2.expenditure_organization_id =
2957 nvl(p_organization_id,paobj2.expenditure_organization_id))
2958 OR
2959 ( summbal2.object_type_code =
2960 PA_REP_UTIL_GLOB.GetObjectTypeOrg
2961 AND paobj2.expenditure_organization_id = p_Entity_ID)
2962 )
2963 AND summbal2.object_id = paobj2.object_id
2964 AND summbal2.period_type = p_Period_Type
2965 AND summbal2.period_set_name = p_Period_Set_Name
2966 AND summbal2.period_name = p_Period_Name
2967 AND summbal2.global_exp_period_end_date =
2968 p_Global_Exp_Period_End_Date
2969 AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2970 group by
2971 summbal2.amount_type_id;
2972
2973 END IF;
2974 LOOP
2975 fetch c_cap_rec into v_amount_type_id,v_period_balance;
2976 IF (v_amount_type_id=p_Amount_ID_Capacity) THEN
2977 v_raw_cap := v_period_balance;
2978 ELSIF (v_amount_type_id=p_Amount_ID_Reduced_Capacity) THEN
2979 v_raw_reduced_cap := v_period_balance;
2980 ELSIF (v_amount_type_id=p_Amount_ID_Resource_Hours) THEN
2981 v_total_hours := v_period_balance;
2982 END IF;
2983 EXIT WHEN c_cap_rec%NOTFOUND;
2984 END LOOP;
2985
2986 v_derived_cap := NVL(v_raw_cap,0) - NVL(v_raw_reduced_cap,0);
2987
2988 IF NVL(v_derived_cap,-1) <= 0 THEN
2989 v_derived_cap := 1;
2990 END IF;
2991
2992 IF NVL(v_total_hours,-1) <= 0 THEN
2993 v_total_hours := 1;
2994 END IF;
2995
2996 IF (p_Show_Percentage_By='CAPACITY') THEN
2997 return v_derived_cap;
2998 ELSE
2999 return v_total_hours;
3000 END IF;
3001
3002 END calculate_capacity;
3003
3004 PROCEDURE poplt_u1_screen_tmp_table(
3005 p_Organization_ID IN NUMBER
3006 , p_Period_Type IN VARCHAR2
3007 , p_Period_Year IN VARCHAR2
3008 , p_Period_Quarter IN VARCHAR2
3009 , p_Period_Name IN VARCHAR2
3010 , p_Global_Week_End_Date IN VARCHAR2
3011 , p_Show_Percentage_By IN VARCHAR2
3012 )
3013 IS
3014 BEGIN
3015
3016 delete from PA_REP_UTIL_SCR_U1_TMP;
3017
3018 /* */
3019 /* Case 1 for GE view */
3020 /* */
3021 PA_REP_UTIL_GLOB.SetU1Params(p_organization_id,p_period_type,p_period_name,to_number(p_period_year));
3022 IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe) THEN
3023 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3024 (
3025 title_name
3026 , title_code
3027 , exp_organization_id
3028 , exp_sub_organization_id
3029 , exp_sub_organization_name
3030 , emp_head_count
3031 , others_head_count
3032 , actuals_capacity
3033 , actuals_hours
3034 , actuals_weighted_hours
3035 , actuals_utilization
3036 , forecast_capacity
3037 , forecast_hours
3038 , forecast_weighted_hours
3039 , forecast_utilization
3040 , period_year
3041 , period_month
3042 , exp_end_date )
3043 SELECT
3044 title_name
3045 , title_code
3046 , exp_organization_id
3047 , exp_sub_organization_id
3048 , exp_sub_organization_name
3049 , emp_head_count
3050 , others_head_count
3051 , actuals_capacity
3052 , actuals_hours
3053 , actuals_weighted_hours
3054 , actuals_utilization
3055 , forecast_capacity
3056 , forecast_hours
3057 , forecast_weighted_hours
3058 , forecast_utilization
3059 , period_year
3060 , period_month
3061 , exp_end_date from PA_REP_UTIL_ORG_GE_V;
3062
3063 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3064 p_period_type
3065 , p_Global_Week_End_Date -- in lieu of p_period_name
3066 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3067 , p_period_year
3068 );
3069 /* */
3070 /* Case 2 for GL view */
3071 /* */
3072 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGl) THEN
3073 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3074 (
3075 title_name
3076 , title_code
3077 , exp_organization_id
3078 , exp_sub_organization_id
3079 , exp_sub_organization_name
3080 , emp_head_count
3081 , others_head_count
3082 , actuals_capacity
3083 , actuals_hours
3084 , actuals_weighted_hours
3085 , actuals_utilization
3086 , forecast_capacity
3087 , forecast_hours
3088 , forecast_weighted_hours
3089 , forecast_utilization
3090 , period_set_name
3091 , period_year
3092 , period_quarter
3093 , period_name
3094 , period_num)
3095 SELECT
3096 title_name
3097 , title_code
3098 , exp_organization_id
3099 , exp_sub_organization_id
3100 , exp_sub_organization_name
3101 , emp_head_count
3102 , others_head_count
3103 , actuals_capacity
3104 , actuals_hours
3105 , actuals_weighted_hours
3106 , actuals_utilization
3107 , forecast_capacity
3108 , forecast_hours
3109 , forecast_weighted_hours
3110 , forecast_utilization
3111 , period_set_name
3112 , period_year
3113 , period_quarter
3114 , period_name
3115 , period_num
3116 from PA_REP_UTIL_ORG_GL_V;
3117
3118 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3119 p_period_type
3120 , p_period_name
3121 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3122 , p_period_year
3123 );
3124 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypePa) THEN
3125 /* */
3126 /* Case 3 for PA view */
3127 /* */
3128 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3129 (
3130 title_name
3131 , title_code
3132 , exp_organization_id
3133 , exp_sub_organization_id
3134 , exp_sub_organization_name
3135 , emp_head_count
3136 , others_head_count
3137 , actuals_capacity
3138 , actuals_hours
3139 , actuals_weighted_hours
3140 , actuals_utilization
3141 , forecast_capacity
3142 , forecast_hours
3143 , forecast_weighted_hours
3144 , forecast_utilization
3145 , period_set_name
3146 , period_year
3147 , period_quarter
3148 , period_name
3149 , period_num)
3150 SELECT
3151 title_name
3152 , title_code
3153 , exp_organization_id
3154 , exp_sub_organization_id
3155 , exp_sub_organization_name
3156 , emp_head_count
3157 , others_head_count
3158 , actuals_capacity
3159 , actuals_hours
3160 , actuals_weighted_hours
3161 , actuals_utilization
3162 , forecast_capacity
3163 , forecast_hours
3164 , forecast_weighted_hours
3165 , forecast_utilization
3166 , period_set_name
3167 , period_year
3168 , period_quarter
3169 , period_name
3170 , period_num
3171 from PA_REP_UTIL_ORG_PA_V;
3172
3173 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3174 p_period_type
3175 , p_period_name
3176 , null
3177 , p_period_year
3178 );
3179 /* */
3180 /* Case 4 for YR view */
3181 /* */
3182 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
3183 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3184 (
3185 title_name
3186 , title_code
3187 , exp_organization_id
3188 , exp_sub_organization_id
3189 , exp_sub_organization_name
3190 , emp_head_count
3191 , others_head_count
3192 , actuals_capacity
3193 , actuals_hours
3194 , actuals_weighted_hours
3195 , actuals_utilization
3196 , forecast_capacity
3197 , forecast_hours
3198 , forecast_weighted_hours
3199 , forecast_utilization
3200 , period_set_name
3201 , period_year)
3202 SELECT
3203 title_name
3204 , title_code
3205 , exp_organization_id
3206 , exp_sub_organization_id
3207 , exp_sub_organization_name
3208 , emp_head_count
3209 , others_head_count
3210 , actuals_capacity
3211 , actuals_hours
3212 , actuals_weighted_hours
3213 , actuals_utilization
3214 , forecast_capacity
3215 , forecast_hours
3216 , forecast_weighted_hours
3217 , forecast_utilization
3218 , period_set_name
3219 , period_year from PA_REP_UTIL_ORG_YR_V;
3220
3221 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3222 p_period_type
3223 , p_period_name
3224 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3225 , p_period_year
3226 );
3227 /* */
3228 /* Case 5 for QR view */
3229 /* */
3230 ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr) THEN
3231 INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3232 (
3233 title_name
3234 , title_code
3235 , exp_organization_id
3236 , exp_sub_organization_id
3237 , exp_sub_organization_name
3238 , emp_head_count
3239 , others_head_count
3240 , actuals_capacity
3241 , actuals_hours
3242 , actuals_weighted_hours
3243 , actuals_utilization
3244 , forecast_capacity
3245 , forecast_hours
3246 , forecast_weighted_hours
3247 , forecast_utilization
3248 , period_set_name
3249 , period_year
3250 , period_quarter )
3251 SELECT
3252 title_name
3253 , title_code
3254 , exp_organization_id
3255 , exp_sub_organization_id
3256 , exp_sub_organization_name
3257 , emp_head_count
3258 , others_head_count
3259 , actuals_capacity
3260 , actuals_hours
3261 , actuals_weighted_hours
3262 , actuals_utilization
3263 , forecast_capacity
3264 , forecast_hours
3265 , forecast_weighted_hours
3266 , forecast_utilization
3267 , period_set_name
3268 , period_year
3269 , period_quarter from PA_REP_UTIL_ORG_QR_V
3270 WHERE
3271 period_quarter = p_period_quarter;
3272
3273 PA_RESOURCE_UTILS.SET_PERIOD_DATE(
3274 p_period_type
3275 , p_period_quarter -- in lieu of p_period_name
3276 , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3277 , p_period_year
3278 );
3279 END IF;
3280
3281 /* Commented for bug 5680366
3282 UPDATE PA_REP_UTIL_SCR_U1_TMP U1
3283 Set (emp_head_count, others_head_count) =
3284 (Select HC.emp_headcount,0
3285 From PA_RES_EMP_HCOUNT_V HC
3286 Where U1.exp_sub_organization_id = HC.organization_id
3287 And DECODE(U1.exp_sub_organization_id
3288 , p_organization_id, U1.title_code
3289 , HC.headcount_code)
3290 = HC.headcount_code
3291 );
3292 */
3293 COMMIT;
3294 END poplt_u1_screen_tmp_table;
3295 END PA_REP_UTIL_SCREEN;