[Home] [Help]
PACKAGE BODY: APPS.PJI_PMV_AVL
Source
1 PACKAGE BODY PJI_PMV_AVL AS
2 -- $Header: PJIRR01B.pls 120.4 2005/11/12 17:15:34 appldev noship $
3
4 -- CACHE THE LABOUR UNITS OPTION SPECIFIED IN THE PJI SETUP.
5 G_LABOUR_UNITS VARCHAR2(40);
6 G_AVL_THRESHOLD_VAL NUMBER;
7
8 -- FORWARD DECLARATION.
9 PROCEDURE GET_AVAILABLE_SINCE_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER, P_THRESHOLD NUMBER);
10 PROCEDURE GET_CURRENT_LAST_PROJECT_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER);
11 PROCEDURE GET_NEXT_ASSIGNMENT_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER);
12
13
14
15 /****************************************************
16 * RA1: AVAILABLE TIME SUMMARY REPORT FUNCTIONS
17 ****************************************************/
18 PROCEDURE GET_SQL_PJI_REP_RA1 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
19 , X_PMV_SQL OUT NOCOPY VARCHAR2
20 , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
21 IS
22 BEGIN
23
24 PJI_PMV_ENGINE.GENERATE_SQL(P_PAGE_PARAMETER_TBL => P_PAGE_PARAMETER_TBL
25 ,P_SELECT_LIST =>
26 'FACT.CAPACITY "PJI_REP_MSR_3",
27 FACT.CONFIRMED "PJI_REP_MSR_4",
28 FACT.PROVISIONAL "PJI_REP_MSR_5",
29 FACT.UNASSIGNED "PJI_REP_MSR_11",
30 FACT.AVAILABLE "PJI_REP_MSR_12",
31 FACT.PERCENT_AVAILABLE "PJI_REP_MSR_13",
32 FACT.PERCENT_ACTUAL_UTILIZATION "PJI_REP_MSR_14",
33 FACT.PERCENT_SCHEDULED_UTILIZATION "PJI_REP_MSR_15",
34 FACT.REDUCE_CAP_A "PJI_REP_MSR_27",
35 FACT.REDUCE_CAP_S "PJI_REP_MSR_28",
36 FACT.TOT_WTD_A "PJI_REP_MSR_29",
37 FACT.CONF_WTD_S "PJI_REP_MSR_30",
38 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
39 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
40 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
41 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
42 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
43 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
44 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
45 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
46 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9",
47 FACT.PJI_REP_TOTAL_10 "PJI_REP_TOTAL_10",
48 FACT.PJI_REP_TOTAL_11 "PJI_REP_TOTAL_11",
49 FACT.PJI_REP_TOTAL_12 "PJI_REP_TOTAL_12"'
50 ,P_SQL_STATEMENT => X_PMV_SQL
51 ,P_PMV_OUTPUT => X_PMV_OUTPUT,
52 P_REGION_CODE => 'PJI_REP_RA1',
53 P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA1',
54 P_PLSQL_DRIVER_PARAMS => '<<ORGANIZATION+FII_OPERATING_UNITS>>, ' ||
55 '<<ORGANIZATION+PJI_ORGANIZATIONS>>, ' ||
56 '<<AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD>>, ' ||
57 '<<AS_OF_DATE>>, ' ||
58 '<<PERIOD_TYPE>>, ' ||
59 '<<VIEW_BY>>');
60 END GET_SQL_PJI_REP_RA1;
61
62 FUNCTION PLSQLDRIVER_RA1 (
63 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
64 P_ORGANIZATION IN VARCHAR2,
65 P_THRESHOLD IN NUMBER,
66 P_AS_OF_DATE IN NUMBER,
67 P_PERIOD_TYPE IN VARCHAR2,
68 P_VIEW_BY IN VARCHAR2
69 ) RETURN PJI_REP_RA1_TBL
70 IS
71 PRAGMA AUTONOMOUS_TRANSACTION;
72 L_AVL_DAYS_SUM_TBL PJI_REP_RA1_TBL := PJI_REP_RA1_TBL();
73
74 L_TOP_ORGANIZATION_NAME VARCHAR2(240);
75 L_TOP_ORG_INDEX NUMBER:=0;
76
77 L_CAPACITY NUMBER:=0;
78 L_CAPACITY_A NUMBER:=0;
79 L_CONFIRMED NUMBER:=0;
80 L_PROVISIONAL NUMBER:=0;
81 L_UNASSIGNED NUMBER:=0;
82 L_AVAILABLE NUMBER:=0;
83 L_PT_AVAILABLE NUMBER:=0;
84 L_PT_ACTUAL_UTILIZATION NUMBER:=0;
85 L_PT_SCHEDULED_UTILIZATION NUMBER:=0;
86
87 L_TO_CAPACITY NUMBER:=0;
88 L_TO_CAPACITY_A NUMBER:=0;
89 L_TO_CONFIRMED NUMBER:=0;
90 L_TO_PROVISIONAL NUMBER:=0;
91 L_TO_UNASSIGNED NUMBER:=0;
92 L_TO_AVAILABLE NUMBER:=0;
93 L_TO_PT_AVAILABLE NUMBER:=0;
94 L_TO_PT_ACTUAL_UTILIZATION NUMBER:=0;
95 L_TO_PT_SCHEDULED_UTILIZATION NUMBER:=0;
96
97 L_REDUCE_CAP_A NUMBER:=0;
98 L_REDUCE_CAP_S NUMBER:=0;
99 L_TOT_WTD_A NUMBER:=0;
100 L_CONF_WTD_S NUMBER:=0;
101
102 L_TO_REDUCE_CAP_A NUMBER:=0;
103 L_TO_REDUCE_CAP_S NUMBER:=0;
104 L_TO_TOT_WTD_A NUMBER:=0;
105 L_TO_CONF_WTD_S NUMBER:=0;
106
107 L_ORG_NAME VARCHAR2(240);
108 L_URL VARCHAR2(2000);
109 L_THRESHOLD NUMBER:=P_THRESHOLD;
110
111 BEGIN
112
113 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT_IDS=>P_OPERATING_UNIT,
114 P_VIEW_BY=>P_VIEW_BY);
115 PJI_PMV_ENGINE.CONVERT_ORGANIZATION (P_TOP_ORGANIZATION_ID=>P_ORGANIZATION,
116 P_VIEW_BY=>P_VIEW_BY,
117 P_TOP_ORGANIZATION_NAME => L_TOP_ORGANIZATION_NAME);
118 PJI_PMV_ENGINE.CONVERT_TIME(P_AS_OF_DATE=>P_AS_OF_DATE,
119 P_PERIOD_TYPE=>P_PERIOD_TYPE,
120 P_VIEW_BY=>P_VIEW_BY,
121 P_PARSE_PRIOR=>NULL,
122 P_REPORT_TYPE=>NULL,
123 P_COMPARATOR=>NULL,
124 P_PARSE_ITD=>NULL,
125 P_FULL_PERIOD_FLAG =>'Y');
126 IF G_LABOUR_UNITS IS NULL THEN
127 BEGIN
128 SELECT REPORT_LABOR_UNITS
129 INTO G_LABOUR_UNITS
130 FROM PJI_SYSTEM_SETTINGS;
131 EXCEPTION
132 WHEN OTHERS THEN
133 G_LABOUR_UNITS := NULL;
134 END;
135 END IF;
136
137 IF G_AVL_THRESHOLD_VAL IS NULL THEN
138 BEGIN
139 SELECT VALUE
140 INTO G_AVL_THRESHOLD_VAL
141 FROM PJI_AVL_THRESHOLDS_V
142 WHERE ID = P_THRESHOLD;
143 EXCEPTION
144 WHEN OTHERS THEN
145 G_AVL_THRESHOLD_VAL := 100;
146 END;
147 END IF;
148
149 IF P_THRESHOLD IS NULL THEN
150 BEGIN
151 SELECT DISTINCT
152 FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
153 FROM PJI_AVL_THRESHOLDS_V;
154 END;
155 END IF;
156
157
158 SELECT PJI_REP_RA1(
159 ORGANIZATION_ID
160 , SUM(CAPACITY)
161 , SUM(CAPACITY_A)
162 , SUM(CONFIRMED)
163 , SUM(PROVISIONAL)
164 , SUM(UNASSIGNED)
165 , DECODE (L_THRESHOLD, 1, SUM(AVL_BKT1)
166 , 2, SUM(AVL_BKT2)
167 , 3, SUM(AVL_BKT3)
168 , 4, SUM(AVL_BKT4)
169 , 5, SUM(AVL_BKT5)
170 , 0)
171 , 0
172 , 0
173 , 0
174 , SUM(REDUCE_CAP_A)
175 , SUM(REDUCE_CAP_S)
176 , SUM(TOT_WTD_A)
177 , SUM(CONF_WTD_S)
178 , 0
179 , 0
180 , 0
181 , 0
182 , 0
183 , 0
184 , 0
185 , 0
186 , 0
187 , 0
188 , 0
189 , 0
190 , 0)
191 BULK COLLECT INTO L_AVL_DAYS_SUM_TBL
192 FROM (
193 /* Bug 3515594 */
194 SELECT /*+ ORDERED */
195 HORG.NAME ORGANIZATION_ID
196 ,CAPACITY_HRS /
197 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
198 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY
199 ,0 CAPACITY_A
200 ,(CONF_HRS_S - NVL(CONF_OVERCOM_HRS_S,0)) /
201 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
202 'WEEKS',IMP.FTE_WEEK, 1) CONFIRMED
203 ,(PROV_HRS_S - NVL(PROV_OVERCOM_HRS_S,0)) /
204 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
205 'WEEKS',IMP.FTE_WEEK, 1) PROVISIONAL
206 ,UNASSIGNED_HRS_S /
207 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
208 'WEEKS',IMP.FTE_WEEK, 1) UNASSIGNED
209 ,0 REDUCE_CAP_A
210 ,REDUCE_CAPACITY_HRS_S /
211 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
212 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_S
213 ,0 TOT_WTD_A
214 ,CONF_WTD_ORG_HRS_S /
215 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
216 'WEEKS',IMP.FTE_WEEK, 1) CONF_WTD_S
217 ,CASE WHEN (AVAILABLE_RES_COUNT_BKT1_S = 0) THEN 0 ELSE
218 AVAILABLE_HRS_BKT1_S /
219 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
220 'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT1
221 ,CASE WHEN (AVAILABLE_RES_COUNT_BKT2_S = 0) THEN 0 ELSE
222 AVAILABLE_HRS_BKT2_S /
223 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
224 'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT2
225 ,CASE WHEN (AVAILABLE_RES_COUNT_BKT3_S = 0) THEN 0 ELSE
226 AVAILABLE_HRS_BKT3_S /
227 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
228 'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT3
229 ,CASE WHEN (AVAILABLE_RES_COUNT_BKT4_S = 0) THEN 0 ELSE
230 AVAILABLE_HRS_BKT4_S /
231 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
232 'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT4
233 ,CASE WHEN (AVAILABLE_RES_COUNT_BKT5_S = 0) THEN 0 ELSE
234 AVAILABLE_HRS_BKT5_S /
235 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
236 'WEEKS',IMP.FTE_WEEK, 1) END AVL_BKT5
237 FROM PJI_PMV_TIME_DIM_TMP TIME,
238 PJI_PMV_ORGZ_DIM_TMP HORG,
239 PJI_RM_ORGO_F_MV FCT,
240 PJI_PMV_ORG_DIM_TMP HOU,
241 PA_IMPLEMENTATIONS_ALL IMP
242 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
243 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
244 AND HOU.ID = IMP.ORG_ID
245 AND FCT.TIME_ID = TIME.ID
246 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
247 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
248 AND TIME.AMOUNT_TYPE=2
249 UNION ALL
250 SELECT /*+ ORDERED */
251 HORG.NAME ORGANIZATION_ID
252 ,0 CAPACITY
253 ,CAPACITY_HRS /
254 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
255 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY_A
256 ,0 CONFIRMED
257 ,0 PROVISIONAL
258 ,0 UNASSIGNED
259 ,REDUCE_CAPACITY_HRS_A /
260 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
261 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_A
262 ,0 REDUCE_CAP_S
263 ,TOTAL_WTD_ORG_HRS_A /
264 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY,
265 'WEEKS',IMP.FTE_WEEK, 1) TOT_WTD_A
266 ,0 CONF_WTD_S
267 ,0 AVL_BKT1
268 ,0 AVL_BKT2
269 ,0 AVL_BKT3
270 ,0 AVL_BKT4
271 ,0 AVL_BKT5
272 FROM PJI_PMV_TIME_DIM_TMP TIME,
273 PJI_PMV_ORGZ_DIM_TMP HORG,
274 PJI_RM_ORGO_F_MV FCT,
275 PJI_PMV_ORG_DIM_TMP HOU,
276 PA_IMPLEMENTATIONS_ALL IMP
277 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
278 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
279 AND HOU.ID = IMP.ORG_ID
280 AND FCT.TIME_ID = TIME.ID
281 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
282 AND FCT.CALENDAR_TYPE = DECODE(FCT.PERIOD_TYPE_ID,1, 'C',TIME.CALENDAR_TYPE)
283 AND TIME.AMOUNT_TYPE=1
284 UNION ALL
285 SELECT NAME ORGANIZATION_ID
286 , 0 CAPACITY
287 , 0 CAPACITY_A
288 , 0 CONFIRMED
289 , 0 PROVISIONAL
290 , 0 UNASSIGNED
291 , 0 REDUCE_CAP_A
292 , 0 REDUCE_CAP_S
293 , 0 TOT_WTD_A
294 , 0 CONF_WTD_S
295 , 0 AVL_BKT1
296 , 0 AVL_BKT2
297 , 0 AVL_BKT3
298 , 0 AVL_BKT4
299 , 0 AVL_BKT5
300 FROM PJI_PMV_ORGZ_DIM_TMP
301 WHERE NAME <> '-1'
302 ) GROUP BY ORGANIZATION_ID;
303
304 FOR I IN 1..L_AVL_DAYS_SUM_TBL.COUNT
305 LOOP
306 IF L_AVL_DAYS_SUM_TBL(I).ORGANIZATION_ID = L_TOP_ORGANIZATION_NAME THEN
307 L_TOP_ORG_INDEX:=I;
308
309 L_TO_CAPACITY := NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY ,0);
310 L_TO_CAPACITY_A := NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY_A ,0);
311 L_TO_CONFIRMED := NVL(L_AVL_DAYS_SUM_TBL(I).CONFIRMED ,0);
312 L_TO_PROVISIONAL := NVL(L_AVL_DAYS_SUM_TBL(I).PROVISIONAL ,0);
313 L_TO_UNASSIGNED := NVL(L_AVL_DAYS_SUM_TBL(I).UNASSIGNED ,0);
314 L_TO_AVAILABLE := NVL(L_AVL_DAYS_SUM_TBL(I).AVAILABLE ,0);
315 L_TO_REDUCE_CAP_A := NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_A ,0);
316 L_TO_REDUCE_CAP_S := NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_S ,0);
317 L_TO_TOT_WTD_A := NVL(L_AVL_DAYS_SUM_TBL(I).TOT_WTD_A ,0);
318 L_TO_CONF_WTD_S := NVL(L_AVL_DAYS_SUM_TBL(I).CONF_WTD_S ,0);
319
320 ELSE
321 L_CAPACITY := L_CAPACITY
322 + NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY ,0);
323 L_CAPACITY_A := L_CAPACITY_A
324 + NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY_A ,0);
325 L_CONFIRMED :=L_CONFIRMED
326 + NVL(L_AVL_DAYS_SUM_TBL(I).CONFIRMED ,0);
327 L_PROVISIONAL :=L_PROVISIONAL
328 + NVL(L_AVL_DAYS_SUM_TBL(I).PROVISIONAL ,0);
329 L_UNASSIGNED :=L_UNASSIGNED
330 + NVL(L_AVL_DAYS_SUM_TBL(I).UNASSIGNED ,0);
331 L_AVAILABLE :=L_AVAILABLE
332 + NVL(L_AVL_DAYS_SUM_TBL(I).AVAILABLE ,0);
333 L_REDUCE_CAP_A:= L_REDUCE_CAP_A
334 + NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_A ,0);
335 L_REDUCE_CAP_S:= L_REDUCE_CAP_S
336 + NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_S ,0);
337 L_TOT_WTD_A:= L_TOT_WTD_A
338 + NVL(L_AVL_DAYS_SUM_TBL(I).TOT_WTD_A ,0);
339 L_CONF_WTD_S:= L_CONF_WTD_S
340 + NVL(L_AVL_DAYS_SUM_TBL(I).CONF_WTD_S ,0);
341
342 END IF;
343 END LOOP;
344
345 IF NVL(L_TOP_ORG_INDEX, 0) <> 0 THEN
346 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY
347 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY,0)
348 -L_CAPACITY;
349
350 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY_A
351 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY_A,0)
352 -L_CAPACITY_A;
353
354 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONFIRMED
355 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONFIRMED ,0)
356 -L_CONFIRMED;
357
358 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).PROVISIONAL
359 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).PROVISIONAL ,0)
360 -L_PROVISIONAL;
361
362 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).UNASSIGNED
363 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).UNASSIGNED ,0)
364 -L_UNASSIGNED;
365
366 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).AVAILABLE
367 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).AVAILABLE,0)
368 -L_AVAILABLE;
369
370 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).REDUCE_CAP_A
371 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).REDUCE_CAP_A ,0)
372 - L_REDUCE_CAP_A;
373
374 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).REDUCE_CAP_S
375 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).REDUCE_CAP_S ,0)
376 - L_REDUCE_CAP_S ;
377
378 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).TOT_WTD_A
379 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).TOT_WTD_A ,0)
380 - L_TOT_WTD_A;
381
382 L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONF_WTD_S
383 := NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONF_WTD_S ,0)
384 - L_CONF_WTD_S;
385
386 IF
387 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY_A ,0) =0 AND
388 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CAPACITY ,0) =0 AND
389 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONFIRMED ,0) =0 AND
390 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).PROVISIONAL ,0) =0 AND
391 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).UNASSIGNED ,0) =0 AND
392 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).AVAILABLE ,0) =0 AND
393 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).REDUCE_CAP_A,0) =0 AND
394 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).TOT_WTD_A ,0) =0 AND
395 NVL(L_AVL_DAYS_SUM_TBL(L_TOP_ORG_INDEX).CONF_WTD_S ,0) =0
396 THEN
397 L_AVL_DAYS_SUM_TBL.DELETE(L_TOP_ORG_INDEX);
398 END IF;
399
400 L_CAPACITY := L_TO_CAPACITY;
401 L_CAPACITY_A := L_TO_CAPACITY_A;
402 L_CONFIRMED := L_TO_CONFIRMED;
403 L_PROVISIONAL := L_TO_PROVISIONAL;
404 L_UNASSIGNED := L_TO_UNASSIGNED;
405 L_AVAILABLE := L_TO_AVAILABLE;
406 L_REDUCE_CAP_A := L_TO_REDUCE_CAP_A;
407 L_REDUCE_CAP_S := L_TO_REDUCE_CAP_S;
408 L_TOT_WTD_A := L_TO_TOT_WTD_A;
409 L_CONF_WTD_S := L_TO_CONF_WTD_S;
410
411 END IF;
412
413 IF L_AVL_DAYS_SUM_TBL.COUNT > 0 THEN
414 FOR I IN L_AVL_DAYS_SUM_TBL.FIRST..L_AVL_DAYS_SUM_TBL.LAST
415 LOOP
416 IF L_AVL_DAYS_SUM_TBL.EXISTS(I) THEN
417
418 IF NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY, 0)=0 THEN
419 L_AVL_DAYS_SUM_TBL(I).PERCENT_AVAILABLE:= NULL;
420 ELSE
421 L_AVL_DAYS_SUM_TBL(I).PERCENT_AVAILABLE:=
422 (L_AVL_DAYS_SUM_TBL(I).AVAILABLE
423 /L_AVL_DAYS_SUM_TBL(I).CAPACITY)*100;
424 END IF;
425
426 IF (NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY_A, 0) -
427 NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_A, 0))=0 THEN
428 L_AVL_DAYS_SUM_TBL(I).PERCENT_ACTUAL_UTILIZATION :=NULL;
429 ELSE
430 L_AVL_DAYS_SUM_TBL(I).PERCENT_ACTUAL_UTILIZATION:=
431 (L_AVL_DAYS_SUM_TBL(I).TOT_WTD_A/
432 (L_AVL_DAYS_SUM_TBL(I).CAPACITY_A -
433 L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_A))*100;
434 END IF;
435
436 IF (NVL(L_AVL_DAYS_SUM_TBL(I).CAPACITY, 0) -
437 NVL(L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_S, 0))=0 THEN
438 L_AVL_DAYS_SUM_TBL(I).PERCENT_SCHEDULED_UTILIZATION :=NULL;
439 ELSE
440
441 L_AVL_DAYS_SUM_TBL(I).PERCENT_SCHEDULED_UTILIZATION:=
442 (L_AVL_DAYS_SUM_TBL(I).CONF_WTD_S/
443 (L_AVL_DAYS_SUM_TBL(I).CAPACITY
444 - L_AVL_DAYS_SUM_TBL(I).REDUCE_CAP_S))*100;
445 END IF;
446
447 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_1 := L_CAPACITY;
448 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_2 := L_CONFIRMED;
449 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_3 := L_PROVISIONAL;
450 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_4 := L_UNASSIGNED;
451 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_5 := L_AVAILABLE;
452 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_9 := L_REDUCE_CAP_A ;
453 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_10 :=L_REDUCE_CAP_S;
454 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_11 :=L_TOT_WTD_A;
455 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_12 :=L_CONF_WTD_S ;
456 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_13 :=L_CAPACITY_A ;
457
458 IF NVL(L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_1, 0)=0 THEN
459 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_6:= NULL;
460 ELSE
461 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_6:=
462 (L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_5
463 /L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_1)*100;
464 END IF;
465
466 IF NVL(L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_13-
467 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_9, 0)=0 THEN
468 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_7:= NULL;
469 ELSE
470 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_7:=
471 (L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_11
472 /(L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_13-
473 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_9))*100;
474 END IF;
475
476 IF NVL(L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_1-
477 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_10, 0)=0 THEN
478 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_8:= NULL;
479 ELSE
480 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_8:=
481 (L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_12
482 /(L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_1-
483 L_AVL_DAYS_SUM_TBL(I).PJI_REP_TOTAL_10))*100;
484 END IF;
485 END IF;
486 END LOOP;
487 END IF;
488
489 COMMIT;
490 RETURN L_AVL_DAYS_SUM_TBL;
491 END PLSQLDRIVER_RA1;
492
493
494 /****************************************************
495 * RA2: CURRENT AVAILABLE RESOURCES REPORT FUNCTIONS
496 ****************************************************/
497 PROCEDURE GET_SQL_PJI_REP_RA2 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
498 , X_PMV_SQL OUT NOCOPY VARCHAR2
499 , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
500 IS
501 BEGIN
502
503 PJI_PMV_ENGINE.GENERATE_SQL(P_PAGE_PARAMETER_TBL => P_PAGE_PARAMETER_TBL
504 ,P_SELECT_LIST =>
505 'FACT.CURR_AVL_RES "PJI_REP_MSR_2",
506 FACT.CURR_AVL_RES_URL_PARAMS "PJI_REP_MSR_12",
507 FACT.TOTAL_RESOURCES "PJI_REP_MSR_3",
508 FACT.CURR_AVL_RES_PER "PJI_REP_MSR_4",
509 FACT.W1 "PJI_REP_MSR_5",
510 FACT.W1_URL_PARAMS "PJI_REP_MSR_15",
511 FACT.W2 "PJI_REP_MSR_6",
512 FACT.W2_URL_PARAMS "PJI_REP_MSR_16",
513 FACT.W3 "PJI_REP_MSR_7",
514 FACT.W3_URL_PARAMS "PJI_REP_MSR_17",
515 FACT.W4 "PJI_REP_MSR_8",
516 FACT.W4_URL_PARAMS "PJI_REP_MSR_18",
517 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
518 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
519 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_3",
520 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_4",
521 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_5",
522 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_6",
523 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_7"'
524 ,P_SQL_STATEMENT => X_PMV_SQL
525 ,P_PMV_OUTPUT => X_PMV_OUTPUT,
526 P_REGION_CODE => 'PJI_REP_RA2',
527 P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA2',
528 P_PLSQL_DRIVER_PARAMS => '<<ORGANIZATION+FII_OPERATING_UNITS>>, ' ||
529 '<<ORGANIZATION+PJI_ORGANIZATIONS>>, ' ||
530 '<<AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD>>, ' ||
531 '<<AS_OF_DATE>>, ' ||
532 '<<VIEW_BY>>');
533 END GET_SQL_PJI_REP_RA2;
534
535 FUNCTION PLSQLDRIVER_RA2 (
536 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
537 P_ORGANIZATION IN VARCHAR2,
538 P_THRESHOLD IN NUMBER,
539 P_AS_OF_DATE IN NUMBER,
540 P_VIEW_BY IN VARCHAR2
541 ) RETURN PJI_REP_RA2_TBL
542 IS
543 PRAGMA AUTONOMOUS_TRANSACTION;
544 L_CUR_AVL_RES_TBL PJI_REP_RA2_TBL := PJI_REP_RA2_TBL();
545 TYPE T_WEEK_IDS IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
546 TYPE T_WEEK_END_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
547 L_THRESHOLD NUMBER := P_THRESHOLD;
548 L_AS_OF_DATE NUMBER := P_AS_OF_DATE;
549
550
551 L_TOP_ORGANIZATION_NAME VARCHAR2(240);
552 L_TOP_ORG_INDEX NUMBER;
553
554 L_TOTAL_CURR_AVL_RES NUMBER := 0;
555 L_TOTAL_TOTAL_RESOURCES NUMBER := 0;
556 L_TOTAL_W1 NUMBER := 0;
557 L_TOTAL_W2 NUMBER := 0;
558 L_TOTAL_W3 NUMBER := 0;
559 L_TOTAL_W4 NUMBER := 0;
560 L_TOTAL_CURR_AVL_RES_PER NUMBER := 0;
561
562 L_TO_TOTAL_CURR_AVL_RES NUMBER := 0;
563 L_TO_TOTAL_TOTAL_RESOURCES NUMBER := 0;
564 L_TO_TOTAL_W1 NUMBER := 0;
565 L_TO_TOTAL_W2 NUMBER := 0;
566 L_TO_TOTAL_W3 NUMBER := 0;
567 L_TO_TOTAL_W4 NUMBER := 0;
568 L_TO_TOTAL_CURR_AVL_RES_PER NUMBER := 0;
569
570 L_WEEK_IDS T_WEEK_IDS;
571 L_WEEK_END_DATE T_WEEK_END_DATE;
572 L_CALENDAR_TYPE VARCHAR2(1):='E';
573 L_PERIOD_TYPE_ID NUMBER:=16;
574 BEGIN
575
576 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT, P_VIEW_BY);
577 PJI_PMV_ENGINE.CONVERT_ORGANIZATION (P_ORGANIZATION, P_VIEW_BY, L_TOP_ORGANIZATION_NAME);
578
579 -- DELETE THE TIME DIMENSION TEMP TABLE FIRST BEFORE INSERTING AGAIN.
580 DELETE PJI_PMV_TIME_DIM_TMP;
581
582 ----------------------------------------------------
583 -- THIS PART OF THE CODE GET THE NEXT FOUR WEEKS
584 -- TIME_ID FROM THE AS_OF_DATE AND INSERT INTO THE
585 -- TIME DIMENSION TEMP TABLE
586 -----------------------------------------------------
587
588 SELECT WEEK_ID , TO_CHAR(END_DATE,'J')
589 BULK COLLECT INTO
590 L_WEEK_IDS , L_WEEK_END_DATE
591 FROM FII_TIME_WEEK
592 WHERE 1 = 1
593 AND TO_DATE(P_AS_OF_DATE+28,'J') >= START_DATE
594 AND TO_DATE(P_AS_OF_DATE,'J') <= END_DATE;
595
596 CASE (L_WEEK_IDS.COUNT)
597 WHEN 0 THEN
598 L_WEEK_IDS(1):=NULL;
599 L_WEEK_END_DATE(1):=NULL;
600 L_WEEK_IDS(2):=NULL;
601 L_WEEK_END_DATE(2):=NULL;
602 L_WEEK_IDS(3):=NULL;
603 L_WEEK_END_DATE(3):=NULL;
604 L_WEEK_IDS(4):=NULL;
605 L_WEEK_END_DATE(4):=NULL;
606 L_WEEK_IDS(5):=NULL;
607 L_WEEK_END_DATE(5):=NULL;
608 WHEN 1 THEN
609 L_WEEK_IDS(2):=NULL;
610 L_WEEK_END_DATE(2):=NULL;
611 L_WEEK_IDS(3):=NULL;
612 L_WEEK_END_DATE(3):=NULL;
613 L_WEEK_IDS(4):=NULL;
614 L_WEEK_END_DATE(4):=NULL;
615 L_WEEK_IDS(5):=NULL;
616 L_WEEK_END_DATE(5):=NULL;
617 WHEN 2 THEN NULL;
618 L_WEEK_IDS(3):=NULL;
619 L_WEEK_END_DATE(3):=NULL;
620 L_WEEK_IDS(4):=NULL;
621 L_WEEK_END_DATE(4):=NULL;
622 L_WEEK_IDS(5):=NULL;
623 L_WEEK_END_DATE(5):=NULL;
624 WHEN 3 THEN NULL;
625 L_WEEK_IDS(4):=NULL;
626 L_WEEK_END_DATE(4):=NULL;
627 L_WEEK_IDS(5):=NULL;
628 L_WEEK_END_DATE(5):=NULL;
629 WHEN 4 THEN NULL;
630 L_WEEK_IDS(5):=NULL;
631 L_WEEK_END_DATE(5):=NULL;
632 ELSE
633 NULL;
634 END CASE;
635 -------------------------------------------
636 -- END OF GETTING NEXT FOUR WEEKS TIME ID
637 -------------------------------------------
638
639
640 IF P_THRESHOLD IS NULL THEN
641 BEGIN
642 SELECT DISTINCT
643 FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
644 FROM PJI_AVL_THRESHOLDS_V;
645 END;
646 END IF;
647
648
649 SELECT PJI_REP_RA2(
650 ORGANIZATION_ID
651 ,SUM(CURR_AVL)
652 ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W0',ID,p_operating_unit,l_threshold)
653 ,SUM(TOTAL_RESOURCES)
654 ,SUM(WEEK_1)
655 ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W1',ID,p_operating_unit,l_threshold)
656 ,SUM(WEEK_2)
657 ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W2',ID,p_operating_unit,l_threshold)
658 ,SUM(WEEK_3)
659 ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W3',ID,p_operating_unit,l_threshold)
660 ,SUM(WEEK_4)
661 ,PJI_PMV_UTIL.ra2_ra5_url(p_as_of_date,'W4',ID,p_operating_unit,l_threshold)
662 ,NULL
663 ,NULL
664 ,NULL
665 ,NULL
666 ,NULL
667 ,NULL
668 ,NULL
669 ,NULL)
670 BULK COLLECT INTO L_CUR_AVL_RES_TBL
671 FROM (
672 SELECT /*+ ORDERED */
673 HORG.NAME ORGANIZATION_ID
674 ,HORG.ID ID
675 ,TOTAL_RES_COUNT TOTAL_RESOURCES
676 ,FCT.AVAILABILITY CURR_AVL
677 ,0 WEEK_1
678 ,0 WEEK_2
679 ,0 WEEK_3
680 ,0 WEEK_4
681 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
682 PJI_CA_ORGO_F_MV FCT,
683 PJI_PMV_ORG_DIM_TMP HOU
684 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
685 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
686 AND FCT.TIME_ID = L_WEEK_IDS(1)
687 AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
688 AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
689 AND FCT.THRESHOLD = L_THRESHOLD
690 AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(1)
691 UNION ALL
692 SELECT /*+ ORDERED */
693 HORG.NAME ORGANIZATION_ID
694 ,HORG.ID ID
695 ,0 TOTAL_RESOURCES
696 ,0 CURR_AVL
697 ,FCT.AVAILABILITY WEEK_1
698 ,0 WEEK_2
699 ,0 WEEK_3
700 ,0 WEEK_4
701 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
702 PJI_CA_ORGO_F_MV FCT,
703 PJI_PMV_ORG_DIM_TMP HOU
704 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
705 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
706 AND FCT.TIME_ID = L_WEEK_IDS(2)
707 AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
708 AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
709 AND FCT.THRESHOLD = L_THRESHOLD
710 AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(2)
711 UNION ALL
712 SELECT /*+ ORDERED */
713 HORG.NAME ORGANIZATION_ID
714 ,HORG.ID ID
715 ,0 TOTAL_RESOURCES
716 ,0 CURR_AVL
717 ,0 WEEK_1
718 ,FCT.AVAILABILITY WEEK_2
719 ,0 WEEK_3
720 ,0 WEEK_4
721 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
722 PJI_CA_ORGO_F_MV FCT,
723 PJI_PMV_ORG_DIM_TMP HOU
724 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
725 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
726 AND FCT.TIME_ID = L_WEEK_IDS(3)
727 AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
728 AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
729 AND FCT.THRESHOLD = L_THRESHOLD
730 AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(3)
731 UNION ALL
732 SELECT /*+ ORDERED */
733 HORG.NAME ORGANIZATION_ID
734 ,HORG.ID ID
735 ,0 TOTAL_RESOURCES
736 ,0 CURR_AVL
737 ,0 WEEK_1
738 ,0 WEEK_2
739 ,FCT.AVAILABILITY WEEK_3
740 ,0 WEEK_4
741 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
742 PJI_CA_ORGO_F_MV FCT,
743 PJI_PMV_ORG_DIM_TMP HOU
744 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
745 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
746 AND FCT.TIME_ID = L_WEEK_IDS(4)
747 AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
748 AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
749 AND FCT.THRESHOLD = L_THRESHOLD
750 AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(4)
751 UNION ALL
752 SELECT /*+ ORDERED */
753 HORG.NAME ORGANIZATION_ID
754 ,HORG.ID ID
755 ,0 TOTAL_RESOURCES
756 ,0 CURR_AVL
757 ,0 WEEK_1
758 ,0 WEEK_2
759 ,0 WEEK_3
760 ,FCT.AVAILABILITY WEEK_4
761 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
762 PJI_CA_ORGO_F_MV FCT,
763 PJI_PMV_ORG_DIM_TMP HOU
764 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
765 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
766 AND FCT.TIME_ID = L_WEEK_IDS(5)
767 AND FCT.CALENDAR_TYPE = L_CALENDAR_TYPE
768 AND FCT.PERIOD_TYPE_ID = L_PERIOD_TYPE_ID
769 AND FCT.THRESHOLD = L_THRESHOLD
770 AND FCT.AS_OF_DATE <= L_WEEK_END_DATE(5)
771 UNION ALL
772 SELECT
773 NAME ORGANIZATION_ID
774 ,ID ID
775 ,0 TOTAL_RESOURCES
776 ,0 CURR_AVL
777 ,0 WEEK_1
778 ,0 WEEK_2
779 ,0 WEEK_3
780 ,0 WEEK_4
781 FROM PJI_PMV_ORGZ_DIM_TMP
782 WHERE NAME <> '-1'
783 ) GROUP BY ORGANIZATION_ID, ID;
784
785 FOR I IN 1..L_CUR_AVL_RES_TBL.COUNT
786 LOOP
787 IF L_CUR_AVL_RES_TBL(I).ORGANIZATION_ID = L_TOP_ORGANIZATION_NAME THEN
788 L_TOP_ORG_INDEX:=I;
789
790 L_TO_TOTAL_CURR_AVL_RES := NVL(L_CUR_AVL_RES_TBL(I).CURR_AVL_RES, 0);
791 L_TO_TOTAL_TOTAL_RESOURCES := NVL(L_CUR_AVL_RES_TBL(I).TOTAL_RESOURCES, 0);
792 L_TO_TOTAL_W1 := NVL(L_CUR_AVL_RES_TBL(I).W1, 0);
793 L_TO_TOTAL_W2 := NVL(L_CUR_AVL_RES_TBL(I).W2, 0);
794 L_TO_TOTAL_W3 := NVL(L_CUR_AVL_RES_TBL(I).W3, 0);
795 L_TO_TOTAL_W4 := NVL(L_CUR_AVL_RES_TBL(I).W4, 0);
796
797 ELSE
798 L_TOTAL_CURR_AVL_RES := L_TOTAL_CURR_AVL_RES+NVL(L_CUR_AVL_RES_TBL(I).CURR_AVL_RES, 0);
799 L_TOTAL_TOTAL_RESOURCES := L_TOTAL_TOTAL_RESOURCES+NVL(L_CUR_AVL_RES_TBL(I).TOTAL_RESOURCES, 0);
800 L_TOTAL_W1 := L_TOTAL_W1 + NVL(L_CUR_AVL_RES_TBL(I).W1, 0);
801 L_TOTAL_W2 := L_TOTAL_W2 + NVL(L_CUR_AVL_RES_TBL(I).W2, 0);
802 L_TOTAL_W3 := L_TOTAL_W3 + NVL(L_CUR_AVL_RES_TBL(I).W3, 0);
803 L_TOTAL_W4 := L_TOTAL_W4 + NVL(L_CUR_AVL_RES_TBL(I).W4, 0);
804 IF NVL(L_CUR_AVL_RES_TBL(I).TOTAL_RESOURCES, 0) <> 0 THEN
805 L_CUR_AVL_RES_TBL(I).CURR_AVL_RES_PER:=
806 (L_CUR_AVL_RES_TBL(I).CURR_AVL_RES * 100)/L_CUR_AVL_RES_TBL(I).TOTAL_RESOURCES;
807 ELSE
808 L_CUR_AVL_RES_TBL(I).CURR_AVL_RES_PER:=NULL;
809 END IF;
810 END IF;
811 END LOOP;
812
813 IF NVL(L_TOP_ORG_INDEX, 0) > 0 THEN
814 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES - L_TOTAL_CURR_AVL_RES;
815 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES - L_TOTAL_TOTAL_RESOURCES;
816 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W1 := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W1 - L_TOTAL_W1;
817 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W2 := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W2 - L_TOTAL_W2;
818 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W3 := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W3 - L_TOTAL_W3;
819 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W4 := L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W4 - L_TOTAL_W4;
820 IF NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES, 0) <> 0 THEN
821 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES_PER := (L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES * 100)/L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES;
822 ELSE
823 L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES_PER := NULL;
824 END IF;
825 END IF;
826
827 FOR I IN 1..L_CUR_AVL_RES_TBL.COUNT
828 LOOP
829 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_1 := L_TO_TOTAL_CURR_AVL_RES;
830 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_2 := L_TO_TOTAL_TOTAL_RESOURCES;
831 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_3 := L_TO_TOTAL_W1;
832 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_4 := L_TO_TOTAL_W2;
833 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_5 := L_TO_TOTAL_W3;
834 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_6 := L_TO_TOTAL_W4;
835
836 IF NVL(L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_2, 0) <> 0 THEN
837 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_7 := (L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_1 * 100)/L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_2;
838 ELSE
839 L_CUR_AVL_RES_TBL(I).PJI_REP_TOTAL_7 := NULL;
840 END IF;
841 END LOOP;
842
843 IF NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES, 0) = 0
844 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES, 0) = 0
845 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W1, 0) = 0
846 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W2, 0) = 0
847 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W3, 0) = 0
848 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).W4, 0) = 0
849 AND NVL(L_CUR_AVL_RES_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES_PER, 0) = 0
850 THEN
851 L_CUR_AVL_RES_TBL.DELETE(L_TOP_ORG_INDEX);
852 END IF;
853
854 COMMIT;
855 RETURN L_CUR_AVL_RES_TBL;
856
857 END PLSQLDRIVER_RA2;
858
859
860 /******************************************************
861 * RA3: AVAILABLE RESOURCE DURATION REPORT FUNCTIONS
862 ******************************************************/
863 PROCEDURE GET_SQL_PJI_REP_RA3 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
864 , X_PMV_SQL OUT NOCOPY VARCHAR2
865 , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
866 IS
867 BEGIN
868
869 PJI_PMV_ENGINE.GENERATE_SQL(P_PAGE_PARAMETER_TBL => P_PAGE_PARAMETER_TBL
870 ,P_SELECT_LIST =>
871 'FACT.EXP_ORGANIZATION_ID "VIEWBYID",
872 FACT.CURR_AVL_RES "PJI_REP_MSR_2",
873 FACT.TOTAL_AVL_RES "PJI_REP_MSR_3",
874 FACT.TOTAL_RESOURCES "PJI_REP_MSR_4",
875 FACT.TOTAL_AVL_RES_PERCENT "PJI_REP_MSR_5",
876 FACT.BUCKET1 "PJI_REP_MSR_11",
877 FACT.BUCKET2 "PJI_REP_MSR_12",
878 FACT.BUCKET3 "PJI_REP_MSR_13",
879 FACT.BUCKET4 "PJI_REP_MSR_14",
880 FACT.BUCKET5 "PJI_REP_MSR_15",
881 FACT.PJI_REP_TOTAL_1 "PJI_REP_TOTAL_1",
882 FACT.PJI_REP_TOTAL_2 "PJI_REP_TOTAL_2",
883 FACT.PJI_REP_TOTAL_3 "PJI_REP_TOTAL_3",
884 FACT.PJI_REP_TOTAL_4 "PJI_REP_TOTAL_4",
885 FACT.PJI_REP_TOTAL_5 "PJI_REP_TOTAL_5",
886 FACT.PJI_REP_TOTAL_6 "PJI_REP_TOTAL_6",
887 FACT.PJI_REP_TOTAL_7 "PJI_REP_TOTAL_7",
888 FACT.PJI_REP_TOTAL_8 "PJI_REP_TOTAL_8",
889 FACT.PJI_REP_TOTAL_9 "PJI_REP_TOTAL_9"'
890 ,P_SQL_STATEMENT => X_PMV_SQL
891 ,P_PMV_OUTPUT => X_PMV_OUTPUT,
892 P_REGION_CODE => 'PJI_REP_RA3',
893 P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA3',
894 P_PLSQL_DRIVER_PARAMS => '<<ORGANIZATION+FII_OPERATING_UNITS>>, ' ||
895 '<<ORGANIZATION+PJI_ORGANIZATIONS>>, ' ||
896 '<<AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD>>, ' ||
897 '<<AVAILABILITY_TYPE+AVAILABILITY_TYPE>>, ' ||
898 '<<AS_OF_DATE>>, ' ||
899 '<<PERIOD_TYPE>>, ' ||
900 '<<VIEW_BY>>');
901 END GET_SQL_PJI_REP_RA3;
902
903 FUNCTION PLSQLDRIVER_RA3 (
904 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
905 P_ORGANIZATION IN VARCHAR2,
906 P_THRESHOLD IN NUMBER,
907 P_AVL_TYPE IN VARCHAR2,
908 P_AS_OF_DATE IN NUMBER,
909 P_PERIOD_TYPE IN VARCHAR2,
910 P_VIEW_BY IN VARCHAR2
911 ) RETURN PJI_REP_RA3_TBL
912 IS
913 PRAGMA AUTONOMOUS_TRANSACTION;
914 L_AVL_RES_DUR_TBL PJI_REP_RA3_TBL := PJI_REP_RA3_TBL();
915 L_THRESHOLD NUMBER := P_THRESHOLD;
916 L_AS_OF_DATE NUMBER := P_AS_OF_DATE;
917 L_AVL_TYPE VARCHAR2(60) := P_AVL_TYPE;
918
919 L_CURR_AVL_RES NUMBER:=0;
920 L_TOTAL_AVL_RES NUMBER:=0;
921 L_TOTAL_RESOURSES NUMBER:=0;
922 L_AVL_1_5_DAYS NUMBER:=0;
923 L_AVL_6_10_DAYS NUMBER:=0;
924 L_AVL_11_15_DAYS NUMBER:=0;
925 L_AVL_16_20_DAYS NUMBER:=0;
926 L_AVL_MORE_THAN_20_DAYS NUMBER:=0;
927
928 L_TO_CURR_AVL_RES NUMBER:=0;
929 L_TO_TOTAL_AVL_RES NUMBER:=0;
930 L_TO_TOTAL_RESOURSES NUMBER:=0;
931 L_TO_AVL_1_5_DAYS NUMBER:=0;
932 L_TO_AVL_6_10_DAYS NUMBER:=0;
933 L_TO_AVL_11_15_DAYS NUMBER:=0;
934 L_TO_AVL_16_20_DAYS NUMBER:=0;
935 L_TO_AVL_MORE_THAN_20_DAYS NUMBER:=0;
936
937 L_TOP_ORGANIZATION_NAME VARCHAR2(240);
938 L_TOP_ORG_INDEX NUMBER:=0;
939 l_DAY_CALENDAR_TYPE VARCHAR2(1):='C';
940 l_DAY_PERIOD_TYPE NUMBER:=1;
941 BEGIN
942
943 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT, P_VIEW_BY);
944 PJI_PMV_ENGINE.CONVERT_ORGANIZATION (P_ORGANIZATION, P_VIEW_BY, L_TOP_ORGANIZATION_NAME);
945 PJI_PMV_ENGINE.CONVERT_TIME (P_AS_OF_DATE=>P_AS_OF_DATE,
946 P_PERIOD_TYPE=>P_PERIOD_TYPE,
947 P_VIEW_BY =>P_VIEW_BY,
948 P_PARSE_PRIOR=>NULL,
949 P_REPORT_TYPE=>NULL,
950 P_COMPARATOR =>NULL,
951 P_PARSE_ITD =>NULL,
952 P_FULL_PERIOD_FLAG =>'Y');
953
954 -- IF P_AS_OF_DATE IS NULL, THEN SET THE DEFAULT VALUE TO SYSDATE
955 IF L_AS_OF_DATE IS NULL THEN
956 L_AS_OF_DATE := TO_NUMBER(TO_CHAR(SYSDATE, 'J'));
957 END IF;
958
959 -- IF AVAILABILITY TYPE IS NULL, SET TO 'CUMULATIVE'
960 IF L_AVL_TYPE IS NULL THEN
961 L_AVL_TYPE := 'CUMULATIVE';
962 END IF;
963
964 -- RETURN DIFFERENT SQL DEPENDING ON THE AVAILABILITY TYPE
965 -- EITHER CUMULATIVE OR CONSECUTIVE
966
967 IF P_THRESHOLD IS NULL THEN
968 BEGIN
969 SELECT DISTINCT
970 FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
971 FROM PJI_AVL_THRESHOLDS_V;
972 END;
973 END IF;
974
975
976 SELECT PJI_REP_RA3 ( ORGANIZATION_ID
977 , EXP_ORGANIZATION_ID
978 ,SUM(AVAILABILITY)
979 ,SUM(CUR_AVL_1)
980 + SUM(CUR_AVL_2)
981 + SUM(CUR_AVL_3)
982 + SUM(CUR_AVL_4)
983 + SUM(CUR_AVL_5)
984 ,SUM(TOTAL_RES_COUNT)
985 , 0
986 ,SUM(CUR_AVL_1)
987 ,SUM(CUR_AVL_2)
988 ,SUM(CUR_AVL_3)
989 ,SUM(CUR_AVL_4)
990 ,SUM(CUR_AVL_5)
991 , 0
992 , 0
993 , 0
994 , 0
995 , 0
996 , 0
997 , 0
998 , 0
999 , 0)
1000 BULK COLLECT INTO L_AVL_RES_DUR_TBL
1001 FROM (
1002 SELECT /*+ ORDERED */
1003 HORG.NAME ORGANIZATION_ID
1004 , HORG.ID EXP_ORGANIZATION_ID
1005 , DECODE (L_THRESHOLD, 1, available_res_count_bkt1_s,
1006 2, available_res_count_bkt2_s,
1007 3, available_res_count_bkt3_s,
1008 4, available_res_count_bkt4_s,
1009 5, available_res_count_bkt5_s) AVAILABILITY
1010 , 0 TOTAL_RES_COUNT
1011 , 0 CUR_AVL_1
1012 , 0 CUR_AVL_2
1013 , 0 CUR_AVL_3
1014 , 0 CUR_AVL_4
1015 , 0 CUR_AVL_5
1016 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1017 PJI_RM_ORGO_F_MV FCT,
1018 PJI_PMV_ORG_DIM_TMP HOU
1019 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1020 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1021 AND FCT.PERIOD_TYPE_ID = l_DAY_PERIOD_TYPE
1022 AND FCT.CALENDAR_TYPE = l_DAY_CALENDAR_TYPE
1023 AND FCT.TIME_ID = L_AS_OF_DATE
1024 UNION ALL
1025 SELECT /*+ ORDERED */
1026 HORG.NAME ORGANIZATION_ID
1027 , HORG.ID EXP_ORGANIZATION_ID
1028 , 0 AVAILABILITY
1029 , TOTAL_RES_COUNT TOTAL_RES_COUNT
1030 , DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_1_CM,
1031 'CONSECUTIVE', BCKT_1_CS, 0) CUR_AVL_1
1032 , DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_2_CM,
1033 'CONSECUTIVE', BCKT_2_CS, 0) CUR_AVL_2
1034 , DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_3_CM,
1035 'CONSECUTIVE', BCKT_3_CS, 0) CUR_AVL_3
1036 , DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_4_CM,
1037 'CONSECUTIVE', BCKT_4_CS, 0) CUR_AVL_4
1038 , DECODE (L_AVL_TYPE, 'CUMULATIVE', BCKT_5_CM,
1039 'CONSECUTIVE', BCKT_5_CS, 0) CUR_AVL_5
1040 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1041 PJI_PMV_TIME_DIM_TMP TIME,
1042 PJI_AV_ORGO_F_MV FCT,
1043 PJI_PMV_ORG_DIM_TMP HOU
1044 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1045 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1046 AND FCT.TIME_ID = TIME.ID
1047 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1048 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1049 AND FCT.THRESHOLD = L_THRESHOLD
1050 AND FCT.AS_OF_DATE <= L_AS_OF_DATE
1051 AND TIME.AMOUNT_TYPE=2
1052 UNION ALL
1053 SELECT /*+ ORDERED */
1054 NAME
1055 , ID
1056 , 0 AVAILABILITY
1057 , 0 TOTAL_RES_COUNT
1058 , 0 CUR_AVL_1
1059 , 0 CUR_AVL_2
1060 , 0 CUR_AVL_3
1061 , 0 CUR_AVL_4
1062 , 0 CUR_AVL_5
1063 FROM PJI_PMV_ORGZ_DIM_TMP HORG
1064 WHERE NAME <> '-1')
1065 GROUP BY ORGANIZATION_ID,
1066 EXP_ORGANIZATION_ID;
1067
1068 -- ********************************************
1069
1070 FOR I IN 1..L_AVL_RES_DUR_TBL.COUNT
1071 LOOP
1072 IF L_AVL_RES_DUR_TBL(I).ORGANIZATION_ID = L_TOP_ORGANIZATION_NAME THEN
1073 L_TOP_ORG_INDEX:=I;
1074
1075 L_TO_CURR_AVL_RES := NVL(L_AVL_RES_DUR_TBL(I).CURR_AVL_RES, 0);
1076 L_TO_TOTAL_AVL_RES := NVL(L_AVL_RES_DUR_TBL(I).TOTAL_AVL_RES, 0);
1077 L_TO_TOTAL_RESOURSES := NVL(L_AVL_RES_DUR_TBL(I).TOTAL_RESOURCES, 0);
1078 L_TO_AVL_1_5_DAYS := NVL(L_AVL_RES_DUR_TBL(I).BUCKET1, 0);
1079 L_TO_AVL_6_10_DAYS := NVL(L_AVL_RES_DUR_TBL(I).BUCKET2, 0);
1080 L_TO_AVL_11_15_DAYS := NVL(L_AVL_RES_DUR_TBL(I).BUCKET3, 0);
1081 L_TO_AVL_16_20_DAYS := NVL(L_AVL_RES_DUR_TBL(I).BUCKET4, 0);
1082 L_TO_AVL_MORE_THAN_20_DAYS := NVL(L_AVL_RES_DUR_TBL(I).BUCKET5, 0);
1083
1084 ELSE
1085 L_CURR_AVL_RES :=L_CURR_AVL_RES
1086 + NVL(L_AVL_RES_DUR_TBL(I).CURR_AVL_RES, 0);
1087
1088 L_TOTAL_AVL_RES := L_TOTAL_AVL_RES
1089 + NVL(L_AVL_RES_DUR_TBL(I).TOTAL_AVL_RES, 0);
1090
1091 L_TOTAL_RESOURSES :=L_TOTAL_RESOURSES
1092 + NVL(L_AVL_RES_DUR_TBL(I).TOTAL_RESOURCES, 0);
1093
1094 L_AVL_1_5_DAYS :=L_AVL_1_5_DAYS
1095 + NVL(L_AVL_RES_DUR_TBL(I).BUCKET1, 0);
1096
1097 L_AVL_6_10_DAYS :=L_AVL_6_10_DAYS
1098 + NVL(L_AVL_RES_DUR_TBL(I).BUCKET2, 0);
1099
1100 L_AVL_11_15_DAYS :=L_AVL_11_15_DAYS
1101 + NVL(L_AVL_RES_DUR_TBL(I).BUCKET3, 0);
1102
1103 L_AVL_16_20_DAYS :=L_AVL_16_20_DAYS
1104 + NVL(L_AVL_RES_DUR_TBL(I).BUCKET4, 0);
1105
1106 L_AVL_MORE_THAN_20_DAYS :=L_AVL_MORE_THAN_20_DAYS
1107 + NVL(L_AVL_RES_DUR_TBL(I).BUCKET5, 0);
1108
1109 END IF;
1110 END LOOP;
1111
1112 IF NVL(L_TOP_ORG_INDEX, 0) <>0 THEN
1113 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES
1114 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES,0)
1115 -L_CURR_AVL_RES;
1116
1117 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_AVL_RES
1118 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_AVL_RES,0)
1119 -L_TOTAL_AVL_RES;
1120
1121 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES
1122 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES,0)
1123 -L_TOTAL_RESOURSES;
1124
1125 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET1
1126 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET1,0)
1127 -L_AVL_1_5_DAYS;
1128
1129 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET2
1130 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET2,0)
1131 -L_AVL_6_10_DAYS;
1132
1133 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET3
1134 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET3,0)
1135 -L_AVL_11_15_DAYS;
1136
1137 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET4
1138 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET4,0)
1139 -L_AVL_16_20_DAYS;
1140
1141 L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET5
1142 := NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET5,0)
1143 -L_AVL_MORE_THAN_20_DAYS;
1144
1145 IF
1146 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).CURR_AVL_RES ,0) =0 AND
1147 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_AVL_RES ,0) =0 AND
1148 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_RESOURCES ,0) =0 AND
1149 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).TOTAL_AVL_RES_PERCENT ,0) =0 AND
1150 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET1 ,0) =0 AND
1151 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET2 ,0) =0 AND
1152 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET3 ,0) =0 AND
1153 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET4 ,0) =0 AND
1154 NVL(L_AVL_RES_DUR_TBL(L_TOP_ORG_INDEX).BUCKET5 ,0) =0
1155 THEN
1156 L_AVL_RES_DUR_TBL.DELETE(L_TOP_ORG_INDEX);
1157 END IF;
1158
1159 L_CURR_AVL_RES :=L_TO_CURR_AVL_RES;
1160 L_TOTAL_AVL_RES :=L_TO_TOTAL_AVL_RES;
1161 L_TOTAL_RESOURSES :=L_TO_TOTAL_RESOURSES;
1162 L_AVL_1_5_DAYS :=L_TO_AVL_1_5_DAYS;
1163 L_AVL_6_10_DAYS :=L_TO_AVL_6_10_DAYS;
1164 L_AVL_11_15_DAYS :=L_TO_AVL_11_15_DAYS;
1165 L_AVL_16_20_DAYS :=L_TO_AVL_16_20_DAYS;
1166 L_AVL_MORE_THAN_20_DAYS :=L_TO_AVL_MORE_THAN_20_DAYS;
1167
1168 END IF;
1169
1170 IF L_AVL_RES_DUR_TBL.COUNT > 0 THEN
1171 FOR I IN L_AVL_RES_DUR_TBL.FIRST..L_AVL_RES_DUR_TBL.LAST
1172 LOOP
1173 IF L_AVL_RES_DUR_TBL.EXISTS(I) THEN
1174 IF NVL(L_AVL_RES_DUR_TBL(I).TOTAL_RESOURCES, 0)=0 THEN
1175 L_AVL_RES_DUR_TBL(I).TOTAL_AVL_RES_PERCENT:= NULL;
1176 ELSE
1177 L_AVL_RES_DUR_TBL(I).TOTAL_AVL_RES_PERCENT:=
1178 (L_AVL_RES_DUR_TBL(I).TOTAL_AVL_RES
1179 /L_AVL_RES_DUR_TBL(I).TOTAL_RESOURCES)*100;
1180 END IF;
1181 END IF;
1182 END LOOP;
1183
1184 FOR I IN L_AVL_RES_DUR_TBL.FIRST..L_AVL_RES_DUR_TBL.LAST
1185 LOOP
1186 IF L_AVL_RES_DUR_TBL.EXISTS(I) THEN
1187 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_1 := L_CURR_AVL_RES ;
1188 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_2 := L_TOTAL_AVL_RES;
1189 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_3 := L_TOTAL_RESOURSES;
1190 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_5 := L_AVL_1_5_DAYS;
1191 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_6 := L_AVL_6_10_DAYS;
1192 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_7 := L_AVL_11_15_DAYS;
1193 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_8 := L_AVL_16_20_DAYS;
1194 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_9 := L_AVL_MORE_THAN_20_DAYS;
1195
1196
1197 IF NVL(L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_3, 0)=0 THEN
1198 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_4:= NULL;
1199 ELSE
1200 L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_4:=
1201 (L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_2
1202 /L_AVL_RES_DUR_TBL(I).PJI_REP_TOTAL_3)*100;
1203 END IF;
1204 END IF;
1205 END LOOP;
1206 END IF;
1207
1208 --*********************************************
1209
1210 COMMIT;
1211 RETURN L_AVL_RES_DUR_TBL;
1212
1213 END PLSQLDRIVER_RA3;
1214
1215
1216 /******************************************************
1217 * RA4: AVAILABILITY TREND REPORT FUNCTIONS
1218 ******************************************************/
1219 PROCEDURE GET_SQL_PJI_REP_RA4 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
1220 , X_PMV_SQL OUT NOCOPY VARCHAR2
1221 , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1222 IS
1223 BEGIN
1224
1225 PJI_PMV_ENGINE.GENERATE_SQL(P_PAGE_PARAMETER_TBL => P_PAGE_PARAMETER_TBL
1226 , P_SELECT_LIST =>
1227 'FACT.UNASSIGNED "PJI_REP_MSR_29",
1228 FACT.AVAILABLE "PJI_REP_MSR_28",
1229 FACT.SCHEDULED_UTIL "PJI_REP_MSR_30",
1230 FACT.CAPACITY "PJI_REP_MSR_3",
1231 FACT.CONFIRMED "PJI_REP_MSR_4",
1232 FACT.PROVISIONAL "PJI_REP_MSR_5",
1233 FACT.UNASSIGNED "PJI_REP_MSR_11",
1234 FACT.AVAILABLE "PJI_REP_MSR_12",
1235 FACT.AVAILABLE_URL "PJI_REP_MSR_22",
1236 FACT.PER_HRS_AVAILABLE "PJI_REP_MSR_13",
1237 FACT.SCHEDULED_UTIL_PER "PJI_REP_MSR_15"'
1238 ,P_SQL_STATEMENT => X_PMV_SQL
1239 ,P_PMV_OUTPUT => X_PMV_OUTPUT,
1240 P_REGION_CODE => 'PJI_REP_RA4',
1241 P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA4',
1242 P_PLSQL_DRIVER_PARAMS => '<<ORGANIZATION+FII_OPERATING_UNITS>>, ' ||
1243 '<<ORGANIZATION+PJI_ORGANIZATIONS>>, ' ||
1244 '<<AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD>>, ' ||
1245 '<<AS_OF_DATE>>, ' ||
1246 '<<PERIOD_TYPE>>, ' ||
1247 '<<VIEW_BY>>');
1248 END GET_SQL_PJI_REP_RA4;
1249
1250 FUNCTION PLSQLDRIVER_RA4 (
1251 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
1252 P_ORGANIZATION IN VARCHAR2,
1253 P_THRESHOLD IN NUMBER,
1254 P_AS_OF_DATE IN NUMBER,
1255 P_PERIOD_TYPE IN VARCHAR2,
1256 P_VIEW_BY IN VARCHAR2
1257 ) RETURN PJI_REP_RA4_TBL
1258 IS
1259 PRAGMA AUTONOMOUS_TRANSACTION;
1260 L_TOP_ORGANIZATION_NAME VARCHAR2(240);
1261 L_RA4_TBL PJI_REP_RA4_TBL := PJI_REP_RA4_TBL();
1262 L_THRESHOLD NUMBER:=P_THRESHOLD;
1263 BEGIN
1264
1265 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT, P_VIEW_BY);
1266
1267 PJI_PMV_ENGINE.CONVERT_ORGANIZATION ( P_TOP_ORGANIZATION_ID => P_ORGANIZATION
1268 , P_VIEW_BY => P_VIEW_BY
1269 , P_TOP_ORGANIZATION_NAME => L_TOP_ORGANIZATION_NAME);
1270
1271 PJI_PMV_ENGINE.CONVERT_TIME ( P_AS_OF_DATE => P_AS_OF_DATE
1272 , P_PERIOD_TYPE => P_PERIOD_TYPE
1273 , P_VIEW_BY => P_VIEW_BY
1274 , P_PARSE_PRIOR => NULL
1275 , P_REPORT_TYPE => NULL
1276 , P_COMPARATOR => NULL
1277 , P_PARSE_ITD => NULL
1278 , P_FULL_PERIOD_FLAG => 'Y');
1279
1280 -- GET THE LABOR UNITS
1281 IF G_LABOUR_UNITS IS NULL THEN
1282 BEGIN
1283 SELECT REPORT_LABOR_UNITS
1284 INTO G_LABOUR_UNITS
1285 FROM PJI_SYSTEM_SETTINGS;
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 G_LABOUR_UNITS := NULL;
1289 END;
1290 END IF;
1291
1292 IF P_THRESHOLD IS NULL THEN
1293 BEGIN
1294 SELECT DISTINCT
1295 FIRST_VALUE(ID) OVER (ORDER BY VALUE DESC) INTO L_THRESHOLD
1296 FROM PJI_AVL_THRESHOLDS_V;
1297 END;
1298 END IF;
1299
1300
1301 SELECT PJI_REP_RA4(
1302 ORG_ID
1303 ,ORGANIZATION_ID
1304 ,TIME_ID
1305 ,TIME_KEY
1306 ,SUM(CAPACITY)
1307 ,SUM(CONFIRMED)
1308 ,SUM(PROVISIONAL)
1309 ,SUM(UNASSIGNED)
1310 ,SUM(SCHEDULED_UTIL)
1311 ,SUM(DECODE(L_THRESHOLD, 1, AVL_BKT1
1312 , 2, AVL_BKT2
1313 , 3, AVL_BKT3
1314 , 4, AVL_BKT4
1315 , 5, AVL_BKT5
1316 , 0))
1317 ,PJI_PMV_UTIL.ra4_ra5_url(TIME_ID, p_organization, p_operating_unit, l_threshold, p_period_type)
1318 ,NULL
1319 ,NULL)
1320 BULK COLLECT INTO L_RA4_TBL
1321 FROM ( /* Bug 3515594 */
1322 SELECT /*+ ORDERED */
1323 HOU.NAME ORG_ID
1324 ,HORG.NAME ORGANIZATION_ID
1325 ,TIME.NAME TIME_ID
1326 ,DECODE(P_VIEW_BY, 'TM', TIME.ORDER_BY_ID, -1) TIME_KEY
1327 ,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
1328 CAPACITY
1329 ,(CONF_HRS_S - CONF_OVERCOM_HRS_S) / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
1330 CONFIRMED
1331 ,(PROV_HRS_S - PROV_OVERCOM_HRS_S) / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
1332 PROVISIONAL
1333 ,UNASSIGNED_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
1334 UNASSIGNED
1335
1336 ,CONF_WTD_ORG_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1)
1337 SCHEDULED_UTIL
1338 ,(CASE WHEN (AVAILABLE_RES_COUNT_BKT1_S = 0)
1339 THEN 0 ELSE AVAILABLE_HRS_BKT1_S /
1340 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT1
1341 ,(CASE WHEN (AVAILABLE_RES_COUNT_BKT2_S = 0)
1342 THEN 0 ELSE AVAILABLE_HRS_BKT2_S /
1343 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT2
1344 ,(CASE WHEN (AVAILABLE_RES_COUNT_BKT3_S = 0)
1345 THEN 0 ELSE AVAILABLE_HRS_BKT3_S /
1346 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT3
1347 ,(CASE WHEN (AVAILABLE_RES_COUNT_BKT4_S = 0)
1348 THEN 0 ELSE AVAILABLE_HRS_BKT4_S /
1349 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT4
1350 ,(CASE WHEN (AVAILABLE_RES_COUNT_BKT5_S = 0)
1351 THEN 0 ELSE AVAILABLE_HRS_BKT5_S /
1352 DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) END) AVL_BKT5
1353
1354 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1355 PJI_PMV_TIME_DIM_TMP TIME,
1356 PJI_RM_ORGO_F_MV FCT,
1357 PJI_PMV_ORG_DIM_TMP HOU,
1358 PA_IMPLEMENTATIONS_ALL IMP
1359 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1360 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1361 AND HOU.ID = IMP.ORG_ID
1362 AND FCT.TIME_ID = TIME.ID
1363 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1364 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1365 AND (TIME.AMOUNT_TYPE = 2
1366 OR TIME.AMOUNT_TYPE IS NULL)
1367 UNION ALL
1368 SELECT '-1' ORG_ID
1369 ,'-1' ORGANIZATION_ID
1370 ,NAME TIME_ID
1371 ,ORDER_BY_ID TIME_KEY
1372 ,0 CAPACITY
1373 ,0 CONFIRMED
1374 ,0 PROVISIONAL
1375 ,0 UNASSIGNED
1376 ,0 SCHEDULED_UTIL
1377 ,0 AVL_BKT1
1378 ,0 AVL_BKT2
1379 ,0 AVL_BKT3
1380 ,0 AVL_BKT4
1381 ,0 AVL_BKT5
1382 FROM PJI_PMV_TIME_DIM_TMP
1383 WHERE NAME <> '-1'
1384 ) GROUP BY ORG_ID
1385 ,ORGANIZATION_ID
1386 ,TIME_KEY
1387 ,TIME_ID ORDER BY TIME_KEY ASC;
1388
1389 FOR I IN 1..L_RA4_TBL.COUNT
1390 LOOP
1391 IF NVL(L_RA4_TBL(I).CAPACITY,0) <> 0 THEN
1392 L_RA4_TBL(I).PER_HRS_AVAILABLE := (L_RA4_TBL(I).AVAILABLE/L_RA4_TBL(I).CAPACITY)*100;
1393 L_RA4_TBL(I).SCHEDULED_UTIL_PER := (L_RA4_TBL(I).SCHEDULED_UTIL/L_RA4_TBL(I).CAPACITY)*100;
1394 ELSE
1395 L_RA4_TBL(I).PER_HRS_AVAILABLE := NULL;
1396 L_RA4_TBL(I).SCHEDULED_UTIL_PER := NULL;
1397 END IF;
1398 END LOOP;
1399 COMMIT;
1400
1401 RETURN L_RA4_TBL;
1402
1403 END PLSQLDRIVER_RA4;
1404
1405
1406 /****************************************************
1407 * RA5: AVAILABLE RESOURCE DETAILS REPORT FUNCTIONS
1408 ****************************************************/
1409
1410 PROCEDURE GET_SQL_PJI_REP_RA5 (P_PAGE_PARAMETER_TBL IN BIS_PMV_PAGE_PARAMETER_TBL
1411 , X_PMV_SQL OUT NOCOPY VARCHAR2
1412 , X_PMV_OUTPUT OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1413 IS
1414 BEGIN
1415
1416 PJI_PMV_ENGINE.GENERATE_SQL(P_PAGE_PARAMETER_TBL => P_PAGE_PARAMETER_TBL
1417 ,P_SELECT_LIST =>
1418 'FACT.PERSON_NAME "VIEWBY",
1419 FACT.JOB_LEVEL "PJI_REP_MSR_2",
1420 FACT.CAPACITY "PJI_REP_MSR_3",
1421 FACT.CONFIRMED "PJI_REP_MSR_4",
1422 FACT.PROVISIONAL "PJI_REP_MSR_5",
1423 FACT.UNASSIGNED "PJI_REP_MSR_11",
1424 FACT.AVAILABLE_HOURS "PJI_REP_MSR_12",
1425 FACT.ACT_UTIL_PER "PJI_REP_MSR_13",
1426 FACT.SCH_UTIL_PER "PJI_REP_MSR_14",
1427 TO_DATE(FACT.AVAILABLE_SINCE, ''RRRR/MM/DD'') "PJI_REP_MSR_15",
1428 FACT.CURR_LAST_PROJ "PJI_REP_MSR_16",
1429 FACT.NEXT_PROJ "PJI_REP_MSR_17",
1430 TO_DATE(FACT.NEXT_ASGMT_DATE, ''RRRR/MM/DD'') "PJI_REP_MSR_18",
1431 FACT.PERSON_ID "PJI_REP_MSR_25",
1432 FACT.REDUCE_CAP_S "PJI_REP_MSR_26",
1433 FACT.REDUCE_CAP_A "PJI_REP_MSR_27",
1434 FACT.TOT_WTD_A "PJI_REP_MSR_29",
1435 FACT.CONF_WTD_S "PJI_REP_MSR_30"'
1436 ,P_SQL_STATEMENT => X_PMV_SQL
1437 ,P_PMV_OUTPUT => X_PMV_OUTPUT,
1438 P_REGION_CODE => 'PJI_REP_RA5',
1439 P_PLSQL_DRIVER => 'PJI_PMV_AVL.PLSQLDRIVER_RA5',
1440 P_PLSQL_DRIVER_PARAMS => '<<ORGANIZATION+FII_OPERATING_UNITS>>, ' ||
1441 '<<ORGANIZATION+PJI_ORGANIZATIONS>>, ' ||
1442 '<<AVAILABILITY_THRESHOLD+AVAILABILITY_THRESHOLD>>, ' ||
1443 '<<AVAILABILITY_DAYS+AVAILABILITY_DAYS>>, ' ||
1444 '<<AS_OF_DATE>>, ' ||
1445 '<<PERIOD_TYPE>>, '||
1446 '<<VIEW_BY>>');
1447 END GET_SQL_PJI_REP_RA5;
1448
1449 FUNCTION PLSQLDRIVER_RA5 (
1450 P_OPERATING_UNIT IN VARCHAR2 DEFAULT NULL,
1451 P_ORGANIZATION IN VARCHAR2,
1452 P_THRESHOLD IN NUMBER,
1453 P_AVL_DAYS IN NUMBER,
1454 P_AS_OF_DATE IN NUMBER,
1455 P_PERIOD_TYPE IN VARCHAR2,
1456 P_VIEW_BY IN VARCHAR2
1457 ) RETURN PJI_REP_RA5_TBL
1458 IS
1459 PRAGMA AUTONOMOUS_TRANSACTION;
1460 L_AVL_RES_DET_TBL PJI_REP_RA5_TBL := PJI_REP_RA5_TBL();
1461 L_AS_OF_DATE NUMBER := P_AS_OF_DATE;
1462 L_MIN_DAYS NUMBER;
1463 L_MAX_DAYS NUMBER;
1464 G_LABOUR_UNITS VARCHAR2(40);
1465 L_DAY_PERIOD_TYPE NUMBER:=1;
1466 L_DAY_CALENDAR_TYPE VARCHAR2(1):='C';
1467 BEGIN
1468
1469 PJI_PMV_ENGINE.CONVERT_OPERATING_UNIT (P_OPERATING_UNIT, P_VIEW_BY);
1470 PJI_PMV_ENGINE.CONVERT_ORGANIZATION (P_ORGANIZATION, P_VIEW_BY);
1471 PJI_PMV_ENGINE.CONVERT_TIME ( P_AS_OF_DATE => P_AS_OF_DATE
1472 , P_PERIOD_TYPE => P_PERIOD_TYPE
1473 , P_VIEW_BY => P_VIEW_BY
1474 , P_PARSE_PRIOR => NULL
1475 , P_REPORT_TYPE => NULL
1476 , P_COMPARATOR => NULL
1477 , P_PARSE_ITD => NULL
1478 , P_FULL_PERIOD_FLAG => 'Y');
1479
1480 -- SET THE MIN_DAYS AND MAX_DAYS VARIABLES,
1481 -- DETERMINED BY THE AVAILABLE_DAYS PARAMETER
1482
1483 BEGIN
1484 SELECT FROM_VALUE FROM_VALUE
1485 , NVL(TO_VALUE,POWER(2,32)) TO_VALUE
1486 INTO L_MIN_DAYS
1487 , L_MAX_DAYS
1488 FROM PJI_MT_BUCKETS
1489 WHERE
1490 BUCKET_SET_CODE = 'PJI_RES_AVL_DAYS'
1491 AND SEQ = P_AVL_DAYS;
1492 EXCEPTION
1493 WHEN NO_DATA_FOUND THEN
1494 L_MIN_DAYS:= 1;
1495 L_MAX_DAYS:= POWER(2, 32);
1496 END;
1497
1498 -- GET THE LABOR UNITS
1499 IF G_LABOUR_UNITS IS NULL THEN
1500 BEGIN
1501 SELECT REPORT_LABOR_UNITS
1502 INTO G_LABOUR_UNITS
1503 FROM PJI_SYSTEM_SETTINGS;
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 G_LABOUR_UNITS := NULL;
1507 END;
1508 END IF;
1509
1510
1511 SELECT PJI_REP_RA5(
1512 PERSON_NAME
1513 ,PERSON_ID
1514 ,NULL
1515 ,SUM(CAPACITY)
1516 ,SUM(CAPACITY_A)
1517 ,SUM(CONFIRMED)
1518 ,SUM(PROVISIONAL)
1519 ,SUM(UNASSIGNED)
1520 ,SUM(AVAILABLE)
1521 ,SUM(AVAILABLE_HOURS)
1522 ,SUM(AVAILABLE_DAYS)
1523 ,SUM(TOT_WTD_A)
1524 ,SUM(CONF_WTD_S)
1525 ,SUM(REDUCE_CAP_A)
1526 ,SUM(REDUCE_CAP_S)
1527 ,NULL
1528 ,NULL
1529 ,NULL
1530 ,NULL
1531 ,NULL
1532 ,NULL)
1533 BULK COLLECT INTO L_AVL_RES_DET_TBL
1534 FROM ( /* Bug 3515594 */
1535 SELECT /*+ ORDERED */
1536 NULL PERSON_NAME
1537 ,FCT.PERSON_ID PERSON_ID
1538 ,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY
1539 ,0 CAPACITY_A
1540 ,CONF_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CONFIRMED
1541 ,PROV_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) PROVISIONAL
1542 ,UNASSIGNED_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) UNASSIGNED
1543 ,0 TOT_WTD_A
1544 ,CONF_WTD_ORG_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CONF_WTD_S
1545 ,0 REDUCE_CAP_A
1546 ,REDUCE_CAPACITY_HRS_S / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_S
1547 ,0 AVAILABLE
1548 ,DECODE(P_THRESHOLD, 1, DECODE(NVL(AVAILABLE_RES_COUNT_BKT1_S, 0),0,0,AVAILABLE_HRS_BKT1_S)
1549 , 2, DECODE(NVL(AVAILABLE_RES_COUNT_BKT2_S, 0),0,0,AVAILABLE_HRS_BKT2_S)
1550 , 3, DECODE(NVL(AVAILABLE_RES_COUNT_BKT3_S, 0),0,0,AVAILABLE_HRS_BKT3_S)
1551 , 4, DECODE(NVL(AVAILABLE_RES_COUNT_BKT4_S, 0),0,0,AVAILABLE_HRS_BKT4_S)
1552 , 5, DECODE(NVL(AVAILABLE_RES_COUNT_BKT5_S, 0),0,0,AVAILABLE_HRS_BKT5_S)
1553 ,DECODE(NVL(AVAILABLE_RES_COUNT_BKT5_S, 0),0,0,AVAILABLE_HRS_BKT5_S)
1554 ) / DECODE(G_LABOUR_UNITS , 'DAYS', IMP.FTE_DAY
1555 , 'WEEKS', IMP.FTE_WEEK
1556 , 1) AVAILABLE_HOURS
1557 ,DECODE(P_THRESHOLD, 1, AVAILABLE_RES_COUNT_BKT1_S
1558 , 2, AVAILABLE_RES_COUNT_BKT2_S
1559 , 3, AVAILABLE_RES_COUNT_BKT3_S
1560 , 4, AVAILABLE_RES_COUNT_BKT4_S
1561 , 5, AVAILABLE_RES_COUNT_BKT5_S
1562 , AVAILABLE_RES_COUNT_BKT5_S
1563 ) AVAILABLE_DAYS
1564 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1565 PJI_PMV_TIME_DIM_TMP TIME,
1566 PJI_RM_RES_F FCT,
1567 PJI_PMV_ORG_DIM_TMP HOU,
1568 PA_IMPLEMENTATIONS_ALL IMP
1569 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1570 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1571 AND FCT.TIME_ID = TIME.ID
1572 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1573 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1574 AND TIME.AMOUNT_TYPE = 2
1575 AND HOU.ID = IMP.ORG_ID
1576 UNION ALL
1577 /* Bug 3515594 */
1578 SELECT /*+ ORDERED */
1579 NULL PERSON_NAME
1580 ,FCT.PERSON_ID PERSON_ID
1581 ,0 CAPACITY
1582 ,CAPACITY_HRS / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) CAPACITY_A
1583 ,0 CONFIRMED
1584 ,0 PROVISIONAL
1585 ,0 UNASSIGNED
1586 ,TOTAL_WTD_ORG_HRS_A / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) TOT_WTD_A
1587 ,0 CONF_WTD_S
1588 ,REDUCE_CAPACITY_HRS_A / DECODE(G_LABOUR_UNITS, 'DAYS', IMP.FTE_DAY, 'WEEKS',IMP.FTE_WEEK, 1) REDUCE_CAP_A
1589 ,0 REDUCE_CAP_S
1590 ,0 AVAILABLE
1591 ,0 AVAILABLE_HOURS
1592 ,0 AVAILABLE_DAYS
1593 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1594 PJI_PMV_TIME_DIM_TMP TIME,
1595 PJI_RM_RES_F FCT,
1596 PJI_PMV_ORG_DIM_TMP HOU,
1597 PA_IMPLEMENTATIONS_ALL IMP
1598 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1599 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1600 AND FCT.TIME_ID = TIME.ID
1601 AND FCT.PERIOD_TYPE_ID = TIME.PERIOD_TYPE
1602 AND FCT.CALENDAR_TYPE = TIME.CALENDAR_TYPE
1603 AND TIME.AMOUNT_TYPE = 1
1604 AND HOU.ID = IMP.ORG_ID
1605 UNION ALL
1606 SELECT /*+ ORDERED */
1607 NULL PERSON_NAME
1608 ,FCT.PERSON_ID PERSON_ID
1609 ,0 CAPACITY
1610 ,0 CAPACITY_A
1611 ,0 CONFIRMED
1612 ,0 PROVISIONAL
1613 ,0 UNASSIGNED
1614 ,0 TOT_WTD_A
1615 ,0 CONF_WTD_S
1616 ,0 REDUCE_CAP_A
1617 ,0 REDUCE_CAP_S
1618 ,DECODE(P_THRESHOLD, 1, AVAILABLE_RES_COUNT_BKT1_S
1619 , 2, AVAILABLE_RES_COUNT_BKT2_S
1620 , 3, AVAILABLE_RES_COUNT_BKT3_S
1621 , 4, AVAILABLE_RES_COUNT_BKT4_S
1622 , 5, AVAILABLE_RES_COUNT_BKT5_S
1623 , AVAILABLE_RES_COUNT_BKT5_S
1624 ) AVAILABLE
1625
1626 ,0 AVAILABLE_HOURS
1627 ,0 AVAILABLE_DAYS
1628 FROM PJI_PMV_ORGZ_DIM_TMP HORG,
1629 PJI_RM_RES_F FCT,
1630 PJI_PMV_ORG_DIM_TMP HOU
1631 WHERE FCT.EXPENDITURE_ORG_ID = HOU.ID
1632 AND FCT.EXPENDITURE_ORGANIZATION_ID = HORG.ID
1633 AND FCT.TIME_ID = P_AS_OF_DATE
1634 AND FCT.PERIOD_TYPE_ID = L_DAY_PERIOD_TYPE
1635 AND FCT.CALENDAR_TYPE = L_DAY_CALENDAR_TYPE
1636 )
1637 GROUP BY PERSON_NAME, PERSON_ID
1638 HAVING SUM(AVAILABLE_DAYS) BETWEEN L_MIN_DAYS AND L_MAX_DAYS
1639 ORDER BY PERSON_ID;
1640
1641 -- CHECK THAT AT LEAST ONE RECORD EXISTS IN THE COLLECTION
1642 -- BEFORE GOING INTO THE LOOP
1643 FOR I IN 1..L_AVL_RES_DET_TBL.COUNT LOOP
1644
1645 -- GET PERSON_ID OF THE RECORD TO GET THE NAME
1646 -- FROM PA_RESOURCES_DENORM
1647 BEGIN
1648 SELECT RESOURCE_NAME
1649 INTO L_AVL_RES_DET_TBL(I).PERSON_NAME
1650 FROM PA_RESOURCES_DENORM
1651 WHERE PERSON_ID = L_AVL_RES_DET_TBL(I).PERSON_ID
1652 AND ROWNUM=1;
1653 EXCEPTION
1654 WHEN NO_DATA_FOUND THEN
1655 NULL;
1656 END;
1657 -- NOTE TO VIJAY M.
1658 -- PLACE CALLS TO FOLLOWING API'S TO POPULATE THE COLUMNS
1659 -- (AVAILABLE_SINCE, CURR_LAST_PROJ, NEXT_PROJ, NEXT_ASGMT_DATE):
1660 -- PJI_PMV_UTIL.GET_AVAILABLE_FROM
1661 -- PJI_PMV_UTIL.GET_PROJECTS
1662 -- PJI_PMV_UTIL.GET_NEXT_ASGMT_DATE
1663 -- SINCE THE LOGIC FOR THE ABOVE API'S ARE BOUND TO CHANGE, THE
1664 -- CALLS TO ABOVE APIS ARE NOT BELOW.
1665 IF NVL((L_AVL_RES_DET_TBL(I).CAPACITY_A-L_AVL_RES_DET_TBL(I).REDUCE_CAP_A),0) <> 0 THEN
1666 L_AVL_RES_DET_TBL(I).ACT_UTIL_PER := (L_AVL_RES_DET_TBL(I).TOT_WTD_A/
1667 ((L_AVL_RES_DET_TBL(I).CAPACITY_A-L_AVL_RES_DET_TBL(I).REDUCE_CAP_A))*100);
1668 ELSE
1669 L_AVL_RES_DET_TBL(I).ACT_UTIL_PER := NULL;
1670 END IF;
1671
1672 IF NVL((L_AVL_RES_DET_TBL(I).CAPACITY-L_AVL_RES_DET_TBL(I).REDUCE_CAP_S),0) <> 0 THEN
1673 L_AVL_RES_DET_TBL(I).SCH_UTIL_PER := (L_AVL_RES_DET_TBL(I).CONF_WTD_S/
1674 ((L_AVL_RES_DET_TBL(I).CAPACITY-L_AVL_RES_DET_TBL(I).REDUCE_CAP_S))*100);
1675 ELSE
1676 L_AVL_RES_DET_TBL(I).ACT_UTIL_PER := NULL;
1677 END IF;
1678
1679 IF L_AVL_RES_DET_TBL(I).PERSON_ID IS NOT NULL THEN
1680 L_AVL_RES_DET_TBL(I).JOB_LEVEL := PJI_PMV_UTIL.GET_JOB_LEVEL( L_AVL_RES_DET_TBL(I).PERSON_ID, TO_DATE(P_AS_OF_DATE,'J'));
1681 ELSE
1682 L_AVL_RES_DET_TBL(I).JOB_LEVEL := NULL;
1683 END IF;
1684 END LOOP;
1685 IF L_AVL_RES_DET_TBL.COUNT > 0 THEN
1686 GET_AVAILABLE_SINCE_INFO(L_AVL_RES_DET_TBL, p_As_Of_Date, P_THRESHOLD);
1687 GET_CURRENT_LAST_PROJECT_INFO(L_AVL_RES_DET_TBL, p_As_Of_Date);
1688 GET_NEXT_ASSIGNMENT_INFO(L_AVL_RES_DET_TBL, p_As_Of_Date);
1689 END IF;
1690 COMMIT;
1691 RETURN L_AVL_RES_DET_TBL;
1692
1693 END PLSQLDRIVER_RA5;
1694
1695 PROCEDURE GET_PROJECT_INFO(P_PERSON_ID NUMBER, P_TIME_ID IN NUMBER, X_PROJECTS_NAME OUT NOCOPY VARCHAR2, X_BILLABLE_FLAG OUT VARCHAR2)
1696 AS
1697 l_Project_List VARCHAR2(300);
1698 l_Time_ID NUMBER:=P_TIME_ID;
1699 l_Period_Type_ID NUMBER:=1;
1700 l_Calendar_Type VARCHAR2(1):='C';
1701 l_Record_Type VARCHAR2(1):='A';
1702 BEGIN
1703 FOR cur_Projects_List IN (SELECT DISTINCT prj.name name
1704 FROM pji_rm_res_wt_f fct
1705 ,pa_projects_all prj
1706 WHERE
1707 prj.project_id = fct.project_id
1708 AND fct.person_id = p_Person_ID
1709 AND fct.time_id = l_Time_ID
1710 AND fct.period_type_id = l_Period_Type_ID
1711 AND fct.calendar_type = l_Calendar_Type
1712 AND fct.record_type = l_Record_Type)
1713 LOOP
1714 IF NVL(LENGTH(l_Project_List), 0) < 240 THEN
1715 IF l_Project_List IS NULL THEN
1716 l_Project_List := cur_Projects_List.name;
1717 ELSE
1718 l_Project_List := l_Project_List||','||cur_Projects_List.name;
1719 END IF;
1720 END IF;
1721 END LOOP;
1722 X_PROJECTS_NAME := SUBSTR(l_Project_List, 1, 240);
1723 END GET_PROJECT_INFO;
1724
1725 PROCEDURE GET_AVAILABLE_SINCE_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER, P_THRESHOLD NUMBER)
1726 AS
1727 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date, 'j');
1728 l_Week_Calendar_Type VARCHAR2(1):='E';
1729 l_Day_Calendar_Type VARCHAR2(1):='C';
1730 l_Week_Period_Type_ID NUMBER:=16;
1731 l_Day_Period_Type_ID NUMBER:=1;
1732 l_Num_of_Weeks NUMBER:=15;
1733 l_Max_Num_of_Weeks NUMBER:=52;
1734
1735 l_From_Time_ID NUMBER;
1736 l_To_Time_ID NUMBER;
1737 l_Start_Date DATE;
1738 l_Start_Week_ID NUMBER;
1739 l_Threshold NUMBER:=P_THRESHOLD;
1740 l_Available_Week NUMBER;
1741 l_Available_Since NUMBER;
1742
1743 l_Project_Date NUMBER;
1744 l_Billable_Flag VARCHAR2(1);
1745 BEGIN
1746 /* For Current Date */
1747 SELECT week_id
1748 , start_date
1749 INTO l_Start_Week_ID
1750 ,l_Start_date
1751 FROM
1752 fii_time_week
1753 WHERE
1754 l_As_Of_Date BETWEEN start_date AND end_date;
1755
1756 -- If threshold is null - that means that we have to pass the id for the maximum percentage value
1757 -- from the PJI setup
1758 -- Following snippet added because of bug4001112
1759
1760 IF P_THRESHOLD is null then
1761 SELECT distinct
1762 first_value(ID) over (ORDER BY VALUE DESC) into l_Threshold
1763 FROM PJI_AVL_THRESHOLDS_V;
1764 END IF;
1765 -- end of snippet (bug4001112)
1766
1767 FOR i IN 1..P_AVL_RES_DET_TBL.LAST
1768 LOOP
1769
1770 l_From_Time_ID:=TO_CHAR(l_As_Of_Date,'j');
1771 l_To_Time_ID:=TO_CHAR(l_Start_date,'j');
1772
1773 l_Available_Week:=NULL;
1774 l_Available_Since:=NULL;
1775
1776 CASE (l_Threshold)
1777 WHEN 1 THEN
1778 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
1779 INTO P_AVL_RES_DET_TBL(i).available_since
1780 FROM pji_rm_res_f
1781 WHERE 1=1
1782 AND person_id = P_AVL_RES_DET_TBL(i).person_id
1783 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
1784 AND calendar_type = l_Day_Calendar_Type
1785 AND period_type_id = l_Day_Period_Type_ID
1786 AND total_res_count <> available_res_count_bkt1_s
1787 AND capacity_hrs <> 0;
1788 WHEN 2 THEN
1789 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
1790 INTO P_AVL_RES_DET_TBL(i).available_since
1791 FROM pji_rm_res_f
1792 WHERE 1=1
1793 AND person_id = P_AVL_RES_DET_TBL(i).person_id
1794 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
1795 AND calendar_type = l_Day_Calendar_Type
1796 AND period_type_id = l_Day_Period_Type_ID
1797 AND total_res_count <> available_res_count_bkt2_s
1798 AND capacity_hrs <> 0;
1799 WHEN 3 THEN
1800 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
1801 INTO P_AVL_RES_DET_TBL(i).available_since
1802 FROM pji_rm_res_f
1803 WHERE 1=1
1804 AND person_id = P_AVL_RES_DET_TBL(i).person_id
1805 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
1806 AND calendar_type = l_Day_Calendar_Type
1807 AND period_type_id = l_Day_Period_Type_ID
1808 AND total_res_count <> available_res_count_bkt3_s
1809 AND capacity_hrs <> 0;
1810 WHEN 4 THEN
1811 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
1812 INTO P_AVL_RES_DET_TBL(i).available_since
1813 FROM pji_rm_res_f
1814 WHERE 1=1
1815 AND person_id = P_AVL_RES_DET_TBL(i).person_id
1816 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
1817 AND calendar_type = l_Day_Calendar_Type
1818 AND period_type_id = l_Day_Period_Type_ID
1819 AND total_res_count <> available_res_count_bkt4_s
1820 AND capacity_hrs <> 0;
1821 WHEN 5 THEN
1822 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
1823 INTO P_AVL_RES_DET_TBL(i).available_since
1824 FROM pji_rm_res_f
1825 WHERE 1=1
1826 AND person_id = P_AVL_RES_DET_TBL(i).person_id
1827 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
1828 AND calendar_type = l_Day_Calendar_Type
1829 AND period_type_id = l_Day_Period_Type_ID
1830 AND total_res_count <> available_res_count_bkt5_s
1831 AND capacity_hrs <> 0;
1832
1833 ELSE
1834 NULL;
1835 END CASE;
1836
1837 IF P_AVL_RES_DET_TBL(i).available_since IS NULL THEN
1838
1839 CASE (l_Threshold)
1840 WHEN 1 THEN
1841 SELECT MAX(TIME_ID)
1842 INTO l_Available_Week
1843 FROM pji_rm_res_f fct
1844 , fii_time_week time
1845 WHERE 1=1
1846 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
1847 AND time.end_date<=l_Start_Date
1848 AND fct.time_id = time.week_id
1849 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1850 AND fct.calendar_type = l_Week_Calendar_Type
1851 AND fct.period_type_id = l_Week_Period_Type_ID
1852 AND fct.total_res_count <> fct.available_res_count_bkt1_s
1853 AND fct.capacity_hrs <> 0;
1854 WHEN 2 THEN
1855 SELECT MAX(TIME_ID)
1856 INTO l_Available_Week
1857 FROM pji_rm_res_f fct
1858 , fii_time_week time
1859 WHERE 1=1
1860 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
1861 AND time.end_date<=l_Start_Date
1862 AND fct.time_id = time.week_id
1863 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1864 AND fct.calendar_type = l_Week_Calendar_Type
1865 AND fct.period_type_id = l_Week_Period_Type_ID
1866 AND fct.total_res_count <> fct.available_res_count_bkt2_s
1867 AND fct.capacity_hrs <> 0;
1868 WHEN 3 THEN
1869 SELECT MAX(TIME_ID)
1870 INTO l_Available_Week
1871 FROM pji_rm_res_f fct
1872 , fii_time_week time
1873 WHERE 1=1
1874 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
1875 AND time.end_date<=l_Start_Date
1876 AND fct.time_id = time.week_id
1877 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1878 AND fct.calendar_type = l_Week_Calendar_Type
1879 AND fct.period_type_id = l_Week_Period_Type_ID
1880 AND fct.total_res_count <> fct.available_res_count_bkt3_s
1881 AND fct.capacity_hrs <> 0;
1882 WHEN 4 THEN
1883 SELECT MAX(TIME_ID)
1884 INTO l_Available_Week
1885 FROM pji_rm_res_f fct
1886 , fii_time_week time
1887 WHERE 1=1
1888 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
1889 AND time.end_date<=l_Start_Date
1890 AND fct.time_id = time.week_id
1891 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1892 AND fct.calendar_type = l_Week_Calendar_Type
1893 AND fct.period_type_id = l_Week_Period_Type_ID
1894 AND fct.total_res_count <> fct.available_res_count_bkt4_s
1895 AND fct.capacity_hrs <> 0;
1896 WHEN 5 THEN
1897 SELECT MAX(TIME_ID)
1898 INTO l_Available_Week
1899 FROM pji_rm_res_f fct
1900 , fii_time_week time
1901 WHERE 1=1
1902 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
1903 AND time.end_date<=l_Start_Date
1904 AND fct.time_id = time.week_id
1905 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1906 AND fct.calendar_type = l_Week_Calendar_Type
1907 AND fct.period_type_id = l_Week_Period_Type_ID
1908 AND fct.total_res_count <> fct.available_res_count_bkt5_s
1909 AND fct.capacity_hrs <> 0;
1910 ELSE
1911 NULL;
1912 END CASE;
1913
1914 IF l_Available_Week IS NULL THEN
1915 CASE (l_Threshold)
1916 WHEN 1 THEN
1917 SELECT MAX(TIME_ID)
1918 INTO l_Available_Week
1919 FROM pji_rm_res_f fct
1920 , fii_time_week time
1921 WHERE 1=1
1922 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
1923 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
1924 AND fct.time_id = time.week_id
1925 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1926 AND fct.calendar_type = l_Week_Calendar_Type
1927 AND fct.period_type_id = l_Week_Period_Type_ID
1928 AND fct.total_res_count <> fct.available_res_count_bkt1_s
1929 AND fct.capacity_hrs <> 0;
1930 WHEN 2 THEN
1931 SELECT MAX(TIME_ID)
1932 INTO l_Available_Week
1933 FROM pji_rm_res_f fct
1934 , fii_time_week time
1935 WHERE 1=1
1936 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
1937 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
1938 AND fct.time_id = time.week_id
1939 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1940 AND fct.calendar_type = l_Week_Calendar_Type
1941 AND fct.period_type_id = l_Week_Period_Type_ID
1942 AND fct.total_res_count <> fct.available_res_count_bkt2_s
1943 AND fct.capacity_hrs <> 0;
1944 WHEN 3 THEN
1945 SELECT MAX(TIME_ID)
1946 INTO l_Available_Week
1947 FROM pji_rm_res_f fct
1948 , fii_time_week time
1949 WHERE 1=1
1950 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
1951 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
1952 AND fct.time_id = time.week_id
1953 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1954 AND fct.calendar_type = l_Week_Calendar_Type
1955 AND fct.period_type_id = l_Week_Period_Type_ID
1956 AND fct.total_res_count <> fct.available_res_count_bkt3_s
1957 AND fct.capacity_hrs <> 0;
1958 WHEN 4 THEN
1959 SELECT MAX(TIME_ID)
1960 INTO l_Available_Week
1961 FROM pji_rm_res_f fct
1962 , fii_time_week time
1963 WHERE 1=1
1964 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
1965 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
1966 AND fct.time_id = time.week_id
1967 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1968 AND fct.calendar_type = l_Week_Calendar_Type
1969 AND fct.period_type_id = l_Week_Period_Type_ID
1970 AND fct.total_res_count <> fct.available_res_count_bkt4_s
1971 AND fct.capacity_hrs <> 0;
1972 WHEN 5 THEN
1973 SELECT MAX(TIME_ID)
1974 INTO l_Available_Week
1975 FROM pji_rm_res_f fct
1976 , fii_time_week time
1977 WHERE 1=1
1978 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
1979 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
1980 AND fct.time_id = time.week_id
1981 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
1982 AND fct.calendar_type = l_Week_Calendar_Type
1983 AND fct.period_type_id = l_Week_Period_Type_ID
1984 AND fct.total_res_count <> fct.available_res_count_bkt5_s
1985 AND fct.capacity_hrs <> 0;
1986 ELSE
1987 NULL;
1988 END CASE;
1989 END IF;
1990
1991 IF l_Available_Week IS NOT NULL THEN
1992 SELECT TO_CHAR(start_date, 'j')
1993 , TO_CHAR(end_date, 'j')
1994 INTO
1995 l_To_Time_ID
1996 , l_From_Time_ID
1997 FROM FII_TIME_WEEK
1998 WHERE week_id = l_Available_Week;
1999
2000 CASE (l_Threshold)
2001 WHEN 1 THEN
2002 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
2003 INTO P_AVL_RES_DET_TBL(i).available_since
2004 FROM pji_rm_res_f
2005 WHERE 1=1
2006 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2007 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2008 AND calendar_type = l_Day_Calendar_Type
2009 AND period_type_id = l_Day_Period_Type_ID
2010 AND total_res_count <> available_res_count_bkt1_s
2011 AND capacity_hrs <> 0;
2012 WHEN 2 THEN
2013 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
2014 INTO P_AVL_RES_DET_TBL(i).available_since
2015 FROM pji_rm_res_f
2016 WHERE 1=1
2017 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2018 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2019 AND calendar_type = l_Day_Calendar_Type
2020 AND period_type_id = l_Day_Period_Type_ID
2021 AND total_res_count <> available_res_count_bkt2_s
2022 AND capacity_hrs <> 0;
2023 WHEN 3 THEN
2024 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
2025 INTO P_AVL_RES_DET_TBL(i).available_since
2026 FROM pji_rm_res_f
2027 WHERE 1=1
2028 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2029 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2030 AND calendar_type = l_Day_Calendar_Type
2031 AND period_type_id = l_Day_Period_Type_ID
2032 AND total_res_count <> available_res_count_bkt3_s
2033 AND capacity_hrs <> 0;
2034 WHEN 4 THEN
2035 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
2036 INTO P_AVL_RES_DET_TBL(i).available_since
2037 FROM pji_rm_res_f
2038 WHERE 1=1
2039 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2040 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2041 AND calendar_type = l_Day_Calendar_Type
2042 AND period_type_id = l_Day_Period_Type_ID
2043 AND total_res_count <> available_res_count_bkt4_s
2044 AND capacity_hrs <> 0;
2045 WHEN 5 THEN
2046 SELECT TO_CHAR(TO_DATE(MAX(TIME_ID),'j'),'RRRR/MM/DD')
2047 INTO P_AVL_RES_DET_TBL(i).available_since
2048 FROM pji_rm_res_f
2049 WHERE 1=1
2050 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2051 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2052 AND calendar_type = l_Day_Calendar_Type
2053 AND period_type_id = l_Day_Period_Type_ID
2054 AND total_res_count <> available_res_count_bkt5_s
2055 AND capacity_hrs <> 0;
2056 ELSE
2057 NULL;
2058 END CASE;
2059
2060 ELSE
2061 NULL;
2062 END IF;
2063 ELSE
2064 NULL;
2065 END IF;
2066
2067 IF P_AVL_RES_DET_TBL(i).available_since IS NOT NULL THEN
2068 l_From_Time_ID:=LEAST(TO_CHAR(TO_DATE(P_AVL_RES_DET_TBL(i).available_since,'RRRR/MM/DD')+7, 'j'),TO_CHAR(l_As_Of_Date, 'j'));
2069 l_To_Time_ID:=TO_CHAR(TO_DATE(P_AVL_RES_DET_TBL(i).available_since,'RRRR/MM/DD'), 'j');
2070 l_Available_Since:=l_To_Time_ID;
2071 CASE (l_Threshold)
2072 WHEN 1 THEN
2073 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2074 INTO P_AVL_RES_DET_TBL(i).available_since
2075 FROM pji_rm_res_f
2076 WHERE 1=1
2077 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2078 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2079 AND calendar_type = l_Day_Calendar_Type
2080 AND period_type_id = l_Day_Period_Type_ID
2081 AND total_res_count = available_res_count_bkt1_s
2082 AND capacity_hrs <> 0;
2083 WHEN 2 THEN
2084 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2085 INTO P_AVL_RES_DET_TBL(i).available_since
2086 FROM pji_rm_res_f
2087 WHERE 1=1
2088 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2089 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2090 AND calendar_type = l_Day_Calendar_Type
2091 AND period_type_id = l_Day_Period_Type_ID
2092 AND total_res_count = available_res_count_bkt2_s
2093 AND capacity_hrs <> 0;
2094 WHEN 3 THEN
2095 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2096 INTO P_AVL_RES_DET_TBL(i).available_since
2097 FROM pji_rm_res_f
2098 WHERE 1=1
2099 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2100 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2101 AND calendar_type = l_Day_Calendar_Type
2102 AND period_type_id = l_Day_Period_Type_ID
2103 AND total_res_count = available_res_count_bkt3_s
2104 AND capacity_hrs <> 0;
2105 WHEN 4 THEN
2106 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2107 INTO P_AVL_RES_DET_TBL(i).available_since
2108 FROM pji_rm_res_f
2109 WHERE 1=1
2110 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2111 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2112 AND calendar_type = l_Day_Calendar_Type
2113 AND period_type_id = l_Day_Period_Type_ID
2114 AND total_res_count = available_res_count_bkt4_s
2115 AND capacity_hrs <> 0;
2116 WHEN 5 THEN
2117 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2118 INTO P_AVL_RES_DET_TBL(i).available_since
2119 FROM pji_rm_res_f
2120 WHERE 1=1
2121 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2122 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2123 AND calendar_type = l_Day_Calendar_Type
2124 AND period_type_id = l_Day_Period_Type_ID
2125 AND total_res_count = available_res_count_bkt5_s
2126 AND capacity_hrs <> 0;
2127 ELSE
2128 NULL;
2129 END CASE;
2130 IF P_AVL_RES_DET_TBL(i).available_since IS NOT NULL THEN
2131 l_From_Time_ID:=TO_CHAR(l_As_Of_Date, 'j');
2132 l_To_Time_ID:=TO_CHAR(TO_DATE(P_AVL_RES_DET_TBL(i).available_since,'RRRR/MM/DD'), 'j');
2133
2134 CASE (l_Threshold)
2135 WHEN 1 THEN
2136 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2137 INTO P_AVL_RES_DET_TBL(i).available_since
2138 FROM pji_rm_res_f
2139 WHERE 1=1
2140 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2141 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2142 AND calendar_type = l_Day_Calendar_Type
2143 AND period_type_id = l_Day_Period_Type_ID
2144 AND total_res_count = available_res_count_bkt1_s
2145 AND capacity_hrs <> 0;
2146 WHEN 2 THEN
2147 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2148 INTO P_AVL_RES_DET_TBL(i).available_since
2149 FROM pji_rm_res_f
2150 WHERE 1=1
2151 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2152 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2153 AND calendar_type = l_Day_Calendar_Type
2154 AND period_type_id = l_Day_Period_Type_ID
2155 AND total_res_count = available_res_count_bkt2_s
2156 AND capacity_hrs <> 0;
2157 WHEN 3 THEN
2158 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2159 INTO P_AVL_RES_DET_TBL(i).available_since
2160 FROM pji_rm_res_f
2161 WHERE 1=1
2162 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2163 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2164 AND calendar_type = l_Day_Calendar_Type
2165 AND period_type_id = l_Day_Period_Type_ID
2166 AND total_res_count = available_res_count_bkt3_s
2167 AND capacity_hrs <> 0;
2168 WHEN 4 THEN
2169 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2170 INTO P_AVL_RES_DET_TBL(i).available_since
2171 FROM pji_rm_res_f
2172 WHERE 1=1
2173 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2174 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2175 AND calendar_type = l_Day_Calendar_Type
2176 AND period_type_id = l_Day_Period_Type_ID
2177 AND total_res_count = available_res_count_bkt4_s
2178 AND capacity_hrs <> 0;
2179 WHEN 5 THEN
2180 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2181 INTO P_AVL_RES_DET_TBL(i).available_since
2182 FROM pji_rm_res_f
2183 WHERE 1=1
2184 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2185 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2186 AND calendar_type = l_Day_Calendar_Type
2187 AND period_type_id = l_Day_Period_Type_ID
2188 AND total_res_count = available_res_count_bkt5_s
2189 AND capacity_hrs <> 0;
2190 ELSE
2191 NULL;
2192 END CASE;
2193 END IF;
2194 END IF;
2195 END LOOP;
2196 END GET_AVAILABLE_SINCE_INFO;
2197
2198 PROCEDURE GET_CURRENT_LAST_PROJECT_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER)
2199 AS
2200 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date, 'j');
2201 l_Week_Calendar_Type VARCHAR2(1):='E';
2202 l_Day_Calendar_Type VARCHAR2(1):='C';
2203 l_Week_Period_Type_ID NUMBER:=16;
2204 l_Day_Period_Type_ID NUMBER:=1;
2205 l_Num_of_Weeks NUMBER:=15;
2206 l_Max_Num_of_Weeks NUMBER:=52;
2207
2208 l_From_Time_ID NUMBER;
2209 l_To_Time_ID NUMBER;
2210 l_Start_Date DATE;
2211 l_Start_Week_ID NUMBER;
2212 l_Available_Week NUMBER;
2213 l_Available_Date DATE;
2214
2215 l_Project_Date NUMBER;
2216 l_Billable_Flag VARCHAR2(1);
2217 BEGIN
2218 /* For Current Date */
2219 SELECT week_id
2220 , start_date
2221 INTO l_Start_Week_ID
2222 ,l_Start_date
2223 FROM
2224 fii_time_week
2225 WHERE
2226 l_As_Of_Date BETWEEN start_date AND end_date;
2227
2228 FOR i IN 1..P_AVL_RES_DET_TBL.LAST
2229 LOOP
2230 l_Available_Week:=NULL;
2231 l_Available_Date:=NULL;
2232
2233 IF NVL(P_AVL_RES_DET_TBL(i).AVAILABLE, 0) = 1 THEN
2234
2235 l_From_Time_ID:=TO_CHAR(l_As_Of_Date,'j');
2236 l_To_Time_ID:=TO_CHAR(l_Start_date,'j');
2237
2238 SELECT TO_DATE(MAX(TIME_ID),'j')
2239 INTO l_Available_Date
2240 FROM pji_rm_res_f
2241 WHERE 1=1
2242 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2243 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2244 AND calendar_type = l_Day_Calendar_Type
2245 AND period_type_id = l_Day_Period_Type_ID
2246 AND capacity_hrs<>available_hrs_bkt1_s
2247 AND capacity_hrs <> 0;
2248
2249 IF l_Available_Date IS NULL THEN
2250 SELECT MAX(TIME_ID)
2251 INTO l_Available_Week
2252 FROM pji_rm_res_f fct
2253 , fii_time_week time
2254 WHERE 1=1
2255 AND time.start_date>=l_Start_Date-(7*l_Num_of_Weeks)
2256 AND time.end_date<=l_Start_Date
2257 AND fct.time_id = time.week_id
2258 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
2259 AND fct.calendar_type = l_Week_Calendar_Type
2260 AND fct.period_type_id = l_Week_Period_Type_ID
2261 AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
2262 AND fct.capacity_hrs <> 0;
2263
2264 IF l_Available_Week IS NULL THEN
2265 SELECT MAX(TIME_ID)
2266 INTO l_Available_Week
2267 FROM pji_rm_res_f fct
2268 , fii_time_week time
2269 WHERE 1=1
2270 AND time.start_date>=l_Start_Date-(7*l_Max_Num_of_Weeks)
2271 AND time.end_date<=l_Start_Date-(7*l_Num_of_Weeks)
2272 AND fct.time_id = time.week_id
2273 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
2274 AND fct.calendar_type = l_Week_Calendar_Type
2275 AND fct.period_type_id = l_Week_Period_Type_ID
2276 AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
2277 AND fct.capacity_hrs <> 0;
2278 END IF;
2279
2280 IF l_Available_Week IS NOT NULL THEN
2281 SELECT TO_CHAR(start_date, 'j')
2282 , TO_CHAR(end_date, 'j')
2283 INTO
2284 l_To_Time_ID
2285 , l_From_Time_ID
2286 FROM FII_TIME_WEEK
2287 WHERE week_id = l_Available_Week;
2288
2289 SELECT TO_DATE(MAX(TIME_ID),'j')
2290 INTO l_Available_Date
2291 FROM pji_rm_res_f
2292 WHERE 1=1
2293 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2294 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2295 AND calendar_type = l_Day_Calendar_Type
2296 AND period_type_id = l_Day_Period_Type_ID
2297 AND capacity_hrs <> available_hrs_bkt1_s
2298 AND capacity_hrs <> 0;
2299 END IF;
2300 END IF;
2301 END IF;
2302 IF l_Available_Date IS NOT NULL OR NVL(P_AVL_RES_DET_TBL(i).AVAILABLE, 0) = 0 THEN
2303 IF l_Available_Date IS NOT NULL THEN
2304 l_Project_Date:=TO_CHAR(l_Available_Date, 'j');
2305 ELSE
2306 l_Project_Date:=TO_CHAR(l_As_Of_Date, 'j');
2307 END IF;
2308 GET_PROJECT_INFO(P_AVL_RES_DET_TBL(i).person_id
2309 , l_Project_Date
2310 , P_AVL_RES_DET_TBL(i).curr_last_proj
2311 , l_Billable_Flag);
2312 END IF;
2313 END LOOP;
2314 END GET_CURRENT_LAST_PROJECT_INFO;
2315
2316 PROCEDURE GET_NEXT_ASSIGNMENT_INFO(P_AVL_RES_DET_TBL IN OUT NOCOPY PJI_REP_RA5_TBL, P_AS_OF_DATE NUMBER)
2317 AS
2318 l_As_Of_Date DATE:=TO_DATE(p_As_Of_Date, 'j');
2319 l_Week_Calendar_Type VARCHAR2(1):='E';
2320 l_Day_Calendar_Type VARCHAR2(1):='C';
2321 l_Week_Period_Type_ID NUMBER:=16;
2322 l_Day_Period_Type_ID NUMBER:=1;
2323 l_Num_of_Weeks NUMBER:=15;
2324 l_Max_Num_of_Weeks NUMBER:=52;
2325
2326 l_From_Time_ID NUMBER;
2327 l_To_Time_ID NUMBER;
2328 l_End_Date DATE;
2329 l_End_Week_ID NUMBER;
2330 l_Available_Week NUMBER;
2331
2332 l_Billable_Flag VARCHAR2(1);
2333 BEGIN
2334 /* For Current Date */
2335 SELECT week_id
2336 , end_date
2337 INTO l_End_Week_ID
2338 ,l_End_date
2339 FROM
2340 fii_time_week
2341 WHERE
2342 l_As_Of_Date BETWEEN start_date AND end_date;
2343
2344 FOR i IN 1..P_AVL_RES_DET_TBL.LAST
2345 LOOP
2346 l_Available_Week:=NULL;
2347
2348 IF NVL(P_AVL_RES_DET_TBL(i).AVAILABLE, 1) = 1 THEN
2349
2350 l_From_Time_ID:=TO_CHAR(l_As_Of_Date,'j');
2351 l_To_Time_ID:=TO_CHAR(l_End_date,'j');
2352
2353 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2354 INTO P_AVL_RES_DET_TBL(i).next_asgmt_date
2355 FROM pji_rm_res_f
2356 WHERE 1=1
2357 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2358 AND time_id BETWEEN l_From_Time_ID AND l_To_Time_ID
2359 AND calendar_type = l_Day_Calendar_Type
2360 AND period_type_id = l_Day_Period_Type_ID
2361 AND capacity_hrs<>available_hrs_bkt1_s
2362 AND capacity_hrs <> 0;
2363
2364 IF P_AVL_RES_DET_TBL(i).next_asgmt_date IS NULL THEN
2365 SELECT MIN(TIME_ID)
2366 INTO l_Available_Week
2367 FROM pji_rm_res_f fct
2368 , fii_time_week time
2369 WHERE 1=1
2370 AND time.start_date>=l_End_Date
2371 AND time.end_date<=l_End_Date+(7*l_Num_of_Weeks)
2372 AND fct.time_id = time.week_id
2373 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
2374 AND fct.calendar_type = l_Week_Calendar_Type
2375 AND fct.period_type_id = l_Week_Period_Type_ID
2376 AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
2377 AND fct.capacity_hrs <> 0;
2378
2379 IF l_Available_Week IS NULL THEN
2380 SELECT MIN(TIME_ID)
2381 INTO l_Available_Week
2382 FROM pji_rm_res_f fct
2383 , fii_time_week time
2384 WHERE 1=1
2385 AND time.start_date>=l_End_Date+(7*l_Num_of_Weeks)
2386 AND time.end_date<=l_End_Date+(7*l_Max_Num_of_Weeks)
2387 AND fct.time_id = time.week_id
2388 AND fct.person_id = P_AVL_RES_DET_TBL(i).person_id
2389 AND fct.calendar_type = l_Week_Calendar_Type
2390 AND fct.period_type_id = l_Week_Period_Type_ID
2391 AND fct.capacity_hrs <> fct.available_hrs_bkt1_s
2392 AND fct.capacity_hrs <> 0;
2393 END IF;
2394
2395 IF l_Available_Week IS NOT NULL THEN
2396 SELECT TO_CHAR(start_date, 'j')
2397 , TO_CHAR(end_date, 'j')
2398 INTO
2399 l_To_Time_ID
2400 , l_From_Time_ID
2401 FROM FII_TIME_WEEK
2402 WHERE week_id = l_Available_Week;
2403
2404 SELECT TO_CHAR(TO_DATE(MIN(TIME_ID),'j'),'RRRR/MM/DD')
2405 INTO P_AVL_RES_DET_TBL(i).next_asgmt_date
2406 FROM pji_rm_res_f
2407 WHERE 1=1
2408 AND person_id = P_AVL_RES_DET_TBL(i).person_id
2409 AND time_id BETWEEN l_To_Time_ID AND l_From_Time_ID
2410 AND calendar_type = l_Day_Calendar_Type
2411 AND period_type_id = l_Day_Period_Type_ID
2412 AND capacity_hrs <> available_hrs_bkt1_s
2413 AND capacity_hrs <> 0;
2414 END IF;
2415 END IF;
2416 END IF;
2417 IF P_AVL_RES_DET_TBL(i).next_asgmt_date IS NOT NULL THEN
2418 GET_PROJECT_INFO(P_AVL_RES_DET_TBL(i).person_id
2419 , TO_CHAR(TO_DATE(P_AVL_RES_DET_TBL(i).next_asgmt_date,'RRRR/MM/DD'), 'j')
2420 , P_AVL_RES_DET_TBL(i).next_proj
2421 , l_Billable_Flag);
2422 END IF;
2423 END LOOP;
2424 END GET_NEXT_ASSIGNMENT_INFO;
2425
2426 END PJI_PMV_AVL;
2427