[Home] [Help]
PACKAGE BODY: APPS.FV_BE_XLA_PKG
Source
1 PACKAGE BODY FV_BE_XLA_PKG AS
2 /* $Header: FVBEXLAB.pls 120.16.12020000.2 2013/02/12 21:04:13 snama ship $ */
3
4 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
6 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
9 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_MODULE_NAME CONSTANT VARCHAR2(50) :='FV.PLSQL.FV_BE_XLA_PKG.';
12
13
14 ----------------------------------------------------------------
15 -- Definition of Accounting Event Entities, Classes and Types.
16 ----------------------------------------------------------------
17
18 --Budget Execution Entity
19
20 BE_ENTITY CONSTANT VARCHAR2(30) := 'BE_TRANSACTIONS';
21
22 BE_CLASS CONSTANT VARCHAR2(30) := 'BUDGET_EXECUTION';
23
24 BUDGET_AUTHORITY_TYPE CONSTANT VARCHAR2(30) := 'BA_RESERVE';
25 FUND_DISTRIBUTION_TYPE CONSTANT VARCHAR2(30) := 'FD_RESERVE';
26
27 --Reprogram Budget Execution Entity
28
29 BE_RPR_ENTITY CONSTANT VARCHAR2(30) := 'BE_RPR_TRANSACTIONS';
30
31 BE_RPR_CLASS CONSTANT VARCHAR2(30) := 'RPR_BUDGET_EXECUTION';
32
33 RPR_BUDGET_AUTHORITY_TYPE CONSTANT VARCHAR2(30) := 'RPR_BA_RESERVE';
34 RPR_FUND_DISTRIBUTION_TYPE CONSTANT VARCHAR2(30) := 'RPR_FD_RESERVE';
35
36 ----------------------------------------------------------------
37 -- Global varibales declaration
38 ----------------------------------------------------------------
39
40 --Event class variable
41 g_event_class VARCHAR2(30);
42
43 --Application_id variables
44 g_application_id FND_APPLICATION.APPLICATION_ID%TYPE;
45
46 g_event_type VARCHAR2(30);
47 g_doc_type VARCHAR2(80);
48 g_entity_code VARCHAR2(80);
49 g_doc_id NUMBER;
50 g_entity_id NUMBER;
51 g_ledger_id GL_LEDGERS.LEDGER_ID%TYPE;
52 g_accounting_date DATE;
53
54 ----------------------------------------------------------------
55 -- AOL User and Resp id
56 ----------------------------------------------------------------
57
58 g_user_id FND_USER.USER_ID%TYPE;
59 g_user_resp_id FND_RESPONSIBILITY.RESPONSIBILITY_ID%TYPE;
60
61 ----------------------------------------------------------------
62 -- Defintions of Private functions/procedures
63 ----------------------------------------------------------------
64
65 -- Definition of create_be_acct_event function(private)
66
67 FUNCTION create_be_acct_event (p_calling_sequence IN VARCHAR2)
68 RETURN INTEGER;
69
70 -- Definition of get_event_source_info function (private)
71
72 FUNCTION get_event_source_info
73 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
74
75 -- Defintion of stamping the event_id of the transaction
76 PROCEDURE stamp_event(p_event_id XLA_EVENTS.EVENT_ID%TYPE);
77
78 -- Definition of reset event_id of the transaction
79 PROCEDURE reset_event(p_event_id XLA_EVENTS.EVENT_ID%TYPE);
80
81 -- Definition of populate_bc_events_tab (private)
82 PROCEDURE populate_bc_events_tab;
83
84
85 /*============================================================================
86 | PROCEDURE - BUDGETARY_CONTROL (PUBLIC)
87 |
88 | DESCRIPTION
89 | This procedure is for creating accounting events and invoke BC API for
90 | user actions i.e., Check and Approve the BE transactions
91 |
92 | PRAMETERS
93 | IN :
94 | p_ledger_id : Ledger Id of the document
95 | p_doc_id : Doc_id for BE Transactions
96 | Transaction_id for Reprogrm BE Transactions
97 | p_doc_type : Possible values:
98 | 'BE_TRANSACTIONS', 'BE_RPR_TRANSACTIONS'
99 | p_event_type: Possible values:
100 | 'BA_RESERVE,'FD_RESERVE',RPR_BA_RESERVE'
101 | and 'RPR_FD_RESERVE'
102 | p_accounting_date : Accounting Date
103 | p_bc_mode : Possible values:
104 | C - Funds Check, R - Funds Reserve
105 | p_calling_sequence : debug information
106 |
107 | OUT:
108 | x_return_status: Possible values
109 | S- Success,E- Error,U- Unexpected
110 | x_status_code : Possible values
111 | XLA_ERROR,FAIL,RFAIL,FATAL,PARTIAL,ADVISORY
112 | and SUCCESS
113 |
114 |
115 | KNOWN ISSUES:
116 |
117 | NOTES:
118 *===========================================================================*/
119
120 PROCEDURE BUDGETARY_CONTROL (p_ledger_id IN NUMBER
121 ,p_doc_id IN NUMBER
122 ,p_doc_type IN VARCHAR2
123 ,p_event_type IN VARCHAR2
124 ,p_accounting_date IN DATE
125 ,p_bc_mode IN VARCHAR2 DEFAULT NULL
126 ,p_calling_sequence IN VARCHAR2
127 ,p_user_resp_id IN NUMBER DEFAULT NULL
128 ,x_return_status OUT NOCOPY VARCHAR2
129 ,x_status_code OUT NOCOPY VARCHAR2)
130 IS
131
132 l_calling_sequence VARCHAR2(2000);
133 l_module_name VARCHAR2(1000);
134
135 l_event_id XLA_EVENTS.EVENT_ID%TYPE;
136
137 l_msg_data VARCHAR2(1000);
138 l_msg_count NUMBER;
139 l_api_version VARCHAR2(100);
140 l_init_msg_list VARCHAR2(1000);
141 l_bc_mode VARCHAR2(1);
142 l_return_status VARCHAR2(1);
143 l_status_code VARCHAR2(100);
144 l_packet_id GL_BC_PACKETS.PACKET_ID%TYPE;
145
146 BEGIN
147
148 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start with FV_BE_XLA_PKG.Budgetary_Control');
149
150 l_calling_sequence := p_calling_sequence ||
151 ' -> FV_BE_XLA_PKG.BUDGETARY_CONTROL';
152
153 l_module_name := G_MODULE_NAME ||'BUDGETARY_CONTROL';
154
155 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
156
157 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
158 'procedure budgetary_control starts.');
159 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
160 'l_calling_sequence -> '||l_calling_sequence);
161
162 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Parameter values');
163
164 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_ledger_id -> '
165 ||p_ledger_id);
166 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_doc_id -> '
167 ||p_doc_id);
168 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_doc_type -> '
169 ||p_doc_type);
170 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_event_type -> '
171 ||p_event_type);
172 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_accounting_date->'
173 ||p_accounting_date);
174 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_bc_mode -> '
175 ||p_bc_mode);
176 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'p_user_resp_id -> '
177 ||p_user_resp_id);
178
179 END IF;
180
181 -- Assign the parameters to the local variables
182 g_application_id := 8901;
183 g_doc_type := p_doc_type;
184 g_event_type := p_event_type;
185 g_doc_id := p_doc_id;
186 g_ledger_id := p_ledger_id;
187 g_accounting_date:= p_accounting_date;
188 l_bc_mode := p_bc_mode;
189
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Input Parameter values...');
191 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_application_id -->'||g_application_id);
192 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_doc_type -->'||g_doc_type);
193 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_event_type -->'||g_event_type);
194 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_doc_id -->'||g_doc_id);
195 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_ledger_id -->'||g_ledger_id);
196 FND_FILE.PUT_LINE(FND_FILE.LOG,'g_accounting_date -->'||g_accounting_date);
197 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_bc_mode -->'||l_bc_mode);
198
199
200 IF l_bc_mode ='C' THEN
201 l_init_msg_list := FND_API.G_TRUE;
202 ELSIF l_bc_mode ='R' THEN
203 l_init_msg_list := FND_API.G_FALSE;
204 END IF;
205
206 --assign AOL user and responsibilty values to global variable
207 g_user_id := FND_GLOBAL.USER_ID;
208
209 if (p_user_resp_id is not null) then
210 g_user_resp_id := p_user_resp_id;
211 else
212 g_user_resp_id := FND_GLOBAL.RESP_ID;
213 end if;
214
215 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'g_user_resp_id -> '
216 || g_user_resp_id );
217 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
218 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
219 'call Create_be_acct_event');
220 END IF;
221 -- Invoke Create_be_acct_event
222 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoke Create_BE_Acct_Event() ');
223
224 l_event_id := create_be_acct_event(p_calling_sequence => l_calling_sequence);
225
226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_event_id -->'||l_event_id);
227 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
228 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'l_event_id -> '||
229 l_event_id);
230 END IF;
231
232 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
233 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
234 'call populate_bc_events_tab');
235 END IF;
236
237 -- Insert into psa_bc_xla_events_gt
238 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoke populate_bc_events() ');
239 populate_bc_events_tab;
240
241
242 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
243 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'call BC PSA API');
244 END IF;
245
246
247 -- call PSA Budgetary Control API
248 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoke PSA_BC_XLA_PUB.Budgetary_Control() ');
249
250 PSA_BC_XLA_PUB.Budgetary_Control (p_api_version => 1.0
251 ,p_init_msg_list => l_init_msg_list
252 ,x_return_status => l_return_status
253 ,x_msg_count => l_msg_count
254 ,x_msg_data => l_msg_data
255 ,p_application_id => g_application_id
256 ,p_bc_mode => l_bc_mode
257 ,p_override_flag => 'Y'
258 ,p_user_id => g_user_id
259 ,p_user_resp_id => g_user_resp_id
260 ,x_status_code => l_status_code
261 ,x_packet_id => l_packet_id);
262
263 IF l_status_code IN ('SUCCESS','PARTIAL','ADVISORY') THEN
264 FND_FILE.PUT_LINE(FND_FILE.LOG,'PSA Budgetary Control API Success');
265 ELSIF l_status_code = 'XLA_ERROR' THEN
266 FND_FILE.PUT_LINE(FND_FILE.LOG,'PSA BC API failed with XLA_ERROR');
267 ELSIF l_status_code IN ('FAIL','RFAIL','FATAL') THEN
268 FND_FILE.PUT_LINE(FND_FILE.LOG,'PSA BC API failed with some technical problem');
269 ELSIF l_status_code = 'XLA_NO_JOURNAL' THEN
270 FND_FILE.PUT_LINE(FND_FILE.LOG,'PSA BC API success but with NO SLA Journal ');
271 ELSE
272 FND_FILE.PUT_LINE(FND_FILE.LOG,'PSA BC API failed with unknown error');
273 END IF;
274
275 -- Assign the local status values to the return variables
276 x_status_code := l_status_code;
277 x_return_status := l_return_status;
278
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 x_return_status := FND_API.G_RET_STS_ERROR;
282 FND_MSG_PUB.count_and_get(p_count => l_msg_count,p_data=>l_msg_data);
283
284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 FND_MSG_PUB.count_and_get(p_count => l_msg_count,p_data=>l_msg_data);
287
288 WHEN OTHERS THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290
291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
292 FND_MSG_PUB.add_exc_msg(G_MODULE_NAME, 'PSA_BC_XLA_PVT');
293 END IF;
294
295 FND_MSG_PUB.count_and_get(p_count=>l_msg_count,p_data=>l_msg_data);
296
297 END BUDGETARY_CONTROL;
298
299 /*============================================================================
300 | FUNCTION - CREATE_BE_ACCT_EVENT (PRIVATE)
301 |
302 | DESCRIPTION
303 | This function is for the creation of all events resulting from user
304 | actions i.e., Check and Approve the BE transactions
305 |
306 | PARAMETERS
307 | IN
308 | p_calling_sequence : For Debug purpose
309 |
310 | RETURN : INTEGER
311 |
312 | KNOWN ISSUES:
313 |
314 | NOTES:
315 *===========================================================================*/
316
317 FUNCTION CREATE_BE_ACCT_EVENT (p_calling_sequence IN VARCHAR2)
318 RETURN INTEGER
319 IS
320
321
322 -- Cursor to pull Event_Id for Delete_Events API for accounting_date change
323 Cursor fv_be_event_id IS
324 SELECT distinct fvbe.event_id,xlae.event_date
325 FROM fv_be_trx_dtls FVBE, xla_events XLAE
326 WHERE FVBE.doc_id = g_doc_id
327 AND FVBE.event_id = XLAE.event_id
328 AND FVBE.gl_date <> XLAE.event_date
329 AND FVBE.transaction_status <> 'AR'
330 AND fvbe.approval_date is null
331 UNION
332 SELECT distinct fvbe.event_id,xlae.event_date
333 FROM fv_be_rpr_transactions FVBE, xla_events XLAE
334 WHERE FVBE.transaction_id = g_doc_id
335 AND FVBE.event_id = XLAE.event_id
336 AND FVBE.gl_date <> XLAE.event_date
337 AND FVBE.transaction_status <> 'AR';
338
339
340 -- Cursor to pull gl_date from FV_BE_TRX_DTLS table for doc_id passed
341 Cursor fv_be_gl_date IS
342 SELECT distinct fvbe.gl_date,fvbe.event_id
343 FROM fv_be_trx_dtls FVBE
344 WHERE FVBE.doc_id = g_doc_id
345 AND fvbe.approval_date is null
346 UNION
347 SELECT distinct fvbe.gl_date,fvbe.event_id
348 FROM fv_be_rpr_transactions FVBE
349 WHERE FVBE.transaction_id = g_doc_id;
350
351
352 l_module_name VARCHAR(1000);
353 l_event_id INTEGER;
354
355 -- XLA event source plsql table definition
356
357 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
358 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
359
360 l_entity_deleted INTEGER;
361
362 BEGIN
363 FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Create_BE_Acct_event function');
364
365 l_module_name := G_MODULE_NAME ||'CREATE_BE_ACCT_EVENTS';
366
367 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
368 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
369 'Create_be_acct_events start..');
370 END IF;
371
372 --Assign the event source info from transactions
373
374 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
375 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
376 'Call get_event_source_info function');
377 END IF;
378
379 l_event_source_info := get_event_source_info;
380
381 l_event_security_context.security_id_int_1 := NULL;
382
383
384 -- STEP#1 -DELETING EVENTS ---
385 -- Invoke DELETE_EVENT to delete the events fetched in the cursor
386 BEGIN
387 FOR fv_be_event_id_Rec in fv_be_event_id
388 LOOP
389 l_event_id := fv_be_event_id_Rec.event_id;
390 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
391 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Call Delete_Event API');
392 END IF;
393
394 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
395 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
396 'Check if event exists before deleteion for event:'||l_event_id);
397 END IF;
398 IF XLA_EVENTS_PUB_PKG.EVENT_EXISTS
399 (p_event_source_info => l_event_source_info
400 ,p_event_class_code => g_event_class
401 ,p_event_type_code => g_event_type
402 ,p_event_date => fv_be_event_id_Rec.event_date
403 ,p_event_status_code => NULL
404 ,p_event_number => NULL
405 ,p_valuation_method => NULL
406 ,p_security_context => l_event_security_context) THEN
407 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
408 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Call Delete_Event API:'||l_event_id);
409 END IF;
410 XLA_EVENTS_PUB_PKG.DELETE_EVENT
411 (p_event_source_info => l_event_source_info
412 ,p_event_id => l_event_id
413 ,p_valuation_method => NULL
414 ,p_security_context => l_event_security_context);
415 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
416 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Call Delete_Entity API:'||l_event_id);
417 END IF;
418
419 l_entity_deleted := XLA_EVENTS_PUB_PKG.delete_entity
420 (p_source_info => l_event_source_info
421 ,p_valuation_method => NULL
422 ,p_security_context => l_event_security_context);
423
424 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
425 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Reset event_id:'||l_event_id);
426 END IF;
427
428 reset_event(l_event_id);
429
430 END IF;
431 END LOOP;
432 END;
433
434
435
436 -- STEP#2 - Find whether accounting_date changed and create/update events
437 -- Loop the cursor through gl_date
438
439 BEGIN
440
441 FOR fv_be_gl_date_Rec in fv_be_gl_date
442 LOOP
443 g_accounting_date := fv_be_gl_date_Rec.gl_date;
444
445 IF NOT XLA_EVENTS_PUB_PKG.EVENT_EXISTS
446 (p_event_source_info => l_event_source_info
447 ,p_event_class_code => g_event_class
448 ,p_event_type_code => g_event_type
449 ,p_event_date => g_accounting_date
450 ,p_event_status_code => NULL
451 ,p_event_number => NULL
452 ,p_valuation_method => NULL
453 ,p_security_context => l_event_security_context) OR
454 fv_be_gl_date_Rec.event_id IS NULL THEN
455 -- Create Event
456 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
457 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Call xla_events_pub_pkg.create_event');
458 END IF;
459 l_event_id := XLA_EVENTS_PUB_PKG.CREATE_EVENT
460 (p_event_source_info => l_event_source_info
461 ,p_event_type_code => g_event_type
462 ,p_event_date => g_accounting_date
463 ,p_event_status_code => 'U'
464 ,p_transaction_date => NULL
465 ,p_reference_info => NULL
466 ,p_event_number => NULL
467 ,p_valuation_method => NULL
468 ,p_security_context => l_event_security_context
469 ,p_budgetary_control_flag => 'Y');
470 ELSE
471 l_event_id := fv_be_gl_date_Rec.event_id;
472 END IF;
473 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
474 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,'Event_id:'||l_event_id);
475 END IF;
476
477 IF(l_event_id IS NOT NULL) THEN
478 -- Stamp Event in FV_BE_TRX_DTLS/FV_BE_RPR_TRANSACTIONS table
479 FND_FILE.PUT_LINE(FND_FILE.LOG,'Invoke Stamp_Event()');
480 stamp_event(l_event_id);
481 END IF;
482 END LOOP;
483 END;
484 FND_FILE.PUT_LINE(FND_FILE.LOG,'End of Create_BE_Acct_Event');
485
486 return l_event_id;
487
488 END CREATE_BE_ACCT_EVENT;
489
490 /*============================================================================
491 | FUNCTION - GET_BE_EVENT_SOURCE_INFO(PRIVATE)
492 |
493 | DESCRIPTION
494 | This function is used to get Budget Execution event source information
495 |
496 | PARAMETERS:
497 | IN
498 | p_ledger_id: Ledger ID
499 | p_doc_id : Document ID
500 | p_calling_sequence: Debug information
501 |
502 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
503 |
504 | KNOWN ISSUES:
505 |
506 | NOTES:
507 *===========================================================================*/
508 FUNCTION get_event_source_info
509 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
510 IS
511
512 l_doc_num VARCHAR2(50);
513 l_prepare_stmt VARCHAR2(1000);
514 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
515 l_module_name VARCHAR2(1000);
516
517 BEGIN
518 FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Get_Event_Source_Info');
519
520 l_module_name := G_MODULE_NAME ||'GET_EVENT_SOURCE_INFO';
521
522 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
523 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
524 'get_event_source_info start..');
525 END IF;
526
527 l_prepare_stmt := 'SELECT DOC_NUMBER FROM ';
528
529 IF g_doc_type = BE_ENTITY THEN
530
531 l_prepare_stmt := l_prepare_stmt ||'FV_BE_TRX_HDRS WHERE DOC_ID = :1';
532 l_event_source_info.entity_type_code := BE_ENTITY;
533 g_event_class := BE_CLASS;
534
535 ELSIF g_doc_type = BE_RPR_ENTITY THEN
536
537 l_prepare_stmt := l_prepare_stmt ||'FV_BE_RPR_TRANSACTIONS WHERE TRANSACTION_ID = :1';
538 l_event_source_info.entity_type_code := BE_RPR_ENTITY;
539 g_event_class := BE_RPR_CLASS;
540
541 END IF;
542
543 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
544 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name||'get_event_source_info start..');
545 END IF;
546
547 EXECUTE IMMEDIATE l_prepare_stmt INTO l_doc_num USING g_doc_id;
548
549 l_event_source_info.transaction_number := l_doc_num;
550 l_event_source_info.application_id := g_application_id;
551 l_event_source_info.ledger_id := g_ledger_id;
552 l_event_source_info.source_id_int_1 := g_doc_id;
553
554 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Get_Event_Source_Info');
555
556 RETURN l_event_source_info;
557
558 END get_event_source_info;
559
560 /*============================================================================
561 | PROCEDURE - POPULATE_BC_EVENTS_TAB(PRIVATE)
562 |
563 | DESCRIPTION
564 | This procedure is used to insert event into psa_bc_xla_events_gt table
565 |
566 | PRAMETERS:
567 | NULL
568 |
569 |
570 | KNOWN ISSUES:
571 |
572 | NOTES:
573 *===========================================================================*/
574
575 PROCEDURE populate_bc_events_tab
576 IS
577 BEGIN
578
579 FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Populate_BC_Events_Tab');
580
581 IF g_doc_id is null THEN
582 RETURN;
583 ELSE
584 IF g_doc_type = BE_ENTITY THEN
585 INSERT INTO psa_bc_xla_events_gt(event_id,result_code)
586 SELECT distinct event_id,'XLA_ERROR'
587 FROM FV_BE_TRX_DTLS
588 WHERE doc_id = g_doc_id
589 AND transaction_status <> 'AR'
590 AND approval_date IS NULL;
591 ELSIF g_doc_type = BE_RPR_ENTITY THEN
592 INSERT INTO psa_bc_xla_events_gt(event_id,result_code)
593 SELECT distinct event_id,'XLA_ERROR' FROM FV_BE_RPR_TRANSACTIONS WHERE transaction_id = g_doc_id;
594 END IF;
595 END IF;
596 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Populate_BC_Events_Tab');
597 END populate_bc_events_tab;
598
599 /*=========================================================================
600 | PROCEDURE - STAMP_EVENT(PRIVATE)
601 |
602 | DESCRIPTION
603 | This procedure is used to stamp event_id in the transactions table
604 |
605 | PRAMETERS:
606 | IN
607 | p_event_id: Event ID
608 |
609 |
610 | KNOWN ISSUES:
611 |
612 | NOTES:
613 *===========================================================================*/
614
615 PROCEDURE stamp_event(p_event_id XLA_EVENTS.EVENT_ID%TYPE)
616 IS
617 BEGIN
618 FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Stamp_Event');
619
620 IF g_doc_type = BE_ENTITY THEN
621 UPDATE FV_BE_TRX_DTLS
622 SET EVENT_ID = p_event_id
623 WHERE doc_id = g_doc_id
624 AND gl_date = g_accounting_date
625 AND transaction_status <> 'AR'
626 AND approval_date IS NULL;
627
628 ELSIF g_doc_type = BE_RPR_ENTITY THEN
629 UPDATE FV_BE_RPR_TRANSACTIONS
630 SET EVENT_ID = p_event_id
631 WHERE transaction_id = g_doc_id
632 AND gl_date = g_accounting_date;
633 END IF;
634 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows ->'||SQL%ROWCOUNT||' updated with event_id ->'||p_event_id);
635
636 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Stamp_Event');
637
638 END stamp_event;
639
640 /*============================================================================
641 | PROCEDURE - RESET_EVENT(PRIVATE)
642 |
643 | Description:
644 | This procedure resets the event_id to null for deleted events.
645 |
646 | Parameters:
647 | IN
648 | p_event_id: Event ID
649 | KNOWN ISSUES:
650 |
651 | NOTES:
652 *===========================================================================*/
653
654
655 PROCEDURE reset_event(p_event_id XLA_EVENTS.EVENT_ID%TYPE)
656 IS
657 BEGIN
658 FND_FILE.PUT_LINE(FND_FILE.LOG,'Begin Reset_Event');
659
660 IF g_doc_type = BE_ENTITY THEN
661 UPDATE FV_BE_TRX_DTLS
662 SET EVENT_ID = null
663 WHERE doc_id = g_doc_id
664 AND event_id = p_event_id;
665
666 ELSIF g_doc_type = BE_RPR_ENTITY THEN
667 UPDATE FV_BE_RPR_TRANSACTIONS
668 SET EVENT_ID = null
669 WHERE transaction_id = g_doc_id
670 AND event_id = p_event_id;
671 END IF;
672 FND_FILE.PUT_LINE(FND_FILE.LOG,'Rows ->'||SQL%ROWCOUNT||' updated with null event_id ');
673
674 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Reset_Event');
675
676 END reset_event;
677
678 Function GET_CCID(application_short_name IN Varchar2
679 ,key_flex_code IN Varchar2
680 ,structure_number IN Number
681 ,validation_date IN Date
682 ,concatenated_segments IN Varchar2) Return Number Is
683 l_date date;
684 l_delim varchar2(1);
685 l_num number;
686 l_segarray fnd_flex_ext.segmentarray;
687 l_ccid number;
688 l_data_set number;
689 l_module_name varchar2(1000);
690 Begin
691 l_module_name := G_MODULE_NAME ||'GET_CCID';
692 If (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) Then
693 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
694 'GET_CCID Parameters ' );
695 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
696 'Application Short Name ' || application_short_name );
697 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
698 'Flex Code ' || key_flex_code);
699 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
700 'Chart of Account Id ' || structure_number );
701 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
702 'Concatenated Segments ' || concatenated_segments);
703 End If;
704
705 l_date := validation_date;
706 l_delim := fnd_flex_ext.get_delimiter(
707 application_short_name
708 ,key_flex_code
709 ,structure_number);
710 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name, 'Delimiter ' || l_delim);
711
712 l_num := fnd_flex_ext.breakup_segments(
713 concatenated_segments
714 ,l_delim
715 ,l_segarray);
716 If (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) Then
717 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name, 'No of Segments ' || l_num);
718 End If;
719
720 If fnd_flex_ext.get_combination_id(
721 application_short_name => application_short_name
722 ,key_flex_code => key_flex_code
723 ,structure_number => structure_number
724 ,validation_date => l_date
725 ,n_segments => l_num
726 ,segments => l_segarray
727 ,combination_id => l_ccid
728 ,data_set => l_data_set) Then
729 If (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) Then
730 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
731 'Code Combination Id fetched ' || l_ccid);
732 End If;
733 Return (l_ccid);
734 End If;
735 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
736 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
737 'Code Combination Id cannot be fetched returning 0 ');
738 End If;
739 Return (0);
740 Exception
741 When Others Then
742 If (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
743 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
744 'Exception encountered returning 0 ');
745 FV_UTILITY.DEBUG_MESG(G_LEVEL_PROCEDURE,l_module_name,
746 'Exception ' || sqlerrm);
747 End If;
748 Return(0);
749 End GET_CCID;
750 END FV_BE_XLA_PKG;