[Home] [Help]
PACKAGE BODY: APPS.OKL_STRY_CALL_CO_PUB
Source
1 PACKAGE BODY OKL_STRY_CALL_CO_PUB as
2 /* $Header: OKLPCWFB.pls 115.3 2002/12/18 03:48:40 rabhupat noship $ */
3 -- Start of Comments
4 -- Package name : OKL_STRY_CALL_CO_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(100):= 'OKL_STRY_CALL_CO_PUB ';
11 G_FILE_NAME CONSTANT VARCHAR2(50) := 'OKLPCWFB.pls';
12
13 /*
14 * wait for 10 mts before sending response back to the strategy engine
15 */
16 procedure set_wait_period ( itemtype in varchar2,
17 itemkey in varchar2) is
18 l_wait_period DATE;
19 Begin
20
21 select sysdate + 10/24/60 into l_wait_period from dual;
22
23 wf_engine.SetItemAttrDate(itemtype => itemtype,
24 itemkey => itemkey,
25 aname => 'WAIT_PERIOD',
26 avalue => l_wait_period);
27
28 end set_wait_period;
29
30 /* Gets email for given party ID
31 *
32 */
33 PROCEDURE GET_PARTY_EMAIL (l_party_id IN NUMBER,
34 x_email OUT NOCOPY VARCHAR2) IS
35
36 CURSOR C_GET_ORG_EMAIL (p_PARTY_ID NUMBER) IS
37 SELECT email_address
38 FROM HZ_PARTIES
39 WHERE party_ID =p_party_ID;
40
41 CURSOR C_GET_CONTACT_EMAIL (p_PARTY_ID NUMBER) IS
42 SELECT email_address
43 FROM HZ_CONTACT_POINTS
44 WHERE owner_table_ID = p_party_ID
45 AND Contact_point_type = 'EMAIL'
46 AND primary_flag = 'Y';
47
48
49
50 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
51
52
53 BEGIN
54
55 --set email address of the customer
56 --get contact email or if contact is null ,
57 --get org email address.
58 Open C_Get_CONTACT_EMAIL(l_party_id);
59 FETCH c_get_contact_email INTO l_email;
60 CLOSE c_get_contact_email;
61 If l_email is NULL THEN
62 Open C_Get_ORG_EMAIL(l_party_id);
63 Fetch C_Get_ORG_EMAIL into l_email;
64 CLOSE C_Get_ORG_EMAIL;
65 End if;
66 x_email :=l_email;
67
68 END GET_PARTY_EMAIL;
69
70
71 /** get user name
72 * this will used to send the notification
73 **/
74
75 procedure get_username
76 ( p_resource_id IN NUMBER,
77 x_username OUT NOCOPY VARCHAR2 ) IS
78 cursor c_getname(p_resource_id NUMBER) is
79 Select user_name
80 from jtf_rs_resource_extns
81 where resource_id =p_resource_id;
82
83 BEGIN
84 OPEN c_getname(p_resource_id);
85 FETCH c_getname INTO x_username;
86 CLOSE c_getname;
87
88 END get_username;
89
90 -----populate set_notification_resources---------------------------------
91 procedure set_notification_resources(
92 p_resource_id in number,
93 itemtype in varchar2,
94 itemkey in varchar2
95 ) IS
96 l_username VARCHAR2(100);
97 l_mgrname VARCHAR2(100);
98 l_mgr_resource_id NUMBER ;
99 BEGIN
100
101 -- get user name from jtf_rs_resource_extns
102 get_username
103 ( p_resource_id =>p_resource_id,
104 x_username =>l_username);
105
106 wf_engine.SetItemAttrText(itemtype => itemtype,
107 itemkey => itemkey,
108 aname => 'NOTIFICATION_USERNAME',
109 avalue => l_username);
110 exception
111 when others then
112 null;
113
114 END set_notification_resources;
115
116
117
118
119
120 -----PUBLIC Procedures--------------------------------
121
122 /**
123 get the most delinquent contract from a
124 case based on the no of days due
125 */
126
127 PROCEDURE get_delinquent_contract(
128 p_case_id IN NUMBER,
129 x_contract_id OUT NOCOPY NUMBER,
130 x_days OUT NOCOPY NUMBER) IS
131
132 l_status VARCHAR(1);
133 --x_days NUMBER := 0;
134 x_greatest NUMBER := 0;
135 cursor c_contracts(p_case_id IN NUMBER) is
136 select object_id from iex_case_objects
137 where cas_id =p_case_id ;
138 BEGIN
139 FOR c_rec in c_contracts(p_case_id)
140 LOOP
141 l_status := okl_contract_info.
142 get_days_past_due(c_rec.object_id,x_days);
143 if x_days >= x_greatest THEN
144 x_contract_id :=c_rec.object_id;
145 x_greatest := x_days;
146 end if;
147 END LOOP;
148 x_days :=x_greatest;
149 EXCEPTION WHEN OTHERS THEN
150 x_contract_id := 0;
151 x_days :=0;
152 END get_delinquent_contract;
153
154 ---------------------------------------------------------------------------
155 -- PROCEDURE get_vendor_info
156 ---------------------------------------------------------------------------
157 PROCEDURE get_vendor_info(p_case_number in varchar2,
158 x_vendor_id out nocopy number,
159 x_vendor_name out nocopy varchar2,
160 x_vendor_email out nocopy varchar2,
161 x_return_status out nocopy varchar2) AS
162
163 --get vendor info
164 CURSOR l_vendor_csr(cp_case_number IN VARCHAR2) IS SELECT pv.vendor_id
165 ,pv.vendor_name
166 --,pvs.email_address
167 FROM iex_cases_all_b ica
168 ,iex_case_objects ico
169 ,okc_k_party_roles_v opr
170 ,po_vendors pv
171 --,po_vendor_sites_all pvs
172 WHERE ica.case_number = cp_case_number
173 AND ica.cas_id = ico.cas_id
174 AND ico.object_id =opr.dnz_chr_id
175 AND opr.rle_code = 'OKL_VENDOR'
176 AND opr.object1_id1 = pv.vendor_id;
177 --AND pv.vendor_id = pvs.vendor_id;
178
179 --get contracts on case
180 CURSOR l_khr_csr(cp_case_number IN VARCHAR2) IS SELECT ico.object_id
181 , okh.contract_number
182 FROM iex_cases_all_b ica
183 ,iex_case_objects ico
184 ,okc_k_headers_b okh
185 WHERE ica.case_number = cp_case_number
186 AND ica.cas_id = ico.cas_id
187 AND ico.object_id = okh.id;
188
189 --get program id to get the vendor sites id
190 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
191 SELECT prog.id program_id
192 ,prog.contract_number program_number
193 ,lease.id contract_id
194 ,rgp.dnz_chr_id
195 ,lease.contract_number contract_number
196 ,rgp.rgd_code
197 FROM okc_k_headers_b prog,
198 okc_k_headers_b lease,
199 okl_k_headers khr,
200 okc_rule_groups_b rgp
201 WHERE khr.id = lease.id
202 AND khr.khr_id = prog.id
203 AND prog.scs_code = 'PROGRAM'
204 AND lease.scs_code in ('LEASE','LOAN')
205 AND rgp.rgd_code = 'COAGRM'
206 AND rgp.dnz_chr_id = prog.id
207 AND lease.contract_number = cp_contract_number;
208
209 --get vendor email address
210 CURSOR l_email_csr(cp_vendor_site_code IN VARCHAR2) IS
211 SELECT pvs.email_address
212 FROM po_vendor_sites_all pvs
213 WHERE pvs.vendor_site_code = cp_vendor_site_code;
214
215 l_id1 VARCHAR2(200) := NULL;
216 l_id2 VARCHAR2(200) := NULL;
217 l_vendor_site_code VARCHAR2(200) := null;
218 l_return_status VARCHAR2(1);
219 BEGIN
220 --get sendto_third_party flag
221 FOR cur_khr IN l_khr_csr(p_case_number) LOOP
222 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
223 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
224 p_contract_id => cur_rule.program_id
225 ,p_rule_group_code => 'COAGRM'
226 ,p_rule_code => 'COVNAG'
227 -- ,p_rule_name => 'Vendor location'
228 ,p_segment_number => 1
229 ,x_id1 => l_id1
230 ,x_id2 => l_id2
231 ,x_value => l_vendor_site_code);
232
233 IF(l_vendor_site_code IS NOT NULL) THEN
234 EXIT;
235 END IF;
236 END LOOP
237 EXIT;
238 END LOOP;
239
240 FOR cur_email IN l_email_csr(l_vendor_site_code) LOOP
241 x_vendor_email := cur_email.email_address;
242 END LOOP;
243
244 FOR cur IN l_vendor_csr(p_case_number) LOOP
245 x_vendor_id := cur.vendor_id;
246 x_vendor_name := cur.vendor_name;
247 --x_vendor_email := cur.email_address;
248 EXIT;
249 END LOOP;
250 END get_vendor_info;
251
252
253 /**
254 * send an email thru fulfilment
255 * right now the okl fulfilment api supports email
256 * only
257 **/
258
259 procedure send_fulfilment(
260 itemtype in varchar2,
261 itemkey in varchar2,
262 actid in number,
263 funcmode in varchar2,
264 result out nocopy varchar2)
265 IS
266
267 l_work_item_id number;
268 l_strategy_id number;
269 l_party_id number;
270 l_delinquency_id NUMBER;
271 l_contract_id NUMBER;
272
273 l_return_status VARCHAR2(20);
274 l_msg_count NUMBER;
275 l_msg_data VARCHAR2(2000);
276 l_error VARCHAR2(32767);
277
278 l_api_version CONSTANT NUMBER := 1;
279
280 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
281 l_subject VARCHAR2(2000);
282 l_from VARCHAR2(2000);
283 l_agent_id NUMBER := to_number(fnd_profile.value('OKL_FULFILLMENT_USER'));
284 l_request_id NUMBER;
285
286 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE :=
287 to_number(fnd_profile.value('OKL_VND_APPROVAL_TEMPLATE'));
288 l_content_name JTF_FM_TEMPLATE_CONTENTS.content_name%TYPE;
289 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
290 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
291 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
292
293
294
295 -- get contract id for this strategy
296 -- this contract id will be used as the
297 --bind variable for fullfillment template.
298
299 cursor c_get_case_number (p_delinquency_id IN NUMBER) is
300 select c.case_number from iex_cases_all_b c,iex_Delinquencies_all d
301 where c.cas_id=d.case_id
302 and d.delinquency_id =p_delinquency_id;
303
304 l_case_number IEX_CASES_ALL_B.CASE_NUMBER%TYPE;
305 l_vendor_id PO_VENDORS.VENDOR_ID%TYPE;
306 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
307
308 Begin
309
310 if funcmode <> 'RUN' then
311 result := wf_engine.eng_null;
312 return;
313 end if;
314
315 l_work_item_id := wf_engine.GetItemAttrNumber(
316 itemtype => itemtype,
317 itemkey => itemkey,
318 aname => 'WORK_ITEMID');
319
320 l_strategy_id := wf_engine.GetItemAttrNumber(
321 itemtype => itemtype,
322 itemkey => itemkey,
323 aname => 'STRATEGY_ID');
324
325 l_party_id := wf_engine.GetItemAttrNumber(
326 itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'PARTY_ID');
329
330 l_delinquency_id := wf_engine.GetItemAttrNumber(
331 itemtype => itemtype,
332 itemkey => itemkey,
333 aname => 'DELINQUENCY_ID');
334
335 l_email := wf_engine.GetItemAttrText(
336 itemtype => itemtype,
337 itemkey => itemkey,
338 aname => 'PARTY_EMAIL');
339
340
341
342
343
344
345
346
347
348 -- 08/16/02
349 --- populate l_agent from the profile okl_fulfillment_user
350 -- l_agent_id := FND_GLOBAL.USER_ID;
351 --l_agent_id :=1001646;
352
353
354 -- populate from to
355 l_from := fnd_profile.value('OKL_VND_APPROVAL_EMAIL_FROM');
356
357 If l_from is null THEN
358 result := wf_engine.eng_completed ||':'||wf_no;
359 return;
360 end if;
361
362 --populate l_subject
363 l_subject := fnd_profile.value('OKL_VND_APPROVAL_EMAIL_SUBJECT');
364 if l_subject is null THEN
365 l_subject := 'Vendor Approval needed for strategy '
366 ||l_strategy_id || ' and Work item '||l_work_item_id;
367 end if;
368
369
370
371 -- populate bind variables
372
373 OPEN c_get_case_number(l_delinquency_id);
374 FETCH c_get_case_number INTO l_case_number;
375 CLOSE c_get_case_number;
376
377 --get vendor email address
378 -- get vendor_info
379 -- don't get vendor info if custom workflow
380 -- is write to customer. the email is picked from the hz_parties
381 --table and is already set in the attribute PARTY_EMAIL.
382
383 if l_email is NULL THEN
384 get_vendor_info(p_case_number => l_case_number,
385 x_vendor_id => l_vendor_id,
386 x_vendor_name => l_vendor_name,
387 x_vendor_email => l_email,
388 x_return_status => l_return_status);
389
390 end if;
391 --set vendor_name
392 wf_engine.SetItemAttrText(itemtype => itemtype,
393 itemkey => itemkey,
394 aname => 'VENDOR_NAME',
395 avalue => l_vendor_name);
396
397 -- for the time being set as '[email protected]
398 if l_email is NULL THEN
399 result := wf_engine.eng_completed ||':'||wf_no;
400 return;
401 end if;
402
403 if itemtype ='OKLCOWRI' THEN
404 l_content_id :=to_number(fnd_profile.value('OKL_WRITE_CUST_TEMPLATE'));
405 end if;
406
407 If l_content_id is null THEN
408 result := wf_engine.eng_completed ||':'||wf_no;
409 return;
410 end if;
411
412
413 l_bind_var(1) := 'p_case_number';
414 l_bind_val(1) := l_case_number;
415 l_bind_var_type(1) := 'VARCHAR2';
416
417
418
419 OKL_FULFILLMENT_PUB.create_fulfillment (
420 p_api_version => l_api_version,
421 p_init_msg_list => fnd_api.G_TRUE,
422 p_agent_id => l_agent_id,
423 p_content_id => l_content_id,
424 p_from => l_from,
425 p_subject => l_subject,
426 p_email => l_email,
427 p_bind_var => l_bind_var,
428 p_bind_val => l_bind_val,
429 p_bind_var_type => l_bind_var_type,
430 p_commit => fnd_api.G_FALSE,
431 x_request_id => l_request_id,
432 x_return_status => l_return_status,
433 x_msg_count => l_msg_count,
434 x_msg_data => l_msg_data);
435
436
437 if l_return_status <>FND_API.G_RET_STS_SUCCESS THEN
438 result := wf_engine.eng_completed ||':'||wf_no;
439 else
440 result := wf_engine.eng_completed ||':'||wf_yes;
441 if itemtype ='OKLCOWRI' THEN
442 wf_engine.SetItemAttrText(itemtype => itemtype,
443 itemkey => itemkey,
444 aname => 'WK_STATUS',
445 avalue => 'COMPLETE');
446 end if;
447 end if;
448
449
450 EXCEPTION
451 when others then
452 wf_core.context('OKL_STRY_CALL_CO_PUB',' send_fulfilment',itemtype,
453 itemkey,to_char(actid),funcmode);
454 raise;
455 end send_fulfilment;
456
457
458 /** send signal to the main work flow that the custom work
459 * flow is over and also updates the work item
460 * the send signal is send when the agent REJECTS the
461 * notification since the vendor didn't approve it ,
462 * so set the status to 'CANCELLED'.
463 **/
464
465 procedure wf_send_signal(
466 itemtype in varchar2,
467 itemkey in varchar2,
468 actid in number,
469 funcmode in varchar2,
470 result out nocopy varchar2)
471 IS
472
473 l_work_item_id number;
474 l_strategy_id number;
475 l_return_status VARCHAR2(20);
476 l_msg_count NUMBER;
477 l_msg_data VARCHAR2(2000);
478 exc EXCEPTION;
479
480 l_error VARCHAR2(32767);
481 l_user_id NUMBER;
482 l_resp_id NUMBER;
483 l_resp_appl_id NUMBER;
484 l_wk_status varchar2(20);
485 Begin
486 if funcmode <> 'RUN' then
487 result := wf_engine.eng_null;
488 return;
489 end if;
490
491 l_work_item_id := wf_engine.GetItemAttrNumber(
492 itemtype => itemtype,
493 itemkey => itemkey,
494 aname => 'WORK_ITEMID');
495
496 l_strategy_id := wf_engine.GetItemAttrNumber(
497 itemtype => itemtype,
498 itemkey => itemkey,
499 aname => 'STRATEGY_ID');
500
501
502 l_user_id := wf_engine.GetItemAttrNumber(
503 itemtype => itemtype,
504 itemkey => itemkey,
505 aname => 'USER_ID');
506
507 l_resp_id := wf_engine.GetItemAttrNumber(
508 itemtype => itemtype,
509 itemkey => itemkey,
510 aname => 'RESP_ID');
511
512 l_resp_appl_id := wf_engine.GetItemAttrNumber(
513 itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'RESP_APPL_ID');
516
517 --set the session
518 --FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
519
520 l_wk_status := wf_engine.GetItemAttrText(
521 itemtype => itemtype,
522 itemkey => itemkey,
523 aname => 'WK_STATUS');
524
525
526 if (l_work_item_id is not null) then
527
528 iex_stry_utl_pub.update_work_item(
529 p_api_version => 1.0,
530 p_commit => FND_API.G_FALSE,
531 p_init_msg_list => FND_API.G_FALSE,
532 p_work_item_id => l_work_item_id,
533 p_status => l_wk_status,
534 x_return_status => l_return_status,
535 x_msg_count => l_msg_count,
536 x_msg_data => l_msg_data
537 );
538
539 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
540 iex_strategy_wf.send_signal(
541 process => 'IEXSTRY' ,
542 strategy_id => l_strategy_id,
543 status => 'CANCELLED',
544 work_item_id => l_work_item_id,
545 signal_source =>'CUSTOM');
546
547
548 end if; -- if update is succcessful;
549 end if;
550
551 result := wf_engine.eng_completed;
552
553 exception
554 WHEN EXC THEN
555 --pass the error message
556 -- get error message and pass
557 iex_strategy_wf.Get_Messages(l_msg_count,l_error);
558 wf_core.context('OKL_STRY_CALL_CO_PUB','wf_send_signal',itemtype,
559 itemkey,to_char(actid),funcmode,l_error);
560 raise;
561
562 when others then
563
564 wf_core.context('OKL_STRY_CALL_CO_PUB','wf_send_signal',itemtype,
565 itemkey,to_char(actid),funcmode);
566 raise;
567 end wf_send_signal;
568
569
570
571
572 ---------------------------------------------------------------------------
573 -- PROCEDURE get_vendorapproval_flag
574 ---------------------------------------------------------------------------
575 PROCEDURE get_vendorapproval_flag(itemtype in varchar2,
576 itemkey in varchar2,
577 actid in number,
578 funcmode in varchar2,
579 result out nocopy varchar2) AS
580
581 l_delinquency_id NUMBER := NULL;
582 l_vendorapproval_flag VARCHAR2(10) := NULL;
583 l_id1 VARCHAR2(200) := NULL;
584
585 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
586
587 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
588 , okh.contract_number
589 FROM iex_delinquencies_all ida
590 ,iex_case_objects ico
591 ,okc_k_headers_b okh
592 WHERE ida.delinquency_id = cp_delinquency_id
593 AND ida.case_id = ico.cas_id
594 AND ico.object_id = okh.id;
595
596 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
597 SELECT prog.id program_id
598 ,prog.contract_number program_number
599 ,lease.id contract_id
600 ,rgp.dnz_chr_id
601 ,lease.contract_number contract_number
602 ,rgp.rgd_code
603 FROM okc_k_headers_b prog,
604 okc_k_headers_b lease,
605 okl_k_headers khr,
606 okc_rule_groups_b rgp
607 WHERE khr.id = lease.id
608 AND khr.khr_id = prog.id
609 AND prog.scs_code = 'PROGRAM'
610 AND lease.scs_code in ('LEASE','LOAN')
611 AND rgp.rgd_code = 'COAGRM'
612 AND rgp.dnz_chr_id = prog.id
613 AND lease.contract_number = cp_contract_number;
614
615
616 l_resource_id NUMBER;
617 l_wait_date DATE;
618
619 cursor c_stry_wkitems (p_workitemid NUMBER) is
620 select resource_id from iex_strategy_work_items
621 where work_item_id =p_workitemid;
622
623 BEGIN
624
625 if funcmode <> 'RUN' then
626 result := wf_engine.eng_null;
627 return;
628 end if;
629
630 --call api get vendor approval
631 --get resource_id from iex_strategy_work_items
632 OPEN c_stry_wkitems(itemkey);
633 FETCH c_stry_wkitems INTO l_resource_id;
634 CLOSE c_stry_wkitems;
635
636 --set notification resource for the reminder notification
637 --this person approves or rejects the notification
638
639 set_notification_resources(
640 p_resource_id =>l_resource_id,
641 itemtype => itemtype,
642 itemkey => itemkey);
643
644
645 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
646 itemkey => itemkey,
647 aname => 'DELINQUENCY_ID');
648 --get vendorapproval flag
649 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
650 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
651 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
652 p_contract_id => cur_rule.program_id
653 ,p_rule_group_code => 'COAGRM'
654 ,p_rule_code => 'COCALL'
655 -- ,p_rule_name =>
656 -- 'Vendor approval required to call customer?'
657 ,p_segment_number => 3
658 ,x_id1 => l_id1
659 ,x_id2 => l_id1
660 ,x_value => l_vendorapproval_flag);
661
662 IF(l_vendorapproval_flag IS NOT NULL) THEN
663 EXIT;
664 END IF;
665 END LOOP
666 EXIT;
667 END LOOP;
668
669 l_vendorapproval_flag := UPPER(NVL(l_vendorapproval_flag, 'No'));
670
671 IF(l_vendorapproval_flag = 'YES') THEN
672 l_vendorapproval_flag := 'Y';
673 ELSE
674 l_vendorapproval_flag := 'N';
675 END IF;
676 result := 'COMPLETE:' || l_vendorapproval_flag;
677
678
679 EXCEPTION
680 when others then
681 result := wf_engine.eng_completed ||':'||wf_no;
682 wf_core.context('OKL_STRY_CALL_CO_PUB','get_vendorapproval_flag',itemtype,
683 itemkey,to_char(actid),funcmode);
684 raise;
685 END get_vendorapproval_flag;
686
687
688 ---------------------------------------------------------------------------
689 -- PROCEDURE get_notification_flag
690 ---------------------------------------------------------------------------
691 PROCEDURE get_notification_flag(itemtype in varchar2,
692 itemkey in varchar2,
693 actid in number,
694 funcmode in varchar2,
695 result out nocopy varchar2) AS
696
697 l_delinquency_id NUMBER := NULL;
698 l_notification_flag VARCHAR2(10) := NULL;
699 l_id1 VARCHAR2(200) := NULL;
700
701 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
702
703 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
704 , okh.contract_number
705 FROM iex_delinquencies_all ida
706 ,iex_case_objects ico
707 ,okc_k_headers_b okh
708 WHERE ida.delinquency_id = cp_delinquency_id
709 AND ida.case_id = ico.cas_id
710 AND ico.object_id = okh.id;
711
712 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
713 SELECT prog.id program_id
714 ,prog.contract_number program_number
715 ,lease.id contract_id
716 ,rgp.dnz_chr_id
717 ,lease.contract_number contract_number
718 ,rgp.rgd_code
719 FROM okc_k_headers_b prog,
720 okc_k_headers_b lease,
721 okl_k_headers khr,
722 okc_rule_groups_b rgp
723 WHERE khr.id = lease.id
724 AND khr.khr_id = prog.id
725 AND prog.scs_code = 'PROGRAM'
726 AND lease.scs_code in ('LEASE','LOAN')
727 AND rgp.rgd_code = 'COAGRM'
728 AND rgp.dnz_chr_id = prog.id
729 AND lease.contract_number = cp_contract_number;
730
731
732
733 BEGIN
734
735 if funcmode <> 'RUN' then
736 result := wf_engine.eng_null;
737 return;
738 end if;
739 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
740 itemkey => itemkey,
741 aname => 'DELINQUENCY_ID');
742
743 set_wait_period(itemtype,itemkey);
744 --get notification_flag
745 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
746 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
747 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
748 p_contract_id => cur_rule.program_id
749 ,p_rule_group_code => 'COAGRM'
750 ,p_rule_code => 'COCALL'
751 -- ,p_rule_name =>
752 --'Call non-notification customer?'
753 ,p_segment_number => 1
754 ,x_id1 => l_id1
755 ,x_id2 => l_id1
756 ,x_value => l_notification_flag);
757
758 IF(l_notification_flag IS NOT NULL) THEN
759 EXIT;
760 END IF;
761 END LOOP
762 EXIT;
763 END LOOP;
764
765 l_notification_flag := UPPER(NVL(l_notification_flag, 'No'));
766
767 IF(l_notification_flag = 'YES') THEN
768 l_notification_flag := 'Y';
769 ELSE
770 l_notification_flag := 'N';
771 END IF;
772 result := 'COMPLETE:' || l_notification_flag;
773
774
775
776 EXCEPTION
777 when others then
778 result := wf_engine.eng_completed ||':'||wf_no;
779 wf_core.context('OKL_STRY_CALL_CO_PUB','get_notification_flag',itemtype,
780 itemkey,to_char(actid),funcmode);
781 raise;
782 END get_notification_flag;
783
784
785
786 ---------------------------------------------------------------------------
787 -- PROCEDURE check_days_past_due
788 ---------------------------------------------------------------------------
789 PROCEDURE check_days_past_due(itemtype in varchar2,
790 itemkey in varchar2,
791 actid in number,
792 funcmode in varchar2,
793 result out nocopy varchar2) AS
794
795 l_delinquency_id NUMBER := NULL;
796 l_noofdays NUMBER :=0;
797 l_actualdays NUMBER :=0;
798 l_contract_id NUMBER;
799 l_id1 VARCHAR2(200) := NULL;
800
801 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
802 l_case_id NUMBER;
803 l_days_after_ven_approval NUMBER;
804
805 /* CURSOR l_khr_csr(p_contract_id IN NUMBER)
806 IS SELECT contract_number
807 FROM okc_k_headers_b
808 WHERE id = p_contract_id;
809 */
810
811 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS
812 SELECT ico.object_id, okh.contract_number
813 FROM iex_delinquencies_all ida
814 ,iex_case_objects ico
815 ,okc_k_headers_b okh
816 WHERE ida.delinquency_id = cp_delinquency_id
817 AND ida.case_id = ico.cas_id
818 AND ico.object_id = okh.id;
819
820 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
821 SELECT prog.id program_id
822 ,prog.contract_number program_number
823 ,lease.id contract_id
824 ,rgp.dnz_chr_id
825 ,lease.contract_number contract_number
826 ,rgp.rgd_code
827 FROM okc_k_headers_b prog,
828 okc_k_headers_b lease,
829 okl_k_headers khr,
830 okc_rule_groups_b rgp
831 WHERE khr.id = lease.id
832 AND khr.khr_id = prog.id
833 AND prog.scs_code = 'PROGRAM'
834 AND lease.scs_code in ('LEASE','LOAN')
835 AND rgp.rgd_code = 'COAGRM'
836 AND rgp.dnz_chr_id = prog.id
837 AND lease.contract_number = cp_contract_number;
838
839 cursor c_get_case_id (p_delinquency_id IN NUMBER) is
840 select case_id from
841 iex_Delinquencies_all d
842 where delinquency_id =p_delinquency_id;
843
844 BEGIN
845
846 if funcmode <> 'RUN' then
847 result := wf_engine.eng_null;
848 return;
849 end if;
850 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
851 itemkey => itemkey,
852 aname => 'DELINQUENCY_ID');
853
854 OPEN c_get_case_id(l_delinquency_id);
855 FETCH c_get_case_id INTO l_case_id;
856 CLOSE c_get_case_id;
857
858 get_delinquent_contract (l_case_id,l_contract_id,l_actualdays);
859
860 --get no of days
861 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
862 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
863 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
864 p_contract_id => cur_rule.program_id
865 ,p_rule_group_code => 'COAGRM'
866 ,p_rule_code => 'COCALL'
867 --,p_rule_name =>
868 --'Number of days after due date to call customer'
869 ,p_segment_number => 2
870 ,x_id1 => l_id1
871 ,x_id2 => l_id1
872 ,x_value => l_noofdays);
873
874 IF(l_noofdays IS NOT NULL) THEN
875 EXIT;
876 END IF;
877 END LOOP
878 EXIT;
879 END LOOP;
880
881 l_noofdays := NVL(l_noofdays,0);
882
883 if l_actualdays > l_noofdays THEN
884 result := 'COMPLETE:' || 'Y';
885 else
886 result := 'COMPLETE:' || 'N';
887 end if;
888
889 --get no of days after getting vendor approval
890 --send this number in the agents email
891 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
892 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
893 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
894 p_contract_id => cur_rule.program_id
895 ,p_rule_group_code => 'COAGRM'
896 ,p_rule_code => 'COCALL'
897 --,p_rule_name =>
898 -- 'Number of days after vendor approval to call customer'
899 ,p_segment_number => 4
900 ,x_id1 => l_id1
901 ,x_id2 => l_id1
902 ,x_value => l_days_after_ven_approval);
903
904 IF(l_days_after_ven_approval IS NOT NULL) THEN
905 --set the workflow attribute
906 --set workitem_template_id attribute
907 wf_engine.SetItemAttrNumber(itemtype => itemtype,
908 itemkey => itemkey,
909 aname => 'CALL_VENDOR_DAYS',
910 avalue => l_days_after_ven_approval);
911
912 EXIT;
913 END IF;
914 END LOOP
915 EXIT;
916 END LOOP;
917
918
919 EXCEPTION
920 when others then
921 result := wf_engine.eng_completed ||':'||wf_no;
922 wf_core.context('OKL_STRY_CALL_CO_PUB','check_days_past_due',itemtype,
923 itemkey,to_char(actid),funcmode);
924 raise;
925 END check_days_past_due;
926
927
928 ---------------------------------------------------------------------------
929 -- PROCEDURE lessor_VISIT_FLAG
930 ---------------------------------------------------------------------------
931 PROCEDURE get_lessor_flag(itemtype in varchar2,
932 itemkey in varchar2,
933 actid in number,
934 funcmode in varchar2,
935 result out nocopy varchar2) AS
936
937 l_delinquency_id NUMBER := NULL;
938 l_lessor_flag VARCHAR2(10) := NULL;
939 l_id1 VARCHAR2(200) := NULL;
940
941 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
942
943 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
944 , okh.contract_number
945 FROM iex_delinquencies_all ida
946 ,iex_case_objects ico
947 ,okc_k_headers_b okh
948 WHERE ida.delinquency_id = cp_delinquency_id
949 AND ida.case_id = ico.cas_id
950 AND ico.object_id = okh.id;
951
952 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
953 SELECT prog.id program_id
954 ,prog.contract_number program_number
955 ,lease.id contract_id
956 ,rgp.dnz_chr_id
957 ,lease.contract_number contract_number
958 ,rgp.rgd_code
959 FROM okc_k_headers_b prog,
960 okc_k_headers_b lease,
961 okl_k_headers khr,
962 okc_rule_groups_b rgp
963 WHERE khr.id = lease.id
964 AND khr.khr_id = prog.id
965 AND prog.scs_code = 'PROGRAM'
966 AND lease.scs_code in ('LEASE','LOAN')
967 AND rgp.rgd_code = 'COAGRM'
968 AND rgp.dnz_chr_id = prog.id
969 AND lease.contract_number = cp_contract_number;
970
971 BEGIN
972
973 if funcmode <> 'RUN' then
974 result := wf_engine.eng_null;
975 return;
976 end if;
977 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
978 itemkey => itemkey,
979 aname => 'DELINQUENCY_ID');
980
981 set_wait_period(itemtype,itemkey);
982
983 --get lessor_flag
984 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
985 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
986 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
987 p_contract_id => cur_rule.program_id
988 ,p_rule_group_code => 'COAGRM'
989 ,p_rule_code => 'COVIST'
990 -- ,p_rule_name =>
991 --'Lessor allowed to visit customer?'
992 ,p_segment_number => 1
993 ,x_id1 => l_id1
994 ,x_id2 => l_id1
995 ,x_value => l_lessor_flag);
996
997 IF(l_lessor_flag IS NOT NULL) THEN
998 EXIT;
999 END IF;
1000 END LOOP
1001 EXIT;
1002 END LOOP;
1003
1004 l_lessor_flag := UPPER(NVL(l_lessor_flag, 'No'));
1005
1006 IF(l_lessor_flag = 'YES') THEN
1007 l_lessor_flag := 'Y';
1008 ELSE
1009 l_lessor_flag := 'N';
1010 END IF;
1011 result := 'COMPLETE:' || l_lessor_flag;
1012
1013
1014
1015 EXCEPTION
1016 when others then
1017 result := wf_engine.eng_completed ||':'||wf_no;
1018 wf_core.context('OKL_STRY_CALL_CO_PUB','get_lessor_flag',itemtype,
1019 itemkey,to_char(actid),funcmode);
1020 raise;
1021 END get_lessor_flag;
1022
1023
1024
1025 ---------------------------------------------------------------------------
1026 -- PROCEDURE Customer_VISIT_FLAG
1027 ---------------------------------------------------------------------------
1028 PROCEDURE get_Customer_flag(itemtype in varchar2,
1029 itemkey in varchar2,
1030 actid in number,
1031 funcmode in varchar2,
1032 result out nocopy varchar2) AS
1033
1034 l_delinquency_id NUMBER := NULL;
1035 l_Customer_flag VARCHAR2(10) := NULL;
1036 l_id1 VARCHAR2(200) := NULL;
1037
1038 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1039
1040 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
1041 , okh.contract_number
1042 FROM iex_delinquencies_all ida
1043 ,iex_case_objects ico
1044 ,okc_k_headers_b okh
1045 WHERE ida.delinquency_id = cp_delinquency_id
1046 AND ida.case_id = ico.cas_id
1047 AND ico.object_id = okh.id;
1048
1049 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
1050 SELECT prog.id program_id
1051 ,prog.contract_number program_number
1052 ,lease.id contract_id
1053 ,rgp.dnz_chr_id
1054 ,lease.contract_number contract_number
1055 ,rgp.rgd_code
1056 FROM okc_k_headers_b prog,
1057 okc_k_headers_b lease,
1058 okl_k_headers khr,
1059 okc_rule_groups_b rgp
1060 WHERE khr.id = lease.id
1061 AND khr.khr_id = prog.id
1062 AND prog.scs_code = 'PROGRAM'
1063 AND lease.scs_code in ('LEASE','LOAN')
1064 AND rgp.rgd_code = 'COAGRM'
1065 AND rgp.dnz_chr_id = prog.id
1066 AND lease.contract_number = cp_contract_number;
1067
1068 BEGIN
1069
1070 if funcmode <> 'RUN' then
1071 result := wf_engine.eng_null;
1072 return;
1073 end if;
1074 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1075 itemkey => itemkey,
1076 aname => 'DELINQUENCY_ID');
1077
1078 --get Customer_flag
1079 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
1080 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
1081 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
1082 p_contract_id => cur_rule.program_id
1083 ,p_rule_group_code => 'COAGRM'
1084 ,p_rule_code => 'COVIST'
1085 --,p_rule_name =>
1086 --'Vendor allowed to visit customer?'
1087 ,p_segment_number => 2
1088 ,x_id1 => l_id1
1089 ,x_id2 => l_id1
1090 ,x_value => l_Customer_flag);
1091
1092 IF(l_Customer_flag IS NOT NULL) THEN
1093 EXIT;
1094 END IF;
1095 END LOOP
1096 EXIT;
1097 END LOOP;
1098
1099 l_Customer_flag := UPPER(NVL(l_Customer_flag, 'No'));
1100
1101 IF(l_Customer_flag = 'YES') THEN
1102 l_Customer_flag := 'Y';
1103 ELSE
1104 l_Customer_flag := 'N';
1105 END IF;
1106 result := 'COMPLETE:' || l_Customer_flag;
1107
1108
1109
1110 EXCEPTION
1111 when others then
1112 result := wf_engine.eng_completed ||':'||wf_no;
1113 wf_core.context('OKL_STRY_CALL_CO_PUB','get_Customer_flag',itemtype,
1114 itemkey,to_char(actid),funcmode);
1115 raise;
1116 END get_Customer_flag;
1117
1118 ---------------------------------------------------------------------------
1119 -- PROCEDURE get_Vendor_approval_flag
1120 ---------------------------------------------------------------------------
1121 PROCEDURE get_Vendor_approval_flag(itemtype in varchar2,
1122 itemkey in varchar2,
1123 actid in number,
1124 funcmode in varchar2,
1125 result out nocopy varchar2) AS
1126
1127 l_delinquency_id NUMBER := NULL;
1128 l_Vendor_Customer_flag VARCHAR2(10) := NULL;
1129 l_id1 VARCHAR2(200) := NULL;
1130
1131 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1132
1133 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
1134 , okh.contract_number
1135 FROM iex_delinquencies_all ida
1136 ,iex_case_objects ico
1137 ,okc_k_headers_b okh
1138 WHERE ida.delinquency_id = cp_delinquency_id
1139 AND ida.case_id = ico.cas_id
1140 AND ico.object_id = okh.id;
1141
1142 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
1143 SELECT prog.id program_id
1144 ,prog.contract_number program_number
1145 ,lease.id contract_id
1146 ,rgp.dnz_chr_id
1147 ,lease.contract_number contract_number
1148 ,rgp.rgd_code
1149 FROM okc_k_headers_b prog,
1150 okc_k_headers_b lease,
1151 okl_k_headers khr,
1152 okc_rule_groups_b rgp
1153 WHERE khr.id = lease.id
1154 AND khr.khr_id = prog.id
1155 AND prog.scs_code = 'PROGRAM'
1156 AND lease.scs_code in ('LEASE','LOAN')
1157 AND rgp.rgd_code = 'COAGRM'
1158 AND rgp.dnz_chr_id = prog.id
1159 AND lease.contract_number = cp_contract_number;
1160
1161 BEGIN
1162
1163 if funcmode <> 'RUN' then
1164 result := wf_engine.eng_null;
1165 return;
1166 end if;
1167 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1168 itemkey => itemkey,
1169 aname => 'DELINQUENCY_ID');
1170
1171 --get Vendor_Customer_flag
1172 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
1173 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
1174 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
1175 p_contract_id => cur_rule.program_id
1176 ,p_rule_group_code => 'COAGRM'
1177 ,p_rule_code => 'COVIST'
1178 --,p_rule_name =>
1179 --'Vendor approval required to visit customer?'
1180 ,p_segment_number => 3
1181 ,x_id1 => l_id1
1182 ,x_id2 => l_id1
1183 ,x_value => l_Vendor_Customer_flag);
1184
1185 IF(l_Vendor_Customer_flag IS NOT NULL) THEN
1186 EXIT;
1187 END IF;
1188 END LOOP
1189 EXIT;
1190 END LOOP;
1191
1192 l_Vendor_Customer_flag := UPPER(NVL(l_Vendor_Customer_flag, 'No'));
1193
1194 IF(l_Vendor_Customer_flag = 'YES') THEN
1195 l_Vendor_Customer_flag := 'Y';
1196 ELSE
1197 l_Vendor_Customer_flag := 'N';
1198 END IF;
1199 result := 'COMPLETE:' || l_Vendor_Customer_flag;
1200
1201
1202
1203
1204
1205 EXCEPTION
1206 when others then
1207 result := wf_engine.eng_completed ||':'||wf_no;
1208 wf_core.context('OKL_STRY_CALL_CO_PUB','get_Vendor_approval_flag',itemtype,
1209 itemkey,to_char(actid),funcmode);
1210 raise;
1211 END get_Vendor_approval_flag;
1212
1213 --------------------------------------------------------------------------
1214 -- PROCEDURE Vend_Cust_NOTIFY
1215 ---------------------------------------------------------------------------
1216 PROCEDURE get_Vend_Cust_NOTIFY_flag(itemtype in varchar2,
1217 itemkey in varchar2,
1218 actid in number,
1219 funcmode in varchar2,
1220 result out nocopy varchar2) AS
1221
1222 l_delinquency_id NUMBER := NULL;
1223 l_Vend_cust_notify_flag VARCHAR2(10) := NULL;
1224 l_id1 VARCHAR2(200) := NULL;
1225
1226 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1227
1228 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
1229 , okh.contract_number
1230 FROM iex_delinquencies_all ida
1231 ,iex_case_objects ico
1232 ,okc_k_headers_b okh
1233 WHERE ida.delinquency_id = cp_delinquency_id
1234 AND ida.case_id = ico.cas_id
1235 AND ico.object_id = okh.id;
1236
1237 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
1238 SELECT prog.id program_id
1239 ,prog.contract_number program_number
1240 ,lease.id contract_id
1241 ,rgp.dnz_chr_id
1242 ,lease.contract_number contract_number
1243 ,rgp.rgd_code
1244 FROM okc_k_headers_b prog,
1245 okc_k_headers_b lease,
1246 okl_k_headers khr,
1247 okc_rule_groups_b rgp
1248 WHERE khr.id = lease.id
1249 AND khr.khr_id = prog.id
1250 AND prog.scs_code = 'PROGRAM'
1251 AND lease.scs_code in ('LEASE','LOAN')
1252 AND rgp.rgd_code = 'COAGRM'
1253 AND rgp.dnz_chr_id = prog.id
1254 AND lease.contract_number = cp_contract_number;
1255
1256 BEGIN
1257
1258 if funcmode <> 'RUN' then
1259 result := wf_engine.eng_null;
1260 return;
1261 end if;
1262 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1263 itemkey => itemkey,
1264 aname => 'DELINQUENCY_ID');
1265
1266 --get Vend_cust_notify_flag
1267 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
1268 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
1269 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
1270 p_contract_id => cur_rule.program_id
1271 ,p_rule_group_code => 'COAGRM'
1272 ,p_rule_code => 'COVIST'
1273 --,p_rule_name =>
1274 --'Vendor notification required prior to customer visit?'
1275 ,p_segment_number => 4
1276 ,x_id1 => l_id1
1277 ,x_id2 => l_id1
1278 ,x_value => l_Vend_cust_notify_flag);
1279
1280 IF(l_Vend_cust_notify_flag IS NOT NULL) THEN
1281 EXIT;
1282 END IF;
1283 END LOOP
1284 EXIT;
1285 END LOOP;
1286
1287 l_Vend_cust_notify_flag := UPPER(NVL(l_Vend_cust_notify_flag, 'No'));
1288
1289 IF(l_Vend_cust_notify_flag = 'YES') THEN
1290 l_Vend_cust_notify_flag := 'Y';
1291 ELSE
1292 l_Vend_cust_notify_flag := 'N';
1293 END IF;
1294 result := 'COMPLETE:' || l_Vend_cust_notify_flag;
1295
1296
1297
1298 EXCEPTION
1299 when others then
1300 result := wf_engine.eng_completed ||':'||wf_no;
1301 wf_core.context('OKL_STRY_CALL_CO_PUB','get_Vend_cust_notify_flag',itemtype,
1302 itemkey,to_char(actid),funcmode);
1303 raise;
1304 END get_Vend_cust_notify_flag;
1305
1306
1307
1308 ---------------------------------------------------------------------------
1309 -- PROCEDURE Days to take notice of assignment for Syndicated Account?
1310 ---------------------------------------------------------------------------
1311 PROCEDURE check_days_for_syn_acct(itemtype in varchar2,
1312 itemkey in varchar2,
1313 actid in number,
1314 funcmode in varchar2,
1315 result out nocopy varchar2) AS
1316
1317 l_delinquency_id NUMBER := NULL;
1318 l_noofdays NUMBER :=0;
1319 l_actualdays NUMBER :=0;
1320 l_contract_id NUMBER;
1321 l_id1 VARCHAR2(200) := NULL;
1322
1323 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
1324 l_case_id NUMBER;
1325 l_days_after_ven_approval NUMBER;
1326
1327 /* CURSOR l_khr_csr(p_contract_id IN NUMBER)
1328 IS SELECT contract_number
1329 FROM okc_k_headers_b
1330 WHERE id = p_contract_id;
1331 */
1332
1333 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS
1334 SELECT ico.object_id, okh.contract_number
1335 FROM iex_delinquencies_all ida
1336 ,iex_case_objects ico
1337 ,okc_k_headers_b okh
1338 WHERE ida.delinquency_id = cp_delinquency_id
1339 AND ida.case_id = ico.cas_id
1340 AND ico.object_id = okh.id;
1341
1342 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
1343 SELECT prog.id program_id
1344 ,prog.contract_number program_number
1345 ,lease.id contract_id
1346 ,rgp.dnz_chr_id
1347 ,lease.contract_number contract_number
1348 ,rgp.rgd_code
1349 FROM okc_k_headers_b prog,
1350 okc_k_headers_b lease,
1351 okl_k_headers khr,
1352 okc_rule_groups_b rgp
1353 WHERE khr.id = lease.id
1354 AND khr.khr_id = prog.id
1355 AND prog.scs_code = 'PROGRAM'
1356 AND lease.scs_code in ('LEASE','LOAN')
1357 AND rgp.rgd_code = 'COAGRM'
1358 AND rgp.dnz_chr_id = prog.id
1359 AND lease.contract_number = cp_contract_number;
1360
1361 cursor c_get_case_id (p_delinquency_id IN NUMBER) is
1362 select case_id from
1363 iex_Delinquencies_all d
1364 where delinquency_id =p_delinquency_id;
1365
1366
1367
1368 l_status VARCHAR2(1);
1369 x_syndicate_flag VARCHAR2(1) DEFAULT 'N';
1370 l_party_id number;
1371 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
1372
1373 BEGIN
1374
1375 if funcmode <> 'RUN' then
1376 result := wf_engine.eng_null;
1377 return;
1378 end if;
1379
1380
1381
1382 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1383 itemkey => itemkey,
1384 aname => 'DELINQUENCY_ID');
1385
1386 l_party_id := wf_engine.GetItemAttrNumber(
1387 itemtype => itemtype,
1388 itemkey => itemkey,
1389 aname => 'PARTY_ID');
1390
1391 OPEN c_get_case_id(l_delinquency_id);
1392 FETCH c_get_case_id INTO l_case_id;
1393 CLOSE c_get_case_id;
1394
1395 get_delinquent_contract (l_case_id,l_contract_id,l_actualdays);
1396
1397 set_wait_period(itemtype,itemkey);
1398
1399 l_status := okl_contract_info.
1400 get_syndicate_flag(l_contract_id, x_syndicate_flag);
1401
1402 IF l_status = 'Y' THEN
1403 --get no of days
1404 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
1405 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
1406 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
1407 p_contract_id => cur_rule.program_id
1408 ,p_rule_group_code => 'COAGRM'
1409 ,p_rule_code => 'COWRIT'
1410 --,p_rule_name =>
1411 --'Days to take notice of assignment for Syndicated Account?'
1412 ,p_segment_number => 1
1413 ,x_id1 => l_id1
1414 ,x_id2 => l_id1
1415 ,x_value => l_noofdays);
1416
1417 IF(l_noofdays IS NOT NULL) THEN
1418 --set the workflow attribute
1419 --set workitem_template_id attribute
1420 wf_engine.SetItemAttrNumber(itemtype => itemtype,
1421 itemkey => itemkey,
1422 aname => 'SYNDICATED_DAYS',
1423 avalue => l_days_after_ven_approval);
1424
1425 EXIT;
1426 END IF;
1427 END LOOP
1428 EXIT;
1429 END LOOP;
1430 result := 'COMPLETE:' || 'Y';
1431
1432 else
1433 result := 'COMPLETE:' || 'N';
1434 end if;
1435
1436 GET_PARTY_EMAIL (l_party_id , l_email );
1437 If l_email is null THEN
1438 result := 'COMPLETE:' || 'N';
1439 return;
1440 else
1441 wf_engine.SetItemAttrText(itemtype => itemtype,
1442 itemkey => itemkey,
1443 aname => 'PARTY_EMAIL',
1444 avalue => l_email);
1445 result := 'COMPLETE:' || 'Y';
1446 end if;
1447
1448
1449
1450 EXCEPTION
1451 when others then
1452 result := wf_engine.eng_completed ||':'||wf_no;
1453 wf_core.context('OKL_STRY_CALL_CO_PUB','check_days_for_syn_acct',itemtype,
1454 itemkey,to_char(actid),funcmode);
1455 raise;
1456 END check_days_for_syn_acct;
1457
1458
1459
1460 END OKL_STRY_CALL_CO_PUB ;
1461
1462