[Home] [Help]
PACKAGE BODY: APPS.LNS_XLA_EVENTS
Source
1 PACKAGE BODY LNS_XLA_EVENTS AS
2 /* $Header: LNS_XLA_EVENTS_B.pls 120.17 2010/12/28 11:39:37 mbolli ship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9 G_FILE_NAME CONSTANT VARCHAR2(30) := 'LNS_XLA_EVENTS_B.pls';
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_XLA_EVENTS';
12 G_DAYS_COUNT NUMBER;
13 G_DAYS_IN_YEAR NUMBER;
14
15 --------------------------------------------
16 -- internal package routines
17 --------------------------------------------
18 procedure logMessage(log_level in number
19 ,module in varchar2
20 ,message in varchar2)
21 is
22
23 begin
24
25 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
26 FND_LOG.STRING(log_level, module, message);
27 if FND_GLOBAL.Conc_Request_Id is not null then
28 fnd_file.put_line(FND_FILE.LOG, message);
29 end if;
30 END IF;
31
32 end;
33
34
35
36 /*=========================================================================
37 || PUBLIC PROCEDURE create_event
38 ||
39 || DESCRIPTION
40 || Overview: will write to xla_events table
41 ||
42 || PSEUDO CODE/LOGIC
43 ||
44 || PARAMETERS
45 || Parameter: p_loan_id => loan_id
46 || ,p_disb_header_id => for disbursement
47 || ,p_loan_amount_adj_id => for loan Amount Adjustments of direct loans
48 || ,p_loan_line => for additional receivable of ERS loans
49 || ,p_event_type_code => seeded code for loans "APPROVED" "IN_FUNDING"
50 || ,p_event_date => most likely GL_DATE
51 || ,p_event_status => event Status
52 || CONSTANT = 'U'; -- event status:unprocessed
53 || CONSTANT = 'I'; -- event status:incomplete
54 || CONSTANT = 'N'; -- event status:noaction
55 ||
56 || Return value:
57 || standard
58 || KNOWN ISSUES
59 ||
60 || NOTES
61 ||
62 || MODIFICATION HISTORY
63 || Date Author Description of Changes
64 || 4/11/2005 raverma Created
65 || 15-Mar-2010 mbolli MultiDisbursement - added loanAmountAdjustmentId as param
66 || 13-Apr-2010 mboli Added new param p_loan_line_id
67 *=======================================================================*/
68 procedure create_event(p_loan_id in number
69 ,p_disb_header_id in number
70 ,p_loan_amount_adj_id in number default NULL
71 ,p_loan_line_id in number default NULL
72 ,p_event_type_code in varchar
73 ,p_event_date in date
74 ,p_event_status in varchar2
75 ,p_init_msg_list in varchar2
76 ,p_commit in varchar2
77 ,p_bc_flag in varchar2
78 ,x_event_id out nocopy number
79 ,x_return_status out nocopy varchar2
80 ,x_msg_count out nocopy number
81 ,x_msg_data out nocopy varchar2)
82 is
83
84 l_api_name varchar2(15);
85 l_return_status VARCHAR2(1);
86 l_msg_count NUMBER;
87 l_msg_data VARCHAR2(32767);
88
89 l_event_id integer;
90 l_loan_details XLA_EVENTS_PUB_PKG.t_event_source_info;
91 l_security_context XLA_EVENTS_PUB_PKG.t_security;
92 l_legal_entity_id number;
93 l_sob_id number;
94 l_loan_number varchar2(60);
95 l_disb_header_id number;
96 l_loan_amount_adj_id number;
97 l_loan_line_id number;
98 l_event_exists boolean;
99 l_event_info xla_events_pub_pkg.t_event_info;
100
101 l_old_event_id NUMBER;
102 l_array_event_info xla_events_pub_pkg.t_array_event_info ;
103 l_delete_event NUMBER;
104 l_entity_count NUMBER;
105
106
107
108 CURSOR C_Get_Loan_Info (X_Loan_Id NUMBER) IS
109 SELECT LEGAL_ENTITY_ID
110 ,LOAN_NUMBER
111 FROM LNS_LOAN_HEADERS_ALL
112 WHERE LOAN_ID = X_Loan_Id;
113
114 CURSOR c_entity_count(c_old_event_id NUMBER) IS
115 SELECT count(event_id)
116 FROM xla_events
117 WHERE entity_id = (select entity_id from xla_events where event_id = c_old_event_id);
118
119 cursor c_sob_id(c_loan_id NUMBER) is
120 select so.ledger_id
121 from lns_system_options sb,
122 gl_ledgers so,
123 lns_loan_headers lhdr
124 where sb.set_of_books_id = so.ledger_id
125 and lhdr.loan_id = c_loan_id
126 and lhdr.org_id = sb.org_id;
127
128 CURSOR C_Get_Event (p_application_id NUMBER,
129 p_ledger_id NUMBER,
130 p_entity_type_code VARCHAR2,
131 p_source_id_int_1 NUMBER,
132 p_source_id_int_2 NUMBER,
133 p_source_id_int_3 NUMBER,
134 p_source_id_int_4 NUMBER,
135 p_valuation_method VARCHAR2) IS
136 SELECT xe.event_id
137 FROM xla_transaction_entities xte
138 ,xla_events xe
139 ,xla_entity_types_b xet
140 WHERE xte.application_id = p_application_id
141 AND xte.ledger_id = p_ledger_id
142 AND xte.entity_code = p_entity_type_code
143 AND NVL(xte.source_id_int_1,-99) = NVL(p_source_id_int_1,-99)
144 AND NVL(xte.source_id_int_2,-99) = NVL(p_source_id_int_2,-99)
145 AND NVL(xte.source_id_int_3,-99) = NVL(p_source_id_int_3,-99)
146 AND NVL(xte.source_id_int_4,-99) = NVL(p_source_id_int_4,-99)
147 AND NVL(xte.valuation_method,' ') = NVL(p_valuation_method,' ')
148 AND xe.entity_id = xte.entity_id
149 AND xet.application_id = xte.application_id
150 AND xte.entity_code = xet.entity_code;
151
152 begin
153
154 l_api_name := 'create_event';
155 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
156
157 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
158 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_id = ' || p_loan_id);
159 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
160 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_amount_adj_id = ' || p_loan_amount_adj_id);
161 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_line_id = ' || p_loan_line_id);
162 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_type_code = ' || p_event_type_code);
163 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_date = ' || p_event_date);
164 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_status = ' || p_event_status);
165 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_bc_flag = ' || p_bc_flag);
166
167 -- Standard Start of API savepoint
168 SAVEPOINT create_event;
169
170 -- Initialize message list IF p_init_msg_list is set to TRUE.
171 IF FND_API.to_Boolean(p_init_msg_list) THEN
172 FND_MSG_PUB.initialize;
173 END IF;
174
175 -- Initialize API return status to SUCCESS
176 x_return_status := FND_API.G_RET_STS_SUCCESS;
177
178 -- ---------------------------------------------------------------------
179 -- Api body
180 -- ---------------------------------------------------------------------
181
182 open C_Get_Loan_Info(p_loan_id);
183 fetch C_Get_Loan_Info into l_legal_entity_id, l_loan_number;
184 close C_Get_Loan_Info;
185
186 open c_sob_id(p_loan_id);
187 fetch c_sob_id into l_sob_id;
188 close c_sob_id;
189
190 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_number = ' || l_loan_number);
191 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_legal_entity_id = ' || l_legal_entity_id);
192 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_sob_id = ' || l_sob_id);
193
194 -- force caller to pass disbursement_id/loan_amount_adj_id/loan_line_id
195 if (p_disb_header_id is null and p_loan_amount_adj_id is null and p_loan_line_id is null)then
196 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
197 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed API call: ' || l_api_name || ' SQLERRM: ' || SQLERRM);
198 FND_MSG_PUB.ADD;
199 RAISE FND_API.G_EXC_ERROR;
200 end if;
201
202 -- initialize any variables here
203 l_loan_details.application_id := 206; -- XLA registered application
204 l_loan_details.ledger_id := l_sob_id; -- l_sob_id;
205 l_loan_details.legal_entity_id := l_legal_entity_id;
206 l_loan_details.source_id_int_1 := p_loan_id; -- loan_id
207 l_loan_details.entity_type_code := 'LOANS';
208 l_loan_details.transaction_number := l_loan_number;
209 l_loan_details.source_id_int_2 := NVL(p_disb_header_id, -1); -- disb_header_id
210 l_loan_details.source_id_int_3 := NVL(p_loan_amount_adj_id, -1); -- loan_amount_adj_id
211 l_loan_details.source_id_int_4 := NVL(p_loan_line_id, -1); -- loan_line_id
212 l_loan_details.source_application_id := 206; -- XLA registered application
213
214 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_1 = ' || l_loan_details.source_id_int_1);
215 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_2 = ' || l_loan_details.source_id_int_2);
216 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_3 = ' || l_loan_details.source_id_int_3);
217 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_4 = ' || l_loan_details.source_id_int_4);
218 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.transaction_number = ' || l_loan_details.transaction_number);
219 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_application_id = ' || l_loan_details.source_application_id);
220 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.entity_type_code = ' || l_loan_details.entity_type_code);
221
222 BEGIN
223 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Checking if event already exists...');
224 IF NOT XLA_EVENTS_PUB_PKG.event_exists
225 (p_event_source_info => l_loan_details
226 ,p_event_type_code => p_event_type_code
227 ,p_valuation_method => null
228 ,p_security_context => l_security_context)
229 THEN
230 -- Call the XLA create_event API
231 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Event doesnt exist');
232
233 x_event_id := XLA_EVENTS_PUB_PKG.create_event
234 (
235 p_event_source_info => l_loan_details
236 ,p_event_type_code => p_event_type_code -- event type code
237 ,p_event_date => p_event_date -- gl date
238 ,p_event_status_code => p_event_status -- event status
239 ,p_event_number => NULL
240 ,p_reference_info => NULL
241 ,p_valuation_method => NULL
242 ,p_security_context => l_security_context
243 ,p_budgetary_control_flag => p_bc_flag
244 );
245
246 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'x_event_id = ' || x_event_id);
247 if x_event_id is null then
248 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
249 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed to create Loans XLA event ' || p_event_type_code);
250 FND_MSG_PUB.ADD;
251 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
252 RAISE FND_API.G_EXC_ERROR;
253 end if;
254 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully created Loans XLA event ' || p_event_type_code);
255
256 ELSE
257 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Event already exists');
258
259 IF (p_event_type_code <> 'LOAN_ACCRUAL') THEN
260
261 l_array_event_info := xla_events_pub_pkg.get_array_event_info
262 ( p_event_source_info => l_loan_details,
263 p_event_type_code => p_event_type_code,
264 p_valuation_method => null,
265 p_security_context => l_security_context
266 );
267 l_old_event_id := l_array_event_info(1).event_id;
268 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_old_event_id is '||l_old_event_id);
269
270 OPEN c_entity_count(l_old_event_id);
271 FETCH c_entity_count INTO l_entity_count;
272 CLOSE c_entity_count;
273
274 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Entity Count is '||l_entity_count);
275
276
277 -- Cleanup XLA tables for this event
278 xla_events_pub_pkg.DELETE_EVENT
279 (
280 p_event_source_info => l_loan_details,
281 p_event_id => l_old_event_id,
282 p_valuation_method => null,
283 p_security_context => l_security_context
284 );
285 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleted l_old_event_id '||l_old_event_id);
286
287 -- Need to delete the entities from the XLA tables if the entity_id is matched to only one event
288
289 IF (l_entity_count <= 1) THEN
290
291 l_delete_event := xla_events_pub_pkg.DELETE_ENTITY( p_source_info => l_loan_details,
292 p_valuation_method => null,
293 p_security_context => l_security_context);
294 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Entity Id of the event is deleted '||l_delete_event);
295
296 END IF;
297
298 END IF;
299
300 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'After deletion, create new event');
301 -- Call the XLA create_event API
302 x_event_id := XLA_EVENTS_PUB_PKG.create_event
303 (
304 p_event_source_info => l_loan_details
305 ,p_event_type_code => p_event_type_code -- event type code
306 ,p_event_date => p_event_date -- gl date
307 ,p_event_status_code => p_event_status -- event status
308 ,p_event_number => NULL
309 ,p_reference_info => NULL
310 ,p_valuation_method => NULL
311 ,p_security_context => l_security_context
312 ,p_budgetary_control_flag => p_bc_flag
313 );
314
315 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'x_event_id = ' || x_event_id);
316 if x_event_id is null then
317 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
318 FND_MESSAGE.SET_TOKEN('ERROR' ,'Failed to create Loans XLA event ' || p_event_type_code);
319 FND_MSG_PUB.ADD;
320 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
321 RAISE FND_API.G_EXC_ERROR;
322 end if;
323 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully created Loans XLA event ' || p_event_type_code);
324 END IF; -- END of IF NOT XLA_EVENTS_PUB_PKG.event_exists
325 END; -- END of event Creation code
326
327 -- ---------------------------------------------------------------------
328 -- End of API body
329 -- ---------------------------------------------------------------------
330 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
331
332 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
333
334 EXCEPTION
335 WHEN FND_API.G_EXC_ERROR THEN
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 x_msg_count := l_msg_count;
338 x_msg_data := l_msg_data;
339 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
340 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
341 ROLLBACK TO create_event;
342
343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 x_msg_count := l_msg_count;
346 x_msg_data := l_msg_data;
347 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
348 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
349 ROLLBACK TO create_event;
350
351 WHEN OTHERS THEN
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 x_msg_count := l_msg_count;
354 x_msg_data := l_msg_data;
355 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
356 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
357 ROLLBACK TO create_event;
358
359 end create_event;
360
361 /*=========================================================================
362 || PUBLIC PROCEDURE update_event
363 ||
364 || DESCRIPTION
365 || Overview: will write to xla_events table
366 ||
367 || PSEUDO CODE/LOGIC
368 ||
369 || PARAMETERS
370 || Parameter: p_loan_id => loan_id
371 || ,p_event_type_code => seeded code for loans "APPROVED" "IN_FUNDING"
372 || ,p_event_date => most likely GL_DATE
373 || ,p_event_status => event Status
374 || CONSTANT = 'U'; -- event status:unprocessed
375 || CONSTANT = 'I'; -- event status:incomplete
376 || CONSTANT = 'N'; -- event status:noaction
377 ||
378 || Return value:
379 || standard
380 || KNOWN ISSUES
381 ||
382 || NOTES
383 ||
384 || MODIFICATION HISTORY
385 || Date Author Description of Changes
386 || 4/11/2005 raverma Created
387 ||
388 *=======================================================================*/
389 procedure update_event(p_loan_id in number
390 ,p_disb_header_id in number
391 ,p_loan_amount_adj_id in number default NULL
392 ,p_loan_line_id in number default NULL
393 ,p_event_id in number
394 ,p_event_type_code in varchar
395 ,p_event_date in date
396 ,p_event_status in varchar2
397 ,p_init_msg_list in varchar2
398 ,p_commit in varchar2
399 ,x_return_status out nocopy varchar2
400 ,x_msg_count out nocopy number
401 ,x_msg_data out nocopy varchar2)
402 is
403 l_api_name varchar2(15);
404 l_return_status VARCHAR2(1);
405 l_msg_count NUMBER;
406 l_msg_data VARCHAR2(32767);
407
408 --l_event_id integer;
409 l_loan_details XLA_EVENTS_PUB_PKG.t_event_source_info;
410 l_security_context XLA_EVENTS_PUB_PKG.t_security;
411 l_legal_entity_id number;
412 l_sob_id number;
413
414 CURSOR C_Get_Loan_Info (X_Loan_Id NUMBER) IS
415 SELECT LEGAL_ENTITY_ID
416 FROM LNS_LOAN_HEADERS
417 WHERE LOAN_ID = X_Loan_Id;
418
419 cursor c_sob_id(c_loan_id NUMBER) is
420 select so.ledger_id
421 from lns_system_options sb,
422 gl_ledgers so,
423 lns_loan_headers lhdr
424 where sb.set_of_books_id = so.ledger_id
425 and lhdr.loan_id = c_loan_id
426 and lhdr.org_id = sb.org_id;
427
428 begin
429 l_api_name := 'update_event';
430 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
431 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Input:');
432 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'event_id ' || p_event_id);
433 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_id = ' || p_loan_id);
434 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
435 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_amount_adj_id = ' || p_loan_amount_adj_id);
436 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_line_id = ' || p_loan_line_id);
437 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_type_code ' || p_event_type_code);
438 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_date ' || p_event_date);
439 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_status ' || p_event_status);
440
441 -- Standard Start of API savepoint
442 SAVEPOINT update_event;
443
444 -- Initialize message list IF p_init_msg_list is set to TRUE.
445 IF FND_API.to_Boolean(p_init_msg_list) THEN
446 FND_MSG_PUB.initialize;
447 END IF;
448
449 -- Initialize API return status to SUCCESS
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451
452 -- ---------------------------------------------------------------------
453 -- Api body
454 -- ---------------------------------------------------------------------
455 open C_Get_Loan_Info(p_loan_id);
456 fetch C_Get_Loan_Info into l_legal_entity_id;
457 close C_Get_Loan_Info;
458
459 open c_sob_id(p_loan_id);
460 fetch c_sob_id into l_sob_id;
461 close c_sob_id;
462
463 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_legal_entity_id = ' || l_legal_entity_id);
464 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_sob_id = ' || l_sob_id);
465
466 -- initialize any variables here
467 l_loan_details.application_id := 206; -- XLA registered application
468 l_loan_details.legal_entity_id := l_legal_entity_id; --
469 l_loan_details.ledger_id := l_sob_id; --
470 l_loan_details.source_id_int_1 := p_loan_id; -- loan_id
471 l_loan_details.entity_type_code := 'LOANS';
472 l_loan_details.source_id_int_2 := p_disb_header_id; -- disb_header_id
473 l_loan_details.source_id_int_3 := p_loan_amount_adj_id; -- loan_amount_adj_id
474 l_loan_details.source_id_int_4 := p_loan_line_id; -- loan_line_id
475
476 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_1 = ' || l_loan_details.source_id_int_1);
477 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_2 = ' || l_loan_details.source_id_int_2);
478 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_3 = ' || l_loan_details.source_id_int_3);
479 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_4 = ' || l_loan_details.source_id_int_4);
480 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.entity_type_code = ' || l_loan_details.entity_type_code);
481
482 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling XLA_EVENTS_PUB_PKG.Update_event...');
483 XLA_EVENTS_PUB_PKG.Update_event(p_event_source_info => l_loan_details
484 ,p_event_id => p_event_id
485 ,p_event_type_code => p_event_type_code
486 ,p_event_date => p_event_date
487 ,p_event_status_code => p_event_status
488 ,p_valuation_method => null
489 ,p_security_context => l_security_context);
490
491 -- ---------------------------------------------------------------------
492 -- End of API body
493 -- ---------------------------------------------------------------------
494 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
495
496 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
497
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 x_msg_count := l_msg_count;
502 x_msg_data := l_msg_data;
503 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
504 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
505 ROLLBACK TO update_event;
506
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 x_msg_count := l_msg_count;
510 x_msg_data := l_msg_data;
511 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
512 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
513 ROLLBACK TO update_event;
514
515 WHEN OTHERS THEN
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 x_msg_count := l_msg_count;
518 x_msg_data := l_msg_data;
519 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
520 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
521 ROLLBACK TO update_event;
522
523 end update_event;
524
525 /*=========================================================================
526 || PUBLIC PROCEDURE delete_event
527 ||
528 || DESCRIPTION
529 || Overview: will delete events from xla_events table
530 ||
531 || PSEUDO CODE/LOGIC
532 ||
533 || PARAMETERS
534 || Parameter: p_event_id => event_id
535 ||
536 || Return value:
537 || standard
538 || KNOWN ISSUES
539 ||
540 || NOTES
541 ||
542 || MODIFICATION HISTORY
543 || Date Author Description of Changes
544 || 4/11/2005 raverma Created
545 ||
546 *=======================================================================*/
547 procedure delete_event(p_loan_id in number
548 ,p_disb_header_id in number
549 ,p_loan_amount_adj_id in number default NULL
550 ,p_loan_line_id in number default NULL
551 ,p_event_id in number
552 ,p_init_msg_list in varchar2
553 ,p_commit in varchar2
554 ,x_return_status out nocopy varchar2
555 ,x_msg_count out nocopy number
556 ,x_msg_data out nocopy varchar2)
557 is
558 l_api_name varchar2(15);
559 l_return_status VARCHAR2(1);
560 l_msg_count NUMBER;
561 l_msg_data VARCHAR2(32767);
562
563 CURSOR C_Get_Loan_Info (X_Loan_Id NUMBER) IS
564 SELECT LEGAL_ENTITY_ID
565 FROM LNS_LOAN_HEADERS
566 WHERE LOAN_ID = X_Loan_Id;
567
568 cursor c_sob_id(c_loan_id NUMBER) is
569 select so.ledger_id
570 from lns_system_options sb,
571 gl_ledgers so,
572 lns_loan_headers lhdr
573 where sb.set_of_books_id = so.ledger_id
574 and lhdr.loan_id = c_loan_id
575 and lhdr.org_id = sb.org_id;
576
577 l_loan_details XLA_EVENTS_PUB_PKG.t_event_source_info;
578 l_security_context XLA_EVENTS_PUB_PKG.t_security;
579 l_legal_entity_id number;
580 l_sob_id number;
581
582 begin
583 l_api_name := 'delete_event';
584 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
585 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - event_id ' || p_event_id);
586 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_id = ' || p_loan_id);
587 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
588 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_amount_adj_id = ' || p_loan_amount_adj_id);
589 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_line_id = ' || p_loan_line_id);
590
591
592 -- Standard Start of API savepoint
593 SAVEPOINT delete_event;
594
595 -- Initialize message list IF p_init_msg_list is set to TRUE.
596 IF FND_API.to_Boolean(p_init_msg_list) THEN
597 FND_MSG_PUB.initialize;
598 END IF;
599
600 -- Initialize API return status to SUCCESS
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602
603 -- ---------------------------------------------------------------------
604 -- Api body
605 -- ---------------------------------------------------------------------
606
607 open C_Get_Loan_Info(p_loan_id);
608 fetch C_Get_Loan_Info into l_legal_entity_id;
609 close C_Get_Loan_Info;
610
611 open c_sob_id(p_loan_id);
612 fetch c_sob_id into l_sob_id;
613 close c_sob_id;
614
615 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_legal_entity_id = ' || l_legal_entity_id);
616 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_sob_id = ' || l_sob_id);
617
618 -- initialize any variables here
619 l_loan_details.application_id := 206; -- XLA registered application
620 l_loan_details.legal_entity_id := l_legal_entity_id; --
621 l_loan_details.ledger_id := l_sob_id; --
622 l_loan_details.source_id_int_1 := p_loan_id; -- loan_id
623 l_loan_details.entity_type_code := 'LOANS';
624 l_loan_details.source_id_int_2 := p_disb_header_id; -- disb_header_id
625 l_loan_details.source_id_int_3 := p_loan_amount_adj_id; -- loan_amount_adj_id
626 l_loan_details.source_id_int_4 := p_loan_line_id; -- loan_line_id
627
628 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_1 = ' || l_loan_details.source_id_int_1);
629 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_2 = ' || l_loan_details.source_id_int_2);
630 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_3 = ' || l_loan_details.source_id_int_3);
631 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.source_id_int_4 = ' || l_loan_details.source_id_int_4);
632 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_details.entity_type_code = ' || l_loan_details.entity_type_code);
633
634
635 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling XLA_EVENTS_PUB_PKG.delete_event...');
636 XLA_EVENTS_PUB_PKG.delete_event
637 (p_event_source_info => l_loan_details
638 ,p_event_id => p_event_id
639 ,p_valuation_method => null
640 ,p_security_context => l_security_context);
641
642 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Deleted event_id '||p_event_id||' successfully');
643
644 -- ---------------------------------------------------------------------
645 -- End of API body
646 -- ---------------------------------------------------------------------
647 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
648
649 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
650
651 EXCEPTION
652 WHEN FND_API.G_EXC_ERROR THEN
653 x_return_status := FND_API.G_RET_STS_ERROR;
654 x_msg_count := l_msg_count;
655 x_msg_data := l_msg_data;
656 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
657 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
658 ROLLBACK TO delete_event;
659
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 x_msg_count := l_msg_count;
663 x_msg_data := l_msg_data;
664 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
665 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
666 ROLLBACK TO delete_event;
667
668 WHEN OTHERS THEN
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 x_msg_count := l_msg_count;
671 x_msg_data := l_msg_data;
672 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
673 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
674 ROLLBACK TO delete_event;
675
676 end delete_event;
677
678 end lns_xla_events;