[Home] [Help]
PACKAGE BODY: APPS.PJI_RM_SUM_MAIN
Source
1 package body PJI_RM_SUM_MAIN as
2 /* $Header: PJISR01B.pls 120.17 2011/09/22 15:07:06 arbandyo ship $ */
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('PA_GLOBAL_START_DATE') is null or
85 FND_PROFILE.VALUE('PA_PRIMARY_CURRENCY_CODE') is null or
86 FND_PROFILE.VALUE('PA_PRIMARY_RATE_TYPE') is null or
87 FND_PROFILE.VALUE('PA_ENTERPRISE_CALENDAR') is null or
88 FND_PROFILE.VALUE('PA_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;Commented for bug 13011859 */
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;Commented for bug 13011859 */
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 Commented for bug 13011859 */
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 Commented for bug 13011859 */
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;Commented for bug 13011859 */
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') thenCommented for bug 13011859 */
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 begin
968
969 fnd_message.set_name('PJI','PJI_MISSING_CAL_HEADER');
970 l_stmt1 := l_newline ||
971 l_newline ||
972 fnd_message.get ||
973 l_newline ||
974 l_newline;
975
976 fnd_message.set_name('PJI','PJI_CALENDAR_TEXT');
977 l_temp := fnd_message.get;
978 l_stmt2 := my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
979 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
980 l_stmt1 := l_stmt1 || l_temp || ' ';
981
982 fnd_message.set_name('PJI','PJI_PERIOD_TYPE_TEXT');
983 l_temp := fnd_message.get;
984 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
985 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
986 l_stmt1 := l_stmt1 || l_temp || ' ';
987
988 fnd_message.set_name('PJI','PJI_FROM_DATE_TEXT');
989 l_temp := fnd_message.get;
990 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-') || ' ';
991 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
992 l_stmt1 := l_stmt1 || l_temp || ' ';
993
994 fnd_message.set_name('PJI','PJI_TO_DATE_TEXT');
995 l_temp := fnd_message.get;
996 l_stmt2 := l_stmt2 || my_pad(greatest(length(l_temp), l_min_width), '-');
997 l_temp := l_temp || my_pad(greatest(l_min_width - length(l_temp), 0), ' ');
998 l_stmt1 := l_stmt1 || l_temp;
999
1000 return l_stmt1 || l_newline || l_stmt2 || l_newline;
1001
1002 end GET_MISSING_TIME_HEADER;
1003
1004
1005 -- -----------------------------------------------------
1006 -- function GET_MISSING_TIME_TEXT
1007 -- -----------------------------------------------------
1008 function GET_MISSING_TIME_TEXT (p_calendar_name in varchar2,
1009 p_period_type in varchar2,
1010 p_from_date in date,
1011 p_to_date in date) return varchar2 is
1012
1013 l_stmt varchar2(2000) := '';
1014 l_temp varchar2(1000) := '';
1015 l_min_width number := 20;
1016 l_newline varchar2(10) := '
1017 ';
1018
1019 begin
1020
1021 l_stmt := p_calendar_name
1022 || my_pad(greatest(l_min_width - length(p_calendar_name), 0), ' ')
1023 || ' ';
1024
1025 l_stmt := l_stmt
1026 || p_period_type
1027 || my_pad(greatest(l_min_width - length(p_period_type), 0), ' ')
1028 || ' ';
1029
1030 l_stmt := l_stmt
1031 || to_char(p_from_date, g_date_mask)
1032 || my_pad(greatest(l_min_width - length(to_char(p_from_date,
1033 g_date_mask)), 0),
1034 ' ')
1035 || ' ';
1036
1037 l_stmt := l_stmt
1038 || to_char(p_to_date, g_date_mask)
1039 || my_pad(greatest(l_min_width - length(to_char(p_to_date,
1040 g_date_mask)), 0),
1041 ' ')
1042 || l_newline;
1043
1044 return l_stmt;
1045
1046 end GET_MISSING_TIME_TEXT;
1047
1048
1049 -- -----------------------------------------------------
1050 -- procedure DANGLING_REPORT
1051 -- -----------------------------------------------------
1052 procedure DANGLING_REPORT is
1053
1054 /* cursor missing_rates (p_g1_currency_code in varchar2,
1055 p_g2_currency_code in varchar2) is
1056 select
1057 distinct
1058 decode(sign(bitand(to_number(log.RECORD_TYPE_CODE), 3)),
1059 1, to_date('1999/01/01', 'YYYY/MM/DD'),
1060 log.FROM_DATE) FROM_DATE,
1061 info.PF_CURRENCY_CODE PF_CURRENCY_CODE,
1062 decode(invert.INVERT_ID,
1063 'G1', p_g1_currency_code,
1064 'G2', p_g2_currency_code) G_CURRENCY_CODE,
1065 decode(invert.INVERT_ID,
1066 'G1', PJI_UTILS.GET_RATE_TYPE,
1067 'G2', FND_PROFILE.VALUE('BIS_SECONDARY_RATE_TYPE')) RATE_TYPE
1068 from
1069 PJI_FM_EXTR_PLN_LOG log,
1070 PJI_ORG_EXTR_INFO info,
1071 (
1072 select 'G1' INVERT_ID from dual union all
1073 select 'G2' INVERT_ID from dual
1074 ) invert
1075 where
1076 bitand(to_number(log.RECORD_TYPE_CODE), 15) > 0 and
1077 log.PROJECT_ORG_ID = info.ORG_ID;
1078
1079 cursor missing_time (p_calendar_id in number) is
1080 select
1081 name.NAME CALENDAR_NAME,
1082 pt.USER_PERIOD_TYPE,
1083 tmp2.CALENDAR_MIN_DATE,
1084 tmp2.CALENDAR_MAX_DATE,
1085 min(tmp2.FROM_DATE) FROM_DATE,
1086 max(tmp2.TO_DATE) TO_DATE
1087 from
1088 (
1089 select
1090 info.CALENDAR_ID,
1091 to_date(info.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
1092 to_date(info.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
1093 min(log.FROM_DATE) FROM_DATE,
1094 max(log.TO_DATE) TO_DATE
1095 from
1096 PJI_FM_EXTR_PLN_LOG log,
1097 (
1098 select
1099 distinct
1100 decode(invert.INVERT_ID,
1101 'EN', p_calendar_id,
1102 'GL', info.GL_CALENDAR_ID,
1103 'PA', info.PA_CALENDAR_ID) CALENDAR_ID,
1104 decode(invert.INVERT_ID,
1105 'EN', info.EN_CALENDAR_MIN_DATE,
1106 'GL', info.GL_CALENDAR_MIN_DATE,
1107 'PA', info.PA_CALENDAR_MIN_DATE) CALENDAR_MIN_DATE,
1108 decode(invert.INVERT_ID,
1109 'EN', info.EN_CALENDAR_MAX_DATE,
1110 'GL', info.GL_CALENDAR_MAX_DATE,
1111 'PA', info.PA_CALENDAR_MAX_DATE) CALENDAR_MAX_DATE
1112 from
1113 PJI_ORG_EXTR_INFO info,
1114 (
1115 select 'EN' INVERT_ID from dual union all
1116 select 'GL' INVERT_ID from dual union all
1117 select 'PA' INVERT_ID from dual
1118 ) invert
1119 where
1120 info.ORG_ID <> -1
1121 ) info
1122 where
1123 bitand(to_number(log.RECORD_TYPE_CODE), 16) > 0 and
1124 nvl(log.CALENDAR_ID, -1) = info.CALENDAR_ID
1125 group by
1126 info.CALENDAR_ID,
1127 to_date(info.CALENDAR_MIN_DATE, 'J'),
1128 to_date(info.CALENDAR_MAX_DATE, 'J')
1129 union all
1130 select
1131 tmp1.CALENDAR_ID,
1132 to_date(tmp1.CALENDAR_MIN_DATE, 'J') CALENDAR_MIN_DATE,
1133 to_date(tmp1.CALENDAR_MAX_DATE, 'J') CALENDAR_MAX_DATE,
1134 min(tmp1.FROM_DATE) FROM_DATE,
1135 max(tmp1.TO_DATE) TO_DATE
1136 from
1137 (
1138 select
1139 case when tmp1.CALENDAR_TYPE = 'C'
1140 then p_calendar_id
1141 when tmp1.CALENDAR_TYPE = 'P'
1142 then info.PA_CALENDAR_ID
1143 when tmp1.CALENDAR_TYPE = 'G'
1144 then info.GL_CALENDAR_ID
1145 end CALENDAR_ID,
1146 case when tmp1.CALENDAR_TYPE = 'C'
1147 then info.EN_CALENDAR_MIN_DATE
1148 when tmp1.CALENDAR_TYPE = 'P'
1149 then info.PA_CALENDAR_MIN_DATE
1150 when tmp1.CALENDAR_TYPE = 'G'
1151 then info.GL_CALENDAR_MIN_DATE
1152 end CALENDAR_MIN_DATE,
1153 case when tmp1.CALENDAR_TYPE = 'C'
1154 then info.EN_CALENDAR_MAX_DATE
1155 when tmp1.CALENDAR_TYPE = 'P'
1156 then info.PA_CALENDAR_MAX_DATE
1157 when tmp1.CALENDAR_TYPE = 'G'
1158 then info.GL_CALENDAR_MAX_DATE
1159 end CALENDAR_MAX_DATE,
1160 to_date(to_char(min(tmp1.FROM_TIME_ID)), 'J') FROM_DATE,
1161 to_date(to_char(max(tmp1.TO_TIME_ID)), 'J') TO_DATE
1162 from
1163 PJI_ORG_EXTR_INFO info,
1164 (
1165 select
1166 distinct
1167 tmp1.EXPENDITURE_ORG_ID ORG_ID,
1168 tmp1.CALENDAR_TYPE,
1169 tmp1.TIME_ID FROM_TIME_ID,
1170 tmp1.TIME_ID TO_TIME_ID,
1171 tmp1.DANGLING_FLAG
1172 from
1173 PJI_RM_DNGL_RES tmp1
1174 where
1175 tmp1.WORKER_ID = 0
1176 ) tmp1
1177 where
1178 tmp1.DANGLING_FLAG = 'T' and
1179 tmp1.ORG_ID = info.ORG_ID
1180 group by
1181 case when tmp1.CALENDAR_TYPE = 'C'
1182 then p_calendar_id
1183 when tmp1.CALENDAR_TYPE = 'P'
1184 then info.PA_CALENDAR_ID
1185 when tmp1.CALENDAR_TYPE = 'G'
1186 then info.GL_CALENDAR_ID
1187 end,
1188 case when tmp1.CALENDAR_TYPE = 'C'
1189 then info.EN_CALENDAR_MIN_DATE
1190 when tmp1.CALENDAR_TYPE = 'P'
1191 then info.PA_CALENDAR_MIN_DATE
1192 when tmp1.CALENDAR_TYPE = 'G'
1193 then info.GL_CALENDAR_MIN_DATE
1194 end,
1195 case when tmp1.CALENDAR_TYPE = 'C'
1196 then info.EN_CALENDAR_MAX_DATE
1197 when tmp1.CALENDAR_TYPE = 'P'
1198 then info.PA_CALENDAR_MAX_DATE
1199 when tmp1.CALENDAR_TYPE = 'G'
1200 then info.GL_CALENDAR_MAX_DATE
1201 end
1202 ) tmp1
1203 group by
1204 tmp1.CALENDAR_ID,
1205 to_date(tmp1.CALENDAR_MIN_DATE, 'J'),
1206 to_date(tmp1.CALENDAR_MAX_DATE, 'J')
1207 ) tmp2,
1208 FII_TIME_CAL_NAME name,
1209 GL_PERIOD_TYPES pt
1210 where
1211 name.CALENDAR_ID = tmp2.CALENDAR_ID and
1212 pt.PERIOD_TYPE = name.PERIOD_TYPE
1213 group by
1214 name.NAME,
1215 pt.USER_PERIOD_TYPE,
1216 tmp2.CALENDAR_MIN_DATE,
1217 tmp2.CALENDAR_MAX_DATE;Commented for bug 13011859 */
1218
1219 l_calendar_id varchar2(255);
1220 l_header_flag varchar2(1);
1221 l_newline varchar2(10) := '
1222 ';
1223
1224 begin
1225
1226 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'N');
1227
1228 --
1229 -- Report dangling rates
1230 --
1231
1232 l_header_flag := 'Y';
1233
1234 /* for c in missing_rates(PJI_UTILS.GET_GLOBAL_PRIMARY_CURRENCY,
1235 PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY) loop
1236
1237 if (l_header_flag = 'Y') then
1238
1239 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'Y');
1240
1241 pji_utils.write2out(l_newline || PJI_UTILS.getMissingRateHeader);
1242
1243 l_header_flag := 'N';
1244
1245 end if;
1246
1247 pji_utils.write2out(
1248 PJI_UTILS.getMissingRateText(c.RATE_TYPE,
1249 c.PF_CURRENCY_CODE,
1250 c.G_CURRENCY_CODE,
1251 c.FROM_DATE,
1252 to_char(c.FROM_DATE, 'YYYY/MM/DD')) ||
1253 l_newline);
1254
1255 end loop;
1256
1257 --
1258 -- Report time dimension gaps
1259 --
1260
1261 select CALENDAR_ID
1262 into l_calendar_id
1263 from FII_TIME_CAL_NAME
1264 where PERIOD_SET_NAME = PJI_UTILS.GET_PERIOD_SET_NAME and
1265 PERIOD_TYPE = PJI_UTILS.GET_PERIOD_TYPE;
1266
1267 l_header_flag := 'Y';
1268
1269 for c in missing_time(l_calendar_id) loop
1270
1271 if (l_header_flag = 'Y') then
1272
1273 PJI_UTILS.SET_PARAMETER('DANGLING_PJI_ROWS_EXIST', 'Y');
1274
1275 pji_utils.write2out(PJI_RM_SUM_MAIN.GET_MISSING_TIME_HEADER);
1276 l_header_flag := 'N';
1277 end if;
1278
1279 if (c.FROM_DATE < c.CALENDAR_MIN_DATE and
1280 c.TO_DATE > c.CALENDAR_MAX_DATE) then
1281
1282 pji_utils.write2out(
1283 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1284 c.USER_PERIOD_TYPE,
1285 c.FROM_DATE,
1286 c.CALENDAR_MIN_DATE));
1287
1288 pji_utils.write2out(
1289 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1290 c.USER_PERIOD_TYPE,
1291 c.CALENDAR_MAX_DATE,
1292 c.TO_DATE));
1293
1294 elsif (c.TO_DATE > c.CALENDAR_MAX_DATE) then
1295
1296 pji_utils.write2out(
1297 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1298 c.USER_PERIOD_TYPE,
1299 c.CALENDAR_MAX_DATE,
1300 c.TO_DATE));
1301
1302 elsif (c.FROM_DATE < c.CALENDAR_MIN_DATE) then
1303
1304 pji_utils.write2out(
1305 PJI_RM_SUM_MAIN.GET_MISSING_TIME_TEXT(c.CALENDAR_NAME,
1306 c.USER_PERIOD_TYPE,
1307 c.FROM_DATE,
1308 c.CALENDAR_MIN_DATE));
1309
1310 end if;
1311
1312 end loop;
1313
1314 pji_utils.write2out(l_newline);
1315
1316 commit;Commented for bug 13011859 */
1317
1318 end DANGLING_REPORT;
1319
1320
1321 -- -----------------------------------------------------
1322 -- procedure WRAPUP_SETUP
1323 -- -----------------------------------------------------
1324 procedure WRAPUP_SETUP is
1325
1326 l_params_proj_perf_flag varchar2(1);
1327 l_params_cost_flag varchar2(1);
1328 l_params_profit_flag varchar2(1);
1329 l_params_util_flag varchar2(1);
1330
1331 begin
1332
1333 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1334 'TRANSITION') = 'Y') then
1335
1336 l_params_proj_perf_flag :=
1337 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1338 'CONFIG_PROJ_PERF_FLAG'), 'N');
1339 l_params_cost_flag :=
1340 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1341 'CONFIG_COST_FLAG'), 'N');
1342 l_params_profit_flag :=
1343 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1344 'CONFIG_PROFIT_FLAG'), 'N');
1345 l_params_util_flag :=
1346 nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process,
1347 'CONFIG_UTIL_FLAG'), 'N');
1348
1349 if (l_params_proj_perf_flag = 'Y') then
1350 PJI_UTILS.SET_PARAMETER('CONFIG_PROJ_PERF_FLAG', 'Y');
1351 end if;
1352
1353 if (l_params_cost_flag = 'Y') then
1354 PJI_UTILS.SET_PARAMETER('CONFIG_COST_FLAG', 'Y');
1355 end if;
1356
1357 if (l_params_profit_flag = 'Y') then
1358 PJI_UTILS.SET_PARAMETER('CONFIG_PROFIT_FLAG', 'Y');
1359 end if;
1360
1361 if (l_params_util_flag = 'Y') then
1362 PJI_UTILS.SET_PARAMETER('CONFIG_UTIL_FLAG', 'Y');
1363 end if;
1364
1365 update PJI_SYSTEM_CONFIG_HIST
1366 set END_DATE = sysdate
1367 where PROCESS_NAME = g_process || 1 and
1368 RUN_TYPE = 'TRANSITION' and
1369 END_DATE is null;
1370
1371 end if;
1372
1373 end WRAPUP_SETUP;
1374
1375 -- -----------------------------------------------------
1376 -- procedure WRAPUP_PROCESS
1377 -- -----------------------------------------------------
1378 procedure WRAPUP_PROCESS is
1379
1380 l_parallel_processes number;
1381 l_extraction_type varchar2(30);
1382 l_request_id number;
1383 l_batch_count number;
1384 l_schema varchar2(30);
1385
1386 begin
1387
1388 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(g_process || 1, 'PJI_RM_SUM_MAIN.WRAPUP_PROCESS;')) then
1389 return;
1390 end if;
1391
1392 l_extraction_type := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1393 (g_process, 'EXTRACTION_TYPE');
1394
1395 l_parallel_processes :=
1396 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PARALLEL_PROCESSES');
1397
1398 /* for x in 2 .. l_parallel_processes loop
1399 PJI_RM_SUM_EXTR.WAIT_FOR_WORKER(x);
1400 end loop;
1401
1402 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') = 'A') then
1403 fnd_message.set_name('PJI','PJI_SUM_ABORT');
1404 dbms_standard.raise_application_error(-20000, fnd_message.get);
1405 elsif (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') = 'F') then
1406 fnd_message.set_name('PJI','PJI_SUM_FAIL');
1407 dbms_standard.raise_application_error(-20000, fnd_message.get);
1408 end if;
1409
1410 DANGLING_REPORT;
1411
1412 -- clean up worker tables
1413 PJI_FM_PLAN_EXTR.CLEANUP_LOG;
1414
1415 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1416
1417 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1418 'PJI_RM_ORG_BATCH_MAP','NORMAL',null);
1419
1420 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1421 'PJI_PJI_PROJ_BATCH_MAP','NORMAL',null);
1422
1423 WRAPUP_SETUP;
1424
1425 PJI_PROCESS_UTIL.WRAPUP_PROCESS(g_process || 1);
1426 PJI_PROCESS_UTIL.WRAPUP_PROCESS(g_process);
1427
1428 update PJI_SYSTEM_CONFIG_HIST
1429 set END_DATE = sysdate,
1430 COMPLETION_TEXT = 'Normal completion'
1431 where PROCESS_NAME = g_process || 1 and
1432 END_DATE is null;
1433
1434 PJI_UTILS.SET_PARAMETER('LAST_PJI_EXTR_DATE', to_char(sysdate, 'YYYY/MM/DD'));
1435 commit;
1436
1437 -- calculate statistics on temporary tables used to retrieve fact data
1438
1439 PJI_PMV_UTIL.SEED_PJI_STATS;Commented for bug 13011859 */
1440
1441 commit;
1442
1443 end WRAPUP_PROCESS;
1444
1445
1446 -- -----------------------------------------------------
1447 -- procedure WRAPUP_FAILURE
1448 -- -----------------------------------------------------
1449 procedure WRAPUP_FAILURE is
1450
1451 l_sqlerrm varchar2(240);
1452
1453 begin
1454
1455 rollback;
1456
1457 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1458 'PROCESS_RUNNING',
1459 'F');
1460
1461 commit;
1462
1463 pji_utils.write2log(sqlerrm, true, 0);
1464
1465 l_sqlerrm := substr(sqlerrm, 1, 240);
1466
1467 update PJI_SYSTEM_CONFIG_HIST
1468 set END_DATE = sysdate,
1469 COMPLETION_TEXT = l_sqlerrm
1470 where PROCESS_NAME = g_process || 1 and
1471 END_DATE is null;
1472
1473 commit;
1474
1475 end WRAPUP_FAILURE;
1476
1477
1478 -- -----------------------------------------------------
1479 -- procedure SHUTDOWN_PROCESS
1480 -- -----------------------------------------------------
1481 procedure SHUTDOWN_PROCESS (errbuf out nocopy varchar2,
1482 retcode out nocopy varchar2) is
1483
1484 begin
1485
1486 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, 'PROCESS_RUNNING') <> 'Y') then
1487
1488 retcode := 1;
1489
1490 else
1491
1492 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1493 'PROCESS_RUNNING',
1494 'A');
1495 commit;
1496 retcode := 0;
1497
1498 end if;
1499
1500 exception when others then
1501
1502 retcode := 2;
1503 errbuf := sqlerrm;
1504
1505 end SHUTDOWN_PROCESS;
1506
1507
1508 -- -----------------------------------------------------
1509 -- procedure SUMMARIZE
1510 --
1511 -- This the the main procedure, it is invoked from
1512 -- a concurrent program.
1513 -- -----------------------------------------------------
1514 procedure SUMMARIZE
1515 (
1516 errbuf out nocopy varchar2,
1517 retcode out nocopy varchar2,
1518 p_run_mode in varchar2,
1519 p_prtl_schedule in varchar2 default null, -- RM
1520 p_organization_id in number default null, -- RM parameter
1521 p_include_sub_org in varchar2 default 'N', -- RM parameter
1522 p_prtl_financial in varchar2 default null, -- FM
1523 p_operating_unit in number default null, -- FM parameter
1524 p_from_project in varchar2 default null, -- FM parameter
1525 p_to_project in varchar2 default null, -- FM parameter
1526 p_plan_type in varchar2 default null -- FM parameter
1527 ) is
1528
1529 l_pji_not_licensed exception;
1530 pragma exception_init(l_pji_not_licensed, -20020);
1531 l_prior_iteration_successful boolean;
1532 l_transition_flag varchar2(1);
1533 --Bug 4892320.
1534 l_org_count NUMBER;
1535
1536 l_sum_running varchar2(255) := 'The process has failed due to a previously running process.';
1537 l_sum_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' is not complete.';
1538 l_sum_refresh_fail varchar2(255) := 'The process has failed because process ''Update Project Intelligence Data'' has not yet been run.';
1539
1540 begin
1541 pa_debug.set_process('PLSQL'); /* start 4893117*/
1542 IF p_run_mode in ('P') then
1543 pa_debug.log_message('=======Concurrent Program Parameters Start =======', 1);
1544 pa_debug.log_message('Argument => Refresh Schedule ['||p_prtl_schedule||']', 1);
1545 pa_debug.log_message('Argument => Expenditure Organization (schedule) ['||p_organization_id||']', 1);
1546 pa_debug.log_message('Argument => Include Sub-Organizations (schedule) ['||p_include_sub_org||']', 1);
1547 pa_debug.log_message('Argument => Refresh Financial ['||p_prtl_financial||']', 1);
1548 pa_debug.log_message('Argument => Project Operating Unit (financial) ['||p_operating_unit||']', 1);
1549 pa_debug.log_message('Argument => From Project (financial) ['||p_from_project||']', 1);
1550 pa_debug.log_message('Argument => To Project (financial) ['||p_to_project||']', 1);
1551 pa_debug.log_message('=======Concurrent Program Parameters End =======', 1);
1552 END IF; /* end 4893117*/
1553
1554 /* PJI_FM_DEBUG.CONC_REQUEST_HOOK(g_process);
1555
1556 /* this is removed as for bug#5075209
1557 if (PA_INSTALL.is_pji_licensed = 'N') then
1558 pji_utils.write2log('Error: PJI is not licensed.');
1559 commit;
1560 raise l_pji_not_licensed;
1561 end if;*
1562
1563 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process) <>
1564 FND_GLOBAL.CONC_REQUEST_ID and
1565 (PJI_PROCESS_UTIL.REQUEST_STATUS
1566 (
1567 'RUNNING',
1568 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
1569 g_full_disp_name
1570 ) or
1571 PJI_PROCESS_UTIL.REQUEST_STATUS
1572 (
1573 'RUNNING',
1574 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(g_process, g_process),
1575 g_incr_disp_name
1576 ))) then
1577 pji_utils.write2log('Error: Summarization is already running.');
1578 commit;
1579 dbms_standard.raise_application_error(-20010, l_sum_running);
1580 end if;
1581
1582 /*
1583
1584 Removing the fix for now. Post interop we need to consider the project
1585 count, since all stage 2 concurrent programs have this procedure as
1586 their entry point.
1587
1588 Customer can avoid this problem by, when they have no schedule data, not
1589 running schedule refresh.
1590
1591 -- Bug 4892320. If pji_org_extr_status has 0 records then there is nothing to summarize and hence return.
1592 IF p_run_mode='R' THEN
1593
1594 SELECT count(*)
1595 INTO l_org_count
1596 FROM pji_org_extr_status;
1597
1598 IF l_org_count=0 THEN
1599
1600 pji_utils.write2log('Nothing to summarize since pji_org_extr_status has 0 records');
1601 RETURN;
1602
1603 END IF;
1604
1605 END IF;
1606 *
1607 Commented for bug 13011859 */
1608 commit;
1609 execute immediate 'alter session enable parallel query';
1610 execute immediate 'alter session enable parallel dml';
1611
1612 PJI_PROCESS_UTIL.SET_PROCESS_PARAMETER(g_process,
1613 g_process,
1614 FND_GLOBAL.CONC_REQUEST_ID);
1615
1616 commit;
1617
1618 PJI_PROCESS_UTIL.REFRESH_STEP_TABLE;
1619
1620 -- determine if a transitional configuration is needed
1621 /* RUN_SETUP;
1622
1623 if (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1624 (g_process, 'TRANSITION') = 'N' and
1625 (PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1626 (g_process, 'CONFIG_PROJ_PERF_FLAG') = 'Y' or
1627 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1628 (g_process, 'CONFIG_COST_FLAG') = 'Y' or
1629 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1630 (g_process, 'CONFIG_PROFIT_FLAG') = 'Y' or
1631 PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1632 (g_process, 'CONFIG_UTIL_FLAG') = 'Y')) then
1633 retcode := 1;
1634 else
1635 retcode := 0;
1636 end if;
1637
1638 l_prior_iteration_successful := PRIOR_ITERATION_SUCCESSFUL;
1639
1640 begin
1641
1642 INIT_PROCESS(p_run_mode,
1643 p_prtl_schedule,
1644 p_organization_id,
1645 p_include_sub_org,
1646 p_prtl_financial,
1647 p_operating_unit,
1648 p_from_project,
1649 p_to_project,
1650 p_plan_type);
1651
1652 -- Synchronize PJI_RM_WORK_TYPE_INFO with transaction system
1653 PJI_PJI_EXTRACTION_UTILS.UPDATE_PJI_RM_WORK_TYPE_INFO(g_process || 1);
1654
1655 -- Determine if Jobs have become utilizable or non-utilizable
1656 PJI_PJI_EXTRACTION_UTILS.UPDATE_RESOURCE_DATA(g_process || 1);
1657
1658 RUN_PROCESS;
1659 WRAPUP_PROCESS;
1660
1661 if (PJI_UTILS.GET_PARAMETER('DANGLING_PJI_ROWS_EXIST') = 'Y') then
1662 retcode := 1;
1663 else
1664 retcode := 0;
1665 end if;
1666
1667 commit;
1668 execute immediate 'alter session disable parallel dml';
1669
1670 exception when others then
1671
1672 WRAPUP_FAILURE;
1673 execute immediate 'alter session disable parallel dml';
1674 retcode := 2;
1675 errbuf := sqlerrm;
1676 raise;
1677
1678 end;Commented for bug 13011859 */
1679
1680 exception when others then
1681
1682 rollback;
1683 retcode := 2;
1684 errbuf := sqlerrm;
1685 raise;
1686
1687 end SUMMARIZE;
1688
1689 end PJI_RM_SUM_MAIN;