[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_ASSET_RETURN_WF
Source
1 PACKAGE BODY OKL_AM_ASSET_RETURN_WF AS
2 /* $Header: OKLRRWFB.pls 120.17 2011/09/19 04:50:06 vsgandhi ship $ */
3
4 -- Start of comments
5 --
6 -- Procedure Name : check_repo_request
7 -- Description : validate repossession request from WF
8 -- Business Rules :
9 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
10 -- Version : 1.0
11 --
12 -- End of comments
13 PROCEDURE check_repo_request( itemtype IN VARCHAR2,
14 itemkey IN VARCHAR2,
15 actid IN NUMBER,
16 funcmode IN VARCHAR2,
17 resultout OUT NOCOPY VARCHAR2 )IS
18
19 l_art_id NUMBER;
20 l_knt NUMBER;
21
22 -- cursor to check request is valid
23 CURSOR okl_check_req_csr(c_art_id NUMBER)
24 IS
25 SELECT count(*)
26 FROM OKL_ASSET_RETURNS_V
27 WHERE ID= c_art_id;
28
29 BEGIN
30
31 IF (funcmode = 'RUN') THEN
32
33 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
34 itemkey => itemkey,
35 aname => 'TRANSACTION_ID');
36
37 OPEN okl_check_req_csr(l_art_id);
38 FETCH okl_check_req_csr INTO l_knt;
39 CLOSE okl_check_req_csr;
40
41 IF l_knt = 0 THEN
42 resultout := 'COMPLETE:INVALID_RETURN';
43 ELSE
44 resultout := 'COMPLETE:VALID_RETURN';
45 END IF;
46
47 RETURN ;
48
49 END IF;
50 --
51 -- CANCEL mode
52 --
53 IF (funcmode = 'CANCEL') THEN
54 --
55 resultout := 'COMPLETE:';
56 RETURN;
57 --
58 END IF;
59 --
60 -- TIMEOUT mode
61 --
62 IF (funcmode = 'TIMEOUT') THEN
63 --
64 resultout := 'COMPLETE:';
65 RETURN;
66 --
67 END IF;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 IF okl_check_req_csr%ISOPEN THEN
72 CLOSE okl_check_req_csr;
73 END IF;
74
75 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_repo_request', itemtype, itemkey, actid, funcmode);
76 RAISE;
77
78 END check_repo_request;
79
80 -- Start of comments
81 --
82 -- Procedure Name : check_remk_assign
83 -- Description : validate remarketer assignement from WF
84 -- Business Rules :
85 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
86 -- Version : 1.0
87 --
88 -- End of comments
89 PROCEDURE check_remk_assign ( itemtype IN VARCHAR2,
90 itemkey IN VARCHAR2,
91 actid IN NUMBER,
92 funcmode IN VARCHAR2,
93 resultout OUT NOCOPY VARCHAR2 )IS
94
95 l_art_id NUMBER;
96 l_knt NUMBER;
97
98 -- cursor to check request is valid
99 CURSOR okl_check_req_csr(c_art_id NUMBER)
100 IS
101 SELECT count(*)
102 FROM OKL_ASSET_RETURNS_V OARV,
103 OKL_AM_REMARKET_TEAMS_UV ORTU
104 WHERE OARV.ID= c_art_id
105 AND OARV.RMR_ID = ORTU.ORIG_SYSTEM_ID;
106
107 BEGIN
108
109 IF (funcmode = 'RUN') THEN
110
111 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
112 itemkey => itemkey,
113 aname => 'TRANSACTION_ID');
114
115 OPEN okl_check_req_csr(l_art_id);
116 FETCH okl_check_req_csr INTO l_knt;
117 CLOSE okl_check_req_csr;
118
119 IF l_knt = 0 THEN
120 resultout := 'COMPLETE:REMARKETER_NOT_ASSIGNED';
121 ELSE
122 resultout := 'COMPLETE:REMARKETER_ASSIGNED';
123 END IF;
124
125 RETURN ;
126
127 END IF;
128 --
129 -- CANCEL mode
130 --
131 IF (funcmode = 'CANCEL') THEN
132 --
133 resultout := 'COMPLETE:';
134 RETURN;
135 --
136 END IF;
137 --
138 -- TIMEOUT mode
139 --
140 IF (funcmode = 'TIMEOUT') THEN
141 --
142 resultout := 'COMPLETE:';
143 RETURN;
144 --
145 END IF;
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 IF okl_check_req_csr%ISOPEN THEN
150 CLOSE okl_check_req_csr;
151 END IF;
152
153 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_remk_assign', itemtype, itemkey, actid, funcmode);
154 RAISE;
155
156 END check_remk_assign;
157
158 -- Start of comments
159 --
160 -- Procedure Name : populate_notification_attribs
161 -- Description : populate collections agent notification attributes from WF
162 -- Business Rules :
163 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
164 -- Version : 1.0
165 --
166 -- End of comments
167 PROCEDURE populate_notification_attribs(itemtype IN VARCHAR2,
168 itemkey IN VARCHAR2,
169 actid IN NUMBER,
170 funcmode IN VARCHAR2,
171 p_art_id IN NUMBER) AS
172
173 l_art_id NUMBER;
174 l_no_data_found BOOLEAN;
175 l_user WF_USERS.NAME%TYPE;
176 l_name WF_USERS.DISPLAY_NAME%TYPE;
177 -- cursor to populate notification attributes
178 --Bug # 6174484 ssdeshpa Fixed for SQL Performance Start
179 CURSOR okl_asset_return_csr(c_art_id NUMBER)
180 IS
181 /* SELECT OAR.LAST_UPDATED_BY, AD.CHR_ID CHR_ID,
182 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
183 AD.CONTRACT_NUMBER CONTRACT_NUMBER, AD.NAME ASSET_NUMBER,
184 AD.SERIAL_NUMBER SERIAL_NUMBER, AD.MODEL_NUMBER MODEL_NUMBER,
185 OAR.DATE_REPOSSESSION_ACTUAL DATE_RETURNED, AD.ID KLE_ID,
186 OAR.COMMENTS COMMENTS
187 FROM
188 OKL_AM_ASSET_DETAILS_UV AD,
189 OKL_ASSET_RETURNS_V OAR
190 WHERE
191 AD.ID = OAR.KLE_ID
192 AND oar.id = c_art_id; */
193 SELECT
194 OAR.LAST_UPDATED_BY ,
195 CLEV.CHR_ID CHR_ID ,
196 CLEV.ITEM_DESCRIPTION ASSET_DESCRIPTION ,
197 OKHV.CONTRACT_NUMBER CONTRACT_NUMBER ,
198 CLEV.NAME ASSET_NUMBER ,
199 OALV.SERIAL_NUMBER SERIAL_NUMBER ,
200 OALV.MODEL_NUMBER MODEL_NUMBER ,
201 OAR.DATE_REPOSSESSION_ACTUAL DATE_RETURNED ,
202 CLEV.ID KLE_ID ,
203 OAR.RMR_ID RMR_ID,
204 OAR.COMMENTS COMMENTS
205 FROM OKC_K_LINES_V CLEV
206 ,OKX_ASSET_LINES_V OALV
207 ,OKC_K_HEADERS_ALL_B OKHV ,
208 OKL_ASSET_RETURNS_V OAR
209 WHERE CLEV.ID = OAR.KLE_ID
210 AND CLEV.ID = OALV.PARENT_LINE_ID(+)
211 AND CLEV.CHR_ID = OKHV.ID
212 AND CLEV.STS_CODE <> 'ABANDONED'
213 AND OAR.ID = c_art_id;
214
215 --Bug # 6174484 ssdeshpa Fixed for SQL Performance End
216 l_asset_return okl_asset_return_csr%rowtype;
217
218 BEGIN
219 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
220 itemkey => itemkey,
221 aname => 'TRANSACTION_ID');
222
223 OPEN okl_asset_return_csr(l_art_id);
224 FETCH okl_asset_return_csr INTO l_asset_return;
225 CLOSE okl_asset_return_csr;
226
227 okl_am_wf.get_notification_agent(
228 itemtype => itemtype
229 , itemkey => itemkey
230 , actid => actid
231 , funcmode => funcmode
232 , p_user_id => l_asset_return.last_updated_by
233 , x_name => l_user
234 , x_description => l_name);
235
236 wf_engine.SetItemAttrText ( itemtype=> itemtype,
237 itemkey => itemkey,
238 aname => 'REQUESTER',
239 avalue => l_user);
240
241 wf_engine.SetItemAttrText ( itemtype=> itemtype,
242 itemkey => itemkey,
243 aname => 'KHR_ID',
244 avalue => to_char(l_asset_return.CHR_ID));
245
246 wf_engine.SetItemAttrText ( itemtype=> itemtype,
247 itemkey => itemkey,
248 aname => 'CONTRACT_NUMBER',
249 avalue => l_asset_return.CONTRACT_NUMBER);
250
251 wf_engine.SetItemAttrText ( itemtype=> itemtype,
252 itemkey => itemkey,
253 aname => 'ASSET_NUMBER',
254 avalue => l_asset_return.ASSET_NUMBER);
255
256 wf_engine.SetItemAttrText ( itemtype=> itemtype,
257 itemkey => itemkey,
258 aname => 'DATE_RETURNED',
259 avalue => to_char(l_asset_return.DATE_RETURNED));
260
261 wf_engine.SetItemAttrText ( itemtype=> itemtype,
262 itemkey => itemkey,
263 aname => 'KLE_ID',
264 avalue => to_char(l_asset_return.KLE_ID));
265
266 wf_engine.SetItemAttrText ( itemtype=> itemtype,
267 itemkey => itemkey,
268 aname => 'CREATED_BY',
269 avalue => to_char(l_asset_return.last_updated_by));
270
271 wf_engine.SetItemAttrText ( itemtype=> itemtype,
272 itemkey => itemkey,
273 aname => 'ASSET_DESCRIPTION',
274 avalue => l_asset_return.asset_description);
275
276 wf_engine.SetItemAttrText ( itemtype=> itemtype,
277 itemkey => itemkey,
278 aname => 'COMMENTS',
279 avalue => l_asset_return.comments);
280
281 wf_engine.SetItemAttrText ( itemtype=> itemtype,
282 itemkey => itemkey,
283 aname => 'SERIAL_NUMBER',
284 avalue => l_asset_return.serial_number);
285
286 wf_engine.SetItemAttrText ( itemtype=> itemtype,
287 itemkey => itemkey,
288 aname => 'MODEL_NUMBER',
289 avalue => l_asset_return.model_number);
290
291 wf_engine.SetItemAttrText ( itemtype=> itemtype,
292 itemkey => itemkey,
293 aname => 'DISPLAY_NAME',
294 avalue => l_name);
295 EXCEPTION
296 WHEN OTHERS THEN
297 IF okl_asset_return_csr%ISOPEN THEN
298 CLOSE okl_asset_return_csr;
299 END IF;
300
301 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'populate_notification_attribs', itemtype, itemkey, actid, funcmode);
302 RAISE;
303 END populate_notification_attribs;
304
305 -- Start of comments
306 --
307 -- Procedure Name : pop_repo_notify_att
308 -- Description : populate repossession notification attributes from WF
309 -- Business Rules :
310 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
311 -- Version : 1.0
312 --
313 -- End of comments
314 PROCEDURE POP_REPO_NOTIFY_ATT( itemtype IN VARCHAR2,
315 itemkey IN VARCHAR2,
316 actid IN NUMBER,
317 funcmode IN VARCHAR2,
318 resultout OUT NOCOPY VARCHAR2 ) AS
319
320 l_art_id NUMBER;
321 l_no_data_found BOOLEAN;
322 l_user WF_USERS.NAME%TYPE;
323 l_name WF_USERS.DESCRIPTION%TYPE;
324
325 -- cursor to populate notification attributes
326 CURSOR okl_asset_return_csr(c_art_id NUMBER)
327 IS
328 SELECT OAR.LAST_UPDATED_BY, KLE.CHR_ID CHR_ID,
329 OKC.CONTRACT_NUMBER CONTRACT_NUMBER, KLE.NAME ASSET_NUMBER,
330 OAR.DATE_RETURNED DATE_RETURNED, KLE.ID KLE_ID, OAR.RNA_ID AGENT_ID
331 FROM OKL_K_LINES_FULL_V KLE,
332 OKC_K_HEADERS_B OKC,
333 OKL_ASSET_RETURNS_B OAR
334 WHERE OKC.ID = KLE.CHR_ID
335 AND OAR.KLE_ID = KLE.ID
336 AND oar.id = c_art_id
337 AND ART1_CODE ='REPOS_REQUEST';
338
339 l_asset_return okl_asset_return_csr%rowtype;
340
341 -- cursor to find valid external notification user
342 CURSOR okl_vendor_csr(c_agent_id NUMBER)
343 IS
344 SELECT *
345 FROM OKX_VENDORS_V
346 WHERE ID1 = c_agent_id;
347
348 l_vendor OKX_VENDORS_V%rowtype;
349
350 --12/20/06 rkuttiya added for XMLP Project
351
352 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
353 x_msg_count NUMBER;
354 x_msg_data VARCHAR2(2000);
355 l_api_version NUMBER := 1;
356 l_init_msg_list VARCHAR2(1) := 'T';
357
358 ERR EXCEPTION;
359 l_batch_id NUMBER;
360 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
361 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
362
363 --get the recipient email address
364 CURSOR c_recipient(p_recipient_id IN NUMBER)
365 IS
366 SELECT hzp.email_address email
367 FROM hz_parties hzp
368 WHERE hzp.party_id = p_recipient_id;
369
370 -- get the sender email address
371 CURSOR c_agent_csr (c_agent_id NUMBER) IS
372 SELECT nvl(ppf.email_address , fu.email_address) email
373 FROM fnd_user fu,
374 per_people_f ppf
375 WHERE fu.employee_id = ppf.person_id (+)
376 AND fu.user_id = c_agent_id;
377 l_from_email VARCHAR2(100);
378 l_to_email VARCHAR2(100);
379
380
381 BEGIN
382 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
383 itemkey => itemkey,
384 aname => 'TRANSACTION_ID');
385
386 OPEN okl_asset_return_csr(l_art_id);
387 FETCH okl_asset_return_csr INTO l_asset_return;
388 CLOSE okl_asset_return_csr;
389
390 OPEN okl_vendor_csr(l_asset_return.AGENT_ID);
391 FETCH okl_vendor_csr INTO l_vendor;
392 CLOSE okl_vendor_csr;
393
394 okl_am_wf.get_notification_agent(
395 itemtype => itemtype
396 , itemkey => itemkey
397 , actid => actid
398 , funcmode => funcmode
399 , p_user_id => l_asset_return.last_updated_by
400 , x_name => l_user
401 , x_description => l_name);
402
403 wf_engine.SetItemAttrText ( itemtype=> itemtype,
404 itemkey => itemkey,
405 aname => 'REQUESTER',
406 avalue => l_user);
407
408 wf_engine.SetItemAttrText ( itemtype=> itemtype,
409 itemkey => itemkey,
410 aname => 'CONTRACT_NUMBER',
411 avalue => l_asset_return.CONTRACT_NUMBER);
412
413 wf_engine.SetItemAttrText ( itemtype=> itemtype,
414 itemkey => itemkey,
415 aname => 'ASSET_NUMBER',
416 avalue => l_asset_return.ASSET_NUMBER);
417
418 wf_engine.SetItemAttrText ( itemtype=> itemtype,
419 itemkey => itemkey,
420 aname => 'DATE_RETURNED',
421 avalue => to_char(l_asset_return.DATE_RETURNED));
422
423 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
424 itemkey => itemkey,
425 aname => 'CREATED_BY',
426 avalue => to_char(l_asset_return.LAST_UPDATED_BY));
427
428 -- Item Attributes for Fulfillment
429
430 wf_engine.SetItemAttrText ( itemtype=> itemtype,
431 itemkey => itemkey,
432 aname => 'PROCESS_CODE',
433 avalue => 'AMNRA');
434
435 wf_engine.SetItemAttrText ( itemtype=> itemtype,
436 itemkey => itemkey,
437 aname => 'TRANSACTION_ID',
438 avalue => l_art_id);
439 --12/20/06 rkuttiya changed recipient type to VENDOR, for XMLP Project
440 /*wf_engine.SetItemAttrText ( itemtype=> itemtype,
441 itemkey => itemkey,
442 aname => 'RECIPIENT_TYPE',
443 avalue => 'V'); */
444
445 wf_engine.SetItemAttrText ( itemtype=> itemtype,
446 itemkey => itemkey,
447 aname => 'RECIPIENT_TYPE',
448 avalue => 'VENDOR');
449
450 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
451 itemkey => itemkey,
452 aname => 'RECIPIENT_ID',
453 avalue => to_char(l_asset_return.AGENT_ID));
454
455 wf_engine.SetItemAttrText ( itemtype=> itemtype,
456 itemkey => itemkey,
457 aname => 'RECIPIENT_DESCRIPTION',
458 avalue => l_vendor.NAME);
459
460 --12/18/06 rkuttiya modified for XMLP Project
461 --set the From Address and TO Address
462 OPEN c_recipient(l_asset_return.agent_id);
463 FETCH c_recipient INTO l_to_email;
464 CLOSE c_recipient;
465
466 wf_engine.SetItemAttrText ( itemtype=> itemtype,
467 itemkey => itemkey,
468 aname => 'EMAIL_ADDRESS',
469 avalue => l_to_email);
470
471 OPEN c_agent_csr(l_asset_return.last_updated_by);
472 FETCH c_agent_csr into l_from_email;
473 CLOSE c_agent_csr;
474
475 wf_engine.SetItemAttrText ( itemtype=> itemtype,
476 itemkey => itemkey,
477 aname => 'FROM_ADDRESS',
478 avalue => l_from_email);
479
480
481 --18-Dec-06 rkuttiya added for XMLP Project
482 --code for inserting bind parameters into table
483
484 l_xmp_rec.param_name := 'P_ART_ID';
485 l_xmp_rec.param_value := l_art_id;
486 l_xmp_rec.param_type_code := 'NUMBER';
487
488 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
489 p_api_version => l_api_version
490 ,p_init_msg_list => l_init_msg_list
491 ,x_return_status => l_return_status
492 ,x_msg_count => x_msg_count
493 ,x_msg_data => x_msg_data
494 ,p_xmp_rec => l_xmp_rec
495 ,x_xmp_rec => lx_xmp_rec
496 );
497
498
499
500 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
501 RAISE ERR;
502 END IF;
503
504
505 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
506 l_batch_id := lx_xmp_rec.batch_id;
507 wf_engine.SetItemAttrText ( itemtype=> itemtype,
508 itemkey => itemkey,
509 aname => 'BATCH_ID',
510 avalue => l_batch_id );
511 resultout := 'COMPLETE:SUCCESS';
512 ELSE
513 resultout := 'COMPLETE:ERROR';
514 END IF;
515
516
517 EXCEPTION
518 WHEN OTHERS THEN
519 IF okl_asset_return_csr%ISOPEN THEN
520 CLOSE okl_asset_return_csr;
521 END IF;
522
523 IF okl_vendor_csr%ISOPEN THEN
524 CLOSE okl_vendor_csr;
525 END IF;
526
527 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_REPO_NOTIFY_ATT', itemtype, itemkey, actid, funcmode);
528 RAISE;
529
530 END POP_REPO_NOTIFY_ATT;
531
532 -- Start of comments
533 --
534 -- Procedure Name : pop_remk_notify_att
535 -- Description : populate remarketer notification attributes from WF
536 -- Business Rules :
537 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
538 -- Version : 1.0
539 --
540 -- End of comments
541 PROCEDURE POP_REMK_NOTIFY_ATT( itemtype IN VARCHAR2,
542 itemkey IN VARCHAR2,
543 actid IN NUMBER,
544 funcmode IN VARCHAR2,
545 resultout OUT NOCOPY VARCHAR2 ) AS
546
547 l_art_id NUMBER;
548 l_no_data_found BOOLEAN;
549 l_user WF_USERS.NAME%TYPE;
550
551 -- cursor to populate notification attributes
552 --Added by cdubey for bug 5253787
553 --Made changes to split the original cursor okl_asset_return_csr into two cursors for performance issue
554
555
556 CURSOR okl_asset_return_csr(c_art_id NUMBER)
557 IS
558 SELECT OAR.LAST_UPDATED_BY,
559 OAR.DATE_REPOSSESSION_ACTUAL DATE_RETURNED,
560 OAR.RMR_ID RMR_ID,
561 OAR.COMMENTS COMMENTS,
562 OAR.KLE_ID KLE_ID
563 FROM OKL_ASSET_RETURNS_V OAR
564 WHERE OAR.ID = c_art_id;
565
566 CURSOR okl_asset_details_csr(c_kle_id NUMBER)
567 IS
568 SELECT AD.CHR_ID CHR_ID,
569
570 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
571 AD.CONTRACT_NUMBER CONTRACT_NUMBER, AD.NAME ASSET_NUMBER,
572 AD.SERIAL_NUMBER SERIAL_NUMBER, AD.MODEL_NUMBER MODEL_NUMBER,
573 AD.ID KLE_ID
574 FROM OKL_AM_ASSET_DETAILS_UV AD
575 WHERE AD.ID = c_kle_id;
576 l_asset_detail okl_asset_details_csr%rowtype;
577 --end cdubey for bug 5253787
578
579
580 l_asset_return okl_asset_return_csr%rowtype;
581
582 -- cursor to find valid notification user
583 CURSOR wf_users_csr(c_team_id NUMBER)
584 IS
585 SELECT count(*)
586 FROM jtf_rs_teams_vl t,
587 jtf_rs_role_relations_vl jtfr,
588 jtf_rs_Resource_extns a,
589 jtf_rs_Team_Members b,
590 jtf_rs_Groups_b d,
591 jtf_rs_resource_extns re,
592 wf_users wu
593 WHERE t.team_id = c_team_id
594 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
595 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
596 AND jtfr.role_code = 'REMARKETER'
597 AND role_resource_type = 'RS_TEAM'
598 AND jtfr.role_resource_id = t.team_id
599 AND t.team_id = b.Team_Id
600 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
601 d.Group_Number)) = re.resource_number
602 AND b.Team_Resource_Id = a.Resource_Id (+)
603 AND b.Team_Resource_Id = d.Group_Id (+)
604 AND re.source_id = wu.orig_system_id
605 AND re.user_name = wu.name; -- mdokal : Bug 3562321
606
607 BEGIN
608
609 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
610 itemkey => itemkey,
611 aname => 'TRANSACTION_ID');
612
613 OPEN okl_asset_return_csr(l_art_id);
614 FETCH okl_asset_return_csr INTO l_asset_return;
615 CLOSE okl_asset_return_csr;
616
617 --Added by cdubey for bug 5253787
618 OPEN okl_asset_details_csr(l_asset_return.KLE_ID);
619 FETCH okl_asset_details_csr INTO l_asset_detail;
620 CLOSE okl_asset_details_csr;
621 --end cdubey
622
623
624 OPEN wf_users_csr(l_asset_return.RMR_ID);
625 FETCH wf_users_csr INTO l_user;
626 CLOSE wf_users_csr;
627
628 wf_engine.SetItemAttrText ( itemtype=> itemtype,
629 itemkey => itemkey,
630 aname => 'TOTAL_RECIPIENTS',
631 avalue => l_user);
632
633 wf_engine.SetItemAttrText ( itemtype=> itemtype,
634 itemkey => itemkey,
635 aname => 'CURRENT_RECIPIENT',
636 avalue => l_user+1);
637
638 wf_engine.SetItemAttrText ( itemtype=> itemtype,
639 itemkey => itemkey,
640 aname => 'CONTRACT_NUMBER',
641 avalue => l_asset_detail.CONTRACT_NUMBER); --bug 5253787
642
643 wf_engine.SetItemAttrText ( itemtype=> itemtype,
644 itemkey => itemkey,
645 aname => 'ASSET_NUMBER',
646 avalue => l_asset_detail.ASSET_NUMBER); --bug 5253787
647
648 wf_engine.SetItemAttrText ( itemtype=> itemtype,
649 itemkey => itemkey,
650 aname => 'DATE_RETURNED',
651 avalue => to_char(l_asset_return.DATE_RETURNED));
652
653 wf_engine.SetItemAttrText ( itemtype=> itemtype,
654 itemkey => itemkey,
655 aname => 'CREATED_BY',
656 avalue => to_char(l_asset_return.LAST_UPDATED_BY));
657
658 wf_engine.SetItemAttrText ( itemtype=> itemtype,
659 itemkey => itemkey,
660 aname => 'REMARKETER_ID',
661 avalue => to_char(l_asset_return.RMR_ID));
662
663 wf_engine.SetItemAttrText ( itemtype=> itemtype,
664 itemkey => itemkey,
665 aname => 'ASSET_DESCRIPTION',
666 avalue => l_asset_detail.asset_description); --bug 5253787
667
668 wf_engine.SetItemAttrText ( itemtype=> itemtype,
669 itemkey => itemkey,
670 aname => 'MODEL_NUMBER',
671 avalue => l_asset_detail.model_number); --bug 5253787
672
673 wf_engine.SetItemAttrText ( itemtype=> itemtype,
674 itemkey => itemkey,
675 aname => 'SERIAL_NUMBER',
676 avalue => l_asset_detail.serial_number); --bug 5253787
677
678 EXCEPTION
679 WHEN OTHERS THEN
680 IF okl_asset_return_csr%ISOPEN THEN
681 CLOSE okl_asset_return_csr;
682 END IF;
683
684 --Added by cdubey for bug 5253787
685 IF okl_asset_details_csr%ISOPEN THEN
686 CLOSE okl_asset_details_csr;
687 END IF;
688 --end cdubey
689
690
691 IF wf_users_csr%ISOPEN THEN
692 CLOSE wf_users_csr;
693 END IF;
694
695 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_REMK_NOTIFY_ATT', itemtype, itemkey, actid, funcmode);
696 RAISE;
697
698 END POP_REMK_NOTIFY_ATT;
699
700 -- Start of comments
701 --
702 -- Procedure Name : notify_remk_user
703 -- Description : populate remarketer notification attributes from WF
704 -- This procedure is called recursively until all team
705 -- members have been notified.
706 -- Business Rules :
707 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
708 -- Version : 1.0
709 --
710 -- End of comments
711 PROCEDURE NOTIFY_REMK_USER( itemtype IN VARCHAR2,
712 itemkey IN VARCHAR2,
713 actid IN NUMBER,
714 funcmode IN VARCHAR2,
715 resultout OUT NOCOPY VARCHAR2 ) AS
716
717 l_rmr_id NUMBER;
718 l_total_rec NUMBER;
719 l_current_rec NUMBER := 0;
720 l_no_data_found BOOLEAN;
721 l_user WF_USERS.NAME%TYPE;
722 l_name WF_USERS.DISPLAY_NAME%TYPE;
723 l_current_user WF_USERS.NAME%TYPE;
724
725 -- cursor to populate notification attributes
726 CURSOR wf_users_csr(c_team_id NUMBER,
727 c_current_user NUMBER,
728 c_name VARCHAR)
729 IS
730 SELECT wu.name, wu.display_name
731 FROM jtf_rs_teams_vl t,
732 jtf_rs_role_relations_vl jtfr,
733 jtf_rs_team_members_vl tm,
734 jtf_rs_resource_extns_vl re,
735 wf_users WU
736 WHERE t.team_id = c_team_id
737 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
738 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
739 AND jtfr.role_code = 'REMARKETER'
740 AND role_resource_type = 'RS_TEAM'
741 AND nvl (jtfr.start_date_active, sysdate - 1) <= sysdate
742 AND nvl (jtfr.end_date_active, sysdate + 1) >= sysdate
743 AND jtfr.role_resource_id = t.team_id
744 AND t.team_id = tm.team_id
745 AND tm.delete_flag = 'N'
746 AND tm.resource_number = re.resource_number
747 AND nvl (re.start_date_active, sysdate - 1) <= sysdate
748 AND nvl (re.end_date_active, sysdate + 1) >= sysdate
749 AND source_id = wu.orig_system_id
750 AND ROWNUM < c_current_user
751 AND wu.name > nvl(c_name, '0')
752 UNION
753 SELECT wu.name, wu.display_name
754 FROM jtf_rs_teams_vl t,
755 jtf_rs_role_relations_vl jtfr,
756 jtf_rs_team_members_vl tm,
757 jtf_rs_resource_extns_vl re,
758 wf_users WU
759 WHERE t.team_id = c_team_id
760 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
761 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
762 AND jtfr.role_code = 'REMARKETER'
763 AND role_resource_type = 'RS_INDIVIDUAL'
764 AND nvl (jtfr.start_date_active, sysdate - 1) <= sysdate
765 AND nvl (jtfr.end_date_active, sysdate + 1) >= sysdate
766 AND jtfr.role_resource_id = re.RESOURCE_ID
767 AND t.team_id = tm.team_id
768 AND tm.delete_flag = 'N'
769 AND tm.resource_number = re.resource_number
770 AND nvl (re.start_date_active, sysdate - 1) <= sysdate
771 AND nvl (re.end_date_active, sysdate + 1) >= sysdate
772 AND source_id = wu.orig_system_id
773 AND ROWNUM < c_current_user
774 AND wu.name > nvl(c_name, '0')
775 UNION
776 SELECT wu.name, wu.display_name
777 FROM jtf_rs_teams_vl t,
778 jtf_rs_role_relations_vl jtfr,
779 jtf_rs_team_members_vl tm,
780 jtf_rs_resource_extns_vl re,
781 wf_users WU
782 WHERE t.team_id = c_team_id
783 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
784 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
785 AND jtfr.role_code = 'REMARKETER'
786 AND role_resource_type = 'RS_TEAM_MEMBER'
787 AND nvl (jtfr.start_date_active, sysdate - 1) <= sysdate
788 AND nvl (jtfr.end_date_active, sysdate + 1) >= sysdate
789 AND jtfr.role_resource_id = tm.team_member_id
790 AND t.team_id = tm.team_id
791 AND tm.delete_flag = 'N'
792 AND tm.resource_number = re.resource_number
793 AND nvl (re.start_date_active, sysdate - 1) <= sysdate
794 AND nvl (re.end_date_active, sysdate + 1) >= sysdate
795 AND source_id = wu.orig_system_id
796 AND ROWNUM < c_current_user
797 AND wu.name > nvl(c_name, '0')
798 ORDER BY 1 ASC;
799
800 BEGIN
801
802 IF (funcmode = 'RUN') THEN
803
804 l_rmr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
805 itemkey => itemkey,
806 aname => 'REMARKETER_ID');
807
808 l_total_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'TOTAL_RECIPIENTS');
811
812 l_current_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'CURRENT_RECIPIENT');
815
816 l_current_user := wf_engine.GetItemAttrText( itemtype => itemtype,
817 itemkey => itemkey,
818 aname => 'PERFORMING_AGENT');
819
820
821 OPEN wf_users_csr(l_rmr_id, l_current_rec, l_current_user);
822 FETCH wf_users_csr INTO l_user, l_name ;
823 CLOSE wf_users_csr;
824
825 wf_engine.SetItemAttrText ( itemtype=> itemtype,
826 itemkey => itemkey,
827 aname => 'PERFORMING_AGENT',
828 avalue => l_user);
829
830 wf_engine.SetItemAttrText ( itemtype=> itemtype,
831 itemkey => itemkey,
832 aname => 'RECIPIENT_NAME',
833 avalue => l_name);
834
835 l_current_rec := l_current_rec-1;
836
837 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
838 itemkey => itemkey,
839 aname => 'CURRENT_RECIPIENT',
840 avalue => l_current_rec);
841
842
843 IF l_current_rec = 0 THEN
844 resultout := 'COMPLETE:NOTIFY_COMPLETE';
845 ELSE
846 resultout := 'COMPLETE:NOTIFY_OUTSTANDING';
847 END IF;
848
849 RETURN ;
850
851 END IF;
852 --
853 -- CANCEL mode
854 --
855 IF (funcmode = 'CANCEL') THEN
856 --
857 resultout := 'COMPLETE:';
858 RETURN;
859 --
860 END IF;
861 --
862 -- TIMEOUT mode
863 --
864 IF (funcmode = 'TIMEOUT') THEN
865 --
866 resultout := 'COMPLETE:';
867 RETURN;
868 --
869 END IF;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 IF wf_users_csr%ISOPEN THEN
874 CLOSE wf_users_csr;
875 END IF;
876
877 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'NOTIFY_REMK_USER', itemtype, itemkey, actid, funcmode);
878 RAISE;
879
880 END NOTIFY_REMK_USER;
881
882 -- Start of comments
883 --
884 -- Procedure Name : check_asset_return
885 -- Description : validate asset return is for a repossession from WF
886 -- Business Rules :
887 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
888 -- Version : 1.0
889 --
890 -- End of comments
891 PROCEDURE check_asset_return( itemtype IN VARCHAR2,
892 itemkey IN VARCHAR2,
893 actid IN NUMBER,
894 funcmode IN VARCHAR2,
895 resultout OUT NOCOPY VARCHAR2 )IS
896
897 l_art_id NUMBER;
898 l_code okl_asset_returns_v.ars_code%type;
899
900 -- Check that the asset return refers to a Repossession Request
901 CURSOR okl_check_req_csr(c_art_id NUMBER)
902 IS
903 SELECT ARS_CODE
904 FROM OKL_ASSET_RETURNS_V
905 WHERE ID= c_art_id
906 AND ARS_CODE IN ('REPOSSESSED', 'UNSUCCESS_REPO')
907 AND ART1_CODE = 'REPOS_REQUEST';
908
909
910 BEGIN
911
912 IF (funcmode = 'RUN') THEN
913
914 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
915 itemkey => itemkey,
916 aname => 'TRANSACTION_ID');
917
918 OPEN okl_check_req_csr(l_art_id);
919 FETCH okl_check_req_csr INTO l_code;
920 CLOSE okl_check_req_csr;
921
922 IF l_code = 'UNSUCCESS_REPO' THEN
923 resultout := 'COMPLETE:ASSET_NOT_RETURNED';
924 ELSIF l_code = 'REPOSSESSED' THEN
925 resultout := 'COMPLETE:ASSET_RETURNED';
926 END IF;
927
928 -- At this point populate the attributes required for the notification
929 populate_notification_attribs(itemtype => itemtype,
930 itemkey => itemkey,
931 actid => actid,
932 funcmode => funcmode,
933 p_art_id => l_art_id );
934
935 RETURN ;
936
937 END IF;
938 --
939 -- CANCEL mode
940 --
941 IF (funcmode = 'CANCEL') THEN
942 --
943 resultout := 'COMPLETE:';
944 RETURN;
945 --
946 END IF;
947 --
948 -- TIMEOUT mode
949 --
950 IF (funcmode = 'TIMEOUT') THEN
951 --
952 resultout := 'COMPLETE:';
953 RETURN;
954 --
955 END IF;
956
957 EXCEPTION
958 WHEN OTHERS THEN
959 IF okl_check_req_csr%ISOPEN THEN
960 CLOSE okl_check_req_csr;
961 END IF;
962
963 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_asset_return', itemtype, itemkey, actid, funcmode);
964 RAISE;
965
966 END check_asset_return;
967
968 -- Start of comments
969 --
970 -- Procedure Name : check_return_type
971 -- Description : validate asset return from WF
972 -- Business Rules :
973 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
974 -- Version : 1.0
975 --
976 -- End of comments
977 PROCEDURE check_return_type ( itemtype IN VARCHAR2,
978 itemkey IN VARCHAR2,
979 actid IN NUMBER,
980 funcmode IN VARCHAR2,
981 resultout OUT NOCOPY VARCHAR2 )IS
982
983 l_art_id NUMBER;
984 l_knt NUMBER;
985
986 -- Check that the asset return refers to a Repossession Request
987 CURSOR okl_check_req_csr(c_art_id NUMBER)
988 IS
989 SELECT count(*)
990 FROM OKL_ASSET_RETURNS_V
991 WHERE ID= c_art_id
992 AND ART1_CODE = 'REPOS_REQUEST';
993
994 BEGIN
995
996 IF (funcmode = 'RUN') THEN
997
998 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
999 itemkey => itemkey,
1000 aname => 'TRANSACTION_ID');
1001
1002 OPEN okl_check_req_csr(l_art_id);
1003 FETCH okl_check_req_csr INTO l_knt;
1004 CLOSE okl_check_req_csr;
1005
1006 IF l_knt = 0 THEN
1007 resultout := 'COMPLETE:NON_REPO_RETURN';
1008 ELSE
1009 resultout := 'COMPLETE:REPO_RETURN';
1010 END IF;
1011
1012 RETURN ;
1013
1014 END IF;
1015 --
1016 -- CANCEL mode
1017 --
1018 IF (funcmode = 'CANCEL') THEN
1019 --
1020 resultout := 'COMPLETE:';
1021 RETURN;
1022 --
1023 END IF;
1024 --
1025 -- TIMEOUT mode
1026 --
1027 IF (funcmode = 'TIMEOUT') THEN
1028 --
1029 resultout := 'COMPLETE:';
1030 RETURN;
1031 --
1032 END IF;
1033
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 IF okl_check_req_csr%ISOPEN THEN
1037 CLOSE okl_check_req_csr;
1038 END IF;
1039
1040 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_return_type', itemtype, itemkey, actid, funcmode);
1041 RAISE;
1042
1043 END check_return_type;
1044
1045 -- Start of comments
1046 --
1047 -- Procedure Name : check_role_exists
1048 -- Description : check notification is sent to a valid user from WF
1049 -- Business Rules :
1050 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1051 -- Version : 1.0
1052 --
1053 -- End of comments
1054 PROCEDURE check_role_exists ( itemtype IN VARCHAR2,
1055 itemkey IN VARCHAR2,
1056 actid IN NUMBER,
1057 funcmode IN VARCHAR2,
1058 resultout OUT NOCOPY VARCHAR2 )IS
1059
1060 l_creator NUMBER;
1061 l_role VARCHAR2(100);
1062 l_name VARCHAR2(100);
1063
1064 BEGIN
1065
1066 IF (funcmode = 'RUN') THEN
1067
1068 l_creator := wf_engine.GetItemAttrText( itemtype => itemtype,
1069 itemkey => itemkey,
1070 aname => 'CREATED_BY');
1071
1072
1073 okl_am_wf.get_notification_agent(
1074 itemtype => itemtype
1075 , itemkey => itemkey
1076 , actid => actid
1077 , funcmode => funcmode
1078 , p_user_id => l_creator
1079 , x_name => l_role
1080 , x_description => l_name);
1081
1082 IF l_role IS NULL THEN
1083 resultout := 'COMPLETE:ROLE_NOT_FOUND';
1084 ELSE
1085
1086 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1087 itemkey => itemkey,
1088 aname => 'REQUESTER',
1089 avalue => l_role);
1090
1091 resultout := 'COMPLETE:ROLE_FOUND';
1092 END IF;
1093
1094 RETURN ;
1095
1096 END IF;
1097 --
1098 -- CANCEL mode
1099 --
1100 IF (funcmode = 'CANCEL') THEN
1101 --
1102 resultout := 'COMPLETE:';
1103 RETURN;
1104 --
1105 END IF;
1106 --
1107 -- TIMEOUT mode
1108 --
1109 IF (funcmode = 'TIMEOUT') THEN
1110 --
1111 resultout := 'COMPLETE:';
1112 RETURN;
1113 --
1114 END IF;
1115
1116 EXCEPTION
1117 WHEN OTHERS THEN
1118
1119 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_role_exists', itemtype, itemkey, actid, funcmode);
1120 RAISE;
1121
1122 END check_role_exists;
1123
1124 -- Start of comments
1125 --
1126 -- Procedure Name : validate_title_ret
1127 -- Description : title return request from WF
1128 -- Business Rules :
1129 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1130 -- Version : 1.0
1131 --
1132 -- End of comments
1133 PROCEDURE validate_title_ret( itemtype IN VARCHAR2,
1134 itemkey IN VARCHAR2,
1135 actid IN NUMBER,
1136 funcmode IN VARCHAR2,
1137 resultout OUT NOCOPY VARCHAR2 )IS
1138
1139 l_art_id NUMBER;
1140 l_code okl_asset_returns_v.ars_code%type;
1141
1142 -- Check that the asset return refers to a Repossession Request
1143 CURSOR okl_check_req_csr(c_art_id NUMBER)
1144 IS
1145 SELECT OAR.KLE_ID KLE_ID, KLE.CHR_ID CHR_ID, OAR.LAST_UPDATED_BY LAST_UPDATED_BY
1146 FROM okl_asset_returns_b OAR, OKL_K_LINES_FULL_V KLE
1147 WHERE OAR.KLE_ID = KLE.ID
1148 AND OAR.ID= c_art_id;
1149
1150 l_art_rec okl_check_req_csr%rowtype;
1151 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1152 l_rule_rec okl_rule_pub.rulv_rec_type;
1153 l_party_object_tbl okl_am_parties_pvt.party_object_tbl_type;
1154 l_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
1155
1156
1157 --12/18/06 rkuttiya added for XMLP Project
1158
1159 x_msg_count NUMBER;
1160 x_msg_data VARCHAR2(2000);
1161 l_api_version NUMBER := 1;
1162 l_init_msg_list VARCHAR2(1) := 'T';
1163
1164 ERR EXCEPTION;
1165 l_batch_id NUMBER;
1166 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1167 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1168 --get the recipient email address
1169 CURSOR c_recipient(p_recipient_id IN NUMBER)
1170 IS
1171 SELECT hzp.email_address email
1172 FROM hz_parties hzp
1173 WHERE hzp.party_id = p_recipient_id;
1174
1175 -- get the sender email address
1176 CURSOR c_agent_csr (c_agent_id NUMBER) IS
1177 SELECT nvl(ppf.email_address , fu.email_address) email
1178 FROM fnd_user fu,
1179 per_people_f ppf
1180 WHERE fu.employee_id = ppf.person_id (+)
1181 AND fu.user_id = c_agent_id;
1182 l_from_email VARCHAR2(100);
1183 l_to_email VARCHAR2(100);
1184
1185 BEGIN
1186
1187 IF (funcmode = 'RUN') THEN
1188
1189 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1190 itemkey => itemkey,
1191 aname => 'TRANSACTION_ID');
1192
1193 OPEN okl_check_req_csr(l_art_id);
1194 FETCH okl_check_req_csr INTO l_art_rec;
1195 CLOSE okl_check_req_csr;
1196
1197 IF l_art_rec.KLE_ID IS NOT NULL THEN
1198
1199 -- First get the party id from the rule if the custodian is a 3rd party
1200 okl_am_util_pvt.get_rule_record (
1201 p_rgd_code => 'LAAFLG',
1202 p_rdf_code => 'LAFLTL',
1203 p_chr_id => l_art_rec.chr_id,
1204 p_cle_id => l_art_rec.kle_id,
1205 x_rulv_rec => l_rule_rec,
1206 x_return_status => l_return_status,
1207 p_message_yn => TRUE); -- put error message on stack if there is no rule
1208
1209 END IF;
1210
1211 IF l_return_status = OKL_API.G_RET_STS_SUCCESS AND nvl(l_rule_rec.object2_id1, l_rule_rec.object1_id1) IS NOT NULL THEN -- party id exists
1212
1213 resultout := 'COMPLETE:VALID';
1214
1215 -- get party name
1216 -- To Do: change to okl_am_util_pvt MDOKAL
1217 okl_am_util_pvt.get_object_details (
1218 p_object_code => l_rule_rec.jtot_object2_code, -- no need to hard-code
1219 p_object_id1 => l_rule_rec.object2_id1, -- correct field to use
1220 p_object_id2 => l_rule_rec.object2_id2, -- correct field to use
1221 x_object_tbl => l_object_tbl,
1222 x_return_status => l_return_status);
1223
1224 -- Item Attributes for Fulfillment
1225 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1226 itemkey => itemkey,
1227 aname => 'CREATED_BY',
1228 avalue => l_art_rec.LAST_UPDATED_BY);
1229
1230 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1231 itemkey => itemkey,
1232 aname => 'PROCESS_CODE',
1233 avalue => 'AMRTR');
1234
1235 --12/20/06 rkuttiya commented to change recipient type to LESSEE, for XMLP Project
1236 /*wf_engine.SetItemAttrText ( itemtype=> itemtype,
1237 itemkey => itemkey,
1238 aname => 'RECIPIENT_TYPE',
1239 avalue => 'P'); */
1240
1241 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1242 itemkey => itemkey,
1243 aname => 'RECIPIENT_TYPE',
1244 avalue => 'LESSEE');
1245
1246 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1247 itemkey => itemkey,
1248 aname => 'RECIPIENT_ID',
1249 avalue => nvl(l_rule_rec.object2_id1, l_rule_rec.object1_id1));
1250
1251 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1252 itemkey => itemkey,
1253 aname => 'RECIPIENT_DESCRIPTION',
1254 avalue => l_object_tbl(1).name );
1255
1256
1257 --12/18/06 rkuttiya modified for XMLP Project
1258 --set the From Address and TO Address
1259 OPEN c_recipient(nvl(l_rule_rec.object2_id1, l_rule_rec.object1_id1));
1260 FETCH c_recipient INTO l_to_email;
1261 CLOSE c_recipient;
1262
1263 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1264 itemkey => itemkey,
1265 aname => 'EMAIL_ADDRESS', -- 20/07/2007 ansethur modified the Item attribute name
1266 avalue => l_to_email);
1267
1268 OPEN c_agent_csr(l_art_rec.last_updated_by);
1269 FETCH c_agent_csr into l_from_email;
1270 CLOSE c_agent_csr;
1271
1272 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1273 itemkey => itemkey,
1274 aname => 'FROM_ADDRESS', -- 20/07/2007 ansethur modified the Item attribute name
1275 avalue => l_from_email);
1276
1277
1278 --18-Dec-06 rkuttiya added for XMLP Project
1279 --code for inserting bind parameters into table
1280
1281 l_xmp_rec.param_name := 'P_ART_ID';
1282 l_xmp_rec.param_value := l_art_id;
1283 l_xmp_rec.param_type_code := 'NUMBER';
1284
1285 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
1286 p_api_version => l_api_version
1287 ,p_init_msg_list => l_init_msg_list
1288 ,x_return_status => l_return_status
1289 ,x_msg_count => x_msg_count
1290 ,x_msg_data => x_msg_data
1291 ,p_xmp_rec => l_xmp_rec
1292 ,x_xmp_rec => lx_xmp_rec
1293 );
1294
1295 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1296 RAISE ERR;
1297 END IF;
1298
1299
1300 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1301 l_batch_id := lx_xmp_rec.batch_id;
1302 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1303 itemkey => itemkey,
1304 aname => 'BATCH_ID',
1305 avalue => l_batch_id );
1306 resultout := 'COMPLETE:VALID'; -- 20/07/2007 ansethur modified the value passed
1307 ELSE
1308 resultout := 'COMPLETE:ERROR';
1309 END IF;
1310
1311 ELSE
1312 resultout := 'COMPLETE:INVALID';
1313 END IF;
1314
1315 RETURN ;
1316
1317 END IF;
1318 --
1319 -- CANCEL mode
1320 --
1321 IF (funcmode = 'CANCEL') THEN
1322 --
1323 resultout := 'COMPLETE:';
1324 RETURN;
1325 --
1326 END IF;
1327 --
1328 -- TIMEOUT mode
1329 --
1330 IF (funcmode = 'TIMEOUT') THEN
1331 --
1332 resultout := 'COMPLETE:';
1333 RETURN;
1334 --
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 IF okl_check_req_csr%ISOPEN THEN
1340 CLOSE okl_check_req_csr;
1341 END IF;
1342
1343 END validate_title_ret;
1344
1345
1346 -- Start of comments
1347 --
1348 -- Procedure Name : validate_shipping_instr
1349 -- Description : validate shipping instruction request from WF
1350 -- Business Rules :
1351 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1352 -- Version : 1.0
1353 -- 18-Dec-06 rkuttiya modified for XMLP Project
1354 -- End of comments
1355 PROCEDURE validate_shipping_instr(
1356 itemtype IN VARCHAR2,
1357 itemkey IN VARCHAR2,
1358 actid IN NUMBER,
1359 funcmode IN VARCHAR2,
1360 resultout OUT NOCOPY VARCHAR2 )IS
1361
1362 l_art_id NUMBER;
1363
1364 -- Check that the asset return refers to a Repossession Request
1365 CURSOR okl_check_req_csr(c_art_id NUMBER)
1366 IS
1367 SELECT ra.last_updated_by, cp.contact_party_id pac_id, contact_party_name
1368 FROM okl_asset_returns_b ar
1369 ,okl_relocate_assets_b ra
1370 ,okl_am_contact_points_uv cp
1371 ,okl_am_contacts_uv c
1372 WHERE ar.id = c_art_id
1373 AND ar.id = ra.art_id
1374 AND pac_id = cp.contact_contact_point_id
1375 AND cp.contact_party_id = c.contact_party_id
1376 AND ist_id IS NOT NULL;
1377
1378 l_csr_rec okl_check_req_csr%rowtype;
1379
1380 l_user_name WF_USERS.name%type;
1381 l_name WF_USERS.description%type;
1382
1383 l_recipient_name varchar2(100);
1384 l_recipient_id number;
1385 l_party_object_tbl okl_am_parties_pvt.party_object_tbl_type;
1386
1387 --12/18/06 rkuttiya added for XMLP Project
1388
1389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1390 x_msg_count NUMBER;
1391 x_msg_data VARCHAR2(2000);
1392 l_api_version NUMBER := 1;
1393 l_init_msg_list VARCHAR2(1) := 'T';
1394
1395 ERR EXCEPTION;
1396 l_batch_id NUMBER;
1397 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1398 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1399 --get the recipient email address
1400 CURSOR c_recipient(p_recipient_id IN NUMBER)
1401 IS
1402 SELECT hzp.email_address email
1403 FROM hz_parties hzp
1404 WHERE hzp.party_id = p_recipient_id;
1405
1406 -- get the sender email address
1407 CURSOR c_agent_csr (c_agent_id NUMBER) IS
1408 SELECT nvl(ppf.email_address , fu.email_address) email
1409 FROM fnd_user fu,
1410 per_people_f ppf
1411 WHERE fu.employee_id = ppf.person_id (+)
1412 AND fu.user_id = c_agent_id;
1413 l_from_email VARCHAR2(100);
1414 l_to_email VARCHAR2(100);
1415
1416 BEGIN
1417
1418 IF (funcmode = 'RUN') THEN
1419
1420 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1421 itemkey => itemkey,
1422 aname => 'TRANSACTION_ID');
1423
1424 OPEN okl_check_req_csr(l_art_id);
1425 FETCH okl_check_req_csr INTO l_csr_rec;
1426 CLOSE okl_check_req_csr;
1427
1428 okl_am_wf.get_notification_agent(
1429 itemtype => itemtype
1430 , itemkey => itemkey
1431 , actid => actid
1432 , funcmode => funcmode
1433 , p_user_id => l_csr_rec.last_updated_by
1434 , x_name => l_user_name
1435 , x_description => l_name);
1436
1437 -- Find party details based on the PAC_ID
1438 --okl_am_parties_pvt.get_party_details (
1439 -- p_id_code => 'PC',
1440 -- p_id_value => l_csr_rec.pac_id,
1441 -- x_party_object_tbl => l_party_object_tbl,
1442 -- x_return_status => l_return_status);
1443
1444 -- Check that a contact was returned for the TRANSACTION_ID given.
1445 IF l_csr_rec.pac_id IS NOT NULL THEN
1446 resultout := 'COMPLETE:VALID';
1447
1448 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1449 itemkey => itemkey,
1450 aname => 'CREATED_BY',
1451 avalue => l_csr_rec.last_updated_by);
1452
1453 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1454 itemkey => itemkey,
1455 aname => 'REQUESTER',
1456 avalue => l_user_name);
1457
1458 -- Populate Item Attributes for Fulfillment
1459 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1460 itemkey => itemkey,
1461 aname => 'PROCESS_CODE',
1462 avalue => 'AMNSI');
1463 --12/18/06 modified recipient type to 'LESSEE' for XML Publisher, since this was originally Party Contact
1464 /*
1465 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1466 itemkey => itemkey,
1467 aname => 'RECIPIENT_TYPE',
1468 avalue => 'PC'); */
1469 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1470 itemkey => itemkey,
1471 aname => 'RECIPIENT_TYPE',
1472 avalue => 'LESSEE');
1473
1474 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1475 itemkey => itemkey,
1476 aname => 'RECIPIENT_ID',
1477 avalue => l_csr_rec.pac_id);
1478
1479
1480 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1481 itemkey => itemkey,
1482 aname => 'RECIPIENT_DESCRIPTION',
1483 avalue => l_csr_rec.contact_party_name);
1484 --12/18/06 rkuttiya modified for XMLP Project
1485 --set the From Address and TO Address
1486 OPEN c_recipient(l_csr_rec.pac_id);
1487 FETCH c_recipient INTO l_to_email;
1488 CLOSE c_recipient;
1489
1490 OPEN c_agent_csr(l_csr_rec.last_updated_by);
1491 FETCH c_agent_csr into l_from_email;
1492 CLOSE c_agent_csr;
1493
1494 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1495 itemkey => itemkey,
1496 aname => 'EMAIL_ADDRESS',
1497 avalue => l_to_email);
1498
1499
1500 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1501 itemkey => itemkey,
1502 aname => 'FROM_ADDRESS',
1503 avalue => l_from_email);
1504
1505 --18-Dec-06 rkuttiya added for XMLP Project
1506 --code for inserting bind parameters into table
1507
1508 l_xmp_rec.param_name := 'P_ART_ID';
1509 l_xmp_rec.param_value := l_art_id;
1510 l_xmp_rec.param_type_code := 'NUMBER';
1511
1512 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
1513 p_api_version => l_api_version
1514 ,p_init_msg_list => l_init_msg_list
1515 ,x_return_status => l_return_status
1516 ,x_msg_count => x_msg_count
1517 ,x_msg_data => x_msg_data
1518 ,p_xmp_rec => l_xmp_rec
1519 ,x_xmp_rec => lx_xmp_rec
1520 );
1521 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1522 RAISE ERR;
1523 END IF;
1524
1525
1526 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1527 l_batch_id := lx_xmp_rec.batch_id;
1528 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1529 itemkey => itemkey,
1530 aname => 'BATCH_ID',
1531 avalue => l_batch_id );
1532 resultout := 'COMPLETE:VALID'; -- rkuttiya changed
1533 ELSE
1534 resultout := 'COMPLETE:ERROR';
1535 END IF;
1536 ELSE
1537 resultout := 'COMPLETE:INVALID';
1538 END IF;
1539
1540 RETURN ;
1541
1542 END IF;
1543 --
1544 -- CANCEL mode
1545 --
1546 IF (funcmode = 'CANCEL') THEN
1547 --
1548 resultout := 'COMPLETE:';
1549 RETURN;
1550 --
1551 END IF;
1552 --
1553 -- TIMEOUT mode
1554 --
1555 IF (funcmode = 'TIMEOUT') THEN
1556 --
1557 resultout := 'COMPLETE:';
1558 RETURN;
1559 --
1560 END IF;
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 IF okl_check_req_csr%ISOPEN THEN
1565 CLOSE okl_check_req_csr;
1566 END IF;
1567
1568 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'validate_shipping_instr', itemtype, itemkey, actid, funcmode);
1569 RAISE;
1570
1571 END validate_shipping_instr;
1572
1573 -- Start of comments
1574 --
1575 -- Procedure Name : validate_asset_repair
1576 -- Description : validate asset repair approval request from WF
1577 -- Business Rules :
1578 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1579 -- Version : 1.0
1580 --
1581 -- End of comments
1582 PROCEDURE validate_asset_repair( itemtype IN VARCHAR2,
1583 itemkey IN VARCHAR2,
1584 actid IN NUMBER,
1585 funcmode IN VARCHAR2,
1586 resultout OUT NOCOPY VARCHAR2 )IS
1587
1588 l_id NUMBER;
1589 l_knt NUMBER;
1590 l_org_id NUMBER;
1591
1592 -- cursor to check request is valid
1593 CURSOR okl_check_req_csr(c_id NUMBER)
1594 IS
1595 SELECT count(*)
1596 FROM OKL_ASSET_CNDTNS ACD, OKL_ASSET_CNDTN_LNS_V ACN
1597 WHERE ACD.ID = c_id
1598 AND ACD.ID = ACN.ACD_ID
1599 AND upper(nvl(ACN.APPROVED_YN, 'N')) <> 'Y'
1600 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL';
1601
1602 BEGIN
1603
1604 IF (funcmode = 'RUN') THEN
1605
1606 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1607 itemkey => itemkey,
1608 aname => 'TRANSACTION_ID');
1609
1610 -- vsgandhi 12959022 16-sept-2011
1611 l_org_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1612 itemkey => itemkey,
1613 aname => 'ORG_ID');
1614
1615
1616 IF l_org_id IS NOT NULL THEN
1617 MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org_id);
1618 MO_GLOBAL.SET_POLICY_CONTEXT( MO_GLOBAL.GET_ACCESS_MODE, l_org_id);
1619 END IF;
1620
1621 OPEN okl_check_req_csr(l_id);
1622 FETCH okl_check_req_csr INTO l_knt;
1623 CLOSE okl_check_req_csr;
1624
1625 IF l_knt = 0 THEN
1626 resultout := 'COMPLETE:INVALID';
1627 ELSE
1628 resultout := 'COMPLETE:VALID';
1629 END IF;
1630
1631 RETURN ;
1632
1633 END IF;
1634 --
1635 -- CANCEL mode
1636 --
1637 IF (funcmode = 'CANCEL') THEN
1638 --
1639 resultout := 'COMPLETE:';
1640 RETURN;
1641 --
1642 END IF;
1643 --
1644 -- TIMEOUT mode
1645 --
1646 IF (funcmode = 'TIMEOUT') THEN
1647 --
1648 resultout := 'COMPLETE:';
1649 RETURN;
1650 --
1651 END IF;
1652
1653 EXCEPTION
1654 WHEN OTHERS THEN
1655 IF okl_check_req_csr%ISOPEN THEN
1656 CLOSE okl_check_req_csr;
1657 END IF;
1658
1659 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'validate_asset_repair', itemtype, itemkey, actid, funcmode);
1660 RAISE;
1661
1662 END validate_asset_repair;
1663
1664 -- Start of comments
1665 --
1666 -- Procedure Name : set_approved_yn
1667 -- Description : set asset repair approval to Y/N from WF,
1668 -- calls TAPI
1669 -- Business Rules :
1670 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1671 -- Version : 1.0
1672 --
1673 -- End of comments
1674 PROCEDURE set_approved_yn( itemtype IN VARCHAR2,
1675 itemkey IN VARCHAR2,
1676 actid IN NUMBER,
1677 funcmode IN VARCHAR2,
1678 resultout OUT NOCOPY VARCHAR2 )IS
1679
1680 l_id NUMBER;
1681 l_knt NUMBER;
1682 l_approved_yn VARCHAR2(10);
1683
1684 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1685 x_msg_count NUMBER;
1686 x_msg_data VARCHAR2(2000);
1687 l_acnv_rec OKL_ACN_PVT.ACNV_REC_TYPE;
1688 x_acnv_rec OKL_ACN_PVT.ACNV_REC_TYPE;
1689 l_api_version NUMBER := 1;
1690 l_init_msg_list VARCHAR2(1) := 'T';
1691
1692 CURSOR c_rec_csr(C_ID NUMBER) IS
1693 SELECT ACN.ID id
1694 FROM OKL_ASSET_CNDTNS ACD,
1695 OKL_ASSET_CNDTN_LNS_V ACN
1696 WHERE ACD.ID = C_ID
1697 AND ACD.ID = ACN.ACD_ID
1698 AND nvl(UPPER(ACN.APPROVED_YN), 'N') <> 'Y'
1699 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL';
1700
1701 ERR EXCEPTION;
1702 BEGIN
1703
1704 IF (funcmode = 'RUN') THEN
1705
1706 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1707 itemkey => itemkey,
1708 aname => 'TRANSACTION_ID');
1709
1710 l_approved_yn := wf_engine.GetItemAttrText( itemtype => itemtype,
1711 itemkey => itemkey,
1712 aname => 'APPROVED_YN');
1713
1714
1715 l_acnv_rec.APPROVED_YN := l_approved_yn;
1716 l_acnv_rec.DATE_APPROVED := SYSDATE;
1717
1718 IF upper(l_approved_yn) = 'Y' THEN
1719 l_acnv_rec.ACS_CODE := 'APPROVED';
1720 ELSE
1721 l_acnv_rec.ACS_CODE := 'REJECTED';
1722 END IF;
1723
1724 FOR c_rec IN c_rec_csr(l_id) LOOP
1725
1726 l_acnv_rec.ID := c_rec.id;
1727
1728 okl_acn_pvt.update_row( p_api_version => l_api_version,
1729 p_init_msg_list => l_init_msg_list,
1730 x_return_status => l_return_status,
1731 x_msg_count => x_msg_count,
1732 x_msg_data => x_msg_data,
1733 p_acnv_rec => l_acnv_rec,
1734 x_acnv_rec => x_acnv_rec);
1735
1736 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1737 RAISE ERR;
1738 END IF;
1739 END LOOP;
1740
1741
1742 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1743 resultout := 'COMPLETE:SUCCESS';
1744 ELSE
1745 resultout := 'COMPLETE:ERROR';
1746 END IF;
1747
1748 RETURN ;
1749
1750 END IF;
1751 --
1752 -- CANCEL mode
1753 --
1754 IF (funcmode = 'CANCEL') THEN
1755 --
1756 resultout := 'COMPLETE:';
1757 RETURN;
1758 --
1759 END IF;
1760 --
1761 -- TIMEOUT mode
1762 --
1763 IF (funcmode = 'TIMEOUT') THEN
1764 --
1765 resultout := 'COMPLETE:';
1766 RETURN;
1767 --
1768 END IF;
1769
1770 EXCEPTION
1771 WHEN ERR THEN
1772 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1773 RAISE;
1774
1775 WHEN OTHERS THEN
1776 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1777 RAISE;
1778
1779 END set_approved_yn;
1780
1781 -- Start of comments
1782 --
1783 -- Procedure Name : pop_asset_repair_att
1784 -- Description : populate asset repair attributes from WF
1785 -- Business Rules :
1786 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1787 -- Version : 1.0
1788 --
1789 -- End of comments
1790 PROCEDURE POP_ASSET_REPAIR_ATT( itemtype IN VARCHAR2,
1791 itemkey IN VARCHAR2,
1792 actid IN NUMBER,
1793 funcmode IN VARCHAR2,
1794 resultout OUT NOCOPY VARCHAR2 ) AS
1795
1796 l_id NUMBER;
1797 l_no_data_found BOOLEAN;
1798 l_user WF_USERS.NAME%TYPE;
1799 l_name WF_USERS.DESCRIPTION%TYPE;
1800 l_message VARCHAR2(30000);
1801
1802 -- cursor to populate notification attributes
1803 --Bug # 6174484 ssdeshpa Fixed Cursor for SQL Performance start
1804 CURSOR okl_asset_repair_csr(c_id NUMBER)
1805 IS
1806 /* SELECT ACN.LAST_UPDATED_BY LAST_UPDATED_BY,
1807 AD.ASSET_NUMBER ASSET_NUMBER,
1808 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1809 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1810 ACN.PART_NAME PART_NAME,
1811 ACN.RECOMMENDED_REPAIR DETAILS,
1812 FND1.MEANING CONDITION_TYPE,
1813 FND2.MEANING DAMAGE_TYPE
1814 FROM OKL_AM_ASSET_RETURNS_UV AD,
1815 OKL_ASSET_CNDTNS ACD,
1816 OKL_ASSET_CNDTN_LNS_V ACN,
1817 FND_LOOKUPS FND1,
1818 FND_LOOKUPS FND2
1819 WHERE ACD.KLE_ID = AD.KLE_ID
1820 AND ACD.ID = C_ID
1821 AND ACD.ID = ACN.ACD_ID
1822 AND upper(nvl(ACN.APPROVED_YN, 'N')) <> 'Y'
1823 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL'
1824 AND FND1.LOOKUP_TYPE = 'OKL_ASSET_CONDITION'
1825 AND FND1.LOOKUP_CODE = ACN.CDN_CODE
1826 AND FND2.LOOKUP_TYPE = 'OKL_DAMAGE_TYPE'
1827 AND FND2.LOOKUP_CODE = ACN.DTY_CODE;*/
1828
1829 SELECT ACNB.LAST_UPDATED_BY LAST_UPDATED_BY,
1830 KLE.NAME ASSET_NUMBER,
1831 KLE.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1832 OKC.CONTRACT_NUMBER CONTRACT_NUMBER,
1833 ACNT.PART_NAME PART_NAME,
1834 ACNT.RECOMMENDED_REPAIR DETAILS,
1835 FND1.MEANING CONDITION_TYPE,
1836 FND2.MEANING DAMAGE_TYPE
1837 FROM OKL_ASSET_RETURNS_B OAR,
1838 OKC_K_HEADERS_ALL_B OKC,
1839 OKC_K_LINES_V KLE,
1840 OKL_ASSET_CNDTNS_ALL ACD,
1841 OKL_AST_CNDTN_LNS_ALL_B ACNB,
1842 OKL_ASSET_CNDTN_LNS_TL ACNT,
1843 FND_LOOKUPS FND1,
1844 FND_LOOKUPS FND2
1845 WHERE OKC.ID = KLE.CHR_ID
1846 AND OAR.KLE_ID = KLE.ID
1847 AND ACD.KLE_ID = OAR.KLE_ID
1848 AND ACD.ID = c_id
1849 AND ACNB.ID = ACNT.ID
1850 AND ACNT.LANGUAGE = USERENV('LANG')
1851 AND ACD.ID = ACNB.ACD_ID
1852 AND UPPER(NVL(ACNB.APPROVED_YN, 'N')) <> 'Y'
1853 AND ACNB.ACS_CODE = 'WAITING_FOR_APPROVAL'
1854 AND FND1.LOOKUP_TYPE = 'OKL_ASSET_CONDITION'
1855 AND FND1.LOOKUP_CODE = ACNB.CDN_CODE
1856 AND FND2.LOOKUP_TYPE = 'OKL_DAMAGE_TYPE'
1857 AND FND2.LOOKUP_CODE = ACNB.DTY_CODE;
1858
1859 --Bug # 6174484 ssdeshpa Fixed Cursor for SQL Performance start
1860
1861 l_header_done BOOLEAN := FALSE;
1862 l_updated_by NUMBER;
1863
1864 BEGIN
1865
1866 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1867 itemkey => itemkey,
1868 aname => 'TRANSACTION_ID');
1869
1870 --build message (temp)
1871
1872 FOR l_asset_repair_rec in okl_asset_repair_csr(l_id) LOOP
1873
1874 IF NOT l_header_done THEN
1875 l_message := '<p>The repairs listed below are requested for the Asset '||l_asset_repair_rec.asset_number||'<br>'||
1876 l_asset_repair_rec.asset_description||' from Contract Number '||l_asset_repair_rec.contract_number||'</p>'||
1877 '<p>The repairs will be completed following your approval.</p>'||
1878 '<table width="50%" border="1">'||
1879 '<tr>'||
1880 '<td><b>Part<b/></td>'||
1881 '<td><b>Condition Type<b/></td>'||
1882 '<td><b>Damage Type<b/></td>'||
1883 '<td><b>Details<b/></td>'||
1884 '</tr>';
1885 l_header_done := TRUE;
1886 l_updated_by := l_asset_repair_rec.last_updated_by;
1887 END IF;
1888
1889 l_message := l_message||'<tr>'||
1890 '<td>'||l_asset_repair_rec.part_name||'</td>'||
1891 '<td>'||l_asset_repair_rec.condition_type||'</td>'||
1892 '<td>'||l_asset_repair_rec.damage_type||'</td>'||
1893 '<td>'||l_asset_repair_rec.details||'</td>'||
1894 '</tr>';
1895
1896 END LOOP;
1897
1898 IF l_header_done THEN
1899 l_message := l_message||'</table>';
1900 END IF;
1901
1902 okl_am_wf.get_notification_agent(
1903 itemtype => itemtype
1904 , itemkey => itemkey
1905 , actid => actid
1906 , funcmode => funcmode
1907 , p_user_id => l_updated_by
1908 , x_name => l_user
1909 , x_description => l_name);
1910
1911 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1912 itemkey => itemkey,
1913 aname => 'REQUESTER',
1914 avalue => l_user);
1915
1916 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1917 itemkey => itemkey,
1918 aname => 'WF_ADMINISTRATOR',
1919 avalue => l_user);
1920
1921 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1922 itemkey => itemkey,
1923 aname => 'TRX_TYPE_ID',
1924 avalue => 'OKLAMAAR');
1925
1926 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1927 itemkey => itemkey,
1928 aname => 'MESSAGE_DESCRIPTION',
1929 avalue => l_message);
1930 EXCEPTION
1931 WHEN OTHERS THEN
1932 IF okl_asset_repair_csr%ISOPEN THEN
1933 CLOSE okl_asset_repair_csr;
1934 END IF;
1935
1936 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1937 RAISE;
1938
1939 END POP_ASSET_REPAIR_ATT;
1940
1941 -- Start of comments
1942 --
1943 -- Procedure Name : populate_itd_atts
1944 -- Description : populate Internal Transport Department notification attributes
1945 -- Business Rules :
1946 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1947 -- Version : 1.0
1948 --
1949 -- End of comments
1950 PROCEDURE populate_itd_atts(itemtype IN VARCHAR2,
1951 itemkey IN VARCHAR2,
1952 actid IN NUMBER,
1953 funcmode IN VARCHAR2,
1954 resultout OUT NOCOPY VARCHAR2) AS
1955
1956 l_art_id NUMBER;
1957 l_no_data_found BOOLEAN;
1958 -- l_performer WF_USERS.NAME%TYPE;
1959 l_requester WF_USERS.NAME%TYPE;
1960 l_name WF_USERS.DISPLAY_NAME%TYPE;
1961 -- cursor to populate notification attributes
1962
1963 CURSOR okl_asset_return_csr(c_art_id NUMBER)
1964 IS
1965 /* SELECT OAR.LAST_UPDATED_BY LAST_UPDATED_BY,
1966 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1967 AD.NAME ASSET_NUMBER,
1968 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1969 AD.SERIAL_NUMBER SERIAL_NUMBER,
1970 AD.MODEL_NUMBER MODEL_NUMBER,
1971 OAR.COMMENTS COMMENTS
1972 FROM OKL_AM_ASSET_DETAILS_UV AD, OKL_ASSET_RETURNS_V OAR
1973 WHERE AD.ID = OAR.KLE_ID
1974 AND OAR.ID = c_art_id;
1975 */
1976 SELECT OAR.LAST_UPDATED_BY LAST_UPDATED_BY,
1977 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1978 AD.NAME ASSET_NUMBER,
1979 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1980 AD.SERIAL_NUMBER SERIAL_NUMBER,
1981 AD.MODEL_NUMBER MODEL_NUMBER,
1982 OAR.COMMENTS COMMENTS,
1983 C.CONTACT_PARTY_NAME CONTACT_NAME,
1984 CP.CONTACT_DETAILS CONTACT_DETAILS
1985 FROM OKL_AM_ASSET_DETAILS_UV AD,
1986 OKL_ASSET_RETURNS_V OAR,
1987 OKL_RELOCATE_ASSETS_V ORA,
1988 OKL_AM_CONTACT_POINTS_UV CP,
1989 OKL_AM_CONTACTS_UV C
1990 WHERE AD.ID = OAR.KLE_ID
1991 AND OAR.ID = c_art_id
1992 AND OAR.ID = ORA.ART_ID
1993 AND ORA.PAC_ID = CP.CONTACT_CONTACT_POINT_ID
1994 AND CP.CONTACT_PARTY_ID = C.CONTACT_PARTY_ID;
1995
1996 l_asset_return okl_asset_return_csr%rowtype;
1997
1998 BEGIN
1999 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2000 itemkey => itemkey,
2001 aname => 'TRANSACTION_ID');
2002
2003 OPEN okl_asset_return_csr(l_art_id);
2004 FETCH okl_asset_return_csr INTO l_asset_return;
2005 CLOSE okl_asset_return_csr;
2006
2007 -- Get the notification recipient from profile.
2008 /* l_performer := fnd_profile.value('OKL_TRANSPORTATION_NOTIFICATION');
2009
2010 -- get the requestor
2011 OKL_AM_WF.GET_NOTIFICATION_AGENT(
2012 itemtype => itemtype,
2013 itemkey => itemkey,
2014 actid => actid,
2015 funcmode => funcmode,
2016 p_user_id => l_asset_return.last_updated_by,
2017 x_name => l_requester,
2018 x_description => l_name);
2019
2020 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2021 itemkey => itemkey,
2022 aname => 'REQUESTER',
2023 avalue => l_requester);
2024
2025 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2026 itemkey => itemkey,
2027 aname => 'PERFORMING_AGENT',
2028 avalue => l_performer);
2029 */
2030 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2031 itemkey => itemkey,
2032 aname => 'CONTACT_NAME',
2033 avalue => l_asset_return.CONTACT_NAME);
2034
2035 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2036 itemkey => itemkey,
2037 aname => 'CONTACT_METHOD',
2038 avalue => l_asset_return.CONTACT_DETAILS);
2039
2040 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2041 itemkey => itemkey,
2042 aname => 'CONTRACT_NUMBER',
2043 avalue => l_asset_return.CONTRACT_NUMBER);
2044
2045 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2046 itemkey => itemkey,
2047 aname => 'ASSET_NUMBER',
2048 avalue => l_asset_return.ASSET_NUMBER);
2049
2050 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2051 itemkey => itemkey,
2052 aname => 'ASSET_DESCRIPTION',
2053 avalue => l_asset_return.asset_description);
2054
2055 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2056 itemkey => itemkey,
2057 aname => 'DETAILS',
2058 avalue => l_asset_return.comments);
2059
2060 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2061 itemkey => itemkey,
2062 aname => 'SERIAL_NUMBER',
2063 avalue => l_asset_return.serial_number);
2064
2065 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2066 itemkey => itemkey,
2067 aname => 'MODEL_NUMBER',
2068 avalue => l_asset_return.model_number);
2069
2070 EXCEPTION
2071 WHEN OTHERS THEN
2072 IF okl_asset_return_csr%ISOPEN THEN
2073 CLOSE okl_asset_return_csr;
2074 END IF;
2075
2076 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'populate_itd_atts', itemtype, itemkey, actid, funcmode);
2077 RAISE;
2078 END populate_itd_atts;
2079
2080 PROCEDURE VALIDATE_CONT_PORT (itemtype IN VARCHAR2,
2081 itemkey IN VARCHAR2,
2082 actid IN NUMBER,
2083 funcmode IN VARCHAR2,
2084 resultout OUT NOCOPY VARCHAR2) IS
2085
2086 CURSOR okl_pfc_csr(c_id NUMBER)
2087 IS
2088 SELECT count(1)
2089 FROM OKL_PRTFL_CNTRCTS_B PFC
2090 WHERE PFC.KHR_ID = c_id;
2091
2092 l_id NUMBER;
2093 l_count NUMBER;
2094
2095 BEGIN
2096
2097 IF (funcmode = 'RUN') THEN
2098 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2099 itemkey => itemkey,
2100 aname => 'TRANSACTION_ID');
2101
2102
2103 OPEN okl_pfc_csr(l_id);
2104 FETCH okl_pfc_csr into l_count;
2105 CLOSE okl_pfc_csr;
2106
2107 IF l_count > 0 THEN
2108 resultout := 'COMPLETE:VALID';
2109 ELSE
2110 resultout := 'COMPLETE:INVALID';
2111 END IF;
2112
2113 RETURN ;
2114
2115 END IF;
2116
2117 EXCEPTION
2118 WHEN OTHERS THEN
2119 IF okl_pfc_csr%ISOPEN THEN
2120 CLOSE okl_pfc_csr;
2121 END IF;
2122
2123 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'VALIDATE_CONT_PORT', itemtype, itemkey, actid, funcmode);
2124 RAISE;
2125 END VALIDATE_CONT_PORT;
2126
2127 -- Start of comments
2128 --
2129 -- Procedure Name : POP_CONT_PORT_ATT
2130 -- Description : populate portfolio approval message attributes
2131 -- Business Rules :
2132 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2133 -- Version : 1.0
2134 --
2135 -- End of comments
2136 PROCEDURE POP_CONT_PORT_ATT( itemtype IN VARCHAR2,
2137 itemkey IN VARCHAR2,
2138 actid IN NUMBER,
2139 funcmode IN VARCHAR2,
2140 resultout OUT NOCOPY VARCHAR2) IS
2141
2142 l_id NUMBER;
2143 l_no_data_found BOOLEAN;
2144 l_user WF_USERS.NAME%TYPE;
2145
2146 l_message VARCHAR2(30000);
2147 -- cursor to populate notification attributes
2148
2149 CURSOR okl_pfc_csr(c_id NUMBER)
2150 IS
2151 SELECT TEAM_NAME ASSIGNMENT_GROUP,
2152 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
2153 FND.MEANING STRATEGY,
2154 PFCL.BUDGET_AMOUNT BUDGET,
2155 PFCL.DATE_STRATEGY_EXECUTION_DUE EXECUTION_DATE,
2156 PFC.LAST_UPDATED_BY LAST_UPDATED_BY
2157 FROM OKL_PRTFL_CNTRCTS_B PFC,
2158 OKL_PRTFL_LINES_V PFCL,
2159 OKC_K_HEADERS_V AD,
2160 FND_LOOKUPS FND,
2161 JTF_RS_TEAMS_VL T
2162 WHERE PFC.KHR_ID = c_id
2163 AND PFC.ID = PFCL.PFC_ID
2164 AND AD.ID = PFC.KHR_ID
2165 AND ASSET_TRACK_STRATEGY_CODE = FND.LOOKUP_CODE
2166 AND FND.LOOKUP_TYPE = 'OKL_ASSET_TRACK_STRATEGIES'
2167 AND TMB_ID = T.TEAM_ID;
2168
2169 l_header_done BOOLEAN := FALSE;
2170 l_user_name WF_USERS.name%type;
2171 l_name WF_USERS.description%type;
2172
2173 BEGIN
2174
2175 IF (funcmode = 'RUN') THEN
2176 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2177 itemkey => itemkey,
2178 aname => 'TRANSACTION_ID');
2179
2180 --build message
2181
2182 FOR l_pfc_rec in okl_pfc_csr(to_number(l_id)) LOOP
2183
2184 IF NOT l_header_done THEN
2185 l_message := '<p>'||l_pfc_rec.assignment_group||'</p>'||
2186 '<p>Contract Number:'||l_pfc_rec.contract_number||'</p>'||
2187 '<p>Please approve the following profile.</p>'||
2188 '<table width="50%" border="1">'||
2189 '<tr>'||
2190 '<td>Strategy</td>'||
2191 '<td>Budget</td>'||
2192 '<td>Execution Date</td>'||
2193 '</tr>';
2194 l_header_done := TRUE;
2195
2196 okl_am_wf.get_notification_agent(
2197 itemtype => itemtype
2198 , itemkey => itemkey
2199 , actid => actid
2200 , funcmode => funcmode
2201 , p_user_id => l_pfc_rec.last_updated_by
2202 , x_name => l_user_name
2203 , x_description => l_name);
2204 END IF;
2205
2206 l_message := l_message||'<tr>'||
2207 '<td>'||l_pfc_rec.strategy||'</td>'||
2208 '<td>'||l_pfc_rec.budget||'</td>'||
2209 '<td>'||l_pfc_rec.execution_date||'</td>'||
2210 '</tr>';
2211 END LOOP;
2212
2213 IF l_header_done THEN
2214 l_message := l_message||'</table>';
2215 END IF;
2216
2217 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2218 itemkey => itemkey,
2219 aname => 'TRX_TYPE_ID',
2220 avalue => 'OKLAMATK');
2221
2222 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2223 itemkey => itemkey,
2224 aname => 'MESSAGE_DESCRIPTION',
2225 avalue => l_message);
2226
2227 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2228 itemkey => itemkey,
2229 aname => 'REQUESTER',
2230 avalue => l_user_name);
2231
2232 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2233 itemkey => itemkey,
2234 aname => 'WF_ADMINISTRATOR',
2235 avalue => l_user);
2236 resultout := 'COMPLETE:';
2237 RETURN ;
2238 END IF;
2239
2240 --
2241 -- CANCEL mode
2242 --
2243 IF (funcmode = 'CANCEL') THEN
2244 --
2245 resultout := 'COMPLETE:';
2246 RETURN;
2247 --
2248 END IF;
2249
2250 --
2251 -- TIMEOUT mode
2252 --
2253 IF (funcmode = 'TIMEOUT') THEN
2254 --
2255 resultout := 'COMPLETE:';
2256 RETURN;
2257 --
2258 END IF;
2259
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 IF okl_pfc_csr%ISOPEN THEN
2263 CLOSE okl_pfc_csr;
2264 END IF;
2265
2266 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_CONT_PORT_ATT', itemtype, itemkey, actid, funcmode);
2267 RAISE;
2268 END POP_CONT_PORT_ATT;
2269
2270 -- Start of comments
2271 --
2272 -- Procedure Name : SET_CP_APPROVED_YN
2273 -- Description : Update the APPROVED FLAG for contract portfolio
2274 -- Business Rules :
2275 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2276 -- Version : 1.0
2277 --
2278 -- End of comments
2279 PROCEDURE SET_CP_APPROVED_YN( itemtype IN VARCHAR2,
2280 itemkey IN VARCHAR2,
2281 actid IN NUMBER,
2282 funcmode IN VARCHAR2,
2283 resultout OUT NOCOPY VARCHAR2) IS
2284
2285 CURSOR okl_pfc_csr(c_id NUMBER)
2286 IS
2287 SELECT PFCL.ID ID
2288 FROM OKL_PRTFL_LINES_B PFCL,
2289 OKL_PRTFL_CNTRCTS_B PFC
2290 WHERE PFC.KHR_ID = c_id
2291 AND PFC.ID = PFCL.PFC_ID;
2292
2293 l_pfc_rec okl_pfc_csr%rowtype;
2294 l_id NUMBER;
2295 l_approved_yn VARCHAR2(10);
2296
2297 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2298 x_msg_count NUMBER;
2299 x_msg_data VARCHAR2(2000);
2300 l_pflv_rec OKL_PRTFL_LINES_PUB.PFLV_REC_TYPE;
2301 x_pflv_rec OKL_PRTFL_LINES_PUB.PFLV_REC_TYPE;
2302 l_api_version NUMBER := 1;
2303 l_init_msg_list VARCHAR2(1) := 'T';
2304
2305 API_ERROR EXCEPTION;
2306
2307 BEGIN
2308
2309 IF (funcmode = 'RUN') THEN
2310 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2311 itemkey => itemkey,
2312 aname => 'TRANSACTION_ID');
2313
2314 l_approved_yn := wf_engine.GetItemAttrText( itemtype => itemtype,
2315 itemkey => itemkey,
2316 aname => 'APPROVED_YN');
2317
2318 IF upper(l_approved_yn) = 'Y' THEN
2319 l_pflv_rec.TRX_STATUS_CODE := 'APPROVED';
2320 ELSE
2321 l_pflv_rec.TRX_STATUS_CODE := 'REJECTED';
2322 END IF;
2323
2324 FOR c_rec in okl_pfc_csr(l_id) LOOP
2325
2326 l_pflv_rec.ID := c_rec.id;
2327
2328 okl_prtfl_lines_pub.update_prtfl_lines(
2329 p_api_version => l_api_version,
2330 p_init_msg_list => l_init_msg_list,
2331 x_return_status => l_return_status,
2332 x_msg_count => x_msg_count,
2333 x_msg_data => x_msg_data,
2334 p_pflv_rec => l_pflv_rec,
2335 x_pflv_rec => x_pflv_rec);
2336
2337 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2338 RAISE API_ERROR;
2339 END IF;
2340
2341 END LOOP;
2342
2343 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2344 RAISE API_ERROR;
2345 ELSE
2346 resultout := 'COMPLETE:SUCCESS';
2347 END IF;
2348
2349 RETURN ;
2350 END IF;
2351
2352 EXCEPTION
2353
2354 WHEN API_ERROR THEN
2355 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'SET_CP_APPROVED_YN', itemtype, itemkey, actid, funcmode);
2356 RAISE;
2357
2358 WHEN OTHERS THEN
2359 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'SET_CP_APPROVED_YN', itemtype, itemkey, actid, funcmode);
2360 RAISE;
2361
2362 END SET_CP_APPROVED_YN;
2363
2364 -- Start of comments
2365 --
2366 -- Procedure Name : POP_CPE_NOTIFY_ATT
2367 -- Description : populate assignment group notification attributes from WF
2368 -- Business Rules :
2369 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2370 -- Version : 1.0
2371 --
2372 -- End of comments
2373 PROCEDURE POP_CPE_NOTIFY_ATT( itemtype IN VARCHAR2,
2374 itemkey IN VARCHAR2,
2375 actid IN NUMBER,
2376 funcmode IN VARCHAR2,
2377 resultout OUT NOCOPY VARCHAR2) IS
2378
2379 l_id NUMBER;
2380 l_no_data_found BOOLEAN;
2381 l_user WF_USERS.NAME%TYPE;
2382
2383 l_message VARCHAR2(30000);
2384 -- cursor to populate notification attributes
2385 CURSOR okl_pfc_csr(c_id NUMBER)
2386 IS
2387 SELECT TMB_ID,
2388 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
2389 FND.MEANING STRATEGY,
2390 PFCL.BUDGET_AMOUNT BUDGET,
2391 PFCL.DATE_STRATEGY_EXECUTION_DUE EXECUTION_DATE,
2392 PFC.LAST_UPDATED_BY LAST_UPDATED_BY
2393 FROM OKL_PRTFL_CNTRCTS_B PFC,
2394 OKL_PRTFL_LINES_V PFCL,
2395 OKC_K_HEADERS_V AD,
2396 FND_LOOKUPS FND
2397 WHERE PFC.KHR_ID = c_id
2398 AND PFC.ID = PFCL.PFC_ID
2399 AND AD.ID = PFC.KHR_ID
2400 AND ASSET_TRACK_STRATEGY_CODE = FND.LOOKUP_CODE
2401 AND FND.LOOKUP_TYPE = 'OKL_ASSET_TRACK_STRATEGIES';
2402
2403
2404 l_pfc_rec okl_pfc_csr%rowtype;
2405
2406 -- cursor to find valid notification user
2407 CURSOR wf_users_csr(c_team_id NUMBER)
2408 IS
2409 SELECT count(1)
2410 FROM jtf_rs_teams_b t,
2411 jtf_rs_role_relations_vl jtfr,
2412 jtf_rs_Resource_extns a,
2413 jtf_rs_Team_Members b,
2414 jtf_rs_Groups_b d,
2415 jtf_rs_resource_extns re,
2416 wf_users WU
2417 WHERE
2418 t.team_id = c_team_id
2419 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
2420 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
2421 AND jtfr.role_code = 'PORTFOLIO_GROUP'
2422 AND role_resource_type = 'RS_TEAM'
2423 AND jtfr.role_resource_id = t.team_id
2424 AND t.team_id = b.Team_Id
2425 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
2426 d.Group_Number)) = re.resource_number
2427 AND b.Team_Resource_Id = a.Resource_Id (+)
2428 AND b.Team_Resource_Id = d.Group_Id (+)
2429 AND re.user_name = wu.name
2430 AND re.source_id = wu.orig_system_id;
2431
2432 l_user_name WF_USERS.name%type;
2433 l_name WF_USERS.description%type;
2434
2435 BEGIN
2436
2437 IF (funcmode = 'RUN') THEN
2438 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2439 itemkey => itemkey,
2440 aname => 'TRANSACTION_ID');
2441
2442 OPEN okl_pfc_csr(l_id);
2443 FETCH okl_pfc_csr INTO l_pfc_rec;
2444 CLOSE okl_pfc_csr;
2445
2446 OPEN wf_users_csr(l_pfc_rec.TMB_ID);
2447 FETCH wf_users_csr INTO l_user;
2448 CLOSE wf_users_csr;
2449
2450 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2451 itemkey => itemkey,
2452 aname => 'TOTAL_RECIPIENTS',
2453 avalue => l_user);
2454
2455 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2456 itemkey => itemkey,
2457 aname => 'CURRENT_RECIPIENT',
2458 avalue => l_user+1);
2459
2460 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2461 itemkey => itemkey,
2462 aname => 'CONTRACT_NUMBER',
2463 avalue => l_pfc_rec.CONTRACT_NUMBER);
2464
2465 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2466 itemkey => itemkey,
2467 aname => 'STRATEGY',
2468 avalue => l_pfc_rec.STRATEGY);
2469
2470 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2471 itemkey => itemkey,
2472 aname => 'ASSIGNMENT_GROUP_ID',
2473 avalue => to_char(l_pfc_rec.TMB_ID));
2474
2475 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2476 itemkey => itemkey,
2477 aname => 'BUDGET',
2478 avalue => l_pfc_rec.BUDGET);
2479
2480 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2481 itemkey => itemkey,
2482 aname => 'EXECUTION_DATE',
2483 avalue => l_pfc_rec.EXECUTION_DATE);
2484
2485 okl_am_wf.get_notification_agent(itemtype => itemtype
2486 ,itemkey => itemkey
2487 ,actid => actid
2488 ,funcmode => funcmode
2489 ,p_user_id => l_pfc_rec.last_updated_by
2490 ,x_name => l_user_name
2491 ,x_description => l_name);
2492
2493 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2494 itemkey => itemkey,
2495 aname => 'REQUESTER',
2496 avalue => l_user_name);
2497
2498 resultout := 'COMPLETE:';
2499 RETURN ;
2500 END IF;
2501
2502 --
2503 -- CANCEL mode
2504 --
2505 IF (funcmode = 'CANCEL') THEN
2506 --
2507 resultout := 'COMPLETE:';
2508 RETURN;
2509 --
2510 END IF;
2511
2512 --
2513 -- TIMEOUT mode
2514 --
2515 IF (funcmode = 'TIMEOUT') THEN
2516 --
2517 resultout := 'COMPLETE:';
2518 RETURN;
2519 --
2520 END IF;
2521
2522 EXCEPTION
2523 WHEN OTHERS THEN
2524 IF okl_pfc_csr%ISOPEN THEN
2525 CLOSE okl_pfc_csr;
2526 END IF;
2527
2528 IF wf_users_csr%ISOPEN THEN
2529 CLOSE wf_users_csr;
2530 END IF;
2531
2532 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_REMK_NOTIFY_ATT', itemtype, itemkey, actid, funcmode);
2533 RAISE;
2534 END POP_CPE_NOTIFY_ATT;
2535
2536
2537 -- Start of comments
2538 --
2539 -- Procedure Name : NOTIFY_ASS_GRP_USER
2540 -- Description : populate assignment group notification attributes from WF
2541 -- This procedure is called recursively until all team
2542 -- members have been notified.
2543 -- Business Rules :
2544 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2545 -- Version : 1.0
2546 --
2547 -- End of comments
2548 PROCEDURE NOTIFY_ASS_GRP_USER( itemtype IN VARCHAR2,
2549 itemkey IN VARCHAR2,
2550 actid IN NUMBER,
2551 funcmode IN VARCHAR2,
2552 resultout OUT NOCOPY VARCHAR2 ) AS
2553
2554 l_rmr_id NUMBER;
2555 l_total_rec NUMBER;
2556 l_current_rec NUMBER := 0;
2557 l_no_data_found BOOLEAN;
2558 l_user WF_USERS.NAME%TYPE;
2559 l_name WF_USERS.DISPLAY_NAME%TYPE;
2560 l_current_user WF_USERS.NAME%TYPE;
2561
2562 -- cursor to populate notification attributes
2563 CURSOR wf_users_csr(c_team_id NUMBER,
2564 c_current_user NUMBER,
2565 c_name VARCHAR)
2566 IS
2567 SELECT wu.name, wu.display_name
2568 FROM jtf_rs_teams_vl t,
2569 jtf_rs_role_relations_vl jtfr,
2570 jtf_rs_Resource_extns a,
2571 jtf_rs_Team_Members b,
2572 jtf_rs_Groups_b d,
2573 jtf_rs_resource_extns re,
2574 wf_users WU
2575 WHERE t.team_id = c_team_id
2576 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
2577 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
2578 AND jtfr.role_code = 'PORTFOLIO_GROUP'
2579 AND role_resource_type = 'RS_TEAM'
2580 AND jtfr.role_resource_id = t.team_id
2581 AND t.team_id = b.Team_Id
2582 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
2583 d.Group_Number)) = re.resource_number
2584 AND b.Team_Resource_Id = a.Resource_Id (+)
2585 AND b.Team_Resource_Id = d.Group_Id (+)
2586 AND re.source_id = wu.orig_system_id
2587 AND re.user_name = wu.name
2588 AND ROWNUM < c_current_user
2589 AND wu.name > nvl(c_name, '0')
2590 order by 1 asc;
2591
2592 BEGIN
2593
2594 IF (funcmode = 'RUN') THEN
2595
2596 l_rmr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2597 itemkey => itemkey,
2598 aname => 'ASSIGNMENT_GROUP_ID');
2599
2600 l_total_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2601 itemkey => itemkey,
2602 aname => 'TOTAL_RECIPIENTS');
2603
2604 l_current_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2605 itemkey => itemkey,
2606 aname => 'CURRENT_RECIPIENT');
2607
2608 l_current_user := wf_engine.GetItemAttrText( itemtype => itemtype,
2609 itemkey => itemkey,
2610 aname => 'PERFORMING_AGENT');
2611
2612
2613 OPEN wf_users_csr(l_rmr_id, l_current_rec, l_current_user);
2614 FETCH wf_users_csr INTO l_user, l_name ;
2615 CLOSE wf_users_csr;
2616
2617 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2618 itemkey => itemkey,
2619 aname => 'PERFORMING_AGENT',
2620 avalue => l_user);
2621
2622 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2623 itemkey => itemkey,
2624 aname => 'RECIPIENT_NAME',
2625 avalue => l_name);
2626
2627 l_current_rec := l_current_rec-1;
2628
2629 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
2630 itemkey => itemkey,
2631 aname => 'CURRENT_RECIPIENT',
2632 avalue => l_current_rec);
2633
2634
2635 IF l_current_rec = 0 THEN
2636 resultout := 'COMPLETE:NOTIFY_COMPLETE';
2637 ELSE
2638 resultout := 'COMPLETE:NOTIFY_OUTSTANDING';
2639 END IF;
2640
2641 RETURN ;
2642
2643 END IF;
2644 --
2645 -- CANCEL mode
2646 --
2647 IF (funcmode = 'CANCEL') THEN
2648 --
2649 resultout := 'COMPLETE:';
2650 RETURN;
2651 --
2652 END IF;
2653 --
2654 -- TIMEOUT mode
2655 --
2656 IF (funcmode = 'TIMEOUT') THEN
2657 --
2658 resultout := 'COMPLETE:';
2659 RETURN;
2660 --
2661 END IF;
2662
2663 EXCEPTION
2664 WHEN OTHERS THEN
2665 IF wf_users_csr%ISOPEN THEN
2666 CLOSE wf_users_csr;
2667 END IF;
2668
2669 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'NOTIFY_ASS_GRP_USER', itemtype, itemkey, actid, funcmode);
2670 RAISE;
2671
2672 END NOTIFY_ASS_GRP_USER;
2673
2674 -- Start of comments
2675 --
2676 -- Procedure Name : check_itd_request
2677 -- Description : Validate Notify Internal Trans. Dept. request from WF
2678 -- Business Rules :
2679 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2680 -- Version : 1.0
2681 --
2682 -- End of comments
2683 PROCEDURE check_itd_request( itemtype IN VARCHAR2,
2684 itemkey IN VARCHAR2,
2685 actid IN NUMBER,
2686 funcmode IN VARCHAR2,
2687 resultout OUT NOCOPY VARCHAR2 )IS
2688
2689 l_art_id NUMBER;
2690 l_last_updated_by NUMBER;
2691 l_requester VARCHAR2(100);
2692 l_description VARCHAR2(100);
2693
2694 -- cursor to check request is valid
2695 CURSOR okl_check_req_csr(c_art_id NUMBER)
2696 IS
2697 SELECT last_updated_by
2698 FROM OKL_ASSET_RETURNS_V
2699 WHERE ID= c_art_id;
2700
2701 BEGIN
2702
2703 IF (funcmode = 'RUN') THEN
2704
2705 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2706 itemkey => itemkey,
2707 aname => 'TRANSACTION_ID');
2708
2709 OPEN okl_check_req_csr(l_art_id);
2710 FETCH okl_check_req_csr INTO l_last_updated_by;
2711 CLOSE okl_check_req_csr;
2712
2713 OKL_AM_WF.GET_NOTIFICATION_AGENT(
2714 itemtype => itemtype,
2715 itemkey => itemkey,
2716 actid => actid,
2717 funcmode => funcmode,
2718 p_user_id => l_last_updated_by,
2719 x_name => l_requester,
2720 x_description => l_description);
2721
2722
2723 wf_engine.SetItemAttrText( itemtype => itemtype,
2724 itemkey => itemkey,
2725 aname => 'REQUESTER',
2726 avalue => l_requester);
2727
2728 wf_engine.SetItemAttrText( itemtype => itemtype,
2729 itemkey => itemkey,
2730 aname => 'WF_ADMINISTRATOR',
2731 avalue => l_requester);
2732
2733 IF l_last_updated_by IS NULL THEN
2734 resultout := 'COMPLETE:INVALID_RETURN';
2735 ELSE
2736 resultout := 'COMPLETE:VALID_RETURN';
2737 END IF;
2738
2739 RETURN ;
2740
2741 END IF;
2742 --
2743 -- CANCEL mode
2744 --
2745 IF (funcmode = 'CANCEL') THEN
2746 --
2747 resultout := 'COMPLETE:';
2748 RETURN;
2749 --
2750 END IF;
2751 --
2752 -- TIMEOUT mode
2753 --
2754 IF (funcmode = 'TIMEOUT') THEN
2755 --
2756 resultout := 'COMPLETE:';
2757 RETURN;
2758 --
2759 END IF;
2760
2761 EXCEPTION
2762 WHEN OTHERS THEN
2763 IF okl_check_req_csr%ISOPEN THEN
2764 CLOSE okl_check_req_csr;
2765 END IF;
2766
2767 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_itd_request', itemtype, itemkey, actid, funcmode);
2768 RAISE;
2769
2770 END check_itd_request;
2771
2772 -- Start of comments
2773 --
2774 -- Procedure Name : check_profile_recipient
2775 -- Description : check if the profile value for OKL_MANUAL_TERMINATION_QUOTE_REP
2776 -- returns valid recipients.
2777 -- Business Rules :
2778 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2779 -- Version : 1.0
2780 --
2781 -- End of comments
2782 PROCEDURE check_profile_recipient( itemtype IN VARCHAR2,
2783 itemkey IN VARCHAR2,
2784 actid IN NUMBER,
2785 funcmode IN VARCHAR2,
2786 resultout OUT NOCOPY VARCHAR2 ) AS
2787
2788
2789 l_id VARCHAR2(100);
2790 l_performer VARCHAR2(100);
2791 l_recipients NUMBER;
2792
2793 cursor c1_csr (p_value varchar) is
2794 select count(*)
2795 from WF_USER_ROLES WUR
2796 where WUR.ROLE_NAME = p_value;
2797
2798
2799 BEGIN
2800
2801 IF (funcmode = 'RUN') THEN
2802 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2803 itemkey => itemkey,
2804 aname => 'TRANSACTION_ID');
2805
2806 -- Get the notification recipient from profile.
2807 l_performer := fnd_profile.value('OKL_TRANSPORTATION_NOTIFICATION');
2808
2809 wf_engine.SetItemAttrText( itemtype => itemtype,
2810 itemkey => itemkey,
2811 aname => 'PERFORMING_AGENT',
2812 avalue => l_performer);
2813
2814
2815 OPEN c1_csr (l_performer);
2816 FETCH c1_csr INTO l_recipients;
2817 CLOSE c1_csr;
2818
2819 IF l_recipients > 0 THEN
2820 resultout := 'COMPLETE:VALID_RETURN';
2821 ELSE
2822 resultout := 'COMPLETE:INVALID_RETURN';
2823 END IF;
2824
2825 RETURN ;
2826 END IF;
2827
2828 --
2829 -- CANCEL mode
2830 --
2831 IF (funcmode = 'CANCEL') THEN
2832 --
2833 resultout := 'COMPLETE:';
2834 RETURN;
2835 --
2836 END IF;
2837
2838 --
2839 -- TIMEOUT mode
2840 --
2841 IF (funcmode = 'TIMEOUT') THEN
2842 --
2843 resultout := 'COMPLETE:';
2844 RETURN;
2845 --
2846 END IF;
2847
2848 EXCEPTION
2849
2850 WHEN OTHERS THEN
2851
2852 IF c1_csr%ISOPEN THEN
2853 CLOSE c1_csr;
2854 END IF;
2855
2856 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_profile_recipient', itemtype, itemkey, actid, funcmode);
2857 RAISE;
2858
2859 END check_profile_recipient;
2860
2861 END OKL_AM_ASSET_RETURN_WF;