[Home] [Help]
PACKAGE BODY: APPS.PN_AM_WF
Source
4 g_module_name CONSTANT VARCHAR2 (2) := 'PN';
1 PACKAGE BODY PN_AM_WF AS
2 /* $Header: PNLAMWFB.pls 120.1.12020000.2 2013/03/26 22:19:45 smahapat ship $ */
3
5 g_debug VARCHAR2 (255)
6 := NVL (fnd_profile.VALUE ('PN_DEBUG_MODE'), 'N');
7
8 -- Start of comments
9 --
10 -- Procedure Name : raise_business_event
11 -- Description : Generic procedure for raising business events
12 -- Business Rules :
13 -- Parameters : p_transaction_id, p_event_name,p_lease_id,p_old_approval_status,p_new_approval_status,p_old_lease_status,p_new_lease_status
14 -- Version : 1.0
15 --Hstory :SREEDHAR MAYA created.
16 -- End of comments
17 PROCEDURE raise_business_event (
18 p_transaction_id IN NUMBER,
19 p_event_name IN VARCHAR2,
20 p_lease_id IN pn_leases_all.lease_id%TYPE,
21 p_old_lease_status IN pn_leases_all.lease_status%TYPE,
22 p_new_lease_status IN pn_leases_all.lease_status%TYPE
23 )
24 IS
25 l_parameter_list wf_parameter_list_t;
26 l_event_key VARCHAR2 (240);
27 l_seq NUMBER;
28
29 -- Cursor for event key
30 CURSOR pn_key_cur
31 IS
32 SELECT pn_wf_item_s.NEXTVAL
33 FROM DUAL;
34 BEGIN
35 OPEN pn_key_cur;
36
37 FETCH pn_key_cur
38 INTO l_seq;
39
40 CLOSE pn_key_cur;
41
42 wf_debug ('Value of l_seq' || l_seq);
43 --defining event key value
44 l_event_key := p_event_name || l_seq;
45 --Adding all the input parameters to the varray list for wf_event.raise parameter
46 wf_event.addparametertolist ('TRANSACTION_ID',
47 p_transaction_id,
48 l_parameter_list
49 );
50 wf_event.addparametertolist ('LEASE_ID', p_lease_id, l_parameter_list);
51 wf_event.addparametertolist ('OLD_LEASE_STATUS',
52 p_old_lease_status,
53 l_parameter_list
54 );
55 wf_event.addparametertolist ('NEW_LEASE_STATUS',
56 p_new_lease_status,
57 l_parameter_list
58 );
59
60 -- MOAC checking
61 IF p_event_name IN ('oracle.apps.pn.lease.leasestatus')
62 THEN
63 wf_event.addparametertolist ('ORG_ID',
64 mo_global.get_current_org_id,
65 l_parameter_list
66 );
67 END IF;
68
69 -- Raise Event
70 wf_event.RAISE (p_event_name => p_event_name,
71 p_event_key => l_event_key,
72 p_parameters => l_parameter_list
73 );
74 -- Clear parameter list
75 l_parameter_list.DELETE;
76 EXCEPTION
77 WHEN OTHERS
78 THEN
79 fnd_message.set_token ('PN_AM_WF.RAISE_BUSINESS_EVENT ERROR',
80 SQLERRM
81 );
82 fnd_msg_pub.ADD;
83
84 IF pn_key_cur%ISOPEN
85 THEN
86 CLOSE pn_key_cur;
87 END IF;
88 END raise_business_event;
89
90 -- Start of comments
91 --
92 -- Procedure Name : get_approver
93 -- Description : Called from the Generic Approvals WF and is recursively executed
94 -- until all approvers have been located or until an approvwer
95 -- rejects a request.
96 -- Business Rules :
97 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
98 -- Version : 1.0
99
100 -- End of comments
101 PROCEDURE get_approver (
102 itemtype IN VARCHAR2,
103 itemkey IN VARCHAR2,
104 actid IN NUMBER,
105 funcmode IN VARCHAR2,
106 resultout OUT NOCOPY VARCHAR2
107 )
108 IS
109 l_trx_type VARCHAR2 (240);
110 l_app_id NUMBER;
111 l_approver_rec ame_util.approverrecord;
112 l_approver wf_users.NAME%TYPE;
113 l_name wf_users.description%TYPE;
114 l_transaction_id VARCHAR2 (100);
115 l_result VARCHAR2 (30);
116 l_user_id NUMBER;
117 BEGIN
118 IF (funcmode = 'RUN')
119 THEN
120 -- Get OAM parameter values from Approvals WF
121 l_trx_type :=
122 wf_engine.getitemattrtext (itemtype => itemtype,
123 itemkey => itemkey,
124 aname => 'TRX_TYPE_ID'
125 );
126 l_app_id :=
127 wf_engine.getitemattrtext (itemtype => itemtype,
128 itemkey => itemkey,
129 aname => 'APPLICATION_ID'
130 );
131 l_transaction_id :=
132 wf_engine.getitemattrtext (itemtype => itemtype,
133 itemkey => itemkey,
134 aname => 'TRANSACTION_ID'
135 );
136 -- Call OAM api to get approval details
137 ame_api.getnextapprover (applicationidin => l_app_id,
138 transactionidin => l_transaction_id,
139 transactiontypein => l_trx_type,
140 nextapproverout => l_approver_rec
141 );
142
143 IF l_approver_rec.person_id IS NOT NULL
144 THEN -- populate attributes
145 l_user_id := ame_util.personidtouserid (l_approver_rec.person_id);
146 wf_debug ('User Id of approver' || l_user_id);
147 get_notification_agent (itemtype => itemtype,
148 itemkey => itemkey,
149 actid => actid,
150 funcmode => funcmode,
151 p_user_id => l_user_id,
152 x_name => l_approver,
153 x_description => l_name
154 );
155 wf_engine.setitemattrtext (itemtype => itemtype,
156 itemkey => itemkey,
157 aname => 'APPROVER',
158 avalue => l_approver
159 );
160 wf_engine.setitemattrtext (itemtype => itemtype,
161 itemkey => itemkey,
162 aname => 'FIRST_NAME',
163 avalue => l_approver_rec.first_name
164 );
165 wf_engine.setitemattrtext (itemtype => itemtype,
166 itemkey => itemkey,
167 aname => 'LAST_NAME',
168 avalue => l_approver_rec.last_name
169 );
170 wf_engine.setitemattrnumber (itemtype => itemtype,
171 itemkey => itemkey,
172 aname => 'USER_ID',
173 avalue => l_approver_rec.user_id
174 );
175 wf_engine.setitemattrnumber (itemtype => itemtype,
176 itemkey => itemkey,
177 aname => 'PERSON_ID',
178 avalue => l_approver_rec.person_id
179 );
180 wf_engine.setitemattrtext (itemtype => itemtype,
181 itemkey => itemkey,
182 aname => 'API_INSERTION',
183 avalue => l_approver_rec.api_insertion
184 );
185 wf_engine.setitemattrtext (itemtype => itemtype,
186 itemkey => itemkey,
187 aname => 'AUTHORITY',
188 avalue => l_approver_rec.authority
189 );
190 resultout := 'COMPLETE:FOUND';
191 ELSE
192 l_result :=
193 wf_engine.getitemattrtext (itemtype => itemtype,
194 itemkey => itemkey,
195 aname => 'RESULT'
196 );
197 wf_debug ('RESULT' || l_result);
198
199 IF l_result IS NULL
200 THEN
201 -- There were no appovers, set RESULT to APPROVE
202 wf_engine.setitemattrtext (itemtype => itemtype,
203 itemkey => itemkey,
204 aname => 'RESULT',
205 avalue => 'APPROVED'
206 );
207 END IF;
208
209 resultout := 'COMPLETE:NOT_FOUND';
210 END IF;
211
212 RETURN;
213 END IF;
214
215 --
216 -- CANCEL mode
217 --
218 IF (funcmode = 'CANCEL')
219 THEN
220 --
221 resultout := 'COMPLETE:';
222 RETURN;
223 --
224 END IF;
225
226 --
227 -- TIMEOUT mode
228 --
229 IF (funcmode = 'TIMEOUT')
230 THEN
231 --
232 resultout := 'COMPLETE:';
233 RETURN;
234 --
235 END IF;
236 EXCEPTION
237 WHEN OTHERS
238 THEN
239 wf_core.CONTEXT ('PN_AM_WF',
240 'GET_APPROVER',
241 itemtype,
242 itemkey,
243 actid,
244 funcmode
245 );
246 RAISE;
247 END get_approver;
248
249 -- Start of comments
250 --
251 -- Procedure Name : update_status
252 -- Description : To update lease status based on whether the lease is approved/rejected
253 -- Business Rules :
254 -- Parameters : itemtype,itemkey,actid,funcmode,resultout
255 -- Version : 1.0
256 --History :Vaishali Telang Created
257 -- End of comments
258 PROCEDURE update_status (
259 itemtype IN VARCHAR2,
260 itemkey IN VARCHAR2,
261 actid IN NUMBER,
262 funcmode IN VARCHAR2,
263 resultout OUT NOCOPY VARCHAR2
264 )
265 IS
266 l_result VARCHAR2 (20);
267 l_transaction_id VARCHAR2 (20);
268 l_lease_status VARCHAR2 (20);
269 l_commencement_date DATE;
270 l_requestid NUMBER;
271 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_STATUS';
272 l_api_name_full CONSTANT VARCHAR2 (61)
273 := 'PN_AM_WF' || '.' || l_api_name;
274 l_org_id CONSTANT NUMBER := mo_global.get_current_org_id;
275 BEGIN
276 IF (funcmode = 'RUN')
277 THEN
278 l_result :=
279 wf_engine.getitemattrtext (itemtype => itemtype,
280 itemkey => itemkey,
281 aname => 'RESULT'
282 );
283 wf_debug ('RESULT' || l_result);
284 l_transaction_id :=
285 wf_engine.getitemattrtext (itemtype => itemtype,
286 itemkey => itemkey,
287 aname => 'TRANSACTION_ID'
288 );
289 wf_debug ('TRANSACTION_ID' || l_transaction_id);
290
291 SELECT plda.lease_commencement_date, pla.lease_status
292 INTO l_commencement_date, l_lease_status
293 FROM pn_leases_all pla, pn_lease_details_all plda
294 WHERE pla.lease_id = plda.lease_id
295 AND pla.lease_id = l_transaction_id
296 AND pla.org_id = plda.org_id;
297
298 IF l_result = 'APPROVED'
299 THEN
300 UPDATE pn_leases_all
301 SET status = 'F'
302 WHERE lease_id = l_transaction_id;
303
304 l_requestid :=
305 submit_program (l_api_name_full,
306 l_org_id,
307 l_transaction_id,
308 l_lease_status,
309 l_commencement_date
310 );
311
312 IF (l_requestid = 0)
313 THEN
314 fnd_message.set_name ('PN', 'PN_SUBSCHEDULES_ERR');
315 fnd_message.set_token ('API_NAME',
316 l_api_name_full
317 || '-SubmitSchedulesConcProgram'
318 );
319 fnd_msg_pub.ADD;
320 RAISE fnd_api.g_exc_error;
321 END IF;
322
323 wf_debug ('Value of request_id' || l_requestid);
324 ELSIF l_result = 'REJECT'
325 THEN
326 UPDATE pn_leases_all
327 SET status = 'D'
328 WHERE lease_id = l_transaction_id;
329 END IF;
330
331 -- ensure the the statuses have been cleared out for reuse.
332 UPDATE ame_temp_old_approver_lists
333 SET approval_status = NULL
334 WHERE transaction_id = l_transaction_id;
335
336 resultout := 'COMPLETE:';
337 RETURN;
338 END IF;
339
340 --
341 -- CANCEL mode
342 --
343 IF (funcmode = 'CANCEL')
344 THEN
345 --
346 resultout := 'COMPLETE:';
347 RETURN;
348 --
349 END IF;
350
351 --
352 -- TIMEOUT mode
353 --
354 IF (funcmode = 'TIMEOUT')
355 THEN
356 --
357 resultout := 'COMPLETE:';
358 RETURN;
359 --
360 END IF;
361
362 COMMIT;
363 EXCEPTION
364 WHEN OTHERS
365 THEN
366 wf_core.CONTEXT ('PN_AM_WF',
367 'UPDATE_STATUS',
368 itemtype,
369 itemkey,
370 actid,
371 funcmode
372 );
373 RAISE;
374 END update_status;
375
376 -- Start of comments
377 --
378 -- Procedure Name : call_lease_approval_wf
379 -- Description : call the workflow when the status is final/submitted
380 -- Business Rules :
381 -- Parameters : p_transaction_id, p_lease_id
382 -- Version : 1.0
383 --History :Vaishali Telang Created
384 -- End of comments
385 PROCEDURE call_lease_approval_wf (
386 p_transaction_id IN NUMBER,
387 p_lease_id IN NUMBER
388 )
389 IS
390 -- Selects the nextval from sequence, used later for defining event key
391 l_key VARCHAR2 (240);
392 l_seq NUMBER;
393 l_itemtype VARCHAR2 (30) := 'PNAPPRWF';
394 l_process VARCHAR2 (30) := 'PN_PROC';
395 l_requester VARCHAR2 (30);
396 l_lease_number VARCHAR2 (4000);
397 l_trx_type VARCHAR2 (1000);
401
398 l_app_id NUMBER;
399 l_commencement_date DATE;
400 l_termination_date DATE;
402 CURSOR pn_key_csr
403 IS
404 SELECT pn_wf_item_s.NEXTVAL
405 FROM DUAL;
406
407 CURSOR get_app_id_csr
408 IS
409 SELECT application_id
410 FROM fnd_application
411 WHERE application_short_name = 'PN';
412
413 l_org_id NUMBER;
414 BEGIN
415 SELECT transaction_type_id
416 INTO l_trx_type
417 FROM ame_calling_apps
418 WHERE application_name = 'PN approval workflow';
419
420 SELECT f.user_name, pla.lease_num, plda.lease_commencement_date,
421 plda.lease_termination_date
422 INTO l_requester, l_lease_number, l_commencement_date,
423 l_termination_date
424 FROM pn_leases_all pla, pn_lease_details_all plda, fnd_user f
425 WHERE pla.lease_id = plda.lease_id
426 AND pla.lease_id = p_lease_id
427 AND pla.last_updated_by = f.user_id
428 AND pla.org_id = plda.org_id;
429
430 OPEN pn_key_csr;
431
432 FETCH pn_key_csr
433 INTO l_seq;
434
435 CLOSE pn_key_csr;
436
437 OPEN get_app_id_csr;
438
439 FETCH get_app_id_csr
440 INTO l_app_id;
441
442 CLOSE get_app_id_csr;
443
444 l_key := l_itemtype || l_seq;
445 wf_engine.createprocess (itemtype => l_itemtype,
446 itemkey => l_key,
447 process => l_process
448 );
449 wf_engine.setitemattrtext (itemtype => l_itemtype,
450 itemkey => l_key,
451 aname => 'REQUESTOR',
452 avalue => l_requester
453 );
454 wf_engine.setitemattrtext (itemtype => l_itemtype,
455 itemkey => l_key,
456 aname => 'LEASE_NUMBER',
457 avalue => l_lease_number
458 );
459 wf_engine.setitemattrtext (itemtype => l_itemtype,
460 itemkey => l_key,
461 aname => 'TRANSACTION_ID',
462 avalue => p_transaction_id
463 );
464 wf_engine.setitemattrtext (itemtype => l_itemtype,
465 itemkey => l_key,
466 aname => 'APPLICATION_ID',
467 avalue => l_app_id
468 );
469 wf_engine.setitemattrtext (itemtype => l_itemtype,
470 itemkey => l_key,
471 aname => 'LEASE_COMMENCEMENT_DATE',
472 avalue => l_commencement_date
473 );
474 wf_engine.setitemattrtext (itemtype => l_itemtype,
475 itemkey => l_key,
476 aname => 'LEASE_TERMINATION_DATE',
477 avalue => l_termination_date
478 );
479 l_org_id :=
480 wf_engine.getitemattrtext (itemtype => l_itemtype,
481 itemkey => l_key,
482 aname => 'ORG_ID'
483 );
484 wf_debug ('Value of ORG_ID' || l_org_id);
485 wf_engine.setitemattrtext (itemtype => l_itemtype,
486 itemkey => l_key,
487 aname => 'ORG_ID',
488 avalue => l_org_id
489 );
490 wf_engine.setitemattrtext (itemtype => l_itemtype,
491 itemkey => l_key,
492 aname => 'TRX_TYPE_ID',
493 avalue => l_trx_type
494 );
495 wf_engine.startprocess (itemtype => l_itemtype, itemkey => l_key);
496 EXCEPTION
497 WHEN OTHERS
498 THEN
499 IF pn_key_csr%ISOPEN
500 THEN
501 CLOSE pn_key_csr;
502 END IF;
503 END call_lease_approval_wf;
504
505 -- Start of comments
506 --
507 -- Procedure Name : set_approval_status
508 -- Description : Called from the Generic Approvals WF to set the approval status
509 -- and is recursively executed
510 -- until all approvers have been located or until an approvwer
511 -- rejects a request.
512 -- Business Rules :
513 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
514 -- Version : 1.0
515 --
516 -- End of comments
517 PROCEDURE set_approval_status (
518 itemtype IN VARCHAR2,
519 itemkey IN VARCHAR2,
520 actid IN NUMBER,
521 funcmode IN VARCHAR2,
522 resultout OUT NOCOPY VARCHAR2
523 )
524 IS
525 l_app_id NUMBER;
526 l_trx_type VARCHAR2 (100);
527 l_approved_yn VARCHAR2 (30);
531 BEGIN
528 l_approver_rec ame_util.approverrecord;
529 l_user_id NUMBER;
530 l_transaction_id VARCHAR2 (100);
532 SAVEPOINT set_atts;
533
534 IF (funcmode = 'RUN')
535 THEN
536 -- Get current approval status
537 l_approved_yn :=
538 wf_engine.getitemattrtext (itemtype => itemtype,
539 itemkey => itemkey,
540 aname => 'RESULT'
541 );
542 wf_debug ('RESULT' || l_approved_yn);
543
544 IF l_approved_yn = 'APPROVED'
545 THEN
546 l_approver_rec.approval_status := 'APPROVE';
547 ELSE
548 l_approver_rec.approval_status := 'REJECT';
549 END IF;
550
551 -- All OAM attributes
552 l_transaction_id :=
553 wf_engine.getitemattrtext (itemtype => itemtype,
554 itemkey => itemkey,
555 aname => 'TRANSACTION_ID'
556 );
557 l_trx_type :=
558 wf_engine.getitemattrtext (itemtype => itemtype,
559 itemkey => itemkey,
560 aname => 'TRX_TYPE_ID'
561 );
562 wf_debug ('TRANSACTION TYPE ID' || l_trx_type);
563 l_app_id :=
564 wf_engine.getitemattrnumber (itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'APPLICATION_ID'
567 );
568 l_approver_rec.last_name :=
569 wf_engine.getitemattrtext (itemtype => itemtype,
570 itemkey => itemkey,
571 aname => 'LAST_NAME'
572 );
573 l_approver_rec.first_name :=
574 wf_engine.getitemattrtext (itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'FIRST_NAME'
577 );
578 l_user_id :=
579 wf_engine.getitemattrnumber (itemtype => itemtype,
580 itemkey => itemkey,
581 aname => 'USER_ID'
582 );
583
584 IF l_user_id = -1
585 THEN
586 l_approver_rec.user_id := NULL;
587 ELSE
588 l_approver_rec.user_id := l_user_id;
589 END IF;
590
591 l_approver_rec.person_id :=
592 wf_engine.getitemattrnumber (itemtype => itemtype,
593 itemkey => itemkey,
594 aname => 'PERSON_ID'
595 );
596 l_approver_rec.api_insertion :=
597 wf_engine.getitemattrtext (itemtype => itemtype,
598 itemkey => itemkey,
599 aname => 'API_INSERTION'
600 );
601 l_approver_rec.authority :=
602 wf_engine.getitemattrtext (itemtype => itemtype,
603 itemkey => itemkey,
604 aname => 'AUTHORITY'
605 );
606 ame_api.updateapprovalstatus (applicationidin => l_app_id,
607 transactionidin => l_transaction_id,
608 approverin => l_approver_rec,
609 transactiontypein => l_trx_type
610 );
611 resultout := 'COMPLETE:';
612 RETURN;
613 END IF;
614
615 --
616 -- CANCEL mode
617 --
618 IF (funcmode = 'CANCEL')
619 THEN
620 --
621 resultout := 'COMPLETE:';
622 RETURN;
623 --
624 END IF;
625
626 --
627 -- TIMEOUT mode
628 --
629 IF (funcmode = 'TIMEOUT')
630 THEN
631 --
632 resultout := 'COMPLETE:';
633 RETURN;
634 --
635 END IF;
636 EXCEPTION
637 WHEN OTHERS
638 THEN
639 wf_core.CONTEXT ('PN_AM_WF',
640 'SET_APPROVAL_STATUS',
641 itemtype,
642 itemkey,
643 actid,
644 funcmode
645 );
646 RAISE;
647 END set_approval_status;
648
649 -- Start of comments
650 --
651 -- Procedure Name : set_status_on_exit
652 -- Description : Called from the Generic Approvals WF to set the Result
653 -- attribute when requet timed out
654 -- Business Rules :
655 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
656 -- Version : 1.0
657 --
658 -- End of comments
659 PROCEDURE set_status_on_exit (
660 itemtype IN VARCHAR2,
664 resultout OUT NOCOPY VARCHAR2
661 itemkey IN VARCHAR2,
662 actid IN NUMBER,
663 funcmode IN VARCHAR2,
665 )
666 IS
667 -- for debug logging
668 BEGIN
669 SAVEPOINT set_atts;
670
671 IF (funcmode = 'RUN')
672 THEN
673 wf_engine.setitemattrtext (itemtype => itemtype,
674 itemkey => itemkey,
675 aname => 'RESULT',
676 avalue => 'REJECT'
677 );
678 resultout := 'COMPLETE:';
679 RETURN;
680 END IF;
681
682 --
683 -- CANCEL mode
684 --
685 IF (funcmode = 'CANCEL')
686 THEN
687 --
688 resultout := 'COMPLETE:';
689 RETURN;
690 --
691 END IF;
692
693 --
694 -- TIMEOUT mode
695 --
696 IF (funcmode = 'TIMEOUT')
697 THEN
698 --
699 resultout := 'COMPLETE:';
700 RETURN;
701 --
702 END IF;
703 EXCEPTION
704 WHEN OTHERS
705 THEN
706 wf_core.CONTEXT ('PN_AM_WF',
707 'SET_STATUS_ON_EXIT',
708 itemtype,
709 itemkey,
710 actid,
711 funcmode
712 );
713 RAISE;
714 END set_status_on_exit;
715
716 -- Start of comments
717 --
718 -- Procedure Name : GET_NOTIFICATION_AGENT
719 -- Description : Used by WF procedures where internal notifications are sent.
720 -- Determines the user_name and description of the notification
721 -- agent.
722 -- Business Rules :
723 -- Parameters : itemtype, itemkey, actid, funcmode, resultout, p_user_id,
724 -- x_name, x_description
725 -- Version : 1.0
726
727 -- End of comments
728 PROCEDURE get_notification_agent (
729 itemtype IN VARCHAR2,
730 itemkey IN VARCHAR2,
731 actid IN NUMBER,
732 funcmode IN VARCHAR2,
733 p_user_id IN NUMBER,
734 x_name OUT NOCOPY VARCHAR2,
735 x_description OUT NOCOPY VARCHAR2
736 )
737 IS
738 CURSOR wf_roles_csr (c_emp_id NUMBER, c_system VARCHAR2)
739 IS
740 SELECT NAME, display_name
741 FROM wf_roles
742 WHERE orig_system_id = c_emp_id AND orig_system = c_system;
743
744 CURSOR fnd_users_csr (c_user_id NUMBER)
745 IS
746 SELECT user_name, description, employee_id
747 FROM fnd_user
748 WHERE user_id = c_user_id;
749
750 l_user VARCHAR2 (50);
751 l_desc VARCHAR2 (100);
752 l_emp NUMBER;
753 BEGIN
754 /* The logic for retrieving a wf user follows this hierarchy:
755 [Step 1] First check if p_user_id is for an FND user.
756 [Step 2] If FND user found, then find if attached to a HR person
757 [Step 3] If Step 2 is TRUE then get user where orig system is 'PER'
758 based on the HR person id (l_emp)
759 [Step 4] If Step 2 TRUE but l_emp is NULL therefore not attached
760 to a HR person, use the FND user as is.
761 [Step 5] If FND user not found, then check if the p_user_id passed
762 is indeed for a HR person that is attached to an FND user.
763 [Step 6] If FND user not found and PER user not found, check if the
764 p_user_id pertains to a HR person not attached to an FND user.
765 [Step 7] Finally, this is an invalid user, user the sysadmin user.
766
767 */
768 -- 1st Check fnd users
769 OPEN fnd_users_csr (p_user_id);
770
771 FETCH fnd_users_csr
772 INTO l_user, l_desc, l_emp;
773
774 IF fnd_users_csr%NOTFOUND
775 THEN
776 -- 2nd check if id passed belongs to an employee rather then fnd user
777 OPEN wf_roles_csr (p_user_id, 'PER');
778
779 FETCH wf_roles_csr
780 INTO l_user, l_desc;
781
782 IF wf_roles_csr%NOTFOUND
783 THEN
784 CLOSE wf_roles_csr;
785
786 -- Maybe a HR user not attached to FND user
787 OPEN wf_roles_csr (p_user_id, 'HZ_PARTY');
788
789 FETCH wf_roles_csr
790 INTO l_user, l_desc;
791
792 CLOSE wf_roles_csr;
793 ELSE
794 CLOSE wf_roles_csr;
795 END IF;
796 END IF;
797
798 CLOSE fnd_users_csr;
799
800 -- if l_emp is not null then the user is attached to an employee
801 IF l_emp IS NOT NULL
802 THEN
803 OPEN wf_roles_csr (l_emp, 'PER');
804
805 FETCH wf_roles_csr
806 INTO l_user, l_desc;
807
808 CLOSE wf_roles_csr;
809 END IF;
810
811 wf_debug ('NOTIFICATION AGENT USER ID' || l_user);
812
813 -- if l_user is still null, no user info was found
817 l_desc := 'System Administrator';
814 IF l_user IS NULL
815 THEN
816 l_user := 'SYSADMIN';
818 END IF;
819
820 x_name := l_user;
821 x_description := l_desc;
822 EXCEPTION
823 WHEN OTHERS
824 THEN
825 wf_core.CONTEXT ('PN_AM_WF',
826 'GET_NOTIFICATION_AGENT',
827 itemtype,
828 itemkey,
829 actid,
830 funcmode
831 );
832 RAISE;
833 END get_notification_agent;
834
835 -- Start of comments
836 --
837 -- Procedure Name : validate_lease_request
838 -- Description : Called from the Generic Approvals WF for validating approval request
839 -- Business Rules :
840 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
841 -- Version : 1.0
842 --
843 -- End of comments
844 PROCEDURE validate_lease_request (
845 itemtype IN VARCHAR2,
846 itemkey IN VARCHAR2,
847 actid IN NUMBER,
848 funcmode IN VARCHAR2,
849 resultout OUT NOCOPY VARCHAR2
850 )
851 IS
852 l_trx_type VARCHAR2 (1000);
853 l_app_id NUMBER;
854 l_knt NUMBER;
855 l_parent_type VARCHAR2 (300);
856 l_parent_key VARCHAR2 (300);
857
858 -- Validate the Transaction Type Id from OAM
859 CURSOR c_validate_trx_type_csr (c_trx_type VARCHAR2)
860 IS
861 SELECT COUNT (*)
862 FROM ame_calling_apps
863 WHERE transaction_type_id = c_trx_type;
864 BEGIN
865 IF (funcmode = 'RUN')
866 THEN
867 l_trx_type :=
868 wf_engine.getitemattrtext (itemtype => itemtype,
869 itemkey => itemkey,
870 aname => 'TRX_TYPE_ID'
871 );
872 l_app_id :=
873 wf_engine.getitemattrtext (itemtype => itemtype,
874 itemkey => itemkey,
875 aname => 'APPLICATION_ID'
876 );
877
878 OPEN c_validate_trx_type_csr (l_trx_type);
879
880 FETCH c_validate_trx_type_csr
881 INTO l_knt;
882
883 CLOSE c_validate_trx_type_csr;
884
885 IF l_knt <> 0 AND l_app_id IS NOT NULL
886 THEN
887 resultout := 'COMPLETE:VALID';
888 ELSE
889 resultout := 'COMPLETE:INVALID';
890 END IF;
891
892 RETURN;
893 END IF;
894
895 --
896 -- CANCEL mode
897 --
898 IF (funcmode = 'CANCEL')
899 THEN
900 --
901 resultout := 'COMPLETE:';
902 RETURN;
903 --
904 END IF;
905
906 --
907 -- TIMEOUT mode
908 --
909 IF (funcmode = 'TIMEOUT')
910 THEN
911 --
912 resultout := 'COMPLETE:';
913 RETURN;
914 --
915 END IF;
916 EXCEPTION
917 WHEN OTHERS
918 THEN
919 IF c_validate_trx_type_csr%ISOPEN
920 THEN
921 CLOSE c_validate_trx_type_csr;
922 END IF;
923
924 wf_core.CONTEXT ('PN_AM_WF',
925 'VALIDATE_LEASE_REQUEST',
926 itemtype,
927 itemkey,
928 actid,
929 funcmode
930 );
931 RAISE;
932 END validate_lease_request;
933
934 -- Start of comments
935 --
936 -- Procedure Name : wf_debug
937 -- Description : to log debug messages
938 -- Business Rules :
939 -- Parameters : p_log_message,p_log_level
940 -- Version : 1.0
941 --
942 -- End of comments
943 PROCEDURE wf_debug (
944 p_log_message IN VARCHAR2,
945 p_log_level IN NUMBER DEFAULT 3
946 )
947 IS
948 BEGIN
949 IF g_debug = 'Y'
950 THEN
951 pn_debug.g_err_stage := p_log_message;
952 pn_debug.WRITE (g_module_name, pn_debug.g_err_stage, p_log_level);
953 ELSE
954 NULL;
955 END IF;
956 END wf_debug;
957
958 FUNCTION submit_program (
959 p_api_name IN VARCHAR2,
960 p_org_id IN NUMBER,
961 p_lease_id IN NUMBER,
962 p_lease_status IN VARCHAR2,
963 p_lease_commencement_date IN DATE
964 )
965 RETURN NUMBER
966 AS
967 l_requestid NUMBER;
968 BEGIN
969 pn_mo_cache_utils.fnd_req_set_org_id (p_org_id);
970 l_requestid :=
971 fnd_request.submit_request ('PN',
972 'PNSCHITM',
973 NULL,
974 NULL,
978 'ABS',
975 FALSE,
976 p_lease_id,
977 --p_lease_status,--Fix for bug#14632702
979 'MAIN',
980 NULL,
981 NULL,
982 'N',
983 p_lease_commencement_date,
984 NULL,
985 NULL,
986 CHR (0),
987 '',
988 '',
989 '',
990 '',
991 '',
992 '',
993 '',
994 '',
995 '',
996 '',
997 '',
998 '',
999 '',
1000 '',
1001 '',
1002 '',
1003 '',
1004 '',
1005 '',
1006 '',
1007 '',
1008 '',
1009 '',
1010 '',
1011 '',
1012 '',
1013 '',
1014 '',
1015 '',
1016 '',
1017 '',
1018 '',
1019 '',
1020 '',
1021 '',
1022 '',
1023 '',
1024 '',
1025 '',
1026 '',
1027 '',
1028 '',
1029 '',
1030 '',
1031 '',
1032 '',
1033 '',
1034 '',
1035 '',
1036 '',
1037 '',
1038 '',
1039 '',
1040 '',
1041 '',
1042 '',
1043 '',
1044 '',
1045 '',
1046 '',
1047 '',
1048 '',
1049 '',
1050 '',
1051 '',
1052 '',
1053 '',
1054 '',
1055 '',
1056 '',
1057 '',
1058 '',
1059 '',
1060 '',
1061 '',
1062 '',
1063 '',
1064 '',
1065 '',
1066 '',
1067 '',
1068 '',
1069 '',
1070 '',
1071 '',
1072 '',
1073 '',
1074 '',
1075 '',
1076 ''
1077 );
1078
1079 --pvt_debug(g_pkg_name||'-SUBMIT_PROGRAM:Request ID'||l_requestid,3);
1080 IF (l_requestid = 0)
1081 THEN
1082 fnd_message.set_name ('PN', 'PN_SUBSCHEDULES_ERR');
1083 fnd_message.set_token ('API_NAME',
1084 p_api_name || '-SubmitSchedulesConcProgram'
1085 );
1086 fnd_msg_pub.ADD;
1087 RAISE fnd_api.g_exc_error;
1088 END IF;
1089
1090 RETURN l_requestid;
1091 END submit_program;
1092 END pn_am_wf;