[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: , max(summbal.period_type)
1590: , 1)
1591: )
1592: , PA_REP_UTIL_SCREEN.calculate_capacity(
1593: PA_REP_UTIL_GLOB.GetOrgId
1594: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1595: , max(paobj.person_id)
1596: , max(summbal.version_id)
1597: , max(summbal.period_type)
1598: , max(summbal.period_set_name)
1614: , DECODE(
1615: sign(
1616: sum(
1617: DecodE(paobj.balance_type_code
1618: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1619: , Decode(summbal.amount_type_id
1620: , 9, NVL(summbal.period_balance,0)
1621: , 0)
1622: , 0))
1621: , 0)
1622: , 0))
1623: -sum(
1624: DecodE(paobj.balance_type_code
1625: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1626: , Decode(summbal.amount_type_id
1627: , 10, NVL(summbal.period_balance,0)
1628: , 0)
1629: , 0)))
1629: , 0)))
1630: , 1,
1631: (sum(
1632: DecodE(paobj.balance_type_code
1633: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1634: , Decode(summbal.amount_type_id
1635: , 9, NVL(summbal.period_balance,0)
1636: , 0)
1637: , 0))
1636: , 0)
1637: , 0))
1638: -sum(
1639: DecodE(paobj.balance_type_code
1640: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1641: , Decode(summbal.amount_type_id
1642: , 10, NVL(summbal.period_balance,0)
1643: , 0)
1644: , 0)))
1647: , DECODE(
1648: sign(
1649: sum(
1650: DecodE(paobj.balance_type_code
1651: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1652: , Decode(summbal.amount_type_id
1653: , 1, NVL(summbal.period_balance,0)
1654: , 0)
1655: , 0)))
1654: , 0)
1655: , 0)))
1656: , 1, sum(
1657: DecodE(paobj.balance_type_code
1658: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1659: , Decode(summbal.amount_type_id
1660: , 1, NVL(summbal.period_balance,0)
1661: , 0)
1662: , 0))
1662: , 0))
1663: , 1)
1664: )
1665: , PA_REP_UTIL_SCREEN.calculate_capacity(
1666: PA_REP_UTIL_GLOB.GetOrgId
1667: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1668: , max(paobj.person_id)
1669: , max(summbal.version_id)
1670: , max(summbal.period_type)
1663: , 1)
1664: )
1665: , PA_REP_UTIL_SCREEN.calculate_capacity(
1666: PA_REP_UTIL_GLOB.GetOrgId
1667: , PA_REP_UTIL_GLOB.GetBalTypeActuals
1668: , max(paobj.person_id)
1669: , max(summbal.version_id)
1670: , max(summbal.period_type)
1671: , max(summbal.period_set_name)
1683: DECODE(
1684: sign(
1685: sum(
1686: DecodE(paobj.balance_type_code
1687: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1688: , Decode(summbal.amount_type_id
1689: , 9, NVL(summbal.period_balance,0)
1690: , 0)
1691: , 0))
1690: , 0)
1691: , 0))
1692: -sum(
1693: DecodE(paobj.balance_type_code
1694: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1695: , Decode(summbal.amount_type_id
1696: , 10, NVL(summbal.period_balance,0)
1697: , 0)
1698: , 0)))
1698: , 0)))
1699: , 1,
1700: (sum(
1701: DecodE(paobj.balance_type_code
1702: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1703: , Decode(summbal.amount_type_id
1704: , 9, NVL(summbal.period_balance,0)
1705: , 0)
1706: , 0))
1705: , 0)
1706: , 0))
1707: -sum(
1708: DecodE(paobj.balance_type_code
1709: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1710: , Decode(summbal.amount_type_id
1711: , 10, NVL(summbal.period_balance,0)
1712: , 0)
1713: , 0)))
1711: , 10, NVL(summbal.period_balance,0)
1712: , 0)
1713: , 0)))
1714: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1715: PA_REP_UTIL_GLOB.GetOrgId
1716: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1717: , max(paobj.person_id)
1718: , max(summbal.version_id)
1719: , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1712: , 0)
1713: , 0)))
1714: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
1715: PA_REP_UTIL_GLOB.GetOrgId
1716: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1717: , max(paobj.person_id)
1718: , max(summbal.version_id)
1719: , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1720: , null
1715: PA_REP_UTIL_GLOB.GetOrgId
1716: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1717: , max(paobj.person_id)
1718: , max(summbal.version_id)
1719: , PA_REP_UTIL_GLOB.GetPeriodTypeQr
1720: , null
1721: , null
1722: , null
1723: , 1
1731: * Field below is for FORECAST_HOURS
1732: */
1733: , sum(
1734: DECODE(paobj.balance_type_code
1735: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1736: , Decode(p_Assignment_Status
1737: , 'ALL'
1738: , decode(summbal.amount_type_id
1739: , 1, NVL(summbal.period_balance,0)
1749: , 0)
1750: , 0))
1751: -sum(
1752: DECODE(paobj.balance_type_code
1753: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1754: , Decode(p_Assignment_Status
1755: , 'CONFIRMED'
1756: , decode(summbal.amount_type_id
1757: , 4, NVL(summbal.period_balance,0)
1762: * Field below is for FORECAST_WEIGHTED_HOURS
1763: */
1764: , (sum(
1765: DECODE(paobj.balance_type_code
1766: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1767: , DecodE(p_Assignment_Status
1768: , 'ALL'
1769: , Decode(p_Utilization_Method
1770: , 'ORGANIZATION'
1798: )
1799: , 0))
1800: -sum(
1801: DECODE(paobj.balance_type_code
1802: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1803: , Decode(p_Assignment_Status
1804: , 'CONFIRMED'
1805: , Decode(p_Utilization_Method
1806: , 'ORGANIZATION'
1818: */
1819: ,ROUND(NVL(
1820: (sum(
1821: DECODE(paobj.balance_type_code
1822: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1823: , DecodE(p_Assignment_Status
1824: , 'ALL'
1825: , Decode(p_Utilization_Method
1826: , 'ORGANIZATION'
1854: )
1855: , 0))
1856: -sum(
1857: DECODE(paobj.balance_type_code
1858: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1859: , Decode(p_Assignment_Status
1860: , 'CONFIRMED'
1861: , Decode(p_Utilization_Method
1862: , 'ORGANIZATION'
1876: , DECODE(
1877: sign(
1878: sum(
1879: DecodE(paobj.balance_type_code
1880: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1881: , Decode(summbal.amount_type_id
1882: , 9, NVL(summbal.period_balance,0)
1883: , 0)
1884: , 0))
1883: , 0)
1884: , 0))
1885: -sum(
1886: DecodE(paobj.balance_type_code
1887: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1888: , Decode(summbal.amount_type_id
1889: , 10, NVL(summbal.period_balance,0)
1890: , 0)
1891: , 0)))
1891: , 0)))
1892: , 1,
1893: (sum(
1894: DecodE(paobj.balance_type_code
1895: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1896: , Decode(summbal.amount_type_id
1897: , 9, NVL(summbal.period_balance,0)
1898: , 0)
1899: , 0))
1898: , 0)
1899: , 0))
1900: -sum(
1901: DecodE(paobj.balance_type_code
1902: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1903: , Decode(summbal.amount_type_id
1904: , 10, NVL(summbal.period_balance,0)
1905: , 0)
1906: , 0)))
1909: , DECODE(
1910: sign(
1911: sum(
1912: DecodE(paobj.balance_type_code
1913: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1914: , Decode(summbal.amount_type_id
1915: , 1, NVL(summbal.period_balance,0)
1916: , 0)
1917: , 0)))
1916: , 0)
1917: , 0)))
1918: , 1, sum(
1919: DecodE(paobj.balance_type_code
1920: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1921: , Decode(summbal.amount_type_id
1922: , 1, NVL(summbal.period_balance,0)
1923: , 0)
1924: , 0))
1924: , 0))
1925: , 1)
1926: )
1927: , PA_REP_UTIL_SCREEN.calculate_capacity(
1928: PA_REP_UTIL_GLOB.GetOrgId
1929: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1930: , max(paobj.person_id)
1931: , max(summbal.version_id)
1932: , max(summbal.period_type)
1925: , 1)
1926: )
1927: , PA_REP_UTIL_SCREEN.calculate_capacity(
1928: PA_REP_UTIL_GLOB.GetOrgId
1929: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1930: , max(paobj.person_id)
1931: , max(summbal.version_id)
1932: , max(summbal.period_type)
1933: , max(summbal.period_set_name)
1949: , DECODE(
1950: sign(
1951: sum(
1952: DecodE(paobj.balance_type_code
1953: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1954: , Decode(summbal.amount_type_id
1955: , 9, NVL(summbal.period_balance,0)
1956: , 0)
1957: , 0))
1956: , 0)
1957: , 0))
1958: -sum(
1959: DecodE(paobj.balance_type_code
1960: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1961: , Decode(summbal.amount_type_id
1962: , 10, NVL(summbal.period_balance,0)
1963: , 0)
1964: , 0)))
1964: , 0)))
1965: , 1,
1966: (sum(
1967: DecodE(paobj.balance_type_code
1968: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1969: , Decode(summbal.amount_type_id
1970: , 9, NVL(summbal.period_balance,0)
1971: , 0)
1972: , 0))
1971: , 0)
1972: , 0))
1973: -sum(
1974: DecodE(paobj.balance_type_code
1975: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1976: , Decode(summbal.amount_type_id
1977: , 10, NVL(summbal.period_balance,0)
1978: , 0)
1979: , 0)))
1982: , DECODE(
1983: sign(
1984: sum(
1985: DecodE(paobj.balance_type_code
1986: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1987: , Decode(summbal.amount_type_id
1988: , 1, NVL(summbal.period_balance,0)
1989: , 0)
1990: , 0)))
1989: , 0)
1990: , 0)))
1991: , 1, sum(
1992: DecodE(paobj.balance_type_code
1993: , PA_REP_UTIL_GLOB.GetBalTypeForecast
1994: , Decode(summbal.amount_type_id
1995: , 1, NVL(summbal.period_balance,0)
1996: , 0)
1997: , 0))
1997: , 0))
1998: , 1)
1999: )
2000: , PA_REP_UTIL_SCREEN.calculate_capacity(
2001: PA_REP_UTIL_GLOB.GetOrgId
2002: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2003: , max(paobj.person_id)
2004: , max(summbal.version_id)
2005: , max(summbal.period_type)
1998: , 1)
1999: )
2000: , PA_REP_UTIL_SCREEN.calculate_capacity(
2001: PA_REP_UTIL_GLOB.GetOrgId
2002: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2003: , max(paobj.person_id)
2004: , max(summbal.version_id)
2005: , max(summbal.period_type)
2006: , max(summbal.period_set_name)
2041: AND glprd.period_set_name = summbal.period_set_name
2042: AND glprd.period_name = summbal.period_name
2043: AND summbal.object_id = paobj.object_id
2044: AND summbal.version_id = -1
2045: AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2046: AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2047: AND summbal.period_year = p_Period_Year
2048: AND summbal.quarter_or_month_number = p_Period_Quarter
2049: AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2042: AND glprd.period_name = summbal.period_name
2043: AND summbal.object_id = paobj.object_id
2044: AND summbal.version_id = -1
2045: AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2046: AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2047: AND summbal.period_year = p_Period_Year
2048: AND summbal.quarter_or_month_number = p_Period_Quarter
2049: AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2050: , 2 /* G_RES_WTDHRS_ORG_C */
2055: , 9 /* G_RES_CAP_C */
2056: ,10 /* G_RES_REDUCEDCAP_C */
2057: )
2058: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2059: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2060: , Decode(p_Utilization_Method
2061: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2062: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2063: AND paobj.object_type_code = summbal.object_type_code
2057: )
2058: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2059: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2060: , Decode(p_Utilization_Method
2061: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2062: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2063: AND paobj.object_type_code = summbal.object_type_code
2064: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2065: AND paobj.project_org_id = -1
2058: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2059: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2060: , Decode(p_Utilization_Method
2061: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2062: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2063: AND paobj.object_type_code = summbal.object_type_code
2064: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2065: AND paobj.project_org_id = -1
2066: AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2060: , Decode(p_Utilization_Method
2061: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2062: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2063: AND paobj.object_type_code = summbal.object_type_code
2064: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2065: AND paobj.project_org_id = -1
2066: AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2067: AND paobj.project_organization_id = -1
2068: AND paobj.project_id = -1
2080: , 0, -1
2081: , Decode(p_Utilization_Method
2082: , 'ORGANIZATION', -1
2083: , 'RESOURCE', p_Utilization_Category_Id))
2084: AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2085: group by
2086: DECODE(p_calling_mode
2087: , 'ORGMGR', paobj.expenditure_organization_id
2088: , 'RESMGR', NULL
2092: ;
2093: END IF;
2094: /*
2095: * END of Case 3 for U2
2096: * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr
2097: */
2098: /*
2099: * BEGINNING of Case 4 for U2
2100: * YR view
2098: /*
2099: * BEGINNING of Case 4 for U2
2100: * YR view
2101: */
2102: IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
2103:
2104: INSERT INTO PA_REP_UTIL_SCREEN_TMP (
2105: Organization_id
2106: , Person_id
2140: DECODE(
2141: sign(
2142: sum(
2143: DecodE(paobj.balance_type_code
2144: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2145: , Decode(summbal.amount_type_id
2146: , 9, NVL(summbal.period_balance,0)
2147: , 0)
2148: , 0))
2147: , 0)
2148: , 0))
2149: -sum(
2150: DecodE(paobj.balance_type_code
2151: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2152: , Decode(summbal.amount_type_id
2153: , 10, NVL(summbal.period_balance,0)
2154: , 0)
2155: , 0)))
2155: , 0)))
2156: , 1,
2157: (sum(
2158: DecodE(paobj.balance_type_code
2159: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2160: , Decode(summbal.amount_type_id
2161: , 9, NVL(summbal.period_balance,0)
2162: , 0)
2163: , 0))
2162: , 0)
2163: , 0))
2164: -sum(
2165: DecodE(paobj.balance_type_code
2166: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2167: , Decode(summbal.amount_type_id
2168: , 10, NVL(summbal.period_balance,0)
2169: , 0)
2170: , 0)))
2168: , 10, NVL(summbal.period_balance,0)
2169: , 0)
2170: , 0)))
2171: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2172: PA_REP_UTIL_GLOB.GetOrgId
2173: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2174: , max(paobj.person_id)
2175: , max(summbal.version_id)
2176: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2169: , 0)
2170: , 0)))
2171: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2172: PA_REP_UTIL_GLOB.GetOrgId
2173: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2174: , max(paobj.person_id)
2175: , max(summbal.version_id)
2176: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2177: , null
2172: PA_REP_UTIL_GLOB.GetOrgId
2173: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2174: , max(paobj.person_id)
2175: , max(summbal.version_id)
2176: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2177: , null
2178: , null
2179: , null
2180: , 1
2187: * Field below is for ACTUALS_HOURS
2188: */
2189: , sum(
2190: DECODE(paobj.balance_type_code
2191: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2192: , Decode(summbal.amount_type_id
2193: , 1, NVL(summbal.period_balance,0)
2194: , 0)
2195: , 0)) AS ACTUALS_HOURS
2197: * Field below is for ACTUALS_WEIGHTED_HOURS
2198: */
2199: , sum(
2200: DECODE(paobj.balance_type_code
2201: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2202: , Decode(p_utilization_method
2203: , 'ORGANIZATION'
2204: , decode(summbal.amount_type_id
2205: , 2, NVL(summbal.period_balance,0)
2214: * Field below is for ACTUALS_WEIGHTED_HOURS_P
2215: */
2216: ,ROUND(NVL(sum(
2217: DECODE(paobj.balance_type_code
2218: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2219: , Decode(p_utilization_method
2220: , 'ORGANIZATION'
2221: , decode(summbal.amount_type_id
2222: , 2, NVL(summbal.period_balance,0)
2233: , DECODE(
2234: sign(
2235: sum(
2236: DecodE(paobj.balance_type_code
2237: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2238: , Decode(summbal.amount_type_id
2239: , 9, NVL(summbal.period_balance,0)
2240: , 0)
2241: , 0))
2240: , 0)
2241: , 0))
2242: -sum(
2243: DecodE(paobj.balance_type_code
2244: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2245: , Decode(summbal.amount_type_id
2246: , 10, NVL(summbal.period_balance,0)
2247: , 0)
2248: , 0)))
2248: , 0)))
2249: , 1,
2250: (sum(
2251: DecodE(paobj.balance_type_code
2252: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2253: , Decode(summbal.amount_type_id
2254: , 9, NVL(summbal.period_balance,0)
2255: , 0)
2256: , 0))
2255: , 0)
2256: , 0))
2257: -sum(
2258: DecodE(paobj.balance_type_code
2259: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2260: , Decode(summbal.amount_type_id
2261: , 10, NVL(summbal.period_balance,0)
2262: , 0)
2263: , 0)))
2266: , DECODE(
2267: sign(
2268: sum(
2269: DecodE(paobj.balance_type_code
2270: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2271: , Decode(summbal.amount_type_id
2272: , 1, NVL(summbal.period_balance,0)
2273: , 0)
2274: , 0)))
2273: , 0)
2274: , 0)))
2275: , 1, sum(
2276: DecodE(paobj.balance_type_code
2277: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2278: , Decode(summbal.amount_type_id
2279: , 1, NVL(summbal.period_balance,0)
2280: , 0)
2281: , 0))
2281: , 0))
2282: , 1)
2283: )
2284: , PA_REP_UTIL_SCREEN.calculate_capacity(
2285: PA_REP_UTIL_GLOB.GetOrgId
2286: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2287: , max(paobj.person_id)
2288: , max(summbal.version_id)
2289: , max(summbal.period_type)
2282: , 1)
2283: )
2284: , PA_REP_UTIL_SCREEN.calculate_capacity(
2285: PA_REP_UTIL_GLOB.GetOrgId
2286: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2287: , max(paobj.person_id)
2288: , max(summbal.version_id)
2289: , max(summbal.period_type)
2290: , max(summbal.period_set_name)
2306: , DECODE(
2307: sign(
2308: sum(
2309: DecodE(paobj.balance_type_code
2310: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2311: , Decode(summbal.amount_type_id
2312: , 9, NVL(summbal.period_balance,0)
2313: , 0)
2314: , 0))
2313: , 0)
2314: , 0))
2315: -sum(
2316: DecodE(paobj.balance_type_code
2317: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2318: , Decode(summbal.amount_type_id
2319: , 10, NVL(summbal.period_balance,0)
2320: , 0)
2321: , 0)))
2321: , 0)))
2322: , 1,
2323: (sum(
2324: DecodE(paobj.balance_type_code
2325: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2326: , Decode(summbal.amount_type_id
2327: , 9, NVL(summbal.period_balance,0)
2328: , 0)
2329: , 0))
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)))
2339: , DECODE(
2340: sign(
2341: sum(
2342: DecodE(paobj.balance_type_code
2343: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2344: , Decode(summbal.amount_type_id
2345: , 1, NVL(summbal.period_balance,0)
2346: , 0)
2347: , 0)))
2346: , 0)
2347: , 0)))
2348: , 1, sum(
2349: DecodE(paobj.balance_type_code
2350: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2351: , Decode(summbal.amount_type_id
2352: , 1, NVL(summbal.period_balance,0)
2353: , 0)
2354: , 0))
2354: , 0))
2355: , 1)
2356: )
2357: , PA_REP_UTIL_SCREEN.calculate_capacity(
2358: PA_REP_UTIL_GLOB.GetOrgId
2359: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2360: , max(paobj.person_id)
2361: , max(summbal.version_id)
2362: , max(summbal.period_type)
2355: , 1)
2356: )
2357: , PA_REP_UTIL_SCREEN.calculate_capacity(
2358: PA_REP_UTIL_GLOB.GetOrgId
2359: , PA_REP_UTIL_GLOB.GetBalTypeActuals
2360: , max(paobj.person_id)
2361: , max(summbal.version_id)
2362: , max(summbal.period_type)
2363: , max(summbal.period_set_name)
2375: DECODE(
2376: sign(
2377: sum(
2378: DecodE(paobj.balance_type_code
2379: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2380: , Decode(summbal.amount_type_id
2381: , 9, NVL(summbal.period_balance,0)
2382: , 0)
2383: , 0))
2382: , 0)
2383: , 0))
2384: -sum(
2385: DecodE(paobj.balance_type_code
2386: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2387: , Decode(summbal.amount_type_id
2388: , 10, NVL(summbal.period_balance,0)
2389: , 0)
2390: , 0)))
2390: , 0)))
2391: , 1,
2392: (sum(
2393: DecodE(paobj.balance_type_code
2394: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2395: , Decode(summbal.amount_type_id
2396: , 9, NVL(summbal.period_balance,0)
2397: , 0)
2398: , 0))
2397: , 0)
2398: , 0))
2399: -sum(
2400: DecodE(paobj.balance_type_code
2401: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2402: , Decode(summbal.amount_type_id
2403: , 10, NVL(summbal.period_balance,0)
2404: , 0)
2405: , 0)))
2403: , 10, NVL(summbal.period_balance,0)
2404: , 0)
2405: , 0)))
2406: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2407: PA_REP_UTIL_GLOB.GetOrgId
2408: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2409: , max(paobj.person_id)
2410: , max(summbal.version_id)
2411: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2404: , 0)
2405: , 0)))
2406: ,+0)),PA_REP_UTIL_SCREEN.calculate_capacity(
2407: PA_REP_UTIL_GLOB.GetOrgId
2408: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2409: , max(paobj.person_id)
2410: , max(summbal.version_id)
2411: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2412: , null
2407: PA_REP_UTIL_GLOB.GetOrgId
2408: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2409: , max(paobj.person_id)
2410: , max(summbal.version_id)
2411: , PA_REP_UTIL_GLOB.GetPeriodTypeYr
2412: , null
2413: , null
2414: , null
2415: , 1
2422: * Field below is for FORECAST_HOURS
2423: */
2424: , sum(
2425: DECODE(paobj.balance_type_code
2426: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2427: , Decode(p_Assignment_Status
2428: , 'ALL'
2429: , decode(summbal.amount_type_id
2430: , 1, NVL(summbal.period_balance,0)
2440: , 0)
2441: , 0))
2442: -sum(
2443: DECODE(paobj.balance_type_code
2444: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2445: , Decode(p_Assignment_Status
2446: , 'CONFIRMED'
2447: , decode(summbal.amount_type_id
2448: , 4, NVL(summbal.period_balance,0)
2453: * Field below is for FORECAST_WEIGHTED_HOURS
2454: */
2455: , (sum(
2456: DECODE(paobj.balance_type_code
2457: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2458: , DecodE(p_Assignment_Status
2459: , 'ALL'
2460: , Decode(p_Utilization_Method
2461: , 'ORGANIZATION'
2489: )
2490: , 0))
2491: -sum(
2492: DECODE(paobj.balance_type_code
2493: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2494: , Decode(p_Assignment_Status
2495: , 'CONFIRMED'
2496: , Decode(p_Utilization_Method
2497: , 'ORGANIZATION'
2509: */
2510: ,ROUND(NVL(
2511: (sum(
2512: DECODE(paobj.balance_type_code
2513: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2514: , DecodE(p_Assignment_Status
2515: , 'ALL'
2516: , Decode(p_Utilization_Method
2517: , 'ORGANIZATION'
2545: )
2546: , 0))
2547: -sum(
2548: DECODE(paobj.balance_type_code
2549: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2550: , Decode(p_Assignment_Status
2551: , 'CONFIRMED'
2552: , Decode(p_Utilization_Method
2553: , 'ORGANIZATION'
2567: , DECODE(
2568: sign(
2569: sum(
2570: DecodE(paobj.balance_type_code
2571: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2572: , Decode(summbal.amount_type_id
2573: , 9, NVL(summbal.period_balance,0)
2574: , 0)
2575: , 0))
2574: , 0)
2575: , 0))
2576: -sum(
2577: DecodE(paobj.balance_type_code
2578: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2579: , Decode(summbal.amount_type_id
2580: , 10, NVL(summbal.period_balance,0)
2581: , 0)
2582: , 0)))
2582: , 0)))
2583: , 1,
2584: (sum(
2585: DecodE(paobj.balance_type_code
2586: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2587: , Decode(summbal.amount_type_id
2588: , 9, NVL(summbal.period_balance,0)
2589: , 0)
2590: , 0))
2589: , 0)
2590: , 0))
2591: -sum(
2592: DecodE(paobj.balance_type_code
2593: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2594: , Decode(summbal.amount_type_id
2595: , 10, NVL(summbal.period_balance,0)
2596: , 0)
2597: , 0)))
2600: , DECODE(
2601: sign(
2602: sum(
2603: DecodE(paobj.balance_type_code
2604: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2605: , Decode(summbal.amount_type_id
2606: , 1, NVL(summbal.period_balance,0)
2607: , 0)
2608: , 0)))
2607: , 0)
2608: , 0)))
2609: , 1, sum(
2610: DecodE(paobj.balance_type_code
2611: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2612: , Decode(summbal.amount_type_id
2613: , 1, NVL(summbal.period_balance,0)
2614: , 0)
2615: , 0))
2615: , 0))
2616: , 1)
2617: )
2618: , PA_REP_UTIL_SCREEN.calculate_capacity(
2619: PA_REP_UTIL_GLOB.GetOrgId
2620: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2621: , max(paobj.person_id)
2622: , max(summbal.version_id)
2623: , max(summbal.period_type)
2616: , 1)
2617: )
2618: , PA_REP_UTIL_SCREEN.calculate_capacity(
2619: PA_REP_UTIL_GLOB.GetOrgId
2620: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2621: , max(paobj.person_id)
2622: , max(summbal.version_id)
2623: , max(summbal.period_type)
2624: , max(summbal.period_set_name)
2640: , DECODE(
2641: sign(
2642: sum(
2643: DecodE(paobj.balance_type_code
2644: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2645: , Decode(summbal.amount_type_id
2646: , 9, NVL(summbal.period_balance,0)
2647: , 0)
2648: , 0))
2647: , 0)
2648: , 0))
2649: -sum(
2650: DecodE(paobj.balance_type_code
2651: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2652: , Decode(summbal.amount_type_id
2653: , 10, NVL(summbal.period_balance,0)
2654: , 0)
2655: , 0)))
2655: , 0)))
2656: , 1,
2657: (sum(
2658: DecodE(paobj.balance_type_code
2659: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2660: , Decode(summbal.amount_type_id
2661: , 9, NVL(summbal.period_balance,0)
2662: , 0)
2663: , 0))
2662: , 0)
2663: , 0))
2664: -sum(
2665: DecodE(paobj.balance_type_code
2666: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2667: , Decode(summbal.amount_type_id
2668: , 10, NVL(summbal.period_balance,0)
2669: , 0)
2670: , 0)))
2673: , DECODE(
2674: sign(
2675: sum(
2676: DecodE(paobj.balance_type_code
2677: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2678: , Decode(summbal.amount_type_id
2679: , 1, NVL(summbal.period_balance,0)
2680: , 0)
2681: , 0)))
2680: , 0)
2681: , 0)))
2682: , 1, sum(
2683: DecodE(paobj.balance_type_code
2684: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2685: , Decode(summbal.amount_type_id
2686: , 1, NVL(summbal.period_balance,0)
2687: , 0)
2688: , 0))
2688: , 0))
2689: , 1)
2690: )
2691: , PA_REP_UTIL_SCREEN.calculate_capacity(
2692: PA_REP_UTIL_GLOB.GetOrgId
2693: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2694: , max(paobj.person_id)
2695: , max(summbal.version_id)
2696: , max(summbal.period_type)
2689: , 1)
2690: )
2691: , PA_REP_UTIL_SCREEN.calculate_capacity(
2692: PA_REP_UTIL_GLOB.GetOrgId
2693: , PA_REP_UTIL_GLOB.GetBalTypeForecast
2694: , max(paobj.person_id)
2695: , max(summbal.version_id)
2696: , max(summbal.period_type)
2697: , max(summbal.period_set_name)
2732: AND glprd.period_set_name = summbal.period_set_name
2733: AND glprd.period_name = summbal.period_name
2734: AND summbal.object_id = paobj.object_id
2735: AND summbal.version_id = -1
2736: AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2737: AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2738: AND summbal.period_year = p_Period_Year
2739: AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2740: , 2 /* G_RES_WTDHRS_ORG_C */
2733: AND glprd.period_name = summbal.period_name
2734: AND summbal.object_id = paobj.object_id
2735: AND summbal.version_id = -1
2736: AND summbal.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2737: AND summbal.period_set_name = PA_REP_UTIL_GLOB.GetPeriodSetName
2738: AND summbal.period_year = p_Period_Year
2739: AND summbal.amount_type_id in ( 1 /* G_RES_HRS_C */
2740: , 2 /* G_RES_WTDHRS_ORG_C */
2741: , 3 /* G_RES_WTDHRS_PEOPLE_C */
2745: , 9 /* G_RES_CAP_C */
2746: ,10 /* G_RES_REDUCEDCAP_C */
2747: )
2748: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2749: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2750: , Decode(p_Utilization_Method
2751: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2752: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2753: AND paobj.object_type_code = summbal.object_type_code
2747: )
2748: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2749: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2750: , Decode(p_Utilization_Method
2751: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2752: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2753: AND paobj.object_type_code = summbal.object_type_code
2754: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2755: AND paobj.project_org_id = -1
2748: AND summbal.object_type_code = DECODE(p_Utilization_Category_Id
2749: , 0, PA_REP_UTIL_GLOB.GetObjectTypeRes
2750: , Decode(p_Utilization_Method
2751: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2752: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2753: AND paobj.object_type_code = summbal.object_type_code
2754: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2755: AND paobj.project_org_id = -1
2756: AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2750: , Decode(p_Utilization_Method
2751: , 'ORGANIZATION', PA_REP_UTIL_GLOB.GetObjectTypeResUco
2752: , 'RESOURCE', PA_REP_UTIL_GLOB.GetObjectTypeResUcr))
2753: AND paobj.object_type_code = summbal.object_type_code
2754: AND paobj.expenditure_org_id = PA_REP_UTIL_GLOB.GetOrgId
2755: AND paobj.project_org_id = -1
2756: AND paobj.expenditure_organization_id = NVL(p_organization_id,paobj.expenditure_organization_id)
2757: AND paobj.project_organization_id = -1
2758: AND paobj.project_id = -1
2770: , 0, -1
2771: , Decode(p_Utilization_Method
2772: , 'ORGANIZATION', -1
2773: , 'RESOURCE', p_Utilization_Category_Id))
2774: AND paobj.balance_type_code in (PA_REP_UTIL_GLOB.GetBalTypeActuals,PA_REP_UTIL_GLOB.GetBalTypeForecast)
2775: group by
2776: DECODE(p_calling_mode
2777: , 'ORGMGR', paobj.expenditure_organization_id
2778: , 'RESMGR', NULL
2782: ;
2783: END IF;
2784: /*
2785: * END of Case 4 for U2
2786: * p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr
2787: */
2788:
2789:
2790: /*
2857: v_period_balance NUMBER := 0;
2858: TYPE t_cap_rec IS REF CURSOR;
2859: c_cap_rec t_cap_rec;
2860: BEGIN
2861: IF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeQr THEN
2862: open c_cap_rec for
2863: select
2864: summbal2.amount_type_id AS amount_type_id
2865: , sum(summbal2.period_balance) AS period_balance
2870: paobj2.Balance_Type_Code = p_Balance_Type_Code
2871: AND paobj2.expenditure_org_id = p_ORG_ID
2872: AND summbal2.version_id = p_Version_ID
2873: AND (( summbal2.object_type_code =
2874: PA_REP_UTIL_GLOB.GetObjectTypeRes
2875: AND paobj2.person_id = p_Entity_ID
2876: AND paobj2.expenditure_organization_id =
2877: nvl(p_organization_id,paobj2.expenditure_organization_id))
2878: OR
2875: AND paobj2.person_id = p_Entity_ID
2876: AND paobj2.expenditure_organization_id =
2877: nvl(p_organization_id,paobj2.expenditure_organization_id))
2878: OR
2879: ( summbal2.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg
2880: AND paobj2.expenditure_organization_id = p_Entity_ID)
2881: )
2882: AND summbal2.object_id = paobj2.object_id
2883: AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2879: ( summbal2.object_type_code = PA_REP_UTIL_GLOB.GetObjectTypeOrg
2880: AND paobj2.expenditure_organization_id = p_Entity_ID)
2881: )
2882: AND summbal2.object_id = paobj2.object_id
2883: AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2884: AND period_year = p_Period_Year
2885: AND quarter_or_month_number = p_Quarter_Or_Month_Number
2886: AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2887: group by
2886: AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2887: group by
2888: summbal2.amount_type_id;
2889:
2890: ELSIF p_Period_Type = PA_REP_UTIL_GLOB.GetPeriodTypeYr THEN
2891: open c_cap_rec for
2892: select
2893: summbal2.amount_type_id AS amount_type_id
2894: , sum(summbal2.period_balance) AS period_balance
2899: paobj2.Balance_Type_Code = p_Balance_Type_Code
2900: AND paobj2.expenditure_org_id = p_ORG_ID
2901: AND summbal2.version_id = p_Version_ID
2902: AND (( summbal2.object_type_code =
2903: PA_REP_UTIL_GLOB.GetObjectTypeRes
2904: AND paobj2.person_id = p_Entity_ID
2905: AND paobj2.expenditure_organization_id =
2906: nvl(p_organization_id,paobj2.expenditure_organization_id))
2907: OR
2905: AND paobj2.expenditure_organization_id =
2906: nvl(p_organization_id,paobj2.expenditure_organization_id))
2907: OR
2908: ( summbal2.object_type_code =
2909: PA_REP_UTIL_GLOB.GetObjectTypeOrg
2910: AND paobj2.expenditure_organization_id = p_Entity_ID)
2911: )
2912: AND summbal2.object_id = paobj2.object_id
2913: AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2909: PA_REP_UTIL_GLOB.GetObjectTypeOrg
2910: AND paobj2.expenditure_organization_id = p_Entity_ID)
2911: )
2912: AND summbal2.object_id = paobj2.object_id
2913: AND summbal2.period_type = PA_REP_UTIL_GLOB.GetPeriodTypeGl
2914: AND period_year = p_Period_Year
2915: AND summbal2.amount_type_id in (p_Amount_ID_Resource_Hours,p_Amount_ID_Capacity,p_Amount_ID_Reduced_Capacity)
2916: group by
2917: summbal2.amount_type_id;
2927: paobj2.Balance_Type_Code = p_Balance_Type_Code
2928: AND paobj2.expenditure_org_id = p_ORG_ID
2929: AND summbal2.version_id = p_Version_ID
2930: AND (( summbal2.object_type_code =
2931: PA_REP_UTIL_GLOB.GetObjectTypeRes
2932: AND paobj2.person_id = p_Entity_ID
2933: AND paobj2.expenditure_organization_id =
2934: nvl(p_organization_id,paobj2.expenditure_organization_id))
2935: OR
2933: AND paobj2.expenditure_organization_id =
2934: nvl(p_organization_id,paobj2.expenditure_organization_id))
2935: OR
2936: ( summbal2.object_type_code =
2937: PA_REP_UTIL_GLOB.GetObjectTypeOrg
2938: AND paobj2.expenditure_organization_id = p_Entity_ID)
2939: )
2940: AND summbal2.object_id = paobj2.object_id
2941: AND summbal2.period_type = p_Period_Type
2994:
2995: /* */
2996: /* Case 1 for GE view */
2997: /* */
2998: PA_REP_UTIL_GLOB.SetU1Params(p_organization_id,p_period_type,p_period_name,to_number(p_period_year));
2999: IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe) THEN
3000: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3001: (
3002: title_name
2995: /* */
2996: /* Case 1 for GE view */
2997: /* */
2998: PA_REP_UTIL_GLOB.SetU1Params(p_organization_id,p_period_type,p_period_name,to_number(p_period_year));
2999: IF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGe) THEN
3000: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3001: (
3002: title_name
3003: , title_code
3045: );
3046: /* */
3047: /* Case 2 for GL view */
3048: /* */
3049: ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeGl) THEN
3050: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3051: (
3052: title_name
3053: , title_code
3097: , p_period_name
3098: , to_date(p_Global_Week_End_Date,'MM/DD/YYYY')
3099: , p_period_year
3100: );
3101: ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypePa) THEN
3102: /* */
3103: /* Case 3 for PA view */
3104: /* */
3105: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3155: );
3156: /* */
3157: /* Case 4 for YR view */
3158: /* */
3159: ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeYr) THEN
3160: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3161: (
3162: title_name
3163: , title_code
3203: );
3204: /* */
3205: /* Case 5 for QR view */
3206: /* */
3207: ELSIF (p_Period_Type=PA_REP_UTIL_GLOB.GetPeriodTypeQr) THEN
3208: INSERT INTO PA_REP_UTIL_SCR_U1_TMP
3209: (
3210: title_name
3211: , title_code