[Home] [Help]
PACKAGE BODY: APPS.AP_EMPLOYEE_UPDATE_PKG
Source
1 PACKAGE BODY AP_EMPLOYEE_UPDATE_PKG AS
2 /* $Header: aphrupdb.pls 120.7 2006/04/29 00:04:50 glchen noship $ */
3
4 -- Performance fix for 115.9 , ARU 2628027
5 -- Add global variable to capture business_group_id
6
7 g_business_group_id FINANCIALS_SYSTEM_PARAMETERS.BUSINESS_GROUP_ID%TYPE;
8
9
10 /* This is a print procedure to split a message string into 132 character
11 strings. */
12
13 PROCEDURE Print
14 (
15 P_debug IN VARCHAR2,
16 P_string IN VARCHAR2
17 ) IS
18
19 stemp VARCHAR2(80);
20 nlength NUMBER := 1;
21
22 BEGIN
23
24 IF (P_Debug in ('y','Y')) THEN
25 WHILE(length(P_string) >= nlength)
26 LOOP
27
28 stemp := substrb(P_string, nlength, 80);
29 fnd_file.put_line(FND_FILE.LOG, stemp);
30 nlength := (nlength + 80);
31
32 END LOOP;
33 END IF;
34
35 EXCEPTION
36 WHEN OTHERS THEN
37
38 IF (SQLCODE <> -20001) THEN
39 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
40 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
41 END IF;
42 APP_EXCEPTION.RAISE_EXCEPTION;
43
44 END Print;
45
46
47
48 FUNCTION Update_Employee(
49 p_update_date IN DATE,
50 p_from_supplier IN VARCHAR2,
51 p_to_supplier IN VARCHAR2,
52 p_debug_mode IN VARCHAR2,
53 p_calling_sequence IN VARCHAR2)
54 RETURN BOOLEAN IS
55 -- ARU 2628027 , change for 115.9 Performance
56 -- Removed reference to FINANCIALS_SYSTEM_PARAMETERS in all the CURSORS below
57 -- to avoid CARTESIAN JOIN. Since join to FINANCIALS_SYSTEM_PARAMETERS was only to
58 -- get the business_group_id , the value is obtained beforehand
59 -- and referenced in each query below as a bind variable
60
61
62 -- BUG 4065699 - The vendor site code will be compared with the lookup value from
63 -- hr_lookups and not the hardcoded value as earlier
64
65 /* Cursor For Name Change where p_from_supplier and p_to_supplier is null*/
66 CURSOR name_cur IS
67 SELECT DECODE(ppf.middle_names,
68 null, ppf.last_name||', '||ppf.first_name,
69 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
70 per_vendor_name
71 , pv.vendor_id ven_vendor_id
72 , pv.vendor_name ven_vendor_name
73 FROM per_all_people_f ppf
74 , ap_suppliers pv
75 WHERE pv.employee_id = ppf.person_id
76 AND ppf.business_group_id = g_business_group_id
77 AND trunc(sysdate)
78 BETWEEN ppf.effective_start_date
79 AND ppf.effective_end_date
80 AND ppf.last_update_date > p_update_date
81 AND DECODE(ppf.middle_names,
82 null, ppf.last_name||', '||ppf.first_name,
83 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
84 <> pv.vendor_name
85 AND not exists (SELECT 'duplicate name exists'
86 FROM ap_suppliers pv1
87 WHERE DECODE(ppf.middle_names,
88 null, ppf.last_name||', '||ppf.first_name,
89 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
90 = pv1.vendor_name)
91 --Bug 2987396 eliminate employees with same name
92 AND not exists (SELECT 'Different Emp with Same Name'
93 FROM per_all_people_f ppf1
94 WHERE ppf1.person_id <> ppf.person_id
95 AND trunc(sysdate) BETWEEN ppf1.effective_start_date
96 AND ppf1.effective_end_date
97 AND DECODE(ppf1.middle_names, null, ppf1.last_name||
98 ', '||ppf1.first_name,ppf1.last_name||', '||
99 ppf1.first_name||' '||ppf1.middle_names)
100 = DECODE(ppf.middle_names,
101 null, ppf.last_name||', '||ppf.first_name,
102 ppf.last_name||', '||ppf.first_name||' '||
103 ppf.middle_names));
104
105
106 /* Cursor For Name Change where p_from_supplier and p_to_supplier is not null */
107 CURSOR name_cur1 IS
108 SELECT DECODE(ppf.middle_names,
109 null, ppf.last_name||', '||ppf.first_name,
110 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
111 per_vendor_name
112 , pv.vendor_id ven_vendor_id
113 , pv.vendor_name ven_vendor_name
114 FROM per_all_people_f ppf
115 , ap_suppliers pv
116 WHERE pv.employee_id = ppf.person_id
117 AND ppf.business_group_id = g_business_group_id
118 AND trunc(sysdate)
119 BETWEEN ppf.effective_start_date
120 AND ppf.effective_end_date
121 AND DECODE(ppf.middle_names,
122 null, ppf.last_name||', '||ppf.first_name,
123 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
124 <> pv.vendor_name
125 AND not exists (SELECT 'duplicate name exists'
126 FROM ap_suppliers pv1
127 WHERE DECODE(ppf.middle_names,
128 null, ppf.last_name||', '||ppf.first_name,
129 ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
130 = pv1.vendor_name)
131 AND pv.vendor_name between p_from_supplier and p_to_supplier
132 --Bug 2987396 eliminate employees with same name.
133 AND not exists (SELECT 'Different Emp with Same Name'
134 FROM per_all_people_f ppf1
135 WHERE ppf1.person_id <> ppf.person_id
136 AND trunc(sysdate) BETWEEN ppf1.effective_start_date
137 AND ppf1.effective_end_date
138 AND DECODE(ppf1.middle_names, null, ppf1.last_name||
139 ', '||ppf1.first_name,ppf1.last_name||', '||
140 ppf1.first_name||' '||ppf1.middle_names)
141 = DECODE(ppf.middle_names,
142 null, ppf.last_name||', '||ppf.first_name,
143 ppf.last_name||', '||ppf.first_name||' '||
144 ppf.middle_names));
145
146 /* Cursor for Inactive Date Change where p_from_supplier and p_to_supplier is null */
147 CURSOR inactive_cur IS
148 SELECT DECODE(greatest(nvl(ppos.actual_termination_date,to_date('4712/12/31',
149 'YYYY/MM/DD')),trunc(sysdate)),
150 trunc(sysdate),ppos.actual_termination_date)
151 per_idate
152 , pv.vendor_id ven_vendor_id
153 , pv.vendor_name ven_vendor_name
154 , pv.end_date_active ven_idate
155 FROM per_all_assignments_f paf
156 , per_periods_of_service ppos
157 , per_all_people_f ppf
158 , ap_suppliers pv
159 WHERE pv.employee_id = ppf.person_id
160 AND ppf.person_id = paf.person_id
161 AND ppf.person_id = ppos.person_id
162 AND ppf.business_group_id = g_business_group_id
163 AND DECODE(ppos.actual_termination_date,
164 null, trunc(sysdate),
165 ppos.actual_termination_date)
166 BETWEEN paf.effective_start_date
167 AND paf.effective_end_date
168 AND ppos.date_start = (SELECT max(ppos2.date_start)
169 FROM per_periods_of_service ppos2
170 WHERE ppos2.person_id = ppos.person_id
171 AND ppos2.date_start <= trunc(sysdate))
172 AND trunc(sysdate)
173 BETWEEN ppf.effective_start_date
174 AND ppf.effective_end_date
175 AND paf.assignment_type = 'E'
176 AND ppos.last_update_date > p_update_date
177 AND nvl(ppos.actual_termination_date,trunc(sysdate)) <>
178 nvl(pv.end_date_active,trunc(sysdate));
179
180 /* Cursor for Inactive Date Change where p_from_supplier and p_to_supplier is not null */
181 CURSOR inactive_cur1 IS
182 SELECT DECODE(greatest(nvl(ppos.actual_termination_date,to_date('4712/12/31',
183 'YYYY/MM/DD')),trunc(sysdate)),
184 trunc(sysdate),ppos.actual_termination_date)
185 per_idate
186 , pv.vendor_id ven_vendor_id
187 , pv.vendor_name ven_vendor_name
188 , pv.end_date_active ven_idate
189 FROM per_all_assignments_f paf
190 , per_periods_of_service ppos
191 , per_all_people_f ppf
192 , ap_suppliers pv
193 WHERE pv.employee_id = ppf.person_id
194 AND ppf.person_id = paf.person_id
195 AND ppf.person_id = ppos.person_id
196 AND ppf.business_group_id = g_business_group_id
197 AND DECODE(ppos.actual_termination_date,
198 null, trunc(sysdate),
199 ppos.actual_termination_date)
200 BETWEEN paf.effective_start_date
201 AND paf.effective_end_date
202 AND ppos.date_start = (SELECT max(ppos2.date_start)
203 FROM per_periods_of_service ppos2
204 WHERE ppos2.person_id = ppos.person_id
205 AND ppos2.date_start <= trunc(sysdate))
206 AND trunc(sysdate)
207 BETWEEN ppf.effective_start_date
208 AND ppf.effective_end_date
209 AND paf.assignment_type = 'E'
210 AND nvl(ppos.actual_termination_date,trunc(sysdate)) <>
211 nvl(pv.end_date_active,trunc(sysdate))
212 AND pv.vendor_name between p_from_supplier and p_to_supplier;
213
214
215 --inactive_date date := '01-JAN-1900';
216 --Bug fix 2161455 change format to standards. DD/MM/YYYY
217 --Bug fix 2219492, fix the assignment.
218 inactive_date date := to_date('01/01/1900', 'DD/MM/YYYY');
219 current_calling_sequence VARCHAR2(2000);
220 l_debug_mode VARCHAR2(1);
221
222
223 BEGIN
224
225 l_debug_mode := p_debug_mode;
226
227 current_calling_sequence := 'AP_EMPLOYEE_UPDATE_PKG.Update_Employee-> '
228 ||p_calling_sequence;
229
230 Print(l_debug_mode, current_calling_sequence);
231
232
233 -- Performance fix for 115.9 , ARU 2628027
234 -- Get value for g_business_group_id
235
236 SELECT business_group_id
237 INTO g_business_group_id
238 FROM financials_system_parameters ;
239
240
241 if (p_from_supplier is null and p_to_supplier is null) then
242
243
244 FOR name_rec IN name_cur LOOP
245
246 IF nvl(name_rec.ven_vendor_name,'EMPTY') <> nvl(name_rec.per_vendor_name,'EMPTY') THEN
247
248 UPDATE ap_suppliers
249 SET vendor_name = name_rec.per_vendor_name
250 ,last_update_date = sysdate -- Bug 3191168
251 ,last_updated_by = fnd_global.user_id -- Bug 3191168
252 ,last_update_login = fnd_global.login_id -- Bug 3191168
253 WHERE vendor_id = name_rec.ven_vendor_id;
254
255 END IF;
256
257 END LOOP;
258
259 COMMIT;
260
261
262 FOR inactive_rec IN inactive_cur LOOP
263
264 IF nvl(inactive_rec.ven_idate, inactive_date) <> nvl(inactive_rec.per_idate, inactive_date) THEN
265
266 UPDATE ap_supplier_sites_all --bug 3162861
267 SET inactive_date = inactive_rec.per_idate
268 ,last_update_date = sysdate -- Bug 3191168
269 ,last_updated_by = fnd_global.user_id -- Bug 3191168
270 ,last_update_login = fnd_global.login_id -- Bug 3191168
271 WHERE vendor_id = inactive_rec.ven_vendor_id;
272
273 /* Bug 1561680 */
274 UPDATE ap_suppliers
275 SET end_date_active = inactive_rec.per_idate
276 ,last_update_date = sysdate -- Bug 3191168
277 ,last_updated_by = fnd_global.user_id -- Bug 3191168
278 ,last_update_login = fnd_global.login_id -- Bug 3191168
279 WHERE vendor_id = inactive_rec.ven_vendor_id;
280 END IF;
281
282 END LOOP;
283
284 COMMIT;
285
286 else
287
288 FOR name_rec1 IN name_cur1 LOOP
289
290 IF nvl(name_rec1.ven_vendor_name,'EMPTY') <> nvl(name_rec1.per_vendor_name,'EMPTY') THEN
291
292 UPDATE ap_suppliers
293 SET vendor_name = name_rec1.per_vendor_name
294 ,last_update_date = sysdate -- Bug 3191168
295 ,last_updated_by = fnd_global.user_id -- Bug 3191168
296 ,last_update_login = fnd_global.login_id -- Bug 3191168
297 WHERE vendor_id = name_rec1.ven_vendor_id;
298 END IF;
299
300 END LOOP;
301
302 COMMIT;
303
304 FOR inactive_rec1 IN inactive_cur1 LOOP
305
306 IF nvl(inactive_rec1.ven_idate, inactive_date) <> nvl(inactive_rec1.per_idate, inactive_date) THEN
307
308 UPDATE ap_supplier_sites_all --bug 3162861
309 SET inactive_date = inactive_rec1.per_idate
310 ,last_update_date = sysdate -- Bug 3191168
311 ,last_updated_by = fnd_global.user_id -- Bug 3191168
312 ,last_update_login = fnd_global.login_id -- Bug 3191168
313 WHERE vendor_id = inactive_rec1.ven_vendor_id;
314
315 /* Bug 1561680 */
316 UPDATE ap_suppliers
317 SET end_date_active = inactive_rec1.per_idate
318 ,last_update_date = sysdate -- Bug 3191168
319 ,last_updated_by = fnd_global.user_id -- Bug 3191168
320 ,last_update_login = fnd_global.login_id -- Bug 3191168
321 WHERE vendor_id = inactive_rec1.ven_vendor_id;
322 END IF;
323
324 END LOOP;
325
326 COMMIT;
327
328 end if;
329
330
331
332 RETURN (TRUE);
333
334 RETURN NULL; EXCEPTION
335
336 WHEN OTHERS then
337
338 IF (SQLCODE < 0) then
339 Print(l_debug_mode,SQLERRM);
340 END IF;
341
342 RETURN (FALSE);
343
344 END Update_Employee;
345
346 END AP_EMPLOYEE_UPDATE_PKG;