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