[Home] [Help]
PACKAGE BODY: APPS.PJI_RM_SUM_MAIN
Source
1 package body PJI_RM_SUM_MAIN as
2 /* $Header: PJISR01B.pls 120.15 2006/03/23 04:02:24 appldev noship $ */
3
4 -- The main procedure is procedure SUMMARIZE, it is invoked from a concurrent
5 -- program.
6 --
7 -- Data extraction consists of several steps that are coded as individual
8 -- program units. Procedure RUN_PROCESS invokes processing steps in the
9 -- appropriate order. Before we call RUN_PROCESS we initialize the process,
10 -- this is done in INIT_PROCESS. After process completes successfully we
11 -- reset process status in WRAPUP_PROCESS. These are three main program
12 -- units called from PUSH.
13 --
14 -- Each processing step listed in RUN_PROCESS is implemented as separate
15 -- database transaction. Extraction process supports error recovery, i.e.
16 -- if the process fails it will continue from the same step next time it
17 -- runs. In order to support error recovery we use tables
18 -- PJI_SYSTEM_PROCESS_STATUS and PJI_SYSTEM_PARAMETERS that store current
19 -- process status and process parameters such as batch id being processed.
20 -- The package accesses status tables through API package PJI_PROCESS_UTIL.
21 -- Scope of data extraction is defined by table PJI_ORG_EXTR_STATUS that
22 -- contains list of organizations for which we extract data.
23 -- We have to use persistent table, i.e. we cannot define scope dynamically
24 -- each time we run extraction because Table PJI_ORG_EXTR_STATUS may be
25 -- updated by multiple Project Intelligence data extraction processes running
26 -- in parallel.
27 -- In order to isolate the process from changes made to the table by
28 -- other extraction programs we create a snapshot of this table in
29 -- PJI_RM_ORG_BATCH_MAP. PJI_RM_ORG_BATCH_MAP is also used to partition
30 -- processing scope into batches.
31 --
32 -- Processing itself consists of two major stages. First, we process orgs
33 -- for which we never extracted fi information before. One of reasons to
34 -- have this stage is to support initial data load. These orgs have
35 -- STATUS in the scope table set to NULL.
36 -- The second stage is to process orgs that have been
37 -- extracted before. For these orgs STATUS is set to 'X'.
38
39 -- -----------------------------------------------------
40 -- procedure RUN_SETUP
41 -- -----------------------------------------------------
42 procedure RUN_SETUP is
43
44 l_transition_flag varchar2(1);
45
46 l_settings_proj_perf_flag varchar2(1);
47 l_settings_cost_flag varchar2(1);
48 l_settings_profit_flag varchar2(1);
49 l_settings_util_flag varchar2(1);
50
51 l_params_proj_perf_flag varchar2(1);
52 l_params_cost_flag varchar2(1);
53 l_params_profit_flag varchar2(1);
54 l_params_util_flag varchar2(1);
55
56 l_row_count number;
57 l_no_setup_error varchar2(255) := 'Environment is not setup to run summarization. Check PJI setup and BIS profiles.';
58 l_setup_error varchar2(255) := 'Turning off an active functional area is not allowed.';
59 l_dangling_error varchar2(255) := 'Cannot run a configuration transition when dangling rows exist.';
60
61 begin
62
63 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'TRANSITION') is not null) then
64 return;
65 end if;
66
67 select
68 CONFIG_PROJ_PERF_FLAG,
69 CONFIG_COST_FLAG,
70 CONFIG_PROFIT_FLAG,
71 CONFIG_UTIL_FLAG
72 into
73 l_settings_proj_perf_flag,
74 l_settings_cost_flag,
75 l_settings_profit_flag,
76 l_settings_util_flag
77 from
78 PJI_SYSTEM_SETTINGS;
79
80 if ((l_settings_proj_perf_flag is null and
81 l_settings_cost_flag is null and
82 l_settings_profit_flag is null and
83 l_settings_util_flag is null) or
84 FND_PROFILE.VALUE('BIS_GLOBAL_START_DATE') is null or
85 FND_PROFILE.VALUE('BIS_PRIMARY_CURRENCY_CODE') is null or
86 FND_PROFILE.VALUE('BIS_PRIMARY_RATE_TYPE') is null or
87 FND_PROFILE.VALUE('BIS_ENTERPRISE_CALENDAR') is null or
88 FND_PROFILE.VALUE('BIS_PERIOD_TYPE') is null) then
89
90 rollback;
91 dbms_standard.raise_application_error(-20044, l_no_setup_error);
92
93 end if;
94
95 l_settings_proj_perf_flag := nvl(l_settings_proj_perf_flag, 'N');
96 l_settings_cost_flag := nvl(l_settings_cost_flag, 'N');
97 l_settings_profit_flag := nvl(l_settings_profit_flag, 'N');
98 l_settings_util_flag := nvl(l_settings_util_flag, 'N');
99
100 l_params_proj_perf_flag :=
101 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_PROJ_PERF_FLAG'), 'N');
102 l_params_cost_flag :=
103 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_COST_FLAG'), 'N');
104 l_params_profit_flag :=
105 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_PROFIT_FLAG'), 'N');
106 l_params_util_flag :=
107 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_UTIL_FLAG'), 'N');
108
109 if (l_settings_profit_flag = 'Y' and l_settings_cost_flag = 'N') then
110 update PJI_SYSTEM_SETTINGS
111 set CONFIG_COST_FLAG = 'Y';
112 l_settings_cost_flag := 'Y';
113 end if;
114
115 if (l_settings_cost_flag = 'Y' and l_settings_proj_perf_flag = 'N') then
116 update PJI_SYSTEM_SETTINGS
117 set CONFIG_PROJ_PERF_FLAG = 'Y';
118 l_settings_proj_perf_flag := 'Y';
119 end if;
120
121 if ((l_settings_proj_perf_flag = 'N' and l_params_proj_perf_flag = 'Y') or
122 (l_settings_cost_flag = 'N' and l_params_cost_flag = 'Y') or
123 (l_settings_profit_flag = 'N' and l_params_profit_flag = 'Y') or
124 (l_settings_util_flag = 'N' and l_params_util_flag = 'Y')) then
125 PJI_UTILS.WRITE2LOG('Error: ' || l_setup_error);
126 commit;
127 dbms_standard.raise_application_error(-20040, l_setup_error);
128 end if;
129
130 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process, 'TRANSITION', 'N');
131 l_transition_flag := 'N';
132
133 if (l_settings_proj_perf_flag = 'Y' and l_params_proj_perf_flag = 'N') then
134 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
135 (g_process, 'TRANSITION', 'Y');
136 l_transition_flag := 'Y';
137 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
138 (g_process, 'CONFIG_PROJ_PERF_FLAG', 'Y');
139 end if;
140
141 if (l_settings_cost_flag = 'Y' and l_params_cost_flag = 'N') then
142 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
143 (g_process, 'TRANSITION', 'Y');
144 l_transition_flag := 'Y';
145 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
146 (g_process, 'CONFIG_COST_FLAG', 'Y');
147 end if;
148
149 if (l_settings_profit_flag = 'Y' and l_params_profit_flag = 'N') then
150 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
151 (g_process, 'TRANSITION', 'Y');
152 l_transition_flag := 'Y';
153 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
154 (g_process, 'CONFIG_PROFIT_FLAG', 'Y');
155 end if;
156
157 if (l_settings_util_flag = 'Y' and l_params_util_flag = 'N') then
158 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
159 (g_process, 'TRANSITION', 'Y');
160 l_transition_flag := 'Y';
161 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
162 (g_process, 'CONFIG_UTIL_FLAG', 'Y');
163 end if;
164
165 select count(*)
166 into l_row_count
167 from PJI_RM_DNGL_RES;
168
169 if (l_row_count > 0 and l_transition_flag = 'Y') then
170
171 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER
172 (g_process, 'TRANSITION', 'N');
173 l_transition_flag := 'N';
174
175 PJI_UTILS.WRITE2LOG('Error: ' || l_dangling_error);
176 PJI_UTILS.WRITE2OUT('Error: ' || l_dangling_error);
177
178 end if;
179
180 if (l_transition_flag = 'Y') then
181
182 insert into PJI_SYSTEM_CONFIG_HIST
183 (
184 REQUEST_ID,
185 USER_NAME,
186 PROCESS_NAME,
187 RUN_TYPE,
188 PARAMETERS,
189 CONFIG_PROJ_PERF_FLAG,
190 CONFIG_COST_FLAG,
191 CONFIG_PROFIT_FLAG,
192 CONFIG_UTIL_FLAG,
193 START_DATE,
194 END_DATE,
195 COMPLETION_TEXT
196 )
197 select
198 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
199 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
200 g_process || 1 PROCESS_NAME,
201 'TRANSITION' RUN_TYPE,
202 null PARAMETERS,
203 l_settings_proj_perf_flag CONFIG_PROJ_PERF_FLAG,
204 l_settings_cost_flag CONFIG_COST_FLAG,
205 l_settings_profit_flag CONFIG_PROFIT_FLAG,
206 l_settings_util_flag CONFIG_UTIL_FLAG,
207 sysdate START_DATE,
208 null END_DATE,
209 null COMPLETION_TEXT
210 from
211 dual;
212
213 end if;
214
215 end RUN_SETUP;
216
217
218 -- -----------------------------------------------------
219 -- function PRIOR_ITERATION_SUCCESSFUL
220 -- -----------------------------------------------------
221 function PRIOR_ITERATION_SUCCESSFUL return boolean is
222
223 l_parallel_processes number;
224 l_count number;
225
226 l_sum_fm_fail varchar2(255) := 'The process has failed because process ''Update Project Financial Data'' failed.';
227
228 l_sum_fm_running varchar2(255) := 'The process has failed because process ''Update Project Financial Data'' is currently running';
229
230 l_sum_rm_running varchar2(255) := 'The process has failed because process ''Update Project Resource Management Data'' is currently running.';
231
232 begin
233
234 l_count := 0;
235
236 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process) <>
237 FND_GLOBAL.CONC_REQUEST_ID and
238 (PJI_PROCESS_UTIL.REQUEST_STATUS
239 (
240 'RUNNING',
241 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
242 g_full_disp_name
243 ) or
244 PJI_PROCESS_UTIL.REQUEST_STATUS
245 (
246 'RUNNING',
247 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
248 g_incr_disp_name
249 ) or
250 PJI_PROCESS_UTIL.REQUEST_STATUS
251 (
252 'RUNNING',
253 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
254 g_prtl_disp_name
255 ))) then
256 l_count := l_count + 1;
257 end if;
258
259 l_parallel_processes :=
260 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PARALLEL_PROCESSES');
261
262 if (l_parallel_processes is not null) then
263
264 for x in 2 .. l_parallel_processes loop
265 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
266 (
267 g_process || to_char(x),
268 g_process || to_char(x)
269 ) < FND_GLOBAL.CONC_REQUEST_ID and
270 PJI_RM_SUM_EXTR.WORKER_STATUS(x, 'RUNNING')) then
271 l_count := l_count + 1;
272 end if;
273 end loop;
274
275 end if;
276
277 if (l_count > 0) then
278 pji_utils.write2log('Error: RM summarization is already running.');
279 commit;
280 dbms_standard.raise_application_error(-20010, l_sum_rm_running);
281 end if;
282
283 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
284 'PROCESS_RUNNING',
285 'Y');
286
287 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
288 g_process,
289 FND_GLOBAL.CONC_REQUEST_ID);
290
291 commit;
292
293 -- API call below check if table PJI_SYSTEM_PRC_STATUS has any
294 -- records for this process. If records exist, this means that prior
295 -- process did not complete successfully.
296
297 return PJI_PROCESS_UTIL.PRIOR_ITERATION_SUCCESSFUL(g_process);
298
299 end PRIOR_ITERATION_SUCCESSFUL;
300
301
302 -- -----------------------------------------------------
303 -- procedure INIT_PROCESS
304 -- -----------------------------------------------------
305 procedure INIT_PROCESS
306 (
307 p_run_mode in varchar2,
308 p_prtl_schedule in varchar2 default null, -- RM
309 p_organization_id in number default null, -- RM parameter
310 p_include_sub_org in varchar2 default 'N', -- RM parameter
311 p_prtl_financial in varchar2 default null, -- FM
312 p_operating_unit in number default null, -- FM parameter
313 p_from_project in varchar2 default null, -- FM parameter
314 p_to_project in varchar2 default null, -- FM parameter
315 p_plan_type in varchar2 default null -- FM parameter
316 ) is
317
318 l_process varchar2(30);
319
320 l_org_count number;
321 l_organization_id number;
322 l_hierarchical varchar2(1);
323 l_global_start_date date;
324 l_extraction_type varchar2(30);
325 l_transition_flag varchar2(1);
326 l_errbuf varchar2(2000);
327 l_retcode varchar2(2000);
328
329 l_project_count number;
330 p_from_project_id number;
331 p_to_project_id number;
332 l_count number;
333 l_from_project_num pa_projects_all.segment1%TYPE;
334 l_to_project_num pa_projects_all.segment1%TYPE;
335 l_invalid_parameter varchar2(255) := 'The specified range of projects is invalid, To Project should be greater than From Project ';
336 l_no_work varchar2(255) := 'There is no project to process for the specified parameters';
337
338 begin
339
340 l_process := PJI_RM_SUM_MAIN.g_process;
341
342 if (p_run_mode = 'F') then
343 l_extraction_type := 'FULL';
344 elsif (p_run_mode = 'I') then
345 l_extraction_type := 'INCREMENTAL';
346 elsif (p_run_mode = 'P') then
347 l_extraction_type := 'PARTIAL';
348 end if;
349
350 select count(*)
351 into l_org_count
352 from PJI_ORG_EXTR_STATUS
353 where ROWNUM = 1;
354
355 select count(*)
356 into l_project_count
357 from PJI_PJI_PROJ_EXTR_STATUS
358 where ROWNUM = 1;
359
360 if (l_org_count = 0 and l_project_count = 0) then
361 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,l_retcode,'All','?','Y');
362 end if;
363
364 if (upper(nvl(FND_PROFILE.VALUE('PJI_USE_DBI_RSG'), 'N')) <> 'Y' ) then -- bug#5075209
365
366 PJI_PJI_EXTRACTION_UTILS.MVIEW_REFRESH(l_errbuf,
367 l_retcode,
368 'PJI_TIME_PA_RPT_STR_MV',
369 'C',
370 'N');
371 end if;
372
373 l_transition_flag :=
374 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_RM_SUM_MAIN.g_process,
375 'TRANSITION');
376
377 if ((l_org_count = 0 and
378 l_project_count = 0) or l_transition_flag = 'Y') then
379 l_extraction_type := 'FULL';
380 elsif ((l_org_count > 0 or
381 l_project_count > 0) and l_extraction_type = 'FULL') then
382 l_extraction_type := 'INCREMENTAL';
383 end if;
384
385 PJI_PROCESS_UTIL.ADD_STEPS(g_process || 1, 'PJI_PJI', l_extraction_type);
386
387 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(g_process || 1, 'PJI_RM_SUM_MAIN.INIT_PROCESS;')) then
388 rollback;
389 return;
390 end if;
391
392 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(l_process,
393 'EXTRACTION_TYPE',
394 l_extraction_type);
395
396 insert into PJI_SYSTEM_CONFIG_HIST
397 (
398 REQUEST_ID,
399 USER_NAME,
400 PROCESS_NAME,
401 RUN_TYPE,
402 PARAMETERS,
403 CONFIG_PROJ_PERF_FLAG,
404 CONFIG_COST_FLAG,
405 CONFIG_PROFIT_FLAG,
406 CONFIG_UTIL_FLAG,
407 START_DATE,
408 END_DATE,
409 COMPLETION_TEXT
410 )
411 select
412 FND_GLOBAL.CONC_REQUEST_ID REQUEST_ID,
413 substr(FND_GLOBAL.USER_NAME, 1, 10) USER_NAME,
414 g_process || 1 PROCESS_NAME,
415 l_extraction_type RUN_TYPE,
416 substr(p_run_mode || ', ' ||
417 p_prtl_schedule || ', ' ||
418 to_char(p_organization_id) || ', ' ||
419 p_include_sub_org || ', ' ||
420 p_prtl_financial || ', ' ||
421 to_char(p_operating_unit) || ', ' ||
422 p_from_project || ', ' ||
423 p_to_project || ', ' ||
424 p_plan_type, 1, 240) PARAMETERS,
425 null CONFIG_PROJ_PERF_FLAG,
426 null CONFIG_COST_FLAG,
427 null CONFIG_PROFIT_FLAG,
428 null CONFIG_UTIL_FLAG,
429 sysdate START_DATE,
430 null END_DATE,
431 null COMPLETION_TEXT
432 from
433 dual;
434
435 -- ------------------------------------------------------------------------
436 -- Initialize Resource Management portion of stage 2 PJI summarization.
437 -- ------------------------------------------------------------------------
438
439 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'P');
440
441 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
442 'ORGANIZATION_ID',
443 p_organization_id);
444
445 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
446 'HIERARCHIAL',
447 p_include_sub_org);
448
449 -- Update list of organizations to be extracted in case
450 -- users defined new organizations.
451 -- List of organizations is stored in table
452 -- PJI_ORG_EXTR_STATUS
453
454 insert into PJI_ORG_EXTR_STATUS
455 (
456 ORGANIZATION_ID,
457 STATUS,
458 CREATION_DATE,
459 LAST_UPDATE_DATE
460 )
461 select
462 org.ORGANIZATION_ID,
463 null,
464 sysdate,
465 sysdate
466 from
467 (
468 select /*+ ordered full(stat) use_hash(stat) */
469 distinct
470 org.ORGANIZATION_ID
471 from
472 PA_ALL_ORGANIZATIONS org,
473 PJI_ORG_EXTR_STATUS stat
474 where
475 org.ORGANIZATION_ID = stat.ORGANIZATION_ID (+) and
476 stat.ORGANIZATION_ID is null
477 ) org
478 where
479 exists (select /*+ index_ffs(fid, PA_FORECAST_ITEM_DETAILS_N2)
480 parallel_index(fid, PA_FORECAST_ITEM_DETAILS_N2) */ 1
481 from PA_FORECAST_ITEM_DETAILS fid
482 where fid.EXPENDITURE_ORGANIZATION_ID > 0 and
483 fid.EXPENDITURE_ORGANIZATION_ID = org.ORGANIZATION_ID);
484
485 if (l_extraction_type = 'INCREMENTAL' ) then
486
487 insert into pji_rm_org_batch_map (
488 worker_id,
489 organization_id,
490 start_date,
491 end_date,
492 extraction_type,
493 row_count)
494 select
495 1,
496 sts.ORGANIZATION_ID,
497 g_min_date,
498 g_max_date,
499 case when sts.STATUS is null
500 then 'F'
501 else 'I'
502 end,
503 null
504 from
505 PJI_ORG_EXTR_STATUS sts;
506
507 elsif (l_extraction_type = 'PARTIAL') then
508
509 if (p_include_sub_org = 'Y') then
510
511 insert into PJI_RM_ORG_BATCH_MAP
512 (
513 WORKER_ID,
514 ORGANIZATION_ID,
515 START_DATE,
516 END_DATE,
517 EXTRACTION_TYPE,
518 ROW_COUNT
519 )
520 select
521 1,
522 sts.ORGANIZATION_ID,
523 g_min_date,
524 g_max_date,
525 'P',
526 null
527 from
528 PJI_ORG_EXTR_STATUS sts,
529 PJI_ORG_DENORM orgs
530 where
531 p_prtl_schedule = 'Y' and
532 orgs.ORGANIZATION_ID = p_organization_id and
533 sts.ORGANIZATION_ID = orgs.SUB_ORGANIZATION_ID and
534 sts.STATUS = 'X';
535
536 else
537
538 insert into PJI_RM_ORG_BATCH_MAP
539 (
540 WORKER_ID,
541 ORGANIZATION_ID,
542 START_DATE,
543 END_DATE,
544 EXTRACTION_TYPE,
545 ROW_COUNT
546 )
547 select
548 1,
549 sts.ORGANIZATION_ID,
550 g_min_date,
551 g_max_date,
552 'P',
553 null
554 from
555 PJI_ORG_EXTR_STATUS sts
556 where
557 p_prtl_schedule = 'Y' and
558 sts.ORGANIZATION_ID = p_organization_id and
559 sts.STATUS = 'X';
560
561 end if;
562
563 elsif (l_extraction_type = 'FULL') then
564
565 insert into PJI_RM_ORG_BATCH_MAP
566 (
567 WORKER_ID,
568 ORGANIZATION_ID,
569 EXTRACTION_TYPE,
570 ROW_COUNT,
571 START_DATE,
572 END_DATE
573 )
574 select
575 1,
576 extr.ORGANIZATION_ID,
577 'F',
578 null,
579 g_min_date,
580 g_max_date
581 from
582 PJI_ORG_EXTR_STATUS extr
583 where
584 extr.STATUS is null;
585
586 end if;
587
588 update PJI_ORG_EXTR_STATUS
589 set STATUS = 'X'
590 where l_extraction_type in ('FULL', 'INCREMENTAL') and
591 ORGANIZATION_ID IN (select map.ORGANIZATION_ID
592 from PJI_RM_ORG_BATCH_MAP map
593 where map.WORKER_ID = 1) and
594 STATUS is null;
595
596 l_global_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE;
597
598 if (PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE') is not null and
599 trunc(l_global_start_date, 'J') <>
600 trunc(to_date(PJI_UTILS.GET_PARAMETER('GLOBAL_START_DATE'),
601 g_date_mask), 'J')) then
602 pji_utils.write2log('WARNING: Global start date has changed.');
603 end if;
604
605 PJI_UTILS.SET_PARAMETER('GLOBAL_START_DATE',
606 to_char
607 (
608 l_global_start_date,
609 g_date_mask
610 ));
611
612 if (PJI_UTILS.GET_SETUP_PARAMETER('PA_PERIOD_FLAG') = 'Y' ) then
613 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,'PA_CALENDAR_FLAG','Y');
614 else
615 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,'PA_CALENDAR_FLAG','N');
616 end if;
617
618 if (PJI_UTILS.GET_SETUP_PARAMETER('GL_PERIOD_FLAG') = 'Y' ) then
619 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,'GL_CALENDAR_FLAG','Y');
620 else
621 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,'GL_CALENDAR_FLAG','N');
622 end if;
623
624 --Enable menu structure for Utilization
625 PA_PJI_MENU_UTIL.ENABLE_MENUS;
626
627 -- ------------------------------------------------------------------------
628 -- Initialize Financial Management portion of stage 2 PJI summarization.
629 -- ------------------------------------------------------------------------
630
631 IF p_from_project > p_to_project then
632 dbms_standard.raise_application_error(-20092, l_invalid_parameter);
633 END IF;
634
635 IF (p_from_project is not null or
636 p_to_project is not null) then
637
638 select min(segment1),
639 max(segment1)
640 into l_from_project_num,
641 l_to_project_num
642 from pa_projects_all
643 where segment1 between nvl(p_from_project,segment1) and
644 nvl(p_to_project,segment1);
645
646 END if;
647
648 IF (l_from_project_num is not null) THEN
649
650 select project_id
651 into p_from_project_id
652 from pa_projects_all
653 where segment1= l_from_project_num;
654
655 else
656
657 p_from_project_id:=-1;
658
659 END IF;
660
661 IF l_to_project_num is not null THEN
662
663 select project_id
664 into p_to_project_id
665 from pa_projects_all
666 where segment1= l_to_project_num;
667
668 else
669
670 p_to_project_id:=-1;
671
672 END IF;
673
674 PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_EXTR_SCOPE;
675
676 l_global_start_date := PJI_UTILS.GET_EXTRACTION_START_DATE;
677
678 insert into PJI_PJI_PROJ_BATCH_MAP
679 (
680 WORKER_ID,
681 PROJECT_ID,
682 PROJECT_TYPE_CLASS,
683 PJI_PROJECT_STATUS,
684 ROW_COUNT,
685 START_DATE,
686 END_DATE,
687 PROJECT_ORG_ID,
688 NEW_PROJECT_ORGANIZATION_ID,
689 NEW_CLOSED_DATE,
690 EXTRACTION_TYPE,
691 EXTRACTION_STATUS,
692 COST_BUDGET_C_VERSION,
693 COST_BUDGET_CO_VERSION,
694 REVENUE_BUDGET_C_VERSION,
695 REVENUE_BUDGET_CO_VERSION,
696 COST_FORECAST_C_VERSION,
697 REVENUE_FORECAST_C_VERSION,
698 PROJECT_ORGANIZATION_ID,
699 OLD_CLOSED_DATE,
700 PLAN_EXTRACTION_STATUS,
701 BACKLOG_EXTRACTION_STATUS
702 )
703 select /*+ ordered full(extr) use_hash(extr)
704 full(prj) use_hash(prj) parallel(prj) */
705 1 WORKER_ID,
706 extr.PROJECT_ID,
707 extr.PROJECT_TYPE_CLASS,
708 'O',
709 0,
710 null,
711 null,
712 prj.ORG_ID,
713 prj.CARRYING_OUT_ORGANIZATION_ID,
714 prj.CLOSED_DATE,
715 decode(extr.EXTRACTION_STATUS, 'F', 'F',
716 decode(l_extraction_type, 'FULL', 'F',
717 'INCREMENTAL', 'I',
718 'PARTIAL', 'P')),
719 extr.EXTRACTION_STATUS,
720 decode(l_extraction_type, 'PARTIAL', -1,
721 nvl(extr.COST_BUDGET_C_VERSION,-2)),
722 decode(l_extraction_type, 'PARTIAL', -1,
723 nvl(extr.COST_BUDGET_CO_VERSION,-2)),
724 decode(l_extraction_type, 'PARTIAL', -1,
725 nvl(extr.REVENUE_BUDGET_C_VERSION,-2)),
726 decode(l_extraction_type, 'PARTIAL', -1,
727 nvl(extr.REVENUE_BUDGET_CO_VERSION,-2)),
728 decode(l_extraction_type, 'PARTIAL', -1,
729 nvl(extr.COST_FORECAST_C_VERSION,-2)),
730 decode(l_extraction_type, 'PARTIAL', -1,
731 nvl(extr.REVENUE_FORECAST_C_VERSION,-2)),
732 decode(extr.EXTRACTION_STATUS, 'I', extr.PROJECT_ORGANIZATION_ID,
733 prj.CARRYING_OUT_ORGANIZATION_ID),
734 decode(extr.EXTRACTION_STATUS, 'I', extr.CLOSED_DATE, prj.CLOSED_DATE),
735 'N',
736 'N'
737 from
738 PJI_PJI_PROJ_EXTR_STATUS extr,
739 PA_PROJECTS_ALL prj
740 where
741 extr.project_id = prj.project_id and
742 nvl(prj.org_id,-99) = nvl(p_operating_unit,nvl(prj.org_id,-99)) and
743 (l_extraction_type = 'FULL' or
744 (prj.segment1 between nvl(p_from_project,prj.segment1) and
745 nvl(p_to_project,prj.segment1))) and
746 not (l_extraction_type = 'PARTIAL' and
747 extr.EXTRACTION_STATUS = 'F') and
748 not (l_extraction_type = 'PARTIAL' and
749 p_prtl_financial = 'N');
750
751 if (SQL%ROWCOUNT = 0 and p_prtl_financial = 'Y') then
752 rollback;
753 dbms_standard.raise_application_error(-20041, l_no_work);
754 end if;
755
756 update PJI_PJI_PROJ_EXTR_STATUS
757 set EXTRACTION_STATUS = 'I',
758 LAST_UPDATE_DATE = sysdate
759 where l_extraction_type in ('FULL', 'INCREMENTAL') and
760 EXTRACTION_STATUS = 'F' and
761 PROJECT_ID in (select PROJECT_ID
762 from PJI_PJI_PROJ_BATCH_MAP
763 where WORKER_ID = 1);
764
765 -- Set global process parameters
766 --
767 -- PROCESS_RUNNING: Y = Yes
768 -- N = No
769 -- F = Failed
770 -- A = Aborted
771 --
772 -- batch statuses: R = Ready
773 -- P = Processing
774 -- C = Completed
775 -- F = Failed
776 --
777 -- dangling flag: R = Rate is missing
778 -- T = TIME_ID is outside a calendar range
779 -- null = not dangling
780
781 PJI_FM_PLAN_EXTR.INIT_GLOBAL_PARAMETERS;
782
783 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
784 'PROJECT_OPERATING_UNIT',
785 p_operating_unit);
786
787 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
788 'FROM_PROJECT_ID',
789 p_from_project_id);
790
791 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
792 'TO_PROJECT_ID',
793 p_to_project_id);
794
795 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
796 'FROM_PROJECT',
797 p_from_project);
798
799 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
800 'TO_PROJECT',
801 p_to_project);
802
803 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
804 'PLAN_TYPE_ID',
805 p_plan_type);
806
807 g_parallel_processes := PJI_EXTRACTION_UTIL.GET_PARALLEL_PROCESSES;
808
809 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
810 'PARALLEL_PROCESSES',
811 least(g_parallel_processes,
812 g_parallel_limit));
813
814 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(g_process || 1, 'PJI_RM_SUM_MAIN.INIT_PROCESS;');
815
816 commit;
817
818 end INIT_PROCESS;
819
820
821 -- -----------------------------------------------------
822 -- function PROCESS_RUNNING
823 -- -----------------------------------------------------
824 function PROCESS_RUNNING (p_wait in varchar2) return boolean is
825
826 l_parallel_processes number;
827 l_batch_count number;
828 l_from_process number;
829
830 begin
831
832 -- if process is determined to be over or any worker has failed then signal
833 -- that workers should stop processing and wrapup
834
835 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') <> 'Y' and
836 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') <> 'N') then
837 return false;
838 end if;
839
840 l_parallel_processes :=
841 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PARALLEL_PROCESSES');
842
843 if (p_wait = 'WAIT') then
844 l_from_process := 2;
845 elsif (p_wait = 'DO_NOT_WAIT') then
846 l_from_process := 1;
847 end if;
848
849 l_batch_count := 0;
850
851 for x in l_from_process .. l_parallel_processes loop
852 if (not PJI_RM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
853 l_batch_count := l_batch_count + 1;
854 end if;
855 end loop;
856
857 if (l_batch_count > 0) then
858 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
859 'PROCESS_RUNNING',
860 'F');
861 commit;
862 return false;
863 end if;
864
865 if (p_wait = 'DO_NOT_WAIT') then
866 return true;
867 end if;
868
869 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING','N');
870 commit;
871
872 for x in l_from_process .. l_parallel_processes loop
873 PJI_RM_SUM_EXTR.WAIT_FOR_WORKER(x);
874 end loop;
875
876 return false;
877
878 end PROCESS_RUNNING;
879
880
881 -- -----------------------------------------------------
882 -- procedure RUN_PROCESS
883 -- -----------------------------------------------------
884 procedure RUN_PROCESS is
885
886 l_parallel_processes number;
887 l_seq number;
888
889 begin
890
891 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(g_process || 1, 'PJI_RM_SUM_MAIN.RUN_PROCESS;')) then
892 return;
893 end if;
894
895 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
896 'PROCESS_RUNNING',
897 'Y');
898
899 -- ensure that worker and helpers can run concurrently
900 FND_PROFILE.PUT('CONC_SINGLE_THREAD', 'N');
901 commit;
902
903 l_parallel_processes :=
904 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PARALLEL_PROCESSES');
905
906 -- start extraction helpers
907
908 for x in 2..l_parallel_processes loop
909 PJI_RM_SUM_EXTR.START_HELPER(x);
910 end loop;
911
912 -- run extraction worker
913
914 PJI_RM_SUM_EXTR.WORKER(1);
915
916 -- sleep until process is complete
917
918 while (PROCESS_RUNNING('WAIT')) loop
919 PJI_PROCESS_UTIL.SLEEP(g_process_delay);
920 end loop;
921
922 -- process finished
923
924 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') = 'N') then
925
926 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(g_process || 1, 'PJI_RM_SUM_MAIN.RUN_PROCESS;');
927
928 commit;
929
930 end if;
931
932 end RUN_PROCESS;
933
934
935 -- -----------------------------------------------------
936 -- function MY_PAD
937 -- -----------------------------------------------------
938 function MY_PAD (p_length in number,
939 p_char in varchar2) return varchar2 is
940
941 l_stmt varchar2(2000) := '';
942
943 begin
944
945 for x in 1 .. p_length loop
946
947 l_stmt := l_stmt || p_char;
948
949 end loop;
950
951 return l_stmt;
952
953 end MY_PAD;
954
955
956 -- -----------------------------------------------------
957 -- function GET_MISSING_TIME_HEADER
958 -- -----------------------------------------------------
959 function GET_MISSING_TIME_HEADER return varchar2 is
960
961 l_stmt1 varchar2(2000) := '';
962 l_stmt2 varchar2(2000) := '';
963 l_temp varchar2(1000) := '';
964 l_min_width number := 20;
965 l_newline varchar2(10) := '
966 ';
967
968 begin
969
970 fnd_message.set_name('PJI','PJI_MISSING_CAL_HEADER');
971 l_stmt1 := l_newline ||
972 l_newline ||
973 fnd_message.get ||
974 l_newline ||
975 l_newline;
976
977 fnd_message.set_name('PJI','PJI_CALENDAR_TEXT');
978 l_temp := fnd_message.get;
979 l_stmt2 := my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
980 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
981 l_stmt1 := l_stmt1 || l_temp || ' ';
982
983 fnd_message.set_name('PJI','PJI_PERIOD_TYPE_TEXT');
984 l_temp := fnd_message.get;
985 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
986 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
987 l_stmt1 := l_stmt1 || l_temp || ' ';
988
989 fnd_message.set_name('PJI','PJI_FROM_DATE_TEXT');
990 l_temp := fnd_message.get;
991 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
992 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
993 l_stmt1 := l_stmt1 || l_temp || ' ';
994
995 fnd_message.set_name('PJI','PJI_TO_DATE_TEXT');
996 l_temp := fnd_message.get;
997 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-');
998 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
999 l_stmt1 := l_stmt1 || l_temp;
1000
1001 return l_stmt1 || l_newline || l_stmt2 || l_newline;
1002
1003 end GET_MISSING_TIME_HEADER;
1004
1005
1006 -- -----------------------------------------------------
1007 -- function GET_MISSING_TIME_TEXT
1008 -- -----------------------------------------------------
1009 function GET_MISSING_TIME_TEXT (p_calendar_name in varchar2,
1010 p_period_type in varchar2,
1011 p_from_date in date,
1012 p_to_date in date) return varchar2 is
1013
1014 l_stmt varchar2(2000) := '';
1015 l_temp varchar2(1000) := '';
1016 l_min_width number := 20;
1017 l_newline varchar2(10) := '
1018 ';
1019
1020 begin
1021
1022 l_stmt := p_calendar_name
1023 || my_pad(greatest(l_min_width - length(p_calendar_name), 0), ' ')
1024 || ' ';
1025
1026 l_stmt := l_stmt
1027 || p_period_type
1028 || my_pad(greatest(l_min_width - length(p_period_type), 0), ' ')
1029 || ' ';
1030
1031 l_stmt := l_stmt
1032 || to_char(p_from_date, g_date_mask)
1033 || my_pad(greatest(l_min_width - length(to_char(p_from_date,
1034 g_date_mask)), 0),
1035 ' ')
1036 || ' ';
1037
1038 l_stmt := l_stmt
1039 || to_char(p_to_date, g_date_mask)
1040 || my_pad(greatest(l_min_width - length(to_char(p_to_date,
1041 g_date_mask)), 0),
1042 ' ')
1043 || l_newline;
1044
1045 return l_stmt;
1046
1047 end GET_MISSING_TIME_TEXT;
1048
1049
1050 -- -----------------------------------------------------
1051 -- procedure DANGLING_REPORT
1052 -- -----------------------------------------------------
1053 procedure DANGLING_REPORT is
1054
1055 cursor missing_rates (p_g1_currency_code in varchar2,
1056 p_g2_currency_code in varchar2) is
1057 select
1058 distinct
1059 decode(sign(bitand(to_number(log.RECORD_TYPE_CODE), 3)),
1060 1, to_date('1999/01/01', 'YYYY/MM/DD'),
1061 log.FROM_DATE) FROM_DATE,
1062 info.PF_CURRENCY_CODE PF_CURRENCY_CODE,
1063 decode(invert.INVERT_ID,
1064 'G1', p_g1_currency_code,
1065 'G2', p_g2_currency_code) G_CURRENCY_CODE,
1066 decode(invert.INVERT_ID,
1067 'G1', PJI_UTILS.GET_RATE_TYPE,
1068 'G2', FND_PROFILE.VALUE('BIS_SECONDARY_RATE_TYPE')) RATE_TYPE
1069 from
1070 PJI_FM_EXTR_PLN_LOG log,
1071 PJI_ORG_EXTR_INFO info,
1072 (
1073 select 'G1' INVERT_ID from dual union all
1074 select 'G2' INVERT_ID from dual
1075 ) invert
1076 where
1077 bitand(to_number(log.RECORD_TYPE_CODE), 15) > 0 and
1078 log.PROJECT_ORG_ID = info.ORG_ID;
1079
1080 cursor missing_time (p_calendar_id in number) is
1081 select
1082 name.NAME CALENDAR_NAME,
1083 pt.USER_PERIOD_TYPE,
1084 tmp2.CALENDAR_MIN_DATE,
1085 tmp2.CALENDAR_MAX_DATE,
1086 min(tmp2.FROM_DATE) FROM_DATE,
1087 max(tmp2.TO_DATE) TO_DATE
1088 from
1089 (
1090 select
1091 info.CALENDAR_ID,
1092 to_date(info.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
1093 to_date(info.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
1094 min(log.FROM_DATE) FROM_DATE,
1095 max(log.TO_DATE) TO_DATE
1096 from
1097 PJI_FM_EXTR_PLN_LOG log,
1098 (
1099 select
1100 distinct
1101 decode(invert.INVERT_ID,
1102 'EN', p_calendar_id,
1103 'GL', info.GL_CALENDAR_ID,
1104 'PA', info.PA_CALENDAR_ID) CALENDAR_ID,
1105 decode(invert.INVERT_ID,
1106 'EN', info.EN_CALENDAR_MIN_DATE,
1107 'GL', info.GL_CALENDAR_MIN_DATE,
1108 'PA', info.PA_CALENDAR_MIN_DATE) CALENDAR_MIN_DATE,
1109 decode(invert.INVERT_ID,
1110 'EN', info.EN_CALENDAR_MAX_DATE,
1111 'GL', info.GL_CALENDAR_MAX_DATE,
1112 'PA', info.PA_CALENDAR_MAX_DATE) CALENDAR_MAX_DATE
1113 from
1114 PJI_ORG_EXTR_INFO info,
1115 (
1116 select 'EN' INVERT_ID from dual union all
1117 select 'GL' INVERT_ID from dual union all
1118 select 'PA' INVERT_ID from dual
1119 ) invert
1120 where
1121 info.ORG_ID <> -1
1122 ) info
1123 where
1124 bitand(to_number(log.RECORD_TYPE_CODE), 16) > 0 and
1125 nvl(log.CALENDAR_ID, -1) = info.CALENDAR_ID
1126 group by
1127 info.CALENDAR_ID,
1128 to_date(info.CALENDAR_MIN_DATE, 'J'),
1129 to_date(info.CALENDAR_MAX_DATE, 'J')
1130 union all
1131 select
1132 tmp1.CALENDAR_ID,
1133 to_date(tmp1.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
1134 to_date(tmp1.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
1135 min(tmp1.FROM_DATE) FROM_DATE,
1136 max(tmp1.TO_DATE) TO_DATE
1137 from
1138 (
1139 select
1140 case when tmp1.CALENDAR_TYPE = 'C'
1141 then p_calendar_id
1142 when tmp1.CALENDAR_TYPE = 'P'
1143 then info.PA_CALENDAR_ID
1144 when tmp1.CALENDAR_TYPE = 'G'
1145 then info.GL_CALENDAR_ID
1146 end CALENDAR_ID,
1147 case when tmp1.CALENDAR_TYPE = 'C'
1148 then info.EN_CALENDAR_MIN_DATE
1149 when tmp1.CALENDAR_TYPE = 'P'
1150 then info.PA_CALENDAR_MIN_DATE
1151 when tmp1.CALENDAR_TYPE = 'G'
1152 then info.GL_CALENDAR_MIN_DATE
1153 end CALENDAR_MIN_DATE,
1154 case when tmp1.CALENDAR_TYPE = 'C'
1155 then info.EN_CALENDAR_MAX_DATE
1156 when tmp1.CALENDAR_TYPE = 'P'
1157 then info.PA_CALENDAR_MAX_DATE
1158 when tmp1.CALENDAR_TYPE = 'G'
1159 then info.GL_CALENDAR_MAX_DATE
1160 end CALENDAR_MAX_DATE,
1161 to_date(to_char(min(tmp1.FROM_TIME_ID)), 'J') FROM_DATE,
1162 to_date(to_char(max(tmp1.TO_TIME_ID)), 'J') TO_DATE
1163 from
1164 PJI_ORG_EXTR_INFO info,
1165 (
1166 select
1167 distinct
1168 tmp1.EXPENDITURE_ORG_ID ORG_ID,
1169 tmp1.CALENDAR_TYPE,
1170 tmp1.TIME_ID FROM_TIME_ID,
1171 tmp1.TIME_ID TO_TIME_ID,
1172 tmp1.DANGLING_FLAG
1173 from
1174 PJI_RM_DNGL_RES tmp1
1175 where
1176 tmp1.WORKER_ID = 0
1177 ) tmp1
1178 where
1179 tmp1.DANGLING_FLAG = 'T' and
1180 tmp1.ORG_ID = info.ORG_ID
1181 group by
1182 case when tmp1.CALENDAR_TYPE = 'C'
1183 then p_calendar_id
1184 when tmp1.CALENDAR_TYPE = 'P'
1185 then info.PA_CALENDAR_ID
1186 when tmp1.CALENDAR_TYPE = 'G'
1187 then info.GL_CALENDAR_ID
1188 end,
1189 case when tmp1.CALENDAR_TYPE = 'C'
1190 then info.EN_CALENDAR_MIN_DATE
1191 when tmp1.CALENDAR_TYPE = 'P'
1192 then info.PA_CALENDAR_MIN_DATE
1193 when tmp1.CALENDAR_TYPE = 'G'
1194 then info.GL_CALENDAR_MIN_DATE
1195 end,
1196 case when tmp1.CALENDAR_TYPE = 'C'
1197 then info.EN_CALENDAR_MAX_DATE
1198 when tmp1.CALENDAR_TYPE = 'P'
1199 then info.PA_CALENDAR_MAX_DATE
1200 when tmp1.CALENDAR_TYPE = 'G'
1201 then info.GL_CALENDAR_MAX_DATE
1202 end
1203 ) tmp1
1204 group by
1205 tmp1.CALENDAR_ID,
1206 to_date(tmp1.CALENDAR_MIN_DATE, 'J'),
1207 to_date(tmp1.CALENDAR_MAX_DATE, 'J')
1208 ) tmp2,
1209 FII_TIME_CAL_NAME name,
1210 GL_PERIOD_TYPES pt
1211 where
1212 name.CALENDAR_ID = tmp2.CALENDAR_ID and
1213 pt.PERIOD_TYPE = name.PERIOD_TYPE
1214 group by
1215 name.NAME,
1216 pt.USER_PERIOD_TYPE,
1217 tmp2.CALENDAR_MIN_DATE,
1218 tmp2.CALENDAR_MAX_DATE;
1219
1220 l_calendar_id varchar2(255);
1221 l_header_flag varchar2(1);
1222 l_newline varchar2(10) := '
1223 ';
1224
1225 begin
1226
1227 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'N');
1228
1229 --
1230 -- Report dangling rates
1231 --
1232
1233 l_header_flag := 'Y';
1234
1235 for c in missing_rates(PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY,
1236 PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY) loop
1237
1238 if (l_header_flag = 'Y') then
1239
1240 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'Y');
1241
1242 pji_utils.write2out(l_newline || PJI_UTILS.getMissingRateHeader);
1243
1244 l_header_flag := 'N';
1245
1246 end if;
1247
1248 pji_utils.write2out(
1249 PJI_UTILS.getMissingRateText(c.RATE_TYPE,
1250 c.PF_CURRENCY_CODE,
1251 c.G_CURRENCY_CODE,
1252 c.FROM_DATE,
1253 to_char(c.FROM_DATE, 'YYYY/MM/DD')) ||
1254 l_newline);
1255
1256 end loop;
1257
1258 --
1259 -- Report time dimension gaps
1260 --
1261
1262 select CALENDAR_ID
1263 into l_calendar_id
1264 from FII_TIME_CAL_NAME
1265 where PERIOD_SET_NAME = PJI_UTILS.GET_PERIOD_SET_NAME and
1266 PERIOD_TYPE = PJI_UTILS.GET_PERIOD_TYPE;
1267
1268 l_header_flag := 'Y';
1269
1270 for c in missing_time(l_calendar_id) loop
1271
1272 if (l_header_flag = 'Y') then
1273
1274 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'Y');
1275
1276 pji_utils.write2out(PJI_RM_SUM_MAIN.GET_MISSING_TIME_HEADER);
1277 l_header_flag := 'N';
1278 end if;
1279
1280 if (c.FROM_DATE < c.CALENDAR_MIN_DATE and
1281 c.TO_DATE > c.CALENDAR_MAX_DATE) then
1282
1283 pji_utils.write2out(
1284 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1285 c.USER_PERIOD_TYPE,
1286 c.FROM_DATE,
1287 c.CALENDAR_MIN_DATE));
1288
1289 pji_utils.write2out(
1290 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1291 c.USER_PERIOD_TYPE,
1292 c.CALENDAR_MAX_DATE,
1293 c.TO_DATE));
1294
1295 elsif (c.TO_DATE > c.CALENDAR_MAX_DATE) then
1296
1297 pji_utils.write2out(
1298 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1299 c.USER_PERIOD_TYPE,
1300 c.CALENDAR_MAX_DATE,
1301 c.TO_DATE));
1302
1303 elsif (c.FROM_DATE < c.CALENDAR_MIN_DATE) then
1304
1305 pji_utils.write2out(
1306 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1307 c.USER_PERIOD_TYPE,
1308 c.FROM_DATE,
1309 c.CALENDAR_MIN_DATE));
1310
1311 end if;
1312
1313 end loop;
1314
1315 pji_utils.write2out(l_newline);
1316
1317 commit;
1318
1319 end DANGLING_REPORT;
1320
1321
1322 -- -----------------------------------------------------
1323 -- procedure WRAPUP_SETUP
1324 -- -----------------------------------------------------
1325 procedure WRAPUP_SETUP is
1326
1327 l_params_proj_perf_flag varchar2(1);
1328 l_params_cost_flag varchar2(1);
1329 l_params_profit_flag varchar2(1);
1330 l_params_util_flag varchar2(1);
1331
1332 begin
1333
1334 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1335 'TRANSITION') = 'Y') then
1336
1337 l_params_proj_perf_flag :=
1338 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1339 'CONFIG_PROJ_PERF_FLAG'), 'N');
1340 l_params_cost_flag :=
1341 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1342 'CONFIG_COST_FLAG'), 'N');
1343 l_params_profit_flag :=
1344 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1345 'CONFIG_PROFIT_FLAG'), 'N');
1346 l_params_util_flag :=
1347 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1348 'CONFIG_UTIL_FLAG'), 'N');
1349
1350 if (l_params_proj_perf_flag = 'Y') then
1351 PJI_UTILS.SET_PARAMETER('CONFIG_PROJ_PERF_FLAG', 'Y');
1352 end if;
1353
1354 if (l_params_cost_flag = 'Y') then
1355 PJI_UTILS.SET_PARAMETER('CONFIG_COST_FLAG', 'Y');
1356 end if;
1357
1358 if (l_params_profit_flag = 'Y') then
1359 PJI_UTILS.SET_PARAMETER('CONFIG_PROFIT_FLAG', 'Y');
1360 end if;
1361
1362 if (l_params_util_flag = 'Y') then
1363 PJI_UTILS.SET_PARAMETER('CONFIG_UTIL_FLAG', 'Y');
1364 end if;
1365
1366 update PJI_SYSTEM_CONFIG_HIST
1367 set END_DATE = sysdate
1368 where PROCESS_NAME = g_process || 1 and
1369 RUN_TYPE = 'TRANSITION' and
1370 END_DATE is null;
1371
1372 end if;
1373
1374 end WRAPUP_SETUP;
1375
1376 -- -----------------------------------------------------
1377 -- procedure WRAPUP_PROCESS
1378 -- -----------------------------------------------------
1379 procedure WRAPUP_PROCESS is
1380
1381 l_parallel_processes number;
1382 l_extraction_type varchar2(30);
1383 l_request_id number;
1384 l_batch_count number;
1385 l_schema varchar2(30);
1386
1387 begin
1388
1389 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(g_process || 1, 'PJI_RM_SUM_MAIN.WRAPUP_PROCESS;')) then
1390 return;
1391 end if;
1392
1393 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1394 (g_process, 'EXTRACTION_TYPE');
1395
1396 l_parallel_processes :=
1397 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PARALLEL_PROCESSES');
1398
1399 for x in 2 .. l_parallel_processes loop
1400 PJI_RM_SUM_EXTR.WAIT_FOR_WORKER(x);
1401 end loop;
1402
1403 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') = 'A') then
1404 fnd_message.set_name('PJI','PJI_SUM_ABORT');
1405 dbms_standard.raise_application_error(-20000, fnd_message.get);
1406 elsif (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') = 'F') then
1407 fnd_message.set_name('PJI','PJI_SUM_FAIL');
1408 dbms_standard.raise_application_error(-20000, fnd_message.get);
1409 end if;
1410
1411 DANGLING_REPORT;
1412
1413 -- clean up worker tables
1414 PJI_FM_PLAN_EXTR.CLEANUP_LOG;
1415
1416 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1417
1418 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1419 'PJI_RM_ORG_BATCH_MAP','NORMAL',null);
1420
1421 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1422 'PJI_PJI_PROJ_BATCH_MAP','NORMAL',null);
1423
1424 WRAPUP_SETUP;
1425
1426 PJI_PROCESS_UTIL.WRAPUP_PROCESS(g_process || 1);
1427 PJI_PROCESS_UTIL.WRAPUP_PROCESS(g_process);
1428
1429 update PJI_SYSTEM_CONFIG_HIST
1430 set END_DATE = sysdate,
1431 COMPLETION_TEXT = 'Normal completion'
1432 where PROCESS_NAME = g_process || 1 and
1433 END_DATE is null;
1434
1435 PJI_UTILS.SET_PARAMETER('LAST_PJI_EXTR_DATE', to_char(sysdate, 'YYYY/MM/DD'));
1436 commit;
1437
1438 -- calculate statistics on temporary tables used to retrieve fact data
1439
1440 PJI_PMV_UTIL.SEED_PJI_STATS;
1441
1442 commit;
1443
1444 end WRAPUP_PROCESS;
1445
1446
1447 -- -----------------------------------------------------
1448 -- procedure WRAPUP_FAILURE
1449 -- -----------------------------------------------------
1450 procedure WRAPUP_FAILURE is
1451
1452 l_sqlerrm varchar2(240);
1453
1454 begin
1455
1456 rollback;
1457
1458 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1459 'PROCESS_RUNNING',
1460 'F');
1461
1462 commit;
1463
1464 pji_utils.write2log(sqlerrm, true, 0);
1465
1466 l_sqlerrm := substr(sqlerrm, 1, 240);
1467
1468 update PJI_SYSTEM_CONFIG_HIST
1469 set END_DATE = sysdate,
1470 COMPLETION_TEXT = l_sqlerrm
1471 where PROCESS_NAME = g_process || 1 and
1472 END_DATE is null;
1473
1474 commit;
1475
1476 end WRAPUP_FAILURE;
1477
1478
1479 -- -----------------------------------------------------
1480 -- procedure SHUTDOWN_PROCESS
1481 -- -----------------------------------------------------
1482 procedure SHUTDOWN_PROCESS (errbuf out nocopy varchar2,
1483 retcode out nocopy varchar2) is
1484
1485 begin
1486
1487 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') <> 'Y') then
1488
1489 retcode := 1;
1490
1491 else
1492
1493 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1494 'PROCESS_RUNNING',
1495 'A');
1496 commit;
1497 retcode := 0;
1498
1499 end if;
1500
1501 exception when others then
1502
1503 retcode := 2;
1504 errbuf := sqlerrm;
1505
1506 end SHUTDOWN_PROCESS;
1507
1508
1509 -- -----------------------------------------------------
1510 -- procedure SUMMARIZE
1511 --
1512 -- This the the main procedure, it is invoked from
1513 -- a concurrent program.
1514 -- -----------------------------------------------------
1515 procedure SUMMARIZE
1516 (
1517 errbuf out nocopy varchar2,
1518 retcode out nocopy varchar2,
1519 p_run_mode in varchar2,
1520 p_prtl_schedule in varchar2 default null, -- RM
1521 p_organization_id in number default null, -- RM parameter
1522 p_include_sub_org in varchar2 default 'N', -- RM parameter
1523 p_prtl_financial in varchar2 default null, -- FM
1524 p_operating_unit in number default null, -- FM parameter
1525 p_from_project in varchar2 default null, -- FM parameter
1526 p_to_project in varchar2 default null, -- FM parameter
1527 p_plan_type in varchar2 default null -- FM parameter
1528 ) is
1529
1530 l_pji_not_licensed exception;
1531 pragma exception_init(l_pji_not_licensed, -20020);
1532 l_prior_iteration_successful boolean;
1533 l_transition_flag varchar2(1);
1534 --Bug 4892320.
1535 l_org_count NUMBER;
1536
1537 l_sum_running varchar2(255) := 'The process has failed due to a previously running process.';
1538 l_sum_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' is not complete.';
1539 l_sum_refresh_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' has not yet been run.';
1540
1541 begin
1542 pa_debug.set_process('PLSQL'); /* start 4893117*/
1543 IF p_run_mode in ('P') then
1544 pa_debug.log_message('=======Concurrent Program Parameters Start =======', 1);
1545 pa_debug.log_message('Argument => Refresh Schedule ['||p_prtl_schedule||']', 1);
1546 pa_debug.log_message('Argument => Expenditure Organization (schedule) ['||p_organization_id||']', 1);
1547 pa_debug.log_message('Argument => Include Sub-Organizations (schedule) ['||p_include_sub_org||']', 1);
1548 pa_debug.log_message('Argument => Refresh Financial ['||p_prtl_financial||']', 1);
1549 pa_debug.log_message('Argument => Project Operating Unit (financial) ['||p_operating_unit||']', 1);
1550 pa_debug.log_message('Argument => From Project (financial) ['||p_from_project||']', 1);
1551 pa_debug.log_message('Argument => To Project (financial) ['||p_to_project||']', 1);
1552 pa_debug.log_message('=======Concurrent Program Parameters End =======', 1);
1553 END IF; /* end 4893117*/
1554
1555 PJI_FM_DEBUG.CONC_REQUEST_HOOK(g_process);
1556
1557 /* this is removed as for bug#5075209
1558 if (PA_INSTALL.is_pji_licensed = 'N') then
1559 pji_utils.write2log('Error: PJI is not licensed.');
1560 commit;
1561 raise l_pji_not_licensed;
1562 end if;*/
1563
1564 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process) <>
1565 FND_GLOBAL.CONC_REQUEST_ID and
1566 (PJI_PROCESS_UTIL.REQUEST_STATUS
1567 (
1568 'RUNNING',
1569 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
1570 g_full_disp_name
1571 ) or
1572 PJI_PROCESS_UTIL.REQUEST_STATUS
1573 (
1574 'RUNNING',
1575 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
1576 g_incr_disp_name
1577 ))) then
1578 pji_utils.write2log('Error: Summarization is already running.');
1579 commit;
1580 dbms_standard.raise_application_error(-20010, l_sum_running);
1581 end if;
1582
1583 /*
1584
1585 Removing the fix for now. Post interop we need to consider the project
1586 count, since all stage 2 concurrent programs have this procedure as
1587 their entry point.
1588
1589 Customer can avoid this problem by, when they have no schedule data, not
1590 running schedule refresh.
1591
1592 -- Bug 4892320. If pji_org_extr_status has 0 records then there is nothing to summarize and hence return.
1593 IF p_run_mode='R' THEN
1594
1595 SELECT count(*)
1596 INTO l_org_count
1597 FROM pji_org_extr_status;
1598
1599 IF l_org_count=0 THEN
1600
1601 pji_utils.write2log('Nothing to summarize since pji_org_extr_status has 0 records');
1602 RETURN;
1603
1604 END IF;
1605
1606 END IF;
1607 */
1608
1609 commit;
1610 execute immediate 'alter session enable parallel query';
1611 execute immediate 'alter session enable parallel dml';
1612
1613 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1614 g_process,
1615 FND_GLOBAL.CONC_REQUEST_ID);
1616
1617 commit;
1618
1619 PJI_PROCESS_UTIL.REFRESH_STEP_TABLE;
1620
1621 -- determine if a transitional configuration is needed
1622 RUN_SETUP;
1623
1624 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1625 (g_process, 'TRANSITION') = 'N' and
1626 (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1627 (g_process, 'CONFIG_PROJ_PERF_FLAG') = 'Y' or
1628 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1629 (g_process, 'CONFIG_COST_FLAG') = 'Y' or
1630 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1631 (g_process, 'CONFIG_PROFIT_FLAG') = 'Y' or
1632 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1633 (g_process, 'CONFIG_UTIL_FLAG') = 'Y')) then
1634 retcode := 1;
1635 else
1636 retcode := 0;
1637 end if;
1638
1639 l_prior_iteration_successful := PRIOR_ITERATION_SUCCESSFUL;
1640
1641 begin
1642
1643 INIT_PROCESS(p_run_mode,
1644 p_prtl_schedule,
1645 p_organization_id,
1646 p_include_sub_org,
1647 p_prtl_financial,
1648 p_operating_unit,
1649 p_from_project,
1650 p_to_project,
1651 p_plan_type);
1652
1653 -- Synchronize PJI_RM_WORK_TYPE_INFO with transaction system
1654 PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(g_process || 1);
1655
1656 -- Determine if Jobs have become utilizable or non-utilizable
1657 PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(g_process || 1);
1658
1659 RUN_PROCESS;
1660 WRAPUP_PROCESS;
1661
1662 if (PJI_UTILS.GET_PARAMETER('DANGLING_PJI_ROWS_EXIST') = 'Y') then
1663 retcode := 1;
1664 else
1665 retcode := 0;
1666 end if;
1667
1668 commit;
1669 execute immediate 'alter session disable parallel dml';
1670
1671 exception when others then
1672
1673 WRAPUP_FAILURE;
1674 execute immediate 'alter session disable parallel dml';
1675 retcode := 2;
1676 errbuf := sqlerrm;
1677 raise;
1678
1679 end;
1680
1681 exception when others then
1682
1683 rollback;
1684 retcode := 2;
1685 errbuf := sqlerrm;
1686 raise;
1687
1688 end SUMMARIZE;
1689
1690 end PJI_RM_SUM_MAIN;