[Home] [Help]
PACKAGE BODY: APPS.IEX_WF_BAN_STATUS_PUB
Source
1 PACKAGE BODY IEX_WF_BAN_STATUS_PUB AS
2 /* $Header: iexwfbsb.pls 120.17.12020000.8 2013/01/31 14:03:33 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 VARCHAR2(30);
10 PG_DEBUG NUMBER;
11 Function get_Bankruptcy_Status (p_party_id IN NUMBER,
12 p_cust_account_id IN NUMBER,
13 p_date IN DATE) RETURN VARCHAR2 AS
14
15
16 cursor c_get_party_status(c_party_id number, c_date date) Is
17
18 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
19 where party_id = c_party_id
20 and nvl(attribute15,'A') <> 'ACCOUNT'
21 and trunc(creation_date) <= c_date
22 and nvl(disposition_code,'A') not in ('DISMISSED' , 'WITHDRAWN')
23
24 UNION
25
26 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
27 where party_id = c_party_id
28 and nvl(attribute15,'A') <> 'ACCOUNT'
29 and trunc(creation_date) <= c_date
30 and nvl(disposition_code,'A') = 'DISMISSED'
31 and trunc(nvl(dismissal_date,last_update_date)) > c_date
32
33 UNION
34
35 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
36 where party_id = c_party_id
37 and nvl(attribute15,'A') <> 'ACCOUNT'
38 and trunc(creation_date) <= c_date
39 and nvl(disposition_code,'A') = 'WITHDRAWN'
40 and trunc(nvl(withdraw_date,last_update_date)) > c_date
41
42 order by bankruptcy_id desc;
43
44
45 cursor c_get_acct_status (c_party_id number,c_cust_account_id number,c_date date) Is
46
47 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
48 where party_id = c_party_id
49 and nvl(cust_account_id,-1) = c_cust_account_id
50 and nvl(attribute15,'A') = 'ACCOUNT'
51 and trunc(creation_date) <= c_date
52 and nvl(disposition_code,'A') not in ('DISMISSED' , 'WITHDRAWN')
53
54 UNION
55
56 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
57 where party_id = c_party_id
58 and nvl(cust_account_id,-1) = c_cust_account_id
59 and nvl(attribute15,'A') = 'ACCOUNT'
60 and trunc(creation_date) <= c_date
61 and nvl(disposition_code,'A') = 'DISMISSED'
62 and trunc(nvl(dismissal_date,last_update_date)) > c_date
63
64 UNION
65
66 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
67 where party_id = c_party_id
68 and nvl(cust_account_id,-1) = c_cust_account_id
69 and nvl(attribute15,'A') = 'ACCOUNT'
70 and trunc(creation_date) <= c_date
71 and nvl(disposition_code,'A') = 'WITHDRAWN'
72 and trunc(nvl(withdraw_date,last_update_date)) > c_date
73
74 order by bankruptcy_id desc;
75
76 l_bankruptcy_id number := 0;
77 l_flag varchar2(02):= 'NN';
78 l_status varchar2(02):= null;
79 l_errmsg varchar2(1000);
80
81 Begin
82
83 iex_debug_pub.logmessage('IEX Start Getting Bankrutpcy Status API .......');
84
85
86 begin
87
88 Open c_get_party_status(p_party_id,p_date);
89 Fetch c_get_party_status into l_bankruptcy_id,l_flag;
90
91 iex_debug_pub.logmessage('IEX get_party_status : BankruptcyID = :'||l_bankruptcy_id||' Turn Invoicing Y/N = :'||l_flag);
92
93 if c_get_party_status%NOTFOUND then
94 iex_debug_pub.logmessage('No Party level Bankruptcy Exist..');
95 null;
96 else
97 if l_flag = 'Y' then
98 l_status := 'PB';
99 else
100 l_status := 'PN';
101 end if;
102 Return l_status;
103 end if;
104
105 Close c_get_party_status;
106
107 exception
108 when others then
109 l_errmsg := SQLERRM;
110 iex_debug_pub.LogMessage('IEX get_party_status ..Excetion Error :' || l_errmsg ||' and l_flag = '||l_flag);
111 null;
112 end;
113
114 begin
115
116 Open c_get_acct_status(p_party_id,p_cust_account_id,p_date);
117 Fetch c_get_acct_status into l_bankruptcy_id,l_flag;
118
119 iex_debug_pub.logmessage('IEX get_acct_status : BankruptcyID = :'||l_bankruptcy_id||' Turn Invoicing Y/N = :'||l_flag);
120
121
122 if c_get_acct_status%NOTFOUND then
123 iex_debug_pub.logmessage('No Account level Bankruptcy Exist..');
124 null;
125 else
126 if l_flag = 'Y' then
127 l_status := 'AB';
128 else
129 l_status := 'AN';
130 end if;
131 Return l_status;
132 end if;
133
134 Close c_get_acct_status;
135
136 exception
137 When others then
138 l_errmsg := SQLERRM;
139 iex_debug_pub.LogMessage('IEX get_TurnInvOff ..Excetion Error :' || l_errmsg ||' and l_flag = '||l_flag);
140 null;
141 end;
142
143 l_status := 'NN';
144 Return l_status;
145
146 End;
147
148 Function get_TurnInvOff (p_party_id IN NUMBER,
149 p_cust_account_id IN NUMBER,
150 p_date IN DATE) RETURN VARCHAR2 AS
151
152 cursor c_get_flag(c_party_id number, c_cust_account_id number,c_date date) Is
153 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
154 where party_id = c_party_id
155 and nvl(cust_account_id,c_cust_account_id) = c_cust_account_id
156 and trunc(creation_date) <= c_date
157 and nvl(disposition_code,'A') not in ('DISMISSED' , 'WITHDRAWN')
158 UNION
159 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
160 where party_id = c_party_id
161 and nvl(cust_account_id,c_cust_account_id) = c_cust_account_id
162 and trunc(creation_date) <= c_date
163 and nvl(disposition_code,'A') = 'DISMISSED'
164 and trunc(nvl(dismissal_date,last_update_date)) > c_date
165 UNION
166 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
167 where party_id = c_party_id
168 and nvl(cust_account_id,c_cust_account_id) = c_cust_account_id
169 and trunc(creation_date) <= c_date
170 and nvl(disposition_code,'A') = 'WITHDRAWN'
171 and trunc(nvl(withdraw_date,last_update_date)) > c_date
172 UNION
173 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
174 where party_id = c_party_id
175 and trunc(creation_date) <= c_date
176 and nvl(attribute15,'A') = 'PARTY'
177 and nvl(disposition_code,'A') not in ('DISMISSED' , 'WITHDRAWN')
178 UNION
179 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
180 where party_id = c_party_id
181 and trunc(creation_date) <= c_date
182 and nvl(attribute15,'A') = 'PARTY'
183 and nvl(disposition_code,'A') = 'DISMISSED'
184 and trunc(nvl(dismissal_date,last_update_date)) > c_date
185 UNION
186 select bankruptcy_id,turn_off_invoicing_yn from iex_bankruptcies
187 where party_id = c_party_id
188 and trunc(creation_date) <= c_date
189 and nvl(attribute15,'A') = 'PARTY'
190 and NVL(disposition_code,'A') = 'WITHDRAWN'
191 and trunc(nvl(withdraw_date,last_update_date)) > c_date
192 order by bankruptcy_id desc;
193
194
195 l_bankruptcy_id number;
196 l_flag varchar2(01):= 'N';
197 l_errmsg varchar2(1000);
198
199
200 begin
201 iex_debug_pub.logmessage('IEX Start get_TurnInvOff Bankruptcy Workflow..');
202
203 Open c_get_flag(p_party_id,p_cust_account_id,p_date);
204 Fetch c_get_flag into l_bankruptcy_id,l_flag;
205
206 iex_debug_pub.logmessage('IEX get_TurnInvOff : BankruptcyID = :'||l_bankruptcy_id||' Y/N = :'||l_flag);
207
208 if c_get_flag%NOTFOUND then
209 iex_debug_pub.logmessage('IEX get_TurnInvOff : c_getflag%NOTFOUND..');
210 null;
211 end if;
212
213 Close c_get_flag;
214
215 iex_debug_pub.logmessage('IEX get_TurnInvOff : Returning Y/N = :'||l_flag);
216 return l_flag;
217
218 exception
219 When others then
220 l_errmsg := SQLERRM;
221 iex_debug_pub.LogMessage('IEX get_TurnInvOff ..Excetion Error :' || l_errmsg ||' and l_flag = '||l_flag);
222 return l_flag;
223 end;
224
225
226 PROCEDURE start_workflow
227 (
228 p_api_version IN NUMBER DEFAULT 1.0,
229 p_init_msg_list IN VARCHAR2 ,
230 p_commit IN VARCHAR2 ,
231 p_user_id IN NUMBER,
232 p_delinquency_id IN NUMBER,
233 p_party_id IN NUMBER,
234 p_bankruptcy_id IN NUMBER, --Added for bug 7661724 gnramasa 8th Jan 09
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2
238 )
239 IS
240 l_result VARCHAR2(10);
241 itemtype VARCHAR2(30);
242 itemkey VARCHAR2(30);
243 workflowprocess VARCHAR2(30);
244 l_init_msg_list varchar2(1);
245 l_user_id NUMBER;
246 l_user_name VARCHAR2(60);
247 l_manager_id NUMBER;
248 l_manager_name VARCHAR2(60);
249 l_party_name VARCHAR2(60);
250 l_bankruptcy_id number; --Added for bug 3659342 by gnramasa
251
252 l_error_msg VARCHAR2(2000);
253 l_return_status VARCHAR2(20);
254 l_msg_count NUMBER;
255 l_msg_data VARCHAR2(2000);
256 l_api_name VARCHAR2(100);
257 l_api_version_number NUMBER;
258 l_account_number varchar2(30) := '';
259
260 CURSOR c_manager(p_user_id NUMBER) IS
261 SELECT b.user_id, b.user_name
262 FROM JTF_RS_RESOURCE_EXTNS a
263 , JTF_RS_RESOURCE_EXTNS b
264 WHERE b.source_id = a.source_mgr_id
265 AND a.user_id = p_user_id;
266
267 BEGIN
268 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
269 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..');
270 end if;
271
272 -- Standard Start of API savepoint
273 SAVEPOINT START_WORKFLOW;
274
275 l_api_name := 'START_WORKFLOW';
276 l_api_version_number := 1.0;
277 l_init_msg_list :=FND_API.G_FALSE;
278
279 -- Standard call to check for call compatibility.
280 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
281 p_api_version,
282 l_api_name,
283 G_PKG_NAME)
284 THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287
288
289 -- Initialize message list if p_init_msg_list is set to TRUE.
290 IF FND_API.to_Boolean( l_init_msg_list )
291 THEN
292 FND_MSG_PUB.initialize;
293 END IF;
294
295
296 -- Initialize API return status to SUCCESS
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 --Start bug 3659342 by gnramasa
300 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
301 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..p_bankruptcy_id=' ||p_bankruptcy_id);
302 end if;
303
304 /*
305 SELECT TO_CHAR(IEX_DEL_WF_S.NEXTVAL) INTO itemkey FROM dual;
306 itemkey := 'BANST'||TO_CHAR(p_delinquency_id)||itemkey;
307 */
308 itemkey := to_char(p_bankruptcy_id);
309 --End bug 3659342 by gnramasa
310
311 itemtype := 'IEXBANST';
312 workflowprocess := 'BANKRUPT_STATUS';
313
314 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
315 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..P_user_id=' ||p_user_id);
316 end if;
317
318 begin
319 -- Get manager
320 SELECT user_name INTO l_user_name from JTF_RS_RESOURCE_EXTNS
321 WHERE user_id = p_user_id;
322 exception
323 when others then
324 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow usernme exception');
325 end;
326
327 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
328 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..P_Party_id=' ||p_party_id);
329 end if;
330
331 begin
332 SELECT party_name INTO l_party_name from hz_parties
333 where party_id = p_party_id;
334 exception
335 when others then
336 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow party_name exception');
337 end;
338
339 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
340 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..P_Useerid=' ||p_user_id);
341 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..username=' ||l_user_name);
342 end if;
343
344 begin
345 OPEN C_MANAGER(p_user_id);
346 FETCH C_MANAGER INTO l_manager_id, l_manager_name;
347 IF C_MANAGER%NOTFOUND THEN
348 l_manager_id := p_user_id;
349 l_manager_name := l_user_name;
350 END IF;
351 CLOSE C_MANAGER;
352 exception
353 when others then
354 l_manager_id := p_user_id;
355 l_manager_name := l_user_name;
356 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow manager exception');
357 end;
358 /*
359 select b.bankruptcy_id bankruptcy_id
360 into l_bankruptcy_id
361 from iex_delinquencies d, iex_bankruptcies b
362 where d.delinquency_id = b.delinquency_id
363 and d.delinquency_id = p_delinquency_id;
364 */
365 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
366 iex_debug_pub.logmessage('IEX Start Bankruptcy Workflow..getting bankruptcy id=' ||l_user_name);
367 end if;
368
369 /*
370 select bankruptcy_id bankruptcy_id
371 into l_bankruptcy_id
372 from iex_bankruptcies
373 where party_id= p_party_id
374 and DISPOSITION_CODE is NULL;
375 */
376
377 -- bug 15935804 begin...
378 begin
379 select account_number into l_account_number from hz_cust_accounts hc
380 where exists (select 1 from iex_bankruptcies bk where bankruptcy_id = p_bankruptcy_id
381 and attribute15 = 'ACCOUNT' and hc.cust_account_id = bk.cust_account_id);
382 exception
383 when others then null;
384 end;
385 -- bug 15935804 end ...
386
387 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
388 --iex_debug_pub.logmessage('IEX-4 Start Invoking BK Id..'||l_bankruptcy_id);
389 iex_debug_pub.logmessage('IEX-4 Start Invoking BK Id..'||p_bankruptcy_id);
390 iex_debug_pub.logmessage('IEX-4 Start Invoking Manager Name..'||l_manager_name);
391 iex_debug_pub.logmessage('IEX-4 Start Invoking requesterID ..'||p_user_id);
392 iex_debug_pub.logmessage('IEX-4 Start Invoking requesterName ..'||l_user_name);
393 iex_debug_pub.logmessage('IEX-4 Start Invoking DelinquencyID ..'||p_delinquency_id);
394 iex_debug_pub.logmessage('IEX-4 Start Invoking approverId ..'||l_manager_id);
395 iex_debug_pub.logmessage('IEX-4 Start Invoking approverName ..'||l_manager_name);
396 iex_debug_pub.logmessage('IEX-4 Start Invoking partyid ..'||p_party_id);
397 iex_debug_pub.logmessage('IEX-4 Start Invoking partyName ..'||l_party_name);
398 iex_debug_pub.logmessage('IEX-4 Start Invoking ItemType ..'||itemtype);
399 iex_debug_pub.logmessage('IEX-4 Start Invoking Itemkey ..'||itemkey);
400 end if;
401
402 wf_engine.createprocess ( itemtype => itemtype,
403 itemkey => itemkey,
404 process => 'BANKRUPT_STATUS');
405
406 wf_engine.setitemattrnumber( itemtype => itemtype,
407 itemkey => itemkey,
408 aname => 'DELINQUENCY_ID',
409 avalue => p_delinquency_id);
410
411 wf_engine.setitemattrnumber( itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'MANAGER_ID',
414 avalue => l_manager_id);
415
416 wf_engine.setitemattrtext( itemtype => itemtype,
417 itemkey => itemkey,
418 aname => 'MANAGER_NAME',
419 avalue => l_manager_name);
420
421 wf_engine.setitemattrnumber( itemtype => itemtype,
422 itemkey => itemkey,
423 aname => 'APPROVER_ID',
424 avalue => l_manager_id);
425
426 wf_engine.setitemattrtext( itemtype => itemtype,
427 itemkey => itemkey,
428 aname => 'APPROVER_NAME',
429 avalue => l_manager_name);
430
431 wf_engine.setitemattrtext( itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'REQUESTER_NAME',
434 avalue => l_user_name);
435
436 wf_engine.setitemattrnumber( itemtype => itemtype,
437 itemkey => itemkey,
438 aname => 'REQUESTER_ID',
439 avalue => p_user_id);
440
441 wf_engine.setitemattrnumber( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'PARTY_ID',
444 avalue => p_party_id);
445
446 wf_engine.setitemattrtext( itemtype => itemtype,
447 itemkey => itemkey,
448 aname => 'PARTY_NAME',
449 avalue => l_party_name);
450
451 wf_engine.setitemattrnumber( itemtype => itemtype,
452 itemkey => itemkey,
453 aname => 'BANKRUPTCY_ID',
454 -- avalue => l_bankruptcy_id);
455 avalue => p_bankruptcy_id);
456
457 wf_engine.setitemattrtext( itemtype => itemtype,
458 itemkey => itemkey,
459 aname => 'ACCOUNT_NUMBER',
460 avalue => l_account_number);
461
462 -- DBMS_OUTPUT.PUT_LINE('Before START PROCESS');
463
464 wf_engine.startprocess( itemtype => itemtype,
465 itemkey => itemkey);
466 -- DBMS_OUTPUT.PUT_LINE('After START PROCESS');
467
468 wf_engine.ItemStatus( itemtype => ItemType,
469 itemkey => ItemKey,
470 status => l_return_status,
471 result => l_result);
472
473 iex_debug_pub.logmessage('IEX-7 Return Status ='||l_return_status);
474
475 if (l_return_status in ('COMPLETE', 'ACTIVE')) THEN
476 x_return_status := 'S';
477 commit;
478 else
479 x_return_status := 'F';
480 end if;
481
482 -- DBMS_OUTPUT.PUT_LINE('GET ITEM STATUS = ' || l_return_status);
483 -- DBMS_OUTPUT.PUT_LINE('GET ITEM result = ' || l_result);
484
485
486 -- Standard call to get message count and if count is 1, get message info.
487 FND_MSG_PUB.Count_And_Get
488 ( p_count => x_msg_count,
489 p_data => x_msg_data
490 );
491 --Start bug 6717204 gnramasa 11th Jan 08
492 EXCEPTION
493 WHEN FND_API.G_EXC_ERROR THEN
494 ROLLBACK TO START_WORKFLOW;
495 x_return_status := FND_API.G_RET_STS_ERROR;
496 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
497
498 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
499 ROLLBACK TO START_WORKFLOW;
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
501 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
502
503 WHEN OTHERS THEN
504 ROLLBACK TO START_WORKFLOW;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
507 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
508 END IF;
509 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
510 --End bug 6717204 gnramasa 11th Jan 08
511 ----------------------------------
512 END start_workflow;
513
514 -- procedure update_approval_status -----------------------------
515 PROCEDURE update_approval_status(
516 itemtype IN VARCHAR2,
517 itemkey IN VARCHAR2,
518 actid IN NUMBER,
519 funcmode IN VARCHAR2,
520 result OUT NOCOPY VARCHAR2
521 ) is
522
523 l_api_version number;
524 l_init_msg_list varchar2(1);
525 l_commit varchar2(1);
526 l_manager_name varchar2(60);
527 l_delinquency_id number;
528 l_bankruptcy_id number;
529 l_party_id number;
530 l_party_name varchar2(30);
531 l_responder varchar2(100);
532 l_text_value varchar2(2000);
533 l_forward_to_username varchar2(100);
534 l_dummy varchar2(1);
535 l_errmsg_name VARCHAR2(30);
536 L_API_ERROR EXCEPTION;
537
538 l_error_msg VARCHAR2(2000);
539 l_return_status VARCHAR2(20);
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR2(2000);
542 l_api_version_number NUMBER ;
543 l_api_name VARCHAR2(100);
544 l_profile varchar2(01);
545 l_collections_methods varchar2(20) := NULL;
546
547 BEGIN
548
549 l_api_version_number := 1.0;
550 l_api_name := 'update_approval_status';
551
552 if funcmode <> 'RUN' then
553 result := wf_engine.eng_null;
554 return;
555 end if;
556
557 l_profile := NVL(fnd_profile.value('IEX_STRY_CREATE_BANKRUPTCY'), 'Y');
558
559 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
560 iex_debug_pub.logmessage ('update_approval_status start');
561 iex_debug_pub.logmessage ('Profile IEX_STRY_CREATE_BANKRUPTCY == '||l_profile);
562 END IF;
563
564 -- bug 15990736 ... begin
565 begin
566 select collections_methods into l_collections_methods from iex_questionnaire_items;
567 exception
568 when others then null;
569 end;
570 iex_debug_pub.logmessage ('Collections Methods ==> '||l_collections_methods);
571 -- bug 15990736 ... end
572
573 -- if l_profile = 'Y' then -- bug 15990736
574 if (l_profile = 'Y') and (l_collections_methods <> 'DUNNING') then -- bug 15990736
575
576 --DBMS_OUTPUT.PUT_LINE('update_approval_status');
577
578 l_manager_name := wf_engine.GetItemAttrText(
579 itemtype => itemtype,
580 itemkey => itemkey,
581 aname => 'MANAGER_NAME');
582
583 l_delinquency_id := wf_engine.GetItemAttrNumber(
584 itemtype => itemtype,
585 itemkey => itemkey,
586 aname => 'DELINQUENCY_ID');
587
588 l_party_id := wf_engine.GetItemAttrNumber(
589 itemtype => itemtype,
590 itemkey => itemkey,
591 aname => 'PARTY_ID');
592
593 l_party_name := wf_engine.GetItemAttrNumber(
594 itemtype => itemtype,
595 itemkey => itemkey,
596 aname => 'PARTY_NAME');
597 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
598 iex_debug_pub.logmessage ('update_approval_status start, before getting the bankruptcy id');
599 END IF;
600
601 --Start bug 6359342 gnramasa 23-Aug-07
602 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
603 itemtype => itemtype,
604 itemkey => itemkey,
605 aname => 'BANKRUPTCY_ID');
606
607 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
608 iex_debug_pub.logmessage ('update_approval_status start, after getting the bankruptcy id');
609 END IF;
610
611 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
612 iex_debug_pub.logmessage ('update_approval_status: ' || 'l_delinquency_id : '||l_delinquency_id);
613 iex_debug_pub.logmessage ('update_approval_status: ' || 'l_bankruptcy_id : '||l_bankruptcy_id);
614 iex_debug_pub.logmessage ('update_approval_status: ' || 'l_party_id : '||l_party_id);
615 END IF;
616
617
618 Create_strategy(
619 p_api_version => l_api_version_number,
620 p_init_msg_list => l_init_msg_list,
621 p_commit => l_commit,
622 X_RETURN_STATUS => l_return_status,
623 X_MSG_COUNT => l_msg_count,
624 X_MSG_DATA => l_msg_data,
625 p_delinquency_id => l_delinquency_id,
626 p_bankruptcy_id => l_bankruptcy_id,
627 p_party_id => l_party_id
628 );
629
630 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
631 iex_debug_pub.logmessage ('update_approval_status:'||l_return_status);
632 END IF;
633 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
634 RAISE L_API_ERROR;
635 END IF;
636 end if;
637
638 --End bug 6359342 gnramasa 23-Aug-07
639
640
641 -- update Bankrupt related record here
642 -- Set "no Contact" flag in TCA
643 -- Make everything for entire customer to 'Delinquent'
644 -- Set Delinquency Status to 'Bankruptcy'
645 -- Create a 'Stop Invoicing' notification to some on the A/R side
646 -- Start Bankruptcy Strategy
647 /*
648 turnoff_bankrupt_collections
649 ( p_api_version => l_api_version_number,
650 p_init_msg_list => l_init_msg_list,
651 p_commit => l_commit,
652 p_party_id => l_party_id,
653 x_return_status => l_return_status,
654 x_msg_count => l_msg_count,
655 x_msg_data => l_msg_data);
656 */
657 result := 'COMPLETE';
658
659 -- bug 14637604
660 begin
661 update IEX_BANKRUPTCIES set approval_date = sysdate
662 where bankruptcy_id = l_bankruptcy_id;
663
664 commit;
665 exception
666 when others then
667 iex_debug_pub.logmessage('IEX- approval date updating Exception....');
668 end;
669
670 EXCEPTION
671 WHEN L_API_ERROR then
672 WF_CORE.Raise(l_errmsg_name);
673 WHEN OTHERS THEN
674 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'update_approval_status',
675 itemtype, itemkey, actid, funcmode);
676 RAISE;
677 END update_approval_status;
678
679 -- procedure update_rejection_status -----------------------------
680 procedure update_rejection_status(
681 itemtype IN VARCHAR2,
682 itemkey IN VARCHAR2,
683 actid IN NUMBER,
684 funcmode IN VARCHAR2,
685 result OUT NOCOPY VARCHAR2
686 ) is
687
688 l_responder varchar2(100);
689 l_text_value varchar2(2000);
690 l_manager_name varchar2(60);
691 l_delinquency_id number;
692 l_party_id number;
693 l_party_name varchar2(30);
694 l_api_name VARCHAR2(100);
695 l_errmsg_name VARCHAR2(30);
696 L_API_ERROR EXCEPTION;
697 l_att15 varchar2(240):= null;
698 l_cust_account_id number;
699 l_bankruptcy_id number;
700
701 BEGIN
702
703 iex_debug_pub.logmessage('IEX-Reject Bankruptcy Start ..');
704 l_api_name := 'update_rejection_status';
705 l_manager_name := wf_engine.GetItemAttrText(
706 itemtype => itemtype,
707 itemkey => itemkey,
708 aname => 'MANAGER_NAME');
709
710 l_delinquency_id := wf_engine.GetItemAttrNumber(
711 itemtype => itemtype,
712 itemkey => itemkey,
713 aname => 'DELINQUENCY_ID');
714
715 l_party_id := wf_engine.GetItemAttrNumber(
716 itemtype => itemtype,
717 itemkey => itemkey,
718 aname => 'PARTY_ID');
719
720 l_party_name := wf_engine.GetItemAttrNumber(
721 itemtype => itemtype,
722 itemkey => itemkey,
723 aname => 'PARTY_NAME');
724
725 -- add for bug 14637604 Account level Bankruptcy
726 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
727 itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'BANKRUPTCY_ID');
730
731 -- bug 14637604
732 begin
733 select attribute15,cust_account_id into l_att15,l_cust_account_id from iex_bankruptcies where bankruptcy_id = l_bankruptcy_id;
734 exception
735 when others then
736 iex_debug_pub.logmessage('IEX-Rejct Attribute15 Exception....');
737 end;
738 -- fixed a bug 5261811
739 begin
740 iex_debug_pub.logmessage('IEX-Reject Bankruptcy partyid ..'||l_party_id);
741 -- bug 14637604
742 if (l_att15 is not null and l_cust_account_id is not null) then
743 update IEX_BANKRUPTCIES set DISPOSITION_CODE = 'WITHDRAWN',WITHDRAW_DATE = sysdate
744 where bankruptcy_id = l_bankruptcy_id;
745 else
746 update IEX_BANKRUPTCIES
747 set DISPOSITION_CODE = 'WITHDRAWN',WITHDRAW_DATE = sysdate
748 where party_id = l_party_id;
749 -- bug 14637604 and disposition_code is null;
750 end if;
751 commit;
752
753 exception
754 when others then
755 iex_debug_pub.logmessage('IEX-Rejct Exception....');
756 end;
757
758 result := 'COMPLETE';
759
760 EXCEPTION
761 WHEN l_API_ERROR then
762 WF_CORE.Raise(l_errmsg_name);
763 WHEN OTHERS THEN
764 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'update_rejection_status',
765 itemtype, itemkey, actid, funcmode);
766 RAISE;
767
768 END update_rejection_status;
769
770 -- procedure set_no_contact_in_tca -----------------------------
771 PROCEDURE set_no_contact_in_tca(
772 itemtype IN VARCHAR2,
773 itemkey IN VARCHAR2,
774 actid IN NUMBER,
775 funcmode IN VARCHAR2,
776 result OUT NOCOPY VARCHAR2) IS
777
778 l_errmsg_name VARCHAR2(30);
779 L_API_ERROR EXCEPTION;
780 l_init_msg_list VARCHAR2(1);
781 l_api_name VARCHAR2(100);
782 l_api_version_number NUMBER;
783 l_contact_preference_id NUMBER;
784 o_contact_preference_id NUMBER;
785 l_return_status VARCHAR2(30);
786 l_msg_count NUMBER;
787 l_msg_data VARCHAR2(2000);
788 l_object_version_number NUMBER;
789
790 l_party_id NUMBER;
791 l_o_party_id NUMBER;
792 l_p_party_id NUMBER;
793
794 l_contact_preference_rec HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
795
796 CURSOR C_DO_PARTY(p_person_id NUMBER) IS
797 SELECT contact_preference_id FROM HZ_CONTACT_PREFERENCES
798 WHERE CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
799 AND CONTACT_LEVEL_TABLE_ID = P_PERSON_ID
800 AND PREFERENCE_CODE = 'DO';
801
802 CURSOR C_DO_NOT_PARTY(p_person_id NUMBER) IS
803 SELECT contact_preference_id FROM HZ_CONTACT_PREFERENCES
804 WHERE CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
805 AND CONTACT_LEVEL_TABLE_ID = P_PERSON_ID
806 AND PREFERENCE_CODE = 'DO_NOT';
807
808 --Begin Bug#4597394 schekuri 08-Sep-2005
809 --Replaced the view HZ_PARTY_RELATIONSHIPS with HZ_RELATIONSHIPS and
810 --added necessary filter conditions
811 CURSOR C_PARTY(p_org_party_id NUMBER) IS
812 SELECT
813 P.PARTY_ID
814 -- , P.PARTY_NAME
815 FROM
816 HZ_RELATIONSHIPS REL
817 , HZ_PARTIES C
818 , HZ_PARTIES P
819 , HZ_PARTIES O
820 WHERE O.PARTY_TYPE = 'ORGANIZATION'
821 AND O.PARTY_ID = REL.OBJECT_ID
822 AND P.PARTY_TYPE = 'PERSON'
823 AND P.PARTY_ID = REL.SUBJECT_ID
824 AND C.PARTY_TYPE = 'PARTY_RELATIONSHIP'
825 AND REL.PARTY_ID = C.PARTY_ID
826 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
827 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
828 AND REL.DIRECTIONAL_FLAG = 'F'
829 AND O.PARTY_ID = p_org_party_id;
830
831
832 /* CURSOR C_PARTY(p_org_party_id NUMBER) IS
833 SELECT
834 P.PARTY_ID
835 -- , P.PARTY_NAME
836 FROM
837 HZ_PARTY_RELATIONSHIPS REL
838 , HZ_PARTIES C
839 , HZ_PARTIES P
840 , HZ_PARTIES O
841 WHERE O.PARTY_TYPE = 'ORGANIZATION'
842 AND O.PARTY_ID = REL.OBJECT_ID
843 AND P.PARTY_TYPE = 'PERSON'
844 AND P.PARTY_ID = REL.SUBJECT_ID
845 AND C.PARTY_TYPE = 'PARTY_RELATIONSHIP'
846 AND REL.PARTY_ID = C.PARTY_ID
847 AND O.PARTY_ID = p_org_party_id;*/
848 --End Bug#4597394 schekuri 08-Sep-2005
849
850 BEGIN
851
852 l_init_msg_list := FND_API.G_FALSE;
853 l_api_name := 'set_no_contact';
854 l_api_version_number := 1.0;
855
856 if funcmode <> 'RUN' then
857 result := wf_engine.eng_null;
858 return;
859 end if;
860
861 l_party_id := wf_engine.GetItemAttrNumber(
862 itemtype => itemtype,
863 itemkey => itemkey,
864 aname => 'PARTY_ID');
865
866 OPEN C_PARTY(l_party_id);
867 LOOP
868 FETCH c_party INTO l_p_party_id;
869 EXIT WHEN NOT C_PARTY%FOUND;
870 -- Dbms_output.put_line('Person Id '||l_p_party_id);
871
872 OPEN C_DO_PARTY(l_p_party_id);
873 FETCH c_do_party INTO l_contact_preference_id;
874 IF C_DO_PARTY%FOUND THEN
875 -- Dbms_output.put_line('P Id '||l_contact_preference_id||' Update');
876 l_contact_preference_rec.CONTACT_PREFERENCE_ID := l_contact_preference_id;
877 l_contact_preference_rec.PREFERENCE_CODE := 'DO_NOT';
878 l_contact_preference_rec.REQUESTED_BY := 'PARTY';
879 --Begin bug#5087608 schekuri 27-May-2006
880 --"CONTACT" lookup code is inactive in AR lookup type CONTACT_TYPE
881 l_contact_preference_rec.CONTACT_TYPE := 'ALL';
882 --l_contact_preference_rec.CONTACT_TYPE := 'CONTACT';
883 --End bug#5087608 schekuri 27-May-2006
884 l_contact_preference_rec.PREFERENCE_START_DATE := sysdate;
885 l_contact_preference_rec.STATUS := 'A';
886 l_contact_preference_rec.CREATED_BY_MODULE := 'IEX';
887
888 HZ_CONTACT_PREFERENCE_V2PUB.get_contact_preference_rec (
889 p_init_msg_list => l_init_msg_list,
890 p_contact_preference_id => l_contact_preference_id,
891 x_contact_preference_rec => l_contact_preference_rec,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_data => l_msg_data);
895
896 IF l_return_status = 'S'
897 and l_contact_preference_rec.PREFERENCE_CODE = 'DO_NOT'
898 and l_contact_preference_rec.CONTACT_TYPE = 'ALL' THEN --Changed for bug#5087608 schekuri 27-May-2006
899 --and l_contact_preference_rec.CONTACT_TYPE = 'CONTACT' THEN
900 null;
901 ELSE
902 -- update contact_preference_type
903 HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference (
904 p_init_msg_list => l_init_msg_list,
905 p_contact_preference_rec => l_contact_preference_rec,
906 p_object_version_number => l_object_version_number,
907 x_return_status => l_return_status,
908 x_msg_count => l_msg_count,
909 x_msg_data => l_msg_data);
910 END IF;
911
912 -- IF PG_DEBUG < 10 THEN
913 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
914 iex_debug_pub.logmessage ('set_no_contact_in_tca: ' || 'Update Contact Preference:'||l_return_status);
915 END IF;
916 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
917 RAISE L_API_ERROR;
918 END IF;
919
920 ELSE
921 -- Dbms_output.put_line('P Id '||l_contact_preference_id||' Null');
922 null;
923 END IF;
924 CLOSE C_DO_PARTY;
925 l_contact_preference_id := null;
926
927 OPEN C_DO_NOT_PARTY(l_p_party_id);
928 FETCH c_do_not_party INTO l_contact_preference_id;
929 IF c_do_not_party%FOUND THEN -- fetch succeeded
930 -- Dbms_output.put_line('P Id*'||l_contact_preference_id||' Null');
931 null;
932 ELSE
933 -- Dbms_output.put_line('P Id*'||l_contact_preference_id||' Create');
934 l_contact_preference_rec.CONTACT_LEVEL_TABLE := 'HZ_PARTIES';
935 l_contact_preference_rec.CONTACT_LEVEL_TABLE_ID := l_p_party_id;
936 l_contact_preference_rec.PREFERENCE_CODE := 'DO_NOT';
937 l_contact_preference_rec.REQUESTED_BY := 'PARTY';
938 --Begin bug#5087608 schekuri 27-May-2006
939 --"CONTACT" lookup code is inactive in AR lookup type CONTACT_TYPE
940 l_contact_preference_rec.CONTACT_TYPE := 'ALL';
941 --l_contact_preference_rec.CONTACT_TYPE := 'CONTACT';
942 --End bug#5087608 schekuri 27-May-2006
943 l_contact_preference_rec.PREFERENCE_START_DATE := sysdate;
944 l_contact_preference_rec.STATUS := 'A';
945 l_contact_preference_rec.CREATED_BY_MODULE := 'IEX';
946
947 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
948 p_init_msg_list => l_init_msg_list,
949 p_contact_preference_rec => l_contact_preference_rec,
950 x_contact_preference_id => o_contact_preference_id,
951 x_return_status => l_return_status,
952 x_msg_count => l_msg_count,
953 x_msg_data => l_msg_data);
954
955 -- IF PG_DEBUG < 10 THEN
956 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
957 iex_debug_pub.logmessage ('set_no_contact_in_tca: ' || 'Create Contact Preference:'||l_return_status);
958 END IF;
959 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
960 RAISE L_API_ERROR;
961 END IF;
962
963 END IF;
964 CLOSE C_DO_NOT_PARTY;
965 l_contact_preference_id := null;
966
967 END LOOP;
968 CLOSE C_PARTY;
969
970 /*
971 HZ_CONTACT_PREFERENCE_V2PUB.get_contact_preference_rec (
972 p_init_msg_list => l_init_msg_list,
973 p_contact_preference_id => l_contact_preference_id,
974 x_contact_preference_rec => l_contact_preference_rec,
975 x_return_status => l_return_status,
976 x_msg_count => l_msg_count,
977 x_msg_data => l_msg_data);
978
979 HZ_contact_perference_v2pub.create_contact_preference (
980 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
981 p_contact_preference_rec IN CONTACT_PREFERENCE_REC_TYPE,
982 x_contact_preference_id OUT NOCOPY NUMBER,
983 x_return_status OUT NOCOPY VARCHAR2,
984 x_msg_count OUT NOCOPY NUMBER,
985 x_msg_data OUT NOCOPY VARCHAR2
986 );
987
988 HZ_contact_perference_v2pub.update_contact_preference (
989 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
990 p_contact_preference_rec IN CONTACT_PREFERENCE_REC_TYPE,
991 p_object_version_number IN OUT NOCOPY NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2,
993 x_msg_count OUT NOCOPY NUMBER,
994 x_msg_data OUT NOCOPY VARCHAR2);
995
996 */
997
998 result := 'COMPLETE';
999
1000 EXCEPTION
1001 WHEN l_API_ERROR then
1002 WF_CORE.Raise(l_errmsg_name);
1003 WHEN OTHERS THEN
1004 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'set_no_contact_in_TCA',
1005 itemtype, itemkey, actid, funcmode);
1006 RAISE;
1007
1008 END set_no_contact_in_tca;
1009
1010 -- procedure turnoff_collection_profile -----------------------------
1011 procedure turnoff_collection_profile(
1012 itemtype IN VARCHAR2,
1013 itemkey IN VARCHAR2,
1014 actid IN NUMBER,
1015 funcmode IN VARCHAR2,
1016 result OUT NOCOPY VARCHAR2) IS
1017
1018 l_api_name VARCHAR2(100);
1019 l_errmsg_name VARCHAR2(30);
1020 l_api_error EXCEPTION;
1021 l_profile VARCHAR2(1);
1022
1023 BEGIN
1024
1025 l_api_name := 'create delinquency';
1026 /* if funcmode <> 'RUN' then
1027 result := wf_engine.eng_null;
1028 return;
1029 end if;
1030
1031 l_profile := NVL(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'), 'Y');
1032
1033
1034 wf_engine.setitemattrtext( itemtype => itemtype,
1035 itemkey => itemkey,
1036 aname => 'TURNOFF_COLLECTION_PROFILE',
1037 avalue => l_profile);
1038
1039 IF l_profile = 'Y' THEN
1040 result := wf_engine.eng_completed ||':Y';
1041 -- result := 'COMPLETE:Y';
1042 ELSE
1043 result := wf_engine.eng_completed ||':N';
1044 -- result := 'COMPLETE:N';
1045 END IF;
1046 -- IF PG_DEBUG < 10 THEN
1047 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1048 iex_debug_pub.logmessage ('turnoff_collection_profile: ' || 'result =>'||result);
1049 END IF;
1050
1051 EXCEPTION
1052 WHEN l_API_ERROR then
1053 WF_CORE.Raise(l_errmsg_name);
1054 WHEN OTHERS THEN
1055 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'Create_Strategy',
1056 itemtype, itemkey, actid, funcmode);
1057 RAISE; */
1058
1059 END turnoff_collection_profile;
1060
1061
1062 -- ******************************************************************
1063 PROCEDURE turnoff_collections
1064 (
1065 itemtype IN VARCHAR2,
1066 itemkey IN VARCHAR2,
1067 actid IN NUMBER,
1068 funcmode IN VARCHAR2,
1069 result OUT NOCOPY VARCHAR2) IS
1070
1071 l_errmsg_name VARCHAR2(30);
1072 l_api_error EXCEPTION;
1073 l_turnoff_collection_profile VARCHAR2(1);
1074 l_turnoff_invoice_profile VARCHAR2(1);
1075 l_default_notice_profile VARCHAR2(1);
1076 l_msg_count NUMBER;
1077 l_msg_data VARCHAR2(2000);
1078 l_api_name VARCHAR2(100);
1079 l_api_version_number NUMBER ;
1080 l_object_code VARCHAR2(10);
1081 l_source_module VARCHAR2(20);
1082 P_ObjectType VARCHAR2(30);
1083 p_ObjectID NUMBER;
1084 l_init_msg_list VARCHAR2(1);
1085 l_return_status VARCHAR2(20);
1086 l_commit varchar2(1);
1087 l_validation_level NUMBER;
1088 l_party_id NUMBER;
1089 l_cas_id NUMBER;
1090 p_delinquency_id NUMBER;
1091 p_bankruptcy_id NUMBER;
1092
1093 CaseIdTab IEX_UTILITIES.t_numbers;
1094 DelIdTab IEX_UTILITIES.t_numbers;
1095
1096 CURSOR C_CASE(p_party_id NUMBER) IS
1097 SELECT cas_id
1098 FROM iex_cases_all_b
1099 WHERE party_id = p_party_id;
1100
1101 CURSOR C_DELINQ(p_case_id NUMBER) IS
1102 SELECT delinquency_id
1103 FROM iex_delinquencies
1104 WHERE case_id = p_case_id ;
1105
1106 l_case_id NUMBER;
1107 l_case_count NUMBER;
1108 l_del_id NUMBER;
1109 l_del_count NUMBER;
1110 l_ban_id NUMBER;
1111
1112 bankruptcy_REC IEX_BANKRUPTCIES_PVT.bankruptcy_Rec_Type;
1113 TYPE bankruptcy_Tbl_Type IS TABLE OF IEX_BANKRUPTCIES_PVT.bankruptcy_Rec_Type
1114 INDEX BY BINARY_INTEGER;
1115 bankruptcy_TBL bankruptcy_Tbl_Type;
1116
1117 BEGIN
1118
1119 l_api_name := 'Turn Off Collections';
1120 l_api_version_number := 1.0;
1121 l_object_code := 'IEX_CASE';
1122 l_source_module := 'create_delinquency';
1123 P_ObjectType := 'BANKRUPTCY';
1124 p_ObjectID := p_bankruptcy_id;
1125 l_init_msg_list := FND_API.G_FALSE;
1126
1127 if funcmode <> 'RUN' then
1128 result := wf_engine.eng_null;
1129 return;
1130 end if;
1131
1132 --get profile
1133 /*
1134 l_turnoff_collection_profile := wf_engine.GetItemAttrNumber(
1135 itemtype => itemtype,
1136 itemkey => itemkey,
1137 aname => 'TURNOFF_COLLECTION_PROFILE');
1138
1139 l_party_id := wf_engine.GetItemAttrNumber(
1140 itemtype => itemtype,
1141 itemkey => itemkey,
1142 aname => 'PARTY_ID');
1143
1144 -- l_turnoff_collection_profile := NVL(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'), 'Y');
1145 -- dbms_output.put_line('Profile '||l_turnoff_collection_profile);
1146
1147 OPEN C_CASE(l_party_id);
1148 LOOP
1149 FETCH C_CASE INTO l_case_id;
1150 EXIT WHEN NOT C_CASE%FOUND;
1151 --dbms_output.put_line('l_case_id '||l_case_id);
1152
1153 OPEN C_DELINQ(l_case_id);
1154 FETCH C_DELINQ INTO l_del_id;
1155 --dbms_output.put_line('No of Del '||C_DELINQ%ROWCOUNT);
1156
1157 IF NOT C_DELINQ%FOUND THEN
1158 --dbms_output.put_line('Create Del and Ban Here');
1159 --Create Del and Ban Record Here
1160 --dbms_output.put_line('Create Del and Ban Here');
1161 CaseIdTab(1) := l_cas_id;
1162
1163 -- Create Del and Ban Record Here
1164 IEX_DELINQUENCY_PUB.Create_Ind_Delinquency
1165 (p_api_version => l_api_version_number,
1166 p_init_msg_list => l_init_msg_list,
1167 p_commit => l_commit,
1168 p_validation_level => l_validation_level,
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data,
1172 p_source_module => 'IEX_WF_BAN_STATUS_PUN.create_delinquency', --Name of the calling procedure in the format Package.Procedure
1173 p_party_id => l_party_id,
1174 p_object_code => 'IEX_CASE' , --'IEX_CASE' for now.
1175 p_object_id_tbl => CaseIdTab, -- Table of Case Ids.
1176 x_del_id_tbl => DelIdTab -- Table of Deliquencies that got created (Index correspoding to the case_id table);
1177 );
1178 -- IF PG_DEBUG < 10 THEN
1179 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1180 iex_debug_pub.logMessage('In turnoff_collections.Create Ind Delinquency: ' ||l_return_status);
1181 END IF;
1182
1183 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1184 RAISE L_API_ERROR;
1185 END IF;
1186
1187 l_del_id := DelIdTab(1);
1188 END IF;
1189 CLOSE C_DELINQ;
1190
1191 bankruptcy_REC.Cas_id := l_cas_id;
1192 bankruptcy_REC.delinquency_id := l_del_id;
1193 bankruptcy_REC.party_id := l_party_id;
1194
1195 IEX_BANKRUPTCIES_PVT.Create_bankruptcy(
1196 P_Api_Version_Number => l_api_version_number,
1197 P_Init_Msg_List => l_init_msg_list,
1198 P_Commit => l_commit,
1199 p_validation_level => l_validation_level,
1200 P_bankruptcy_Rec => bankruptcy_REC,
1201 X_BANKRUPTCY_ID => l_ban_id,
1202 X_Return_Status => l_return_status,
1203 X_Msg_Count => l_msg_count,
1204 X_Msg_Data => l_msg_data
1205 );
1206 -- IF PG_DEBUG < 10 THEN
1207 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1208 iex_debug_pub.logMessage('In turnoff_collections.Create bankruptcy: ' ||l_return_status);
1209 END IF;
1210 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1211 RAISE L_API_ERROR;
1212 END IF;
1213
1214 IEX_STRATEGY_PUB.create_strategy(
1215 P_Api_Version_Number => l_api_version_number,
1216 P_Init_Msg_List => l_init_msg_list,
1217 P_Commit => l_commit,
1218 p_validation_level => l_validation_level,
1219 X_Return_Status => l_return_status,
1220 X_Msg_Count => l_msg_count,
1221 X_Msg_Data => l_msg_data,
1222 p_DelinquencyID => l_del_id,
1223 p_ObjectType => P_ObjectType,
1224 p_ObjectID => P_ObjectID);
1225
1226 -- IF PG_DEBUG < 10 THEN
1227 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1228 iex_debug_pub.logMessage('In turnoff_collections.Create strategy: ' ||l_return_status);
1229 END IF;
1230 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1231 RAISE L_API_ERROR;
1232 END IF;
1233
1234 l_del_id := null;
1235 END LOOP;
1236 CLOSE C_CASE;
1237 */
1238 result := 'COMPLETE';
1239
1240 EXCEPTION
1241 WHEN L_API_ERROR then
1242 WF_CORE.Raise(l_errmsg_name);
1243 WHEN OTHERS THEN
1244 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'turnoff Bankrupt Collections',
1245 itemtype, itemkey, actid, funcmode);
1246 RAISE;
1247
1248 END turnoff_collections;
1249
1250 -- ******************************************************************
1251 PROCEDURE no_turnoff_collections
1252 (
1253 itemtype IN VARCHAR2,
1254 itemkey IN VARCHAR2,
1255 actid IN NUMBER,
1256 funcmode IN VARCHAR2,
1257 result OUT NOCOPY VARCHAR2) IS
1258
1259 l_errmsg_name VARCHAR2(30);
1260 l_api_error EXCEPTION;
1261 l_turnoff_collection_profile VARCHAR2(1);
1262 l_turnoff_invoice_profile VARCHAR2(1);
1263 l_default_notice_profile VARCHAR2(1);
1264 l_msg_count NUMBER;
1265 l_msg_data VARCHAR2(2000);
1266 l_api_name VARCHAR2(100);
1267 l_api_version_number NUMBER;
1268 l_init_msg_list VARCHAR2(3) ;
1269 l_commit varchar2(1) ;
1270 l_object_code VARCHAR2(10);
1271 l_source_module VARCHAR2(20);
1272 l_party_id NUMBER;
1273 l_return_status VARCHAR2(20);
1274 l_validation_level NUMBER;
1275 l_cas_id NUMBER;
1276 p_delinquency_id NUMBER;
1277 p_bankruptcy_id NUMBER;
1278
1279 CaseIdTab IEX_UTILITIES.t_numbers;
1280 DelIdTab IEX_UTILITIES.t_numbers;
1281
1282 CURSOR C_CASE(p_party_id NUMBER) IS
1283 SELECT cas_id
1284 FROM iex_cases_all_b
1285 WHERE party_id = p_party_id;
1286
1287 CURSOR C_DELINQ(p_case_id NUMBER) IS
1288 SELECT delinquency_id
1289 FROM iex_delinquencies
1290 WHERE case_id = p_case_id;
1291
1292 l_case_id NUMBER;
1293 l_case_count NUMBER;
1294 l_del_id NUMBER;
1295 l_del_count NUMBER;
1296 l_ban_id NUMBER;
1297
1298 bankruptcy_REC IEX_BANKRUPTCIES_PVT.bankruptcy_Rec_Type;
1299 TYPE bankruptcy_Tbl_Type IS TABLE OF IEX_BANKRUPTCIES_PVT.bankruptcy_Rec_Type
1300 INDEX BY BINARY_INTEGER;
1301 bankruptcy_TBL bankruptcy_Tbl_Type;
1302
1303 P_ObjectType VARCHAR2(30);
1304 p_ObjectID NUMBER;
1305
1306 BEGIN
1307 -- 12/15/04
1308 -- obseleting this routine,we will have only one bankruptcy record in the database
1309 l_api_version_number := 1.0;
1310 l_init_msg_list := FND_API.G_FALSE;
1311 l_commit := 'T';
1312 l_object_code := 'IEX_CASE';
1313 l_source_module := 'create_delinquency';
1314
1315 P_ObjectType := 'BANKRUPTCY';
1316 p_ObjectID := p_bankruptcy_id;
1317
1318 if funcmode <> 'RUN' then
1319 result := wf_engine.eng_null;
1320 return;
1321 end if;
1322
1323 /*
1324 --get profile
1325 l_turnoff_collection_profile := wf_engine.GetItemAttrNumber(
1326 itemtype => itemtype,
1327 itemkey => itemkey,
1328 aname => 'TURNOFF_COLLECTION_PROFILE');
1329
1330 l_party_id := wf_engine.GetItemAttrNumber(
1331 itemtype => itemtype,
1332 itemkey => itemkey,
1333 aname => 'PARTY_ID');
1334
1335 --l_turnoff_collection_profile := NVL(fnd_profile.value('IEX_TURNOFF_COLLECT_BANKRUPTCY'), 'Y');
1336 --dbms_output.put_line('Profile '||l_turnoff_collection_profile);
1337
1338 OPEN C_CASE(l_party_id);
1339 -- l_case_count := C_CASE%ROWCOUNT;
1340 LOOP
1341 FETCH C_CASE INTO l_case_id;
1342 EXIT WHEN NOT C_CASE%FOUND;
1343 --dbms_output.put_line('l_case_id '||l_case_id);
1344
1345 OPEN C_DELINQ(l_case_id);
1346 FETCH C_DELINQ INTO l_del_id;
1347 --dbms_output.put_line('No of Del '||C_DELINQ%ROWCOUNT);
1348
1349 IF NOT C_DELINQ%FOUND THEN
1350 --dbms_output.put_line('Create Del and Ban Here');
1351 --Create Del and Ban Record Here
1352 --dbms_output.put_line('Create Del and Ban Here');
1353 CaseIdTab(1) := l_cas_id;
1354
1355 -- Create Del and Ban Record Here
1356 IEX_DELINQUENCY_PUB.Create_Ind_Delinquency
1357 (p_api_version => l_api_version_number,
1358 p_init_msg_list => l_init_msg_list,
1359 p_commit => l_commit,
1360 p_validation_level => l_validation_level,
1361 x_return_status => l_return_status,
1362 x_msg_count => l_msg_count,
1363 x_msg_data => l_msg_data,
1364 p_source_module => 'IEX_WF_BAN_STATUS_PUN.create_delinquency', --Name of the calling procedure in the format Package.Procedure
1365 p_party_id => l_party_id,
1366 p_object_code => 'IEX_CASE' , --'IEX_CASE' for now.
1367 p_object_id_tbl => CaseIdTab, -- Table of Case Ids.
1368 x_del_id_tbl => DelIdTab -- Table of Deliquencies that got created (Index correspoding to the case_id table);
1369 );
1370 -- IF PG_DEBUG < 10 THEN
1371 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1372 iex_debug_pub.logMessage('In no_turnoff_collections.Create Ind Delinquency: ' ||l_return_status);
1373 END IF;
1374 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1375 RAISE L_API_ERROR;
1376 END IF;
1377
1378 l_del_id := DelIdTab(1);
1379
1380 bankruptcy_REC.Cas_id := l_cas_id;
1381 bankruptcy_REC.delinquency_id := l_del_id;
1382 bankruptcy_REC.party_id := l_party_id;
1383
1384 IEX_BANKRUPTCIES_PVT.Create_bankruptcy(
1385 P_Api_Version_Number => l_api_version_number,
1386 P_Init_Msg_List => l_init_msg_list,
1387 P_Commit => l_commit,
1388 p_validation_level => l_validation_level,
1389 P_bankruptcy_Rec => bankruptcy_REC,
1390 X_BANKRUPTCY_ID => l_ban_id,
1391 X_Return_Status => l_return_status,
1392 X_Msg_Count => l_msg_count,
1393 X_Msg_Data => l_msg_data
1394 );
1395 -- IF PG_DEBUG < 10 THEN
1396 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1397 iex_debug_pub.logMessage('In no_turnoff_collections.Create bankruptcy: ' ||l_return_status);
1398 END IF;
1399 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1400 RAISE L_API_ERROR;
1401 END IF;
1402
1403 IEX_STRATEGY_PUB.create_strategy(
1404 P_Api_Version_Number => l_api_version_number,
1405 P_Init_Msg_List => l_init_msg_list,
1406 P_Commit => l_commit,
1407 p_validation_level => l_validation_level,
1408 X_Return_Status => l_return_status,
1409 X_Msg_Count => l_msg_count,
1410 X_Msg_Data => l_msg_data,
1411 p_DelinquencyID => l_del_id,
1412 p_ObjectType => P_ObjectType,
1413 p_ObjectID => P_ObjectID);
1414 -- IF PG_DEBUG < 10 THEN
1415 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1416 iex_debug_pub.logMessage('In no_turnoff_collections.Create strategy: ' ||l_return_status);
1417 END IF;
1418 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1419 RAISE L_API_ERROR;
1420 END IF;
1421
1422 l_del_id := null;
1423 END IF;
1424 CLOSE C_DELINQ;
1425
1426 END LOOP;
1427 CLOSE C_CASE;
1428 */
1429
1430 result := 'COMPLETE';
1431
1432 EXCEPTION
1433 WHEN L_API_ERROR then
1434 WF_CORE.Raise(l_errmsg_name);
1435 WHEN OTHERS THEN
1436 WF_CORE.Context('IEX_WF_BAN_STATUS_PUB', 'turnoff Bankrupt Collections',
1437 itemtype, itemkey, actid, funcmode);
1438 RAISE;
1439
1440 END no_turnoff_collections;
1441
1442 -- procedure Create Strategy -----------------------------
1443 PROCEDURE Create_Strategy(
1444 p_api_version IN NUMBER DEFAULT 1.0,
1445 p_init_msg_list IN VARCHAR2 ,
1446 p_commit IN VARCHAR2 ,
1447 p_delinquency_id IN NUMBER,
1448 p_bankruptcy_id IN NUMBER,
1449 p_party_id IN NUMBER,
1450 x_return_status OUT NOCOPY VARCHAR2,
1451 x_msg_count OUT NOCOPY NUMBER,
1452 x_msg_data OUT NOCOPY VARCHAR2) IS
1453
1454 l_errmsg_name VARCHAR2(30);
1455 L_API_ERROR EXCEPTION;
1456 l_msg_count NUMBER;
1457 l_msg_data VARCHAR2(2000);
1458 l_return_status VARCHAR2(20);
1459 l_commit varchar2(1);
1460 l_validation_level NUMBER;
1461 l_error_msg VARCHAR2(2000);
1462
1463 P_ObjectType VARCHAR2(30);
1464 p_ObjectID NUMBER;
1465 l_api_version_number NUMBER;
1466 l_api_name VARCHAR2(100);
1467 l_init_msg_list VARCHAR2(1);
1468 l_disposition_code varchar2(30);
1469
1470 BEGIN
1471 -- Standard Start of API savepoint
1472 SAVEPOINT START_STRATEGY;
1473
1474 P_ObjectType := 'BANKRUPTCY';
1475 p_ObjectID := p_bankruptcy_id;
1476 l_api_version_number := 1.0;
1477 l_api_name := 'Create Strategy';
1478 l_init_msg_list := FND_API.G_FALSE;
1479
1480 -- Standard call to check for call compatibility.
1481 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1482 p_api_version,
1483 l_api_name,
1484 G_PKG_NAME)
1485 THEN
1486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1487 END IF;
1488
1489
1490 -- Initialize message list if p_init_msg_list is set to TRUE.
1491 IF FND_API.to_Boolean( p_init_msg_list )
1492 THEN
1493 FND_MSG_PUB.initialize;
1494 END IF;
1495
1496 -- Initialize API return status to SUCCESS
1497 x_return_status := FND_API.G_RET_STS_SUCCESS;
1498
1499 /* Create Strategy for a object
1500
1501 P_API_VERSION_NUMBER := 2.0
1502 P_ObjectType := 'DELINQUENT', 'BANKRUPTCY', 'WRITEOFF', 'REPOSSESSION', 'LITIGATION', 'BANKRUPTCY'
1503 p_ObjectID := DelinquencyID, BankRuptcyID, WriteoffID, RepossessionID, Litigation ID, Bankruptcy ID
1504 */
1505
1506 select disposition_code
1507 into l_disposition_code
1508 from iex_bankruptcies
1509 where bankruptcy_id = p_bankruptcy_id;
1510
1511 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1512 IEX_DEBUG_PUB.logMessage('Create_Strategy : l_disposition_code := ' ||l_disposition_code);
1513 END IF;
1514
1515 if l_disposition_code is NULL then
1516 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1517 IEX_DEBUG_PUB.logMessage('Create_Strategy : Calling IEX_STRATEGY_PUB.create_strategy');
1518 END IF;
1519 IEX_STRATEGY_PUB.create_strategy(
1520 P_Api_Version_Number => l_api_version_number,
1521 P_Init_Msg_List => l_init_msg_list,
1522 P_Commit => l_commit,
1523 p_validation_level => l_validation_level,
1524 X_Return_Status => l_return_status,
1525 X_Msg_Count => l_msg_count,
1526 X_Msg_Data => l_msg_data,
1527 p_DelinquencyID => p_delinquency_id,
1528 p_ObjectType => P_ObjectType,
1529 p_ObjectID => P_ObjectID);
1530 else
1531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1532 IEX_DEBUG_PUB.logMessage('Start cancel_strategy_and_workflow');
1533 END IF;
1534 end if;
1535
1536 -- Standard call to get message count and if count is 1, get message info.
1537 FND_MSG_PUB.Count_And_Get
1538 ( p_count => x_msg_count,
1539 p_data => x_msg_data
1540 );
1541 --Start bug 6717204 gnramasa 11th Jan 08
1542 EXCEPTION
1543 WHEN FND_API.G_EXC_ERROR THEN
1544 ROLLBACK TO START_STRATEGY;
1545 x_return_status := FND_API.G_RET_STS_ERROR;
1546 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1547
1548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1549 ROLLBACK TO START_STRATEGY;
1550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1552
1553 WHEN OTHERS THEN
1554 ROLLBACK TO START_STRATEGY;
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1557 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1558 END IF;
1559 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1560 --End bug 6717204 gnramasa 11th Jan 08
1561 END Create_Strategy;
1562
1563 --Start bug 7661724 gnramasa 8th Jan 09
1564 procedure cancel_strategy_and_workflow(
1565 p_party_id IN NUMBER,
1566 p_bankruptcy_id IN NUMBER,
1567 p_disposition_code IN VARCHAR2)
1568 IS
1569 cursor c_get_strategy_id (l_bkrid number) is
1570 select st.strategy_id
1571 from iex_strategies st
1572 where st.status_code = 'OPEN'
1573 and st.jtf_object_type = 'IEX_BANKRUPTCY'
1574 and st.jtf_object_id = l_bkrid;
1575
1576 l_startegy_id number;
1577 l_item_type VARCHAR2(100) := 'IEXBANST';
1578 l_result VARCHAR2(100);
1579 l_status VARCHAR2(8);
1580
1581 -- Begin bug 7703313
1582 l_itemkey varchar2(240);
1583
1584 cursor get_cr_itemkey(c_id number) is
1585 select distinct item_key from wf_item_attr_values_ondemand
1586 where name = 'BANKRUPTCY_ID' and number_value = c_id and item_type = 'IEXDELCR';
1587 cursor get_cs_itemkey(c_id number) is
1588 select distinct item_key from wf_item_attr_values_ondemand
1589 where name = 'BANKRUPTCY_ID' and number_value = c_id and item_type = 'IEXDELCS';
1590 -- End bug 7703313
1591
1592 begin
1593 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1594 IEX_DEBUG_PUB.logMessage('Start cancel_strategy_and_workflow');
1595 END IF;
1596 SAVEPOINT cancel_strategy_and_workflow;
1597
1598 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1599 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: p_party_id =>' || p_party_id);
1600 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: p_bankruptcy_id =>' || p_bankruptcy_id);
1601 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: p_disposition_code =>' || p_disposition_code);
1602 END IF;
1603
1604 begin
1605 open c_get_strategy_id (p_bankruptcy_id);
1606 fetch c_get_strategy_id into l_startegy_id;
1607 close c_get_strategy_id;
1608
1609 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1610 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: l_startegy_id =>' || l_startegy_id);
1611 END IF;
1612
1613 exception
1614 when others then IEX_DEBUG_PUB.logMessage('exception to get strategy ID'); null;
1615 end;
1616
1617 if l_startegy_id is not null then
1618 begin
1619 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1620 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: Before calling IEX_STRATEGY_WF.SEND_SIGNAL');
1621 END IF;
1622 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1623 strategy_id => l_startegy_id,
1624 status => 'CANCELLED' ) ;
1625
1626 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1627 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: After calling IEX_STRATEGY_WF.SEND_SIGNAL');
1628 END IF;
1629 exception
1630 when others then
1631 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1632 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: In others exception');
1633 END IF;
1634 Update iex_strategies set status_code = 'CANCELLED'
1635 where strategy_id = l_startegy_id;
1636
1637 update iex_strategy_work_items
1638 set status_code = 'CANCELLED'
1639 where strategy_id = l_startegy_id
1640 and status_code in ('PRE-WAIT','OPEN');
1641
1642 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1643 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: End of others exception');
1644 END IF;
1645 end;
1646 end if;
1647
1648 if (p_disposition_code = 'WITHDRAWN') or (p_disposition_code = 'DISMISSED') then
1649 begin
1650 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_bankruptcy_id, status => l_status, result => l_result);
1651 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1652 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: Workflow status =>' || l_status);
1653 END IF;
1654
1655 IF l_status <> wf_engine.eng_completed THEN
1656 wf_engine.abortprocess(itemtype => l_item_type, itemkey => p_bankruptcy_id);
1657 wf_engine.itemstatus(itemtype => l_item_type, itemkey => p_bankruptcy_id, status => l_status, result => l_result);
1658 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1659 IEX_DEBUG_PUB.logMessage('cancel_strategy_and_workflow: Abort process has completed and status =>' || l_status);
1660 END IF;
1661
1662 END IF;
1663 EXCEPTION
1664 when others then IEX_DEBUG_PUB.logMessage('exception to disposition code step 1 '); null;
1665
1666 END;
1667
1668 -- Begin bug 7703313
1669 begin
1670 open get_cs_itemkey(p_bankruptcy_id);
1671 Loop
1672 fetch get_cs_itemkey into l_itemkey;
1673 exit when get_cs_itemkey%NOTFOUND;
1674
1675 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1676 IEX_DEBUG_PUB.logMessage('IEXDELCS Workflow Status = :: =>' || l_status||'and itemkey is...'||l_itemkey);
1677 end if;
1678
1679 wf_engine.itemstatus(itemtype => 'IEXDELCS', itemkey => l_itemkey, status => l_status, result => l_result);
1680 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1681 IEX_DEBUG_PUB.logMessage('IEXDELCS Workflow Status = :: =>' || l_status||'and itemkey is...'||l_itemkey);
1682 end if;
1683
1684 if l_status <> wf_engine.eng_completed THEN
1685 wf_engine.abortprocess(itemtype => 'IEXDELCS', itemkey => l_itemkey);
1686 wf_engine.itemstatus(itemtype => 'IEXDELCS', itemkey => l_itemkey, status => l_status, result => l_result);
1687 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1688 IEX_DEBUG_PUB.logMessage('cancel serviceHold_workflow: Abort process has completed and status =>' || l_status);
1689 END IF;
1690 end if;
1691 End Loop;
1692 close get_cs_itemkey;
1693
1694 exception
1695 when others then
1696 IEX_DEBUG_PUB.logMessage('ServiceHold Workflow does not exist '||p_bankruptcy_id);
1697 null;
1698 end;
1699
1700 begin
1701 open get_cr_itemkey(p_bankruptcy_id);
1702 Loop
1703 fetch get_cr_itemkey into l_itemkey;
1704 exit when get_cr_itemkey%NOTFOUND;
1705
1706 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1707 IEX_DEBUG_PUB.logMessage('IEXDELCR Workflow Status = :: =>' || l_status||'and itemkwy is ...'||l_itemkey);
1708 end if;
1709
1710 wf_engine.itemstatus(itemtype => 'IEXDELCR', itemkey => l_itemkey, status => l_status, result => l_result);
1711 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1712 IEX_DEBUG_PUB.logMessage('IEXDELCR Workflow Status = :: =>' || l_status||'and itemkwy is ...'||l_itemkey);
1713 end if;
1714
1715 if l_status <> wf_engine.eng_completed THEN
1716 wf_engine.abortprocess(itemtype => 'IEXDELCR', itemkey => l_itemkey);
1717 wf_engine.itemstatus(itemtype => 'IEXDELCR', itemkey => l_itemkey, status => l_status, result => l_result);
1718 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1719 IEX_DEBUG_PUB.logMessage('cancel_creditHold_request_workflow: Abort process has completed and status =>' || l_status);
1720 END IF;
1721 end if;
1722 End Loop;
1723 close get_cr_itemkey;
1724
1725 exception
1726 when others then
1727 IEX_DEBUG_PUB.logMessage('Credit Hold Request Workflow does not exist '||p_bankruptcy_id);
1728 null;
1729 end;
1730 -- End bug 7703313
1731
1732 end if;
1733 commit;
1734 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1735 IEX_DEBUG_PUB.logMessage('End cancel_strategy_and_workflow');
1736 END IF;
1737 end cancel_strategy_and_workflow;
1738 --End bug 7661724 gnramasa 8th Jan 09
1739
1740 /**
1741 * check whether the to send a notification -- bug 15951096
1742 **/
1743 procedure NOTIFY_CHECK(
1744 itemtype in varchar2,
1745 itemkey in varchar2,
1746 actid in number,
1747 funcmode in varchar2,
1748 result out NOCOPY varchar2)IS
1749
1750 l_result VARCHAR2(1) := 'N';
1751 l_value VARCHAr2(300);
1752 l_bankruptcy_id number;
1753 l_notify VARCHAR2(1);
1754
1755 CURSOR c_notify(p_bankruptcy_id NUMBER) IS
1756 SELECT nvl(TURN_OFF_INVOICING_YN,'N') FROM iex_bankruptcies
1757 WHERE bankruptcy_id = p_bankruptcy_id;
1758
1759 BEGIN
1760
1761 if (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1762 iex_debug_pub.logmessage ('**** START NOTIFY_CHECK ************');
1763 end if;
1764
1765 if funcmode <> 'RUN' then
1766 result := wf_engine.eng_null;
1767 return;
1768 end if;
1769
1770 l_bankruptcy_id := wf_engine.GetItemAttrNumber(
1771 itemtype => itemtype,
1772 itemkey => itemkey,
1773 aname => 'BANKRUPTCY_ID');
1774
1775 iex_debug_pub.logmessage ('**** Bankruptcy ID ==> '||l_bankruptcy_id);
1776
1777 OPEN c_notify(l_bankruptcy_id);
1778 FETCH c_notify INTO l_notify;
1779 CLOSE c_notify;
1780
1781 IF l_notify IS NOT NULL THEN
1782 l_result := l_notify;
1783 END IF;
1784
1785 result := wf_engine.eng_completed ||':'||l_result;
1786 iex_debug_pub.logmessage ('**** Result ==> '||l_notify);
1787
1788 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1789 iex_debug_pub.logmessage ('**** END NOTIFY_CHECK ************');
1790 END IF;
1791
1792 exception
1793 when others then
1794 iex_debug_pub.logmessage ('====> Exception from NOTIFY_CHECK <======');
1795 result := wf_engine.eng_completed ||':'||l_result;
1796 wf_core.context('IEX_WF_BAN_STATUS_PUB','NOTIFY_CHECK',itemtype,itemkey,to_char(actid),funcmode);
1797 raise;
1798
1799 END NOTIFY_CHECK;
1800
1801
1802 BEGIN
1803
1804 G_PKG_NAME := 'IEX_WF_BAN_STATUS_PUB';
1805 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1806
1807 END IEX_WF_BAN_STATUS_PUB;