[Home] [Help]
PACKAGE BODY: APPS.IEX_WF_DEL_REQ_CREDIT_PUB
Source
1 PACKAGE BODY IEX_WF_DEL_REQ_CREDIT_PUB AS
2 /* $Header: iexwfdcb.pls 120.1 2006/05/30 21:19:24 scherkas noship $ */
3 /*
4 * This procedure needs to be called with an itemtype and workflow process
5 * which'll launch workflow .Start Workfolw will call workflow based on
6 * Meth_flag in methodology base table
7 */
8
9 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_WF_DEL_REQ_CREDIT_PUB';
10
11 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
12
13 PROCEDURE start_workflow
14 (
15 p_api_version IN NUMBER := 1.0,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_user_id IN NUMBER,
19 p_delinquency_id IN NUMBER,
20 p_del_type IN VARCHAR2,
21 p_repossession_id IN NUMBER,
22 p_litigation_id IN NUMBER,
23 p_writeoff_id IN NUMBER,
24 p_bankruptcy_id IN NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 )
29 IS
30 l_result VARCHAR2(10);
31 itemtype VARCHAR2(30);
32 itemkey VARCHAR2(30);
33 workflowprocess VARCHAR2(30);
34 l_user_id NUMBER;
35 l_user_name VARCHAR2(60);
36 l_manager_id NUMBER;
37 l_manager_name VARCHAR2(60);
38
39 l_error_msg VARCHAR2(2000);
40 l_return_status VARCHAR2(20);
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000);
43 l_api_name VARCHAR2(100) := 'START_WORKFLOW';
44 l_api_version_number CONSTANT NUMBER := 1.0;
45
46 CURSOR c_manager(p_user_id NUMBER) IS
47 SELECT b.user_id, b.user_name
48 FROM JTF_RS_RESOURCE_EXTNS a
49 , JTF_RS_RESOURCE_EXTNS b
50 WHERE b.source_id = a.source_mgr_id
51 AND a.user_id = p_user_id;
52
53 BEGIN
54 -- Standard Start of API savepoint
55 SAVEPOINT START_WORKFLOW;
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
58 p_api_version,
59 l_api_name,
60 G_PKG_NAME)
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65
66 -- Initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.to_Boolean( p_init_msg_list )
68 THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72
73
74 -- Initialize API return status to SUCCESS
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 SELECT TO_CHAR(IEX_DEL_WF_S.NEXTVAL) INTO itemkey FROM dual;
78 -- itemkey := TO_CHAR(p_delinquency_id);
79 itemtype := 'IEXDELCR';
80 workflowprocess := 'CREDIT_HOLD';
81
82 IF p_del_type = 'Delinquency' THEN
83
84 itemkey := 'DEL'||itemkey; --to_char(p_delinquency_id);
85 --DBMS_OUTPUT.PUT_LINE('itemkey = ' || itemkey);
86
87 wf_engine.createprocess ( itemtype => itemtype,
88 itemkey => itemkey,
89 process => 'CREDIT_HOLD');
90
91 wf_engine.setitemattrtext( itemtype => itemtype,
92 itemkey => itemkey,
93 aname => 'DEL_TYPE',
94 avalue => 'Delinquency');
95
96 wf_engine.setitemattrtext( itemtype => itemtype,
97 itemkey => itemkey,
98 aname => 'DELINQUENCY_ID',
99 avalue => p_delinquency_id);
100
101 wf_engine.setitemattrtext( itemtype => itemtype,
102 itemkey => itemkey,
103 aname => 'UNIQUE_ID',
104 avalue => 'Delinquency Id: '||p_delinquency_id);
105
106 ELSIF p_del_type = 'Repossession' THEN
107
108 itemkey := 'REP'||itemkey; --to_char(p_repossession_id);
109 --DBMS_OUTPUT.PUT_LINE('itemkey = ' || itemkey);
110
111 wf_engine.createprocess ( itemtype => itemtype,
112 itemkey => itemkey,
113 process => 'CREDIT_HOLD');
114
115 wf_engine.setitemattrtext( itemtype => itemtype,
116 itemkey => itemkey,
117 aname => 'DEL_TYPE',
118 avalue => 'Repossession');
119 --DBMS_OUTPUT.PUT_LINE('*');
120
121 wf_engine.setitemattrtext( itemtype => itemtype,
122 itemkey => itemkey,
123 aname => 'REPOSSESSION_ID',
124 avalue => p_repossession_id);
125 --DBMS_OUTPUT.PUT_LINE('**');
126
127 wf_engine.setitemattrtext( itemtype => itemtype,
128 itemkey => itemkey,
129 aname => 'UNIQUE_ID',
130 avalue => 'Reposession Id: '||p_repossession_id);
131 --DBMS_OUTPUT.PUT_LINE('***');
132
133 ELSIF p_del_type = 'Litigation' THEN
134
135 itemkey := 'LIT'||itemkey; --to_char(p_litigation_id);
136
137 wf_engine.createprocess ( itemtype => itemtype,
138 itemkey => itemkey,
139 process => 'CREDIT_HOLD');
140
141 wf_engine.setitemattrtext( itemtype => itemtype,
142 itemkey => itemkey,
143 aname => 'DEL_TYPE',
144 avalue => 'Litigation');
145
146 wf_engine.setitemattrtext( itemtype => itemtype,
147 itemkey => itemkey,
148 aname => 'LITIGATION_ID',
149 avalue => p_litigation_id);
150
151 wf_engine.setitemattrtext( itemtype => itemtype,
152 itemkey => itemkey,
153 aname => 'UNIQUE_ID',
154 avalue => 'Litigation Id: '||p_litigation_id);
155
156 ELSIF p_del_type = 'Writeoff' THEN
157
158 itemkey := 'WRI'||itemkey; --to_char(p_writeoff_id);
159
160 wf_engine.createprocess ( itemtype => itemtype,
161 itemkey => itemkey,
162 process => 'CREDIT_HOLD');
163
164 wf_engine.setitemattrtext( itemtype => itemtype,
165 itemkey => itemkey,
166 aname => 'DEL_TYPE',
167 avalue => 'Writeoff');
168
169 wf_engine.setitemattrtext( itemtype => itemtype,
170 itemkey => itemkey,
171 aname => 'WRITEOFF_ID',
172 avalue => p_writeoff_id);
173
174 wf_engine.setitemattrtext( itemtype => itemtype,
175 itemkey => itemkey,
176 aname => 'UNIQUE_ID',
177 avalue => 'Writeoff Id: '||p_writeoff_id);
178
179 ELSIF p_del_type = 'Bankruptcy' THEN
180
181 itemkey := 'Ban'||itemkey; --to_char(p_bankruptcy_id);
182 --DBMS_OUTPUT.PUT_LINE('itemkey = ' || itemkey);
183
184 wf_engine.createprocess ( itemtype => itemtype,
185 itemkey => itemkey,
186 process => 'CREDIT_HOLD');
187
188 wf_engine.setitemattrtext( itemtype => itemtype,
189 itemkey => itemkey,
190 aname => 'DEL_TYPE',
191 avalue => 'Bankrupt');
192
193 wf_engine.setitemattrtext( itemtype => itemtype,
194 itemkey => itemkey,
195 aname => 'BANKRUPTCY_ID',
196 avalue => p_bankruptcy_id);
197
198 wf_engine.setitemattrtext( itemtype => itemtype,
199 itemkey => itemkey,
200 aname => 'UNIQUE_ID',
201 avalue => 'Bankruptcy Id: '||p_bankruptcy_id);
202
203 ELSE
204 null;
205 -- result := 'COMPLETE';
206 END IF;
207 --DBMS_OUTPUT.PUT_LINE('*');
208 -- Get manager
209 SELECT user_name INTO l_user_name from JTF_RS_RESOURCE_EXTNS
210 WHERE user_id = p_user_id;
211
212 OPEN C_MANAGER(p_user_id);
213 FETCH C_MANAGER INTO l_manager_id, l_manager_name;
214 IF C_MANAGER%NOTFOUND THEN
215 l_manager_id := p_user_id;
216 l_manager_name := l_user_name;
217 END IF;
218 CLOSE C_MANAGER;
219 --DBMS_OUTPUT.PUT_LINE('**');
220 wf_engine.setitemattrnumber( itemtype => itemtype,
221 itemkey => itemkey,
222 aname => 'DELINQUENCY_ID',
223 avalue => p_delinquency_id);
224
225 wf_engine.setitemattrnumber( itemtype => itemtype,
226 itemkey => itemkey,
227 aname => 'MANAGER_ID',
228 avalue => l_manager_id);
229
230 wf_engine.setitemattrtext( itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'MANAGER_NAME',
233 avalue => l_manager_name);
234
235 wf_engine.setitemattrtext( itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'REQUESTER_NAME',
238 avalue => l_user_name);
239
240 wf_engine.setitemattrtext( itemtype => itemtype,
241 itemkey => itemkey,
242 aname => 'REQUESTER_ID',
243 avalue => p_user_id);
244
245 wf_engine.startprocess( itemtype => itemtype,
246 itemkey => itemkey);
247
248 wf_engine.ItemStatus( itemtype => ItemType,
249 itemkey => ItemKey,
250 status => l_return_status,
251 result => l_result);
252 --DBMS_OUTPUT.PUT_LINE('***'||l_return_status);
253 if (l_return_status = 'COMPLETE') OR (l_return_status = 'ACTIVE') THEN
254 x_return_status := 'S';
255 commit;
256 else
257 x_return_status := 'F';
258 end if;
259
260
261 -- Standard call to get message count and if count is 1, get message info.
262 FND_MSG_PUB.Count_And_Get
263 ( p_count => x_msg_count,
264 p_data => x_msg_data
265 );
266
267 EXCEPTION
268 WHEN FND_API.G_EXC_ERROR THEN
269 as_utility_pvt.HANDLE_EXCEPTIONS(
270 P_API_NAME => L_API_NAME
271 ,P_PKG_NAME => G_PKG_NAME
272 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
273 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
274 ,X_MSG_COUNT => X_MSG_COUNT
275 ,X_MSG_DATA => X_MSG_DATA
276 ,X_RETURN_STATUS => X_RETURN_STATUS);
277
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 as_utility_pvt.HANDLE_EXCEPTIONS(
280 P_API_NAME => L_API_NAME
281 ,P_PKG_NAME => G_PKG_NAME
282 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
283 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
284 ,X_MSG_COUNT => X_MSG_COUNT
285 ,X_MSG_DATA => X_MSG_DATA
286 ,X_RETURN_STATUS => X_RETURN_STATUS);
287
288 WHEN OTHERS THEN
289 as_utility_pvt.HANDLE_EXCEPTIONS(
290 P_API_NAME => L_API_NAME
291 ,P_PKG_NAME => G_PKG_NAME
292 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
293 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
294 ,X_MSG_COUNT => X_MSG_COUNT
295 ,X_MSG_DATA => X_MSG_DATA
296 ,X_RETURN_STATUS => X_RETURN_STATUS);
297 ----------------------------------
298 END start_workflow;
299
300 -- procedure update_approval_status -----------------------------
301 PROCEDURE update_approval_status(
302 itemtype IN VARCHAR2,
303 itemkey IN VARCHAR2,
304 actid IN NUMBER,
305 funcmode IN VARCHAR2,
306 result OUT NOCOPY VARCHAR2
307 ) is
308
309 l_manager_name varchar2(60);
310 l_delinquency_id number(30);
311 l_repossession_id number;
312 l_litigation_id number;
313 l_writeoff_id number;
314 l_bankruptcy_id number;
315 l_responder varchar2(100);
316 l_text_value varchar2(2000);
317 l_del_type varchar2(100);
318 l_del_type_id varchar2(100);
319 l_dummy varchar2(1);
320 l_api_name VARCHAR2(100) := 'update_approval_status';
321 l_errmsg_name VARCHAR2(30);
322 L_API_ERROR EXCEPTION;
323
324 BEGIN
325
326 if funcmode <> 'RUN' then
327 result := wf_engine.eng_null;
328 return;
329 end if;
330
331 l_manager_name := wf_engine.GetItemAttrText(
332 itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'MANAGER_NAME');
335
336 l_delinquency_id := wf_engine.GetItemAttrNumber(
337 itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'DELINQUENCY_ID');
340
341 l_del_type := wf_engine.GetItemAttrText(
342 itemtype => itemtype,
343 itemkey => itemkey,
344 aname => 'DEL_TYPE');
345
346 l_repossession_id := wf_engine.GetItemAttrNumber(
347 itemtype => itemtype,
348 itemkey => itemkey,
349 aname => 'REPOSSESSION_ID');
350
351 l_litigation_id := wf_engine.GetItemAttrNumber(
352 itemtype => itemtype,
353 itemkey => itemkey,
354 aname => 'LITIGATION_ID');
355
356 l_writeoff_id := wf_engine.GetItemAttrNumber(
357 itemtype => itemtype,
358 itemkey => itemkey,
359 aname => 'WRITEOFF_ID');
360
361 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
362 itemtype => itemtype,
363 itemkey => itemkey,
364 aname => 'BANKRUPTCY_ID');
365
366 IF l_del_type = 'Delinquency' THEN
367 update IEX_DELINQUENCIES_ALL
368 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
369 where delinquency_id = l_delinquency_id;
370 ELSIF l_del_type = 'Repossession' THEN
371 update IEX_REPOSSESSIONS
372 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
373 where repossession_id = l_repossession_id;
374 ELSIF l_del_type = 'Litigation' THEN
375 update IEX_LITIGATIONS
376 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
380 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
377 where litigation_id = l_litigation_id;
378 ELSIF l_del_type = 'Writeoff' THEN
379 update IEX_WRITEOFFS
381 where writeoff_id = l_writeoff_id;
382 ELSIF l_del_type = 'Bankruptcy' THEN
383 update IEX_BANKRUPTCIES
384 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
385 where bankruptcy_id = l_bankruptcy_id;
386 ELSE
387 null;
388 END IF;
389
390 result := 'COMPLETE';
391
392 EXCEPTION
393 WHEN L_API_ERROR then
394 WF_CORE.Raise(l_errmsg_name);
395 WHEN OTHERS THEN
396 WF_CORE.Context('IEX_DEL_REQ_CREDIT_WF_PUB', 'Reject_Contract',
397 itemtype, itemkey, actid, funcmode);
398 RAISE;
399 END update_approval_status;
400
401 -- procedure update_rejection_status -----------------------------
402 procedure update_rejection_status(
403 itemtype IN VARCHAR2,
404 itemkey IN VARCHAR2,
405 actid IN NUMBER,
406 funcmode IN VARCHAR2,
407 result OUT NOCOPY VARCHAR2
408 ) is
409
410 l_manager_name varchar2(60);
411 l_delinquency_id number(30);
412 l_repossession_id number;
413 l_litigation_id number;
414 l_writeoff_id number;
415 l_bankruptcy_id number;
416 l_responder varchar2(100);
417 l_text_value varchar2(2000);
418 l_del_type varchar2(100);
419 l_del_type_id varchar2(100);
420 l_api_name VARCHAR2(100) := 'update_rejection_status';
421 l_errmsg_name VARCHAR2(30);
422 L_API_ERROR EXCEPTION;
423
424 BEGIN
425 l_manager_name := wf_engine.GetItemAttrText(
426 itemtype => itemtype,
427 itemkey => itemkey,
428 aname => 'MANAGER_NAME');
429
430 l_delinquency_id := wf_engine.GetItemAttrNumber(
431 itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'DELINQUENCY_ID');
434
435 l_del_type := wf_engine.GetItemAttrText(
436 itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'DEL_TYPE');
439
440 l_repossession_id := wf_engine.GetItemAttrNumber(
441 itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'REPOSSESSION_ID');
444
445 l_litigation_id := wf_engine.GetItemAttrNumber(
446 itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'LITIGATION_ID');
449
450 l_writeoff_id := wf_engine.GetItemAttrNumber(
451 itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'WRITEOFF_ID');
454
455 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
456 itemtype => itemtype,
457 itemkey => itemkey,
458 aname => 'BANKRUPTCY_ID');
459
460 IF l_del_type = 'Delinquency' THEN
461 update IEX_DELINQUENCIES_ALL
462 set CREDIT_HOLD_APPROVED_FLAG = 'N'
463 where delinquency_id = l_delinquency_id;
464 ELSIF l_del_type = 'Repossession' THEN
465 update IEX_REPOSSESSIONS
466 set CREDIT_HOLD_APPROVED_FLAG = 'N'
467 where repossession_id = l_repossession_id;
468 ELSIF l_del_type = 'Litigation' THEN
469 update IEX_LITIGATIONS
470 set CREDIT_HOLD_APPROVED_FLAG = 'N'
471 where litigation_id = l_litigation_id;
472 ELSIF l_del_type = 'Writeoff' THEN
473 update IEX_WRITEOFFS
474 set CREDIT_HOLD_APPROVED_FLAG = 'N'
475 where writeoff_id = l_writeoff_id;
476 ELSIF l_del_type = 'Bankruptcy' THEN
477 update IEX_BANKRUPTCIES
478 set CREDIT_HOLD_APPROVED_FLAG = 'N'
479 where bankruptcy_id = l_bankruptcy_id;
480 ELSE
481 null;
482 END IF;
483
484 result := 'COMPLETE';
485
486 EXCEPTION
487 WHEN l_API_ERROR then
488 WF_CORE.Raise(l_errmsg_name);
489 WHEN OTHERS THEN
490 WF_CORE.Context('IEX_DEL_REQ_CREDIT_WF_PUB', 'Reject_Contract',
491 itemtype, itemkey, actid, funcmode);
492 RAISE;
493
494 END update_rejection_status;
495
496 END IEX_WF_DEL_REQ_CREDIT_PUB;