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