[Home] [Help]
PACKAGE BODY: APPS.PJI_PJI_EXTRACTION_UTILS
Source
1 PACKAGE BODY PJI_PJI_EXTRACTION_UTILS as
2 /* $Header: PJIUT07B.pls 120.5 2011/09/22 12:09:47 arbandyo 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 NULL;
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; Commented for bug 13011859 */
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 pji.ROWID
560 from
561 PA_WORK_TYPES_B pa,
562 PJI_RM_WORK_TYPE_INFO pji
563 where
564 pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
565 pji.RECORD_TYPE (+)= 'NORMAL' AND
566 pji.ROWID IS NOT null
567
568 )
569 and wt.RECORD_TYPE = 'NORMAL';
570
571 pji_utils.write2log(sql%rowcount || ' rows deleted.');
572
573 update PJI_RM_WORK_TYPE_INFO wt
574 set
575 (
576 WORK_TYPE_ID,
577 BILLABLE_CAPITALIZABLE_FLAG,
578 REDUCE_CAPACITY_FLAG,
579 RES_UTILIZATION_PERCENTAGE,
580 ORG_UTILIZATION_PERCENTAGE,
581 TRAINING_FLAG,
582 LAST_UPDATE_DATE,
583 LAST_UPDATED_BY
584 ) =
585 (
586 select
587 pa.WORK_TYPE_ID,
588 pa.BILLABLE_CAPITALIZABLE_FLAG,
589 pa.REDUCE_CAPACITY_FLAG,
590 pa.RES_UTILIZATION_PERCENTAGE,
591 pa.ORG_UTILIZATION_PERCENTAGE,
592 pa.TRAINING_FLAG,
593 pa.LAST_UPDATE_DATE,
594 pa.LAST_UPDATED_BY
595 from
596 ( select
597 pa.ROWID PA_ROWID,
598 pji.ROWID PJI_ROWID,
599 case when nvl(pa.BILLABLE_CAPITALIZABLE_FLAG,'Y') <> nvl(pji.BILLABLE_CAPITALIZABLE_FLAG,'Y') or
600 nvl(pa.REDUCE_CAPACITY_FLAG,'Y') <> nvl(pji.REDUCE_CAPACITY_FLAG,'Y') or
601 nvl(pa.RES_UTILIZATION_PERCENTAGE,0) <> nvl(pji.RES_UTILIZATION_PERCENTAGE,0) or
602 nvl(pa.ORG_UTILIZATION_PERCENTAGE,0) <> nvl(pji.ORG_UTILIZATION_PERCENTAGE,0) or
603 nvl(pa.TRAINING_FLAG,'Y') <> nvl(pji.TRAINING_FLAG,'Y')
604 then 'Y'
605 else 'N'
606 end
607 from
608 PA_WORK_TYPES_B pa,
609 PJI_RM_WORK_TYPE_INFO pji
610 where
611 pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
612 pji.RECORD_TYPE (+)= 'NORMAL') wt_r,
613 PA_WORK_TYPES_B pa
614 where
615 wt_r.PJI_ROWID = wt.ROWID and
616 pa.ROWID = wt_r.PA_ROWID
617 )
618 where
619 wt.ROWID in
620 (
621 select
622 wt_r.PJI_ROWID
623 from
624 ( select
625 pa.ROWID PA_ROWID,
626 pji.ROWID PJI_ROWID,
627 case when nvl(pa.BILLABLE_CAPITALIZABLE_FLAG,'Y') <> nvl(pji.BILLABLE_CAPITALIZABLE_FLAG,'Y') or
628 nvl(pa.REDUCE_CAPACITY_FLAG,'Y') <> nvl(pji.REDUCE_CAPACITY_FLAG,'Y') or
629 nvl(pa.RES_UTILIZATION_PERCENTAGE,0) <> nvl(pji.RES_UTILIZATION_PERCENTAGE,0) or
630 nvl(pa.ORG_UTILIZATION_PERCENTAGE,0) <> nvl(pji.ORG_UTILIZATION_PERCENTAGE,0) or
631 nvl(pa.TRAINING_FLAG,'Y') <> nvl(pji.TRAINING_FLAG,'Y')
632 then 'Y'
633 else 'N'
634 end CHANGE_FLAG
635 from
636 PA_WORK_TYPES_B pa,
637 PJI_RM_WORK_TYPE_INFO pji
638 where
639 pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
640 pji.RECORD_TYPE (+)= 'NORMAL') wt_r
641 where
642 wt_r.PJI_ROWID is not null and
643 wt_r.CHANGE_FLAG = 'Y'
644 );
645
646 pji_utils.write2log(sql%rowcount || ' rows updated.');
647
648 insert into PJI_RM_WORK_TYPE_INFO
649 (
650 WORK_TYPE_ID,
651 BILLABLE_CAPITALIZABLE_FLAG,
652 REDUCE_CAPACITY_FLAG,
653 RES_UTILIZATION_PERCENTAGE,
654 ORG_UTILIZATION_PERCENTAGE,
655 TRAINING_FLAG,
656 RECORD_TYPE,
657 CREATION_DATE,
658 CREATED_BY,
659 LAST_UPDATE_DATE,
660 LAST_UPDATED_BY
661 )
662 select /*+ rowid(pa) */
663 pa.WORK_TYPE_ID,
664 pa.BILLABLE_CAPITALIZABLE_FLAG,
665 pa.REDUCE_CAPACITY_FLAG,
666 pa.RES_UTILIZATION_PERCENTAGE,
667 pa.ORG_UTILIZATION_PERCENTAGE,
668 pa.TRAINING_FLAG,
669 'NORMAL',
670 pa.CREATION_DATE,
671 pa.CREATED_BY,
672 pa.LAST_UPDATE_DATE,
673 pa.LAST_UPDATED_BY
674 from
675 PA_WORK_TYPES_B pa
676 where
677 pa.ROWID in
678 (
679 select
680 wt_r.PA_ROWID
681 from
682 ( select
683 pa.ROWID PA_ROWID,
684 pji.ROWID PJI_ROWID,
685 case when nvl(pa.BILLABLE_CAPITALIZABLE_FLAG,'Y') <> nvl(pji.BILLABLE_CAPITALIZABLE_FLAG,'Y') or
686 nvl(pa.REDUCE_CAPACITY_FLAG,'Y') <> nvl(pji.REDUCE_CAPACITY_FLAG,'Y') or
687 nvl(pa.RES_UTILIZATION_PERCENTAGE,0) <> nvl(pji.RES_UTILIZATION_PERCENTAGE,0) or
688 nvl(pa.ORG_UTILIZATION_PERCENTAGE,0) <> nvl(pji.ORG_UTILIZATION_PERCENTAGE,0) or
689 nvl(pa.TRAINING_FLAG,'Y') <> nvl(pji.TRAINING_FLAG,'Y')
690 then 'Y'
691 else 'N'
692 end
693 from
694 PA_WORK_TYPES_B pa,
695 PJI_RM_WORK_TYPE_INFO pji
696 where
697 pa.WORK_TYPE_ID = pji.WORK_TYPE_ID (+) and
698 pji.RECORD_TYPE (+)= 'NORMAL') wt_r
699 where
700 wt_r.PJI_ROWID is null
701 );
702
703 pji_utils.write2log(sql%rowcount || ' rows inserted.');
704
705 --Only those work type changes which occured upto launch of summarization
706 --process will be handled in a given run. This is done by tracking
707 --the MAX(EVENT_ID) on the log table PA_PJI_PROJ_EVENTS_LOG
708 begin
709
710 select max(event_id)
711 into l_event_id
712 from
713 pa_pji_proj_events_log log
714 where
715 log.EVENT_TYPE = 'Work Types' and
716 log.OPERATION_TYPE = 'U';
717
718 exception
719 when others then
720 l_event_id := 0;
721 end;
722
723 --WORK TYPE change handling is done as net change handling
724 --not processing every change for a given worktype
725 --Log table stores the old value of worktype attribute
726 --Only the first change is retained for a given worktype
727 --All subsequent changes are deleted below.
728 delete
729 from
730 pa_pji_proj_events_log log
731 where
732 log.EVENT_TYPE = 'Work Types' and
733 log.OPERATION_TYPE = 'U' and
734 log.EVENT_ID <= l_event_id and
735 log.EVENT_ID > ( select min(log1.event_id)
736 from pa_pji_proj_events_log log1
737 where log1.event_object = log.EVENT_OBJECT
738 and log1.operation_type = 'U'
739 group by log1.event_object );
740
741 pji_utils.write2log(sql%rowcount || ' rows deleted :2.');
742
743 --populate CHANGE_OLD slice from PA_PJI_PROJ_EVENTS_LOG table
744 insert into PJI_RM_WORK_TYPE_INFO
745 (
746 WORK_TYPE_ID,
747 BILLABLE_CAPITALIZABLE_FLAG,
748 REDUCE_CAPACITY_FLAG,
749 RES_UTILIZATION_PERCENTAGE,
750 ORG_UTILIZATION_PERCENTAGE,
751 TRAINING_FLAG,
752 RECORD_TYPE,
753 CREATION_DATE,
754 CREATED_BY,
755 LAST_UPDATE_DATE,
756 LAST_UPDATED_BY
757 )
758 select
759 to_number(EVENT_OBJECT),
760 ATTRIBUTE3,
761 ATTRIBUTE4,
762 to_number(ATTRIBUTE1),
763 to_number(ATTRIBUTE2),
764 ATTRIBUTE5,
765 'CHANGE_OLD',
766 sysdate,
767 -1,
768 sysdate,
769 -1
770 from pa_pji_proj_events_log
771 where
772 EVENT_ID <= l_event_id and
773 EVENT_TYPE = 'Work Types' and
774 OPERATION_TYPE = 'U';
775
776 pji_utils.write2log(sql%rowcount || ' rows inserted :2.');
777
778 --Cleanup log table for processed Worktype changes
779 delete
780 from
781 pa_pji_proj_events_log log
782 where
783 log.EVENT_ID <= l_event_id and
784 log.EVENT_TYPE = 'Work Types' and
785 log.OPERATION_TYPE = 'U';
786 pji_utils.write2log(sql%rowcount || ' rows deleted 3.');
787
788 --Populate PJI_RM_WORK_TYPE_INFO with CHANGE_NEW records
789 insert into PJI_RM_WORK_TYPE_INFO
790 (
791 WORK_TYPE_ID,
792 BILLABLE_CAPITALIZABLE_FLAG,
793 REDUCE_CAPACITY_FLAG,
794 RES_UTILIZATION_PERCENTAGE,
795 ORG_UTILIZATION_PERCENTAGE,
796 TRAINING_FLAG,
797 RECORD_TYPE,
798 CREATION_DATE,
799 CREATED_BY,
800 LAST_UPDATE_DATE,
801 LAST_UPDATED_BY
802 )
803 select
804 WORK_TYPE_ID,
805 BILLABLE_CAPITALIZABLE_FLAG,
806 REDUCE_CAPACITY_FLAG,
807 RES_UTILIZATION_PERCENTAGE,
808 ORG_UTILIZATION_PERCENTAGE,
809 TRAINING_FLAG,
810 'CHANGE_NEW',
811 CREATION_DATE,
812 CREATED_BY,
813 LAST_UPDATE_DATE,
814 LAST_UPDATED_BY
815 from PJI_RM_WORK_TYPE_INFO info
816 where info.RECORD_TYPE = 'NORMAL'
817 and info.WORK_TYPE_ID in ( select WORK_TYPE_ID
818 from PJI_RM_WORK_TYPE_INFO wt
819 where wt.RECORD_TYPE = 'CHANGE_OLD');
820
821 pji_utils.write2log(sql%rowcount || ' rows inserted :3.');
822
823 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(p_process);');
824
825 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME, 'PJI_RM_WORK_TYPE_ROWID', 'NORMAL', null);
826
827 commit;
828
829 end UPDATE_PJI_RM_WORK_TYPE_INFO;
830
831
832 -- -----------------------------------------------------
833 -- procedure UPDATE_PJI_ORG_HRCHY
834 --
835 -- This procedure incrementally synchronizes HRI_ORG_HRCHY_SUMMARY with
836 -- PJI_ORG_DENORM. This is required because incremental updates on
837 -- HRI_ORG_HRCHY_SUMMARY are performed by deleting the entire table and
838 -- repopulating it. This would cause slow mview refreshes.
839 --
840 -- -----------------------------------------------------
841 procedure UPDATE_PJI_ORG_HRCHY is
842
843 l_org_structure_version_id number;
844
845 begin
846
847 select ORG_STRUCTURE_VERSION_ID
848 into l_org_structure_version_id
849 from PJI_SYSTEM_SETTINGS;
850
851 /* insert into PJI_ROWID_ORG_DENORM
852 (
853 HRI_ROWID,
854 PJI_ROWID,
855 CHANGE_FLAG
856 )
857 select /*+ ordered full(pji) use_hash(pji)
858 index(hri, HRI_ORG_HRCHY_SUMMARY_U1) *
859 hri.ROWID,
860 pji.ROWID,
861 case when hri.ORGANIZATION_LEVEL <> pji.ORGANIZATION_LEVEL or
862 hri.SUB_ORGANIZATION_LEVEL <> pji.SUB_ORGANIZATION_LEVEL
863 then 'Y'
864 else 'N'
865 end
866 from
867 HRI_ORG_HRCHY_SUMMARY hri,
868 PJI_ORG_DENORM pji
869 where
870 hri.ORG_STRUCTURE_VERSION_ID = l_org_structure_version_id and
871 hri.ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
872 hri.SUB_ORGANIZATION_ID = pji.SUB_ORGANIZATION_ID (+);
873
874 delete /*+ use_nl(denorm) rowid(denorm) *
875 from PJI_ORG_DENORM denorm
876 where
877 denorm.ROWID not in
878 (
879 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) *
880 org_r.PJI_ROWID
881 from
882 PJI_ROWID_ORG_DENORM org_r
883 where
884 org_r.PJI_ROWID is not null
885 );
886
887 pji_utils.write2log(sql%rowcount || ' rows deleted.');
888
889 update /*+ use_nl(denorm) rowid(denorm) * PJI_ORG_DENORM denorm
890 set
891 (
892 ORGANIZATION_LEVEL,
893 SUB_ORGANIZATION_LEVEL
894 ) =
895 (
896 select /*+ ordered index(org_r, PJI_ROWID_ORG_DENORM_N1) rowid(hri) *
897 hri.ORGANIZATION_LEVEL,
898 hri.SUB_ORGANIZATION_LEVEL
899 from
900 PJI_ROWID_ORG_DENORM org_r,
901 HRI_ORG_HRCHY_SUMMARY hri
902 where
903 org_r.PJI_ROWID = denorm.ROWID and
904 hri.ROWID = org_r.HRI_ROWID
905 )
906 where
907 denorm.ROWID in
908 (
909 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) *
910 org_r.PJI_ROWID
911 from
912 PJI_ROWID_ORG_DENORM org_r
913 where
914 org_r.PJI_ROWID is not null and
915 org_r.CHANGE_FLAG = 'Y'
916 );
917
918 pji_utils.write2log(sql%rowcount || ' rows updated.');
919
920 insert into PJI_ORG_DENORM
921 (
922 ORGANIZATION_ID,
923 ORGANIZATION_LEVEL,
924 SUB_ORGANIZATION_ID,
925 SUB_ORGANIZATION_LEVEL
926 )
927 select /*+ rowid(hri) *
928 hri.ORGANIZATION_ID,
929 hri.ORGANIZATION_LEVEL,
930 hri.SUB_ORGANIZATION_ID,
931 hri.SUB_ORGANIZATION_LEVEL
932 from
933 HRI_ORG_HRCHY_SUMMARY hri
934 where
935 hri.ROWID in
936 (
937 select /*+ index(org_r, PJI_ROWID_ORG_DENORM_N1) *
938 org_r.HRI_ROWID
939 from
940 PJI_ROWID_ORG_DENORM org_r
941 where
942 org_r.PJI_ROWID is null
943 );
944
945 pji_utils.write2log(sql%rowcount || ' rows inserted.');
946
947 execute immediate 'truncate table '|| pji_utils.get_pji_schema_name
948 || '.PJI_ROWID_ORG_DENORM drop storage';Commented for bug 13011859 */
949
950 commit;
951
952 end UPDATE_PJI_ORG_HRCHY;
953
954
955 -- -----------------------------------------------------
956 -- procedure UPDATE_RESOURCE_DATA
957 --
958 -- This procedure incrementally synchronizes PA_RESOURCES_DENORM with
959 -- PJI_RESOURCES_DENORM.
960 --
961 -- -----------------------------------------------------
962 procedure UPDATE_RESOURCE_DATA (p_process in varchar2) is
963
964 l_row_count number;
965 l_max_date date;
966 l_extraction_type varchar2(30);
967
968 begin
969
970 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);')) then
971 return;
972 end if;
973
974 /* select count(*)
975 into l_row_count
976 from PJI_RESOURCES_DENORM
977 where ROWNUM = 1;
978
979 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
980 'TRANSITION') = 'Y' and
981 l_row_count <> 0) then
982 return;
983 end if;
984
985 l_max_date := PJI_RM_SUM_MAIN.g_max_date;
986 l_extraction_type := PJI_UTILS.GET_PARAMETER('EXTRACTION_TYPE');
987
988 insert into PJI_ROWID_RESOURCES_DENORM
989 (
990 PA_ROWID,
991 PJI_ROWID,
992 CHANGE_FLAG
993 )
994 select /*+ full(pa) parallel(pa) use_hash(pa)
995 full(pji) parallel(pji) use_hash(pji) *
996 pa.ROWID,
997 pji.ROWID,
998 case when nvl(pa.JOB_ID, -999) <>
999 nvl(pji.JOB_ID, -999) or
1000 nvl(pa.UTILIZATION_FLAG, 'PJI$NULL') <>
1001 nvl(pji.UTILIZATION_FLAG, 'PJI$NULL')
1002 then 'Y'
1003 else 'N'
1004 end
1005 from
1006 PA_RESOURCES_DENORM pa,
1007 PJI_RESOURCES_DENORM pji
1008 where
1009 pa.PERSON_ID = pji.PERSON_ID (+) and
1010 pa.RESOURCE_ID = pji.RESOURCE_ID (+) and
1011 pa.RESOURCE_NAME = pji.RESOURCE_NAME (+) and
1012 pa.RESOURCE_ORGANIZATION_ID = pji.ORGANIZATION_ID (+) and
1013 pa.RESOURCE_EFFECTIVE_START_DATE = pji.START_DATE (+) and
1014 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date) = pji.END_DATE (+); Commented for bug 13011859*/
1015
1016 -- --------------------------------------------------------------------
1017 -- Determine delta between PA_RESOURCES_DENORM and PJI_RESOURCES_DENORM
1018 -- --------------------------------------------------------------------
1019
1020 if (l_extraction_type <> 'FULL') then
1021 NULL;
1022 /* insert into PJI_RES_DELTA
1023 (
1024 PERSON_ID,
1025 RESOURCE_ID,
1026 START_DATE,
1027 END_DATE,
1028 CHANGE_TYPE
1029 )
1030 select /*+ use_nl(denorm) rowid(denorm) * -- old resources
1031 denorm.PERSON_ID,
1032 denorm.RESOURCE_ID,
1033 denorm.START_DATE,
1034 denorm.END_DATE,
1035 'N'
1036 from
1037 PJI_RESOURCES_DENORM denorm
1038 where
1039 denorm.UTILIZATION_FLAG = 'Y' and
1040 denorm.ROWID not in
1041 (
1042 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) *
1043 res_r.PJI_ROWID
1044 from
1045 PJI_ROWID_RESOURCES_DENORM res_r
1046 where
1047 res_r.PJI_ROWID is not null
1048 )
1049 union all -- updated resources
1050 select /*+ ordered
1051 index(res_r, PJI_ROWID_RESOURCES_DENORM_N1)
1052 rowid(pa)
1053 rowid(pji) *
1054 pa.PERSON_ID,
1055 pa.RESOURCE_ID,
1056 pa.RESOURCE_EFFECTIVE_START_DATE,
1057 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1058 case when (nvl(pa.UTILIZATION_FLAG, 'N') = 'N' and
1059 nvl(pji.UTILIZATION_FLAG, 'N') = 'Y')
1060 then 'N'
1061 when (nvl(pa.UTILIZATION_FLAG, 'N') = 'Y' and
1062 nvl(pji.UTILIZATION_FLAG, 'N') = 'N')
1063 then 'Y'
1064 end
1065 from
1066 PJI_ROWID_RESOURCES_DENORM res_r,
1067 PA_RESOURCES_DENORM pa,
1068 PJI_RESOURCES_DENORM pji
1069 where
1070 res_r.PJI_ROWID is not null and
1071 res_r.CHANGE_FLAG = 'Y' and
1072 res_r.PA_ROWID = pa.ROWID and
1073 res_r.PJI_ROWID = pji.ROWID and
1074 nvl(pji.UTILIZATION_FLAG, 'N') <> nvl(pa.UTILIZATION_FLAG, 'N')
1075 union all -- new resources
1076 select /*+ rowid(pa) *
1077 pa.PERSON_ID,
1078 pa.RESOURCE_ID,
1079 pa.RESOURCE_EFFECTIVE_START_DATE,
1080 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1081 'Y'
1082 from
1083 PA_RESOURCES_DENORM pa
1084 where
1085 pa.UTILIZATION_FLAG = 'Y' and
1086 pa.ROWID in
1087 (
1088 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) *
1089 res_r.PA_ROWID
1090 from
1091 PJI_ROWID_RESOURCES_DENORM res_r
1092 where
1093 res_r.PJI_ROWID is null Commented for bug 13011859
1094 ); Commented for bug 13011859 */
1095
1096 end if;
1097
1098 -- --------------------------------------------------------
1099 -- Synchronize PA_RESOURCES_DENORM and PJI_RESOURCES_DENORM
1100 -- --------------------------------------------------------
1101
1102 /* delete /*+ use_nl(denorm) rowid(denorm) *
1103 from PJI_RESOURCES_DENORM denorm
1104 where
1105 denorm.ROWID not in
1106 (
1107 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) *
1108 res_r.PJI_ROWID
1109 from
1110 PJI_ROWID_RESOURCES_DENORM res_r
1111 where
1112 res_r.PJI_ROWID is not null
1113 );
1114
1115 pji_utils.write2log(sql%rowcount || ' rows deleted.');
1116
1117 update /*+ use_nl(denorm) rowid(denorm) * PJI_RESOURCES_DENORM denorm
1118 set
1119 (
1120 JOB_ID,
1121 UTILIZATION_FLAG
1122 ) =
1123 (
1124 select /*+ordered index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) rowid(pa)*
1125 pa.JOB_ID,
1126 pa.UTILIZATION_FLAG
1127 from
1128 PJI_ROWID_RESOURCES_DENORM res_r,
1129 PA_RESOURCES_DENORM pa
1130 where
1131 res_r.PJI_ROWID = denorm.ROWID and
1132 pa.ROWID = res_r.PA_ROWID
1133 )
1134 where
1135 denorm.ROWID in
1136 (
1137 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) *
1138 res_r.PJI_ROWID
1139 from
1140 PJI_ROWID_RESOURCES_DENORM res_r
1141 where
1142 res_r.PJI_ROWID is not null and
1143 res_r.CHANGE_FLAG = 'Y'
1144 );
1145
1146 pji_utils.write2log(sql%rowcount || ' rows updated.');
1147
1148 insert into PJI_RESOURCES_DENORM
1149 (
1150 PERSON_ID,
1151 RESOURCE_ID,
1152 RESOURCE_NAME,
1153 START_DATE,
1154 END_DATE,
1155 JOB_ID,
1156 ORGANIZATION_ID,
1157 UTILIZATION_FLAG
1158 )
1159 select /*+ rowid(pa) *
1160 pa.PERSON_ID,
1161 pa.RESOURCE_ID,
1162 pa.RESOURCE_NAME,
1163 pa.RESOURCE_EFFECTIVE_START_DATE,
1164 nvl(pa.RESOURCE_EFFECTIVE_END_DATE, l_max_date),
1165 pa.JOB_ID,
1166 pa.RESOURCE_ORGANIZATION_ID,
1167 pa.UTILIZATION_FLAG
1168 from
1169 PA_RESOURCES_DENORM pa
1170 where
1171 pa.ROWID in
1172 (
1173 select /*+ index(res_r, PJI_ROWID_RESOURCES_DENORM_N1) *
1174 res_r.PA_ROWID
1175 from
1176 PJI_ROWID_RESOURCES_DENORM res_r
1177 where
1178 res_r.PJI_ROWID is null
1179 );
1180
1181 pji_utils.write2log(sql%rowcount || ' rows inserted.');
1182
1183 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(p_process, 'PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(p_process);');
1184
1185 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(PJI_UTILS.GET_PJI_SCHEMA_NAME, 'PJI_ROWID_RESOURCES_DENORM', 'NORMAL', null);Commented for bug 13011859 */
1186
1187 commit;
1188
1189 end UPDATE_RESOURCE_DATA;
1190
1191
1192 -- -----------------------------------------------------
1193 -- procedure TRUNCATE_PJI_TABLES
1194 --
1195 -- This procedure resets the summarization process by
1196 -- truncating all PJI stage 2 summarization tables.
1197 --
1198 -- -----------------------------------------------------
1199 procedure TRUNCATE_PJI_TABLES
1200 (
1201 errbuf out nocopy varchar2,
1202 retcode out nocopy varchar2,
1203 p_check in varchar2 default 'N'
1204 ) is
1205
1206 l_profile_check varchar2(30);
1207 l_pji_schema varchar2(30);
1208 l_sqlerrm varchar2(240);
1209
1210 begin
1211
1212 l_profile_check := FND_PROFILE.VALUE('PJI_SUM_CLEANALL');
1213
1214 FND_MESSAGE.SET_NAME('PJI', 'PJI_SUM_CLEANALL_FAILED');
1215
1216 if (upper(nvl(l_profile_check, 'N')) <> 'Y') then
1217 pji_utils.write2out(FND_MESSAGE.GET);
1218 commit;
1219 retcode := 1;
1220 return;
1221 end if;
1222
1223 if (upper(nvl(p_check, 'N')) <> 'Y') then
1224 pji_utils.write2out(FND_MESSAGE.GET);
1225 commit;
1226 retcode := 1;
1227 return;
1228 end if;
1229
1230 update FND_PROFILE_OPTION_VALUES
1231 set PROFILE_OPTION_VALUE = 'N'
1232 where APPLICATION_ID = 1292 and
1233 -- LEVEL_ID = 10001 and
1234 PROFILE_OPTION_ID in
1235 (select PROFILE_OPTION_ID
1236 from FND_PROFILE_OPTIONS
1237 where APPLICATION_ID = 1292 and
1238 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
1239
1240 commit;
1241
1242 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1243
1244 insert into PJI_SYSTEM_CONFIG_HIST
1245 (
1246 REQUEST_ID,
1247 USER_NAME,
1248 PROCESS_NAME,
1249 RUN_TYPE,
1250 PARAMETERS,
1251 CONFIG_PROJ_PERF_FLAG,
1252 CONFIG_COST_FLAG,
1253 CONFIG_PROFIT_FLAG,
1254 CONFIG_UTIL_FLAG,
1255 START_DATE,
1256 END_DATE,
1257 COMPLETION_TEXT
1258 )
1259 select
1260 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
1261 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
1262 'STAGE2' PROCESS_NAME,
1263 'CLEANALL' RUN_TYPE,
1264 substr(p_check, 1, 240) PARAMETERS,
1265 null CONFIG_PROJ_PERF_FLAG,
1266 null CONFIG_COST_FLAG,
1267 null CONFIG_PROFIT_FLAG,
1268 null CONFIG_UTIL_FLAG,
1269 sysdate START_DATE,
1270 null END_DATE,
1271 null COMPLETION_TEXT
1272 from
1273 dual;
1274
1275 -- PJP summarization tables with persistent data
1276 delete from PJI_MT_PRC_STEPS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
1277 delete from PJI_SYSTEM_PARAMETERS where NAME like (PJI_RM_SUM_MAIN.g_process || '%$%') or
1278 NAME like 'DANGLING_PJI_ROWS_EXIST' or
1279 NAME like 'LAST_PJI_EXTR_DATE';
1280
1281 -- PJI facts
1282 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_F', 'NORMAL', null);
1283 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_WT_F', 'NORMAL', null);
1284 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AV_ORG_F', 'NORMAL', null);
1285 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CA_ORG_F', 'NORMAL', null);
1286 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_ET_WT_F', 'NORMAL', null);
1287 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_ET_F', 'NORMAL', null);
1288 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_PROJ_F', 'NORMAL', null);
1289 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_PROJ_F', 'NORMAL', null);
1290
1291 -- PJP intermediate summarization tables
1292 delete from PJI_SYSTEM_PRC_STATUS where PROCESS_NAME like (PJI_RM_SUM_MAIN.g_process || '%');
1293
1294 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PROJECT_CLASSES', 'NORMAL', null);
1295 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RESOURCES_DENORM', 'NORMAL', null); Commented for bug 13011859 */
1296 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_WORK_TYPE_INFO', 'NORMAL', null);
1297 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CLASS_CATEGORIES', 'NORMAL', null);
1298 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_CLASS_CODES', 'NORMAL', null);
1299 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ORG_DENORM', 'NORMAL', null);Commented for bug 13011859 */
1300
1301 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_HELPER_BATCH_MAP', 'NORMAL', null);
1302 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_PROJ_EXTR_STATUS','NORMAL', null);
1303 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_PROJ_BATCH_MAP', 'NORMAL', null);
1304 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLN', 'NORMAL', null);
1305 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLN_LOG', 'NORMAL', null);Commented for bug 13011859 */
1306 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLNVER1', 'NORMAL', null);
1307 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLNVER2', 'NORMAL', null);
1308 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLAN', 'NORMAL', null);
1309 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJ_EXTR_PRJCLS', 'NORMAL', null);Commented for bug 13011859 */
1310 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_DLY_RATES', 'NORMAL', null);
1311 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_PLN', 'NORMAL', null);
1312 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN3', 'NORMAL', null);
1313 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN4', 'NORMAL', null);
1314 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN5', 'NORMAL', null);
1315 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_FIN', 'NORMAL', null);
1316 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT3', 'NORMAL', null);
1317 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_ACT', 'NORMAL', null);Commented for bug 13011859 */
1318 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_RMAP_FIN', 'NORMAL', null);
1319 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_RMAP_ACT', 'NORMAL', null);
1320
1321 /* PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RES_DELTA', 'NORMAL', null);
1322 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ORG_EXTR_STATUS', 'NORMAL', null);
1323 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_ORG_BATCH_MAP', 'NORMAL', null);
1324 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_REXT_FCSTITEM', 'NORMAL', null);
1325 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_DNGL_RES', 'NORMAL', null);
1326 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES1', 'NORMAL', null);
1327 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES2', 'NORMAL', null);
1328 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES3', 'NORMAL', null);
1329 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJI_RMAP_RES', 'NORMAL', null);
1330 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL1', 'NORMAL', null);
1331 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL2', 'NORMAL', null);
1332 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL3', 'NORMAL', null);
1333 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL4', 'NORMAL', null);
1334 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_AVL5', 'NORMAL', null);
1335 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ROLL_WEEK_OFFSET', 'NORMAL', null);
1336 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_RES_BATCH_MAP', 'NORMAL', null);Commented for bug 13011859 */
1337
1338 retcode := 0;
1339
1340 -- PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(errbuf, retcode, 'All', 'C', 'N');
1341
1342 update PJI_SYSTEM_CONFIG_HIST
1343 set END_DATE = sysdate,
1344 COMPLETION_TEXT = 'Normal completion'
1345 where PROCESS_NAME = 'STAGE2' and
1346 END_DATE is null;
1347
1348 commit;
1349
1350 exception when others then
1351
1352 rollback;
1353
1354 l_sqlerrm := substr(sqlerrm, 1, 240);
1355
1356 update PJI_SYSTEM_CONFIG_HIST
1357 set END_DATE = sysdate,
1358 COMPLETION_TEXT = l_sqlerrm
1359 where PROCESS_NAME = 'STAGE2' and
1360 END_DATE is null;
1361
1362 commit;
1363
1364 raise;
1365
1366 end TRUNCATE_PJI_TABLES;
1367
1368
1369 end PJI_PJI_EXTRACTION_UTILS;
1370