[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_REP_YEP_PVT
Source
1 PACKAGE BODY IGC_CC_REP_YEP_PVT AS
2 /*$Header: IGCCPRVB.pls 120.7.12000000.10 2007/12/06 15:02:03 bmaddine ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_REP_YEP_PVT';
5
6 -- The flag determines whether to print debug information or not.
7 l_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
8 --g_debug_flag VARCHAR2(1) := 'N' ;
9 g_line_num NUMBER := 0;
10 l_state_level number;
11 l_debug_level number;
12
13 /*==================================================================================
14 Procedure INVOICE_CANC_OR_APPROVED
15 =================================================================================*/
16 -- bug 2098010 ssmales - added p_process_type parameter to function below
17 FUNCTION invoice_canc_or_approved(p_cc_header_id NUMBER, p_process_type VARCHAR2)
18 RETURN BOOLEAN
19 IS
20 l_count NUMBER := 0;
21 l_appr_count NUMBER := 0;
22 l_canc_count NUMBER := 0;
23 l_dist_count NUMBER := 0;
24 l_appr_dist_count NUMBER := 0;
25
26 l_invoice_id ap_invoices_all.invoice_id%TYPE;
27 l_cancelled_date ap_invoices_all.cancelled_date%TYPE;
28
29 CURSOR c_invoices(p_cc_header_id NUMBER)
30 IS
31 SELECT unique apid.invoice_id
32 FROM
33 ap_invoice_distributions_all apid,
34 po_distributions_all pod,
35 po_headers_all phh,
36 igc_cc_headers cch
37 WHERE
38 apid.po_distribution_id = pod.po_distribution_id AND
39 pod.po_header_id = phh.po_header_id AND
40 phh.org_id = cch.org_id AND
41 phh.type_lookup_code = 'STANDARD' AND
42 phh.segment1 = cch.cc_num AND
43 cch.cc_header_id = p_cc_header_id;
44 BEGIN
45 l_count := 0;
46
47 l_canc_count := 0;
48 l_appr_count := 0;
49
50 OPEN c_invoices(p_cc_header_id);
51 LOOP
52 FETCH c_invoices INTO l_invoice_id;
53 EXIT WHEN c_invoices%NOTFOUND;
54
55 l_count := l_count + 1;
56
57 l_cancelled_date := NULL;
58
59 SELECT cancelled_date
60 INTO l_cancelled_date
61 FROM ap_invoices_all
62 WHERE invoice_id = l_invoice_id;
63
64 IF (l_cancelled_date IS NOT NULL)
65 THEN
66 l_canc_count := l_canc_count + 1;
67 ELSE
68
69 l_dist_count := 0;
70 l_appr_dist_count := 0;
71
72 BEGIN
73 SELECT count(invoice_distribution_id)
74 INTO l_dist_count
75 FROM
76 ap_invoice_distributions_all
77 WHERE invoice_id = l_invoice_id;
78 EXCEPTION
79 WHEN NO_DATA_FOUND
80 THEN
81 l_dist_count := 0;
82 END;
83
84 -- bug 2098010 ssmales - added if statement below to exec existing block only if proc_type is 'R'
85 IF p_process_type = 'R' THEN
86
87 BEGIN
88 SELECT count(invoice_distribution_id)
89 INTO l_appr_dist_count
90 FROM
91 ap_invoice_distributions_all
92 WHERE invoice_id = l_invoice_id AND
93 NVL(match_status_flag,'X') = 'A' AND
94 NVL(exchange_rate_variance,0) = 0;
95 EXCEPTION
96 WHEN NO_DATA_FOUND
97 THEN
98 l_appr_dist_count := 0;
99 END;
100 -- bug 2098010 ssmales - start - block below added for when proc_type is 'F'
101
102 ELSE --p_process_type = 'F'
103
104 BEGIN
105 SELECT count(invoice_distribution_id)
106 INTO l_appr_dist_count
107 FROM
108 ap_invoice_distributions_all
109 WHERE invoice_id = l_invoice_id AND
110 NVL(match_status_flag, 'X') = 'A' ;
111 EXCEPTION
112 WHEN NO_DATA_FOUND
113 THEN
114 l_appr_dist_count := 0;
115 END;
116
117 END IF;
118 -- bug 2098010 ssmales - end
119
120
121 IF (l_dist_count = l_appr_dist_count)
122 THEN
123 l_appr_count := l_appr_count + 1;
124
125 END IF;
126
127 END IF;
128
129
130 END LOOP;
131 CLOSE c_invoices;
132
133 IF (l_count = l_appr_count + l_canc_count)
134 THEN
135 RETURN(FALSE);
136 ELSE
137 RETURN(TRUE);
138 END IF;
139
140 END invoice_canc_or_approved;
141
142 /*==================================================================================
143 Procedure LOCK_CC
144 =================================================================================*/
145
146
147 FUNCTION LOCK_CC(p_cc_header_id IN NUMBER)
148 RETURN BOOLEAN AS
149
150 CURSOR C(p_cc_header_id NUMBER) IS
151 SELECT *
152 FROM IGC_CC_HEADERS
153 WHERE
154 CC_HEADER_ID = p_CC_HEADER_ID
155 FOR UPDATE
156 NOWAIT;
157
158 V C%ROWTYPE;
159 l_ERROR NUMBER;
160
161 BEGIN
162 OPEN C(P_CC_HEADER_ID);
163 FETCH C INTO V;
164 IF C%NOTFOUND
165 THEN RETURN FALSE;
166 ELSE RETURN TRUE;
167 END IF;
168 CLOSE C;
169
170 EXCEPTION
171 WHEN OTHERS
172 THEN
173 l_ERROR := SQLCODE;
174 IF l_ERROR = -54
175 THEN
176 RETURN FALSE;
177 END IF;
178
179
180 END LOCK_CC;
181
182 /*==================================================================================
183 End of Procedure LOCK_CC
184 =================================================================================*/
185
186
187
188 /*==================================================================================
189 Procedure LOCK_PO
190 =================================================================================*/
191
192
193 FUNCTION LOCK_PO( p_cc_header_id NUMBER)
194 RETURN BOOLEAN AS
195
196 CURSOR CC(P_CC_HEADER_ID NUMBER) IS
197 SELECT 'Y'
198 FROM PO_HEADERS_ALL A
199 WHERE
200 A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
201 FROM IGC_CC_HEADERS B,
202 PO_HEADERS_ALL C
203 WHERE B.ORG_ID = C.ORG_ID AND
204 B.CC_NUM = C.SEGMENT1 AND
205 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
206 B.CC_HEADER_ID = P_CC_HEADER_ID);
207 CURSOR CC1(P_CC_HEADER_ID NUMBER) IS
208 SELECT 'Y'
209 FROM PO_HEADERS_ALL A
210 WHERE
211 A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
212 FROM IGC_CC_HEADERS B,
213 PO_HEADERS_ALL C
214 WHERE B.ORG_ID = C.ORG_ID AND
215 B.CC_NUM = C.SEGMENT1 AND
216 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
217 B.CC_HEADER_ID = P_CC_HEADER_ID)
218 FOR UPDATE
219 NOWAIT;
220
221 VV CC%ROWTYPE;
222 VV1 CC1%ROWTYPE;
223 l_ERROR NUMBER;
224
225 BEGIN
226 OPEN CC(P_CC_HEADER_ID);
227 FETCH CC INTO VV;
228 IF CC%NOTFOUND
229 THEN
230 CLOSE CC;
231 RETURN TRUE;
232 ELSE
233 OPEN CC1(P_CC_HEADER_ID);
234 FETCH CC1 INTO VV1;
235 IF CC1%NOTFOUND
236 THEN
237 CLOSE CC;
238 CLOSE CC1;
239 RETURN FALSE;
240 ELSE
241 CLOSE CC;
242 CLOSE CC1;
243 RETURN TRUE;
244 END IF;
245 END IF;
246 EXCEPTION
247 WHEN OTHERS
248 THEN
249 l_ERROR := SQLCODE;
250 IF l_ERROR = -54
251 THEN
252 RETURN FALSE;
253 END IF;
254
255 END LOCK_PO;
256
257 /*==================================================================================
258 End of Procedure LOCK_PO
259 =================================================================================*/
260
261
262 /*==================================================================================
263 Procedure VALIDATE_CC
264 =================================================================================*/
265
266
267
268 FUNCTION VALIDATE_CC( p_CC_HEADER_ID IN NUMBER,
269 p_PROCESS_TYPE IN VARCHAR2,
270 p_PROCESS_PHASE IN VARCHAR2,
271 p_YEAR IN NUMBER,
272 p_SOB_ID IN NUMBER,
273 p_ORG_ID IN NUMBER,
274 p_PROV_ENC_ON IN BOOLEAN,
275 p_REQUEST_ID IN NUMBER)
276 RETURN VARCHAR2 AS
277
278 CURSOR C3 IS
279 SELECT *
280 FROM IGC_CC_HEADERS
281 WHERE IGC_CC_HEADERS.CC_HEADER_ID = p_CC_HEADER_ID;
282
283 CURSOR C4(HEADER_ID NUMBER) IS
284 SELECT *
285 FROM IGC_CC_ACCT_LINES
286 WHERE IGC_CC_ACCT_LINES.CC_HEADER_ID = HEADER_ID;
287
288 CURSOR C5(ACCT_LINE_ID NUMBER) IS
289 SELECT *
290 FROM IGC_CC_DET_PF_V
291 WHERE IGC_CC_DET_PF_V.CC_ACCT_LINE_ID = ACCT_LINE_ID;
292
293 CURSOR C9 IS
294 SELECT *
295 FROM IGC_CC_HEADERS
296 WHERE PARENT_HEADER_ID IN ( SELECT A.PARENT_HEADER_ID
297 FROM IGC_CC_HEADERS A , IGC_CC_HEADERS B
298 WHERE A.PARENT_HEADER_ID = B.CC_HEADER_ID
299 AND A.PARENT_HEADER_ID = p_CC_HEADER_ID);
300
301
302 v3 C3%ROWTYPE;
303 V4 C4%ROWTYPE;
304 V5 C5%ROWTYPE;
305 V9 C9%ROWTYPE;
306 l_INVOICE_STATUS BOOLEAN;
307 l_PERIOD_COUNTER NUMBER := 0;
308 l_STATUS_COUNTER NUMBER :=0;
309 l_CC_APPROVAL_STATUS IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE;
310 l_OVERBILLED_COUNTER NUMBER := 0;
311 l_COUNTER NUMBER :=0;
312 l_PROCESS_TYPE VARCHAR2(25);
313 l_STATE IGC_CC_HEADERS.CC_STATE%TYPE;
314 l_PREVIOUS_APPRVL_STATUS IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE;
315 l_DUMMY VARCHAR2(1);
316 l_EXCEPTION igc_cc_process_exceptions.exception_reason%TYPE := NULL;
317
318 BEGIN
319
320 IF p_PROCESS_TYPE = 'Y'
321 THEN
322 l_PROCESS_TYPE := 'YEAR-END PROCESS';
323 ELSIF p_PROCESS_TYPE = 'R'
324 THEN
325 l_PROCESS_TYPE := 'REVALUATION PROCESS';
326 ELSIF p_PROCESS_TYPE = 'F'
327 THEN
328 l_PROCESS_TYPE := 'REVALUATION FIX PROCESS';
329 END IF;
330
331 /*************************************** Start of VALIDATE_CC Procedure *****************************************/
332 OPEN C3;
333 LOOP
334 FETCH C3 INTO V3; /* Fetch Contracts based upon CC_HEADER_ID */
335 EXIT WHEN C3%NOTFOUND;
336
337 /**** Preliminary Mode and Final Mode Validation For STANDARD CC****/
338
339 IF p_PROCESS_TYPE IN ('Y','R','F') AND
340 (p_PROCESS_PHASE = 'P'OR p_PROCESS_PHASE = 'F') AND
341 V3.CC_TYPE = 'S'
342 THEN
343
344 /* Revaluation Validation */
345 IF p_PROCESS_TYPE = 'R' OR p_PROCESS_TYPE = 'F'
346 THEN
347 IF ( ( (v3.cc_state = 'PR') AND (p_prov_enc_on = TRUE) ) OR
348 (v3.cc_state = 'CM') OR
349 ( ((v3.cc_state = 'CL') AND (p_prov_enc_on = TRUE)) AND (v3.cc_apprvl_status <> 'AP')) OR
350 ( (v3.cc_state = 'CT') AND (v3.cc_apprvl_status <> 'AP'))
351 )
352 AND V3.CC_ENCMBRNC_STATUS = 'N'
353 THEN
354 l_EXCEPTION := NULL;
355 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_NOT_FULLY_ENC');
356 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
357 l_EXCEPTION := FND_MESSAGE.GET;
358
359 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
360 (PROCESS_TYPE ,
361 PROCESS_PHASE,
362 CC_HEADER_ID,
363 CC_ACCT_LINE_ID ,
364 CC_DET_PF_LINE_ID,
365 EXCEPTION_REASON,
366 ORG_ID,
367 SET_OF_BOOKS_ID,
368 REQUEST_ID)
369 VALUES (p_PROCESS_TYPE,
370 p_PROCESS_PHASE,
371 V3.CC_HEADER_ID,
372 NULL,
373 NULL,
374 l_EXCEPTION,
375 V3.ORG_ID,
376 V3.SET_OF_BOOKS_ID,
377 p_REQUEST_ID);
378 RETURN 'F';
379
380 END IF;
381
382 -- bug 2098010 ssmales - added p_process_type argument to function call below
383 l_INVOICE_STATUS := IGC_CC_REP_YEP_PVT.INVOICE_CANC_OR_APPROVED(V3.CC_HEADER_ID,
384 p_PROCESS_TYPE);
385
386 IF l_INVOICE_STATUS = TRUE
387 THEN
388 l_EXCEPTION := NULL;
389 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_INVOICE_PAID_OR_CAN');
390 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
391 l_EXCEPTION := FND_MESSAGE.GET;
392
393 -- INSERT INTO IGC_CC_PROCESS_EXCEPTIONS VALUES(
394 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
395 (PROCESS_TYPE ,
396 PROCESS_PHASE,
397 CC_HEADER_ID,
398 CC_ACCT_LINE_ID ,
399 CC_DET_PF_LINE_ID,
400 EXCEPTION_REASON,
401 ORG_ID,
402 SET_OF_BOOKS_ID,
403 REQUEST_ID)
404 VALUES (
405 p_PROCESS_TYPE,
406 p_PROCESS_PHASE,
407 V3.CC_HEADER_ID,
408 NULL,
409 NULL,
410 l_EXCEPTION,
411 V3.ORG_ID,
412 V3.SET_OF_BOOKS_ID,
413 p_REQUEST_ID);
414
415 RETURN 'F';
416
417 END IF;
418
419 IF V3.CC_STATE = 'CM' AND V3.CC_APPRVL_STATUS = 'IN'
420 THEN
421 l_EXCEPTION := NULL;
422 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_CM_INCOMPLETE');
423 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
424 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
425 l_EXCEPTION := FND_MESSAGE.GET;
426
427 -- INSERT INTO IGC_CC_PROCESS_EXCEPTIONS VALUES(p_PROCESS_TYPE,
428 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
429 (PROCESS_TYPE ,
430 PROCESS_PHASE,
431 CC_HEADER_ID,
432 CC_ACCT_LINE_ID ,
433 CC_DET_PF_LINE_ID,
434 EXCEPTION_REASON,
435 ORG_ID,
436 SET_OF_BOOKS_ID,
437 REQUEST_ID)
438 VALUES (P_PROCESS_TYPE,
439 p_PROCESS_PHASE,
440 V3.CC_HEADER_ID,
441 NULL,
442 NULL,
443 l_EXCEPTION,
444 V3.ORG_ID,
445 V3.SET_OF_BOOKS_ID,
446 p_REQUEST_ID);
447 RETURN 'F';
448 END IF;
449
450 IF p_PROCESS_TYPE = 'R'
451 THEN
452 IF IGC_CC_REVAL_FIX_PROCESS_PKG.REVALUE_FIX(V3.CC_HEADER_ID)
453 THEN
454 l_EXCEPTION := NULL;
455 FND_MESSAGE.SET_NAME('IGC','IGC_CC_HAS_REV_VARIANCES');
456 l_EXCEPTION := FND_MESSAGE.GET;
457
458 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
459 (PROCESS_TYPE ,
460 PROCESS_PHASE,
461 CC_HEADER_ID,
462 CC_ACCT_LINE_ID ,
463 CC_DET_PF_LINE_ID,
464 EXCEPTION_REASON,
465 ORG_ID,
466 SET_OF_BOOKS_ID,
467 REQUEST_ID)
468 VALUES (P_PROCESS_TYPE,
469
470 p_PROCESS_PHASE,
471 V3.CC_HEADER_ID,
472 NULL,
473 NULL,
474 l_EXCEPTION,
475 V3.ORG_ID,
476 V3.SET_OF_BOOKS_ID,
477 p_REQUEST_ID);
478 RETURN 'F';
479 END IF;
480 END IF;
481 END IF;
482 /* End of Revaluation Validation */
483
484
485 /* Fully Encumbrance check for YEAR END PROCESS */
486
487 IF p_PROCESS_TYPE = 'Y'
488 THEN
489 IF ( ( (v3.cc_state = 'PR') AND (p_prov_enc_on = TRUE) ) OR
490 (v3.cc_state = 'CM') OR
491 ( ( (v3.cc_state = 'CL') AND (p_prov_enc_on = TRUE) ) AND (v3.cc_apprvl_status <> 'AP')) OR
492 ( (v3.cc_state = 'CT') AND (v3.cc_apprvl_status <> 'AP'))
493 )
494 AND V3.CC_ENCMBRNC_STATUS = 'N'
495 THEN
496 l_EXCEPTION := NULL;
497 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_NOT_FULLY_ENC');
498 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
499 l_EXCEPTION := FND_MESSAGE.GET;
500
501 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
502 (PROCESS_TYPE ,
503 PROCESS_PHASE,
504 CC_HEADER_ID,
505 CC_ACCT_LINE_ID ,
506 CC_DET_PF_LINE_ID,
507 EXCEPTION_REASON,
508 ORG_ID,
509 SET_OF_BOOKS_ID,
510 REQUEST_ID)
511 VALUES (P_PROCESS_TYPE,
512
513 p_PROCESS_PHASE,
514 V3.CC_HEADER_ID,
515 NULL,
516 NULL,
517 l_EXCEPTION,
518 V3.ORG_ID,
519 V3.SET_OF_BOOKS_ID,
520 p_REQUEST_ID);
521 RETURN 'F';
522
523 END IF;
524 END IF;
525
526 /* END OF Fully Encumbrance check for YEAR END PROCESS */
527
528
529 IF V3.CC_STATE = 'PR' AND V3.CC_APPRVL_STATUS = 'IP'
530 THEN
531 l_EXCEPTION := NULL;
532 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_PR_INPROCESS');
533 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
534 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
535 l_EXCEPTION := FND_MESSAGE.GET;
536
537 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
538 (PROCESS_TYPE ,
539 PROCESS_PHASE,
540 CC_HEADER_ID,
541 CC_ACCT_LINE_ID ,
542 CC_DET_PF_LINE_ID,
543 EXCEPTION_REASON,
544 ORG_ID,
545 SET_OF_BOOKS_ID,
546 REQUEST_ID)
547 VALUES (P_PROCESS_TYPE,
548 p_PROCESS_PHASE,
549 V3.CC_HEADER_ID,
550 NULL,
551 NULL,
552 l_EXCEPTION,
553 V3.ORG_ID,
554 V3.SET_OF_BOOKS_ID,
555 p_REQUEST_ID);
556 RETURN 'F';
557 END IF;
558
559 IF V3.CC_STATE = 'CL' AND V3.CC_APPRVL_STATUS = 'IP'
560 THEN
561 l_EXCEPTION := NULL;
562 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CL_INPROCESS');
563 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
564 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
565 l_EXCEPTION := FND_MESSAGE.GET;
566
567 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
568 (PROCESS_TYPE ,
569 PROCESS_PHASE,
570 CC_HEADER_ID,
571 CC_ACCT_LINE_ID ,
572 CC_DET_PF_LINE_ID,
573 EXCEPTION_REASON,
574 ORG_ID,
575 SET_OF_BOOKS_ID,
576 REQUEST_ID)
577 VALUES (P_PROCESS_TYPE,
578
579 p_PROCESS_PHASE,
580 V3.CC_HEADER_ID,
581 NULL,
582 NULL,
583 l_EXCEPTION,
584 V3.ORG_ID,
585 V3.SET_OF_BOOKS_ID,
586 p_REQUEST_ID);
587 RETURN 'F';
588 END IF;
589
590 /* Over Billed Amount Validation for Standard CC in Cancelled State */
591
592 IF V3.CC_STATE = 'CL' AND V3.CC_APPRVL_STATUS <>'IP'
593 THEN
594 l_OVERBILLED_COUNTER := 0;
595 OPEN C4(V3.CC_HEADER_ID); /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
596 LOOP
597 FETCH C4 INTO V4;
598 EXIT WHEN C4%NOTFOUND;
599 OPEN C5(V4.CC_ACCT_LINE_ID);
600 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
601 LOOP
602 FETCH C5 INTO V5;
603 EXIT WHEN C5%NOTFOUND;
604 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
605 THEN
606 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
607 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
608 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
609 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
610 l_EXCEPTION := FND_MESSAGE.GET;
611
612 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
613 (PROCESS_TYPE ,
614 PROCESS_PHASE,
615 CC_HEADER_ID,
616 CC_ACCT_LINE_ID ,
617 CC_DET_PF_LINE_ID,
618 EXCEPTION_REASON,
619 ORG_ID,
620 SET_OF_BOOKS_ID,
621 REQUEST_ID)
622 VALUES (P_PROCESS_TYPE,
623 p_PROCESS_PHASE,
624 p_CC_HEADER_ID,
625 V5.CC_ACCT_LINE_ID,
626 V5.CC_DET_PF_LINE_ID,
627 l_EXCEPTION,
628 V3.ORG_ID,
629 V3.SET_OF_BOOKS_ID,
630 p_REQUEST_ID);
631
632 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
633
634 END IF;
635 END LOOP; /* End of Loop for Cursor C5 */
636 CLOSE C5;
637 END LOOP; /* End of Loop for Cursor C4 */
638 CLOSE C4;
639
640 IF l_OVERBILLED_COUNTER > 0
641 THEN
642 RETURN 'F';
643 END IF;
644
645 END IF;
646
647
648 IF V3.CC_STATE = 'CM' AND V3.CC_APPRVL_STATUS = 'IP'
649 THEN
650 l_EXCEPTION := NULL;
651 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CM_INPROCESS');
652 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
653 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
654 l_EXCEPTION := FND_MESSAGE.GET;
655
656 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
657 (PROCESS_TYPE ,
658 PROCESS_PHASE,
659 CC_HEADER_ID,
660 CC_ACCT_LINE_ID ,
661 CC_DET_PF_LINE_ID,
662 EXCEPTION_REASON,
663 ORG_ID,
664 SET_OF_BOOKS_ID,
665 REQUEST_ID)
666 VALUES (P_PROCESS_TYPE,
667 p_PROCESS_PHASE,
668 V3.CC_HEADER_ID,
669 NULL,
670 NULL,
671 l_EXCEPTION,
672 V3.ORG_ID,
673 V3.SET_OF_BOOKS_ID,
674 p_REQUEST_ID);
675
676 RETURN 'F';
677 END IF;
678
679 IF V3.CC_STATE = 'CM' AND V3.CC_APPRVL_STATUS <> 'IP'
680 THEN
681 l_OVERBILLED_COUNTER := 0;
682 OPEN C4(V3.CC_HEADER_ID); /* Over Billed Amount Validation for Standard CC */
683 /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
684 LOOP
685 FETCH C4 INTO V4;
686 EXIT WHEN C4%NOTFOUND;
687
688 OPEN C5(V4.CC_ACCT_LINE_ID);
689 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
690 LOOP
691 FETCH C5 INTO V5;
692 EXIT WHEN C5%NOTFOUND;
693
694 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
695 THEN
696 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
697 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
698 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
699 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
700 l_EXCEPTION := FND_MESSAGE.GET;
701
702 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
703 (PROCESS_TYPE ,
704 PROCESS_PHASE,
705 CC_HEADER_ID,
706 CC_ACCT_LINE_ID ,
707 CC_DET_PF_LINE_ID,
708 EXCEPTION_REASON,
709 ORG_ID,
710 SET_OF_BOOKS_ID,
711 REQUEST_ID)
712 VALUES (P_PROCESS_TYPE,
713 p_PROCESS_PHASE,
714 p_CC_HEADER_ID,
715 V5.CC_ACCT_LINE_ID,
716 V5.CC_DET_PF_LINE_ID,
717 l_EXCEPTION,
718 V3.ORG_ID,
719 V3.SET_OF_BOOKS_ID,
720 p_REQUEST_ID);
721
722 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
723
724 END IF;
725 END LOOP; /* End of Loop for Cursor C5 */
726 CLOSE C5;
727 END LOOP; /* End of Loop for Cursor C4 */
728 CLOSE C4;
729
730 IF l_OVERBILLED_COUNTER > 0
731 THEN
732 RETURN 'F';
733 END IF;
734
735 END IF;
736
737
738 IF V3.CC_STATE = 'CT' AND V3.CC_APPRVL_STATUS = 'IP'
739 THEN
740 l_EXCEPTION := NULL;
741 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CT_INPROCESS');
742 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
743 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
744 l_EXCEPTION := FND_MESSAGE.GET;
745
746 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
747 (PROCESS_TYPE ,
748 PROCESS_PHASE,
749 CC_HEADER_ID,
750 CC_ACCT_LINE_ID ,
751 CC_DET_PF_LINE_ID,
752 EXCEPTION_REASON,
753 ORG_ID,
754 SET_OF_BOOKS_ID,
755 REQUEST_ID)
756 VALUES (P_PROCESS_TYPE,
757
758 p_PROCESS_PHASE,
759 V3.CC_HEADER_ID,
760 NULL,
761 NULL,
762 l_EXCEPTION,
763 V3.ORG_ID,
764 V3.SET_OF_BOOKS_ID,
765 p_REQUEST_ID);
766 RETURN 'F';
767 END IF;
768
769 IF V3.CC_STATE = 'CT' AND V3.CC_APPRVL_STATUS <> 'IP'
770 THEN
771 l_OVERBILLED_COUNTER := 0;
772 OPEN C4(V3.CC_HEADER_ID); /* Over Billed Amount Validation for Standard CC */
773 /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
774 LOOP
775 FETCH C4 INTO V4;
776 EXIT WHEN C4%NOTFOUND;
777
778 OPEN C5(V4.CC_ACCT_LINE_ID);
779 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
780 LOOP
781 FETCH C5 INTO V5;
782 EXIT WHEN C5%NOTFOUND;
783
784 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
785 THEN
786 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
787 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
788 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
789 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
790 l_EXCEPTION := FND_MESSAGE.GET;
791
792 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
793 (PROCESS_TYPE ,
794 PROCESS_PHASE,
795 CC_HEADER_ID,
796 CC_ACCT_LINE_ID ,
797 CC_DET_PF_LINE_ID,
798 EXCEPTION_REASON,
799 ORG_ID,
800 SET_OF_BOOKS_ID,
801 REQUEST_ID)
802 VALUES (P_PROCESS_TYPE,
803
804 p_PROCESS_PHASE,
805 p_CC_HEADER_ID,
806 V5.CC_ACCT_LINE_ID,
807 V5.CC_DET_PF_LINE_ID,
808 l_EXCEPTION,
809 V3.ORG_ID,
810 V3.SET_OF_BOOKS_ID,
811 p_REQUEST_ID);
812
813 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
814 END IF;
815 END LOOP; /* End of Loop for Cursor C5 */
816 CLOSE C5;
817 END LOOP; /* End of Loop for Cursor C4 */
818 CLOSE C4;
819
820 IF l_OVERBILLED_COUNTER > 0
821 THEN
822 RETURN 'F';
823 END IF;
824 END IF;
825
826 RETURN 'P'; /* if all IF Statements failed then Validation Status is Passed */
827 END IF;
828 END LOOP; /* End of Loop for Cursor C3 */
829 CLOSE C3; /* End Of Cursor C3 */
830
831
832 /********************** End of Preliminary and Final Mode Validation For STATNDARD CC ***********************/
833
834
835
836
837 /***************Validation Check for Cover and Release CC For Both Preliminary And Final Mode ***************/
838
839
840 OPEN C3;
841 LOOP
842 FETCH C3 INTO V3;
843 EXIT WHEN C3%NOTFOUND;
844
845 IF p_PROCESS_TYPE IN ('Y','R','F') AND
846 (p_PROCESS_PHASE = 'P' OR p_PROCESS_PHASE = 'F') AND
847 V3.CC_TYPE = 'R'
848 THEN
849 RETURN 'F';
850 END IF;
851
852
853
854 IF p_PROCESS_TYPE IN ('Y','R','F') AND
855 (p_PROCESS_PHASE = 'P' OR p_PROCESS_PHASE = 'F') AND
856 V3.CC_TYPE = 'C'
857 THEN
858
859 /* Revaluation Validation */
860
861 IF p_PROCESS_TYPE = 'R' OR p_PROCESS_TYPE = 'F'
862 THEN
863
864 IF ( ( (v3.cc_state = 'PR') AND (p_prov_enc_on = TRUE) ) OR
865 (v3.cc_state = 'CM') OR
866 ( ( (v3.cc_state = 'CL') AND (p_prov_enc_on = TRUE) ) AND (v3.cc_apprvl_status <> 'AP')) OR
867 ( (v3.cc_state = 'CT') AND (v3.cc_apprvl_status <> 'AP'))
868 )
869 AND V3.CC_ENCMBRNC_STATUS = 'N'
870 THEN
871 l_EXCEPTION := NULL;
872 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_NOT_FULLY_ENC');
873 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
874 l_EXCEPTION := FND_MESSAGE.GET;
875
876 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
877 (PROCESS_TYPE ,
878 PROCESS_PHASE,
879 CC_HEADER_ID,
880 CC_ACCT_LINE_ID ,
881 CC_DET_PF_LINE_ID,
882 EXCEPTION_REASON,
883 ORG_ID,
884 SET_OF_BOOKS_ID,
885 REQUEST_ID)
886 VALUES (P_PROCESS_TYPE,
887
888 p_PROCESS_PHASE,
889 V3.CC_HEADER_ID,
890 NULL,
891 NULL,
892 l_EXCEPTION,
893 V3.ORG_ID,
894 V3.SET_OF_BOOKS_ID,
895 p_REQUEST_ID);
896 RETURN 'F';
897
898 END IF;
899
900 -- bug 2098010 ssmales - added p_process_type argument to function call below
901 l_INVOICE_STATUS := IGC_CC_REP_YEP_PVT.INVOICE_CANC_OR_APPROVED(V3.CC_HEADER_ID,
902 p_PROCESS_TYPE);
903
904 IF l_INVOICE_STATUS = TRUE
905 THEN
906 l_EXCEPTION := NULL;
907 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_INVOICE_PAID_OR_CAN');
908 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
909 l_EXCEPTION := FND_MESSAGE.GET;
910
911 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
912 (PROCESS_TYPE ,
913 PROCESS_PHASE,
914 CC_HEADER_ID,
915 CC_ACCT_LINE_ID ,
916 CC_DET_PF_LINE_ID,
917 EXCEPTION_REASON,
918 ORG_ID,
919 SET_OF_BOOKS_ID,
920 REQUEST_ID)
921 VALUES (P_PROCESS_TYPE,
922
923 p_PROCESS_PHASE,
924 V3.CC_HEADER_ID,
925 NULL,
926 NULL,
927 l_EXCEPTION,
928 V3.ORG_ID,
929 V3.SET_OF_BOOKS_ID,
930 p_REQUEST_ID);
931 RETURN 'F';
932
933 END IF;
934
935 IF V3.CC_STATE = 'CM' AND V3.CC_APPRVL_STATUS = 'IN'
936 THEN
937 l_EXCEPTION := NULL;
938 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_CM_INCOMPLETE');
939 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
940 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
941 l_EXCEPTION := FND_MESSAGE.GET;
942
943 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
944 (PROCESS_TYPE ,
945 PROCESS_PHASE,
946 CC_HEADER_ID,
947 CC_ACCT_LINE_ID ,
948 CC_DET_PF_LINE_ID,
949 EXCEPTION_REASON,
950 ORG_ID,
951 SET_OF_BOOKS_ID,
952 REQUEST_ID)
953 VALUES (P_PROCESS_TYPE,
954
955 p_PROCESS_PHASE,
956 V3.CC_HEADER_ID,
957 NULL,
958 NULL,
959 l_EXCEPTION,
960 V3.ORG_ID,
961 V3.SET_OF_BOOKS_ID,
962 p_REQUEST_ID);
963 RETURN 'F';
964 END IF;
965
966 END IF;
967 /* End Revaluation Validation */
968
969
970 /*************** Checking Release Validation for each selected Cover CC *****/
971
972 OPEN C9;
973 LOOP
974 FETCH C9 INTO V9;
975 EXIT WHEN C9%NOTFOUND;
976
977 /* Revaluation Validation at Releaese Level*/
978
979 IF p_PROCESS_TYPE = 'R' OR p_PROCESS_TYPE = 'F'
980 THEN
981
982 IF ( ( (v9.cc_state = 'PR') AND (p_prov_enc_on = TRUE) ) OR
983 (v9.cc_state = 'CM') OR
984 ( ( (v9.cc_state = 'CL') AND (p_prov_enc_on = TRUE) ) AND (v9.cc_apprvl_status <> 'AP')) OR
985 ( (v9.cc_state = 'CT') AND (v9.cc_apprvl_status <> 'AP'))
986 )
987 AND V9.CC_ENCMBRNC_STATUS = 'N'
988 THEN
989 l_EXCEPTION := NULL;
990 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_NOT_FULLY_ENC');
991 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
992 l_EXCEPTION := FND_MESSAGE.GET;
993
994 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
995 (PROCESS_TYPE ,
996 PROCESS_PHASE,
997 CC_HEADER_ID,
998 CC_ACCT_LINE_ID ,
999 CC_DET_PF_LINE_ID,
1000 EXCEPTION_REASON,
1001 ORG_ID,
1002 SET_OF_BOOKS_ID,
1003 REQUEST_ID)
1004 VALUES (P_PROCESS_TYPE,
1005
1006 p_PROCESS_PHASE,
1007 V9.CC_HEADER_ID,
1008 NULL,
1009 NULL,
1010 l_EXCEPTION,
1011 V3.ORG_ID,
1012 V3.SET_OF_BOOKS_ID,
1013 p_REQUEST_ID);
1014
1015 l_EXCEPTION := NULL;
1016 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1017 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1018 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1019 l_EXCEPTION := FND_MESSAGE.GET;
1020
1021 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1022 (PROCESS_TYPE ,
1023 PROCESS_PHASE,
1024 CC_HEADER_ID,
1025 CC_ACCT_LINE_ID ,
1026 CC_DET_PF_LINE_ID,
1027 EXCEPTION_REASON,
1028 ORG_ID,
1029 SET_OF_BOOKS_ID,
1030 REQUEST_ID)
1031 VALUES (P_PROCESS_TYPE,
1032
1033 p_PROCESS_PHASE,
1034 V3.CC_HEADER_ID,
1035 NULL,
1036 NULL,
1037 l_EXCEPTION,
1038 V3.ORG_ID,
1039 V3.SET_OF_BOOKS_ID,
1040 p_REQUEST_ID);
1041
1042
1043 l_COUNTER := l_COUNTER + 1;
1044 END IF;
1045
1046 -- bug 2098010 ssmales - added p_process_type argument to function call below
1047 l_INVOICE_STATUS := IGC_CC_REP_YEP_PVT.INVOICE_CANC_OR_APPROVED(V9.CC_HEADER_ID,
1048 p_PROCESS_TYPE);
1049
1050 IF l_INVOICE_STATUS = TRUE
1051 THEN
1052 l_EXCEPTION := NULL;
1053 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_INVOICE_PAID_OR_CAN');
1054 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1055 l_EXCEPTION := FND_MESSAGE.GET;
1056
1057 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1058 (PROCESS_TYPE ,
1059 PROCESS_PHASE,
1060 CC_HEADER_ID,
1061 CC_ACCT_LINE_ID ,
1062 CC_DET_PF_LINE_ID,
1063 EXCEPTION_REASON,
1064 ORG_ID,
1065 SET_OF_BOOKS_ID,
1066 REQUEST_ID)
1067 VALUES (P_PROCESS_TYPE,
1068
1069 p_PROCESS_PHASE,
1070 V9.CC_HEADER_ID,
1071 NULL,
1072 NULL,
1073 l_EXCEPTION,
1074 V3.ORG_ID,
1075 V3.SET_OF_BOOKS_ID,
1076 p_REQUEST_ID);
1077
1078 l_EXCEPTION := NULL;
1079 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1080 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1081 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1082 l_EXCEPTION := FND_MESSAGE.GET;
1083
1084 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1085 (PROCESS_TYPE ,
1086 PROCESS_PHASE,
1087 CC_HEADER_ID,
1088 CC_ACCT_LINE_ID ,
1089 CC_DET_PF_LINE_ID,
1090 EXCEPTION_REASON,
1091 ORG_ID,
1092 SET_OF_BOOKS_ID,
1093 REQUEST_ID)
1094 VALUES (P_PROCESS_TYPE,
1095 p_PROCESS_PHASE,
1096 V3.CC_HEADER_ID,
1097 NULL,
1098 NULL,
1099 l_EXCEPTION,
1100 V3.ORG_ID,
1101 V3.SET_OF_BOOKS_ID,
1102 p_REQUEST_ID);
1103
1104
1105 l_COUNTER := l_COUNTER + 1;
1106 END IF;
1107
1108 IF V9.CC_STATE = 'CM' AND V9.CC_APPRVL_STATUS = 'IN'
1109 THEN
1110 l_EXCEPTION := NULL;
1111 FND_MESSAGE.SET_NAME('IGC','IGC_CC_REP_CM_INCOMPLETE');
1112 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1113 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
1114 l_EXCEPTION := FND_MESSAGE.GET;
1115
1116 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1117 (PROCESS_TYPE ,
1118 PROCESS_PHASE,
1119 CC_HEADER_ID,
1120 CC_ACCT_LINE_ID ,
1121 CC_DET_PF_LINE_ID,
1122 EXCEPTION_REASON,
1123 ORG_ID,
1124 SET_OF_BOOKS_ID,
1125 REQUEST_ID)
1126 VALUES (P_PROCESS_TYPE,
1127
1128 p_PROCESS_PHASE,
1129 V3.CC_HEADER_ID,
1130 NULL,
1131 NULL,
1132 l_EXCEPTION,
1133 V3.ORG_ID,
1134 V3.SET_OF_BOOKS_ID,
1135 p_REQUEST_ID);
1136
1137 l_EXCEPTION := NULL;
1138 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1139 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1140 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1141 l_EXCEPTION := FND_MESSAGE.GET;
1142
1143 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1144 (PROCESS_TYPE ,
1145 PROCESS_PHASE,
1146 CC_HEADER_ID,
1147 CC_ACCT_LINE_ID ,
1148 CC_DET_PF_LINE_ID,
1149 EXCEPTION_REASON,
1150 ORG_ID,
1151 SET_OF_BOOKS_ID,
1152 REQUEST_ID)
1153 VALUES (P_PROCESS_TYPE,
1154 p_PROCESS_PHASE,
1155 V3.CC_HEADER_ID,
1156 NULL,
1157 NULL,
1158 l_EXCEPTION,
1159 V3.ORG_ID,
1160 V3.SET_OF_BOOKS_ID,
1161 p_REQUEST_ID);
1162
1163
1164 l_COUNTER := l_COUNTER + 1;
1165
1166 END IF;
1167
1168 IF p_PROCESS_TYPE = 'R'
1169 THEN
1170 IF IGC_CC_REVAL_FIX_PROCESS_PKG.REVALUE_FIX(V9.CC_HEADER_ID)
1171 THEN
1172
1173 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1174 (PROCESS_TYPE ,
1175 PROCESS_PHASE,
1176 CC_HEADER_ID,
1177 CC_ACCT_LINE_ID ,
1178 CC_DET_PF_LINE_ID,
1179 EXCEPTION_REASON,
1180 ORG_ID,
1181 SET_OF_BOOKS_ID,
1182 REQUEST_ID)
1183 VALUES (P_PROCESS_TYPE,
1184
1185 p_PROCESS_PHASE,
1186 V9.CC_HEADER_ID,
1187 NULL,
1188 NULL,
1189 'Contract Commitment has Revaluation Variances',
1190 V3.ORG_ID,
1191 V3.SET_OF_BOOKS_ID,
1192 p_REQUEST_ID);
1193 l_EXCEPTION := NULL;
1194 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1195 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1196 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1197 l_EXCEPTION := FND_MESSAGE.GET;
1198
1199 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1200 (PROCESS_TYPE ,
1201 PROCESS_PHASE,
1202 CC_HEADER_ID,
1203 CC_ACCT_LINE_ID ,
1204 CC_DET_PF_LINE_ID,
1205 EXCEPTION_REASON,
1206 ORG_ID,
1207 SET_OF_BOOKS_ID,
1208 REQUEST_ID)
1209 VALUES (P_PROCESS_TYPE,
1210
1211 p_PROCESS_PHASE,
1212 V3.CC_HEADER_ID,
1213 NULL,
1214 NULL,
1215 l_EXCEPTION,
1216 V3.ORG_ID,
1217 V3.SET_OF_BOOKS_ID,
1218 p_REQUEST_ID);
1219
1220
1221 l_COUNTER := l_COUNTER + 1;
1222
1223
1224 END IF;
1225 END IF;
1226
1227
1228 END IF;
1229 /* End of Revaluation Validation at Release Level*/
1230
1231
1232 IF V9.CC_STATE = 'PR' AND V9.CC_APPRVL_STATUS = 'IP'
1233 THEN
1234 l_EXCEPTION := NULL;
1235 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_PR_INPROCESS');
1236 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1237 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
1238 l_EXCEPTION := FND_MESSAGE.GET;
1239
1240 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1241 (PROCESS_TYPE ,
1242 PROCESS_PHASE,
1243 CC_HEADER_ID,
1244 CC_ACCT_LINE_ID ,
1245 CC_DET_PF_LINE_ID,
1246 EXCEPTION_REASON,
1247 ORG_ID,
1248 SET_OF_BOOKS_ID,
1249 REQUEST_ID)
1250 VALUES (P_PROCESS_TYPE,
1251
1252 p_PROCESS_PHASE,
1253 V9.CC_HEADER_ID,
1254 NULL,
1255 NULL,
1256 l_EXCEPTION,
1257 V3.ORG_ID,
1258 V3.SET_OF_BOOKS_ID,
1259 p_REQUEST_ID);
1260 l_EXCEPTION := NULL;
1261 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1262 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1263 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1264 l_EXCEPTION := FND_MESSAGE.GET;
1265
1266 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1267 (PROCESS_TYPE ,
1268 PROCESS_PHASE,
1269 CC_HEADER_ID,
1270 CC_ACCT_LINE_ID ,
1271 CC_DET_PF_LINE_ID,
1272 EXCEPTION_REASON,
1273 ORG_ID,
1274 SET_OF_BOOKS_ID,
1275 REQUEST_ID)
1276 VALUES (P_PROCESS_TYPE,
1277
1278 p_PROCESS_PHASE,
1279 V3.CC_HEADER_ID,
1280 NULL,
1281 NULL,
1282 l_EXCEPTION,
1283 V3.ORG_ID,
1284 V3.SET_OF_BOOKS_ID,
1285 p_REQUEST_ID);
1286
1287 IF p_PROCESS_TYPE = 'Y'
1288 THEN
1289 UPDATE IGC_CC_PROCESS_DATA A
1290 SET VALIDATION_STATUS = 'F'
1291 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1292 AND A.REQUEST_ID = p_REQUEST_ID
1293 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1294 AND A.ORG_ID = p_ORG_ID
1295 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1296 END IF;
1297
1298 l_COUNTER := l_COUNTER + 1;
1299
1300 END IF;
1301
1302 IF V9.CC_STATE = 'PR' AND V9.CC_APPRVL_STATUS <> 'IP'
1303 THEN
1304 IF p_PROCESS_PHASE = 'F'
1305 THEN
1306 IF p_PROCESS_TYPE = 'Y'
1307 THEN
1308 UPDATE IGC_CC_PROCESS_DATA A
1309 SET VALIDATION_STATUS = 'P'
1310 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1311 AND A.REQUEST_ID = p_REQUEST_ID
1312 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1313 AND A.ORG_ID = p_ORG_ID
1314 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1315
1316 UPDATE IGC_CC_HEADERS
1317 SET CC_APPRVL_STATUS = 'IP'
1318 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
1319 END IF;
1320
1321 END IF;
1322
1323 IF p_PROCESS_PHASE = 'P'
1324 THEN
1325 IF p_PROCESS_TYPE = 'Y'
1326 THEN
1327 UPDATE IGC_CC_PROCESS_DATA A
1328 SET VALIDATION_STATUS = 'P'
1329 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1330 AND A.REQUEST_ID = p_REQUEST_ID
1331 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1332 AND A.ORG_ID = p_ORG_ID
1333 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1334 END IF;
1335 END IF;
1336 END IF;
1337
1338 IF V9.CC_STATE = 'CL' AND V9.CC_APPRVL_STATUS = 'IP'
1339 THEN
1340 l_EXCEPTION := NULL;
1341 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CL_INPROCESS');
1342 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1343 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
1344 l_EXCEPTION := FND_MESSAGE.GET;
1345
1346 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1347 (PROCESS_TYPE ,
1348 PROCESS_PHASE,
1349 CC_HEADER_ID,
1350 CC_ACCT_LINE_ID ,
1351 CC_DET_PF_LINE_ID,
1352 EXCEPTION_REASON,
1353 ORG_ID,
1354 SET_OF_BOOKS_ID,
1355 REQUEST_ID)
1356 VALUES (P_PROCESS_TYPE,
1357
1358 p_PROCESS_PHASE,
1359 V9.CC_HEADER_ID,
1360 NULL,
1361 NULL,
1362 l_EXCEPTION,
1363 V3.ORG_ID,
1364 V3.SET_OF_BOOKS_ID,
1365 p_REQUEST_ID);
1366 l_EXCEPTION := NULL;
1367 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1368 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1369 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1370 l_EXCEPTION := FND_MESSAGE.GET;
1371
1372 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1373 (PROCESS_TYPE ,
1374 PROCESS_PHASE,
1375 CC_HEADER_ID,
1376 CC_ACCT_LINE_ID ,
1377 CC_DET_PF_LINE_ID,
1378 EXCEPTION_REASON,
1379 ORG_ID,
1380 SET_OF_BOOKS_ID,
1381 REQUEST_ID)
1382 VALUES (P_PROCESS_TYPE,
1383
1384 p_PROCESS_PHASE,
1385 V3.CC_HEADER_ID,
1386 NULL,
1387 NULL,
1388 l_EXCEPTION,
1389 V3.ORG_ID,
1390 V3.SET_OF_BOOKS_ID,
1391 p_REQUEST_ID);
1392
1393
1394 IF p_PROCESS_TYPE = 'Y'
1395 THEN
1396 UPDATE IGC_CC_PROCESS_DATA A
1397 SET VALIDATION_STATUS = 'F'
1398 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1399 AND A.REQUEST_ID = p_REQUEST_ID
1400 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1401 AND A.ORG_ID = p_ORG_ID
1402 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1403 END IF;
1404
1405 l_COUNTER := l_COUNTER + 1;
1406
1407 END IF;
1408
1409 IF V9.CC_STATE = 'CL' AND V9.CC_APPRVL_STATUS <>'IP'
1410 THEN
1411 l_OVERBILLED_COUNTER := 0;
1412 OPEN C4(V9.CC_HEADER_ID); /* Over Billed Amount Validation for Standard CC */
1413
1414 /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
1415 LOOP
1416 FETCH C4 INTO V4;
1417 EXIT WHEN C4%NOTFOUND;
1418 OPEN C5(V4.CC_ACCT_LINE_ID);
1419 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
1420 LOOP
1421 FETCH C5 INTO V5;
1422 EXIT WHEN C5%NOTFOUND;
1423
1424 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
1425 THEN
1426 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
1427 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1428 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
1429 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
1430 l_EXCEPTION := FND_MESSAGE.GET;
1431
1432 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1433 (PROCESS_TYPE ,
1434 PROCESS_PHASE,
1435 CC_HEADER_ID,
1436 CC_ACCT_LINE_ID ,
1437 CC_DET_PF_LINE_ID,
1438 EXCEPTION_REASON,
1439 ORG_ID,
1440 SET_OF_BOOKS_ID,
1441 REQUEST_ID)
1442 VALUES (P_PROCESS_TYPE,
1443
1444 p_PROCESS_PHASE,
1445 V9.CC_HEADER_ID,
1446 V5.CC_ACCT_LINE_ID,
1447 V5.CC_DET_PF_LINE_ID,
1448 l_EXCEPTION,
1449 V3.ORG_ID,
1450 V3.SET_OF_BOOKS_ID,
1451 p_REQUEST_ID);
1452
1453 l_EXCEPTION := NULL;
1454 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1455 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1456 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1457 l_EXCEPTION := FND_MESSAGE.GET;
1458
1459 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1460 (PROCESS_TYPE ,
1461 PROCESS_PHASE,
1462 CC_HEADER_ID,
1463 CC_ACCT_LINE_ID ,
1464 CC_DET_PF_LINE_ID,
1465 EXCEPTION_REASON,
1466 ORG_ID,
1467 SET_OF_BOOKS_ID,
1468 REQUEST_ID)
1469 VALUES (P_PROCESS_TYPE,
1470
1471 p_PROCESS_PHASE,
1472 V3.CC_HEADER_ID,
1473 NULL,
1474 NULL,
1475 l_EXCEPTION,
1476 V3.ORG_ID,
1477 V3.SET_OF_BOOKS_ID,
1478 p_REQUEST_ID);
1479
1480 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
1481 l_COUNTER := l_COUNTER + 1;
1482 END IF;
1483
1484 END LOOP; /* End of Loop for Cursor C5 */
1485 CLOSE C5;
1486 END LOOP; /* End of Loop for Cursor C4 */
1487 CLOSE C4;
1488
1489 IF l_OVERBILLED_COUNTER > 0
1490 THEN
1491 IF p_PROCESS_TYPE = 'Y'
1492 THEN
1493 UPDATE IGC_CC_PROCESS_DATA A
1494 SET VALIDATION_STATUS = 'F'
1495 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1496 AND A.REQUEST_ID = p_REQUEST_ID
1497 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1498 AND A.ORG_ID = p_ORG_ID
1499 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1500 END IF;
1501 ELSE
1502
1503 IF p_PROCESS_PHASE = 'F' AND p_PROCESS_TYPE = 'Y'
1504 THEN
1505
1506 UPDATE IGC_CC_PROCESS_DATA A
1507 SET VALIDATION_STATUS = 'P'
1508 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1509 AND A.REQUEST_ID = p_REQUEST_ID
1510 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1511 AND A.ORG_ID = p_ORG_ID
1512 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1513
1514 UPDATE IGC_CC_HEADERS
1515 SET CC_APPRVL_STATUS = 'IP'
1516 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
1517
1518 SELECT CC_STATE,CC_APPRVL_STATUS
1519 INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
1520 FROM IGC_CC_HEADERS
1521 WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
1522
1523
1524 IF l_STATE = 'CM' AND l_PREVIOUS_APPRVL_STATUS = 'AP'
1525 THEN
1526
1527 BEGIN
1528 SELECT 'Y'
1529 INTO l_DUMMY
1530 FROM PO_HEADERS_ALL A
1531 WHERE
1532 A.PO_HEADER_ID =
1533 (SELECT C.PO_HEADER_ID
1534 FROM IGC_CC_HEADERS B,
1535 PO_HEADERS_ALL C
1536 WHERE B.ORG_ID = C.ORG_ID AND
1537 B.CC_NUM = C.SEGMENT1 AND
1538 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
1539 B.CC_HEADER_ID = V9.CC_HEADER_ID );
1540
1541
1542 UPDATE PO_HEADERS_ALL
1543 SET APPROVED_FLAG = 'N'
1544 WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
1545 (SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
1546 FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
1547 WHERE a.SEGMENT1 = b.CC_NUM
1548 AND a.ORG_ID = b.ORG_ID
1549 AND a.TYPE_LOOKUP_CODE = 'STANDARD'
1550 AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
1551
1552 EXCEPTION
1553 WHEN NO_DATA_FOUND
1554 THEN
1555 NULL;
1556 END;
1557 END IF;
1558
1559 END IF;
1560
1561 IF p_PROCESS_PHASE = 'P'
1562 THEN
1563 IF p_PROCESS_TYPE = 'Y'
1564 THEN
1565 UPDATE IGC_CC_PROCESS_DATA A
1566 SET VALIDATION_STATUS = 'P'
1567 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1568 AND A.REQUEST_ID = p_REQUEST_ID
1569 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1570 AND A.ORG_ID = p_ORG_ID
1571 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1572 END IF;
1573 END IF;
1574 END IF;
1575 END IF;
1576
1577
1578 IF V9.CC_STATE = 'CM' AND V9.CC_APPRVL_STATUS = 'IP'
1579 THEN
1580 l_EXCEPTION := NULL;
1581 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CM_INPROCESS');
1582 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1583 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
1584 l_EXCEPTION := FND_MESSAGE.GET;
1585
1586 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1587 (PROCESS_TYPE ,
1588 PROCESS_PHASE,
1589 CC_HEADER_ID,
1590 CC_ACCT_LINE_ID ,
1591 CC_DET_PF_LINE_ID,
1592 EXCEPTION_REASON,
1593 ORG_ID,
1594 SET_OF_BOOKS_ID,
1595 REQUEST_ID)
1596 VALUES (P_PROCESS_TYPE,
1597 p_PROCESS_PHASE,
1598 V9.CC_HEADER_ID,
1599 NULL,
1600 NULL,
1601 l_EXCEPTION,
1602 V3.ORG_ID,
1603 V3.SET_OF_BOOKS_ID,
1604 p_REQUEST_ID);
1605
1606 l_EXCEPTION := NULL;
1607 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1608 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1609 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1610 l_EXCEPTION := FND_MESSAGE.GET;
1611
1612 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1613 (PROCESS_TYPE ,
1614 PROCESS_PHASE,
1615 CC_HEADER_ID,
1616 CC_ACCT_LINE_ID ,
1617 CC_DET_PF_LINE_ID,
1618 EXCEPTION_REASON,
1619 ORG_ID,
1620 SET_OF_BOOKS_ID,
1621 REQUEST_ID)
1622 VALUES (P_PROCESS_TYPE,
1623 p_PROCESS_PHASE,
1624 V3.CC_HEADER_ID,
1625 NULL,
1626 NULL,
1627 l_EXCEPTION,
1628 V3.ORG_ID,
1629 V3.SET_OF_BOOKS_ID,
1630 p_REQUEST_ID);
1631
1632 IF p_PROCESS_TYPE = 'Y'
1633 THEN
1634 UPDATE IGC_CC_PROCESS_DATA A
1635 SET VALIDATION_STATUS = 'F'
1636 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1637 AND A.REQUEST_ID = p_REQUEST_ID
1638 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1639 AND A.ORG_ID = p_ORG_ID
1640 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1641 END IF;
1642
1643 l_COUNTER := l_COUNTER + 1;
1644
1645 END IF;
1646
1647 IF V9.CC_STATE = 'CM' AND V9.CC_APPRVL_STATUS <> 'IP'
1648 THEN
1649 l_OVERBILLED_COUNTER := 0;
1650 OPEN C4(V9.CC_HEADER_ID); /* Over Billed Amount Validation for Standard CC */
1651 /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
1652 LOOP
1653 FETCH C4 INTO V4;
1654 EXIT WHEN C4%NOTFOUND;
1655 OPEN C5(V4.CC_ACCT_LINE_ID);
1656 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
1657 LOOP
1658 FETCH C5 INTO V5;
1659 EXIT WHEN C5%NOTFOUND;
1660
1661 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
1662 THEN
1663 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
1664 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1665 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
1666 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
1667 l_EXCEPTION := FND_MESSAGE.GET;
1668
1669 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1670 (PROCESS_TYPE ,
1671 PROCESS_PHASE,
1672 CC_HEADER_ID,
1673 CC_ACCT_LINE_ID ,
1674 CC_DET_PF_LINE_ID,
1675 EXCEPTION_REASON,
1676 ORG_ID,
1677 SET_OF_BOOKS_ID,
1678 REQUEST_ID)
1679 VALUES (P_PROCESS_TYPE,
1680
1681 p_PROCESS_PHASE,
1682 V9.CC_HEADER_ID,
1683 V5.CC_ACCT_LINE_ID,
1684 V5.CC_DET_PF_LINE_ID,
1685 l_EXCEPTION,
1686 V3.ORG_ID,
1687 V3.SET_OF_BOOKS_ID,
1688 p_REQUEST_ID);
1689
1690 l_EXCEPTION := NULL;
1691 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1692 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1693 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1694 l_EXCEPTION := FND_MESSAGE.GET;
1695
1696 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1697 (PROCESS_TYPE ,
1698 PROCESS_PHASE,
1699 CC_HEADER_ID,
1700 CC_ACCT_LINE_ID ,
1701 CC_DET_PF_LINE_ID,
1702 EXCEPTION_REASON,
1703 ORG_ID,
1704 SET_OF_BOOKS_ID,
1705 REQUEST_ID)
1706 VALUES (P_PROCESS_TYPE,
1707 p_PROCESS_PHASE,
1708 V3.CC_HEADER_ID,
1709 NULL,
1710 NULL,
1711 l_EXCEPTION,
1712 V3.ORG_ID,
1713 V3.SET_OF_BOOKS_ID,
1714 p_REQUEST_ID);
1715
1716 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
1717 l_COUNTER := l_COUNTER + 1;
1718 END IF;
1719
1720 END LOOP; /* End of Loop for Cursor C5 */
1721 CLOSE C5;
1722 END LOOP; /* End of Loop for Cursor C4 */
1723 CLOSE C4;
1724
1725 IF l_OVERBILLED_COUNTER > 0
1726 THEN
1727 IF p_PROCESS_TYPE = 'Y'
1728 THEN
1729 UPDATE IGC_CC_PROCESS_DATA A
1730 SET VALIDATION_STATUS = 'F'
1731 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1732 AND A.REQUEST_ID = p_REQUEST_ID
1733 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1734 AND A.ORG_ID = p_ORG_ID
1735 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1736 END IF;
1737 ELSE
1738 IF p_PROCESS_PHASE = 'F' AND p_PROCESS_TYPE = 'Y'
1739 THEN
1740
1741 UPDATE IGC_CC_PROCESS_DATA A
1742 SET VALIDATION_STATUS = 'P'
1743 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1744 AND A.REQUEST_ID = p_REQUEST_ID
1745 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1746 AND A.ORG_ID = p_ORG_ID
1747 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1748
1749 UPDATE IGC_CC_HEADERS
1750 SET CC_APPRVL_STATUS = 'IP'
1751 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
1752
1753 SELECT CC_STATE,CC_APPRVL_STATUS
1754 INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
1755 FROM IGC_CC_HEADERS
1756 WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
1757
1758 IF l_STATE = 'CM' AND l_PREVIOUS_APPRVL_STATUS = 'AP'
1759 THEN
1760
1761 BEGIN
1762 SELECT 'Y'
1763 INTO l_DUMMY
1764 FROM PO_HEADERS_ALL A
1765 WHERE
1766 A.PO_HEADER_ID =
1767 (SELECT C.PO_HEADER_ID
1768 FROM IGC_CC_HEADERS B,
1769 PO_HEADERS_ALL C
1770 WHERE B.ORG_ID = C.ORG_ID AND
1771 B.CC_NUM = C.SEGMENT1 AND
1772 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
1773 B.CC_HEADER_ID = V9.CC_HEADER_ID );
1774
1775
1776 UPDATE PO_HEADERS_ALL
1777 SET APPROVED_FLAG = 'N'
1778 WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
1779 (SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
1780 FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
1781 WHERE a.SEGMENT1 = b.CC_NUM
1782 AND a.ORG_ID = b.ORG_ID
1783 AND a.TYPE_LOOKUP_CODE = 'STANDARD'
1784 AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
1785
1786 EXCEPTION
1787 WHEN NO_DATA_FOUND
1788 THEN
1789 NULL;
1790 END;
1791 END IF;
1792
1793 END IF;
1794
1795 IF p_PROCESS_PHASE = 'P' AND p_PROCESS_TYPE = 'Y'
1796 THEN
1797 UPDATE IGC_CC_PROCESS_DATA A
1798 SET VALIDATION_STATUS = 'P'
1799 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1800 AND A.REQUEST_ID = p_REQUEST_ID
1801 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1802 AND A.ORG_ID = p_ORG_ID
1803 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1804 END IF;
1805
1806 END IF;
1807
1808 END IF;
1809
1810 IF V9.CC_STATE = 'CT' AND V9.CC_APPRVL_STATUS = 'IP'
1811 THEN
1812 l_EXCEPTION := NULL;
1813 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CT_INPROCESS');
1814 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1815 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
1816 l_EXCEPTION := FND_MESSAGE.GET;
1817
1818 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1819 (PROCESS_TYPE ,
1820 PROCESS_PHASE,
1821 CC_HEADER_ID,
1822 CC_ACCT_LINE_ID ,
1823 CC_DET_PF_LINE_ID,
1824 EXCEPTION_REASON,
1825 ORG_ID,
1826 SET_OF_BOOKS_ID,
1827 REQUEST_ID)
1828 VALUES (P_PROCESS_TYPE,
1829 p_PROCESS_PHASE,
1830 V9.CC_HEADER_ID,
1831 NULL,
1832 NULL,
1833 l_EXCEPTION,
1834 V3.ORG_ID,
1835 V3.SET_OF_BOOKS_ID,
1836 p_REQUEST_ID);
1837
1838 l_EXCEPTION := NULL;
1839 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1840 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1841 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1842 l_EXCEPTION := FND_MESSAGE.GET;
1843
1844 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1845 (PROCESS_TYPE ,
1846 PROCESS_PHASE,
1847 CC_HEADER_ID,
1848 CC_ACCT_LINE_ID ,
1849 CC_DET_PF_LINE_ID,
1850 EXCEPTION_REASON,
1851 ORG_ID,
1852 SET_OF_BOOKS_ID,
1853 REQUEST_ID)
1854 VALUES (P_PROCESS_TYPE,
1855 p_PROCESS_PHASE,
1856 V3.CC_HEADER_ID,
1857 NULL,
1858 NULL,
1859 l_EXCEPTION,
1860 V3.ORG_ID,
1861 V3.SET_OF_BOOKS_ID,
1862 p_REQUEST_ID);
1863 IF (p_PROCESS_TYPE = 'Y')
1864 THEN
1865 UPDATE IGC_CC_PROCESS_DATA A
1866 SET VALIDATION_STATUS = 'F'
1867 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1868 AND A.REQUEST_ID = p_REQUEST_ID
1869 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1870 AND A.ORG_ID = p_ORG_ID
1871 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1872 END IF;
1873
1874 l_COUNTER := l_COUNTER + 1;
1875
1876 END IF;
1877
1878 IF V9.CC_STATE = 'CT' AND V9.CC_APPRVL_STATUS <> 'IP'
1879 THEN
1880 l_OVERBILLED_COUNTER := 0;
1881 OPEN C4(V9.CC_HEADER_ID); /* Over Billed Amount Validation for Standard CC */
1882 /* Fetching Records based upon CC_HEADER_ID in CC_ACCT_LINES */
1883 LOOP
1884 FETCH C4 INTO V4;
1885 EXIT WHEN C4%NOTFOUND;
1886 OPEN C5(V4.CC_ACCT_LINE_ID);
1887 /* Fetching Records based upon CC_HEADER_ID in CC_DET_PF */
1888 LOOP
1889 FETCH C5 INTO V5;
1890 EXIT WHEN C5%NOTFOUND;
1891
1892 IF V5.CC_DET_PF_BILLED_AMT > V5.CC_DET_PF_ENTERED_AMT
1893 THEN
1894 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_OVERBILLED');
1895 FND_MESSAGE.SET_TOKEN('NUMBER',V9.CC_NUM,TRUE);
1896 FND_MESSAGE.SET_TOKEN('ACCT_LINE_NUM',V4.CC_ACCT_LINE_NUM,TRUE);
1897 FND_MESSAGE.SET_TOKEN('PF_LINE_NUM',V5.CC_DET_PF_LINE_NUM,TRUE);
1898 l_EXCEPTION := FND_MESSAGE.GET;
1899
1900 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1901 (PROCESS_TYPE ,
1902 PROCESS_PHASE,
1903 CC_HEADER_ID,
1904 CC_ACCT_LINE_ID ,
1905 CC_DET_PF_LINE_ID,
1906 EXCEPTION_REASON,
1907 ORG_ID,
1908 SET_OF_BOOKS_ID,
1909 REQUEST_ID)
1910 VALUES (P_PROCESS_TYPE,
1911 p_PROCESS_PHASE,
1912 V9.CC_HEADER_ID,
1913 V5.CC_ACCT_LINE_ID,
1914 V5.CC_DET_PF_LINE_ID,
1915 l_EXCEPTION,
1916 V3.ORG_ID,
1917 V3.SET_OF_BOOKS_ID,
1918 p_REQUEST_ID);
1919
1920 l_EXCEPTION := NULL;
1921 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_REL_COVER_FAIL');
1922 FND_MESSAGE.SET_TOKEN('NUMBER1',V9.CC_NUM,TRUE);
1923 FND_MESSAGE.SET_TOKEN('NUMBER2',V3.CC_NUM,TRUE);
1924 l_EXCEPTION := FND_MESSAGE.GET;
1925
1926 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1927 (PROCESS_TYPE ,
1928 PROCESS_PHASE,
1929 CC_HEADER_ID,
1930 CC_ACCT_LINE_ID ,
1931 CC_DET_PF_LINE_ID,
1932 EXCEPTION_REASON,
1933 ORG_ID,
1934 SET_OF_BOOKS_ID,
1935 REQUEST_ID)
1936 VALUES (P_PROCESS_TYPE,
1937 p_PROCESS_PHASE,
1938 V3.CC_HEADER_ID,
1939 NULL,
1940 NULL,
1941 l_EXCEPTION,
1942 V3.ORG_ID,
1943 V3.SET_OF_BOOKS_ID,
1944 p_REQUEST_ID);
1945
1946 l_OVERBILLED_COUNTER := l_OVERBILLED_COUNTER + 1;
1947 l_COUNTER := l_COUNTER + 1;
1948 END IF;
1949
1950 END LOOP; /* End of Loop for Cursor C5 */
1951 CLOSE C5;
1952 END LOOP; /* End of Loop for Cursor C4 */
1953 CLOSE C4;
1954
1955 IF l_OVERBILLED_COUNTER > 0
1956 THEN
1957 UPDATE IGC_CC_PROCESS_DATA A
1958 SET VALIDATION_STATUS = 'F'
1959 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1960 AND A.REQUEST_ID = p_REQUEST_ID
1961 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1962 AND A.ORG_ID = p_ORG_ID
1963 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1964 ELSE
1965 IF p_PROCESS_PHASE = 'F' AND P_PROCESS_TYPE = 'Y'
1966 THEN
1967
1968 UPDATE IGC_CC_PROCESS_DATA A
1969 SET VALIDATION_STATUS = 'P'
1970 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
1971 AND A.REQUEST_ID = p_REQUEST_ID
1972 AND A.PROCESS_TYPE = p_PROCESS_TYPE
1973 AND A.ORG_ID = p_ORG_ID
1974 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
1975
1976 UPDATE IGC_CC_HEADERS
1977 SET CC_APPRVL_STATUS = 'IP'
1978 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V9.CC_HEADER_ID;
1979
1980 SELECT CC_STATE,CC_APPRVL_STATUS
1981 INTO l_STATE,l_PREVIOUS_APPRVL_STATUS
1982 FROM IGC_CC_HEADERS
1983 WHERE CC_HEADER_ID = V9.CC_HEADER_ID;
1984
1985 IF l_STATE = 'CM' AND l_PREVIOUS_APPRVL_STATUS = 'AP'
1986 THEN
1987
1988 BEGIN
1989 SELECT 'Y'
1990 INTO l_DUMMY
1991 FROM PO_HEADERS_ALL A
1992 WHERE
1993 A.PO_HEADER_ID =
1994 (SELECT C.PO_HEADER_ID
1995 FROM IGC_CC_HEADERS B,
1996 PO_HEADERS_ALL C
1997 WHERE B.ORG_ID = C.ORG_ID AND
1998 B.CC_NUM = C.SEGMENT1 AND
1999 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
2000 B.CC_HEADER_ID = V9.CC_HEADER_ID );
2001
2002
2003 UPDATE PO_HEADERS_ALL
2004 SET APPROVED_FLAG = 'N'
2005 WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN
2006 (SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
2007 FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
2008 WHERE a.SEGMENT1 = b.CC_NUM
2009 AND a.ORG_ID = b.ORG_ID
2010 AND a.TYPE_LOOKUP_CODE = 'STANDARD'
2011 AND b.CC_HEADER_ID = V9.CC_HEADER_ID);
2012
2013 EXCEPTION
2014 WHEN NO_DATA_FOUND
2015 THEN
2016 NULL;
2017 END;
2018 END IF;
2019
2020 END IF;
2021 IF p_PROCESS_PHASE = 'P'
2022 THEN
2023 UPDATE IGC_CC_PROCESS_DATA A
2024 SET VALIDATION_STATUS = 'P'
2025 WHERE A.CC_HEADER_ID = V9.CC_HEADER_ID
2026 AND A.REQUEST_ID = p_REQUEST_ID
2027 AND A.PROCESS_TYPE = p_PROCESS_TYPE
2028 AND A.ORG_ID = p_ORG_ID
2029 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
2030 END IF;
2031
2032 END IF;
2033 END IF;
2034 COMMIT;
2035 END LOOP; /* End of LOOP C6 */
2036 CLOSE C9; /* CURSOR C9 CLOSED */
2037
2038 /******************* End of Checking Release Validation ********************/
2039
2040
2041 /***** Checking Cover Validation after chacking Corresponding Releases *****/
2042
2043 /* Fully Encumbrance check for YEAR END PROCESS */
2044
2045 IF p_PROCESS_TYPE = 'Y'
2046 THEN
2047 IF ( ( (v3.cc_state = 'PR') AND (p_prov_enc_on = TRUE) ) OR
2048 (v3.cc_state = 'CM') OR
2049 ( ( (v3.cc_state = 'CL') AND (p_prov_enc_on = TRUE) ) AND (v3.cc_apprvl_status <> 'AP')) OR
2050 ( (v3.cc_state = 'CT') AND (v3.cc_apprvl_status <> 'AP'))
2051 )
2052 AND V3.CC_ENCMBRNC_STATUS = 'N'
2053 THEN
2054 l_EXCEPTION := NULL;
2055 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_NOT_FULLY_ENC');
2056 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
2057 l_EXCEPTION := FND_MESSAGE.GET;
2058
2059 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2060 (PROCESS_TYPE ,
2061 PROCESS_PHASE,
2062 CC_HEADER_ID,
2063 CC_ACCT_LINE_ID ,
2064 CC_DET_PF_LINE_ID,
2065 EXCEPTION_REASON,
2066 ORG_ID,
2067 SET_OF_BOOKS_ID,
2068 REQUEST_ID)
2069 VALUES (P_PROCESS_TYPE,
2070
2071 p_PROCESS_PHASE,
2072 V3.CC_HEADER_ID,
2073 NULL,
2074 NULL,
2075 l_EXCEPTION,
2076 V3.ORG_ID,
2077 V3.SET_OF_BOOKS_ID,
2078 p_REQUEST_ID);
2079 RETURN 'F';
2080
2081 END IF;
2082 END IF;
2083
2084 /* END OF Fully Encumbrance check for YEAR END PROCESS */
2085
2086
2087 IF V3.CC_STATE = 'PR' AND V3.CC_APPRVL_STATUS = 'IP'
2088 THEN
2089 l_EXCEPTION := NULL;
2090 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_PR_INPROCESS');
2091 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
2092 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
2093 l_EXCEPTION := FND_MESSAGE.GET;
2094
2095 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2096 (PROCESS_TYPE ,
2097 PROCESS_PHASE,
2098 CC_HEADER_ID,
2099 CC_ACCT_LINE_ID ,
2100 CC_DET_PF_LINE_ID,
2101 EXCEPTION_REASON,
2102 ORG_ID,
2103 SET_OF_BOOKS_ID,
2104 REQUEST_ID)
2105 VALUES (P_PROCESS_TYPE,
2106 p_PROCESS_PHASE,
2107 V3.CC_HEADER_ID,
2108 NULL,
2109 NULL,
2110 l_EXCEPTION,
2111 V3.ORG_ID,
2112 V3.SET_OF_BOOKS_ID,
2113 p_REQUEST_ID);
2114 IF (p_PROCESS_TYPE = 'Y')
2115 THEN
2116 UPDATE IGC_CC_PROCESS_DATA A
2117 SET VALIDATION_STATUS = 'F'
2118 WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
2119 AND A.REQUEST_ID = p_REQUEST_ID
2120 AND A.PROCESS_TYPE = p_PROCESS_TYPE
2121 AND A.ORG_ID = p_ORG_ID
2122 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
2123 END IF;
2124
2125 RETURN 'F';
2126
2127
2128 END IF;
2129
2130 IF V3.CC_STATE = 'CL' AND V3.CC_APPRVL_STATUS = 'IP'
2131 THEN
2132 l_EXCEPTION := NULL;
2133 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CL_INPROCESS');
2134 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
2135 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
2136 l_EXCEPTION := FND_MESSAGE.GET;
2137
2138 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2139 (PROCESS_TYPE ,
2140 PROCESS_PHASE,
2141 CC_HEADER_ID,
2142 CC_ACCT_LINE_ID ,
2143 CC_DET_PF_LINE_ID,
2144 EXCEPTION_REASON,
2145 ORG_ID,
2146 SET_OF_BOOKS_ID,
2147 REQUEST_ID)
2148 VALUES (P_PROCESS_TYPE,
2149 p_PROCESS_PHASE,
2150 V3.CC_HEADER_ID,
2151 NULL,
2152 NULL,
2153 l_EXCEPTION,
2154 V3.ORG_ID,
2155 V3.SET_OF_BOOKS_ID,
2156 p_REQUEST_ID);
2157 IF (p_PROCESS_TYPE = 'Y')
2158 THEN
2159 UPDATE IGC_CC_PROCESS_DATA A
2160 SET VALIDATION_STATUS = 'F'
2161 WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
2162 AND A.REQUEST_ID = p_REQUEST_ID
2163 AND A.PROCESS_TYPE = p_PROCESS_TYPE
2164 AND A.ORG_ID = p_ORG_ID
2165 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
2166 END IF;
2167
2168 RETURN 'F';
2169
2170
2171 END IF;
2172
2173 IF V3.CC_STATE = 'CM' AND V3.CC_APPRVL_STATUS = 'IP'
2174 THEN
2175 l_EXCEPTION := NULL;
2176 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CM_INPROCESS');
2177 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
2178 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
2179 l_EXCEPTION := FND_MESSAGE.GET;
2180
2181 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2182 (PROCESS_TYPE ,
2183 PROCESS_PHASE,
2184 CC_HEADER_ID,
2185 CC_ACCT_LINE_ID ,
2186 CC_DET_PF_LINE_ID,
2187 EXCEPTION_REASON,
2188 ORG_ID,
2189 SET_OF_BOOKS_ID,
2190 REQUEST_ID)
2191 VALUES (P_PROCESS_TYPE,
2192 p_PROCESS_PHASE,
2193 V3.CC_HEADER_ID,
2194 NULL,
2195 NULL,
2196 l_EXCEPTION,
2197 V3.ORG_ID,
2198 V3.SET_OF_BOOKS_ID,
2199 p_REQUEST_ID);
2200
2201
2202 IF (p_PROCESS_TYPE = 'Y')
2203 THEN
2204 UPDATE IGC_CC_PROCESS_DATA A
2205 SET VALIDATION_STATUS = 'F'
2206 WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
2207 AND A.REQUEST_ID = p_REQUEST_ID
2208 AND A.PROCESS_TYPE = p_PROCESS_TYPE
2209 AND A.ORG_ID = p_ORG_ID
2210 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
2211 END IF;
2212
2213 RETURN 'F';
2214
2215
2216 END IF;
2217
2218 IF V3.CC_STATE = 'CT' AND V3.CC_APPRVL_STATUS = 'IP'
2219 THEN
2220 l_EXCEPTION := NULL;
2221 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CT_INPROCESS');
2222 FND_MESSAGE.SET_TOKEN('NUMBER',V3.CC_NUM,TRUE);
2223 FND_MESSAGE.SET_TOKEN('PROCESS_TYPE',l_PROCESS_TYPE,TRUE);
2224 l_EXCEPTION := FND_MESSAGE.GET;
2225
2226 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2227 (PROCESS_TYPE ,
2228 PROCESS_PHASE,
2229 CC_HEADER_ID,
2230 CC_ACCT_LINE_ID ,
2231 CC_DET_PF_LINE_ID,
2232 EXCEPTION_REASON,
2233 ORG_ID,
2234 SET_OF_BOOKS_ID,
2235 REQUEST_ID)
2236 VALUES (P_PROCESS_TYPE,
2237 p_PROCESS_PHASE,
2238 V3.CC_HEADER_ID,
2239 NULL,
2240 NULL,
2241 l_EXCEPTION,
2242 V3.ORG_ID,
2243 V3.SET_OF_BOOKS_ID,
2244 p_REQUEST_ID);
2245
2246 IF (p_PROCESS_TYPE = 'Y')
2247 THEN
2248 UPDATE IGC_CC_PROCESS_DATA A
2249 SET VALIDATION_STATUS = 'F'
2250 WHERE A.CC_HEADER_ID = V3.CC_HEADER_ID
2251 AND A.REQUEST_ID = p_REQUEST_ID
2252 AND A.PROCESS_TYPE = p_PROCESS_TYPE
2253 AND A.ORG_ID = p_ORG_ID
2254 AND A.SET_OF_BOOKS_ID = p_SOB_ID;
2255 END IF;
2256
2257 RETURN 'F';
2258
2259
2260 END IF;
2261
2262 /****************** End of Checking Cover Validation ********************/
2263 IF l_COUNTER > 0
2264 THEN
2265 RETURN 'F';
2266 ELSE
2267 RETURN 'P'; /* if all IF Statements failed then Validation Status is Passed */
2268 END IF;
2269 END IF;
2270 END LOOP; /* End of Loop for Cursor C3 */
2271 CLOSE C3; /* Cursor Closed */
2272
2273 /******** End of Validation Check for Cover and Release CC For Both Preliminary And Final Mode **********/
2274
2275
2276 END VALIDATE_CC;
2277
2278 /*==================================================================================
2279 End of VALIDATE_CC Procedure
2280 =================================================================================*/
2281
2282
2283
2284 /* Checks whether all the invoices related to
2285 Contract Commitment are either paid or cancelled */
2286
2287
2288 /*==================================================================================
2289 Procedure INVOICE_CANC_OR_PAID
2290 =================================================================================*/
2291 FUNCTION invoice_canc_or_paid(p_cc_header_id NUMBER)
2292 RETURN BOOLEAN
2293 IS
2294 l_count NUMBER := 0;
2295 l_paid_canc_count NUMBER := 0;
2296 l_inv_dist_total NUMBER := 0;
2297
2298 l_invoice_id ap_invoices_all.invoice_id%TYPE;
2299 l_cancelled_date ap_invoices_all.cancelled_date%TYPE;
2300 l_payment_status_flag ap_invoices_all.payment_status_flag%TYPE;
2301
2302 l_po_header_id po_headers_all.po_header_id%TYPE;
2303 l_po_distribution_id po_distributions_all.po_distribution_id%TYPE;
2304
2305 l_invoice_canc_paid_flag BOOLEAN := FALSE;
2306
2307 CURSOR c_po_distributions(p_cc_header_id NUMBER)
2308 IS
2309 SELECT pod.po_distribution_id
2310 FROM
2311 po_distributions_all pod,
2312 po_headers_all phh,
2313 igc_cc_headers cch
2314 WHERE
2315 pod.po_header_id = phh.po_header_id AND
2316 phh.org_id = cch.org_id AND
2317 phh.type_lookup_code = 'STANDARD' AND
2318 phh.segment1 = cch.cc_num AND
2319 cch.cc_header_id = p_cc_header_id;
2320
2321 CURSOR c_invoices(p_po_distribution_id NUMBER)
2322 IS
2323 SELECT unique api.invoice_id
2324 FROM
2325 ap_invoice_distributions_all apid,
2326 ap_invoices_all api
2327 WHERE
2328 apid.invoice_id = api.invoice_id AND
2329 apid.po_distribution_id = p_po_distribution_id;
2330 BEGIN
2331 l_count := 0;
2332
2333 l_paid_canc_count := 0;
2334
2335 OPEN c_po_distributions(p_cc_header_id);
2336 LOOP
2337 FETCH c_po_distributions INTO l_po_distribution_id;
2338 EXIT WHEN c_po_distributions%NOTFOUND;
2339
2340 OPEN c_invoices(l_po_distribution_id);
2341 LOOP
2342 FETCH c_invoices INTO l_invoice_id;
2343 EXIT WHEN c_invoices%NOTFOUND;
2344
2345 l_count := l_count + 1;
2346
2347 l_cancelled_date := NULL;
2348 l_payment_status_flag := 'N';
2349
2350 SELECT cancelled_date , NVL(payment_status_flag,'N')
2351 INTO l_cancelled_date, l_payment_status_flag
2352 FROM ap_invoices_all
2353 WHERE invoice_id = l_invoice_id;
2354
2355 IF (l_cancelled_date IS NOT NULL) OR (l_payment_status_flag = 'Y')
2356 THEN
2357 l_paid_canc_count := l_paid_canc_count + 1;
2358 ELSE
2359 /* check for invoice distribution lines reversal */
2360 l_inv_dist_total := 9999;
2361
2362 SELECT SUM(NVL(apid.amount,0))
2363 INTO l_inv_dist_total
2364 FROM
2365 ap_invoice_distributions_all apid
2366 WHERE
2367 apid.invoice_id = l_invoice_id AND
2368 apid.po_distribution_id = l_po_distribution_id ;
2369
2370 IF (l_inv_dist_total <> 0)
2371 THEN
2372 CLOSE c_invoices;
2373 CLOSE c_po_distributions;
2374 RETURN(TRUE);
2375 END IF;
2376 l_count := l_count - 1;
2377
2378 END IF;
2379
2380
2381 END LOOP;
2382 CLOSE c_invoices;
2383
2384 END LOOP;
2385 CLOSE c_po_distributions;
2386
2387
2388 IF (l_count = l_paid_canc_count)
2389 THEN
2390 RETURN(FALSE);
2391 ELSE
2392 RETURN(TRUE);
2393 END IF;
2394
2395 END invoice_canc_or_paid;
2396
2397 /* Inserts row into budgetary control interface table */
2398
2399 PROCEDURE Insert_Interface_Row(p_cc_interface_rec IN igc_cc_interface%ROWTYPE, l_insert_return_status OUT NOCOPY VARCHAR2)
2400 IS
2401 BEGIN
2402 INSERT INTO igc_cc_interface (
2403 batch_line_num,
2404 cc_header_id,
2405 cc_version_num,
2406 cc_acct_line_id,
2407 cc_det_pf_line_id,
2408 set_of_books_id,
2409 code_combination_id,
2410 cc_transaction_date,
2411 transaction_description,
2412 encumbrance_type_id,
2413 currency_code,
2414 cc_func_dr_amt,
2415 cc_func_cr_amt,
2416 je_source_name,
2417 je_category_name,
2418 actual_flag,
2419 budget_dest_flag,
2420 last_update_date,
2421 last_updated_by,
2422 last_update_login,
2423 creation_date,
2424 created_by,
2425 period_set_name,
2426 period_name,
2427 cbc_result_code,
2428 status_code,
2429 budget_version_id,
2430 budget_amt,
2431 commitment_encmbrnc_amt,
2432 obligation_encmbrnc_amt,
2433 funds_available_amt,
2434 document_type,
2435 reference_1,
2436 reference_2,
2437 reference_3,
2438 reference_4,
2439 reference_5,
2440 reference_6,
2441 reference_7,
2442 reference_8,
2443 reference_9,
2444 reference_10,
2445 cc_encmbrnc_date,
2446 /*Bug No : 6341012. SLA Uptake. Project_Line field added*/
2447 project_line
2448 )
2449 VALUES
2450 (p_cc_interface_rec.batch_line_num,
2451 p_cc_interface_rec.cc_header_id,
2452 p_cc_interface_rec.cc_version_num,
2453 p_cc_interface_rec.cc_acct_line_id,
2454 p_cc_interface_rec.cc_det_pf_line_id,
2455 p_cc_interface_rec.set_of_books_id,
2456 p_cc_interface_rec.code_combination_id,
2457 p_cc_interface_rec.cc_transaction_date,
2458 p_cc_interface_rec.transaction_description,
2459 p_cc_interface_rec.encumbrance_type_id,
2460 p_cc_interface_rec.currency_code,
2461 p_cc_interface_rec.cc_func_dr_amt,
2462 p_cc_interface_rec.cc_func_cr_amt,
2463 p_cc_interface_rec.je_source_name,
2464 p_cc_interface_rec.je_category_name,
2465 p_cc_interface_rec.actual_flag,
2466 p_cc_interface_rec.budget_dest_flag,
2467 p_cc_interface_rec.last_update_date,
2468 p_cc_interface_rec.last_updated_by,
2469 p_cc_interface_rec.last_update_login,
2470 p_cc_interface_rec.creation_date,
2471 p_cc_interface_rec.created_by,
2472 p_cc_interface_rec.period_set_name,
2473 p_cc_interface_rec.period_name,
2474 p_cc_interface_rec.cbc_result_code,
2475 p_cc_interface_rec.status_code,
2476 p_cc_interface_rec.budget_version_id,
2477 p_cc_interface_rec.budget_amt,
2478 p_cc_interface_rec.commitment_encmbrnc_amt,
2479 p_cc_interface_rec.obligation_encmbrnc_amt,
2480 p_cc_interface_rec.funds_available_amt,
2481 p_cc_interface_rec.document_type,
2482 p_cc_interface_rec.reference_1,
2483 p_cc_interface_rec.reference_2,
2484 p_cc_interface_rec.reference_3,
2485 p_cc_interface_rec.reference_4,
2486 p_cc_interface_rec.reference_5,
2487 p_cc_interface_rec.reference_6,
2488 p_cc_interface_rec.reference_7,
2489 p_cc_interface_rec.reference_8,
2490 p_cc_interface_rec.reference_9,
2491 p_cc_interface_rec.reference_10,
2492 p_cc_interface_rec.cc_encmbrnc_date,
2493 /*Bug No : 6341012. SLA Uptake. Project_Line field added*/
2494 p_cc_interface_rec.project_line
2495 );
2496
2497 EXCEPTION
2498 WHEN OTHERS THEN
2499 l_insert_return_status := 'F';
2500
2501 IF (l_state_level >= l_debug_level) THEN
2502 FND_LOG.STRING(l_state_level, 'igc_cc_rep_yep_pvt.insert_interface_row',
2503 'Record not inserted' || to_char(sysdate,'DD-MON-YY:MI:SS'));
2504 END IF;
2505
2506
2507 END Insert_Interface_Row;
2508
2509 /* Populates the interface table for the budgetary control as per the process*/
2510
2511 PROCEDURE Process_Interface_Row(
2512 /*Bug No : 6341012. SLA Uptake. Encumbrance_Type_Ids are not required*/
2513 -- p_cc_prov_enc_type_id IN NUMBER,
2514 -- p_cc_conf_enc_type_id IN NUMBER,
2515 -- p_req_encumbrance_type_id IN financials_system_params_all.req_encumbrance_type_id%TYPE,
2516 -- p_purch_encumbrance_type_id IN financials_system_params_all.purch_encumbrance_type_id%TYPE,
2517 p_currency_code IN VARCHAR2,
2518 p_cc_headers_rec IN igc_cc_headers%ROWTYPE,
2519 p_cc_acct_lines_rec IN igc_cc_acct_lines%ROWTYPE,
2520 p_cc_pmt_fcst_rec IN igc_cc_det_pf%ROWTYPE,
2521 p_mode IN VARCHAR2,
2522 p_type IN VARCHAR2,
2523 p_process_type IN VARCHAR2,
2524 p_yr_end_cr_date IN DATE,
2525 p_yr_end_dr_date IN DATE,
2526 p_rate_date IN DATE,
2527 p_rate IN NUMBER,
2528 p_revalue_fix_date IN DATE,
2529 l_insert_status OUT NOCOPY VARCHAR2
2530 )
2531 IS
2532
2533 l_cc_interface_rec igc_cc_interface%ROWTYPE;
2534 l_insert_return_status VARCHAR2(1);
2535 E_RETURN_FAIL exception;
2536 l_ent_amt NUMBER;
2537 l_enc_amt NUMBER;
2538 l_func_amt NUMBER;
2539 l_func_billed_amt NUMBER;
2540 l_tran_amount NUMBER;
2541 l_cr_tran_amount NUMBER;
2542 l_dr_tran_amount NUMBER;
2543 l_billed_amt NUMBER;
2544 l_unbilled_amt NUMBER;
2545 l_old_rate NUMBER;
2546
2547 l_unbilled_tax_amt NUMBER;
2548 l_cr_tran_tax_amt NUMBER;
2549 l_dr_tran_tax_amt NUMBER;
2550 l_tran_tax_amt NUMBER;
2551 l_return_status VARCHAR2(1);
2552 l_msg_count NUMBER;
2553 l_msg_data VARCHAR2(2000);
2554
2555 -- bug 2043221 ssmales - added declaration on line below
2556 l_withheld_tran_amt NUMBER;
2557
2558 l_cover_cc_num igc_cc_headers.cc_num%TYPE;
2559 l_cover_cc_version_num igc_cc_headers.cc_version_num%TYPE;
2560 l_cover_set_of_books_id igc_cc_headers.set_of_books_id%TYPE;
2561 l_cover_cc_acct_date igc_cc_headers.cc_acct_date%TYPE;
2562
2563 l_cover_cc_acct_desc igc_cc_acct_lines.cc_acct_desc%TYPE;
2564 l_cover_budg_code_comb_id igc_cc_acct_lines.cc_budget_code_combination_id%TYPE;
2565 l_cover_cc_det_pf_date igc_cc_det_pf.cc_det_pf_date%TYPE;
2566
2567
2568 /* Begin for fix for bug 1689924*/
2569 l_cc_header_id igc_cc_headers.cc_header_id%TYPE;
2570 l_cc_acct_line_id igc_cc_acct_lines.cc_acct_line_id%TYPE;
2571 /* End for fix for bug 1689924*/
2572 P_Error_Code VARCHAR2(32); /*EB Tax uptake - Bug No : 6472296*/
2573 l_taxable_flag VARCHAR2(2); /*Bug 6472296 EB Tax uptake - CC*/
2574
2575 BEGIN
2576 l_taxable_flag := nvl(p_cc_acct_lines_rec.cc_acct_taxable_flag,'N'); /*Bug 6472296 EB Tax uptake - CC*/
2577 IF (p_cc_headers_rec.cc_type = 'R') AND
2578 (p_process_type = 'F')
2579 THEN
2580 SELECT cc_num,cc_version_num, set_of_books_id, cc_acct_date
2581 INTO l_cover_cc_num, l_cover_cc_version_num, l_cover_set_of_books_id, l_cover_cc_acct_date
2582 FROM igc_cc_headers
2583 WHERE cc_header_id = p_cc_headers_rec.parent_header_id;
2584
2585 SELECT cc_acct_desc, cc_budget_code_combination_id
2586 INTO l_cover_cc_acct_desc, l_cover_budg_code_comb_id
2587 FROM igc_cc_acct_lines
2588 WHERE cc_acct_line_id = p_cc_acct_lines_rec.parent_acct_line_id;
2589 END IF;
2590
2591 l_cc_interface_rec.cbc_result_code := NULL;
2592 l_cc_interface_rec.status_code := NULL;
2593 l_cc_interface_rec.budget_version_id := NULL;
2594 l_cc_interface_rec.budget_amt := NULL;
2595 l_cc_interface_rec.commitment_encmbrnc_amt := NULL;
2596 l_cc_interface_rec.obligation_encmbrnc_amt := NULL;
2597 l_cc_interface_rec.funds_available_amt := NULL;
2598 l_cc_interface_rec.reference_1 := NULL;
2599 l_cc_interface_rec.reference_2 := NULL;
2600 l_cc_interface_rec.reference_3 := NULL;
2601 l_cc_interface_rec.reference_4 := NULL;
2602 l_cc_interface_rec.reference_5 := NULL;
2603 l_cc_interface_rec.reference_6 := NULL;
2604 /*Bug No : 6341012. SLA Uptake. Reference_7 should be 'EC'*/
2605 -- l_cc_interface_rec.reference_7 := NULL;
2606 l_cc_interface_rec.reference_7 := 'EC';
2607 l_cc_interface_rec.reference_8 := NULL;
2608 l_cc_interface_rec.reference_9 := NULL;
2609 l_cc_interface_rec.reference_10 := NULL;
2610 l_cc_interface_rec.cc_encmbrnc_date := NULL;
2611 l_cc_interface_rec.document_type := 'CC';
2612
2613 IF (p_cc_headers_rec.cc_type = 'R') AND
2614 (p_process_type = 'F')
2615 THEN
2616 l_cc_interface_rec.cc_header_id := p_cc_headers_rec.parent_header_id;
2617 l_cc_interface_rec.cc_version_num := l_cover_cc_version_num + 1;
2618 l_cc_interface_rec.set_of_books_id := l_cover_set_of_books_id;
2619 ELSE
2620 l_cc_interface_rec.cc_header_id := p_cc_headers_rec.cc_header_id;
2621 l_cc_interface_rec.cc_version_num := p_cc_headers_rec.cc_version_num + 1;
2622 l_cc_interface_rec.set_of_books_id := p_cc_headers_rec.set_of_books_id;
2623
2624 END IF;
2625
2626 IF (p_cc_headers_rec.cc_type = 'R') AND
2627 (p_process_type = 'F')
2628 THEN
2629 l_cc_interface_rec.code_combination_id := l_cover_budg_code_comb_id;
2630 ELSE
2631 l_cc_interface_rec.code_combination_id := p_cc_acct_lines_rec.cc_budget_code_combination_id;
2632 END IF;
2633
2634 l_cc_interface_rec.currency_code := p_currency_code;
2635 /*Bug No : 6341012. SLA Uptake. Je_Source_Name should be NULL*/
2636 -- l_cc_interface_rec.je_source_name := 'Contract Commitment';
2637 l_cc_interface_rec.actual_flag := 'E';
2638 l_cc_interface_rec.last_update_date := sysdate;
2639 l_cc_interface_rec.last_updated_by := -1;
2640 l_cc_interface_rec.last_update_login := -1;
2641 l_cc_interface_rec.creation_date := sysdate;
2642 l_cc_interface_rec.created_by := -1;
2643
2644 /*Bug No : 6341012. SLA Uptake. Event_id, Project_ida re newly added in IGC_CC_INTERFACE
2645 Encumbrance_type_id is not required*/
2646 l_cc_interface_rec.Event_Id := Null;
2647 l_cc_interface_rec.Project_line := 'N';
2648 l_cc_interface_rec.encumbrance_type_id := NULL;
2649
2650 IF (p_cc_headers_rec.cc_type = 'R') AND
2651 (p_process_type = 'F')
2652 THEN
2653 l_cc_interface_rec.transaction_description := LTRIM(RTRIM(l_cover_cc_num))
2654 || ' ' || rtrim(ltrim(l_cover_cc_acct_desc));
2655 ELSE
2656 l_cc_interface_rec.transaction_description := LTRIM(RTRIM(p_cc_headers_rec.cc_num))
2657 || ' ' || rtrim(ltrim(p_cc_acct_lines_rec.cc_acct_desc));
2658 END IF;
2659
2660 l_old_rate := p_cc_headers_rec.conversion_rate;
2661
2662 IF (p_type = 'A')
2663 THEN
2664 IF (p_cc_headers_rec.cc_type = 'R') AND
2665 (p_process_type = 'F')
2666 THEN
2667 l_cc_interface_rec.cc_acct_line_id := p_cc_acct_lines_rec.parent_acct_line_id;
2668 l_cc_interface_rec.cc_det_pf_line_id := NULL;
2669 l_cc_interface_rec.budget_dest_flag := 'C';
2670 l_cc_interface_rec.reference_1 := p_cc_headers_rec.parent_header_id;
2671 l_cc_interface_rec.reference_2 := p_cc_acct_lines_rec.parent_acct_line_id;
2672 l_cc_interface_rec.reference_3 := l_cover_cc_version_num + 1;
2673 -- Bug 6341012 made reference_4 to be assigned from p_cc_headers_rec.cc_num rather than from p_cc_pmt_fcst.cc_det_pf_line_id
2674 l_cc_interface_rec.reference_4 := p_cc_headers_rec.cc_num;
2675 ELSE
2676 l_cc_interface_rec.cc_acct_line_id := p_cc_acct_lines_rec.cc_acct_line_id;
2677 l_cc_interface_rec.cc_det_pf_line_id := NULL;
2678 l_cc_interface_rec.budget_dest_flag := 'C';
2679 l_cc_interface_rec.reference_1 := p_cc_headers_rec.cc_header_id;
2680 l_cc_interface_rec.reference_2 := p_cc_acct_lines_rec.cc_acct_line_id;
2681 l_cc_interface_rec.reference_3 := p_cc_headers_rec.cc_version_num + 1;
2682 -- Bug 6341012 made reference_4 to be assigned from p_cc_headers_rec.cc_num rather than from p_cc_pmt_fcst.cc_det_pf_line_id
2683 l_cc_interface_rec.reference_4 := p_cc_headers_rec.cc_num;
2684
2685
2686 END IF;
2687
2688 l_func_amt := 0;
2689 l_func_billed_amt := 0;
2690
2691 IF (p_process_type = 'Y')
2692 THEN
2693 -- Performance fixes. Replaced the query with the one
2694 -- below.
2695 /*
2696 SELECT cc_acct_func_amt , cc_acct_func_billed_amt
2697 INTO l_func_amt, l_func_billed_amt
2698 FROM igc_cc_acct_lines_v
2699 WHERE cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
2700 */
2701 SELECT Nvl(cc_acct_func_amt,0) , Nvl(IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id),0)
2702 INTO l_func_amt, l_func_billed_amt
2703 FROM igc_cc_acct_lines ccal
2704 WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id;
2705
2706 l_unbilled_amt := l_func_amt - l_func_billed_amt;
2707
2708 -- Bug 2409502, calculate the non recoverable tax
2709
2710
2711 /*EB Tax uptake - Bug No : 6472296*/
2712 /*
2713 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
2714 (p_api_version => 1.0,
2715 p_init_msg_list => FND_API.G_FALSE,
2716 p_commit => FND_API.G_FALSE,
2717 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2718 x_return_status => l_return_status,
2719 x_msg_count => l_msg_count,
2720 x_msg_data => l_msg_data,
2721 p_tax_id => p_cc_acct_lines_rec.tax_id,
2722 p_amount => l_unbilled_amt,
2723 p_tax_amount => l_unbilled_tax_amt);
2724 */
2725 IF (l_taxable_flag = 'Y') THEN
2726 IGC_ETAX_UTIL_PKG.Calculate_Tax
2727 (P_CC_Header_Rec =>p_cc_headers_rec,
2728 P_Calling_Mode =>null,
2729 P_Amount =>l_unbilled_amt,
2730 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
2731 P_Tax_Amount =>l_unbilled_tax_amt,
2732 P_Return_Status =>l_return_status,
2733 P_Error_Code =>P_Error_Code);
2734 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2735 THEN
2736 RAISE FND_API.G_EXC_ERROR;
2737 END IF;
2738 END IF;
2739 /*EB Tax uptake - Bug No : 6472296 END*/
2740 l_unbilled_amt := l_unbilled_amt + Nvl(l_unbilled_tax_amt,0);
2741
2742
2743 -- Bug 2409502, End
2744
2745 /* Reserve on first date of year */
2746 g_line_num := g_line_num + 1;
2747
2748 l_cc_interface_rec.cc_transaction_date := p_yr_end_dr_date;
2749 l_cc_interface_rec.batch_line_num := g_line_num;
2750 l_cc_interface_rec.cc_func_cr_amt := NULL;
2751 l_cc_interface_rec.cc_func_dr_amt := l_unbilled_amt;
2752
2753 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
2754
2755 IF l_insert_return_status = 'F' THEN
2756 raise E_RETURN_FAIL;
2757 END IF;
2758
2759 l_cc_interface_rec.cc_transaction_date := NULL;
2760 /* Liquidate on last date of fiscal year */
2761
2762 g_line_num := g_line_num + 1;
2763 l_cc_interface_rec.cc_transaction_date := p_yr_end_cr_date;
2764
2765 l_cc_interface_rec.batch_line_num := g_line_num;
2766 l_cc_interface_rec.cc_func_cr_amt := NULL; /* 6670549 l_unbilled_amt; */
2767 l_cc_interface_rec.cc_func_dr_amt := - l_unbilled_amt; /* 6670549 NULL; */
2768
2769 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
2770
2771 IF l_insert_return_status = 'F' THEN
2772 raise E_RETURN_FAIL;
2773 END IF;
2774 ELSIF (p_process_type = 'F')
2775 THEN
2776
2777 /* Queries to compute CBC entries based on realted SBC entries */
2778
2779
2780 /* Begin for fix for bug 1689924*/
2781 IF (p_cc_headers_rec.cc_type = 'R') AND
2782 (p_process_type = 'F')
2783 THEN
2784 l_cc_header_id := p_cc_headers_rec.parent_header_id;
2785 l_cc_acct_line_id := p_cc_acct_lines_rec.parent_acct_line_id;
2786 ELSE
2787 l_cc_header_id := p_cc_headers_rec.cc_header_id;
2788 l_cc_acct_line_id := p_cc_acct_lines_rec.cc_acct_line_id;
2789 END IF;
2790
2791 /* End for fix for bug 1689924*/
2792
2793 l_dr_tran_amount := 0;
2794
2795
2796 BEGIN
2797
2798 SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
2799 INTO l_dr_tran_amount
2800 FROM igc_cc_interface igcci
2801 WHERE
2802 igcci.cc_header_id = l_cc_header_id AND
2803 igcci.actual_flag = 'E' AND
2804 igcci.cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
2805 FROM igc_cc_det_pf ccdpf
2806 WHERE cc_acct_line_id =
2807 l_cc_acct_line_id);
2808 EXCEPTION
2809 WHEN NO_DATA_FOUND
2810 THEN
2811 l_dr_tran_amount := 0;
2812 END;
2813
2814 l_cr_tran_amount := 0;
2815
2816 BEGIN
2817
2818 SELECT SUM(NVL(igcci.cc_func_cr_amt,0))
2819 INTO l_cr_tran_amount
2820 FROM igc_cc_interface igcci
2821 WHERE
2822 igcci.cc_header_id = l_cc_header_id AND
2823 igcci.actual_flag = 'E' AND
2824 igcci.cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
2825 FROM igc_cc_det_pf ccdpf
2826 WHERE cc_acct_line_id =
2827 l_cc_acct_line_id);
2828 EXCEPTION
2829 WHEN NO_DATA_FOUND
2830 THEN
2831 l_cr_tran_amount := 0;
2832 END;
2833
2834 -- Bug 2409502, calculate the non recoverable tax
2835 /*EB Tax uptake - Bug No : 6472296*/
2836 /*
2837 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
2838 (p_api_version => 1.0,
2839 p_init_msg_list => FND_API.G_FALSE,
2840 p_commit => FND_API.G_FALSE,
2841 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2842 x_return_status => l_return_status,
2843 x_msg_count => l_msg_count,
2844 x_msg_data => l_msg_data,
2845 p_tax_id => p_cc_acct_lines_rec.tax_id,
2846 p_amount => l_dr_tran_amount,
2847 p_tax_amount => l_dr_tran_tax_amt);
2848 */
2849 IF (l_taxable_flag = 'Y') THEN
2850 IGC_ETAX_UTIL_PKG.Calculate_Tax
2851 (P_CC_Header_Rec =>p_cc_headers_rec,
2852 P_Calling_Mode =>null,
2853 P_Amount =>l_dr_tran_amount,
2854 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
2855 P_Tax_Amount =>l_dr_tran_tax_amt,
2856 P_Return_Status =>l_return_status,
2857 P_Error_Code =>P_Error_Code);
2858
2859 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2860 THEN
2861 RAISE FND_API.G_EXC_ERROR;
2862 END IF;
2863 END IF;
2864 l_dr_tran_amount := l_dr_tran_amount + Nvl(l_dr_tran_tax_amt,0);
2865
2866 /* igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
2867 (p_api_version => 1.0,
2868 p_init_msg_list => FND_API.G_FALSE,
2869 p_commit => FND_API.G_FALSE,
2870 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2871 x_return_status => l_return_status,
2872 x_msg_count => l_msg_count,
2873 x_msg_data => l_msg_data,
2874 p_tax_id => p_cc_acct_lines_rec.tax_id,
2875 p_amount => l_cr_tran_amount,
2876 p_tax_amount => l_cr_tran_tax_amt);
2877 */
2878 IF (l_taxable_flag = 'Y') THEN
2879 IGC_ETAX_UTIL_PKG.Calculate_Tax
2880 (P_CC_Header_Rec =>p_cc_headers_rec,
2881 P_Calling_Mode =>null,
2882 P_Amount =>l_cr_tran_amount,
2883 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
2884 P_Tax_Amount =>l_cr_tran_tax_amt,
2885 P_Return_Status =>l_return_status,
2886 P_Error_Code =>P_Error_Code);
2887 /*EB Tax uptake - Bug No : 6472296 END*/
2888 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2889 THEN
2890 RAISE FND_API.G_EXC_ERROR;
2891 END IF;
2892 END IF;
2893 l_cr_tran_amount := l_cr_tran_amount + Nvl(l_cr_tran_tax_amt,0);
2894 -- Bug 2409502, End
2895
2896 IF (p_cc_headers_rec.cc_type = 'R')
2897 THEN
2898
2899 IF (l_cover_cc_acct_date <= p_revalue_fix_date)
2900 THEN
2901 l_cc_interface_rec.cc_transaction_date := p_revalue_fix_date;
2902 END IF;
2903
2904 IF (l_cover_cc_acct_date > p_revalue_fix_date)
2905 THEN
2906 l_cc_interface_rec.cc_transaction_date := l_cover_cc_acct_date ;
2907 END IF;
2908 ELSE
2909 IF (p_cc_headers_rec.cc_acct_date <= p_revalue_fix_date)
2910 THEN
2911 l_cc_interface_rec.cc_transaction_date := p_revalue_fix_date;
2912 END IF;
2913
2914 IF (p_cc_headers_rec.cc_acct_date > p_revalue_fix_date)
2915 THEN
2916 l_cc_interface_rec.cc_transaction_date := p_cc_headers_rec.cc_acct_date ;
2917 END IF;
2918 END IF;
2919
2920 IF (abs(l_dr_tran_amount) > 0 )
2921 THEN
2922
2923 g_line_num := g_line_num + 1;
2924
2925 l_cc_interface_rec.batch_line_num := g_line_num;
2926
2927 l_cc_interface_rec.cc_func_cr_amt := NULL;
2928 l_cc_interface_rec.cc_func_dr_amt := l_dr_tran_amount; /* 6670549 ABS(l_dr_tran_amount); */
2929 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
2930
2931 IF l_insert_return_status = 'F' THEN
2932 raise E_RETURN_FAIL;
2933 END IF;
2934
2935 END IF;
2936
2937 IF (abs(l_cr_tran_amount) > 0 )
2938 THEN
2939 g_line_num := g_line_num + 1;
2940
2941 l_cc_interface_rec.batch_line_num := g_line_num;
2942
2943 l_cc_interface_rec.cc_func_cr_amt := ABS(l_cr_tran_amount);
2944 l_cc_interface_rec.cc_func_dr_amt := NULL;
2945 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
2946
2947 IF l_insert_return_status = 'F' THEN
2948 raise E_RETURN_FAIL;
2949 END IF;
2950 END IF;
2951
2952 ELSIF (p_process_type = 'R')
2953 THEN
2954 l_tran_amount := 0;
2955 l_func_billed_amt := 0;
2956 /* Queries to compute CBC entries based on realted SBC entries */
2957 IF (l_old_rate < p_rate)
2958 THEN
2959 SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
2960 INTO l_tran_amount
2961 FROM igc_cc_interface igcci
2962 WHERE
2963 igcci.cc_header_id = p_cc_headers_rec.cc_header_id AND
2964 igcci.actual_flag = 'E' AND
2965 cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
2966 FROM igc_cc_det_pf ccdpf
2967 WHERE cc_acct_line_id =
2968 p_cc_acct_lines_rec.cc_acct_line_id);
2969 END IF;
2970
2971 IF (l_old_rate > p_rate)
2972 THEN
2973 /* 6670549 The Select statement changed as we are entering debit amount as negative instead of positive credit amount
2974 SELECT SUM(NVL(igcci.cc_func_cr_amt,0)) */
2975 SELECT SUM(NVL(igcci.cc_func_dr_amt,0))
2976 INTO l_tran_amount
2977 FROM igc_cc_interface igcci
2978 WHERE
2979 igcci.cc_header_id = p_cc_headers_rec.cc_header_id AND
2980 igcci.actual_flag = 'E' AND
2981 cc_det_pf_line_id IN (SELECT ccdpf.cc_det_pf_line_id
2982 FROM igc_cc_det_pf ccdpf
2983 WHERE cc_acct_line_id =
2984 p_cc_acct_lines_rec.cc_acct_line_id);
2985 END IF;
2986
2987
2988 g_line_num := g_line_num + 1;
2989
2990
2991 IF (p_cc_headers_rec.cc_acct_date <= p_rate_date)
2992 THEN
2993 l_cc_interface_rec.cc_transaction_date := p_rate_date;
2994 END IF;
2995
2996 IF (p_cc_headers_rec.cc_acct_date > p_rate_date)
2997 THEN
2998 l_cc_interface_rec.cc_transaction_date := p_cc_headers_rec.cc_acct_date ;
2999 END IF;
3000
3001
3002 l_cc_interface_rec.batch_line_num := g_line_num;
3003
3004 -- bug 2043221 ssmales - statement below added
3005
3006 l_withheld_tran_amt := ((NVL(p_cc_acct_lines_rec.cc_func_withheld_amt,0)
3007 * p_rate)
3008 / l_old_rate )
3009 - NVL(p_cc_acct_lines_rec.cc_func_withheld_amt,0);
3010
3011 l_tran_amount := Nvl(l_tran_amount,0) + Nvl(l_withheld_tran_amt,0);
3012
3013 -- Bug 2409502, Calculate non recoverable tax
3014 /*EB Tax uptake - Bug No : 6472296*/
3015 /*
3016 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
3017 (p_api_version => 1.0,
3018 p_init_msg_list => FND_API.G_FALSE,
3019 p_commit => FND_API.G_FALSE,
3020 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3021 x_return_status => l_return_status,
3022 x_msg_count => l_msg_count,
3023 x_msg_data => l_msg_data,
3024 p_tax_id => p_cc_acct_lines_rec.tax_id,
3025 p_amount => l_tran_amount,
3026 p_tax_amount => l_tran_tax_amt);
3027 */
3028 IF (l_taxable_flag = 'Y') THEN
3029 IGC_ETAX_UTIL_PKG.Calculate_Tax
3030 (P_CC_Header_Rec =>p_cc_headers_rec,
3031 P_Calling_Mode =>null,
3032 P_Amount =>l_tran_amount,
3033 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
3034 P_Tax_Amount =>l_tran_tax_amt,
3035 P_Return_Status =>l_return_status,
3036 P_Error_Code =>P_Error_Code);
3037 /*EB Tax uptake - Bug No : 6472296 END*/
3038 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
3039 THEN
3040 RAISE FND_API.G_EXC_ERROR;
3041 END IF;
3042 END IF;
3043 l_tran_amount := l_tran_amount + Nvl(l_tran_tax_amt,0);
3044
3045 -- Bug 2409502, End
3046
3047 IF (l_old_rate < p_rate)
3048 THEN
3049 l_cc_interface_rec.cc_func_cr_amt := NULL;
3050 l_cc_interface_rec.cc_func_dr_amt := ABS(l_tran_amount);
3051 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3052
3053 IF l_insert_return_status = 'F' THEN
3054 raise E_RETURN_FAIL;
3055 END IF;
3056 END IF;
3057
3058 IF (l_old_rate > p_rate)
3059 THEN
3060 l_cc_interface_rec.cc_func_cr_amt := NULL; /* 6670549 ABS(l_tran_amount);*/
3061 l_cc_interface_rec.cc_func_dr_amt := l_tran_amount; /* 6670549 NULL;*/
3062 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3063
3064 IF l_insert_return_status = 'F' THEN
3065 raise E_RETURN_FAIL;
3066 END IF;
3067 END IF;
3068
3069
3070 END IF;
3071 END IF;
3072
3073 /* Payment Forecast Row */
3074 IF (p_type = 'P')
3075 THEN
3076 IF (p_cc_headers_rec.cc_type = 'R') AND
3077 (p_process_type = 'F')
3078 THEN
3079 /*Bug No : 6341012. SLA Uptake. CC_Account_line_id should not be Null */
3080 l_cc_interface_rec.cc_acct_line_id := p_cc_acct_lines_rec.cc_acct_line_id;
3081 l_cc_interface_rec.cc_det_pf_line_id := p_cc_pmt_fcst_rec.parent_det_pf_line_id;
3082 l_cc_interface_rec.budget_dest_flag := 'S';
3083 l_cc_interface_rec.reference_1 := p_cc_headers_rec.parent_header_id;
3084 l_cc_interface_rec.reference_2 := p_cc_acct_lines_rec.parent_acct_line_id;
3085 l_cc_interface_rec.reference_3 := l_cover_cc_version_num + 1;
3086 -- Bug 6341012 made reference_4 to be assigned from p_cc_headers_rec.cc_num rather than from p_cc_pmt_fcst.cc_det_pf_line_id
3087 l_cc_interface_rec.reference_4 := p_cc_headers_rec.cc_num;
3088 l_cover_cc_det_pf_date := NULL;
3089
3090 SELECT cc_det_pf_date
3091 INTO l_cover_cc_det_pf_date
3092 FROM igc_cc_det_pf
3093 WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.parent_det_pf_line_id;
3094 ELSE
3095 /*Bug No : 6341012. SLA Uptake. CC_Account_line_id should not be Null */
3096 l_cc_interface_rec.cc_acct_line_id := p_cc_acct_lines_rec.cc_acct_line_id;
3097 l_cc_interface_rec.cc_det_pf_line_id := p_cc_pmt_fcst_rec.cc_det_pf_line_id;
3098 l_cc_interface_rec.budget_dest_flag := 'S';
3099 l_cc_interface_rec.reference_1 := p_cc_headers_rec.cc_header_id;
3100 l_cc_interface_rec.reference_2 := p_cc_acct_lines_rec.cc_acct_line_id;
3101 l_cc_interface_rec.reference_3 := p_cc_headers_rec.cc_version_num + 1;
3102 -- Bug 6341012 made reference_4 to be assigned from p_cc_headers_rec.cc_num rather than from p_cc_pmt_fcst.cc_det_pf_line_id
3103 l_cc_interface_rec.reference_4 := p_cc_headers_rec.cc_num;
3104
3105 END IF;
3106
3107 /* Year-end processing */
3108
3109 l_func_amt := 0;
3110 l_func_billed_amt := 0;
3111
3112 IF (p_process_type = 'Y')
3113 THEN
3114
3115 SELECT cc_det_pf_func_amt,cc_det_pf_func_billed_amt
3116 INTO l_func_amt,l_func_billed_amt
3117 FROM igc_cc_det_pf_v
3118 WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
3119
3120 l_unbilled_amt := l_func_amt - l_func_billed_amt;
3121
3122
3123
3124 -- Bug 2409502, calculate the non recoverable tax
3125 /*EB Tax uptake - Bug No : 6472296*/
3126 /*
3127 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
3128 (p_api_version => 1.0,
3129 p_init_msg_list => FND_API.G_FALSE,
3130 p_commit => FND_API.G_FALSE,
3131 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3132 x_return_status => l_return_status,
3133 x_msg_count => l_msg_count,
3134 x_msg_data => l_msg_data,
3135 p_tax_id => p_cc_acct_lines_rec.tax_id,
3136 p_amount => l_unbilled_amt,
3137 p_tax_amount => l_unbilled_tax_amt);
3138 */
3139 IF (l_taxable_flag = 'Y') THEN
3140 IGC_ETAX_UTIL_PKG.Calculate_Tax
3141 (P_CC_Header_Rec =>p_cc_headers_rec,
3142 P_Calling_Mode =>null,
3143 P_Amount =>l_unbilled_amt,
3144 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
3145 P_Tax_Amount =>l_unbilled_tax_amt,
3146 P_Return_Status =>l_return_status,
3147 P_Error_Code =>P_Error_Code);
3148
3149 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
3150 THEN
3151 RAISE FND_API.G_EXC_ERROR;
3152 END IF;
3153 END IF;
3154 /*EB Tax uptake - Bug No : 6472296 END*/
3155 l_unbilled_amt := l_unbilled_amt + Nvl(l_unbilled_tax_amt,0);
3156
3157
3158 -- Bug 2409502, End
3159 /* Reserve on first date of year */
3160 g_line_num := g_line_num + 1;
3161
3162 l_cc_interface_rec.cc_transaction_date := p_yr_end_dr_date;
3163 l_cc_interface_rec.batch_line_num := g_line_num;
3164 l_cc_interface_rec.cc_func_cr_amt := NULL;
3165 l_cc_interface_rec.cc_func_dr_amt := l_unbilled_amt;
3166
3167 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3168
3169 IF l_insert_return_status = 'F' THEN
3170 raise E_RETURN_FAIL;
3171 END IF;
3172
3173 /* Liquidate on last date of fiscal year */
3174
3175 g_line_num := g_line_num + 1;
3176
3177 l_cc_interface_rec.cc_transaction_date := p_yr_end_cr_date;
3178 l_cc_interface_rec.batch_line_num := g_line_num;
3179 l_cc_interface_rec.cc_func_cr_amt := NULL; /* 6670549 l_unbilled_amt; */
3180 l_cc_interface_rec.cc_func_dr_amt := -l_unbilled_amt; /* 6670549 NULL; */
3181
3182 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3183
3184 IF l_insert_return_status = 'F' THEN
3185 raise E_RETURN_FAIL;
3186 END IF;
3187 ELSIF (p_process_type = 'F')
3188 THEN
3189 /* Re_valuation Fix*/
3190 l_tran_amount := 0;
3191 l_func_billed_amt := 0;
3192 l_func_amt := 0;
3193 l_ent_amt := 0;
3194 l_billed_amt := 0;
3195
3196 BEGIN
3197 SELECT cc_det_pf_entered_amt,
3198 cc_det_pf_func_amt,
3199 cc_det_pf_billed_amt,
3200 cc_det_pf_func_billed_amt
3201 INTO l_ent_amt,
3202 l_func_amt,
3203 l_billed_amt,
3204 l_func_billed_amt
3205 FROM igc_cc_det_pf_v
3206 WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
3207 EXCEPTION
3208 WHEN NO_DATA_FOUND
3209 THEN
3210 l_func_billed_amt := 0;
3211 l_func_amt := 0;
3212 l_ent_amt := 0;
3213 l_billed_amt := 0;
3214 END;
3215
3216 l_tran_amount :=
3217 ( (l_ent_amt - l_billed_amt) * p_cc_headers_rec.conversion_rate ) -
3218 (l_func_amt - l_func_billed_amt);
3219
3220
3221 -- Bug 2409502, Calculate non recoverable tax
3222 /*EB Tax uptake - Bug No : 6472296*/
3223 /*
3224 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
3225 (p_api_version => 1.0,
3226 p_init_msg_list => FND_API.G_FALSE,
3227 p_commit => FND_API.G_FALSE,
3228 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3229 x_return_status => l_return_status,
3230 x_msg_count => l_msg_count,
3231 x_msg_data => l_msg_data,
3232 p_tax_id => p_cc_acct_lines_rec.tax_id,
3233 p_amount => l_tran_amount,
3234 p_tax_amount => l_tran_tax_amt);
3235 */
3236 IF (l_taxable_flag = 'Y') THEN
3237 IGC_ETAX_UTIL_PKG.Calculate_Tax
3238 (P_CC_Header_Rec =>p_cc_headers_rec,
3239 P_Calling_Mode =>null,
3240 P_Amount =>l_tran_amount,
3241 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
3242 P_Tax_Amount =>l_tran_tax_amt,
3243 P_Return_Status =>l_return_status,
3244 P_Error_Code =>P_Error_Code);
3245 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
3246 THEN
3247 RAISE FND_API.G_EXC_ERROR;
3248 END IF;
3249 END IF;
3250 /*EB Tax uptake - Bug No : 6472296 END*/
3251 l_tran_amount := l_tran_amount + Nvl(l_tran_tax_amt,0);
3252 -- Bug 2409502, End
3253 IF (l_tran_amount <> 0)
3254 THEN
3255
3256 g_line_num := g_line_num + 1;
3257
3258 IF (p_cc_headers_rec.cc_type = 'R')
3259 THEN
3260 IF (l_cover_cc_det_pf_date <= p_revalue_fix_date)
3261 THEN
3262 l_cc_interface_rec.cc_transaction_date := p_revalue_fix_date;
3263 ELSE
3264 l_cc_interface_rec.cc_transaction_date := l_cover_cc_det_pf_date;
3265 END IF;
3266 END IF;
3267
3268 IF (p_cc_headers_rec.cc_type <> 'R')
3269 THEN
3270 IF (p_cc_pmt_fcst_rec.cc_det_pf_date <= p_revalue_fix_date)
3271 THEN
3272 l_cc_interface_rec.cc_transaction_date := p_revalue_fix_date;
3273 ELSE
3274 l_cc_interface_rec.cc_transaction_date := p_cc_pmt_fcst_rec.cc_det_pf_date;
3275 END IF;
3276 END IF;
3277
3278 l_cc_interface_rec.batch_line_num := g_line_num;
3279
3280 IF (l_tran_amount < 0)
3281 THEN
3282 l_cc_interface_rec.cc_func_cr_amt := NULL; /* 6670549 ABS(l_tran_amount); */
3283 l_cc_interface_rec.cc_func_dr_amt := l_tran_amount; /* 6670549 NULL; */
3284 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3285
3286 IF l_insert_return_status = 'F' THEN
3287 raise E_RETURN_FAIL;
3288 END IF;
3289 END IF;
3290
3291 IF (l_tran_amount > 0)
3292 THEN
3293 l_cc_interface_rec.cc_func_cr_amt := NULL;
3294 l_cc_interface_rec.cc_func_dr_amt := ABS(l_tran_amount);
3295 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3296
3297 IF l_insert_return_status = 'F' THEN
3298 raise E_RETURN_FAIL;
3299 END IF;
3300 END IF;
3301 END IF;
3302 ELSIF (p_process_type = 'R')
3303 THEN
3304 /* Re_valuation */
3305 l_tran_amount := 0;
3306 l_func_billed_amt := 0;
3307 l_func_amt := p_cc_pmt_fcst_rec.cc_det_pf_func_amt;
3308 /* Fixed Bug 1497003 By including Confirmed state */
3309 IF ( (p_cc_headers_rec.cc_state = 'CT') OR
3310 (p_cc_headers_rec.cc_state = 'CM'))
3311 THEN
3312 l_func_billed_amt := 0;
3313
3314 BEGIN
3315 SELECT cc_det_pf_func_billed_amt
3316 INTO l_func_billed_amt
3317 FROM igc_cc_det_pf_v
3318 WHERE cc_det_pf_line_id = p_cc_pmt_fcst_rec.cc_det_pf_line_id;
3319 EXCEPTION
3320 WHEN NO_DATA_FOUND
3321 THEN
3322 l_func_billed_amt := 0;
3323 END;
3324 ELSE
3325 l_func_billed_amt := 0;
3326 END IF;
3327
3328 IF ((l_func_amt > l_func_billed_amt) AND (p_rate > 0))
3329 THEN
3330 g_line_num := g_line_num + 1;
3331
3332 IF (p_cc_pmt_fcst_rec.cc_det_pf_date <= p_rate_date)
3333 THEN
3334 l_cc_interface_rec.cc_transaction_date := p_rate_date;
3335 ELSE
3336 l_cc_interface_rec.cc_transaction_date := p_cc_pmt_fcst_rec.cc_det_pf_date;
3337 END IF;
3338
3339 l_cc_interface_rec.batch_line_num := g_line_num;
3340
3341 l_tran_amount :=
3342 ((p_cc_pmt_fcst_rec.cc_det_pf_func_amt - l_func_billed_amt) / l_old_rate) *
3343 (p_rate - l_old_rate);
3344
3345
3346 -- Bug 2409502, Calculate non recoverable tax
3347 /*EB Tax uptake - Bug No : 6472296*/
3348 /*
3349 igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
3350 (p_api_version => 1.0,
3351 p_init_msg_list => FND_API.G_FALSE,
3352 p_commit => FND_API.G_FALSE,
3353 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3354 x_return_status => l_return_status,
3355 x_msg_count => l_msg_count,
3356 x_msg_data => l_msg_data,
3357 p_tax_id => p_cc_acct_lines_rec.tax_id,
3358 p_amount => l_tran_amount,
3359 p_tax_amount => l_tran_tax_amt);
3360 */
3361 IF (l_taxable_flag = 'Y') THEN
3362 IGC_ETAX_UTIL_PKG.Calculate_Tax
3363 (P_CC_Header_Rec =>p_cc_headers_rec,
3364 P_Calling_Mode =>null,
3365 P_Amount =>l_tran_amount,
3366 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
3367 P_Tax_Amount =>l_tran_tax_amt,
3368 P_Return_Status =>l_return_status,
3369 P_Error_Code =>P_Error_Code);
3370
3371 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
3372 THEN
3373 RAISE FND_API.G_EXC_ERROR;
3374 END IF;
3375 END IF;
3376 /*EB Tax uptake - Bug No : 6472296 END*/
3377 l_tran_amount := l_tran_amount + Nvl(l_tran_tax_amt,0);
3378
3379 -- Bug 2409502, End
3380 IF (l_tran_amount < 0)
3381 THEN
3382 l_cc_interface_rec.cc_func_cr_amt := NULL; /* 6670549 ABS(l_tran_amount); */
3383 l_cc_interface_rec.cc_func_dr_amt := l_tran_amount; /* 6670549 NULL; */
3384 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3385
3386 IF l_insert_return_status = 'F' THEN
3387 raise E_RETURN_FAIL;
3388 END IF;
3389 END IF;
3390
3391 IF (l_tran_amount > 0)
3392 THEN
3393 l_cc_interface_rec.cc_func_cr_amt := NULL;
3394 l_cc_interface_rec.cc_func_dr_amt := ABS(l_tran_amount);
3395 Insert_Interface_Row(l_cc_interface_rec, l_insert_return_status);
3396
3397 IF l_insert_return_status = 'F' THEN
3398 raise E_RETURN_FAIL;
3399 END IF;
3400 END IF;
3401 END IF;
3402 END IF;
3403
3404 END IF; /* p_type = 'P'*/
3405
3406 EXCEPTION
3407 When E_RETURN_FAIL then
3408 l_insert_status := 'F';
3409
3410
3411 END Process_Interface_Row;
3412
3413 FUNCTION Encumber_CC
3414 (
3415 p_process_type IN VARCHAR2,
3416 p_cc_header_id IN NUMBER,
3417 p_sbc_on IN BOOLEAN,
3418 p_cbc_on IN BOOLEAN,
3419 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
3420 -- p_cc_prov_enc_type_id IN NUMBER,
3421 -- p_cc_conf_enc_type_id IN NUMBER,
3422 -- p_req_encumbrance_type_id IN NUMBER,
3423 -- p_purch_encumbrance_type_id IN NUMBER,
3424 p_currency_code IN VARCHAR2,
3425 p_yr_start_date IN DATE,
3426 p_yr_end_date IN DATE,
3427 p_yr_end_cr_date IN DATE,
3428 p_yr_end_dr_date IN DATE,
3429 p_rate_date IN DATE,
3430 p_rate IN NUMBER,
3431 p_revalue_fix_date IN DATE
3432 ) RETURN VARCHAR2
3433 IS
3434 l_interface_row_count NUMBER;
3435 l_insert_status VARCHAR2(1);
3436 l_cc_headers_rec igc_cc_headers%ROWTYPE;
3437 l_cc_acct_lines_rec igc_cc_acct_lines%ROWTYPE;
3438 l_cc_pmt_fcst_rec igc_cc_det_pf%ROWTYPE;
3439
3440 l_cover_set_of_books_id igc_cc_headers.set_of_books_id%TYPE;
3441
3442 l_process_account_row BOOLEAN := FALSE;
3443
3444 l_debug VARCHAR2(1);
3445
3446 l_cbc_on BOOLEAN;
3447 l_batch_result_code VARCHAR2(3);
3448 l_bc_return_status VARCHAR2(2);
3449 l_bc_success BOOLEAN;
3450
3451
3452 e_process_row EXCEPTION;
3453 e_bc_execution EXCEPTION;
3454 e_cc_not_found EXCEPTION;
3455 e_delete EXCEPTION;
3456
3457 /* Contract Commitment detail payment forecast */
3458 CURSOR c_payment_forecast(t_cc_acct_line_id NUMBER) IS
3459 SELECT *
3460 FROM igc_cc_det_pf
3461 WHERE cc_acct_line_id = t_cc_acct_line_id;
3462 /* Current year payment forecast lines only */
3463
3464 /* Contract Commitment account lines */
3465
3466 CURSOR c_account_lines(t_cc_header_id NUMBER) IS
3467 SELECT *
3468 FROM igc_cc_acct_lines ccac
3469 WHERE ccac.cc_header_id = t_cc_header_id;
3470
3471 BEGIN
3472 SAVEPOINT Execute_Budgetary_Ctrl1;
3473
3474 g_line_num := 0;
3475
3476
3477 BEGIN
3478
3479 SELECT *
3480 INTO l_cc_headers_rec
3481 FROM igc_cc_headers
3482 WHERE cc_header_id = p_cc_header_id;
3483
3484 EXCEPTION
3485
3486 WHEN OTHERS
3487 THEN
3488 RAISE E_CC_NOT_FOUND;
3489
3490 END;
3491
3492
3493 IF (l_cc_headers_rec.cc_type = 'R')
3494 THEN
3495 SELECT set_of_books_id
3496 INTO l_cover_set_of_books_id
3497 FROM igc_cc_headers
3498 WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
3499 END IF;
3500
3501 /* Delete existing interface rows */
3502
3503 IF (l_cc_headers_rec.cc_type = 'R')
3504 THEN
3505 BEGIN
3506
3507 DELETE igc_cc_interface
3508 WHERE cc_header_id = l_cc_headers_rec.parent_header_id AND
3509 actual_flag = 'E';
3510 EXCEPTION
3511 WHEN OTHERS
3512 THEN
3513 NULL;
3514 END;
3515
3516 COMMIT;
3517 END IF;
3518
3519 -- Commented out the following IF caluse as we want all the records
3520 -- to be cleared out of of the IGC_CC_INTERFACE table.
3521 -- Bug 1916208, Bidisha S 2 Aug 2001
3522 -- IF (l_cc_headers_rec.cc_type <> 'R')
3523 -- THEN
3524 BEGIN
3525
3526 DELETE igc_cc_interface
3527 WHERE cc_header_id = p_cc_header_id AND
3528 actual_flag = 'E';
3529 EXCEPTION
3530 WHEN OTHERS
3531 THEN
3532 NULL;
3533 END;
3534
3535 IF l_cc_headers_rec.cc_type = 'C'
3536 THEN
3537 -- Delete all the child releases.
3538 BEGIN
3539
3540 DELETE igc_cc_interface
3541 WHERE cc_header_id IN (SELECT cc_header_id
3542 FROM igc_cc_headers
3543 WHERE parent_header_id = p_cc_header_id )
3544 AND actual_flag = 'E';
3545 EXCEPTION
3546 WHEN OTHERS
3547 THEN
3548 NULL;
3549 END;
3550 END IF;
3551
3552 COMMIT;
3553 -- END IF;
3554
3555 SAVEPOINT Execute_Budgetary_Ctrl2;
3556
3557 /* Process Interface Rows */
3558
3559
3560 OPEN c_account_lines(p_cc_header_id);
3561
3562 LOOP
3563 FETCH c_account_lines INTO l_cc_acct_lines_rec;
3564
3565 EXIT WHEN c_account_lines%NOTFOUND;
3566
3567 l_process_account_row := FALSE;
3568
3569 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
3570
3571 LOOP
3572 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
3573
3574 EXIT WHEN c_payment_forecast%NOTFOUND;
3575
3576 /* Year-end processing */
3577
3578 IF (p_process_type = 'Y')
3579 THEN
3580 /* check whether payment forecast belongs to yr-end processing year */
3581 IF ( (l_cc_pmt_fcst_rec.cc_det_pf_date <= p_yr_end_date) AND
3582 (l_cc_pmt_fcst_rec.cc_det_pf_date >= p_yr_start_date)
3583 )
3584 THEN
3585
3586 BEGIN
3587 Process_Interface_Row(
3588 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
3589 -- p_cc_prov_enc_type_id,
3590 -- p_cc_conf_enc_type_id,
3591 -- p_req_encumbrance_type_id,
3592 -- p_purch_encumbrance_type_id,
3593 p_currency_code,
3594 l_cc_headers_rec,
3595 l_cc_acct_lines_rec,
3596 l_cc_pmt_fcst_rec,
3597 'F',
3598 'P',
3599 p_process_type,
3600 p_yr_end_cr_date,
3601 p_yr_end_dr_date,
3602 p_rate_date,
3603 p_rate,
3604 p_revalue_fix_date,
3605 l_insert_status
3606 );
3607 END;
3608
3609 END IF;
3610 END IF;
3611
3612 /* Re-valuation */
3613
3614 IF (p_process_type = 'R')
3615 THEN
3616 BEGIN
3617 Process_Interface_Row(
3618 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
3619 -- p_cc_prov_enc_type_id,
3620 -- p_cc_conf_enc_type_id,
3621 -- p_req_encumbrance_type_id,
3622 -- p_purch_encumbrance_type_id,
3623 p_currency_code,
3624 l_cc_headers_rec,
3625 l_cc_acct_lines_rec,
3626 l_cc_pmt_fcst_rec,
3627 'F',
3628 'P',
3629 p_process_type,
3630 p_yr_end_cr_date,
3631 p_yr_end_dr_date,
3632 p_rate_date,
3633 p_rate,
3634 p_revalue_fix_date,
3635 l_insert_status
3636 );
3637 END;
3638
3639 IF l_insert_status = 'F' THEN
3640 RETURN ('F');
3641 END IF;
3642 END IF;
3643
3644 /* Re-valuation fix */
3645
3646 IF (p_process_type = 'F')
3647 THEN
3648 BEGIN
3649 Process_Interface_Row(
3650 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
3651 -- p_cc_prov_enc_type_id,
3652 -- p_cc_conf_enc_type_id,
3653 -- p_req_encumbrance_type_id,
3654 -- p_purch_encumbrance_type_id,
3655 p_currency_code,
3656 l_cc_headers_rec,
3657 l_cc_acct_lines_rec,
3658 l_cc_pmt_fcst_rec,
3659 'F',
3660 'P',
3661 p_process_type,
3662 p_yr_end_cr_date,
3663 p_yr_end_dr_date,
3664 p_rate_date,
3665 p_rate,
3666 p_revalue_fix_date,
3667 l_insert_status
3668 );
3669 END;
3670 END IF;
3671
3672 END LOOP;
3673
3674 CLOSE c_payment_forecast;
3675
3676 /* Changed cc_type to cc_state in condition below to fix bug 1510337 */
3677
3678 IF (p_cbc_on = TRUE) AND
3679 ( (p_process_type = 'R') OR
3680 (p_process_type = 'F') OR
3681 ( (p_process_type = 'Y') AND
3682 ( (l_cc_headers_rec.cc_state = 'CL') OR
3683 (l_cc_headers_rec.cc_state = 'PR') )
3684 )
3685 )
3686 THEN
3687 BEGIN
3688
3689
3690 Process_Interface_Row(
3691 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required*/
3692 -- p_cc_prov_enc_type_id,
3693 -- p_cc_conf_enc_type_id,
3694 -- p_req_encumbrance_type_id,
3695 -- p_purch_encumbrance_type_id,
3696 p_currency_code,
3697 l_cc_headers_rec,
3698 l_cc_acct_lines_rec,
3699 l_cc_pmt_fcst_rec,
3700 'F',
3701 'A',
3702 p_process_type,
3703 p_yr_end_cr_date,
3704 p_yr_end_dr_date,
3705 p_rate_date,
3706 p_rate,
3707 p_revalue_fix_date,
3708 l_insert_status
3709 );
3710 END;
3711
3712 IF l_insert_status = 'F' and p_process_type = 'R' THEN
3713 RETURN('F');
3714 END IF;
3715
3716 END IF;
3717
3718 END LOOP;
3719
3720 CLOSE c_account_lines;
3721
3722 COMMIT;
3723
3724 l_interface_row_count := 0;
3725
3726 IF (l_cc_headers_rec.cc_type = 'R')
3727 THEN
3728 SELECT count(*)
3729 INTO l_interface_row_count
3730 FROM igc_cc_interface
3731 WHERE cc_header_id = l_cc_headers_rec.parent_header_id;
3732 END IF;
3733
3734 IF (l_cc_headers_rec.cc_type <> 'R')
3735 THEN
3736 SELECT count(*)
3737 INTO l_interface_row_count
3738 FROM igc_cc_interface
3739 WHERE cc_header_id = p_cc_header_id;
3740 END IF;
3741
3742 SAVEPOINT Execute_Budgetary_Ctrl4;
3743
3744 /* Execute budgetary control */
3745
3746 BEGIN
3747
3748 COMMIT; -- bug number 4130976
3749
3750 IF (l_interface_row_count <> 0)
3751 THEN
3752 l_batch_result_code := NULL;
3753
3754 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
3755
3756 IF (l_debug_mode = 'Y')
3757 THEN
3758 l_debug := FND_API.G_TRUE;
3759 ELSE
3760 l_debug := FND_API.G_FALSE;
3761 END IF;
3762
3763 BEGIN
3764
3765 IF (l_cc_headers_rec.cc_type = 'R')
3766 THEN
3767 -- The call to IGCFCK updated to IGCPAFCK for bug 1844214.
3768 -- Bidisha S , 21 June 2001
3769
3770 l_bc_success := IGC_CBC_PA_BC_PKG.IGCPAFCK(
3771 p_sobid => l_cover_set_of_books_id,
3772 p_header_id => l_cc_headers_rec.parent_header_id,
3773 p_mode => 'F',
3774 p_actual_flag => 'E',
3775 p_ret_status => l_bc_return_status,
3776 p_batch_result_code => l_batch_result_code,
3777 p_doc_type => 'CC',
3778 p_debug => l_debug,
3779 p_conc_proc => FND_API.G_FALSE);
3780 END IF;
3781
3782 IF (l_cc_headers_rec.cc_type <> 'R')
3783 THEN
3784 -- The call to IGCFCK updated to IGCPAFCK for bug 1844214.
3785 -- Bidisha S , 21 June 2001
3786
3787 l_bc_success := IGC_CBC_PA_BC_PKG.IGCPAFCK(
3788 p_sobid => l_cc_headers_rec.set_of_books_id,
3789 p_header_id => l_cc_headers_rec.cc_header_id,
3790 p_mode => 'F',
3791 p_actual_flag => 'E',
3792 p_ret_status => l_bc_return_status,
3793 p_batch_result_code => l_batch_result_code,
3794 p_doc_type => 'CC',
3795 p_debug => l_debug,
3796 p_conc_proc => FND_API.G_FALSE);
3797 END IF;
3798
3799 EXCEPTION
3800 WHEN OTHERS
3801 THEN
3802 NULL;
3803
3804 END;
3805 END IF;
3806 END;
3807
3808 IF (l_interface_row_count <> 0)
3809 THEN
3810
3811 IF (l_bc_success = TRUE)
3812 THEN
3813 IF ( (l_bc_return_status <> 'NA') AND
3814 (l_bc_return_status <> 'AN') AND
3815 (l_bc_return_status <> 'AA') AND
3816 (l_bc_return_status <> 'AS') AND
3817 (l_bc_return_status <> 'SA') AND
3818 (l_bc_return_status <> 'SS') AND
3819 (l_bc_return_status <> 'SN') AND
3820 (l_bc_return_status <> 'NS') )
3821 THEN
3822 RETURN('F');
3823 ELSE
3824 RETURN('P');
3825 END IF;
3826 ELSE
3827 RETURN('F');
3828 END IF;
3829 ELSE
3830 RETURN('P');
3831 END IF;
3832 END Encumber_CC;
3833
3834 FUNCTION get_budg_ctrl_params(
3835 p_sob_id IN NUMBER,
3836 p_org_id IN NUMBER,
3837 p_currency_code OUT NOCOPY VARCHAR2,
3838 p_sbc_on OUT NOCOPY BOOLEAN,
3839 p_cbc_on OUT NOCOPY BOOLEAN,
3840 p_prov_enc_on OUT NOCOPY BOOLEAN,
3841 p_conf_enc_on OUT NOCOPY BOOLEAN,
3842 /*Bug No : 6341012. SLA Uptake. Encumbrance Types are not required*/
3843 -- p_req_encumbrance_type_id OUT NOCOPY NUMBER,
3844 -- p_purch_encumbrance_type_id OUT NOCOPY NUMBER,
3845 -- p_cc_prov_enc_type_id OUT NOCOPY NUMBER,
3846 -- p_cc_conf_enc_type_id OUT NOCOPY NUMBER,
3847 p_msg_data OUT NOCOPY VARCHAR2,
3848 p_msg_count OUT NOCOPY NUMBER,
3849 p_usr_msg OUT NOCOPY VARCHAR2
3850 ) RETURN BOOLEAN
3851 IS
3852 l_currency_code gl_sets_of_books.currency_code%TYPE;
3853 l_enable_budg_control_flag gl_sets_of_books.enable_budgetary_control_flag%TYPE;
3854 l_cc_bc_enable_flag igc_cc_bc_enable.cc_bc_enable_flag%TYPE;
3855 /*Bug No : 6341012. SLA Uptake. Encumbrance Types are not required*/
3856 -- l_req_encumbrance_type_id financials_system_params_all.req_encumbrance_type_id%TYPE;
3857 -- l_purch_encumbrance_type_id financials_system_params_all.purch_encumbrance_type_id%TYPE;
3858 l_req_encumbrance_flag financials_system_params_all.req_encumbrance_flag%TYPE;
3859 l_purch_encumbrance_flag financials_system_params_all.purch_encumbrance_flag%TYPE;
3860 -- l_cc_prov_enc_enable_flag igc_cc_encmbrnc_ctrls.cc_prov_encmbrnc_enable_flag%TYPE;
3861 -- l_cc_conf_enc_enable_flag igc_cc_encmbrnc_ctrls.cc_conf_encmbrnc_enable_flag%TYPE;
3862 -- l_cc_prov_enc_type_id igc_cc_encmbrnc_ctrls.cc_prov_encmbrnc_type_id%TYPE;
3863 -- l_cc_conf_enc_type_id igc_cc_encmbrnc_ctrls.cc_conf_encmbrnc_type_id%TYPE;
3864
3865 e_cc_not_found EXCEPTION;
3866 e_cc_invalid_set_up EXCEPTION;
3867 e_gl_data EXCEPTION;
3868 e_null_parameter EXCEPTION;
3869
3870
3871 BEGIN
3872 p_currency_code := NULL;
3873
3874 p_sbc_on := FALSE;
3875 p_cbc_on := FALSE;
3876 p_prov_enc_on := FALSE;
3877 p_conf_enc_on := FALSE;
3878 /*Bug No : 6341012. SLA Uptake. Encumbrance Types are not required*/
3879 -- p_req_encumbrance_type_id := NULL;
3880 -- p_purch_encumbrance_type_id := NULL;
3881 -- p_cc_prov_enc_type_id := NULL;
3882 -- p_cc_conf_enc_type_id := NULL;
3883
3884 l_enable_budg_control_flag := 'N';
3885
3886 IF (p_org_id IS NULL)
3887 THEN
3888 fnd_message.set_name('IGC', 'IGC_CC_NO_ORG_ID');
3889 fnd_msg_pub.add;
3890 RAISE E_NULL_PARAMETER;
3891 END IF;
3892
3893 IF (p_sob_id IS NULL)
3894 THEN
3895 fnd_message.set_name('IGC', 'IGC_CC_NO_SOB_ID');
3896 fnd_msg_pub.add;
3897 RAISE E_NULL_PARAMETER;
3898 END IF;
3899
3900
3901 /* Check whether SBC is turned on */
3902
3903 BEGIN
3904
3905 SELECT NVL(enable_budgetary_control_flag,'N'),currency_code
3906 INTO l_enable_budg_control_flag,l_currency_code
3907 FROM gl_sets_of_books
3908 WHERE set_of_books_id = p_sob_id;
3909
3910 EXCEPTION
3911
3912 WHEN NO_DATA_FOUND
3913 THEN
3914 l_enable_budg_control_flag := 'N';
3915 END;
3916
3917 p_currency_code := l_currency_code;
3918
3919
3920 IF ( NVL(l_enable_budg_control_flag,'N') = 'Y')
3921 THEN
3922 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID's are not required*/
3923 BEGIN
3924 SELECT req_encumbrance_flag, purch_encumbrance_flag
3925 INTO l_req_encumbrance_flag, l_purch_encumbrance_flag
3926 FROM financials_system_params_all
3927 WHERE set_of_books_id = p_sob_id AND
3928 org_id = p_org_id;
3929 EXCEPTION
3930 WHEN NO_DATA_FOUND
3931 THEN
3932 l_req_encumbrance_flag := 'N';
3933 l_purch_encumbrance_flag := 'N';
3934 END;
3935
3936 p_sbc_on := TRUE;
3937
3938 IF (l_req_encumbrance_flag = 'Y') OR ( l_purch_encumbrance_flag = 'Y')
3939 THEN
3940
3941 /* Check whether CBC is turned on */
3942
3943 BEGIN
3944
3945 SELECT cc_bc_enable_flag
3946 INTO l_cc_bc_enable_flag
3947 FROM igc_cc_bc_enable
3948 WHERE set_of_books_id = p_sob_id;
3949
3950 EXCEPTION
3951
3952 WHEN NO_DATA_FOUND
3953 THEN
3954 l_cc_bc_enable_flag := 'N';
3955 END;
3956
3957 IF (l_cc_bc_enable_flag = 'Y')
3958 THEN
3959 p_cbc_on := TRUE;
3960 ELSE
3961 p_cbc_on := FALSE;
3962 END IF;
3963
3964
3965
3966 IF (NVL(l_req_encumbrance_flag,'N') = 'Y')
3967 THEN
3968 p_prov_enc_on := TRUE;
3969 ELSE
3970 p_prov_enc_on := FALSE;
3971 END IF;
3972
3973 IF (NVL(l_purch_encumbrance_flag,'N') = 'Y')
3974 THEN
3975 p_conf_enc_on := TRUE;
3976 ELSE
3977 p_conf_enc_on := FALSE;
3978 END IF;
3979 END IF;
3980 END IF;
3981
3982 RETURN TRUE;
3983 EXCEPTION
3984
3985 WHEN E_CC_INVALID_SET_UP OR E_NULL_PARAMETER
3986 THEN
3987 p_currency_code := NULL;
3988 p_sbc_on := FALSE;
3989 p_cbc_on := FALSE;
3990 p_prov_enc_on := FALSE;
3991 p_conf_enc_on := FALSE;
3992 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID's are not required*/
3993 /*
3994 p_req_encumbrance_type_id := NULL;
3995 p_purch_encumbrance_type_id := NULL;
3996 p_cc_prov_enc_type_id := NULL;
3997 p_cc_conf_enc_type_id := NULL;
3998 */
3999
4000 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4001 p_data => p_msg_data );
4002 RETURN FALSE;
4003
4004 WHEN OTHERS
4005 THEN
4006 p_currency_code := NULL;
4007 p_sbc_on := FALSE;
4008 p_cbc_on := FALSE;
4009 p_prov_enc_on := FALSE;
4010 p_conf_enc_on := FALSE;
4011 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID's are not required*/
4012 /*
4013 p_req_encumbrance_type_id := NULL;
4014 p_purch_encumbrance_type_id := NULL;
4015 p_cc_prov_enc_type_id := NULL;
4016 p_cc_conf_enc_type_id := NULL;
4017 */
4018
4019 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
4020 'get_budg_ctrl_params');
4021
4022 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
4023 p_data => p_msg_data );
4024
4025 RETURN FALSE;
4026
4027 END get_budg_ctrl_params;
4028
4029 BEGIN
4030 l_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4031 l_state_level := FND_LOG.LEVEL_STATEMENT;
4032
4033 END IGC_CC_REP_YEP_PVT;