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