[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.15 2007/08/28 20:41:14 rkuttiya noship $ */
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_Resource_extns a,
734 jtf_rs_Team_Members b,
735 jtf_rs_Groups_b d,
736 jtf_rs_resource_extns re,
737 wf_users WU
738 WHERE t.team_id = c_team_id
739 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
740 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
741 AND jtfr.role_code = 'REMARKETER'
742 AND role_resource_type = 'RS_TEAM'
743 AND jtfr.role_resource_id = t.team_id
744 AND t.team_id = b.Team_Id
745 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
746 d.Group_Number)) = re.resource_number
747 AND re.source_id = wu.orig_system_id
748 AND re.user_name = wu.name
749 AND b.Team_Resource_Id = a.Resource_Id (+)
750 AND b.Team_Resource_Id = d.Group_Id (+)
751 AND ROWNUM < c_current_user
752 AND wu.name > nvl(c_name, '0')
753 order by 1 asc;
754
755 BEGIN
756
757 IF (funcmode = 'RUN') THEN
758
759 l_rmr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
760 itemkey => itemkey,
761 aname => 'REMARKETER_ID');
762
763 l_total_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
764 itemkey => itemkey,
765 aname => 'TOTAL_RECIPIENTS');
766
767 l_current_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
768 itemkey => itemkey,
769 aname => 'CURRENT_RECIPIENT');
770
771 l_current_user := wf_engine.GetItemAttrText( itemtype => itemtype,
772 itemkey => itemkey,
773 aname => 'PERFORMING_AGENT');
774
775
776 OPEN wf_users_csr(l_rmr_id, l_current_rec, l_current_user);
777 FETCH wf_users_csr INTO l_user, l_name ;
778 CLOSE wf_users_csr;
779
780 wf_engine.SetItemAttrText ( itemtype=> itemtype,
781 itemkey => itemkey,
782 aname => 'PERFORMING_AGENT',
783 avalue => l_user);
784
785 wf_engine.SetItemAttrText ( itemtype=> itemtype,
786 itemkey => itemkey,
787 aname => 'RECIPIENT_NAME',
788 avalue => l_name);
789
790 l_current_rec := l_current_rec-1;
791
792 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
793 itemkey => itemkey,
794 aname => 'CURRENT_RECIPIENT',
795 avalue => l_current_rec);
796
797
798 IF l_current_rec = 0 THEN
799 resultout := 'COMPLETE:NOTIFY_COMPLETE';
800 ELSE
801 resultout := 'COMPLETE:NOTIFY_OUTSTANDING';
802 END IF;
803
804 RETURN ;
805
806 END IF;
807 --
808 -- CANCEL mode
809 --
810 IF (funcmode = 'CANCEL') THEN
811 --
812 resultout := 'COMPLETE:';
813 RETURN;
814 --
815 END IF;
816 --
817 -- TIMEOUT mode
818 --
819 IF (funcmode = 'TIMEOUT') THEN
820 --
821 resultout := 'COMPLETE:';
822 RETURN;
823 --
824 END IF;
825
826 EXCEPTION
827 WHEN OTHERS THEN
828 IF wf_users_csr%ISOPEN THEN
829 CLOSE wf_users_csr;
830 END IF;
831
832 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'NOTIFY_REMK_USER', itemtype, itemkey, actid, funcmode);
833 RAISE;
834
835 END NOTIFY_REMK_USER;
836
837 -- Start of comments
838 --
839 -- Procedure Name : check_asset_return
840 -- Description : validate asset return is for a repossession from WF
841 -- Business Rules :
842 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
843 -- Version : 1.0
844 --
845 -- End of comments
846 PROCEDURE check_asset_return( itemtype IN VARCHAR2,
847 itemkey IN VARCHAR2,
848 actid IN NUMBER,
849 funcmode IN VARCHAR2,
850 resultout OUT NOCOPY VARCHAR2 )IS
851
852 l_art_id NUMBER;
853 l_code okl_asset_returns_v.ars_code%type;
854
855 -- Check that the asset return refers to a Repossession Request
856 CURSOR okl_check_req_csr(c_art_id NUMBER)
857 IS
858 SELECT ARS_CODE
859 FROM OKL_ASSET_RETURNS_V
860 WHERE ID= c_art_id
861 AND ARS_CODE IN ('REPOSSESSED', 'UNSUCCESS_REPO')
862 AND ART1_CODE = 'REPOS_REQUEST';
863
864
865 BEGIN
866
867 IF (funcmode = 'RUN') THEN
868
869 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
870 itemkey => itemkey,
871 aname => 'TRANSACTION_ID');
872
873 OPEN okl_check_req_csr(l_art_id);
874 FETCH okl_check_req_csr INTO l_code;
875 CLOSE okl_check_req_csr;
876
877 IF l_code = 'UNSUCCESS_REPO' THEN
878 resultout := 'COMPLETE:ASSET_NOT_RETURNED';
879 ELSIF l_code = 'REPOSSESSED' THEN
880 resultout := 'COMPLETE:ASSET_RETURNED';
881 END IF;
882
883 -- At this point populate the attributes required for the notification
884 populate_notification_attribs(itemtype => itemtype,
885 itemkey => itemkey,
886 actid => actid,
887 funcmode => funcmode,
888 p_art_id => l_art_id );
889
890 RETURN ;
891
892 END IF;
893 --
894 -- CANCEL mode
895 --
896 IF (funcmode = 'CANCEL') THEN
897 --
898 resultout := 'COMPLETE:';
899 RETURN;
900 --
901 END IF;
902 --
903 -- TIMEOUT mode
904 --
905 IF (funcmode = 'TIMEOUT') THEN
906 --
907 resultout := 'COMPLETE:';
908 RETURN;
909 --
910 END IF;
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 IF okl_check_req_csr%ISOPEN THEN
915 CLOSE okl_check_req_csr;
916 END IF;
917
918 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_asset_return', itemtype, itemkey, actid, funcmode);
919 RAISE;
920
921 END check_asset_return;
922
923 -- Start of comments
924 --
925 -- Procedure Name : check_return_type
926 -- Description : validate asset return from WF
927 -- Business Rules :
928 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
929 -- Version : 1.0
930 --
931 -- End of comments
932 PROCEDURE check_return_type ( itemtype IN VARCHAR2,
933 itemkey IN VARCHAR2,
934 actid IN NUMBER,
935 funcmode IN VARCHAR2,
936 resultout OUT NOCOPY VARCHAR2 )IS
937
938 l_art_id NUMBER;
939 l_knt NUMBER;
940
941 -- Check that the asset return refers to a Repossession Request
942 CURSOR okl_check_req_csr(c_art_id NUMBER)
943 IS
944 SELECT count(*)
945 FROM OKL_ASSET_RETURNS_V
946 WHERE ID= c_art_id
947 AND ART1_CODE = 'REPOS_REQUEST';
948
949 BEGIN
950
951 IF (funcmode = 'RUN') THEN
952
953 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
954 itemkey => itemkey,
955 aname => 'TRANSACTION_ID');
956
957 OPEN okl_check_req_csr(l_art_id);
958 FETCH okl_check_req_csr INTO l_knt;
959 CLOSE okl_check_req_csr;
960
961 IF l_knt = 0 THEN
962 resultout := 'COMPLETE:NON_REPO_RETURN';
963 ELSE
964 resultout := 'COMPLETE:REPO_RETURN';
965 END IF;
966
967 RETURN ;
968
969 END IF;
970 --
971 -- CANCEL mode
972 --
973 IF (funcmode = 'CANCEL') THEN
974 --
975 resultout := 'COMPLETE:';
976 RETURN;
977 --
978 END IF;
979 --
980 -- TIMEOUT mode
981 --
982 IF (funcmode = 'TIMEOUT') THEN
983 --
984 resultout := 'COMPLETE:';
985 RETURN;
986 --
987 END IF;
988
989 EXCEPTION
990 WHEN OTHERS THEN
991 IF okl_check_req_csr%ISOPEN THEN
992 CLOSE okl_check_req_csr;
993 END IF;
994
995 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_return_type', itemtype, itemkey, actid, funcmode);
996 RAISE;
997
998 END check_return_type;
999
1000 -- Start of comments
1001 --
1002 -- Procedure Name : check_role_exists
1003 -- Description : check notification is sent to a valid user from WF
1004 -- Business Rules :
1005 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1006 -- Version : 1.0
1007 --
1008 -- End of comments
1009 PROCEDURE check_role_exists ( itemtype IN VARCHAR2,
1010 itemkey IN VARCHAR2,
1011 actid IN NUMBER,
1012 funcmode IN VARCHAR2,
1013 resultout OUT NOCOPY VARCHAR2 )IS
1014
1015 l_creator NUMBER;
1016 l_role VARCHAR2(100);
1017 l_name VARCHAR2(100);
1018
1019 BEGIN
1020
1021 IF (funcmode = 'RUN') THEN
1022
1023 l_creator := wf_engine.GetItemAttrText( itemtype => itemtype,
1024 itemkey => itemkey,
1025 aname => 'CREATED_BY');
1026
1027
1028 okl_am_wf.get_notification_agent(
1029 itemtype => itemtype
1030 , itemkey => itemkey
1031 , actid => actid
1032 , funcmode => funcmode
1033 , p_user_id => l_creator
1034 , x_name => l_role
1035 , x_description => l_name);
1036
1037 IF l_role IS NULL THEN
1038 resultout := 'COMPLETE:ROLE_NOT_FOUND';
1039 ELSE
1040
1041 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1042 itemkey => itemkey,
1043 aname => 'REQUESTER',
1044 avalue => l_role);
1045
1046 resultout := 'COMPLETE:ROLE_FOUND';
1047 END IF;
1048
1049 RETURN ;
1050
1051 END IF;
1052 --
1053 -- CANCEL mode
1054 --
1055 IF (funcmode = 'CANCEL') THEN
1056 --
1057 resultout := 'COMPLETE:';
1058 RETURN;
1059 --
1060 END IF;
1061 --
1062 -- TIMEOUT mode
1063 --
1064 IF (funcmode = 'TIMEOUT') THEN
1065 --
1066 resultout := 'COMPLETE:';
1067 RETURN;
1068 --
1069 END IF;
1070
1071 EXCEPTION
1072 WHEN OTHERS THEN
1073
1074 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_role_exists', itemtype, itemkey, actid, funcmode);
1075 RAISE;
1076
1077 END check_role_exists;
1078
1079 -- Start of comments
1080 --
1081 -- Procedure Name : validate_title_ret
1082 -- Description : title return request from WF
1083 -- Business Rules :
1084 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1085 -- Version : 1.0
1086 --
1087 -- End of comments
1088 PROCEDURE validate_title_ret( itemtype IN VARCHAR2,
1089 itemkey IN VARCHAR2,
1090 actid IN NUMBER,
1091 funcmode IN VARCHAR2,
1092 resultout OUT NOCOPY VARCHAR2 )IS
1093
1094 l_art_id NUMBER;
1095 l_code okl_asset_returns_v.ars_code%type;
1096
1097 -- Check that the asset return refers to a Repossession Request
1098 CURSOR okl_check_req_csr(c_art_id NUMBER)
1099 IS
1100 SELECT OAR.KLE_ID KLE_ID, KLE.CHR_ID CHR_ID, OAR.LAST_UPDATED_BY LAST_UPDATED_BY
1101 FROM okl_asset_returns_b OAR, OKL_K_LINES_FULL_V KLE
1102 WHERE OAR.KLE_ID = KLE.ID
1103 AND OAR.ID= c_art_id;
1104
1105 l_art_rec okl_check_req_csr%rowtype;
1106 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
1107 l_rule_rec okl_rule_pub.rulv_rec_type;
1108 l_party_object_tbl okl_am_parties_pvt.party_object_tbl_type;
1109 l_object_tbl okl_am_util_pvt.jtf_object_tbl_type;
1110
1111
1112 --12/18/06 rkuttiya added for XMLP Project
1113
1114 x_msg_count NUMBER;
1115 x_msg_data VARCHAR2(2000);
1116 l_api_version NUMBER := 1;
1117 l_init_msg_list VARCHAR2(1) := 'T';
1118
1119 ERR EXCEPTION;
1120 l_batch_id NUMBER;
1121 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1122 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1123 --get the recipient email address
1124 CURSOR c_recipient(p_recipient_id IN NUMBER)
1125 IS
1126 SELECT hzp.email_address email
1127 FROM hz_parties hzp
1128 WHERE hzp.party_id = p_recipient_id;
1129
1130 -- get the sender email address
1131 CURSOR c_agent_csr (c_agent_id NUMBER) IS
1132 SELECT nvl(ppf.email_address , fu.email_address) email
1133 FROM fnd_user fu,
1134 per_people_f ppf
1135 WHERE fu.employee_id = ppf.person_id (+)
1136 AND fu.user_id = c_agent_id;
1137 l_from_email VARCHAR2(100);
1138 l_to_email VARCHAR2(100);
1139
1140 BEGIN
1141
1142 IF (funcmode = 'RUN') THEN
1143
1144 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1145 itemkey => itemkey,
1146 aname => 'TRANSACTION_ID');
1147
1148 OPEN okl_check_req_csr(l_art_id);
1149 FETCH okl_check_req_csr INTO l_art_rec;
1150 CLOSE okl_check_req_csr;
1151
1152 IF l_art_rec.KLE_ID IS NOT NULL THEN
1153
1154 -- First get the party id from the rule if the custodian is a 3rd party
1155 okl_am_util_pvt.get_rule_record (
1156 p_rgd_code => 'LAAFLG',
1157 p_rdf_code => 'LAFLTL',
1158 p_chr_id => l_art_rec.chr_id,
1159 p_cle_id => l_art_rec.kle_id,
1160 x_rulv_rec => l_rule_rec,
1161 x_return_status => l_return_status,
1162 p_message_yn => TRUE); -- put error message on stack if there is no rule
1163
1164 END IF;
1165
1166 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
1167
1168 resultout := 'COMPLETE:VALID';
1169
1170 -- get party name
1171 -- To Do: change to okl_am_util_pvt MDOKAL
1172 okl_am_util_pvt.get_object_details (
1173 p_object_code => l_rule_rec.jtot_object2_code, -- no need to hard-code
1174 p_object_id1 => l_rule_rec.object2_id1, -- correct field to use
1175 p_object_id2 => l_rule_rec.object2_id2, -- correct field to use
1176 x_object_tbl => l_object_tbl,
1177 x_return_status => l_return_status);
1178
1179 -- Item Attributes for Fulfillment
1180 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1181 itemkey => itemkey,
1182 aname => 'CREATED_BY',
1183 avalue => l_art_rec.LAST_UPDATED_BY);
1184
1185 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1186 itemkey => itemkey,
1187 aname => 'PROCESS_CODE',
1188 avalue => 'AMRTR');
1189
1190 --12/20/06 rkuttiya commented to change recipient type to LESSEE, for XMLP Project
1191 /*wf_engine.SetItemAttrText ( itemtype=> itemtype,
1192 itemkey => itemkey,
1193 aname => 'RECIPIENT_TYPE',
1194 avalue => 'P'); */
1195
1196 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1197 itemkey => itemkey,
1198 aname => 'RECIPIENT_TYPE',
1199 avalue => 'LESSEE');
1200
1201 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1202 itemkey => itemkey,
1203 aname => 'RECIPIENT_ID',
1204 avalue => nvl(l_rule_rec.object2_id1, l_rule_rec.object1_id1));
1205
1206 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1207 itemkey => itemkey,
1208 aname => 'RECIPIENT_DESCRIPTION',
1209 avalue => l_object_tbl(1).name );
1210
1211
1212 --12/18/06 rkuttiya modified for XMLP Project
1213 --set the From Address and TO Address
1214 OPEN c_recipient(nvl(l_rule_rec.object2_id1, l_rule_rec.object1_id1));
1215 FETCH c_recipient INTO l_to_email;
1216 CLOSE c_recipient;
1217
1218 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1219 itemkey => itemkey,
1220 aname => 'EMAIL_ADDRESS', -- 20/07/2007 ansethur modified the Item attribute name
1221 avalue => l_to_email);
1222
1223 OPEN c_agent_csr(l_art_rec.last_updated_by);
1224 FETCH c_agent_csr into l_from_email;
1225 CLOSE c_agent_csr;
1226
1227 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1228 itemkey => itemkey,
1229 aname => 'FROM_ADDRESS', -- 20/07/2007 ansethur modified the Item attribute name
1230 avalue => l_from_email);
1231
1232
1233 --18-Dec-06 rkuttiya added for XMLP Project
1234 --code for inserting bind parameters into table
1235
1236 l_xmp_rec.param_name := 'P_ART_ID';
1237 l_xmp_rec.param_value := l_art_id;
1238 l_xmp_rec.param_type_code := 'NUMBER';
1239
1240 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
1241 p_api_version => l_api_version
1242 ,p_init_msg_list => l_init_msg_list
1243 ,x_return_status => l_return_status
1244 ,x_msg_count => x_msg_count
1245 ,x_msg_data => x_msg_data
1246 ,p_xmp_rec => l_xmp_rec
1247 ,x_xmp_rec => lx_xmp_rec
1248 );
1249
1250 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1251 RAISE ERR;
1252 END IF;
1253
1254
1255 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1256 l_batch_id := lx_xmp_rec.batch_id;
1257 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1258 itemkey => itemkey,
1259 aname => 'BATCH_ID',
1260 avalue => l_batch_id );
1261 resultout := 'COMPLETE:VALID'; -- 20/07/2007 ansethur modified the value passed
1262 ELSE
1263 resultout := 'COMPLETE:ERROR';
1264 END IF;
1265
1266 ELSE
1267 resultout := 'COMPLETE:INVALID';
1268 END IF;
1269
1270 RETURN ;
1271
1272 END IF;
1273 --
1274 -- CANCEL mode
1275 --
1276 IF (funcmode = 'CANCEL') THEN
1277 --
1278 resultout := 'COMPLETE:';
1279 RETURN;
1280 --
1281 END IF;
1282 --
1283 -- TIMEOUT mode
1284 --
1285 IF (funcmode = 'TIMEOUT') THEN
1286 --
1287 resultout := 'COMPLETE:';
1288 RETURN;
1289 --
1290 END IF;
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294 IF okl_check_req_csr%ISOPEN THEN
1295 CLOSE okl_check_req_csr;
1296 END IF;
1297
1298 END validate_title_ret;
1299
1300
1301 -- Start of comments
1302 --
1303 -- Procedure Name : validate_shipping_instr
1304 -- Description : validate shipping instruction request from WF
1305 -- Business Rules :
1306 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1307 -- Version : 1.0
1308 -- 18-Dec-06 rkuttiya modified for XMLP Project
1309 -- End of comments
1310 PROCEDURE validate_shipping_instr(
1311 itemtype IN VARCHAR2,
1312 itemkey IN VARCHAR2,
1313 actid IN NUMBER,
1314 funcmode IN VARCHAR2,
1315 resultout OUT NOCOPY VARCHAR2 )IS
1316
1317 l_art_id NUMBER;
1318
1319 -- Check that the asset return refers to a Repossession Request
1320 CURSOR okl_check_req_csr(c_art_id NUMBER)
1321 IS
1322 SELECT ra.last_updated_by, cp.contact_party_id pac_id, contact_party_name
1323 FROM okl_asset_returns_b ar
1324 ,okl_relocate_assets_b ra
1325 ,okl_am_contact_points_uv cp
1326 ,okl_am_contacts_uv c
1327 WHERE ar.id = c_art_id
1328 AND ar.id = ra.art_id
1329 AND pac_id = cp.contact_contact_point_id
1330 AND cp.contact_party_id = c.contact_party_id
1331 AND ist_id IS NOT NULL;
1332
1333 l_csr_rec okl_check_req_csr%rowtype;
1334
1335 l_user_name WF_USERS.name%type;
1336 l_name WF_USERS.description%type;
1337
1338 l_recipient_name varchar2(100);
1339 l_recipient_id number;
1340 l_party_object_tbl okl_am_parties_pvt.party_object_tbl_type;
1341
1342 --12/18/06 rkuttiya added for XMLP Project
1343
1344 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1345 x_msg_count NUMBER;
1346 x_msg_data VARCHAR2(2000);
1347 l_api_version NUMBER := 1;
1348 l_init_msg_list VARCHAR2(1) := 'T';
1349
1350 ERR EXCEPTION;
1351 l_batch_id NUMBER;
1352 l_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1353 lx_xmp_rec OKL_XMLP_PARAMS_PVT.xmp_rec_type;
1354 --get the recipient email address
1355 CURSOR c_recipient(p_recipient_id IN NUMBER)
1356 IS
1357 SELECT hzp.email_address email
1358 FROM hz_parties hzp
1359 WHERE hzp.party_id = p_recipient_id;
1360
1361 -- get the sender email address
1362 CURSOR c_agent_csr (c_agent_id NUMBER) IS
1363 SELECT nvl(ppf.email_address , fu.email_address) email
1364 FROM fnd_user fu,
1365 per_people_f ppf
1366 WHERE fu.employee_id = ppf.person_id (+)
1367 AND fu.user_id = c_agent_id;
1368 l_from_email VARCHAR2(100);
1369 l_to_email VARCHAR2(100);
1370
1371 BEGIN
1372
1373 IF (funcmode = 'RUN') THEN
1374
1375 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1376 itemkey => itemkey,
1377 aname => 'TRANSACTION_ID');
1378
1379 OPEN okl_check_req_csr(l_art_id);
1380 FETCH okl_check_req_csr INTO l_csr_rec;
1381 CLOSE okl_check_req_csr;
1382
1383 okl_am_wf.get_notification_agent(
1384 itemtype => itemtype
1385 , itemkey => itemkey
1386 , actid => actid
1387 , funcmode => funcmode
1388 , p_user_id => l_csr_rec.last_updated_by
1389 , x_name => l_user_name
1390 , x_description => l_name);
1391
1392 -- Find party details based on the PAC_ID
1393 --okl_am_parties_pvt.get_party_details (
1394 -- p_id_code => 'PC',
1395 -- p_id_value => l_csr_rec.pac_id,
1396 -- x_party_object_tbl => l_party_object_tbl,
1397 -- x_return_status => l_return_status);
1398
1399 -- Check that a contact was returned for the TRANSACTION_ID given.
1400 IF l_csr_rec.pac_id IS NOT NULL THEN
1401 resultout := 'COMPLETE:VALID';
1402
1403 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
1404 itemkey => itemkey,
1405 aname => 'CREATED_BY',
1406 avalue => l_csr_rec.last_updated_by);
1407
1408 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1409 itemkey => itemkey,
1410 aname => 'REQUESTER',
1411 avalue => l_user_name);
1412
1413 -- Populate Item Attributes for Fulfillment
1414 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1415 itemkey => itemkey,
1416 aname => 'PROCESS_CODE',
1417 avalue => 'AMNSI');
1418 --12/18/06 modified recipient type to 'LESSEE' for XML Publisher, since this was originally Party Contact
1419 /*
1420 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1421 itemkey => itemkey,
1422 aname => 'RECIPIENT_TYPE',
1423 avalue => 'PC'); */
1424 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1425 itemkey => itemkey,
1426 aname => 'RECIPIENT_TYPE',
1427 avalue => 'LESSEE');
1428
1429 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1430 itemkey => itemkey,
1431 aname => 'RECIPIENT_ID',
1432 avalue => l_csr_rec.pac_id);
1433
1434
1435 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1436 itemkey => itemkey,
1437 aname => 'RECIPIENT_DESCRIPTION',
1438 avalue => l_csr_rec.contact_party_name);
1439 --12/18/06 rkuttiya modified for XMLP Project
1440 --set the From Address and TO Address
1441 OPEN c_recipient(l_csr_rec.pac_id);
1442 FETCH c_recipient INTO l_to_email;
1443 CLOSE c_recipient;
1444
1445 OPEN c_agent_csr(l_csr_rec.last_updated_by);
1446 FETCH c_agent_csr into l_from_email;
1447 CLOSE c_agent_csr;
1448
1449 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1450 itemkey => itemkey,
1451 aname => 'EMAIL_ADDRESS',
1452 avalue => l_to_email);
1453
1454
1455 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1456 itemkey => itemkey,
1457 aname => 'FROM_ADDRESS',
1458 avalue => l_from_email);
1459
1460 --18-Dec-06 rkuttiya added for XMLP Project
1461 --code for inserting bind parameters into table
1462
1463 l_xmp_rec.param_name := 'P_ART_ID';
1464 l_xmp_rec.param_value := l_art_id;
1465 l_xmp_rec.param_type_code := 'NUMBER';
1466
1467 OKL_XMLP_PARAMS_PVT.create_xmlp_params_rec(
1468 p_api_version => l_api_version
1469 ,p_init_msg_list => l_init_msg_list
1470 ,x_return_status => l_return_status
1471 ,x_msg_count => x_msg_count
1472 ,x_msg_data => x_msg_data
1473 ,p_xmp_rec => l_xmp_rec
1474 ,x_xmp_rec => lx_xmp_rec
1475 );
1476 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1477 RAISE ERR;
1478 END IF;
1479
1480
1481 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1482 l_batch_id := lx_xmp_rec.batch_id;
1483 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1484 itemkey => itemkey,
1485 aname => 'BATCH_ID',
1486 avalue => l_batch_id );
1487 resultout := 'COMPLETE:VALID'; -- rkuttiya changed
1488 ELSE
1489 resultout := 'COMPLETE:ERROR';
1490 END IF;
1491 ELSE
1492 resultout := 'COMPLETE:INVALID';
1493 END IF;
1494
1495 RETURN ;
1496
1497 END IF;
1498 --
1499 -- CANCEL mode
1500 --
1501 IF (funcmode = 'CANCEL') THEN
1502 --
1503 resultout := 'COMPLETE:';
1504 RETURN;
1505 --
1506 END IF;
1507 --
1508 -- TIMEOUT mode
1509 --
1510 IF (funcmode = 'TIMEOUT') THEN
1511 --
1512 resultout := 'COMPLETE:';
1513 RETURN;
1514 --
1515 END IF;
1516
1517 EXCEPTION
1518 WHEN OTHERS THEN
1519 IF okl_check_req_csr%ISOPEN THEN
1520 CLOSE okl_check_req_csr;
1521 END IF;
1522
1523 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'validate_shipping_instr', itemtype, itemkey, actid, funcmode);
1524 RAISE;
1525
1526 END validate_shipping_instr;
1527
1528 -- Start of comments
1529 --
1530 -- Procedure Name : validate_asset_repair
1531 -- Description : validate asset repair approval request from WF
1532 -- Business Rules :
1533 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1534 -- Version : 1.0
1535 --
1536 -- End of comments
1537 PROCEDURE validate_asset_repair( itemtype IN VARCHAR2,
1538 itemkey IN VARCHAR2,
1539 actid IN NUMBER,
1540 funcmode IN VARCHAR2,
1541 resultout OUT NOCOPY VARCHAR2 )IS
1542
1543 l_id NUMBER;
1544 l_knt NUMBER;
1545
1546 -- cursor to check request is valid
1547 CURSOR okl_check_req_csr(c_id NUMBER)
1548 IS
1549 SELECT count(*)
1550 FROM OKL_ASSET_CNDTNS ACD, OKL_ASSET_CNDTN_LNS_V ACN
1551 WHERE ACD.ID = c_id
1552 AND ACD.ID = ACN.ACD_ID
1553 AND upper(nvl(ACN.APPROVED_YN, 'N')) <> 'Y'
1554 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL';
1555
1556 BEGIN
1557
1558 IF (funcmode = 'RUN') THEN
1559
1560 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1561 itemkey => itemkey,
1562 aname => 'TRANSACTION_ID');
1563
1564 OPEN okl_check_req_csr(l_id);
1565 FETCH okl_check_req_csr INTO l_knt;
1566 CLOSE okl_check_req_csr;
1567
1568 IF l_knt = 0 THEN
1569 resultout := 'COMPLETE:INVALID';
1570 ELSE
1571 resultout := 'COMPLETE:VALID';
1572 END IF;
1573
1574 RETURN ;
1575
1576 END IF;
1577 --
1578 -- CANCEL mode
1579 --
1580 IF (funcmode = 'CANCEL') THEN
1581 --
1582 resultout := 'COMPLETE:';
1583 RETURN;
1584 --
1585 END IF;
1586 --
1587 -- TIMEOUT mode
1588 --
1589 IF (funcmode = 'TIMEOUT') THEN
1590 --
1591 resultout := 'COMPLETE:';
1592 RETURN;
1593 --
1594 END IF;
1595
1596 EXCEPTION
1597 WHEN OTHERS THEN
1598 IF okl_check_req_csr%ISOPEN THEN
1599 CLOSE okl_check_req_csr;
1600 END IF;
1601
1602 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'validate_asset_repair', itemtype, itemkey, actid, funcmode);
1603 RAISE;
1604
1605 END validate_asset_repair;
1606
1607 -- Start of comments
1608 --
1609 -- Procedure Name : set_approved_yn
1610 -- Description : set asset repair approval to Y/N from WF,
1611 -- calls TAPI
1612 -- Business Rules :
1613 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1614 -- Version : 1.0
1615 --
1616 -- End of comments
1617 PROCEDURE set_approved_yn( itemtype IN VARCHAR2,
1618 itemkey IN VARCHAR2,
1619 actid IN NUMBER,
1620 funcmode IN VARCHAR2,
1621 resultout OUT NOCOPY VARCHAR2 )IS
1622
1623 l_id NUMBER;
1624 l_knt NUMBER;
1625 l_approved_yn VARCHAR2(10);
1626
1627 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1628 x_msg_count NUMBER;
1629 x_msg_data VARCHAR2(2000);
1630 l_acnv_rec OKL_ACN_PVT.ACNV_REC_TYPE;
1631 x_acnv_rec OKL_ACN_PVT.ACNV_REC_TYPE;
1632 l_api_version NUMBER := 1;
1633 l_init_msg_list VARCHAR2(1) := 'T';
1634
1635 CURSOR c_rec_csr(C_ID NUMBER) IS
1636 SELECT ACN.ID id
1637 FROM OKL_ASSET_CNDTNS ACD,
1638 OKL_ASSET_CNDTN_LNS_V ACN
1639 WHERE ACD.ID = C_ID
1640 AND ACD.ID = ACN.ACD_ID
1641 AND nvl(UPPER(ACN.APPROVED_YN), 'N') <> 'Y'
1642 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL';
1643
1644 ERR EXCEPTION;
1645 BEGIN
1646
1647 IF (funcmode = 'RUN') THEN
1648
1649 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1650 itemkey => itemkey,
1651 aname => 'TRANSACTION_ID');
1652
1653 l_approved_yn := wf_engine.GetItemAttrText( itemtype => itemtype,
1654 itemkey => itemkey,
1655 aname => 'APPROVED_YN');
1656
1657
1658 l_acnv_rec.APPROVED_YN := l_approved_yn;
1659 l_acnv_rec.DATE_APPROVED := SYSDATE;
1660
1661 IF upper(l_approved_yn) = 'Y' THEN
1662 l_acnv_rec.ACS_CODE := 'APPROVED';
1663 ELSE
1664 l_acnv_rec.ACS_CODE := 'REJECTED';
1665 END IF;
1666
1667 FOR c_rec IN c_rec_csr(l_id) LOOP
1668
1669 l_acnv_rec.ID := c_rec.id;
1670
1671 okl_acn_pvt.update_row( p_api_version => l_api_version,
1672 p_init_msg_list => l_init_msg_list,
1673 x_return_status => l_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_data,
1676 p_acnv_rec => l_acnv_rec,
1677 x_acnv_rec => x_acnv_rec);
1678
1679 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1680 RAISE ERR;
1681 END IF;
1682 END LOOP;
1683
1684
1685 IF l_return_status = OKC_API.G_RET_STS_SUCCESS THEN
1686 resultout := 'COMPLETE:SUCCESS';
1687 ELSE
1688 resultout := 'COMPLETE:ERROR';
1689 END IF;
1690
1691 RETURN ;
1692
1693 END IF;
1694 --
1695 -- CANCEL mode
1696 --
1697 IF (funcmode = 'CANCEL') THEN
1698 --
1699 resultout := 'COMPLETE:';
1700 RETURN;
1701 --
1702 END IF;
1703 --
1704 -- TIMEOUT mode
1705 --
1706 IF (funcmode = 'TIMEOUT') THEN
1707 --
1708 resultout := 'COMPLETE:';
1709 RETURN;
1710 --
1711 END IF;
1712
1713 EXCEPTION
1714 WHEN ERR THEN
1715 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1716 RAISE;
1717
1718 WHEN OTHERS THEN
1719 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1720 RAISE;
1721
1722 END set_approved_yn;
1723
1724 -- Start of comments
1725 --
1726 -- Procedure Name : pop_asset_repair_att
1727 -- Description : populate asset repair attributes from WF
1728 -- Business Rules :
1729 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1730 -- Version : 1.0
1731 --
1732 -- End of comments
1733 PROCEDURE POP_ASSET_REPAIR_ATT( itemtype IN VARCHAR2,
1734 itemkey IN VARCHAR2,
1735 actid IN NUMBER,
1736 funcmode IN VARCHAR2,
1737 resultout OUT NOCOPY VARCHAR2 ) AS
1738
1739 l_id NUMBER;
1740 l_no_data_found BOOLEAN;
1741 l_user WF_USERS.NAME%TYPE;
1742 l_name WF_USERS.DESCRIPTION%TYPE;
1743 l_message VARCHAR2(30000);
1744
1745 -- cursor to populate notification attributes
1746 --Bug # 6174484 ssdeshpa Fixed Cursor for SQL Performance start
1747 CURSOR okl_asset_repair_csr(c_id NUMBER)
1748 IS
1749 /* SELECT ACN.LAST_UPDATED_BY LAST_UPDATED_BY,
1750 AD.ASSET_NUMBER ASSET_NUMBER,
1751 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1752 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1753 ACN.PART_NAME PART_NAME,
1754 ACN.RECOMMENDED_REPAIR DETAILS,
1755 FND1.MEANING CONDITION_TYPE,
1756 FND2.MEANING DAMAGE_TYPE
1757 FROM OKL_AM_ASSET_RETURNS_UV AD,
1758 OKL_ASSET_CNDTNS ACD,
1759 OKL_ASSET_CNDTN_LNS_V ACN,
1760 FND_LOOKUPS FND1,
1761 FND_LOOKUPS FND2
1762 WHERE ACD.KLE_ID = AD.KLE_ID
1763 AND ACD.ID = C_ID
1764 AND ACD.ID = ACN.ACD_ID
1765 AND upper(nvl(ACN.APPROVED_YN, 'N')) <> 'Y'
1766 AND ACN.ACS_CODE = 'WAITING_FOR_APPROVAL'
1767 AND FND1.LOOKUP_TYPE = 'OKL_ASSET_CONDITION'
1768 AND FND1.LOOKUP_CODE = ACN.CDN_CODE
1769 AND FND2.LOOKUP_TYPE = 'OKL_DAMAGE_TYPE'
1770 AND FND2.LOOKUP_CODE = ACN.DTY_CODE;*/
1771
1772 SELECT ACNB.LAST_UPDATED_BY LAST_UPDATED_BY,
1773 KLE.NAME ASSET_NUMBER,
1774 KLE.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1775 OKC.CONTRACT_NUMBER CONTRACT_NUMBER,
1776 ACNT.PART_NAME PART_NAME,
1777 ACNT.RECOMMENDED_REPAIR DETAILS,
1778 FND1.MEANING CONDITION_TYPE,
1779 FND2.MEANING DAMAGE_TYPE
1780 FROM OKL_ASSET_RETURNS_B OAR,
1781 OKC_K_HEADERS_ALL_B OKC,
1782 OKC_K_LINES_V KLE,
1783 OKL_ASSET_CNDTNS_ALL ACD,
1784 OKL_AST_CNDTN_LNS_ALL_B ACNB,
1785 OKL_ASSET_CNDTN_LNS_TL ACNT,
1786 FND_LOOKUPS FND1,
1787 FND_LOOKUPS FND2
1788 WHERE OKC.ID = KLE.CHR_ID
1789 AND OAR.KLE_ID = KLE.ID
1790 AND ACD.KLE_ID = OAR.KLE_ID
1791 AND ACD.ID = c_id
1792 AND ACNB.ID = ACNT.ID
1793 AND ACNT.LANGUAGE = USERENV('LANG')
1794 AND ACD.ID = ACNB.ACD_ID
1795 AND UPPER(NVL(ACNB.APPROVED_YN, 'N')) <> 'Y'
1796 AND ACNB.ACS_CODE = 'WAITING_FOR_APPROVAL'
1797 AND FND1.LOOKUP_TYPE = 'OKL_ASSET_CONDITION'
1798 AND FND1.LOOKUP_CODE = ACNB.CDN_CODE
1799 AND FND2.LOOKUP_TYPE = 'OKL_DAMAGE_TYPE'
1800 AND FND2.LOOKUP_CODE = ACNB.DTY_CODE;
1801
1802 --Bug # 6174484 ssdeshpa Fixed Cursor for SQL Performance start
1803
1804 l_header_done BOOLEAN := FALSE;
1805 l_updated_by NUMBER;
1806
1807 BEGIN
1808
1809 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1810 itemkey => itemkey,
1811 aname => 'TRANSACTION_ID');
1812
1813 --build message (temp)
1814
1815 FOR l_asset_repair_rec in okl_asset_repair_csr(l_id) LOOP
1816
1817 IF NOT l_header_done THEN
1818 l_message := '<p>The repairs listed below are requested for the Asset '||l_asset_repair_rec.asset_number||'<br>'||
1819 l_asset_repair_rec.asset_description||' from Contract Number '||l_asset_repair_rec.contract_number||'</p>'||
1820 '<p>The repairs will be completed following your approval.</p>'||
1821 '<table width="50%" border="1">'||
1822 '<tr>'||
1823 '<td><b>Part<b/></td>'||
1824 '<td><b>Condition Type<b/></td>'||
1825 '<td><b>Damage Type<b/></td>'||
1826 '<td><b>Details<b/></td>'||
1827 '</tr>';
1828 l_header_done := TRUE;
1829 l_updated_by := l_asset_repair_rec.last_updated_by;
1830 END IF;
1831
1832 l_message := l_message||'<tr>'||
1833 '<td>'||l_asset_repair_rec.part_name||'</td>'||
1834 '<td>'||l_asset_repair_rec.condition_type||'</td>'||
1835 '<td>'||l_asset_repair_rec.damage_type||'</td>'||
1836 '<td>'||l_asset_repair_rec.details||'</td>'||
1837 '</tr>';
1838
1839 END LOOP;
1840
1841 IF l_header_done THEN
1842 l_message := l_message||'</table>';
1843 END IF;
1844
1845 okl_am_wf.get_notification_agent(
1846 itemtype => itemtype
1847 , itemkey => itemkey
1848 , actid => actid
1849 , funcmode => funcmode
1850 , p_user_id => l_updated_by
1851 , x_name => l_user
1852 , x_description => l_name);
1853
1854 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1855 itemkey => itemkey,
1856 aname => 'REQUESTER',
1857 avalue => l_user);
1858
1859 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1860 itemkey => itemkey,
1861 aname => 'WF_ADMINISTRATOR',
1862 avalue => l_user);
1863
1864 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1865 itemkey => itemkey,
1866 aname => 'TRX_TYPE_ID',
1867 avalue => 'OKLAMAAR');
1868
1869 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1870 itemkey => itemkey,
1871 aname => 'MESSAGE_DESCRIPTION',
1872 avalue => l_message);
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 IF okl_asset_repair_csr%ISOPEN THEN
1876 CLOSE okl_asset_repair_csr;
1877 END IF;
1878
1879 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_ASSET_REPAIR_ATT', itemtype, itemkey, actid, funcmode);
1880 RAISE;
1881
1882 END POP_ASSET_REPAIR_ATT;
1883
1884 -- Start of comments
1885 --
1886 -- Procedure Name : populate_itd_atts
1887 -- Description : populate Internal Transport Department notification attributes
1888 -- Business Rules :
1889 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1890 -- Version : 1.0
1891 --
1892 -- End of comments
1893 PROCEDURE populate_itd_atts(itemtype IN VARCHAR2,
1894 itemkey IN VARCHAR2,
1895 actid IN NUMBER,
1896 funcmode IN VARCHAR2,
1897 resultout OUT NOCOPY VARCHAR2) AS
1898
1899 l_art_id NUMBER;
1900 l_no_data_found BOOLEAN;
1901 -- l_performer WF_USERS.NAME%TYPE;
1902 l_requester WF_USERS.NAME%TYPE;
1903 l_name WF_USERS.DISPLAY_NAME%TYPE;
1904 -- cursor to populate notification attributes
1905
1906 CURSOR okl_asset_return_csr(c_art_id NUMBER)
1907 IS
1908 /* SELECT OAR.LAST_UPDATED_BY LAST_UPDATED_BY,
1909 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1910 AD.NAME ASSET_NUMBER,
1911 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1912 AD.SERIAL_NUMBER SERIAL_NUMBER,
1913 AD.MODEL_NUMBER MODEL_NUMBER,
1914 OAR.COMMENTS COMMENTS
1915 FROM OKL_AM_ASSET_DETAILS_UV AD, OKL_ASSET_RETURNS_V OAR
1916 WHERE AD.ID = OAR.KLE_ID
1917 AND OAR.ID = c_art_id;
1918 */
1919 SELECT OAR.LAST_UPDATED_BY LAST_UPDATED_BY,
1920 AD.ITEM_DESCRIPTION ASSET_DESCRIPTION,
1921 AD.NAME ASSET_NUMBER,
1922 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
1923 AD.SERIAL_NUMBER SERIAL_NUMBER,
1924 AD.MODEL_NUMBER MODEL_NUMBER,
1925 OAR.COMMENTS COMMENTS,
1926 C.CONTACT_PARTY_NAME CONTACT_NAME,
1927 CP.CONTACT_DETAILS CONTACT_DETAILS
1928 FROM OKL_AM_ASSET_DETAILS_UV AD,
1929 OKL_ASSET_RETURNS_V OAR,
1930 OKL_RELOCATE_ASSETS_V ORA,
1931 OKL_AM_CONTACT_POINTS_UV CP,
1932 OKL_AM_CONTACTS_UV C
1933 WHERE AD.ID = OAR.KLE_ID
1934 AND OAR.ID = c_art_id
1935 AND OAR.ID = ORA.ART_ID
1936 AND ORA.PAC_ID = CP.CONTACT_CONTACT_POINT_ID
1937 AND CP.CONTACT_PARTY_ID = C.CONTACT_PARTY_ID;
1938
1939 l_asset_return okl_asset_return_csr%rowtype;
1940
1941 BEGIN
1942 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
1943 itemkey => itemkey,
1944 aname => 'TRANSACTION_ID');
1945
1946 OPEN okl_asset_return_csr(l_art_id);
1947 FETCH okl_asset_return_csr INTO l_asset_return;
1948 CLOSE okl_asset_return_csr;
1949
1950 -- Get the notification recipient from profile.
1951 /* l_performer := fnd_profile.value('OKL_TRANSPORTATION_NOTIFICATION');
1952
1953 -- get the requestor
1954 OKL_AM_WF.GET_NOTIFICATION_AGENT(
1955 itemtype => itemtype,
1956 itemkey => itemkey,
1957 actid => actid,
1958 funcmode => funcmode,
1959 p_user_id => l_asset_return.last_updated_by,
1960 x_name => l_requester,
1961 x_description => l_name);
1962
1963 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1964 itemkey => itemkey,
1965 aname => 'REQUESTER',
1966 avalue => l_requester);
1967
1968 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1969 itemkey => itemkey,
1970 aname => 'PERFORMING_AGENT',
1971 avalue => l_performer);
1972 */
1973 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1974 itemkey => itemkey,
1975 aname => 'CONTACT_NAME',
1976 avalue => l_asset_return.CONTACT_NAME);
1977
1978 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1979 itemkey => itemkey,
1980 aname => 'CONTACT_METHOD',
1981 avalue => l_asset_return.CONTACT_DETAILS);
1982
1983 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1984 itemkey => itemkey,
1985 aname => 'CONTRACT_NUMBER',
1986 avalue => l_asset_return.CONTRACT_NUMBER);
1987
1988 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1989 itemkey => itemkey,
1990 aname => 'ASSET_NUMBER',
1991 avalue => l_asset_return.ASSET_NUMBER);
1992
1993 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1994 itemkey => itemkey,
1995 aname => 'ASSET_DESCRIPTION',
1996 avalue => l_asset_return.asset_description);
1997
1998 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1999 itemkey => itemkey,
2000 aname => 'DETAILS',
2001 avalue => l_asset_return.comments);
2002
2003 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2004 itemkey => itemkey,
2005 aname => 'SERIAL_NUMBER',
2006 avalue => l_asset_return.serial_number);
2007
2008 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2009 itemkey => itemkey,
2010 aname => 'MODEL_NUMBER',
2011 avalue => l_asset_return.model_number);
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 IF okl_asset_return_csr%ISOPEN THEN
2016 CLOSE okl_asset_return_csr;
2017 END IF;
2018
2019 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'populate_itd_atts', itemtype, itemkey, actid, funcmode);
2020 RAISE;
2021 END populate_itd_atts;
2022
2023 PROCEDURE VALIDATE_CONT_PORT (itemtype IN VARCHAR2,
2024 itemkey IN VARCHAR2,
2025 actid IN NUMBER,
2026 funcmode IN VARCHAR2,
2027 resultout OUT NOCOPY VARCHAR2) IS
2028
2029 CURSOR okl_pfc_csr(c_id NUMBER)
2030 IS
2031 SELECT count(1)
2032 FROM OKL_PRTFL_CNTRCTS_B PFC
2033 WHERE PFC.KHR_ID = c_id;
2034
2035 l_id NUMBER;
2036 l_count NUMBER;
2037
2038 BEGIN
2039
2040 IF (funcmode = 'RUN') THEN
2041 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2042 itemkey => itemkey,
2043 aname => 'TRANSACTION_ID');
2044
2045
2046 OPEN okl_pfc_csr(l_id);
2047 FETCH okl_pfc_csr into l_count;
2048 CLOSE okl_pfc_csr;
2049
2050 IF l_count > 0 THEN
2051 resultout := 'COMPLETE:VALID';
2052 ELSE
2053 resultout := 'COMPLETE:INVALID';
2054 END IF;
2055
2056 RETURN ;
2057
2058 END IF;
2059
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 IF okl_pfc_csr%ISOPEN THEN
2063 CLOSE okl_pfc_csr;
2064 END IF;
2065
2066 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'VALIDATE_CONT_PORT', itemtype, itemkey, actid, funcmode);
2067 RAISE;
2068 END VALIDATE_CONT_PORT;
2069
2070 -- Start of comments
2071 --
2072 -- Procedure Name : POP_CONT_PORT_ATT
2073 -- Description : populate portfolio approval message attributes
2074 -- Business Rules :
2075 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2076 -- Version : 1.0
2077 --
2078 -- End of comments
2079 PROCEDURE POP_CONT_PORT_ATT( itemtype IN VARCHAR2,
2080 itemkey IN VARCHAR2,
2081 actid IN NUMBER,
2082 funcmode IN VARCHAR2,
2083 resultout OUT NOCOPY VARCHAR2) IS
2084
2085 l_id NUMBER;
2086 l_no_data_found BOOLEAN;
2087 l_user WF_USERS.NAME%TYPE;
2088
2089 l_message VARCHAR2(30000);
2090 -- cursor to populate notification attributes
2091
2092 CURSOR okl_pfc_csr(c_id NUMBER)
2093 IS
2094 SELECT TEAM_NAME ASSIGNMENT_GROUP,
2095 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
2096 FND.MEANING STRATEGY,
2097 PFCL.BUDGET_AMOUNT BUDGET,
2098 PFCL.DATE_STRATEGY_EXECUTION_DUE EXECUTION_DATE,
2099 PFC.LAST_UPDATED_BY LAST_UPDATED_BY
2100 FROM OKL_PRTFL_CNTRCTS_B PFC,
2101 OKL_PRTFL_LINES_V PFCL,
2102 OKC_K_HEADERS_V AD,
2103 FND_LOOKUPS FND,
2104 JTF_RS_TEAMS_VL T
2105 WHERE PFC.KHR_ID = c_id
2106 AND PFC.ID = PFCL.PFC_ID
2107 AND AD.ID = PFC.KHR_ID
2108 AND ASSET_TRACK_STRATEGY_CODE = FND.LOOKUP_CODE
2109 AND FND.LOOKUP_TYPE = 'OKL_ASSET_TRACK_STRATEGIES'
2110 AND TMB_ID = T.TEAM_ID;
2111
2112 l_header_done BOOLEAN := FALSE;
2113 l_user_name WF_USERS.name%type;
2114 l_name WF_USERS.description%type;
2115
2116 BEGIN
2117
2118 IF (funcmode = 'RUN') THEN
2119 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2120 itemkey => itemkey,
2121 aname => 'TRANSACTION_ID');
2122
2123 --build message
2124
2125 FOR l_pfc_rec in okl_pfc_csr(to_number(l_id)) LOOP
2126
2127 IF NOT l_header_done THEN
2128 l_message := '<p>'||l_pfc_rec.assignment_group||'</p>'||
2129 '<p>Contract Number:'||l_pfc_rec.contract_number||'</p>'||
2130 '<p>Please approve the following profile.</p>'||
2131 '<table width="50%" border="1">'||
2132 '<tr>'||
2133 '<td>Strategy</td>'||
2134 '<td>Budget</td>'||
2135 '<td>Execution Date</td>'||
2136 '</tr>';
2137 l_header_done := TRUE;
2138
2139 okl_am_wf.get_notification_agent(
2140 itemtype => itemtype
2141 , itemkey => itemkey
2142 , actid => actid
2143 , funcmode => funcmode
2144 , p_user_id => l_pfc_rec.last_updated_by
2145 , x_name => l_user_name
2146 , x_description => l_name);
2147 END IF;
2148
2149 l_message := l_message||'<tr>'||
2150 '<td>'||l_pfc_rec.strategy||'</td>'||
2151 '<td>'||l_pfc_rec.budget||'</td>'||
2152 '<td>'||l_pfc_rec.execution_date||'</td>'||
2153 '</tr>';
2154 END LOOP;
2155
2156 IF l_header_done THEN
2157 l_message := l_message||'</table>';
2158 END IF;
2159
2160 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2161 itemkey => itemkey,
2162 aname => 'TRX_TYPE_ID',
2163 avalue => 'OKLAMATK');
2164
2165 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2166 itemkey => itemkey,
2167 aname => 'MESSAGE_DESCRIPTION',
2168 avalue => l_message);
2169
2170 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2171 itemkey => itemkey,
2172 aname => 'REQUESTER',
2173 avalue => l_user_name);
2174
2175 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2176 itemkey => itemkey,
2177 aname => 'WF_ADMINISTRATOR',
2178 avalue => l_user);
2179 resultout := 'COMPLETE:';
2180 RETURN ;
2181 END IF;
2182
2183 --
2184 -- CANCEL mode
2185 --
2186 IF (funcmode = 'CANCEL') THEN
2187 --
2188 resultout := 'COMPLETE:';
2189 RETURN;
2190 --
2191 END IF;
2192
2193 --
2194 -- TIMEOUT mode
2195 --
2196 IF (funcmode = 'TIMEOUT') THEN
2197 --
2198 resultout := 'COMPLETE:';
2199 RETURN;
2200 --
2201 END IF;
2202
2203 EXCEPTION
2204 WHEN OTHERS THEN
2205 IF okl_pfc_csr%ISOPEN THEN
2206 CLOSE okl_pfc_csr;
2207 END IF;
2208
2209 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_CONT_PORT_ATT', itemtype, itemkey, actid, funcmode);
2210 RAISE;
2211 END POP_CONT_PORT_ATT;
2212
2213 -- Start of comments
2214 --
2215 -- Procedure Name : SET_CP_APPROVED_YN
2216 -- Description : Update the APPROVED FLAG for contract portfolio
2217 -- Business Rules :
2218 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2219 -- Version : 1.0
2220 --
2221 -- End of comments
2222 PROCEDURE SET_CP_APPROVED_YN( itemtype IN VARCHAR2,
2223 itemkey IN VARCHAR2,
2224 actid IN NUMBER,
2225 funcmode IN VARCHAR2,
2226 resultout OUT NOCOPY VARCHAR2) IS
2227
2228 CURSOR okl_pfc_csr(c_id NUMBER)
2229 IS
2230 SELECT PFCL.ID ID
2231 FROM OKL_PRTFL_LINES_B PFCL,
2232 OKL_PRTFL_CNTRCTS_B PFC
2233 WHERE PFC.KHR_ID = c_id
2234 AND PFC.ID = PFCL.PFC_ID;
2235
2236 l_pfc_rec okl_pfc_csr%rowtype;
2237 l_id NUMBER;
2238 l_approved_yn VARCHAR2(10);
2239
2240 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2241 x_msg_count NUMBER;
2242 x_msg_data VARCHAR2(2000);
2243 l_pflv_rec OKL_PRTFL_LINES_PUB.PFLV_REC_TYPE;
2244 x_pflv_rec OKL_PRTFL_LINES_PUB.PFLV_REC_TYPE;
2245 l_api_version NUMBER := 1;
2246 l_init_msg_list VARCHAR2(1) := 'T';
2247
2248 API_ERROR EXCEPTION;
2249
2250 BEGIN
2251
2252 IF (funcmode = 'RUN') THEN
2253 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2254 itemkey => itemkey,
2255 aname => 'TRANSACTION_ID');
2256
2257 l_approved_yn := wf_engine.GetItemAttrText( itemtype => itemtype,
2258 itemkey => itemkey,
2259 aname => 'APPROVED_YN');
2260
2261 IF upper(l_approved_yn) = 'Y' THEN
2262 l_pflv_rec.TRX_STATUS_CODE := 'APPROVED';
2263 ELSE
2264 l_pflv_rec.TRX_STATUS_CODE := 'REJECTED';
2265 END IF;
2266
2267 FOR c_rec in okl_pfc_csr(l_id) LOOP
2268
2269 l_pflv_rec.ID := c_rec.id;
2270
2271 okl_prtfl_lines_pub.update_prtfl_lines(
2272 p_api_version => l_api_version,
2273 p_init_msg_list => l_init_msg_list,
2274 x_return_status => l_return_status,
2275 x_msg_count => x_msg_count,
2276 x_msg_data => x_msg_data,
2277 p_pflv_rec => l_pflv_rec,
2278 x_pflv_rec => x_pflv_rec);
2279
2280 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2281 RAISE API_ERROR;
2282 END IF;
2283
2284 END LOOP;
2285
2286 IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2287 RAISE API_ERROR;
2288 ELSE
2289 resultout := 'COMPLETE:SUCCESS';
2290 END IF;
2291
2292 RETURN ;
2293 END IF;
2294
2295 EXCEPTION
2296
2297 WHEN API_ERROR THEN
2298 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'SET_CP_APPROVED_YN', itemtype, itemkey, actid, funcmode);
2299 RAISE;
2300
2301 WHEN OTHERS THEN
2302 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'SET_CP_APPROVED_YN', itemtype, itemkey, actid, funcmode);
2303 RAISE;
2304
2305 END SET_CP_APPROVED_YN;
2306
2307 -- Start of comments
2308 --
2309 -- Procedure Name : POP_CPE_NOTIFY_ATT
2310 -- Description : populate assignment group notification attributes from WF
2311 -- Business Rules :
2312 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2313 -- Version : 1.0
2314 --
2315 -- End of comments
2316 PROCEDURE POP_CPE_NOTIFY_ATT( itemtype IN VARCHAR2,
2317 itemkey IN VARCHAR2,
2318 actid IN NUMBER,
2319 funcmode IN VARCHAR2,
2320 resultout OUT NOCOPY VARCHAR2) IS
2321
2322 l_id NUMBER;
2323 l_no_data_found BOOLEAN;
2324 l_user WF_USERS.NAME%TYPE;
2325
2326 l_message VARCHAR2(30000);
2327 -- cursor to populate notification attributes
2328 CURSOR okl_pfc_csr(c_id NUMBER)
2329 IS
2330 SELECT TMB_ID,
2331 AD.CONTRACT_NUMBER CONTRACT_NUMBER,
2332 FND.MEANING STRATEGY,
2333 PFCL.BUDGET_AMOUNT BUDGET,
2334 PFCL.DATE_STRATEGY_EXECUTION_DUE EXECUTION_DATE,
2335 PFC.LAST_UPDATED_BY LAST_UPDATED_BY
2336 FROM OKL_PRTFL_CNTRCTS_B PFC,
2337 OKL_PRTFL_LINES_V PFCL,
2338 OKC_K_HEADERS_V AD,
2339 FND_LOOKUPS FND
2340 WHERE PFC.KHR_ID = c_id
2341 AND PFC.ID = PFCL.PFC_ID
2342 AND AD.ID = PFC.KHR_ID
2343 AND ASSET_TRACK_STRATEGY_CODE = FND.LOOKUP_CODE
2344 AND FND.LOOKUP_TYPE = 'OKL_ASSET_TRACK_STRATEGIES';
2345
2346
2347 l_pfc_rec okl_pfc_csr%rowtype;
2348
2349 -- cursor to find valid notification user
2350 CURSOR wf_users_csr(c_team_id NUMBER)
2351 IS
2352 SELECT count(1)
2353 FROM jtf_rs_teams_b t,
2354 jtf_rs_role_relations_vl jtfr,
2355 jtf_rs_Resource_extns a,
2356 jtf_rs_Team_Members b,
2357 jtf_rs_Groups_b d,
2358 jtf_rs_resource_extns re,
2359 wf_users WU
2360 WHERE
2361 t.team_id = c_team_id
2362 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
2363 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
2364 AND jtfr.role_code = 'PORTFOLIO_GROUP'
2365 AND role_resource_type = 'RS_TEAM'
2366 AND jtfr.role_resource_id = t.team_id
2367 AND t.team_id = b.Team_Id
2368 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
2369 d.Group_Number)) = re.resource_number
2370 AND b.Team_Resource_Id = a.Resource_Id (+)
2371 AND b.Team_Resource_Id = d.Group_Id (+)
2372 AND re.user_name = wu.name
2373 AND re.source_id = wu.orig_system_id;
2374
2375 l_user_name WF_USERS.name%type;
2376 l_name WF_USERS.description%type;
2377
2378 BEGIN
2379
2380 IF (funcmode = 'RUN') THEN
2381 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2382 itemkey => itemkey,
2383 aname => 'TRANSACTION_ID');
2384
2385 OPEN okl_pfc_csr(l_id);
2386 FETCH okl_pfc_csr INTO l_pfc_rec;
2387 CLOSE okl_pfc_csr;
2388
2389 OPEN wf_users_csr(l_pfc_rec.TMB_ID);
2390 FETCH wf_users_csr INTO l_user;
2391 CLOSE wf_users_csr;
2392
2393 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2394 itemkey => itemkey,
2395 aname => 'TOTAL_RECIPIENTS',
2396 avalue => l_user);
2397
2398 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2399 itemkey => itemkey,
2400 aname => 'CURRENT_RECIPIENT',
2401 avalue => l_user+1);
2402
2403 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2404 itemkey => itemkey,
2405 aname => 'CONTRACT_NUMBER',
2406 avalue => l_pfc_rec.CONTRACT_NUMBER);
2407
2408 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2409 itemkey => itemkey,
2410 aname => 'STRATEGY',
2411 avalue => l_pfc_rec.STRATEGY);
2412
2413 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2414 itemkey => itemkey,
2415 aname => 'ASSIGNMENT_GROUP_ID',
2416 avalue => to_char(l_pfc_rec.TMB_ID));
2417
2418 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2419 itemkey => itemkey,
2420 aname => 'BUDGET',
2421 avalue => l_pfc_rec.BUDGET);
2422
2423 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2424 itemkey => itemkey,
2425 aname => 'EXECUTION_DATE',
2426 avalue => l_pfc_rec.EXECUTION_DATE);
2427
2428 okl_am_wf.get_notification_agent(itemtype => itemtype
2429 ,itemkey => itemkey
2430 ,actid => actid
2431 ,funcmode => funcmode
2432 ,p_user_id => l_pfc_rec.last_updated_by
2433 ,x_name => l_user_name
2434 ,x_description => l_name);
2435
2436 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2437 itemkey => itemkey,
2438 aname => 'REQUESTER',
2439 avalue => l_user_name);
2440
2441 resultout := 'COMPLETE:';
2442 RETURN ;
2443 END IF;
2444
2445 --
2446 -- CANCEL mode
2447 --
2448 IF (funcmode = 'CANCEL') THEN
2449 --
2450 resultout := 'COMPLETE:';
2451 RETURN;
2452 --
2453 END IF;
2454
2455 --
2456 -- TIMEOUT mode
2457 --
2458 IF (funcmode = 'TIMEOUT') THEN
2459 --
2460 resultout := 'COMPLETE:';
2461 RETURN;
2462 --
2463 END IF;
2464
2465 EXCEPTION
2466 WHEN OTHERS THEN
2467 IF okl_pfc_csr%ISOPEN THEN
2468 CLOSE okl_pfc_csr;
2469 END IF;
2470
2471 IF wf_users_csr%ISOPEN THEN
2472 CLOSE wf_users_csr;
2473 END IF;
2474
2475 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'POP_REMK_NOTIFY_ATT', itemtype, itemkey, actid, funcmode);
2476 RAISE;
2477 END POP_CPE_NOTIFY_ATT;
2478
2479
2480 -- Start of comments
2481 --
2482 -- Procedure Name : NOTIFY_ASS_GRP_USER
2483 -- Description : populate assignment group notification attributes from WF
2484 -- This procedure is called recursively until all team
2485 -- members have been notified.
2486 -- Business Rules :
2487 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2488 -- Version : 1.0
2489 --
2490 -- End of comments
2491 PROCEDURE NOTIFY_ASS_GRP_USER( itemtype IN VARCHAR2,
2492 itemkey IN VARCHAR2,
2493 actid IN NUMBER,
2494 funcmode IN VARCHAR2,
2495 resultout OUT NOCOPY VARCHAR2 ) AS
2496
2497 l_rmr_id NUMBER;
2498 l_total_rec NUMBER;
2499 l_current_rec NUMBER := 0;
2500 l_no_data_found BOOLEAN;
2501 l_user WF_USERS.NAME%TYPE;
2502 l_name WF_USERS.DISPLAY_NAME%TYPE;
2503 l_current_user WF_USERS.NAME%TYPE;
2504
2505 -- cursor to populate notification attributes
2506 CURSOR wf_users_csr(c_team_id NUMBER,
2507 c_current_user NUMBER,
2508 c_name VARCHAR)
2509 IS
2510 SELECT wu.name, wu.display_name
2511 FROM jtf_rs_teams_vl t,
2512 jtf_rs_role_relations_vl jtfr,
2513 jtf_rs_Resource_extns a,
2514 jtf_rs_Team_Members b,
2515 jtf_rs_Groups_b d,
2516 jtf_rs_resource_extns re,
2517 wf_users WU
2518 WHERE t.team_id = c_team_id
2519 AND nvl (t.start_date_active, sysdate - 1) <= sysdate
2520 AND nvl (t.end_date_active, sysdate + 1) >= sysdate
2521 AND jtfr.role_code = 'PORTFOLIO_GROUP'
2522 AND role_resource_type = 'RS_TEAM'
2523 AND jtfr.role_resource_id = t.team_id
2524 AND t.team_id = b.Team_Id
2525 AND (DECODE(b.Resource_Type,'INDIVIDUAL',a.Resource_Number,
2526 d.Group_Number)) = re.resource_number
2527 AND b.Team_Resource_Id = a.Resource_Id (+)
2528 AND b.Team_Resource_Id = d.Group_Id (+)
2529 AND re.source_id = wu.orig_system_id
2530 AND re.user_name = wu.name
2531 AND ROWNUM < c_current_user
2532 AND wu.name > nvl(c_name, '0')
2533 order by 1 asc;
2534
2535 BEGIN
2536
2537 IF (funcmode = 'RUN') THEN
2538
2539 l_rmr_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2540 itemkey => itemkey,
2541 aname => 'ASSIGNMENT_GROUP_ID');
2542
2543 l_total_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2544 itemkey => itemkey,
2545 aname => 'TOTAL_RECIPIENTS');
2546
2547 l_current_rec := wf_engine.GetItemAttrNumber( itemtype => itemtype,
2548 itemkey => itemkey,
2549 aname => 'CURRENT_RECIPIENT');
2550
2551 l_current_user := wf_engine.GetItemAttrText( itemtype => itemtype,
2552 itemkey => itemkey,
2553 aname => 'PERFORMING_AGENT');
2554
2555
2556 OPEN wf_users_csr(l_rmr_id, l_current_rec, l_current_user);
2557 FETCH wf_users_csr INTO l_user, l_name ;
2558 CLOSE wf_users_csr;
2559
2560 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2561 itemkey => itemkey,
2562 aname => 'PERFORMING_AGENT',
2563 avalue => l_user);
2564
2565 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2566 itemkey => itemkey,
2567 aname => 'RECIPIENT_NAME',
2568 avalue => l_name);
2569
2570 l_current_rec := l_current_rec-1;
2571
2572 wf_engine.SetItemAttrNumber ( itemtype=> itemtype,
2573 itemkey => itemkey,
2574 aname => 'CURRENT_RECIPIENT',
2575 avalue => l_current_rec);
2576
2577
2578 IF l_current_rec = 0 THEN
2579 resultout := 'COMPLETE:NOTIFY_COMPLETE';
2580 ELSE
2581 resultout := 'COMPLETE:NOTIFY_OUTSTANDING';
2582 END IF;
2583
2584 RETURN ;
2585
2586 END IF;
2587 --
2588 -- CANCEL mode
2589 --
2590 IF (funcmode = 'CANCEL') THEN
2591 --
2592 resultout := 'COMPLETE:';
2593 RETURN;
2594 --
2595 END IF;
2596 --
2597 -- TIMEOUT mode
2598 --
2599 IF (funcmode = 'TIMEOUT') THEN
2600 --
2601 resultout := 'COMPLETE:';
2602 RETURN;
2603 --
2604 END IF;
2605
2606 EXCEPTION
2607 WHEN OTHERS THEN
2608 IF wf_users_csr%ISOPEN THEN
2609 CLOSE wf_users_csr;
2610 END IF;
2611
2612 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'NOTIFY_ASS_GRP_USER', itemtype, itemkey, actid, funcmode);
2613 RAISE;
2614
2615 END NOTIFY_ASS_GRP_USER;
2616
2617 -- Start of comments
2618 --
2619 -- Procedure Name : check_itd_request
2620 -- Description : Validate Notify Internal Trans. Dept. request from WF
2621 -- Business Rules :
2622 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2623 -- Version : 1.0
2624 --
2625 -- End of comments
2626 PROCEDURE check_itd_request( itemtype IN VARCHAR2,
2627 itemkey IN VARCHAR2,
2628 actid IN NUMBER,
2629 funcmode IN VARCHAR2,
2630 resultout OUT NOCOPY VARCHAR2 )IS
2631
2632 l_art_id NUMBER;
2633 l_last_updated_by NUMBER;
2634 l_requester VARCHAR2(100);
2635 l_description VARCHAR2(100);
2636
2637 -- cursor to check request is valid
2638 CURSOR okl_check_req_csr(c_art_id NUMBER)
2639 IS
2640 SELECT last_updated_by
2641 FROM OKL_ASSET_RETURNS_V
2642 WHERE ID= c_art_id;
2643
2644 BEGIN
2645
2646 IF (funcmode = 'RUN') THEN
2647
2648 l_art_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2649 itemkey => itemkey,
2650 aname => 'TRANSACTION_ID');
2651
2652 OPEN okl_check_req_csr(l_art_id);
2653 FETCH okl_check_req_csr INTO l_last_updated_by;
2654 CLOSE okl_check_req_csr;
2655
2656 OKL_AM_WF.GET_NOTIFICATION_AGENT(
2657 itemtype => itemtype,
2658 itemkey => itemkey,
2659 actid => actid,
2660 funcmode => funcmode,
2661 p_user_id => l_last_updated_by,
2662 x_name => l_requester,
2663 x_description => l_description);
2664
2665
2666 wf_engine.SetItemAttrText( itemtype => itemtype,
2667 itemkey => itemkey,
2668 aname => 'REQUESTER',
2669 avalue => l_requester);
2670
2671 wf_engine.SetItemAttrText( itemtype => itemtype,
2672 itemkey => itemkey,
2673 aname => 'WF_ADMINISTRATOR',
2674 avalue => l_requester);
2675
2676 IF l_last_updated_by IS NULL THEN
2677 resultout := 'COMPLETE:INVALID_RETURN';
2678 ELSE
2679 resultout := 'COMPLETE:VALID_RETURN';
2680 END IF;
2681
2682 RETURN ;
2683
2684 END IF;
2685 --
2686 -- CANCEL mode
2687 --
2688 IF (funcmode = 'CANCEL') THEN
2689 --
2690 resultout := 'COMPLETE:';
2691 RETURN;
2692 --
2693 END IF;
2694 --
2695 -- TIMEOUT mode
2696 --
2697 IF (funcmode = 'TIMEOUT') THEN
2698 --
2699 resultout := 'COMPLETE:';
2700 RETURN;
2701 --
2702 END IF;
2703
2704 EXCEPTION
2705 WHEN OTHERS THEN
2706 IF okl_check_req_csr%ISOPEN THEN
2707 CLOSE okl_check_req_csr;
2708 END IF;
2709
2710 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_itd_request', itemtype, itemkey, actid, funcmode);
2711 RAISE;
2712
2713 END check_itd_request;
2714
2715 -- Start of comments
2716 --
2717 -- Procedure Name : check_profile_recipient
2718 -- Description : check if the profile value for OKL_MANUAL_TERMINATION_QUOTE_REP
2719 -- returns valid recipients.
2720 -- Business Rules :
2721 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
2722 -- Version : 1.0
2723 --
2724 -- End of comments
2725 PROCEDURE check_profile_recipient( itemtype IN VARCHAR2,
2726 itemkey IN VARCHAR2,
2727 actid IN NUMBER,
2728 funcmode IN VARCHAR2,
2729 resultout OUT NOCOPY VARCHAR2 ) AS
2730
2731
2732 l_id VARCHAR2(100);
2733 l_performer VARCHAR2(100);
2734 l_recipients NUMBER;
2735
2736 cursor c1_csr (p_value varchar) is
2737 select count(*)
2738 from WF_USER_ROLES WUR
2739 where WUR.ROLE_NAME = p_value;
2740
2741
2742 BEGIN
2743
2744 IF (funcmode = 'RUN') THEN
2745 l_id := wf_engine.GetItemAttrText( itemtype => itemtype,
2746 itemkey => itemkey,
2747 aname => 'TRANSACTION_ID');
2748
2749 -- Get the notification recipient from profile.
2750 l_performer := fnd_profile.value('OKL_TRANSPORTATION_NOTIFICATION');
2751
2752 wf_engine.SetItemAttrText( itemtype => itemtype,
2753 itemkey => itemkey,
2754 aname => 'PERFORMING_AGENT',
2755 avalue => l_performer);
2756
2757
2758 OPEN c1_csr (l_performer);
2759 FETCH c1_csr INTO l_recipients;
2760 CLOSE c1_csr;
2761
2762 IF l_recipients > 0 THEN
2763 resultout := 'COMPLETE:VALID_RETURN';
2764 ELSE
2765 resultout := 'COMPLETE:INVALID_RETURN';
2766 END IF;
2767
2768 RETURN ;
2769 END IF;
2770
2771 --
2772 -- CANCEL mode
2773 --
2774 IF (funcmode = 'CANCEL') THEN
2775 --
2776 resultout := 'COMPLETE:';
2777 RETURN;
2778 --
2779 END IF;
2780
2781 --
2782 -- TIMEOUT mode
2783 --
2784 IF (funcmode = 'TIMEOUT') THEN
2785 --
2786 resultout := 'COMPLETE:';
2787 RETURN;
2788 --
2789 END IF;
2790
2791 EXCEPTION
2792
2793 WHEN OTHERS THEN
2794
2795 IF c1_csr%ISOPEN THEN
2796 CLOSE c1_csr;
2797 END IF;
2798
2799 wf_core.context('OKL_AM_ASSET_RETURN_WF' , 'check_profile_recipient', itemtype, itemkey, actid, funcmode);
2800 RAISE;
2801
2802 END check_profile_recipient;
2803
2804 END OKL_AM_ASSET_RETURN_WF;