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