[Home] [Help]
PACKAGE BODY: APPS.PJI_EXTRACTION_UTIL
Source
1 PACKAGE BODY PJI_EXTRACTION_UTIL as
2 /* $Header: PJIUT02B.pls 120.16 2011/12/06 19:58:56 sachandr ship $ */
3
4 g_wh_db_link varchar2(128);
5 g_src_db_link varchar2(128);
6 g_ent_period_refresh_flag boolean := FALSE;
7
8 -- -------------------------------------
9 -- procedure UPDATE_EXTR_SCOPE
10 -- -------------------------------------
11 procedure UPDATE_EXTR_SCOPE is
12
13 l_extr_start_date date;
14 Cursor csr_purge_projs is
15 select prj.project_id, sts.project_system_status_code
16 from pa_projects_all prj
17 , pa_project_statuses sts
18 where prj.project_status_code = sts.project_status_code
19 and sts.project_system_status_code in ('PARTIALLY_PURGED'
20 ,'PURGED'
21 ,'PENDING_PURGE')
22 ;
23
24 rec_purge_projs csr_purge_projs%ROWTYPE;
25
26 l_row_count number;
27
28 begin
29
30 select count(*)
31 into l_row_count
32 from PJI_PROJ_EXTR_STATUS
33 where ROWNUM = 1;
34
35 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,
36 'TRANSITION') = 'Y' and
37 l_row_count <> 0) then
38 return;
39 end if;
40
41 -- Delete all events for new and deleted projects as well as changes
42 -- in organization all of which are handled without the log table.
43 delete
44 from PA_PJI_PROJ_EVENTS_LOG
45 where EVENT_TYPE = 'Projects';
46
47 l_extr_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE;
48
49 if (PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE') is not null and
50 trunc(l_extr_start_date, 'J') <>
51 trunc(to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
52 PJI_FM_SUM_MAIN.g_date_mask), 'J')) then
53 pji_utils.write2log('WARNING: Global start date has changed.');
54 end if;
55
56 -- Delete from PJI_PROJ_EXTR_STATUS those projects deleted from
57 -- PA_PROJECTS_ALL. Note that a project cannot be deleted if it has any
58 -- actuals transactions, so we don't need to worry about purging data from
59 -- PJI facts.
60
61 delete
62 from PJI_PROJ_EXTR_STATUS pji
63 where not exists (select 1
64 from PA_PROJECTS_ALL pa
65 where pa.PROJECT_ID = pji.PROJECT_ID);
66
67 insert into PJI_PROJ_EXTR_STATUS
68 (
69 PROJECT_ID,
70 PROJECT_ORGANIZATION_ID,
71 PROJECT_NAME,
72 LAST_UPDATE_DATE,
73 CREATION_DATE,
74 PURGE_STATUS,
75 PROJECT_TYPE_CLASS
76 )
77 select
78 prj.PROJECT_ID,
79 prj.CARRYING_OUT_ORGANIZATION_ID,
80 'PJI$NULL',
81 sysdate,
82 sysdate,
83 sts.PROJECT_SYSTEM_STATUS_CODE,
84 DECODE(pt.PROJECT_TYPE_CLASS_CODE,
85 'CAPITAL', 'C',
86 'CONTRACT', 'B',
87 'INDIRECT', 'I')
88 from
89 PA_PROJECTS_ALL prj,
90 PA_PROJECT_STATUSES sts,
91 PA_PROJECT_TYPES_ALL pt,
92 (
93 select
94 PROJECT_STATUS_CODE
95 from
96 (
97 select /*+ index_ffs(prj, PA_PROJECTS_N4)
98 parallel_index(prj, PA_PROJECTS_N4) */
99 distinct
100 prj.PROJECT_STATUS_CODE
101 from
102 PA_PROJECTS_ALL prj
103 )
104 where
105 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
106 (PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
107 ) psc
108 where
109 nvl(closed_date,l_extr_start_date) >= l_extr_start_date and
110 prj.project_status_code = psc.project_status_code and
111 not exists
112 (
113 select 1
114 from PJI_PROJ_EXTR_STATUS ps
115 where ps.PROJECT_ID = prj.PROJECT_ID
116 ) and
117 prj.project_status_code = sts.project_status_code and
118 nvl(prj.ORG_ID, -1) = nvl(pt.ORG_ID, -1) and
119 prj.PROJECT_TYPE = pt.PROJECT_TYPE
120 and prj.project_type <> 'AWARD_PROJECT'; /* Added for Bug 6450518 */
121
122
123 -- Some existing project might have got archived/purged since the last run. These
124 -- projects need to be updated, they should not be included in the current run.
125
126 For rec_purge_projs in csr_purge_projs LOOP
127
128 update PJI_PROJ_EXTR_STATUS extr
129 set extr.purge_status = rec_purge_projs.project_system_status_code
130 where extr.project_id = rec_purge_projs.project_id
131 and NVL(extr.purge_status, 'X') not in ('PARTIALLY_PURGED'
132 ,'PURGED'
133 ,'PENDING_PURGE')
134 ;
135
136 End LOOP;
137
138 end UPDATE_EXTR_SCOPE;
139
140
141 -- ----------------------------------------------------------
142 -- procedure POPULATE_ORG_EXTR_INFO
143 -- ----------------------------------------------------------
144 procedure POPULATE_ORG_EXTR_INFO is
145
146 begin
147
148 UPDATE_ORG_EXTR_INFO; -- PJI_ORG_EXTR_INFO always maintained incrementally
149
150 end POPULATE_ORG_EXTR_INFO;
151
152
153 -- ----------------------------------------------------------
154 -- procedure UPDATE_ORG_EXTR_INFO
155 -- ----------------------------------------------------------
156 procedure UPDATE_ORG_EXTR_INFO is
157
158 pragma AUTONOMOUS_TRANSACTION;
159
160 l_ent_cal_min_date number;
161 l_ent_cal_max_date number;
162
163 begin
164
165 begin
166
167 select
168 to_number(to_char(min(START_DATE), 'J')),
169 to_number(to_char(max(END_DATE), 'J'))
170 into
171 l_ent_cal_min_date,
172 l_ent_cal_max_date
173 from
174 PJI_TIME_ENT_PERIOD_V;
175
176 exception when no_data_found then null;
177
178 end;
179
180 insert into PJI_ORG_EXTR_INFO
181 (
182 ORG_ID,
183 PF_CURRENCY_CODE,
184 EN_CALENDAR_MIN_DATE,
185 EN_CALENDAR_MAX_DATE,
186 GL_CALENDAR_ID,
187 GL_CALENDAR_MIN_DATE,
188 GL_CALENDAR_MAX_DATE,
189 PA_CALENDAR_ID,
190 PA_CALENDAR_MIN_DATE,
191 PA_CALENDAR_MAX_DATE
192 )
193 select
194 -1, -- -1 can be a valid operating unit when a row
195 'PJI$NULL', -- is only a receiver row or only a provider
196 to_number(null), -- row. When a row applies to both receiver
197 to_number(null), -- and provider, ord_id will never be -1.
198 to_number(null),
199 to_number(null), -- Added to_number for bug 3621077
200 to_number(null),
201 to_number(null),
202 to_number(null),
203 to_number(null)
204 from
205 dual
206 where
207 not exists (select ORG_ID
208 from PA_IMPLEMENTATIONS_ALL
209 where ORG_ID is null) and
210 -1 not in (select ORG_ID
211 from PJI_ORG_EXTR_INFO)
212 union all
213 select
214 nvl(imp.ORG_ID,-1) ORG_ID,
215 to_char(null),
216 to_number(null),
217 to_number(null),
218 to_number(null),
219 to_number(null),
220 to_number(null),
221 to_number(null),
222 to_number(null),
223 to_number(null)
224 from
225 PA_IMPLEMENTATIONS_ALL imp
226 where
227 imp.ORG_ID not in (select ORG_ID
228 from PJI_ORG_EXTR_INFO);
229
230 update PJI_ORG_EXTR_INFO info
231 set (PF_CURRENCY_CODE,
232 EN_CALENDAR_MIN_DATE,
233 EN_CALENDAR_MAX_DATE,
234 GL_CALENDAR_ID,
235 GL_CALENDAR_MIN_DATE,
236 GL_CALENDAR_MAX_DATE,
237 PA_CALENDAR_ID,
238 PA_CALENDAR_MIN_DATE,
239 PA_CALENDAR_MAX_DATE) =
240 (select
241 gl.CURRENCY_CODE,
242 l_ent_cal_min_date,
243 l_ent_cal_max_date,
244 gl.CALENDAR_ID,
245 to_number(to_char(gl.START_DATE, 'J')),
246 to_number(to_char(gl.END_DATE, 'J')),
247 pa.CALENDAR_ID,
248 to_number(to_char(pa.START_DATE, 'J')),
249 to_number(to_char(pa.END_DATE, 'J'))
250 from
251 (
252 select
253 nvl(imp.ORG_ID,-1) ORG_ID,
254 sob.CURRENCY_CODE,
255 min(glp.START_DATE) START_DATE,
256 max(glp.END_DATE) END_DATE,
257 fii.CALENDAR_ID
258 from
259 PA_IMPLEMENTATIONS_ALL imp,
260 GL_SETS_OF_BOOKS sob,
261 GL_PERIODS glp,
262 PA_TIME_CAL_NAME fii /* Modified for bug 12979524 */
263 where
264 imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
265 sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
266 sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
267 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
268 fii.PERIOD_TYPE = glp.PERIOD_TYPE
269 group by
270 nvl(imp.ORG_ID,-1),
271 sob.CURRENCY_CODE,
272 fii.CALENDAR_ID
273 ) gl,
274 (
275 select
276 nvl(imp.ORG_ID,-1) ORG_ID,
277 min(glp.START_DATE) START_DATE,
278 max(glp.END_DATE) END_DATE,
279 fii.CALENDAR_ID
280 from
281 PA_IMPLEMENTATIONS_ALL imp,
282 GL_PERIODS glp,
283 PA_TIME_CAL_NAME fii /* Modified for bug 12979524 */
284 where
285 imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
286 imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
287 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
288 fii.PERIOD_TYPE = glp.PERIOD_TYPE
289 group by
290 nvl(imp.ORG_ID,-1),
291 fii.CALENDAR_ID
292 ) pa
293 where
294 gl.ORG_ID = pa.ORG_ID and
295 gl.ORG_ID = info.ORG_ID)
296 where
297 (nvl(ORG_ID, -1),
298 nvl(PF_CURRENCY_CODE, 'PJI$NULL1'),
299 nvl(EN_CALENDAR_MIN_DATE, 1),
300 nvl(EN_CALENDAR_MAX_DATE, 1),
301 nvl(GL_CALENDAR_ID, -1),
302 nvl(GL_CALENDAR_MIN_DATE, 1),
303 nvl(GL_CALENDAR_MAX_DATE, 1),
304 nvl(PA_CALENDAR_ID, -1),
305 nvl(PA_CALENDAR_MIN_DATE, 1),
306 nvl(PA_CALENDAR_MAX_DATE, 1)) not in
307 (select
308 nvl(gl.ORG_ID, -1),
309 nvl(gl.CURRENCY_CODE, 'PJI$NULL2'),
310 nvl(l_ent_cal_min_date, 2),
311 nvl(l_ent_cal_max_date, 2),
312 nvl(gl.CALENDAR_ID, -2),
313 nvl(to_number(to_char(gl.START_DATE, 'J')), 2),
314 nvl(to_number(to_char(gl.END_DATE, 'J')), 2),
315 nvl(pa.CALENDAR_ID, -2),
316 nvl(to_number(to_char(pa.START_DATE, 'J')), 2),
317 nvl(to_number(to_char(pa.END_DATE, 'J')), 2)
318 from
319 (
320 select
321 nvl(imp.ORG_ID,-1) ORG_ID,
322 sob.CURRENCY_CODE,
323 min(glp.START_DATE) START_DATE,
324 max(glp.END_DATE) END_DATE,
325 fii.CALENDAR_ID
326 from
327 PA_IMPLEMENTATIONS_ALL imp,
328 GL_SETS_OF_BOOKS sob,
329 GL_PERIODS glp,
330 PA_TIME_CAL_NAME fii /* Modified for bug 12979524 */
331 where
332 imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
333 sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
334 sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
335 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
336 fii.PERIOD_TYPE = glp.PERIOD_TYPE
337 group by
338 nvl(imp.ORG_ID,-1),
339 sob.CURRENCY_CODE,
340 fii.CALENDAR_ID
341 ) gl,
342 (
343 select
344 nvl(imp.ORG_ID,-1) ORG_ID,
345 min(glp.START_DATE) START_DATE,
346 max(glp.END_DATE) END_DATE,
347 fii.CALENDAR_ID
348 from
349 PA_IMPLEMENTATIONS_ALL imp,
350 GL_PERIODS glp,
351 PA_TIME_CAL_NAME fii /* Modified for bug 12979524 */
352 where
353 imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
354 imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
355 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
356 fii.PERIOD_TYPE = glp.PERIOD_TYPE
357 group by
358 nvl(imp.ORG_ID,-1),
359 fii.CALENDAR_ID
360 ) pa
361 where
362 gl.ORG_ID = pa.ORG_ID and
363 gl.ORG_ID = info.ORG_ID);
364
365 update PJI_ORG_EXTR_INFO
366 set PF_CURRENCY_CODE = 'PJI$NULL'
367 where ORG_ID = -1 and
368 nvl(PF_CURRENCY_CODE, 'x') <> 'PJI$NULL';
369
370 update PJI_ORG_EXTR_INFO
371 set EN_CALENDAR_MIN_DATE = l_ent_cal_min_date,
372 EN_CALENDAR_MAX_DATE = l_ent_cal_max_date
373 where ORG_ID <> -1 and
374 (nvl(EN_CALENDAR_MIN_DATE, 1) <> l_ent_cal_min_date or
375 nvl(EN_CALENDAR_MAX_DATE, 1) <> l_ent_cal_max_date);
376
377 commit; -- we can commit since transaction is autonomous
378
379 end UPDATE_ORG_EXTR_INFO;
380
381
382 /* ------------------------------------------------------
383 Procedure : SEED_PJI_FM_STATS
384 -----------------------------------------------------*/
385
386 PROCEDURE SEED_PJI_FM_STATS IS
387
388 l_high_rows number;
389 l_med_rows number;
390 l_low_rows number;
391 l_db_block_size number;
392 l_high_blocks number;
393 l_med_blocks number;
394 l_low_blocks number;
395
396 l_schema varchar2(30);
397 l_degree number;
398
399 BEGIN
400 /* This procedure sets statistics for all PJI_FM intermediate tables
401 *
402 * Presently this procedure sets statistics for only
403 * the first partition for partitioned tables since
404 * there will be only one worker for Phase I. Later when
405 * this restriction is removed then the statistics need to
406 * be set for other partitions too.
407 *
408 * The tables are divided into 3 broad categories: high medium and low
409 * The statistics seeded are based on 3 sets of parameters:
410 * Number of rows Average row length
411 * High batch_size 225
412 * Medium batch_size/50 150
413 * Low batch_size/150 75
414 *
415 * Blocks = 1.5* (number of rows * average row length)/block size
416 *
417 * A factor of 1.5 is assumed for row chaining into multiple blocks
418 *
419 */
420
421
422 l_high_rows := GET_BATCH_SIZE;
423 l_med_rows := l_high_rows/50;
424 l_low_rows := l_high_rows/150;
425
426 select to_number(value)
427 into l_db_block_size
428 from v$parameter
429 where name = 'db_block_size'
430 ;
431
432 l_high_blocks := 1.25*(l_high_rows*225)/l_db_block_size;
433 l_med_blocks := 1.25*(l_med_rows*150)/l_db_block_size;
434 l_low_blocks := 1.25*(l_low_rows*75)/l_db_block_size;
435
436
437 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
438 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
439
440 -- non-partitioned tables
441 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT1' , l_high_rows, l_high_blocks, 225);
442 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT2' , l_high_rows, l_high_blocks, 225);
443 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT4' , l_high_rows, l_high_blocks, 225);
444 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN1' , l_high_rows, l_high_blocks, 225);
445 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN2' , l_high_rows, l_high_blocks, 225);
446 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_DLY_RATES', l_low_rows, l_low_blocks, 75);
447 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_ARINV' , l_med_rows, l_med_blocks, 150);
448 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DINVC' , l_med_rows, l_med_blocks, 150);
449 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DINVCITM' , l_med_rows, l_med_blocks, 150);
450 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DREVN' , l_med_rows, l_med_blocks, 150);
451 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_FUNDG' , l_med_rows, l_med_blocks, 150);
452 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_CDL' , l_high_rows, l_high_blocks, 225);
453 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_CRDL' , l_high_rows, l_high_blocks, 225);
454 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_ERDL' , l_high_rows, l_high_blocks, 225);
455 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_PROJ_BATCH_MAP' , l_low_rows, l_low_blocks, 75);
456
457 -- gather statistics for PJI metadata tables
458 FND_STATS.GATHER_TABLE_STATS(
459 ownname => l_schema
460 , tabname => 'PJI_PROJ_EXTR_STATUS'
461 , percent => 10
462 , degree => l_degree
463 );
464 FND_STATS.GATHER_INDEX_STATS(ownname => l_schema,
465 indname => 'PJI_PROJ_EXTR_STATUS_U1',
466 percent => 10);
467 -- Commenting this because the table is used in Stage2 Summarization . Bug#4997700
468 /* FND_STATS.GATHER_TABLE_STATS(
469 ownname => l_schema
470 , tabname => 'PJI_PROJECT_CLASSES'
471 , percent => 10
472 , degree => l_degree
473 ); */
474 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
475 tabname => 'PJI_FM_PROJ_BATCH_MAP',
476 percent => 10,
477 degree => l_degree);
478 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
479 tabname => 'PJI_FM_PROJ_BATCH_MAP',
480 colname => 'EXTRACTION_TYPE',
481 percent => 10,
482 degree => l_degree);
483 FND_STATS.GATHER_INDEX_STATS(ownname => l_schema,
484 indname => 'PJI_FM_PROJ_BATCH_MAP_U1',
485 percent => 10);
486
487 END SEED_PJI_FM_STATS;
488
489
490 -- -----------------------------------------------------
491 -- procedure TRUNCATE_PJI_TABLES
492 --
493 -- This procedure resets the summarization process by
494 -- truncating all PJI stage 1 summarization tables.
495 --
496 -- -----------------------------------------------------
497 procedure TRUNCATE_PJI_TABLES
498 (
499 errbuf out nocopy varchar2,
500 retcode out nocopy varchar2,
501 p_check in varchar2 default 'N',
502 p_truncate_pji_tables in varchar2 default 'Y',
503 p_truncate_pjp_tables in varchar2 default 'Y',
504 p_run_fpm_upgrade in varchar2 default 'N'
505 ) is
506
507 l_profile_check varchar2(30);
508 l_pji_schema varchar2(30);
509 l_sqlerrm varchar2(240);
510 l_last_update_date date;
511 l_last_updated_by number;
512 l_last_update_login number;
513
514 begin
515
516 l_profile_check := FND_PROFILE.VALUE('PJI_SUM_CLEANALL');
517
518 FND_MESSAGE.SET_NAME('PJI', 'PJI_SUM_CLEANALL_FAILED');
519
520 if (upper(nvl(l_profile_check, 'N')) <> 'Y') then
521 pji_utils.write2out(FND_MESSAGE.GET);
522 commit;
523 retcode := 1;
524 return;
525 end if;
526
527 if (upper(nvl(p_check, 'N')) <> 'Y') then
528 pji_utils.write2out(FND_MESSAGE.GET);
529 commit;
530 retcode := 1;
531 return;
532 end if;
533
534 update FND_PROFILE_OPTION_VALUES
535 set PROFILE_OPTION_VALUE = 'N'
536 where APPLICATION_ID = 1292 and
537 -- LEVEL_ID = 10001 and
538 PROFILE_OPTION_ID in
539 (select PROFILE_OPTION_ID
540 from FND_PROFILE_OPTIONS
541 where APPLICATION_ID = 1292 and
542 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
543
544 commit;
545
546 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
547
548 -- PJI summarization tables with persistent data
549 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_MT_PRC_STEPS', 'NORMAL', null);
550 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_CONFIG_HIST', 'NORMAL', null);
551 delete from PJI_SYSTEM_PARAMETERS where NAME not in ('PJI_PJP_ENT_CURR_REP_PERIOD','PJP_SIN_PRG'); -- Bug 13451898
552 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PROJ_EXTR_STATUS', 'NORMAL', null);
553 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_DEBUG_MSG', 'NORMAL', null);
554
555 insert into PJI_SYSTEM_CONFIG_HIST
556 (
557 REQUEST_ID,
558 USER_NAME,
559 PROCESS_NAME,
560 RUN_TYPE,
561 PARAMETERS,
562 CONFIG_PROJ_PERF_FLAG,
563 CONFIG_COST_FLAG,
564 CONFIG_PROFIT_FLAG,
565 CONFIG_UTIL_FLAG,
566 START_DATE,
567 END_DATE,
568 COMPLETION_TEXT
569 )
570 select
571 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
572 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
573 'STAGE1' PROCESS_NAME,
574 'CLEANALL' RUN_TYPE,
575 substr(p_check || ', ' ||
576 p_truncate_pji_tables || ', ' ||
577 p_truncate_pjp_tables || ', ' ||
578 p_run_fpm_upgrade, 1, 240) PARAMETERS,
579 null CONFIG_PROJ_PERF_FLAG,
580 null CONFIG_COST_FLAG,
581 null CONFIG_PROFIT_FLAG,
582 null CONFIG_UTIL_FLAG,
583 sysdate START_DATE,
584 null END_DATE,
585 null COMPLETION_TEXT
586 from
587 dual;
588
589 -- PJI intermediate summarization tables
590 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_PRC_STATUS', 'NORMAL', null);
591 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_HELPER_BATCH_MAP', 'NORMAL', null);
592 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_PROJ_BATCH_MAP', 'NORMAL', null);
593 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DREVN', 'NORMAL', null);
594 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DINVC', 'NORMAL', null);
595 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DINVCITM', 'NORMAL', null);
596 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_ARINV', 'NORMAL', null);
597 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_FUNDG', 'NORMAL', null);
598 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_DLY_RATES', 'NORMAL', null);
599 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_CDL', 'NORMAL', null);
600 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_CRDL', 'NORMAL', null);
601 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_ERDL', 'NORMAL', null);
602 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_DNGL_FIN', 'NORMAL', null);
603 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN1', 'NORMAL', null);
604 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN2', 'NORMAL', null);
605 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN6', 'NORMAL', null);
606 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_DNGL_ACT', 'NORMAL', null);
607 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT1', 'NORMAL', null);
608 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT2', 'NORMAL', null);
609
610 -- Staging Tables
611
612 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES6', 'NORMAL', null);
613 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN9', 'NORMAL', null);
614 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT5', 'NORMAL', null);
615 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM_HEADER', 'NORMAL', null);
616 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN7', 'NORMAL', null);
617 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM', 'NORMAL', null);
618 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM1', 'NORMAL', null);
619 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_PJI_CMT', 'NORMAL', null); /* Added for bug 9317177 */
620 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT4', 'NORMAL', null);
621
622 -- Added for bug 6857368
623 -- Debug Tables
624
625 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_PLAN_LINES_DEBUG','NORMAL', null); /* Added for bug 6857368 */
626 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_XBS_ACCUM_TMP1_DEBUG','NORMAL', null); /* Added for bug 6857368 */
627
628 -- Added for bug 6603016
629 l_last_update_date := sysdate;
630 l_last_updated_by := FND_GLOBAL.USER_ID;
631 l_last_update_login := FND_GLOBAL.LOGIN_ID;
632
633 update PA_PROJECTS_ALL
634 set PJI_SOURCE_FLAG = null,
635 LAST_UPDATE_DATE = l_last_update_date,
636 LAST_UPDATED_BY = l_last_updated_by,
637 LAST_UPDATE_LOGIN = l_last_update_login
638 where PJI_SOURCE_FLAG = 'Y';
639 commit;
640 -- Added for bug 6603016 ends
641
642 if (p_truncate_pji_tables = 'Y') then
643
644 update FND_PROFILE_OPTION_VALUES
645 set PROFILE_OPTION_VALUE = 'Y'
646 where APPLICATION_ID = 1292 and
647 -- LEVEL_ID = 10001 and
648 PROFILE_OPTION_ID in
649 (select PROFILE_OPTION_ID
650 from FND_PROFILE_OPTIONS
651 where APPLICATION_ID = 1292 and
652 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
653
654 commit;
655
656 /* Temporary removal of stage 1 dependency on stage 2. temptemp
657 PJI_EXTRACTION_UTIL.TRUNCATE_PJI_PJI_TABLES
658 (
659 errbuf,
660 retcode,
661 'Y'
662 );
663
664 commit;
665 */
666
667 end if;
668
669 if (p_truncate_pjp_tables = 'Y') then
670
671 update FND_PROFILE_OPTION_VALUES
672 set PROFILE_OPTION_VALUE = 'Y'
673 where APPLICATION_ID = 1292 and
674 -- LEVEL_ID = 10001 and
675 PROFILE_OPTION_ID in
676 (select PROFILE_OPTION_ID
677 from FND_PROFILE_OPTIONS
678 where APPLICATION_ID = 1292 and
679 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
680
681 commit;
682
683 PJI_PJP_EXTRACTION_UTILS.TRUNCATE_PJP_TABLES
684 (
685 errbuf,
686 retcode,
687 'Y',
688 p_run_fpm_upgrade
689 );
690
691 commit;
692
693 end if;
694
695 update PJI_SYSTEM_CONFIG_HIST
696 set END_DATE = sysdate,
697 COMPLETION_TEXT = 'Normal completion'
698 where PROCESS_NAME = 'STAGE1' and
699 END_DATE is null;
700
701 commit;
702
703 retcode := 0;
704
705 exception when others then
706
707 rollback;
708
709 l_sqlerrm := substr(sqlerrm, 1, 240);
710
711 update PJI_SYSTEM_CONFIG_HIST
712 set END_DATE = sysdate,
713 COMPLETION_TEXT = l_sqlerrm
714 where PROCESS_NAME = 'STAGE1' and
715 END_DATE is null;
716
717 commit;
718
719 raise;
720
721 end TRUNCATE_PJI_TABLES;
722
723
724 -- -----------------------------------------------------
725 -- function GET_PARALLEL_PROCESSES
726 -- -----------------------------------------------------
727 function GET_PARALLEL_PROCESSES return number is
728
729 l_parallel_processes number;
730
731 begin
732
733 l_parallel_processes :=
734 trunc(to_number(FND_PROFILE.VALUE('PJI_EXTRACTION_PARALLELISM')), 0);
735
736 l_parallel_processes:= nvl(l_parallel_processes, 4);
737
738 l_parallel_processes:= greatest(l_parallel_processes, 2);
739
740 -- no upper limit on number of helpers
741 -- l_parallel_processes:= least(l_parallel_processes, 8);
742
743 return l_parallel_processes;
744
745 exception when others then
746
747 l_parallel_processes := 4;
748 return l_parallel_processes;
749
750 end GET_PARALLEL_PROCESSES;
751
752
753 -- -----------------------------------------------------
754 -- function GET_BATCH_SIZE
755 -- -----------------------------------------------------
756 function GET_BATCH_SIZE return number is
757
758 l_batch_size number;
759
760 begin
761 l_batch_size := TRUNC(to_number(FND_PROFILE.VALUE('PJI_EXTRACTION_BATCH_SIZE')),0);
762 l_batch_size:= GREATEST(l_batch_size,1000000);
763 l_batch_size:= NVL(l_batch_size,5000000);
764
765 return l_batch_size;
766
767 exception
768 when others then
769 l_batch_size:=5000000;
770 return l_batch_size;
771
772 end GET_BATCH_SIZE;
773
774
775 end PJI_EXTRACTION_UTIL;