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