[Home] [Help]
PACKAGE BODY: APPS.PJI_RM_SUM_ROLLUP_RES
Source
1 package body PJI_RM_SUM_ROLLUP_RES as
2 /* $Header: PJISR03B.pls 120.4 2006/03/31 12:04:06 appldev noship $ */
3
4
5 -- -----------------------------------------------------
6 -- procedure JOB_NONUTIL2UTIL
7 -- -----------------------------------------------------
8 procedure JOB_NONUTIL2UTIL (p_worker_id in number) is
9
10 l_process varchar2(30);
11 l_row_count number;
12
13 begin
14
15 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
16
17 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
18 (
19 l_process,
20 'PJI_RM_SUM_ROLLUP_RES.JOB_NONUTIL2UTIL(p_worker_id);'
21 )) then
22 return;
23 end if;
24
25 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process,
26 'EXTRACTION_TYPE') = 'FULL') then
27 return;
28 end if;
29
30 -- implicit commit
31 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
32 tabname => 'PJI_RES_DELTA',
33 percent => 10,
34 degree => BIS_COMMON_PARAMETERS.
35 GET_DEGREE_OF_PARALLELISM);
36 -- implicit commit
37 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
38 tabname => 'PJI_RES_DELTA',
39 colname => 'CHANGE_TYPE',
40 percent => 10,
41 degree => BIS_COMMON_PARAMETERS.
42 GET_DEGREE_OF_PARALLELISM);
43 -- implicit commit
44 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
45 tabname => 'PJI_RES_DELTA',
46 colname => 'RESOURCE_ID',
47 percent => 10,
48 degree => BIS_COMMON_PARAMETERS.
49 GET_DEGREE_OF_PARALLELISM);
50 -- implicit commit
51 FND_STATS.GATHER_COLUMN_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
52 tabname => 'PJI_RES_DELTA',
53 colname => 'PERSON_ID',
54 percent => 10,
55 degree => BIS_COMMON_PARAMETERS.
56 GET_DEGREE_OF_PARALLELISM);
57
58 select count(*)
59 into l_row_count
60 from PJI_RES_DELTA
61 where CHANGE_TYPE = 'Y';
62
63 if (l_row_count > 0) then
64
65 insert /*+ append parallel(fcst_i) */ into PJI_RM_REXT_FCSTITEM fcst_i
66 (
67 WORKER_ID,
68 FID_ROWID,
69 START_DATE,
70 END_DATE,
71 PJI_SUMMARIZED_FLAG,
72 BATCH_ID
73 )
74 select /*+ ordered
75 full(delta)
76 full(fcst) use_hash(fcst)
77 full(status) use_hash(status)
78 */
79 p_worker_id WORKER_ID,
80 fid.ROWID FID_ROWID,
81 delta.START_DATE,
82 delta.END_DATE,
83 fid.PJI_SUMMARIZED_FLAG,
84 ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
85 from
86 PJI_RES_DELTA delta,
87 PA_FORECAST_ITEMS fi,
88 PA_FORECAST_ITEM_DETAILS fid,
89 PJI_RM_REXT_FCSTITEM fcst,
90 PJI_ORG_EXTR_STATUS status
91 where
92 delta.CHANGE_TYPE = 'Y' and
93 fi.RESOURCE_ID = delta.RESOURCE_ID and
94 fi.FORECAST_ITEM_TYPE in ('U', 'A') and
95 fi.ITEM_DATE between delta.START_DATE and delta.END_DATE and
96 fi.FORECAST_ITEM_ID = fid.FORECAST_ITEM_ID and
97 nvl(fid.pji_summarized_flag,'Y') <> 'N' and
98 fi.EXPENDITURE_ORGANIZATION_ID = status.ORGANIZATION_ID and
99 fcst.FID_ROWID (+) = fid.ROWID and
100 fcst.WORKER_ID (+) = p_worker_id and
101 fcst.FID_ROWID is null;
102
103 insert /*+ append parallel(cdl_i) */ into PJI_FM_REXT_CDL cdl_i
104 (
105 WORKER_ID,
106 CDL_ROWID,
107 START_DATE,
108 END_DATE,
109 PROJECT_ORG_ID,
110 PROJECT_ORGANIZATION_ID,
111 PJI_SUMMARIZED_FLAG,
112 BATCH_ID
113 )
114 select /*+ ordered
115 full(delta)
116 full(rcdl) use_hash(rcdl)
117 full(status) use_hash(status)
118 */
119 p_worker_id WORKER_ID,
120 cdl.ROWID CDL_ROWID,
121 delta.START_DATE,
122 delta.END_DATE,
123 -1 PROJECT_ORG_ID,
124 -1 PROJECT_ORGANIZATION_ID,
125 cdl.PJI_SUMMARIZED_FLAG,
126 ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
127 from
128 PJI_RES_DELTA delta,
129 PA_EXPENDITURES_ALL exp,
130 PA_EXPENDITURE_ITEMS_ALL ei,
131 PA_COST_DISTRIBUTION_LINES_ALL cdl,
132 PJI_FM_REXT_CDL rcdl,
133 PJI_PROJ_EXTR_STATUS status
134 where
135 delta.CHANGE_TYPE = 'Y' and
136 nvl(cdl.ORG_ID, -999) = nvl(ei.ORG_ID, -999) and
137 nvl(exp.ORG_ID, -999) = nvl(ei.ORG_ID, -999) and
138 ei.EXPENDITURE_ITEM_DATE between delta.START_DATE and
139 delta.END_DATE and
140 delta.PERSON_ID = exp.INCURRED_BY_PERSON_ID and
141 exp.EXPENDITURE_ID = ei.EXPENDITURE_ID and
142 ei.EXPENDITURE_ITEM_ID = cdl.EXPENDITURE_ITEM_ID and
143 cdl.LINE_TYPE = 'R' and
144 nvl(cdl.PJI_SUMMARIZED_FLAG, 'Y') <> 'N' and
145 cdl.PROJECT_ID = status.PROJECT_ID and
146 rcdl.CDL_ROWID (+) = cdl.ROWID and
147 rcdl.WORKER_ID (+) = p_worker_id and
148 rcdl.CDL_ROWID is null;
149
150 end if;
151
152 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
153 (
154 l_process,
155 'PJI_RM_SUM_ROLLUP_RES.JOB_NONUTIL2UTIL(p_worker_id);'
156 );
157
158 commit;
159
160 end JOB_NONUTIL2UTIL;
161
162
163 -- -----------------------------------------------------
164 -- procedure CALC_RMS_AVL_AND_WT
165 -- -----------------------------------------------------
166 procedure CALC_RMS_AVL_AND_WT (p_worker_id in number) is
167
168 l_process varchar2(30);
169 l_extraction_type varchar2(30);
170 l_work_type_change varchar2(30);
171 l_count number;
172 l_avl_bkt_1 number;
173 l_avl_bkt_2 number;
174 l_avl_bkt_3 number;
175 l_avl_bkt_4 number;
176 l_avl_bkt_5 number;
177
178 l_missing_avl_setup exception;
179
180 begin
181
182 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
183
184 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
185 (
186 l_process,
187 'PJI_RM_SUM_ROLLUP_RES.CALC_RMS_AVL_AND_WT(p_worker_id);'
188 )) then
189 return;
190 end if;
191
192 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
193 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
194
195 select count(*)
196 into l_count
197 from PJI_RM_WORK_TYPE_INFO
198 where RECORD_TYPE = 'CHANGE_OLD' or
199 RECORD_TYPE = 'CHANGE_NEW';
200
201 if (l_count > 0) then
202 l_work_type_change := 'CHANGE_EXISTS';
203 else
204 l_work_type_change := 'NO_CHANGE';
205 end if;
206
207 --Initialize the availability bucket thresholds into variables
208 --Each threshold corresponds to an availability definition
209 begin
210
211 select
212 sum(case when bkt.SEQ = 1
213 then bkt.TO_VALUE
214 else null
215 end) ,
216 sum(case when bkt.SEQ = 2
217 then bkt.TO_VALUE
218 else null
219 end) ,
220 sum(case when bkt.SEQ = 3
221 then bkt.TO_VALUE
222 else null
223 end) ,
224 sum(case when bkt.SEQ = 4
225 then bkt.TO_VALUE
226 else null
227 end) ,
228 sum(case when bkt.SEQ = 5
229 then bkt.TO_VALUE
230 else null
231 end)
232 into
233 l_avl_bkt_1,
234 l_avl_bkt_2,
235 l_avl_bkt_3,
236 l_avl_bkt_4,
237 l_avl_bkt_5
238 from
239 PJI_MT_BUCKETS bkt
240 where
241 bkt.BUCKET_SET_CODE = 'PJI_RESOURCE_AVAILABILITY';
242
243 exception
244 when no_data_found then
245 raise l_missing_avl_setup;
246 end;
247
248 insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
249 (
250 WORKER_ID,
251 PERSON_ID,
252 EXPENDITURE_ORG_ID,
253 EXPENDITURE_ORGANIZATION_ID,
254 JOB_ID,
255 TIME_ID,
256 PERIOD_TYPE_ID,
257 CALENDAR_TYPE,
258 GL_CALENDAR_ID,
259 PA_CALENDAR_ID,
260 CAPACITY_HRS,
261 TOTAL_HRS_A,
262 MISSING_HRS_A,
263 TOTAL_WTD_ORG_HRS_A,
264 TOTAL_WTD_RES_HRS_A,
265 BILL_HRS_A,
266 BILL_WTD_ORG_HRS_A,
267 BILL_WTD_RES_HRS_A,
268 TRAINING_HRS_A,
269 REDUCIBLE_CAPACITY_HRS_A,
270 REDUCE_CAPACITY_HRS_A,
271 CONF_HRS_S,
272 CONF_WTD_ORG_HRS_S,
273 CONF_WTD_RES_HRS_S,
274 CONF_BILL_HRS_S,
275 CONF_BILL_WTD_ORG_HRS_S,
276 CONF_BILL_WTD_RES_HRS_S,
277 PROV_HRS_S,
278 PROV_WTD_ORG_HRS_S,
279 PROV_WTD_RES_HRS_S,
280 PROV_BILL_HRS_S,
281 PROV_BILL_WTD_ORG_HRS_S,
282 PROV_BILL_WTD_RES_HRS_S,
283 TRAINING_HRS_S,
284 UNASSIGNED_HRS_S,
285 REDUCIBLE_CAPACITY_HRS_S,
286 REDUCE_CAPACITY_HRS_S,
287 CONF_OVERCOM_HRS_S,
288 PROV_OVERCOM_HRS_S,
289 AVAILABLE_HRS_BKT1_S,
290 AVAILABLE_HRS_BKT2_S,
291 AVAILABLE_HRS_BKT3_S,
292 AVAILABLE_HRS_BKT4_S,
293 AVAILABLE_HRS_BKT5_S,
294 AVAILABLE_RES_COUNT_BKT1_S,
295 AVAILABLE_RES_COUNT_BKT2_S,
296 AVAILABLE_RES_COUNT_BKT3_S,
297 AVAILABLE_RES_COUNT_BKT4_S,
298 AVAILABLE_RES_COUNT_BKT5_S,
299 TOTAL_RES_COUNT
300 )
301 select
302 p_worker_id WORKER_ID,
303 tmp1.PERSON_ID,
304 tmp1.EXPENDITURE_ORG_ID,
305 tmp1.EXPENDITURE_ORGANIZATION_ID,
306 tmp1.JOB_ID,
307 tmp1.TIME_ID,
308 tmp1.PERIOD_TYPE_ID,
309 tmp1.CALENDAR_TYPE,
310 tmp1.GL_CALENDAR_ID,
311 tmp1.PA_CALENDAR_ID,
312 sum(tmp1.CAPACITY_HRS),
313 sum(tmp1.TOTAL_HRS_A),
314 sum(greatest(nvl(tmp1.CAPACITY_HRS,0) + nvl(rms.CAPACITY_HRS, 0) -
315 (nvl(tmp1.TOTAL_HRS_A,0) + nvl(rms.TOTAL_HRS_A, 0)), 0) -
316 nvl(rms.MISSING_HRS_A, 0)) MISSING_HRS_A,
317 sum(tmp1.TOTAL_WTD_ORG_HRS_A),
318 sum(tmp1.TOTAL_WTD_RES_HRS_A),
319 sum(tmp1.BILL_HRS_A),
320 sum(tmp1.BILL_WTD_ORG_HRS_A),
321 sum(tmp1.BILL_WTD_RES_HRS_A),
322 sum(tmp1.TRAINING_HRS_A),
323 sum(tmp1.REDUCE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
324 sum(least(nvl(tmp1.CAPACITY_HRS, 0) + nvl(rms.CAPACITY_HRS, 0),
325 nvl(tmp1.REDUCE_CAPACITY_HRS_A, 0) +
326 nvl(rms.REDUCIBLE_CAPACITY_HRS_A, 0)) -
327 nvl(rms.REDUCE_CAPACITY_HRS_A, 0)) REDUCE_CAPACITY_HRS_A,
328 sum(tmp1.CONF_HRS_S),
329 sum(tmp1.CONF_WTD_ORG_HRS_S),
330 sum(tmp1.CONF_WTD_RES_HRS_S),
331 sum(tmp1.CONF_BILL_HRS_S),
332 sum(tmp1.CONF_BILL_WTD_ORG_HRS_S),
333 sum(tmp1.CONF_BILL_WTD_RES_HRS_S),
334 sum(tmp1.PROV_HRS_S),
335 sum(tmp1.PROV_WTD_ORG_HRS_S),
336 sum(tmp1.PROV_WTD_RES_HRS_S),
337 sum(tmp1.PROV_BILL_HRS_S),
338 sum(tmp1.PROV_BILL_WTD_ORG_HRS_S),
339 sum(tmp1.PROV_BILL_WTD_RES_HRS_S),
340 sum(tmp1.TRAINING_HRS_S),
341 sum(tmp1.UNASSIGNED_HRS_S),
342 sum(tmp1.REDUCE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
343 sum(least(nvl(tmp1.CAPACITY_HRS, 0) + nvl(rms.CAPACITY_HRS, 0),
344 nvl(tmp1.REDUCE_CAPACITY_HRS_S, 0) +
345 nvl(rms.REDUCIBLE_CAPACITY_HRS_S, 0)) -
346 nvl(rms.REDUCE_CAPACITY_HRS_S, 0)) REDUCE_CAPACITY_HRS_S,
347 sum(tmp1.CONF_OVERCOM_HRS_S),
348 sum(tmp1.PROV_OVERCOM_HRS_S),
349 sum(case when l_avl_bkt_1 is not null and
350 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
351 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
352 ((100-l_avl_bkt_1)/100))
353 then - nvl(tmp1.CONF_HRS_S, 0) + nvl(tmp1.CAPACITY_HRS, 0)
354 else - nvl(rms.AVAILABLE_HRS_BKT1_S, 0)
355 end) AVAILABLE_HRS_BKT1_S,
356 sum(case when l_avl_bkt_2 is not null and
357 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
358 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
359 ((100-l_avl_bkt_2)/100))
360 then - nvl(tmp1.CONF_HRS_S, 0) + nvl(tmp1.CAPACITY_HRS, 0)
361 else - nvl(rms.AVAILABLE_HRS_BKT2_S, 0)
362 end) AVAILABLE_HRS_BKT2_S,
363 sum(case when l_avl_bkt_3 is not null and
364 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
365 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
366 ((100-l_avl_bkt_3)/100))
367 then - nvl(tmp1.CONF_HRS_S, 0) + nvl(tmp1.CAPACITY_HRS, 0)
368 else - nvl(rms.AVAILABLE_HRS_BKT3_S, 0)
369 end) AVAILABLE_HRS_BKT3_S,
370 sum(case when l_avl_bkt_4 is not null and
371 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
372 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
373 ((100-l_avl_bkt_4)/100))
374 then - nvl(tmp1.CONF_HRS_S, 0) + nvl(tmp1.CAPACITY_HRS, 0)
375 else - nvl(rms.AVAILABLE_HRS_BKT4_S, 0)
376 end) AVAILABLE_HRS_BKT4_S,
377 sum(case when l_avl_bkt_5 is not null and
378 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
379 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
380 ((100-l_avl_bkt_5)/100))
381 then - nvl(tmp1.CONF_HRS_S, 0) + nvl(tmp1.CAPACITY_HRS, 0)
382 else - nvl(rms.AVAILABLE_HRS_BKT5_S, 0)
383 end) AVAILABLE_HRS_BKT5_S,
384 sum(case when l_avl_bkt_1 is not null and
385 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
386 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
387 ((100-l_avl_bkt_1)/100))
388 then 1 - nvl(rms.AVAILABLE_RES_COUNT_BKT1_S, 0)
389 else - nvl(rms.AVAILABLE_RES_COUNT_BKT1_S, 0)
390 end) AVAILABLE_RES_COUNT_BKT1_S,
391 sum(case when l_avl_bkt_2 is not null and
392 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
393 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
394 ((100-l_avl_bkt_2)/100))
395 then 1 - nvl(rms.AVAILABLE_RES_COUNT_BKT2_S, 0)
396 else - nvl(rms.AVAILABLE_RES_COUNT_BKT2_S, 0)
397 end) AVAILABLE_RES_COUNT_BKT2_S,
398 sum(case when l_avl_bkt_3 is not null and
399 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
400 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
401 ((100-l_avl_bkt_3)/100))
402 then 1 - nvl(rms.AVAILABLE_RES_COUNT_BKT3_S, 0)
403 else - nvl(rms.AVAILABLE_RES_COUNT_BKT3_S, 0)
404 end) AVAILABLE_RES_COUNT_BKT3_S,
405 sum(case when l_avl_bkt_4 is not null and
406 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
407 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
408 ((100-l_avl_bkt_4)/100))
409 then 1 - nvl(rms.AVAILABLE_RES_COUNT_BKT4_S, 0)
410 else - nvl(rms.AVAILABLE_RES_COUNT_BKT4_S, 0)
411 end) AVAILABLE_RES_COUNT_BKT4_S,
412 sum(case when l_avl_bkt_5 is not null and
413 nvl(rms.CONF_HRS_S, 0) + nvl(tmp1.CONF_HRS_S, 0) <=
414 ((nvl(rms.CAPACITY_HRS, 0) + nvl(tmp1.CAPACITY_HRS, 0)) *
415 ((100-l_avl_bkt_5)/100))
416 then 1 - nvl(rms.AVAILABLE_RES_COUNT_BKT5_S, 0)
417 else - nvl(rms.AVAILABLE_RES_COUNT_BKT5_S, 0)
418 end) AVAILABLE_RES_COUNT_BKT5_S,
419 sum(case when tmp1.CAPACITY_HRS < 0 and
420 (tmp1.CAPACITY_HRS + nvl(rms.CAPACITY_HRS, 0)) = 0
421 then -1
422 when tmp1.CAPACITY_HRS > 0 and
423 nvl(rms.CAPACITY_HRS, 0) = 0
424 then 1
425 else 0
426 end) TOTAL_RES_COUNT
427 from
428 (
429 select
430 PERSON_ID,
431 EXPENDITURE_ORG_ID,
432 EXPENDITURE_ORGANIZATION_ID,
433 JOB_ID,
434 TIME_ID,
435 PERIOD_TYPE_ID,
436 CALENDAR_TYPE,
437 GL_CALENDAR_ID,
438 PA_CALENDAR_ID,
439 sum(CAPACITY_HRS) CAPACITY_HRS,
440 sum(TOTAL_HRS_A) TOTAL_HRS_A,
441 sum(TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
442 sum(TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
443 sum(BILL_HRS_A) BILL_HRS_A,
444 sum(BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
445 sum(BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
446 sum(TRAINING_HRS_A) TRAINING_HRS_A,
447 sum(REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
448 sum(CONF_HRS_S) CONF_HRS_S,
449 sum(CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
450 sum(CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
451 sum(CONF_BILL_HRS_S) CONF_BILL_HRS_S,
452 sum(CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
453 sum(CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
454 sum(PROV_HRS_S) PROV_HRS_S,
455 sum(PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
456 sum(PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
457 sum(PROV_BILL_HRS_S) PROV_BILL_HRS_S,
458 sum(PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
459 sum(PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
460 sum(TRAINING_HRS_S) TRAINING_HRS_S,
461 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
462 sum(REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
463 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
464 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
465 from
466 (
467 select /*+ ordered
468 full(wt) use_hash(wt) swap_join_inputs(wt)
469 full(tmp1) use_hash(tmp1) parallel(tmp1) */
470 tmp1.PERSON_ID,
471 tmp1.EXPENDITURE_ORG_ID,
472 tmp1.EXPENDITURE_ORGANIZATION_ID,
473 tmp1.JOB_ID,
474 tmp1.TIME_ID,
475 tmp1.PERIOD_TYPE_ID,
476 tmp1.CALENDAR_TYPE,
477 tmp1.GL_CALENDAR_ID,
478 tmp1.PA_CALENDAR_ID,
479 tmp1.CAPACITY_HRS CAPACITY_HRS,
480 tmp1.TOTAL_HRS_A TOTAL_HRS_A,
481 tmp1.TOTAL_HRS_A
482 * wt.ORG_UTILIZATION_PERCENTAGE / 100 TOTAL_WTD_ORG_HRS_A,
483 tmp1.TOTAL_HRS_A
484 * wt.RES_UTILIZATION_PERCENTAGE / 100 TOTAL_WTD_RES_HRS_A,
485 tmp1.BILL_HRS_A BILL_HRS_A,
486 tmp1.BILL_HRS_A
487 * wt.ORG_UTILIZATION_PERCENTAGE / 100 BILL_WTD_ORG_HRS_A,
488 tmp1.BILL_HRS_A
489 * wt.RES_UTILIZATION_PERCENTAGE / 100 BILL_WTD_RES_HRS_A,
490 decode(wt.TRAINING_FLAG,
491 'Y', tmp1.TOTAL_HRS_A, 0) TRAINING_HRS_A,
492 decode(wt.REDUCE_CAPACITY_FLAG,
493 'Y', tmp1.TOTAL_HRS_A, 0) REDUCE_CAPACITY_HRS_A,
494 tmp1.CONF_HRS_S CONF_HRS_S,
495 tmp1.CONF_HRS_S
496 * wt.ORG_UTILIZATION_PERCENTAGE / 100 CONF_WTD_ORG_HRS_S,
497 tmp1.CONF_HRS_S
498 * wt.RES_UTILIZATION_PERCENTAGE / 100 CONF_WTD_RES_HRS_S,
499 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
500 'Y', tmp1.CONF_HRS_S, 0) CONF_BILL_HRS_S,
501 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
502 'Y', tmp1.CONF_HRS_S
503 * wt.ORG_UTILIZATION_PERCENTAGE
504 / 100, 0) CONF_BILL_WTD_ORG_HRS_S,
505 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
506 'Y', tmp1.CONF_HRS_S
507 * wt.RES_UTILIZATION_PERCENTAGE
508 / 100, 0) CONF_BILL_WTD_RES_HRS_S,
509 tmp1.PROV_HRS_S PROV_HRS_S,
510 tmp1.PROV_HRS_S
511 * wt.ORG_UTILIZATION_PERCENTAGE / 100 PROV_WTD_ORG_HRS_S,
512 tmp1.PROV_HRS_S
513 * wt.RES_UTILIZATION_PERCENTAGE / 100 PROV_WTD_RES_HRS_S,
514 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
515 'Y', tmp1.PROV_HRS_S, 0) PROV_BILL_HRS_S,
516 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
517 'Y', tmp1.PROV_HRS_S
518 * wt.ORG_UTILIZATION_PERCENTAGE
519 / 100, 0) PROV_BILL_WTD_ORG_HRS_S,
520 decode(wt.BILLABLE_CAPITALIZABLE_FLAG,
521 'Y', tmp1.PROV_HRS_S
522 * wt.RES_UTILIZATION_PERCENTAGE
523 / 100, 0) PROV_BILL_WTD_RES_HRS_S,
524 decode(wt.TRAINING_FLAG,
525 'Y', tmp1.CONF_HRS_S, 0) TRAINING_HRS_S,
526 tmp1.UNASSIGNED_HRS_S UNASSIGNED_HRS_S,
527 decode(wt.REDUCE_CAPACITY_FLAG,
528 'Y', tmp1.CONF_HRS_S, 0) REDUCE_CAPACITY_HRS_S,
529 tmp1.CONF_OVERCOM_HRS_S CONF_OVERCOM_HRS_S,
530 tmp1.PROV_OVERCOM_HRS_S PROV_OVERCOM_HRS_S
531 from
532 PJI_RM_WORK_TYPE_INFO wt,
533 PJI_RM_AGGR_RES1 tmp1
534 where
535 tmp1.WORKER_ID = p_worker_id and
536 tmp1.RECORD_TYPE <> 'N' and
537 'NORMAL' = wt.RECORD_TYPE (+) and
538 tmp1.WORK_TYPE_ID = wt.WORK_TYPE_ID (+)
539 union all
540 select /*+ ordered
541 full(wt_old) use_hash(wt_old)
542 full(wt_new) use_hash(wt_new)
543 parallel(rmr)
544 full(info) use_hash(info) */ -- work type corrections
545 rmr.PERSON_ID,
546 rmr.EXPENDITURE_ORG_ID,
547 rmr.EXPENDITURE_ORGANIZATION_ID,
548 rmr.JOB_ID,
549 rmr.TIME_ID,
550 rmr.PERIOD_TYPE_ID,
551 rmr.CALENDAR_TYPE,
552 info.GL_CALENDAR_ID,
553 info.PA_CALENDAR_ID,
554 0 CAPACITY_HRS,
555 0 TOTAL_HRS_A,
556 rmr.TOTAL_HRS_A
557 * (wt_new.ORG_UTILIZATION_PERCENTAGE -
558 wt_old.ORG_UTILIZATION_PERCENTAGE)
559 / 100 TOTAL_WTD_ORG_HRS_A,
560 rmr.TOTAL_HRS_A
561 * (wt_new.RES_UTILIZATION_PERCENTAGE -
562 wt_old.RES_UTILIZATION_PERCENTAGE)
563 / 100 TOTAL_WTD_RES_HRS_A,
564 0 BILL_HRS_A,
565 rmr.BILL_HRS_A
566 * (wt_new.ORG_UTILIZATION_PERCENTAGE -
567 wt_old.ORG_UTILIZATION_PERCENTAGE)
568 / 100 BILL_WTD_ORG_HRS_A,
569 rmr.BILL_HRS_A
570 * (wt_new.RES_UTILIZATION_PERCENTAGE -
571 wt_old.RES_UTILIZATION_PERCENTAGE)
572 / 100 BILL_WTD_RES_HRS_A,
573 case when nvl(wt_old.TRAINING_FLAG, 'N') = 'N' and
574 nvl(wt_new.TRAINING_FLAG, 'N') = 'Y'
575 then rmr.TOTAL_HRS_A
576 when nvl(wt_old.TRAINING_FLAG, 'N') = 'Y' and
577 nvl(wt_new.TRAINING_FLAG, 'N') = 'N'
578 then -rmr.TOTAL_HRS_A
579 else 0
580 end TRAINING_HRS_A,
581 case when nvl(wt_old.REDUCE_CAPACITY_FLAG, 'N') = 'N' and
582 nvl(wt_new.REDUCE_CAPACITY_FLAG, 'N') = 'Y'
583 then rmr.TOTAL_HRS_A
584 when nvl(wt_old.REDUCE_CAPACITY_FLAG, 'N') = 'Y' and
585 nvl(wt_new.REDUCE_CAPACITY_FLAG, 'N') = 'N'
586 then -rmr.TOTAL_HRS_A
587 else 0
588 end REDUCE_CAPACITY_HRS_A,
589 0 CONF_HRS_S,
590 rmr.CONF_HRS_S
591 * (wt_new.ORG_UTILIZATION_PERCENTAGE -
592 wt_old.ORG_UTILIZATION_PERCENTAGE)
593 / 100 CONF_WTD_ORG_HRS_S,
594 rmr.CONF_HRS_S
595 * (wt_new.RES_UTILIZATION_PERCENTAGE -
596 wt_old.RES_UTILIZATION_PERCENTAGE)
597 / 100 CONF_WTD_RES_HRS_S,
598 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
599 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
600 then rmr.CONF_HRS_S
601 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
602 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
603 then -rmr.CONF_HRS_S
604 else 0
605 end CONF_BILL_HRS_S,
606 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
607 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
608 then rmr.CONF_HRS_S * wt_new.ORG_UTILIZATION_PERCENTAGE / 100
609 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
610 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
611 then -rmr.CONF_HRS_S * wt_old.ORG_UTILIZATION_PERCENTAGE / 100
612 else 0
613 end CONF_BILL_WTD_ORG_HRS_S,
614 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
615 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
616 then rmr.CONF_HRS_S * wt_new.RES_UTILIZATION_PERCENTAGE / 100
617 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
618 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
619 then -rmr.CONF_HRS_S * wt_old.RES_UTILIZATION_PERCENTAGE / 100
620 else 0
621 end CONF_BILL_WTD_RES_HRS_S,
622 0 PROV_HRS_S,
623 rmr.PROV_HRS_S
624 * (wt_new.ORG_UTILIZATION_PERCENTAGE -
625 wt_old.ORG_UTILIZATION_PERCENTAGE)
626 / 100 PROV_WTD_ORG_HRS_S,
627 rmr.PROV_HRS_S
628 * (wt_new.RES_UTILIZATION_PERCENTAGE -
629 wt_old.RES_UTILIZATION_PERCENTAGE)
630 / 100 PROV_WTD_RES_HRS_S,
631 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
632 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
633 then rmr.PROV_HRS_S
634 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
635 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
636 then -rmr.PROV_HRS_S
637 else 0
638 end PROV_BILL_HRS_S,
639 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
640 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
641 then rmr.PROV_HRS_S * wt_new.ORG_UTILIZATION_PERCENTAGE / 100
642 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
643 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
644 then -rmr.PROV_HRS_S * wt_old.ORG_UTILIZATION_PERCENTAGE / 100
645 else 0
646 end PROV_BILL_WTD_ORG_HRS_S,
647 case when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N' and
648 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y'
649 then rmr.PROV_HRS_S * wt_new.RES_UTILIZATION_PERCENTAGE / 100
650 when nvl(wt_old.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'Y' and
651 nvl(wt_new.BILLABLE_CAPITALIZABLE_FLAG, 'N') = 'N'
652 then -rmr.PROV_HRS_S * wt_old.RES_UTILIZATION_PERCENTAGE / 100
653 else 0
654 end PROV_BILL_WTD_RES_HRS_S,
655 case when nvl(wt_old.TRAINING_FLAG, 'N') = 'N' and
656 nvl(wt_new.TRAINING_FLAG, 'N') = 'Y'
657 then rmr.CONF_HRS_S
658 when nvl(wt_old.TRAINING_FLAG, 'N') = 'Y' and
659 nvl(wt_new.TRAINING_FLAG, 'N') = 'N'
660 then -rmr.CONF_HRS_S
661 else 0
662 end TRAINING_HRS_S,
663 0 UNASSIGNED_HRS_S,
664 case when nvl(wt_old.REDUCE_CAPACITY_FLAG, 'N') = 'N' and
665 nvl(wt_new.REDUCE_CAPACITY_FLAG, 'N') = 'Y'
666 then rmr.CONF_HRS_S
667 when nvl(wt_old.REDUCE_CAPACITY_FLAG, 'N') = 'Y' and
668 nvl(wt_new.REDUCE_CAPACITY_FLAG, 'N') = 'N'
669 then -rmr.CONF_HRS_S
670 else 0
671 end REDUCE_CAPACITY_HRS_S,
672 0 CONF_OVERCOM_HRS_S,
673 0 PROV_OVERCOM_HRS_S
674 from
675 PJI_RM_WORK_TYPE_INFO wt_old,
676 PJI_RM_WORK_TYPE_INFO wt_new,
677 PJI_RM_RES_WT_F rmr,
678 PJI_ORG_EXTR_INFO info
679 where
680 l_extraction_type = 'INCREMENTAL' and
681 l_work_type_change = 'CHANGE_EXISTS' and
682 wt_old.RECORD_TYPE = 'CHANGE_OLD' and
683 wt_new.RECORD_TYPE = 'CHANGE_NEW' and
684 wt_old.WORK_TYPE_ID = wt_new.WORK_TYPE_ID and
685 rmr.CALENDAR_TYPE = 'C' and
686 rmr.PERIOD_TYPE_ID = 1 and
687 wt_new.WORK_TYPE_ID = rmr.WORK_TYPE_ID and
688 (rmr.PROJECT_ID in
689 (select /*+ full(map1) */
690 PROJECT_ID
691 from PJI_PJI_PROJ_BATCH_MAP map1
692 where WORKER_ID = p_worker_id) or
693 rmr.EXPENDITURE_ORGANIZATION_ID in
694 (select /*+ full(map2) */
695 ORGANIZATION_ID
696 from PJI_RM_ORG_BATCH_MAP map2
697 where WORKER_ID = p_worker_id)) and
698 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
699 )
700 group by
701 PERSON_ID,
702 EXPENDITURE_ORG_ID,
703 EXPENDITURE_ORGANIZATION_ID,
704 JOB_ID,
705 TIME_ID,
706 PERIOD_TYPE_ID,
707 CALENDAR_TYPE,
708 GL_CALENDAR_ID,
709 PA_CALENDAR_ID
710 ) tmp1,
711 PJI_RM_RES_F rms
712 where
713 tmp1.PERSON_ID = rms.PERSON_ID (+) and
714 tmp1.EXPENDITURE_ORG_ID = rms.EXPENDITURE_ORG_ID (+) and
715 tmp1.EXPENDITURE_ORGANIZATION_ID = rms.EXPENDITURE_ORGANIZATION_ID(+) and
716 tmp1.TIME_ID = rms.TIME_ID (+) and
717 tmp1.PERIOD_TYPE_ID = rms.PERIOD_TYPE_ID (+) and
718 tmp1.CALENDAR_TYPE = rms.CALENDAR_TYPE (+)
719 group by
720 tmp1.PERSON_ID,
721 tmp1.EXPENDITURE_ORG_ID,
722 tmp1.EXPENDITURE_ORGANIZATION_ID,
723 tmp1.JOB_ID,
724 tmp1.TIME_ID,
725 tmp1.PERIOD_TYPE_ID,
726 tmp1.CALENDAR_TYPE,
727 tmp1.GL_CALENDAR_ID,
728 tmp1.PA_CALENDAR_ID;
729
730 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
731 (
732 l_process,
733 'PJI_RM_SUM_ROLLUP_RES.CALC_RMS_AVL_AND_WT(p_worker_id);'
734 );
735
736 commit;
737
738 end CALC_RMS_AVL_AND_WT;
739
740
741 -- -----------------------------------------------------
742 -- procedure EXPAND_RMR_CAL_EN
743 -- -----------------------------------------------------
744 procedure EXPAND_RMR_CAL_EN (p_worker_id in number) is
745
746 l_process varchar2(30);
747 l_row_count number;
748
749 begin
750
751 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
752
753 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
754 (
755 l_process,
756 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_EN(p_worker_id);'
757 )) then
758 return;
759 end if;
760
761 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
762 (
763 WORKER_ID,
764 RECORD_TYPE,
765 PROJECT_ID,
766 PERSON_ID,
767 EXPENDITURE_ORG_ID,
768 EXPENDITURE_ORGANIZATION_ID,
769 WORK_TYPE_ID,
770 JOB_ID,
771 TIME_ID,
772 PERIOD_TYPE_ID,
773 CALENDAR_TYPE,
774 CAPACITY_HRS,
775 TOTAL_HRS_A,
776 BILL_HRS_A,
777 CONF_HRS_S,
778 PROV_HRS_S,
779 UNASSIGNED_HRS_S,
780 CONF_OVERCOM_HRS_S,
781 PROV_OVERCOM_HRS_S
782 )
783 select /*+ ordered
784 full(time) use_hash(time) swap_join_inputs(time)
785 full(tmp1) use_hash(tmp1) parallel(tmp1) */
786 p_worker_id,
787 tmp1.RECORD_TYPE,
788 -1,
789 tmp1.PERSON_ID,
790 tmp1.EXPENDITURE_ORG_ID,
791 tmp1.EXPENDITURE_ORGANIZATION_ID,
792 tmp1.WORK_TYPE_ID,
793 tmp1.JOB_ID,
794 case when grouping(time.ENT_YEAR_ID) = 0 and
795 grouping(time.ENT_QTR_ID) = 0 and
796 grouping(time.ENT_PERIOD_ID) = 0
797 then time.ENT_PERIOD_ID
798 when grouping(time.ENT_YEAR_ID) = 0 and
799 grouping(time.ENT_QTR_ID) = 0 and
800 grouping(time.ENT_PERIOD_ID) = 1
801 then time.ENT_QTR_ID
802 when grouping(time.ENT_YEAR_ID) = 0 and
803 grouping(time.ENT_QTR_ID) = 1 and
804 grouping(time.ENT_PERIOD_ID) = 1
805 then time.ENT_YEAR_ID
806 end TIME_ID,
807 case when grouping(time.ENT_YEAR_ID) = 0 and
808 grouping(time.ENT_QTR_ID) = 0 and
809 grouping(time.ENT_PERIOD_ID) = 0
810 then 32
811 when grouping(time.ENT_YEAR_ID) = 0 and
812 grouping(time.ENT_QTR_ID) = 0 and
813 grouping(time.ENT_PERIOD_ID) = 1
814 then 64
815 when grouping(time.ENT_YEAR_ID) = 0 and
816 grouping(time.ENT_QTR_ID) = 1 and
817 grouping(time.ENT_PERIOD_ID) = 1
818 then 128
819 end PERIOD_TYPE_ID,
820 'E' CALENDAR_TYPE,
821 sum(tmp1.CAPACITY_HRS) CAPACITY_HRS,
822 sum(tmp1.TOTAL_HRS_A) TOTAL_HRS_A,
823 sum(tmp1.BILL_HRS_A) BILL_HRS_A,
824 sum(tmp1.CONF_HRS_S) CONF_HRS_S,
825 sum(tmp1.PROV_HRS_S) PROV_HRS_S,
826 sum(tmp1.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
827 sum(tmp1.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
828 sum(tmp1.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
829 from
830 FII_TIME_DAY time,
831 PJI_RM_AGGR_RES1 tmp1
832 where
833 tmp1.WORKER_ID = p_worker_id and
834 tmp1.RECORD_TYPE <> 'N' and
835 tmp1.PERIOD_TYPE_ID = 1 and
836 tmp1.CALENDAR_TYPE = 'C' and
837 tmp1.TIME_ID = time.REPORT_DATE_JULIAN
838 group by
839 tmp1.RECORD_TYPE,
840 tmp1.PERSON_ID,
841 tmp1.EXPENDITURE_ORG_ID,
842 tmp1.EXPENDITURE_ORGANIZATION_ID,
843 tmp1.WORK_TYPE_ID,
844 tmp1.JOB_ID,
845 rollup (time.ENT_YEAR_ID,
846 time.ENT_QTR_ID,
847 time.ENT_PERIOD_ID)
848 having
849 not (grouping(time.ENT_YEAR_ID) = 1 and
850 grouping(time.ENT_QTR_ID) = 1 and
851 grouping(time.ENT_PERIOD_ID) = 1);
852
853 l_row_count := sql%rowcount;
854
855 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
856 (
857 l_process,
858 'TOTAL_RES_ROW_COUNT'
859 );
860
861 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
862 (
863 l_process,
864 'TOTAL_RES_ROW_COUNT',
865 l_row_count
866 );
867
868 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
869 (
870 l_process,
871 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_EN(p_worker_id);'
872 );
873
874 commit;
875
876 end EXPAND_RMR_CAL_EN;
877
878
879 -- -----------------------------------------------------
880 -- procedure EXPAND_RMR_CAL_PA
881 -- -----------------------------------------------------
882 procedure EXPAND_RMR_CAL_PA (p_worker_id in number) is
883
884 l_process varchar2(30);
885 l_row_count number;
886
887 begin
888
889 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
890
891 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
892 (
893 l_process,
894 'PA_CALENDAR_FLAG'
895 ) = 'N') then
896 return;
897 end if;
898
899 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
900
901 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
902 (
903 l_process,
904 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_PA(p_worker_id);'
905 )) then
906 return;
907 end if;
908
909 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
910 (
911 WORKER_ID,
912 RECORD_TYPE,
913 PROJECT_ID,
914 PERSON_ID,
915 EXPENDITURE_ORG_ID,
916 EXPENDITURE_ORGANIZATION_ID,
917 WORK_TYPE_ID,
918 JOB_ID,
919 TIME_ID,
920 PERIOD_TYPE_ID,
921 CALENDAR_TYPE,
922 CAPACITY_HRS,
923 TOTAL_HRS_A,
924 BILL_HRS_A,
925 CONF_HRS_S,
926 PROV_HRS_S,
927 UNASSIGNED_HRS_S,
928 CONF_OVERCOM_HRS_S,
929 PROV_OVERCOM_HRS_S
930 )
931 select /*+ ordered
932 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
933 full(tmp1) use_hash(tmp1) parallel(tmp1) */
934 p_worker_id,
935 tmp1.RECORD_TYPE,
936 -1,
937 tmp1.PERSON_ID,
938 tmp1.EXPENDITURE_ORG_ID,
939 tmp1.EXPENDITURE_ORGANIZATION_ID,
940 tmp1.WORK_TYPE_ID,
941 tmp1.JOB_ID,
942 case when grouping(time.CAL_YEAR_ID) = 0 and
943 grouping(time.CAL_QTR_ID) = 0 and
944 grouping(time.CAL_PERIOD_ID) = 0
945 then time.CAL_PERIOD_ID
946 when grouping(time.CAL_YEAR_ID) = 0 and
947 grouping(time.CAL_QTR_ID) = 0 and
948 grouping(time.CAL_PERIOD_ID) = 1
949 then time.CAL_QTR_ID
950 when grouping(time.CAL_YEAR_ID) = 0 and
951 grouping(time.CAL_QTR_ID) = 1 and
952 grouping(time.CAL_PERIOD_ID) = 1
953 then time.CAL_YEAR_ID
954 end TIME_ID,
955 case when grouping(time.CAL_YEAR_ID) = 0 and
956 grouping(time.CAL_QTR_ID) = 0 and
957 grouping(time.CAL_PERIOD_ID) = 0
958 then 32
959 when grouping(time.CAL_YEAR_ID) = 0 and
960 grouping(time.CAL_QTR_ID) = 0 and
961 grouping(time.CAL_PERIOD_ID) = 1
962 then 64
963 when grouping(time.CAL_YEAR_ID) = 0 and
964 grouping(time.CAL_QTR_ID) = 1 and
965 grouping(time.CAL_PERIOD_ID) = 1
966 then 128
967 end PERIOD_TYPE_ID,
968 'P' CALENDAR_TYPE,
969 sum(tmp1.CAPACITY_HRS) CAPACITY_HRS,
970 sum(tmp1.TOTAL_HRS_A) TOTAL_HRS_A,
971 sum(tmp1.BILL_HRS_A) BILL_HRS_A,
972 sum(tmp1.CONF_HRS_S) CONF_HRS_S,
973 sum(tmp1.PROV_HRS_S) PROV_HRS_S,
974 sum(tmp1.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
975 sum(tmp1.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
976 sum(tmp1.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
977 from
978 FII_TIME_CAL_DAY_MV time,
979 PJI_RM_AGGR_RES1 tmp1
980 where
981 tmp1.WORKER_ID = p_worker_id and
982 tmp1.RECORD_TYPE <> 'N' and
983 tmp1.PERIOD_TYPE_ID = 1 and
984 tmp1.CALENDAR_TYPE = 'C' and
985 to_date(to_char(tmp1.TIME_ID), 'J') = time.REPORT_DATE and
986 tmp1.PA_CALENDAR_ID = time.CALENDAR_ID
987 group by
988 tmp1.RECORD_TYPE,
989 tmp1.PERSON_ID,
990 tmp1.EXPENDITURE_ORGANIZATION_ID,
991 tmp1.EXPENDITURE_ORG_ID,
992 tmp1.WORK_TYPE_ID,
993 tmp1.JOB_ID,
994 rollup (time.CAL_YEAR_ID,
995 time.CAL_QTR_ID,
996 time.CAL_PERIOD_ID)
997 having
998 not (grouping(time.CAL_YEAR_ID) = 1 and
999 grouping(time.CAL_QTR_ID) = 1 and
1000 grouping(time.CAL_PERIOD_ID) = 1);
1001
1002 l_row_count := sql%rowcount;
1003
1004 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1005 (
1006 l_process,
1007 'TOTAL_RES_ROW_COUNT'
1008 );
1009
1010 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1011 (
1012 l_process,
1013 'TOTAL_RES_ROW_COUNT',
1014 l_row_count
1015 );
1016
1017 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1018 (
1019 l_process,
1020 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_PA(p_worker_id);'
1021 );
1022
1023 commit;
1024
1025 end EXPAND_RMR_CAL_PA;
1026
1027
1028 -- -----------------------------------------------------
1029 -- procedure EXPAND_RMR_CAL_GL
1030 -- -----------------------------------------------------
1031 procedure EXPAND_RMR_CAL_GL (p_worker_id in number) is
1032
1033 l_process varchar2(30);
1034 l_row_count number;
1035
1036 begin
1037
1038 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1039
1040 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1041 (
1042 l_process,
1043 'GL_CALENDAR_FLAG'
1044 ) = 'N') then
1045 return;
1046 end if;
1047
1048 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1049
1050 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1051 (
1052 l_process,
1053 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_GL(p_worker_id);'
1054 )) then
1055 return;
1056 end if;
1057
1058 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
1059 (
1060 WORKER_ID,
1061 RECORD_TYPE,
1062 PROJECT_ID,
1063 PERSON_ID,
1064 EXPENDITURE_ORG_ID,
1065 EXPENDITURE_ORGANIZATION_ID,
1066 WORK_TYPE_ID,
1067 JOB_ID,
1068 TIME_ID,
1069 PERIOD_TYPE_ID,
1070 CALENDAR_TYPE,
1071 CAPACITY_HRS,
1072 TOTAL_HRS_A,
1073 BILL_HRS_A,
1074 CONF_HRS_S,
1075 PROV_HRS_S,
1076 UNASSIGNED_HRS_S,
1077 CONF_OVERCOM_HRS_S,
1078 PROV_OVERCOM_HRS_S
1079 )
1080 select /*+ ordered
1081 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1082 full(tmp1) use_hash(tmp1) parallel(tmp1) */
1083 p_worker_id,
1084 tmp1.RECORD_TYPE,
1085 -1,
1086 tmp1.PERSON_ID,
1087 tmp1.EXPENDITURE_ORG_ID,
1088 tmp1.EXPENDITURE_ORGANIZATION_ID,
1089 tmp1.WORK_TYPE_ID,
1090 tmp1.JOB_ID,
1091 case when grouping(time.CAL_YEAR_ID) = 0 and
1092 grouping(time.CAL_QTR_ID) = 0 and
1093 grouping(time.CAL_PERIOD_ID) = 0
1094 then time.CAL_PERIOD_ID
1095 when grouping(time.CAL_YEAR_ID) = 0 and
1096 grouping(time.CAL_QTR_ID) = 0 and
1097 grouping(time.CAL_PERIOD_ID) = 1
1098 then time.CAL_QTR_ID
1099 when grouping(time.CAL_YEAR_ID) = 0 and
1100 grouping(time.CAL_QTR_ID) = 1 and
1101 grouping(time.CAL_PERIOD_ID) = 1
1102 then time.CAL_YEAR_ID
1103 end TIME_ID,
1104 case when grouping(time.CAL_YEAR_ID) = 0 and
1105 grouping(time.CAL_QTR_ID) = 0 and
1106 grouping(time.CAL_PERIOD_ID) = 0
1107 then 32
1108 when grouping(time.CAL_YEAR_ID) = 0 and
1109 grouping(time.CAL_QTR_ID) = 0 and
1110 grouping(time.CAL_PERIOD_ID) = 1
1111 then 64
1112 when grouping(time.CAL_YEAR_ID) = 0 and
1113 grouping(time.CAL_QTR_ID) = 1 and
1114 grouping(time.CAL_PERIOD_ID) = 1
1115 then 128
1116 end PERIOD_TYPE_ID,
1117 'G' CALENDAR_TYPE,
1118 sum(tmp1.CAPACITY_HRS) CAPACITY_HRS,
1119 sum(tmp1.TOTAL_HRS_A) TOTAL_HRS_A,
1120 sum(tmp1.BILL_HRS_A) BILL_HRS_A,
1121 sum(tmp1.CONF_HRS_S) CONF_HRS_S,
1122 sum(tmp1.PROV_HRS_S) PROV_HRS_S,
1123 sum(tmp1.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1124 sum(tmp1.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1125 sum(tmp1.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
1126 from
1127 FII_TIME_CAL_DAY_MV time,
1128 PJI_RM_AGGR_RES1 tmp1
1129 where
1130 tmp1.WORKER_ID = p_worker_id and
1131 tmp1.RECORD_TYPE <> 'N' and
1132 tmp1.PERIOD_TYPE_ID = 1 and
1133 tmp1.CALENDAR_TYPE = 'C' and
1134 to_date(to_char(tmp1.TIME_ID), 'J') = time.REPORT_DATE and
1135 tmp1.GL_CALENDAR_ID = time.CALENDAR_ID
1136 group by
1137 tmp1.RECORD_TYPE,
1138 tmp1.PERSON_ID,
1139 tmp1.EXPENDITURE_ORGANIZATION_ID,
1140 tmp1.EXPENDITURE_ORG_ID,
1141 tmp1.WORK_TYPE_ID,
1142 tmp1.JOB_ID,
1143 rollup (time.CAL_YEAR_ID,
1144 time.CAL_QTR_ID,
1145 time.CAL_PERIOD_ID)
1146 having
1147 not (grouping(time.CAL_YEAR_ID) = 1 and
1148 grouping(time.CAL_QTR_ID) = 1 and
1149 grouping(time.CAL_PERIOD_ID) = 1);
1150
1151 l_row_count := sql%rowcount;
1152
1153 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1154 (
1155 l_process,
1156 'TOTAL_RES_ROW_COUNT'
1157 );
1158
1159 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1160 (
1161 l_process,
1162 'TOTAL_RES_ROW_COUNT',
1163 l_row_count
1164 );
1165
1166 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1167 (
1168 l_process,
1169 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_GL(p_worker_id);'
1170 );
1171
1172 commit;
1173
1174 end EXPAND_RMR_CAL_GL;
1175
1176
1177 -- -----------------------------------------------------
1178 -- procedure EXPAND_RMR_CAL_WK
1179 -- -----------------------------------------------------
1180 procedure EXPAND_RMR_CAL_WK (p_worker_id in number) is
1181
1182 l_process varchar2(30);
1183 l_row_count number;
1184
1185 begin
1186
1187 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1188
1189 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1190 (
1191 l_process,
1192 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_WK(p_worker_id);'
1193 )) then
1194 return;
1195 end if;
1196
1197 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
1198 (
1199 WORKER_ID,
1200 RECORD_TYPE,
1201 PROJECT_ID,
1202 PERSON_ID,
1203 EXPENDITURE_ORG_ID,
1204 EXPENDITURE_ORGANIZATION_ID,
1205 WORK_TYPE_ID,
1206 JOB_ID,
1207 TIME_ID,
1208 PERIOD_TYPE_ID,
1209 CALENDAR_TYPE,
1210 CAPACITY_HRS,
1211 TOTAL_HRS_A,
1212 BILL_HRS_A,
1213 CONF_HRS_S,
1214 PROV_HRS_S,
1215 UNASSIGNED_HRS_S,
1216 CONF_OVERCOM_HRS_S,
1217 PROV_OVERCOM_HRS_S
1218 )
1219 select /*+ ordered
1220 full(time) use_hash(time) swap_join_inputs(time)
1221 full(tmp1) use_hash(tmp1) parallel(tmp1) */
1222 p_worker_id,
1223 tmp1.RECORD_TYPE,
1224 -1,
1225 tmp1.PERSON_ID,
1226 tmp1.EXPENDITURE_ORG_ID,
1227 tmp1.EXPENDITURE_ORGANIZATION_ID,
1228 tmp1.WORK_TYPE_ID,
1229 tmp1.JOB_ID,
1230 time.WEEK_ID TIME_ID,
1231 16 PERIOD_TYPE_ID,
1232 'E' CALENDAR_TYPE,
1233 sum(tmp1.CAPACITY_HRS) CAPACITY_HRS,
1234 sum(tmp1.TOTAL_HRS_A) TOTAL_HRS_A,
1235 sum(tmp1.BILL_HRS_A) BILL_HRS_A,
1236 sum(tmp1.CONF_HRS_S) CONF_HRS_S,
1237 sum(tmp1.PROV_HRS_S) PROV_HRS_S,
1238 sum(tmp1.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1239 sum(tmp1.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1240 sum(tmp1.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
1241 from
1242 FII_TIME_DAY time,
1243 PJI_RM_AGGR_RES1 tmp1
1244 where
1245 tmp1.WORKER_ID = p_worker_id and
1246 tmp1.RECORD_TYPE <> 'N' and
1247 tmp1.PERIOD_TYPE_ID = 1 and
1248 tmp1.CALENDAR_TYPE = 'C' and
1249 tmp1.TIME_ID = time.REPORT_DATE_JULIAN
1250 group by
1251 tmp1.RECORD_TYPE,
1252 tmp1.PERSON_ID,
1253 tmp1.EXPENDITURE_ORGANIZATION_ID,
1254 tmp1.EXPENDITURE_ORG_ID,
1255 tmp1.WORK_TYPE_ID,
1256 tmp1.JOB_ID,
1257 time.WEEK_ID;
1258
1259 l_row_count := sql%rowcount;
1260
1261 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1262 (
1263 l_process,
1264 'TOTAL_RES_ROW_COUNT'
1265 );
1266
1267 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1268 (
1269 l_process,
1270 'TOTAL_RES_ROW_COUNT',
1271 l_row_count
1272 );
1273
1274 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1275 (
1276 l_process,
1277 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_WK(p_worker_id);'
1278 );
1279
1280 commit;
1281
1282 end EXPAND_RMR_CAL_WK;
1283
1284
1285 -- -----------------------------------------------------
1286 -- procedure EXPAND_RMS_CAL_EN
1287 -- -----------------------------------------------------
1288 procedure EXPAND_RMS_CAL_EN (p_worker_id in number) is
1289
1290 l_process varchar2(30);
1291 l_row_count number;
1292
1293 begin
1294
1295 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1296
1297 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1298 (
1299 l_process,
1300 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_EN(p_worker_id);'
1301 )) then
1302 return;
1303 end if;
1304
1305 insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1306 (
1307 WORKER_ID,
1308 PERSON_ID,
1309 EXPENDITURE_ORG_ID,
1310 EXPENDITURE_ORGANIZATION_ID,
1311 JOB_ID,
1312 TIME_ID,
1313 PERIOD_TYPE_ID,
1314 CALENDAR_TYPE,
1315 CAPACITY_HRS,
1316 TOTAL_HRS_A,
1317 MISSING_HRS_A,
1318 TOTAL_WTD_ORG_HRS_A,
1319 TOTAL_WTD_RES_HRS_A,
1320 BILL_HRS_A,
1321 BILL_WTD_ORG_HRS_A,
1322 BILL_WTD_RES_HRS_A,
1323 TRAINING_HRS_A,
1324 UNASSIGNED_HRS_A,
1325 REDUCIBLE_CAPACITY_HRS_A,
1326 REDUCE_CAPACITY_HRS_A,
1327 CONF_HRS_S,
1328 CONF_WTD_ORG_HRS_S,
1329 CONF_WTD_RES_HRS_S,
1330 CONF_BILL_HRS_S,
1331 CONF_BILL_WTD_ORG_HRS_S,
1332 CONF_BILL_WTD_RES_HRS_S,
1333 PROV_HRS_S,
1334 PROV_WTD_ORG_HRS_S,
1335 PROV_WTD_RES_HRS_S,
1336 PROV_BILL_HRS_S,
1337 PROV_BILL_WTD_ORG_HRS_S,
1338 PROV_BILL_WTD_RES_HRS_S,
1339 TRAINING_HRS_S,
1340 UNASSIGNED_HRS_S,
1341 REDUCIBLE_CAPACITY_HRS_S,
1342 REDUCE_CAPACITY_HRS_S,
1343 CONF_OVERCOM_HRS_S,
1344 PROV_OVERCOM_HRS_S,
1345 AVAILABLE_HRS_BKT1_S,
1346 AVAILABLE_HRS_BKT2_S,
1347 AVAILABLE_HRS_BKT3_S,
1348 AVAILABLE_HRS_BKT4_S,
1349 AVAILABLE_HRS_BKT5_S,
1350 AVAILABLE_RES_COUNT_BKT1_S,
1351 AVAILABLE_RES_COUNT_BKT2_S,
1352 AVAILABLE_RES_COUNT_BKT3_S,
1353 AVAILABLE_RES_COUNT_BKT4_S,
1354 AVAILABLE_RES_COUNT_BKT5_S,
1355 TOTAL_RES_COUNT
1356 )
1357 select /*+ ordered
1358 full(time) use_hash(time) swap_join_inputs(time)
1359 full(tmp2) use_hash(tmp2) parallel(tmp2) */
1360 p_worker_id,
1361 tmp2.PERSON_ID,
1362 tmp2.EXPENDITURE_ORG_ID,
1363 tmp2.EXPENDITURE_ORGANIZATION_ID,
1364 tmp2.JOB_ID,
1365 case when grouping(time.ENT_YEAR_ID) = 0 and
1366 grouping(time.ENT_QTR_ID) = 0 and
1367 grouping(time.ENT_PERIOD_ID) = 0
1368 then time.ENT_PERIOD_ID
1369 when grouping(time.ENT_YEAR_ID) = 0 and
1370 grouping(time.ENT_QTR_ID) = 0 and
1371 grouping(time.ENT_PERIOD_ID) = 1
1372 then time.ENT_QTR_ID
1373 when grouping(time.ENT_YEAR_ID) = 0 and
1374 grouping(time.ENT_QTR_ID) = 1 and
1375 grouping(time.ENT_PERIOD_ID) = 1
1376 then time.ENT_YEAR_ID
1377 end TIME_ID,
1378 case when grouping(time.ENT_YEAR_ID) = 0 and
1379 grouping(time.ENT_QTR_ID) = 0 and
1380 grouping(time.ENT_PERIOD_ID) = 0
1381 then 32
1382 when grouping(time.ENT_YEAR_ID) = 0 and
1383 grouping(time.ENT_QTR_ID) = 0 and
1384 grouping(time.ENT_PERIOD_ID) = 1
1385 then 64
1386 when grouping(time.ENT_YEAR_ID) = 0 and
1387 grouping(time.ENT_QTR_ID) = 1 and
1388 grouping(time.ENT_PERIOD_ID) = 1
1389 then 128
1390 end PERIOD_TYPE_ID,
1391 'E' CALENDAR_TYPE,
1392 sum(tmp2.CAPACITY_HRS) CAPACITY_HRS,
1393 sum(tmp2.TOTAL_HRS_A) TOTAL_HRS_A,
1394 sum(tmp2.MISSING_HRS_A) MISSING_HRS_A,
1395 sum(tmp2.TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
1396 sum(tmp2.TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
1397 sum(tmp2.BILL_HRS_A) BILL_HRS_A,
1398 sum(tmp2.BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
1399 sum(tmp2.BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
1400 sum(tmp2.TRAINING_HRS_A) TRAINING_HRS_A,
1401 sum(tmp2.UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
1402 sum(tmp2.REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
1403 sum(tmp2.REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
1404 sum(tmp2.CONF_HRS_S) CONF_HRS_S,
1405 sum(tmp2.CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
1406 sum(tmp2.CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
1407 sum(tmp2.CONF_BILL_HRS_S) CONF_BILL_HRS_S,
1408 sum(tmp2.CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
1409 sum(tmp2.CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
1410 sum(tmp2.PROV_HRS_S) PROV_HRS_S,
1411 sum(tmp2.PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
1412 sum(tmp2.PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
1413 sum(tmp2.PROV_BILL_HRS_S) PROV_BILL_HRS_S,
1414 sum(tmp2.PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
1415 sum(tmp2.PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
1416 sum(tmp2.TRAINING_HRS_S) TRAINING_HRS_S,
1417 sum(tmp2.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1418 sum(tmp2.REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
1419 sum(tmp2.REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
1420 sum(tmp2.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1421 sum(tmp2.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
1422 sum(tmp2.AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
1423 sum(tmp2.AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
1424 sum(tmp2.AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
1425 sum(tmp2.AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
1426 sum(tmp2.AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
1427 sum(tmp2.AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
1428 sum(tmp2.AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
1429 sum(tmp2.AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
1430 sum(tmp2.AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
1431 sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1432 sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1433 from
1434 FII_TIME_DAY time,
1435 PJI_RM_AGGR_RES2 tmp2
1436 where
1437 tmp2.WORKER_ID = p_worker_id and
1438 tmp2.PERIOD_TYPE_ID = 1 and
1439 tmp2.CALENDAR_TYPE = 'C' and
1440 tmp2.TIME_ID = time.REPORT_DATE_JULIAN
1441 group by
1442 tmp2.PERSON_ID,
1443 tmp2.EXPENDITURE_ORG_ID,
1444 tmp2.EXPENDITURE_ORGANIZATION_ID,
1445 tmp2.JOB_ID,
1446 rollup (time.ENT_YEAR_ID,
1447 time.ENT_QTR_ID,
1448 time.ENT_PERIOD_ID)
1449 having
1450 not (grouping(time.ENT_YEAR_ID) = 1 and
1451 grouping(time.ENT_QTR_ID) = 1 and
1452 grouping(time.ENT_PERIOD_ID) = 1);
1453
1454 l_row_count := sql%rowcount;
1455
1456 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1457 (
1458 l_process,
1459 'TOTAL_RES_ROW_COUNT'
1460 );
1461
1462 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1463 (
1464 l_process,
1465 'TOTAL_RES_ROW_COUNT',
1466 l_row_count
1467 );
1468
1469 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1470 (
1471 l_process,
1472 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_EN(p_worker_id);'
1473 );
1474
1475 commit;
1476
1477 end EXPAND_RMS_CAL_EN;
1478
1479
1480 -- -----------------------------------------------------
1481 -- procedure EXPAND_RMS_CAL_PA
1482 -- -----------------------------------------------------
1483 procedure EXPAND_RMS_CAL_PA (p_worker_id in number) is
1484
1485 l_process varchar2(30);
1486 l_row_count number;
1487
1488 begin
1489
1490 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1491
1492 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1493 (
1494 l_process,
1495 'PA_CALENDAR_FLAG'
1496 ) = 'N') then
1497 return;
1498 end if;
1499
1500 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1501
1502 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1503 (
1504 l_process,
1505 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_PA(p_worker_id);'
1506 )) then
1507 return;
1508 end if;
1509
1510 insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1511 (
1512 WORKER_ID,
1513 PERSON_ID,
1514 EXPENDITURE_ORG_ID,
1515 EXPENDITURE_ORGANIZATION_ID,
1516 JOB_ID,
1517 TIME_ID,
1518 PERIOD_TYPE_ID,
1519 CALENDAR_TYPE,
1520 CAPACITY_HRS,
1521 TOTAL_HRS_A,
1522 MISSING_HRS_A,
1523 TOTAL_WTD_ORG_HRS_A,
1524 TOTAL_WTD_RES_HRS_A,
1525 BILL_HRS_A,
1526 BILL_WTD_ORG_HRS_A,
1527 BILL_WTD_RES_HRS_A,
1528 TRAINING_HRS_A,
1529 UNASSIGNED_HRS_A,
1530 REDUCIBLE_CAPACITY_HRS_A,
1531 REDUCE_CAPACITY_HRS_A,
1532 CONF_HRS_S,
1533 CONF_WTD_ORG_HRS_S,
1534 CONF_WTD_RES_HRS_S,
1535 CONF_BILL_HRS_S,
1536 CONF_BILL_WTD_ORG_HRS_S,
1537 CONF_BILL_WTD_RES_HRS_S,
1538 PROV_HRS_S,
1539 PROV_WTD_ORG_HRS_S,
1540 PROV_WTD_RES_HRS_S,
1541 PROV_BILL_HRS_S,
1542 PROV_BILL_WTD_ORG_HRS_S,
1543 PROV_BILL_WTD_RES_HRS_S,
1544 TRAINING_HRS_S,
1545 UNASSIGNED_HRS_S,
1546 REDUCIBLE_CAPACITY_HRS_S,
1547 REDUCE_CAPACITY_HRS_S,
1548 CONF_OVERCOM_HRS_S,
1549 PROV_OVERCOM_HRS_S,
1550 AVAILABLE_HRS_BKT1_S,
1551 AVAILABLE_HRS_BKT2_S,
1552 AVAILABLE_HRS_BKT3_S,
1553 AVAILABLE_HRS_BKT4_S,
1554 AVAILABLE_HRS_BKT5_S,
1555 AVAILABLE_RES_COUNT_BKT1_S,
1556 AVAILABLE_RES_COUNT_BKT2_S,
1557 AVAILABLE_RES_COUNT_BKT3_S,
1558 AVAILABLE_RES_COUNT_BKT4_S,
1559 AVAILABLE_RES_COUNT_BKT5_S,
1560 TOTAL_RES_COUNT
1561 )
1562 select /*+ ordered
1563 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1564 full(tmp2) use_hash(tmp2) parallel(tmp2) */
1565 p_worker_id,
1566 tmp2.PERSON_ID,
1567 tmp2.EXPENDITURE_ORG_ID,
1568 tmp2.EXPENDITURE_ORGANIZATION_ID,
1569 tmp2.JOB_ID,
1570 case when grouping(time.CAL_YEAR_ID) = 0 and
1571 grouping(time.CAL_QTR_ID) = 0 and
1572 grouping(time.CAL_PERIOD_ID) = 0
1573 then time.CAL_PERIOD_ID
1574 when grouping(time.CAL_YEAR_ID) = 0 and
1575 grouping(time.CAL_QTR_ID) = 0 and
1576 grouping(time.CAL_PERIOD_ID) = 1
1577 then time.CAL_QTR_ID
1578 when grouping(time.CAL_YEAR_ID) = 0 and
1579 grouping(time.CAL_QTR_ID) = 1 and
1580 grouping(time.CAL_PERIOD_ID) = 1
1581 then time.CAL_YEAR_ID
1582 end TIME_ID,
1583 case when grouping(time.CAL_YEAR_ID) = 0 and
1584 grouping(time.CAL_QTR_ID) = 0 and
1585 grouping(time.CAL_PERIOD_ID) = 0
1586 then 32
1587 when grouping(time.CAL_YEAR_ID) = 0 and
1588 grouping(time.CAL_QTR_ID) = 0 and
1589 grouping(time.CAL_PERIOD_ID) = 1
1590 then 64
1591 when grouping(time.CAL_YEAR_ID) = 0 and
1592 grouping(time.CAL_QTR_ID) = 1 and
1593 grouping(time.CAL_PERIOD_ID) = 1
1594 then 128
1595 end PERIOD_TYPE_ID,
1596 'P' CALENDAR_TYPE,
1597 sum(tmp2.CAPACITY_HRS) CAPACITY_HRS,
1598 sum(tmp2.TOTAL_HRS_A) TOTAL_HRS_A,
1599 sum(tmp2.MISSING_HRS_A) MISSING_HRS_A,
1600 sum(tmp2.TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
1601 sum(tmp2.TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
1602 sum(tmp2.BILL_HRS_A) BILL_HRS_A,
1603 sum(tmp2.BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
1604 sum(tmp2.BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
1605 sum(tmp2.TRAINING_HRS_A) TRAINING_HRS_A,
1606 sum(tmp2.UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
1607 sum(tmp2.REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
1608 sum(tmp2.REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
1609 sum(tmp2.CONF_HRS_S) CONF_HRS_S,
1610 sum(tmp2.CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
1611 sum(tmp2.CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
1612 sum(tmp2.CONF_BILL_HRS_S) CONF_BILL_HRS_S,
1613 sum(tmp2.CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
1614 sum(tmp2.CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
1615 sum(tmp2.PROV_HRS_S) PROV_HRS_S,
1616 sum(tmp2.PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
1617 sum(tmp2.PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
1618 sum(tmp2.PROV_BILL_HRS_S) PROV_BILL_HRS_S,
1619 sum(tmp2.PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
1620 sum(tmp2.PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
1621 sum(tmp2.TRAINING_HRS_S) TRAINING_HRS_S,
1622 sum(tmp2.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1623 sum(tmp2.REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
1624 sum(tmp2.REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
1625 sum(tmp2.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1626 sum(tmp2.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
1627 sum(tmp2.AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
1628 sum(tmp2.AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
1629 sum(tmp2.AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
1630 sum(tmp2.AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
1631 sum(tmp2.AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
1632 sum(tmp2.AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
1633 sum(tmp2.AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
1634 sum(tmp2.AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
1635 sum(tmp2.AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
1636 sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1637 sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1638 from
1639 FII_TIME_CAL_DAY_MV time,
1640 PJI_RM_AGGR_RES2 tmp2
1641 where
1642 tmp2.WORKER_ID = p_worker_id and
1643 tmp2.PERIOD_TYPE_ID = 1 and
1644 tmp2.CALENDAR_TYPE = 'C' and
1645 to_date(to_char(tmp2.TIME_ID), 'J') = time.REPORT_DATE and
1646 tmp2.PA_CALENDAR_ID = time.CALENDAR_ID
1647 group by
1648 tmp2.PERSON_ID,
1649 tmp2.EXPENDITURE_ORGANIZATION_ID,
1650 tmp2.EXPENDITURE_ORG_ID,
1651 tmp2.JOB_ID,
1652 rollup (time.CAL_YEAR_ID,
1653 time.CAL_QTR_ID,
1654 time.CAL_PERIOD_ID)
1655 having
1656 not (grouping(time.CAL_YEAR_ID) = 1 and
1657 grouping(time.CAL_QTR_ID) = 1 and
1658 grouping(time.CAL_PERIOD_ID) = 1);
1659
1660 l_row_count := sql%rowcount;
1661
1662 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1663 (
1664 l_process,
1665 'TOTAL_RES_ROW_COUNT'
1666 );
1667
1668 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1669 (
1670 l_process,
1671 'TOTAL_RES_ROW_COUNT',
1672 l_row_count
1673 );
1674
1675 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1676 (
1677 l_process,
1678 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_PA(p_worker_id);'
1679 );
1680
1681 commit;
1682
1683 end EXPAND_RMS_CAL_PA;
1684
1685
1686 -- -----------------------------------------------------
1687 -- procedure EXPAND_RMS_CAL_GL
1688 -- -----------------------------------------------------
1689 procedure EXPAND_RMS_CAL_GL (p_worker_id in number) is
1690
1691 l_process varchar2(30);
1692 l_row_count number;
1693
1694 begin
1695
1696 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1697
1698 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1699 (
1700 l_process,
1701 'GL_CALENDAR_FLAG'
1702 ) = 'N') then
1703 return;
1704 end if;
1705
1706 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1707
1708 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1709 (
1710 l_process,
1711 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_GL(p_worker_id);'
1712 )) then
1713 return;
1714 end if;
1715
1716 insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1717 (
1718 WORKER_ID,
1719 PERSON_ID,
1720 EXPENDITURE_ORG_ID,
1721 EXPENDITURE_ORGANIZATION_ID,
1722 JOB_ID,
1723 TIME_ID,
1724 PERIOD_TYPE_ID,
1725 CALENDAR_TYPE,
1726 CAPACITY_HRS,
1727 TOTAL_HRS_A,
1728 MISSING_HRS_A,
1729 TOTAL_WTD_ORG_HRS_A,
1730 TOTAL_WTD_RES_HRS_A,
1731 BILL_HRS_A,
1732 BILL_WTD_ORG_HRS_A,
1733 BILL_WTD_RES_HRS_A,
1734 TRAINING_HRS_A,
1735 UNASSIGNED_HRS_A,
1736 REDUCIBLE_CAPACITY_HRS_A,
1737 REDUCE_CAPACITY_HRS_A,
1738 CONF_HRS_S,
1739 CONF_WTD_ORG_HRS_S,
1740 CONF_WTD_RES_HRS_S,
1741 CONF_BILL_HRS_S,
1742 CONF_BILL_WTD_ORG_HRS_S,
1743 CONF_BILL_WTD_RES_HRS_S,
1744 PROV_HRS_S,
1745 PROV_WTD_ORG_HRS_S,
1746 PROV_WTD_RES_HRS_S,
1747 PROV_BILL_HRS_S,
1748 PROV_BILL_WTD_ORG_HRS_S,
1749 PROV_BILL_WTD_RES_HRS_S,
1750 TRAINING_HRS_S,
1751 UNASSIGNED_HRS_S,
1752 REDUCIBLE_CAPACITY_HRS_S,
1753 REDUCE_CAPACITY_HRS_S,
1754 CONF_OVERCOM_HRS_S,
1755 PROV_OVERCOM_HRS_S,
1756 AVAILABLE_HRS_BKT1_S,
1757 AVAILABLE_HRS_BKT2_S,
1758 AVAILABLE_HRS_BKT3_S,
1759 AVAILABLE_HRS_BKT4_S,
1760 AVAILABLE_HRS_BKT5_S,
1761 AVAILABLE_RES_COUNT_BKT1_S,
1762 AVAILABLE_RES_COUNT_BKT2_S,
1763 AVAILABLE_RES_COUNT_BKT3_S,
1764 AVAILABLE_RES_COUNT_BKT4_S,
1765 AVAILABLE_RES_COUNT_BKT5_S,
1766 TOTAL_RES_COUNT
1767 )
1768 select /*+ ordered
1769 full(time) use_hash(time) parallel(time) swap_join_inputs(time)
1770 full(tmp2) use_hash(tmp2) parallel(tmp2) */
1771 p_worker_id,
1772 tmp2.PERSON_ID,
1773 tmp2.EXPENDITURE_ORG_ID,
1774 tmp2.EXPENDITURE_ORGANIZATION_ID,
1775 tmp2.JOB_ID,
1776 case when grouping(time.CAL_YEAR_ID) = 0 and
1777 grouping(time.CAL_QTR_ID) = 0 and
1778 grouping(time.CAL_PERIOD_ID) = 0
1779 then time.CAL_PERIOD_ID
1780 when grouping(time.CAL_YEAR_ID) = 0 and
1781 grouping(time.CAL_QTR_ID) = 0 and
1782 grouping(time.CAL_PERIOD_ID) = 1
1783 then time.CAL_QTR_ID
1784 when grouping(time.CAL_YEAR_ID) = 0 and
1785 grouping(time.CAL_QTR_ID) = 1 and
1786 grouping(time.CAL_PERIOD_ID) = 1
1787 then time.CAL_YEAR_ID
1788 end TIME_ID,
1789 case when grouping(time.CAL_YEAR_ID) = 0 and
1790 grouping(time.CAL_QTR_ID) = 0 and
1791 grouping(time.CAL_PERIOD_ID) = 0
1792 then 32
1793 when grouping(time.CAL_YEAR_ID) = 0 and
1794 grouping(time.CAL_QTR_ID) = 0 and
1795 grouping(time.CAL_PERIOD_ID) = 1
1796 then 64
1797 when grouping(time.CAL_YEAR_ID) = 0 and
1798 grouping(time.CAL_QTR_ID) = 1 and
1799 grouping(time.CAL_PERIOD_ID) = 1
1800 then 128
1801 end PERIOD_TYPE_ID,
1802 'G' CALENDAR_TYPE,
1803 sum(tmp2.CAPACITY_HRS) CAPACITY_HRS,
1804 sum(tmp2.TOTAL_HRS_A) TOTAL_HRS_A,
1805 sum(tmp2.MISSING_HRS_A) MISSING_HRS_A,
1806 sum(tmp2.TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
1807 sum(tmp2.TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
1808 sum(tmp2.BILL_HRS_A) BILL_HRS_A,
1809 sum(tmp2.BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
1810 sum(tmp2.BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
1811 sum(tmp2.TRAINING_HRS_A) TRAINING_HRS_A,
1812 sum(tmp2.UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
1813 sum(tmp2.REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
1814 sum(tmp2.REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
1815 sum(tmp2.CONF_HRS_S) CONF_HRS_S,
1816 sum(tmp2.CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
1817 sum(tmp2.CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
1818 sum(tmp2.CONF_BILL_HRS_S) CONF_BILL_HRS_S,
1819 sum(tmp2.CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
1820 sum(tmp2.CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
1821 sum(tmp2.PROV_HRS_S) PROV_HRS_S,
1822 sum(tmp2.PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
1823 sum(tmp2.PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
1824 sum(tmp2.PROV_BILL_HRS_S) PROV_BILL_HRS_S,
1825 sum(tmp2.PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
1826 sum(tmp2.PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
1827 sum(tmp2.TRAINING_HRS_S) TRAINING_HRS_S,
1828 sum(tmp2.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1829 sum(tmp2.REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
1830 sum(tmp2.REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
1831 sum(tmp2.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1832 sum(tmp2.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
1833 sum(tmp2.AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
1834 sum(tmp2.AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
1835 sum(tmp2.AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
1836 sum(tmp2.AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
1837 sum(tmp2.AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
1838 sum(tmp2.AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
1839 sum(tmp2.AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
1840 sum(tmp2.AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
1841 sum(tmp2.AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
1842 sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
1843 sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
1844 from
1845 FII_TIME_CAL_DAY_MV time,
1846 PJI_RM_AGGR_RES2 tmp2
1847 where
1848 tmp2.WORKER_ID = p_worker_id and
1849 tmp2.PERIOD_TYPE_ID = 1 and
1850 tmp2.CALENDAR_TYPE = 'C' and
1851 to_date(to_char(tmp2.TIME_ID), 'J') = time.REPORT_DATE and
1852 tmp2.GL_CALENDAR_ID = time.CALENDAR_ID
1853 group by
1854 tmp2.PERSON_ID,
1855 tmp2.EXPENDITURE_ORGANIZATION_ID,
1856 tmp2.EXPENDITURE_ORG_ID,
1857 tmp2.JOB_ID,
1858 rollup (time.CAL_YEAR_ID,
1859 time.CAL_QTR_ID,
1860 time.CAL_PERIOD_ID)
1861 having
1862 not (grouping(time.CAL_YEAR_ID) = 1 and
1863 grouping(time.CAL_QTR_ID) = 1 and
1864 grouping(time.CAL_PERIOD_ID) = 1);
1865
1866 l_row_count := sql%rowcount;
1867
1868 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1869 (
1870 l_process,
1871 'TOTAL_RES_ROW_COUNT'
1872 );
1873
1874 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
1875 (
1876 l_process,
1877 'TOTAL_RES_ROW_COUNT',
1878 l_row_count
1879 );
1880
1881 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1882 (
1883 l_process,
1884 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_GL(p_worker_id);'
1885 );
1886
1887 commit;
1888
1889 end EXPAND_RMS_CAL_GL;
1890
1891
1892 -- -----------------------------------------------------
1893 -- procedure EXPAND_RMS_CAL_WK
1894 -- -----------------------------------------------------
1895 procedure EXPAND_RMS_CAL_WK (p_worker_id in number) is
1896
1897 l_process varchar2(30);
1898 l_row_count number;
1899
1900 begin
1901
1902 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1903
1904 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1905 (
1906 l_process,
1907 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_WK(p_worker_id);'
1908 )) then
1909 return;
1910 end if;
1911
1912 insert /*+ append parallel(res2_i) */ into PJI_RM_AGGR_RES2 res2_i
1913 (
1914 WORKER_ID,
1915 PERSON_ID,
1916 EXPENDITURE_ORG_ID,
1917 EXPENDITURE_ORGANIZATION_ID,
1918 JOB_ID,
1919 TIME_ID,
1920 PERIOD_TYPE_ID,
1921 CALENDAR_TYPE,
1922 CAPACITY_HRS,
1923 TOTAL_HRS_A,
1924 MISSING_HRS_A,
1925 TOTAL_WTD_ORG_HRS_A,
1926 TOTAL_WTD_RES_HRS_A,
1927 BILL_HRS_A,
1928 BILL_WTD_ORG_HRS_A,
1929 BILL_WTD_RES_HRS_A,
1930 TRAINING_HRS_A,
1931 UNASSIGNED_HRS_A,
1932 REDUCIBLE_CAPACITY_HRS_A,
1933 REDUCE_CAPACITY_HRS_A,
1934 CONF_HRS_S,
1935 CONF_WTD_ORG_HRS_S,
1936 CONF_WTD_RES_HRS_S,
1937 CONF_BILL_HRS_S,
1938 CONF_BILL_WTD_ORG_HRS_S,
1939 CONF_BILL_WTD_RES_HRS_S,
1940 PROV_HRS_S,
1941 PROV_WTD_ORG_HRS_S,
1942 PROV_WTD_RES_HRS_S,
1943 PROV_BILL_HRS_S,
1944 PROV_BILL_WTD_ORG_HRS_S,
1945 PROV_BILL_WTD_RES_HRS_S,
1946 TRAINING_HRS_S,
1947 UNASSIGNED_HRS_S,
1948 REDUCIBLE_CAPACITY_HRS_S,
1949 REDUCE_CAPACITY_HRS_S,
1950 CONF_OVERCOM_HRS_S,
1951 PROV_OVERCOM_HRS_S,
1952 AVAILABLE_HRS_BKT1_S,
1953 AVAILABLE_HRS_BKT2_S,
1954 AVAILABLE_HRS_BKT3_S,
1955 AVAILABLE_HRS_BKT4_S,
1956 AVAILABLE_HRS_BKT5_S,
1957 AVAILABLE_RES_COUNT_BKT1_S,
1958 AVAILABLE_RES_COUNT_BKT2_S,
1959 AVAILABLE_RES_COUNT_BKT3_S,
1960 AVAILABLE_RES_COUNT_BKT4_S,
1961 AVAILABLE_RES_COUNT_BKT5_S,
1962 TOTAL_RES_COUNT
1963 )
1964 select /*+ ordered
1965 full(time) use_hash(time) swap_join_inputs(time)
1966 full(tmp2) use_hash(tmp2) parallel(tmp2) */
1967 p_worker_id,
1968 tmp2.PERSON_ID,
1969 tmp2.EXPENDITURE_ORG_ID,
1970 tmp2.EXPENDITURE_ORGANIZATION_ID,
1971 tmp2.JOB_ID,
1972 time.WEEK_ID TIME_ID,
1973 16 PERIOD_TYPE_ID,
1974 'E' CALENDAR_TYPE,
1975 sum(tmp2.CAPACITY_HRS) CAPACITY_HRS,
1976 sum(tmp2.TOTAL_HRS_A) TOTAL_HRS_A,
1977 sum(tmp2.MISSING_HRS_A) MISSING_HRS_A,
1978 sum(tmp2.TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
1979 sum(tmp2.TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
1980 sum(tmp2.BILL_HRS_A) BILL_HRS_A,
1981 sum(tmp2.BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
1982 sum(tmp2.BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
1983 sum(tmp2.TRAINING_HRS_A) TRAINING_HRS_A,
1984 sum(tmp2.UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
1985 sum(tmp2.REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
1986 sum(tmp2.REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
1987 sum(tmp2.CONF_HRS_S) CONF_HRS_S,
1988 sum(tmp2.CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
1989 sum(tmp2.CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
1990 sum(tmp2.CONF_BILL_HRS_S) CONF_BILL_HRS_S,
1991 sum(tmp2.CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
1992 sum(tmp2.CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
1993 sum(tmp2.PROV_HRS_S) PROV_HRS_S,
1994 sum(tmp2.PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
1995 sum(tmp2.PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
1996 sum(tmp2.PROV_BILL_HRS_S) PROV_BILL_HRS_S,
1997 sum(tmp2.PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
1998 sum(tmp2.PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
1999 sum(tmp2.TRAINING_HRS_S) TRAINING_HRS_S,
2000 sum(tmp2.UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
2001 sum(tmp2.REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
2002 sum(tmp2.REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
2003 sum(tmp2.CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
2004 sum(tmp2.PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
2005 sum(tmp2.AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
2006 sum(tmp2.AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
2007 sum(tmp2.AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
2008 sum(tmp2.AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
2009 sum(tmp2.AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
2010 sum(tmp2.AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
2011 sum(tmp2.AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
2012 sum(tmp2.AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
2013 sum(tmp2.AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
2014 sum(tmp2.AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
2015 sum(tmp2.TOTAL_RES_COUNT) TOTAL_RES_COUNT
2016 from
2017 FII_TIME_DAY time,
2018 PJI_RM_AGGR_RES2 tmp2
2019 where
2020 tmp2.WORKER_ID = p_worker_id and
2021 tmp2.PERIOD_TYPE_ID = 1 and
2022 tmp2.CALENDAR_TYPE = 'C' and
2023 tmp2.TIME_ID = time.REPORT_DATE_JULIAN
2024 group by
2025 tmp2.PERSON_ID,
2026 tmp2.EXPENDITURE_ORGANIZATION_ID,
2027 tmp2.EXPENDITURE_ORG_ID,
2028 tmp2.JOB_ID,
2029 time.WEEK_ID;
2030
2031 l_row_count := sql%rowcount;
2032
2033 l_row_count := l_row_count + PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2034 (
2035 l_process,
2036 'TOTAL_RES_ROW_COUNT'
2037 );
2038
2039 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
2040 (
2041 l_process,
2042 'TOTAL_RES_ROW_COUNT',
2043 l_row_count
2044 );
2045
2046 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2047 (
2048 l_process,
2049 'PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_WK(p_worker_id);'
2050 );
2051
2052 commit;
2053
2054 end EXPAND_RMS_CAL_WK;
2055
2056
2057 -- -----------------------------------------------------
2058 -- procedure MERGE_TMP1_INTO_RMR
2059 -- -----------------------------------------------------
2060 procedure MERGE_TMP1_INTO_RMR (p_worker_id in number) is
2061
2062 l_process varchar2(30);
2063 l_extraction_type varchar2(30);
2064 l_last_update_date date;
2065 l_last_updated_by number;
2066 l_creation_date date;
2067 l_created_by number;
2068 l_last_update_login number;
2069
2070 begin
2071
2072 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2073
2074 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2075 (
2076 l_process,
2077 'PJI_RM_SUM_ROLLUP_RES.MERGE_TMP1_INTO_RMR(p_worker_id);'
2078 )) then
2079 return;
2080 end if;
2081
2082 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2083 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2084
2085 l_last_update_date := sysdate;
2086 l_last_updated_by := FND_GLOBAL.USER_ID;
2087 l_creation_date := sysdate;
2088 l_created_by := FND_GLOBAL.USER_ID;
2089 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2090
2091 if (l_extraction_type = 'FULL') then
2092
2093 insert /*+ append parallel(rmr_i) */ into PJI_RM_RES_WT_F rmr_i
2094 (
2095 RECORD_TYPE,
2096 PROJECT_ID,
2097 PERSON_ID,
2098 EXPENDITURE_ORG_ID,
2099 EXPENDITURE_ORGANIZATION_ID,
2100 WORK_TYPE_ID,
2101 JOB_ID,
2102 TIME_ID,
2103 PERIOD_TYPE_ID,
2104 CALENDAR_TYPE,
2105 CAPACITY_HRS,
2106 TOTAL_HRS_A,
2107 BILL_HRS_A,
2108 CONF_HRS_S,
2109 PROV_HRS_S,
2110 UNASSIGNED_HRS_S,
2111 CONF_OVERCOM_HRS_S,
2112 PROV_OVERCOM_HRS_S,
2113 LAST_UPDATE_DATE,
2114 LAST_UPDATED_BY,
2115 CREATION_DATE,
2116 CREATED_BY,
2117 LAST_UPDATE_LOGIN
2118 )
2119 select /*+ parallel(res1) */
2120 RECORD_TYPE,
2121 PROJECT_ID,
2122 PERSON_ID,
2123 EXPENDITURE_ORG_ID,
2124 EXPENDITURE_ORGANIZATION_ID,
2125 WORK_TYPE_ID,
2126 JOB_ID,
2127 TIME_ID,
2128 PERIOD_TYPE_ID,
2129 CALENDAR_TYPE,
2130 sum(CAPACITY_HRS) CAPACITY_HRS,
2131 sum(TOTAL_HRS_A) TOTAL_HRS_A,
2132 sum(BILL_HRS_A) BILL_HRS_A,
2133 sum(CONF_HRS_S) CONF_HRS_S,
2134 sum(PROV_HRS_S) PROV_HRS_S,
2135 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
2136 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
2137 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
2138 l_last_update_date LAST_UPDATE_DATE,
2139 l_last_updated_by LAST_UPDATED_BY,
2140 l_creation_date CREATION_DATE,
2141 l_created_by CREATED_BY,
2142 l_last_update_login LAST_UPDATE_LOGIN
2143 from
2144 PJI_RM_AGGR_RES1 res1
2145 where
2146 WORKER_ID = p_worker_id and
2147 EXPENDITURE_ORGANIZATION_ID is not null and
2148 RECORD_TYPE <> 'N'
2149 group by
2150 RECORD_TYPE,
2151 PROJECT_ID,
2152 PERSON_ID,
2153 EXPENDITURE_ORG_ID,
2154 EXPENDITURE_ORGANIZATION_ID,
2155 WORK_TYPE_ID,
2156 JOB_ID,
2157 TIME_ID,
2158 PERIOD_TYPE_ID,
2159 CALENDAR_TYPE;
2160
2161 else
2162
2163 merge /*+ parallel(rmr) */ into PJI_RM_RES_WT_F rmr
2164 using
2165 (
2166 select /*+ parallel(res1) */
2167 RECORD_TYPE,
2168 PROJECT_ID,
2169 PERSON_ID,
2170 EXPENDITURE_ORG_ID,
2171 EXPENDITURE_ORGANIZATION_ID,
2172 WORK_TYPE_ID,
2173 JOB_ID,
2174 TIME_ID,
2175 PERIOD_TYPE_ID,
2176 CALENDAR_TYPE,
2177 sum(CAPACITY_HRS) CAPACITY_HRS,
2178 sum(TOTAL_HRS_A) TOTAL_HRS_A,
2179 sum(BILL_HRS_A) BILL_HRS_A,
2180 sum(CONF_HRS_S) CONF_HRS_S,
2181 sum(PROV_HRS_S) PROV_HRS_S,
2182 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
2183 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
2184 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
2185 l_last_update_date LAST_UPDATE_DATE,
2186 l_last_updated_by LAST_UPDATED_BY,
2187 l_creation_date CREATION_DATE,
2188 l_created_by CREATED_BY,
2189 l_last_update_login LAST_UPDATE_LOGIN
2190 from
2191 PJI_RM_AGGR_RES1 res1
2192 where
2193 WORKER_ID = p_worker_id and
2194 EXPENDITURE_ORGANIZATION_ID is not null and
2195 RECORD_TYPE <> 'N'
2196 group by
2197 RECORD_TYPE,
2198 PROJECT_ID,
2199 PERSON_ID,
2200 EXPENDITURE_ORG_ID,
2201 EXPENDITURE_ORGANIZATION_ID,
2202 WORK_TYPE_ID,
2203 JOB_ID,
2204 TIME_ID,
2205 PERIOD_TYPE_ID,
2206 CALENDAR_TYPE
2207 ) res1
2208 on
2209 (
2210 res1.RECORD_TYPE = rmr.RECORD_TYPE and
2211 res1.PROJECT_ID = rmr.PROJECT_ID and
2212 res1.PERSON_ID = rmr.PERSON_ID and
2213 res1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID and
2214 res1.EXPENDITURE_ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
2215 res1.JOB_ID = rmr.JOB_ID and
2216 res1.WORK_TYPE_ID = rmr.WORK_TYPE_ID and
2217 res1.TIME_ID = rmr.TIME_ID and
2218 res1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID and
2219 res1.CALENDAR_TYPE = rmr.CALENDAR_TYPE
2220 )
2221 when matched then update set
2222 rmr.CAPACITY_HRS = case when rmr.CAPACITY_HRS is null and
2223 res1.CAPACITY_HRS is null
2224 then to_number(null)
2225 else nvl(rmr.CAPACITY_HRS, 0) +
2226 nvl(res1.CAPACITY_HRS, 0)
2227 end,
2228 rmr.TOTAL_HRS_A = case when rmr.TOTAL_HRS_A is null and
2229 res1.TOTAL_HRS_A is null
2230 then to_number(null)
2231 else nvl(rmr.TOTAL_HRS_A, 0) +
2232 nvl(res1.TOTAL_HRS_A, 0)
2233 end,
2234 rmr.BILL_HRS_A = case when rmr.BILL_HRS_A is null and
2235 res1.BILL_HRS_A is null
2236 then to_number(null)
2237 else nvl(rmr.BILL_HRS_A, 0) +
2238 nvl(res1.BILL_HRS_A, 0)
2239 end,
2240 rmr.CONF_HRS_S = case when rmr.CONF_HRS_S is null and
2241 res1.CONF_HRS_S is null
2242 then to_number(null)
2243 else nvl(rmr.CONF_HRS_S, 0) +
2244 nvl(res1.CONF_HRS_S, 0)
2245 end,
2246 rmr.PROV_HRS_S = case when rmr.PROV_HRS_S is null and
2247 res1.PROV_HRS_S is null
2248 then to_number(null)
2249 else nvl(rmr.PROV_HRS_S, 0) +
2250 nvl(res1.PROV_HRS_S, 0)
2251 end,
2252 rmr.UNASSIGNED_HRS_S = case when rmr.UNASSIGNED_HRS_S is null and
2253 res1.UNASSIGNED_HRS_S is null
2254 then to_number(null)
2255 else nvl(rmr.UNASSIGNED_HRS_S, 0) +
2256 nvl(res1.UNASSIGNED_HRS_S, 0)
2257 end,
2258 rmr.CONF_OVERCOM_HRS_S = case when rmr.CONF_OVERCOM_HRS_S is null and
2259 res1.CONF_OVERCOM_HRS_S is null
2260 then to_number(null)
2261 else nvl(rmr.CONF_OVERCOM_HRS_S, 0) +
2262 nvl(res1.CONF_OVERCOM_HRS_S, 0)
2263 end,
2264 rmr.PROV_OVERCOM_HRS_S = case when rmr.PROV_OVERCOM_HRS_S is null and
2265 res1.PROV_OVERCOM_HRS_S is null
2266 then to_number(null)
2267 else nvl(rmr.PROV_OVERCOM_HRS_S, 0) +
2268 nvl(res1.PROV_OVERCOM_HRS_S, 0)
2269 end,
2270 rmr.LAST_UPDATE_DATE = res1.LAST_UPDATE_DATE,
2271 rmr.LAST_UPDATED_BY = res1.LAST_UPDATED_BY,
2272 rmr.LAST_UPDATE_LOGIN = res1.LAST_UPDATE_LOGIN
2273 when not matched then insert
2274 (
2275 rmr.RECORD_TYPE,
2276 rmr.PROJECT_ID,
2277 rmr.PERSON_ID,
2278 rmr.EXPENDITURE_ORG_ID,
2279 rmr.EXPENDITURE_ORGANIZATION_ID,
2280 rmr.WORK_TYPE_ID,
2281 rmr.JOB_ID,
2282 rmr.TIME_ID,
2283 rmr.PERIOD_TYPE_ID,
2284 rmr.CALENDAR_TYPE,
2285 rmr.CAPACITY_HRS,
2286 rmr.TOTAL_HRS_A,
2287 rmr.BILL_HRS_A,
2288 rmr.CONF_HRS_S,
2289 rmr.PROV_HRS_S,
2290 rmr.UNASSIGNED_HRS_S,
2291 rmr.CONF_OVERCOM_HRS_S,
2292 rmr.PROV_OVERCOM_HRS_S,
2293 rmr.LAST_UPDATE_DATE,
2294 rmr.LAST_UPDATED_BY,
2295 rmr.CREATION_DATE,
2296 rmr.CREATED_BY,
2297 rmr.LAST_UPDATE_LOGIN
2298 )
2299 values
2300 (
2301 res1.RECORD_TYPE,
2302 res1.PROJECT_ID,
2303 res1.PERSON_ID,
2304 res1.EXPENDITURE_ORG_ID,
2305 res1.EXPENDITURE_ORGANIZATION_ID,
2306 res1.WORK_TYPE_ID,
2307 res1.JOB_ID,
2308 res1.TIME_ID,
2309 res1.PERIOD_TYPE_ID,
2310 res1.CALENDAR_TYPE,
2311 res1.CAPACITY_HRS,
2312 res1.TOTAL_HRS_A,
2313 res1.BILL_HRS_A,
2314 res1.CONF_HRS_S,
2315 res1.PROV_HRS_S,
2316 res1.UNASSIGNED_HRS_S,
2317 res1.CONF_OVERCOM_HRS_S,
2318 res1.PROV_OVERCOM_HRS_S,
2319 res1.LAST_UPDATE_DATE,
2320 res1.LAST_UPDATED_BY,
2321 res1.CREATION_DATE,
2322 res1.CREATED_BY,
2323 res1.LAST_UPDATE_LOGIN
2324 );
2325
2326 end if;
2327
2328 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2329 (
2330 l_process,
2331 'PJI_RM_SUM_ROLLUP_RES.MERGE_TMP1_INTO_RMR(p_worker_id);'
2332 );
2333
2334 commit;
2335
2336 end MERGE_TMP1_INTO_RMR;
2337
2338
2339 -- -----------------------------------------------------
2340 -- procedure CLEANUP_RMR
2341 -- -----------------------------------------------------
2342 procedure CLEANUP_RMR (p_worker_id in number) is
2343
2344 l_process varchar2(30);
2345
2346 begin
2347
2348 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2349
2350 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2351 (
2352 l_process,
2353 'PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMR(p_worker_id);'
2354 )) then
2355 return;
2356 end if;
2357
2358 delete
2359 from PJI_RM_RES_WT_F
2360 where (RECORD_TYPE,
2361 PROJECT_ID,
2362 PERSON_ID,
2363 EXPENDITURE_ORG_ID,
2364 EXPENDITURE_ORGANIZATION_ID,
2365 WORK_TYPE_ID,
2366 JOB_ID,
2367 TIME_ID,
2368 PERIOD_TYPE_ID,
2369 CALENDAR_TYPE) in
2370 (select /*+ parallel(res1) */
2371 RECORD_TYPE,
2372 PROJECT_ID,
2373 PERSON_ID,
2374 EXPENDITURE_ORG_ID,
2375 EXPENDITURE_ORGANIZATION_ID,
2376 WORK_TYPE_ID,
2377 JOB_ID,
2378 TIME_ID,
2379 PERIOD_TYPE_ID,
2380 CALENDAR_TYPE
2381 from PJI_RM_AGGR_RES1 res1
2382 where WORKER_ID = p_worker_id and
2383 RECORD_TYPE <> 'N') and
2384 nvl(CAPACITY_HRS, 0) = 0 and
2385 nvl(TOTAL_HRS_A, 0) = 0 and
2386 nvl(BILL_HRS_A, 0) = 0 and
2387 nvl(CONF_HRS_S, 0) = 0 and
2388 nvl(PROV_HRS_S, 0) = 0 and
2389 nvl(UNASSIGNED_HRS_S, 0) = 0 and
2390 nvl(CONF_OVERCOM_HRS_S, 0) = 0 and
2391 nvl(PROV_OVERCOM_HRS_S, 0) = 0;
2392
2393 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
2394 (
2395 l_process,
2396 'PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMR(p_worker_id);'
2397 );
2398
2399 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
2400 'PJI_RM_AGGR_RES1','NORMAL',null);
2401
2402 commit;
2403
2404 end CLEANUP_RMR;
2405
2406
2407 -- -----------------------------------------------------
2408 -- procedure MERGE_TMP2_INTO_RMS
2409 -- -----------------------------------------------------
2410 procedure MERGE_TMP2_INTO_RMS (p_worker_id in number) is
2411
2412 l_process varchar2(30);
2413 l_extraction_type varchar2(30);
2414 l_last_update_date date;
2415 l_last_updated_by number;
2416 l_creation_date date;
2417 l_created_by number;
2418 l_last_update_login number;
2419
2420 begin
2421
2422 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2423
2424 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
2425 (
2426 l_process,
2427 'PJI_RM_SUM_ROLLUP_RES.MERGE_TMP2_INTO_RMS(p_worker_id);'
2428 )) then
2429 return;
2430 end if;
2431
2432 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2433 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2434
2435 l_last_update_date := sysdate;
2436 l_last_updated_by := FND_GLOBAL.USER_ID;
2437 l_creation_date := sysdate;
2438 l_created_by := FND_GLOBAL.USER_ID;
2439 l_last_update_login := FND_GLOBAL.LOGIN_ID;
2440
2441 if (l_extraction_type = 'FULL') then
2442
2443 insert /*+ append parallel(rms_i) */ into PJI_RM_RES_F rms_i
2444 (
2445 PERSON_ID,
2446 EXPENDITURE_ORG_ID,
2447 EXPENDITURE_ORGANIZATION_ID,
2448 JOB_ID,
2449 TIME_ID,
2450 PERIOD_TYPE_ID,
2451 CALENDAR_TYPE,
2452 CAPACITY_HRS,
2453 TOTAL_HRS_A,
2454 MISSING_HRS_A,
2455 TOTAL_WTD_ORG_HRS_A,
2456 TOTAL_WTD_RES_HRS_A,
2457 BILL_HRS_A,
2458 BILL_WTD_ORG_HRS_A,
2459 BILL_WTD_RES_HRS_A,
2460 TRAINING_HRS_A,
2461 UNASSIGNED_HRS_A,
2462 REDUCIBLE_CAPACITY_HRS_A,
2463 REDUCE_CAPACITY_HRS_A,
2464 CONF_HRS_S,
2465 CONF_WTD_ORG_HRS_S,
2466 CONF_WTD_RES_HRS_S,
2467 CONF_BILL_HRS_S,
2468 CONF_BILL_WTD_ORG_HRS_S,
2469 CONF_BILL_WTD_RES_HRS_S,
2470 PROV_HRS_S,
2471 PROV_WTD_ORG_HRS_S,
2472 PROV_WTD_RES_HRS_S,
2473 PROV_BILL_HRS_S,
2474 PROV_BILL_WTD_ORG_HRS_S,
2475 PROV_BILL_WTD_RES_HRS_S,
2476 TRAINING_HRS_S,
2477 UNASSIGNED_HRS_S,
2478 REDUCIBLE_CAPACITY_HRS_S,
2479 REDUCE_CAPACITY_HRS_S,
2480 CONF_OVERCOM_HRS_S,
2481 PROV_OVERCOM_HRS_S,
2482 AVAILABLE_HRS_BKT1_S,
2483 AVAILABLE_HRS_BKT2_S,
2484 AVAILABLE_HRS_BKT3_S,
2485 AVAILABLE_HRS_BKT4_S,
2486 AVAILABLE_HRS_BKT5_S,
2487 AVAILABLE_RES_COUNT_BKT1_S,
2488 AVAILABLE_RES_COUNT_BKT2_S,
2489 AVAILABLE_RES_COUNT_BKT3_S,
2490 AVAILABLE_RES_COUNT_BKT4_S,
2491 AVAILABLE_RES_COUNT_BKT5_S,
2492 TOTAL_RES_COUNT,
2493 LAST_UPDATE_DATE,
2494 LAST_UPDATED_BY,
2495 CREATION_DATE,
2496 CREATED_BY,
2497 LAST_UPDATE_LOGIN
2498 )
2499 select /*+ parallel(res2) */
2500 PERSON_ID,
2501 EXPENDITURE_ORG_ID,
2502 EXPENDITURE_ORGANIZATION_ID,
2503 JOB_ID,
2504 TIME_ID,
2505 PERIOD_TYPE_ID,
2506 CALENDAR_TYPE,
2507 sum(CAPACITY_HRS) CAPACITY_HRS,
2508 sum(TOTAL_HRS_A) TOTAL_HRS_A,
2509 sum(MISSING_HRS_A) MISSING_HRS_A,
2510 sum(TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
2511 sum(TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
2512 sum(BILL_HRS_A) BILL_HRS_A,
2513 sum(BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
2514 sum(BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
2515 sum(TRAINING_HRS_A) TRAINING_HRS_A,
2516 sum(UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
2517 sum(REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
2518 sum(REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
2519 sum(CONF_HRS_S) CONF_HRS_S,
2520 sum(CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
2521 sum(CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
2522 sum(CONF_BILL_HRS_S) CONF_BILL_HRS_S,
2523 sum(CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
2524 sum(CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
2525 sum(PROV_HRS_S) PROV_HRS_S,
2526 sum(PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
2527 sum(PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
2528 sum(PROV_BILL_HRS_S) PROV_BILL_HRS_S,
2529 sum(PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
2530 sum(PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
2531 sum(TRAINING_HRS_S) TRAINING_HRS_S,
2532 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
2533 sum(REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
2534 sum(REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
2535 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
2536 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
2537 sum(AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
2538 sum(AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
2539 sum(AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
2540 sum(AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
2541 sum(AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
2542 sum(AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
2543 sum(AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
2544 sum(AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
2545 sum(AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
2546 sum(AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
2547 sum(TOTAL_RES_COUNT) TOTAL_RES_COUNT,
2548 l_last_update_date LAST_UPDATE_DATE,
2549 l_last_updated_by LAST_UPDATED_BY,
2550 l_creation_date CREATION_DATE,
2551 l_created_by CREATED_BY,
2552 l_last_update_login LAST_UPDATE_LOGIN
2553 from
2554 PJI_RM_AGGR_RES2 res2
2555 where
2556 WORKER_ID = p_worker_id and
2557 EXPENDITURE_ORGANIZATION_ID is not null
2558 group by
2559 PERSON_ID,
2560 EXPENDITURE_ORG_ID,
2561 EXPENDITURE_ORGANIZATION_ID,
2562 JOB_ID,
2563 TIME_ID,
2564 PERIOD_TYPE_ID,
2565 CALENDAR_TYPE;
2566
2567 else
2568
2569 merge /*+ parallel(rms) */ into PJI_RM_RES_F rms
2570 using
2571 (
2572 select /*+ parallel(res2) */
2573 PERSON_ID,
2574 EXPENDITURE_ORG_ID,
2575 EXPENDITURE_ORGANIZATION_ID,
2576 JOB_ID,
2577 TIME_ID,
2578 PERIOD_TYPE_ID,
2579 CALENDAR_TYPE,
2580 sum(CAPACITY_HRS) CAPACITY_HRS,
2581 sum(TOTAL_HRS_A) TOTAL_HRS_A,
2582 sum(MISSING_HRS_A) MISSING_HRS_A,
2583 sum(TOTAL_WTD_ORG_HRS_A) TOTAL_WTD_ORG_HRS_A,
2584 sum(TOTAL_WTD_RES_HRS_A) TOTAL_WTD_RES_HRS_A,
2585 sum(BILL_HRS_A) BILL_HRS_A,
2586 sum(BILL_WTD_ORG_HRS_A) BILL_WTD_ORG_HRS_A,
2587 sum(BILL_WTD_RES_HRS_A) BILL_WTD_RES_HRS_A,
2588 sum(TRAINING_HRS_A) TRAINING_HRS_A,
2589 sum(UNASSIGNED_HRS_A) UNASSIGNED_HRS_A,
2590 sum(REDUCIBLE_CAPACITY_HRS_A) REDUCIBLE_CAPACITY_HRS_A,
2591 sum(REDUCE_CAPACITY_HRS_A) REDUCE_CAPACITY_HRS_A,
2592 sum(CONF_HRS_S) CONF_HRS_S,
2593 sum(CONF_WTD_ORG_HRS_S) CONF_WTD_ORG_HRS_S,
2594 sum(CONF_WTD_RES_HRS_S) CONF_WTD_RES_HRS_S,
2595 sum(CONF_BILL_HRS_S) CONF_BILL_HRS_S,
2596 sum(CONF_BILL_WTD_ORG_HRS_S) CONF_BILL_WTD_ORG_HRS_S,
2597 sum(CONF_BILL_WTD_RES_HRS_S) CONF_BILL_WTD_RES_HRS_S,
2598 sum(PROV_HRS_S) PROV_HRS_S,
2599 sum(PROV_WTD_ORG_HRS_S) PROV_WTD_ORG_HRS_S,
2600 sum(PROV_WTD_RES_HRS_S) PROV_WTD_RES_HRS_S,
2601 sum(PROV_BILL_HRS_S) PROV_BILL_HRS_S,
2602 sum(PROV_BILL_WTD_ORG_HRS_S) PROV_BILL_WTD_ORG_HRS_S,
2603 sum(PROV_BILL_WTD_RES_HRS_S) PROV_BILL_WTD_RES_HRS_S,
2604 sum(TRAINING_HRS_S) TRAINING_HRS_S,
2605 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
2606 sum(REDUCIBLE_CAPACITY_HRS_S) REDUCIBLE_CAPACITY_HRS_S,
2607 sum(REDUCE_CAPACITY_HRS_S) REDUCE_CAPACITY_HRS_S,
2608 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
2609 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
2610 sum(AVAILABLE_HRS_BKT1_S) AVAILABLE_HRS_BKT1_S,
2611 sum(AVAILABLE_HRS_BKT2_S) AVAILABLE_HRS_BKT2_S,
2612 sum(AVAILABLE_HRS_BKT3_S) AVAILABLE_HRS_BKT3_S,
2613 sum(AVAILABLE_HRS_BKT4_S) AVAILABLE_HRS_BKT4_S,
2614 sum(AVAILABLE_HRS_BKT5_S) AVAILABLE_HRS_BKT5_S,
2615 sum(AVAILABLE_RES_COUNT_BKT1_S) AVAILABLE_RES_COUNT_BKT1_S,
2616 sum(AVAILABLE_RES_COUNT_BKT2_S) AVAILABLE_RES_COUNT_BKT2_S,
2617 sum(AVAILABLE_RES_COUNT_BKT3_S) AVAILABLE_RES_COUNT_BKT3_S,
2618 sum(AVAILABLE_RES_COUNT_BKT4_S) AVAILABLE_RES_COUNT_BKT4_S,
2619 sum(AVAILABLE_RES_COUNT_BKT5_S) AVAILABLE_RES_COUNT_BKT5_S,
2620 sum(TOTAL_RES_COUNT) TOTAL_RES_COUNT,
2621 l_last_update_date LAST_UPDATE_DATE,
2622 l_last_updated_by LAST_UPDATED_BY,
2623 l_creation_date CREATION_DATE,
2624 l_created_by CREATED_BY,
2625 l_last_update_login LAST_UPDATE_LOGIN
2626 from
2627 PJI_RM_AGGR_RES2 res2
2628 where
2629 WORKER_ID = p_worker_id and
2630 EXPENDITURE_ORGANIZATION_ID is not null
2631 group by
2632 PERSON_ID,
2633 EXPENDITURE_ORG_ID,
2634 EXPENDITURE_ORGANIZATION_ID,
2635 JOB_ID,
2636 TIME_ID,
2637 PERIOD_TYPE_ID,
2638 CALENDAR_TYPE
2639 ) res2
2640 on
2641 (
2642 res2.PERSON_ID = rms.PERSON_ID and
2643 res2.EXPENDITURE_ORG_ID = rms.EXPENDITURE_ORG_ID and
2644 res2.EXPENDITURE_ORGANIZATION_ID = rms.EXPENDITURE_ORGANIZATION_ID and
2645 res2.JOB_ID = rms.JOB_ID and
2646 res2.TIME_ID = rms.TIME_ID and
2647 res2.PERIOD_TYPE_ID = rms.PERIOD_TYPE_ID and
2648 res2.CALENDAR_TYPE = rms.CALENDAR_TYPE
2649 )
2650 when matched then update set
2651 rms.CAPACITY_HRS = case when rms.CAPACITY_HRS is null and
2652 res2.CAPACITY_HRS is null
2653 then to_number(null)
2654 else nvl(rms.CAPACITY_HRS, 0) +
2655 nvl(res2.CAPACITY_HRS, 0)
2656 end,
2657 rms.TOTAL_HRS_A = case when rms.TOTAL_HRS_A is null and
2658 res2.TOTAL_HRS_A is null
2659 then to_number(null)
2660 else nvl(rms.TOTAL_HRS_A, 0) +
2661 nvl(res2.TOTAL_HRS_A, 0)
2662 end,
2663 rms.MISSING_HRS_A= case when rms.MISSING_HRS_A is null and
2664 res2.MISSING_HRS_A is null
2665 then to_number(null)
2666 else nvl(rms.MISSING_HRS_A, 0) +
2667 nvl(res2.MISSING_HRS_A, 0)
2668 end,
2669 rms.TOTAL_WTD_ORG_HRS_A
2670 = case when rms.TOTAL_WTD_ORG_HRS_A is null and
2671 res2.TOTAL_WTD_ORG_HRS_A is null
2672 then to_number(null)
2673 else nvl(rms.TOTAL_WTD_ORG_HRS_A, 0) +
2674 nvl(res2.TOTAL_WTD_ORG_HRS_A, 0)
2675 end,
2676 rms.TOTAL_WTD_RES_HRS_A
2677 = case when rms.TOTAL_WTD_RES_HRS_A is null and
2678 res2.TOTAL_WTD_RES_HRS_A is null
2679 then to_number(null)
2680 else nvl(rms.TOTAL_WTD_RES_HRS_A, 0) +
2681 nvl(res2.TOTAL_WTD_RES_HRS_A, 0)
2682 end,
2683 rms.BILL_HRS_A = case when rms.BILL_HRS_A is null and
2684 res2.BILL_HRS_A is null
2685 then to_number(null)
2686 else nvl(rms.BILL_HRS_A, 0) +
2687 nvl(res2.BILL_HRS_A, 0)
2688 end,
2689 rms.BILL_WTD_ORG_HRS_A
2690 = case when rms.BILL_WTD_ORG_HRS_A is null and
2691 res2.BILL_WTD_ORG_HRS_A is null
2692 then to_number(null)
2693 else nvl(rms.BILL_WTD_ORG_HRS_A, 0) +
2694 nvl(res2.BILL_WTD_ORG_HRS_A, 0)
2695 end,
2696 rms.BILL_WTD_RES_HRS_A
2697 = case when rms.BILL_WTD_RES_HRS_A is null and
2698 res2.BILL_WTD_RES_HRS_A is null
2699 then to_number(null)
2700 else nvl(rms.BILL_WTD_RES_HRS_A, 0) +
2701 nvl(res2.BILL_WTD_RES_HRS_A, 0)
2702 end,
2703 rms.TRAINING_HRS_A
2704 = case when rms.TRAINING_HRS_A is null and
2705 res2.TRAINING_HRS_A is null
2706 then to_number(null)
2707 else nvl(rms.TRAINING_HRS_A, 0) +
2708 nvl(res2.TRAINING_HRS_A, 0)
2709 end,
2710 rms.UNASSIGNED_HRS_A
2711 = case when rms.UNASSIGNED_HRS_A is null and
2712 res2.UNASSIGNED_HRS_A is null
2713 then to_number(null)
2714 else nvl(rms.UNASSIGNED_HRS_A, 0) +
2715 nvl(res2.UNASSIGNED_HRS_A, 0)
2716 end,
2717 rms.REDUCIBLE_CAPACITY_HRS_A
2718 = case when rms.REDUCIBLE_CAPACITY_HRS_A is null and
2719 res2.REDUCIBLE_CAPACITY_HRS_A is null
2720 then to_number(null)
2721 else nvl(rms.REDUCIBLE_CAPACITY_HRS_A, 0) +
2722 nvl(res2.REDUCIBLE_CAPACITY_HRS_A, 0)
2723 end,
2724 rms.REDUCE_CAPACITY_HRS_A
2725 = case when rms.REDUCE_CAPACITY_HRS_A is null and
2726 res2.REDUCE_CAPACITY_HRS_A is null
2727 then to_number(null)
2728 else nvl(rms.REDUCE_CAPACITY_HRS_A, 0) +
2729 nvl(res2.REDUCE_CAPACITY_HRS_A, 0)
2730 end,
2731 rms.CONF_HRS_S = case when rms.CONF_HRS_S is null and
2732 res2.CONF_HRS_S is null
2733 then to_number(null)
2734 else nvl(rms.CONF_HRS_S, 0) +
2735 nvl(res2.CONF_HRS_S, 0)
2736 end,
2737 rms.CONF_WTD_ORG_HRS_S
2738 = case when rms.CONF_WTD_ORG_HRS_S is null and
2739 res2.CONF_WTD_ORG_HRS_S is null
2740 then to_number(null)
2741 else nvl(rms.CONF_WTD_ORG_HRS_S, 0) +
2742 nvl(res2.CONF_WTD_ORG_HRS_S, 0)
2743 end,
2744 rms.CONF_WTD_RES_HRS_S
2745 = case when rms.CONF_WTD_RES_HRS_S is null and
2746 res2.CONF_WTD_RES_HRS_S is null
2747 then to_number(null)
2748 else nvl(rms.CONF_WTD_RES_HRS_S, 0) +
2749 nvl(res2.CONF_WTD_RES_HRS_S, 0)
2750 end,
2751 rms.CONF_BILL_HRS_S
2752 = case when rms.CONF_BILL_HRS_S is null and
2753 res2.CONF_BILL_HRS_S is null
2754 then to_number(null)
2755 else nvl(rms.CONF_BILL_HRS_S, 0) +
2756 nvl(res2.CONF_BILL_HRS_S, 0)
2757 end,
2758 rms.CONF_BILL_WTD_ORG_HRS_S
2759 = case when rms.CONF_BILL_WTD_ORG_HRS_S is null and
2760 res2.CONF_BILL_WTD_ORG_HRS_S is null
2761 then to_number(null)
2762 else nvl(rms.CONF_BILL_WTD_ORG_HRS_S, 0) +
2763 nvl(res2.CONF_BILL_WTD_ORG_HRS_S, 0)
2764 end,
2765 rms.CONF_BILL_WTD_RES_HRS_S
2766 = case when rms.CONF_BILL_WTD_RES_HRS_S is null and
2767 res2.CONF_BILL_WTD_RES_HRS_S is null
2768 then to_number(null)
2769 else nvl(rms.CONF_BILL_WTD_RES_HRS_S, 0) +
2770 nvl(res2.CONF_BILL_WTD_RES_HRS_S, 0)
2771 end,
2772 rms.PROV_HRS_S = case when rms.PROV_HRS_S is null and
2773 res2.PROV_HRS_S is null
2774 then to_number(null)
2775 else nvl(rms.PROV_HRS_S, 0) +
2776 nvl(res2.PROV_HRS_S, 0)
2777 end,
2778 rms.PROV_WTD_ORG_HRS_S
2779 = case when rms.PROV_WTD_ORG_HRS_S is null and
2780 res2.PROV_WTD_ORG_HRS_S is null
2781 then to_number(null)
2782 else nvl(rms.PROV_WTD_ORG_HRS_S, 0) +
2783 nvl(res2.PROV_WTD_ORG_HRS_S, 0)
2784 end,
2785 rms.PROV_WTD_RES_HRS_S
2786 = case when rms.PROV_WTD_RES_HRS_S is null and
2787 res2.PROV_WTD_RES_HRS_S is null
2788 then to_number(null)
2789 else nvl(rms.PROV_WTD_RES_HRS_S, 0) +
2790 nvl(res2.PROV_WTD_RES_HRS_S, 0)
2791 end,
2792 rms.PROV_BILL_HRS_S
2793 = case when rms.PROV_BILL_HRS_S is null and
2794 res2.PROV_BILL_HRS_S is null
2795 then to_number(null)
2796 else nvl(rms.PROV_BILL_HRS_S, 0) +
2797 nvl(res2.PROV_BILL_HRS_S, 0)
2798 end,
2799 rms.PROV_BILL_WTD_ORG_HRS_S
2800 = case when rms.PROV_BILL_WTD_ORG_HRS_S is null and
2801 res2.PROV_BILL_WTD_ORG_HRS_S is null
2802 then to_number(null)
2803 else nvl(rms.PROV_BILL_WTD_ORG_HRS_S, 0) +
2804 nvl(res2.PROV_BILL_WTD_ORG_HRS_S, 0)
2805 end,
2806 rms.PROV_BILL_WTD_RES_HRS_S
2807 = case when rms.PROV_BILL_WTD_RES_HRS_S is null and
2808 res2.PROV_BILL_WTD_RES_HRS_S is null
2809 then to_number(null)
2810 else nvl(rms.PROV_BILL_WTD_RES_HRS_S, 0) +
2811 nvl(res2.PROV_BILL_WTD_RES_HRS_S, 0)
2812 end,
2813 rms.TRAINING_HRS_S
2814 = case when rms.TRAINING_HRS_S is null and
2815 res2.TRAINING_HRS_S is null
2816 then to_number(null)
2817 else nvl(rms.TRAINING_HRS_S, 0) +
2818 nvl(res2.TRAINING_HRS_S, 0)
2819 end,
2820 rms.UNASSIGNED_HRS_S
2821 = case when rms.UNASSIGNED_HRS_S is null and
2822 res2.UNASSIGNED_HRS_S is null
2823 then to_number(null)
2824 else nvl(rms.UNASSIGNED_HRS_S, 0) +
2825 nvl(res2.UNASSIGNED_HRS_S, 0)
2826 end,
2827 rms.REDUCIBLE_CAPACITY_HRS_S
2828 = case when rms.REDUCIBLE_CAPACITY_HRS_S is null and
2829 res2.REDUCIBLE_CAPACITY_HRS_S is null
2830 then to_number(null)
2831 else nvl(rms.REDUCIBLE_CAPACITY_HRS_S, 0) +
2832 nvl(res2.REDUCIBLE_CAPACITY_HRS_S, 0)
2833 end,
2834 rms.REDUCE_CAPACITY_HRS_S
2835 = case when rms.REDUCE_CAPACITY_HRS_S is null and
2836 res2.REDUCE_CAPACITY_HRS_S is null
2837 then to_number(null)
2838 else nvl(rms.REDUCE_CAPACITY_HRS_S, 0) +
2839 nvl(res2.REDUCE_CAPACITY_HRS_S, 0)
2840 end,
2841 rms.CONF_OVERCOM_HRS_S
2842 = case when rms.CONF_OVERCOM_HRS_S is null and
2843 res2.CONF_OVERCOM_HRS_S is null
2844 then to_number(null)
2845 else nvl(rms.CONF_OVERCOM_HRS_S, 0) +
2846 nvl(res2.CONF_OVERCOM_HRS_S, 0)
2847 end,
2848 rms.PROV_OVERCOM_HRS_S
2849 = case when rms.PROV_OVERCOM_HRS_S is null and
2850 res2.PROV_OVERCOM_HRS_S is null
2851 then to_number(null)
2852 else nvl(rms.PROV_OVERCOM_HRS_S, 0) +
2853 nvl(res2.PROV_OVERCOM_HRS_S, 0)
2854 end,
2855 rms.AVAILABLE_HRS_BKT1_S
2856 = case when rms.AVAILABLE_HRS_BKT1_S is null and
2857 res2.AVAILABLE_HRS_BKT1_S is null
2858 then to_number(null)
2859 else nvl(rms.AVAILABLE_HRS_BKT1_S, 0) +
2860 nvl(res2.AVAILABLE_HRS_BKT1_S, 0)
2861 end,
2862 rms.AVAILABLE_HRS_BKT2_S
2863 = case when rms.AVAILABLE_HRS_BKT2_S is null and
2864 res2.AVAILABLE_HRS_BKT2_S is null
2865 then to_number(null)
2866 else nvl(rms.AVAILABLE_HRS_BKT2_S, 0) +
2867 nvl(res2.AVAILABLE_HRS_BKT2_S, 0)
2868 end,
2869 rms.AVAILABLE_HRS_BKT3_S
2870 = case when rms.AVAILABLE_HRS_BKT3_S is null and
2871 res2.AVAILABLE_HRS_BKT3_S is null
2872 then to_number(null)
2873 else nvl(rms.AVAILABLE_HRS_BKT3_S, 0) +
2874 nvl(res2.AVAILABLE_HRS_BKT3_S, 0)
2875 end,
2876 rms.AVAILABLE_HRS_BKT4_S
2877 = case when rms.AVAILABLE_HRS_BKT4_S is null and
2878 res2.AVAILABLE_HRS_BKT4_S is null
2879 then to_number(null)
2880 else nvl(rms.AVAILABLE_HRS_BKT4_S, 0) +
2881 nvl(res2.AVAILABLE_HRS_BKT4_S, 0)
2882 end,
2883 rms.AVAILABLE_HRS_BKT5_S
2884 = case when rms.AVAILABLE_HRS_BKT5_S is null and
2885 res2.AVAILABLE_HRS_BKT5_S is null
2886 then to_number(null)
2887 else nvl(rms.AVAILABLE_HRS_BKT5_S, 0) +
2888 nvl(res2.AVAILABLE_HRS_BKT5_S, 0)
2889 end,
2890 rms.AVAILABLE_RES_COUNT_BKT1_S
2891 = case when rms.AVAILABLE_RES_COUNT_BKT1_S is null and
2892 res2.AVAILABLE_RES_COUNT_BKT1_S is null
2893 then to_number(null)
2894 else nvl(rms.AVAILABLE_RES_COUNT_BKT1_S, 0) +
2895 nvl(res2.AVAILABLE_RES_COUNT_BKT1_S, 0)
2896 end,
2897 rms.AVAILABLE_RES_COUNT_BKT2_S
2898 = case when rms.AVAILABLE_RES_COUNT_BKT2_S is null and
2899 res2.AVAILABLE_RES_COUNT_BKT2_S is null
2900 then to_number(null)
2901 else nvl(rms.AVAILABLE_RES_COUNT_BKT2_S, 0) +
2902 nvl(res2.AVAILABLE_RES_COUNT_BKT2_S, 0)
2903 end,
2904 rms.AVAILABLE_RES_COUNT_BKT3_S
2905 = case when rms.AVAILABLE_RES_COUNT_BKT3_S is null and
2906 res2.AVAILABLE_RES_COUNT_BKT3_S is null
2907 then to_number(null)
2908 else nvl(rms.AVAILABLE_RES_COUNT_BKT3_S, 0) +
2909 nvl(res2.AVAILABLE_RES_COUNT_BKT3_S, 0)
2910 end,
2911 rms.AVAILABLE_RES_COUNT_BKT4_S
2912 = case when rms.AVAILABLE_RES_COUNT_BKT4_S is null and
2913 res2.AVAILABLE_RES_COUNT_BKT4_S is null
2914 then to_number(null)
2915 else nvl(rms.AVAILABLE_RES_COUNT_BKT4_S, 0) +
2916 nvl(res2.AVAILABLE_RES_COUNT_BKT4_S, 0)
2917 end,
2918 rms.AVAILABLE_RES_COUNT_BKT5_S
2919 = case when rms.AVAILABLE_RES_COUNT_BKT5_S is null and
2920 res2.AVAILABLE_RES_COUNT_BKT5_S is null
2921 then to_number(null)
2922 else nvl(rms.AVAILABLE_RES_COUNT_BKT5_S, 0) +
2923 nvl(res2.AVAILABLE_RES_COUNT_BKT5_S, 0)
2924 end,
2925 rms.TOTAL_RES_COUNT
2926 = case when rms.TOTAL_RES_COUNT is null and
2927 res2.TOTAL_RES_COUNT is null
2928 then to_number(null)
2929 else nvl(rms.TOTAL_RES_COUNT, 0) +
2930 nvl(res2.TOTAL_RES_COUNT, 0)
2931 end,
2932 rms.LAST_UPDATE_DATE
2933 = res2.LAST_UPDATE_DATE,
2934 rms.LAST_UPDATED_BY
2935 = res2.LAST_UPDATED_BY,
2936 rms.LAST_UPDATE_LOGIN
2937 = res2.LAST_UPDATE_LOGIN
2938 when not matched then insert
2939 (
2940 rms.PERSON_ID,
2941 rms.EXPENDITURE_ORG_ID,
2942 rms.EXPENDITURE_ORGANIZATION_ID,
2943 rms.JOB_ID,
2944 rms.TIME_ID,
2945 rms.PERIOD_TYPE_ID,
2946 rms.CALENDAR_TYPE,
2947 rms.CAPACITY_HRS,
2948 rms.TOTAL_HRS_A,
2949 rms.MISSING_HRS_A,
2950 rms.TOTAL_WTD_ORG_HRS_A,
2951 rms.TOTAL_WTD_RES_HRS_A,
2952 rms.BILL_HRS_A,
2953 rms.BILL_WTD_ORG_HRS_A,
2954 rms.BILL_WTD_RES_HRS_A,
2955 rms.TRAINING_HRS_A,
2956 rms.UNASSIGNED_HRS_A,
2957 rms.REDUCIBLE_CAPACITY_HRS_A,
2958 rms.REDUCE_CAPACITY_HRS_A,
2959 rms.CONF_HRS_S,
2960 rms.CONF_WTD_ORG_HRS_S,
2961 rms.CONF_WTD_RES_HRS_S,
2962 rms.CONF_BILL_HRS_S,
2963 rms.CONF_BILL_WTD_ORG_HRS_S,
2964 rms.CONF_BILL_WTD_RES_HRS_S,
2965 rms.PROV_HRS_S,
2966 rms.PROV_WTD_ORG_HRS_S,
2967 rms.PROV_WTD_RES_HRS_S,
2968 rms.PROV_BILL_HRS_S,
2969 rms.PROV_BILL_WTD_ORG_HRS_S,
2970 rms.PROV_BILL_WTD_RES_HRS_S,
2971 rms.TRAINING_HRS_S,
2972 rms.UNASSIGNED_HRS_S,
2973 rms.REDUCIBLE_CAPACITY_HRS_S,
2974 rms.REDUCE_CAPACITY_HRS_S,
2975 rms.CONF_OVERCOM_HRS_S,
2976 rms.PROV_OVERCOM_HRS_S,
2977 rms.AVAILABLE_HRS_BKT1_S,
2978 rms.AVAILABLE_HRS_BKT2_S,
2979 rms.AVAILABLE_HRS_BKT3_S,
2980 rms.AVAILABLE_HRS_BKT4_S,
2981 rms.AVAILABLE_HRS_BKT5_S,
2982 rms.AVAILABLE_RES_COUNT_BKT1_S,
2983 rms.AVAILABLE_RES_COUNT_BKT2_S,
2984 rms.AVAILABLE_RES_COUNT_BKT3_S,
2985 rms.AVAILABLE_RES_COUNT_BKT4_S,
2986 rms.AVAILABLE_RES_COUNT_BKT5_S,
2987 rms.TOTAL_RES_COUNT,
2988 rms.LAST_UPDATE_DATE,
2989 rms.LAST_UPDATED_BY,
2990 rms.CREATION_DATE,
2991 rms.CREATED_BY,
2992 rms.LAST_UPDATE_LOGIN
2993 )
2994 values
2995 (
2996 res2.PERSON_ID,
2997 res2.EXPENDITURE_ORG_ID,
2998 res2.EXPENDITURE_ORGANIZATION_ID,
2999 res2.JOB_ID,
3000 res2.TIME_ID,
3001 res2.PERIOD_TYPE_ID,
3002 res2.CALENDAR_TYPE,
3003 res2.CAPACITY_HRS,
3004 res2.TOTAL_HRS_A,
3005 res2.MISSING_HRS_A,
3006 res2.TOTAL_WTD_ORG_HRS_A,
3007 res2.TOTAL_WTD_RES_HRS_A,
3008 res2.BILL_HRS_A,
3009 res2.BILL_WTD_ORG_HRS_A,
3010 res2.BILL_WTD_RES_HRS_A,
3011 res2.TRAINING_HRS_A,
3012 res2.UNASSIGNED_HRS_A,
3013 res2.REDUCIBLE_CAPACITY_HRS_A,
3014 res2.REDUCE_CAPACITY_HRS_A,
3015 res2.CONF_HRS_S,
3016 res2.CONF_WTD_ORG_HRS_S,
3017 res2.CONF_WTD_RES_HRS_S,
3018 res2.CONF_BILL_HRS_S,
3019 res2.CONF_BILL_WTD_ORG_HRS_S,
3020 res2.CONF_BILL_WTD_RES_HRS_S,
3021 res2.PROV_HRS_S,
3022 res2.PROV_WTD_ORG_HRS_S,
3023 res2.PROV_WTD_RES_HRS_S,
3024 res2.PROV_BILL_HRS_S,
3025 res2.PROV_BILL_WTD_ORG_HRS_S,
3026 res2.PROV_BILL_WTD_RES_HRS_S,
3027 res2.TRAINING_HRS_S,
3028 res2.UNASSIGNED_HRS_S,
3029 res2.REDUCIBLE_CAPACITY_HRS_S,
3030 res2.REDUCE_CAPACITY_HRS_S,
3031 res2.CONF_OVERCOM_HRS_S,
3032 res2.PROV_OVERCOM_HRS_S,
3033 res2.AVAILABLE_HRS_BKT1_S,
3034 res2.AVAILABLE_HRS_BKT2_S,
3035 res2.AVAILABLE_HRS_BKT3_S,
3036 res2.AVAILABLE_HRS_BKT4_S,
3037 res2.AVAILABLE_HRS_BKT5_S,
3038 res2.AVAILABLE_RES_COUNT_BKT1_S,
3039 res2.AVAILABLE_RES_COUNT_BKT2_S,
3040 res2.AVAILABLE_RES_COUNT_BKT3_S,
3041 res2.AVAILABLE_RES_COUNT_BKT4_S,
3042 res2.AVAILABLE_RES_COUNT_BKT5_S,
3043 res2.TOTAL_RES_COUNT,
3044 res2.LAST_UPDATE_DATE,
3045 res2.LAST_UPDATED_BY,
3046 res2.CREATION_DATE,
3047 res2.CREATED_BY,
3048 res2.LAST_UPDATE_LOGIN
3049 );
3050
3051 end if;
3052
3053 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3054 (
3055 l_process,
3056 'PJI_RM_SUM_ROLLUP_RES.MERGE_TMP2_INTO_RMS(p_worker_id);'
3057 );
3058
3059 commit;
3060
3061 end MERGE_TMP2_INTO_RMS;
3062
3063
3064 -- -----------------------------------------------------
3065 -- procedure CLEANUP_RMS
3066 -- -----------------------------------------------------
3067 procedure CLEANUP_RMS (p_worker_id in number) is
3068
3069 l_process varchar2(30);
3070
3071 begin
3072
3073 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3074
3075 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3076 (
3077 l_process,
3078 'PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMS(p_worker_id);'
3079 )) then
3080 return;
3081 end if;
3082
3083 delete
3084 from PJI_RM_RES_F
3085 where (PERSON_ID,
3086 EXPENDITURE_ORG_ID,
3087 EXPENDITURE_ORGANIZATION_ID,
3088 JOB_ID,
3089 TIME_ID,
3090 PERIOD_TYPE_ID,
3091 CALENDAR_TYPE) in
3092 (select /*+ parallel(res2) */
3093 PERSON_ID,
3094 EXPENDITURE_ORG_ID,
3095 EXPENDITURE_ORGANIZATION_ID,
3096 JOB_ID,
3097 TIME_ID,
3098 PERIOD_TYPE_ID,
3099 CALENDAR_TYPE
3100 from PJI_RM_AGGR_RES2 res2
3101 where WORKER_ID = p_worker_id) and
3102 nvl(CAPACITY_HRS, 0) = 0 and
3103 nvl(TOTAL_HRS_A, 0) = 0 and
3104 nvl(BILL_HRS_A, 0) = 0 and
3105 nvl(CONF_HRS_S, 0) = 0 and
3106 nvl(PROV_HRS_S, 0) = 0 and
3107 nvl(UNASSIGNED_HRS_S, 0) = 0 and
3108 nvl(CONF_OVERCOM_HRS_S, 0) = 0 and
3109 nvl(PROV_OVERCOM_HRS_S, 0) = 0;
3110
3111 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3112 (
3113 l_process,
3114 'PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMS(p_worker_id);'
3115 );
3116
3117 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME,
3118 'PJI_RM_AGGR_RES2','NORMAL',null);
3119
3120 commit;
3121
3122 end CLEANUP_RMS;
3123
3124
3125 -- -----------------------------------------------------
3126 -- procedure REFRESH_MVIEW_UTW
3127 -- -----------------------------------------------------
3128 procedure REFRESH_MVIEW_UTW (p_worker_id in number) is
3129
3130 l_process varchar2(30);
3131 l_extraction_type varchar2(30);
3132 l_pji_schema varchar2(30);
3133 l_apps_schema varchar2(30);
3134 l_p_degree number := 0;
3135
3136 l_errbuf varchar2(255);
3137 l_retcode varchar2(255);
3138
3139 begin
3140
3141 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3142
3143 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3144 (
3145 l_process,
3146 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTW(p_worker_id);'
3147 )) then
3148 return;
3149 end if;
3150
3151 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3152 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3153
3154 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3155 l_extraction_type <> 'PARTIAL') then
3156 return;
3157 end if;
3158
3159 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3160 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3161 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3162 if (l_p_degree = 1) then
3163 l_p_degree := 0;
3164 end if;
3165
3166 /* Stats gathered for this table in availability mview refresh.
3167 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
3168 TABNAME => 'PJI_ORG_DENORM',
3169 PERCENT => 10,
3170 DEGREE => l_p_degree);
3171 */
3172
3173 if (l_extraction_type = 'FULL') then
3174 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3175 l_retcode,
3176 'PJI_RM_WT_F_MV',
3177 'C',
3178 'N');
3179 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3180 l_retcode,
3181 'PJI_RM_WTO_F_MV',
3182 'C',
3183 'N');
3184 else
3185 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
3186 TABNAME => 'MLOG$_PJI_RM_RES_WT_F',
3187 PERCENT => 10,
3188 DEGREE => l_p_degree);
3189 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3190 l_retcode,
3191 'PJI_RM_WT_F_MV',
3192 'F',
3193 'N');
3194 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
3195 TABNAME => 'MLOG$_PJI_RM_WT_F_MV',
3196 PERCENT => 10,
3197 DEGREE => l_p_degree);
3198 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3199 l_retcode,
3200 'PJI_RM_WTO_F_MV',
3201 'F',
3202 'N');
3203 end if;
3204
3205 if (l_extraction_type <> 'INCREMENTAL') then
3206 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3207 tabname => 'PJI_RM_WT_F_MV',
3208 percent => 10,
3209 degree => l_p_degree);
3210 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3211 tabname => 'PJI_RM_WTO_F_MV',
3212 percent => 10,
3213 degree => l_p_degree);
3214 end if;
3215
3216 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3217 (
3218 l_process,
3219 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTW(p_worker_id);'
3220 );
3221
3222 commit;
3223
3224 end REFRESH_MVIEW_UTW;
3225
3226
3227 -- -----------------------------------------------------
3228 -- procedure REFRESH_MVIEW_UTX
3229 -- -----------------------------------------------------
3230 procedure REFRESH_MVIEW_UTX (p_worker_id in number) is
3231
3232 l_process varchar2(30);
3233 l_extraction_type varchar2(30);
3234 l_pji_schema varchar2(30);
3235 l_apps_schema varchar2(30);
3236 l_p_degree number := 0;
3237
3238 l_errbuf varchar2(255);
3239 l_retcode varchar2(255);
3240
3241 begin
3242
3243 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3244
3245 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3246 (
3247 l_process,
3248 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTX(p_worker_id);'
3249 )) then
3250 return;
3251 end if;
3252
3253 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3254 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3255
3256 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3257 l_extraction_type <> 'PARTIAL') then
3258 return;
3259 end if;
3260
3261 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3262 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3263 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3264 if (l_p_degree = 1) then
3265 l_p_degree := 0;
3266 end if;
3267
3268 if (l_extraction_type = 'FULL') then
3269 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3270 l_retcode,
3271 'PJI_RM_ORG_F_MV',
3272 'C',
3273 'N');
3274 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3275 l_retcode,
3276 'PJI_RM_ORGO_F_MV',
3277 'C',
3278 'N');
3279 else
3280 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pji_schema,
3281 TABNAME => 'MLOG$_PJI_RM_RES_F',
3282 PERCENT => 10,
3283 DEGREE => l_p_degree);
3284 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3285 l_retcode,
3286 'PJI_RM_ORG_F_MV',
3287 'F',
3288 'N');
3289 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
3290 TABNAME => 'MLOG$_PJI_RM_ORG_F_MV',
3291 PERCENT => 10,
3292 DEGREE => l_p_degree);
3293 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3294 l_retcode,
3295 'PJI_RM_ORGO_F_MV',
3296 'F',
3297 'N');
3298 end if;
3299
3300 if (l_extraction_type <> 'INCREMENTAL') then
3301 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3302 tabname => 'PJI_RM_ORG_F_MV',
3303 percent => 10,
3304 degree => l_p_degree);
3305 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3306 tabname => 'PJI_RM_ORGO_F_MV',
3307 percent => 10,
3308 degree => l_p_degree);
3309 end if;
3310
3311 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3312 (
3313 l_process,
3314 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTX(p_worker_id);'
3315 );
3316
3317 commit;
3318
3319 end REFRESH_MVIEW_UTX;
3320
3321
3322 -- -----------------------------------------------------
3323 -- procedure REFRESH_MVIEW_UTJ
3324 -- -----------------------------------------------------
3325 procedure REFRESH_MVIEW_UTJ (p_worker_id in number) is
3326
3327 l_process varchar2(30);
3328 l_extraction_type varchar2(30);
3329 l_apps_schema varchar2(30);
3330 l_p_degree number := 0;
3331
3332 l_errbuf varchar2(255);
3333 l_retcode varchar2(255);
3334
3335 begin
3336
3337 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3338
3339 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3340 (
3341 l_process,
3342 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTJ(p_worker_id);'
3343 )) then
3344 return;
3345 end if;
3346
3347 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3348 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3349
3350 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3351 l_extraction_type <> 'PARTIAL') then
3352 return;
3353 end if;
3354
3355 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3356 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3357 if (l_p_degree = 1) then
3358 l_p_degree := 0;
3359 end if;
3360
3361 if (l_extraction_type = 'FULL') then
3362 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3363 l_retcode,
3364 'PJI_RM_JOB_F_MV',
3365 'C',
3366 'N');
3367 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3368 l_retcode,
3369 'PJI_RM_JOBO_F_MV',
3370 'C',
3371 'N');
3372 else
3373 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3374 l_retcode,
3375 'PJI_RM_JOB_F_MV',
3376 'F',
3377 'N');
3378 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_apps_schema,
3379 TABNAME => 'MLOG$_PJI_RM_JOB_F_MV',
3380 PERCENT => 10,
3381 DEGREE => l_p_degree);
3382 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3383 l_retcode,
3384 'PJI_RM_JOBO_F_MV',
3385 'F',
3386 'N');
3387 end if;
3388
3389 if (l_extraction_type <> 'INCREMENTAL') then
3390 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3391 tabname => 'PJI_RM_JOB_F_MV',
3392 percent => 10,
3393 degree => l_p_degree);
3394 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3395 tabname => 'PJI_RM_JOBO_F_MV',
3396 percent => 10,
3397 degree => l_p_degree);
3398 end if;
3399
3400 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3401 (
3402 l_process,
3403 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTJ(p_worker_id);'
3404 );
3405
3406 commit;
3407
3408 end REFRESH_MVIEW_UTJ;
3409
3410 -- -----------------------------------------------------
3411 -- procedure REFRESH_MVIEW_TIME
3412 -- -----------------------------------------------------
3413 procedure REFRESH_MVIEW_TIME (p_worker_id in number) is
3414
3415 l_process varchar2(30);
3416 l_extraction_type varchar2(30);
3417 l_apps_schema varchar2(30);
3418 l_p_degree number := 0;
3419
3420 l_errbuf varchar2(255);
3421 l_retcode varchar2(255);
3422
3423 begin
3424
3425 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3426
3427 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3428 (
3429 l_process,
3430 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME(p_worker_id);'
3431 )) then
3432 return;
3433 end if;
3434
3435 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3436 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3437
3438 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3439 l_extraction_type <> 'PARTIAL') then
3440 return;
3441 end if;
3442
3443 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3444 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3445 if (l_p_degree = 1) then
3446 l_p_degree := 0;
3447 end if;
3448
3449 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3450 l_retcode,
3451 'PJI_TIME_MV',
3452 'C',
3453 'N');
3454
3455 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3456 tabname => 'PJI_TIME_MV',
3457 percent => 10,
3458 degree => l_p_degree);
3459
3460 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3461 (
3462 l_process,
3463 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME(p_worker_id);'
3464 );
3465
3466 commit;
3467
3468 end REFRESH_MVIEW_TIME;
3469
3470
3471 -- -----------------------------------------------------
3472 -- procedure REFRESH_MVIEW_TIME_DAY
3473 -- -----------------------------------------------------
3474 procedure REFRESH_MVIEW_TIME_DAY (p_worker_id in number) is
3475
3476 l_process varchar2(30);
3477 l_extraction_type varchar2(30);
3478 l_apps_schema varchar2(30);
3479 l_p_degree number := 0;
3480
3481 l_errbuf varchar2(255);
3482 l_retcode varchar2(255);
3483
3484 begin
3485
3486 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3487
3488 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3489 (
3490 l_process,
3491 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_DAY(p_worker_id);'
3492 )) then
3493 return;
3494 end if;
3495
3496 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3497 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3498
3499 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3500 l_extraction_type <> 'PARTIAL') then
3501 return;
3502 end if;
3503
3504 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3505 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3506 if (l_p_degree = 1) then
3507 l_p_degree := 0;
3508 end if;
3509
3510 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3511 l_retcode,
3512 'PJI_TIME_DAY_MV',
3513 'C',
3514 'N');
3515
3516 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3517 tabname => 'PJI_TIME_DAY_MV',
3518 percent => 10,
3519 degree => l_p_degree);
3520
3521 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3522 (
3523 l_process,
3524 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_DAY(p_worker_id);'
3525 );
3526
3527 commit;
3528
3529 end REFRESH_MVIEW_TIME_DAY;
3530
3531
3532 -- -----------------------------------------------------
3533 -- procedure REFRESH_MVIEW_TIME_TREND
3534 -- -----------------------------------------------------
3535 procedure REFRESH_MVIEW_TIME_TREND (p_worker_id in number) is
3536
3537 l_process varchar2(30);
3538 l_extraction_type varchar2(30);
3539 l_apps_schema varchar2(30);
3540 l_p_degree number := 0;
3541
3542 l_errbuf varchar2(255);
3543 l_retcode varchar2(255);
3544
3545 begin
3546
3547 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
3548
3549 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
3550 (
3551 l_process,
3552 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_TREND(p_worker_id);'
3553 )) then
3554 return;
3555 end if;
3556
3557 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
3558 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
3559
3560 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) = 'Y' and
3561 l_extraction_type <> 'PARTIAL') then
3562 return;
3563 end if;
3564
3565 l_apps_schema := PJI_UTILS.GET_APPS_SCHEMA_NAME;
3566 l_p_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
3567 if (l_p_degree = 1) then
3568 l_p_degree := 0;
3569 end if;
3570
3571 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
3572 l_retcode,
3573 'PJI_TIME_TREND_MV',
3574 'C',
3575 'N');
3576
3577 FND_STATS.GATHER_TABLE_STATS(ownname => l_apps_schema,
3578 tabname => 'PJI_TIME_TREND_MV',
3579 percent => 10,
3580 degree => l_p_degree);
3581
3582 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
3583 (
3584 l_process,
3585 'PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_TREND(p_worker_id);'
3586 );
3587
3588 commit;
3589
3590 end REFRESH_MVIEW_TIME_TREND;
3591
3592
3593 -- -----------------------------------------------------
3594 -- procedure CLEANUP
3595 -- -----------------------------------------------------
3596 procedure CLEANUP (p_worker_id in number) is
3597
3598 l_schema varchar2(30);
3599
3600 begin
3601
3602 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
3603
3604 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
3605 'PJI_RM_AGGR_RES1','NORMAL',null);
3606
3607 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
3608 'PJI_RM_AGGR_RES2','NORMAL',null);
3609
3610 end CLEANUP;
3611
3612 end PJI_RM_SUM_ROLLUP_RES;