[Home] [Help]
PACKAGE BODY: APPS.IGC_CBC_FUNDS_CHECKER
Source
1 PACKAGE BODY IGC_CBC_FUNDS_CHECKER AS
2 /* $Header: IGCBEFCB.pls 120.30.12010000.2 2008/09/30 09:53:27 sharoy ship $ */
3 -- Types :
4
5 /*R12 Uptake Refer Bug No : 6341012 - Start*/
6
7 TYPE g_cc_interface_head_rec_type IS RECORD
8 (CC_HEADER_ID igc_cc_interface.cc_header_id%TYPE,
9 DOCUMENT_TYPE igc_cc_interface.DOCUMENT_TYPE%TYPE,
10 BUDGET_DEST_FLAG igc_cc_interface.BUDGET_DEST_FLAG%TYPE,
11 REFERENCE_4 igc_cc_interface.REFERENCE_4%TYPE,
12 CC_TRANSACTION_DATE igc_cc_interface.CC_TRANSACTION_DATE%TYPE,
13 EVENT_ID igc_cc_interface.EVENT_ID%TYPE,
14 CC_DET_PF_LINE_ID igc_cc_interface.CC_DET_PF_LINE_ID%TYPE
15 );
16 TYPE g_cc_interface_head_tbl_type IS TABLE OF g_cc_interface_head_rec_type;
17 g_cc_interface_head_tbl g_cc_interface_head_tbl_type;
18
19 TYPE g_xla_events_gt_rec_type IS RECORD
20 (
21 EVENT_ID psa_bc_xla_events_gt.event_id%TYPE,
22 RESULT_CODE psa_bc_xla_events_gt.result_code%TYPE
23 );
24 TYPE g_xla_events_gt_tbl_type IS TABLE OF g_xla_events_gt_rec_type;
25 g_xla_events_gt_tbl g_xla_events_gt_tbl_type;
26
27 TYPE g_num_rec IS TABLE OF NUMBER;
28
29 /*R12 Uptake Refer Bug No : 6341012 - End*/
30
31 -- Private Global Variables :
32 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CBC_FUNDS_CKECKER';
33 g_debug VARCHAR2(10000);
34 g_conc_proc BOOLEAN := FALSE;
35 g_mode VARCHAR2(1);
36
37 g_cc_header_id igc_cc_interface.cc_header_id%TYPE;
38 /*
39 g_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
40 */
41 /*R12 Uptake Refer Bug No : 6341012 - Start*/
42 g_set_of_books_id gl_ledgers.ledger_id%TYPE;
43 g_cbc_ledger_id gl_ledgers.ledger_id%TYPE;
44 g_cbc_ledger_name VARCHAR2(50);
45 /*R12 Uptake Refer Bug No : 6341012 - End*/
46 g_actual_flag VARCHAR2(1);
47 g_update_login igc_cc_interface.last_update_login%TYPE;
48 g_update_by igc_cc_interface.last_updated_by%TYPE;
49 g_resp_id NUMBER;
50 g_maxloops NUMBER(10) := 50;
51 g_seconds NUMBER(10) := 2;
52 g_cbc_flag BOOLEAN :=TRUE;
53 g_sbc_flag BOOLEAN :=TRUE;
54 g_sbc_status VARCHAR2(1);
55 g_cbc_status VARCHAR2(1);
56 g_date1 NUMBER;
57 g_date2 NUMBER;
58 /*Need to comment*/
59 --Encumbrance types are seeded through SLA Bug No 6341012
60 g_summary_line_num IGC_CBC_JE_LINES.cbc_je_line_num%TYPE;
61 g_com_enc_id IGC_CBC_JE_LINES.encumbrance_type_id%TYPE;
62 g_obl_enc_id IGC_CBC_JE_LINES.encumbrance_type_id%TYPE;
63
64 g_cbc_enabled VARCHAR2(1);
65 g_doc_type IGC_CC_INTERFACE.document_type%TYPE;
66 g_validation_error BOOLEAN;
67 g_batch_result_code VARCHAR2(4); --Global maximum rank
68 g_efc_enabled VARCHAR2(1);
69 g_prod VARCHAR2(3) := 'IGC';
70 g_sub_comp VARCHAR2(3) := 'CBC';
71 g_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
72 g_group_severity_level VARCHAR2(1);
73 g_source VARCHAR2(255) ;
74 g_category VARCHAR2(255) ;
75 g_packet_id NUMBER;
76 g_gl_application_id fnd_application.application_id%TYPE;
77 g_cc_application_id fnd_application.application_id%TYPE; --R12 Uptake Refer Bug No 6341012
78 -- Variables for logging levels
79 --bug 3199488
80 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
81 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
82 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
83 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
84 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
85 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
86 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
87 g_path VARCHAR2(255) := 'IGC.PLSQL.IGCBEFCB.IGC_CBC_FUNDS_CHECKER.';
88
89 --bug 3199488
90 -- ssmales 25/01/02 bug 2201905 - added g_p_packet_id
91 g_p_packet_id NUMBER(15) ;
92 g_called_from_PO BOOLEAN := FALSE;
93 --bug 3199488
94 --g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
95 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
96 --g_legal_entity_id NUMBER; --R12 Uptake Refer Bug No 6341012
97
98 g_ledger_tbl PSA_FUNDS_CHECKER_PKG.num_rec;
99 g_event_tbl PSA_FUNDS_CHECKER_PKG.num_rec;
100
101 g_event_ind NUMBER;
102 g_ledger_ind NUMBER;
103
104 -- Private Function Definition:
105
106 /*R12 Uptake. Bug No 6341012 - Start*/
107
108 FUNCTION Get_Max_Result_Code(x_sev_rank OUT NOCOPY NUMBER) RETURN VARCHAR2;
109
110 PROCEDURE POPULATE_INTERFACE_TBL(
111 p_return_status OUT NOCOPY VARCHAR2
112 );
113
114 PROCEDURE PROCESS_CC_INT_LINES(
115 p_budget_dest_flag IN VARCHAR2,
116 p_mode IN VARCHAR2,
117 x_return_status OUT NOCOPY VARCHAR2
118 );
119
120 PROCEDURE GET_EVENT_DETAILS(
121 x_entity_type OUT NOCOPY VARCHAR2,
122 x_event_type_code OUT NOCOPY VARCHAR2,
123 p_org_id IN OUT NOCOPY NUMBER
124 );
125
126 PROCEDURE PROCESS_RESULTS(
127 x_ret_status OUT NOCOPY VARCHAR2,
128 x_batch_result_code OUT NOCOPY VARCHAR2
129 );
130
131 PROCEDURE UNDO_GL_BC_PACKETS(
132 p_ledger_array IN PSA_FUNDS_CHECKER_PKG.num_rec,
133 p_event_array IN PSA_FUNDS_CHECKER_PKG.num_rec,
134 p_return_status OUT NOCOPY VARCHAR2
135 );
136
137 PROCEDURE Get_Status(
138 x_sev_rank OUT NOCOPY NUMBER
139 );
140
141 PROCEDURE Update_Event_ID;
142
143 PROCEDURE Restore_events_gt;
144
145 PROCEDURE Set_Batch_Result_Code(
146 p_code IN VARCHAR2) ;
147
148 /*R12 Uptake. Bug No 6341012 - End*/
149
150 --Procedure, for registering time of execution of any operation
151 PROCEDURE Register_time(
152 p_name VARCHAR2,
153 p_mode BOOLEAN
154 );
155
156 PROCEDURE Put_Debug_Msg (
157 p_path IN VARCHAR2,
158 p_debug_msg IN VARCHAR2
159 );
160
161
162 /* ------------------------------------------------------------------------- */
163 /* */
164 /* Funds Check API for CC and PSB whenever Funds Check and/or Funds */
165 /* Funds Reservation need to be performed. */
166 /* */
167 /* This routine returns TRUE if successful; otherwise, it returns FALSE */
168 /* */
169 /* In case of failure, this routine will populate the global Message Stack */
170 /* using FND_MESSAGE. The calling routine will retrieve the message from */
171 /* the Stack */
172 /* */
173 /* External Packages which are being invoked include : */
174 /* */
175 /* FND_* */
176 /* */
177 /* GL Tables which are being used include : */
178 /* */
179 /* GL_* */
180 /* */
181 /* AOL Tables which are being used include : */
182 /* */
183 /* FND_* */
184 /* */
185 /* Return status two characters. First one for CBC, second for SBC */
186 /* 'S' Success, */
187 /* 'A' Advisory, */
188 /* 'F' Failure */
189 /* 'T' Fatal */
190 /* 'N' No records */
191 /* 'U' Unreservation failed */
192 /* ------------------------------------------------------------------------- */
193 -- Parameters :
194 -- p_sobid : set of books ID
195 -- p_header_id : CC header ID
196 -- p_mode : funds check mode - 'C', 'R' or 'F'
197 -- p_ret_status : return status of funds checking/reservation
198 -- p_actual_flag: 'E' for CC or 'B' for PSB
199 -- ssmales 25/01/02 bug 2201905 - added parameter p_packet_id
200 FUNCTION IGCFCK(
201 p_sobid IN NUMBER,
202 p_header_id IN NUMBER,
203 p_mode IN VARCHAR2,
204 p_actual_flag IN VARCHAR2,
205 p_doc_type IN VARCHAR2,
206 p_ret_status OUT NOCOPY VARCHAR2,
207 p_batch_result_code OUT NOCOPY VARCHAR2,
208 p_debug IN VARCHAR2:=FND_API.G_FALSE,
209 p_conc_proc IN VARCHAR2:=FND_API.G_FALSE
210 -- p_packet_id IN NUMBER
211 ) RETURN BOOLEAN IS
212 CURSOR c_cc_interface IS --All records for CBC from interface table
213 SELECT cc_header_id,
214 cc_version_num,
215 cc_acct_line_id,
216 cc_det_pf_line_id,
217 code_combination_id,
218 batch_line_num,
219 cc_transaction_date,
220 cc_func_dr_amt ,
221 cc_func_cr_amt ,
222 je_source_name,
223 je_category_name,
224 actual_flag,
225 set_of_books_id,
226 encumbrance_type_id,
227 budget_version_id,
228 currency_code,
229 transaction_description,
230 reference_1,
231 reference_2 ,
232 reference_3 ,
233 reference_4 ,
234 reference_5 ,
235 reference_6 ,
236 reference_7 ,
237 reference_8 ,
238 reference_9 ,
239 reference_10
240 FROM igc_cc_interface_v a
241 WHERE cc_header_id = g_cc_header_id
242 -- AND budget_dest_flag = 'C' /*R12 Uptake. Need to process for both Commitment and Standard budget*/
243 AND actual_flag = g_actual_flag
244 AND document_type = g_doc_type
245 ORDER BY cc_transaction_date;
246
247 l_api_name CONSTANT VARCHAR2(30) := 'IGCFCK';
248 l_ccid GL_CODE_COMBINATIONS.code_combination_id%TYPE;
249 l_period_name GL_PERIODS.period_name%TYPE;
250 l_return_status VARCHAR2(1);
251 l_fc_return_status VARCHAR2(2);
252 l_result_code VARCHAR2(3);
253 l_status_code VARCHAR2(1);
254 l_cbc_status VARCHAR2(1);
255 l_sbc_status VARCHAR2(1);
256 l_res BOOLEAN;
257 l_msg_count NUMBER;
258 l_msg_data VARCHAR(2000);
259 l_cbc_flag VARCHAR2(1);
260 l_sbc_flag VARCHAR2(1);
261 l_rank NUMBER(4);
262 l_cbc_ret_status VARCHAR2(4);
263 l_sbc_ret_status VARCHAR2(4);
264 l_pop_ret_status VARCHAR2(4);
265 l_pro_ret_status VARCHAR2(4);
266 l_undo_ret_status VARCHAR2(4);
267 -- ssmales 28/01/02 bug 2201905 - added variables below
268 l_cc_header_rec IGC_CC_INTERFACE%ROWTYPE ;
269 l_full_path VARCHAR2(255);
270 BEGIN
271 -- Standard Start of API savepoint
272 SAVEPOINT IGCFCK;
273 l_full_path := g_path || 'IGCFCK';
274 -- Initialize message list
275 -- Unreserve Mode does not exist in R12. R12 Uptake Bug No 6341012
276 -- IF p_mode <> 'U' THEN
277 FND_MSG_PUB.initialize;
278 -- END IF;
279
280 /*TO DO: Get CBC Ledger ID from GL tables based on p_sobid*/
281
282 /*Initialize the Ledger and Event Tables*/
283 g_event_tbl := PSA_FUNDS_CHECKER_PKG.num_rec();
284 g_ledger_tbl := PSA_FUNDS_CHECKER_PKG.num_rec();
285 --Initialize global variables
286 -- If packet id is not null, it means the call has come from outside
287 -- CC module, primarily from the PO Funds Checker.
288 -- Bidisha S, 28 Nov 2002
289
290 g_cbc_flag := TRUE;
291 g_sbc_flag := TRUE;
292 -- l_batch_status := 'A';
293 l_cbc_status := 'N';
294 l_sbc_status := 'N';
295 g_validation_error := FALSE;
296 g_mode := p_mode;
297 g_resp_id := FND_GLOBAL.RESP_ID;
298 g_update_login := FND_GLOBAL.LOGIN_ID;
299 g_update_by := FND_GLOBAL.USER_ID;
300 g_actual_flag := p_actual_flag;
301 g_cc_header_id := p_header_id;
302 g_set_of_books_id := p_sobid ;
303 -- g_cbc_ledger_id := 2599; /*Get from GL table based on p_sobid TO DO*/
304 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(p_debug);
305 g_conc_proc := FND_API.TO_BOOLEAN(p_conc_proc);
306 /*Bug No 6341012. Need to modify the below procedure*/
307 g_doc_type := p_doc_type;
308 g_batch_result_code := 9999;
309 g_validation_error := FALSE;
310 g_event_ind := 0;
311 g_ledger_ind := 0;
312
313 -- ssmales 25/01/02 bug 2201905 - added line below
314 -- g_p_packet_id := p_packet_id ;
315 -- --------------------------------------------------------------------
316 -- Obtain the application ID that will be used throughout this process.
317 -- --------------------------------------------------------------------
318 SELECT application_id
319 INTO g_gl_application_id
320 FROM fnd_application
321 WHERE application_short_name = 'SQLGL';
322
323 /*R12 Uptake Bug No 6341012. Obtain the IGC Application ID*/
324
325 SELECT application_id
326 INTO g_cc_application_id
327 FROM fnd_application
328 WHERE application_short_name = 'IGC';
329
330 IGC_LEDGER_UTILS.get_cbc_ledger(p_primary_ledger_id => p_sobid, p_cbc_ledger_id => g_cbc_ledger_id, p_cbc_ledger_Name => g_cbc_ledger_name);
331 IF (g_debug_mode = 'Y') THEN
332 Put_Debug_Msg(l_full_path, 'Secondary Ledger obtained based on primary Ledger: ' || g_cbc_ledger_id);
333 END IF;
334 g_cbc_enabled := IGC_LEDGER_UTILS.is_dual_bc_enabled(g_set_of_books_id);
335 IF (g_debug_mode = 'Y') THEN
336 Put_Debug_Msg(l_full_path, 'CBC Enabled Flag: ' || g_cbc_enabled);
337 END IF;
338 IF (g_debug_mode <> 'Y') AND (p_debug = FND_API.G_TRUE)
339 THEN
340 g_debug_mode := 'Y';
341 END IF;
342 IF (g_debug_mode = 'Y') THEN
343 Put_Debug_Msg(l_full_path, SUBSTR('**************************************************************************************************',1,70));
344 Put_Debug_Msg(l_full_path, SUBSTR('*********Starting CBC Funds Checker '||TO_CHAR(SYSDATE,'DD-MON-YY:MI:SS')||' *********************',1,70));
345 Put_Debug_Msg(l_full_path, SUBSTR('**************************************************************************************************',1,70));
346 Put_Debug_Msg(l_full_path, 'Parameters SOB:' || p_sobid ||' Mode: ' || p_mode || ' HeaderID ' ||p_header_id);
347 END IF;
348
349 /*Get the Rank for entries that already have CBC_RESULT_CODE. Based on the Rank set the Batch Result Code*/
350 SELECT MIN(Get_Rank(cbc_result_code))
351 INTO l_rank
352 FROM igc_cc_interface_v a
353 WHERE cc_header_id = g_cc_header_id
354 AND budget_dest_flag = 'C'
355 AND actual_flag = g_actual_flag
356 AND document_type = g_doc_type
357 AND cbc_result_code IS NOT NULL;
358
359 IF l_rank IS NOT NULL THEN
360 IF (g_debug_mode = 'Y') THEN
361 Put_Debug_Msg(l_full_path, 'The most severe found in the batch: '||l_rank);
362 END IF;
363 Set_Batch_Result_Code ( Get_Result_By_Rank(l_rank));
364 END IF;
365
366 IF (g_debug_mode = 'Y') THEN
367 Put_Debug_Msg(l_full_path, 'Validating interface table..');
368 Register_time('',TRUE);
369 END IF;
370
371 -- Commented Packet ID, as it is no longer used in R12. Bug No 6341012
372
373 IGC_CBC_VALIDATIONS_PKG.Validate_CC_Interface
374 (
375 p_api_version => 1.0,
376 p_return_status => l_return_status,
377 p_msg_count => l_msg_count,
378 p_msg_data => l_msg_data,
379 p_sob_id => g_set_of_books_id,
380 p_cbc_enabled => g_cbc_enabled,
381 p_cc_head_id => g_cc_header_id,
382 p_actl_flag => g_actual_flag,
383 p_documt_type => g_doc_type,
384 -- p_sum_line_num => g_summary_line_num,
385 p_cbc_flag => l_cbc_flag,
386 p_sbc_flag => l_sbc_flag
387 -- p_packet_id => g_p_packet_id
388 ) ;
389 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392 g_cbc_flag := FND_API.TO_BOOLEAN(l_cbc_flag);
393 g_sbc_flag := FND_API.TO_BOOLEAN(l_sbc_flag);
394 IF NOT(g_cbc_flag) AND NOT(g_sbc_flag) THEN
395 --No rows - return success.
396 IF (g_debug_mode = 'Y') THEN
397 Put_Debug_Msg(l_full_path, 'Now rows found - return success');
398 END IF;
399 p_ret_status := 'SN';
400 p_batch_result_code:=NULL;
401 RETURN(TRUE);
402 END IF;
403 IF (g_debug_mode = 'Y') THEN
404 Register_time('Validate interface ',FALSE);
405 END IF;
406
407 /*R12 SLA Uptake Bug No 6341012 - Start*/
408 /*PO will do Fund check*/
409 IF g_doc_type IN ('REQ', 'PO', 'REL') THEN
410 g_called_from_PO := TRUE;
411 g_sbc_flag := FALSE;
412 IF (g_debug_mode = 'Y') THEN
413 Put_Debug_Msg(l_full_path, 'Called from PO - Standard Budget Fund Checker is disabled');
414 END IF;
415 END IF;
416
417 /*Clearing previous entries if any*/
418 g_event_tbl.DELETE;
419 g_ledger_tbl.DELETE;
420
421 POPULATE_INTERFACE_TBL(p_return_status => l_pop_ret_status);
422
423 IF (g_debug_mode = 'Y') THEN
424 Put_Debug_Msg(l_full_path, 'Populated Interface Table');
425 END IF;
426
427 IF (l_pop_ret_status = 'Y') THEN
428
429 IF (g_cbc_flag) THEN
430 IF (g_debug_mode = 'Y') THEN
431 Put_Debug_Msg(l_full_path, 'CBC records found');
432 END IF;
433 /*Fetch CBC Ledger ID from GL. SLA Uptake. Bug No 6341012*/
434 /*GL need to provide table and column details*/
435 /*Hard Coded as of now TO DO - At the Start of IGCFCK*/
436 PROCESS_CC_INT_LINES(p_budget_dest_flag => 'C', p_mode => g_mode, x_return_status => l_cbc_ret_status);
437 IF (g_debug_mode = 'Y') THEN
438 Put_Debug_Msg(l_full_path, 'CBC Return Status: ' || l_cbc_ret_status);
439 END IF;
440 ELSE
441 l_cbc_ret_status := 'Y';
442 g_cbc_status := 'N';
443 END IF;
444
445 IF (l_cbc_ret_status = 'N' AND p_mode = 'F') THEN
446 g_mode := 'C';
447 END IF;
448
449 IF (g_sbc_flag) THEN
450 IF (g_debug_mode = 'Y') THEN
451 Put_Debug_Msg(l_full_path, 'SBC records found');
452 END IF;
453 PROCESS_CC_INT_LINES(p_budget_dest_flag => 'S', p_mode => g_mode, x_return_status => l_sbc_ret_status);
454 IF (g_debug_mode = 'Y') THEN
455 Put_Debug_Msg(l_full_path, 'SBC Return Status: ' || l_sbc_ret_status);
456 END IF;
457 ELSE
458 l_sbc_ret_status := 'Y';
459 g_sbc_status := 'N';
460 END IF;
461 END IF;
462
463 IF (g_debug_mode = 'Y') THEN
464 Put_Debug_Msg(l_full_path, 'CBC Status: ' || g_cbc_status || ' SBC Status: ' || g_sbc_status);
465 END IF;
466
467 IF g_sbc_status <> 'T' AND g_cbc_status <> 'T' AND l_cbc_ret_status = 'Y' AND l_sbc_ret_status = 'Y' THEN
468
469 IF (g_debug_mode = 'Y') THEN
470 Put_Debug_Msg(l_full_path, 'Processing Results...');
471 END IF;
472
473 PROCESS_RESULTS(
474 x_ret_status => l_pro_ret_status,
475 x_batch_result_code => g_batch_result_code
476 );
477
478 IF (g_debug_mode = 'Y') THEN
479 Put_Debug_Msg(l_full_path, 'Processed results. Updated Batch Result Code: ' || g_batch_result_code);
480 END IF;
481
482 END IF;
483
484
485 IF (l_sbc_ret_status = 'N' OR l_cbc_ret_status = 'N') THEN
486 ROLLBACK TO SAVEPOINT IGCFCK;
487 Update_Event_ID;
488 p_ret_status := g_cbc_status || g_sbc_status;
489 IF (g_debug_mode = 'Y') THEN
490 Put_Debug_Msg(l_full_path, 'Return Status: ' || p_ret_status);
491 END IF;
492 IF g_sbc_status <> 'T' AND g_cbc_status <> 'T' THEN
493 PROCESS_RESULTS(
494 x_ret_status => l_pro_ret_status,
495 x_batch_result_code => g_batch_result_code
496 );
497 p_batch_result_code := g_batch_result_code;
498 ELSE
499 p_batch_result_code := NULL;
500 END IF;
501 IF (g_debug_mode = 'Y') THEN
502 Put_Debug_Msg(l_full_path, 'Roll backed to Save point IGCFCK due to Funds Check failure');
503 Put_Debug_Msg(l_full_path, 'Processed Results');
504 Put_Debug_Msg(l_full_path, 'SBC Return Status: ' || l_sbc_ret_status || ' CBC Return Status: ' || l_cbc_ret_status );
505 END IF;
506 UNDO_GL_BC_PACKETS (
507 p_ledger_array => g_ledger_tbl,
508 p_event_array => g_event_tbl,
509 p_return_status => l_undo_ret_status);
510 IF (g_debug_mode = 'Y') THEN
511 Put_Debug_Msg(l_full_path, 'GL_BC_PACKETS Rollbacked Status: ' ||l_undo_ret_status);
512 END IF;
513
514 Restore_events_gt;
515
516 RETURN FALSE;
517 END IF;
518
519 IF (g_debug_mode = 'Y') THEN
520 Put_Debug_Msg(l_full_path, 'CBC Status: ' || g_cbc_status || ' SBC Status: ' || g_sbc_status);
521 END IF;
522
523 p_ret_status := g_cbc_status || g_sbc_status;
524
525 p_batch_result_code := g_batch_result_code;
526
527 IF (g_debug_mode = 'Y') THEN
528 Put_Debug_Msg(l_full_path, 'Return Status: ' || p_ret_status || ' Batch Result Code: ' || p_batch_result_code);
529 END IF;
530
531 Restore_events_gt;
532
533 RETURN (TRUE);
534
535 EXCEPTION
536
537 WHEN OTHERS THEN
538
539 ROLLBACK TO SAVEPOINT IGCFCK;
540 p_ret_status := g_cbc_status || g_sbc_status;
541 p_batch_result_code := NULL;
542 IF (g_debug_mode = 'Y') THEN
543 Put_Debug_Msg(l_full_path, 'Exception Occured ' || SQLERRM);
544 END IF;
545
546 UNDO_GL_BC_PACKETS (
547 p_ledger_array => g_ledger_tbl,
548 p_event_array => g_event_tbl,
549 p_return_status => l_undo_ret_status);
550 IF (g_debug_mode = 'Y') THEN
551 Put_Debug_Msg(l_full_path, 'Exception Occured - Rollbacked GL_BC_PACKETS Status: ' || l_undo_ret_status);
552 END IF;
553 RETURN (FALSE);
554
555 /*R12 SLA Uptake Bug No 6341012 - End*/
556
557 END IGCFCK;
558
559 FUNCTION Get_Batch_Result_Code (
560 p_mode VARCHAR2,
561 p_batch_result_code VARCHAR2 )
562 RETURN VARCHAR2
563 IS
564 l_batch_result_code VARCHAR2(3);
565 l_ranked_result_code VARCHAR2(3);
566 -- 1947176, Aug 21 2001
567 CURSOR c_get_msg IS
568 SELECT DISTINCT popup_messg_code
569 FROM igc_cc_result_code_ranks
570 WHERE action = DECODE(p_mode,'F','R',p_mode)
571 AND severity_rank = p_batch_result_code;
572
573 l_full_path VARCHAR2(255);
574 BEGIN
575 l_full_path := g_path || 'Get_Batch_Result_Code';
576 IF (g_debug_mode = 'Y') THEN
577 Put_Debug_Msg(l_full_path, 'The most severe rank is: '||p_batch_result_code);
578 END IF;
579 -- The values now stored in table IGC_CC_RESULT_CODE_RANKS
580 -- 1947176, Aug 21 2001
581 OPEN c_get_msg;
582 FETCH c_get_msg INTO l_batch_result_code;
583 CLOSE c_get_msg;
584 RETURN l_batch_result_code;
585 EXCEPTION
586 WHEN OTHERS
587 THEN
588 RETURN NULL;
589 END Get_Batch_Result_Code;
590
591
592 PROCEDURE Put_Debug_Msg (
593 p_path IN VARCHAR2,
594 p_debug_msg IN VARCHAR2
595 ) IS
596 BEGIN
597 IF(g_state_level >= g_debug_level) THEN
598 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
599 END IF;
600 RETURN;
601 -- --------------------------------------------------------------------
602 -- Exception handler section for the Put_Debug_Msg procedure.
603 -- --------------------------------------------------------------------
604 EXCEPTION
605
606 WHEN OTHERS THEN
607 NULL;
608 RETURN;
609 END Put_Debug_Msg;
610
611 PROCEDURE Register_time(
612 p_name VARCHAR2,
613 p_mode BOOLEAN
614 ) IS
615 l_full_path VARCHAR2(255);
616 BEGIN
617 l_full_path := g_path || 'Register_time';
618 IF (p_mode) THEN
619 g_date1:=DBMS_UTILITY.GET_TIME;
620 ELSE
621 g_date2 := DBMS_UTILITY.GET_TIME;
622 IF (g_debug_mode = 'Y') THEN
623 Put_Debug_Msg (l_full_path, 'Time ' || p_name || ': ' || TO_CHAR(g_date2-g_date1));
624 END IF;
625 END IF;
626 END Register_time;
627
628 FUNCTION Get_Rank(
629 p_code IN VARCHAR2)
630 RETURN NUMBER
631 IS
632 l_batch_result_code NUMBER(4);
633 -- 1947176, Aug 21 2001
634 CURSOR c_get_rank IS
635 SELECT DISTINCT severity_rank
636 FROM igc_cc_result_code_ranks
637 WHERE funds_checker_code = p_code;
638
639 l_full_path VARCHAR2(255);
640 BEGIN
641 l_full_path := g_path || 'Get_Rank';
642 --Selecting the Sevirity rank for result_code
643 -- The values now stored in table IGC_CC_RESULT_CODE_RANKS
644 -- 1947176, Aug 21 2001
645 OPEN c_get_rank;
646 FETCH c_get_rank INTO l_batch_result_code;
647 CLOSE c_get_rank;
648 RETURN l_batch_result_code;
649 EXCEPTION
650 WHEN OTHERS
651 THEN
652 RETURN NULL;
653 END Get_Rank;
654
655 /* function determines status code, using result code */
656 FUNCTION Get_Status_By_Result(
657 p_result_code IN VARCHAR2)
658 RETURN VARCHAR2 IS
659 l_status_code VARCHAR2(1);
660 -- 1947176, Aug 21 2001
661 CURSOR c_get_result IS
662 SELECT DISTINCT result_status_code
663 FROM igc_cc_result_code_ranks
664 WHERE funds_checker_code = p_result_code
665 AND action = DECODE(g_mode, 'F', 'R', g_mode);
666
667 l_full_path VARCHAR2(255);
668 BEGIN
669 l_full_path := g_path || 'Get_Status_By_Result';
670 IF p_result_code IS NULL THEN
671 RETURN '';
672 ELSE
673 -- The values now stored in table IGC_CC_RESULT_CODE_RANKS
674 -- 1947176, Aug 21 2001
675 OPEN c_get_result;
676 FETCH c_get_result INTO l_status_code;
677 CLOSE c_get_result;
678 END IF;
679 RETURN l_status_code;
680 EXCEPTION
681 WHEN OTHERS
682 THEN
683 RETURN NULL;
684 END Get_Status_By_Result;
685
686 FUNCTION Get_Result_By_Rank(
687 p_rank NUMBER )
688 RETURN VARCHAR2
689 IS
690 l_result_code VARCHAR2(3);
691 -- 1947176, Aug 21 2001
692 CURSOR c_get_result IS
693 SELECT DISTINCT funds_checker_code
694 FROM igc_cc_result_code_ranks
695 WHERE severity_rank = p_rank;
696
697 l_full_path VARCHAR2(255);
698 BEGIN
699 l_full_path := g_path || 'Get_Result_By_Rank';
700 --Selecting the result_code, using the Sevirity rank
701 -- Selecting the ranked result code, using result_code
702 -- The values now stored in table IGC_CC_RESULT_CODE_RANKS
703 -- 1947176, Aug 21 2001
704 OPEN c_get_result;
705 FETCH c_get_result INTO l_result_code;
706 CLOSE c_get_result;
707 RETURN l_result_code;
708 EXCEPTION
709 WHEN OTHERS
710 THEN
711 RETURN NULL;
712 END Get_Result_By_Rank;
713
714 PROCEDURE Set_Batch_Result_Code(
715 p_code IN VARCHAR2)
716 IS
717 l_batch_result_code NUMBER(4);
718 l_full_path VARCHAR2(255);
719 BEGIN
720 --Selecting the Sevirity rank for result_code
721 l_full_path := g_path || 'Set_Batch_Result_Code';
722 l_batch_result_code := Get_Rank (p_code);
723 IF g_batch_result_code > l_batch_result_code THEN
724 g_batch_result_code := l_batch_result_code;
725 END IF;
726 END Set_Batch_Result_Code;
727
728
729 /*R12 Uptake. Bug No 6341012 - Start*/
730
731 PROCEDURE POPULATE_INTERFACE_TBL(
732 p_return_status OUT NOCOPY VARCHAR2
733 ) IS
734 CURSOR c_pop_interface_tbl IS
735 SELECT DISTINCT cc_header_id,
736 DOCUMENT_TYPE,
737 BUDGET_DEST_FLAG,
738 REFERENCE_4,
739 CC_TRANSACTION_DATE,
740 EVENT_ID,
741 CC_DET_PF_LINE_ID
742 FROM IGC_CC_INTERFACE
743 WHERE event_id IS NULL
744 AND cc_header_id = g_cc_header_id;
745
746 l_full_path VARCHAR2(255);
747 l_err_code NUMBER;
748 l_err_msg VARCHAR2(200);
749
750 BEGIN
751
752 l_full_path := g_path || 'POPULATE_INTERFACE_TBL';
753 OPEN c_pop_interface_tbl;
754 FETCH c_pop_interface_tbl BULK COLLECT INTO g_cc_interface_head_tbl;
755 CLOSE c_pop_interface_tbl;
756
757 p_return_status := 'Y';
758
759 EXCEPTION
760
761 WHEN OTHERS THEN
762
763 p_return_status := 'N';
764 l_err_code := SQLCODE;
765 l_err_msg := SUBSTR(SQLERRM,1,200);
766 IF (g_debug_mode = 'Y') THEN
767 Put_Debug_Msg (l_full_path, 'SQL Code: ' || l_err_code );
768 Put_Debug_Msg (l_full_path, 'SQL Error Message: ' || l_err_msg);
769 END IF;
770 IF (g_debug_mode = 'Y') THEN
771 Put_Debug_Msg (l_full_path, 'Failed to populate Data from Interface Table' );
772 END IF;
773
774 END POPULATE_INTERFACE_TBL;
775
776 PROCEDURE PROCESS_CC_INT_LINES(
777 p_budget_dest_flag IN VARCHAR2,
778 p_mode IN VARCHAR2,
779 x_return_status OUT NOCOPY VARCHAR2
780 ) IS
781
782 l_entity_type VARCHAR2(100);
783 l_event_type_code VARCHAR2(100);
784 l_event_status_code VARCHAR2(1);
785 l_event_number NUMBER;
786 l_reference_info XLA_EVENTS_PUB_PKG.T_EVENT_REFERENCE_INFO;
787 l_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
788 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
789 l_event_id XLA_EVENTS_GT.EVENT_ID%TYPE;
790 l_budget_dest_flag VARCHAR2(1);
791 l_valuation_method VARCHAR2(3);
792
793 l_err_code NUMBER;
794 l_err_msg VARCHAR2(200);
795
796 /*Variables for PSA_BC_XLA_PUB.Budgetary_control - Start*/
797
798 l_return_status VARCHAR2(100);
799 l_msg_count NUMBER;
800 l_msg_data VARCHAR2(4000);
801 l_status_code VARCHAR2(100);
802 l_packet_id NUMBER;
803
804 /*Variables for PSA_BC_XLA_PUB.Budgetary_control - End*/
805
806 l_ledger_id NUMBER;
807 l_application_id NUMBER;
808
809 l_full_path VARCHAR2(255);
810 l_sev_rank NUMBER;
811 l_status_flag VARCHAR2(2);
812 l_bud_cntrl VARCHAR2(1);
813 l_bc_mode VARCHAR2(2);
814 l_org_id NUMBER;
815
816 CURSOR c_xla_events_gt IS
817 SELECT * FROM psa_bc_xla_events_gt;
818
819 BEGIN
820
821 l_full_path := g_path || 'PROCESS_CC_INT_LINES';
822 x_return_status := 'Y';
823
824 IF p_budget_dest_flag = 'C' THEN
825 l_ledger_id := g_cbc_ledger_id;
826 l_valuation_method := 'CBC';
827
828 l_application_id := g_cc_application_id;
829 IF (g_debug_mode = 'Y') THEN
830 Put_Debug_Msg (l_full_path, 'Processing Interface lines for Secondary Ledger (Commitment Budget)' );
831 END IF;
832 ELSIF p_budget_dest_flag = 'S' THEN
833 l_ledger_id := g_set_of_books_id;
834 l_valuation_method := 'SBC';
835
836 l_application_id := g_cc_application_id;
837 IF (g_debug_mode = 'Y') THEN
838 Put_Debug_Msg (l_full_path, 'Processing Interface lines for Primary Ledger (Standard Budget)' );
839 END IF;
840 END IF;
841
842 /*Extend the Vector and add the Ledger ID. This is used for reversing the GL_BC_PACKETS in case the funds check fails*/
843 g_ledger_tbl.EXTEND;
844 g_ledger_ind := g_ledger_ind+1;
845 g_ledger_tbl(g_ledger_ind) := l_ledger_id;
846
847 IF (g_debug_mode = 'Y') THEN
848 Put_Debug_Msg (l_full_path, 'Ledger ID: ' || l_ledger_id || ' Valuation Method: ' || l_valuation_method);
849 Put_Debug_Msg (l_full_path, 'Getting Event Type Code and Entity Type' );
850 END IF;
851
852 l_org_id := MO_GLOBAL.get_current_org_id;
853
854 IF (g_debug_mode = 'Y') THEN
855 Put_Debug_Msg (l_full_path, 'Org ID: ' || l_org_id);
856 END IF;
857
858 GET_EVENT_DETAILS(
859 x_entity_type => l_entity_type,
860 x_event_type_code => l_event_type_code,
861 p_org_id => l_org_id
862 );
863
864 l_security_context.security_id_int_1 := l_org_id;
865
866 IF (g_debug_mode = 'Y') THEN
867 Put_Debug_Msg (l_full_path, 'Security Context Set to: ' || l_security_context.security_id_int_1);
868 END IF;
869
870 IF (g_debug_mode = 'Y') THEN
871 Put_Debug_Msg (l_full_path, 'Obtained Event Type Code and Entity Type' );
872 Put_Debug_Msg (l_full_path, 'Entity Type Code: ' || l_entity_type || ' Event Type Code: ' || l_event_type_code);
873 END IF;
874
875 /*Backup records in psa_bc_xla_events_gt before deletion - May be required by calling module after FC call*/
876
877 OPEN c_xla_events_gt;
878 FETCH c_xla_events_gt BULK COLLECT INTO g_xla_events_gt_tbl;
879 CLOSE c_xla_events_gt;
880
881 DELETE FROM psa_bc_xla_events_gt pgt ;
882
883 /*
884 Commented due to issues during baselining - GT table has PA Events and IGC Events. This will cause PSA Funds Checker to fail with XLA-ERROR
885 PSA fails to fetch the correct Ledger due to Events from 2 different Applications i.e PA and IGC
886 */
887 /*
888 WHERE pgt.event_id IN
889 (SELECT event_id FROM xla_events xe WHERE application_id = 8407 AND pgt.event_id = xe.event_id);
890 */
891
892 IF (g_debug_mode = 'Y') THEN
893 Put_Debug_Msg (l_full_path, 'Deleted all Event entries from psa_bc_xla_events_gt' );
894 END IF;
895
896 FOR i IN 1..g_cc_interface_head_tbl.COUNT
897 LOOP
898
899 IF g_cc_interface_head_tbl(i).budget_dest_flag = p_budget_dest_flag THEN
900
901 l_event_source_info.source_application_id := NULL;
902 l_event_source_info.application_id := l_application_id;
903 l_event_source_info.legal_entity_id := NULL;
904 l_event_source_info.ledger_id := l_ledger_id;
905 l_event_source_info.entity_type_code := l_entity_type;
906 l_event_source_info.transaction_number := g_cc_interface_head_tbl(i).reference_4;
907 l_event_source_info.source_id_int_1 := g_cc_interface_head_tbl(i).CC_HEADER_ID;
908 /*
909 Not Required as we have seperate Entity Codes for CC, Projects, PO and Requisition
910 l_event_source_info.source_id_char_1 := g_cc_interface_head_tbl(i).DOCUMENT_TYPE;
911 */
912
913 l_event_status_code := XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED;
914
915 IF (g_debug_mode = 'Y') THEN
916 Put_Debug_Msg (l_full_path, 'Calling Create Event API...' );
917 END IF;
918
919 l_event_id := Xla_Events_Pub_Pkg.Create_Event
920 (
921 p_event_source_info => l_event_source_info,
922 p_event_type_code => l_event_type_code,
923 p_event_date => g_cc_interface_head_tbl(i).CC_TRANSACTION_DATE,
924 p_event_status_code => l_event_status_code,
925 p_event_number => NULL,
926 p_reference_info => l_reference_info,
927 p_valuation_method => l_valuation_method,
928 p_security_context => l_security_context,
929 p_budgetary_control_flag => 'Y'
930 );
931
932 IF (g_debug_mode = 'Y') THEN
933 Put_Debug_Msg (l_full_path, 'Completed Create Event' );
934 END IF;
935
936 /*Extend the Vector and add the Event ID. This is used for reversing the GL_BC_PACKETS in case the funds check fails*/
937 g_event_tbl.EXTEND;
938 g_event_ind := g_event_ind + 1;
939 g_event_tbl(g_event_ind) := l_event_id;
940
941 g_cc_interface_head_tbl(i).event_id := l_event_id;
942
943 INSERT
944 INTO psa_bc_xla_events_gt(event_id, result_code)
945 VALUES(l_event_id, 'XLA_ERROR');
946
947 IF (g_debug_mode = 'Y') THEN
948 Put_Debug_Msg (l_full_path, 'Inserted Event ID :' || l_event_id || ' into psa_bc_xla_events_gt' );
949 END IF;
950
951
952 UPDATE igc_cc_interface
953 SET event_id = l_event_id
954 WHERE cc_header_id = g_cc_interface_head_tbl(i).cc_header_id
955 AND document_type = g_cc_interface_head_tbl(i).document_type
956 AND budget_dest_flag = g_cc_interface_head_tbl(i).budget_dest_flag
957 AND reference_4 = g_cc_interface_head_tbl(i).reference_4
958 AND nvl(cc_det_pf_line_id, 1) = nvl(g_cc_interface_head_tbl(i).cc_det_pf_line_id, 1)
959 AND cc_transaction_date = g_cc_interface_head_tbl(i).cc_transaction_date;
960
961 IF (g_debug_mode = 'Y') THEN
962 Put_Debug_Msg (l_full_path, 'Inserted Event ID: ' || l_event_id || ' into psa_bc_xla_events_gt' );
963 Put_Debug_Msg (l_full_path, 'Update Event ID: ' || l_event_id || ' in IGC_CC_INTERFACE' );
964 END IF;
965
966 END IF;
967
968 END LOOP;
969
970 IF p_mode = 'C' THEN
971 l_bc_mode := 'M';
972 ELSIF p_mode in ('R', 'F') THEN
973 l_bc_mode := 'R';
974 END IF;
975
976 IF (g_debug_mode = 'Y') THEN
977 Put_Debug_Msg (l_full_path, 'BC Mode: ' || l_bc_mode);
978 END IF;
979
980 PSA_BC_XLA_PUB.Budgetary_control
981 ( p_api_version => 1.0,
982 p_init_msg_list => NULL,
983 x_return_status => l_return_status,
984 x_msg_count => l_msg_count,
985 x_msg_data => l_msg_data,
986 p_application_id => l_application_id,
987 p_bc_mode => l_bc_mode,
988 p_override_flag => 'Y',
989 P_user_id => NULL,
990 P_user_resp_id => NULL,
991 x_status_code => l_status_code,
992 x_Packet_ID => l_packet_id
993 );
994
995 IF (g_debug_mode = 'Y') THEN
996 Put_Debug_Msg (l_full_path,
997 'Return Status: ' || l_return_status ||
998 ' Msg Count: ' || l_msg_count ||
999 ' Msg Data: ' || l_msg_data ||
1000 ' Packet Id: ' || l_packet_id ||
1001 ' Status Code: ' || l_status_code);
1002 END IF;
1003
1004 select decode(l_status_code, 'ADVISORY' , 'SUCCESS', 'PARTIAL', 'FAIL', 'XLA_ERROR', 'FATAL', l_status_code)
1005 INTO l_status_code
1006 FROM DUAL;
1007
1008 CASE l_status_code
1009 WHEN 'SUCCESS' THEN
1010 x_return_status := 'Y';
1011 Get_Status(x_sev_rank => l_sev_rank);
1012
1013 IF (g_debug_mode = 'Y') THEN
1014 Put_Debug_Msg (l_full_path, 'Status Rank: ' || l_sev_rank);
1015 END IF;
1016
1017 SELECT result_status_code INTO l_status_flag
1018 FROM igc_cc_result_code_ranks
1019 WHERE severity_rank = l_sev_rank
1020 AND action = decode(g_mode, 'F', 'R', g_mode);
1021
1022 IF (p_budget_dest_flag = 'S') THEN
1023 g_sbc_status := l_status_flag;
1024 ELSE
1025 g_cbc_status := l_status_flag;
1026 END IF;
1027 WHEN 'FAIL' THEN
1028 x_return_status := 'N';
1029 IF (p_budget_dest_flag = 'S') THEN
1030 g_sbc_status := 'F';
1031 ELSE
1032 g_cbc_status := 'F';
1033 END IF;
1034 WHEN 'FATAL' THEN
1035 x_return_status := 'N';
1036 IF (p_budget_dest_flag = 'S') THEN
1037 g_sbc_status := 'T';
1038 ELSE
1039 g_cbc_status := 'T';
1040 END IF;
1041 WHEN 'XLA_NO_JOURNAL' THEN
1042 x_return_status := 'N';
1043 IF (p_budget_dest_flag = 'S') THEN
1044 g_sbc_status := 'N';
1045 ELSE
1046 g_cbc_status := 'N';
1047 END IF;
1048 END CASE;
1049
1050 EXCEPTION
1051
1052 WHEN OTHERS THEN
1053 x_return_status := 'N';
1054 l_err_code := SQLCODE;
1055 l_err_msg := SUBSTR(SQLERRM,1,200);
1056 IF (g_debug_mode = 'Y') THEN
1057 Put_Debug_Msg (l_full_path, 'SQL Code: ' || l_err_code );
1058 Put_Debug_Msg (l_full_path, 'SQL Error Message: ' || l_err_msg);
1059 END IF;
1060
1061 END PROCESS_CC_INT_LINES;
1062
1063 PROCEDURE Get_Status(
1064 x_sev_rank OUT NOCOPY NUMBER
1065 ) IS
1066
1067 l_max_sev_rank NUMBER;
1068
1069 BEGIN
1070
1071 SELECT min(severity_rank) INTO l_max_sev_rank
1072 FROM igc_cc_result_code_ranks
1073 WHERE funds_checker_code IN(SELECT distinct(result_code)
1074 FROM GL_BC_PACKETS
1075 WHERE event_id IN (SELECT event_id
1076 FROM
1077 psa_bc_xla_events_gt));
1078 x_sev_rank := l_max_sev_rank;
1079 END Get_Status;
1080
1081 PROCEDURE UNDO_GL_BC_PACKETS(
1082 p_ledger_array IN PSA_FUNDS_CHECKER_PKG.num_rec,
1083 p_event_array IN PSA_FUNDS_CHECKER_PKG.num_rec,
1084 p_return_status OUT NOCOPY VARCHAR2
1085
1086 ) IS
1087
1088 PRAGMA AUTONOMOUS_TRANSACTION;
1089
1090 l_err_code NUMBER;
1091 l_err_msg VARCHAR2(200);
1092 l_full_path VARCHAR2(255);
1093
1094 BEGIN
1095
1096 l_full_path := g_path || 'UNDO_GL_BC_PACKETS';
1097 IF (g_debug_mode = 'Y') THEN
1098 Put_Debug_Msg (l_full_path, 'Switching to Autonomous Transaction Mode' );
1099 Put_Debug_Msg (l_full_path, 'Undoing GL_BC_PACKETS by calling sync_xla_errors' );
1100 END IF;
1101
1102 PSA_FUNDS_CHECKER_PKG.sync_xla_errors(
1103 p_failed_ldgr_array => p_ledger_array,
1104 p_failed_evnt_array => p_event_array
1105 );
1106
1107 p_return_status := 'Y';
1108
1109 COMMIT;
1110
1111 EXCEPTION
1112
1113 WHEN OTHERS THEN
1114
1115 ROLLBACK;
1116 p_return_status := 'N';
1117 l_err_code := SQLCODE;
1118 l_err_msg := SUBSTR(SQLERRM,1,200);
1119 IF (g_debug_mode = 'Y') THEN
1120 Put_Debug_Msg (l_full_path, 'SQL Code: ' || l_err_code );
1121 Put_Debug_Msg (l_full_path, 'SQL Error Message: ' || l_err_msg);
1122 END IF;
1123
1124 END UNDO_GL_BC_PACKETS;
1125
1126 PROCEDURE PROCESS_RESULTS(
1127 x_ret_status OUT NOCOPY VARCHAR2,
1128 x_batch_result_code OUT NOCOPY VARCHAR2
1129 )
1130 IS
1131
1132 l_batch_result_code VARCHAR2(4);
1133 l_sev_rank NUMBER;
1134
1135 l_err_code NUMBER;
1136 l_err_msg VARCHAR2(200);
1137 l_full_path VARCHAR2(255);
1138
1139 BEGIN
1140
1141 l_batch_result_code := NULL;
1142 l_full_path := g_path || 'PROCESS_RESULTS';
1143 x_ret_status := 'Y';
1144
1145
1146 IF (g_debug_mode = 'Y') THEN
1147 Put_Debug_Msg (l_full_path, 'Updating Batch Result Code for CC Header ID: '|| g_cc_header_id);
1148 END IF;
1149
1150 UPDATE igc_cc_interface int
1151 SET (batch_id,
1152 cbc_result_code,
1153 status_code,
1154 budget_version_id,
1155 period_name,
1156 encumbrance_type_id
1157 )
1158 =
1159 (
1160 SELECT distinct pac.je_batch_id,
1161 pac.result_code,
1162 pac.status_code,
1163 pac.funding_budget_version_id,
1164 pac.period_name,
1165 pac.encumbrance_type_id
1166 FROM gl_bc_packets pac
1167 WHERE int.event_id = pac.event_id
1168 AND int.cc_acct_line_id = pac.source_distribution_id_num_1
1169 -- Commented as it is causing issues with result updation Refer Bug 6628196
1170 -- AND (nvl(pac.accounted_dr, 0) = nvl(INT.cc_func_dr_amt, -1) OR nvl(pac.accounted_cr, 0) = nvl(INT.cc_func_cr_amt, -1))
1171 AND (sign(nvl(pac.accounted_dr, 0)) = sign(nvl(INT.cc_func_dr_amt, -1)) OR sign(nvl(pac.accounted_cr, 0)) = sign(nvl(INT.cc_func_cr_amt, -1)))
1172 )
1173 WHERE
1174 int.cbc_result_code IS NULL AND
1175 int.cc_header_id = g_cc_header_id;
1176
1177 IF (g_debug_mode = 'Y') THEN
1178 Put_Debug_Msg (l_full_path, 'Number of rows updated: ' || SQL%ROWCOUNT);
1179 Put_Debug_Msg (l_full_path, 'Calling Get Maximum Result Code');
1180 END IF;
1181
1182 l_batch_result_code := Get_Max_Result_Code(l_sev_rank);
1183
1184 /*Gets Pop up Message Code. This has to be returned to Wrapper package*/
1185
1186 x_batch_result_code := Get_Batch_Result_Code(g_mode, l_sev_rank);
1187
1188 x_ret_status := 'Y';
1189
1190 IF (g_debug_mode = 'Y') THEN
1191 Put_Debug_Msg (l_full_path, 'Process Results Return Status: ' || x_ret_status || ' Batch Result Code: ' || x_batch_result_code);
1192 END IF;
1193
1194 EXCEPTION
1195
1196 WHEN OTHERS THEN
1197 ROLLBACK;
1198 x_ret_status := 'N';
1199 x_batch_result_code := NULL;
1200 l_err_code := SQLCODE;
1201 l_err_msg := SUBSTR(SQLERRM,1,200);
1202 IF (g_debug_mode = 'Y') THEN
1203 Put_Debug_Msg (l_full_path, 'SQL Code: ' || l_err_code );
1204 Put_Debug_Msg (l_full_path, 'SQL Error Message: ' || l_err_msg);
1205 END IF;
1206
1207 END PROCESS_RESULTS;
1208
1209 PROCEDURE GET_EVENT_DETAILS(
1210 x_entity_type OUT NOCOPY VARCHAR2,
1211 x_event_type_code OUT NOCOPY VARCHAR2,
1212 p_org_id IN OUT NOCOPY NUMBER
1213 ) IS
1214
1215 CURSOR c_igc_head IS
1216 SELECT cc_state
1217 FROM igc_cc_headers
1218 WHERE cc_header_id = g_cc_header_id;
1219
1220 l_doc_type VARCHAR2(100);
1221 l_cc_state VARCHAR2(2);
1222 l_full_path VARCHAR2(255);
1223
1224 /*Added for CBC Upgrade - Start*/
1225
1226 l_reference_8 VARCHAR2(5);
1227 l_je_category_name VARCHAR2(100);
1228
1229 /*Added for CBC Upgrade - End*/
1230
1231 BEGIN
1232
1233 l_full_path := g_path || 'Get Event Details';
1234
1235 OPEN c_igc_head;
1236 FETCH c_igc_head INTO l_cc_state;
1237 CLOSE c_igc_head;
1238
1239 l_doc_type := g_cc_interface_head_tbl(1).document_type;
1240
1241 IF l_doc_type = 'CC' THEN
1242 /*Added for CBC Upgrade - Start*/
1243 SELECT distinct reference_8
1244 INTO l_reference_8
1245 FROM igc_cc_interface
1246 WHERE cc_header_id = g_cc_header_id;
1247 IF (l_reference_8 IS NULL) THEN
1248 /*Added for CBC Upgrade - End*/
1249 IF (p_org_id IS NULL) THEN
1250 SELECT org_id INTO p_org_id
1251 FROM igc_cc_headers_all
1252 WHERE cc_header_id = g_cc_header_id;
1253 IF (g_debug_mode = 'Y') THEN
1254 Put_Debug_Msg (l_full_path, 'Org ID: '|| p_org_id || ' obtained from igc_cc_headers_all');
1255 END IF;
1256 END IF;
1257 x_entity_type := 'CC_CONTRACTS';
1258 IF l_cc_state = 'PR' THEN
1259 x_event_type_code := 'CC_CONTRACT_PRO_RESERVE';
1260 ELSIF l_cc_state = 'CL' THEN
1261 x_event_type_code := 'CC_CONTRACT_PRO_CANCEL';
1262 ELSIF l_cc_state = 'CM' THEN
1263 x_event_type_code := 'CC_CONTRACT_CMT_RESERVE';
1264 ELSIF l_cc_state = 'CT' THEN
1265 x_event_type_code := 'CC_CONTRACT_CMT_COMPLETE';
1266 END IF;
1267 /*Added for CBC Upgrade - Start*/
1268 ELSIF (l_reference_8 = 'MIG') THEN
1269 SELECT distinct je_category_name INTO l_je_category_name
1270 FROM igc_cc_interface
1271 WHERE cc_header_id = g_cc_header_id;
1272 IF (l_je_category_name = 'Provisional') THEN
1273 x_entity_type := 'CC_CONTRACTS';
1274 x_event_type_code := 'CC_CONTRACT_PRO_RESERVE';
1275 ELSIF (l_je_category_name = 'Confirmed') THEN
1276 x_entity_type := 'CC_CONTRACTS';
1277 x_event_type_code := 'CC_CONTRACT_CMT_RESERVE';
1278 END IF;
1279 END IF;
1280 /*Added for CBC Upgrade - End*/
1281 ELSIF l_doc_type = 'REQ' THEN
1282 IF (p_org_id IS NULL) THEN
1283 SELECT org_id INTO p_org_id
1284 FROM po_requisition_headers_all
1285 WHERE requisition_header_id = g_cc_header_id;
1286 IF (g_debug_mode = 'Y') THEN
1287 Put_Debug_Msg (l_full_path, 'Org ID: '|| p_org_id || ' obtained from po_requisition_headers_all' );
1288 END IF;
1289 END IF;
1290 x_entity_type := 'CC_REQUISITIONS';
1291 x_event_type_code := 'CC_REQUISITION_EVENT';
1292 ELSIF l_doc_type in ('PO', 'REL') THEN
1293 IF (p_org_id IS NULL) THEN
1294 SELECT org_id INTO p_org_id
1295 FROM po_headers_all
1296 WHERE po_header_id = g_cc_header_id;
1297 IF (g_debug_mode = 'Y') THEN
1298 Put_Debug_Msg (l_full_path, 'Org ID: '|| p_org_id || ' obtained from po_headers_all' );
1299 END IF;
1300 END IF;
1301 x_entity_type := 'CC_PURCHASE_ORDERS';
1302 x_event_type_code := 'CC_PURCHASE_ORDER_EVENT';
1303 ELSIF l_doc_type = 'PA' THEN
1304 IF (p_org_id IS NULL) THEN
1305 SELECT proj.org_id INTO p_org_id
1306 FROM pa_budget_versions BUD,
1307 pa_projects_all PROJ
1308 WHERE proj.project_id = bud.project_id
1309 AND bud.budget_version_id = g_cc_header_id;
1310 IF (g_debug_mode = 'Y') THEN
1311 Put_Debug_Msg (l_full_path, 'Org ID: '|| p_org_id || ' obtained from pa_budget_versions' );
1312 END IF;
1313 END IF;
1314 x_entity_type := 'CC_PROJECTS';
1315 x_event_type_code := 'CC_PROJECT_BUDGET_BASELINE';
1316 END IF;
1317
1318 END GET_EVENT_DETAILS;
1319
1320 PROCEDURE Restore_events_gt IS
1321
1322 BEGIN
1323
1324 FOR i IN 1..g_xla_events_gt_tbl.COUNT
1325 LOOP
1326 INSERT INTO psa_bc_xla_events_gt (event_id, result_code)
1327 VALUES (g_xla_events_gt_tbl(i).event_id, g_xla_events_gt_tbl(i).result_code);
1328 END LOOP;
1329
1330 END Restore_events_gt;
1331
1332 PROCEDURE Update_Event_ID IS
1333
1334 BEGIN
1335
1336 FOR i IN 1..g_cc_interface_head_tbl.COUNT
1337 LOOP
1338
1339 UPDATE igc_cc_interface SET event_id = g_cc_interface_head_tbl(i).event_id
1340 WHERE
1341 cc_header_id = g_cc_interface_head_tbl(i).cc_header_id AND
1342 document_type = g_cc_interface_head_tbl(i).document_type AND
1343 budget_dest_flag = g_cc_interface_head_tbl(i).budget_dest_flag AND
1344 cc_transaction_date = g_cc_interface_head_tbl(i).cc_transaction_date AND
1345 nvl(cc_det_pf_line_id, 1) = nvl(g_cc_interface_head_tbl(i).cc_det_pf_line_id, 1) AND
1346 reference_4 = g_cc_interface_head_tbl(i).reference_4;
1347
1348 END LOOP;
1349
1350 END Update_Event_ID;
1351
1352 FUNCTION Get_Max_Result_Code(x_sev_rank OUT NOCOPY NUMBER) RETURN VARCHAR2
1353 IS
1354
1355 l_batch_result_code igc_cc_result_code_ranks.funds_checker_code%TYPE;
1356 l_err_code NUMBER;
1357 l_err_msg VARCHAR2(200);
1358 l_full_path VARCHAR2(255);
1359
1360 BEGIN
1361
1362 l_batch_result_code := NULL;
1363 l_full_path := g_path || 'Get Max Result Code';
1364
1365 IF (g_debug_mode = 'Y') THEN
1366 Put_Debug_Msg (l_full_path, 'CC Header ID: '|| g_cc_header_id );
1367 END IF;
1368
1369 SELECT distinct funds_checker_code, severity_rank INTO l_batch_result_code, x_sev_rank
1370 FROM igc_cc_result_code_ranks
1371 WHERE severity_rank =
1372 (
1373 SELECT min(severity_rank)
1374 FROM igc_cc_result_code_ranks
1375 WHERE funds_checker_code IN
1376 (
1377 SELECT TRIM(cbc_result_code)
1378 FROM igc_cc_interface
1379 WHERE cc_header_id = g_cc_header_id
1380 )
1381 );
1382
1383 RETURN l_batch_result_code;
1384
1385 END Get_Max_Result_Code;
1386
1387 /*R12 Uptake. Bug No 6341012 - End*/
1388
1389 END IGC_CBC_FUNDS_CHECKER;