[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_CMT_EXTR
Source
1 package body PJI_FM_CMT_EXTR as
2 /* $Header: PJISF14B.pls 120.5.12010000.3 2008/11/28 05:53:57 paljain ship $ */
3
4 PROCEDURE accum_projperf_commitments
5 ( x_project_id IN NUMBER,
6 x_err_stage IN OUT NOCOPY VARCHAR2,
7 x_err_code IN OUT NOCOPY NUMBER)
8 IS
9
10 CURSOR selcmts IS
11 SELECT
12 pct.rowid,
13 pct.cmt_line_id,
14 pct.project_id,
15 pct.task_id,
16 pa_utils.GetWeekEnding(pct.expenditure_item_date) week_ending_date,
17 LAST_DAY(pct.expenditure_item_date) month_ending_date,
18 pct.pa_period,
19 pct.gl_period,
20 pct.organization_id,
21 pct.vendor_id,
22 pct.expenditure_type,
23 pct.expenditure_category,
24 pct.revenue_category,
25 pct.system_linkage_function,
26 pct.cmt_ind_compiled_set_id,
27 pct.expenditure_item_date,
28 pct.denom_currency_code,
29 pct.denom_raw_cost,
30 pct.denom_burdened_cost,
31 pct.acct_currency_code,
32 pct.acct_rate_date,
33 pct.acct_rate_type,
34 pct.acct_exchange_rate,
35 pct.acct_raw_cost,
36 pct.acct_burdened_cost,
37 pct.receipt_currency_code,
38 pct.receipt_currency_amount,
39 pct.receipt_exchange_rate
40 FROM
41 pa_commitment_txns pct
42 WHERE
43 pct.project_id = x_project_id ;
44
45
46 CURSOR l_project_curr_code_csr
47 (l_project_id pa_projects_all.project_id%TYPE)
48 IS
49 SELECT project_currency_code,projfunc_currency_code
50 FROM pa_projects_all p
51 WHERE p.project_id = l_project_id;
52
53
54 l_proj_curr_OK VARCHAR2(1) := 'Y';
55 l_project_curr_code pa_projects_all.project_currency_code%TYPE := NULL;
56
57
58 cmtrec selcmts%ROWTYPE;
59 row_processed NUMBER;
60 l_cmtrec_curr_OK VARCHAR2(1);
61 l_cmt_rejection_code pa_commitment_txns.cmt_rejection_code%TYPE;
62 l_err_msg VARCHAR2(2000);
63
64 l_Project_Rate_Type pa_commitment_txns.project_rate_type%TYPE;
65 l_Project_Rate_Date DATE;
66 l_project_exch_rate NUMBER;
67 l_PROJECT_RAW_COST NUMBER := NULL;
68 l_PROJECT_BURDENED_COST NUMBER := NULL; -- added for FP.M
69
70 l_amount_out NUMBER;
71 l_tot_cmt_raw_cost NUMBER;
72 l_tot_cmt_burdened_cost NUMBER;
73 l_status VARCHAR2(200) := NULL;
74 l_stage NUMBER := NULL;
75
76 l_SYSTEM_LINKAGE pa_expenditure_items_all.SYSTEM_LINKAGE_FUNCTION%TYPE :=NULL;
77
78
79 l_PROJFUNC_CURR_CODE pa_projects_all.project_currency_code%TYPE := NULL;
80 l_PROJFUNC_COST_RATE_TYPE pa_commitment_txns.project_rate_type%TYPE := NULL;
81 l_PROJFUNC_COST_RATE_DATE DATE := NULL;
82 l_PROJFUNC_COST_EXCH_RATE NUMBER := NULL;
83
84 BEGIN
85 pji_utils.write2log('Enering accum_projperf_commitments');
86 x_err_code :=0;
87 x_err_stage := 'Accumulating Commitments';
88
89 row_processed :=0;
90
91 OPEN l_project_curr_code_csr(x_project_id);
92 FETCH l_project_curr_code_csr INTO l_project_curr_code,l_projfunc_curr_code;
93 CLOSE l_project_curr_code_csr;
94
95 FOR cmtrec IN selcmts LOOP
96
97 row_processed := row_processed + 1;
98
99 l_cmtrec_curr_OK := 'Y';
100 l_project_Rate_Type := NULL;
101 l_project_Rate_Date := cmtrec.expenditure_item_date;
102 l_project_exch_rate := NULL;
103 l_project_raw_cost := NULL;
104 l_project_burdened_cost := NULL;
105
106 l_amount_out := NULL;
107 l_tot_cmt_raw_cost := NULL;
108 l_tot_cmt_burdened_cost := NULL;
109 l_status := NULL;
110 l_stage := NULL;
111 l_cmt_rejection_code := NULL;
112 l_err_msg := NULL;
113
114 IF (l_cmtrec_curr_OK = 'Y')
115 THEN
116
117 pa_multi_currency_txn.get_currency_amounts
118 (p_project_curr_code => l_project_curr_code
119 , p_ei_date => cmtrec.expenditure_item_date
120 , p_task_id => cmtrec.task_id
121 , p_denom_raw_cost => cmtrec.denom_raw_cost
122 , p_denom_curr_code => cmtrec.denom_currency_code
123 , p_acct_curr_code => cmtrec.acct_currency_code
124 , p_accounted_flag => 'Y'
125 , p_acct_rate_date => cmtrec.acct_rate_date
126 , p_acct_rate_type => cmtrec.acct_rate_type
127 , p_acct_exch_rate => cmtrec.acct_exchange_rate
128 , p_acct_raw_cost => cmtrec.acct_raw_cost
129 , p_project_rate_type => l_project_rate_type
130 , p_project_rate_date => l_project_rate_date
131 , p_project_exch_rate => l_project_exch_rate
132 , P_PROJFUNC_RAW_COST => l_amount_out
133 , p_status => l_status
134 , p_stage => l_stage
135 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
136 , P_PROJECT_RAW_COST => l_PROJECT_RAW_COST
137 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
138 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
139 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
140 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
141 );
142
143
144 l_tot_cmt_raw_cost := l_amount_out;
145
146 IF (l_status IS NOT NULL) THEN
147 l_cmt_rejection_code := l_status;
148 l_cmtrec_curr_OK := 'N';
149 END IF;
150
151 IF (l_cmtrec_curr_OK = 'Y') THEN
152
153 IF cmtrec.denom_raw_cost <> cmtrec.denom_burdened_cost THEN
154 pa_multi_currency_txn.get_currency_amounts
155 (p_project_curr_code => l_project_curr_code
156 , p_ei_date => cmtrec.expenditure_item_date
157 , p_task_id => cmtrec.task_id
158 , p_denom_raw_cost => cmtrec.denom_burdened_cost
159 , p_denom_curr_code => cmtrec.denom_currency_code
160 , p_acct_curr_code => cmtrec.acct_currency_code
161 , p_accounted_flag => 'Y'
162 , p_acct_rate_date => cmtrec.acct_rate_date
163 , p_acct_rate_type => cmtrec.acct_rate_type
164 , p_acct_exch_rate => cmtrec.acct_exchange_rate
165 , p_acct_raw_cost => cmtrec.acct_burdened_cost
166 , p_project_rate_type => l_project_rate_type
167 , p_project_rate_date => l_project_rate_date
168 , p_project_exch_rate => l_project_exch_rate
169 , P_PROJFUNC_RAW_COST => l_amount_out
170 , p_status => l_status
171 , p_stage => l_stage
172 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
173 , P_PROJECT_RAW_COST => l_PROJECT_BURDENED_COST /*Bug#4137193*/
174 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
175 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
176 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
177 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
178 );
179 END IF;
180
181 l_tot_cmt_burdened_cost := l_amount_out;
182
183 /* placed the below statement within IF condition
184 for the bug 4137193 */
185
186 IF l_PROJECT_BURDENED_COST IS null THEN
187 l_PROJECT_BURDENED_COST := l_PROJECT_RAW_COST;
188 END IF;
189
190 IF (l_status IS NOT NULL) THEN
191 l_cmt_rejection_code := l_status;
192 l_cmtrec_curr_OK := 'N';
193 END IF;
194
195 END IF; --BURDENED COST
196
197 END IF; -- DERIVATION SUBsection
198
199 IF (l_cmtrec_curr_OK = 'Y') THEN
200 UPDATE pa_commitment_txns
201 SET tot_cmt_raw_cost = l_tot_cmt_raw_cost
202 , tot_cmt_burdened_cost = l_tot_cmt_burdened_cost
203 , project_currency_code = l_project_curr_code
204 , project_rate_date = l_project_rate_date
205 , project_rate_type = l_project_rate_type
206 , project_exchange_rate = l_project_exch_rate
207 , proj_raw_cost = l_PROJECT_RAW_COST
208 , proj_burdened_cost = l_PROJECT_BURDENED_COST
209 WHERE rowid = cmtrec.rowid;
210 ELSE
211 UPDATE pa_commitment_txns
212 SET generation_error_flag = 'Y'
213 , cmt_rejection_code = l_cmt_rejection_code
214 WHERE rowid = cmtrec.rowid;
215
216 l_proj_curr_OK := 'N';
217 END IF; -- UPDATE COMMITMENT ROW
218
219 END LOOP; -- CMTREC Processing
220
221 pji_utils.write2log('Leaving accum_projperf_commitments');
222 EXCEPTION
223 WHEN OTHERS THEN
224 x_err_code := SQLCODE;
225 pji_utils.write2log('within exception block of accum_projperf_commitments');
226 RAISE;
227
228 END accum_projperf_commitments;
229
230
231 -- -----------------------------------------------------
232 -- procedure REFRESH_PROJPERF_CMT_PRE
233 -- -----------------------------------------------------
234 procedure REFRESH_PROJPERF_CMT_PRE (p_worker_id in number) is
235
236 l_extract_commitments varchar2(30);
237 l_process varchar2(30);
238
239 l_operating_unit number := null;
240 l_from_project varchar2(50) := null;
241 l_to_project varchar2(50) := null;
242 l_batch_size number := 1;
243
244 begin
245
246 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
247
248 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
249 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);')) then
250 return;
251 end if;
252
253 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
254 (PJI_FM_SUM_MAIN.g_process,
255 'EXTRACT_COMMITMENTS');
256
257 if (l_extract_commitments = 'N') then
258 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
259 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);');
260 commit;
261 return;
262 end if;
263
264 l_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
265 (PJI_FM_SUM_MAIN.g_process, 'PROJECT_OPERATING_UNIT');
266
267 l_from_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
268 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT');
269
270 l_to_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
271 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT');
272
273 if (l_from_project is null) then
274
275 begin
276
277 select
278 prj.SEGMENT1
279 into
280 l_from_project
281 from
282 PA_PROJECTS_ALL prj
283 where
284 prj.PROJECT_ID = PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
285 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT_ID');
286
287 exception when no_data_found then null;
288
289 end;
290
291 end if;
292
293 if (l_to_project is null) then
294
295 begin
296
297 select
298 prj.SEGMENT1
299 into
300 l_to_project
301 from
302 PA_PROJECTS_ALL prj
303 where
304 prj.PROJECT_ID = PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
305 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT_ID');
306
307 exception when no_data_found then null;
308
309 end;
310
311 end if;
312
313 insert into PJI_FM_EXTR_DREVN -- overload of draft revenues table for cmt
314 (
315 WORKER_ID,
316 ROW_ID,
317 LINE_SOURCE_TYPE,
318 PROJECT_ID,
319 PA_PERIOD_NAME,
320 GL_PERIOD_NAME,
321 BATCH_ID
322 )
323 select
324 -1 WORKER_ID, -- not used
325 cmt.ROW_ID ROW_ID, -- not used
326 'X' LINE_SOURCE_TYPE, -- not used
327 cmt.PROJECT_ID PROJECT_ID,
328 cmt.PA_PERIOD PA_PERIOD_NAME,
329 cmt.GL_PERIOD GL_PERIOD_NAME,
330 ceil(ROWNUM / l_batch_size) BATCH_ID
331 from
332 (
333 select /*+ ordered */
334 prj.PROJECT_ID,
335 prj.ROW_ID,
336 per.PA_PERIOD,
337 per.GL_PERIOD
338 from
339 (
340 select /*+ index(prj, PA_PROJECTS_U1) */
341 prj.PROJECT_ID,
342 prj.ROWID ROW_ID,
343 prj.ORG_ID ORG_ID, /*5377131*/
344 prj.PROJECT_STATUS_CODE
345 from
346 PA_PROJECTS_ALL prj
347 where
348 prj.ORG_ID = nvl(l_operating_unit,
349 prj.ORG_ID) and /*5377131*/
350 prj.SEGMENT1 between nvl(l_from_project, prj.SEGMENT1) and
351 nvl(l_to_project, prj.SEGMENT1) and
352 prj.TEMPLATE_FLAG = 'N'
353 ) prj,
354 (
355 select
356 PROJECT_STATUS_CODE
357 from
358 (
359 select /*+ index_ffs(prj, PA_PROJECTS_N4)
360 parallel_index(prj, PA_PROJECTS_N4) */
361 distinct
362 prj.PROJECT_STATUS_CODE
363 from
364 PA_PROJECTS_ALL prj
365 )
366 where
367 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
368 (PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
369 ) psc,
370 (
371 select /*+ index(per, PA_PERIODS_N3) */
372 nvl(per.ORG_ID, -1) ORG_ID,
373 per.PERIOD_NAME PA_PERIOD,
374 per.GL_PERIOD_NAME GL_PERIOD
375 from
376 PA_PERIODS_ALL per
377 where
378 -- per.CURRENT_PA_PERIOD_FLAG = 'Y' Bug fix 7602463
379 trunc(sysdate) between per.start_date and per.end_date
380 ) per
381 where
382 prj.PROJECT_STATUS_CODE = psc.PROJECT_STATUS_CODE and
383 prj.ORG_ID = per.ORG_ID
384 ) cmt
385 where
386 1 = 1
387 -- The below API only checks for those projects that already
388 -- have rows in PA_COMMITMENTS_TXNS.
389 -- PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED(cmt.PROJECT_ID) = 'Y'
390 order by
391 cmt.PROJECT_ID;
392
393 insert into PJI_HELPER_BATCH_MAP
394 (
395 BATCH_ID,
396 WORKER_ID,
397 STATUS
398 )
399 select
400 distinct
401 BATCH_ID,
402 null,
403 null
404 from
405 PJI_FM_EXTR_DREVN; -- overload of draft revenues table for commitments
406
407 delete
408 from PA_COMMITMENT_TXNS
409 where PROJECT_ID in
410 (
411 select
412 PROJECT_ID
413 from
414 PJI_FM_EXTR_DREVN -- overload of draft revenues table for cmt
415 );
416
417 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
418 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);');
419
420 commit;
421
422 end REFRESH_PROJPERF_CMT_PRE;
423
424
425 -- -----------------------------------------------------
426 -- procedure REFRESH_PROJPERF_CMT
427 -- -----------------------------------------------------
428 procedure REFRESH_PROJPERF_CMT (p_worker_id in number) is
429
430 l_extract_commitments varchar2(30);
431 l_process varchar2(30);
432
433 l_leftover_batches number;
434 l_helper_batch_id number;
435 l_row_count number;
436 l_parallel_processes number;
437
438 x_run_id number;
439 x_status number;
440 x_stage number;
441 x_err_stage varchar2(120);
442 x_err_code number;
443
444 l_project_id number;
445 l_org_id number; -- bug 6847113
446
447 begin
448
449 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
450
451 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
452 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);')) then
453 return;
454 end if;
455
456 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
457 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
458
459 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
460 (PJI_FM_SUM_MAIN.g_process,
461 'EXTRACT_COMMITMENTS');
462
463 if (l_extract_commitments = 'N') then
464
465 for x in 2 .. l_parallel_processes loop
466
467 update PJI_SYSTEM_PRC_STATUS
468 set STEP_STATUS = 'C'
469 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
470 STEP_NAME =
471 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);' and
472 START_DATE is null;
473
474 commit;
475
476 end loop;
477
478 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
479 (l_process, 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);');
480
481 commit;
482
483 return;
484
485 end if;
486
487 select count(*)
488 into l_leftover_batches
489 from PJI_HELPER_BATCH_MAP
490 where WORKER_ID = p_worker_id and
491 STATUS = 'P';
492
493 l_helper_batch_id := 0;
494
495 while l_helper_batch_id >= 0 loop
496
497 if (l_leftover_batches > 0) then
498
499 l_leftover_batches := l_leftover_batches - 1;
500
501 select BATCH_ID
502 into l_helper_batch_id
503 from PJI_HELPER_BATCH_MAP
504 where WORKER_ID = p_worker_id and
505 STATUS = 'P' and
506 ROWNUM = 1;
507
508 else
509
510 update PJI_HELPER_BATCH_MAP
511 set WORKER_ID = p_worker_id,
512 STATUS = 'P'
513 where WORKER_ID is null and
514 ROWNUM = 1
515 returning BATCH_ID
516 into l_helper_batch_id;
517
518 end if;
519
520 if (sql%rowcount <> 0) then
521
522 commit;
523 -- bug 6847113
524 select org_id into l_org_id
525 from pa_projects_all
526 where project_id =
527 ( select project_id from PJI_FM_EXTR_DREVN
528 where BATCH_ID = l_helper_batch_id
529 and rownum=1);
530 PA_CURRENCY.G_org_id := l_org_id;
531
532 -- Bug 6847113
533
534
535 PA_TXN_ACCUMS.CREATE_CMT_TXNS(null,
536 l_helper_batch_id, -- overload of to
537 null, -- project parameter
538 x_err_stage,
539 x_err_code);
540
541 for c in (select PROJECT_ID
542 from PJI_FM_EXTR_DREVN -- overload of drev table for cmt
543 where BATCH_ID = l_helper_batch_id) loop
544
545 begin
546
547 -- Create summarized burden commitment transactions
548
549 PA_BURDEN_COSTING.CREATE_BURDEN_CMT_TRANSACTION
550 (x_project_id => c.PROJECT_ID,
551 status => x_status,
552 stage => x_stage ,
553 x_run_id => x_run_id);
554
555 ACCUM_PROJPERF_COMMITMENTS(c.PROJECT_ID,
556 x_err_stage,
557 x_err_code);
558
559 exception when others then
560
561 x_err_code := SQLCODE;
562
563 end;
564
565 end loop;
566
567 update PJI_HELPER_BATCH_MAP
568 set STATUS = 'C'
569 where WORKER_ID = p_worker_id and
570 BATCH_ID = l_helper_batch_id;
571
572 commit;
573 PA_CURRENCY.G_org_id := NULL; -- bug 6847113
574
575 else
576
577 select count(*)
578 into l_row_count
579 from PJI_HELPER_BATCH_MAP
580 where nvl(STATUS, 'X') <> 'C';
581
582 if (l_row_count = 0) then
583
584 for x in 2 .. l_parallel_processes loop
585
586 update PJI_SYSTEM_PRC_STATUS
587 set STEP_STATUS = 'C'
588 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
589 STEP_NAME =
590 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);' and
591 START_DATE is null;
592
593 commit;
594
595 end loop;
596
597 l_helper_batch_id := -1;
598
599 else
600
601 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
602
603 end if;
604
605 end if;
606
607 if (l_helper_batch_id >= 0) then
608
609 for x in 2 .. l_parallel_processes loop
610 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
611 l_helper_batch_id := -2;
612 end if;
613 end loop;
614
615 end if;
616
617 end loop;
618
619 if (l_helper_batch_id <> -2) then
620
621 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
622 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);');
623
624 end if;
625
626 commit;
627
628 end REFRESH_PROJPERF_CMT;
629
630
631 -- -----------------------------------------------------
632 -- procedure REFRESH_PROJPERF_CMT_POST
633 -- -----------------------------------------------------
634 procedure REFRESH_PROJPERF_CMT_POST (p_worker_id in number) is
635
636 l_extract_commitments varchar2(30);
637 l_process varchar2(30);
638 l_schema varchar2(30);
639
640 begin
641
642 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
643
644 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
645 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);')) then
646 return;
647 end if;
648
649 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
650 (PJI_FM_SUM_MAIN.g_process,
651 'EXTRACT_COMMITMENTS');
652
653 if (l_extract_commitments = 'N') then
654 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
655 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);');
656 commit;
657 return;
658 end if;
659
660 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
661
662 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
663 'PJI_FM_EXTR_DREVN',
664 'NORMAL',
665 null);
666
667 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
668 'PJI_HELPER_BATCH_MAP',
669 'NORMAL',
670 null);
671
672 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
673 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);');
674
675 commit;
676
677 end REFRESH_PROJPERF_CMT_POST;
678
679
680 -- -----------------------------------------------------
681 -- procedure FIN_CMT_SUMMARY
682 -- -----------------------------------------------------
683 procedure FIN_CMT_SUMMARY (p_worker_id in number) is
684
685 l_extract_commitments varchar2(30);
686 l_process varchar2(30);
687
688 l_transition_flag varchar2(1);
689 l_params_cost_flag varchar2(1);
690 l_params_util_flag varchar2(1);
691 l_g2_currency_code varchar2(30);
692
693 begin
694
695 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
696
697 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
698 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);')) then
699 return;
700 end if;
701
702 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
703 (PJI_FM_SUM_MAIN.g_process,
704 'EXTRACT_COMMITMENTS');
705
706 if (l_extract_commitments = 'N') then
707 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
708 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);');
709 commit;
710 return;
711 end if;
712
713 pji_utils.write2log('Entering FIN_CMT_SUMMARY');
714 l_transition_flag := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process, 'TRANSITION');
715
716 if (l_transition_flag = 'Y') then
717 l_params_cost_flag := nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,'CONFIG_COST_FLAG'), 'N');
718 else -- l_transition is null or 'N'
719 l_params_cost_flag := nvl(PJI_UTILS.GET_PARAMETER('CONFIG_COST_FLAG'), 'N');
720 end if;
721
722 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
723
724 if (l_g2_currency_code is not null) then
725 l_g2_currency_code := 'Y';
726 else
727 l_g2_currency_code := 'N';
728 end if;
729
730 insert /*+ append parallel(fin2_i) */ into PJI_FM_AGGR_FIN2 fin2_i -- in FIN_SUMMARY
731 (
732 WORKER_ID,
733 ROW_ID,
734 RECORD_TYPE,
735 CMT_RECORD_TYPE,
736 DANGLING_RECVR_GL_RATE_FLAG,
737 DANGLING_RECVR_PA_RATE_FLAG,
738 DANGLING_RECVR_GL_RATE2_FLAG,
739 DANGLING_RECVR_PA_RATE2_FLAG,
740 DANGLING_PRVDR_EN_TIME_FLAG,
741 DANGLING_PRVDR_GL_TIME_FLAG,
742 DANGLING_PRVDR_PA_TIME_FLAG,
743 DANGLING_RECVR_EN_TIME_FLAG,
744 DANGLING_RECVR_GL_TIME_FLAG,
745 DANGLING_RECVR_PA_TIME_FLAG,
746 DANGLING_EXP_EN_TIME_FLAG,
747 DANGLING_EXP_GL_TIME_FLAG,
748 DANGLING_EXP_PA_TIME_FLAG,
749 PJI_PROJECT_RECORD_FLAG,
750 PJI_RESOURCE_RECORD_FLAG,
751 PROJECT_ID,
752 PROJECT_ORG_ID,
753 PROJECT_ORGANIZATION_ID,
754 PERSON_ID,
755 EXPENDITURE_ORG_ID,
756 EXPENDITURE_ORGANIZATION_ID,
757 WORK_TYPE_ID,
758 JOB_ID,
759 EXP_EVT_TYPE_ID,
760 PROJECT_TYPE_CLASS,
761 TASK_ID,
762 VENDOR_ID,
763 EXPENDITURE_TYPE,
764 EVENT_TYPE,
765 EVENT_TYPE_CLASSIFICATION,
766 EXPENDITURE_CATEGORY,
767 REVENUE_CATEGORY,
768 NON_LABOR_RESOURCE,
769 BOM_LABOR_RESOURCE_ID,
770 BOM_EQUIPMENT_RESOURCE_ID,
771 INVENTORY_ITEM_ID,
772 SYSTEM_LINKAGE_FUNCTION,
773 RESOURCE_CLASS_CODE,
774 PRVDR_GL_TIME_ID,
775 RECVR_GL_TIME_ID,
776 GL_PERIOD_NAME,
777 PRVDR_PA_TIME_ID,
778 RECVR_PA_TIME_ID,
779 PA_PERIOD_NAME,
780 EXPENDITURE_ITEM_TIME_ID,
781 PJ_GL_CALENDAR_ID,
782 PJ_PA_CALENDAR_ID,
783 RS_GL_CALENDAR_ID,
784 RS_PA_CALENDAR_ID,
785 TXN_CURRENCY_CODE,
786 TXN_REVENUE,
787 TXN_RAW_COST,
788 TXN_BRDN_COST,
789 TXN_BILL_RAW_COST,
790 TXN_BILL_BRDN_COST,
791 PRJ_REVENUE,
792 PRJ_LABOR_REVENUE,
793 PRJ_RAW_COST,
794 PRJ_BRDN_COST,
795 PRJ_BILL_RAW_COST,
796 PRJ_BILL_BRDN_COST,
797 PRJ_LABOR_RAW_COST,
798 PRJ_LABOR_BRDN_COST,
799 PRJ_BILL_LABOR_RAW_COST,
800 PRJ_BILL_LABOR_BRDN_COST,
801 PRJ_REVENUE_WRITEOFF,
802 POU_REVENUE,
803 POU_LABOR_REVENUE,
804 POU_RAW_COST,
805 POU_BRDN_COST,
806 POU_BILL_RAW_COST,
807 POU_BILL_BRDN_COST,
808 POU_LABOR_RAW_COST,
809 POU_LABOR_BRDN_COST,
810 POU_BILL_LABOR_RAW_COST,
811 POU_BILL_LABOR_BRDN_COST,
812 POU_REVENUE_WRITEOFF,
813 EOU_REVENUE,
814 EOU_RAW_COST,
815 EOU_BRDN_COST,
816 EOU_BILL_RAW_COST,
817 EOU_BILL_BRDN_COST,
818 LABOR_HRS,
819 BILL_LABOR_HRS,
820 TOTAL_HRS_A,
821 BILL_HRS_A,
822 GG1_REVENUE,
823 GG1_LABOR_REVENUE,
824 GG1_RAW_COST,
825 GG1_BRDN_COST,
826 GG1_BILL_RAW_COST,
827 GG1_BILL_BRDN_COST,
828 GG1_LABOR_RAW_COST,
829 GG1_LABOR_BRDN_COST,
830 GG1_BILL_LABOR_RAW_COST,
831 GG1_BILL_LABOR_BRDN_COST,
832 GG1_REVENUE_WRITEOFF,
833 GP1_REVENUE,
834 GP1_LABOR_REVENUE,
835 GP1_RAW_COST,
836 GP1_BRDN_COST,
837 GP1_BILL_RAW_COST,
838 GP1_BILL_BRDN_COST,
839 GP1_LABOR_RAW_COST,
840 GP1_LABOR_BRDN_COST,
841 GP1_BILL_LABOR_RAW_COST,
842 GP1_BILL_LABOR_BRDN_COST,
843 GP1_REVENUE_WRITEOFF,
844 GG2_REVENUE,
845 GG2_LABOR_REVENUE,
846 GG2_RAW_COST,
847 GG2_BRDN_COST,
848 GG2_BILL_RAW_COST,
849 GG2_BILL_BRDN_COST,
850 GG2_LABOR_RAW_COST,
851 GG2_LABOR_BRDN_COST,
852 GG2_BILL_LABOR_RAW_COST,
853 GG2_BILL_LABOR_BRDN_COST,
854 GG2_REVENUE_WRITEOFF,
855 GP2_REVENUE,
856 GP2_LABOR_REVENUE,
857 GP2_RAW_COST,
858 GP2_BRDN_COST,
859 GP2_BILL_RAW_COST,
860 GP2_BILL_BRDN_COST,
861 GP2_LABOR_RAW_COST,
862 GP2_LABOR_BRDN_COST,
863 GP2_BILL_LABOR_RAW_COST,
864 GP2_BILL_LABOR_BRDN_COST,
865 GP2_REVENUE_WRITEOFF
866 )
867 select /*+ no_merge(tmp1) */
868 1 WORKER_ID,
869 null ROW_ID,
870 'M' RECORD_TYPE,
871 tmp1.LINE_TYPE CMT_RECORD_TYPE,
872 tmp1.DANGLING_RECVR_GL_RATE_FLAG,
873 tmp1.DANGLING_RECVR_PA_RATE_FLAG,
874 tmp1.DANGLING_RECVR_GL_RATE2_FLAG,
875 tmp1.DANGLING_RECVR_PA_RATE2_FLAG,
876 null DANGLING_PRVDR_EN_TIME_FLAG,
877 null DANGLING_PRVDR_GL_TIME_FLAG,
878 null DANGLING_PRVDR_PA_TIME_FLAG,
879 tmp1.DANGLING_RECVR_EN_TIME_FLAG,
880 tmp1.DANGLING_RECVR_GL_TIME_FLAG,
881 tmp1.DANGLING_RECVR_PA_TIME_FLAG,
882 tmp1.DANGLING_EXP_EN_TIME_FLAG,
883 tmp1.DANGLING_EXP_GL_TIME_FLAG,
884 tmp1.DANGLING_EXP_PA_TIME_FLAG,
885 decode(l_params_cost_flag,'N','N','Y') PJI_PROJECT_RECORD_FLAG,
886 'N' PJI_RESOURCE_RECORD_FLAG,
887 tmp1.PROJECT_ID,
888 tmp1.PROJECT_ORG_ID,
889 tmp1.PROJECT_ORGANIZATION_ID,
890 -1 PERSON_ID,
891 -1 EXPENDITURE_ORG_ID,
892 tmp1.EXPENDITURE_ORGANIZATION_ID,
893 -1 WORK_TYPE_ID,
894 -1 JOB_ID,
895 et.EXPENDITURE_TYPE_ID EXP_EVT_TYPE_ID,
896 tmp1.PROJECT_TYPE_CLASS,
897 tmp1.TASK_ID,
898 tmp1.VENDOR_ID,
899 et.EXPENDITURE_TYPE EXPENDITURE_TYPE,
900 'PJI$NULL' EVENT_TYPE,
901 'PJI$NULL' EVENT_TYPE_CLASSIFICATION,
902 tmp1.EXPENDITURE_CATEGORY,
903 et.REVENUE_CATEGORY_CODE REVENUE_CATEGORY,
904 'PJI$NULL' NON_LABOR_RESOURCE,
905 tmp1.BOM_LABOR_RESOURCE_ID,
906 tmp1.BOM_EQUIPMENT_RESOURCE_ID,
907 tmp1.INVENTORY_ITEM_ID,
908 tmp1.SYSTEM_LINKAGE_FUNCTION,
909 tmp1.RESOURCE_CLASS_CODE,
910 -1 PRVDR_GL_TIME_ID,
911 tmp1.RECVR_GL_TIME_ID,
912 tmp1.GL_PERIOD_NAME,
913 -1 PRVDR_PA_TIME_ID,
914 tmp1.RECVR_PA_TIME_ID,
915 tmp1.PA_PERIOD_NAME,
916 tmp1.EXPENDITURE_ITEM_TIME_ID,
917 tmp1.PJ_GL_CALENDAR_ID,
918 tmp1.PJ_PA_CALENDAR_ID,
919 -1 RS_GL_CALENDAR_ID,
920 -1 RS_PA_CALENDAR_ID,
921 tmp1.DENOM_CURRENCY_CODE TXN_CURRENCY_CODE,
922 to_number(null) TXN_REVENUE,
923 tmp1.DENOM_RAW_COST TXN_RAW_COST,
924 tmp1.DENOM_BURDENED_COST TXN_BRDN_COST,
925 to_number(null) TXN_BILL_RAW_COST,
926 to_number(null) TXN_BILL_BRDN_COST,
927 to_number(null) PRJ_REVENUE,
928 to_number(null) PRJ_LABOR_REVENUE,
929 tmp1.PRJ_RAW_COST,
930 tmp1.PRJ_BRDN_COST,
931 to_number(null) PRJ_BILL_RAW_COST,
932 to_number(null) PRJ_BILL_BRDN_COST,
933 tmp1.PRJ_LABOR_RAW_COST,
934 tmp1.PRJ_LABOR_BRDN_COST,
935 to_number(null) PRJ_BILL_LABOR_RAW_COST,
936 to_number(null) PRJ_BILL_LABOR_BRDN_COST,
937 to_number(null) PRJ_REVENUE_WRITEOFF,
938 to_number(null) POU_REVENUE,
939 to_number(null) POU_LABOR_REVENUE,
940 tmp1.POU_RAW_COST,
941 tmp1.POU_BRDN_COST,
942 to_number(null) POU_BILL_RAW_COST,
943 to_number(null) POU_BILL_BRDN_COST,
944 tmp1.POU_LABOR_RAW_COST,
945 tmp1.POU_LABOR_BRDN_COST,
946 to_number(null) POU_BILL_LABOR_RAW_COST,
947 to_number(null) POU_BILL_LABOR_BRDN_COST,
948 to_number(null) POU_REVENUE_WRITEOFF,
949 to_number(null) EOU_REVENUE,
950 tmp1.EOU_RAW_COST,
951 tmp1.EOU_BRDN_COST,
952 to_number(null) EOU_BILL_RAW_COST,
953 to_number(null) EOU_BILL_BRDN_COST,
954 to_number(null) LABOR_HRS,
955 to_number(null) BILL_LABOR_HRS,
956 to_number(null) TOTAL_HRS_A,
957 to_number(null) BILL_HRS_A,
958 to_number(null) GG1_REVENUE,
959 to_number(null) GG1_LABOR_REVENUE,
960 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
961 GG1_RAW_COST,
962 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
963 GG1_BRDN_COST,
964 to_number(null) GG1_BILL_RAW_COST,
965 to_number(null) GG1_BILL_BRDN_COST,
966 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
967 GG1_LABOR_RAW_COST,
968 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
969 GG1_LABOR_BRDN_COST,
970 to_number(null) GG1_BILL_LABOR_RAW_COST,
971 to_number(null) GG1_BILL_LABOR_BRDN_COST,
972 to_number(null) GG1_REVENUE_WRITEOFF,
973 to_number(null) GP1_REVENUE,
974 to_number(null) GP1_LABOR_REVENUE,
975 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
976 GP1_RAW_COST,
977 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
978 GP1_BRDN_COST,
979 to_number(null) GP1_BILL_RAW_COST,
980 to_number(null) GP1_BILL_BRDN_COST,
981 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
982 GP1_LABOR_RAW_COST,
983 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
984 GP1_LABOR_BRDN_COST,
985 to_number(null) GP1_BILL_LABOR_RAW_COST,
986 to_number(null) GP1_BILL_LABOR_BRDN_COST,
987 to_number(null) GP1_REVENUE_WRITEOFF,
988 to_number(null) GG2_REVENUE,
989 to_number(null) GG2_LABOR_REVENUE,
990 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
991 GG2_RAW_COST,
992 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
993 GG2_BRDN_COST,
994 to_number(null) GG2_BILL_RAW_COST,
995 to_number(null) GG2_BILL_BRDN_COST,
996 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
997 GG2_LABOR_RAW_COST,
998 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
999 GG2_LABOR_BRDN_COST,
1000 to_number(null) GG2_BILL_LABOR_RAW_COST,
1001 to_number(null) GG2_BILL_LABOR_BRDN_COST,
1002 to_number(null) GG2_REVENUE_WRITEOFF,
1003 to_number(null) GP2_REVENUE,
1004 to_number(null) GP2_LABOR_REVENUE,
1005 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1006 GP2_RAW_COST,
1007 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1008 GP2_BRDN_COST,
1009 to_number(null) GP2_BILL_RAW_COST,
1010 to_number(null) GP2_BILL_BRDN_COST,
1011 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1012 GP2_LABOR_RAW_COST,
1013 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1014 GP2_LABOR_BRDN_COST,
1015 to_number(null) GP2_BILL_LABOR_RAW_COST,
1016 to_number(null) GP2_BILL_LABOR_BRDN_COST,
1017 to_number(null) GP2_REVENUE_WRITEOFF
1018 from
1019 (
1020 select /*+ no_merge(tmp1) */
1021 decode(prj_rt.RATE,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1022 ,decode(sign(prj_rt.RATE),-1,'Y',null)) DANGLING_RECVR_GL_RATE_FLAG,
1023 decode(prj_rt.RATE,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1024 ,decode(sign(prj_rt.RATE),-1,'Y',null)) DANGLING_RECVR_PA_RATE_FLAG,
1025 decode(l_g2_currency_code,
1026 'Y', decode(prj_rt.RATE2,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1027 ,decode(sign(prj_rt.RATE2),-1,'Y',null)),
1028 null) DANGLING_RECVR_GL_RATE2_FLAG,
1029 decode(l_g2_currency_code,
1030 'Y', decode(prj_rt.RATE2,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1031 ,decode(sign(prj_rt.RATE2),-1,'Y',null)),
1032 null) DANGLING_RECVR_PA_RATE2_FLAG,
1033 decode(sign(prj_info.EN_CALENDAR_MIN_DATE-tmp1.RECVR_GL_TIME_ID)+
1034 sign(tmp1.RECVR_GL_TIME_ID-prj_info.EN_CALENDAR_MAX_DATE),
1035 0,'Y',null) DANGLING_RECVR_EN_TIME_FLAG,
1036 decode(sign(prj_info.EN_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1037 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.EN_CALENDAR_MAX_DATE),
1038 0,'Y',null) DANGLING_EXP_EN_TIME_FLAG,
1039 decode(sign(prj_info.GL_CALENDAR_MIN_DATE-tmp1.RECVR_GL_TIME_ID)+
1040 sign(tmp1.RECVR_GL_TIME_ID-prj_info.GL_CALENDAR_MAX_DATE),
1041 0,'Y',null) DANGLING_RECVR_GL_TIME_FLAG,
1042 decode(sign(prj_info.GL_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1043 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.GL_CALENDAR_MAX_DATE),
1044 0,'Y',null) DANGLING_EXP_GL_TIME_FLAG,
1045 decode(sign(prj_info.PA_CALENDAR_MIN_DATE-tmp1.RECVR_PA_TIME_ID)+
1046 sign(tmp1.RECVR_PA_TIME_ID-prj_info.PA_CALENDAR_MAX_DATE),
1047 0,'Y',null) DANGLING_RECVR_PA_TIME_FLAG,
1048 decode(sign(prj_info.PA_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1049 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.PA_CALENDAR_MAX_DATE),
1050 0,'Y',null) DANGLING_EXP_PA_TIME_FLAG,
1051 'Y',
1052 tmp1.PROJECT_ID,
1053 tmp1.PROJECT_ORG_ID,
1054 tmp1.PROJECT_ORGANIZATION_ID,
1055 tmp1.PROJECT_TYPE_CLASS,
1056 tmp1.EXPENDITURE_ORGANIZATION_ID,
1057 tmp1.RECVR_GL_TIME_ID,
1058 tmp1.RECVR_PA_TIME_ID,
1059 tmp1.EXPENDITURE_ITEM_TIME_ID,
1060 tmp1.GL_PERIOD_NAME,
1061 tmp1.PA_PERIOD_NAME,
1062 prj_info.GL_CALENDAR_ID PJ_GL_CALENDAR_ID,
1063 prj_info.PA_CALENDAR_ID PJ_PA_CALENDAR_ID,
1064 prj_rt.RATE PRJ_PA_RATE,
1065 prj_rt.MAU MAU,
1066 prj_rt.RATE2 PRJ_PA_RATE2,
1067 prj_rt.MAU2 MAU2,
1068 tmp1.PRJ_RAW_COST,
1069 tmp1.PRJ_BRDN_COST,
1070 tmp1.PRJ_LABOR_RAW_COST,
1071 tmp1.PRJ_LABOR_BRDN_COST,
1072 tmp1.POU_RAW_COST,
1073 tmp1.POU_BRDN_COST,
1074 tmp1.POU_LABOR_RAW_COST,
1075 tmp1.POU_LABOR_BRDN_COST,
1076 tmp1.EOU_RAW_COST,
1077 tmp1.EOU_BRDN_COST,
1078 tmp1.DENOM_CURRENCY_CODE,
1079 tmp1.DENOM_RAW_COST,
1080 tmp1.DENOM_BURDENED_COST,
1081 tmp1.TASK_ID,
1082 tmp1.VENDOR_ID,
1083 tmp1.EXPENDITURE_TYPE,
1084 tmp1.EXPENDITURE_CATEGORY,
1085 tmp1.SYSTEM_LINKAGE_FUNCTION,
1086 tmp1.RESOURCE_CLASS_CODE,
1087 tmp1.LINE_TYPE,
1088 tmp1.INVENTORY_ITEM_ID,
1089 tmp1.BOM_LABOR_RESOURCE_ID,
1090 tmp1.BOM_EQUIPMENT_RESOURCE_ID
1091 from
1092 PJI_ORG_EXTR_INFO prj_info,
1093 (
1094 select /*+ parallel(tmp1) */
1095 tmp1.PROJECT_ID,
1096 proj.org_id PROJECT_ORG_ID,
1097 proj.carrying_out_organization_id PROJECT_ORGANIZATION_ID,
1098 DECODE(projtyp.PROJECT_TYPE_CLASS_CODE,
1099 'CAPITAL', 'C',
1100 'CONTRACT', 'B',
1101 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
1102 tmp1.ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,
1103 decode(l_params_cost_flag,'N',-1,
1104 to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J')))
1105 RECVR_GL_TIME_ID,
1106 decode(l_params_cost_flag,'N',-1,
1107 to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J')))
1108 RECVR_PA_TIME_ID,
1109 to_number(to_char(tmp1.EXPENDITURE_ITEM_DATE,'J')) EXPENDITURE_ITEM_TIME_ID,
1110 null GL_PERIOD_NAME,
1111 null PA_PERIOD_NAME,
1112 sum(tmp1.PROJ_RAW_COST) PRJ_RAW_COST,
1113 sum(tmp1.PROJ_BURDENED_COST) PRJ_BRDN_COST,
1114 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1115 tmp1.SYSTEM_LINKAGE_FUNCTION),
1116 'ST', tmp1.PROJ_RAW_COST,
1117 'OT', tmp1.PROJ_RAW_COST, 0)) PRJ_LABOR_RAW_COST,
1118 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1119 tmp1.SYSTEM_LINKAGE_FUNCTION),
1120 'ST', tmp1.PROJ_BURDENED_COST,
1121 'OT', tmp1.PROJ_BURDENED_COST,
1122 0)) PRJ_LABOR_BRDN_COST,
1123 sum(tmp1.TOT_CMT_RAW_COST) POU_RAW_COST,
1124 sum(tmp1.TOT_CMT_BURDENED_COST) POU_BRDN_COST,
1125 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1126 tmp1.SYSTEM_LINKAGE_FUNCTION),
1127 'ST', tmp1.TOT_CMT_RAW_COST,
1128 'OT', tmp1.TOT_CMT_RAW_COST,
1129 0)) POU_LABOR_RAW_COST,
1130 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1131 tmp1.SYSTEM_LINKAGE_FUNCTION),
1132 'ST', tmp1.TOT_CMT_BURDENED_COST,
1133 'OT', tmp1.TOT_CMT_BURDENED_COST,
1134 0)) POU_LABOR_BRDN_COST,
1135 sum(tmp1.ACCT_RAW_COST) EOU_RAW_COST,
1136 sum(tmp1.ACCT_BURDENED_COST) EOU_BRDN_COST,
1137 tmp1.DENOM_CURRENCY_CODE,
1138 sum(tmp1.DENOM_RAW_COST) DENOM_RAW_COST,
1139 sum(tmp1.DENOM_BURDENED_COST) DENOM_BURDENED_COST,
1140 tmp1.TASK_ID,
1141 tmp1.VENDOR_ID,
1142 tmp1.EXPENDITURE_TYPE,
1143 tmp1.EXPENDITURE_CATEGORY,
1144 NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1145 tmp1.SYSTEM_LINKAGE_FUNCTION) SYSTEM_LINKAGE_FUNCTION, --Bug 3964738
1146 tmp1.RESOURCE_CLASS RESOURCE_CLASS_CODE,
1147 tmp1.LINE_TYPE,
1148 tmp1.INVENTORY_ITEM_ID,
1149 tmp1.BOM_LABOR_RESOURCE_ID,
1150 tmp1.BOM_EQUIPMENT_RESOURCE_ID
1151 from
1152 PA_COMMITMENT_TXNS tmp1,
1153 PA_PROJECTS_ALL proj,
1154 PA_PROJECT_TYPES_ALL projtyp
1155 where
1156 tmp1.project_id = proj.project_id and
1157 proj.project_type = projtyp.project_type and
1158 proj.org_id = projtyp.org_id /*5377131*/
1159 group by
1160 tmp1.PROJECT_ID,
1161 proj.org_id,
1162 proj.carrying_out_organization_id,
1163 projtyp.PROJECT_TYPE_CLASS_CODE,
1164 tmp1.ORGANIZATION_ID,
1165 decode(l_params_cost_flag,'N',-1,to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J'))),
1166 decode(l_params_cost_flag,'N',-1,to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J'))),
1167 to_number(to_char(tmp1.EXPENDITURE_ITEM_DATE,'J')),
1168 tmp1.GL_PERIOD,
1169 tmp1.PA_PERIOD,
1170 tmp1.DENOM_CURRENCY_CODE,
1171 tmp1.TASK_ID,
1172 tmp1.VENDOR_ID,
1173 tmp1.EXPENDITURE_TYPE,
1174 tmp1.EXPENDITURE_CATEGORY,
1175 NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1176 tmp1.SYSTEM_LINKAGE_FUNCTION), --Bug 3964738
1177 tmp1.RESOURCE_CLASS,
1178 tmp1.LINE_TYPE,
1179 tmp1.INVENTORY_ITEM_ID,
1180 tmp1.BOM_LABOR_RESOURCE_ID,
1181 tmp1.BOM_EQUIPMENT_RESOURCE_ID
1182 ) tmp1,
1183 PJI_FM_AGGR_DLY_RATES prj_rt
1184 where
1185 tmp1.PROJECT_ORG_ID = prj_info.ORG_ID and
1186 prj_rt.WORKER_ID = -1 and
1187 --tmp1.RECVR_PA_TIME_ID = prj_rt.TIME_ID and --Bug 6894858
1188 to_number(to_char(trunc(sysdate), 'J')) = prj_rt.TIME_ID and
1189 prj_info.PF_CURRENCY_CODE = prj_rt.PF_CURRENCY_CODE
1190 ) tmp1,
1191 PA_EXPENDITURE_TYPES et
1192 where
1193 tmp1.EXPENDITURE_TYPE = et.EXPENDITURE_TYPE;
1194
1195 pji_utils.write2log('Leaving FIN_CMT_SUMMARY');
1196
1197 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1198 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);');
1199
1200 commit;
1201
1202 end FIN_CMT_SUMMARY;
1203
1204 end PJI_FM_CMT_EXTR;