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