[Home] [Help]
PACKAGE BODY: APPS.LNS_BUS_EVENT_SUB_PVT
Source
1 PACKAGE BODY LNS_BUS_EVENT_SUB_PVT AS
2 /* $Header: LNS_BUS_EVENT_B.pls 120.3 2006/07/31 23:37:47 karamach noship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'LNS_BUS_EVENT_SUB_PVT';
8 G_LOG_ENABLED varchar2(5);
9 G_MSG_LEVEL NUMBER;
10
11
12 /*========================================================================
13 | PRIVATE PROCEDURE LogMessage
14 |
15 | DESCRIPTION
16 | This procedure logs debug messages to db and to CM log
17 |
18 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
19 | Delinquency_Create
20 |
21 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
22 | None
23 |
24 | PARAMETERS
25 | p_msg_level IN Debug msg level
26 | p_msg IN Debug msg itself
27 |
28 | KNOWN ISSUES
29 | None
30 |
31 | NOTES
32 | Any interesting aspect of the code in the package body which needs
33 | to be stated.
34 |
35 | MODIFICATION HISTORY
36 | Date Author Description of Changes
37 | 01-01-2004 scherkas Created
38 |
39 *=======================================================================*/
40 Procedure LogMessage(p_msg_level IN NUMBER, p_msg in varchar2)
41 IS
42 BEGIN
43 if (p_msg_level >= G_MSG_LEVEL) then
44
45 FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
46
47 end if;
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR in LogMessage: ' || sqlerrm);
52 END;
53
54
55
56
57 /*========================================================================
58 | PUBLIC FUNCTION Delinquency_Create
59 |
60 | DESCRIPTION
61 | This function processes oracle.apps.iex.delinquency.create event
62 |
63 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
64 | None
65 |
66 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
67 | LogMessage
68 |
69 | PARAMETERS
70 | p_subscription_guid IN Standard in parameter
71 | p_event IN Standard in parameter
72 |
73 | KNOWN ISSUES
74 | None
75 |
76 | NOTES
77 | Any interesting aspect of the code in the package body which needs
78 | to be stated.
79 |
80 | MODIFICATION HISTORY
81 | Date Author Description of Changes
82 | 01-01-2004 scherkas Created
83 |
84 *=======================================================================*/
85 FUNCTION Delinquency_Create(p_subscription_guid In RAW, p_event IN OUT NOCOPY WF_EVENT_T)RETURN VARCHAR2
86 IS
87 /*-----------------------------------------------------------------------+
88 | Local Variable Declarations and initializations |
89 +-----------------------------------------------------------------------*/
90 l_api_name CONSTANT VARCHAR2(30) := 'Delinquency_Create';
91 l_return_status VARCHAR2(1);
92 l_msg_count NUMBER;
93 l_msg_data VARCHAR2(32767);
94 l_status varchar2(30);
95 l_loan_number varchar2(60);
96 l_loan_id number;
97 l_version_number number;
98 l_loan_header_rec LNS_LOAN_HEADER_PUB.loan_header_rec_type;
99 l_score number;
100 l_index number := 0;
101 l_indexNo number := 1;
102 l_msg varchar2(4000) := null;
103 l_org_id number;
104 l_request_id number;
105 l_num_del_cr number;
106 l_num_del_upd number;
107 l_count number;
108 l_amortization_schedule_id number;
109 l_cust_trx_id number;
110
111 /*-----------------------------------------------------------------------+
112 | Cursor Declarations |
113 +-----------------------------------------------------------------------*/
114
115 /* query for loans trx */
116 CURSOR loan_trx_cur(P_REQUEST_ID number) IS
117 select ams.LOAN_ID,
118 loan.loan_number,
119 loan.org_id,
120 ams.AMORTIZATION_SCHEDULE_ID,
121 del.transaction_id
122 from
123 lns_loan_headers_all loan,
124 lns_amortization_scheds ams,
125 IEX_DELINQUENCIES_ALL del
126 where
127 del.request_id = P_REQUEST_ID and
128 del.transaction_id in (ams.PRINCIPAL_TRX_ID, ams.INTEREST_TRX_ID, ams.FEE_TRX_ID) and
129 ams.loan_id = loan.loan_id;
130
131 -- getting loan version
132 CURSOR loan_version_cur(P_LOAN_ID number) IS
133 select OBJECT_VERSION_NUMBER
134 from LNS_LOAN_HEADERS_ALL
135 where LOAN_ID = P_LOAN_ID;
136
137 BEGIN
138 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');
139 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Received event ' || p_event.getEventName());
140
141 /* Established savepoint */
142 SAVEPOINT Delinquency_Create;
143 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Established savepoint');
144
145 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
146 l_num_del_cr := p_event.GetValueForParameter('NOOFDELCREATED');
147 l_num_del_upd := p_event.GetValueForParameter('NOOFDELUPDATED');
148
149 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'Event parameters:');
150 LogMessage(FND_LOG.LEVEL_PROCEDURE, 'request id: ' || l_request_id);
151 LogMessage(FND_LOG.LEVEL_STATEMENT, 'number of created del: ' || l_num_del_cr);
152 LogMessage(FND_LOG.LEVEL_STATEMENT, 'number of updated del: ' || l_num_del_upd);
153
154 if l_request_id is not null then
155
156 l_count := 0;
157
158 /* query for loans trx */
159 open loan_trx_cur(to_number(l_request_id));
160 LOOP
161
162 fetch loan_trx_cur into l_loan_id,
163 l_loan_number,
164 l_org_id,
165 l_amortization_schedule_id,
166 l_cust_trx_id;
167 exit when loan_trx_cur%NOTFOUND;
168
169 l_count := l_count + 1;
170 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Found trx #' || l_count);
171 LogMessage(FND_LOG.LEVEL_STATEMENT, 'cust_trx_id: ' || l_cust_trx_id);
172 LogMessage(FND_LOG.LEVEL_STATEMENT, 'amortization_schedule_id: ' || l_amortization_schedule_id);
173 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_id: ' || l_loan_id);
174 LogMessage(FND_LOG.LEVEL_STATEMENT, 'loan_number: ' || l_loan_number);
175 LogMessage(FND_LOG.LEVEL_STATEMENT, 'org_id: ' || l_org_id);
176
177 /* calling scoring engine to get new loan status */
178 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling collections scoring engine to get new loan status...');
179
180 BEGIN
181
182 MO_GLOBAL.INIT('LNS');
183 MO_GLOBAL.set_policy_context('S', l_org_id);
184
185 IEX_SCOREAPI_PUB.GETSCORESTATUS(
186 P_API_VERSION => 1.0,
187 P_INIT_MSG_LIST => FND_API.G_TRUE,
188 P_SCORE_ID => 8, -- hardcoded value from collections seeded data
189 P_OBJECT_ID => l_loan_id,
190 X_STATUS => l_status,
191 X_SCORE => l_score,
192 X_RETURN_STATUS => l_return_status,
193 X_MSG_COUNT => l_msg_count,
194 X_MSG_DATA => l_msg_data);
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 LogMessage(FND_LOG.LEVEL_ERROR, 'Collections scoring engine API is not installed. Please install it first.');
199 RAISE FND_API.G_EXC_ERROR;
200 END;
201
202 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
203
204 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
205
206 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to collections scoring engine failed with following error:');
207 while (l_indexNo <= l_msg_Count ) loop
208 fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
209 LogMessage(FND_LOG.LEVEL_UNEXPECTED, l_msg);
210 l_indexNo := l_indexNo + 1;
211 End Loop;
212
213 RAISE FND_API.G_EXC_ERROR;
214
215 END IF;
216
217 LogMessage(FND_LOG.LEVEL_STATEMENT, 'New score: ' || l_score);
218 LogMessage(FND_LOG.LEVEL_STATEMENT, 'New status: ' || l_status);
219
220 -- updating loan header table
221 l_loan_header_rec.loan_id := l_loan_id;
222 l_loan_header_rec.LOAN_STATUS := l_status;
223
224 -- getting loan version
225 open loan_version_cur(l_loan_header_rec.loan_id);
226 fetch loan_version_cur into l_version_number;
227 close loan_version_cur;
228
229 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Updating loan header...');
230
231 LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_version_number,
232 P_LOAN_HEADER_REC => l_loan_header_rec,
233 P_INIT_MSG_LIST => FND_API.G_TRUE,
234 X_RETURN_STATUS => l_return_status,
235 X_MSG_COUNT => l_msg_count,
236 X_MSG_DATA => l_msg_data);
237
238 LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
239
240 IF l_return_status = 'S' THEN
241 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL with status ' || l_status);
242 ELSE
243 FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
244 FND_MSG_PUB.Add;
245 LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));
246 RAISE FND_API.G_EXC_ERROR;
247 END IF;
248
249 END LOOP;
250
251 close loan_trx_cur;
252
253 end if;
254
255 -- commiting
256 if l_count > 0 then
257 COMMIT WORK;
258 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Commited');
259 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Total processed ' || l_count || ' transactions');
260 else
261 LogMessage(FND_LOG.LEVEL_STATEMENT, 'No loan invoices found - no actions will be performed');
262 end if;
263
264 LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully processed event ' || p_event.getEventName());
265 LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' -');
266 RETURN 'SUCCESS';
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 ROLLBACK TO Delinquency_Create;
271
272 WF_CORE.CONTEXT('LNS_BUS_EVENT_SUB_PVT', 'Delinquency_Create', p_event.getEventName(), p_subscription_guid);
273 WF_EVENT.setErrorInfo(p_event, 'ERROR');
274 LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to process event ' || p_event.getEventName());
275
276 RETURN 'ERROR';
277
278 END Delinquency_Create;
279
280
281 BEGIN
282
283 G_LOG_ENABLED := 'N';
284 G_MSG_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
285
286 /* getting msg logging info */
287 G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
288 /*
289 if (G_LOG_ENABLED = 'N') then
290 G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
291 else
292 G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
293 end if;
294 */
295 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
296 LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
297
298 END LNS_BUS_EVENT_SUB_PVT; -- Package body