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