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