[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_YEP_PROCESS_PKG
Source
1 PACKAGE BODY IGC_CC_YEP_PROCESS_PKG as
2 /* $Header: IGCCYEPB.pls 120.16.12010000.3 2008/11/04 09:43:17 dramired ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_YEP_PROCESS_PKG';
5 g_debug_flag VARCHAR2(1) := 'N' ;
6 --following variables added for bug 3199488: fnd logging changes: sdixit
7 g_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 g_state_level number := FND_LOG.LEVEL_STATEMENT;
9 g_proc_level number := FND_LOG.LEVEL_PROCEDURE;
10 g_event_level number := FND_LOG.LEVEL_EVENT;
11 g_excep_level number := FND_LOG.LEVEL_EXCEPTION;
12 g_error_level number := FND_LOG.LEVEL_ERROR;
13 g_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
14 g_path varchar2(500) := 'igc.plsql.igccyepb.igc_cc_yep_process_pkg.';
15
16
17
18
19 /*==================================================================================
20 Procedure YEAR_END_UPDATE
21 =================================================================================*/
22
23
24 FUNCTION YEAR_END_UPDATE ( p_CC_HEADER_ID IN NUMBER,
25 p_YEAR IN NUMBER,
26 p_SOB_ID IN NUMBER,
27 p_REQUEST_ID IN NUMBER,
28 p_yr_start_date IN DATE,
29 p_yr_end_date IN DATE,
30 p_sbc_on IN BOOLEAN,
31 p_cbc_on IN BOOLEAN,
32 p_prov_enc_on IN BOOLEAN,
33 p_conf_enc_on IN BOOLEAN)
34 RETURN VARCHAR2 AS
35
36 CURSOR C14 IS
37 SELECT *
38 FROM IGC_CC_HEADERS
39 WHERE IGC_CC_HEADERS.CC_HEADER_ID = p_CC_HEADER_ID;
40
41 CURSOR C15(p_cc_header_id NUMBER, p_yr_start_date DATE, p_yr_end_date DATE) IS
42 SELECT *
43 FROM IGC_CC_ACCT_LINES A
44 WHERE A.CC_HEADER_ID = p_cc_header_id AND
45 EXISTS (SELECT 'X'
46 FROM IGC_CC_DET_PF B
47 WHERE B.CC_ACCT_LINE_ID = A.CC_ACCT_LINE_ID AND
48 ( B.CC_DET_PF_DATE >= p_yr_start_date AND B.CC_DET_PF_DATE <= p_yr_end_date) );
49
50 CURSOR C16(p_cc_acct_line_id NUMBER) IS
51 SELECT *
52 FROM IGC_CC_DET_PF B
53 WHERE B.CC_ACCT_LINE_ID = p_cc_acct_line_id;
54
55
56
57 l_HEADER_HISTORY_ROWID VARCHAR2(18);
58 l_DET_PF_LINE_HISTORY_ROWID VARCHAR2(18);
59 l_ACCT_HISTORY_ROWID VARCHAR2(18);
60 l_ACTION_ROWID VARCHAR2(18);
61 l_HEADERS_ROWID VARCHAR2(18);
62 l_DET_PF_LINE_ROWID VARCHAR2(18);
63 l_ACCT_ROWID VARCHAR2(18);
64 l_CC_STATE IGC_CC_HEADERS.CC_STATE%TYPE;
65 l_next_yr_start_date DATE;
66 l_min_period_num gl_periods.period_num%TYPE;
67 V14 C14%ROWTYPE;
68 V15 C15%ROWTYPE;
69 V16 C16%ROWTYPE;
70 l_api_version CONSTANT NUMBER := 1.0;
71 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
72 l_commit VARCHAR2(1) := FND_API.G_FALSE;
73 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
74 l_return_status VARCHAR2(1);
75 l_msg_count NUMBER;
76 l_msg_data VARCHAR2(2000);
77 G_FLAG VARCHAR2(1);
78 l_CC_VERSION_NUM IGC_CC_HEADERS.CC_VERSION_NUM%TYPE;
79 l_application_id fnd_application.application_id%TYPE;
80 l_APPROVAL_STATUS IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE;
81 l_PROVISIONAL_COUNTER NUMBER;
82 l_CC_ACCT_DATE DATE;
83 l_cc_acct_encmbrnc_date igc_cc_acct_lines.cc_acct_encmbrnc_date%TYPE;
84 l_cc_det_pf_encmbrnc_date igc_cc_det_pf.cc_det_pf_encmbrnc_date%TYPE;
85 l_Last_Updated_By NUMBER := FND_GLOBAL.USER_ID;
86 l_Last_Update_Login NUMBER := FND_GLOBAL.LOGIN_ID;
87 l_Created_By NUMBER := FND_GLOBAL.USER_ID;
88 l_EXCEPTION igc_cc_process_exceptions.exception_reason%TYPE := NULL;
89 l_action_hist_msg igc_cc_actions.cc_action_notes%TYPE:= NULL;
90 l_CC_ENCMBRNC_STATUS IGC_CC_HEADERS.CC_ENCMBRNC_STATUS%TYPE;
91
92 l_func_amt NUMBER;
93 l_func_billed_amt NUMBER;
94 l_unbilled_amt NUMBER;
95 l_full_path VARCHAR2(500);
96
97 BEGIN
98
99 l_full_path := g_path||'Year_End_Update';--bug 3199488
100
101 SAVEPOINT S1;
102
103 SELECT application_id
104 INTO l_application_id
105 FROM fnd_application
106 WHERE application_short_name = 'SQLGL';
107
108
109 SELECT CC_STATE,CC_ENCMBRNC_STATUS
110 INTO l_CC_STATE,l_CC_ENCMBRNC_STATUS
111 FROM IGC_CC_HEADERS A
112 WHERE A.CC_HEADER_ID = p_CC_HEADER_ID;
113
114
115 SELECT min(gp.period_num)
116 INTO l_min_period_num
117 FROM gl_period_statuses gps,
118 gl_periods gp,
119 gl_sets_of_books gb
120 WHERE
121 gb.set_of_books_id = p_SOB_ID AND
122 gp.period_set_name = gb.period_set_name AND
123 gp.period_type = gb.accounted_period_type AND
124 gps.set_of_books_id = gb.set_of_books_id AND
125 gps.period_name = gp.period_name AND
126 gps.application_id = l_application_id AND
127 gp.period_year = p_year+1 AND
128 gp.adjustment_period_flag = 'N';
129
130
131 SELECT gps.start_date INTO
132 l_next_yr_start_date
133 FROM gl_period_statuses gps,
134 gl_periods gp,
135 gl_sets_of_books gb
136 WHERE gb.set_of_books_id = p_SOB_ID AND
137 gp.period_set_name = gb.period_set_name AND
138 gp.period_type = gb.accounted_period_type AND
139 gps.set_of_books_id = gb.set_of_books_id AND
140 gps.period_name = gp.period_name AND
141 gps.application_id = l_application_id AND
142 gp.period_year = p_year+1 AND
143 gp.period_num = l_min_period_num;
144
145 SELECT OLD_APPROVAL_STATUS
146 INTO l_APPROVAL_STATUS
147 FROM IGC_CC_PROCESS_DATA
148 WHERE CC_HEADER_ID = p_CC_HEADER_ID
149 AND REQUEST_ID = p_REQUEST_ID;
150
151
152 OPEN C14;
153
154 FETCH C14 INTO V14;
155
156 SELECT ROWID INTO l_HEADERS_ROWID
157 FROM IGC_CC_HEADERS A
158 WHERE A.CC_HEADER_ID = V14.CC_HEADER_ID;
159
160 l_CC_VERSION_NUM := V14.CC_VERSION_NUM;
161
162
163 l_PROVISIONAL_COUNTER := 0;
164
165 IF l_CC_STATE = 'PR' OR ( l_CC_STATE = 'CL' AND l_CC_ENCMBRNC_STATUS = 'P')
166 THEN
167 l_PROVISIONAL_COUNTER := l_PROVISIONAL_COUNTER + 1;
168
169 END IF; /* Provisional CC */
170
171
172 l_return_status := FND_API.G_RET_STS_SUCCESS;
173
174 IGC_CC_HEADER_HISTORY_PKG.Insert_Row(
175 l_api_version,
176 l_init_msg_list,
177 l_commit,
178 l_validation_level,
179 l_return_status,
180 l_msg_count,
181 l_msg_data,
182 l_HEADER_HISTORY_ROWID,
183 V14.CC_HEADER_ID,
184 V14.ORG_ID,
185 V14.CC_TYPE,
186 V14.CC_NUM,
187 l_CC_VERSION_NUM,
188 'U',
189 V14.CC_STATE,
190 V14.PARENT_HEADER_ID,
191 V14.CC_CTRL_STATUS,
192 V14.CC_ENCMBRNC_STATUS,
193 l_APPROVAL_STATUS,
194 V14.VENDOR_ID,
195 V14.VENDOR_SITE_ID,
196 V14.VENDOR_CONTACT_ID,
197 V14.TERM_ID,
198 V14.LOCATION_ID,
199 V14.SET_OF_BOOKS_ID,
200 V14.CC_ACCT_DATE,
201 V14.CC_DESC,
202 V14.CC_START_DATE,
203 V14.CC_END_DATE,
204 V14.CC_OWNER_USER_ID,
205 V14.CC_PREPARER_USER_ID,
206 V14.CURRENCY_CODE,
207 V14.CONVERSION_TYPE,
208 V14.CONVERSION_DATE,
209 V14.CONVERSION_RATE,
210 V14.LAST_UPDATE_DATE,
211 V14.LAST_UPDATED_BY,
212 V14.LAST_UPDATE_LOGIN,
213 V14.CREATED_BY,
214 V14.CREATION_DATE,
215 V14.WF_ITEM_TYPE,
216 V14.WF_ITEM_KEY,
217 V14.CC_CURRENT_USER_ID,
218 V14.ATTRIBUTE1,
219 V14.ATTRIBUTE2,
220 V14.ATTRIBUTE3,
221 V14.ATTRIBUTE4,
222 V14.ATTRIBUTE5,
223 V14.ATTRIBUTE6,
224 V14.ATTRIBUTE7,
225 V14.ATTRIBUTE8,
226 V14.ATTRIBUTE9,
227 V14.ATTRIBUTE10,
228 V14.ATTRIBUTE11,
229 V14.ATTRIBUTE12,
230 V14.ATTRIBUTE13,
231 V14.ATTRIBUTE14,
232 V14.ATTRIBUTE15,
233 V14.CONTEXT,
234 V14.CC_GUARANTEE_FLAG,
235 G_FLAG);
236
237 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
238 THEN
239 ROLLBACK TO S1;
240 l_EXCEPTION := NULL;
241 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_HST_INSERT');
242 IF(g_excep_level >= g_debug_level) THEN
243 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
244 END IF;
245 l_EXCEPTION := FND_MESSAGE.GET;
246 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
247 (PROCESS_TYPE,
248 PROCESS_PHASE,
249 CC_HEADER_ID,
250 CC_ACCT_LINE_ID,
251 CC_DET_PF_LINE_ID,
252 EXCEPTION_REASON,
253 ORG_ID,
254 SET_OF_BOOKS_ID,
255 REQUEST_ID)
256 VALUES(
257 'Y',
258 'F',
259 V14.CC_HEADER_ID,
260 NULL,
261 NULL,
262 l_EXCEPTION,
263 V14.ORG_ID,
264 p_SOB_ID,
265 p_REQUEST_ID);
266
267 RETURN 'N';
268 END IF;
269
270
271
272 OPEN C15(V14.cc_header_id, p_yr_start_date, p_yr_end_date);
273
274 LOOP
275 FETCH C15 INTO V15;
276 EXIT WHEN C15%NOTFOUND;
277
278 /* Update Account Line History*/
279
280 l_return_status := FND_API.G_RET_STS_SUCCESS;
281
282 IGC_CC_ACCT_LINE_HISTORY_PKG.Insert_Row(
283 l_api_version ,
284 l_init_msg_list,
285 l_commit,
286 l_validation_level,
287 l_return_status,
288 l_msg_count,
289 l_msg_data,
290 l_acct_history_rowid,
291 V15.CC_Acct_Line_Id,
292 V15.CC_Header_Id,
293 V15.Parent_Header_Id,
294 V15.Parent_Acct_Line_Id ,
295 V15.CC_Acct_Line_Num,
296 l_CC_VERSION_NUM,
297 'U',
298 V15.CC_Charge_Code_Combination_Id,
299 V15.CC_Budget_Code_Combination_Id,
300 V15.CC_Acct_Entered_Amt ,
301 V15.CC_Acct_Func_Amt,
302 V15.CC_Acct_Desc ,
303 V15.CC_Acct_Billed_Amt ,
304 V15.CC_Acct_Unbilled_Amt,
305 V15.CC_Acct_Taxable_Flag,
306 Null,-- tax_id Bug 6472296 EB Tax uptake
307 V15.CC_Acct_Encmbrnc_Amt,
308 V15.CC_Acct_Encmbrnc_Date,
309 V15.CC_Acct_Encmbrnc_Status,
310 V15.Project_Id,
311 V15.Task_Id,
312 V15.Expenditure_Type,
313 V15.Expenditure_Org_Id,
314 V15.Expenditure_Item_Date,
315 V15.Last_Update_Date,
316 V15.Last_Updated_By,
317 V15.Last_Update_Login ,
318 V15.Creation_Date ,
319 V15.Created_By ,
320 V15.Attribute1,
321 V15.Attribute2,
322 V15.Attribute3,
323 V15.Attribute4,
324 V15.Attribute5,
325 V15.Attribute6,
326 V15.Attribute7,
327 V15.Attribute8,
328 V15.Attribute9,
329 V15.Attribute10,
330 V15.Attribute11,
331 V15.Attribute12,
332 V15.Attribute13,
333 V15.Attribute14,
334 V15.Attribute15,
335 V15.Context,
336 V15.CC_FUNC_WITHHELD_AMT,
337 V15.CC_ENT_WITHHELD_AMT,
338 G_FLAG,
339 V15.tax_classif_code--Bug 6472296 EB Tax uptake
340 );
341
342 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
343 THEN
344 ROLLBACK TO S1;
345 l_EXCEPTION := NULL;
346 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINE_HST_INSERT');
347 IF(g_excep_level >= g_debug_level) THEN
348 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
349 END IF;
350 l_EXCEPTION := FND_MESSAGE.GET;
351
352
353 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
354 (PROCESS_TYPE,
355 PROCESS_PHASE,
356 CC_HEADER_ID,
357 CC_ACCT_LINE_ID,
358 CC_DET_PF_LINE_ID,
359 EXCEPTION_REASON,
360 ORG_ID,
361 SET_OF_BOOKS_ID,
362 REQUEST_ID)
363 VALUES(
364 'Y',
365 'F',
366 V14.CC_HEADER_ID,
367 V15.CC_ACCT_LINE_ID,
368 NULL,
369 l_EXCEPTION,
370 V14.ORG_ID,
371 p_SOB_ID,
372 p_REQUEST_ID);
373
374 RETURN 'N';
375 END IF;
376
377
378 SELECT ROWID INTO l_ACCT_ROWID
379 FROM IGC_CC_ACCT_LINES B
380 WHERE B.CC_HEADER_ID = V14.CC_HEADER_ID
381 AND B.CC_ACCT_LINE_ID = V15.CC_ACCT_LINE_ID;
382
383 IF ( ( l_cc_state = 'PR' OR l_cc_state = 'CL')
384 AND p_cbc_on = TRUE AND p_prov_enc_on = TRUE
385 )
386 OR ( ( l_cc_state = 'CM' OR l_cc_state = 'CT')
387 AND p_cbc_on = TRUE AND p_conf_enc_on = TRUE
388 )
389 THEN
390
391 l_cc_acct_encmbrnc_date := l_next_yr_start_date;
392 ELSE
393 l_cc_acct_encmbrnc_date := v15.cc_acct_encmbrnc_date;
394
395 END IF;
396
397 IGC_CC_ACCT_LINES_PKG.Update_Row(
398 l_api_version ,
399 l_init_msg_list,
400 l_commit,
401 l_validation_level,
402 l_return_status,
403 l_msg_count,
404 l_msg_data,
405 l_acct_rowid,
406 V15.CC_Acct_Line_Id,
407 V15.CC_Header_Id,
408 V15.Parent_Header_Id,
409 V15.Parent_Acct_Line_Id ,
410 V15.CC_Charge_Code_Combination_Id,
411 V15.CC_Acct_Line_Num,
412 V15.CC_Budget_Code_Combination_Id,
413 V15.CC_Acct_Entered_Amt ,
414 V15.CC_Acct_Func_Amt,
415 V15.CC_Acct_Desc ,
416 V15.CC_Acct_Billed_Amt ,
417 V15.CC_Acct_Unbilled_Amt,
418 V15.CC_Acct_Taxable_Flag,
419 Null,--tax_id Bug 6472296 EB Tax uptake
420 V15.CC_Acct_Encmbrnc_Amt,
421 l_cc_acct_encmbrnc_date,
422 V15.CC_Acct_Encmbrnc_Status,
423 V15.Project_Id,
424 V15.Task_Id,
425 V15.Expenditure_Type,
426 V15.Expenditure_Org_Id,
427 V15.Expenditure_Item_Date,
428 V15.Last_Update_Date,
429 V15.Last_Updated_By,
430 V15.Last_Update_Login ,
431 V15.Creation_Date ,
432 V15.Created_By ,
433 V15.Attribute1,
434 V15.Attribute2,
435 V15.Attribute3,
436 V15.Attribute4,
437 V15.Attribute5,
438 V15.Attribute6,
439 V15.Attribute7,
440 V15.Attribute8,
441 V15.Attribute9,
442 V15.Attribute10 ,
443 V15.Attribute11,
444 V15.Attribute12,
445 V15.Attribute13,
446 V15.Attribute14,
447 V15.Attribute15,
448 V15.Context,
449 V15.CC_FUNC_WITHHELD_AMT,
450 V15.CC_ENT_WITHHELD_AMT,
451 G_FLAG,
452 V15.tax_classif_code -- Bug 6472296 EB Tax uptake
453 );
454
455
456 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
457 THEN
458 ROLLBACK TO S1;
459 l_EXCEPTION := NULL;
460 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACT_LINES_UPDATE');
461 IF(g_excep_level >= g_debug_level) THEN
462 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
463 END IF;
464 l_EXCEPTION := FND_MESSAGE.GET;
465
466
467 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
468 (PROCESS_TYPE,
469 PROCESS_PHASE,
470 CC_HEADER_ID,
471 CC_ACCT_LINE_ID,
472 CC_DET_PF_LINE_ID,
473 EXCEPTION_REASON,
474 ORG_ID,
475 SET_OF_BOOKS_ID,
476 REQUEST_ID)
477 VALUES(
478 'Y',
479 'F',
480 V14.CC_HEADER_ID,
481 V15.CC_ACCT_LINE_ID,
482 NULL,
483 l_EXCEPTION,
484 V14.ORG_ID,
485 p_SOB_ID,
486 p_REQUEST_ID);
487
488 RETURN 'N';
489 END IF;
490
491
492 OPEN C16(V15.CC_ACCT_LINE_ID);
493 LOOP
494
495 FETCH C16 INTO V16;
496
497 EXIT WHEN C16%NOTFOUND;
498
499 l_return_status := FND_API.G_RET_STS_SUCCESS;
500
501 -- 2251118, Check that the line is not fully billed
502 -- Bidisha S, 12 Mar 2002
503 SELECT cc_det_pf_func_amt,
504 cc_det_pf_func_billed_amt
505 INTO l_func_amt,
506 l_func_billed_amt
507 FROM igc_cc_det_pf_v
508 WHERE cc_det_pf_line_id = V16.cc_det_pf_line_id;
509
510 l_unbilled_amt := l_func_amt - l_func_billed_amt;
511
512 IF V16.CC_DET_PF_DATE >= p_yr_start_date
513 AND V16.CC_DET_PF_DATE <= p_yr_end_date
514 AND l_unbilled_amt > 0
515 THEN
516
517 IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
518 l_api_version,
519 l_init_msg_list,
520 l_commit,
521 l_validation_level,
522 l_return_status,
523 l_msg_count,
524 l_msg_data,
525 l_DET_PF_LINE_HISTORY_ROWID,
526 V16.CC_Det_PF_Line_Id,
527 V16.CC_Det_PF_Line_Num,
528 V16.CC_Acct_Line_Id,
529 V16.Parent_Acct_Line_Id,
530 V16.Parent_Det_PF_Line_Id,
531 l_CC_VERSION_NUM,
532 'U',
533 V16.CC_Det_PF_Entered_Amt,
534 V16.CC_Det_PF_Func_Amt,
535 V16.CC_Det_PF_Date,
536 V16.CC_Det_PF_Billed_Amt,
537 V16.CC_Det_PF_Unbilled_Amt,
538 V16.CC_Det_PF_Encmbrnc_Amt,
539 V16.CC_Det_PF_Encmbrnc_Date,
540 V16.CC_Det_PF_Encmbrnc_Status,
541 V16.Last_Update_Date,
542 V16.Last_Updated_By,
543 V16.Last_Update_Login,
544 V16.Creation_Date,
545 V16.Created_By,
546 V16.Attribute1,
547 V16.Attribute2,
548 V16.Attribute3,
549 V16.Attribute4,
550 V16.Attribute5,
551 V16.Attribute6,
552 V16.Attribute7,
553 V16.Attribute8,
554 V16.Attribute9,
555 V16.Attribute10,
556 V16.Attribute11,
557 V16.Attribute12,
558 V16.Attribute13,
559 V16.Attribute14,
560 V16.Attribute15,
561 V16.Context,
562 G_FLAG );
563 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
564 THEN
565 ROLLBACK TO S1;
566 l_EXCEPTION := NULL;
567 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_HST_INSERT');
568 IF(g_excep_level >= g_debug_level) THEN
569 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
570 END IF;
571 l_EXCEPTION := FND_MESSAGE.GET;
572
573
574 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
575 (PROCESS_TYPE,
576 PROCESS_PHASE,
577 CC_HEADER_ID,
578 CC_ACCT_LINE_ID,
579 CC_DET_PF_LINE_ID,
580 EXCEPTION_REASON,
581 ORG_ID,
582 SET_OF_BOOKS_ID,
583 REQUEST_ID)
584 VALUES(
585 'Y',
586 'F',
587 V14.CC_HEADER_ID,
588 V15.CC_ACCT_LINE_ID,
589 V16.CC_Det_PF_Line_Id,
590 l_EXCEPTION,
591 V14.ORG_ID,
592 p_SOB_ID,
593 p_REQUEST_ID);
594
595 RETURN 'N';
596 END IF;
597
598
599 SELECT ROWID INTO l_DET_PF_LINE_ROWID
600 FROM IGC_CC_DET_PF D
601 WHERE D.CC_DET_PF_LINE_ID = V16.CC_DET_PF_LINE_ID
602 AND D.CC_ACCT_LINE_ID = V15.CC_ACCT_LINE_ID;
603
604 IF ( (l_cc_state = 'PR') OR (l_cc_state = 'CL') )
605 AND (p_sbc_on = TRUE) AND (p_prov_enc_on = TRUE)
606 OR
607 ( (l_cc_state = 'CM') OR (l_cc_state = 'CT') )
608 AND (p_sbc_on = TRUE) AND (p_conf_enc_on = TRUE)
609 THEN
610
611 l_cc_det_pf_encmbrnc_date := l_next_yr_start_date;
612 ELSE
613 l_cc_det_pf_encmbrnc_date := v16.cc_det_pf_encmbrnc_date;
614
615 END IF;
616
617
618 IGC_CC_DET_PF_PKG.Update_Row(
619 l_api_version,
620 l_init_msg_list,
621 l_commit,
622 l_validation_level,
623 l_return_status,
624 l_msg_count,
625 l_msg_data,
626 l_DET_PF_LINE_ROWID,
627 V16.CC_Det_PF_Line_Id,
628 V16.CC_Det_PF_Line_Num,
629 V16.CC_Acct_Line_Id,
630 V16.Parent_Acct_Line_Id,
631 V16.Parent_Det_PF_Line_Id,
632 V16.CC_Det_PF_Entered_Amt,
633 V16.CC_Det_PF_Func_Amt,
634 l_next_yr_start_date,
635 V16.CC_Det_PF_Billed_Amt,
636 V16.CC_Det_PF_Unbilled_Amt,
637 V16.CC_Det_PF_Encmbrnc_Amt,
638 l_cc_det_pf_encmbrnc_date,
639 V16.CC_Det_PF_Encmbrnc_Status,
640 V16.Last_Update_Date,
641 V16.Last_Updated_By,
642 V16.Last_Update_Login,
643 V16.Creation_Date,
644 V16.Created_By,
645 V16.Attribute1,
646 V16.Attribute2,
647 V16.Attribute3,
648 V16.Attribute4,
649 V16.Attribute5,
650 V16.Attribute6,
651 V16.Attribute7,
652 V16.Attribute8,
653 V16.Attribute9,
654 V16.Attribute10,
655 V16.Attribute11,
656 V16.Attribute12,
657 V16.Attribute13,
658 V16.Attribute14,
659 V16.Attribute15,
660 V16.Context,
661 G_FLAG );
662
663
664 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
665 THEN
666 ROLLBACK TO S1;
667 l_EXCEPTION := NULL;
668 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_DET_PF_UPDATE');
669 IF(g_excep_level >= g_debug_level) THEN
670 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
671 END IF;
672 l_EXCEPTION := FND_MESSAGE.GET;
673
674
675 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
676 (PROCESS_TYPE,
677 PROCESS_PHASE,
678 CC_HEADER_ID,
679 CC_ACCT_LINE_ID,
680 CC_DET_PF_LINE_ID,
681 EXCEPTION_REASON,
682 ORG_ID,
683 SET_OF_BOOKS_ID,
684 REQUEST_ID)
685 VALUES(
686 'Y',
687 'F',
688 V14.CC_HEADER_ID,
689 V15.CC_ACCT_LINE_ID,
690 V16.CC_Det_PF_Line_Id,
691 l_EXCEPTION,
692 V14.ORG_ID,
693 p_SOB_ID,
694 p_REQUEST_ID);
695
696 RETURN 'N';
697 END IF;
698
699 END IF;
700 END LOOP;
701 CLOSE C16; /* PF lines */
702
703 END LOOP; /* Account lines */
704 CLOSE C15;
705
706 IF ( (l_cc_state = 'PR') OR (l_cc_state = 'CL') )
707 AND (p_cbc_on = TRUE ) AND (p_prov_enc_on = TRUE)
708 THEN
709
710 IF l_PROVISIONAL_COUNTER > 0
711 THEN
712 l_CC_ACCT_DATE := l_next_yr_start_date;
713 ELSE
714 l_CC_ACCT_DATE := V14.CC_ACCT_DATE;
715 END IF;
716 ELSE
717 l_CC_ACCT_DATE := V14.CC_ACCT_DATE;
718
719 END IF;
720
721
722 SELECT ROWID INTO l_HEADERS_ROWID
723 FROM IGC_CC_HEADERS A
724 WHERE A.CC_HEADER_ID = V14.CC_HEADER_ID;
725
726
727 IGC_CC_HEADERS_PKG.Update_Row(
728 l_api_version,
729 l_init_msg_list,
730 l_commit,
731 l_validation_level,
732 l_return_status,
733 l_msg_count,
734 l_msg_data,
735 l_HEADERS_ROWID,
736 V14.CC_HEADER_ID,
737 V14.ORG_ID,
738 V14.CC_TYPE,
739 V14.CC_NUM,
740 l_CC_VERSION_NUM + 1 ,
741 V14.PARENT_HEADER_ID,
742 V14.CC_STATE,
743 V14.CC_CTRL_STATUS,
744 V14.CC_ENCMBRNC_STATUS,
745 l_APPROVAL_STATUS,
746 V14.VENDOR_ID,
747 V14.VENDOR_SITE_ID,
748 V14.VENDOR_CONTACT_ID,
749 V14.TERM_ID,
750 V14.LOCATION_ID,
751 V14.SET_OF_BOOKS_ID,
752 l_CC_ACCT_DATE,
753 V14.CC_DESC,
754 V14.CC_START_DATE,
755 V14.CC_END_DATE,
756 V14.CC_OWNER_USER_ID,
757 V14.CC_PREPARER_USER_ID,
758 V14.CURRENCY_CODE,
759 V14.CONVERSION_TYPE,
760 V14.CONVERSION_DATE,
761 V14.CONVERSION_RATE,
762 V14.LAST_UPDATE_DATE,
763 V14.LAST_UPDATED_BY,
764 V14.LAST_UPDATE_LOGIN,
765 V14.CREATED_BY,
766 V14.CREATION_DATE,
767 V14.CC_CURRENT_USER_ID,
768 V14.WF_ITEM_TYPE,
769 V14.WF_ITEM_KEY,
770 V14.ATTRIBUTE1,
771 V14.ATTRIBUTE2,
772 V14.ATTRIBUTE3,
773 V14.ATTRIBUTE4,
774 V14.ATTRIBUTE5,
775 V14.ATTRIBUTE6,
776 V14.ATTRIBUTE7,
777 V14.ATTRIBUTE8,
778 V14.ATTRIBUTE9,
779 V14.ATTRIBUTE10,
780 V14.ATTRIBUTE11,
781 V14.ATTRIBUTE12,
782 V14.ATTRIBUTE13,
783 V14.ATTRIBUTE14,
784 V14.ATTRIBUTE15,
785 V14.CONTEXT,
786 V14.CC_GUARANTEE_FLAG,
787 G_FLAG);
788
789 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
790 THEN
791 ROLLBACK TO S1;
792 l_EXCEPTION := NULL;
793 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADERS_UPDATE');
794 IF(g_excep_level >= g_debug_level) THEN
795 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
796 END IF;
797 l_EXCEPTION := FND_MESSAGE.GET;
798
799
800 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
801 (PROCESS_TYPE,
802 PROCESS_PHASE,
803 CC_HEADER_ID,
804 CC_ACCT_LINE_ID,
805 CC_DET_PF_LINE_ID,
806 EXCEPTION_REASON,
807 ORG_ID,
808 SET_OF_BOOKS_ID,
809 REQUEST_ID)
810 VALUES(
811 'Y',
812 'F',
813 V14.CC_HEADER_ID,
814 NULL,
815 NULL,
816 l_EXCEPTION,
817 V14.ORG_ID,
818 p_SOB_ID,
819 p_REQUEST_ID);
820
821 RETURN 'N';
822 END IF;
823
824
825 /* Insert into Action History */
826
827 l_init_msg_list := FND_API.G_FALSE;
828 l_commit := FND_API.G_FALSE;
829 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
830
831 l_return_status := '';
832
833 /* added following code to remove hard coded message reference */
834 /* change begin */
835
836 l_action_hist_msg := NULL;
837
838 /* change end */
839
840 IGC_CC_ACTIONS_PKG.Insert_Row(
841 1.0,
842 l_init_msg_list,
843 l_commit,
844 l_validation_level,
845 l_return_status,
846 l_msg_count,
847 l_msg_data,
848 l_ACTION_ROWID,
849 V14.CC_HEADER_ID,
850 NVL(l_CC_VERSION_NUM,0) + 1,
851 'YP',
852 V14.CC_STATE,
853 V14.CC_CTRL_STATUS,
854 l_APPROVAL_STATUS,
855 l_action_hist_msg,
856 Sysdate,
857 l_Last_Updated_By,
858 l_Last_Update_Login,
859 Sysdate,
860 l_Created_By);
861
862 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
863 THEN
864 ROLLBACK TO S1;
865 l_EXCEPTION := NULL;
866 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_ACTION_HST_INSERT');
867 IF(g_excep_level >= g_debug_level) THEN
868 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
869 END IF;
870 l_EXCEPTION := FND_MESSAGE.GET;
871
872
873 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
874 (PROCESS_TYPE,
875 PROCESS_PHASE,
876 CC_HEADER_ID,
877 CC_ACCT_LINE_ID,
878 CC_DET_PF_LINE_ID,
879 EXCEPTION_REASON,
880 ORG_ID,
881 SET_OF_BOOKS_ID,
882 REQUEST_ID)
883 VALUES(
884 'Y',
885 'F',
886 V14.CC_HEADER_ID,
887 NULL,
888 NULL,
889 l_EXCEPTION,
890 V14.ORG_ID,
891 p_SOB_ID,
892 p_REQUEST_ID);
893
894 RETURN 'N';
895 END IF;
896
897
898 CLOSE C14; /* Header */
899
900 RETURN 'Y';
901
902 EXCEPTION
903 WHEN OTHERS
904 THEN
905 ROLLBACK TO S1;
906 RETURN 'N';
907
908 END YEAR_END_UPDATE;
909
910
911 /*==================================================================================
912 End of UPDATE_CC Procedure
913 =================================================================================*/
914
915 /*==================================================================================
916 Procedure YEAR_END_MAIN
917 =================================================================================*/
918
919
920 PROCEDURE YEAR_END_MAIN ( errbuf OUT NOCOPY VARCHAR2,
921 retcode OUT NOCOPY VARCHAR2,
922 /* Bug No : 6341012. MOAC uptake. SOB_ID, ORG_ID are no more retrieved from profile values in R12 */
923 -- p_SOB_ID IN NUMBER,
924 -- p_ORG_ID IN NUMBER,
925 p_PROCESS_PHASE IN VARCHAR2,
926 p_YEAR IN NUMBER)
927 AS
928
929 l_REQUEST_ID1 NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
930 -- Bug No : 6341012. MOAC uptake. Local variables for SOB_ID,SOB_NAME,ORG_ID
931 l_sob_id NUMBER;
932 l_sob_name VARCHAR2(30);
933 l_org_id NUMBER;
934
935 CURSOR C1(START_DATE DATE,
936 END_DATE DATE,
937 c_SOB_ID NUMBER,
938 c_ORG_ID NUMBER) IS
939 -- Performance tuning, replaced the following
940 -- query with the one below.
941 -- SELECT *
942 -- FROM IGC_CC_HEADERS
943 -- WHERE CC_HEADER_ID IN
944 -- (
945 -- SELECT IGC_CC_HEADERS.CC_HEADER_ID
946 -- FROM IGC_CC_HEADERS, IGC_CC_ACCT_LINES, IGC_CC_DET_PF
947 -- WHERE IGC_CC_DET_PF. CC_ACCT_LINE_ID = IGC_CC_ACCT_LINES. CC_ACCT_LINE_ID
948 -- AND IGC_CC_ACCT_LINES.CC_HEADER_ID = IGC_CC_HEADERS.CC_HEADER_ID
949 -- AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = c_SOB_ID
950 -- AND IGC_CC_HEADERS.ORG_ID = c_ORG_ID
951 -- AND CC_DET_PF_DATE BETWEEN START_DATE AND END_DATE )
952 --
953 -- AND ( ( CC_STATE = 'PR' )
954 -- OR ( CC_STATE = 'CM' )
955 -- OR ( CC_STATE = 'CT' AND CC_APPRVL_STATUS <> 'AP' )
956 -- OR ( CC_STATE = 'CL' AND CC_APPRVL_STATUS <> 'AP' ) )
957
958 -- AND ( CC_END_DATE > END_DATE OR CC_END_DATE IS NULL);
959
960
961 SELECT *
962 FROM IGC_CC_HEADERS A
963 WHERE (( A.CC_STATE = 'PR' )
964 OR ( A.CC_STATE = 'CM' )
965 OR ( A.CC_STATE = 'CT' AND A.CC_APPRVL_STATUS <> 'AP' )
966 OR ( A.CC_STATE = 'CL' AND A.CC_APPRVL_STATUS <> 'AP' ) )
967 AND ( A.CC_END_DATE > END_DATE OR A.CC_END_DATE IS NULL)
968 AND A.SET_OF_BOOKS_ID = c_SOB_ID
969 AND A.ORG_ID = c_ORG_ID
970 AND EXISTS
971 (
972 SELECT 'X'
973 FROM IGC_CC_ACCT_LINES B,
974 IGC_CC_DET_PF C
975 WHERE B.CC_ACCT_LINE_ID = C.CC_ACCT_LINE_ID
976 AND B.CC_HEADER_ID = A.CC_HEADER_ID
977 AND C.CC_DET_PF_DATE BETWEEN START_DATE AND END_DATE );
978
979
980 CURSOR C2 IS
981 SELECT *
982 FROM IGC_CC_PROCESS_DATA X
983 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
984 WHERE X.SET_OF_BOOKS_ID = l_sob_id
985 AND REQUEST_ID = l_REQUEST_ID1
986 AND X.ORG_ID = l_org_id
987 AND X.PROCESS_TYPE = 'Y'
988 AND (X.PROCESSED <> 'Y' OR X.PROCESSED IS NULL);
989
990 CURSOR C6 IS
991 SELECT B.PERIOD_NUM, B.PERIOD_NAME , A.CC_PERIOD_STATUS
992 FROM IGC_CC_PERIODS A,
993 GL_PERIODS_V B,
994 GL_SETS_OF_BOOKS C
995 WHERE B.PERIOD_YEAR = p_YEAR
996 AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
997 AND B.PERIOD_SET_NAME = C.PERIOD_SET_NAME
998 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
999 AND C.SET_OF_BOOKS_ID = l_sob_id
1000 AND B.PERIOD_TYPE = C.ACCOUNTED_PERIOD_TYPE
1001 AND A.ORG_ID = l_org_id
1002 AND A.PERIOD_NAME = B.PERIOD_NAME
1003 AND ADJUSTMENT_PERIOD_FLAG = 'N';
1004
1005
1006 CURSOR C7 IS
1007 SELECT B.PERIOD_NUM, B.PERIOD_NAME , A.CC_PERIOD_STATUS
1008 FROM IGC_CC_PERIODS A,
1009 GL_PERIODS_V B,
1010 GL_SETS_OF_BOOKS C
1011 WHERE B.PERIOD_YEAR = p_YEAR+1
1012 AND A.PERIOD_SET_NAME = B.PERIOD_SET_NAME
1013 AND B.PERIOD_SET_NAME = C.PERIOD_SET_NAME
1014 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1015 AND C.SET_OF_BOOKS_ID = l_sob_id
1016 AND B.PERIOD_TYPE = C.ACCOUNTED_PERIOD_TYPE
1017 AND A.ORG_ID = l_org_id
1018 AND A.PERIOD_NAME = B.PERIOD_NAME
1019 AND ADJUSTMENT_PERIOD_FLAG = 'N';
1020
1021 -- Added parameters p_year and p_period_num for Bug 3464401
1022 -- This makes cursor C9 redundant.
1023 CURSOR C8 (p_year NUMBER,
1024 p_period_num NUMBER) IS
1025 SELECT PERIOD_NAME, PERIOD_NUM, CLOSING_STATUS
1026 FROM GL_PERIOD_STATUSES
1027 WHERE APPLICATION_ID = (SELECT APPLICATION_ID
1028 FROM FND_APPLICATION
1029 WHERE APPLICATION_SHORT_NAME = 'SQLGL')
1030 -- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
1031 AND SET_OF_BOOKS_ID = l_sob_id
1032 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1033 AND PERIOD_YEAR = p_year
1034 AND PERIOD_NUM = p_period_num;
1035
1036 -- AND PERIOD_YEAR = p_YEAR+1
1037 -- AND PERIOD_NUM = 1;
1038
1039 /*
1040 CURSOR C9 IS
1041 SELECT PERIOD_NAME, PERIOD_NUM, CLOSING_STATUS
1042 FROM GL_PERIOD_STATUSES
1043 WHERE APPLICATION_ID = (SELECT APPLICATION_ID
1044 FROM FND_APPLICATION
1045 WHERE APPLICATION_SHORT_NAME = 'SQLGL')
1046
1047 AND PERIOD_YEAR = p_YEAR
1048 -- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
1049 AND SET_OF_BOOKS_ID = l_sob_id
1050 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1051 AND PERIOD_NUM = 12;
1052
1053 */
1054
1055 CURSOR C11(H_ID NUMBER) IS
1056 SELECT *
1057 FROM IGC_CC_HEADERS
1058 WHERE CC_HEADER_ID IN (SELECT IGC_CC_HEADERS.CC_HEADER_ID
1059 FROM IGC_CC_HEADERS,IGC_CC_PROCESS_DATA
1060 WHERE IGC_CC_HEADERS.PARENT_HEADER_ID = H_ID
1061 AND IGC_CC_HEADERS.CC_HEADER_ID = IGC_CC_PROCESS_DATA.CC_HEADER_ID
1062 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1063 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
1064 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
1065 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id);
1066
1067
1068 V1 C1%ROWTYPE;
1069 V2 C2%ROWTYPE;
1070 V6 C6%ROWTYPE;
1071 V7 C7%ROWTYPE;
1072 V8 C8%ROWTYPE;
1073 --V9 C9%ROWTYPE;
1074 V11 C11%ROWTYPE;
1075 l_PERIOD_COUNTER NUMBER := 0;
1076 l_STATUS_COUNTER NUMBER :=0;
1077 l_LOCK_CC_STATUS BOOLEAN;
1078 l_LOCK_PO_STATUS BOOLEAN;
1079 l_budg_status BOOLEAN;
1080 l_PROCESS_TYPE IGC_CC_PROCESS_DATA.PROCESS_TYPE%TYPE;
1081 l_RESULT_OF_VALIDATION IGC_CC_PROCESS_DATA.VALIDATION_STATUS%TYPE;
1082 l_RESULT_OF_RESERVATION IGC_CC_PROCESS_DATA.RESERVATION_STATUS%TYPE;
1083 l_RESULT_OF_YEAR_END_UPDATE IGC_CC_PROCESS_DATA.PROCESSED%TYPE;
1084 l_VALIDATION_COUNTER NUMBER;
1085 l_PROCESSED_COUNTER NUMBER;
1086 l_CC_APPROVAL_STATUS IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE;
1087 l_CC_TYPE IGC_CC_HEADERS.CC_TYPE%TYPE;
1088 l_CONTRACT_COUNTER NUMBER := 0;
1089 l_CC_CTRL_STATUS IGC_CC_HEADERS.CC_CTRL_STATUS%TYPE;
1090 l_APPROVED_FLAG PO_HEADERS_ALL.APPROVED_FLAG%TYPE;
1091 l_HEADER_ID IGC_CC_HEADERS.CC_HEADER_ID%TYPE;
1092 RELEASE_YEAR_END_COUNTER NUMBER;
1093 l_api_version CONSTANT NUMBER := 1.0;
1094 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
1095 l_commit VARCHAR2(1) := FND_API.G_FALSE;
1096 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1097 l_return_status VARCHAR2(1);
1098 l_msg_count NUMBER;
1099 l_msg_data VARCHAR2(12000);
1100 l_usr_msg igc_cc_process_exceptions.exception_reason%TYPE;
1101 l_EXCEPTION igc_cc_process_exceptions.exception_reason%TYPE := NULL;
1102 l_error_text VARCHAR2(12000);
1103 l_CC_NUM IGC_CC_HEADERS.CC_NUM%TYPE;
1104 l_invalid_counter NUMBER;
1105
1106 /********************** ENCUMBRANCE DECLARATION *******************/
1107
1108 l_application_id fnd_application.application_id%TYPE;
1109 l_yr_start_date DATE;
1110 l_yr_start_date_next DATE;
1111 l_yr_end_date DATE;
1112 l_yr_end_cr_date DATE;
1113 l_yr_end_dr_date DATE;
1114 l_min_period_num gl_periods.period_num%TYPE;
1115 l_max_period_num gl_periods.period_num%TYPE;
1116
1117 l_currency_code gl_sets_of_books.currency_code%TYPE;
1118 l_sbc_on BOOLEAN;
1119 l_cbc_on BOOLEAN;
1120 l_prov_enc_on BOOLEAN;
1121 l_conf_enc_on BOOLEAN;
1122
1123 /* Bug No : 6341012. SLA uptake. Encumbrance Type IDs are not required */
1124 -- l_req_encumbrance_type_id NUMBER;
1125 -- l_purch_encumbrance_type_id NUMBER;
1126 -- l_cc_prov_enc_type_id NUMBER;
1127 -- l_cc_conf_enc_type_id NUMBER;
1128
1129 l_COUNTER NUMBER := 0;
1130 l_REQUEST_ID NUMBER;
1131 l_Type IGC_CC_HEADERS.CC_TYPE%TYPE;
1132 l_STATE IGC_CC_HEADERS.CC_STATE%TYPE;
1133 l_PREVIOUS_APPRVL_STATUS IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE;
1134 l_DUMMY VARCHAR2(1);
1135
1136
1137 /******************** END OF ENCUMBRANCE DECLARATION ********************/
1138
1139 l_option_name VARCHAR2(80);
1140 lv_message VARCHAR2(800);
1141 l_full_path VARCHAR2(500);
1142 -- Varibles used for xml report
1143 l_terr VARCHAR2(10):='US';
1144 l_lang VARCHAR2(10):='en';
1145 l_layout BOOLEAN;
1146
1147 BEGIN
1148
1149 l_full_path := g_path||'Year_End_Main';--bug 3199488
1150
1151 -- 01/03/02, check to see if CBC is installed
1152 -- code will remain commented out for now
1153
1154 IF NOT igi_gen.is_req_installed('CC') THEN
1155
1156 SELECT meaning
1157 INTO l_option_name
1158 FROM igi_lookups
1159 WHERE lookup_code = 'CC'
1160 AND lookup_type = 'GCC_DESCRIPTION';
1161
1162 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
1163 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
1164 IF(g_excep_level >= g_debug_level) THEN
1165 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1166 END IF;
1167 lv_message := fnd_message.get;
1168 errbuf := lv_message;
1169 retcode := 2;
1170 return;
1171 END IF;
1172
1173 retcode := 0;
1174
1175 -- Bug 1914745, clear any old records from the igc_cc_interface table
1176 -- DELETE FROM igc_cc_interface
1177 -- WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date((sysdate - interval '2' day), 'DD/MM/YYYY');
1178
1179 -- Bug 2872060. Above Delete command commented out, was causing compilation probs in Oracle8i.
1180 DELETE FROM igc_cc_interface
1181 WHERE to_date(creation_date,'DD/MM/YYYY') <= to_date(sysdate ,'DD/MM/YYYY') - 2;
1182
1183 /* Begin fix for bug 1576023 */
1184 l_msg_data := NULL;
1185 l_msg_count := 0;
1186 l_usr_msg := NULL;
1187
1188 /* Bug No : 6341012. MOAC Uptake. SOB_ID,ORG_ID values are retrieved */
1189 l_org_id := MO_GLOBAL.get_current_org_id;
1190 MO_UTILS.get_ledger_info(l_org_id,l_sob_id,l_sob_name);
1191
1192 l_budg_status := IGC_CC_REP_YEP_PVT.get_budg_ctrl_params(
1193 /* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1194 l_sob_id,
1195 l_org_id,
1196 l_currency_code,
1197 l_sbc_on,
1198 l_cbc_on,
1199 l_prov_enc_on,
1200 l_conf_enc_on,
1201 /*Bug No : 6341012. R12 SLA Uptake. Encumbrance Type IDs are not required */
1202 -- l_req_encumbrance_type_id,
1203 -- l_purch_encumbrance_type_id,
1204 -- l_cc_prov_enc_type_id,
1205 -- l_cc_conf_enc_type_id,
1206 l_msg_data,
1207 l_msg_count,
1208 l_usr_msg
1209 ) ;
1210
1211
1212 IF (l_budg_status = FALSE) AND (l_usr_msg IS NOT NULL)
1213 THEN
1214 INSERT INTO
1215 igc_cc_process_exceptions
1216 (process_type,
1217 process_phase,
1218 cc_header_id,
1219 cc_acct_line_id,
1220 cc_det_pf_line_id,
1221 exception_reason,
1222 org_id,
1223 set_of_books_id,
1224 request_id)
1225 VALUES
1226 ( 'Y',
1227 p_process_phase,
1228 NULL,
1229 NULL,
1230 NULL,
1231 l_usr_msg,
1232 /* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1233 l_org_id,
1234 l_sob_id,
1235 l_request_id1);
1236
1237 COMMIT;
1238
1239 /* Concurrent Program Request Id for generating Report */
1240
1241 /*Bug No : 6341012. MOAC Uptake. Need to set ORG_ID before submitting request */
1242 Fnd_request.set_org_id(l_org_id);
1243 l_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
1244 'IGC',
1245 'IGCCYRPR',
1246 NULL,
1247 NULL,
1248 FALSE,
1249 /* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1250 l_sob_id,
1251 l_org_id,
1252 p_PROCESS_PHASE,
1253 'Y',
1254 l_REQUEST_ID1);
1255 -----------------------
1256 -- Start of XML Report
1257 -----------------------
1258 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
1259 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
1260 l_lang,
1261 l_terr,
1262 'IGCCYRPR_XML',
1263 'IGC',
1264 'IGCCYRPR_XML' );
1265
1266 l_layout := FND_REQUEST.ADD_LAYOUT(
1267 'IGC',
1268 'IGCCYRPR_XML',
1269 l_lang,
1270 l_terr,
1271 'RTF');
1272
1273 IF l_layout then
1274 Fnd_request.set_org_id(l_org_id);
1275 l_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
1276 'IGC',
1277 'IGCCYRPR_XML',
1278 NULL,
1279 NULL,
1280 FALSE,
1281 l_sob_id,
1282 l_org_id,
1283 p_PROCESS_PHASE,
1284 'Y',
1285 l_REQUEST_ID1);
1286 END IF;
1287 END IF;
1288
1289 --------------------
1290 -- End of XML Report
1291 --------------------
1292
1293 /* End of Concurrent Program Request Id for generating Report */
1294 END IF;
1295
1296 -- ------------------------------------------------------------------------------------
1297 -- Ensure that any exceptions raised are output into the log file to be reported to
1298 -- the user if any are present.
1299 -- ------------------------------------------------------------------------------------
1300
1301 IF (l_budg_status = FALSE)
1302 THEN
1303 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
1304 p_data => l_msg_data );
1305
1306 IF (l_msg_count > 0)
1307 THEN
1308 l_error_text := '';
1309 FOR l_cur IN 1..l_msg_count
1310 LOOP
1311 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
1312
1313 IF (g_excep_level >= g_debug_level) then
1314 fnd_log.string(g_excep_level,l_full_path,l_error_text);
1315 END IF;
1316
1317 fnd_file.put_line (FND_FILE.LOG,
1318 l_error_text);
1319 END LOOP;
1320 END IF;
1321 END IF;
1322
1323 IF (l_usr_msg IS NULL) AND (l_budg_status = FALSE)
1324 THEN
1325 RETCODE := 2;
1326 END IF;
1327
1328 IF (l_budg_status = FALSE)
1329 THEN
1330 RETURN;
1331 END IF;
1332
1333
1334 /* End fix for bug 1576023 */
1335
1336 /******************** ENCUMBRANCE CODE *******************/
1337
1338 SELECT application_id
1339 INTO l_application_id
1340 FROM fnd_application
1341 WHERE application_short_name = 'SQLGL';
1342
1343
1344 l_min_period_num := NULL;
1345
1346 l_max_period_num := NULL;
1347
1348 l_COUNTER := 0;
1349
1350 SELECT min(gp.period_num)
1351 INTO l_min_period_num
1352 FROM gl_period_statuses gps,
1353 gl_periods gp,
1354 gl_sets_of_books gb
1355 WHERE
1356 gb.set_of_books_id = l_sob_id AND /*p_sob_id => l_sob_id by Bug 6341012 */
1357 gp.period_set_name = gb.period_set_name AND
1358 gp.period_type = gb.accounted_period_type AND
1359 gps.set_of_books_id = gb.set_of_books_id AND
1360 gps.period_name = gp.period_name AND
1361 gps.application_id = l_application_id AND
1362 gp.period_year = p_year+1 AND
1363 gp.adjustment_Period_flag = 'N';
1364
1365
1366 SELECT max(gp.period_num)
1367 INTO l_max_period_num
1368 FROM gl_period_statuses gps,
1369 gl_periods gp,
1370 gl_sets_of_books gb
1371 WHERE
1372 gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
1373 gp.period_set_name = gb.period_set_name AND
1374 gp.period_type = gb.accounted_period_type AND
1375 gps.set_of_books_id = gb.set_of_books_id AND
1376 gps.period_name = gp.period_name AND
1377 gps.application_id = l_application_id AND
1378 gp.period_year = p_year AND
1379 gp.adjustment_Period_flag = 'N';
1380
1381
1382
1383 SELECT gps.end_date,gps.end_date INTO
1384 l_yr_end_cr_date,l_yr_end_date
1385 FROM gl_period_statuses gps,
1386 gl_periods gp,
1387 gl_sets_of_books gb
1388 WHERE
1389 gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
1390 gp.period_set_name = gb.period_set_name AND
1391 gp.period_type = gb.accounted_period_type AND
1392 gps.set_of_books_id = gb.set_of_books_id AND
1393 gps.period_name = gp.period_name AND
1394 gps.application_id = l_application_id AND
1395 gp.period_year = p_year AND
1396 gp.period_num = l_max_period_num;
1397
1398
1399 SELECT gps.start_date, gps.start_date INTO
1400 l_yr_end_dr_date, l_yr_start_date_next
1401 FROM gl_period_statuses gps,
1402 gl_periods gp,
1403 gl_sets_of_books gb
1404 WHERE
1405 gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
1406 gp.period_set_name = gb.period_set_name AND
1407 gp.period_type = gb.accounted_period_type AND
1408 gps.set_of_books_id = gb.set_of_books_id AND
1409 gps.period_name = gp.period_name AND
1410 gps.application_id = l_application_id AND
1411 gp.period_year = p_year+1 AND
1412 gp.period_num = l_min_period_num;
1413
1414
1415 SELECT gps.start_date INTO
1416 l_yr_start_date
1417 FROM gl_period_statuses gps,
1418 gl_periods gp,
1419 gl_sets_of_books gb
1420 WHERE
1421 gb.set_of_books_id = l_sob_id AND /* p_sob_id => l_sob_id by Bug 6341012 */
1422 gp.period_set_name = gb.period_set_name AND
1423 gp.period_type = gb.accounted_period_type AND
1424 gps.set_of_books_id = gb.set_of_books_id AND
1425 gps.period_name = gp.period_name AND
1426 gps.application_id = l_application_id AND
1427 gp.period_year = p_year AND
1428 gp.period_num = l_min_period_num;
1429
1430
1431 /******************* END ENCUMBRANCE CODE ************************/
1432
1433
1434 /* Fetching Rows from IGC_CC_HEADERS into IGC_CC_PROCESS_DATA depending upon Parameters */
1435
1436 l_PROCESS_TYPE := 'Y';
1437
1438 DELETE FROM IGC_CC_PROCESS_DATA A
1439 WHERE A.PROCESS_TYPE = 'Y'
1440 AND A.PROCESS_PHASE = 'P'
1441 /* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1442 AND A.ORG_ID = l_org_id
1443 AND A.SET_OF_BOOKS_ID = l_sob_id;
1444
1445 DELETE FROM IGC_CC_PROCESS_EXCEPTIONS B
1446 WHERE B.PROCESS_TYPE = 'Y'
1447 /* Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id */
1448 AND B.ORG_ID = l_org_id
1449 AND B.SET_OF_BOOKS_ID = l_sob_id;
1450
1451 COMMIT;
1452
1453 IF p_PROCESS_PHASE = 'F'
1454 THEN
1455
1456 /* Bug 1866742, Year end not running for 2 consecutive years
1457 -- Commented the following out as it did not make sense.
1458
1459 -- Because the records are left behind with status processed
1460 -- in IGC_CC_PROCESS_DATA, they do not get picked up for subsequent runs.
1461 -- Hence cleaning the table off like is being done for Preliminary mode
1462 -- Bidisha S, 24 July 2001
1463
1464 DELETE FROM IGC_CC_PROCESS_DATA A
1465 WHERE A.PROCESS_TYPE = 'Y'
1466 AND A.PROCESS_PHASE IN ('F','P')
1467 AND ( A.PROCESSED <> 'Y' OR A.PROCESSED IS NULL)
1468 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1469 AND A.ORG_ID = l_org_id
1470 AND A.SET_OF_BOOKS_ID = l_sob_id;
1471
1472 UPDATE IGC_CC_PROCESS_DATA A
1473 SET REQUEST_ID = l_REQUEST_ID1
1474 WHERE A.PROCESS_TYPE = 'Y'
1475 AND A.PROCESS_PHASE = 'F'
1476 AND A.PROCESSED = 'Y'
1477 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1478 AND A.ORG_ID = l_org_id
1479 AND A.SET_OF_BOOKS_ID = l_sob_id;
1480 */
1481
1482
1483 -- Added for 1866742, Bidisha S, 24 July 2001
1484 -- Delete all records that have been processed
1485 -- Updation of request id has been moved below.
1486 DELETE FROM IGC_CC_PROCESS_DATA A
1487 WHERE A.PROCESS_TYPE = 'Y'
1488 AND A.PROCESS_PHASE = 'F'
1489 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1490 AND A.ORG_ID = l_org_id
1491 AND A.SET_OF_BOOKS_ID = l_sob_id
1492 AND A.PROCESSED = 'Y';
1493
1494 END IF;
1495
1496 COMMIT;
1497
1498 OPEN C1( l_yr_start_date,
1499 l_yr_end_date,
1500 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1501 l_sob_id,
1502 l_org_id);
1503
1504 LOOP
1505 FETCH C1 INTO V1;
1506 IF C1%ROWCOUNT = 0
1507 THEN
1508 l_EXCEPTION := NULL;
1509 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CC_EXIST');
1510 FND_MESSAGE.SET_TOKEN('YEP_YEAR',p_YEAR,TRUE);
1511 IF(g_excep_level >= g_debug_level) THEN
1512 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1513 END IF;
1514 l_EXCEPTION := FND_MESSAGE.GET;
1515
1516
1517 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1518 (PROCESS_TYPE,
1519 PROCESS_PHASE,
1520 CC_HEADER_ID,
1521 CC_ACCT_LINE_ID,
1522 CC_DET_PF_LINE_ID,
1523 EXCEPTION_REASON,
1524 ORG_ID,
1525 SET_OF_BOOKS_ID,
1526 REQUEST_ID)
1527 VALUES(
1528 l_PROCESS_TYPE,
1529 p_PROCESS_PHASE,
1530 NULL,
1531 NULL,
1532 NULL,
1533 l_EXCEPTION,
1534 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1535 l_org_id,
1536 l_sob_id,
1537 l_REQUEST_ID1);
1538 l_CONTRACT_COUNTER := 1;
1539
1540 END IF;
1541
1542 EXIT WHEN C1%NOTFOUND;
1543
1544 IF p_PROCESS_PHASE = 'F'
1545 THEN
1546 BEGIN
1547 SELECT CC_HEADER_ID
1548 INTO l_HEADER_ID
1549 FROM IGC_CC_PROCESS_DATA A
1550 WHERE A.CC_HEADER_ID = V1.CC_HEADER_ID
1551 AND A.PROCESS_PHASE = 'F'
1552 AND A.PROCESS_TYPE = 'Y';
1553 -- AND A.REQUEST_ID = l_REQUEST_ID1;
1554
1555 UPDATE IGC_CC_PROCESS_DATA A
1556 SET OLD_APPROVAL_STATUS = V1.CC_APPRVL_STATUS,
1557 REQUEST_ID = l_REQUEST_ID1
1558 WHERE A.CC_HEADER_ID = V1.CC_HEADER_ID
1559 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1560 AND A.ORG_ID = l_org_id
1561 AND A.SET_OF_BOOKS_ID = l_sob_id
1562 AND A.PROCESS_PHASE = 'F'
1563 AND A.PROCESS_TYPE = 'Y';
1564
1565 EXCEPTION
1566 WHEN NO_DATA_FOUND
1567 THEN
1568
1569 INSERT INTO IGC_CC_PROCESS_DATA
1570 (PROCESS_TYPE,
1571 PROCESS_PHASE,
1572 CC_HEADER_ID,
1573 VALIDATION_STATUS,
1574 RESERVATION_STATUS,
1575 PROCESSED,
1576 OLD_APPROVAL_STATUS,
1577 ORG_ID,
1578 SET_OF_BOOKS_ID,
1579 VALIDATE_ONLY,
1580 REQUEST_ID)
1581 VALUES(l_PROCESS_TYPE,
1582 p_PROCESS_PHASE,
1583 V1.CC_HEADER_ID,
1584 NULL,
1585 NULL,
1586 NULL,
1587 V1.CC_APPRVL_STATUS,
1588 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1589 l_org_id,
1590 l_sob_id,
1591 NULL,
1592 l_REQUEST_ID1);
1593 END;
1594 END IF;
1595
1596 IF p_PROCESS_PHASE = 'P'
1597 THEN
1598
1599 INSERT INTO IGC_CC_PROCESS_DATA
1600 (PROCESS_TYPE,
1601 PROCESS_PHASE,
1602 CC_HEADER_ID,
1603 VALIDATION_STATUS,
1604 RESERVATION_STATUS,
1605 PROCESSED,
1606 OLD_APPROVAL_STATUS,
1607 ORG_ID,
1608 SET_OF_BOOKS_ID,
1609 VALIDATE_ONLY,
1610 REQUEST_ID)
1611 VALUES(l_PROCESS_TYPE,
1612 p_PROCESS_PHASE,
1613 V1.CC_HEADER_ID,
1614 NULL,
1615 NULL,
1616 NULL,
1617 V1.CC_APPRVL_STATUS,
1618 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1619 l_org_id,
1620 l_sob_id,
1621 NULL,
1622 l_REQUEST_ID1);
1623 END IF;
1624
1625 END LOOP;
1626 CLOSE C1;
1627 COMMIT;
1628
1629
1630 IF l_CONTRACT_COUNTER = 0 /* IF CONTRACT EXIST IN THAT YEAR */
1631 THEN
1632
1633 /* **** Preliminary Mode *****/
1634
1635 IF p_PROCESS_PHASE = 'P'
1636 THEN OPEN C2;
1637
1638 LOOP
1639
1640 FETCH C2 INTO V2;
1641
1642 EXIT WHEN C2%NOTFOUND;
1643
1644 SELECT CC_TYPE
1645 INTO l_Type
1646 FROM IGC_CC_HEADERS
1647 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
1648
1649 IF l_type = 'S' OR l_Type = 'C'
1650 THEN
1651
1652 /* Validation Phase */
1653
1654 l_RESULT_OF_VALIDATION := IGC_CC_REP_YEP_PVT.VALIDATE_CC( V2.CC_HEADER_ID,
1655 l_PROCESS_TYPE,
1656 p_PROCESS_PHASE,
1657 p_YEAR,
1658 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1659 l_sob_id,
1660 l_org_id,
1661 l_prov_enc_on,
1662 l_REQUEST_ID1);
1663
1664 UPDATE IGC_CC_PROCESS_DATA
1665 SET VALIDATION_STATUS = l_RESULT_OF_VALIDATION
1666 WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
1667 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1668 AND SET_OF_BOOKS_ID = l_sob_id
1669 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
1670 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE
1671 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1;
1672
1673 END IF;
1674
1675 END LOOP;
1676
1677 CLOSE C2;
1678
1679 END IF;
1680
1681 /**** End of Preliminary Mode ****/
1682
1683
1684
1685 /****** Final Mode ******/
1686
1687 IF p_PROCESS_PHASE = 'F'
1688 THEN OPEN C2;
1689 LOOP
1690 FETCH C2 INTO V2;
1691 EXIT WHEN C2%NOTFOUND;
1692
1693 SELECT CC_NUM
1694 INTO l_CC_NUM
1695 FROM IGC_CC_HEADERS
1696 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
1697
1698 l_LOCK_CC_STATUS := IGC_CC_REP_YEP_PVT.LOCK_CC(V2.CC_HEADER_ID);
1699
1700 IF l_LOCK_CC_STATUS = FALSE
1701 THEN
1702 l_EXCEPTION := NULL;
1703 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_HEADER_LOCK');
1704 FND_MESSAGE.SET_TOKEN('NUMBER',l_CC_NUM,TRUE);
1705 IF(g_excep_level >= g_debug_level) THEN
1706 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1707 END IF;
1708 l_EXCEPTION := FND_MESSAGE.GET;
1709
1710
1711 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1712 (PROCESS_TYPE,
1713 PROCESS_PHASE,
1714 CC_HEADER_ID,
1715 CC_ACCT_LINE_ID,
1716 CC_DET_PF_LINE_ID,
1717 EXCEPTION_REASON,
1718 ORG_ID,
1719 SET_OF_BOOKS_ID,
1720 REQUEST_ID)
1721 VALUES(
1722 l_PROCESS_TYPE,
1723 p_PROCESS_PHASE,
1724 V2.CC_HEADER_ID,
1725 NULL,
1726 NULL,
1727 l_EXCEPTION,
1728 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1729 l_org_id,
1730 l_sob_id,
1731 l_REQUEST_ID1);
1732 END IF;
1733
1734
1735 SELECT CC_TYPE
1736 INTO l_Type
1737 FROM IGC_CC_HEADERS
1738 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
1739
1740 IF l_type <> 'C'
1741 THEN
1742
1743 l_LOCK_PO_STATUS := IGC_CC_REP_YEP_PVT.LOCK_PO(V2.CC_HEADER_ID);
1744
1745 IF l_LOCK_PO_STATUS = FALSE
1746 THEN
1747 l_EXCEPTION := NULL;
1748 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_PO_LOCKED');
1749 FND_MESSAGE.SET_TOKEN('NUMBER',l_CC_NUM,TRUE);
1750 IF(g_excep_level >= g_debug_level) THEN
1751 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1752 END IF;
1753 l_EXCEPTION := FND_MESSAGE.GET;
1754
1755 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1756 (PROCESS_TYPE,
1757 PROCESS_PHASE,
1758 CC_HEADER_ID,
1759 CC_ACCT_LINE_ID,
1760 CC_DET_PF_LINE_ID,
1761 EXCEPTION_REASON,
1762 ORG_ID,
1763 SET_OF_BOOKS_ID,
1764 REQUEST_ID)
1765 VALUES(
1766 l_PROCESS_TYPE,
1767 p_PROCESS_PHASE,
1768 V2.CC_HEADER_ID,
1769 NULL,
1770 NULL,
1771 l_EXCEPTION,
1772 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1773 l_org_id,
1774 l_sob_id,
1775 l_REQUEST_ID1);
1776 END IF;
1777 END IF;
1778
1779 IF l_LOCK_CC_STATUS = FALSE OR l_LOCK_PO_STATUS = FALSE
1780 THEN
1781 l_COUNTER := l_COUNTER + 1;
1782
1783 END IF;
1784
1785 END LOOP;
1786
1787 CLOSE C2;
1788
1789
1790 IF l_COUNTER = 0 AND p_PROCESS_PHASE = 'F' /* If LOcks are Successful */
1791 THEN
1792 /* Checking Period Status For CC and GL in FINAL MODE */
1793
1794 OPEN C6;
1795
1796 LOOP
1797 FETCH C6 INTO V6;
1798 EXIT WHEN C6%NOTFOUND;
1799
1800 -- Bug 3464401, Removed hardcoding of period numbers.
1801 -- IF (V6.PERIOD_NUM BETWEEN 1 AND 11)
1802 -- AND V6.CC_PERIOD_STATUS NOT IN ('C','P','N')
1803 IF (V6.PERIOD_NUM < l_max_period_num)
1804 AND V6.CC_PERIOD_STATUS NOT IN ('C','P','N')
1805 THEN
1806 l_EXCEPTION := NULL;
1807 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CC_PERIOD_STATUS');
1808 FND_MESSAGE.SET_TOKEN('PERIOD_NAME',V6.PERIOD_NAME,TRUE);
1809 IF(g_excep_level >= g_debug_level) THEN
1810 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1811 END IF;
1812 l_EXCEPTION := FND_MESSAGE.GET;
1813
1814
1815 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1816 (PROCESS_TYPE,
1817 PROCESS_PHASE,
1818 CC_HEADER_ID,
1819 CC_ACCT_LINE_ID,
1820 CC_DET_PF_LINE_ID,
1821 EXCEPTION_REASON,
1822 ORG_ID,
1823 SET_OF_BOOKS_ID,
1824 REQUEST_ID)
1825 VALUES(
1826 l_PROCESS_TYPE,
1827 p_PROCESS_PHASE,
1828 NULL,
1829 NULL,
1830 NULL,
1831 l_EXCEPTION,
1832 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1833 l_org_id,
1834 l_sob_id,
1835 l_REQUEST_ID1);
1836
1837 l_STATUS_COUNTER := l_STATUS_COUNTER + 1;
1838 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
1839 END IF;
1840
1841 -- Bug 3464401, Removed hardcoding of period numbers.
1842 --IF V6.PERIOD_NUM = 12 AND V6.CC_PERIOD_STATUS <> 'O'
1843 IF V6.PERIOD_NUM = l_max_period_num
1844 AND V6.CC_PERIOD_STATUS <> 'O'
1845 THEN
1846 l_EXCEPTION := NULL;
1847 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CC_LAST_PERIOD');
1848 FND_MESSAGE.SET_TOKEN('PERIOD_NAME',V6.PERIOD_NAME,TRUE);
1849 IF(g_excep_level >= g_debug_level) THEN
1850 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1851 END IF;
1852 l_EXCEPTION := FND_MESSAGE.GET;
1853
1854
1855 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1856 (PROCESS_TYPE,
1857 PROCESS_PHASE,
1858 CC_HEADER_ID,
1859 CC_ACCT_LINE_ID,
1860 CC_DET_PF_LINE_ID,
1861 EXCEPTION_REASON,
1862 ORG_ID,
1863 SET_OF_BOOKS_ID,
1864 REQUEST_ID)
1865 VALUES(
1866 l_PROCESS_TYPE,
1867 p_PROCESS_PHASE,
1868 NULL,
1869 NULL,
1870 NULL,
1871 l_EXCEPTION,
1872 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1873 l_org_id,
1874 l_sob_id,
1875 l_REQUEST_ID1);
1876 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
1877
1878 END IF;
1879
1880 END LOOP;
1881
1882 CLOSE C6;
1883
1884 OPEN C7;
1885 LOOP
1886 FETCH C7 INTO V7;
1887 EXIT WHEN C7%NOTFOUND;
1888 IF C7%ROWCOUNT = 0
1889 THEN
1890 l_EXCEPTION := NULL;
1891 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CC_PERIOD_EXISTS');
1892 IF(g_excep_level >= g_debug_level) THEN
1893 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1894 END IF;
1895 l_EXCEPTION := FND_MESSAGE.GET;
1896
1897
1898 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1899 (PROCESS_TYPE,
1900 PROCESS_PHASE,
1901 CC_HEADER_ID,
1902 CC_ACCT_LINE_ID,
1903 CC_DET_PF_LINE_ID,
1904 EXCEPTION_REASON,
1905 ORG_ID,
1906 SET_OF_BOOKS_ID,
1907 REQUEST_ID)
1908 VALUES(
1909 l_PROCESS_TYPE,
1910 p_PROCESS_PHASE,
1911 NULL,
1912 NULL,
1913 NULL,
1914 l_EXCEPTION,
1915 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1916 l_org_id,
1917 l_sob_id,
1918 l_REQUEST_ID1);
1919
1920 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
1921 EXIT;
1922 END IF;
1923
1924 -- Bug 3464401, Removed hardcoding of period numbers.
1925 --IF V7.PERIOD_NUM = 1 AND V7.CC_PERIOD_STATUS NOT IN ('O','F')
1926 IF V7.PERIOD_NUM = l_min_period_num
1927 AND V7.CC_PERIOD_STATUS NOT IN ('O','F')
1928 THEN
1929 l_EXCEPTION := NULL;
1930 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_CC_FUTURE_PERIOD');
1931 FND_MESSAGE.SET_TOKEN('PERIOD_NAME',V7.PERIOD_NAME,TRUE);
1932 IF(g_excep_level >= g_debug_level) THEN
1933 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1934 END IF;
1935 l_EXCEPTION := FND_MESSAGE.GET;
1936
1937
1938 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1939 (PROCESS_TYPE,
1940 PROCESS_PHASE,
1941 CC_HEADER_ID,
1942 CC_ACCT_LINE_ID,
1943 CC_DET_PF_LINE_ID,
1944 EXCEPTION_REASON,
1945 ORG_ID,
1946 SET_OF_BOOKS_ID,
1947 REQUEST_ID)
1948 VALUES(
1949 l_PROCESS_TYPE,
1950 p_PROCESS_PHASE,
1951 NULL,
1952 NULL,
1953 NULL,
1954 l_EXCEPTION,
1955 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1956 l_org_id,
1957 l_sob_id,
1958 l_REQUEST_ID1);
1959 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
1960 END IF;
1961
1962 END LOOP;
1963 CLOSE C7;
1964
1965 -- Added parameters into C8 for Bug 3464401
1966 -- Fetch the details of the first period in the new year
1967 OPEN C8 (p_year + 1,
1968 l_min_period_num);
1969 FETCH C8 INTO V8;
1970 IF C8%ROWCOUNT = 0
1971 THEN
1972 l_EXCEPTION := NULL;
1973 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_GL_PERIOD_EXISTS');
1974 IF(g_excep_level >= g_debug_level) THEN
1975 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1976 END IF;
1977 l_EXCEPTION := FND_MESSAGE.GET;
1978
1979
1980 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
1981 (PROCESS_TYPE,
1982 PROCESS_PHASE,
1983 CC_HEADER_ID,
1984 CC_ACCT_LINE_ID,
1985 CC_DET_PF_LINE_ID,
1986 EXCEPTION_REASON,
1987 ORG_ID,
1988 SET_OF_BOOKS_ID,
1989 REQUEST_ID)
1990 VALUES(
1991 l_PROCESS_TYPE,
1992 p_PROCESS_PHASE,
1993 NULL,
1994 NULL,
1995 NULL,
1996 l_EXCEPTION,
1997 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
1998 l_org_id,
1999 l_sob_id,
2000 l_REQUEST_ID1);
2001 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
2002 END IF;
2003
2004 IF V8.CLOSING_STATUS NOT IN ('F','O')
2005 THEN
2006 l_EXCEPTION := NULL;
2007 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_GL_FIRST_PERIOD');
2008 FND_MESSAGE.SET_TOKEN('PERIOD_NAME',V8.PERIOD_NAME,TRUE);
2009 IF(g_excep_level >= g_debug_level) THEN
2010 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
2011 END IF;
2012 l_EXCEPTION := FND_MESSAGE.GET;
2013
2014 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2015 (PROCESS_TYPE,
2016 PROCESS_PHASE,
2017 CC_HEADER_ID,
2018 CC_ACCT_LINE_ID,
2019 CC_DET_PF_LINE_ID,
2020 EXCEPTION_REASON,
2021 ORG_ID,
2022 SET_OF_BOOKS_ID,
2023 REQUEST_ID)
2024 VALUES(
2025 l_PROCESS_TYPE,
2026 p_PROCESS_PHASE,
2027 NULL,
2028 NULL,
2029 NULL,
2030 l_EXCEPTION,
2031 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2032 l_org_id,
2033 l_sob_id,
2034 l_REQUEST_ID1);
2035 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
2036 END IF;
2037
2038 CLOSE C8;
2039
2040 -- Added parameters into C8 for Bug 3464401
2041 -- Fetch the details of the last period in the current year
2042 -- With the parameters being used, the cursor C9 is
2043 -- redundant and C8 can be used
2044 OPEN C8 (p_year,
2045 l_max_period_num);
2046 -- OPEN C9;
2047 FETCH C8 INTO V8;
2048 IF C8%ROWCOUNT = 0
2049 THEN
2050 l_EXCEPTION := NULL;
2051 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_GL_LAST_PRD_EXISTS');
2052 IF(g_excep_level >= g_debug_level) THEN
2053 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
2054 END IF;
2055 l_EXCEPTION := FND_MESSAGE.GET;
2056
2057 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2058 (PROCESS_TYPE,
2059 PROCESS_PHASE,
2060 CC_HEADER_ID,
2061 CC_ACCT_LINE_ID,
2062 CC_DET_PF_LINE_ID,
2063 EXCEPTION_REASON,
2064 ORG_ID,
2065 SET_OF_BOOKS_ID,
2066 REQUEST_ID)
2067 VALUES(
2068 l_PROCESS_TYPE,
2069 p_PROCESS_PHASE,
2070 NULL,
2071 NULL,
2072 NULL,
2073 l_EXCEPTION,
2074 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2075 l_org_id,
2076 l_sob_id,
2077 l_REQUEST_ID1);
2078 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
2079 END IF;
2080
2081 IF V8.CLOSING_STATUS <> 'O'
2082 THEN
2083 l_EXCEPTION := NULL;
2084 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_GL_LAST_PERIOD');
2085 FND_MESSAGE.SET_TOKEN('PERIOD_NAME',V8.PERIOD_NAME,TRUE);
2086 IF(g_excep_level >= g_debug_level) THEN
2087 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
2088 END IF;
2089 l_EXCEPTION := FND_MESSAGE.GET;
2090
2091 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2092 (PROCESS_TYPE,
2093 PROCESS_PHASE,
2094 CC_HEADER_ID,
2095 CC_ACCT_LINE_ID,
2096 CC_DET_PF_LINE_ID,
2097 EXCEPTION_REASON,
2098 ORG_ID,
2099 SET_OF_BOOKS_ID,
2100 REQUEST_ID)
2101 VALUES(
2102 l_PROCESS_TYPE,
2103 p_PROCESS_PHASE,
2104 NULL,
2105 NULL,
2106 NULL,
2107 l_EXCEPTION,
2108 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2109 l_org_id,
2110 l_sob_id,
2111 l_REQUEST_ID1);
2112 l_PERIOD_COUNTER := l_PERIOD_COUNTER + 1;
2113 END IF;
2114
2115 CLOSE C8;
2116
2117 /* End of Final Mode PERIOD_STATUS Checking */
2118
2119
2120 IF l_PERIOD_COUNTER = 0
2121 THEN
2122 OPEN C2;
2123 LOOP
2124 FETCH C2 INTO V2;
2125 EXIT WHEN C2%NOTFOUND;
2126
2127 SELECT CC_TYPE,CC_STATE,CC_APPRVL_STATUS
2128 INTO l_TYPE,l_STATE,l_PREVIOUS_APPRVL_STATUS
2129 FROM IGC_CC_HEADERS
2130 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2131
2132 IF l_type = 'S' OR l_Type = 'C'
2133 THEN
2134
2135 /* Validation Phase for Final MOde */
2136
2137 l_RESULT_OF_VALIDATION := IGC_CC_REP_YEP_PVT.VALIDATE_CC(
2138 V2.CC_HEADER_ID,
2139 l_PROCESS_TYPE,
2140 p_PROCESS_PHASE,
2141 p_YEAR,
2142 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2143 l_sob_id,
2144 l_org_id,
2145 l_prov_enc_on,
2146 l_REQUEST_ID1);
2147
2148 IF l_RESULT_OF_VALIDATION = 'P'
2149 THEN
2150 UPDATE IGC_CC_PROCESS_DATA A
2151 SET VALIDATION_STATUS = 'P'
2152 WHERE A.CC_HEADER_ID = V2.CC_HEADER_ID
2153 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2154 AND A.ORG_ID = l_org_id
2155 AND A.SET_OF_BOOKS_ID = l_sob_id
2156 AND A.REQUEST_ID = l_REQUEST_ID1
2157 AND A.PROCESS_TYPE = l_PROCESS_TYPE;
2158
2159
2160 /* Changing IGC_CC_HEADERS.APPRVL_STATUS => IN PROCESS */
2161
2162
2163 UPDATE IGC_CC_HEADERS
2164 SET CC_APPRVL_STATUS = 'IP'
2165 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V2.CC_HEADER_ID;
2166
2167 IF l_TYPE = 'S' AND l_STATE = 'CM' AND l_PREVIOUS_APPRVL_STATUS = 'AP'
2168 THEN
2169
2170 BEGIN
2171 SELECT 'Y'
2172 INTO l_DUMMY
2173 FROM PO_HEADERS_ALL A
2174 WHERE
2175 A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
2176 FROM IGC_CC_HEADERS B,
2177 PO_HEADERS_ALL C
2178 WHERE B.ORG_ID = C.ORG_ID AND
2179 B.CC_NUM = C.SEGMENT1 AND
2180 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
2181 B.CC_HEADER_ID = V2.CC_HEADER_ID );
2182
2183 UPDATE PO_HEADERS_ALL
2184 SET APPROVED_FLAG = 'N'
2185 WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN ( SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
2186 FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
2187 WHERE a.SEGMENT1 = b.CC_NUM
2188 AND a.ORG_ID = b.ORG_ID
2189 AND a.TYPE_LOOKUP_CODE = 'STANDARD'
2190 AND b.CC_HEADER_ID = V2.CC_HEADER_ID);
2191
2192 EXCEPTION
2193 WHEN NO_DATA_FOUND
2194 THEN
2195 NULL;
2196 END;
2197 END IF;
2198
2199 END IF;
2200
2201 IF l_RESULT_OF_VALIDATION = 'F'
2202 THEN
2203 UPDATE IGC_CC_PROCESS_DATA
2204 SET VALIDATION_STATUS = 'F'
2205 WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
2206 AND REQUEST_ID = l_REQUEST_ID1;
2207 END IF;
2208
2209 END IF;
2210
2211 END LOOP;
2212
2213 CLOSE C2;
2214
2215 -- COMMIT; /* COMMIT Releases Database Lock on Contract Commitments and POs */
2216 -- Comented out COMMIT. Locks should not be released untill the end of the process.
2217 -- This is to make sure no other process updates cc_headers while this process is running
2218 -- Bidisha S , 1825957
2219
2220
2221 /*
2222 -- Bug 1886713, Process should continue even if CC's have failed validation
2223 -- CC's which have failed validation should of course not be processed.
2224 -- Bidisha S, 23 July 2001
2225
2226 -- check for all Contract Commitments get passed through Validation Phase
2227 SELECT COUNT(*)
2228 INTO l_VALIDATION_COUNTER
2229 FROM IGC_CC_PROCESS_DATA
2230 WHERE VALIDATION_STATUS = 'F'
2231 AND REQUEST_ID = l_REQUEST_ID1
2232 AND PROCESS_TYPE = 'Y'
2233 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2234 AND SET_OF_BOOKS_ID = l_sob_id
2235 AND ORG_ID = l_org_id;
2236
2237
2238 IF l_VALIDATION_COUNTER = 0
2239 THEN
2240
2241 */
2242 OPEN C2;
2243 LOOP
2244 FETCH C2 INTO V2;
2245 EXIT WHEN C2%NOTFOUND;
2246
2247 -- Check if the CC has passed validation.
2248 -- Bug 1866713
2249
2250 l_invalid_counter := 0;
2251
2252 SELECT COUNT(*)
2253 INTO l_invalid_counter
2254 FROM IGC_CC_PROCESS_DATA
2255 WHERE validation_status = 'F'
2256 AND request_id = l_REQUEST_ID1
2257 AND process_type = 'Y'
2258 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2259 AND set_of_books_id = l_sob_id
2260 AND org_id = l_org_id
2261 AND cc_header_id = V2.cc_header_id;
2262
2263
2264 IF l_invalid_counter = 0
2265 THEN
2266 SELECT CC_STATE
2267 INTO l_STATE
2268 FROM IGC_CC_HEADERS
2269 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2270
2271 /* Reservation_Phase OR Encumberence Check */
2272
2273 IF ( (l_State = 'PR' OR l_State = 'CL' )
2274 AND l_sbc_on = TRUE AND l_prov_enc_on = TRUE
2275 )
2276 OR
2277 ( (l_State = 'CM' OR l_State = 'CT' )
2278 AND l_sbc_on = TRUE AND l_conf_enc_on = TRUE
2279 )
2280 THEN
2281
2282
2283 l_RESULT_OF_RESERVATION := IGC_CC_REP_YEP_PVT.Encumber_CC(
2284 p_process_type => 'Y',
2285 p_cc_header_id => V2.CC_HEADER_ID,
2286 p_sbc_on => l_sbc_on,
2287 p_cbc_on => l_cbc_on,
2288 /*Bug No : 6341012. R12 SLA Uptake*/
2289 -- p_cc_prov_enc_type_id => l_cc_prov_enc_type_id,
2290 -- p_cc_conf_enc_type_id => l_cc_conf_enc_type_id,
2291 -- p_req_encumbrance_type_id => l_req_encumbrance_type_id,
2292 -- p_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
2293 p_currency_code => l_currency_code,
2294 p_yr_start_date => l_yr_start_date,
2295 p_yr_end_date => l_yr_end_date,
2296 p_yr_end_cr_date => l_yr_end_cr_date,
2297 p_yr_end_dr_date => l_yr_end_dr_date,
2298 p_rate_date => NULL,
2299 p_rate => NULL,
2300 p_revalue_fix_date => NULL );
2301 ELSE
2302 l_result_of_reservation := 'P';
2303 END IF;
2304
2305 IF l_RESULT_OF_RESERVATION = 'P'
2306 THEN
2307 UPDATE IGC_CC_PROCESS_DATA
2308 SET RESERVATION_STATUS ='P'
2309 WHERE IGC_CC_PROCESS_DATA.CC_HEADER_ID = V2.CC_HEADER_ID
2310 AND REQUEST_ID = l_REQUEST_ID1;
2311
2312
2313 SELECT CC_TYPE
2314 INTO l_CC_TYPE
2315 FROM IGC_CC_HEADERS
2316 WHERE IGC_CC_HEADERS.CC_HEADER_ID = V2.CC_HEADER_ID;
2317
2318
2319 /* Perform Year End Processing on Contract Commitment */
2320
2321
2322 IF l_CC_TYPE = 'S' /* Year End processing for Standard Contracts */
2323
2324 THEN
2325 l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V2.CC_HEADER_ID,
2326 p_YEAR,
2327 -- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
2328 l_sob_id,
2329 l_REQUEST_ID1,
2330 l_yr_start_date,
2331 l_yr_end_date,
2332 l_sbc_on,
2333 l_cbc_on,
2334 l_prov_enc_on,
2335 l_conf_enc_on);
2336
2337
2338
2339
2340 IF l_RESULT_OF_YEAR_END_UPDATE = 'Y'
2341 THEN
2342 UPDATE IGC_CC_PROCESS_DATA
2343 SET PROCESSED = 'Y'
2344 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2345 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2346 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2347 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2348 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2349 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2350
2351 UPDATE IGC_CC_HEADERS
2352 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2353 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2354
2355
2356 SELECT CC_STATE
2357 INTO l_STATE
2358 FROM IGC_CC_HEADERS
2359 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2360
2361
2362 IF l_STATE = 'CM'
2363 THEN
2364
2365 BEGIN
2366 SELECT 'Y'
2367 INTO l_DUMMY
2368 FROM PO_HEADERS_ALL A
2369 WHERE
2370 A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
2371 FROM IGC_CC_HEADERS B,
2372 PO_HEADERS_ALL C
2373 WHERE B.ORG_ID = C.ORG_ID AND
2374 B.CC_NUM = C.SEGMENT1 AND
2375 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
2376 B.CC_HEADER_ID = V2.CC_HEADER_ID );
2377
2378 IGC_CC_PO_INTERFACE_PKG.convert_cc_to_po
2379 ( 1.0,
2380 FND_API.G_FALSE,
2381 FND_API.G_FALSE,
2382 FND_API.G_VALID_LEVEL_FULL,
2383 l_return_status,
2384 l_msg_count,
2385 l_msg_data,
2386 V2.CC_HEADER_ID);
2387
2388 EXCEPTION
2389 WHEN NO_DATA_FOUND
2390 THEN
2391 NULL;
2392 END;
2393 END IF;
2394
2395 ELSE
2396 UPDATE IGC_CC_PROCESS_DATA
2397 SET PROCESSED = 'N',
2398 VALIDATION_STATUS = 'F',
2399 RESERVATION_STATUS = 'F'
2400 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2401 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2402 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2403 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2404 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2405 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE ;
2406
2407 UPDATE IGC_CC_HEADERS
2408 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2409 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2410 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2411 AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
2412 AND IGC_CC_HEADERS.ORG_ID = l_org_id;
2413
2414 END IF;
2415 END IF;
2416
2417 IF l_CC_TYPE = 'C' /* Year End processing for Cover Contracts */
2418
2419 THEN
2420 l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V2.CC_HEADER_ID,
2421 p_YEAR,
2422 -- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
2423 l_sob_id,
2424 l_REQUEST_ID1,
2425 l_yr_start_date,
2426 l_yr_end_date,
2427 l_sbc_on,
2428 l_cbc_on,
2429 l_prov_enc_on,
2430 l_conf_enc_on);
2431
2432
2433 IF l_RESULT_OF_YEAR_END_UPDATE = 'Y'
2434 THEN
2435 UPDATE IGC_CC_PROCESS_DATA
2436 SET PROCESSED = 'Y'
2437 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2438 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2439 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2440 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2441 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2442 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2443
2444 OPEN C11(V2.CC_HEADER_ID);
2445
2446 RELEASE_YEAR_END_COUNTER := 0;
2447
2448 LOOP
2449
2450 FETCH C11 INTO V11;
2451 EXIT WHEN C11%NOTFOUND;
2452
2453 l_RESULT_OF_YEAR_END_UPDATE := YEAR_END_UPDATE(V11.CC_HEADER_ID,
2454 p_YEAR,
2455 -- Bug No : 6341012. MOAC Uptake. p_sob_id is changed to l_sob_id
2456 l_sob_id,
2457 l_REQUEST_ID1,
2458 l_yr_start_date,
2459 l_yr_end_date,
2460 l_sbc_on,
2461 l_cbc_on,
2462 l_prov_enc_on,
2463 l_conf_enc_on);
2464 IF l_RESULT_OF_YEAR_END_UPDATE = 'N'
2465 THEN
2466 RELEASE_YEAR_END_COUNTER := RELEASE_YEAR_END_COUNTER +1;
2467 END IF;
2468
2469 UPDATE IGC_CC_PROCESS_DATA
2470 SET PROCESSED = l_RESULT_OF_YEAR_END_UPDATE
2471 WHERE CC_HEADER_ID = V11.CC_HEADER_ID
2472 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2473 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2474 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2475 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2476 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2477
2478 END LOOP;
2479 CLOSE C11;
2480
2481 IF RELEASE_YEAR_END_COUNTER > 0
2482 THEN
2483 UPDATE IGC_CC_PROCESS_DATA
2484 SET PROCESSED = 'N'
2485 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2486 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2487 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2488 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2489 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2490 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2491
2492 l_EXCEPTION := NULL;
2493 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_COVER_REL_INSERT');
2494 IF(g_excep_level >= g_debug_level) THEN
2495 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
2496 END IF;
2497 l_EXCEPTION := FND_MESSAGE.GET;
2498
2499
2500 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2501 (PROCESS_TYPE,
2502 PROCESS_PHASE,
2503 CC_HEADER_ID,
2504 CC_ACCT_LINE_ID,
2505 CC_DET_PF_LINE_ID,
2506 EXCEPTION_REASON,
2507 ORG_ID,
2508 SET_OF_BOOKS_ID,
2509 REQUEST_ID)
2510 VALUES(
2511 l_PROCESS_TYPE,
2512 p_PROCESS_PHASE,
2513 V2.CC_HEADER_ID,
2514 NULL,
2515 NULL,
2516 l_EXCEPTION,
2517 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2518 l_org_id,
2519 l_sob_id,
2520 l_REQUEST_ID1);
2521
2522 ELSE
2523 OPEN C11(V2.CC_HEADER_ID);
2524 LOOP
2525 FETCH C11 INTO V11;
2526 EXIT WHEN C11%NOTFOUND;
2527
2528 UPDATE IGC_CC_HEADERS
2529 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2530 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2531
2532 SELECT CC_STATE
2533 INTO l_STATE
2534 FROM IGC_CC_HEADERS
2535 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2536
2537
2538 IF l_STATE = 'CM'
2539 THEN
2540
2541 BEGIN
2542 SELECT 'Y'
2543 INTO l_DUMMY
2544 FROM PO_HEADERS_ALL A
2545 WHERE
2546 A.PO_HEADER_ID =
2547 (SELECT C.PO_HEADER_ID
2548 FROM IGC_CC_HEADERS B,
2549 PO_HEADERS_ALL C
2550 WHERE B.ORG_ID = C.ORG_ID AND
2551 B.CC_NUM = C.SEGMENT1 AND
2552 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
2553 B.CC_HEADER_ID = V11.CC_HEADER_ID );
2554
2555 IGC_CC_PO_INTERFACE_PKG.convert_cc_to_po
2556 ( 1.0,
2557 FND_API.G_FALSE,
2558 FND_API.G_FALSE,
2559 FND_API.G_VALID_LEVEL_FULL,
2560 l_return_status,
2561 l_msg_count,
2562 l_msg_data,
2563 V11.CC_HEADER_ID);
2564
2565 EXCEPTION
2566 WHEN NO_DATA_FOUND
2567 THEN
2568 NULL;
2569 END;
2570 END IF;
2571 END LOOP;
2572 CLOSE C11;
2573 END IF;
2574
2575
2576 ELSE UPDATE IGC_CC_PROCESS_DATA
2577 SET PROCESSED = 'N',
2578 VALIDATION_STATUS = 'F',
2579 RESERVATION_STATUS = 'F'
2580 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2581 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2582 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2583 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2584 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2585 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2586
2587
2588 UPDATE IGC_CC_HEADERS
2589 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2590 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2591 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2592 AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
2593 AND IGC_CC_HEADERS.ORG_ID = l_org_id;
2594
2595 OPEN C11(V2.CC_HEADER_ID);
2596 LOOP
2597
2598 FETCH C11 INTO V11;
2599 EXIT WHEN C11%NOTFOUND;
2600
2601 UPDATE IGC_CC_PROCESS_DATA
2602 SET PROCESSED = 'N'
2603 WHERE CC_HEADER_ID = V11.CC_HEADER_ID
2604 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2605 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2606 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2607 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2608 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2609
2610 END LOOP;
2611 CLOSE C11;
2612 END IF;
2613 END IF;
2614 ELSE
2615
2616 SELECT CC_NUM
2617 INTO l_CC_NUM
2618 FROM IGC_CC_HEADERS
2619 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2620
2621 l_EXCEPTION := NULL;
2622 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_VAL_DUE_TO_ENC_FAIL');
2623 FND_MESSAGE.SET_TOKEN('NUMBER',l_CC_NUM,TRUE);
2624 IF(g_excep_level >= g_debug_level) THEN
2625 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
2626 END IF;
2627 l_EXCEPTION := FND_MESSAGE.GET;
2628
2629 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2630 (PROCESS_TYPE,
2631 PROCESS_PHASE,
2632 CC_HEADER_ID,
2633 CC_ACCT_LINE_ID,
2634 CC_DET_PF_LINE_ID,
2635 EXCEPTION_REASON,
2636 ORG_ID,
2637 SET_OF_BOOKS_ID,
2638 REQUEST_ID)
2639 VALUES(
2640 l_PROCESS_TYPE ,
2641 p_PROCESS_PHASE,
2642 V2.CC_HEADER_ID,
2643 NULL,
2644 NULL,
2645 l_EXCEPTION,
2646 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2647 l_org_id,
2648 l_sob_id,
2649 l_REQUEST_ID1);
2650
2651
2652 UPDATE IGC_CC_PROCESS_DATA
2653 SET VALIDATION_STATUS = 'F',
2654 PROCESSED = 'N',
2655 RESERVATION_STATUS = 'F'
2656 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2657 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2658 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2659 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2660 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2661 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE;
2662
2663 END IF;
2664
2665 ELSE -- CC has not passed validation, Bug 1866713
2666
2667 UPDATE IGC_CC_PROCESS_DATA
2668 SET PROCESSED = 'N',
2669 VALIDATION_STATUS = 'F',
2670 RESERVATION_STATUS = 'F'
2671 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2672 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2673 AND IGC_CC_PROCESS_DATA.SET_OF_BOOKS_ID = l_sob_id
2674 AND IGC_CC_PROCESS_DATA.REQUEST_ID = l_REQUEST_ID1
2675 AND IGC_CC_PROCESS_DATA.ORG_ID = l_org_id
2676 AND IGC_CC_PROCESS_DATA.PROCESS_TYPE = l_PROCESS_TYPE ;
2677
2678 -- bug 2043221 ssmales - bug found during testing of topic 46 - approval status needs resetting
2679 -- update added below to process this
2680
2681 UPDATE IGC_CC_HEADERS
2682 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2683 WHERE CC_HEADER_ID = V2.CC_HEADER_ID
2684 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2685 AND IGC_CC_HEADERS.SET_OF_BOOKS_ID = l_sob_id
2686 AND IGC_CC_HEADERS.ORG_ID = l_org_id ;
2687
2688 END IF; -- CC has passed validation , Bug 1866713
2689
2690 END LOOP; -- C2
2691
2692 CLOSE C2;
2693
2694
2695 /*
2696 -- Bug 1866713
2697 -- Processing should continue in final mode even if a CC has failed validation.
2698 ELSE
2699 l_EXCEPTION := NULL;
2700 FND_MESSAGE.SET_NAME('IGC','IGC_CC_YEP_VALIDATE_ALL');
2701 l_EXCEPTION := FND_MESSAGE.GET;
2702
2703
2704 INSERT INTO IGC_CC_PROCESS_EXCEPTIONS
2705 (PROCESS_TYPE,
2706 PROCESS_PHASE,
2707 CC_HEADER_ID,
2708 CC_ACCT_LINE_ID,
2709 CC_DET_PF_LINE_ID,
2710 EXCEPTION_REASON,
2711 ORG_ID,
2712 SET_OF_BOOKS_ID,
2713 REQUEST_ID)
2714 VALUES(
2715 l_PROCESS_TYPE,
2716 p_PROCESS_PHASE,
2717 NULL,
2718 NULL,
2719 NULL,
2720 l_EXCEPTION,
2721 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2722 l_org_id,
2723 l_sob_id,
2724 l_REQUEST_ID1);
2725 END IF;
2726 */ -- 1866713 , End
2727
2728 /* Reset the Values in IGC_CC_HEADERS AND PO_HEADERS_ALL When all Contracts are processed */
2729 l_PROCESSED_COUNTER := 0;
2730
2731 SELECT COUNT(*)
2732 INTO l_PROCESSED_COUNTER
2733 FROM IGC_CC_PROCESS_DATA
2734 WHERE PROCESSED = 'N'
2735 AND PROCESS_TYPE = l_PROCESS_TYPE
2736 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2737 AND ORG_ID = l_org_id
2738 AND REQUEST_ID = l_REQUEST_ID1
2739 AND SET_OF_BOOKS_ID = l_sob_id ;
2740
2741 -- IF l_PROCESSED_COUNTER = 0
2742 -- Bidisha S, should the check not be count > 0
2743 -- Cause if all records have been processed, then cursor C2 will not return any rows anyway.
2744
2745 IF l_PROCESSED_COUNTER > 0
2746 THEN OPEN C2;
2747 LOOP
2748 FETCH C2 INTO V2;
2749 EXIT WHEN C2%NOTFOUND;
2750
2751 /* Restoring Original Values to IGC_CC_HAEDERS */
2752
2753 UPDATE IGC_CC_HEADERS
2754 SET CC_APPRVL_STATUS = V2.OLD_APPROVAL_STATUS
2755 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2756
2757 /* Fetching value for PO_HEADERS_ALL => APPROVED_FLAG from IGC_CC_HEADERS => CC_CTRL_STATUS */
2758
2759 SELECT CC_CTRL_STATUS
2760 INTO l_CC_CTRL_STATUS
2761 FROM IGC_CC_HEADERS
2762 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2763
2764 IF l_CC_CTRL_STATUS = 'O'
2765 THEN
2766 l_APPROVED_FLAG := 'Y';
2767 ELSE
2768 l_APPROVED_FLAG := 'N';
2769 END IF;
2770
2771 /* Restoring Original Values to PO_HEADERS_ALL */
2772
2773 SELECT CC_TYPE,CC_STATE,CC_APPRVL_STATUS
2774 INTO l_TYPE,l_STATE,l_PREVIOUS_APPRVL_STATUS
2775 FROM IGC_CC_HEADERS
2776 WHERE CC_HEADER_ID = V2.CC_HEADER_ID;
2777
2778 IF (l_TYPE = 'S' OR l_TYPE = 'R') AND l_STATE = 'CM' AND l_PREVIOUS_APPRVL_STATUS = 'AP'
2779 THEN
2780
2781 BEGIN
2782 SELECT 'Y'
2783 INTO l_DUMMY
2784 FROM PO_HEADERS_ALL A
2785 WHERE
2786 A.PO_HEADER_ID = (SELECT C.PO_HEADER_ID
2787 FROM IGC_CC_HEADERS B,
2788 PO_HEADERS_ALL C
2789 WHERE B.ORG_ID = C.ORG_ID AND
2790 B.CC_NUM = C.SEGMENT1 AND
2791 C.TYPE_LOOKUP_CODE = 'STANDARD' AND
2792 B.CC_HEADER_ID = V2.CC_HEADER_ID );
2793
2794 UPDATE PO_HEADERS_ALL
2795 SET APPROVED_FLAG = l_APPROVED_FLAG
2796 WHERE (SEGMENT1,ORG_ID,TYPE_LOOKUP_CODE) IN ( SELECT SEGMENT1,a.ORG_ID,TYPE_LOOKUP_CODE
2797 FROM PO_HEADERS_ALL a, IGC_CC_HEADERS b
2798 WHERE a.SEGMENT1 = b.CC_NUM
2799 AND a.ORG_ID = b.ORG_ID
2800 AND a.TYPE_LOOKUP_CODE = 'STANDARD'
2801 AND b.CC_HEADER_ID = V2.CC_HEADER_ID);
2802 EXCEPTION
2803 WHEN NO_DATA_FOUND
2804 THEN
2805 NULL;
2806 END;
2807 END IF;
2808 END LOOP;
2809 CLOSE C2;
2810
2811 END IF; /* END IF of Processed_Counter */
2812
2813 END IF; /* END IF of Period_counter */
2814
2815 COMMIT;
2816 END IF; /* End of CC and GL Period Counter */
2817 END IF; /* End of Lock CC and Lock PO Counter */
2818 END IF; /* End of Final Mode IF statement */
2819
2820
2821 /* Concurrent Program Request Id for generating Report */
2822
2823 /*Bug No : 6341012. MOAC Uptake. Set ORG_ID before submitting request */
2824 Fnd_request.set_org_id(l_org_id);
2825 l_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
2826 'IGC',
2827 'IGCCYRPR',
2828 NULL,
2829 NULL,
2830 FALSE,
2831 -- Bug No : 6341012. MOAC Uptake. p_sob_id,p_org_id are changed to l_sob_id,l_org_id
2832 l_sob_id,
2833 l_org_id,
2834 p_PROCESS_PHASE,
2835 l_PROCESS_TYPE,
2836 l_REQUEST_ID1);
2837 -----------------------
2838 -- Start of XML Report
2839 -----------------------
2840 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2841 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2842 l_lang,
2843 l_terr,
2844 'IGCCYRPR_XML',
2845 'IGC',
2846 'IGCCYRPR_XML' );
2847
2848 l_layout := FND_REQUEST.ADD_LAYOUT(
2849 'IGC',
2850 'IGCCYRPR_XML',
2851 l_lang,
2852 l_terr,
2853 'RTF');
2854
2855 IF l_layout then
2856 Fnd_request.set_org_id(l_org_id);
2857 l_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(
2858 'IGC',
2859 'IGCCYRPR_XML',
2860 NULL,
2861 NULL,
2862 FALSE,
2863 l_sob_id,
2864 l_org_id,
2865 p_PROCESS_PHASE,
2866 'Y',
2867 l_REQUEST_ID1);
2868 END IF;
2869 END IF;
2870
2871 --------------------
2872 -- End of XML Report
2873 --------------------
2874
2875 /* End of Concurrent Program Request Id for generating Report */
2876
2877 -- ------------------------------------------------------------------------------------
2878 -- Ensure that any exceptions raised are output into the log file to be reported to
2879 -- the user if any are present.
2880 -- ------------------------------------------------------------------------------------
2881 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2882 p_data => l_msg_data );
2883
2884 IF (l_msg_count > 0) THEN
2885 l_error_text := '';
2886 FOR l_cur IN 1..l_msg_count LOOP
2887 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2888
2889 IF (g_excep_level >= g_debug_level) then
2890 fnd_log.string(g_excep_level,l_full_path,l_error_text);
2891 END IF;
2892
2893 fnd_file.put_line (FND_FILE.LOG,
2894 l_error_text);
2895 END LOOP;
2896 END IF;
2897
2898 RETURN;
2899
2900 EXCEPTION
2901 WHEN OTHERS THEN
2902 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2903 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'YEAR_END_MAIN');
2904 END IF;
2905 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2906 p_data => l_msg_data );
2907 IF (l_msg_count > 0) THEN
2908
2909 l_error_text := '';
2910 FOR l_cur IN 1..l_msg_count LOOP
2911 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2912
2913 IF (g_excep_level >= g_debug_level) then
2914
2915 fnd_log.string(g_excep_level,l_full_path,l_error_text);
2916 END IF;
2917
2918 fnd_file.put_line(fnd_file.log,l_error_text);
2919 END LOOP;
2920 ELSE
2921 l_error_text := 'Error Returned but Error stack has no data';
2922
2923 IF (g_excep_level >= g_debug_level) then
2924
2925 fnd_log.string(g_excep_level,l_full_path,l_error_text);
2926 END IF;
2927
2928 fnd_file.put_line(fnd_file.log,l_error_text);
2929 END IF;
2930 IF ( g_unexp_level >= g_debug_level ) THEN
2931 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2932 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
2933 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
2934 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
2935 END IF;
2936
2937 END YEAR_END_MAIN;
2938
2939
2940 /*==================================================================================
2941 End of YEAR_END_MAIN Procedure
2942 =================================================================================*/
2943
2944 END IGC_CC_YEP_PROCESS_PKG;