[Home] [Help]
PACKAGE BODY: APPS.IGC_CBC_GL_FC_PKG
Source
1 PACKAGE BODY IGC_CBC_GL_FC_PKG AS
2 /*$Header: IGCBGFCB.pls 120.23.12000000.10 2007/12/07 14:24:00 dvjoshi ship $*/
3
4 G_PATH CONSTANT VARCHAR2(100):= 'IGC.PLSQL.IGCBGFCB.IGC_CBC_GL_FC_PKG.';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_mode VARCHAR2(1);
8
9 g_line_num NUMBER := 0;
10
11 g_debug_msg VARCHAR2(10000) := NULL;
12
13 g_debug_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
15 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
17 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
19 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
20
21 /* This variable contains constant number which can be added to batch_line_number
22 */
23 g_batch_line_const NUMBER := 100000;
24
25 CURSOR c_igc_cc_int(p_doc_id in NUMBER, p_doc_type IN VARCHAR2) IS
26 SELECT * from IGC_CC_INTERFACE
27 WHERE cc_header_id = p_doc_id
28 AND document_type = p_doc_type;
29 TYPE t_tbl_igc_cc_int IS TABLE OF c_igc_cc_int%ROWTYPE index by PLS_INTEGER;
30 g_tbl_igc_cc_int t_tbl_igc_cc_int;
31
32 CURSOR c_gl_bc_packets(p_event_id NUMBER, p_ledger_id in NUMBER,p_document_type IN VARCHAR2) IS
33 SELECT
34 xte.source_id_int_1 CC_HEADER_ID
35 ,pck.source_distribution_id_num_1 CC_ACCT_LINE_ID
36 ,pck.code_combination_id CODE_COMBINATION_ID
37 ,xah.accounting_date CC_TRANSACTION_DATE
38 ,pck.accounted_dr CC_FUNC_DR_AMT
39 ,pck.accounted_cr CC_FUNC_CR_AMT
40 ,pck.period_name PERIOD_NAME
41 ,'E' ACTUAL_FLAG
42 ,pck.ledger_id SET_OF_BOOKS_ID
43 ,pck.encumbrance_type_id ENCUMBRANCE_TYPE_ID
44 ,pck.result_code CBC_RESULT_CODE
45 ,pck.status_code STATUS_CODE
46 ,pck.funding_budget_version_id BUDGET_VERSION_ID
47 ,pck.currency_code CURRENCY_CODE
48 ,p_document_type DOCUMENT_TYPE
49 ,xal.description TRANSACTION_DESCRIPTION
50 ,p_document_type REFERENCE_1
51 ,xte.source_id_int_1 REFERENCE_2
52 /*Bug 6650138 set to null. We can use it for Version number.
53 I am not changing it at present as code changes large and hence testing
54 */
55 ,NULL REFERENCE_3
56 ,xte.transaction_number REFERENCE_4
57 ,xah.description REFERENCE_5
58 ,pck.packet_id REFERENCE_6
59 ,pck.event_id EVENT_ID
60 ,xah.last_update_date LAST_UPDATE_DATE
61 ,xah.creation_date CREATION_DATE
62 FROM xla_ae_headers xah
63 ,xla_ae_lines xal
64 ,xla_transaction_entities xte
65 ,gl_bc_packets pck
66 WHERE xah.ae_header_id = pck.ae_header_id
67 AND xal.ae_header_id = pck.ae_header_id
68 AND xal.ae_line_num = pck.ae_line_num
69 AND xte.entity_id = xah.entity_id
70 AND pck.event_id = p_event_id
71 AND pck.ledger_id = p_ledger_id
72 Order by pck.event_id,pck.source_distribution_id_num_1;
73
74 TYPE t_tbl_gl_bc_packets IS TABLE OF c_gl_bc_packets%ROWTYPE index by PLS_INTEGER;
75
76 g_gl_pck_count NUMBER;
77
78
79 PROCEDURE Put_Debug_Msg (
80 p_path IN VARCHAR2,
81 p_debug_msg IN VARCHAR2
82 );
83
84 PROCEDURE purge_igc_cc_int (
85 p_document_type IN VARCHAR2, p_document_id IN NUMBER
86 );
87
88 PROCEDURE populate_sbc_records(
89 p_t_tbl_gl_pck IN t_tbl_gl_bc_packets
90 );
91
92 PROCEDURE populate_igc_cc_int (
93 p_tbl_igc_cc_int IN t_tbl_igc_cc_int
94 );
95
96
97 PROCEDURE Put_Debug_Msg (
98 p_path IN VARCHAR2,
99 p_debug_msg IN VARCHAR2
100 ) IS
101 BEGIN
102 IF(g_state_level >= g_debug_level) THEN
103 FND_LOG.STRING(g_state_level, p_path, p_debug_msg);
104 END IF;
105 RETURN;
106 -- --------------------------------------------------------------------
107 -- Exception handler section for the Put_Debug_Msg procedure.
108 -- --------------------------------------------------------------------
109 EXCEPTION
110 WHEN OTHERS THEN
111 NULL;
112 RETURN;
113 END Put_Debug_Msg;
114
115
116 PROCEDURE purge_igc_cc_int (
117 p_document_type IN VARCHAR2, p_document_id IN NUMBER
118 ) IS
119 PRAGMA AUTONOMOUS_TRANSACTION;
120 l_full_path VARCHAR2(255);
121 BEGIN
122 l_full_path := g_path || 'PURGE_IGC_CC_INT';
123
124 IF (g_debug_mode = 'Y') THEN
125 Put_Debug_Msg(l_full_path, 'Start of purge_igc_cc_int');
126 END IF;
127
128 DELETE FROM IGC_CC_INTERFACE
129 WHERE cc_header_id = p_document_id
130 AND document_type = p_document_type;
131
132 COMMIT;
133
134 IF (g_debug_mode = 'Y') THEN
135 Put_Debug_Msg(l_full_path, 'Completed purge_igc_cc_int');
136 END IF;
137 EXCEPTION
138 WHEN OTHERS THEN
139 IF (g_debug_mode = 'Y') THEN
140 Put_Debug_Msg(l_full_path, 'Error in purge_igc_cc_int :'||SQLERRM);
141 END IF;
142 ROLLBACK;
143 RAISE;
144 END;
145
146
147 PROCEDURE populate_sbc_records(
148 p_t_tbl_gl_pck IN t_tbl_gl_bc_packets
149 ) IS
150 PRAGMA AUTONOMOUS_TRANSACTION;
151 l_batch_line_num NUMBER := g_gl_pck_count;
152 l_user_id NUMBER := fnd_global.user_id ;
153 l_full_path VARCHAR2(255);
154 BEGIN
155 l_full_path := g_path || 'POPULATE_SBC_RECORDS';
156 IF (g_debug_mode = 'Y') THEN
157 Put_Debug_Msg(l_full_path, 'Start of populate_sbc_records');
158 END IF;
159 FOR i in p_t_tbl_gl_pck.FIRST..p_t_tbl_gl_pck.LAST
160 LOOP
161 l_batch_line_num := l_batch_line_num+1;
162 INSERT INTO igc_cc_interface
163 (
164 CC_HEADER_ID
165 ,CC_ACCT_LINE_ID
166 ,CODE_COMBINATION_ID
167 ,BATCH_LINE_NUM
168 ,CC_TRANSACTION_DATE
169 ,CC_FUNC_DR_AMT
170 ,CC_FUNC_CR_AMT
171 ,ACTUAL_FLAG
172 ,PERIOD_NAME
173 ,BUDGET_DEST_FLAG
174 ,SET_OF_BOOKS_ID
175 ,ENCUMBRANCE_TYPE_ID
176 ,CBC_RESULT_CODE
177 ,STATUS_CODE
178 ,BUDGET_VERSION_ID
179 ,CURRENCY_CODE
180 ,DOCUMENT_TYPE
181 ,TRANSACTION_DESCRIPTION
182 ,REFERENCE_1
183 ,REFERENCE_2
184 ,REFERENCE_3
185 ,REFERENCE_4
186 ,REFERENCE_5
187 ,REFERENCE_6
188 ,EVENT_ID
189 ,PROJECT_LINE
190 ,LAST_UPDATE_DATE
191 ,LAST_UPDATED_BY
192 ,CREATION_DATE
193 ,CREATED_BY
194 )
195 VALUES
196 (
197 p_t_tbl_gl_pck(i).CC_HEADER_ID
198 ,p_t_tbl_gl_pck(i).CC_ACCT_LINE_ID
199 ,p_t_tbl_gl_pck(i).CODE_COMBINATION_ID
200 ,l_batch_line_num
201 ,p_t_tbl_gl_pck(i).CC_TRANSACTION_DATE
202 ,p_t_tbl_gl_pck(i).CC_FUNC_DR_AMT
203 ,p_t_tbl_gl_pck(i).CC_FUNC_CR_AMT
204 ,p_t_tbl_gl_pck(i).ACTUAL_FLAG
205 ,p_t_tbl_gl_pck(i).PERIOD_NAME
206 ,'S'
207 ,p_t_tbl_gl_pck(i).SET_OF_BOOKS_ID
208 ,p_t_tbl_gl_pck(i).ENCUMBRANCE_TYPE_ID
209 ,p_t_tbl_gl_pck(i).CBC_RESULT_CODE
210 ,p_t_tbl_gl_pck(i).STATUS_CODE
211 ,p_t_tbl_gl_pck(i).BUDGET_VERSION_ID
212 ,p_t_tbl_gl_pck(i).CURRENCY_CODE
213 ,p_t_tbl_gl_pck(i).DOCUMENT_TYPE
214 ,p_t_tbl_gl_pck(i).TRANSACTION_DESCRIPTION
215 ,p_t_tbl_gl_pck(i).REFERENCE_1
216 ,p_t_tbl_gl_pck(i).REFERENCE_2
217 ,p_t_tbl_gl_pck(i).REFERENCE_3
218 ,p_t_tbl_gl_pck(i).REFERENCE_4
219 ,p_t_tbl_gl_pck(i).REFERENCE_5
220 ,p_t_tbl_gl_pck(i).REFERENCE_6
221 ,p_t_tbl_gl_pck(i).EVENT_ID
222 ,'N'
223 ,p_t_tbl_gl_pck(i).LAST_UPDATE_DATE
224 ,l_user_id
225 ,p_t_tbl_gl_pck(i).CREATION_DATE
226 ,l_user_id
227 ) ;
228 END LOOP;
229 IF (g_debug_mode = 'Y') THEN
230 Put_Debug_Msg(l_full_path, 'populate_sbc_records Completed');
231 END IF;
232 COMMIT;
233 EXCEPTION
234 WHEN OTHERS THEN
235 IF (g_debug_mode = 'Y') THEN
236 Put_Debug_Msg(l_full_path, 'Error in populate_sbc_records :'||SQLERRM);
237 END IF;
238 ROLLBACK;
239 RAISE;
240 END;
241
242 PROCEDURE populate_igc_cc_int (
243 p_tbl_igc_cc_int IN t_tbl_igc_cc_int
244 ) IS
245 PRAGMA AUTONOMOUS_TRANSACTION;
246 l_full_path VARCHAR2(255);
247 BEGIN
248 l_full_path := g_path || 'POP_IGC_CC_INT';
249 IF (g_debug_mode = 'Y') THEN
250 Put_Debug_Msg(l_full_path, 'Start of populate_igc_cc_int');
251 END IF;
252 FOR i IN p_tbl_igc_cc_int.FIRST..p_tbl_igc_cc_int.LAST
253 LOOP
254 Insert into igc_cc_interface
255 (
256 CC_HEADER_ID
257 ,CC_ACCT_LINE_ID
258 ,CODE_COMBINATION_ID
259 ,BATCH_LINE_NUM
260 ,CC_TRANSACTION_DATE
261 ,CC_FUNC_DR_AMT
262 ,CC_FUNC_CR_AMT
263 ,ACTUAL_FLAG
264 ,PERIOD_NAME
265 ,BUDGET_DEST_FLAG
266 ,SET_OF_BOOKS_ID
267 ,ENCUMBRANCE_TYPE_ID
268 ,CBC_RESULT_CODE
269 ,STATUS_CODE
270 ,BUDGET_VERSION_ID
271 ,CURRENCY_CODE
272 ,DOCUMENT_TYPE
273 ,TRANSACTION_DESCRIPTION
274 ,REFERENCE_1
275 ,REFERENCE_2
276 ,REFERENCE_3
277 ,REFERENCE_4
278 ,REFERENCE_5
279 ,REFERENCE_6
280 ,EVENT_ID
281 ,PROJECT_LINE
282 ,LAST_UPDATE_DATE
283 ,LAST_UPDATED_BY
284 ,CREATION_DATE
285 ,CREATED_BY
286 )
287 VALUES
288 (
289 p_tbl_igc_cc_int(i).CC_HEADER_ID
290 ,p_tbl_igc_cc_int(i).CC_ACCT_LINE_ID
291 ,p_tbl_igc_cc_int(i).CODE_COMBINATION_ID
292 ,i
293 ,p_tbl_igc_cc_int(i).CC_TRANSACTION_DATE
294 ,p_tbl_igc_cc_int(i).CC_FUNC_DR_AMT
295 ,p_tbl_igc_cc_int(i).CC_FUNC_CR_AMT
296 ,p_tbl_igc_cc_int(i).ACTUAL_FLAG
297 ,p_tbl_igc_cc_int(i).PERIOD_NAME
298 ,p_tbl_igc_cc_int(i).BUDGET_DEST_FLAG
299 ,p_tbl_igc_cc_int(i).SET_OF_BOOKS_ID
300 ,p_tbl_igc_cc_int(i).ENCUMBRANCE_TYPE_ID
301 ,p_tbl_igc_cc_int(i).CBC_RESULT_CODE
302 ,p_tbl_igc_cc_int(i).STATUS_CODE
303 ,p_tbl_igc_cc_int(i).BUDGET_VERSION_ID
304 ,p_tbl_igc_cc_int(i).CURRENCY_CODE
305 ,p_tbl_igc_cc_int(i).DOCUMENT_TYPE
306 ,p_tbl_igc_cc_int(i).TRANSACTION_DESCRIPTION
307 ,p_tbl_igc_cc_int(i).REFERENCE_1
308 ,p_tbl_igc_cc_int(i).REFERENCE_2
309 ,p_tbl_igc_cc_int(i).REFERENCE_3
310 ,p_tbl_igc_cc_int(i).REFERENCE_4
311 ,p_tbl_igc_cc_int(i).REFERENCE_5
312 ,p_tbl_igc_cc_int(i).REFERENCE_6
313 ,p_tbl_igc_cc_int(i).EVENT_ID
314 ,p_tbl_igc_cc_int(i).PROJECT_LINE
315 ,p_tbl_igc_cc_int(i).LAST_UPDATE_DATE
316 ,p_tbl_igc_cc_int(i).LAST_UPDATED_BY
317 ,p_tbl_igc_cc_int(i).CREATION_DATE
318 ,p_tbl_igc_cc_int(i).CREATED_BY
319 );
320 IF (g_debug_mode = 'Y') THEN
321 Put_Debug_Msg(l_full_path, 'Record Inserted :'||SQL%ROWCOUNT);
322 END IF;
323 END LOOP;
324 COMMIT;
325 EXCEPTION
326 WHEN OTHERS THEN
327 IF (g_debug_mode = 'Y') THEN
328 Put_Debug_Msg(l_full_path, 'Error in populate_igc_cc_int :'||SQLERRM);
329 END IF;
330 ROLLBACK;
331 RAISE;
332 END Populate_IGC_CC_INT;
333
334
335
336 FUNCTION glzcbc
337 (
338 p_mode IN VARCHAR2,
339 p_conc_proc IN VARCHAR2 := FND_API.G_FALSE
340 ) RETURN NUMBER AS
341
342 CURSOR c_event_details
343 is
344 Select event_id, application_id, event_type_code, event_status_code, entity_id
345 from xla_events xla
346 where xla.event_id in
347 (
348 Select psa.event_id
349 from psa_bc_xla_events_gt psa
350 )
351 and application_id = 201;
352
353 TYPE t_event_details IS TABLE OF c_event_details%ROWTYPE index by PLS_INTEGER;
354
355 CURSOR c_entity_details(p_entity_id IN NUMBER) IS
356 SELECT Source_id_int_1
357 FROM xla_transaction_entities
358 WHERE entity_id = p_entity_id;
359
360
361
362 l_event_details t_event_details;
363
364 CURSOR c_ledger_details(p_event_id in NUMBER) is
365 Select distinct ledger_id, nvl(cbc_po_enable, 'N') cbc_enable
366 From gl_bc_packets pck,
367 igc_cc_bc_enable cbc
368 where event_id = p_event_id
369 and actual_flag = 'E'
370 and pck.ledger_id = cbc.set_of_books_id(+)
371 and exists
372 (select 1 from gl_ledgers l
373 where l.ledger_id = pck.ledger_id
374 and l.ledger_category_code = 'PRIMARY');
375
376 l_return NUMBER := 1;
377 l_user_id NUMBER;
378 l_rec_count NUMBER;
379
380 cursor c_psa_events is
381 SELECT event_id,result_code from psa_bc_xla_events_gt;
382 Type tbl_psa_event is TABLE OF c_psa_events%ROWTYPE INDEX BY PLS_INTEGER;
383 l_tbl_psa_event tbl_psa_event;
384
385 l_cbc_mode VARCHAR2(1) ;
386
387 l_full_path VARCHAR2(255);
388
389 l_debug_count NUMBER;
390
391 l_year_end BOOLEAN;
392
393
394 BEGIN
395 SAVEPOINT IGC_GLZCBC;
396 l_full_path := g_path || 'GLZCBC';
397 g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
398 l_user_id := fnd_global.user_id ;
399 l_year_end := FALSE ;
400 IF (g_debug_mode = 'Y') THEN
401 Put_Debug_Msg(l_full_path, 'Start of glzcbc');
402 END IF;
403 /* Bug 6650138. Coverered all modes as used in PSA funds checker */
404 IF p_mode in ('R','P','F','A') THEN
405 l_cbc_mode := 'R' ;
406 ELSIF p_mode in ('C', 'M') THEN
407 l_cbc_mode := 'C' ;
408 ELSE
409 l_cbc_mode := 'R';
410 END IF ;
411
412 open c_psa_events;
413 FETCH c_psa_events
414 BULK COLLECT INTO l_tbl_psa_event;
415 CLOSE c_psa_events;
416
417
418 Open c_event_details;
419 FETCH c_event_details
420 BULK COLLECT INTO l_event_details;
421 CLOSE c_event_details;
422
423 delete from psa_bc_xla_events_gt;
424
425 -- nvl(l_event_details.LAST,0)
426 FOR i_evt in 1..nvl(l_event_details.LAST,0)
427 LOOP
428 DECLARE
429 CURSOR c_po_det(p_document_id IN NUMBER) IS
430 SELECT poh.type_lookup_code po_type
431 FROM po_headers_all poh
432 where po_header_id = p_document_id;
433
434 l_document_type VARCHAR2(3);
435 l_document_subtype VARCHAR2(25) ;
436 l_document_id NUMBER;
437 l_main_doc_id NUMBER := NULL;
438 l_main_type VARCHAR2(25) := null ;
439 l_ledger_id NUMBER;
440 l_cbc_enable VARCHAR2(1) := 'N';
441 l_bc_success BOOLEAN ;
442 l_bc_return_status VARCHAR2(2) := null ;
443 l_batch_result_code VARCHAR2(3) ;
444 l_tbl_igc_cc_int t_tbl_igc_cc_int;
445
446 l_t_gl_bc_packets_sbc t_tbl_gl_bc_packets;
447
448 l_process_record VARCHAR2(1) := 'N';
449 l_return_status VARCHAR2(1) ;
450 l_msg_count NUMBER ;
451 l_msg_data VARCHAR2(2000) ;
452 l_accounting_date DATE ;
453 l_packet_accounting_date DATE ;
454 l_count NUMBER;
455
456 BEGIN
457 IF (g_debug_mode = 'Y') THEN
458 Put_Debug_Msg(l_full_path, 'Event Found '||l_event_details(i_evt).event_id||':'||l_event_details(i_evt).event_type_code);
459 END IF;
460
461 OPEN c_ledger_details(l_event_details(i_evt).event_id);
462 FETCH c_ledger_details INTO l_ledger_id, l_cbc_enable;
463 CLOSE c_ledger_details;
464
465 l_year_end := FALSE;
466
467 IF (g_debug_mode = 'Y') THEN
468 Put_Debug_Msg(l_full_path, 'Ledger Found '||l_ledger_id);
469 END IF;
470
471 /* To check if this procedure called for year end */
472
473 SELECT SUM(INSTR(UPPER(xal.description), UPPER(b.description)))
474 INTO l_count
475 FROM po_lookup_codes b,xla_ae_lines xal,xla_ae_headers xah
476 WHERE xah.event_id = l_event_details(i_evt).event_id
477 AND xah.ae_header_id = xal.ae_header_id
478 AND b.lookup_code IN ('IGC YEAR END RESERVE',
479 'IGC YEAR END UNRESERVE')
480 AND b.lookup_type = 'CONTROL ACTIONS';
481
482 -- IF UPPER(l_bc_packets_rec.je_line_description) LIKE '%YEAR%END%'
483 IF NVL(l_count,0) > 0
484 THEN
485 l_year_end := TRUE ;
486 IF (g_debug_mode = 'Y') THEN
487 Put_Debug_Msg(l_full_path, 'Called for IGC Year End : '||l_count);
488 END IF;
489 END IF ;
490
491 IF (l_cbc_enable = 'N') or (l_ledger_id is NULL) or (l_year_end = TRUE) THEN
492 l_process_record := 'N';
493 l_return := 1;
494 ELSE
495 OPEN c_entity_details(l_event_details(i_evt).entity_id);
496 FETCH c_entity_details INTO l_document_id;
497 CLOSE c_entity_details;
498 l_process_record:= 'Y';
499 IF l_event_details(i_evt).event_type_code like 'PO%' THEN
500 l_document_type := 'PO';
501 OPEN c_po_det(l_document_id);
502 FETCH c_po_det INTO l_document_subtype;
503 CLOSE c_po_det;
504 IF l_document_subtype NOT IN ('STANDARD','PLANNED')
505 THEN
506 l_process_record := 'N';
507 l_return := 1;
508 END IF ;
509 ELSIF l_event_details(i_evt).event_type_code like 'REQ%' THEN
510 l_document_type := 'REQ';
511 SELECT type_lookup_code
512 INTO l_document_subtype
513 FROM po_requisition_headers_all
514 WHERE requisition_header_id = l_document_id ;
515 ELSIF l_event_details(i_evt).event_type_code like 'REL%' THEN
516 l_document_type := 'REL';
517 /* Get PO details for a release */
518 SELECT release_type, po_header_id
519 INTO l_document_subtype, l_main_doc_id
520 FROM po_releases_all
521 WHERE po_release_id = l_document_id ;
522
523 l_main_type := 'PO' ;
524
525 END IF;
526
527 IF l_process_record = 'Y' THEN
528
529 IF (g_debug_mode = 'Y') THEN
530 Put_Debug_Msg(l_full_path, 'Document details are '||l_document_type||' : '||l_document_id);
531 END IF;
532
533 IF (g_debug_mode = 'Y') THEN
534 Put_Debug_Msg(l_full_path, 'Before determining l_accounting_date');
535 END IF;
536 -- Get accounting date
537 igc_cbc_po_grp.get_cbc_acct_date
538 (
539 p_api_version => 1.0
540 ,p_init_msg_list => FND_API.G_FALSE
541 ,p_commit => FND_API.G_FALSE
542 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
543 ,x_return_status => l_return_status
544 ,x_msg_count => l_msg_count
545 ,x_msg_data => l_msg_data
546 ,p_document_id => l_document_id
547 ,p_document_type => l_document_type
548 ,p_document_sub_type => l_document_subtype
549 ,p_default => 'Y'
550 ,x_cbc_acct_date => l_packet_accounting_date
551 ) ;
552
553 IF (g_debug_mode = 'Y') THEN
554 Put_Debug_Msg(l_full_path, 'Returned from igc_cbc_po_grp.get_cbc_acct_date '||l_return_status||' : '||l_packet_accounting_date );
555 END IF;
556
557 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_packet_accounting_date IS NULL
558 THEN
559 ROLLBACK TO IGC_GLZCBC ;
560 RETURN(0) ;
561 END IF;
562
563 IF l_packet_accounting_date IS NULL
564 THEN
565 --Get the accounting date from the database
566 igc_cbc_po_grp.get_cbc_acct_date
567 (
568 p_api_version => 1.0
569 ,p_init_msg_list => FND_API.G_FALSE
570 ,p_commit => FND_API.G_FALSE
574 ,x_msg_data => l_msg_data
571 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
572 ,x_return_status => l_return_status
573 ,x_msg_count => l_msg_count
575 ,p_document_id => l_document_id
576 ,p_document_type => l_document_type
577 ,p_document_sub_type => l_document_subtype
578 ,p_default => 'N'
579 ,x_cbc_acct_date => l_accounting_date
580 ) ;
581
582 -- This section would normally be executed when called from
583 -- Doc Import with automatic funds reservation.
584 -- It would not get executed if invoked from the front
585 -- end forms.
586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
587 OR l_accounting_date IS NULL
588 THEN
589 --Default the accounting date
590 igc_cbc_po_grp.get_cbc_acct_date
591 (
592 p_api_version => 1.0
593 ,p_init_msg_list => FND_API.G_FALSE
594 ,p_commit => FND_API.G_FALSE
595 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
596 ,x_return_status => l_return_status
597 ,x_msg_count => l_msg_count
598 ,x_msg_data => l_msg_data
599 ,p_document_id => l_document_id
600 ,p_document_type => l_document_type
601 ,p_document_sub_type => l_document_subtype
602 ,p_default => 'Y'
603 ,x_cbc_acct_date => l_accounting_date
604 ) ;
605
606 -- If there were any errors or if a valid accounting date was not found, return error
607 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR l_accounting_date IS NULL
608 THEN
609
610 ROLLBACK TO IGC_GLZCBC ;
611 RETURN(0) ;
612 END IF ;
613
614 -- A valid accounting date was obtained. Therefore update the document.
615 igc_cbc_po_grp.update_cbc_acct_date
616 (
617 p_api_version => 1.0
618 ,p_init_msg_list => FND_API.G_FALSE
619 ,p_commit => FND_API.G_FALSE
620 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
621 ,x_return_status => l_return_status
622 ,x_msg_count => l_msg_count
623 ,x_msg_data => l_msg_data
624 ,p_document_id => l_document_id
625 ,p_document_type => l_document_type
626 ,p_document_sub_type => l_document_subtype
627 ,p_cbc_acct_date => l_accounting_date
628 ) ;
629
630 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
631 THEN
632 ROLLBACK TO IGC_GLZCBC ;
633 RETURN(0) ;
634 END IF ;
635 END IF; -- Accounting date found from db
636
637 ELSIF l_packet_accounting_date IS NOT NULL
638 THEN
639 l_accounting_date := l_packet_accounting_date ;
640 END IF; -- l_packet_accounting_date null
641
642 IF (g_debug_mode = 'Y') THEN
643 Put_Debug_Msg(l_full_path, 'After determining l_accounting_date :'||l_accounting_date);
644 END IF;
645
646 l_main_type := Nvl(l_main_type, l_document_type);
647 l_main_doc_id := Nvl(l_main_doc_id, l_document_id);
648
649 /* Delete old records from IGC_CC_INTERFACE table */
650 purge_igc_cc_int(l_main_type, l_main_doc_id);
651
652 open c_gl_bc_packets(l_event_details(i_evt).event_id, l_ledger_id, l_document_type);
653 FETCH c_gl_bc_packets
654 BULK COLLECT INTO l_t_gl_bc_packets_sbc;
655 CLOSE c_gl_bc_packets;
656
660
657 g_gl_pck_count := l_t_gl_bc_packets_sbc.LAST;
658
659 /* Number of records in gl_bc_packets */
661 IF (g_debug_mode = 'Y') THEN
662 Put_Debug_Msg(l_full_path, 'Document Id'||l_main_doc_id || ' Records in gl_bc_packets '||l_t_gl_bc_packets_sbc.LAST);
663 END IF;
664
665 FOR l_ind IN l_t_gl_bc_packets_sbc.FIRST..l_t_gl_bc_packets_sbc.LAST
666 LOOP
667 IF l_document_type = 'REL' THEN
668 l_t_gl_bc_packets_sbc(l_ind).CC_HEADER_ID := l_main_doc_id;
669 l_t_gl_bc_packets_sbc(l_ind).DOCUMENT_TYPE := l_main_type ;
670 END IF;
671 Insert into igc_cc_interface
672 (
673 CC_HEADER_ID
674 ,CC_ACCT_LINE_ID
675 ,CODE_COMBINATION_ID
676 ,BATCH_LINE_NUM
677 ,CC_TRANSACTION_DATE
678 ,CC_FUNC_DR_AMT
679 ,CC_FUNC_CR_AMT
680 ,ACTUAL_FLAG
681 ,BUDGET_DEST_FLAG
682 ,SET_OF_BOOKS_ID
683 ,CURRENCY_CODE
684 ,DOCUMENT_TYPE
685 ,TRANSACTION_DESCRIPTION
686 ,REFERENCE_1
687 ,REFERENCE_2
688 ,REFERENCE_3
689 ,REFERENCE_4
690 ,REFERENCE_5
691 ,REFERENCE_6
692 ,PROJECT_LINE
693 ,LAST_UPDATE_DATE
694 ,LAST_UPDATED_BY
695 ,CREATION_DATE
696 ,CREATED_BY
697 )
698 Values
699 (
700 l_t_gl_bc_packets_sbc(l_ind).CC_HEADER_ID
701 ,l_t_gl_bc_packets_sbc(l_ind).CC_ACCT_LINE_ID
702 ,l_t_gl_bc_packets_sbc(l_ind).CODE_COMBINATION_ID
703 ,g_batch_line_const + l_ind
704 ,l_accounting_date
705 ,l_t_gl_bc_packets_sbc(l_ind).CC_FUNC_DR_AMT
706 ,l_t_gl_bc_packets_sbc(l_ind).CC_FUNC_CR_AMT
707 ,l_t_gl_bc_packets_sbc(l_ind).ACTUAL_FLAG
708 ,'C'
709 ,l_t_gl_bc_packets_sbc(l_ind).SET_OF_BOOKS_ID
710 ,l_t_gl_bc_packets_sbc(l_ind).CURRENCY_CODE
711 ,l_t_gl_bc_packets_sbc(l_ind).DOCUMENT_TYPE
712 ,l_t_gl_bc_packets_sbc(l_ind).TRANSACTION_DESCRIPTION
713 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_1
714 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_2
715 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_3
716 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_4
717 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_5
718 ,l_t_gl_bc_packets_sbc(l_ind).REFERENCE_6
719 ,'N'
720 ,l_t_gl_bc_packets_sbc(l_ind).LAST_UPDATE_DATE
721 ,l_user_id
722 ,l_t_gl_bc_packets_sbc(l_ind).CREATION_DATE
723 ,l_user_id
724 );
725 END LOOP;
726
727
728 IF (g_debug_mode = 'Y') THEN
729 Put_Debug_Msg(l_full_path, 'Calling PSA Budgetary Control : '||l_t_gl_bc_packets_sbc.LAST);
730 END IF;
731
732 l_bc_success := igc_cbc_pa_bc_pkg.igcpafck(
733 l_ledger_id,
734 l_main_doc_id,
735 l_cbc_mode,
736 'E',
737 l_main_type,
738 l_bc_return_status,
739 l_batch_result_code,
740 g_debug_mode,
741 p_conc_proc
742 );
743 -- We are not performing SBC funds check through IGCFCK, therefore do not
744 -- need to check for its success or failure.
745
746 IF (g_debug_mode = 'Y') THEN
747 Put_Debug_Msg(l_full_path, 'PSA Budgetary Control completed '||l_bc_return_status||' : '||l_batch_result_code);
748 END IF;
749
750 IF l_bc_success = TRUE
751 AND SUBSTR(l_bc_return_status,1,1) IN ('N', 'S', 'A') -- cbc successful
752 AND l_return = 1 THEN
753 l_return := 1; -- Success ! Funds check passed
754 ELSIF l_bc_success = FALSE
755 OR SUBSTR(l_bc_return_status,1,1) = 'T' -- cbc fatal error
756 THEN
757 l_return := -1;
758 ELSIF l_return <> -1 THEN -- Successful completion but failed funds check
759 l_return := 0;
760 END IF;
761
762 IF (g_debug_mode = 'Y') THEN
763 Put_Debug_Msg(l_full_path, 'Populating PL-SQL table');
764 END IF;
765
766 /* Populate PL-SQL table with igc_cc_interface data */
767 open c_igc_cc_int(l_main_doc_id, l_main_type);
768 FETCH c_igc_cc_int
769 BULK COLLECT INTO l_tbl_igc_cc_int;
770 CLOSE c_igc_cc_int;
771 /* Delete records from IGC_CC_INTERFACE table */
772 DELETE FROM igc_cc_interface
773 WHERE cc_header_id = l_main_doc_id
774 AND document_type = l_main_type;
775
776 /* Now, populate back IGC_CC_INTERFACE table from PL-SQL table.
777 This procedure will be called in autonomous transaction mode
778 */
779 populate_igc_cc_int(l_tbl_igc_cc_int);
780
781 populate_sbc_records(l_t_gl_bc_packets_sbc);
782
783 IF (g_debug_mode = 'Y') THEN
784 Put_Debug_Msg(l_full_path, 'Call to populate_igc_cc_int is successful');
785 END IF;
786
787 END IF; -- l_doc_type
788
789
790 END IF; --cbc_enable
791 END;
792 END LOOP;
793
794 FOR Idx IN l_tbl_psa_event.FIRST .. l_tbl_psa_event.LAST
795 LOOP
796 INSERT INTO psa_bc_xla_events_gt(event_id,result_code )
797 VALUES
798 (l_tbl_psa_event(Idx).event_id,l_tbl_psa_event(Idx).result_code );
799 END LOOP;
800
801 IF (g_debug_mode = 'Y') THEN
802 Put_Debug_Msg(l_full_path, 'Returning Value : '||l_return);
803 END IF;
804 return l_return;
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 IF (g_debug_mode = 'Y') THEN
809 Put_Debug_Msg(l_full_path, 'Error in GLZCBC :'||SQLERRM);
810 END IF;
811 ROLLBACK TO IGC_IGCFCK ;
812 RETURN(-1) ;
813
814 END glzcbc ;
815
816 /* ------------------------------------------------------------------------
817 RECONCILE_GLZCBC gets called from glzfrs() to give the CBC funds checker
818 a chance to reconcile with the GL funds check result. It will be used to
819 rollback the CBC journals which might have already been created in case GL
820 funds check has failed.
821 If we dont rollback the CBC journals,they get committed by the PO
822 processes even if the Standard budget funds check fails.
823 ------------------------------------------------------------------------ */
824
825 FUNCTION RECONCILE_GLZCBC
826 (
827 p_mode IN VARCHAR2
828 ) RETURN NUMBER IS
829 BEGIN
830
831 Return(1);
832
833 End RECONCILE_GLZCBC;
834
835
836
837 END IGC_CBC_GL_FC_PKG;