[Home] [Help]
PACKAGE BODY: APPS.PA_TXN_ACCUMS
Source
1 PACKAGE BODY PA_TXN_ACCUMS AS
2 /* $Header: PATXNACB.pls 120.6.12000000.2 2007/04/10 09:34:19 degupta 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
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 'Yes'
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 x_err_stage := 'Creating commitment txns from PA_COMMITMENT_TXNS_V Source';
1363 pa_debug.debug('create_cmt_txns: ' || x_err_stage);
1364 END IF;
1365
1366 -- Insert Commitments from the Oracle Projects User-Defined PA_COMMITMENT_TXNS_V
1367 -- View.
1368
1369 if (x_start_project_id is not null) then -- refresh cmts for single prj
1370
1371 /* Added for commitment change request */
1372
1373 -- l_cur_pa_period := pa_accum_utils.Get_current_pa_period;
1374 -- l_cur_gl_period := pa_accum_utils.Get_current_gl_period;
1375
1376 -- bug 3746527
1377 select
1378 per.PERIOD_NAME,
1379 per.GL_PERIOD_NAME
1380 into
1381 l_cur_pa_period,
1382 l_cur_gl_period
1383 from
1384 PA_PROJECTS_ALL prj,
1385 PA_PERIODS_ALL per
1386 where
1387 prj.PROJECT_ID = x_start_project_id and
1388 nvl(per.ORG_ID, -1) = nvl(prj.ORG_ID, -1) and
1389 per.CURRENT_PA_PERIOD_FLAG = 'Y';
1390
1391 /* End of commitment change request*/
1392 IF x_use_tmp_table='N' THEN /*Added for bug 5635857*/
1393
1394 INSERT INTO pa_commitment_txns
1395 ( CMT_LINE_ID,
1396 PROJECT_ID,
1397 TASK_ID,
1398 TRANSACTION_SOURCE,
1399 LINE_TYPE,
1400 CMT_NUMBER,
1401 CMT_DISTRIBUTION_ID,
1402 CMT_HEADER_ID,
1403 DESCRIPTION,
1404 EXPENDITURE_ITEM_DATE,
1405 PA_PERIOD,
1406 GL_PERIOD,
1407 CMT_LINE_NUMBER,
1408 CMT_CREATION_DATE,
1409 CMT_APPROVED_DATE,
1410 CMT_REQUESTOR_NAME,
1411 CMT_BUYER_NAME,
1412 CMT_APPROVED_FLAG,
1413 CMT_PROMISED_DATE,
1414 CMT_NEED_BY_DATE,
1415 ORGANIZATION_ID,
1416 VENDOR_ID,
1417 VENDOR_NAME,
1418 EXPENDITURE_TYPE,
1419 EXPENDITURE_CATEGORY,
1420 REVENUE_CATEGORY,
1421 SYSTEM_LINKAGE_FUNCTION,
1422 UNIT_OF_MEASURE,
1423 UNIT_PRICE,
1424 CMT_IND_COMPILED_SET_ID,
1425 TOT_CMT_RAW_COST,
1426 TOT_CMT_BURDENED_COST,
1427 TOT_CMT_QUANTITY,
1428 QUANTITY_ORDERED,
1429 AMOUNT_ORDERED,
1430 ORIGINAL_QUANTITY_ORDERED,
1431 ORIGINAL_AMOUNT_ORDERED,
1432 QUANTITY_CANCELLED,
1433 AMOUNT_CANCELLED,
1434 QUANTITY_DELIVERED,
1435 AMOUNT_DELIVERED,
1436 QUANTITY_INVOICED,
1437 AMOUNT_INVOICED,
1438 QUANTITY_OUTSTANDING_DELIVERY,
1439 AMOUNT_OUTSTANDING_DELIVERY,
1440 QUANTITY_OUTSTANDING_INVOICE,
1441 AMOUNT_OUTSTANDING_INVOICE,
1442 QUANTITY_OVERBILLED,
1443 AMOUNT_OVERBILLED,
1444 ORIGINAL_TXN_REFERENCE1,
1445 ORIGINAL_TXN_REFERENCE2,
1446 ORIGINAL_TXN_REFERENCE3,
1447 LAST_UPDATE_DATE,
1448 LAST_UPDATED_BY,
1449 CREATION_DATE,
1450 CREATED_BY,
1451 LAST_UPDATE_LOGIN,
1452 REQUEST_ID,
1453 PROGRAM_APPLICATION_ID,
1454 PROGRAM_ID,
1455 PROGRAM_UPDATE_DATE,
1456 BURDEN_SUM_SOURCE_RUN_ID,
1457 BURDEN_SUM_DEST_RUN_ID,
1458 BURDEN_SUM_REJECTION_CODE,
1459 acct_raw_cost,
1460 acct_burdened_cost,
1461 denom_currency_code,
1462 denom_raw_cost,
1463 denom_burdened_cost,
1464 acct_currency_code,
1465 acct_rate_date,
1466 acct_rate_type,
1467 acct_exchange_rate,
1468 receipt_currency_code,
1469 receipt_currency_amount,
1470 receipt_exchange_rate,
1471 project_currency_code,
1472 project_rate_date,
1473 project_rate_type,
1474 project_exchange_rate,
1475 generation_error_flag,
1476 cmt_rejection_code,
1477 /* added in FP.M */
1478 INVENTORY_ITEM_ID,
1479 UOM_CODE,
1480 BOM_LABOR_RESOURCE_ID,
1481 BOM_EQUIPMENT_RESOURCE_ID,
1482 RESOURCE_CLASS
1483 )
1484 SELECT
1485 pa_txn_accums.cmt_line_id,
1486 pctv.project_id,
1487 pctv.task_id,
1488 pctv.transaction_source,
1489 decode(pctv.line_type,'P','P','R','R','I','I','O'),/*Bug 4050269*/
1490 pctv.cmt_number,
1491 pctv.cmt_distribution_id,
1492 pctv.cmt_header_id,
1493 pctv.description,
1494 pctv.expenditure_item_date,
1495 /* For commitment change request
1496 pctv.pa_period,
1497 pctv.gl_period, and added below variables*/
1498 l_cur_pa_period, /* Added for commitment change request*/
1499 l_cur_gl_period, /* Added for commitment change request*/
1500 pctv.cmt_line_number,
1501 pctv.cmt_creation_date,
1502 pctv.cmt_approved_date,
1503 pctv.cmt_requestor_name,
1504 pctv.cmt_buyer_name,
1505 pctv.cmt_approved_flag,
1506 pctv.cmt_promised_date,
1507 pctv.cmt_need_by_date,
1508 pctv.organization_id,
1509 pctv.vendor_id,
1510 pctv.vendor_name,
1511 pctv.expenditure_type,
1512 pctv.expenditure_category,
1513 pctv.revenue_category,
1514 pctv.system_linkage_function,
1515 pctv.unit_of_measure,
1516 pctv.unit_price,
1517 pctv.cmt_ind_compiled_set_id,
1518 TO_NUMBER(NULL),
1519 TO_NUMBER(NULL),
1520 pctv.tot_cmt_quantity,
1521 pctv.quantity_ordered,
1522 pctv.amount_ordered,
1523 pctv.original_quantity_ordered,
1524 pctv.original_amount_ordered,
1525 pctv.quantity_cancelled,
1526 pctv.amount_cancelled,
1527 pctv.quantity_delivered,
1528 TO_NUMBER(NULL),
1529 pctv.quantity_invoiced,
1530 pctv.amount_invoiced,
1531 pctv.quantity_outstanding_delivery,
1532 pctv.amount_outstanding_delivery,
1533 pctv.quantity_outstanding_invoice,
1534 pctv.amount_outstanding_invoice,
1535 pctv.quantity_overbilled,
1536 pctv.amount_overbilled,
1537 pctv.original_txn_reference1,
1538 pctv.original_txn_reference2,
1539 pctv.original_txn_reference3,
1540 SYSDATE,
1541 x_last_updated_by,
1542 SYSDATE,
1543 x_created_by,
1544 x_last_update_login,
1545 x_request_id,
1546 x_program_application_id,
1547 x_program_id,
1548 NULL,
1549 -9999,
1550 NULL,
1551 NULL,
1552 pctv.acct_raw_cost,
1553 pctv.acct_burdened_cost,
1554 pctv.denom_currency_code,
1555 pctv.denom_raw_cost,
1556 pctv.denom_burdened_cost,
1557 pctv.acct_currency_code,
1558 pctv.acct_rate_date,
1559 pctv.acct_rate_type,
1560 pctv.acct_exchange_rate,
1561 pctv.receipt_currency_code,
1562 pctv.receipt_currency_amount,
1563 pctv.receipt_exchange_rate,
1564 NULL,
1565 TO_DATE(NULL),
1566 NULL,
1567 TO_NUMBER(NULL),
1568 'N',
1569 NULL,
1570 /* added in FP.M */
1571 pctv.INVENTORY_ITEM_ID,
1572 pctv.UOM_CODE,
1573 pctv.wip_resource_id,
1574 pctv.wip_resource_id,
1575 pctv.resource_class
1576 FROM
1577 pa_commitment_txns_v pctv
1578 WHERE
1579 pctv.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
1580 AND NVL(pctv.system_linkage_function,'X') =
1581 NVL(NVL(x_system_linkage_function,pctv.system_linkage_function),'X');
1582
1583 ELSIF x_use_tmp_table='Y' THEN /*Start of addition for bug 5635857*/
1584 INSERT INTO pa_commitment_txns
1585 ( CMT_LINE_ID,
1586 PROJECT_ID,
1587 TASK_ID,
1588 TRANSACTION_SOURCE,
1589 LINE_TYPE,
1590 CMT_NUMBER,
1591 CMT_DISTRIBUTION_ID,
1592 CMT_HEADER_ID,
1593 DESCRIPTION,
1594 EXPENDITURE_ITEM_DATE,
1595 PA_PERIOD,
1596 GL_PERIOD,
1597 CMT_LINE_NUMBER,
1598 CMT_CREATION_DATE,
1599 CMT_APPROVED_DATE,
1600 CMT_REQUESTOR_NAME,
1601 CMT_BUYER_NAME,
1602 CMT_APPROVED_FLAG,
1603 CMT_PROMISED_DATE,
1604 CMT_NEED_BY_DATE,
1605 ORGANIZATION_ID,
1606 VENDOR_ID,
1607 VENDOR_NAME,
1608 EXPENDITURE_TYPE,
1609 EXPENDITURE_CATEGORY,
1610 REVENUE_CATEGORY,
1611 SYSTEM_LINKAGE_FUNCTION,
1612 UNIT_OF_MEASURE,
1613 UNIT_PRICE,
1614 CMT_IND_COMPILED_SET_ID,
1615 TOT_CMT_RAW_COST,
1616 TOT_CMT_BURDENED_COST,
1617 TOT_CMT_QUANTITY,
1618 QUANTITY_ORDERED,
1619 AMOUNT_ORDERED,
1620 ORIGINAL_QUANTITY_ORDERED,
1621 ORIGINAL_AMOUNT_ORDERED,
1622 QUANTITY_CANCELLED,
1623 AMOUNT_CANCELLED,
1624 QUANTITY_DELIVERED,
1625 AMOUNT_DELIVERED,
1626 QUANTITY_INVOICED,
1627 AMOUNT_INVOICED,
1628 QUANTITY_OUTSTANDING_DELIVERY,
1629 AMOUNT_OUTSTANDING_DELIVERY,
1630 QUANTITY_OUTSTANDING_INVOICE,
1631 AMOUNT_OUTSTANDING_INVOICE,
1632 QUANTITY_OVERBILLED,
1633 AMOUNT_OVERBILLED,
1634 ORIGINAL_TXN_REFERENCE1,
1635 ORIGINAL_TXN_REFERENCE2,
1636 ORIGINAL_TXN_REFERENCE3,
1637 LAST_UPDATE_DATE,
1638 LAST_UPDATED_BY,
1639 CREATION_DATE,
1640 CREATED_BY,
1641 LAST_UPDATE_LOGIN,
1642 REQUEST_ID,
1643 PROGRAM_APPLICATION_ID,
1644 PROGRAM_ID,
1645 PROGRAM_UPDATE_DATE,
1646 BURDEN_SUM_SOURCE_RUN_ID,
1647 BURDEN_SUM_DEST_RUN_ID,
1648 BURDEN_SUM_REJECTION_CODE,
1649 acct_raw_cost,
1650 acct_burdened_cost,
1651 denom_currency_code,
1652 denom_raw_cost,
1653 denom_burdened_cost,
1654 acct_currency_code,
1655 acct_rate_date,
1656 acct_rate_type,
1657 acct_exchange_rate,
1658 receipt_currency_code,
1659 receipt_currency_amount,
1660 receipt_exchange_rate,
1661 project_currency_code,
1662 project_rate_date,
1663 project_rate_type,
1664 project_exchange_rate,
1665 generation_error_flag,
1666 cmt_rejection_code,
1667 /* added in FP.M */
1668 INVENTORY_ITEM_ID,
1669 UOM_CODE,
1670 BOM_LABOR_RESOURCE_ID,
1671 BOM_EQUIPMENT_RESOURCE_ID,
1672 RESOURCE_CLASS
1673 )
1674 SELECT
1675 pa_txn_accums.cmt_line_id,
1676 pctv.project_id,
1677 pctv.task_id,
1678 pctv.transaction_source,
1679 pctv.line_type,/*Bug 4050269*/
1680 pctv.cmt_number,
1681 pctv.cmt_distribution_id,
1682 pctv.cmt_header_id,
1683 pctv.description,
1684 pctv.expenditure_item_date,
1685 /* For commitment change request
1686 pctv.pa_period,
1687 pctv.gl_period, and added below variables*/
1688 l_cur_pa_period, /* Added for commitment change request*/
1689 l_cur_gl_period, /* Added for commitment change request*/
1690 pctv.cmt_line_number,
1691 pctv.cmt_creation_date,
1692 pctv.cmt_approved_date,
1693 pctv.cmt_requestor_name,
1694 pctv.cmt_buyer_name,
1695 pctv.cmt_approved_flag,
1696 pctv.cmt_promised_date,
1697 pctv.cmt_need_by_date,
1698 pctv.organization_id,
1699 pctv.vendor_id,
1700 pctv.vendor_name,
1701 pctv.expenditure_type,
1702 pctv.expenditure_category,
1703 pctv.revenue_category,
1704 pctv.system_linkage_function,
1705 pctv.unit_of_measure,
1706 pctv.unit_price,
1707 pctv.cmt_ind_compiled_set_id,
1708 TO_NUMBER(NULL),
1709 TO_NUMBER(NULL),
1710 pctv.tot_cmt_quantity,
1711 pctv.quantity_ordered,
1712 pctv.amount_ordered,
1713 pctv.original_quantity_ordered,
1714 pctv.original_amount_ordered,
1715 pctv.quantity_cancelled,
1716 pctv.amount_cancelled,
1717 pctv.quantity_delivered,
1718 TO_NUMBER(NULL),
1719 pctv.quantity_invoiced,
1720 pctv.amount_invoiced,
1721 pctv.quantity_outstanding_delivery,
1722 pctv.amount_outstanding_delivery,
1723 pctv.quantity_outstanding_invoice,
1724 pctv.amount_outstanding_invoice,
1725 pctv.quantity_overbilled,
1726 pctv.amount_overbilled,
1727 pctv.original_txn_reference1,
1728 pctv.original_txn_reference2,
1729 pctv.original_txn_reference3,
1730 SYSDATE,
1731 x_last_updated_by,
1732 SYSDATE,
1733 x_created_by,
1734 x_last_update_login,
1735 x_request_id,
1736 x_program_application_id,
1737 x_program_id,
1738 NULL,
1739 -9999,
1740 NULL,
1741 NULL,
1742 pctv.acct_raw_cost,
1743 pctv.acct_burdened_cost,
1744 pctv.denom_currency_code,
1745 pctv.denom_raw_cost,
1746 pctv.denom_burdened_cost,
1747 pctv.acct_currency_code,
1748 pctv.acct_rate_date,
1749 pctv.acct_rate_type,
1750 pctv.acct_exchange_rate,
1751 pctv.receipt_currency_code,
1752 pctv.receipt_currency_amount,
1753 pctv.receipt_exchange_rate,
1754 NULL,
1755 TO_DATE(NULL),
1756 NULL,
1757 TO_NUMBER(NULL),
1758 'N',
1759 NULL,
1760 /* added in FP.M */
1761 pctv.INVENTORY_ITEM_ID,
1762 pctv.UOM_CODE,
1763 pctv.wip_resource_id,
1764 pctv.wip_resource_id,
1765 pctv.resource_class
1766 FROM
1767 pa_commitment_txns_tmp pctv
1768 WHERE
1769 pctv.project_id = x_start_project_id ;
1770
1771 end if; /* End of Addition for bug 5635857*/
1772
1773 commit;
1774
1775 else -- refresh commitments for all projects
1776
1777 declare
1778
1779 l_helper_batch_id number;
1780
1781 l_x number;
1782 l_project_id_1 number := null;
1783 l_project_id_2 number := null;
1784 l_project_id_3 number := null;
1785 l_project_id_4 number := null;
1786 l_project_id_5 number := null;
1787 l_project_id_6 number := null;
1788 l_project_id_7 number := null;
1789 l_project_id_8 number := null;
1790 l_project_id_9 number := null;
1791 l_project_id_10 number := null;
1792 l_project_id_11 number := null;
1793 l_project_id_12 number := null;
1794 l_project_id_13 number := null;
1795 l_project_id_14 number := null;
1796 l_project_id_15 number := null;
1797 l_project_id_16 number := null;
1798 l_project_id_17 number := null;
1799 l_project_id_18 number := null;
1800 l_project_id_19 number := null;
1801 l_project_id_20 number := null;
1802
1803 l_pa_period_1 varchar2(15) := null;
1804 l_pa_period_2 varchar2(15) := null;
1805 l_pa_period_3 varchar2(15) := null;
1806 l_pa_period_4 varchar2(15) := null;
1807 l_pa_period_5 varchar2(15) := null;
1808 l_pa_period_6 varchar2(15) := null;
1809 l_pa_period_7 varchar2(15) := null;
1810 l_pa_period_8 varchar2(15) := null;
1811 l_pa_period_9 varchar2(15) := null;
1812 l_pa_period_10 varchar2(15) := null;
1813 l_pa_period_11 varchar2(15) := null;
1814 l_pa_period_12 varchar2(15) := null;
1815 l_pa_period_13 varchar2(15) := null;
1816 l_pa_period_14 varchar2(15) := null;
1817 l_pa_period_15 varchar2(15) := null;
1818 l_pa_period_16 varchar2(15) := null;
1819 l_pa_period_17 varchar2(15) := null;
1820 l_pa_period_18 varchar2(15) := null;
1821 l_pa_period_19 varchar2(15) := null;
1822 l_pa_period_20 varchar2(15) := null;
1823
1824 l_gl_period_1 varchar2(15) := null;
1825 l_gl_period_2 varchar2(15) := null;
1826 l_gl_period_3 varchar2(15) := null;
1827 l_gl_period_4 varchar2(15) := null;
1828 l_gl_period_5 varchar2(15) := null;
1829 l_gl_period_6 varchar2(15) := null;
1830 l_gl_period_7 varchar2(15) := null;
1831 l_gl_period_8 varchar2(15) := null;
1832 l_gl_period_9 varchar2(15) := null;
1833 l_gl_period_10 varchar2(15) := null;
1834 l_gl_period_11 varchar2(15) := null;
1835 l_gl_period_12 varchar2(15) := null;
1836 l_gl_period_13 varchar2(15) := null;
1837 l_gl_period_14 varchar2(15) := null;
1838 l_gl_period_15 varchar2(15) := null;
1839 l_gl_period_16 varchar2(15) := null;
1840 l_gl_period_17 varchar2(15) := null;
1841 l_gl_period_18 varchar2(15) := null;
1842 l_gl_period_19 varchar2(15) := null;
1843 l_gl_period_20 varchar2(15) := null;
1844
1845 begin
1846
1847 l_helper_batch_id := x_end_project_id; -- overload of to_proj param
1848
1849 l_x := 1;
1850
1851 for c in (select PROJECT_ID,
1852 PA_PERIOD_NAME,
1853 GL_PERIOD_NAME
1854 from PJI_FM_EXTR_DREVN -- overload of drev table for cmt
1855 where BATCH_ID = l_helper_batch_id) loop
1856
1857 if (l_x = 1) then
1858 l_project_id_1 := c.PROJECT_ID;
1859 l_pa_period_1 := c.PA_PERIOD_NAME;
1860 l_gl_period_1 := c.GL_PERIOD_NAME;
1861 elsif (l_x = 2) then
1862 l_project_id_2 := c.PROJECT_ID;
1863 l_pa_period_2 := c.PA_PERIOD_NAME;
1864 l_gl_period_2 := c.GL_PERIOD_NAME;
1865 elsif (l_x = 3) then
1866 l_project_id_3 := c.PROJECT_ID;
1867 l_pa_period_3 := c.PA_PERIOD_NAME;
1868 l_gl_period_3 := c.GL_PERIOD_NAME;
1869 elsif (l_x = 4) then
1870 l_project_id_4 := c.PROJECT_ID;
1871 l_pa_period_4 := c.PA_PERIOD_NAME;
1872 l_gl_period_4 := c.GL_PERIOD_NAME;
1873 elsif (l_x = 5) then
1874 l_project_id_5 := c.PROJECT_ID;
1875 l_pa_period_5 := c.PA_PERIOD_NAME;
1876 l_gl_period_5 := c.GL_PERIOD_NAME;
1877 elsif (l_x = 6) then
1878 l_project_id_6 := c.PROJECT_ID;
1879 l_pa_period_6 := c.PA_PERIOD_NAME;
1880 l_gl_period_6 := c.GL_PERIOD_NAME;
1881 elsif (l_x = 7) then
1882 l_project_id_7 := c.PROJECT_ID;
1883 l_pa_period_7 := c.PA_PERIOD_NAME;
1884 l_gl_period_7 := c.GL_PERIOD_NAME;
1885 elsif (l_x = 8) then
1886 l_project_id_8 := c.PROJECT_ID;
1887 l_pa_period_8 := c.PA_PERIOD_NAME;
1888 l_gl_period_8 := c.GL_PERIOD_NAME;
1889 elsif (l_x = 9) then
1890 l_project_id_9 := c.PROJECT_ID;
1891 l_pa_period_9 := c.PA_PERIOD_NAME;
1892 l_gl_period_9 := c.GL_PERIOD_NAME;
1893 elsif (l_x = 10) then
1894 l_project_id_10 := c.PROJECT_ID;
1895 l_pa_period_10 := c.PA_PERIOD_NAME;
1896 l_gl_period_10 := c.GL_PERIOD_NAME;
1897 elsif (l_x = 11) then
1898 l_project_id_11 := c.PROJECT_ID;
1899 l_pa_period_11 := c.PA_PERIOD_NAME;
1900 l_gl_period_11 := c.GL_PERIOD_NAME;
1901 elsif (l_x = 12) then
1902 l_project_id_12 := c.PROJECT_ID;
1903 l_pa_period_12 := c.PA_PERIOD_NAME;
1904 l_gl_period_12 := c.GL_PERIOD_NAME;
1905 elsif (l_x = 13) then
1906 l_project_id_13 := c.PROJECT_ID;
1907 l_pa_period_13 := c.PA_PERIOD_NAME;
1908 l_gl_period_13 := c.GL_PERIOD_NAME;
1909 elsif (l_x = 14) then
1910 l_project_id_14 := c.PROJECT_ID;
1911 l_pa_period_14 := c.PA_PERIOD_NAME;
1912 l_gl_period_14 := c.GL_PERIOD_NAME;
1913 elsif (l_x = 15) then
1914 l_project_id_15 := c.PROJECT_ID;
1915 l_pa_period_15 := c.PA_PERIOD_NAME;
1916 l_gl_period_15 := c.GL_PERIOD_NAME;
1917 elsif (l_x = 16) then
1918 l_project_id_16 := c.PROJECT_ID;
1919 l_pa_period_16 := c.PA_PERIOD_NAME;
1920 l_gl_period_16 := c.GL_PERIOD_NAME;
1921 elsif (l_x = 17) then
1922 l_project_id_17 := c.PROJECT_ID;
1923 l_pa_period_17 := c.PA_PERIOD_NAME;
1924 l_gl_period_17 := c.GL_PERIOD_NAME;
1925 elsif (l_x = 18) then
1926 l_project_id_18 := c.PROJECT_ID;
1927 l_pa_period_18 := c.PA_PERIOD_NAME;
1928 l_gl_period_18 := c.GL_PERIOD_NAME;
1929 elsif (l_x = 19) then
1930 l_project_id_19 := c.PROJECT_ID;
1931 l_pa_period_19 := c.PA_PERIOD_NAME;
1932 l_gl_period_19 := c.GL_PERIOD_NAME;
1933 elsif (l_x = 20) then
1934 l_project_id_20 := c.PROJECT_ID;
1935 l_pa_period_20 := c.PA_PERIOD_NAME;
1936 l_gl_period_20 := c.GL_PERIOD_NAME;
1937 else
1938 dbms_standard.raise_application_error(-20010, 'batch too large');
1939 end if;
1940
1941 l_x := l_x + 1;
1942
1943 end loop;
1944
1945 IF x_use_tmp_table='N' THEN /*Added for bug 5635857*/
1946
1947 insert into PA_COMMITMENT_TXNS
1948 (
1949 CMT_LINE_ID,
1950 PROJECT_ID,
1951 TASK_ID,
1952 TRANSACTION_SOURCE,
1953 LINE_TYPE,
1954 CMT_NUMBER,
1955 CMT_DISTRIBUTION_ID,
1956 CMT_HEADER_ID,
1957 DESCRIPTION,
1958 EXPENDITURE_ITEM_DATE,
1959 PA_PERIOD,
1960 GL_PERIOD,
1961 CMT_LINE_NUMBER,
1962 CMT_CREATION_DATE,
1963 CMT_APPROVED_DATE,
1964 CMT_REQUESTOR_NAME,
1965 CMT_BUYER_NAME,
1966 CMT_APPROVED_FLAG,
1967 CMT_PROMISED_DATE,
1968 CMT_NEED_BY_DATE,
1969 ORGANIZATION_ID,
1970 VENDOR_ID,
1971 VENDOR_NAME,
1972 EXPENDITURE_TYPE,
1973 EXPENDITURE_CATEGORY,
1974 REVENUE_CATEGORY,
1975 SYSTEM_LINKAGE_FUNCTION,
1976 UNIT_OF_MEASURE,
1977 UNIT_PRICE,
1978 CMT_IND_COMPILED_SET_ID,
1979 TOT_CMT_RAW_COST,
1980 TOT_CMT_BURDENED_COST,
1981 TOT_CMT_QUANTITY,
1982 QUANTITY_ORDERED,
1983 AMOUNT_ORDERED,
1984 ORIGINAL_QUANTITY_ORDERED,
1985 ORIGINAL_AMOUNT_ORDERED,
1986 QUANTITY_CANCELLED,
1987 AMOUNT_CANCELLED,
1988 QUANTITY_DELIVERED,
1989 AMOUNT_DELIVERED,
1990 QUANTITY_INVOICED,
1991 AMOUNT_INVOICED,
1992 QUANTITY_OUTSTANDING_DELIVERY,
1993 AMOUNT_OUTSTANDING_DELIVERY,
1994 QUANTITY_OUTSTANDING_INVOICE,
1995 AMOUNT_OUTSTANDING_INVOICE,
1996 QUANTITY_OVERBILLED,
1997 AMOUNT_OVERBILLED,
1998 ORIGINAL_TXN_REFERENCE1,
1999 ORIGINAL_TXN_REFERENCE2,
2000 ORIGINAL_TXN_REFERENCE3,
2001 LAST_UPDATE_DATE,
2002 LAST_UPDATED_BY,
2003 CREATION_DATE,
2004 CREATED_BY,
2005 LAST_UPDATE_LOGIN,
2006 REQUEST_ID,
2007 PROGRAM_APPLICATION_ID,
2008 PROGRAM_ID,
2009 PROGRAM_UPDATE_DATE,
2010 BURDEN_SUM_SOURCE_RUN_ID,
2011 BURDEN_SUM_DEST_RUN_ID,
2012 BURDEN_SUM_REJECTION_CODE,
2013 ACCT_RAW_COST,
2014 ACCT_BURDENED_COST,
2015 DENOM_CURRENCY_CODE,
2016 DENOM_RAW_COST,
2017 DENOM_BURDENED_COST,
2018 ACCT_CURRENCY_CODE,
2019 ACCT_RATE_DATE,
2020 ACCT_RATE_TYPE,
2021 ACCT_EXCHANGE_RATE,
2022 RECEIPT_CURRENCY_CODE,
2023 RECEIPT_CURRENCY_AMOUNT,
2024 RECEIPT_EXCHANGE_RATE,
2025 PROJECT_CURRENCY_CODE,
2026 PROJECT_RATE_DATE,
2027 PROJECT_RATE_TYPE,
2028 PROJECT_EXCHANGE_RATE,
2029 GENERATION_ERROR_FLAG,
2030 CMT_REJECTION_CODE,
2031 INVENTORY_ITEM_ID,
2032 UOM_CODE,
2033 BOM_LABOR_RESOURCE_ID,
2034 BOM_EQUIPMENT_RESOURCE_ID,
2035 RESOURCE_CLASS
2036 )
2037 select /*+ push_pred(pctv) */
2038 PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
2039 pctv.PROJECT_ID,
2040 pctv.TASK_ID,
2041 pctv.TRANSACTION_SOURCE,
2042 decode(pctv.LINE_TYPE,
2043 'P', 'P',
2044 'R', 'R',
2045 'I', 'I',
2046 'O') LINE_TYPE,
2047 pctv.CMT_NUMBER,
2048 pctv.CMT_DISTRIBUTION_ID,
2049 pctv.CMT_HEADER_ID,
2050 pctv.DESCRIPTION,
2051 pctv.EXPENDITURE_ITEM_DATE,
2052 decode(pctv.PROJECT_ID,
2053 l_project_id_1, l_pa_period_1,
2054 l_project_id_2, l_pa_period_2,
2055 l_project_id_3, l_pa_period_3,
2056 l_project_id_4, l_pa_period_4,
2057 l_project_id_5, l_pa_period_5,
2058 l_project_id_6, l_pa_period_6,
2059 l_project_id_7, l_pa_period_7,
2060 l_project_id_8, l_pa_period_8,
2061 l_project_id_9, l_pa_period_9,
2062 l_project_id_10, l_pa_period_10,
2063 l_project_id_11, l_pa_period_11,
2064 l_project_id_12, l_pa_period_12,
2065 l_project_id_13, l_pa_period_13,
2066 l_project_id_14, l_pa_period_14,
2067 l_project_id_15, l_pa_period_15,
2068 l_project_id_16, l_pa_period_16,
2069 l_project_id_17, l_pa_period_17,
2070 l_project_id_18, l_pa_period_18,
2071 l_project_id_19, l_pa_period_19,
2072 l_project_id_20, l_pa_period_20) PA_PERIOD,
2073 decode(pctv.PROJECT_ID,
2074 l_project_id_1, l_gl_period_1,
2075 l_project_id_2, l_gl_period_2,
2076 l_project_id_3, l_gl_period_3,
2077 l_project_id_4, l_gl_period_4,
2078 l_project_id_5, l_gl_period_5,
2079 l_project_id_6, l_gl_period_6,
2080 l_project_id_7, l_gl_period_7,
2081 l_project_id_8, l_gl_period_8,
2082 l_project_id_9, l_gl_period_9,
2083 l_project_id_10, l_gl_period_10,
2084 l_project_id_11, l_gl_period_11,
2085 l_project_id_12, l_gl_period_12,
2086 l_project_id_13, l_gl_period_13,
2087 l_project_id_14, l_gl_period_14,
2088 l_project_id_15, l_gl_period_15,
2089 l_project_id_16, l_gl_period_16,
2090 l_project_id_17, l_gl_period_17,
2091 l_project_id_18, l_gl_period_18,
2092 l_project_id_19, l_gl_period_19,
2093 l_project_id_20, l_gl_period_20) GL_PERIOD,
2094 pctv.CMT_LINE_NUMBER,
2095 pctv.CMT_CREATION_DATE,
2096 pctv.CMT_APPROVED_DATE,
2097 pctv.CMT_REQUESTOR_NAME,
2098 pctv.CMT_BUYER_NAME,
2099 pctv.CMT_APPROVED_FLAG,
2100 pctv.CMT_PROMISED_DATE,
2101 pctv.CMT_NEED_BY_DATE,
2102 pctv.ORGANIZATION_ID,
2103 pctv.VENDOR_ID,
2104 pctv.VENDOR_NAME,
2105 pctv.EXPENDITURE_TYPE,
2106 pctv.EXPENDITURE_CATEGORY,
2107 pctv.REVENUE_CATEGORY,
2108 pctv.SYSTEM_LINKAGE_FUNCTION,
2109 pctv.UNIT_OF_MEASURE,
2110 pctv.UNIT_PRICE,
2111 pctv.CMT_IND_COMPILED_SET_ID,
2112 to_number(null) TOT_CMT_RAW_COST,
2113 to_number(null) TOT_CMT_BURDENED_COST,
2114 pctv.TOT_CMT_QUANTITY,
2115 pctv.QUANTITY_ORDERED,
2116 pctv.AMOUNT_ORDERED,
2117 pctv.ORIGINAL_QUANTITY_ORDERED,
2118 pctv.ORIGINAL_AMOUNT_ORDERED,
2119 pctv.QUANTITY_CANCELLED,
2120 pctv.AMOUNT_CANCELLED,
2121 pctv.QUANTITY_DELIVERED,
2122 to_number(null) AMOUNT_DELIVERED,
2123 pctv.QUANTITY_INVOICED,
2124 pctv.AMOUNT_INVOICED,
2125 pctv.QUANTITY_OUTSTANDING_DELIVERY,
2126 pctv.AMOUNT_OUTSTANDING_DELIVERY,
2127 pctv.QUANTITY_OUTSTANDING_INVOICE,
2128 pctv.AMOUNT_OUTSTANDING_INVOICE,
2129 pctv.QUANTITY_OVERBILLED,
2130 pctv.AMOUNT_OVERBILLED,
2131 pctv.ORIGINAL_TXN_REFERENCE1,
2132 pctv.ORIGINAL_TXN_REFERENCE2,
2133 pctv.ORIGINAL_TXN_REFERENCE3,
2134 sysdate LAST_UPDATE_DATE,
2135 x_last_updated_by LAST_UPDATED_BY,
2136 sysdate CREATION_DATE,
2137 x_created_by CREATED_BY,
2138 x_last_update_login LAST_UPDATE_LOGIN,
2139 x_request_id REQUEST_ID,
2140 x_program_application_id PROGRAM_APPLICATION_ID,
2141 x_program_id PROGRAM_ID,
2142 null PROGRAM_UPDATE_DATE,
2143 -9999 BURDEN_SUM_SOURCE_RUN_ID,
2144 null BURDEN_SUM_DEST_RUN_ID,
2145 null BURDEN_SUM_REJECTION_CODE,
2146 pctv.ACCT_RAW_COST,
2147 pctv.ACCT_BURDENED_COST,
2148 pctv.DENOM_CURRENCY_CODE,
2149 pctv.DENOM_RAW_COST,
2150 pctv.DENOM_BURDENED_COST,
2151 pctv.ACCT_CURRENCY_CODE,
2152 pctv.ACCT_RATE_DATE,
2153 pctv.ACCT_RATE_TYPE,
2154 pctv.ACCT_EXCHANGE_RATE,
2155 pctv.RECEIPT_CURRENCY_CODE,
2156 pctv.RECEIPT_CURRENCY_AMOUNT,
2157 pctv.RECEIPT_EXCHANGE_RATE,
2158 null PROJECT_CURRENCY_CODE,
2159 to_date(null) PROJECT_RATE_DATE,
2160 null PROJECT_RATE_TYPE,
2161 to_number(null) PROJECT_EXCHANGE_RATE,
2162 'N' GENERATION_ERROR_FLAG,
2163 null CMT_REJECTION_CODE,
2164 pctv.INVENTORY_ITEM_ID,
2165 pctv.UOM_CODE,
2166 pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
2167 pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
2168 pctv.RESOURCE_CLASS
2169 from
2170 PA_COMMITMENT_TXNS_V pctv
2171 where
2172 pctv.PROJECT_ID = l_project_id_1;
2173
2174 /* single project batch
2175
2176 pctv.PROJECT_ID in (l_project_id_1,
2177 l_project_id_2,
2178 l_project_id_3,
2179 l_project_id_4,
2180 l_project_id_5,
2181 l_project_id_6,
2182 l_project_id_7,
2183 l_project_id_8,
2184 l_project_id_9,
2185 l_project_id_10,
2186 l_project_id_11,
2187 l_project_id_12,
2188 l_project_id_13,
2189 l_project_id_14,
2190 l_project_id_15,
2191 l_project_id_16,
2192 l_project_id_17,
2193 l_project_id_18,
2194 l_project_id_19,
2195 l_project_id_20);
2196 */
2197
2198 /* Start of Addition for bug 5635857*/
2199 ELSIF x_use_tmp_table='Y' THEN
2200 insert into PA_COMMITMENT_TXNS
2201 (
2202 CMT_LINE_ID,
2203 PROJECT_ID,
2204 TASK_ID,
2205 TRANSACTION_SOURCE,
2206 LINE_TYPE,
2207 CMT_NUMBER,
2208 CMT_DISTRIBUTION_ID,
2209 CMT_HEADER_ID,
2210 DESCRIPTION,
2211 EXPENDITURE_ITEM_DATE,
2212 PA_PERIOD,
2213 GL_PERIOD,
2214 CMT_LINE_NUMBER,
2215 CMT_CREATION_DATE,
2216 CMT_APPROVED_DATE,
2217 CMT_REQUESTOR_NAME,
2218 CMT_BUYER_NAME,
2219 CMT_APPROVED_FLAG,
2220 CMT_PROMISED_DATE,
2221 CMT_NEED_BY_DATE,
2222 ORGANIZATION_ID,
2223 VENDOR_ID,
2224 VENDOR_NAME,
2225 EXPENDITURE_TYPE,
2226 EXPENDITURE_CATEGORY,
2227 REVENUE_CATEGORY,
2228 SYSTEM_LINKAGE_FUNCTION,
2229 UNIT_OF_MEASURE,
2230 UNIT_PRICE,
2231 CMT_IND_COMPILED_SET_ID,
2232 TOT_CMT_RAW_COST,
2233 TOT_CMT_BURDENED_COST,
2234 TOT_CMT_QUANTITY,
2235 QUANTITY_ORDERED,
2236 AMOUNT_ORDERED,
2237 ORIGINAL_QUANTITY_ORDERED,
2238 ORIGINAL_AMOUNT_ORDERED,
2239 QUANTITY_CANCELLED,
2240 AMOUNT_CANCELLED,
2241 QUANTITY_DELIVERED,
2242 AMOUNT_DELIVERED,
2243 QUANTITY_INVOICED,
2244 AMOUNT_INVOICED,
2245 QUANTITY_OUTSTANDING_DELIVERY,
2246 AMOUNT_OUTSTANDING_DELIVERY,
2247 QUANTITY_OUTSTANDING_INVOICE,
2248 AMOUNT_OUTSTANDING_INVOICE,
2249 QUANTITY_OVERBILLED,
2250 AMOUNT_OVERBILLED,
2251 ORIGINAL_TXN_REFERENCE1,
2252 ORIGINAL_TXN_REFERENCE2,
2253 ORIGINAL_TXN_REFERENCE3,
2254 LAST_UPDATE_DATE,
2255 LAST_UPDATED_BY,
2256 CREATION_DATE,
2257 CREATED_BY,
2258 LAST_UPDATE_LOGIN,
2259 REQUEST_ID,
2260 PROGRAM_APPLICATION_ID,
2261 PROGRAM_ID,
2262 PROGRAM_UPDATE_DATE,
2263 BURDEN_SUM_SOURCE_RUN_ID,
2264 BURDEN_SUM_DEST_RUN_ID,
2265 BURDEN_SUM_REJECTION_CODE,
2266 ACCT_RAW_COST,
2267 ACCT_BURDENED_COST,
2268 DENOM_CURRENCY_CODE,
2269 DENOM_RAW_COST,
2270 DENOM_BURDENED_COST,
2271 ACCT_CURRENCY_CODE,
2272 ACCT_RATE_DATE,
2273 ACCT_RATE_TYPE,
2274 ACCT_EXCHANGE_RATE,
2275 RECEIPT_CURRENCY_CODE,
2276 RECEIPT_CURRENCY_AMOUNT,
2277 RECEIPT_EXCHANGE_RATE,
2278 PROJECT_CURRENCY_CODE,
2279 PROJECT_RATE_DATE,
2280 PROJECT_RATE_TYPE,
2281 PROJECT_EXCHANGE_RATE,
2282 GENERATION_ERROR_FLAG,
2283 CMT_REJECTION_CODE,
2284 INVENTORY_ITEM_ID,
2285 UOM_CODE,
2286 BOM_LABOR_RESOURCE_ID,
2287 BOM_EQUIPMENT_RESOURCE_ID,
2288 RESOURCE_CLASS
2289 )
2290 select
2291 PA_COMMITMENT_TXNS_S.NEXTVAL CMT_LINE_ID,
2292 pctv.PROJECT_ID,
2293 pctv.TASK_ID,
2294 pctv.TRANSACTION_SOURCE,
2295 pctv.LINE_TYPE,
2296 pctv.CMT_NUMBER,
2297 pctv.CMT_DISTRIBUTION_ID,
2298 pctv.CMT_HEADER_ID,
2299 pctv.DESCRIPTION,
2300 pctv.EXPENDITURE_ITEM_DATE,
2301 decode(pctv.PROJECT_ID,
2302 l_project_id_1, l_pa_period_1,
2303 l_project_id_2, l_pa_period_2,
2304 l_project_id_3, l_pa_period_3,
2305 l_project_id_4, l_pa_period_4,
2306 l_project_id_5, l_pa_period_5,
2307 l_project_id_6, l_pa_period_6,
2308 l_project_id_7, l_pa_period_7,
2309 l_project_id_8, l_pa_period_8,
2310 l_project_id_9, l_pa_period_9,
2311 l_project_id_10, l_pa_period_10,
2312 l_project_id_11, l_pa_period_11,
2313 l_project_id_12, l_pa_period_12,
2314 l_project_id_13, l_pa_period_13,
2315 l_project_id_14, l_pa_period_14,
2316 l_project_id_15, l_pa_period_15,
2317 l_project_id_16, l_pa_period_16,
2318 l_project_id_17, l_pa_period_17,
2319 l_project_id_18, l_pa_period_18,
2320 l_project_id_19, l_pa_period_19,
2321 l_project_id_20, l_pa_period_20) PA_PERIOD,
2322 decode(pctv.PROJECT_ID,
2323 l_project_id_1, l_gl_period_1,
2324 l_project_id_2, l_gl_period_2,
2325 l_project_id_3, l_gl_period_3,
2326 l_project_id_4, l_gl_period_4,
2327 l_project_id_5, l_gl_period_5,
2328 l_project_id_6, l_gl_period_6,
2329 l_project_id_7, l_gl_period_7,
2330 l_project_id_8, l_gl_period_8,
2331 l_project_id_9, l_gl_period_9,
2332 l_project_id_10, l_gl_period_10,
2333 l_project_id_11, l_gl_period_11,
2334 l_project_id_12, l_gl_period_12,
2335 l_project_id_13, l_gl_period_13,
2336 l_project_id_14, l_gl_period_14,
2337 l_project_id_15, l_gl_period_15,
2338 l_project_id_16, l_gl_period_16,
2339 l_project_id_17, l_gl_period_17,
2340 l_project_id_18, l_gl_period_18,
2341 l_project_id_19, l_gl_period_19,
2342 l_project_id_20, l_gl_period_20) GL_PERIOD,
2343 pctv.CMT_LINE_NUMBER,
2344 pctv.CMT_CREATION_DATE,
2345 pctv.CMT_APPROVED_DATE,
2346 pctv.CMT_REQUESTOR_NAME,
2347 pctv.CMT_BUYER_NAME,
2348 pctv.CMT_APPROVED_FLAG,
2349 pctv.CMT_PROMISED_DATE,
2350 pctv.CMT_NEED_BY_DATE,
2351 pctv.ORGANIZATION_ID,
2352 pctv.VENDOR_ID,
2353 pctv.VENDOR_NAME,
2354 pctv.EXPENDITURE_TYPE,
2355 pctv.EXPENDITURE_CATEGORY,
2356 pctv.REVENUE_CATEGORY,
2357 pctv.SYSTEM_LINKAGE_FUNCTION,
2358 pctv.UNIT_OF_MEASURE,
2359 pctv.UNIT_PRICE,
2360 pctv.CMT_IND_COMPILED_SET_ID,
2361 to_number(null) TOT_CMT_RAW_COST,
2362 to_number(null) TOT_CMT_BURDENED_COST,
2363 pctv.TOT_CMT_QUANTITY,
2364 pctv.QUANTITY_ORDERED,
2365 pctv.AMOUNT_ORDERED,
2366 pctv.ORIGINAL_QUANTITY_ORDERED,
2367 pctv.ORIGINAL_AMOUNT_ORDERED,
2368 pctv.QUANTITY_CANCELLED,
2369 pctv.AMOUNT_CANCELLED,
2370 pctv.QUANTITY_DELIVERED,
2371 to_number(null) AMOUNT_DELIVERED,
2372 pctv.QUANTITY_INVOICED,
2373 pctv.AMOUNT_INVOICED,
2374 pctv.QUANTITY_OUTSTANDING_DELIVERY,
2375 pctv.AMOUNT_OUTSTANDING_DELIVERY,
2376 pctv.QUANTITY_OUTSTANDING_INVOICE,
2377 pctv.AMOUNT_OUTSTANDING_INVOICE,
2378 pctv.QUANTITY_OVERBILLED,
2379 pctv.AMOUNT_OVERBILLED,
2380 pctv.ORIGINAL_TXN_REFERENCE1,
2381 pctv.ORIGINAL_TXN_REFERENCE2,
2382 pctv.ORIGINAL_TXN_REFERENCE3,
2383 sysdate LAST_UPDATE_DATE,
2384 x_last_updated_by LAST_UPDATED_BY,
2385 sysdate CREATION_DATE,
2386 x_created_by CREATED_BY,
2387 x_last_update_login LAST_UPDATE_LOGIN,
2388 x_request_id REQUEST_ID,
2389 x_program_application_id PROGRAM_APPLICATION_ID,
2390 x_program_id PROGRAM_ID,
2391 null PROGRAM_UPDATE_DATE,
2392 -9999 BURDEN_SUM_SOURCE_RUN_ID,
2393 null BURDEN_SUM_DEST_RUN_ID,
2394 null BURDEN_SUM_REJECTION_CODE,
2395 pctv.ACCT_RAW_COST,
2396 pctv.ACCT_BURDENED_COST,
2397 pctv.DENOM_CURRENCY_CODE,
2398 pctv.DENOM_RAW_COST,
2399 pctv.DENOM_BURDENED_COST,
2400 pctv.ACCT_CURRENCY_CODE,
2401 pctv.ACCT_RATE_DATE,
2402 pctv.ACCT_RATE_TYPE,
2403 pctv.ACCT_EXCHANGE_RATE,
2404 pctv.RECEIPT_CURRENCY_CODE,
2405 pctv.RECEIPT_CURRENCY_AMOUNT,
2406 pctv.RECEIPT_EXCHANGE_RATE,
2407 null PROJECT_CURRENCY_CODE,
2408 to_date(null) PROJECT_RATE_DATE,
2409 null PROJECT_RATE_TYPE,
2410 to_number(null) PROJECT_EXCHANGE_RATE,
2411 'N' GENERATION_ERROR_FLAG,
2412 null CMT_REJECTION_CODE,
2413 pctv.INVENTORY_ITEM_ID,
2414 pctv.UOM_CODE,
2415 pctv.WIP_RESOURCE_ID BOM_LABOR_RESOURCE_ID,
2416 pctv.WIP_RESOURCE_ID BOM_EQUIPMENT_RESOURCE_ID,
2417 pctv.RESOURCE_CLASS
2418 from
2419 pa_commitment_txns_tmp pctv
2420 WHERE
2421 pctv.PROJECT_ID = l_project_id_1;
2422 end if;
2423 /* End of Addition for bug 5635857*/
2424
2425 end;
2426
2427 end if;
2428
2429 END IF; -- ( PA_PROJ_ACCUM_MAIN.G_GMS_Enabled = 'Y'
2430
2431 -- End: Grants Management Integrated Commitment Processing ---------------------
2432
2433
2434 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2435 pa_debug.debug('create_cmt_txns: ' || 'Records Inserted = '||TO_CHAR(SQL%ROWCOUNT));
2436 END IF;
2437
2438 EXCEPTION
2439 WHEN OTHERS THEN
2440 x_err_code := SQLCODE;
2441 RAISE;
2442
2443 END create_cmt_txns;
2444
2445 -- This procedure accumulates the actual cost txn in the given
2446 -- txn_accum_id. The quantity figures are accumulated in
2447 -- either tot_quantity or tot_labor_hours column on
2448 -- pa_txn_accum depending if the UNIT_OF_MEASURE is 'HOURS'
2449 -- We are assuming that for a given expenditure type we
2450 -- can have only one and only one UNIT_OF_MEASURE
2451 -- Since expenditure types is one of the transaction attributes
2452 -- for a row in pa_txn_accum it will have only one and only one
2453 -- value for UNIT_OF_MEASURE
2454
2455 PROCEDURE accum_act_txn
2456 ( x_txn_accum_id IN NUMBER,
2457 x_tot_raw_cost IN NUMBER,
2458 x_tot_burdened_cost IN NUMBER,
2459 x_tot_quantity IN NUMBER,
2460 x_tot_billable_raw_cost IN NUMBER,
2461 x_tot_billable_burdened_cost IN NUMBER,
2462 x_tot_billable_quantity IN NUMBER,
2463 x_unit_of_measure IN VARCHAR2,
2464 x_err_stage IN OUT NOCOPY VARCHAR2,
2465 x_err_code IN OUT NOCOPY NUMBER)
2466 IS
2467 BEGIN
2468 x_err_code :=0;
2469
2470 -- for actual costs x_unit_of_measure will always be not null
2471 -- If the transactions are labor transactions than add them to
2472 -- to labor_hours and billable_labor_hours as well
2473
2474 x_err_stage := 'Accumulating Actual Cost transaction';
2475
2476 UPDATE pa_txn_accum pta
2477 SET pta.i_tot_raw_cost = DECODE(raw_cost_flag,'Y',
2478 (NVL(i_tot_raw_cost, 0) + x_tot_raw_cost),
2479 NULL),
2480 pta.i_tot_burdened_cost = DECODE(burdened_cost_flag,'Y',
2481 (NVL(i_tot_burdened_cost, 0) +
2482 x_tot_burdened_cost),NULL),
2483 pta.i_tot_quantity = DECODE(quantity_flag,'Y',
2484 (NVL(i_tot_quantity, 0) +
2485 x_tot_quantity),NULL),
2486 pta.i_tot_labor_hours = DECODE(labor_hours_flag,'Y',
2487 (NVL(i_tot_labor_hours,0) +
2488 DECODE(pta.system_linkage_function,
2489 'OT',
2490 x_tot_quantity,
2491 'ST',
2492 x_tot_quantity,
2493 0)),NULL),
2494 pta.i_tot_billable_raw_cost = DECODE(billable_raw_cost_flag,'Y',
2495 (NVL(i_tot_billable_raw_cost, 0) +
2496 x_tot_billable_raw_cost),NULL),
2497 pta.i_tot_billable_burdened_cost = DECODE(billable_burdened_cost_flag,'Y',
2498 (NVL(i_tot_billable_burdened_cost, 0) +
2499 x_tot_billable_burdened_cost),NULL),
2500 pta.i_tot_billable_quantity = DECODE(billable_quantity_flag,'Y',
2501 (NVL(i_tot_billable_quantity, 0) +
2502 x_tot_billable_quantity),NULL),
2503 pta.i_tot_billable_labor_hours = DECODE(billable_labor_hours_flag,'Y',
2504 (NVL(i_tot_billable_labor_hours,0) +
2505 DECODE(pta.system_linkage_function,
2506 'OT',
2507 x_tot_billable_quantity,
2508 'ST',
2509 x_tot_billable_quantity,
2510 0)),NULL),
2511 pta.unit_of_measure = x_unit_of_measure,
2512 pta.actual_cost_rollup_flag = 'Y',
2513 pta.last_update_date = SYSDATE,
2514 pta.last_updated_by = x_last_updated_by,
2515 pta.request_Id = x_request_id,
2516 pta.program_application_id = x_program_application_id,
2517 pta.program_id = x_program_id,
2518 pta.program_update_Date = SYSDATE
2519 WHERE
2520 pta.txn_accum_id = x_txn_accum_id;
2521
2522 EXCEPTION
2523 WHEN OTHERS THEN
2524 x_err_code := SQLCODE;
2525 RAISE;
2526 END accum_act_txn;
2527
2528 -- This procedure accumulates the revenue txn in the given
2529 -- txn_accum_id.
2530
2531 PROCEDURE accum_rev_txn
2532 ( x_txn_accum_id IN NUMBER,
2533 x_tot_revenue IN NUMBER,
2534 x_unit_of_measure IN VARCHAR2,
2535 x_err_stage IN OUT NOCOPY VARCHAR2,
2536 x_err_code IN OUT NOCOPY NUMBER)
2537 IS
2538 BEGIN
2539 x_err_code :=0;
2540 x_err_stage := 'Accumulating Revenue transactions';
2541
2542 -- accumulate revenue now
2543 UPDATE pa_txn_accum pta
2544 SET pta.i_tot_revenue = DECODE(revenue_flag,'Y',
2545 (NVL(i_tot_revenue, 0) +
2546 x_tot_revenue),NULL),
2547 pta.unit_of_measure = x_unit_of_measure,
2548 pta.revenue_rollup_flag = 'Y',
2549 pta.last_update_date = SYSDATE,
2550 pta.last_updated_by = x_last_updated_by,
2551 pta.request_Id = x_request_id,
2552 pta.program_application_id = x_program_application_id,
2553 pta.program_id = x_program_id,
2554 pta.program_update_Date = SYSDATE
2555 WHERE
2556 pta.txn_accum_id = x_txn_accum_id;
2557
2558 EXCEPTION
2559 WHEN OTHERS THEN
2560 x_err_code := SQLCODE;
2561 RAISE;
2562 END accum_rev_txn;
2563
2564 -- This procedure accumulates the commitment txn in the given
2565 -- txn_accum_id.
2566 -- Please note that the commitment quantity is always accumulated
2567 -- into CMT_QUANTITY column irrespective of the unit_of_measure
2568 -- is 'HOURS'
2569
2570 PROCEDURE accum_cmt_txn
2571 ( x_txn_accum_id IN NUMBER,
2572 x_tot_cmt_raw_cost IN NUMBER,
2573 x_tot_cmt_burdened_cost IN NUMBER,
2574 x_err_stage IN OUT NOCOPY VARCHAR2,
2575 x_err_code IN OUT NOCOPY NUMBER)
2576 IS
2577 BEGIN
2578 x_err_code :=0;
2579 x_err_stage := 'Accumulating Commitments transaction';
2580 -- accumulate commitment now
2581
2582 /* Bug# 1239605 - Included NVL for x_tot_cmt_raw_cost and
2583 x_tot_burdened_cost in the following update */
2584
2585 UPDATE pa_txn_accum pta
2586 SET pta.tot_cmt_raw_cost = DECODE(cmt_raw_cost_flag,'Y',
2587 (NVL(tot_cmt_raw_cost, 0) +
2588 NVL(x_tot_cmt_raw_cost,0)),NULL),
2589 pta.tot_cmt_burdened_cost = DECODE(cmt_burdened_cost_flag,'Y',
2590 (NVL(tot_cmt_burdened_cost, 0) +
2591 NVL(x_tot_cmt_burdened_cost,0)),NULL),
2592 pta.cmt_rollup_flag = 'Y',
2593 pta.last_update_date = SYSDATE,
2594 pta.last_updated_by = x_last_updated_by,
2595 pta.request_Id = x_request_id,
2596 pta.program_application_id= x_program_application_id,
2597 pta.program_id = x_program_id,
2598 pta.program_update_Date = SYSDATE
2599 WHERE
2600 pta.txn_accum_id = x_txn_accum_id;
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 x_err_code := SQLCODE;
2605 RAISE;
2606 END accum_cmt_txn;
2607
2608 -- Accumulate cost from CDLS
2609
2610 -- Name: Accum_Cdls
2611 --
2612 -- History
2613 -- dd-mmm-1997 Vbanal Created.
2614 --
2615 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
2616 -- Removed joins to gl_date_period_map and related entities
2617 -- and referenced new GL and PA period name columns on cdl.
2618 --
2619 -- 13-OCT-2002 Sacgupta Bug # 2580808.
2620 -- Modified for the Enhanced Period Processing effort.
2621 -- Fetching PA period name and GL period name from gl_date_period_map
2622 -- and related entities for inserting into pa_txn_accum table when
2623 -- GL and PA period name columns on cdl are null.
2624 --
2625 -- 05-NOV-2002 Sacgupta Bug # 2650900.
2626 -- Removed the condition x_mode <> I. So now both Update process and
2627 -- Refresh process will fetch PA period name and GL period name from
2628 -- gl_date_period_map and related entities for inserting into
2629 -- pa_txn_accum table when GL and PA period name columns on cdl are null.
2630 --
2631 -- 20-MAY-2003 jwhite For r11i.PA.L Burdening Enhancements, modified the following
2632 -- cursors: selcdls1, selcdls2, selcdls3.
2633 --
2634 -- Code like the following:
2635 -- AND cdl.line_type = 'R'
2636 -- was replaced with the following:
2637 -- AND ( cdl.line_type = 'R' OR cdl.line_type = 'I')
2638 --
2639 --
2640 -- 31-JUL-2003 jwhite For patchset 'L' Reburdening Enhancement, added this
2641 -- IN-parm to the accum_cdls procedure to help minimize found
2642 -- performance issues:
2643 -- x_cdl_line_type VARCHAR2
2644 --
2645 -- Code like the following:
2646 -- AND ( cdl.line_type = 'R' OR cdl.line_type = 'I')
2647 -- was replaced with the following:
2648 -- AND cdl.line_type = x_cdl_line_type
2649 --
2650 -- 07-Jul-2004 Sacgupta Bug # 3736097.
2651 -- Commented out all occurence of x_end_project_id.
2652 -- This is done because all the processing is done for a
2653 -- single project rather than for a range of projects.
2654 --
2655
2656
2657
2658 PROCEDURE accum_cdls
2659 ( x_start_project_id IN NUMBER,
2660 x_end_project_id IN NUMBER,
2661 x_start_pa_date IN DATE,
2662 x_end_pa_date IN DATE,
2663 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
2664 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
2665 x_cdl_line_type IN VARCHAR2,
2666 x_err_stage IN OUT NOCOPY VARCHAR2,
2667 x_err_code IN OUT NOCOPY NUMBER)
2668 IS
2669 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
2670
2671
2672 -- The cursor selcdl selects all CDLs which
2673 -- satisfy the pa_period given as the parameters
2674 -- the argument x_mode represents the mode for accumulation i.e.
2675 -- 'I' for incremental and 'F' for FULL
2676
2677 /* Bug# 1770772 - Breaking the cursor into two and call/open it conditionally based on x_mode parameter
2678 to eliminate the decode on resource_accumulated_Flag and hence use index usage
2679 */
2680 CURSOR selcdls1 IS
2681 SELECT
2682 cdl.ROWID cdlrowid,
2683 cdl.expenditure_item_id expenditure_item_id,
2684 cdl.line_num line_num,
2685 pe.incurred_by_person_id person_id,
2686 ei.job_id job_id,
2687 NVL(ei.override_to_organization_id,
2688 pe.incurred_by_organization_id) organization_id,
2689 decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
2690 et.expenditure_type expenditure_type,
2691 ei.non_labor_resource non_labor_resource,
2692 et.expenditure_category expenditure_category,
2693 et.revenue_category_code revenue_category,
2694 ei.organization_id non_labor_resource_org_id,
2695 ei.system_linkage_function system_linkage_function,
2696 cdl.project_id project_id,
2697 cdl.task_id task_id,
2698 cdl.RECVR_PA_PERIOD_NAME pa_period,
2699 cdl.RECVR_GL_PERIOD_NAME gl_period,
2700 pe.expenditure_ending_date week_ending_date,
2701 LAST_DAY(ei.expenditure_item_date) month_ending_date,
2702 NVL(cdl.amount,0) raw_cost,
2703 NVL(cdl.quantity,0) quantity,
2704 NVL(cdl.burdened_cost,0) burdened_cost,
2705 DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
2706 DECODE(cdl.billable_flag,'Y',NVL(cdl.quantity,0),0) billable_quantity,
2707 DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
2708 decode(et.unit_of_measure,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
2709 cdl.ind_compiled_set_id cost_ind_compiled_set_id
2710 FROM
2711 pa_expenditures_all pe,
2712 pa_expenditure_types et,
2713 pa_expenditure_items_all ei,
2714 pa_cost_distribution_lines_all cdl
2715 WHERE
2716 cdl.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id
2717 AND cdl.line_type = x_cdl_line_type
2718 AND cdl.resource_accumulated_flag = 'N'
2719 AND cdl.expenditure_item_id = ei.expenditure_item_id
2720 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2721 AND ei.expenditure_type = et.expenditure_type
2722 AND ei.task_id = cdl.task_id
2723 AND pe.expenditure_id = ei.expenditure_id
2724 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2725 AND ei.system_linkage_function||'' =
2726 NVL(x_system_linkage_function,ei.system_linkage_function)
2727 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2728
2729 CURSOR selcdls2 IS
2730 SELECT
2731 cdl.ROWID cdlrowid,
2732 cdl.expenditure_item_id expenditure_item_id,
2733 cdl.line_num line_num,
2734 pe.incurred_by_person_id person_id,
2735 ei.job_id job_id,
2736 NVL(ei.override_to_organization_id,
2737 pe.incurred_by_organization_id) organization_id,
2738 decode(ei.system_linkage_function,'VI',cdl.system_reference1,NULL) vendor_id, -- Modified for bug#5878137
2739 et.expenditure_type expenditure_type,
2740 ei.non_labor_resource non_labor_resource,
2741 et.expenditure_category expenditure_category,
2742 et.revenue_category_code revenue_category,
2743 ei.organization_id non_labor_resource_org_id,
2744 ei.system_linkage_function system_linkage_function,
2745 cdl.project_id project_id,
2746 cdl.task_id task_id,
2747 cdl.RECVR_PA_PERIOD_NAME pa_period,
2748 cdl.RECVR_GL_PERIOD_NAME gl_period,
2749 pe.expenditure_ending_date week_ending_date,
2750 LAST_DAY(ei.expenditure_item_date) month_ending_date,
2751 NVL(cdl.amount,0) raw_cost,
2752 NVL(cdl.quantity,0) quantity,
2753 NVL(cdl.burdened_cost,0) burdened_cost,
2754 DECODE(cdl.billable_flag,'Y',NVL(cdl.amount,0),0) billable_raw_cost,
2755 DECODE(cdl.billable_flag,'Y',NVL(cdl.quantity,0),0) billable_quantity,
2756 DECODE(cdl.billable_flag,'Y',NVL(cdl.burdened_cost,0),0) billable_burdened_cost,
2757 decode(et.unit_of_measure ,NULL, ei.unit_of_measure, et.unit_of_measure) unit_of_measure,
2758 cdl.ind_compiled_set_id cost_ind_compiled_set_id
2759 FROM
2760 pa_expenditures_all pe,
2761 pa_expenditure_types et,
2762 pa_expenditure_items_all ei,
2763 pa_cost_distribution_lines_all cdl
2764 WHERE
2765 -- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
2766 cdl.project_id = x_start_project_id
2767 AND cdl.line_type = x_cdl_line_type
2768 /* Commented for bug# 1770772 while splitting the cursor in two
2769 AND cdl.resource_accumulated_flag =
2770 decode(x_mode,'I','N',
2771 'F',cdl.resource_accumulated_flag,'N')
2772 */
2773 AND cdl.expenditure_item_id = ei.expenditure_item_id
2774 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2775 AND ei.expenditure_type = et.expenditure_type
2776 AND ei.task_id = cdl.task_id
2777 AND pe.expenditure_id = ei.expenditure_id
2778 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2779 AND ei.system_linkage_function||'' =
2780 NVL(x_system_linkage_function,ei.system_linkage_function)
2781 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2782
2783 -- Cursor added for bug 2580808
2784 CURSOR selcdls3 (crowid VARCHAR2)IS
2785 SELECT
2786 p.period_name pa_period1,
2787 g.period_name gl_period1
2788 FROM
2789 gl_date_period_map p,
2790 gl_date_period_map g,
2791 pa_expenditures_all pe,
2792 pa_expenditure_types et,
2793 pa_expenditure_items_all ei,
2794 pa_cost_distribution_lines_all cdl,
2795 pa_implementations pi,
2796 gl_sets_of_books sob
2797 WHERE
2798 -- cdl.project_id BETWEEN x_start_project_id AND x_end_project_id -- Modified for bug 3736097
2799 cdl.project_id = x_start_project_id
2800 AND cdl.ROWID = CHARTOROWID(crowid)
2801 AND cdl.line_type = x_cdl_line_type
2802 AND cdl.expenditure_item_id = ei.expenditure_item_id
2803 AND NVL(cdl.org_id,-99) = NVL(ei.org_id,-99)
2804 AND ei.expenditure_type = et.expenditure_type
2805 AND ei.task_id = cdl.task_id
2806 AND pe.expenditure_id = ei.expenditure_id
2807 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
2808 AND sob.set_of_books_id = pi.set_of_books_id
2809 AND p.period_set_name = sob.period_set_name
2810 AND g.period_set_name = sob.period_set_name
2811 AND p.period_type = pi.pa_period_type
2812 AND g.period_type = sob.accounted_period_type
2813 /* Bug #3493462: Added trunc to recvr_pa_date */
2814 AND p.accounting_date = TRUNC(cdl.recvr_pa_date)
2815 AND g.accounting_date = NVL(TRUNC(cdl.recvr_gl_date), TRUNC(cdl.recvr_pa_date))
2816 AND ei.system_linkage_function||'' =
2817 NVL(x_system_linkage_function,ei.system_linkage_function)
2818 AND TRUNC(cdl.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
2819
2820 cdlrec selcdls1%ROWTYPE;
2821 cdlrec1 selcdls3%ROWTYPE; -- added for bug 2580808.
2822 x_txn_accum_id NUMBER;
2823 row_processed NUMBER;
2824 commit_rows NUMBER;
2825
2826 BEGIN
2827
2828 x_txn_accum_id :=0;
2829 x_err_code :=0;
2830 row_processed :=0;
2831 commit_rows :=0;
2832 x_err_stage := 'Accumulating CDLs';
2833
2834 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2835 pa_debug.debug('accum_cdls: ' || x_err_stage);
2836 END IF;
2837
2838 /* Included for bug# 1770772 */
2839
2840 IF x_mode = 'I' THEN
2841 OPEN selcdls1;
2842 ELSE
2843 OPEN selcdls2;
2844 END IF;
2845
2846 LOOP
2847 IF x_mode = 'I' THEN
2848 FETCH selcdls1 INTO cdlrec;
2849 EXIT WHEN selcdls1%NOTFOUND;
2850 ELSE
2851 FETCH selcdls2 INTO cdlrec;
2852 EXIT WHEN selcdls2%NOTFOUND;
2853 END IF;
2854
2855 /* Commented for bug# 1770772 */
2856
2857 /* FOR cdlrec IN selcdls LOOP */
2858
2859 /* End of changes for bug# 1770772 */
2860
2861 row_processed := row_processed + 1;
2862 commit_rows := commit_rows + 1;
2863
2864 -- IF clause added for bug 2580808.
2865 -- IF x_mode <> 'I' THEN -- for bug 2650900
2866 IF cdlrec.pa_period IS NULL OR cdlrec.gl_period IS NULL THEN
2867 OPEN selcdls3(ROWIDTOCHAR(cdlrec.cdlrowid));
2868 FETCH selcdls3 INTO cdlrec1;
2869 CLOSE selcdls3;
2870 END IF;
2871 -- END IF; -- for bug 2650900
2872
2873
2874 create_txn_accum(
2875 cdlrec.project_id,
2876 cdlrec.task_id,
2877 NVL(cdlrec.pa_period, cdlrec1.pa_period1), -- Modified for bug 2580808.
2878 NVL(cdlrec.gl_period, cdlrec1.gl_period1), -- Modified for bug 2580808.
2879 cdlrec.week_ending_date,
2880 cdlrec.month_ending_date,
2881 cdlrec.person_id,
2882 cdlrec.job_id,
2883 cdlrec.vendor_id,
2884 cdlrec.expenditure_type,
2885 cdlrec.organization_id,
2886 cdlrec.non_labor_resource,
2887 cdlrec.non_labor_resource_org_id,
2888 cdlrec.expenditure_category,
2889 cdlrec.revenue_category,
2890 NULL, -- event_type
2891 NULL, -- event_type_classification
2892 cdlrec.system_linkage_function,
2893 'C', -- x_line_type = 'C' for CDL
2894 cdlrec.cost_ind_compiled_set_id,
2895 NULL, -- rev_ind_compiled_set_id
2896 NULL, -- inv_ind_compiled_set_id
2897 NULL, -- cmt_ind_compiled_set_id
2898 x_txn_accum_id,
2899 x_err_stage,
2900 x_err_code);
2901
2902 -- Create a row for drilldown in pa_txn_accume_details Now
2903
2904 create_txn_accum_details(
2905 x_txn_accum_id,
2906 'C', -- CDLS
2907 cdlrec.expenditure_item_id,
2908 cdlrec.line_num,
2909 NULL, -- event_num
2910 NULL, -- cmt_line_id
2911 cdlrec.project_id,
2912 cdlrec.task_id,
2913 x_err_stage,
2914 x_err_code);
2915
2916 -- Accume this row now for txn_accum_id = x_txn_accum_id
2917 -- also create rows for drilldown
2918
2919 accum_act_txn(
2920 x_txn_accum_id,
2921 cdlrec.raw_cost,
2922 cdlrec.burdened_cost,
2923 cdlrec.quantity,
2924 cdlrec.billable_raw_cost,
2925 cdlrec.billable_burdened_cost,
2926 cdlrec.billable_quantity,
2927 cdlrec.unit_of_measure,
2928 x_err_stage,
2929 x_err_code);
2930
2931 --- Update the CDL.Resource_accumulated_flag = 'Y' Now
2932
2933 UPDATE
2934 pa_cost_distribution_lines_all
2935 SET
2936 resource_accumulated_flag = 'Y'
2937 WHERE
2938 ROWID = cdlrec.cdlrowid;
2939
2940 IF (commit_rows >= pa_proj_accum_main.x_commit_size) THEN
2941 COMMIT;
2942 commit_rows := 0;
2943 END IF;
2944
2945 END LOOP;
2946
2947 /* Included for bug# 1770772 */
2948
2949 IF x_mode = 'I' THEN
2950 CLOSE selcdls1;
2951 ELSE
2952 CLOSE selcdls2;
2953 END IF;
2954
2955 /* End of changes for bug# 1770772 */
2956
2957 IF (commit_rows < pa_proj_accum_main.x_commit_size) THEN
2958 COMMIT;
2959 END IF;
2960
2961 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
2962 pa_debug.debug('accum_cdls: ' || 'Number of CDL Processed = ' || TO_CHAR(row_processed));
2963 END IF;
2964
2965 EXCEPTION
2966 WHEN OTHERS THEN
2967 x_err_code := SQLCODE;
2968 RAISE;
2969
2970 END accum_cdls;
2971
2972 -- Accumulate revenue from RDLS
2973
2974 -- Name: Accum_Rdls
2975 --
2976 -- History
2977 -- dd-mmm-1997 Vbanal Created.
2978 --
2979 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
2980 -- Removed joins to gl_date_period_map and related entities
2981 -- and referenced new GL and PA period name columns on rdl.
2982 --
2983 -- 13-OCT-2002 Sacgupta Bug # 2580808.
2984 -- Modified for the Enhanced Period Processing effort.
2985 -- Fetching PA period name and GL period name from gl_date_period_map
2986 -- and related entities for inserting into pa_txn_accum table when
2987 -- GL and PA period name columns on rdl are null.
2988 --
2989 -- 05-NOV-2002 Sacgupta Bug # 2650900.
2990 -- Removed the condition x_mode <> I. So now both Update process and
2991 -- Refresh process will fetch PA period name and GL period name from
2992 -- gl_date_period_map and related entities for inserting into
2993 -- pa_txn_accum table when GL and PA period name columns on rdl are null.
2994 --
2995 -- 07-Jul-2004 Sacgupta Bug # 3736097.
2996 -- Commented out all occurence of x_end_project_id.
2997 -- This is done because all the processing is done for a
2998 -- single project rather than for a range of projects.
2999 PROCEDURE accum_rdls
3000 ( x_start_project_id IN NUMBER,
3001 x_end_project_id IN NUMBER,
3002 x_start_pa_date IN DATE,
3003 x_end_pa_date IN DATE,
3004 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3005 x_err_stage IN OUT NOCOPY VARCHAR2,
3006 x_err_code IN OUT NOCOPY NUMBER)
3007 IS
3008 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3009
3010
3011 CURSOR selrdls IS
3012 SELECT
3013 dr.ROWID drrowid,
3014 rdl.expenditure_item_id expenditure_item_id,
3015 rdl.line_num line_num,
3016 pe.incurred_by_person_id person_id,
3017 ei.job_id job_id,
3018 NVL(ei.override_to_organization_id,
3019 pe.incurred_by_organization_id) organization_id,
3020 et.expenditure_type expenditure_type,
3021 ei.non_labor_resource non_labor_resource,
3022 et.expenditure_category expenditure_category,
3023 et.revenue_category_code revenue_category,
3024 ei.organization_id non_labor_resource_org_id,
3025 ei.system_linkage_function system_linkage_function,
3026 dr.project_id project_id,
3027 ei.task_id task_id,
3028 dr.PA_PERIOD_NAME pa_period,
3029 dr.GL_PERIOD_NAME gl_period,
3030 pe.expenditure_ending_date week_ending_date,
3031 LAST_DAY(ei.expenditure_item_date) month_ending_date,
3032 rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
3033 rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
3034 NVL(rdl.amount,0) amount,
3035 decode(et.unit_of_measure,NULL,et.unit_of_measure,ei.unit_of_measure) unit_of_measure
3036 FROM
3037 pa_expenditures_all pe,
3038 pa_expenditure_types et,
3039 pa_expenditure_items_all ei,
3040 pa_cust_rev_dist_lines rdl,
3041 pa_draft_revenues dr
3042 WHERE
3043 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
3044 AND NVL(dr.resource_accumulated_flag,'S') =
3045 DECODE(x_mode,'I','S',
3046 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3047 AND dr.released_date IS NOT NULL
3048 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3049 AND dr.project_id = rdl.project_id
3050 AND dr.draft_revenue_num = rdl.draft_revenue_num
3051 AND rdl.expenditure_item_id = ei.expenditure_item_id
3052 AND ei.expenditure_type = et.expenditure_type
3053 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3054 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3055 AND pe.expenditure_id = ei.expenditure_id;
3056
3057 -- Cursor added for bug 2580808
3058 CURSOR selrdls1 (rrowid VARCHAR2)IS
3059 SELECT
3060 p.period_name pa_period1,
3061 g.period_name gl_period1
3062 FROM
3063 gl_date_period_map p,
3064 gl_date_period_map g,
3065 pa_expenditures_all pe,
3066 pa_expenditure_types et,
3067 pa_expenditure_items_all ei,
3068 pa_cust_rev_dist_lines rdl,
3069 pa_draft_revenues dr,
3070 pa_implementations pi,
3071 gl_sets_of_books sob
3072 WHERE
3073 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id Commented for Bug # 3736097
3074 AND dr.ROWID = CHARTOROWID(rrowid)
3075 AND NVL(dr.resource_accumulated_flag,'S') =
3076 DECODE(x_mode,'I','S',
3077 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3078 AND dr.released_date IS NOT NULL
3079 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3080 AND dr.project_id = rdl.project_id
3081 AND dr.draft_revenue_num = rdl.draft_revenue_num
3082 AND rdl.expenditure_item_id = ei.expenditure_item_id
3083 AND ei.expenditure_type = et.expenditure_type
3084 AND sob.set_of_books_id = pi.set_of_books_id
3085 AND p.period_set_name = sob.period_set_name
3086 AND g.period_set_name = sob.period_set_name
3087 AND p.period_type = pi.pa_period_type
3088 AND g.period_type = sob.accounted_period_type
3089 AND p.accounting_date = dr.pa_date
3090 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3091 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3092 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3093 AND pe.expenditure_id = ei.expenditure_id;
3094
3095
3096 rdlrec selrdls%ROWTYPE;
3097 rdlrec1 selrdls1%ROWTYPE; -- added for bug 2580808.
3098 x_txn_accum_id NUMBER;
3099 row_processed NUMBER;
3100
3101 BEGIN
3102 x_txn_accum_id :=0;
3103 x_err_code :=0;
3104 row_processed :=0;
3105 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3106 x_err_stage := 'Accumulating revenue';
3107 pa_debug.debug('accum_rdls: ' || x_err_stage);
3108 END IF;
3109
3110 FOR rdlrec IN selrdls LOOP
3111
3112 row_processed := row_processed + 1;
3113
3114 -- IF clause added for bug 2580808.
3115 -- IF x_mode <> 'I' THEN -- for bug 2650900
3116 IF (rdlrec.pa_period IS NULL OR rdlrec.gl_period IS NULL) THEN
3117 OPEN selrdls1(ROWIDTOCHAR(rdlrec.drrowid));
3118 FETCH selrdls1 INTO rdlrec1;
3119 CLOSE selrdls1;
3120 END IF;
3121 -- END IF; -- for bug 2650900
3122
3123 create_txn_accum(
3124 rdlrec.project_id,
3125 rdlrec.task_id,
3126 -- Commented out for bug 2580808
3127 -- rdlrec.pa_period,
3128 -- rdlrec.gl_period,
3129 NVL(rdlrec.pa_period,rdlrec1.pa_period1), -- added for bug 2580808
3130 NVL(rdlrec.gl_period,rdlrec1.gl_period1), -- added for bug 2580808
3131 rdlrec.week_ending_date,
3132 rdlrec.month_ending_date,
3133 rdlrec.person_id,
3134 rdlrec.job_id,
3135 NULL, -- vendor_id
3136 rdlrec.expenditure_type,
3137 rdlrec.organization_id,
3138 rdlrec.non_labor_resource,
3139 rdlrec.non_labor_resource_org_id,
3140 rdlrec.expenditure_category,
3141 rdlrec.revenue_category,
3142 NULL, -- event_type
3143 NULL, -- event_type_classification
3144 rdlrec.system_linkage_function,
3145 'R', -- x_line_type = 'R' for RDL
3146 NULL, -- cost_ind_compiled_set_id
3147 rdlrec.rev_ind_compiled_set_id,
3148 rdlrec.inv_ind_compiled_set_id,
3149 NULL, -- cmt_ind_compiled_set_id
3150 x_txn_accum_id,
3151 x_err_stage,
3152 x_err_code);
3153
3154 -- Create a row for drilldown in pa_txn_accum_details Now
3155
3156 create_txn_accum_details(
3157 x_txn_accum_id,
3158 'R', -- RDLS
3159 rdlrec.expenditure_item_id,
3160 rdlrec.line_num,
3161 NULL, -- Event Num
3162 NULL, -- CMT_LINE_ID
3163 rdlrec.project_id,
3164 rdlrec.task_id,
3165 x_err_stage,
3166 x_err_code);
3167
3168 -- Accume this row in txn_accum_id = x_txn_accum_id
3169 -- also create rows for drilldown
3170
3171 accum_rev_txn(
3172 x_txn_accum_id,
3173 rdlrec.amount,
3174 rdlrec.unit_of_measure,
3175 x_err_stage,
3176 x_err_code);
3177
3178 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3179
3180 UPDATE
3181 pa_draft_revenues
3182 SET
3183 resource_accumulated_flag = 'S'
3184 WHERE
3185 ROWID = rdlrec.drrowid;
3186
3187 END LOOP;
3188
3189 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3190 pa_debug.debug('accum_rdls: ' || 'Number of RDL Processed = ' || TO_CHAR(row_processed));
3191 END IF;
3192
3193 EXCEPTION
3194 WHEN OTHERS THEN
3195 x_err_code := SQLCODE;
3196 RAISE;
3197
3198 END accum_rdls;
3199
3200 -- Accumulate revenue from Events
3201
3202 -- Name: Accum_Erdls
3203 --
3204 -- History
3205 -- dd-mmm-1997 Vbanal Created.
3206 --
3207 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
3208 -- Removed joins to gl_date_period_map and related entities
3209 -- and referenced new GL and PA period name columns on erdls
3210 --
3211 -- 13-OCT-2002 Sacgupta Bug # 2580808.
3212 -- Modified for the Enhanced Period Processing effort.
3213 -- Fetching PA period name and GL period name from gl_date_period_map
3214 -- and related entities for inserting into pa_txn_accum table when
3215 -- GL and PA period name columns on erdls are null.
3216 --
3217 -- 05-NOV-2002 Sacgupta Bug # 2650900.
3218 -- Removed the condition x_mode <> I. So now both Update process and
3219 -- Refresh process will fetch PA period name and GL period name from
3220 -- gl_date_period_map and related entities for inserting into
3221 -- pa_txn_accum table when GL and PA period name columns on erdls are null.
3222 --
3223 -- 07-JUL-2004 Sacgupta Bug # 3736097.
3224 -- Commented out all occurence of x_end_project_id.
3225 -- This is done because all the processing is done for a
3226 -- single project rather than for a range of projects.
3227 --
3228 PROCEDURE accum_erdls
3229 ( x_start_project_id IN NUMBER,
3230 x_end_project_id IN NUMBER,
3231 x_start_pa_date IN DATE,
3232 x_end_pa_date IN DATE,
3233 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3234 x_err_stage IN OUT NOCOPY VARCHAR2,
3235 x_err_code IN OUT NOCOPY NUMBER)
3236 IS
3237 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3238
3239
3240
3241 CURSOR selevents IS
3242 SELECT
3243 dr.ROWID drrowid,
3244 erdl.event_num event_num,
3245 erdl.line_num line_num,
3246 ev.organization_id organization_id,
3247 ev.event_type,
3248 evt.revenue_category_code revenue_category,
3249 erdl.project_id,
3250 NVL(erdl.task_id,0) task_id,
3251 dr.PA_PERIOD_NAME pa_period,
3252 dr.GL_PERIOD_NAME gl_period,
3253 evt.event_type_classification,
3254 pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
3255 LAST_DAY(ev.completion_date) month_ending_date,
3256 NVL(erdl.amount,0) amount
3257 FROM
3258 pa_events ev,
3259 pa_event_types evt,
3260 pa_cust_event_rev_dist_lines erdl,
3261 pa_draft_revenues dr
3262 WHERE
3263 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
3264 AND NVL(dr.resource_accumulated_flag,'S') =
3265 DECODE(x_mode,'I','S',
3266 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3267 AND dr.released_date IS NOT NULL
3268 AND dr.project_id = erdl.project_id
3269 AND dr.draft_revenue_num = erdl.draft_revenue_num
3270 AND erdl.project_id = ev.project_id
3271 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3272 AND ev.event_num = erdl.event_num
3273 AND ev.event_type = evt.event_type
3274 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3275
3276 -- Cursor added for bug 2580808
3277 CURSOR selevents1 (rrowid VARCHAR2)IS
3278 SELECT
3279 p.period_name pa_period1,
3280 g.period_name gl_period1
3281 FROM
3282 gl_date_period_map p,
3283 gl_date_period_map g,
3284 pa_events ev,
3285 pa_event_types evt,
3286 pa_cust_event_rev_dist_lines erdl,
3287 pa_draft_revenues dr,
3288 pa_implementations pi,
3289 gl_sets_of_books sob
3290 WHERE
3291 dr.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for Bug # 3736097
3292 AND dr.ROWID = CHARTOROWID(rrowid)
3293 AND NVL(dr.resource_accumulated_flag,'S') =
3294 DECODE(x_mode,'I','S',
3295 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3296 AND dr.released_date IS NOT NULL
3297 AND dr.project_id = erdl.project_id
3298 AND dr.draft_revenue_num = erdl.draft_revenue_num
3299 AND erdl.project_id = ev.project_id
3300 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3301 AND ev.event_num = erdl.event_num
3302 AND ev.event_type = evt.event_type
3303 AND sob.set_of_books_id = pi.set_of_books_id
3304 AND p.period_set_name = sob.period_set_name
3305 AND g.period_set_name = sob.period_set_name
3306 AND p.period_type = pi.pa_period_type
3307 AND g.period_type = sob.accounted_period_type
3308 AND p.accounting_date = dr.pa_date
3309 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3310 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3311
3312
3313 eventrec selevents%ROWTYPE;
3314 eventrec1 selevents1%ROWTYPE; -- for bug 2580808
3315 x_txn_accum_id NUMBER;
3316 row_processed NUMBER;
3317
3318
3319 BEGIN
3320 x_txn_accum_id :=0;
3321 x_err_code :=0;
3322 row_processed :=0;
3323 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3324 x_err_stage := 'Accumulating Event Revenue';
3325 pa_debug.debug('accum_erdls: ' || x_err_stage);
3326 END IF;
3327
3328 FOR eventrec IN selevents LOOP
3329
3330 row_processed := row_processed + 1;
3331
3332 -- IF clause added for bug 2580808.
3333 -- IF x_mode <> 'I' THEN -- for bug 2650900
3334 IF (eventrec.pa_period IS NULL OR eventrec.gl_period IS NULL) THEN
3335 OPEN selevents1(ROWIDTOCHAR(eventrec.drrowid));
3336 FETCH selevents1 INTO eventrec1;
3337 CLOSE selevents1;
3338 END IF;
3339 -- END IF; -- for bug 2650900
3340
3341 create_txn_accum(
3342 eventrec.project_id,
3343 eventrec.task_id,
3344 -- Commented out for bug 2580808
3345 -- eventrec.pa_period,
3346 -- eventrec.gl_period,
3347 NVL(eventrec.pa_period,eventrec1.pa_period1), -- for bug 2580808
3348 NVL(eventrec.gl_period,eventrec1.gl_period1), -- for bug 2580808
3349 eventrec.week_ending_date,
3350 eventrec.month_ending_date,
3351 NULL, -- person_id
3352 NULL, -- job_id
3353 NULL, -- vendor_id
3354 NULL, -- expenditure_type
3355 eventrec.organization_id,
3356 NULL, -- non_labor_resource
3357 NULL, -- non_labor_resource_org_id
3358 NULL, -- expenditure_category
3359 eventrec.revenue_category,
3360 eventrec.event_type,
3361 eventrec.event_type_classification,
3362 NULL, -- system_linkage_function
3363 'R', -- x_line_type = 'R' for revenue
3364 NULL, -- cost_ind_compiled_set_id
3365 NULL, -- rev_ind_compiled_set_id
3366 NULL, -- inv_ind_compiled_set_id
3367 NULL, -- cmt_ind_compiled_set_id
3368 x_txn_accum_id,
3369 x_err_stage,
3370 x_err_code);
3371
3372 -- Create a row for drilldown in pa_txn_accum_details Now
3373
3374 create_txn_accum_details(
3375 x_txn_accum_id,
3376 'E', -- ERDLS
3377 NULL, -- expenditure_item_id
3378 eventrec.line_num,
3379 eventrec.event_num,
3380 NULL, -- cmt_line_id
3381 eventrec.project_id,
3382 eventrec.task_id,
3383 x_err_stage,
3384 x_err_code);
3385
3386 -- Accume this row in txn_accum_id = x_txn_accum_id
3387 -- also create rows for drilldown
3388
3389 accum_rev_txn(
3390 x_txn_accum_id,
3391 eventrec.amount,
3392 NULL, -- Unit_of_measure
3393 x_err_stage,
3394 x_err_code);
3395
3396 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3397
3398 UPDATE
3399 pa_draft_revenues
3400 SET
3401 resource_accumulated_flag = 'S'
3402 WHERE
3403 ROWID = eventrec.drrowid;
3404
3405 END LOOP;
3406
3407 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3408 pa_debug.debug('accum_erdls: ' || 'Number of Event RDL Processed = ' || TO_CHAR(row_processed));
3409 END IF;
3410
3411 EXCEPTION
3412 WHEN OTHERS THEN
3413 x_err_code := SQLCODE;
3414 RAISE;
3415
3416 END accum_erdls;
3417
3418 -- The procedure given below should be called to accumulate
3419 -- revenue
3420
3421 -- Name: Accum_Revenue
3422 --
3423 -- History
3424 -- dd-mmm-1997 Vbanal Created.
3425 --
3426 -- 15-OCT-2001 Jwhite Modified for the Enhanced Period Processing effort.
3427 -- Removed joins to gl_date_period_map and related entities
3428 -- and referenced new GL and PA period name columns.
3429 --
3430 -- 13-OCT-2002 Sacgupta Bug # 2580808.
3431 -- Modified for the Enhanced Period Processing effort.
3432 -- Fetching PA period name and GL period name from gl_date_period_map
3433 -- and related entities for inserting into pa_txn_accum table when
3434 -- GL and PA period name columns on rdl are null.
3435 --
3436 -- 05-NOV-2002 Sacgupta Bug # 2650900.
3437 -- Removed the condition x_mode <> I. So now both Update process and
3438 -- Refresh process will fetch PA period name and GL period name from
3439 -- gl_date_period_map and related entities for inserting into
3440 -- pa_txn_accum table when GL and PA period name columns on rdl are null.
3441 --
3442 -- 07-JUL-2004 Sacgupta Bug # 3736097.
3443 -- Commented out all occurence of x_end_project_id.
3444 -- This is done because all the processing is done for a
3445 -- single project rather than for a range of projects.
3446 --
3447 PROCEDURE accum_revenue
3448 ( x_start_project_id IN NUMBER,
3449 x_end_project_id IN NUMBER,
3450 x_start_pa_date IN DATE,
3451 x_end_pa_date IN DATE,
3452 x_mode IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3453 x_err_stage IN OUT NOCOPY VARCHAR2,
3454 x_err_code IN OUT NOCOPY NUMBER)
3455 IS
3456 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3457
3458 CURSOR selrdls IS
3459 SELECT
3460 'R' line_type,
3461 dr.ROWID drrowid,
3462 rdl.expenditure_item_id expenditure_item_id,
3463 rdl.line_num line_num,
3464 pe.incurred_by_person_id person_id,
3465 ei.job_id job_id,
3466 NVL(ei.override_to_organization_id,
3467 pe.incurred_by_organization_id) organization_id,
3468 et.expenditure_type expenditure_type,
3469 ei.non_labor_resource non_labor_resource,
3470 et.expenditure_category expenditure_category,
3471 et.revenue_category_code revenue_category,
3472 ei.organization_id non_labor_resource_org_id,
3473 ei.system_linkage_function system_linkage_function,
3474 dr.project_id project_id,
3475 ei.task_id task_id,
3476 dr.PA_PERIOD_NAME pa_period,
3477 dr.GL_PERIOD_NAME gl_period,
3478 pe.expenditure_ending_date week_ending_date,
3479 LAST_DAY(ei.expenditure_item_date) month_ending_date,
3480 rdl.rev_ind_compiled_set_id rev_ind_compiled_set_id,
3481 rdl.inv_ind_compiled_set_id inv_ind_compiled_set_id,
3482 NVL(rdl.amount,0) amount,
3483 TO_NUMBER(NULL) event_num,
3484 NULL event_type,
3485 NULL event_type_classification,
3486 et.unit_of_measure unit_of_measure
3487 FROM
3488 pa_expenditures_all pe,
3489 pa_expenditure_types et,
3490 pa_expenditure_items_all ei,
3491 pa_cust_rev_dist_lines rdl,
3492 pa_draft_revenues dr
3493 WHERE
3494 dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
3495 AND NVL(dr.resource_accumulated_flag,'S') =
3496 DECODE(x_mode,'I','S',
3497 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3498 AND dr.released_date IS NOT NULL
3499 AND rdl.function_code NOT IN ('LRL','LRB','URL','URB')
3500 AND dr.project_id = rdl.project_id
3501 AND dr.draft_revenue_num = rdl.draft_revenue_num
3502 AND rdl.expenditure_item_id = ei.expenditure_item_id
3503 AND ei.expenditure_type = et.expenditure_type
3504 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3505 AND NVL(pe.org_id,-99) = NVL(ei.org_id,-99)
3506 AND pe.expenditure_id = ei.expenditure_id
3507 UNION ALL
3508 SELECT
3509 'E' line_type,
3510 dr.ROWID drrowid,
3511 TO_NUMBER(NULL) expenditure_item_id,
3512 erdl.line_num line_num,
3513 TO_NUMBER(NULL) person_id,
3514 TO_NUMBER(NULL) job_id,
3515 ev.organization_id organization_id,
3516 NULL expenditure_type,
3517 NULL non_labor_resource,
3518 NULL expenditure_category,
3519 evt.revenue_category_code revenue_category,
3520 TO_NUMBER(NULL) non_labor_resource_org_id,
3521 NULL system_linkage_function,
3522 erdl.project_id project_id,
3523 NVL(erdl.task_id,0) task_id,
3524 dr.PA_PERIOD_NAME pa_period,
3525 dr.GL_PERIOD_NAME gl_period,
3526 pa_utils.GetWeekEnding(ev.completion_date) week_ending_date,
3527 LAST_DAY(ev.completion_date) month_ending_date,
3528 TO_NUMBER(NULL) rev_ind_compiled_set_id,
3529 TO_NUMBER(NULL) inv_ind_compiled_set_id,
3530 NVL(erdl.amount,0) amount,
3531 erdl.event_num event_num,
3532 ev.event_type event_type,
3533 evt.event_type_classification event_type_classification,
3534 NULL unit_of_measure
3535 FROM
3536 pa_events ev,
3537 pa_event_types evt,
3538 pa_cust_event_rev_dist_lines erdl,
3539 pa_draft_revenues dr
3540 WHERE
3541 dr.project_id = x_start_project_id ---- x_start_project_id and x_end_project_id
3542 AND NVL(dr.resource_accumulated_flag,'S') =
3543 DECODE(x_mode,'I','S',
3544 'F',NVL(dr.resource_accumulated_flag,'S'),'S')
3545 AND dr.released_date IS NOT NULL
3546 AND dr.project_id = erdl.project_id
3547 AND dr.draft_revenue_num = erdl.draft_revenue_num
3548 AND erdl.project_id = ev.project_id
3549 AND NVL(erdl.task_id,0) = NVL(ev.task_id,0)
3550 AND ev.event_num = erdl.event_num
3551 AND ev.event_type = evt.event_type
3552 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date
3553 ORDER BY 2;
3554
3555 -- Cursor added for bug 2580808
3556 CURSOR selrdls1 (rrowid VARCHAR2)IS
3557 SELECT
3558 p.period_name pa_period1,
3559 g.period_name gl_period1
3560 FROM
3561 gl_date_period_map p,
3562 gl_date_period_map g,
3563 pa_draft_revenues dr,
3564 pa_implementations pi,
3565 gl_sets_of_books sob
3566 WHERE
3567 dr.project_id = x_start_project_id
3568 AND dr.ROWID = CHARTOROWID(rrowid)
3569 AND NVL(dr.resource_accumulated_flag,'S') = DECODE(x_mode,'I','S','F',NVL(dr.resource_accumulated_flag,'S'),'S')
3570 AND dr.released_date IS NOT NULL
3571 AND sob.set_of_books_id = pi.set_of_books_id
3572 AND p.period_set_name = sob.period_set_name
3573 AND g.period_set_name = sob.period_set_name
3574 AND p.period_type = pi.pa_period_type
3575 AND g.period_type = sob.accounted_period_type
3576 AND p.accounting_date = dr.pa_date
3577 AND g.accounting_date = NVL(dr.gl_date, dr.pa_date)
3578 AND TRUNC(dr.pa_date) BETWEEN x_start_pa_date AND x_end_pa_date;
3579
3580
3581 rdlrec selrdls%ROWTYPE;
3582 rdlrec1 selrdls1%ROWTYPE; -- for bug 2580808
3583 x_txn_accum_id NUMBER;
3584 row_processed NUMBER;
3585 commit_rows NUMBER;
3586 curr_rowid ROWID;
3587
3588 BEGIN
3589 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3590 x_err_stage := 'Accumulating All Revenues';
3591 pa_debug.debug('accum_revenue: ' || x_err_stage);
3592 END IF;
3593
3594 x_txn_accum_id :=0;
3595 x_err_code :=0;
3596 row_processed :=0;
3597 commit_rows :=0;
3598
3599
3600 FOR rdlrec IN selrdls LOOP
3601 -- pa_debug.debug('Each row, drrowid='||rdlrec.drrowid);
3602 -- pa_debug.debug('Each row, curr_rowid='||curr_rowid);
3603
3604 row_processed := row_processed + 1;
3605
3606 IF rdlrec.drrowid <> curr_rowid THEN
3607 -- pa_debug.debug('drrowid='||rdlrec.drrowid);
3608 -- pa_debug.debug('curr_rowid='||curr_rowid);
3609
3610 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3611 UPDATE
3612 pa_draft_revenues
3613 SET
3614 resource_accumulated_flag = 'Y',
3615 last_updated_by = x_last_updated_by,
3616 last_update_login = x_last_update_login,
3617 request_id = x_request_id,
3618 program_application_id = x_program_application_id,
3619 program_id = x_program_id
3620 WHERE
3621 ROWID = curr_rowid;
3622
3623 IF commit_rows >= pa_proj_accum_main.x_commit_size THEN
3624 COMMIT;
3625 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3626 pa_debug.debug('accum_revenue: ' || 'Number of Records Commited = '|| TO_CHAR(commit_rows));
3627 END IF;
3628 commit_rows := 0;
3629 END IF;
3630 END IF;
3631
3632 curr_rowid := rdlrec.drrowid;
3633 commit_rows := commit_rows + 1;
3634
3635 -- IF clause added for bug 2580808.
3636 -- IF x_mode <> 'I' THEN -- for bug 2650900
3637 IF (rdlrec.pa_period IS NULL OR rdlrec.gl_period IS NULL) THEN
3638 OPEN selrdls1(ROWIDTOCHAR(rdlrec.drrowid));
3639 FETCH selrdls1 INTO rdlrec1;
3640 CLOSE selrdls1;
3641 END IF;
3642 -- END IF; -- for bug 2650900
3643
3644 create_txn_accum(
3645 rdlrec.project_id,
3646 rdlrec.task_id,
3647 -- Commented out for 2580808
3648 -- rdlrec.pa_period,
3649 -- rdlrec.gl_period,
3650 NVL(rdlrec.pa_period,rdlrec1.pa_period1), -- added for bug 2580808
3651 NVL(rdlrec.gl_period,rdlrec1.gl_period1), --added for bug 2580808
3652 rdlrec.week_ending_date,
3653 rdlrec.month_ending_date,
3654 rdlrec.person_id,
3655 rdlrec.job_id,
3656 NULL, -- vendor_id
3657 rdlrec.expenditure_type,
3658 rdlrec.organization_id,
3659 rdlrec.non_labor_resource,
3660 rdlrec.non_labor_resource_org_id,
3661 rdlrec.expenditure_category,
3662 rdlrec.revenue_category,
3663 rdlrec.event_type, -- event_type
3664 rdlrec.event_type_classification, -- event_type_classification
3665 rdlrec.system_linkage_function,
3666 rdlrec.line_type, -- x_line_type = 'R' for RDL
3667 NULL, -- cost_ind_compiled_set_id
3668 rdlrec.rev_ind_compiled_set_id,
3669 rdlrec.inv_ind_compiled_set_id,
3670 NULL, -- cmt_ind_compiled_set_id
3671 x_txn_accum_id,
3672 x_err_stage,
3673 x_err_code);
3674
3675 -- Create a row for drilldown in pa_txn_accum_details Now
3676
3677 create_txn_accum_details(
3678 x_txn_accum_id,
3679 rdlrec.line_type, -- RDLS
3680 rdlrec.expenditure_item_id,
3681 rdlrec.line_num,
3682 rdlrec.event_num, -- Event Num
3683 NULL, -- CMT_LINE_ID
3684 rdlrec.project_id,
3685 rdlrec.task_id,
3686 x_err_stage,
3687 x_err_code);
3688
3689 -- Accume this row in txn_accum_id = x_txn_accum_id
3690 -- also create rows for drilldown
3691
3692 accum_rev_txn(
3693 x_txn_accum_id,
3694 rdlrec.amount,
3695 rdlrec.unit_of_measure,
3696 x_err_stage,
3697 x_err_code);
3698
3699 END LOOP;
3700
3701 --- Update the DR.Resource_accumulated_flag = 'Y' Now
3702 UPDATE
3703 pa_draft_revenues
3704 SET
3705 resource_accumulated_flag = 'Y',
3706 last_updated_by = x_last_updated_by,
3707 last_update_login = x_last_update_login,
3708 request_id = x_request_id,
3709 program_application_id = x_program_application_id,
3710 program_id = x_program_id
3711 WHERE
3712 ROWID = curr_rowid;
3713
3714 COMMIT;
3715
3716 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3717 pa_debug.debug('accum_revenue: ' || 'Number of Draft Revenues Processed = '||TO_CHAR(row_processed));
3718 END IF;
3719
3720 EXCEPTION
3721 WHEN OTHERS THEN
3722 x_err_code := SQLCODE;
3723 RAISE;
3724
3725 END accum_revenue;
3726
3727 -- Procedure to accumulate the commitments
3728 -- The pa_period and the gl_period parameter are not passed to
3729 -- this routine, since the commitments are always accumulated
3730 -- in the current pa_period and gl_period
3731
3732
3733 -- Name: Accum_Commitments
3734 --
3735 -- History
3736 --
3737 --
3738 -- 12-FEB-99 jwhite For the accum_commitments procedure, added
3739 -- MC related design elements.
3740 -- Numerous changes were made to the procedure.
3741 --
3742 -- 04-MAR-99 jwhite Implemented latest design changes:
3743 -- 1) Removed all references to amount_delivered columns.
3744 -- 2) Added generation_error_flag to Update
3745 -- pa_project_accum_headers
3746 -- 3) Removed pa_debug design elements.
3747 --
3748 -- 03-DEC-2001 jwhite Bug 2119738
3749 -- The parameter list for the following was changed:
3750 -- pa_multi_currency_txn.get_currency_amounts. This change
3751 -- was not communicated to the Reporting team. System
3752 -- testing revealed this oversight.
3753 --
3754
3755 PROCEDURE accum_commitments
3756 ( x_start_project_id IN NUMBER,
3757 x_end_project_id IN NUMBER,
3758 x_system_linkage_function IN VARCHAR2, -- Default value removed to avoid GSCC warning File.Pkg.22
3759 x_err_stage IN OUT NOCOPY VARCHAR2,
3760 x_err_code IN OUT NOCOPY NUMBER)
3761 IS
3762 P_DEBUG_MODE varchar2(1) :=NVL(FND_PROFILE.VALUE('PA_DEBUG_MODE'),'N');/*Added the default profile option variable initialization for bug 2674619*/
3763 --
3764 -- Cursors ---------------------------------------------------
3765 --
3766 -- The cursor selcmts selects all PA_COMMITMENT_TXNS which
3767 -- satisfy the given parameters
3768
3769 CURSOR selcmts IS
3770 SELECT
3771 pct.cmt_line_id,
3772 pct.project_id,
3773 pct.task_id,
3774 pa_utils.GetWeekEnding(pct.expenditure_item_date) week_ending_date,
3775 LAST_DAY(pct.expenditure_item_date) month_ending_date,
3776 pct.pa_period,
3777 pct.gl_period,
3778 pct.organization_id,
3779 pct.vendor_id,
3780 pct.expenditure_type,
3781 pct.expenditure_category,
3782 pct.revenue_category,
3783 pct.system_linkage_function,
3784 pct.cmt_ind_compiled_set_id,
3785 pct.expenditure_item_date,
3786 pct.denom_currency_code,
3787 pct.denom_raw_cost,
3788 pct.denom_burdened_cost,
3789 pct.acct_currency_code,
3790 pct.acct_rate_date,
3791 pct.acct_rate_type,
3792 pct.acct_exchange_rate,
3793 pct.acct_raw_cost,
3794 pct.acct_burdened_cost,
3795 pct.receipt_currency_code,
3796 pct.receipt_currency_amount,
3797 pct.receipt_exchange_rate
3798 FROM
3799 pa_commitment_txns pct
3800 -- Bug#2634995 - removed the reference to pa_implentations as it is not used in the SQL
3801 -- ,pa_implementations pi
3802 WHERE
3803 pct.project_id = x_start_project_id -- BETWEEN x_start_project_id AND x_end_project_id commented for bug 3736097
3804 AND pct.system_linkage_function||'' =
3805 NVL(x_system_linkage_function,pct.system_linkage_function);
3806
3807 -- This cursor retrives the Project Currency Code for the project.
3808 /* Bug# 2158736 - Included projfunc_currency_code in the cursor */
3809
3810 CURSOR l_project_curr_code_csr
3811 (l_project_id pa_projects.project_id%TYPE)
3812 IS
3813 SELECT project_currency_code,projfunc_currency_code
3814 FROM pa_projects p
3815 WHERE p.project_id = l_project_id;
3816
3817
3818 --
3819 -- Local Variables -------------------------------------------
3820 --
3821
3822 -- Procedure Variables
3823 l_proj_curr_OK VARCHAR2(1) := 'Y';
3824 l_project_curr_code pa_projects.project_currency_code%TYPE := NULL;
3825 l_sum_exception_code pa_project_accum_headers.sum_exception_code%TYPE := NULL;
3826
3827
3828 -- Main LOOP Variables
3829 cmtrec selcmts%ROWTYPE;
3830 x_txn_accum_id NUMBER;
3831 row_processed NUMBER;
3832 l_cmtrec_curr_OK VARCHAR2(1);
3833 l_cmt_rejection_code pa_commitment_txns.cmt_rejection_code%TYPE;
3834 l_err_msg VARCHAR2(2000);
3835
3836 -- Client Extension API Variables
3837 l_Project_Rate_Type pa_commitment_txns.project_rate_type%TYPE;
3838 l_Project_Rate_Date DATE;
3839 l_project_exch_rate NUMBER;
3840 l_Num_Rate NUMBER;
3841 l_Denom_Rate NUMBER;
3842 l_Converted_Amount NUMBER;
3843 l_Msg_Application fnd_application.application_short_name%TYPE;
3844 l_Msg_Data pa_commitment_txns.cmt_rejection_code%TYPE;
3845 l_Msg_Count NUMBER;
3846
3847 -- Multicurrency API Variables
3848 l_amount_out NUMBER;
3849 l_tot_cmt_raw_cost NUMBER;
3850 l_tot_cmt_burdened_cost NUMBER;
3851 l_status VARCHAR2(200) := NULL;
3852 l_stage NUMBER := NULL;
3853
3854 -- Bug 2119738: New Parameters
3855 l_SYSTEM_LINKAGE pa_expenditure_items_all.SYSTEM_LINKAGE_FUNCTION%TYPE :=NULL;
3856 l_PROJECT_RAW_COST NUMBER := NULL;
3857 l_PROJFUNC_CURR_CODE pa_projects.project_currency_code%TYPE := NULL;
3858 l_PROJFUNC_COST_RATE_TYPE pa_commitment_txns.project_rate_type%TYPE := NULL;
3859 l_PROJFUNC_COST_RATE_DATE DATE := NULL;
3860 l_PROJFUNC_COST_EXCH_RATE NUMBER := NULL;
3861
3862 -- added for FP.M
3863 l_PROJECT_BURDENED_COST NUMBER := NULL;
3864
3865 BEGIN
3866
3867 x_txn_accum_id :=0;
3868 x_err_code :=0;
3869
3870 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
3871 x_err_stage := 'Accumulating Commitments';
3872 row_processed :=0;
3873 pa_debug.debug('accum_commitments: ' || x_err_stage);
3874 END IF;
3875
3876
3877 -- Get Project Currency Code for Project
3878
3879 /* Bug# 2158736 - Included project functional currency code in the fetch */
3880
3881 OPEN l_project_curr_code_csr(x_start_project_id);
3882 FETCH l_project_curr_code_csr INTO l_project_curr_code,l_projfunc_curr_code;
3883 CLOSE l_project_curr_code_csr;
3884
3885 FOR cmtrec IN selcmts LOOP
3886
3887 row_processed := row_processed + 1;
3888
3889 -- Initialize Cmtrec Local Variables
3890
3891 l_cmtrec_curr_OK := 'Y';
3892 l_project_Rate_Type := NULL;
3893 l_project_Rate_Date := cmtrec.expenditure_item_date;
3894 l_project_exch_rate := NULL;
3895 l_Num_Rate := NULL;
3896 l_Denom_Rate := NULL;
3897 l_Converted_Amount := NULL;
3898 l_Msg_Application := NULL;
3899 l_Msg_Data := NULL;
3900 l_Msg_Count := NULL;
3901 l_amount_out := NULL;
3902 l_tot_cmt_raw_cost := NULL;
3903 l_tot_cmt_burdened_cost := NULL;
3904 l_status := NULL;
3905 l_stage := NULL;
3906 l_cmt_rejection_code := NULL;
3907 l_err_msg := NULL;
3908
3909
3910 create_txn_accum(
3911 cmtrec.project_id,
3912 cmtrec.task_id,
3913 cmtrec.pa_period,
3914 cmtrec.gl_period,
3915 cmtrec.week_ending_date,
3916 cmtrec.month_ending_date,
3917 NULL, -- person_id
3918 NULL, -- Job_id
3919 cmtrec.vendor_id,
3920 cmtrec.expenditure_type,
3921 cmtrec.organization_id,
3922 NULL, -- non_labor_resource
3923 NULL, -- non_labor_resource_org_id
3924 cmtrec.expenditure_category,
3925 cmtrec.revenue_category,
3926 NULL, -- event_type
3927 NULL, -- event_type_classification
3928 cmtrec.system_linkage_function,
3929 'M', -- x_line_type = 'M' for commitments
3930 NULL, -- cost_ind_compiled_set_id
3931 NULL, -- rev_ind_compiled_set_id
3932 NULL, -- inv_ind_compiled_set_id
3933 cmtrec.cmt_ind_compiled_set_id,
3934 x_txn_accum_id,
3935 x_err_stage,
3936 x_err_code);
3937
3938 -- Create a row for drilldown in pa_txn_accume_details Now
3939
3940 create_txn_accum_details(
3941 x_txn_accum_id,
3942 'M', -- pa_commitments_txns
3943 NULL, -- expenditure_item_id
3944 NULL, -- line_num
3945 NULL, -- event_num
3946 cmtrec.cmt_line_id,
3947 cmtrec.project_id,
3948 cmtrec.task_id,
3949 x_err_stage,
3950 x_err_code);
3951
3952
3953 --
3954 -- VALIDATION Currency Business Rules ------------
3955 --
3956 -- Go Here!
3957 --
3958 -- Set l_cmtrec_curr_OK to 'N' if any one of the rules are
3959 -- violated.
3960 --
3961
3962
3963 --
3964 -- PROJECT COLUMN DERIVATION --------------------
3965 --
3966
3967 IF (l_cmtrec_curr_OK = 'Y')
3968 THEN
3969
3970
3971 -- RAW COST Derivation
3972
3973 IF (l_cmtrec_curr_OK = 'Y')
3974 THEN
3975
3976 pa_multi_currency_txn.get_currency_amounts
3977 (p_project_curr_code => l_project_curr_code
3978 , p_ei_date => cmtrec.expenditure_item_date
3979 , p_task_id => cmtrec.task_id
3980 , p_denom_raw_cost => cmtrec.denom_raw_cost
3981 , p_denom_curr_code => cmtrec.denom_currency_code
3982 , p_acct_curr_code => cmtrec.acct_currency_code
3983 , p_accounted_flag => 'Y' /* Bug 1642321 manokuma */
3984 , p_acct_rate_date => cmtrec.acct_rate_date
3985 , p_acct_rate_type => cmtrec.acct_rate_type
3986 , p_acct_exch_rate => cmtrec.acct_exchange_rate
3987 , p_acct_raw_cost => cmtrec.acct_raw_cost
3988 , p_project_rate_type => l_project_rate_type
3989 , p_project_rate_date => l_project_rate_date
3990 , p_project_exch_rate => l_project_exch_rate
3991 , P_PROJFUNC_RAW_COST => l_amount_out
3992 , p_status => l_status
3993 , p_stage => l_stage
3994 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
3995 , P_PROJECT_RAW_COST => l_PROJECT_RAW_COST
3996 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
3997 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
3998 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
3999 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
4000 );
4001
4002 l_tot_cmt_raw_cost := l_amount_out;
4003
4004 IF (l_status IS NOT NULL) -- Error returned
4005 THEN
4006
4007 l_cmt_rejection_code := l_status;
4008 l_cmtrec_curr_OK := 'N';
4009
4010 END IF; --(l_status IS NOT NULL)
4011
4012 END IF; --RAW COST
4013
4014 -- BURDENED COST Derivation
4015
4016 IF (l_cmtrec_curr_OK = 'Y')
4017 THEN
4018
4019 pa_multi_currency_txn.get_currency_amounts
4020 (p_project_curr_code => l_project_curr_code
4021 , p_ei_date => cmtrec.expenditure_item_date
4022 , p_task_id => cmtrec.task_id
4023 , p_denom_raw_cost => cmtrec.denom_burdened_cost
4024 , p_denom_curr_code => cmtrec.denom_currency_code
4025 , p_acct_curr_code => cmtrec.acct_currency_code
4026 , p_accounted_flag => 'Y' /* Bug 1642321 manokuma */
4027 , p_acct_rate_date => cmtrec.acct_rate_date
4028 , p_acct_rate_type => cmtrec.acct_rate_type
4029 , p_acct_exch_rate => cmtrec.acct_exchange_rate
4030 , p_acct_raw_cost => cmtrec.acct_burdened_cost
4031 , p_project_rate_type => l_project_rate_type
4032 , p_project_rate_date => l_project_rate_date
4033 , p_project_exch_rate => l_project_exch_rate
4034 , P_PROJFUNC_RAW_COST => l_amount_out
4035 , p_status => l_status
4036 , p_stage => l_stage
4037 , P_SYSTEM_LINKAGE => l_SYSTEM_LINKAGE
4038 , P_PROJECT_RAW_COST => l_PROJECT_RAW_COST
4039 , P_PROJFUNC_CURR_CODE => l_PROJFUNC_CURR_CODE
4040 , P_PROJFUNC_COST_RATE_TYPE => l_PROJFUNC_COST_RATE_TYPE
4041 , P_PROJFUNC_COST_RATE_DATE => l_PROJFUNC_COST_RATE_DATE
4042 , P_PROJFUNC_COST_EXCH_RATE => l_PROJFUNC_COST_EXCH_RATE
4043 );
4044
4045 l_tot_cmt_burdened_cost := l_amount_out;
4046
4047 -- added for FP.M
4048 l_PROJECT_BURDENED_COST := l_PROJECT_RAW_COST;
4049
4050 IF (l_status IS NOT NULL) -- Error returned
4051 THEN
4052
4053 l_cmt_rejection_code := l_status;
4054 l_cmtrec_curr_OK := 'N';
4055
4056 END IF; --(l_status IS NOT NULL)
4057
4058 END IF; --BURDENED COST
4059
4060 END IF; -- DERIVATION SUBsection
4061
4062 --
4063 -- UPDATE COMMITMENT ROW -------------------------------
4064 --
4065
4066 IF (l_cmtrec_curr_OK = 'Y')
4067 THEN
4068 UPDATE pa_commitment_txns
4069 SET tot_cmt_raw_cost = l_tot_cmt_raw_cost
4070 , tot_cmt_burdened_cost = l_tot_cmt_burdened_cost
4071 , project_currency_code = l_project_curr_code
4072 , project_rate_date = l_project_rate_date
4073 , project_rate_type = l_project_rate_type
4074 , project_exchange_rate = l_project_exch_rate
4075 , proj_raw_cost = l_PROJECT_RAW_COST /* added for FP.M proj_raw_cost stores raw cost in project currency */
4076 , proj_burdened_cost = l_PROJECT_BURDENED_COST /* added for FP.M proj_burdened_cost stores burdened cost in project currency */
4077 WHERE cmt_line_id = cmtrec.cmt_line_id;
4078
4079 ELSE
4080
4081 UPDATE pa_commitment_txns
4082 SET generation_error_flag = 'Y'
4083 , cmt_rejection_code = l_cmt_rejection_code
4084 WHERE cmt_line_id = cmtrec.cmt_line_id;
4085
4086 l_proj_curr_OK := 'N';
4087
4088 END IF; -- UPDATE COMMITMENT ROW
4089
4090
4091 -- Accume this row now for txn_accum_id = x_txn_accum_id
4092 -- also create rows for drilldown
4093
4094 accum_cmt_txn(
4095 x_txn_accum_id,
4096 l_tot_cmt_raw_cost,
4097 l_tot_cmt_burdened_cost,
4098 x_err_stage,
4099 x_err_code);
4100
4101 END LOOP; -- CMTREC Processing
4102
4103 COMMIT;
4104 IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
4105 pa_debug.debug('accum_commitments: ' || 'Number of Commitments Processed = ' || TO_CHAR(row_processed));
4106 END IF;
4107
4108 --
4109 -- UPDATE PROJECT LOCKROW to Record Exception, If Any -----------
4110 --
4111
4112 IF (l_proj_curr_OK = 'Y')
4113 THEN
4114 l_sum_exception_code := NULL;
4115 ELSE
4116 l_sum_exception_code := 'PA_SUM_CMT_REJECTIONS';
4117 END IF;
4118
4119 UPDATE pa_project_accum_headers
4120 SET sum_exception_code = l_sum_exception_code
4121 WHERE project_id = x_start_project_id
4122 AND task_id = 0
4123 AND resource_list_id = 0
4124 AND resource_list_member_id = 0;
4125
4126
4127
4128 EXCEPTION
4129 WHEN OTHERS THEN
4130 x_err_code := SQLCODE;
4131 RAISE;
4132
4133 END accum_commitments;
4134
4135 END PA_TXN_ACCUMS;