[Home] [Help]
PACKAGE BODY: APPS.GHR_WF
Source
1 Package Body GHR_WF As
2 /* $Header: ghrwfnot.pkb 120.2 2005/06/28 15:05 sshetty noship $ */
3 --
4 -- Package variables
5 --
6 g_package varchar2(33) := 'ghr_wf.';
7
8
9 PROCEDURE initiate_notification (p_request_id IN NUMBER
10 ,p_result_id IN NUMBER
11 ,p_role IN VARCHAR2
12 )
13 IS
14
15 CURSOR c_get_role_name
16 IS
17 SELECT * FROM WF_ROLES
18 WHERE NAME =p_role;
19
20 CURSOR c_get_rslt
21 IS
22 SELECT *
23 FROM ben_ext_rslt
24 WHERE ext_rslt_id= p_result_id
25 AND request_id = p_request_id;
26
27
28 CURSOR c_dfn (cp_ext_dfn_id NUMBER)
29 IS
30 SELECT DISTINCT (bed.name)
31 FROM ben_ext_dfn bed
32 WHERE bed.ext_dfn_id=cp_ext_dfn_id;
33
34 CURSOR c_get_rec_cnt(cp_rslt_id NUMBER
35 ,cp_dfn_id NUMBER
36 )
37 IS
38 SELECT count(rslt.ext_rslt_id) rec_cnt
39 FROM ben_ext_rcd_in_file rin
40 ,ben_ext_dfn dfn
41 ,ben_ext_rslt_dtl rslt
42 WHERE dfn.ext_dfn_id = cp_dfn_id
43 AND rin.ext_file_id = dfn.ext_file_id
44 AND rin.hide_flag = 'N'
45 AND rin.ext_rcd_id = rslt.EXT_RCD_ID
46 and rslt.ext_rslt_id =cp_rslt_id;
47 l_get_rec_cnt c_get_rec_cnt%ROWTYPE;
48
49 l_dfn c_dfn%ROWTYPE;
50 l_get_role_name c_get_role_name%ROWTYPE;
51 l_get_rslt c_get_rslt%ROWTYPE;
52 l_get_seq VARCHAR2(30);
53 l_attr varchar2(30);
54 l_ignore boolean ;
55 l_userkey varchar2(10);
56 l_owner varchar2(30);
57 BEGIN
58
59 OPEN c_get_rslt;
60 FETCH c_get_rslt INTO l_get_rslt;
61 CLOSE c_get_rslt;
62
63
64 OPEN c_get_rec_cnt (l_get_rslt.ext_rslt_id
65 ,l_get_rslt.ext_dfn_id);
66 FETCH c_get_rec_cnt INTO l_get_rec_cnt;
67 CLOSE c_get_rec_cnt;
68 OPEN c_dfn (l_get_rslt.ext_dfn_id);
69 FETCH c_dfn INTO l_dfn;
70 CLOSE c_dfn;
71
72 OPEN c_get_role_name;
73 LOOP
74 FETCH c_get_role_name INTO l_get_role_name;
75 EXIT WHEN c_get_role_name%NOTFOUND;
76 END LOOP;
77
78 CLOSE c_get_role_name;
79 SELECT ghr_nfc_notif_seq_s.nextval
80 INTO l_get_seq
81 FROM dual;
82
83 l_get_seq :='NFC'||l_get_seq;
84
85
86 wf_engine.CreateProcess ('GHR',l_get_seq,'NOTIFYUSER');
87
88
89 wf_engine.SetItemAttrText (
90 itemType => 'GHR',
91 itemKey => l_get_seq,
92 aname => 'USER_ROLE',
93 avalue => p_role);--l_get_role_name.name );
94
95 l_attr:= wf_engine.GetItemAttrText(
96 itemtype => 'GHR',
97 itemkey =>l_get_seq,
98 aname =>'NOTIF_TYPE',
99 ignore_notfound =>l_ignore);
100 --
101 IF l_attr='EVERY_TIME' THEN
102 wf_engine.setItemAttrText (
103 itemType => 'GHR',
104 itemKey => l_get_seq,
105 aname => 'ERROR_WARN',
106 avalue => ben_ext_thread.g_err_cnt );
107 IF ben_ext_thread.g_err_cnt > 0 THEN
108 wf_engine.setItemAttrText (
109 itemType => 'GHR',
110 itemKey => l_get_seq,
111 aname => 'STATUS',
112 avalue => ' with Errors' );
113
114 END IF;
115 wf_engine.setItemAttrText (
116 itemType => 'GHR',
117 itemKey => l_get_seq,
118 aname => 'TOTALEXTRACT',
119 avalue => l_get_rec_cnt.rec_cnt );
120
121 wf_engine.setItemAttrText (
122 itemType => 'GHR',
123 itemKey => l_get_seq,
124 aname => 'REQUEST_ID',
125 avalue => p_request_id );
126
127 IF INSTR(l_dfn.name,'Position') > 0 THEN
128 wf_engine.setItemAttrText (
129 itemType => 'GHR',
130 itemKey => l_get_seq,
131 aname => 'FILETYPE',
132 avalue => 'Position' );
133
134 ELSIF INSTR(l_dfn.name,'Personnel') > 0 THEN
135 wf_engine.setItemAttrText (
136 itemType => 'GHR',
137 itemKey => l_get_seq,
138 aname => 'FILETYPE',
139 avalue => 'Personnel Action' );
140
141 END IF;
142
143 wf_engine.StartProcess ('GHR',l_get_seq);
144 ELSIF l_attr='ONLY_ERROR' THEN
145 IF l_get_rslt.tot_err_num>0 THEN
146 wf_engine.setItemAttrText (
147 itemType => 'GHR',
148 itemKey => l_get_seq,
149 aname => 'ERROR_WARN',
150 avalue => ben_ext_thread.g_err_cnt );
151
152 wf_engine.setItemAttrText (
153 itemType => 'GHR',
154 itemKey => l_get_seq,
155 aname => 'STATUS',
156 avalue => ' with Errors' );
157
158 wf_engine.setItemAttrText (
159 itemType => 'GHR',
160 itemKey => l_get_seq,
161 aname => 'TOTALEXTRACT',
162 avalue => l_get_rslt.TOT_REC_NUM );
163
164 wf_engine.setItemAttrNumber (
165 itemType => 'GHR',
166 itemKey => l_get_seq,
167 aname => 'REQUEST_ID',
168 avalue => p_request_id );
169
170 IF INSTR(l_dfn.name,'Position') > 0 THEN
171 wf_engine.setItemAttrText (
172 itemType => 'GHR',
173 itemKey => l_get_seq,
174 aname => 'FILETYPE',
175 avalue => 'Position' );
176
177 ELSIF INSTR(l_dfn.name,'Personnel') > 0 THEN
178 wf_engine.setItemAttrText (
179 itemType => 'GHR',
180 itemKey => l_get_seq,
181 aname => 'FILETYPE',
182 avalue => 'Personnel Action' );
183
184 END IF;
185 wf_engine.StartProcess ('GHR',l_get_seq);
186 END IF;
187
188 END IF;
189 END;
190
191 PROCEDURE CHECK_USER_EXIST
192 ( itemtype in varchar2
193 , itemkey in varchar2
194 , actid in number
195 , funcmode in varchar2
196 , result out nocopy varchar2
197 )
198 IS
199 l_attr VARCHAR2(45);
200 l_ignore BOOLEAN;
201
202 CURSOR c_get_users(cp_role VARCHAR2)
203 IS
204 SELECT COUNT(*)
205 FROM wf_user_roles wur
206 WHERE wur.role_name =cp_role;
207 l_count NUMBER;
208 BEGIN
209 l_attr:= wf_engine.GetItemAttrText(
210 itemtype => itemtype,
211 itemkey =>itemkey,
212 aname =>'USER_ROLE',
213 ignore_notfound =>l_ignore);
214 OPEN c_get_users(l_attr) ;
215 FETCH c_get_users INTO l_count;
216 CLOSE c_get_users;
217 IF l_count > 0 THEN
218 result := 'COMPLETE:'||'YES' ;
219 ELSE
220
221 result := 'COMPLETE:'||'NO' ;
222 END IF;
223
224
225
226 EXCEPTION
227 --------
228 WHEN OTHERS THEN
229 result := 'COMPLETE:'||'NO' ;
230 NULL;
231 END;
232
233 end GHR_WF;