[Home] [Help]
PACKAGE BODY: APPS.PJI_PJP_EXTRACTION_UTILS
Source
1 PACKAGE BODY PJI_PJP_EXTRACTION_UTILS as
2 /* $Header: PJIUT06B.pls 120.18.12020000.2 2012/12/27 07:01:05 rtalakon ship $ */
3
4 g_worker_id number;
5
6 -- -------------------------------------
7 -- function SET_WORKER_ID
8 --
9 -- History
10 -- 19-MAR-2004 SVERMETT Created
11 --
12 -- Internal PJP Summarization API.
13 --
14 -- -------------------------------------
15 procedure SET_WORKER_ID (p_worker_id in number) is
16
17 l_invalid_worker_id varchar2(255) := 'Partitioning worker ID is invalid.';
18
19 begin
20
21 if (p_worker_id < 1 or
22 p_worker_id > PJI_PJP_SUM_MAIN.g_parallel_processes or
23 p_worker_id <> trunc(p_worker_id)) then
24 dbms_standard.raise_application_error(-20010, l_invalid_worker_id);
25 end if;
26
27 g_worker_id := p_worker_id;
28
29 end SET_WORKER_ID;
30
31
32 -- -------------------------------------
33 -- function GET_WORKER_ID
34 --
35 -- History
36 -- 19-MAR-2004 SVERMETT Created
37 --
38 -- External PJP Summarization API.
39 --
40 -- -------------------------------------
41 function GET_WORKER_ID return number is
42
43 l_no_worker_context varchar2(255) := 'Worker context does not exist.';
44
45 begin
46
47 if (g_worker_id is null) then
48 dbms_standard.raise_application_error(-20020, l_no_worker_context);
49 end if;
50
51 return g_worker_id;
52
53 end GET_WORKER_ID;
54
55
56 -- -------------------------------------
57 -- procedure UPDATE_EXTR_SCOPE
58 --
59 -- History
60 -- 19-MAR-2004 SVERMETT Created
61 --
62 -- Internal PJP Summarization API.
63 --
64 -- -------------------------------------
65 procedure UPDATE_EXTR_SCOPE is
66
67 l_count number;
68
69 l_last_update_date date;
70 l_last_updated_by number;
71 l_creation_date date;
72 l_created_by number;
73 l_last_update_login number;
74
75 begin
76
77 l_last_update_date := sysdate;
78 l_last_updated_by := FND_GLOBAL.USER_ID;
79 l_creation_date := sysdate;
80 l_created_by := FND_GLOBAL.USER_ID;
81 l_last_update_login := FND_GLOBAL.LOGIN_ID;
82
83 select count(*)
84 into l_count
85 from PJI_PJP_PROJ_EXTR_STATUS
86 where ROWNUM = 1;
87
88 if (l_count > 0) then
89
90 insert into PA_PJI_PROJ_EVENTS_LOG
91 (
92 EVENT_TYPE,
93 EVENT_ID,
94 EVENT_OBJECT,
95 OPERATION_TYPE,
96 STATUS,
97 ATTRIBUTE1,
98 LAST_UPDATE_DATE,
99 LAST_UPDATED_BY,
100 CREATION_DATE,
101 CREATED_BY,
102 LAST_UPDATE_LOGIN
103 )
104 select
105 'PRG_CHANGE',
106 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
107 -1,
108 'I',
109 'X',
110 prj.PROJECT_ID,
111 l_last_update_date,
112 l_last_updated_by,
113 l_creation_date,
114 l_created_by,
115 l_last_update_login
116 from
117 PA_PROJECTS_ALL prj,
118 PJI_PJP_PROJ_EXTR_STATUS pjp_status/*, Commented for bug 8916168
119 PJI_PROJ_EXTR_STATUS fm_status Added for bug 8661279 */
120 where
121 prj.TEMPLATE_FLAG = 'N' and
122 prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
123 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
124 (prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y' and /* Added for bug 8916168 */
125 /*prj.PROJECT_ID = fm_status.PROJECT_ID and Added for bug 8661279 Commented for bug 8916168 */
126 pjp_status.PROJECT_ID is null
127 and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
128
129 delete
130 from PJI_PJP_PROJ_EXTR_STATUS pjp
131 where not exists (select 1
132 from PA_PROJECTS_ALL prj
133 where prj.PROJECT_ID = pjp.PROJECT_ID);
134
135 /* This delete statement is added so that data in PJI_PJP_PROJ_EXTR_STATUS
136 is always in sync with data in PJI_PROJ_EXTR_STATUS.
137 Code added for bug 6748705 starts **** Commented for bug 9034593 ****
138 delete
139 from PJI_PJP_PROJ_EXTR_STATUS pjp
140 where not exists (select 1
141 from PJI_PROJ_EXTR_STATUS prj
142 where prj.PROJECT_ID = pjp.PROJECT_ID);
143 /* Code added for bug 6748705 ends */
144
145 /* Below delete added for Bug# 9749751 */
146 -- Commented the delete for bug 11785181 - When project went to CLOSED status, then the below delete was deleting
147 -- the project from PJI_PJP_PROJ_EXTR_STATUS. Later if the status of the project was changed to APPROVED, then
148 -- the insert statement was again inserting the project in 'F' status. Because of this, UPPD was running in FULL
149 -- mode for this project and was inserting duplicates in UPDATE_PROGRAM_WBS. Hence, we will not delete a project
150 -- which has been summarized before from PJI_PJP_PROJ_EXTR_STATUS. It will remain in PJI_PJP_PROJ_EXTR_STATUS
151 -- with extraction status as 'I'. In INIT_PROCESS procedure, while inserting into pji_pjp_proj_batch_map we
152 -- will select only those projects which are eligible for status reporting.
153 /*delete
154 from PJI_PJP_PROJ_EXTR_STATUS pjp
155 where exists (select 1
156 from PA_PROJECTS_ALL prj
157 where prj.PROJECT_ID = pjp.PROJECT_ID
158 and PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED(prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'N'); */
159 /* End for Bug# 9749751 */
160
161 update PJI_PJP_PROJ_EXTR_STATUS sts
162 set sts.PROJECT_ORGANIZATION_ID =
163 (
164 select prj.CARRYING_OUT_ORGANIZATION_ID
165 from PA_PROJECTS_ALL prj
166 where prj.PROJECT_ID = sts.PROJECT_ID
167 )
168 where exists
169 (
170 select 1
171 from PA_PROJECTS_ALL prj
172 where prj.PROJECT_ID = sts.PROJECT_ID and
173 prj.CARRYING_OUT_ORGANIZATION_ID <>
174 sts.PROJECT_ORGANIZATION_ID
175 );
176
177 insert into PJI_PJP_PROJ_EXTR_STATUS
178 (
179 PROJECT_ID,
180 PROJECT_ORGANIZATION_ID,
181 PROJECT_NAME,
182 PROJECT_TYPE_CLASS,
183 EXTRACTION_STATUS,
184 LAST_UPDATE_DATE,
185 LAST_UPDATED_BY,
186 CREATION_DATE,
187 CREATED_BY,
188 LAST_UPDATE_LOGIN
189 )
190 select
191 prj.PROJECT_ID,
192 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
193 'PJI$NULL' PROJECT_NAME,
194 decode(pt.PROJECT_TYPE_CLASS_CODE,
195 'CAPITAL', 'C',
196 'CONTRACT', 'B',
197 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
198 'F' EXTRACTION_STATUS,
199 l_last_update_date,
200 l_last_updated_by,
201 l_creation_date,
202 l_created_by,
203 l_last_update_login
204 from
205 PA_PROJECTS_ALL prj,
206 PA_PROJECT_TYPES_ALL pt,
207 PJI_PJP_PROJ_EXTR_STATUs pjp_status/*, Commented for bug 8916168
208 PJI_PROJ_EXTR_STATUS fm_status Added for bug 6748705 */
209 where
210 prj.TEMPLATE_FLAG = 'N' and
211 prj.ORG_ID = pt.ORG_ID and /*5377131*/
212 prj.PROJECT_TYPE = pt.PROJECT_TYPE and
213 prj.PROJECT_ID = pjp_status.PROJECT_ID (+) and
214 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
215 (prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y' and /* Added for bug 8916168 */
216 /*prj.PROJECT_ID = fm_status.project_id and Added for bug 6748705 Commented for bug 8916168 */
217 pjp_status.PROJECT_ID is null
218 and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
219
220 else
221
222 delete
223 from PA_PJI_PROJ_EVENTS_LOG
224 where EVENT_TYPE in ('WBS_CHANGE',
225 'WBS_PUBLISH',
226 'PRG_CHANGE'
227 -- 'RBS_ASSOC', The source system depends on
228 -- 'RBS_PRG', updates from Project Performance
229 -- 'RBS_PUSH', processing of these events, so
230 -- 'RBS_DELETE' they must persist after truncate.
231 );
232
233 insert into PA_PJI_PROJ_EVENTS_LOG
234 (
235 EVENT_TYPE,
236 EVENT_ID,
237 EVENT_OBJECT,
238 OPERATION_TYPE,
239 STATUS,
240 ATTRIBUTE1,
241 LAST_UPDATE_DATE,
242 LAST_UPDATED_BY,
243 CREATION_DATE,
244 CREATED_BY,
245 LAST_UPDATE_LOGIN
246 )
247 select
248 'PRG_CHANGE',
249 PA_PJI_PROJ_EVENTS_LOG_S.NEXTVAL,
250 -1,
251 'I',
252 'X',
253 prj.PROJECT_ID,
254 l_last_update_date,
255 l_last_updated_by,
256 l_creation_date,
257 l_created_by,
258 l_last_update_login
259 from
260 PA_PROJECTS_ALL prj,
261 PJI_PROJ_EXTR_STATUS fm_status /* Added for bug 8661279 */
262 where
263 prj.TEMPLATE_FLAG = 'N' and
264 not exists (select 1
265 from PA_XBS_DENORM den
266 where den.STRUCT_TYPE = 'PRG' and
267 den.SUP_PROJECT_ID = prj.PROJECT_ID) /*and
268 not exists (select 1
269 from PA_PJI_PROJ_EVENTS_LOG log
270 where log.EVENT_TYPE = 'PRG_CHANGE' and
271 log.EVENT_OBJECT = -1 and
272 log.ATTRIBUTE1 = prj.PROJECT_ID) Commented for bug 9340121 */
273 and prj.PROJECT_ID = fm_status.project_id /* Added for bug 8661279 */
274 and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
275
276 insert into PJI_PJP_PROJ_EXTR_STATUS pjp_i
277 (
278 PROJECT_ID,
279 PROJECT_ORGANIZATION_ID,
280 PROJECT_NAME,
281 PROJECT_TYPE_CLASS,
282 EXTRACTION_STATUS,
283 LAST_UPDATE_DATE,
284 LAST_UPDATED_BY,
285 CREATION_DATE,
286 CREATED_BY,
287 LAST_UPDATE_LOGIN
288 )
289 select
290 prj.PROJECT_ID,
291 prj.CARRYING_OUT_ORGANIZATION_ID PROJECT_ORGANIZATION_ID,
292 'PJI$NULL' PROJECT_NAME,
293 decode(pt.PROJECT_TYPE_CLASS_CODE,
294 'CAPITAL', 'C',
295 'CONTRACT', 'B',
296 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
297 'F' EXTRACTION_STATUS,
298 l_last_update_date,
299 l_last_updated_by,
300 l_creation_date,
301 l_created_by,
302 l_last_update_login
303 from
304 PA_PROJECTS_ALL prj,
305 PA_PROJECT_TYPES_ALL pt,
306 PJI_PROJ_EXTR_STATUS fm_status /* Added for bug 6748705 */
307 where
308 prj.TEMPLATE_FLAG = 'N' and
309 prj.ORG_ID = pt.ORG_ID and /*5377131*/
310 prj.PROJECT_ID = fm_status.project_id and /* Added for bug 6748705 */
311 prj.PROJECT_TYPE = pt.PROJECT_TYPE
312 and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
313
314 end if;
315
316 end UPDATE_EXTR_SCOPE;
317
318
319 -- ----------------------------------------------------------
320 -- procedure POPULATE_ORG_EXTR_INFO
321 --
322 -- History
323 -- 19-MAR-2004 SVERMETT Created
324 --
325 -- Internal PJP Summarization API.
326 --
327 -- ----------------------------------------------------------
328 procedure POPULATE_ORG_EXTR_INFO is
329
330 begin
331
332 PJI_EXTRACTION_UTIL.POPULATE_ORG_EXTR_INFO;
333
334 end POPULATE_ORG_EXTR_INFO;
335
336
337 -- ----------------------------------------------------------
338 -- procedure UPDATE_ORG_EXTR_INFO
339 --
340 -- History
341 -- 19-MAR-2004 SVERMETT Created
342 --
343 -- Internal PJP Summarization API.
344 --
345 -- ----------------------------------------------------------
346 procedure UPDATE_ORG_EXTR_INFO is
347
348 begin
349
350 PJI_EXTRACTION_UTIL.UPDATE_ORG_EXTR_INFO;
351
352 end UPDATE_ORG_EXTR_INFO;
353
354
355 -- ------------------------------------------------------
356 -- Procedure : SEED_PJI_PJP_STATS
357 --
358 -- History
359 -- 19-MAR-2004 SVERMETT Created
360 --
361 -- Internal PJP Summarization API.
362 --
363 -- -----------------------------------------------------
364
365 procedure SEED_PJI_PJP_STATS(p_worker_id in number) is
366
367 l_high_rows number;
368 l_med_rows number;
369 l_low_rows number;
370 l_db_block_size number;
371 l_high_blocks number;
372 l_med_blocks number;
373 l_low_blocks number;
374
375 l_pa_schema varchar2(30);
376 l_pji_schema varchar2(30);
377 l_degree number;
378
379 begin
380
381 /* This procedure sets statistics for all PJI_PJP intermediate tables
382 *
383 * Presently this procedure sets statistics for only
384 * the first partition for partitioned tables since
385 * there will be only one worker for Phase I. Later when
386 * this restriction is removed then the statistics need to
387 * be set for other partitions too.
388 *
389 * The tables are divided into 3 broad categories: high medium and low
390 * The statistics seeded are based on 3 sets of parameters:
391 * Number of rows Average row length
392 * High batch_size 225
393 * Medium batch_size/50 150
394 * Low batch_size/150 75
395 *
396 * Blocks = 1.5* (number of rows * average row length)/block size
397 *
398 * A factor of 1.5 is assumed for row chaining into multiple blocks
399 *
400 */
401
402 l_high_rows := 100000000;
403 l_med_rows := l_high_rows/50;
404 l_low_rows := l_high_rows/150;
405
406 select to_number(value)
407 into l_db_block_size
408 from v$parameter
409 where name = 'db_block_size';
410
411 l_high_blocks := 1.25*(l_high_rows*225)/l_db_block_size;
412 l_med_blocks := 1.25*(l_med_rows*150)/l_db_block_size;
413 l_low_blocks := 1.25*(l_low_rows*75)/l_db_block_size;
414
415 l_pa_schema := PJI_UTILS.GET_PA_SCHEMA_NAME;
416 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
417 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
418
419 -- partitioned tables
420 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_AGGR_PJP0', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
421 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_AC_AGGR_PJP0', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
422 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_CUST_PJP0', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
423 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_AC_CUST_PJP0', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
424 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_AGGR_PJP1', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
425 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_AC_AGGR_PJP1', l_high_rows, l_high_blocks, 225, 'P' || p_worker_id);
426 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_AGGR_XBS', l_med_rows, l_med_blocks, 150, 'P' || p_worker_id);
427 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_AGGR_RBS', l_med_rows, l_med_blocks, 150, 'P' || p_worker_id);
428 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_PJP_PROJ_BATCH_MAP', l_med_rows, l_med_blocks, 150, 'P' || p_worker_id);
429 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_XBS_DENORM_DELTA', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
430 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_RBS_DENORM_DELTA', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
431 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_PA_PROJ_EVENTS_LOG', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
432 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_FP_RMAP_FPR', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
433 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_AC_RMAP_ACR', l_low_rows, l_low_blocks, 75, 'P' || p_worker_id);
434
435 -- non-partitioned tables
436 FND_STATS.SET_TABLE_STATS(l_pa_schema, 'PA_RBS_TXN_ACCUM_MAP', l_med_rows, l_med_blocks, 150);
437 FND_STATS.SET_TABLE_STATS(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', l_med_rows, l_med_blocks, 150);
438
439 -- gather statistics for PJI metadata tables
440 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
441 tabname => 'PJI_PJP_PROJ_EXTR_STATUS',
442 percent => 10,
443 degree => l_degree);
444
445 commit;
446
447 end SEED_PJI_PJP_STATS;
448
449
450 -- ------------------------------------------------------
451 -- procedure ANALYZE_PJP_FACTS
452 --
453 -- History
454 -- 19-MAR-2004 SVERMETT Created
455 --
456 -- Internal PJP Summarization API.
457 --
458 -- ------------------------------------------------------
459 procedure ANALYZE_PJP_FACTS is
460
461 l_pa_schema varchar2(30);
462 l_pji_schema varchar2(30);
463 l_degree number;
464
465 begin
466
467 l_pa_schema := PJI_UTILS.GET_PA_SCHEMA_NAME;
468 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
469 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM;
470
471 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
472 tabname => 'PJI_FP_XBS_ACCUM_F',
473 percent => 10,
474 degree => l_degree);
475
476 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
477 tabname => 'PJI_AC_XBS_ACCUM_F',
478 percent => 10,
479 degree => l_degree);
480
481 FND_STATS.GATHER_TABLE_STATS(ownname => l_pa_schema,
482 tabname => 'PA_XBS_DENORM',
483 percent => 10,
484 degree => l_degree);
485
486 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
487 tabname => 'PJI_XBS_DENORM',
488 percent => 10,
489 degree => l_degree);
490
491 FND_STATS.GATHER_TABLE_STATS(ownname => l_pa_schema,
492 tabname => 'PA_RBS_DENORM',
493 percent => 10,
494 degree => l_degree);
495
496 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
497 tabname => 'PJI_RBS_DENORM',
498 percent => 10,
499 degree => l_degree);
500
501 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
502 tabname => 'PJI_PJP_WBS_HEADER',
503 percent => 10,
504 degree => l_degree);
505
506 FND_STATS.GATHER_TABLE_STATS(ownname => l_pji_schema,
507 tabname => 'PJI_PJP_RBS_HEADER',
508 percent => 10,
509 degree => l_degree);
510
511 commit;
512
513 end ANALYZE_PJP_FACTS;
514
515
516 -- -----------------------------------------------------
517 -- procedure TRUNCATE_PJP_TABLES
518 --
519 -- This procedure resets the summarization process by
520 -- truncating all PJI stage 3 summarization tables.
521 --
522 -- History
523 -- 19-MAR-2004 SVERMETT Created
524 --
525 -- Internal PJP Summarization API.
526 --
527 -- -----------------------------------------------------
528 procedure TRUNCATE_PJP_TABLES
529 (
530 errbuf out nocopy varchar2,
531 retcode out nocopy varchar2,
532 p_check in varchar2 default 'N',
533 p_fpm_upgrade in varchar2 default 'Y',
534 p_recover in varchar2 default 'N'
535 ) is
536
537 l_profile_check varchar2(30);
538 l_pji_schema varchar2(30);
539 l_pa_schema varchar2(30);
540 l_fpm_upgrade varchar2(100);
541 l_return_status varchar2(1);
542 l_msg_count number;
543 l_msg_data varchar2(2000);
544 l_sqlerrm varchar2(240);
545
546 begin
547
548 FND_MESSAGE.SET_NAME('PJI', 'PJI_SUM_CLEANALL_FAILED');
549
550 if (upper(nvl(p_check, 'N')) <> 'Y') then
551 pji_utils.write2out(FND_MESSAGE.GET);
552 commit;
553 retcode := 1;
554 return;
555 end if;
556 /* starts here bug#5414276 , this code is moved out of the profile value check
557 as it should work for only FPM upgrade recovery also */
558 insert into PJI_SYSTEM_CONFIG_HIST
559 (
560 REQUEST_ID,
561 USER_NAME,
562 PROCESS_NAME,
563 RUN_TYPE,
564 PARAMETERS,
565 CONFIG_PROJ_PERF_FLAG,
566 CONFIG_COST_FLAG,
567 CONFIG_PROFIT_FLAG,
568 CONFIG_UTIL_FLAG,
569 START_DATE,
570 END_DATE,
571 COMPLETION_TEXT
572 )
573 select
574 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
575 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
576 'STAGE3' PROCESS_NAME,
577 'CLEANALL' RUN_TYPE,
578 substr(p_check || ', ' ||
579 p_fpm_upgrade || ', ' ||
580 p_recover, 1, 240) PARAMETERS,
581 null CONFIG_PROJ_PERF_FLAG,
582 null CONFIG_COST_FLAG,
583 null CONFIG_PROFIT_FLAG,
584 null CONFIG_UTIL_FLAG,
585 sysdate START_DATE,
586 null END_DATE,
587 null COMPLETION_TEXT
588 from
589 dual;
590
591
592 l_profile_check := FND_PROFILE.VALUE('PJI_SUM_CLEANALL');
593
594 if (upper(nvl(l_profile_check, 'N')) = 'Y') then
595
596 update FND_PROFILE_OPTION_VALUES
597 set PROFILE_OPTION_VALUE = 'N'
598 where APPLICATION_ID = 1292 and
599 -- LEVEL_ID = 10001 and
600 PROFILE_OPTION_ID in
601 (select PROFILE_OPTION_ID
602 from FND_PROFILE_OPTIONS
603 where APPLICATION_ID = 1292 and
604 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
605
606 commit;
607
608 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
609 l_pa_schema := PJI_UTILS.GET_PA_SCHEMA_NAME;
610
611
612
613 -- PJP summarization tables with persistent data
614 delete from PJI_MT_PRC_STEPS where PROCESS_NAME like (PJI_PJP_SUM_MAIN.g_process || '%');
615 delete from PJI_SYSTEM_PARAMETERS where NAME like (PJI_PJP_SUM_MAIN.g_process || '%$%') or
616 NAME like 'PJI_FPM_UPGRADE' or
617 NAME like 'PJI_PTC_UPGRADE' or /*4882640 */
618 NAME like 'PJP_FPM_UPGRADE_DATE' or
619 NAME like 'LAST_PJP_EXTR_DATE%';
620 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', 'NORMAL', null);
621
622 -- PJP facts
623 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_XBS_ACCUM_F', 'NORMAL', null);
624 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_XBS_ACCUM_F', 'NORMAL', null);
625
626 -- PJP intermediate summarization tables
627 delete from PJI_SYSTEM_PRC_STATUS where PROCESS_NAME like (PJI_PJP_SUM_MAIN.g_process || '%');
628 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_BATCH_MAP', 'NORMAL', null);
629 delete from PA_PJI_PROJ_EVENTS_LOG where event_type = 'PLANTYPE_UPG'; /*4882640 */
630 -------------------
631
632 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_XBS', 'NORMAL', null);
633 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pa_schema, 'PA_XBS_DENORM', 'NORMAL', null);
634 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_XBS_DENORM', 'NORMAL', null);
635 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_XBS_DENORM_DELTA', 'NORMAL', null);
636 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_RBS', 'NORMAL', null);
637 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pa_schema, 'PA_RBS_DENORM', 'NORMAL', null);
638 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RBS_DENORM', 'NORMAL', null);
639 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RBS_DENORM_DELTA', 'NORMAL', null);
640 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pa_schema, 'PA_RBS_TXN_ACCUM_MAP', 'NORMAL', null);
641 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_RES_TYPES', 'NORMAL', null);
642 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_EXTR_STATUS', 'NORMAL', null);
643 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_PROJ_BATCH_MAP', 'NORMAL', null);
644 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PA_PROJ_EVENTS_LOG', 'NORMAL', null);
645 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_PJP0', 'NORMAL', null);
646 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_AGGR_PJP0', 'NORMAL', null);
647 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_CUST_PJP0', 'NORMAL', null);
648 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_CUST_PJP0', 'NORMAL', null);
649 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_PJP1', 'NORMAL', null);
650 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_AGGR_PJP1', 'NORMAL', null);
651 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_RBS_HEADER', 'NORMAL', null);
652 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_WBS_HEADER', 'NORMAL', null);
653 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_RMAP_FPR', 'NORMAL', null);
654 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_RMAP_FPR', 'NORMAL', null);
655 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PJP_RMAP_ACR', 'NORMAL', null);
656 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_AC_RMAP_ACR', 'NORMAL', null);
657 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_WEEK', 'NORMAL', null);
658 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_RPT_STRUCT', 'NORMAL', null);
659 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_ENT_PERIOD', 'NORMAL', null);
660 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_ENT_QTR', 'NORMAL', null);
661 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_ENT_YEAR', 'NORMAL', null);
662 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_CAL_EXTR_INFO', 'NORMAL', null);
663 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_CAL_PERIOD', 'NORMAL', null);
664 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_CAL_QTR', 'NORMAL', null);
665 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_CAL_RPT_STRUCT', 'NORMAL', null);
666 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_TIME_CAL_YEAR', 'NORMAL', null);
667 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_REP_XBS_DENORM', 'NORMAL', null);
668 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_ROLLUP_LEVEL_STATUS', 'NORMAL', null);
669 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN8', 'NORMAL', null);
670 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLNVER4', 'NORMAL', null);
671 /* Added for bug 13897252 starts */
672 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_AGGR_PJP2', 'NORMAL', null);
673 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_MERGE_HELPER', 'NORMAL', null);
674 /* Added for bug 13897252 ends */
675
676 commit;
677
678 end if;
679
680 l_fpm_upgrade := nvl(PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE'), 'X');
681
682 if (p_fpm_upgrade = 'Y' and l_fpm_upgrade <> 'C') then
683
684 PJI_FM_PLAN_MAINT.CREATE_PRIMARY_UPGRD_PVT(p_context => 'TRUNCATE');
685
686 end if;
687
688 update PJI_SYSTEM_CONFIG_HIST
689 set END_DATE = sysdate,
690 COMPLETION_TEXT = 'Normal completion'
691 where PROCESS_NAME = 'STAGE3' and
692 END_DATE is null;
693
694 commit;
695
696 retcode := 0;
697
698 exception when others then
699
700 rollback;
701
702 l_sqlerrm := substr(sqlerrm, 1, 240);
703 /* starts here bug#5414276 , if the program failed this is showing completed
704 successfully in the SRS, retcode=2 will make sure it shows Error */
705
706 retcode := 2;
707 errbuf := l_sqlerrm;
708 /* ends here bug#5414276 */
709 update PJI_SYSTEM_CONFIG_HIST
710 set END_DATE = sysdate,
711 COMPLETION_TEXT = l_sqlerrm
712 where PROCESS_NAME = 'STAGE3' and
713 END_DATE is null;
714
715 commit;
716
717 raise;
718
719 end TRUNCATE_PJP_TABLES;
720
721
722 -- -----------------------------------------------------
723 -- function LAST_PJP_EXTR_DATE
724 --
725 -- History
726 -- 26-MAY-2004 SVERMETT Created
727 --
728 -- External PJP Summarization API.
729 --
730 -- -----------------------------------------------------
731
732 function LAST_PJP_EXTR_DATE( p_project_id IN number DEFAULT null) return date is
733
734 l_last_proj_extr_date date;
735
736 begin
737
738 select trunc(last_update_date)
739 into l_last_proj_extr_date
740 from PJI_PJP_PROJ_EXTR_STATUS
741 where project_id = p_project_id ;
742
743 return l_last_proj_extr_date;
744
745 exception when no_data_found then
746
747 return null;
748
749 end LAST_PJP_EXTR_DATE;
750
751 end PJI_PJP_EXTRACTION_UTILS;