PACKAGE BODY: APPS.BEN_FYI_NTFN_SS

Source

1 package body ben_fyi_ntfn_ss as
2 /* $Header: befyintf.pkb 120.1 2008/02/21 11:16:44 sagnanas noship $*/
3 --
4 -- ---------------------------------------------------------------------------+
5 -- Purpose: This function will set the value of the workflow attribute
6 --          receiver name to the seeded workflow role.
7 --          Workflow engine will send notification to this role.
8 -- ---------------------------------------------------------------------------+
9 g_package varchar2(60) := 'ben_fyi_ntfn_ss' ;
10 
11 procedure set_role_to_send_ntfn
12   (itemtype in     varchar2
13   ,itemkey in     varchar2
14   ,actid    in     number
15   ,funcmode in     varchar2
16   ,result   out nocopy varchar2
17   ) is
18 
19 cursor c1 is select pqr.role_id,pqr.role_name
20                   from pqh_roles pqr
21                   where pqr.role_type_cd ='SSBNFT' ;
22 
23 cursor c2(l_role_id number) is
24                   select pei.person_id,
25                   ppf.full_name,
26                   usr.user_name,
27                   usr.user_id user_id
28                   from per_people_extra_info pei,
29                   per_all_people_f ppf,
30                   fnd_user usr,
31                   pqh_roles rls
32                   where pei.information_type = 'PQH_ROLE_USERS'
33                   and pei.person_id = ppf.person_id
34                   and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
35                   and usr.employee_id = ppf.person_id
36                   and rls.role_id = to_number(pei.pei_information3)
37                   and nvl(pei.pei_information5,'Y')='Y'
38                   and rls.role_id = l_role_id;
39 
40 l_proc varchar2(61) := g_package||':'||'set_role_to_send_ntfn';
41 
42 begin
43 --
44 --hr_utility.set_location(l_proc || ' Entering ',10);
45 --
46 for role_rec in c1 loop
47   --hr_utility.set_location('checking people for role '||role_rec.role_name,20);
48   for user_in_role in c2(role_rec.role_id) loop
49     --hr_utility.set_location('user '||user_in_role.user_name||' has this role ',20);
50     --6773021
51     wf_engine.SetItemAttrText(itemtype => itemtype
52                           ,itemkey => itemkey
53                           ,aname  => 'RECEIVER_NAME'
54                           ,avalue => role_rec.role_name);
55   end loop;
56 end loop;
57 --
58 --hr_utility.set_location(l_proc || ' Exiting ',100);
59 --
60 result := 'COMPLETE:';
61 --
62 exception
63 --
64 when others then
65 --
66   result := null;
67   raise;
68 --
69 end set_role_to_send_ntfn;
70 --
71 -- This function is currently not used anywhere
72 --
73 procedure build_url(
74            p_item_type                 in varchar2
75           ,p_item_key                  in varchar2
76           ,p_from_ntfn                 in varchar2 default 'Y'
77           ) is
78 
79 l_proc  varchar2(61) := g_package||':'||'build_url';
80 l_process_name varchar2(100);
81 l_url   varchar2(2000);
82 l_param varchar2(2000);
83 --
84 begin
85 --
86 hr_utility.set_location('Entering'||l_proc,10);
87 --
88 l_url := wf_engine.GetItemAttrText(
89              itemtype => p_item_type,
90              itemkey  => p_item_key,
91              aname    => 'NTFN_LINK_TO_REVIEW');
92 --
93 l_process_name := wf_engine.GetItemAttrText(
94              itemtype => p_item_type,
95              itemkey  => p_item_key,
96              aname    => 'PROCESS_NAME');
97 --
98 l_url := l_url||'&'||'pItemType='||p_item_type||'&'||'pItemKey='||p_item_key||'&'||'pProcessName='||l_process_name||'&'||'pFromNtfn='||p_from_ntfn;
99 --
100 hr_utility.set_location('Leaving'||l_proc,10);
101 --
102 wf_engine.SetItemAttrText(itemtype => p_item_type
103                           ,itemkey => p_item_key
104                           ,aname  => 'NTFN_LINK_TO_REVIEW'
105                           ,avalue => l_url);
106 --
107 end;
108 --
109 end ben_fyi_ntfn_ss;