[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_EMP_DTL_PKG
Source
1 PACKAGE BODY igs_ad_emp_dtl_pkg AS
2 /* $Header: IGSAI33B.pls 120.1 2005/07/16 02:01:07 appldev ship $ */
3 /* Change History
4 Who When What
5 Bug : 2037512
6 avenkatr 08-OCT-2001 Added column 'Contact' to Insert_row and Update_row procedures
7
8 ssawhney 7 may 2002 Bug 2338473 -- allow for more than one HZ error to appear.
9 gmaheswa 6 Nov 2003 HZ.K Impact Changes.
10 pkpatel 14 Jul 2005 Bug 4327807 (Person SS Enhancement)
11 Called the igs_ad_hz_emp_dtl_pkg.add_row instead of update_row as per value in
12 Occupitional Title Code
13 */
14 l_rowid VARCHAR2(25);
15 l_msg_data VARCHAR2(25);
16 l_return_status VARCHAR2(1);
17
18 procedure INSERT_ROW (
19 X_ROWID in out NOCOPY VARCHAR2,
20 x_employment_history_id OUT NOCOPY NUMBER,
21 x_PERSON_ID IN NUMBER ,
22 x_START_DT IN DATE,
23 x_END_DT IN DATE,
24 x_TYPE_OF_EMPLOYMENT IN VARCHAR2,
25 x_FRACTION_OF_EMPLOYMENT IN NUMBER,
26 x_TENURE_OF_EMPLOYMENT IN VARCHAR2,
27 x_POSITION IN VARCHAR2,
28 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
29 x_OCCUPATIONAL_TITLE IN VARCHAR2,
30 x_WEEKLY_WORK_HOURS IN NUMBER,
31 x_COMMENTS IN VARCHAR2,
32 x_EMPLOYER IN VARCHAR2,
33 x_EMPLOYED_BY_DIVISION_NAME IN VARCHAR2,
34 x_BRANCH IN VARCHAR2,
35 x_MILITARY_RANK IN VARCHAR2,
36 x_SERVED IN VARCHAR2,
37 x_STATION IN VARCHAR2,
38 x_CONTACT IN VARCHAR2, --Bug : 2037512
39 x_msg_data OUT NOCOPY VARCHAR2,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_object_version_number IN OUT NOCOPY NUMBER,
42 x_employed_by_party_id IN NUMBER,
43 x_reason_for_leaving IN VARCHAR2,
44 X_MODE in VARCHAR2 default 'R'
45 ) AS
46 CURSOR c2 IS
47 SELECT ROWID
48 FROM hz_employment_history
49 WHERE party_id = x_person_id;
50
51 X_CREATED_BY NUMBER;
52 X_CREATION_DATE DATE;
53 X_REQUEST_ID NUMBER;
54 X_PROGRAM_APPLICATION_ID NUMBER;
55 X_PROGRAM_ID NUMBER;
56 X_PROGRAM_UPDATE_DATE DATE;
57 X_LAST_UPDATE_DATE DATE ;
58 X_LAST_UPDATED_BY NUMBER ;
59 X_LAST_UPDATE_LOGIN NUMBER ;
60
61 x_hz_emp_dtl_id igs_ad_hz_emp_dtl.hz_emp_dtl_id%TYPE;
62 x_rowid1 VARCHAR2(25);
63 l_emp_history_rec HZ_PERSON_INFO_V2PUB.employment_history_rec_type;
64 l_msg_count NUMBER;
65 lrow_id varchar2(30);
66 tmp_var1 VARCHAR2(2000);
67 tmp_var VARCHAR2(2000);
68 CURSOR c_birth_date_val is SELECT date_of_birth FROM HZ_PERSON_PROFILES
69 WHERE party_id = x_person_id AND effective_end_Date is null;
70 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
71
72 BEGIN
73 x_PROGRAM_UPDATE_DATE := SYSDATE;
74 x_PROGRAM_ID := 0;
75 X_PROGRAM_APPLICATION_ID := 0;
76 x_REQUEST_ID := 0;
77 X_CREATED_BY := FND_GLOBAL.USER_ID;
78 X_CREATION_DATE := SYSDATE;
79 X_LAST_UPDATE_DATE := SYSDATE;
80
81 IF (X_MODE = 'I') THEN
82 X_LAST_UPDATED_BY := 1;
83 X_LAST_UPDATE_LOGIN := 0;
84
85 ELSIF (X_MODE = 'R') THEN
86 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
87 IF (X_LAST_UPDATED_BY is NULL) THEN
88 X_LAST_UPDATED_BY := -1;
89 END IF;
90 X_LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
91 IF (X_LAST_UPDATE_LOGIN is NULL) THEN
92 X_LAST_UPDATE_LOGIN := -1;
93 END IF;
94
95 ELSE
96 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
97 IGS_GE_MSG_STACK.ADD;
98 app_exception.raise_exception;
99 END IF;
100 OPEN c_birth_date_val;
101 FETCH c_birth_date_val INTO l_date_of_birth; CLOSE c_birth_date_val;
102 IF(x_start_dt IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
103 IF(x_start_dt < l_date_of_birth) THEN
104 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
105 IGS_GE_MSG_STACK.ADD;
106 x_msg_data := FND_MESSAGE.GET;
107 x_return_status := 'E';
108 l_msg_count :=1;
109 RETURN;
110 END IF;
111 END IF;
112
113 --If any of the fields tenure of employment or fraction of employment is not null
114 --then faculty position flag is set to 'Y'
115 IF x_tenure_of_employment IS NOT NULL OR x_fraction_of_employment IS NOT NULL THEN
116 L_EMP_HISTORY_REC.FACULTY_POSITION_FLAG := 'Y';
117 ELSE
118 L_EMP_HISTORY_REC.FACULTY_POSITION_FLAG := 'N';
119 END IF;
120
121 IF x_employed_by_party_id IS NOT NULL AND x_employer IS NOT NULL THEN
122 Fnd_Message.Set_Name('IGS','IGS_PE_EMP_MUT_EXCL');
123 IGS_GE_MSG_STACK.ADD;
124 x_msg_data := FND_MESSAGE.GET;
125 x_return_status := 'E';
126 l_msg_count :=1;
127 RETURN;
128 END IF;
129
130 --Branch,Military_rank,serverd and station fields are obsoleted.
131 L_EMP_HISTORY_REC.EMPLOYMENT_HISTORY_ID := x_employment_history_id;
132 L_EMP_HISTORY_REC.BEGIN_DATE := x_start_dt;
133 L_EMP_HISTORY_REC.EMPLOYED_AS_TITLE := x_position;
134 L_EMP_HISTORY_REC.EMPLOYED_BY_DIVISION_NAME := x_employed_by_division_name;
135 L_EMP_HISTORY_REC.EMPLOYED_BY_NAME_COMPANY := x_employer;
136 L_EMP_HISTORY_REC.END_DATE := x_end_dt;
137 L_EMP_HISTORY_REC.SUPERVISOR_NAME := x_contact; --Bug :2037512
138 L_EMP_HISTORY_REC.PARTY_ID := x_person_id;
139 L_EMP_HISTORY_REC.EMPLOYMENT_TYPE_CODE := x_type_of_employment;
140 L_EMP_HISTORY_REC.FRACTION_OF_TENURE := x_fraction_of_employment;
141 L_EMP_HISTORY_REC.TENURE_CODE := x_tenure_of_employment;
142 L_EMP_HISTORY_REC.WEEKLY_WORK_HOURS := x_weekly_work_hours;
143 L_EMP_HISTORY_REC.COMMENTS := x_comments;
144 L_EMP_HISTORY_REC.RESPONSIBILITY := NULL;
145 L_EMP_HISTORY_REC.CREATED_BY_MODULE := 'IGS';
146 L_EMP_HISTORY_REC.APPLICATION_ID := 8405;
147 L_EMP_HISTORY_REC.EMPLOYED_BY_PARTY_ID := x_employed_by_party_id;
148 L_EMP_HISTORY_REC.REASON_FOR_LEAVING := x_reason_for_leaving;
149
150 --HZ_API is changed from HZ_PER_INFO_PUB to HZ_PERSON_INFO_V2PUB
151 HZ_PERSON_INFO_V2PUB.create_employment_history(
152 P_INIT_MSG_LIST => FND_API.G_TRUE,
153 P_EMPLOYMENT_HISTORY_REC => l_EMP_HISTORY_REC,
154 X_EMPLOYMENT_HISTORY_ID => x_employment_history_id,
155 X_RETURN_STATUS => x_return_status,
156 X_MSG_COUNT => l_msg_count,
157 X_MSG_DATA => x_msg_data
158
159 );
160
161
162 IF x_RETURN_STATUS IN ('E','U') THEN
163 IF l_msg_count > 1 THEN
164 FOR i IN 1..l_msg_count LOOP
165 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
166 tmp_var1 := tmp_var1 || ' '|| tmp_var;
167 END LOOP;
168 x_msg_data := tmp_var1;
169 END IF;
170 RETURN;
171 END IF;
172
173 OPEN c2;
174 FETCH c2 INTO X_ROWID;
175 IF (c2%notfound) THEN
176 CLOSE c2;
177 RAISE no_data_found;
178 END IF;
179 CLOSE c2;
180
181 x_object_version_number := 1;
182
183 --Type_pf_employment,fraction_of_employment,tenure_of_employment,weekly_work_hours,comments are mde obsolete
184 -- as they are passed to HZ_API's
185 igs_ad_hz_emp_dtl_pkg.insert_row(
186 X_ROWID => lrow_id,
187 X_HZ_EMP_DTL_ID => x_hz_emp_dtl_id,
188 X_EMPLOYMENT_HISTORY_ID => x_employment_history_id,
189 X_TYPE_OF_EMPLOYMENT => null,
190 X_FRACION_OF_EMPLOYMENT => null,
191 X_TENURE_OF_EMPLOYMENT => null,
192 X_OCCUPATIONAL_TITLE_CODE => x_occupational_title_code,
193 X_WEEKLY_WORK_HOURS => null,
194 X_COMMENTS => null,
195 X_MODE => x_mode
196 );
197
198
199 END INSERT_ROW;
200
201
202
203 procedure UPDATE_ROW (
204 X_ROWID in VARCHAR2,
205 x_employment_history_id IN NUMBER,
206 x_PERSON_ID IN NUMBER,
207 x_START_DT IN DATE,
208 x_END_DT IN DATE,
209 x_TYPE_OF_EMPLOYMENT IN VARCHAR2,
210 x_FRACTION_OF_EMPLOYMENT IN NUMBER,
211 x_TENURE_OF_EMPLOYMENT IN VARCHAR2,
212 x_POSITION IN VARCHAR2,
213 x_OCCUPATIONAL_TITLE_CODE IN VARCHAR2,
214 x_OCCUPATIONAL_TITLE IN VARCHAR2,
215 x_WEEKLY_WORK_HOURS IN NUMBER,
216 x_COMMENTS IN VARCHAR2,
217 x_EMPLOYER IN VARCHAR2,
218 x_EMPLOYED_BY_DIVISION_NAME IN VARCHAR2,
219 x_BRANCH IN VARCHAR2,
220 x_MILITARY_RANK IN VARCHAR2,
221 x_SERVED IN VARCHAR2,
222 x_STATION IN VARCHAR2,
223 x_CONTACT IN VARCHAR2, -- Bug : 2037512
224 x_msg_data OUT NOCOPY VARCHAR2,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_object_version_number IN OUT NOCOPY NUMBER,
227 x_employed_by_party_id IN NUMBER,
228 x_reason_for_leaving IN VARCHAR2,
229 X_MODE in VARCHAR2 default 'R'
230 ) AS
231
232 l_hz_emp_dtl_id igs_ad_hz_emp_dtl.hz_emp_dtl_id%TYPE;
233 l_LAST_UPDATED_BY NUMBER ;
234 l_LAST_UPDATE_LOGIN NUMBER ;
235 l_msg_count NUMBER;
236 l_emp_history_rec HZ_PERSON_INFO_V2PUB.employment_history_rec_type;
237 lv_rowid VARCHAR2(25);
238 l_occupational_title_code igs_ad_emp_dtl.occupational_title_code%TYPE;
239
240 tmp_var1 VARCHAR2(2000);
241 tmp_var VARCHAR2(2000);
242
243 CURSOR C2 is
244 SELECT rowid,hz_emp_dtl_id, occupational_title_code
245 FROM igs_ad_hz_emp_dtl
246 WHERE employment_history_id = x_employment_history_id;
247
248 CURSOR c_birth_date_val is SELECT date_of_birth FROM HZ_PERSON_PROFILES
249 WHERE party_id = x_person_id AND effective_end_Date is null;
250 l_date_of_birth HZ_PERSON_PROFILES.DATE_OF_BIRTH%TYPE;
251
252
253 BEGIN
254
255 IF (X_MODE = 'I') THEN
256 l_LAST_UPDATED_BY := 1;
257 l_LAST_UPDATE_LOGIN := 0;
258
259 ELSIF (X_MODE = 'R') THEN
260 l_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
261 IF (l_LAST_UPDATED_BY is NULL) THEN
262 l_LAST_UPDATED_BY := -1;
263 END IF;
264 l_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
265 IF (l_LAST_UPDATE_LOGIN is NULL) THEN
266 l_LAST_UPDATE_LOGIN := -1;
267 END IF;
268
269 ELSE
270 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
271 IGS_GE_MSG_STACK.ADD;
272 app_exception.raise_exception;
273 END IF;
274 OPEN c_birth_date_val; FETCH c_birth_date_val INTO l_date_of_birth; CLOSE c_birth_date_val;
275 IF(x_start_dt IS NOT NULL AND l_Date_of_birth IS NOT NULL) THEN
276 IF(x_start_dt < l_date_of_birth) THEN
277 Fnd_Message.Set_Name('IGS','IGS_PE_DREC_GT_BTDT');
278 IGS_GE_MSG_STACK.ADD;
279 x_msg_data := FND_MESSAGE.GET;
280 x_return_status := 'E';
281 l_msg_count :=1;
282 RETURN;
283 END IF;
284 END IF;
285
286 --If any of the fields tenure of employment or fraction of employment is not null
287 --then faculty position flag is set to 'Y'
288
289 IF x_tenure_of_employment IS NOT NULL OR x_fraction_of_employment IS NOT NULL THEN
290 L_EMP_HISTORY_REC.FACULTY_POSITION_FLAG := 'Y';
291 ELSE
292 L_EMP_HISTORY_REC.FACULTY_POSITION_FLAG := 'N';
293 END IF;
294
295 IF x_employed_by_party_id IS NOT NULL AND x_employer IS NOT NULL THEN
296 Fnd_Message.Set_Name('IGS','IGS_PE_EMP_MUT_EXCL');
297 IGS_GE_MSG_STACK.ADD;
298 x_msg_data := FND_MESSAGE.GET;
299 x_return_status := 'E';
300 l_msg_count :=1;
301 RETURN;
302 END IF;
303
304 --Branch,Military_rank,serverd and station fields are obsoleted.
305 L_EMP_HISTORY_REC.EMPLOYMENT_HISTORY_ID := x_employment_history_id;
306 L_EMP_HISTORY_REC.BEGIN_DATE := x_start_dt;
307 L_EMP_HISTORY_REC.EMPLOYED_AS_TITLE := NVL(x_position,FND_API.G_MISS_CHAR);
308 L_EMP_HISTORY_REC.EMPLOYED_BY_DIVISION_NAME := NVL(x_employed_by_division_name,FND_API.G_MISS_CHAR);
309 L_EMP_HISTORY_REC.EMPLOYED_BY_NAME_COMPANY := NVL(x_employer,FND_API.G_MISS_CHAR);
310 L_EMP_HISTORY_REC.END_DATE := NVL(x_end_dt,FND_API.G_MISS_DATE);
311 L_EMP_HISTORY_REC.SUPERVISOR_NAME := NVL(x_contact,FND_API.G_MISS_CHAR); --Bug :2037512
312 L_EMP_HISTORY_REC.PARTY_ID := x_person_id;
313 L_EMP_HISTORY_REC.EMPLOYMENT_TYPE_CODE := NVL(x_type_of_employment,FND_API.G_MISS_CHAR);
314 L_EMP_HISTORY_REC.FRACTION_OF_TENURE := NVL(x_fraction_of_employment,FND_API.G_MISS_NUM);
315 L_EMP_HISTORY_REC.TENURE_CODE := NVL(x_tenure_of_employment,FND_API.G_MISS_CHAR);
316 L_EMP_HISTORY_REC.WEEKLY_WORK_HOURS := NVL(x_weekly_work_hours,FND_API.G_MISS_NUM);
317 L_EMP_HISTORY_REC.COMMENTS := NVL(x_comments,FND_API.G_MISS_CHAR);
318 L_EMP_HISTORY_REC.RESPONSIBILITY := NULL;
319 L_EMP_HISTORY_REC.EMPLOYED_BY_PARTY_ID := NVL(x_employed_by_party_id,FND_API.G_MISS_NUM);
320 L_EMP_HISTORY_REC.REASON_FOR_LEAVING := NVL(x_reason_for_leaving,FND_API.G_MISS_CHAR);
321
322 --HZ_API is changed from HZ_PER_INFO_PUB to HZ_PERSON_INFO_V2PUB
323 HZ_PERSON_INFO_V2PUB.update_employment_history(
324 P_INIT_MSG_LIST => FND_API.G_TRUE,
325 P_EMPLOYMENT_HISTORY_REC => l_EMP_HISTORY_REC,
326 P_OBJECT_VERSION_NUMBER => x_object_version_number,
327 X_RETURN_STATUS => x_return_status,
328 X_MSG_COUNT => l_msg_count,
329 X_MSG_DATA => x_msg_data
330 );
331
332 IF (x_RETURN_STATUS NOT IN ('E','U')) THEN
333
334 OPEN C2;
335 FETCH C2 INTO lv_rowid,l_hz_emp_dtl_id, l_occupational_title_code;
336 CLOSE C2;
337
338
339 --Type_pf_employment,fraction_of_employment,tenure_of_employment,weekly_work_hours,comments are mde obsolete
340 -- as they are passed to HZ_API's
341 IF NVL(l_occupational_title_code, '-1') <> NVL(x_occupational_title_code, '-1') THEN
342 igs_ad_hz_emp_dtl_pkg.add_row(
343 X_ROWID => lv_rowid,
344 X_HZ_EMP_DTL_ID => l_hz_emp_dtl_id,
345 X_EMPLOYMENT_HISTORY_ID => x_employment_history_id,
346 X_TYPE_OF_EMPLOYMENT => null, --x_type_of_employment,
347 X_FRACION_OF_EMPLOYMENT => null, --x_fraction_of_employment,
348 X_TENURE_OF_EMPLOYMENT => null, --x_tenure_of_employment,
349 X_OCCUPATIONAL_TITLE_CODE => x_occupational_title_code,
350 X_WEEKLY_WORK_HOURS => null, --x_weekly_work_hours,
351 X_COMMENTS => null, --x_comments,
352 X_MODE => x_mode
353 );
354 END IF;
355
356
357 ELSE
358 -- this means that the HZ record update gave an error
359 IF l_msg_count > 1 THEN
360 FOR i IN 1..l_msg_count LOOP
361 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
362 tmp_var1 := tmp_var1 || ' '|| tmp_var;
363 END LOOP;
364 x_msg_data := tmp_var1;
365 END IF;
366 RETURN;
367 END IF;
368
369 END UPDATE_ROW;
370
371 procedure DELETE_ROW (
372 X_ROWID in VARCHAR2
373 ) AS
374 Begin
375 igs_ad_hz_emp_dtl_pkg.delete_row(x_rowid);
376 end DELETE_ROW;
377
378
379 END igs_ad_emp_dtl_pkg;