[Home] [Help]
PACKAGE BODY: APPS.PA_TXN_ACCUMS
Source
1 PACKAGE BODY PA_TXN_ACCUMS AS
2 /* $Header: PATXNACB.pls 120.13.12020000.7 2013/04/23 06:31:36 bpottipa ship $ */
3
4 -- Initialize function
5
6
7 FUNCTION initialize RETURN NUMBER IS
8 x_err_code NUMBER:=0;
9 BEGIN
10
11 RETURN 0;
12 EXCEPTION
13 WHEN OTHERS THEN
14 x_err_code := SQLCODE;
15 RETURN x_err_code;
16 END initialize;
17
18 FUNCTION cmt_line_id RETURN NUMBER IS
19 cmt_line_id NUMBER;
20 BEGIN
21 SELECT
22 pa_commitment_txns_s.NEXTVAL
23 INTO cmt_line_id
24 FROM
25 SYS.DUAL;
26 RETURN cmt_line_id;
27 END cmt_line_id;
28
29 -- Get accumulation configuration
30
31
32
33 PROCEDURE get_accum_configurations
34 ( x_project_id IN NUMBER,
35 x_err_stage IN OUT NOCOPY VARCHAR2,
36 x_err_code IN OUT NOCOPY NUMBER)
37 IS
38 x_err_stack VARCHAR2(255);
39 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
40
41 BEGIN
42 x_err_code :=0;
43 x_err_stage := 'Getting the accumulation configuration';
44 x_err_stack := '->get_accum_configurations';
45
46 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
47 pa_debug.debug('get_accum_configurations: ' || x_err_stage);
48 END IF;
49
50 -- Pass on the project_id to determine the configuration
51
52 pa_accum_utils.get_config_option
53 (x_project_id,
54 'ACTUALS',
55 'RAW_COST',
56 raw_cost_flag,
57 x_err_code,
58 x_err_stage,
59 x_err_stack);
60
61 pa_accum_utils.get_config_option
62 (x_project_id,
63 'ACTUALS',
64 'BURDENED_COST',
65 burdened_cost_flag,
66 x_err_code,
67 x_err_stage,
68 x_err_stack);
69 pa_accum_utils.get_config_option
70 (x_project_id,
71 'ACTUALS',
72 'QUANTITY',
73 quantity_flag,
74 x_err_code,
75 x_err_stage,
76 x_err_stack);
77 pa_accum_utils.get_config_option
78 (x_project_id,
79 'ACTUALS',
80 'LABOR_HOURS',
81 labor_hours_flag,
82 x_err_code,
83 x_err_stage,
84 x_err_stack);
85
86 pa_accum_utils.get_config_option
87 (x_project_id,
88 'ACTUALS',
89 'BILLABLE_RAW_COST',
90 billable_raw_cost_flag,
91 x_err_code,
92 x_err_stage,
93 x_err_stack);
94
95 pa_accum_utils.get_config_option
96 (x_project_id,
97 'ACTUALS',
98 'BILLABLE_BURDENED_COST',
99 billable_burdened_cost_flag,
100 x_err_code,
101 x_err_stage,
102 x_err_stack);
103
104 pa_accum_utils.get_config_option
105 (x_project_id,
106 'ACTUALS',
107 'BILLABLE_QUANTITY',
108 billable_quantity_flag,
109 x_err_code,
110 x_err_stage,
111 x_err_stack);
112 pa_accum_utils.get_config_option
113 (x_project_id,
114 'ACTUALS',
115 'BILLABLE_LABOR_HOURS',
116 billable_labor_hours_flag,
117 x_err_code,
118 x_err_stage,
119 x_err_stack);
120 pa_accum_utils.get_config_option
121 (x_project_id,
122 'ACTUALS',
123 'REVENUE',
124 revenue_flag,
125 x_err_code,
126 x_err_stage,
127 x_err_stack);
128 pa_accum_utils.get_config_option
129 (x_project_id,
130 'COMMITMENTS',
131 'CMT_RAW_COST',
132 cmt_raw_cost_flag,
133 x_err_code,
134 x_err_stage,
135 x_err_stack);
136 pa_accum_utils.get_config_option
137 (x_project_id,
138 'COMMITMENTS',
139 'CMT_BURDENED_COST',
140 cmt_burdened_cost_flag,
141 x_err_code,
142 x_err_stage,
143 x_err_stack);
144
145 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
146 pa_debug.debug('get_accum_configurations: ' || 'The following columns are configured for accumulation for this project:');
147 pa_debug.debug('get_accum_configurations: ' || 'Raw_cost='||RAW_COST_FLAG||' Burdened_cost='||BURDENED_COST_FLAG||
148 ' Quantity='||QUANTITY_FLAG||' Labor_hours='||LABOR_HOURS_FLAG);
149 pa_debug.debug('get_accum_configurations: ' || 'Billable_raw_cost='||BILLABLE_RAW_COST_FLAG||
150 ' Billable_burdened_cost='||BILLABLE_BURDENED_COST_FLAG||
151 ' Billable_quantity='||BILLABLE_QUANTITY_FLAG||
152 ' Billable_labor_hours='|| BILLABLE_LABOR_HOURS_FLAG);
153 pa_debug.debug('get_accum_configurations: ' || 'Revenue='||REVENUE_FLAG||' Cmt_raw_cost'||CMT_RAW_COST_FLAG||
154 ' Cmt_burdened_cost='|| CMT_BURDENED_COST_FLAG);
155 END IF;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 x_err_code := SQLCODE;
160 RAISE;
161 END get_accum_configurations;
162
163
164 --
165 -- 20-MAY-2003 jwhite For r11i.PA.L Burdening Enhancements, modified the
166 -- UPDATE pa_cost_distribution_lines_all statement:
167 --
168 -- Code like the following:
169 -- AND cdl.line_type = 'R'
170 -- was replaced with the following:
171 -- AND ( cdl.line_type = 'R' OR cdl.line_type = 'I')
172
173 PROCEDURE update_resource_flag
174 (x_start_project_id IN NUMBER,
175 x_end_project_id IN NUMBER,
176 x_start_pa_date IN DATE,
177 x_end_pa_date IN DATE,
178 x_err_stage IN OUT NOCOPY VARCHAR2,
179 x_err_code IN OUT NOCOPY NUMBER)
180 IS
181 tot_recs_processed NUMBER;
182 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
183 /*Code Changes for Bug No.2984871 start */
184 l_rowcount number :=0;
185 /*Code Changes for Bug No.2984871 end */
186
187 BEGIN
188 x_err_code :=0;
189 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
190 x_err_stage := 'Updating resource_accumulated_flag';
191 pa_debug.debug('update_resource_flag: ' || x_err_stage);
192 END IF;
193 tot_recs_processed := 0;
194
195 LOOP
196
197 UPDATE pa_cost_distribution_lines_all SET
198 resource_accumulated_flag = 'N'
199 WHERE project_id = x_start_project_id AND
200 (line_type = 'R' OR line_type = 'I') AND
201 resource_accumulated_flag <> 'N' AND
202 TRUNC(pa_date) BETWEEN x_start_pa_date AND x_end_pa_date AND
203 ROWNUM <= pa_proj_accum_main.x_commit_size;
204 /*Code Changes for Bug No.2984871 start */
205 l_rowcount:=sql%rowcount;
206 /*Code Changes for Bug No.2984871 end */
207 COMMIT;
208 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
209 tot_recs_processed := tot_recs_processed + l_rowcount;
210
211 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
212 pa_debug.debug('update_resource_flag: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
213 END IF;
214 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
215 IF (l_rowcount < pa_proj_accum_main.x_commit_size) THEN
216 EXIT;
217 END IF;
218 END LOOP;
219
220 LOOP
221 UPDATE pa_draft_revenues SET
222 resource_accumulated_flag = 'S'
223 WHERE project_id = x_start_project_id AND
224 released_date IS NOT NULL AND
225 resource_accumulated_flag <> 'S' AND
226 TRUNC(pa_date) BETWEEN x_start_pa_date AND x_end_pa_date AND
227 ROWNUM <= pa_proj_accum_main.x_commit_size;
228 /*Code Changes for Bug No.2984871 start */
229 l_rowcount:=sql%rowcount;
230 /*Code Changes for Bug No.2984871 end */
231 COMMIT;
232 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
233 tot_recs_processed := tot_recs_processed + l_rowcount;
234
235 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
236 pa_debug.debug('update_resource_flag: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
237 END IF;
238 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
239 IF (l_rowcount < pa_proj_accum_main.x_commit_size) THEN
240 EXIT;
241 END IF;
242 END LOOP;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 x_err_code := SQLCODE;
247 RAISE;
248
249 END update_resource_flag;
250
251 -- Procedure for refreshing transaction accum
252
253 PROCEDURE refresh_txn_accum
254 ( x_start_project_id IN NUMBER,
255 x_end_project_id IN NUMBER,
256 x_start_pa_date IN DATE,
257 x_end_pa_date IN DATE,
258 x_transaction_type IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
259 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
260 x_err_stage IN OUT NOCOPY VARCHAR2,
261 x_err_code IN OUT NOCOPY NUMBER)
262 IS
263 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
264
265
266 BEGIN
267 x_err_code :=0;
268
269 -- Call the appropriate procedure depending on the value of
270 -- x_transaction_type. If it is = 'C' then only actual
271 -- cost accumulation need to be refreshed. If it is 'R' then
272 -- Revenue accumulation figures need to be refreshed. If
273 -- it is 'C' then commitment accumulation figures need to be refreshed.
274 -- If it is not specified then all the accumulation figures need
275 -- to be refreshed
276 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
277 x_err_stage := 'Refreshing transaction accum';
278 pa_debug.debug('refresh_txn_accum: ' || x_err_stage);
279 END IF;
280
281 IF ( x_transaction_type = 'C' ) THEN
282 -- Refresh actual cost accumulation Figures
283 refresh_act_txn_accum
284 ( x_start_project_id,
285 x_end_project_id,
286 x_start_pa_date,
287 x_end_pa_date,
288 x_system_linkage_function,
289 x_err_stage,
290 x_err_code);
291 delete_act_txn_accum_details
292 ( x_start_project_id,
293 x_end_project_id,
294 x_start_pa_date,
295 x_end_pa_date,
296 x_system_linkage_function,
297 x_err_stage,
298 x_err_code);
299
300 ELSIF ( x_transaction_type = 'R' ) THEN
301 -- Refresh revenue accumulation Figures
302 refresh_rev_txn_accum
303 ( x_start_project_id,
304 x_end_project_id,
305 x_start_pa_date,
306 x_end_pa_date,
307 x_err_stage,
308 x_err_code);
309 delete_rev_txn_accum_details
310 ( x_start_project_id,
311 x_end_project_id,
312 x_start_pa_date,
313 x_end_pa_date,
314 x_err_stage,
315 x_err_code);
316 ELSIF ( x_transaction_type = 'M' ) THEN
317 -- Refresh commitment accumulation Figures
318 refresh_cmt_txn_accum
319 ( x_start_project_id,
320 x_end_project_id,
321 x_start_pa_date,
322 x_end_pa_date,
323 x_system_linkage_function,
324 x_err_stage,
325 x_err_code);
326 delete_cmt_txn_accum_details
327 ( x_start_project_id,
328 x_end_project_id,
329 x_start_pa_date,
330 x_end_pa_date,
331 x_system_linkage_function,
332 x_err_stage,
333 x_err_code);
334 delete_cmt_txns
335 ( x_start_project_id,
336 x_end_project_id,
337 x_start_pa_date,
338 x_end_pa_date,
339 x_system_linkage_function,
340 x_err_stage,
341 x_err_code);
342 ELSIF ( x_transaction_type IS NULL ) THEN
343 -- Refresh actual cost, revenue and commitment accumulation Figures
344 -- and there drilldown keys
345 refresh_act_txn_accum
346 ( x_start_project_id,
347 x_end_project_id,
348 x_start_pa_date,
349 x_end_pa_date,
350 x_system_linkage_function,
351 x_err_stage,
352 x_err_code);
353 delete_act_txn_accum_details
354 ( x_start_project_id,
355 x_end_project_id,
356 x_start_pa_date,
357 x_end_pa_date,
358 x_system_linkage_function,
359 x_err_stage,
360 x_err_code);
361 refresh_rev_txn_accum
362 ( x_start_project_id,
363 x_end_project_id,
364 x_start_pa_date,
365 x_end_pa_date,
366 x_err_stage,
367 x_err_code);
368 delete_rev_txn_accum_details
369 ( x_start_project_id,
370 x_end_project_id,
371 x_start_pa_date,
372 x_end_pa_date,
373 x_err_stage,
374 x_err_code);
375 refresh_cmt_txn_accum
376 ( x_start_project_id,
377 x_end_project_id,
378 x_start_pa_date,
379 x_end_pa_date,
380 x_system_linkage_function,
381 x_err_stage,
382 x_err_code);
383 delete_cmt_txn_accum_details
384 ( x_start_project_id,
385 x_end_project_id,
386 x_start_pa_date,
387 x_end_pa_date,
388 x_system_linkage_function,
389 x_err_stage,
390 x_err_code);
391 delete_cmt_txns
392 ( x_start_project_id,
393 x_end_project_id,
394 x_start_pa_date,
395 x_end_pa_date,
396 x_system_linkage_function,
397 x_err_stage,
398 x_err_code);
399 END IF;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 x_err_code := SQLCODE;
404 RAISE;
405 END refresh_txn_accum;
406
407 -- Procedure for refreshing transaction accum for actual costs
408 -- This procedure will refresh the transaction accum table for
409 -- actual cost. The following from pa_txn_accum table is
410 -- refreshed
411 -- tot_raw_cost
412 -- tot_burdened_cost
413 -- tot_quantity
414 -- tot_labor_hours
415 -- tot_billable_raw_cost
416 -- tot_billable_burdened_cost
417 -- tot_billable_quantity
418 -- tot_billable_labor_hours
419 -- i_tot_raw_cost
420 -- i_tot_burdened_cost
421 -- i_tot_quantity
422 -- i_tot_labor_hours
423 -- i_tot_billable_raw_cost
424 -- i_tot_billable_burdened_cost
425 -- i_tot_billable_quantity
426 -- i_tot_billable_labor_hours
427 -- unit_of_measure
428
429 PROCEDURE refresh_act_txn_accum
430 ( x_start_project_id IN NUMBER,
431 x_end_project_id IN NUMBER,
432 x_start_pa_date IN DATE,
433 x_end_pa_date IN DATE,
434 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
435 x_err_stage IN OUT NOCOPY VARCHAR2,
436 x_err_code IN OUT NOCOPY NUMBER)
437 IS
438 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
439
440
441 BEGIN
442 x_err_code :=0;
443
444 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
445 x_err_stage := 'Refreshing Actual transaction accum';
446 pa_debug.debug('refresh_act_txn_accum: ' || x_err_stage);
447 END IF;
448
449 UPDATE
450 pa_txn_accum pta
451 SET
452 pta.tot_raw_cost = NULL,
453 pta.tot_burdened_cost = NULL,
454 pta.tot_quantity = NULL,
455 pta.tot_labor_hours = NULL,
456 pta.tot_billable_raw_cost = NULL,
457 pta.tot_billable_burdened_cost = NULL,
458 pta.tot_billable_quantity = NULL,
459 pta.tot_billable_labor_hours = NULL,
460 pta.i_tot_raw_cost = NULL,
461 pta.i_tot_burdened_cost = NULL,
462 pta.i_tot_quantity = NULL,
463 pta.i_tot_labor_hours = NULL,
464 pta.i_tot_billable_raw_cost = NULL,
465 pta.i_tot_billable_burdened_cost = NULL,
466 pta.i_tot_billable_quantity = NULL,
467 pta.i_tot_billable_labor_hours = NULL,
468 pta.unit_of_measure = NULL,
469 pta.actual_cost_rollup_flag = 'N',
470 pta.last_updated_by = x_last_updated_by,
471 pta.last_update_date = SYSDATE,
472 pta.request_id = x_request_id,
473 pta.program_application_id = x_program_application_id,
474 pta.program_id = x_program_id,
475 pta.program_update_date = SYSDATE
476 WHERE
477 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id - Commented for bug 3736097
478 AND pta.system_linkage_function =
479 NVL(x_system_linkage_function,pta.system_linkage_function)
480 AND EXISTS
481 ( SELECT 'Yes'
482 FROM pa_txn_accum_details ptad
483 WHERE pta.txn_accum_id = ptad.txn_accum_id
484 AND ptad.line_type = 'C'
485 )
486 AND EXISTS
487 ( SELECT 'Yes'
488 FROM pa_periods
489 WHERE period_name = pta.pa_period
490 AND start_date >= x_start_pa_date
491 AND end_date <= x_end_pa_date
492 );
493
494 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
495 pa_debug.debug('refresh_act_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
496 END IF;
497
498 COMMIT;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 x_err_code := SQLCODE;
503 RAISE;
504 END refresh_act_txn_accum;
505
506 -- Procedure for refreshing transaction accum for Revenue
507 -- This procedure will refresh the transaction accum table for
508 -- revenue. The following from pa_txn_accum table is
509 -- refreshed
510 -- tot_revenue
511 -- i_tot_revenue
512
513 PROCEDURE refresh_rev_txn_accum
514 ( x_start_project_id IN NUMBER,
515 x_end_project_id IN NUMBER,
516 x_start_pa_date IN DATE,
517 x_end_pa_date IN DATE,
518 x_err_stage IN OUT NOCOPY VARCHAR2,
519 x_err_code IN OUT NOCOPY NUMBER)
520
521 IS
522 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
523 BEGIN
524 x_err_code :=0;
525 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
526 x_err_stage := 'Refreshing Revenue transaction accum';
527 pa_debug.debug('refresh_rev_txn_accum: ' || x_err_stage);
528 END IF;
529
530 UPDATE
531 pa_txn_accum pta
532 SET
533 pta.tot_revenue = NULL,
534 pta.i_tot_revenue = NULL,
535 pta.revenue_rollup_flag = 'N',
536 pta.last_updated_by = x_last_updated_by,
537 pta.last_update_date = SYSDATE,
538 pta.request_id = x_request_id,
539 pta.program_application_id = x_program_application_id,
540 pta.program_id = x_program_id,
541 pta.program_update_date = SYSDATE
542 WHERE
543 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented for bug 3736097
544 AND EXISTS
545 ( SELECT 'Yes'
546 FROM pa_txn_accum_details ptad
547 WHERE pta.txn_accum_id = ptad.txn_accum_id
548 AND ptad.line_type IN ('R','E')
549 )
550 AND EXISTS
551 ( SELECT 'Yes'
552 FROM pa_periods
553 WHERE period_name = pta.pa_period
554 AND start_date >= x_start_pa_date
555 AND end_date <= x_end_pa_date
556 );
557
558 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
559 pa_debug.debug('refresh_rev_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
560 END IF;
561
562 COMMIT;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 x_err_code := SQLCODE;
567 RAISE;
568 END refresh_rev_txn_accum;
569
570 -- Procedure for refreshing transaction accum for commitment costs
571 -- This procedure will refresh the transaction accum table for
572 -- commitment cost. The following from pa_txn_accum table is
573 -- refreshed
574
575 PROCEDURE refresh_cmt_txn_accum
576 ( x_start_project_id IN NUMBER,
577 x_end_project_id IN NUMBER,
578 x_start_pa_date IN DATE,
579 x_end_pa_date IN DATE,
580 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
581 x_err_stage IN OUT NOCOPY VARCHAR2,
582 x_err_code IN OUT NOCOPY NUMBER)
583 IS
584 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
585 BEGIN
586 x_err_code :=0;
587 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
588 x_err_stage := 'Refreshing commitments transaction accum';
589 pa_debug.debug('refresh_cmt_txn_accum: ' || x_err_stage);
590 END IF;
591
592 UPDATE /*+ leading(PTA) */ /* 10144700 */
593 pa_txn_accum pta
594 SET
595 pta.tot_cmt_raw_cost = NULL,
596 pta.tot_cmt_burdened_cost = NULL,
597 pta.cmt_rollup_flag = 'N',
598 pta.last_updated_by = x_last_updated_by,
599 pta.last_update_date = SYSDATE,
600 pta.request_id = x_request_id,
601 pta.program_application_id = x_program_application_id,
602 pta.program_id = x_program_id,
603 pta.program_update_date = SYSDATE
604 WHERE
605 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for bug 3736097
606 -- System_linkage_function can be Null for commitments
607 AND NVL(pta.system_linkage_function,'X') =
608 NVL(NVL(x_system_linkage_function,pta.system_linkage_function),'X')
609 AND EXISTS
610 ( SELECT /*+ NO_UNNEST push_subq */ 'Yes' /* 10144700 */
611 FROM pa_txn_accum_details ptad
612 WHERE pta.txn_accum_id = ptad.txn_accum_id
613 AND ptad.line_type = 'M'
614 )
615 AND EXISTS
616 ( SELECT 'Yes'
617 FROM pa_periods
618 WHERE period_name = pta.pa_period
619 AND start_date >= x_start_pa_date
620 AND end_date <= x_end_pa_date
621 );
622
623 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
624 pa_debug.debug('refresh_cmt_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
625 END IF;
626
627 COMMIT;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 x_err_code := SQLCODE;
632 RAISE;
633 END refresh_cmt_txn_accum;
634
635 -- This procedure will update txn accumulation for re-accumulation
636
637 PROCEDURE update_act_txn_accum
638 ( x_start_project_id IN NUMBER,
639 x_end_project_id IN NUMBER,
640 x_err_stage IN OUT NOCOPY VARCHAR2,
641 x_err_code IN OUT NOCOPY NUMBER)
642 IS
643 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
644 BEGIN
645 x_err_code :=0;
646
647 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
648 x_err_stage := 'Refreshing transaction accumulation for Reaccumulation';
649 pa_debug.debug('update_act_txn_accum: ' || x_err_stage);
650 END IF;
651
652 UPDATE
653 pa_txn_accum pta
654 SET
655 pta.i_tot_raw_cost = DECODE(raw_cost_flag,'Y',
656 (NVL(i_tot_raw_cost, 0) + NVL(tot_raw_cost,0)), NULL),
657 pta.i_tot_burdened_cost = DECODE(burdened_cost_flag,'Y',
658 (NVL(i_tot_burdened_cost, 0) + NVL(tot_burdened_cost,0)),
659 NULL),
660 pta.i_tot_quantity = DECODE(quantity_flag,'Y',
661 (NVL(i_tot_quantity, 0) + NVL(tot_quantity,0)), NULL),
662 pta.i_tot_labor_hours = DECODE(labor_hours_flag,'Y',
663 (NVL(i_tot_labor_hours,0) +
664 DECODE(pta.system_linkage_function,
665 'OT', NVL(tot_quantity,0),
666 'ST', NVL(tot_quantity,0), 0)),NULL),
667 pta.i_tot_billable_raw_cost = DECODE(billable_raw_cost_flag,'Y',
668 (NVL(i_tot_billable_raw_cost, 0) +
669 NVL(tot_billable_raw_cost,0)),NULL),
670 pta.i_tot_billable_burdened_cost = DECODE(billable_burdened_cost_flag,'Y',
671 (NVL(i_tot_billable_burdened_cost, 0) +
672 NVL(tot_billable_burdened_cost,0)),NULL),
673 pta.i_tot_billable_quantity = DECODE(billable_quantity_flag,'Y',
674 (NVL(i_tot_billable_quantity, 0) +
675 NVL(tot_billable_quantity,0)),NULL),
676 pta.i_tot_billable_labor_hours = DECODE(billable_labor_hours_flag,'Y',
677 (NVL(i_tot_billable_labor_hours,0) +
678 DECODE(pta.system_linkage_function,
679 'OT', NVL(tot_billable_quantity,0),
680 'ST', NVL(tot_billable_quantity,0), 0)),NULL),
681 pta.i_tot_revenue = DECODE(revenue_flag,'Y',
682 (NVL(i_tot_revenue, 0) + NVL(tot_revenue,0)),NULL),
683 pta.tot_raw_cost = NULL,
684 pta.tot_burdened_cost = NULL,
685 pta.tot_quantity = NULL,
686 pta.tot_labor_hours = NULL,
687 pta.tot_billable_raw_cost = NULL,
688 pta.tot_billable_burdened_cost = NULL,
689 pta.tot_billable_quantity = NULL,
690 pta.tot_billable_labor_hours = NULL,
691 pta.tot_revenue = NULL,
692 pta.actual_cost_rollup_flag = 'Y',
693 pta.revenue_rollup_flag = 'Y',
694 pta.last_update_date = SYSDATE,
695 pta.last_updated_by = x_last_updated_by,
696 pta.request_Id = x_request_id,
697 pta.program_application_id = x_program_application_id,
698 pta.program_id = x_program_id,
699 pta.program_update_Date = SYSDATE
700 WHERE
701 pta.project_id BETWEEN x_start_project_id AND x_end_project_id
702 AND pta.request_id <> x_request_id
703 AND EXISTS
704 ( SELECT 'Yes'
705 FROM pa_txn_accum_details ptad
706 WHERE pta.txn_accum_id = ptad.txn_accum_id
707 AND ptad.line_type IN ('C','R','E')
708 );
709
710 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
711 pa_debug.debug('update_act_txn_accum: ' || 'Records Updated = '||TO_CHAR(SQL%ROWCOUNT));
712 END IF;
713
714 COMMIT;
715
716 EXCEPTION
717 WHEN OTHERS THEN
718 x_err_code := SQLCODE;
719 RAISE;
720 END update_act_txn_accum;
721
722 -- Procedure for refreshing transaction accumes details for actual costs
723 -- This procedure deletes the rows from drill down table when
724 -- transaction accumes are refreshed
725
726 PROCEDURE delete_act_txn_accum_details
727 ( x_start_project_id IN NUMBER,
728 x_end_project_id IN NUMBER,
729 x_start_pa_date IN DATE,
730 x_end_pa_date IN DATE,
731 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
732 x_err_stage IN OUT NOCOPY VARCHAR2,
733 x_err_code IN OUT NOCOPY NUMBER)
734 IS
735 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
736 tot_recs_processed NUMBER;
737 /*Code Changes for Bug No.2984871 start */
738 l_rowcount number :=0;
739 /*Code Changes for Bug No.2984871 end */
740
741 BEGIN
742 x_err_code :=0;
743 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
744 x_err_stage := 'Deleteing Actual transaction accum details';
745 tot_recs_processed := 0;
746 pa_debug.debug('delete_act_txn_accum_details: ' || x_err_stage);
747 END IF;
748
749 LOOP
750 DELETE pa_txn_accum_details ptad
751 WHERE txn_accum_id IN
752 (SELECT txn_accum_id FROM pa_txn_accum pta
753 WHERE
754 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented for bug 3736097
755 AND pta.system_linkage_function =
756 NVL(x_system_linkage_function,pta.system_linkage_function)
757 AND EXISTS
758 ( SELECT 'Yes'
759 FROM pa_periods
760 WHERE period_name = pta.pa_period
761 AND start_date >= x_start_pa_date
762 AND end_date <= x_end_pa_date
763 )
764 )
765 AND ptad.line_type = 'C'
766 AND ROWNUM <= pa_proj_accum_main.x_commit_size;
767 /*Code Changes for Bug No.2984871 start */
768 l_rowcount:=sql%rowcount;
769 /*Code Changes for Bug No.2984871 end */
770 COMMIT;
771 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
772 tot_recs_processed := tot_recs_processed + l_rowcount;
773 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
774 pa_debug.debug('delete_act_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
775 END IF;
776 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
777 IF l_rowcount < pa_proj_accum_main.x_commit_size THEN
778 EXIT;
779 END IF;
780 END LOOP;
781
782 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
783 pa_debug.debug('delete_act_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
784 END IF;
785
786 EXCEPTION
787 WHEN OTHERS THEN
788 x_err_code := SQLCODE;
789 RAISE;
790 END delete_act_txn_accum_details;
791
792
793 -- Procedure for refreshing transaction accumes details for revenue
794 -- This procedure deletes the rows from drill down table when
795 -- transaction accumes are refreshed
796
797 PROCEDURE delete_rev_txn_accum_details
798 ( x_start_project_id IN NUMBER,
799 x_end_project_id IN NUMBER,
800 x_start_pa_date IN DATE,
801 x_end_pa_date IN DATE,
802 x_err_stage IN OUT NOCOPY VARCHAR2,
803 x_err_code IN OUT NOCOPY NUMBER)
804 IS
805 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
806 tot_recs_processed NUMBER;
807 /*Code Changes for Bug No.2984871 start */
808 l_rowcount number :=0;
809 /*Code Changes for Bug No.2984871 end */
810
811 BEGIN
812 x_err_code :=0;
813 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
814 x_err_stage := 'Deleteing Revenue transaction accum details';
815 tot_recs_processed := 0;
816 pa_debug.debug('delete_rev_txn_accum_details: ' || x_err_stage);
817 END IF;
818
819 LOOP
820 DELETE pa_txn_accum_details ptad
821 WHERE txn_accum_id IN
822 (SELECT txn_accum_id FROM pa_txn_accum pta
823 WHERE
824 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for bug 3736097
825 AND EXISTS
826 ( SELECT 'Yes'
827 FROM pa_periods
828 WHERE period_name = pta.pa_period
829 AND start_date >= x_start_pa_date
830 AND end_date <= x_end_pa_date
831 )
832 )
833 AND ptad.line_type IN ('R','E')
834 AND ROWNUM <= pa_proj_accum_main.x_commit_size;
835 /*Code Changes for Bug No.2984871 start */
836 l_rowcount:=sql%rowcount;
837 /*Code Changes for Bug No.2984871 end */
838
839 COMMIT;
840 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
841 tot_recs_processed := tot_recs_processed + l_rowcount;
842 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
843 pa_debug.debug('delete_rev_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
844 END IF;
845 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
846 IF (l_rowcount < pa_proj_accum_main.x_commit_size) THEN
847 EXIT;
848 END IF;
849 END LOOP;
850
851 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
852 pa_debug.debug('delete_rev_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
853 END IF;
854
855 EXCEPTION
856 WHEN OTHERS THEN
857 x_err_code := SQLCODE;
858 RAISE;
859 END delete_rev_txn_accum_details;
860
861 -- Procedure for refreshing transaction accumes details for commitments
862 -- This procedure deletes the rows from drill down table when
863 -- transaction accumes are refreshed
864
865 PROCEDURE delete_cmt_txn_accum_details
866 ( x_start_project_id IN NUMBER,
867 x_end_project_id IN NUMBER,
868 x_start_pa_date IN DATE,
869 x_end_pa_date IN DATE,
870 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
871 x_err_stage IN OUT NOCOPY VARCHAR2,
872 x_err_code IN OUT NOCOPY NUMBER)
873 IS
874 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
875 /*Code Changes for Bug No.2984871 start */
876 l_rowcount number :=0;
877 /*Code Changes for Bug No.2984871 end */
878
879
880 tot_recs_processed NUMBER;
881 BEGIN
882 x_err_code :=0;
883 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
884 x_err_stage := 'Deleteing commitments transaction accum details';
885 tot_recs_processed := 0;
886 pa_debug.debug('delete_cmt_txn_accum_details: ' || x_err_stage);
887 END IF;
888
889 LOOP
890 DELETE pa_txn_accum_details ptad
891 WHERE txn_accum_id IN
892 (SELECT txn_accum_id FROM pa_txn_accum pta
893 WHERE
894 pta.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented out for bug 3736097
895 AND NVL(pta.system_linkage_function,'X') =
896 NVL(NVL(x_system_linkage_function,pta.system_linkage_function),'X')
897 AND EXISTS
898 ( SELECT 'Yes'
899 FROM pa_periods
900 WHERE period_name = pta.pa_period
901 AND start_date >= x_start_pa_date
902 AND end_date <= x_end_pa_date
903 )
904 )
905 AND ptad.line_type = 'M'
906 AND ROWNUM <= pa_proj_accum_main.x_commit_size;
907 /*Code Changes for Bug No.2984871 start */
908 l_rowcount:=sql%rowcount;
909 /*Code Changes for Bug No.2984871 end */
910 COMMIT;
911 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
912 tot_recs_processed := tot_recs_processed + l_rowcount;
913 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
914 pa_debug.debug('delete_cmt_txn_accum_details: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
915 END IF;
916 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
917 IF l_rowcount < pa_proj_accum_main.x_commit_size THEN
918 EXIT;
919 END IF;
920 END LOOP;
921
922 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
923 pa_debug.debug('delete_cmt_txn_accum_details: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
924 END IF;
925
926 EXCEPTION
927 WHEN OTHERS THEN
928 x_err_code := SQLCODE;
929 RAISE;
930 END delete_cmt_txn_accum_details;
931
932 -- Procedure for creating transaction accum Details
933
934 PROCEDURE create_txn_accum_details
935 (x_txn_accum_id IN NUMBER,
936 x_line_type IN VARCHAR2,
937 x_expenditure_item_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
938 x_line_num IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
939 x_event_num IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
940 x_cmt_line_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
941 x_project_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
942 x_task_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
943 x_err_stage IN OUT NOCOPY VARCHAR2,
944 x_err_code IN OUT NOCOPY NUMBER)
945 IS
946 BEGIN
947 x_err_code :=0;
948 x_err_stage := 'Creating transaction accum details';
949
950 INSERT INTO pa_txn_accum_details
951 (txn_accum_id,
952 line_type,
953 expenditure_item_id,
954 line_num,
955 event_num,
956 cmt_line_id,
957 project_id,
958 task_id,
959 creation_date,
960 created_by,
961 last_updated_by,
962 last_update_date,
963 last_update_login,
964 request_id,
965 program_application_id,
966 program_id)
967 VALUES
968 (x_txn_accum_id,
969 x_line_type,
970 x_expenditure_item_id,
971 x_line_num,
972 x_event_num,
973 x_cmt_line_id,
974 x_project_id,
975 x_task_id,
976 SYSDATE,
977 x_created_by,
978 x_last_updated_by,
979 SYSDATE,
980 x_last_update_login,
981 x_request_id,
982 x_program_application_id,
983 x_program_id);
984
985 EXCEPTION
986 WHEN OTHERS THEN
987 x_err_code := SQLCODE;
988 RAISE;
989
990 END create_txn_accum_details;
991
992 -- This procedure checks if a combinations of given column
993 -- Exit in the PA_TXN_ACCUM table
994 -- x_line_type represent the type fo line
995 -- i.e. 'C' for CDL, 'R' for revenue and 'M' for commitments
996 -- If combination does not exist it will create the row
997 -- and returns the primary key TXN_ACCUM_ID and Status
998
999 PROCEDURE create_txn_accum
1000 ( x_project_id IN NUMBER,
1001 x_task_Id IN NUMBER,
1002 x_pa_period IN VARCHAR2,
1003 x_gl_period IN VARCHAR2,
1004 x_week_ending_date IN DATE,
1005 x_month_ending_date IN DATE,
1006 x_person_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1007 x_job_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1008 x_vendor_id IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1009 x_expenditure_type IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1010 x_organization_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1011 x_non_labor_resource IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1012 x_non_labor_resource_org_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1013 x_expenditure_category IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1014 x_revenue_category IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1015 x_event_type IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1016 x_event_type_classification IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1017 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1018 x_line_type IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1019 x_cost_ind_compiled_set_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1020 x_rev_ind_compiled_set_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1021 x_inv_ind_compiled_set_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1022 x_cmt_ind_compiled_set_id IN NUMBER, -- Default value removed to avoid GSCC warning File.Pkg.22
1023 x_txn_accum_id IN OUT NOCOPY NUMBER,
1024 x_err_stage IN OUT NOCOPY VARCHAR2,
1025 x_err_code IN OUT NOCOPY NUMBER)
1026 IS
1027 is_row_found NUMBER;
1028 BEGIN
1029
1030 is_row_found := 1; /* Assume a row exist in pa_txn_accum */
1031 x_err_code := 0;
1032 x_err_stage := 'Creating transaction accums';
1033
1034 <<get_txn_accum_id>>
1035 BEGIN
1036
1037 -- Seperating Expenditure Items/Events Processing
1038 IF ( x_expenditure_type IS NOT NULL ) THEN
1039
1040 -- Seperating processing where person_id is null/not null
1041 -- to take advantage of index on person_id
1042 IF ( x_person_id IS NOT NULL ) THEN
1043 -- person_id is not null
1044 SELECT /*+ index(pta PA_TXN_ACCUM_N2)*/ txn_accum_id -- Added hint for bug 4504019
1045 INTO x_txn_accum_id
1046 FROM pa_txn_accum pta
1047 WHERE x_project_id = pta.project_id
1048 AND x_task_Id = pta.task_id
1049 AND x_pa_period = pta.pa_period
1050 AND x_gl_period = pta.gl_period
1051 AND x_week_ending_date = pta.week_ending_date
1052 AND x_month_ending_date = pta.month_ending_date
1053 AND x_expenditure_type = pta.expenditure_type
1054 AND x_organization_id = pta.organization_id
1055 AND x_person_id = pta.person_id
1056 AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
1057 AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
1058 AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
1059 AND NVL(x_non_labor_resource_org_id,-1)
1060 = NVL(pta.non_labor_resource_org_id,-1)
1061 AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
1062 AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
1063 AND NVL(x_system_linkage_function,'X')
1064 = NVL(pta.system_linkage_function,'X')
1065 AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
1066 = NVL(pta.cost_ind_compiled_set_id,-1)
1067 AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
1068 = NVL(pta.rev_ind_compiled_set_id,-1)
1069 AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
1070 = NVL(pta.inv_ind_compiled_set_id,-1)
1071 AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
1072 = NVL(pta.cmt_ind_compiled_set_id,-1);
1073 ELSE
1074 -- When person_id is not available
1075 SELECT txn_accum_id
1076 INTO x_txn_accum_id
1077 FROM pa_txn_accum pta
1078 WHERE x_project_id = pta.project_id
1079 AND x_task_Id = pta.task_id
1080 AND x_pa_period = pta.pa_period
1081 AND x_gl_period = pta.gl_period
1082 AND x_week_ending_date = pta.week_ending_date
1083 AND x_month_ending_date = pta.month_ending_date
1084 AND x_expenditure_type = pta.expenditure_type
1085 AND x_organization_id = pta.organization_id
1086 AND pta.person_id IS NULL
1087 AND NVL(x_job_id,-1) = NVL(pta.job_id,-1)
1088 AND NVL(x_vendor_id,-1) = NVL(pta.vendor_id,-1)
1089 AND NVL(x_non_labor_resource,'X') = NVL(pta.non_labor_resource,'X')
1090 AND NVL(x_non_labor_resource_org_id,-1)
1091 = NVL(pta.non_labor_resource_org_id,-1)
1092 AND NVL(x_expenditure_category,'X')= NVL(pta.expenditure_category,'X')
1093 AND NVL(x_revenue_category,'X') = NVL(pta.revenue_category,'X')
1094 AND NVL(x_system_linkage_function,'X')
1095 = NVL(pta.system_linkage_function,'X')
1096 AND DECODE(x_line_type,'C',(NVL(x_cost_ind_compiled_set_id,-1)),-1)
1097 = NVL(pta.cost_ind_compiled_set_id,-1)
1098 AND DECODE(x_line_type,'R',(NVL(x_rev_ind_compiled_set_id,-1)),-1)
1099 = NVL(pta.rev_ind_compiled_set_id,-1)
1100 AND DECODE(x_line_type,'R',(NVL(x_inv_ind_compiled_set_id,-1)),-1)
1101 = NVL(pta.inv_ind_compiled_set_id,-1)
1102 AND DECODE(x_line_type,'M',(NVL(x_cmt_ind_compiled_set_id,-1)),-1)
1103 = NVL(pta.cmt_ind_compiled_set_id,-1);
1104 END IF; -- IF ( x_person_id IS NOT NULL )
1105 ELSE
1106 /* Process Event Here */
1107 SELECT txn_accum_id
1108 INTO x_txn_accum_id
1109 FROM pa_txn_accum pta
1110 WHERE x_project_id = pta.project_id
1111 AND x_task_Id = pta.task_id
1112 AND x_pa_period = pta.pa_period
1113 AND x_gl_period = pta.gl_period
1114 AND x_week_ending_date = pta.week_ending_date
1115 AND x_month_ending_date = pta.month_ending_date
1116 AND x_event_type = pta.event_type
1117 AND x_event_type_classification = pta.event_type_classification
1118 AND x_organization_id = pta.organization_id
1119 AND x_revenue_category = pta.revenue_category;
1120 END IF; -- IF ( x_expenditure_type IS NOT NULL )
1121
1122 EXCEPTION
1123 WHEN NO_DATA_FOUND THEN
1124 is_row_found := 0;
1125 WHEN OTHERS THEN
1126 x_err_code := SQLCODE;
1127 RAISE;
1128 END get_txn_accum_id;
1129
1130 IF ( is_row_found = 0 ) THEN
1131 --- Could not find a row, for the given transaction attributes
1132 BEGIN
1133
1134 SELECT pa_txn_accum_s.NEXTVAL
1135 INTO x_txn_accum_Id
1136 FROM SYS.DUAL;
1137
1138 -- Insert a row in PA_TXN_ACCUM now
1139
1140 INSERT INTO PA_TXN_ACCUM (
1141 txn_accum_id,
1142 project_id,
1143 task_Id,
1144 pa_period,
1145 gl_period,
1146 week_ending_date,
1147 month_ending_date,
1148 person_id,
1149 job_id,
1150 vendor_id,
1151 expenditure_type,
1152 organization_id,
1153 non_labor_resource,
1154 non_labor_resource_org_id,
1155 expenditure_category,
1156 revenue_category,
1157 event_type,
1158 event_type_classification,
1159 system_linkage_function,
1160 cost_ind_compiled_set_id,
1161 rev_ind_compiled_set_id,
1162 inv_ind_compiled_set_id,
1163 cmt_ind_compiled_set_id,
1164 actual_cost_rollup_flag,
1165 revenue_rollup_flag,
1166 cmt_rollup_flag,
1167 creation_date,
1168 created_by,
1169 last_updated_by,
1170 last_update_date,
1171 last_update_login,
1172 request_id,
1173 program_application_id,
1174 program_id
1175 )
1176 VALUES (
1177 x_txn_accum_id,
1178 x_project_id,
1179 x_task_Id,
1180 x_pa_period,
1181 x_gl_period,
1182 x_week_ending_date,
1183 x_month_ending_date,
1184 x_person_id,
1185 x_job_id,
1186 x_vendor_id,
1187 x_expenditure_type,
1188 x_organization_id,
1189 x_non_labor_resource,
1190 x_non_labor_resource_org_id,
1191 x_expenditure_category,
1192 x_revenue_category,
1193 x_event_type,
1194 x_event_type_classification,
1195 x_system_linkage_function,
1196 x_cost_ind_compiled_set_id,
1197 x_rev_ind_compiled_set_id,
1198 x_inv_ind_compiled_set_id,
1199 x_cmt_ind_compiled_set_id,
1200 'N',
1201 'N',
1202 'N',
1203 SYSDATE,
1204 x_created_by,
1205 x_last_updated_by,
1206 SYSDATE,
1207 x_last_update_login,
1208 x_request_id,
1209 x_program_application_id,
1210 x_program_id
1211 );
1212
1213 EXCEPTION
1214 WHEN OTHERS THEN
1215 x_err_code := SQLCODE;
1216 RAISE;
1217 END;
1218 END IF;
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 x_err_code := SQLCODE;
1222 RAISE;
1223 END create_txn_accum;
1224
1225 -- This procedure deletes the pa_commitment_txns which were refreshed
1226
1227 PROCEDURE delete_cmt_txns
1228 ( x_start_project_id IN NUMBER,
1229 x_end_project_id IN NUMBER,
1230 x_start_pa_date IN DATE,
1231 x_end_pa_date IN DATE,
1232 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1233 x_err_stage IN OUT NOCOPY VARCHAR2,
1234 x_err_code IN OUT NOCOPY NUMBER)
1235
1236 IS
1237 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
1238
1239 tot_recs_processed NUMBER;
1240 /*Code Changes for Bug No.2984871 start */
1241 l_rowcount number :=0;
1242 /*Code Changes for Bug No.2984871 end */
1243
1244 BEGIN
1245 x_err_code :=0;
1246 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1247 x_err_stage := 'Deleting commitments transaction';
1248 tot_recs_processed := 0;
1249 pa_debug.debug('delete_cmt_txns: ' || x_err_stage);
1250 END IF;
1251
1252
1253 LOOP
1254 DELETE
1255 pa_commitment_txns pct
1256 WHERE
1257 pct.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id -- Commented out for bug 3736097
1258 -- System_linkage_function can be Null for commitments
1259 AND NVL(pct.system_linkage_function,'X') =
1260 NVL(NVL(x_system_linkage_function,pct.system_linkage_function),'X')
1261 AND EXISTS
1262 ( SELECT 'Yes'
1263 FROM pa_periods
1264 WHERE period_name = pct.pa_period
1265 AND start_date >= x_start_pa_date
1266 AND end_date <= x_end_pa_date
1267 )
1268 AND ROWNUM <= pa_proj_accum_main.x_commit_size;
1269 /*Code Changes for Bug No.2984871 start */
1270 l_rowcount:=sql%rowcount;
1271 /*Code Changes for Bug No.2984871 end */
1272
1273 COMMIT;
1274 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
1275 tot_recs_processed := tot_recs_processed + l_rowcount;
1276 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1277 pa_debug.debug('delete_cmt_txns: ' || 'Number of Records Commited cumulatively = '|| TO_CHAR(tot_recs_processed));
1278 END IF;
1279 /*Bug 2984871:Replaced sql%rowcount with l_rowcount */
1280 IF l_rowcount < pa_proj_accum_main.x_commit_size THEN
1281 EXIT;
1282 END IF;
1283 END LOOP;
1284
1285 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1286 pa_debug.debug('delete_cmt_txns: ' || 'Records Deleted = '||TO_CHAR(tot_recs_processed));
1287 END IF;
1288
1289 EXCEPTION
1290 WHEN OTHERS THEN
1291 x_err_code := SQLCODE;
1292 RAISE;
1293 END delete_cmt_txns;
1294
1295 -- This procedure creates the commitment txns into pa_commitment_txns
1296 -- from the view pa_commitment_txns_v
1297
1298 --
1299 -- Name: Create_Cmt_Txns
1300 --
1301 -- History
1302 --
1303 -- 04-MAR-99 jwhite Implemented lastest MC related design:
1304 -- 1) create_cmt_txns
1305 -- a. removed the rounding_limit column.
1306 -- b. specified NULL for cmt_rejection_code.
1307 -- c. specified 'N' for new generation_error_flag.
1308 -- d. removed denom and acct amount_delivered columns
1309 --
1310 --
1311 -- 30-MAY-03 jwhite As per design for Initial Grants Managment Integration
1312 -- for summarization commitment processing, modified
1313 -- the create_cmt_txns to conditionally insert
1314 -- commitment rows from a GMS object.
1315 --
1316
1317
1318 PROCEDURE create_cmt_txns
1319 ( x_start_project_id IN NUMBER,
1320 x_end_project_id IN NUMBER,
1321 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
1322 x_err_stage IN OUT NOCOPY VARCHAR2,
1323 x_err_code IN OUT NOCOPY NUMBER,
1324 x_use_tmp_table IN VARCHAR2 DEFAULT 'N' /*Added for bug 5635857*/)
1325 IS
1326
1327 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
1328
1329 l_cur_pa_period varchar2(20); /* Added for commitment change request */
1330 l_cur_gl_period varchar2(15); /* Added for commitment change request */
1331
1332 BEGIN
1333
1334 x_err_code :=0;
1335
1336 -- Grants Management Integrated Commitment Processing ---------------------
1337 -- added 30-MAY-2003, jwhite
1338
1339
1340 /*Commented for bug 4094814 IF ( PA_PROJ_ACCUM_MAIN.G_GMS_Enabled = 'Y' ) and added below if*/
1341
1342 IF ( pa_gms_api.is_sponsored_project(x_start_project_id) )
1343 THEN
1344
1345 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1346 x_err_stage := 'Creating commitment txns from Grants Management Source';
1347 pa_debug.debug('create_cmt_txns: ' || x_err_stage);
1348 END IF;
1349
1350 -- Insert Commitments from GMS Source
1351 GMS_PA_API3.create_cmt_txns
1352 (p_start_project_id => x_start_project_id
1353 , p_end_project_id => x_end_project_id
1354 , p_system_linkage_function => x_system_linkage_function
1355 ) ;
1356
1357 commit;
1358
1359 ELSE
1360
1361 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
1362 IF x_use_tmp_table = 'N' THEN /* 14457478 */
1363 x_err_stage := 'Creating commitment txns from PA_COMMITMENT_TXNS_V Source';
1364 pa_debug.debug('create_cmt_txns: ' || x_err_stage);
1365 ELSIF x_use_tmp_table = 'Y' THEN
1366 x_err_stage := 'Creating commitment txns from PA_COMMITMENT_TXNS_TMP Source';
1367 pa_debug.debug('create_cmt_txns: ' || x_err_stage);
1368 END IF; /* 14457478 */
1369 END IF;
1370
1371 -- Insert Commitments from the Oracle Projects User-Defined PA_COMMITMENT_TXNS_V
1372 -- View.
1373
1374 if (x_start_project_id is not null) then -- refresh cmts for single prj
1375
1376 /* Added for commitment change request */
1377
1378 -- l_cur_pa_period := pa_accum_utils.Get_current_pa_period;
1379 -- l_cur_gl_period := pa_accum_utils.Get_current_gl_period;
1380
1381 -- bug 3746527
1382 select
1383 per.PERIOD_NAME,
1384 per.GL_PERIOD_NAME
1385 into
1386 l_cur_pa_period,
1387 l_cur_gl_period
1388 from
1389 PA_PROJECTS_ALL prj,
1390 PA_PERIODS_ALL per
1391 where
1392 prj.PROJECT_ID = x_start_project_id and
1393 nvl(per.ORG_ID, -1) = nvl(prj.ORG_ID, -1) and
1394 per.CURRENT_PA_PERIOD_FLAG = 'Y';
1395
1396 /* End of commitment change request*/
1397 IF x_use_tmp_table='N' THEN /*Added for bug 5635857*/
1398
1399 INSERT INTO pa_commitment_txns
1400 ( CMT_LINE_ID,
1401 PROJECT_ID,
1402 TASK_ID,
1403 TRANSACTION_SOURCE,
1404 LINE_TYPE,
1405 CMT_NUMBER,
1406 CMT_DISTRIBUTION_ID,
1407 CMT_HEADER_ID,
1408 DESCRIPTION,
1409 EXPENDITURE_ITEM_DATE,
1410 PA_PERIOD,
1411 GL_PERIOD,
1412 CMT_LINE_NUMBER,
1413 CMT_CREATION_DATE,
1414 CMT_APPROVED_DATE,
1415 CMT_REQUESTOR_NAME,
1416 CMT_BUYER_NAME,
1417 CMT_APPROVED_FLAG,
1418 CMT_PROMISED_DATE,
1419 CMT_NEED_BY_DATE,
1420 ORGANIZATION_ID,
1421 VENDOR_ID,
1422 VENDOR_NAME,
1423 EXPENDITURE_TYPE,
1424 EXPENDITURE_CATEGORY,
1425 REVENUE_CATEGORY,
1426 SYSTEM_LINKAGE_FUNCTION,
1427 UNIT_OF_MEASURE,
1428 UNIT_PRICE,
1429 CMT_IND_COMPILED_SET_ID,
1430 TOT_CMT_RAW_COST,
1431 TOT_CMT_BURDENED_COST,
1432 TOT_CMT_QUANTITY,
1433 QUANTITY_ORDERED,
1434 AMOUNT_ORDERED,
1435 ORIGINAL_QUANTITY_ORDERED,
1436 ORIGINAL_AMOUNT_ORDERED,
1437 QUANTITY_CANCELLED,
1438 AMOUNT_CANCELLED,
1439 QUANTITY_DELIVERED,
1440 AMOUNT_DELIVERED,
1441 QUANTITY_INVOICED,
1442 AMOUNT_INVOICED,
1443 QUANTITY_OUTSTANDING_DELIVERY,
1444 AMOUNT_OUTSTANDING_DELIVERY,
1445 QUANTITY_OUTSTANDING_INVOICE,
1446 AMOUNT_OUTSTANDING_INVOICE,
1447 QUANTITY_OVERBILLED,
1448 AMOUNT_OVERBILLED,
1449 ORIGINAL_TXN_REFERENCE1,
1450 ORIGINAL_TXN_REFERENCE2,
1451 ORIGINAL_TXN_REFERENCE3,
1452 LAST_UPDATE_DATE,
1453 LAST_UPDATED_BY,
1454 CREATION_DATE,
1455 CREATED_BY,
1456 LAST_UPDATE_LOGIN,
1457 REQUEST_ID,
1458 PROGRAM_APPLICATION_ID,
1459 PROGRAM_ID,
1460 PROGRAM_UPDATE_DATE,
1461 BURDEN_SUM_SOURCE_RUN_ID,
1462 BURDEN_SUM_DEST_RUN_ID,
1463 BURDEN_SUM_REJECTION_CODE,
1464 acct_raw_cost,
1465 acct_burdened_cost,
1466 denom_currency_code,
1467 denom_raw_cost,
1468 denom_burdened_cost,
1469 acct_currency_code,
1470 acct_rate_date,
1471 acct_rate_type,
1472 acct_exchange_rate,
1473 receipt_currency_code,
1474 receipt_currency_amount,
1475 receipt_exchange_rate,
1476 project_currency_code,
1477 project_rate_date,
1478 project_rate_type,
1479 project_exchange_rate,
1480 generation_error_flag,
1481 cmt_rejection_code,
1482 /* added in FP.M */
1483 INVENTORY_ITEM_ID,
1484 UOM_CODE,
1485 BOM_LABOR_RESOURCE_ID,
1486 BOM_EQUIPMENT_RESOURCE_ID,
1487 RESOURCE_CLASS ,
1488 CBS_ELEMENT_ID --16461684
1489 )
1490 SELECT
1491 pa_txn_accums.cmt_line_id,
1492 pctv.project_id,
1493 pctv.task_id,
1494 pctv.transaction_source,
1495 decode(pctv.line_type,'P','P','R','R','I','I','O'),/*Bug 4050269*/
1496 pctv.cmt_number,
1497 pctv.cmt_distribution_id,
1498 pctv.cmt_header_id,
1499 pctv.description,
1500 pctv.expenditure_item_date,
1501 /* For commitment change request
1502 pctv.pa_period,
1503 pctv.gl_period, and added below variables*/
1504 l_cur_pa_period, /* Added for commitment change request*/
1505 l_cur_gl_period, /* Added for commitment change request*/
1506 pctv.cmt_line_number,
1507 pctv.cmt_creation_date,
1508 pctv.cmt_approved_date,
1509 pctv.cmt_requestor_name,
1510 pctv.cmt_buyer_name,
1511 pctv.cmt_approved_flag,
1512 pctv.cmt_promised_date,
1513 pctv.cmt_need_by_date,
1514 pctv.organization_id,
1515 pctv.vendor_id,
1516 pctv.vendor_name,
1517 pctv.expenditure_type,
1518 pctv.expenditure_category,
1519 pctv.revenue_category,
1520 pctv.system_linkage_function,
1521 pctv.unit_of_measure,
1522 pctv.unit_price,
1523 pctv.cmt_ind_compiled_set_id,
1524 TO_NUMBER(NULL),
1525 TO_NUMBER(NULL),
1526 pctv.tot_cmt_quantity,
1527 pctv.quantity_ordered,
1528 pctv.amount_ordered,
1529 pctv.original_quantity_ordered,
1530 pctv.original_amount_ordered,
1531 pctv.quantity_cancelled,
1532 pctv.amount_cancelled,
1533 pctv.quantity_delivered,
1534 TO_NUMBER(NULL),
1535 pctv.quantity_invoiced,
1536 pctv.amount_invoiced,
1537 pctv.quantity_outstanding_delivery,
1538 pctv.amount_outstanding_delivery,
1539 pctv.quantity_outstanding_invoice,
1540 pctv.amount_outstanding_invoice,
1541 pctv.quantity_overbilled,
1542 pctv.amount_overbilled,
1543 pctv.original_txn_reference1,
1544 pctv.original_txn_reference2,
1545 pctv.original_txn_reference3,
1546 SYSDATE,
1547 x_last_updated_by,
1548 SYSDATE,
1549 x_created_by,
1550 x_last_update_login,
1551 x_request_id,
1552 x_program_application_id,
1553 x_program_id,
1554 NULL,
1555 -9999,
1556 NULL,
1557 NULL,
1558 -- Bug 8848682
1559 -- pctv.acct_raw_cost,
1560 -- pctv.acct_burdened_cost,
1561 PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
1562 PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
1563 -- End bug 8848682
1564 pctv.denom_currency_code,
1565 -- Bug 8848682
1566 -- pctv.denom_raw_cost,
1567 -- pctv.denom_burdened_cost,
1568 PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
1569 PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
1570 -- End bug 8848682
1571 pctv.acct_currency_code,
1572 pctv.acct_rate_date,
1573 pctv.acct_rate_type,
1574 pctv.acct_exchange_rate,
1575 pctv.receipt_currency_code,
1576 pctv.receipt_currency_amount,
1577 pctv.receipt_exchange_rate,
1578 NULL,
1579 TO_DATE(NULL),
1580 NULL,
1581 TO_NUMBER(NULL),
1582 'N',
1583 NULL,
1584 /* added in FP.M */
1585 pctv.INVENTORY_ITEM_ID,
1586 pctv.UOM_CODE,
1587 pctv.wip_resource_id,
1588 pctv.wip_resource_id,
1589 pctv.resource_class,
1590 pctv.cbs_element_id --16461684
1591 FROM
1592 pa_commitment_txns_v pctv
1593 WHERE
1594 pctv.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
1595 AND NVL(pctv.system_linkage_function,'X') =
1596 NVL(NVL(x_system_linkage_function,pctv.system_linkage_function),'X')
1597 /* 14457478 */
1598 AND pctv.PROJECT_ID IS NOT NULL
1599 AND pctv.TASK_ID IS NOT NULL
1600 AND pctv.TRANSACTION_SOURCE IS NOT NULL
1601 AND pctv.LINE_TYPE IS NOT NULL
1602 AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
1603 AND pctv.EXPENDITURE_TYPE IS NOT NULL
1604 AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
1605 AND pctv.REVENUE_CATEGORY IS NOT NULL
1606 AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
1607 /* 14457478 */
1608
1609 ELSIF x_use_tmp_table='Y' THEN /*Start of addition for bug 5635857*/
1610 INSERT INTO pa_commitment_txns
1611 ( CMT_LINE_ID,
1612 PROJECT_ID,
1613 TASK_ID,
1614 TRANSACTION_SOURCE,
1615 LINE_TYPE,
1616 CMT_NUMBER,
1617 CMT_DISTRIBUTION_ID,
1618 CMT_HEADER_ID,
1619 DESCRIPTION,
1620 EXPENDITURE_ITEM_DATE,
1621 PA_PERIOD,
1622 GL_PERIOD,
1623 CMT_LINE_NUMBER,
1624 CMT_CREATION_DATE,
1625 CMT_APPROVED_DATE,
1626 CMT_REQUESTOR_NAME,
1627 CMT_BUYER_NAME,
1628 CMT_APPROVED_FLAG,
1629 CMT_PROMISED_DATE,
1630 CMT_NEED_BY_DATE,
1631 ORGANIZATION_ID,
1632 VENDOR_ID,
1633 VENDOR_NAME,
1634 EXPENDITURE_TYPE,
1635 EXPENDITURE_CATEGORY,
1636 REVENUE_CATEGORY,
1637 SYSTEM_LINKAGE_FUNCTION,
1638 UNIT_OF_MEASURE,
1639 UNIT_PRICE,
1640 CMT_IND_COMPILED_SET_ID,
1641 TOT_CMT_RAW_COST,
1642 TOT_CMT_BURDENED_COST,
1643 TOT_CMT_QUANTITY,
1644 QUANTITY_ORDERED,
1645 AMOUNT_ORDERED,
1646 ORIGINAL_QUANTITY_ORDERED,
1647 ORIGINAL_AMOUNT_ORDERED,
1648 QUANTITY_CANCELLED,
1649 AMOUNT_CANCELLED,
1650 QUANTITY_DELIVERED,
1651 AMOUNT_DELIVERED,
1652 QUANTITY_INVOICED,
1653 AMOUNT_INVOICED,
1654 QUANTITY_OUTSTANDING_DELIVERY,
1655 AMOUNT_OUTSTANDING_DELIVERY,
1656 QUANTITY_OUTSTANDING_INVOICE,
1657 AMOUNT_OUTSTANDING_INVOICE,
1658 QUANTITY_OVERBILLED,
1659 AMOUNT_OVERBILLED,
1660 ORIGINAL_TXN_REFERENCE1,
1661 ORIGINAL_TXN_REFERENCE2,
1662 ORIGINAL_TXN_REFERENCE3,
1663 LAST_UPDATE_DATE,
1664 LAST_UPDATED_BY,
1665 CREATION_DATE,
1666 CREATED_BY,
1667 LAST_UPDATE_LOGIN,
1668 REQUEST_ID,
1669 PROGRAM_APPLICATION_ID,
1670 PROGRAM_ID,
1671 PROGRAM_UPDATE_DATE,
1672 BURDEN_SUM_SOURCE_RUN_ID,
1673 BURDEN_SUM_DEST_RUN_ID,
1674 BURDEN_SUM_REJECTION_CODE,
1675 acct_raw_cost,
1676 acct_burdened_cost,
1677 denom_currency_code,
1678 denom_raw_cost,
1679 denom_burdened_cost,
1680 acct_currency_code,
1681 acct_rate_date,
1682 acct_rate_type,
1683 acct_exchange_rate,
1684 receipt_currency_code,
1685 receipt_currency_amount,
1686 receipt_exchange_rate,
1687 project_currency_code,
1688 project_rate_date,
1689 project_rate_type,
1690 project_exchange_rate,
1691 generation_error_flag,
1692 cmt_rejection_code,
1693 /* added in FP.M */
1694 INVENTORY_ITEM_ID,
1695 UOM_CODE,
1696 BOM_LABOR_RESOURCE_ID,
1697 BOM_EQUIPMENT_RESOURCE_ID,
1698 RESOURCE_CLASS
1699 )
1700 SELECT
1701 pa_txn_accums.cmt_line_id,
1702 pctv.project_id,
1703 pctv.task_id,
1704 pctv.transaction_source,
1705 pctv.line_type,/*Bug 4050269*/
1706 pctv.cmt_number,
1707 pctv.cmt_distribution_id,
1708 pctv.cmt_header_id,
1709 pctv.description,
1710 pctv.expenditure_item_date,
1711 /* For commitment change request
1712 pctv.pa_period,
1713 pctv.gl_period, and added below variables*/
1714 l_cur_pa_period, /* Added for commitment change request*/
1715 l_cur_gl_period, /* Added for commitment change request*/
1716 pctv.cmt_line_number,
1717 pctv.cmt_creation_date,
1718 pctv.cmt_approved_date,
1719 pctv.cmt_requestor_name,
1720 pctv.cmt_buyer_name,
1721 pctv.cmt_approved_flag,
1722 pctv.cmt_promised_date,
1723 pctv.cmt_need_by_date,
1724 pctv.organization_id,
1725 pctv.vendor_id,
1726 pctv.vendor_name,
1727 pctv.expenditure_type,
1728 pctv.expenditure_category,
1729 pctv.revenue_category,
1730 pctv.system_linkage_function,
1731 pctv.unit_of_measure,
1732 pctv.unit_price,
1733 pctv.cmt_ind_compiled_set_id,
1734 TO_NUMBER(NULL),
1735 TO_NUMBER(NULL),
1736 pctv.tot_cmt_quantity,
1737 pctv.quantity_ordered,
1738 pctv.amount_ordered,
1739 pctv.original_quantity_ordered,
1740 pctv.original_amount_ordered,
1741 pctv.quantity_cancelled,
1742 pctv.amount_cancelled,
1743 pctv.quantity_delivered,
1744 TO_NUMBER(NULL),
1745 pctv.quantity_invoiced,
1746 pctv.amount_invoiced,
1747 pctv.quantity_outstanding_delivery,
1748 pctv.amount_outstanding_delivery,
1749 pctv.quantity_outstanding_invoice,
1750 pctv.amount_outstanding_invoice,
1751 pctv.quantity_overbilled,
1752 pctv.amount_overbilled,
1753 pctv.original_txn_reference1,
1754 pctv.original_txn_reference2,
1755 pctv.original_txn_reference3,
1756 SYSDATE,
1757 x_last_updated_by,
1758 SYSDATE,
1759 x_created_by,
1760 x_last_update_login,
1761 x_request_id,
1762 x_program_application_id,
1763 x_program_id,
1764 NULL,
1765 -9999,
1766 NULL,
1767 NULL,
1768 -- Bug 8848682
1769 -- pctv.acct_raw_cost,
1770 -- pctv.acct_burdened_cost,
1771 PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
1772 PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
1773 -- End bug 8848682
1774 pctv.denom_currency_code,
1775 -- Bug 8848682
1776 -- pctv.denom_raw_cost,
1777 -- pctv.denom_burdened_cost,
1778 PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
1779 PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
1780 -- End bug 8848682
1781 pctv.acct_currency_code,
1782 pctv.acct_rate_date,
1783 pctv.acct_rate_type,
1784 pctv.acct_exchange_rate,
1785 pctv.receipt_currency_code,
1786 pctv.receipt_currency_amount,
1787 pctv.receipt_exchange_rate,
1788 NULL,
1789 TO_DATE(NULL),
1790 NULL,
1791 TO_NUMBER(NULL),
1792 'N',
1793 NULL,
1794 /* added in FP.M */
1795 pctv.INVENTORY_ITEM_ID,
1796 pctv.UOM_CODE,
1797 pctv.wip_resource_id,
1798 pctv.wip_resource_id,
1799 pctv.resource_class
1800 FROM
1801 pa_commitment_txns_tmp pctv
1802 WHERE
1803 pctv.project_id = x_start_project_id
1804 /* 14457478 */
1805 AND pctv.PROJECT_ID IS NOT NULL
1806 AND pctv.TASK_ID IS NOT NULL
1807 AND pctv.TRANSACTION_SOURCE IS NOT NULL
1808 AND pctv.LINE_TYPE IS NOT NULL
1809 AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
1810 AND pctv.EXPENDITURE_TYPE IS NOT NULL
1811 AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
1812 AND pctv.REVENUE_CATEGORY IS NOT NULL
1813 AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
1814 /* 14457478 */
1815
1816 end if; /* End of Addition for bug 5635857*/
1817
1818 commit;
1819
1820 else -- refresh commitments for all projects
1821
1822 declare
1823
1824 l_helper_batch_id number;
1825
1826 l_x number;
1827 l_project_id_1 number := null;
1828 l_project_id_2 number := null;
1829 l_project_id_3 number := null;
1830 l_project_id_4 number := null;
1831 l_project_id_5 number := null;
1832 l_project_id_6 number := null;
1833 l_project_id_7 number := null;
1834 l_project_id_8 number := null;
1835 l_project_id_9 number := null;
1836 l_project_id_10 number := null;
1837 l_project_id_11 number := null;
1838 l_project_id_12 number := null;
1839 l_project_id_13 number := null;
1840 l_project_id_14 number := null;
1841 l_project_id_15 number := null;
1842 l_project_id_16 number := null;
1843 l_project_id_17 number := null;
1844 l_project_id_18 number := null;
1845 l_project_id_19 number := null;
1846 l_project_id_20 number := null;
1847
1848 l_pa_period_1 varchar2(15) := null;
1849 l_pa_period_2 varchar2(15) := null;
1850 l_pa_period_3 varchar2(15) := null;
1851 l_pa_period_4 varchar2(15) := null;
1852 l_pa_period_5 varchar2(15) := null;
1853 l_pa_period_6 varchar2(15) := null;
1854 l_pa_period_7 varchar2(15) := null;
1855 l_pa_period_8 varchar2(15) := null;
1856 l_pa_period_9 varchar2(15) := null;
1857 l_pa_period_10 varchar2(15) := null;
1858 l_pa_period_11 varchar2(15) := null;
1859 l_pa_period_12 varchar2(15) := null;
1860 l_pa_period_13 varchar2(15) := null;
1861 l_pa_period_14 varchar2(15) := null;
1862 l_pa_period_15 varchar2(15) := null;
1863 l_pa_period_16 varchar2(15) := null;
1864 l_pa_period_17 varchar2(15) := null;
1865 l_pa_period_18 varchar2(15) := null;
1866 l_pa_period_19 varchar2(15) := null;
1867 l_pa_period_20 varchar2(15) := null;
1868
1869 l_gl_period_1 varchar2(15) := null;
1870 l_gl_period_2 varchar2(15) := null;
1871 l_gl_period_3 varchar2(15) := null;
1872 l_gl_period_4 varchar2(15) := null;
1873 l_gl_period_5 varchar2(15) := null;
1874 l_gl_period_6 varchar2(15) := null;
1875 l_gl_period_7 varchar2(15) := null;
1876 l_gl_period_8 varchar2(15) := null;
1877 l_gl_period_9 varchar2(15) := null;
1878 l_gl_period_10 varchar2(15) := null;
1879 l_gl_period_11 varchar2(15) := null;
1880 l_gl_period_12 varchar2(15) := null;
1881 l_gl_period_13 varchar2(15) := null;
1882 l_gl_period_14 varchar2(15) := null;
1883 l_gl_period_15 varchar2(15) := null;
1884 l_gl_period_16 varchar2(15) := null;
1885 l_gl_period_17 varchar2(15) := null;
1886 l_gl_period_18 varchar2(15) := null;
1887 l_gl_period_19 varchar2(15) := null;
1888 l_gl_period_20 varchar2(15) := null;
1889
1890 begin
1891
1892 l_helper_batch_id := x_end_project_id; -- overload of to_proj param
1893
1894 l_x := 1;
1895
1896 for c in (select PROJECT_ID,
1897 PA_PERIOD_NAME,
1898 GL_PERIOD_NAME
1899 from PJI_FM_EXTR_DREVN -- overload of drev table for cmt
1900 where BATCH_ID = l_helper_batch_id) loop
1901
1902 if (l_x = 1) then
1903 l_project_id_1 := c.PROJECT_ID;
1904 l_pa_period_1 := c.PA_PERIOD_NAME;
1905 l_gl_period_1 := c.GL_PERIOD_NAME;
1906 elsif (l_x = 2) then
1907 l_project_id_2 := c.PROJECT_ID;
1908 l_pa_period_2 := c.PA_PERIOD_NAME;
1909 l_gl_period_2 := c.GL_PERIOD_NAME;
1910 elsif (l_x = 3) then
1911 l_project_id_3 := c.PROJECT_ID;
1912 l_pa_period_3 := c.PA_PERIOD_NAME;
1913 l_gl_period_3 := c.GL_PERIOD_NAME;
1914 elsif (l_x = 4) then
1915 l_project_id_4 := c.PROJECT_ID;
1916 l_pa_period_4 := c.PA_PERIOD_NAME;
1917 l_gl_period_4 := c.GL_PERIOD_NAME;
1918 elsif (l_x = 5) then
1919 l_project_id_5 := c.PROJECT_ID;
1920 l_pa_period_5 := c.PA_PERIOD_NAME;
1921 l_gl_period_5 := c.GL_PERIOD_NAME;
1922 elsif (l_x = 6) then
1923 l_project_id_6 := c.PROJECT_ID;
1924 l_pa_period_6 := c.PA_PERIOD_NAME;
1925 l_gl_period_6 := c.GL_PERIOD_NAME;
1926 elsif (l_x = 7) then
1927 l_project_id_7 := c.PROJECT_ID;
1928 l_pa_period_7 := c.PA_PERIOD_NAME;
1929 l_gl_period_7 := c.GL_PERIOD_NAME;
1930 elsif (l_x = 8) then
1931 l_project_id_8 := c.PROJECT_ID;
1932 l_pa_period_8 := c.PA_PERIOD_NAME;
1933 l_gl_period_8 := c.GL_PERIOD_NAME;
1934 elsif (l_x = 9) then
1935 l_project_id_9 := c.PROJECT_ID;
1936 l_pa_period_9 := c.PA_PERIOD_NAME;
1937 l_gl_period_9 := c.GL_PERIOD_NAME;
1938 elsif (l_x = 10) then
1939 l_project_id_10 := c.PROJECT_ID;
1940 l_pa_period_10 := c.PA_PERIOD_NAME;
1941 l_gl_period_10 := c.GL_PERIOD_NAME;
1942 elsif (l_x = 11) then
1943 l_project_id_11 := c.PROJECT_ID;
1944 l_pa_period_11 := c.PA_PERIOD_NAME;
1945 l_gl_period_11 := c.GL_PERIOD_NAME;
1946 elsif (l_x = 12) then
1947 l_project_id_12 := c.PROJECT_ID;
1948 l_pa_period_12 := c.PA_PERIOD_NAME;
1949 l_gl_period_12 := c.GL_PERIOD_NAME;
1950 elsif (l_x = 13) then
1951 l_project_id_13 := c.PROJECT_ID;
1952 l_pa_period_13 := c.PA_PERIOD_NAME;
1953 l_gl_period_13 := c.GL_PERIOD_NAME;
1954 elsif (l_x = 14) then
1955 l_project_id_14 := c.PROJECT_ID;
1956 l_pa_period_14 := c.PA_PERIOD_NAME;
1957 l_gl_period_14 := c.GL_PERIOD_NAME;
1958 elsif (l_x = 15) then
1959 l_project_id_15 := c.PROJECT_ID;
1960 l_pa_period_15 := c.PA_PERIOD_NAME;
1961 l_gl_period_15 := c.GL_PERIOD_NAME;
1962 elsif (l_x = 16) then
1963 l_project_id_16 := c.PROJECT_ID;
1964 l_pa_period_16 := c.PA_PERIOD_NAME;
1965 l_gl_period_16 := c.GL_PERIOD_NAME;
1966 elsif (l_x = 17) then
1967 l_project_id_17 := c.PROJECT_ID;
1968 l_pa_period_17 := c.PA_PERIOD_NAME;
1969 l_gl_period_17 := c.GL_PERIOD_NAME;
1970 elsif (l_x = 18) then
1971 l_project_id_18 := c.PROJECT_ID;
1972 l_pa_period_18 := c.PA_PERIOD_NAME;
1973 l_gl_period_18 := c.GL_PERIOD_NAME;
1974 elsif (l_x = 19) then
1975 l_project_id_19 := c.PROJECT_ID;
1976 l_pa_period_19 := c.PA_PERIOD_NAME;
1977 l_gl_period_19 := c.GL_PERIOD_NAME;
1978 elsif (l_x = 20) then
1979 l_project_id_20 := c.PROJECT_ID;
1980 l_pa_period_20 := c.PA_PERIOD_NAME;
1981 l_gl_period_20 := c.GL_PERIOD_NAME;
1982 else
1983 dbms_standard.raise_application_error(-20010, 'batch too large');
1984 end if;
1985
1986 l_x := l_x + 1;
1987
1988 end loop;
1989
1990 IF x_use_tmp_table='N' THEN /*Added for bug 5635857*/
1991
1992 insert into PA_COMMITMENT_TXNS
1993 (
1994 CMT_LINE_ID,
1995 PROJECT_ID,
1996 TASK_ID,
1997 TRANSACTION_SOURCE,
1998 LINE_TYPE,
1999 CMT_NUMBER,
2000 CMT_DISTRIBUTION_ID,
2001 CMT_HEADER_ID,
2002 DESCRIPTION,
2003 EXPENDITURE_ITEM_DATE,
2004 PA_PERIOD,
2005 GL_PERIOD,
2006 CMT_LINE_NUMBER,
2007 CMT_CREATION_DATE,
2008 CMT_APPROVED_DATE,
2009 CMT_REQUESTOR_NAME,
2010 CMT_BUYER_NAME,
2011 CMT_APPROVED_FLAG,
2012 CMT_PROMISED_DATE,
2013 CMT_NEED_BY_DATE,
2014 ORGANIZATION_ID,
2015 VENDOR_ID,
2016 VENDOR_NAME,
2017 EXPENDITURE_TYPE,
2018 EXPENDITURE_CATEGORY,
2019 REVENUE_CATEGORY,
2020 SYSTEM_LINKAGE_FUNCTION,
2021 UNIT_OF_MEASURE,
2022 UNIT_PRICE,
2023 CMT_IND_COMPILED_SET_ID,
2024 TOT_CMT_RAW_COST,
2025 TOT_CMT_BURDENED_COST,
2026 TOT_CMT_QUANTITY,
2027 QUANTITY_ORDERED,
2028 AMOUNT_ORDERED,
2029 ORIGINAL_QUANTITY_ORDERED,
2030 ORIGINAL_AMOUNT_ORDERED,
2031 QUANTITY_CANCELLED,
2032 AMOUNT_CANCELLED,
2033 QUANTITY_DELIVERED,
2034 AMOUNT_DELIVERED,
2035 QUANTITY_INVOICED,
2036 AMOUNT_INVOICED,
2037 QUANTITY_OUTSTANDING_DELIVERY,
2038 AMOUNT_OUTSTANDING_DELIVERY,
2039 QUANTITY_OUTSTANDING_INVOICE,
2040 AMOUNT_OUTSTANDING_INVOICE,
2041 QUANTITY_OVERBILLED,
2042 AMOUNT_OVERBILLED,
2043 ORIGINAL_TXN_REFERENCE1,
2044 ORIGINAL_TXN_REFERENCE2,
2045 ORIGINAL_TXN_REFERENCE3,
2046 LAST_UPDATE_DATE,
2047 LAST_UPDATED_BY,
2048 CREATION_DATE,
2049 CREATED_BY,
2050 LAST_UPDATE_LOGIN,
2051 REQUEST_ID,
2052 PROGRAM_APPLICATION_ID,
2053 PROGRAM_ID,
2054 PROGRAM_UPDATE_DATE,
2055 BURDEN_SUM_SOURCE_RUN_ID,
2056 BURDEN_SUM_DEST_RUN_ID,
2057 BURDEN_SUM_REJECTION_CODE,
2058 ACCT_RAW_COST,
2059 ACCT_BURDENED_COST,
2060 DENOM_CURRENCY_CODE,
2061 DENOM_RAW_COST,
2062 DENOM_BURDENED_COST,
2063 ACCT_CURRENCY_CODE,
2064 ACCT_RATE_DATE,
2065 ACCT_RATE_TYPE,
2066 ACCT_EXCHANGE_RATE,
2067 RECEIPT_CURRENCY_CODE,
2068 RECEIPT_CURRENCY_AMOUNT,
2069 RECEIPT_EXCHANGE_RATE,
2070 PROJECT_CURRENCY_CODE,
2071 PROJECT_RATE_DATE,
2072 PROJECT_RATE_TYPE,
2073 PROJECT_EXCHANGE_RATE,
2074 GENERATION_ERROR_FLAG,
2075 CMT_REJECTION_CODE,
2076 INVENTORY_ITEM_ID,
2077 UOM_CODE,
2078 BOM_LABOR_RESOURCE_ID,
2079 BOM_EQUIPMENT_RESOURCE_ID,
2080 RESOURCE_CLASS,
2081 CBS_ELEMENT_ID --16461684
2082 )
2083 select /*+ push_pred(pctv) */
2084 PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
2085 pctv.PROJECT_ID,
2086 pctv.TASK_ID,
2087 pctv.TRANSACTION_SOURCE,
2088 decode(pctv.LINE_TYPE,
2089 'P', 'P',
2090 'R', 'R',
2091 'I', 'I',
2092 'O') LINE_TYPE,
2093 pctv.CMT_NUMBER,
2094 pctv.CMT_DISTRIBUTION_ID,
2095 pctv.CMT_HEADER_ID,
2096 pctv.DESCRIPTION,
2097 pctv.EXPENDITURE_ITEM_DATE,
2098 decode(pctv.PROJECT_ID,
2099 l_project_id_1, l_pa_period_1,
2100 l_project_id_2, l_pa_period_2,
2101 l_project_id_3, l_pa_period_3,
2102 l_project_id_4, l_pa_period_4,
2103 l_project_id_5, l_pa_period_5,
2104 l_project_id_6, l_pa_period_6,
2105 l_project_id_7, l_pa_period_7,
2106 l_project_id_8, l_pa_period_8,
2107 l_project_id_9, l_pa_period_9,
2108 l_project_id_10, l_pa_period_10,
2109 l_project_id_11, l_pa_period_11,
2110 l_project_id_12, l_pa_period_12,
2111 l_project_id_13, l_pa_period_13,
2112 l_project_id_14, l_pa_period_14,
2113 l_project_id_15, l_pa_period_15,
2114 l_project_id_16, l_pa_period_16,
2115 l_project_id_17, l_pa_period_17,
2116 l_project_id_18, l_pa_period_18,
2117 l_project_id_19, l_pa_period_19,
2118 l_project_id_20, l_pa_period_20) PA_PERIOD,
2119 decode(pctv.PROJECT_ID,
2120 l_project_id_1, l_gl_period_1,
2121 l_project_id_2, l_gl_period_2,
2122 l_project_id_3, l_gl_period_3,
2123 l_project_id_4, l_gl_period_4,
2124 l_project_id_5, l_gl_period_5,
2125 l_project_id_6, l_gl_period_6,
2126 l_project_id_7, l_gl_period_7,
2127 l_project_id_8, l_gl_period_8,
2128 l_project_id_9, l_gl_period_9,
2129 l_project_id_10, l_gl_period_10,
2130 l_project_id_11, l_gl_period_11,
2131 l_project_id_12, l_gl_period_12,
2132 l_project_id_13, l_gl_period_13,
2133 l_project_id_14, l_gl_period_14,
2134 l_project_id_15, l_gl_period_15,
2135 l_project_id_16, l_gl_period_16,
2136 l_project_id_17, l_gl_period_17,
2137 l_project_id_18, l_gl_period_18,
2138 l_project_id_19, l_gl_period_19,
2139 l_project_id_20, l_gl_period_20) GL_PERIOD,
2140 pctv.CMT_LINE_NUMBER,
2141 pctv.CMT_CREATION_DATE,
2142 pctv.CMT_APPROVED_DATE,
2143 pctv.CMT_REQUESTOR_NAME,
2144 pctv.CMT_BUYER_NAME,
2145 pctv.CMT_APPROVED_FLAG,
2146 pctv.CMT_PROMISED_DATE,
2147 pctv.CMT_NEED_BY_DATE,
2148 pctv.ORGANIZATION_ID,
2149 pctv.VENDOR_ID,
2150 pctv.VENDOR_NAME,
2151 pctv.EXPENDITURE_TYPE,
2152 pctv.EXPENDITURE_CATEGORY,
2153 pctv.REVENUE_CATEGORY,
2154 pctv.SYSTEM_LINKAGE_FUNCTION,
2155 pctv.UNIT_OF_MEASURE,
2156 pctv.UNIT_PRICE,
2157 pctv.CMT_IND_COMPILED_SET_ID,
2158 to_number(null) TOT_CMT_RAW_COST,
2159 to_number(null) TOT_CMT_BURDENED_COST,
2160 pctv.TOT_CMT_QUANTITY,
2161 pctv.QUANTITY_ORDERED,
2162 pctv.AMOUNT_ORDERED,
2163 pctv.ORIGINAL_QUANTITY_ORDERED,
2164 pctv.ORIGINAL_AMOUNT_ORDERED,
2165 pctv.QUANTITY_CANCELLED,
2166 pctv.AMOUNT_CANCELLED,
2167 pctv.QUANTITY_DELIVERED,
2168 to_number(null) AMOUNT_DELIVERED,
2169 pctv.QUANTITY_INVOICED,
2170 pctv.AMOUNT_INVOICED,
2171 pctv.QUANTITY_OUTSTANDING_DELIVERY,
2172 pctv.AMOUNT_OUTSTANDING_DELIVERY,
2173 pctv.QUANTITY_OUTSTANDING_INVOICE,
2174 pctv.AMOUNT_OUTSTANDING_INVOICE,
2175 pctv.QUANTITY_OVERBILLED,
2176 pctv.AMOUNT_OVERBILLED,
2177 pctv.ORIGINAL_TXN_REFERENCE1,
2178 pctv.ORIGINAL_TXN_REFERENCE2,
2179 pctv.ORIGINAL_TXN_REFERENCE3,
2180 sysdate LAST_UPDATE_DATE,
2181 x_last_updated_by LAST_UPDATED_BY,
2182 sysdate CREATION_DATE,
2183 x_created_by CREATED_BY,
2184 x_last_update_login LAST_UPDATE_LOGIN,
2185 x_request_id REQUEST_ID,
2186 x_program_application_id PROGRAM_APPLICATION_ID,
2187 x_program_id PROGRAM_ID,
2188 null PROGRAM_UPDATE_DATE,
2189 -9999 BURDEN_SUM_SOURCE_RUN_ID,
2190 null BURDEN_SUM_DEST_RUN_ID,
2191 null BURDEN_SUM_REJECTION_CODE,
2192 -- Bug 8848682
2193 -- pctv.acct_raw_cost,
2194 -- pctv.acct_burdened_cost,
2195 PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
2196 PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
2197 -- End bug 8848682
2198 pctv.denom_currency_code,
2199 -- Bug 8848682
2200 -- pctv.denom_raw_cost,
2201 -- pctv.denom_burdened_cost,
2202 PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
2203 PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
2204 -- End bug 8848682
2205 pctv.ACCT_CURRENCY_CODE,
2206 pctv.ACCT_RATE_DATE,
2207 pctv.ACCT_RATE_TYPE,
2208 pctv.ACCT_EXCHANGE_RATE,
2209 pctv.RECEIPT_CURRENCY_CODE,
2210 pctv.RECEIPT_CURRENCY_AMOUNT,
2211 pctv.RECEIPT_EXCHANGE_RATE,
2212 null PROJECT_CURRENCY_CODE,
2213 to_date(null) PROJECT_RATE_DATE,
2214 null PROJECT_RATE_TYPE,
2215 to_number(null) PROJECT_EXCHANGE_RATE,
2216 'N' GENERATION_ERROR_FLAG,
2217 null CMT_REJECTION_CODE,
2218 pctv.INVENTORY_ITEM_ID,
2219 pctv.UOM_CODE,
2220 pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
2221 pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
2222 pctv.RESOURCE_CLASS,
2223 pctv.CBS_ELEMENT_ID --16461684
2224 from
2225 PA_COMMITMENT_TXNS_V pctv
2226 where
2227 pctv.PROJECT_ID = l_project_id_1
2228 /* 14457478 */
2229 AND pctv.PROJECT_ID IS NOT NULL
2230 AND pctv.TASK_ID IS NOT NULL
2231 AND pctv.TRANSACTION_SOURCE IS NOT NULL
2232 AND pctv.LINE_TYPE IS NOT NULL
2233 AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
2234 AND pctv.EXPENDITURE_TYPE IS NOT NULL
2235 AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
2236 AND pctv.REVENUE_CATEGORY IS NOT NULL
2237 AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
2238 /* 14457478 */
2239
2240 /* single project batch
2241
2242 pctv.PROJECT_ID in (l_project_id_1,
2243 l_project_id_2,
2244 l_project_id_3,
2245 l_project_id_4,
2246 l_project_id_5,
2247 l_project_id_6,
2248 l_project_id_7,
2249 l_project_id_8,
2250 l_project_id_9,
2251 l_project_id_10,
2252 l_project_id_11,
2253 l_project_id_12,
2254 l_project_id_13,
2255 l_project_id_14,
2256 l_project_id_15,
2257 l_project_id_16,
2258 l_project_id_17,
2259 l_project_id_18,
2260 l_project_id_19,
2261 l_project_id_20);
2262 */
2263
2264 /* Start of Addition for bug 5635857*/
2265 ELSIF x_use_tmp_table='Y' THEN
2266 insert into PA_COMMITMENT_TXNS
2267 (
2268 CMT_LINE_ID,
2269 PROJECT_ID,
2270 TASK_ID,
2271 TRANSACTION_SOURCE,
2272 LINE_TYPE,
2273 CMT_NUMBER,
2274 CMT_DISTRIBUTION_ID,
2275 CMT_HEADER_ID,
2276 DESCRIPTION,
2277 EXPENDITURE_ITEM_DATE,
2278 PA_PERIOD,
2279 GL_PERIOD,
2280 CMT_LINE_NUMBER,
2281 CMT_CREATION_DATE,
2282 CMT_APPROVED_DATE,
2283 CMT_REQUESTOR_NAME,
2284 CMT_BUYER_NAME,
2285 CMT_APPROVED_FLAG,
2286 CMT_PROMISED_DATE,
2287 CMT_NEED_BY_DATE,
2288 ORGANIZATION_ID,
2289 VENDOR_ID,
2290 VENDOR_NAME,
2291 EXPENDITURE_TYPE,
2292 EXPENDITURE_CATEGORY,
2293 REVENUE_CATEGORY,
2294 SYSTEM_LINKAGE_FUNCTION,
2295 UNIT_OF_MEASURE,
2296 UNIT_PRICE,
2297 CMT_IND_COMPILED_SET_ID,
2298 TOT_CMT_RAW_COST,
2299 TOT_CMT_BURDENED_COST,
2300 TOT_CMT_QUANTITY,
2301 QUANTITY_ORDERED,
2302 AMOUNT_ORDERED,
2303 ORIGINAL_QUANTITY_ORDERED,
2304 ORIGINAL_AMOUNT_ORDERED,
2305 QUANTITY_CANCELLED,
2306 AMOUNT_CANCELLED,
2307 QUANTITY_DELIVERED,
2308 AMOUNT_DELIVERED,
2309 QUANTITY_INVOICED,
2310 AMOUNT_INVOICED,
2311 QUANTITY_OUTSTANDING_DELIVERY,
2312 AMOUNT_OUTSTANDING_DELIVERY,
2313 QUANTITY_OUTSTANDING_INVOICE,
2314 AMOUNT_OUTSTANDING_INVOICE,
2315 QUANTITY_OVERBILLED,
2316 AMOUNT_OVERBILLED,
2317 ORIGINAL_TXN_REFERENCE1,
2318 ORIGINAL_TXN_REFERENCE2,
2319 ORIGINAL_TXN_REFERENCE3,
2320 LAST_UPDATE_DATE,
2321 LAST_UPDATED_BY,
2322 CREATION_DATE,
2323 CREATED_BY,
2324 LAST_UPDATE_LOGIN,
2325 REQUEST_ID,
2326 PROGRAM_APPLICATION_ID,
2327 PROGRAM_ID,
2328 PROGRAM_UPDATE_DATE,
2329 BURDEN_SUM_SOURCE_RUN_ID,
2330 BURDEN_SUM_DEST_RUN_ID,
2331 BURDEN_SUM_REJECTION_CODE,
2332 ACCT_RAW_COST,
2333 ACCT_BURDENED_COST,
2334 DENOM_CURRENCY_CODE,
2335 DENOM_RAW_COST,
2336 DENOM_BURDENED_COST,
2337 ACCT_CURRENCY_CODE,
2338 ACCT_RATE_DATE,
2339 ACCT_RATE_TYPE,
2340 ACCT_EXCHANGE_RATE,
2341 RECEIPT_CURRENCY_CODE,
2342 RECEIPT_CURRENCY_AMOUNT,
2343 RECEIPT_EXCHANGE_RATE,
2344 PROJECT_CURRENCY_CODE,
2345 PROJECT_RATE_DATE,
2346 PROJECT_RATE_TYPE,
2347 PROJECT_EXCHANGE_RATE,
2348 GENERATION_ERROR_FLAG,
2349 CMT_REJECTION_CODE,
2350 INVENTORY_ITEM_ID,
2351 UOM_CODE,
2352 BOM_LABOR_RESOURCE_ID,
2353 BOM_EQUIPMENT_RESOURCE_ID,
2354 RESOURCE_CLASS
2355 )
2356 select
2357 PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
2358 pctv.PROJECT_ID,
2359 pctv.TASK_ID,
2360 pctv.TRANSACTION_SOURCE,
2361 pctv.LINE_TYPE,
2362 pctv.CMT_NUMBER,
2363 pctv.CMT_DISTRIBUTION_ID,
2364 pctv.CMT_HEADER_ID,
2365 pctv.DESCRIPTION,
2366 pctv.EXPENDITURE_ITEM_DATE,
2367 decode(pctv.PROJECT_ID,
2368 l_project_id_1, l_pa_period_1,
2369 l_project_id_2, l_pa_period_2,
2370 l_project_id_3, l_pa_period_3,
2371 l_project_id_4, l_pa_period_4,
2372 l_project_id_5, l_pa_period_5,
2373 l_project_id_6, l_pa_period_6,
2374 l_project_id_7, l_pa_period_7,
2375 l_project_id_8, l_pa_period_8,
2376 l_project_id_9, l_pa_period_9,
2377 l_project_id_10, l_pa_period_10,
2378 l_project_id_11, l_pa_period_11,
2379 l_project_id_12, l_pa_period_12,
2380 l_project_id_13, l_pa_period_13,
2381 l_project_id_14, l_pa_period_14,
2382 l_project_id_15, l_pa_period_15,
2383 l_project_id_16, l_pa_period_16,
2384 l_project_id_17, l_pa_period_17,
2385 l_project_id_18, l_pa_period_18,
2386 l_project_id_19, l_pa_period_19,
2387 l_project_id_20, l_pa_period_20) PA_PERIOD,
2388 decode(pctv.PROJECT_ID,
2389 l_project_id_1, l_gl_period_1,
2390 l_project_id_2, l_gl_period_2,
2391 l_project_id_3, l_gl_period_3,
2392 l_project_id_4, l_gl_period_4,
2393 l_project_id_5, l_gl_period_5,
2394 l_project_id_6, l_gl_period_6,
2395 l_project_id_7, l_gl_period_7,
2396 l_project_id_8, l_gl_period_8,
2397 l_project_id_9, l_gl_period_9,
2398 l_project_id_10, l_gl_period_10,
2399 l_project_id_11, l_gl_period_11,
2400 l_project_id_12, l_gl_period_12,
2401 l_project_id_13, l_gl_period_13,
2402 l_project_id_14, l_gl_period_14,
2403 l_project_id_15, l_gl_period_15,
2404 l_project_id_16, l_gl_period_16,
2405 l_project_id_17, l_gl_period_17,
2406 l_project_id_18, l_gl_period_18,
2407 l_project_id_19, l_gl_period_19,
2408 l_project_id_20, l_gl_period_20) GL_PERIOD,
2409 pctv.CMT_LINE_NUMBER,
2410 pctv.CMT_CREATION_DATE,
2411 pctv.CMT_APPROVED_DATE,
2412 pctv.CMT_REQUESTOR_NAME,
2413 pctv.CMT_BUYER_NAME,
2414 pctv.CMT_APPROVED_FLAG,
2415 pctv.CMT_PROMISED_DATE,
2416 pctv.CMT_NEED_BY_DATE,
2417 pctv.ORGANIZATION_ID,
2418 pctv.VENDOR_ID,
2419 pctv.VENDOR_NAME,
2420 pctv.EXPENDITURE_TYPE,
2421 pctv.EXPENDITURE_CATEGORY,
2422 pctv.REVENUE_CATEGORY,
2423 pctv.SYSTEM_LINKAGE_FUNCTION,
2424 pctv.UNIT_OF_MEASURE,
2425 pctv.UNIT_PRICE,
2426 pctv.CMT_IND_COMPILED_SET_ID,
2427 to_number(null) TOT_CMT_RAW_COST,
2428 to_number(null) TOT_CMT_BURDENED_COST,
2429 pctv.TOT_CMT_QUANTITY,
2430 pctv.QUANTITY_ORDERED,
2431 pctv.AMOUNT_ORDERED,
2432 pctv.ORIGINAL_QUANTITY_ORDERED,
2433 pctv.ORIGINAL_AMOUNT_ORDERED,
2434 pctv.QUANTITY_CANCELLED,
2435 pctv.AMOUNT_CANCELLED,
2436 pctv.QUANTITY_DELIVERED,
2437 to_number(null) AMOUNT_DELIVERED,
2438 pctv.QUANTITY_INVOICED,
2439 pctv.AMOUNT_INVOICED,
2440 pctv.QUANTITY_OUTSTANDING_DELIVERY,
2441 pctv.AMOUNT_OUTSTANDING_DELIVERY,
2442 pctv.QUANTITY_OUTSTANDING_INVOICE,
2443 pctv.AMOUNT_OUTSTANDING_INVOICE,
2444 pctv.QUANTITY_OVERBILLED,
2445 pctv.AMOUNT_OVERBILLED,
2446 pctv.ORIGINAL_TXN_REFERENCE1,
2447 pctv.ORIGINAL_TXN_REFERENCE2,
2448 pctv.ORIGINAL_TXN_REFERENCE3,
2449 sysdate LAST_UPDATE_DATE,
2450 x_last_updated_by LAST_UPDATED_BY,
2451 sysdate CREATION_DATE,
2452 x_created_by CREATED_BY,
2453 x_last_update_login LAST_UPDATE_LOGIN,
2454 x_request_id REQUEST_ID,
2455 x_program_application_id PROGRAM_APPLICATION_ID,
2456 x_program_id PROGRAM_ID,
2457 null PROGRAM_UPDATE_DATE,
2458 -9999 BURDEN_SUM_SOURCE_RUN_ID,
2459 null BURDEN_SUM_DEST_RUN_ID,
2460 null BURDEN_SUM_REJECTION_CODE,
2461 -- Bug 8848682
2462 -- pctv.acct_raw_cost,
2463 -- pctv.acct_burdened_cost,
2464 PA_CURRENCY.round_trans_currency_amt(pctv.acct_raw_cost, pctv.acct_currency_code) acct_raw_cost,
2465 PA_CURRENCY.round_trans_currency_amt(pctv.acct_burdened_cost, pctv.acct_currency_code) acct_burdened_cost,
2466 -- End bug 8848682
2467 pctv.denom_currency_code,
2468 -- Bug 8848682
2469 -- pctv.denom_raw_cost,
2470 -- pctv.denom_burdened_cost,
2471 PA_CURRENCY.round_trans_currency_amt(pctv.denom_raw_cost, pctv.denom_currency_code) denom_raw_cost,
2472 PA_CURRENCY.round_trans_currency_amt(pctv.denom_burdened_cost, pctv.denom_currency_code) denom_burdened_cost,
2473 -- End bug 8848682
2474 pctv.ACCT_CURRENCY_CODE,
2475 pctv.ACCT_RATE_DATE,
2476 pctv.ACCT_RATE_TYPE,
2477 pctv.ACCT_EXCHANGE_RATE,
2478 pctv.RECEIPT_CURRENCY_CODE,
2479 pctv.RECEIPT_CURRENCY_AMOUNT,
2480 pctv.RECEIPT_EXCHANGE_RATE,
2481 null PROJECT_CURRENCY_CODE,
2482 to_date(null) PROJECT_RATE_DATE,
2483 null PROJECT_RATE_TYPE,
2484 to_number(null) PROJECT_EXCHANGE_RATE,
2485 'N' GENERATION_ERROR_FLAG,
2486 null CMT_REJECTION_CODE,
2487 pctv.INVENTORY_ITEM_ID,
2488 pctv.UOM_CODE,
2489 pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
2490 pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
2491 pctv.RESOURCE_CLASS
2492 from
2493 pa_commitment_txns_tmp pctv
2494 WHERE
2495 pctv.PROJECT_ID = l_project_id_1
2496 /* 14457478 */
2497 AND pctv.PROJECT_ID IS NOT NULL
2498 AND pctv.TASK_ID IS NOT NULL
2499 AND pctv.TRANSACTION_SOURCE IS NOT NULL
2500 AND pctv.LINE_TYPE IS NOT NULL
2501 AND pctv.EXPENDITURE_ITEM_DATE IS NOT NULL
2502 AND pctv.EXPENDITURE_TYPE IS NOT NULL
2503 AND pctv.EXPENDITURE_CATEGORY IS NOT NULL
2504 AND pctv.REVENUE_CATEGORY IS NOT NULL
2505 AND pctv.SYSTEM_LINKAGE_FUNCTION IS NOT NULL ;
2506 /* 14457478 */
2507 end if;
2508 /* End of Addition for bug 5635857*/
2509
2510 end;
2511
2512 end if;
2513
2514 END IF; -- ( PA_PROJ_ACCUM_MAIN.G_GMS_Enabled = 'Y'
2515
2516 -- End: Grants Management Integrated Commitment Processing ---------------------
2517
2518
2519 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2520 pa_debug.debug('create_cmt_txns: ' || 'Records Inserted = '||TO_CHAR(SQL%ROWCOUNT));
2521 END IF;
2522
2523 EXCEPTION
2524 WHEN OTHERS THEN
2525 x_err_code := SQLCODE;
2526 RAISE;
2527
2528 END create_cmt_txns;
2529
2530 -- This procedure accumulates the actual cost txn in the given
2531 -- txn_accum_id. The quantity figures are accumulated in
2532 -- either tot_quantity or tot_labor_hours column on
2533 -- pa_txn_accum depending if the UNIT_OF_MEASURE is 'HOURS'
2534 -- We are assuming that for a given expenditure type we
2535 -- can have only one and only one UNIT_OF_MEASURE
2536 -- Since expenditure types is one of the transaction attributes
2537 -- for a row in pa_txn_accum it will have only one and only one
2538 -- value for UNIT_OF_MEASURE
2539
2540 /* Start of Code changes for bug 13448783 */
2541 PROCEDURE accum_act_txn
2542 ( x_txn_accum_id_tb2 IN x_txn_accum_id_tb,
2543 x_tot_raw_cost_tb2 IN x_tot_raw_cost_tb,
2544 x_tot_burdened_cost_tb2 IN x_tot_burdened_cost_tb,
2545 x_tot_quantity_tb2 IN x_tot_quantity_tb,
2546 x_tot_billable_raw_cost_tb2 IN x_tot_billable_raw_cost_tb,
2547 x_tot_billable_brdn_cost_tb2 IN x_tot_billable_brdn_cost_tb,
2548 x_tot_billable_quantity_tb2 IN x_tot_billable_quantity_tb,
2549 x_unit_of_measure_tb2 IN x_unit_of_measure_tb,
2550 x_err_stage IN OUT NOCOPY VARCHAR2,
2551 x_err_code IN OUT NOCOPY NUMBER)
2552 IS
2553 BEGIN
2554 x_err_code :=0;
2555
2556 -- for actual costs x_unit_of_measure will always be not null
2557 -- If the transactions are labor transactions than add them to
2558 -- to labor_hours and billable_labor_hours as well
2559
2560 x_err_stage := 'Accumulating Actual Cost transaction';
2561
2562 If x_txn_accum_id_tb2.COUNT > 0 THEN
2563 FORALL i in x_txn_accum_id_tb2.FIRST .. x_txn_accum_id_tb2.LAST
2564
2565 UPDATE pa_txn_accum pta
2566 SET pta.i_tot_raw_cost = DECODE(raw_cost_flag,'Y',
2567 (NVL(i_tot_raw_cost, 0) + x_tot_raw_cost_tb2(i)),
2568 NULL),
2569 pta.i_tot_burdened_cost = DECODE(burdened_cost_flag,'Y',
2570 (NVL(i_tot_burdened_cost, 0) +
2571 x_tot_burdened_cost_tb2(i)),NULL),
2572 pta.i_tot_quantity = DECODE(quantity_flag,'Y',
2573 (NVL(i_tot_quantity, 0) +
2574 x_tot_quantity_tb2(i)),NULL),
2575 pta.i_tot_labor_hours = DECODE(labor_hours_flag,'Y',
2576 (NVL(i_tot_labor_hours,0) +
2577 DECODE(pta.system_linkage_function,
2578 'OT',
2579 x_tot_quantity_tb2(i),
2580 'ST',
2581 x_tot_quantity_tb2(i),
2582 0)),NULL),
2583 pta.i_tot_billable_raw_cost = DECODE(billable_raw_cost_flag,'Y',
2584 (NVL(i_tot_billable_raw_cost, 0) +
2585 x_tot_billable_raw_cost_tb2(i)),NULL),
2586 pta.i_tot_billable_burdened_cost = DECODE(billable_burdened_cost_flag,'Y',
2587 (NVL(i_tot_billable_burdened_cost, 0) +
2588 x_tot_billable_brdn_cost_tb2(i)),NULL),
2589 pta.i_tot_billable_quantity = DECODE(billable_quantity_flag,'Y',
2590 (NVL(i_tot_billable_quantity, 0) +
2591 x_tot_billable_quantity_tb2(i)),NULL),
2592 pta.i_tot_billable_labor_hours = DECODE(billable_labor_hours_flag,'Y',
2593 (NVL(i_tot_billable_labor_hours,0) +
2594 DECODE(pta.system_linkage_function,
2595 'OT',
2596 x_tot_billable_quantity_tb2(i),
2597 'ST',
2598 x_tot_billable_quantity_tb2(i),
2599 0)),NULL),
2600 pta.unit_of_measure = x_unit_of_measure_tb2(i),
2601 pta.actual_cost_rollup_flag = 'Y',
2602 pta.last_update_date = SYSDATE,
2603 pta.last_updated_by = x_last_updated_by,
2604 pta.request_Id = x_request_id,
2605 pta.program_application_id = x_program_application_id,
2606 pta.program_id = x_program_id,
2607 pta.program_update_Date = SYSDATE
2608 WHERE
2609 pta.txn_accum_id = x_txn_accum_id_tb2(i);
2610 END IF;
2611
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 x_err_code := SQLCODE;
2615 RAISE;
2616 END accum_act_txn;
2617 /* End of Code changes for bug 13448783 */
2618
2619 -- This procedure accumulates the revenue txn in the given
2620 -- txn_accum_id.
2621
2622 PROCEDURE accum_rev_txn
2623 ( x_txn_accum_id IN NUMBER,
2624 x_tot_revenue IN NUMBER,
2625 x_unit_of_measure IN VARCHAR2,
2626 x_err_stage IN OUT NOCOPY VARCHAR2,
2627 x_err_code IN OUT NOCOPY NUMBER)
2628 IS
2629 BEGIN
2630 x_err_code :=0;
2631 x_err_stage := 'Accumulating Revenue transactions';
2632
2633 -- accumulate revenue now
2634 UPDATE pa_txn_accum pta
2635 SET pta.i_tot_revenue = DECODE(revenue_flag,'Y',
2636 (NVL(i_tot_revenue, 0) +
2637 x_tot_revenue),NULL),
2638 pta.unit_of_measure = x_unit_of_measure,
2639 pta.revenue_rollup_flag = 'Y',
2640 pta.last_update_date = SYSDATE,
2641 pta.last_updated_by = x_last_updated_by,
2642 pta.request_Id = x_request_id,
2643 pta.program_application_id = x_program_application_id,
2644 pta.program_id = x_program_id,
2645 pta.program_update_Date = SYSDATE
2646 WHERE
2647 pta.txn_accum_id = x_txn_accum_id;
2648
2649 EXCEPTION
2650 WHEN OTHERS THEN
2651 x_err_code := SQLCODE;
2652 RAISE;
2653 END accum_rev_txn;
2654
2655 -- This procedure accumulates the commitment txn in the given
2656 -- txn_accum_id.
2657 -- Please note that the commitment quantity is always accumulated
2658 -- into CMT_QUANTITY column irrespective of the unit_of_measure
2659 -- is 'HOURS'
2660
2661 PROCEDURE accum_cmt_txn
2662 ( x_txn_accum_id IN NUMBER,
2663 x_tot_cmt_raw_cost IN NUMBER,
2664 x_tot_cmt_burdened_cost IN NUMBER,
2665 x_err_stage IN OUT NOCOPY VARCHAR2,
2666 x_err_code IN OUT NOCOPY NUMBER)
2667 IS
2668 BEGIN
2669 x_err_code :=0;
2670 x_err_stage := 'Accumulating Commitments transaction';
2671 -- accumulate commitment now
2672
2673 /* Bug# 1239605 - Included NVL for x_tot_cmt_raw_cost and
2674 x_tot_burdened_cost in the following update */
2675
2676 UPDATE pa_txn_accum pta
2677 SET pta.tot_cmt_raw_cost = DECODE(cmt_raw_cost_flag,'Y',
2678 (NVL(tot_cmt_raw_cost, 0) +
2679 NVL(x_tot_cmt_raw_cost,0)),NULL),
2680 pta.tot_cmt_burdened_cost = DECODE(cmt_burdened_cost_flag,'Y',
2681 (NVL(tot_cmt_burdened_cost, 0) +
2682 NVL(x_tot_cmt_burdened_cost,0)),NULL),
2683 pta.cmt_rollup_flag = 'Y',
2684 pta.last_update_date = SYSDATE,
2685 pta.last_updated_by = x_last_updated_by,
2686 pta.request_Id = x_request_id,
2687 pta.program_application_id= x_program_application_id,
2688 pta.program_id = x_program_id,
2689 pta.program_update_Date = SYSDATE
2690 WHERE
2691 pta.txn_accum_id = x_txn_accum_id;
2692
2693 EXCEPTION
2694 WHEN OTHERS THEN
2695 x_err_code := SQLCODE;
2696 RAISE;
2697 END accum_cmt_txn;
2698
2699 -- Accumulate cost from CDLS
2700
2701 -- Name: Accum_Cdls
2702 --
2703 -- History
2704 -- dd-mmm-1997 Vbanal Created.
2705 --
2706 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
2707 -- Removed joins to gl_date_period_map and related entities
2708 -- and referenced new GL and PA period name columns on cdl.
2709 --
2710 -- 13-OCT-2002 Sacgupta Bug # 2580808.
2711 -- Modified for the Enhanced Period Processing effort.
2712 -- Fetching PA period name and GL period name from gl_date_period_map
2713 -- and related entities for inserting into pa_txn_accum table when
2714 -- GL and PA period name columns on cdl are null.
2715 --
2716 -- 05-NOV-2002 Sacgupta Bug # 2650900.
2717 -- Removed the condition x_mode <> I. So now both Update process and
2718 -- Refresh process will fetch PA period name and GL period name from
2719 -- gl_date_period_map and related entities for inserting into
2720 -- pa_txn_accum table when GL and PA period name columns on cdl are null.
2721 --
2722 -- 20-MAY-2003 jwhite For r11i.PA.L Burdening Enhancements, modified the following
2723 -- cursors: selcdls1, selcdls2, selcdls3.
2724 --
2725 -- Code like the following:
2726 -- AND cdl.line_type = 'R'
2727 -- was replaced with the following:
2728 -- AND ( cdl.line_type = 'R' OR cdl.line_type = 'I')
2729 --
2730 --
2731 -- 31-JUL-2003 jwhite For patchset 'L' Reburdening Enhancement, added this
2732 -- IN-parm to the accum_cdls procedure to help minimize found
2733 -- performance issues:
2734 -- x_cdl_line_type VARCHAR2
2735 --
2736 -- Code like the following:
2737 -- AND ( cdl.line_type = 'R' OR cdl.line_type = 'I')
2738 -- was replaced with the following:
2739 -- AND cdl.line_type = x_cdl_line_type
2740 --
2741 -- 07-Jul-2004 Sacgupta Bug # 3736097.
2742 -- Commented out all occurence of x_end_project_id.
2743 -- This is done because all the processing is done for a
2744 -- single project rather than for a range of projects.
2745 --
2746
2747
2748
2749 PROCEDURE accum_cdls
2750 ( x_start_project_id IN NUMBER,
2751 x_end_project_id IN NUMBER,
2752 x_start_pa_date IN DATE,
2753 x_end_pa_date IN DATE,
2754 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
2755 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
2756 x_cdl_line_type IN VARCHAR2,
2757 x_err_stage IN OUT NOCOPY VARCHAR2,
2758 x_err_code IN OUT NOCOPY NUMBER)
2759 IS
2760 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
2761
2762
2763 -- The cursor selcdl selects all CDLs which
2764 -- satisfy the pa_period given as the parameters
2765 -- the argument x_mode represents the mode for accumulation i.e.
2766 -- 'I' for incremental and 'F' for FULL
2767
2768 /* Bug# 1770772 - Breaking the cursor into two and call/open it conditionally based on x_mode parameter
2769 to eliminate the decode on resource_accumulated_Flag and hence use index usage
2770 */
2771 /* Bug# 10371758 - quantity from pa_cost_distribution_lines_all is selected as
2772 NVL(cdl.quantity,0) for 'I' lines also.This is modified as
2773 DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS',
2774 DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity.
2775 And also billable quantity is modified as
2776 DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
2777 */
2778
2779
2780 CURSOR selcdls1 IS
2781 SELECT
2782 cdl.ROWID cdlrowid,
2783 cdl.expenditure_item_id expenditure_item_id,
2784 cdl.line_num line_num,
2785 pe.incurred_by_person_id person_id,
2786 ei.job_id job_id,
2787 NVL(ei.override_to_organization_id,
2788 pe.incurred_by_organization_id) organization_id,
2789 decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
2790 et.expenditure_type expenditure_type,
2791 ei.non_labor_resource non_labor_resource,
2792 et.expenditure_category expenditure_category,
2793 et.revenue_category_code revenue_category,
2794 ei.organization_id non_labor_resource_org_id,
2795 ei.system_linkage_function system_linkage_function,
2796 cdl.project_id project_id,
2797 cdl.task_id task_id,
2798 cdl.RECVR_PA_PERIOD_NAME pa_period,
2799 cdl.RECVR_GL_PERIOD_NAME gl_period,
2800 pe.expenditure_ending_date week_ending_date,
2801 LAST_DAY(ei.expenditure_item_date) month_ending_date,
2802 NVL(cdl.amount,0) raw_cost,
2803 --DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity,
2804 DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity, --Bug 16022826
2805 NVL(cdl.burdened_cost,0) burdened_cost,
2806 DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
2807 --DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
2808 DECODE(cdl.billable_flag,'Y',DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0),0) billable_quantity, --Bug 16022826
2809 DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
2810 decode(et.unit_of_measure,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
2811 cdl.ind_compiled_set_id cost_ind_compiled_set_id
2812 FROM
2813 pa_expenditures_all pe,
2814 pa_expenditure_types et,
2815 pa_expenditure_items_all ei,
2816 pa_cost_distribution_lines_all cdl
2817 WHERE
2818 cdl.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id
2819 AND cdl.line_type = x_cdl_line_type
2820 AND cdl.resource_accumulated_flag = 'N'
2821 AND cdl.expenditure_item_id = ei.expenditure_item_id
2822 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2823 AND ei.expenditure_type = et.expenditure_type
2824 AND ei.task_id = cdl.task_id
2825 AND pe.expenditure_id = ei.expenditure_id
2826 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2827 AND ei.system_linkage_function||'' =
2828 NVL(x_system_linkage_function,ei.system_linkage_function)
2829 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2830
2831
2832 /* Bug# 10371758 - quantity from pa_cost_distribution_lines_all is selected as
2833 NVL(cdl.quantity,0) for 'I' lines also.This is modified as
2834 DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure, et.unit_of_measure),'HOURS',
2835 DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity.
2836 And also billable quantity is modified as
2837 DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0,NVL(cdl.quantity,0)),0) billable_quantity,
2838 */
2839
2840 CURSOR selcdls2 IS
2841 SELECT
2842 cdl.ROWID cdlrowid,
2843 cdl.expenditure_item_id expenditure_item_id,
2844 cdl.line_num line_num,
2845 pe.incurred_by_person_id person_id,
2846 ei.job_id job_id,
2847 NVL(ei.override_to_organization_id,
2848 pe.incurred_by_organization_id) organization_id,
2849 decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
2850 et.expenditure_type expenditure_type,
2851 ei.non_labor_resource non_labor_resource,
2852 et.expenditure_category expenditure_category,
2853 et.revenue_category_code revenue_category,
2854 ei.organization_id non_labor_resource_org_id,
2855 ei.system_linkage_function system_linkage_function,
2856 cdl.project_id project_id,
2857 cdl.task_id task_id,
2858 cdl.RECVR_PA_PERIOD_NAME pa_period,
2859 cdl.RECVR_GL_PERIOD_NAME gl_period,
2860 pe.expenditure_ending_date week_ending_date,
2861 LAST_DAY(ei.expenditure_item_date) month_ending_date,
2862 NVL(cdl.amount,0) raw_cost,
2863 --DECODE( DECODE(et.unit_of_measure, NULL, ei.unit_of_measure,et.unit_of_measure),'HOURS', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity,
2864 DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) quantity, --Bug 16022826
2865 NVL(cdl.burdened_cost,0) burdened_cost,
2866 DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
2867 --DECODE(cdl.billable_flag,'Y',DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0) billable_quantity,
2868 DECODE(cdl.billable_flag,'Y',DECODE( DECODE(ei.system_linkage_function, 'ST', 'Y', 'OT', 'Y', 'N'),'Y', DECODE(line_type, 'I', 0, NVL(cdl.quantity,0)),0),0) billable_quantity, --Bug 16022826
2869 DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
2870 decode(et.unit_of_measure ,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
2871 cdl.ind_compiled_set_id cost_ind_compiled_set_id
2872 FROM
2873 pa_expenditures_all pe,
2874 pa_expenditure_types et,
2875 pa_expenditure_items_all ei,
2876 pa_cost_distribution_lines_all cdl
2877 WHERE
2878 -- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
2879 cdl.project_id = x_start_project_id
2880 AND cdl.line_type = x_cdl_line_type
2881 /* Commented for bug# 1770772 while splitting the cursor in two
2882 AND cdl.resource_accumulated_flag =
2883 decode(x_mode,'I','N',
2884 'F',cdl.resource_accumulated_flag,'N')
2885 */
2886 AND cdl.expenditure_item_id = ei.expenditure_item_id
2887 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2888 AND ei.expenditure_type = et.expenditure_type
2889 AND ei.task_id = cdl.task_id
2890 AND pe.expenditure_id = ei.expenditure_id
2891 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2892 AND ei.system_linkage_function||'' =
2893 NVL(x_system_linkage_function,ei.system_linkage_function)
2894 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2895
2896 -- Cursor added for bug 2580808
2897 CURSOR selcdls3 (crowid VARCHAR2)IS
2898 SELECT
2899 p.period_name pa_period1,
2900 g.period_name gl_period1
2901 FROM
2902 gl_date_period_map p,
2903 gl_date_period_map g,
2904 pa_expenditures_all pe,
2905 pa_expenditure_types et,
2906 pa_expenditure_items_all ei,
2907 pa_cost_distribution_lines_all cdl,
2908 pa_implementations pi,
2909 gl_sets_of_books sob
2910 WHERE
2911 -- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
2912 cdl.project_id = x_start_project_id
2913 AND cdl.ROWID = CHARTOROWID(crowid)
2914 AND cdl.line_type = x_cdl_line_type
2915 AND cdl.expenditure_item_id = ei.expenditure_item_id
2916 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2917 AND ei.expenditure_type = et.expenditure_type
2918 AND ei.task_id = cdl.task_id
2919 AND pe.expenditure_id = ei.expenditure_id
2920 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2921 AND sob.set_of_books_id = pi.set_of_books_id
2922 AND p.period_set_name = sob.period_set_name
2923 AND g.period_set_name = sob.period_set_name
2924 AND p.period_type = pi.pa_period_type
2925 AND g.period_type = sob.accounted_period_type
2926 /* Bug #3493462: Added trunc to recvr_pa_date */
2927 AND p.accounting_date = TRUNC(cdl.recvr_pa_date)
2928 AND g.accounting_date = NVL(TRUNC(cdl.recvr_gl_date), TRUNC(cdl.recvr_pa_date))
2929 AND ei.system_linkage_function||'' =
2930 NVL(x_system_linkage_function,ei.system_linkage_function)
2931 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2932
2933 /* Start of Code changes for bug 13448783 */
2934 Type cdlrec_tb is table of selcdls1%ROWTYPE;
2935 cdltab cdlrec_tb;
2936 --cdlrec selcdls1%ROWTYPE;
2937 cdlrec1 selcdls3%ROWTYPE; -- added for bug 2580808.
2938 x_txn_accum_id NUMBER;
2939 row_processed NUMBER;
2940 commit_rows NUMBER;
2941
2942 cdlrowid_tb1 cdlrowid_tb := cdlrowid_tb();
2943 x_txn_accum_id_tb1 x_txn_accum_id_tb := x_txn_accum_id_tb();
2944 x_tot_raw_cost_tb1 x_tot_raw_cost_tb := x_tot_raw_cost_tb();
2945 x_tot_burdened_cost_tb1 x_tot_burdened_cost_tb := x_tot_burdened_cost_tb();
2946 x_tot_quantity_tb1 x_tot_quantity_tb := x_tot_quantity_tb();
2947 x_tot_billable_raw_cost_tb1 x_tot_billable_raw_cost_tb := x_tot_billable_raw_cost_tb();
2948 x_tot_billable_brdn_cost_tb1 x_tot_billable_brdn_cost_tb := x_tot_billable_brdn_cost_tb();
2949 x_tot_billable_quantity_tb1 x_tot_billable_quantity_tb := x_tot_billable_quantity_tb();
2950 x_unit_of_measure_tb1 x_unit_of_measure_tb := x_unit_of_measure_tb();
2951 i NUMBER := 1;
2952
2953 BEGIN
2954
2955 x_txn_accum_id :=0;
2956 x_err_code :=0;
2957 row_processed :=0;
2958 commit_rows :=0;
2959 x_err_stage := 'Accumulating CDLs';
2960
2961 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2962 pa_debug.debug('accum_cdls: ' || x_err_stage);
2963 END IF;
2964
2965 /* Included for bug# 1770772 */
2966
2967 IF x_mode = 'I' THEN
2968 OPEN selcdls1;
2969 ELSE
2970 OPEN selcdls2;
2971 END IF;
2972
2973 LOOP
2974 IF x_mode = 'I' THEN
2975 FETCH selcdls1 bulk collect INTO cdltab;
2976 -- EXIT WHEN selcdls1%NOTFOUND;
2977 ELSE
2978 FETCH selcdls2 bulk collect INTO cdltab;
2979 -- EXIT WHEN selcdls2%NOTFOUND;
2980 END IF;
2981
2982 /* Commented for bug# 1770772 */
2983
2984 /* FOR cdlrec IN selcdls LOOP */
2985
2986 /* End of changes for bug# 1770772 */
2987
2988 if cdltab.count <> 0 then
2989 FOR j in cdltab.first .. cdltab.last LOOP
2990
2991 row_processed := row_processed + 1;
2992 commit_rows := commit_rows + 1;
2993
2994 -- IF clause added for bug 2580808.
2995 -- IF x_mode <> 'I' THEN -- for bug 2650900
2996 IF cdltab(j).pa_period IS NULL OR cdltab(j).gl_period IS NULL THEN
2997 OPEN selcdls3(ROWIDTOCHAR(cdltab(j).cdlrowid));
2998 FETCH selcdls3 INTO cdlrec1;
2999 CLOSE selcdls3;
3000 END IF;
3001 -- END IF; -- for bug 2650900
3002
3003
3004 create_txn_accum(
3005 cdltab(j).project_id,
3006 cdltab(j).task_id,
3007 NVL(cdltab(j).pa_period, cdlrec1.pa_period1), -- Modified for bug 2580808.
3008 NVL(cdltab(j).gl_period, cdlrec1.gl_period1), -- Modified for bug 2580808.
3009 cdltab(j).week_ending_date,
3010 cdltab(j).month_ending_date,
3011 cdltab(j).person_id,
3012 cdltab(j).job_id,
3013 cdltab(j).vendor_id,
3014 cdltab(j).expenditure_type,
3015 cdltab(j).organization_id,
3016 cdltab(j).non_labor_resource,
3017 cdltab(j).non_labor_resource_org_id,
3018 cdltab(j).expenditure_category,
3019 cdltab(j).revenue_category,
3020 NULL, -- event_type
3021 NULL, -- event_type_classification
3022 cdltab(j).system_linkage_function,
3023 'C', -- x_line_type = 'C' for CDL
3024 cdltab(j).cost_ind_compiled_set_id,
3025 NULL, -- rev_ind_compiled_set_id
3026 NULL, -- inv_ind_compiled_set_id
3027 NULL, -- cmt_ind_compiled_set_id
3028 x_txn_accum_id,
3029 x_err_stage,
3030 x_err_code);
3031
3032 -- Create a row for drilldown in pa_txn_accume_details Now
3033
3034 create_txn_accum_details(
3035 x_txn_accum_id,
3036 'C', -- CDLS
3037 cdltab(j).expenditure_item_id,
3038 cdltab(j).line_num,
3039 NULL, -- event_num
3040 NULL, -- cmt_line_id
3041 cdltab(j).project_id,
3042 cdltab(j).task_id,
3043 x_err_stage,
3044 x_err_code);
3045
3046 -- Accume this row now for txn_accum_id = x_txn_accum_id
3047 -- also create rows for drilldown
3048
3049 /* accum_act_txn(
3050 x_txn_accum_id,
3051 cdlrec.raw_cost,
3052 cdlrec.burdened_cost,
3053 cdlrec.quantity,
3054 cdlrec.billable_raw_cost,
3055 cdlrec.billable_burdened_cost,
3056 cdlrec.billable_quantity,
3057 cdlrec.unit_of_measure,
3058 x_err_stage,
3059 x_err_code);
3060 */
3061
3062 cdlrowid_tb1.extend;
3063 cdlrowid_tb1(i) := cdltab(j).cdlrowid;
3064 x_txn_accum_id_tb1.extend;
3065 x_txn_accum_id_tb1(i) := x_txn_accum_id;
3066 x_tot_raw_cost_tb1.extend;
3067 x_tot_raw_cost_tb1(i) := cdltab(j).raw_cost;
3068 x_tot_burdened_cost_tb1.extend;
3069 x_tot_burdened_cost_tb1(i) := cdltab(j).burdened_cost;
3070 x_tot_quantity_tb1.extend;
3071 x_tot_quantity_tb1(i) := cdltab(j).quantity;
3072 x_tot_billable_raw_cost_tb1.extend;
3073 x_tot_billable_raw_cost_tb1(i) := cdltab(j).billable_raw_cost;
3074 x_tot_billable_brdn_cost_tb1.extend;
3075 x_tot_billable_brdn_cost_tb1(i) := cdltab(j).billable_burdened_cost;
3076 x_tot_billable_quantity_tb1.extend;
3077 x_tot_billable_quantity_tb1(i) := cdltab(j).billable_quantity;
3078 x_unit_of_measure_tb1.extend;
3079 x_unit_of_measure_tb1(i) := cdltab(j).unit_of_measure;
3080 i := i + 1;
3081
3082
3083 --- Update the CDL.Resource_accumulated_flag = 'Y' Now
3084 /*
3085 UPDATE
3086 pa_cost_distribution_lines_all
3087 SET
3088 resource_accumulated_flag = 'Y'
3089 WHERE
3090 ROWID = cdlrec.cdlrowid;
3091 */
3092 IF (commit_rows >= pa_proj_accum_main.x_commit_size) THEN
3093 COMMIT;
3094 commit_rows := 0;
3095 END IF;
3096 END LOOP;
3097 END IF;
3098 IF x_mode = 'I' THEN
3099 EXIT WHEN selcdls1%NOTFOUND;
3100 ELSE
3101 EXIT WHEN selcdls2%NOTFOUND;
3102 END IF;
3103 END LOOP;
3104
3105 /* Included for bug# 1770772 */
3106
3107 IF x_mode = 'I' THEN
3108 CLOSE selcdls1;
3109 ELSE
3110 CLOSE selcdls2;
3111 END IF;
3112
3113 accum_act_txn(
3114 x_txn_accum_id_tb1,
3115 x_tot_raw_cost_tb1,
3116 x_tot_burdened_cost_tb1,
3117 x_tot_quantity_tb1,
3118 x_tot_billable_raw_cost_tb1,
3119 x_tot_billable_brdn_cost_tb1,
3120 x_tot_billable_quantity_tb1,
3121 x_unit_of_measure_tb1,
3122 x_err_stage,
3123 x_err_code);
3124
3125 FORALL i in 1 .. cdlrowid_tb1.COUNT
3126 UPDATE
3127 pa_cost_distribution_lines_all
3128 SET
3129 resource_accumulated_flag = 'Y'
3130 WHERE
3131 ROWID = cdlrowid_tb1(i);
3132
3133 /* End of Code changes for bug 13448783 */
3134
3135 /* End of changes for bug# 1770772 */
3136
3137 IF (commit_rows < pa_proj_accum_main.x_commit_size) THEN
3138 COMMIT;
3139 END IF;
3140
3141 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3142 pa_debug.debug('accum_cdls: ' || 'Number of CDL Processed = ' || TO_CHAR(row_processed));
3143 END IF;
3144
3145 EXCEPTION
3146 WHEN OTHERS THEN
3147 x_err_code := SQLCODE;
3148 RAISE;
3149
3150 END accum_cdls;
3151
3152 -- Accumulate revenue from RDLS
3153
3154 -- Name: Accum_Rdls
3155 --
3156 -- History
3157 -- dd-mmm-1997 Vbanal Created.
3158 --
3159 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
3160 -- Removed joins to gl_date_period_map and related entities
3161 -- and referenced new GL and PA period name columns on rdl.
3162 --
3163 -- 13-OCT-2002 Sacgupta Bug # 2580808.
3164 -- Modified for the Enhanced Period Processing effort.
3165 -- Fetching PA period name and GL period name from gl_date_period_map
3166 -- and related entities for inserting into pa_txn_accum table when
3167 -- GL and PA period name columns on rdl are null.
3168 --
3169 -- 05-NOV-2002 Sacgupta Bug # 2650900.
3170 -- Removed the condition x_mode <> I. So now both Update process and
3171 -- Refresh process will fetch PA period name and GL period name from
3172 -- gl_date_period_map and related entities for inserting into
3173 -- pa_txn_accum table when GL and PA period name columns on rdl are null.
3174 --
3175 -- 07-Jul-2004 Sacgupta Bug # 3736097.
3176 -- Commented out all occurence of x_end_project_id.
3177 -- This is done because all the processing is done for a
3178 -- single project rather than for a range of projects.
3179 PROCEDURE accum_rdls
3180 ( x_start_project_id IN NUMBER,
3181 x_end_project_id IN NUMBER,
3182 x_start_pa_date IN DATE,
3183 x_end_pa_date IN DATE,
3184 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3185 x_err_stage IN OUT NOCOPY VARCHAR2,
3186 x_err_code IN OUT NOCOPY NUMBER)
3187 IS
3188 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3189
3190
3191 CURSOR selrdls IS
3192 SELECT
3193 dr.ROWID drrowid,
3194 rdl.expenditure_item_id expenditure_item_id,
3195 rdl.line_num line_num,
3196 pe.incurred_by_person_id person_id,
3197 ei.job_id job_id,
3198 NVL(ei.override_to_organization_id,
3199 pe.incurred_by_organization_id) organization_id,
3200 et.expenditure_type expenditure_type,
3201 ei.non_labor_resource non_labor_resource,
3202 et.expenditure_category expenditure_category,
3203 et.revenue_category_code revenue_category,
3204 ei.organization_id non_labor_resource_org_id,
3205 ei.system_linkage_function system_linkage_function,
3206 dr.project_id project_id,
3207 ei.task_id task_id,
3208 dr.PA_PERIOD_NAME pa_period,
3209 dr.GL_PERIOD_NAME gl_period,
3210 pe.expenditure_ending_date week_ending_date,
3211 LAST_DAY(ei.expenditure_item_date) month_ending_date,
3212 rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
3213 rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
3214 NVL(rdl.amount,0) amount,
3215 decode(et.unit_of_measure,NULL,et.unit_of_measure,ei.unit_of_measure) unit_of_measure
3216 FROM
3217 pa_expenditures_all pe,
3218 pa_expenditure_types et,
3219 pa_expenditure_items_all ei,
3220 pa_cust_rev_dist_lines rdl,
3221 pa_draft_revenues dr
3222 WHERE
3223 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
3224 AND NVL(dr.resource_accumulated_flag,'S') =
3225 DECODE(x_mode,'I','S',
3226 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3227 AND dr.released_date IS NOT NULL
3228 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3229 AND dr.project_id = rdl.project_id
3230 AND dr.draft_revenue_num = rdl.draft_revenue_num
3231 AND rdl.expenditure_item_id = ei.expenditure_item_id
3232 AND ei.expenditure_type = et.expenditure_type
3233 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3234 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3235 AND pe.expenditure_id = ei.expenditure_id;
3236
3237 -- Cursor added for bug 2580808
3238 CURSOR selrdls1 (rrowid VARCHAR2)IS
3239 SELECT
3240 p.period_name pa_period1,
3241 g.period_name gl_period1
3242 FROM
3243 gl_date_period_map p,
3244 gl_date_period_map g,
3245 pa_expenditures_all pe,
3246 pa_expenditure_types et,
3247 pa_expenditure_items_all ei,
3248 pa_cust_rev_dist_lines rdl,
3249 pa_draft_revenues dr,
3250 pa_implementations pi,
3251 gl_sets_of_books sob
3252 WHERE
3253 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
3254 AND dr.ROWID = CHARTOROWID(rrowid)
3255 AND NVL(dr.resource_accumulated_flag,'S') =
3256 DECODE(x_mode,'I','S',
3257 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3258 AND dr.released_date IS NOT NULL
3259 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3260 AND dr.project_id = rdl.project_id
3261 AND dr.draft_revenue_num = rdl.draft_revenue_num
3262 AND rdl.expenditure_item_id = ei.expenditure_item_id
3263 AND ei.expenditure_type = et.expenditure_type
3264 AND sob.set_of_books_id = pi.set_of_books_id
3265 AND p.period_set_name = sob.period_set_name
3266 AND g.period_set_name = sob.period_set_name
3267 AND p.period_type = pi.pa_period_type
3268 AND g.period_type = sob.accounted_period_type
3269 AND p.accounting_date = dr.pa_date
3270 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3271 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3272 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3273 AND pe.expenditure_id = ei.expenditure_id;
3274
3275
3276 rdlrec selrdls%ROWTYPE;
3277 rdlrec1 selrdls1%ROWTYPE; -- added for bug 2580808.
3278 x_txn_accum_id NUMBER;
3279 row_processed NUMBER;
3280
3281 BEGIN
3282 x_txn_accum_id :=0;
3283 x_err_code :=0;
3284 row_processed :=0;
3285 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3286 x_err_stage := 'Accumulating revenue';
3287 pa_debug.debug('accum_rdls: ' || x_err_stage);
3288 END IF;
3289
3290 FOR rdlrec IN selrdls LOOP
3291
3292 row_processed := row_processed + 1;
3293
3294 -- IF clause added for bug 2580808.
3295 -- IF x_mode <> 'I' THEN -- for bug 2650900
3296 IF (rdlrec.pa_period IS NULL OR rdlrec.gl_period IS NULL) THEN
3297 OPEN selrdls1(ROWIDTOCHAR(rdlrec.drrowid));
3298 FETCH selrdls1 INTO rdlrec1;
3299 CLOSE selrdls1;
3300 END IF;
3301 -- END IF; -- for bug 2650900
3302
3303 create_txn_accum(
3304 rdlrec.project_id,
3305 rdlrec.task_id,
3306 -- Commented out for bug 2580808
3307 -- rdlrec.pa_period,
3308 -- rdlrec.gl_period,
3309 NVL(rdlrec.pa_period,rdlrec1.pa_period1), -- added for bug 2580808
3310 NVL(rdlrec.gl_period,rdlrec1.gl_period1), -- added for bug 2580808
3311 rdlrec.week_ending_date,
3312 rdlrec.month_ending_date,
3313 rdlrec.person_id,
3314 rdlrec.job_id,
3315 NULL, -- vendor_id
3316 rdlrec.expenditure_type,
3317 rdlrec.organization_id,
3318 rdlrec.non_labor_resource,
3319 rdlrec.non_labor_resource_org_id,
3320 rdlrec.expenditure_category,
3321 rdlrec.revenue_category,
3322 NULL, -- event_type
3323 NULL, -- event_type_classification
3324 rdlrec.system_linkage_function,
3325 'R', -- x_line_type = 'R' for RDL
3326 NULL, -- cost_ind_compiled_set_id
3327 rdlrec.rev_ind_compiled_set_id,
3328 rdlrec.inv_ind_compiled_set_id,
3329 NULL, -- cmt_ind_compiled_set_id
3330 x_txn_accum_id,
3331 x_err_stage,
3332 x_err_code);
3333
3334 -- Create a row for drilldown in pa_txn_accum_details Now
3335
3336 create_txn_accum_details(
3337 x_txn_accum_id,
3338 'R', -- RDLS
3339 rdlrec.expenditure_item_id,
3340 rdlrec.line_num,
3341 NULL, -- Event Num
3342 NULL, -- CMT_LINE_ID
3343 rdlrec.project_id,
3344 rdlrec.task_id,
3345 x_err_stage,
3346 x_err_code);
3347
3348 -- Accume this row in txn_accum_id = x_txn_accum_id
3349 -- also create rows for drilldown
3350
3351 accum_rev_txn(
3352 x_txn_accum_id,
3353 rdlrec.amount,
3354 rdlrec.unit_of_measure,
3355 x_err_stage,
3356 x_err_code);
3357
3358 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3359
3360 UPDATE
3361 pa_draft_revenues
3362 SET
3363 resource_accumulated_flag = 'S'
3364 WHERE
3365 ROWID = rdlrec.drrowid;
3366
3367 END LOOP;
3368
3369 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3370 pa_debug.debug('accum_rdls: ' || 'Number of RDL Processed = ' || TO_CHAR(row_processed));
3371 END IF;
3372
3373 EXCEPTION
3374 WHEN OTHERS THEN
3375 x_err_code := SQLCODE;
3376 RAISE;
3377
3378 END accum_rdls;
3379
3380 -- Accumulate revenue from Events
3381
3382 -- Name: Accum_Erdls
3383 --
3384 -- History
3385 -- dd-mmm-1997 Vbanal Created.
3386 --
3387 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
3388 -- Removed joins to gl_date_period_map and related entities
3389 -- and referenced new GL and PA period name columns on erdls
3390 --
3391 -- 13-OCT-2002 Sacgupta Bug # 2580808.
3392 -- Modified for the Enhanced Period Processing effort.
3393 -- Fetching PA period name and GL period name from gl_date_period_map
3394 -- and related entities for inserting into pa_txn_accum table when
3395 -- GL and PA period name columns on erdls are null.
3396 --
3397 -- 05-NOV-2002 Sacgupta Bug # 2650900.
3398 -- Removed the condition x_mode <> I. So now both Update process and
3399 -- Refresh process will fetch PA period name and GL period name from
3400 -- gl_date_period_map and related entities for inserting into
3401 -- pa_txn_accum table when GL and PA period name columns on erdls are null.
3402 --
3403 -- 07-JUL-2004 Sacgupta Bug # 3736097.
3404 -- Commented out all occurence of x_end_project_id.
3405 -- This is done because all the processing is done for a
3406 -- single project rather than for a range of projects.
3407 --
3408 PROCEDURE accum_erdls
3409 ( x_start_project_id IN NUMBER,
3410 x_end_project_id IN NUMBER,
3411 x_start_pa_date IN DATE,
3412 x_end_pa_date IN DATE,
3413 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3414 x_err_stage IN OUT NOCOPY VARCHAR2,
3415 x_err_code IN OUT NOCOPY NUMBER)
3416 IS
3417 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3418
3419
3420
3421 CURSOR selevents IS
3422 SELECT
3423 dr.ROWID drrowid,
3424 erdl.event_num event_num,
3425 erdl.line_num line_num,
3426 ev.organization_id organization_id,
3427 ev.event_type,
3428 evt.revenue_category_code revenue_category,
3429 erdl.project_id,
3430 NVL(erdl.task_id,0) task_id,
3431 dr.PA_PERIOD_NAME pa_period,
3432 dr.GL_PERIOD_NAME gl_period,
3433 evt.event_type_classification,
3434 pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
3435 LAST_DAY(ev.completion_date) month_ending_date,
3436 NVL(erdl.amount,0) amount
3437 FROM
3438 pa_events ev,
3439 pa_event_types evt,
3440 pa_cust_event_rev_dist_lines erdl,
3441 pa_draft_revenues dr
3442 WHERE
3443 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
3444 AND NVL(dr.resource_accumulated_flag,'S') =
3445 DECODE(x_mode,'I','S',
3446 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3447 AND dr.released_date IS NOT NULL
3448 AND dr.project_id = erdl.project_id
3449 AND dr.draft_revenue_num = erdl.draft_revenue_num
3450 AND erdl.project_id = ev.project_id
3451 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3452 AND ev.event_num = erdl.event_num
3453 AND ev.event_type = evt.event_type
3454 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3455
3456 -- Cursor added for bug 2580808
3457 CURSOR selevents1 (rrowid VARCHAR2)IS
3458 SELECT
3459 p.period_name pa_period1,
3460 g.period_name gl_period1
3461 FROM
3462 gl_date_period_map p,
3463 gl_date_period_map g,
3464 pa_events ev,
3465 pa_event_types evt,
3466 pa_cust_event_rev_dist_lines erdl,
3467 pa_draft_revenues dr,
3468 pa_implementations pi,
3469 gl_sets_of_books sob
3470 WHERE
3471 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
3472 AND dr.ROWID = CHARTOROWID(rrowid)
3473 AND NVL(dr.resource_accumulated_flag,'S') =
3474 DECODE(x_mode,'I','S',
3475 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3476 AND dr.released_date IS NOT NULL
3477 AND dr.project_id = erdl.project_id
3478 AND dr.draft_revenue_num = erdl.draft_revenue_num
3479 AND erdl.project_id = ev.project_id
3480 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3481 AND ev.event_num = erdl.event_num
3482 AND ev.event_type = evt.event_type
3483 AND sob.set_of_books_id = pi.set_of_books_id
3484 AND p.period_set_name = sob.period_set_name
3485 AND g.period_set_name = sob.period_set_name
3486 AND p.period_type = pi.pa_period_type
3487 AND g.period_type = sob.accounted_period_type
3488 AND p.accounting_date = dr.pa_date
3489 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3490 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3491
3492
3493 eventrec selevents%ROWTYPE;
3494 eventrec1 selevents1%ROWTYPE; -- for bug 2580808
3495 x_txn_accum_id NUMBER;
3496 row_processed NUMBER;
3497
3498
3499 BEGIN
3500 x_txn_accum_id :=0;
3501 x_err_code :=0;
3502 row_processed :=0;
3503 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3504 x_err_stage := 'Accumulating Event Revenue';
3505 pa_debug.debug('accum_erdls: ' || x_err_stage);
3506 END IF;
3507
3508 FOR eventrec IN selevents LOOP
3509
3510 row_processed := row_processed + 1;
3511
3512 -- IF clause added for bug 2580808.
3513 -- IF x_mode <> 'I' THEN -- for bug 2650900
3514 IF (eventrec.pa_period IS NULL OR eventrec.gl_period IS NULL) THEN
3515 OPEN selevents1(ROWIDTOCHAR(eventrec.drrowid));
3516 FETCH selevents1 INTO eventrec1;
3517 CLOSE selevents1;
3518 END IF;
3519 -- END IF; -- for bug 2650900
3520
3521 create_txn_accum(
3522 eventrec.project_id,
3523 eventrec.task_id,
3524 -- Commented out for bug 2580808
3525 -- eventrec.pa_period,
3526 -- eventrec.gl_period,
3527 NVL(eventrec.pa_period,eventrec1.pa_period1), -- for bug 2580808
3528 NVL(eventrec.gl_period,eventrec1.gl_period1), -- for bug 2580808
3529 eventrec.week_ending_date,
3530 eventrec.month_ending_date,
3531 NULL, -- person_id
3532 NULL, -- job_id
3533 NULL, -- vendor_id
3534 NULL, -- expenditure_type
3535 eventrec.organization_id,
3536 NULL, -- non_labor_resource
3537 NULL, -- non_labor_resource_org_id
3538 NULL, -- expenditure_category
3539 eventrec.revenue_category,
3540 eventrec.event_type,
3541 eventrec.event_type_classification,
3542 NULL, -- system_linkage_function
3543 'R', -- x_line_type = 'R' for revenue
3544 NULL, -- cost_ind_compiled_set_id
3545 NULL, -- rev_ind_compiled_set_id
3546 NULL, -- inv_ind_compiled_set_id
3547 NULL, -- cmt_ind_compiled_set_id
3548 x_txn_accum_id,
3549 x_err_stage,
3550 x_err_code);
3551
3552 -- Create a row for drilldown in pa_txn_accum_details Now
3553
3554 create_txn_accum_details(
3555 x_txn_accum_id,
3556 'E', -- ERDLS
3557 NULL, -- expenditure_item_id
3558 eventrec.line_num,
3559 eventrec.event_num,
3560 NULL, -- cmt_line_id
3561 eventrec.project_id,
3562 eventrec.task_id,
3563 x_err_stage,
3564 x_err_code);
3565
3566 -- Accume this row in txn_accum_id = x_txn_accum_id
3567 -- also create rows for drilldown
3568
3569 accum_rev_txn(
3570 x_txn_accum_id,
3571 eventrec.amount,
3572 NULL, -- Unit_of_measure
3573 x_err_stage,
3574 x_err_code);
3575
3576 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3577
3578 UPDATE
3579 pa_draft_revenues
3580 SET
3581 resource_accumulated_flag = 'S'
3582 WHERE
3583 ROWID = eventrec.drrowid;
3584
3585 END LOOP;
3586
3587 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3588 pa_debug.debug('accum_erdls: ' || 'Number of Event RDL Processed = ' || TO_CHAR(row_processed));
3589 END IF;
3590
3591 EXCEPTION
3592 WHEN OTHERS THEN
3593 x_err_code := SQLCODE;
3594 RAISE;
3595
3596 END accum_erdls;
3597
3598 -- The procedure given below should be called to accumulate
3599 -- revenue
3600
3601 -- Name: Accum_Revenue
3602 --
3603 -- History
3604 -- dd-mmm-1997 Vbanal Created.
3605 --
3606 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
3607 -- Removed joins to gl_date_period_map and related entities
3608 -- and referenced new GL and PA period name columns.
3609 --
3610 -- 13-OCT-2002 Sacgupta Bug # 2580808.
3611 -- Modified for the Enhanced Period Processing effort.
3612 -- Fetching PA period name and GL period name from gl_date_period_map
3613 -- and related entities for inserting into pa_txn_accum table when
3614 -- GL and PA period name columns on rdl are null.
3615 --
3616 -- 05-NOV-2002 Sacgupta Bug # 2650900.
3617 -- Removed the condition x_mode <> I. So now both Update process and
3618 -- Refresh process will fetch PA period name and GL period name from
3619 -- gl_date_period_map and related entities for inserting into
3620 -- pa_txn_accum table when GL and PA period name columns on rdl are null.
3621 --
3622 -- 07-JUL-2004 Sacgupta Bug # 3736097.
3623 -- Commented out all occurence of x_end_project_id.
3624 -- This is done because all the processing is done for a
3625 -- single project rather than for a range of projects.
3626 --
3627 PROCEDURE accum_revenue
3628 ( x_start_project_id IN NUMBER,
3629 x_end_project_id IN NUMBER,
3630 x_start_pa_date IN DATE,
3631 x_end_pa_date IN DATE,
3632 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3633 x_err_stage IN OUT NOCOPY VARCHAR2,
3634 x_err_code IN OUT NOCOPY NUMBER)
3635 IS
3636 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3637
3638 CURSOR selrdls IS
3639 SELECT
3640 'R' line_type,
3641 dr.ROWID drrowid,
3642 rdl.expenditure_item_id expenditure_item_id,
3643 rdl.line_num line_num,
3644 pe.incurred_by_person_id person_id,
3645 ei.job_id job_id,
3646 NVL(ei.override_to_organization_id,
3647 pe.incurred_by_organization_id) organization_id,
3648 et.expenditure_type expenditure_type,
3649 ei.non_labor_resource non_labor_resource,
3650 et.expenditure_category expenditure_category,
3651 et.revenue_category_code revenue_category,
3652 ei.organization_id non_labor_resource_org_id,
3653 ei.system_linkage_function system_linkage_function,
3654 dr.project_id project_id,
3655 ei.task_id task_id,
3656 dr.PA_PERIOD_NAME pa_period,
3657 dr.GL_PERIOD_NAME gl_period,
3658 pe.expenditure_ending_date week_ending_date,
3659 LAST_DAY(ei.expenditure_item_date) month_ending_date,
3660 rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
3661 rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
3662 NVL(rdl.amount,0) amount,
3663 TO_NUMBER(NULL) event_num,
3664 NULL event_type,
3665 NULL event_type_classification,
3666 et.unit_of_measure unit_of_measure
3667 FROM
3668 pa_expenditures_all pe,
3669 pa_expenditure_types et,
3670 pa_expenditure_items_all ei,
3671 pa_cust_rev_dist_lines rdl,
3672 pa_draft_revenues dr
3673 WHERE
3674 dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
3675 AND NVL(dr.resource_accumulated_flag,'S') =
3676 DECODE(x_mode,'I','S',
3677 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3678 AND dr.released_date IS NOT NULL
3679 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3680 AND dr.project_id = rdl.project_id
3681 AND dr.draft_revenue_num = rdl.draft_revenue_num
3682 AND rdl.expenditure_item_id = ei.expenditure_item_id
3683 AND ei.expenditure_type = et.expenditure_type
3684 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3685 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3686 AND pe.expenditure_id = ei.expenditure_id
3687 UNION ALL
3688 SELECT
3689 'E' line_type,
3690 dr.ROWID drrowid,
3691 TO_NUMBER(NULL) expenditure_item_id,
3692 erdl.line_num line_num,
3693 TO_NUMBER(NULL) person_id,
3694 TO_NUMBER(NULL) job_id,
3695 ev.organization_id organization_id,
3696 NULL expenditure_type,
3697 NULL non_labor_resource,
3698 NULL expenditure_category,
3699 evt.revenue_category_code revenue_category,
3700 TO_NUMBER(NULL) non_labor_resource_org_id,
3701 NULL system_linkage_function,
3702 erdl.project_id project_id,
3703 NVL(erdl.task_id,0) task_id,
3704 dr.PA_PERIOD_NAME pa_period,
3705 dr.GL_PERIOD_NAME gl_period,
3706 pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
3707 LAST_DAY(ev.completion_date) month_ending_date,
3708 TO_NUMBER(NULL) rev_ind_compiled_set_id,
3709 TO_NUMBER(NULL) inv_ind_compiled_set_id,
3710 NVL(erdl.amount,0) amount,
3711 erdl.event_num event_num,
3712 ev.event_type event_type,
3713 evt.event_type_classification event_type_classification,
3714 NULL unit_of_measure
3715 FROM
3716 pa_events ev,
3717 pa_event_types evt,
3718 pa_cust_event_rev_dist_lines erdl,
3719 pa_draft_revenues dr
3720 WHERE
3721 dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
3722 AND NVL(dr.resource_accumulated_flag,'S') =
3723 DECODE(x_mode,'I','S',
3724 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3725 AND dr.released_date IS NOT NULL
3726 AND dr.project_id = erdl.project_id
3727 AND dr.draft_revenue_num = erdl.draft_revenue_num
3728 AND erdl.project_id = ev.project_id
3729 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3730 AND ev.event_num = erdl.event_num
3731 AND ev.event_type = evt.event_type
3732 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3733 ORDER BY 2;
3734
3735 -- Cursor added for bug 2580808
3736 CURSOR selrdls1 (rrowid VARCHAR2)IS
3737 SELECT
3738 p.period_name pa_period1,
3739 g.period_name gl_period1
3740 FROM
3741 gl_date_period_map p,
3742 gl_date_period_map g,
3743 pa_draft_revenues dr,
3744 pa_implementations pi,
3745 gl_sets_of_books sob
3746 WHERE
3747 dr.project_id = x_start_project_id
3748 AND dr.ROWID = CHARTOROWID(rrowid)
3749 AND NVL(dr.resource_accumulated_flag,'S') = DECODE(x_mode,'I','S','F',NVL(dr.resource_accumulated_flag,'S'),'S')
3750 AND dr.released_date IS NOT NULL
3751 AND sob.set_of_books_id = pi.set_of_books_id
3752 AND p.period_set_name = sob.period_set_name
3753 AND g.period_set_name = sob.period_set_name
3754 AND p.period_type = pi.pa_period_type
3755 AND g.period_type = sob.accounted_period_type
3756 AND p.accounting_date = dr.pa_date
3757 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3758 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3759
3760
3761 rdlrec selrdls%ROWTYPE;
3762 rdlrec1 selrdls1%ROWTYPE; -- for bug 2580808
3763 x_txn_accum_id NUMBER;
3764 row_processed NUMBER;
3765 commit_rows NUMBER;
3766 curr_rowid ROWID;
3767
3768 BEGIN
3769 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3770 x_err_stage := 'Accumulating All Revenues';
3771 pa_debug.debug('accum_revenue: ' || x_err_stage);
3772 END IF;
3773
3774 x_txn_accum_id :=0;
3775 x_err_code :=0;
3776 row_processed :=0;
3777 commit_rows :=0;
3778
3779
3780 FOR rdlrec IN selrdls LOOP
3781 -- pa_debug.debug('Each row, drrowid='||rdlrec.drrowid);
3782 -- pa_debug.debug('Each row, curr_rowid='||curr_rowid);
3783
3784 row_processed := row_processed + 1;
3785
3786 IF rdlrec.drrowid <> curr_rowid THEN
3787 -- pa_debug.debug('drrowid='||rdlrec.drrowid);
3788 -- pa_debug.debug('curr_rowid='||curr_rowid);
3789
3790 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3791 UPDATE
3792 pa_draft_revenues
3793 SET
3794 resource_accumulated_flag = 'Y',
3795 last_updated_by = x_last_updated_by,
3796 last_update_login = x_last_update_login,
3797 request_id = x_request_id,
3798 program_application_id = x_program_application_id,
3799 program_id = x_program_id
3800 WHERE
3801 ROWID = curr_rowid;
3802
3803 IF commit_rows >= pa_proj_accum_main.x_commit_size THEN
3804 COMMIT;
3805 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3806 pa_debug.debug('accum_revenue: ' || 'Number of Records Commited = '|| TO_CHAR(commit_rows));
3807 END IF;
3808 commit_rows := 0;
3809 END IF;
3810 END IF;
3811
3812 curr_rowid := rdlrec.drrowid;
3813 commit_rows := commit_rows + 1;
3814
3815 -- IF clause added for bug 2580808.
3816 -- IF x_mode <> 'I' THEN -- for bug 2650900
3817 IF (rdlrec.pa_period IS NULL OR rdlrec.gl_period IS NULL) THEN
3818 OPEN selrdls1(ROWIDTOCHAR(rdlrec.drrowid));
3819 FETCH selrdls1 INTO rdlrec1;
3820 CLOSE selrdls1;
3821 END IF;
3822 -- END IF; -- for bug 2650900
3823
3824 create_txn_accum(
3825 rdlrec.project_id,
3826 rdlrec.task_id,
3827 -- Commented out for 2580808
3828 -- rdlrec.pa_period,
3829 -- rdlrec.gl_period,
3830 NVL(rdlrec.pa_period,rdlrec1.pa_period1), -- added for bug 2580808
3831 NVL(rdlrec.gl_period,rdlrec1.gl_period1), --added for bug 2580808
3832 rdlrec.week_ending_date,
3833 rdlrec.month_ending_date,
3834 rdlrec.person_id,
3835 rdlrec.job_id,
3836 NULL, -- vendor_id
3837 rdlrec.expenditure_type,
3838 rdlrec.organization_id,
3839 rdlrec.non_labor_resource,
3840 rdlrec.non_labor_resource_org_id,
3841 rdlrec.expenditure_category,
3842 rdlrec.revenue_category,
3843 rdlrec.event_type, -- event_type
3844 rdlrec.event_type_classification, -- event_type_classification
3845 rdlrec.system_linkage_function,
3846 rdlrec.line_type, -- x_line_type = 'R' for RDL
3847 NULL, -- cost_ind_compiled_set_id
3848 rdlrec.rev_ind_compiled_set_id,
3849 rdlrec.inv_ind_compiled_set_id,
3850 NULL, -- cmt_ind_compiled_set_id
3851 x_txn_accum_id,
3852 x_err_stage,
3853 x_err_code);
3854
3855 -- Create a row for drilldown in pa_txn_accum_details Now
3856
3857 create_txn_accum_details(
3858 x_txn_accum_id,
3859 rdlrec.line_type, -- RDLS
3860 rdlrec.expenditure_item_id,
3861 rdlrec.line_num,
3862 rdlrec.event_num, -- Event Num
3863 NULL, -- CMT_LINE_ID
3864 rdlrec.project_id,
3865 rdlrec.task_id,
3866 x_err_stage,
3867 x_err_code);
3868
3869 -- Accume this row in txn_accum_id = x_txn_accum_id
3870 -- also create rows for drilldown
3871
3872 accum_rev_txn(
3873 x_txn_accum_id,
3874 rdlrec.amount,
3875 rdlrec.unit_of_measure,
3876 x_err_stage,
3877 x_err_code);
3878
3879 END LOOP;
3880
3881 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3882 UPDATE
3883 pa_draft_revenues
3884 SET
3885 resource_accumulated_flag = 'Y',
3886 last_updated_by = x_last_updated_by,
3887 last_update_login = x_last_update_login,
3888 request_id = x_request_id,
3889 program_application_id = x_program_application_id,
3890 program_id = x_program_id
3891 WHERE
3892 ROWID = curr_rowid;
3893
3894 COMMIT;
3895
3896 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3897 pa_debug.debug('accum_revenue: ' || 'Number of Draft Revenues Processed = '||TO_CHAR(row_processed));
3898 END IF;
3899
3900 EXCEPTION
3901 WHEN OTHERS THEN
3902 x_err_code := SQLCODE;
3903 RAISE;
3904
3905 END accum_revenue;
3906
3907 -- Procedure to accumulate the commitments
3908 -- The pa_period and the gl_period parameter are not passed to
3909 -- this routine, since the commitments are always accumulated
3910 -- in the current pa_period and gl_period
3911
3912
3913 -- Name: Accum_Commitments
3914 --
3915 -- History
3916 --
3917 --
3918 -- 12-FEB-99 jwhite For the accum_commitments procedure, added
3919 -- MC related design elements.
3920 -- Numerous changes were made to the procedure.
3921 --
3922 -- 04-MAR-99 jwhite Implemented latest design changes:
3923 -- 1) Removed all references to amount_delivered columns.
3924 -- 2) Added generation_error_flag to Update
3925 -- pa_project_accum_headers
3926 -- 3) Removed pa_debug design elements.
3927 --
3928 -- 03-DEC-2001 jwhite Bug 2119738
3929 -- The parameter list for the following was changed:
3930 -- pa_multi_currency_txn.get_currency_amounts. This change
3931 -- was not communicated to the Reporting team. System
3932 -- testing revealed this oversight.
3933 --
3934
3935 PROCEDURE accum_commitments
3936 ( x_start_project_id IN NUMBER,
3937 x_end_project_id IN NUMBER,
3938 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3939 x_err_stage IN OUT NOCOPY VARCHAR2,
3940 x_err_code IN OUT NOCOPY NUMBER)
3941 IS
3942 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3943 --
3944 -- Cursors ---------------------------------------------------
3945 --
3946 -- The cursor selcmts selects all PA_COMMITMENT_TXNS which
3947 -- satisfy the given parameters
3948
3949 CURSOR selcmts IS
3950 SELECT
3951 pct.cmt_line_id,
3952 pct.project_id,
3953 pct.task_id,
3954 pa_utils.GetWeekEnding(pct.expenditure_item_date) week_ending_date,
3955 LAST_DAY(pct.expenditure_item_date) month_ending_date,
3956 pct.pa_period,
3957 pct.gl_period,
3958 pct.organization_id,
3959 pct.vendor_id,
3960 pct.expenditure_type,
3961 pct.expenditure_category,
3962 pct.revenue_category,
3963 pct.system_linkage_function,
3964 pct.cmt_ind_compiled_set_id,
3965 pct.expenditure_item_date,
3966 pct.denom_currency_code,
3967 pct.denom_raw_cost,
3968 pct.denom_burdened_cost,
3969 pct.acct_currency_code,
3970 pct.acct_rate_date,
3971 pct.acct_rate_type,
3972 pct.acct_exchange_rate,
3973 pct.acct_raw_cost,
3974 pct.acct_burdened_cost,
3975 pct.receipt_currency_code,
3976 pct.receipt_currency_amount,
3977 pct.receipt_exchange_rate
3978 FROM
3979 pa_commitment_txns pct
3980 -- Bug#2634995 - removed the reference to pa_implentations as it is not used in the SQL
3981 -- ,pa_implementations pi
3982 WHERE
3983 pct.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
3984 AND pct.system_linkage_function||'' =
3985 NVL(x_system_linkage_function,pct.system_linkage_function);
3986
3987 -- This cursor retrives the Project Currency Code for the project.
3988 /* Bug# 2158736 - Included projfunc_currency_code in the cursor */
3989
3990 CURSOR l_project_curr_code_csr
3991 (l_project_id pa_projects.project_id%TYPE)
3992 IS
3993 SELECT project_currency_code,projfunc_currency_code
3994 FROM pa_projects p
3995 WHERE p.project_id = l_project_id;
3996
3997
3998 --
3999 -- Local Variables -------------------------------------------
4000 --
4001
4002 -- Procedure Variables
4003 l_proj_curr_OK VARCHAR2(1) := 'Y';
4004 l_project_curr_code pa_projects.project_currency_code%TYPE := NULL;
4005 l_sum_exception_code pa_project_accum_headers.sum_exception_code%TYPE := NULL;
4006
4007
4008 -- Main LOOP Variables
4009 cmtrec selcmts%ROWTYPE;
4010 x_txn_accum_id NUMBER;
4011 row_processed NUMBER;
4012 l_cmtrec_curr_OK VARCHAR2(1);
4013 l_cmt_rejection_code pa_commitment_txns.cmt_rejection_code%TYPE;
4014 l_err_msg VARCHAR2(2000);
4015
4016 -- Client Extension API Variables
4017 l_Project_Rate_Type pa_commitment_txns.project_rate_type%TYPE;
4018 l_Project_Rate_Date DATE;
4019 l_project_exch_rate NUMBER;
4020 l_Num_Rate NUMBER;
4021 l_Denom_Rate NUMBER;
4022 l_Converted_Amount NUMBER;
4023 l_Msg_Application fnd_application.application_short_name%TYPE;
4024 l_Msg_Data pa_commitment_txns.cmt_rejection_code%TYPE;
4025 l_Msg_Count NUMBER;
4026
4027 -- Multicurrency API Variables
4028 l_amount_out NUMBER;
4029 l_tot_cmt_raw_cost NUMBER;
4030 l_tot_cmt_burdened_cost NUMBER;
4031 l_status VARCHAR2(200) := NULL;
4032 l_stage NUMBER := NULL;
4033
4034 -- Bug 2119738: New Parameters
4035 l_SYSTEM_LINKAGE pa_expenditure_items_all.SYSTEM_LINKAGE_FUNCTION%TYPE :=NULL;
4036 l_PROJECT_RAW_COST NUMBER := NULL;
4037 l_PROJFUNC_CURR_CODE pa_projects.project_currency_code%TYPE := NULL;
4038 l_PROJFUNC_COST_RATE_TYPE pa_commitment_txns.project_rate_type%TYPE := NULL;
4039 l_PROJFUNC_COST_RATE_DATE DATE := NULL;
4040 l_PROJFUNC_COST_EXCH_RATE NUMBER := NULL;
4041
4042 -- added for FP.M
4043 l_PROJECT_BURDENED_COST NUMBER := NULL;
4044
4045 BEGIN
4046
4047 x_txn_accum_id :=0;
4048 x_err_code :=0;
4049
4050 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
4051 x_err_stage := 'Accumulating Commitments';
4052 row_processed :=0;
4053 pa_debug.debug('accum_commitments: ' || x_err_stage);
4054 END IF;
4055
4056
4057 -- Get Project Currency Code for Project
4058
4059 /* Bug# 2158736 - Included project functional currency code in the fetch */
4060
4061 OPEN l_project_curr_code_csr(x_start_project_id);
4062 FETCH l_project_curr_code_csr INTO l_project_curr_code,l_projfunc_curr_code;
4063 CLOSE l_project_curr_code_csr;
4064
4065 FOR cmtrec IN selcmts LOOP
4066
4067 row_processed := row_processed + 1;
4068
4069 -- Initialize Cmtrec Local Variables
4070
4071 l_cmtrec_curr_OK := 'Y';
4072 l_project_Rate_Type := NULL;
4073 /* Bug 8416706 Start Resetting the values*/
4074 -- l_project_Rate_Date := cmtrec.expenditure_item_date;
4075 l_project_Rate_Date := NULL; -- Issue 1
4076 l_PROJFUNC_COST_RATE_TYPE := NULL; -- Issue 2
4077 l_PROJFUNC_COST_RATE_DATE := NULL; -- Issue 2
4078 l_PROJFUNC_COST_EXCH_RATE := NULL; -- Issue 2
4079 /* Bug 8416706 End */
4080 l_project_exch_rate := NULL;
4081 l_Num_Rate := NULL;
4082 l_Denom_Rate := NULL;
4083 l_Converted_Amount := NULL;
4084 l_Msg_Application := NULL;
4085 l_Msg_Data := NULL;
4086 l_Msg_Count := NULL;
4087 l_amount_out := NULL;
4088 l_tot_cmt_raw_cost := NULL;
4089 l_tot_cmt_burdened_cost := NULL;
4090 l_status := NULL;
4091 l_stage := NULL;
4092 l_cmt_rejection_code := NULL;
4093 l_err_msg := NULL;
4094
4095
4096 create_txn_accum(
4097 cmtrec.project_id,
4098 cmtrec.task_id,
4099 cmtrec.pa_period,
4100 cmtrec.gl_period,
4101 cmtrec.week_ending_date,
4102 cmtrec.month_ending_date,
4103 NULL, -- person_id
4104 NULL, -- Job_id
4105 cmtrec.vendor_id,
4106 cmtrec.expenditure_type,
4107 cmtrec.organization_id,
4108 NULL, -- non_labor_resource
4109 NULL, -- non_labor_resource_org_id
4110 cmtrec.expenditure_category,
4111 cmtrec.revenue_category,
4112 NULL, -- event_type
4113 NULL, -- event_type_classification
4114 cmtrec.system_linkage_function,
4115 'M', -- x_line_type = 'M' for commitments
4116 NULL, -- cost_ind_compiled_set_id
4117 NULL, -- rev_ind_compiled_set_id
4118 NULL, -- inv_ind_compiled_set_id
4119 cmtrec.cmt_ind_compiled_set_id,
4120 x_txn_accum_id,
4121 x_err_stage,
4122 x_err_code);
4123
4124 -- Create a row for drilldown in pa_txn_accume_details Now
4125
4126 create_txn_accum_details(
4127 x_txn_accum_id,
4128 'M', -- pa_commitments_txns
4129 NULL, -- expenditure_item_id
4130 NULL, -- line_num
4131 NULL, -- event_num
4132 cmtrec.cmt_line_id,
4133 cmtrec.project_id,
4134 cmtrec.task_id,
4135 x_err_stage,
4136 x_err_code);
4137
4138
4139 --
4140 -- VALIDATION Currency Business Rules ------------
4141 --
4142 -- Go Here!
4143 --
4144 -- Set l_cmtrec_curr_OK to 'N' if any one of the rules are
4145 -- violated.
4146 --
4147
4148
4149 --
4150 -- PROJECT COLUMN DERIVATION --------------------
4151 --
4152
4153 IF (l_cmtrec_curr_OK = 'Y')
4154 THEN
4155
4156
4157 -- RAW COST Derivation
4158
4159 IF (l_cmtrec_curr_OK = 'Y')
4160 THEN
4161
4162 pa_multi_currency_txn.get_currency_amounts
4163 (p_project_curr_code => l_project_curr_code
4164 , p_ei_date => cmtrec.expenditure_item_date
4165 , p_task_id => cmtrec.task_id
4166 , p_denom_raw_cost => cmtrec.denom_raw_cost
4167 , p_denom_curr_code => cmtrec.denom_currency_code
4168 , p_acct_curr_code => cmtrec.acct_currency_code
4169 , p_accounted_flag => 'Y' /* Bug 1642321 manokuma */
4170 , p_acct_rate_date => cmtrec.acct_rate_date
4171 , p_acct_rate_type => cmtrec.acct_rate_type
4172 , p_acct_exch_rate => cmtrec.acct_exchange_rate
4173 , p_acct_raw_cost => cmtrec.acct_raw_cost
4174 , p_project_rate_type => l_project_rate_type
4175 , p_project_rate_date => l_project_rate_date
4176 , p_project_exch_rate => l_project_exch_rate
4177 , P_PROJFUNC_RAW_COST => l_amount_out
4178 , p_status => l_status
4179 , p_stage => l_stage
4180 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
4181 , P_PROJECT_RAW_COST => l_PROJECT_RAW_COST
4182 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
4183 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
4184 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
4185 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
4186 );
4187
4188 l_tot_cmt_raw_cost := l_amount_out;
4189
4190 IF (l_status IS NOT NULL) -- Error returned
4191 THEN
4192
4193 l_cmt_rejection_code := l_status;
4194 l_cmtrec_curr_OK := 'N';
4195
4196 END IF; --(l_status IS NOT NULL)
4197
4198 END IF; --RAW COST
4199
4200 -- BURDENED COST Derivation
4201
4202 IF (l_cmtrec_curr_OK = 'Y')
4203 THEN
4204
4205 pa_multi_currency_txn.get_currency_amounts
4206 (p_project_curr_code => l_project_curr_code
4207 , p_ei_date => cmtrec.expenditure_item_date
4208 , p_task_id => cmtrec.task_id
4209 , p_denom_raw_cost => cmtrec.denom_burdened_cost
4210 , p_denom_curr_code => cmtrec.denom_currency_code
4211 , p_acct_curr_code => cmtrec.acct_currency_code
4212 , p_accounted_flag => 'Y' /* Bug 1642321 manokuma */
4213 , p_acct_rate_date => cmtrec.acct_rate_date
4214 , p_acct_rate_type => cmtrec.acct_rate_type
4215 , p_acct_exch_rate => cmtrec.acct_exchange_rate
4216 , p_acct_raw_cost => cmtrec.acct_burdened_cost
4217 , p_project_rate_type => l_project_rate_type
4218 , p_project_rate_date => l_project_rate_date
4219 , p_project_exch_rate => l_project_exch_rate
4220 , P_PROJFUNC_RAW_COST => l_amount_out
4221 , p_status => l_status
4222 , p_stage => l_stage
4223 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
4224 , P_PROJECT_RAW_COST => l_PROJECT_BURDENED_COST /*l_PROJECT_RAW_COST bug 9076987*/
4225 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
4226 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
4227 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
4228 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
4229 );
4230
4231 l_tot_cmt_burdened_cost := l_amount_out;
4232
4233 -- added for FP.M
4234 -- l_PROJECT_BURDENED_COST := l_PROJECT_RAW_COST; Commented for bug 9076987
4235
4236 IF (l_status IS NOT NULL) -- Error returned
4237 THEN
4238
4239 l_cmt_rejection_code := l_status;
4240 l_cmtrec_curr_OK := 'N';
4241
4242 END IF; --(l_status IS NOT NULL)
4243
4244 END IF; --BURDENED COST
4245
4246 END IF; -- DERIVATION SUBsection
4247
4248 --
4249 -- UPDATE COMMITMENT ROW -------------------------------
4250 --
4251
4252 IF (l_cmtrec_curr_OK = 'Y')
4253 THEN
4254 UPDATE pa_commitment_txns
4255 SET tot_cmt_raw_cost = l_tot_cmt_raw_cost
4256 , tot_cmt_burdened_cost = l_tot_cmt_burdened_cost
4257 , project_currency_code = l_project_curr_code
4258 , project_rate_date = l_project_rate_date
4259 , project_rate_type = l_project_rate_type
4260 , project_exchange_rate = l_project_exch_rate
4261 , proj_raw_cost = l_PROJECT_RAW_COST /* added for FP.M proj_raw_cost stores raw cost in project currency */
4262 , proj_burdened_cost = l_PROJECT_BURDENED_COST /* added for FP.M proj_burdened_cost stores burdened cost in project currency */
4263 WHERE cmt_line_id = cmtrec.cmt_line_id;
4264
4265 ELSE
4266
4267 UPDATE pa_commitment_txns
4268 SET generation_error_flag = 'Y'
4269 , cmt_rejection_code = l_cmt_rejection_code
4270 WHERE cmt_line_id = cmtrec.cmt_line_id;
4271
4272 l_proj_curr_OK := 'N';
4273
4274 END IF; -- UPDATE COMMITMENT ROW
4275
4276
4277 -- Accume this row now for txn_accum_id = x_txn_accum_id
4278 -- also create rows for drilldown
4279
4280 accum_cmt_txn(
4281 x_txn_accum_id,
4282 l_tot_cmt_raw_cost,
4283 l_tot_cmt_burdened_cost,
4284 x_err_stage,
4285 x_err_code);
4286
4287 END LOOP; -- CMTREC Processing
4288
4289 COMMIT;
4290 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
4291 pa_debug.debug('accum_commitments: ' || 'Number of Commitments Processed = ' || TO_CHAR(row_processed));
4292 END IF;
4293
4294 --
4295 -- UPDATE PROJECT LOCKROW to Record Exception, If Any -----------
4296 --
4297
4298 IF (l_proj_curr_OK = 'Y')
4299 THEN
4300 l_sum_exception_code := NULL;
4301 ELSE
4302 l_sum_exception_code := 'PA_SUM_CMT_REJECTIONS';
4303 END IF;
4304
4305 UPDATE pa_project_accum_headers
4306 SET sum_exception_code = l_sum_exception_code
4307 WHERE project_id = x_start_project_id
4308 AND task_id = 0
4309 AND resource_list_id = 0
4310 AND resource_list_member_id = 0;
4311
4312
4313
4314 EXCEPTION
4315 WHEN OTHERS THEN
4316 x_err_code := SQLCODE;
4317 RAISE;
4318
4319 END accum_commitments;
4320
4321 END PA_TXN_ACCUMS;