[Home] [Help]
PACKAGE BODY: APPS.LNS_DISTRIBUTIONS_PUB
Source
1 PACKAGE BODY LNS_DISTRIBUTIONS_PUB AS
2 /* $Header: LNS_DIST_PUBP_B.pls 120.45.12010000.4 2009/03/03 08:45:17 mbolli ship $ */
3
4 /*========================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_DEBUG_COUNT NUMBER := 0;
8 G_DEBUG BOOLEAN := FALSE;
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'LNS_DISTRIBUTIONS_PUB';
11
12 --------------------------------------------
13 -- internal package routines
14 --------------------------------------------
15 procedure logMessage(log_level in number
16 ,module in varchar2
17 ,message in varchar2)
18 is
19
20 begin
21 dbms_output.put_line(message);
22 IF log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
23 FND_LOG.STRING(log_level, module, message);
24 if FND_GLOBAL.Conc_Request_Id is not null then
25 fnd_file.put_line(FND_FILE.LOG, message);
26 end if;
27 END IF;
28 end;
29
30
31 procedure cancel_disbursements(p_init_msg_list in varchar2
32 ,p_commit in varchar2
33 ,p_loan_id in number
34 ,x_return_status OUT NOCOPY VARCHAR2
35 ,x_msg_count OUT NOCOPY NUMBER
36 ,x_msg_data OUT NOCOPY VARCHAR2)
37 is
38
39 l_api_name varchar2(50);
40 l_event_id number;
41 l_budget_req_approval varchar2(1);
42 l_funds_reserved_flag varchar2(1);
43 l_gl_date date;
44 l_budget_event_exists number;
45 l_status_code varchar2(25);
46 l_return_status varchar2(1);
47 l_packet_id number;
48 l_msg_count number;
49 l_msg_data VARCHAR2(2000);
50 l_version number;
51 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
52 l_disbursement_id number;
53
54 cursor c_events(p_loan_id number) is
55 select event_id
56 from xla_transaction_entities xlee
57 ,xla_events xle
58 where xle.application_id = 206
59 and xle.entity_id = xlee.entity_id
60 and xlee.source_id_int_1 = p_loan_id
61 and xle.budgetary_control_flag = 'Y'
62 and xle.event_type_code = 'FUTURE_DISBURSEMENT_CANCELLED'
63 and xle.process_status_code <> 'P';
64
65 cursor c_budget_req(p_loan_id number) is
66 select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'Y')
67 ,nvl(h.funds_reserved_flag, 'N')
68 ,nvl(h.gl_date, sysdate)
69 from lns_loan_headers h,
70 lns_loan_products p
71 where p.loan_product_id = h.product_id
72 and h.loan_id = p_loan_id;
73
74 cursor c_obj_vers(p_loan_id number) is
75 select object_version_number
76 from lns_loan_headers
77 where loan_id = p_loan_id;
78
79 cursor c_disbursements(p_loan_id number) is
80 select disb_header_id
81 from lns_disb_headers
82 where loan_id = p_loan_id
83 and disbursement_number = 1;
84
85 begin
86
87 SAVEPOINT cancel_disbursements;
88 l_api_name := 'cancel_disbursements';
89 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
90 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id ' || p_loan_id);
91
92 -- Initialize message list IF p_init_msg_list is set to TRUE.
93 IF FND_API.to_Boolean( p_init_msg_list ) THEN
94 FND_MSG_PUB.initialize;
95 END IF;
96
97 -- Initialize API return status to SUCCESS
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 -- first complete accounting for any unprocessed events / documents for the loan transaction
101 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.onlineAccounting...');
102 lns_distributions_pub.onlineAccounting(p_loan_id => p_loan_id
103 ,p_init_msg_list => fnd_api.g_false
104 ,p_accounting_mode => 'F'
105 ,p_transfer_flag => 'Y'
106 ,p_offline_flag => 'N'
107 ,p_gl_posting_flag => 'N'
108 ,x_return_status => l_return_status
109 ,x_msg_count => l_msg_count
110 ,x_msg_data => l_msg_data);
111 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
112 if l_return_status <> 'S' then
113 RAISE FND_API.G_EXC_ERROR;
114 end if;
115
116 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'fetching l_disbusement_id ');
117 open c_disbursements(p_loan_id);
118 fetch c_disbursements into l_disbursement_id;
119 close c_disbursements;
120 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_disbusement_id ' || l_disbursement_id);
121
122 if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y') then
123
124 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'federal enabled');
125 open c_budget_req(p_loan_id);
126 fetch c_budget_req into l_budget_req_approval, l_funds_reserved_flag, l_gl_date;
127 close c_budget_req;
128 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_budget_req_approval ' || l_budget_req_approval);
129 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_funds_reserved_flag ' || l_funds_reserved_flag);
130
131 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_XLA_EVENTS.create_event...');
132
133 LNS_XLA_EVENTS.create_event(p_loan_id => p_loan_id
134 ,p_disb_header_id => l_disbursement_id
135 ,p_event_type_code => 'FUTURE_DISBURSEMENT_CANCELLED'
136 ,p_event_date => l_gl_date
137 ,p_event_status => 'U'
138 ,p_init_msg_list => fnd_api.g_false
139 ,p_commit => fnd_api.g_false
140 ,p_bc_flag => 'Y'
141 ,x_event_id => l_event_id
142 ,x_return_status => l_return_status
143 ,x_msg_count => l_msg_count
144 ,x_msg_data => l_msg_data);
145 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
146 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
147
148 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
149 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACCOUNTING_EVENT_ERROR');
150 FND_MSG_PUB.ADD;
151 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END IF;
154
155 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping new event_id on distributions');
156 update lns_distributions
157 set event_id = l_event_id
158 ,last_update_date = sysdate
159 where distribution_type = 'ORIGINATION'
160 and loan_id = p_loan_id
161 and event_id is not null
162 and disb_header_id is null;
163 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
164
165 if l_funds_reserved_flag = 'Y' then
166
167 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT - event => ' || l_event_id);
168 insert into PSA_BC_XLA_EVENTS_GT (event_id, result_code)
169 values (l_event_id, 'FAIL');
170
171 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling PSA_BC_XLA_PUB.Budgetary_Control ' || l_event_id);
172 -- always pass P_BC_MODE = reserve as per shaniqua williams
173 PSA_BC_XLA_PUB.Budgetary_Control(p_api_version => 1.0
174 ,p_init_msg_list => FND_API.G_FALSE
175 ,x_return_status => l_return_status
176 ,x_msg_count => l_msg_count
177 ,x_msg_data => l_msg_data
178 ,p_application_id => 206
179 ,p_bc_mode => 'R'
180 ,p_override_flag => null
181 ,p_user_id => null
182 ,p_user_resp_id => null
183 ,x_status_code => l_status_code
184 ,x_packet_ID => l_packet_id);
185
186 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'BC status is = ' || l_return_status);
187 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_status_code = ' || l_status_code);
188 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_packet_id = ' || l_packet_id);
189
190 -- we want to commit ONLY in the case of SUCCESS or ADVISORY
191 if (l_return_status <> 'S') then
192
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
195 FND_MESSAGE.SET_TOKEN('ERROR' ,'Call to PSA_BC_XLA_PUB.Budgetary_Control failed with Status Code = ' || l_status_code);
196 FND_MSG_PUB.ADD;
197 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
198 RAISE FND_API.G_EXC_ERROR;
199
200 else
201 -- caller handle success status
202 null;
203
204 end if; -- BC_API.RETURN_STATUS
205
206 end if; -- l_funds_reserved_flag
207
208 end if;
209
210 x_return_status := l_return_status;
211 IF FND_API.to_Boolean(p_commit)
212 THEN
213 COMMIT WORK;
214 END IF;
215
216 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
217
218 EXCEPTION
219
220 WHEN FND_API.G_EXC_ERROR THEN
221 ROLLBACK TO cancel_disbursements;
222 x_return_status := FND_API.G_RET_STS_ERROR;
223 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
224 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 ROLLBACK TO cancel_disbursements;
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
230 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
231
232 WHEN OTHERS THEN
233 ROLLBACK TO cancel_disbursements;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
236 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
237
238 end cancel_disbursements;
239
240 /*=========================================================================
241 || FUNCTION GENERATE_BC_REPORT
242 ||
243 || DESCRIPTION
244 || this function generatesthe BC report and returns the sequence_id
245 || needed for the rpt
246 ||
247 || PARAMETERS p_loan_id => loan identifier
248 ||
249 || Return value: sequence for BC report
250 ||
251 || Source Tables:
252 ||
253 || Target Tables: NA
254 ||
255 || KNOWN ISSUES
256 ||
257 || NOTES
258 ||
259 || MODIFICATION HISTORY
260 || Date Author Description of Changes
261 || 10-01-2005 raverma Created
262 *=======================================================================*/
263 FUNCTION GENERATE_BC_REPORT(p_loan_id number) RETURN NUMBER IS
264
265 l_api_name varchar2(50);
266 l_count NUMBER;
267 l_event_id number;
268 l_distribution_id number;
269 l_errbuf VARCHAR2(3000);
270 l_retcode NUMBER;
271 l_ledger_id number;
272 l_application_id number;
273 l_event_flag varchar2(1);
274 l_sequence_id number;
275
276 cursor c_event (p_loan_id number) is
277 select xle.event_id, xll.source_distribution_id_num_1, ledger_id
278 from xla_transaction_entities xlee
279 ,xla_events xle
280 ,xla_distribution_links xll
281 where xle.application_id = 206
282 and xle.entity_id = xlee.entity_id
283 and xlee.source_id_int_1 = p_loan_id
284 and xle.budgetary_control_flag = 'Y'
285 and xll.event_id =xle.event_id;
286
287 BEGIN
288
289 l_sequence_id := -1;
290 l_application_id := 206;
291 l_event_flag := 'E';
292 l_api_name := 'generate_bc_report';
293
294 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
295 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id ' || p_loan_id);
296
297 -- steps 1. delete from PSA_BC_REPORT_EVENTS_GT
298 -- 2. insert into PSA_BC_REPORT_EVENTS_GT
299 -- 3. generate sequence
300 -- 4. call Create_BC_Transaction_report API
301 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'deleting from PSA_BC_REPORT_EVENTS_GT');
302 DELETE FROM PSA_BC_REPORT_EVENTS_GT;
303
304 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'opening c_event...');
305 open c_event(p_loan_id);
306 LOOP
307 fetch c_event into
308 l_event_id
309 ,l_distribution_id
310 ,l_ledger_id;
311 EXIT WHEN c_event%NOTFOUND;
312
313 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
314 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_id = ' || l_distribution_id);
315 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_ledger_id = ' || l_ledger_id);
316 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into psa table...');
317 INSERT INTO PSA_BC_REPORT_EVENTS_GT
318 (event_id
319 ,SOURCE_DISTRIBUTION_ID_NUM_1
320 ,SOURCE_DISTRIBUTION_ID_NUM_2)
321 values(l_event_id
322 ,l_distribution_id
323 ,null);
324 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
325 end loop;
326 close c_event;
327
328 select PSA_BC_XML_REPORT_S.nextval
329 into l_sequence_id
330 from dual;
331
332 SELECT count(*) INTO l_count
333 FROM PSA_BC_REPORT_EVENTS_GT;
334 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'rows found ' || l_count);
335
336 IF l_count > 0 then
337
338 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'calling PSA_BC_XML_REPORT_PUB.Create_BC_Transaction_Report...');
339 -- Call the XML Genertion Procedure
340 PSA_BC_XML_REPORT_PUB.Create_BC_Transaction_Report(l_errbuf
341 ,l_retcode
342 ,l_ledger_id
343 ,l_application_id
344 ,l_event_flag
345 ,l_sequence_id);
346 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_errbuf = ' || l_errbuf);
347 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_retcode = ' || l_retcode);
348 END IF;
349
350 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_sequence_id = ' || l_sequence_id);
351 return l_sequence_id;
352
353 END GENERATE_BC_REPORT;
354
355 /*=========================================================================
356 || FUNCTION budgetary_control
357 ||
358 || DESCRIPTION
359 || this function does funds check / funds reserve
360 ||
361 ||
362 || PARAMETERS p_loan_id => loan identifier
363 || p_budgetary_control_mode => 'C' Check ; 'R' Reserve
364 ||
365 || Return value: x_budgetary_status_code
366 || SUCCESS = FUNDS CHECK / RESERVE SUCCESSFUL
367 || PARTIAL = AT LEAST ONE EVENT FAILED
368 || FAIL = FUNDS CHECK / RESERVE FAILED
369 || XLA_ERROR = XLA SetUp ERROR
370 || ADVISORY = BUDGETARY WARNING
371 ||
372 || Source Tables: NA
373 ||
374 || Target Tables: NA
375 ||
376 || MODIFICATION HISTORY
377 || Date Author Description of Changes
378 || 10-01-2005 raverma Created
379 *=======================================================================*/
380 procedure budgetary_control(p_init_msg_list in varchar2
381 ,p_commit in varchar2
382 ,p_loan_id in number
383 ,p_budgetary_control_mode in varchar2
384 ,x_budgetary_status_code out nocopy varchar2
385 ,x_return_status OUT NOCOPY VARCHAR2
386 ,x_msg_count OUT NOCOPY NUMBER
387 ,x_msg_data OUT NOCOPY VARCHAR2)
388 is
389 l_api_name varchar2(50);
390 l_msg_count NUMBER;
391 l_msg_data VARCHAR2(2000);
392 l_return_Status VARCHAR2(1);
393 l_status_code varchar2(50);
394 l_packet_id number;
395 l_event_id number;
396 l_version number;
397 l_budget_req_approval varchar2(1);
398 l_funds_reserved_flag varchar2(1);
399 l_gl_date date;
400 l_budget_event_exists number;
401 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
402 l_disbursement_id number;
403 x_event_id number;
404
405 cursor c_budget_req(p_loan_id number) is
406 select nvl(p.BDGT_REQ_FOR_APPR_FLAG, 'Y')
407 ,nvl(h.funds_reserved_flag, 'N')
408 ,nvl(h.gl_date, sysdate)
409 from lns_loan_headers h,
410 lns_loan_products p
411 where p.loan_product_id = h.product_id
412 and h.loan_id = p_loan_id;
413
414 -- get budgetary control events only
415 cursor c_events(p_loan_id number) is
416 select event_id
417 from xla_transaction_entities xlee
418 ,xla_events xle
419 where xle.application_id = 206
420 and xle.entity_id = xlee.entity_id
421 and xlee.source_id_int_1 = p_loan_id
422 and xle.budgetary_control_flag = 'Y'
423 and xle.event_type_code = 'DIRECT_LOAN_APPROVED'
424 and xle.process_status_code <> 'P';
425
426 cursor c_disbursements(p_loan_id number) is
427 select disb_header_id
428 from lns_disb_headers
429 where loan_id = p_loan_id
430 and disbursement_number = 1;
431
432 cursor c_obj_vers(p_loan_id number) is
433 select object_version_number
434 from lns_loan_headers
435 where loan_id = p_loan_id;
436
437 cursor c_budget_event(p_loan_id number, p_disb_header_id number) is
438 select count(1)
439 from xla_transaction_entities xlee
440 ,xla_events xle
441 where xle.application_id = 206
442 and xle.entity_id = xlee.entity_id
443 and xlee.source_id_int_1 = p_loan_id
444 and xlee.source_id_int_2 = p_disb_header_id
445 and xle.budgetary_control_flag = 'Y';
446
447 begin
448
449 SAVEPOINT budgetary_control_pvt;
450 l_api_name := 'budgetary_control';
451 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
452 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
453 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_budgetary_control_mode = ' || p_budgetary_control_mode);
454
455 -- Initialize message list IF p_init_msg_list is set to TRUE.
456 IF FND_API.to_Boolean( p_init_msg_list ) THEN
457 FND_MSG_PUB.initialize;
458 END IF;
459
460 -- Initialize API return status to SUCCESS
461 x_return_status := FND_API.G_RET_STS_SUCCESS;
462
463 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'fetching l_disbusement_id ');
464 open c_disbursements(p_loan_id);
465 fetch c_disbursements into l_disbursement_id;
466 close c_disbursements;
467 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_disbusement_id = ' || l_disbursement_id);
468
469 -- Bug#6711479 We can't check funds without valid disbursement
470 IF (l_disbursement_id IS NULL) THEN
471 FND_MESSAGE.SET_NAME('LNS', 'LNS_CHK_FUND_DISB_INVALID');
472 FND_MSG_PUB.ADD;
473 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
474 FND_MESSAGE.SET_NAME('LNS', 'LNS_CREATE_DISB_SCHED');
475 FND_MSG_PUB.ADD;
476 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' AND l_disbursement_id IS NOT NULL) then
481
482 -- check if budget event exists
483 -- find if budgetary event already exists, if not, create the event
484 open c_budget_event(p_loan_id, l_disbursement_id);
485 fetch c_budget_event into l_budget_event_exists;
486 close c_budget_event;
487 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_budget_event_exists = ' || l_budget_event_exists);
488
489 open c_budget_req(p_loan_id);
490 fetch c_budget_req into l_budget_req_approval, l_funds_reserved_flag, l_gl_date;
491 close c_budget_req;
492 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_budget_req_approval = ' || l_budget_req_approval);
493 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_funds_reserved_flag = ' || l_funds_reserved_flag);
494
495 if l_budget_event_exists = 0 then
496 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_XLA_EVENTS.create_event...');
497
498 LNS_XLA_EVENTS.create_event(p_loan_id => p_loan_id
499 ,p_disb_header_id => l_disbursement_id
500 ,p_event_type_code => 'DIRECT_LOAN_APPROVED'
501 ,p_event_date => l_gl_date
502 ,p_event_status => 'U'
503 ,p_init_msg_list => fnd_api.g_false
504 ,p_commit => fnd_api.g_false
505 ,p_bc_flag => 'Y'
506 ,x_event_id => x_event_id
507 ,x_return_status => x_return_status
508 ,x_msg_count => x_msg_count
509 ,x_msg_data => x_msg_data);
510 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'x_return_status = ' || x_return_status);
511 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'x_event_id ' || x_event_id);
512
513 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
514 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACCOUNTING_EVENT_ERROR');
515 FND_MSG_PUB.ADD;
516 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519
520 -- stamp the eventID onto the lns_distributions table
521 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'stamping eventID on lns_distributions');
522 update lns_distributions
523 set event_id = x_event_id
524 ,last_update_date = sysdate
525 where distribution_type = 'ORIGINATION'
526 and loan_id = p_loan_id;
527 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
528
529 end if; -- budget event already created
530
531 -- now process the event
532 if l_funds_reserved_flag <> 'Y' then
533 --and p_budgetary_control_mode = 'R' then
534 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'getting events');
535
536 open c_events(p_loan_id);
537 LOOP
538 fetch c_events into l_event_id;
539 EXIT WHEN c_events%NOTFOUND;
540
541 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
542 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserting into PSA_BC_XLA_EVENTS_GT ');
543 insert
544 into PSA_BC_XLA_EVENTS_GT (event_id, result_code)
545 values (l_event_id, 'FAIL');
546
547 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling PSA_BC_XLA_PUB.Budgetary_Control ' || l_event_id);
548 PSA_BC_XLA_PUB.Budgetary_Control(p_api_version => 1.0
549 ,p_init_msg_list => FND_API.G_FALSE
550 ,x_return_status => l_return_status
551 ,x_msg_count => l_msg_count
552 ,x_msg_data => l_msg_data
553 ,p_application_id => 206
554 ,p_bc_mode => p_budgetary_control_mode
555 ,p_override_flag => null
556 ,p_user_id => null
557 ,p_user_resp_id => null
558 ,x_status_code => l_status_code
559 ,x_packet_ID => l_packet_id);
560 end loop;
561 close c_events;
562
563 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'BC status is = ' || l_return_status);
564 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_status_code = ' || l_status_code);
565 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'l_packet_id = ' || l_packet_id);
566
567 -- we want to commit ONLY in the case of SUCCESS or ADVISORY
568 if (l_return_status <> 'S' ) then
569
570 l_return_status := FND_API.G_RET_STS_ERROR;
571 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
572 FND_MESSAGE.SET_TOKEN('ERROR' ,'Call to PSA_BC_XLA_PUB.Budgetary_Control failed with Status Code = ' || l_status_code);
573 FND_MSG_PUB.ADD;
574 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
575 RAISE FND_API.G_EXC_ERROR;
576
577 else
578 if l_status_code NOT IN ('SUCCESS','ADVISORY') then
579 IF (l_status_code = 'PARTIAL') THEN
580 FND_MESSAGE.SET_NAME('LNS', 'LNS_FUND_CHK_PARTIAL');
581 FND_MSG_PUB.ADD;
582 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
583 RAISE FND_API.G_EXC_ERROR;
584 ELSE
585 FND_MESSAGE.SET_NAME('LNS', 'LNS_API_OTHERS_EXCEP');
586 FND_MESSAGE.SET_TOKEN('ERROR' ,'Call to PSA_BC_XLA_PUB.Budgetary_Control failed with Status Code = ' || l_status_code);
587 FND_MSG_PUB.ADD;
588 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
589 RAISE FND_API.G_EXC_ERROR;
590 END IF;
591 end if;
592
593 open c_obj_vers(p_loan_id);
594 fetch c_obj_vers into l_version;
595 close c_obj_vers;
596
597 if (l_status_code = 'ADVISORY' or l_status_code = 'SUCCESS') and p_budgetary_control_mode = 'R' then
598 l_loan_header_rec.FUNDS_RESERVED_FLAG := 'Y';
599 end if;
600
601 l_loan_header_rec.loan_id := p_loan_id;
602 l_loan_header_rec.FUNDS_CHECK_DATE := sysdate;
603 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || 'updating loan');
604 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version
605 ,P_LOAN_HEADER_REC => l_loan_header_rec
606 ,P_INIT_MSG_LIST => FND_API.G_FALSE
607 ,X_RETURN_STATUS => l_return_status
608 ,X_MSG_COUNT => l_msg_count
609 ,X_MSG_DATA => l_msg_data);
610 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'update loan status = ' || l_return_status);
611
612 if l_return_status <> 'S' then
613 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
614 FND_MSG_PUB.ADD;
615 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
616 RAISE FND_API.G_EXC_ERROR;
617 end if;
618
619 end if; -- BC_API.RETURN_STATUS
620
621 end if; -- l_funds_reserved_flag
622 x_budgetary_status_code := l_status_code;
623 x_return_status := l_return_status;
624
625 IF (l_return_status = 'S' AND FND_API.to_Boolean(p_commit))
626 THEN
627 COMMIT WORK;
628 END IF;
629
630 end if; -- no budgetary control-- end if (lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' AND l_disbursement_id IS NOT NULL) then
631
632 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
633
634 EXCEPTION
635
636 WHEN FND_API.G_EXC_ERROR THEN
637 ROLLBACK TO budgetary_control_pvt;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
640 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
641
642 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
643 ROLLBACK TO budgetary_control_pvt;
644 x_return_status := FND_API.G_RET_STS_ERROR;
645 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
646 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
647
648 WHEN OTHERS THEN
649 ROLLBACK TO budgetary_control_pvt;
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
652 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
653
654 end budgetary_control;
655
656
657 /*=========================================================================
658 || PRIVATE PROCEDURE do_insert_distributions
659 ||
660 || DESCRIPTION
661 || this procedure insert records into lns_distributions table
662 ||
663 || PARAMETERS p_distributions_tbl => table -f distribution records
664 ||
665 || Return value: NA
666 ||
667 || Source Tables:
668 ||
669 || Target Tables: LNS_DISTRIBUTIONS
670 ||
671 || KNOWN ISSUES
672 ||
673 || NOTES
674 ||
675 || MODIFICATION HISTORY
676 || Date Author Description of Changes
677 || 04-20-2005 raverma Created
678 *=======================================================================*/
679 procedure do_insert_distributions(p_distributions_tbl in lns_distributions_pub.distribution_tbl
680 ,p_loan_id in number)
681
682 is
683 l_total_distributions number;
684 l_api_name varchar2(25);
685 begin
686 l_api_name := 'do_insert_distributions';
687
688 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
689 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'rows received = ' || p_distributions_tbl.count);
690
691 l_total_distributions := p_distributions_tbl.count;
692
693 if l_total_distributions > 0 then
694
695 for k in 1..l_total_distributions
696 loop
697 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Inserting row : ' || k);
698 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LINE_TYPE = ' || p_distributions_tbl(k).line_type);
699 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ACC_NAME = ' || p_distributions_tbl(k).account_name);
700 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CC_ID = ' || p_distributions_tbl(k).code_combination_id);
701 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ACC_TYPE = ' || p_distributions_tbl(k).account_type);
702 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'PERCENT = ' || p_distributions_tbl(k).distribution_percent);
703 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'AMOUNT = ' || p_distributions_tbl(k).distribution_amount);
704 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_TYPE = ' || p_distributions_tbl(k).distribution_type);
705
706 Insert into lns_distributions
707 (DISTRIBUTION_ID
708 ,LOAN_ID
709 ,LINE_TYPE
710 ,ACCOUNT_NAME
711 ,CODE_COMBINATION_ID
712 ,ACCOUNT_TYPE
713 ,DISTRIBUTION_PERCENT
714 ,DISTRIBUTION_AMOUNT
715 ,DISTRIBUTION_TYPE
716 ,EVENT_ID
717 ,CREATION_DATE
718 ,CREATED_BY
719 ,LAST_UPDATE_DATE
720 ,LAST_UPDATED_BY
721 ,OBJECT_VERSION_NUMBER
722 ,DISB_HEADER_ID)
723 values
724 (LNS_DISTRIBUTIONS_S.nextval
725 ,p_loan_id
726 ,p_distributions_tbl(k).line_type
727 ,p_distributions_tbl(k).account_name
728 ,p_distributions_tbl(k).code_combination_id
729 ,p_distributions_tbl(k).account_type
730 ,p_distributions_tbl(k).distribution_percent
731 ,p_distributions_tbl(k).distribution_amount
732 ,p_distributions_tbl(k).distribution_type
733 ,p_distributions_tbl(k).event_id
734 ,lns_utility_pub.creation_date
735 ,lns_utility_pub.created_by
736 ,lns_utility_pub.last_update_date
737 ,lns_utility_pub.last_updated_by
738 ,1
739 ,p_distributions_tbl(k).disb_header_id);
740
741 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, '****************************************** ');
742 end loop;
743
744 else
745
746 FND_MESSAGE.SET_NAME('LNS', 'LNS_DEFAULT_DIST_NOT_FOUND');
747 FND_MSG_PUB.ADD;
748 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
749 RAISE FND_API.G_EXC_ERROR;
750
751 end if;
752 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
753
754 end do_insert_distributions;
755
756 /*=========================================================================
757 || PRIVATE PROCEDURE defaultDistributionsCatch
758 ||
759 || DESCRIPTION
760 || ths procedure is the "catchAll" logic for accounting set-up
761 || it will ensure that a valid distributions_tbl is built for
762 || INTEREST_INCOME, INTEREST_RECEIVABLE, and PRINCIPAL_RECEIVABLE
763 ||
764 || as well if the parameter p_include_loan_receivables = 'Y' then
765 || it will also pull
766 || LOAN_RECEIVABLE and LOAN_CLEARING
767 ||
768 || PSEUDO CODE/LOGIC
769 ||
770 || PARAMETERS p_include_loan_receivables 'Y' to include loan_receivable/clearing
771 || 'X' to exclude loan_clearing
772 ||
773 || Return value: x_distribution_tbl distribution table set to write to database
774 ||
775 || Source Tables: lns_default_distribs
776 ||
777 || Target Tables: NA
778 ||
779 || KNOWN ISSUES
780 ||
781 || NOTES
782 ||
783 ||
784 || MODIFICATION HISTORY
785 || Date Author Description of Changes
786 || 02-18-2004 raverma Created
787 *=======================================================================*/
788 procedure defaultDistributionsCatch(p_api_version IN NUMBER
789 ,p_init_msg_list IN VARCHAR2
790 ,p_commit IN VARCHAR2
791 ,p_loan_id IN NUMBER
792 ,p_disb_header_id IN NUMBER
793 ,p_include_loan_receivables IN VARCHAR2
794 ,p_distribution_type IN VARCHAR2
795 ,x_distribution_tbl OUT NOCOPY lns_distributions_pub.distribution_tbl
796 ,x_return_status OUT NOCOPY VARCHAR2
797 ,x_msg_count OUT NOCOPY NUMBER
798 ,x_msg_data OUT NOCOPY VARCHAR2)
799
800 is
801 /*------------------------------------------------------------------------+
802 | Local Variable Declarations and initializations |
803 +-----------------------------------------------------------------------*/
804 l_api_name varchar2(50);
805 l_msg_count NUMBER;
806 l_msg_data VARCHAR2(2000);
807 l_return_Status VARCHAR2(1);
808 l_class varchar2(30);
809 l_loan_type_id number;
810 i number := 0;
811 l_line_type varchar2(30);
812 l_account_name varchar2(30);
813 l_code_combination_id number;
814 l_account_type varchar2(30);
815 l_distribution_percent number;
816 l_distribution_type varchar2(30);
817 l_funded_amount number;
818 l_loan_receivables_count number;
819 l_loan_payables_count number;
820 l_distributions lns_distributions_pub.distribution_tbl;
821 l_running_amount1 number;
822 l_running_amount2 number;
823 l_running_amount3 number;
824 l_running_amount4 number;
825 k number;
826 n number;
827 l number;
828 m number;
829 l_ledger_details lns_distributions_pub.gl_ledger_details;
830 Type refCur is ref cursor;
831 sql_Cur refCur;
832 vSqlCur varchar2(1000);
833 vPLSQL VARCHAR2(1000);
834
835 /*------------------------------------------------------------------------+
836 | Cursor Declarations |
837 +-----------------------------------------------------------------------*/
838 -- R12 for loan_types
839 cursor c_loan_info(p_loan_id NUMBER)
840 is
841 select h.loan_class_code
842 ,t.loan_type_id
843 ,h.funded_amount
844 from lns_loan_headers_all h
845 ,lns_loan_types t
846 where h.loan_id = p_loan_id
847 and h.loan_type_id = t.loan_type_id;
848
849 cursor c_loan_info2(p_loan_id NUMBER, p_disb_header_id number)
850 is
851 select h.loan_class_code
852 ,t.loan_type_id
853 ,d.header_amount
854 from lns_loan_headers_all h
855 ,lns_loan_types t
856 ,lns_disb_headers d
857 where h.loan_id = p_loan_id
858 and h.loan_type_id = t.loan_type_id
859 and h.loan_id = d.loan_id
860 and d.disb_header_id = p_disb_header_id;
861
862 cursor c_num_receivables(p_loan_class varchar2, p_loan_type_id number)
863 is
864 select count(1)
865 from lns_default_distribs
866 where loan_class = p_loan_class
867 AND loan_type_id = p_loan_type_id
868 and account_name = 'LOAN_RECEIVABLE'
869 and distribution_type = 'ORIGINATION'
870 and account_type = 'DR';
871
872 cursor c_num_payables(p_loan_class varchar2, p_loan_type_id number)
873 is
874 select count(1)
875 from lns_default_distribs
876 where loan_class = p_loan_class
877 AND loan_type_id = p_loan_type_id
878 and account_name = 'LOAN_PAYABLE'
879 and distribution_type = 'FUNDING'
880 and account_type = 'DR';
881
882 begin
883 SAVEPOINT defaultDistributionsCatch;
884 l_api_name := 'defaultDistributionsCatch';
885 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
886
887 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
888 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
889 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_include_loan_receivables = ' || p_include_loan_receivables);
890 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_distribution_type = ' || p_distribution_type);
891
892 -- Initialize message list IF p_init_msg_list is set to TRUE.
893 IF FND_API.to_Boolean( p_init_msg_list ) THEN
894 FND_MSG_PUB.initialize;
895 END IF;
896
897 -- Initialize API return status to SUCCESS
898 x_return_status := FND_API.G_RET_STS_SUCCESS;
899
900 n := 0;
901 k := 0;
902 l := 0;
903 m := 0;
904 l_running_amount1 := 0;
905 l_running_amount2 := 0;
906 l_running_amount3 := 0;
907 l_running_amount4 := 0;
908 l_ledger_details := lns_distributions_pub.getLedgerDetails;
909
910 -- get class and type for the loan
911 if p_disb_header_id is null then
912 OPEN c_loan_info(p_loan_id);
913 FETCH c_loan_info INTO l_class, l_loan_type_id, l_funded_amount;
914 close c_loan_info;
915 else
916 OPEN c_loan_info2(p_loan_id, p_disb_header_id);
917 FETCH c_loan_info2 INTO l_class, l_loan_type_id, l_funded_amount;
918 close c_loan_info2;
919 end if;
920 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_class = ' || l_class);
921 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_type_id = ' || l_loan_type_id);
922 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_funded_amount = ' || l_funded_amount);
923
924 open c_num_receivables(l_class, l_loan_type_id);
925 fetch c_num_receivables into l_loan_receivables_count;
926 close c_num_receivables;
927 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_receivables_count = ' || l_loan_receivables_count);
928
929 open c_num_payables(l_class, l_loan_type_id);
930 fetch c_num_payables into l_loan_payables_count;
931 close c_num_payables;
932
933 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_payables_count = ' || l_loan_payables_count);
934
935 -- now see if any default distributions exist for this loan
936 -- 2-24-2005 raverma -- add loan_payable IF loan_class = DIRECT OR
937 -- loan is not MFAR
938 -- Bug#5295575 Modified the query to work for an organization instead
939 -- of working for all organzations by replacing lns_default_distribs_all
940 -- with lns_default_distribs table
941
942
943 Begin
944 vPLSQL := 'SELECT d.line_type ' ||
945 ' ,d.account_name ' ||
946 ' ,d.code_combination_id ' ||
947 ' ,d.account_type ' ||
948 ' ,d.distribution_percent ' ||
949 ' ,d.distribution_type ' ||
950 'FROM lns_default_distribs d ' ||
951 'WHERE ((d.loan_class = :p_loan_class_code AND d.loan_type_id = :p_loan_type_id) ) ' ||
952 ' AND account_name IN (''PRINCIPAL_RECEIVABLE'', ''INTEREST_RECEIVABLE'', ''INTEREST_INCOME'' ';
953
954 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'include receivabvles ' || p_include_loan_receivables);
955 -- these are only appropriate for class=DIRECT or loan <> MFAR
956 if p_include_loan_receivables = 'Y' then
957 vPLSQL := vPLSQL || ' ,''LOAN_RECEIVABLE'', ''LOAN_CLEARING'', ''LOAN_PAYABLE'')';
958 else
959 vPLSQL := vPLSQL || ' )';
960 end if;
961
962 if p_distribution_type is not null then
963 vPLSQL := vPLSQL || ' AND d.distribution_type = ' || '''' || p_distribution_type || '''';
964 end if;
965
966 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'sql: ' || vPLSQL);
967
968 open sql_cur for
969 vPLSQL
970 using l_class, l_loan_type_id;
971 LOOP
972 fetch sql_cur into l_line_type
973 ,l_account_name
974 ,l_code_combination_id
975 ,l_account_type
976 ,l_distribution_percent
977 ,l_distribution_type;
978 exit when sql_cur%NOTFOUND;
979
980 i := i + 1;
981
982 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Record ' || i);
983 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_line_type = ' || l_line_type);
984 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_account_name = ' || l_account_name);
985 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_code_combination_id = ' || l_code_combination_id);
986 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_account_type = ' || l_account_type);
987 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_percent = ' || l_distribution_percent);
988 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_type = ' || l_distribution_type);
989
990 l_distributions(i).line_type := l_line_type;
991 l_distributions(i).account_name := l_account_name;
992 l_distributions(i).code_combination_id := l_code_combination_id;
993 l_distributions(i).account_type := l_account_type;
994 l_distributions(i).distribution_percent := l_distribution_percent;
995 l_distributions(i).distribution_type := l_distribution_type;
996
997 if l_account_name = 'LOAN_RECEIVABLE' and l_distribution_type = 'ORIGINATION' then
998
999 k := k + 1;
1000 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan receivables line ' || k);
1001
1002 if k <> l_loan_receivables_count then
1003 l_distributions(i).distribution_amount := round(l_distribution_percent * l_funded_amount, l_ledger_details.currency_precision) / 100;
1004 l_running_amount1 := l_running_amount1 + l_distributions(i).distribution_amount;
1005 else
1006 l_distributions(i).distribution_amount := l_funded_amount - l_running_amount1;
1007 end if;
1008
1009 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distributions(i).distribution_amount = ' || l_distributions(i).distribution_amount);
1010 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_running_amount1 = ' || l_running_amount1);
1011
1012 end if;
1013
1014 if (l_account_name = 'LOAN_CLEARING' or l_account_name = 'LOAN_PAYABLE') and l_distribution_type = 'ORIGINATION' then
1015
1016 n := n + 1;
1017 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan clearing line ' || n);
1018
1019 if n <> l_loan_receivables_count then
1020 l_distributions(i).distribution_amount := round(l_distribution_percent * l_funded_amount, l_ledger_details.currency_precision) / 100;
1021 l_running_amount2 := l_running_amount2 + l_distributions(i).distribution_amount;
1022 else
1023 l_distributions(i).distribution_amount := l_funded_amount - l_running_amount2;
1024 end if;
1025
1026 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distributions(i).distribution_amount = ' || l_distributions(i).distribution_amount);
1027 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_running_amount2 = ' || l_running_amount2);
1028
1029 end if;
1030
1031 end loop;
1032 close sql_cur;
1033
1034 exception
1035 when no_data_found then
1036 FND_MESSAGE.SET_NAME('LNS', 'LNS_DEFAULT_DIST_NOT_FOUND');
1037 FND_MSG_PUB.ADD;
1038 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1039 RAISE FND_API.G_EXC_ERROR;
1040 End; -- c_default_info cursor
1041
1042 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribs2 count is ' || l_distributions.count);
1043 x_distribution_tbl := l_distributions;
1044
1045 IF FND_API.to_Boolean(p_commit)
1046 THEN
1047 COMMIT WORK;
1048 END IF;
1049
1050 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1051
1052 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1053
1054 EXCEPTION
1055 WHEN FND_API.G_EXC_ERROR THEN
1056 ROLLBACK TO defaultDistributionsCatch;
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1059 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1060
1061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1062 ROLLBACK TO defaultDistributionsCatch;
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1065 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1066
1067 WHEN OTHERS THEN
1068 ROLLBACK TO defaultDistributionsCatch;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1071 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1072
1073 end defaultDistributionsCatch;
1074
1075
1076
1077 /*=========================================================================
1078 || PUBLIC PROCEDURE create_event
1079 ||
1080 || DESCRIPTION
1081 || Overview: will write to xla_events table and update lns_distributions
1082 || this can handle a set of accounting event records
1083 ||
1084 || PSEUDO CODE/LOGIC
1085 ||
1086 || PARAMETERS
1087 || Parameter: p_acc_event_tbl => table of accounting records
1088 || ,p_event_type_code => seeded code for loans "APPROVED" "IN_FUNDING"
1089 || ,p_event_date => most likely GL_DATE
1090 || ,p_event_status => event Status
1091 || CONSTANT = 'U'; -- event status:unprocessed
1092 || CONSTANT = 'I'; -- event status:incomplete
1093 || CONSTANT = 'N'; -- event status:noaction
1094 ||
1095 || Return value:
1096 || standard
1097 || KNOWN ISSUES
1098 ||
1099 || NOTES
1100 ||
1101 || MODIFICATION HISTORY
1102 || Date Author Description of Changes
1103 || 8/3/2005 raverma Created
1104 ||
1105 *=======================================================================*/
1106 procedure create_event(p_acc_event_tbl in LNS_DISTRIBUTIONS_PUB.acc_event_tbl
1107 ,p_init_msg_list in varchar2
1108 ,p_commit in varchar2
1109 ,x_return_status out nocopy varchar2
1110 ,x_msg_count out nocopy number
1111 ,x_msg_data out nocopy varchar2)
1112
1113 is
1114 l_api_name varchar2(25);
1115 l_loan_class varchar2(30);
1116 l_loan_type_id number;
1117 l_distributions LNS_DISTRIBUTIONS_PUB.distribution_tbl;
1118 l_msg_count NUMBER;
1119 l_msg_data VARCHAR2(2000);
1120 l_return_Status VARCHAR2(1);
1121 l_event_id number;
1122
1123 cursor c_loan_info(p_loan_id number) is
1124 select h.loan_class_code
1125 ,h.loan_type_id
1126 from lns_loan_headers_all h
1127 where h.loan_id = p_loan_id;
1128
1129 begin
1130 l_api_name := 'create_event';
1131 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1132 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_acc_event_tbl count = ' || p_acc_event_tbl.count);
1133
1134 -- Standard Start of API savepoint
1135 SAVEPOINT create_event;
1136
1137 -- Initialize message list IF p_init_msg_list is set to TRUE.
1138 IF FND_API.to_Boolean(p_init_msg_list) THEN
1139 FND_MSG_PUB.initialize;
1140 END IF;
1141
1142 -- Initialize API return status to SUCCESS
1143 x_return_status := FND_API.G_RET_STS_SUCCESS;
1144
1145 -- ---------------------------------------------------------------------
1146 -- Api body
1147 -- ---------------------------------------------------------------------
1148 for k in 1..p_acc_event_tbl.count loop
1149
1150 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_id = ' || p_acc_event_tbl(k).loan_id);
1151 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_type_code = ' || p_acc_event_tbl(k).event_type_code);
1152 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_date = ' || p_acc_event_tbl(k).event_date);
1153 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_event_status = ' || p_acc_event_tbl(k).event_status);
1154 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_bc_flag = ' || p_acc_event_tbl(k).budgetary_control_flag);
1155
1156 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_XLA_EVENTS.create_event...');
1157 LNS_XLA_EVENTS.create_event(p_loan_id => p_acc_event_tbl(k).loan_id
1158 ,p_disb_header_id => p_acc_event_tbl(k).disb_header_id
1159 ,p_event_type_code => p_acc_event_tbl(k).event_type_code
1160 ,p_event_date => p_acc_event_tbl(k).event_date
1161 ,p_event_status => p_acc_event_tbl(k).event_status
1162 ,p_bc_flag => p_acc_event_tbl(k).budgetary_control_flag
1163 ,p_init_msg_list => p_init_msg_list
1164 ,p_commit => p_commit
1165 ,x_event_id => l_event_id
1166 ,x_return_status => x_return_status
1167 ,x_msg_count => x_msg_count
1168 ,x_msg_data => x_msg_data);
1169 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
1170 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'x_return_status = ' || x_return_status);
1171
1172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1173 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACCOUNTING_EVENT_ERROR');
1174 FND_MSG_PUB.ADD;
1175 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177 ELSE
1178 -- update the distributions table with proper event_id for valid disb_header_id
1179 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
1180 update lns_distributions
1181 set event_id = l_event_id
1182 ,last_update_date = sysdate
1183 where disb_header_id = p_acc_event_tbl(k).disb_header_id
1184 and loan_id = p_acc_event_tbl(k).loan_id;
1185 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
1186
1187 end if;
1188
1189 end loop;
1190 -- ---------------------------------------------------------------------
1191 -- End of API body
1192 -- ---------------------------------------------------------------------
1193 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1194
1195 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1196
1197 EXCEPTION
1198 WHEN FND_API.G_EXC_ERROR THEN
1199 ROLLBACK TO create_event;
1200 x_return_status := FND_API.G_RET_STS_ERROR;
1201 x_msg_count := l_msg_count;
1202 x_msg_data := l_msg_data;
1203 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1204 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1205
1206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1207 ROLLBACK TO create_event;
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 x_msg_count := l_msg_count;
1210 x_msg_data := l_msg_data;
1211 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1212 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1213
1214 WHEN OTHERS THEN
1215 ROLLBACK TO create_event;
1216 x_return_status := FND_API.G_RET_STS_ERROR;
1217 x_msg_count := l_msg_count;
1218 x_msg_data := l_msg_data;
1219 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1220 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
1221
1222 end create_event;
1223
1224
1225
1226
1227 /*=========================================================================
1228 || PRIVATE function getNaturalSwapAccount
1229 ||
1230 || DESCRIPTION
1231 || this procedure will return the swap the segment from loans setup
1232 ||
1233 || PSEUDO CODE/LOGIC
1234 ||
1235 || PARAMETERS p_loan_id = loan_id
1236 ||
1237 || Return value: new swap segment value
1238 ||
1239 || Source Tables: lns_system_options
1240 ||
1241 || Target Tables: NA
1242 ||
1243 || KNOWN ISSUES
1244 ||
1245 || NOTES
1246 ||
1247 || MODIFICATION HISTORY
1248 || Date Author Description of Changes
1249 || 04-19-2005 raverma Created
1250 *=======================================================================*/
1251 function getNaturalSwapAccount(p_loan_id number) return varchar2
1252 is
1253
1254 -- this is cursor is for reading configuration for multi-fund swap natural account
1255 cursor c_mfar_nat_acct (p_loan_id number) is
1256 select MFAR_NATURAL_ACCOUNT_REC
1257 from lns_default_distribs_all d
1258 ,lns_loan_headers_all h
1259 where account_name = 'MFAR_FUND_ACCOUNT_CHANGE'
1260 and h.loan_id = p_loan_id
1261 and h.loan_class_code = d.loan_class
1262 and h.loan_type_id = d.loan_type_id
1263 and h.org_id = d.org_id;
1264
1265 l_segment_value varchar2(60);
1266
1267 begin
1268
1269 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Opening c_mfar_nat_acct...');
1270 open c_mfar_nat_acct(p_loan_id);
1271 fetch c_mfar_nat_acct into l_segment_value;
1272 close c_mfar_nat_acct;
1273 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
1274 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_segment_value = ' || l_segment_value);
1275
1276 return l_segment_value;
1277
1278 Exception
1279 When others then
1280 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'failed to retrieve replacement natural account');
1281 FND_MESSAGE.SET_NAME('LNS', 'LNS_MFAR_CONFIGURATION_ERROR');
1282 FND_MSG_PUB.ADD;
1283 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1284 RAISE FND_API.G_EXC_ERROR;
1285
1286 end getNaturalSwapAccount;
1287
1288 /*=========================================================================
1289 || PRIVATE PROCEDURE swap_code_combination
1290 ||
1291 || DESCRIPTION
1292 || this procedure will swap the segment at given segment number
1293 || with given value and create and return the new cc_id
1294 ||
1295 || PSEUDO CODE/LOGIC
1296 ||
1297 || PARAMETERS p_chart_of_accounts_id => chart of accounts id
1298 || p_original_cc_id => original cc_id
1299 || p_swap_segment_number => segment number to swap
1300 || p_swap_segment_value => segment value to swap with
1301 ||
1302 || Return value: new code_combination_id
1303 ||
1304 || Source Tables:
1305 ||
1306 || Target Tables: NA
1307 ||
1308 || KNOWN ISSUES
1309 ||
1310 || NOTES
1311 ||
1312 || MODIFICATION HISTORY
1313 || Date Author Description of Changes
1314 || 04-19-2005 raverma Created
1315 *=======================================================================*/
1316 function swap_code_combination(p_chart_of_accounts_id in number
1317 ,p_original_cc_id in number
1318 ,p_swap_segment_number in number
1319 ,p_swap_segment_value in varchar
1320 ) return number
1321
1322 is
1323 l_original_segments FND_FLEX_EXT.SEGMENTARRAY;
1324 l_new_segments FND_FLEX_EXT.SEGMENTARRAY;
1325 l_num_segments number;
1326 l_api_name varchar2(50);
1327 l_new_cc_id number;
1328
1329 begin
1330 l_api_name := 'swap_code_combination';
1331 l_new_cc_id := -1;
1332
1333 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1334 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_chart_of_accounts_id = ' || p_chart_of_accounts_id);
1335 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_original_cc_id = ' || p_original_cc_id);
1336 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_swap_segment_number = ' || p_swap_segment_number);
1337 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_swap_segment_value = ' || p_swap_segment_value);
1338
1339 -- build the original code combination segments into array
1340 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling FND_FLEX_EXT.GET_SEGMENTS...');
1341 IF (NOT FND_FLEX_EXT.GET_SEGMENTS('SQLGL'
1342 ,'GL#'
1343 ,p_chart_of_accounts_id
1344 ,p_original_cc_id
1345 ,l_num_segments
1346 ,l_original_segments))
1347 Then
1348 FND_MESSAGE.SET_NAME('LNS', 'LNS_ERR_BUILDING_SEGMENTS');
1349 FND_MSG_PUB.ADD;
1350 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1351 RAISE FND_API.G_EXC_ERROR;
1352 END IF;
1353
1354 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Original segments:');
1355 for n in 1..l_num_segments loop
1356 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'seg#' || n || ' = ' || l_original_segments(n));
1357 end loop;
1358
1359 -- get the replacement accounts from lns_default_distribs
1360 FOR n IN 1..l_num_segments LOOP
1361 IF (n = p_swap_segment_number) THEN
1362 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'found the account to swap = ' || l_original_segments(n));
1363 l_new_segments(n) := p_swap_segment_value;
1364 else
1365 l_new_segments(n) := l_original_segments(n);
1366 END IF;
1367 END LOOP;
1368
1369 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'New segments:');
1370 for n in 1..l_num_segments loop
1371 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'seg#' || n || ' = ' || l_new_segments(n));
1372 end loop;
1373
1374 -------------------------- Get new ccid -------------------------------
1375 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling FND_FLEX_EXT.GET_COMBINATION_ID...');
1376 iF (NOT FND_FLEX_EXT.GET_COMBINATION_ID(
1377 'SQLGL',
1378 'GL#',
1379 p_chart_of_accounts_id,
1380 SYSDATE,
1381 l_num_segments,
1382 l_new_segments,
1383 l_new_cc_id))
1384 Then
1385 FND_MESSAGE.SET_NAME('LNS', 'LNS_CODE_COMBINATION_ERROR');
1386 FND_MSG_PUB.ADD;
1387 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1388 RAISE FND_API.G_EXC_ERROR;
1389
1390 END IF;
1391 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'new cc_id = ' || l_new_cc_id);
1392 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1393
1394 return l_new_cc_id;
1395
1396 exception
1397 when others then
1398 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'failed to create new code combination');
1399 RAISE FND_API.G_EXC_ERROR;
1400 end swap_code_combination;
1401
1402
1403 /*=========================================================================
1404 || PRIVATE PROCEDURE transformDistribution
1405 ||
1406 || DESCRIPTION
1407 || this function is currently not used nor finished
1408 || the idea was to include this function into the transaction_object as
1409 || part of the accounting sources within AAD
1410 ||
1411 || PSEUDO CODE/LOGIC
1412 ||
1413 || PARAMETERS
1414 ||
1415 || Return value: new code_combination_id
1416 ||
1417 || Source Tables:
1418 ||
1419 || Target Tables: NA
1420 ||
1421 || KNOWN ISSUES
1422 ||
1423 || NOTES
1424 ||
1425 || MODIFICATION HISTORY
1426 || Date Author Description of Changes
1427 || 06-19-2005 raverma Created
1428 *=======================================================================*/
1429 function transformDistribution(p_distribution_id number
1430 ,p_distribution_type varchar2
1431 ,p_loan_id number) return number
1432 is
1433
1434 l_new_cc_id number;
1435 l_ledger_details lns_distributions_pub.gl_ledger_details;
1436 l_natural_account_rec varchar2(25); -- the lns_def_distribs replacement for Loans Receivable
1437 l_nat_acct_seg_number number;
1438 l_api_name varchar2(25);
1439
1440 -- gets the swap natural account value for LOAN_RECEIVABLE
1441 cursor c_mfar_nat_acct (p_loan_id number) is
1442 select MFAR_NATURAL_ACCOUNT_REC
1443 from lns_default_distribs d
1444 ,lns_loan_headers h
1445 where account_name = 'MFAR_FUND_ACCOUNT_CHANGE'
1446 and h.loan_id = p_loan_id
1447 and h.loan_class_code = d.loan_class
1448 and h.loan_type_id = d.loan_type_id;
1449
1450 begin
1451
1452 l_ledger_details := lns_distributions_pub.getLedgerDetails;
1453 -- given a code_combination
1454 if p_distribution_type = 'LOAN_RECEIVABLE' then
1455
1456 -- build new cc_id
1457 Begin
1458 -- swap account is established from set-up
1459 open c_mfar_nat_acct(p_loan_id);
1460 fetch c_mfar_nat_acct into l_natural_account_rec;
1461 close c_mfar_nat_acct;
1462
1463 Exception
1464 When others then
1465 FND_MESSAGE.SET_NAME('LNS', 'LNS_MFAR_CONFIGURATION_ERROR');
1466 FND_MSG_PUB.ADD;
1467 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1468 RAISE FND_API.G_EXC_ERROR;
1469 End;
1470 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'swap natural account with ' || l_natural_account_rec);
1471 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'COA ' || l_ledger_details.chart_of_accounts_id);
1472
1473 -- Get natural account segment number to swap
1474 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling FND_FLEX_APIS.GET_QUALIFIER_SEGNUM...');
1475 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(appl_id => 101
1476 ,key_flex_code => 'GL#'
1477 ,structure_number=> l_ledger_details.chart_of_accounts_id
1478 ,flex_qual_name => 'GL_ACCOUNT'
1479 ,segment_number => l_nat_acct_seg_number))
1480 THEN
1481 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_NATURAL_ACCOUNT_SEGMENT');
1482 FND_MSG_PUB.ADD;
1483 logMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1484 RAISE FND_API.G_EXC_ERROR;
1485
1486 END IF;
1487 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'natural acct segment is ' || l_nat_acct_seg_number);
1488
1489 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling swap_code_combination...');
1490 l_new_cc_id := swap_code_combination(p_chart_of_accounts_id => l_ledger_details.chart_of_accounts_id
1491 ,p_original_cc_id => p_distribution_id
1492 ,p_swap_segment_number => l_nat_acct_seg_number
1493 ,p_swap_segment_value => l_natural_account_rec);
1494
1495 elsif p_distribution_type = 'LOAN_CLEARING' then
1496 -- get adjustment cc_id
1497 l_new_cc_id := 123;
1498 end if;
1499
1500 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_new_cc_id = ' || l_new_cc_id);
1501 return l_new_cc_id;
1502
1503 end transformDistribution;
1504
1505
1506 /*=========================================================================
1507 || function getDefaultDistributions
1508 ||
1509 || DESCRIPTION
1510 || This function returns default distribution entities
1511 ||
1512 || PSEUDO CODE/LOGIC
1513 ||
1514 || PARAMETERS
1515 || p_loan_class = loan class
1516 || p_loan_type = loan_type
1517 || p_account_type = 'CR' or 'DR'
1518 || p_account_name = 'LOAN_RECEIVABLE' 'LOAN_CLEARING', 'PRINCIPAL_RECIEVABLE',
1519 || 'INTEREST_RECEIVABLE', 'INTEREST_INCOME'
1520 || 'FEE_RECEIVABLE', 'FEE_INCOME'
1521 ||
1522 || p_line_type = 'ORIG', 'PRIN', 'INT', 'CLEAR' , 'FEE'
1523 || p_distribution_type = 'ORIGINATION' , 'BILLING', 'FUNDING'
1524 ||
1525 || Return value: table of distribution entities
1526 ||
1527 || Source Tables: lns_distributions
1528 ||
1529 || Target Tables:
1530 ||
1531 || KNOWN ISSUES
1532 ||
1533 || NOTES
1534 ||
1535 ||
1536 || MODIFICATION HISTORY
1537 || Date Author Description of Changes
1538 || 06-28-2004 raverma Created
1539 ||
1540 *=======================================================================*/
1541 function getDefaultDistributions(p_loan_class in varchar2
1542 ,p_loan_type_id in number
1543 ,p_account_type in varchar2
1544 ,p_account_name in varchar2
1545 ,p_line_type in varchar2
1546 ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.default_distributions_tbl
1547 is
1548 x_distribution_tbl LNS_DISTRIBUTIONS_PUB.default_distributions_tbl;
1549 l_index number := 1;
1550 l_loan_id number;
1551 l_loan_class varchar2(30);
1552 l_loan_type_id number;
1553 l_line_type varchar2(30);
1554 l_account_name varchar2(30);
1555 l_code_combination_id number;
1556 l_distribution_percent number;
1557 l_distribution_type varchar2(30);
1558 l_fee_id number;
1559 l_org_id number;
1560 l_mfar_natural_account_rec varchar2(60);
1561
1562 cursor c_get_distribution(p_loan_class varchar2
1563 ,p_loan_type_id number
1564 ,p_acct_type varchar2
1565 ,p_acct_name varchar2
1566 ,p_line_type varchar2
1567 ,p_distribution_type varchar2) is
1568 select loan_class
1569 ,loan_type_id
1570 ,line_type
1571 ,account_name
1572 ,code_combination_id
1573 ,distribution_percent
1574 ,distribution_type
1575 ,FEE_ID
1576 ,ORG_ID
1577 ,MFAR_NATURAL_ACCOUNT_REC
1578 from lns_default_distribs
1579 where loan_class = p_loan_class
1580 and loan_type_id = p_loan_type_id
1581 and account_type = p_acct_type
1582 and account_name = p_acct_name
1583 and line_type = p_line_type
1584 and distribution_type = p_distribution_type
1585 and distribution_percent > 0
1586 order by code_combination_id;
1587
1588 l_api_name varchar2(30);
1589
1590 begin
1591
1592 l_api_name := 'getDefaultDistributions';
1593 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1594 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_class = ' || p_loan_class);
1595 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan typeID = ' || p_loan_type_id);
1596 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account type = ' || p_account_type);
1597 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account name = ' || p_account_name);
1598 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'line tpye = ' || p_line_type);
1599 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribution type = ' || p_distribution_type);
1600
1601 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'opening c_get_distribution...');
1602 OPEN c_get_distribution (p_loan_class
1603 ,p_loan_type_id
1604 ,p_account_type
1605 ,p_account_name
1606 ,p_line_type
1607 ,p_distribution_type);
1608 LOOP
1609 FETCH C_Get_Distribution into
1610 l_loan_class
1611 ,l_loan_type_id
1612 ,l_line_type
1613 ,l_account_name
1614 ,l_code_combination_id
1615 ,l_distribution_percent
1616 ,l_distribution_type
1617 ,l_fee_id
1618 ,l_org_id
1619 ,l_mfar_natural_account_rec;
1620 EXIT WHEN C_Get_Distribution%NOTFOUND;
1621
1622 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Record ' || l_index);
1623 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_class = ' || l_loan_class);
1624 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_type_id = ' || l_loan_type_id);
1625 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_line_type = ' || l_line_type);
1626 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_account_name = ' || l_account_name);
1627 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_code_combination_id = ' || l_code_combination_id);
1628 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_percent = ' || l_distribution_percent);
1629 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_type = ' || l_distribution_type);
1630 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_fee_id = ' || l_fee_id);
1631 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_org_id = ' || l_org_id);
1632 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_mfar_natural_account_rec = ' || l_mfar_natural_account_rec);
1633
1634 x_distribution_tbl(l_index).line_type := l_line_type;
1635 x_distribution_tbl(l_index).account_name := l_account_name;
1636 x_distribution_tbl(l_index).code_combination_id := l_code_combination_id;
1637 x_distribution_tbl(l_index).distribution_percent := l_distribution_percent;
1638 x_distribution_tbl(l_index).distribution_type := l_distribution_type;
1639 x_distribution_tbl(l_index).fee_id := l_fee_id;
1640 x_distribution_tbl(l_index).org_id := l_org_id;
1641 x_distribution_tbl(l_index).mfar_natural_account_rec := l_mfar_natural_account_rec;
1642 l_line_type := null;
1643 l_account_name := null;
1644 l_code_combination_id := null;
1645 l_distribution_percent := null;
1646 l_distribution_type := null;
1647 l_fee_id := null;
1648 l_org_id := null;
1649 l_mfar_natural_account_rec := null;
1650
1651 l_index := l_index + 1;
1652
1653 END LOOP;
1654
1655 CLOSE C_Get_Distribution;
1656
1657 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'found: ' || x_distribution_tbl.count || ' distributions');
1658 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1659 return x_distribution_tbl;
1660
1661 end getDefaultDistributions;
1662
1663 /*=========================================================================
1664 || function getDistributions
1665 ||
1666 || DESCRIPTION
1667 || This function returns distribution entities
1668 ||
1669 || PSEUDO CODE/LOGIC
1670 ||
1671 || PARAMETERS
1672 || p_loan_id = loan_id
1673 || p_account_type = 'CR' or 'DR'
1674 || p_account_name = 'LOAN_RECEIVABLE' 'LOAN_CLEARING', 'PRINCIPAL_RECIEVABLE',
1675 || 'INTEREST_RECEIVABLE', 'INTEREST_INCOME'
1676 || 'FEE_RECEIVABLE', 'FEE_INCOME'
1677 || p_line_type = 'ORIG', 'PRIN', 'INT', 'CLEAR' , 'FEE'
1678 || p_distribution_type = 'ORIGINATION' , 'BILLING' , ' FUNDING'
1679 ||
1680 || Return value: table of distribution entities
1681 ||
1682 || Source Tables: lns_distributions
1683 ||
1684 || Target Tables:
1685 ||
1686 || KNOWN ISSUES
1687 ||
1688 || NOTES
1689 ||
1690 ||
1691 || MODIFICATION HISTORY
1692 || Date Author Description of Changes
1693 || 04-26-2004 raverma Created
1694 ||
1695 *=======================================================================*/
1696 function getDistributions(p_loan_id in number
1697 ,p_account_type in varchar2
1698 ,p_account_name in varchar2
1699 ,p_line_type in varchar2
1700 ,p_distribution_type in varchar2) return LNS_DISTRIBUTIONS_PUB.distribution_tbl
1701
1702 is
1703
1704 l_api_name varchar2(30);
1705 x_distribution_tbl lns_distributions_pub.distribution_tbl;
1706 l_index number;
1707 l_loan_id number;
1708 l_distribution_id number;
1709 l_line_type varchar2(30);
1710 l_account_name varchar2(30);
1711 l_code_combination_id number;
1712 l_distribution_percent number;
1713 l_distribution_amount number;
1714 l_distribution_type varchar2(30);
1715 l_event_id number;
1716
1717 cursor c_get_distribution(x_loan_id number
1718 ,x_acct_type varchar2
1719 ,x_acct_name varchar2
1720 ,x_line_type varchar2
1721 ,x_distribution_type varchar2) is
1722 select d.distribution_id
1723 ,d.loan_id
1724 ,d.line_type
1725 ,d.account_name
1726 ,d.code_combination_id
1727 ,d.distribution_percent
1728 ,d.distribution_amount
1729 ,d.distribution_type
1730 ,d.event_id
1731 from lns_distributions d
1732 where d.loan_id = x_loan_id
1733 and d.account_type = x_acct_type
1734 and d.account_name = x_acct_name
1735 and d.line_type = x_line_type
1736 and d.distribution_type = x_distribution_type
1737 and d.distribution_percent > 0
1738 order by d.code_combination_id;
1739
1740 begin
1741
1742 l_api_name := 'getDistributions';
1743 l_index := 0;
1744
1745 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1746 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
1747 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_account_type = ' || p_account_type);
1748 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_account_name = ' || p_account_name);
1749 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_line_type = ' || p_line_type);
1750 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_distribution_type = ' || p_distribution_type);
1751 x_distribution_tbl.delete;
1752
1753 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'opening c_get_distribution...');
1754 OPEN c_get_distribution (p_loan_id
1755 ,p_account_type
1756 ,p_account_name
1757 ,p_line_type
1758 ,p_distribution_type);
1759 LOOP
1760 FETCH C_Get_Distribution into
1761 l_distribution_id
1762 ,l_loan_id
1763 ,l_line_type
1764 ,l_account_name
1765 ,l_code_combination_id
1766 ,l_distribution_percent
1767 ,l_distribution_amount
1768 ,l_distribution_type
1769 ,l_event_id;
1770 EXIT WHEN C_Get_Distribution%NOTFOUND;
1771 l_index := l_index + 1;
1772
1773 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Record ' || l_index);
1774 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_id = ' || l_distribution_id);
1775 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_id = ' || l_loan_id);
1776 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_line_type = ' || l_line_type);
1777 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_account_name = ' || l_account_name);
1778 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_code_combination_id = ' || l_code_combination_id);
1779 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_percent = ' || l_distribution_percent);
1780 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_amount = ' || l_distribution_amount);
1781 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_type = ' || l_distribution_type);
1782 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_event_id = ' || l_event_id);
1783
1784 x_distribution_tbl(l_index).distribution_id := l_distribution_id;
1785 x_distribution_tbl(l_index).loan_id := l_loan_id;
1786 x_distribution_tbl(l_index).line_type := l_line_type;
1787 x_distribution_tbl(l_index).account_name := l_account_name;
1788 x_distribution_tbl(l_index).code_combination_id := l_code_combination_id;
1789 x_distribution_tbl(l_index).distribution_percent := l_distribution_percent;
1790 x_distribution_tbl(l_index).distribution_amount := l_distribution_amount;
1791 x_distribution_tbl(l_index).distribution_type := l_distribution_type;
1792 x_distribution_tbl(l_index).event_id := l_event_id;
1793 l_distribution_id := null;
1794 l_loan_id := null;
1795 l_line_type := null;
1796 l_account_name := null;
1797 l_code_combination_id := null;
1798 l_distribution_percent := null;
1799 l_distribution_amount := null;
1800 l_distribution_type := null;
1801 l_event_id := null;
1802
1803 END LOOP;
1804
1805 CLOSE C_Get_Distribution;
1806
1807 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'found: ' || x_distribution_tbl.count || ' distributions');
1808 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1809 return x_distribution_tbl;
1810
1811 end getDistributions;
1812
1813 function getDistributions(p_distribution_id in number) return LNS_DISTRIBUTIONS_PUB.distribution_rec
1814
1815 is
1816
1817 x_distribution_rec lns_distributions_pub.distribution_rec;
1818 l_api_name varchar2(30);
1819
1820 cursor c_get_distribution(x_distribution_id number) is
1821 select distribution_id
1822 ,loan_id
1823 ,line_type
1824 ,account_name
1825 ,code_combination_id
1826 ,distribution_percent
1827 ,distribution_amount
1828 ,distribution_type
1829 ,account_type
1830 ,account_name
1831 ,event_id
1832 from lns_distributions
1833 where distribution_id = x_distribution_id
1834 and distribution_percent > 0;
1835
1836 begin
1837 l_api_name := 'getDistributions';
1838
1839 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
1840 OPEN c_get_distribution (p_distribution_id);
1841 FETCH C_Get_Distribution into
1842 x_distribution_rec.distribution_id
1843 ,x_distribution_rec.loan_id
1844 ,x_distribution_rec.line_type
1845 ,x_distribution_rec.account_name
1846 ,x_distribution_rec.code_combination_id
1847 ,x_distribution_rec.distribution_percent
1848 ,x_distribution_rec.distribution_amount
1849 ,x_distribution_rec.distribution_type
1850 ,x_distribution_rec.account_type
1851 ,x_distribution_rec.account_name
1852 ,x_distribution_rec.event_id;
1853 CLOSE C_Get_Distribution;
1854
1855 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribution_id = ' || x_distribution_rec.distribution_id);
1856 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_id = ' || x_distribution_rec.loan_id);
1857 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'line_type = ' || x_distribution_rec.line_type);
1858 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account_name = ' || x_distribution_rec.account_name);
1859 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'code_combination_id = ' || x_distribution_rec.code_combination_id);
1860 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribution_percent = ' || x_distribution_rec.distribution_percent);
1861 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribution_amount = ' || x_distribution_rec.distribution_amount);
1862 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'distribution_type = ' || x_distribution_rec.distribution_type);
1863 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account_type = ' || x_distribution_rec.account_type);
1864 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account_name = ' || x_distribution_rec.account_name);
1865 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'event_id = ' || x_distribution_rec.event_id);
1866
1867 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
1868 return x_distribution_rec;
1869
1870 end getDistributions;
1871
1872 /*=========================================================================
1873 || PUBLIC PROCEDURE getLedgerDetails
1874 ||
1875 || DESCRIPTION
1876 || This procedure gets details about the General Ledger
1877 || THIS FUNCTION IS THE MAIN INTERFACE INTO GENERAL LEDGER
1878 ||
1879 ||
1880 || PSEUDO CODE/LOGIC
1881 ||
1882 || PARAMETERS
1883 ||
1884 || Return value:
1885 || type gl_ledger_details is record(SET_OF_BOOKS_ID NUMBER(15)
1886 || ,NAME VARCHAR2(30)
1887 || ,SHORT_NAME VARCHAR2(20)
1888 || ,CHART_OF_ACCOUNTS_ID NUMBER(15)
1889 || ,PERIOD_SET_NAME VARCHAR2(15));
1890 ||
1891 || Source Tables: LNS_SYSTEM_OPTIONS, GL_SETS_OF_BOOKS
1892 ||
1893 || Target Tables: NA
1894 ||
1895 || KNOWN ISSUES
1896 ||
1897 || NOTES
1898 ||
1899 ||
1900 || MODIFICATION HISTORY
1901 || Date Author Description of Changes
1902 || 02-18-2004 raverma Created
1903 ||
1904 *=======================================================================*/
1905 function getLedgerDetails return lns_distributions_pub.gl_ledger_details
1906 is
1907 cursor c_ledger
1908 is
1909 SELECT so.set_of_books_id
1910 ,sb.name
1911 ,sb.short_name
1912 ,sb.chart_of_accounts_id
1913 ,sb.period_set_name
1914 ,sb.currency_code
1915 ,fndc.precision
1916 FROM lns_system_options so,
1917 gl_ledgers sb,
1918 fnd_currencies fndc
1919 WHERE sb.ledger_id = so.set_of_books_id
1920 and sb.currency_code = fndc.currency_code;
1921
1922 l_ledger_details lns_distributions_pub.gl_ledger_details;
1923
1924 begin
1925
1926 begin
1927 open c_ledger;
1928 fetch c_ledger into l_ledger_details.set_of_books_id
1929 ,l_ledger_details.name
1930 ,l_ledger_details.short_name
1931 ,l_ledger_details.chart_of_accounts_id
1932 ,l_ledger_details.period_set_name
1933 ,l_ledger_details.currency_code
1934 ,l_ledger_details.currency_precision;
1935 close c_ledger;
1936
1937 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Ledger details:');
1938 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'set_of_books_id = ' || l_ledger_details.set_of_books_id);
1939 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'name = ' || l_ledger_details.name);
1940 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'short_name = ' || l_ledger_details.short_name);
1941 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'chart_of_accounts_id = ' || l_ledger_details.chart_of_accounts_id);
1942 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'period_set_name = ' || l_ledger_details.period_set_name);
1943 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'currency_code = ' || l_ledger_details.currency_code);
1944 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'currency_precision = ' || l_ledger_details.currency_precision);
1945
1946 return l_ledger_details;
1947
1948 exception
1949 when others then
1950 FND_MESSAGE.SET_NAME('LNS', 'LNS_LEDGER_DETAILS_FAIL');
1951 FND_MSG_PUB.ADD;
1952 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
1953 RAISE FND_API.G_EXC_ERROR;
1954 end;
1955
1956 end getLedgerDetails;
1957
1958 /*=========================================================================
1959 || PUBLIC FUNCTION calculateDistributionAmount
1960 ||
1961 || DESCRIPTION
1962 || calculatest the distribution amount based on the distribution percentage
1963 || this api assumes the defaultDistributions API has been called to
1964 || store the distributions on LNS_DISTRIBUTIONS
1965 ||
1966 || PSEUDO CODE/LOGIC
1967 ||
1968 || PARAMETERS
1969 || p_distribution_id = pk to LNS_DISTRIBUTIONS
1970 ||
1971 || Return value: amount of distribution based from loan funded amount
1972 ||
1973 || Source Tables: lns_distributions, lns_loan_headers
1974 ||
1975 || Target Tables: NA
1976 ||
1977 || KNOWN ISSUES
1978 ||
1979 || NOTES
1980 ||
1981 ||
1982 || MODIFICATION HISTORY
1983 || Date Author Description of Changes
1984 || 06/15/04 2:48:PM raverma Created
1985 || 09/07/04 raverma enhance to know about all distributions on loan
1986 || bug #3736979
1987 *=======================================================================*/
1988 function calculateDistributionAmount(p_distribution_id in number) return number
1989
1990 is
1991 l_api_name varchar2(50);
1992 l_distribution_amount number;
1993 l_ledger_details lns_distributions_pub.gl_ledger_details;
1994 l_distribution_rec lns_distributions_pub.distribution_rec;
1995 l_distribution_tbl lns_distributions_pub.distribution_tbl;
1996 l_loan_id number;
1997 l_max_distribution_id number;
1998
1999 cursor c_get_distribution(p_distribution_id number) is
2000 select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
2001 from lns_distributions lnd
2002 ,lns_loan_headers lnh
2003 ,fnd_currencies curr
2004 where lnh.loan_id = lnd.loan_id
2005 and curr.currency_code = lnh.loan_currency
2006 and lnd.distribution_id = p_distribution_id;
2007
2008 -- cursor to find if this is the last distribution on the loan
2009 cursor c_max_dist(p_loan_id number
2010 ,p_distribution_type varchar
2011 ,p_account_type varchar2) is
2012 select max(distribution_id)
2013 from lns_distributions lnd
2014 ,lns_loan_headers lnh
2015 where lnh.loan_id = lnd.loan_id
2016 and lnd.distribution_type = p_distribution_type
2017 and lnd.account_type = p_account_type
2018 and lnh.loan_id = p_loan_id;
2019
2020 cursor c_last_distribution(p_loan_id number
2021 ,p_distribution_id number
2022 ,p_distribution_type varchar
2023 ,p_account_type varchar2) is
2024 select
2025 lnh.funded_amount -
2026 (round(lnh.funded_amount *
2027 (select sum(distribution_percent) / 100
2028 from lns_distributions
2029 where distribution_id <> p_distribution_id
2030 and distribution_type = p_distribution_type
2031 and account_type = p_account_type
2032 and loan_id = p_loan_id), curr.precision))
2033 from lns_distributions lnd
2034 ,lns_loan_headers lnh
2035 ,fnd_currencies curr
2036 where lnh.loan_id = lnd.loan_id
2037 and lnh.loan_id = p_loan_id
2038 and curr.currency_code = lnh.loan_currency;
2039
2040 begin
2041
2042 l_api_name := 'calculateDistributionAmount';
2043 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2044 l_ledger_details := lns_distributions_pub.getLedgerDetails;
2045 l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2046
2047 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2048 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2049 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'disttype = ' || l_distribution_rec.distribution_type);
2050 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounttype = ' || l_distribution_rec.account_type);
2051
2052 if l_distribution_rec.distribution_amount is null then
2053 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_distributions_pub.getDistributions...');
2054 l_distribution_tbl := lns_distributions_pub.getDistributions(p_loan_id => l_distribution_rec.loan_id
2055 ,p_account_type => l_distribution_rec.account_type
2056 ,p_account_name => l_distribution_rec.account_name
2057 ,p_line_type => l_distribution_rec.line_type
2058 ,p_distribution_type => l_distribution_rec.distribution_type);
2059 open c_max_dist(l_distribution_rec.loan_id
2060 ,l_distribution_rec.distribution_type
2061 ,l_distribution_rec.account_type);
2062 fetch c_max_dist into l_max_distribution_id;
2063 close c_max_dist;
2064
2065 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_max_distribution_id = ' ||l_max_distribution_id);
2066
2067 -- check to see if this is the last distribution
2068 if l_max_distribution_id = p_distribution_id and l_distribution_tbl.count > 1 then
2069 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'last distribution');
2070 open c_last_distribution(l_distribution_rec.loan_id
2071 ,l_distribution_rec.distribution_id
2072 ,l_distribution_rec.distribution_type
2073 ,l_distribution_rec.account_type);
2074 fetch c_last_distribution into l_distribution_amount;
2075 close c_last_distribution;
2076 else
2077 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2078 open c_get_distribution(p_distribution_id);
2079 fetch c_get_distribution into l_distribution_amount;
2080 close c_get_distribution;
2081
2082 end if;
2083 else
2084 l_distribution_amount := l_distribution_rec.distribution_amount;
2085 end if;
2086
2087 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distribution_amount = ' ||l_distribution_amount);
2088 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2089 return l_distribution_amount;
2090
2091 end calculateDistributionAmount;
2092
2093 /*========================================================================
2094 || PUBLIC FUNCTION calculateDistributionAmount
2095 ||
2096 || DESCRIPTION
2097 || calculatest the distribution amount based on the distribution percentage
2098 || this api assumes the defaultDistributions API has been called to
2099 || store the distributions on LNS_DISTRIBUTIONS
2100 ||
2101 || PSEUDO CODE/LOGIC
2102 ||
2103 || PARAMETERS
2104 || p_distribution_id = pk to LNS_DISTRIBUTIONS
2105 || p_accounted_flag = 'Y' to get amount in set_of_books currency
2106 || 'N' to get amount in loan currency
2107 ||
2108 || Return value: amount of distribution based from loan funded amount
2109 ||
2110 || Source Tables: lns_distributions, lns_loan_headers
2111 ||
2112 || Target Tables: NA
2113 ||
2114 || KNOWN ISSUES
2115 ||
2116 || NOTES
2117 ||
2118 ||
2119 || MODIFICATION HISTORY
2120 || Date Author Description of Changes
2121 || 06/15/04 2:48:PM raverma Created
2122 || 09/07/04 raverma enhance to know about all distributions on loan
2123 || bug #3736979
2124 *=======================================================================*/
2125 function calculateDistributionAmount(p_distribution_id in number
2126 ,p_accounted_flag in varchar2) return number
2127
2128 is
2129 l_api_name varchar2(50);
2130 l_distribution_amount number;
2131 l_return number;
2132 l_ledger_details lns_distributions_pub.gl_ledger_details;
2133 l_distribution_rec lns_distributions_pub.distribution_rec;
2134 l_distribution_tbl lns_distributions_pub.distribution_tbl;
2135 l_loan_id number;
2136 l_max_distribution_id number;
2137 l_currency_code varchar2(10);
2138 l_exchange_rate_type varchar2(30);
2139 l_exchange_rate number;
2140 l_exchange_date date;
2141
2142 cursor c_exchange_info(p_loan_id number) is
2143 select lnh.exchange_rate_type
2144 ,lnh.exchange_rate
2145 ,lnh.exchange_date
2146 ,lnh.loan_currency
2147 from lns_loan_headers lnh
2148 where loan_id = p_loan_id;
2149
2150 cursor c_get_distribution(p_distribution_id number) is
2151 select round(lnh.funded_amount * lnd.distribution_percent / 100, curr.precision)
2152 from lns_distributions lnd
2153 ,lns_loan_headers lnh
2154 ,fnd_currencies curr
2155 where lnh.loan_id = lnd.loan_id
2156 and curr.currency_code = lnh.loan_currency
2157 and lnd.distribution_id = p_distribution_id;
2158
2159 cursor c_last_distribution(p_loan_id number
2160 ,p_distribution_id number
2161 ,p_distribution_type varchar
2162 ,p_account_type varchar2) is
2163 select
2164 lnh.funded_amount -
2165 (round(lnh.funded_amount *
2166 (select sum(distribution_percent) / 100
2167 from lns_distributions
2168 where distribution_id <> p_distribution_id
2169 and distribution_type = p_distribution_type
2170 and account_type = p_account_type
2171 and loan_id = p_loan_id), curr.precision))
2172 from lns_distributions lnd
2173 ,lns_loan_headers lnh
2174 ,fnd_currencies curr
2175 where lnh.loan_id = lnd.loan_id
2176 and lnh.loan_id = p_loan_id
2177 and curr.currency_code = lnh.loan_currency;
2178
2179 begin
2180
2181 l_api_name := 'calculateDistributionAmount';
2182
2183 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2184 l_ledger_details := lns_distributions_pub.getLedgerDetails;
2185 l_distribution_rec := lns_distributions_pub.getDistributions(p_distribution_id);
2186
2187 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2188 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2189 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'disttype = ' || l_distribution_rec.distribution_type);
2190 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounttype = ' || l_distribution_rec.account_type);
2191 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounted flag = ' || p_accounted_flag);
2192
2193 if p_accounted_flag = 'Y' then
2194
2195 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loanID = ' || l_distribution_rec.loan_id);
2196 open c_exchange_info(l_distribution_rec.loan_id);
2197 fetch c_exchange_info into
2198 l_exchange_rate_type
2199 ,l_exchange_rate
2200 ,l_exchange_date
2201 ,l_currency_code;
2202 close c_exchange_info;
2203 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'exchange rate type = ' || l_exchange_rate_type);
2204 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'exchange rate = ' || l_exchange_rate);
2205 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'exchange date = ' || l_exchange_date);
2206 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'currency = ' || l_currency_code);
2207 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'currency2 = ' || l_ledger_details.CURRENCY_CODE);
2208 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'amount = ' || l_distribution_rec.distribution_amount);
2209
2210 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling lns_utility_pub.convertAmount...');
2211 l_return := lns_utility_pub.convertAmount(p_from_amount => l_distribution_rec.distribution_amount
2212 ,p_from_currency => l_currency_code -- loan currency
2213 ,p_to_currency => l_ledger_details.CURRENCY_CODE -- set of books currency
2214 ,p_exchange_type => l_exchange_rate_type
2215 ,p_exchange_date => l_exchange_date
2216 ,p_exchange_rate => l_exchange_rate);
2217 else
2218 l_return := l_distribution_rec.distribution_amount;
2219 end if;
2220
2221 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'accounted amount = ' || l_return);
2222 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2223
2224 return l_return;
2225
2226 end calculateDistributionAmount;
2227
2228
2229 function calculateDistAmount(p_distribution_id in number
2230 ,p_accounted_flag in varchar2) return varchar2
2231 is
2232
2233 l_api_name varchar2(50);
2234 l_return varchar2(100);
2235 l_currency varchar2(10);
2236 l_char varchar2(25);
2237 l_amount number;
2238
2239 cursor c_currency1(p_distribution_id number) is
2240 select lnh.loan_currency
2241 from lns_loan_headers lnh
2242 ,lns_distributions lnd
2243 where lnh.loan_id = lnd.loan_id
2244 and lnd.distribution_id = p_distribution_id;
2245
2246 cursor c_currency2 is
2247 SELECT sb.currency_code
2248 FROM lns_system_options so,
2249 gl_sets_of_books sb
2250 WHERE sb.set_of_books_id = so.set_of_books_id;
2251
2252 begin
2253 l_api_name := 'calculateDistAmount';
2254 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2255 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIST_ID = ' || p_distribution_id);
2256
2257 if p_accounted_flag = 'Y' then
2258 open c_currency2;
2259 fetch c_currency2 into l_currency;
2260 close c_currency2;
2261 else
2262 open c_currency1(p_distribution_id);
2263 fetch c_currency1 into l_currency;
2264 close c_currency1;
2265 end if;
2266
2267 l_amount := lns_distributions_pub.calculateDistributionAmount(p_distribution_id => p_distribution_id
2268 ,p_accounted_flag => p_accounted_flag);
2269 l_char := to_char(l_amount, fnd_currency.safe_get_format_mask(l_currency,25));
2270
2271 l_return := l_char || ' ' || l_currency;
2272 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'returned amount = ' || l_return);
2273 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2274
2275 return l_return;
2276 end;
2277
2278 /*========================================================================
2279 | PUBLIC FUNCTION getFlexSegmentNumber
2280 |
2281 | DESCRIPTION
2282 | returns the segmentNumber for a given segment attribute type
2283 |
2284 | PSEUDO CODE/LOGIC
2285 |
2286 | PARAMETERS
2287 | p_segment_attribute_type = 'GL_BALANCING' 'GL_ACCOUNT' etc
2288 | Return value: value_set_id
2289 |
2290 | Source Tables: fnd_id_flex_segments s, fnd_segment_attribute_values sav,
2291 | fnd_segment_attribute_types sat
2292 | lns_system_options lso
2293 | gl_sets_of_books gl
2294 |
2295 | Target Tables: NA
2296 |
2297 | KNOWN ISSUES
2298 |
2299 | NOTES
2300 |
2301 |
2302 | MODIFICATION HISTORY
2303 | Date Author Description of Changes
2304 | 10/06/05 2:48:PM raverma Created
2305 *=======================================================================*/
2306 function getFlexSegmentNumber(p_flex_code in varchar2
2307 ,p_application_id in number
2308 ,p_segment_attribute_type in varchar2) return number
2309
2310 is
2311 l_flex_seg_num number;
2312
2313 cursor c_segmentNumber(p_flex_code varchar2, p_application_id number, p_segment_attribute_type varchar2)
2314 is
2315 SELECT s.segment_num
2316 FROM fnd_id_flex_segments s
2317 , fnd_segment_attribute_values sav
2318 , fnd_segment_attribute_types sat
2319 , lns_system_options lso
2320 , gl_ledgers gl
2321 WHERE s.application_id = p_application_id
2322 and lso.set_of_books_id = gl.ledger_id
2323 AND s.id_flex_code = p_flex_code
2324 AND s.id_flex_num = gl.chart_of_accounts_id
2325 AND s.enabled_flag = 'Y'
2326 AND s.application_column_name = sav.application_column_name
2327 AND sav.application_id = p_application_id
2328 AND sav.id_flex_code = p_flex_code
2329 AND sav.id_flex_num = gl.chart_of_accounts_id
2330 AND sav.attribute_value = 'Y'
2331 AND sav.segment_attribute_type = sat.segment_attribute_type
2332 AND sat.application_id = p_application_id
2333 AND sat.id_flex_code = p_flex_code
2334 AND sat.unique_flag = 'Y'
2335 and sat.segment_attribute_type = p_segment_attribute_type;
2336
2337 begin
2338
2339 l_flex_seg_num := -1;
2340
2341 open c_segmentNumber(p_flex_code , p_application_id , p_segment_attribute_type );
2342 fetch c_segmentNumber into l_flex_seg_num;
2343 close c_segmentNumber;
2344 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_flex_seg_num = ' || l_flex_seg_num);
2345
2346 return l_flex_seg_num;
2347
2348 end getFlexSegmentNumber;
2349
2350 /*========================================================================
2351 | PUBLIC FUNCTION getValueSetID
2352 |
2353 | DESCRIPTION
2354 | returns a valueSetID for a given segment attribute
2355 |
2356 | PSEUDO CODE/LOGIC
2357 |
2358 | PARAMETERS
2359 | p_segment_attribute_type = 'GL_BALANCING' 'GL_ACCOUNT' etc
2360 | Return value: value_set_id
2361 |
2362 | Source Tables: fnd_id_flex_segments s, fnd_segment_attribute_values sav,
2363 | fnd_segment_attribute_types sat
2364 | lns_system_options lso
2365 | gl_sets_of_books gl
2366 |
2367 | Target Tables: NA
2368 |
2369 | KNOWN ISSUES
2370 |
2371 | NOTES
2372 |
2373 |
2374 | MODIFICATION HISTORY
2375 | Date Author Description of Changes
2376 | 06/15/04 2:48:PM raverma Created
2377 |
2378 *=======================================================================*/
2379 function getValueSetID(p_segment_attribute_type in varchar) return number
2380 is
2381 l_value_set_id number;
2382
2383 cursor c_valueSetID(p_segment_attribute_type varchar2) is
2384 SELECT s.flex_value_set_id
2385 FROM fnd_id_flex_segments s
2386 ,fnd_segment_attribute_values sav
2387 ,fnd_segment_attribute_types sat
2388 ,lns_system_options lso
2389 ,gl_ledgers gl
2390 WHERE s.application_id = 101
2391 and lso.set_of_books_id = gl.ledger_id
2392 AND s.id_flex_code = 'GL#'
2393 AND s.id_flex_num = gl.chart_of_accounts_id
2394 AND s.enabled_flag = 'Y'
2395 AND s.application_column_name = sav.application_column_name
2396 AND sav.application_id = 101
2397 AND sav.id_flex_code = 'GL#'
2398 AND sav.id_flex_num = gl.chart_of_accounts_id
2399 AND sav.attribute_value = 'Y'
2400 AND sav.segment_attribute_type = sat.segment_attribute_type
2401 AND sat.application_id = 101
2402 AND sat.id_flex_code = 'GL#'
2403 AND sat.unique_flag = 'Y'
2404 AND sat.segment_attribute_type = p_segment_attribute_type;
2405
2406 begin
2407 open c_valueSetID(p_segment_attribute_type);
2408 fetch c_valueSetID into l_value_set_id;
2409 close c_valueSetID;
2410 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_value_set_id = ' || l_value_set_id);
2411
2412 return l_value_set_id;
2413
2414 end getValueSetID;
2415
2416 /*=========================================================================
2417 | PUBLIC procedure validateAccounting
2418 |
2419 | DESCRIPTION
2420 | validates all accounting for a given loan
2421 |
2422 | PSEUDO CODE/LOGIC
2423 |
2424 | PARAMETERS
2425 | p_loan_id => id of loan
2426 |
2427 | Return value: standard api values
2428 |
2429 | Source Tables: lns_Distritbutions
2430 |
2431 | Target Tables: NA
2432 |
2433 | KNOWN ISSUES
2434 | E. The code combinations distribution percentages for each Loans Receivable
2435 | and corresponding Loans Clearing account must be equal
2436 |
2437 | NOTES
2438 |
2439 |
2440 | MODIFICATION HISTORY
2441 | Date Author Description of Changes
2442 | 06/28/04 2:48:PM raverma Created
2443 |
2444 *=======================================================================*/
2445 procedure validateAccounting(p_loan_id in number
2446 ,p_init_msg_list IN VARCHAR2
2447 ,x_return_status OUT NOCOPY VARCHAR2
2448 ,x_msg_count OUT NOCOPY NUMBER
2449 ,x_msg_data OUT NOCOPY VARCHAR2)
2450 is
2451
2452 --l_loan_liability_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2453 l_loan_clearing_fund LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2454 l_loan_receivables_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2455 l_loan_clearing_orig LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2456 l_loan_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2457 l_prin_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2458 l_int_receivables_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2459 l_int_income_bill LNS_DISTRIBUTIONS_PUB.distribution_tbl;
2460
2461 l_dist_percent_rec_orig number;
2462 l_dist_percent_rec_bill number;
2463 l_dist_percent_clear_orig number;
2464 l_dist_percent_int_income number;
2465 l_loan_class varchar2(30);
2466 l_num_receivables_ers number;
2467 l_num_receivables_acc number;
2468
2469 l_api_name varchar2(30);
2470
2471 cursor c_loan_class(p_loan_id number) is
2472 select loan_class_code
2473 from lns_loan_headers_all
2474 where loan_id = p_loan_id;
2475
2476 begin
2477
2478 l_api_name := 'validateAccounting1';
2479 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2480
2481 -- Initialize message list IF p_init_msg_list is set to TRUE.
2482 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2483 FND_MSG_PUB.initialize;
2484 END IF;
2485
2486 -- Initialize API return status to SUCCESS
2487 x_return_status := FND_API.G_RET_STS_SUCCESS;
2488
2489 l_dist_percent_rec_orig := 0;
2490 l_dist_percent_rec_bill := 0;
2491 l_dist_percent_clear_orig := 0;
2492 l_dist_percent_int_income := 0;
2493
2494 open c_loan_class(p_loan_id);
2495 fetch c_loan_class into l_loan_class;
2496 close c_loan_class;
2497
2498 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan_class = ' || l_loan_class);
2499 -- get the distributions details
2500 /*
2501 l_loan_liability_fund := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2502 ,p_account_type => 'DR'
2503 ,p_account_name => 'LOAN_LIABILITY'
2504 ,p_line_type => 'ORIG'
2505 ,p_distribution_type => 'FUNDING');
2506 */
2507 if l_loan_class = 'ERS' then
2508
2509 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 1...');
2510 l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2511 ,p_account_type => 'CR'
2512 ,p_account_name => 'LOAN_CLEARING'
2513 ,p_line_type => 'CLEAR'
2514 ,p_distribution_type => 'ORIGINATION');
2515 elsif l_loan_class = 'DIRECT' then
2516
2517 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 2...');
2518 l_loan_clearing_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2519 ,p_account_type => 'CR'
2520 ,p_account_name => 'LOAN_PAYABLE'
2521 ,p_line_type => 'CLEAR'
2522 ,p_distribution_type => 'ORIGINATION');
2523 end if;
2524
2525 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 3...');
2526 l_loan_receivables_orig := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2527 ,p_account_type => 'DR'
2528 ,p_account_name => 'LOAN_RECEIVABLE'
2529 ,p_line_type => 'ORIG'
2530 ,p_distribution_type => 'ORIGINATION');
2531
2532 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 4...');
2533 l_loan_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2534 ,p_account_type => 'CR'
2535 ,p_account_name => 'LOAN_RECEIVABLE'
2536 ,p_line_type => 'PRIN'
2537 ,p_distribution_type => 'BILLING');
2538
2539 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 5...');
2540 l_prin_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2541 ,p_account_type => 'DR'
2542 ,p_account_name => 'PRINCIPAL_RECEIVABLE'
2543 ,p_line_type => 'PRIN'
2544 ,p_distribution_type => 'BILLING');
2545
2546 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 6...');
2547 l_int_receivables_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2548 ,p_account_type => 'DR'
2549 ,p_account_name => 'INTEREST_RECEIVABLE'
2550 ,p_line_type => 'INT'
2551 ,p_distribution_type => 'BILLING');
2552
2553 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_DISTRIBUTIONS_PUB.getDistributions 7...');
2554 l_int_income_bill := LNS_DISTRIBUTIONS_PUB.getDistributions(p_loan_id => p_loan_id
2555 ,p_account_type => 'CR'
2556 ,p_account_name => 'INTEREST_INCOME'
2557 ,p_line_type => 'INT'
2558 ,p_distribution_type => 'BILLING');
2559
2560 if l_loan_class = 'ERS' then
2561
2562 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ERS VALIDATION');
2563
2564 /*
2565 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'getting number of receivables on ers loan');
2566 open c_num_receivables_ers(p_loan_id);
2567 fetch c_num_receivables_ers into l_num_receivables_ers;
2568 close c_num_receivables_ers;
2569 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'num is: ' || l_num_receivables_ers);
2570
2571 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'getting number of receivables ACCOUNTED');
2572 open c_num_receivables_acc(p_loan_id);
2573 fetch c_num_receivables_acc into l_num_receivables_acc;
2574 close c_num_receivables_acc;
2575 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'num is: ' || l_num_receivables_acc);
2576
2577 if l_num_receivables_acc <> l_num_receivables_ers then
2578 FND_MESSAGE.Set_Name('LNS', 'LNS_AR_RECEIVABLES_UNACC');
2579 FND_MSG_PUB.Add;
2580 RAISE FND_API.G_EXC_ERROR;
2581 end if;
2582 */
2583
2584 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'comparing origination receivables to billing receivables count');
2585 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables origination Count = ' || l_loan_receivables_orig.count );
2586 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables billing Count = ' || l_loan_receivables_bill.count);
2587 if l_loan_receivables_orig.count <> l_loan_receivables_bill.count then
2588 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_INVALID_RECEIVABLES');
2589 FND_MSG_PUB.Add;
2590 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2591 RAISE FND_API.G_EXC_ERROR;
2592 end if;
2593
2594 -- B. The account code combinations for the Loans Receivable accounts within
2595 -- Origination must be the same as the account code combinations for the Loans
2596 -- Receivable accounts within Billing
2597 for j in 1..l_loan_receivables_orig.count loop
2598
2599 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'comparing origination receivables to billing receivables cc_ids');
2600 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables origination cc_id = ' || l_loan_receivables_orig(j).code_combination_id );
2601 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables billing cc_id = ' || l_loan_receivables_bill(j).code_combination_id);
2602 if l_loan_receivables_orig(j).code_combination_id <> l_loan_receivables_bill(j).code_combination_id then
2603 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_LOAN_REC_CCIDS_UNMATCH');
2604 FND_MSG_PUB.Add;
2605 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2606 RAISE FND_API.G_EXC_ERROR;
2607 end if;
2608
2609 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking for duplicate IDs');
2610 if j < l_loan_receivables_orig.count then
2611 if (l_loan_receivables_orig(j).code_combination_id = l_loan_receivables_orig(j+1).code_combination_id) OR
2612 (l_loan_clearing_orig(j).code_combination_id = l_loan_clearing_orig(j+1).code_combination_id)
2613 then
2614 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'found duplicate IDs');
2615 FND_MESSAGE.Set_Name('LNS', 'LNS_UNIQUE_CC_IDS');
2616 FND_MSG_PUB.Add;
2617 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2618 RAISE FND_API.G_EXC_ERROR;
2619 end if;
2620 end if;
2621
2622 -- F. The code combinations distribution percentages for each Loans Receivable
2623 -- within Origination must equal the distribution percentage for each Loans
2624 -- Receivable within Billing
2625 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'comparing origination receivables to billing receivables percentages');
2626 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables origination percent = ' || l_loan_receivables_orig(j).distribution_percent );
2627 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables billing percent = ' || l_loan_receivables_bill(j).distribution_percent);
2628 if l_loan_receivables_orig(j).distribution_percent <> l_loan_receivables_bill(j).distribution_percent then
2629 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_LOAN_REC_PER_UNMATCH');
2630 FND_MSG_PUB.Add;
2631 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2632 RAISE FND_API.G_EXC_ERROR;
2633 end if;
2634
2635 l_dist_percent_rec_orig := l_dist_percent_rec_orig + l_loan_receivables_orig(j).distribution_percent;
2636 l_dist_percent_rec_bill := l_dist_percent_rec_bill + l_loan_receivables_bill(j).distribution_percent;
2637 --l_dist_percent_clear_orig := l_dist_percent_clear_orig + l_loan_clearing_orig(j).distribution_percent;
2638
2639 end loop;
2640
2641 -- C. The distribution percentage for the Loans Receivable accounts in both
2642 -- Origination and Billing must add to 100%
2643 -- D. The distribution percentage for the Loans Clearing accounts in Origination must add to 100%
2644 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking origination receivables total percentages');
2645 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables total percent = ' || l_dist_percent_rec_orig);
2646 if l_dist_percent_rec_orig <> 100 then
2647 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_ORIG_REC_PER_INVALID');
2648 FND_MSG_PUB.Add;
2649 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2650 RAISE FND_API.G_EXC_ERROR;
2651 end if;
2652 end if;
2653
2654 /*
2655 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking billing receivables total percentages');
2656 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan recivables total percent: ' || l_dist_percent_rec_bill);
2657 if l_dist_percent_rec_bill <> 100 then
2658 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_BILL_REC_PER_INVALID');
2659 FND_MSG_PUB.Add;
2660 RAISE FND_API.G_EXC_ERROR;
2661 end if;
2662 */
2663
2664 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking origination clearing total percentages');
2665 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'loan clearing total percent = ' || l_dist_percent_clear_orig);
2666 /*
2667 if l_dist_percent_clear_orig <> 100 then
2668 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_ORIG_CLR_PER_INVALID');
2669 FND_MSG_PUB.Add;
2670 RAISE FND_API.G_EXC_ERROR;
2671 end if;
2672 */
2673 -- G. In the current release of 11i, there must be only one Principal Receivable
2674 -- account
2675 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking principal receivables count');
2676 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'principal receivables count = ' || l_prin_receivables_bill.count);
2677 if l_prin_receivables_bill.count <> 1 then
2678 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_MULT_PRIN_RECEIVABLE');
2679 FND_MSG_PUB.Add;
2680 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2681 RAISE FND_API.G_EXC_ERROR;
2682 end if;
2683
2684 -- H. In the current release of 11i, there must be only one Interest Receivable
2685 -- account
2686 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking Interest receivables count');
2687 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Interest receivables count = ' || l_int_receivables_bill.count);
2688 if l_int_receivables_bill.count <> 1 then
2689 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_MULT_INT_RECEIVABLE');
2690 FND_MSG_PUB.Add;
2691 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2692 RAISE FND_API.G_EXC_ERROR;
2693 end if;
2694
2695 -- I. There may be multiple Interest Income accounts
2696 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking Interest Income count');
2697 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Interest Income count = ' || l_int_income_bill.count);
2698 if l_int_income_bill.count < 1 then
2699 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_NO_INTEREST_INCOME');
2700 FND_MSG_PUB.Add;
2701 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2702 RAISE FND_API.G_EXC_ERROR;
2703 end if;
2704
2705 -- J. The distribution percentages for Interest Income must add to 100%
2706 for j in 1..l_int_income_bill.count loop
2707 l_dist_percent_int_income := l_dist_percent_int_income + l_int_income_bill(j).distribution_percent;
2708 end loop;
2709
2710 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking Interest Income percentage');
2711 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Interest Income percentage = ' || l_dist_percent_int_income);
2712 if l_dist_percent_int_income <> 100 then
2713 FND_MESSAGE.Set_Name('LNS', 'LNS_ACC_INT_INCOME_PER_INVALID');
2714 FND_MSG_PUB.Add;
2715 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
2716 RAISE FND_API.G_EXC_ERROR;
2717 end if;
2718
2719 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2720 ,p_data => x_msg_data);
2721
2722 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2723
2724 Exception
2725 WHEN FND_API.G_EXC_ERROR THEN
2726 x_return_status := FND_API.G_RET_STS_ERROR;
2727 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2728 ,p_data => x_msg_data);
2729 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2730
2731 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2732 x_return_status := FND_API.G_RET_STS_ERROR;
2733 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2734 ,p_data => x_msg_data);
2735 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2736
2737 WHEN OTHERS THEN
2738 x_return_status := FND_API.G_RET_STS_ERROR;
2739 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
2740 ,p_data => x_msg_data);
2741 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2742
2743 end validateAccounting;
2744
2745
2746 /*========================================================================
2747 | PUBLIC procedure validateDefaultAccounting
2748 |
2749 | DESCRIPTION
2750 | validates all accounting for a given loan loan_class and type
2751 |
2752 | PSEUDO CODE/LOGIC
2753 |
2754 | PARAMETERS
2755 | p_loan_id => id of loan
2756 ||
2757 | Return value: standard api values
2758 |
2759 | Source Tables: lns_Distritbutions
2760 |
2761 | Target Tables: NA
2762 |
2763 | KNOWN ISSUES
2764 E. The code combinations distribution percentages for each Loans Receivable
2765 and corresponding Loans Clearing account must be equal
2766 |
2767 | NOTES
2768 |
2769 |
2770 | MODIFICATION HISTORY
2771 | Date Author Description of Changes
2772 | 06/28/04 2:48:PM raverma Created
2773 | 06/29/04 raverma added MFAR checking
2774 || 08/04/05 raverma deprecate
2775 *=======================================================================*/
2776 procedure validateDefaultAccounting(p_loan_class in varchar2
2777 ,p_loan_type_id in number
2778 ,p_init_msg_list IN VARCHAR2
2779 ,x_return_status OUT NOCOPY VARCHAR2
2780 ,x_msg_count OUT NOCOPY NUMBER
2781 ,x_msg_data OUT NOCOPY VARCHAR2)
2782 is
2783 begin
2784
2785 -- Initialize API return status to SUCCESS
2786 x_return_status := FND_API.G_RET_STS_SUCCESS;
2787
2788 end validateDefaultAccounting;
2789
2790 /*========================================================================
2791 | PUBLIC procedure validateLoanLines
2792 |
2793 | DESCRIPTION
2794 | verifies that the loan lines are either ALL MFAR or ALL NON-MFAR
2795 |
2796 | PSEUDO CODE/LOGIC
2797 |
2798 | PARAMETERS
2799 | p_loan_id => id of loan
2800 ||
2801 | Return value: standard api values
2802 |
2803 | Source Tables: lns_Distritbutions
2804 |
2805 | Target Tables: NA
2806 |
2807 | KNOWN ISSUES
2808 E. The code combinations distribution percentages for each Loans Receivable
2809 and corresponding Loans Clearing account must be equal
2810 |
2811 | NOTES
2812 |
2813 |
2814 | MODIFICATION HISTORY
2815 | Date Author Description of Changes
2816 | 12/20/04 2:48:PM raverma Created
2817 *=======================================================================*/
2818 procedure validateLoanLines(p_init_msg_list IN VARCHAR2
2819 ,p_loan_id IN number
2820 ,x_MFAR OUT NOCOPY boolean
2821 ,x_return_status OUT NOCOPY VARCHAR2
2822 ,x_msg_count OUT NOCOPY NUMBER
2823 ,x_msg_data OUT NOCOPY VARCHAR2)
2824 is
2825 l_api_name varchar2(25);
2826 l_return_status VARCHAR2(1);
2827 l_msg_count NUMBER;
2828 l_msg_data VARCHAR2(32767);
2829 l_trx_type_id number;
2830 l_psa_trx_type_id number;
2831 l_mfar boolean;
2832 l_lines_count number;
2833 i number;
2834 l_multifund number;
2835
2836 --find out if there is more than one line
2837 cursor c_loan_lines(p_loan_id number) is
2838 select count(1)
2839 from lns_loan_lines
2840 where loan_id = p_loan_id
2841 and reference_type = 'RECEIVABLE'
2842 and end_date is null;
2843
2844 -- for loans with more than one line
2845 cursor c_validate_MFAR (p_loan_id number) is
2846 select ra.cust_trx_type_id, nvl(psa.psa_trx_type_id,-1)
2847 from lns_loan_lines lines
2848 ,ra_customer_trx ra
2849 ,psa_trx_types_all psa
2850 where ra.customer_trx_id = lines.reference_id
2851 and psa.psa_trx_type_id (+)= ra.cust_trx_type_id
2852 and lines.reference_type = 'RECEIVABLE'
2853 and lines.end_date is null
2854 and lines.loan_id = p_loan_id
2855 group by ra.cust_trx_type_id, psa.psa_trx_type_id;
2856
2857 -- cursor to identify MFAR trx 1 = MFAR, 0 <> MFAR
2858 -- assumes only one line is on the loan
2859 cursor c_multiFundTrx(p_loan_id in number)
2860 is
2861 select nvl(1,0)
2862 from ra_customer_trx ra
2863 ,psa_trx_types_all psa
2864 ,lns_loan_lines lines
2865 where ra.CUST_TRX_TYPE_ID = psa.psa_trx_type_id
2866 and ra.customer_trx_id = lines.reference_id
2867 and lines.end_date is null
2868 and lines.reference_type = 'RECEIVABLE'
2869 and lines.loan_id = p_loan_id
2870 group by lines.loan_id;
2871
2872 begin
2873
2874 l_api_name := 'validateLoanLines';
2875 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
2876
2877 -- Standard Start of API savepoint
2878 SAVEPOINT validateLoanLines;
2879
2880 -- Initialize message list IF p_init_msg_list is set to TRUE.
2881 IF FND_API.to_Boolean(p_init_msg_list) THEN
2882 FND_MSG_PUB.initialize;
2883 END IF;
2884
2885 -- Initialize API return status to SUCCESS
2886 x_return_status := FND_API.G_RET_STS_SUCCESS;
2887
2888 -- ---------------------------------------------------------------------
2889 -- Api body
2890 -- ---------------------------------------------------------------------
2891 l_lines_count := 0;
2892 i := 0;
2893
2894 open c_loan_lines(p_loan_id);
2895 fetch c_loan_lines into l_lines_count;
2896 close c_loan_lines;
2897
2898 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'lines count is ' || l_lines_count);
2899 -- check if ALL are MFAR or all are NOT MFAR only if there is more
2900 -- than 1 line on the loan
2901 if l_lines_count > 1 then
2902
2903 open c_validate_MFAR(p_loan_id);
2904 LOOP
2905
2906 i := i + 1;
2907 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'processing ' || i || ' loan line');
2908 fetch c_validate_MFAR
2909 into l_trx_type_id
2910 ,l_psa_trx_type_id;
2911
2912 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'trx type ' || l_trx_type_id);
2913 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'psa trx type '|| l_psa_trx_type_id);
2914
2915 exit when c_validate_MFAR%NOTFOUND;
2916
2917 if i <> 1 then
2918 if l_mfar then
2919 if l_psa_trx_type_id = -1 then
2920 RAISE FND_API.G_EXC_ERROR;
2921 end if;
2922 else
2923 if l_psa_trx_type_id <> -1 then
2924 RAISE FND_API.G_EXC_ERROR;
2925 end if;
2926 end if;
2927 else
2928 if l_psa_trx_type_id = -1 then
2929 -- the first line on the loan is NOT MFAR
2930 -- all subsequent lines SHOULD be MFAR
2931 l_mfar := false;
2932 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'NON-MFAR');
2933 else
2934 l_mfar := true;
2935 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'MFAR');
2936 end if;
2937
2938 end if;
2939 end loop;
2940
2941 else
2942 -- we only have 1 line on the loan...verify is it is MFAR or not
2943 -- check to see if trx_type is refered to in psa_trx_type
2944 -- if so this is a multi-fund receivable
2945 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking for MFAR');
2946 begin
2947
2948 open c_multiFundTrx(p_loan_id) ;
2949 fetch c_multiFundTrx into l_multifund;
2950 close c_multiFundTrx;
2951
2952 exception
2953 when others then
2954 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'no rows found');
2955 l_multifund := 0;
2956 end;
2957
2958 if l_multifund = 1 then
2959 l_mfar := true;
2960 else
2961 l_mfar := false;
2962 end if;
2963
2964 end if;
2965
2966 -- this will be needed by defaultDistributions
2967 x_mfar := l_mfar;
2968 --logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'sql ' || vPLSQL);
2969
2970 -- ---------------------------------------------------------------------
2971 -- End of API body
2972 -- ---------------------------------------------------------------------
2973
2974 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2975
2976 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
2977
2978 EXCEPTION
2979 WHEN FND_API.G_EXC_ERROR THEN
2980 ROLLBACK TO validateLoanLines;
2981 x_return_status := FND_API.G_RET_STS_ERROR;
2982 x_msg_count := l_msg_count;
2983 x_msg_data := l_msg_data;
2984 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2985 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2986
2987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2988 ROLLBACK TO validateLoanLines;
2989 x_return_status := FND_API.G_RET_STS_ERROR;
2990 x_msg_count := l_msg_count;
2991 x_msg_data := l_msg_data;
2992 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2993 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
2994
2995 WHEN OTHERS THEN
2996 ROLLBACK TO validateLoanLines;
2997 x_return_status := FND_API.G_RET_STS_ERROR;
2998 x_msg_count := l_msg_count;
2999 x_msg_data := l_msg_data;
3000 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3001 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3002
3003 end validateLoanLines;
3004
3005
3006 procedure create_DisbursementDistribs(p_api_version IN NUMBER
3007 ,p_init_msg_list IN VARCHAR2
3008 ,p_commit IN VARCHAR2
3009 ,p_loan_id IN NUMBER
3010 ,p_disb_header_id IN NUMBER
3011 ,x_return_status OUT NOCOPY VARCHAR2
3012 ,x_msg_count OUT NOCOPY NUMBER
3013 ,x_msg_data OUT NOCOPY VARCHAR2)
3014
3015 is
3016 l_api_name varchar2(30);
3017 l_msg_count NUMBER;
3018 l_msg_data VARCHAR2(2000);
3019 l_return_Status VARCHAR2(1);
3020 l_distributions lns_distributions_pub.distribution_tbl;
3021 l_exists number;
3022 l_disb_header_id number;
3023 l_distributions_count number;
3024 i number;
3025 l_subsidy_rate number;
3026 l_subsidy_exists number;
3027
3028
3029 cursor c_distribsExist(p_loan_id number, p_disb_header_id number) is
3030 select count(1)
3031 from lns_distributions
3032 where loan_id = p_loan_id
3033 and distribution_type = 'ORIGINATION'
3034 and disb_header_id = p_disb_header_id;
3035
3036 cursor c_first_disb(p_loan_id number) is
3037 select disb_header_id
3038 from lns_disb_headers
3039 where loan_id = p_loan_id;
3040
3041 cursor c_subsidy_rows_exist(p_loan_id number) is
3042 select count(1)
3043 from lns_distributions
3044 where loan_id = p_loan_id
3045 and line_type = 'SUBSIDY'
3046 and distribution_type = 'ORIGINATION'
3047 and event_id is not null;
3048
3049 -- Bug#6711399 subsidy_rate is taken from 'loan header' table, which is
3050 -- defaulted from product at the loan creation time.
3051 cursor c_subsidy_rate (p_loan_id number) is
3052 SELECT
3053 (subsidy_rate/100)
3054 FROM
3055 lns_loan_headers_all
3056 WHERE
3057 loan_id = p_loan_id;
3058
3059
3060 begin
3061
3062 l_api_name := 'create_DisbursementDistribs';
3063 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - BEGIN');
3064
3065 SAVEPOINT create_DisbursementDistribs;
3066
3067 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'BEGIN2');
3068 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
3069 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
3070
3071 -- Initialize message list IF p_init_msg_list is set to TRUE.
3072 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3073 FND_MSG_PUB.initialize;
3074 END IF;
3075
3076 -- Initialize API return status to SUCCESS
3077 x_return_status := FND_API.G_RET_STS_SUCCESS;
3078
3079 if p_disb_header_id is not null then
3080 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_disb_header_id = ' || p_disb_header_id);
3081 l_disb_header_id := p_disb_header_id;
3082 else
3083 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'finding first disbursement');
3084 open c_first_disb(p_loan_id);
3085 fetch c_first_disb into l_disb_header_id;
3086 close c_first_disb;
3087 end if;
3088
3089 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'getting subsidy_rate ');
3090 open c_subsidy_rate(p_loan_id);
3091 fetch c_subsidy_rate into l_subsidy_rate;
3092 close c_subsidy_rate;
3093 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_subsidy_rate = ' || l_subsidy_rate);
3094
3095 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_disb_header_id = ' || l_disb_header_id);
3096 open c_distribsExist(p_loan_id, l_disb_header_id);
3097 fetch c_distribsExist into l_exists;
3098 close c_distribsExist;
3099
3100 if l_exists = 0 then
3101 -- get the cc_ids and percentages for the each loan_class_code and loan_type_id
3102 --break up the distribution amounts for the disbursement
3103 -- insert into the distributions table
3104 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling defaultDistributionsCatch...');
3105 defaultDistributionsCatch(p_api_version => 1.0
3106 ,p_init_msg_list => FND_API.G_TRUE
3107 ,p_commit => p_commit
3108 ,p_loan_id => p_loan_id
3109 ,p_disb_header_id => l_disb_header_id
3110 ,p_include_loan_receivables => 'Y'
3111 ,p_distribution_type => 'ORIGINATION'
3112 ,x_distribution_tbl => l_distributions
3113 ,x_return_status => l_return_status
3114 ,x_msg_count => l_msg_count
3115 ,x_msg_data => l_msg_data);
3116
3117 if l_return_status <> 'S' then
3118 RAISE FND_API.G_EXC_ERROR;
3119 end if;
3120
3121 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_distributionsCatch.count = ' || l_distributions.count);
3122 for j in 1..l_distributions.count loop
3123 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'adding disb_header_id ' || j);
3124 -- l_distributions(j).event_id := l_event_id;
3125 l_distributions(j).disb_header_id := p_disb_header_id;
3126 end loop;
3127
3128 l_distributions_count := l_distributions.count;
3129 i := l_distributions.count;
3130
3131 if lns_utility_pub.IS_FED_FIN_ENABLED = 'Y' then
3132
3133 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'checking for existing subsidy rows');
3134 open c_subsidy_rows_exist(p_loan_id);
3135 fetch c_subsidy_rows_exist into l_subsidy_exists;
3136 close c_subsidy_rows_exist;
3137
3138 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_subsidy_exists = ' || l_subsidy_exists);
3139 if l_subsidy_exists = 0 then
3140
3141 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'fed enabled adding subsidy rows ');
3142 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'subsidy rows do not exist ');
3143 for j in 1..l_distributions_count loop
3144
3145 -- if (l_distributions(j).ACCOUNT_TYPE = 'ORIGINATION' AND
3146 -- (l_distributions(j).ACCOUNT_NAME = 'LOAN_RECEIVABLE' OR
3147 -- l_distributions(j).ACCOUNT_NAME = 'LOAN_PAYABLE' )) then
3148 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'adding subsidy row # ' || j);
3149 i := i + 1;
3150 -- add rows for subsidy cost
3151
3152 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account_name = ' || l_distributions(j).account_name);
3153 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'CODE_COMBINATION_ID = ' || l_distributions(j).CODE_COMBINATION_ID);
3154 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'account_type = ' || l_distributions(j).account_type);
3155 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DISTRIBUTION_PERCENT = ' || l_distributions(j).DISTRIBUTION_PERCENT);
3156 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DISTRIBUTION_TYPE = ' || l_distributions(j).DISTRIBUTION_TYPE);
3157 l_distributions(i).LOAN_ID := p_loan_id;
3158 l_distributions(i).LINE_TYPE := 'SUBSIDY';
3159 l_distributions(i).ACCOUNT_NAME := l_distributions(j).account_name;
3160 l_distributions(i).CODE_COMBINATION_ID := l_distributions(j).CODE_COMBINATION_ID;
3161 l_distributions(i).ACCOUNT_TYPE := l_distributions(j).account_type;
3162 l_distributions(i).DISTRIBUTION_PERCENT:= l_distributions(j).DISTRIBUTION_PERCENT;
3163 l_distributions(i).distribution_amount := l_distributions(j).DISTRIBUTION_AMOUNT * l_subsidy_Rate;
3164 l_distributions(i).DISTRIBUTION_TYPE := l_distributions(j).DISTRIBUTION_TYPE;
3165 l_distributions(i).EVENT_ID := null;
3166
3167 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'running count = ' || l_distributions.count);
3168 -- end if;
3169 end loop;
3170
3171 end if; -- l_subsidy_exists
3172
3173 end if;
3174 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'total distributions adding = ' || l_distributions.count);
3175
3176 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling do_insert_distributions...');
3177 do_insert_distributions(l_distributions, p_loan_id);
3178
3179 end if;
3180
3181 IF FND_API.to_Boolean(p_commit)
3182 THEN
3183 COMMIT WORK;
3184 END IF;
3185
3186 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3187 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
3188
3189 EXCEPTION
3190
3191 WHEN FND_API.G_EXC_ERROR THEN
3192 ROLLBACK TO create_DisbursementDistribs;
3193 x_return_status := FND_API.G_RET_STS_ERROR;
3194 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3195 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3196
3197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3198 ROLLBACK TO create_DisbursementDistribs;
3199 x_return_status := FND_API.G_RET_STS_ERROR;
3200 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3201 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3202
3203 WHEN OTHERS THEN
3204 ROLLBACK TO create_DisbursementDistribs;
3205 x_return_status := FND_API.G_RET_STS_ERROR;
3206 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
3207 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3208
3209 end create_DisbursementDistribs;
3210
3211 /*=========================================================================
3212 || PUBLIC PROCEDURE defaultDistributions
3213 ||
3214 || DESCRIPTION
3215 || This procedure defaults distributions (if set) for a particular
3216 || loan_class + loan_Type
3217 ||
3218 || PSEUDO CODE/LOGIC
3219 ||
3220 || PARAMETERS
3221 || Parameter: p_loan_id => loan to default
3222 ||
3223 || Return value: Standard S = Success E = Error U = Unexpected
3224 ||
3225 || Source Tables: LNS_DEFAULT_DISTRIBUTIONS, lns_loan_headers_all
3226 ||
3227 || Target Tables: LNS_DISTRIBUTIONS
3228 ||
3229 || KNOWN ISSUES
3230 ||
3231 || NOTES
3232 ||
3233 || MODIFICATION HISTORY
3234 || Date Author Description of Changes
3235 || 02-16-2004 raverma Created
3236 || 03-16-2004 raverma added in default for multi-fund loans inherit from receivables
3237 || 06-16-2004 raverma changed rules on inheritance for MFAR transactions
3238 || get amounts for MFAR from PSA_MF_BALANCES_VIEW
3239 || 07-26-2004 raverma delete rows before each call to accounting
3240 || 12-18-2004 raverma look at lns_loan_lines
3241 || 12-18-2004 raverma need to get loan class code
3242 || 04-19-2005 raverma establish loan clearing as per bug #4313925
3243 *=======================================================================*/
3244 procedure defaultDistributions(p_api_version IN NUMBER
3245 ,p_init_msg_list IN VARCHAR2
3246 ,p_commit IN VARCHAR2
3247 ,p_loan_id IN NUMBER
3248 ,p_loan_class_code IN VARCHAR2
3249 ,x_return_status OUT NOCOPY VARCHAR2
3250 ,x_msg_count OUT NOCOPY NUMBER
3251 ,x_msg_data OUT NOCOPY VARCHAR2)
3252
3253 is
3254 /*------------------------------------------------------------------------+
3255 | Local Variable Declarations and initializations |
3256 +-----------------------------------------------------------------------*/
3257 l_api_name varchar2(30);
3258 l_msg_count NUMBER;
3259 l_msg_data VARCHAR2(2000);
3260 l_return_Status VARCHAR2(1);
3261 i number;
3262 n number;
3263 l_code_combination_id number;
3264 l_code_combination_id_new_rec number;
3265 l_distributions lns_distributions_pub.distribution_tbl;
3266 l_distributionsCLEAR_ORIG lns_distributions_pub.distribution_tbl;
3267 l_distributionsREC_ORIG lns_distributions_pub.distribution_tbl;
3268 l_distributionsREC_BILL lns_distributions_pub.distribution_tbl;
3269 l_distributionsCatch lns_distributions_pub.distribution_tbl;
3270 l_distributionsALL lns_distributions_pub.distribution_tbl;
3271 l_distributions_count number;
3272 l_distributionsCatch_count number;
3273 l_total_distributions number;
3274 l_ers_distribution_amount number;
3275 l_orig_distribution_amount number;
3276 l_ledger_details lns_distributions_pub.gl_ledger_details;
3277 l_include_receivables varchar2(1);
3278 l_sum number;
3279 l_multifund number;
3280 l_multifund_exists number;
3281 l_total_percent number;
3282 l_total_receivable_amount number;
3283 l_natural_account_rec varchar2(25); -- the lns_def_distribs replacement for Loans Receivable
3284 l_nat_acct_seg_number number;
3285 l_num_segments number;
3286 l_adjustment_exists boolean;
3287 l_funded_amount number;
3288 l_total_amount_due number;
3289 l_amount_adjusted number;
3290 l_running_amount number;
3291 l_running_percent number;
3292 l_amount number;
3293 l_percent number;
3294 l_subsidy_rate number;
3295 l_loan_class varchar2(30);
3296 l_loan_type_id number;
3297 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
3298 adj_info xla_events_pub_pkg.t_event_source_info;
3299 inv_info xla_events_pub_pkg.t_event_source_info;
3300 l_adjustment_id number;
3301 l_customer_trx_id number;
3302 l_accounting_batch_id NUMBER;
3303 l_errbuf VARCHAR2(10000);
3304 l_retcode NUMBER;
3305 l_request_id NUMBER;
3306 l_legal_entity_id number;
3307 l_version number;
3308 l_error_counter number;
3309 l_event_array xla_events_pub_pkg.t_array_event_info;
3310 l_error_message varchar2(2000);
3311 l_invoice_number varchar2(100);
3312 l_entity_code varchar2(30);
3313 l_transactions_count number;
3314 l_entity_id number;
3315 l_source_id_int_1 number;
3316 l_upgrade_status varchar2(1);
3317 l_trx_number varchar2(100);
3318 l_clearing_total_amount_due number;
3319 l_receivable_total_amount_due number;
3320 l_transaction_number VARCHAR2(240);
3321 l_sob_id number;
3322 l_EVENT_SOURCE_INFO xla_events_pub_pkg.t_event_source_info;
3323
3324 /*------------------------------------------------------------------------+
3325 | Cursor Declarations |
3326 +-----------------------------------------------------------------------*/
3327
3328 -- cursor to establish the loan receivables accounts
3329 -- 11-16-2005 added reference to XLA_POST_ACCT_PROGS_B as per
3330 -- xla team instructions (noela, ayse)
3331 cursor C_ERS_LOAN_RECEIVABLE(p_loan_id number) is
3332 select sum(ael.entered_dr)
3333 ,ael.code_combination_id
3334 from ra_customer_trx_all inv
3335 ,xla_transaction_entities ent
3336 ,xla_ae_headers aeh
3337 ,xla_ae_lines ael
3338 where ent.application_id = 222
3339 and inv.customer_trx_id = ent.source_id_int_1
3340 and ent.entity_code = 'TRANSACTIONS'
3341 and ent.entity_id = aeh.entity_id
3342 and ent.ledger_id = aeh.ledger_id
3343 and aeh.ae_header_id = ael.ae_header_id
3344 and aeh.accounting_entry_status_code = 'F'
3345 and ael.accounting_class_code IN
3346 (select xaa.accounting_class_code
3347 from XLA_ACCT_CLASS_ASSGNS xaa
3348 ,XLA_ASSIGNMENT_DEFNS_B xad
3349 ,XLA_POST_ACCT_PROGS_B xpa
3350 where xaa.program_code = 'GET_RECEIVABLE_CCID'
3351 and xpa.program_code = xaa.program_code
3352 and xaa.program_code = xad.program_code
3353 and xad.assignment_code = xaa.assignment_code
3354 and xad.enabled_flag = 'Y')
3355 and inv.customer_trx_id in
3356 (select reference_id
3357 from lns_loan_lines lines
3358 where reference_type = 'RECEIVABLE'
3359 and end_date is null
3360 and loan_id = p_loan_id)
3361 group by ael.code_combination_id;
3362
3363 -- cursor to establish the loan clearing accounts
3364 -- 11-16-2005 added reference to XLA_POST_ACCT_PROGS_B as per
3365 -- xla team instructions (noela, ayse)
3366 cursor C_ERS_LOAN_CLEARING(p_loan_id number) is
3367 select sum(ael.entered_dr)
3368 ,ael.code_combination_id
3369 from ar_adjustments_all adj
3370 ,xla_transaction_entities ent
3371 ,xla_ae_headers aeh
3372 ,xla_ae_lines ael
3373 where ent.application_id = 222
3374 and adj.adjustment_id = ent.source_id_int_1
3375 and ent.entity_code = 'ADJUSTMENTS'
3376 and ent.entity_id = aeh.entity_id
3377 and ent.ledger_id = aeh.ledger_id
3378 and aeh.ae_header_id = ael.ae_header_id
3379 and aeh.accounting_entry_status_code = 'F'
3380 and ael.accounting_class_code in
3381 (select xaa.accounting_class_code
3382 from XLA_ACCT_CLASS_ASSGNS xaa
3383 ,XLA_ASSIGNMENT_DEFNS_B xad
3384 ,XLA_POST_ACCT_PROGS_B xpa
3385 where xaa.program_code = 'LNS_ADJUSTMENT_DEBIT' -- Bug#8231149
3386 and xpa.program_code = xaa.program_code
3387 and xaa.program_code = xad.program_code
3388 and xad.assignment_code = xaa.assignment_code
3389 and xad.enabled_flag = 'Y')
3390 and adj.adjustment_id in
3391 (select rec_adjustment_id
3392 from lns_loan_lines lines
3393 where reference_type = 'RECEIVABLE'
3394 and end_date is null
3395 and loan_id = p_loan_id)
3396 group by ael.code_combination_id;
3397
3398 -- use this to get the loan_class and type
3399 cursor c_loan_class(p_loan_id number) is
3400 select h.loan_class_code
3401 ,t.loan_type_id
3402 ,h.funded_amount
3403 ,h.legal_entity_id
3404 from lns_loan_headers_all h
3405 ,lns_loan_types t
3406 where h.loan_id = p_loan_id
3407 and h.loan_type_id = t.loan_type_id;
3408
3409 -- use this to get all adjustments to be processed for the loan
3410 cursor c_adj_ids (p_loan_id number) is
3411 select adj.adjustment_id
3412 ,adj.adjustment_number
3413 from ar_adjustments adj
3414 ,lns_loan_lines lines
3415 where lines.rec_adjustment_number = adj.adjustment_number
3416 and lines.end_date is null
3417 and lines.reference_type = 'RECEIVABLE'
3418 and lines.loan_id = p_loan_id;
3419
3420 -- use this to get all receivables to be processed for the loan
3421 cursor c_inv_ids(p_loan_id number) is
3422 select lines.reference_id
3423 ,lines.reference_number
3424 from lns_loan_lines lines
3425 where lines.end_date is null
3426 and lines.reference_type = 'RECEIVABLE'
3427 and lines.loan_id = p_loan_id;
3428
3429 -- cursor to update loan header
3430 cursor c_obj_vers(p_loan_id number) is
3431 select object_version_number
3432 from lns_loan_headers
3433 where loan_id = p_loan_id;
3434
3435 -- cursor to get documents and check upgrade status
3436 cursor c_get_loan_documents(p_loan_id number) is
3437 select lines.reference_id, trx.trx_number
3438 from lns_loan_lines lines
3439 ,ra_customer_trx trx
3440 where lines.reference_type = 'RECEIVABLE'
3441 and lines.end_date is null
3442 and lines.loan_id = p_loan_id
3443 and lines.reference_id = trx.customer_trx_id;
3444
3445 -- cursor to get accounting errors
3446 cursor c_acc_errors (p_loan_id number, p_accounting_batch_id number) is
3447 select xlt.transaction_number, xlt.entity_code, err.encoded_msg
3448 from xla_accounting_errors err
3449 ,xla_transaction_entities xlt
3450 where xlt.application_id = 222
3451 --and err.accounting_batch_id = nvl(p_accounting_batch_id, null)
3452 and xlt.entity_id = err.entity_id
3453 and xlt.entity_id in (select entity_id from xla_transaction_entities
3454 where application_id = 222
3455 and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
3456 and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))
3457 OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))));
3458 -- -----------------------------------------------------------------
3459 cursor c_entities(p_loan_id number) is
3460 select entity_id, entity_code, source_id_int_1, transaction_number
3461 from xla_transaction_entities
3462 where application_id = 222
3463 and entity_code IN ('TRANSACTIONS', 'ADJUSTMENTS')
3464 and ((source_id_int_1 in (select reference_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id)
3465 OR (source_id_int_1 in (select rec_adjustment_id from lns_loan_lines where end_date is null and reference_type = 'RECEIVABLE' and loan_id = p_loan_id))));
3466
3467 begin
3468
3469 SAVEPOINT defaultDistributions;
3470 l_api_name := 'defaultDistributions';
3471 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
3472 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
3473 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'p_loan_class_code = ' || p_loan_class_code);
3474
3475 -- Initialize message list IF p_init_msg_list is set to TRUE.
3476 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3477 FND_MSG_PUB.initialize;
3478 END IF;
3479
3480 -- Initialize API return status to SUCCESS
3481 x_return_status := FND_API.G_RET_STS_SUCCESS;
3482
3483 -- initialize variables
3484 l_distributions_count := 0;
3485 l_distributionsCatch_count := 0;
3486 l_total_distributions := 0;
3487 l_ers_distribution_amount := 0;
3488 i := 0;
3489 l_distributions_count := 0;
3490 l_distributionsCatch_count := 0;
3491 l_total_distributions := 0;
3492 l_orig_distribution_amount := 0;
3493 l_include_receivables := 'Y';
3494 l_sum := 0;
3495 l_funded_amount := 0;
3496 l_multifund_exists := 0;
3497 n := 0;
3498 l_total_percent := 0;
3499 l_total_receivable_amount := 0;
3500 l_adjustment_exists := false;
3501 l_running_amount := 0;
3502 l_running_percent := 0;
3503 l_total_amount_due := 0;
3504 l_subsidy_rate := 0;
3505 l_receivable_total_amount_due := 0;
3506 l_clearing_total_amount_due := 0;
3507
3508 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Opening cursor c_loan_class...');
3509 open c_loan_class(p_loan_id);
3510 fetch c_loan_class
3511 into l_loan_class
3512 ,l_loan_type_id
3513 ,l_funded_amount
3514 ,l_legal_entity_id;
3515 close c_loan_class;
3516
3517 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_loan_class = ' || l_loan_class);
3518 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_loan_type_id = ' || l_loan_type_id);
3519 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_funded_amount = ' || l_funded_amount);
3520 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_legal_entity_id = ' || l_legal_entity_id);
3521
3522 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateDefaultAccounting...');
3523 lns_distributions_pub.validateDefaultAccounting(p_loan_class => l_loan_class
3524 ,p_loan_type_id => l_loan_type_id
3525 ,p_init_msg_list => p_init_msg_list
3526 ,x_return_status => l_return_status
3527 ,x_msg_count => l_msg_count
3528 ,x_msg_data => l_msg_data);
3529
3530 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
3531 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3532 FND_MESSAGE.SET_NAME('LNS', 'LNS_DEFAULT_DIST_NOT_FOUND');
3533 FND_MSG_PUB.ADD;
3534 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3535 RAISE FND_API.G_EXC_ERROR;
3536 end if;
3537
3538 /* delete any rows for this loan before inheritance do not delete FEE_RECEIVABLE or FEE_INCOME rows*/
3539 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Deleting any existing accounting rows except FEE_RECEIVABLE or FEE_INCOME...');
3540 delete from lns_distributions
3541 where loan_id = p_loan_id
3542 and account_name in ('PRINCIPAL_RECEIVABLE', 'INTEREST_RECEIVABLE', 'INTEREST_INCOME', 'LOAN_RECEIVABLE', 'LOAN_CLEARING', 'LOAN_LIABILITY', 'LOAN_PAYABLE')
3543 and event_id is null;
3544 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Done');
3545
3546 -- first check if we are creating accounting for an ERS or DIRECT loan
3547 if p_loan_class_code = 'DIRECT' then
3548
3549
3550 /*
3551 -- Bug#6711399 Subsidy_rate defaulted from loanProduct to loan at the time of loanCreation
3552 -- in API LNS_LOAN_HEADER_PUB.do_create_loan.
3553
3554 -- we are creating accounting for DIRECT loan class
3555 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' fetching subsidy rate');
3556 begin
3557 open c_subsidy_rate(p_loan_id);
3558 fetch c_subsidy_rate into l_subsidy_rate;
3559 close c_subsidy_rate;
3560 exception
3561 when no_data_found then
3562 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_SUBSIDY_RATE');
3563 FND_MSG_PUB.ADD;
3564 RAISE FND_API.G_EXC_ERROR;
3565 end;
3566
3567 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' l_subsidy_rate ' || l_subsidy_rate );
3568
3569 open c_obj_vers(p_loan_id);
3570 fetch c_obj_vers into l_version;
3571 close c_obj_vers;
3572
3573 l_loan_header_rec.subsidy_rate := l_subsidy_rate;
3574 l_loan_header_rec.loan_id := p_loan_id;
3575 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || 'updating loan');
3576 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version
3577 ,P_LOAN_HEADER_REC => l_loan_header_rec
3578 ,P_INIT_MSG_LIST => p_init_msg_list
3579 ,X_RETURN_STATUS => l_return_status
3580 ,X_MSG_COUNT => l_msg_count
3581 ,X_MSG_DATA => l_msg_data);
3582 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' update loan status = ' || l_return_status);
3583 if l_return_status <> 'S' then
3584 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
3585 FND_MSG_PUB.ADD;
3586 RAISE FND_API.G_EXC_ERROR;
3587 end if;
3588 */
3589 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'DIRECT LOAN INHERITANCE');
3590
3591 -- we establish BILING only for this procedure
3592 -- inherit based on loan class + type ONLY
3593 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling defaultDistributionsCatch...');
3594 defaultDistributionsCatch(p_api_version => 1.0
3595 ,p_init_msg_list => p_init_msg_list
3596 ,p_commit => FND_API.G_FALSE
3597 ,p_loan_id => p_loan_id
3598 ,p_disb_header_id => null
3599 ,p_include_loan_receivables => 'Y'
3600 ,p_distribution_type => 'BILLING'
3601 ,x_distribution_tbl => l_distributionsCatch
3602 ,x_return_status => l_return_status
3603 ,x_msg_count => l_msg_count
3604 ,x_msg_data => l_msg_data);
3605 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
3606 if l_return_status <> 'S' then
3607 RAISE FND_API.G_EXC_ERROR;
3608 end if;
3609
3610 -- we establish the distributions for the first DISBURSEMENT only
3611 -- in order to process Budgetary Control Information
3612 -- in SLA Transaction Line Object
3613 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling create_DisbursementDistribs...');
3614 create_DisbursementDistribs(p_api_version => 1.0
3615 ,p_init_msg_list => p_init_msg_list
3616 ,p_commit => FND_API.G_FALSE
3617 ,p_loan_id => p_loan_id
3618 ,p_disb_header_id => null
3619 ,x_return_status => l_return_status
3620 ,x_msg_count => l_msg_count
3621 ,x_msg_data => l_msg_data);
3622 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
3623 if l_return_status <> 'S' then
3624 RAISE FND_API.G_EXC_ERROR;
3625 end if;
3626
3627 elsif p_loan_class_code = 'ERS' then
3628
3629 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'ERS LOAN INHERITANCE');
3630
3631 -- this switch is for the CatchAll Procedure
3632 l_include_receivables := 'N';
3633 l_ledger_details := lns_distributions_pub.getLedgerDetails;
3634 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'chart_of_accounts_id = ' || l_ledger_details.chart_of_accounts_id);
3635
3636 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Fetching documents to account...');
3637 open c_get_loan_documents(p_loan_id);
3638 loop
3639 fetch c_get_loan_documents into l_source_id_int_1, l_trx_number;
3640 exit when c_get_loan_documents%notfound;
3641
3642 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_source_id_int_1 = ' || l_source_id_int_1);
3643 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_trx_number = ' || l_trx_number);
3644
3645 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling arp_acct_event_pkg.upgrade_status_per_doc...');
3646 -- check for upgrade status bug#4872154
3647 arp_acct_event_pkg.upgrade_status_per_doc(p_init_msg_list => p_init_msg_list
3648 ,p_entity_code => l_entity_code
3649 ,p_source_int_id => l_source_id_int_1
3650 ,x_upgrade_status => l_upgrade_status
3651 ,x_return_status => l_return_status
3652 ,x_msg_count => l_msg_count
3653 ,x_msg_data => l_msg_data);
3654 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
3655 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_upgrade_status = ' || l_upgrade_status);
3656
3657 if l_return_status <> 'S' then
3658 FND_MESSAGE.SET_NAME('LNS', 'LNS_CHK_UPG_FAIL');
3659 FND_MESSAGE.SET_TOKEN('DOC_NUM', l_trx_number);
3660 FND_MSG_PUB.ADD;
3661 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3662 RAISE FND_API.G_EXC_ERROR;
3663 else
3664 if l_upgrade_status <> 'Y' then
3665 FND_MESSAGE.SET_NAME('LNS', 'LNS_INVALID_TRX');
3666 FND_MESSAGE.SET_TOKEN('DOC_NUM', l_trx_number);
3667 FND_MSG_PUB.ADD;
3668 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3669 RAISE FND_API.G_EXC_ERROR;
3670 end if;
3671 end if;
3672
3673 end loop;
3674 close c_get_loan_documents;
3675
3676 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Fetching entities xla_transaction_entities...');
3677 l_transactions_count := 0;
3678 open c_entities(p_loan_id);
3679 loop
3680 fetch c_entities into l_entity_id, l_entity_code, l_source_id_int_1, l_transaction_number;
3681 exit when c_entities%notfound;
3682
3683 l_transactions_count := l_transactions_count + 1;
3684 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Entity ' || l_transactions_count);
3685 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_entity_id = ' || l_entity_id);
3686 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_entity_code = ' || l_entity_code);
3687 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_source_id_int_1 = ' || l_source_id_int_1);
3688 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_transaction_number = ' || l_transaction_number);
3689
3690 insert into XLA_ACCT_PROG_DOCS_GT (entity_id) VALUES (l_entity_id);
3691 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted into XLA_ACCT_PROG_DOCS_GT');
3692
3693 end loop;
3694 close c_entities ;
3695
3696 select count(1) into l_transactions_count
3697 from XLA_ACCT_PROG_DOCS_GT;
3698 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Inserted transaction_entities = ' || l_transactions_count);
3699
3700 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling XLA_ACCOUNTING_PUB_PKG.accounting_program_doc_batch...');
3701 XLA_ACCOUNTING_PUB_PKG.accounting_program_doc_batch(p_application_id => 222
3702 ,p_accounting_mode => 'F'
3703 ,p_gl_posting_flag => 'N'
3704 ,p_accounting_batch_id => l_accounting_batch_id
3705 ,p_errbuf => l_errbuf
3706 ,p_retcode => l_retcode);
3707 logMessage(FND_LOG.level_statement, G_PKG_NAME, ' l_retcode = ' || l_retcode);
3708 logMessage(FND_LOG.level_statement, G_PKG_NAME, ' l_accounting_batch_id = ' || l_accounting_batch_id);
3709
3710 if l_retcode <> 0 then
3711
3712 logMessage(FND_LOG.level_unexpected, G_PKG_NAME, 'Online accounting failed with error: ' || l_errbuf);
3713
3714 /* query XLA_ACCOUNTING_ERRORS */
3715 l_error_counter := 0;
3716 open c_acc_errors(p_loan_id, l_accounting_batch_id);
3717
3718 LOOP
3719
3720 fetch c_acc_errors into
3721 l_invoice_number,
3722 l_entity_code,
3723 l_error_message;
3724 exit when c_acc_errors%NOTFOUND;
3725
3726 l_error_counter := l_error_counter + 1;
3727
3728 if l_error_counter = 1 then
3729 FND_MESSAGE.SET_NAME('LNS', 'LNS_ONLINE_ACCOUNTING_FAILED');
3730 FND_MSG_PUB.Add;
3731 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3732 end if;
3733
3734 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACC_DOC_FAIL');
3735 FND_MESSAGE.SET_TOKEN('DOC_NUM', l_invoice_number);
3736 FND_MESSAGE.SET_TOKEN('DOC_TYPE', l_entity_code);
3737 FND_MESSAGE.SET_TOKEN('ACC_ERR', l_error_message);
3738 FND_MSG_PUB.Add;
3739 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3740
3741 END LOOP;
3742
3743 close c_acc_errors;
3744
3745 RAISE FND_API.G_EXC_ERROR;
3746 else
3747 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Online accounting SUCCESS! ');
3748 end if;
3749
3750 -- get the swap segment value
3751 l_natural_account_rec := getNaturalSwapAccount(p_loan_id);
3752 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Swap natural account with ' || l_natural_account_rec);
3753
3754 -- Get natural account segment number
3755 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling FND_FLEX_APIS.GET_QUALIFIER_SEGNUM...');
3756 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(appl_id => 101
3757 ,key_flex_code => 'GL#'
3758 ,structure_number=> l_ledger_details.chart_of_accounts_id
3759 ,flex_qual_name => 'GL_ACCOUNT'
3760 ,segment_number => l_nat_acct_seg_number))
3761 THEN
3762 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_NATURAL_ACCOUNT_SEGMENT');
3763 FND_MSG_PUB.ADD;
3764 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3765 RAISE FND_API.G_EXC_ERROR;
3766 END IF;
3767 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Natural acct segment = ' || l_nat_acct_seg_number);
3768
3769 -- here we establish the loan clearing first
3770 -- if adjustment activity is found in XLA then we take amounts, cc_ids from XLA tables for both CLEARING and RECEIVABLES
3771 Begin
3772 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Opening cursor C_ERS_LOAN_CLEARING...');
3773 i := 0;
3774 open C_ERS_LOAN_CLEARING(p_loan_id);
3775 Loop
3776 -- reintialize these
3777 l_code_combination_id := null;
3778 l_ers_distribution_amount := 0;
3779
3780 fetch C_ERS_LOAN_CLEARING into l_ers_distribution_amount, l_code_combination_id;
3781 EXIT WHEN C_ERS_LOAN_CLEARING%NOTFOUND;
3782
3783 l_clearing_total_amount_due := l_clearing_total_amount_due + l_ers_distribution_amount;
3784
3785 -- bug #4313925 --
3786 l_adjustment_exists := true;
3787 i := i + 1;
3788
3789 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Loan Clearing Record ' || i);
3790 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_ers_distribution_amount = ' || l_ers_distribution_amount);
3791 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_code_combination_id = ' || l_code_combination_id);
3792 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_clearing_total_amount_due = ' || l_clearing_total_amount_due);
3793
3794 l_distributionsCLEAR_ORIG(i).line_type := 'CLEAR';
3795 l_distributionsCLEAR_ORIG(i).account_name := 'LOAN_CLEARING';
3796 l_distributionsCLEAR_ORIG(i).code_combination_id := l_code_combination_id;
3797 l_distributionsCLEAR_ORIG(i).account_type := 'CR';
3798 l_distributionsCLEAR_ORIG(i).distribution_amount := l_ers_distribution_amount;
3799 l_distributionsCLEAR_ORIG(i).distribution_percent := null;
3800 l_distributionsCLEAR_ORIG(i).distribution_type := 'ORIGINATION';
3801
3802 end loop; -- loan clearing loop
3803 exception
3804 when others then
3805 --logMessage(FND_LOG.LEVEL_UNEX, G_PKG_NAME, 'Failed to inherit receivables distributions');
3806 FND_MESSAGE.SET_NAME('LNS', 'LNS_INHERIT_DIST_NOT_FOUND');
3807 FND_MSG_PUB.ADD;
3808 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3809 RAISE FND_API.G_EXC_ERROR;
3810 end;
3811
3812 --logMessage(FND_LOG.level_statement, G_PKG_NAME, 'After loan clearing lines calculated. total amount due = ' || l_total_amount_due);
3813
3814 -- if the adjustment exists in PSA table it means loan is approved and adjustment was created for receivables
3815 i := 0;
3816 if l_adjustment_exists then
3817 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'ACCOUNTED ADJUSTMENT EXISTS');
3818 Begin
3819 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Opening cursor C_ERS_LOAN_RECEIVABLE...');
3820 open C_ERS_LOAN_RECEIVABLE(p_loan_id);
3821 Loop
3822 -- reintialize these
3823 l_code_combination_id := null;
3824 l_code_combination_id_new_rec := null;
3825 l_ers_distribution_amount := 0;
3826
3827 fetch C_ERS_LOAN_RECEIVABLE into l_ers_distribution_amount, l_code_combination_id;
3828 EXIT WHEN C_ERS_LOAN_RECEIVABLE%NOTFOUND;
3829
3830 l_receivable_total_amount_due := l_receivable_total_amount_due + l_ers_distribution_amount;
3831
3832 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Record:');
3833 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_ers_distribution_amount = ' || l_ers_distribution_amount);
3834 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_code_combination_id = ' || l_code_combination_id);
3835 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_receivable_total_amount_due = ' || l_receivable_total_amount_due);
3836
3837 -- here we need to rebuild the code_Combination_id as per swapping rules
3838 -- replace the natual account segement with the natural account segment found in the set-up/configuration
3839 if l_ers_distribution_amount > 0 then
3840 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling swap_code_combination...');
3841 l_code_combination_id_new_rec :=
3842 swap_code_combination(p_chart_of_accounts_id => l_ledger_details.chart_of_accounts_id
3843 ,p_original_cc_id => l_code_combination_id
3844 ,p_swap_segment_number => l_nat_acct_seg_number
3845 ,p_swap_segment_value => l_natural_account_rec);
3846
3847 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_code_combination_id_new_rec = ' || l_code_combination_id_new_Rec);
3848
3849 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Assigning distributions...');
3850
3851 -- we need 2 records PER receivable account distribution
3852 i := i + 1;
3853 l_distributionsREC_ORIG(i).line_type := 'ORIG';
3854 l_distributionsREC_ORIG(i).account_name := 'LOAN_RECEIVABLE';
3855 l_distributionsREC_ORIG(i).code_combination_id := l_code_combination_id_new_rec;
3856 l_distributionsREC_ORIG(i).account_type := 'DR';
3857 l_distributionsREC_ORIG(i).distribution_amount := l_ers_distribution_amount;
3858 l_distributionsREC_ORIG(i).distribution_type := 'ORIGINATION';
3859
3860 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Added LOAN_RECEIVABLE FOR ORIGINATION ' || l_code_combination_id_new_rec);
3861 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsREC_ORIG.count = ' || l_distributionsREC_ORIG.count);
3862
3863 l_distributionsREC_BILL(i).line_type := 'PRIN';
3864 l_distributionsREC_BILL(i).account_name := 'LOAN_RECEIVABLE';
3865 l_distributionsREC_BILL(i).code_combination_id := l_code_combination_id_new_rec;
3866 l_distributionsREC_BILL(i).account_type := 'CR';
3867 l_distributionsREC_BILL(i).distribution_amount := null;
3868 l_distributionsREC_BILL(i).distribution_type := 'BILLING';
3869
3870 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Added LOAN_RECEIVABLE FOR BILLING ' || l_code_combination_id_new_rec);
3871 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsREC_BILL.count = ' || l_distributionsREC_BILL.count);
3872
3873 l_sum := l_sum + l_ers_distribution_amount;
3874 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_sum = ' || l_sum);
3875 end if;
3876
3877 end loop;
3878
3879 close C_ERS_LOAN_RECEIVABLE;
3880
3881 exception
3882 when others then
3883 -- logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, 'failed to inherit receivables distributions');
3884 FND_MESSAGE.SET_NAME('LNS', 'LNS_INHERIT_DIST_NOT_FOUND');
3885 FND_MSG_PUB.ADD;
3886 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
3887 RAISE FND_API.G_EXC_ERROR;
3888 end;
3889
3890 else
3891 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'NO ACCOUNTED ADJUSTMENT EXISTS');
3892 end if;
3893
3894 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsREC_BILL.count = ' || l_distributionsREC_BILL.count);
3895 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsREC_ORIG.count = ' || l_distributionsREC_ORIG.count);
3896 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsCLEAR_ORIG.count = ' || l_distributionsCLEAR_ORIG.count);
3897 --logMessage(FND_LOG.level_statement, G_PKG_NAME, 'TOTAL AMOUNT DUE = ' || l_total_amount_due);
3898 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_funded_amount = ' || l_funded_amount);
3899
3900 -- this logic is copied from PSA 04-19-2005
3901 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'CALCULATING %AGES FOR LOANS RECEIVABLE...');
3902
3903 for k in 1..l_distributionsREC_ORIG.count loop
3904
3905 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Iteration ' || k);
3906 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calculating %ages for a loan with ERS Adjustments...');
3907
3908 if k <> l_distributionsREC_ORIG.count then
3909 -- use the adjustment amounts to calculate percentages -- this ensures percents but not cc_ids
3910 -- this may or may not be an offending line karamach
3911 l_percent := round(l_distributionsCLEAR_ORIG(k).distribution_amount / l_clearing_total_amount_due * 100,4);
3912 --l_percent := round(l_distributionsREC_ORIG(k).distribution_amount / l_receivable_total_amount_due * 100,4);
3913 l_distributionsREC_ORIG(k).distribution_percent := l_percent;
3914 l_distributionsREC_BILL(k).distribution_percent := l_percent;
3915 -- ensure this amount is accurate it will get inserted into lns_distributions for loans booking
3916 l_distributionsREC_ORIG(k).distribution_amount := l_percent / 100 * l_funded_amount;
3917 else
3918 -- last row ensure that amounts = 100% and total = funded amount of loan
3919 l_percent := 100 - l_running_percent;
3920 l_distributionsREC_ORIG(k).distribution_percent := l_percent;
3921 l_distributionsREC_BILL(k).distribution_percent := l_percent;
3922 l_distributionsREC_ORIG(k).distribution_amount := l_funded_amount - l_running_amount;
3923 end if;
3924 l_running_amount := l_running_amount + l_distributionsREC_ORIG(k).distribution_amount;
3925 l_running_percent := l_running_percent + l_percent;
3926
3927 l_percent := 0;
3928 l_amount := 0;
3929
3930 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'distribution_percent = ' || l_distributionsREC_ORIG(k).distribution_percent);
3931 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'distribution_amount = ' || l_distributionsREC_ORIG(k).distribution_amount);
3932 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_running_percent = ' || l_running_percent);
3933 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_running_amount = ' || l_running_amount);
3934
3935 end loop;
3936
3937 l_running_percent := 0;
3938 l_running_amount := 0;
3939
3940 -- this logic is copied from PSA 04-19-2005
3941 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'CALCULATING %AGES FOR LOANS CLEARING...');
3942
3943 for k in 1..l_distributionsCLEAR_ORIG.count loop
3944
3945 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'iteration ' || k);
3946
3947 if k <> l_distributionsCLEAR_ORIG.count then
3948 l_percent := round(l_distributionsCLEAR_ORIG(k).distribution_amount / l_clearing_total_amount_due * 100,4);
3949 l_distributionsCLEAR_ORIG(k).distribution_percent := l_percent;
3950 l_distributionsCLEAR_ORIG(k).distribution_amount := l_percent / 100 * l_funded_amount;
3951 else
3952 -- last row ensure that amounts = 100% and total = funded amount of loan
3953 l_percent := 100 - l_running_percent;
3954 l_distributionsCLEAR_ORIG(k).distribution_percent := 100 - l_running_percent;
3955 l_distributionsCLEAR_ORIG(k).distribution_amount := l_funded_amount - l_running_amount;
3956 end if;
3957 l_running_percent := l_running_percent + l_percent;
3958 l_running_amount := l_running_amount + l_distributionsCLEAR_ORIG(k).distribution_amount;
3959
3960 l_percent := 0;
3961 l_amount := 0;
3962
3963 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'distribution_percent = ' || l_distributionsCLEAR_ORIG(k).distribution_percent);
3964 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'distribution_amount = ' || l_distributionsCLEAR_ORIG(k).distribution_amount);
3965 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_running_percent = ' || l_running_percent);
3966 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_running_amount = ' || l_running_amount);
3967
3968 end loop;
3969
3970 -- inherit remaining account_names based on loan class + type for
3971 -- principal / interest receivable, interest income
3972 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling defaultDistributionsCatch...');
3973 defaultDistributionsCatch(p_api_version => 1.0
3974 ,p_init_msg_list => FND_API.G_FALSE
3975 ,p_commit => FND_API.G_FALSE
3976 ,p_loan_id => p_loan_id
3977 ,p_disb_header_id => null
3978 ,p_include_loan_receivables => l_include_receivables
3979 ,p_distribution_type => null
3980 ,x_distribution_tbl => l_distributionsCatch
3981 ,x_return_status => l_return_status
3982 ,x_msg_count => l_msg_count
3983 ,x_msg_data => l_msg_data);
3984 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
3985 if l_return_status <> 'S' then
3986 RAISE FND_API.G_EXC_ERROR;
3987 end if;
3988
3989 End if; --loan class
3990
3991 l_distributionsCatch_count := l_distributionsCatch.count;
3992 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsCatch_count = ' || l_distributionsCatch_count);
3993
3994 l_total_distributions := l_distributions_count + l_distributionsCatch_count;
3995
3996 n := 0;
3997 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Copying l_distributionsREC_ORIG to l_distributionsALL...');
3998 for j in 1..l_distributionsREC_ORIG.count loop
3999 n := n + 1;
4000 l_distributionsALL(n) := l_distributionsREC_ORIG(j);
4001 end loop;
4002 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsALL.count = ' || l_distributionsALL.count);
4003
4004 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Copying l_distributionsCLEAR_ORIG to l_distributionsALL...');
4005 for j in 1..l_distributionsCLEAR_ORIG.count loop
4006 n := n + 1;
4007 l_distributionsALL(n) := l_distributionsCLEAR_ORIG(j);
4008 end loop;
4009 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsALL.count = ' || l_distributionsALL.count);
4010
4011 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Copying l_distributionsREC_BILL to l_distributionsALL...');
4012 for j in 1..l_distributionsREC_BILL.count loop
4013 n := n + 1;
4014 l_distributionsALL(n) := l_distributionsREC_BILL(j);
4015 end loop;
4016 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsALL.count = ' || l_distributionsALL.count);
4017
4018 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Copying l_distributionsCatch to l_distributionsALL...');
4019 for j in 1..l_distributionsCatch.count
4020 loop
4021 n := n + 1;
4022 l_distributionsALL(n) := l_distributionsCatch(j);
4023 end loop;
4024 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsALL.count = ' || l_distributionsALL.count);
4025
4026 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling do_insert_distributions...');
4027 do_insert_distributions(p_distributions_tbl => l_distributionsALL
4028 ,p_loan_id => p_loan_id);
4029
4030 -- validate the accounting rows here
4031 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling lns_distributions_pub.validateAccounting...');
4032 lns_distributions_pub.validateAccounting(p_loan_id => p_loan_id
4033 ,p_init_msg_list => p_init_msg_list
4034 ,x_return_status => l_return_status
4035 ,x_msg_count => l_msg_count
4036 ,x_msg_data => l_msg_data);
4037
4038 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4039 if l_return_status <> 'S' then
4040 FND_MESSAGE.SET_NAME('LNS', 'LNS_DEFAULT_DIST_NOT_FOUND');
4041 FND_MSG_PUB.ADD;
4042 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4043 RAISE FND_API.G_EXC_ERROR;
4044 end if;
4045
4046 IF FND_API.to_Boolean(p_commit) THEN
4047 COMMIT WORK;
4048 END IF;
4049
4050 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4051 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
4052
4053 EXCEPTION
4054
4055 WHEN FND_API.G_EXC_ERROR THEN
4056 ROLLBACK TO defaultDistributions;
4057 x_return_status := FND_API.G_RET_STS_ERROR;
4058 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4059 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4060
4061 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4062 ROLLBACK TO defaultDistributions;
4063 x_return_status := FND_API.G_RET_STS_ERROR;
4064 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4065 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4066
4067 WHEN OTHERS THEN
4068 ROLLBACK TO defaultDistributions;
4069 x_return_status := FND_API.G_RET_STS_ERROR;
4070 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4071 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4072
4073 end defaultDistributions;
4074
4075 /*=========================================================================
4076 || PUBLIC PROCEDURE onlineAccounting
4077 ||
4078 || DESCRIPTION
4079 || This procedure generates online-Accounting
4080 ||
4081 || PSEUDO CODE/LOGIC
4082 ||
4083 || PARAMETERS
4084 || Parameter: p_loan_id => loan_ID
4085 || p_disb_header_id => null for ERS loan,
4086 || disb_header_id for DIRECT loan
4087 ||
4088 || Return value: Standard S = Success E = Error U = Unexpected
4089 ||
4090 || Source Tables:
4091 ||
4092 || Target Tables:
4093 ||
4094 || KNOWN ISSUES
4095 ||
4096 || NOTES
4097 ||
4098 || MODIFICATION HISTORY
4099 || Date Author Description of Changes
4100 || 08-25-2005 raverma Created
4101 *=======================================================================*/
4102 procedure onlineAccounting(p_loan_id IN NUMBER
4103 ,p_init_msg_list IN VARCHAR2
4104 ,p_accounting_mode IN VARCHAR2
4105 ,p_transfer_flag IN VARCHAR2
4106 ,p_offline_flag IN VARCHAR2
4107 ,p_gl_posting_flag IN VARCHAR2
4108 ,x_return_status OUT NOCOPY VARCHAR2
4109 ,x_msg_count OUT NOCOPY NUMBER
4110 ,x_msg_data OUT NOCOPY VARCHAR2)
4111
4112 is
4113 l_legal_entity_id number;
4114 l_accounting_batch_id NUMBER;
4115 l_errbuf VARCHAR2(500);
4116 l_retcode NUMBER;
4117 l_api_name varchar2(25);
4118 l_loan_class varchar2(30);
4119 l_transactions_count number;
4120 l_error_counter number;
4121 l_error_message varchar2(2000);
4122 l_invoice_number varchar2(100);
4123 l_entity_code varchar2(30);
4124
4125 cursor c_loan_info(p_loan_id number) is
4126 select h.legal_entity_id
4127 ,h.loan_class_code
4128 from lns_loan_headers h
4129 where h.loan_id = p_loan_id;
4130
4131 -- this is only for loans entities
4132 cursor c_acc_errors (p_loan_id number, p_accounting_batch_id number) is
4133 select xlt.transaction_number, xlt.entity_code, err.encoded_msg
4134 from xla_accounting_errors err
4135 ,xla_Transaction_entities xlt
4136 where xlt.application_id = 206
4137 and err.accounting_batch_id = p_accounting_batch_id
4138 and err.entity_id = xlt.entity_id
4139 and xlt.entity_id in (select entity_id from xla_transaction_entities
4140 where application_id = 206
4141 and entity_code = 'LOANS'
4142 and source_id_int_1 = p_loan_id
4143 and source_id_int_2 IN
4144 (select disb.disb_header_id
4145 from lns_disb_headers dh
4146 ,lns_distributions disb
4147 where disb.loan_id = p_loan_id
4148 and disb.disb_header_id = dh.disb_header_id
4149 and disb.account_name = 'LOAN_RECEIVABLE'
4150 and dh.status = 'FULLY_FUNDED'
4151 and not exists
4152 (select 'X'
4153 from xla_events xle
4154 ,XLA_TRANSACTION_ENTITIES XLEE
4155 ,xla_ae_headers aeh
4156 where XLE.application_id = 206
4157 and XLE.event_id = disb.event_id
4158 and XLE.entity_id = xlee.entity_id
4159 and XLEE.source_id_int_1 = dh.loan_id
4160 and XLEE.source_id_int_2 = dh.disb_header_id
4161 and xlee.entity_id = aeh.entity_id
4162 and xlee.ledger_id = aeh.ledger_id
4163 and aeh.accounting_entry_status_code = 'F'
4164 and xlee.entity_code = 'LOANS')
4165 OR source_id_int_2 = -1));
4166
4167 begin
4168
4169 SAVEPOINT onlineAccounting;
4170 l_api_name := 'onlineAccounting';
4171
4172 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - BEGIN');
4173 -- Initialize API return status to SUCCESS
4174 x_return_status := FND_API.G_RET_STS_SUCCESS;
4175 -- Initialize message list IF p_init_msg_list is set to TRUE.
4176 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4177 FND_MSG_PUB.initialize;
4178 END IF;
4179
4180 open c_loan_info(p_loan_id);
4181 fetch c_loan_info into l_legal_entity_id, l_loan_class;
4182 close c_loan_info;
4183
4184 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'running on-line accounting for loan_id = ' || p_loan_id);
4185 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_accounting_mode = ' || p_accounting_mode);
4186 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_transfer_flag = ' || p_transfer_flag);
4187 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_gl_posting_flag = ' || p_gl_posting_flag);
4188 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'p_offline_flag = ' || p_offline_flag);
4189
4190 if l_loan_class = 'ERS' then
4191
4192 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'ERS loan accounting');
4193 -- at this point in time, the INVOICES and ADJUSTMENTS MUST have already been accounted for
4194 insert into XLA_ACCT_PROG_DOCS_GT
4195 (entity_id)
4196 select entity_id from xla_transaction_entities
4197 where application_id = 206
4198 and entity_code = 'LOANS'
4199 and source_id_int_1 = p_loan_id
4200 and source_id_int_2 = -1;
4201
4202 else
4203
4204 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'DIRECT loan accounting');
4205
4206 -- can we make join thru lns_distributions
4207 insert into XLA_ACCT_PROG_DOCS_GT
4208 (entity_id)
4209 select entity_id from xla_transaction_entities
4210 where application_id = 206
4211 and entity_code = 'LOANS'
4212 and source_id_int_1 = p_loan_id
4213 and source_id_int_2 in (select disb.disb_header_id
4214 from lns_disb_headers dh
4215 ,lns_distributions disb
4216 where disb.loan_id = p_loan_id
4217 and disb.disb_header_id = dh.disb_header_id
4218 and disb.account_name = 'LOAN_RECEIVABLE'
4219 and dh.status = 'FULLY_FUNDED');
4220 -- and not exists
4221 -- (select 'X'
4222 -- from xla_events xle
4223 -- ,XLA_TRANSACTION_ENTITIES XLEE
4224 -- ,xla_ae_headers aeh
4225 -- where XLE.application_id = 206
4226 -- and XLE.event_id = disb.event_id
4227 -- and XLE.entity_id = xlee.entity_id
4228 -- and XLEE.source_id_int_1 = dh.loan_id
4229 -- and XLEE.source_id_int_2 = dh.disb_header_id
4230 -- and xlee.entity_id = aeh.entity_id
4231 -- and xlee.ledger_id = aeh.ledger_id
4232 -- and aeh.accounting_entry_status_code = 'F'
4233 -- and xlee.entity_code = 'LOANS'));
4234 end if;
4235
4236 select count(1) into l_transactions_count
4237 from XLA_ACCT_PROG_DOCS_GT;
4238 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'inserted transaction_entities ' || l_transactions_count);
4239 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'calling XLA_ACCOUNTING_PUB_PKG.accounting_program_doc_batch ');
4240 XLA_ACCOUNTING_PUB_PKG.accounting_program_doc_batch(p_application_id => 206
4241 ,p_accounting_mode => p_accounting_mode
4242 ,p_gl_posting_flag => p_gl_posting_flag
4243 ,p_accounting_batch_id => l_accounting_batch_id
4244 ,p_errbuf => l_errbuf
4245 ,p_retcode => l_retcode);
4246 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_retcode = ' || l_retcode);
4247 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_accounting_batch_id = ' || l_accounting_batch_id);
4248
4249 if l_retcode = 0 then
4250
4251 --FND_MESSAGE.SET_NAME('XLA', 'XLA_ONLINE_ACCT_SUCCESS');
4252 --FND_MSG_PUB.Add;
4253 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'online accounting SUCCESS! ');
4254
4255 elsif l_retcode = 2 then
4256
4257 FND_MESSAGE.SET_NAME('XLA', 'XLA_ONLINE_ACCTG_ERROR');
4258 FND_MSG_PUB.Add;
4259 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4260 RAISE FND_API.G_EXC_ERROR;
4261
4262 elsif l_retcode = 1 then
4263
4264 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'online accounting failed ' || l_errbuf);
4265 /* query XLA_ACCOUNTING_ERRORS */
4266 l_error_counter := 0;
4267 open c_acc_errors(p_loan_id, l_accounting_batch_id);
4268
4269 LOOP
4270
4271 fetch c_acc_errors into
4272 l_invoice_number,
4273 l_entity_code,
4274 l_error_message;
4275 exit when c_acc_errors%NOTFOUND;
4276
4277 l_error_counter := l_error_counter + 1;
4278
4279 if l_error_counter = 1 then
4280 FND_MESSAGE.SET_NAME('XLA', 'XLA_ONLINE_ACCT_WARNING');
4281 FND_MSG_PUB.Add;
4282 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4283 end if;
4284
4285 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACC_DOC_FAIL');
4286 FND_MESSAGE.SET_TOKEN('DOC_NUM', l_invoice_number);
4287 FND_MESSAGE.SET_TOKEN('DOC_TYPE', l_entity_code);
4288 FND_MESSAGE.SET_TOKEN('ACC_ERR', l_error_message);
4289 FND_MSG_PUB.Add;
4290 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4291
4292 END LOOP;
4293
4294 close c_acc_errors;
4295
4296 RAISE FND_API.G_EXC_ERROR;
4297 end if;
4298
4299 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4300 commit;
4301 logMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME, l_api_name || ' - END');
4302
4303 EXCEPTION
4304
4305 WHEN FND_API.G_EXC_ERROR THEN
4306 ROLLBACK TO onlineAccounting;
4307 x_return_status := FND_API.G_RET_STS_ERROR;
4308 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4309 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4310
4311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4312 ROLLBACK TO onlineAccounting;
4313 x_return_status := FND_API.G_RET_STS_ERROR;
4314 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4315 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4316
4317 WHEN OTHERS THEN
4318 ROLLBACK TO onlineAccounting;
4319 x_return_status := FND_API.G_RET_STS_ERROR;
4320 logMessage(FND_LOG.LEVEL_ERROR, G_PKG_NAME, sqlerrm);
4321 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4322
4323 end onlineAccounting;
4324
4325
4326 /*=========================================================================
4327 || PUBLIC PROCEDURE LNS_ACCOUNTING_CONCUR
4328 ||
4329 || DESCRIPTION
4330 || This procedure generates generates ERS distributions
4331 ||
4332 || PSEUDO CODE/LOGIC
4333 ||
4334 || PARAMETERS
4335 || Parameter: p_loan_id => p_loan_ID
4336 ||
4337 || Return value: Standard CP parameters
4338 ||
4339 || Source Tables:
4340 ||
4341 || Target Tables:
4342 ||
4343 || KNOWN ISSUES
4344 ||
4345 || NOTES
4346 ||
4347 || MODIFICATION HISTORY
4348 || Date Author Description of Changes
4349 || 01-05-2006 raverma Created
4350 || 23-Dec-2008 mbolli Changed the param name from loan_id
4351 || to p_loan_id
4352 *=======================================================================*/
4353 PROCEDURE LNS_ACCOUNTING_CONCUR(ERRBUF OUT NOCOPY VARCHAR2
4354 ,RETCODE OUT NOCOPY VARCHAR2
4355 ,P_LOAN_ID IN NUMBER)
4356 IS
4357 l_loan_class_code varchar2(30);
4358 l_gl_date date;
4359 l_api_name varchar2(30);
4360 l_msg_count NUMBER;
4361 l_msg_data VARCHAR2(2000);
4362 l_return_Status VARCHAR2(1);
4363 x_event_id number;
4364 l_object_version_number number;
4365 l_loan_header_rec LNS_LOAN_HEADER_PUB.LOAN_HEADER_REC_TYPE;
4366 l_return boolean;
4367 l_do_billing number;
4368 l_last_api_called varchar2(100);
4369 l_request_id number;
4370 l_org_id number;
4371 l_xml_output BOOLEAN;
4372 l_iso_language FND_LANGUAGES.iso_language%TYPE;
4373 l_iso_territory FND_LANGUAGES.iso_territory%TYPE;
4374
4375 cursor c_loan_info (c_loan_id number) is
4376 select loan_class_code
4377 ,gl_date
4378 ,OBJECT_VERSION_NUMBER
4379 ,org_id
4380 from lns_loan_headers
4381 where loan_id = c_loan_id;
4382
4383 CURSOR do_billing_cur(C_LOAN_ID number) IS
4384 select nvl(count(1),0)
4385 from lns_fee_assignments
4386 where begin_installment_number = 0
4387 and end_installment_number = 0
4388 and end_date_active is null
4389 and billing_option = 'ORIGINATION'
4390 and loan_id = C_LOAN_ID;
4391
4392
4393 begin
4394
4395 l_api_name := 'LNS_ACCOUNTING_CONCUR';
4396
4397 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Generate Distributions process has started');
4398
4399 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'LOAN_ID = ' || P_LOAN_ID);
4400 if P_LOAN_ID is null then
4401
4402 -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: Loan must be set.');
4403 FND_MESSAGE.SET_NAME('LNS', 'LNS_NO_LOAN');
4404 FND_MSG_PUB.Add;
4405 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4406 RAISE FND_API.G_EXC_ERROR;
4407
4408 end if;
4409
4410 open c_loan_info(P_LOAN_ID);
4411 fetch c_loan_info into l_loan_class_code, l_gl_date, l_object_version_number, l_org_id;
4412 close c_loan_info;
4413 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_loan_class_code = ' || l_loan_class_code);
4414 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_gl_date = ' || l_gl_date);
4415 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_object_version_number = ' || l_object_version_number);
4416 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_org_id = ' || l_org_id);
4417
4418
4419 IF ((l_loan_class_code IS NULL) OR (l_loan_class_code <> 'ERS')) THEN
4420 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, l_api_name || ' - Only works for ERS loans.');
4421 return;
4422 END IF;
4423
4424 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling LNS_XLA_EVENTS.create_event...');
4425 LNS_XLA_EVENTS.create_event(p_loan_id => P_LOAN_ID
4426 ,p_disb_header_id => -1
4427 ,p_event_type_code => 'APPROVED'
4428 ,p_event_date => l_gl_date
4429 ,p_event_status => 'U'
4430 ,p_init_msg_list => fnd_api.g_false
4431 ,p_commit => fnd_api.g_false
4432 ,p_bc_flag => 'N'
4433 ,x_event_id => x_event_id
4434 ,x_return_status => l_return_status
4435 ,x_msg_count => l_msg_count
4436 ,x_msg_data => l_msg_data);
4437 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'event_id = ' || x_event_id);
4438 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4439
4440 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4441 FND_MESSAGE.SET_NAME('LNS', 'LNS_ACCOUNTING_EVENT_ERROR');
4442 FND_MSG_PUB.ADD;
4443 --l_last_api_called := 'LNS_XLA_EVENTS.create_event';
4444 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4445 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4446 END IF;
4447
4448
4449
4450 -- we should do online accounting in batch mode here
4451 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Calling Lns_distributions_pub.defaultDistributions...');
4452 Lns_distributions_pub.defaultDistributions(p_api_version => 1.0
4453 ,p_init_msg_list => FND_API.G_TRUE
4454 ,p_commit => FND_API.G_FALSE
4455 ,p_loan_id => P_LOAN_ID
4456 ,p_loan_class_code => l_loan_class_code
4457 ,x_return_status => l_return_status
4458 ,x_msg_count => l_msg_count
4459 ,x_msg_data => l_msg_data);
4460 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4461
4462 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4463 -- logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Call to Lns_distributions_pub.defaultDistributions failed with status ' || l_return_status);
4464 -- fnd_file.put_line(FND_FILE.LOG, 'FAILED TO INHERIT DISTRIBUTIONS');
4465 --l_last_api_called := 'Lns_distributions_pub.defaultDistributions';
4466 RAISE FND_API.G_EXC_ERROR;
4467 ELSE
4468
4469 if x_event_id is not null then
4470 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Updating lns_distributions...');
4471 update lns_distributions
4472 set event_id = x_event_id
4473 where loan_id = P_LOAN_ID
4474 and account_name in ('LOAN_RECEIVABLE', 'LOAN_CLEARING')
4475 and distribution_type = 'ORIGINATION';
4476 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Done');
4477 end if;
4478
4479 -- finally update the loan header
4480 l_loan_header_rec.loan_id := P_LOAN_ID;
4481 l_loan_header_rec.loan_status := 'ACTIVE';
4482 l_loan_header_rec.secondary_status := FND_API.G_MISS_CHAR;
4483 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Before call to LNS_LOAN_HEADER_PUB.update_loan');
4484 LNS_LOAN_HEADER_PUB.update_loan(p_init_msg_list => FND_API.G_FALSE
4485 ,p_loan_header_rec => l_loan_header_rec
4486 ,p_object_version_number => l_object_version_number
4487 ,x_return_status => l_return_status
4488 ,x_msg_count => l_msg_count
4489 ,x_msg_data => l_msg_data);
4490 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4491 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_msg_data = ' || l_msg_data);
4492
4493 END IF;
4494
4495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4496 RAISE FND_API.G_EXC_ERROR;
4497 else
4498 -- now check if 0th installment needs billing
4499
4500 /* check to start billing for 0-th installment */
4501 open do_billing_cur(l_loan_header_rec.loan_id);
4502 fetch do_billing_cur into l_do_billing;
4503 close do_billing_cur;
4504 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'l_do_billing = ' || l_do_billing);
4505
4506 if l_do_billing > 0 then
4507
4508 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Billing Concurrent Program to bill 0-th installment...');
4509 FND_REQUEST.SET_ORG_ID(l_org_id);
4510
4511 -- Bug#6313716 : Invoke the function add_layout to specify the template type,code etc., before submitting request
4512 SELECT
4513 lower(iso_language),iso_territory
4514 INTO
4515 l_iso_language,l_iso_territory
4516 FROM
4517 FND_LANGUAGES
4518 WHERE
4519 language_code = USERENV('LANG');
4520
4521 l_xml_output:= fnd_request.add_layout(
4522 template_appl_name => 'LNS',
4523 template_code => 'LNSBILLTML',
4524 template_language => l_iso_language,
4525 template_territory => l_iso_territory,
4526 output_format => 'PDF'
4527 );
4528
4529
4530 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
4531 'LNS',
4532 'LNS_BILLING',
4533 '', '', FALSE,
4534 null,
4535 l_loan_header_rec.loan_id,
4536 null,
4537 null);
4538
4539 if l_request_id = 0 then
4540 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4541 FND_MESSAGE.SET_NAME('LNS', 'LNS_BILLING_REQUEST_FAILED');
4542 FND_MSG_PUB.Add;
4543 l_last_api_called := 'FND_REQUEST.SUBMIT_REQUEST for 0th installment billing';
4544 RAISE FND_API.G_EXC_ERROR;
4545 else
4546 LogMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'Successfully submited Billing Concurrent Program to bill 0-th installment. Request id = ' || l_request_id);
4547 end if;
4548
4549 logMessage(FND_LOG.LEVEL_STATEMENT, G_PKG_NAME, 'After call to submit request');
4550
4551 end if;
4552
4553 end if;
4554
4555 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, ' ');
4556 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, '-------------------');
4557 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Generate Distributions process has succeeded!');
4558
4559 EXCEPTION
4560 WHEN others THEN
4561 l_return := FND_CONCURRENT.SET_COMPLETION_STATUS(
4562 status => 'ERROR',
4563 message => 'Generate Distributions process has failed. Please review log file.');
4564 RETCODE := FND_API.G_RET_STS_ERROR;
4565 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => ERRBUF);
4566 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, ' ');
4567 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, '-------------------');
4568 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, 'Generate Distributions process has failed!');
4569
4570 end LNS_ACCOUNTING_CONCUR;
4571
4572
4573
4574 procedure defaultDistrForImport(p_api_version IN NUMBER
4575 ,p_init_msg_list IN VARCHAR2
4576 ,p_commit IN VARCHAR2
4577 ,p_loan_id IN NUMBER
4578 ,p_loan_class_code IN varchar2
4579 ,x_return_status OUT NOCOPY VARCHAR2
4580 ,x_msg_count OUT NOCOPY NUMBER
4581 ,x_msg_data OUT NOCOPY VARCHAR2)
4582 is
4583 l_api_name constant VARCHAR2(30) := 'defaultDistrForImport';
4584 l_return_status VARCHAR2(1) ;
4585 l_msg_count NUMBER;
4586 l_msg_data VARCHAR2(32767);
4587 l_distributionsCatch lns_distributions_pub.distribution_tbl;
4588
4589 BEGIN
4590
4591 logmessage(fnd_log.level_procedure, G_PKG_NAME, g_pkg_name || '.' || l_api_name || ' +');
4592 logmessage(fnd_log.level_statement, G_PKG_NAME, 'p_loan_id = ' || p_loan_id);
4593 logmessage(fnd_log.level_statement, G_PKG_NAME, 'p_loan_class_code = ' || p_loan_class_code);
4594
4595 -- Initialize message list IF p_init_msg_list is set to TRUE.
4596 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4597 FND_MSG_PUB.initialize;
4598 END IF;
4599
4600 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling defaultDistributionsCatch...');
4601 LNS_DISTRIBUTIONS_PUB.defaultDistributionsCatch(
4602 p_api_version => 1.0
4603 ,p_init_msg_list => FND_API.G_TRUE
4604 ,p_commit => FND_API.G_FALSE
4605 ,p_loan_id => p_loan_id
4606 ,p_disb_header_id => null
4607 ,p_include_loan_receivables => 'Y'
4608 ,p_distribution_type => null
4609 ,x_distribution_tbl => l_distributionsCatch
4610 ,x_return_status => l_return_status
4611 ,x_msg_count => l_msg_count
4612 ,x_msg_data => l_msg_data);
4613 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4614 if l_return_status <> 'S' then
4615 RAISE FND_API.G_EXC_ERROR;
4616 end if;
4617
4618 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_distributionsCatch.count = ' || l_distributionsCatch.count);
4619
4620 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling do_insert_distributions...');
4621 lns_distributions_pub.do_insert_distributions(
4622 p_distributions_tbl => l_distributionsCatch
4623 ,p_loan_id => p_loan_id);
4624 if l_return_status <> 'S' then
4625 RAISE FND_API.G_EXC_ERROR;
4626 end if;
4627
4628 -- validate the accounting rows here
4629 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'Calling validateAccounting...');
4630 lns_distributions_pub.validateAccounting(p_loan_id => p_loan_id
4631 ,p_init_msg_list => FND_API.G_TRUE
4632 ,x_return_status => l_return_status
4633 ,x_msg_count => l_msg_count
4634 ,x_msg_data => l_msg_data);
4635
4636 logMessage(FND_LOG.level_statement, G_PKG_NAME, 'l_return_status = ' || l_return_status);
4637 if l_return_status <> 'S' then
4638 FND_MESSAGE.SET_NAME('LNS', 'LNS_DEFAULT_DIST_NOT_FOUND');
4639 FND_MSG_PUB.ADD;
4640 LogMessage(FND_LOG.LEVEL_UNEXPECTED, G_PKG_NAME, FND_MSG_PUB.Get(p_encoded => 'F'));
4641 RAISE FND_API.G_EXC_ERROR;
4642 end if;
4643
4644 x_return_status := fnd_api.g_ret_sts_success;
4645 logmessage(fnd_log.level_procedure, G_PKG_NAME, g_pkg_name || '.' || l_api_name || ' -');
4646
4647 EXCEPTION
4648 WHEN others THEN
4649 x_return_status := fnd_api.g_ret_sts_unexp_error;
4650 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
4651 END;
4652
4653
4654
4655 END LNS_DISTRIBUTIONS_PUB;