[Home] [Help]
PACKAGE BODY: APPS.PJI_PJI_EXTRACTION_UTILS
Source
1 PACKAGE BODY PJI_PJI_EXTRACTION_UTILS as
2 /* $Header: PJIUT07B.pls 120.3 2007/01/25 00:45:54 degupta ship $ */
3
4 -- -------------------------------------
5 -- procedure UPDATE_PJI_EXTR_SCOPE
6 -- -------------------------------------
7 procedure UPDATE_PJI_EXTR_SCOPE is
8
9 l_count number;
10
11 l_last_update_date date;
12 l_last_updated_by number;
13 l_creation_date date;
14 l_created_by number;
15 l_last_update_login number;
16
17 begin
18
19 l_last_update_date := sysdate;
20 l_last_updated_by := FND_GLOBAL.USER_ID;
21 l_creation_date := sysdate;
22 l_created_by := FND_GLOBAL.USER_ID;
23 l_last_update_login := FND_GLOBAL.LOGIN_ID;
24
25 select count(*)
26 into l_count
27 from PJI_PJI_PROJ_EXTR_STATUS
28 where ROWNUM = 1;
29
30 if (l_count > 0) then
31
32 insert into PJI_PJI_PROJ_EXTR_STATUS
33 (
34 PROJECT_ID,
35 PROJECT_ORGANIZATION_ID,
36 PROJECT_NAME,
37 PROJECT_TYPE_CLASS,
38 EXTRACTION_STATUS,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_LOGIN
44 )
45 select
46 prj.PROJECT_ID,
47 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
48 'PJI$NULL' PROJECT_NAME,
49 decode(pt.PROJECT_TYPE_CLASS_CODE,
50 'CAPITAL', 'C',
51 'CONTRACT', 'B',
52 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
53 'F' EXTRACTION_STATUS,
54 l_last_update_date,
55 l_last_updated_by,
56 l_creation_date,
57 l_created_by,
58 l_last_update_login
59 from
60 PA_PROJECTS_ALL prj,
61 PA_PROJECT_TYPES_ALL pt,
62 PJI_PJI_PROJ_EXTR_STATUS pji_status
63 where
64 prj.TEMPLATE_FLAG = 'N' and
65 prj.ORG_ID = pt.ORG_ID and /*5377131*/
66 prj.PROJECT_TYPE = pt.PROJECT_TYPE and
67 prj.PROJECT_ID = pji_status.PROJECT_ID (+) and
68 pji_status.PROJECT_ID is null;
69
70 else
71
72 insert into PJI_PJI_PROJ_EXTR_STATUS pjp_i
73 (
74 PROJECT_ID,
75 PROJECT_ORGANIZATION_ID,
76 PROJECT_NAME,
77 PROJECT_TYPE_CLASS,
78 EXTRACTION_STATUS,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_LOGIN
84 )
85 select
86 prj.PROJECT_ID,
87 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
88 'PJI$NULL' PROJECT_NAME,
89 decode(pt.PROJECT_TYPE_CLASS_CODE,
90 'CAPITAL', 'C',
91 'CONTRACT', 'B',
92 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
93 'F' EXTRACTION_STATUS,
94 l_last_update_date,
95 l_last_updated_by,
96 l_creation_date,
97 l_created_by,
98 l_last_update_login
99 from
100 PA_PROJECTS_ALL prj,
101 PA_PROJECT_TYPES_ALL pt
102 where
103 prj.TEMPLATE_FLAG = 'N' and
104 prj.ORG_ID = pt.ORG_ID and /*5377131*/
105 prj.PROJECT_TYPE = pt.PROJECT_TYPE;
106
107 end if;
108
109 end UPDATE_PJI_EXTR_SCOPE;
110
111
112 -- ----------------------------------------------------------
113 -- procedure POPULATE_ORG_EXTR_INFO
114 -- ----------------------------------------------------------
115 procedure POPULATE_ORG_EXTR_INFO is
116
117 begin
118
119 PJI_EXTRACTION_UTIL.POPULATE_ORG_EXTR_INFO;
120
121 end POPULATE_ORG_EXTR_INFO;
122
123
124 -- ----------------------------------------------------------
125 -- procedure UPDATE_ORG_EXTR_INFO
126 -- ----------------------------------------------------------
127 procedure UPDATE_ORG_EXTR_INFO is
128
129 begin
130
131 PJI_EXTRACTION_UTIL.UPDATE_ORG_EXTR_INFO;
132
133 end UPDATE_ORG_EXTR_INFO;
134
135
136 -- ------------------------------------------------------
137 -- procedure MVIEW_REFRESH( p_name )
138 -- ------------------------------------------------------
139 procedure MVIEW_REFRESH
140 (
141 errbuf out nocopy varchar2
142 , retcode out nocopy varchar2
143 , p_name in varchar2 default 'All'
144 , p_method in varchar2 default 'C'
145 , p_refresh_mview_lookups in varchar2 default 'Y'
146 ) is
147
148 l_chk number := 0;
149
150 cursor cur_mv is
151 SELECT
152 level_1.owner owner
153 , level_1.name mv_name
154 , level_1.mview_id mv_id
155 , max(decode(level_1.ord_bod1
156 , 1, decode(bod2.DEPEND_OBJECT_TYPE
157 , 'MV', 2
158 , 1)
159 , 0)) ord_bod2
160 FROM
161 (
162 select
163 rmv.OWNER
164 , rmv.NAME
165 , rmv.MVIEW_ID
166 , bod1.DEPEND_OBJECT_NAME prnt1
167 , decode(bod1.DEPEND_OBJECT_TYPE
168 , 'MV' , 1
169 , 0) ord_bod1
170 from DBA_REGISTERED_MVIEWS rmv
171 , BIS_OBJ_DEPENDENCY bod1
172 where 1=1
173 and rmv.NAME like 'PJI%'
174 and bod1.OBJECT_TYPE (+) = 'MV'
175 and rmv.NAME = bod1.OBJECT_NAME (+)
176 ) level_1
177 , BIS_OBJ_DEPENDENCY bod2
178 WHERE 1=1
179 AND decode(level_1.ord_bod1
180 , 1, level_1.prnt1
181 , level_1.name ) = bod2.OBJECT_NAME (+)
182 --and level_1.name = 'PJI_FP_ORGO_F_MV'
183 group by level_1.owner
184 , level_1.name
185 , level_1.mview_id
186 order by 4,3
187 ;
188
189
190 cur_mv_rec cur_mv%ROWTYPE;
191
192 begin
193
194 /*
195 * Update tables on which only PJI mviews rely. This way if massive
196 * changes take place in these tables we can run a full refresh on the
197 * materialized views rather than an incremental refresh.
198 *
199 */
200
201 if (p_refresh_mview_lookups = 'Y') then
202 PJI_PJ_PROJ_CLASS_EXTR.EXTR_CLASS_CODES;
203 PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_ORG_HRCHY;
204 end if;
205
206 commit; -- we need to end any transactions before altering parallel DML
207
208 IF (upper(p_name) <> 'ALL') THEN
209
210 BIS_MV_REFRESH.REFRESH_WRAPPER(p_name, p_method);
211
212 ELSE
213
214 IF cur_mv%ISOPEN then
215 CLOSE cur_mv;
216 END IF;
217
218 For cur_mv_rec in cur_mv LOOP
219 BIS_MV_REFRESH.REFRESH_WRAPPER(cur_mv_rec.owner ||'.'||
220 cur_mv_rec.mv_name,
221 p_method);
222 End LOOP;
223
224 END IF;
225
226 -- bis utility disables parallel query, but pji summarization always
227 -- uses parallel query
228 execute immediate 'alter session enable parallel query';
229
230 retcode := 0;
231 exception when others then
232 retcode := 2;
233 errbuf := sqlerrm;
234 PJI_UTILS.write2log('PJI_PJI_EXTRACTION_UTILS.mview_refresh '||sqlerrm);
235 PJI_UTILS.write2out('PJI_PJI_EXTRACTION_UTILS.mview_refresh '||sqlerrm);
236 raise;
237 end MVIEW_REFRESH;
238
239
240 -- ------------------------------------------------------
241 -- procedure ANALYZE_PJI_FACTS
242 -- ------------------------------------------------------
243 procedure ANALYZE_PJI_FACTS
244 is
245
246 l_schema varchar2(30);
247 l_degree number;
248
249
250
251 begin
252
253 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
254 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
255
256 FND_STATS.GATHER_TABLE_STATS(
257 ownname => l_schema
258 , tabname => 'PJI_FP_PROJ_ET_WT_F'
259 , percent => 10
260 , degree => l_degree
261 );
262 FND_STATS.GATHER_TABLE_STATS(
263 ownname => l_schema
264 , tabname => 'PJI_FP_PROJ_ET_F'
265 , percent => 10
266 , degree => l_degree
267 );
268 FND_STATS.GATHER_TABLE_STATS(
269 ownname => l_schema
270 , tabname => 'PJI_FP_PROJ_F'
271 , percent => 10
272 , degree => l_degree
273 );
274 FND_STATS.GATHER_TABLE_STATS(
275 ownname => l_schema
276 , tabname => 'PJI_AC_PROJ_F'
277 , percent => 10
278 , degree => l_degree
279 );
280 FND_STATS.GATHER_TABLE_STATS(
281 ownname => l_schema
282 , tabname => 'PJI_RM_RES_F'
283 , percent => 10
284 , degree => l_degree
285 );
286 FND_STATS.GATHER_TABLE_STATS(
287 ownname => l_schema
288 , tabname => 'PJI_RM_RES_WT_F'
289 , percent => 10
290 , degree => l_degree
291 );
292 FND_STATS.GATHER_TABLE_STATS(
293 ownname => l_schema
294 , tabname => 'PJI_FP_TXN_ACCUM_HEADER'
295 , percent => 10
296 , degree => l_degree
297 );
298 FND_STATS.GATHER_TABLE_STATS(
299 ownname => l_schema
300 , tabname => 'PJI_FP_TXN_ACCUM'
301 , percent => 10
302 , degree => l_degree
303 );
304
305 end ANALYZE_PJI_FACTS;
306
307
308 /* ------------------------------------------------------
309 Procedure : SEED_PJI_RM_STATS
310 -----------------------------------------------------*/
311
312 PROCEDURE SEED_PJI_RM_STATS IS
313
314 l_high_rows number;
315 l_med_rows number;
316 l_low_rows number;
317 l_db_block_size number;
318 l_high_blocks number;
319 l_med_blocks number;
320 l_low_blocks number;
321
322 l_schema varchar2(30);
323 l_degree number;
324
325 BEGIN
326 /* This procedure sets statistics for all PJI_RM intermediate tables
327 *
328 * Presently this procedure sets statistics for only
329 * the first partition for partitioned tables since
330 * there will be only one worker for Phase I. Later when
331 * this restriction is removed then the statistics need to
332 * be set for other partitions too.
333 *
334 * The tables are divided into 3 broad categories: high medium and low
335 * The statistics seeded are based on 3 sets of parameters:
336 * Number of rows Average row length
337 * High batch_size 225
338 * Medium batch_size/50 150
339 * Low batch_size/150 75
340 *
341 * Blocks = 1.5* (number of rows * average row length)/block size
342 *
343 * A factor of 1.5 is assumed for row chaining into multiple blocks
344 *
345 */
346
347 l_high_rows := 10000000;
348 l_med_rows := l_high_rows/50;
349 l_low_rows := l_high_rows/150;
350
351 select to_number(value)
352 into l_db_block_size
353 from v$parameter
354 where name = 'db_block_size'
355 ;
356
357 l_high_blocks := 1.25*(l_high_rows*225)/l_db_block_size;
358 l_med_blocks := 1.25*(l_med_rows*150)/l_db_block_size;
359 l_low_blocks := 1.25*(l_low_rows*75)/l_db_block_size;
360
361 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
362 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
363
364 -- partitioned tables
365 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150, 'P0' );
366 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150, 'P1' );
367 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150, 'P2' );
368 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150, 'P3' );
369 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150, 'P4' );
370
371 -- non-partitioned tables
372 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_RMAP_ACT' , l_low_rows, l_low_blocks, 75);
373 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT3' , l_high_rows, l_high_blocks, 225);
374 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN3' , l_high_rows, l_high_blocks, 225);
375 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN4' , l_high_rows, l_high_blocks, 225);
376 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN5' , l_high_rows, l_high_blocks, 225);
377 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_PLN' , l_med_rows, l_med_blocks, 150);
378 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN' , l_med_rows, l_med_blocks, 150);
379 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_RMAP_FIN' , l_low_rows, l_low_blocks, 75);
380 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_PJ_EXTR_PRJCLS' , l_low_rows, l_low_blocks, 75);
381 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_CLASS_CATEGORIES' , l_low_rows, l_low_blocks, 75);
382 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_CLASS_CODES' , l_low_rows, l_low_blocks, 75);
383 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_PLN_LOG' , l_low_rows, l_low_blocks, 75);
384 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_RM_REXT_FCSTITEM' , l_high_rows, l_high_blocks, 225);
385 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_RM_AGGR_RES1' , l_high_rows, l_high_blocks, 225);
386 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_RM_AGGR_RES2' , l_high_rows, l_high_blocks, 225);
387 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_RM_AGGR_RES3' , l_high_rows, l_high_blocks, 225);
388 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_RM_ORG_BATCH_MAP' , l_low_rows, l_low_blocks, 75);
389
390 -- global temporary tables
391 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_ROWID_ORG_DENORM' , l_low_rows, l_low_blocks, 75);
392
393 -- gather statistics for PJI metadata tables
394 FND_STATS.GATHER_TABLE_STATS(
395 ownname => l_schema
396 , tabname => 'PJI_ORG_DENORM'
397 , percent => 50
398 , degree => l_degree
399 );
400 FND_STATS.GATHER_TABLE_STATS(
401 ownname => l_schema
402 , tabname => 'PJI_ORG_EXTR_INFO'
403 , percent => 50
404 , degree => l_degree
405 );
406 FND_STATS.GATHER_TABLE_STATS(
407 ownname => l_schema
408 , tabname => 'PJI_ORG_EXTR_STATUS'
409 , percent => 50
410 , degree => l_degree
411 );
412 FND_STATS.GATHER_TABLE_STATS(
413 ownname => l_schema
414 , tabname => 'PJI_RM_WORK_TYPE_INFO'
415 , percent => 50
416 , degree => l_degree
417 );
418 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
419 tabname => 'PJI_RM_WORK_TYPE_INFO',
420 colname => 'WORK_TYPE_ID',
421 percent => 10,
422 degree => l_degree);
423 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
424 tabname => 'PJI_RM_WORK_TYPE_INFO',
425 colname => 'RECORD_TYPE',
426 percent => 10,
427 degree => l_degree);
428 FND_STATS.GATHER_TABLE_STATS(
429 ownname => l_schema
430 , tabname => 'PJI_RESOURCES_DENORM'
431 , percent => 10
432 , degree => l_degree
433 );
434 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
435 tabname => 'PJI_ORG_EXTR_STATUS',
436 percent => 10,
437 degree => l_degree);
438 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
439 tabname => 'PJI_ORG_EXTR_STATUS',
440 colname => 'ORGANIZATION_ID',
441 percent => 10,
442 degree => l_degree);
443 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
444 tabname => 'PJI_ORG_EXTR_STATUS',
445 colname => 'STATUS',
446 percent => 10,
447 degree => l_degree);
448 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
449 tabname => 'PJI_RM_ORG_BATCH_MAP',
450 percent => 10,
451 degree => l_degree);
452 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
453 tabname => 'PJI_RM_ORG_BATCH_MAP',
454 colname => 'ORGANIZATION_ID',
455 percent => 10,
456 degree => l_degree);
457 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
458 tabname => 'PJI_RM_ORG_BATCH_MAP',
459 colname => 'EXTRACTION_TYPE',
460 percent => 10,
461 degree => l_degree);
462 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
463 tabname => 'PJI_PROJECT_CLASSES',
464 percent => 10,
465 degree => l_degree); --Bug#4997700
466 END SEED_PJI_RM_STATS;
467
468
469 -- ------------------------------------------------------------------
470 -- procedure UPDATE_PJI_RM_WORK_TYPE_INFO
471 --
472 -- This procedure maintains the table PJI_RM_WORK_TYPE_INFO
473 -- This table contains 3 slices of data which can be distinguished
474 -- by the value in RECORD_TYPE column
475 -- RECORD_TYPE column can take 3 values
476 -- NORMAL - This slice is a copy of PA_WORK_TYPE _B table;
477 -- the slice is maintained incrementally,
478 -- i.e. we never delete records from this slice
479 -- CHANGE_OLD - This slice is used for processing changes in
480 -- work type attributes. It stores the old version of
481 -- changed work type record
482 -- CHANGE_NEW - This slice is used for processing changes in
483 -- work type attributes. It stores the new version of
484 -- changed work type record. Records in this slice will
485 -- be copies of NORMAL slice records for which CHANGE_OLD
486 -- record exists
487 -- ------------------------------------------------------------------
488 procedure UPDATE_PJI_RM_WORK_TYPE_INFO (p_process in varchar2) is
489
490 l_row_count number;
491 l_extraction_type varchar2(30);
492 l_event_id number;
493
494 begin
495
496 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(p_process);')) then
497 return;
498 end if;
499
500 select count(*)
501 into l_row_count
502 from PJI_RM_WORK_TYPE_INFO
503 where ROWNUM = 1;
504
505 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
506 'TRANSITION') = 'Y' and
507 l_row_count <> 0) then
508 return;
509 end if;
510
511 --delete CHANGE_NEW / CHANGE_OLD records
512 delete
513 from PJI_RM_WORK_TYPE_INFO
514 where RECORD_TYPE in ( 'CHANGE_NEW', 'CHANGE_OLD');
515
516 --Conditional processing based on extraction type
517 --Work type change processing is not done for
518 --Partial refresh
519 l_extraction_type := PJI_UTILS.get_parameter (
520 p_name => 'EXTRACTION_TYPE');
521
522 IF l_extraction_type = 'PARTIAL' THEN
523 return;
524 END IF;
525
526 --Synchronize NORMAL slice of PJI_RM_WORK_TYPE_INFO
527 --with PA_WORK_TYPES_B when extraction type is FULL
528 --or INCREMENTAL
529 insert into PJI_RM_WORK_TYPE_ROWID
530 (
531 PA_ROWID,
532 PJI_ROWID,
533 CHANGE_FLAG
534 )
535 select
536 pa.ROWID,
537 pji.ROWID,
538 case when nvl(pa.BILLABLE_CAPITALIZABLE_FLAG,'Y') <> nvl(pji.BILLABLE_CAPITALIZABLE_FLAG,'Y') or
539 nvl(pa.REDUCE_CAPACITY_FLAG,'Y') <> nvl(pji.REDUCE_CAPACITY_FLAG,'Y') or
540 nvl(pa.RES_UTILIZATION_PERCENTAGE,0) <> nvl(pji.RES_UTILIZATION_PERCENTAGE,0) or
541 nvl(pa.ORG_UTILIZATION_PERCENTAGE,0) <> nvl(pji.ORG_UTILIZATION_PERCENTAGE,0) or
542 nvl(pa.TRAINING_FLAG,'Y') <> nvl(pji.TRAINING_FLAG,'Y')
543 then 'Y'
544 else 'N'
545 end
546 from
547 PA_WORK_TYPES_B pa,
548 PJI_RM_WORK_TYPE_INFO pji
549 where
550 pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
551 pji.RECORD_TYPE (+)= 'NORMAL';
552
553 delete
554 from PJI_RM_WORK_TYPE_INFO wt
555 where
556 wt.ROWID not in
557 (
558 select
559 wt_r.PJI_ROWID
560 from
561 PJI_RM_WORK_TYPE_ROWID wt_r
562 where
563 wt_r.PJI_ROWID is not null
564 )
565 and wt.RECORD_TYPE = 'NORMAL';
566
567 pji_utils.write2log(sql%rowcount || ' rows deleted.');
568
569 update PJI_RM_WORK_TYPE_INFO wt
570 set
571 (
572 WORK_TYPE_ID,
573 BILLABLE_CAPITALIZABLE_FLAG,
574 REDUCE_CAPACITY_FLAG,
575 RES_UTILIZATION_PERCENTAGE,
576 ORG_UTILIZATION_PERCENTAGE,
577 TRAINING_FLAG,
578 LAST_UPDATE_DATE,
579 LAST_UPDATED_BY
580 ) =
581 (
582 select
583 pa.WORK_TYPE_ID,
584 pa.BILLABLE_CAPITALIZABLE_FLAG,
585 pa.REDUCE_CAPACITY_FLAG,
586 pa.RES_UTILIZATION_PERCENTAGE,
587 pa.ORG_UTILIZATION_PERCENTAGE,
588 pa.TRAINING_FLAG,
589 pa.LAST_UPDATE_DATE,
590 pa.LAST_UPDATED_BY
591 from
592 PJI_RM_WORK_TYPE_ROWID wt_r,
593 PA_WORK_TYPES_B pa
594 where
595 wt_r.PJI_ROWID = wt.ROWID and
596 pa.ROWID = wt_r.PA_ROWID
597 )
598 where
599 wt.ROWID in
600 (
601 select
602 wt_r.PJI_ROWID
603 from
604 PJI_RM_WORK_TYPE_ROWID wt_r
605 where
606 wt_r.PJI_ROWID is not null and
607 wt_r.CHANGE_FLAG = 'Y'
608 );
609
610 pji_utils.write2log(sql%rowcount || ' rows updated.');
611
612 insert into PJI_RM_WORK_TYPE_INFO
613 (
614 WORK_TYPE_ID,
615 BILLABLE_CAPITALIZABLE_FLAG,
616 REDUCE_CAPACITY_FLAG,
617 RES_UTILIZATION_PERCENTAGE,
618 ORG_UTILIZATION_PERCENTAGE,
619 TRAINING_FLAG,
620 RECORD_TYPE,
621 CREATION_DATE,
622 CREATED_BY,
623 LAST_UPDATE_DATE,
624 LAST_UPDATED_BY
625 )
626 select /*+ rowid(pa) */
627 pa.WORK_TYPE_ID,
628 pa.BILLABLE_CAPITALIZABLE_FLAG,
629 pa.REDUCE_CAPACITY_FLAG,
630 pa.RES_UTILIZATION_PERCENTAGE,
631 pa.ORG_UTILIZATION_PERCENTAGE,
632 pa.TRAINING_FLAG,
633 'NORMAL',
634 pa.CREATION_DATE,
635 pa.CREATED_BY,
636 pa.LAST_UPDATE_DATE,
637 pa.LAST_UPDATED_BY
638 from
639 PA_WORK_TYPES_B pa
640 where
641 pa.ROWID in
642 (
643 select
644 wt_r.PA_ROWID
645 from
646 PJI_RM_WORK_TYPE_ROWID wt_r
647 where
648 wt_r.PJI_ROWID is null
649 );
650
651 pji_utils.write2log(sql%rowcount || ' rows inserted.');
652
653 --Only those work type changes which occured upto launch of summarization
654 --process will be handled in a given run. This is done by tracking
655 --the MAX(EVENT_ID) on the log table PA_PJI_PROJ_EVENTS_LOG
656 begin
657
658 select max(event_id)
659 into l_event_id
660 from
661 pa_pji_proj_events_log log
662 where
663 log.EVENT_TYPE = 'Work Types' and
664 log.OPERATION_TYPE = 'U';
665
666 exception
667 when others then
668 l_event_id := 0;
669 end;
670
671 --WORK TYPE change handling is done as net change handling
672 --not processing every change for a given worktype
673 --Log table stores the old value of worktype attribute
674 --Only the first change is retained for a given worktype
675 --All subsequent changes are deleted below.
676 delete
677 from
678 pa_pji_proj_events_log log
679 where
680 log.EVENT_TYPE = 'Work Types' and
681 log.OPERATION_TYPE = 'U' and
682 log.EVENT_ID <= l_event_id and
683 log.EVENT_ID > ( select min(log1.event_id)
684 from pa_pji_proj_events_log log1
685 where log1.event_object = log.EVENT_OBJECT
686 and log1.operation_type = 'U'
687 group by log1.event_object );
688
689 pji_utils.write2log(sql%rowcount || ' rows deleted :2.');
690
691 --populate CHANGE_OLD slice from PA_PJI_PROJ_EVENTS_LOG table
692 insert into PJI_RM_WORK_TYPE_INFO
693 (
694 WORK_TYPE_ID,
695 BILLABLE_CAPITALIZABLE_FLAG,
696 REDUCE_CAPACITY_FLAG,
697 RES_UTILIZATION_PERCENTAGE,
698 ORG_UTILIZATION_PERCENTAGE,
699 TRAINING_FLAG,
700 RECORD_TYPE,
701 CREATION_DATE,
702 CREATED_BY,
703 LAST_UPDATE_DATE,
704 LAST_UPDATED_BY
705 )
706 select
707 to_number(EVENT_OBJECT),
708 ATTRIBUTE3,
709 ATTRIBUTE4,
710 to_number(ATTRIBUTE1),
711 to_number(ATTRIBUTE2),
712 ATTRIBUTE5,
713 'CHANGE_OLD',
714 sysdate,
715 -1,
716 sysdate,
717 -1
718 from pa_pji_proj_events_log
719 where
720 EVENT_ID <= l_event_id and
721 EVENT_TYPE = 'Work Types' and
722 OPERATION_TYPE = 'U';
723
724 pji_utils.write2log(sql%rowcount || ' rows inserted :2.');
725
726 --Cleanup log table for processed Worktype changes
727 delete
728 from
729 pa_pji_proj_events_log log
730 where
731 log.EVENT_ID <= l_event_id and
732 log.EVENT_TYPE = 'Work Types' and
733 log.OPERATION_TYPE = 'U';
734 pji_utils.write2log(sql%rowcount || ' rows deleted 3.');
735
736 --Populate PJI_RM_WORK_TYPE_INFO with CHANGE_NEW records
737 insert into PJI_RM_WORK_TYPE_INFO
738 (
739 WORK_TYPE_ID,
740 BILLABLE_CAPITALIZABLE_FLAG,
741 REDUCE_CAPACITY_FLAG,
742 RES_UTILIZATION_PERCENTAGE,
743 ORG_UTILIZATION_PERCENTAGE,
744 TRAINING_FLAG,
745 RECORD_TYPE,
746 CREATION_DATE,
747 CREATED_BY,
748 LAST_UPDATE_DATE,
749 LAST_UPDATED_BY
750 )
751 select
752 WORK_TYPE_ID,
753 BILLABLE_CAPITALIZABLE_FLAG,
754 REDUCE_CAPACITY_FLAG,
755 RES_UTILIZATION_PERCENTAGE,
756 ORG_UTILIZATION_PERCENTAGE,
757 TRAINING_FLAG,
758 'CHANGE_NEW',
759 CREATION_DATE,
760 CREATED_BY,
761 LAST_UPDATE_DATE,
762 LAST_UPDATED_BY
763 from PJI_RM_WORK_TYPE_INFO info
764 where info.RECORD_TYPE = 'NORMAL'
765 and info.WORK_TYPE_ID in ( select WORK_TYPE_ID
766 from PJI_RM_WORK_TYPE_INFO wt
767 where wt.RECORD_TYPE = 'CHANGE_OLD');
768
769 pji_utils.write2log(sql%rowcount || ' rows inserted :3.');
770
771 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(p_process);');
772
773 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME, 'PJI_RM_WORK_TYPE_ROWID', 'NORMAL', null);
774
775 commit;
776
777 end UPDATE_PJI_RM_WORK_TYPE_INFO;
778
779
780 -- -----------------------------------------------------
781 -- procedure UPDATE_PJI_ORG_HRCHY
782 --
783 -- This procedure incrementally synchronizes HRI_ORG_HRCHY_SUMMARY with
784 -- PJI_ORG_DENORM. This is required because incremental updates on
785 -- HRI_ORG_HRCHY_SUMMARY are performed by deleting the entire table and
786 -- repopulating it. This would cause slow mview refreshes.
787 --
788 -- -----------------------------------------------------
789 procedure UPDATE_PJI_ORG_HRCHY is
790
791 l_org_structure_version_id number;
792
793 begin
794
795 select ORG_STRUCTURE_VERSION_ID
796 into l_org_structure_version_id
797 from PJI_SYSTEM_SETTINGS;
798
799 insert into PJI_ROWID_ORG_DENORM
800 (
801 HRI_ROWID,
802 PJI_ROWID,
803 CHANGE_FLAG
804 )
805 select /*+ ordered full(pji) use_hash(pji)
806 index(hri, HRI_ORG_HRCHY_SUMMARY_U1) */
807 hri.ROWID,
808 pji.ROWID,
809 case when hri.ORGANIZATION_LEVEL <> pji.ORGANIZATION_LEVEL or
810 hri.SUB_ORGANIZATION_LEVEL <> pji.SUB_ORGANIZATION_LEVEL
811 then 'Y'
812 else 'N'
813 end
814 from
815 HRI_ORG_HRCHY_SUMMARY hri,
816 PJI_ORG_DENORM pji
817 where
818 hri.ORG_STRUCTURE_VERSION_ID = l_org_structure_version_id and
819 hri.ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
820 hri.SUB_ORGANIZATION_ID = pji.SUB_ORGANIZATION_ID (+);
821
822 delete /*+ use_nl(denorm) rowid(denorm) */
823 from PJI_ORG_DENORM denorm
824 where
825 denorm.ROWID not in
826 (
827 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
828 org_r.PJI_ROWID
829 from
830 PJI_ROWID_ORG_DENORM org_r
831 where
832 org_r.PJI_ROWID is not null
833 );
834
835 pji_utils.write2log(sql%rowcount || ' rows deleted.');
836
837 update /*+ use_nl(denorm) rowid(denorm) */ PJI_ORG_DENORM denorm
838 set
839 (
840 ORGANIZATION_LEVEL,
841 SUB_ORGANIZATION_LEVEL
842 ) =
843 (
844 select /*+ ordered index(org_r, PJI_ROWID_ORG_DENORM_N1) rowid(hri) */
845 hri.ORGANIZATION_LEVEL,
846 hri.SUB_ORGANIZATION_LEVEL
847 from
848 PJI_ROWID_ORG_DENORM org_r,
849 HRI_ORG_HRCHY_SUMMARY hri
850 where
851 org_r.PJI_ROWID = denorm.ROWID and
852 hri.ROWID = org_r.HRI_ROWID
853 )
854 where
855 denorm.ROWID in
856 (
857 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
858 org_r.PJI_ROWID
859 from
860 PJI_ROWID_ORG_DENORM org_r
861 where
862 org_r.PJI_ROWID is not null and
863 org_r.CHANGE_FLAG = 'Y'
864 );
865
866 pji_utils.write2log(sql%rowcount || ' rows updated.');
867
868 insert into PJI_ORG_DENORM
869 (
870 ORGANIZATION_ID,
871 ORGANIZATION_LEVEL,
872 SUB_ORGANIZATION_ID,
873 SUB_ORGANIZATION_LEVEL
874 )
875 select /*+ rowid(hri) */
876 hri.ORGANIZATION_ID,
877 hri.ORGANIZATION_LEVEL,
878 hri.SUB_ORGANIZATION_ID,
879 hri.SUB_ORGANIZATION_LEVEL
880 from
881 HRI_ORG_HRCHY_SUMMARY hri
882 where
883 hri.ROWID in
884 (
885 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) */
886 org_r.HRI_ROWID
887 from
888 PJI_ROWID_ORG_DENORM org_r
889 where
890 org_r.PJI_ROWID is null
891 );
892
893 pji_utils.write2log(sql%rowcount || ' rows inserted.');
894
895 execute immediate 'truncate table '|| pji_utils.get_pji_schema_name
896 || '.PJI_ROWID_ORG_DENORM drop storage';
897
898 commit;
899
900 end UPDATE_PJI_ORG_HRCHY;
901
902
903 -- -----------------------------------------------------
904 -- procedure UPDATE_RESOURCE_DATA
905 --
906 -- This procedure incrementally synchronizes PA_RESOURCES_DENORM with
907 -- PJI_RESOURCES_DENORM.
908 --
909 -- -----------------------------------------------------
910 procedure UPDATE_RESOURCE_DATA (p_process in varchar2) is
911
912 l_row_count number;
913 l_max_date date;
914 l_extraction_type varchar2(30);
915
916 begin
917
918 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);')) then
919 return;
920 end if;
921
922 select count(*)
923 into l_row_count
924 from PJI_RESOURCES_DENORM
925 where ROWNUM = 1;
926
927 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
928 'TRANSITION') = 'Y' and
929 l_row_count <> 0) then
930 return;
931 end if;
932
933 l_max_date := PJI_RM_SUM_MAIN.g_max_date;
934 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
935
936 insert into PJI_ROWID_RESOURCES_DENORM
937 (
938 PA_ROWID,
939 PJI_ROWID,
940 CHANGE_FLAG
941 )
942 select /*+ full(pa) parallel(pa) use_hash(pa)
943 full(pji) parallel(pji) use_hash(pji) */
944 pa.ROWID,
945 pji.ROWID,
946 case when nvl(pa.JOB_ID, -999) <>
947 nvl(pji.JOB_ID, -999) or
948 nvl(pa.UTILIZATION_FLAG, 'PJI$NULL') <>
949 nvl(pji.UTILIZATION_FLAG, 'PJI$NULL')
950 then 'Y'
951 else 'N'
952 end
953 from
954 PA_RESOURCES_DENORM pa,
955 PJI_RESOURCES_DENORM pji
956 where
957 pa.PERSON_ID = pji.PERSON_ID (+) and
958 pa.RESOURCE_ID = pji.RESOURCE_ID (+) and
959 pa.RESOURCE_NAME = pji.RESOURCE_NAME (+) and
960 pa.RESOURCE_ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
961 pa.RESOURCE_EFFECTIVE_START_DATE = pji.START_DATE (+) and
962 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date) = pji.END_DATE (+);
963
964 -- --------------------------------------------------------------------
965 -- Determine delta between PA_RESOURCES_DENORM and PJI_RESOURCES_DENORM
966 -- --------------------------------------------------------------------
967
968 if (l_extraction_type <> 'FULL') then
969
970 insert into PJI_RES_DELTA
971 (
972 PERSON_ID,
973 RESOURCE_ID,
974 START_DATE,
975 END_DATE,
976 CHANGE_TYPE
977 )
978 select /*+ use_nl(denorm) rowid(denorm) */ -- old resources
979 denorm.PERSON_ID,
980 denorm.RESOURCE_ID,
981 denorm.START_DATE,
982 denorm.END_DATE,
983 'N'
984 from
985 PJI_RESOURCES_DENORM denorm
986 where
987 denorm.UTILIZATION_FLAG = 'Y' and
988 denorm.ROWID not in
989 (
990 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
991 res_r.PJI_ROWID
992 from
993 PJI_ROWID_RESOURCES_DENORM res_r
994 where
995 res_r.PJI_ROWID is not null
996 )
997 union all -- updated resources
998 select /*+ ordered
999 index(res_r, PJI_ROWID_RESOURCES_DENORM_N1)
1000 rowid(pa)
1001 rowid(pji) */
1002 pa.PERSON_ID,
1003 pa.RESOURCE_ID,
1004 pa.RESOURCE_EFFECTIVE_START_DATE,
1005 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1006 case when (nvl(pa.UTILIZATION_FLAG, 'N') = 'N' and
1007 nvl(pji.UTILIZATION_FLAG, 'N') = 'Y')
1008 then 'N'
1009 when (nvl(pa.UTILIZATION_FLAG, 'N') = 'Y' and
1010 nvl(pji.UTILIZATION_FLAG, 'N') = 'N')
1011 then 'Y'
1012 end
1013 from
1014 PJI_ROWID_RESOURCES_DENORM res_r,
1015 PA_RESOURCES_DENORM pa,
1016 PJI_RESOURCES_DENORM pji
1017 where
1018 res_r.PJI_ROWID is not null and
1019 res_r.CHANGE_FLAG = 'Y' and
1020 res_r.PA_ROWID = pa.ROWID and
1021 res_r.PJI_ROWID = pji.ROWID and
1022 nvl(pji.UTILIZATION_FLAG, 'N') <> nvl(pa.UTILIZATION_FLAG, 'N')
1023 union all -- new resources
1024 select /*+ rowid(pa) */
1025 pa.PERSON_ID,
1026 pa.RESOURCE_ID,
1027 pa.RESOURCE_EFFECTIVE_START_DATE,
1028 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1029 'Y'
1030 from
1031 PA_RESOURCES_DENORM pa
1032 where
1033 pa.UTILIZATION_FLAG = 'Y' and
1034 pa.ROWID in
1035 (
1036 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
1037 res_r.PA_ROWID
1038 from
1039 PJI_ROWID_RESOURCES_DENORM res_r
1040 where
1041 res_r.PJI_ROWID is null
1042 );
1043
1044 end if;
1045
1046 -- --------------------------------------------------------
1047 -- Synchronize PA_RESOURCES_DENORM and PJI_RESOURCES_DENORM
1048 -- --------------------------------------------------------
1049
1050 delete /*+ use_nl(denorm) rowid(denorm) */
1051 from PJI_RESOURCES_DENORM denorm
1052 where
1053 denorm.ROWID not in
1054 (
1055 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
1056 res_r.PJI_ROWID
1057 from
1058 PJI_ROWID_RESOURCES_DENORM res_r
1059 where
1060 res_r.PJI_ROWID is not null
1061 );
1062
1063 pji_utils.write2log(sql%rowcount || ' rows deleted.');
1064
1065 update /*+ use_nl(denorm) rowid(denorm) */ PJI_RESOURCES_DENORM denorm
1066 set
1067 (
1068 JOB_ID,
1069 UTILIZATION_FLAG
1070 ) =
1071 (
1072 select /*+ordered index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) rowid(pa)*/
1073 pa.JOB_ID,
1074 pa.UTILIZATION_FLAG
1075 from
1076 PJI_ROWID_RESOURCES_DENORM res_r,
1077 PA_RESOURCES_DENORM pa
1078 where
1079 res_r.PJI_ROWID = denorm.ROWID and
1080 pa.ROWID = res_r.PA_ROWID
1081 )
1082 where
1083 denorm.ROWID in
1084 (
1085 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
1086 res_r.PJI_ROWID
1087 from
1088 PJI_ROWID_RESOURCES_DENORM res_r
1089 where
1090 res_r.PJI_ROWID is not null and
1091 res_r.CHANGE_FLAG = 'Y'
1092 );
1093
1094 pji_utils.write2log(sql%rowcount || ' rows updated.');
1095
1096 insert into PJI_RESOURCES_DENORM
1097 (
1098 PERSON_ID,
1099 RESOURCE_ID,
1100 RESOURCE_NAME,
1101 START_DATE,
1102 END_DATE,
1103 JOB_ID,
1104 ORGANIZATION_ID,
1105 UTILIZATION_FLAG
1106 )
1107 select /*+ rowid(pa) */
1108 pa.PERSON_ID,
1109 pa.RESOURCE_ID,
1110 pa.RESOURCE_NAME,
1111 pa.RESOURCE_EFFECTIVE_START_DATE,
1112 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1113 pa.JOB_ID,
1114 pa.RESOURCE_ORGANIZATION_ID,
1115 pa.UTILIZATION_FLAG
1116 from
1117 PA_RESOURCES_DENORM pa
1118 where
1119 pa.ROWID in
1120 (
1121 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) */
1122 res_r.PA_ROWID
1123 from
1124 PJI_ROWID_RESOURCES_DENORM res_r
1125 where
1126 res_r.PJI_ROWID is null
1127 );
1128
1129 pji_utils.write2log(sql%rowcount || ' rows inserted.');
1130
1131 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);');
1132
1133 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME, 'PJI_ROWID_RESOURCES_DENORM', 'NORMAL', null);
1134
1135 commit;
1136
1137 end UPDATE_RESOURCE_DATA;
1138
1139
1140 -- -----------------------------------------------------
1141 -- procedure TRUNCATE_PJI_TABLES
1142 --
1143 -- This procedure resets the summarization process by
1144 -- truncating all PJI stage 2 summarization tables.
1145 --
1146 -- -----------------------------------------------------
1147 procedure TRUNCATE_PJI_TABLES
1148 (
1149 errbuf out nocopy varchar2,
1150 retcode out nocopy varchar2,
1151 p_check in varchar2 default 'N'
1152 ) is
1153
1154 l_profile_check varchar2(30);
1155 l_pji_schema varchar2(30);
1156 l_sqlerrm varchar2(240);
1157
1158 begin
1159
1160 l_profile_check := FND_PROFILE.VALUE('PJI_SUM_CLEANALL');
1161
1162 FND_MESSAGE.SET_NAME('PJI', 'PJI_SUM_CLEANALL_FAILED');
1163
1164 if (upper(nvl(l_profile_check, 'N')) <> 'Y') then
1165 pji_utils.write2out(FND_MESSAGE.GET);
1166 commit;
1167 retcode := 1;
1168 return;
1169 end if;
1170
1171 if (upper(nvl(p_check, 'N')) <> 'Y') then
1172 pji_utils.write2out(FND_MESSAGE.GET);
1173 commit;
1174 retcode := 1;
1175 return;
1176 end if;
1177
1178 update FND_PROFILE_OPTION_VALUES
1179 set PROFILE_OPTION_VALUE = 'N'
1180 where APPLICATION_ID = 1292 and
1181 -- LEVEL_ID = 10001 and
1182 PROFILE_OPTION_ID in
1183 (select PROFILE_OPTION_ID
1184 from FND_PROFILE_OPTIONS
1185 where APPLICATION_ID = 1292 and
1186 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
1187
1188 commit;
1189
1190 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1191
1192 insert into PJI_SYSTEM_CONFIG_HIST
1193 (
1194 REQUEST_ID,
1195 USER_NAME,
1196 PROCESS_NAME,
1197 RUN_TYPE,
1198 PARAMETERS,
1199 CONFIG_PROJ_PERF_FLAG,
1200 CONFIG_COST_FLAG,
1201 CONFIG_PROFIT_FLAG,
1202 CONFIG_UTIL_FLAG,
1203 START_DATE,
1204 END_DATE,
1205 COMPLETION_TEXT
1206 )
1207 select
1208 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
1209 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
1210 'STAGE2' PROCESS_NAME,
1211 'CLEANALL' RUN_TYPE,
1212 substr(p_check, 1, 240) PARAMETERS,
1213 null CONFIG_PROJ_PERF_FLAG,
1214 null CONFIG_COST_FLAG,
1215 null CONFIG_PROFIT_FLAG,
1216 null CONFIG_UTIL_FLAG,
1217 sysdate START_DATE,
1218 null END_DATE,
1219 null COMPLETION_TEXT
1220 from
1221 dual;
1222
1223 -- PJP summarization tables with persistent data
1224 delete from PJI_MT_PRC_STEPS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
1225 delete from PJI_SYSTEM_PARAMETERS where NAME like (PJI_RM_SUM_MAIN.g_process || '%$%') or
1226 NAME like 'DANGLING_PJI_ROWS_EXIST' or
1227 NAME like 'LAST_PJI_EXTR_DATE';
1228
1229 -- PJI facts
1230 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_F', 'NORMAL', null);
1231 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_WT_F', 'NORMAL', null);
1232 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AV_ORG_F', 'NORMAL', null);
1233 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CA_ORG_F', 'NORMAL', null);
1234 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_ET_WT_F', 'NORMAL', null);
1235 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_ET_F', 'NORMAL', null);
1236 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_F', 'NORMAL', null);
1237 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_PROJ_F', 'NORMAL', null);
1238
1239 -- PJP intermediate summarization tables
1240 delete from PJI_SYSTEM_PRC_STATUS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
1241
1242 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PROJECT_CLASSES', 'NORMAL', null);
1243 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RESOURCES_DENORM', 'NORMAL', null);
1244 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_WORK_TYPE_INFO', 'NORMAL', null);
1245 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CLASS_CATEGORIES', 'NORMAL', null);
1246 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CLASS_CODES', 'NORMAL', null);
1247 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ORG_DENORM', 'NORMAL', null);
1248
1249 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_HELPER_BATCH_MAP', 'NORMAL', null);
1250 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_PROJ_EXTR_STATUS','NORMAL', null);
1251 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_PROJ_BATCH_MAP', 'NORMAL', null);
1252 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLN', 'NORMAL', null);
1253 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLN_LOG', 'NORMAL', null);
1254 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLNVER1', 'NORMAL', null);
1255 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLNVER2', 'NORMAL', null);
1256 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLAN', 'NORMAL', null);
1257 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJ_EXTR_PRJCLS', 'NORMAL', null);
1258 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_DLY_RATES', 'NORMAL', null);
1259 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_PLN', 'NORMAL', null);
1260 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN3', 'NORMAL', null);
1261 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN4', 'NORMAL', null);
1262 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN5', 'NORMAL', null);
1263 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_FIN', 'NORMAL', null);
1264 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT3', 'NORMAL', null);
1265 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_ACT', 'NORMAL', null);
1266 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_RMAP_FIN', 'NORMAL', null);
1267 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_RMAP_ACT', 'NORMAL', null);
1268
1269 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RES_DELTA', 'NORMAL', null);
1270 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ORG_EXTR_STATUS', 'NORMAL', null);
1271 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_ORG_BATCH_MAP', 'NORMAL', null);
1272 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_REXT_FCSTITEM', 'NORMAL', null);
1273 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_DNGL_RES', 'NORMAL', null);
1274 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES1', 'NORMAL', null);
1275 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES2', 'NORMAL', null);
1276 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES3', 'NORMAL', null);
1277 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_RES', 'NORMAL', null);
1278 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL1', 'NORMAL', null);
1279 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL2', 'NORMAL', null);
1280 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL3', 'NORMAL', null);
1281 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL4', 'NORMAL', null);
1282 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL5', 'NORMAL', null);
1283 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ROLL_WEEK_OFFSET', 'NORMAL', null);
1284 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_BATCH_MAP', 'NORMAL', null);
1285
1286 retcode := 0;
1287
1288 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(errbuf, retcode, 'All', 'C', 'N');
1289
1290 update PJI_SYSTEM_CONFIG_HIST
1291 set END_DATE = sysdate,
1292 COMPLETION_TEXT = 'Normal completion'
1293 where PROCESS_NAME = 'STAGE2' and
1294 END_DATE is null;
1295
1296 commit;
1297
1298 exception when others then
1299
1300 rollback;
1301
1302 l_sqlerrm := substr(sqlerrm, 1, 240);
1303
1304 update PJI_SYSTEM_CONFIG_HIST
1305 set END_DATE = sysdate,
1306 COMPLETION_TEXT = l_sqlerrm
1307 where PROCESS_NAME = 'STAGE2' and
1308 END_DATE is null;
1309
1310 commit;
1311
1312 raise;
1313
1314 end TRUNCATE_PJI_TABLES;
1315
1316
1317 end PJI_PJI_EXTRACTION_UTILS;