[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_REVALUE_PROCESS_PKG
Source
1 PACKAGE BODY IGC_CC_REVALUE_PROCESS_PKG AS
2 /*$Header: IGCCREPB.pls 120.21.12010000.2 2008/08/29 13:16:25 schakkin ship $*/
3
4 --Bug 3199488 Start Block
5 l_debug_level number;
6
7 l_state_level number;
8 l_proc_level number;
9 l_event_level number;
10 l_excep_level number;
11 l_error_level number;
12 l_unexp_level number;
13 --Bug 3199488 End Block
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_REVALUE_PROCESS_PKG';
16 l_debug_mode VARCHAR2(1);
17
18 -- The flag determines whether to print debug information or not.
19 g_debug_flag VARCHAR2(1);
20 g_debug_msg VARCHAR2(10000);
21
22 --
23 -- Generic Procedure for putting out debug information
24 --
25 /* Commented out as per bug 3199488
26 PROCEDURE Output_Debug (
27 p_debug_msg IN VARCHAR2
28 );
29
30
31 /* Checks whether all the invoices related to
32 Contract Commitment are either approved or cancelled */
33
34 FUNCTION validate_params(p_process_phase IN VARCHAR2,
35 p_sob_id IN NUMBER,
36 p_org_id IN NUMBER,
37 p_currency_code IN VARCHAR2,
38 p_func_currency_code IN VARCHAR2,
39 p_rate_type IN VARCHAR2,
40 p_rate IN NUMBER,
41 p_rate_date IN DATE,
42 p_request_id IN NUMBER)
43 RETURN BOOLEAN
44 IS
45 l_period_status gl_period_statuses.closing_status%TYPE;
46 l_cc_period_status igc_cc_periods.cc_period_status%TYPE;
47 l_rate NUMBER;
48 l_message igc_cc_process_exceptions.exception_reason%TYPE;
49 BEGIN
50
51 -- IF l_debug_mode = 'Y' THEN
52 -- Output_Debug (' IGCCREPB -- Validate Params begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
53 -- END IF;
54
55 -- bug 3199488, start block
56 IF (l_state_level >= l_debug_level) THEN
57 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.validate_params.Msg1',
58 ' IGCCREPB -- Validate Params begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
59 END IF;
60 -- bug 3199488, end block
61
62 IF (p_rate_type <> 'User') AND (p_rate > 0)
63 THEN
64 l_rate := 0;
65
66 BEGIN
67
68 SELECT conversion_rate
69 INTO l_rate
70 FROM gl_daily_rates
71 WHERE from_currency = p_currency_code AND
72 to_currency = p_func_currency_code AND
73 conversion_type = p_rate_type AND
74 conversion_date = p_rate_date;
75
76 -- If length of rate is greater than 38
77 -- round it off to 30
78 -- as that is the maximum precision of p_rate
79 -- the input parameter)
80 -- Bug 1808755, Bidisha S, 13 July 01
81 IF LENGTH(l_rate) > 38
82 THEN
83 l_rate := ROUND(l_rate, 30);
84 END IF;
85 EXCEPTION
86 WHEN NO_DATA_FOUND
87 THEN
88 l_rate := 0;
89 END;
90
91 END IF;
92
93 /* Check the rate */
94 IF (NVL(p_rate,-9999) <= 0) OR (p_rate IS NULL)
95 OR
96 ( (p_rate > 0) AND (p_rate_type <> 'User')
97 AND (l_rate <> p_rate))
98 THEN
99 l_message := NULL;
100 FND_MESSAGE.SET_NAME('IGC','IGC_CC_INVALID_RATE');
101 FND_MESSAGE.SET_TOKEN('RATE',TO_CHAR(p_rate),TRUE);
102 l_message := FND_MESSAGE.GET;
103
104 INSERT INTO
105 igc_cc_process_exceptions
106 ( process_type,
107 process_phase,
108 cc_header_id,
109 cc_acct_line_id,
110 cc_det_pf_line_id,
111 exception_reason,
112 org_id,
113 set_of_books_id,
114 request_id
115 )
116 VALUES
117 ( 'R',
118 p_process_phase,
119 NULL,
120 NULL,
121 NULL,
122 l_message,
123 p_org_id,
124 p_sob_id,
125 p_request_id
126 );
127
128 COMMIT;
129
130 RETURN(FALSE);
131
132 END IF;
133
134 l_period_status := NULL;
135
136 IF (p_rate_date IS NOT NULL)
137 THEN
138
139 /* Check whether GL period is open */
140
141 BEGIN
142 SELECT gps.closing_status
143 INTO l_period_status
144 FROM gl_period_statuses gps,
145 gl_periods gp,
146 gl_sets_of_books gb,
147 gl_period_types gpt,
148 fnd_application fa
149 WHERE
150 gb.set_of_books_id = p_sob_id AND
151 gp.period_set_name = gb.period_set_name AND
152 gp.period_type = gb.accounted_period_type AND
153 /* begin fix for bug 1569051*/
154 gp.adjustment_period_flag = 'N' AND
155 /* end fix for bug 1569051*/
156 gpt.period_type = gp.period_type AND
157 gps.set_of_books_id = gb.set_of_books_id AND
158 gps.period_name = gp.period_name AND
159 gps.application_id = fa.application_id AND
160 fa.application_short_name = 'SQLGL' AND
161 (gp.start_date <= p_rate_date AND gp.end_date >= p_rate_date);
162 EXCEPTION
163 WHEN NO_DATA_FOUND
164 THEN
165 NULL;
166
167 END;
168 END IF;
169
170 IF (l_period_status IS NULL) OR ( NVL(l_period_status,'X') <> 'O') OR (p_rate_date IS NULL)
171 THEN
172 l_message := NULL;
173 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REVALUATION_DATE');
174 FND_MESSAGE.SET_TOKEN('DATE',to_char(p_rate_date ,'DD-MON-YYYY') ,TRUE);
175 l_message := FND_MESSAGE.GET;
176
177 INSERT INTO
178 igc_cc_process_exceptions
179 ( process_type,
180 process_phase,
181 cc_header_id,
182 cc_acct_line_id,
183 cc_det_pf_line_id,
184 exception_reason,
185 org_id,
186 set_of_books_id,
187 request_id
188 )
189 VALUES
190 ( 'R',
191 p_process_phase,
192 NULL,
193 NULL,
194 NULL,
195 l_message,
196 p_org_id,
197 p_sob_id,
198 p_request_id
199 );
200
201 COMMIT;
202
203 RETURN(FALSE);
204 END IF;
205
206 /* Check whether CC period is open */
207 BEGIN
208 SELECT ccp.cc_period_status
209 INTO l_cc_period_status
210 FROM igc_cc_periods ccp,
211 gl_periods gp ,
212 gl_sets_of_books gb
213 WHERE
214 ccp.period_set_name = gp.period_set_name AND
215 gp.period_set_name = gb.period_set_name AND
216 ccp.org_id = p_org_id AND
217 ccp.period_name = gp.period_name AND
218 gp.period_type = gb.accounted_period_type AND
219 gp.adjustment_period_flag = 'N' AND
220 gb.set_of_books_id = p_sob_id AND
221 (gp.start_date <= p_rate_date AND gp.end_date >= p_rate_date);
222 EXCEPTION
223 WHEN NO_DATA_FOUND
224 THEN
225 RETURN(FALSE);
226 END;
227
228 IF (l_cc_period_status IS NULL) OR (NVL(l_cc_period_status,'X') <> 'O')
229 THEN
230 l_message := NULL;
231 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REVALUATION_DATE');
232 FND_MESSAGE.SET_TOKEN('DATE',to_char(p_rate_date ,'DD-MON-YYYY') ,TRUE);
233 l_message := FND_MESSAGE.GET;
234
235 INSERT INTO
236 igc_cc_process_exceptions
237 ( process_type,
238 process_phase,
239 cc_header_id,
240 cc_acct_line_id,
241 cc_det_pf_line_id,
242 exception_reason,
243 org_id,
244 set_of_books_id,
245 request_id
246 )
247 VALUES
248 ( 'R',
249 p_process_phase,
250 NULL,
251 NULL,
252 NULL,
253 l_message,
254 p_org_id,
255 p_sob_id,
256 p_request_id
257 );
258
259 COMMIT;
260
261 RETURN(FALSE);
262 END IF;
263
264 -- IF l_debug_mode = 'Y' THEN
265 -- Output_Debug (' IGCCREPB -- Validate Params ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
266 -- END IF;
267
268 -- bug 3199488, start block
269 IF (l_state_level >= l_debug_level) THEN
270 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.validate_params.Msg2',
271 ' IGCCREPB -- Validate Params ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
272 END IF;
273 -- bug 3199488, end block
274
275 RETURN(TRUE);
276
277 END validate_params;
278
279 FUNCTION lock_cc_po(p_sob_id IN NUMBER,
280 p_org_id IN NUMBER,
281 p_cc_header_id IN NUMBER,
282 p_request_id IN NUMBER)
283 RETURN BOOLEAN
284 IS
285 l_lock_cc BOOLEAN;
286 l_lock_po BOOLEAN;
287 l_message igc_cc_process_exceptions.exception_reason%TYPE;
288 l_cc_num igc_cc_headers.cc_num%TYPE;
289 BEGIN
290
291 --GSCC Warnings Fixed
292
293 l_lock_cc := TRUE;
294 l_lock_po := TRUE;
295
296 -- IF l_debug_mode = 'Y' THEN
297 -- Output_Debug (' IGCCREPB -- Lock CC PO Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
298 -- END IF;
299
300 -- bug 3199488, start block
301 IF (l_state_level >= l_debug_level) THEN
302 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.lock_cc_po.Msg1',
303 ' IGCCREPB -- Lock CC PO Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
304 END IF;
305 -- bug 3199488, end block
306
307 SELECT cc_num
308 INTO l_cc_num
309 FROM igc_cc_headers
310 WHERE cc_header_id = p_cc_header_id;
311
312 l_lock_cc := TRUE;
313 l_lock_po := TRUE;
314
315 /* Lock all contract commitment being re-valued */
316 l_lock_cc := IGC_CC_REP_YEP_PVT.lock_cc(p_cc_header_id);
317
318 IF (NOT l_lock_cc)
319 THEN
320 l_message := NULL;
321 FND_MESSAGE.SET_NAME('IGC','IGC_CC_LOCK_FAILURE');
322 FND_MESSAGE.SET_TOKEN('CC_NUM', l_cc_num ,TRUE);
323 l_message := FND_MESSAGE.GET;
324
325 INSERT
326 INTO igc_cc_process_exceptions
327 ( process_type,
328 process_phase,
329 cc_header_id,
330 cc_acct_line_id,
331 cc_det_pf_line_id,
332 exception_reason,
333 org_id,
334 set_of_books_id,
335 request_id
336 )
337 VALUES
338 ( 'R',
339 'F',
340 p_cc_header_id,
341 NULL,
342 NULL,
343 l_message,
344 p_org_id,
345 p_sob_id,
346 p_request_id
347 );
348
349 END IF;
350
351
352
353 /* Lock all purchase orders related to contract commitments being re-valued */
354 l_lock_po := IGC_CC_REP_YEP_PVT.lock_po(p_cc_header_id);
355
356 IF (NOT l_lock_po)
357 THEN
358 l_message := NULL;
359 FND_MESSAGE.SET_NAME('IGC','IGC_CC_PO_LOCK_FAILURE');
360 FND_MESSAGE.SET_TOKEN('CC_NUM', l_cc_num ,TRUE);
361 l_message := FND_MESSAGE.GET;
362
363 INSERT
364 INTO igc_cc_process_exceptions
365 ( process_type,
366 process_phase,
367 cc_header_id,
368 cc_acct_line_id,
369 cc_det_pf_line_id,
370 exception_reason,
371 org_id,
372 set_of_books_id,
373 request_id
374 )
375 VALUES
376 ( 'R',
377 'F',
378 p_cc_header_id,
379 NULL,
380 NULL,
381 l_message,
382 p_org_id,
383 p_sob_id,
384 p_request_id
385 );
386
387 END IF;
388
389 -- IF l_debug_mode = 'Y' THEN
390 -- Output_Debug (' IGCCREPB -- Lock CC PO Ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
391 -- END IF;
392
393 -- bug 3199488, start block
394 IF (l_state_level >= l_debug_level) THEN
395 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.lock_cc_po.Msg2',
396 ' IGCCREPB -- Lock CC PO Ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
397 END IF;
398 -- bug 3199488, end block
399
400 IF (l_lock_po = TRUE) AND (l_lock_cc = TRUE)
401 THEN
402 RETURN(TRUE);
403 ELSE
404 RETURN(FALSE);
405
406 END IF;
407 EXCEPTION
408 WHEN OTHERS
409 THEN
410 RETURN(FALSE);
411
412 END lock_cc_po;
413
414 FUNCTION reval_update(p_cc_header_id IN NUMBER,
415 p_rate_date IN DATE,
416 p_rate IN NUMBER,
417 p_sob_id IN NUMBER,
418 p_org_id IN NUMBER,
419 p_sbc_on IN BOOLEAN,
420 p_cbc_on IN BOOLEAN,
421 p_prov_enc_on IN BOOLEAN,
422 p_conf_enc_on IN BOOLEAN,
423 p_validate_only IN VARCHAR2,
424 p_request_id IN NUMBER,
425 p_message OUT NOCOPY VARCHAR2,
426 p_err_header_id OUT NOCOPY NUMBER,
427 p_err_acct_line_id OUT NOCOPY NUMBER,
428 p_err_det_pf_line_id OUT NOCOPY NUMBER
429 )
430 RETURN VARCHAR2
431 IS
432
433 l_cc_headers_rec igc_cc_headers%ROWTYPE;
434 l_rel_cc_headers_rec igc_cc_headers%ROWTYPE;
435 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
436 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
437
438 l_exception igc_cc_process_exceptions.exception_reason%TYPE;
439 l_action_hist_msg igc_cc_actions.cc_action_notes%TYPE;
440
441 CURSOR c_acct_lines(p_cc_header_id NUMBER)
442 IS
443 -- Replaced the folllowing query with the one below for
444 -- performance tuning fixes.
445 -- The record definition of l_cc_acct_lines_rec is still based on
446 -- view igc_cc_acct_lines_v. Instead of selecting from the view,
447 -- select is being done from the base table, but all the columns
448 -- as defined in the view are retained even though they are not used.
449 -- This is just so that minimal change is made to the code.
450 /*
451 SELECT *
452 FROM igc_cc_acct_lines_v
453 WHERE cc_header_id = p_cc_header_id;
454 */
455
456 SELECT ccal.ROWID,
457 ccal.cc_header_id,
458 NULL org_id,
459 NULL cc_type,
460 NULL cc_type_code,
461 NULL cc_num,
462 ccal.cc_acct_line_id,
463 ccal.cc_acct_line_num,
464 ccal.cc_acct_desc,
465 ccal.parent_header_id,
466 ccal.parent_acct_line_id,
467 NULL parent_cc_acct_line_num,
468 NULL cc_budget_acct_desc,
469 ccal.cc_budget_code_combination_id,
470 NULL cc_charge_acct_desc,
471 ccal.cc_charge_code_combination_id,
472 ccal.cc_acct_entered_amt,
473 ccal.cc_acct_func_amt,
474 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
475 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
476 ccal.cc_acct_encmbrnc_amt,
477 ( 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,
478 ccal.cc_acct_unbilled_amt,
479 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
480 NULL project_number ,
481 ccal.project_id,
482 NULL task_number,
483 ccal.task_id,
484 ccal.expenditure_type,
485 NULL expenditure_org_name,
486 ccal.expenditure_org_id,
487 ccal.expenditure_item_date,
488 ccal.cc_acct_taxable_flag,
489 NULL tax_name,
490 NULL tax_id, -- Bug 6472296 Ebtax uptake for cc
491 ccal.cc_acct_encmbrnc_status,
492 ccal.cc_acct_encmbrnc_date,
493 ccal.context,
494 ccal.attribute1,
495 ccal.attribute2,
496 ccal.attribute3,
497 ccal.attribute4,
498 ccal.attribute5,
499 ccal.attribute6,
500 ccal.attribute7,
501 ccal.attribute8,
502 ccal.attribute9,
503 ccal.attribute10,
504 ccal.attribute11,
505 ccal.attribute12,
506 ccal.attribute13,
507 ccal.attribute14,
508 ccal.attribute15,
509 ccal.created_by,
510 ccal.creation_date,
511 ccal.last_updated_by,
512 ccal.last_update_date,
513 ccal.last_update_login,
514 ccal.cc_func_withheld_amt,
515 ccal.cc_ent_withheld_amt,
516 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
517 NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
518 ccal.tax_classif_code -- added Bug 6472296 Ebtax uptake for cc
519 FROM igc_cc_acct_lines ccal
520 WHERE ccal.cc_header_id = p_cc_header_id;
521
522 CURSOR c_pf_lines(p_cc_acct_line_id NUMBER)
523 IS
524 -- SELECT *
525 -- FROM igc_cc_det_pf_v
526 -- WHERE cc_acct_line_id = p_cc_acct_line_id;
527
528 SELECT ccdpf.ROWID,
529 ccdpf.cc_det_pf_line_id,
530 ccdpf.cc_det_pf_line_num,
531 NULL cc_acct_line_num,
532 ccdpf.cc_acct_line_id,
533 NULL parent_det_pf_line_num,
534 ccdpf.parent_det_pf_line_id,
535 ccdpf.parent_acct_line_id,
536 ccdpf.cc_det_pf_entered_amt,
537 ccdpf.cc_det_pf_func_amt,
538 ccdpf.cc_det_pf_date,
539 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,
540 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,
541 ccdpf.cc_det_pf_unbilled_amt,
542 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,
543 ccdpf.cc_det_pf_encmbrnc_amt,
544 ( 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 ,
545 ccdpf.cc_det_pf_encmbrnc_date,
546 ccdpf.cc_det_pf_encmbrnc_status,
547 ccdpf.context,
548 ccdpf.attribute1,
549 ccdpf.attribute2,
550 ccdpf.attribute3,
551 ccdpf.attribute4,
552 ccdpf.attribute5,
553 ccdpf.attribute6,
554 ccdpf.attribute7,
555 ccdpf.attribute8,
556 ccdpf.attribute9,
557 ccdpf.attribute10,
558 ccdpf.attribute11,
559 ccdpf.attribute12,
560 ccdpf.attribute13,
561 ccdpf.attribute14,
562 ccdpf.attribute15,
563 ccdpf.last_update_date,
564 ccdpf.last_updated_by,
565 ccdpf.last_update_login,
566 ccdpf.creation_date,
567 ccdpf.created_by
568 FROM igc_cc_det_pf ccdpf
569 WHERE cc_acct_line_id = p_cc_acct_line_id
570 AND ccdpf.cc_det_pf_entered_amt <> IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id,ccdpf.cc_det_pf_line_num,
571 ccdpf.cc_acct_line_id); -- Bug 3856265
572
573 l_hdr_row_id VARCHAR2(18);
574 l_hist_hdr_row_id VARCHAR2(18);
575 l_acct_row_id VARCHAR2(18);
576 l_hist_acct_row_id VARCHAR2(18);
577 l_pf_row_id VARCHAR2(18);
578 l_hist_pf_row_id VARCHAR2(18);
579 l_action_row_id VARCHAR2(18);
580
581
582 l_new_cc_det_pf_func_amt igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
583 l_new_cc_acct_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
584
585 l_api_version CONSTANT NUMBER := 1.0;
586 l_init_msg_list VARCHAR2(1);
587 l_commit VARCHAR2(1);
588 l_validation_level NUMBER;
589 l_return_status VARCHAR2(1);
590 l_msg_count NUMBER;
591 l_msg_data VARCHAR2(2000);
592 G_FLAG VARCHAR2(1);
593
594 l_approval_status igc_cc_process_data.old_approval_status%TYPE;
595
596 l_Last_Updated_By NUMBER;
597 l_Last_Update_Login NUMBER;
598 l_Created_By NUMBER;
599
600 l_cc_acct_date igc_cc_headers.cc_acct_date%TYPE;
601 l_conversion_date igc_cc_headers.conversion_date%TYPE;
602 l_conversion_rate igc_cc_headers.conversion_rate%TYPE;
603 l_cc_version_num igc_cc_headers.cc_version_num%TYPE;
604 l_cc_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
605
606 l_cc_acct_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
607 l_cc_acct_encmbrnc_date igc_cc_acct_lines.cc_acct_encmbrnc_date%TYPE;
608 l_cc_acct_encmbrnc_amt igc_cc_acct_lines.cc_acct_encmbrnc_amt%TYPE ;
609
610 l_cc_det_pf_date igc_cc_det_pf.cc_det_pf_date%TYPE;
611 l_cc_det_pf_encmbrnc_date igc_cc_det_pf.cc_det_pf_encmbrnc_date%TYPE;
612 l_cc_det_pf_func_amt igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
613 l_cc_det_pf_encmbrnc_amt igc_cc_det_pf.cc_det_pf_encmbrnc_amt%TYPE;
614
615 -- bug 2043221 ssmales - added two variable declarations below
616 l_new_cc_func_withheld_amt igc_cc_acct_lines.cc_func_withheld_amt%TYPE;
617 l_cc_func_withheld_amt igc_cc_acct_lines.cc_func_withheld_amt%TYPE;
618
619
620 BEGIN
621
622 --GSCC Warnings Fixed
623
624 l_init_msg_list := FND_API.G_FALSE;
625 l_commit := FND_API.G_FALSE;
626 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
627 l_Last_Updated_By := FND_GLOBAL.USER_ID;
628 l_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
629 l_Created_By := FND_GLOBAL.USER_ID;
630
631 -- IF l_debug_mode = 'Y' THEN
632 -- Output_Debug (' IGCCREPB -- Reval Update Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
633 -- END IF;
634
635 -- bug 3199488, start block
636 IF (l_state_level >= l_debug_level) THEN
637 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg1',
638 ' IGCCREPB -- Reval Update Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
639 END IF;
640 -- bug 3199488, end block
641
642 SELECT *
643 INTO l_cc_headers_rec
644 FROM igc_cc_headers
645 WHERE cc_header_id = p_cc_header_id;
646
647
648 SELECT old_approval_status
649 INTO l_approval_status
650 FROM igc_cc_process_data
651 WHERE cc_header_id = p_cc_header_id AND
652 request_id = p_request_id ;
653
654
655 IF (p_validate_only = 'N')
656 THEN
657
658
659 /* Update Header History */
660 l_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 IGC_CC_HEADER_HISTORY_PKG.Insert_Row(
663 l_api_version,
664 l_init_msg_list,
665 l_commit,
666 l_validation_level,
667 l_return_status,
668 l_msg_count,
669 l_msg_data,
670 l_hist_hdr_row_id,
671 l_cc_headers_rec.CC_HEADER_ID,
672 l_cc_headers_rec.ORG_ID,
673 l_cc_headers_rec.CC_TYPE,
674 l_cc_headers_rec.CC_NUM,
675 l_cc_headers_rec.CC_VERSION_NUM,
676 'R',
677 l_cc_headers_rec.CC_STATE,
678 l_cc_headers_rec.PARENT_HEADER_ID,
679 l_cc_headers_rec.CC_CTRL_STATUS,
680 l_cc_headers_rec.CC_ENCMBRNC_STATUS,
681 l_approval_status,
682 l_cc_headers_rec.VENDOR_ID,
683 l_cc_headers_rec.VENDOR_SITE_ID,
684 l_cc_headers_rec.VENDOR_CONTACT_ID,
685 l_cc_headers_rec.TERM_ID,
686 l_cc_headers_rec.LOCATION_ID,
687 l_cc_headers_rec.SET_OF_BOOKS_ID,
688 l_cc_headers_rec.CC_ACCT_DATE,
689 l_cc_headers_rec.CC_DESC,
690 l_cc_headers_rec.CC_START_DATE,
691 l_cc_headers_rec.CC_END_DATE,
692 l_cc_headers_rec.CC_OWNER_USER_ID,
693 l_cc_headers_rec.CC_PREPARER_USER_ID,
694 l_cc_headers_rec.CURRENCY_CODE,
695 l_cc_headers_rec.CONVERSION_TYPE,
696 l_cc_headers_rec.CONVERSION_DATE,
697 l_cc_headers_rec.CONVERSION_RATE,
698 l_cc_headers_rec.LAST_UPDATE_DATE,
699 l_cc_headers_rec.LAST_UPDATED_BY,
700 l_cc_headers_rec.LAST_UPDATE_LOGIN,
701 l_cc_headers_rec.CREATED_BY,
702 l_cc_headers_rec.CREATION_DATE,
703 l_cc_headers_rec.WF_ITEM_TYPE,
704 l_cc_headers_rec.WF_ITEM_KEY,
705 l_cc_headers_rec.CC_CURRENT_USER_ID,
706 l_cc_headers_rec.ATTRIBUTE1,
707 l_cc_headers_rec.ATTRIBUTE2,
708 l_cc_headers_rec.ATTRIBUTE3,
709 l_cc_headers_rec.ATTRIBUTE4,
710 l_cc_headers_rec.ATTRIBUTE5,
711 l_cc_headers_rec.ATTRIBUTE6,
712 l_cc_headers_rec.ATTRIBUTE7,
713 l_cc_headers_rec.ATTRIBUTE8,
714 l_cc_headers_rec.ATTRIBUTE9,
715 l_cc_headers_rec.ATTRIBUTE10,
716 l_cc_headers_rec.ATTRIBUTE11,
717 l_cc_headers_rec.ATTRIBUTE12,
718 l_cc_headers_rec.ATTRIBUTE13,
719 l_cc_headers_rec.ATTRIBUTE14,
720 l_cc_headers_rec.ATTRIBUTE15,
721 --bug 4137743 changed the position
722 l_cc_headers_rec.CONTEXT,
723 -- bug 2043221 ssmales - added Guarantee Flag argument in line below
724 l_cc_headers_rec.CC_GUARANTEE_FLAG,
725 G_FLAG);
726
727 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
728 THEN
729
730 l_EXCEPTION := NULL;
731 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_HST_INSERT');
732 l_EXCEPTION := FND_MESSAGE.GET;
733 p_message := l_exception;
734 p_err_header_id := l_cc_headers_rec.cc_header_id;
735 p_err_acct_line_id := NULL;
736 p_err_det_pf_line_id := NULL;
737
738 -- IF l_debug_mode = 'Y' THEN
739 -- Output_Debug (' IGCCREPB -- Reval Update Ends 9 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
740 -- END IF;
741
742 -- bug 3199488, start block
743 IF (l_state_level >= l_debug_level) THEN
744 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg2',
745 ' IGCCREPB -- Reval Update Ends 9 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
746 END IF;
747 -- bug 3199488, end block
748
749 RETURN 'F';
750
751 END IF;
752
753 OPEN c_acct_lines(l_cc_headers_rec.cc_header_id);
754 LOOP
755 FETCH c_acct_lines INTO l_cc_acct_lines_rec;
756 EXIT WHEN c_acct_lines%NOTFOUND;
757
758 /* Update Account Line History*/
759 l_return_status := FND_API.G_RET_STS_SUCCESS;
760
761 IGC_CC_ACCT_LINE_HISTORY_PKG.Insert_Row(
762 l_api_version ,
763 l_init_msg_list,
764 l_commit,
765 l_validation_level,
766 l_return_status,
767 l_msg_count,
768 l_msg_data,
769 l_hist_acct_row_id,
770 l_cc_acct_lines_rec.CC_Acct_Line_Id,
771 l_cc_acct_lines_rec.CC_Header_Id,
772 l_cc_acct_lines_rec.Parent_Header_Id,
773 l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
774 l_cc_acct_lines_rec.CC_Acct_Line_Num,
775 l_cc_headers_rec.cc_version_num,
776 'U',
777 l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
778 l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
779 l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
780 l_cc_acct_lines_rec.CC_Acct_Func_Amt,
781 l_cc_acct_lines_rec.CC_Acct_Desc ,
782 l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
783 l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
784 l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
785 l_cc_acct_lines_rec.Tax_Id,
786 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Amt,
787 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Date,
788 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
789 l_cc_acct_lines_rec.Project_Id,
790 l_cc_acct_lines_rec.Task_Id,
791 l_cc_acct_lines_rec.Expenditure_Type,
792 l_cc_acct_lines_rec.Expenditure_Org_Id,
793 l_cc_acct_lines_rec.Expenditure_Item_Date,
794 l_cc_acct_lines_rec.Last_Update_Date,
795 l_cc_acct_lines_rec.Last_Updated_By,
796 l_cc_acct_lines_rec.Last_Update_Login ,
797 l_cc_acct_lines_rec.Creation_Date ,
798 l_cc_acct_lines_rec.Created_By ,
799 l_cc_acct_lines_rec.Attribute1,
800 l_cc_acct_lines_rec.Attribute2,
801 l_cc_acct_lines_rec.Attribute3,
802 l_cc_acct_lines_rec.Attribute4,
803 l_cc_acct_lines_rec.Attribute5,
804 l_cc_acct_lines_rec.Attribute6,
805 l_cc_acct_lines_rec.Attribute7,
806 l_cc_acct_lines_rec.Attribute8,
807 l_cc_acct_lines_rec.Attribute9,
808 l_cc_acct_lines_rec.Attribute10 ,
809 l_cc_acct_lines_rec.Attribute11,
810 l_cc_acct_lines_rec.Attribute12,
811 l_cc_acct_lines_rec.Attribute13,
812 l_cc_acct_lines_rec.Attribute14,
813 l_cc_acct_lines_rec.Attribute15,
814 l_cc_acct_lines_rec.Context,
815 -- bug 2043221 ssmales - added argument for Ent Withheld and Func Withheld amts in 2 lines below
816 l_cc_acct_lines_rec.CC_Func_Withheld_Amt,
817 l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
818 G_FLAG,
819 l_cc_acct_lines_rec.tax_classif_code -- added for Bug 6472296 Ebtax uptake for cc
820 );
821
822 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
823 THEN
824 l_EXCEPTION := NULL;
825 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
826 l_EXCEPTION := FND_MESSAGE.GET;
827 p_message := l_exception;
828 p_err_header_id := l_cc_headers_rec.cc_header_id;
829 p_err_acct_line_id := NULL;
830 p_err_det_pf_line_id := NULL;
831
832 -- IF l_debug_mode = 'Y' THEN
833 -- Output_Debug (' IGCCREPB -- Reval Update Ends 8 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
834 -- END IF;
835
836 -- bug 3199488, start block
837 IF (l_state_level >= l_debug_level) THEN
838 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg3',
839 ' IGCCREPB -- Reval Update Ends 8 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
840 END IF;
841 -- bug 3199488, end block
842
843 RETURN 'F';
844 END IF;
845
846 l_new_cc_acct_func_amt := 0;
847
848 l_new_cc_acct_func_amt :=
849 l_cc_acct_lines_rec.cc_acct_func_amt +
850 ( ( (l_cc_acct_lines_rec.cc_acct_func_amt -
851 l_cc_acct_lines_rec.cc_acct_func_billed_amt) / l_cc_headers_rec.conversion_rate
852 ) * (p_rate - l_cc_headers_rec.conversion_rate)
853 );
854
855 l_cc_acct_func_amt := l_new_cc_acct_func_amt;
856
857 -- bug 2043221 ssmales - start block
858
859 l_new_cc_func_withheld_amt := 0;
860 l_new_cc_func_withheld_amt := (l_cc_acct_lines_rec.cc_func_withheld_amt * p_rate) / l_cc_headers_rec.conversion_rate;
861 l_cc_func_withheld_amt := l_new_cc_func_withheld_amt;
862
863 -- bug 2043221 ssmales - end block
864
865
866 /* Update cc_acct_encmbrnc_date,cc_acct_encmbrnc_amt depending on budgetary control set up */
867
868 IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
869 AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
870 ) OR
871 ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
872 AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
873 )
874 THEN
875 l_cc_acct_encmbrnc_date := p_rate_date;
876 l_cc_acct_encmbrnc_amt := (l_cc_acct_lines_rec.cc_acct_entered_amt * p_rate);
877 END IF;
878
879 SELECT rowid
880 INTO l_acct_row_id
881 FROM igc_cc_acct_lines
882 WHERE cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
883
884 IGC_CC_ACCT_LINES_PKG.Update_Row(
885 l_api_version ,
886 l_init_msg_list,
887 l_commit,
888 l_validation_level,
889 l_return_status,
890 l_msg_count,
891 l_msg_data,
892 l_acct_row_id,
893 l_cc_acct_lines_rec.CC_Acct_Line_Id,
894 l_cc_acct_lines_rec.CC_Header_Id,
895 l_cc_acct_lines_rec.Parent_Header_Id,
896 l_cc_acct_lines_rec.Parent_Acct_Line_Id ,
897 l_cc_acct_lines_rec.CC_Charge_Code_Combination_Id,
898 l_cc_acct_lines_rec.CC_Acct_Line_Num,
899 l_cc_acct_lines_rec.CC_Budget_Code_Combination_Id,
900 l_cc_acct_lines_rec.CC_Acct_Entered_Amt ,
901 l_cc_acct_func_amt,
902 l_cc_acct_lines_rec.CC_Acct_Desc ,
903 l_cc_acct_lines_rec.CC_Acct_Billed_Amt ,
904 l_cc_acct_lines_rec.CC_Acct_Unbilled_Amt,
905 l_cc_acct_lines_rec.CC_Acct_Taxable_Flag,
906 l_cc_acct_lines_rec.Tax_Id,
907 l_cc_acct_encmbrnc_amt,
908 l_cc_acct_encmbrnc_date,
909 l_cc_acct_lines_rec.CC_Acct_Encmbrnc_Status,
910 l_cc_acct_lines_rec.Project_Id,
911 l_cc_acct_lines_rec.Task_Id,
912 l_cc_acct_lines_rec.Expenditure_Type,
913 l_cc_acct_lines_rec.Expenditure_Org_Id,
914 l_cc_acct_lines_rec.Expenditure_Item_Date,
915 sysdate,
916 l_Last_Updated_By,
917 l_Last_Update_Login ,
918 l_cc_acct_lines_rec.Creation_Date ,
919 l_cc_acct_lines_rec.Created_By ,
920 l_cc_acct_lines_rec.Attribute1,
921 l_cc_acct_lines_rec.Attribute2,
922 l_cc_acct_lines_rec.Attribute3,
923 l_cc_acct_lines_rec.Attribute4,
924 l_cc_acct_lines_rec.Attribute5,
925 l_cc_acct_lines_rec.Attribute6,
926 l_cc_acct_lines_rec.Attribute7,
927 l_cc_acct_lines_rec.Attribute8,
928 l_cc_acct_lines_rec.Attribute9,
929 l_cc_acct_lines_rec.Attribute10 ,
930 l_cc_acct_lines_rec.Attribute11,
931 l_cc_acct_lines_rec.Attribute12,
932 l_cc_acct_lines_rec.Attribute13,
933 l_cc_acct_lines_rec.Attribute14,
934 l_cc_acct_lines_rec.Attribute15,
935 l_cc_acct_lines_rec.Context,
936 l_cc_func_withheld_amt,
937 l_cc_acct_lines_rec.CC_Ent_Withheld_Amt,
938 G_FLAG,
939 l_cc_acct_lines_rec.tax_classif_code);
940
941
942 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
943 THEN
944 l_EXCEPTION := NULL;
945 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
946 l_EXCEPTION := FND_MESSAGE.GET;
947 p_message := l_exception;
948 p_err_header_id := l_cc_headers_rec.cc_header_id;
949 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
950 p_err_det_pf_line_id := NULL;
951
952 -- IF l_debug_mode = 'Y' THEN
953 -- Output_Debug (' IGCCREPB -- Reval Update Ends 7 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
954 -- END IF;
955
956 -- bug 3199488, start block
957 IF (l_state_level >= l_debug_level) THEN
958 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg4',
959 ' IGCCREPB -- Reval Update Ends 7 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
960 END IF;
961 -- bug 3199488, end block
962
963 RETURN 'F';
964 END IF;
965
966
967 OPEN c_pf_lines(l_cc_acct_lines_rec.cc_acct_line_id);
968 LOOP
969 FETCH c_pf_lines INTO l_cc_pmt_fcst_rec;
970 EXIT WHEN c_pf_lines%NOTFOUND;
971
972 /* Update PF Line History */
973
974 l_return_status := FND_API.G_RET_STS_SUCCESS;
975
976 IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
977 l_api_version,
978 l_init_msg_list,
979 l_commit,
980 l_validation_level,
981 l_return_status,
982 l_msg_count,
983 l_msg_data,
984 l_hist_pf_row_id,
985 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
986 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
987 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
988 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
989 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
990 l_cc_headers_rec.cc_version_num,
991 'U',
992 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
993 l_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
994 l_cc_pmt_fcst_rec.CC_Det_PF_Date,
995 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
996 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
997 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
998 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
999 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1000 l_cc_pmt_fcst_rec.Last_Update_Date,
1001 l_cc_pmt_fcst_rec.Last_Updated_By,
1002 l_cc_pmt_fcst_rec.Last_Update_Login,
1003 l_cc_pmt_fcst_rec.Creation_Date,
1004 l_cc_pmt_fcst_rec.Created_By,
1005 l_cc_pmt_fcst_rec.Attribute1,
1006 l_cc_pmt_fcst_rec.Attribute2,
1007 l_cc_pmt_fcst_rec.Attribute3,
1008 l_cc_pmt_fcst_rec.Attribute4,
1009 l_cc_pmt_fcst_rec.Attribute5,
1010 l_cc_pmt_fcst_rec.Attribute6,
1011 l_cc_pmt_fcst_rec.Attribute7,
1012 l_cc_pmt_fcst_rec.Attribute8,
1013 l_cc_pmt_fcst_rec.Attribute9,
1014 l_cc_pmt_fcst_rec.Attribute10,
1015 l_cc_pmt_fcst_rec.Attribute11,
1016 l_cc_pmt_fcst_rec.Attribute12,
1017 l_cc_pmt_fcst_rec.Attribute13,
1018 l_cc_pmt_fcst_rec.Attribute14,
1019 l_cc_pmt_fcst_rec.Attribute15,
1020 l_cc_pmt_fcst_rec.Context,
1021 G_FLAG );
1022
1023 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1024 THEN
1025
1026 l_EXCEPTION := NULL;
1027 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_HST_INSERT');
1028 l_EXCEPTION := FND_MESSAGE.GET;
1029 p_message := l_exception;
1030 p_err_header_id := l_cc_headers_rec.cc_header_id;
1031 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1032 p_err_det_pf_line_id := l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id;
1033
1034 -- IF l_debug_mode = 'Y' THEN
1035 -- Output_Debug (' IGCCREPB -- Reval Update Ends 6 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1036 -- END IF;
1037
1038 -- bug 3199488, start block
1039 IF (l_state_level >= l_debug_level) THEN
1040 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg5',
1041 ' IGCCREPB -- Reval Update Ends 6 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1042 END IF;
1043 -- bug 3199488, end block
1044
1045 RETURN 'F';
1046 END IF;
1047
1048
1049 l_new_cc_det_pf_func_amt :=
1050 l_cc_pmt_fcst_rec.cc_det_pf_func_amt +
1051 ( ( ( l_cc_pmt_fcst_rec.cc_det_pf_func_amt -
1052 l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt) / l_cc_headers_rec.conversion_rate
1053 ) * (p_rate - l_cc_headers_rec.conversion_rate )
1054 );
1055
1056 IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
1057 AND (p_sbc_on = TRUE) AND (p_prov_enc_on = TRUE)
1058 ) OR
1059 ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
1060 AND (p_sbc_on = TRUE) AND (p_conf_enc_on = TRUE)
1061 )
1062 THEN
1063 l_cc_det_pf_encmbrnc_amt := (l_cc_pmt_fcst_rec.cc_det_pf_entered_amt * p_rate);
1064 /* Bug fix for 1634793 */
1065 IF (l_cc_headers_rec.cc_type <> 'R')
1066 THEN
1067 IF (l_cc_pmt_fcst_rec.cc_det_pf_date <= p_rate_date)
1068 THEN
1069 l_cc_det_pf_date := p_rate_date;
1070 l_cc_det_pf_encmbrnc_date := p_rate_date;
1071 END IF;
1072
1073 IF (l_cc_pmt_fcst_rec.cc_det_pf_date > p_rate_date)
1074 THEN
1075 l_cc_det_pf_date := l_cc_pmt_fcst_rec.cc_det_pf_date;
1076 l_cc_det_pf_encmbrnc_date := l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date;
1077 END IF;
1078 ELSIF (l_cc_headers_rec.cc_type = 'R')
1079 THEN
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 END IF;
1083 ELSE
1084 l_cc_det_pf_date := l_cc_pmt_fcst_rec.cc_det_pf_date;
1085 l_cc_det_pf_encmbrnc_date := l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date;
1086 END IF;
1087
1088 l_cc_det_pf_func_amt := l_new_cc_det_pf_func_amt;
1089
1090 SELECT rowid
1091 INTO l_pf_row_id
1092 FROM igc_cc_det_pf
1093 WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
1094
1095 IGC_CC_DET_PF_PKG.Update_Row(
1096 l_api_version,
1097 l_init_msg_list,
1098 l_commit,
1099 l_validation_level,
1100 l_return_status,
1101 l_msg_count,
1102 l_msg_data,
1103 l_pf_row_id,
1104 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
1105 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
1106 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
1107 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
1108 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
1109 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
1110 l_cc_det_pf_func_amt,
1111 l_cc_det_pf_date,
1112 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
1113 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
1114 l_cc_det_pf_encmbrnc_amt,
1115 l_cc_det_pf_encmbrnc_date,
1116 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1117 sysdate,
1118 l_Last_Updated_By,
1119 l_last_Update_Login,
1120 l_cc_pmt_fcst_rec.Creation_Date,
1121 l_cc_pmt_fcst_rec.Created_By,
1122 l_cc_pmt_fcst_rec.Attribute1,
1123 l_cc_pmt_fcst_rec.Attribute2,
1124 l_cc_pmt_fcst_rec.Attribute3,
1125 l_cc_pmt_fcst_rec.Attribute4,
1126 l_cc_pmt_fcst_rec.Attribute5,
1127 l_cc_pmt_fcst_rec.Attribute6,
1128 l_cc_pmt_fcst_rec.Attribute7,
1129 l_cc_pmt_fcst_rec.Attribute8,
1130 l_cc_pmt_fcst_rec.Attribute9,
1131 l_cc_pmt_fcst_rec.Attribute10,
1132 l_cc_pmt_fcst_rec.Attribute11,
1133 l_cc_pmt_fcst_rec.Attribute12,
1134 l_cc_pmt_fcst_rec.Attribute13,
1135 l_cc_pmt_fcst_rec.Attribute14,
1136 l_cc_pmt_fcst_rec.Attribute15,
1137 l_cc_pmt_fcst_rec.Context,
1138 G_FLAG );
1139
1140
1141 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1142 THEN
1143
1144 l_EXCEPTION := NULL;
1145 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_UPDATE');
1146 l_EXCEPTION := FND_MESSAGE.GET;
1147 p_message := l_exception;
1148 p_err_header_id := l_cc_headers_rec.cc_header_id;
1149 p_err_acct_line_id := l_cc_acct_lines_rec.CC_ACCT_LINE_ID;
1150 p_err_det_pf_line_id := l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id;
1151
1152 -- IF l_debug_mode = 'Y' THEN
1153 -- Output_Debug (' IGCCREPB -- Reval Update Ends 5 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1154 -- END IF;
1155
1156 -- bug 3199488, start block
1157 IF (l_state_level >= l_debug_level) THEN
1158 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg6',
1159 ' IGCCREPB -- Reval Update Ends 5 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1160 END IF;
1161 -- bug 3199488, end block
1162
1163 RETURN 'F';
1164 END IF;
1165
1166 END LOOP;
1167
1168 CLOSE c_pf_lines;
1169
1170 END LOOP;
1171
1172 CLOSE c_acct_lines;
1173
1174 END IF;
1175
1176 IF (p_validate_only = 'N')
1177 THEN
1178 /* Fix for bug 1498700
1179 Update Accounting date at CC Header if Account Date <= rate_date
1180 */
1181
1182 IF ( ( (l_cc_headers_rec.cc_state = 'PR') OR (l_cc_headers_rec.cc_state = 'CL') )
1183 AND (p_cbc_on = TRUE) AND (p_prov_enc_on = TRUE)
1184 ) OR
1185 ( ( (l_cc_headers_rec.cc_state = 'CM') OR (l_cc_headers_rec.cc_state = 'CT') )
1186 AND (p_cbc_on = TRUE) AND (p_conf_enc_on = TRUE)
1187 )
1188 THEN
1189
1190 IF (l_cc_headers_rec.cc_acct_date IS NOT NULL)
1191 THEN
1192 IF (l_cc_headers_rec.cc_acct_date <= p_rate_date)
1193 THEN
1194 l_cc_acct_date := p_rate_date;
1195 ELSIF (l_cc_headers_rec.cc_acct_date > p_rate_date)
1196 THEN
1197 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1198 END IF;
1199 END IF;
1200
1201 IF (l_cc_headers_rec.cc_acct_date IS NULL)
1202 THEN
1203 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1204 END IF;
1205 ELSE
1206
1207 l_cc_acct_date := l_cc_headers_rec.cc_acct_date;
1208 END IF;
1209
1210 l_conversion_date := p_rate_date;
1211 l_conversion_rate := p_rate;
1212 l_cc_version_num := l_cc_headers_rec.cc_version_num + 1;
1213 l_cc_apprvl_status := l_approval_status;
1214
1215 SELECT rowid
1216 INTO l_hdr_row_id
1217 FROM igc_cc_headers
1218 WHERE CC_HEADER_ID = l_cc_headers_rec.cc_header_id;
1219
1220 IGC_CC_HEADERS_PKG.Update_Row(
1221 l_api_version,
1222 l_init_msg_list,
1223 l_commit,
1224 l_validation_level,
1225 l_return_status,
1226 l_msg_count,
1227 l_msg_data,
1228 l_hdr_row_id,
1229 l_cc_headers_rec.CC_HEADER_ID,
1230 l_cc_headers_rec.ORG_ID,
1231 l_cc_headers_rec.CC_TYPE,
1232 l_cc_headers_rec.CC_NUM,
1233 l_cc_version_num,
1234 l_cc_headers_rec.PARENT_HEADER_ID,
1235 l_cc_headers_rec.CC_STATE,
1236 l_cc_headers_rec.CC_CTRL_STATUS,
1237 l_cc_headers_rec.CC_ENCMBRNC_STATUS,
1238 l_cc_apprvl_status,
1239 l_cc_headers_rec.VENDOR_ID,
1240 l_cc_headers_rec.VENDOR_SITE_ID,
1241 l_cc_headers_rec.VENDOR_CONTACT_ID,
1242 l_cc_headers_rec.TERM_ID,
1243 l_cc_headers_rec.LOCATION_ID,
1244 l_cc_headers_rec.SET_OF_BOOKS_ID,
1245 l_cc_acct_date,
1246 l_cc_headers_rec.CC_DESC,
1247 l_cc_headers_rec.CC_START_DATE,
1248 l_cc_headers_rec.CC_END_DATE,
1249 l_cc_headers_rec.CC_OWNER_USER_ID,
1250 l_cc_headers_rec.CC_PREPARER_USER_ID,
1251 l_cc_headers_rec.CURRENCY_CODE,
1252 l_cc_headers_rec.CONVERSION_TYPE,
1253 l_conversion_date,
1254 l_conversion_rate,
1255 sysdate,
1256 l_LAST_UPDATED_BY,
1257 l_LAST_UPDATE_LOGIN,
1258 l_cc_headers_rec.CREATED_BY,
1259 l_cc_headers_rec.CREATION_DATE,
1260 l_cc_headers_rec.CC_CURRENT_USER_ID,
1261 l_cc_headers_rec.WF_ITEM_TYPE,
1262 l_cc_headers_rec.WF_ITEM_KEY,
1263 l_cc_headers_rec.ATTRIBUTE1,
1264 l_cc_headers_rec.ATTRIBUTE2,
1265 l_cc_headers_rec.ATTRIBUTE3,
1266 l_cc_headers_rec.ATTRIBUTE4,
1267 l_cc_headers_rec.ATTRIBUTE5,
1268 l_cc_headers_rec.ATTRIBUTE6,
1269 l_cc_headers_rec.ATTRIBUTE7,
1270 l_cc_headers_rec.ATTRIBUTE8,
1271 l_cc_headers_rec.ATTRIBUTE9,
1272 l_cc_headers_rec.ATTRIBUTE10,
1273 l_cc_headers_rec.ATTRIBUTE11,
1274 l_cc_headers_rec.ATTRIBUTE12,
1275 l_cc_headers_rec.ATTRIBUTE13,
1276 l_cc_headers_rec.ATTRIBUTE14,
1277 l_cc_headers_rec.ATTRIBUTE15,
1278 l_cc_headers_rec.CONTEXT,
1279 l_cc_headers_rec.CC_GUARANTEE_FLAG,
1280 G_FLAG);
1281
1282 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1283 THEN
1284
1285 l_EXCEPTION := NULL;
1286 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADERS_UPDATE');
1287 l_EXCEPTION := FND_MESSAGE.GET;
1288 p_message := l_exception;
1289 p_err_header_id := l_cc_headers_rec.cc_header_id;
1290 p_err_acct_line_id := NULL;
1291 p_err_det_pf_line_id := NULL;
1292
1293 -- IF l_debug_mode = 'Y' THEN
1294 -- Output_Debug (' IGCCREPB -- Reval Update Ends 4 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1295 -- END IF;
1296
1297 -- bug 3199488, start block
1298 IF (l_state_level >= l_debug_level) THEN
1299 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg7',
1300 ' IGCCREPB -- Reval Update Ends 4 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1301 END IF;
1302 -- bug 3199488, end block
1303
1304 RETURN 'F';
1305 END IF;
1306
1307
1308 ELSIF (p_validate_only = 'Y')
1309 THEN
1310 UPDATE igc_cc_headers
1311 SET cc_apprvl_status = l_approval_status
1312 WHERE cc_header_id = p_cc_header_id;
1313 END IF;
1314
1315
1316
1317 /* Update Corresponding PO */
1318
1319 IF ( (l_cc_headers_rec.cc_type = 'S') OR
1320 (l_cc_headers_rec.cc_type = 'R') ) AND
1321 /* Changed l_cc_headers_rec.cc_apprvl_status to l_approval_status in the following line to fix bug 1613811 */
1322 ( ( (l_cc_headers_rec.cc_state = 'CM') AND (l_approval_status = 'AP') ) OR
1323 (l_cc_headers_rec.cc_state = 'CT') )
1324 THEN
1325 l_return_status := FND_API.G_RET_STS_SUCCESS;
1326
1327 IGC_CC_PO_INTERFACE_PKG.Convert_CC_TO_PO(1.0,
1328 FND_API.G_FALSE,
1329 FND_API.G_TRUE,
1330 FND_API.G_VALID_LEVEL_NONE,
1331 l_return_status,
1332 l_msg_count,
1333 l_msg_data,
1334 l_cc_headers_rec.cc_header_id);
1335
1336 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1337 THEN
1338 -- IF l_debug_mode = 'Y' THEN
1339 -- Output_Debug (' IGCCREPB -- Reval Update Ends 3 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1340 -- END IF;
1341
1342 -- bug 3199488, start block
1343 IF (l_state_level >= l_debug_level) THEN
1344 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg8',
1345 ' IGCCREPB -- Reval Update Ends 3 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1346 END IF;
1347 -- bug 3199488, end block
1348
1349 RETURN 'F';
1350 END IF;
1351 END IF;
1352
1353 /* added following code to remove hard coded message */
1354 /* begin */
1355 l_action_hist_msg := NULL;
1356 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_ACT_HIST_MSG');
1357 FND_MESSAGE.SET_TOKEN ('REVAL_RATE', p_rate);
1358 FND_MESSAGE.SET_TOKEN ('REVAL_DATE_RATE', to_char(p_rate_date, 'DD-MON-YYYY'));
1359 l_action_hist_msg := FND_MESSAGE.GET;
1360 /*end */
1361
1362 IGC_CC_ACTIONS_PKG.Insert_Row(
1363 l_api_version,
1364 l_init_msg_list,
1365 l_commit,
1366 l_validation_level,
1367 l_return_status,
1368 l_msg_count,
1369 l_msg_data,
1370 l_action_row_id,
1371 l_cc_headers_rec.CC_HEADER_ID,
1372 l_cc_version_num,
1373 'RP',
1374 l_cc_headers_rec.CC_STATE,
1375 l_cc_headers_rec.CC_CTRL_STATUS,
1376 l_cc_apprvl_status,
1377 l_action_hist_msg,
1378 Sysdate,
1379 l_Last_Updated_By,
1380 l_Last_Update_Login,
1381 Sysdate,
1382 l_Created_By);
1383
1384 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1385 THEN
1386
1387 l_EXCEPTION := NULL;
1388 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACTION_HST_INSERT');
1389 l_EXCEPTION := FND_MESSAGE.GET;
1390 p_message := l_exception;
1391 p_err_header_id := l_cc_headers_rec.cc_header_id;
1392 p_err_acct_line_id := NULL;
1393 p_err_det_pf_line_id := NULL;
1394
1395 -- IF l_debug_mode = 'Y' THEN
1396 -- Output_Debug (' IGCCREPB -- Reval Update Ends 1 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1397 -- END IF;
1398
1399 -- bug 3199488, start block
1400 IF (l_state_level >= l_debug_level) THEN
1401 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg9',
1402 ' IGCCREPB -- Reval Update Ends 1 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1403 END IF;
1404 -- bug 3199488, end block
1405
1406 RETURN 'F';
1407 END IF;
1408
1409 /* Update validation status, in temporary table*/
1410 UPDATE igc_cc_process_data
1411 SET
1412 processed = 'Y'
1413 WHERE request_id = p_request_id AND
1414 cc_header_id = P_cc_header_id ;
1415
1416 RETURN 'P';
1417 -- IF l_debug_mode = 'Y' THEN
1418 -- Output_Debug (' IGCCREPB -- Reval Update Ends 2 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1419 -- END IF;
1420
1421 -- bug 3199488, start block
1422 IF (l_state_level >= l_debug_level) THEN
1423 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg10',
1424 ' IGCCREPB -- Reval Update Ends 2 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1425 END IF;
1426 -- bug 3199488, end block
1427
1428 EXCEPTION
1429 WHEN OTHERS
1430 THEN
1431 -- IF l_debug_mode = 'Y' THEN
1432 -- Output_Debug (' IGCCREPB -- Reval Update Ends 10 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1433 -- END IF;
1434
1435 -- bug 3199488, start block
1436 IF (l_state_level >= l_debug_level) THEN
1437 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.reval_update.Msg11',
1438 ' IGCCREPB -- Reval Update Ends 10 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1439 END IF;
1440 -- bug 3199488, end block
1441
1442 RETURN 'F';
1443
1444 END reval_update;
1445
1446 /* Commented out as per bug 3199488
1447 --
1448 -- Output_Debug Procedure is the Generic procedure designed for outputting debug
1449 -- information that is required from this procedure.
1450 --
1451 -- Parameters :
1452 --
1453 -- p_debug_msg ==> Record to be output into the debug log file.
1454 --
1455 PROCEDURE Output_Debug (
1456 p_debug_msg IN VARCHAR2
1457 ) IS
1458
1459 -- Constants :
1460
1461 l_prod VARCHAR2(3);
1462 l_sub_comp VARCHAR2(6);
1463 l_profile_name VARCHAR2(255);
1464 l_Return_Status VARCHAR2(1);
1465 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';
1466
1467 BEGIN
1468
1469 --GSCC Warnings fixed
1470 l_prod := 'IGC';
1471 l_sub_comp := 'CC_RVL';
1472 l_profile_name := 'IGC_DEBUG_LOG_DIRECTORY';
1473
1474 IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
1475 p_profile_log_name => l_profile_name,
1476 p_prod => l_prod,
1477 p_sub_comp => l_sub_comp,
1478 p_filename_val => NULL,
1479 x_Return_Status => l_Return_Status
1480 );
1481
1482 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1483 raise FND_API.G_EXC_ERROR;
1484 END IF;
1485
1486 RETURN;
1487
1488 -- --------------------------------------------------------------------
1489 -- Exception handler section for the Output_Debug procedure.
1490 -- --------------------------------------------------------------------
1491 EXCEPTION
1492
1493 WHEN FND_API.G_EXC_ERROR THEN
1494 RETURN;
1495
1496 WHEN OTHERS THEN
1497 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1498 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1499 END IF;
1500 RETURN;
1501
1502 END Output_Debug;
1503 */
1504
1505 PROCEDURE populate_errors(p_cc_header_id NUMBER,
1506 p_process_phase VARCHAR2,
1507 p_currency_code VARCHAR2,
1508 p_rate_type VARCHAR2,
1509 p_sob_id NUMBER,
1510 p_org_id NUMBER,
1511 p_request_id NUMBER)
1512 IS
1513 l_message igc_cc_process_exceptions.exception_reason%TYPE;
1514 BEGIN
1515 /* Update validation_status to 'F' in temporary table for releases */
1516
1517 -- IF l_debug_mode = 'Y' THEN
1518 -- Output_Debug (' IGCCREPB -- Populate Error Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1519 -- END IF;
1520
1521 -- bug 3199488, start block
1522 IF (l_state_level >= l_debug_level) THEN
1523 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.populate_errors.Msg1',
1524 ' IGCCREPB -- Populate Error Begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1525 END IF;
1526 -- bug 3199488, end block
1527
1528 UPDATE igc_cc_process_data a
1529 SET a.validation_status = 'F'
1530 WHERE a.cc_header_id = p_cc_header_id AND
1531 request_id = p_request_id;
1532
1533 l_message := NULL;
1534 FND_MESSAGE.SET_NAME('IGC','IGC_CC_AVAILABLE_AMT_EXCEEDED');
1535 l_message := FND_MESSAGE.GET;
1536
1537 INSERT
1538 INTO igc_cc_process_exceptions
1539 (process_type,
1540 process_phase,
1541 cc_header_id,
1542 cc_acct_line_id,
1543 cc_det_pf_line_id,
1544 exception_reason,
1545 org_id,
1546 set_of_books_id,
1547 request_id)
1548 SELECT 'R',
1549 p_process_phase,
1550 b.cc_header_id,
1551 NULL,
1552 NULL,
1553 l_message,
1554 p_org_id,
1555 p_sob_id,
1556 p_request_id
1557 FROM igc_cc_headers b,
1558 igc_cc_process_data a
1559 WHERE
1560 NVL(b.parent_header_id,0) = p_cc_header_id AND
1561 b.cc_header_id = a.cc_header_id AND
1562 b.currency_code = p_currency_code AND
1563 b.conversion_type = p_rate_type AND
1564 a.request_id = p_request_id;
1565 -- IF l_debug_mode = 'Y' THEN
1566 -- Output_Debug (' IGCCREPB -- Populate Error Ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1567 -- END IF;
1568
1569 -- bug 3199488, start block
1570 IF (l_state_level >= l_debug_level) THEN
1571 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.populate_errors.Msg2',
1572 ' IGCCREPB -- Populate Error Ends ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1573 END IF;
1574 -- bug 3199488, end block
1575
1576 END populate_errors;
1577
1578
1579 PROCEDURE revalue_main( ERRBUF OUT NOCOPY VARCHAR2,
1580 RETCODE OUT NOCOPY VARCHAR2,
1581 p_process_phase IN VARCHAR2,
1582 p_currency_code IN VARCHAR2,
1583 p_rate_type IN VARCHAR2,
1584 p_rate_date IN VARCHAR2,
1585 p_rate IN VARCHAR2,
1586 p_cc_header_id IN NUMBER)
1587 IS
1588 l_cc_headers_rec igc_cc_headers%ROWTYPE;
1589 l_rel_cc_headers_rec igc_cc_headers%ROWTYPE;
1590 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
1591 l_rel_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
1592 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
1593 l_rel_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
1594 l_err_header_id NUMBER;
1595 l_err_acct_line_id NUMBER;
1596 l_err_det_pf_line_id NUMBER;
1597
1598 l_rate_date DATE;
1599
1600 l_budg_status BOOLEAN;
1601 l_validation_status VARCHAR2(1);
1602 l_curr_validation_status VARCHAR2(1);
1603 l_reservation_status VARCHAR2(1);
1604 l_processed VARCHAR2(1);
1605 l_validate_only VARCHAR2(1);
1606 l_process_flag VARCHAR2(1);
1607
1608 l_org_id NUMBER;
1609 l_sob_id NUMBER;
1610 /*Bug No : 6341012. SLA Uptake. l_sob_name is added to get SOB Name*/
1611 l_sob_name VARCHAR2(30);
1612
1613 l_process_data_count NUMBER;
1614 l_cc_count NUMBER;
1615 l_invalid_cc_count NUMBER;
1616 l_po_count NUMBER;
1617
1618 l_cc_cover_count NUMBER;
1619
1620 l_request_id2 NUMBER;
1621 l_request_id1 NUMBER;
1622
1623 l_lock_cc_po BOOLEAN;
1624 l_cover_not_found BOOLEAN;
1625 l_cc_not_found BOOLEAN;
1626 l_approval_status VARCHAR2(2);
1627
1628 l_message igc_cc_process_exceptions.exception_reason%TYPE;
1629 -- l_debug VARCHAR2(1);
1630
1631 l_currency_code gl_sets_of_books.currency_code%TYPE;
1632 l_sbc_on BOOLEAN;
1633 l_cbc_on BOOLEAN;
1634 l_prov_enc_on BOOLEAN;
1635 l_conf_enc_on BOOLEAN;
1636 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
1637 -- l_req_encumbrance_type_id NUMBER;
1638 -- l_purch_encumbrance_type_id NUMBER;
1639 -- l_cc_prov_enc_type_id NUMBER;
1640 -- l_cc_conf_enc_type_id NUMBER;
1641
1642 l_non_reval_acct_amt_total NUMBER;
1643 l_reval_acct_amt_total NUMBER;
1644 l_non_reval_pf_amt_total NUMBER;
1645 l_reval_pf_amt_total NUMBER;
1646 l_cover_acct_func_amt NUMBER;
1647 l_cover_pf_func_amt NUMBER;
1648 l_msg_count NUMBER;
1649 l_msg_data VARCHAR2(12000);
1650 l_error_text VARCHAR2(12000);
1651 l_usr_msg igc_cc_process_exceptions.exception_reason%TYPE;
1652
1653 -- Bug 2441322
1654 l_rate NUMBER;
1655
1656 -- 01/03/02, add code to check if CC is enabled for IGI
1657 l_option_name VARCHAR2(80);
1658 lv_message VARCHAR2(1000);
1659 -- Varibles used for xml report
1660 l_terr VARCHAR2(10):='US';
1661 l_lang VARCHAR2(10):='en';
1662 l_layout BOOLEAN;
1663 /* Cursor for fetching all contract commitments eligible for re-valuation */
1664
1665 CURSOR c_revalue_process_cc(p_process_phase VARCHAR2,
1666 p_sob_id NUMBER,
1667 p_org_id NUMBER,
1668 p_currency_code VARCHAR2,
1669 p_rate_type VARCHAR2,
1670 p_rate NUMBER,
1671 p_func_currency_code VARCHAR2,
1672 p_cc_header_id NUMBER)
1673 IS
1674
1675 SELECT *
1676 FROM igc_cc_headers a
1677 WHERE
1678 ( (a.cc_state = 'PR') OR
1679 (a.cc_state = 'CM') OR
1680 ( (a.cc_state = 'CT') AND (a.cc_apprvl_status <> 'AP')) OR
1681 ( (a.cc_state = 'CL') AND (a.cc_apprvl_status <> 'AP'))
1682 ) AND
1683 (
1684
1685 ( (a.org_id = p_org_id AND
1686 a.set_of_books_id = p_sob_id AND
1687 a.currency_code = p_currency_code AND
1688 ( p_process_phase = 'P' OR
1689 (p_process_phase = 'F' AND
1690 a.conversion_rate <> NVL(p_rate, -99999))
1691 ) AND
1692 a.conversion_type = p_rate_type AND
1693 ( (a.cc_type = 'C') OR
1694 (a.cc_type = 'S') OR
1695 ( (a.cc_type = 'R') AND
1696 EXISTS (SELECT 'x'
1697 FROM igc_cc_headers b
1698 WHERE b.cc_header_id = NVL(a.parent_header_id,0) AND
1699 b.currency_code = p_func_currency_code)
1700 )
1701 )
1702 ) AND
1703 p_cc_header_id IS NULL
1704 )
1705 OR
1706 ( (a.org_id = p_org_id AND
1707 a.set_of_books_id = p_sob_id AND
1708 a.currency_code = p_currency_code AND
1709 a.cc_header_id = NVL(p_cc_header_id,-999999) AND
1710 ( p_process_phase = 'P' OR
1711 (p_process_phase = 'F' AND
1712 a.conversion_rate <> NVL(p_rate, -99999))
1713 ) AND
1714 a.conversion_type = p_rate_type
1715 ) AND
1716 p_cc_header_id IS NOT NULL
1717 )
1718 );
1719
1720 /* Fetch the cover-relase both revalued data from temporary table */
1721 CURSOR c_cover_reval_data(p_request_id NUMBER)
1722 IS
1723 SELECT a.cc_header_id
1724 FROM igc_cc_process_data a ,
1725 igc_cc_headers b
1726 WHERE a.request_id = p_request_id AND
1727 a.cc_header_id = b.cc_header_id AND
1728 /* bug 1622969 */
1729 a.validation_status = 'I' AND
1730 /* bug 1622969 */
1731 b.cc_type = 'C';
1732
1733
1734 /* Fetch the cover-standard data from temporary table */
1735 CURSOR c_reval_data(p_request_id NUMBER)
1736 IS
1737 SELECT a.cc_header_id
1738 FROM igc_cc_process_data a ,
1739 igc_cc_headers b
1740 WHERE a.request_id = p_request_id AND
1741 a.cc_header_id = b.cc_header_id AND
1742 /* bug 1622969 */
1743 (a.validation_status = 'I' OR a.validation_status = 'P') AND
1744 /* bug 1622969 */
1745 (b.cc_type = 'C' OR b.cc_type = 'S');
1746
1747 CURSOR C_ALL_RELEASES1(p_cc_header_id NUMBER)
1748 IS
1749 SELECT *
1750 FROM igc_cc_headers
1751 WHERE NVL(parent_header_id,0) = p_cc_header_id;
1752
1753 CURSOR C_ALL_RELEASES(p_cc_header_id NUMBER)
1754 IS
1755 SELECT a.cc_header_id
1756 FROM igc_cc_headers a
1757 WHERE NVL(a.parent_header_id,0) = p_cc_header_id;
1758
1759 CURSOR C_RELEASES(p_cc_header_id NUMBER,
1760 p_currency_code VARCHAR2,
1761 p_rate_type VARCHAR2)
1762 IS
1763 SELECT a.cc_header_id
1764 FROM igc_cc_headers a
1765 WHERE NVL(a.parent_header_id,0) = p_cc_header_id AND
1766 a.currency_code = p_currency_code AND
1767 a.conversion_type = p_rate_type ;
1768
1769 CURSOR C_ACCT_LINES(p_cc_header_id NUMBER)
1770 IS
1771 -- Replaced the folllowing query with the one below for
1772 -- performance tuning fixes.
1773 -- The record definition of l_cc_acct_lines_rec is still based on
1774 -- view igc_cc_acct_lines_v. Instead of selecting from the view,
1775 -- select is being done from the base table, but all the columns
1776 -- as defined in the view are retained even though they are not used.
1777 -- This is just so that minimal change is made to the code.
1778 /*
1779 SELECT *
1780 FROM igc_cc_acct_lines_v
1781 WHERE cc_header_id = p_cc_header_id;
1782 */
1783
1784 SELECT ccal.ROWID,
1785 ccal.cc_header_id,
1786 NULL org_id,
1787 NULL cc_type,
1788 NULL cc_type_code,
1789 NULL cc_num,
1790 ccal.cc_acct_line_id,
1791 ccal.cc_acct_line_num,
1792 ccal.cc_acct_desc,
1793 ccal.parent_header_id,
1794 ccal.parent_acct_line_id,
1795 NULL parent_cc_acct_line_num,
1796 NULL cc_budget_acct_desc,
1797 ccal.cc_budget_code_combination_id,
1798 NULL cc_charge_acct_desc,
1799 ccal.cc_charge_code_combination_id,
1800 ccal.cc_acct_entered_amt,
1801 ccal.cc_acct_func_amt,
1802 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
1803 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
1804 ccal.cc_acct_encmbrnc_amt,
1805 ( 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,
1806 ccal.cc_acct_unbilled_amt,
1807 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
1808 NULL project_number ,
1809 ccal.project_id,
1810 NULL task_number,
1811 ccal.task_id,
1812 ccal.expenditure_type,
1813 NULL expenditure_org_name,
1814 ccal.expenditure_org_id,
1815 ccal.expenditure_item_date,
1816 ccal.cc_acct_taxable_flag,
1817 NULL tax_name,
1818 NULL tax_id, -- added for Bug 6472296 Ebtax uptake for cc
1819 ccal.cc_acct_encmbrnc_status,
1820 ccal.cc_acct_encmbrnc_date,
1821 ccal.context,
1822 ccal.attribute1,
1823 ccal.attribute2,
1824 ccal.attribute3,
1825 ccal.attribute4,
1826 ccal.attribute5,
1827 ccal.attribute6,
1828 ccal.attribute7,
1829 ccal.attribute8,
1830 ccal.attribute9,
1831 ccal.attribute10,
1832 ccal.attribute11,
1833 ccal.attribute12,
1834 ccal.attribute13,
1835 ccal.attribute14,
1836 ccal.attribute15,
1837 ccal.created_by,
1838 ccal.creation_date,
1839 ccal.last_updated_by,
1840 ccal.last_update_date,
1841 ccal.last_update_login,
1842 ccal.cc_func_withheld_amt,
1843 ccal.cc_ent_withheld_amt,
1844 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(ccal.cc_header_id,
1845 NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
1846 ccal.tax_classif_code -- added for Bug 6472296 Ebtax uptake for cc
1847 FROM igc_cc_acct_lines ccal
1848 WHERE cc_header_id = p_cc_header_id;
1849
1850 CURSOR C_PF_LINES(p_cc_acct_line_id NUMBER)
1851 IS
1852 -- SELECT *
1853 -- FROM igc_cc_det_pf_v
1854 -- WHERE cc_acct_line_id = p_cc_acct_line_id;
1855
1856 SELECT ccdpf.ROWID,
1857 ccdpf.cc_det_pf_line_id,
1858 ccdpf.cc_det_pf_line_num,
1859 NULL cc_acct_line_num,
1860 ccdpf.cc_acct_line_id,
1861 NULL parent_det_pf_line_num,
1862 ccdpf.parent_det_pf_line_id,
1863 ccdpf.parent_acct_line_id,
1864 ccdpf.cc_det_pf_entered_amt,
1865 ccdpf.cc_det_pf_func_amt,
1866 ccdpf.cc_det_pf_date,
1867 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,
1868 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,
1869 ccdpf.cc_det_pf_unbilled_amt,
1870 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,
1871 ccdpf.cc_det_pf_encmbrnc_amt,
1872 ( 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 ,
1873 ccdpf.cc_det_pf_encmbrnc_date,
1874 ccdpf.cc_det_pf_encmbrnc_status,
1875 ccdpf.context,
1876 ccdpf.attribute1,
1877 ccdpf.attribute2,
1878 ccdpf.attribute3,
1879 ccdpf.attribute4,
1880 ccdpf.attribute5,
1881 ccdpf.attribute6,
1882 ccdpf.attribute7,
1883 ccdpf.attribute8,
1884 ccdpf.attribute9,
1885 ccdpf.attribute10,
1886 ccdpf.attribute11,
1887 ccdpf.attribute12,
1888 ccdpf.attribute13,
1889 ccdpf.attribute14,
1890 ccdpf.attribute15,
1891 ccdpf.last_update_date,
1892 ccdpf.last_updated_by,
1893 ccdpf.last_update_login,
1894 ccdpf.creation_date,
1895 ccdpf.created_by
1896 FROM igc_cc_det_pf ccdpf
1897 WHERE cc_acct_line_id = p_cc_acct_line_id
1898 AND ccdpf.cc_det_pf_entered_amt <> IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id,ccdpf.cc_det_pf_line_num,
1899 ccdpf.cc_acct_line_id); -- Bug 3856265
1900
1901
1902 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
1903 l_rel_cc_header_id igc_cc_headers.cc_header_id%TYPE;
1904 l_cc_acct_line_id igc_cc_acct_lines.cc_acct_line_id%TYPE;
1905
1906 insert_data EXCEPTION;
1907 BEGIN
1908
1909 --GSCC Warnings Fixed
1910 l_process_data_count := 0;
1911 l_cc_count := 0;
1912 l_invalid_cc_count := 0;
1913 l_po_count := 0;
1914 l_cc_cover_count := 0;
1915 l_request_id2 := 0;
1916 l_request_id1 := 79000;
1917 l_lock_cc_po := FALSE;
1918 l_cover_not_found := FALSE;
1919 l_cc_not_found := FALSE;
1920 l_non_reval_acct_amt_total := 0;
1921 l_reval_acct_amt_total := 0;
1922 l_non_reval_pf_amt_total := 0;
1923 l_reval_pf_amt_total := 0;
1924 l_cover_acct_func_amt := 0;
1925 l_cover_pf_func_amt := 0;
1926 l_msg_count := 0;
1927
1928 -- 01/03/02, check to see if CC is installed
1929 IF NOT igi_gen.is_req_installed('CC') THEN
1930
1931 SELECT meaning
1932 INTO l_option_name
1933 FROM igi_lookups
1934 WHERE lookup_code = 'CC'
1935 AND lookup_type = 'GCC_DESCRIPTION';
1936
1937 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
1938 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
1939 lv_message := fnd_message.get;
1940 errbuf := lv_message;
1941 retcode := 2;
1942 return;
1943 END IF;
1944
1945 -- Bug 2441322, get the numeric rate
1946 l_rate := Fnd_number.canonical_to_number(canonical => p_rate) ;
1947
1948 /*Bug No : 6341012. MOAC Uptake. ORG_ID,SOB_ID are not retrieved from Profile values
1949 But from other packages...
1950 -- Get the profile values
1951 l_org_id := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
1952 l_sob_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
1953 */
1954 l_org_id := MO_GLOBAL.get_current_org_id;
1955 MO_UTILS.get_ledger_info(l_org_id,l_sob_id,l_sob_name);
1956
1957 --
1958 -- Setup debug information based upon profile setup options.
1959 --
1960 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
1961 -- IF (l_debug = 'Y') THEN
1962 -- l_debug := FND_API.G_TRUE;
1963 -- ELSE
1964 -- l_debug := FND_API.G_FALSE;
1965 -- END IF;
1966 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
1967
1968 -- IF l_debug_mode = 'Y' THEN
1969 -- Output_Debug (' IGCCREPB -- ************ Starting Revalue CC '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1970 -- END IF;
1971
1972 -- bug 3199488, start block
1973 IF (l_state_level >= l_debug_level) THEN
1974 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg1',
1975 ' IGCCREPB -- Starting Revalue CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
1976 END IF;
1977 -- bug 3199488, end block
1978
1979 RETCODE := '0';
1980
1981 -- Bug 1914745, clear any old records from the igc_cc_interface table
1982 -- DELETE FROM igc_cc_interface
1983 -- WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date((sysdate - interval '2' day), 'DD/MM/YYYY');
1984
1985 --Bug 2872060. Above delete commented out. Was causing compilation probs in Oracle8i
1986 DELETE FROM igc_cc_interface
1987 WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date(sysdate ,'DD/MM/YYYY') - 2;
1988
1989
1990 /* Begin fix bug 1591845 */
1991
1992 l_rate_date := trunc(to_date (p_rate_date, 'YYYY/MM/DD HH24:MI:SS'));
1993
1994 /* Begin fix bug 1591845 */
1995
1996 l_request_id1 := fnd_global.conc_request_id;
1997
1998 SAVEPOINT REVALUE1;
1999
2000 l_currency_code := NULL;
2001 l_sbc_on := NULL;
2002 l_cbc_on := NULL;
2003 l_prov_enc_on := NULL;
2004 l_conf_enc_on := NULL;
2005 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
2006 -- l_req_encumbrance_type_id := NULL;
2007 -- l_purch_encumbrance_type_id := NULL;
2008 -- l_cc_prov_enc_type_id := NULL;
2009 -- l_cc_conf_enc_type_id := NULL;
2010
2011
2012 /* Get Budgetary Control information */
2013 -- IF l_debug_mode = 'Y' THEN
2014 -- Output_Debug (' IGCCREPB -- Calling get_budg_ctrl_params ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2015 -- END IF;
2016
2017 -- bug 3199488, start block
2018 IF (l_state_level >= l_debug_level) THEN
2019 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg2',
2020 ' IGCCREPB -- Calling get_budg_ctrl_params ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2021 END IF;
2022 -- bug 3199488, end block
2023
2024 /* Begin fix for bug 1576023 */
2025 l_msg_data := NULL;
2026 l_msg_count := 0;
2027 l_usr_msg := NULL;
2028
2029 l_budg_status := IGC_CC_REP_YEP_PVT.get_budg_ctrl_params(
2030 l_sob_id,
2031 l_org_id,
2032 l_currency_code,
2033 l_sbc_on,
2034 l_cbc_on,
2035 l_prov_enc_on,
2036 l_conf_enc_on,
2037 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required */
2038 -- l_req_encumbrance_type_id,
2039 -- l_purch_encumbrance_type_id,
2040 -- l_cc_prov_enc_type_id,
2041 -- l_cc_conf_enc_type_id ,
2042 l_msg_data,
2043 l_msg_count,
2044 l_usr_msg
2045 ) ;
2046
2047
2048 IF (l_budg_status = FALSE) AND (l_usr_msg IS NOT NULL)
2049 THEN
2050 INSERT INTO
2051 igc_cc_process_exceptions
2052 (process_type,
2053 process_phase,
2054 cc_header_id,
2055 cc_acct_line_id,
2056 cc_det_pf_line_id,
2057 exception_reason,
2058 org_id,
2059 set_of_books_id,
2060 request_id)
2061 VALUES
2062 ( 'R',
2063 p_process_phase,
2064 NULL,
2065 NULL,
2066 NULL,
2067 l_usr_msg,
2068 l_org_id,
2069 l_sob_id,
2070 l_request_id1);
2071
2072 COMMIT;
2073
2074 END IF;
2075
2076 IF (l_budg_status = FALSE AND l_usr_msg IS NOT NULL)
2077 THEN
2078
2079 --IF l_debug_mode = 'Y' THEN
2080 --Output_Debug (' IGCCREPB -- Submitting request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2081 --END IF;
2082
2083 -- bug 3199488, start block
2084 IF (l_state_level >= l_debug_level) THEN
2085 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg3',
2086 ' IGCCREPB -- Submitting request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2087 END IF;
2088 -- bug 3199488, end block
2089
2090
2091 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
2092 Fnd_request.set_org_id(l_org_id);
2093 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2094 'IGC',
2095 'IGCCRVPR',
2096 NULL,
2097 NULL,
2098 FALSE,
2099 l_sob_id,
2100 l_org_id,
2101 p_process_phase,
2102 'R',
2103 l_request_id1);
2104 -----------------------
2105 -- Start of XML Report
2106 -----------------------
2107 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2108 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2109 l_lang,
2110 l_terr,
2111 'IGCCRVPR_XML',
2112 'IGC',
2113 'IGCCRVPR_XML' );
2114
2115 l_layout := FND_REQUEST.ADD_LAYOUT(
2116 'IGC',
2117 'IGCCRVPR_XML',
2118 l_lang,
2119 l_terr,
2120 'RTF');
2121
2122 IF l_layout then
2123 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2124 'IGC',
2125 'IGCCRVPR_XML',
2126 NULL,
2127 NULL,
2128 FALSE,
2129 l_sob_id,
2130 l_org_id,
2131 p_process_phase,
2132 'R',
2133 l_request_id1);
2134 END IF;
2135 END IF;
2136 --------------------
2137 -- End of XML Report
2138 --------------------
2139
2140 END IF;
2141
2142 -- ------------------------------------------------------------------------------------
2143 -- Ensure that any exceptions raised are output into the log file to be reported to
2144 -- the user if any are present.
2145 -- ------------------------------------------------------------------------------------
2146 IF (l_budg_status = FALSE )
2147 THEN
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)
2152 THEN
2153 l_error_text := '';
2154 FOR l_cur IN 1..l_msg_count
2155 LOOP
2156 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2157 -- fnd_file.put_line (FND_FILE.LOG,
2158 -- l_error_text);
2159 -- bug 3199488 start block
2160 IF (l_state_level >= l_debug_level) THEN
2161 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp1',
2162 l_error_text);
2163 END IF;
2164 -- bug 3199488, end block
2165 END LOOP;
2166 END IF;
2167 END IF;
2168
2169 IF (l_budg_status = FALSE AND l_usr_msg IS NULL)
2170 THEN
2171 RETCODE := 2;
2172 END IF;
2173
2174 IF (l_budg_status = FALSE )
2175 THEN
2176 RETURN;
2177 END IF;
2178
2179 /* End fix for bug 1576023 */
2180
2181
2182 --IF l_debug_mode = 'Y' THEN
2183 -- Output_Debug (' IGCCREPB -- Calling validate_params ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2184 --END IF;
2185
2186 -- bug 3199488, start block
2187 IF (l_state_level >= l_debug_level) THEN
2188 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg4',
2189 ' IGCCREPB -- Calling validate_params ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2190 END IF;
2191 -- bug 3199488, end block
2192
2193 IF ( NOT validate_params(p_process_phase,
2194 l_sob_id,
2195 l_org_id,
2196 p_currency_code,
2197 l_currency_code,
2198 p_rate_type,
2199 l_rate,
2200 l_rate_date,
2201 l_request_id1)
2202 )
2203 THEN
2204
2205 --IF l_debug_mode = 'Y' THEN
2206 --Output_Debug (' IGCCREPB -- Submitting request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2207 --END IF;
2208
2209 -- bug 3199488, start block
2210 IF (l_state_level >= l_debug_level) THEN
2211 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg5',
2212 ' IGCCREPB -- Submitting request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2213 END IF;
2214 -- bug 3199488, end block
2215
2216 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
2217 Fnd_request.set_org_id(l_org_id);
2218 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2219 'IGC',
2220 'IGCCRVPR',
2221 NULL,
2222 NULL,
2223 FALSE,
2224 l_sob_id,
2225 l_org_id,
2226 p_process_phase,
2227 'R',
2228 l_request_id1);
2229 -----------------------
2230 -- Start of XML Report
2231 -----------------------
2232 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2233 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2234 l_lang,
2235 l_terr,
2236 'IGCCRVPR_XML',
2237 'IGC',
2238 'IGCCRVPR_XML' );
2239
2240 l_layout := FND_REQUEST.ADD_LAYOUT(
2241 'IGC',
2242 'IGCCRVPR_XML',
2243 l_lang,
2244 l_terr,
2245 'RTF');
2246
2247 IF l_layout then
2248 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
2249 'IGC',
2250 'IGCCRVPR_XML',
2251 NULL,
2252 NULL,
2253 FALSE,
2254 l_sob_id,
2255 l_org_id,
2256 p_process_phase,
2257 'R',
2258 l_request_id1);
2259 END IF;
2260 END IF;
2261 --------------------
2262 -- End of XML Report
2263 --------------------
2264
2265 -- ------------------------------------------------------------------------------------
2266 -- Ensure that any exceptions raised are output into the log file to be reported to
2267 -- the user if any are present.
2268 -- ------------------------------------------------------------------------------------
2269 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2270 p_data => l_msg_data );
2271
2272 IF (l_msg_count > 0) THEN
2273 l_error_text := '';
2274 FOR l_cur IN 1..l_msg_count LOOP
2275 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2276 -- fnd_file.put_line (FND_FILE.LOG,
2277 -- l_error_text);
2278 -- bug 3199488 start block
2279 IF (l_state_level >= l_debug_level) THEN
2280 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp2',
2281 l_error_text);
2282 END IF;
2283 -- bug 3199488, end block
2284 END LOOP;
2285 END IF;
2286
2287 RETURN;
2288 END IF;
2289
2290 SAVEPOINT REVALUE3;
2291
2292 l_process_data_count := 0;
2293 /* Populate temporary table */
2294
2295 -- IF l_debug_mode = 'Y' THEN
2296 -- Output_Debug (' IGCCREPB -- Opening revalue_process_cc cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2297 -- END IF;
2298
2299 -- bug 3199488, start block
2300 IF (l_state_level >= l_debug_level) THEN
2301 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg6',
2302 ' IGCCREPB -- Opening revalue_process_cc_cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2303 END IF;
2304 -- bug 3199488, end block
2305
2306 OPEN c_revalue_process_cc(p_process_phase,
2307 l_sob_id,
2308 l_org_id,
2309 p_currency_code, /* Non functional currency code */
2310 p_rate_type,
2311 l_rate,
2312 l_currency_code, /* Functional Currency Code */
2313 p_cc_header_id);
2314 LOOP
2315 FETCH c_revalue_process_cc
2316 INTO l_cc_headers_rec;
2317
2318 EXIT WHEN c_revalue_process_cc%NOTFOUND;
2319
2320 l_process_data_count := l_process_data_count + 1;
2321
2322 /* Begin Standard Revaluation */
2323 IF (l_cc_headers_rec.cc_type = 'S')
2324 THEN
2325 -- IF l_debug_mode = 'Y' THEN
2326 -- Output_Debug (' IGCCREPB -- Inserting CC type S record into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2327 -- END IF;
2328
2329 -- bug 3199488, start block
2330 IF (l_state_level >= l_debug_level) THEN
2331 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg7',
2332 ' IGCCREPB -- Inserting CC type S record into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2333 END IF;
2334 -- bug 3199488, end block
2335
2336 INSERT INTO igc_cc_process_data
2337 (
2338 process_type,
2339 process_phase,
2340 cc_header_id,
2341 validation_status,
2342 reservation_status,
2343 processed,
2344 old_approval_status,
2345 org_id,
2346 set_of_books_id,
2347 validate_only,
2348 request_id)
2349 VALUES
2350 ( 'R',
2351 p_process_phase,
2352 l_cc_headers_rec.cc_header_id,
2353 'I',
2354 'F',
2355 'N',
2356 NULL,
2357 l_org_id,
2358 l_sob_id,
2359 'Y',
2360 l_request_id1);
2361
2362 COMMIT;
2363
2364 END IF;
2365 /* End Standard Revaluation */
2366
2367 /* Begin release Revaluation */
2368
2369 IF (l_cc_headers_rec.cc_type = 'R')
2370 THEN
2371 -- IF l_debug_mode = 'Y' THEN
2372 -- Output_Debug (' IGCCREPB -- Checking R CC Type record ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2373 -- END IF;
2374
2375 -- bug 3199488, start block
2376 IF (l_state_level >= l_debug_level) THEN
2377 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg8',
2378 ' IGCCREPB -- Checking R CC Type record ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2379 END IF;
2380 -- bug 3199488, end block
2381
2382 l_processed := 'N';
2383 l_cover_not_found := FALSE;
2384
2385 BEGIN
2386 SELECT NVL(processed,'N')
2387 INTO l_processed
2388 FROM igc_cc_process_data a
2389 WHERE a.cc_header_id = NVL(l_cc_headers_rec.parent_header_id,0) AND
2390 a.request_id = l_request_id1;
2391 EXCEPTION
2392 WHEN NO_DATA_FOUND
2393 THEN
2394 -- IF l_debug_mode = 'Y' THEN
2395 -- Output_Debug (' IGCCREPB -- Cover not found ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2396 -- END IF;
2397
2398 -- bug 3199488, start block
2399 IF (l_state_level >= l_debug_level) THEN
2400 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg9',
2401 ' IGCCREPB -- Cover not found ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2402 END IF;
2403 -- bug 3199488, end block
2404
2405 l_cover_not_found := TRUE;
2406 END;
2407
2408 IF (l_cover_not_found = TRUE)
2409 THEN
2410 -- IF l_debug_mode = 'Y' THEN
2411 -- Output_Debug (' IGCCREPB -- Inserting cover into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2412 -- END IF;
2413
2414 -- bug 3199488, start block
2415 IF (l_state_level >= l_debug_level) THEN
2416 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg10',
2417 ' IGCCREPB -- Inserting cover into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2418 END IF;
2419 -- bug 3199488, end block
2420
2421 INSERT INTO igc_cc_process_data
2422 (
2423 process_type,
2424 process_phase,
2425 cc_header_id,
2426 validation_status,
2427 reservation_status,
2428 processed,
2429 old_approval_status,
2430 org_id,
2431 set_of_books_id,
2432 validate_only,
2433 request_id)
2434 VALUES
2435 ( 'R',
2436 p_process_phase,
2437 l_cc_headers_rec.parent_header_id,
2438 'I',
2439 'F',
2440 'N',
2441 NULL,
2442 l_org_id,
2443 l_sob_id,
2444 'Y',
2445 l_request_id1);
2446 COMMIT;
2447 END IF;
2448
2449 IF (l_cover_not_found = TRUE)
2450 THEN
2451 -- IF l_debug_mode = 'Y' THEN
2452 -- Output_Debug (' IGCCREPB -- Opening all releases Cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2453 -- END IF;
2454
2455 -- bug 3199488, start block
2456 IF (l_state_level >= l_debug_level) THEN
2457 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg11',
2458 ' IGCCREPB -- Opening all releases Cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2459 END IF;
2460 -- bug 3199488, end block
2461
2462 OPEN c_all_releases(l_cc_headers_rec.parent_header_id);
2463 LOOP
2464 FETCH c_all_releases INTO l_cc_header_id;
2465 EXIT WHEN c_all_releases%NOTFOUND;
2466
2467 -- IF l_debug_mode = 'Y' THEN
2468 -- Output_Debug (' IGCCREPB -- Inserting release into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2469 -- END IF;
2470
2471 -- bug 3199488, start block
2472 IF (l_state_level >= l_debug_level) THEN
2473 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg12',
2474 ' IGCCREPB -- Inserting release into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2475 END IF;
2476 -- bug 3199488, end block
2477
2478 IF (l_cc_header_id = l_cc_headers_rec.cc_header_id)
2479 THEN
2480
2481 INSERT INTO igc_cc_process_data
2482 (
2483 process_type,
2484 process_phase,
2485 cc_header_id,
2486 validation_status,
2487 reservation_status,
2488 processed,
2489 old_approval_status,
2490 org_id,
2491 set_of_books_id,
2492 validate_only,
2493 request_id)
2494 VALUES
2495 ( 'R',
2496 p_process_phase,
2497 l_cc_header_id,
2498 'I',
2499 'F',
2500 'N',
2501 NULL,
2502 l_org_id,
2503 l_sob_id,
2504 'N',
2505 l_request_id1);
2506 ELSE
2507 INSERT INTO igc_cc_process_data
2508 (
2509 process_type,
2510 process_phase,
2511 cc_header_id,
2512 validation_status,
2513 reservation_status,
2514 processed,
2515 old_approval_status,
2516 org_id,
2517 set_of_books_id,
2518 validate_only,
2519 request_id)
2520 VALUES
2521 ( 'R',
2522 p_process_phase,
2523 l_cc_header_id,
2524 'I',
2525 'F',
2526 'N',
2527 NULL,
2528 l_org_id,
2529 l_sob_id,
2530 'Y',
2531 l_request_id1);
2532 END IF;
2533
2534 END LOOP;
2535
2536 CLOSE c_all_releases;
2537
2538 COMMIT;
2539
2540 END IF;
2541
2542 IF (l_cover_not_found = FALSE)
2543 THEN
2544 -- IF l_debug_mode = 'Y' THEN
2545 -- Output_Debug (' IGCCREPB -- Attempting lock CC PO ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2546 -- END IF;
2547
2548 -- bug 3199488, start block
2549 IF (l_state_level >= l_debug_level) THEN
2550 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg13',
2551 ' IGCCREPB -- Attempting lock CC PO ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2552 END IF;
2553 -- bug 3199488, end block
2554
2555 l_lock_cc_po := FALSE;
2556 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_cc_headers_rec.parent_header_id,l_request_id1);
2557
2558 IF (l_lock_cc_po = TRUE)
2559 THEN
2560 UPDATE igc_cc_process_data a
2561 SET
2562 validate_only = 'N'
2563 WHERE a.request_id = l_request_id1 AND
2564 a.cc_header_id = l_cc_headers_rec.cc_header_id;
2565 COMMIT;
2566 END IF;
2567
2568 END IF;
2569 END IF;
2570 /* End release Revaluation */
2571
2572 /* Begin Cover Revaluation */
2573
2574 IF (l_cc_headers_rec.cc_type = 'C')
2575 THEN
2576 -- IF l_debug_mode = 'Y' THEN
2577 -- Output_Debug (' IGCCREPB -- Begin Cover revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2578 -- END IF;
2579
2580 -- bug 3199488, start block
2581 IF (l_state_level >= l_debug_level) THEN
2582 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg14',
2583 ' IGCCREPB -- Begin Cover revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2584 END IF;
2585 -- bug 3199488, end block
2586
2587 l_processed := 'N';
2588 l_cover_not_found := FALSE;
2589
2590 BEGIN
2591 SELECT NVL(processed,'N')
2592 INTO l_processed
2593 FROM igc_cc_process_data a
2594 WHERE a.cc_header_id = l_cc_headers_rec.cc_header_id AND
2595 a.request_id = l_request_id1;
2596 EXCEPTION
2597 WHEN NO_DATA_FOUND
2598 THEN
2599 -- IF l_debug_mode = 'Y' THEN
2600 -- Output_Debug (' IGCCREPB -- Cover not found ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2601 -- END IF;
2602
2603 -- bug 3199488, start block
2604 IF (l_state_level >= l_debug_level) THEN
2605 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg15',
2606 ' IGCCREPB -- Cover not found ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2607 END IF;
2608 -- bug 3199488, end block
2609
2610 l_cover_not_found := TRUE;
2611 END;
2612
2613 IF (l_cover_not_found = TRUE)
2614 THEN
2615 -- IF l_debug_mode = 'Y' THEN
2616 -- Output_Debug (' IGCCREPB -- Obtaining releases cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2617 -- END IF;
2618
2619 -- bug 3199488, start block
2620 IF (l_state_level >= l_debug_level) THEN
2621 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg16',
2622 ' IGCCREPB -- Obtaining releases cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2623 END IF;
2624 -- bug 3199488, end block
2625
2626 OPEN c_all_releases(l_cc_headers_rec.cc_header_id);
2627 LOOP
2628 FETCH c_all_releases INTO l_cc_header_id;
2629 EXIT WHEN c_all_releases%NOTFOUND;
2630 -- IF l_debug_mode = 'Y' THEN
2631 -- Output_Debug (' IGCCREPB -- Inserting into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2632 -- END IF;
2633
2634 -- bug 3199488, start block
2635 IF (l_state_level >= l_debug_level) THEN
2636 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg17',
2637 ' IGCCREPB -- Inserting into process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2638 END IF;
2639 -- bug 3199488, end block
2640
2641 INSERT INTO igc_cc_process_data
2642 (
2643 process_type,
2644 process_phase,
2645 cc_header_id,
2646 validation_status,
2647 reservation_status,
2648 processed,
2649 old_approval_status,
2650 org_id,
2651 set_of_books_id,
2652 validate_only,
2653 request_id)
2654 VALUES
2655 ( 'R',
2656 p_process_phase,
2657 l_cc_header_id,
2658 'I',
2659 'F',
2660 'N',
2661 NULL,
2662 l_org_id,
2663 l_sob_id,
2664 'N',
2665 l_request_id1);
2666 END LOOP;
2667 CLOSE c_all_releases;
2668 -- IF l_debug_mode = 'Y' THEN
2669 -- Output_Debug (' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2670 -- Output_Debug (' IGCCREPB -- Inserting cover into process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2671 -- END IF;
2672
2673 -- bug 3199488, start block
2674 IF (l_state_level >= l_debug_level) THEN
2675 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg18',
2676 ' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2677 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg19',
2678 ' IGCCREPB -- Inserting cover into process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2679
2680 END IF;
2681 -- bug 3199488, end block
2682
2683 INSERT INTO igc_cc_process_data
2684 (
2685 process_type,
2686 process_phase,
2687 cc_header_id,
2688 validation_status,
2689 reservation_status,
2690 processed,
2691 old_approval_status,
2692 org_id,
2693 set_of_books_id,
2694 validate_only,
2695 request_id)
2696 VALUES
2697 ( 'R',
2698 p_process_phase,
2699 l_cc_headers_rec.cc_header_id,
2700 'I',
2701 'F',
2702 'N',
2703 NULL,
2704 l_org_id,
2705 l_sob_id,
2706 'N',
2707 l_request_id1);
2708
2709 COMMIT;
2710 END IF;
2711
2712 END IF;
2713 /* End Cover revaluation */
2714
2715 END LOOP;
2716
2717 CLOSE c_revalue_process_cc;
2718 -- IF l_debug_mode = 'Y' THEN
2719 -- Output_Debug (' IGCCREPB -- Done with revalue process CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2720 -- END IF;
2721
2722 -- bug 3199488, start block
2723 IF (l_state_level >= l_debug_level) THEN
2724 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg20',
2725 ' IGCCREPB -- Done with revalue process CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2726 END IF;
2727 -- bug 3199488, end block
2728
2729 COMMIT;
2730
2731 /* Begin Lock CC and PO */
2732 /* Lock Contract Commitments and related PO's If Phase = 'Final' */
2733 -- IF l_debug_mode = 'Y' THEN
2734 -- Output_Debug (' IGCCREPB -- Checking Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2735 -- END IF;
2736
2737 -- bug 3199488, start block
2738 IF (l_state_level >= l_debug_level) THEN
2739 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg21',
2740 ' IGCCREPB -- Checking Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2741 END IF;
2742 -- bug 3199488, end block
2743
2744 IF (p_process_phase = 'F') AND (l_process_data_count > 0)
2745 THEN
2746 -- IF l_debug_mode = 'Y' THEN
2747 -- Output_Debug (' IGCCREPB -- Final Phase Starts ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2748 -- END IF;
2749
2750 -- bug 3199488, start block
2751 IF (l_state_level >= l_debug_level) THEN
2752 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg22',
2753 ' IGCCREPB -- Final Phase Starts ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2754 END IF;
2755 -- bug 3199488, end block
2756
2757 OPEN c_reval_data(l_request_id1);
2758 LOOP
2759 FETCH c_reval_data INTO l_cc_header_id;
2760 EXIT WHEN c_reval_data%NOTFOUND;
2761
2762 /* Get Contract Details */
2763 SELECT *
2764 INTO l_cc_headers_rec
2765 FROM igc_cc_headers
2766 WHERE cc_header_id = l_cc_header_id;
2767
2768 l_lock_cc_po := TRUE;
2769
2770 /* Standard */
2771 IF (l_cc_headers_rec.cc_type = 'S')
2772 THEN
2773 -- IF l_debug_mode = 'Y' THEN
2774 -- Output_Debug (' IGCCREPB -- Attempting lock cc po ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2775 -- END IF;
2776
2777 -- bug 3199488, start block
2778 IF (l_state_level >= l_debug_level) THEN
2779 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg23',
2780 ' IGCCREPB -- Attempting lock cc po ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2781 END IF;
2782 -- bug 3199488, end block
2783
2784 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_cc_header_id, l_request_id1);
2785
2786 END IF;
2787
2788 IF (l_lock_cc_po = FALSE)
2789 THEN
2790 -- IF l_debug_mode = 'Y' THEN
2791 -- Output_Debug (' IGCCREPB -- lock cc po FALSE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2792 -- END IF;
2793
2794 -- bug 3199488, start block
2795 IF (l_state_level >= l_debug_level) THEN
2796 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg24',
2797 ' IGCCREPB -- Attempting lock cc po FALSE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2798 END IF;
2799 -- bug 3199488, end block
2800
2801 /* bug 1622969 */
2802 UPDATE igc_cc_process_data
2803 SET validation_status = 'F',
2804 processed = 'Y'
2805 WHERE cc_header_id = l_cc_header_id AND
2806 request_id = l_request_id1;
2807 /* bug 1622969 */
2808
2809 END IF;
2810
2811 /* Cover Relase */
2812 -- IF l_debug_mode = 'Y' THEN
2813 -- Output_Debug (' IGCCREPB -- Checking Cover Type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2814 -- END IF;
2815
2816 -- bug 3199488, start block
2817 IF (l_state_level >= l_debug_level) THEN
2818 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg25',
2819 ' IGCCREPB -- Checking Cover Type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2820 END IF;
2821 -- bug 3199488, end block
2822
2823 IF (l_cc_headers_rec.cc_type = 'C')
2824 THEN
2825 -- IF l_debug_mode = 'Y' THEN
2826 -- Output_Debug (' IGCCREPB -- Getting Releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2827 -- END IF;
2828
2829 -- bug 3199488, start block
2830 IF (l_state_level >= l_debug_level) THEN
2831 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg26',
2832 ' IGCCREPB -- Getting Releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2833 END IF;
2834 -- bug 3199488, end block
2835
2836 OPEN c_all_releases(l_cc_headers_rec.cc_header_id);
2837 LOOP
2838 FETCH c_all_releases INTO l_rel_cc_header_id;
2839 EXIT WHEN c_all_releases%NOTFOUND;
2840
2841 l_lock_cc_po := TRUE;
2842
2843 IF (l_lock_cc_po = TRUE)
2844 THEN
2845 -- IF l_debug_mode = 'Y' THEN
2846 -- Output_Debug (' IGCCREPB -- locking cc po for release ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2847 -- END IF;
2848
2849 -- bug 3199488, start block
2850 IF (l_state_level >= l_debug_level) THEN
2851 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg27',' IGCCREPB -- locking cc po for release ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2852 END IF;
2853 -- bug 3199488, end block
2854
2855 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_rel_cc_header_id,l_request_id1);
2856 END IF;
2857
2858 IF (l_lock_cc_po = FALSE)
2859 THEN
2860 -- IF l_debug_mode = 'Y' THEN
2861 -- Output_Debug (' IGCCREPB -- lock cc po for release FALSE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2862 -- END IF;
2863
2864 -- bug 3199488, start block
2865 IF (l_state_level >= l_debug_level) THEN
2866 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg28',
2867 ' IGCCREPB -- locking cc po for release FALSE' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2868 END IF;
2869 -- bug 3199488, end block
2870
2871 /* bug 1622969 */
2872
2873 /* Update releases status in igc_cc_process_data */
2874 UPDATE igc_cc_process_data
2875 SET validation_status = 'F',
2876 processed = 'Y'
2877 WHERE cc_header_id = l_rel_cc_header_id AND
2878 request_id = l_request_id1;
2879
2880 /* Update cover status in igc_cc_process_data */
2881 UPDATE igc_cc_process_data
2882 SET validation_status = 'F',
2883 processed = 'Y'
2884 WHERE cc_header_id = l_cc_header_id AND
2885 request_id = l_request_id1;
2886
2887 /* bug 1622969 */
2888 END IF;
2889
2890 END LOOP;
2891 CLOSE c_all_releases;
2892
2893 -- IF l_debug_mode = 'Y' THEN
2894 -- Output_Debug (' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2895 -- END IF;
2896
2897 -- bug 3199488, start block
2898 IF (l_state_level >= l_debug_level) THEN
2899 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg29',
2900 ' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2901 END IF;
2902 -- bug 3199488, end block
2903
2904 l_lock_cc_po := TRUE;
2905
2906 IF (l_lock_cc_po = TRUE)
2907 THEN
2908 -- IF l_debug_mode = 'Y' THEN
2909 -- Output_Debug (' IGCCREPB -- lock cc po TRUE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2910 -- END IF;
2911
2912 -- bug 3199488, start block
2913 IF (l_state_level >= l_debug_level) THEN
2914 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg30',
2915 ' IGCCREPB -- lock cc po TRUE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2916 END IF;
2917 -- bug 3199488, end block
2918
2919 l_lock_cc_po := lock_cc_po(l_sob_id,l_org_id,l_cc_header_id,l_request_id1);
2920 END IF;
2921
2922 IF (l_lock_cc_po = FALSE)
2923 THEN
2924 -- IF l_debug_mode = 'Y' THEN
2925 -- Output_Debug (' IGCCREPB -- Lock cc po FALSE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2926 -- END IF;
2927
2928 -- bug 3199488, start block
2929 IF (l_state_level >= l_debug_level) THEN
2930 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg31',
2931 ' IGCCREPB -- lock cc po FALSE ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2932 END IF;
2933 -- bug 3199488, end block
2934
2935 /* bug 1622969 */
2936
2937 /* Update cover status in igc_cc_process_data */
2938 UPDATE igc_cc_process_data
2939 SET validation_status = 'F',
2940 processed = 'Y'
2941 WHERE cc_header_id = l_cc_header_id AND
2942 request_id = l_request_id1;
2943
2944 /* Update releases statuses in igc_cc_process_data */
2945 UPDATE igc_cc_process_data
2946 SET
2947 validation_status = 'F' ,
2948 processed = 'Y'
2949 WHERE request_id = l_request_id1 AND
2950 cc_header_id IN (SELECT cc_header_id
2951 FROM igc_cc_headers
2952 WHERE NVL(parent_header_id,0) = l_cc_header_id);
2953 /* bug 1622969 */
2954 END IF;
2955
2956
2957 END IF;
2958
2959 END LOOP;
2960 CLOSE c_reval_data;
2961 -- IF l_debug_mode = 'Y' THEN
2962 -- Output_Debug (' IGCCREPB -- Done with reval_data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2963 -- END IF;
2964
2965 -- bug 3199488, start block
2966 IF (l_state_level >= l_debug_level) THEN
2967 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg32',
2968 ' IGCCREPB -- Done with reval_data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2969 END IF;
2970 -- bug 3199488, end block
2971
2972 END IF;
2973
2974 /* End Lock CC and PO */
2975 -- IF l_debug_mode = 'Y' THEN
2976 -- Output_Debug (' IGCCREPB -- Done with Lock CC and PO ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2977 -- END IF;
2978
2979 -- bug 3199488, start block
2980 IF (l_state_level >= l_debug_level) THEN
2981 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg33',
2982 ' IGCCREPB -- Done with lock CC and PO ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2983 END IF;
2984 -- bug 3199488, end block
2985
2986 IF ((p_process_phase = 'P') OR (p_process_phase = 'F')) AND (l_process_data_count > 0)
2987 THEN
2988 -- IF l_debug_mode = 'Y' THEN
2989 -- Output_Debug (' IGCCREPB -- Validate Cover for avail amount ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2990 -- END IF;
2991
2992 -- bug 3199488, start block
2993 IF (l_state_level >= l_debug_level) THEN
2994 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg34',
2995 ' IGCCREPB -- Validate Cover for avail amount ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2996 END IF;
2997 -- bug 3199488, end block
2998
2999 /* Validate all covers for available amount subject to re_valuation */
3000 OPEN c_cover_reval_data(l_request_id1);
3001 LOOP
3002
3003 FETCH c_cover_reval_data INTO l_cc_header_id;
3004 EXIT WHEN c_cover_reval_data%NOTFOUND;
3005
3006 /* Get Contract Details */
3007 SELECT *
3008 INTO l_cc_headers_rec
3009 FROM igc_cc_headers
3010 WHERE cc_header_id = l_cc_header_id;
3011
3012 -- IF l_debug_mode = 'Y' THEN
3013 -- Output_Debug (' IGCCREPB -- Getting account lines ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3014 -- END IF;
3015
3016 -- bug 3199488, start block
3017 IF (l_state_level >= l_debug_level) THEN
3018 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg35',
3019 ' IGCCREPB -- Getting account lines ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3020 END IF;
3021 -- bug 3199488, end block
3022
3023 OPEN c_acct_lines(l_cc_header_id);
3024 LOOP
3025 FETCH c_acct_lines INTO l_cc_acct_lines_rec;
3026 EXIT WHEN c_acct_lines%NOTFOUND;
3027
3028 l_cover_acct_func_amt := 0;
3029 l_non_reval_acct_amt_total := 0;
3030 l_reval_acct_amt_total := 0;
3031
3032 /* Single release revaluation */
3033 IF (l_cc_headers_rec.cc_header_id <> NVL(p_cc_header_id,-9999) ) AND (p_cc_header_id IS NOT NULL)
3034 THEN
3035 -- IF l_debug_mode = 'Y' THEN
3036 -- Output_Debug (' IGCCREPB -- Single release revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3037 -- END IF;
3038
3039 -- bug 3199488, start block
3040 IF (l_state_level >= l_debug_level) THEN
3041 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg36',
3042 ' IGCCREPB -- Getting account lines ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3043 END IF;
3044 -- bug 3199488, end block
3045
3046 BEGIN
3047 SELECT NVL(SUM(NVL(CC_ACCT_FUNC_AMT,0)),0)
3048 INTO l_non_reval_acct_amt_total
3049 FROM igc_cc_acct_lines a,
3050 igc_cc_headers b
3051 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3052 a.cc_header_id = b.cc_header_id AND
3053 NVL(b.parent_header_id,0) = l_cc_header_id AND
3054 b.cc_header_id <> p_cc_header_id;
3055 EXCEPTION
3056 WHEN NO_DATA_FOUND
3057 THEN
3058 -- IF l_debug_mode = 'Y' THEN
3059 -- Output_Debug (' IGCCREPB -- Amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3060 -- END IF;
3061
3062 -- bug 3199488, start block
3063 IF (l_state_level >= l_debug_level) THEN
3064 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg37',' IGCCREPB -- Amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3065 END IF;
3066 -- bug 3199488, end block
3067
3068 l_non_reval_acct_amt_total := 0;
3069 END;
3070
3071 BEGIN
3072
3073 SELECT SUM(
3074 NVL(a.cc_acct_func_amt,0) +
3075 (
3076 ( ( NVL(a.cc_acct_func_amt,0) -
3077 -- a.cc_acct_func_billed_amt)
3078 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
3079 ) * (l_rate - b.conversion_rate )
3080 )
3081 )
3082 INTO l_reval_acct_amt_total
3083 --FROM igc_cc_acct_lines_v a,
3084 FROM igc_cc_acct_lines a,
3085 igc_cc_headers b
3086 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3087 a.cc_header_id = b.cc_header_id AND
3088 b.cc_header_id = p_cc_header_id;
3089 EXCEPTION
3090 WHEN NO_DATA_FOUND
3091 THEN
3092 -- IF l_debug_mode = 'Y' THEN
3093 -- Output_Debug (' IGCCREPB -- Amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3094 -- END IF;
3095 -- bug 3199488, start block
3096 IF (l_state_level >= l_debug_level) THEN
3097 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg38',
3098 ' IGCCREPB -- Amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3099 END IF;
3100 -- bug 3199488, end block
3101 l_reval_acct_amt_total := 0;
3102 END;
3103
3104 l_cover_acct_func_amt := l_cc_acct_lines_rec.cc_acct_func_amt;
3105 END IF;
3106
3107 l_validate_only := 'Y';
3108
3109 SELECT validate_only
3110 INTO l_validate_only
3111 FROM igc_cc_process_data
3112 WHERE request_id = l_request_id1 AND
3113 cc_header_id = l_cc_headers_rec.cc_header_id;
3114
3115 /* Release revaluation only */
3116
3117 IF (p_cc_header_id IS NULL) AND (l_validate_only = 'Y')
3118 THEN
3119 /* added NVL to SUM function below to fix bug 1606212 */
3120 -- IF l_debug_mode = 'Y' THEN
3121 -- Output_Debug (' IGCCREPB -- Revalue ONLY ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3122 -- END IF;
3123
3124 -- bug 3199488, start block
3125 IF (l_state_level >= l_debug_level) THEN
3126 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg39',
3127 ' IGCCREPB -- Revalue ONLY ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3128 END IF;
3129 -- bug 3199488, end block
3130
3131 BEGIN
3132 SELECT NVL(SUM(NVL(CC_ACCT_FUNC_AMT,0)),0)
3133 INTO l_non_reval_acct_amt_total
3134 FROM igc_cc_acct_lines a,
3135 igc_cc_headers b
3136 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3137 a.cc_header_id = b.cc_header_id AND
3138 NVL(b.parent_header_id,0) = l_cc_header_id AND
3139 ( (b.currency_code = p_currency_code AND b.conversion_type <> p_rate_type) OR
3140 (b.currency_code <> p_currency_code) ) ;
3141 EXCEPTION
3142 WHEN NO_DATA_FOUND
3143 THEN
3144 -- IF l_debug_mode = 'Y' THEN
3145 -- Output_Debug (' IGCCREPB -- NON Revalue amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3146 -- END IF;
3147
3148 -- bug 3199488, start block
3149 IF (l_state_level >= l_debug_level) THEN
3150 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg40',
3151 ' IGCCREPB -- NON Revalue amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3152 END IF;
3153 -- bug 3199488, end block
3154
3155 l_non_reval_acct_amt_total := 0;
3156 END;
3157
3158 BEGIN
3159 SELECT SUM(
3160 NVL(a.cc_acct_func_amt,0) +
3161 (
3162 ( ( NVL(a.cc_acct_func_amt,0) -
3163 -- a.cc_acct_func_billed_amt)
3164 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
3165 ) * (l_rate - b.conversion_rate )
3166 )
3167 )
3168 INTO l_reval_acct_amt_total
3169 --FROM igc_cc_acct_lines_v a,
3170 FROM igc_cc_acct_lines a,
3171 igc_cc_headers b
3172 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3173 a.cc_header_id = b.cc_header_id AND
3174 NVL(b.parent_header_id,0) = l_cc_header_id AND
3175 ( (b.currency_code = p_currency_code) AND
3176 ( b.conversion_type = p_rate_type));
3177 EXCEPTION
3178 WHEN NO_DATA_FOUND
3179 THEN
3180 -- IF l_debug_mode = 'Y' THEN
3181 -- Output_Debug (' IGCCREPB -- Revalue amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3182 -- END IF;
3183
3184 -- bug 3199488, start block
3185 IF (l_state_level >= l_debug_level) THEN
3186 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg41',
3187 ' IGCCREPB -- Revalue amt total 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3188 END IF;
3189 -- bug 3199488, end block
3190
3191 l_reval_acct_amt_total := 0;
3192 END;
3193
3194 l_cover_acct_func_amt := l_cc_acct_lines_rec.cc_acct_func_amt;
3195 END IF;
3196
3197 /* Cover and release revaluation */
3198 IF (p_cc_header_id IS NOT NULL) AND (l_validate_only = 'N') AND
3199 (l_cc_headers_rec.cc_header_id = NVL(p_cc_header_id,-9999) )
3200 THEN
3201 -- IF l_debug_mode = 'Y' THEN
3202 -- Output_Debug (' IGCCREPB -- Cover AND Release revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3203 -- END IF;
3204
3205 -- bug 3199488, start block
3206 IF (l_state_level >= l_debug_level) THEN
3207 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg42',
3208 ' IGCCREPB -- Cover AND Release revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3209 END IF;
3210 -- bug 3199488, end block
3211
3212 BEGIN
3213 SELECT SUM(NVL(CC_ACCT_FUNC_AMT,0))
3214 INTO l_non_reval_acct_amt_total
3215 FROM igc_cc_acct_lines a,
3216 igc_cc_headers b
3217 WHERE NVL(parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3218 NVL(b.parent_header_id,0) = l_cc_header_id AND
3219 a.cc_header_id = b.cc_header_id AND
3220 ( (b.currency_code = p_currency_code AND b.conversion_type <> p_rate_type) OR
3221 (b.currency_code <> p_currency_code) ) ;
3222 EXCEPTION
3223 WHEN NO_DATA_FOUND
3224 THEN
3225 -- IF l_debug_mode = 'Y' THEN
3226 -- Output_Debug (' IGCCREPB -- NON reval acct amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3227 -- END IF;
3228
3229 -- bug 3199488, start block
3230 IF (l_state_level >= l_debug_level) THEN
3231 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg43',
3232 ' IGCCREPB -- NON reval acct amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3233 END IF;
3234 -- bug 3199488, end block
3235
3236 l_non_reval_acct_amt_total := 0;
3237 END;
3238
3239 BEGIN
3240 SELECT SUM(
3241 NVL(a.cc_acct_func_amt,0) +
3242 (
3243 ( ( NVL(a.cc_acct_func_amt,0) -
3244 --a.cc_acct_func_billed_amt)
3245 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( a.cc_acct_line_id)) / b.conversion_rate
3246 ) * (l_rate - b.conversion_rate )
3247 )
3248 )
3249 INTO l_reval_acct_amt_total
3250 --FROM igc_cc_acct_lines_v a,
3251 FROM igc_cc_acct_lines a,
3252 igc_cc_headers b
3253 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3254 a.cc_header_id = b.cc_header_id AND
3255 ( (b.currency_code = p_currency_code) AND
3256 ( b.conversion_type = p_rate_type));
3257 EXCEPTION
3258 WHEN NO_DATA_FOUND
3259 THEN
3260 -- IF l_debug_mode = 'Y' THEN
3261 -- Output_Debug (' IGCCREPB -- reval acct amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3262 -- END IF;
3263 -- bug 3199488, start block
3264 IF (l_state_level >= l_debug_level) THEN
3265 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg44',
3266 ' IGCCREPB -- reval acct amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3267 END IF;
3268 -- bug 3199488, end block
3269 l_reval_acct_amt_total := 0;
3270 END;
3271
3272 l_cover_acct_func_amt :=
3273 ( l_cc_acct_lines_rec.cc_acct_func_amt +
3274 ( ( ( l_cc_acct_lines_rec.cc_acct_func_amt -
3275 l_cc_acct_lines_rec.cc_acct_func_billed_amt
3276 ) / l_cc_headers_rec.conversion_rate
3277 ) * (l_rate - l_cc_headers_rec.conversion_rate )
3278 )
3279 );
3280 END IF;
3281
3282 IF (l_non_reval_acct_amt_total + l_reval_acct_amt_total)
3283 > (l_cover_acct_func_amt)
3284 THEN
3285 -- IF l_debug_mode = 'Y' THEN
3286 -- Output_Debug (' IGCCREPB -- Populating error for amounts ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3287 -- END IF;
3288 -- bug 3199488, start block
3289 IF (l_state_level >= l_debug_level) THEN
3290 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg45',
3291 ' IGCCREPB -- Populating error for amounts ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3292 END IF;
3293 -- bug 3199488, end block
3294
3295 populate_errors(l_cc_headers_rec.cc_header_id,
3296 p_process_phase,
3297 p_currency_code,
3298 p_rate_type,
3299 l_sob_id,
3300 l_org_id,
3301 l_request_id1);
3302 EXIT;
3303 END IF;
3304 -- IF l_debug_mode = 'Y' THEN
3305 -- Output_Debug (' IGCCREPB -- Getting PF Lines ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3306 -- END IF;
3307
3308 -- bug 3199488, start block
3309 IF (l_state_level >= l_debug_level) THEN
3310 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg46',
3311 ' IGCCREPB -- Getting PF Lines ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3312 END IF;
3313 -- bug 3199488, end block
3314
3315 OPEN c_pf_lines(l_cc_acct_lines_rec.cc_acct_line_id);
3316 LOOP
3317 FETCH c_pf_lines INTO l_cc_pmt_fcst_rec;
3318 EXIT WHEN c_pf_lines%NOTFOUND;
3319
3320 l_non_reval_pf_amt_total := 0;
3321 l_reval_pf_amt_total := 0;
3322 l_cover_pf_func_amt := 0;
3323
3324 /* Single release revaluation */
3325 IF (l_cc_headers_rec.cc_header_id <> NVL(p_cc_header_id,-9999) )
3326 AND (p_cc_header_id IS NOT NULL)
3327 THEN
3328 -- IF l_debug_mode = 'Y' THEN
3329 -- Output_Debug (' IGCCREPB -- Single Release revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3330 -- END IF;
3331
3332 -- bug 3199488, start block
3333 IF (l_state_level >= l_debug_level) THEN
3334 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg47',
3335 ' IGCCREPB -- Single Release revaluation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3336 END IF;
3337 -- bug 3199488, end block
3338
3339 BEGIN
3340 SELECT NVL(SUM(NVL(CC_DET_PF_FUNC_AMT,0)),0)
3341 INTO l_non_reval_pf_amt_total
3342 FROM igc_cc_det_pf a,
3343 igc_cc_acct_lines b,
3344 igc_cc_headers c
3345 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3346 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3347 a.cc_acct_line_id = b.cc_acct_line_id AND
3348 b.cc_header_id = c.cc_header_id AND
3349 NVL(c.parent_header_id,0) = l_cc_header_id AND
3350 c.cc_header_id <> p_cc_header_id;
3351 EXCEPTION
3352 WHEN NO_DATA_FOUND
3353 THEN
3354 -- IF l_debug_mode = 'Y' THEN
3355 -- Output_Debug (' IGCCREPB -- Non reval PF amount 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3356 -- END IF;
3357 -- bug 3199488, start block
3358 IF (l_state_level >= l_debug_level) THEN
3359 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg48',
3360 ' IGCCREPB -- Non reval PF amount 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3361 END IF;
3362 -- bug 3199488, end block
3363 l_non_reval_pf_amt_total := 0;
3364 END;
3365
3366
3367 BEGIN
3368 -- Replaced the view igc_cc_det_pf_v with
3369 -- igc_cc_det_pf.
3370 -- Also replaced the following line.
3371 -- ( NVL(a.cc_det_pf_func_amt,0) - a.cc_det_pf_func_billed_amt
3372
3373 SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
3374 (
3375 (
3376 ( NVL(a.cc_det_pf_func_amt,0) -
3377 NVL(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),0)
3378 ) / c.conversion_rate
3379 ) * (l_rate - c.conversion_rate)
3380 )
3381 )
3382 INTO l_reval_pf_amt_total
3383 FROM igc_cc_det_pf a,
3384 igc_cc_acct_lines b,
3385 igc_cc_headers c
3386 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3387 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3388 a.cc_acct_line_id = b.cc_acct_line_id AND
3389 b.cc_header_id = c.cc_header_id AND
3390 c.org_id = l_org_id AND
3391 c.cc_header_id = p_cc_header_id;
3392 EXCEPTION
3393 WHEN NO_DATA_FOUND
3394 THEN
3395 -- IF l_debug_mode = 'Y' THEN
3396 -- Output_Debug (' IGCCREPB -- reval PF amount 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3397 -- END IF;
3398 -- bug 3199488, start block
3399 IF (l_state_level >= l_debug_level) THEN
3400 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg49', ' IGCCREPB -- reval PF amount 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3401 END IF;
3402 -- bug 3199488, end block
3403
3404 l_reval_pf_amt_total := 0;
3405 END;
3406
3407 l_cover_pf_func_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_amt;
3408
3409 END IF;
3410
3411 /* Cover and release revaluation */
3412 IF (p_cc_header_id IS NOT NULL) AND (l_validate_only = 'N') AND
3413 (l_cc_headers_rec.cc_header_id = NVL(p_cc_header_id,-9999) )
3414 THEN
3415 -- IF l_debug_mode = 'Y' THEN
3416 -- Output_Debug (' IGCCREPB -- Cover AND release reval ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3417 -- END IF;
3418
3419 -- bug 3199488, start block
3420 IF (l_state_level >= l_debug_level) THEN
3421 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg50',
3422 ' IGCCREPB -- Cover AND release reval ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3423 END IF;
3424 -- bug 3199488, end block
3425
3426 BEGIN
3427 SELECT SUM(NVL(CC_DET_PF_FUNC_AMT,0))
3428 INTO l_non_reval_pf_amt_total
3429 FROM igc_cc_det_pf a,
3430 igc_cc_acct_lines b,
3431 igc_cc_headers c
3432 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3433 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3434 NVL(c.parent_header_id,0) = l_cc_header_id AND
3435 a.cc_acct_line_id = b.cc_acct_line_id AND
3436 b.cc_header_id = c.cc_header_id AND
3437 ( (c.currency_code = p_currency_code AND c.conversion_type <> p_rate_type) OR
3438 (c.currency_code <> p_currency_code) ) ;
3439 EXCEPTION
3440 WHEN NO_DATA_FOUND
3441 THEN
3442 -- IF l_debug_mode = 'Y' THEN
3443 -- Output_Debug (' IGCCREPB -- NON reval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3444 -- END IF;
3445
3446 -- bug 3199488, start block
3447 IF (l_state_level >= l_debug_level) THEN
3448 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg51',' IGCCREPB -- NON reval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3449 END IF;
3450 -- bug 3199488, end block
3451
3452 l_non_reval_pf_amt_total := 0;
3453 END;
3454
3455
3456 BEGIN
3457 -- Replaced view igc_cc_det_pf_v with
3458 -- igc_cc_det_pf. Also replaced the following
3459 -- line.
3460 -- ( NVL(a.cc_det_pf_func_amt,0) - a.cc_det_pf_func_billed_amt
3461 SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
3462 (
3463 (
3464 ( NVL(a.cc_det_pf_func_amt,0) -
3465 NVL(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),0)
3466 ) / c.conversion_rate
3467 ) * (l_rate - c.conversion_rate)
3468 )
3469 )
3470 INTO l_reval_pf_amt_total
3471 FROM igc_cc_det_pf a,
3472 igc_cc_acct_lines b,
3473 igc_cc_headers c
3474 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3475 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3476 NVL(c.parent_header_id,0) = l_cc_header_id AND
3477 a.cc_acct_line_id = b.cc_acct_line_id AND
3478 b.cc_header_id = c.cc_header_id AND
3479 ( c.currency_code = p_currency_code AND
3480 c.conversion_type = p_rate_type) ;
3481 EXCEPTION
3482 WHEN NO_DATA_FOUND
3483 THEN
3484 -- IF l_debug_mode = 'Y' THEN
3485 -- Output_Debug (' IGCCREPB -- reval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3486 -- END IF;
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_revalue_process_pkg.revalue_main.Msg52',' IGCCREPB -- reval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3490 END IF;
3491 -- bug 3199488, end block
3492 l_reval_pf_amt_total := 0;
3493 END;
3494
3495 l_cover_pf_func_amt :=
3496 ( l_cc_pmt_fcst_rec.cc_det_pf_func_amt +
3497 ( ( ( l_cc_pmt_fcst_rec.cc_det_pf_func_amt -
3498 l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt
3499 ) / l_cc_headers_rec.conversion_rate
3500 ) * (l_rate - l_cc_headers_rec.conversion_rate )
3501 )
3502 );
3503
3504 END IF;
3505
3506 /* Release revaluation only */
3507 IF (p_cc_header_id IS NULL) AND (l_validate_only = 'Y')
3508 THEN
3509 -- IF l_debug_mode = 'Y' THEN
3510 -- Output_Debug (' IGCCREPB -- Release REVAL Only ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3511 -- END IF;
3512 -- bug 3199488, start block
3513 IF (l_state_level >= l_debug_level) THEN
3514 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg53',
3515 ' IGCCREPB -- Release REVAL Only ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3516 END IF;
3517 -- bug 3199488, end block
3518
3519 BEGIN
3520 SELECT NVL(SUM(NVL(CC_DET_PF_FUNC_AMT,0)),0)
3521 INTO l_non_reval_pf_amt_total
3522 FROM igc_cc_det_pf a,
3523 igc_cc_acct_lines b,
3524 igc_cc_headers c
3525 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3526 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3527 NVL(c.parent_header_id,0) = l_cc_header_id AND
3528 a.cc_acct_line_id = b.cc_acct_line_id AND
3529 b.cc_header_id = c.cc_header_id AND
3530 ( (c.currency_code = p_currency_code AND c.conversion_type <> p_rate_type) OR
3531 (c.currency_code <> p_currency_code) ) ;
3532 EXCEPTION
3533 WHEN NO_DATA_FOUND
3534 THEN
3535 -- IF l_debug_mode = 'Y' THEN
3536 -- Output_Debug (' IGCCREPB -- NON raval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3537 -- END IF;
3538 -- bug 3199488, start block
3539 IF (l_state_level >= l_debug_level) THEN
3540 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg54',
3541 ' IGCCREPB -- Non raval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3542 END IF;
3543 -- bug 3199488, end block
3544 l_non_reval_pf_amt_total := 0;
3545 END;
3546
3547
3548 BEGIN
3549
3550 -- Replaced the view igc_cc_det_pf_v with
3551 -- igc_cc_det_pf and replaced the
3552 -- following line.
3553 -- ( NVL(a.cc_det_pf_func_amt,0) - a.cc_det_pf_func_billed_amt
3554 SELECT SUM( NVL(a.cc_det_pf_func_amt,0) +
3555 (
3556 (
3557 ( NVL(a.cc_det_pf_func_amt,0) -
3558 Nvl(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),0)
3559 ) / c.conversion_rate
3560 ) * (l_rate - c.conversion_rate)
3561 )
3562 )
3563 INTO l_reval_pf_amt_total
3564 FROM igc_cc_det_pf a,
3565 igc_cc_acct_lines b,
3566 igc_cc_headers c
3567 WHERE NVL(a.parent_acct_line_id,0) = l_cc_acct_lines_rec.cc_acct_line_id AND
3568 NVL(a.parent_det_pf_line_id,0) = l_cc_pmt_fcst_rec.cc_det_pf_line_id AND
3569 NVL(c.parent_header_id,0) = l_cc_header_id AND
3570 a.cc_acct_line_id = b.cc_acct_line_id AND
3571 b.cc_header_id = c.cc_header_id AND
3572 ( c.currency_code = p_currency_code AND
3573 c.conversion_type = p_rate_type) ;
3574 EXCEPTION
3575 WHEN NO_DATA_FOUND
3576 THEN
3577 -- IF l_debug_mode = 'Y' THEN
3578 -- Output_Debug (' IGCCREPB -- raval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3579 -- END IF;
3580 -- bug 3199488, start block
3581 IF (l_state_level >= l_debug_level) THEN
3582 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg55',
3583 ' IGCCREPB -- Non raval PF amt 0 ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3584 END IF;
3585 -- bug 3199488, end block
3586 l_reval_pf_amt_total := 0;
3587 END;
3588
3589 l_cover_pf_func_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_amt;
3590
3591 END IF;
3592
3593 IF (l_non_reval_pf_amt_total + l_reval_pf_amt_total)
3594 > (l_cover_pf_func_amt)
3595 THEN
3596 /* Update validation_status to 'F' in temporary table for releases */
3597 -- IF l_debug_mode = 'Y' THEN
3598 -- Output_Debug (' IGCCREPB -- Populate error PF amt ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3599 -- END IF;
3600
3601 -- bug 3199488, start block
3602 IF (l_state_level >= l_debug_level) THEN
3603 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg56',
3604 ' IGCCREPB -- Populate error PF amt ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3605 END IF;
3606 -- bug 3199488, end block
3607
3608 populate_errors(l_cc_headers_rec.cc_header_id,
3609 p_process_phase,
3610 p_currency_code,
3611 p_rate_type,
3612 l_sob_id,
3613 l_org_id,
3614 l_request_id1);
3615 EXIT;
3616 END IF;
3617
3618 END LOOP; /* payment forecast */
3619 CLOSE c_pf_lines;
3620 -- IF l_debug_mode = 'Y' THEN
3621 -- Output_Debug (' IGCCREPB -- Done with PF line cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3622 -- END IF;
3623
3624 -- bug 3199488, start block
3625 IF (l_state_level >= l_debug_level) THEN
3626 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg57',
3627 ' IGCCREPB -- Done with PF line cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3628 END IF;
3629 -- bug 3199488, end block
3630
3631 END LOOP; /* Account Lines */
3632 CLOSE c_acct_lines;
3633 -- IF l_debug_mode = 'Y' THEN
3634 -- Output_Debug (' IGCCREPB -- Done with ACCT line cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3635 -- END IF;
3636
3637 -- bug 3199488, start block
3638 IF (l_state_level >= l_debug_level) THEN
3639 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg58',
3640 ' IGCCREPB -- Done with ACCT line cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3641 END IF;
3642 -- bug 3199488, end block
3643
3644 END LOOP; /* Cover Contract Commitments */
3645
3646 CLOSE c_cover_reval_data;
3647 -- IF l_debug_mode = 'Y' THEN
3648 -- Output_Debug (' IGCCREPB -- Done with cover reval cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3649 -- END IF;
3650
3651 -- bug 3199488, start block
3652 IF (l_state_level >= l_debug_level) THEN
3653 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg59',
3654 ' IGCCREPB -- Done with cover reval cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3655 END IF;
3656 -- bug 3199488, end block
3657
3658 /* Validate Contract Commitments */
3659
3660 /* Validate all contract commitments subject to re_valuation */
3661 -- IF l_debug_mode = 'Y' THEN
3662 -- Output_Debug (' IGCCREPB -- Validate contract commitment cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3663 -- END IF;
3664
3665 -- bug 3199488, start block
3666 IF (l_state_level >= l_debug_level) THEN
3667 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg60',
3668 ' IGCCREPB -- Validate contract commitment cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3669 END IF;
3670 -- bug 3199488, end block
3671
3672 OPEN c_reval_data(l_request_id1);
3673 LOOP
3674 FETCH c_reval_data
3675 INTO l_cc_header_id;
3676
3677 EXIT WHEN c_reval_data%NOTFOUND;
3678
3679 /* Get Contract Details */
3680 SELECT *
3681 INTO l_cc_headers_rec
3682 FROM igc_cc_headers
3683 WHERE cc_header_id = l_cc_header_id;
3684
3685 l_validation_status := 'P';
3686
3687 /* Validate Contract Commitment */
3688
3689 IF (l_cc_headers_rec.cc_type = 'C')
3690 THEN
3691 SELECT validation_status
3692 INTO l_curr_validation_status
3693 FROM igc_cc_process_data
3694 WHERE cc_header_id = l_cc_header_id AND
3695 request_id = l_request_id1;
3696 END IF;
3697
3698 -- IF l_debug_mode = 'Y' THEN
3699 -- Output_Debug (' IGCCREPB -- calling Validate CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3700 -- END IF;
3701
3702 -- bug 3199488, start block
3703 IF (l_state_level >= l_debug_level) THEN
3704 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg61',
3705 ' IGCCREPB -- calling Validate CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3706 END IF;
3707 -- bug 3199488, end block
3708
3709 l_validation_status := IGC_CC_REP_YEP_PVT.validate_cc(p_process_phase => p_process_phase,
3710 p_process_type => 'R',
3711 p_cc_header_id =>l_cc_header_id,
3712 p_sob_id => l_sob_id,
3713 p_org_id => l_org_id,
3714 p_year => NULL,
3715 p_prov_enc_on => l_prov_enc_on,
3716 p_request_id => l_request_id1);
3717
3718 -- IF l_debug_mode = 'Y' THEN
3719 -- Output_Debug (' IGCCREPB -- Done calling Validate CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3720 -- END IF;
3721
3722 -- bug 3199488, start block
3723 IF (l_state_level >= l_debug_level) THEN
3724 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg62',
3725 ' IGCCREPB -- Done calling Validate CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3726 END IF;
3727 -- bug 3199488, end block
3728
3729 IF (l_cc_headers_rec.cc_type = 'C')
3730 THEN
3731 IF (l_curr_validation_status <> 'I')
3732 THEN
3733 l_validation_status := l_curr_validation_status;
3734 END IF;
3735 END IF;
3736
3737 /* Preliminary phase */
3738 -- IF l_debug_mode = 'Y' THEN
3739 -- Output_Debug (' IGCCREPB -- Checking Preliminary Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3740 -- END IF;
3741
3742 -- bug 3199488, start block
3743 IF (l_state_level >= l_debug_level) THEN
3744 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg63',
3745 ' IGCCREPB -- Checking Preliminary Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3746 END IF;
3747 -- bug 3199488, end block
3748
3749 IF (p_process_phase = 'P')
3750 THEN
3751 /* Update validation status in temporary table*/
3752
3753 -- IF l_debug_mode = 'Y' THEN
3754 -- Output_Debug (' IGCCREPB -- Preliminary Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3755 -- END IF;
3756
3757 -- bug 3199488, start block
3758 IF (l_state_level >= l_debug_level) THEN
3759 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg64',
3760 ' IGCCREPB -- Preliminary Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3761 END IF;
3762 -- bug 3199488, end block
3763
3764 BEGIN
3765 UPDATE igc_cc_process_data
3766 SET
3767 validation_status = l_validation_Status,
3768 processed = 'Y'
3769 WHERE
3770 request_id = l_request_id1 AND
3771 cc_header_id = l_cc_header_id;
3772 EXCEPTION
3773 WHEN NO_DATA_FOUND
3774 THEN
3775 NULL;
3776 END;
3777 -- IF l_debug_mode = 'Y' THEN
3778 -- Output_Debug (' IGCCREPB -- Checking Cover header type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3779 -- END IF;
3780
3781 -- bug 3199488, start block
3782 IF (l_state_level >= l_debug_level) THEN
3783 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg65',
3784 ' IGCCREPB -- Checking Cover header type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3785 END IF;
3786 -- bug 3199488, end block
3787
3788 IF (l_cc_headers_rec.cc_type = 'C')
3789 THEN
3790 UPDATE igc_cc_process_data
3791 SET
3792 validation_status = l_validation_Status,
3793 processed = 'Y'
3794 WHERE
3795 request_id = l_request_id1 AND
3796 cc_header_id IN (SELECT cc_header_id
3797 FROM igc_cc_headers
3798 WHERE NVL(parent_header_id,0) = l_cc_header_id);
3799
3800 END IF;
3801
3802 COMMIT;
3803 -- IF l_debug_mode = 'Y' THEN
3804 -- Output_Debug (' IGCCREPB -- Done prelim phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3805 -- END IF;
3806
3807 -- bug 3199488, start block
3808 IF (l_state_level >= l_debug_level) THEN
3809 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg66',
3810 ' IGCCREPB -- Done prelim phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3811 END IF;
3812 -- bug 3199488, end block
3813
3814 END IF; /* Preliminary Phase */
3815
3816 /* Final phase */
3817 -- IF l_debug_mode = 'Y' THEN
3818 -- Output_Debug (' IGCCREPB -- Checking if Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3819 -- END IF;
3820
3821 -- bug 3199488, start block
3822 IF (l_state_level >= l_debug_level) THEN
3823 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg67',
3824 ' IGCCREPB -- Checking if Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3825 END IF;
3826 -- bug 3199488, end block
3827
3828 IF (p_process_phase = 'F')
3829 THEN
3830 /* Passed Validation */
3831 -- IF l_debug_mode = 'Y' THEN
3832 -- Output_Debug (' IGCCREPB -- Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3833 -- END IF;
3834
3835 -- bug 3199488, start block
3836 IF (l_state_level >= l_debug_level) THEN
3837 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg68',
3838 ' IGCCREPB -- Final Phase ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3839 END IF;
3840 -- bug 3199488, end block
3841
3842 IF (l_validation_status = 'P')
3843 THEN
3844 /* Update validation status, store old status in temporary table */
3845 -- IF l_debug_mode = 'Y' THEN
3846 -- Output_Debug (' IGCCREPB -- Passed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3847 -- END IF;
3848
3849 -- bug 3199488, start block
3850 IF (l_state_level >= l_debug_level) THEN
3851 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg69',
3852 ' IGCCREPB -- Passed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3853 END IF;
3854 -- bug 3199488, end block
3855
3856 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
3857 THEN
3858 -- IF l_debug_mode = 'Y' THEN
3859 -- Output_Debug (' IGCCREPB -- Updating process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3860 -- END IF;
3861
3862 -- bug 3199488, start block
3863 IF (l_state_level >= l_debug_level) THEN
3864 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg70',
3865 ' IGCCREPB -- Updating process data table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3866 END IF;
3867 -- bug 3199488, end block
3868
3869 BEGIN
3870 UPDATE igc_cc_process_data
3871 SET
3872 validation_status = l_validation_Status,
3873 old_approval_status = l_cc_headers_rec.cc_apprvl_status
3874 WHERE
3875 request_id = l_request_id1 AND
3876 cc_header_id = l_cc_header_id;
3877
3878 EXCEPTION
3879 WHEN NO_DATA_FOUND
3880 THEN
3881 NULL;
3882 END;
3883 END IF;
3884
3885 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
3886 THEN
3887 -- IF l_debug_mode = 'Y' THEN
3888 -- Output_Debug (' IGCCREPB -- Updating Headers table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3889 -- END IF;
3890 -- bug 3199488, start block
3891 IF (l_state_level >= l_debug_level) THEN
3892 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg71',
3893 ' IGCCREPB -- Updating Headers table ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3894 END IF;
3895 -- bug 3199488, end block
3896 UPDATE igc_cc_headers
3897 SET cc_apprvl_status = 'IP'
3898 WHERE cc_header_id = l_cc_header_id;
3899 END IF;
3900
3901 /* Added the following code for bug 1613811 */
3902 /* Change begin */
3903 -- IF l_debug_mode = 'Y' THEN
3904 -- Output_Debug (' IGCCREPB -- Checking CC type Standard ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3905 -- END IF;
3906
3907 -- bug 3199488, start block
3908 IF (l_state_level >= l_debug_level) THEN
3909 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg72',
3910 ' IGCCREPB -- Checking CC type Standard ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3911 END IF;
3912 -- bug 3199488, end block
3913
3914 IF (l_cc_headers_rec.cc_type = 'S')
3915 THEN
3916 -- IF l_debug_mode = 'Y' THEN
3917 -- Output_Debug (' IGCCREPB -- Standard CC getting PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3918 -- END IF;
3919
3920 -- bug 3199488, start block
3921 IF (l_state_level >= l_debug_level) THEN
3922 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg73',
3923 ' IGCCREPB -- Standard CC getting PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3924 END IF;
3925 -- bug 3199488, end block
3926
3927 l_po_count := 0;
3928
3929 BEGIN
3930 SELECT count(po_header_id)
3931 INTO l_po_count
3932 FROM po_headers_all
3933 WHERE segment1 = l_cc_headers_rec.cc_num AND
3934 org_id = l_cc_headers_rec.org_id AND
3935 type_lookup_code = 'STANDARD' ;
3936 EXCEPTION
3937 WHEN NO_DATA_FOUND
3938 THEN
3939 l_po_count := 0;
3940 END;
3941
3942 -- IF l_debug_mode = 'Y' THEN
3943 -- Output_Debug (' IGCCREPB -- Checking PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3944 -- END IF;
3945 -- bug 3199488, start block
3946 IF (l_state_level >= l_debug_level) THEN
3947 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg74',
3948 ' IGCCREPB -- Checking PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3949 END IF;
3950 -- bug 3199488, end block
3951 IF (l_po_count = 1)
3952 THEN
3953
3954 BEGIN
3955 -- IF l_debug_mode = 'Y' THEN
3956 -- Output_Debug (' IGCCREPB -- Updating PO Headers ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3957 -- END IF;
3958 -- bug 3199488, start block
3959 IF (l_state_level >= l_debug_level) THEN
3960 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg75',' IGCCREPB -- Updating PO Headers ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3961 END IF;
3962 -- bug 3199488, end block
3963 UPDATE po_headers_all
3964 SET approved_flag = 'N'
3965 WHERE segment1 = l_cc_headers_rec.cc_num AND
3966 org_id = l_cc_headers_rec.org_id AND
3967 type_lookup_code = 'STANDARD' AND
3968 approved_flag = 'Y';
3969 END;
3970 END IF;
3971
3972 END IF;
3973 /* Change end */
3974
3975 -- IF l_debug_mode = 'Y' THEN
3976 -- Output_Debug (' IGCCREPB -- Checking if Cover Type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3977 -- END IF;
3978
3979 -- bug 3199488, start block
3980 IF (l_state_level >= l_debug_level) THEN
3981 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg76',
3982 ' IGCCREPB -- Checking if Cover Type ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3983 END IF;
3984 -- bug 3199488, end block
3985
3986 IF (l_cc_headers_rec.cc_type = 'C')
3987 THEN
3988 -- IF l_debug_mode = 'Y' THEN
3989 -- Output_Debug (' IGCCREPB -- Obtain all releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3990 -- END IF;
3991
3992 -- bug 3199488, start block
3993 IF (l_state_level >= l_debug_level) THEN
3994 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg77',
3995 ' IGCCREPB -- Obtain all releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
3996 END IF;
3997 -- bug 3199488, end block
3998
3999 OPEN c_all_releases1(l_cc_header_id);
4000 LOOP
4001 FETCH c_all_releases1 INTO l_rel_cc_headers_rec;
4002 EXIT WHEN c_all_releases1%NOTFOUND;
4003
4004 -- IF l_debug_mode = 'Y' THEN
4005 -- Output_Debug (' IGCCREPB -- Update process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4006 -- END IF;
4007
4008 -- bug 3199488, start block
4009 IF (l_state_level >= l_debug_level) THEN
4010 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg78',' IGCCREPB -- Update process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4011 END IF;
4012 -- bug 3199488, end block
4013
4014 UPDATE igc_cc_process_data
4015 SET
4016 validation_status = l_validation_Status,
4017 old_approval_status = l_rel_cc_headers_rec.cc_apprvl_status
4018 WHERE request_id = l_request_id1 AND
4019 cc_header_id = l_rel_cc_headers_rec.cc_header_id;
4020
4021 -- IF l_debug_mode = 'Y' THEN
4022 -- Output_Debug (' IGCCREPB -- Update Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4023 -- END IF;
4024
4025 -- bug 3199488, start block
4026 IF (l_state_level >= l_debug_level) THEN
4027 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg79',' IGCCREPB -- Update Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4028 END IF;
4029 -- bug 3199488, end block
4030
4031 UPDATE igc_cc_headers
4032 SET cc_apprvl_status = 'IP'
4033 WHERE cc_header_id = l_rel_cc_headers_rec.cc_header_id ;
4034
4035 l_po_count := 0;
4036
4037 -- IF l_debug_mode = 'Y' THEN
4038 -- Output_Debug (' IGCCREPB -- Getting PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4039 -- END IF;
4040
4041 -- bug 3199488, start block
4042 IF (l_state_level >= l_debug_level) THEN
4043 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg80',' IGCCREPB -- Getting PO line count ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4044 END IF;
4045 -- bug 3199488, end block
4046
4047 BEGIN
4048 SELECT count(po_header_id)
4049 INTO l_po_count
4050 FROM po_headers_all
4051 WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
4052 org_id = l_rel_cc_headers_rec.org_id AND
4053 type_lookup_code = 'STANDARD' ;
4054 EXCEPTION
4055 WHEN NO_DATA_FOUND
4056 THEN
4057 l_po_count := 0;
4058 END;
4059
4060 IF (l_po_count = 1)
4061 THEN
4062 -- IF l_debug_mode = 'Y' THEN
4063 -- Output_Debug (' IGCCREPB -- Update PO Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4064 -- END IF;
4065
4066 -- bug 3199488, start block
4067 IF (l_state_level >= l_debug_level) THEN
4068 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg81',' IGCCREPB -- Update PO Header data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4069 END IF;
4070 -- bug 3199488, end block
4071
4072 BEGIN
4073 UPDATE po_headers_all
4074 SET approved_flag = 'N'
4075 WHERE segment1 = l_rel_cc_headers_rec.cc_num AND
4076 org_id = l_rel_cc_headers_rec.org_id AND
4077 type_lookup_code = 'STANDARD' AND
4078 /* Changed condition below from approved_flag = Y to approved flag = N to fix bug 1613811 */
4079 approved_flag = 'Y';
4080 END;
4081 END IF;
4082
4083 END LOOP;
4084
4085 CLOSE c_all_releases1;
4086 -- IF l_debug_mode = 'Y' THEN
4087 -- Output_Debug (' IGCCREPB -- End Releases Loop ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4088 -- END IF;
4089
4090 -- bug 3199488, start block
4091 IF (l_state_level >= l_debug_level) THEN
4092 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg82',
4093 ' IGCCREPB -- End Releases Loop ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4094 END IF;
4095 -- bug 3199488, end block
4096
4097 END IF;
4098
4099
4100 ELSIF (l_validation_status = 'F') /* Failed Validation */
4101 THEN
4102 /* Update validation status, in temporary table*/
4103 -- IF l_debug_mode = 'Y' THEN
4104 -- Output_Debug (' IGCCREPB -- Failed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4105 -- END IF;
4106
4107 -- bug 3199488, start block
4108 IF (l_state_level >= l_debug_level) THEN
4109 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg83',
4110 ' IGCCREPB -- Failed validation ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4111 END IF;
4112 -- bug 3199488, end block
4113
4114 IF (l_cc_headers_rec.cc_type = 'C') OR (l_cc_headers_rec.cc_type = 'S')
4115 THEN
4116 -- IF l_debug_mode = 'Y' THEN
4117 -- Output_Debug (' IGCCREPB -- Updating Cover or Standard in process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4118 -- END IF;
4119 -- bug 3199488, start block
4120 IF (l_state_level >= l_debug_level) THEN
4121 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg84',
4122 ' IGCCREPB -- Updating cover or Standard in process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4123 END IF;
4124 -- bug 3199488, end block
4125
4126 UPDATE igc_cc_process_data
4127 SET
4128 validation_status = l_validation_Status ,
4129 processed = 'Y'
4130 WHERE request_id = l_request_id1 AND
4131 cc_header_id = l_cc_header_id;
4132 END IF;
4133
4134 IF (l_cc_headers_rec.cc_type = 'C')
4135 THEN
4136 -- IF l_debug_mode = 'Y' THEN
4137 -- Output_Debug (' IGCCREPB -- Updating Cover process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4138 -- END IF;
4139
4140 -- bug 3199488, start block
4141 IF (l_state_level >= l_debug_level) THEN
4142 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg85',
4143 ' IGCCREPB -- Updating Cover process data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4144 END IF;
4145 -- bug 3199488, end block
4146
4147 UPDATE igc_cc_process_data
4148 SET
4149 validation_status = l_validation_Status ,
4150 processed = 'Y'
4151 WHERE request_id = l_request_id1 AND
4152 cc_header_id IN (SELECT cc_header_id
4153 FROM igc_cc_headers
4154 WHERE NVL(parent_header_id,0) = l_cc_header_id);
4155 END IF;
4156
4157 END IF;
4158
4159 END IF; /* Final Phase */
4160
4161 END LOOP;
4162
4163 CLOSE c_reval_data;
4164 COMMIT;
4165 -- IF l_debug_mode = 'Y' THEN
4166 -- Output_Debug (' IGCCREPB -- Done c_reval_data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4167 -- END IF;
4168
4169 -- bug 3199488, start block
4170 IF (l_state_level >= l_debug_level) THEN
4171 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg86',
4172 ' IGCCREPB -- Done c_reval data ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4173 END IF;
4174 -- bug 3199488, end block
4175
4176 /* End Validation Phase */
4177
4178 /* Begin Reservation phase */
4179 -- IF l_debug_mode = 'Y' THEN
4180 -- Output_Debug (' IGCCREPB -- Check if reservation phase is to begin ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4181 -- END IF;
4182
4183 -- bug 3199488, start block
4184 IF (l_state_level >= l_debug_level) THEN
4185 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg87',
4186 ' IGCCREPB -- Check if reservation phase is to begin ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4187 END IF;
4188 -- bug 3199488, end block
4189
4190 IF (p_process_phase = 'F')
4191 THEN
4192 /* Perform Funds Reservation for Contract Commitments */
4193
4194 -- IF l_debug_mode = 'Y' THEN
4195 -- Output_Debug (' IGCCREPB -- reservation phase begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4196 -- END IF;
4197
4198 -- bug 3199488, start block
4199 IF (l_state_level >= l_debug_level) THEN
4200 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg88',
4201 ' IGCCREPB -- reservation phase begins ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4202 END IF;
4203 -- bug 3199488, end block
4204
4205 OPEN c_reval_data(l_request_id1);
4206 LOOP
4207 FETCH c_reval_data INTO l_cc_header_id;
4208 EXIT WHEN c_reval_data%NOTFOUND;
4209
4210 SELECT *
4211 INTO l_cc_headers_rec
4212 FROM igc_cc_headers
4213 WHERE cc_header_id = l_cc_header_id;
4214
4215
4216 SELECT validation_status
4217 INTO l_validation_status
4218 FROM igc_cc_process_data
4219 WHERE request_id = l_request_id1 AND
4220 cc_header_id = l_cc_header_id ;
4221
4222 IF (l_validation_status = 'P')
4223 THEN
4224 -- IF l_debug_mode = 'Y' THEN
4225 -- Output_Debug (' IGCCREPB -- Validation status P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4226 -- END IF;
4227
4228 -- bug 3199488, start block
4229 IF (l_state_level >= l_debug_level) THEN
4230 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg89',
4231 ' IGCCREPB -- Validation status P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4232 END IF;
4233 -- bug 3199488, end block
4234
4235 l_validate_only := 'Y';
4236
4237 SELECT validate_only
4238 INTO l_validate_only
4239 FROM igc_cc_process_data
4240 WHERE request_id = l_request_id1 AND
4241 cc_header_id = l_cc_headers_rec.cc_header_id;
4242
4243
4244 l_reservation_status := 'P';
4245
4246 /* Perform funds reservation in Forced mode for Contract Commitment */
4247
4248
4249 IF ( ((l_cc_headers_rec.cc_type = 'C') AND (l_validate_only = 'N') ) OR
4250 (l_cc_headers_rec.cc_type = 'S') )
4251 THEN
4252 -- IF l_debug_mode = 'Y' THEN
4253 -- Output_Debug (' IGCCREPB -- Reservation in Forced mode beginning ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4254 -- END IF;
4255
4256 -- bug 3199488, start block
4257 IF (l_state_level >= l_debug_level) THEN
4258 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg90',
4259 ' IGCCREPB -- Reservation in Forced mode beginning ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4260 END IF;
4261 -- bug 3199488, end block
4262
4263 IF (l_sbc_on = TRUE)
4264 THEN
4265 IF ( ( (l_cc_headers_rec.cc_state = 'PR')
4266 OR (l_cc_headers_rec.cc_state = 'CL') )
4267 AND (l_prov_enc_on = TRUE)
4268 )
4269 OR
4270 ( ( (l_cc_headers_rec.cc_state = 'CM')
4271 OR (l_cc_headers_rec.cc_state = 'CT') )
4272 AND (l_conf_enc_on = TRUE)
4273 )
4274 THEN
4275 -- IF l_debug_mode = 'Y' THEN
4276 -- Output_Debug (' IGCCREPB -- Encumber CC call ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4277 -- END IF;
4278
4279 -- bug 3199488, start block
4280 IF (l_state_level >= l_debug_level) THEN
4281 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg91', ' IGCCREPB -- Encumber CC call ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4282 END IF;
4283 -- bug 3199488, end block
4284
4285 -- fix bug 2124590 start 1
4286 UPDATE igc_cc_headers
4287 SET cc_version_num = cc_version_num + 1
4288 WHERE cc_header_id = l_cc_header_id;
4289
4290 COMMIT;
4291 -- fix bug 2124590 end 1
4292
4293 l_reservation_status :=
4294 IGC_CC_REP_YEP_PVT.Encumber_CC
4295 (
4296 p_process_type => 'R',
4297 p_cc_header_id => l_cc_header_id,
4298 p_sbc_on => l_sbc_on,
4299 p_cbc_on => l_cbc_on,
4300 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
4301 -- p_cc_prov_enc_type_id => l_cc_prov_enc_type_id,
4302 -- p_cc_conf_enc_type_id => l_cc_conf_enc_type_id,
4303 -- p_req_encumbrance_type_id => l_req_encumbrance_type_id,
4304 -- p_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
4305 p_currency_code => l_currency_code,
4306 p_yr_start_date => NULL,
4307 p_yr_end_date => NULL,
4308 p_yr_end_cr_date => NULL,
4309 p_yr_end_dr_date => NULL,
4310 p_rate_date => l_rate_date,
4311 p_rate => l_rate,
4312 p_revalue_fix_date => NULL );
4313
4314 -- fix bug 2124590 start 2
4315 UPDATE igc_cc_headers
4316 SET cc_version_num = cc_version_num - 1
4317 WHERE cc_header_id = l_cc_header_id;
4318
4319 COMMIT;
4320 -- fix bug 2124590 end 2
4321 ELSE
4322 l_reservation_status := 'P';
4323 END IF;
4324 ELSE
4325 l_reservation_status := 'P';
4326 END IF;
4327
4328
4329 END IF;
4330
4331 IF (l_reservation_status = 'F')
4332 THEN
4333
4334 l_approval_status := NULL;
4335
4336 select old_approval_status
4337 into l_approval_status
4338 from igc_cc_process_data
4339 where cc_header_id = l_cc_headers_rec.cc_header_id
4340 and request_id = l_request_id1;
4341
4342 update igc_cc_headers
4343 set cc_apprvl_status = l_approval_status
4344 WHERE cc_header_id = l_cc_headers_rec.cc_header_id;
4345
4346
4347 l_message := NULL;
4348 FND_MESSAGE.SET_NAME('IGC','IGC_CC_ENCUMBRANCE_FAILURE');
4349 l_message := FND_MESSAGE.GET;
4350
4351 -- IF l_debug_mode = 'Y' THEN
4352 -- Output_Debug (' IGCCREPB -- Reval Cover Fail process exceptions ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4353 -- END IF;
4354
4355 -- bug 3199488, start block
4356 IF (l_state_level >= l_debug_level) THEN
4357 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg92',
4358 ' IGCCREPB -- Reval Cover Fail process exceptions ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4359 END IF;
4360 -- bug 3199488, end block
4361
4362 INSERT INTO
4363 igc_cc_process_exceptions
4364 ( process_type,
4365 process_phase,
4366 cc_header_id,
4367 cc_acct_line_id,
4368 cc_det_pf_line_id,
4369 exception_reason,
4370 org_id,
4371 set_of_books_id,
4372 request_id
4373 )
4374 VALUES
4375 (
4376 'R',
4377 'F',
4378 l_cc_headers_rec.cc_header_id,
4379 NULL,
4380 NULL,
4381 l_message,
4382 l_org_id,
4383 l_sob_id,
4384 l_request_id1
4385 );
4386
4387 END IF;
4388
4389 /* Update validation status, in temporary table*/
4390 -- IF l_debug_mode = 'Y' THEN
4391 -- Output_Debug (' IGCCREPB -- Updating process data after encumber CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4392 -- END IF;
4393
4394 -- bug 3199488, start block
4395 IF (l_state_level >= l_debug_level) THEN
4396 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg93',
4397 ' IGCCREPB -- Updating process data after encumber CC ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4398 END IF;
4399 -- bug 3199488, end block
4400
4401 UPDATE igc_cc_process_data
4402 SET
4403 reservation_status = l_reservation_Status
4404 WHERE
4405 request_id = l_request_id1 AND
4406 cc_header_id = l_cc_header_id ;
4407
4408 IF (l_cc_headers_rec.cc_type = 'C')
4409 THEN
4410 -- IF l_debug_mode = 'Y' THEN
4411 -- Output_Debug (' IGCCREPB -- Updating process data after encumber CC for Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4412 -- END IF;
4413
4414 -- bug 3199488, start block
4415 IF (l_state_level >= l_debug_level) THEN
4416 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg94',
4417 ' IGCCREPB -- Updating process data after encumber CC for Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4418 END IF;
4419 -- bug 3199488, end block
4420
4421 UPDATE igc_cc_process_data
4422 SET
4423 reservation_status = l_reservation_Status
4424 WHERE
4425 request_id = l_request_id1 AND
4426 cc_header_id IN (SELECT cc_header_id
4427 FROM igc_cc_headers
4428 WHERE NVL(parent_header_id,0) = l_cc_header_id);
4429 END IF;
4430
4431 COMMIT;
4432
4433 /* Process Cover release */
4434 IF (l_cc_headers_rec.cc_type = 'C')
4435 THEN
4436 -- IF l_debug_mode = 'Y' THEN
4437 -- Output_Debug (' IGCCREPB -- Getting all releases for Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4438 -- END IF;
4439
4440 -- bug 3199488, start block
4441 IF (l_state_level >= l_debug_level) THEN
4442 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg95',
4443 ' IGCCREPB -- Getting all releases for Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4444 END IF;
4445 -- bug 3199488, end block
4446
4447 /* Process the releases */
4448 l_process_flag := 'P';
4449
4450 SAVEPOINT REVALUE5;
4451
4452 OPEN c_all_releases(l_cc_headers_rec.cc_header_id);
4453 LOOP
4454 FETCH c_all_releases INTO l_rel_cc_header_id;
4455 EXIT WHEN c_all_releases%NOTFOUND;
4456
4457
4458 l_validate_only := 'Y';
4459
4460 SELECT validate_only
4461 INTO l_validate_only
4462 FROM igc_cc_process_data
4463 WHERE request_id = l_request_id1 AND
4464 cc_header_id = l_rel_cc_header_id;
4465
4466 l_process_flag := 'P';
4467
4468 IF (l_reservation_status = 'P')
4469 THEN
4470
4471 -- IF l_debug_mode = 'Y' THEN
4472 -- Output_Debug (' IGCCREPB -- Calling reval_update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4473 -- END IF;
4474 -- bug 3199488, start block
4475 IF (l_state_level >= l_debug_level) THEN
4476 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg96',' IGCCREPB -- Calling reval_update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4477 END IF;
4478 -- bug 3199488, end block
4479 l_message := NULL;
4480 l_err_header_id := NULL;
4481 l_err_acct_line_id := NULL;
4482 l_err_det_pf_line_id := NULL;
4483
4484 l_process_flag := reval_update(l_rel_cc_header_id,
4485 l_rate_date,
4486 p_rate,
4487 l_sob_id,
4488 l_org_id,
4489 l_sbc_on,
4490 l_cbc_on,
4491 l_prov_enc_on,
4492 l_conf_enc_on,
4493 l_validate_only,
4494 l_request_id1,
4495 l_message,
4496 l_err_header_id,
4497 l_err_acct_line_id,
4498 l_err_det_pf_line_id);
4499
4500 ELSIF (l_reservation_status = 'F')
4501 THEN
4502 -- IF l_debug_mode = 'Y' THEN
4503 -- Output_Debug (' IGCCREPB -- Calling reval_update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4504 -- END IF;
4505
4506 -- bug 3199488, start block
4507 IF (l_state_level >= l_debug_level) THEN
4508 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg97',' IGCCREPB -- Calling reval_update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4509 END IF;
4510 -- bug 3199488, end block
4511
4512 l_message := NULL;
4513 l_err_header_id := NULL;
4514 l_err_acct_line_id := NULL;
4515 l_err_det_pf_line_id := NULL;
4516
4517 l_process_flag := reval_update(l_rel_cc_header_id,
4518 l_rate_date,
4519 l_rate,
4520 l_sob_id,
4521 l_org_id,
4522 l_sbc_on,
4523 l_cbc_on,
4524 l_prov_enc_on,
4525 l_conf_enc_on,
4526 'Y',
4527 l_request_id1,
4528 l_message,
4529 l_err_header_id,
4530 l_err_acct_line_id,
4531 l_err_det_pf_line_id);
4532 END IF;
4533
4534 IF (l_process_flag = 'F')
4535 THEN
4536 -- IF l_debug_mode = 'Y' THEN
4537 -- Output_Debug (' IGCCREPB -- Rolling back Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4538 -- END IF;
4539 -- bug 3199488, start block
4540 IF (l_state_level >= l_debug_level) THEN
4541 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg98',' IGCCREPB -- Rolling back Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4542 END IF;
4543 -- bug 3199488, end block
4544 EXIT;
4545 END IF;
4546
4547 END LOOP;
4548
4549 CLOSE c_all_releases;
4550 -- IF l_debug_mode = 'Y' THEN
4551 -- Output_Debug (' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4552 -- END IF;
4553
4554 -- bug 3199488, start block
4555 IF (l_state_level >= l_debug_level) THEN
4556 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg99',' IGCCREPB -- Done with releases ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4557 END IF;
4558 -- bug 3199488, end block
4559
4560 /* Process the cover */
4561 -- IF l_debug_mode = 'Y' THEN
4562 -- Output_Debug (' IGCCREPB -- Checking to process Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4563 -- END IF;
4564
4565 -- bug 3199488, start block
4566 IF (l_state_level >= l_debug_level) THEN
4567 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg100',' IGCCREPB -- Checking to process Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4568 END IF;
4569 -- bug 3199488, end block
4570
4571 IF (l_process_flag = 'P')
4572 THEN
4573 l_validate_only := 'Y';
4574
4575 -- IF l_debug_mode = 'Y' THEN
4576 -- Output_Debug (' IGCCREPB -- process Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4577 -- END IF;
4578
4579 -- bug 3199488, start block
4580 IF (l_state_level >= l_debug_level) THEN
4581 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg101','IGCCREPB -- process Cover ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4582 END IF;
4583 -- bug 3199488, end block
4584
4585 SELECT validate_only
4586 INTO l_validate_only
4587 FROM igc_cc_process_data
4588 WHERE request_id = l_request_id1 AND
4589 cc_header_id = l_cc_headers_rec.cc_header_id;
4590
4591
4592 IF (l_reservation_status = 'P')
4593 THEN
4594 -- IF l_debug_mode = 'Y' THEN
4595 -- Output_Debug (' IGCCREPB -- Reval Cover Update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4596 -- END IF;
4597 -- bug 3199488, start block
4598 IF (l_state_level >= l_debug_level) THEN
4599 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg102','IGCCREPB -- Reval Cover Update P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4600 END IF;
4601 -- bug 3199488, end block
4602
4603 l_message := NULL;
4604 l_err_header_id := NULL;
4605 l_err_acct_line_id := NULL;
4606 l_err_det_pf_line_id := NULL;
4607
4608 l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
4609 l_rate_date,
4610 l_rate,
4611 l_sob_id,
4612 l_org_id,
4613 l_sbc_on,
4614 l_cbc_on,
4615 l_prov_enc_on,
4616 l_conf_enc_on,
4617 l_validate_only,
4618 l_request_id1,
4619 l_message,
4620 l_err_header_id,
4621 l_err_acct_line_id,
4622 l_err_det_pf_line_id);
4623 ELSIF (l_reservation_status = 'F')
4624 THEN
4625 -- IF l_debug_mode = 'Y' THEN
4626 -- Output_Debug (' IGCCREPB -- Reval Cover Update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4627 -- END IF;
4628 -- bug 3199488, start block
4629 IF (l_state_level >= l_debug_level) THEN
4630 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg103','IGCCREPB -- Reval Cover Update F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4631 END IF;
4632 -- bug 3199488, end block
4633
4634 l_message := NULL;
4635 l_err_header_id := NULL;
4636 l_err_acct_line_id := NULL;
4637 l_err_det_pf_line_id := NULL;
4638
4639 l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
4640 l_rate_date,
4641 l_rate,
4642 l_sob_id,
4643 l_org_id,
4644 l_sbc_on,
4645 l_cbc_on,
4646 l_prov_enc_on,
4647 l_conf_enc_on,
4648 'Y',
4649 l_request_id1,
4650 l_message,
4651 l_err_header_id,
4652 l_err_acct_line_id,
4653 l_err_det_pf_line_id);
4654 END IF;
4655 END IF;
4656
4657 IF (l_process_flag = 'F')
4658 THEN
4659 -- IF l_debug_mode = 'Y' THEN
4660 -- Output_Debug (' IGCCREPB -- Rollback Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4661 -- END IF;
4662
4663 -- bug 3199488, start block
4664 IF (l_state_level >= l_debug_level) THEN
4665 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg104','IGCCREPB -- Rollback Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4666 END IF;
4667 -- bug 3199488, end block
4668
4669 ROLLBACK TO REVALUE5;
4670
4671 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
4672 (PROCESS_TYPE,
4673 PROCESS_PHASE,
4674 CC_HEADER_ID,
4675 CC_ACCT_LINE_ID,
4676 CC_DET_PF_LINE_ID,
4677 EXCEPTION_REASON,
4678 ORG_ID,
4679 SET_OF_BOOKS_ID,
4680 REQUEST_ID)
4681 VALUES(
4682 'R',
4683 'F',
4684 l_err_header_id,
4685 l_err_acct_line_id,
4686 l_err_det_pf_line_id,
4687 l_message,
4688 l_ORG_ID,
4689 l_SOB_ID,
4690 l_REQUEST_ID1);
4691 COMMIT;
4692 ELSIF (l_process_flag = 'P')
4693 THEN
4694 -- IF l_debug_mode = 'Y' THEN
4695 -- Output_Debug (' IGCCREPB -- Commit Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4696 -- END IF;
4697
4698 -- bug 3199488, start block
4699 IF (l_state_level >= l_debug_level) THEN
4700 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg105','IGCCREPB -- Commit Revalues ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4701 END IF;
4702 -- bug 3199488, end block
4703
4704 COMMIT;
4705 END IF;
4706 ELSIF (l_cc_headers_rec.cc_type = 'S')
4707 THEN
4708 -- IF l_debug_mode = 'Y' THEN
4709 -- Output_Debug (' IGCCREPB -- Standard reval ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4710 -- END IF;
4711
4712 -- bug 3199488, start block
4713 IF (l_state_level >= l_debug_level) THEN
4714 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg106','IGCCREPB -- Standard reval ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4715 END IF;
4716 -- bug 3199488, end block
4717
4718 SAVEPOINT REVALUE6;
4719
4720 l_process_flag := 'F';
4721
4722
4723 IF (l_reservation_status = 'P')
4724 THEN
4725 -- IF l_debug_mode = 'Y' THEN
4726 -- Output_Debug (' IGCCREPB -- Standard reval P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4727 -- END IF;
4728 -- bug 3199488, start block
4729 IF (l_state_level >= l_debug_level) THEN
4730 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg107','IGCCREPB -- Standard reval P ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4731 END IF;
4732 -- bug 3199488, end block
4733 l_message := NULL;
4734 l_err_header_id := NULL;
4735 l_err_acct_line_id := NULL;
4736 l_err_det_pf_line_id := NULL;
4737
4738 l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
4739 l_rate_date,
4740 l_rate,
4741 l_sob_id,
4742 l_org_id,
4743 l_sbc_on,
4744 l_cbc_on,
4745 l_prov_enc_on,
4746 l_conf_enc_on,
4747 'N',
4748 l_request_id1,
4749 l_message,
4750 l_err_header_id,
4751 l_err_acct_line_id,
4752 l_err_det_pf_line_id);
4753 ELSIF (l_reservation_status = 'F')
4754 THEN
4755 -- IF l_debug_mode = 'Y' THEN
4756 -- Output_Debug (' IGCCREPB -- Standard reval F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4757 -- END IF;
4758 -- bug 3199488, start block
4759 IF (l_state_level >= l_debug_level) THEN
4760 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg108',
4761 'IGCCREPB -- Standard reval F ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4762 END IF;
4763 -- bug 3199488, end block
4764
4765 l_message := NULL;
4766 l_err_header_id := NULL;
4767 l_err_acct_line_id := NULL;
4768 l_err_det_pf_line_id := NULL;
4769
4770 l_process_flag := reval_update(l_cc_headers_rec.cc_header_id,
4771 l_rate_date,
4772 l_rate,
4773 l_sob_id,
4774 l_org_id,
4775 l_sbc_on,
4776 l_cbc_on,
4777 l_prov_enc_on,
4778 l_conf_enc_on,
4779 'Y',
4780 l_request_id1,
4781 l_message,
4782 l_err_header_id,
4783 l_err_acct_line_id,
4784 l_err_det_pf_line_id);
4785 END IF;
4786
4787 IF (l_process_flag = 'F')
4788 THEN
4789 -- IF l_debug_mode = 'Y' THEN
4790 -- Output_Debug (' IGCCREPB -- Standard Rollback revalue ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4791 -- END IF;
4792 -- bug 3199488, start block
4793 IF (l_state_level >= l_debug_level) THEN
4794 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg109',
4795 'IGCCREPB -- Standard Rollback revalue ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4796 END IF;
4797 -- bug 3199488, end block
4798 ROLLBACK TO REVALUE6;
4799
4800 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
4801 (PROCESS_TYPE,
4802 PROCESS_PHASE,
4803 CC_HEADER_ID,
4804 CC_ACCT_LINE_ID,
4805 CC_DET_PF_LINE_ID,
4806 EXCEPTION_REASON,
4807 ORG_ID,
4808 SET_OF_BOOKS_ID,
4809 REQUEST_ID)
4810 VALUES(
4811 'R',
4812 'F',
4813 l_err_header_id,
4814 l_err_acct_line_id,
4815 l_err_det_pf_line_id,
4816 l_message,
4817 l_ORG_ID,
4818 l_SOB_ID,
4819 l_REQUEST_ID1);
4820 COMMIT;
4821 ELSIF (l_process_flag = 'P')
4822 THEN
4823
4824 -- IF l_debug_mode = 'Y' THEN
4825 -- Output_Debug (' IGCCREPB -- Standard Commit revalue ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4826 -- END IF;
4827 -- bug 3199488, start block
4828 IF (l_state_level >= l_debug_level) THEN
4829 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg110',
4830 'IGCCREPB -- Standard Commit revalue ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4831 END IF;
4832 -- bug 3199488, end block
4833 COMMIT;
4834 END IF;
4835
4836 END IF; /* STANDARD */
4837 END IF; /* validation_status = P */
4838
4839 END LOOP;
4840
4841 CLOSE c_reval_data;
4842 -- IF l_debug_mode = 'Y' THEN
4843 -- Output_Debug (' IGCCREPB -- Close revalue Cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4844 -- END IF;
4845
4846 -- bug 3199488, start block
4847 IF (l_state_level >= l_debug_level) THEN
4848 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg111',
4849 'IGCCREPB -- Close revalue Cursor ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4850 END IF;
4851 -- bug 3199488, end block
4852
4853 END IF; /* Final Phase */
4854 /* End Reservation Phase */
4855
4856 END IF;
4857
4858 /* Begin fix for bug 1609006 */
4859 IF (l_process_data_count = 0)
4860 THEN
4861 l_message := NULL;
4862 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_NO_CC_SELECTED');
4863 l_message := FND_MESSAGE.GET;
4864
4865 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
4866 (PROCESS_TYPE,
4867 PROCESS_PHASE,
4868 CC_HEADER_ID,
4869 CC_ACCT_LINE_ID,
4870 CC_DET_PF_LINE_ID,
4871 EXCEPTION_REASON,
4872 ORG_ID,
4873 SET_OF_BOOKS_ID,
4874 REQUEST_ID)
4875 VALUES(
4876 'R',
4877 p_process_phase,
4878 NULL,
4879 NULL,
4880 NULL,
4881 l_message,
4882 l_ORG_ID,
4883 l_SOB_ID,
4884 l_REQUEST_ID1);
4885 END IF;
4886 /* End fix for bug 1609006 */
4887
4888 COMMIT;
4889 -- IF l_debug_mode = 'Y' THEN
4890 -- Output_Debug (' IGCCREPB -- End Main submit request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4891 -- END IF;
4892
4893 -- bug 3199488, start block
4894 IF (l_state_level >= l_debug_level) THEN
4895 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Msg112',
4896 'IGCCREPB -- End Main submit request ' || to_char(sysdate,'DD-MON-YY:MI:SS'));
4897 END IF;
4898 -- bug 3199488, end block
4899
4900 /*Bug No : 6341012. MOAC Uptake. Need to set org_id before submiting a request*/
4901 Fnd_request.set_org_id(l_org_id);
4902 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
4903 'IGC',
4904 'IGCCRVPR',
4905 NULL,
4906 NULL,
4907 FALSE,
4908 l_sob_id,
4909 l_org_id,
4910 p_process_phase,
4911 'R',
4912 l_request_id1);
4913
4914 -----------------------
4915 -- Start of XML Report
4916 -----------------------
4917
4918 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
4919 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
4920 l_lang,
4921 l_terr,
4922 'IGCCRVPR_XML',
4923 'IGC',
4924 'IGCCRVPR_XML' );
4925
4926 l_layout := FND_REQUEST.ADD_LAYOUT(
4927 'IGC',
4928 'IGCCRVPR_XML',
4929 l_lang,
4930 l_terr,
4931 'RTF');
4932
4933 IF l_layout then
4934 l_request_id2 := FND_REQUEST.SUBMIT_REQUEST(
4935 'IGC',
4936 'IGCCRVPR_XML',
4937 NULL,
4938 NULL,
4939 FALSE,
4940 l_sob_id,
4941 l_org_id,
4942 p_process_phase,
4943 'R',
4944 l_request_id1);
4945 END IF;
4946 END IF;
4947 --------------------
4948 -- End of XML Report
4949 --------------------
4950
4951 -- ------------------------------------------------------------------------------------
4952 -- Ensure that any exceptions raised are output into the log file to be reported to
4953 -- the user if any are present.
4954 -- ------------------------------------------------------------------------------------
4955 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
4956 p_data => l_msg_data );
4957
4958 IF (l_msg_count > 0) THEN
4959 l_error_text := '';
4960 FOR l_cur IN 1..l_msg_count LOOP
4961 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
4962 -- fnd_file.put_line (FND_FILE.LOG,
4963 -- l_error_text);
4964 -- bug 3199488 start block
4965 IF (l_state_level >= l_debug_level) THEN
4966 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp3',
4967 l_error_text);
4968 END IF;
4969 -- bug 3199488, end block
4970 END LOOP;
4971 END IF;
4972
4973 EXCEPTION
4974
4975 WHEN insert_data
4976 THEN
4977 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4978 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'revalue_main');
4979 END IF;
4980 -- bug 3199488 start block
4981 IF (l_unexp_level >= l_debug_level) THEN
4982 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Unexp1',TRUE);
4983 END IF;
4984 -- bug 3199488, end block
4985 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
4986 p_data => l_msg_data );
4987
4988 IF (l_msg_count > 0) THEN
4989
4990 l_error_text := '';
4991 FOR l_cur IN 1..l_msg_count LOOP
4992 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
4993 -- fnd_file.put_line (FND_FILE.LOG,
4994 -- l_error_text);
4995 -- bug 3199488 start block
4996 IF (l_state_level >= l_debug_level) THEN
4997 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp4',
4998 l_error_text);
4999 END IF;
5000 -- bug 3199488, end block
5001 END LOOP;
5002 ELSE
5003 l_error_text := 'Error Returned but Error stack has no data';
5004 -- fnd_file.put_line (FND_FILE.LOG,
5005 -- l_error_text);
5006 -- bug 3199488 start block
5007 IF (l_state_level >= l_debug_level) THEN
5008 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp5',
5009 l_error_text);
5010 END IF;
5011 -- bug 3199488, end block
5012 END IF;
5013 ROLLBACK TO REVALUE3;
5014
5015 WHEN OTHERS THEN
5016 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5017 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'revalue_main');
5018 END IF;
5019 -- bug 3199488, start block
5020 IF (l_unexp_level >= l_debug_level) THEN
5021 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
5022 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
5023 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
5024 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Unexp2',TRUE);
5025 END IF;
5026 -- bug 3199488, end block
5027 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
5028 p_data => l_msg_data );
5029
5030 IF (l_msg_count > 0) THEN
5031
5032 l_error_text := '';
5033 FOR l_cur IN 1..l_msg_count LOOP
5034 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
5035 -- fnd_file.put_line (FND_FILE.LOG,
5036 -- l_error_text);
5037 -- bug 3199488 start block
5038 IF (l_state_level >= l_debug_level) THEN
5039 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp6',
5040 l_error_text);
5041 END IF;
5042 -- bug 3199488, end block
5043 END LOOP;
5044 ELSE
5045 l_error_text := 'Error Returned but Error stack has no data';
5046 -- fnd_file.put_line (FND_FILE.LOG,
5047 -- l_error_text);
5048 -- bug 3199488 start block
5049 IF (l_state_level >= l_debug_level) THEN
5050 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_revalue_process_pkg.revalue_main.Excp7',
5051 l_error_text);
5052 END IF;
5053 -- bug 3199488, end block
5054 END IF;
5055
5056 END revalue_main;
5057 BEGIN
5058 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5059 l_state_level := FND_LOG.LEVEL_STATEMENT;
5060 l_proc_level := FND_LOG.LEVEL_PROCEDURE;
5061 l_event_level := FND_LOG.LEVEL_EVENT;
5062 l_excep_level := FND_LOG.LEVEL_EXCEPTION;
5063 l_error_level := FND_LOG.LEVEL_ERROR;
5064 l_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
5065
5066 l_debug_mode := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
5067 g_debug_flag := 'N' ;
5068 g_debug_msg := NULL;
5069
5070
5071 END IGC_CC_REVALUE_PROCESS_PKG;