[Home] [Help]
PACKAGE BODY: APPS.OKL_SSC_WF
Source
1 PACKAGE BODY OKL_SSC_WF as
2 /* $Header: OKLSSWFB.pls 120.21 2007/11/30 13:07:19 rajnisku noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SSC_WF';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
8 L_MODULE VARCHAR2(40) := 'LEASE.SETUP.FUNCTIONS';
9 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
10 L_LEVEL_PROCEDURE NUMBER;
11 IS_DEBUG_PROCEDURE_ON BOOLEAN;
12
13 --
14 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
15 -- directory of SQL Navigator
16 --
17 -- Purpose: Briefly explain the functionality of the package body
18 --
19 -- MODIFICATION HISTORY
20 -- Person Date Comments
21 -- --------- ------ ------------------------------------------
22 -- Enter procedure, function bodies as shown below
23
24 -- START: Sameer Added on 30-Sep-2002 to handle Raise Event call from Asset BC4Js
25 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - Start
26 -- Modified signature to accept parameters of location change
27 -- Set the paramaters required by the workflow and raise event
28 procedure raise_assets_update_event ( p_event_name in varchar2 ,
29 parent_line_id in varchar2,
30 requestorId in varchar2,
31 new_site_id1 in varchar2,
32 new_site_id2 in varchar2,
33 old_site_id1 in varchar2,
34 old_site_id2 in varchar2,
35 trx_date in date
36 )
37 IS
38
39 x_return_status VARCHAR2(1);
40 l_api_version NUMBER:=1.0;
41 x_msg_count NUMBER;
42 x_msg_data VARCHAR2(4000);
43 l_parameter_list wf_parameter_list_t;
44
45 begin
46
47 -- pass the parameters to the event
48 wf_event.addparametertolist('SSCREQUESTORID'
49 ,requestorId
50 ,l_parameter_list);
51 wf_event.addparametertolist('ASSETID'
52 ,parent_line_id
53 ,l_parameter_list);
54
55 wf_event.addparametertolist('NEWSITEID1'
56 ,new_site_id1
57 ,l_parameter_list);
58
59 wf_event.addparametertolist('NEWSITEID2'
60 ,new_site_id2
61 ,l_parameter_list);
62 wf_event.addparametertolist('OLDSITE1'
63 ,old_site_id1
64 ,l_parameter_list);
65 wf_event.addparametertolist('OLDSITE2'
66 ,old_site_id2
67 ,l_parameter_list);
68 wf_event.addparametertolist('TRX_DATE'
69 ,fnd_date.date_to_canonical(trx_date)
70 ,l_parameter_list);
71
72
73 okl_wf_pvt.raise_event(p_api_version => l_api_version
74 ,p_init_msg_list => 'T'
75 ,x_return_status => x_return_status
76 ,x_msg_count => x_msg_count
77 ,x_msg_data => x_msg_data
78 ,p_event_name => p_event_name
79 ,p_parameters => l_parameter_list);
80
81 end raise_assets_update_event;
82 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - End
83 -- END: Sameer Added on 30-Sep-2002 to handle Raise Event call from Asset BC4Js
84
85 -- START: Zhendi added the following procedures
86
87 ----------------------------------ASSET RETURN--------------------------------------------------------
88
89 procedure getAssetReturnMessage (itemtype in varchar2,
90 itemkey in varchar2,
91 actid in number,
92 funcmode in varchar2,
93 resultout out nocopy varchar2 )
94
95 IS
96
97 CURSOR user_info(p_id NUMBER) IS
98 SELECT user_name from fnd_user
99 WHERE user_id = p_id;
100
101
102 CURSOR approver_cur(respKey varchar2) IS
103 SELECT responsibility_id
104 FROM fnd_responsibility
105 WHERE responsibility_key = respKey
106 AND application_id = 540;
107
108
109
110
111
112 requestor_info_rec user_info%rowtype;
113
114
115 p_requestor_id NUMBER;
116 p_resp_key varchar2(30);
117
118 l_requestor_name varchar2(100);
119 l_approver_name varchar2(100);
120 l_respString VARCHAR2(15) := 'FND_RESP540:';
121 l_approver_id varchar2(20);
122 l_doc_attr varchar2(20) := 'SSCRTASTDOC';
123
124
125 BEGIN
126
127 if ( funcmode = 'RUN' ) then
128
129 p_requestor_id:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
130
131 open user_info(p_requestor_id);
132 fetch user_info into requestor_info_rec;
133 l_requestor_name := requestor_info_rec.user_name;
134 close user_info;
135
136 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR', l_requestor_name);
137
138
139 p_resp_key := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY');
140
141 open approver_cur(p_resp_key);
142 fetch approver_cur into l_approver_id;
143 close approver_cur;
144 l_approver_name := l_respString||l_approver_id;
145
146 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCAPPROVER', l_approver_name);
147
148
149
150 --get the global variables transaction id and transaction type
151
152
153 wf_engine.SetItemAttrText (itemtype => itemtype,
154 itemkey => itemkey,
155 aname => l_doc_attr,
156 avalue => 'PLSQLCLOB:OKL_SSC_WF.getAssetReturnDocument/'||
157 itemtype ||':'||itemkey||':&#NID');
158
159
160
161 resultout := 'COMPLETE';
162
163 return;
164 end if;
165
166
167 if ( funcmode = 'CANCEL' ) then
168
169 resultout := 'COMPLETE';
170 return;
171 end if;
172 if ( funcmode = 'RESPOND') then
173 resultout := 'COMPLETE';
174 return;
175 end if;
176 if ( funcmode = 'FORWARD') then
177 resultout := 'COMPLETE';
178 return;
179 end if;
180 if ( funcmode = 'TRANSFER') then
181 resultout := 'COMPLETE';
182 return;
183 end if;
184 if ( funcmode = 'TIMEOUT' ) then
185 resultout := 'COMPLETE';
186 else
187 resultout := wf_engine.eng_timedout;
188 return;
189 end if;
190
191 exception
192 when others then
193 WF_CORE.CONTEXT ('OKL_SSC_WF', 'getAssetReturnMessage', itemtype, itemkey,actid,funcmode);
194 resultout := 'ERROR';
195 raise;
196
197
198
199 end getAssetReturnMessage;
200
201
202 Procedure getAssetReturnDocument
203 ( document_id in varchar2,
204 display_type in varchar2,
205 --document in out varchar2,
206 document in out nocopy clob,
207 document_type in out nocopy varchar2
208 )
209
210 IS
211
212
213
214 CURSOR returned_assets(p_tas_id NUMBER, p_tal_type varchar2) IS
215 select asset_number from okl_txl_assets_b
216 where tas_id = p_tas_id
217 and tal_type = p_tal_type
218 ;
219
220 first_index number;
221 second_index number;
222 third_index number;
223
224 l_tas_id number;
225 l_tal_type varchar2(100);
226 l_itemtype varchar2(100);
227 l_itemkey varchar2(100);
228
229 l_document varchar2(32000);
230 NL VARCHAR2(1) := fnd_global.newline;
231
232
233 BEGIN
234
235 --the document_id is in the form of
236 --'PLSQLCLOB:OKL_SSC_WF.getAssetReturnDocument/itemtyp:itemkey:#NID'
237 --we need to get itemtype and itemkey
238
239 first_index := instr(document_id, '/', 1, 1); --index of the slash '/'
240 second_index := instr(document_id, ':', 1,1); --index of first colon ':'
241 third_index := instr(document_id, ':', 1, 2); --index of the second colon ':'
242
243 l_itemtype := substr(document_id, first_index+1, second_index-first_index-1);
244 l_itemkey := substr(document_id, second_index+1, third_index-second_index-1);
245
246 l_tas_id := to_number(WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTASID'));
247 l_tal_type := WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTALTYPE');
248
249
250 IF (display_type = 'text/html') THEN
251
252 --first generate the header
253 l_document := '<BR>' || NL;
254 l_document := l_document ||
255 '<table cellpadding="3" cellspacing="3" border="3" summary="">' || NL;
256 l_document := l_document ||
257 '<tr<th>Asset Number</th></tr>' || NL;
258 --loop through the record, and generate line by line
259
260
261 FOR returned_assets_rec in returned_assets(l_tas_id, l_tal_type)
262 LOOP
263 l_document := l_document ||
264 '<tr><td>' ||returned_assets_rec.asset_number || '</td></tr>' || NL;
265
266 END LOOP;
267
268 l_document := l_document || '</table>';
269 END IF;-- end to 'text/html' display type
270
271 --document := l_document;
272 wf_notification.WriteToClob( document, l_document);
273
274
275 EXCEPTION
276
277 when others then
278 WF_CORE.CONTEXT ('OKL_SSC_WF', 'getAssetReturnDocument', l_itemtype, l_itemkey);
279 raise;
280
281 END getAssetReturnDocument;
282
283
284 ---------------------------MASK CREDIT CARD------------------------------------------------------------
285 FUNCTION mask_cc
286 ( cc_number IN varchar2)
287 RETURN varchar2 IS
288
289 l_mask_string varchar2(4);
290 masked_cc varchar2(20);
291 l_result varchar2(20);
292 BEGIN
293 l_mask_string := '*';
294 select decode(fnd_profile.value('AR_MASK_BANK_ACCOUNT_NUMBERS'),'F',rpad(substr(cc_number,1,4),length(cc_number),l_mask_string),'L',lpad(substr(cc_number,length(cc_number)-3),length(cc_number),l_mask_string),'N','N')
295 into l_result
296 from dual;
297 if l_result='N' then
298 masked_cc := cc_number;
299 else
300 masked_cc := l_result;
301 end if;
302 RETURN masked_cc ;
303 END;
304 ----------------------------UPDATE SERIAL NUMBERS------------------------------------------------------
305
306 procedure getSerialNumMessage (itemtype in varchar2,
307 itemkey in varchar2,
308 actid in number,
309 funcmode in varchar2,
310 resultout out nocopy varchar2 )
311
312 IS
313
314 CURSOR user_info(p_id NUMBER) IS
315 SELECT user_name from fnd_user
316 WHERE user_id = p_id;
317
318 CURSOR approver_cur(respKey varchar2) IS
319 SELECT responsibility_id
320 FROM fnd_responsibility
321 WHERE responsibility_key = respKey
322 AND application_id = 540;
323
324 requestor_info_rec user_info%rowtype;
325
326 p_requestor_id NUMBER;
327 p_resp_key varchar2(30);
328
329 l_requestor_name varchar2(100);
330 l_approver_name varchar2(100);
331 l_respString VARCHAR2(15) := 'FND_RESP540:';
332 l_approver_id varchar2(20);
333
334 l_doc_attr varchar2(20) := 'SSCUPASNDOC';
335
336
337 BEGIN
338
339 if ( funcmode = 'RUN' ) then
340
341 p_requestor_id:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
342 open user_info(p_requestor_id);
343 fetch user_info into requestor_info_rec;
344 l_requestor_name := requestor_info_rec.user_name;
345 close user_info;
346
347
348
349 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR', l_requestor_name);
350
351 p_resp_key := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY');
352
353 open approver_cur(p_resp_key);
354 fetch approver_cur into l_approver_id;
355 close approver_cur;
356 l_approver_name := l_respString||l_approver_id;
357
358 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCAPPROVER', l_approver_name);
359
360
361 wf_engine.SetItemAttrText (itemtype => itemtype,
362 itemkey => itemkey,
363 aname => l_doc_attr,
364 avalue => 'PLSQLCLOB:OKL_SSC_WF.getSerialNumDocument/'||
365 itemtype ||':'||itemkey||':&#NID');
366
367 resultout := 'COMPLETE';
368
369 return;
370 end if;
371
372
373 if ( funcmode = 'CANCEL' ) then
374
375 resultout := 'COMPLETE';
376 return;
377 end if;
378 if ( funcmode = 'RESPOND') then
379 resultout := 'COMPLETE';
380 return;
381 end if;
382 if ( funcmode = 'FORWARD') then
383 resultout := 'COMPLETE';
384 return;
385 end if;
386 if ( funcmode = 'TRANSFER') then
387 resultout := 'COMPLETE';
388 return;
389 end if;
390 if ( funcmode = 'TIMEOUT' ) then
391 resultout := 'COMPLETE';
392 else
393 resultout := wf_engine.eng_timedout;
394 return;
395 end if;
396
397 exception
398 when others then
399 WF_CORE.CONTEXT ('okl_ssc_wf', 'getSerialNumMessage', itemtype, itemkey,actid,funcmode);
400 resultout := 'ERROR';
401 raise;
402
403
404
405 end getSerialNumMessage;
406
407
408 Procedure getSerialNumDocument
409 ( document_id in varchar2,
410 display_type in varchar2,
411 document in out nocopy clob,
412 document_type in out nocopy varchar2
413 )
414
415 IS
416
417
418
419 CURSOR serial_nums(p_tas_id NUMBER, p_tal_type varchar2) IS
420 SELECT
421 DISTINCT ASX.ASSET_NUMBER,
422 INX.SERIAL_NUMBER
423 FROM OKL_TXL_ITM_INSTS INX, OKL_TXL_ASSETS_B ASX
424 WHERE INX.TAS_ID = p_tas_id
425 AND INX.TAL_TYPE = p_tal_type
426 AND ASX.KLE_ID = INX.DNZ_CLE_ID
427 ;
428
429 first_index number;
430 second_index number;
431 third_index number;
432
433 l_tas_id number;
434 l_tal_type varchar2(100);
435 l_itemtype varchar2(100);
436 l_itemkey varchar2(100);
437
438 l_document varchar2(32000);
439 NL VARCHAR2(1) := fnd_global.newline;
440
441 BEGIN
442
443 --the document_id is in the form of
444 --'PLSQLCLOB:OKL_SSC_WF.getAssetReturnDocument/itemtyp:itemkey:#NID'
445 --we need to get itemtype and itemkey
446
447 first_index := instr(document_id, '/', 1, 1); --index of the slash '/'
448 second_index := instr(document_id, ':', 1,1); --index of first colon ':'
449 third_index := instr(document_id, ':', 1, 2); --index of the second colon ':'
450
451 l_itemtype := substr(document_id, first_index+1, second_index-first_index-1);
452 l_itemkey := substr(document_id, second_index+1, third_index-second_index-1);
453
454 l_tas_id := to_number(WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTASID'));
455 l_tal_type := WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTALTYPE');
456
457
458 IF (display_type = 'text/html') THEN
459
460 --first generate the header
461 l_document := '<BR>' || NL;
462 l_document := l_document ||
463 '<table cellpadding="3" cellspacing="3" border="3" summary="">' || NL;
464 l_document := l_document ||
465 '<tr><th>Asset Number</th><th>Serial Number</th></tr>' || NL;
466 --loop through the record, and generate line by line
467
468
469 FOR serial_nums_rec in serial_nums(l_tas_id, l_tal_type)
470 LOOP
471 l_document := l_document ||
472 '<tr><td>' ||serial_nums_rec.ASSET_NUMBER || '</td>';
473
474 l_document := l_document ||
475 '<td>' ||serial_nums_rec.SERIAL_NUMBER || '</td></tr>' || NL;
476
477 END LOOP;
478
479 l_document := l_document || '</table>';
480 END IF;-- end to 'text/html' display type
481
482 wf_notification.WriteToClob( document, l_document);
483
484
485 EXCEPTION
486
487 when others then
488 WF_CORE.CONTEXT ('OKL_SSC_WF', 'getSerialNumDocument', l_itemtype, l_itemkey);
489 raise;
490
491 END getSerialNumDocument;
492
493
494
495
496 procedure update_serial_fnc (itemtype in varchar2,
497 itemkey in varchar2,
498 actid in number,
499 funcmode in varchar2,
500 resultout out nocopy varchar2 ) is
501
502 x_return_status varchar2(1);
503 x_msg_count number;
504 l_msg_data varchar2(2000);
505 l_tas_id number;
506
507 l_admin VARCHAR2(120) := 'SYSADMIN';
508
509 error_updating_serial_numbers EXCEPTION;
510
511 begin
512
513
514 -- assign variable to attribute
515 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
516
517
518 if ( funcmode = 'RUN' ) then
519
520
521 l_tas_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCTASID'));
522
523 update_serial_number(p_api_version => 1.0,
524 p_init_msg_list => OKC_API.G_FALSE,
525 p_tas_id => l_tas_id,
526 x_return_status => x_return_status,
527 x_msg_count => x_msg_count,
528 x_msg_data => l_msg_data);
529
530 --check the update result
531 IF x_return_status <> 'S' THEN
532 RAISE error_updating_serial_numbers;
533 ELSE
534 resultout := 'COMPLETE';
535 return;
536 END IF;
537
538
539 end if;
540
541
542 if ( funcmode = 'CANCEL' ) then
543 resultout := 'COMPLETE';
544 return;
545 end if;
546 if ( funcmode = 'RESPOND') then
547 resultout := 'COMPLETE';
548 return;
549 end if;
550 if ( funcmode = 'FORWARD') then
551 resultout := 'COMPLETE';
552 return;
553 end if;
554 if ( funcmode = 'TRANSFER') then
555 resultout := 'COMPLETE';
556 return;
557 end if;
558 if ( funcmode = 'TIMEOUT' ) then
559 resultout := 'COMPLETE';
560 else
561 resultout := wf_engine.eng_timedout;
562 return;
563 end if;
564
565 exception
566 when others then
567 WF_CORE.CONTEXT ('okl_ssc_wf', 'update_serial_fnc:'||l_msg_data, itemtype, itemkey,actid,funcmode);
568 resultout := 'ERROR';
569 raise;
570
571 end update_serial_fnc;
572
573
574
575
576 ----------------------------UPDATE ASSET LOCATION------------------------------------------------------
577
578 procedure getLocationMessage (itemtype in varchar2,
579 itemkey in varchar2,
580 actid in number,
581 funcmode in varchar2,
582 resultout out nocopy varchar2 )
583
584 IS
585
586 CURSOR user_info(p_id NUMBER) IS
587 SELECT user_name from fnd_user
588 WHERE user_id = p_id;
589
590 CURSOR approver_cur(respKey varchar2) IS
591 SELECT responsibility_id
592 FROM fnd_responsibility
593 WHERE responsibility_key = respKey
594 AND application_id = 540;
595
596 requestor_info_rec user_info%rowtype;
597 approver_info_rec user_info%rowtype;
598
599 p_requestor_id NUMBER;
600 p_resp_key varchar2(30);
601
602 l_requestor_name varchar2(100);
603 l_approver_name varchar2(100);
604 l_respString VARCHAR2(15) := 'FND_RESP540:';
605 l_approver_id varchar2(20);
606
607 l_doc_attr varchar2(20) := 'SSCUPASLDOC';
608
609 BEGIN
610
611 if ( funcmode = 'RUN' ) then
612
613 p_requestor_id:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
614
615 open user_info(p_requestor_id);
616 fetch user_info into requestor_info_rec;
617 l_requestor_name := requestor_info_rec.user_name;
618 close user_info;
619
620 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR', l_requestor_name);
621
622 p_resp_key := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY');
623
624 open approver_cur(p_resp_key);
625 fetch approver_cur into l_approver_id;
626 close approver_cur;
627 l_approver_name := l_respString||l_approver_id;
628
629 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCAPPROVER', l_approver_name);
630
631 wf_engine.SetItemAttrText (itemtype => itemtype,
632 itemkey => itemkey,
633 aname => l_doc_attr,
634 avalue => 'PLSQLCLOB:OKL_SSC_WF.getLocationDocument/'||
635 itemtype ||':'||itemkey||':&#NID');
636
637 resultout := 'COMPLETE';
638
639 return;
640 end if;
641
642 if ( funcmode = 'CANCEL' ) then
643
644 resultout := 'COMPLETE';
645 return;
646 end if;
647 if ( funcmode = 'RESPOND') then
648 resultout := 'COMPLETE';
649 return;
650 end if;
651 if ( funcmode = 'FORWARD') then
652 resultout := 'COMPLETE';
653 return;
654 end if;
655 if ( funcmode = 'TRANSFER') then
656 resultout := 'COMPLETE';
657 return;
658 end if;
659 if ( funcmode = 'TIMEOUT' ) then
660 resultout := 'COMPLETE';
661 else
662 resultout := wf_engine.eng_timedout;
663 return;
664 end if;
665
666 exception
667 when others then
668 WF_CORE.CONTEXT ('OKL_SSC_WF', 'getLocationMessage', itemtype, itemkey,actid,funcmode);
669 resultout := 'ERROR';
670 raise;
671
672
673
674 end getLocationMessage;
675
676
677 Procedure getLocationDocument
678 ( document_id in varchar2,
679 display_type in varchar2,
680 document in out nocopy clob,
681 document_type in out nocopy varchar2
682 )
683
684 IS
685 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - Start
686 -- Commented cursor
687 /*
688 CURSOR asset_locations(p_tas_id number, p_tal_type varchar2) IS
689 select
690 distinct
691 ASX.ASSET_NUMBER,
692 PSU1.DESCRIPTION OLD_LOCATION,
693 PSU2.DESCRIPTION NEW_LOCATION,
694 TRX.DATE_TRANS_OCCURRED EFFECTIVE_DATE
695 from OKL_TXL_ITM_INSTS INX,
696 OKX_PARTY_SITE_USES_V PSU1,
697 OKX_PARTY_SITE_USES_V PSU2,
698 OKL_TXL_ASSETS_B ASX,
699 OKL_TRX_ASSETS TRX
700 where
701 INX.TAS_ID = p_tas_id
702 AND INX.TAL_TYPE = p_tal_type
703 AND ASX.KLE_ID = INX.DNZ_CLE_ID
704 AND PSU1.ID1 (+) = INX.OBJECT_ID1_OLD
705 AND PSU1.ID2 (+) = INX.OBJECT_ID2_OLD
706 AND PSU2.ID1 = INX.OBJECT_ID1_NEW
707 AND PSU2.ID2 = INX.OBJECT_ID2_NEW
708 AND TRX.ID = INX.TAS_ID
709 ;
710
711 */
712 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - End
713 first_index number;
714 second_index number;
715 third_index number;
716
717 l_tas_id number;
718 l_tal_type varchar2(100);
719 l_itemtype varchar2(100);
720 l_itemkey varchar2(100);
721
722 l_document varchar2(32000);
723 NL VARCHAR2(1) := fnd_global.newline;
724
725 -- Bug#4274575 - smadhava - 28-Sep-2005 - Added - Start
726 l_old_location_id1 VARCHAR2(100);
727 l_new_location_id1 VARCHAR2(100);
728 l_old_location_id2 VARCHAR2(100);
729 l_new_location_id2 VARCHAR2(100);
730 l_trx_date DATE;
731 l_asset_id VARCHAR2(100);
732 l_asset_number OKC_K_LINES_V.NAME%TYPE;
733 l_old_location OKX_PARTY_SITE_USES_V.DESCRIPTION%TYPE;
734 l_new_location OKX_PARTY_SITE_USES_V.DESCRIPTION%TYPE;
735
736 CURSOR c_get_location(cp_id1 IN OKL_TXL_ITM_INSTS.OBJECT_ID1_NEW%TYPE,
737 cp_id2 IN OKL_TXL_ITM_INSTS.OBJECT_ID2_NEW%TYPE) IS
738 SELECT PSU.DESCRIPTION
739 FROM OKX_PARTY_SITE_USES_V PSU
740 WHERE PSU.ID1 = cp_id1
741 AND PSU.ID2 = cp_id2;
742
743 CURSOR c_get_asset_dtls(cp_kle_id IN OKC_K_LINES_V.ID%TYPE) IS
744 SELECT NAME
745 FROM OKC_K_LINES_V
746 WHERE ID = cp_kle_id;
747 -- Bug#4274575 - smadhava - 28-Sep-2005 - Added - End
748
749 BEGIN
750
751 --the document_id is in the form of
752 --'PLSQLCLOB:OKL_SSC_WF.getAssetReturnDocument/itemtyp:itemkey:#NID'
753 --we need to get itemtype and itemkey
754
755 first_index := instr(document_id, '/', 1, 1); --index of the slash '/'
756 second_index := instr(document_id, ':', 1,1); --index of first colon ':'
757 third_index := instr(document_id, ':', 1, 2); --index of the second colon ':'
758
759 l_itemtype := substr(document_id, first_index+1, second_index-first_index-1);
760 l_itemkey := substr(document_id, second_index+1, third_index-second_index-1);
761
762 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - Start
763 /*
764 l_tas_id := to_number(WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTASID'));
765 l_tal_type := WF_ENGINE.GetItemAttrText(l_itemtype,l_itemkey,'SSCTALTYPE');
766 */
767
768 -- have to fetch the location names for the Ids
769 l_new_location_id1 := wf_engine.GetItemAttrText(l_itemtype, l_itemkey, 'NEWSITEID1');
770 l_old_location_id1 := wf_engine.GetItemAttrText(l_itemtype, l_itemkey, 'OLDSITE1');
771 l_new_location_id2 := wf_engine.GetItemAttrText(l_itemtype, l_itemkey, 'NEWSITEID2');
772 l_old_location_id2 := wf_engine.GetItemAttrText(l_itemtype, l_itemkey, 'OLDSITE2');
773 l_trx_date := wf_engine.GetItemAttrDate(l_itemtype, l_itemkey, 'TRX_DATE');
774 l_asset_id := wf_engine.GetItemAttrText(l_itemtype, l_itemkey, 'ASSETID');
775
776 OPEN c_get_location(l_old_location_id1,l_old_location_id2);
777 FETCH c_get_location INTO l_old_location;
778 CLOSE c_get_location;
779
780 OPEN c_get_location(l_new_location_id1,l_new_location_id2);
781 FETCH c_get_location INTO l_new_location;
782 CLOSE c_get_location;
783
784 OPEN c_get_asset_dtls(l_asset_id);
785 FETCH c_get_asset_dtls INTO l_asset_number;
786 CLOSE c_get_asset_dtls;
787
788 IF (display_type = 'text/html') THEN
789
790 --first generate the header
791 l_document := '<BR><BR>' || NL;
792 l_document := l_document ||
793 '<table cellpadding="3" cellspacing="3" border="3" summary="">' || NL;
794 l_document := l_document ||
795 '<tr><th>Asset Number</th><th>Current Asset Location</th><th>New Asset Location Requested</th><th>Effective Date</th></tr>' || NL;
796 --loop through the record, and generate line by line
797
798
799 /*FOR asset_locations_rec in asset_locations(l_tas_id, l_tal_type)
800 LOOP*/
801 l_document := l_document ||
802 '<tr><td>' ||
803 l_asset_number ||
804 --asset_locations_rec.ASSET_NUMBER ||
805 '</td>';
806
807 l_document := l_document ||
808 '<td>' ||
809 l_old_location ||
810 --asset_locations_rec.OLD_LOCATION ||
811 '</td>' || NL;
812
813 l_document := l_document ||
814 '<td>' ||
815 l_new_location ||
816 --asset_locations_rec.NEW_LOCATION ||
817 '</td>' || NL;
818
819 l_document := l_document ||
820 '<td>' ||
821 --asset_locations_rec.EFFECTIVE_DATE ||
822 fnd_Date.date_to_displaydate(l_trx_date) ||
823 '</td></tr>' || NL;
824
825
826 -- END LOOP;
827 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - End
828
829 l_document := l_document || '</table>';
830 END IF;-- end to 'text/html' display type
831
832 --document := l_document;
833 wf_notification.WriteToClob( document, l_document);
834
835
836 EXCEPTION
837
838 when others then
839 WF_CORE.CONTEXT ('okl_ssc_wf', 'getLocationDocument', l_itemtype, l_itemkey);
840 raise;
841
842 END getLocationDocument;
843
844
845
846
847 procedure update_location_fnc (itemtype in varchar2,
848 itemkey in varchar2,
849 actid in number,
850 funcmode in varchar2,
851 resultout out nocopy varchar2 ) is
852
853 l_tas_id number;
854 x_return_status varchar2(1);
855 x_msg_count number;
856 l_msg_data varchar2(2000);
857
858 l_admin VARCHAR2(120) := 'SYSADMIN';
859
860 error_updating_asset_locations EXCEPTION;
861
862 -- Bug#4274575 - smadhava - 28-Sep-2005 - Added - Start
863 l_new_location_id1 VARCHAR2(100);
864 l_new_location_id2 VARCHAR2(100);
865 l_old_location_id1 VARCHAR2(100);
866 l_old_location_id2 VARCHAR2(100);
867 l_trx_date OKL_TRX_ASSETS.DATE_TRANS_OCCURRED%TYPE;
868 l_asset_id OKC_K_LINES_V.ID%TYPE;
869 l_party_site_id okx_party_site_uses_v.PARTY_SITE_ID%TYPE;
870 l_location_id okx_party_site_uses_v.LOCATION_ID%TYPE;
871
872 l_loc_rec OKL_BLK_AST_UPD_PUB.blk_rec_type;
873 l_trxv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
874 x_trxv_rec OKL_TRX_ASSETS_PUB.thpv_rec_type;
875
876 CURSOR okl_loc_csr (p_id1 IN OKL_TXL_ITM_INSTS.OBJECT_ID1_NEW%TYPE, p_id2 IN OKL_TXL_ITM_INSTS.OBJECT_ID2_NEW%TYPE) IS
877 SELECT
878 PARTY_SITE_ID,
879 LOCATION_ID
880 FROM okx_party_site_uses_v
881 WHERE ID1 = p_id1 AND ID2 = p_id2;
882
883 -- Bug#4274575 - smadhava - 28-Sep-2005 - Added - End
884 begin
885
886
887
888 -- assign variable to attribute
889 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
890
891
892 if ( funcmode = 'RUN' ) then
893 /*
894 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - Start
895 l_new_location_id1 := wf_engine.GetItemAttrText(itemtype, itemkey, 'NEWSITEID1');
896 l_new_location_id2 := wf_engine.GetItemAttrText(itemtype, itemkey, 'NEWSITEID2');
897 l_old_location_id1 := wf_engine.GetItemAttrText(itemtype, itemkey, 'OLDSITE1');
898 l_old_location_id2 := wf_engine.GetItemAttrText(itemtype, itemkey, 'OLDSITE2');
899 l_trx_date := wf_engine.GetItemAttrDate(itemtype, itemkey, 'TRX_DATE');
900 l_asset_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ASSETID');
901
902 OPEN okl_loc_csr(l_new_location_id1, l_new_location_id2);
903 FETCH okl_loc_csr INTO l_party_site_id, l_location_id;
904 CLOSE okl_loc_csr;
905
906 l_loc_rec.parent_line_id := l_asset_id;
907 l_loc_rec.loc_id := l_location_id;
908 l_loc_rec.party_site_id := l_party_site_id;
909 l_loc_rec.newsite_id1 := l_new_location_id1;
910 l_loc_rec.newsite_id2 := l_new_location_id2;
911 l_loc_rec.oldsite_id1 := l_old_location_id1;
912 l_loc_rec.oldsite_id2 := l_old_location_id2;
913 l_loc_rec.date_from := l_trx_date;
914
915 OKL_BLK_AST_UPD_PUB.update_location(
916 p_api_version => 1.0,
917 p_init_msg_list => OKL_API.G_FALSE,
918 p_loc_rec => l_loc_rec,
919 x_return_status => x_return_status,
920 x_msg_count => x_msg_count,
921 x_msg_data => l_msg_data);
922
923 l_tas_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCTASID'));
924
925
926 update_location(p_api_version => 1.0,
927 p_init_msg_list => OKC_API.G_FALSE,
928 p_tas_id => l_tas_id,
929 x_return_status => x_return_status,
930 x_msg_count => x_msg_count,
931 x_msg_data => l_msg_data);
932 */
933 -- Bug#4274575 - smadhava - 28-Sep-2005 - Modified - end
934 --check the update result
935 --asawanka ebtax changes start
936
937 l_asset_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ASSETID');
938
939
940 OKL_BLK_AST_UPD_PVT.process_update_location(
941 p_api_version => 1.0,
942 p_init_msg_list => 'T',
943 p_kle_id => l_asset_id,
944 x_return_status => x_return_status,
945 x_msg_count => x_msg_count,
946 x_msg_data => l_msg_data);
947
948 IF x_return_status <> 'S' THEN
949 RAISE error_updating_asset_locations;
950 END IF;
951
952 resultout := 'COMPLETE';
953
954 return;
955 end if;
956
957
958 if ( funcmode = 'CANCEL' ) then
959 resultout := 'COMPLETE';
960 return;
961 end if;
962 if ( funcmode = 'RESPOND') then
963 resultout := 'COMPLETE';
964 return;
965 end if;
966 if ( funcmode = 'FORWARD') then
967 resultout := 'COMPLETE';
968 return;
969 end if;
970 if ( funcmode = 'TRANSFER') then
971 resultout := 'COMPLETE';
972 return;
973 end if;
974 if ( funcmode = 'TIMEOUT' ) then
975 resultout := 'COMPLETE';
976 else
977 resultout := wf_engine.eng_timedout;
978 return;
979 end if;
980
981 exception
982 when others then
983 WF_CORE.CONTEXT ('OKL_SSC_WF', 'update_location_fnc:'||l_msg_data, itemtype, itemkey,actid,funcmode);
984 resultout := 'ERROR';
985 raise;
986
987
988 end update_location_fnc;
989 -- END: procedures added by Zhendi
990
991 ---------------------Manu's Procedures-----------------------------------
992
993 PROCEDURE get_trx_rec
994 (p_api_version IN NUMBER,
995 p_init_msg_list IN VARCHAR2,
996 x_return_status OUT NOCOPY VARCHAR2,
997 x_msg_count OUT NOCOPY NUMBER,
998 x_msg_data OUT NOCOPY VARCHAR2,
999 p_cle_id IN NUMBER,
1000 p_transaction_type IN VARCHAR2,
1001 x_trx_rec OUT NOCOPY CSI_DATASTRUCTURES_PUB.transaction_rec) IS
1002
1003 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1004 l_api_name CONSTANT VARCHAR2(30) := 'GET_TRX_REC';
1005 l_api_version CONSTANT NUMBER := 1.0;
1006
1007 --Following cursor assumes that a transaction type called
1008 --'OKL LINE ACTIVATION' and 'OKL SPLIT ASSET' will be seeded in IB
1009 Cursor okl_trx_type_curs(p_transaction_type IN VARCHAR2)is
1010 select transaction_type_id
1011 from CS_TRANSACTION_TYPES_V
1012 where Name = p_transaction_type;
1013 l_trx_type_id NUMBER;
1014 begin
1015 open okl_trx_type_curs(p_transaction_type);
1016 Fetch okl_trx_type_curs
1017 into l_trx_type_id;
1018 If okl_trx_type_curs%NotFound Then
1019 --OKL LINE ACTIVATION not seeded as a source transaction in IB
1020 Raise OKC_API.G_EXCEPTION_ERROR;
1021 End If;
1022 close okl_trx_type_curs;
1023 --Assign transaction Type id to seeded value in cs_lookups
1024 x_trx_rec.transaction_type_id := l_trx_type_id;
1025 --Assign Source Line Ref id to contract line id of IB instance line
1026 x_trx_rec.source_line_ref_id := p_cle_id;
1027 x_trx_rec.transaction_date := sysdate;
1028 x_trx_rec.source_transaction_date := sysdate;
1029 Exception
1030 When OKC_API.G_EXCEPTION_ERROR Then
1031 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1032 (
1033 l_api_name,
1034 G_PKG_NAME,
1035 'OKC_API.G_RET_STS_ERROR',
1036 x_msg_count,
1037 x_msg_data,
1038 '_PUB'
1039 );
1040 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1042 (
1043 l_api_name,
1044 G_PKG_NAME,
1045 'OKC_API.G_RET_STS_UNEXP_ERROR',
1046 x_msg_count,
1047 x_msg_data,
1048 '_PUB'
1049 );
1050 WHEN OTHERS THEN
1051 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1052 (
1053 l_api_name,
1054 G_PKG_NAME,
1055 'OTHERS',
1056 x_msg_count,
1057 x_msg_data,
1058 '_PUB'
1059 );
1060 END get_trx_rec;
1061
1062
1063
1064 PROCEDURE update_serial_number(
1065 p_api_version IN NUMBER,
1066 p_init_msg_list IN VARCHAR2,
1067 p_tas_id IN NUMBER,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2)
1071 AS
1072
1073 CURSOR okl_itiv_csr (p_tas_id IN NUMBER) IS
1074 SELECT
1075 KLE_ID,
1076 SERIAL_NUMBER,
1077 instance_number_ib,
1078 INVENTORY_ITEM_ID
1079 FROM OKL_TXL_ITM_INSTS
1080 WHERE TAS_ID = p_tas_id;
1081
1082 CURSOR okl_inst_csr (p_kle_id IN NUMBER) IS
1083 SELECT
1084 KIT.OBJECT1_ID1
1085 FROM OKC_K_ITEMS KIT
1086 WHERE KIT.CLE_ID = (SELECT KLB.ID FROM OKC_K_LINES_B KLB WHERE
1087 KLB.ID = p_kle_id);
1088
1089 l_tas_id NUMBER;
1090 l_kle_id NUMBER;
1091 l_object_version_number NUMBER;
1092
1093 SUBTYPE instance_rec IS CSI_DATASTRUCTURES_PUB.instance_rec;
1094 SUBTYPE extend_attrib_values_tbl IS CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
1095 SUBTYPE party_tbl IS CSI_DATASTRUCTURES_PUB.party_tbl;
1096 SUBTYPE account_tbl IS CSI_DATASTRUCTURES_PUB.party_account_tbl;
1097 SUBTYPE pricing_attribs_tbl IS CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
1098 SUBTYPE organization_units_tbl IS CSI_DATASTRUCTURES_PUB.organization_units_tbl;
1099 SUBTYPE instance_asset_tbl IS CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
1100 SUBTYPE transaction_rec IS CSI_DATASTRUCTURES_PUB.transaction_rec;
1101 SUBTYPE id_tbl IS CSI_DATASTRUCTURES_PUB.id_tbl;
1102
1103 l_instance_rec_type instance_rec;
1104 l_ext_attrib_values_tbl extend_attrib_values_tbl;
1105 l_party_tbl party_tbl;
1106 l_account_tbl account_tbl;
1107 l_pricing_attrib_tbl pricing_attribs_tbl;
1108 l_org_assignments_tbl organization_units_tbl;
1109 l_asset_assignment_tbl instance_asset_tbl;
1110 l_txn_rec transaction_rec;
1111 l_instance_id_lst id_tbl;
1112
1113
1114 BEGIN
1115
1116 If p_tas_id is not null Then
1117 l_tas_id := p_tas_id;
1118 ELSE
1119 l_tas_id := -9999;
1120 END IF;
1121
1122 FOR csr_1 in okl_itiv_csr(l_tas_id)
1123 loop
1124
1125 l_kle_id := csr_1.KLE_ID;
1126 l_instance_rec_type.SERIAL_NUMBER := csr_1.SERIAL_NUMBER;
1127 -- l_instance_rec_type.INSTANCE_NUMBER := csr_1.instance_number_ib;
1128 -- l_instance_rec_type.INVENTORY_ITEM_ID := csr_1.INVENTORY_ITEM_ID;
1129 FOR csr_2 in okl_inst_csr(l_kle_id)
1130 loop
1131 l_instance_rec_type.INSTANCE_ID := csr_2.OBJECT1_ID1;
1132 end loop;
1133 select object_version_number into l_object_version_number from
1134 csi_item_instances
1135 where instance_id = l_instance_rec_type.INSTANCE_ID;
1136
1137 l_instance_rec_type.object_version_number := l_object_version_number;
1138 l_instance_rec_type.MFG_SERIAL_NUMBER_FLAG := 'N';
1139
1140 get_trx_rec(p_api_version => p_api_version,
1141 p_init_msg_list => p_init_msg_list,
1142 x_return_status => x_return_status,
1143 x_msg_count => x_msg_count,
1144 x_msg_data => x_msg_data,
1145 p_cle_id => NULL,
1146 p_transaction_type => 'New',
1147 x_trx_rec => l_txn_rec);
1148
1149 l_txn_rec.transaction_id := FND_API.G_MISS_NUM;
1150 csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
1151 p_commit => fnd_api.g_false,
1152 p_init_msg_list => p_init_msg_list,
1153 p_validation_level => fnd_api.g_valid_level_full,
1154 p_instance_rec => l_instance_rec_type,
1155 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1156 p_party_tbl => l_party_tbl,
1157 p_account_tbl => l_account_tbl,
1158 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1159 p_org_assignments_tbl => l_org_assignments_tbl,
1160 p_asset_assignment_tbl => l_asset_assignment_tbl,
1161 p_txn_rec => l_txn_rec,
1162 x_instance_id_lst => l_instance_id_lst,
1163 x_return_status => x_return_status,
1164 x_msg_count => x_msg_count,
1165 x_msg_data => x_msg_data);
1166
1167 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1168 RAISE OKL_API.G_EXCEPTION_ERROR;
1169 ELSIF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 END IF;
1172 end loop;
1173
1174 EXCEPTION
1175
1176 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1177 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1178 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1179 p_data => x_msg_data);
1180
1181 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1182 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1183 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1184 p_data => x_msg_data);
1185
1186 WHEN OTHERS THEN
1187 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1188 FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','Update_Serial_Number');
1189 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1190 p_data => x_msg_data);
1191
1192 END update_serial_number;
1193
1194 PROCEDURE update_location(
1195 p_api_version IN NUMBER,
1196 p_init_msg_list IN VARCHAR2,
1197 p_tas_id IN NUMBER,
1198 x_return_status OUT NOCOPY VARCHAR2,
1199 x_msg_count OUT NOCOPY NUMBER,
1200 x_msg_data OUT NOCOPY VARCHAR2)
1201 AS
1202
1203 CURSOR okl_itiv_csr (p_tas_id IN NUMBER) IS
1204 SELECT
1205 KLE_ID,
1206 OBJECT_ID1_NEW,
1207 OBJECT_ID2_NEW,
1208 instance_number_ib,
1209 INVENTORY_ITEM_ID
1210 FROM OKL_TXL_ITM_INSTS
1211 WHERE TAS_ID = p_tas_id;
1212
1213 CURSOR okl_loc_csr (p_id1 IN OKL_TXL_ITM_INSTS.OBJECT_ID1_NEW%TYPE, p_id2 IN OKL_TXL_ITM_INSTS.OBJECT_ID2_NEW%TYPE) IS
1214 SELECT
1215 PARTY_SITE_ID,
1216 LOCATION_ID
1217 FROM okx_party_site_uses_v
1218 WHERE ID1 = p_id1 AND ID2 = p_id2;
1219
1220 CURSOR okl_inst_csr (p_kle_id IN OKL_TXL_ITM_INSTS.KLE_ID%TYPE) IS
1221 SELECT
1222 KIT.OBJECT1_ID1
1223 FROM OKC_K_ITEMS KIT
1224 WHERE KIT.CLE_ID = (SELECT KLB.ID FROM OKC_K_LINES_B KLB WHERE
1225 KLB.ID = p_kle_id);
1226
1227
1228 SUBTYPE instance_rec IS CSI_DATASTRUCTURES_PUB.instance_rec;
1229 SUBTYPE extend_attrib_values_tbl IS CSI_DATASTRUCTURES_PUB.extend_attrib_values_tbl;
1230 SUBTYPE party_tbl IS CSI_DATASTRUCTURES_PUB.party_tbl;
1231 SUBTYPE account_tbl IS CSI_DATASTRUCTURES_PUB.party_account_tbl;
1232 SUBTYPE pricing_attribs_tbl IS CSI_DATASTRUCTURES_PUB.pricing_attribs_tbl;
1233 SUBTYPE organization_units_tbl IS CSI_DATASTRUCTURES_PUB.organization_units_tbl;
1234 SUBTYPE instance_asset_tbl IS CSI_DATASTRUCTURES_PUB.instance_asset_tbl;
1235 SUBTYPE transaction_rec IS CSI_DATASTRUCTURES_PUB.transaction_rec;
1236 SUBTYPE id_tbl IS CSI_DATASTRUCTURES_PUB.id_tbl;
1237
1238 l_instance_rec_type instance_rec;
1239 l_ext_attrib_values_tbl extend_attrib_values_tbl;
1240 l_party_tbl party_tbl;
1241 l_account_tbl account_tbl;
1242 l_pricing_attrib_tbl pricing_attribs_tbl;
1243 l_org_assignments_tbl organization_units_tbl;
1244 l_asset_assignment_tbl instance_asset_tbl;
1245 l_txn_rec transaction_rec;
1246 l_instance_id_lst id_tbl;
1247
1248
1249 l_tas_id OKL_TXL_ITM_INSTS.TAS_ID%TYPE;
1250 l_kle_id OKL_TXL_ITM_INSTS.KLE_ID%TYPE;
1251 l_id1_id OKL_TXL_ITM_INSTS.OBJECT_ID1_NEW%TYPE;
1252 l_id2_id OKL_TXL_ITM_INSTS.OBJECT_ID2_NEW%TYPE;
1253 l_object_version_number OKL_TXL_ITM_INSTS.object_version_number%TYPE;
1254 BEGIN
1255
1256 If p_tas_id is not null Then
1257 l_tas_id := p_tas_id;
1258 ELSE
1259 l_tas_id := -9999;
1260 END IF;
1261 FOR csr_1 in okl_itiv_csr(l_tas_id)
1262 loop
1263
1264 l_kle_id := csr_1.KLE_ID;
1265 l_id1_id := csr_1.OBJECT_ID1_NEW;
1266 l_id2_id := csr_1.OBJECT_ID2_NEW;
1267
1268 FOR csr_2 in okl_inst_csr(l_kle_id)
1269 loop
1270 l_instance_rec_type.INSTANCE_ID := TO_NUMBER(csr_2.OBJECT1_ID1);
1271 end loop;
1272
1273 -- l_instance_rec_type.INSTANCE_NUMBER := csr_1.instance_number_ib;
1274
1275 FOR csr_3 in okl_loc_csr(l_id1_id, l_id2_id)
1276 loop
1277 l_instance_rec_type.LOCATION_ID := TO_NUMBER(csr_3.LOCATION_ID);
1278 l_instance_rec_type.INSTALL_LOCATION_ID := TO_NUMBER(csr_3.PARTY_SITE_ID);
1279 l_instance_rec_type.INSTALL_LOCATION_TYPE_CODE := 'HZ_PARTY_SITES';
1280 end loop;
1281
1282 select object_version_number into l_object_version_number from
1283 csi_item_instances
1284 where instance_id = l_instance_rec_type.INSTANCE_ID;
1285
1286 l_instance_rec_type.object_version_number := l_object_version_number;
1287
1288 get_trx_rec(p_api_version => p_api_version,
1289 p_init_msg_list => p_init_msg_list,
1290 x_return_status => x_return_status,
1291 x_msg_count => x_msg_count,
1292 x_msg_data => x_msg_data,
1293 p_cle_id => NULL,
1294 p_transaction_type => 'New',
1295 x_trx_rec => l_txn_rec);
1296
1297 l_txn_rec.transaction_id := FND_API.G_MISS_NUM;
1298
1299 csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
1300 p_commit => fnd_api.g_false,
1301 p_init_msg_list => p_init_msg_list,
1302 p_validation_level => fnd_api.g_valid_level_full,
1303 p_instance_rec => l_instance_rec_type,
1304 p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
1305 p_party_tbl => l_party_tbl,
1306 p_account_tbl => l_account_tbl,
1307 p_pricing_attrib_tbl => l_pricing_attrib_tbl,
1308 p_org_assignments_tbl => l_org_assignments_tbl,
1309 p_asset_assignment_tbl => l_asset_assignment_tbl,
1310 p_txn_rec => l_txn_rec,
1311 x_instance_id_lst => l_instance_id_lst,
1312 x_return_status => x_return_status,
1313 x_msg_count => x_msg_count,
1314 x_msg_data => x_msg_data);
1315
1316 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1317 RAISE OKL_API.G_EXCEPTION_ERROR;
1318 ELSIF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1320 END IF;
1321
1322 end loop;
1323
1324 EXCEPTION
1325
1326 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1327 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1328 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1329 p_data => x_msg_data);
1330
1331 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1332 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1333 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1334 p_data => x_msg_data);
1335
1336 WHEN OTHERS THEN
1337 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1338 FND_MSG_PUB.ADD_EXC_MSG('OKL_SSC_ASST_LOC_SERNUM_PUB','Update_Location');
1339 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
1340 p_data => x_msg_data);
1341
1342 END update_location;
1343
1344 PROCEDURE get_assets_for_transaction(
1345 p_tas_id IN NUMBER,
1346 x_talv_rec OUT NOCOPY OKL_TXL_ASSETS_PVT.tlvv_tbl_type)
1347 IS
1348
1349 CURSOR okl_tax_csr (p_tas_id IN NUMBER) IS
1350 SELECT
1351 ID,
1352 KLE_ID,
1353 DNZ_KHR_ID,
1354 ASSET_NUMBER,
1355 DESCRIPTION
1356 FROM OKL_TXL_ASSETS_V
1357 WHERE nvl(OKL_TXL_ASSETS_V.TAS_ID,-9999) = p_tas_id;
1358 Type r_rec is record( ID NUMBER,
1359 KLE_ID NUMBER,
1360 DNZ_KHR_ID NUMBER,
1361 ASSET_NUMBER VARCHAR2(30),
1362 DESCRIPTION VARCHAR2(1995));
1363 i NUMBER default 1;
1364 l_tas_id NUMBER;
1365 BEGIN
1366 If p_tas_id is not null Then
1367 l_tas_id := p_tas_id;
1368 ELSE
1369 l_tas_id := -9999;
1370 END IF;
1371 FOR csr_1 in okl_tax_csr(l_tas_id)
1372 loop
1373 x_talv_rec(i).ID := csr_1.id;
1374 x_talv_rec(i).KLE_ID := csr_1.kle_id;
1375 x_talv_rec(i).DNZ_KHR_ID := csr_1.dnz_khr_id;
1376 x_talv_rec(i).ASSET_NUMBER := csr_1.asset_number;
1377 x_talv_rec(i).DESCRIPTION := csr_1.description;
1378 i := i + 1;
1379 end loop;
1380 END get_assets_for_transaction;
1381
1382 -- END: Zhendi added the following procedures
1383
1384 --- Added by DKHANDEL
1385
1386 FUNCTION exist_subscription(p_event_name IN VARCHAR2) RETURN VARCHAR2
1387 -----------------------------------------------------------------------
1388 -- Return 'Y' if there are some active subscription for the given event
1389 -- Otherwise it returns 'N'
1390 -----------------------------------------------------------------------
1391 IS
1392 CURSOR cu0 IS
1393 SELECT 'Y'
1394 FROM wf_event_subscriptions a,
1395 wf_events b
1396 WHERE a.event_filter_guid = b.guid
1397 AND a.status = 'ENABLED'
1398 AND b.name = p_event_name
1399 AND rownum = 1;
1400 l_yn VARCHAR2(1);
1401 BEGIN
1402 OPEN cu0;
1403 FETCH cu0 INTO l_yn;
1404 IF cu0%NOTFOUND THEN
1405 l_yn := 'N';
1406 END IF;
1407 CLOSE cu0;
1408 RETURN l_yn;
1409 END;
1410 -----------------------------------------------------------------------
1411
1412 procedure submit_third_party_ins_wrapper(
1413 provider_id in number ,
1414 site_id in number ,
1415 policy_number in varchar2,
1416 policy_start_date in date,
1417 policy_end_date in date,
1418 coverage_amount in number ,
1419 deductible in number ,
1420 lessor_insured in varchar2 ,
1421 lessor_payee in varchar2 ,
1422 contract_id in number,
1423 requestor_id in number,
1424 provider_name in varchar2,
1425 address1 in varchar2,
1426 address2 in varchar2 ,
1427 address3 in varchar2 ,
1428 address4 in varchar2 ,
1429 city in varchar2,
1430 state in varchar2,
1431 province in varchar2 ,
1432 county in varchar2 ,
1433 zip in varchar2,
1434 country in varchar2,
1435 telephone in varchar2,
1436 email in varchar2) IS
1437
1438 l_params wf_parameter_list_t;
1439 l_event_key NUMBER;
1440
1441 CURSOR event_number IS
1442 SELECT OKLSSC_WFITEMKEY_S.nextval from dual;
1443
1444
1445 begin
1446
1447 open event_number;
1448 fetch event_number into l_event_key;
1449 close event_number;
1450
1451
1452 WF_EVENT.AddParameterToList('SSCKID',to_char(contract_id),l_params);
1453 WF_EVENT.AddParameterToList('SSCINSPROVIDERID',to_char(provider_id),l_params);
1454 WF_EVENT.AddParameterToList('SSCSITEID',to_char(site_id),l_params);
1455 WF_EVENT.AddParameterToList('SSCPOLICYNUMBER',policy_number,l_params);
1456 WF_EVENT.AddParameterToList('SSCSTARTDATE',to_char(policy_start_date),l_params);
1457 WF_EVENT.AddParameterToList('SSCENDDATE',to_char(policy_end_date),l_params);
1458 WF_EVENT.AddParameterToList('SSCCOVERAGEAMOUNT',to_char(coverage_amount),l_params);
1459 WF_EVENT.AddParameterToList('SSCDEDUCTIBLE',to_char(deductible),l_params);
1460 WF_EVENT.AddParameterToList('SSCLESSORINSURED',lessor_insured,l_params);
1461 WF_EVENT.AddParameterToList('SSCLESSORPAYEE',lessor_payee,l_params);
1462 WF_EVENT.AddParameterToList('REQUESTOR',to_char(requestor_id),l_params);
1463
1464 if (provider_id IS NOT null) then --existing provider workflow
1465
1466 WF_EVENT.raise('oracle.apps.okl.ssc.submitthirdpartyinsurance',
1467 l_event_key,
1468 null,
1469 l_params);
1470 else
1471
1472 WF_EVENT.AddParameterToList('SSCNEWPROVIDERNAME',provider_name,l_params);
1473 WF_EVENT.AddParameterToList('SSCPROVIDERADDRESS1',address1,l_params);
1474 WF_EVENT.AddParameterToList('SSCPROVIDERADDRESS2',address2,l_params);
1475 WF_EVENT.AddParameterToList('SSCPROVIDERADDRESS3',address3,l_params);
1476 WF_EVENT.AddParameterToList('SSCPROVIDERADDRESS4',address4,l_params);
1477 WF_EVENT.AddParameterToList('SSCPROVIDERCITY',city,l_params);
1478 WF_EVENT.AddParameterToList('SSCPROVIDERSTATECODE',state,l_params);
1479 WF_EVENT.AddParameterToList('SSCPROVIDERCOUNTRY',country,l_params);
1480 WF_EVENT.AddParameterToList('SSCPROVIDERCOUNTY',county,l_params);
1481 WF_EVENT.AddParameterToList('SSCPROVIDERPROVINCE',province,l_params);
1482 WF_EVENT.AddParameterToList('SSCPROVIDERZIPCODE',zip,l_params);
1483 WF_EVENT.AddParameterToList('SSCPROVIDERPHONENUMBER',telephone,l_params);
1484 WF_EVENT.AddParameterToList('SSCPROVIDEREMAILADDRESS',email,l_params);
1485
1486 WF_EVENT.raise('oracle.apps.okl.ssc.createandsubmitthirdpartyinsurance',
1487 l_event_key,
1488 null,
1489 l_params);
1490
1491
1492 end if;
1493 end submit_third_party_ins_wrapper;
1494
1495 procedure set_ins_provider_wf (itemtype in varchar2,
1496 itemkey in varchar2,
1497 actid in number,
1498 funcmode in varchar2,
1499 resultout out nocopy varchar2 ) is
1500
1501 CURSOR provider_info(provider_name VARCHAR2) IS
1502 SELECT party_id from hz_parties
1503 WHERE party_number = provider_name;
1504
1505 CURSOR site_id_cur(p_party_id NUMBER) IS
1506 SELECT site_id from OKL_INS_PARTYSITES_V
1507 WHERE party_id = p_party_id;
1508
1509 l_provider_name VARCHAR2(120);
1510 l_provider_id NUMBER;
1511 l_site_id NUMBER;
1512
1513 begin
1514
1515 if ( funcmode = 'RUN' ) then
1516
1517 --Read attributes from WorkFlow
1518 l_provider_name:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCNEWINSURANCEPROVIDERNUMBER');
1519
1520 open provider_info(l_provider_name);
1521 fetch provider_info into l_provider_id;
1522 close provider_info;
1523
1524
1525
1526 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCINSPROVIDERID',to_char(l_provider_id));
1527
1528 open site_id_cur(l_provider_id);
1529 fetch site_id_cur into l_site_id;
1530 close site_id_cur;
1531
1532 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCSITEID',to_char(l_site_id));
1533
1534 resultout := 'COMPLETE';
1535
1536 return;
1537 end if;
1538
1539
1540 if ( funcmode = 'CANCEL' ) then
1541 resultout := 'COMPLETE';
1542 return;
1543 end if;
1544 if ( funcmode = 'RESPOND') then
1545 resultout := 'COMPLETE';
1546 return;
1547 end if;
1548 if ( funcmode = 'FORWARD') then
1549 resultout := 'COMPLETE';
1550 return;
1551 end if;
1552 if ( funcmode = 'TRANSFER') then
1553 resultout := 'COMPLETE';
1554 return;
1555 end if;
1556 if ( funcmode = 'TIMEOUT' ) then
1557 resultout := 'COMPLETE';
1558 else
1559 resultout := wf_engine.eng_timedout;
1560 return;
1561 end if;
1562
1563 exception
1564 when others then
1565 WF_CORE.CONTEXT ('okl_ssc_wf', 'set_ins_provider_wf', itemtype, itemkey,actid,funcmode);
1566 raise;
1567 end set_ins_provider_wf;
1568
1569
1570 procedure submit_ins_set_notif_wf (itemtype in varchar2,
1571 itemkey in varchar2,
1572 actid in number,
1573 funcmode in varchar2,
1574 resultout out nocopy varchar2 ) is
1575
1576 l_respString VARCHAR2(15);
1577 l_respId NUMBER;
1578 l_resp_key VARCHAR2(30);
1579
1580
1581 begin
1582 l_respString:='FND_RESP540:';
1583 l_resp_key :=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEYFORNEWPROV');
1584
1585 if ( funcmode = 'RUN' ) then
1586
1587
1588
1589 SELECT responsibility_id
1590 INTO l_respId
1591 FROM fnd_responsibility
1592 WHERE responsibility_key = l_resp_key
1593 AND application_id = 540;
1594
1595 wf_engine.SetItemAttrText (itemtype => itemtype,
1596 itemkey => itemkey,
1597 aname => 'SSC_NTFRECIPIENT_ITMATTR2',
1598 avalue => l_respString || l_respId);
1599
1600
1601
1602 resultout := 'COMPLETE';
1603
1604 return;
1605 end if;
1606
1607
1608 if ( funcmode = 'CANCEL' ) then
1609 resultout := 'COMPLETE';
1610 return;
1611 end if;
1612 if ( funcmode = 'RESPOND') then
1613 resultout := 'COMPLETE';
1614 return;
1615 end if;
1616 if ( funcmode = 'FORWARD') then
1617 resultout := 'COMPLETE';
1618 return;
1619 end if;
1620 if ( funcmode = 'TRANSFER') then
1621 resultout := 'COMPLETE';
1622 return;
1623 end if;
1624 if ( funcmode = 'TIMEOUT' ) then
1625 resultout := 'COMPLETE';
1626 else
1627 resultout := wf_engine.eng_timedout;
1628 return;
1629 end if;
1630
1631 exception
1632 when others then
1633 WF_CORE.CONTEXT ('okl_ssc_wf', 'submit_ins_set_notif_wf',itemtype, itemkey,actid,funcmode);
1634 raise;
1635 end submit_ins_set_notif_wf;
1636
1637
1638
1639 procedure submit_insurance_wf (itemtype in varchar2,
1640 itemkey in varchar2,
1641 actid in number,
1642 funcmode in varchar2,
1643 resultout out nocopy varchar2 ) is
1644 l_providerid NUMBER;
1645 l_knum VARCHAR2(120);
1646 l_policynum VARCHAR2(120);
1647 l_start_date DATE;
1648 l_end_date DATE;
1649 l_coverage_amount NUMBER;
1650 l_deductible NUMBER;
1651 l_lessor_insured VARCHAR2(1);
1652 l_lessor_payee VARCHAR2(1);
1653 l_chrid NUMBER;
1654 l_siteid NUMBER;
1655 l_requestor_userid NUMBER;
1656 l_policy_rec okl_insurance_policies_pub.ipyv_rec_type;
1657 lx_return_status VARCHAR2(1);
1658 lx_msg_count NUMBER;
1659 lx_msg_data VARCHAR2(100);
1660 lx_policy_rec okl_insurance_policies_pub.ipyv_rec_type;
1661 api_exception exception;
1662 l_respString VARCHAR2(15);
1663 l_respId NUMBER;
1664 l_resp_key VARCHAR2(30);
1665 l_admin VARCHAR2(120) := 'SYSADMIN';
1666 begin
1667 l_respString:='FND_RESP540:';
1668
1669 if ( funcmode = 'RUN' ) then
1670
1671
1672
1673 l_resp_key:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
1674
1675
1676 SELECT responsibility_id
1677 INTO l_respId
1678 FROM fnd_responsibility
1679 WHERE responsibility_key = l_resp_key
1680 AND application_id = 540;
1681
1682 wf_engine.SetItemAttrText (itemtype => itemtype,
1683 itemkey => itemkey,
1684 aname => 'SSC_NTFRECIPIENT_ITMATTR',
1685 avalue => l_respString || l_respId);
1686
1687
1688
1689 --Read attributes from WorkFlow
1690 --Read attributes from WorkFlow
1691 l_providerid:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCINSPROVIDERID');
1692 l_siteid := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCSITEID');
1693 l_chrid:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID');
1694 l_coverage_amount:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCCOVERAGEAMOUNT');
1695 l_deductible:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCDEDUCTIBLE');
1696 l_requestor_userid:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'REQUESTOR');
1697
1698 l_policynum:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCPOLICYNUMBER');
1699 l_lessor_insured:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCLESSORINSURED');
1700 l_lessor_payee:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCLESSORPAYEE');
1701
1702 l_start_date:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCSTARTDATE');
1703 l_end_date:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCENDDATE');
1704
1705 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
1706
1707
1708 --get contract number for notification
1709 SELECT contract_number
1710 INTO l_knum
1711 FROM okc_k_headers_v
1712 WHERE id = l_chrid;
1713
1714 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCONTRACTNUMBER',l_knum);
1715
1716
1717 --Set attributes in record
1718 l_policy_rec.policy_number := l_policynum;
1719 l_policy_rec.covered_amount:= l_coverage_amount;
1720 l_policy_rec.lessor_insured_yn := l_lessor_insured;
1721 l_policy_rec.lessor_payee_yn := l_lessor_payee;
1722 l_policy_rec.deductible := l_deductible;
1723 l_policy_rec.date_to := l_end_date;
1724 l_policy_rec.date_from := l_start_date;
1725 l_policy_rec.isu_id := l_providerid;
1726 l_policy_rec.khr_id := l_chrid;
1727 l_policy_rec.sfwt_flag := 'T';
1728 l_policy_rec.iss_code := null;
1729 l_policy_rec.quote_yn := 'N';
1730 l_policy_rec.agent_yn := 'N';
1731 l_policy_rec.ipy_type := 'THIRD_PARTY_POLICY';
1732 l_policy_rec.agency_site_id := l_siteid;
1733
1734 --call api
1735 OKL_INS_POLICIES_PUB.insert_ins_policies(1.0,
1736 FND_API.G_FALSE,
1737 lx_return_status,
1738 lx_msg_count,
1739 lx_msg_data,
1740 l_policy_rec,
1741 lx_policy_rec);
1742
1743
1744 if (lx_return_status = 'E') then
1745 raise api_exception;
1746 end if;
1747
1748 resultout := 'COMPLETE';
1749
1750 return;
1751 end if;
1752
1753
1754 if ( funcmode = 'CANCEL' ) then
1755 resultout := 'COMPLETE';
1756 return;
1757 end if;
1758 if ( funcmode = 'RESPOND') then
1759 resultout := 'COMPLETE';
1760 return;
1761 end if;
1762 if ( funcmode = 'FORWARD') then
1763 resultout := 'COMPLETE';
1764 return;
1765 end if;
1766 if ( funcmode = 'TRANSFER') then
1767 resultout := 'COMPLETE';
1768 return;
1769 end if;
1770 if ( funcmode = 'TIMEOUT' ) then
1771 resultout := 'COMPLETE';
1772 else
1773 resultout := wf_engine.eng_timedout;
1774 return;
1775 end if;
1776
1777 exception
1778 /*when api_exception then
1779 FND_MSG_PUB.Count_And_Get (
1780 p_encoded => FND_API.G_FALSE,
1781 p_count => lx_msg_count,
1782 p_data => lx_msg_data);*/
1783 when others then
1784 WF_CORE.CONTEXT ('okl_ssc_wf', 'submit_ins_wf',itemtype, itemkey,actid,funcmode);
1785 raise;
1786 end submit_insurance_wf;
1787
1788 procedure req_renewal_quote_wf (itemtype in varchar2,
1789 itemkey in varchar2,
1790 actid in number,
1791 funcmode in varchar2,
1792 resultout out nocopy varchar2 ) is
1793
1794 CURSOR contract_info(p_id NUMBER) IS
1795 SELECT contract_number from okc_k_headers_b
1796 WHERE id = p_id;
1797
1798 CURSOR user_info(p_id NUMBER) IS
1799 SELECT user_name from fnd_user
1800 WHERE user_id = p_id;
1801
1802 user_info_rec user_info%rowtype;
1803
1804
1805 l_knum VARCHAR2(120);
1806 l_username VARCHAR2(100);
1807 p_chrid NUMBER;
1808 p_requestor_userid NUMBER;
1809 l_respString VARCHAR2(15);
1810 l_respId NUMBER;
1811 l_resp_key VARCHAR2(30);
1812
1813 begin
1814 l_respString:='FND_RESP540:';
1815
1816
1817 if ( funcmode = 'RUN' ) then
1818 p_chrid:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID');
1819 p_requestor_userid:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'REQUESTOR');
1820 l_resp_key :=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
1821
1822 open contract_info(p_chrid);
1823 fetch contract_info into l_knum;
1824 close contract_info;
1825
1826 open user_info(p_requestor_userid);
1827 fetch user_info into l_username;
1828 close user_info;
1829
1830 SELECT responsibility_id
1831 INTO l_respId
1832 FROM fnd_responsibility
1833 WHERE responsibility_key = l_resp_key
1834 AND application_id = 540;
1835
1836 wf_engine.SetItemAttrText (itemtype => itemtype,
1837 itemkey => itemkey,
1838 aname => 'SSC_NTFRECIPIENT_ITMATTR',
1839 avalue => l_respString || l_respId);
1840
1841 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCKNUM',l_knum);
1842 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR',l_username);
1843 resultout := 'COMPLETE';
1844
1845 return;
1846 end if;
1847
1848
1849 if ( funcmode = 'CANCEL' ) then
1850 resultout := 'COMPLETE';
1851 return;
1852 end if;
1853 if ( funcmode = 'RESPOND') then
1854 resultout := 'COMPLETE';
1855 return;
1856 end if;
1857 if ( funcmode = 'FORWARD') then
1858 resultout := 'COMPLETE';
1859 return;
1860 end if;
1861 if ( funcmode = 'TRANSFER') then
1862 resultout := 'COMPLETE';
1863 return;
1864 end if;
1865 if ( funcmode = 'TIMEOUT' ) then
1866 resultout := 'COMPLETE';
1867 else
1868 resultout := wf_engine.eng_timedout;
1869 return;
1870 end if;
1871
1872 exception
1873 when others then
1874 WF_CORE.CONTEXT ('okl_ssc_wf', 'req_renewal_quote_wf',itemtype, itemkey,actid,funcmode);
1875 raise;
1876 end req_renewal_quote_wf;
1877
1878
1879 -- added by padmaja
1880 -- this procedure sets attribute values in the WF
1881 PROCEDURE set_invoice_format_attributes (itemtype in varchar2,
1882 itemkey in varchar2,
1883 actid in number,
1884 funcmode in varchar2,
1885 resultout out nocopy varchar2 )
1886 IS
1887
1888
1889 l_chr_id NUMBER;
1890 l_contract_number VARCHAR2(250);
1891 l_current_format VARCHAR2(150);
1892 l_new_format VARCHAR2(150);
1893 l_format_id NUMBER;
1894 l_requestor VARCHAR2(100);
1895 l_approver VARCHAR2(100);
1896 l_admin VARCHAR2(100) := 'SYSADMIN';
1897 l_requestor_id NUMBER;
1898 l_approver_id NUMBER;
1899 l_resp_key VARCHAR2(30);
1900 l_respString VARCHAR2(15) := 'FND_RESP540:';
1901 l_org_info okc_k_headers_all_b.org_id%type;
1902 CURSOR contract_cur(l_chr_id NUMBER)
1903 IS
1904 SELECT CONTRACT_NUMBER ,org_id
1905 FROM OKC_K_HEADERS_all_b --modified by rajnisku for getting org_id
1906 WHERE id = l_chr_id;
1907
1908 CURSOR user_info(l_user_id NUMBER) IS
1909 SELECT user_name from fnd_user
1910 WHERE user_id = l_user_id;
1911
1912 CURSOR format_cur(l_format_id NUMBER) IS
1913 SELECT name
1914 FROM okl_invoice_formats_v
1915 WHERE id = l_format_id;
1916
1917 CURSOR old_format_cur(l_chr_id NUMBER) IS
1918 SELECT rule_information1
1919 FROM okc_rules_b
1920 WHERE rule_information_category = 'LAINVD'
1921 AND dnz_chr_id = l_chr_id;
1922
1923 CURSOR approver_cur (l_resp_key VARCHAR2)IS
1924 SELECT responsibility_id
1925 FROM fnd_responsibility
1926 WHERE responsibility_key = l_resp_key
1927 AND application_id = 540;
1928
1929 begin
1930
1931 if ( funcmode = 'RUN' ) then
1932
1933 l_chr_id:=to_number( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID'));
1934 l_format_id:=to_number( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCINVFORID'));
1935 l_requestor_id:=to_number( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
1936 l_resp_key := ( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY'));
1937
1938 open contract_cur(l_chr_id);
1939 fetch contract_cur into l_contract_number,l_org_info;
1940 close contract_cur;
1941
1942 MO_GLOBAL.set_policy_context('S',l_org_info);
1943
1944 open user_info(l_requestor_id);
1945 fetch user_info into l_requestor;
1946 close user_info;
1947
1948 open approver_cur(l_resp_key);
1949 fetch approver_cur into l_approver_id;
1950 close approver_cur;
1951
1952 open format_cur(l_format_id);
1953 fetch format_cur into l_new_format;
1954 close format_cur;
1955
1956 open old_format_cur(l_chr_id);
1957 fetch old_format_cur into l_current_format;
1958 close old_format_cur;
1959
1960 l_approver := l_respString||l_approver_id;
1961
1962 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCKNUM',l_contract_number);
1963 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR',l_requestor);
1964 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCAPPROVER',l_approver);
1965 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCNEWINVFOR',l_new_format);
1966 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCURINVFOR',l_current_format);
1967 -- the error notification goes to the l_admin through WF default error processing
1968 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
1969 resultout := 'COMPLETE';
1970
1971 return;
1972 end if;
1973
1974
1975 if ( funcmode = 'CANCEL' ) then
1976 resultout := 'COMPLETE';
1977 return;
1978 end if;
1979 if ( funcmode = 'RESPOND') then
1980 resultout := 'COMPLETE';
1981 return;
1982 end if;
1983 if ( funcmode = 'FORWARD') then
1984 resultout := 'COMPLETE';
1985 return;
1986 end if;
1987 if ( funcmode = 'TRANSFER') then
1988 resultout := 'COMPLETE';
1989 return;
1990 end if;
1991 if ( funcmode = 'TIMEOUT' ) then
1992 resultout := 'COMPLETE';
1993 else
1994 resultout := wf_engine.eng_timedout;
1995 return;
1996 end if;
1997
1998 exception
1999 when others then
2000 -- default wf error handling
2001 WF_CORE.CONTEXT ('okl_ssc_wf'
2002 , 'set_invoice_format_attributes'
2003 , itemtype
2004 , itemkey
2005 , actid
2006 , funcmode);
2007 RAISE;
2008
2009 end set_invoice_format_attributes;
2010
2011 -- added by pnayani
2012 -- Updates the invoice format
2013 PROCEDURE invoice_format_change_wf(itemtype in varchar2,
2014 itemkey in varchar2,
2015 actid in number,
2016 funcmode in varchar2,
2017 resultout out nocopy varchar2 )
2018 IS
2019
2020 CURSOR rule_id_cur( p_chr_id IN NUMBER)
2021 IS
2022 SELECT rl.id,
2023 rl.rgp_id
2024 FROM okc_rule_groups_v rg,
2025 okc_rules_v rl
2026 WHERE rl.rgp_id = rg.id
2027 AND rl.dnz_chr_id = rg.dnz_chr_id
2028 AND rg.chr_id = rl.dnz_chr_id
2029 AND rg.cle_id is null
2030 AND rg.rgd_code ='LABILL'
2031 AND rl.rule_information_category = 'LAINVD'
2032 AND rg.dnz_chr_id = p_chr_id;
2033 CURSOR org_info(p_contract_id NUMBER) IS --added by rajnisku for retrieving orginfo
2034 select org_id from okc_k_headers_all_b
2035 where id=p_contract_id;
2036 l_org_info okc_k_headers_all_b.org_id%type;
2037 rule_id_rec rule_id_cur%ROWTYPE;
2038 l_rule_id NUMBER;
2039 l_rgp_id NUMBER;
2040 l_contract_id NUMBER;
2041 l_contract_number VARCHAR2(150);
2042 l_invoice_format VARCHAR2(150);
2043 l_counter NUMBER := 1;
2044 l_rule_tbl l_rule_tbl_type;
2045 l_return_status VARCHAR2(1);
2046 l_msg_count NUMBER;
2047 l_msg_data VARCHAR2(250);
2048 l_api_version NUMBER :=1.0;
2049 cus_bank_acc_id number;
2050
2051 error_updating_invoice_format EXCEPTION;
2052
2053 begin
2054
2055
2056 -- get attribute values from WF
2057 l_contract_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID');
2058 l_contract_number:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKNUM');
2059 l_invoice_format:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCNEWINVFOR');
2060 open org_info(l_contract_id);
2061 fetch org_info into l_org_info;
2062 close org_info ;
2063 MO_GLOBAL.set_policy_context('S',l_org_info);
2064
2065 OPEN rule_id_cur(l_contract_id);
2066 FETCH rule_id_cur INTO rule_id_rec;
2067 l_rule_id := rule_id_rec.id;
2068 l_rgp_id := rule_id_rec.rgp_id;
2069 CLOSE rule_id_cur;
2070
2071 l_rule_tbl(l_counter).rgd_code := 'LABILL';
2072 l_rule_tbl(l_counter).rule_id := l_rule_id;
2073 l_rule_tbl(l_counter).rgp_id := l_rgp_id;
2074 l_rule_tbl(l_counter).dnz_chr_id := l_contract_id;
2075 l_rule_tbl(l_counter).sfwt_flag := 'N';
2076 l_rule_tbl(l_counter).std_template_yn := 'N';
2077 l_rule_tbl(l_counter).warn_yn := 'N';
2078 l_rule_tbl(l_counter).created_by := OKC_API.G_MISS_NUM;
2079 l_rule_tbl(l_counter).CREATION_DATE := OKC_API.G_MISS_DATE;
2080 l_rule_tbl(l_counter).LAST_UPDATED_BY := OKC_API.G_MISS_NUM;
2081 l_rule_tbl(l_counter).LAST_UPDATE_DATE := OKC_API.G_MISS_DATE;
2082 l_rule_tbl(l_counter).LAST_UPDATE_LOGIN := OKC_API.G_MISS_NUM;
2083 l_rule_tbl(l_counter).rule_information_category := 'LAINVD';
2084 l_rule_tbl(l_counter).rule_information1 := l_invoice_format;
2085
2086 OKL_RGRP_RULES_PROCESS_PUB.process_rule_group_rules(
2087 p_api_version => l_api_version
2088 ,p_init_msg_list => 'T'
2089 ,x_return_status => l_return_status
2090 ,x_msg_count => l_msg_count
2091 ,x_msg_data => l_msg_data
2092 ,p_chr_id => l_contract_id
2093 ,p_line_id => -1
2094 ,p_cpl_id => -1
2095 ,p_rrd_id => -1
2096 ,p_rgr_tbl => l_rule_tbl);
2097
2098 IF l_return_status <> 'S' THEN
2099 RAISE error_updating_invoice_format;
2100 END IF;
2101
2102
2103 EXCEPTION
2104
2105 when others then
2106 -- default wf error handling
2107 WF_CORE.CONTEXT ('okl_ssc_wf'
2108 , 'invoice_format_change_wf'
2109 , itemtype
2110 , itemkey,actid,funcmode);
2111 RAISE;
2112 END;
2113
2114
2115 /* procedure added by Vishal */
2116 procedure req_billinf_change_getdata_wf (itemtype in varchar2,
2117 itemkey in varchar2,
2118 actid in number,
2119 funcmode in varchar2,
2120 resultout out nocopy varchar2 ) is
2121 CURSOR org_info(p_contract_id NUMBER) IS
2122 select org_id from okc_k_headers_all_b
2123 where id=p_contract_id; --added by rajnisku for retrieving orginfo
2124 l_org_info okc_k_headers_all_b.org_id%type;
2125
2126 --rkuttiya commented the below cursor for bug #6523600
2127 /*CURSOR current_billing_info(p_contract_id NUMBER) IS
2128 SELECT contract.contract_number contract_number,
2129 site.name bill_to_site,
2130 site.description bill_to_address
2131 FROM okx_cust_site_uses_v site ,
2132 okc_k_headers_all_b contract
2133 WHERE contract.id = p_contract_id
2134 AND site.id1 = contract.bill_to_site_use_id; */
2135
2136 --rkuttiya added the below cursor for bug#6523600
2137 CURSOR current_billing_info(p_contract_id IN NUMBER) IS
2138 SELECT contract.contract_number contract_number,
2139 cs.location bill_to_site,
2140 ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,L.ADDRESS1,L.ADDRESS2,L.ADDRESS3,
2141 L.ADDRESS4,L.CITY,L.COUNTY,L.STATE,L.PROVINCE,L.POSTAL_CODE,NULL,L.COUNTRY,NULL,
2142 NULL,NULL,NULL,NULL,NULL,NULL,'N','N',300,1,1) bill_to_address
2143 FROM hz_cust_site_uses_all cs,
2144 hz_cust_acct_sites_all ca,
2145 hz_party_sites ps,
2146 hz_locations l,
2147 okc_k_headers_all_b contract
2148 WHERE cs.cust_acct_site_id = ca.cust_acct_site_id
2149 AND ca.party_site_id = ps.party_site_id
2150 AND ps.location_id = l.location_id
2151 AND l.content_source_type = 'USER_ENTERED'
2152 AND cs.site_use_id = contract.bill_to_site_use_id
2153 AND contract.id = p_contract_id;
2154
2155
2156 current_billing_info_rec current_billing_info%rowtype;
2157
2158 -- parameters set in the procedure
2159 l_contract_number okc_k_headers_b.contract_number%type;
2160 l_current_billing_site okx_cust_site_uses_v.name%type;
2161 l_current_billing_address okx_cust_site_uses_v.description%type;
2162 l_new_billing_site okx_cust_site_uses_v.name%type;
2163 l_new_billing_address okx_cust_site_uses_v.description%type;
2164 l_username fnd_user.user_name%type;
2165 l_respString VARCHAR2(50);
2166 l_respId NUMBER;
2167 l_resp_key VARCHAR2(30);
2168
2169 -- parameters getting passed
2170 p_requestor_userid NUMBER;
2171 p_new_billing_site_id NUMBER;
2172 p_chrid NUMBER;
2173
2174 -- declare variable
2175 l_admin VARCHAR2(120) ;
2176
2177 --rkuttiya commented for bug #6523600
2178 /*CURSOR new_billing_info(p_billing_site_id NUMBER) IS
2179 select name bill_to_site, description bill_to_address
2180 from okx_cust_site_uses_v
2181 where id1= p_billing_site_id; */
2182
2183 CURSOR new_billing_info(p_billing_site_id IN VARCHAR2) IS
2184 SELECT CS.LOCATION BILL_TO_SITE,
2185 ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,L.ADDRESS1,L.ADDRESS2,L.ADDRESS3,
2186 L.ADDRESS4,L.CITY,L.COUNTY,L.STATE,L.PROVINCE,L.POSTAL_CODE,NULL,L.COUNTRY,NULL,
2187 NULL,NULL,NULL,NULL,NULL,NULL,'N','N',300,1,1) BILL_TO_ADDRESS
2188 FROM HZ_CUST_SITE_USES_ALL CS,
2189 HZ_CUST_ACCT_SITES_ALL CA,
2190 HZ_PARTY_SITES PS,
2191 HZ_LOCATIONS L
2192 WHERE CS.CUST_ACCT_SITE_ID = CA.CUST_ACCT_SITE_ID
2193 AND CA.PARTY_SITE_ID = PS.PARTY_SITE_ID
2194 AND PS.LOCATION_ID = L.LOCATION_ID
2195 AND L.CONTENT_SOURCE_TYPE = 'USER_ENTERED'
2196 AND CS.site_use_id = p_billing_site_id;
2197
2198 new_billing_info_rec new_billing_info%rowtype;
2199
2200 CURSOR user_info(l_user_id NUMBER) IS
2201 SELECT user_name from fnd_user
2202 WHERE user_id = l_user_id;
2203 user_info_rec user_info%rowtype;
2204
2205 begin
2206
2207
2208
2209 if ( funcmode = 'RUN' ) then
2210
2211 l_admin := 'SYSADMIN';
2212 -- assign variable to attribute
2213 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
2214
2215 -- retrieve attributes from workflow engine
2216 p_chrid:=to_number( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID'));
2217 p_requestor_userid:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID'));
2218 p_new_billing_site_id:=to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCNEWBILLSITEID'));
2219 l_resp_key :=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCAPPROVERRESPONSIBILITYKEY');
2220 open org_info(p_chrid);
2221 fetch org_info into l_org_info;
2222 close org_info ;
2223 MO_GLOBAL.set_policy_context('S',l_org_info);
2224
2225 --populate other attributes
2226 open current_billing_info(p_chrid);
2227 fetch current_billing_info into current_billing_info_rec;
2228 l_contract_number := current_billing_info_rec.contract_number;
2229 l_current_billing_site := current_billing_info_rec.bill_to_site;
2230 l_current_billing_address := current_billing_info_rec.bill_to_address;
2231 close current_billing_info;
2232
2233 open user_info(p_requestor_userid);
2234 fetch user_info into user_info_rec;
2235 l_username := user_info_rec.user_name;
2236 close user_info;
2237
2238
2239 open new_billing_info(p_new_billing_site_id);
2240 fetch new_billing_info into new_billing_info_rec;
2241 l_new_billing_site := new_billing_info_rec.bill_to_site;
2242 l_new_billing_address := new_billing_info_rec.bill_to_address;
2243 close new_billing_info;
2244
2245
2246
2247 l_respString := 'FND_RESP540:';
2248
2249 SELECT responsibility_id
2250 INTO l_respId
2251 FROM fnd_responsibility
2252 WHERE responsibility_key = l_resp_key -- This example is for 'Lease Center Agent' responsibility
2253 AND application_id = 540;
2254
2255 wf_engine.SetItemAttrText (itemtype => itemtype ,
2256 itemkey => itemkey ,
2257 aname => 'SSC_NTFRECIPIENT_ITMATTR',
2258 avalue => l_respString || l_respId);
2259
2260
2261
2262 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCKNUM', l_contract_number );
2263 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTOR', l_username);
2264 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCURBILLSITEADDRESS', l_current_billing_address );
2265 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCURBILLSITE',l_current_billing_site);
2266 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCNEWBILLSITE',l_new_billing_site);
2267 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCNEWBILLSITEADDRESS',l_new_billing_address);
2268
2269 resultout := 'COMPLETE';
2270
2271 return;
2272 end if;
2273
2274
2275 if ( funcmode = 'CANCEL' ) then
2276 resultout := 'COMPLETE';
2277 return;
2278 end if;
2279 if ( funcmode = 'RESPOND') then
2280 resultout := 'COMPLETE';
2281 return;
2282 end if;
2283 if ( funcmode = 'FORWARD') then
2284 resultout := 'COMPLETE';
2285 return;
2286 end if;
2287 if ( funcmode = 'TRANSFER') then
2288 resultout := 'COMPLETE';
2289 return;
2290 end if;
2291 if ( funcmode = 'TIMEOUT' ) then
2292 resultout := 'COMPLETE';
2293 else
2294 resultout := wf_engine.eng_timedout;
2295 return;
2296 end if;
2297
2298 exception
2299 when others then
2300 WF_CORE.CONTEXT ('okl_ssc_wf', 'req_billinf_change_getdata_wf', itemtype, itemkey,actid,funcmode);
2301 raise;
2302 end req_billinf_change_getdata_wf;
2303
2304
2305
2306 /* procedure added by Vishal */
2307 procedure req_billinf_change_wrapper_wf (itemtype in varchar2,
2308 itemkey in varchar2,
2309 actid in number,
2310 funcmode in varchar2,
2311 resultout out nocopy varchar2 ) is
2312
2313
2314 l_contract_id NUMBER ;
2315 l_billing_site_id NUMBER;
2316 l_counter NUMBER := 1;
2317 l_return_status VARCHAR2(1);
2318 l_msg_count NUMBER;
2319 l_msg_data VARCHAR2(250);
2320 l_api_version NUMBER;
2321
2322
2323 l_chrv_rec OKL_OKC_MIGRATION_PVT.CHRV_REC_TYPE;
2324 l_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2325 x_chrv_rec OKL_OKC_MIGRATION_PVT.CHRV_REC_TYPE;
2326 x_khrv_rec OKL_CONTRACT_PUB.khrv_rec_type;
2327
2328 api_exception EXCEPTION;
2329 -- declare variable
2330 l_admin VARCHAR2(120) ;
2331 l_org_id NUMBER;
2332
2333 begin
2334
2335 if ( funcmode = 'RUN' ) then
2336
2337 l_admin := 'SYSADMIN';
2338 -- assign variable to attribute
2339 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'WF_ADMINISTRATOR',l_admin);
2340
2341
2342 --retrieve from the workflow engine
2343 l_contract_id := to_number( WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCKID'));
2344 l_billing_site_id := to_number(WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCNEWBILLSITEID'));
2345 l_org_id := wf_engine.GetItemAttrNumber(itemtype,itemkey,'ORG_ID');
2346 l_chrv_rec.ID := l_contract_id;
2347 /* Bug 3292221 SPILLAIP changed l_khrv_rec.khr_id to id */
2348 l_khrv_rec.ID := l_contract_id;
2349 l_chrv_rec.bill_to_site_use_id := l_billing_site_id;
2350
2351 --rkuttiya modified for bug 6523600
2352 --setting the org context to the contract org before updating the contract
2353 --mo_global.init('OKL');
2354 MO_GLOBAL.set_policy_context('S',l_org_id);
2355
2356 -- call the API to update billing information
2357 okl_contract_pub.update_contract_header(
2358 p_api_version => '1.0'
2359 ,p_init_msg_list => 'T'
2360 ,x_return_status => l_return_status
2361 ,x_msg_count => l_msg_count
2362 ,x_msg_data => l_msg_data
2363 ,p_restricted_update => OKL_API.G_FALSE
2364 ,p_chrv_rec => l_chrv_rec
2365 ,p_khrv_rec => l_khrv_rec
2366 ,x_chrv_rec => x_chrv_rec
2367 ,x_khrv_rec => x_khrv_rec);
2368
2369 IF l_return_status <> 'S' THEN
2370
2371 RAISE api_exception;
2372 END IF;
2373
2374
2375 resultout := 'COMPLETE';
2376
2377 return;
2378 end if;
2379
2380
2381 if ( funcmode = 'CANCEL' ) then
2382 resultout := 'COMPLETE';
2383 return;
2384 end if;
2385 if ( funcmode = 'RESPOND') then
2386 resultout := 'COMPLETE';
2387 return;
2388 end if;
2389 if ( funcmode = 'FORWARD') then
2390 resultout := 'COMPLETE';
2391 return;
2392 end if;
2393 if ( funcmode = 'TRANSFER') then
2394 resultout := 'COMPLETE';
2395 return;
2396 end if;
2397 if ( funcmode = 'TIMEOUT' ) then
2398 resultout := 'COMPLETE';
2399 else
2400 resultout := wf_engine.eng_timedout;
2401 return;
2402 end if;
2403
2404 EXCEPTION
2405
2406 when api_exception then
2407 FND_MSG_PUB.Count_And_Get (
2408 p_encoded => FND_API.G_FALSE,
2409 p_count => l_msg_count,
2410 p_data => l_msg_data);
2411
2412 WF_CORE.CONTEXT ('okl_ssc_wf'
2413 , 'req_billinf_change_wrapper_wf'
2414 , itemtype, itemkey, actid, funcmode);
2415
2416 raise;
2417 when others then
2418 WF_CORE.CONTEXT ('okl_ssc_wf'
2419 , 'req_billinf_change_wrapper_wf'
2420 , itemtype, itemkey, actid, funcmode);
2421
2422 raise;
2423 END req_billinf_change_wrapper_wf;
2424
2425
2426 -- Vishal Added on 19-Sep-2002 to handle integration from EO
2427 procedure raise_inv_format_chg_event ( contract_id in varchar2 ,
2428 user_id in varchar2,
2429 invoice_format_id in varchar2)
2430
2431 IS
2432 CURSOR item_key_seq IS
2433 SELECT OKLSSC_WFITEMKEY_S.nextval key from dual;
2434
2435 item_key_rec item_key_seq%rowtype;
2436
2437 item_key varchar2(100) ;
2438 begin
2439
2440
2441 OPEN item_key_seq;
2442 FETCH item_key_seq INTO item_key_rec;
2443 item_key := to_char( item_key_rec.key);
2444 CLOSE item_key_seq;
2445
2446 WF_EVENT.raise2( 'oracle.apps.okl.ssc.requestinvoiceformatchange',item_key, null, 'SSCKID', contract_id , 'SSCREQUESTORID', user_id, 'SSCINVFORID', invoice_format_id ) ;
2447
2448
2449
2450 end raise_inv_format_chg_event;
2451
2452
2453
2454 -- Vishal Added on 20-Sep-2002 to handle integration from EO
2455 --rkuttiya modified to add org id to set the org context
2456 procedure raise_billinf_change_event ( contract_id in varchar2 ,
2457 user_id in varchar2,
2458 bill_site_id in varchar2)
2459
2460 IS
2461 CURSOR item_key_seq IS
2462 SELECT OKLSSC_WFITEMKEY_S.nextval key from dual;
2463
2464 CURSOR c_get_khr_org(p_contract_id IN NUMBER) IS
2465 select authoring_org_id
2466 from okc_k_headers_all_b
2467 where id = p_contract_id;
2468
2469 item_key_rec item_key_seq%rowtype;
2470
2471 item_key varchar2(100) ;
2472 l_org_id NUMBER;
2473 begin
2474
2475
2476 OPEN item_key_seq;
2477 FETCH item_key_seq INTO item_key_rec;
2478 item_key := 'x' || to_char( item_key_rec.key);
2479 CLOSE item_key_seq;
2480
2481 OPEN c_get_khr_org(contract_id);
2482 FETCH c_get_khr_org INTO l_org_id;
2483 CLOSE c_get_khr_org;
2484
2485
2486 mo_global.init('OKL');
2487 MO_GLOBAL.set_policy_context('S',l_org_id);
2488
2489 WF_EVENT.raise2('oracle.apps.okl.ssc.requestbillinginfochange',item_key, null, 'SSCKID', contract_id , 'SSCREQUESTORID', user_id, 'SSCNEWBILLSITEID', bill_site_id,'ORG_ID',l_org_id) ;
2490
2491
2492 end raise_billinf_change_event;
2493
2494 --Cancel Insurance Event function to set attributes
2495 PROCEDURE cancel_ins_set_attr_wf
2496 (itemtype in varchar2,
2497 itemkey in varchar2,
2498 actid in number,
2499 funcmode in varchar2,
2500 resultout out nocopy varchar2 )
2501 IS
2502
2503 CURSOR contract_info_cur( p_chr_id IN NUMBER) IS
2504 SELECT contract_number from okc_k_headers_b
2505 WHERE id = p_chr_id;
2506
2507 CURSOR policy_info_cur(p_pol_id IN NUMBER) IS
2508 SELECT policy_number, iss_code, cancellation_date, khr_id
2509 from OKL_INS_POLICIES_B
2510 WHERE id = p_pol_id;
2511
2512 CURSOR requestor_info_cur(p_requestor_id IN NUMBER) IS
2513 SELECT user_name from fnd_user
2514 WHERE user_id = p_requestor_id;
2515
2516 CURSOR fnd_lookup_cur(p_code IN VARCHAR2, p_type IN VARCHAR2) IS
2517 SELECT meaning from fnd_lookups
2518 WHERE lookup_type = p_type
2519 AND lookup_code = p_code;
2520
2521 CURSOR policy_info_tl_cur(p_pol_id IN NUMBER) IS
2522 SELECT cancellation_comment
2523 FROM OKL_INS_POLICIES_TL
2524 WHERE id = p_pol_id;
2525
2526 l_chr_id VARCHAR2(40);
2527 l_pol_id VARCHAR2(40);
2528 l_requestor_id VARCHAR2(40);
2529 l_chr_number VARCHAR2(120);
2530 l_pol_number VARCHAR2(20);
2531 l_iss_code VARCHAR2(30);
2532 l_pol_status VARCHAR2(80);
2533 l_cancel_date DATE;
2534 l_user_name VARCHAR2(100);
2535 l_comments VARCHAR2(240);
2536 l_resp_id VARCHAR2(15);
2537 l_resp_key VARCHAR2(30);
2538 l_performer VARCHAR2(27);
2539 l_respString VARCHAR2(12):='FND_RESP540:';
2540
2541 api_exception EXCEPTION;
2542
2543 begin
2544
2545 if ( funcmode = 'RUN' ) then
2546
2547 --Read attributes from WorkFlow
2548 l_pol_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCINSPOLID');
2549 l_requestor_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID');
2550 l_resp_key:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
2551
2552 --Read from table
2553 open policy_info_cur(l_pol_id);
2554 fetch policy_info_cur into l_pol_number,l_iss_code, l_cancel_date,l_chr_id ;
2555 close policy_info_cur;
2556
2557 open contract_info_cur(l_chr_id);
2558 fetch contract_info_cur into l_chr_number;
2559 close contract_info_cur;
2560
2561 open requestor_info_cur(l_requestor_id);
2562 fetch requestor_info_cur into l_user_name;
2563 close requestor_info_cur;
2564
2565 open fnd_lookup_cur(l_iss_code, 'OKL_INSURANCE_STATUS');
2566 fetch fnd_lookup_cur into l_pol_status;
2567 close fnd_lookup_cur;
2568
2569 open policy_info_tl_cur(l_pol_id);
2570 fetch policy_info_tl_cur into l_comments;
2571 close policy_info_tl_cur;
2572
2573
2574 SELECT responsibility_id
2575 into l_resp_id
2576 FROM fnd_responsibility
2577 WHERE responsibility_key = l_resp_key
2578 AND application_id = 540;
2579
2580
2581 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCONTRACTNUMBER', l_chr_number );
2582 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTORNAME', l_user_name);
2583 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPOLNUMBER', l_pol_number);
2584 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPOLSTATUS', l_pol_status);
2585 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCANCELDATE', l_cancel_date);
2586 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCOMMENTS', l_comments);
2587 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPERFORMER', l_respString||l_resp_id);
2588
2589 resultout := 'COMPLETE';
2590
2591 return;
2592 end if;
2593
2594
2595 if ( funcmode = 'CANCEL' ) then
2596 resultout := 'COMPLETE';
2597 return;
2598 end if;
2599 if ( funcmode = 'RESPOND') then
2600 resultout := 'COMPLETE';
2601 return;
2602 end if;
2603 if ( funcmode = 'FORWARD') then
2604 resultout := 'COMPLETE';
2605 return;
2606 end if;
2607 if ( funcmode = 'TRANSFER') then
2608 resultout := 'COMPLETE';
2609 return;
2610 end if;
2611 if ( funcmode = 'TIMEOUT' ) then
2612 resultout := 'COMPLETE';
2613 else
2614 resultout := wf_engine.eng_timedout;
2615 return;
2616 end if;
2617
2618 exception
2619 when others then
2620 WF_CORE.CONTEXT ('OKL_SSC_WF', 'cancel_ins_set_attr_wf', itemtype, itemkey,'c','d');
2621 raise;
2622 END cancel_ins_set_attr_wf;
2623
2624 --Cancel Insurance PL/SQL wrapper
2625 PROCEDURE cancel_ins_wrapper_wf
2626 (p_api_version IN NUMBER,
2627 p_init_msg_list IN VARCHAR2,
2628 p_polid IN number,
2629 p_cancelcomment IN varchar2,
2630 p_canceldate IN date,
2631 p_canrsn_code IN varchar2,
2632 p_userid IN NUMBER,
2633 x_return_status OUT NOCOPY VARCHAR2,
2634 x_msg_count OUT NOCOPY NUMBER,
2635 x_msg_data OUT NOCOPY VARCHAR2
2636 )
2637 IS
2638 l_ipyv_rec ipyv_rec_type;
2639 lx_ipyv_rec ipyv_rec_type;
2640 l_iss_code VARCHAR2(30);
2641 l_init_msg_list VARCHAR2(1) ;
2642 l_return_status VARCHAR2(1);
2643 l_msg_count NUMBER ;
2644 l_msg_data VARCHAR2(2000);
2645 l_message VARCHAR2(2000);
2646 l_seq NUMBER;
2647 l_event_name varchar2(240) := 'oracle.apps.okl.ssc.cancelInsurance';
2648 l_yn VARCHAR2(1);
2649
2650 CURSOR policy_info_cur(p_polid IN NUMBER) IS
2651 SELECT iss_code
2652 FROM OKL_INS_POLICIES_B
2653 WHERE id = p_polid;
2654
2655 CURSOR wf_seq IS
2656 SELECT OKLSSC_WFITEMKEY_S.nextval
2657 FROM dual;
2658
2659
2660 api_exception EXCEPTION;
2661 begin
2662
2663 SAVEPOINT cancel_insurance;
2664
2665 l_return_status := OKC_API.G_RET_STS_SUCCESS ;
2666 -- Test if there are any active subscritions
2667 -- if it is the case then execute the subscriptions
2668 l_yn := exist_subscription(l_event_name);
2669
2670 IF l_yn = 'Y' THEN
2671
2672 open policy_info_cur(p_polid);
2673 fetch policy_info_cur into l_iss_code;
2674 close policy_info_cur;
2675
2676 open wf_seq;
2677 fetch wf_seq into l_seq;
2678 close wf_seq;
2679
2680 l_ipyv_rec.id := p_polid;
2681 l_ipyv_rec.crx_code :=p_canrsn_code;
2682 l_ipyv_rec.cancellation_comment := p_cancelcomment;
2683 l_ipyv_rec.cancellation_date := p_canceldate;
2684
2685 if (l_iss_code='PENDING' OR l_iss_code='ACCEPTED') THEN
2686 OKL_INSURANCE_POLICIES_PUB.delete_policy(
2687 p_api_version,
2688 p_init_msg_list => l_init_msg_list,
2689 x_return_status => l_return_status,
2690 x_msg_count => l_msg_count,
2691 x_msg_data => l_msg_data,
2692 p_ipyv_rec => l_ipyv_rec,
2693 x_ipyv_rec => lx_ipyv_rec);
2694 ELSE
2695 OKL_INSURANCE_POLICIES_PUB.cancel_policy(
2696 p_api_version,
2697 p_init_msg_list => l_init_msg_list,
2698 x_return_status => l_return_status,
2699 x_msg_count => l_msg_count,
2700 x_msg_data => l_msg_data,
2701 p_ipyv_rec => l_ipyv_rec,
2702 x_ipyv_rec => lx_ipyv_rec);
2703
2704 END IF;
2705
2706
2707 x_return_status := l_return_status ;
2708 x_msg_count := l_msg_count ;
2709 x_msg_data := l_msg_data ;
2710 IF (l_return_status='S') THEN
2711 WF_EVENT.raise2('oracle.apps.okl.ssc.cancelInsurance'
2712 ,l_event_name||l_seq, null
2713 ,'SSCINSPOLID', p_polid
2714 ,'SSCREQUESTORID',p_userid);
2715 END IF;
2716
2717 ELSE
2718 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
2719 FND_MSG_PUB.ADD;
2720 l_return_status := OKC_API.G_RET_STS_ERROR ;
2721
2722 x_return_status := l_return_status ;
2723 x_msg_count := l_msg_count ;
2724 x_msg_data := l_msg_data ;
2725
2726 END IF;
2727
2728 return;
2729 EXCEPTION
2730 when api_exception then
2731 x_return_status := l_return_status;
2732 x_msg_count := l_msg_count ;
2733 x_msg_data := l_msg_data ;
2734
2735 FND_MSG_PUB.Count_And_Get (
2736 p_encoded => FND_API.G_FALSE,
2737 p_count => x_msg_count,
2738 p_data => x_msg_data);
2739
2740
2741 WHEN OTHERS THEN
2742 x_return_status := l_return_status;
2743 x_msg_count := l_msg_count ;
2744 x_msg_data := l_msg_data ;
2745
2746 --FND_MSG_PUB.ADD_EXC_MSG( 'OKL_SSC_WF' , 'cancel_ins_wrapper_wf', itemtype, itemkey, 'c','d');
2747 FND_MSG_PUB.count_and_get(
2748 p_count => x_msg_count
2749 ,p_data => x_msg_data);
2750 ROLLBACK TO cancel_insurance;
2751 END cancel_ins_wrapper_wf;
2752
2753 ------------------------------------------------------------------------------------
2754 ---------- Calim Notification ---------------------------------------------------
2755 PROCEDURE set_claim_receiver
2756 (itemtype in varchar2,
2757 itemkey in varchar2,
2758 actid in number,
2759 funcmode in varchar2,
2760 resultout out nocopy varchar2)
2761 IS
2762
2763
2764
2765 CURSOR receiver_cur(respKey varchar2) IS
2766 SELECT responsibility_id
2767 FROM fnd_responsibility
2768 WHERE responsibility_key = respKey
2769 AND application_id = 540;
2770
2771
2772 CURSOR claim_info(claim_id VARCHAR2) IS
2773 SELECT IPYB.POLICY_NUMBER , ICMB.CLAIM_NUMBER
2774 FROM OKL_INS_POLICIES_B IPYB, OKL_INS_CLAIMS_B ICMB
2775 WHERE IPYB.ID = ICMB.IPY_ID
2776 AND ICMB.ID = claim_id;
2777
2778
2779 l_claim_number VARCHAR2(15);
2780 l_policy_number VARCHAR2(20);
2781 p_claim_id VARCHAR2(50);
2782 p_resp_key varchar2(30) := 'OKLCS';
2783 l_respString VARCHAR2(15) := 'FND_RESP540:';
2784 l_approver_name VARCHAR2(200) := 'FND_RESP540:0' ;
2785 l_approver_id NUMBER := 0 ;
2786
2787 api_exception EXCEPTION;
2788
2789 BEGIN
2790
2791 if ( funcmode = 'RUN' ) then
2792
2793 p_resp_key:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
2794 p_claim_id := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'CLAIM_ID');
2795
2796
2797 -- SET RECEIVER
2798 open receiver_cur(p_resp_key);
2799 fetch receiver_cur into l_approver_id;
2800 close receiver_cur;
2801 l_approver_name := l_respString||TO_CHAR(l_approver_id);
2802
2803 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSC_NTFRECIPIENT_ITMATTR', l_approver_name);
2804
2805 -- SET CLAIM NUMBER
2806 open claim_info(p_claim_id);
2807 fetch claim_info into l_policy_number,l_claim_number ;
2808
2809 IF (claim_info%NOTFOUND) THEN
2810 FND_MESSAGE.SET_NAME('OKL', 'OKL_INVALID_VALUE');
2811 FND_MSG_PUB.ADD;
2812 raise api_exception;
2813 END IF ;
2814
2815 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'POLICY_NUMBER', l_policy_number);
2816 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'CLAIM_NUMBER', l_claim_number);
2817
2818
2819
2820 close claim_info;
2821
2822
2823
2824 resultout := 'COMPLETE';
2825
2826 return;
2827 end if;
2828
2829
2830 if ( funcmode = 'CANCEL' ) then
2831
2832 resultout := 'COMPLETE';
2833 return;
2834 end if;
2835 if ( funcmode = 'RESPOND') then
2836 resultout := 'COMPLETE';
2837 return;
2838 end if;
2839 if ( funcmode = 'FORWARD') then
2840 resultout := 'COMPLETE';
2841 return;
2842 end if;
2843 if ( funcmode = 'TRANSFER') then
2844 resultout := 'COMPLETE';
2845 return;
2846 end if;
2847 if ( funcmode = 'TIMEOUT' ) then
2848 resultout := 'COMPLETE';
2849 else
2850 resultout := wf_engine.eng_timedout;
2851 return;
2852 end if;
2853
2854 exception
2855 when others then
2856 WF_CORE.CONTEXT ('OKL_SSC_WF', 'set_claim_receiver', itemtype, itemkey,actid,funcmode);
2857 resultout := 'ERROR';
2858 raise;
2859
2860 end set_claim_receiver;
2861
2862 --- Added by DKHANDEL
2863 PROCEDURE create_claim_event
2864 ( p_claim_id IN NUMBER,
2865 x_retrun_status OUT NOCOPY VARCHAR2)
2866 IS
2867 l_parameter_list wf_parameter_list_t;
2868 l_key varchar2(240);
2869 l_yn varchar2(1);
2870 l_event_name varchar2(240) := 'oracle.apps.okl.ssc.createinsuranceclaim';
2871 l_seq NUMBER ;
2872 BEGIN
2873
2874 SAVEPOINT create_claim_event;
2875
2876 x_retrun_status := OKC_API.G_RET_STS_SUCCESS ;
2877 -- Test if there are any active subscritions
2878 -- if it is the case then execute the subscriptions
2879 l_yn := exist_subscription(l_event_name);
2880 IF l_yn = 'Y' THEN
2881
2882 --Get the item key
2883 select OKLSSC_WFITEMKEY_S.nextval INTO l_seq FROM DUAL ;
2884 l_key := l_event_name ||l_seq ;
2885
2886 --Set Parameters
2887 wf_event.AddParameterToList('CLAIM_ID',TO_CHAR(p_claim_id),l_parameter_list);
2888
2889 -- Call it again if you have more than one parameter
2890 -- Keep data type (text) only
2891
2892 -- Raise Event
2893 -- It is overloaded function so use according to requirement
2894 wf_event.raise( p_event_name => l_event_name
2895 ,p_event_key => l_key
2896 ,p_parameters => l_parameter_list);
2897 l_parameter_list.DELETE;
2898
2899 ELSE
2900 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
2901 FND_MSG_PUB.ADD;
2902 x_retrun_status := OKC_API.G_RET_STS_ERROR ;
2903 END IF;
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
2907 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2908 FND_MSG_PUB.ADD;
2909 ROLLBACK TO create_claim_event;
2910 x_retrun_status := OKC_API.G_RET_STS_UNEXP_ERROR ;
2911
2912 END create_claim_event;
2913
2914 -- Start of comments
2915 --
2916 -- Procedure Name : load_mess
2917 -- Description : Private procedure to load messages into attributes
2918 -- Business Rules :
2919 -- Parameters :
2920 -- Version : 1.0
2921 -- End of comments
2922
2923 procedure load_mess( itemtype in varchar2,
2924 itemkey in varchar2) is
2925 i integer;
2926 j integer;
2927 begin
2928 j := NVL(FND_MSG_PUB.Count_Msg,0);
2929 if (j=0) then return; end if;
2930 if (j>9) then j:=9; end if;
2931 FOR I IN 1..J LOOP
2932 wf_engine.SetItemAttrText (itemtype => itemtype,
2933 itemkey => itemkey,
2934 aname => 'MESSAGE'||i,
2935 avalue => FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE ));
2936 END LOOP;
2937 end;
2938
2939
2940 -- Start of comments
2941 --
2942 -- Procedure Name : accept_renewal_quote
2943 -- Description : Public procedure to accept a renewal quote
2944 -- Business Rules :
2945 -- Parameters :
2946 -- Version : 1.0
2947 -- End of comments
2948
2949 procedure accept_renewal_quote(quote_id in number,
2950 contract_id in number,
2951 user_id in number,
2952 x_return_status OUT NOCOPY VARCHAR2,
2953 x_msg_count OUT NOCOPY NUMBER,
2954 x_msg_data OUT NOCOPY VARCHAR2) IS
2955
2956 l_currency_code VARCHAR2(3);
2957 chrvrec1 okl_okc_migration_pvt.chrv_rec_type;
2958 chrvrec2 okl_contract_pub.khrv_rec_type;
2959 xchrvrec1 okl_okc_migration_pvt.chrv_rec_type;
2960 xchrvrec2 okl_contract_pub.khrv_rec_type;
2961 api_exception EXCEPTION;
2962 l_obj_vers_number NUMBER;
2963 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
2964 p_trqv_rec okl_trx_requests_pub.trqv_rec_type;
2965
2966 CURSOR contract_info(p_id NUMBER) IS
2967 SELECT object_version_number, currency_code from okc_k_headers_b
2968 WHERE id = p_id;
2969
2970 CURSOR user_info(p_id NUMBER) IS
2971 SELECT user_name from fnd_user
2972 WHERE user_id = p_id;
2973
2974
2975
2976 begin
2977
2978 open contract_info(contract_id);
2979 fetch contract_info into l_obj_vers_number, l_currency_code;
2980 close contract_info;
2981
2982 p_trqv_rec.id := quote_id;
2983 p_trqv_rec.request_status_code := 'ACCEPTED';
2984 p_trqv_rec.dnz_khr_id := contract_id;
2985 p_trqv_rec.currency_code := l_currency_code;
2986
2987 --OKL_CS_LEASE_RENEWAL_PUB.update_trx_request(
2988 OKL_CS_LEASE_RENEWAL_PUB.update_lrnw_request(1.0,
2989 OKL_API.G_FALSE,
2990 x_return_status,
2991 x_msg_count,
2992 x_msg_data,
2993 p_trqv_rec,
2994 x_trqv_rec);
2995
2996 if (x_return_status <> 'S') then
2997 raise api_exception;
2998 end if;
2999
3000
3001
3002 chrvrec1.id := contract_id;
3003 chrvrec1.object_version_number := l_obj_vers_number;
3004 chrvrec1.sts_code := 'ABANDONED';
3005
3006 OKL_CONTRACT_PUB.update_contract_header(1.0,
3007 FND_API.G_FALSE,
3008 x_return_status,
3009 x_msg_count,
3010 x_msg_data,
3011 null,
3012 chrvrec1,
3013 chrvrec2,
3014 xchrvrec1,
3015 xchrvrec2);
3016
3017 if (x_return_status <> 'S') then
3018 raise api_exception;
3019 end if;
3020
3021 okl_cs_wf.raise_lease_renewal_event(quote_id);
3022
3023 EXCEPTION
3024
3025 when api_exception then
3026
3027 FND_MSG_PUB.Count_And_Get (
3028 p_encoded => FND_API.G_FALSE,
3029 p_count => x_msg_count,
3030 p_data => x_msg_data);
3031 raise;
3032 end accept_renewal_quote;
3033
3034 --Zhendi Added the procedure process_renewal_quote
3035
3036 -- Start of comments
3037 --
3038 -- Procedure Name : process_renewal_quote
3039 -- Description : Public procedure to accept or reject a renewal quote
3040 -- Business Rules :
3041 -- Parameters :
3042 -- Version : 1.0
3043 -- End of comments
3044
3045 procedure process_renewal_quote(quote_id in number,
3046 contract_id in number,
3047 user_id in number,
3048 status_mode in varchar2,
3049 x_return_status OUT NOCOPY VARCHAR2,
3050 x_msg_count OUT NOCOPY NUMBER,
3051 x_msg_data OUT NOCOPY VARCHAR2) IS
3052
3053 l_currency_code VARCHAR2(3);
3054 chrvrec1 okl_okc_migration_pvt.chrv_rec_type;
3055 chrvrec2 okl_contract_pub.khrv_rec_type;
3056 xchrvrec1 okl_okc_migration_pvt.chrv_rec_type;
3057 xchrvrec2 okl_contract_pub.khrv_rec_type;
3058 api_exception EXCEPTION;
3059 l_obj_vers_number NUMBER;
3060 x_trqv_rec okl_trx_requests_pub.trqv_rec_type;
3061 p_trqv_rec okl_trx_requests_pub.trqv_rec_type;
3062
3063 CURSOR contract_info(p_id NUMBER) IS
3064 SELECT object_version_number, currency_code from okc_k_headers_b
3065 WHERE id = p_id;
3066
3067 CURSOR user_info(p_id NUMBER) IS
3068 SELECT user_name from fnd_user
3069 WHERE user_id = p_id;
3070
3071
3072
3073 begin
3074
3075 open contract_info(contract_id);
3076 fetch contract_info into l_obj_vers_number, l_currency_code;
3077 close contract_info;
3078
3079 p_trqv_rec.id := quote_id;
3080 p_trqv_rec.request_status_code := status_mode; --'ACCEPTED' or 'REJECTED';
3081 p_trqv_rec.dnz_khr_id := contract_id;
3082 p_trqv_rec.currency_code := l_currency_code;
3083
3084 --OKL_CS_LEASE_RENEWAL_PUB.update_trx_request(
3085 OKL_CS_LEASE_RENEWAL_PUB.update_lrnw_request(1.0,
3086 OKL_API.G_FALSE,
3087 x_return_status,
3088 x_msg_count,
3089 x_msg_data,
3090 p_trqv_rec,
3091 x_trqv_rec);
3092
3093 if (x_return_status <> 'S') then
3094 raise api_exception;
3095 end if;
3096
3097
3098
3099 chrvrec1.id := contract_id;
3100 chrvrec1.object_version_number := l_obj_vers_number;
3101 chrvrec1.sts_code := 'ABANDONED';
3102
3103 OKL_CONTRACT_PUB.update_contract_header(1.0,
3104 FND_API.G_FALSE,
3105 x_return_status,
3106 x_msg_count,
3107 x_msg_data,
3108 null,
3109 chrvrec1,
3110 chrvrec2,
3111 xchrvrec1,
3112 xchrvrec2);
3113
3114 if (x_return_status <> 'S') then
3115 raise api_exception;
3116 end if;
3117
3118 okl_cs_wf.raise_lease_renewal_event(quote_id);
3119
3120 EXCEPTION
3121
3122 when api_exception then
3123
3124 FND_MSG_PUB.Count_And_Get (
3125 p_encoded => FND_API.G_FALSE,
3126 p_count => x_msg_count,
3127 p_data => x_msg_data);
3128 raise;
3129 end process_renewal_quote;
3130
3131 --End of procedure process_renewal_quote added by Zhendi
3132
3133 -- Make payment PL/SQL wrapper
3134 PROCEDURE make_payment_wrapper_wf
3135 (p_api_version IN NUMBER,
3136 p_init_msg_list IN VARCHAR2,
3137 p_invid IN NUMBER DEFAULT NULL,
3138 p_paymentamount IN NUMBER,
3139 p_paymentcurrency IN VARCHAR2,
3140 p_cctype IN VARCHAR2 DEFAULT NULL,
3141 p_expdate IN DATE DEFAULT NULL,
3142 p_ccnum IN VARCHAR2 DEFAULT NULL,
3143 p_ccname IN VARCHAR2 DEFAULT NULL,
3144 p_userid IN NUMBER,
3145 p_custid IN VARCHAR2 DEFAULT NULL, -- smoduga 4055222
3146 x_return_status OUT NOCOPY VARCHAR2,
3147 x_payment_ref_number OUT NOCOPY VARCHAR2,
3148 x_msg_count OUT NOCOPY NUMBER,
3149 x_msg_data OUT NOCOPY VARCHAR2,
3150 p_paymentdate IN DATE,
3151 p_conInv IN VARCHAR2 DEFAULT NULL,
3152 -- Begin - Additional Parameters included by Varangan- for Oracle Payments Uptake
3153 p_customer_trx_id IN NUMBER,
3154 p_customer_id IN NUMBER,
3155 p_customer_site_use_id IN NUMBER,
3156 p_payment_trxn_extension_id IN NUMBER,
3157 x_cash_receipt_id OUT NOCOPY NUMBER
3158 -- End - Payments Uptake
3159 )
3160 IS
3161 -------------------------------------------------------------------------------------
3162 pl_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
3163 xl_return_status VARCHAR2(1) := 'U';
3164 xl_msg_count NUMBER;
3165 xl_msg_data VARCHAR2(2000);
3166 pl_cons_bill_id NUMBER;
3167 pl_cons_bill_num VARCHAR2(90) default null;
3168 pl_currency_code VARCHAR2(30);
3169 pl_irm_id NUMBER DEFAULT NULL;
3170 pl_rcpt_amount NUMBER;
3171 pl_customer_id NUMBER;
3172 pl_commit VARCHAR2(1);
3173 pl_payment_amount NUMBER DEFAULT pl_rcpt_amount;
3174 pl_payment_date DATE DEFAULT TRUNC(SYSDATE);
3175 pl_payment_instrument VARCHAR2(15) DEFAULT 'CREDIT_CARD';
3176 --START: Fixed Bug 5697488
3177 pl_customer_bank_acct_id IBY_EXT_BANK_ACCOUNTS.EXT_BANK_ACCOUNT_ID%TYPE DEFAULT NULL;
3178 --END: Fixed Bug 5697488
3179 pl_account_holder_name VARCHAR2(15);
3180 pl_account_type VARCHAR2(15) default null;
3181 pl_expiration_date DATE;
3182 xl_payment_ref_number AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER%TYPE DEFAULT NULL;
3183 l_cust_id NUMBER;
3184
3185 -- Begin - Varangan- for Oracle Payments Uptake
3186 pl_customer_trx_id NUMBER;
3187 pl_customer_site_use_id NUMBER;
3188 pl_payment_trxn_extension_id NUMBER;
3189 xl_cash_receipt_id NUMBER;
3190 l_org_id NUMBER := mo_global.get_current_org_id();
3191
3192 -- Get receipt method id
3193 CURSOR c_get_irm(lorg_id Number) IS
3194 SELECT ccard_remittance_id rm_id
3195 FROM okl_system_params_all
3196 WHERE org_id = lorg_id ;
3197 -- Get site use id
3198 Cursor c_get_site_use (l_inv_id Number)
3199 IS
3200 Select bill_to_site_use_id site_use_id
3201 from ra_customer_trx
3202 where customer_Trx_id = l_inv_id;
3203
3204 -- End - Payments Uptake
3205 ------------------------------------------------------------------------------------
3206 make_payment_error EXCEPTION;
3207
3208 begin
3209 SAVEPOINT make_payment;
3210
3211 -- Commented for Make Payment change
3212 /*
3213 select cust_account_id INTO l_cust_id
3214 from HZ_CUST_ACCOUNTS
3215 where account_number = p_custid;--Smoduga removed char conversion
3216 --as p_cust_id is of type varchar2.4055222
3217 --modified by akrangan on 28.7.06 MOAC Changes
3218 pl_irm_id := OKL_SYSTEM_PARAMS_ALL_PUB.get_system_param_value(OKL_SYSTEM_PARAMS_ALL_PUB.G_CCARD_REMITTANCE_ID) ; */
3219
3220 For K In c_get_irm(l_org_id)
3221 Loop
3222 pl_irm_id:=K.rm_id;
3223 End Loop;
3224
3225 pl_currency_code := p_paymentcurrency ;
3226 pl_cons_bill_id := p_invid ;
3227 pl_payment_amount := p_paymentamount;
3228 pl_payment_date := p_paymentdate;
3229 pl_account_holder_name := p_ccname;
3230 pl_expiration_date := p_expdate;
3231 pl_cons_bill_num := p_conInv;
3232
3233 -- Begin - Varangan- for Oracle Payments Uptake
3234 pl_customer_trx_id:=p_customer_trx_id;
3235 pl_customer_id :=p_customer_id;
3236 pl_customer_site_use_id :=p_customer_site_use_id;
3237 If pl_customer_site_use_id Is Null Then
3238 FOR I In c_get_site_use(pl_customer_trx_id)
3239 Loop
3240 pl_customer_site_use_id := I.site_use_id;
3241 End Loop;
3242 End If;
3243 pl_payment_trxn_extension_id := p_payment_trxn_extension_id;
3244 -- End - Payments Uptake
3245
3246 -- Call to OKL_PAYMENT_PUB.CREATEPAYMENTS From OKL_SSC_WF Start
3247
3248 -- Start of wraper code generated automatically by Debug code generator for okl_setupfunctions_pvt.get_rec
3249 IF(L_DEBUG_ENABLED='Y') THEN
3250 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
3251 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
3252 END IF;
3253 IF(IS_DEBUG_PROCEDURE_ON) THEN
3254 BEGIN
3255 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLSSWFB.pls call OKL_PAYMENT_PUB.CREATE_PAYMENTS ');
3256 END;
3257 END IF;
3258 --commented out the call to eliminate receipt dependency by dkagrawa
3259 -- Begin - Make Payment - varangan
3260 /* okl_payment_pub.CREATE_PAYMENTS(
3261 p_api_version => p_api_version,
3262 p_init_msg_list => pl_init_msg_list,
3263 p_commit => pl_commit,
3264 x_return_status => xl_return_status,
3265 x_msg_count => xl_msg_count,
3266 x_msg_data => xl_msg_data,
3267 p_currency_code => p_paymentcurrency,
3268 p_irm_id => pl_irm_id,
3269 p_payment_amount => p_paymentamount,
3270 p_customer_id => pl_customer_id,
3271 p_payment_date => p_paymentdate,
3272 x_payment_ref_number => xl_payment_ref_number,
3273 p_customer_trx_id =>pl_customer_trx_id,
3274 p_customer_site_use_id =>pl_customer_site_use_id,
3275 p_payment_trxn_extension_id =>pl_payment_trxn_extension_id,
3276 x_cash_receipt_id => xl_cash_receipt_id
3277 ); */
3278
3279 -- End - Make Payment - varangan
3280
3281 -- Call to OKL_PAYMENT_PUB.CREATEPAYMENTS From OKL_SSC_WF End
3282
3283 xl_return_status := nvl(xl_return_status,OKL_API.G_RET_STS_UNEXP_ERROR);
3284
3285 IF(IS_DEBUG_PROCEDURE_ON) THEN
3286 BEGIN
3287 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLSSWFB.pls call OKL_PAYMENT_PUB.CREATE_PAYMENTS ');
3288 END;
3289 END IF;
3290 -- End of wraper code generated automatically by Debug code generator for okl_setupfunctions_pvt.get_rec
3291 -- check for errors
3292 IF xl_return_status <> OKL_API.G_RET_STS_SUCCESS then
3293 xl_msg_data := 'OKL_PAYMENT_CREATE_FAILED';
3294 RAISE make_payment_error;
3295
3296 END IF;
3297
3298 -- Return the Payment Reference Number to display in the Make Payment
3299 -- confirmation page
3300 -- Assign value to OUT variables
3301
3302 x_payment_ref_number := xl_payment_ref_number;
3303 x_return_status := xl_return_status;
3304 x_cash_receipt_id :=xl_cash_receipt_id;
3305 x_msg_count :=xl_msg_count;
3306 x_msg_data :=xl_msg_data;
3307 return;
3308
3309 EXCEPTION
3310 when make_payment_error then
3311 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3312 END make_payment_wrapper_wf;
3313
3314
3315 --Make Payment Event function to set attributes
3316
3317 PROCEDURE make_payment_set_attr_wf
3318 (itemtype in varchar2,
3319 itemkey in varchar2,
3320 actid in number,
3321 funcmode in varchar2,
3322 resultout out nocopy varchar2 )
3323 IS
3324
3325 CURSOR cust_id_info_cur(p_payment_id IN NUMBER) IS
3326 SELECT pay_from_customer cust_account_id
3327 FROM ar_cash_receipts_all
3328 WHERE cash_receipt_id = p_payment_id;
3329
3330 CURSOR cust_name_info_cur(p_cust_id IN NUMBER) IS
3331 SELECT account_name
3332 FROM hz_cust_accounts
3333 WHERE cust_account_id = p_cust_id;
3334
3335 CURSOR requestor_info_cur(p_requestor_id IN NUMBER) IS
3336 SELECT user_name from fnd_user
3337 WHERE user_id = p_requestor_id;
3338
3339
3340 l_payment_id VARCHAR2(40);
3341 l_inv_number VARCHAR2(80);
3342 l_cust_id VARCHAR2(40);
3343 l_cust_name VARCHAR2(100);
3344 l_payment_number VARCHAR2(80);
3345 l_requestor_id VARCHAR2(40);
3346 l_payee_role VARCHAR2(30);
3347 l_resp_id NUMBER;
3348 l_respString VARCHAR2(15) := 'FND_RESP540:';
3349 l_payment_ref_number VARCHAR2(30);
3350
3351
3352 api_exception EXCEPTION;
3353
3354 begin
3355
3356 if ( funcmode = 'RUN' ) then
3357
3358 --Read attributes from WorkFlow
3359 l_payment_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCPAYMENTID');
3360 l_requestor_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID');
3361 l_payment_number:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCPAYMENTREFNUMBER');
3362
3363
3364 --Read from table
3365 open cust_id_info_cur(l_payment_id);
3366 fetch cust_id_info_cur into l_cust_id;
3367 close cust_id_info_cur;
3368
3369 open cust_name_info_cur(l_cust_id);
3370 fetch cust_name_info_cur into l_cust_name;
3371 close cust_name_info_cur;
3372
3373
3374 l_payee_role := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCPAYEEROLE');
3375
3376 SELECT responsibility_id
3377 into l_resp_id
3378 FROM fnd_responsibility
3379 WHERE responsibility_key = l_payee_role
3380 AND application_id = 540;
3381
3382 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCACCOUNTNAME', l_cust_name );
3383 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPAYMENTNUM', l_payment_number);
3384 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPAYEEPERFORMER', l_respString || l_resp_id );
3385
3386 resultout := 'COMPLETE';
3387
3388 return;
3389 end if;
3390
3391
3392 if ( funcmode = 'CANCEL' ) then
3393 resultout := 'COMPLETE';
3394 return;
3395 end if;
3396 if ( funcmode = 'RESPOND') then
3397 resultout := 'COMPLETE';
3398 return;
3399 end if;
3400 if ( funcmode = 'FORWARD') then
3401 resultout := 'COMPLETE';
3402 return;
3403 end if;
3404 if ( funcmode = 'TRANSFER') then
3405 resultout := 'COMPLETE';
3406 return;
3407 end if;
3408 if ( funcmode = 'TIMEOUT' ) then
3409 resultout := 'COMPLETE';
3410 else
3411 resultout := wf_engine.eng_timedout;
3412 return;
3413 end if;
3414
3415 exception
3416 when others then
3417 WF_CORE.CONTEXT ('OKL_SSC_WF', 'make_payment_set_attr_wf', itemtype, itemkey,'actid','funcmode');
3418 raise;
3419 END make_payment_set_attr_wf;
3420
3421 --IBYON added on OCT-01-2002 wrapper to call party api and validate parties for termination quote
3422 --IBYON added on OCT-10-2002 x_cpl_id and x_email_address just in case there are no recipient
3423 PROCEDURE validate_recipient_term_quote
3424 (p_api_version IN NUMBER,
3425 p_init_msg_list IN VARCHAR2,
3426 p_khrid IN number,
3427 p_qrs_code IN VARCHAR2,
3428 p_qtp_code IN VARCHAR2,
3429 p_comments IN VARCHAR2,
3430 x_vendor_flag OUT NOCOPY VARCHAR2,
3431 x_lessee_flag OUT NOCOPY VARCHAR2,
3432 x_cpl_id OUT NOCOPY VARCHAR2,
3433 x_email_address OUT NOCOPY VARCHAR2,
3434 x_return_status OUT NOCOPY VARCHAR2,
3435 x_msg_count OUT NOCOPY NUMBER,
3436 x_msg_data OUT NOCOPY VARCHAR2
3437 )
3438 IS
3439 l_qtev_rec qtev_rec_type;
3440 l_qpyv_tbl qpyv_tbl_type;
3441 l_q_party_uv_tbl q_party_uv_tbl_type;
3442 l_record_count NUMBER;
3443 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3444 l_recipient_exist_flag VARCHAR2(1) :='N';
3445 l_api_version CONSTANT NUMBER := 1.0;
3446 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
3447 l_msg_data VARCHAR2(2000);
3448 i NUMBER;
3449 api_exception EXCEPTION;
3450 --Fixed Bug # 5484903
3451 CURSOR cpl_id_cur( p_khr_id IN NUMBER) IS
3452 --Fixed Bug 5484309
3453 select id from okc_k_party_roles_b
3454 where dnz_chr_id = p_khr_id
3455 AND chr_id = dnz_chr_id
3456 and rle_code='LESSEE';
3457
3458 CURSOR email_address_cur(p_user_id IN NUMBER) IS
3459 SELECT email_address from fnd_user
3460 WHERE user_id = p_user_id;
3461
3462 begin
3463
3464 l_qtev_rec.khr_id := p_khrid;
3465 l_qtev_rec.qrs_code := p_qrs_code;
3466 l_qtev_rec.qtp_code := p_qtp_code;
3467 l_qtev_rec.comments := p_comments;
3468
3469 x_vendor_flag := 'N';
3470 x_lessee_flag := 'N';
3471 OKL_AM_PARTIES_PVT.fetch_rule_quote_parties( p_api_version,
3472 p_init_msg_list,
3473 l_msg_count,
3474 l_msg_data,
3475 l_return_status,
3476 l_qtev_rec,
3477 l_qpyv_tbl,
3478 l_q_party_uv_tbl,
3479 l_record_count);
3480
3481 -- Validate whether one of the party is vendor
3482 IF l_qpyv_tbl IS NOT NULL THEN
3483 IF l_qpyv_tbl.count > 0 THEN
3484 FOR i IN l_qpyv_tbl.first..l_qpyv_tbl.last LOOP
3485 IF l_qpyv_tbl(i).qpt_code = 'RECIPIENT' OR
3486 l_qpyv_tbl(i).qpt_code = 'RECIPIENT_ADDITIONAL' THEN
3487 l_recipient_exist_flag :='Y';
3488 IF l_q_party_uv_tbl(i).kp_role_code = 'OKL_VENDOR' THEN
3489 x_vendor_flag := 'Y';
3490 END IF;
3491 IF l_q_party_uv_tbl(i).kp_role_code = 'LESSEE' THEN
3492 x_lessee_flag := 'Y';
3493 END IF;
3494 END IF;
3495 END LOOP;
3496 END IF;
3497 END IF;
3498
3499 -- If there is no recipient in parties then we need to return party id and
3500 -- email address of requestor
3501
3502 IF l_recipient_exist_flag = 'N' THEN
3503 open cpl_id_cur(p_khrid);
3504 fetch cpl_id_cur into x_cpl_id;
3505 close cpl_id_cur;
3506
3507 open email_address_cur(FND_GLOBAL.USER_ID);
3508 fetch email_address_cur into x_email_address;
3509 close email_address_cur;
3510 END IF;
3511
3512
3513 x_return_status := l_return_status;
3514 x_msg_count := l_msg_count ;
3515 x_msg_data := l_msg_data ;
3516
3517 EXCEPTION
3518 when api_exception then
3519 x_return_status := l_return_status;
3520 x_msg_count := l_msg_count ;
3521 x_msg_data := l_msg_data ;
3522
3523 FND_MSG_PUB.Count_And_Get (
3524 p_encoded => FND_API.G_FALSE,
3525 p_count => x_msg_count,
3526 p_data => x_msg_data);
3527
3528
3529 WHEN OTHERS THEN
3530 x_return_status := l_return_status;
3531 x_msg_count := l_msg_count ;
3532 x_msg_data := l_msg_data ;
3533
3534 FND_MSG_PUB.count_and_get(
3535 p_count => x_msg_count
3536 ,p_data => x_msg_data);
3537
3538 END validate_recipient_term_quote;
3539
3540 -- IBYON added on 01-OCT-2002 to raise event for termination quote
3541 PROCEDURE create_termqt_raise_event_wf
3542 (p_qte_id IN NUMBER,
3543 p_user_id IN VARCHAR2,
3544 x_return_status OUT NOCOPY VARCHAR2,
3545 x_msg_count OUT NOCOPY NUMBER,
3546 x_msg_data OUT NOCOPY VARCHAR2)
3547 IS
3548 l_yn VARCHAR2(1);
3549 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3550 l_event_name varchar2(240) := 'oracle.apps.okl.ssc.createterminationquote';
3551 l_msg_count NUMBER ;
3552 l_msg_data VARCHAR2(2000);
3553 l_message VARCHAR2(2000);
3554 l_seq NUMBER;
3555 api_exception EXCEPTION;
3556 begin
3557 l_yn := exist_subscription(l_event_name);
3558 SELECT OKLSSC_WFITEMKEY_S.nextval into l_seq
3559 FROM dual;
3560
3561 IF l_yn = 'N' THEN
3562 FND_MESSAGE.SET_NAME('OKL', 'OKL_NO_EVENT');
3563 FND_MSG_PUB.ADD;
3564 l_return_status := OKC_API.G_RET_STS_ERROR;
3565 raise api_exception;
3566 ELSE
3567 WF_EVENT.raise2(l_event_name,
3568 l_event_name||l_seq,
3569 null,
3570 'SSCQUOTEID', p_qte_id,
3571 'SSCREQUESTORID',p_user_id);
3572 END IF;
3573 x_return_status :=l_return_status;
3574 x_msg_count:=l_msg_count;
3575 x_msg_data := l_msg_data;
3576 EXCEPTION
3577 when api_exception then
3578 x_return_status := l_return_status;
3579 x_msg_count := l_msg_count ;
3580 x_msg_data := l_msg_data ;
3581
3582 FND_MSG_PUB.Count_And_Get (
3583 p_encoded => FND_API.G_FALSE,
3584 p_count => x_msg_count,
3585 p_data => x_msg_data);
3586
3587
3588 WHEN OTHERS THEN
3589 x_return_status := l_return_status;
3590 x_msg_count := l_msg_count ;
3591 x_msg_data := l_msg_data ;
3592
3593 FND_MSG_PUB.count_and_get(
3594 p_count => x_msg_count
3595 ,p_data => x_msg_data);
3596
3597 END create_termqt_raise_event_wf;
3598
3599
3600 -- IBYON added on 01-OCT-2002 to set attributes for termination quote notification
3601 PROCEDURE create_termqt_set_attr_wf
3602 (itemtype in varchar2,
3603 itemkey in varchar2,
3604 actid in number,
3605 funcmode in varchar2,
3606 resultout out nocopy varchar2 )
3607 IS
3608
3609 CURSOR contract_info_cur( p_khr_id IN NUMBER) IS
3610 SELECT contract_number from okc_k_headers_b
3611 WHERE id = p_khr_id;
3612
3613 CURSOR quote_info_cur(p_qte_id IN NUMBER) IS
3614 SELECT quote_number, quote_type_description,
3615 quote_reason_description, comments, khr_id
3616 from okl_am_quotes_uv
3617 WHERE id = p_qte_id;
3618
3619 CURSOR requestor_info_cur(p_requestor_id IN NUMBER) IS
3620 SELECT user_name from fnd_user
3621 WHERE user_id = p_requestor_id;
3622
3623
3624 l_chr_id VARCHAR2(40);
3625 l_qte_id VARCHAR2(40);
3626 l_requestor_id VARCHAR2(40);
3627 l_chr_number VARCHAR2(120);
3628 l_qte_number VARCHAR2(20);
3629 l_qtp VARCHAR2(80);
3630 l_qrs VARCHAR2(80);
3631 l_user_name VARCHAR2(100);
3632 l_comments VARCHAR2(1995);
3633 l_resp_id VARCHAR2(15);
3634 l_resp_key VARCHAR2(30);
3635 l_performer VARCHAR2(27);
3636 l_respString VARCHAR2(12):='FND_RESP540:';
3637
3638 api_exception EXCEPTION;
3639
3640 begin
3641
3642 if ( funcmode = 'RUN' ) then
3643
3644 --Read attributes from WorkFlow
3645 l_qte_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCQUOTEID');
3646 l_requestor_id:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCREQUESTORID');
3647 l_resp_key:=WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SSCRESPONSIBILITYKEY');
3648
3649
3650 --Read from table
3651 open quote_info_cur(l_qte_id);
3652 fetch quote_info_cur into l_qte_number,l_qtp, l_qrs, l_comments,l_chr_id ;
3653 close quote_info_cur;
3654
3655 open contract_info_cur(l_chr_id);
3656 fetch contract_info_cur into l_chr_number;
3657 close contract_info_cur;
3658
3659 open requestor_info_cur(l_requestor_id);
3660 fetch requestor_info_cur into l_user_name;
3661 close requestor_info_cur;
3662
3663
3664 SELECT responsibility_id into l_resp_id
3665 FROM fnd_responsibility
3666 WHERE responsibility_key = l_resp_key
3667 AND application_id = 540;
3668
3669 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCONTRACTNUMBER', l_chr_number );
3670 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCREQUESTORNAME', l_user_name);
3671 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCQUOTENUMBER', l_qte_number);
3672 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCQTP', l_qtp);
3673 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCQRS', l_qrs);
3674 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCCOMMENTS', l_comments);
3675 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'SSCPERFORMER', l_respString||l_resp_id);
3676
3677 resultout := 'COMPLETE';
3678
3679 return;
3680 end if;
3681
3682
3683 if ( funcmode = 'CANCEL' ) then
3684 resultout := 'COMPLETE';
3685 return;
3686 end if;
3687 if ( funcmode = 'RESPOND') then
3688 resultout := 'COMPLETE';
3689 return;
3690 end if;
3691 if ( funcmode = 'FORWARD') then
3692 resultout := 'COMPLETE';
3693 return;
3694 end if;
3695 if ( funcmode = 'TRANSFER') then
3696 resultout := 'COMPLETE';
3697 return;
3698 end if;
3699 if ( funcmode = 'TIMEOUT' ) then
3700 resultout := 'COMPLETE';
3701 else
3702 resultout := wf_engine.eng_timedout;
3703 return;
3704 end if;
3705
3706 exception
3707 when others then
3708 WF_CORE.CONTEXT (G_PKG_NAME, 'create_termqt_set_attr_wf', itemtype, itemkey);
3709 raise;
3710 END create_termqt_set_attr_wf;
3711
3712 -- procedure : raise_assets_return_event
3713 -- Comments: Raises the assets return event
3714 -- Created by: viselvar
3715 -- version :1
3716 -- Fix for bug 4754894
3717 procedure raise_assets_return_event ( p_event_name in varchar2 ,
3718 requestId in varchar2,
3719 requestorId in varchar2,
3720 requestType in varchar2
3721 ) IS
3722
3723 x_return_status VARCHAR2(1);
3724 l_api_version NUMBER:=1.0;
3725 x_msg_count NUMBER;
3726 x_msg_data VARCHAR2(4000);
3727 l_parameter_list wf_parameter_list_t;
3728 l_api_name VARCHAR2(40):= 'okl_ssc_wf';
3729
3730 begin
3731
3732 -- pass the parameters to the event
3733 wf_event.addparametertolist('SSCREQUESTORID'
3734 ,requestorId
3735 ,l_parameter_list);
3736 wf_event.addparametertolist('SSCTASID'
3737 ,requestId
3738 ,l_parameter_list);
3739
3740 wf_event.addparametertolist('SSCTALTYPE'
3741 ,requestType
3742 ,l_parameter_list);
3743
3744
3745 okl_wf_pvt.raise_event(p_api_version => l_api_version
3746 ,p_init_msg_list => 'T'
3747 ,x_return_status => x_return_status
3748 ,x_msg_count => x_msg_count
3749 ,x_msg_data => x_msg_data
3750 ,p_event_name => p_event_name
3751 ,p_parameters => l_parameter_list);
3752
3753 exception
3754 WHEN OTHERS THEN
3755 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3756 (
3757 l_api_name,
3758 G_PKG_NAME,
3759 'OTHERS',
3760 x_msg_count,
3761 x_msg_data,
3762 ''
3763 );
3764 end raise_assets_return_event;
3765
3766 END okl_ssc_wf;