[Home] [Help]
PACKAGE BODY: APPS.PJI_FM_CMT_EXTR
Source
1 package body PJI_FM_CMT_EXTR as
2 /* $Header: PJISF14B.pls 120.13.12020000.7 2013/05/20 07:35:26 krkondur ship $ */
3
4 -- Procedure to accumulate the commitment txns.
5 -- This procedure is mainly used in PPR for customers using
6 -- both PSI and PPR. There exists an equivalent apis in
7 -- PA_TXN_ACCUMS package so this api is made as private.
8
9 /* Commented procedure for bug 13889383
10 PROCEDURE ACCUM_PROJPERF_COMMIT_TXNS
11 ( x_project_id IN NUMBER,
12 x_err_stage IN OUT NOCOPY VARCHAR2,
13 x_err_code IN OUT NOCOPY NUMBER)
14 IS
15 CURSOR selcmts IS
16 SELECT
17 pct.cmt_line_id,
18 pct.project_id,
19 pct.task_id,
20 LAST_DAY(pct.expenditure_item_date) month_ending_date,
21 pct.pa_period,
22 pct.gl_period,
23 pct.organization_id,
24 pct.vendor_id,
25 pct.expenditure_type,
26 pct.expenditure_category,
27 pct.revenue_category,
28 pct.system_linkage_function,
29 pct.cmt_ind_compiled_set_id,
30 pct.expenditure_item_date,
31 pct.denom_currency_code,
32 pct.denom_raw_cost,
33 pct.denom_burdened_cost,
34 pct.acct_currency_code,
35 pct.acct_rate_date,
36 pct.acct_rate_type,
37 pct.acct_exchange_rate,
38 pct.acct_raw_cost,
39 pct.acct_burdened_cost,
40 pct.receipt_currency_code,
41 pct.receipt_currency_amount,
42 pct.receipt_exchange_rate
43 FROM
44 pa_commitment_txns pct
45 WHERE
46 pct.project_id = x_project_id;
47
48 --
49 -- Local Variables -------------------------------------------
50 --
51 cmtrec selcmts%ROWTYPE;
52 x_txn_accum_id NUMBER;
53 row_processed NUMBER;
54 l_count NUMBER;
55 is_row_found NUMBER;
56
57 x_task_Id NUMBER;
58 x_week_ending_date DATE;
59 x_month_ending_date DATE;
60 x_person_id NUMBER;
61 x_job_id NUMBER;
62 x_vendor_id NUMBER;
63 x_expenditure_type VARCHAR2(30);
64 x_organization_id NUMBER;
65 x_non_labor_resource VARCHAR2(30);
66 x_non_labor_resource_org_id NUMBER;
67 x_expenditure_category VARCHAR2(30);
68 x_revenue_category VARCHAR2(30);
69 x_event_type VARCHAR2(30);
70 x_event_type_classification VARCHAR2(30);
71 x_system_linkage_function VARCHAR2(30);
72 x_line_type VARCHAR2(1);
73 x_cost_ind_compiled_set_id NUMBER;
74 x_rev_ind_compiled_set_id NUMBER;
75 x_inv_ind_compiled_set_id NUMBER;
76 x_cmt_ind_compiled_set_id NUMBER;
77
78 x_pa_period varchar2(20); -- Added for commitment change request
79 x_gl_period varchar2(15); -- Added for commitment change request
80
81 X_week_ending DATE;
82 X_week_ending_day VARCHAR2(80);
83 X_week_ending_day_index number;
84
85 BEGIN
86
87 SELECT COUNT(*) INTO l_count FROM pa_commitment_txns WHERE project_id = x_project_id;
88
89 select
90 per.PERIOD_NAME,
91 per.GL_PERIOD_NAME
92 into
93 x_pa_period,
94 x_gl_period
95 from
96 PA_PROJECTS_ALL prj,
97 PA_PERIODS_ALL per
98 where
99 prj.PROJECT_ID = x_project_id and
100 nvl(per.ORG_ID, -1) = nvl(prj.ORG_ID, -1) and
101 per.CURRENT_PA_PERIOD_FLAG = 'Y';
102
103 x_txn_accum_id :=0;
104 x_err_code :=0;
105
106 pji_utils.write2log('Enering accum_projperf_commit_txns');
107 x_err_code :=0;
108 x_err_stage := 'Accumulating Commitment Txns';
109
110 FOR cmtrec IN selcmts LOOP
111 is_row_found := 0;
112 row_processed := row_processed + 1;
113
114 BEGIN
115 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
116 into X_week_ending_day_index
117 FROM pa_implementations_all
118 WHERE org_id = (select org_id from pa_projects_all where project_id = x_project_id);
119
120 select to_char(to_date('01-01-1950','DD-MM-YYYY') +X_week_ending_day_index-1, 'Day')
121 into X_week_ending_day from dual;
122
123 SELECT
124 next_day( trunc( cmtrec.expenditure_item_date )-1, X_week_ending_day )
125 INTO
126 x_week_ending_date
127 FROM
128 sys.dual;
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 RAISE;
132 END;
133
134 x_task_Id := cmtrec.task_id;
135 --x_week_ending_date := cmtrec.week_ending_date;
136 x_month_ending_date := cmtrec.month_ending_date;
137 x_person_id := NULL;
138 x_job_id := NULL;
139 x_vendor_id := cmtrec.vendor_id;
140 x_expenditure_type := cmtrec.expenditure_type;
141 x_organization_id := cmtrec.organization_id;
142 x_non_labor_resource := NULL;
143 x_non_labor_resource_org_id := NULL;
144 x_expenditure_category := cmtrec.expenditure_category;
145 x_revenue_category := cmtrec.revenue_category;
146 x_event_type := NULL;
147 x_event_type_classification := NULL;
148 x_system_linkage_function := cmtrec.system_linkage_function;
149 x_line_type := 'M';
150 x_cost_ind_compiled_set_id := NULL;
151 x_rev_ind_compiled_set_id := NULL;
152 x_inv_ind_compiled_set_id := NULL;
153 x_cmt_ind_compiled_set_id := cmtrec.cmt_ind_compiled_set_id;
154
155 <<get_txn_accum_id>>
156 BEGIN
157 -- Seperating Expenditure Items/Events Processing
158 IF ( x_expenditure_type IS NOT NULL ) THEN
159 -- Seperating processing where person_id is null/not null
160 -- to take advantage of index on person_id
161 IF ( x_person_id IS NOT NULL ) THEN
162 -- person_id is not null
163 SELECT /*+ index(pta PA_TXN_ACCUM_N2) txn_accum_id
164 INTO x_txn_accum_id
165 FROM pa_txn_accum pta
166 WHERE x_project_id = pta.project_id
167 AND x_task_Id = pta.task_id
168 AND x_pa_period = pta.pa_period
169 AND x_gl_period = pta.gl_period
170 AND x_week_ending_date = pta.week_ending_date
171 AND x_month_ending_date = pta.month_ending_date
172 AND x_expenditure_type = pta.expenditure_type
173 AND x_organization_id = pta.organization_id
174 AND x_person_id = pta.person_id
175 AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
176 AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
177 AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
178 AND NVL(x_non_labor_resource_org_id,-1)
179 = NVL(pta.non_labor_resource_org_id,-1)
180 AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
181 AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
182 AND NVL(x_system_linkage_function,'X')
183 = NVL(pta.system_linkage_function,'X')
184 AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
185 = NVL(pta.cost_ind_compiled_set_id,-1)
186 AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
187 = NVL(pta.rev_ind_compiled_set_id,-1)
188 AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
189 = NVL(pta.inv_ind_compiled_set_id,-1)
190 AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
191 = NVL(pta.cmt_ind_compiled_set_id,-1);
192
193 is_row_found := 1;
194
195 ELSE
196 BEGIN
197 -- When person_id is not available
198 SELECT txn_accum_id
199 INTO x_txn_accum_id
200 FROM pa_txn_accum pta
201 WHERE x_project_id = pta.project_id
202 AND x_task_Id = pta.task_id
203 AND x_pa_period = pta.pa_period
204 AND x_gl_period = pta.gl_period
205 AND x_week_ending_date = pta.week_ending_date
206 AND x_month_ending_date = pta.month_ending_date
207 AND x_expenditure_type = pta.expenditure_type
208 AND x_organization_id = pta.organization_id
209 AND pta.person_id IS NULL
210 AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
211 AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
212 AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
213 AND NVL(x_non_labor_resource_org_id,-1)
214 = NVL(pta.non_labor_resource_org_id,-1)
215 AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
216 AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
217 AND NVL(x_system_linkage_function,'X')
218 = NVL(pta.system_linkage_function,'X')
219 AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
220 = NVL(pta.cost_ind_compiled_set_id,-1)
221 AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
222 = NVL(pta.rev_ind_compiled_set_id,-1)
223 AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
224 = NVL(pta.inv_ind_compiled_set_id,-1)
225 AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
226 = NVL(pta.cmt_ind_compiled_set_id,-1);
227
228 is_row_found := 1;
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 -- For burden lines
233 SELECT txn_accum_id
234 INTO x_txn_accum_id
235 FROM pa_txn_accum pta
236 WHERE x_project_id = pta.project_id
237 AND x_task_Id = pta.task_id
238 AND x_pa_period = pta.pa_period
239 AND x_gl_period = pta.gl_period
240 AND x_expenditure_type = pta.expenditure_type
241 AND x_organization_id = pta.organization_id
242 AND pta.person_id IS NULL
243 AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
244 AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
245 AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
246 AND NVL(x_non_labor_resource_org_id,-1)
247 = NVL(pta.non_labor_resource_org_id,-1)
248 AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
249 AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
250 AND NVL(x_system_linkage_function,'X')
251 = NVL(pta.system_linkage_function,'X')
252 AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
253 = NVL(pta.cost_ind_compiled_set_id,-1)
254 AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
255 = NVL(pta.rev_ind_compiled_set_id,-1)
256 AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
257 = NVL(pta.inv_ind_compiled_set_id,-1)
258 AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
259 = NVL(pta.cmt_ind_compiled_set_id,-1);
260 is_row_found := 1;
261 END;
262
263 END IF; -- IF ( x_person_id IS NOT NULL )
264
265 ELSE
266 -- Process Event Here
267 SELECT txn_accum_id
268 INTO x_txn_accum_id
269 FROM pa_txn_accum pta
270 WHERE x_project_id = pta.project_id
271 AND x_task_Id = pta.task_id
272 AND x_pa_period = pta.pa_period
273 AND x_gl_period = pta.gl_period
274 AND x_week_ending_date = pta.week_ending_date
275 AND x_month_ending_date = pta.month_ending_date
276 AND x_event_type = pta.event_type
277 AND x_event_type_classification = pta.event_type_classification
278 AND x_organization_id = pta.organization_id
279 AND x_revenue_category = pta.revenue_category;
280
281 is_row_found := 1;
282 END IF; -- IF ( x_expenditure_type IS NOT NULL )
283 EXCEPTION
284 WHEN NO_DATA_FOUND THEN
285 is_row_found := 0;
286 WHEN OTHERS THEN
287 x_err_code := SQLCODE;
288 RAISE;
289 END;
290
291 IF ( is_row_found > 0 ) THEN
292 -- Create a row for drilldown in pa_txn_accume_details Now
293 Pa_Txn_Accums.create_txn_accum_details(
294 x_txn_accum_id,
295 'M', -- pa_commitments_txns
296 NULL, -- expenditure_item_id
297 NULL, -- line_num
298 NULL, -- event_num
299 cmtrec.cmt_line_id,
300 cmtrec.project_id,
301 cmtrec.task_id,
302 x_err_stage,
303 x_err_code);
304 END IF;
305
306 END LOOP;
307
308 pji_utils.write2log('Leaving accum_projperf_commit_txns');
309 EXCEPTION
310 WHEN OTHERS THEN
311 x_err_code := SQLCODE;
312 pji_utils.write2log('within exception block of accum_projperf_commit_txns');
313 RAISE;
314
315 END ACCUM_PROJPERF_COMMIT_TXNS;
316 */
317
318 PROCEDURE accum_projperf_commitments
319 ( x_project_id IN NUMBER,
320 x_err_stage IN OUT NOCOPY VARCHAR2,
321 x_err_code IN OUT NOCOPY NUMBER)
322 IS
323
324 CURSOR selcmts IS
325 SELECT
326 pct.rowid,
327 pct.cmt_line_id,
328 pct.project_id,
329 pct.task_id,
330 pa_utils.GetWeekEnding(pct.expenditure_item_date) week_ending_date,
331 LAST_DAY(pct.expenditure_item_date) month_ending_date,
332 pct.pa_period,
333 pct.gl_period,
334 pct.organization_id,
335 pct.vendor_id,
336 pct.expenditure_type,
337 pct.expenditure_category,
338 pct.revenue_category,
339 pct.system_linkage_function,
340 pct.cmt_ind_compiled_set_id,
341 pct.expenditure_item_date,
342 pct.denom_currency_code,
343 pct.denom_raw_cost,
344 pct.denom_burdened_cost,
345 pct.acct_currency_code,
346 pct.acct_rate_date,
347 pct.acct_rate_type,
348 pct.acct_exchange_rate,
349 pct.acct_raw_cost,
350 pct.acct_burdened_cost,
351 pct.receipt_currency_code,
352 pct.receipt_currency_amount,
353 pct.receipt_exchange_rate
354 FROM
355 pa_commitment_txns pct
356 WHERE
357 pct.project_id = x_project_id ;
358
359
360 CURSOR l_project_curr_code_csr
361 (l_project_id pa_projects_all.project_id%TYPE)
362 IS
363 SELECT project_currency_code,projfunc_currency_code
364 FROM pa_projects_all p
365 WHERE p.project_id = l_project_id;
366
367
368 l_proj_curr_OK VARCHAR2(1) := 'Y';
369 l_project_curr_code pa_projects_all.project_currency_code%TYPE := NULL;
370
371
372 cmtrec selcmts%ROWTYPE;
373 row_processed NUMBER;
374 l_cmtrec_curr_OK VARCHAR2(1);
375 l_cmt_rejection_code pa_commitment_txns.cmt_rejection_code%TYPE;
376 l_err_msg VARCHAR2(2000);
377
378 l_Project_Rate_Type pa_commitment_txns.project_rate_type%TYPE;
379 l_Project_Rate_Date DATE;
380 l_project_exch_rate NUMBER;
381 l_PROJECT_RAW_COST NUMBER := NULL;
382 l_PROJECT_BURDENED_COST NUMBER := NULL; -- added for FP.M
383
384 l_amount_out NUMBER;
385 l_tot_cmt_raw_cost NUMBER;
386 l_tot_cmt_burdened_cost NUMBER;
387 l_status VARCHAR2(200) := NULL;
388 l_stage NUMBER := NULL;
389
390 l_SYSTEM_LINKAGE pa_expenditure_items_all.SYSTEM_LINKAGE_FUNCTION%TYPE :=NULL;
391
392
393 l_PROJFUNC_CURR_CODE pa_projects_all.project_currency_code%TYPE := NULL;
394 l_PROJFUNC_COST_RATE_TYPE pa_commitment_txns.project_rate_type%TYPE := NULL;
395 l_PROJFUNC_COST_RATE_DATE DATE := NULL;
396 l_PROJFUNC_COST_EXCH_RATE NUMBER := NULL;
397
398 BEGIN
399 pji_utils.write2log('Enering accum_projperf_commitments');
400 x_err_code :=0;
401 x_err_stage := 'Accumulating Commitments';
402
403 row_processed :=0;
404
405 OPEN l_project_curr_code_csr(x_project_id);
406 FETCH l_project_curr_code_csr INTO l_project_curr_code,l_projfunc_curr_code;
407 CLOSE l_project_curr_code_csr;
408
409 FOR cmtrec IN selcmts LOOP
410
411 row_processed := row_processed + 1;
412
413 l_cmtrec_curr_OK := 'Y';
414 l_project_Rate_Type := NULL;
415 l_project_Rate_Date := cmtrec.expenditure_item_date;
416 l_project_exch_rate := NULL;
417 l_project_raw_cost := NULL;
418 l_project_burdened_cost := NULL;
419
420 l_amount_out := NULL;
421 l_tot_cmt_raw_cost := NULL;
422 l_tot_cmt_burdened_cost := NULL;
423 l_status := NULL;
424 l_stage := NULL;
425 l_cmt_rejection_code := NULL;
426 l_err_msg := NULL;
427
428 IF (l_cmtrec_curr_OK = 'Y')
429 THEN
430
431 pa_multi_currency_txn.get_currency_amounts
432 (p_project_curr_code => l_project_curr_code
433 , p_ei_date => cmtrec.expenditure_item_date
434 , p_task_id => cmtrec.task_id
435 , p_denom_raw_cost => cmtrec.denom_raw_cost
436 , p_denom_curr_code => cmtrec.denom_currency_code
437 , p_acct_curr_code => cmtrec.acct_currency_code
438 , p_accounted_flag => 'Y'
439 , p_acct_rate_date => cmtrec.acct_rate_date
440 , p_acct_rate_type => cmtrec.acct_rate_type
441 , p_acct_exch_rate => cmtrec.acct_exchange_rate
442 , p_acct_raw_cost => cmtrec.acct_raw_cost
443 , p_project_rate_type => l_project_rate_type
444 , p_project_rate_date => l_project_rate_date
445 , p_project_exch_rate => l_project_exch_rate
446 , P_PROJFUNC_RAW_COST => l_amount_out
447 , p_status => l_status
448 , p_stage => l_stage
449 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
450 , P_PROJECT_RAW_COST => l_PROJECT_RAW_COST
451 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
452 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
453 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
454 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
455 );
456
457
458 l_tot_cmt_raw_cost := l_amount_out;
459
460 IF (l_status IS NOT NULL) THEN
461 l_cmt_rejection_code := l_status;
462 l_cmtrec_curr_OK := 'N';
463 END IF;
464
465 IF (l_cmtrec_curr_OK = 'Y') THEN
466
467 IF cmtrec.denom_raw_cost <> cmtrec.denom_burdened_cost THEN
468 pa_multi_currency_txn.get_currency_amounts
469 (p_project_curr_code => l_project_curr_code
470 , p_ei_date => cmtrec.expenditure_item_date
471 , p_task_id => cmtrec.task_id
472 , p_denom_raw_cost => cmtrec.denom_burdened_cost
473 , p_denom_curr_code => cmtrec.denom_currency_code
474 , p_acct_curr_code => cmtrec.acct_currency_code
475 , p_accounted_flag => 'Y'
476 , p_acct_rate_date => cmtrec.acct_rate_date
477 , p_acct_rate_type => cmtrec.acct_rate_type
478 , p_acct_exch_rate => cmtrec.acct_exchange_rate
479 , p_acct_raw_cost => cmtrec.acct_burdened_cost
480 , p_project_rate_type => l_project_rate_type
481 , p_project_rate_date => l_project_rate_date
482 , p_project_exch_rate => l_project_exch_rate
483 , P_PROJFUNC_RAW_COST => l_amount_out
484 , p_status => l_status
485 , p_stage => l_stage
486 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
487 , P_PROJECT_RAW_COST => l_PROJECT_BURDENED_COST /*Bug#4137193*/
488 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
489 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
490 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
491 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
492 );
493 END IF;
494
495 l_tot_cmt_burdened_cost := l_amount_out;
496
497 /* placed the below statement within IF condition
498 for the bug 4137193 */
499
500 IF l_PROJECT_BURDENED_COST IS null THEN
501 l_PROJECT_BURDENED_COST := l_PROJECT_RAW_COST;
502 END IF;
503
504 IF (l_status IS NOT NULL) THEN
505 l_cmt_rejection_code := l_status;
506 l_cmtrec_curr_OK := 'N';
507 END IF;
508
509 END IF; --BURDENED COST
510
511 END IF; -- DERIVATION SUBsection
512
513 IF (l_cmtrec_curr_OK = 'Y') THEN
514 UPDATE pa_commitment_txns
515 SET tot_cmt_raw_cost = l_tot_cmt_raw_cost
516 , tot_cmt_burdened_cost = l_tot_cmt_burdened_cost
517 , project_currency_code = l_project_curr_code
518 , project_rate_date = l_project_rate_date
519 , project_rate_type = l_project_rate_type
520 , project_exchange_rate = l_project_exch_rate
521 , proj_raw_cost = l_PROJECT_RAW_COST
522 , proj_burdened_cost = l_PROJECT_BURDENED_COST
523 WHERE rowid = cmtrec.rowid;
524 ELSE
525 UPDATE pa_commitment_txns
526 SET generation_error_flag = 'Y'
527 , cmt_rejection_code = l_cmt_rejection_code
528 WHERE rowid = cmtrec.rowid;
529
530 l_proj_curr_OK := 'N';
531 END IF; -- UPDATE COMMITMENT ROW
532
533 END LOOP; -- CMTREC Processing
534
535 pji_utils.write2log('Leaving accum_projperf_commitments');
536 EXCEPTION
537 WHEN OTHERS THEN
538 x_err_code := SQLCODE;
539 pji_utils.write2log('within exception block of accum_projperf_commitments');
540 RAISE;
541
542 END accum_projperf_commitments;
543
544
545 -- -----------------------------------------------------
546 -- procedure REFRESH_PROJPERF_CMT_PRE
547 -- -----------------------------------------------------
548 procedure REFRESH_PROJPERF_CMT_PRE (p_worker_id in number) is
549
550 l_extract_commitments varchar2(30);
551 l_process varchar2(30);
552
553 l_operating_unit number := null;
554 l_from_project varchar2(50) := null;
555 l_to_project varchar2(50) := null;
556 l_batch_size number := 1;
557 l_psi_ppr_flag varchar2(1); -- bug 13889383
558
559 begin
560
561 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
562
563 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
564 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);')) then
565 return;
566 end if;
567
568 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
569 (PJI_FM_SUM_MAIN.g_process,
570 'EXTRACT_COMMITMENTS');
571
572 select psi_ppr_flag into l_psi_ppr_flag from pji_system_settings; -- Added for bug 13889383
573
574 if (l_extract_commitments = 'N' or (l_extract_commitments = 'Y' and l_psi_ppr_flag = 'Y')) then -- Changes for bug 13889383
575 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
576 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);');
577 commit;
578 return;
579 end if;
580
581 l_operating_unit := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
582 (PJI_FM_SUM_MAIN.g_process, 'PROJECT_OPERATING_UNIT');
583
584 l_from_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
585 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT');
586
587 l_to_project := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
588 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT');
589
590 if (l_from_project is null) then
591
592 begin
593
594 select
595 prj.SEGMENT1
596 into
597 l_from_project
598 from
599 PA_PROJECTS_ALL prj
600 where
601 prj.PROJECT_ID = PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
602 (PJI_FM_SUM_MAIN.g_process, 'FROM_PROJECT_ID');
603
604 exception when no_data_found then null;
605
606 end;
607
608 end if;
609
610 if (l_to_project is null) then
611
612 begin
613
614 select
615 prj.SEGMENT1
616 into
617 l_to_project
618 from
619 PA_PROJECTS_ALL prj
620 where
621 prj.PROJECT_ID = PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
622 (PJI_FM_SUM_MAIN.g_process, 'TO_PROJECT_ID');
623
624 exception when no_data_found then null;
625
626 end;
627
628 end if;
629
630 insert into PJI_FM_EXTR_DREVN -- overload of draft revenues table for cmt
631 (
632 WORKER_ID,
633 ROW_ID,
634 LINE_SOURCE_TYPE,
635 PROJECT_ID,
636 PA_PERIOD_NAME,
637 GL_PERIOD_NAME,
638 BATCH_ID
639 )
640 select
641 -1 WORKER_ID, -- not used
642 cmt.ROW_ID ROW_ID, -- not used
643 'X' LINE_SOURCE_TYPE, -- not used
644 cmt.PROJECT_ID PROJECT_ID,
645 cmt.PA_PERIOD PA_PERIOD_NAME,
646 cmt.GL_PERIOD GL_PERIOD_NAME,
647 ceil(ROWNUM / l_batch_size) BATCH_ID
648 from
649 (
650 select /*+ ordered */
651 prj.PROJECT_ID,
652 prj.ROW_ID,
653 per.PA_PERIOD,
654 per.GL_PERIOD
655 from
656 (
657 select /*+ index(prj, PA_PROJECTS_U1) */
658 prj.PROJECT_ID,
659 prj.ROWID ROW_ID,
660 prj.ORG_ID ORG_ID, /*5377131*/
661 prj.PROJECT_STATUS_CODE
662 from
663 PA_PROJECTS_ALL prj
664 where
665 prj.ORG_ID = nvl(l_operating_unit,
666 prj.ORG_ID) and /*5377131*/
667 prj.SEGMENT1 between nvl(l_from_project, prj.SEGMENT1) and
668 nvl(l_to_project, prj.SEGMENT1) and
669 prj.TEMPLATE_FLAG = 'N'
670 ) prj,
671 (
672 select
673 PROJECT_STATUS_CODE
674 from
675 (
676 select /*+ index_ffs(prj, PA_PROJECTS_N4)
677 parallel_index(prj, PA_PROJECTS_N4) */
678 distinct
679 prj.PROJECT_STATUS_CODE
680 from
681 PA_PROJECTS_ALL prj
682 )
683 where
684 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
685 (PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
686 ) psc,
687 (
688 select /*+ index(per, PA_PERIODS_N3) */
689 nvl(per.ORG_ID, -1) ORG_ID,
690 per.PERIOD_NAME PA_PERIOD,
691 per.GL_PERIOD_NAME GL_PERIOD
692 from
693 PA_PERIODS_ALL per
694 where
695 -- per.CURRENT_PA_PERIOD_FLAG = 'Y' Bug fix 7602463
696 trunc(sysdate) between per.start_date and per.end_date
697 ) per
698 where
699 prj.PROJECT_STATUS_CODE = psc.PROJECT_STATUS_CODE and
700 prj.ORG_ID = per.ORG_ID
701 ) cmt
702 where
703 1 = 1
704 -- The below API only checks for those projects that already
705 -- have rows in PA_COMMITMENTS_TXNS.
706 -- PA_CHECK_COMMITMENTS.COMMITMENTS_CHANGED(cmt.PROJECT_ID) = 'Y'
707 order by
708 cmt.PROJECT_ID;
709
710 insert into PJI_HELPER_BATCH_MAP
711 (
712 BATCH_ID,
713 WORKER_ID,
714 STATUS
715 )
716 select
717 distinct
718 BATCH_ID,
719 null,
720 null
721 from
722 PJI_FM_EXTR_DREVN; -- overload of draft revenues table for commitments
723
724 delete
725 from PA_COMMITMENT_TXNS
726 where PROJECT_ID in
727 (
728 select
729 PROJECT_ID
730 from
731 PJI_FM_EXTR_DREVN -- overload of draft revenues table for cmt
732 );
733
734 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
735 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_PRE(p_worker_id);');
736
737 commit;
738
739 end REFRESH_PROJPERF_CMT_PRE;
740
741
742 -- -----------------------------------------------------
743 -- procedure REFRESH_PROJPERF_CMT
744 -- -----------------------------------------------------
745 procedure REFRESH_PROJPERF_CMT (p_worker_id in number) is
746
747 l_extract_commitments varchar2(30);
748 l_process varchar2(30);
749
750 l_leftover_batches number;
751 l_helper_batch_id number;
752 l_row_count number;
753 l_parallel_processes number;
754
755 x_run_id number;
756 x_status number;
757 x_stage number;
758 x_err_stage varchar2(120);
759 x_err_code number;
760
761 l_project_id number;
762 l_org_id number; -- bug 6847113
763 l_psi_ppr_flag varchar2(1); -- bug 13889383
764
765 begin
766
767 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
768
769 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
770 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);')) then
771 return;
772 end if;
773
774 l_parallel_processes := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
775 (PJI_FM_SUM_MAIN.g_process, 'PARALLEL_PROCESSES');
776
777 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
778 (PJI_FM_SUM_MAIN.g_process,
779 'EXTRACT_COMMITMENTS');
780
781 select psi_ppr_flag into l_psi_ppr_flag from pji_system_settings; -- Added for bug 13889383
782
783 if (l_extract_commitments = 'N' or (l_extract_commitments = 'Y' and l_psi_ppr_flag = 'Y')) then -- Changes for bug 13889383
784
785 for x in 2 .. l_parallel_processes loop
786
787 update PJI_SYSTEM_PRC_STATUS
788 set STEP_STATUS = 'C'
789 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
790 STEP_NAME =
791 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);' and
792 START_DATE is null;
793
794 commit;
795
796 end loop;
797
798 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION
799 (l_process, 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);');
800
801 commit;
802
803 return;
804
805 end if;
806
807 select count(*)
808 into l_leftover_batches
809 from PJI_HELPER_BATCH_MAP
810 where WORKER_ID = p_worker_id and
811 STATUS = 'P';
812
813 l_helper_batch_id := 0;
814
815 while l_helper_batch_id >= 0 loop
816
817 if (l_leftover_batches > 0) then
818
819 l_leftover_batches := l_leftover_batches - 1;
820
821 select BATCH_ID
822 into l_helper_batch_id
823 from PJI_HELPER_BATCH_MAP
824 where WORKER_ID = p_worker_id and
825 STATUS = 'P' and
826 ROWNUM = 1;
827
828 else
829
830 update PJI_HELPER_BATCH_MAP
831 set WORKER_ID = p_worker_id,
832 STATUS = 'P'
833 where WORKER_ID is null and
834 ROWNUM = 1
835 returning BATCH_ID
836 into l_helper_batch_id;
837
838 end if;
839
840 if (sql%rowcount <> 0) then
841
842 commit;
843 -- bug 6847113
844 select org_id into l_org_id
845 from pa_projects_all
846 where project_id =
847 ( select project_id from PJI_FM_EXTR_DREVN
848 where BATCH_ID = l_helper_batch_id
849 and rownum=1);
850 PA_CURRENCY.G_org_id := l_org_id;
851
852 -- Bug 6847113
853
854
855 PA_TXN_ACCUMS.CREATE_CMT_TXNS(null,
856 l_helper_batch_id, -- overload of to
857 null, -- project parameter
858 x_err_stage,
859 x_err_code);
860
861 for c in (select PROJECT_ID
862 from PJI_FM_EXTR_DREVN -- overload of drev table for cmt
863 where BATCH_ID = l_helper_batch_id) loop
864
865 begin
866
867 -- Create summarized burden commitment transactions
868
869 PA_BURDEN_COSTING.CREATE_BURDEN_CMT_TRANSACTION
870 (x_project_id => c.PROJECT_ID,
871 status => x_status,
872 stage => x_stage ,
873 x_run_id => x_run_id);
874
875 /* Commented for bug 13889383 */
876 /* IF Pa_Check_Commitments.commitments_changed(c.project_id) = 'Y' THEN -- Added for bug 13420421
877 -- Added for bug 10399153 starts
878 UPDATE pa_txn_accum pta
879 SET
880 pta.tot_cmt_raw_cost = NULL,
881 pta.tot_cmt_burdened_cost = NULL,
882 pta.cmt_rollup_flag = 'N',
883 pta.last_update_date = SYSDATE,
884 pta.program_update_date = SYSDATE
885 WHERE
886 pta.project_id = c.PROJECT_ID
887 AND EXISTS
888 ( SELECT 'Yes'
889 FROM pa_txn_accum_details ptad
890 WHERE pta.txn_accum_id = ptad.txn_accum_id
891 AND ptad.line_type = 'M'
892 );
893
894 update pa_project_accum_commitments
895 SET cmt_raw_cost_itd = NULL,
896 cmt_raw_cost_ytd = NULL,
897 cmt_raw_cost_pp = NULL,
898 cmt_raw_cost_ptd = NULL,
899 cmt_burdened_cost_itd = NULL,
900 cmt_burdened_cost_ytd = NULL,
901 cmt_burdened_cost_pp = NULL,
902 cmt_burdened_cost_ptd = NULL,
903 cmt_quantity_itd = NULL,
904 cmt_quantity_ytd = NULL,
905 cmt_quantity_pp = NULL,
906 cmt_quantity_ptd = NULL,
907 cmt_unit_of_measure = NULL,
908 last_update_date = sysdate
909 WHERE project_accum_id IN (SELECT project_accum_id
910 FROM pa_project_accum_headers WHERE project_id = c.PROJECT_ID);
911
912 DELETE FROM pa_txn_accum_details pd
913 WHERE pd.line_type = 'M'
914 AND pd.txn_accum_id in (SELECT pt.txn_accum_id
915 FROM pa_txn_accum pt
916 WHERE pt.project_id = c.PROJECT_ID);
917 -- Added for bug 10399153 ends
918
919 ELSE
920
921 DELETE FROM pa_txn_accum_details pd
922 WHERE pd.line_type = 'M'
923 AND pd.txn_accum_id in (SELECT pt.txn_accum_id
924 FROM pa_txn_accum pt
925 WHERE pt.project_id = c.PROJECT_ID);
926
927 ACCUM_PROJPERF_COMMIT_TXNS(c.PROJECT_ID,
928 x_err_stage,
929 x_err_code);
930
931 END IF; -- Added for bug 13420421
932 */
933 ACCUM_PROJPERF_COMMITMENTS(c.PROJECT_ID,
934 x_err_stage,
935 x_err_code);
936
937 exception when others then
938
939 x_err_code := SQLCODE;
940
941 end;
942
943 end loop;
944
945 update PJI_HELPER_BATCH_MAP
946 set STATUS = 'C'
947 where WORKER_ID = p_worker_id and
948 BATCH_ID = l_helper_batch_id;
949
950 commit;
951 PA_CURRENCY.G_org_id := NULL; -- bug 6847113
952
953 else
954
955 select count(*)
956 into l_row_count
957 from PJI_HELPER_BATCH_MAP
958 where nvl(STATUS, 'X') <> 'C';
959
960 if (l_row_count = 0) then
961
962 for x in 2 .. l_parallel_processes loop
963
964 update PJI_SYSTEM_PRC_STATUS
965 set STEP_STATUS = 'C'
966 where PROCESS_NAME like PJI_FM_SUM_MAIN.g_process || x and
967 STEP_NAME =
968 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);' and
969 START_DATE is null;
970
971 commit;
972
973 end loop;
974
975 l_helper_batch_id := -1;
976
977 else
978
979 PJI_PROCESS_UTIL.SLEEP(1); -- so the CPU is not bombarded
980
981 end if;
982
983 end if;
984
985 if (l_helper_batch_id >= 0) then
986
987 for x in 2 .. l_parallel_processes loop
988 if (not PJI_FM_SUM_EXTR.WORKER_STATUS(x, 'OKAY')) then
989 l_helper_batch_id := -2;
990 end if;
991 end loop;
992
993 end if;
994
995 end loop;
996
997 if (l_helper_batch_id <> -2) then
998
999 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1000 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT(p_worker_id);');
1001
1002 end if;
1003
1004 commit;
1005
1006 end REFRESH_PROJPERF_CMT;
1007
1008
1009 -- -----------------------------------------------------
1010 -- procedure REFRESH_PROJPERF_CMT_POST
1011 -- -----------------------------------------------------
1012 procedure REFRESH_PROJPERF_CMT_POST (p_worker_id in number) is
1013
1014 l_extract_commitments varchar2(30);
1015 l_process varchar2(30);
1016 l_schema varchar2(30);
1017 l_psi_ppr_flag varchar2(1); -- bug 13889383
1018
1019 begin
1020
1021 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1022
1023 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
1024 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);')) then
1025 return;
1026 end if;
1027
1028 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1029 (PJI_FM_SUM_MAIN.g_process,
1030 'EXTRACT_COMMITMENTS');
1031
1032 select psi_ppr_flag into l_psi_ppr_flag from pji_system_settings; -- Added for bug 13889383
1033
1034 if (l_extract_commitments = 'N' or (l_extract_commitments = 'Y' and l_psi_ppr_flag = 'Y')) then -- Changes for bug 13889383
1035 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1036 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);');
1037 commit;
1038 return;
1039 end if;
1040
1041 l_schema := PJI_UTILS.GET_PJI_SCHEMA_NAME;
1042
1043 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1044 'PJI_FM_EXTR_DREVN',
1045 'NORMAL',
1046 null);
1047
1048 PJI_PROCESS_UTIL.TRUNC_INT_TABLE(l_schema,
1049 'PJI_HELPER_BATCH_MAP',
1050 'NORMAL',
1051 null);
1052
1053 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1054 'PJI_FM_CMT_EXTR.REFRESH_PROJPERF_CMT_POST(p_worker_id);');
1055
1056 commit;
1057
1058 end REFRESH_PROJPERF_CMT_POST;
1059
1060
1061 -- -----------------------------------------------------
1062 -- procedure FIN_CMT_SUMMARY
1063 -- -----------------------------------------------------
1064 procedure FIN_CMT_SUMMARY (p_worker_id in number) is
1065
1066 l_extract_commitments varchar2(30);
1067 l_process varchar2(30);
1068
1069 l_transition_flag varchar2(1);
1070 l_params_cost_flag varchar2(1);
1071 l_params_util_flag varchar2(1);
1072 l_g2_currency_code varchar2(30);
1073
1074 begin
1075
1076 l_process := PJI_FM_SUM_MAIN.g_process || p_worker_id;
1077
1078 if (not PJI_PROCESS_UTIL.NEED_TO_RUN_STEP(l_process,
1079 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);')) then
1080 return;
1081 end if;
1082
1083 l_extract_commitments := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER
1084 (PJI_FM_SUM_MAIN.g_process,
1085 'EXTRACT_COMMITMENTS');
1086
1087 if (l_extract_commitments = 'N') then
1088 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1089 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);');
1090 commit;
1091 return;
1092 end if;
1093
1094 pji_utils.write2log('Entering FIN_CMT_SUMMARY');
1095 l_transition_flag := PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process, 'TRANSITION');
1096
1097 if (l_transition_flag = 'Y') then
1098 l_params_cost_flag := nvl(PJI_PROCESS_UTIL.GET_PROCESS_PARAMETER(PJI_FM_SUM_MAIN.g_process,'CONFIG_COST_FLAG'), 'N');
1099 else -- l_transition is null or 'N'
1100 l_params_cost_flag := nvl(PJI_UTILS.GET_PARAMETER('CONFIG_COST_FLAG'), 'N');
1101 end if;
1102
1103 l_g2_currency_code := PJI_UTILS.GET_GLOBAL_SECONDARY_CURRENCY;
1104
1105 if (l_g2_currency_code is not null) then
1106 l_g2_currency_code := 'Y';
1107 else
1108 l_g2_currency_code := 'N';
1109 end if;
1110
1111 insert /*+ append parallel(fin2_i) */ into PJI_FM_AGGR_FIN2 fin2_i -- in FIN_SUMMARY
1112 (
1113 WORKER_ID,
1114 ROW_ID,
1115 RECORD_TYPE,
1116 CMT_RECORD_TYPE,
1117 DANGLING_RECVR_GL_RATE_FLAG,
1118 DANGLING_RECVR_PA_RATE_FLAG,
1119 DANGLING_RECVR_GL_RATE2_FLAG,
1120 DANGLING_RECVR_PA_RATE2_FLAG,
1121 DANGLING_PRVDR_EN_TIME_FLAG,
1122 DANGLING_PRVDR_GL_TIME_FLAG,
1123 DANGLING_PRVDR_PA_TIME_FLAG,
1124 DANGLING_RECVR_EN_TIME_FLAG,
1125 DANGLING_RECVR_GL_TIME_FLAG,
1126 DANGLING_RECVR_PA_TIME_FLAG,
1127 DANGLING_EXP_EN_TIME_FLAG,
1128 DANGLING_EXP_GL_TIME_FLAG,
1129 DANGLING_EXP_PA_TIME_FLAG,
1130 PJI_PROJECT_RECORD_FLAG,
1131 PJI_RESOURCE_RECORD_FLAG,
1132 PROJECT_ID,
1133 PROJECT_ORG_ID,
1134 PROJECT_ORGANIZATION_ID,
1135 PERSON_ID,
1136 EXPENDITURE_ORG_ID,
1137 EXPENDITURE_ORGANIZATION_ID,
1138 WORK_TYPE_ID,
1139 JOB_ID,
1140 EXP_EVT_TYPE_ID,
1141 PROJECT_TYPE_CLASS,
1142 TASK_ID,
1143 VENDOR_ID,
1144 EXPENDITURE_TYPE,
1145 EVENT_TYPE,
1146 EVENT_TYPE_CLASSIFICATION,
1147 EXPENDITURE_CATEGORY,
1148 REVENUE_CATEGORY,
1149 NON_LABOR_RESOURCE,
1150 BOM_LABOR_RESOURCE_ID,
1151 BOM_EQUIPMENT_RESOURCE_ID,
1152 INVENTORY_ITEM_ID,
1153 SYSTEM_LINKAGE_FUNCTION,
1154 RESOURCE_CLASS_CODE,
1155 PRVDR_GL_TIME_ID,
1156 RECVR_GL_TIME_ID,
1157 GL_PERIOD_NAME,
1158 PRVDR_PA_TIME_ID,
1159 RECVR_PA_TIME_ID,
1160 PA_PERIOD_NAME,
1161 EXPENDITURE_ITEM_TIME_ID,
1162 PJ_GL_CALENDAR_ID,
1163 PJ_PA_CALENDAR_ID,
1164 RS_GL_CALENDAR_ID,
1165 RS_PA_CALENDAR_ID,
1166 TXN_CURRENCY_CODE,
1167 TXN_REVENUE,
1168 TXN_RAW_COST,
1169 TXN_BRDN_COST,
1170 TXN_BILL_RAW_COST,
1171 TXN_BILL_BRDN_COST,
1172 PRJ_REVENUE,
1173 PRJ_LABOR_REVENUE,
1174 PRJ_RAW_COST,
1175 PRJ_BRDN_COST,
1176 PRJ_BILL_RAW_COST,
1177 PRJ_BILL_BRDN_COST,
1178 PRJ_LABOR_RAW_COST,
1179 PRJ_LABOR_BRDN_COST,
1180 PRJ_BILL_LABOR_RAW_COST,
1181 PRJ_BILL_LABOR_BRDN_COST,
1182 PRJ_REVENUE_WRITEOFF,
1183 POU_REVENUE,
1184 POU_LABOR_REVENUE,
1185 POU_RAW_COST,
1186 POU_BRDN_COST,
1187 POU_BILL_RAW_COST,
1188 POU_BILL_BRDN_COST,
1189 POU_LABOR_RAW_COST,
1190 POU_LABOR_BRDN_COST,
1191 POU_BILL_LABOR_RAW_COST,
1192 POU_BILL_LABOR_BRDN_COST,
1193 POU_REVENUE_WRITEOFF,
1194 EOU_REVENUE,
1195 EOU_RAW_COST,
1196 EOU_BRDN_COST,
1197 EOU_BILL_RAW_COST,
1198 EOU_BILL_BRDN_COST,
1199 LABOR_HRS,
1200 BILL_LABOR_HRS,
1201 TOTAL_HRS_A,
1202 BILL_HRS_A,
1203 GG1_REVENUE,
1204 GG1_LABOR_REVENUE,
1205 GG1_RAW_COST,
1206 GG1_BRDN_COST,
1207 GG1_BILL_RAW_COST,
1208 GG1_BILL_BRDN_COST,
1209 GG1_LABOR_RAW_COST,
1210 GG1_LABOR_BRDN_COST,
1211 GG1_BILL_LABOR_RAW_COST,
1212 GG1_BILL_LABOR_BRDN_COST,
1213 GG1_REVENUE_WRITEOFF,
1214 GP1_REVENUE,
1215 GP1_LABOR_REVENUE,
1216 GP1_RAW_COST,
1217 GP1_BRDN_COST,
1218 GP1_BILL_RAW_COST,
1219 GP1_BILL_BRDN_COST,
1220 GP1_LABOR_RAW_COST,
1221 GP1_LABOR_BRDN_COST,
1222 GP1_BILL_LABOR_RAW_COST,
1223 GP1_BILL_LABOR_BRDN_COST,
1224 GP1_REVENUE_WRITEOFF,
1225 GG2_REVENUE,
1226 GG2_LABOR_REVENUE,
1227 GG2_RAW_COST,
1228 GG2_BRDN_COST,
1229 GG2_BILL_RAW_COST,
1230 GG2_BILL_BRDN_COST,
1231 GG2_LABOR_RAW_COST,
1232 GG2_LABOR_BRDN_COST,
1233 GG2_BILL_LABOR_RAW_COST,
1234 GG2_BILL_LABOR_BRDN_COST,
1235 GG2_REVENUE_WRITEOFF,
1236 GP2_REVENUE,
1237 GP2_LABOR_REVENUE,
1238 GP2_RAW_COST,
1239 GP2_BRDN_COST,
1240 GP2_BILL_RAW_COST,
1241 GP2_BILL_BRDN_COST,
1242 GP2_LABOR_RAW_COST,
1243 GP2_LABOR_BRDN_COST,
1244 GP2_BILL_LABOR_RAW_COST,
1245 GP2_BILL_LABOR_BRDN_COST,
1246 GP2_REVENUE_WRITEOFF,
1247 CBS_ELEMENT_ID /*Added for CBS Changes */
1248 )
1249 select /*+ no_merge(tmp1) */
1250 1 WORKER_ID,
1251 null ROW_ID,
1252 'M' RECORD_TYPE,
1253 tmp1.LINE_TYPE CMT_RECORD_TYPE,
1254 tmp1.DANGLING_RECVR_GL_RATE_FLAG,
1255 tmp1.DANGLING_RECVR_PA_RATE_FLAG,
1256 tmp1.DANGLING_RECVR_GL_RATE2_FLAG,
1257 tmp1.DANGLING_RECVR_PA_RATE2_FLAG,
1258 null DANGLING_PRVDR_EN_TIME_FLAG,
1259 null DANGLING_PRVDR_GL_TIME_FLAG,
1260 null DANGLING_PRVDR_PA_TIME_FLAG,
1261 tmp1.DANGLING_RECVR_EN_TIME_FLAG,
1262 tmp1.DANGLING_RECVR_GL_TIME_FLAG,
1263 tmp1.DANGLING_RECVR_PA_TIME_FLAG,
1264 tmp1.DANGLING_EXP_EN_TIME_FLAG,
1265 tmp1.DANGLING_EXP_GL_TIME_FLAG,
1266 tmp1.DANGLING_EXP_PA_TIME_FLAG,
1267 decode(l_params_cost_flag,'N','N','Y') PJI_PROJECT_RECORD_FLAG,
1268 'N' PJI_RESOURCE_RECORD_FLAG,
1269 tmp1.PROJECT_ID,
1270 tmp1.PROJECT_ORG_ID,
1271 tmp1.PROJECT_ORGANIZATION_ID,
1272 -1 PERSON_ID,
1273 -1 EXPENDITURE_ORG_ID,
1274 tmp1.EXPENDITURE_ORGANIZATION_ID,
1275 -1 WORK_TYPE_ID,
1276 -1 JOB_ID,
1277 et.EXPENDITURE_TYPE_ID EXP_EVT_TYPE_ID,
1278 tmp1.PROJECT_TYPE_CLASS,
1279 tmp1.TASK_ID,
1280 tmp1.VENDOR_ID,
1281 et.EXPENDITURE_TYPE EXPENDITURE_TYPE,
1282 'PJI$NULL' EVENT_TYPE,
1283 'PJI$NULL' EVENT_TYPE_CLASSIFICATION,
1284 tmp1.EXPENDITURE_CATEGORY,
1285 et.REVENUE_CATEGORY_CODE REVENUE_CATEGORY,
1286 'PJI$NULL' NON_LABOR_RESOURCE,
1287 tmp1.BOM_LABOR_RESOURCE_ID,
1288 tmp1.BOM_EQUIPMENT_RESOURCE_ID,
1289 tmp1.INVENTORY_ITEM_ID,
1290 tmp1.SYSTEM_LINKAGE_FUNCTION,
1291 tmp1.RESOURCE_CLASS_CODE,
1292 -1 PRVDR_GL_TIME_ID,
1293 tmp1.RECVR_GL_TIME_ID,
1294 tmp1.GL_PERIOD_NAME,
1295 -1 PRVDR_PA_TIME_ID,
1296 tmp1.RECVR_PA_TIME_ID,
1297 tmp1.PA_PERIOD_NAME,
1298 tmp1.EXPENDITURE_ITEM_TIME_ID,
1299 tmp1.PJ_GL_CALENDAR_ID,
1300 tmp1.PJ_PA_CALENDAR_ID,
1301 -1 RS_GL_CALENDAR_ID,
1302 -1 RS_PA_CALENDAR_ID,
1303 tmp1.DENOM_CURRENCY_CODE TXN_CURRENCY_CODE,
1304 to_number(null) TXN_REVENUE,
1305 tmp1.DENOM_RAW_COST TXN_RAW_COST,
1306 tmp1.DENOM_BURDENED_COST TXN_BRDN_COST,
1307 to_number(null) TXN_BILL_RAW_COST,
1308 to_number(null) TXN_BILL_BRDN_COST,
1309 to_number(null) PRJ_REVENUE,
1310 to_number(null) PRJ_LABOR_REVENUE,
1311 tmp1.PRJ_RAW_COST,
1312 tmp1.PRJ_BRDN_COST,
1313 to_number(null) PRJ_BILL_RAW_COST,
1314 to_number(null) PRJ_BILL_BRDN_COST,
1315 tmp1.PRJ_LABOR_RAW_COST,
1316 tmp1.PRJ_LABOR_BRDN_COST,
1317 to_number(null) PRJ_BILL_LABOR_RAW_COST,
1318 to_number(null) PRJ_BILL_LABOR_BRDN_COST,
1319 to_number(null) PRJ_REVENUE_WRITEOFF,
1320 to_number(null) POU_REVENUE,
1321 to_number(null) POU_LABOR_REVENUE,
1322 tmp1.POU_RAW_COST,
1323 tmp1.POU_BRDN_COST,
1324 to_number(null) POU_BILL_RAW_COST,
1325 to_number(null) POU_BILL_BRDN_COST,
1326 tmp1.POU_LABOR_RAW_COST,
1327 tmp1.POU_LABOR_BRDN_COST,
1328 to_number(null) POU_BILL_LABOR_RAW_COST,
1329 to_number(null) POU_BILL_LABOR_BRDN_COST,
1330 to_number(null) POU_REVENUE_WRITEOFF,
1331 to_number(null) EOU_REVENUE,
1332 tmp1.EOU_RAW_COST,
1333 tmp1.EOU_BRDN_COST,
1334 to_number(null) EOU_BILL_RAW_COST,
1335 to_number(null) EOU_BILL_BRDN_COST,
1336 to_number(null) LABOR_HRS,
1337 to_number(null) BILL_LABOR_HRS,
1338 to_number(null) TOTAL_HRS_A,
1339 to_number(null) BILL_HRS_A,
1340 to_number(null) GG1_REVENUE,
1341 to_number(null) GG1_LABOR_REVENUE,
1342 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1343 GG1_RAW_COST,
1344 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1345 GG1_BRDN_COST,
1346 to_number(null) GG1_BILL_RAW_COST,
1347 to_number(null) GG1_BILL_BRDN_COST,
1348 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1349 GG1_LABOR_RAW_COST,
1350 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1351 GG1_LABOR_BRDN_COST,
1352 to_number(null) GG1_BILL_LABOR_RAW_COST,
1353 to_number(null) GG1_BILL_LABOR_BRDN_COST,
1354 to_number(null) GG1_REVENUE_WRITEOFF,
1355 to_number(null) GP1_REVENUE,
1356 to_number(null) GP1_LABOR_REVENUE,
1357 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1358 GP1_RAW_COST,
1359 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1360 GP1_BRDN_COST,
1361 to_number(null) GP1_BILL_RAW_COST,
1362 to_number(null) GP1_BILL_BRDN_COST,
1363 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1364 GP1_LABOR_RAW_COST,
1365 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE / MAU) * MAU
1366 GP1_LABOR_BRDN_COST,
1367 to_number(null) GP1_BILL_LABOR_RAW_COST,
1368 to_number(null) GP1_BILL_LABOR_BRDN_COST,
1369 to_number(null) GP1_REVENUE_WRITEOFF,
1370 to_number(null) GG2_REVENUE,
1371 to_number(null) GG2_LABOR_REVENUE,
1372 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1373 GG2_RAW_COST,
1374 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1375 GG2_BRDN_COST,
1376 to_number(null) GG2_BILL_RAW_COST,
1377 to_number(null) GG2_BILL_BRDN_COST,
1378 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1379 GG2_LABOR_RAW_COST,
1380 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1381 GG2_LABOR_BRDN_COST,
1382 to_number(null) GG2_BILL_LABOR_RAW_COST,
1383 to_number(null) GG2_BILL_LABOR_BRDN_COST,
1384 to_number(null) GG2_REVENUE_WRITEOFF,
1385 to_number(null) GP2_REVENUE,
1386 to_number(null) GP2_LABOR_REVENUE,
1387 round(tmp1.POU_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1388 GP2_RAW_COST,
1389 round(tmp1.POU_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1390 GP2_BRDN_COST,
1391 to_number(null) GP2_BILL_RAW_COST,
1392 to_number(null) GP2_BILL_BRDN_COST,
1393 round(tmp1.POU_LABOR_RAW_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1394 GP2_LABOR_RAW_COST,
1395 round(tmp1.POU_LABOR_BRDN_COST * tmp1.PRJ_PA_RATE2 / MAU2) * MAU2
1396 GP2_LABOR_BRDN_COST,
1397 to_number(null) GP2_BILL_LABOR_RAW_COST,
1398 to_number(null) GP2_BILL_LABOR_BRDN_COST,
1399 to_number(null) GP2_REVENUE_WRITEOFF,
1400 tmp1.CBS_ELEMENT_ID CBS_ELEMENT_ID /*Added for CBS Changes */
1401 from
1402 (
1403 select /*+ no_merge(tmp1) */
1404 decode(prj_rt.RATE,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1405 ,decode(sign(prj_rt.RATE),-1,'Y',null)) DANGLING_RECVR_GL_RATE_FLAG,
1406 decode(prj_rt.RATE,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1407 ,decode(sign(prj_rt.RATE),-1,'Y',null)) DANGLING_RECVR_PA_RATE_FLAG,
1408 decode(l_g2_currency_code,
1409 'Y', decode(prj_rt.RATE2,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1410 ,decode(sign(prj_rt.RATE2),-1,'Y',null)),
1411 null) DANGLING_RECVR_GL_RATE2_FLAG,
1412 decode(l_g2_currency_code,
1413 'Y', decode(prj_rt.RATE2,-3,'E' -- EUR conversion rate for 01-JAN-1999 is missing
1414 ,decode(sign(prj_rt.RATE2),-1,'Y',null)),
1415 null) DANGLING_RECVR_PA_RATE2_FLAG,
1416 decode(sign(prj_info.EN_CALENDAR_MIN_DATE-tmp1.RECVR_GL_TIME_ID)+
1417 sign(tmp1.RECVR_GL_TIME_ID-prj_info.EN_CALENDAR_MAX_DATE),
1418 0,'Y',null) DANGLING_RECVR_EN_TIME_FLAG,
1419 decode(sign(prj_info.EN_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1420 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.EN_CALENDAR_MAX_DATE),
1421 0,'Y',null) DANGLING_EXP_EN_TIME_FLAG,
1422 decode(sign(prj_info.GL_CALENDAR_MIN_DATE-tmp1.RECVR_GL_TIME_ID)+
1423 sign(tmp1.RECVR_GL_TIME_ID-prj_info.GL_CALENDAR_MAX_DATE),
1424 0,'Y',null) DANGLING_RECVR_GL_TIME_FLAG,
1425 decode(sign(prj_info.GL_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1426 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.GL_CALENDAR_MAX_DATE),
1427 0,'Y',null) DANGLING_EXP_GL_TIME_FLAG,
1428 decode(sign(prj_info.PA_CALENDAR_MIN_DATE-tmp1.RECVR_PA_TIME_ID)+
1429 sign(tmp1.RECVR_PA_TIME_ID-prj_info.PA_CALENDAR_MAX_DATE),
1430 0,'Y',null) DANGLING_RECVR_PA_TIME_FLAG,
1431 decode(sign(prj_info.PA_CALENDAR_MIN_DATE-tmp1.EXPENDITURE_ITEM_TIME_ID)+
1432 sign(tmp1.EXPENDITURE_ITEM_TIME_ID-prj_info.PA_CALENDAR_MAX_DATE),
1433 0,'Y',null) DANGLING_EXP_PA_TIME_FLAG,
1434 'Y',
1435 tmp1.PROJECT_ID,
1436 tmp1.PROJECT_ORG_ID,
1437 tmp1.PROJECT_ORGANIZATION_ID,
1438 tmp1.PROJECT_TYPE_CLASS,
1439 tmp1.EXPENDITURE_ORGANIZATION_ID,
1440 tmp1.RECVR_GL_TIME_ID,
1441 tmp1.RECVR_PA_TIME_ID,
1442 tmp1.EXPENDITURE_ITEM_TIME_ID,
1443 tmp1.GL_PERIOD_NAME,
1444 tmp1.PA_PERIOD_NAME,
1445 prj_info.GL_CALENDAR_ID PJ_GL_CALENDAR_ID,
1446 prj_info.PA_CALENDAR_ID PJ_PA_CALENDAR_ID,
1447 prj_rt.RATE PRJ_PA_RATE,
1448 prj_rt.MAU MAU,
1449 prj_rt.RATE2 PRJ_PA_RATE2,
1450 prj_rt.MAU2 MAU2,
1451 tmp1.PRJ_RAW_COST,
1452 tmp1.PRJ_BRDN_COST,
1453 tmp1.PRJ_LABOR_RAW_COST,
1454 tmp1.PRJ_LABOR_BRDN_COST,
1455 tmp1.POU_RAW_COST,
1456 tmp1.POU_BRDN_COST,
1457 tmp1.POU_LABOR_RAW_COST,
1458 tmp1.POU_LABOR_BRDN_COST,
1459 tmp1.EOU_RAW_COST,
1460 tmp1.EOU_BRDN_COST,
1461 tmp1.DENOM_CURRENCY_CODE,
1462 tmp1.DENOM_RAW_COST,
1463 tmp1.DENOM_BURDENED_COST,
1464 tmp1.TASK_ID,
1465 tmp1.VENDOR_ID,
1466 tmp1.EXPENDITURE_TYPE,
1467 tmp1.EXPENDITURE_CATEGORY,
1468 tmp1.SYSTEM_LINKAGE_FUNCTION,
1469 tmp1.RESOURCE_CLASS_CODE,
1470 tmp1.LINE_TYPE,
1471 tmp1.INVENTORY_ITEM_ID,
1472 tmp1.BOM_LABOR_RESOURCE_ID,
1473 tmp1.BOM_EQUIPMENT_RESOURCE_ID,
1474 tmp1.CBS_ELEMENT_ID /*Added for CBS Changes */
1475 from
1476 PJI_ORG_EXTR_INFO prj_info,
1477 (
1478 select /*+ parallel(tmp1) */
1479 tmp1.PROJECT_ID,
1480 proj.org_id PROJECT_ORG_ID,
1481 proj.carrying_out_organization_id PROJECT_ORGANIZATION_ID,
1482 DECODE(projtyp.PROJECT_TYPE_CLASS_CODE,
1483 'CAPITAL', 'C',
1484 'CONTRACT', 'B',
1485 'INDIRECT', 'I') PROJECT_TYPE_CLASS,
1486 tmp1.ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID,
1487 decode(l_params_cost_flag,'N',-1,
1488 to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J')))
1489 RECVR_GL_TIME_ID,
1490 decode(l_params_cost_flag,'N',-1,
1491 to_number(to_char(nvl(tmp1.CMT_PROMISED_DATE,nvl(tmp1.CMT_NEED_BY_DATE,tmp1.EXPENDITURE_ITEM_DATE)), 'J')))
1492 RECVR_PA_TIME_ID,
1493 to_number(to_char(tmp1.EXPENDITURE_ITEM_DATE,'J')) EXPENDITURE_ITEM_TIME_ID,
1494 null GL_PERIOD_NAME,
1495 null PA_PERIOD_NAME,
1496 sum(tmp1.PROJ_RAW_COST) PRJ_RAW_COST,
1497 sum(tmp1.PROJ_BURDENED_COST) PRJ_BRDN_COST,
1498 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1499 tmp1.SYSTEM_LINKAGE_FUNCTION),
1500 'ST', tmp1.PROJ_RAW_COST,
1501 'OT', tmp1.PROJ_RAW_COST, 0)) PRJ_LABOR_RAW_COST,
1502 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1503 tmp1.SYSTEM_LINKAGE_FUNCTION),
1504 'ST', tmp1.PROJ_BURDENED_COST,
1505 'OT', tmp1.PROJ_BURDENED_COST,
1506 0)) PRJ_LABOR_BRDN_COST,
1507 sum(tmp1.TOT_CMT_RAW_COST) POU_RAW_COST,
1508 sum(tmp1.TOT_CMT_BURDENED_COST) POU_BRDN_COST,
1509 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1510 tmp1.SYSTEM_LINKAGE_FUNCTION),
1511 'ST', tmp1.TOT_CMT_RAW_COST,
1512 'OT', tmp1.TOT_CMT_RAW_COST,
1513 0)) POU_LABOR_RAW_COST,
1514 sum(decode(NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1515 tmp1.SYSTEM_LINKAGE_FUNCTION),
1516 'ST', tmp1.TOT_CMT_BURDENED_COST,
1517 'OT', tmp1.TOT_CMT_BURDENED_COST,
1518 0)) POU_LABOR_BRDN_COST,
1519 sum(tmp1.ACCT_RAW_COST) EOU_RAW_COST,
1520 sum(tmp1.ACCT_BURDENED_COST) EOU_BRDN_COST,
1521 tmp1.DENOM_CURRENCY_CODE,
1522 sum(tmp1.DENOM_RAW_COST) DENOM_RAW_COST,
1523 sum(tmp1.DENOM_BURDENED_COST) DENOM_BURDENED_COST,
1524 tmp1.TASK_ID,
1525 tmp1.VENDOR_ID,
1526 tmp1.EXPENDITURE_TYPE,
1527 tmp1.EXPENDITURE_CATEGORY,
1528 NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1529 tmp1.SYSTEM_LINKAGE_FUNCTION) SYSTEM_LINKAGE_FUNCTION, --Bug 3964738
1530 tmp1.RESOURCE_CLASS RESOURCE_CLASS_CODE,
1531 tmp1.LINE_TYPE,
1532 tmp1.INVENTORY_ITEM_ID,
1533 tmp1.BOM_LABOR_RESOURCE_ID,
1534 tmp1.BOM_EQUIPMENT_RESOURCE_ID,
1535 tmp1.CBS_ELEMENT_ID /*Added for CBS Changes 16832862 */
1536 from
1537 PA_COMMITMENT_TXNS tmp1,
1538 PA_PROJECTS_ALL proj,
1539 PA_PROJECT_TYPES_ALL projtyp
1540 where
1541 tmp1.project_id = proj.project_id and
1542 proj.project_type = projtyp.project_type and
1543 proj.org_id = projtyp.org_id /*5377131*/
1544 group by
1545 tmp1.PROJECT_ID,
1546 proj.org_id,
1547 proj.carrying_out_organization_id,
1548 projtyp.PROJECT_TYPE_CLASS_CODE,
1549 tmp1.ORGANIZATION_ID,
1550 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'))),
1551 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'))),
1552 to_number(to_char(tmp1.EXPENDITURE_ITEM_DATE,'J')),
1553 tmp1.GL_PERIOD,
1554 tmp1.PA_PERIOD,
1555 tmp1.DENOM_CURRENCY_CODE,
1556 tmp1.TASK_ID,
1557 tmp1.VENDOR_ID,
1558 tmp1.EXPENDITURE_TYPE,
1559 tmp1.EXPENDITURE_CATEGORY,
1560 NVL(tmp1.SRC_SYSTEM_LINKAGE_FUNCTION,
1561 tmp1.SYSTEM_LINKAGE_FUNCTION), --Bug 3964738
1562 tmp1.RESOURCE_CLASS,
1563 tmp1.LINE_TYPE,
1564 tmp1.INVENTORY_ITEM_ID,
1565 tmp1.BOM_LABOR_RESOURCE_ID,
1566 tmp1.BOM_EQUIPMENT_RESOURCE_ID,
1567 tmp1.CBS_ELEMENT_ID /* Added for CBS Changes */
1568 ) tmp1,
1569 PJI_FM_AGGR_DLY_RATES prj_rt
1570 where
1571 tmp1.PROJECT_ORG_ID = prj_info.ORG_ID and
1572 prj_rt.WORKER_ID = -1 and
1573 --tmp1.RECVR_PA_TIME_ID = prj_rt.TIME_ID and --Bug 6894858
1574 to_number(to_char(trunc(sysdate), 'J')) = prj_rt.TIME_ID and
1575 prj_info.PF_CURRENCY_CODE = prj_rt.PF_CURRENCY_CODE
1576 ) tmp1,
1577 PA_EXPENDITURE_TYPES et
1578 where
1579 tmp1.EXPENDITURE_TYPE = et.EXPENDITURE_TYPE;
1580
1581 pji_utils.write2log('Leaving FIN_CMT_SUMMARY');
1582
1583 PJI_PROCESS_UTIL.REGISTER_STEP_COMPLETION(l_process,
1584 'PJI_FM_CMT_EXTR.FIN_CMT_SUMMARY(p_worker_id);');
1585
1586 commit;
1587
1588 end FIN_CMT_SUMMARY;
1589
1590 end PJI_FM_CMT_EXTR;