[Home] [Help]
PACKAGE BODY: APPS.PSP_WF_EFT_PKG
Source
1 PACKAGE BODY PSP_WF_EFT_PKG AS
5 itemkey IN VARCHAR2,
2 --$Header: PSPWFCRB.pls 115.11 2002/12/31 09:58:05 ddubey ship $
3
4 PROCEDURE Populate_Attribute(itemtype IN VARCHAR2,
6 actid IN NUMBER,
7 funcmode IN VARCHAR2,
8 result OUT NOCOPY VARCHAR2
9 ) IS
10
11 l_person_id NUMBER;
12 l_template_id NUMBER;
13 l_bgn_Perd DATE;
14 l_end_Perd DATE;
15 l_emp_num VARCHAR2(30);
16 l_emp_name VARCHAR2(240);
17 l_sup_name VARCHAR2(240);
18 l_orig_login VARCHAR2(100);
19 l_result VARCHAR2(30);
20 l_emp_login varchar2(100);
21 -- 8.31.99...Itemkey passed has version number concatenated..causes probs with some queries
22 l_itemkey varchar2(30);
23 l_ver varchar2(2);
24 -- End 8.31.99
25 CURSOR get_emp_details_csr IS
26 SELECT full_name,
27 employee_number
28 FROM per_people_f
29 WHERE person_id = L_Person_id and
30 L_end_perd between effective_start_date and effective_end_date;
31
32 CURSOR get_period_details_csr IS
33 SELECT Begin_date,
34 End_date
35 FROM psp_effort_report_templates
36 WHERE template_id = l_template_id;
37
38 CURSOR get_supervisor_csr IS
39 SELECT ppf.full_name
40 FROM per_people_f ppf
41 WHERE ppf.person_id = (select paf.supervisor_id from per_assignments_f paf
42 where paf.person_id = l_person_id
43 AND paf.assignment_type ='E' --Added for bug 2624259.
44 AND l_end_perd between paf.effective_start_date and
45 paf.effective_end_date and
46 paf.primary_flag = 'Y' ) and
47 l_end_perd between ppf.effective_start_date and ppf.effective_end_date;
48
49 --For Bug 2624263: Changed the select. Selecting name from fnd_users instead of wf_users
50 CURSOR get_user_name_csr IS
51 SELECT usr.user_name
52 FROM fnd_user usr,
53 per_people_f ppf,
54 fnd_languages fndl
55 WHERE usr.employee_id = ppf.person_id
56 AND trunc ( SYSDATE ) between ppf.effective_start_date and ppf.effective_end_date
57 AND fndl.installed_flag='B'
58 AND usr.end_date IS NULL
59 AND ppf.person_id = l_person_id
60 AND rownum = 1 ;
61 /************
62 SELECT name
63 FROM wf_users
64 where orig_system_id = l_person_id and
65 orig_system = 'PER' and
66 status = 'ACTIVE' and
67 rownum = 1;
68 ******/
69 --End of bug fix 2624263
70
71 begin
72 IF (funcmode = 'RUN') THEN
73 L_orig_login := Fnd_Global.User_name;
74
75 -- 8.31.99 ... Here is where itemkey gives prob...remove the version num..
76
77 l_itemkey := substr(itemkey, 1, length(itemkey) - 1);
78 l_ver := substr(itemkey, length(itemkey));
79 -- 8.31.99
80
81 SELECT person_id, template_id INTO L_person_id,L_template_id
82 FROM psp_effort_reports
83 -- WHERE effort_report_id = to_number(itemkey); 8.31.99...use the l_itemkey
84 WHERE effort_report_id = to_number(l_itemkey)
85 AND version_num = to_number(l_ver); -- to get the correct record and avoid multiple row returns.
86 -- End 8.31.99
87
88 OPEN get_period_details_csr;
89 FETCH get_period_details_csr INTO L_bgn_perd,
90 L_end_perd;
91 CLOSE get_period_details_csr;
92
93 OPEN get_emp_details_csr;
94 FETCH get_emp_details_csr INTO L_emp_name,
95 L_emp_num;
96 CLOSE get_emp_details_csr;
97
98 OPEN get_supervisor_csr;
99 FETCH get_supervisor_csr INTO L_sup_name;
100 CLOSE get_supervisor_csr;
101
102 OPEN get_user_name_csr;
103 FETCH get_user_name_csr INTO l_emp_login;
104 if get_user_name_csr%NOTFOUND then
105 result := wf_engine.eng_completed||':'||'N';
106 else
107 result := wf_engine.eng_completed||':'||'Y';
108 end if;
109 close get_user_name_csr;
110
111 wf_engine.setitemattrtext(itemtype,
112 itemkey,
113 'EMP_NAME',
114 L_emp_name);
115 --dbms_output.put_line('Emp Name ' || l_emp_name);
116
117 wf_engine.setitemattrtext(itemtype,
118 itemkey,
119 'EMP_NO',
120 L_emp_num);
121 --dbms_output.put_line('Emp No ' || l_emp_num);
122
123 wf_engine.setitemattrtext(itemtype,
124 itemkey,
125 'SUP_NAME',
126 L_sup_name);
127
128 --dbms_output.put_line('Sup Name ' || l_sup_name);
129
130 wf_engine.setitemattrtext(itemtype,
131 itemkey,
132 'EMP_LOGIN',
133 l_emp_login);
134
135 --dbms_output.put_line('Emp Login ' || l_emp_login);
136
137 wf_engine.setitemattrtext(itemtype,
138 itemkey,
139 'ORG_NAME',
140 L_orig_login);
141
142 --dbms_output.put_line('Orig Login ' || l_orig_login);
143
144
145 wf_engine.setitemattrtext(itemtype,
146 itemkey,
147 'BEGIN_DT',
148 L_bgn_perd);
149
150 --dbms_output.put_line('Begin Dt ' || to_char(l_bgn_perd));
151
152 wf_engine.setitemattrtext(itemtype,
153 itemkey,
154 'END_DT',
155 L_end_perd);
156
157 --dbms_output.put_line('End Dt ' || to_char(l_end_perd));
158
159 /* wf_engine.setitemattrtext(itemtype,
160 itemkey,
161 'RESULT',
162 l_result);
163 */
164
165 -- result := 'COMPLETE';
166 END IF;
167
168 EXCEPTION
169 WHEN OTHERS THEN
170 wf_core.context('Psh_Wf_Eft_Pkg','Populate_Attribute',itemtype,itemkey,to_char(actid),funcmode);
171 raise;
172
173 end Populate_Attribute;
174
175 PROCEDURE Populate_Attribute1(itemtype IN VARCHAR2,
176 itemkey IN VARCHAR2,
177 actid IN NUMBER,
178 funcmode IN VARCHAR2,
179 result OUT NOCOPY VARCHAR2
180 ) IS
181
182 l_person_id NUMBER;
183 l_template_id NUMBER;
184 l_supervisor_id NUMBER;
185 l_no_sup NUMBER;
186 l_total_sup NUMBER;
187 l_count NUMBER;
188 l_bgn_Perd DATE;
189 l_end_Perd DATE;
190 l_emp_num VARCHAR2(30);
191 l_emp_name VARCHAR2(240);
192 l_sup_name VARCHAR2(240);
193 l_result VARCHAR2(30);
194 l_action VARCHAR2(30);
195 l_emp_login varchar2(100);
196 l_supervisor_login varchar2(100);
197 l_tmp varchar2(50);
198
199 -- 8.31.99....remove the version number from the itemkey and use the ER Id...
200 l_itemkey varchar2(30);
201 l_ver varchar2(2);
202 --8.31.99
203
204 CURSOR get_emp_details_csr IS
205 SELECT full_name,
206 employee_number
207 FROM per_people_f
208 WHERE person_id = L_Person_id and
209 L_end_perd between effective_start_date and effective_end_date;
210
211 CURSOR get_period_details_csr IS
212 SELECT Begin_date,
213 End_date
214 FROM psp_effort_report_templates
215 WHERE template_id = l_template_id;
216
217 CURSOR get_supervisor_csr IS
218 SELECT ppf.full_name, ppf.person_id
219 FROM per_people_f ppf
220 WHERE ppf.person_id = (select paf.supervisor_id from per_assignments_f paf
221 where paf.person_id = l_person_id
222 AND paf.assignment_type ='E' --Added for bug 2624259.
223 AND l_end_perd between paf.effective_start_date and
224 paf.effective_end_date and
225 primary_flag = 'Y') and
226 l_end_perd between ppf.effective_start_date and ppf.effective_end_date;
227
228 --For Bug 2624263 : Supervisor 's login name being selected from fnd_users instead of wf_users
229 CURSOR get_supervisor_login_csr IS
230 SELECT usr.user_name
231 FROM fnd_user usr,
232 per_people_f ppf,
233 fnd_languages fndl
234 WHERE usr.employee_id = ppf.person_id
235 AND trunc ( SYSDATE ) between ppf.effective_start_date and ppf.effective_end_date
236 AND fndl.installed_flag='B'
237 AND usr.end_date IS NULL
238 AND ppf.person_id = l_supervisor_id
239 AND rownum = 1 ;
240 /*************************
241 SELECT name
242 FROM wf_users
243 where orig_system_id = l_supervisor_id and
244 orig_system = 'PER' and
245 status = 'ACTIVE';
246 **************************/
247 --end of bug fix 2624263
248 begin
249 IF (funcmode = 'RUN') THEN
250 L_emp_login := Fnd_Global.User_name;
251
252 -- 8.31.99...itemkey causes prob with the version num concatenated..remove it..
253 l_itemkey := substr(itemkey, 1, length(itemkey) - 1);
254 l_ver := substr(itemkey, length(itemkey));
255 -- End 8.31.99
256
257 SELECT person_id, template_id INTO L_person_id, l_template_id
258 FROM psp_effort_reports
259 --8.31.99 ....use the l_itemkey above...BUG#969850
260 -- WHERE effort_report_id = to_number(itemkey);
261 WHERE effort_report_id = to_number(l_itemkey)
262 AND version_num = to_number(l_ver); -- to get the correct record and avoid multiple row returns.
263 -- End 8.31.99
264
265 --dbms_output.put_line('Crossed 1 Select ');
266
267 OPEN get_period_details_csr;
268 FETCH get_period_details_csr INTO L_bgn_perd,
269 L_end_perd;
270 CLOSE get_period_details_csr;
271
272 --dbms_output.put_line('Crossed 2 Select ');
273
274 OPEN get_emp_details_csr;
275 FETCH get_emp_details_csr INTO L_emp_name,
276 L_emp_num;
277 CLOSE get_emp_details_csr;
278
279 --dbms_output.put_line('Crossed 3 Select ');
280
281 OPEN get_supervisor_csr;
282 LOOP
283 FETCH get_supervisor_csr INTO L_sup_name, l_supervisor_id;
284 EXIT WHEN get_supervisor_csr%NOTFOUND;
285
286 l_action := wf_engine.GetItemAttrText (
287 itemtype,
288 itemkey,
289 aname => 'RESULT');
290 l_no_sup := wf_engine.GetItemAttrNumber (
291 itemtype,
292 itemkey,
293 aname => 'L_COUNTER');
294
295 l_total_sup := wf_engine.GetItemAttrNumber (
296 itemtype,
297 itemkey,
298 aname => 'NO_SUP');
299
300
301 --dbms_output.put_line('Supervisor ID ' || to_char(l_supervisor_id));
302 --dbms_output.put_line('Action ' || l_action);
303 --dbms_output.put_line('No Of Supervisors ' || to_char(l_no_sup));
304 l_no_sup := l_no_sup + 1;
305 l_count := 1;
306 if l_count = l_no_sup then
307 OPEN get_supervisor_login_csr;
308 FETCH get_supervisor_login_csr INTO l_supervisor_login;
309 if get_supervisor_login_csr%NOTFOUND then
310 if l_action = 'Accepted' then
311 l_tmp := wf_engine.eng_completed||':'||'NOSUP_ACC';
312 else
313 l_tmp := wf_engine.eng_completed||':'||'NOSUP_RET';
314 end if;
315 else
316 if l_action = 'Accepted' then
317 l_tmp := wf_engine.eng_completed||':'||'SUP_ACC';
318 else
319 l_tmp := wf_engine.eng_completed||':'||'SUP_RET';
320 end if;
321 end if;
322 close get_supervisor_login_csr;
323
324 --dbms_output.put_line('Result ' || l_tmp);
325 result := l_tmp;
326
327
328 wf_engine.SetItemAttrNumber (
329 itemtype,
330 itemkey,
331 'L_COUNTER',
332 l_no_sup);
333
334 wf_engine.setitemattrtext(itemtype,
335 itemkey,
336 'EMP_NAME',
337 L_emp_name);
338
339 --dbms_output.put_line('Emp Name ' || l_emp_name);
340
341 wf_engine.setitemattrtext(itemtype,
342 itemkey,
343 'EMP_NO',
344 L_emp_num);
345
346 --dbms_output.put_line('Emp Num ' || l_emp_num);
347
348 wf_engine.setitemattrtext(itemtype,
349 itemkey,
350 'SUP_NAME',
351 L_sup_name);
352
353 --dbms_output.put_line('Sup Name ' || l_sup_name);
354
355 wf_engine.setitemattrtext(itemtype,
356 itemkey,
357 'EMP_LOGIN',
358 l_emp_login);
359
360 --dbms_output.put_line('Emp Login ' || l_emp_login);
361
362 wf_engine.setitemattrtext(itemtype,
363 itemkey,
364 'BEGIN_DT',
365 L_bgn_perd);
366
367 --dbms_output.put_line('Begin Period ' || to_date(l_bgn_perd));
368
369 wf_engine.setitemattrtext(itemtype,
370 itemkey,
371 'END_DT',
372 L_end_perd);
373
374 --dbms_output.put_line('End Period ' || to_date(l_end_perd));
375
376 wf_engine.setitemattrtext(itemtype,
377 itemkey,
378 'SUP_LOGIN',
379 l_supervisor_login);
380 --dbms_output.put_line('Supervisor Login ' || l_supervisor_login);
381 end if;
382 END LOOP;
383 CLOSE get_supervisor_csr;
384 END IF;
385
386 EXCEPTION
387 WHEN OTHERS THEN
388 wf_core.context('Psh_Wf_Eft_Pkg','Populate_Attribute',itemtype,itemkey,to_char(actid),funcmode);
389 raise;
390
391 end Populate_Attribute1;
392
393 END PSP_WF_EFT_PKG;
394