[Home] [Help]
PACKAGE BODY: APPS.PJI_RM_SUM_EXTR
Source
1 package body PJI_RM_SUM_EXTR as
2 /* $Header: PJISR02B.pls 120.8 2005/12/07 21:57:59 appldev noship $ */
3
4 -- -----------------------------------------------------
5 -- procedure PROCESS_DANGLING_ROWS
6 -- -----------------------------------------------------
7 procedure PROCESS_DANGLING_ROWS
8 (
9 p_worker_id in number
10 ) is
11
12 l_process varchar2(30);
13
14 begin
15
16 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
17
18 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
19 'PJI_RM_SUM_EXTR.PROCESS_DANGLING_ROWS(p_worker_id);')) then
20 return;
21 end if;
22
23 --The calendar_type is hard coded as 'C'. The dangling 'P' and 'G'
24 --records are inserted into TMP1 as 'C'
25
26 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
27 (
28 WORKER_ID,
29 DANGLING_FLAG,
30 ROW_ID,
31 RECORD_TYPE,
32 PROJECT_ID,
33 PERSON_ID,
34 EXPENDITURE_ORG_ID,
35 EXPENDITURE_ORGANIZATION_ID,
36 WORK_TYPE_ID,
37 JOB_ID,
38 TIME_ID,
39 PERIOD_TYPE_ID,
40 CALENDAR_TYPE,
41 GL_CALENDAR_ID,
42 PA_CALENDAR_ID,
43 CAPACITY_HRS,
44 TOTAL_HRS_A,
45 BILL_HRS_A,
46 CONF_HRS_S,
47 PROV_HRS_S,
48 UNASSIGNED_HRS_S,
49 CONF_OVERCOM_HRS_S,
50 PROV_OVERCOM_HRS_S
51 )
52 select /*+ parallel(tmp1) full(res_map) */
53 p_worker_id,
54 null,
55 tmp1.ROWID,
56 tmp1.RECORD_TYPE,
57 tmp1.PROJECT_ID,
58 tmp1.PERSON_ID,
59 tmp1.EXPENDITURE_ORG_ID,
60 tmp1.EXPENDITURE_ORGANIZATION_ID,
61 tmp1.WORK_TYPE_ID,
62 tmp1.JOB_ID,
63 tmp1.TIME_ID,
64 tmp1.PERIOD_TYPE_ID,
65 'C',
66 res_map.GL_CALENDAR_ID,
67 res_map.PA_CALENDAR_ID,
68 tmp1.CAPACITY_HRS,
69 tmp1.TOTAL_HRS_A,
70 tmp1.BILL_HRS_A,
71 tmp1.CONF_HRS_S,
72 tmp1.PROV_HRS_S,
73 tmp1.UNASSIGNED_HRS_S,
74 tmp1.CONF_OVERCOM_HRS_S,
75 tmp1.PROV_OVERCOM_HRS_S
76 from
77 PJI_RM_DNGL_RES tmp1,
78 PJI_RM_ORG_BATCH_MAP orgs,
79 PJI_ORG_EXTR_INFO res_map
80 where
81 tmp1.WORKER_ID = 0 and
82 orgs.WORKER_ID = p_worker_id and
83 orgs.ORGANIZATION_ID = tmp1.EXPENDITURE_ORGANIZATION_ID and
84 tmp1.EXPENDITURE_ORG_ID = res_map.ORG_ID and
85 tmp1.TIME_ID >= res_map.PA_CALENDAR_MIN_DATE and
86 tmp1.TIME_ID <= res_map.PA_CALENDAR_MAX_DATE and
87 tmp1.TIME_ID >= res_map.GL_CALENDAR_MIN_DATE and
88 tmp1.TIME_ID <= res_map.GL_CALENDAR_MAX_DATE and
89 tmp1.TIME_ID >= res_map.EN_CALENDAR_MIN_DATE and
90 tmp1.TIME_ID <= res_map.EN_CALENDAR_MAX_DATE;
91
92 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process,
93 'PJI_RM_SUM_EXTR.PROCESS_DANGLING_ROWS(p_worker_id);');
94
95 commit;
96
97 end PROCESS_DANGLING_ROWS;
98
99
100 -- -----------------------------------------------------
101 -- procedure PURGE_DANGLING_ROWS
102 -- -----------------------------------------------------
103 procedure PURGE_DANGLING_ROWS (p_worker_id in number) is
104
105 l_process varchar2(30);
106
107 begin
108
109 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
110
111 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.PURGE_DANGLING_ROWS(p_worker_id);')) then
112 return;
113 end if;
114
115 delete /*+ parallel(res) */
116 from PJI_RM_DNGL_RES res
117 where WORKER_ID = 0 and
118 ROWID in (select ROW_ID
119 from PJI_RM_AGGR_RES1
120 where WORKER_ID = p_worker_id);
121
122 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process, 'PJI_RM_SUM_EXTR.PURGE_DANGLING_ROWS(p_worker_id);');
123
124 commit;
125
126 end PURGE_DANGLING_ROWS;
127
128
129 -- -----------------------------------------------------
130 -- procedure RES_ROWID_TABLE
131 -- -----------------------------------------------------
132 procedure RES_ROWID_TABLE (p_worker_id in number) is
133
134 l_process varchar2(30);
135 l_schema varchar2(30);
136
137 begin
138
139 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
140
141 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.RES_ROWID_TABLE(p_worker_id);')) then
142 return;
143 end if;
144
145 insert /*+ append parallel(res_i) */ into PJI_PJI_RMAP_RES res_i
146 (
147 WORKER_ID,
148 STG_ROWID
149 )
150 select /*+ ordered */
151 p_worker_id WORKER_ID,
152 res6.ROWID STG_ROWID
153 from
154 PJI_PJI_PROJ_BATCH_MAP map,
155 PJI_RM_AGGR_RES6 res6,
156 PJI_RESOURCES_DENORM denorm
157 where
158 map.WORKER_ID = p_worker_id and
159 res6.PROJECT_ID = map.PROJECT_ID and
160 res6.PERSON_ID = denorm.PERSON_ID and
161 denorm.UTILIZATION_FLAG = 'Y' and
162 to_date(to_char(res6.TIME_ID), 'J') between denorm.START_DATE and
163 denorm.END_DATE;
164
165 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_RM_SUM_EXTR.RES_ROWID_TABLE(p_worker_id);');
166
167 commit;
168
169 end RES_ROWID_TABLE;
170
171
172 -- -----------------------------------------------------
173 -- procedure EXTRACT_BATCH_FID_FULL
174 --
175 -- This procedure is used for initial data extraction
176 -- -----------------------------------------------------
177 procedure EXTRACT_BATCH_FID_FULL (p_worker_id IN NUMBER) is
178
179 l_process varchar2(30);
180 l_counter number := 0;
181 l_from_org_id number := 0;
182 l_to_org_id number := 0;
183 l_min_date number;
184
185 cursor c_update_fid is
186 select fid.ROWID as row_id
187 from pa_forecast_item_details fid
188 where fid.expenditure_organization_id in (select organization_id
189 from pji_rm_org_batch_map
190 where worker_id = p_worker_id)
191 and fid.pji_summarized_flag = 'N';
192
193 begin
194
195 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
196
197 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
198 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_FULL(p_worker_id);')) then
199 return;
200 end if;
201
202 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
203 (PJI_RM_SUM_MAIN.g_process,
204 'EXTRACTION_TYPE') <> 'FULL' ) then
205 return;
206 end if;
207
208 l_min_date := to_number(to_char(to_date(
209 PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
210 PJI_RM_SUM_MAIN.g_date_mask), 'J'));
211
212 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
213 (
214 WORKER_ID,
215 DANGLING_FLAG,
216 RECORD_TYPE,
217 TOTAL_HRS_A,
218 BILL_HRS_A,
219 CAPACITY_HRS,
220 CONF_HRS_S,
221 PROV_HRS_S,
222 UNASSIGNED_HRS_S,
223 CONF_OVERCOM_HRS_S,
224 PROV_OVERCOM_HRS_S,
225 PERIOD_TYPE_ID,
226 CALENDAR_TYPE,
227 GL_CALENDAR_ID,
228 PA_CALENDAR_ID,
229 EXPENDITURE_ORGANIZATION_ID,
230 EXPENDITURE_ORG_ID,
231 TIME_ID,
232 PERSON_ID,
233 JOB_ID,
234 WORK_TYPE_ID,
235 PROJECT_ID
236 )
237 select
238 WORKER_ID,
239 DANGLING_FLAG,
240 RECORD_TYPE,
241 TOTAL_HRS_A,
242 BILL_HRS_A,
243 CAPACITY_HRS,
244 CONF_HRS_S,
245 PROV_HRS_S,
246 UNASSIGNED_HRS_S,
247 CONF_OVERCOM_HRS_S,
248 PROV_OVERCOM_HRS_S,
249 PERIOD_TYPE_ID,
250 CALENDAR_TYPE,
251 GL_CALENDAR_ID,
252 PA_CALENDAR_ID,
253 EXPENDITURE_ORGANIZATION_ID,
254 EXPENDITURE_ORG_ID,
255 TIME_ID,
256 PERSON_ID,
257 JOB_ID,
258 WORK_TYPE_ID,
259 PROJECT_ID
260 from
261 (
262 select -- Selecting data from source : FI
263 WORKER_ID,
264 DANGLING_FLAG,
265 RECORD_TYPE,
266 sum(TOTAL_HRS_A) TOTAL_HRS_A,
267 sum(BILL_HRS_A) BILL_HRS_A,
268 sum(CAPACITY_HRS) CAPACITY_HRS,
269 sum(CONF_HRS_S) CONF_HRS_S,
270 sum(PROV_HRS_S) PROV_HRS_S,
271 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
272 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
273 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
274 PERIOD_TYPE_ID,
275 CALENDAR_TYPE,
276 GL_CALENDAR_ID,
277 PA_CALENDAR_ID,
278 EXPENDITURE_ORGANIZATION_ID,
279 EXPENDITURE_ORG_ID,
280 TIME_ID,
281 PERSON_ID,
282 JOB_ID,
283 WORK_TYPE_ID,
284 PROJECT_ID
285 from
286 (
287 select /*+ ORDERED
288 full(fid) use_hash(fid) parallel(fid)
289 full(fi) use_hash(fi) parallel(fi)
290 full(res) use_hash(res) parallel(res)
291 full(wt) use_hash(wt)
292 full(res_info) use_hash(res_info)
293 */
294 p_worker_id WORKER_ID,
295 case when res_info.ORG_ID is null
296 then 'O'
297 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.EN_CALENDAR_MIN_DATE or
298 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.EN_CALENDAR_MAX_DATE or
299 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.GL_CALENDAR_MIN_DATE or
300 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.GL_CALENDAR_MAX_DATE or
301 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.PA_CALENDAR_MIN_DATE or
302 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.PA_CALENDAR_MAX_DATE
303 then 'T'
304 else null
305 end DANGLING_FLAG,
306 case when fi.FORECAST_ITEM_TYPE = 'U'
307 then 'U'
308 else 'N'
309 end RECORD_TYPE,
310 case when fi.FORECAST_ITEM_TYPE = 'U'
311 then fid.CAPACITY_QUANTITY
312 else to_number(null)
313 end CAPACITY_HRS,
314 case when fi.FORECAST_ITEM_TYPE = 'A'
315 then fid.ITEM_QUANTITY *
316 decode(fi.PROVISIONAL_FLAG, 'N', 1, 0)
317 else to_number(null)
318 end CONF_HRS_S,
319 case when fi.FORECAST_ITEM_TYPE = 'A'
320 then fid.ITEM_QUANTITY *
321 decode(fi.PROVISIONAL_FLAG, 'Y', 1, 0)
322 else to_number(null)
323 end PROV_HRS_S,
324 case when fi.FORECAST_ITEM_TYPE = 'U'
325 then fid.ITEM_QUANTITY
326 else to_number(null)
327 end UNASSIGNED_HRS_S,
328 case when fi.FORECAST_ITEM_TYPE = 'U'
329 then fid.OVERCOMMITMENT_QTY *
330 decode(fi.OVERCOMMITMENT_FLAG, 'Y', 1, 0)
331 else to_number(null)
332 end CONF_OVERCOM_HRS_S,
333 case when fi.FORECAST_ITEM_TYPE = 'U'
334 then fid.OVERPROVISIONAL_QTY *
335 decode(fi.OVERCOMMITMENT_FLAG, 'Y', 1, 0)
336 else to_number(null)
337 end PROV_OVERCOM_HRS_S,
338 to_number(null) TOTAL_HRS_A,
339 to_number(null) BILL_HRS_A,
340 1 PERIOD_TYPE_ID,
341 case when res_info.ORG_ID is null
342 then 'C'
343 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.PA_CALENDAR_MIN_DATE or
344 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.PA_CALENDAR_MAX_DATE
345 then 'P'
346 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.GL_CALENDAR_MIN_DATE or
347 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.GL_CALENDAR_MAX_DATE
348 then 'G'
349 else 'C'
350 end CALENDAR_TYPE ,
351 res_info.GL_CALENDAR_ID,
352 res_info.PA_CALENDAR_ID,
353 fid.EXPENDITURE_ORGANIZATION_ID,
354 fid.EXPENDITURE_ORG_ID,
355 greatest(to_number(to_char(fi.ITEM_DATE,'J')), l_min_date) TIME_ID,
356 fi.PERSON_ID,
357 nvl(nvl(fid.JOB_ID, res.JOB_ID), -1) JOB_ID,
358 fid.WORK_TYPE_ID,
359 fid.PROJECT_ID
360 from
361 PJI_RM_WORK_TYPE_INFO wt,
362 PA_FORECAST_ITEM_DETAILS fid,
363 PA_FORECAST_ITEMS fi,
364 PJI_RESOURCES_DENORM res,
365 PJI_ORG_EXTR_INFO res_info
366 where
367 nvl(fid.pji_summarized_flag,'Y') <> 'N' and
368 fi.FORECAST_ITEM_ID = fid.FORECAST_ITEM_ID and
369 fi.FORECAST_ITEM_TYPE in ('U', 'A') and
370 fid.WORK_TYPE_ID = wt.WORK_TYPE_ID and
371 wt.RECORD_TYPE = 'NORMAL' and
372 res.PERSON_ID = fi.PERSON_ID and
373 res.UTILIZATION_FLAG = 'Y' and
374 fi.item_date between res.START_DATE and res.END_DATE and
375 fid.EXPENDITURE_ORG_ID = res_info.ORG_ID
376 union all
377 select /*+ ordered */
378 p_worker_id WORKER_ID,
379 null DANGLING_FLAG,
380 'N' RECORD_TYPE,
381 to_number(null) CAPACITY_HRS,
382 to_number(null) CONF_HRS_S,
383 to_number(null) PROV_HRS_S,
384 to_number(null) UNASSIGNED_HRS_S,
385 to_number(null) CONF_OVERCOM_HRS_S,
386 to_number(null) PROV_OVERCOM_HRS_S,
387 res6.TOTAL_HRS_A,
388 res6.BILL_HRS_A,
389 1 PERIOD_TYPE_ID,
390 res6.CALENDAR_TYPE,
391 res6.GL_CALENDAR_ID,
392 res6.PA_CALENDAR_ID,
393 res6.EXPENDITURE_ORGANIZATION_ID,
394 res6.EXPENDITURE_ORG_ID,
395 res6.TIME_ID,
396 res6.PERSON_ID,
397 res6.JOB_ID,
398 res6.WORK_TYPE_ID,
399 res6.PROJECT_ID
400 from
401 PJI_RM_AGGR_RES6 res6,
402 PJI_PJI_RMAP_RES res6_r
403 where
404 res6_r.WORKER_ID = p_worker_id and
405 res6.ROWID = res6_r.STG_ROWID
406 ) tmp1
407 group by
408 WORKER_ID,
409 DANGLING_FLAG,
410 RECORD_TYPE,
411 PERIOD_TYPE_ID,
412 CALENDAR_TYPE,
413 GL_CALENDAR_ID,
414 PA_CALENDAR_ID,
415 EXPENDITURE_ORGANIZATION_ID,
416 EXPENDITURE_ORG_ID,
417 TIME_ID,
418 PERSON_ID,
419 JOB_ID,
420 WORK_TYPE_ID,
421 PROJECT_ID
422 )
423 where
424 nvl(TOTAL_HRS_A, 0) <> 0 or
425 nvl(BILL_HRS_A, 0) <> 0 or
426 nvl(CAPACITY_HRS, 0) <> 0 or
427 nvl(CONF_HRS_S, 0) <> 0 or
428 nvl(PROV_HRS_S, 0) <> 0 or
429 nvl(UNASSIGNED_HRS_S, 0) <> 0 or
430 nvl(CONF_OVERCOM_HRS_S, 0) <> 0 or
431 nvl(PROV_OVERCOM_HRS_S, 0) <> 0;
432
433 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_FULL(p_worker_id);');
434
435 COMMIT;
436
437 end EXTRACT_BATCH_FID_FULL;
438
439
440 -- -----------------------------------------------------
441 -- procedure EXTRACT_BATCH_FID_ROWIDS
442 -- This procedure is used in partial and incremental
443 -- data extraction
444 -- -----------------------------------------------------
445 PROCEDURE EXTRACT_BATCH_FID_ROWIDS (p_worker_id IN NUMBER) IS
446
447 l_process varchar2(30);
448 l_from_org_id number := 0;
449 l_to_org_id number := 0;
450 l_extraction_type varchar2(30);
451
452 l_row_count number;
453 l_last_update_date date;
454 l_last_updated_by number;
455 l_request_id number;
456 l_program_appl_id number;
457 l_program_id number;
458 l_program_update_date date;
459
460 BEGIN
461
462 l_process := PJI_RM_SUM_MAIN.g_process || TO_CHAR(p_worker_id);
463
464 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_ROWIDS(p_worker_id);')) THEN
465 RETURN;
466 END IF;
467
468 -- JOB_ID Util --> Non-Util: make sure source reversals are not summarized
469
470 select count(*)
471 into l_row_count
472 from PJI_RES_DELTA
473 where CHANGE_TYPE = 'N';
474
475 if (l_row_count > 0) then
476
477 l_last_update_date := sysdate;
478 l_last_updated_by := FND_GLOBAL.USER_ID;
479 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
480 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
481 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
482 l_program_update_date := sysdate;
483
484 update PA_FORECAST_ITEM_DETAILS fid
485 set fid.PJI_SUMMARIZED_FLAG = null,
486 fid.LAST_UPDATE_DATE = l_last_update_date,
487 fid.LAST_UPDATED_BY = l_last_updated_by,
488 fid.REQUEST_ID = l_request_id,
489 fid.PROGRAM_APPLICATION_ID = l_program_appl_id,
490 fid.PROGRAM_ID = l_program_id,
491 fid.PROGRAM_UPDATE_DATE = l_program_update_date
492 where fid.PJI_SUMMARIZED_FLAG = 'N' and
493 fid.FORECAST_ITEM_ID in
494 (select /*+ cardinality(delta, 1) */
495 fi.FORECAST_ITEM_ID
496 from PJI_RES_DELTA delta,
497 PA_FORECAST_ITEMS fi
498 where delta.CHANGE_TYPE = 'N' and
499 delta.RESOURCE_ID = fi.RESOURCE_ID and
500 fi.FORECAST_ITEM_TYPE in ('U', 'A') and
501 fi.DELETE_FLAG in ('Y', 'N') and
502 fi.ITEM_DATE between delta.START_DATE and
503 delta.END_DATE);
504
505 end if;
506
507 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
508 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
509
510 if (l_extraction_type = 'FULL') then
511
512 insert /*+ append */ into PJI_RM_REXT_FCSTITEM
513 (
514 WORKER_ID
515 , FID_ROWID
516 , START_DATE
517 , END_DATE
518 , PJI_SUMMARIZED_FLAG
519 , BATCH_ID
520 )
521 SELECT /*+ index_ffs(fid, PA_FORECAST_ITEM_DETAILS_N2) */
522 p_worker_id
523 , fid.ROWID
524 , null
525 , null
526 , fid.PJI_SUMMARIZED_FLAG
527 , ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
528 FROM
529 PA_FORECAST_ITEM_DETAILS fid
530 WHERE
531 fid.PJI_SUMMARIZED_FLAG = 'N';
532
533 else
534
535 INSERT /*+ APPEND */ INTO PJI_RM_REXT_FCSTITEM
536 (
537 WORKER_ID
538 , FID_ROWID
539 , START_DATE
540 , END_DATE
541 , PJI_SUMMARIZED_FLAG
542 , BATCH_ID
543 )
544 SELECT /*+ ORDERED
545 USE_NL(fid)
546 INDEX(fid, PA_FORECAST_ITEM_DETAILS_N2)
547 NOPARALLEL(bat)
548 */
549 p_worker_id WORKER_ID
550 , fid.ROWID FID_ROWID
551 , bat.start_date START_DATE
552 , bat.end_date END_DATE
553 , fid.PJI_SUMMARIZED_FLAG
554 , ceil(ROWNUM / PJI_RM_SUM_MAIN.g_commit_threshold)
555 FROM
556 pji_rm_org_batch_map bat
557 , pa_forecast_item_details fid
558 , pji_rm_rext_fcstitem fcst
559 WHERE
560 bat.WORKER_ID = p_worker_id and
561 fid.EXPENDITURE_ORGANIZATION_ID = bat.ORGANIZATION_ID and
562 ((nvl(fid.PJI_SUMMARIZED_FLAG, 'N') <> 'X' and
563 bat.EXTRACTION_TYPE in ('F', 'P')) or
564 (fid.PJI_SUMMARIZED_FLAG = 'N' and
565 bat.EXTRACTION_TYPE = 'I')) and
566 p_worker_id = fcst.WORKER_ID (+) and
567 fid.ROWID = fcst.FID_ROWID (+) and
568 fcst.WORKER_ID is null;
569
570 end if;
571
572 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_ROWIDS(p_worker_id);');
573
574 COMMIT;
575
576 END EXTRACT_BATCH_FID_ROWIDS;
577
578 -- -----------------------------------------------------
579 -- procedure EXTRACT_BATCH_FID
580 --
581 -- The following steps are done in this procedure
582 -- - Extract data from FID
583 -- - Identify dangling records
584 -- - Identify data for partial refresh
585 -- - Aggregate and insert data into PJI_RM_AGGR_RES1
586 -- -----------------------------------------------------
587 PROCEDURE EXTRACT_BATCH_FID (p_worker_id IN NUMBER) IS
588
589 l_process VARCHAR2(30);
590 l_min_date NUMBER;
591 l_extraction_type VARCHAR2(30);
592
593 l_last_update_date date;
594 l_last_updated_by number;
595 l_request_id number;
596 l_program_appl_id number;
597 l_program_id number;
598 l_program_update_date date;
599
600 BEGIN
601
602 l_process := PJI_RM_SUM_MAIN.g_process || TO_CHAR(p_worker_id);
603
604 IF (NOT PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID(p_worker_id);')) THEN
605 RETURN;
606 END IF;
607
608 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
609 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
610
611 if (l_extraction_type = 'FULL' ) then
612 return;
613 end if;
614
615 l_min_date := to_number(to_char(to_date(
616 PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
617 PJI_RM_SUM_MAIN.g_date_mask), 'J'));
618
619 -- implicit commit
620 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
621 tabname => 'PJI_RM_REXT_FCSTITEM',
622 percent => 10,
623 degree => BIS_COMMON_PARAMETERS.
624 GET_DEGREE_OF_PARALLELISM);
625
626 -- Initial/Incremental collection from forecast log table
627 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
628 (
629 WORKER_ID,
630 DANGLING_FLAG,
631 RECORD_TYPE,
632 TOTAL_HRS_A,
633 BILL_HRS_A,
634 CAPACITY_HRS,
635 CONF_HRS_S,
636 PROV_HRS_S,
637 UNASSIGNED_HRS_S,
638 CONF_OVERCOM_HRS_S,
639 PROV_OVERCOM_HRS_S,
640 PERIOD_TYPE_ID,
641 CALENDAR_TYPE,
642 GL_CALENDAR_ID,
643 PA_CALENDAR_ID,
644 EXPENDITURE_ORGANIZATION_ID,
645 EXPENDITURE_ORG_ID,
646 TIME_ID,
647 PERSON_ID,
648 JOB_ID,
649 WORK_TYPE_ID,
650 PROJECT_ID
651 )
652 select
653 WORKER_ID,
654 DANGLING_FLAG,
655 RECORD_TYPE,
656 TOTAL_HRS_A,
657 BILL_HRS_A,
658 CAPACITY_HRS,
659 CONF_HRS_S,
660 PROV_HRS_S,
661 UNASSIGNED_HRS_S,
662 CONF_OVERCOM_HRS_S,
663 PROV_OVERCOM_HRS_S,
664 PERIOD_TYPE_ID,
665 CALENDAR_TYPE,
666 GL_CALENDAR_ID,
667 PA_CALENDAR_ID,
668 EXPENDITURE_ORGANIZATION_ID,
669 EXPENDITURE_ORG_ID,
670 TIME_ID,
671 PERSON_ID,
672 JOB_ID,
673 WORK_TYPE_ID,
674 PROJECT_ID
675 from
676 (
677 select -- Selecting data from source : FI
678 WORKER_ID,
679 DANGLING_FLAG,
680 RECORD_TYPE,
681 sum(TOTAL_HRS_A) TOTAL_HRS_A,
682 sum(BILL_HRS_A) BILL_HRS_A,
683 sum(CAPACITY_HRS) CAPACITY_HRS,
684 sum(CONF_HRS_S) CONF_HRS_S,
685 sum(PROV_HRS_S) PROV_HRS_S,
686 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
687 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
688 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S,
689 PERIOD_TYPE_ID,
690 CALENDAR_TYPE,
691 GL_CALENDAR_ID,
692 PA_CALENDAR_ID,
693 EXPENDITURE_ORGANIZATION_ID,
694 EXPENDITURE_ORG_ID,
695 TIME_ID,
696 PERSON_ID,
697 JOB_ID,
698 WORK_TYPE_ID,
699 PROJECT_ID
700 from
701 (
702 select /*+ ordered use_nl(fid, fi, res)
703 parallel(scope) parallel(fi)
704 parallel(fid) parallel(res) */
705 p_worker_id WORKER_ID,
706 case when res_info.ORG_ID is null
707 then 'O'
708 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.EN_CALENDAR_MIN_DATE or
709 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.EN_CALENDAR_MAX_DATE or
710 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.GL_CALENDAR_MIN_DATE or
711 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.GL_CALENDAR_MAX_DATE or
712 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.PA_CALENDAR_MIN_DATE or
713 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.PA_CALENDAR_MAX_DATE
714 then 'T'
715 else null
716 end DANGLING_FLAG,
717 case when fi.FORECAST_ITEM_TYPE = 'U'
718 then 'U'
719 else 'N'
720 end RECORD_TYPE,
721 case when fi.FORECAST_ITEM_TYPE = 'U'
722 then fid.CAPACITY_QUANTITY
723 else to_number(null)
724 end CAPACITY_HRS,
725 case when fi.FORECAST_ITEM_TYPE = 'A'
726 then fid.ITEM_QUANTITY *
727 decode(fi.PROVISIONAL_FLAG, 'N', 1, 0)
728 else to_number(null)
729 end CONF_HRS_S,
730 case when fi.FORECAST_ITEM_TYPE = 'A'
731 then fid.ITEM_QUANTITY *
732 decode(fi.PROVISIONAL_FLAG, 'Y', 1, 0)
733 else to_number(null)
734 end PROV_HRS_S,
735 case when fi.FORECAST_ITEM_TYPE = 'U'
736 then fid.ITEM_QUANTITY
737 else to_number(null)
738 end UNASSIGNED_HRS_S,
739 case when fi.FORECAST_ITEM_TYPE = 'U'
740 then fid.OVERCOMMITMENT_QTY *
741 decode(fi.OVERCOMMITMENT_FLAG,'Y',1,0)
742 else to_number(null)
743 end CONF_OVERCOM_HRS_S,
744 case when fi.FORECAST_ITEM_TYPE = 'U'
745 then fid.OVERPROVISIONAL_QTY *
746 decode(fi.OVERCOMMITMENT_FLAG,'Y',1,0)
747 else to_number(null)
748 end PROV_OVERCOM_HRS_S,
749 to_number(null) TOTAL_HRS_A,
750 to_number(null) BILL_HRS_A,
751 1 PERIOD_TYPE_ID,
752 case when res_info.ORG_ID is null
753 then 'C'
754 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.PA_CALENDAR_MIN_DATE or
755 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.PA_CALENDAR_MAX_DATE
756 then 'P'
757 when greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) < res_info.GL_CALENDAR_MIN_DATE or
758 greatest(to_number(to_char( fi.ITEM_DATE, 'J')), l_min_date) > res_info.GL_CALENDAR_MAX_DATE
759 then 'G'
760 else 'C'
761 end CALENDAR_TYPE ,
762 res_info.GL_CALENDAR_ID,
763 res_info.PA_CALENDAR_ID,
764 fid.EXPENDITURE_ORGANIZATION_ID,
765 fid.EXPENDITURE_ORG_ID,
766 greatest(to_number(to_char(fi.ITEM_DATE,'J')), l_min_date) TIME_ID,
767 fi.PERSON_ID,
768 nvl(nvl(fid.JOB_ID, res.JOB_ID), -1) JOB_ID,
769 fid.WORK_TYPE_ID,
770 fid.PROJECT_ID
771 from
772 PJI_RM_REXT_FCSTITEM scope,
773 PA_FORECAST_ITEM_DETAILS fid,
774 PA_FORECAST_ITEMS fi,
775 PJI_RM_WORK_TYPE_INFO wt,
776 PJI_RESOURCES_DENORM res,
777 PJI_ORG_EXTR_INFO res_info
778 where
779 scope.WORKER_ID = p_worker_id and
780 scope.fid_rowid = fid.rowid and
781 fi.FORECAST_ITEM_ID = fid.FORECAST_ITEM_ID and
782 fi.ITEM_DATE between scope.START_DATE
783 and scope.END_DATE and
784 fi.FORECAST_ITEM_TYPE in ('U', 'A') and
785 fid.WORK_TYPE_ID = wt.WORK_TYPE_ID and
786 wt.RECORD_TYPE = 'NORMAL' and
787 res.PERSON_ID = fi.PERSON_ID and
788 res.UTILIZATION_FLAG = 'Y' and
789 fi.item_date between res.START_DATE and res.END_DATE and
790 fid.EXPENDITURE_ORG_ID = res_info.ORG_ID
791 union all
792 select /*+ ordered */
793 p_worker_id WORKER_ID,
794 null DANGLING_FLAG,
795 'N' RECORD_TYPE,
796 to_number(null) CAPACITY_HRS,
797 to_number(null) CONF_HRS_S,
798 to_number(null) PROV_HRS_S,
799 to_number(null) UNASSIGNED_HRS_S,
800 to_number(null) CONF_OVERCOM_HRS_S,
801 to_number(null) PROV_OVERCOM_HRS_S,
802 res6.TOTAL_HRS_A,
803 res6.BILL_HRS_A,
804 1 PERIOD_TYPE_ID,
805 res6.CALENDAR_TYPE,
806 res6.GL_CALENDAR_ID,
807 res6.PA_CALENDAR_ID,
808 res6.EXPENDITURE_ORGANIZATION_ID,
809 res6.EXPENDITURE_ORG_ID,
810 res6.TIME_ID,
811 res6.PERSON_ID,
812 res6.JOB_ID,
813 res6.WORK_TYPE_ID,
814 res6.PROJECT_ID
815 from
816 PJI_PJI_RMAP_RES res6_r,
817 PJI_RM_AGGR_RES6 res6
818 where
819 res6_r.WORKER_ID = p_worker_id and
820 res6.ROWID = res6_r.STG_ROWID
821 ) tmp1
822 group by
823 WORKER_ID,
824 DANGLING_FLAG,
825 RECORD_TYPE,
826 PERIOD_TYPE_ID,
827 CALENDAR_TYPE,
828 GL_CALENDAR_ID,
829 PA_CALENDAR_ID,
830 EXPENDITURE_ORGANIZATION_ID,
831 EXPENDITURE_ORG_ID,
832 TIME_ID,
833 PERSON_ID,
834 JOB_ID,
835 WORK_TYPE_ID,
836 PROJECT_ID
837 )
838 where
839 nvl(TOTAL_HRS_A, 0) <> 0 or
840 nvl(BILL_HRS_A, 0) <> 0 or
841 nvl(CAPACITY_HRS, 0) <> 0 or
842 nvl(CONF_HRS_S, 0) <> 0 or
843 nvl(PROV_HRS_S, 0) <> 0 or
844 nvl(UNASSIGNED_HRS_S, 0) <> 0 or
845 nvl(CONF_OVERCOM_HRS_S, 0) <> 0 or
846 nvl(PROV_OVERCOM_HRS_S, 0) <> 0;
847
848 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID(p_worker_id);');
849
850 COMMIT;
851
852 END EXTRACT_BATCH_FID;
853
854
855 -- -----------------------------------------------------
856 -- procedure MOVE_DANGLING_ROWS
857 -- -----------------------------------------------------
858 procedure MOVE_DANGLING_ROWS (p_worker_id in number) is
859
860 l_process varchar2(30);
861
862 begin
863
864 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
865
866 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.MOVE_DANGLING_ROWS(p_worker_id);')) then
867 return;
868 end if;
869
870 insert into PJI_RM_DNGL_RES
871 (
872 WORKER_ID,
873 DANGLING_FLAG,
874 RECORD_TYPE,
875 TOTAL_HRS_A,
876 BILL_HRS_A,
877 CAPACITY_HRS,
878 CONF_HRS_S,
879 PROV_HRS_S,
880 UNASSIGNED_HRS_S,
881 CONF_OVERCOM_HRS_S,
882 PROV_OVERCOM_HRS_S,
883 PERIOD_TYPE_ID,
884 CALENDAR_TYPE,
885 EXPENDITURE_ORGANIZATION_ID,
886 EXPENDITURE_ORG_ID,
887 TIME_ID,
888 PERSON_ID,
889 JOB_ID,
890 WORK_TYPE_ID,
891 PROJECT_ID
892 )
893 select /*+ full(tmp) parallel(tmp) */
894 0 WORKER_ID,
895 DANGLING_FLAG,
896 RECORD_TYPE,
897 TOTAL_HRS_A,
898 BILL_HRS_A,
899 CAPACITY_HRS,
900 CONF_HRS_S,
901 PROV_HRS_S,
902 UNASSIGNED_HRS_S,
903 CONF_OVERCOM_HRS_S,
904 PROV_OVERCOM_HRS_S,
905 PERIOD_TYPE_ID,
906 CALENDAR_TYPE,
907 EXPENDITURE_ORGANIZATION_ID,
908 EXPENDITURE_ORG_ID,
909 TIME_ID,
910 PERSON_ID,
911 JOB_ID,
912 WORK_TYPE_ID,
913 PROJECT_ID
914 from
915 PJI_RM_AGGR_RES1 tmp
916 where
917 WORKER_ID = p_worker_id and
918 DANGLING_FLAG is not null;
919
920 delete
921 from PJI_RM_AGGR_RES1
922 where WORKER_ID = p_worker_id and
923 DANGLING_FLAG is not null;
924
925 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process, 'PJI_RM_SUM_EXTR.MOVE_DANGLING_ROWS(p_worker_id);');
926
927 commit;
928
929 end MOVE_DANGLING_ROWS;
930
931
932 -- -----------------------------------------------------
933 -- procedure PURGE_RES_DATA
934 -- -----------------------------------------------------
935 procedure PURGE_RES_DATA (p_worker_id in number) is
936
937 l_process varchar2(30);
938 l_schema varchar2(30);
939
940 begin
941
942 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
943
944 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process, 'PJI_RM_SUM_EXTR.PURGE_RES_DATA(p_worker_id);')) then
945 return;
946 end if;
947
948 delete
949 from PJI_RM_AGGR_RES6
950 where ROWID in (select STG_ROWID
951 from PJI_PJI_RMAP_RES
952 where WORKER_ID = p_worker_id);
953
954 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process, 'PJI_RM_SUM_EXTR.PURGE_RES_DATA(p_worker_id);');
955
956 commit;
957
958 end PURGE_RES_DATA;
959
960
961 -- -----------------------------------------------------
962 -- procedure GET_JOB_ID_LOOKUPS
963 -- -----------------------------------------------------
964 procedure GET_JOB_ID_LOOKUPS
965 (
966 p_worker_id in number
967 ) is
968
969 l_process varchar2(30);
970
971 begin
972
973 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
974
975 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
976 (
977 l_process,
978 'PJI_RM_SUM_EXTR.GET_JOB_ID_LOOKUPS(p_worker_id);'
979 )) then
980 return;
981 end if;
982
983 insert /*+ append parallel(res3_i) */ into PJI_RM_AGGR_RES3 res3_i
984 (
985 WORKER_ID,
986 PROJECT_ID,
987 PERSON_ID,
988 TIME_ID,
989 CALENDAR_TYPE,
990 GL_CALENDAR_ID,
991 PA_CALENDAR_ID,
992 JOB_ID
993 )
994 select /*+ parallel(tmp1) */
995 p_worker_id,
996 PROJECT_ID,
997 PERSON_ID,
998 TIME_ID,
999 CALENDAR_TYPE,
1000 GL_CALENDAR_ID,
1001 PA_CALENDAR_ID,
1002 JOB_ID
1003 from
1004 PJI_RM_AGGR_RES1 tmp1
1005 where
1006 WORKER_ID = p_worker_id and
1007 RECORD_TYPE = 'U' and
1008 CAPACITY_HRS >= 0 and
1009 UNASSIGNED_HRS_S >= 0;
1010
1011 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1012 (
1013 l_process,
1014 'PJI_RM_SUM_EXTR.GET_JOB_ID_LOOKUPS(p_worker_id);'
1015 );
1016
1017 commit;
1018
1019 end GET_JOB_ID_LOOKUPS;
1020
1021
1022 -- -----------------------------------------------------
1023 -- procedure PROCESS_JOB_ID
1024 -- -----------------------------------------------------
1025 procedure PROCESS_JOB_ID
1026 (
1027 p_worker_id in number
1028 ) is
1029
1030 l_process varchar2(30);
1031 l_extraction_type varchar2(30);
1032
1033 begin
1034
1035 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1036
1037 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP
1038 (
1039 l_process,
1040 'PJI_RM_SUM_EXTR.PROCESS_JOB_ID(p_worker_id);'
1041 )) then
1042 return;
1043 end if;
1044
1045 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1046 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
1047
1048 insert /*+ append parallel(res1_i) */ into PJI_RM_AGGR_RES1 res1_i
1049 (
1050 WORKER_ID,
1051 DANGLING_FLAG,
1052 RECORD_TYPE,
1053 PROJECT_ID,
1054 PERSON_ID,
1055 EXPENDITURE_ORG_ID,
1056 EXPENDITURE_ORGANIZATION_ID,
1057 WORK_TYPE_ID,
1058 JOB_ID,
1059 TIME_ID,
1060 PERIOD_TYPE_ID,
1061 CALENDAR_TYPE,
1062 GL_CALENDAR_ID,
1063 PA_CALENDAR_ID,
1064 CAPACITY_HRS,
1065 TOTAL_HRS_A,
1066 BILL_HRS_A,
1067 CONF_HRS_S,
1068 PROV_HRS_S,
1069 UNASSIGNED_HRS_S,
1070 CONF_OVERCOM_HRS_S,
1071 PROV_OVERCOM_HRS_S
1072 )
1073 select
1074 p_worker_id WORKER_ID,
1075 null DANGLING_FLAG,
1076 RECORD_TYPE,
1077 PROJECT_ID,
1078 PERSON_ID,
1079 EXPENDITURE_ORG_ID,
1080 EXPENDITURE_ORGANIZATION_ID,
1081 WORK_TYPE_ID,
1082 JOB_ID,
1083 TIME_ID,
1084 PERIOD_TYPE_ID,
1085 CALENDAR_TYPE,
1086 GL_CALENDAR_ID,
1087 PA_CALENDAR_ID,
1088 sum(CAPACITY_HRS) CAPACITY_HRS,
1089 sum(TOTAL_HRS_A) TOTAL_HRS_A,
1090 sum(BILL_HRS_A) BILL_HRS_A,
1091 sum(CONF_HRS_S) CONF_HRS_S,
1092 sum(PROV_HRS_S) PROV_HRS_S,
1093 sum(UNASSIGNED_HRS_S) UNASSIGNED_HRS_S,
1094 sum(CONF_OVERCOM_HRS_S) CONF_OVERCOM_HRS_S,
1095 sum(PROV_OVERCOM_HRS_S) PROV_OVERCOM_HRS_S
1096 from
1097 (
1098 select /*+ ordered
1099 full(map)
1100 */ -- partial refresh (RM) and job Util --> Non-Util
1101 rmr.RECORD_TYPE,
1102 rmr.PROJECT_ID,
1103 rmr.PERSON_ID,
1104 rmr.EXPENDITURE_ORG_ID,
1105 rmr.EXPENDITURE_ORGANIZATION_ID,
1106 rmr.WORK_TYPE_ID,
1107 rmr.JOB_ID,
1108 rmr.TIME_ID,
1109 rmr.PERIOD_TYPE_ID,
1110 rmr.CALENDAR_TYPE,
1111 info.GL_CALENDAR_ID,
1112 info.PA_CALENDAR_ID,
1113 -rmr.CAPACITY_HRS CAPACITY_HRS,
1114 to_number(null) TOTAL_HRS_A,
1115 to_number(null) BILL_HRS_A,
1116 -rmr.CONF_HRS_S CONF_HRS_S,
1117 -rmr.PROV_HRS_S PROV_HRS_S,
1118 -rmr.UNASSIGNED_HRS_S UNASSIGNED_HRS_S,
1119 -rmr.CONF_OVERCOM_HRS_S CONF_OVERCOM_HRS_S,
1120 -rmr.PROV_OVERCOM_HRS_S PROV_OVERCOM_HRS_S
1121 from
1122 PJI_RM_ORG_BATCH_MAP map,
1123 PJI_RM_RES_WT_F rmr,
1124 PJI_ORG_EXTR_INFO info
1125 where
1126 l_extraction_type = 'PARTIAL' and
1127 map.WORKER_ID = p_worker_id and
1128 map.EXTRACTION_TYPE = 'P' and
1129 rmr.PERIOD_TYPE_ID = 1 and
1130 map.ORGANIZATION_ID = rmr.EXPENDITURE_ORGANIZATION_ID and
1131 rmr.TIME_ID between to_number(to_char(map.START_DATE, 'J'))
1132 and to_number(to_char(map.END_DATE, 'J')) and
1133 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
1134 union all -- partial refresh (FM) and job Util --> Non-Util
1135 select /*+ ordered
1136 full(map)
1137 */
1138 rmr.RECORD_TYPE,
1139 rmr.PROJECT_ID,
1140 rmr.PERSON_ID,
1141 rmr.EXPENDITURE_ORG_ID,
1142 rmr.EXPENDITURE_ORGANIZATION_ID,
1143 rmr.WORK_TYPE_ID,
1144 rmr.JOB_ID,
1145 rmr.TIME_ID,
1146 rmr.PERIOD_TYPE_ID,
1147 rmr.CALENDAR_TYPE,
1148 info.GL_CALENDAR_ID,
1149 info.PA_CALENDAR_ID,
1150 to_number(null) CAPACITY_HRS,
1151 -rmr.TOTAL_HRS_A TOTAL_HRS_A,
1152 -rmr.BILL_HRS_A BILL_HRS_A,
1153 to_number(null) CONF_HRS_S,
1154 to_number(null) PROV_HRS_S,
1155 to_number(null) UNASSIGNED_HRS_S,
1156 to_number(null) CONF_OVERCOM_HRS_S,
1157 to_number(null) PROV_OVERCOM_HRS_S
1158 from
1159 PJI_PJI_PROJ_BATCH_MAP map,
1160 PJI_RM_RES_WT_F rmr,
1161 PJI_ORG_EXTR_INFO info
1162 where
1163 l_extraction_type = 'PARTIAL' and
1164 map.WORKER_ID = p_worker_id and
1165 map.EXTRACTION_TYPE = 'P' and
1166 rmr.PERIOD_TYPE_ID = 1 and
1167 map.PROJECT_ID = rmr.PROJECT_ID and
1168 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
1169 union all -- JOB_ID Util --> Non-Util corrections
1170 select /*+ ordered
1171 full(delta)
1172 full(map)
1173 */
1174 rmr.RECORD_TYPE,
1175 rmr.PROJECT_ID,
1176 rmr.PERSON_ID,
1177 rmr.EXPENDITURE_ORG_ID,
1178 rmr.EXPENDITURE_ORGANIZATION_ID,
1179 rmr.WORK_TYPE_ID,
1180 rmr.JOB_ID,
1181 rmr.TIME_ID,
1182 rmr.PERIOD_TYPE_ID,
1183 rmr.CALENDAR_TYPE,
1184 info.GL_CALENDAR_ID,
1185 info.PA_CALENDAR_ID,
1186 -rmr.CAPACITY_HRS CAPACITY_HRS,
1187 -rmr.TOTAL_HRS_A TOTAL_HRS_A,
1188 -rmr.BILL_HRS_A BILL_HRS_A,
1189 -rmr.CONF_HRS_S CONF_HRS_S,
1190 -rmr.PROV_HRS_S PROV_HRS_S,
1191 -rmr.UNASSIGNED_HRS_S UNASSIGNED_HRS_S,
1192 -rmr.CONF_OVERCOM_HRS_S CONF_OVERCOM_HRS_S,
1193 -rmr.PROV_OVERCOM_HRS_S PROV_OVERCOM_HRS_S
1194 from
1195 PJI_RES_DELTA delta,
1196 PJI_RM_RES_WT_F rmr,
1197 PJI_RM_ORG_BATCH_MAP map,
1198 PJI_PJI_PROJ_BATCH_MAP fm_map,
1199 PJI_ORG_EXTR_INFO info
1200 where
1201 l_extraction_type = 'PARTIAL' and
1202 delta.CHANGE_TYPE = 'N' and
1203 delta.PERSON_ID = rmr.PERSON_ID and
1204 rmr.PERIOD_TYPE_ID = 1 and
1205 rmr.TIME_ID between to_number(to_char(delta.START_DATE, 'J')) and
1206 to_number(to_char(delta.END_DATE, 'J')) and
1207 p_worker_id = map.WORKER_ID (+) and
1208 'P' = map.EXTRACTION_TYPE (+) and
1209 rmr.EXPENDITURE_ORGANIZATION_ID = map.ORGANIZATION_ID (+) and
1210 rmr.TIME_ID between to_number(to_char(map.START_DATE (+), 'J')) and
1211 to_number(to_char(map.END_DATE (+), 'J')) and
1212 map.WORKER_ID is null and
1213 p_worker_id = fm_map.WORKER_ID (+) and
1214 'P' = fm_map.EXTRACTION_TYPE (+) and
1215 rmr.PROJECT_ID = fm_map.PROJECT_ID (+) and
1216 fm_map.WORKER_ID is null and
1217 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
1218 union all -- JOB_ID Util --> Non-Util corrections
1219 select /*+ ordered
1220 full(delta)
1221 full(info)
1222 */
1223 rmr.RECORD_TYPE,
1224 rmr.PROJECT_ID,
1225 rmr.PERSON_ID,
1226 rmr.EXPENDITURE_ORG_ID,
1227 rmr.EXPENDITURE_ORGANIZATION_ID,
1228 rmr.WORK_TYPE_ID,
1229 rmr.JOB_ID,
1230 rmr.TIME_ID,
1231 rmr.PERIOD_TYPE_ID,
1232 rmr.CALENDAR_TYPE,
1233 info.GL_CALENDAR_ID,
1234 info.PA_CALENDAR_ID,
1235 -rmr.CAPACITY_HRS,
1236 -rmr.TOTAL_HRS_A,
1237 -rmr.BILL_HRS_A,
1238 -rmr.CONF_HRS_S,
1239 -rmr.PROV_HRS_S,
1240 -rmr.UNASSIGNED_HRS_S,
1241 -rmr.CONF_OVERCOM_HRS_S,
1242 -rmr.PROV_OVERCOM_HRS_S
1243 from
1244 PJI_RES_DELTA delta,
1245 PJI_RM_RES_WT_F rmr,
1246 PJI_ORG_EXTR_INFO info
1247 where
1248 l_extraction_type = 'INCREMENTAL' and
1249 delta.CHANGE_TYPE = 'N' and
1250 delta.PERSON_ID = rmr.PERSON_ID and
1251 rmr.PERIOD_TYPE_ID = 1 and
1252 rmr.TIME_ID between to_number(to_char(delta.START_DATE, 'J')) and
1253 to_number(to_char(delta.END_DATE, 'J')) and
1254 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
1255 union all -- JOB_ID corrections for 'A' slice of rmr
1256 select /*+ ordered
1257 full(tmp3)
1258 parallel(rmr) */
1259 'A' RECORD_TYPE,
1260 rmr.PROJECT_ID,
1261 rmr.PERSON_ID,
1262 rmr.EXPENDITURE_ORG_ID,
1263 rmr.EXPENDITURE_ORGANIZATION_ID,
1264 rmr.WORK_TYPE_ID,
1265 case when invert.INVERT_ID = 'TMP3'
1266 then tmp3.JOB_ID
1267 when invert.INVERT_ID = 'RMR'
1268 then rmr.JOB_ID
1269 end JOB_ID,
1270 rmr.TIME_ID,
1271 rmr.PERIOD_TYPE_ID,
1272 rmr.CALENDAR_TYPE,
1273 tmp3.GL_CALENDAR_ID,
1274 tmp3.PA_CALENDAR_ID,
1275 case when invert.INVERT_ID = 'TMP3'
1276 then rmr.CAPACITY_HRS
1277 when invert.INVERT_ID = 'RMR'
1278 then -rmr.CAPACITY_HRS
1279 end CAPACITY_HRS,
1280 case when invert.INVERT_ID = 'TMP3'
1281 then rmr.TOTAL_HRS_A
1282 when invert.INVERT_ID = 'RMR'
1283 then -rmr.TOTAL_HRS_A
1284 end TOTAL_HRS_A,
1285 case when invert.INVERT_ID = 'TMP3'
1286 then rmr.BILL_HRS_A
1287 when invert.INVERT_ID = 'RMR'
1288 then -rmr.BILL_HRS_A
1289 end BILL_HRS_A,
1290 case when invert.INVERT_ID = 'TMP3'
1291 then rmr.CONF_HRS_S
1292 when invert.INVERT_ID = 'RMR'
1293 then -rmr.CONF_HRS_S
1294 end CONF_HRS_S,
1295 case when invert.INVERT_ID = 'TMP3'
1296 then rmr.PROV_HRS_S
1297 when invert.INVERT_ID = 'RMR'
1298 then -rmr.PROV_HRS_S
1299 end PROV_HRS_S,
1300 case when invert.INVERT_ID = 'TMP3'
1301 then rmr.UNASSIGNED_HRS_S
1302 when invert.INVERT_ID = 'RMR'
1303 then -rmr.UNASSIGNED_HRS_S
1304 end UNASSIGNED_HRS_S,
1305 case when invert.INVERT_ID = 'TMP3'
1306 then rmr.CONF_OVERCOM_HRS_S
1307 when invert.INVERT_ID = 'RMR'
1308 then -rmr.CONF_OVERCOM_HRS_S
1309 end CONF_OVERCOM_HRS_S,
1310 case when invert.INVERT_ID = 'TMP3'
1311 then rmr.PROV_OVERCOM_HRS_S
1312 when invert.INVERT_ID = 'RMR'
1313 then -rmr.PROV_OVERCOM_HRS_S
1314 end PROV_OVERCOM_HRS_S
1315 from
1316 PJI_RM_AGGR_RES3 tmp3,
1317 PJI_RM_RES_WT_F rmr,
1318 PJI_RES_DELTA delta,
1319 (
1320 select 'TMP3' INVERT_ID from dual union all
1321 select 'RMR' INVERT_ID from dual
1322 ) invert
1323 where
1324 l_extraction_type <> 'PARTIAL' and
1325 tmp3.WORKER_ID = p_worker_id and
1326 'A' = rmr.RECORD_TYPE and
1327 tmp3.PERSON_ID = rmr.PERSON_ID and
1328 tmp3.JOB_ID <> rmr.JOB_ID and
1329 tmp3.TIME_ID = rmr.TIME_ID and
1330 1 = rmr.PERIOD_TYPE_ID and
1331 tmp3.CALENDAR_TYPE = rmr.CALENDAR_TYPE and
1332 'N' = delta.CHANGE_TYPE (+) and
1333 rmr.PERSON_ID = delta.PERSON_ID (+) and
1334 rmr.TIME_ID >= to_number(to_char(delta.START_DATE (+), 'J')) and
1335 rmr.TIME_ID <= to_number(to_char(delta.END_DATE (+), 'J')) and
1336 delta.PERSON_ID is null
1337 union all -- JOB_ID lookups for assignments in tmp1
1338 select /*+ ordered index(tmp3, PJI_RM_AGGR_RES3_N1)
1339 index(rmr, PJI_RM_RES_WT_F_N2)
1340 parallel(rmr) */
1341 'A' RECORD_TYPE,
1342 tmp1.PROJECT_ID,
1343 tmp1.PERSON_ID,
1344 tmp1.EXPENDITURE_ORG_ID,
1345 tmp1.EXPENDITURE_ORGANIZATION_ID,
1346 tmp1.WORK_TYPE_ID,
1347 nvl(tmp3.JOB_ID, nvl(rmr.JOB_ID, -1)) JOB_ID,
1348 tmp1.TIME_ID,
1349 tmp1.PERIOD_TYPE_ID,
1350 tmp1.CALENDAR_TYPE,
1351 tmp1.GL_CALENDAR_ID,
1352 tmp1.PA_CALENDAR_ID,
1353 tmp1.CAPACITY_HRS,
1354 tmp1.TOTAL_HRS_A,
1355 tmp1.BILL_HRS_A,
1356 tmp1.CONF_HRS_S,
1357 tmp1.PROV_HRS_S,
1358 tmp1.UNASSIGNED_HRS_S,
1359 tmp1.CONF_OVERCOM_HRS_S,
1360 tmp1.PROV_OVERCOM_HRS_S
1361 from
1362 PJI_RM_AGGR_RES1 tmp1,
1363 PJI_RM_AGGR_RES3 tmp3,
1364 PJI_RM_RES_WT_F rmr
1365 where
1366 tmp1.WORKER_ID = p_worker_id and
1367 tmp1.RECORD_TYPE = 'N' and
1368 p_worker_id = tmp3.WORKER_ID (+) and
1369 tmp1.PERSON_ID = tmp3.PERSON_ID (+) and
1370 tmp1.TIME_ID = tmp3.TIME_ID (+) and
1371 tmp1.PERIOD_TYPE_ID = 1 and
1372 tmp1.CALENDAR_TYPE = tmp3.CALENDAR_TYPE (+) and
1373 'U' = rmr.RECORD_TYPE (+) and
1374 tmp1.PERSON_ID = rmr.PERSON_ID (+) and
1375 tmp1.EXPENDITURE_ORG_ID = rmr.EXPENDITURE_ORG_ID (+) and
1376 tmp1.EXPENDITURE_ORGANIZATION_ID
1377 = rmr.EXPENDITURE_ORGANIZATION_ID (+) and
1378 tmp1.TIME_ID = rmr.TIME_ID (+) and
1379 tmp1.PERIOD_TYPE_ID = rmr.PERIOD_TYPE_ID (+) and
1380 tmp1.CALENDAR_TYPE = rmr.CALENDAR_TYPE (+)
1381 union all -- reversals for deleted projects
1382 select /*+ ordered
1383 index(rmr, PJI_RM_RES_WT_F_N3)
1384 full(delta)
1385 full(info)
1386 */
1387 rmr.RECORD_TYPE,
1388 rmr.PROJECT_ID,
1389 rmr.PERSON_ID,
1390 rmr.EXPENDITURE_ORG_ID,
1391 rmr.EXPENDITURE_ORGANIZATION_ID,
1392 rmr.WORK_TYPE_ID,
1393 rmr.JOB_ID,
1394 rmr.TIME_ID,
1395 rmr.PERIOD_TYPE_ID,
1396 rmr.CALENDAR_TYPE,
1397 info.GL_CALENDAR_ID,
1398 info.PA_CALENDAR_ID,
1399 -rmr.CAPACITY_HRS,
1400 -rmr.TOTAL_HRS_A,
1401 -rmr.BILL_HRS_A,
1402 -rmr.CONF_HRS_S,
1403 -rmr.PROV_HRS_S,
1404 -rmr.UNASSIGNED_HRS_S,
1405 -rmr.CONF_OVERCOM_HRS_S,
1406 -rmr.PROV_OVERCOM_HRS_S
1407 from
1408 (
1409 select
1410 PROJECT_ID
1411 from
1412 (
1413 select /*+ index_ffs(rmr, PJI_RM_RES_WT_F_N3)
1414 parallel_index(rmr, PJI_RM_RES_WT_F_N3) */
1415 distinct
1416 PROJECT_ID
1417 from
1418 PJI_RM_RES_WT_F rmr
1419 where
1420 PROJECT_ID is not null and
1421 PROJECT_ID <> -1
1422 ) pji
1423 where
1424 not exists (select 1
1425 from PA_PROJECTS_ALL pa
1426 where pa.PROJECT_ID = pji.PROJECT_ID)
1427 ) prj,
1428 PJI_RM_RES_WT_F rmr,
1429 PJI_RES_DELTA delta,
1430 PJI_ORG_EXTR_INFO info
1431 where
1432 l_extraction_type = 'INCREMENTAL' and
1433 rmr.PROJECT_ID = prj.PROJECT_ID and
1434 rmr.PERIOD_TYPE_ID = 1 and
1435 rmr.TIME_ID between to_number(to_char(delta.START_DATE (+), 'J')) and
1436 to_number(to_char(delta.END_DATE (+), 'J')) and
1437 rmr.PERSON_ID = delta.PERSON_ID (+) and
1438 delta.PERSON_ID is null and
1439 rmr.EXPENDITURE_ORG_ID = info.ORG_ID
1440 )
1441 group by
1442 RECORD_TYPE,
1443 PROJECT_ID,
1444 PERSON_ID,
1445 EXPENDITURE_ORG_ID,
1446 EXPENDITURE_ORGANIZATION_ID,
1447 WORK_TYPE_ID,
1448 JOB_ID,
1449 TIME_ID,
1450 PERIOD_TYPE_ID,
1451 CALENDAR_TYPE,
1452 GL_CALENDAR_ID,
1453 PA_CALENDAR_ID;
1454
1455 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
1456 (
1457 l_process,
1458 'PJI_RM_SUM_EXTR.PROCESS_JOB_ID(p_worker_id);'
1459 );
1460
1461 commit;
1462
1463 end PROCESS_JOB_ID;
1464
1465
1466 -- -----------------------------------------------------
1467 -- procedure MARK_EXTRACTED_ROWS_PRE
1468 -- -----------------------------------------------------
1469 procedure MARK_EXTRACTED_ROWS_PRE
1470 (
1471 p_worker_id in number
1472 ) is
1473
1474 l_process varchar2(30);
1475
1476 begin
1477
1478 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1479
1480 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
1481 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_PRE(p_worker_id);')) then
1482 return;
1483 end if;
1484
1485 insert /*+ append */ into PJI_HELPER_BATCH_MAP
1486 (
1487 BATCH_ID,
1488 WORKER_ID,
1489 STATUS
1490 )
1491 select
1492 distinct
1493 BATCH_ID,
1494 null,
1495 null
1496 from
1497 PJI_RM_REXT_FCSTITEM
1498 where
1499 PJI_SUMMARIZED_FLAG is not null;
1500
1501 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process,
1502 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_PRE(p_worker_id);');
1503
1504 commit;
1505
1506 end MARK_EXTRACTED_ROWS_PRE;
1507
1508
1509 -- -----------------------------------------------------
1510 -- procedure MARK_EXTRACTED_ROWS
1511 -- -----------------------------------------------------
1512 procedure MARK_EXTRACTED_ROWS
1513 (
1514 p_worker_id in number
1515 ) is
1516
1517 l_process varchar2(30);
1518 l_last_update_date date;
1519 l_last_updated_by number;
1520 l_request_id number;
1521 l_program_appl_id number;
1522 l_program_id number;
1523 l_program_update_date date;
1524 l_helper_batch_id number;
1525 l_parallel_processes number;
1526 l_row_count number;
1527
1528 begin
1529
1530 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1531
1532 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
1533 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS(p_worker_id);')) then
1534 return;
1535 end if;
1536
1537 l_last_update_date := sysdate;
1538 l_last_updated_by := FND_GLOBAL.USER_ID;
1539 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1540 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
1541 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1542 l_program_update_date := sysdate;
1543 l_helper_batch_id := 0;
1544
1545 while (l_helper_batch_id <> -1) loop
1546
1547 update PJI_HELPER_BATCH_MAP
1548 set WORKER_ID = p_worker_id,
1549 STATUS = 'P'
1550 where WORKER_ID is null and
1551 ROWNUM = 1
1552 returning BATCH_ID
1553 into l_helper_batch_id;
1554
1555 if (sql%rowcount <> 0) then
1556
1557 commit;
1558
1559 update PA_FORECAST_ITEM_DETAILS
1560 set
1561 PJI_SUMMARIZED_FLAG = null,
1562 LAST_UPDATE_DATE = l_last_update_date,
1563 LAST_UPDATED_BY = l_last_updated_by,
1564 REQUEST_ID = l_request_id,
1565 PROGRAM_APPLICATION_ID = l_program_appl_id,
1566 PROGRAM_ID = l_program_id,
1567 PROGRAM_UPDATE_DATE = l_program_update_date
1568 where
1569 ROWID in
1570 (
1571 select /*+ cardinality(fcst, 1) */
1572 fcst.FID_ROWID
1573 from
1574 PJI_RM_REXT_FCSTITEM fcst
1575 where
1576 fcst.PJI_SUMMARIZED_FLAG = 'N' and
1577 fcst.BATCH_ID = l_helper_batch_id
1578 );
1579
1580 update PJI_HELPER_BATCH_MAP
1581 set STATUS = 'C'
1582 where WORKER_ID = p_worker_id and
1583 BATCH_ID = l_helper_batch_id;
1584
1585 commit;
1586
1587 else
1588
1589 select count(*)
1590 into l_row_count
1591 from PJI_HELPER_BATCH_MAP
1592 where nvl(STATUS, 'X') <> 'C';
1593
1594 if (l_row_count = 0) then
1595
1596 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
1597
1598 for x in 2 .. l_parallel_processes loop
1599
1600 update PJI_SYSTEM_PRC_STATUS
1601 set STEP_STATUS = 'C'
1602 where PROCESS_NAME like PJI_RM_SUM_MAIN.g_process|| to_char(x) and
1603 STEP_NAME = 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS(p_worker_id);' and
1604 START_DATE is null;
1605
1606 commit;
1607
1608 end loop;
1609
1610 l_helper_batch_id := -1;
1611
1612 end if;
1613
1614 end if;
1615
1616 end loop;
1617
1618 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process,
1619 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS(p_worker_id);');
1620
1621 commit;
1622
1623 end MARK_EXTRACTED_ROWS;
1624
1625
1626 -- -----------------------------------------------------
1627 -- procedure MARK_EXTRACTED_ROWS_POST
1628 -- -----------------------------------------------------
1629 procedure MARK_EXTRACTED_ROWS_POST
1630 (
1631 p_worker_id in number
1632 ) is
1633
1634 l_process varchar2(30);
1635
1636 begin
1637
1638 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1639
1640 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
1641 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_POST(p_worker_id);')) then
1642 return;
1643 end if;
1644
1645 PJI_PROCESS_UTIL.TRUNC_INT_TABLE('PJI',
1646 'PJI_HELPER_BATCH_MAP',
1647 'NORMAL',
1648 null);
1649
1650 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION (l_process,
1651 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_POST(p_worker_id);');
1652
1653 commit;
1654
1655 end MARK_EXTRACTED_ROWS_POST;
1656
1657
1658 -- -----------------------------------------------------
1659 -- procedure CLEANUP_WORKER
1660 -- -----------------------------------------------------
1661 procedure CLEANUP_WORKER
1662 (
1663 p_worker_id in number
1664 ) is
1665
1666 l_process varchar2(30);
1667 l_schema varchar2(30);
1668
1669 begin
1670
1671 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1672
1673 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1674
1675 PJI_PJ_PROJ_CLASS_EXTR.CLEANUP(p_worker_id);
1676 PJI_FM_PLAN_EXTR.CLEANUP(p_worker_id);
1677 PJI_RM_SUM_ROLLUP_RES.CLEANUP(p_worker_id);
1678
1679 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1680 'PJI_RM_REXT_FCSTITEM',
1681 'NORMAL',
1682 null);
1683
1684 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1685 'PJI_PJI_RMAP_RES',
1686 'NORMAL',
1687 null);
1688
1689 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1690 'PJI_PJI_RMAP_FIN',
1691 'NORMAL',
1692 null);
1693
1694 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1695 'PJI_PJI_RMAP_ACT',
1696 'NORMAL',
1697 null);
1698
1699 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1700 'PJI_RM_AGGR_RES3',
1701 'NORMAL',
1702 null);
1703
1704 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1705 'PJI_RES_DELTA',
1706 'NORMAL',
1707 null);
1708
1709 delete
1710 from PJI_FM_AGGR_DLY_RATES
1711 where WORKER_ID = -1;
1712
1713 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1714 'PJI_FM_RMAP_FIN',
1715 'NORMAL',
1716 null);
1717
1718 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1719 'PJI_FM_RMAP_ACT',
1720 'NORMAL',
1721 null);
1722
1723 commit;
1724
1725 end CLEANUP_WORKER;
1726
1727
1728 -- -----------------------------------------------------
1729 -- procedure WRAPUP_FAILURE
1730 -- -----------------------------------------------------
1731 procedure WRAPUP_FAILURE is
1732
1733 begin
1734
1735 rollback;
1736
1737 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process, 'PROCESS_RUNNING', 'F');
1738
1739 commit;
1740
1741 pji_utils.write2log(sqlerrm, true, 0);
1742
1743 commit;
1744
1745 end WRAPUP_FAILURE;
1746
1747
1748 -- -----------------------------------------------------
1749 -- procedure WORKER
1750 -- -----------------------------------------------------
1751 procedure WORKER (p_worker_id in number) is
1752
1753 begin
1754
1755 PJI_PJI_EXTRACTION_UTILS.SEED_PJI_RM_STATS;
1756
1757 PJI_PJI_EXTRACTION_UTILS.POPULATE_ORG_EXTR_INFO;
1758
1759 FND_STATS.GATHER_TABLE_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1760 tabname => 'PJI_ORG_EXTR_INFO',
1761 percent => 10,
1762 degree => PJI_UTILS.
1763 GET_DEGREE_OF_PARALLELISM);
1764 FND_STATS.GATHER_INDEX_STATS(ownname => PJI_UTILS.GET_PJI_SCHEMA_NAME,
1765 indname => 'PJI_ORG_EXTR_INFO_N1',
1766 percent => 10);
1767
1768 -- Reset status for Availability helper workers
1769 PJI_RM_SUM_AVL.UPDATE_RES_STATUS;
1770
1771 -- Populate Rolling Week Offset Table if it is not populated
1772 PJI_RM_SUM_AVL.POP_ROLL_WEEK_OFFSET;
1773
1774 PJI_PROCESS_UTIL.CLEAN_HELPER_BATCH_TABLE;
1775
1776 -- Procedure updates project classification dimension tables
1777 -- PJI_CLASS_CODES and PJI_CLASS_CATEGORIES. Data extraction is
1778 -- always incremental.
1779 PJI_PJ_PROJ_CLASS_EXTR.EXTR_CLASS_CODES;
1780
1781 -- Populates PJI_ORG_DENORM to be used with the Materialized views
1782 PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_ORG_HRCHY;
1783
1784 PJI_FM_PLAN_EXTR.UPDATE_PLAN_ORG_INFO(p_worker_id);
1785 PJI_FM_PLAN_EXTR.EXTRACT_PLAN_VERSIONS(p_worker_id);
1786 PJI_FM_PLAN_EXTR.EXTRACT_BATCH_PLAN(p_worker_id );
1787 PJI_FM_PLAN_EXTR.SPREAD_ENT_PLANS(p_worker_id);
1788 PJI_FM_PLAN_EXTR.PLAN_CURR_CONV_TABLE(p_worker_id);
1789 PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL_CURRENCY(p_worker_id);
1790 PJI_FM_PLAN_EXTR.CONVERT_TO_GLOBAL2_CURRENCY(p_worker_id);
1791 PJI_FM_PLAN_EXTR.CONVERT_TO_PA_PERIODS(p_worker_id);
1792 PJI_FM_PLAN_EXTR.CONVERT_TO_GL_PERIODS(p_worker_id);
1793 PJI_FM_PLAN_EXTR.CONVERT_TO_ENT_PERIODS(p_worker_id);
1794 PJI_FM_PLAN_EXTR.CONVERT_TO_ENTW_PERIODS(p_worker_id);
1795 PJI_FM_PLAN_EXTR.DANGLING_PLAN_VERSIONS(p_worker_id);
1796 PJI_FM_PLAN_EXTR.SUMMARIZE_EXTRACT(p_worker_id);
1797 PJI_FM_PLAN_EXTR.EXTRACT_UPDATED_VERSIONS(p_worker_id);
1798
1799 PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);
1800 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1801 (PJI_RM_SUM_MAIN.g_process,
1802 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);',
1803 PJI_RM_SUM_MAIN.g_process_delay)) then
1804 return;
1805 end if;
1806 PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_POST(p_worker_id);
1807 PJI_FM_PLAN_EXTR.UPDATE_BATCH_STATUSES(p_worker_id);
1808
1809 PJI_PJ_PROJ_CLASS_EXTR.EXTR_PROJECT_CLASSES(p_worker_id);
1810
1811 PJI_RM_SUM_EXTR.PROCESS_DANGLING_ROWS(p_worker_id);
1812 PJI_RM_SUM_EXTR.PURGE_DANGLING_ROWS(p_worker_id);
1813 PJI_RM_SUM_ROLLUP_RES.JOB_NONUTIL2UTIL(p_worker_id);
1814 PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_ROWIDS(p_worker_id);
1815
1816 PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_PRE(p_worker_id);
1817 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1818 (PJI_RM_SUM_MAIN.g_process,
1819 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS(p_worker_id);',
1820 PJI_RM_SUM_MAIN.g_process_delay)) then
1821 return;
1822 end if;
1823 PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_POST(p_worker_id);
1824
1825 PJI_RM_SUM_EXTR.RES_ROWID_TABLE(p_worker_id);
1826 PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID_FULL(p_worker_id);
1827 PJI_RM_SUM_EXTR.EXTRACT_BATCH_FID(p_worker_id);
1828 PJI_RM_SUM_EXTR.MOVE_DANGLING_ROWS(p_worker_id);
1829 PJI_RM_SUM_EXTR.PURGE_RES_DATA(p_worker_id);
1830 PJI_RM_SUM_EXTR.GET_JOB_ID_LOOKUPS(p_worker_id);
1831 PJI_RM_SUM_EXTR.PROCESS_JOB_ID(p_worker_id);
1832
1833 PJI_RM_SUM_ROLLUP_RES.CALC_RMS_AVL_AND_WT(p_worker_id);
1834
1835 PJI_RM_SUM_AVL.INS_INTO_RES_STATUS(p_worker_id);
1836 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1837 (PJI_RM_SUM_MAIN.g_process,
1838 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);',
1839 PJI_RM_SUM_MAIN.g_process_delay)) then
1840 return;
1841 end if;
1842
1843 PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_FIN(p_worker_id);
1844 PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_FIN(p_worker_id);
1845 PJI_FM_SUM_BKLG.ROWID_ACTIVITY_DATES_ACT(p_worker_id);
1846 PJI_FM_SUM_BKLG.UPDATE_ACTIVITY_DATES_ACT(p_worker_id);
1847
1848 PJI_FM_SUM_ROLLUP_FIN.FIN_ROWID_TABLE(p_worker_id);
1849 PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_WT_SLICES(p_worker_id);
1850 PJI_FM_SUM_ROLLUP_FIN.PURGE_FIN_DATA(p_worker_id);
1851 PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_ET_SLICES(p_worker_id);
1852 PJI_FM_SUM_ROLLUP_FIN.AGGREGATE_FIN_SLICES(p_worker_id);
1853
1854 PJI_FM_SUM_ROLLUP_ACT.ACT_ROWID_TABLE(p_worker_id);
1855 PJI_FM_SUM_ROLLUP_ACT.AGGREGATE_ACT_SLICES(p_worker_id);
1856 PJI_FM_SUM_ROLLUP_ACT.PURGE_ACT_DATA(p_worker_id);
1857
1858 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_EN(p_worker_id);
1859 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_PA(p_worker_id);
1860 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_GL(p_worker_id);
1861 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMR_CAL_WK(p_worker_id);
1862 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_EN(p_worker_id);
1863 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_PA(p_worker_id);
1864 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_GL(p_worker_id);
1865 PJI_RM_SUM_ROLLUP_RES.EXPAND_RMS_CAL_WK(p_worker_id);
1866
1867 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_EN(p_worker_id);
1868 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_PA(p_worker_id);
1869 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_GL(p_worker_id);
1870 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPW_CAL_WK(p_worker_id);
1871 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_EN(p_worker_id);
1872 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_PA(p_worker_id);
1873 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_GL(p_worker_id);
1874 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPE_CAL_WK(p_worker_id);
1875 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_EN(p_worker_id);
1876 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_PA(p_worker_id);
1877 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_GL(p_worker_id);
1878 PJI_FM_SUM_ROLLUP_FIN.EXPAND_FPP_CAL_WK(p_worker_id);
1879
1880 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_EN(p_worker_id, 'N');
1881 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_PA(p_worker_id, 'N');
1882 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_GL(p_worker_id, 'N');
1883 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_WK(p_worker_id, 'N');
1884
1885 PJI_RM_SUM_ROLLUP_RES.MERGE_TMP1_INTO_RMR(p_worker_id);
1886 PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMR(p_worker_id);
1887 PJI_RM_SUM_ROLLUP_RES.MERGE_TMP2_INTO_RMS(p_worker_id);
1888 PJI_RM_SUM_ROLLUP_RES.CLEANUP_RMS(p_worker_id);
1889
1890 PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);
1891 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1892 (PJI_RM_SUM_MAIN.g_process,
1893 'PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);',
1894 PJI_RM_SUM_MAIN.g_process_delay)) then
1895 return;
1896 end if;
1897 PJI_RM_SUM_AVL.MERGE_ORG_AVL_DUR(p_worker_id);
1898 PJI_RM_SUM_AVL.MERGE_CURR_ORG_AVL(p_worker_id);
1899 PJI_RM_SUM_AVL.RES_CALC_CLEANUP(p_worker_id);
1900
1901 PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPW(p_worker_id);
1902 PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPE(p_worker_id);
1903 PJI_FM_SUM_ROLLUP_FIN.MERGE_FIN_INTO_FPP(p_worker_id);
1904 PJI_FM_SUM_ROLLUP_ACT.MERGE_ACT_INTO_ACP(p_worker_id, 'N');
1905
1906 PJI_FM_SUM_BKLG.SCOPE_PROJECTS_BKLG(p_worker_id);
1907 PJI_FM_SUM_BKLG.CLEANUP_INT_TABLE(p_worker_id);
1908 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1909 (PJI_RM_SUM_MAIN.g_process,
1910 'PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);',
1911 PJI_RM_SUM_MAIN.g_process_delay)) then
1912 return;
1913 end if;
1914
1915 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_EN(p_worker_id, 'Y');
1916 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_PA(p_worker_id, 'Y');
1917 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_GL(p_worker_id, 'Y');
1918 PJI_FM_SUM_ROLLUP_ACT.EXPAND_ACT_CAL_WK(p_worker_id, 'Y');
1919
1920 PJI_FM_SUM_ROLLUP_ACT.MERGE_ACT_INTO_ACP(p_worker_id, 'Y');
1921
1922 PJI_FM_SUM_ROLLUP_FIN.PROJECT_ORGANIZATION(p_worker_id);
1923 PJI_FM_SUM_ROLLUP_ACT.PROJECT_ORGANIZATION(p_worker_id);
1924
1925 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTW(p_worker_id);
1926 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTX(p_worker_id);
1927 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_UTJ(p_worker_id);
1928
1929 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME(p_worker_id);
1930 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_DAY(p_worker_id);
1931 PJI_RM_SUM_ROLLUP_RES.REFRESH_MVIEW_TIME_TREND(p_worker_id);
1932
1933 PJI_RM_SUM_AVL.REFRESH_AV_ORGO_F_MV(p_worker_id);
1934 PJI_RM_SUM_AVL.REFRESH_CA_ORGO_F_MV(p_worker_id);
1935
1936 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWO(p_worker_id);
1937 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FWC(p_worker_id);
1938 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEO(p_worker_id);
1939 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FEC(p_worker_id);
1940 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPO(p_worker_id);
1941 PJI_FM_SUM_ROLLUP_FIN.REFRESH_MVIEW_FPC(p_worker_id);
1942 PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACO(p_worker_id);
1943 PJI_FM_SUM_ROLLUP_ACT.REFRESH_MVIEW_ACC(p_worker_id);
1944
1945 CLEANUP_WORKER(p_worker_id);
1946
1947 end WORKER;
1948
1949
1950 -- -----------------------------------------------------
1951 -- procedure HELPER
1952 -- -----------------------------------------------------
1953 procedure HELPER
1954 (
1955 errbuf out nocopy varchar2,
1956 retcode out nocopy varchar2,
1957 p_worker_id in number
1958 ) is
1959
1960 l_process varchar2(30);
1961
1962 begin
1963
1964 -- If this helper's concurrent request ID does not exist in the
1965 -- parameters table, the dispatcher must have kicked off a new
1966 -- helper. Therefore do nothing.
1967 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process,
1968 PJI_RM_SUM_MAIN.g_process ||
1969 to_char(p_worker_id))
1970 <> FND_GLOBAL.CONC_REQUEST_ID) then
1971 pji_utils.write2log('Warning: Helper is already running.');
1972 commit;
1973 retcode := 0;
1974 return;
1975 end if;
1976
1977 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
1978
1979 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1980 (PJI_RM_SUM_MAIN.g_process,
1981 'PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS_PRE(p_worker_id);',
1982 PJI_RM_SUM_MAIN.g_process_delay,
1983 'EVEN_IF_NOT_EXISTS')) then
1984 return;
1985 end if;
1986
1987 PJI_FM_PLAN_EXTR.UPDATE_BATCH_VERSIONS(p_worker_id);
1988
1989 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1990 (PJI_RM_SUM_MAIN.g_process,
1991 'PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS_PRE(p_worker_id);',
1992 PJI_RM_SUM_MAIN.g_process_delay)) then
1993 return;
1994 end if;
1995
1996 PJI_RM_SUM_EXTR.MARK_EXTRACTED_ROWS(p_worker_id);
1997
1998 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
1999 (PJI_RM_SUM_MAIN.g_process,
2000 'PJI_RM_SUM_AVL.INS_INTO_RES_STATUS(p_worker_id);',
2001 PJI_RM_SUM_MAIN.g_process_delay)) then
2002 return;
2003 end if;
2004
2005 PJI_RM_SUM_AVL.START_RES_AVL_CALC_R1(p_worker_id);
2006
2007 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
2008 (PJI_RM_SUM_MAIN.g_process,
2009 'PJI_RM_SUM_AVL.UPDATE_RES_STA_FOR_RUN2(p_worker_id);',
2010 PJI_RM_SUM_MAIN.g_process_delay)) then
2011 return;
2012 end if;
2013
2014 PJI_RM_SUM_AVL.START_RES_AVL_CALC_R2(p_worker_id);
2015
2016 if (not PJI_PROCESS_UTIL.WAIT_FOR_STEP
2017 (PJI_RM_SUM_MAIN.g_process,
2018 'PJI_FM_SUM_BKLG.CLEANUP_INT_TABLE(p_worker_id);',
2019 PJI_RM_SUM_MAIN.g_process_delay)) then
2020 return;
2021 end if;
2022
2023 PJI_FM_SUM_BKLG.PROCESS_DRMT_BKLG(p_worker_id);
2024
2025 while (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2026 (PJI_RM_SUM_MAIN.g_process, 'PROCESS_RUNNING') = 'Y') loop
2027 PJI_PROCESS_UTIL.SLEEP(PJI_RM_SUM_MAIN.g_process_delay);
2028 end loop;
2029
2030 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2031 (PJI_RM_SUM_MAIN.g_process, 'PROCESS_RUNNING') = 'F') then
2032 return;
2033 end if;
2034
2035 PJI_PROCESS_UTIL.WRAPUP_PROCESS(l_process);
2036
2037 commit;
2038
2039 retcode := 0;
2040
2041 exception when others then
2042
2043 WRAPUP_FAILURE;
2044 retcode := 2;
2045 errbuf := sqlerrm;
2046
2047 end HELPER;
2048
2049
2050 -- -----------------------------------------------------
2051 -- procedure START_HELPER
2052 -- -----------------------------------------------------
2053 procedure START_HELPER
2054 (
2055 p_worker_id in number
2056 ) is
2057
2058 l_process varchar2(30);
2059 l_extraction_type varchar2(30);
2060
2061 begin
2062
2063 -- If a helper with this concurrent request ID is already running
2064 -- then we do not need to do anything.
2065 if (WORKER_STATUS(p_worker_id, 'RUNNING')) then
2066 return;
2067 end if;
2068
2069 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2070
2071 -- Initialize status tables with worker details
2072
2073 -- Note that adding a new step will do nothing if the step already
2074 -- exists. Therefore, no state will be overwritten in the case of
2075 -- error recovery.
2076
2077 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2078 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2079
2080 PJI_PROCESS_UTIL.ADD_STEPS(l_process, 'PJI_PJI_HELPER', l_extraction_type);
2081
2082 -- Kick off worker
2083
2084 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
2085 (
2086 PJI_RM_SUM_MAIN.g_process,
2087 l_process,
2088 FND_REQUEST.SUBMIT_REQUEST
2089 (
2090 PJI_UTILS.GET_PJI_SCHEMA_NAME, -- Application name
2091 PJI_RM_SUM_MAIN.g_helper_name, -- concurrent program name
2092 null, -- description (optional)
2093 null, -- Start Time (optional)
2094 false, -- called from another conc. request
2095 p_worker_id -- first parameter
2096 )
2097 );
2098
2099 if (nvl(to_number(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2100 (PJI_RM_SUM_MAIN.g_process, l_process)), 0) = 0) then
2101 fnd_message.set_name('PJI', 'PJI_SUM_NO_SUB_REQ');
2102 dbms_standard.raise_application_error(-20030, fnd_message.get);
2103 end if;
2104
2105 commit;
2106
2107 end START_HELPER;
2108
2109
2110 -- -----------------------------------------------------
2111 -- function WORKER_STATUS
2112 -- -----------------------------------------------------
2113 function WORKER_STATUS (p_worker_id in number,
2114 p_mode in varchar2) return boolean is
2115
2116 l_process varchar2(30);
2117 l_request_id number;
2118 l_worker_name varchar2(255);
2119 l_extraction_type varchar2(30);
2120
2121 begin
2122
2123 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2124 (PJI_RM_SUM_MAIN.g_process, 'EXTRACTION_TYPE');
2125
2126 if (p_worker_id = 1) then
2127
2128 if (l_extraction_type = 'FULL') then
2129 l_worker_name := PJI_RM_SUM_MAIN.g_full_disp_name;
2130 elsif (l_extraction_type = 'INCREMENTAL') then
2131 l_worker_name := PJI_RM_SUM_MAIN.g_incr_disp_name;
2132 elsif (l_extraction_type = 'PARTIAL') then
2133 l_worker_name := PJI_RM_SUM_MAIN.g_prtl_disp_name;
2134 end if;
2135
2136 l_request_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2137 (PJI_RM_SUM_MAIN.g_process,
2138 PJI_RM_SUM_MAIN.g_process);
2139
2140 else
2141
2142 l_process := PJI_RM_SUM_MAIN.g_process || p_worker_id;
2143
2144 l_worker_name := PJI_RM_SUM_MAIN.g_helper_name;
2145
2146 l_request_id := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2147 (PJI_RM_SUM_MAIN.g_process, l_process);
2148
2149 end if;
2150
2151 return PJI_PROCESS_UTIL.REQUEST_STATUS(p_mode,
2152 l_request_id,
2153 l_worker_name);
2154
2155 end WORKER_STATUS;
2156
2157
2158 -- -----------------------------------------------------
2159 -- procedure WAIT_FOR_WORKER
2160 -- -----------------------------------------------------
2161 procedure WAIT_FOR_WORKER (p_worker_id in number) is
2162
2163 l_process varchar2(30);
2164 l_request_id number;
2165
2166 begin
2167
2168 l_process := PJI_RM_SUM_MAIN.g_process || to_char(p_worker_id);
2169
2170 l_request_id :=
2171 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
2172 (
2173 PJI_RM_SUM_MAIN.g_process,
2174 l_process
2175 );
2176
2177 PJI_PROCESS_UTIL.WAIT_FOR_REQUEST(l_request_id,
2178 PJI_RM_SUM_MAIN.g_process_delay);
2179
2180 end WAIT_FOR_WORKER;
2181
2182 end PJI_RM_SUM_EXTR;