[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_REVAL_FIX_PROCESS_PKG
Source
1 PACKAGE BODY IGC_CC_REVAL_FIX_PROCESS_PKG AS
2 /*$Header: IGCCREFB.pls 120.16.12010000.2 2008/08/29 13:14:52 schakkin ship $*/
3
4 --Bug 3199488 Start Block
5 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
8 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
9 l_event_level number:=FND_LOG.LEVEL_EVENT;
10 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
11 l_error_level number:=FND_LOG.LEVEL_ERROR;
12 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
13 --Bug 3199488 End Block
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_REVAL_FIX_PROCESS_PKG';
16
17 -- The flag determines whether to print debug information or not.
18 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
19 --g_debug_flag VARCHAR2(1) := 'N' ;
20 g_debug_msg VARCHAR2(10000) := NULL;
21
22 --
23 -- Generic Procedure for putting out NOCOPY debug information
24 --
25 /* commented out as per bug 3299548
26 PROCEDURE Output_Debug (
27 p_debug_msg IN VARCHAR2
28 );
29 */
30
31 /* Checks whether contract commitment is eligible for revalution fix*/
32 FUNCTION revalue_fix
33 (
34 p_cc_header_id IN NUMBER
35 ) RETURN BOOLEAN
36 IS
37 l_cc_headers_rec igc_cc_headers%ROWTYPE;
38 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
39 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
40
41 /* Contract Commitment detail payment forecast */
42 CURSOR c_payment_forecast(t_cc_acct_line_id NUMBER) IS
43 -- Performance Tuning, replaced the view igc_cc_det_pf_v
44 -- with igc_cc_det_pf
45 -- SELECT *
46 -- FROM igc_cc_det_pf_v
47 -- WHERE cc_acct_line_id = t_cc_acct_line_id;
48
49 SELECT ccdpf.ROWID,
50 ccdpf.cc_det_pf_line_id,
51 ccdpf.cc_det_pf_line_num,
52 NULL cc_acct_line_num,
53 ccdpf.cc_acct_line_id,
54 NULL parent_det_pf_line_num,
55 ccdpf.parent_det_pf_line_id,
56 ccdpf.Parent_acct_line_id,
57 ccdpf.cc_det_pf_entered_amt,
58 ccdpf.cc_det_pf_func_amt,
59 ccdpf.cc_det_pf_date,
60 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
61 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
62 ccdpf.cc_det_pf_unbilled_amt,
63 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
64 ccdpf.cc_det_pf_encmbrnc_amt,
65 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
66 ccdpf.cc_det_pf_encmbrnc_date,
67 ccdpf.cc_det_pf_encmbrnc_status,
68 ccdpf.context,
69 ccdpf.attribute1,
70 ccdpf.attribute2,
71 ccdpf.attribute3,
72 ccdpf.attribute4,
73 ccdpf.attribute5,
74 ccdpf.attribute6,
75 ccdpf.attribute7,
76 ccdpf.attribute8,
77 ccdpf.attribute9,
78 ccdpf.attribute10,
79 ccdpf.attribute11,
80 ccdpf.attribute12,
81 ccdpf.attribute13,
82 ccdpf.attribute14,
83 ccdpf.attribute15,
84 ccdpf.last_update_date,
85 ccdpf.last_updated_by,
86 ccdpf.last_update_login,
87 ccdpf.creation_date,
88 ccdpf.created_by
89 FROM igc_cc_det_pf ccdpf
90 WHERE cc_acct_line_id = t_cc_acct_line_id;
91
92
93 /* Contract Commitment account lines */
94
95 CURSOR c_account_lines(t_cc_header_id NUMBER) IS
96 -- Replaced the folllowing query with the one below for
97 -- performance tuning fixes.
98 -- The record definition of l_cc_acct_lines_rec is still based on
99 -- view igc_cc_acct_lines_v. Instead of selecting from the view,
100 -- select is being done from the base table, but all the columns
101 -- as defined in the view are retained even though they are not used.
102 -- This is just so that minimal change is made to the code.
103 /*
104 SELECT *
105 FROM igc_cc_acct_lines_v ccac
106 WHERE ccac.cc_header_id = t_cc_header_id;
107 */
108
109 SELECT ccal.ROWID,
110 ccal.cc_header_id,
111 NULL org_id,
112 NULL cc_type,
113 NULL cc_type_code,
114 NULL cc_num,
115 ccal.cc_acct_line_id,
116 ccal.cc_acct_line_num,
117 ccal.cc_acct_desc,
118 ccal.parent_header_id,
119 ccal.parent_acct_line_id,
120 NULL parent_cc_acct_line_num,
121 NULL cc_budget_acct_desc,
122 ccal.cc_budget_code_combination_id,
123 NULL cc_charge_acct_desc,
124 ccal.cc_charge_code_combination_id,
125 ccal.cc_acct_entered_amt,
126 ccal.cc_acct_func_amt,
127 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
128 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
129 ccal.cc_acct_encmbrnc_amt,
130 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
131 ccal.cc_acct_unbilled_amt,
132 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
133 NULL project_number ,
134 ccal.project_id,
135 NULL task_number,
136 ccal.task_id,
137 ccal.expenditure_type,
138 NULL expenditure_org_name,
139 ccal.expenditure_org_id,
140 ccal.expenditure_item_date,
141 ccal.cc_acct_taxable_flag,
142 NULL tax_name,
143 NULL tax_id, -- Added for Bug 6472296 r12 EBtax uptake for CC
144 ccal.cc_acct_encmbrnc_status,
145 ccal.cc_acct_encmbrnc_date,
146 ccal.context,
147 ccal.attribute1,
148 ccal.attribute2,
149 ccal.attribute3,
150 ccal.attribute4,
151 ccal.attribute5,
152 ccal.attribute6,
153 ccal.attribute7,
154 ccal.attribute8,
155 ccal.attribute9,
156 ccal.attribute10,
157 ccal.attribute11,
158 ccal.attribute12,
159 ccal.attribute13,
160 ccal.attribute14,
161 ccal.attribute15,
162 ccal.created_by,
163 ccal.creation_date,
164 ccal.last_updated_by,
165 ccal.last_update_date,
166 ccal.last_update_login,
167 ccal.cc_func_withheld_amt,
168 ccal.cc_ent_withheld_amt,
169 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
170 NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
171 ccal.TAX_CLASSIF_CODE /* Bug 6472296 for r12 EBtax uptake for CC */
172 FROM igc_cc_acct_lines ccal
173 WHERE ccal.cc_header_id = t_cc_header_id;
174
175 reval_fix BOOLEAN := FALSE;
176
177 BEGIN
178
179 reval_fix := FALSE;
180
181 SELECT *
182 INTO l_cc_headers_rec
183 FROM igc_cc_headers
184 WHERE cc_header_id = p_cc_header_id;
185
186 OPEN c_account_lines(p_cc_header_id);
187
188 LOOP
189 FETCH c_account_lines INTO l_cc_acct_lines_rec;
190
191 EXIT WHEN c_account_lines%NOTFOUND;
192
193 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
194
195 LOOP
196 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
197
198 EXIT WHEN c_payment_forecast%NOTFOUND;
199
200 IF
201 (
202 ROUND((l_cc_pmt_fcst_rec.cc_det_pf_entered_amt - l_cc_pmt_fcst_rec.cc_det_pf_billed_amt)
203 *
204 l_cc_headers_rec.conversion_rate,2)
205 )
206 <>
207 (ROUND(l_cc_pmt_fcst_rec.cc_det_pf_func_amt,2) - ROUND(l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt,2))
208 THEN
209 RETURN(TRUE);
210
211 END IF;
212
213
214 END LOOP;
215
216 CLOSE c_payment_forecast;
217
218 END LOOP;
219
220 CLOSE c_account_lines;
221
222 RETURN(reval_fix);
223
224 END revalue_fix;
225
226 /* Commented out as per bug 3299548
227 --
228 -- Output_Debug Procedure is the Generic procedure designed for outputting debug
229 -- information that is required from this procedure.
230 --
231 -- Parameters :
232 --
233 -- p_debug_msg ==> Record to be output into the debug log file.
234 --
235 PROCEDURE Output_Debug (
236 p_debug_msg IN VARCHAR2
237 ) IS
238
239 -- Constants :
240
241 l_prod VARCHAR2(3) := 'IGC';
242 l_sub_comp VARCHAR2(6) := 'CC_RVF';
243 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
244 l_Return_Status VARCHAR2(1);
245 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';
246
247 BEGIN
248
249 IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
250 p_profile_log_name => l_profile_name,
251 p_prod => l_prod,
252 p_sub_comp => l_sub_comp,
253 p_filename_val => NULL,
254 x_Return_Status => l_Return_Status
255 );
256
257 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
258 raise FND_API.G_EXC_ERROR;
259 END IF;
260
261 RETURN;
262
263 -- --------------------------------------------------------------------
264 -- Exception handler section for the Output_Debug procedure.
265 -- --------------------------------------------------------------------
266 EXCEPTION
267
268 WHEN FND_API.G_EXC_ERROR THEN
269 RETURN;
270
271 WHEN OTHERS THEN
272 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
273 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
274 END IF;
275 RETURN;
276
277 END Output_Debug;
278
279 */
280
281 /* Checks whether all the invoices related to
282 Contract Commitment are either approved or cancelled */
283
284 FUNCTION validate_params(p_process_phase IN VARCHAR2,
285 p_sob_id IN NUMBER,
286 p_org_id IN NUMBER,
287 p_cc_header_id IN NUMBER,
288 p_revalue_fix_date IN DATE,
289 p_request_id IN NUMBER)
290 RETURN BOOLEAN
291 IS
292 l_period_status gl_period_statuses.closing_status%TYPE;
293 l_cc_period_status igc_cc_periods.cc_period_status%TYPE;
294 l_rate NUMBER;
295 l_message igc_cc_process_exceptions.exception_reason%TYPE;
296 l_cc_num igc_cc_headers.cc_num%TYPE;
297 BEGIN
298
299 BEGIN
300
301 SELECT cc_num
302 INTO l_cc_num
303 FROM igc_cc_headers
304 WHERE cc_header_id = p_cc_header_id;
305
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
309 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Validate_Params');
310 END IF;
311
312 -- bug 3199488, start block
313 IF (l_unexp_level >= l_debug_level) THEN
314 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
315 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
316 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
317 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.validate_params.Unexp1',TRUE);
318 END IF;
319 -- bug 3199488, end block
320
321 RETURN (FALSE);
322 END;
323
324 IF (p_process_phase = 'F')
325 THEN
326
327 IF (NOT revalue_fix(p_cc_header_id))
328 THEN
329 l_message := NULL;
330 FND_MESSAGE.SET_NAME('IGC','IGC_CC_HAS_NO_REV_VARIANCES');
331 FND_MESSAGE.SET_TOKEN('CC_NUM', l_cc_num ,TRUE);
332 l_message := FND_MESSAGE.GET;
333
334 INSERT INTO
335 igc_cc_process_exceptions
336 ( process_type,
337 process_phase,
338 cc_header_id,
339 cc_acct_line_id,
340 cc_det_pf_line_id,
341 exception_reason,
342 org_id,
343 set_of_books_id,
344 request_id
345 )
346 VALUES
347 (
348 'F',
349 'F',
350 NULL,
351 NULL,
352 NULL,
353 l_message,
354 p_org_id,
355 p_sob_id,
356 p_request_id
357 );
358
359 COMMIT;
360
361 RETURN(FALSE);
362
363 END IF;
364
365 IF (p_revalue_fix_date IS NOT NULL)
366 THEN
367
368 /* Check whether GL period is open */
369
370 BEGIN
371 SELECT gps.closing_status
372 INTO l_period_status
373 FROM gl_period_statuses gps,
374 gl_periods gp,
375 gl_sets_of_books gb,
376 gl_period_types gpt,
377 fnd_application fa
378 WHERE
379 gb.set_of_books_id = p_sob_id AND
380 gp.period_set_name = gb.period_set_name AND
381 gp.period_type = gb.accounted_period_type AND
382 /* Begin fix for bug 1569324 */
383 gp.adjustment_period_flag = 'N' AND
384 /* End fix for bug 1569324 */
385 gpt.period_type = gp.period_type AND
386 gps.set_of_books_id = gb.set_of_books_id AND
387 gps.period_name = gp.period_name AND
388 gps.application_id = fa.application_id AND
389 fa.application_short_name = 'SQLGL' AND
390 (gp.start_date <= p_revalue_fix_date AND gp.end_date >= p_revalue_fix_date);
391 EXCEPTION
392 WHEN NO_DATA_FOUND
393 THEN
394 NULL;
395
396 END;
397 END IF;
398
399 IF (l_period_status IS NULL) OR ( NVL(l_period_status,'X') <> 'O') OR (p_revalue_fix_date IS NULL)
400 THEN
401 l_message := NULL;
402 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REV_VAR_FIX_DATE');
403 FND_MESSAGE.SET_TOKEN('DATE',to_char( p_revalue_fix_date,'DD-MON-YYYY') ,TRUE);
404 l_message := FND_MESSAGE.GET;
405
406 INSERT INTO
407 igc_cc_process_exceptions
408 ( process_type,
409 process_phase,
410 cc_header_id,
411 cc_acct_line_id,
412 cc_det_pf_line_id,
413 exception_reason,
414 org_id,
415 set_of_books_id,
416 request_id
417 )
418 VALUES
419 ( 'F',
420 'F',
421 NULL,
422 NULL,
423 NULL,
424 l_message,
425 p_org_id,
426 p_sob_id,
427 p_request_id
428 );
429
430 COMMIT;
431
432 RETURN(FALSE);
433 END IF;
434
435 /* Check whether CC period is open */
436 BEGIN
437 SELECT ccp.cc_period_status
438 INTO l_cc_period_status
439 FROM igc_cc_periods ccp,
440 gl_periods gp ,
441 gl_sets_of_books gb
442 WHERE
443 ccp.period_set_name = gp.period_set_name AND
444 gp.period_set_name = gb.period_set_name AND
445 ccp.org_id = p_org_id AND
446 ccp.period_name = gp.period_name AND
447 gp.period_type = gb.accounted_period_type AND
448 gp.adjustment_period_flag = 'N' AND
449 gb.set_of_books_id = p_sob_id AND
450 (gp.start_date <= p_revalue_fix_date AND gp.end_date >= p_revalue_fix_date);
451 EXCEPTION
452 WHEN NO_DATA_FOUND
453 THEN
454 RETURN(FALSE);
455 END;
456
457 IF (l_cc_period_status IS NULL) OR (NVL(l_cc_period_status,'X') <> 'O')
458 THEN
459 l_message := NULL;
460 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REV_VAR_FIX_DATE');
461 FND_MESSAGE.SET_TOKEN('DATE',to_char( p_revalue_fix_date,'DD-MON-YYYY') ,TRUE);
462 l_message := FND_MESSAGE.GET;
463
464 INSERT INTO
465 igc_cc_process_exceptions
466 ( process_type,
467 process_phase,
468 cc_header_id,
469 cc_acct_line_id,
470 cc_det_pf_line_id,
471 exception_reason,
472 org_id,
473 set_of_books_id,
474 request_id
475 )
476 VALUES
477 ( 'F',
478 'F',
479 NULL,
480 NULL,
481 NULL,
482 l_message,
483 p_org_id,
484 p_sob_id,
485 p_request_id
486 );
487
488 COMMIT;
489
490 RETURN(FALSE);
491 END IF;
492
493 END IF;
494
495 RETURN(TRUE);
496
497 END validate_params;
498
499 FUNCTION lock_cc_po(p_sob_id IN NUMBER,
500 p_org_id IN NUMBER,
501 p_cc_header_id IN NUMBER,
502 p_request_id IN NUMBER)
503 RETURN BOOLEAN
504 IS
505 l_lock_cc BOOLEAN := TRUE;
506 l_lock_po BOOLEAN := TRUE;
507 l_message igc_cc_process_exceptions.exception_reason%TYPE;
508 l_cc_num igc_cc_headers.cc_num%TYPE;
509 BEGIN
510
511 SELECT cc_num
512 INTO l_cc_num
513 FROM igc_cc_headers
514 WHERE cc_header_id = p_cc_header_id;
515
516 l_lock_cc := TRUE;
517 l_lock_po := TRUE;
518
519 /* Lock all contract commitment being re-valued */
520 l_lock_cc := IGC_CC_REP_YEP_PVT.lock_cc(p_cc_header_id);
521
522 IF (NOT l_lock_cc)
523 THEN
524
525 l_message := NULL;
526 FND_MESSAGE.SET_NAME('IGC','IGC_CC_LOCK_FAILURE');
527 FND_MESSAGE.SET_TOKEN('CC_NUM', l_cc_num ,TRUE);
528 l_message := FND_MESSAGE.GET;
529
530 INSERT
531 INTO igc_cc_process_exceptions
532 ( process_type,
533 process_phase,
534 cc_header_id,
535 cc_acct_line_id,
536 cc_det_pf_line_id,
537 exception_reason,
538 org_id,
539 set_of_books_id,
540 request_id
541 )
542 VALUES
543 ( 'F',
544 'F',
545 p_cc_header_id,
546 NULL,
547 NULL,
548 l_message,
549 p_org_id,
550 p_sob_id,
551 p_request_id
552 );
553
554 END IF;
555
556
557
558 /* Lock all purchase orders related to contract commitments being re-valued */
559 l_lock_po := IGC_CC_REP_YEP_PVT.lock_po(p_cc_header_id);
560
561 IF (NOT l_lock_po)
562 THEN
563
564 l_message := NULL;
565 FND_MESSAGE.SET_NAME('IGC','IGC_CC_PO_LOCK_FAILURE');
566 FND_MESSAGE.SET_TOKEN('CC_NUM', l_cc_num ,TRUE);
567 l_message := FND_MESSAGE.GET;
568
569 INSERT
570 INTO igc_cc_process_exceptions
571 ( process_type,
572 process_phase,
573 cc_header_id,
574 cc_acct_line_id,
575 cc_det_pf_line_id,
576 exception_reason,
577 org_id,
578 set_of_books_id,
579 request_id
580 )
581 VALUES
582 ( 'F',
583 'F',
584 p_cc_header_id,
585 NULL,
586 NULL,
587 l_message,
588 p_org_id,
589 p_sob_id,
590 p_request_id
591 );
592
593 END IF;
594
595 IF (l_lock_po = TRUE) AND (l_lock_cc = TRUE)
596 THEN
597 RETURN(TRUE);
598 ELSE
599 RETURN(FALSE);
600
601 END IF;
602 EXCEPTION
603 WHEN OTHERS
604 THEN
605 RETURN(FALSE);
606
607 END lock_cc_po;
608
609 FUNCTION reval_fix_update(p_cc_header_id IN NUMBER,
610 p_rel_cc_header_id IN NUMBER,
611 p_revalue_fix_date IN DATE,
612 p_sob_id IN NUMBER,
613 p_org_id IN NUMBER,
614 p_sbc_on IN BOOLEAN,
615 p_cbc_on IN BOOLEAN,
616 p_prov_enc_on IN BOOLEAN,
617 p_conf_enc_on IN BOOLEAN,
618 p_validate_only IN VARCHAR2,
619 p_request_id IN NUMBER,
620 p_message OUT NOCOPY VARCHAR2,
621 p_err_header_id OUT NOCOPY NUMBER,
622 p_err_acct_line_id OUT NOCOPY NUMBER,
623 p_err_det_pf_line_id OUT NOCOPY NUMBER
624 )
625 RETURN VARCHAR2
626 IS
627
628 l_cc_headers_rec igc_cc_headers%ROWTYPE;
629 l_rel_cc_headers_rec igc_cc_headers%ROWTYPE;
630 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
631 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
632 l_exception igc_cc_process_exceptions.exception_reason%TYPE;
633 l_action_hist_msg igc_cc_actions.cc_action_notes%TYPE;
634
635
636 CURSOR c_acct_lines(p_cc_header_id NUMBER)
637 IS
638 -- Replaced the folllowing query with the one below for
639 -- performance tuning fixes.
640 -- The record definition of l_cc_acct_lines_rec is still based on
641 -- view igc_cc_acct_lines_v. Instead of selecting from the view,
642 -- select is being done from the base table, but all the columns
643 -- as defined in the view are retained even though they are not used.
644 -- This is just so that minimal change is made to the code.
645
646 -- SELECT *
647 -- FROM igc_cc_acct_lines_v
648 -- WHERE cc_header_id = p_cc_header_id;
649
650
651 SELECT ccal.ROWID,
652 ccal.cc_header_id,
653 NULL org_id,
654 NULL cc_type,
655 NULL cc_type_code,
656 NULL cc_num,
657 ccal.cc_acct_line_id,
658 ccal.cc_acct_line_num,
659 ccal.cc_acct_desc,
660 ccal.parent_header_id,
661 ccal.parent_acct_line_id,
662 NULL parent_cc_acct_line_num,
663 NULL cc_budget_acct_desc,
664 ccal.cc_budget_code_combination_id,
665 NULL cc_charge_acct_desc,
666 ccal.cc_charge_code_combination_id,
667 ccal.cc_acct_entered_amt,
668 ccal.cc_acct_func_amt,
669 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
670 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
671 ccal.cc_acct_encmbrnc_amt,
672 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
673 ccal.cc_acct_unbilled_amt,
674 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
675 NULL project_number ,
676 ccal.project_id,
677 NULL task_number,
678 ccal.task_id,
679 ccal.expenditure_type,
680 NULL expenditure_org_name,
681 ccal.expenditure_org_id,
682 ccal.expenditure_item_date,
683 ccal.cc_acct_taxable_flag,
684 NULL tax_name,
685 NULL tax_id, -- Bug 6472296 for r12 EBtax uptake for CC
686 ccal.cc_acct_encmbrnc_status,
687 ccal.cc_acct_encmbrnc_date,
688 ccal.context,
689 ccal.attribute1,
690 ccal.attribute2,
691 ccal.attribute3,
692 ccal.attribute4,
693 ccal.attribute5,
694 ccal.attribute6,
695 ccal.attribute7,
696 ccal.attribute8,
697 ccal.attribute9,
698 ccal.attribute10,
699 ccal.attribute11,
700 ccal.attribute12,
701 ccal.attribute13,
702 ccal.attribute14,
703 ccal.attribute15,
704 ccal.created_by,
705 ccal.creation_date,
706 ccal.last_updated_by,
707 ccal.last_update_date,
708 ccal.last_update_login,
709 ccal.cc_func_withheld_amt,
710 ccal.cc_ent_withheld_amt,
711 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
712 NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
713 ccal.TAX_CLASSIF_CODE /* Bug No : 6341012. E-BTax uptake.New field is ebing added to Account_Lines Table R12 */
714 FROM igc_cc_acct_lines ccal
715 WHERE cc_header_id = p_cc_header_id;
716
717 CURSOR c_pf_lines(p_cc_acct_line_id NUMBER)
718 IS
719 -- Performance Tuning, replaced view igc_cc_det_pf_v with igc_cc_det_pf
720 -- SELECT *
721 -- FROM igc_cc_det_pf_v
722 -- WHERE cc_acct_line_id = p_cc_acct_line_id;
723
724 SELECT ccdpf.ROWID,
725 ccdpf.cc_det_pf_line_id,
726 ccdpf.cc_det_pf_line_num,
727 NULL cc_acct_line_num,
728 ccdpf.cc_acct_line_id,
729 NULL parent_det_pf_line_num,
730 ccdpf.parent_det_pf_line_id,
731 ccdpf.parent_acct_line_id,
732 ccdpf.cc_det_pf_entered_amt,
733 ccdpf.cc_det_pf_func_amt,
734 ccdpf.cc_det_pf_date,
735 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
736 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
737 ccdpf.cc_det_pf_unbilled_amt,
738 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
739 ccdpf.cc_det_pf_encmbrnc_amt,
740 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
741 ccdpf.cc_det_pf_encmbrnc_date,
742 ccdpf.cc_det_pf_encmbrnc_status,
743 ccdpf.context,
744 ccdpf.attribute1,
745 ccdpf.attribute2,
746 ccdpf.attribute3,
747 ccdpf.attribute4,
748 ccdpf.attribute5,
749 ccdpf.attribute6,
750 ccdpf.attribute7,
751 ccdpf.attribute8,
752 ccdpf.attribute9,
753 ccdpf.attribute10,
754 ccdpf.attribute11,
755 ccdpf.attribute12,
756 ccdpf.attribute13,
757 ccdpf.attribute14,
758 ccdpf.attribute15,
759 ccdpf.last_update_date,
760 ccdpf.last_updated_by,
761 ccdpf.last_update_login,
762 ccdpf.creation_date,
763 ccdpf.created_by
764 FROM igc_cc_det_pf ccdpf
765 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id;
766
767 l_hdr_row_id VARCHAR2(18);
768 l_hist_hdr_row_id VARCHAR2(18);
769 l_acct_row_id VARCHAR2(18);
770 l_hist_acct_row_id VARCHAR2(18);
771 l_pf_row_id VARCHAR2(18);
772 l_hist_pf_row_id VARCHAR2(18);
773 l_action_row_id VARCHAR2(18);
774
775
776 l_new_cc_det_pf_func_amt igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
777 l_new_cc_acct_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
778
779 l_api_version CONSTANT NUMBER := 1.0;
780 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
781 l_commit VARCHAR2(1) := FND_API.G_FALSE;
782 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
783 l_return_status VARCHAR2(1);
784 l_msg_count NUMBER;
785 l_msg_data VARCHAR2(2000);
786 G_FLAG VARCHAR2(1);
787
788 l_approval_status igc_cc_process_data.old_approval_status%TYPE;
789
790 l_Last_Updated_By NUMBER := FND_GLOBAL.USER_ID;
791 l_Last_Update_Login NUMBER := FND_GLOBAL.LOGIN_ID;
792 l_Created_By NUMBER := FND_GLOBAL.USER_ID;
793
794 l_cc_acct_date igc_cc_headers.cc_acct_date%TYPE;
795 l_conversion_date igc_cc_headers.conversion_date%TYPE;
796 l_conversion_rate igc_cc_headers.conversion_rate%TYPE;
797 l_cc_version_num igc_cc_headers.cc_version_num%TYPE;
798 l_cc_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
799
800 l_cc_acct_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
801 l_cc_acct_encmbrnc_date igc_cc_acct_lines.cc_acct_encmbrnc_date%TYPE;
802 l_cc_acct_encmbrnc_amt igc_cc_acct_lines.cc_acct_encmbrnc_amt%TYPE ;
803
804 l_cc_det_pf_date igc_cc_det_pf.cc_det_pf_date%TYPE;
805 l_cc_det_pf_encmbrnc_date igc_cc_det_pf.cc_det_pf_encmbrnc_date%TYPE;
806 l_cc_det_pf_func_amt igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
807 l_cc_det_pf_encmbrnc_amt igc_cc_det_pf.cc_det_pf_encmbrnc_amt%TYPE;
808
809 l_det_pf_func_amt_total igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
810
811 l_cc_acct_func_amt_total igc_cc_acct_lines.cc_acct_func_amt%TYPE;
812
813 l_rel_conversion_rate igc_cc_headers.conversion_rate%TYPE;
814
815 BEGIN
816
817 SELECT *
818 INTO l_cc_headers_rec
819 FROM igc_cc_headers
820 WHERE cc_header_id = p_cc_header_id;
821
822
823 SELECT old_approval_status
824 INTO l_approval_status
825 FROM igc_cc_process_data
826 WHERE cc_header_id = p_cc_header_id AND
827 request_id = p_request_id ;
828
829 IF (p_validate_only = 'N')
830 THEN
831 IF (l_cc_headers_rec.cc_type = 'C')
832 THEN
833 SELECT conversion_rate
834 INTO l_rel_conversion_rate
835 FROM igc_cc_headers
836 WHERE cc_header_id = p_rel_cc_header_id;
837 END IF;
838
839
840 /* Update Header History */
841 l_return_status := FND_API.G_RET_STS_SUCCESS;
842
843 IGC_CC_HEADER_HISTORY_PKG.Insert_Row(
844 l_api_version,
845 l_init_msg_list,
846 l_commit,
847 l_validation_level,
848 l_return_status,
849 l_msg_count,
850 l_msg_data,
851 l_hist_hdr_row_id,
852 l_cc_headers_rec.CC_HEADER_ID,
853 l_cc_headers_rec.ORG_ID,
854 l_cc_headers_rec.CC_TYPE,
855 l_cc_headers_rec.CC_NUM,
856 l_cc_headers_rec.CC_VERSION_NUM,
857 'R',
858 l_cc_headers_rec.CC_STATE,
859 l_cc_headers_rec.PARENT_HEADER_ID,
860 l_cc_headers_rec.CC_CTRL_STATUS,
861 l_cc_headers_rec.CC_ENCMBRNC_STATUS,
862 l_approval_status,
863 l_cc_headers_rec.VENDOR_ID,
864 l_cc_headers_rec.VENDOR_SITE_ID,
865 l_cc_headers_rec.VENDOR_CONTACT_ID,
866 l_cc_headers_rec.TERM_ID,
867 l_cc_headers_rec.LOCATION_ID,
868 l_cc_headers_rec.SET_OF_BOOKS_ID,
869 l_cc_headers_rec.CC_ACCT_DATE,
870 l_cc_headers_rec.CC_DESC,
871 l_cc_headers_rec.CC_START_DATE,
872 l_cc_headers_rec.CC_END_DATE,
873 l_cc_headers_rec.CC_OWNER_USER_ID,
874 l_cc_headers_rec.CC_PREPARER_USER_ID,
875 l_cc_headers_rec.CURRENCY_CODE,
876 l_cc_headers_rec.CONVERSION_TYPE,
877 l_cc_headers_rec.CONVERSION_DATE,
878 l_cc_headers_rec.CONVERSION_RATE,
879 l_cc_headers_rec.LAST_UPDATE_DATE,
880 l_cc_headers_rec.LAST_UPDATED_BY,
881 l_cc_headers_rec.LAST_UPDATE_LOGIN,
882 l_cc_headers_rec.CREATED_BY,
883 l_cc_headers_rec.CREATION_DATE,
884 l_cc_headers_rec.WF_ITEM_TYPE,
885 l_cc_headers_rec.WF_ITEM_KEY,
886 l_cc_headers_rec.CC_CURRENT_USER_ID,
887 -- Context should be after attributes, so moved below - ssmales 18/10/01
888 -- l_cc_headers_rec.CONTEXT,
889 l_cc_headers_rec.ATTRIBUTE1,
890 l_cc_headers_rec.ATTRIBUTE2,
891 l_cc_headers_rec.ATTRIBUTE3,
892 l_cc_headers_rec.ATTRIBUTE4,
893 l_cc_headers_rec.ATTRIBUTE5,
894 l_cc_headers_rec.ATTRIBUTE6,
895 l_cc_headers_rec.ATTRIBUTE7,
896 l_cc_headers_rec.ATTRIBUTE8,
897 l_cc_headers_rec.ATTRIBUTE9,
898 l_cc_headers_rec.ATTRIBUTE10,
899 l_cc_headers_rec.ATTRIBUTE11,
900 l_cc_headers_rec.ATTRIBUTE12,
901 l_cc_headers_rec.ATTRIBUTE13,
902 l_cc_headers_rec.ATTRIBUTE14,
903 l_cc_headers_rec.ATTRIBUTE15,
904 l_cc_headers_rec.CONTEXT,
905 l_cc_headers_rec.CC_GUARANTEE_FLAG,
906 G_FLAG);
907
908 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
909 THEN
910
911 l_EXCEPTION := NULL;
912 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_HST_INSERT');
913 l_EXCEPTION := FND_MESSAGE.GET;
914 p_message := l_exception;
915 p_err_header_id := l_cc_headers_rec.cc_header_id;
916 p_err_acct_line_id := NULL;
917 p_err_det_pf_line_id := NULL;
918
919 RETURN 'F';
920 END IF;
921
922 OPEN c_acct_lines(l_cc_headers_rec.cc_header_id);
923 LOOP
924 FETCH c_acct_lines INTO l_cc_acct_lines_rec;
925 EXIT WHEN c_acct_lines%NOTFOUND;
926
927 l_new_cc_acct_func_amt := 0;
928
929 l_cc_acct_func_amt_total := 0;
930
931 OPEN c_pf_lines(l_cc_acct_lines_rec.cc_acct_line_id);
932 LOOP
933 FETCH c_pf_lines INTO l_cc_pmt_fcst_rec;
934 EXIT WHEN c_pf_lines%NOTFOUND;
935
936 l_new_cc_det_pf_func_amt := 0;
937
938 IF (l_cc_headers_rec.cc_type = 'C')
939 THEN
940 l_det_pf_func_amt_total := 0;
941
942 BEGIN
943 -- Performance Tuning, replaced view igc_cc_det_pf_v
944 -- with igc_cc_det_pf_v and replaced the
945 -- following line.
946 -- - (a.cc_det_pf_func_amt - a.cc_det_pf_func_billed_amt)
947 SELECT
948 ( ( ( a.cc_det_pf_entered_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
949 * l_rel_conversion_rate
950 )
951 - (a.cc_det_pf_func_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
952 )
953 INTO l_det_pf_func_amt_total
954 FROM igc_cc_det_pf a,
955 igc_cc_acct_lines b
956 WHERE a.parent_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
957 a.cc_acct_line_id = b.cc_acct_line_id AND
958 b.cc_header_id = p_rel_cc_header_id;
959 EXCEPTION
960 WHEN NO_DATA_FOUND
961 THEN
962 l_det_pf_func_amt_total := 0;
963 END;
964
965 l_new_cc_det_pf_func_amt := l_det_pf_func_amt_total;
966 l_cc_acct_func_amt_total := l_cc_acct_func_amt_total + l_new_cc_det_pf_func_amt;
967 END IF;
968
969 IF (l_cc_headers_rec.cc_type = 'S') OR (l_cc_headers_rec.cc_type = 'R')
970 THEN
971 l_new_cc_det_pf_func_amt :=
972 ( ( ( l_cc_pmt_fcst_rec.cc_det_pf_entered_amt -
973 l_cc_pmt_fcst_rec.cc_det_pf_billed_amt)
974 * l_cc_headers_rec.conversion_rate
975 )
976 - (l_cc_pmt_fcst_rec.cc_det_pf_func_amt -
977 l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt)
978 );
979 l_cc_acct_func_amt_total := l_cc_acct_func_amt_total + l_new_cc_det_pf_func_amt;
980 END IF;
981
982 IF (l_new_cc_det_pf_func_amt <> 0)
983 THEN
984
985 /* Update PF Line History */
986
987 l_return_status := FND_API.G_RET_STS_SUCCESS;
988
989 IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
990 l_api_version,
991 l_init_msg_list,
992 l_commit,
993 l_validation_level,
994 l_return_status,
995 l_msg_count,
996 l_msg_data,
997 l_hist_pf_row_id,
998 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
999 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
1000 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
1001 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
1002 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
1003 l_cc_headers_rec.cc_version_num,
1004 'U',
1005 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
1006 l_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
1007 l_cc_pmt_fcst_rec.CC_Det_PF_Date,
1008 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
1009 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
1010 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
1011 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
1012 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1013 l_cc_pmt_fcst_rec.Last_Update_Date,
1014 l_cc_pmt_fcst_rec.Last_Updated_By,
1015 l_cc_pmt_fcst_rec.Last_Update_Login,
1016 l_cc_pmt_fcst_rec.Creation_Date,
1017 l_cc_pmt_fcst_rec.Created_By,
1018 l_cc_pmt_fcst_rec.Attribute1,
1019 l_cc_pmt_fcst_rec.Attribute2,
1020 l_cc_pmt_fcst_rec.Attribute3,
1021 l_cc_pmt_fcst_rec.Attribute4,
1022 l_cc_pmt_fcst_rec.Attribute5,
1023 l_cc_pmt_fcst_rec.Attribute6,
1024 l_cc_pmt_fcst_rec.Attribute7,
1025 l_cc_pmt_fcst_rec.Attribute8,
1026 l_cc_pmt_fcst_rec.Attribute9,
1027 l_cc_pmt_fcst_rec.Attribute10,
1028 l_cc_pmt_fcst_rec.Attribute11,
1029 l_cc_pmt_fcst_rec.Attribute12,
1030 l_cc_pmt_fcst_rec.Attribute13,
1031 l_cc_pmt_fcst_rec.Attribute14,
1032 l_cc_pmt_fcst_rec.Attribute15,
1033 l_cc_pmt_fcst_rec.Context,
1034 G_FLAG );
1035
1036 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1037 THEN
1038
1039 l_EXCEPTION := NULL;
1040 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_HST_INSERT');
1041 l_EXCEPTION := FND_MESSAGE.GET;
1042
1043 p_message := l_exception;
1044 p_err_header_id := l_cc_headers_rec.cc_header_id;
1045 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1046 p_err_det_pf_line_id := l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id;
1047
1048 RETURN 'F';
1049 END IF;
1050
1051
1052 l_new_cc_det_pf_func_amt :=
1053 l_cc_pmt_fcst_rec.cc_det_pf_func_amt +
1054 l_new_cc_det_pf_func_amt;
1055
1056
1057 IF (( ( (l_cc_headers_rec.cc_state = 'PR')
1058 OR (l_cc_headers_rec.cc_state = 'CL') )
1059 AND (p_sbc_on = TRUE) AND (p_prov_enc_on = TRUE)
1060 ) OR
1061 ( ( (l_cc_headers_rec.cc_state = 'CM')
1062 OR (l_cc_headers_rec.cc_state = 'CT') )
1063 AND (p_sbc_on = TRUE) AND (p_conf_enc_on = TRUE)
1064 ))
1065 /* Fix for bug 1634793 */
1066 AND (l_cc_headers_rec.cc_type <> 'R')
1067 THEN
1068 IF (l_cc_pmt_fcst_rec.cc_det_pf_date <= p_revalue_fix_date)
1069 THEN
1070 l_cc_det_pf_date := p_revalue_fix_date;
1071 l_cc_det_pf_encmbrnc_date := p_revalue_fix_date;
1072 END IF;
1073
1074 IF (l_cc_pmt_fcst_rec.cc_det_pf_date > p_revalue_fix_date)
1075 THEN
1076 l_cc_det_pf_date := l_cc_pmt_fcst_rec.cc_det_pf_date;
1077 l_cc_det_pf_encmbrnc_date := l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date;
1078 END IF;
1079 ELSE
1080 l_cc_det_pf_date := l_cc_pmt_fcst_rec.cc_det_pf_date;
1081 l_cc_det_pf_encmbrnc_date := l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date;
1082
1083 END IF;
1084
1085 l_cc_det_pf_func_amt := l_new_cc_det_pf_func_amt;
1086
1087 SELECT rowid
1088 INTO l_pf_row_id
1089 FROM igc_cc_det_pf
1090 WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
1091
1092 IGC_CC_DET_PF_PKG.Update_Row(
1093 l_api_version,
1094 l_init_msg_list,
1095 l_commit,
1096 l_validation_level,
1097 l_return_status,
1098 l_msg_count,
1099 l_msg_data,
1100 l_pf_row_id,
1101 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
1102 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
1103 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
1104 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
1105 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
1106 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
1107 l_cc_det_pf_func_amt,
1108 l_cc_det_pf_date,
1109 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
1110 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
1111 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt,
1112 l_cc_det_pf_encmbrnc_date,
1113 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1114 sysdate,
1115 l_Last_Updated_By,
1116 l_Last_Update_Login,
1117 l_cc_pmt_fcst_rec.Creation_Date,
1118 l_cc_pmt_fcst_rec.Created_By,
1119 l_cc_pmt_fcst_rec.Attribute1,
1120 l_cc_pmt_fcst_rec.Attribute2,
1121 l_cc_pmt_fcst_rec.Attribute3,
1122 l_cc_pmt_fcst_rec.Attribute4,
1123 l_cc_pmt_fcst_rec.Attribute5,
1124 l_cc_pmt_fcst_rec.Attribute6,
1125 l_cc_pmt_fcst_rec.Attribute7,
1126 l_cc_pmt_fcst_rec.Attribute8,
1127 l_cc_pmt_fcst_rec.Attribute9,
1128 l_cc_pmt_fcst_rec.Attribute10,
1129 l_cc_pmt_fcst_rec.Attribute11,
1130 l_cc_pmt_fcst_rec.Attribute12,
1131 l_cc_pmt_fcst_rec.Attribute13,
1132 l_cc_pmt_fcst_rec.Attribute14,
1133 l_cc_pmt_fcst_rec.Attribute15,
1134 l_cc_pmt_fcst_rec.Context,
1135 G_FLAG );
1136
1137
1138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1139 THEN
1140
1141 l_EXCEPTION := NULL;
1142 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_UPDATE');
1143 l_EXCEPTION := FND_MESSAGE.GET;
1144
1145 p_message := l_exception;
1146 p_err_header_id := l_cc_headers_rec.cc_header_id;
1147 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1148 p_err_det_pf_line_id := l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id;
1149
1150 RETURN 'F';
1151 END IF;
1152 END IF;
1153
1154 END LOOP;
1155
1156 CLOSE c_pf_lines;
1157
1158 l_new_cc_acct_func_amt := l_cc_acct_func_amt_total;
1159
1160 IF (l_new_cc_acct_func_amt <> 0)
1161 THEN
1162 /* Update Account Line History*/
1163 l_return_status := FND_API.G_RET_STS_SUCCESS;
1164
1165 IGC_CC_ACCT_LINE_HISTORY_PKG.Insert_Row(
1166 l_api_version ,
1167 l_init_msg_list,
1168 l_commit,
1169 l_validation_level,
1170 l_return_status,
1171 l_msg_count,
1172 l_msg_data,
1173 l_hist_acct_row_id,
1174 l_cc_acct_lines_rec.CC_Acct_Line_Id,
1175 l_cc_acct_lines_rec.CC_Header_Id,
1176 l_cc_acct_lines_rec.Parent_Header_Id,
1177 l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
1178 l_cc_acct_lines_rec.CC_Acct_Line_Num,
1179 l_cc_headers_rec.cc_version_num,
1180 'U',
1181 l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
1182 l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
1183 l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
1184 l_cc_acct_lines_rec.CC_Acct_Func_Amt,
1185 l_cc_acct_lines_rec.CC_Acct_Desc ,
1186 l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
1187 l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
1188 l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
1189 l_cc_acct_lines_rec.Tax_Id,
1190 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Amt,
1191 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Date,
1192 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
1193 l_cc_acct_lines_rec.Project_Id,
1194 l_cc_acct_lines_rec.Task_Id,
1195 l_cc_acct_lines_rec.Expenditure_Type,
1196 l_cc_acct_lines_rec.Expenditure_Org_Id,
1197 l_cc_acct_lines_rec.Expenditure_Item_Date,
1198 l_cc_acct_lines_rec.Last_Update_Date,
1199 l_cc_acct_lines_rec.Last_Updated_By,
1200 l_cc_acct_lines_rec.Last_Update_Login ,
1201 l_cc_acct_lines_rec.Creation_Date ,
1202 l_cc_acct_lines_rec.Created_By ,
1203 l_cc_acct_lines_rec.Attribute1,
1204 l_cc_acct_lines_rec.Attribute2,
1205 l_cc_acct_lines_rec.Attribute3,
1206 l_cc_acct_lines_rec.Attribute4,
1207 l_cc_acct_lines_rec.Attribute5,
1208 l_cc_acct_lines_rec.Attribute6,
1209 l_cc_acct_lines_rec.Attribute7,
1210 l_cc_acct_lines_rec.Attribute8,
1211 l_cc_acct_lines_rec.Attribute9,
1212 l_cc_acct_lines_rec.Attribute10 ,
1213 l_cc_acct_lines_rec.Attribute11,
1214 l_cc_acct_lines_rec.Attribute12,
1215 l_cc_acct_lines_rec.Attribute13,
1216 l_cc_acct_lines_rec.Attribute14,
1217 l_cc_acct_lines_rec.Attribute15,
1218 l_cc_acct_lines_rec.Context,
1219 l_cc_acct_lines_rec.CC_Func_Withheld_Amt,
1220 l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
1221 G_FLAG,
1222 l_cc_acct_lines_rec.TAX_CLASSIF_CODE /* r12 EBtax uptake for CC */
1223 );
1224
1225 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1226 THEN
1227
1228 l_EXCEPTION := NULL;
1229 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
1230 l_EXCEPTION := FND_MESSAGE.GET;
1231
1232 p_message := l_exception;
1233 p_err_header_id := l_cc_headers_rec.cc_header_id;
1234 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1235 p_err_det_pf_line_id := NULL;
1236
1237 RETURN 'F';
1238 END IF;
1239
1240
1241 l_new_cc_acct_func_amt :=
1242 l_cc_acct_lines_rec.cc_acct_func_amt +
1243 l_new_cc_acct_func_amt;
1244
1245 l_cc_acct_func_amt := l_new_cc_acct_func_amt;
1246
1247 IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
1248 AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
1249 ) OR
1250 ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
1251 AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
1252 )
1253 THEN
1254 l_cc_acct_encmbrnc_date := p_revalue_fix_date;
1255 END IF;
1256
1257 SELECT rowid
1258 INTO l_acct_row_id
1259 FROM igc_cc_acct_lines
1260 WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
1261
1262 IGC_CC_ACCT_LINES_PKG.Update_Row(
1263 l_api_version ,
1264 l_init_msg_list,
1265 l_commit,
1266 l_validation_level,
1267 l_return_status,
1268 l_msg_count,
1269 l_msg_data,
1270 l_acct_row_id,
1271 l_cc_acct_lines_rec.CC_Acct_Line_Id,
1272 l_cc_acct_lines_rec.CC_Header_Id,
1273 l_cc_acct_lines_rec.Parent_Header_Id,
1274 l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
1275 l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
1276 l_cc_acct_lines_rec.CC_Acct_Line_Num,
1277 l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
1278 l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
1279 l_cc_acct_func_amt,
1280 l_cc_acct_lines_rec.CC_Acct_Desc ,
1281 l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
1282 l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
1283 l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
1284 l_cc_acct_lines_rec.Tax_Id,
1285 l_cc_acct_lines_rec.cc_acct_encmbrnc_amt,
1286 l_cc_acct_encmbrnc_date,
1287 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
1288 l_cc_acct_lines_rec.Project_Id,
1289 l_cc_acct_lines_rec.Task_Id,
1290 l_cc_acct_lines_rec.Expenditure_Type,
1291 l_cc_acct_lines_rec.Expenditure_Org_Id,
1292 l_cc_acct_lines_rec.Expenditure_Item_Date,
1293 sysdate,
1294 l_Last_Updated_By,
1295 l_Last_Update_Login ,
1296 l_cc_acct_lines_rec.Creation_Date ,
1297 l_cc_acct_lines_rec.Created_By ,
1298 l_cc_acct_lines_rec.Attribute1,
1299 l_cc_acct_lines_rec.Attribute2,
1300 l_cc_acct_lines_rec.Attribute3,
1301 l_cc_acct_lines_rec.Attribute4,
1302 l_cc_acct_lines_rec.Attribute5,
1303 l_cc_acct_lines_rec.Attribute6,
1304 l_cc_acct_lines_rec.Attribute7,
1305 l_cc_acct_lines_rec.Attribute8,
1306 l_cc_acct_lines_rec.Attribute9,
1307 l_cc_acct_lines_rec.Attribute10 ,
1308 l_cc_acct_lines_rec.Attribute11,
1309 l_cc_acct_lines_rec.Attribute12,
1310 l_cc_acct_lines_rec.Attribute13,
1311 l_cc_acct_lines_rec.Attribute14,
1312 l_cc_acct_lines_rec.Attribute15,
1313 l_cc_acct_lines_rec.Context,
1314 l_cc_acct_lines_rec.CC_Func_Withheld_Amt,
1315 l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
1316 G_FLAG,
1317 l_cc_acct_lines_rec.TAX_CLASSIF_CODE /* Bug 6472296 for r12 EBtax uptake for CC */
1318 );
1319
1320
1321 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1322 THEN
1323
1324 l_EXCEPTION := NULL;
1325 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
1326 l_EXCEPTION := FND_MESSAGE.GET;
1327
1328 p_message := l_exception;
1329 p_err_header_id := l_cc_headers_rec.cc_header_id;
1330 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1331 p_err_det_pf_line_id := NULL;
1332
1333 RETURN 'F';
1334 END IF;
1335 END IF;
1336
1337 END LOOP;
1338
1339 CLOSE c_acct_lines;
1340
1341 END IF;
1342
1343 IF (p_validate_only = 'N')
1344 THEN
1345
1346 IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
1347 AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
1348 ) OR
1349 ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
1350 AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
1351 )
1352 THEN
1353 IF (l_cc_headers_rec.cc_acct_date IS NOT NULL)
1354 THEN
1355 IF (l_cc_headers_rec.cc_acct_date <= p_revalue_fix_date)
1356 THEN
1357 l_cc_acct_date := p_revalue_fix_date;
1358 ELSIF (l_cc_headers_rec.cc_acct_date > p_revalue_fix_date)
1359 THEN
1360 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1361 END IF;
1362 END IF;
1363
1364 IF (l_cc_headers_rec.cc_acct_date IS NULL)
1365 THEN
1366 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1367 END IF;
1368 ELSE
1369
1370 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1371 END IF;
1372
1373 l_cc_version_num := l_cc_headers_rec.cc_version_num + 1;
1374 l_cc_apprvl_status := l_approval_status;
1375
1376 SELECT rowid
1377 INTO l_hdr_row_id
1378 FROM igc_cc_headers
1379 WHERE CC_HEADER_ID = l_cc_headers_rec.cc_header_id;
1380
1381 IGC_CC_HEADERS_PKG.Update_Row(
1382 l_api_version,
1383 l_init_msg_list,
1384 l_commit,
1385 l_validation_level,
1386 l_return_status,
1387 l_msg_count,
1388 l_msg_data,
1389 l_hdr_row_id,
1390 l_cc_headers_rec.CC_HEADER_ID,
1391 l_cc_headers_rec.ORG_ID,
1392 l_cc_headers_rec.CC_TYPE,
1393 l_cc_headers_rec.CC_NUM,
1394 l_cc_version_num,
1395 l_cc_headers_rec.PARENT_HEADER_ID,
1396 l_cc_headers_rec.CC_STATE,
1397 l_cc_headers_rec.CC_CTRL_STATUS,
1398 l_cc_headers_rec.CC_ENCMBRNC_STATUS,
1399 l_cc_apprvl_status,
1400 l_cc_headers_rec.VENDOR_ID,
1401 l_cc_headers_rec.VENDOR_SITE_ID,
1402 l_cc_headers_rec.VENDOR_CONTACT_ID,
1403 l_cc_headers_rec.TERM_ID,
1404 l_cc_headers_rec.LOCATION_ID,
1405 l_cc_headers_rec.SET_OF_BOOKS_ID,
1406 l_cc_acct_date,
1407 l_cc_headers_rec.CC_DESC,
1408 l_cc_headers_rec.CC_START_DATE,
1409 l_cc_headers_rec.CC_END_DATE,
1410 l_cc_headers_rec.CC_OWNER_USER_ID,
1411 l_cc_headers_rec.CC_PREPARER_USER_ID,
1412 l_cc_headers_rec.CURRENCY_CODE,
1413 l_cc_headers_rec.CONVERSION_TYPE,
1414 l_cc_headers_rec.conversion_date,
1415 l_cc_headers_rec.conversion_rate,
1416 sysdate,
1417 l_LAST_UPDATED_BY,
1418 l_LAST_UPDATE_LOGIN,
1419 l_cc_headers_rec.CREATED_BY,
1420 l_cc_headers_rec.CREATION_DATE,
1421 l_cc_headers_rec.CC_CURRENT_USER_ID,
1422 l_cc_headers_rec.WF_ITEM_TYPE,
1423 l_cc_headers_rec.WF_ITEM_KEY,
1424 l_cc_headers_rec.ATTRIBUTE1,
1425 l_cc_headers_rec.ATTRIBUTE2,
1426 l_cc_headers_rec.ATTRIBUTE3,
1427 l_cc_headers_rec.ATTRIBUTE4,
1428 l_cc_headers_rec.ATTRIBUTE5,
1429 l_cc_headers_rec.ATTRIBUTE6,
1430 l_cc_headers_rec.ATTRIBUTE7,
1431 l_cc_headers_rec.ATTRIBUTE8,
1432 l_cc_headers_rec.ATTRIBUTE9,
1433 l_cc_headers_rec.ATTRIBUTE10,
1434 l_cc_headers_rec.ATTRIBUTE11,
1435 l_cc_headers_rec.ATTRIBUTE12,
1436 l_cc_headers_rec.ATTRIBUTE13,
1437 l_cc_headers_rec.ATTRIBUTE14,
1438 l_cc_headers_rec.ATTRIBUTE15,
1439 l_cc_headers_rec.CONTEXT,
1440 l_cc_headers_rec.CC_Guarantee_Flag,
1441 G_FLAG);
1442
1443 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1444 THEN
1445
1446 l_EXCEPTION := NULL;
1447 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADERS_UPDATE');
1448 l_EXCEPTION := FND_MESSAGE.GET;
1449
1450
1451 p_message := l_exception;
1452 p_err_header_id := l_cc_headers_rec.cc_header_id;
1453 p_err_acct_line_id := NULL;
1454 p_err_det_pf_line_id := NULL;
1455
1456 RETURN 'F';
1457 END IF;
1458
1459
1460 ELSIF (p_validate_only = 'Y')
1461 THEN
1462 UPDATE igc_cc_headers
1463 SET cc_apprvl_status = l_approval_status
1464 WHERE cc_header_id = p_cc_header_id;
1465 END IF;
1466
1467
1468
1469 /* Update Corresponding PO */
1470
1471 IF ( (l_cc_headers_rec.cc_type = 'S') OR
1472 (l_cc_headers_rec.cc_type = 'R') ) AND
1473 /* Changed l_cc_headers_rec.cc_apprvl_status to l_approval_status to fix bug 1632984 */
1474 ( ( (l_cc_headers_rec.cc_state = 'CM') AND (l_approval_status = 'AP') ) OR
1475 (l_cc_headers_rec.cc_state = 'CT') )
1476 THEN
1477 l_return_status := FND_API.G_RET_STS_SUCCESS;
1478
1479 IGC_CC_PO_INTERFACE_PKG.Convert_CC_TO_PO(1.0,
1480 FND_API.G_FALSE,
1481 FND_API.G_TRUE,
1482 FND_API.G_VALID_LEVEL_NONE,
1483 l_return_status,
1484 l_msg_count,
1485 l_msg_data,
1486 l_cc_headers_rec.cc_header_id);
1487
1488 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1489 THEN
1490 RETURN 'F';
1491 END IF;
1492 END IF;
1493
1494 /* added following code to remove hard coded message */
1495 /* begin */
1496 l_action_hist_msg := NULL;
1497 /* end */
1498
1499 IGC_CC_ACTIONS_PKG.Insert_Row(
1500 l_api_version,
1501 l_init_msg_list,
1502 l_commit,
1503 l_validation_level,
1504 l_return_status,
1505 l_msg_count,
1506 l_msg_data,
1507 l_action_row_id,
1508 l_cc_headers_rec.CC_HEADER_ID,
1509 l_cc_version_num,
1510 'RF',
1511 l_cc_headers_rec.CC_STATE,
1512 l_cc_headers_rec.CC_CTRL_STATUS,
1513 l_cc_apprvl_status,
1514 l_action_hist_msg,
1515 Sysdate,
1516 l_Last_Updated_By,
1517 l_Last_Update_Login,
1518 Sysdate,
1519 l_Created_By);
1520
1521 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1522 THEN
1523
1524 l_EXCEPTION := NULL;
1525 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACTION_HST_INSERT');
1526 l_EXCEPTION := FND_MESSAGE.GET;
1527
1528
1529 p_message := l_exception;
1530 p_err_header_id := l_cc_headers_rec.cc_header_id;
1531 p_err_acct_line_id := NULL;
1532 p_err_det_pf_line_id := NULL;
1533
1534 RETURN 'F';
1535 END IF;
1536
1537 /* Update validation status, in temporary table*/
1538 UPDATE igc_cc_process_data
1539 SET
1540 processed = 'Y'
1541 WHERE request_id = p_request_id AND
1542 cc_header_id = P_cc_header_id ;
1543
1544 RETURN 'P';
1545
1546 EXCEPTION
1547 WHEN OTHERS
1548 THEN
1549 RETURN 'F';
1550
1551 END reval_fix_update;
1552
1553
1554 PROCEDURE populate_errors(p_cc_header_id NUMBER,
1555 p_process_phase VARCHAR2,
1556 p_sob_id NUMBER,
1557 p_org_id NUMBER,
1558 p_request_id NUMBER)
1559 IS
1560 l_message igc_cc_process_exceptions.exception_reason%TYPE;
1561 BEGIN
1562 /* Update validation_status to 'F' in temporary table for releases */
1563
1564 UPDATE igc_cc_process_data a
1565 SET a.validation_status = 'F'
1566 WHERE a.cc_header_id = p_cc_header_id AND
1567 request_id = p_request_id;
1568
1569 l_message := NULL;
1570 FND_MESSAGE.SET_NAME('IGC','IGC_CC_AVAILABLE_AMT_EXCEEDED');
1571 l_message := FND_MESSAGE.GET;
1572
1573 INSERT
1574 INTO igc_cc_process_exceptions
1575 (process_type,
1576 process_phase,
1577 cc_header_id,
1578 cc_acct_line_id,
1579 cc_det_pf_line_id,
1580 exception_reason,
1581 org_id,
1582 set_of_books_id,
1583 request_id)
1584 SELECT 'F',
1585 p_process_phase,
1586 b.cc_header_id,
1587 NULL,
1588 NULL,
1589 l_message,
1590 p_org_id,
1591 p_sob_id,
1592 p_request_id
1593 FROM igc_cc_headers b,
1594 igc_cc_process_data a
1595 WHERE
1596 b.parent_header_id = p_cc_header_id AND
1597 b.cc_header_id = a.cc_header_id AND
1598 a.request_id = p_request_id;
1599 END populate_errors;
1600
1601 PROCEDURE revalue_fix_main( ERRBUF OUT NOCOPY VARCHAR2,
1602 RETCODE OUT NOCOPY VARCHAR2,
1603 /*Bug No : 6341012. MOAC Uptake. SOB_ID,ORG_ID are retrieved from Packages rather than from Profile values*/
1604 -- p_sob_id IN NUMBER,
1605 -- p_org_id IN NUMBER,
1606 p_cc_header_id IN NUMBER,
1607 p_revalue_fix_date IN VARCHAR2)
1608 IS
1609 l_cc_headers_rec igc_cc_headers%ROWTYPE;
1610 l_rel_cc_headers_rec igc_cc_headers%ROWTYPE;
1611 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
1612 l_rel_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
1613 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
1614 l_rel_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
1615
1616 /*Bug No : 6341012. MOAC Uptake. Local variables for SOB_ID,ORG_ID,SOB_NAME */
1617 l_sob_id NUMBER;
1618 l_org_id NUMBER;
1619 l_sob_name VARCHAR2(30);
1620
1621 l_revalue_fix_date DATE;
1622
1623 l_budg_status BOOLEAN;
1624 l_validation_status VARCHAR2(1);
1625 l_curr_validation_status VARCHAR2(1);
1626 l_reservation_status VARCHAR2(1);
1627 l_processed VARCHAR2(1);
1628 l_validate_only VARCHAR2(1);
1629 l_process_flag VARCHAR2(1);
1630 l_message igc_cc_process_exceptions.exception_reason%TYPE;
1631
1632 l_cc_count NUMBER := 0;
1633 l_invalid_cc_count NUMBER := 0;
1634 l_po_count NUMBER := 0;
1635
1636 l_cc_cover_count NUMBER := 0;
1637
1638 l_request_id2 NUMBER := 0;
1639 l_request_id1 NUMBER := 79000;
1640
1641 l_lock_cc_po BOOLEAN := FALSE;
1642 l_cover_not_found BOOLEAN := FALSE;
1643 l_cc_not_found BOOLEAN := FALSE;
1644
1645 l_currency_code gl_sets_of_books.currency_code%TYPE;
1646 l_cover_currency_code gl_sets_of_books.currency_code%TYPE;
1647 l_sbc_on BOOLEAN;
1648 l_cbc_on BOOLEAN;
1649 l_prov_enc_on BOOLEAN;
1650 l_conf_enc_on BOOLEAN;
1651 /*Bug No : 6341012. SLA Uptake. Encumbrance Type IDs are not required */
1652 -- l_req_encumbrance_type_id NUMBER;
1653 -- l_purch_encumbrance_type_id NUMBER;
1654 -- l_cc_prov_enc_type_id NUMBER;
1655 -- l_cc_conf_enc_type_id NUMBER;
1656
1657 l_non_reval_acct_amt_total NUMBER := 0;
1658 l_reval_acct_amt_total NUMBER := 0;
1659 l_non_reval_pf_amt_total NUMBER := 0;
1660 l_reval_pf_amt_total NUMBER := 0;
1661 l_cover_acct_func_amt NUMBER := 0;
1662 l_cover_pf_func_amt NUMBER := 0;
1663 l_msg_count NUMBER := 0;
1664 l_msg_data VARCHAR2(12000);
1665 l_error_text VARCHAR2(12000);
1666 l_usr_msg igc_cc_process_exceptions.exception_reason%TYPE;
1667
1668 l_err_header_id NUMBER;
1669 l_err_acct_line_id NUMBER;
1670 l_err_det_pf_line_id NUMBER;
1671
1672 p_process_phase VARCHAR2(1) := 'F';
1673
1674 -- 01/03/02, CC enabled in IGI
1675 l_option_name VARCHAR2(80);
1676 lv_message VARCHAR2(1000);
1677 ----Variables related to XML report
1678 l_terr VARCHAR2(10):='US';
1679 l_lang VARCHAR2(10):='en';
1680 l_layout BOOLEAN;
1681
1682 /* Cursor for fetching all contract commitments eligible for re-valuation */
1683
1684 CURSOR c_revalue_process_cc(p_cc_header_id NUMBER)
1685 IS
1686
1687 SELECT *
1688 FROM igc_cc_headers a
1689 WHERE a.cc_header_id = p_cc_header_id;
1690
1691 /* Fetch the cover-relase both revalued data from temporary table */
1692 CURSOR c_cover_reval_data(p_request_id NUMBER)
1693 IS
1694 SELECT a.cc_header_id
1695 FROM igc_cc_process_data a ,
1696 igc_cc_headers b
1697 WHERE a.request_id = p_request_id AND
1698 a.cc_header_id = b.cc_header_id AND
1699 b.cc_type = 'C';
1700
1701
1702 /* Fetch the cover-standard data from temporary table */
1703 CURSOR c_reval_data(p_request_id NUMBER)
1704 IS
1705 SELECT a.cc_header_id
1706 FROM igc_cc_process_data a ,
1707 igc_cc_headers b
1708 WHERE a.request_id = p_request_id AND
1709 a.cc_header_id = b.cc_header_id AND
1710 (b.cc_type = 'C' OR b.cc_type = 'S');
1711
1712 CURSOR C_ALL_RELEASES1(p_cc_header_id NUMBER)
1713 IS
1714 SELECT *
1715 FROM igc_cc_headers
1716 WHERE parent_header_id = p_cc_header_id;
1717
1718 CURSOR C_ALL_RELEASES(p_cc_header_id NUMBER)
1719 IS
1720 SELECT a.cc_header_id
1721 FROM igc_cc_headers a
1722 WHERE a.parent_header_id = p_cc_header_id;
1723
1724 CURSOR C_RELEASES(p_cc_header_id NUMBER)
1725 IS
1726 SELECT a.cc_header_id
1727 FROM igc_cc_headers a
1728 WHERE a.parent_header_id = p_cc_header_id;
1729
1730 CURSOR C_ACCT_LINES(p_cc_header_id NUMBER)
1731 IS
1732 -- Replaced the folllowing query with the one below for
1733 -- performance tuning fixes.
1734 -- The record definition of l_cc_acct_lines_rec is still based on
1735 -- view igc_cc_acct_lines_v. Instead of selecting from the view,
1736 -- select is being done from the base table, but all the columns
1737 -- as defined in the view are retained even though they are not used.
1738 -- This is just so that minimal change is made to the code.
1739 /*
1740 SELECT *
1741 FROM igc_cc_acct_lines_v
1742 WHERE cc_header_id = p_cc_header_id;
1743 */
1744
1745 SELECT ccal.ROWID,
1746 ccal.cc_header_id,
1747 NULL org_id,
1748 NULL cc_type,
1749 NULL cc_type_code,
1750 NULL cc_num,
1751 ccal.cc_acct_line_id,
1752 ccal.cc_acct_line_num,
1753 ccal.cc_acct_desc,
1754 ccal.parent_header_id,
1755 ccal.parent_acct_line_id,
1756 NULL parent_cc_acct_line_num,
1757 NULL cc_budget_acct_desc,
1758 ccal.cc_budget_code_combination_id,
1759 NULL cc_charge_acct_desc,
1760 ccal.cc_charge_code_combination_id,
1761 ccal.cc_acct_entered_amt,
1762 ccal.cc_acct_func_amt,
1763 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
1764 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
1765 ccal.cc_acct_encmbrnc_amt,
1766 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
1767 ccal.cc_acct_unbilled_amt,
1768 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
1769 NULL project_number ,
1770 ccal.project_id,
1771 NULL task_number,
1772 ccal.task_id,
1773 ccal.expenditure_type,
1774 NULL expenditure_org_name,
1775 ccal.expenditure_org_id,
1776 ccal.expenditure_item_date,
1777 ccal.cc_acct_taxable_flag,
1778 NULL tax_name,
1779 NULL tax_id, -- Bug 6472296 for r12 EBtax uptake for CC
1780 ccal.cc_acct_encmbrnc_status,
1781 ccal.cc_acct_encmbrnc_date,
1782 ccal.context,
1783 ccal.attribute1,
1784 ccal.attribute2,
1785 ccal.attribute3,
1786 ccal.attribute4,
1787 ccal.attribute5,
1788 ccal.attribute6,
1789 ccal.attribute7,
1790 ccal.attribute8,
1791 ccal.attribute9,
1792 ccal.attribute10,
1793 ccal.attribute11,
1794 ccal.attribute12,
1795 ccal.attribute13,
1796 ccal.attribute14,
1797 ccal.attribute15,
1798 ccal.created_by,
1799 ccal.creation_date,
1800 ccal.last_updated_by,
1801 ccal.last_update_date,
1802 ccal.last_update_login,
1803 ccal.cc_func_withheld_amt,
1804 ccal.cc_ent_withheld_amt,
1805 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
1806 NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
1807 ccal.TAX_CLASSIF_CODE /* Bug No : 6341012. E-BTax uptake.New field is ebing added to Account_Lines Table R12 */
1808 FROM igc_cc_acct_lines ccal
1809 WHERE cc_header_id = p_cc_header_id;
1810
1811 CURSOR C_PF_LINES(p_cc_acct_line_id NUMBER)
1812 IS
1813 -- SELECT *
1814 -- FROM igc_cc_det_pf_v
1815 -- WHERE cc_acct_line_id = p_cc_acct_line_id;
1816
1817 SELECT ccdpf.ROWID,
1818 ccdpf.cc_det_pf_line_id,
1819 ccdpf.cc_det_pf_line_num,
1820 NULL cc_acct_line_num,
1821 ccdpf.cc_acct_line_id,
1822 NULL parent_det_pf_line_num,
1823 ccdpf.parent_det_pf_line_id,
1824 ccdpf.parent_acct_line_id,
1825 ccdpf.cc_det_pf_entered_amt,
1826 ccdpf.cc_det_pf_func_amt,
1827 ccdpf.cc_det_pf_date,
1828 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
1829 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
1830 ccdpf.cc_det_pf_unbilled_amt,
1831 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
1832 ccdpf.cc_det_pf_encmbrnc_amt,
1833 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT (p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
1834 ccdpf.cc_det_pf_encmbrnc_date,
1835 ccdpf.cc_det_pf_encmbrnc_status,
1836 ccdpf.context,
1837 ccdpf.attribute1,
1838 ccdpf.attribute2,
1839 ccdpf.attribute3,
1840 ccdpf.attribute4,
1841 ccdpf.attribute5,
1842 ccdpf.attribute6,
1843 ccdpf.attribute7,
1844 ccdpf.attribute8,
1845 ccdpf.attribute9,
1846 ccdpf.attribute10,
1847 ccdpf.attribute11,
1848 ccdpf.attribute12,
1849 ccdpf.attribute13,
1850 ccdpf.attribute14,
1851 ccdpf.attribute15,
1852 ccdpf.last_update_date,
1853 ccdpf.last_updated_by,
1854 ccdpf.last_update_login,
1855 ccdpf.creation_date,
1856 ccdpf.created_by
1857 FROM igc_cc_det_pf ccdpf
1858 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id;
1859
1860 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
1861 l_rel_cc_header_id igc_cc_headers.cc_header_id%TYPE;
1862 l_cc_acct_line_id igc_cc_acct_lines.cc_acct_line_id%TYPE;
1863 l_debug VARCHAR2(1);
1864
1865 insert_data EXCEPTION;
1866 BEGIN
1867
1868
1869 -- 01/03/02, check to see if CC is installed
1870 IF NOT igi_gen.is_req_installed('CC') THEN
1871
1872 SELECT meaning
1873 INTO l_option_name
1874 FROM igi_lookups
1875 WHERE lookup_code = 'CC'
1876 AND lookup_type = 'GCC_DESCRIPTION';
1877
1878 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
1879 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
1880 lv_message := fnd_message.get;
1881 errbuf := lv_message;
1882 retcode := 2;
1883 return;
1884 END IF;
1885
1886 /*Bug No : 6341012. MOAC Uptake. ORG_ID,SOB_ID are retrieved from packages */
1887 l_org_id := MO_GLOBAL.get_current_org_id;
1888 MO_UTILS.get_ledger_info(l_org_id,l_sob_id,l_sob_name);
1889
1890 RETCODE := '0';
1891
1892 l_request_id1 := fnd_global.conc_request_id;
1893 p_process_phase := 'F';
1894
1895 -- Bug 1914745, clear any old records from the igc_cc_interface table
1896 -- DELETE FROM igc_cc_interface
1897 -- WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date((sysdate - interval '2' day), 'DD/MM/YYYY');
1898
1899 -- Bug 2872060. Above Delete commented out due to compilation probs in Oracle8i
1900 DELETE FROM igc_cc_interface
1901 WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date(sysdate ,'DD/MM/YYYY') - 2;
1902
1903 /* Begin bug fix 1591845 */
1904 /* Added TRUNC to statement below to remove time portion of p_revalue_fix_date to fix bug 1632975.*/
1905
1906 l_revalue_fix_date := TRUNC(to_date (p_revalue_fix_date, 'YYYY/MM/DD HH24:MI:SS'));
1907
1908 /* End bug fix 1591845 */
1909 SAVEPOINT REVALUE1;
1910
1911 l_currency_code := NULL;
1912 l_sbc_on := NULL;
1913 l_cbc_on := NULL;
1914 l_prov_enc_on := NULL;
1915 l_conf_enc_on := NULL;
1916 /*Bug No : 6341012. SLA Uptake. Encumbrance Type IDs are not required */
1917 -- l_req_encumbrance_type_id := NULL;
1918 -- l_purch_encumbrance_type_id := NULL;
1919 -- l_cc_prov_enc_type_id := NULL;
1920 -- l_cc_conf_enc_type_id := NULL;
1921
1922 --
1923 -- Setup debug information based upon profile setup options.
1924 --
1925 /*
1926 l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
1927 IF (l_debug = 'Y') THEN
1928 l_debug := FND_API.G_TRUE;
1929 ELSE
1930 l_debug := FND_API.G_FALSE;
1931 END IF;
1932 IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
1933 */
1934
1935 /* Get Budgetary Control information */
1936
1937 /* Begin fix for bug 1576023 */
1938 l_msg_data := NULL;
1939 l_msg_count := 0;
1940 l_usr_msg := NULL;
1941
1942 l_budg_status := IGC_CC_REP_YEP_PVT.get_budg_ctrl_params(
1943 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1944 l_sob_id,
1945 l_org_id,
1946 l_currency_code,
1947 l_sbc_on,
1948 l_cbc_on,
1949 l_prov_enc_on,
1950 l_conf_enc_on,
1951 /* Bug No : 6341012. R12 Uptake. Encumbrance Type IDs are not required */
1952 -- l_req_encumbrance_type_id,
1953 -- l_purch_encumbrance_type_id,
1954 -- l_cc_prov_enc_type_id,
1955 -- l_cc_conf_enc_type_id,
1956 l_msg_data,
1957 l_msg_count,
1958 l_usr_msg
1959 ) ;
1960
1961
1962 IF (l_budg_status = FALSE) AND (l_usr_msg IS NOT NULL)
1963 THEN
1964 INSERT INTO
1965 igc_cc_process_exceptions
1966 (process_type,
1967 process_phase,
1968 cc_header_id,
1969 cc_acct_line_id,
1970 cc_det_pf_line_id,
1971 exception_reason,
1972 org_id,
1973 set_of_books_id,
1974 request_id)
1975 VALUES
1976 ( 'F',
1977 p_process_phase,
1978 NULL,
1979 NULL,
1980 NULL,
1981 l_usr_msg,
1982 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1983 l_org_id,
1984 l_sob_id,
1985 l_request_id1);
1986
1987 COMMIT;
1988
1989 END IF;
1990
1991 IF ( l_budg_status = FALSE AND l_usr_msg IS NOT NULL)
1992 THEN
1993 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
1994 Fnd_request.set_org_id(l_org_id);
1995 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
1996 'IGC',
1997 'IGCCRVFR',
1998 NULL,
1999 NULL,
2000 FALSE,
2001 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2002 l_sob_id,
2003 l_org_id,
2004 p_process_phase,
2005 'F',
2006 l_request_id1);
2007 -----------------------
2008 -- Start of XML Report
2009 -----------------------
2010 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2011 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2012 l_lang,
2013 l_terr,
2014 'IGCCRVFR_XML',
2015 'IGC',
2016 'IGCCRVFR_XML' );
2017 l_layout := FND_REQUEST.ADD_LAYOUT(
2018 'IGC',
2019 'IGCCRVFR_XML',
2020 l_lang,
2021 l_terr,
2022 'RTF');
2023 IF l_layout then
2024 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2025 'IGC',
2026 'IGCCRVFR_XML',
2027 NULL,
2028 NULL,
2029 FALSE,
2030 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2031 l_sob_id,
2032 l_org_id,
2033 p_process_phase,
2034 'F',
2035 l_request_id1);
2036 END IF;
2037 END IF;
2038
2039 --------------------
2040 -- End of XML Report
2041 --------------------
2042
2043 END IF;
2044
2045 -- ------------------------------------------------------------------------------------
2046 -- Ensure that any exceptions raised are output into the log file to be reported to
2047 -- the user if any are present.
2048 -- ------------------------------------------------------------------------------------
2049 IF ( l_budg_status = FALSE )
2050 THEN
2051 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2052 p_data => l_msg_data );
2053
2054 IF (l_msg_count > 0)
2055 THEN
2056 l_error_text := '';
2057
2058 FOR l_cur IN 1..l_msg_count
2059 LOOP
2060 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2061 -- fnd_file.put_line (FND_FILE.LOG,
2062 -- l_error_text);
2063 -- bug 3199488 start block
2064 IF (l_state_level >= l_debug_level) THEN
2065 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp1',
2066 l_error_text);
2067 END IF;
2068 -- bug 3199488, end block
2069
2070 END LOOP;
2071 END IF;
2072 END IF;
2073
2074 IF (l_usr_msg IS NULL AND l_budg_status = FALSE)
2075 THEN
2076 RETCODE := 2;
2077 END IF;
2078
2079 IF ( l_budg_status = FALSE )
2080 THEN
2081 RETURN;
2082 END IF;
2083
2084 /* End fix for bug 1576023 */
2085
2086
2087 IF ( NOT validate_params(p_process_phase,
2088 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2089 l_sob_id,
2090 l_org_id,
2091 p_cc_header_id,
2092 l_revalue_fix_date,
2093 l_request_id1)
2094 )
2095 THEN
2096 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
2097 Fnd_request.set_org_id(l_org_id);
2098 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2099 'IGC',
2100 'IGCCRVFR',
2101 NULL,
2102 NULL,
2103 FALSE,
2104 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2105 l_sob_id,
2106 l_org_id,
2107 p_process_phase,
2108 'F',
2109 l_request_id1);
2110 -----------------------
2111 -- Start of XML Report
2112 -----------------------
2113 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2114 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2115 l_lang,
2116 l_terr,
2117 'IGCCRVFR_XML',
2118 'IGC',
2119 'IGCCRVFR_XML' );
2120 l_layout := FND_REQUEST.ADD_LAYOUT(
2121 'IGC',
2122 'IGCCRVFR_XML',
2123 l_lang,
2124 l_terr,
2125 'RTF');
2126 IF l_layout then
2127 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2128 'IGC',
2129 'IGCCRVFR_XML',
2130 NULL,
2131 NULL,
2132 FALSE,
2133 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2134 l_sob_id,
2135 l_org_id,
2136 p_process_phase,
2137 'F',
2138 l_request_id1);
2139 END IF;
2140 END IF;
2141 --------------------
2142 -- End of XML Report
2143 --------------------
2144 -- ------------------------------------------------------------------------------------
2145 -- Ensure that any exceptions raised are output into the log file to be reported to
2146 -- the user if any are present.
2147 -- ------------------------------------------------------------------------------------
2148 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2149 p_data => l_msg_data );
2150
2151 IF (l_msg_count > 0) THEN
2152 l_error_text := '';
2153 FOR l_cur IN 1..l_msg_count LOOP
2154 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2155 -- fnd_file.put_line (FND_FILE.LOG,
2156 -- l_error_text);
2157 -- bug 3199488 start block
2158 IF (l_state_level >= l_debug_level) THEN
2159 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp2',
2160 l_error_text);
2161 END IF;
2162 -- bug 3199488, end block
2163 END LOOP;
2164 END IF;
2165
2166 RETURN;
2167 END IF;
2168
2169 SAVEPOINT REVALUE3;
2170
2171 /* Populate temporary table */
2172
2173 OPEN c_revalue_process_cc(p_cc_header_id);
2174 LOOP
2175 FETCH c_revalue_process_cc
2176 INTO l_cc_headers_rec;
2177
2178 EXIT WHEN c_revalue_process_cc%NOTFOUND;
2179
2180 /* Begin Standard Revaluation */
2181 IF (l_cc_headers_rec.cc_type = 'S')
2182 THEN
2183 INSERT INTO igc_cc_process_data
2184 (
2185 process_type,
2186 process_phase,
2187 cc_header_id,
2188 validation_status,
2189 reservation_status,
2190 processed,
2191 old_approval_status,
2192 org_id,
2193 set_of_books_id,
2194 validate_only,
2195 request_id)
2196 VALUES
2197 ( 'F',
2198 p_process_phase,
2199 l_cc_headers_rec.cc_header_id,
2200 'I',
2201 'F',
2202 'N',
2203 NULL,
2204 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2205 l_org_id,
2206 l_sob_id,
2207 'Y',
2208 l_request_id1);
2209
2210 COMMIT;
2211
2212 END IF;
2213 /* End Standard Revaluation */
2214
2215 /* Begin release Revaluation */
2216
2217 IF (l_cc_headers_rec.cc_type = 'R')
2218 THEN
2219 SELECT currency_code
2220 INTO l_cover_currency_code
2221 FROM igc_cc_headers
2222 WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
2223
2224 /* Functional Currency Cover */
2225 IF (l_currency_code = l_cover_currency_code)
2226 THEN
2227 INSERT INTO igc_cc_process_data
2228 (
2229 process_type,
2230 process_phase,
2231 cc_header_id,
2232 validation_status,
2233 reservation_status,
2234 processed,
2235 old_approval_status,
2236 org_id,
2237 set_of_books_id,
2238 validate_only,
2239 request_id)
2240 VALUES
2241 ( 'F',
2242 p_process_phase,
2243 l_cc_headers_rec.parent_header_id,
2244 'I',
2245 'F',
2246 'N',
2247 NULL,
2248 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2249 l_org_id,
2250 l_sob_id,
2251 'Y',
2252 l_request_id1);
2253 COMMIT;
2254 END IF;
2255
2256
2257 /* Non Functional Currency Cover */
2258 IF (l_currency_code <> l_cover_currency_code)
2259 THEN
2260 INSERT INTO igc_cc_process_data
2261 (
2262 process_type,
2263 process_phase,
2264 cc_header_id,
2265 validation_status,
2266 reservation_status,
2267 processed,
2268 old_approval_status,
2269 org_id,
2270 set_of_books_id,
2271 validate_only,
2272 request_id)
2273 VALUES
2274 ( 'F',
2275 p_process_phase,
2276 l_cc_headers_rec.parent_header_id,
2277 'I',
2278 'F',
2279 'N',
2280 NULL,
2281 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2282 l_org_id,
2283 l_sob_id,
2284 'N',
2285 l_request_id1);
2286 COMMIT;
2287 END IF;
2288
2289
2290 OPEN c_all_releases(l_cc_headers_rec.parent_header_id);
2291 LOOP
2292 FETCH c_all_releases INTO l_cc_header_id;
2293 EXIT WHEN c_all_releases%NOTFOUND;
2294
2295 IF (l_cc_header_id = l_cc_headers_rec.cc_header_id)
2296 THEN
2297
2298 INSERT INTO igc_cc_process_data
2299 (
2300 process_type,
2301 process_phase,
2302 cc_header_id,
2303 validation_status,
2304 reservation_status,
2305 processed,
2306 old_approval_status,
2307 org_id,
2308 set_of_books_id,
2309 validate_only,
2310 request_id)
2311 VALUES
2312 ( 'F',
2313 p_process_phase,
2314 l_cc_header_id,
2315 'I',
2316 'F',
2317 'N',
2318 NULL,
2319 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2320 l_org_id,
2321 l_sob_id,
2322 'N',
2323 l_request_id1);
2324 ELSE
2325 INSERT INTO igc_cc_process_data
2326 (
2327 process_type,
2328 process_phase,
2329 cc_header_id,
2330 validation_status,
2331 reservation_status,
2332 processed,
2333 old_approval_status,
2334 org_id,
2335 set_of_books_id,
2336 validate_only,
2337 request_id)
2338 VALUES
2339 ( 'F',
2340 p_process_phase,
2341 l_cc_header_id,
2342 'I',
2343 'F',
2344 'N',
2345 NULL,
2346 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2347 l_org_id,
2348 l_sob_id,
2349 'Y',
2350 l_request_id1);
2351 END IF;
2352
2353 END LOOP;
2354
2355 CLOSE c_all_releases;
2356
2357 COMMIT;
2358
2359 END IF;
2360 /* End release */
2361
2362 END LOOP;
2363
2364 CLOSE c_revalue_process_cc;
2365
2366 COMMIT;
2367
2368 /* Begin Lock CC and PO */
2369 /* Lock Contract Commitments and related PO's If Phase = 'Final' */
2370
2371 IF (p_process_phase = 'F')
2372 THEN
2373
2374 OPEN c_reval_data(l_request_id1);
2375 LOOP
2376 FETCH c_reval_data INTO l_cc_header_id;
2377 EXIT WHEN c_reval_data%NOTFOUND;
2378
2379 /* Get Contract Details */
2380 SELECT *
2381 INTO l_cc_headers_rec
2382 FROM igc_cc_headers
2383 WHERE cc_header_id = l_cc_header_id;
2384
2385 l_lock_cc_po := TRUE;
2386
2387 /* Standard */
2388 IF (l_cc_headers_rec.cc_type = 'S')
2389 THEN
2390 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2391 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_cc_header_id, l_request_id1);
2392
2393 END IF;
2394
2395 IF (l_lock_cc_po = FALSE)
2396 THEN
2397 COMMIT;
2398 EXIT;
2399 END IF;
2400
2401 /* Cover Relase */
2402
2403 IF (l_cc_headers_rec.cc_type = 'C')
2404 THEN
2405 OPEN c_releases(l_cc_headers_rec.cc_header_id);
2406 LOOP
2407 FETCH c_releases INTO l_rel_cc_header_id;
2408 EXIT WHEN c_releases%NOTFOUND;
2409
2410 IF (l_lock_cc_po = TRUE)
2411 THEN
2412 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2413 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_rel_cc_header_id,l_request_id1);
2414 END IF;
2415
2416 IF (l_lock_cc_po = FALSE)
2417 THEN
2418 COMMIT;
2419 EXIT;
2420 END IF;
2421
2422 END LOOP;
2423 CLOSE c_releases;
2424
2425
2426 IF (l_lock_cc_po = TRUE)
2427 THEN
2428 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2429 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_cc_header_id,l_request_id1);
2430 END IF;
2431
2432 IF (l_lock_cc_po = FALSE)
2433 THEN
2434 COMMIT;
2435 EXIT;
2436 END IF;
2437
2438
2439 END IF;
2440
2441 END LOOP;
2442 CLOSE c_reval_data;
2443
2444 END IF;
2445
2446 /* End Lock CC and PO */
2447
2448 IF (p_process_phase = 'P') OR ( (p_process_phase = 'F' AND l_lock_cc_po = TRUE))
2449 THEN
2450
2451
2452 /* Functional Currency Cover */
2453 IF (l_currency_code = l_cover_currency_code)
2454 THEN
2455 /* Validate all functional currency covers for available amount */
2456 OPEN c_cover_reval_data(l_request_id1);
2457 LOOP
2458
2459 FETCH c_cover_reval_data INTO l_cc_header_id;
2460 EXIT WHEN c_cover_reval_data%NOTFOUND;
2461
2462 /* Get Contract Details */
2463 SELECT *
2464 INTO l_cc_headers_rec
2465 FROM igc_cc_headers
2466 WHERE cc_header_id = l_cc_header_id;
2467
2468 OPEN c_acct_lines(l_cc_header_id);
2469 LOOP
2470 FETCH c_acct_lines INTO l_cc_acct_lines_rec;
2471 EXIT WHEN c_acct_lines%NOTFOUND;
2472
2473 l_cover_acct_func_amt := 0;
2474 l_non_reval_acct_amt_total := 0;
2475 l_reval_acct_amt_total := 0;
2476
2477 BEGIN
2478 SELECT NVL(SUM(NVL(CC_ACCT_FUNC_AMT,0)),0)
2479 INTO l_non_reval_acct_amt_total
2480 FROM igc_cc_acct_lines a,
2481 igc_cc_headers b
2482 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
2483 a.cc_header_id = b.cc_header_id AND
2484 NVL(b.parent_header_id,0) = l_cc_header_id AND
2485 b.cc_header_id <> p_cc_header_id;
2486 EXCEPTION
2487 WHEN NO_DATA_FOUND
2488 THEN
2489 l_non_reval_acct_amt_total := 0;
2490 END;
2491
2492 BEGIN
2493
2494 SELECT SUM(
2495 NVL(a.cc_acct_func_amt,0) +
2496 ( ( ( a.cc_acct_entered_amt -
2497 --a.cc_acct_billed_amt)
2498 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( a.cc_acct_line_id))
2499 * b.conversion_rate
2500 )
2501 -
2502 (a.cc_acct_func_amt -
2503 -- a.cc_acct_func_billed_amt)
2504 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id))
2505 )
2506 )
2507 INTO l_reval_acct_amt_total
2508 FROM igc_cc_acct_lines a,
2509 igc_cc_headers b
2510 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
2511 a.cc_header_id = b.cc_header_id AND
2512 b.cc_header_id = p_cc_header_id;
2513 EXCEPTION
2514 WHEN NO_DATA_FOUND
2515 THEN
2516 l_reval_acct_amt_total := 0;
2517 END;
2518
2519 l_cover_acct_func_amt := l_cc_acct_lines_rec.cc_acct_func_amt;
2520
2521 IF (l_non_reval_acct_amt_total + l_reval_acct_amt_total)
2522 > (l_cover_acct_func_amt)
2523 THEN
2524 populate_errors(l_cc_headers_rec.cc_header_id,
2525 p_process_phase,
2526 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2527 l_sob_id,
2528 l_org_id,
2529 l_request_id1);
2530 EXIT;
2531 END IF;
2532
2533 OPEN c_pf_lines(l_cc_acct_lines_rec.cc_acct_line_id);
2534 LOOP
2535 FETCH c_pf_lines INTO l_cc_pmt_fcst_rec;
2536 EXIT WHEN c_pf_lines%NOTFOUND;
2537
2538 l_non_reval_pf_amt_total := 0;
2539 l_reval_pf_amt_total := 0;
2540 l_cover_pf_func_amt := 0;
2541
2542
2543 BEGIN
2544 SELECT SUM(NVL(CC_DET_PF_FUNC_AMT,0))
2545 INTO l_non_reval_pf_amt_total
2546 FROM igc_cc_det_pf a,
2547 igc_cc_acct_lines b,
2548 igc_cc_headers c
2549 WHERE NVL(a.parent_acct_line_id,0) =
2550 l_cc_acct_lines_rec.cc_acct_line_id AND
2551 NVL(a.parent_det_pf_line_id,0) =
2552 l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
2553 a.cc_acct_line_id = b.cc_acct_line_id AND
2554 b.cc_header_id = c.cc_header_id AND
2555 NVL(c.parent_header_id,0) = l_cc_header_id AND
2556 c.cc_header_id <> p_cc_header_id;
2557 EXCEPTION
2558 WHEN NO_DATA_FOUND
2559 THEN
2560 l_non_reval_pf_amt_total := 0;
2561 END;
2562
2563
2564 BEGIN
2565 -- Replaced view igc_cc_det_pf_v with
2566 -- igc_cc_det_pf
2567 -- (a.cc_det_pf_func_amt - a.cc_det_pf_func_billed_amt)
2568
2569 SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
2570 ( ( ( a.cc_det_pf_entered_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
2571 * c.conversion_rate
2572 )
2573 -
2574 (a.cc_det_pf_func_amt - IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id))
2575 )
2576 )
2577 INTO l_reval_pf_amt_total
2578 FROM igc_cc_det_pf a,
2579 igc_cc_acct_lines b,
2580 igc_cc_headers c
2581 WHERE NVL(a.parent_acct_line_id,0) =
2582 l_cc_acct_lines_rec.cc_acct_line_id AND
2583 NVL(a.parent_det_pf_line_id,0) =
2584 l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
2585 a.cc_acct_line_id = b.cc_acct_line_id AND
2586 b.cc_header_id = c.cc_header_id AND
2587 /* Bug No : 6341012. R12 Uptake. p_org_id is changed to l_org_id */
2588 c.org_id = l_org_id AND
2589 c.cc_header_id = p_cc_header_id;
2590 EXCEPTION
2591 WHEN NO_DATA_FOUND
2592 THEN
2593 l_reval_pf_amt_total := 0;
2594 END;
2595
2596 l_cover_pf_func_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_amt;
2597
2598 IF (l_non_reval_pf_amt_total + l_reval_pf_amt_total)
2599 > (l_cover_pf_func_amt)
2600 THEN
2601 /* Update validation_status to 'F' in temporary table for releases */
2602
2603 populate_errors(l_cc_headers_rec.cc_header_id,
2604 p_process_phase,
2605 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2606 l_sob_id,
2607 l_org_id,
2608 l_request_id1);
2609 EXIT;
2610 END IF;
2611
2612 END LOOP; /* payment forecast */
2613
2614 CLOSE c_pf_lines;
2615
2616 END LOOP; /* Account Lines */
2617
2618 CLOSE c_acct_lines;
2619
2620 END LOOP; /* Cover Contract Commitments */
2621
2622 CLOSE c_cover_reval_data;
2623
2624 END IF; /* Functional Currency Cover */
2625
2626
2627 /* Validate all contract commitments subject to correct revaluation varainces */
2628
2629 OPEN c_reval_data(l_request_id1);
2630 LOOP
2631 FETCH c_reval_data
2632 INTO l_cc_header_id;
2633
2634 EXIT WHEN c_reval_data%NOTFOUND;
2635
2636 /* Get Contract Details */
2637 SELECT *
2638 INTO l_cc_headers_rec
2639 FROM igc_cc_headers
2640 WHERE cc_header_id = l_cc_header_id;
2641
2642 l_validation_status := 'P';
2643
2644 /* Validate Contract Commitment */
2645
2646 IF (l_cc_headers_rec.cc_type = 'C')
2647 THEN
2648 SELECT validation_status
2649 INTO l_curr_validation_status
2650 FROM igc_cc_process_data
2651 WHERE cc_header_id = l_cc_header_id AND
2652 request_id = l_request_id1;
2653 END IF;
2654
2655
2656
2657 l_validation_status := IGC_CC_REP_YEP_PVT.validate_cc(p_process_phase => p_process_phase,
2658 p_process_type => 'F',
2659 p_cc_header_id =>l_cc_header_id,
2660 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2661 p_sob_id => l_sob_id,
2662 p_org_id => l_org_id,
2663 p_year => NULL,
2664 p_prov_enc_on => l_prov_enc_on,
2665 p_request_id => l_request_id1);
2666
2667
2668 IF (l_cc_headers_rec.cc_type = 'C')
2669 THEN
2670 IF (l_curr_validation_status <> 'I')
2671 THEN
2672 l_validation_status := l_curr_validation_status;
2673 END IF;
2674 END IF;
2675
2676 /* Preliminary phase */
2677 IF (p_process_phase = 'P')
2678 THEN
2679 /* Update validation status in temporary table*/
2680
2681
2682 BEGIN
2683 UPDATE igc_cc_process_data
2684 SET
2685 validation_status = l_validation_Status,
2686 processed = 'Y'
2687 WHERE
2688 request_id = l_request_id1 AND
2689 cc_header_id = l_cc_header_id;
2690 EXCEPTION
2691 WHEN NO_DATA_FOUND
2692 THEN
2693 NULL;
2694 END;
2695
2696 IF (l_cc_headers_rec.cc_type = 'C')
2697 THEN
2698 UPDATE igc_cc_process_data
2699 SET
2700 validation_status = l_validation_Status,
2701 processed = 'Y'
2702 WHERE
2703 request_id = l_request_id1 AND
2704 cc_header_id IN (SELECT cc_header_id
2705 FROM igc_cc_headers
2706 WHERE parent_header_id = l_cc_header_id);
2707
2708 END IF;
2709
2710 COMMIT;
2711
2712 END IF; /* Preliminary Phase */
2713
2714 /* Final phase */
2715
2716 IF (p_process_phase = 'F')
2717 THEN
2718 /* Passed Validation */
2719
2720 IF (l_validation_status = 'P')
2721 THEN
2722 /* Update validation status, store old status in temporary table */
2723
2724 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
2725 THEN
2726 BEGIN
2727 UPDATE igc_cc_process_data
2728 SET
2729 validation_status = l_validation_Status,
2730 old_approval_status = l_cc_headers_rec.cc_apprvl_status
2731 WHERE
2732 request_id = l_request_id1 AND
2733 cc_header_id = l_cc_header_id;
2734
2735 EXCEPTION
2736 WHEN NO_DATA_FOUND
2737 THEN
2738 NULL;
2739 END;
2740 END IF;
2741
2742 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
2743 THEN
2744 UPDATE igc_cc_headers
2745 SET cc_apprvl_status = 'IP'
2746 WHERE cc_header_id = l_cc_header_id;
2747 END IF;
2748
2749
2750
2751 /* Added the following code for bug 1632984 */
2752 /* Change begin */
2753
2754 IF (l_cc_headers_rec.cc_type = 'S')
2755 THEN
2756
2757 l_po_count := 0;
2758
2759 BEGIN
2760 SELECT count(po_header_id)
2761 INTO l_po_count
2762 FROM po_headers_all
2763 WHERE segment1 = l_cc_headers_rec.cc_num AND
2764 org_id = l_cc_headers_rec.org_id AND
2765 type_lookup_code = 'STANDARD' ;
2766 EXCEPTION
2767 WHEN NO_DATA_FOUND
2768 THEN
2769 l_po_count := 0;
2770 END;
2771
2772 IF (l_po_count = 1)
2773 THEN
2774
2775 BEGIN
2776 UPDATE po_headers_all
2777 SET approved_flag = 'N'
2778 WHERE segment1 = l_cc_headers_rec.cc_num AND
2779 org_id = l_cc_headers_rec.org_id AND
2780 type_lookup_code = 'STANDARD' AND
2781 approved_flag = 'Y';
2782 END;
2783 END IF;
2784
2785 END IF;
2786 /* Change end */
2787
2788
2789 IF (l_cc_headers_rec.cc_type = 'C')
2790 THEN
2791 OPEN c_all_releases1(l_cc_header_id);
2792 LOOP
2793 FETCH c_all_releases1 INTO l_rel_cc_headers_rec;
2794 EXIT WHEN c_all_releases1%NOTFOUND;
2795
2796 UPDATE igc_cc_process_data
2797 SET
2798 validation_status = l_validation_Status,
2799 old_approval_status = l_rel_cc_headers_rec.cc_apprvl_status
2800 WHERE request_id = l_request_id1 AND
2801 cc_header_id = l_rel_cc_headers_rec.cc_header_id;
2802
2803 UPDATE igc_cc_headers
2804 SET cc_apprvl_status = 'IP'
2805 WHERE cc_header_id = l_rel_cc_headers_rec.cc_header_id ;
2806
2807 l_po_count := 0;
2808
2809
2810 BEGIN
2811 SELECT count(po_header_id)
2812 INTO l_po_count
2813 FROM po_headers_all
2814 WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
2815 org_id = l_rel_cc_headers_rec.org_id AND
2816 type_lookup_code = 'STANDARD' ;
2817 EXCEPTION
2818 WHEN NO_DATA_FOUND
2819 THEN
2820 l_po_count := 0;
2821 END;
2822
2823 IF (l_po_count = 1)
2824 THEN
2825
2826 BEGIN
2827 UPDATE po_headers_all
2828 SET approved_flag = 'N'
2829 WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
2830 org_id = l_rel_cc_headers_rec.org_id AND
2831 type_lookup_code = 'STANDARD' AND
2832 /* Changed statement below from approved_flag = N to approved_flag = Y to fix bug 1632984 */
2833 approved_flag = 'Y';
2834 END;
2835 END IF;
2836
2837 END LOOP;
2838
2839 CLOSE c_all_releases1;
2840 END IF;
2841
2842
2843 ELSIF (l_validation_status = 'F') /* Failed Validation */
2844 THEN
2845 /* Update validation status, in temporary table*/
2846
2847 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
2848 THEN
2849 UPDATE igc_cc_process_data
2850 SET
2851 validation_status = l_validation_Status ,
2852 processed = 'Y'
2853 WHERE request_id = l_request_id1 AND
2854 cc_header_id = l_cc_header_id;
2855 END IF;
2856
2857 IF (l_cc_headers_rec.cc_type = 'C')
2858 THEN
2859 UPDATE igc_cc_process_data
2860 SET
2861 validation_status = l_validation_Status ,
2862 processed = 'Y'
2863 WHERE request_id = l_request_id1 AND
2864 cc_header_id IN (SELECT cc_header_id
2865 FROM igc_cc_headers
2866 WHERE parent_header_id = l_cc_header_id);
2867 END IF;
2868
2869 END IF;
2870
2871 END IF; /* Final Phase */
2872
2873 END LOOP;
2874
2875 CLOSE c_reval_data;
2876 COMMIT;
2877
2878 /* End Validation Phase */
2879
2880 /* Begin Reservation phase */
2881 IF (p_process_phase = 'F')
2882 THEN
2883 /* Perform Funds Reservation for Contract Commitments */
2884
2885
2886 SELECT *
2887 INTO l_cc_headers_rec
2888 FROM igc_cc_headers
2889 WHERE cc_header_id = p_cc_header_id;
2890
2891
2892 SELECT validation_status
2893 INTO l_validation_status
2894 FROM igc_cc_process_data
2895 WHERE request_id = l_request_id1 AND
2896 cc_header_id = p_cc_header_id ;
2897
2898 IF (l_validation_status = 'P')
2899 THEN
2900
2901 l_reservation_status := 'P';
2902
2903 /* Perform funds reservation in Forced mode for Contract Commitment */
2904
2905 IF ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT')
2906 )
2907 AND (l_sbc_on = TRUE) AND (l_conf_enc_on = TRUE)
2908 )
2909 THEN
2910
2911 IF ( ((l_cc_headers_rec.cc_type = 'R') AND (l_currency_code <> l_cover_currency_code) )
2912 OR
2913 (l_cc_headers_rec.cc_type = 'S') )
2914 THEN
2915 l_reservation_status :=
2916 IGC_CC_REP_YEP_PVT.Encumber_CC
2917 (
2918 p_process_type => 'F',
2919 p_cc_header_id => p_cc_header_id,
2920 p_sbc_on => l_sbc_on,
2921 p_cbc_on => l_cbc_on,
2922 /* Bug No : 6341012. SLA Uptake. Encumbrance Type IDs are not required */
2923 -- p_cc_prov_enc_type_id => l_cc_prov_enc_type_id,
2924 -- p_cc_conf_enc_type_id => l_cc_conf_enc_type_id,
2925 -- p_req_encumbrance_type_id => l_req_encumbrance_type_id,
2926 -- p_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
2927 p_currency_code => l_currency_code,
2928 p_yr_start_date => NULL,
2929 p_yr_end_date => NULL,
2930 p_yr_end_cr_date => NULL,
2931 p_yr_end_dr_date => NULL,
2932 p_rate_date => NULL,
2933 p_rate => NULL,
2934 p_revalue_fix_date => l_revalue_fix_date);
2935 END IF;
2936 ELSE
2937 l_reservation_status := 'P';
2938 END IF;
2939
2940
2941 IF (l_reservation_status = 'F')
2942 THEN
2943 l_message := NULL;
2944 FND_MESSAGE.SET_NAME('IGC','IGC_CC_ENCUMBRANCE_FAILURE');
2945 l_message := FND_MESSAGE.GET;
2946
2947 INSERT INTO
2948 igc_cc_process_exceptions
2949 ( process_type,
2950 process_phase,
2951 cc_header_id,
2952 cc_acct_line_id,
2953 cc_det_pf_line_id,
2954 exception_reason,
2955 org_id,
2956 set_of_books_id,
2957 request_id
2958 )
2959 VALUES
2960 ( 'F',
2961 'F',
2962 l_cc_headers_rec.parent_header_id,
2963 NULL,
2964 NULL,
2965 l_message,
2966 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
2967 l_org_id,
2968 l_sob_id,
2969 l_request_id1
2970 );
2971
2972 COMMIT;
2973 END IF;
2974
2975 /* Update validation status, in temporary table*/
2976
2977 IF (l_cc_headers_rec.cc_type = 'S')
2978 THEN
2979 UPDATE igc_cc_process_data
2980 SET
2981 reservation_status = l_reservation_Status
2982 WHERE
2983 request_id = l_request_id1 AND
2984 cc_header_id = p_cc_header_id ;
2985 END IF;
2986
2987 IF (l_cc_headers_rec.cc_type = 'R')
2988 THEN
2989 /* Update Cover */
2990 UPDATE igc_cc_process_data
2991 SET
2992 reservation_status = l_reservation_Status
2993 WHERE
2994 request_id = l_request_id1 AND
2995 cc_header_id = l_cc_headers_rec.parent_header_id ;
2996
2997 /* Update Relases */
2998 UPDATE igc_cc_process_data
2999 SET
3000 reservation_status = l_reservation_Status
3001 WHERE
3002 request_id = l_request_id1 AND
3003 cc_header_id IN (SELECT cc_header_id
3004 FROM igc_cc_headers
3005 WHERE parent_header_id =
3006 l_cc_headers_rec.parent_header_id);
3007 END IF;
3008
3009 COMMIT;
3010
3011 /* Process Cover release */
3012 IF (l_cc_headers_rec.cc_type = 'R')
3013 THEN
3014 SAVEPOINT REVALUE5;
3015
3016 l_process_flag := 'P';
3017
3018 /* Process the cover */
3019
3020 l_validate_only := 'Y';
3021
3022 SELECT validate_only
3023 INTO l_validate_only
3024 FROM igc_cc_process_data
3025 WHERE request_id = l_request_id1 AND
3026 cc_header_id = l_cc_headers_rec.parent_header_id;
3027
3028
3029 IF (l_reservation_status = 'P')
3030 THEN
3031
3032
3033 l_message := NULL;
3034 l_err_header_id := NULL;
3035 l_err_acct_line_id := NULL;
3036 l_err_det_pf_line_id := NULL;
3037
3038 l_process_flag := reval_fix_update(l_cc_headers_rec.parent_header_id,
3039 p_cc_header_id,
3040 l_revalue_fix_date,
3041 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3042 l_sob_id,
3043 l_org_id,
3044 l_sbc_on,
3045 l_cbc_on,
3046 l_prov_enc_on,
3047 l_conf_enc_on,
3048 l_validate_only,
3049 l_request_id1,
3050 l_message,
3051 l_err_header_id,
3052 l_err_acct_line_id,
3053 l_err_det_pf_line_id);
3054 IF (l_process_flag = 'F')
3055 THEN
3056 ROLLBACK TO REVALUE5;
3057
3058 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
3059 (PROCESS_TYPE,
3060 PROCESS_PHASE,
3061 CC_HEADER_ID,
3062 CC_ACCT_LINE_ID,
3063 CC_DET_PF_LINE_ID,
3064 EXCEPTION_REASON,
3065 ORG_ID,
3066 SET_OF_BOOKS_ID,
3067 REQUEST_ID)
3068 VALUES (
3069 'F',
3070 'F',
3071 l_err_header_id,
3072 l_err_acct_line_id,
3073 l_err_det_pf_line_id,
3074 l_message,
3075 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3076 l_org_id,
3077 l_sob_id,
3078 l_REQUEST_ID1);
3079 COMMIT;
3080 END IF;
3081
3082 ELSIF (l_reservation_status = 'F')
3083 THEN
3084
3085 l_message := NULL;
3086 l_err_header_id := NULL;
3087 l_err_acct_line_id := NULL;
3088 l_err_det_pf_line_id := NULL;
3089
3090 l_process_flag := reval_fix_update(l_cc_headers_rec.parent_header_id,
3091 NULL,
3092 l_revalue_fix_date,
3093 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3094 l_sob_id,
3095 l_org_id,
3096 l_sbc_on,
3097 l_cbc_on,
3098 l_prov_enc_on,
3099 l_conf_enc_on,
3100 'Y',
3101 l_request_id1,
3102 l_message,
3103 l_err_header_id,
3104 l_err_acct_line_id,
3105 l_err_det_pf_line_id);
3106
3107
3108 IF (l_process_flag = 'F')
3109 THEN
3110 ROLLBACK TO REVALUE5;
3111
3112 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
3113 (PROCESS_TYPE,
3114 PROCESS_PHASE,
3115 CC_HEADER_ID,
3116 CC_ACCT_LINE_ID,
3117 CC_DET_PF_LINE_ID,
3118 EXCEPTION_REASON,
3119 ORG_ID,
3120 SET_OF_BOOKS_ID,
3121 REQUEST_ID)
3122 VALUES (
3123 'F',
3124 'F',
3125 l_err_header_id,
3126 l_err_acct_line_id,
3127 l_err_det_pf_line_id,
3128 l_message,
3129 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3130 l_org_id,
3131 l_sob_id,
3132 l_REQUEST_ID1);
3133 COMMIT;
3134 END IF;
3135
3136 END IF; /* Reservation status */
3137
3138
3139 /* Process the releases */
3140 IF (l_process_flag = 'P')
3141 THEN
3142 OPEN c_all_releases(l_cc_headers_rec.parent_header_id);
3143 LOOP
3144 FETCH c_all_releases INTO l_rel_cc_header_id;
3145 EXIT WHEN c_all_releases%NOTFOUND;
3146
3147
3148 l_validate_only := 'Y';
3149
3150 SELECT validate_only
3151 INTO l_validate_only
3152 FROM igc_cc_process_data
3153 WHERE request_id = l_request_id1 AND
3154 cc_header_id = l_rel_cc_header_id;
3155
3156
3157 IF (l_reservation_status = 'P')
3158 THEN
3159
3160
3161 l_message := NULL;
3162 l_err_header_id := NULL;
3163 l_err_acct_line_id := NULL;
3164 l_err_det_pf_line_id := NULL;
3165
3166 l_process_flag := reval_fix_update(l_rel_cc_header_id,
3167 NULL,
3168 l_revalue_fix_date,
3169 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3170 l_sob_id,
3171 l_org_id,
3172 l_sbc_on,
3173 l_cbc_on,
3174 l_prov_enc_on,
3175 l_conf_enc_on,
3176 l_validate_only,
3177 l_request_id1,
3178 l_message,
3179 l_err_header_id,
3180 l_err_acct_line_id,
3181 l_err_det_pf_line_id);
3182 ELSIF (l_reservation_status = 'F')
3183 THEN
3184
3185
3186 l_message := NULL;
3187 l_err_header_id := NULL;
3188 l_err_acct_line_id := NULL;
3189 l_err_det_pf_line_id := NULL;
3190
3191 l_process_flag := reval_fix_update(l_rel_cc_header_id,
3192 NULL,
3193 l_revalue_fix_date,
3194 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3195 l_sob_id,
3196 l_org_id,
3197 l_sbc_on,
3198 l_cbc_on,
3199 l_prov_enc_on,
3200 l_conf_enc_on,
3201 'Y',
3202 l_request_id1,
3203 l_message,
3204 l_err_header_id,
3205 l_err_acct_line_id,
3206 l_err_det_pf_line_id);
3207 END IF;
3208
3209 IF (l_process_flag = 'F')
3210 THEN
3211 /* Rollback changes to contract commitments */
3212 ROLLBACK TO REVALUE5;
3213
3214 /* Populate exceptions table */
3215
3216 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
3217 (PROCESS_TYPE,
3218 PROCESS_PHASE,
3219 CC_HEADER_ID,
3220 CC_ACCT_LINE_ID,
3221 CC_DET_PF_LINE_ID,
3222 EXCEPTION_REASON,
3223 ORG_ID,
3224 SET_OF_BOOKS_ID,
3225 REQUEST_ID)
3226 VALUES (
3227 'F',
3228 'F',
3229 l_err_header_id,
3230 l_err_acct_line_id,
3231 l_err_det_pf_line_id,
3232 l_message,
3233 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3234 l_org_id,
3235 l_sob_id,
3236 l_REQUEST_ID1);
3237
3238 COMMIT;
3239
3240 EXIT;
3241 END IF;
3242
3243 END LOOP;
3244
3245 CLOSE c_all_releases;
3246
3247 END IF;
3248
3249 IF (l_process_flag = 'P')
3250 THEN
3251 COMMIT;
3252 END IF;
3253
3254
3255 ELSIF (l_cc_headers_rec.cc_type = 'S')
3256 THEN
3257 SAVEPOINT REVALUE6;
3258
3259 l_process_flag := 'F';
3260
3261
3262 IF (l_reservation_status = 'P')
3263 THEN
3264
3265
3266 l_message := NULL;
3267 l_err_header_id := NULL;
3268 l_err_acct_line_id := NULL;
3269 l_err_det_pf_line_id := NULL;
3270
3271 l_process_flag := reval_fix_update(l_cc_headers_rec.cc_header_id,
3272 NULL,
3273 l_revalue_fix_date,
3274 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3275 l_sob_id,
3276 l_org_id,
3277 l_sbc_on,
3278 l_cbc_on,
3279 l_prov_enc_on,
3280 l_conf_enc_on,
3281 'N',
3282 l_request_id1,
3283 l_message,
3284 l_err_header_id,
3285 l_err_acct_line_id,
3286 l_err_det_pf_line_id);
3287 IF (l_process_flag = 'F')
3288 THEN
3289 ROLLBACK TO REVALUE6;
3290
3291 /* Populate exceptions table */
3292
3293 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
3294 (PROCESS_TYPE,
3295 PROCESS_PHASE,
3296 CC_HEADER_ID,
3297 CC_ACCT_LINE_ID,
3298 CC_DET_PF_LINE_ID,
3299 EXCEPTION_REASON,
3300 ORG_ID,
3301 SET_OF_BOOKS_ID,
3302 REQUEST_ID)
3303 VALUES (
3304 'F',
3305 'F',
3306 l_err_header_id,
3307 l_err_acct_line_id,
3308 l_err_det_pf_line_id,
3309 l_message,
3310 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3311 l_org_id,
3312 l_org_id,
3313 l_REQUEST_ID1);
3314
3315 COMMIT;
3316
3317 END IF;
3318 ELSIF (l_reservation_status = 'F')
3319 THEN
3320
3321
3322 l_message := NULL;
3323 l_err_header_id := NULL;
3324 l_err_acct_line_id := NULL;
3325 l_err_det_pf_line_id := NULL;
3326
3327 l_process_flag := reval_fix_update(l_cc_headers_rec.cc_header_id,
3328 NULL,
3329 l_revalue_fix_date,
3330 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3331 l_sob_id,
3332 l_org_id,
3333 l_sbc_on,
3334 l_cbc_on,
3335 l_prov_enc_on,
3336 l_conf_enc_on,
3337 'Y',
3338 l_request_id1,
3339 l_message,
3340 l_err_header_id,
3341 l_err_acct_line_id,
3342 l_err_det_pf_line_id);
3343
3344
3345 IF (l_process_flag = 'F')
3346 THEN
3347 ROLLBACK TO REVALUE6;
3348
3349 /* Populate exceptions table */
3350
3351 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
3352 (PROCESS_TYPE,
3353 PROCESS_PHASE,
3354 CC_HEADER_ID,
3355 CC_ACCT_LINE_ID,
3356 CC_DET_PF_LINE_ID,
3357 EXCEPTION_REASON,
3358 ORG_ID,
3359 SET_OF_BOOKS_ID,
3360 REQUEST_ID)
3361 VALUES (
3362 'F',
3363 'F',
3364 l_err_header_id,
3365 l_err_acct_line_id,
3366 l_err_det_pf_line_id,
3367 l_message,
3368 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3369 l_org_id,
3370 l_sob_id,
3371 l_REQUEST_ID1);
3372
3373 COMMIT;
3374
3375 END IF;
3376
3377 END IF; /* reservation status = F */
3378
3379 IF (l_process_flag = 'P')
3380 THEN
3381 COMMIT;
3382 END IF;
3383
3384 END IF; /* cc_type = STANDARD */
3385 END IF; /* validation_status = P */
3386
3387 END IF; /* Final Phase */
3388 /* End Reservation Phase */
3389
3390 END IF;
3391
3392 COMMIT;
3393
3394 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
3395 Fnd_request.set_org_id(l_org_id);
3396 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
3397 'IGC',
3398 'IGCCRVFR',
3399 NULL,
3400 NULL,
3401 FALSE,
3402 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3403 l_sob_id,
3404 l_org_id,
3405 p_process_phase,
3406 'F',
3407 l_request_id1);
3408 -----------------------
3409 -- Start of XML Report
3410 -----------------------
3411 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
3412 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
3413 l_lang,
3414 l_terr,
3415 'IGCCRVFR_XML',
3416 'IGC',
3417 'IGCCRVFR_XML' );
3418 l_layout := FND_REQUEST.ADD_LAYOUT(
3419 'IGC',
3420 'IGCCRVFR_XML',
3421 l_lang,
3422 l_terr,
3423 'RTF');
3424 IF l_layout then
3425 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
3426 'IGC',
3427 'IGCCRVFR_XML',
3428 NULL,
3429 NULL,
3430 FALSE,
3431 /* Bug No : 6341012. R12 Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
3432 l_sob_id,
3433 l_org_id,
3434 p_process_phase,
3435 'F',
3436 l_request_id1);
3437 END IF;
3438 END IF;
3439
3440 --------------------
3441 -- End of XML Report
3442 --------------------
3443 -- ------------------------------------------------------------------------------------
3444 -- Ensure that any exceptions raised are output into the log file to be reported to
3445 -- the user if any are present.
3446 -- ------------------------------------------------------------------------------------
3447 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
3448 p_data => l_msg_data );
3449
3450 IF (l_msg_count > 0) THEN
3451 l_error_text := '';
3452 FOR l_cur IN 1..l_msg_count LOOP
3453 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
3454 -- fnd_file.put_line (FND_FILE.LOG,
3455 -- l_error_text);
3456 -- bug 3199488 start block
3457 IF (l_state_level >= l_debug_level) THEN
3458 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp3',
3459 l_error_text);
3460 END IF;
3461 -- bug 3199488, end block
3462 END LOOP;
3463 END IF;
3464
3465 EXCEPTION
3466
3467 WHEN insert_data
3468 THEN
3469 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3470 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'revalue_fix_main');
3471 END IF;
3472 -- bug 3199488 start block
3473 IF (l_unexp_level >= l_debug_level) THEN
3474 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Unexp1',TRUE);
3475 END IF;
3476 -- bug 3199488, end block
3477 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
3478 p_data => l_msg_data );
3479
3480 IF (l_msg_count > 0) THEN
3481
3482 l_error_text := '';
3483 FOR l_cur IN 1..l_msg_count LOOP
3484 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
3485 -- fnd_file.put_line (FND_FILE.LOG,
3486 -- l_error_text);
3487 -- bug 3199488 start block
3488 IF (l_state_level >= l_debug_level) THEN
3489 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp4',
3490 l_error_text);
3491 END IF;
3492 -- bug 3199488, end block
3493 END LOOP;
3494 ELSE
3495 l_error_text := 'Error Returned but Error stack has no data';
3496 -- fnd_file.put_line (FND_FILE.LOG,
3497 -- l_error_text);
3498 -- bug 3199488 start block
3499 IF (l_state_level >= l_debug_level) THEN
3500 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp5',
3501 l_error_text);
3502 END IF;
3503 -- bug 3199488, end block
3504 END IF;
3505 ROLLBACK TO REVALUE3;
3506
3507 WHEN OTHERS THEN
3508 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3509 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'revalue_fix_main');
3510 END IF;
3511 -- bug 3199488, start block
3512 IF (l_unexp_level >= l_debug_level) THEN
3513 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3514 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
3515 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
3516 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Unexp2',TRUE);
3517 END IF;
3518 -- bug 3199488, end block
3519
3520 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
3521 p_data => l_msg_data );
3522
3523 IF (l_msg_count > 0) THEN
3524
3525 l_error_text := '';
3526 FOR l_cur IN 1..l_msg_count LOOP
3527 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
3528 -- fnd_file.put_line (FND_FILE.LOG,
3529 -- l_error_text);
3530 -- bug 3199488 start block
3531 IF (l_state_level >= l_debug_level) THEN
3532 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp6',
3533 l_error_text);
3534 END IF;
3535 -- bug 3199488, end block
3536 END LOOP;
3537 ELSE
3538 l_error_text := 'Error Returned but Error stack has no data';
3539 -- fnd_file.put_line (FND_FILE.LOG,
3540 -- l_error_text);
3541 -- bug 3199488 start block
3542 IF (l_state_level >= l_debug_level) THEN
3543 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_reval_fix_process_pkg.revalue_fix_main.Excp7',
3544 l_error_text);
3545 END IF;
3546 -- bug 3199488, end block
3547 END IF;
3548
3549 END revalue_fix_main;
3550
3551 END IGC_CC_REVAL_FIX_PROCESS_PKG;