[Home] [Help]
PACKAGE BODY: APPS.IGC_CBC_PA_BC_PKG
Source
1 PACKAGE BODY IGC_CBC_PA_BC_PKG AS
2 /* $Header: IGCBPBCB.pls 120.8.12000000.4 2007/12/07 10:06:16 mbremkum ship $ */
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CBC_PA_BC_PKG';
7
8 g_debug VARCHAR2(1);
9 g_prod VARCHAR2(3) := 'IGC';
10 g_sub_comp VARCHAR2(3) := 'CPA';
11 g_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
12 g_mode VARCHAR2(1);
13
14 --g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
15 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
16
17 --Variables for ATG Central logging
18 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
19 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
20 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
22 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
23 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
24 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
25 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCBPBCB.IGC_CBC_PA_BC_PKG.';
26 l_full_path VARCHAR2(255);
27
28 PROCEDURE Put_Debug_Msg (
29 p_path IN VARCHAR2,
30 p_debug_msg IN VARCHAR2
31 );
32
33 PROCEDURE message_token(
34 tokname IN VARCHAR2,
35 tokval IN VARCHAR2
36 );
37
38 PROCEDURE add_message(
39 appname IN VARCHAR2,
40 msgname IN VARCHAR2
41 );
42
43
44 FUNCTION Get_H_Code (
45 p_header_id IN NUMBER ,
46 p_mode IN VARCHAR2,
47 p_actual_flag IN VARCHAR2,
48 p_doc_type IN VARCHAR2
49 ) RETURN VARCHAR2;
50
51 FUNCTION Validate_Interface (
52 p_header_id IN NUMBER ,
53 p_mode IN VARCHAR2,
54 p_actual_flag IN VARCHAR2,
55 p_doc_type IN VARCHAR2
56 ) RETURN BOOLEAN ;
57
58 FUNCTION Check_PA_BC (
59 p_project_id IN NUMBER
60 ) RETURN VARCHAR2;
61
62 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to function below
63 FUNCTION Unreserve_PA (
64 p_sobid IN NUMBER,
65 p_header_id IN NUMBER ,
66 p_mode IN VARCHAR2,
67 p_actual_flag IN VARCHAR2,
68 p_doc_type IN VARCHAR2,
69 p_pa_reserved IN BOOLEAN ,
70 p_cbc_reserved IN BOOLEAN,
71 p_packet_id IN NUMBER
72
73 )RETURN VARCHAR2;
74
75 FUNCTION Check_PA (
76 p_sobid IN NUMBER,
77 p_header_id IN NUMBER ,
78 p_mode IN VARCHAR2,
79 p_actual_flag IN VARCHAR2,
80 p_doc_type IN VARCHAR2,
81 p_pa_return_code OUT NOCOPY VARCHAR2
82
83 )RETURN BOOLEAN;
84
85 /*Added for Bug 6672778 - Start*/
86
87 FUNCTION Get_Batch_Result_Code (
88 p_header_id IN NUMBER,
89 p_mode IN VARCHAR2
90 )RETURN VARCHAR2;
91
92 /*Added for Bug 6672778 - End*/
93
94 /* ------------------------------------------------------------------------- */
95 /* */
96 /* PA Funds Check API for CC */
97 /* */
98 /* This routine returns TRUE if successful; otherwise, it returns FALSE */
99 /* */
100 /* In case of failure, this routine will populate the global Message Stack */
101 /* using FND_MESSAGE. The calling routine will retrieve the message from */
102 /* the Stack */
103 /* */
104 /* External Packages which are being invoked include : */
105 /* */
106 /* FND_* */
107 /* */
108 /* GL Tables which are being used include : */
109 /* */
110 /* GL_* */
111 /* */
112 /* AOL Tables which are being used include : */
113 /* */
114 /* FND_* */
115 /* */
116 /* Return status two characters. First one for CBC, second for SBC */
117 /* 'S' Success, */
118 /* 'A' Advisory, */
119 /* 'F' Failure */
120 /* 'T' Fatal */
121 /* 'N' No records */
122 /* ------------------------------------------------------------------------- */
123
124
125 FUNCTION IGCPAFCK(
126 p_sobid IN NUMBER,
127 p_header_id IN NUMBER,
128 p_mode IN VARCHAR2,
129 p_actual_flag IN VARCHAR2,
130 p_doc_type IN VARCHAR2,
131 p_ret_status OUT NOCOPY VARCHAR2,
132 p_batch_result_code OUT NOCOPY VARCHAR2,
133 p_debug IN VARCHAR2:=FND_API.G_FALSE,
134 p_conc_proc IN VARCHAR2:=FND_API.G_FALSE
135 /*Commented Packet ID for SLA Uptake*/
136 -- p_packet_id IN NUMBER
137 ) RETURN BOOLEAN IS
138
139 l_api_name CONSTANT VARCHAR2(30) := 'IGCPAFCK';
140 l_return_status VARCHAR2(1);
141 l_fc_return_status VARCHAR2(2);
142 l_batch_result_code VARCHAR2(3);
143 l_res VARCHAR2(2);
144 l_pa_bc_required BOOLEAN := FALSE;
145 l_pa_reserved BOOLEAN := FALSE;
146 l_cbc_reserved BOOLEAN := FALSE;
147 l_fc_boolean_status BOOLEAN := FALSE;
148 l_flag VARCHAR2(1);
149 l_pa_return_code VARCHAR2(1);
150 l_pa_overall_code VARCHAR2(1);
151 l_ret_status VARCHAR2(2);
152 p_packet_id NUMBER;
153 /* Cursor select all projects from CC account lines table */
154 CURSOR c_acct_info IS
155 SELECT DISTINCT project_id
156 FROM igc_cc_acct_lines
157 WHERE cc_acct_line_id IN
158 (SELECT cc_acct_line_id
159 FROM igc_cc_interface_v a
160 WHERE cc_header_id = p_header_id
161 AND budget_dest_flag = 'C'
162 AND actual_flag = p_actual_flag
163 AND document_type = p_doc_type )
164 AND project_id IS NOT NULL ;
165
166 l_full_path VARCHAR2(255);
167
168 BEGIN
169
170 l_full_path := g_path || 'IGCPAFCK';
171 /*Added for SLA Uptake Bug No 6341012 - Temporary*/
172 p_packet_id := NULL;
173
174 -- Standard Start of API savepoint
175
176 SAVEPOINT IGCPAFCK;
177
178 -- Initialize message list
179
180 FND_MSG_PUB.initialize;
181
182 --Initialize global variables
183
184 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(p_debug);
185
186 -- debug information
187
188 -- IF (NOT IGC_MSGS_PKG.g_debug_mode) OR (upper(fnd_profile.value('IGC_DEBUG_ENABLED')) ='Y') THEN
189 -- IGC_MSGS_PKG.g_debug_mode:=TRUE;
190 -- END IF;
191 /*IF (g_debug_mode <> 'Y') AND (p_debug = FND_API.G_TRUE)
192 IF(g_debug_mode <> 'Y')
193 THEN
194 g_debug_mode := 'Y';
195 END IF;*/
196
197 -- IF (IGC_MSGS_PKG.g_debug_mode) THEN
198 IF (g_debug_mode = 'Y') THEN
199 Put_Debug_Msg(l_full_path, substr('**************************************************************************************************',1,70));
200 Put_Debug_Msg(l_full_path, substr('*********Starting PA CBC Funds Checker '||to_char(sysdate,'DD-MON-YY:MI:SS')||' *********************',1,70));
201 Put_Debug_Msg(l_full_path, substr('**************************************************************************************************',1,70));
202 Put_Debug_Msg(l_full_path, 'Parameters SOB:' || p_sobid ||' Mode: ' || p_mode || ' HeaderID ' ||p_header_id);
203 END IF;
204
205 IF (g_debug_mode = 'Y') THEN
206 Put_Debug_Msg(l_full_path, 'Getting project info');
207 END IF;
208 g_debug := p_debug;
209 g_mode := p_mode;
210
211 FOR c_acct_info_rec IN c_acct_info LOOP
212
213 IF (g_debug_mode = 'Y') THEN
214 Put_Debug_Msg(l_full_path, 'Checking project setup '||c_acct_info_rec.project_id);
215 END IF;
216
217 --Call PA API and check setup
218 l_flag := Check_PA_BC(c_acct_info_rec.project_id);
219
220 IF FND_API.TO_BOOLEAN(l_flag) THEN
221 IF (g_debug_mode = 'Y') THEN
222 Put_Debug_Msg(l_full_path, 'Project Bc enabled');
223 END IF;
224 l_pa_bc_required:=TRUE;
225
226 UPDATE igc_cc_interface_v v1
227 SET v1.pa_flag = 'Y',
228 v1.result_code_source = 'P',
229 v1.period_name =
230 ( SELECT min(per.period_name)
231 FROM gl_period_statuses per
232 WHERE per.application_id = 101
233 AND per.adjustment_period_flag='N'
234 AND per.set_of_books_id = p_sobid
235 AND v1.cc_transaction_date
236 BETWEEN per.start_date
237 AND per.end_date
238 )
239 WHERE cc_header_id = p_header_id
240 AND budget_dest_flag = 'C'
241 AND actual_flag = p_actual_flag
242 AND document_type = p_doc_type
243 AND cc_acct_line_id IN
244 (SELECT cc_acct_line_id
245 FROM igc_cc_acct_lines
246 WHERE cc_header_id = p_header_id
247 AND project_id = c_acct_info_rec.project_id
248 AND project_id IS NOT NULL);
249
250 ELSE
251 IF (g_debug_mode = 'Y') THEN
252 Put_Debug_Msg(l_full_path, 'Project Bc disabled');
253 END IF;
254 END IF;
255
256
257 END LOOP; --End of interface update
258
259 IF l_pa_bc_required THEN
260
261 IF (g_debug_mode = 'Y') THEN
262 Put_Debug_Msg(l_full_path, 'Calling PA FC');
263 END IF;
264
265 --Call PA FC
266
267 l_fc_boolean_status := Check_PA (p_sobid => p_sobid,
268 p_header_id => p_header_id,
269 p_mode => p_mode,
270 p_actual_flag => p_actual_flag,
271 p_doc_type => p_doc_type,
272 p_pa_return_code => l_pa_return_code );
273
274
275 IF (g_debug_mode = 'Y') THEN
276 Put_Debug_Msg(l_full_path, 'PA FC result is '|| l_pa_return_code);
277 END IF;
278
279 l_pa_overall_code := l_pa_return_code;
280
281 IF l_pa_return_code IN ('A','S') AND p_mode IN ('F','R','U') THEN
282
283 l_pa_reserved := TRUE;
284
285 IF NOT Validate_Interface(p_header_id,p_mode,p_actual_flag,p_doc_type) THEN
286
287 IF (g_debug_mode = 'Y') THEN
288 Put_Debug_Msg(l_full_path, 'Post PA validation failed');
289 END IF;
290
291 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to call below
292 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
293
294 IF (g_debug_mode = 'Y') THEN
295 Put_Debug_Msg(l_full_path, 'Returning '||l_res);
296 END IF;
297
298 p_ret_status := l_res;
299
300 RETURN FALSE;
301
302 END IF;
303
304
305 ELSIF p_mode IN ('F','R','U') AND l_pa_return_code NOT IN ('A','S') THEN
306
307 IF (g_debug_mode = 'Y') THEN
308 Put_Debug_Msg(l_full_path, 'Calculatig H-code and returning to the user');
309 END IF;
310
311 p_batch_result_code := Get_H_Code(p_header_id ,
312 p_mode ,
313 p_actual_flag,
314 p_doc_type);
315
316 p_ret_status := l_pa_return_code||'N';
317
318 IF(l_pa_return_code ='T') THEN
319 RETURN FALSE;
320 END IF;
321
322 RETURN TRUE;
323
324 ELSIF p_mode = 'C' AND l_pa_return_code IN ('A','S') THEN
325
326 IF NOT Validate_Interface(p_header_id,p_mode,p_actual_flag,p_doc_type) THEN
327
328 IF (g_debug_mode = 'Y') THEN
329 Put_Debug_Msg(l_full_path, 'Post PA validation failed');
330 END IF;
331
332 p_ret_status := 'TN';
333
334 IF (g_debug_mode = 'Y') THEN
335 Put_Debug_Msg(l_full_path, 'Returning '||'TN');
336 END IF;
337
338 RETURN FALSE;
339
340 END IF;
341
342 ELSIF l_pa_return_code ='T' OR NOT l_fc_boolean_status THEN
343
344 IF (g_debug_mode = 'Y') THEN
345 Put_Debug_Msg(l_full_path, 'PA returned fatal exception - exiting ');
346 END IF;
347
348 p_ret_status := 'TN';
349
350 IF (g_debug_mode = 'Y') THEN
351 Put_Debug_Msg(l_full_path, 'Returning '||'TN');
352 END IF;
353
354 RETURN FALSE;
355
356 END IF;
357
358 END IF; --End of PA BC call
359
360 IF (g_debug_mode = 'Y') THEN
361 Put_Debug_Msg(l_full_path, 'Calling CBC FC');
362 END IF;
363
364 l_fc_boolean_status := IGC_CBC_FUNDS_CHECKER.IGCFCK(p_sobid => p_sobid,
365 p_header_id => p_header_id,
366 p_mode => p_mode,
367 p_actual_flag => p_actual_flag,
368 p_doc_type => p_doc_type,
369 p_ret_status => l_fc_return_status,
370 p_batch_result_code => l_batch_result_code,
371 p_debug => p_debug,
372 p_conc_proc => p_conc_proc
373 -- p_packet_id => p_packet_id
374 );
375 IF l_fc_boolean_status
376 AND substr(l_fc_return_status,1,1) IN ('S','A','N')
377 AND substr(l_fc_return_status,2,1) IN ('S','A','N')
378 AND p_mode IN ('F','R','U') THEN
379
380 IF (g_debug_mode = 'Y') THEN
381 Put_Debug_Msg(l_full_path, 'Successfull execution');
382 END IF;
383
384 l_cbc_reserved := TRUE;
385
386 IF l_pa_reserved THEN
387
388 IF (g_debug_mode = 'Y') THEN
389 Put_Debug_Msg(l_full_path, 'Calling PA confirm reservation');
390 END IF;
391
392 -- Do PA confirm reservation
393 l_fc_boolean_status := Check_PA (p_sobid => p_sobid,
394 p_header_id => p_header_id,
395 p_mode => 'N',
396 p_actual_flag => p_actual_flag,
397 p_doc_type => p_doc_type,
398 p_pa_return_code => l_pa_return_code );
399
400 /*Added for Bug 6672778*/
401 l_batch_result_code := Get_Batch_Result_Code (p_header_id, p_mode);
402
403 IF l_pa_return_code <> 'S' THEN
404
405 IF (g_debug_mode = 'Y') THEN
406 Put_Debug_Msg(l_full_path, 'Failure during PA confirm reservation, unreserving..');
407 END IF;
408
409 l_pa_reserved := FALSE;
410
411 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to call below
412 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
413
414 p_ret_status := l_res;
415
416 p_batch_result_code := NULL;
417
418 RETURN FALSE;
419
420 END IF;
421 END IF;
422
423 ELSIF p_mode IN ('F','R','U') THEN --FC failed
424
425 IF (g_debug_mode = 'Y') THEN
426 Put_Debug_Msg(l_full_path, 'FC failed status: '||l_fc_return_status);
427 END IF;
428
429 IF l_pa_reserved THEN
430
431 -- ssmales 31/01/02 bug 2201905 - adde p_packet_id to call below
432 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
433
434 IF substr(l_res,1,1) = 'U' THEN
435
436 p_ret_status := 'U'||substr(l_fc_return_status,2,1);
437
438 p_batch_result_code :=NULL;
439
440 RETURN FALSE;
441
442 END IF;
443
444 END IF;
445
446 ELSE --Mode check - just call confirmation, assign the result code.
447
448 -- ssmales 29/01/02 bug 2201905 - not actually part of this bug, but code below was wrong.
449 -- should only call Check_PA if l_pa_bc_required is True, so If statement added
450
451 IF l_pa_bc_required THEN
452 l_fc_boolean_status := Check_PA (p_sobid => p_sobid,
453 p_header_id => p_header_id,
454 p_mode => 'N',
455 p_actual_flag => p_actual_flag,
456 p_doc_type => p_doc_type,
457 p_pa_return_code => l_pa_return_code );
458
459 /*Added for Bug 6672778*/
460 l_batch_result_code := Get_Batch_Result_Code (p_header_id, p_mode);
461
462 END IF ;
463
464 END IF;
465
466 IF l_pa_overall_code IS NOT NULL THEN
467
468 IF l_pa_overall_code = 'A' AND substr(l_fc_return_status,1,1) = 'S' THEN
469
470 l_ret_status := 'A'||substr(l_fc_return_status,2,1) ;
471
472 ELSIF l_pa_overall_code = 'F' AND substr(l_fc_return_status,1,1) IN ('S','A') THEN
473
474 l_ret_status := 'F'||substr(l_fc_return_status,2,1) ;
475
476 ELSE
477
478 l_ret_status := l_fc_return_status;
479
480 END IF;
481
482 ELSE
483 l_ret_status := l_fc_return_status;
484 END IF;
485
486 IF (g_debug_mode = 'Y') THEN
487 Put_Debug_Msg(l_full_path, 'PA status '||l_pa_overall_code);
488 Put_Debug_Msg(l_full_path, 'CBC status '||l_fc_return_status);
489 END IF;
490
491 p_ret_status := l_ret_status;
492
493 IF (g_debug_mode = 'Y') THEN
494 Put_Debug_Msg(l_full_path, 'Returning result'||l_batch_result_code||' '||l_ret_status);
495 END IF;
496
497 p_batch_result_code := l_batch_result_code;
498
499 RETURN TRUE;
500
501 EXCEPTION
502
503 WHEN FND_API.G_EXC_ERROR THEN
504
505 IF (g_debug_mode = 'Y') THEN
506 Put_Debug_Msg(l_full_path, 'Execution error occured');
507 END IF;
508
509 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to call below
510 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
511
512 p_ret_status := l_res;
513
514 p_batch_result_code := NULL;
515
516 IF (g_excep_level >= g_debug_level ) THEN
517 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
518 END IF;
519
520 RETURN(FALSE);
521
522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
523
524 IF (g_debug_mode = 'Y') THEN
525 Put_Debug_Msg(l_full_path, 'Unexpected error occured');
526 END IF;
527
528 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to call below
529 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
530
531 p_ret_status := l_res;
532
533 p_batch_result_code := NULL;
534
535 IF (g_excep_level >= g_debug_level ) THEN
536 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_UNEXPECTED_ERROR Exception Raised');
537 END IF;
538
539 RETURN(FALSE);
540
541 WHEN OTHERS THEN
542
543 IF (g_debug_mode = 'Y') THEN
544 Put_Debug_Msg(l_full_path, 'Unknown error occured');
545 END IF;
546
547 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
548 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
549 END IF;
550
551 -- ssmales 31/01/02 bug 2201905 - added p_packet_id to call below
552 l_res := Unreserve_PA(p_sobid,p_header_id,p_mode,p_actual_flag,p_doc_type,l_pa_reserved,l_cbc_reserved,p_packet_id);
553
554 p_ret_status := l_res;
555
556 p_batch_result_code := NULL;
557
558 IF ( g_unexp_level >= g_debug_level ) THEN
559 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
560 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
561 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
562 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
563 END IF;
564
565 RETURN (FALSE);
566
567 END IGCPAFCK;
568
569 /*Code change for Bug 6672778 - Start*/
570
571 FUNCTION Get_Batch_Result_Code (
572 p_header_id IN NUMBER,
573 p_mode IN VARCHAR2
574 ) RETURN VARCHAR2 IS
575
576 l_h_code VARCHAR2(3);
577 l_sev_rank NUMBER;
578 l_min_sev_rank NUMBER;
579 l_full_path VARCHAR2(255);
580
581 CURSOR c_result_code IS
582 SELECT distinct cbc_result_code, cc_header_id
583 FROM igc_cc_interface
584 WHERE cc_header_id = p_header_id;
585
586 BEGIN
587
588 l_min_sev_rank := 9999;
589 l_full_path := g_path || 'Get_Batch_Result_Code';
590
591 IF ( g_unexp_level >= g_debug_level ) THEN
592 Put_Debug_Msg(l_full_path, 'Header ID: ' || p_header_id || ' Mode: ' || p_mode);
593 END IF;
594
595 FOR l_result_code IN c_result_code LOOP
596
597 SELECT distinct severity_rank INTO l_sev_rank
598 FROM igc_cc_result_code_ranks
599 WHERE funds_checker_code = l_result_code.cbc_result_code;
600
601 IF (l_min_sev_rank > l_sev_rank) THEN
602 l_min_sev_rank := l_sev_rank;
603 END IF;
604
605 END LOOP;
606
607 IF ( g_unexp_level >= g_debug_level ) THEN
608 Put_Debug_Msg(l_full_path, 'Minimum Severity Rank: ' || l_min_sev_rank);
609 END IF;
610
611 SELECT distinct popup_messg_code INTO l_h_code
612 FROM igc_cc_result_code_ranks
613 WHERE severity_rank = l_min_sev_rank
614 AND action = decode(p_mode,'F','R',p_mode);
615
616 IF ( g_unexp_level >= g_debug_level ) THEN
617 Put_Debug_Msg(l_full_path, 'Pop Up Message Code: ' || l_h_code);
618 END IF;
619
620 RETURN l_h_code;
621
622 END Get_Batch_Result_Code;
623
624 /*Code change for Bug 6672778 - End*/
625
626 PROCEDURE Put_Debug_Msg (
627 p_path IN VARCHAR2,
628 p_debug_msg IN VARCHAR2
629 ) IS
630
631 -- Constants :
632
633 /*l_Return_Status VARCHAR2(1);*/
634 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
635
636 BEGIN
637
638 IF(g_state_level >= g_debug_level) THEN
639 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
640 END IF;
641
642 RETURN;
643
644 -- --------------------------------------------------------------------
645 -- Exception handler section for the Put_Debug_Msg procedure.
646 -- --------------------------------------------------------------------
647 EXCEPTION
648
649 /*WHEN FND_API.G_EXC_ERROR THEN
650 RETURN;*/
651
652 WHEN OTHERS THEN
653 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
654 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
655 END IF;
656 NULL;
657 RETURN;
658 END Put_Debug_Msg;
659
660 /****************************************************************************/
661
662 -- Add Token and Value to the Message Token array
663
664 PROCEDURE message_token(
665 tokname IN VARCHAR2,
666 tokval IN VARCHAR2
667 ) IS
668
669 BEGIN
670
671 IGC_MSGS_PKG.message_token (p_tokname => tokname,
672 p_tokval => tokval);
673
674 END message_token;
675
676
677 /****************************************************************************/
678
679 -- Sets the Message Stack
680
681 PROCEDURE add_message(
682 appname IN VARCHAR2,
683 msgname IN VARCHAR2
684 ) IS
685
686 i BINARY_INTEGER;
687
688 BEGIN
689
690 IGC_MSGS_PKG.add_message (p_appname => appname,
691 p_msgname => msgname);
692
693 END add_message;
694
695 /* Procedure unreserves PA and CBC
696 returns two caracters one for CBC(PA) and the second for SBC
697 T if successfully unreserved and U if not */
698
699 -- ssmales 31/01/02 bug 2201905 added p_packet_id to function below
700 FUNCTION Unreserve_PA (
701 p_sobid IN NUMBER,
702 p_header_id IN NUMBER ,
703 p_mode IN VARCHAR2,
704 p_actual_flag IN VARCHAR2,
705 p_doc_type IN VARCHAR2,
706 p_pa_reserved IN BOOLEAN ,
707 p_cbc_reserved IN BOOLEAN,
708 p_packet_id IN NUMBER
709
710 )
711 RETURN VARCHAR2 IS
712
713 l_fc_return_status VARCHAR2(2);
714 l_cbc_return_status VARCHAR2(2);
715 l_batch_result_code VARCHAR2(3);
716 l_pa_return_status VARCHAR2(2);
717 l_fc_boolean_status BOOLEAN;
718 l_return_code VARCHAR2(1);
719 l_full_path VARCHAR2(255);
720 BEGIN
721
722 l_full_path := g_path || 'Unreserve_PA';
723
724 l_fc_return_status := 'TN';
725
726 IF (g_debug_mode = 'Y') THEN
727 Put_Debug_Msg(l_full_path, 'Unreservation module called');
728 END IF;
729
730 l_pa_return_status := 'T';
731
732 IF p_pa_reserved THEN
733
734 IF (g_debug_mode = 'Y') THEN
735 Put_Debug_Msg(l_full_path, 'PA unreservation reqired');
736 END IF;
737
738 l_return_code := 'F';
739
740 PA_FUNDS_CONTROL_PKG.PA_GL_CBC_CONFIRMATION(
741 P_calling_module => 'CBC' ,
742 P_packet_id => NULL,
743 P_mode => p_mode,
744 P_reference1 => 'CC',
745 P_reference2 => p_header_id,
746 P_gl_cbc_return_code => l_return_code,
747 x_return_status => l_pa_return_status);
748
749 IF (g_debug_mode = 'Y') THEN
750 Put_Debug_Msg(l_full_path, 'Return status is:'||l_pa_return_status);
751 END IF;
752
753 IF l_pa_return_status <> 'T' THEN
754 IF (g_debug_mode = 'Y') THEN
755 Put_Debug_Msg(l_full_path, 'Successfully unreserved, status'||l_pa_return_status);
756 END IF;
757 l_pa_return_status := 'T';
758 ELSE
759 --Unreservation failed - return U.
760 IF (g_debug_mode = 'Y') THEN
761 Put_Debug_Msg(l_full_path, 'Failed status'||l_pa_return_status);
762 END IF;
763 l_pa_return_status := 'U';
764 l_fc_return_status := 'UN';
765 END IF;
766 END IF;
767
768
769 IF p_cbc_reserved THEN
770
771 IF (g_debug_mode = 'Y') THEN
772 Put_Debug_Msg(l_full_path, 'CBC unreservation reqired');
773 END IF;
774
775 l_fc_boolean_status := IGC_CBC_FUNDS_CHECKER.IGCFCK(p_sobid => p_sobid,
776 p_header_id => p_header_id,
777 p_mode => 'U',
778 p_actual_flag => p_actual_flag,
779 p_doc_type => p_doc_type,
780 p_ret_status => l_fc_return_status,
781 p_batch_result_code => l_batch_result_code,
782 p_debug => g_debug,
783 p_conc_proc => 'F'
784 -- p_packet_id => p_packet_id
785 );
786
787
788
789 IF substr(l_fc_return_status,2,1) = 'S' THEN --Need to convert S SBC message to T
790
791 l_fc_return_status := substr(l_fc_return_status,1,1)||'T';
792
793 END IF;
794
795 IF l_fc_boolean_status THEN
796
797 IF (g_debug_mode = 'Y') THEN
798 Put_Debug_Msg(l_full_path, 'Successfully unreserved, CBC FC return status '||l_fc_return_status);
799 END IF;
800
801 l_fc_return_status := l_pa_return_status||substr(l_fc_return_status,2,1);
802
803 ELSE
804 --Unreservation failed - return U.
805 IF (g_debug_mode = 'Y') THEN
806 Put_Debug_Msg(l_full_path, 'Failed CBC FC return status '||l_fc_return_status);
807 END IF;
808
809 IF l_pa_return_status = 'U' THEN --Asssign U as CBC result anyway
810
811 l_fc_return_status := 'U'||substr(l_fc_return_status,2,1);
812
813 END IF;
814
815 END IF;
816
817 END IF;
818
819 IF (g_debug_mode = 'Y') THEN
820 Put_Debug_Msg(l_full_path, 'Overall unreservation status '||l_fc_return_status);
821 END IF;
822
823 RETURN l_fc_return_status;
824
825 END Unreserve_PA;
826
827
828 /* This functon calculates the H-code based on PA provided result codes */
829 FUNCTION Get_H_Code (
830 p_header_id IN NUMBER ,
831 p_mode IN VARCHAR2,
832 p_actual_flag IN VARCHAR2,
833 p_doc_type IN VARCHAR2
834 )RETURN VARCHAR2 IS
835
836 l_h_code VARCHAR2(3);
837 l_rank NUMBER;
838 l_full_path VARCHAR2(255);
839 BEGIN
840
841 l_full_path := g_path || 'Get_H_Code';
842
843 IF (g_debug_mode = 'Y') THEN
844 Put_Debug_Msg(l_full_path, 'Caclulating H-code');
845 END IF;
846
847 SELECT min(IGC_CBC_FUNDS_CHECKER.Get_Rank(cbc_result_code))
848 INTO l_rank
849 FROM igc_cc_interface_v
850 WHERE cc_header_id = p_header_id
851 AND budget_dest_flag = 'C'
852 AND actual_flag = p_actual_flag
853 AND document_type = p_doc_type
854 AND cbc_result_code IS NOT NULL;
855
856 l_h_code := IGC_CBC_FUNDS_CHECKER.Get_Batch_Result_Code(p_mode,l_rank);
857
858 IF (g_debug_mode = 'Y') THEN
859 Put_Debug_Msg(l_full_path, 'H-code found: '||l_h_code );
860 END IF;
861
862 RETURN l_h_code;
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN --No result codes in the table after PA.Probably Unexpected error raised
865
866 IF (g_debug_mode = 'Y') THEN
867 Put_Debug_Msg(l_full_path, 'No records with result code, H-code is null');
868 END IF;
869
870 IF ( g_unexp_level >= g_debug_level ) THEN
871 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
872 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
873 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
874 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
875 END IF;
876
877 RETURN '';
878
879 END Get_H_Code;
880
881
882 /* This procedure validates Interface table and checks the following information to be correct:
883 The PA -related lines have a result code. The lines cretaed by PA for budget
884 liquidation have the same CCID as the target lines */
885 FUNCTION Validate_Interface (
886 p_header_id IN NUMBER ,
887 p_mode IN VARCHAR2,
888 p_actual_flag IN VARCHAR2,
889 p_doc_type IN VARCHAR2
890 )
891 RETURN BOOLEAN IS
892
893 --This cursor returns lines which have the same cc_acct_line_id but different code combinations from CC and PA.
894 CURSOR c_int IS
895 SELECT count(DISTINCT CODE_COMBINATION_ID)
896 FROM igc_cc_interface_v a
897 WHERE cc_header_id = p_header_id
898 AND budget_dest_flag = 'C'
899 AND actual_flag = p_actual_flag
900 AND document_type = p_doc_type
901 AND pa_flag ='Y'
902 HAVING count(DISTINCT CODE_COMBINATION_ID) > 1
903 GROUP BY cc_acct_line_id;
904
905 l_result_count NUMBER := 0;
906 l_full_path VARCHAR2(255);
907
908 BEGIN
909
910 l_full_path := g_path || 'Validate_Interface';
911
912 OPEN c_int;
913 FETCH c_int
914 INTO l_result_count;
915
916
917 IF c_int%FOUND THEN
918 CLOSE c_int;
919 add_message ('IGC', 'IGC_CBC_CCID_NOT_MATCH'); -- There are records with not matched CCID
920 RETURN FALSE;
921 END IF;
922
923 CLOSE c_int;
924
925 RETURN TRUE;
926
927 END Validate_Interface;
928
929
930 FUNCTION Check_PA_BC (
931 p_project_id IN NUMBER
932 ) RETURN VARCHAR2 IS
933 l_full_path VARCHAR2(255);
934 BEGIN
935
936 l_full_path := g_path || 'Check_PA_BC';
937
938 IF PA_BUDGET_FUND_PKG.Is_bdgt_intg_enabled (p_project_id => p_project_id,
939 p_mode => 'C' )
940 THEN
941 RETURN FND_API.G_TRUE;
942 END IF;
943
944 RETURN FND_API.G_FALSE;
945
946 END Check_PA_BC;
947
948 FUNCTION Check_PA (
949 p_sobid IN NUMBER,
950 p_header_id IN NUMBER ,
951 p_mode IN VARCHAR2,
952 p_actual_flag IN VARCHAR2,
953 p_doc_type IN VARCHAR2,
954 p_pa_return_code OUT NOCOPY VARCHAR2
955
956 )RETURN BOOLEAN IS
957 l_pa_return_code VARCHAR2(1) := 'S';
958 l_cbc_return_code VARCHAR2(1) := 'S';
959 l_return_status VARCHAR2(1);
960 l_return_code VARCHAR2(1);
961 l_stage VARCHAR2(2000);
962 l_err_msg VARCHAR2(2000);
963 l_err_count NUMBER(10);
964 l_full_path VARCHAR2(255);
965 BEGIN
966
967 l_full_path := g_path || 'Check_PA';
968
969 IF p_mode ='N' THEN
970 IF (g_debug_mode = 'Y') THEN
971 Put_Debug_Msg(l_full_path, 'Calling PA FC in confirmation mode');
972 END IF;
973
974 l_return_code := 'S';
975
976 PA_FUNDS_CONTROL_PKG.PA_GL_CBC_CONFIRMATION(
977 P_calling_module => 'CBC' ,
978 P_packet_id => NULL,
979 P_mode => g_mode,
980 P_reference1 => 'CC',
981 P_reference2 => p_header_id,
982 p_gl_cbc_return_code => l_return_code,
983 x_return_status => l_return_status);
984
985 IF (g_debug_mode = 'Y') THEN
986 Put_Debug_Msg(l_full_path, 'Return status is:'||l_return_status);
987 END IF;
988
989 p_pa_return_code := l_return_status;
990
991 IF (l_return_status <> 'S' ) THEN
992 RETURN FALSE;
993 ELSE
994 RETURN TRUE;
995 END IF;
996
997 ELSE
998 --Mode not confirmation
999 -- bug 2689651 : Added call in force mode: Start
1000 IF p_mode = 'F' THEN
1001
1002 PA_FUNDS_CONTROL_PKG.PA_GL_CBC_CONFIRMATION(
1003 P_calling_module => 'CBC' ,
1004 P_packet_id => NULL,
1005 P_mode => p_mode,
1006 P_reference1 => 'CC',
1007 P_reference2 => p_header_id,
1008 p_gl_cbc_return_code => l_return_code,
1009 x_return_status => l_return_status);
1010
1011 p_pa_return_code := l_return_status;
1012
1013 IF (l_return_status <> 'S' ) THEN
1014 RETURN FALSE;
1015 END IF;
1016 END IF;
1017 -- bug 2689651 : Added call in force mode: end
1018
1019 IF PA_FUNDS_CONTROL_PKG.PA_FUNDS_CHECK(
1020 P_calling_module => 'CBC' ,
1021 P_set_of_book_id => p_sobid ,
1022 P_packet_id => NULL,
1023 P_mode => p_mode,
1024 P_partial_flag => 'N' ,
1025 P_reference1 => 'CC',
1026 P_reference2 => p_header_id,
1027 X_return_status => l_pa_return_code ,
1028 X_error_msg =>l_err_msg ,
1029 X_error_stage => l_stage )
1030 THEN
1031
1032 p_pa_return_code := l_pa_return_code;
1033
1034 IF (g_debug_mode = 'Y') THEN
1035 Put_Debug_Msg(l_full_path, 'PA return status is:'||l_pa_return_code||' message '||l_err_msg||' stage '||l_stage);
1036 END IF;
1037
1038 ELSE
1039 IF (g_debug_mode = 'Y') THEN
1040 Put_Debug_Msg(l_full_path, 'PA FC returned FALSE, return status is:'||l_pa_return_code||' message '||l_err_msg||' stage '||l_stage);
1041 END IF;
1042
1043 p_pa_return_code := 'T';
1044
1045 RETURN FALSE;
1046 END IF;
1047
1048 END IF;
1049
1050 RETURN TRUE;
1051
1052 END;
1053
1054 END IGC_CBC_PA_BC_PKG;
1055