[Home] [Help]
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
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))
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)))
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))
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)))
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)
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)
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
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)
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)
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)
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)
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'
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'
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'
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'
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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 */
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 */
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
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
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)
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
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
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
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
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
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))
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)))
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))
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)))
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)
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)
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
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)
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)
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)
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)
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'
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'
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'
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'
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))
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)))
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))
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)))
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)))
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))
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)
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)
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))
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)))
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))
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)))
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)))
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))
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)
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)
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 */
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
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
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)
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
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
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
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
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))
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)))
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))
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)))
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
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
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
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
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)
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)
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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
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
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
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)
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)
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'
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'
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'
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'
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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)))
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))
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
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
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
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 */
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 */
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
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
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)
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
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
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
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
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))
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)))
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))
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)))
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
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
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
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
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)
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)
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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
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
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
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)
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)
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'
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'
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'
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'
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))
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)))
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))
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)))
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)))
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))
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
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
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
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))
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)))
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))
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)))
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)))
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))
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
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
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
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 */
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 */
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
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
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)
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
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
2805:
2806:
2807: /*
2808: * END of Case 4 for U2
2809: * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr
2810: */
2811:
2812:
2813: /*
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
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
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
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
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
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
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
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;
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
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
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
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
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
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
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
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