[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_COMPLETE_COVER_PKG
Source
1 PACKAGE BODY IGC_CC_COMPLETE_COVER_PKG AS
2 -- $Header: IGCCCOVB.pls 120.14.12010000.3 2008/11/19 09:35:23 schakkin ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_COMPLETE_COVER_PKG';
5
6 --g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
7 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8 --following variables added for bug 3199488: fnd logging changes: sdixit
9 g_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10 g_state_level number := FND_LOG.LEVEL_STATEMENT;
11 g_proc_level number := FND_LOG.LEVEL_PROCEDURE;
12 g_event_level number := FND_LOG.LEVEL_EVENT;
13 g_excep_level number := FND_LOG.LEVEL_EXCEPTION;
14 g_error_level number := FND_LOG.LEVEL_ERROR;
15 g_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
16 g_path varchar2(500) := 'igc.plsql.igcccovb.igc_cc_complete_cover_pkg.';
17
18 Procedure complete_cover ( errbuf OUT NOCOPY VARCHAR2
19 ,retcode OUT NOCOPY NUMBER
20 ,p_cc_header_id IN IGC_CC_HEADERS.CC_HEADER_ID%TYPE
21 /*Bug No : 6341012. MOAC uptake SOB_ID,ORG_ID are retrieved from Packages rather than from Profile values */
22 -- ,p_set_of_books_id IN IGC_CC_HEADERS.SET_OF_BOOKS_ID%TYPE
23 -- ,p_org_id IN IGC_CC_HEADERS.ORG_ID%TYPE
24 ,p_comp_unmatched_rel IN VARCHAR2
25 ,p_comp_cover IN VARCHAR2 ) IS
26
27 /*Bug No : 6341012. MOAC uptake. Local variables for SOB_ID,ORG_ID,SOB_NAME */
28 l_org_id IGC_CC_HEADERS.ORG_ID%TYPE;
29 l_set_of_books_id IGC_CC_HEADERS.SET_OF_BOOKS_ID%TYPE;
30 l_sob_name VARCHAR2(30);
31
32 l_request_id NUMBER;
33 l_validation_error_code VARCHAR2(1);
34 l_return_status VARCHAR2(1);
35 l_bc_status VARCHAR2(1);
36 l_msg_count NUMBER;
37 l_msg_data igc_cc_process_exceptions.exception_reason%TYPE;
38 l_error_text VARCHAR2(12000);
39
40 l_stage1_parent_req NUMBER;
41 l_stage1_wait_for_request BOOLEAN;
42 l_stage1_phase VARCHAR2(240);
43 l_stage1_status VARCHAR2(240);
44 l_stage1_dev_phase VARCHAR2(240);
45 l_stage1_dev_status VARCHAR2(240);
46 l_stage1_message VARCHAR2(240);
47
48 -- l_start_date DATE;
49 l_acct_date DATE;
50 -- l_end_date DATE;
51 -- l_count NUMBER;
52
53 -- 01/03/02, added for checking if CC is enabled in IGI
54 l_option_name VARCHAR2(80);
55 lv_message VARCHAR2(1000);
56 l_full_path VARCHAR2(500) := g_path||'Complete_Cover';
57 ---Variables related to the XML Report
58 l_terr VARCHAR2(10):='US';
59 l_lang VARCHAR2(10):='en';
60 l_layout BOOLEAN;
61
62
63 -- Cursor C_releases selects all releases for a cover commitment
64
65 Cursor C_releases is
66 Select *
67 from igc_cc_headers
68 where parent_header_id = p_cc_header_id
69 and set_of_books_id = l_set_of_books_id
70 and org_id = l_org_id;
71
72 -- Cursor C_cover selects the cover commitment information
73
74 Cursor C_cover is
75 Select *
76 from igc_cc_headers
77 where cc_header_id = p_cc_header_id
78 and set_of_books_id = l_set_of_books_id
79 and org_id = l_org_id;
80
81 /*Bug 5464993 - No need to check for current year release payment forecasts
82 * Commenting out c_fiscal_year_dates and c_payment_forecast cursors
83
84 -- Cursor C_fiscal_year_dates selects the start and end dates of the current fiscal year
85
86 Cursor C_fiscal_year_dates is
87 -- Performance Tuning, replaced this sql with the one below.
88 -- Select min(start_date) start_date, max(end_date) end_date
89 -- from gl_periods_v
90 -- where ( period_set_name
91 -- , period_type) in ( Select period_set_name, period_type
92 -- from gl_sets_of_books_v
93 -- where set_of_books_id = ( select set_of_books_id
94 -- from ap_system_parameters))
95 -- and period_year = to_char(sysdate, 'YYYY')
96 -- and adjustment_period_flag = 'N';
97
98 SELECT min(start_date) start_date, max(end_date) end_date
99 FROM gl_periods_v a,
100 gl_sets_of_books b,
101 ap_system_parameters c
102 WHERE a.period_set_name = b.period_set_name
103 AND a.period_type = b.accounted_period_type
104 AND b.set_of_books_id = c.set_of_books_id
105 AND a.period_year = to_char(sysdate, 'YYYY')
106 AND a.adjustment_period_flag = 'N';
107
108
109 -- Cursor C_payment_forecast counts the number of payment forecast lines
110 -- in the current fiscal year for a particular CC Release
111
112 Cursor C_payment_forecast ( x_start_date IN DATE
113 , x_end_date IN DATE
114 , x_cc_header_id IN IGC_CC_HEADERS.CC_HEADER_ID%TYPE ) IS
115 Select count(*) lines
116 from igc_cc_det_pf_v pf
117 where pf.cc_acct_line_id in ( select al.cc_acct_line_id
118 from igc_cc_acct_lines al
119 where al.cc_header_id = x_cc_header_id)
120 and pf.cc_det_pf_date between x_start_date and x_end_date;
121 */
122
123 -- Cursor C_purchase_orders selects the purchase orders created from a CC Release
124
125 Cursor C_purchase_orders ( x_cc_num IN IGC_CC_HEADERS.CC_NUM%TYPE ) IS
126 Select po_header_id
127 from PO_HEADERS
128 where segment1 = x_cc_num
129 and type_lookup_code = 'STANDARD';
130
131 /*modifed for 3199488 - fnd logging changes*/
132 /*ocedure Writelog (l_full_path, p_mesg in varchar2)IS
133 Begin
134 fnd_file.put_line( fnd_file.log , p_mesg ) ;
135 End Writelog;*/
136 PROCEDURE Writelog (
137 p_path IN VARCHAR2,
138 p_debug_msg IN VARCHAR2,
139 p_sev_level IN VARCHAR2 := g_state_level
140 ) IS
141 BEGIN
142
143 IF p_sev_level >= g_debug_level THEN
144 fnd_log.string(p_sev_level, p_path, p_debug_msg);
145 END IF;
146 END;
147
148 -- Procedure initialise_variables initialises the variables
149
150 Procedure initialise_variables IS
151 Begin
152 l_return_status := NULL;
153 l_bc_status := NULL;
154 l_msg_count := NULL;
155 l_msg_data := NULL;
156 End;
157
158 -- Procedure update_releases update the columns of igc_cc_headers table
159
160 Procedure Update_releases ( x_cc_state IN IGC_CC_HEADERS.CC_STATE%TYPE
161 , x_cc_apprvl_status IN IGC_CC_HEADERS.CC_APPRVL_STATUS%TYPE
162 , x_ctrl_status IN IGC_CC_HEADERS.CC_CTRL_STATUS%TYPE
163 , x_cc_header_id IN IGC_CC_HEADERS.CC_HEADER_ID%TYPE
164 , x_set_of_books_id IN IGC_CC_HEADERS.SET_OF_BOOKS_ID%TYPE
165 , x_org_id IN IGC_CC_HEADERS.ORG_ID%TYPE ) IS
166 Begin
167 Update igc_cc_headers
168 set cc_state = x_cc_state
169 , cc_apprvl_status = x_cc_apprvl_status
170 , cc_ctrl_status = x_ctrl_status
171 where cc_header_id = x_cc_header_id
172 and set_of_books_id = x_set_of_books_id
173 and org_id = x_org_id;
174 Exception
175 When others then
176 raise fnd_api.g_exc_error;
177 End Update_releases;
178
179
180 -- Procedure Insert_exceptions insert validations or budgetary control errors
181 -- for a CC in the igc_cc_process_exceptions table
182
183 Procedure Insert_exception (
184 x_request_id IN IGC_CC_PROCESS_EXCEPTIONS.REQUEST_ID%TYPE
185 , x_set_of_books_id IN IGC_CC_PROCESS_EXCEPTIONS.SET_OF_BOOKS_ID%TYPE
186 , x_org_id IN IGC_CC_PROCESS_EXCEPTIONS.ORG_ID%TYPE
187 , x_process_type IN IGC_CC_PROCESS_EXCEPTIONS.PROCESS_TYPE%TYPE
188 , x_process_phase IN IGC_CC_PROCESS_EXCEPTIONS.PROCESS_PHASE%TYPE
189 , x_cc_header_id IN IGC_CC_PROCESS_EXCEPTIONS.CC_HEADER_ID%TYPE
190 , x_reason IN IGC_CC_PROCESS_EXCEPTIONS.EXCEPTION_REASON%TYPE ) IS
191 Begin
192 Insert into igc_cc_process_exceptions ( process_type
193 , process_phase
194 , cc_header_id
195 , exception_reason
196 , org_id
197 , set_of_books_id
198 , request_id )
199 values ( x_process_type
200 , x_process_phase
201 , x_cc_header_id
202 , x_reason
203 , x_org_id
204 , x_set_of_books_id
205 , x_request_id );
206 Exception
207 When others then
208 raise fnd_api.g_exc_error;
209 End Insert_exception;
210
211 -- Procedure Submit_report submits the exception report after the Cover commitment is
212 -- processed
213
214 Procedure Submit_report is
215 l_full_path VARCHAR2(500) := g_path||'Submit_report';
216 Begin
217
218 /*Bug No : 6341012. MOAC uptake. Set Operaating Unit for the request before submitting. */
219
220 fnd_request.set_org_id(l_org_id);
221
222 l_stage1_parent_req := Fnd_request.Submit_request
223 ( 'IGC'
224 , 'IGCCCOVR'
225 , NULL
226 , NULL
227 , FALSE
228 , to_char(l_set_of_books_id)
229 , to_char(l_org_id)
230 , 'F'
231 , 'C'
232 , to_char(l_request_id)
233 );
234
235 If l_stage1_parent_req > 0 then
236 IF g_debug_mode = 'Y' THEN
237 Writelog (l_full_path,'IGCCCOVB - Submitted the Report IGCCCOVR ');
238 END IF;
239 Commit;
240 Else
241 IF g_debug_mode = 'Y' THEN
242 Writelog (l_full_path,'IGCCCOVB - Error Submitting the Report IGCCCOVR ');
243 END IF;
244 Raise_application_error
245 (-20000,'IGCCCOVB - Error submitting IGCCCOVR '||
246 SQLERRM ||'-'||SQLCODE);
247 End if;
248
249 l_stage1_wait_for_request :=
250 Fnd_concurrent.Wait_For_Request( l_stage1_parent_req,
251 05,
252 0,
253 l_stage1_phase,
254 l_stage1_status,
255 l_stage1_dev_phase,
256 l_stage1_dev_status,
257 l_stage1_message );
258
259 If ((l_stage1_dev_phase = 'COMPLETE') AND (l_stage1_dev_status = 'NORMAL')) THEN
260 IF g_debug_mode = 'Y' THEN
261 Writelog (l_full_path,'IGCCCOVB - COMPLETE / NORMAL status of IGCCCOVR request');
262 END IF;
263 Else
264 IF g_debug_mode = 'Y' THEN
265 Writelog (l_full_path,'IGCCCOVB - FAILED status of IGCCOVR request');
266 END IF;
267 Raise Fnd_api.g_exc_error;
268 End if;
269
270
271 ------------------------------------
272 ---Run XML Report
273 ------------------------------------
274 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
275 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
276 l_lang,
277 l_terr,
278 'IGCCCOVR_XML',
279 'IGC',
280 'IGCCCOVR_XML' );
281 l_layout := FND_REQUEST.ADD_LAYOUT(
282 'IGC',
283 'IGCCCOVR_XML',
284 l_lang,
285 l_terr,
286 'RTF');
287 IF l_layout then
288 fnd_request.set_org_id(l_org_id);
289 l_stage1_parent_req := Fnd_request.Submit_request
290 ( 'IGC'
291 , 'IGCCCOVR_XML'
292 , NULL
293 , NULL
294 , FALSE
295 , to_char(l_set_of_books_id)
296 , to_char(l_org_id)
297 , 'F'
298 , 'C'
299 , to_char(l_request_id)
300 );
301
302 End if;
303 End If;
304 If l_stage1_parent_req > 0 then
305 IF g_debug_mode = 'Y' THEN
306 Writelog (l_full_path,'IGCCCOVB - Submitted the XML Report IGCCCOVR_XML ');
307 END IF;
308 Commit;
309 Else
310 IF g_debug_mode = 'Y' THEN
311 Writelog (l_full_path,'IGCCCOVB - Error Submitting the XML Report IGCCCOVR_XML');
312 END IF;
313 Raise_application_error
314 (-20000,'IGCCCOVB - Error submitting IGCCCOVR_XML '||
315 SQLERRM ||'-'||SQLCODE);
316 End if;
317 l_stage1_wait_for_request :=
318 Fnd_concurrent.Wait_For_Request( l_stage1_parent_req,
319 05,
320 0,
321 l_stage1_phase,
322 l_stage1_status,
323 l_stage1_dev_phase,
324 l_stage1_dev_status,
325 l_stage1_message );
326 If ((l_stage1_dev_phase = 'COMPLETE') AND (l_stage1_dev_status = 'NORMAL')) THEN
327 IF g_debug_mode = 'Y' THEN
328 Writelog (l_full_path,'IGCCCOVB - COMPLETE / NORMAL status of IGCCCOVR_XML request');
329 END IF;
330 Else
331 IF g_debug_mode = 'Y' THEN
332 Writelog (l_full_path,'IGCCCOVB - FAILED status of IGCCOVR_XML request');
333 END IF;
334 Raise Fnd_api.g_exc_error;
335 End if;
336 ------------------------------------
337 ---End Of XML Report
338 ------------------------------------
339
340 End Submit_report;
341
342 Function matched_releases ( x_po_header_id IN PO_HEADERS.PO_HEADER_ID%TYPE )
343 return boolean IS
344 Cursor C_matched_releases is
345 Select 'X'
346 from ap_invoice_distributions ind
347 , po_distributions pd
348 , po_headers ph
349 where ind.po_distribution_id = pd.po_distribution_id
350 and pd.po_header_id = ph.po_header_id
351 and ph.po_header_id = x_po_header_id;
352 l_invoice_rec varchar2(1) := NULL;
353 Begin
354 Open C_matched_releases;
355 Fetch C_matched_releases into l_invoice_rec;
356 If C_matched_releases%found then
357 Close c_matched_releases;
358 return TRUE;
359 Else
360 Close C_matched_releases;
361 return FALSE;
362 End if;
363 Exception
364 When others then
365 If C_matched_releases%isopen then
366 Close C_matched_releases;
367 End if;
368 Raise Fnd_api.g_exc_error;
369 End;
370
371
372 -- Begin section of the complete cover procedure
373
374 Begin
375
376
377 -- 01/03/02, check to see if CC is installed
378
379 IF NOT igi_gen.is_req_installed('CC') THEN
380
381 SELECT meaning
382 INTO l_option_name
383 FROM igi_lookups
384 WHERE lookup_code = 'CC'
385 AND lookup_type = 'GCC_DESCRIPTION';
386
387 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
388 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
389 IF(g_error_level >= g_debug_level) THEN
390 FND_LOG.MESSAGE(g_error_level, l_full_path, FALSE);
391 END IF;
392 lv_message := fnd_message.get;
393 errbuf := lv_message;
394 retcode := 2;
395 return;
396 END IF;
397
398 /* Bug No : 6341012. MOAC uptake.get ORG_ID,SOB_ID from Packages */
399 l_org_id := MO_GLOBAL.get_current_org_id;
400 MO_UTILS.get_ledger_info(l_org_id,l_set_of_books_id,l_sob_name);
401
402 --
403 l_request_id := fnd_global.conc_request_id;
404 l_validation_error_code := 'N';
405 l_acct_date := SYSDATE;
406
407 Delete from igc_cc_process_exceptions
408 where process_type = 'C'
409 and process_phase = 'F'
410 and cc_header_id = p_cc_header_id
411 and set_of_books_id = l_set_of_books_id
412 and org_id = l_org_id ;
413
414 /*Commenting out the cursor for bug 5464993
415 *
416 Open C_fiscal_year_dates;
417 Fetch C_fiscal_year_dates into l_start_date, l_end_date;
418 Close C_fiscal_year_dates;
419 */
420
421 For C_rel_rec in C_releases loop
422 l_msg_data := NULL;
423 If ( c_rel_rec.cc_state = 'CL' and c_rel_rec.cc_apprvl_status = 'AP')
424 or (c_rel_rec.cc_state = 'CT' and c_rel_rec.cc_apprvl_status = 'AP')
425 or (c_rel_rec.cc_state = 'CT' and c_rel_rec.cc_apprvl_status = 'IN')
426 or (c_rel_rec.cc_state = 'CM' and c_rel_rec.cc_apprvl_status = 'AP') then
427
428 /*Commenting out the cursor for bug 5464993
429 *
430 Open C_payment_forecast ( l_start_date
431 , l_end_date
432 , c_rel_rec.cc_header_id );
433 Fetch C_payment_forecast into l_count;
434 Close C_payment_forecast;
435 */
436
437 -- If l_count > 0 then --commenting as part of fix for bug 5464993
438 For C_purchase_orders_rec in C_purchase_orders ( c_rel_rec.cc_num ) loop
439 If p_comp_unmatched_rel = 'N' then
440 If matched_releases ( C_purchase_orders_rec.po_header_id) then
441 If Igc_cc_rep_yep_pvt.Invoice_canc_or_paid(c_rel_rec.cc_header_id) then
442 fnd_message.set_name('IGC', 'IGC_CC_PURCHASE_ORDER_ERROR');
443 IF(g_excep_level >= g_debug_level) THEN
444 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
445 END IF;
446 l_msg_data := fnd_message.get;
447 l_validation_error_code := 'Y';
448 Insert_exception ( l_request_id
449 , c_rel_rec.set_of_books_id
450 , c_rel_rec.org_id
451 , 'C'
452 , 'F'
453 , c_rel_rec.cc_header_id
454 , l_msg_data);
455 goto process_next;
456 End if;
457 Else
458 fnd_message.set_name('IGC', 'IGC_CC_INVOICE_MATCH_ERROR');
459 IF(g_error_level >= g_debug_level) THEN
460 FND_LOG.MESSAGE(g_error_level, l_full_path, FALSE);
461 END IF;
462 l_msg_data := fnd_message.get;
463 l_validation_error_code := 'Y';
464 Insert_exception ( l_request_id
465 , c_rel_rec.set_of_books_id
466 , c_rel_rec.org_id
467 , 'C'
468 , 'F'
469 , c_rel_rec.cc_header_id
470 , l_msg_data);
471 goto process_next;
472 End if;
473 End if;
474 End loop;
475 -- process the budgetary control for releases
476 If ((c_rel_rec.cc_state = 'CM' and c_rel_rec.cc_apprvl_status = 'AP') OR
477 (c_rel_rec.cc_state = 'CT' and c_rel_rec.cc_apprvl_status = 'IN')) then
478 initialise_variables;
479 Update_releases ( 'CT'
480 , 'IN'
481 , 'C'
482 , c_rel_rec.cc_header_id
483 , c_rel_rec.set_of_books_id
484 , c_rel_rec.org_id );
485 Igc_cc_po_interface_pkg.update_po_approved_flag (
486 p_api_version => 1.0
487 , p_init_msg_list => fnd_api.g_false
488 , p_commit => fnd_api.g_false
489 , p_validation_level => fnd_api.g_valid_level_full
490 , x_return_status => l_return_status
491 , x_msg_count => l_msg_count
492 , x_msg_data => l_msg_data
493 , p_cc_header_id => c_rel_rec.cc_header_id );
494 If l_return_status = fnd_api.g_ret_sts_success then
495 initialise_variables;
496 Igc_cc_budgetary_ctrl_pkg.Execute_budgetary_ctrl (
497 p_api_version => 1.0
498 , p_init_msg_list => fnd_api.g_false
499 , p_commit => fnd_api.g_false
500 , p_validation_level => fnd_api.g_valid_level_full
501 , x_return_status => l_return_status
502 , x_bc_status => l_bc_status
503 , x_msg_count => l_msg_count
504 , x_msg_data => l_msg_data
505 , p_cc_header_id => c_rel_rec.cc_header_id
506 , p_accounting_date => c_rel_rec.cc_acct_date
507 , p_mode => 'R'
508 , p_notes => null);
509 If l_return_status = fnd_api.g_ret_sts_success and l_bc_status = fnd_api.g_true then
510 Update_releases ( 'CT'
511 , 'AP'
512 , 'C'
513 , c_rel_rec.cc_header_id
514 , c_rel_rec.set_of_books_id
515 , c_rel_rec.org_id );
516 Igc_cc_po_interface_pkg.Convert_cc_to_po(
517 p_api_version => 1.0
518 , p_init_msg_list => fnd_api.g_false
519 , p_commit => fnd_api.g_false
520 , p_validation_level => fnd_api.g_valid_level_full
521 , x_return_status => l_return_status
522 , x_msg_count => l_msg_count
523 , x_msg_data => l_msg_data
524 , p_cc_header_id => c_rel_rec.cc_header_id);
525 If l_return_status = fnd_api.g_ret_sts_success then
526 null;
527 Else
528 l_validation_error_code := 'Y';
529 Insert_exception ( l_request_id
530 , c_rel_rec.set_of_books_id
531 , c_rel_rec.org_id
532 , 'C'
533 , 'F'
534 , c_rel_rec.cc_header_id
535 , l_msg_data);
536 End if;
537 Else
538 l_validation_error_code := 'Y';
539 Insert_exception ( l_request_id
540 , c_rel_rec.set_of_books_id
541 , c_rel_rec.org_id
542 , 'C'
543 , 'F'
544 , c_rel_rec.cc_header_id
545 , l_msg_data);
546 End if;
547 Else
548 l_validation_error_code := 'Y';
549 Insert_exception ( l_request_id
550 , c_rel_rec.set_of_books_id
551 , c_rel_rec.org_id
552 , 'C'
553 , 'F'
554 , c_rel_rec.cc_header_id
555 , l_msg_data);
556 End if;
557 End if;
558 -- End if; --Bug 5464993
559 Else
560 fnd_message.set_name('IGC', 'IGC_CC_COMPLETE_COVER_ERROR');
561 fnd_message.set_token('RELEASE_STATE', c_rel_rec.cc_state);
562 fnd_message.set_token('APPROVAL_STATUS', c_rel_rec.cc_apprvl_status);
563 l_msg_data := fnd_message.get;
564 IF(g_error_level >= g_debug_level) THEN
565 FND_LOG.MESSAGE(g_error_level, l_full_path, FALSE);
566 END IF;
567 l_validation_error_code := 'Y';
568 Insert_exception ( l_request_id
569 , c_rel_rec.set_of_books_id
570 , c_rel_rec.org_id
571 , 'C'
572 , 'F'
573 , c_rel_rec.cc_header_id
574 , l_msg_data);
575 End if;
576 <<process_next>>
577 null;
578 End loop;
579
580 If l_validation_error_code = 'N' and p_comp_cover = 'Y' then
581 For c_cover_rec in C_cover loop
582 If (c_cover_rec.cc_state = 'CM' and c_cover_rec.cc_apprvl_status = 'AP') then
583 Initialise_variables;
584 Update_releases ( 'CT'
585 , 'IN'
586 , 'C'
587 , c_cover_rec.cc_header_id
588 , c_cover_rec.set_of_books_id
589 , c_cover_rec.org_id );
590 Igc_cc_budgetary_ctrl_pkg.Execute_budgetary_ctrl (
591 p_api_version => 1.0
592 , p_init_msg_list => fnd_api.g_false
593 , p_commit => fnd_api.g_false
594 , p_validation_level => fnd_api.g_valid_level_full
595 , x_return_status => l_return_status
596 , x_bc_status => l_bc_status
597 , x_msg_count => l_msg_count
598 , x_msg_data => l_msg_data
599 , p_cc_header_id => c_cover_rec.cc_header_id
600 , p_accounting_date => l_acct_date
601 , p_mode => 'R'
602 , p_notes => null);
603 If l_return_status = fnd_api.g_ret_sts_success and l_bc_status = fnd_api.g_true then
604 Update_releases ( 'CT'
605 , 'AP'
606 , 'C'
607 , c_cover_rec.cc_header_id
608 , c_cover_rec.set_of_books_id
609 , c_cover_rec.org_id );
610 Else
611 Insert_exception ( l_request_id
612 , c_cover_rec.set_of_books_id
613 , c_cover_rec.org_id
614 , 'C'
615 , 'F'
616 , c_cover_rec.cc_header_id
617 , l_msg_data);
618 End if;
619 End if;
620 End loop;
621 End if;
622
623 Submit_report;
624
625 -- ------------------------------------------------------------------------------------
626 -- Ensure that any exceptions raised are output into the log file to be reported to
627 -- the user if any are present.
628 -- ------------------------------------------------------------------------------------
629 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
630 p_data => l_msg_data );
631
632 IF (l_msg_count > 0) THEN
633 l_error_text := '';
634 FOR l_cur IN 1..l_msg_count LOOP
635 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
636 fnd_file.put_line (FND_FILE.LOG,
637 l_error_text);
638 WriteLog(l_full_path, l_error_text, g_excep_level);
639 END LOOP;
640 END IF;
641
642 commit;
643 retcode := 0;
644 errbuf := 'Normal Completion';
645
646 -- Exception section of Complete_cover procedure
647
648 Exception
649 When Fnd_api.g_exc_error THEN
650 If C_releases%isopen then
651 Close C_releases;
652 End if;
653 If C_cover%isopen then
654 Close C_cover;
655 End if;
656 /* IF C_fiscal_year_dates%isopen then
657 Close C_fiscal_year_dates;
658 End if;
659 If C_payment_forecast%isopen then
660 Close C_payment_forecast;
661 End if; */
662 If C_purchase_orders%isopen then
663 Close C_purchase_orders;
664 End if;
665 retcode := 2;
666 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
667 errbuf := fnd_message.get;
668 IF g_unexp_level >= g_debug_level THEN
669 fnd_message.set_name('IGC','IGC_LOGGING_UNEXP_ERROR');
670 fnd_message.set_token('CODE',SQLCODE);
671 fnd_message.set_token('MESG',SQLERRM);
672 fnd_log.message(g_unexp_level,l_full_path,TRUE);
673 END IF;
674 raise_application_error (-20000,'IGCCCOVB : '||SQLERRM ||'-'||SQLCODE);
675 --raise_application_error (-20000,errbuf);
676 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
677 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Complete_cover');
678 END IF;
679 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
680 p_data => l_msg_data );
681
682 IF (l_msg_count > 0) THEN
683
684 l_error_text := '';
685 FOR l_cur IN 1..l_msg_count LOOP
686 l_error_text := l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
687 IF(g_excep_level >= g_debug_level) THEN
688 FND_LOG.STRING(g_excep_level, l_full_path, l_error_text);
689 END IF;
690 fnd_file.put_line (FND_FILE.LOG,
691 l_error_text);
692 WriteLog(l_full_path, l_error_text, g_excep_level);
693 END LOOP;
694 ELSE
695 l_error_text := 'Error Returned but Error stack has no data';
696
697 if g_excep_level >= g_debug_level then
698 FND_LOG.STRING(g_excep_level, l_full_path, l_error_text);
699 end if;
700
701 fnd_file.put_line (FND_FILE.LOG,
702 l_error_text);
703 END IF;
704
705 When others then
706 If C_releases%isopen then
707 Close C_releases;
708 End if;
709 If C_cover%isopen then
710 Close C_cover;
711 End if;
712 /* IF C_fiscal_year_dates%isopen then
713 Close C_fiscal_year_dates;
714 End if;
715 If C_payment_forecast%isopen then
716 Close C_payment_forecast;
717 End if; */
718 If C_purchase_orders%isopen then
719 Close C_purchase_orders;
720 End if;
721 retcode := 2;
722 fnd_message.set_name('IGC','IGC_LOGGING_USER_ERROR');
723 errbuf := fnd_message.get;
724 IF g_unexp_level >= g_debug_level THEN
725 fnd_message.set_name('IGC','IGC_LOGGING_UNEXP_ERROR');
726 fnd_message.set_token('CODE',SQLCODE);
727 fnd_message.set_token('MESG',SQLERRM);
728 fnd_log.message(g_unexp_level,l_full_path,TRUE);
729 END IF;
730 raise_application_error (-20000,'IGCCCOVB : '||SQLERRM ||'-'||SQLCODE);
731 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
732 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Complete_cover');
733 END IF;
734 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
735 p_data => l_msg_data );
736
737 IF (l_msg_count > 0) THEN
738
739 l_error_text := '';
740 FOR l_cur IN 1..l_msg_count LOOP
741 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
742 fnd_file.put_line (FND_FILE.LOG,
743 l_error_text);
744 WriteLog(l_full_path, l_error_text, g_excep_level);
745 END LOOP;
746 ELSE
747 l_error_text := 'Error Returned but Error stack has no data';
748 fnd_file.put_line (FND_FILE.LOG,
749 l_error_text);
750 WriteLog(l_full_path, l_error_text, g_excep_level);
751 END IF;
752
753 End Complete_cover ;
754
755 END IGC_CC_COMPLETE_COVER_PKG;