[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_FUNDS_CONTROL_PKG
Source
1 PACKAGE BODY IGI_ITR_FUNDS_CONTROL_PKG AS
2 -- $Header: igiitrhb.pls 120.10 2007/08/23 14:52:16 smannava ship $
3
4 ------------------------------------------------------------------------
5 -- Cursor to be used in Funds_Control for both Fundschecker and Approval
6 ------------------------------------------------------------------------
7
8 Cursor Fundscntrl_Itr_Cur(
9 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
10 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
11 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
12 p_parent_value IN igi_itr_charge_lines.entered_dr%type) IS
13 Select
14 A.rowid,
15 A.it_service_line_id,
16 decode(nvl(A.entered_dr,0),0,A.creation_code_combination_id,A.receiving_code_combination_id),
17 decode(nvl(A.entered_dr,0),0,A.entered_cr,A.entered_dr) amount,
18 P1.start_date gl_encumbered_date,
19 H.it_period_name,
20 H.currency_code,
21 P1.period_num,
22 P1.period_year,
23 P1.quarter_num,
24 A.reversal_flag,
25 A.packet_id,
26 H.name,
27 A.description,
28 A.charge_service_id,
29 A.status_flag,
30 A.prevent_encumbrance_flag,
31 H.it_originator_id
32 From
33 igi_itr_charge_headers H,
34 igi_itr_charge_lines L,
35 igi_itr_charge_lines_audit A,
36 gl_period_statuses P1
37 Where H.it_header_id = p_it_header_id
38 And L.it_header_id = H.it_header_id
39 And A.it_header_id = L.it_header_id
40 And L.it_service_line_id = nvl(p_it_service_line_id, L.it_service_line_id)
41 And A.it_service_line_id = L.it_service_line_id
42 And (nvl(A.status_flag,'P') = 'F'
43 Or (nvl(A.status_flag,'P') = 'L' and A.encumbrance_flag = 'Y' and nvl(A.unencumbered_amount,0) = 0)
44 Or nvl(A.status_flag,'P') = 'P'
45 Or nvl(A.status_flag,'P') = 'C'
46 Or nvl(A.status_flag,'P') = 'U'
47 Or nvl(A.status_flag,'P') = 'R'
48 Or nvl(A.status_flag,'P') = 'J'
49 Or (A.encumbrance_flag = 'Y' and nvl(A.prevent_encumbrance_flag,'N') = 'Y'))
50 And nvl(A.reversal_flag,'N') = 'N'
51 And H.set_of_books_id = p_set_of_books_id
52 And H.it_period_name = P1.period_name
53 And P1.set_of_books_id = H.set_of_books_id
54 And NVL(P1.adjustment_period_flag,'N') = 'N'
55 And P1.application_id = (Select F1.application_id
56 From fnd_application F1
57 Where F1.application_short_name = 'SQLGL');
58
59 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
60 l_state_level number := FND_LOG.LEVEL_STATEMENT;
61 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
62 l_event_level number := FND_LOG.LEVEL_EVENT;
63 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
64 l_error_level number := FND_LOG.LEVEL_ERROR;
65 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
66 ------------------------------------------------
67 -- Private (Non Public) Procedure Specifications
68 ------------------------------------------------
69
70 Procedure Writelog(
71 p_mesg IN varchar2,
72 p_debug_mode IN boolean ) IS
73 Begin
74 If p_debug_mode Then
75 fnd_file.put_line(fnd_file.log , p_mesg) ;
76 Else
77 null;
78 End if;
79 End Writelog;
80
81 Procedure Process_Fundschk_Failure_Code(
82 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
83 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
84 p_status_flag IN igi_itr_charge_lines.status_flag%type,
85 p_prevent_encumbrance_flag IN igi_itr_charge_lines.prevent_encumbrance_flag%type,
86 p_packet_id IN igi_itr_charge_lines_audit.packet_id%type,
87 p_return_message_name IN OUT NOCOPY varchar2,
88 p_called_by IN varchar2,
89 p_rowid IN varchar2,
90 p_calling_sequence IN varchar2);
91
92 Procedure Itr_Enc_Update(
93 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
94 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
95 p_status_flag IN varchar2,
96 p_prevent_encumbrance_flag IN varchar2,
97 p_packet_id IN number,
98 p_fc_result_code IN varchar2,
99 p_rowid IN varchar2);
100
101 Procedure Get_Gl_Fundschk_Result_Code(
102 p_packet_id IN number,
103 p_fc_result_code IN OUT NOCOPY varchar2);
104
105 ------------------------
106 -- Procedure Definitions
107 ------------------------
108
109 --------------------------------------------------------------------
110 -- ENCUMBRANCE_ENABLED: is a function that returns boolean. True if
111 -- encumbrance is enabled, false otherwise.
112 --------------------------------------------------------------------
113
114 Function Encumbrance_Enabled(
115 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type) Return Boolean IS
116 l_enc_enabled varchar2(1);
117 l_debug_loc varchar2(30) := 'Encumbrance_Enabled';
118 l_curr_calling_sequence varchar2(2000);
119 l_debug_info varchar2(100);
120 Begin
121 ----------------------------------------------------------------------
122 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'|| l_debug_loc;
123 l_debug_info := 'Checking if encumbrance is enabled';
124 ----------------------------------------------------------------------
125 Select nvl(use_encumbrance_flag,'N')
126 Into l_enc_enabled
127 From igi_itr_charge_setup
128 Where set_of_books_id = p_set_of_books_id;
129 If (l_enc_enabled = 'Y') Then
130 Return(TRUE);
131 Else
132 Return(FALSE);
133 End If;
134 Exception
135 When No_data_found Then
136 Return(FALSE);
137 When Others Then
138 If (SQLCODE <> -20001) Then
139 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
140 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
141 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
142 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
143
144 IF ( l_unexp_level >= l_debug_level) THEN
145 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
146 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
147 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
148 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Encumbrance_Enabled.msg1',TRUE);
149 END IF;
150
151 End If;
152 APP_EXCEPTION.RAISE_EXCEPTION;
153 End Encumbrance_Enabled;
154
155 Procedure Setup_Gl_Fundschk_Params(
156 p_packet_id IN OUT NOCOPY igi_itr_charge_lines_audit.packet_id%type,
157 p_mode IN OUT NOCOPY varchar2,
158 p_partial_resv_flag IN OUT NOCOPY varchar2,
159 p_called_by IN varchar2,
160 p_calling_sequence IN varchar2) IS
161 l_debug_loc varchar2(30) := 'Setup_Gl_Fundschk_Params';
162 l_curr_calling_sequence varchar2(2000);
163 l_debug_info varchar2(100);
164 l_packet_id_old igi_itr_charge_lines_audit.packet_id%type;
165 l_mode_old varchar2(1);
166 l_partial_resv_flag_old varchar2(1);
167 Begin
168 -----------------------------------------------------------------------------------------------
169 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
170 l_debug_info := 'Call Get_Gl_Fundschk_Packet_id';
171 -----------------------------------------------------------------------------------------------
172
173 l_packet_id_old := p_packet_id;
174 l_mode_old := p_mode;
175 l_partial_resv_flag_old := p_partial_resv_flag;
176
177 Get_Gl_Fundschk_Packet_Id(p_packet_id);
178
179 -------------------------------------------------------------------
180 -- Init p_mode and p_partial_resv_flag depending on calling program
181 -------------------------------------------------------------------
182 If (p_called_by = 'A') Then -- Reservation
183 p_mode := 'R'; -- reserve funds
184 p_partial_resv_flag := 'N'; -- partial reservation not allowed
185 Else -- p_called_by = 'F' Fundschecker
186 p_mode := 'C'; -- check funds
187 p_partial_resv_flag := 'Y'; -- partial reservation allowed
188 End If;
189 Exception
190 When Others Then
191 p_packet_id := l_packet_id_old;
192 p_mode := l_mode_old;
193 p_partial_resv_flag := l_partial_resv_flag_old;
194 If (SQLCODE <> -20001) Then
195 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
196 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
197 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
198 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Called_by = '|| p_called_by
199 ||', Packet Id = '|| to_char(p_packet_id)
200 ||', Mode = '|| p_mode
201 ||', Partial Reservation Flag = '|| p_partial_resv_flag);
202 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
203
204 IF ( l_unexp_level >= l_debug_level) THEN
205 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
206 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
207 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
208 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Setup_Gl_Fundschk_Params.msg2',TRUE);
209 END IF;
210
211 End If;
212 APP_EXCEPTION.RAISE_EXCEPTION;
213 End Setup_Gl_Fundschk_Params;
214
215
216 Procedure Get_Gl_Fundschk_Packet_Id(
217 p_packet_id IN OUT NOCOPY igi_itr_charge_lines_audit.packet_id%type) IS
218 l_debug_loc varchar2(30) := 'Get_Gl_Fundschk_Packet_Id';
219 l_curr_calling_sequence varchar2(2000);
220 l_debug_info varchar2(100);
221 l_packet_id_old igi_itr_charge_lines_audit.packet_id%type;
222 Begin
223 ---------------------------------------------------------------
224 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc;
225 l_debug_info := 'Retrieve next packet id from gl_bc_packets_s';
226 ---------------------------------------------------------------
227
228 l_packet_id_old := p_packet_id;
229
230 Select gl_bc_packets_s.nextval
231 Into p_packet_id
232 From sys.dual;
233 Exception
234 When Others Then
235 p_packet_id := l_packet_id_old;
236 If (SQLCODE <> -20001) Then
237 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
238 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
239 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
240 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
241
242
243 IF ( l_unexp_level >= l_debug_level) THEN
244 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
245 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
246 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
247 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Get_Gl_Fundschk_Packet_Id.msg3',TRUE);
248 END IF;
249
250 End If;
251 APP_EXCEPTION.RAISE_EXCEPTION;
252 End Get_Gl_Fundschk_Packet_Id;
253
254 PROCEDURE Funds_Check_Reserve(
255 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
256 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
257 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
258 p_reversal_amount IN igi_itr_charge_lines.entered_dr%type,
259 p_called_by IN varchar2, -- fundschecker(F)/approval(A)
260 p_return_message_name IN OUT NOCOPY varchar2,
261 p_calling_sequence IN varchar2) IS
262 l_rowid varchar2(100);
263 l_packet_id number;
264 l_fundschk_mode varchar2(1);
265 l_partial_reserv_flag varchar2(1);
266 l_chart_of_accounts_id number;
267 l_itr_enc_type_id number;
268 l_gl_user_id number;
269 l_it_service_line_id igi_itr_charge_lines_audit.it_service_line_id%type;
270 l_ccid igi_itr_charge_lines_audit.receiving_code_combination_id%type;
271 l_amount igi_itr_charge_lines_audit.entered_dr%type;
272 l_gl_encumbered_date igi_itr_charge_lines_audit.gl_encumbered_date%type;
273 l_period_name igi_itr_charge_headers.it_period_name%type;
274 l_currency_code igi_itr_charge_headers.currency_code%type;
275 l_period_num gl_period_statuses.period_num%type;
276 l_period_year gl_period_statuses.period_year%type;
277 l_quarter_num gl_period_statuses.quarter_num%type;
278 l_current_period_name igi_itr_charge_headers.it_period_name%type;
279 l_current_period_num gl_period_statuses.period_num%type;
280 l_current_period_year gl_period_statuses.period_year%type;
281 l_current_quarter_num gl_period_statuses.quarter_num%type;
282 l_reversal_flag igi_itr_charge_lines_audit.reversal_flag%type;
283 l_old_packet_id igi_itr_charge_lines_audit.packet_id%type;
284 l_description igi_itr_charge_lines_audit.description%type;
285 l_charge_service_id igi_itr_charge_lines_audit.charge_service_id%type;
286 l_status_flag igi_itr_charge_lines_audit.status_flag%type;
287 l_prevent_encumbrance_flag igi_itr_charge_lines_audit.prevent_encumbrance_flag%type;
288 l_it_originator_id igi_itr_charge_headers.it_originator_id%type;
289 l_debug_loc varchar2(30) := 'Funds_Check_Reserve';
290 l_curr_calling_sequence varchar2(2000);
291 l_debug_info varchar2(100);
292 l_return_code varchar2(4);
293 l_status_code varchar2(1); -- used in GL BC PACKETS C[fundschecking]/P[Reservation]
294 l_je_category_name gl_je_categories.je_category_name%type := 'IGIITRCC';
295 l_return_message_name_old varchar2(30);
296 l_charge_name igi_itr_charge_headers.name%type;
297 /*
298 Cursor C_cat_name IS
299 Select je_category_name
300 From gl_je_categories
301 Where user_je_category_name = 'Cross Charges';
302 */
303
304 Cursor C_current_period IS
305 Select
306 period_name,
307 period_num,
308 period_year,
309 quarter_num
310 From gl_period_statuses
311 Where trunc(sysdate) Between trunc(start_date) And trunc(end_date)
312 And set_of_books_id = p_set_of_books_id
313 And NVL(adjustment_period_flag,'N') = 'N'
314 And application_id = (Select application_id
315 From fnd_application
316 Where application_short_name = 'SQLGL');
317 Begin
318 -----------------------------------------------------------------------------------------------
319 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
320 -----------------------------------------------------------------------------------------------
321
322 l_return_message_name_old := p_return_message_name;
323
324 If Encumbrance_Enabled (p_set_of_books_id) Then
325 ----------------------------------------------------------
326 -- Retrieve system variables to be used by fundschecker --
327 ----------------------------------------------------------
328 Fundscheck_Init(
329 l_chart_of_accounts_id,
330 p_set_of_books_id,
331 l_itr_enc_type_id,
332 l_gl_user_id,
333 l_curr_calling_sequence);
334
335 /*
336 -----------------------------------------
337 l_debug_info := 'Open C_cat_name Cursor';
338 -----------------------------------------
339 Open C_cat_name;
340 Fetch C_cat_name Into l_je_category_name;
341 Close C_cat_name;
342 */
343
344 -------------------------------------------------
345 l_debug_info := 'Open Fundscntrl_Itr_Cur Cursor';
346 -------------------------------------------------
347 Open Fundscntrl_Itr_Cur(
348 p_set_of_books_id,
349 p_it_header_id,
350 p_it_service_line_id,
351 p_reversal_amount);
352
353 Loop
354 ----------------------------------------------------------------
355 l_debug_info := 'Fetch from Fundscntrl_Itr_Cur in Fundschecker';
356 ----------------------------------------------------------------
357 Fetch Fundscntrl_Itr_Cur Into
358 l_rowid,
359 l_it_service_line_id,
360 l_ccid,
361 l_amount,
362 l_gl_encumbered_date,
363 l_period_name,
364 l_currency_code,
365 l_period_num,
366 l_period_year,
367 l_quarter_num,
368 l_reversal_flag,
369 l_old_packet_id,
370 l_charge_name, --shsaxena for bug 2948237
371 l_description,
372 l_charge_service_id,
373 l_status_flag,
374 l_prevent_encumbrance_flag,
375 l_it_originator_id;
376 Exit When Fundscntrl_Itr_Cur%notfound;
377 If p_called_by = 'F' Then
378 l_status_code := 'C';
379 Else
380 l_status_code := 'P';
381 End If;
382 ------------------------------------------------------------
383 -- Encumbrance enabled, setup gl_fundschecker parameters --
384 ------------------------------------------------------------
385 Setup_Gl_Fundschk_Params(
386 l_packet_id,
387 l_fundschk_mode,
388 l_partial_reserv_flag,
389 p_called_by, -- 'FUNDSCHECK',
390 l_curr_calling_sequence);
391
392 If l_status_flag in ('R','J') Then
393 -- insert reversal line
394 -- based on assumption that a charge/payment cannot be modified
395 Bc_Packets_Insert(
396 l_packet_id,
397 p_set_of_books_id,
398 l_ccid,
399 p_reversal_amount,
400 l_period_year,
401 l_period_num,
402 l_quarter_num,
403 l_gl_user_id,
404 l_itr_enc_type_id,
405 l_it_service_line_id,
406 l_charge_service_id,
407 l_it_originator_id,
408 'Internal Trading',
409 l_je_category_name,
410 'E',
411 l_period_name,
412 l_currency_code,
413 l_status_code, -- 'C'or 'P'
414 'Y', -- l_reversal_flag,
415 l_status_flag,
416 l_prevent_encumbrance_flag,
417 l_charge_name,--shsaxena for bug 2948237
418 -- l_description,
419 l_curr_calling_sequence);
420 End If;
421
422 ----------------------------------------------------------------
423 l_debug_info := 'Open C_current_period Cursor';
424 ----------------------------------------------------------------
425 Open C_current_period;
426 Fetch C_current_period Into
427 l_current_period_name,
428 l_current_period_num,
429 l_current_period_year,
430 l_current_quarter_num;
431 Close C_current_period;
432
433 If (l_current_period_name = l_period_name And
434 l_current_period_num = l_period_num And
435 l_current_period_year = l_period_year And
436 l_current_quarter_num = l_quarter_num) Then
437 -- Current period gl_encumbered date is sysdate else the first date of the period
438 l_gl_encumbered_date := sysdate;
439 End if;
440
441 Bc_Packets_Insert(l_packet_id,
442 p_set_of_books_id,
443 l_ccid,
444 l_amount,
445 l_period_year,
446 l_period_num,
447 l_quarter_num,
448 l_gl_user_id,
449 l_itr_enc_type_id,
450 l_it_service_line_id,
451 l_charge_service_id,
452 l_it_originator_id,
453 'Internal Trading',
454 l_je_category_name,
455 'E',
456 l_period_name,
457 l_currency_code,
458 l_status_code, -- 'C'or 'P'
459 l_reversal_flag,
460 l_status_flag,
461 l_prevent_encumbrance_flag,
462 l_charge_name, --shsaxena for bug 2948237
463 -- l_description,
464 l_curr_calling_sequence);
465
466 ---------------------------------------
467 l_debug_info := 'Call Gl_Fundschecker';
468 ---------------------------------------
469 /* Commented below code and added another call
470 since the GL funds checker has changed in R12. Changed during r12 uptake for bug#602857
471 /* If (Not GL_FUNDS_CHECKER_PKG.glxfck(
472 p_set_of_books_id,
473 l_packet_id,
474 l_fundschk_mode,
475 l_partial_reserv_flag,
476 'N',
477 'N',
478 NULL,
479 NULL,
480 l_return_code)) Then
481 APP_EXCEPTION.Raise_Exception;
482 End If; */
483 If (Not PSA_FUNDS_CHECKER_PKG.GLXFCK(p_set_of_books_id,
484 l_packet_id,
485 l_fundschk_mode,
486 'N',
487 'N',
488 NULL,
489 NULL,
490 'G',
491 l_return_code)) Then
492 APP_EXCEPTION.Raise_Exception;
493 End If;
494 --------------------------------------
495 l_debug_info := 'Process_Return_Code';
496 --------------------------------------
497 If (l_return_code in ('T', 'F', 'P')) Then -- Fundscheck Failed
498 Process_Fundschk_Failure_Code(
499 p_it_header_id,
500 l_it_service_line_id,
501 l_status_flag,
502 l_prevent_encumbrance_flag,
503 l_packet_id,
504 p_return_message_name,
505 p_called_by,
506 l_rowid,
507 p_calling_sequence);
508 Else -- Fundscheck Passed --
509 If p_called_by = 'A' Then --Approval
510 -------------------------------------------
511 l_debug_info := 'Funds Reservation Passed';
512 -------------------------------------------
513 If l_status_flag in ('R','J') Then
514 -----------------------------------------------------------------------
515 l_debug_info := 'Rejection Lines Passed Updating Charge Lines'
516 || ' and Audit Tables ';
517 -- set the unencumbered amount = parent_value and the reversal flag = N
518 -----------------------------------------------------------------------
519 Update igi_itr_charge_lines
520 Set failed_funds_lookup_code = 'N',
521 status_flag = 'N',
522 encumbrance_flag = 'Y',
523 encumbered_amount = l_amount,
524 gl_encumbered_date = l_gl_encumbered_date,
525 gl_encumbered_period_name = l_period_name,
526 unencumbered_amount = NULL,
527 packet_id = l_packet_id
528 Where it_header_id = p_it_header_id
529 And it_service_line_id = l_it_service_line_id;
530
531 Update igi_itr_charge_lines_audit
532 Set failed_funds_lookup_code = 'N',
533 status_flag = 'N',
534 encumbrance_flag = 'Y',
535 encumbered_amount = l_amount,
536 gl_encumbered_date = l_gl_encumbered_date,
537 gl_encumbered_period_name = l_period_name,
538 unencumbered_amount = NULL,
539 --packet_id = NULL
540 packet_id = l_packet_id
541 Where it_header_id = p_it_header_id
542 And it_service_line_id = l_it_service_line_id
543 And rowid = l_rowid;
544
545 Update igi_itr_charge_lines_audit
546 Set unencumbered_amount = p_reversal_amount,
547 reversal_flag = 'O',
548 -- obselete so it doesn not get picked up in the cursor select again ,
549 -- problem in multiple modifications [N]
550 --packet_id = NULL
551 packet_id = l_packet_id
552 Where it_header_id = p_it_header_id
553 And it_service_line_id = l_it_service_line_id
554 And reversal_flag = 'Y';
555 Elsif l_status_flag = 'L' Then -- Cancellation
556 ------------------------------------------------------------
557 l_debug_info := 'Cancellation Passed, Updating Charge Lines'
558 || ' and Audit Tables ';
559 ------------------------------------------------------------
560 Update igi_itr_charge_lines
561 Set unencumbered_amount = l_amount * -1,
562 encumbrance_flag = 'N',
563 gl_cancelled_date = l_gl_encumbered_date,
564 packet_id = l_packet_id
565 Where it_header_id = p_it_header_id
566 And it_service_line_id = l_it_service_line_id;
567
568 Update igi_itr_charge_lines_audit
569 Set unencumbered_amount = l_amount * -1,
570 encumbrance_flag = 'N',
571 gl_cancelled_date = l_gl_encumbered_date,
572 --packet_id = NULL
573 packet_id = l_packet_id
574 Where it_header_id = p_it_header_id
575 And it_service_line_id = l_it_service_line_id
576 And rowid = l_rowid;
577 Elsif l_prevent_encumbrance_flag = 'Y' Then -- Unreservation
578 -------------------------------------------------------------
579 l_debug_info := 'Unreservation Passed, Updating Charge Lines'
580 || ' and Audit Tables ';
581 -------------------------------------------------------------
582 Update igi_itr_charge_lines
583 Set failed_funds_lookup_code = 'N',
584 status_flag = 'U',
585 prevent_encumbrance_flag = 'N',
586 unencumbered_amount = l_amount,
587 packet_id = l_packet_id
588 Where it_header_id = p_it_header_id
589 And it_service_line_id = l_it_service_line_id;
590
591 Update igi_itr_charge_lines_audit
592 Set failed_funds_lookup_code = 'N',
593 prevent_encumbrance_flag = 'N',
594 status_flag = 'U',
595 unencumbered_amount = l_amount,
596 --packet_id = NULL
597 packet_id = l_packet_id
598 Where it_header_id = p_it_header_id
599 And it_service_line_id = l_it_service_line_id
600 And rowid = l_rowid;
601 Else
602 ----------------------------------------------------------
603 l_debug_info := 'Reservation Passed, Updating Charge Lines'
604 || ' and Audit Tables ';
605 ----------------------------------------------------------
606 Update igi_itr_charge_lines
607 Set failed_funds_lookup_code = 'N',
608 status_flag = 'N',
609 encumbrance_flag = 'Y',
610 encumbered_amount = l_amount,
611 gl_encumbered_date = l_gl_encumbered_date,
612 gl_encumbered_period_name = l_period_name,
613 unencumbered_amount = NULL,
614 packet_id = l_packet_id
615 Where it_header_id = p_it_header_id
616 And it_service_line_id = l_it_service_line_id;
617
618 Update igi_itr_charge_lines_audit
619 Set failed_funds_lookup_code = 'N',
620 status_flag = 'N',
621 encumbrance_flag = 'Y',
622 encumbered_amount = l_amount,
623 gl_encumbered_date = l_gl_encumbered_date,
624 gl_encumbered_period_name = l_period_name,
625 unencumbered_amount = NULL,
626 --packet_id = NULL
627 packet_id = l_packet_id
628 Where it_header_id = p_it_header_id
629 And it_service_line_id = l_it_service_line_id
630 And rowid = l_rowid;
631 End if;
632 Else --Funds Checking
633 ----------------------------------------------------------
634 l_debug_info := 'Fundscheck Passed, Updating Charge Lines'
635 || ' and Audit Tables ';
636 ----------------------------------------------------------
637 Update igi_itr_charge_lines
638 Set status_flag = 'C',
639 failed_funds_lookup_code = 'N',
640 packet_id = l_packet_id
641 Where it_header_id = p_it_header_id
642 And it_service_line_id = l_it_service_line_id;
643
644 Update igi_itr_charge_lines_audit
645 Set status_flag = 'C',
646 failed_funds_lookup_code = 'N',
647 --packet_id = NULL
648 packet_id = l_packet_id
649 Where it_header_id = p_it_header_id
650 And it_service_line_id = l_it_service_line_id
651 And rowid = l_rowid;
652 End If;
653 If (l_return_code = 'A') Then
654 p_return_message_name := 'IGI_ITR_FCK_PASSED_FUNDS_ADVIS';
655 Else
656 p_return_message_name := 'IGI_ITR_FCK_PASSED_FUNDS_CHECK';
657 End If;
658 End If; -- Fundscheck Passed --
659 End Loop;
660 Close Fundscntrl_Itr_Cur;
661 Commit;
662 Else -- Encumbrance is off --
663 p_return_message_name := 'IGI_ITR_ALL_ENC_OFF';
664 End If;
665 Exception
666 When Others Then
667 p_return_message_name := l_return_message_name_old;
668 If (SQLCODE <> -20001) Then
669 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
670 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
671 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
672 FND_MESSAGE.SET_TOKEN(
673 'PARAMETERS','Charge Header Id = '|| to_char(p_it_header_id)
674 ||', Service Id = '|| to_char(l_it_service_line_id)
675 ||', Set of Books Id = ' || to_char(p_set_of_books_id)
676 ||', Reversal Amount = ' || to_char(p_reversal_amount)
677 ||', Called By = ' || p_called_by );
678 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
679
680 IF ( l_unexp_level >= l_debug_level) THEN
681 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
682 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
683 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
684 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Funds_Check_Reserve.msg4',TRUE);
685 END IF;
686
687 End If;
688 APP_EXCEPTION.RAISE_EXCEPTION;
689 End Funds_Check_Reserve;
690
691 Procedure Fundscheck_Init(
692 p_chart_of_accounts_id IN OUT NOCOPY gl_sets_of_books.chart_of_accounts_id%type,
693 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
694 p_itr_enc_type_id IN OUT NOCOPY igi_itr_charge_setup.encumbrance_type_id%type,
695 p_gl_user_id IN OUT NOCOPY number,
696 p_calling_sequence IN varchar2) IS
697 l_debug_loc varchar2(30) := 'Fundscheck_Init';
698 l_curr_calling_sequence varchar2(2000);
699 l_debug_info varchar2(100);
700 l_chart_of_accounts_id_old gl_sets_of_books.chart_of_accounts_id%type;
701 l_itr_enc_type_id_old igi_itr_charge_setup.encumbrance_type_id%type;
702 l_gl_user_id_old number;
703 Begin
704 -----------------------------------------------------------------------------------------------
705 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
706 l_debug_info := 'Retrieving system parameters for fundschecker';
707 -----------------------------------------------------------------------------------------------
708
709 l_chart_of_accounts_id_old := p_chart_of_accounts_id;
710 l_itr_enc_type_id_old := p_itr_enc_type_id;
711 l_gl_user_id_old := p_gl_user_id;
712
713 Select
714 nvl(gls.chart_of_accounts_id, -1),
715 nvl(igi.encumbrance_type_id, -1)
716 Into
717 p_chart_of_accounts_id,
718 p_itr_enc_type_id
719 From
720 igi_itr_charge_setup igi,
721 gl_sets_of_books gls
722 Where gls.set_of_books_id = p_set_of_books_id
723 And igi.set_of_books_id(+) = gls.set_of_books_id;
724
725 ----------------------------------------------------------------
726 l_debug_info := 'Retrieving profile option user id';
727 ----------------------------------------------------------------
728 Fnd_profile.Get('USER_ID', p_gl_user_id);
729 Exception
730 When Others Then
731 p_chart_of_accounts_id := l_chart_of_accounts_id_old;
732 p_itr_enc_type_id := l_itr_enc_type_id_old;
733 p_gl_user_id := l_gl_user_id_old;
734 If (SQLCODE <> -20001) Then
735 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
736 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
737 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
738 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
739
740 IF ( l_unexp_level >= l_debug_level) THEN
741 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
742 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
743 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
744 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Fundscheck_Init.msg5',TRUE);
745 END IF;
746
747 End If;
748 APP_EXCEPTION.RAISE_EXCEPTION;
749 End Fundscheck_Init;
750
751 Procedure Bc_Packets_Insert(
752 p_packet_id IN gl_bc_packets.packet_id%type,
753 p_set_of_books_id IN gl_bc_packets.ledger_id%type,
754 p_ccid IN gl_bc_packets.code_combination_id%type,
755 p_amount IN gl_bc_packets.entered_dr%type,
756 p_period_year IN gl_bc_packets.period_year%type,
757 p_period_num IN gl_bc_packets.period_num%type,
758 p_quarter_num IN gl_bc_packets.quarter_num%type,
759 p_gl_user IN gl_bc_packets.last_updated_by%type,
760 p_enc_type_id IN gl_bc_packets.encumbrance_type_id%type,
761 p_ref2 IN gl_bc_packets.reference2%type,
762 p_ref4 IN gl_bc_packets.reference4%type,
763 p_ref5 IN gl_bc_packets.reference5%type,
764 p_je_source IN gl_bc_packets.je_source_name%type,
765 p_je_category IN gl_bc_packets.je_category_name%type,
766 p_actual_flag IN gl_bc_packets.actual_flag%type,
767 p_period_name IN gl_bc_packets.period_name%type,
768 p_base_currency_code IN gl_bc_packets.currency_code%type,
769 p_status_code IN gl_bc_packets.status_code%type,
770 p_reversal_flag IN igi_itr_charge_lines_audit.reversal_flag%type,
771 p_status_flag IN igi_itr_charge_lines.status_flag%type,
772 p_prevent_encumbrance_flag IN igi_itr_charge_lines.prevent_encumbrance_flag%type,
773 p_charge_name IN igi_itr_charge_headers.name%type, --shsaxena for bug 2948237
774 --p_description IN varchar2,
775 p_calling_sequence IN varchar2) IS PRAGMA AUTONOMOUS_TRANSACTION;
776 l_ins_dr number := NULL;
777 l_ins_cr number := NULL;
778 l_debug_loc varchar2(30) := 'Bc_Packets_Insert';
779 l_curr_calling_sequence varchar2(2000);
780 l_debug_info varchar2(100);
781 l_charge_name igi_itr_charge_headers.name%type; --shsaxena for bug 2948237
782 l_session_id number := NULL;
783 l_serial_id number := NULL;
784 Begin
785
786 ------------------------------------------------------------------------------------------------
787 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
788 l_debug_info := 'Inserting record into gl_bc_packets';
789 ------------------------------------------------------------------------------------------------
790 -- Logic for switching
791 If ((p_status_flag in ('R','J') and p_reversal_flag = 'Y') -- Modification and Reversal
792 OR p_prevent_encumbrance_flag = 'Y') Then -- Unreservation then
793 l_ins_cr := p_amount;
794 Elsif p_status_flag = 'L' Then -- Cancellation
795 l_ins_dr := p_amount * -1;
796 Else
797 l_ins_dr := p_amount;
798 End If;
799
800 /* Start of changes for bug#6028574 to insert into manadatory columns of gl_bc_packets introduced in r12. */
801 BEGIN
802 SELECT s.audsid, s.serial# into l_session_id, l_serial_id
803 FROM v$session s, v$process p
804 WHERE s.paddr = p.addr
805 AND s.audsid = USERENV('SESSIONID');
806 EXCEPTION
807 WHEN OTHERS THEN
808 raise;
809 END;
810 /* End of changes for bug#6028574 */
811
812 Insert Into gl_bc_packets (
813 packet_id, ledger_id, je_source_name,
814 je_category_name, code_combination_id, actual_flag,
815 period_name, period_year, period_num,
816 quarter_num, currency_code, status_code,
817 last_update_date, last_updated_by, encumbrance_type_id,
818 entered_dr, entered_cr, accounted_dr,
819 accounted_cr, reference2, reference4,
820 reference5, je_line_description, session_id,
821 serial_id, application_id)
822 Values(
823 p_packet_id, p_set_of_books_id, p_je_source,
824 p_je_category, p_ccid, p_actual_flag,
825 p_period_name, p_period_year, p_period_num,
826 p_quarter_num, p_base_currency_code, p_status_code,
827 sysdate, p_gl_user, p_enc_type_id,
828 l_ins_dr, l_ins_cr, l_ins_dr,
829 l_ins_cr, p_ref2, p_ref4,
830 p_ref5, p_charge_name, l_session_id,
831 l_serial_id, 101); --shsaxena for bug 2948237 -- p_description);
832 COMMIT;
833 Exception
834 When Others Then
835 If (SQLCODE <> -20001) Then
836 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
837 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
838 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
839 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Packet Id = ' || to_char(p_packet_id)
840 ||', Set_of_books_id = '|| to_char(p_set_of_books_id)
841 ||', Je_source = '|| p_je_source
842 ||', Je_category = '|| p_je_category
843 ||', CCID = '|| to_char(p_ccid)
844 ||', Actual_flag = '|| p_actual_flag
845 ||', Period_name = '|| p_period_name
846 ||', Period_year = '|| to_char(p_period_year)
847 ||', Period_num = '|| to_char(p_period_num)
848 ||', Quarter_num = '|| to_char(p_quarter_num)
849 ||', Base_currency_code = '|| p_base_currency_code
850 ||', Status_code = '|| p_status_code
851 ||', Gl_user = '|| to_char(p_gl_user)
852 ||', Encumbrance Id = '|| to_char(p_enc_type_id)
853 ||', Entered Dr = '|| to_char(l_ins_dr)
854 ||', Entered Cr = '|| to_char(l_ins_cr)
855 ||', Ref 2 = '|| p_ref2
856 ||', Ref 4 = '|| p_ref4
857 ||', Ref 5 = '|| p_ref5
858 ||', Status Flag = '|| p_status_flag
859 ||', Prevent Encumbrance Flag = '|| p_prevent_encumbrance_flag
860 ||', Reversal_flag = '|| p_reversal_flag);
861 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
862
863 IF ( l_unexp_level >= l_debug_level) THEN
864 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
865 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
866 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
867 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.BC_Packets_Insert.msg6',TRUE);
868 END IF;
869
870 End If;
871 APP_EXCEPTION.RAISE_EXCEPTION;
872 End BC_Packets_Insert;
873
874 Procedure Process_Fundschk_Failure_Code(
875 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
876 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
877 p_status_flag IN igi_itr_charge_lines.status_flag%type,
878 p_prevent_encumbrance_flag IN igi_itr_charge_lines.prevent_encumbrance_flag%type,
879 p_packet_id IN igi_itr_charge_lines_audit.packet_id%type,
880 p_return_message_name IN OUT NOCOPY varchar2,
881 p_called_by IN varchar2,
882 p_rowid IN varchar2,
883 p_calling_sequence IN varchar2) IS
884 l_fc_result_code varchar2(3);
885 l_debug_loc varchar2(30) := 'Process_Fundschk_Failure_Code';
886 l_curr_calling_sequence varchar2(2000);
887 l_return_message_name_old varchar2(30);
888
889 Begin
890 -----------------------------------------------------------------------------------------------
891 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc||'<-'||p_calling_sequence;
892 -----------------------------------------------------------------------------------------------
893
894 l_return_message_name_old := p_return_message_name;
895
896 Get_Gl_Fundschk_Result_Code(
897 p_packet_id,
898 l_fc_result_code);
899
900 Itr_Enc_Update(
901 p_it_header_id,
902 p_it_service_line_id,
903 p_status_flag,
904 p_prevent_encumbrance_flag,
905 p_packet_id,
906 l_fc_result_code,
907 p_rowid);
908
909 If (l_fc_result_code in ('F00', 'F01', 'F02', 'F03', 'F04')) Then
910 p_return_message_name := 'IGI_ITR_FCK_INSUFFICIENT_FUNDS';
911 Elsif (l_fc_result_code = 'F20') Then
912 p_return_message_name := 'IGI_ITR_FCK_ACCT_FLEX_UNDEFINE';
913 Elsif (l_fc_result_code = 'F21') Then
914 p_return_message_name := 'IGI_ITR_FCK_ACCT_FLEX_EXPIRED';
915 Elsif (l_fc_result_code in ('F22', 'F23')) Then
916 p_return_message_name := 'IGI_ITR_FCK_ACCT_FLEX_NO_POST'; --This message is not available
917 Elsif (l_fc_result_code in ('F24', 'F25')) Then
918 p_return_message_name := 'IGI_ITR_FCK_INCORRECT_CALENDAR';
919 Elsif (l_fc_result_code in ('F26', 'F27')) Then
920 p_return_message_name := 'IGI_ITR_FCK_BUDGET_UNDEFINED';
921 ELSE -- return generic failure message --
922 p_return_message_name := 'IGI_ITR_FCK_FAILED_FUNDSCHECK';
923 End If;
924 Exception
925 When Others Then
926 p_return_message_name := l_return_message_name_old;
927 If (SQLCODE <> -20001) Then
928 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
929 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
930 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
931 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'ITR Header Id = '|| to_char(p_it_header_id)
932 ||', ITR Service Id = '|| to_char(p_it_service_line_id)
933 ||', Status Flag ' || p_status_flag
934 ||', Prevent Encumbrance Flag ' || p_prevent_encumbrance_flag
935 ||', Packet_id = '|| to_char(p_packet_id));
936
937 IF ( l_unexp_level >= l_debug_level) THEN
938 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
939 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
940 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
941 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Process_Fundschk_Failure_Code.msg7',TRUE);
942 END IF;
943
944 End If;
945 APP_EXCEPTION.RAISE_EXCEPTION;
946 End Process_Fundschk_Failure_Code;
947
948 Procedure Itr_Enc_Update(
949 p_it_header_id IN igi_itr_charge_headers.it_header_id%type,
950 p_it_service_line_id IN igi_itr_charge_lines.it_service_line_id%type,
951 p_status_flag IN varchar2,
952 p_prevent_encumbrance_flag IN varchar2,
953 p_packet_id IN number,
954 p_fc_result_code IN varchar2,
955 p_rowid IN varchar2) IS
956
957 l_debug_loc varchar2(30) := 'Itr_Enc_Update';
958 l_curr_calling_sequence varchar2(2000);
959 l_debug_info varchar2(100);
960 Begin
961 ---------------------------------------------------------------------
962 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc;
963 ---------------------------------------------------------------------
964 If (p_status_flag = 'L' or p_prevent_encumbrance_flag = 'Y') Then
965 -------------------------------------------------------------------------------------------
966 l_debug_info := 'Cancelled , Unreservation Lines , updating Charge Lines and Audit Tables';
967 -------------------------------------------------------------------------------------------
968 Update igi_itr_charge_lines
969 Set failed_funds_lookup_code = 'Y',
970 packet_id = p_packet_id
971 Where it_header_id = p_it_header_id
972 And it_service_line_id = p_it_service_line_id;
973
974 Update igi_itr_charge_lines_audit
975 Set failed_funds_lookup_code = 'Y',
976 packet_id = p_packet_id
977 Where it_header_id = p_it_header_id
978 And it_service_line_id = p_it_service_line_id
979 And rowid = p_rowid;
980 Else
981 -------------------------------------------------------------------------------------------
982 l_debug_info := 'Updating Charge Lines and Audit Tables';
983 -------------------------------------------------------------------------------------------
984 Update igi_itr_charge_lines
985 Set failed_funds_lookup_code = 'Y',
986 status_flag = 'F',
987 packet_id = p_packet_id
988 Where it_header_id = p_it_header_id
989 And it_service_line_id = p_it_service_line_id;
990
991 Update igi_itr_charge_lines_audit
992 Set failed_funds_lookup_code = 'Y',
993 status_flag = 'F',
994 packet_id = p_packet_id
995 Where it_header_id = p_it_header_id
996 And it_service_line_id = p_it_service_line_id
997 And rowid = p_rowid;
998 End if;
999 Exception
1000 When Others Then
1001 If (SQLCODE <> -20001) Then
1002 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
1003 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1004 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1005 FND_MESSAGE.SET_TOKEN('PARAMETERS','Header Id = '|| to_char(p_it_header_id)
1006 ||', Service Id = '|| to_char(p_it_service_line_id)
1007 ||', Packet_id = '|| to_char(p_packet_id)
1008 ||', Status Flag ' || p_status_flag
1009 ||', Prevent Encumbrance Flag ' || p_prevent_encumbrance_flag
1010 ||', Funds check code = ' || p_fc_result_code);
1011 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1012
1013 IF ( l_unexp_level >= l_debug_level) THEN
1014 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1015 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1016 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1017 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Itr_Enc_Update.msg8',TRUE);
1018 END IF;
1019
1020 End If;
1021 APP_EXCEPTION.RAISE_EXCEPTION;
1022 End Itr_Enc_Update;
1023
1024 Procedure Get_Gl_Fundschk_result_code(
1025 p_packet_id IN number,
1026 p_fc_result_code IN OUT NOCOPY varchar2) IS
1027 l_debug_loc varchar2(30) := 'Get_Gl_Fundschk_Result_Code';
1028 l_curr_calling_sequence varchar2(2000);
1029 l_fc_result_code_old varchar2(30);
1030
1031 Begin
1032 ---------------------------------------------------------------------
1033 l_curr_calling_sequence := 'IGI_ITR_FUNDS_CONTROL_PKG.'||l_debug_loc;
1034 ---------------------------------------------------------------------
1035
1036 l_fc_result_code_old := p_fc_result_code;
1037
1038 Select l.lookup_code
1039 Into p_fc_result_code
1040 From gl_lookups l
1041 Where lookup_type = 'FUNDS_CHECK_RESULT_CODE'
1042 And Exists (Select 'x'
1043 From gl_bc_packets bc
1044 Where result_code like 'F%'
1045 And bc.result_code = l.lookup_code
1046 And packet_id = p_packet_id)
1047 And rownum = 1;
1048 Exception
1049 When Others Then
1050 p_fc_result_code := l_fc_result_code_old;
1051 If (SQLCODE <> -20001) Then
1052 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
1053 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1054 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
1055 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Packet_id = '|| to_char(p_packet_id)
1056 || ', Fundschecker Result code = ' || p_fc_result_code);
1057
1058 IF ( l_unexp_level >= l_debug_level) THEN
1059 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
1060 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1061 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1062 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrhb.IGI_ITR_FUNDS_CONTROL_PKG.Get_Gl_Fundschk_Result_Code.msg9',TRUE);
1063 END IF;
1064 End If;
1065 APP_EXCEPTION.RAISE_EXCEPTION;
1066 End Get_Gl_Fundschk_Result_Code;
1067
1068 END IGI_ITR_FUNDS_CONTROL_PKG;