[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SPLACEMENTS_API
Source
1 PACKAGE BODY igs_en_splacements_api AS
2 /* $Header: IGSENB0B.pls 120.2 2005/10/02 23:47:37 appldev ship $ */
3
4 --cursor to retrieve rowid forgiven SplacementId and SupervisorId
5 Cursor cur_sp(c_splacement_id NUMBER, c_supervisor_id NUMBER) IS
6 Select rowid from IGS_EN_SPLACE_SUPS where Splacement_id=c_splacement_id
7 and supervisor_id=c_supervisor_id;
8
9
10 FUNCTION create_plcmnt_sup (
11 p_person_id IN NUMBER,
12 p_last_name IN VARCHAR2,
13 p_first_name IN VARCHAR2,
14 p_title IN VARCHAR2,
15 p_employment_history_id IN OUT NOCOPY NUMBER,
16 p_email_address IN VARCHAR2,
17 p_email_id IN NUMBER,
18 p_email_ovn IN NUMBER,
19 p_phone IN VARCHAR2,
20 p_phone_id IN NUMBER,
21 p_phone_ovn IN NUMBER,
22 p_ignore_duplicate IN VARCHAR2 ,
23 p_party_number IN OUT NOCOPY VARCHAR2,
24 p_empl_ovn IN OUT NOCOPY HZ_EMPLOYMENT_HISTORY.OBJECT_VERSION_NUMBER%TYPE) RETURN NUMBER
25 IS
26 -------------------------------------------------------------------
27 -- rvangala 28-OCT-2003 Created
28 -- Saves placement supervisor information
29 --
30 --
31 -- ssaleem 01-DEC-2003 Bug : 3223943 Added Object version Number as parameter
32 --skpandey 01-OCT-2005 Bug: 3663505
33 -- Description: Added ATTRIBUTES 21 TO 24 TO STORE ADDITIONAL INFORMATION
34 ---------------------------------------------------------------------
35
36
37 Cursor cur_c1(c_person_id IN NUMBER, c_emp_hist_id IN NUMBER) IS SELECT * FROM IGS_AD_EMP_DTL
38 WHERE person_id=c_person_id
39 AND employment_history_id=c_emp_hist_id;
40
41 Cursor cur_c2 IS SELECT *
42 FROM IGS_PE_CONTACTS_V
43 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
44 AND OWNER_TABLE_ID = P_PERSON_ID
45 AND CONTACT_POINT_ID = P_PHONE_ID
46 AND CONTACT_POINT_TYPE = 'PHONE'
47 FOR UPDATE NOWAIT;
48
49 Cursor cur_c3 IS SELECT *
50 FROM IGS_PE_CONTACTS_V
51 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
52 AND OWNER_TABLE_ID = P_PERSON_ID
53 AND CONTACT_POINT_ID = P_EMAIL_ID
54 AND CONTACT_POINT_TYPE = 'EMAIL'
55 FOR UPDATE NOWAIT;
56
57 Cursor cur_c4(c_person_id IN NUMBER) IS SELECT party_number
58 FROM HZ_PARTIES
59 WHERE HZ_PARTIES.PARTY_ID=c_person_id;
60
61 l_profile_id VARCHAR2(60) :=NULL;
62 l_dup_found VARCHAR2(1) :=NULL;
63 l_dup_where_clause VARCHAR2(2000) :=NULL;
64 l_partial VARCHAR2(1) :=NULL;
65 l_rowid VARCHAR2(25) :=NULL;
66 l_msg_data VARCHAR2(2000) :=NULL;
67 l_msg_count NUMBER :=NULL;
68 l_return_status VARCHAR2(10) :=NULL;
69 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE :=P_PERSON_ID;
70 l_person_number IGS_PE_PERSON.PERSON_NUMBER%TYPE :=NULL;
71 l_hz_parties_ovn NUMBER(10);
72 l_last_update_date HZ_CONTACT_POINTS.LAST_UPDATE_DATE%TYPE :=NULL;
73 l_contact_point_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE :=NULL;
74 l_contact_point_ovn HZ_CONTACT_POINTS.OBJECT_VERSION_NUMBER%TYPE :=NULL;
75 l_phone HZ_CONTACT_POINTS.PHONE_NUMBER%TYPE := P_PHONE;
76 l_phone_id HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE := P_PHONE_ID;
77 l_phone_ovn HZ_CONTACT_POINTS.OBJECT_VERSION_NUMBER%TYPE := P_PHONE_OVN;
78 l_email_address IGS_PE_CONTACTS_V.EMAIL_ADDRESS%TYPE := P_EMAIL_ADDRESS;
79 l_email_id IGS_PE_CONTACTS_V.EMAIL_ADDRESS%TYPE := P_EMAIL_ID;
80 l_email_ovn HZ_CONTACT_POINTS.OBJECT_VERSION_NUMBER%TYPE := P_EMAIL_OVN;
81 l_employment_history_id IGS_AD_EMP_DTL.EMPLOYMENT_HISTORY_ID%TYPE := P_EMPLOYMENT_HISTORY_ID;
82
83 l_emp_obj_ver_no IGS_AD_EMP_DTL.OBJECT_VERSION_NUMBER%TYPE;
84 v_emp_hist_rec cur_c1%ROWTYPE;
85 v_phone_update_rec cur_c2%ROWTYPE;
86 v_email_update_rec cur_c3%ROWTYPE;
87 BEGIN
88
89 IF P_PERSON_ID IS NULL AND P_LAST_NAME IS NOT NULL THEN
90 IF NVL(P_IGNORE_DUPLICATE,'N') = 'N' THEN
91 l_profile_id := FND_PROFILE.VALUE('IGS_PE_DUP_MATCH_CRITERIA');
92
93 IGS_PE_DUP_PERSON.FIND_DUPLICATES(
94 X_MATCH_SET_ID => l_profile_id,
95 X_SURNAME => P_LAST_NAME,
96 X_GIVEN_NAMES => P_FIRST_NAME,
97 X_BIRTH_DT => NULL,
98 X_SEX => NULL,
99 X_DUP_FOUND => l_dup_found,
100 X_WHERE_CLAUSE => l_dup_where_clause,
101 X_EXACT_PARTIAL => l_partial,
102 X_PREF_ALTERNATE_ID => NULL
103 );
104
105 IF l_dup_found = 'Y' THEN
106 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PLCMNT_SUP_DUP');
107 FND_MESSAGE.SET_TOKEN('PERSON_NAME',P_LAST_NAME || ', ' || P_FIRST_NAME);
108 IGS_GE_MSG_STACK.ADD;
109 APP_EXCEPTION.RAISE_EXCEPTION;
110 END IF;
111 END IF;
112
113 IGS_PE_PERSON_PKG.INSERT_ROW(
114 X_ROWID => l_rowid,
115 X_MSG_DATA => L_MSG_DATA,
116 X_MSG_COUNT => L_MSG_COUNT,
117 X_RETURN_STATUS => L_RETURN_STATUS,
118 X_PERSON_ID => L_PERSON_ID,
119 X_PERSON_NUMBER => L_PERSON_NUMBER,
120 X_SURNAME => P_LAST_NAME,
121 X_MIDDLE_NAME => NULL,
122 X_GIVEN_NAMES => P_FIRST_NAME,
123 X_SEX => NULL,
124 X_TITLE => NULL,
125 X_STAFF_MEMBER_IND => NULL,
126 X_DECEASED_IND => NULL,
127 X_SUFFIX => NULL,
128 X_PRE_NAME_ADJUNCT => NULL,
129 X_ARCHIVE_EXCLUSION_IND=> NULL,
130 X_ARCHIVE_DT => NULL,
131 X_PURGE_EXCLUSION_IND => NULL,
132 X_PURGE_DT => NULL,
133 X_DECEASED_DATE => NULL,
134 X_PROOF_OF_INS => NULL,
135 X_PROOF_OF_IMMU => NULL,
136 X_BIRTH_DT => NULL,
137 X_SALUTATION => NULL,
138 X_ORACLE_USERNAME => NULL,
139 X_PREFERRED_GIVEN_NAME => NULL,
140 X_EMAIL_ADDR => NULL,
141 X_LEVEL_OF_QUAL_ID => NULL,
142 X_MILITARY_SERVICE_REG => NULL,
143 X_VETERAN => NULL,
144 X_HZ_PARTIES_OVN => l_hz_parties_ovn,
145 X_ATTRIBUTE_CATEGORY => NULL,
146 X_ATTRIBUTE1 => NULL,
147 X_ATTRIBUTE2 => NULL,
148 X_ATTRIBUTE3 => NULL,
149 X_ATTRIBUTE4 => NULL,
150 X_ATTRIBUTE5 => NULL,
151 X_ATTRIBUTE6 => NULL,
152 X_ATTRIBUTE7 => NULL,
153 X_ATTRIBUTE8 => NULL,
154 X_ATTRIBUTE9 => NULL,
155 X_ATTRIBUTE10 => NULL,
156 X_ATTRIBUTE11 => NULL,
157 X_ATTRIBUTE12 => NULL,
158 X_ATTRIBUTE13 => NULL,
159 X_ATTRIBUTE14 => NULL,
160 X_ATTRIBUTE15 => NULL,
161 X_ATTRIBUTE16 => NULL,
162 X_ATTRIBUTE17 => NULL,
163 X_ATTRIBUTE18 => NULL,
164 X_ATTRIBUTE19 => NULL,
165 X_ATTRIBUTE20 => NULL,
166 X_PERSON_ID_TYPE => NULL,
167 X_API_PERSON_ID => NULL,
168 X_MODE => 'S',
169 X_ATTRIBUTE21 => NULL,
170 X_ATTRIBUTE22 => NULL,
171 X_ATTRIBUTE23 => NULL,
172 X_ATTRIBUTE24 => NULL);
173
174 IF l_return_status IN ('E','U') THEN
175 FND_MESSAGE.SET_ENCODED(l_msg_data);
176 APP_EXCEPTION.RAISE_EXCEPTION;
177 END IF;
178
179 END IF;
180
181 -- if phone number is not null but phone_id is null, new entry insert
182 IF P_PHONE IS NOT NULL AND P_PHONE_ID IS NULL THEN
183 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP(
184 P_ACTION => 'INSERT',
185 P_ROWID => l_rowid,
186 P_STATUS => 'A',
187 P_OWNER_TABLE_NAME => 'HZ_PARTIES',
188 P_OWNER_TABLE_ID => L_PERSON_ID,
189 P_PRIMARY_FLAG => 'Y',
190 P_PHONE_COUNTRY_CODE => NULL,
191 P_PHONE_AREA_CODE => NULL,
192 P_PHONE_NUMBER => P_PHONE,
193 P_PHONE_EXTENSION => NULL,
194 P_PHONE_LINE_TYPE => 'GEN',
195 P_RETURN_STATUS => l_return_status,
196 P_MSG_DATA => l_msg_data,
197 P_LAST_UPDATE_DATE => l_last_update_date,
198 P_CONTACT_POINT_ID => L_CONTACT_POINT_ID,
199 P_CONTACT_POINT_OVN => L_CONTACT_POINT_OVN,
200 P_ATTRIBUTE_CATEGORY => NULL,
201 P_ATTRIBUTE1 => NULL,
202 P_ATTRIBUTE2 => NULL,
203 P_ATTRIBUTE3 => NULL,
204 P_ATTRIBUTE4 => NULL,
205 P_ATTRIBUTE5 => NULL,
206 P_ATTRIBUTE6 => NULL,
207 P_ATTRIBUTE7 => NULL,
208 P_ATTRIBUTE8 => NULL,
209 P_ATTRIBUTE9 => NULL,
210 P_ATTRIBUTE10 => NULL,
211 P_ATTRIBUTE11 => NULL,
212 P_ATTRIBUTE12 => NULL,
213 P_ATTRIBUTE13 => NULL,
214 P_ATTRIBUTE14 => NULL,
215 P_ATTRIBUTE15 => NULL,
216 P_ATTRIBUTE16 => NULL,
217 P_ATTRIBUTE17 => NULL,
218 P_ATTRIBUTE18 => NULL,
219 P_ATTRIBUTE19 => NULL,
220 P_ATTRIBUTE20 => NULL);
221
222 ELSE -- if phone id is not null, it is an existing record, so update
223
224 --Select all the details of the PHONE record using a cursor based on the PERSON_ID, OBJECT VERSION NUMBER AND PHONE_ID
225 OPEN cur_c2;
226 FETCH cur_c2 INTO v_phone_update_rec;
227
228 IF cur_c2%FOUND THEN
229 IF v_phone_update_rec.OBJECT_VERSION_NUMBER <> P_PHONE_OVN THEN
230 -- the record was updated by some other user hence the object version number is not matching.
231 CLOSE cur_c2;
232 FND_MESSAGE.SET_NAME ('FND',' FORM_RECORD_DELETED');
233 IGS_GE_MSG_STACK.ADD;
234 APP_EXCEPTION.RAISE_EXCEPTION;
235 END IF;
236 END IF;
237
238
239 --Call the IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP with the action as updated
240 IF cur_c2%FOUND AND (nvl(v_phone_update_rec.phone_number,'null')<>nvl(p_phone,'null')) THEN
241 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP (
242 P_ACTION =>'UPDATE',
243 P_ROWID => V_PHONE_UPDATE_REC.ROW_ID,
244 P_STATUS => 'A',
245 P_OWNER_TABLE_NAME => 'HZ_PARTIES',
246 P_OWNER_TABLE_ID => P_PERSON_ID,
247 P_PRIMARY_FLAG => 'Y',
248 P_PHONE_COUNTRY_CODE => V_PHONE_UPDATE_REC.PHONE_COUNTRY_CODE,
249 P_PHONE_AREA_CODE => V_PHONE_UPDATE_REC.PHONE_AREA_CODE,
250 P_PHONE_NUMBER => P_PHONE,
251 P_PHONE_EXTENSION => V_PHONE_UPDATE_REC.PHONE_EXTENSION,
252 P_PHONE_LINE_TYPE => V_PHONE_UPDATE_REC.PHONE_LINE_TYPE,
253 P_RETURN_STATUS => l_return_status,
254 P_MSG_DATA => l_msg_data,
255 P_LAST_UPDATE_DATE => V_PHONE_UPDATE_REC.LAST_UPDATE_DATE,
256 P_CONTACT_POINT_ID => l_phone_id,
257 P_CONTACT_POINT_OVN => l_phone_ovn,
258 P_ATTRIBUTE_CATEGORY=> V_PHONE_UPDATE_REC. ATTRIBUTE_CATEGORY,
259 P_ATTRIBUTE1 => V_PHONE_UPDATE_REC.ATTRIBUTE1,
260 P_ATTRIBUTE2 => V_PHONE_UPDATE_REC.ATTRIBUTE2,
261 P_ATTRIBUTE3 => V_PHONE_UPDATE_REC.ATTRIBUTE3,
262 P_ATTRIBUTE4 => V_PHONE_UPDATE_REC.ATTRIBUTE4,
263 P_ATTRIBUTE5 => V_PHONE_UPDATE_REC.ATTRIBUTE5,
264 P_ATTRIBUTE6 => V_PHONE_UPDATE_REC.ATTRIBUTE6,
265 P_ATTRIBUTE7 => V_PHONE_UPDATE_REC.ATTRIBUTE7,
266 P_ATTRIBUTE8 => V_PHONE_UPDATE_REC.ATTRIBUTE8,
267 P_ATTRIBUTE9 => V_PHONE_UPDATE_REC.ATTRIBUTE9,
268 P_ATTRIBUTE10 => V_PHONE_UPDATE_REC.ATTRIBUTE10,
269 P_ATTRIBUTE12 => V_PHONE_UPDATE_REC.ATTRIBUTE12,
270 P_ATTRIBUTE13 => V_PHONE_UPDATE_REC.ATTRIBUTE13,
271 P_ATTRIBUTE14 => V_PHONE_UPDATE_REC.ATTRIBUTE14,
272 P_ATTRIBUTE15 => V_PHONE_UPDATE_REC.ATTRIBUTE15,
273 P_ATTRIBUTE16 => V_PHONE_UPDATE_REC.ATTRIBUTE16,
274 P_ATTRIBUTE17 => V_PHONE_UPDATE_REC.ATTRIBUTE17,
275 P_ATTRIBUTE18 => V_PHONE_UPDATE_REC.ATTRIBUTE18,
276 P_ATTRIBUTE19 => V_PHONE_UPDATE_REC.ATTRIBUTE19,
277 P_ATTRIBUTE20 => V_PHONE_UPDATE_REC.ATTRIBUTE20);
278 END IF;
279 CLOSE cur_c2;
280
281 IF l_return_status IN ('E', 'U') THEN
282 FND_MESSAGE.SET_ENCODED (l_msg_data);
283 APP_EXCEPTION.RAISE_EXCEPTION;
284 END IF;
285 END IF;
286
287 -- if email address is not null but email id is null, new record - insert
288 IF P_EMAIL_ADDRESS IS NOT NULL AND P_EMAIL_ID IS NULL THEN
289
290 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
291 P_ACTION =>'INSERT',
292 P_ROWID => l_rowid,
293 P_STATUS => 'A',
294 P_OWNER_TABLE_NAME => 'HZ_PARTIES',
295 P_OWNER_TABLE_ID => L_PERSON_ID,
296 P_PRIMARY_FLAG => 'Y',
297 P_EMAIL_FORMAT => 'MAILTEXT',
298 P_EMAIL_ADDRESS => P_EMAIL_ADDRESS,
299 P_RETURN_STATUS => l_return_status,
300 P_MSG_DATA => l_msg_data,
301 P_LAST_UPDATE_DATE => L_LAST_UPDATE_DATE,
302 P_CONTACT_POINT_ID => l_email_id,
303 P_CONTACT_POINT_OVN => L_CONTACT_POINT_OVN,
304 P_ATTRIBUTE_CATEGORY=> NULL,
305 P_ATTRIBUTE1 => NULL,
306 P_ATTRIBUTE2 => NULL,
307 P_ATTRIBUTE3 => NULL,
308 P_ATTRIBUTE4 => NULL,
309 P_ATTRIBUTE5 => NULL,
310 P_ATTRIBUTE6 => NULL,
311 P_ATTRIBUTE7 => NULL,
312 P_ATTRIBUTE8 => NULL,
313 P_ATTRIBUTE9 => NULL,
314 P_ATTRIBUTE10 => NULL,
315 P_ATTRIBUTE11 => NULL,
316 P_ATTRIBUTE12 => NULL,
317 P_ATTRIBUTE13 => NULL,
318 P_ATTRIBUTE14 => NULL,
319 P_ATTRIBUTE15 => NULL,
320 P_ATTRIBUTE16 => NULL,
321 P_ATTRIBUTE17 => NULL,
322 P_ATTRIBUTE18 => NULL,
323 P_ATTRIBUTE19 => NULL,
324 P_ATTRIBUTE20 => NULL);
325
326 ELSE --if email id is not null, existing record - so update email details
327
328 --Select all the details of the email record using a cursor based on the PERSON_ID, OBJECT VERSION NUMBER AND EMAIL_ID
329 OPEN cur_c3;
330 FETCH cur_c3 INTO v_email_update_rec;
331
332 IF cur_c3%FOUND THEN
333 IF V_EMAIL_UPDATE_REC.OBJECT_VERSION_NUMBER <> l_EMAIL_OVN THEN
334 -- the record was updated by some other user hence the object version number is not matching.
335 CLOSE cur_c3;
336 FND_MESSAGE.SET_NAME('FND',' FORM_RECORD_DELETED');
337 IGS_GE_MSG_STACK.ADD;
338 APP_EXCEPTION.RAISE_EXCEPTION;
339 END IF;
340 END IF;
341
342 IF p_email_address IS NULL THEN
343 l_email_address:=' ';
344 ELSE
345 l_email_address:=p_email_address;
346 END IF;
347
348 --Call the IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE with the action as updated
349 IF cur_c3%FOUND AND (nvl(v_email_update_rec.email_address,'null')<>nvl(l_email_address,'null')) THEN
350 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
351 P_ACTION => 'UPDATE',
352 P_ROWID => V_EMAIL_UPDATE_REC.ROW_ID,
353 P_STATUS => 'A',
354 P_OWNER_TABLE_NAME => 'HZ_PARTIES',
355 P_OWNER_TABLE_ID => P_PERSON_ID,
356 P_PRIMARY_FLAG => 'Y',
357 P_EMAIL_FORMAT => V_EMAIL_UPDATE_REC.EMAIL_FORMAT,
358 P_EMAIL_ADDRESS => l_EMAIL_ADDRESS,
359 P_RETURN_STATUS => l_RETURN_STATUS,
360 P_MSG_DATA => l_MSG_DATA,
361 P_LAST_UPDATE_DATE => V_EMAIL_UPDATE_REC.LAST_UPDATE_DATE,
362 P_CONTACT_POINT_ID => L_EMAIL_ID,
363 P_CONTACT_POINT_OVN => L_EMAIL_OVN,
364 P_ATTRIBUTE_CATEGORY=> V_EMAIL_UPDATE_REC.ATTRIBUTE_CATEGORY,
365 P_ATTRIBUTE1 => V_EMAIL_UPDATE_REC.ATTRIBUTE1,
366 P_ATTRIBUTE2 => V_EMAIL_UPDATE_REC.ATTRIBUTE2,
367 P_ATTRIBUTE3 => V_EMAIL_UPDATE_REC.ATTRIBUTE3,
368 P_ATTRIBUTE4 => V_EMAIL_UPDATE_REC.ATTRIBUTE4,
369 P_ATTRIBUTE5 => V_EMAIL_UPDATE_REC.ATTRIBUTE5,
370 P_ATTRIBUTE6 => V_EMAIL_UPDATE_REC.ATTRIBUTE6,
371 P_ATTRIBUTE7 => V_EMAIL_UPDATE_REC.ATTRIBUTE7,
372 P_ATTRIBUTE8 => V_EMAIL_UPDATE_REC.ATTRIBUTE8,
373 P_ATTRIBUTE9 => V_EMAIL_UPDATE_REC.ATTRIBUTE9,
374 P_ATTRIBUTE10 => V_EMAIL_UPDATE_REC.ATTRIBUTE10,
375 P_ATTRIBUTE12 => V_EMAIL_UPDATE_REC.ATTRIBUTE12,
376 P_ATTRIBUTE13 => V_EMAIL_UPDATE_REC.ATTRIBUTE13,
377 P_ATTRIBUTE14 => V_EMAIL_UPDATE_REC.ATTRIBUTE14,
378 P_ATTRIBUTE15 => V_EMAIL_UPDATE_REC.ATTRIBUTE15,
379 P_ATTRIBUTE16 => V_EMAIL_UPDATE_REC.ATTRIBUTE16,
380 P_ATTRIBUTE17 => V_EMAIL_UPDATE_REC.ATTRIBUTE17,
381 P_ATTRIBUTE18 => V_EMAIL_UPDATE_REC.ATTRIBUTE18,
382 P_ATTRIBUTE19 => V_EMAIL_UPDATE_REC.ATTRIBUTE19,
383 P_ATTRIBUTE20 => V_EMAIL_UPDATE_REC.ATTRIBUTE20);
384 END IF;
385 CLOSE cur_c3;
386
387 -- CHECK FOR THE V_RETURN_STATUS
388 IF l_RETURN_STATUS IN ( 'E' , 'U' ) THEN
389 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
390 APP_EXCEPTION.RAISE_EXCEPTION;
391 END IF;
392
393 END IF;
394
395
396 IF P_TITLE IS NOT NULL THEN
397
398 OPEN cur_c1(l_person_id,p_employment_history_id);
399 FETCH cur_c1 INTO v_emp_hist_rec;
400
401 IF cur_c1%FOUND THEN
402 IGS_AD_EMP_DTL_PKG.UPDATE_ROW (
403 X_ROWID => V_EMP_HIST_REC.ROW_ID,
404 X_EMPLOYMENT_HISTORY_ID => V_EMP_HIST_REC.EMPLOYMENT_HISTORY_ID,
405 X_PERSON_ID => l_PERSON_ID,
406 X_START_DT => V_EMP_HIST_REC.START_DT,
407 X_END_DT => V_EMP_HIST_REC.END_DT,
408 X_TYPE_OF_EMPLOYMENT => V_EMP_HIST_REC.TYPE_OF_EMPLOYMENT,
409 X_FRACTION_OF_EMPLOYMENT => V_EMP_HIST_REC.FRACTION_OF_EMPLOYMENT,
410 X_TENURE_OF_EMPLOYMENT => V_EMP_HIST_REC.TENURE_OF_EMPLOYMENT,
411 X_POSITION => p_title,
412 X_OCCUPATIONAL_TITLE_CODE => V_EMP_HIST_REC.OCCUPATIONAL_TITLE_CODE,
413 X_OCCUPATIONAL_TITLE => V_EMP_HIST_REC.OCCUPATIONAL_TITLE,
414 X_WEEKLY_WORK_HOURS => V_EMP_HIST_REC.WEEKLY_WORK_HOURS,
415 X_COMMENTS => V_EMP_HIST_REC.COMMENTS,
416 X_EMPLOYER => V_EMP_HIST_REC.EMPLOYER,
417 X_EMPLOYED_BY_DIVISION_NAME => V_EMP_HIST_REC.EMPLOYED_BY_DIVISION_NAME,
418 X_BRANCH => V_EMP_HIST_REC.BRANCH,
419 X_MILITARY_RANK => V_EMP_HIST_REC.MILITARY_RANK,
420 X_SERVED => V_EMP_HIST_REC.SERVED,
421 X_STATION => V_EMP_HIST_REC.STATION,
422 X_CONTACT => V_EMP_HIST_REC.CONTACT,
423 X_MSG_DATA => L_MSG_DATA,
424 X_RETURN_STATUS => L_RETURN_STATUS,
425 X_OBJECT_VERSION_NUMBER => p_empl_ovn,
426 X_EMPLOYED_BY_PARTY_ID => V_EMP_HIST_REC.EMPLOYED_BY_PARTY_ID,
427 X_REASON_FOR_LEAVING => V_EMP_HIST_REC.REASON_FOR_LEAVING
428 );
429 p_employment_history_id :=v_emp_hist_rec.EMPLOYMENT_HISTORY_ID;
430 CLOSE cur_c1;
431
432 ELSE
433 IGS_AD_EMP_DTL_PKG.INSERT_ROW (
434 X_ROWID => L_ROWID,
435 X_EMPLOYMENT_HISTORY_ID => l_EMPLOYMENT_HISTORY_ID,
436 X_PERSON_ID => l_PERSON_ID,
437 X_START_DT => SYSDATE,
438 X_END_DT => NULL,
439 X_TYPE_OF_EMPLOYMENT => NULL,
440 X_FRACTION_OF_EMPLOYMENT => NULL,
441 X_TENURE_OF_EMPLOYMENT => NULL,
442 X_POSITION => P_TITLE,
443 X_OCCUPATIONAL_TITLE_CODE => NULL,
444 X_OCCUPATIONAL_TITLE => NULL,
445 X_WEEKLY_WORK_HOURS => NULL,
446 X_COMMENTS => NULL,
447 X_EMPLOYER => NULL,
448 X_EMPLOYED_BY_DIVISION_NAME => NULL,
449 X_BRANCH => NULL,
450 X_MILITARY_RANK => NULL,
451 X_SERVED => NULL,
452 X_STATION => NULL,
453 X_CONTACT => NULL,
454 X_MSG_DATA => L_MSG_DATA,
455 X_RETURN_STATUS => L_RETURN_STATUS,
456 X_OBJECT_VERSION_NUMBER => L_EMP_OBJ_VER_NO,
457 X_EMPLOYED_BY_PARTY_ID => NULL,
458 X_REASON_FOR_LEAVING => NULL
459 );
460 p_employment_history_id :=l_employment_history_id;
461
462 CLOSE cur_c1;
463 IF L_RETURN_STATUS IN ('E','U') THEN
464 FND_MESSAGE.SET_ENCODED(L_MSG_DATA);
465 APP_EXCEPTION.RAISE_EXCEPTION;
466 END IF;
467
468 END IF;
469 END IF;
470
471 IF (p_party_number IS NULL and l_person_id IS NOT NULL) THEN
472 OPEN cur_c4(l_person_id);
473 FETCH cur_c4 INTO p_party_number;
474 CLOSE cur_c4;
475 END IF;
476
477 RETURN l_person_id;
478 END create_plcmnt_sup;
479
480 FUNCTION get_splacement_id RETURN NUMBER
481 IS
482 -------------------------------------------------------------------
483 -- rvangala 28-OCT-2003 Created
484 -- Gets splacement_id from igs_en_splacements_s sequence
485
486
487 ---------------------------------------------------------------------
488 Cursor cur_s1 IS Select IGS_EN_Splacements_S.nextVal from dual;
489 l_splacement_id NUMBER(15);
490 BEGIN
491 OPEN cur_s1;
492 FETCH cur_s1 INTO l_splacement_id;
493 CLOSE cur_s1;
494 RETURN l_splacement_id;
495
496 END get_splacement_id;
497
498 PROCEDURE delete_supervisor_info(p_splacement_id IN NUMBER, p_supervisor_id IN NUMBER)
499 IS
500 /*--------------------------------------------------------------
501 -- rvangala 01-NOV-2003 Created
502 -- Deletes supervisor from IGS_EN_SPLACE_SUPS
503 --
504 --
505 ----------------------------------------------------------------
506 */
507 l_row_id VARCHAR2(30);
508 BEGIN
509 OPEN cur_sp(p_splacement_id,p_supervisor_id);
510 FETCH cur_sp INTO l_row_id;
511 IF cur_sp%FOUND THEN
512 igs_en_splace_sups_pkg.DELETE_ROW(l_row_id);
513 END IF;
514 CLOSE cur_sp;
515 END delete_supervisor_info;
516
517 /*--------------------------------------------------------------
518 -- rvangala 01-NOV-2003 Created
519 -- Processes creation/updation of placement information
520 -- Returns PersonId, which is new Id for a new person
521 -- and old Id for an existing person
522 --
523 --ssaleem 01-DEC-2003 Bug : 3223943 Added Object version Number as parameter
524 ----------------------------------------------------------------
525 */
526 FUNCTION process_supervisor_info(p_splacement_id IN NUMBER,
527 p_person_id IN NUMBER,
528 p_last_name IN VARCHAR2,
529 p_first_name IN VARCHAR2,
530 p_title IN VARCHAR2,
531 p_employment_history_id IN OUT NOCOPY NUMBER,
532 p_email_address IN VARCHAR2,
533 p_email_id IN NUMBER,
534 p_email_ovn IN NUMBER,
535 p_phone IN VARCHAR2,
536 p_phone_id IN NUMBER,
537 p_phone_ovn IN NUMBER,
538 p_ignore_duplicate IN VARCHAR2,
539 p_party_number IN OUT NOCOPY VARCHAR2,
540 p_object_version_number IN OUT NOCOPY HZ_EMPLOYMENT_HISTORY.OBJECT_VERSION_NUMBER%TYPE) RETURN NUMBER
541 IS
542 l_rowid VARCHAR2(25);
543 l_mode VARCHAR2(1) :='R';
544 l_person_id NUMBER(15);
545 BEGIN
546
547 l_person_id := create_plcmnt_sup (p_person_id => p_person_id,
548 p_last_name => p_last_name,
549 p_first_name => p_first_name,
550 p_title => p_title,
551 p_employment_history_id => p_employment_history_id,
552 p_email_address => p_email_address,
553 p_email_id => p_email_id,
554 p_email_ovn => p_email_ovn,
555 p_phone => p_phone,
556 p_phone_id => p_phone_id,
557 p_phone_ovn => p_phone_ovn,
558 p_ignore_duplicate => p_ignore_duplicate,
559 p_party_number => p_party_number,
560 p_empl_ovn => p_object_version_number);
561
562 OPEN cur_sp(p_splacement_id,p_person_id);
563 FETCH cur_sp INTO l_rowid;
564
565 IF cur_sp%NOTFOUND THEN
566 igs_en_splace_sups_pkg.INSERT_ROW(
567 x_rowid => l_rowid,
568 x_splacement_id => p_splacement_id,
569 x_supervisor_id => l_person_id,
570 x_mode => l_mode);
571 END IF;
572 CLOSE cur_sp;
573 RETURN l_person_id;
574 END process_supervisor_info;
575
576
577 END igs_en_splacements_api;