[Home] [Help]
PACKAGE BODY: APPS.OKL_CO_WF
Source
1 PACKAGE BODY OKL_CO_WF AS
2 /* $Header: OKLRCOWB.pls 115.8 2002/12/18 05:51:12 rabhupat noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE get_syndicate_flag
5 ---------------------------------------------------------------------------
6 PROCEDURE get_syndicate_flag(itemtype in varchar2,
7 itemkey in varchar2,
8 actid in number,
9 funcmode in varchar2,
10 resultout out nocopy varchar2) AS
11 l_delinquency_id NUMBER := NULL;
12 l_report_after_days NUMBER := NULL;
13 l_syndicate_flag VARCHAR2(10) := 'N';
14 l_case_number VARCHAR2(240) := NULL;
15 --l_notification_role VARCHAR2(25) := 'ADMIN';
16 l_report_date Date := NULL;
17 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
18
19 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
20 ,ica.case_number
21 FROM iex_delinquencies_all ida
22 ,iex_case_objects ico
23 ,iex_cases_all_b ica
24 WHERE ida.delinquency_id = cp_delinquency_id
25 AND ida.case_id = ico.cas_id
26 AND ico.cas_id = ica.cas_id;
27 BEGIN
28 if (funcmode = 'RUN') then
29 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
30 itemkey => itemkey,
31 aname => 'DELINQUENCY_ID');
32
33 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
34 --get syndicate flag
35 l_return_status := OKL_CONTRACT_INFO.get_syndicate_flag(
36 p_contract_id => cur.object_id
37 ,x_syndicate_flag => l_syndicate_flag);
38 l_case_number := cur.case_number;
39
40 IF(l_return_status <> Fnd_Api.G_RET_STS_SUCCESS) THEN
41 raise G_EXCEPTION_HALT_VALIDATION;
42 END IF;
43
44 EXIT;
45 END LOOP;
46 resultout := 'COMPLETE:' || l_syndicate_flag;
47
48 IF(itemtype = 'OKLCORCB') THEN
49 --get the number of days after which a notification is sent to get approval
50 --for reporting the customer to the credit bureau.
51 l_report_after_days := fnd_profile.value('IEX_CB_NOTIFY_GRACE_DAYS');
52 --l_report_after_days := 0.003;
53
54 l_report_date := SYSDATE + l_report_after_days;
55
56 wf_engine.SetItemAttrDate (itemtype=> itemtype,
57 itemkey => itemkey,
58 aname => 'REPORT_DATE',
59 avalue => l_report_date);
60 END IF;
61
62 wf_engine.SetItemAttrText (itemtype=> itemtype,
63 itemkey => itemkey,
64 aname => 'CASE_NUMBER',
65 avalue => l_case_number);
66
67 /*
68 wf_engine.SetItemAttrText (itemtype=> itemtype,
69 itemkey => itemkey,
70 aname => 'NOTIFICATION_USERNAME',
71 avalue => l_notification_role);
72 */
73 RETURN;
74 end if;
75 --
76 -- CANCEL mode
77 --
78 if (funcmode = 'CANCEL') then
79 --
80 resultout := 'COMPLETE:';
81 return;
82 --
83 end if;
84 --
85 -- TIMEOUT mode
86 --
87 if (funcmode = 'TIMEOUT') then
88 --
89 resultout := 'COMPLETE:';
90 return;
91 --
92 end if;
93
94 EXCEPTION
95 when others then
96 --resultout := wf_engine.eng_completed ||':'||wf_no;
97 wf_core.context('OKL_CO_WF','get_syndicate_flag',itemtype,
98 itemkey,to_char(actid),funcmode);
99 raise;
100 END get_syndicate_flag;
101
102 ---------------------------------------------------------------------------
103 -- PROCEDURE get_sendtothirdparty_flag
104 ---------------------------------------------------------------------------
105 PROCEDURE get_sendtothirdparty_flag(itemtype in varchar2,
106 itemkey in varchar2,
107 actid in number,
108 funcmode in varchar2,
109 resultout out nocopy varchar2) AS
110
111 l_delinquency_id NUMBER := NULL;
112 l_sendtothirdparty_flag VARCHAR2(10) := NULL;
113 l_id1 VARCHAR2(200) := NULL;
114
115 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
116
117 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
118 , okh.contract_number
119 FROM iex_delinquencies_all ida
120 ,iex_case_objects ico
121 ,okc_k_headers_b okh
122 WHERE ida.delinquency_id = cp_delinquency_id
123 AND ida.case_id = ico.cas_id
124 AND ico.object_id = okh.id;
125
126 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
127 SELECT prog.id program_id
128 ,prog.contract_number program_number
129 ,lease.id contract_id
130 ,rgp.dnz_chr_id
131 ,lease.contract_number contract_number
132 ,rgp.rgd_code
133 FROM okc_k_headers_b prog,
134 okc_k_headers_b lease,
135 okl_k_headers khr,
136 okc_rule_groups_b rgp
137 WHERE khr.id = lease.id
138 AND khr.khr_id = prog.id
139 AND prog.scs_code = 'PROGRAM'
140 AND lease.scs_code in ('LEASE','LOAN')
141 AND rgp.rgd_code = 'COAGRM'
142 AND rgp.dnz_chr_id = prog.id
143 AND lease.contract_number = cp_contract_number;
144
145 BEGIN
146 if (funcmode = 'RUN') then
147 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
148 itemkey => itemkey,
149 aname => 'DELINQUENCY_ID');
150
151 --get sendto_third_party flag
152 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
153 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
154 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
155 p_contract_id => cur_rule.program_id
156 ,p_rule_group_code => 'COAGRM'
157 ,p_rule_code => 'COPTA'
158 ,p_segment_number => 1
159 ,x_id1 => l_id1
160 ,x_id2 => l_id1
161 ,x_value => l_sendtothirdparty_flag);
162
163 IF(l_sendtothirdparty_flag IS NOT NULL) THEN
164 EXIT;
165 END IF;
166 END LOOP
167 EXIT;
168 END LOOP;
169
170 l_sendtothirdparty_flag := UPPER(NVL(l_sendtothirdparty_flag, 'Yes'));
171
172 IF(l_sendtothirdparty_flag = 'YES') THEN
173 l_sendtothirdparty_flag := 'Y';
174 ELSE
175 l_sendtothirdparty_flag := 'N';
176 END IF;
177 resultout := 'COMPLETE:' || l_sendtothirdparty_flag;
178
179 RETURN ;
180 end if;
181 --
182 -- CANCEL mode
183 --
184 if (funcmode = 'CANCEL') then
185 --
186 resultout := 'COMPLETE:';
187 return;
188 --
189 end if;
190 --
191 -- TIMEOUT mode
192 --
193 if (funcmode = 'TIMEOUT') then
194 --
195 resultout := 'COMPLETE:';
196 return;
197 --
198 end if;
199
200 EXCEPTION
201 when others then
202 --resultout := wf_engine.eng_completed ||':'||wf_no;
203 wf_core.context('OKL_CO_WF','get_sendtothirdparty_flag',itemtype,
204 itemkey,to_char(actid),funcmode);
205 raise;
206 END get_sendtothirdparty_flag;
207
208 ---------------------------------------------------------------------------
209 -- PROCEDURE get_vendorapproval_flag
210 ---------------------------------------------------------------------------
211 PROCEDURE get_vendorapproval_flag(itemtype in varchar2,
212 itemkey in varchar2,
213 actid in number,
214 funcmode in varchar2,
215 resultout out nocopy varchar2) AS
216
217 l_delinquency_id NUMBER := NULL;
218 l_vendorapproval_flag VARCHAR2(10) := NULL;
219 l_id1 VARCHAR2(200) := NULL;
220
221 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
222
223 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
224 , okh.contract_number
225 FROM iex_delinquencies_all ida
226 ,iex_case_objects ico
227 ,okc_k_headers_b okh
228 WHERE ida.delinquency_id = cp_delinquency_id
229 AND ida.case_id = ico.cas_id
230 AND ico.object_id = okh.id;
231
232 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
233 SELECT prog.id program_id
234 ,prog.contract_number program_number
235 ,lease.id contract_id
236 ,rgp.dnz_chr_id
237 ,lease.contract_number contract_number
238 ,rgp.rgd_code
239 FROM okc_k_headers_b prog,
240 okc_k_headers_b lease,
241 okl_k_headers khr,
242 okc_rule_groups_b rgp
243 WHERE khr.id = lease.id
244 AND khr.khr_id = prog.id
245 AND prog.scs_code = 'PROGRAM'
246 AND lease.scs_code in ('LEASE','LOAN')
247 AND rgp.rgd_code = 'COAGRM'
248 AND rgp.dnz_chr_id = prog.id
249 AND lease.contract_number = cp_contract_number;
250
251 BEGIN
252 if (funcmode = 'RUN') then
253 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
254 itemkey => itemkey,
255 aname => 'DELINQUENCY_ID');
256
257 --get vendorapproval flag
258 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
259 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
260 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
261 p_contract_id => cur_rule.program_id
262 ,p_rule_group_code => 'COAGRM'
263 ,p_rule_code => 'COPTA'
264 ,p_segment_number => 2
265 ,x_id1 => l_id1
266 ,x_id2 => l_id1
267 ,x_value => l_vendorapproval_flag);
268
269 IF(l_vendorapproval_flag IS NOT NULL) THEN
270 EXIT;
271 END IF;
272 END LOOP
273 EXIT;
274 END LOOP;
275
276 l_vendorapproval_flag := UPPER(NVL(l_vendorapproval_flag, 'No'));
277
278 IF(l_vendorapproval_flag = 'YES') THEN
279 l_vendorapproval_flag := 'Y';
280 ELSE
281 l_vendorapproval_flag := 'N';
282 END IF;
283 resultout := 'COMPLETE:' || l_vendorapproval_flag;
284
285 RETURN ;
286 end if;
287 --
288 -- CANCEL mode
289 --
290 if (funcmode = 'CANCEL') then
291 --
292 resultout := 'COMPLETE:';
293 return;
294 --
295 end if;
296 --
297 -- TIMEOUT mode
298 --
299 if (funcmode = 'TIMEOUT') then
300 --
301 resultout := 'COMPLETE:';
302 return;
303 --
304 end if;
305
306 EXCEPTION
307 when others then
308 --resultout := wf_engine.eng_completed ||':'||wf_no;
309 wf_core.context('OKL_CO_WF','get_vendorapproval_flag',itemtype,
310 itemkey,to_char(actid),funcmode);
311 raise;
312 END get_vendorapproval_flag;
313
314 ---------------------------------------------------------------------------
315 -- PROCEDURE get_vendornotify_flag
316 ---------------------------------------------------------------------------
317 PROCEDURE get_vendornotify_flag(itemtype in varchar2,
318 itemkey in varchar2,
319 actid in number,
320 funcmode in varchar2,
321 resultout out nocopy varchar2) AS
322
323 l_delinquency_id NUMBER := NULL;
324 l_vendornotify_flag VARCHAR2(10) := NULL;
325 l_id1 VARCHAR2(200) := NULL;
326
327 l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
328
329 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
330 , okh.contract_number
331 FROM iex_delinquencies_all ida
332 ,iex_case_objects ico
333 ,okc_k_headers_b okh
334 WHERE ida.delinquency_id = cp_delinquency_id
335 AND ida.case_id = ico.cas_id
336 AND ico.object_id = okh.id;
337
338 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
339 SELECT prog.id program_id
340 ,prog.contract_number program_number
341 ,lease.id contract_id
342 ,rgp.dnz_chr_id
343 ,lease.contract_number contract_number
344 ,rgp.rgd_code
345 FROM okc_k_headers_b prog,
346 okc_k_headers_b lease,
347 okl_k_headers khr,
348 okc_rule_groups_b rgp
349 WHERE khr.id = lease.id
350 AND khr.khr_id = prog.id
351 AND prog.scs_code = 'PROGRAM'
352 AND lease.scs_code in ('LEASE','LOAN')
353 AND rgp.rgd_code = 'COAGRM'
354 AND rgp.dnz_chr_id = prog.id
355 AND lease.contract_number = cp_contract_number;
356
357 BEGIN
358 if (funcmode = 'RUN') then
359 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'DELINQUENCY_ID');
362
363 --get vendornotify flag
364 FOR cur_khr IN l_khr_csr(l_delinquency_id) LOOP
365 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
366 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
367 p_contract_id => cur_rule.program_id
368 ,p_rule_group_code => 'COAGRM'
369 ,p_rule_code => 'COPTA'
370 ,p_segment_number => 3
371 ,x_id1 => l_id1
372 ,x_id2 => l_id1
373 ,x_value => l_vendornotify_flag);
374
375 IF(l_vendornotify_flag IS NOT NULL) THEN
376 EXIT;
377 END IF;
378 END LOOP
379 EXIT;
380 END LOOP;
381
382 l_vendornotify_flag := UPPER(NVL(l_vendornotify_flag, 'No'));
383
384 IF(l_vendornotify_flag = 'YES') THEN
385 l_vendornotify_flag := 'Y';
386 ELSE
387 l_vendornotify_flag := 'N';
388 END IF;
389 resultout := 'COMPLETE:' || l_vendornotify_flag;
390
391 RETURN ;
392 end if;
393 --
394 -- CANCEL mode
395 --
396 if (funcmode = 'CANCEL') then
397 --
398 resultout := 'COMPLETE:';
399 return;
400 --
401 end if;
402 --
403 -- TIMEOUT mode
404 --
405 if (funcmode = 'TIMEOUT') then
406 --
407 resultout := 'COMPLETE:';
408 return;
409 --
410 end if;
411
412 EXCEPTION
413 when others then
414 --resultout := wf_engine.eng_completed ||':'||wf_no;
415 wf_core.context('OKL_CO_WF','get_vendorapproval_flag',itemtype,
416 itemkey,to_char(actid),funcmode);
417 raise;
418 END get_vendornotify_flag;
419
420 ---------------------------------------------------------------------------
421 -- PROCEDURE get_vendor_info
422 ---------------------------------------------------------------------------
423 PROCEDURE get_vendor_info(p_case_number in varchar2,
424 x_vendor_id out nocopy number,
425 x_vendor_name out nocopy varchar2,
426 x_vendor_email out nocopy varchar2,
427 x_return_status out nocopy varchar2) AS
428
429 --get vendor info
430 CURSOR l_vendor_csr(cp_case_number IN VARCHAR2) IS SELECT pv.vendor_id
431 ,pv.vendor_name
432 --,pvs.email_address
433 FROM iex_cases_all_b ica
434 ,iex_case_objects ico
435 ,okc_k_party_roles_v opr
436 ,po_vendors pv
437 --,po_vendor_sites_all pvs
438 WHERE ica.case_number = cp_case_number
439 AND ica.cas_id = ico.cas_id
440 AND ico.object_id =opr.dnz_chr_id
441 AND opr.rle_code = 'OKL_VENDOR'
442 AND opr.object1_id1 = pv.vendor_id;
443 --AND pv.vendor_id = pvs.vendor_id;
444
445 --get contracts on case
446 CURSOR l_khr_csr(cp_case_number IN VARCHAR2) IS SELECT ico.object_id
447 , okh.contract_number
448 FROM iex_cases_all_b ica
449 ,iex_case_objects ico
450 ,okc_k_headers_b okh
451 WHERE ica.case_number = cp_case_number
452 AND ica.cas_id = ico.cas_id
453 AND ico.object_id = okh.id;
454
455 --get program id to get the vendor sites id
456 CURSOR l_rule_csr(cp_contract_number IN VARCHAR2) IS
457 SELECT prog.id program_id
458 ,prog.contract_number program_number
459 ,lease.id contract_id
460 ,rgp.dnz_chr_id
461 ,lease.contract_number contract_number
462 ,rgp.rgd_code
463 FROM okc_k_headers_b prog,
464 okc_k_headers_b lease,
465 okl_k_headers khr,
466 okc_rule_groups_b rgp
467 WHERE khr.id = lease.id
468 AND khr.khr_id = prog.id
469 AND prog.scs_code = 'PROGRAM'
470 AND lease.scs_code in ('LEASE','LOAN')
471 AND rgp.rgd_code = 'COAGRM'
472 AND rgp.dnz_chr_id = prog.id
473 AND lease.contract_number = cp_contract_number;
474
475 --get vendor email address
476 CURSOR l_email_csr(cp_vendor_site_code IN VARCHAR2) IS
477 SELECT pvs.email_address
478 FROM po_vendor_sites_all pvs
479 WHERE pvs.vendor_site_code = cp_vendor_site_code;
480
481 l_id1 VARCHAR2(200) := NULL;
482 l_id2 VARCHAR2(200) := NULL;
483 l_vendor_site_code VARCHAR2(200) := null;
484 l_return_status VARCHAR2(1);
485 BEGIN
486 --get vendor_site_code flag
487 FOR cur_khr IN l_khr_csr(p_case_number) LOOP
488 FOR cur_rule IN l_rule_csr(cur_khr.contract_number) LOOP
489 l_return_status := OKL_CONTRACT_INFO.get_rule_value(
490 p_contract_id => cur_rule.program_id
491 ,p_rule_group_code => 'COAGRM'
492 ,p_rule_code => 'COVNAG'
493 ,p_segment_number => 1
494 ,x_id1 => l_id1
495 ,x_id2 => l_id2
496 ,x_value => l_vendor_site_code);
497
498 IF(l_vendor_site_code IS NOT NULL) THEN
499 EXIT;
500 END IF;
501 END LOOP
502 EXIT;
503 END LOOP;
504
505 FOR cur_email IN l_email_csr(l_vendor_site_code) LOOP
506 x_vendor_email := cur_email.email_address;
507 END LOOP;
508
509 FOR cur IN l_vendor_csr(p_case_number) LOOP
510 x_vendor_id := cur.vendor_id;
511 x_vendor_name := cur.vendor_name;
512 --x_vendor_email := cur.email_address;
513 EXIT;
514 END LOOP;
515 END get_vendor_info;
516
517 ---------------------------------------------------------------------------
518 -- PROCEDURE notify_customer
519 ---------------------------------------------------------------------------
520 PROCEDURE notify_customer(itemtype in varchar2,
521 itemkey in varchar2,
522 actid in number,
523 funcmode in varchar2,
524 resultout out nocopy varchar2) AS
525 l_delinquency_id NUMBER := NULL;
526 l_api_version NUMBER := 1.0;
527 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
528 l_return_status VARCHAR2(1);
529 lx_msg_count NUMBER ;
530 lx_msg_data VARCHAR2(2000);
531
532 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
533 FROM iex_delinquencies_all ida
534 ,iex_case_objects ico
535 WHERE ida.delinquency_id = cp_delinquency_id
536 AND ida.case_id = ico.cas_id;
537 BEGIN
538 if (funcmode = 'RUN') then
539 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
540 itemkey => itemkey,
541 aname => 'DELINQUENCY_ID');
542
543 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
544 iex_open_interface_pub.insert_pending(
545 p_api_version => l_api_version,
546 p_init_msg_list => l_init_msg_list,
547 p_object1_id1 => cur.object_id,
548 p_object1_id2 => '#',
549 p_jtot_object1_code => 'OKX_LEASE',
550 p_action => IEX_OPI_PVT.ACTION_NOTIFY_CUST,
551 p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
552 p_comments => OKC_API.G_MISS_CHAR,
553 p_ext_agncy_id => NULL,
554 p_review_date => NULL,
555 p_recall_date => NULL,
556 p_automatic_recall_flag => NULL,
557 p_review_before_recall_flag => NULL,
558 x_return_status => l_return_status,
559 x_msg_count => lx_msg_count,
560 x_msg_data => lx_msg_data);
561
562 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
563 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
564 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
565 RAISE okl_api.G_EXCEPTION_ERROR;
566 END IF;
567 END LOOP;
568 resultout := 'COMPLETE:';
569 RETURN ;
570 end if;
571 --
572 -- CANCEL mode
573 --
574 if (funcmode = 'CANCEL') then
575 --
576 resultout := 'COMPLETE:';
577 return;
578 --
579 end if;
580 --
581 -- TIMEOUT mode
582 --
583 if (funcmode = 'TIMEOUT') then
584 --
585 resultout := 'COMPLETE:';
586 return;
587 --
588 end if;
589 EXCEPTION
590 when others then
591 --resultout := wf_engine.eng_completed ||':'||wf_no;
592 wf_core.context('OKL_CO_WF','notify_customer',itemtype,
593 itemkey,to_char(actid),funcmode);
594 raise;
595 END notify_customer;
596
597 ---------------------------------------------------------------------------
598 -- PROCEDURE wait_before_report
599 ---------------------------------------------------------------------------
600 PROCEDURE wait_before_report(itemtype in varchar2,
601 itemkey in varchar2,
602 actid in number,
603 funcmode in varchar2,
604 resultout out nocopy varchar2) AS
605 l_work_item_temp_id NUMBER;
606 l_result VARCHAR2(1);
607 l_value VARCHAR2(300);
608
609 BEGIN
610 if funcmode <> wf_engine.eng_run then
611 resultout := wf_engine.eng_null;
612 return;
613 end if;
614
615 l_value :=wf_engine.GetActivityLabel(actid);
616 wf_engine.SetItemAttrText(itemtype => itemtype,
617 itemkey => itemkey,
618 aname => 'ACTIVITY_NAME',
619 avalue => l_value);
620
621 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
622 ':'||wf_engine.eng_null;
623 exception
624 when others then
625 --resultout := wf_engine.eng_completed ||':'||wf_no;
626 wf_core.context('OKL_CO_WF','wait_before_report',itemtype,
627 itemkey,to_char(actid),funcmode);
628 raise;
629 END wait_before_report;
630
631 ---------------------------------------------------------------------------
632 -- PROCEDURE report_customer
633 ---------------------------------------------------------------------------
634 PROCEDURE report_customer(itemtype in varchar2,
635 itemkey in varchar2,
636 actid in number,
637 funcmode in varchar2,
638 resultout out nocopy varchar2) AS
639 l_delinquency_id NUMBER := NULL;
640 l_api_version NUMBER := 1.0;
641 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
642 l_return_status VARCHAR2(1);
643 lx_msg_count NUMBER ;
644 lx_msg_data VARCHAR2(2000);
645
646 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
647 FROM iex_delinquencies_all ida
648 ,iex_case_objects ico
649 WHERE ida.delinquency_id = cp_delinquency_id
650 AND ida.case_id = ico.cas_id;
651 BEGIN
652 if (funcmode = 'RUN') then
653 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
654 itemkey => itemkey,
655 aname => 'DELINQUENCY_ID');
656
657 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
658 iex_open_interface_pub.insert_pending(
659 p_api_version => l_api_version,
660 p_init_msg_list => l_init_msg_list,
661 p_object1_id1 => cur.object_id,
662 p_object1_id2 => '#',
663 p_jtot_object1_code => 'OKX_LEASE',
664 p_action => IEX_OPI_PVT.ACTION_REPORT_CB,
665 p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
666 p_comments => OKC_API.G_MISS_CHAR,
667 p_ext_agncy_id => NULL,
668 p_review_date => NULL,
669 p_recall_date => NULL,
670 p_automatic_recall_flag => NULL,
671 p_review_before_recall_flag => NULL,
672 x_return_status => l_return_status,
673 x_msg_count => lx_msg_count,
674 x_msg_data => lx_msg_data);
675
676 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
677 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
678 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
679 RAISE okl_api.G_EXCEPTION_ERROR;
680 END IF;
681 END LOOP;
682 resultout := 'COMPLETE:';
683 RETURN ;
684 end if;
685 --
686 -- CANCEL mode
687 --
688 if (funcmode = 'CANCEL') then
689 --
690 resultout := 'COMPLETE:';
691 return;
692 --
693 end if;
694 --
695 -- TIMEOUT mode
696 --
697 if (funcmode = 'TIMEOUT') then
698 --
699 resultout := 'COMPLETE:';
700 return;
701 --
702 end if;
703 EXCEPTION
704 when others then
705 --resultout := wf_engine.eng_completed ||':'||wf_no;
706 wf_core.context('OKL_CO_WF','report_customer',itemtype,
707 itemkey,to_char(actid),funcmode);
708 raise;
709 END report_customer;
710
711 ---------------------------------------------------------------------------
712 -- PROCEDURE send_vendor_approval
713 ---------------------------------------------------------------------------
714 PROCEDURE send_vendor_approval(itemtype in varchar2,
715 itemkey in varchar2,
716 actid in number,
717 funcmode in varchar2,
718 resultout out nocopy varchar2) AS
719 l_api_version NUMBER := 1.0;
720 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
721 l_return_status VARCHAR2(1);
722 lx_msg_count NUMBER ;
723 lx_msg_data VARCHAR2(2000);
724
725 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
726 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
727 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
728
729 l_case_number IEX_CASES_ALL_B.CASE_NUMBER%TYPE;
730 l_vendor_id PO_VENDORS.VENDOR_ID%TYPE;
731 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
732 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
733 l_subject VARCHAR2(2000);
734 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE;
735 l_from VARCHAR2(2000);
736 l_agent_id NUMBER;
737 l_request_id NUMBER;
738 BEGIN
739 if (funcmode = 'RUN') then
740 --get subject
741 l_subject := fnd_profile.value('OKL_VND_APPROVAL_EMAIL_SUBJECT');
742
743 --get content_id
744 l_content_id := to_number(fnd_profile.value('OKL_VND_APPROVAL_TEMPLATE'));
745
746 --get approval_email_from
747 l_from := fnd_profile.value('OKL_VND_APPROVAL_EMAIL_FROM');
748
749 l_case_number := wf_engine.GetItemAttrText(itemtype => itemtype,
750 itemkey => itemkey,
751 aname => 'CASE_NUMBER');
752
753 l_bind_var(1) := 'p_case_number';
754 l_bind_val(1) := l_case_number;
755 l_bind_var_type(1) := 'VARCHAR2';
756
757 get_vendor_info(p_case_number => l_case_number,
758 x_vendor_id => l_vendor_id,
759 x_vendor_name => l_vendor_name,
760 x_vendor_email => l_email,
761 x_return_status => l_return_status);
762
763 wf_engine.SetItemAttrText (itemtype=> itemtype,
764 itemkey => itemkey,
765 aname => 'VENDOR_NAME',
766 avalue => l_vendor_name);
767
768 l_agent_id := fnd_global.user_id;
769
770 --call fulfillment
771 OKL_FULFILLMENT_PUB.create_fulfillment (
772 p_api_version => l_api_version,
773 p_init_msg_list => okl_api.G_TRUE,
774 p_agent_id => l_agent_id,
775 p_content_id => l_content_id,
776 p_from => l_from,
777 p_subject => l_subject,
778 p_email => l_email,
779 p_bind_var => l_bind_var,
780 p_bind_val => l_bind_val,
781 p_bind_var_type => l_bind_var_type,
782 p_commit => okl_api.G_FALSE,
783 x_request_id => l_request_id,
784 x_return_status => l_return_status,
785 x_msg_count => lx_msg_count,
786 x_msg_data => lx_msg_data);
787
788 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
789 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
790 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
791 RAISE okl_api.G_EXCEPTION_ERROR;
792 END IF;
793
794 resultout := 'COMPLETE:';
795 RETURN ;
796 end if;
797
798 --
799 -- CANCEL mode
800 --
801 if (funcmode = 'CANCEL') then
802 --
803 resultout := 'COMPLETE:';
804 return;
805 --
806 end if;
807 --
808 -- TIMEOUT mode
809 --
810 if (funcmode = 'TIMEOUT') then
811 --
812 resultout := 'COMPLETE:';
813 return;
814 --
815 end if;
816 EXCEPTION
817 when others then
818 --resultout := wf_engine.eng_completed ||':'||wf_no;
819 wf_core.context('OKL_CO_WF','send_vendor_approval',itemtype,
820 itemkey,to_char(actid),funcmode);
821 raise;
822 END send_vendor_approval;
823
824 ---------------------------------------------------------------------------
825 -- PROCEDURE transfer_case
826 ---------------------------------------------------------------------------
827 PROCEDURE transfer_case(itemtype in varchar2,
828 itemkey in varchar2,
829 actid in number,
830 funcmode in varchar2,
831 resultout out nocopy varchar2) AS
832 l_delinquency_id NUMBER := NULL;
833 l_api_version NUMBER := 1.0;
834 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
835 l_return_status VARCHAR2(1);
836 lx_msg_count NUMBER ;
837 lx_msg_data VARCHAR2(2000);
838
839 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT ico.object_id
840 FROM iex_delinquencies_all ida
841 ,iex_case_objects ico
842 WHERE ida.delinquency_id = cp_delinquency_id
843 AND ida.case_id = ico.cas_id;
844 BEGIN
845 if (funcmode = 'RUN') then
846 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
847 itemkey => itemkey,
848 aname => 'DELINQUENCY_ID');
849
850 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
851 iex_open_interface_pub.insert_pending(
852 p_api_version => l_api_version,
853 p_init_msg_list => l_init_msg_list,
854 p_object1_id1 => cur.object_id,
855 p_object1_id2 => '#',
856 p_jtot_object1_code => 'OKX_LEASE',
857 p_action => IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY,
858 p_status => IEX_OPI_PVT.STATUS_PENDING_AUTO,
859 p_comments => OKC_API.G_MISS_CHAR,
860 p_ext_agncy_id => NULL,
861 p_review_date => NULL,
862 p_recall_date => NULL,
863 p_automatic_recall_flag => NULL,
864 p_review_before_recall_flag => NULL,
865 x_return_status => l_return_status,
866 x_msg_count => lx_msg_count,
867 x_msg_data => lx_msg_data);
868
869 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
870 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
871 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
872 RAISE okl_api.G_EXCEPTION_ERROR;
873 END IF;
874 END LOOP;
875 resultout := 'COMPLETE:';
876 RETURN ;
877 end if;
878 --
879 -- CANCEL mode
880 --
881 if (funcmode = 'CANCEL') then
882 --
883 resultout := 'COMPLETE:';
884 return;
885 --
886 end if;
887 --
888 -- TIMEOUT mode
889 --
890 if (funcmode = 'TIMEOUT') then
891 --
892 resultout := 'COMPLETE:';
893 return;
894 --
895 end if;
896 EXCEPTION
897 when others then
898 --resultout := wf_engine.eng_completed ||':'||wf_no;
899 wf_core.context('OKL_CO_WF','transfer_case',itemtype,
900 itemkey,to_char(actid),funcmode);
901 raise;
902 END transfer_case;
903
904 ---------------------------------------------------------------------------
905 -- PROCEDURE review_case
906 ---------------------------------------------------------------------------
907 PROCEDURE review_case(itemtype in varchar2,
908 itemkey in varchar2,
909 actid in number,
910 funcmode in varchar2,
911 resultout out nocopy varchar2) AS
912 l_delinquency_id NUMBER := NULL;
913 l_api_version NUMBER := 1.0;
914 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
915 l_return_status VARCHAR2(1);
916 lx_msg_count NUMBER ;
917 lx_msg_data VARCHAR2(2000);
918
919 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT OIN.ID,
920 OIN.KHR_ID,
921 OIN.CAS_ID,
922 IOH.ID HST_ID,
923 IOH.OBJECT1_ID1,
924 IOH.OBJECT1_ID2,
925 IOH.JTOT_OBJECT1_CODE,
926 IOH.ACTION,
927 IOH.STATUS,
928 IOH.REQUEST_DATE,
929 IOH.PROCESS_DATE,
930 IOH.EXT_AGNCY_ID
931 FROM iex_delinquencies_all ida
932 ,iex_case_objects ico
933 ,Okl_Open_Int OIN
934 ,Iex_Open_Int_Hst IOH
935 WHERE ida.delinquency_id = cp_delinquency_id
936 AND ida.case_id = ico.cas_id
937 AND ico.object_id = oin.khr_id
938 AND OIN.khr_id = TO_NUMBER(IOH.object1_id1)
939 AND IOH.jtot_object1_code = 'OKX_LEASE'
940 AND (IOH.ACTION = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY)
941 AND (IOH.STATUS = IEX_OPI_PVT.STATUS_PROCESSED);
942
943 l_oinv_rec iex_open_interface_pub.oinv_rec_type;
944 lx_oinv_rec iex_open_interface_pub.oinv_rec_type;
945 l_iohv_rec iex_open_interface_pub.iohv_rec_type;
946 lx_iohv_rec iex_open_interface_pub.iohv_rec_type;
947 BEGIN
948 if (funcmode = 'RUN') then
949 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
950 itemkey => itemkey,
951 aname => 'DELINQUENCY_ID');
952
953 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
954 l_oinv_rec.id := cur.id;
955 l_oinv_rec.khr_id := cur.khr_id;
956 l_oinv_rec.cas_id := cur.cas_id;
957 l_iohv_rec.id := cur.hst_id;
958 l_iohv_rec.object1_id1 := cur.object1_id1;
959 l_iohv_rec.object1_id2 := cur.object1_id2;
960 l_iohv_rec.jtot_object1_code := cur.jtot_object1_code;
961 l_iohv_rec.action := cur.action;
962 l_iohv_rec.status := cur.status;
963 l_iohv_rec.request_date := cur.request_date;
964 l_iohv_rec.process_date := cur.process_date;
965 l_iohv_rec.ext_agncy_id := cur.ext_agncy_id;
966
967 iex_open_interface_pub.review_transfer(
968 p_api_version => l_api_version,
969 p_init_msg_list => l_init_msg_list,
970 p_oinv_rec => l_oinv_rec,
971 p_iohv_rec => l_iohv_rec,
972 x_oinv_rec => lx_oinv_rec,
973 x_iohv_rec => lx_iohv_rec,
974 x_return_status => l_return_status,
975 x_msg_count => lx_msg_count,
976 x_msg_data => lx_msg_data);
977
978 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
979 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
980 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
981 RAISE okl_api.G_EXCEPTION_ERROR;
982 END IF;
983 END LOOP;
984 resultout := 'COMPLETE:';
985 RETURN ;
986 end if;
987 --
988 -- CANCEL mode
989 --
990 if (funcmode = 'CANCEL') then
991 --
992 resultout := 'COMPLETE:';
993 return;
994 --
995 end if;
996 --
997 -- TIMEOUT mode
998 --
999 if (funcmode = 'TIMEOUT') then
1000 --
1001 resultout := 'COMPLETE:';
1002 return;
1003 --
1004 end if;
1005 EXCEPTION
1006 when others then
1007 --resultout := wf_engine.eng_completed ||':'||wf_no;
1008 wf_core.context('OKL_CO_WF','review_case',itemtype,
1009 itemkey,to_char(actid),funcmode);
1010 raise;
1011 END review_case;
1012
1013 ---------------------------------------------------------------------------
1014 -- PROCEDURE recall_case
1015 ---------------------------------------------------------------------------
1016 PROCEDURE recall_case(itemtype in varchar2,
1017 itemkey in varchar2,
1018 actid in number,
1019 funcmode in varchar2,
1020 resultout out nocopy varchar2) AS
1021 l_delinquency_id NUMBER := NULL;
1022 l_api_version NUMBER := 1.0;
1023 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
1024 l_return_status VARCHAR2(1);
1025 lx_msg_count NUMBER ;
1026 lx_msg_data VARCHAR2(2000);
1027
1028 CURSOR l_khr_csr(cp_delinquency_id IN NUMBER) IS SELECT OIN.ID,
1029 OIN.KHR_ID,
1030 IOH.ID hst_id,
1031 IOH.OBJECT1_ID1,
1032 IOH.OBJECT1_ID2,
1033 IOH.JTOT_OBJECT1_CODE,
1034 IOH.EXT_AGNCY_ID
1035 FROM iex_delinquencies_all ida
1036 ,iex_case_objects ico
1037 ,Okl_Open_Int OIN
1038 ,Iex_Open_Int_Hst IOH
1039 WHERE ida.delinquency_id = cp_delinquency_id
1040 AND ida.case_id = ico.cas_id
1041 AND ico.object_id = oin.khr_id
1042 AND OIN.khr_id = TO_NUMBER(IOH.object1_id1)
1043 AND IOH.jtot_object1_code = 'OKX_LEASE'
1044 AND (IOH.ACTION = IEX_OPI_PVT.ACTION_TRANSFER_EXT_AGNCY)
1045 AND (IOH.STATUS = IEX_OPI_PVT.STATUS_NOTIFIED OR IOH.STATUS = IEX_OPI_PVT.STATUS_PROCESSED);
1046
1047 l_oinv_rec iex_open_interface_pub.oinv_rec_type;
1048 lx_oinv_rec iex_open_interface_pub.oinv_rec_type;
1049 l_iohv_rec iex_open_interface_pub.iohv_rec_type;
1050 lx_iohv_rec iex_open_interface_pub.iohv_rec_type;
1051 BEGIN
1052 if (funcmode = 'RUN') then
1053 l_delinquency_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1054 itemkey => itemkey,
1055 aname => 'DELINQUENCY_ID');
1056
1057 FOR cur IN l_khr_csr(l_delinquency_id) LOOP
1058 l_oinv_rec.id := cur.id;
1059 l_oinv_rec.khr_id := cur.khr_id;
1060 l_iohv_rec.id := cur.hst_id;
1061 l_iohv_rec.object1_id1 := cur.object1_id1;
1062 l_iohv_rec.object1_id2 := cur.object1_id2;
1063 l_iohv_rec.jtot_object1_code := cur.jtot_object1_code;
1064 l_iohv_rec.ext_agncy_id := cur.ext_agncy_id;
1065
1066 iex_open_interface_pub.recall_transfer(p_api_version => l_api_version
1067 ,p_init_msg_list => l_init_msg_list
1068 ,p_interface_id => l_oinv_rec.id
1069 ,p_recall_date => SYSDATE
1070 ,p_comments => null
1071 ,x_return_status => l_return_status
1072 ,x_msg_count => lx_msg_count
1073 ,x_msg_data => lx_msg_data);
1074
1075 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1076 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1077 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1078 RAISE okl_api.G_EXCEPTION_ERROR;
1079 END IF;
1080 END LOOP;
1081 resultout := 'COMPLETE:';
1082 RETURN ;
1083 end if;
1084 --
1085 -- CANCEL mode
1086 --
1087 if (funcmode = 'CANCEL') then
1088 --
1089 resultout := 'COMPLETE:';
1090 return;
1091 --
1092 end if;
1093 --
1094 -- TIMEOUT mode
1095 --
1096 if (funcmode = 'TIMEOUT') then
1097 --
1098 resultout := 'COMPLETE:';
1099 return;
1100 --
1101 end if;
1102 EXCEPTION
1103 when others then
1104 --resultout := wf_engine.eng_completed ||':'||wf_no;
1105 wf_core.context('OKL_CO_WF','recall_case',itemtype,
1106 itemkey,to_char(actid),funcmode);
1107 raise;
1108 END recall_case;
1109
1110
1111 ---------------------------------------------------------------------------
1112 -- PROCEDURE send_vendor_notify
1113 ---------------------------------------------------------------------------
1114 PROCEDURE send_vendor_notify(itemtype in varchar2,
1115 itemkey in varchar2,
1116 actid in number,
1117 funcmode in varchar2,
1118 resultout out nocopy varchar2) AS
1119 l_api_version NUMBER := 1.0;
1120 l_init_msg_list VARCHAR2(1) := fnd_api.g_false;
1121 l_return_status VARCHAR2(1);
1122 lx_msg_count NUMBER ;
1123 lx_msg_data VARCHAR2(2000);
1124
1125 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1126 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1127 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1128
1129 l_case_number IEX_CASES_ALL_B.CASE_NUMBER%TYPE;
1130 l_contract_id OKC_K_HEADERS_B.ID%TYPE;
1131 l_vendor_id PO_VENDORS.VENDOR_ID%TYPE;
1132 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
1133 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
1134 l_subject VARCHAR2(2000);
1135 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE;
1136 l_from VARCHAR2(2000);
1137 l_agent_id NUMBER;
1138 l_request_id NUMBER;
1139
1140 BEGIN
1141 if (funcmode = 'RUN') then
1142 --get subject
1143 l_subject := fnd_profile.value('OKL_VND_NOTIFY_EMAIL_SUBJECT');
1144
1145 --get content_id
1146 l_content_id := to_number(fnd_profile.value('OKL_VND_NOTIFY_TEMPLATE'));
1147
1148 --get subject
1149 l_from := fnd_profile.value('OKL_VND_NOTIFY_EMAIL_FROM');
1150
1151 l_case_number := wf_engine.GetItemAttrText(itemtype => itemtype,
1152 itemkey => itemkey,
1153 aname => 'CASE_NUMBER');
1154
1155 l_bind_var(1) := 'p_case_number';
1156 l_bind_val(1) := l_case_number;
1157 l_bind_var_type(1) := 'VARCHAR2';
1158
1159 get_vendor_info(p_case_number => l_case_number,
1160 x_vendor_id => l_vendor_id,
1161 x_vendor_name => l_vendor_name,
1162 x_vendor_email => l_email,
1163 x_return_status => l_return_status);
1164
1165 l_agent_id := fnd_global.user_id;
1166
1167 --call fulfillment
1168 OKL_FULFILLMENT_PUB.create_fulfillment (
1169 p_api_version => l_api_version,
1170 p_init_msg_list => okl_api.G_TRUE,
1171 p_agent_id => l_agent_id,
1172 p_content_id => l_content_id,
1173 p_from => l_from,
1174 p_subject => l_subject,
1175 p_email => l_email,
1176 p_bind_var => l_bind_var,
1177 p_bind_val => l_bind_val,
1178 p_bind_var_type => l_bind_var_type,
1179 p_commit => okl_api.G_FALSE,
1180 x_request_id => l_request_id,
1181 x_return_status => l_return_status,
1182 x_msg_count => lx_msg_count,
1183 x_msg_data => lx_msg_data);
1184
1185 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1186 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1187 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1188 RAISE okl_api.G_EXCEPTION_ERROR;
1189 END IF;
1190
1191 resultout := 'COMPLETE:';
1192 RETURN ;
1193 end if;
1194
1195 --
1196 -- CANCEL mode
1197 --
1198 if (funcmode = 'CANCEL') then
1199 --
1200 resultout := 'COMPLETE:';
1201 return;
1202 --
1203 end if;
1204 --
1205 -- TIMEOUT mode
1206 --
1207 if (funcmode = 'TIMEOUT') then
1208 --
1209 resultout := 'COMPLETE:';
1210 return;
1211 --
1212 end if;
1213 EXCEPTION
1214 when others then
1215 --resultout := wf_engine.eng_completed ||':'||wf_no;
1216 wf_core.context('OKL_CO_WF','send_vendor_notify',itemtype,
1217 itemkey,to_char(actid),funcmode);
1218 raise;
1219 END send_vendor_notify;
1220
1221 ---------------------------------------------------------------------------
1222 -- PROCEDURE wf_send_signal_cancelled
1223 ---------------------------------------------------------------------------
1224 /** send signal to the main work flow that the custom work
1225 * flow is over and also updates the work item
1226 * the send signal is sent when the agent REJECTS the
1227 * notification since the vendor didn't approve it ,
1228 * so set the status to 'CANCELLED'.
1229 **/
1230
1231 PROCEDURE wf_send_signal_cancelled(
1232 itemtype in varchar2,
1233 itemkey in varchar2,
1234 actid in number,
1235 funcmode in varchar2,
1236 result out nocopy varchar2) AS
1237
1238 l_work_item_id number;
1239 l_strategy_id number;
1240 l_return_status VARCHAR2(20);
1241 l_msg_count NUMBER;
1242 l_msg_data VARCHAR2(2000);
1243 l_msg_index_out number;
1244 i number;
1245 l_error VARCHAR2(32767);
1246 Begin
1247 if funcmode <> 'RUN' then
1248 result := wf_engine.eng_null;
1249 return;
1250 end if;
1251
1252 l_work_item_id := wf_engine.GetItemAttrNumber(
1253 itemtype => itemtype,
1254 itemkey => itemkey,
1255 aname => 'WORK_ITEMID');
1256
1257 l_strategy_id := wf_engine.GetItemAttrNumber(
1258 itemtype => itemtype,
1259 itemkey => itemkey,
1260 aname => 'STRATEGY_ID');
1261
1262 if (l_work_item_id is not null) then
1263
1264 iex_stry_utl_pub.update_work_item(
1265 p_api_version => 1.0,
1266 p_commit => FND_API.G_TRUE,
1267 p_init_msg_list => FND_API.G_TRUE,
1268 p_work_item_id => l_work_item_id,
1269 p_status => 'CANCELLED',
1270 x_return_status => l_return_status,
1271 x_msg_count => l_msg_count,
1272 x_msg_data => l_msg_data
1273 );
1274
1275 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1276 iex_strategy_wf.send_signal(
1277 process => 'IEXSTRY' ,
1278 strategy_id => l_strategy_id,
1279 status => 'CANCELLED',
1280 work_item_id => l_work_item_id,
1281 signal_source =>'CUSTOM');
1282
1283
1284 end if; -- if update is succcessful;
1285 end if;
1286
1287 result := wf_engine.eng_completed;
1288
1289 EXCEPTION
1290 when others then
1291 FOR i in 1..fnd_msg_pub.count_msg() LOOP
1292 fnd_msg_pub.get(p_data => l_msg_data,
1293 p_msg_index_out => l_msg_index_out);
1294 --dbms_output.put_line(to_char(i) || ':' || x_msg_data);
1295 END LOOP;
1296
1297 --iex_strategy_wf.Get_Messages(l_msg_count,l_error);
1298 wf_core.context('OKL_CO_WF','wf_send_signal_cancelled',itemtype,
1299 itemkey,to_char(actid),funcmode);
1300 raise;
1301 END wf_send_signal_cancelled;
1302
1303 ---------------------------------------------------------------------------
1304 -- PROCEDURE wf_send_signal_complete
1305 ---------------------------------------------------------------------------
1306 /** send signal to the main work flow that the custom work
1307 * flow is over and also updates the work item
1308 * the send signal is sent when the agent REJECTS the
1309 * notification since the vendor didn't approve it ,
1310 * so set the status to 'COMPLETE'.
1311 **/
1312
1313 PROCEDURE wf_send_signal_complete(
1314 itemtype in varchar2,
1315 itemkey in varchar2,
1316 actid in number,
1317 funcmode in varchar2,
1318 result out nocopy varchar2) AS
1319
1320 l_work_item_id number;
1321 l_strategy_id number;
1322 l_return_status VARCHAR2(20);
1323 l_msg_count NUMBER;
1324 l_msg_data VARCHAR2(2000);
1325 l_msg_index_out number;
1326 i number;
1327 l_error VARCHAR2(32767);
1328 Begin
1329 if funcmode <> 'RUN' then
1330 result := wf_engine.eng_null;
1331 return;
1332 end if;
1333
1334 l_work_item_id := wf_engine.GetItemAttrNumber(
1335 itemtype => itemtype,
1336 itemkey => itemkey,
1337 aname => 'WORK_ITEMID');
1338
1339 l_strategy_id := wf_engine.GetItemAttrNumber(
1340 itemtype => itemtype,
1341 itemkey => itemkey,
1342 aname => 'STRATEGY_ID');
1343
1344 if (l_work_item_id is not null) then
1345
1346 iex_stry_utl_pub.update_work_item(
1347 p_api_version => 1.0,
1348 p_commit => FND_API.G_TRUE,
1349 p_init_msg_list => FND_API.G_TRUE,
1350 p_work_item_id => l_work_item_id,
1351 p_status => 'COMPLETE',
1352 x_return_status => l_return_status,
1353 x_msg_count => l_msg_count,
1354 x_msg_data => l_msg_data
1355 );
1356
1357 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1358 iex_strategy_wf.send_signal(
1359 process => 'IEXSTRY' ,
1360 strategy_id => l_strategy_id,
1361 status => 'COMPLETE',
1362 work_item_id => l_work_item_id,
1363 signal_source =>'CUSTOM');
1364
1365
1366 end if; -- if update is succcessful;
1367 end if;
1368
1369 result := wf_engine.eng_completed;
1370
1371 EXCEPTION
1372 when others then
1373 FOR i in 1..fnd_msg_pub.count_msg() LOOP
1374 fnd_msg_pub.get(p_data => l_msg_data,
1375 p_msg_index_out => l_msg_index_out);
1376 --dbms_output.put_line(to_char(i) || ':' || x_msg_data);
1377 END LOOP;
1378
1379 --iex_strategy_wf.Get_Messages(l_msg_count,l_error);
1380 wf_core.context('OKL_CO_WF','wf_send_signal_complete',itemtype,
1381 itemkey,to_char(actid),funcmode);
1382 raise;
1383 END wf_send_signal_complete;
1384
1385 ---------------------------------------------------------------------------
1386 -- FUNCTION get_party_name
1387 ---------------------------------------------------------------------------
1388 FUNCTION get_party_name(p_case_number in varchar2) RETURN VARCHAR2 AS
1389 l_party_id NUMBER;
1390 l_party_name VARCHAR2(360);
1391 l_return_status VARCHAR2(1) := 'S';
1392
1393 CURSOR l_party_csr(cp_case_number in varchar2) IS SELECT hp.party_name
1394 FROM IEX_CASES_ALL_B ica
1395 ,HZ_PARTIES hp
1396 WHERE ica.case_number = cp_case_number
1397 AND ica.party_id = hp.party_id;
1398 BEGIN
1399 FOR cur IN l_party_csr(p_case_number) LOOP
1400 l_party_name := cur.party_name;
1401 END LOOP;
1402
1403 RETURN l_party_name;
1404 END get_party_name;
1405
1406 ---------------------------------------------------------------------------
1407 -- FUNCTION get_case_contracts
1408 ---------------------------------------------------------------------------
1409 FUNCTION get_case_contracts(p_case_number in varchar2) RETURN VARCHAR2 AS
1410 CURSOR l_contract_csr(cp_case_number in varchar2) IS SELECT okh.contract_number
1411 FROM IEX_CASES_ALL_B ica
1412 ,IEX_CASE_OBJECTS ico
1413 ,OKC_K_HEADERS_V okh
1414 WHERE ica.case_number = cp_case_number
1415 AND ica.cas_id = ico.cas_id
1416 AND ico.object_id = okh.id;
1417
1418 l_contracts varchar2(2000);
1419 l_comma varchar2(10) := NULL;
1420 BEGIN
1421 FOR cur IN l_contract_csr(p_case_number) LOOP
1422 l_contracts := l_contracts || l_comma || cur.contract_number;
1423 l_comma := ', ';
1424 END LOOP;
1425 RETURN l_contracts;
1426 END get_case_contracts;
1427
1428 ---------------------------------------------------------------------------
1429 -- FUNCTION get_case_total_value
1430 ---------------------------------------------------------------------------
1431 FUNCTION get_case_total_value(p_case_number in varchar2) RETURN NUMBER AS
1432 CURSOR l_contract_csr(cp_case_number in varchar2) IS SELECT okh.id
1433 FROM IEX_CASES_ALL_B ica
1434 ,IEX_CASE_OBJECTS ico
1435 ,OKC_K_HEADERS_V okh
1436 WHERE ica.case_number = cp_case_number
1437 AND ica.cas_id = ico.cas_id
1438 AND ico.object_id = okh.id;
1439
1440 l_case_total number := 0;
1441 BEGIN
1442 FOR cur IN l_contract_csr(p_case_number) LOOP
1443 l_case_total := l_case_total + NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_oec(p_chr_id => cur.id, p_line_id => NULL),0)
1444 - NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_tradein(p_chr_id => cur.id, p_line_id => NULL),0)
1445 - NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capital_reduction(p_chr_id => cur.id, p_line_id => NULL),0)
1446 + NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_fees_capitalized(p_chr_id => cur.id, p_line_id => NULL),0);
1447 END LOOP;
1448 RETURN l_case_total;
1449 END get_case_total_value;
1450
1451 ---------------------------------------------------------------------------
1452 -- FUNCTION get_amt_overdue
1453 ---------------------------------------------------------------------------
1454 FUNCTION get_amt_overdue(p_case_number in varchar2) RETURN NUMBER AS
1455 CURSOR l_khr_past_due_csr(cp_case_number in varchar2) IS
1456 SELECT sum(nvl(aps.amount_due_remaining, 0)) past_due_amount
1457 FROM iex_cases_all_b ica
1458 ,iex_case_objects ico
1459 ,okl_cnsld_ar_strms_b ocas
1460 ,ar_payment_schedules_all aps
1461 WHERE ica.case_number = cp_case_number
1462 AND ica.cas_id = ico.cas_id
1463 AND ico.object_id = ocas.khr_id
1464 AND ocas.receivables_invoice_id = aps.customer_trx_id
1465 AND aps.class = 'INV'
1466 AND aps.due_date < sysdate
1467 AND nvl(aps.amount_due_remaining, 0) > 0;
1468
1469 l_amt_overdue number := 0;
1470 BEGIN
1471 FOR cur IN l_khr_past_due_csr(p_case_number) LOOP
1472 l_amt_overdue := l_amt_overdue + cur.past_due_amount;
1473 END LOOP;
1474 RETURN l_amt_overdue;
1475 END get_amt_overdue;
1476
1477 ---------------------------------------------------------------------------
1478 -- FUNCTION get_vendor_name
1479 ---------------------------------------------------------------------------
1480 FUNCTION get_vendor_name(p_case_number in varchar2) RETURN VARCHAR2 AS
1481 --how to obtain the vendor site
1482 CURSOR l_vendor_csr(cp_case_number in varchar2) IS SELECT pv.vendor_name
1483 FROM iex_cases_all_b ica
1484 ,iex_case_objects ico
1485 ,okc_k_party_roles_v opr
1486 ,po_vendors pv
1487 ,po_vendor_sites_all pvs
1488 WHERE ica.case_number = cp_case_number
1489 AND ica.cas_id = ico.cas_id
1490 AND ico.object_id =opr.dnz_chr_id
1491 AND opr.rle_code = 'OKL_VENDOR'
1492 AND opr.object1_id1 = pv.vendor_id
1493 AND pv.vendor_id = pvs.vendor_id;
1494
1495 l_vendor_name PO_VENDORS.VENDOR_NAME%TYPE;
1496 BEGIN
1497 FOR cur IN l_vendor_csr(p_case_number) LOOP
1498 l_vendor_name := cur.vendor_name;
1499 exit;
1500 END LOOP;
1501 RETURN l_vendor_name;
1502 END get_vendor_name;
1503
1504 /*
1505 ---------------------------------------------------------------------------
1506 -- PROCEDURE raise_report_cb_event
1507 ---------------------------------------------------------------------------
1508 PROCEDURE raise_report_cb_event(p_delinquency_id IN NUMBER) AS
1509 l_parameter_list wf_parameter_list_t;
1510 l_key varchar2(240);
1511 l_seq NUMBER;
1512 l_event_name varchar2(240) := 'oracle.apps.okl.co.reportcb';
1513 l_itemtype varchar2(240) := 'OKLCORCB';
1514 --l_event_name varchar2(240) := 'oracle.apps.okl.co.transferea';
1515 --l_itemtype varchar2(240) := 'OKLCOTEA';
1516
1517 CURSOR okl_key_csr IS
1518 SELECT okl_wf_item_s.nextval
1519 FROM dual;
1520 BEGIN
1521 SAVEPOINT raise_report_cb_event;
1522
1523 OPEN okl_key_csr;
1524 FETCH okl_key_csr INTO l_seq;
1525 CLOSE okl_key_csr;
1526 l_key := l_event_name ||l_seq ;
1527
1528
1529 --Code for starting a workflow starts here
1530 wf_engine.createprocess(itemtype => l_itemtype,
1531 itemkey => l_key,
1532 process =>'IEX:STRATEGY_CUSTOM_WORKFLOW');
1533
1534
1535 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1536 itemkey => l_key,
1537 aname => 'DELINQUENCY_ID',
1538 avalue => p_delinquency_id);
1539
1540 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1541 itemkey => l_key,
1542 aname => 'PARTY_NAME',
1543 avalue => 'Pradeep Gomes');
1544
1545 wf_engine.SetItemAttrText(itemtype => l_itemtype,
1546 itemkey => l_key,
1547 aname => 'NOTIFICATION_USERNAME',
1548 avalue => 'ADMIN');
1549
1550 wf_engine.SetItemAttrNumber(itemtype => l_itemtype,
1551 itemkey => l_key,
1552 aname => 'WORK_ITEMID',
1553 avalue => 123456789012345678901234567890);
1554
1555 wf_engine.startprocess(itemtype => l_itemtype,
1556 itemkey => l_key);
1557 --Code for ending a workflow starts here
1558
1559 --Code for raising an event starts here
1560 --wf_event.AddParameterToList('DELINQUENCY_ID',p_delinquency_id,l_parameter_list);
1561 --wf_event.AddParameterToList('PARTY_NAME','PRADEEP GOMES',l_parameter_list);
1562 --wf_event.AddParameterToList('CASE_NUMBER','PGOMES1002',l_parameter_list);
1563 --wf_event.AddParameterToList('NOTIFICATION_USERNAME','ADMIN',l_parameter_list);
1564
1565 -- Raise Event
1566 --wf_event.raise(p_event_name => l_event_name
1567 -- ,p_event_key => l_key
1568 -- ,p_parameters => l_parameter_list);
1569 -- l_parameter_list.DELETE;
1570 --Code for raising an event ends here
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
1575 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1576 FND_MSG_PUB.ADD;
1577 ROLLBACK TO raise_report_cb_event;
1578 END raise_report_cb_event;
1579 */
1580
1581 END OKL_CO_WF;