[Home] [Help]
PACKAGE BODY: APPS.PJI_EXTRACTION_UTIL
Source
1 PACKAGE BODY PJI_EXTRACTION_UTIL as
2 /* $Header: PJIUT02B.pls 120.9 2006/07/22 02:44:42 svermett noship $ */
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
121
122 -- Some existing project might have got archived/purged since the last run. These
123 -- projects need to be updated, they should not be included in the current run.
124
125 For rec_purge_projs in csr_purge_projs LOOP
126
127 update PJI_PROJ_EXTR_STATUS extr
128 set extr.purge_status = rec_purge_projs.project_system_status_code
129 where extr.project_id = rec_purge_projs.project_id
130 and NVL(extr.purge_status, 'X') not in ('PARTIALLY_PURGED'
131 ,'PURGED'
132 ,'PENDING_PURGE')
133 ;
134
135 End LOOP;
136
137 end UPDATE_EXTR_SCOPE;
138
139
140 -- ----------------------------------------------------------
141 -- procedure POPULATE_ORG_EXTR_INFO
142 -- ----------------------------------------------------------
143 procedure POPULATE_ORG_EXTR_INFO is
144
145 begin
146
147 UPDATE_ORG_EXTR_INFO; -- PJI_ORG_EXTR_INFO always maintained incrementally
148
149 end POPULATE_ORG_EXTR_INFO;
150
151
152 -- ----------------------------------------------------------
153 -- procedure UPDATE_ORG_EXTR_INFO
154 -- ----------------------------------------------------------
155 procedure UPDATE_ORG_EXTR_INFO is
156
157 pragma AUTONOMOUS_TRANSACTION;
158
159 l_ent_cal_min_date number;
160 l_ent_cal_max_date number;
161
162 begin
163
164 begin
165
166 select
167 to_number(to_char(min(START_DATE), 'J')),
168 to_number(to_char(max(END_DATE), 'J'))
169 into
170 l_ent_cal_min_date,
171 l_ent_cal_max_date
172 from
173 PJI_TIME_ENT_PERIOD_V;
174
175 exception when no_data_found then null;
176
177 end;
178
179 insert into PJI_ORG_EXTR_INFO
180 (
181 ORG_ID,
182 PF_CURRENCY_CODE,
183 EN_CALENDAR_MIN_DATE,
184 EN_CALENDAR_MAX_DATE,
185 GL_CALENDAR_ID,
186 GL_CALENDAR_MIN_DATE,
187 GL_CALENDAR_MAX_DATE,
188 PA_CALENDAR_ID,
189 PA_CALENDAR_MIN_DATE,
190 PA_CALENDAR_MAX_DATE
191 )
192 select
193 -1, -- -1 can be a valid operating unit when a row
194 'PJI$NULL', -- is only a receiver row or only a provider
195 to_number(null), -- row. When a row applies to both receiver
196 to_number(null), -- and provider, ord_id will never be -1.
197 to_number(null),
198 to_number(null), -- Added to_number for bug 3621077
199 to_number(null),
200 to_number(null),
201 to_number(null),
202 to_number(null)
203 from
204 dual
205 where
206 not exists (select ORG_ID
207 from PA_IMPLEMENTATIONS_ALL
208 where ORG_ID is null) and
209 -1 not in (select ORG_ID
210 from PJI_ORG_EXTR_INFO)
211 union all
212 select
213 nvl(imp.ORG_ID,-1) ORG_ID,
214 to_char(null),
215 to_number(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 from
224 PA_IMPLEMENTATIONS_ALL imp
225 where
226 imp.ORG_ID not in (select ORG_ID
227 from PJI_ORG_EXTR_INFO);
228
229 update PJI_ORG_EXTR_INFO info
230 set (PF_CURRENCY_CODE,
231 EN_CALENDAR_MIN_DATE,
232 EN_CALENDAR_MAX_DATE,
233 GL_CALENDAR_ID,
234 GL_CALENDAR_MIN_DATE,
235 GL_CALENDAR_MAX_DATE,
236 PA_CALENDAR_ID,
237 PA_CALENDAR_MIN_DATE,
238 PA_CALENDAR_MAX_DATE) =
239 (select
240 gl.CURRENCY_CODE,
241 l_ent_cal_min_date,
242 l_ent_cal_max_date,
243 gl.CALENDAR_ID,
244 to_number(to_char(gl.START_DATE, 'J')),
245 to_number(to_char(gl.END_DATE, 'J')),
246 pa.CALENDAR_ID,
247 to_number(to_char(pa.START_DATE, 'J')),
248 to_number(to_char(pa.END_DATE, 'J'))
249 from
250 (
251 select
252 nvl(imp.ORG_ID,-1) ORG_ID,
253 sob.CURRENCY_CODE,
254 min(glp.START_DATE) START_DATE,
255 max(glp.END_DATE) END_DATE,
256 fii.CALENDAR_ID
257 from
258 PA_IMPLEMENTATIONS_ALL imp,
259 GL_SETS_OF_BOOKS sob,
260 GL_PERIODS glp,
261 FII_TIME_CAL_NAME fii
262 where
263 imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
264 sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
265 sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
266 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
267 fii.PERIOD_TYPE = glp.PERIOD_TYPE
268 group by
269 nvl(imp.ORG_ID,-1),
270 sob.CURRENCY_CODE,
271 fii.CALENDAR_ID
272 ) gl,
273 (
274 select
275 nvl(imp.ORG_ID,-1) ORG_ID,
276 min(glp.START_DATE) START_DATE,
277 max(glp.END_DATE) END_DATE,
278 fii.CALENDAR_ID
279 from
280 PA_IMPLEMENTATIONS_ALL imp,
281 GL_PERIODS glp,
282 FII_TIME_CAL_NAME fii
283 where
284 imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
285 imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
286 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
287 fii.PERIOD_TYPE = glp.PERIOD_TYPE
288 group by
289 nvl(imp.ORG_ID,-1),
290 fii.CALENDAR_ID
291 ) pa
292 where
293 gl.ORG_ID = pa.ORG_ID and
294 gl.ORG_ID = info.ORG_ID)
295 where
296 (nvl(ORG_ID, -1),
297 nvl(PF_CURRENCY_CODE, 'PJI$NULL1'),
298 nvl(EN_CALENDAR_MIN_DATE, 1),
299 nvl(EN_CALENDAR_MAX_DATE, 1),
300 nvl(GL_CALENDAR_ID, -1),
301 nvl(GL_CALENDAR_MIN_DATE, 1),
302 nvl(GL_CALENDAR_MAX_DATE, 1),
303 nvl(PA_CALENDAR_ID, -1),
304 nvl(PA_CALENDAR_MIN_DATE, 1),
305 nvl(PA_CALENDAR_MAX_DATE, 1)) not in
306 (select
307 nvl(gl.ORG_ID, -1),
308 nvl(gl.CURRENCY_CODE, 'PJI$NULL2'),
309 nvl(l_ent_cal_min_date, 2),
310 nvl(l_ent_cal_max_date, 2),
311 nvl(gl.CALENDAR_ID, -2),
312 nvl(to_number(to_char(gl.START_DATE, 'J')), 2),
313 nvl(to_number(to_char(gl.END_DATE, 'J')), 2),
314 nvl(pa.CALENDAR_ID, -2),
315 nvl(to_number(to_char(pa.START_DATE, 'J')), 2),
316 nvl(to_number(to_char(pa.END_DATE, 'J')), 2)
317 from
318 (
319 select
320 nvl(imp.ORG_ID,-1) ORG_ID,
321 sob.CURRENCY_CODE,
322 min(glp.START_DATE) START_DATE,
323 max(glp.END_DATE) END_DATE,
324 fii.CALENDAR_ID
325 from
326 PA_IMPLEMENTATIONS_ALL imp,
327 GL_SETS_OF_BOOKS sob,
328 GL_PERIODS glp,
329 FII_TIME_CAL_NAME fii
330 where
331 imp.SET_OF_BOOKS_ID = sob.SET_OF_BOOKS_ID and
332 sob.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
333 sob.ACCOUNTED_PERIOD_TYPE = glp.PERIOD_TYPE and
334 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
335 fii.PERIOD_TYPE = glp.PERIOD_TYPE
336 group by
337 nvl(imp.ORG_ID,-1),
338 sob.CURRENCY_CODE,
339 fii.CALENDAR_ID
340 ) gl,
341 (
342 select
343 nvl(imp.ORG_ID,-1) ORG_ID,
344 min(glp.START_DATE) START_DATE,
345 max(glp.END_DATE) END_DATE,
346 fii.CALENDAR_ID
347 from
348 PA_IMPLEMENTATIONS_ALL imp,
349 GL_PERIODS glp,
350 FII_TIME_CAL_NAME fii
351 where
352 imp.PA_PERIOD_TYPE = glp.PERIOD_TYPE and
353 imp.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
354 fii.PERIOD_SET_NAME = glp.PERIOD_SET_NAME and
355 fii.PERIOD_TYPE = glp.PERIOD_TYPE
356 group by
357 nvl(imp.ORG_ID,-1),
358 fii.CALENDAR_ID
359 ) pa
360 where
361 gl.ORG_ID = pa.ORG_ID and
362 gl.ORG_ID = info.ORG_ID);
363
364 update PJI_ORG_EXTR_INFO
365 set PF_CURRENCY_CODE = 'PJI$NULL'
366 where ORG_ID = -1 and
367 nvl(PF_CURRENCY_CODE, 'x') <> 'PJI$NULL';
368
369 update PJI_ORG_EXTR_INFO
370 set EN_CALENDAR_MIN_DATE = l_ent_cal_min_date,
371 EN_CALENDAR_MAX_DATE = l_ent_cal_max_date
372 where ORG_ID <> -1 and
373 (nvl(EN_CALENDAR_MIN_DATE, 1) <> l_ent_cal_min_date or
374 nvl(EN_CALENDAR_MAX_DATE, 1) <> l_ent_cal_max_date);
375
376 commit; -- we can commit since transaction is autonomous
377
378 end UPDATE_ORG_EXTR_INFO;
379
380
381 /* ------------------------------------------------------
382 Procedure : SEED_PJI_FM_STATS
383 -----------------------------------------------------*/
384
385 PROCEDURE SEED_PJI_FM_STATS IS
386
387 l_high_rows number;
388 l_med_rows number;
389 l_low_rows number;
390 l_db_block_size number;
391 l_high_blocks number;
392 l_med_blocks number;
393 l_low_blocks number;
394
395 l_schema varchar2(30);
396 l_degree number;
397
398 BEGIN
399 /* This procedure sets statistics for all PJI_FM intermediate tables
400 *
401 * Presently this procedure sets statistics for only
402 * the first partition for partitioned tables since
403 * there will be only one worker for Phase I. Later when
404 * this restriction is removed then the statistics need to
405 * be set for other partitions too.
406 *
407 * The tables are divided into 3 broad categories: high medium and low
408 * The statistics seeded are based on 3 sets of parameters:
409 * Number of rows Average row length
410 * High batch_size 225
411 * Medium batch_size/50 150
412 * Low batch_size/150 75
413 *
414 * Blocks = 1.5* (number of rows * average row length)/block size
415 *
416 * A factor of 1.5 is assumed for row chaining into multiple blocks
417 *
418 */
419
420
421 l_high_rows := GET_BATCH_SIZE;
422 l_med_rows := l_high_rows/50;
423 l_low_rows := l_high_rows/150;
424
425 select to_number(value)
426 into l_db_block_size
427 from v$parameter
428 where name = 'db_block_size'
429 ;
430
431 l_high_blocks := 1.25*(l_high_rows*225)/l_db_block_size;
432 l_med_blocks := 1.25*(l_med_rows*150)/l_db_block_size;
433 l_low_blocks := 1.25*(l_low_rows*75)/l_db_block_size;
434
435
436 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
437 l_degree := PJI_UTILS.GET_DEGREE_OF_PARALLELISM();
438
439 -- non-partitioned tables
440 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT1' , l_high_rows, l_high_blocks, 225);
441 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT2' , l_high_rows, l_high_blocks, 225);
442 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_ACT4' , l_high_rows, l_high_blocks, 225);
443 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN1' , l_high_rows, l_high_blocks, 225);
444 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_FIN2' , l_high_rows, l_high_blocks, 225);
445 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_AGGR_DLY_RATES', l_low_rows, l_low_blocks, 75);
446 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_ARINV' , l_med_rows, l_med_blocks, 150);
447 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DINVC' , l_med_rows, l_med_blocks, 150);
448 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DINVCITM' , l_med_rows, l_med_blocks, 150);
449 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_DREVN' , l_med_rows, l_med_blocks, 150);
450 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_EXTR_FUNDG' , l_med_rows, l_med_blocks, 150);
451 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_CDL' , l_high_rows, l_high_blocks, 225);
452 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_CRDL' , l_high_rows, l_high_blocks, 225);
453 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_REXT_ERDL' , l_high_rows, l_high_blocks, 225);
454 FND_STATS.SET_TABLE_STATS(l_schema,'PJI_FM_PROJ_BATCH_MAP' , l_low_rows, l_low_blocks, 75);
455
456 -- gather statistics for PJI metadata tables
457 FND_STATS.GATHER_TABLE_STATS(
458 ownname => l_schema
459 , tabname => 'PJI_PROJ_EXTR_STATUS'
460 , percent => 10
461 , degree => l_degree
462 );
463 FND_STATS.GATHER_INDEX_STATS(ownname => l_schema,
464 indname => 'PJI_PROJ_EXTR_STATUS_U1',
465 percent => 10);
466 -- Commenting this because the table is used in Stage2 Summarization . Bug#4997700
467 /* FND_STATS.GATHER_TABLE_STATS(
468 ownname => l_schema
469 , tabname => 'PJI_PROJECT_CLASSES'
470 , percent => 10
471 , degree => l_degree
472 ); */
473 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
474 tabname => 'PJI_FM_PROJ_BATCH_MAP',
475 percent => 10,
476 degree => l_degree);
477 FND_STATS.GATHER_COLUMN_STATS(ownname => l_schema,
478 tabname => 'PJI_FM_PROJ_BATCH_MAP',
479 colname => 'EXTRACTION_TYPE',
480 percent => 10,
481 degree => l_degree);
482 FND_STATS.GATHER_INDEX_STATS(ownname => l_schema,
483 indname => 'PJI_FM_PROJ_BATCH_MAP_U1',
484 percent => 10);
485
486 END SEED_PJI_FM_STATS;
487
488
489 -- -----------------------------------------------------
490 -- procedure TRUNCATE_PJI_TABLES
491 --
492 -- This procedure resets the summarization process by
493 -- truncating all PJI stage 1 summarization tables.
494 --
495 -- -----------------------------------------------------
496 procedure TRUNCATE_PJI_TABLES
497 (
498 errbuf out nocopy varchar2,
499 retcode out nocopy varchar2,
500 p_check in varchar2 default 'N',
501 p_truncate_pji_tables in varchar2 default 'Y',
502 p_truncate_pjp_tables in varchar2 default 'Y',
503 p_run_fpm_upgrade in varchar2 default 'N'
504 ) is
505
506 l_profile_check varchar2(30);
507 l_pji_schema varchar2(30);
508 l_sqlerrm varchar2(240);
509
510 begin
511
512 l_profile_check := FND_PROFILE.VALUE('PJI_SUM_CLEANALL');
513
514 FND_MESSAGE.SET_NAME('PJI', 'PJI_SUM_CLEANALL_FAILED');
515
516 if (upper(nvl(l_profile_check, 'N')) <> 'Y') then
517 pji_utils.write2out(FND_MESSAGE.GET);
518 commit;
519 retcode := 1;
520 return;
521 end if;
522
523 if (upper(nvl(p_check, 'N')) <> 'Y') then
524 pji_utils.write2out(FND_MESSAGE.GET);
525 commit;
526 retcode := 1;
527 return;
528 end if;
529
530 update FND_PROFILE_OPTION_VALUES
531 set PROFILE_OPTION_VALUE = 'N'
532 where APPLICATION_ID = 1292 and
533 -- LEVEL_ID = 10001 and
534 PROFILE_OPTION_ID in
535 (select PROFILE_OPTION_ID
536 from FND_PROFILE_OPTIONS
537 where APPLICATION_ID = 1292 and
538 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
539
540 commit;
541
542 l_pji_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
543
544 -- PJI summarization tables with persistent data
545 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_MT_PRC_STEPS', 'NORMAL', null);
546 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_CONFIG_HIST', 'NORMAL', null);
547 delete from PJI_SYSTEM_PARAMETERS where NAME not in ('PJI_PJP_ENT_CURR_REP_PERIOD');
548 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_PROJ_EXTR_STATUS', 'NORMAL', null);
549 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_DEBUG_MSG', 'NORMAL', null);
550
551 insert into PJI_SYSTEM_CONFIG_HIST
552 (
553 REQUEST_ID,
554 USER_NAME,
555 PROCESS_NAME,
556 RUN_TYPE,
557 PARAMETERS,
558 CONFIG_PROJ_PERF_FLAG,
559 CONFIG_COST_FLAG,
560 CONFIG_PROFIT_FLAG,
561 CONFIG_UTIL_FLAG,
562 START_DATE,
563 END_DATE,
564 COMPLETION_TEXT
565 )
566 select
567 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
568 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
569 'STAGE1' PROCESS_NAME,
570 'CLEANALL' RUN_TYPE,
571 substr(p_check || ', ' ||
572 p_truncate_pji_tables || ', ' ||
573 p_truncate_pjp_tables || ', ' ||
574 p_run_fpm_upgrade, 1, 240) PARAMETERS,
575 null CONFIG_PROJ_PERF_FLAG,
576 null CONFIG_COST_FLAG,
577 null CONFIG_PROFIT_FLAG,
578 null CONFIG_UTIL_FLAG,
579 sysdate START_DATE,
580 null END_DATE,
581 null COMPLETION_TEXT
582 from
583 dual;
584
585 -- PJI intermediate summarization tables
586 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_SYSTEM_PRC_STATUS', 'NORMAL', null);
587 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_HELPER_BATCH_MAP', 'NORMAL', null);
588 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_PROJ_BATCH_MAP', 'NORMAL', null);
589 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DREVN', 'NORMAL', null);
590 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DINVC', 'NORMAL', null);
591 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_DINVCITM', 'NORMAL', null);
592 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_ARINV', 'NORMAL', null);
593 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_EXTR_FUNDG', 'NORMAL', null);
594 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_DLY_RATES', 'NORMAL', null);
595 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_CDL', 'NORMAL', null);
596 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_CRDL', 'NORMAL', null);
597 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_REXT_ERDL', 'NORMAL', null);
598 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_DNGL_FIN', 'NORMAL', null);
599 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN1', 'NORMAL', null);
600 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN2', 'NORMAL', null);
601 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN6', 'NORMAL', null);
602 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_DNGL_ACT', 'NORMAL', null);
603 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT1', 'NORMAL', null);
604 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT2', 'NORMAL', null);
605
606 -- Staging Tables
607
608 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_RM_AGGR_RES6', 'NORMAL', null);
609 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN9', 'NORMAL', null);
610 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT5', 'NORMAL', null);
611 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM_HEADER', 'NORMAL', null);
612 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_FIN7', 'NORMAL', null);
613 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM', 'NORMAL', null);
614 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FP_TXN_ACCUM1', 'NORMAL', null);
615 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_pji_schema, 'PJI_FM_AGGR_ACT4', 'NORMAL', null);
616
617 if (p_truncate_pji_tables = 'Y') then
618
619 update FND_PROFILE_OPTION_VALUES
620 set PROFILE_OPTION_VALUE = 'Y'
621 where APPLICATION_ID = 1292 and
622 -- LEVEL_ID = 10001 and
623 PROFILE_OPTION_ID in
624 (select PROFILE_OPTION_ID
625 from FND_PROFILE_OPTIONS
626 where APPLICATION_ID = 1292 and
627 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
628
629 commit;
630
631 /* Temporary removal of stage 1 dependency on stage 2. temptemp
632 PJI_EXTRACTION_UTIL.TRUNCATE_PJI_PJI_TABLES
633 (
634 errbuf,
635 retcode,
636 'Y'
637 );
638
639 commit;
640 */
641
642 end if;
643
644 if (p_truncate_pjp_tables = 'Y') then
645
646 update FND_PROFILE_OPTION_VALUES
647 set PROFILE_OPTION_VALUE = 'Y'
648 where APPLICATION_ID = 1292 and
649 -- LEVEL_ID = 10001 and
650 PROFILE_OPTION_ID in
651 (select PROFILE_OPTION_ID
652 from FND_PROFILE_OPTIONS
653 where APPLICATION_ID = 1292 and
654 PROFILE_OPTION_NAME = 'PJI_SUM_CLEANALL');
655
656 commit;
657
658 PJI_PJP_EXTRACTION_UTILS.TRUNCATE_PJP_TABLES
659 (
660 errbuf,
661 retcode,
662 'Y',
663 p_run_fpm_upgrade
664 );
665
666 commit;
667
668 end if;
669
670 update PJI_SYSTEM_CONFIG_HIST
671 set END_DATE = sysdate,
672 COMPLETION_TEXT = 'Normal completion'
673 where PROCESS_NAME = 'STAGE1' and
674 END_DATE is null;
675
676 commit;
677
678 retcode := 0;
679
680 exception when others then
681
682 rollback;
683
684 l_sqlerrm := substr(sqlerrm, 1, 240);
685
686 update PJI_SYSTEM_CONFIG_HIST
687 set END_DATE = sysdate,
688 COMPLETION_TEXT = l_sqlerrm
689 where PROCESS_NAME = 'STAGE1' and
690 END_DATE is null;
691
692 commit;
693
694 raise;
695
696 end TRUNCATE_PJI_TABLES;
697
698
699 -- -----------------------------------------------------
700 -- function GET_PARALLEL_PROCESSES
701 -- -----------------------------------------------------
702 function GET_PARALLEL_PROCESSES return number is
703
704 l_parallel_processes number;
705
706 begin
707
708 l_parallel_processes :=
709 trunc(to_number(FND_PROFILE.VALUE('PJI_EXTRACTION_PARALLELISM')), 0);
710
711 l_parallel_processes:= nvl(l_parallel_processes, 4);
712
713 l_parallel_processes:= greatest(l_parallel_processes, 2);
714
715 -- no upper limit on number of helpers
716 -- l_parallel_processes:= least(l_parallel_processes, 8);
717
718 return l_parallel_processes;
719
720 exception when others then
721
722 l_parallel_processes := 4;
723 return l_parallel_processes;
724
725 end GET_PARALLEL_PROCESSES;
726
727
728 -- -----------------------------------------------------
729 -- function GET_BATCH_SIZE
730 -- -----------------------------------------------------
731 function GET_BATCH_SIZE return number is
732
733 l_batch_size number;
734
735 begin
736 l_batch_size := TRUNC(to_number(FND_PROFILE.VALUE('PJI_EXTRACTION_BATCH_SIZE')),0);
737 l_batch_size:= GREATEST(l_batch_size,1000000);
738 l_batch_size:= NVL(l_batch_size,5000000);
739
740 return l_batch_size;
741
742 exception
743 when others then
744 l_batch_size:=5000000;
745 return l_batch_size;
746
747 end GET_BATCH_SIZE;
748
749
750 end PJI_EXTRACTION_UTIL;