[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.2.12020000.3 2012/12/21 17:17:01 ehuh ship $ */
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 l_party_id number;
47 l_party_name varchar2(50) := '';
48 l_account_number varchar2(30) := '';
49
50 CURSOR c_manager(p_user_id NUMBER) IS
51 SELECT b.user_id, b.user_name
52 FROM JTF_RS_RESOURCE_EXTNS a
53 , JTF_RS_RESOURCE_EXTNS b
54 WHERE b.source_id = a.source_mgr_id
55 AND a.user_id = p_user_id;
56
57 BEGIN
58 -- Standard Start of API savepoint
59 SAVEPOINT START_WORKFLOW;
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
62 p_api_version,
63 l_api_name,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.to_Boolean( p_init_msg_list )
72 THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76
77
78 -- Initialize API return status to SUCCESS
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 SELECT TO_CHAR(IEX_DEL_WF_S.NEXTVAL) INTO itemkey FROM dual;
82 -- itemkey := TO_CHAR(p_delinquency_id);
83 itemtype := 'IEXDELCR';
84 workflowprocess := 'CREDIT_HOLD';
85
86 IF p_del_type = 'Delinquency' THEN
87
88 itemkey := 'DEL'||itemkey; --to_char(p_delinquency_id);
89
90 wf_engine.createprocess ( itemtype => itemtype,
91 itemkey => itemkey,
92 process => 'CREDIT_HOLD');
93
94 wf_engine.setitemattrtext( itemtype => itemtype,
95 itemkey => itemkey,
96 aname => 'DEL_TYPE',
97 avalue => 'Delinquency');
98
99 wf_engine.setitemattrtext( itemtype => itemtype,
100 itemkey => itemkey,
101 aname => 'DELINQUENCY_ID',
102 avalue => p_delinquency_id);
103
104 wf_engine.setitemattrtext( itemtype => itemtype,
105 itemkey => itemkey,
106 aname => 'UNIQUE_ID',
107 avalue => 'Delinquency Id: '||p_delinquency_id);
108
109 -- bug 15935804 begin...
110 begin
111 select party_id,party_name into l_party_id,l_party_name from hz_parties
112 where party_id = (select party_id from iex_delinquencies_all where delinquency_id = p_delinquency_id);
113
114 select account_number into l_account_number from hz_cust_accounts
115 where cust_account_id = (select cust_account_id from iex_delinquencies_all where delinquency_id = p_delinquency_id);
116 exception
117 when others then null;
118 end;
119
120 wf_engine.setitemattrtext( itemtype => itemtype,
121 itemkey => itemkey,
122 aname => 'PARTY_ID',
123 avalue => l_party_id);
124
125 wf_engine.setitemattrtext( itemtype => itemtype,
126 itemkey => itemkey,
127 aname => 'PARTY_NAME',
128 avalue => l_party_name);
129
130 wf_engine.setitemattrtext( itemtype => itemtype,
131 itemkey => itemkey,
132 aname => 'ACCOUNT_NUMBER',
133 avalue => l_account_number);
134 -- bug 15935804 end...
135
136 ELSIF p_del_type = 'Repossession' THEN
137
138 itemkey := 'REP'||itemkey; --to_char(p_repossession_id);
139
140 wf_engine.createprocess ( itemtype => itemtype,
141 itemkey => itemkey,
142 process => 'CREDIT_HOLD');
143
144 wf_engine.setitemattrtext( itemtype => itemtype,
145 itemkey => itemkey,
146 aname => 'DEL_TYPE',
147 avalue => 'Repossession');
148
149 wf_engine.setitemattrtext( itemtype => itemtype,
150 itemkey => itemkey,
151 aname => 'REPOSSESSION_ID',
152 avalue => p_repossession_id);
153
154 wf_engine.setitemattrtext( itemtype => itemtype,
155 itemkey => itemkey,
156 aname => 'UNIQUE_ID',
157 avalue => 'Reposession Id: '||p_repossession_id);
158
159 -- bug 15935804 begin...
160 begin
161 select party_id,party_name into l_party_id,l_party_name from hz_parties
162 where party_id = (select party_id from iex_repossessions where repossession_id = p_repossession_id);
163
164 select account_number into l_account_number from hz_cust_accounts
165 where cust_account_id = (select cust_account_id from iex_repossessions where repossession_id = p_repossession_id);
166 exception
167 when others then null;
168 end;
169
170 wf_engine.setitemattrtext( itemtype => itemtype,
171 itemkey => itemkey,
172 aname => 'PARTY_ID',
173 avalue => l_party_id);
174
175 wf_engine.setitemattrtext( itemtype => itemtype,
176 itemkey => itemkey,
177 aname => 'PARTY_NAME',
178 avalue => l_party_name);
179
180 wf_engine.setitemattrtext( itemtype => itemtype,
181 itemkey => itemkey,
182 aname => 'ACCOUNT_NUMBER',
183 avalue => l_account_number);
184 -- bug 15935804 end...
185
186 ELSIF p_del_type = 'Litigation' THEN
187
188 itemkey := 'LIT'||itemkey; --to_char(p_litigation_id);
189
190 wf_engine.createprocess ( itemtype => itemtype,
191 itemkey => itemkey,
192 process => 'CREDIT_HOLD');
193
194 wf_engine.setitemattrtext( itemtype => itemtype,
195 itemkey => itemkey,
196 aname => 'DEL_TYPE',
197 avalue => 'Litigation');
198
199 wf_engine.setitemattrtext( itemtype => itemtype,
200 itemkey => itemkey,
201 aname => 'LITIGATION_ID',
202 avalue => p_litigation_id);
203
204 wf_engine.setitemattrtext( itemtype => itemtype,
205 itemkey => itemkey,
206 aname => 'UNIQUE_ID',
207 avalue => 'Litigation Id: '||p_litigation_id);
208
209 -- bug 15935804 begin...
210 begin
211 select party_id,party_name into l_party_id,l_party_name from hz_parties
212 where party_id = (select party_id from iex_litigations where litigation_id = p_litigation_id);
213
214 select account_number into l_account_number from hz_cust_accounts
215 where cust_account_id = (select cust_account_id from iex_litigations where litigation_id = p_litigation_id);
216 exception
217 when others then null;
218 end;
219
220 wf_engine.setitemattrtext( itemtype => itemtype,
221 itemkey => itemkey,
222 aname => 'PARTY_ID',
223 avalue => l_party_id);
224
225 wf_engine.setitemattrtext( itemtype => itemtype,
226 itemkey => itemkey,
227 aname => 'PARTY_NAME',
228 avalue => l_party_name);
229
230 wf_engine.setitemattrtext( itemtype => itemtype,
231 itemkey => itemkey,
232 aname => 'ACCOUNT_NUMBER',
233 avalue => l_account_number);
234 -- bug 15935804 end...
235
236 ELSIF p_del_type = 'Writeoff' THEN
237
238 itemkey := 'WRI'||itemkey; --to_char(p_writeoff_id);
239
240 wf_engine.createprocess ( itemtype => itemtype,
241 itemkey => itemkey,
242 process => 'CREDIT_HOLD');
243
244 wf_engine.setitemattrtext( itemtype => itemtype,
245 itemkey => itemkey,
246 aname => 'DEL_TYPE',
247 avalue => 'Writeoff');
248
249 wf_engine.setitemattrtext( itemtype => itemtype,
250 itemkey => itemkey,
251 aname => 'WRITEOFF_ID',
252 avalue => p_writeoff_id);
253
254 wf_engine.setitemattrtext( itemtype => itemtype,
255 itemkey => itemkey,
256 aname => 'UNIQUE_ID',
257 avalue => 'Writeoff Id: '||p_writeoff_id);
258
259 -- bug 15935804 begin...
260 begin
261 select party_id,party_name into l_party_id,l_party_name from hz_parties
262 where party_id = (select party_id from iex_writeoffs where writeoff_id = p_writeoff_id);
263
264 select account_number into l_account_number from hz_cust_accounts
265 where cust_account_id = (select cust_account_id from iex_writeoffs where writeoff_id = p_writeoff_id);
266 exception
267 when others then null;
268 end;
269
270 wf_engine.setitemattrtext( itemtype => itemtype,
271 itemkey => itemkey,
272 aname => 'PARTY_ID',
273 avalue => l_party_id);
274
275 wf_engine.setitemattrtext( itemtype => itemtype,
276 itemkey => itemkey,
277 aname => 'PARTY_NAME',
278 avalue => l_party_name);
279
280 wf_engine.setitemattrtext( itemtype => itemtype,
281 itemkey => itemkey,
282 aname => 'ACCOUNT_NUMBER',
283 avalue => l_account_number);
284 -- bug 15935804 end...
285
286 ELSIF p_del_type = 'Bankruptcy' THEN
287
288 itemkey := 'Ban'||itemkey; --to_char(p_bankruptcy_id);
289 --DBMS_OUTPUT.PUT_LINE('itemkey = ' || itemkey);
290
291 wf_engine.createprocess ( itemtype => itemtype,
292 itemkey => itemkey,
293 process => 'CREDIT_HOLD');
294
295 wf_engine.setitemattrtext( itemtype => itemtype,
296 itemkey => itemkey,
297 aname => 'DEL_TYPE',
298 avalue => 'Bankrupt');
299
300 wf_engine.setitemattrtext( itemtype => itemtype,
301 itemkey => itemkey,
302 aname => 'BANKRUPTCY_ID',
303 avalue => p_bankruptcy_id);
304
305 wf_engine.setitemattrtext( itemtype => itemtype,
306 itemkey => itemkey,
307 aname => 'UNIQUE_ID',
308 avalue => 'Bankruptcy Id: '||p_bankruptcy_id);
309
310 -- bug 15935804 begin...
311 begin
312 select party_id,party_name into l_party_id,l_party_name from hz_parties
313 where party_id = (select party_id from iex_bankruptcies where bankruptcy_id = p_bankruptcy_id);
314
315 select account_number into l_account_number from hz_cust_accounts hc
316 where exists (select 1 from iex_bankruptcies bk where bankruptcy_id = p_bankruptcy_id
317 and attribute15 = 'ACCOUNT' and hc.cust_account_id = bk.cust_account_id);
318 exception
319 when others then null;
320 end;
321
322 wf_engine.setitemattrtext( itemtype => itemtype,
323 itemkey => itemkey,
324 aname => 'PARTY_ID',
325 avalue => l_party_id);
326
327 wf_engine.setitemattrtext( itemtype => itemtype,
328 itemkey => itemkey,
329 aname => 'PARTY_NAME',
330 avalue => l_party_name);
331
332 wf_engine.setitemattrtext( itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'ACCOUNT_NUMBER',
335 avalue => l_account_number);
336 -- bug 15935804 end...
337
338 ELSE
339 null;
340 -- result := 'COMPLETE';
341 END IF;
342 --DBMS_OUTPUT.PUT_LINE('*');
343 -- Get manager
344 SELECT user_name INTO l_user_name from JTF_RS_RESOURCE_EXTNS
345 WHERE user_id = p_user_id;
346
347 OPEN C_MANAGER(p_user_id);
348 FETCH C_MANAGER INTO l_manager_id, l_manager_name;
349 IF C_MANAGER%NOTFOUND THEN
350 l_manager_id := p_user_id;
351 l_manager_name := l_user_name;
352 END IF;
353 CLOSE C_MANAGER;
354 --DBMS_OUTPUT.PUT_LINE('**');
355 wf_engine.setitemattrnumber( itemtype => itemtype,
356 itemkey => itemkey,
357 aname => 'DELINQUENCY_ID',
358 avalue => p_delinquency_id);
359
360 wf_engine.setitemattrnumber( itemtype => itemtype,
361 itemkey => itemkey,
362 aname => 'MANAGER_ID',
363 avalue => l_manager_id);
364
365 wf_engine.setitemattrtext( itemtype => itemtype,
366 itemkey => itemkey,
367 aname => 'MANAGER_NAME',
368 avalue => l_manager_name);
369
370 wf_engine.setitemattrtext( itemtype => itemtype,
371 itemkey => itemkey,
372 aname => 'REQUESTER_NAME',
373 avalue => l_user_name);
374
375 wf_engine.setitemattrtext( itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'REQUESTER_ID',
378 avalue => p_user_id);
379
380 wf_engine.startprocess( itemtype => itemtype,
381 itemkey => itemkey);
382
383 wf_engine.ItemStatus( itemtype => ItemType,
384 itemkey => ItemKey,
385 status => l_return_status,
386 result => l_result);
387 --DBMS_OUTPUT.PUT_LINE('***'||l_return_status);
388 if (l_return_status = 'COMPLETE') OR (l_return_status = 'ACTIVE') THEN
389 x_return_status := 'S';
390 commit;
391 else
392 x_return_status := 'F';
393 end if;
394
395
396 -- Standard call to get message count and if count is 1, get message info.
397 FND_MSG_PUB.Count_And_Get
398 ( p_count => x_msg_count,
399 p_data => x_msg_data
400 );
401
402 EXCEPTION
403 WHEN FND_API.G_EXC_ERROR THEN
404 as_utility_pvt.HANDLE_EXCEPTIONS(
405 P_API_NAME => L_API_NAME
406 ,P_PKG_NAME => G_PKG_NAME
407 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
408 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
409 ,X_MSG_COUNT => X_MSG_COUNT
410 ,X_MSG_DATA => X_MSG_DATA
411 ,X_RETURN_STATUS => X_RETURN_STATUS);
412
413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
414 as_utility_pvt.HANDLE_EXCEPTIONS(
415 P_API_NAME => L_API_NAME
416 ,P_PKG_NAME => G_PKG_NAME
417 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
418 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
419 ,X_MSG_COUNT => X_MSG_COUNT
420 ,X_MSG_DATA => X_MSG_DATA
421 ,X_RETURN_STATUS => X_RETURN_STATUS);
422
423 WHEN OTHERS THEN
424 as_utility_pvt.HANDLE_EXCEPTIONS(
425 P_API_NAME => L_API_NAME
426 ,P_PKG_NAME => G_PKG_NAME
427 ,P_EXCEPTION_LEVEL => as_utility_pvt.G_EXC_OTHERS
428 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PUB
429 ,X_MSG_COUNT => X_MSG_COUNT
430 ,X_MSG_DATA => X_MSG_DATA
431 ,X_RETURN_STATUS => X_RETURN_STATUS);
432 ----------------------------------
433 END start_workflow;
434
435 -- procedure update_approval_status -----------------------------
436 PROCEDURE update_approval_status(
437 itemtype IN VARCHAR2,
438 itemkey IN VARCHAR2,
439 actid IN NUMBER,
440 funcmode IN VARCHAR2,
441 result OUT NOCOPY VARCHAR2
442 ) is
443
444 l_manager_name varchar2(60);
445 l_delinquency_id number(30);
446 l_repossession_id number;
447 l_litigation_id number;
448 l_writeoff_id number;
449 l_bankruptcy_id number;
450 l_responder varchar2(100);
451 l_text_value varchar2(2000);
452 l_del_type varchar2(100);
453 l_del_type_id varchar2(100);
454 l_dummy varchar2(1);
455 l_api_name VARCHAR2(100) := 'update_approval_status';
456 l_errmsg_name VARCHAR2(30);
457 L_API_ERROR EXCEPTION;
458
459 -- Bug 6936225 by Ehuh
460 CURSOR c_get_acct (in_bankruptcy_id number) IS
461 select cust_account_id
462 from hz_cust_accounts hz
463 where party_id = (select party_id from iex_bankruptcies
464 where bankruptcy_id = in_bankruptcy_id)
465 and hz.cust_account_id = (select decode(bc.attribute15,'ACCOUNT',bc.cust_account_id,hz.cust_account_id) from iex_bankruptcies bc
466 where bankruptcy_id = in_bankruptcy_id) -- add by bug 15935812
467 and status = 'A';
468
469 TYPE ACCT_ID_TBL_TYPE is Table of NUMBER
470 INDEX BY BINARY_INTEGER;
471 l_acct_id_tbl ACCT_ID_TBL_TYPE;
472 iIdx NUMBER := 0;
473 l_account_id VARCHAR2(10);
474 l_return_status VARCHAR2(20);
475 l_msg_count NUMBER;
476 l_msg_data VARCHAR2(2000);
477
478 BEGIN
479
480 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: Starting update_approval_status......= ');
481
482 if funcmode <> 'RUN' then
483 result := wf_engine.eng_null;
484 return;
485 end if;
486
487 l_manager_name := wf_engine.GetItemAttrText(
488 itemtype => itemtype,
489 itemkey => itemkey,
490 aname => 'MANAGER_NAME');
491
492 l_delinquency_id := wf_engine.GetItemAttrNumber(
493 itemtype => itemtype,
494 itemkey => itemkey,
495 aname => 'DELINQUENCY_ID');
496
497 l_del_type := wf_engine.GetItemAttrText(
498 itemtype => itemtype,
499 itemkey => itemkey,
500 aname => 'DEL_TYPE');
501
502 l_repossession_id := wf_engine.GetItemAttrNumber(
503 itemtype => itemtype,
504 itemkey => itemkey,
505 aname => 'REPOSSESSION_ID');
506
507 l_litigation_id := wf_engine.GetItemAttrNumber(
508 itemtype => itemtype,
509 itemkey => itemkey,
510 aname => 'LITIGATION_ID');
511
512 l_writeoff_id := wf_engine.GetItemAttrNumber(
513 itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'WRITEOFF_ID');
516
517 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
518 itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'BANKRUPTCY_ID');
521
522 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: l_del_type ......= '||l_del_type);
523
524 IF l_del_type = 'Delinquency' THEN
525 update IEX_DELINQUENCIES_ALL
526 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
527 where delinquency_id = l_delinquency_id;
528 ELSIF l_del_type = 'Repossession' THEN
529 update IEX_REPOSSESSIONS
530 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
531 where repossession_id = l_repossession_id;
532 ELSIF l_del_type = 'Litigation' THEN
533 update IEX_LITIGATIONS
534 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
535 where litigation_id = l_litigation_id;
536 ELSIF l_del_type = 'Writeoff' THEN
537 update IEX_WRITEOFFS
538 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
539 where writeoff_id = l_writeoff_id;
540 ELSIF l_del_type = 'Bankrupt' THEN
541 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: Bankrutpcy Start ......= ');
542 update IEX_BANKRUPTCIES
543 set CREDIT_HOLD_APPROVED_FLAG = 'Y'
544 where bankruptcy_id = l_bankruptcy_id;
545 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: Bankrutpcy End ......= ');
546
547 -- Bug 6936225 by Ehuh Starting....
548 begin
549 Open c_get_acct(l_bankruptcy_id);
550 Loop
551 Fetch c_get_acct into l_account_id;
552
553 If (C_GET_ACCT%NOTFOUND) THEN
554 if (iIdx = 0) then
555 iex_debug_pub.logmessage('IEX:no acct');
556 end if;
557 exit;
558 else
559 iIdx := iIdx + 1;
560 l_acct_id_tbl(iIdx) := l_account_id;
561 iex_debug_pub.logmessage ('IEX: INDEX ......= '||iIdx);
562 iex_debug_pub.logmessage ('IEX: l_account_id ......= '||l_account_id);
563 end if;
564 End Loop;
565
566 Close C_GET_ACCT;
567 --
568 For i in 1..iIdx loop
569 iex_debug_pub.logmessage ('IEX: l_acct_id_tbl(i) .....= '||l_acct_id_tbl(i));
570 IEX_CREDIT_HOLD_API.UPDATE_CREDIT_HOLD
571 (p_api_version => 1.0,
572 p_init_msg_list => 'T',
573 p_commit => 'T',
574 p_account_id => l_acct_id_tbl(i),
575 p_site_id => null ,
576 p_credit_hold => 'Y',
577 x_return_status => l_return_status,
578 x_msg_count => l_msg_count,
579 x_msg_data => l_msg_data);
580
581 iex_debug_pub.logmessage ('IEX_CREDIT_HOLD: l_return_status .....= '||l_return_status);
582 End loop;
583
584 exception
585 when others then
586 iex_debug_pub.logmessage ('Exception from 2nd Begin...... ');
587 null;
588 end;
589 -- Bug 6936225 by Ehuh Ending....
590
591 ELSE
592 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: Else ......= ');
593 null;
594 END IF;
595
596 result := 'COMPLETE';
597
598 EXCEPTION
599 WHEN L_API_ERROR then
600 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: l_errmsg_name......= '||l_errmsg_name);
601 WF_CORE.Raise(l_errmsg_name);
602 WHEN OTHERS THEN
603 iex_debug_pub.logmessage ('IEX_WF_DEL_REQ_CREDIT_PUB: Exception Others ......= ');
604 WF_CORE.Context('IEX_DEL_REQ_CREDIT_WF_PUB', 'Reject_Contract',
605 itemtype, itemkey, actid, funcmode);
606 RAISE;
607 END update_approval_status;
608
609 -- procedure update_rejection_status -----------------------------
610 procedure update_rejection_status(
611 itemtype IN VARCHAR2,
612 itemkey IN VARCHAR2,
613 actid IN NUMBER,
614 funcmode IN VARCHAR2,
615 result OUT NOCOPY VARCHAR2
616 ) is
617
618 l_manager_name varchar2(60);
619 l_delinquency_id number(30);
620 l_repossession_id number;
621 l_litigation_id number;
622 l_writeoff_id number;
623 l_bankruptcy_id number;
624 l_responder varchar2(100);
625 l_text_value varchar2(2000);
626 l_del_type varchar2(100);
627 l_del_type_id varchar2(100);
628 l_api_name VARCHAR2(100) := 'update_rejection_status';
629 l_errmsg_name VARCHAR2(30);
630 L_API_ERROR EXCEPTION;
631
632 BEGIN
633 l_manager_name := wf_engine.GetItemAttrText(
634 itemtype => itemtype,
635 itemkey => itemkey,
636 aname => 'MANAGER_NAME');
637
638 l_delinquency_id := wf_engine.GetItemAttrNumber(
639 itemtype => itemtype,
640 itemkey => itemkey,
641 aname => 'DELINQUENCY_ID');
642
643 l_del_type := wf_engine.GetItemAttrText(
644 itemtype => itemtype,
645 itemkey => itemkey,
646 aname => 'DEL_TYPE');
647
648 l_repossession_id := wf_engine.GetItemAttrNumber(
649 itemtype => itemtype,
650 itemkey => itemkey,
651 aname => 'REPOSSESSION_ID');
652
653 l_litigation_id := wf_engine.GetItemAttrNumber(
654 itemtype => itemtype,
655 itemkey => itemkey,
656 aname => 'LITIGATION_ID');
657
658 l_writeoff_id := wf_engine.GetItemAttrNumber(
659 itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'WRITEOFF_ID');
662
663 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
664 itemtype => itemtype,
665 itemkey => itemkey,
666 aname => 'BANKRUPTCY_ID');
667
668 IF l_del_type = 'Delinquency' THEN
669 update IEX_DELINQUENCIES_ALL
670 set CREDIT_HOLD_APPROVED_FLAG = 'N'
671 where delinquency_id = l_delinquency_id;
672 ELSIF l_del_type = 'Repossession' THEN
673 update IEX_REPOSSESSIONS
674 set CREDIT_HOLD_APPROVED_FLAG = 'N'
675 where repossession_id = l_repossession_id;
676 ELSIF l_del_type = 'Litigation' THEN
677 update IEX_LITIGATIONS
678 set CREDIT_HOLD_APPROVED_FLAG = 'N'
679 where litigation_id = l_litigation_id;
680 ELSIF l_del_type = 'Writeoff' THEN
681 update IEX_WRITEOFFS
682 set CREDIT_HOLD_APPROVED_FLAG = 'N'
683 where writeoff_id = l_writeoff_id;
684 ELSIF l_del_type = 'Bankrupt' THEN
685 update IEX_BANKRUPTCIES
686 set CREDIT_HOLD_APPROVED_FLAG = 'N'
687 where bankruptcy_id = l_bankruptcy_id;
688 ELSE
689 null;
690 END IF;
691
692 result := 'COMPLETE';
693
694 EXCEPTION
695 WHEN l_API_ERROR then
696 WF_CORE.Raise(l_errmsg_name);
697 WHEN OTHERS THEN
698 WF_CORE.Context('IEX_DEL_REQ_CREDIT_WF_PUB', 'Reject_Contract',
699 itemtype, itemkey, actid, funcmode);
700 RAISE;
701
702 END update_rejection_status;
703
704 END IEX_WF_DEL_REQ_CREDIT_PUB;