[Home] [Help]
PACKAGE BODY: APPS.OKL_CS_WF
Source
1 PACKAGE BODY OKL_CS_WF AS
2 /* $Header: OKLRCSWB.pls 120.16.12010000.3 2008/10/03 17:58:36 rkuttiya ship $ */
3
4 l_ntf_result VARCHAR2(30);
5
6 -----get messages from the server side-----------------
7 PROCEDURE Get_Messages (
8 p_message_count IN NUMBER,
9 x_msgs OUT NOCOPY VARCHAR2)
10 IS
11 l_msg_list VARCHAR2(5000) := '';
12 l_temp_msg VARCHAR2(2000);
13 l_appl_short_name VARCHAR2(50) ;
14 l_message_name VARCHAR2(30) ;
15 l_id NUMBER;
16 l_message_num NUMBER;
17 l_msg_count NUMBER;
18 l_msg_data VARCHAR2(2000);
19
20 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
21 SELECT application_id
22 FROM fnd_application_vl
23 WHERE application_short_name = x_short_name;
24
25 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
26 SELECT msg.message_number
27 FROM fnd_new_messages msg, fnd_languages_vl lng
28 WHERE msg.message_name = x_msg
29 and msg.application_id = x_id
30 and lng.LANGUAGE_CODE = msg.language_code
31 and lng.language_id = x_lang_id;
32 BEGIN
33 FOR l_count in 1..p_message_count LOOP
34
35 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
36 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
37 OPEN Get_Appl_Id (l_appl_short_name);
38 FETCH Get_Appl_Id into l_id;
39 CLOSE Get_Appl_Id;
40 l_message_num := NULL;
41
42 IF l_id is not NULL
43 THEN
44 OPEN Get_Message_Num (l_message_name, l_id,
45 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
46 FETCH Get_Message_Num into l_message_num;
47 CLOSE Get_Message_Num;
48 END IF;
49
50 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
51
52 IF NVL(l_message_num, 0) <> 0
53 THEN
54 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
55 ELSE
56 l_temp_msg := NULL;
57 END IF;
58
59 IF l_count = 1
60 THEN
61 l_msg_list := l_msg_list || l_temp_msg ||
62 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
63 ELSE
64 l_msg_list := l_msg_list || l_temp_msg ||
65 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
66 END IF;
67
68 l_msg_list := l_msg_list || '';
69
70 END LOOP;
71
72 x_msgs := l_msg_list;
73 END Get_Messages;
74
75
76
77 PROCEDURE raise_equipment_exchange_event (
78 p_tas_id IN NUMBER)
79 AS
80 l_parameter_list wf_parameter_list_t;
81 l_key varchar2(240);
82 l_event_name varchar2(240) := 'oracle.apps.okl.cs.equipmentexchange';
83 l_seq NUMBER;
84 CURSOR okl_key_csr IS
85 SELECT okl_wf_item_s.nextval
86 FROM dual;
87
88
89 BEGIN
90
91 SAVEPOINT raise_equipment_exchange_event;
92
93 OPEN okl_key_csr;
94 FETCH okl_key_csr INTO l_seq;
95 CLOSE okl_key_csr;
96 l_key := l_event_name ||l_seq ;
97
98 wf_event.AddParameterToList('TAS_ID',p_tas_id,l_parameter_list);
99 --added by akrangan
100 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
101
102 -- Raise Event
103 wf_event.raise(p_event_name => l_event_name
104 ,p_event_key => l_key
105 ,p_parameters => l_parameter_list);
106 l_parameter_list.DELETE;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
111 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
112 FND_MSG_PUB.ADD;
113 ROLLBACK TO raise_equipment_exchange_event;
114 END raise_equipment_exchange_event;
115
116
117 PROCEDURE exchange_equipment ( itemtype in varchar2,
118 itemkey in varchar2,
119 actid in number,
120 funcmode in varchar2,
121 resultout out nocopy varchar2 )
122 IS
123
124 l_dummy varchar(1) ;
125 l_tas_id NUMBER ;
126 l_return_status VARCHAR2(100);
127 l_api_version NUMBER := 1.0;
128 l_msg_count NUMBER;
129 l_msg_data VARCHAR2(2000);
130 BEGIN
131
132 if (funcmode = 'RUN') then
133 l_tas_id := wf_engine.GetItemAttrText( itemtype => itemtype,
134 itemkey => itemkey,
135 aname => 'TAS_ID');
136
137 okl_equipment_exchange_pub.exchange(
138 p_api_version =>l_api_version,
139 p_init_msg_list =>fnd_api.g_false,
140 p_tas_id =>l_tas_id,
141 x_return_status =>l_return_status,
142 x_msg_count =>l_msg_count,
143 x_msg_data =>l_msg_data);
144 --I think if the api is not a success we should log the error in a
145 --table.
146
147 IF l_return_status <> 'S' THEN
148 resultout := 'COMPLETE:N';
149 ELSE
150 resultout := 'COMPLETE:Y';
151 END IF;
152 RETURN ;
153
154 end if;
155 --
156 -- CANCEL mode
157 --
158 if (funcmode = 'CANCEL') then
159 --
160 resultout := 'COMPLETE:N';
161
162 --
163 end if;
164 --
165 -- TIMEOUT mode
166 --
167 if (funcmode = 'TIMEOUT') then
168 --
169 resultout := 'COMPLETE:Y';
170 return ;
171 --
172 end if;
173 EXCEPTION
174 when others then
175 wf_core.context('OKL_CS_WF',
176 'exchange_equipment',
177 itemtype,
178 itemkey,
179 to_char(actid),
180 funcmode);
181 RAISE;
182
183 END exchange_equipment;
184
185 PROCEDURE check_for_request ( itemtype in varchar2,
186 itemkey in varchar2,
187 actid in number,
188 funcmode in varchar2,
189 resultout out nocopy varchar2)
190 IS
191 l_tas_id NUMBER;
192 l_knt NUMBER;
193
194 CURSOR okl_check_req_csr(c_tas_id NUMBER)
195 IS
196 SELECT count(*)
197 FROM OKL_TRX_ASSETS
198 WHERE ID=c_tas_id;
199
200 BEGIN
201 if (funcmode = 'RUN') then
202 l_tas_id := wf_engine.GetItemAttrText( itemtype => itemtype,
203 itemkey => itemkey,
204 aname => 'TAS_ID');
205
206
207
208 OPEN okl_check_req_csr(l_tas_id);
209 FETCH okl_check_req_csr into l_knt;
210 CLOSE okl_check_req_csr;
211
212 IF l_knt = 0 THEN
213 resultout := 'COMPLETE:N';
214 ELSE
215 resultout := 'COMPLETE:Y';
216 END IF;
217 RETURN ;
218
219 end if;
220 --
221 -- CANCEL mode
222 --
223 if (funcmode = 'CANCEL') then
224 --
225 resultout := 'COMPLETE:';
226 return;
227 --
228 end if;
229 --
230 -- TIMEOUT mode
231 --
232 if (funcmode = 'TIMEOUT') then
233 --
234 resultout := 'COMPLETE:';
235 return;
236 --
237 end if;
238
239 END check_for_request;
240
241 PROCEDURE populate_notification_attribs(itemtype in varchar2,
242 itemkey in varchar2,
243 p_tas_id IN NUMBER)
244 AS
245 l_restruct_role VARCHAR2(100);
246 l_contract_number VARCHAR2(120);
247 l_request_number NUMBER;
248 x_thpv_tbl okl_equipment_exchange_pub.thpv_tbl_type;
249 l_thpv_tbl okl_equipment_exchange_pub.thpv_tbl_type;
250
251 l_new_talv_tbl okl_equipment_exchange_pub.talv_tbl_type;
252 x_new_talv_tbl okl_equipment_exchange_pub.talv_tbl_type;
253
254 l_old_talv_tbl okl_equipment_exchange_pub.talv_tbl_type;
255 x_old_talv_tbl okl_equipment_exchange_pub.talv_tbl_type;
256 l_no_data_found BOOLEAN;
257
258 l_old_vendor VARCHAR2(240);
259 l_new_vendor VARCHAR2(240);
260
261 CURSOR okl_contract_number(c_contract_id NUMBER)
262 IS
263 SELECT contract_number
264 FROM OKC_K_HEADERS_V
265 WHERE id=c_contract_id;
266 BEGIN
267
268 l_thpv_tbl(1).id:= p_tas_id;
269 x_thpv_tbl :=okl_equipment_exchange_pub.get_Tas_hdr_rec(l_thpv_tbl,l_no_data_found);
270
271 l_old_talv_tbl(1).tas_id := p_tas_id;
272 l_old_talv_tbl(1).tal_type := 'OAS';
273 x_old_talv_tbl :=okl_equipment_exchange_pub.get_tal_rec(l_old_talv_tbl,l_no_data_found);
274 l_new_talv_tbl(1).tas_id := p_tas_id;
275 l_new_talv_tbl(1).tal_type := 'NAS';
276 x_new_talv_tbl :=okl_equipment_exchange_pub.get_tal_rec(l_new_talv_tbl,l_no_data_found);
277
278 OPEN okl_contract_number(x_new_talv_tbl(1).dnz_khr_id);
279 FETCH okl_contract_number INTO l_contract_number;
280 CLOSE okl_contract_number;
281
282 --This should be populated from the DB.
283 --rkuttiya added for bug:2923037
284 l_restruct_role := fnd_profile.value('OKL_CTR_RESTRUCTURE_REP');
285 IF l_restruct_role IS NULL THEN
286 l_restruct_role := 'SYSADMIN';
287 END IF;
288 l_request_number := x_thpv_tbl(1).trans_number;
289
290 wf_engine.SetItemAttrText ( itemtype=> itemtype,
291 itemkey => itemkey,
292 aname => 'OKLCSEQUIP_RESTRUCTURE_ROLE',
293 avalue => l_restruct_role);
294 --Header Information
295 wf_engine.SetItemAttrText ( itemtype=> itemtype,
296 itemkey => itemkey,
297 aname => 'CONTRACT_NUMBER',
298 avalue => l_contract_number);
299 wf_engine.SetItemAttrText ( itemtype=> itemtype,
300 itemkey => itemkey,
301 aname => 'REQUEST_NUMBER',
302 avalue => l_request_number);
303 wf_engine.SetItemAttrText ( itemtype=> itemtype,
304 itemkey => itemkey,
305 aname => 'COMMENTS',
306 avalue => x_thpv_tbl(1).comments);
307 wf_engine.SetItemAttrText ( itemtype=> itemtype,
308 itemkey => itemkey,
309 aname => 'RETURN_DATE',
310 avalue => x_old_talv_tbl(1).date_due);
311
312
313 --Old Asset Information
314 wf_engine.SetItemAttrText ( itemtype=> itemtype,
315 itemkey => itemkey,
316 aname => 'OLD_ASSET_NUMBER',
317 avalue => x_old_talv_tbl(1).asset_number);
318 wf_engine.SetItemAttrText ( itemtype=> itemtype,
319 itemkey => itemkey,
320 aname => 'OLD_ASSET_DESC',
321 avalue => x_old_talv_tbl(1).DESCRIPTION);
322 wf_engine.SetItemAttrText ( itemtype=> itemtype,
323 itemkey => itemkey,
324 aname => 'OLD_COST',
325 avalue => x_old_talv_tbl(1).ORIGINAL_COST);
326 wf_engine.SetItemAttrText ( itemtype=> itemtype,
327 itemkey => itemkey,
328 aname => 'OLD_YEAR',
329 avalue => x_old_talv_tbl(1).YEAR_MANUFACTURED);
330 wf_engine.SetItemAttrText ( itemtype=> itemtype,
331 itemkey => itemkey,
332 aname => 'OLD_MODEL',
333 avalue => x_old_talv_tbl(1).MODEL_NUMBER);
334 wf_engine.SetItemAttrText ( itemtype=> itemtype,
335 itemkey => itemkey,
336 aname => 'OLD_MANUFACTURER',
337 avalue => x_old_talv_tbl(1).MANUFACTURER_NAME);
338 IF x_old_talv_tbl(1).SUPPLIER_ID IS NOT NULL THEN
339 l_old_vendor := okl_equipment_exchange_pub.get_vendor_name(x_old_talv_tbl(1).SUPPLIER_ID);
340 wf_engine.SetItemAttrText ( itemtype=> itemtype,
341 itemkey => itemkey,
342 aname => 'OLD_VENDOR',
343 avalue => l_old_vendor);
344 END IF;
345
346 --New Asset Information
347 /* wf_engine.SetItemAttrText ( itemtype=> itemtype,
348 itemkey => itemkey,
349 aname => 'OLD_ASSET_NUMBER',
350 avalue => x_new_talv_tbl(1).asset_number);
351 */
352 wf_engine.SetItemAttrText ( itemtype=> itemtype,
353 itemkey => itemkey,
354 aname => 'OLD_ASSET_DESC',
355 avalue => x_new_talv_tbl(1).DESCRIPTION);
356
357 wf_engine.SetItemAttrText ( itemtype=> itemtype,
358 itemkey => itemkey,
359 aname => 'NEW_COST',
360 avalue => x_new_talv_tbl(1).ORIGINAL_COST);
361 wf_engine.SetItemAttrText ( itemtype=> itemtype,
362 itemkey => itemkey,
363 aname => 'NEW_YEAR',
364 avalue => x_new_talv_tbl(1).YEAR_MANUFACTURED);
365 wf_engine.SetItemAttrText ( itemtype=> itemtype,
366 itemkey => itemkey,
367 aname => 'NEW_MODEL',
368 avalue => x_new_talv_tbl(1).MODEL_NUMBER);
369 wf_engine.SetItemAttrText ( itemtype=> itemtype,
370 itemkey => itemkey,
371 aname => 'NEW_MANUFACTURER',
372 avalue => x_new_talv_tbl(1).MANUFACTURER_NAME);
373 IF x_new_talv_tbl(1).SUPPLIER_ID IS NOT NULL THEN
374 l_new_vendor := okl_equipment_exchange_pub.get_vendor_name(x_new_talv_tbl(1).SUPPLIER_ID);
375 wf_engine.SetItemAttrText ( itemtype=> itemtype,
376 itemkey => itemkey,
377 aname => 'NEW_VENDOR',
378 avalue => l_new_vendor);
379 END IF;
380 END populate_notification_attribs;
381
382 PROCEDURE check_exchange_type ( itemtype in varchar2,
383 itemkey in varchar2,
384 actid in number,
385 funcmode in varchar2,
386 resultout out nocopy varchar2)
387 IS
388 l_tas_id NUMBER;
389 l_exchange_type VARCHAR2(60);
390 BEGIN
391 if (funcmode = 'RUN') then
392 l_tas_id := wf_engine.GetItemAttrText( itemtype => itemtype,
393 itemkey => itemkey,
394 aname => 'TAS_ID');
395 l_exchange_type := okl_equipment_exchange_pub.get_exchange_type(l_tas_id);
396
397 populate_notification_attribs(itemtype,itemkey,l_tas_id);
398
399 if l_exchange_type IN ('LLT','LLP') THEN
400 resultout := 'COMPLETE:LL';
401 ELSE
402 resultout := 'COMPLETE:'|| l_exchange_type;
403 END IF;
404 RETURN ;
405
406 end if;
407 --
408 -- CANCEL mode
409 --
410 if (funcmode = 'CANCEL') then
411 --
412 resultout := 'COMPLETE:';
413 return;
414 --
415 end if;
416 --
417 -- TIMEOUT mode
418 --
419 if (funcmode = 'TIMEOUT') then
420 --
421 resultout := 'COMPLETE:';
422 return;
423 --
424 end if;
425
426 END check_exchange_type;
427
428
429
430 PROCEDURE check_temp_exchange ( itemtype in varchar2,
431 itemkey in varchar2,
432 actid in number,
433 funcmode in varchar2,
434 resultout out nocopy varchar2)
435 --Returns YES for Temporary Exchange and NO in other cases.
436 IS
437 l_tas_id NUMBER;
438 l_exchange_type VARCHAR2(60);
439 BEGIN
440 if (funcmode = 'RUN') then
441 l_tas_id := wf_engine.GetItemAttrText( itemtype => itemtype,
442 itemkey => itemkey,
443 aname => 'TAS_ID');
444 l_exchange_type := okl_equipment_exchange_pub.get_exchange_type(l_tas_id);
445 IF l_exchange_type = 'LLT' THEN
446 resultout := 'COMPLETE:Y';
447 ELSE
448 resultout := 'COMPLETE:N';
449 END IF;
450 RETURN ;
451
452 end if;
453 --
454 -- CANCEL mode
455 --
456 if (funcmode = 'CANCEL') then
457 --
458 resultout := 'COMPLETE:';
459 return;
460 --
461 end if;
462 --
463 -- TIMEOUT mode
464 --
465 if (funcmode = 'TIMEOUT') then
466 --
467 resultout := 'COMPLETE:';
468 return;
469 --
470 end if;
471
472 END check_temp_exchange;
473
474 ---Procedures for Transfer And Assumption Request Workflow
475
476 PROCEDURE Raise_TransferAsu_Event(p_trx_id IN NUMBER)
477 AS
478 l_parameter_list wf_parameter_list_t;
479 l_key varchar2(240);
480 l_event_name varchar2(240) := 'oracle.apps.okl.cs.transferandassumption';
481 l_seq NUMBER;
482 CURSOR okl_key_csr IS
483 SELECT okl_wf_item_s.nextval
484 FROM dual;
485
486
487 BEGIN
488
489 SAVEPOINT raise_transferasu_event;
490
491 OPEN okl_key_csr;
492 FETCH okl_key_csr INTO l_seq;
493 CLOSE okl_key_csr;
494 l_key := l_event_name ||l_seq ;
495 wf_event.AddParameterToList('TRX_ID',p_trx_id,l_parameter_list);
496 --added by akrangan
497 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
498
499 -- Raise Event
500 wf_event.raise(p_event_name => l_event_name
501 ,p_event_key => l_key
502 ,p_parameters => l_parameter_list);
503 l_parameter_list.DELETE;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
508 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
509 FND_MSG_PUB.ADD;
510 ROLLBACK TO raise_transferasu_event;
511 END raise_transferAsu_event;
512
513
514 Procedure Check_Approval( itemtype in varchar2,
515 itemkey in varchar2,
516 actid in number,
517 funcmode in varchar2,
518 resultout out nocopy varchar2)
519 AS
520 l_trx_id NUMBER;
521 l_ctr NUMBER;
522
523 CURSOR c_check_tfr_req(p_trx_id IN NUMBER)
524 IS
525 SELECT count(*)
526 FROM OKL_TRX_CONTRACTS
527 WHERE ID=p_trx_id;
528
529 BEGIN
530 if (funcmode = 'RUN') then
531 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'TRX_ID');
534
535
536 OPEN c_check_tfr_req(l_trx_id);
537 FETCH c_check_tfr_req into l_ctr;
538 CLOSE c_check_tfr_req;
539
540 IF l_ctr = 0 THEN
541 resultout := 'COMPLETE:REJECTED';
542 ELSIF l_ctr > 0 THEN
543 resultout := 'COMPLETE:APPROVED';
544 END IF;
545
546 RETURN ;
547
548 /*
549 IF l_trx_id = 123 THEN
550 resultout := 'COMPLETE:APPROVED';
551 ELSE
552 resultout := 'COMPLETE:REJECTED';
553 END IF;
554
555 RETURN; */
556
557 end if;
558 --
559 -- CANCEL mode
560 --
561 if (funcmode = 'CANCEL') then
562 --
563 resultout := 'COMPLETE:';
564 return;
565 --
566 end if;
567 --
568 -- TIMEOUT mode
569 --
570 if (funcmode = 'TIMEOUT') then
571 --
572 resultout := 'COMPLETE:';
573 return;
574 --
575 end if;
576
577 END check_approval;
578
579 PROCEDURE Populate_TandA_attributes(itemtype in varchar2,
580 itemkey in varchar2,
581 actid in number,
582 funcmode in varchar2,
583 resultout out nocopy varchar2)
584 AS
585
586 CURSOR c_req_record(p_id IN NUMBER) IS
587 SELECT *
588 FROM OKL_TRX_CONTRACTS
589 WHERE ID = p_id;
590
591 CURSOR c_ctr_no(p_ctr_id IN NUMBER)
592 IS
593 SELECT contract_number
594 FROM OKC_K_HEADERS_V
595 WHERE id=p_ctr_id;
596
597 --Cursor for obtaining the party name on the old contract
598 CURSOR c_party(p_contract_id IN NUMBER) IS
599 SELECT object1_id1,
600 object1_id2
601 FROM okc_k_party_roles_b
602 WHERE dnz_chr_id = p_contract_id
603 AND rle_code = 'LESSEE';
604
605 CURSOR c_lessee(p_id1 IN VARCHAR2,
606 p_id2 IN VARCHAR2) IS
607 SELECT name
608 FROM okx_parties_v
609 WHERE ID1 = p_id1
610 AND ID2 = p_id2;
611
612 l_cust_role VARCHAR2(100);
613 l_credit_role VARCHAR2(100);
614 l_ctr_admin_role VARCHAR2(100);
615 l_vendor_role VARCHAR2(100);
616 l_collections_role VARCHAR2(100);
617 l_contact_email VARCHAR2(2000);
618 l_trx_id NUMBER;
619 l_tcnv_rec OKL_TRX_CONTRACTS_PUB.tcnv_rec_type;
620 l_req_no NUMBER;
621 l_type VARCHAR2(30);
622 l_party_name VARCHAR2(360);
623 l_ctr_no VARCHAR2(120);
624 lx_new_lessee_tbl OKL_CS_TRANSFER_ASSUMPTION_PVT.new_lessee_tbl_type;
625 lx_insurance_tbl OKL_CS_TRANSFER_ASSUMPTION_PVT.insurance_tbl_type;
626 l_message VARCHAR2(30000);
627 l_error VARCHAR2(2000);
628 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
629 l_api_version CONSTANT NUMBER := 1;
630 l_msg_count NUMBER;
631 l_msg_data VARCHAR2(2000);
632 l_requestor VARCHAR2(200);
633 l_requestor_id NUMBER;
634 l_description VARCHAR2(200);
635 l_req_rec c_req_record%ROWTYPE;
636 l_recipient_type VARCHAR2(30);
637 l_id1 VARCHAR2(40);
638 l_id2 VARCHAR2(200);
639 -- rkuttiya added for bug: 4056337
640 l_transfer_date DATE;
641
642 --rkuttiya added for populating From_Address for XMLP Project
643 CURSOR c_agent_csr (c_agent_id NUMBER) IS
644 SELECT nvl(ppf.email_address , fu.email_address) email
645 FROM fnd_user fu,
646 per_people_f ppf
647 WHERE fu.employee_id = ppf.person_id (+)
648 AND fu.user_id = c_agent_id;
649
650 l_from_email VARCHAR2(100);
651
652
653 BEGIN
654 IF (funcmode = 'RUN') THEN
655
656 l_cust_role := fnd_profile.value('OKL_CS_AGENT_CUST_RESPONSE');
657 IF l_cust_role IS NULL THEN
658 l_cust_role := 'SYSADMIN';
659 END IF;
660
661 l_credit_role := fnd_profile.value('OKL_CS_CREDIT_APPROVER');
662 IF l_credit_role IS NULL THEN
663 l_credit_role := 'SYSADMIN';
664 END IF;
665
666 l_ctr_admin_role := fnd_profile.value('OKL_CS_CONTRACT_ADMIN');
667 IF l_ctr_admin_role IS NULL THEN
668 l_ctr_admin_role := 'SYSADMIN';
669 END IF;
670
671 l_vendor_role := fnd_profile.value('OKL_CS_AGENT_VENDOR_RESPONSE');
672 IF l_vendor_role IS NULL THEN
673 l_vendor_role := 'SYSADMIN';
674 END IF;
675
676 l_collections_role := fnd_profile.value('OKL_CS_COLLECTIONS_APPROVER');
677 IF l_collections_role IS NULL THEN
678 l_collections_role := 'SYSADMIN';
679 END IF;
680
681
682 -- Get the value of the request id
683 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'TRX_ID');
686
687 OPEN c_req_record(l_trx_id);
688 FETCH c_req_record INTO l_req_rec;
689 CLOSE c_req_record;
690
691 l_req_no := l_req_rec.trx_number;
692 --rkuttiya added for bug: 4056337
693 l_transfer_date := l_req_rec.date_transaction_occurred;
694
695 OPEN c_party(l_req_rec.khr_id);
696 FETCH c_party INTO l_id1,l_id2;
697 CLOSE c_party;
698
699 OPEN c_lessee(l_id1,l_id2);
700 FETCH c_lessee INTO l_party_name;
701 CLOSE c_lessee;
702
703 OPEN c_ctr_no(l_req_rec.khr_id);
704 FETCH c_ctr_no INTO l_ctr_no;
705 CLOSE c_ctr_no;
706 l_requestor_id := l_req_rec.created_by;
707
708 OPEN c_agent_csr(l_req_rec.last_updated_by);
709 FETCH c_agent_csr into l_from_email;
710 CLOSE c_agent_csr;
711
712 -- get the requestor
713 OKL_AM_WF.GET_NOTIFICATION_AGENT(
714 itemtype => itemtype,
715 itemkey => itemkey,
716 actid => actid,
717 funcmode => funcmode,
718 p_user_id => l_requestor_id,
719 x_name => l_requestor,
720 x_description => l_description);
721
722 --Set the Customer Recipient Type
723 --l_recipient_type := 'PC';
724 --rkuttiya changed recipient type to LESSEE for XMLP
725 l_recipient_type := 'LESSEE';
726
727 --rkuttiya added for Bug:4257336
728 wf_engine.SetItemAttrText(itemtype => itemtype,
729 itemkey => itemkey,
730 aname => 'CONTRACT_ID',
731 avalue => l_req_rec.khr_id);
732 --end changes for Bug:4257336
733
734 wf_engine.SetItemAttrText(itemtype => itemtype,
735 itemkey => itemkey,
736 aname => 'CREATED_BY',
737 avalue => l_requestor_id);
738 wf_engine.SetItemAttrText(itemtype => itemtype,
739 itemkey => itemkey,
740 aname => 'REQUESTER',
741 avalue => l_requestor);
742 wf_engine.SetItemAttrText(itemtype => itemtype,
743 itemkey => itemkey,
744 aname => 'NOTIFY_AGENT',
745 avalue => l_requestor);
746 wf_engine.SetItemAttrText(itemtype => itemtype,
747 itemkey => itemkey,
748 aname => 'RECIPIENT_TYPE',
749 avalue => l_recipient_type);
750 wf_engine.SetItemAttrText(itemtype => itemtype,
751 itemkey => itemkey,
752 aname => 'PROCESS_CODE',
753 avalue => 'CSTSFRASU');
754 wf_engine.SetItemAttrText(itemtype => itemtype,
755 itemkey => itemkey,
756 aname => 'SERVICE_FEE_CODE',
757 avalue => 'CSTSFRFEE');
758 wf_engine.SetItemAttrText(itemtype => itemtype,
759 itemkey => itemkey,
760 aname => 'FROM_ADDRESS',
761 avalue => l_from_email);
762
763 wf_engine.SetItemAttrText (itemtype=> itemtype,
764 itemkey => itemkey,
765 aname => 'CUST_ROLE',
766 avalue => l_cust_role) ;
767
768 wf_engine.SetItemAttrText (itemtype=> itemtype,
769 itemkey => itemkey,
770 aname => 'ROLE_CREDIT',
771 avalue => l_credit_role) ;
772 wf_engine.SetItemAttrText (itemtype=> itemtype,
773 itemkey => itemkey,
774 aname => 'ROLE_ADMIN',
775 avalue => l_ctr_admin_role) ;
776 wf_engine.SetItemAttrText (itemtype=> itemtype,
777 itemkey => itemkey,
778 aname => 'ROLE_VND',
779 avalue => l_vendor_role) ;
780 wf_engine.SetItemAttrText (itemtype=> itemtype,
781 itemkey => itemkey,
782 aname => 'ROLE_COLLECTION',
783 avalue => l_collections_role) ;
784
785
786
787 wf_engine.SetItemAttrText (itemtype=> itemtype,
788 itemkey => itemkey,
789 aname => 'CONTRACT_NUMBER',
790 avalue => l_ctr_no) ;
791 wf_engine.SetItemAttrText (itemtype=> itemtype,
792 itemkey => itemkey,
793 aname => 'TRANSACTION_ID',
794 avalue => l_trx_id) ;
795
796 --rkuttiya added for bug: 4056337
797 wf_engine.SetItemAttrText (itemtype=> itemtype,
798 itemkey => itemkey,
799 aname => 'TRANSFER_DATE',
800 avalue => l_transfer_date) ;
801
802
803 wf_engine.SetItemAttrText (itemtype=> itemtype,
804 itemkey => itemkey,
805 aname => 'OLD_LESSEE',
806 avalue => l_party_name) ;
807
808 wf_engine.SetItemAttrText (itemtype=> itemtype,
809 itemkey => itemkey,
810 aname => 'REQUEST_NUMBER',
811 avalue => l_req_no) ;
812
813
814 IF l_req_rec.complete_transfer_yn = 'N' THEN
815 l_type := 'Partial';
816 wf_engine.SetItemAttrText (itemtype=> itemtype,
817 itemkey => itemkey,
818 aname => 'TRANSFER_TYPE',
819 avalue => 'Partial') ;
820 ELSIF l_req_rec.complete_transfer_yn = 'Y' THEN
821 l_type := 'Complete';
822 wf_engine.SetItemAttrText (itemtype=> itemtype,
823 itemkey => itemkey,
824 aname => 'TRANSFER_TYPE',
825 avalue => 'Complete') ;
826 END IF;
827
828
829 OKL_CS_TRANSFER_ASSUMPTION_PVT.populate_new_lessee_details(p_api_version => l_api_version ,
830 p_init_msg_list => 'F',
831 p_request_id => l_trx_id,
832 x_new_lessee_tbl => lx_new_lessee_tbl ,
833 x_return_status => l_return_status,
834 x_msg_count => l_msg_count,
835 x_msg_data => l_msg_data);
836
837 IF l_return_status <> 'S' THEN
838 FND_MSG_PUB.Count_And_Get
839 ( p_count => l_msg_count,
840 p_data => l_msg_data);
841 Get_Messages(l_msg_count,l_error);
842
843 wf_engine.SetItemAttrText(itemtype => itemtype,
844 itemkey => itemkey,
845 aname => 'ERROR_MESSAGE',
846 avalue => l_error);
847
848 resultout := 'COMPLETE:N';
849 ELSE
850 wf_engine.SetItemAttrText (itemtype=> itemtype,
851 itemkey => itemkey,
852 aname => 'NEW_CONTRACT_NUMBER',
853 avalue => lx_new_lessee_tbl(1).new_contract_number) ;
854
855 wf_engine.SetItemAttrText (itemtype=> itemtype,
856 itemkey => itemkey,
857 aname => 'NEW_LESSEE',
858 avalue => lx_new_lessee_tbl(1).new_lessee);
859 wf_engine.SetItemAttrText (itemtype=> itemtype,
860 itemkey => itemkey,
861 aname => 'RECIPIENT_DESCRIPTION',
862 avalue => lx_new_lessee_tbl(1).contact_name);
863 wf_engine.SetItemAttrText (itemtype=> itemtype,
864 itemkey => itemkey,
865 aname => 'EMAIL_ADDRESS',
866 avalue => lx_new_lessee_tbl(1).contact_email);
867 wf_engine.SetItemAttrText (itemtype=> itemtype,
868 itemkey => itemkey,
869 aname => 'RECIPIENT_ID',
870 avalue => lx_new_lessee_tbl(1).contact_id);
871 wf_engine.SetItemAttrText (itemtype=> itemtype,
872 itemkey => itemkey,
873 aname => 'BILL_TO',
874 avalue => lx_new_lessee_tbl(1).bill_to_address);
875 wf_engine.SetItemAttrText (itemtype=> itemtype,
876 itemkey => itemkey,
877 aname => 'CUST_ACCT_NUMBER',
878 avalue => lx_new_lessee_tbl(1).cust_acct_number);
879 wf_engine.SetItemAttrText (itemtype=> itemtype,
880 itemkey => itemkey,
881 aname => 'BANK_ACCOUNT',
882 avalue => lx_new_lessee_tbl(1).bank_account);
883 wf_engine.SetItemAttrText (itemtype=> itemtype,
884 itemkey => itemkey,
885 aname => 'INVOICE_FORMAT',
886 avalue => lx_new_lessee_tbl(1).invoice_format);
887 wf_engine.SetItemAttrText (itemtype=> itemtype,
888 itemkey => itemkey,
889 aname => 'PAYMENT_METHOD',
890 avalue => lx_new_lessee_tbl(1).payment_method);
891 wf_engine.SetItemAttrText (itemtype=> itemtype,
892 itemkey => itemkey,
893 aname => 'MLA_NO',
894 avalue => lx_new_lessee_tbl(1).master_lease);
895 wf_engine.SetItemAttrText (itemtype=> itemtype,
896 itemkey => itemkey,
897 aname => 'CREDIT_LINE_NUMBER',
898 avalue => lx_new_lessee_tbl(1).credit_line_no);
899
900 IF lx_new_lessee_tbl(1).lease_policy_yn = 'N' THEN
901 OKL_CS_TRANSFER_ASSUMPTION_PVT.Populate_ThirdParty_Insurance
902 (p_api_version => l_api_version ,
903 p_init_msg_list => 'F',
904 p_taa_id => lx_new_lessee_tbl(1).taa_id,
905 x_insurance_tbl => lx_insurance_tbl ,
906 x_return_status => l_return_status,
907 x_msg_count => l_msg_count,
908 x_msg_data => l_msg_data);
909 IF l_return_status <> 'S' THEN
910 FND_MSG_PUB.Count_And_Get
911 ( p_count => l_msg_count,
912 p_data => l_msg_data);
913 Get_Messages(l_msg_count,l_error);
914
915 wf_engine.SetItemAttrText(itemtype => itemtype,
916 itemkey => itemkey,
917 aname => 'ERROR_MESSAGE',
918 avalue => l_error);
919
920 resultout := 'COMPLETE:N';
921 ELSE
922 wf_engine.SetItemAttrText (itemtype=> itemtype,
923 itemkey => itemkey,
924 aname => 'INSURER',
925 avalue => lx_insurance_tbl(1).insurer) ;
926
927 wf_engine.SetItemAttrText (itemtype=> itemtype,
928 itemkey => itemkey,
929 aname => 'INSURANCE_AGENT',
930 avalue => lx_insurance_tbl(1).insurance_agent);
931 wf_engine.SetItemAttrText (itemtype=> itemtype,
932 itemkey => itemkey,
933 aname => 'POLICY_NUMBER',
934 avalue => lx_insurance_tbl(1).policy_number);
935 wf_engine.SetItemAttrText (itemtype=> itemtype,
936 itemkey => itemkey,
937 aname => 'COVERED_AMOUNT',
938 avalue => lx_insurance_tbl(1).covered_amount);
939 wf_engine.SetItemAttrText (itemtype=> itemtype,
940 itemkey => itemkey,
941 aname => 'DEDUCTIBLE_AMOUNT',
942 avalue => lx_insurance_tbl(1).deductible_amount);
943 wf_engine.SetItemAttrText (itemtype=> itemtype,
944 itemkey => itemkey,
945 aname => 'EFFECTIVE_FROM',
946 avalue => lx_insurance_tbl(1).effective_from);
947 wf_engine.SetItemAttrText (itemtype=> itemtype,
948 itemkey => itemkey,
949 aname => 'EFFECTIVE_TO',
950 avalue => lx_insurance_tbl(1).effective_to);
951 wf_engine.SetItemAttrText (itemtype=> itemtype,
952 itemkey => itemkey,
953 aname => 'PROOF_PROVIDED',
954 avalue => lx_insurance_tbl(1).proof_provided);
955 wf_engine.SetItemAttrText (itemtype=> itemtype,
956 itemkey => itemkey,
957 aname => 'PROOF_REQUIRED',
958 avalue => lx_insurance_tbl(1).proof_required);
959 IF lx_insurance_tbl(1).lessor_insured_yn = 'Y' THEN
960 wf_engine.SetItemAttrText (itemtype=> itemtype,
961 itemkey => itemkey,
962 aname => 'LESSOR_INSURED_YN',
963 avalue => 'Yes');
964 ELSIF lx_insurance_tbl(1).lessor_insured_yn = 'Y' THEN
965 wf_engine.SetItemAttrText (itemtype=> itemtype,
966 itemkey => itemkey,
967 aname => 'LESSOR_INSURED_YN',
968 avalue => 'No');
969 END IF;
970
971 IF lx_insurance_tbl(1).lessor_payee_yn = 'Y' THEN
972 wf_engine.SetItemAttrText (itemtype=> itemtype,
973 itemkey => itemkey,
974 aname => 'LESSOR_PAYEE_YN',
975 avalue => 'Yes');
976 ELSIF lx_insurance_tbl(1).lessor_payee_yn = 'Y' THEN
977 wf_engine.SetItemAttrText (itemtype=> itemtype,
978 itemkey => itemkey,
979 aname => 'LESSOR_PAYEE_YN',
980 avalue => 'No');
981 END IF;
982
983 END IF;
984 END IF;
985 END IF;
986
987 l_message := '<p>Please review and approve the following Transfer and Assumption Request: <br> ' ||
988 'Request Number :' || l_req_no ||'<br>'||
989 'Old Contract Number :' || l_ctr_no ||'<br>'||
990 'Old Lessee :' || l_party_name ||'<br>'||
991 'New Contract Number :' || lx_new_lessee_tbl(1).new_contract_number ||'<br>'||
992 'New Lessee :' || lx_new_lessee_tbl(1).new_lessee||'<br>'||
993 'Type of Transfer :' || l_type ||'</p>'||
994 '<p> Please review further details of the request in the Lease Center.</p>';
995
996
997 wf_engine.SetItemAttrText ( itemtype=> itemtype,
998 itemkey => itemkey,
999 aname => 'TRX_TYPE_ID',
1000 avalue => 'OKLCSTRQ');
1001
1002 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1003 itemkey => itemkey,
1004 aname => 'MESSAGE_DESCRIPTION',
1005 avalue => l_message);
1006
1007 resultout := 'COMPLETE:';
1008 RETURN ;
1009 END IF;
1010 --
1011 -- CANCEL mode
1012 --
1013 IF (funcmode = 'CANCEL') THEN
1014 --
1015 resultout := 'COMPLETE:';
1016 return;
1017 --
1018 END IF;
1019 --
1020 -- TIMEOUT mode
1021 --
1022 IF (funcmode = 'TIMEOUT') THEN
1023 --
1024 resultout := 'COMPLETE:';
1025 return;
1026 --
1027 END IF;
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 IF c_req_record%ISOPEN THEN
1031 CLOSE c_req_record;
1032 END IF;
1033 IF c_ctr_no%ISOPEN THEN
1034 CLOSE c_ctr_no;
1035 END IF;
1036 IF c_party%ISOPEN THEN
1037 CLOSE c_party;
1038 END IF;
1039 IF c_lessee%ISOPEN THEN
1040 CLOSE c_lessee;
1041 END IF;
1042
1043 wf_core.context('OKL_CS_WF' , 'populate_TandA_attributes', itemtype, itemkey, actid, funcmode);
1044 RAISE;
1045 END;
1046
1047 Procedure Send_Cust_Fulfill(itemtype in varchar2,
1048 itemkey in varchar2,
1049 actid in number,
1050 funcmode in varchar2,
1051 resultout out nocopy varchar2)
1052 AS
1053 CURSOR c_document(p_ptm_code IN VARCHAR2) IS
1054 SELECT jtf_amv_item_id,email_subject_line
1055 FROM okl_cs_process_tmplts_uv
1056 WHERE NVL(org_id, -99) = NVL(mo_global.get_current_org_id(), -99)
1057 AND start_date <= TRUNC(sysdate)
1058 AND NVL(end_date, sysdate) >= TRUNC(sysdate)
1059 AND ptm_code = p_ptm_code;
1060
1061 l_trx_id NUMBER;
1062 l_ptm_code VARCHAR2(30);
1063 l_agent_id NUMBER(15);
1064 l_server_id NUMBER;
1065 l_content_id NUMBER(15);
1066 l_from VARCHAR2(100);
1067 l_subject VARCHAR2(100);
1068 l_email VARCHAR2(2000);
1069 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1070 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1071 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1072 l_commit CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
1073 lx_return_status VARCHAR2(3);
1074 l_error VARCHAR2(2000);
1075 l_api_version NUMBER := 1.0;
1076 lx_msg_count NUMBER;
1077 lx_msg_data VARCHAR2(2000);
1078 lx_request_id NUMBER;
1079
1080 BEGIN
1081 IF (funcmode = 'RUN') THEN
1082 -- Get the value of the request id
1083 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'TRX_ID');
1086 l_ptm_code := wf_engine.GetItemAttrText( itemtype => itemtype,
1087 itemkey => itemkey,
1088 aname => 'PROCESS_CODE');
1089
1090 l_email := wf_engine.GetItemAttrText( itemtype => itemtype,
1091 itemkey => itemkey,
1092 aname => 'CONTACT_EMAIL');
1093
1094 l_bind_var(1) := 'p_request_id';
1095 l_bind_val(1) := l_trx_id;
1096 l_bind_var_type(1) := 'NUMBER';
1097 l_agent_id := FND_PROFILE.VALUE('USER_ID');
1098 l_server_id := FND_PROFILE.VALUE('OKL_FM_SERVER');
1099
1100 OPEN c_document(l_ptm_code);
1101 FETCH c_document INTO l_content_id,l_subject;
1102 CLOSE c_document;
1103
1104 l_from := '[email protected]';
1105
1106 OKL_FULFILLMENT_PUB.create_fulfillment (
1107 p_api_version => l_api_version,
1108 p_init_msg_list => 'T',
1109 p_agent_id => l_agent_id,
1110 p_server_id => l_server_id,
1111 p_content_id => l_content_id,
1112 p_from => l_from,
1113 p_subject => l_subject,
1114 p_email => l_email,
1115 p_bind_var => l_bind_var,
1116 p_bind_val => l_bind_val,
1117 p_bind_var_type => l_bind_var_type,
1118 p_commit => l_commit,
1119 x_request_id => lx_request_id,
1120 x_return_status => lx_return_status,
1121 x_msg_count => lx_msg_count,
1122 x_msg_data => lx_msg_data);
1123
1124 IF lx_return_status <> 'S' THEN
1125 FND_MSG_PUB.Count_And_Get
1126 ( p_count => lx_msg_count,
1127 p_data => lx_msg_data);
1128 Get_Messages(lx_msg_count,l_error);
1129
1130 wf_engine.SetItemAttrText(itemtype => itemtype,
1131 itemkey => itemkey,
1132 aname => 'ERROR_MESSAGE',
1133 avalue => l_error);
1134
1135 resultout := 'COMPLETE:N';
1136
1137 ELSE
1138 resultout := 'COMPLETE:Y';
1139 END IF;
1140 END IF;
1141
1142 --
1143 -- CANCEL mode
1144 --
1145 IF (funcmode = 'CANCEL') THEN
1146 --
1147 resultout := 'COMPLETE:';
1148 return;
1149 --
1150 END IF;
1151 --
1152 -- TIMEOUT mode
1153 --
1154 IF (funcmode = 'TIMEOUT') THEN
1155 --
1156 resultout := 'COMPLETE:';
1157 return;
1158 --
1159 END IF;
1160 EXCEPTION
1161 WHEN OTHERS THEN
1162 IF c_document%ISOPEN THEN
1163 CLOSE c_document;
1164 END IF;
1165 wf_core.context('OKL_CS_WF' , 'Send_Cust_Fulfill', itemtype, itemkey, actid, funcmode);
1166 RAISE;
1167 END Send_Cust_Fulfill;
1168
1169 Procedure Send_Vendor_Fulfill(itemtype in varchar2,
1170 itemkey in varchar2,
1171 actid in number,
1172 funcmode in varchar2,
1173 resultout out nocopy varchar2)
1174 AS
1175 CURSOR c_document(p_ptm_code IN VARCHAR2) IS
1176 SELECT jtf_amv_item_id,email_subject_line
1177 FROM okl_cs_process_tmplts_uv
1178 WHERE NVL(org_id, -99) = NVL(mo_global.get_current_org_id(), -99)
1179 AND start_date <= TRUNC(sysdate)
1180 AND NVL(end_date, sysdate) >= TRUNC(sysdate)
1181 AND ptm_code = p_ptm_code;
1182
1183 l_trx_id NUMBER;
1184 l_ptm_code VARCHAR2(30);
1185 l_agent_id NUMBER(15);
1186 l_server_id NUMBER;
1187 l_content_id NUMBER(15);
1188 l_from VARCHAR2(100);
1189 l_subject VARCHAR2(100);
1190 l_email VARCHAR2(2000);
1191 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1192 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1193 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1194 l_commit CONSTANT VARCHAR2(1) := OKL_API.G_TRUE;
1195 lx_return_status VARCHAR2(3);
1196 l_error VARCHAR2(2000);
1197 l_api_version NUMBER := 1.0;
1198 lx_msg_count NUMBER;
1199 lx_msg_data VARCHAR2(2000);
1200 lx_request_id NUMBER;
1201
1202 BEGIN
1203 IF (funcmode = 'RUN') THEN
1204 -- Get the value of the request id
1205 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1206 itemkey => itemkey,
1207 aname => 'TRX_ID');
1208 l_ptm_code := wf_engine.GetItemAttrText( itemtype => itemtype,
1209 itemkey => itemkey,
1210 aname => 'PROCESS_CODE');
1211
1212 l_email := wf_engine.GetItemAttrText( itemtype => itemtype,
1213 itemkey => itemkey,
1214 aname => 'VENDOR_EMAIL');
1215
1216 l_bind_var(1) := 'p_request_id';
1217 l_bind_val(1) := l_trx_id;
1218 l_bind_var_type(1) := 'NUMBER';
1219 l_agent_id := FND_PROFILE.VALUE('USER_ID');
1220 l_server_id := FND_PROFILE.VALUE('OKL_FM_SERVER');
1221
1222 OPEN c_document(l_ptm_code);
1223 FETCH c_document INTO l_content_id,l_subject;
1224 CLOSE c_document;
1225
1226 l_from := '[email protected]';
1227
1228 OKL_FULFILLMENT_PUB.create_fulfillment (
1229 p_api_version => l_api_version,
1230 p_init_msg_list => 'T',
1231 p_agent_id => l_agent_id,
1232 p_server_id => l_server_id,
1233 p_content_id => l_content_id,
1234 p_from => l_from,
1235 p_subject => l_subject,
1236 p_email => l_email,
1237 p_bind_var => l_bind_var,
1238 p_bind_val => l_bind_val,
1239 p_bind_var_type => l_bind_var_type,
1240 p_commit => l_commit,
1241 x_request_id => lx_request_id,
1242 x_return_status => lx_return_status,
1243 x_msg_count => lx_msg_count,
1244 x_msg_data => lx_msg_data);
1245
1246 IF lx_return_status <> 'S' THEN
1247 FND_MSG_PUB.Count_And_Get
1248 ( p_count => lx_msg_count,
1249 p_data => lx_msg_data);
1250 Get_Messages(lx_msg_count,l_error);
1251
1252 wf_engine.SetItemAttrText(itemtype => itemtype,
1253 itemkey => itemkey,
1254 aname => 'ERROR_MESSAGE',
1255 avalue => l_error);
1256
1257 resultout := 'COMPLETE:N';
1258
1259 ELSE
1260 resultout := 'COMPLETE:Y';
1261 END IF;
1262 END IF;
1263
1264 --
1265 -- CANCEL mode
1266 --
1267 IF (funcmode = 'CANCEL') THEN
1268 --
1269 resultout := 'COMPLETE:';
1270 return;
1271 --
1272 END IF;
1273 --
1274 -- TIMEOUT mode
1275 --
1276 IF (funcmode = 'TIMEOUT') THEN
1277 --
1278 resultout := 'COMPLETE:';
1279 return;
1280 --
1281 END IF;
1282 EXCEPTION
1283 WHEN OTHERS THEN
1284 IF c_document%ISOPEN THEN
1285 CLOSE c_document;
1286 END IF;
1287 wf_core.context('OKL_CS_WF' , 'Send_Vendor_Fulfill', itemtype, itemkey, actid, funcmode);
1288 RAISE;
1289 END Send_Vendor_Fulfill;
1290
1291 Procedure Approve_Request(itemtype in varchar2,
1292 itemkey in varchar2,
1293 actid in number,
1294 funcmode in varchar2,
1295 resultout out nocopy varchar2)
1296 AS
1297
1298 l_trx_id NUMBER;
1299 l_contract_id NUMBER;
1300 l_status VARCHAR2(30);
1301 l_return_status VARCHAR2(100);
1302 l_api_version NUMBER := 1.0;
1303 l_msg_count NUMBER;
1304 l_msg_data VARCHAR2(2000);
1305 l_error VARCHAR2(2000);
1306
1307 SUBTYPE tcnv_rec_type IS okl_trx_contracts_pvt.tcnv_rec_type;
1308 SUBTYPE tcnv_tbl_type IS okl_trx_contracts_pvt.tcnv_tbl_type;
1309
1310 SUBTYPE tclv_rec_type IS okl_trx_contracts_pvt.tclv_rec_type;
1311 SUBTYPE tclv_tbl_type IS okl_trx_contracts_pvt.tclv_tbl_type;
1312
1313 l_tcnv_rec tcnv_rec_type;
1314 l_tclv_tbl tclv_tbl_type;
1315 lx_tcnv_rec tcnv_rec_type;
1316 lx_tclv_tbl tclv_tbl_type;
1317
1318 BEGIN
1319 IF (funcmode = 'RUN') THEN
1320 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1321 itemkey => itemkey,
1322 aname => 'TRX_ID');
1323 l_status := 'APPROVED';
1324
1325 l_tcnv_rec.id := l_trx_id;
1326 l_tcnv_rec.tsu_code := l_status;
1327
1328 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1329 p_init_msg_list => fnd_api.g_false,
1330 x_return_status => l_return_status,
1331 x_msg_count => l_msg_count,
1332 x_msg_data => l_msg_data,
1333 p_tcnv_rec => l_tcnv_rec,
1334 p_tclv_tbl => l_tclv_tbl,
1335 x_tcnv_rec => lx_tcnv_rec,
1336 x_tclv_tbl => lx_tclv_tbl);
1337
1338 IF l_return_status <> 'S' THEN
1339 FND_MSG_PUB.Count_And_Get
1340 ( p_count => l_msg_count,
1341 p_data => l_msg_data);
1342 Get_Messages(l_msg_count,l_error);
1343
1344 wf_engine.SetItemAttrText(itemtype => itemtype,
1345 itemkey => itemkey,
1346 aname => 'ERROR_MESSAGE',
1347 avalue => l_error);
1348 resultout := 'COMPLETE:N';
1349 ELSE
1350 resultout := 'COMPLETE:Y';
1351 END IF;
1352 RETURN ;
1353 END IF;
1354 --
1355 -- CANCEL mode
1356 --
1357 IF (funcmode = 'CANCEL') THEN
1358 --
1359 resultout := 'COMPLETE:';
1360 return;
1361 --
1362 END IF;
1363 --
1364 -- TIMEOUT mode
1365 --
1366 IF (funcmode = 'TIMEOUT') THEN
1367 --
1368 resultout := 'COMPLETE:';
1369 return;
1370 --
1371 END IF;
1372
1373
1374
1375 END Approve_Request;
1376
1377 PROCEDURE Update_Request_Internal( itemtype in varchar2,
1378 itemkey in varchar2,
1379 actid in number,
1380 funcmode in varchar2,
1381 resultout out nocopy varchar2)
1382 AS
1383
1384 l_trx_id NUMBER;
1385 l_contract_id NUMBER;
1386 l_status VARCHAR2(30);
1387 l_rjn_code VARCHAR2(30);
1388 l_approved_yn VARCHAR2(1);
1389
1390 l_return_status VARCHAR2(100);
1391 l_api_version NUMBER := 1.0;
1392 l_msg_count NUMBER;
1393 l_msg_data VARCHAR2(2000);
1394
1395 SUBTYPE tcnv_rec_type IS okl_trx_contracts_pvt.tcnv_rec_type;
1396 SUBTYPE tcnv_tbl_type IS okl_trx_contracts_pvt.tcnv_tbl_type;
1397
1398 SUBTYPE tclv_rec_type IS okl_trx_contracts_pvt.tclv_rec_type;
1399 SUBTYPE tclv_tbl_type IS okl_trx_contracts_pvt.tclv_tbl_type;
1400
1401 l_tcnv_rec tcnv_rec_type;
1402 l_tclv_tbl tclv_tbl_type;
1403 lx_tcnv_rec tcnv_rec_type;
1404 lx_tclv_tbl tclv_tbl_type;
1405 l_error VARCHAR2(2000);
1406
1407 BEGIN
1408 IF (funcmode = 'RUN') THEN
1409 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1410 itemkey => itemkey,
1411 aname => 'TRX_ID');
1412
1413 l_approved_yn := wf_engine.GetItemAttrText( itemtype => itemtype,
1414 itemkey => itemkey,
1415 aname => 'APPROVED_YN');
1416 IF l_approved_yn = 'Y' THEN
1417 l_status := 'SUBMITTED';
1418 ELSE
1419 l_status := 'REJECTED';
1420 l_rjn_code := 'INTAPPR';
1421 END IF;
1422
1423 l_tcnv_rec.id := l_trx_id;
1424 l_tcnv_rec.tsu_code := l_status;
1425 l_tcnv_rec.rjn_code := l_rjn_code;
1426
1427 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1428 p_init_msg_list => fnd_api.g_false,
1429 x_return_status => l_return_status,
1430 x_msg_count => l_msg_count,
1431 x_msg_data => l_msg_data,
1432 p_tcnv_rec => l_tcnv_rec,
1433 p_tclv_tbl => l_tclv_tbl,
1434 x_tcnv_rec => lx_tcnv_rec,
1435 x_tclv_tbl => lx_tclv_tbl);
1436
1437 IF l_return_status <> 'S' THEN
1438 FND_MSG_PUB.Count_And_Get
1439 ( p_count => l_msg_count,
1440 p_data => l_msg_data);
1441 Get_Messages(l_msg_count,l_error);
1442
1443 wf_engine.SetItemAttrText(itemtype => itemtype,
1444 itemkey => itemkey,
1445 aname => 'ERROR_MESSAGE',
1446 avalue => l_error);
1447
1448 resultout := 'COMPLETE:N';
1449
1450 ELSE
1451 IF l_approved_yn = 'Y' THEN
1452 resultout := 'COMPLETE:APPROVED';
1453 ELSIF l_approved_yn = 'N' THEN
1454 resultout := 'COMPLETE:REJECTED';
1455 END IF;
1456 END IF;
1457 RETURN ;
1458 END IF;
1459 --
1460 -- CANCEL mode
1461 --
1462 IF (funcmode = 'CANCEL') THEN
1463 --
1464 resultout := 'COMPLETE:';
1465 return;
1466 --
1467 END IF;
1468 --
1469 -- TIMEOUT mode
1470 --
1471 IF (funcmode = 'TIMEOUT') THEN
1472 --
1473 resultout := 'COMPLETE:';
1474 return;
1475 --
1476 END IF;
1477
1478
1479
1480 END Update_Request_Internal;
1481
1482 PROCEDURE Customer_Post( itemtype in varchar2,
1483 itemkey in varchar2,
1484 actid in number,
1485 funcmode in varchar2,
1486 resultout out nocopy varchar2)
1487
1488 AS
1489
1490
1491 l_nid NUMBER;
1492 l_ntf_comments VARCHAR2(4000);
1493
1494 l_trx_id NUMBER;
1495 l_contract_id NUMBER;
1496 l_sts_code VARCHAR2(30);
1497 l_rjn_code VARCHAR2(30);
1498
1499 l_return_status VARCHAR2(100);
1500 l_api_version NUMBER := 1.0;
1501 l_msg_count NUMBER;
1502 l_msg_data VARCHAR2(2000);
1503
1504 l_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
1505 l_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1506 lx_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
1507 lx_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1508 l_error VARCHAR2(2000);
1509
1510 BEGIN
1511 IF (funcmode = 'RESPOND') THEN
1512 --get request id
1513 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1514 itemkey => itemkey,
1515 aname => 'TRX_ID');
1516
1517
1518 --get notification id from wf_engine context
1519 l_nid := WF_ENGINE.CONTEXT_NID;
1520 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
1521
1522 IF l_ntf_result = 'NO' THEN
1523 l_sts_code := 'REJECTED';
1524 l_rjn_code := 'CUST';
1525 -- l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
1526 ELSIF l_ntf_result = 'YES' THEN
1527 l_sts_code := 'CUSTAPPR';
1528 -- l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
1529 END IF;
1530
1531
1532 l_tcnv_rec.id := l_trx_id;
1533 --l_tcnv_rec.description := l_ntf_comments;
1534 l_tcnv_rec.tsu_code := l_sts_code;
1535 l_tcnv_rec.rjn_code := l_rjn_code;
1536
1537
1538 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1539 p_init_msg_list => fnd_api.g_false,
1540 x_return_status => l_return_status,
1541 x_msg_count => l_msg_count,
1542 x_msg_data => l_msg_data,
1543 p_tcnv_rec => l_tcnv_rec,
1544 p_tclv_tbl => l_tclv_tbl,
1545 x_tcnv_rec => lx_tcnv_rec,
1546 x_tclv_tbl => lx_tclv_tbl);
1547
1548
1549 IF l_return_status <> 'S' THEN
1550 FND_MSG_PUB.Count_And_Get
1551 ( p_count => l_msg_count,
1552 p_data => l_msg_data);
1553 Get_Messages(l_msg_count,l_error);
1554
1555 wf_engine.SetItemAttrText(itemtype => itemtype,
1556 itemkey => itemkey,
1557 aname => 'ERROR_MESSAGE',
1558 avalue => l_error);
1559
1560 resultout := 'COMPLETE:N';
1561 ELSE
1562 IF l_ntf_result = 'YES' THEN
1563 resultout := 'COMPLETE:YES';
1564 return;
1565 ELSIF l_ntf_result = 'NO' THEN
1566 resultout := 'COMPLETE:NO';
1567 return;
1568 END IF;
1569 END IF;
1570 END IF;
1571 --
1572 --Transfer Mode
1573 --
1574 IF funcmode = 'TRANSFER' THEN
1575 resultout := wf_engine.eng_null;
1576 return;
1577 END IF;
1578
1579 --Run Mode
1580 IF funcmode = 'RUN' THEN
1581 resultout := 'COMPLETE:'||l_ntf_result;
1582 return;
1583 END IF;
1584
1585 --
1586 -- CANCEL mode
1587 --
1588 IF (funcmode = 'CANCEL') THEN
1589 --
1590 resultout := 'COMPLETE:';
1591 return;
1592 --
1593 END IF;
1594 --
1595 -- TIMEOUT mode
1596 --
1597 IF (funcmode = 'TIMEOUT') THEN
1598 --
1599 resultout := 'COMPLETE:';
1600 return;
1601 --
1602 END IF;
1603
1604
1605 EXCEPTION
1606 when others then
1607 wf_core.context('OKL_CS_WF',
1608 'Collections_Post',
1609 itemtype,
1610 itemkey,
1611 to_char(actid),
1612 funcmode);
1613 RAISE;
1614 END Customer_post;
1615
1616 PROCEDURE Vendor_Post( itemtype in varchar2,
1617 itemkey in varchar2,
1618 actid in number,
1619 funcmode in varchar2,
1620 resultout out nocopy varchar2)
1621
1622 AS
1623
1624 l_nid NUMBER;
1625 --rkuttiya commented foll.for bug # 5149488
1626 -- l_ntf_result VARCHAR2(30);
1627 --
1628 l_ntf_comments VARCHAR2(4000);
1629 l_trx_id NUMBER;
1630 l_contract_id NUMBER;
1631 l_sts_code VARCHAR2(30);
1632 l_rjn_code VARCHAR2(30);
1633 l_approved_yn VARCHAR2(1);
1634
1635 l_return_status VARCHAR2(100);
1636 l_api_version NUMBER := 1.0;
1637 l_msg_count NUMBER;
1638 l_msg_data VARCHAR2(2000);
1639
1640 l_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
1641 l_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1642 lx_tcnv_rec okl_trx_contracts_pvt.tcnv_rec_type;
1643 lx_tclv_tbl okl_trx_contracts_pvt.tclv_tbl_type;
1644 l_error VARCHAR2(2000);
1645
1646 BEGIN
1647 IF (funcmode = 'RESPOND') THEN
1648 --get request id
1649 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'TRX_ID');
1652
1653
1654 --get notification id from wf_engine context
1655 l_nid := WF_ENGINE.CONTEXT_NID;
1656 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
1657
1658 --rkuttiya changed for bug#5149488
1659 IF l_ntf_result = 'VND_REJECTED' THEN
1660 l_sts_code := 'REJECTED';
1661 l_rjn_code := 'VND';
1662 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
1663 ELSIF l_ntf_result = 'VND_APPROVED' THEN
1664 l_sts_code := 'VENDAPPR';
1665 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
1666 END IF;
1667
1668
1669 l_tcnv_rec.id := l_trx_id;
1670 l_tcnv_rec.description := l_ntf_comments;
1671 l_tcnv_rec.tsu_code := l_sts_code;
1672 l_tcnv_rec.rjn_code := l_rjn_code;
1673
1674
1675 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
1676 p_init_msg_list => fnd_api.g_false,
1677 x_return_status => l_return_status,
1678 x_msg_count => l_msg_count,
1679 x_msg_data => l_msg_data,
1680 p_tcnv_rec => l_tcnv_rec,
1681 p_tclv_tbl => l_tclv_tbl,
1682 x_tcnv_rec => lx_tcnv_rec,
1683 x_tclv_tbl => lx_tclv_tbl);
1684
1685
1686 IF l_return_status <> 'S' THEN
1687 FND_MSG_PUB.Count_And_Get
1688 ( p_count => l_msg_count,
1689 p_data => l_msg_data);
1690 Get_Messages(l_msg_count,l_error);
1691
1692 wf_engine.SetItemAttrText(itemtype => itemtype,
1693 itemkey => itemkey,
1694 aname => 'ERROR_MESSAGE',
1695 avalue => l_error);
1696
1697 resultout := 'COMPLETE:N';
1698 ELSE
1699 --rkuttiya changed for bug#5149488
1700 IF l_ntf_result = 'VND_APPROVED' THEN
1701 resultout := 'COMPLETE:VND_APPROVED';
1702 return;
1703 ELSIF l_ntf_result = 'VND_REJECTED' THEN
1704 resultout := 'COMPLETE:VND_REJECTED';
1705 return;
1706 END IF;
1707 END IF;
1708 END IF;
1709 --
1710 --Transfer Mode
1711 --
1712 IF funcmode = 'TRANSFER' THEN
1713 resultout := wf_engine.eng_null;
1714 return;
1715 END IF;
1716
1717 --rkuttiya added for bug # 5149488
1718 --Run Mode
1719 IF funcmode = 'RUN' THEN
1720 resultout := 'COMPLETE:'|| l_ntf_result;
1721 return;
1722 END IF;
1723
1724 --
1725 -- CANCEL mode
1726 --
1727 IF (funcmode = 'CANCEL') THEN
1728 --
1729 resultout := 'COMPLETE:';
1730 return;
1731 --
1732 END IF;
1733 --
1734 -- TIMEOUT mode
1735 --
1736 IF (funcmode = 'TIMEOUT') THEN
1737 --
1738 resultout := 'COMPLETE:';
1739 return;
1740 --
1741 END IF;
1742
1743
1744 EXCEPTION
1745 when others then
1746 wf_core.context('OKL_CS_WF',
1747 'Vendor_Post',
1748 itemtype,
1749 itemkey,
1750 to_char(actid),
1751 funcmode);
1752 RAISE;
1753 END Vendor_post;
1754
1755
1756 Procedure Check_Vendor_Pgm(itemtype in varchar2,
1757 itemkey in varchar2,
1758 actid in number,
1759 funcmode in varchar2,
1760 resultout out nocopy varchar2) IS
1761
1762 CURSOR c_vnd_pgm(p_ctr_id IN NUMBER) IS
1763 SELECT khr_id
1764 FROM okl_k_headers_v
1765 where id = p_ctr_id ;
1766
1767 CURSOR c_contact_role(p_contract_id IN NUMBER) IS
1768 SELECT co.object1_id1,
1769 co.jtot_object1_code
1770 FROM okc_contacts_v co,
1771 okc_k_party_roles_b pr
1772 WHERE co.dnz_chr_id = p_contract_id
1773 AND co.cpl_id = pr.id
1774 AND co.dnz_chr_id = pr.dnz_chr_id
1775 AND pr.rle_code = 'OKL_VENDOR';
1776
1777 CURSOR c_email(p_object_id IN NUMBER) IS
1778 SELECT name,
1779 email_address
1780 FROM okx_salesreps_v
1781 WHERE id1 = p_object_id;
1782
1783
1784 l_ctr_id NUMBER;
1785 l_khr_id NUMBER;
1786 l_email VARCHAR2(30);
1787 l_object_id NUMBER;
1788 l_object_code VARCHAR2(30);
1789 l_contact_name VARCHAR2(240);
1790
1791 --rkuttiya added for XMLP
1792 l_api_version NUMBER := 1.0;
1793 l_msg_count NUMBER;
1794 l_msg_data VARCHAR2(2000);
1795 l_error VARCHAR2(2000);
1796 l_trx_id NUMBER;
1797 l_init_msg_list VARCHAR2(1) := 'T';
1798 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1799 ERR EXCEPTION;
1800 l_batch_id NUMBER;
1801 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1802 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1803 BEGIN
1804 IF funcmode = 'RUN' THEN
1805 l_ctr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1806 itemkey => itemkey,
1807 aname => 'CONTRACT_ID');
1808 OPEN c_vnd_pgm(l_ctr_id);
1809 FETCH c_vnd_pgm INTO l_khr_id;
1810 CLOSE c_vnd_pgm;
1811
1812 IF l_khr_id IS NOT NULL THEN
1813 OPEN c_contact_role(l_khr_id);
1814 FETCH c_contact_role INTO l_object_id,l_object_code;
1815 CLOSE c_contact_role;
1816 IF l_object_code = 'OKX_SALEPERS' THEN
1817 OPEN c_email(l_object_id);
1818 FETCH c_email INTO l_contact_name,l_email;
1819 CLOSE c_email;
1820 END IF;
1821
1822 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1823 itemkey => itemkey,
1824 aname => 'TRX_ID');
1825
1826 --set the value for the vendor email attribute,recipient type - VC for Vendor Contact
1827 wf_engine.SetItemAttrText (itemtype=> itemtype,
1828 itemkey => itemkey,
1829 aname => 'VENDOR_EMAIL',
1830 avalue => l_email) ;
1831
1832 --set the EMAIL_ADDRESS attribute to that of Vendor for XML Publisher Report
1833 wf_engine.SetItemAttrText(itemtype => itemtype,
1834 itemkey => itemkey,
1835 aname => 'EMAIL_ADDRESS',
1836 avalue => l_email);
1837
1838 --rkuttiya changed recipient type to VENDOR for XMLPProject
1839 wf_engine.SetItemAttrText (itemtype=> itemtype,
1840 itemkey => itemkey,
1841 aname => 'RECIPIENT_TYPE',
1842 avalue => 'VENDOR') ;
1843
1844 wf_engine.SetItemAttrText (itemtype=> itemtype,
1845 itemkey => itemkey,
1846 aname => 'RECIPIENT_DESCRIPTION',
1847 avalue => l_contact_name);
1848
1849 wf_engine.SetItemAttrText (itemtype=> itemtype,
1850 itemkey => itemkey,
1851 aname => 'RECIPIENT_ID',
1852 avalue => l_object_id);
1853
1854 --rkuttiya added for XMLP Project
1855 --code for inserting bind parameters into table
1856
1857 l_xmp_rec.param_name := 'P_REQUEST_ID';
1858 l_xmp_rec.param_value := l_trx_id;
1859 l_xmp_rec.param_type_code := 'NUMBER';
1860
1861 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
1862 p_api_version => l_api_version
1863 ,p_init_msg_list => l_init_msg_list
1864 ,x_return_status => l_return_status
1865 ,x_msg_count => l_msg_count
1866 ,x_msg_data => l_msg_data
1867 ,p_xmp_rec => l_xmp_rec
1868 ,x_xmp_rec => lx_xmp_rec
1869 );
1870
1871 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1872 l_batch_id := lx_xmp_rec.batch_id;
1873 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1874 itemkey => itemkey,
1875 aname => 'BATCH_ID',
1876 avalue => l_batch_id );
1877 resultout := 'COMPLETE:YES';
1878
1879 ELSE
1880 FND_MSG_PUB.Count_And_Get
1881 ( p_count => l_msg_count,
1882 p_data => l_msg_data);
1883 Get_Messages(l_msg_count,l_error);
1884
1885 wf_engine.SetItemAttrText(itemtype => itemtype,
1886 itemkey => itemkey,
1887 aname => 'ERROR_MESSAGE',
1888 avalue => l_error);
1889 resultout := 'COMPLETE:N';
1890 END IF;
1891
1892 ELSE
1893 resultout := 'COMPLETE:NO';
1894 END IF;
1895 END IF;
1896
1897 --
1898 -- CANCEL mode
1899 --
1900 IF (funcmode = 'CANCEL') THEN
1901 --
1902 resultout := 'COMPLETE:';
1903 return;
1904 --
1905 END IF;
1906 --
1907 -- TIMEOUT mode
1908 --
1909 IF (funcmode = 'TIMEOUT') THEN
1910 --
1911 resultout := 'COMPLETE:';
1912 return;
1913 --
1914 END IF;
1915
1916
1917 EXCEPTION
1918 when others then
1919 wf_core.context('OKL_CS_WF',
1920 'Check_Vendor_Pgm',
1921 itemtype,
1922 itemkey,
1923 to_char(actid),
1924 funcmode);
1925 RAISE;
1926 END Check_Vendor_Pgm;
1927
1928 Procedure Check_Cust_Delinquency(itemtype in varchar2,
1929 itemkey in varchar2,
1930 actid in number,
1931 funcmode in varchar2,
1932 resultout out nocopy varchar2)
1933 AS
1934 CURSOR c_cust_del(p_party_id IN NUMBER) IS
1935 SELECT COUNT(*) FROM
1936 IEX_DELINQUENCIES_ALL
1937 WHERE PARTY_CUST_ID = p_party_id
1938 AND STATUS NOT IN ('CURRENT','PREDELINQUENT');
1939 l_party_id NUMBER;
1940 l_ctr NUMBER;
1941 BEGIN
1942 IF funcmode = 'RUN' THEN
1943 l_party_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1944 itemkey => itemkey,
1945 aname => 'PARTY_ID');
1946 OPEN c_cust_del(l_party_id);
1947 FETCH c_cust_del INTO l_ctr;
1948 CLOSE c_cust_del;
1949
1950 IF l_ctr > 0 THEN
1951 resultout := 'COMPLETE:YES';
1952 ELSE
1953 resultout := 'COMPLETE:NO';
1954 END IF;
1955 return;
1956 END IF;
1957
1958 --
1959 -- CANCEL mode
1960 --
1961 IF (funcmode = 'CANCEL') THEN
1962 --
1963 resultout := 'COMPLETE:';
1964 return;
1965 --
1966 END IF;
1967 --
1968 -- TIMEOUT mode
1969 --
1970 IF (funcmode = 'TIMEOUT') THEN
1971 --
1972 resultout := 'COMPLETE:';
1973 return;
1974 --
1975 END IF;
1976
1977
1978 EXCEPTION
1979 when others then
1980 wf_core.context('OKL_CS_WF',
1981 'Check_Cust_Delinquency',
1982 itemtype,
1983 itemkey,
1984 to_char(actid),
1985 funcmode);
1986 RAISE;
1987 END Check_Cust_Delinquency;
1988
1989 Procedure Apply_Service_Fees(itemtype in varchar2,
1990 itemkey in varchar2,
1991 actid in number,
1992 funcmode in varchar2,
1993 resultout out nocopy varchar2)
1994 AS
1995 CURSOR c_svf_info(p_svf_code IN VARCHAR2) IS
1996 SELECT fnd.meaning svf_name,
1997 fnd.description svf_desc,
1998 svf.amount svf_amount
1999 FROM fnd_lookups fnd,
2000 okl_service_fees_b svf
2001 WHERE svf.srv_code= 'CSTSFRFEE'
2002 AND svf.srv_code = fnd.lookup_code
2003 AND lookup_type = 'OKL_SERVICE_FEES';
2004
2005 l_trx_id NUMBER;
2006 l_khr_id NUMBER;
2007 l_sty_name VARCHAR2(150);
2008 l_svf_code VARCHAR2(30);
2009 l_svf_amount NUMBER;
2010 l_svf_desc VARCHAR2(1995);
2011 l_svf_name VARCHAR2(100);
2012 lx_return_status VARCHAR2(1);
2013 lx_msg_count NUMBER;
2014 lx_msg_data VARCHAR2(2000);
2015
2016 lx_tai_id NUMBER;
2017
2018 l_data VARCHAR2(2000);
2019 l_msg_index_out NUMBER;
2020 l_error VARCHAR2(2000);
2021
2022
2023 BEGIN
2024 IF (funcmode = 'RUN') THEN
2025 -- Get the value of the request id
2026 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2027 itemkey => itemkey,
2028 aname => 'TRX_ID');
2029 l_svf_code := wf_engine.GetItemAttrText( itemtype => itemtype,
2030 itemkey => itemkey,
2031 aname => 'SERVICE_FEE_CODE');
2032
2033 l_sty_name := 'SERVICE_FEE_TRANS_REQUEST';
2034
2035
2036 l_khr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2037 itemkey => itemkey,
2038 aname => 'CONTRACT_ID');
2039 OPEN c_svf_info(l_svf_code);
2040 FETCH c_svf_info into l_svf_name,l_svf_desc,l_svf_amount;
2041 CLOSE c_svf_info;
2042
2043
2044 okl_cs_transactions_pub.create_svf_invoice(p_khr_id => l_khr_id,
2045 p_sty_name => l_sty_name,
2046 p_svf_code => l_svf_code,
2047 p_svf_amount => l_svf_amount,
2048 p_svf_desc => l_svf_desc,
2049 x_tai_id => lx_tai_id,
2050 x_return_status => lx_return_status,
2051 x_msg_count => lx_msg_count,
2052 x_msg_data => lx_msg_data);
2053
2054
2055 IF lx_return_status <> 'S' THEN
2056 FND_MSG_PUB.Count_And_Get
2057 ( p_count => lx_msg_count,
2058 p_data => lx_msg_data);
2059 Get_Messages(lx_msg_count,l_error);
2060
2061 wf_engine.SetItemAttrText(itemtype => itemtype,
2062 itemkey => itemkey,
2063 aname => 'ERROR_MESSAGE',
2064 avalue => l_error);
2065 resultout := 'COMPLETE:N';
2066
2067 ELSE
2068 resultout := 'COMPLETE:Y';
2069 END IF;
2070 END IF;
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073 IF c_svf_info%ISOPEN THEN
2074 CLOSE c_svf_info;
2075 END IF;
2076 wf_core.context('OKL_CS_WF' , 'Apply_Service_Fees', itemtype, itemkey, actid, funcmode);
2077 RAISE;
2078 END Apply_Service_Fees;
2079
2080
2081 Procedure Credit_post(itemtype in varchar2,
2082 itemkey in varchar2,
2083 actid in number,
2084 funcmode in varchar2,
2085 resultout out nocopy varchar2)
2086 AS
2087
2088 l_nid NUMBER;
2089 l_ntf_comments VARCHAR2(4000);
2090 l_rjn_code VARCHAR2(30);
2091
2092 l_trx_id NUMBER;
2093 l_contract_id NUMBER;
2094
2095 SUBTYPE tcnv_rec_type IS okl_trx_contracts_pvt.tcnv_rec_type;
2096 SUBTYPE tcnv_tbl_type IS okl_trx_contracts_pvt.tcnv_tbl_type;
2097
2098 SUBTYPE tclv_rec_type IS okl_trx_contracts_pvt.tclv_rec_type;
2099 SUBTYPE tclv_tbl_type IS okl_trx_contracts_pvt.tclv_tbl_type;
2100
2101 l_tcnv_rec tcnv_rec_type;
2102 l_tclv_tbl tclv_tbl_type;
2103 lx_tcnv_rec tcnv_rec_type;
2104 lx_tclv_tbl tclv_tbl_type;
2105
2106 l_api_version NUMBER := 1.0;
2107 l_msg_count NUMBER;
2108 l_msg_data VARCHAR2(2000);
2109 l_error VARCHAR2(2000);
2110 l_sts_code VARCHAR2(30);
2111
2112 l_init_msg_list VARCHAR2(1) := 'T';
2113 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2114 ERR EXCEPTION;
2115 l_batch_id NUMBER;
2116 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
2117 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
2118 BEGIN
2119 IF (funcmode = 'RESPOND') THEN
2120 --get request id
2121 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2122 itemkey => itemkey,
2123 aname => 'TRX_ID');
2124
2125
2126 --get notification id from wf_engine context
2127 l_nid := WF_ENGINE.CONTEXT_NID;
2128 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
2129
2130 IF l_ntf_result = 'CREDIT_REJECTED' THEN
2131 l_sts_code := 'REJECTED';
2132 l_rjn_code := 'CRDPT';
2133 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
2134
2135 ELSIF l_ntf_result = 'CREDIT_APPROVED' THEN
2136 l_sts_code := 'CREDAPPR';
2137 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
2138 END IF;
2139
2140
2141 l_tcnv_rec.id := l_trx_id;
2142 l_tcnv_rec.description := l_ntf_comments;
2143 l_tcnv_rec.tsu_code := l_sts_code;
2144 l_tcnv_rec.rjn_code := l_rjn_code;
2145
2146 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
2147 p_init_msg_list => fnd_api.g_false,
2148 x_return_status => l_return_status,
2149 x_msg_count => l_msg_count,
2150 x_msg_data => l_msg_data,
2151 p_tcnv_rec => l_tcnv_rec,
2152 p_tclv_tbl => l_tclv_tbl,
2153 x_tcnv_rec => lx_tcnv_rec,
2154 x_tclv_tbl => lx_tclv_tbl);
2155
2156
2157 IF l_return_status <> 'S' THEN
2158 FND_MSG_PUB.Count_And_Get
2159 ( p_count => l_msg_count,
2160 p_data => l_msg_data);
2161 Get_Messages(l_msg_count,l_error);
2162
2163 wf_engine.SetItemAttrText(itemtype => itemtype,
2164 itemkey => itemkey,
2165 aname => 'ERROR_MESSAGE',
2166 avalue => l_error);
2167
2168 resultout := 'COMPLETE:N';
2169 ELSE
2170 IF l_ntf_result = 'CREDIT_REJECTED' THEN
2171 resultout := 'COMPLETE:CREDIT_REJECTED';
2172 return;
2173 ELSIF l_ntf_result = 'CREDIT_APPROVED' THEN
2174 resultout := 'COMPLETE:CREDIT_APPROVED';
2175
2176 --18-Dec-06 rkuttiya added for XMLP Project
2177 --code for inserting bind parameters into table
2178
2179 l_xmp_rec.param_name := 'P_REQUEST_ID';
2180 l_xmp_rec.param_value := l_trx_id;
2181 l_xmp_rec.param_type_code := 'NUMBER';
2182
2183 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
2184 p_api_version => l_api_version
2185 ,p_init_msg_list => l_init_msg_list
2186 ,x_return_status => l_return_status
2187 ,x_msg_count => l_msg_count
2188 ,x_msg_data => l_msg_data
2189 ,p_xmp_rec => l_xmp_rec
2190 ,x_xmp_rec => lx_xmp_rec
2191 );
2192
2193 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
2194 l_batch_id := lx_xmp_rec.batch_id;
2195 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2196 itemkey => itemkey,
2197 aname => 'BATCH_ID',
2198 avalue => l_batch_id );
2199
2200 ELSE
2201 FND_MSG_PUB.Count_And_Get
2202 ( p_count => l_msg_count,
2203 p_data => l_msg_data);
2204 Get_Messages(l_msg_count,l_error);
2205
2206 wf_engine.SetItemAttrText(itemtype => itemtype,
2207 itemkey => itemkey,
2208 aname => 'ERROR_MESSAGE',
2209 avalue => l_error);
2210 resultout := 'COMPLETE:N';
2211 END IF;
2212 return;
2213 END IF;
2214 END IF;
2215 END IF;
2216 --
2217 --Transfer Mode
2218 --
2219 IF funcmode = 'TRANSFER' THEN
2220 resultout := wf_engine.eng_null;
2221 return;
2222 END IF;
2223
2224 --Run Mode
2225 IF funcmode = 'RUN' THEN
2226 resultout := 'COMPLETE:'|| l_ntf_result;
2227 return;
2228 END IF;
2229
2230 --
2231 -- CANCEL mode
2232 --
2233 IF (funcmode = 'CANCEL') THEN
2234 --
2235 resultout := 'COMPLETE:';
2236 return;
2237 --
2238 END IF;
2239 --
2240 -- TIMEOUT mode
2241 --
2242 IF (funcmode = 'TIMEOUT') THEN
2243 --
2244 resultout := 'COMPLETE:';
2245 return;
2246 --
2247 END IF;
2248
2249
2250 EXCEPTION
2251 when others then
2252 wf_core.context('OKL_CS_WF',
2253 'Credit_Post',
2254 itemtype,
2255 itemkey,
2256 to_char(actid),
2257 funcmode);
2258 RAISE;
2259 END Credit_post;
2260
2261 Procedure Collections_post(itemtype in varchar2,
2262 itemkey in varchar2,
2263 actid in number,
2264 funcmode in varchar2,
2265 resultout out nocopy varchar2)
2266 AS
2267
2268 l_nid NUMBER;
2269 l_ntf_comments VARCHAR2(4000);
2270 l_rjn_code VARCHAR2(30);
2271
2272 l_trx_id NUMBER;
2273 l_contract_id NUMBER;
2274
2275 SUBTYPE tcnv_rec_type IS okl_trx_contracts_pvt.tcnv_rec_type;
2276 SUBTYPE tcnv_tbl_type IS okl_trx_contracts_pvt.tcnv_tbl_type;
2277
2278 SUBTYPE tclv_rec_type IS okl_trx_contracts_pvt.tclv_rec_type;
2279 SUBTYPE tclv_tbl_type IS okl_trx_contracts_pvt.tclv_tbl_type;
2280
2281 l_tcnv_rec tcnv_rec_type;
2282 l_tclv_tbl tclv_tbl_type;
2283 lx_tcnv_rec tcnv_rec_type;
2284 lx_tclv_tbl tclv_tbl_type;
2285
2286 l_return_status VARCHAR2(100);
2287 l_api_version NUMBER := 1.0;
2288 l_msg_count NUMBER;
2289 l_msg_data VARCHAR2(2000);
2290 l_error VARCHAR2(2000);
2291 l_sts_code VARCHAR2(30);
2292 BEGIN
2293 IF (funcmode = 'RESPOND') THEN
2294 --get request id
2295 l_trx_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2296 itemkey => itemkey,
2297 aname => 'TRX_ID');
2298
2299
2300 --get notification id from wf_engine context
2301 l_nid := WF_ENGINE.CONTEXT_NID;
2302 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
2303
2304 IF l_ntf_result = 'COLLECTIONS_REJECTED' THEN
2305 l_sts_code := 'REJECTED';
2306 l_rjn_code := 'CODPT';
2307 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
2308 ELSE
2309 l_sts_code := 'COLLAPPR';
2310 l_ntf_comments := wf_notification.GetAttrText(l_nid,'COMMENTS');
2311 END IF;
2312
2313
2314 l_tcnv_rec.id := l_trx_id;
2315 l_tcnv_rec.description := l_ntf_comments;
2316 l_tcnv_rec.tsu_code := l_sts_code;
2317 l_tcnv_rec.rjn_code := l_rjn_code;
2318
2319 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
2320 p_init_msg_list => fnd_api.g_false,
2321 x_return_status => l_return_status,
2322 x_msg_count => l_msg_count,
2323 x_msg_data => l_msg_data,
2324 p_tcnv_rec => l_tcnv_rec,
2325 p_tclv_tbl => l_tclv_tbl,
2326 x_tcnv_rec => lx_tcnv_rec,
2327 x_tclv_tbl => lx_tclv_tbl);
2328
2329
2330 IF l_return_status <> 'S' THEN
2331 FND_MSG_PUB.Count_And_Get
2332 ( p_count => l_msg_count,
2333 p_data => l_msg_data);
2334 Get_Messages(l_msg_count,l_error);
2335
2336 wf_engine.SetItemAttrText(itemtype => itemtype,
2337 itemkey => itemkey,
2338 aname => 'ERROR_MESSAGE',
2339 avalue => l_error);
2340
2341 resultout := 'COMPLETE:N';
2342 ELSE
2343 IF l_ntf_result = 'COLLECTIONS_REJECTED' THEN
2344 resultout := 'COMPLETE:COLLECTIONS_REJECTED';
2345 return;
2346 ELSIF l_ntf_result = 'COLLECTIONS_APPROVED' THEN
2347 resultout := 'COMPLETE:COLLECTIONS_APPROVED';
2348 return;
2349 END IF;
2350 END IF;
2351 END IF;
2352 --
2353 --Transfer Mode
2354 --
2355 IF funcmode = 'TRANSFER' THEN
2356 resultout := wf_engine.eng_null;
2357 return;
2358 END IF;
2359
2360 --Run Mode
2361 IF funcmode = 'RUN' THEN
2362 resultout := 'COMPLETE:'|| l_ntf_result;
2363 return;
2364 END IF;
2365
2366 --
2367 -- CANCEL mode
2368 --
2369 IF (funcmode = 'CANCEL') THEN
2370 --
2371 resultout := 'COMPLETE:';
2372 return;
2373 --
2374 END IF;
2375 --
2376 -- TIMEOUT mode
2377 --
2378 IF (funcmode = 'TIMEOUT') THEN
2379 --
2380 resultout := 'COMPLETE:';
2381 return;
2382 --
2383 END IF;
2384
2385
2386 EXCEPTION
2387 when others then
2388 wf_core.context('OKL_CS_WF',
2389 'Collections_Post',
2390 itemtype,
2391 itemkey,
2392 to_char(actid),
2393 funcmode);
2394 RAISE;
2395 END Collections_post;
2396
2397 PROCEDURE days_cust_balance_overdue ( itemtype in varchar2,
2398 itemkey in varchar2,
2399 actid in number,
2400 funcmode in varchar2,
2401 resultout out nocopy varchar2)
2402 IS
2403 l_contract_id NUMBER;
2404 l_inv_days_tbl inv_days_tbl_type;
2405 l_return_status VARCHAR2(10);
2406 l_days_overdue NUMBER;
2407 BEGIN
2408 if (funcmode = 'RUN') then
2409 l_contract_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2410 itemkey => itemkey,
2411 aname => 'CONTRACT_ID');
2412
2413 days_cust_balance_overdue(p_contract_id => l_contract_id
2414 ,x_inv_days_tbl => l_inv_days_tbl
2415 ,x_return_status => l_return_status);
2416
2417 IF l_return_status = 'S' THEN
2418 l_days_overdue := l_inv_days_tbl(l_inv_days_tbl.FIRST).days;
2419 resultout := 'COMPLETE:' || l_days_overdue;
2420 ELSE
2421 resultout := 'COMPLETE:NO';
2422 END IF;
2423 RETURN ;
2424
2425 end if;
2426 --
2427 -- CANCEL mode
2428 --
2429 if (funcmode = 'CANCEL') then
2430 --
2431 resultout := 'COMPLETE:';
2432 return;
2433 --
2434 end if;
2435 --
2436 -- TIMEOUT mode
2437 --
2438 if (funcmode = 'TIMEOUT') then
2439 --
2440 resultout := 'COMPLETE:';
2441 return;
2442 --
2443 end if;
2444
2445 EXCEPTION
2446 when others then
2447 wf_core.context('OKL_CS_WF',
2448 'days_cust_balance_overdue',
2449 itemtype,
2450 itemkey,
2451 to_char(actid),
2452 funcmode);
2453 RAISE;
2454
2455
2456 END days_cust_balance_overdue;
2457
2458 PROCEDURE get_contract_balance ( itemtype in varchar2,
2459 itemkey in varchar2,
2460 actid in number,
2461 funcmode in varchar2,
2462 resultout out nocopy varchar2)
2463 IS
2464 l_contract_id NUMBER;
2465 l_outstanding_balance NUMBER;
2466 l_return_status VARCHAR2(10);
2467 BEGIN
2468 if (funcmode = 'RUN') then
2469 l_contract_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2470 itemkey => itemkey,
2471 aname => 'CONTRACT_ID');
2472
2473 get_contract_balance(p_contract_id => l_contract_id
2474 ,x_outstanding_balance => l_outstanding_balance
2475 ,x_return_status => l_return_status);
2476
2477 IF l_return_status = 'S' THEN
2478 resultout := 'COMPLETE:' || l_outstanding_balance;
2479 ELSE
2480 resultout := 'COMPLETE:NO';
2481 END IF;
2482 RETURN ;
2483
2484 end if;
2485 --
2486 -- CANCEL mode
2487 --
2488 if (funcmode = 'CANCEL') then
2489 --
2490 resultout := 'COMPLETE:';
2491 return;
2492 --
2493 end if;
2494 --
2495 -- TIMEOUT mode
2496 --
2497 if (funcmode = 'TIMEOUT') then
2498 --
2499 resultout := 'COMPLETE:';
2500 return;
2501 --
2502 end if;
2503
2504 EXCEPTION
2505 when others then
2506 wf_core.context('OKL_CS_WF',
2507 'get_contract_balance',
2508 itemtype,
2509 itemkey,
2510 to_char(actid),
2511 funcmode);
2512 RAISE;
2513
2514
2515 END get_contract_balance;
2516
2517 PROCEDURE get_customer_balance ( itemtype in varchar2,
2518 itemkey in varchar2,
2519 actid in number,
2520 funcmode in varchar2,
2521 resultout out nocopy varchar2)
2522 IS
2523 l_cust_account_id NUMBER;
2524 l_outstanding_balance NUMBER;
2525 l_return_status VARCHAR2(10);
2526 BEGIN
2527 if (funcmode = 'RUN') then
2528 l_cust_account_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2529 itemkey => itemkey,
2530 aname => 'CUST_ACCOUNT_ID');
2531
2532 get_customer_balance(p_cust_account_id => l_cust_account_id
2533 ,x_outstanding_balance => l_outstanding_balance
2534 ,x_return_status => l_return_status);
2535
2536 IF l_return_status = 'S' THEN
2537 resultout := 'COMPLETE:' || l_outstanding_balance;
2538 ELSE
2539 resultout := 'COMPLETE:NO';
2540 END IF;
2541 RETURN ;
2542
2543 end if;
2544 --
2545 -- CANCEL mode
2546 --
2547 if (funcmode = 'CANCEL') then
2548 --
2549 resultout := 'COMPLETE:';
2550 return;
2551 --
2552 end if;
2553 --
2554 -- TIMEOUT mode
2555 --
2556 if (funcmode = 'TIMEOUT') then
2557 --
2558 resultout := 'COMPLETE:';
2559 return;
2560 --
2561 end if;
2562
2563 EXCEPTION
2564 when others then
2565 wf_core.context('OKL_CS_WF',
2566 'get_customer_balance',
2567 itemtype,
2568 itemkey,
2569 to_char(actid),
2570 funcmode);
2571 RAISE;
2572
2573
2574 END get_customer_balance;
2575
2576 PROCEDURE get_product ( itemtype in varchar2,
2577 itemkey in varchar2,
2578 actid in number,
2579 funcmode in varchar2,
2580 resultout out nocopy varchar2)
2581 IS
2582 l_contract_id NUMBER;
2583 l_product_rec product_rec_type;
2584 l_return_status VARCHAR2(10);
2585 l_product_name VARCHAR2(150);
2586 BEGIN
2587 if (funcmode = 'RUN') then
2588 l_contract_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2589 itemkey => itemkey,
2590 aname => 'CONTRACT_ID');
2591
2592 get_product(p_contract_id => l_contract_id
2593 ,x_product_rec => l_product_rec
2594 ,x_return_status => l_return_status);
2595
2596 IF l_return_status = 'S' THEN
2597 l_product_name := l_product_rec.product_name;
2598 resultout := 'COMPLETE:' || l_product_name;
2599 ELSE
2600 resultout := 'COMPLETE:NO';
2601 END IF;
2602 RETURN ;
2603
2604 end if;
2605 --
2606 -- CANCEL mode
2607 --
2608 if (funcmode = 'CANCEL') then
2609 --
2610 resultout := 'COMPLETE:';
2611 return;
2612 --
2613 end if;
2614 --
2615 -- TIMEOUT mode
2616 --
2617 if (funcmode = 'TIMEOUT') then
2618 --
2619 resultout := 'COMPLETE:';
2620 return;
2621 --
2622 end if;
2623 EXCEPTION
2624 when others then
2625 wf_core.context('OKL_CS_WF',
2626 'get_product',
2627 itemtype,
2628 itemkey,
2629 to_char(actid),
2630 funcmode);
2631 RAISE;
2632
2633
2634 END get_product;
2635
2636 PROCEDURE get_bill_to_address ( itemtype in varchar2,
2637 itemkey in varchar2,
2638 actid in number,
2639 funcmode in varchar2,
2640 resultout out nocopy varchar2)
2641 IS
2642 l_contract_id NUMBER;
2643 l_address_rec address_rec_type;
2644 l_return_status VARCHAR2(10);
2645 l_address_desc VARCHAR2(80);
2646 BEGIN
2647 if (funcmode = 'RUN') then
2648 l_contract_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2649 itemkey => itemkey,
2650 aname => 'CONTRACT_ID');
2651
2652 get_bill_to_address(p_contract_id => l_contract_id
2653 ,x_address_rec => l_address_rec
2654 ,x_return_status => l_return_status);
2655
2656 IF l_return_status = 'S' THEN
2657 l_address_desc := l_address_rec.description;
2658 resultout := 'COMPLETE:' || l_address_desc;
2659 ELSE
2660 resultout := 'COMPLETE:NO';
2661 END IF;
2662 RETURN ;
2663
2664 end if;
2665 --
2666 -- CANCEL mode
2667 --
2668 if (funcmode = 'CANCEL') then
2669 --
2670 resultout := 'COMPLETE:';
2671 return;
2672 --
2673 end if;
2674 --
2675 -- TIMEOUT mode
2676 --
2677 if (funcmode = 'TIMEOUT') then
2678 --
2679 resultout := 'COMPLETE:';
2680 return;
2681 --
2682 end if;
2683
2684 EXCEPTION
2685 when others then
2686 wf_core.context('OKL_CS_WF',
2687 'get_bill_to_address',
2688 itemtype,
2689 itemkey,
2690 to_char(actid),
2691 funcmode);
2692 RAISE;
2693
2694
2695 END get_bill_to_address;
2696
2697
2698
2699 ---------------------------------------------------------------
2700 -- The following APIS are utility APIs for getting information
2701 -- for a contract.
2702 ---------------------------------------------------------------
2703
2704 procedure days_cust_balance_overdue
2705 (p_contract_id IN NUMBER
2706 ,x_inv_days_tbl OUT NOCOPY inv_days_tbl_type
2707 ,x_return_status OUT NOCOPY VARCHAR2)
2708 AS
2709 ------------------------------------------------------------------
2710 -- The output parameter is of the form
2711 -- Invoice Number Days Amount Khrid
2712 -- ABC 8 3000.00 7667718289128936832
2713 -- DEF 5 8000.00 7667718289128936832
2714 -- XYZ 2 1000.00 7667718289128936832
2715 -------------------------------------------------------------------
2716
2717 ----------------------------------------------------------
2718 -- This cursor gets the id from the header table for all the
2719 -- invoices which have payment due and the due date is less
2720 -- than the current date(this info is stored in ar_payment_schedules_all).
2721 -- So traverse from header to line to Streams table and then to the
2722 -- payments table.
2723 ----------------------------------------------------------
2724 CURSOR c_overdue_hdr(c_khr_id NUMBER)
2725 IS
2726 select consolidated_invoice_number,id
2727 from okl_cnsld_ar_hdrs_b
2728 where id in
2729 (select distinct(cnr_id)
2730 from okl_cnsld_ar_lines_b
2731 where id in
2732 (select
2733 distinct b.lln_id
2734 from ar_payment_schedules_all a,
2735 okl_cnsld_ar_strms_b b
2736 where b.receivables_invoice_id=a.customer_trx_id
2737 and b.khr_id = c_khr_id
2738 and a.amount_due_remaining > 0
2739 and a.due_date < sysdate));
2740
2741 ----------------------------------------------------------
2742 -- This cursor get the days the payment is overdue, Total Amount Due,
2743 -- for a particular contract and in descending order of the date.
2744 ----------------------------------------------------------
2745 CURSOR c_overdue_days (c_ar_hdr_id NUMBER)
2746 IS
2747 select trunc(sysdate) - trunc(d.due_date) days,
2748 sum(amount_due_remaining) total_amount,
2749 c.khr_id
2750 from
2751 okl_cnsld_ar_hdrs_b a,
2752 okl_cnsld_ar_lines_b b,
2753 okl_cnsld_ar_strms_b c,
2754 ar_payment_schedules_all d
2755 where
2756 a.id=b.cnr_id and
2757 b.id=c.lln_id and
2758 c.receivables_invoice_id = d.customer_trx_id and
2759 a.id=c_ar_hdr_id
2760 group by (trunc(sysdate) - trunc(d.due_date)),c.khr_id
2761 order by days desc;
2762
2763 i NUMBER := 0;
2764
2765 BEGIN
2766
2767
2768 FOR hdr_rec in c_overdue_hdr(p_contract_id)
2769 LOOP
2770
2771 FOR day_rec in c_overdue_days(hdr_rec.id)
2772 LOOP
2773 i := i+1;
2774
2775 x_inv_days_tbl(i).consolidated_invoice_number := hdr_rec.consolidated_invoice_number;
2776 x_inv_days_tbl(i).days := day_rec.days;
2777 x_inv_days_tbl(i).amount_due_remaining := day_rec.total_amount;
2778 x_inv_days_tbl(i).khr_id := day_rec.khr_id;
2779
2780 END LOOP;
2781 END LOOP;
2782 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2783 EXCEPTION
2784 WHEN OTHERS THEN
2785 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2786 ,p_msg_name => G_UNEXPECTED_ERROR
2787 ,p_token1 => G_SQLCODE_TOKEN
2788 ,p_token1_value => SQLCODE
2789 ,p_token2 => G_SQLERRM_TOKEN
2790 ,p_token2_value => SQLERRM);
2791 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2792 END days_cust_balance_overdue;
2793
2794 ------------------------------------------------------------------
2795 -- The following API gets the outstanding Balance for a
2796 -- particular contract.
2797 ------------------------------------------------------------------
2798
2799 PROCEDURE get_contract_balance (
2800 p_contract_id IN NUMBER,
2801 x_outstanding_balance OUT NOCOPY NUMBER,
2802 x_return_status OUT NOCOPY VARCHAR2)
2803 IS
2804 -- Get amount outstanding
2805 CURSOR outstanding_rcvble_csr IS
2806 SELECT NVL(SUM(amount_due_remaining),0)
2807 FROM okl_bpd_leasing_payment_trx_v
2808 WHERE contract_id = p_contract_id;
2809
2810 BEGIN
2811 OPEN outstanding_rcvble_csr;
2812 FETCH outstanding_rcvble_csr into x_outstanding_balance;
2813 CLOSE outstanding_rcvble_csr;
2814 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2815 EXCEPTION
2816 WHEN OTHERS THEN
2817 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2818 ,p_msg_name => G_UNEXPECTED_ERROR
2819 ,p_token1 => G_SQLCODE_TOKEN
2820 ,p_token1_value => SQLCODE
2821 ,p_token2 => G_SQLERRM_TOKEN
2822 ,p_token2_value => SQLERRM);
2823 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2824 END get_contract_balance;
2825
2826 ------------------------------------------------------------------
2827 -- The following API gets the outstanding Balance for a
2828 -- particular customer.
2829 -- This Calls the get_contract_balance API in a loop for all
2830 -- the contracts found for a customer and totals the amount for
2831 -- each customer.
2832 ------------------------------------------------------------------
2833 PROCEDURE get_customer_balance (
2834 p_cust_account_id IN NUMBER,
2835 x_outstanding_balance OUT NOCOPY NUMBER,
2836 x_return_status OUT NOCOPY VARCHAR2)
2837 IS
2838 -- Get List of contracts associated with this customer.
2839
2840 CURSOR contracts_csr(c_cust_account_id NUMBER) IS
2841 SELECT chrb.id khr_id , CHRB.contract_number,
2842 hca.cust_account_id
2843 FROM OKC_K_HEADERS_B CHRB,
2844 HZ_CUST_ACCOUNTS HCA
2845 WHERE CHRB.CUST_ACCT_ID = HCA.CUST_ACCOUNT_ID
2846 AND hca.cust_account_id = c_cust_account_id;
2847
2848
2849 -- smoduga:Removed for rules migration
2850 /*FROM OKC_K_HEADERS_B CHRB,
2851 OKC_RULES_B ORGB1,
2852 OKC_RULE_GROUPS_B ORGB2,
2853 HZ_CUST_ACCOUNTS HCA
2854 WHERE orgb2.id = orgb1.rgp_id
2855 AND to_char(HCA.CUST_ACCOUNT_ID) = orgb1.object1_id1
2856 AND chrb.id = orgb1.dnz_chr_id
2857 AND chrb.id = orgb2.chr_id
2858 AND orgb1.rule_information_category = 'CAN'
2859 AND orgb2.rgd_code = 'LACAN'
2860 AND hca.cust_account_id = c_cust_account_id;*/
2861
2862 l_outstanding_balance NUMBER := 0;
2863 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
2864 l_total_balance NUMBER :=0;
2865 BEGIN
2866
2867 FOR khr_rec in contracts_csr(p_cust_account_id)
2868 LOOP
2869 get_contract_balance(khr_rec.khr_id,l_outstanding_balance,l_return_status);
2870
2871 IF l_return_status = Okl_Api.G_RET_STS_SUCCESS THEN
2872 l_total_balance := l_total_balance + l_outstanding_balance;
2873 END IF;
2874 END LOOP;
2875 x_outstanding_balance := l_total_balance;
2876 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2877
2878 EXCEPTION
2879 WHEN OTHERS THEN
2880 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2881 ,p_msg_name => G_UNEXPECTED_ERROR
2882 ,p_token1 => G_SQLCODE_TOKEN
2883 ,p_token1_value => SQLCODE
2884 ,p_token2 => G_SQLERRM_TOKEN
2885 ,p_token2_value => SQLERRM);
2886 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2887 END get_customer_balance;
2888
2889 ------------------------------------------------------------------
2890 -- The following API gets the product name for a particular
2891 -- Contract
2892 ------------------------------------------------------------------
2893 PROCEDURE get_product (
2894 p_contract_id IN NUMBER,
2895 x_product_rec OUT NOCOPY product_rec_type,
2896 x_return_status OUT NOCOPY VARCHAR2)
2897 IS
2898 CURSOR products_csr(c_contract_id NUMBER)
2899 IS
2900 SELECT b.id,b.name,b.description
2901 FROM okl_k_headers a,okl_products b
2902 WHERE a.pdt_id=b.id
2903 AND a.pdt_id is not null
2904 AND a.id=c_contract_id;
2905 BEGIN
2906
2907 OPEN products_csr(p_contract_id);
2908 FETCH products_csr INTO x_product_rec.product_id,
2909 x_product_rec.product_name,
2910 x_product_rec.product_description;
2911 CLOSE products_csr;
2912 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2913
2914 EXCEPTION
2915 WHEN OTHERS THEN
2916 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2917 ,p_msg_name => G_UNEXPECTED_ERROR
2918 ,p_token1 => G_SQLCODE_TOKEN
2919 ,p_token1_value => SQLCODE
2920 ,p_token2 => G_SQLERRM_TOKEN
2921 ,p_token2_value => SQLERRM);
2922 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2923 END get_product;
2924
2925 ------------------------------------------------------------------
2926 -- The following API gets the Bill to Address for a particular
2927 -- Contract.
2928 -- Note : the Description field formats the address and returns the
2929 -- address
2930 ------------------------------------------------------------------
2931
2932 PROCEDURE get_bill_to_address (
2933 p_contract_id IN NUMBER,
2934 x_address_rec OUT NOCOPY address_rec_type,
2935 x_return_status OUT NOCOPY VARCHAR2)
2936 IS
2937 CURSOR address_csr(c_contract_id NUMBER)
2938 IS
2939
2940 SELECT c.address1,
2941 c.address2,
2942 c.address3,
2943 c.address4,
2944 c.city,
2945 c.postal_code,
2946 c.state,
2947 c.province,
2948 c.county,
2949 c.country,
2950 c.description
2951 FROM okc_k_headers_b chr,
2952 okx_cust_site_uses_v c
2953 WHERE c.id1 = chr.bill_to_site_use_id
2954 and chr.id = c_contract_id ;
2955
2956 -- smoduga : Removed rule related table for rules migration
2957 /*
2958 FROM
2959 okc_rule_groups_v a
2960 ,okc_rules_v b
2961 ,okx_cust_site_uses_v c
2962 WHERE
2963 a.rgd_code='LABILL'
2964 AND a.id=b.rgp_id
2965 AND b.RULE_INFORMATION_CATEGORY = 'BTO'
2966 AND b.object1_id1= c.id1
2967 AND a.chr_id=c_contract_id;*/
2968
2969 BEGIN
2970
2971 OPEN address_csr(p_contract_id);
2972 FETCH address_csr INTO x_address_rec.address1,
2973 x_address_rec.address2,
2974 x_address_rec.address3,
2975 x_address_rec.address4,
2976 x_address_rec.city,
2977 x_address_rec.postal_code,
2978 x_address_rec.state,
2979 x_address_rec.province,
2980 x_address_rec.county,
2981 x_address_rec.country,
2982 x_address_rec.description;
2983 CLOSE address_csr;
2984 x_return_status := OKL_API.G_RET_STS_SUCCESS;
2985
2986 EXCEPTION
2987 WHEN OTHERS THEN
2988 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
2989 ,p_msg_name => G_UNEXPECTED_ERROR
2990 ,p_token1 => G_SQLCODE_TOKEN
2991 ,p_token1_value => SQLCODE
2992 ,p_token2 => G_SQLERRM_TOKEN
2993 ,p_token2_value => SQLERRM);
2994 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2995 END get_bill_to_address;
2996
2997 --Lease Renewal Work flow APIs
2998
2999 PROCEDURE raise_lease_renewal_event(p_request_id IN NUMBER)
3000 AS
3001 l_parameter_list wf_parameter_list_t;
3002 l_key varchar2(240);
3003 l_event_name varchar2(240) := 'oracle.apps.okl.cs.contractleaserenewal';
3004 l_seq NUMBER;
3005 CURSOR okl_key_csr IS
3006 SELECT okl_wf_item_s.nextval
3007 FROM dual;
3008
3009
3010 BEGIN
3011
3012 SAVEPOINT raise_lease_renewal_event;
3013
3014 OPEN okl_key_csr;
3015 FETCH okl_key_csr INTO l_seq;
3016 CLOSE okl_key_csr;
3017 l_key := l_event_name ||l_seq ;
3018
3019 wf_event.AddParameterToList('REQUEST_ID',p_request_id,l_parameter_list);
3020 --added by akrangan
3021 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
3022
3023 -- Raise Event
3024 wf_event.raise(p_event_name => l_event_name
3025 ,p_event_key => l_key
3026 ,p_parameters => l_parameter_list);
3027 l_parameter_list.DELETE;
3028
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
3032 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3033 FND_MSG_PUB.ADD;
3034 ROLLBACK TO raise_lease_renewal_event;
3035 END raise_lease_renewal_event;
3036
3037
3038 PROCEDURE populate_lease_renew_attrib(itemtype in varchar2,
3039 itemkey in varchar2,
3040 actid in number,
3041 funcmode in varchar2,
3042 resultout out nocopy varchar2)
3043 AS
3044 l_lease_renewal_role VARCHAR2(50) ;
3045 l_request_id NUMBER;
3046 l_old_contract_id NUMBER;
3047 l_yield NUMBER;
3048 l_start_date DATE;
3049 l_end_date DATE;
3050 l_term NUMBER;
3051 l_rent NUMBER;
3052 l_residula_percentage NUMBER;
3053 l_new_contract_id NUMBER;
3054 l_parent_contract_num VARCHAR2(100);
3055
3056 CURSOR c_req_record(p_id IN NUMBER) IS
3057 SELECT *
3058 FROM OKL_TRX_REQUESTS
3059 WHERE ID = p_id;
3060
3061 l_req_rec c_req_record%ROWTYPE;
3062
3063 CURSOR c_ctr_no(p_ctr_id IN NUMBER) IS
3064 SELECT contract_number
3065 FROM OKC_K_HEADERS_V
3066 WHERE id=p_ctr_id;
3067 BEGIN
3068
3069 if (funcmode = 'RUN') then
3070 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3071 itemkey => itemkey,
3072 aname => 'REQUEST_ID');
3073 OPEN c_req_record(l_request_id);
3074 FETCH c_req_record INTO l_req_rec;
3075 CLOSE c_req_record;
3076
3077 l_old_contract_id := l_req_rec.parent_khr_id;
3078 l_yield := l_req_rec.yield;
3079 l_start_date := l_req_rec.start_date;
3080 l_end_date := l_req_rec.end_date;
3081 l_term := l_req_rec.term_duration;
3082 l_rent := l_req_rec.amount;
3083 l_residula_percentage := l_req_rec.residual;
3084 l_new_contract_id := l_req_rec.dnz_khr_id;
3085
3086
3087 OPEN c_ctr_no(l_old_contract_id);
3088 FETCH c_ctr_no INTO l_parent_contract_num;
3089 CLOSE c_ctr_no;
3090
3091 --rkuttiya added for bug:2923037
3092 l_lease_renewal_role := fnd_profile.value('OKL_CTR_RENEWAL_REP');
3093 IF l_lease_renewal_role IS NULL THEN
3094 l_lease_renewal_role := 'SYSADMIN';
3095 END IF;
3096 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3097 itemkey => itemkey,
3098 aname => 'ROLE_TO_RENEW_LEASE',
3099 avalue => l_lease_renewal_role);
3100 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3101 itemkey => itemkey,
3102 aname => 'OLD_CHRID',
3103 avalue => l_old_contract_id);
3104 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3105 itemkey => itemkey,
3106 aname => 'YIELD',
3107 avalue => l_yield);
3108 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3109 itemkey => itemkey,
3110 aname => 'START_DATE',
3111 avalue => l_start_date);
3112 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3113 itemkey => itemkey,
3114 aname => 'END_DATE',
3115 avalue => l_end_date);
3116 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3117 itemkey => itemkey,
3118 aname => 'TERM',
3119 avalue => l_term);
3120 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3121 itemkey => itemkey,
3122 aname => 'RENT',
3123 avalue => l_rent);
3124 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3125 itemkey => itemkey,
3126 aname => 'RESIDUAL_PTY',
3127 avalue => l_residula_percentage);
3128 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3129 itemkey => itemkey,
3130 aname => 'CHR_ID',
3131 avalue => l_new_contract_id);
3132 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3133 itemkey => itemkey,
3134 aname => 'OLD_CONTRACT_NUMBER',
3135 avalue => l_parent_contract_num);
3136 resultout := 'COMPLETE:';
3137 RETURN ;
3138
3139 end if;
3140 --
3141 -- CANCEL mode
3142 --
3143 if (funcmode = 'CANCEL') then
3144 --
3145 resultout := 'COMPLETE:';
3146 return;
3147 --
3148 end if;
3149 --
3150 -- TIMEOUT mode
3151 --
3152 if (funcmode = 'TIMEOUT') then
3153 --
3154 resultout := 'COMPLETE:';
3155 return;
3156 --
3157 end if;
3158 EXCEPTION
3159 when others then
3160 wf_core.context('OKL_CS_WF',
3161 'populate_lease_renew_attrib',
3162 itemtype,
3163 itemkey,
3164 to_char(actid),
3165 funcmode);
3166 RAISE;
3167
3168 END populate_lease_renew_attrib;
3169
3170
3171 PROCEDURE approve_lease_renewal ( itemtype in varchar2,
3172 itemkey in varchar2,
3173 actid in number,
3174 funcmode in varchar2,
3175 resultout out nocopy varchar2)
3176 AS
3177 BEGIN
3178 --Dummy API which alawys approves the request.
3179
3180 if (funcmode = 'RUN') then
3181 --
3182 resultout := 'COMPLETE:APPROVED';
3183 RETURN ;
3184
3185 end if;
3186 --
3187 -- CANCEL mode
3188 --
3189 if (funcmode = 'CANCEL') then
3190 --
3191 resultout := 'COMPLETE:REJECTED';
3192 return;
3193 --
3194 end if;
3195 --
3196 -- TIMEOUT mode
3197 --
3198 if (funcmode = 'TIMEOUT') then
3199 --
3200 resultout := 'COMPLETE:REJECTED';
3201 return;
3202 --
3203 end if;
3204 EXCEPTION
3205 when others then
3206 wf_core.context('OKL_CS_WF',
3207 'approve_lease_renewal',
3208 itemtype,
3209 itemkey,
3210 to_char(actid),
3211 funcmode);
3212 RAISE;
3213
3214 END approve_lease_renewal;
3215
3216
3217 PROCEDURE post_notify_lease_renewal(itemtype in varchar2,
3218 itemkey in varchar2,
3219 actid in number,
3220 funcmode in varchar2,
3221 resultout out nocopy varchar2)
3222 AS
3223
3224 l_request_id NUMBER;
3225 l_nid NUMBER;
3226 BEGIN
3227
3228 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
3229 itemkey => itemkey,
3230 aname => 'REQUEST_ID');
3231
3232 IF (funcmode = 'RESPOND') THEN
3233 --get notification id from wf_engine context
3234 l_nid := WF_ENGINE.CONTEXT_NID;
3235 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
3236
3237 resultout := 'COMPLETE:'|| l_ntf_result;
3238
3239 --Run Mode
3240 ELSIF funcmode = 'RUN' THEN
3241 resultout := 'COMPLETE:'|| l_ntf_result;
3242
3243 -- CANCEL mode
3244 ELSIF (funcmode = 'CANCEL') THEN
3245 resultout := 'COMPLETE:';
3246
3247 -- TIMEOUT mode
3248 ELSIF (funcmode = 'TIMEOUT') THEN
3249 resultout := 'COMPLETE:';
3250
3251 END IF;
3252
3253 EXCEPTION
3254 WHEN OTHERS THEN
3255 wf_core.context('OKL_CS_WF',
3256 'post_notify_lease_renewal',
3257 itemtype,
3258 itemkey,
3259 to_char(actid),
3260 funcmode);
3261 RAISE;
3262 END post_notify_lease_renewal;
3263
3264
3265
3266 PROCEDURE post_reject_lease_renewal(itemtype in varchar2,
3267 itemkey in varchar2,
3268 actid in number,
3269 funcmode in varchar2,
3270 resultout out nocopy varchar2)
3271 AS
3272
3273 l_request_id NUMBER;
3274 l_nid NUMBER;
3275 BEGIN
3276
3277 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
3278 itemkey => itemkey,
3279 aname => 'REQUEST_ID');
3280
3281 IF (funcmode = 'RESPOND') THEN
3282 --get notification id from wf_engine context
3283 l_nid := WF_ENGINE.CONTEXT_NID;
3284 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
3285
3286 resultout := 'COMPLETE:'|| l_ntf_result;
3287
3288 --Run Mode
3289 ELSIF funcmode = 'RUN' THEN
3290 resultout := 'COMPLETE:'|| l_ntf_result;
3291
3292 -- CANCEL mode
3293 ELSIF (funcmode = 'CANCEL') THEN
3294 resultout := 'COMPLETE:';
3295
3296 -- TIMEOUT mode
3297 ELSIF (funcmode = 'TIMEOUT') THEN
3298 resultout := 'COMPLETE:';
3299
3300 END IF;
3301
3302 EXCEPTION
3303 WHEN OTHERS THEN
3304 wf_core.context('OKL_CS_WF',
3305 'post_reject_lease_renewal',
3306 itemtype,
3307 itemkey,
3308 to_char(actid),
3309 funcmode);
3310 RAISE;
3311 END post_reject_lease_renewal;
3312
3313
3314
3315 --Principal Paydown Work flow APIs
3316
3317 PROCEDURE raise_principal_paydown_event(p_request_id IN NUMBER)
3318 AS
3319 l_parameter_list wf_parameter_list_t;
3320 l_key varchar2(240);
3321 l_event_name varchar2(240) := 'oracle.apps.okl.cs.principalpaydown';
3322 l_seq NUMBER;
3323 CURSOR okl_key_csr IS
3324 SELECT okl_wf_item_s.nextval
3325 FROM dual;
3326
3327
3328 BEGIN
3329
3330 SAVEPOINT raise_principal_paydown_event;
3331
3332 OPEN okl_key_csr;
3333 FETCH okl_key_csr INTO l_seq;
3334 CLOSE okl_key_csr;
3335 l_key := l_event_name ||l_seq ;
3336
3337 wf_event.AddParameterToList('REQUEST_ID',p_request_id,l_parameter_list);
3338 --added by akrangan
3339 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
3340
3341 -- Raise Event
3342 wf_event.raise(p_event_name => l_event_name
3343 ,p_event_key => l_key
3344 ,p_parameters => l_parameter_list);
3345 l_parameter_list.DELETE;
3346
3347 EXCEPTION
3348 WHEN OTHERS THEN
3349 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
3350 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3351 FND_MSG_PUB.ADD;
3352 ROLLBACK TO raise_principal_paydown_event;
3353 END raise_principal_paydown_event;
3354
3355
3356 PROCEDURE populate_ppd_attrib(itemtype in varchar2,
3357 itemkey in varchar2,
3358 actid in number,
3359 funcmode in varchar2,
3360 resultout out nocopy varchar2)
3361 AS
3362 l_principal_paydown_role VARCHAR2(50) ;
3363 l_request_id NUMBER;
3364 l_yield NUMBER;
3365 l_start_date DATE;
3366 l_end_date DATE;
3367 l_term NUMBER;
3368 l_rent NUMBER;
3369 l_residula_percentage NUMBER;
3370 l_contract_id NUMBER;
3371 l_contract_num VARCHAR2(100);
3372 l_return_status VARCHAR2(100);
3373 l_api_version NUMBER := 1.0;
3374 l_msg_count NUMBER;
3375 l_msg_data VARCHAR2(2000);
3376 l_cur_prin_balance NUMBER;
3377 l_new_prin_balance NUMBER;
3378 l_principal_balance NUMBER;
3379 l_acc_int NUMBER;
3380
3381
3382 CURSOR c_req_record(p_id IN NUMBER) IS
3383 SELECT *
3384 FROM OKL_TRX_REQUESTS
3385 WHERE ID = p_id;
3386
3387 l_req_rec c_req_record%ROWTYPE;
3388
3389 CURSOR c_ctr_no(p_ctr_id IN NUMBER) IS
3390 SELECT contract_number
3391 FROM OKC_K_HEADERS_V
3392 WHERE id=p_ctr_id;
3393 BEGIN
3394
3395 if (funcmode = 'RUN') then
3396 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3397 itemkey => itemkey,
3398 aname => 'REQUEST_ID');
3399 OPEN c_req_record(l_request_id);
3400 FETCH c_req_record INTO l_req_rec;
3401 CLOSE c_req_record;
3402
3403
3404 l_contract_id := l_req_rec.dnz_khr_id;
3405
3406
3407 OPEN c_ctr_no(l_contract_id);
3408 FETCH c_ctr_no INTO l_contract_num;
3409 CLOSE c_ctr_no;
3410
3411 --rkuttiya added for bug:2923037
3412 l_principal_paydown_role := fnd_profile.value('OKL_CTR_RESTRUCTURE_REP');
3413 IF l_principal_paydown_role IS NULL THEN
3414 l_principal_paydown_role := 'SYSADMIN';
3415 END IF;
3416 /*
3417 OKL_STREAM_GENERATOR_PVT.get_sched_principal_bal(
3418 p_api_version => l_api_version,
3419 p_init_msg_list => 'T',
3420 p_khr_id => l_old_contract_id,
3421 p_kle_id => NULL,
3422 p_date => NVL(l_req_rec.payment_date,sysdate),
3423 x_principal_balance => l_principal_balance,
3424 x_accumulated_int => l_acc_int,
3425 x_return_status => l_return_status,
3426 x_msg_count => l_msg_count,
3427 x_msg_data => l_msg_data);
3428 l_cur_prin_balance := l_principal_balance + l_acc_int;
3429
3430 l_new_prin_balance := (nvl(l_cur_prin_balance,0) - nvl(l_req_rec.payment_amount,0));
3431 */
3432
3433 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3434 itemkey => itemkey,
3435 aname => 'ROLE_TO_EXECUTE_PPD',
3436 avalue => l_principal_paydown_role);
3437 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3438 itemkey => itemkey,
3439 aname => 'KHR_ID',
3440 avalue => l_contract_id);
3441 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3442 itemkey => itemkey,
3443 aname => 'PAYDOWN_AMOUNT',
3444 avalue => l_req_rec.payment_amount);
3445 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3446 itemkey => itemkey,
3447 aname => 'REQUEST_NUMBER',
3448 avalue => l_req_rec.request_number);
3449 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3450 itemkey => itemkey,
3451 aname => 'CONTRACT_NUMBER',
3452 avalue => l_contract_num);
3453 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3454 itemkey => itemkey,
3455 aname => 'NEW_PAYMENT_AMOUNT',
3456 avalue => l_req_rec.amount);
3457
3458 --Next Payment Date
3459 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3460 itemkey => itemkey,
3461 aname => 'NEXT_PAYMENT_DATE',
3462 avalue => l_req_rec.start_date);
3463 --Paydown Date
3464 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3465 itemkey => itemkey,
3466 aname => 'PAYDOWN_DATE',
3467 avalue => l_req_rec.payment_date);
3468
3469 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3470 itemkey => itemkey,
3471 aname => 'CURRENT_PRIN_BALANCE',
3472 avalue => l_cur_prin_balance);
3473
3474 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3475 itemkey => itemkey,
3476 aname => 'NEW_PRIN_BALANCE',
3477 avalue => l_new_prin_balance);
3478
3479 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3480 itemkey => itemkey,
3481 aname => 'CURRENCY_CODE',
3482 avalue => l_req_rec.currency_code);
3483
3484 resultout := 'COMPLETE:';
3485 RETURN ;
3486
3487 end if;
3488 --
3489 -- CANCEL mode
3490 --
3491 if (funcmode = 'CANCEL') then
3492 --
3493 resultout := 'COMPLETE:';
3494 return;
3495 --
3496 end if;
3497 --
3498 -- TIMEOUT mode
3499 --
3500 if (funcmode = 'TIMEOUT') then
3501 --
3502 resultout := 'COMPLETE:';
3503 return;
3504 --
3505 end if;
3506 EXCEPTION
3507 when others then
3508 wf_core.context('OKL_CS_WF',
3509 'populate_ppd_attrib',
3510 itemtype,
3511 itemkey,
3512 to_char(actid),
3513 funcmode);
3514 RAISE;
3515
3516 END populate_ppd_attrib;
3517
3518 PROCEDURE post_notify_ppd(itemtype in varchar2,
3519 itemkey in varchar2,
3520 actid in number,
3521 funcmode in varchar2,
3522 resultout out nocopy varchar2)
3523 AS
3524
3525 l_request_id NUMBER;
3526 l_nid NUMBER;
3527 BEGIN
3528
3529 l_request_id := wf_engine.GetItemAttrText(itemtype => itemtype,
3530 itemkey => itemkey,
3531 aname => 'REQUEST_ID');
3532
3533 IF (funcmode = 'RESPOND') THEN
3534 --get notification id from wf_engine context
3535 l_nid := WF_ENGINE.CONTEXT_NID;
3536 l_ntf_result := wf_notification.GetAttrText(l_nid,'RESULT');
3537
3538 resultout := 'COMPLETE:'|| l_ntf_result;
3539
3540 --Run Mode
3541 ELSIF funcmode = 'RUN' THEN
3542 resultout := 'COMPLETE:'|| l_ntf_result;
3543
3544 -- CANCEL mode
3545 ELSIF (funcmode = 'CANCEL') THEN
3546 resultout := 'COMPLETE:';
3547
3548 -- TIMEOUT mode
3549 ELSIF (funcmode = 'TIMEOUT') THEN
3550 resultout := 'COMPLETE:';
3551
3552 END IF;
3553
3554 EXCEPTION
3555 WHEN OTHERS THEN
3556 wf_core.context('OKL_CS_WF',
3557 'post_notify_principal_paydown',
3558 itemtype,
3559 itemkey,
3560 to_char(actid),
3561 funcmode);
3562 RAISE;
3563 END post_notify_ppd;
3564
3565 --Added the following APIs as part of 11.5.10+
3566 PROCEDURE invoice_bill_apply(itemtype in varchar2,
3567 itemkey in varchar2,
3568 actid in number,
3569 funcmode in varchar2,
3570 resultout out nocopy varchar2)
3571 IS
3572
3573 l_return_status VARCHAR2(100) := 'S';
3574 l_api_version NUMBER := 1.0;
3575 l_msg_count NUMBER;
3576 l_msg_data VARCHAR2(2000);
3577 l_error VARCHAR2(2000);
3578
3579 l_khr_id NUMBER;
3580 l_request_id NUMBER;
3581 BEGIN
3582
3583 if (funcmode = 'RUN') then
3584
3585 l_khr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3586 itemkey => itemkey,
3587 aname => 'KHR_ID');
3588
3589 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3590 itemkey => itemkey,
3591 aname => 'REQUEST_ID');
3592
3593 --call the API here.
3594 okl_cs_principal_paydown_pvt.invoice_bill_apply
3595 (p_api_version => l_api_version,
3596 p_init_msg_list => fnd_api.g_false,
3597 x_return_status => l_return_status,
3598 x_msg_count => l_msg_count,
3599 x_msg_data => l_msg_data,
3600 p_khr_id => l_khr_id,
3601 p_req_id => l_request_id);
3602
3603 IF l_return_status <> 'S' THEN
3604 FND_MSG_PUB.Count_And_Get
3605 ( p_count => l_msg_count,
3606 p_data => l_msg_data
3607 );
3608 Get_Messages(l_msg_count,l_error);
3609
3610 wf_engine.SetItemAttrText(itemtype => itemtype,
3611 itemkey => itemkey,
3612 aname => 'ERROR_MESSAGE',
3613 avalue => l_error);
3614
3615 resultout := 'COMPLETE:N';
3616 ELSE
3617 resultout := 'COMPLETE:Y';
3618 END IF;
3619 RETURN ;
3620
3621 end if;
3622 --
3623 -- CANCEL mode
3624 --
3625 if (funcmode = 'CANCEL') then
3626 --
3627 resultout := 'COMPLETE:N';
3628
3629 --
3630 end if;
3631 --
3632 -- TIMEOUT mode
3633 --
3634 if (funcmode = 'TIMEOUT') then
3635 --
3636 resultout := 'COMPLETE:Y';
3637 return ;
3638 --
3639 end if;
3640 EXCEPTION
3641 when G_EXCEPTION then
3642 wf_core.context('OKL_CS_WF',
3643 'invoice_bill_apply',
3644 itemtype,
3645 itemkey,
3646 to_char(actid),
3647 funcmode);
3648 RAISE;
3649
3650 when others then
3651 wf_core.context('OKL_CS_WF',
3652 'invoice_bill_apply',
3653 itemtype,
3654 itemkey,
3655 to_char(actid),
3656 funcmode);
3657 RAISE;
3658
3659 END invoice_bill_apply;
3660
3661
3662 PROCEDURE update_ppd_processed_status(itemtype in varchar2,
3663 itemkey in varchar2,
3664 actid in number,
3665 funcmode in varchar2,
3666 resultout out nocopy varchar2)
3667 AS
3668 l_request_id NUMBER ;
3669 l_return_status VARCHAR2(100) := 'S';
3670 l_api_version NUMBER := 1.0;
3671 l_msg_count NUMBER;
3672 l_msg_data VARCHAR2(2000);
3673 l_error VARCHAR2(2000);
3674 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
3675 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
3676
3677 CURSOR c_obj_vers_csr (a_id NUMBER)
3678 IS
3679 SELECT object_Version_number
3680 FROM okl_trx_requests
3681 WHERE id=a_id;
3682
3683
3684 BEGIN
3685
3686 if (funcmode = 'RUN') then
3687
3688 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3689 itemkey => itemkey,
3690 aname => 'REQUEST_ID');
3691 l_trqv_rec.id := l_request_id;
3692 l_trqv_rec.request_status_code := 'PROCESSED';
3693
3694 OPEN c_obj_vers_csr(l_request_id);
3695 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
3696 CLOSE c_obj_vers_csr;
3697
3698 -- Call the public API for updation here.
3699 okl_trx_requests_pub.update_trx_requests(
3700 p_api_version => l_api_version,
3701 p_init_msg_list => fnd_api.g_false,
3702 x_return_status => l_return_status,
3703 x_msg_count => l_msg_count,
3704 x_msg_data => l_msg_data,
3705 p_trqv_rec => l_trqv_rec,
3706 x_trqv_rec => x_trqv_rec);
3707
3708
3709
3710 IF l_return_status <> 'S' THEN
3711 FND_MSG_PUB.Count_And_Get
3712 ( p_count => l_msg_count,
3713 p_data => l_msg_data
3714 );
3715 Get_Messages(l_msg_count,l_error);
3716
3717 wf_engine.SetItemAttrText(itemtype => itemtype,
3718 itemkey => itemkey,
3719 aname => 'ERROR_MESSAGE',
3720 avalue => l_error);
3721
3722 resultout := 'COMPLETE:N';
3723 ELSE
3724 resultout := 'COMPLETE:Y';
3725 END IF;
3726 RETURN ;
3727
3728 end if;
3729 --
3730 -- CANCEL mode
3731 --
3732 if (funcmode = 'CANCEL') then
3733 --
3734 resultout := 'COMPLETE:N';
3735
3736 --
3737 end if;
3738 --
3739 -- TIMEOUT mode
3740 --
3741 if (funcmode = 'TIMEOUT') then
3742 --
3743 resultout := 'COMPLETE:Y';
3744 return ;
3745 --
3746 end if;
3747 EXCEPTION
3748 when G_EXCEPTION then
3749 wf_core.context('OKL_CS_WF',
3750 'update_ppd_processed_status',
3751 itemtype,
3752 itemkey,
3753 to_char(actid),
3754 funcmode);
3755 RAISE;
3756
3757 when others then
3758 wf_core.context('OKL_CS_WF',
3759 'update_ppd_processed_status',
3760 itemtype,
3761 itemkey,
3762 to_char(actid),
3763 funcmode);
3764 RAISE;
3765
3766 END update_ppd_processed_status;
3767
3768
3769 PROCEDURE raise_credit_memo_event(p_request_id IN NUMBER)
3770 AS
3771 l_parameter_list wf_parameter_list_t;
3772 l_key varchar2(240);
3773 l_event_name varchar2(240) := 'oracle.apps.okl.cs.issuecreditmemo';
3774 l_seq NUMBER;
3775 CURSOR okl_key_csr IS
3776 SELECT okl_wf_item_s.nextval
3777 FROM dual;
3778
3779
3780 BEGIN
3781
3782 SAVEPOINT raise_credit_memo_event;
3783
3784 OPEN okl_key_csr;
3785 FETCH okl_key_csr INTO l_seq;
3786 CLOSE okl_key_csr;
3787 l_key := l_event_name ||l_seq ;
3788
3789 wf_event.AddParameterToList('REQUEST_ID',p_request_id,l_parameter_list);
3790 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list); --dkagrawa added for MOAC
3791 -- Set the User Id, Responsibility Id and Application Id as workflow attributes Bug#5743303
3792 wf_event.AddParameterToList('USER_ID',Fnd_Global.User_Id,l_parameter_list);
3793 wf_event.AddParameterToList('RESPONSIBILITY_ID',Fnd_Global.Resp_Id,l_parameter_list);
3794 wf_event.AddParameterToList('APPLICATION_ID',Fnd_Global.Resp_Appl_Id,l_parameter_list);
3795
3796 -- Raise Event
3797 wf_event.raise(p_event_name => l_event_name
3798 ,p_event_key => l_key
3799 ,p_parameters => l_parameter_list);
3800 l_parameter_list.DELETE;
3801
3802 EXCEPTION
3803 WHEN OTHERS THEN
3804 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
3805 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
3806 FND_MSG_PUB.ADD;
3807 ROLLBACK TO raise_credit_memo_event;
3808 END raise_credit_memo_event;
3809
3810
3811
3812 PROCEDURE populate_credit_memo_attribs(itemtype in varchar2,
3813 itemkey in varchar2,
3814 actid in number,
3815 funcmode in varchar2,
3816 resultout out nocopy varchar2)
3817 AS
3818 l_approve_credit_memo_role VARCHAR2(50) ;
3819 l_request_id NUMBER;
3820 l_contract_id NUMBER;
3821 l_contract_num VARCHAR2(100);
3822
3823 CURSOR c_req_record(p_id IN NUMBER) IS
3824 SELECT *
3825 FROM OKL_TRX_REQUESTS
3826 WHERE ID = p_id;
3827
3828 l_req_rec c_req_record%ROWTYPE;
3829
3830 CURSOR c_ctr_no(p_ctr_id IN NUMBER) IS
3831 SELECT contract_number
3832 FROM OKC_K_HEADERS_V
3833 WHERE id=p_ctr_id;
3834 BEGIN
3835
3836 if (funcmode = 'RUN') then
3837 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3838 itemkey => itemkey,
3839 aname => 'REQUEST_ID');
3840 OPEN c_req_record(l_request_id);
3841 FETCH c_req_record INTO l_req_rec;
3842 CLOSE c_req_record;
3843
3844 l_contract_id := l_req_rec.dnz_khr_id;
3845
3846 OPEN c_ctr_no(l_contract_id);
3847 FETCH c_ctr_no INTO l_contract_num;
3848 CLOSE c_ctr_no;
3849
3850 --rkuttiya added for bug:2923037
3851 l_approve_credit_memo_role := fnd_profile.value('OKL_CREDIT_MEMO_REP');
3852 IF l_approve_credit_memo_role IS NULL THEN
3853 l_approve_credit_memo_role := 'SYSADMIN';
3854 END IF;
3855 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3856 itemkey => itemkey,
3857 aname => 'NOTIFICATION_ROLE',
3858 avalue => l_approve_credit_memo_role);
3859 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3860 itemkey => itemkey,
3861 aname => 'CONTRACT_ID',
3862 avalue => l_contract_id);
3863 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3864 itemkey => itemkey,
3865 aname => 'CREDIT_AMOUNT',
3866 avalue => l_req_rec.amount);
3867 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3868 itemkey => itemkey,
3869 aname => 'REQUEST_NUMBER',
3870 avalue => l_req_rec.request_number);
3871 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3872 itemkey => itemkey,
3873 aname => 'CONTRACT_NUMBER',
3874 avalue => l_contract_num);
3875 wf_engine.SetItemAttrText ( itemtype=> itemtype,
3876 itemkey => itemkey,
3877 aname => 'LSM_ID',
3878 avalue => l_req_rec.lsm_id);
3879 resultout := 'COMPLETE:Y';
3880 RETURN ;
3881
3882 end if;
3883 --
3884 -- CANCEL mode
3885 --
3886 if (funcmode = 'CANCEL') then
3887 --
3888 resultout := 'COMPLETE:N';
3889 return;
3890 --
3891 end if;
3892 -- TIMEOUT mode
3893 --
3894 if (funcmode = 'TIMEOUT') then
3895 --
3896 resultout := 'COMPLETE:Y';
3897 return;
3898 --
3899 end if;
3900 EXCEPTION
3901 when others then
3902 wf_core.context('OKL_CS_WF',
3903 'populate_credit_memo_attribs',
3904 itemtype,
3905 itemkey,
3906 to_char(actid),
3907 funcmode);
3908 RAISE;
3909
3910 END populate_credit_memo_attribs;
3911
3912
3913 PROCEDURE create_credit_memo_invoice(itemtype in varchar2,
3914 itemkey in varchar2,
3915 actid in number,
3916 funcmode in varchar2,
3917 resultout out nocopy varchar2)
3918 IS
3919
3920 l_dummy varchar(1) ;
3921 l_lsm_id NUMBER ;
3922 l_credit_amount NUMBER ;
3923 lx_tai_id NUMBER ;
3924 l_return_status VARCHAR2(100);
3925 l_api_version NUMBER := 1.0;
3926 l_msg_count NUMBER;
3927 l_msg_data VARCHAR2(2000);
3928 l_error VARCHAR2(2000);
3929 l_sty_id NUMBER;
3930 l_transaction_source VARCHAR2(100); -- vpanwar for bug no 6334774
3931
3932 CURSOR get_sty_id(c_lsm_id IN NUMBER)
3933 IS
3934 SELECT sty_id
3935 FROM OKL_CNSLD_AR_STRMS_B
3936 WHERE id=c_lsm_id;
3937
3938 BEGIN
3939
3940 if (funcmode = 'RUN') then
3941 l_lsm_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3942 itemkey => itemkey,
3943 aname => 'LSM_ID');
3944
3945 l_credit_amount := wf_engine.GetItemAttrText( itemtype => itemtype,
3946 itemkey => itemkey,
3947 aname => 'CREDIT_AMOUNT');
3948 --We need to send -ve amount to the API so negating the amount.
3949 l_credit_amount := -(l_credit_amount);
3950
3951 OPEN get_sty_id(l_lsm_id);
3952 FETCH get_sty_id INTO l_sty_id;
3953 CLOSE get_sty_id;
3954
3955 l_transaction_source := 'LEASE_CENTER'; -- vpanwar for bug no 6334774
3956
3957
3958 okl_credit_memo_pub.insert_request(p_api_version => l_api_version,
3959 p_init_msg_list => fnd_api.g_false,
3960 -- p_lsm_id => l_lsm_id,
3961 p_tld_id => l_lsm_id,
3962 p_credit_amount => l_credit_amount,
3963 p_credit_sty_id => l_sty_id,
3964 p_transaction_source => l_transaction_source, -- vpanwar for bug no 6334774
3965 x_tai_id => lx_tai_id,
3966 x_return_status => l_return_status,
3967 x_msg_count => l_msg_count,
3968 x_msg_data => l_msg_data);
3969
3970 --I think if the api is not a success we should log the error in a
3971 --table.
3972
3973 IF l_return_status <> 'S' THEN
3974 FND_MSG_PUB.Count_And_Get
3975 ( p_count => l_msg_count,
3976 p_data => l_msg_data
3977 );
3978 Get_Messages(l_msg_count,l_error);
3979
3980 wf_engine.SetItemAttrText(itemtype => itemtype,
3981 itemkey => itemkey,
3982 aname => 'ERROR_MESSAGE',
3983 avalue => l_error);
3984
3985 resultout := 'COMPLETE:N';
3986
3987 ELSE
3988
3989 wf_engine.SetItemAttrText (itemtype=> itemtype,
3990 itemkey => itemkey,
3991 aname => 'TAI_ID',
3992 avalue => lx_tai_id);
3993
3994 resultout := 'COMPLETE:Y';
3995 END IF;
3996 RETURN ;
3997
3998 end if;
3999 --
4000 -- CANCEL mode
4001 --
4002 if (funcmode = 'CANCEL') then
4003 --
4004 resultout := 'COMPLETE:N';
4005
4006 --
4007 end if;
4008 --
4009 -- TIMEOUT mode
4010 --
4011 if (funcmode = 'TIMEOUT') then
4012 --
4013 resultout := 'COMPLETE:Y';
4014 return ;
4015 --
4016 end if;
4017 EXCEPTION
4018 when others then
4019 wf_core.context('OKL_CS_WF',
4020 'create_credit_memo_invoice',
4021 itemtype,
4022 itemkey,
4023 to_char(actid),
4024 funcmode);
4025 RAISE;
4026
4027 END create_credit_memo_invoice;
4028
4029 PROCEDURE update_crm_approved_status(itemtype in varchar2,
4030 itemkey in varchar2,
4031 actid in number,
4032 funcmode in varchar2,
4033 resultout out nocopy varchar2)
4034 AS
4035 l_request_id NUMBER ;
4036 l_return_status VARCHAR2(100);
4037 l_api_version NUMBER := 1.0;
4038 l_msg_count NUMBER;
4039 l_msg_data VARCHAR2(2000);
4040 l_error VARCHAR2(2000);
4041 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4042 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4043
4044 CURSOR c_obj_vers_csr (a_id NUMBER)
4045 IS
4046 SELECT object_Version_number
4047 FROM okl_trx_requests
4048 WHERE id=a_id;
4049
4050
4051 BEGIN
4052
4053 if (funcmode = 'RUN') then
4054
4055 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
4056 itemkey => itemkey,
4057 aname => 'REQUEST_ID');
4058 l_trqv_rec.id := l_request_id;
4059 l_trqv_rec.request_status_code := 'APPROVED';
4060
4061 OPEN c_obj_vers_csr(l_request_id);
4062 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
4063 CLOSE c_obj_vers_csr;
4064
4065 -- Call the public API for updation here.
4066 okl_trx_requests_pub.update_trx_requests(
4067 p_api_version => l_api_version,
4068 p_init_msg_list =>fnd_api.g_false,
4069 x_return_status => l_return_status,
4070 x_msg_count => l_msg_count,
4071 x_msg_data => l_msg_data,
4072 p_trqv_rec => l_trqv_rec,
4073 x_trqv_rec => x_trqv_rec);
4074
4075
4076
4077 IF l_return_status <> 'S' THEN
4078 FND_MSG_PUB.Count_And_Get
4079 ( p_count => l_msg_count,
4080 p_data => l_msg_data
4081 );
4082 Get_Messages(l_msg_count,l_error);
4083
4084 wf_engine.SetItemAttrText(itemtype => itemtype,
4085 itemkey => itemkey,
4086 aname => 'ERROR_MESSAGE',
4087 avalue => l_error);
4088
4089 resultout := 'COMPLETE:N';
4090
4091 ELSE
4092 resultout := 'COMPLETE:Y';
4093 END IF;
4094 RETURN ;
4095
4096 end if;
4097 --
4098 -- CANCEL mode
4099 --
4100 if (funcmode = 'CANCEL') then
4101 --
4102 resultout := 'COMPLETE:N';
4103
4104 --
4105 end if;
4106 --
4107 -- TIMEOUT mode
4108 --
4109 if (funcmode = 'TIMEOUT') then
4110 --
4111 resultout := 'COMPLETE:Y';
4112 return ;
4113 --
4114 end if;
4115 EXCEPTION
4116 when others then
4117 wf_core.context('OKL_CS_WF',
4118 'update_crm_approved_status',
4119 itemtype,
4120 itemkey,
4121 to_char(actid),
4122 funcmode);
4123 RAISE;
4124
4125 END update_crm_approved_status;
4126 -------------------------------
4127
4128
4129 PROCEDURE update_crm_rejected_status(itemtype in varchar2,
4130 itemkey in varchar2,
4131 actid in number,
4132 funcmode in varchar2,
4133 resultout out nocopy varchar2)
4134 AS
4135 l_request_id NUMBER ;
4136 l_return_status VARCHAR2(100);
4137 l_api_version NUMBER := 1.0;
4138 l_msg_count NUMBER;
4139 l_msg_data VARCHAR2(2000);
4140 l_error VARCHAR2(2000);
4141 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4142 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4143
4144 CURSOR c_obj_vers_csr (a_id NUMBER)
4145 IS
4146 SELECT object_Version_number
4147 FROM okl_trx_requests
4148 WHERE id=a_id;
4149
4150
4151 BEGIN
4152
4153 if (funcmode = 'RUN') then
4154
4155 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
4156 itemkey => itemkey,
4157 aname => 'REQUEST_ID');
4158 l_trqv_rec.id := l_request_id;
4159 l_trqv_rec.request_status_code := 'REJECTED';
4160
4161 OPEN c_obj_vers_csr(l_request_id);
4162 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
4163 CLOSE c_obj_vers_csr;
4164
4165 -- Call the public API for updation here.
4166 okl_trx_requests_pub.update_trx_requests(
4167 p_api_version => l_api_version,
4168 p_init_msg_list =>fnd_api.g_false,
4169 x_return_status => l_return_status,
4170 x_msg_count => l_msg_count,
4171 x_msg_data => l_msg_data,
4172 p_trqv_rec => l_trqv_rec,
4173 x_trqv_rec => x_trqv_rec);
4174
4175
4176
4177 IF l_return_status <> 'S' THEN
4178 FND_MSG_PUB.Count_And_Get
4179 ( p_count => l_msg_count,
4180 p_data => l_msg_data
4181 );
4182 Get_Messages(l_msg_count,l_error);
4183
4184 wf_engine.SetItemAttrText(itemtype => itemtype,
4185 itemkey => itemkey,
4186 aname => 'ERROR_MESSAGE',
4187 avalue => l_error);
4188
4189 resultout := 'COMPLETE:N';
4190
4191 ELSE
4192 resultout := 'COMPLETE:Y';
4193 END IF;
4194 RETURN ;
4195
4196 end if;
4197 --
4198 -- CANCEL mode
4199 --
4200 if (funcmode = 'CANCEL') then
4201 --
4202 resultout := 'COMPLETE:N';
4203
4204 --
4205 end if;
4206 --
4207 -- TIMEOUT mode
4208 --
4209 if (funcmode = 'TIMEOUT') then
4210 --
4211 resultout := 'COMPLETE:Y';
4212 return ;
4213 --
4214 end if;
4215 EXCEPTION
4216 when others then
4217 wf_core.context('OKL_CS_WF',
4218 'update_crm_rejected_status',
4219 itemtype,
4220 itemkey,
4221 to_char(actid),
4222 funcmode);
4223 RAISE;
4224
4225 END update_crm_rejected_status;
4226 -------------------------------
4227
4228
4229
4230 PROCEDURE update_crm_success_status(itemtype in varchar2,
4231 itemkey in varchar2,
4232 actid in number,
4233 funcmode in varchar2,
4234 resultout out nocopy varchar2)
4235 AS
4236
4237 l_tai_id NUMBER ;
4238 l_request_id NUMBER ;
4239 l_return_status VARCHAR2(100);
4240 l_api_version NUMBER := 1.0;
4241 l_msg_count NUMBER;
4242 l_msg_data VARCHAR2(2000);
4243 l_error VARCHAR2(2000);
4244 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4245 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4246
4247 CURSOR c_obj_vers_csr (a_id NUMBER)
4248 IS
4249 SELECT object_Version_number
4250 FROM okl_trx_requests
4251 WHERE id=a_id;
4252
4253
4254 BEGIN
4255
4256 if (funcmode = 'RUN') then
4257 l_tai_id := wf_engine.GetItemAttrText( itemtype => itemtype,
4258 itemkey => itemkey,
4259 aname => 'TAI_ID');
4260
4261 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
4262 itemkey => itemkey,
4263 aname => 'REQUEST_ID');
4264 l_trqv_rec.id := l_request_id;
4265 l_trqv_rec.object1_id1 := l_tai_id;
4266 l_trqv_rec.jtot_object1_code := 'OKL_TRX_AR_INVOICES_B';
4267 l_trqv_rec.request_status_code := 'ENTERED';
4268
4269 OPEN c_obj_vers_csr(l_request_id);
4270 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
4271 CLOSE c_obj_vers_csr;
4272
4273 -- Call the public API for updation here.
4274 okl_trx_requests_pub.update_trx_requests(
4275 p_api_version => l_api_version,
4276 p_init_msg_list =>fnd_api.g_false,
4277 x_return_status => l_return_status,
4278 x_msg_count => l_msg_count,
4279 x_msg_data => l_msg_data,
4280 p_trqv_rec => l_trqv_rec,
4281 x_trqv_rec => x_trqv_rec);
4282
4283
4284
4285 IF l_return_status <> 'S' THEN
4286 FND_MSG_PUB.Count_And_Get
4287 ( p_count => l_msg_count,
4288 p_data => l_msg_data
4289 );
4290 Get_Messages(l_msg_count,l_error);
4291
4292 wf_engine.SetItemAttrText(itemtype => itemtype,
4293 itemkey => itemkey,
4294 aname => 'ERROR_MESSAGE',
4295 avalue => l_error);
4296
4297 resultout := 'COMPLETE:N';
4298
4299 ELSE
4300 resultout := 'COMPLETE:Y';
4301 END IF;
4302 RETURN ;
4303
4304 end if;
4305 --
4306 -- CANCEL mode
4307 --
4308 if (funcmode = 'CANCEL') then
4309 --
4310 resultout := 'COMPLETE:N';
4311
4312 --
4313 end if;
4314 --
4315 -- TIMEOUT mode
4316 --
4317 if (funcmode = 'TIMEOUT') then
4318 --
4319 resultout := 'COMPLETE:Y';
4320 return ;
4321 --
4322 end if;
4323 EXCEPTION
4324 when others then
4325 wf_core.context('OKL_CS_WF',
4326 'update_crm_success_status',
4327 itemtype,
4328 itemkey,
4329 to_char(actid),
4330 funcmode);
4331 RAISE;
4332
4333 END update_crm_success_status;
4334
4335 -------------------------------
4336 PROCEDURE update_crm_error_status(itemtype in varchar2,
4337 itemkey in varchar2,
4338 actid in number,
4339 funcmode in varchar2,
4340 resultout out nocopy varchar2)
4341 AS
4342 l_request_id NUMBER ;
4343 l_return_status VARCHAR2(100);
4344 l_api_version NUMBER := 1.0;
4345 l_msg_count NUMBER;
4346 l_msg_data VARCHAR2(2000);
4347 l_error VARCHAR2(2000);
4348 l_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4349 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
4350
4351 CURSOR c_obj_vers_csr (a_id NUMBER)
4352 IS
4353 SELECT object_Version_number
4354 FROM okl_trx_requests
4355 WHERE id=a_id;
4356
4357
4358 BEGIN
4359
4360 if (funcmode = 'RUN') then
4361
4362 l_request_id := wf_engine.GetItemAttrText( itemtype => itemtype,
4363 itemkey => itemkey,
4364 aname => 'REQUEST_ID');
4365 l_trqv_rec.id := l_request_id;
4366 l_trqv_rec.request_status_code := 'INCOMPLETE'; --Should this be error instead
4367
4368 OPEN c_obj_vers_csr(l_request_id);
4369 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
4370 CLOSE c_obj_vers_csr;
4371
4372 -- Call the public API for updation here.
4373 okl_trx_requests_pub.update_trx_requests(
4374 p_api_version => l_api_version,
4375 p_init_msg_list =>fnd_api.g_false,
4376 x_return_status => l_return_status,
4377 x_msg_count => l_msg_count,
4378 x_msg_data => l_msg_data,
4379 p_trqv_rec => l_trqv_rec,
4380 x_trqv_rec => x_trqv_rec);
4381
4382
4383
4384 IF l_return_status <> 'S' THEN
4385 FND_MSG_PUB.Count_And_Get
4386 ( p_count => l_msg_count,
4387 p_data => l_msg_data
4388 );
4389 Get_Messages(l_msg_count,l_error);
4390
4391 wf_engine.SetItemAttrText(itemtype => itemtype,
4392 itemkey => itemkey,
4393 aname => 'ERROR_MESSAGE',
4394 avalue => l_error);
4395
4396 resultout := 'COMPLETE:N';
4397
4398 ELSE
4399 resultout := 'COMPLETE:Y';
4400 END IF;
4401 RETURN ;
4402
4403 end if;
4404 --
4405 -- CANCEL mode
4406 --
4407 if (funcmode = 'CANCEL') then
4408 --
4409 resultout := 'COMPLETE:N';
4410
4411 --
4412 end if;
4413 --
4414 -- TIMEOUT mode
4415 --
4416 if (funcmode = 'TIMEOUT') then
4417 --
4418 resultout := 'COMPLETE:Y';
4419 return ;
4420 --
4421 end if;
4422 EXCEPTION
4423 when others then
4424 wf_core.context('OKL_CS_WF',
4425 'update_crm_error_status',
4426 itemtype,
4427 itemkey,
4428 to_char(actid),
4429 funcmode);
4430 RAISE;
4431
4432 END update_crm_error_status;
4433 -------------------------------
4434
4435
4436
4437 END OKL_CS_WF;