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