[Home] [Help]
PACKAGE BODY: APPS.HZ_EMPLOYMENT_HISTORY_PKG
Source
1 PACKAGE BODY HZ_EMPLOYMENT_HISTORY_PKG as
2 /* $Header: ARHEHITB.pls 120.7 2005/10/30 03:52:10 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6
7 x_EMPLOYMENT_HISTORY_ID IN OUT NOCOPY NUMBER,
8 x_BEGIN_DATE IN DATE,
9 x_PARTY_ID IN NUMBER,
10 x_EMPLOYED_AS_TITLE IN VARCHAR2,
11 x_EMPLOYED_BY_DIVISION_NAME IN VARCHAR2,
12 x_EMPLOYED_BY_NAME_COMPANY IN VARCHAR2,
13 x_END_DATE IN DATE,
14 x_SUPERVISOR_NAME IN VARCHAR2,
15 x_BRANCH IN VARCHAR2,
16 x_MILITARY_RANK IN VARCHAR2,
17 x_SERVED IN VARCHAR2,
18 x_STATION IN VARCHAR2,
19 x_RESPONSIBILITY IN VARCHAR2,
20 x_STATUS IN VARCHAR2,
21 x_OBJECT_VERSION_NUMBER IN NUMBER,
22 x_CREATED_BY_MODULE IN VARCHAR2,
23 x_APPLICATION_ID IN NUMBER,
24 x_EMPLOYED_BY_PARTY_ID IN NUMBER,
25 x_REASON_FOR_LEAVING IN VARCHAR2,
26 x_FACULTY_POSITION_FLAG IN VARCHAR2,
27 x_TENURE_CODE IN VARCHAR2,
28 x_FRACTION_OF_TENURE IN NUMBER,
29 x_EMPLOYMENT_TYPE_CODE IN VARCHAR2,
30 x_EMPLOYED_AS_TITLE_CODE IN VARCHAR2,
31 x_WEEKLY_WORK_HOURS IN NUMBER,
32 x_COMMENTS IN VARCHAR2
33
34 ) IS
35
36 l_success VARCHAR2(1) := 'N';
37
38 BEGIN
39
40 WHILE l_success = 'N' LOOP
41 BEGIN
42
43 INSERT INTO HZ_EMPLOYMENT_HISTORY(
44 EMPLOYMENT_HISTORY_ID,
45 BEGIN_DATE,
46 PARTY_ID,
47 EMPLOYED_AS_TITLE,
48 EMPLOYED_BY_DIVISION_NAME,
49 EMPLOYED_BY_NAME_COMPANY,
50 END_DATE,
51 SUPERVISOR_NAME,
52 BRANCH,
53 MILITARY_RANK,
54 CREATED_BY,
55 CREATION_DATE,
56 SERVED,
57 LAST_UPDATE_LOGIN,
58 STATION,
59 LAST_UPDATE_DATE,
60 LAST_UPDATED_BY,
61 REQUEST_ID,
62 PROGRAM_APPLICATION_ID,
63 PROGRAM_ID,
64 PROGRAM_UPDATE_DATE,
65 RESPONSIBILITY,
66 STATUS,
67 OBJECT_VERSION_NUMBER,
68 CREATED_BY_MODULE,
69 APPLICATION_ID,
70 EMPLOYED_BY_PARTY_ID,
71 REASON_FOR_LEAVING,
72 FACULTY_POSITION_FLAG,
73 TENURE_CODE,
74 FRACTION_OF_TENURE,
75 EMPLOYMENT_TYPE_CODE,
76 EMPLOYED_AS_TITLE_CODE,
77 WEEKLY_WORK_HOURS,
78 COMMENTS
79 ) VALUES (
80 decode( x_EMPLOYMENT_HISTORY_ID, FND_API.G_MISS_NUM, HZ_EMPLOYMENT_HISTORY_S.NEXTVAL, NULL, HZ_EMPLOYMENT_HISTORY_S.NEXTVAL, x_EMPLOYMENT_HISTORY_ID ),
81 decode( x_BEGIN_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_BEGIN_DATE),
82 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
83 decode( x_EMPLOYED_AS_TITLE, FND_API.G_MISS_CHAR, NULL,x_EMPLOYED_AS_TITLE),
84 decode( x_EMPLOYED_BY_DIVISION_NAME, FND_API.G_MISS_CHAR, NULL,x_EMPLOYED_BY_DIVISION_NAME),
85 decode( x_EMPLOYED_BY_NAME_COMPANY, FND_API.G_MISS_CHAR, NULL,x_EMPLOYED_BY_NAME_COMPANY),
86 decode( x_END_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_END_DATE),
87 decode( x_SUPERVISOR_NAME, FND_API.G_MISS_CHAR, NULL,x_SUPERVISOR_NAME),
88 decode( x_BRANCH, FND_API.G_MISS_CHAR, NULL,x_BRANCH),
89 decode( x_MILITARY_RANK, FND_API.G_MISS_CHAR, NULL,x_MILITARY_RANK),
90 HZ_UTILITY_V2PUB.CREATED_BY,
91 HZ_UTILITY_V2PUB.CREATION_DATE,
92 decode( x_SERVED, FND_API.G_MISS_CHAR, NULL,x_SERVED),
93 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
94 decode( x_STATION, FND_API.G_MISS_CHAR, NULL,x_STATION),
95 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
96 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
97 HZ_UTILITY_V2PUB.REQUEST_ID,
98 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
99 HZ_UTILITY_V2PUB.PROGRAM_ID,
100 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
101 decode( x_RESPONSIBILITY, FND_API.G_MISS_CHAR, NULL, x_RESPONSIBILITY),
102 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
103 DECODE( x_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, x_OBJECT_VERSION_NUMBER ),
104 DECODE( x_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, x_CREATED_BY_MODULE ),
105 HZ_UTILITY_V2PUB.APPLICATION_ID,
106 decode( x_EMPLOYED_BY_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_EMPLOYED_BY_PARTY_ID),
107 decode( x_REASON_FOR_LEAVING, FND_API.G_MISS_CHAR, NULL, x_REASON_FOR_LEAVING ),
108 decode( x_FACULTY_POSITION_FLAG, FND_API.G_MISS_CHAR, NULL, x_FACULTY_POSITION_FLAG ),
109 decode( x_TENURE_CODE, FND_API.G_MISS_CHAR, NULL, X_TENURE_CODE ),
110 decode( x_FRACTION_OF_TENURE, FND_API.G_MISS_NUM, NULL,x_FRACTION_OF_TENURE),
111 decode( x_EMPLOYMENT_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, x_EMPLOYMENT_TYPE_CODE ),
112 decode( x_EMPLOYED_AS_TITLE_CODE, FND_API.G_MISS_CHAR, NULL, x_EMPLOYED_AS_TITLE_CODE ),
113 decode( x_WEEKLY_WORK_HOURS, FND_API.G_MISS_NUM, NULL,x_WEEKLY_WORK_HOURS),
114 decode( x_COMMENTS, FND_API.G_MISS_CHAR, NULL, x_COMMENTS )
115 )
116
117 RETURNING
118 EMPLOYMENT_HISTORY_ID
119 INTO
120 X_EMPLOYMENT_HISTORY_ID;
121
122 l_success := 'Y';
123
124 EXCEPTION
125 WHEN DUP_VAL_ON_INDEX THEN
126 IF INSTRB( SQLERRM, 'HZ_EMPLOYMENT_HISTORY_U1' ) <> 0 OR
127 INSTRB( SQLERRM, 'HZ_EMPLOYMENT_HISTORY_PK' ) <> 0
128 THEN
129 DECLARE
130 l_count NUMBER;
131 l_dummy VARCHAR2(1);
132 BEGIN
133 l_count := 1;
134 WHILE l_count > 0 LOOP
135 SELECT HZ_EMPLOYMENT_HISTORY_S.NEXTVAL
136 INTO X_EMPLOYMENT_HISTORY_ID FROM dual;
137 BEGIN
138 SELECT 'Y' INTO l_dummy
139 FROM HZ_EMPLOYMENT_HISTORY
140 WHERE EMPLOYMENT_HISTORY_ID = EMPLOYMENT_HISTORY_ID;
141 l_count := 1;
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 l_count := 0;
145 END;
146 END LOOP;
147 END;
148 ELSE
149 RAISE;
150 END IF;
151
152 END;
153 END LOOP;
154
155 END Insert_Row;
156
157
158
159
160 PROCEDURE Delete_Row( x_EMPLOYMENT_HISTORY_ID NUMBER
161 ) IS
162 BEGIN
163 DELETE FROM HZ_EMPLOYMENT_HISTORY
164 WHERE EMPLOYMENT_HISTORY_ID = x_EMPLOYMENT_HISTORY_ID;
165 If (SQL%NOTFOUND) then
166 RAISE NO_DATA_FOUND;
167 End If;
168 END Delete_Row;
169
170
171
172 PROCEDURE Update_Row(
173 x_Rowid IN OUT NOCOPY VARCHAR2,
174 x_EMPLOYMENT_HISTORY_ID IN OUT NOCOPY NUMBER,
175 x_BEGIN_DATE IN DATE,
176 x_PARTY_ID IN NUMBER,
177 x_EMPLOYED_AS_TITLE IN VARCHAR2,
178 x_EMPLOYED_BY_DIVISION_NAME IN VARCHAR2,
179 x_EMPLOYED_BY_NAME_COMPANY IN VARCHAR2,
180 x_END_DATE IN DATE,
181 x_SUPERVISOR_NAME IN VARCHAR2,
182 x_BRANCH IN VARCHAR2,
183 x_MILITARY_RANK IN VARCHAR2,
184 x_SERVED IN VARCHAR2,
185 x_STATION IN VARCHAR2,
186 x_RESPONSIBILITY IN VARCHAR2,
187 x_STATUS IN VARCHAR2,
188 x_OBJECT_VERSION_NUMBER IN NUMBER,
189 x_CREATED_BY_MODULE IN VARCHAR2,
190 x_APPLICATION_ID IN NUMBER,
191 x_EMPLOYED_BY_PARTY_ID IN NUMBER,
192 x_REASON_FOR_LEAVING IN VARCHAR2,
193 x_FACULTY_POSITION_FLAG IN VARCHAR2,
194 x_TENURE_CODE IN VARCHAR2,
195 x_FRACTION_OF_TENURE IN NUMBER,
196 x_EMPLOYMENT_TYPE_CODE IN VARCHAR2,
197 x_EMPLOYED_AS_TITLE_CODE IN VARCHAR2,
198 x_WEEKLY_WORK_HOURS IN NUMBER,
199 x_COMMENTS IN VARCHAR2
200 ) IS
201 BEGIN
202 Update HZ_EMPLOYMENT_HISTORY
203 SET
204 EMPLOYMENT_HISTORY_ID = decode( x_EMPLOYMENT_HISTORY_ID, NULL, EMPLOYMENT_HISTORY_ID, FND_API.G_MISS_NUM,NULL, X_EMPLOYMENT_HISTORY_ID,x_EMPLOYMENT_HISTORY_ID),
205 BEGIN_DATE = decode( x_BEGIN_DATE, NULL,BEGIN_DATE,FND_API.G_MISS_DATE,NULL,x_BEGIN_DATE),
206 PARTY_ID = decode( x_PARTY_ID, NULL,PARTY_ID,FND_API.G_MISS_NUM,NULL,x_PARTY_ID),
207 EMPLOYED_AS_TITLE = decode( x_EMPLOYED_AS_TITLE, NULL,EMPLOYED_AS_TITLE, FND_API.G_MISS_CHAR,NULL,x_EMPLOYED_AS_TITLE),
208 EMPLOYED_BY_DIVISION_NAME = decode( x_EMPLOYED_BY_DIVISION_NAME, NULL,EMPLOYED_BY_DIVISION_NAME, FND_API.G_MISS_CHAR,NULL,x_EMPLOYED_BY_DIVISION_NAME),
209 EMPLOYED_BY_NAME_COMPANY = decode( x_EMPLOYED_BY_NAME_COMPANY, NULL,EMPLOYED_BY_NAME_COMPANY,FND_API.G_MISS_CHAR,NULL,x_EMPLOYED_BY_NAME_COMPANY),
210 END_DATE = decode( x_END_DATE, NULL,END_DATE,FND_API.G_MISS_DATE,NULL,x_END_DATE),
211 SUPERVISOR_NAME = decode( x_SUPERVISOR_NAME, NULL,SUPERVISOR_NAME,FND_API.G_MISS_CHAR,NULL,x_SUPERVISOR_NAME),
212 BRANCH = decode( x_BRANCH,NULL,BRANCH, FND_API.G_MISS_CHAR,NULL, x_BRANCH),
213 MILITARY_RANK = decode( x_MILITARY_RANK, NULL,MILITARY_RANK,FND_API.G_MISS_CHAR,NULL,x_MILITARY_RANK),
214 SERVED = decode( x_SERVED, NULL,SERVED,FND_API.G_MISS_CHAR,NULL,x_SERVED),
215 STATION = decode( x_STATION, NULL,STATION,FND_API.G_MISS_CHAR,NULL,x_STATION),
216 RESPONSIBILITY = decode( x_RESPONSIBILITY, NULL,RESPONSIBILITY, FND_API.G_MISS_CHAR,NULL,x_RESPONSIBILITY),
217 STATUS = decode( x_STATUS,NULL,STATUS, FND_API.G_MISS_CHAR,NULL,x_STATUS),
218 OBJECT_VERSION_NUMBER = DECODE( x_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
219 CREATED_BY_MODULE = DECODE( x_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
220 APPLICATION_ID = DECODE( x_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID ),
221 EMPLOYED_BY_PARTY_ID = decode( x_EMPLOYED_BY_PARTY_ID, NULL,EMPLOYED_BY_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_EMPLOYED_BY_PARTY_ID),
222 REASON_FOR_LEAVING = decode( x_REASON_FOR_LEAVING, NULL,REASON_FOR_LEAVING, FND_API.G_MISS_CHAR, NULL, X_REASON_FOR_LEAVING ),
223 FACULTY_POSITION_FLAG = decode( x_FACULTY_POSITION_FLAG, NULL,FACULTY_POSITION_FLAG, FND_API.G_MISS_CHAR, NULL, X_FACULTY_POSITION_FLAG ),
224 TENURE_CODE = decode( x_TENURE_CODE, NULL,TENURE_CODE, FND_API.G_MISS_CHAR,NULL,X_TENURE_CODE ),
225 FRACTION_OF_TENURE = decode( x_FRACTION_OF_TENURE, NULL,FRACTION_OF_TENURE, FND_API.G_MISS_NUM, NULL, x_FRACTION_OF_TENURE),
226 EMPLOYMENT_TYPE_CODE = decode( x_EMPLOYMENT_TYPE_CODE, NULL, EMPLOYMENT_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, X_EMPLOYMENT_TYPE_CODE ),
227 EMPLOYED_AS_TITLE_CODE = decode( x_EMPLOYED_AS_TITLE_CODE, NULL, EMPLOYED_AS_TITLE_CODE, FND_API.G_MISS_CHAR, NULL, X_EMPLOYED_AS_TITLE_CODE ),
228 WEEKLY_WORK_HOURS = decode( x_WEEKLY_WORK_HOURS, NULL, WEEKLY_WORK_HOURS, FND_API.G_MISS_NUM, NULL, x_WEEKLY_WORK_HOURS),
229 COMMENTS = decode( x_COMMENTS, NULL, COMMENTS, FND_API.G_MISS_CHAR, NULL, X_COMMENTS ),
230 -- Bug 3032780
231 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
232 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
233 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
234 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
235 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
236 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
237 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
238 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
239 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE
240
241 where rowid = X_RowId;
242
243 If (SQL%NOTFOUND) then
244 RAISE NO_DATA_FOUND;
245 End If;
246 END Update_Row;
247
248
249
250 PROCEDURE Lock_Row(
251 x_Rowid IN VARCHAR2,
252 x_EMPLOYMENT_HISTORY_ID IN NUMBER,
253 x_BEGIN_DATE IN DATE,
254 x_PARTY_ID IN NUMBER,
255 x_EMPLOYED_AS_TITLE IN VARCHAR2,
256 x_EMPLOYED_BY_DIVISION_NAME IN VARCHAR2,
257 x_EMPLOYED_BY_NAME_COMPANY IN VARCHAR2,
258 x_END_DATE IN DATE,
259 x_SUPERVISOR_NAME IN VARCHAR2,
260 x_BRANCH IN VARCHAR2,
261 x_MILITARY_RANK IN VARCHAR2,
262 x_CREATED_BY IN NUMBER,
263 x_CREATION_DATE IN DATE,
264 x_SERVED IN VARCHAR2,
265 x_LAST_UPDATE_LOGIN IN NUMBER,
266 x_STATION IN VARCHAR2,
267 x_LAST_UPDATE_DATE IN DATE,
268 x_LAST_UPDATED_BY IN NUMBER,
269 x_REQUEST_ID IN NUMBER,
270 x_PROGRAM_APPLICATION_ID IN NUMBER,
271 x_PROGRAM_ID IN NUMBER,
272 x_PROGRAM_UPDATE_DATE IN DATE,
273 x_WH_UPDATE_DATE IN DATE,
274 x_RESPONSIBILITY IN VARCHAR2,
275 x_STATUS IN VARCHAR2,
276 x_EMPLOYED_BY_PARTY_ID IN NUMBER,
277 x_REASON_FOR_LEAVING IN VARCHAR2,
278 x_FACULTY_POSITION_FLAG IN VARCHAR2,
279 x_TENURE_CODE IN VARCHAR2,
280 x_FRACTION_OF_TENURE IN NUMBER,
281 x_EMPLOYMENT_TYPE_CODE IN VARCHAR2,
282 x_EMPLOYED_AS_TITLE_CODE IN VARCHAR2,
283 x_WEEKLY_WORK_HOURS IN NUMBER,
284 x_COMMENTS IN VARCHAR2,
285 x_APPLICATION_ID IN NUMBER
286 ) IS
287 CURSOR C IS
288 SELECT *
289 FROM HZ_EMPLOYMENT_HISTORY
290 WHERE rowid = x_Rowid
291 FOR UPDATE of EMPLOYMENT_HISTORY_ID NOWAIT;
292 Recinfo C%ROWTYPE;
293 BEGIN
294 OPEN C;
295 FETCH C INTO Recinfo;
296 If (C%NOTFOUND) then
297 CLOSE C;
298 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
299 APP_EXCEPTION.RAISE_EXCEPTION;
300 End If;
301 CLOSE C;
302 if (
303 ( ( Recinfo.EMPLOYMENT_HISTORY_ID = x_EMPLOYMENT_HISTORY_ID)
304 OR ( ( Recinfo.EMPLOYMENT_HISTORY_ID = NULL )
305 AND ( x_EMPLOYMENT_HISTORY_ID = NULL )))
306 AND ( ( Recinfo.BEGIN_DATE = x_BEGIN_DATE)
307 OR ( ( Recinfo.BEGIN_DATE = NULL )
308 AND ( x_BEGIN_DATE = NULL )))
312 AND ( ( Recinfo.EMPLOYED_AS_TITLE = x_EMPLOYED_AS_TITLE)
309 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
310 OR ( ( Recinfo.PARTY_ID = NULL )
311 AND ( x_PARTY_ID = NULL )))
313 OR ( ( Recinfo.EMPLOYED_AS_TITLE = NULL )
314 AND ( x_EMPLOYED_AS_TITLE = NULL )))
315 AND ( ( Recinfo.EMPLOYED_BY_DIVISION_NAME = x_EMPLOYED_BY_DIVISION_NAME)
316 OR ( ( Recinfo.EMPLOYED_BY_DIVISION_NAME = NULL )
317 AND ( x_EMPLOYED_BY_DIVISION_NAME = NULL )))
318 AND ( ( Recinfo.EMPLOYED_BY_NAME_COMPANY = x_EMPLOYED_BY_NAME_COMPANY)
319 OR ( ( Recinfo.EMPLOYED_BY_NAME_COMPANY = NULL )
320 AND ( x_EMPLOYED_BY_NAME_COMPANY = NULL )))
321 AND ( ( Recinfo.END_DATE = x_END_DATE)
322 OR ( ( Recinfo.END_DATE = NULL )
323 AND ( x_END_DATE = NULL )))
324 AND ( ( Recinfo.SUPERVISOR_NAME = x_SUPERVISOR_NAME)
325 OR ( ( Recinfo.SUPERVISOR_NAME = NULL )
326 AND ( x_SUPERVISOR_NAME = NULL )))
327 AND ( ( Recinfo.BRANCH = x_BRANCH)
328 OR ( ( Recinfo.BRANCH = NULL )
329 AND ( x_BRANCH = NULL )))
330 AND ( ( Recinfo.MILITARY_RANK = x_MILITARY_RANK)
331 OR ( ( Recinfo.MILITARY_RANK = NULL )
332 AND ( x_MILITARY_RANK = NULL )))
333 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
334 OR ( ( Recinfo.CREATED_BY = NULL )
335 AND ( x_CREATED_BY = NULL )))
336 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
337 OR ( ( Recinfo.CREATION_DATE = NULL )
338 AND ( x_CREATION_DATE = NULL )))
339 AND ( ( Recinfo.SERVED = x_SERVED)
340 OR ( ( Recinfo.SERVED = NULL )
341 AND ( x_SERVED = NULL )))
342 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
343 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
344 AND ( x_LAST_UPDATE_LOGIN = NULL )))
345 AND ( ( Recinfo.STATION = x_STATION)
346 OR ( ( Recinfo.STATION = NULL )
347 AND ( x_STATION = NULL )))
348 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
349 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
350 AND ( x_LAST_UPDATE_DATE = NULL )))
351 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
352 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
353 AND ( x_LAST_UPDATED_BY = NULL )))
354 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
355 OR ( ( Recinfo.REQUEST_ID = NULL )
356 AND ( x_REQUEST_ID = NULL )))
357 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
358 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
359 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
360 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
361 OR ( ( Recinfo.PROGRAM_ID = NULL )
362 AND ( x_PROGRAM_ID = NULL )))
363 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
364 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
365 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
366 AND ( ( Recinfo.WH_UPDATE_DATE = x_WH_UPDATE_DATE)
367 OR ( ( Recinfo.WH_UPDATE_DATE = NULL )
368 AND ( x_WH_UPDATE_DATE = NULL )))
369 AND ( ( Recinfo.RESPONSIBILITY = x_RESPONSIBILITY)
370 OR ( ( Recinfo.RESPONSIBILITY = NULL )
371 AND ( x_RESPONSIBILITY = NULL )))
372 AND ( ( Recinfo.STATUS = x_STATUS)
373 OR ( ( Recinfo.STATUS = NULL )
374 AND ( x_STATUS = NULL )))
375 AND ( ( Recinfo.APPLICATION_ID = x_APPLICATION_ID)
376 OR ( ( Recinfo.APPLICATION_ID = NULL )
377 AND ( x_APPLICATION_ID = NULL )))
378 AND ( ( Recinfo.EMPLOYED_BY_PARTY_ID = x_EMPLOYED_BY_PARTY_ID)
379 OR ( ( Recinfo.EMPLOYED_BY_PARTY_ID = NULL )
380 AND ( x_EMPLOYED_BY_PARTY_ID = NULL )))
381 AND ( ( Recinfo.REASON_FOR_LEAVING = x_REASON_FOR_LEAVING)
382 OR ( ( Recinfo.REASON_FOR_LEAVING = NULL )
383 AND ( x_REASON_FOR_LEAVING = NULL )))
384 AND ( ( Recinfo.FACULTY_POSITION_FLAG = x_FACULTY_POSITION_FLAG)
385 OR ( ( Recinfo.FACULTY_POSITION_FLAG = NULL )
386 AND ( x_FACULTY_POSITION_FLAG = NULL )))
387 AND ( ( Recinfo.TENURE_CODE = x_TENURE_CODE)
388 OR ( ( Recinfo.TENURE_CODE = NULL )
389 AND ( x_TENURE_CODE = NULL )))
390 AND ( ( Recinfo.FRACTION_OF_TENURE = x_FRACTION_OF_TENURE)
391 OR ( ( Recinfo.FRACTION_OF_TENURE = NULL )
392 AND ( x_FRACTION_OF_TENURE = NULL )))
393 AND ( ( Recinfo.EMPLOYMENT_TYPE_CODE = x_EMPLOYMENT_TYPE_CODE)
394 OR ( ( Recinfo.EMPLOYMENT_TYPE_CODE = NULL )
395 AND ( x_EMPLOYMENT_TYPE_CODE = NULL )))
396 AND ( ( Recinfo.EMPLOYED_AS_TITLE_CODE = x_EMPLOYED_AS_TITLE_CODE)
397 OR ( ( Recinfo.EMPLOYED_AS_TITLE_CODE = NULL )
398 AND ( x_EMPLOYED_AS_TITLE_CODE = NULL )))
399 AND ( ( Recinfo.WEEKLY_WORK_HOURS = x_WEEKLY_WORK_HOURS)
400 OR ( ( Recinfo.WEEKLY_WORK_HOURS = NULL )
401 AND ( x_WEEKLY_WORK_HOURS = NULL )))
402 AND ( ( Recinfo.COMMENTS = x_COMMENTS)
403 OR ( ( Recinfo.COMMENTS = NULL )
404 AND ( x_COMMENTS = NULL )))
405
406
407
408
409 ) then
413 APP_EXCEPTION.RAISE_EXCEPTION;
410 return;
411 else
412 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
414 End If;
415 END Lock_Row;
416
417
418 PROCEDURE Select_Row (
419 x_employment_history_id IN OUT NOCOPY NUMBER,
420 x_begin_date OUT NOCOPY DATE,
421 x_party_id OUT NOCOPY NUMBER,
422 x_employed_as_title OUT NOCOPY VARCHAR2,
423 x_employed_by_division_name OUT NOCOPY VARCHAR2,
424 x_employed_by_name_company OUT NOCOPY VARCHAR2,
425 x_end_date OUT NOCOPY DATE,
426 x_supervisor_name OUT NOCOPY VARCHAR2,
427 x_branch OUT NOCOPY VARCHAR2,
428 x_military_rank OUT NOCOPY VARCHAR2,
429 x_served OUT NOCOPY VARCHAR2,
430 x_station OUT NOCOPY VARCHAR2,
431 x_responsibility OUT NOCOPY VARCHAR2,
432 x_status OUT NOCOPY VARCHAR2,
433 x_application_id OUT NOCOPY NUMBER,
434 x_created_by_module OUT NOCOPY VARCHAR2,
435 x_reason_for_leaving OUT NOCOPY VARCHAR2,
436 x_faculty_position_flag OUT NOCOPY VARCHAR2,
437 x_tenure_code OUT NOCOPY VARCHAR2,
438 x_fraction_of_tenure OUT NOCOPY NUMBER,
439 x_employment_type_code OUT NOCOPY VARCHAR2,
440 x_employed_as_title_code OUT NOCOPY VARCHAR2,
441 x_weekly_work_hours OUT NOCOPY NUMBER,
442 x_comments OUT NOCOPY VARCHAR2
443 ) IS
444 BEGIN
445
446 SELECT
447 NVL(employment_history_id, FND_API.G_MISS_NUM),
448 NVL(begin_date, FND_API.G_MISS_DATE),
449 NVL(party_id, FND_API.G_MISS_NUM),
450 NVL(employed_as_title, FND_API.G_MISS_CHAR),
451 NVL(employed_by_division_name, FND_API.G_MISS_CHAR),
452 NVL(employed_by_name_company, FND_API.G_MISS_CHAR),
453 NVL(end_date, FND_API.G_MISS_DATE),
454 NVL(supervisor_name, FND_API.G_MISS_CHAR),
455 NVL(branch, FND_API.G_MISS_CHAR),
456 NVL(military_rank, FND_API.G_MISS_CHAR),
457 NVL(served, FND_API.G_MISS_CHAR),
458 NVL(station, FND_API.G_MISS_CHAR),
459 NVL(responsibility, FND_API.G_MISS_CHAR),
460 NVL(status, FND_API.G_MISS_CHAR),
461 NVL(application_id, FND_API.G_MISS_NUM),
462 NVL(created_by_module, FND_API.G_MISS_CHAR),
463 NVL(reason_for_leaving, FND_API.G_MISS_CHAR),
464 NVL(faculty_position_flag, FND_API.G_MISS_CHAR),
465 NVL(tenure_code, FND_API.G_MISS_CHAR),
466 NVL(fraction_of_tenure, FND_API.G_MISS_NUM),
467 NVL(employment_type_code, FND_API.G_MISS_CHAR),
468 NVL(employed_as_title_code, FND_API.G_MISS_CHAR),
469 NVL(weekly_work_hours, FND_API.G_MISS_NUM),
470 NVL(comments, FND_API.G_MISS_CHAR)
471 INTO
472 x_employment_history_id,
473 x_begin_date,
474 x_party_id,
475 x_employed_as_title,
476 x_employed_by_division_name,
477 x_employed_by_name_company,
478 x_end_date,
479 x_supervisor_name,
480 x_branch,
481 x_military_rank,
482 x_served,
483 x_station,
484 x_responsibility,
485 x_status,
486 x_application_id,
487 x_created_by_module,
488 x_reason_for_leaving,
489 x_faculty_position_flag,
490 x_tenure_code,
491 x_fraction_of_tenure,
492 x_employment_type_code,
493 x_employed_as_title_code,
494 x_weekly_work_hours,
495 x_comments
496 FROM HZ_EMPLOYMENT_HISTORY
497 WHERE employment_history_id = x_employment_history_id;
498
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
502 FND_MESSAGE.SET_TOKEN('RECORD', 'employment_history_rec');
503 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_employment_history_id));
504 FND_MSG_PUB.ADD;
505 RAISE FND_API.G_EXC_ERROR;
506
507 END Select_Row;
508
509
510 END HZ_EMPLOYMENT_HISTORY_PKG;