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