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