1 PACKAGE BODY PER_PEOPLE13_PKG AS
2 /* $Header: peper13t.pkb 120.1 2005/06/15 05:41:39 bshukla noship $ */
3 --
4 procedure lock_row1(p_rowid VARCHAR2
5 ,p_person_id NUMBER
6 ,p_effective_start_date DATE
7 ,p_effective_end_date DATE
8 ,p_business_group_id NUMBER
9 ,p_person_type_id NUMBER
10 ,p_last_name VARCHAR2
11 ,p_start_date DATE
12 ,p_applicant_number VARCHAR2
13 ,p_comment_id NUMBER
14 ,p_current_applicant_flag VARCHAR2
15 ,p_current_emp_or_apl_flag VARCHAR2
16 ,p_current_employee_flag VARCHAR2
17 ,p_date_employee_data_verified DATE
18 ,p_date_of_birth DATE
19 ,p_email_address VARCHAR2
20 ,p_employee_number VARCHAR2
21 ,p_expense_check_send_to_addr VARCHAR2
22 ,p_first_name VARCHAR2
23 ,p_full_name VARCHAR2
24 ,p_known_as VARCHAR2
25 ,p_marital_status VARCHAR2
26 ,p_middle_names VARCHAR2
27 ,p_nationality VARCHAR2
28 ,p_national_identifier VARCHAR2
29 ,p_previous_last_name VARCHAR2
30 ,p_registered_disabled_flag VARCHAR2
31 ,p_sex VARCHAR2
32 ,p_title VARCHAR2
33 ,p_suffix VARCHAR2
34 ,p_vendor_id NUMBER
35 ,p_work_telephone VARCHAR2
36 ,p_a_cat VARCHAR2
37 ,p_a1 VARCHAR2
38 ,p_a2 VARCHAR2
39 ,p_a3 VARCHAR2
40 ,p_a4 VARCHAR2
41 ,p_a5 VARCHAR2
42 ,p_a6 VARCHAR2
43 ,p_a7 VARCHAR2
44 ,p_a8 VARCHAR2
45 ,p_a9 VARCHAR2
46 ,p_a10 VARCHAR2
47 ,p_a11 VARCHAR2
48 ,p_a12 VARCHAR2
49 ,p_a13 VARCHAR2
50 ,p_a14 VARCHAR2
51 ,p_a15 VARCHAR2
52 ,p_a16 VARCHAR2
53 ,p_a17 VARCHAR2
54 ,p_a18 VARCHAR2
55 ,p_a19 VARCHAR2
56 ,p_a20 VARCHAR2
57 ,p_a21 VARCHAR2
58 ,p_a22 VARCHAR2
59 ,p_a23 VARCHAR2
60 ,p_a24 VARCHAR2
61 ,p_a25 VARCHAR2
62 ,p_a26 VARCHAR2
63 ,p_a27 VARCHAR2
64 ,p_a28 VARCHAR2
65 ,p_a29 VARCHAR2
66 ,p_a30 VARCHAR2
67 ,p_i_cat VARCHAR2
68 ,p_i1 VARCHAR2
69 ,p_i2 VARCHAR2
70 ,p_i3 VARCHAR2
71 ,p_i4 VARCHAR2
72 ,p_i5 VARCHAR2
73 ,p_i6 VARCHAR2
74 ,p_i7 VARCHAR2
75 ,p_i8 VARCHAR2
76 ,p_i9 VARCHAR2
77 ,p_i10 VARCHAR2
78 ,p_i11 VARCHAR2
79 ,p_i12 VARCHAR2
80 ,p_i13 VARCHAR2
81 ,p_i14 VARCHAR2
82 ,p_i15 VARCHAR2
83 ,p_i16 VARCHAR2
84 ,p_i17 VARCHAR2
85 ,p_i18 VARCHAR2
86 ,p_i19 VARCHAR2
87 ,p_i20 VARCHAR2
88 ,p_i21 VARCHAR2
89 ,p_i22 VARCHAR2
90 ,p_i23 VARCHAR2
91 ,p_i24 VARCHAR2
92 ,p_i25 VARCHAR2
93 ,p_i26 VARCHAR2
94 ,p_i27 VARCHAR2
95 ,p_i28 VARCHAR2
96 ,p_i29 VARCHAR2
97 ,p_i30 VARCHAR2
98 ,p_work_schedule VARCHAR2
99 ,p_correspondence_language VARCHAR2
100 ,p_student_status VARCHAR2
101 ,p_fte_capacity NUMBER
102 ,p_on_military_service VARCHAR2
103 ,p_second_passport_exists VARCHAR2
104 ,p_background_check_status VARCHAR2
105 ,p_background_date_check DATE
106 ,p_blood_type VARCHAR2
107 ,p_last_medical_test_date DATE
108 ,p_last_medical_test_by VARCHAR2
109 ,p_rehire_recommendation VARCHAR2
110 ,p_rehire_reason VARCHAR2
111 ,p_resume_exists VARCHAR2
112 ,p_resume_last_updated DATE
113 ,p_office_number VARCHAR2
114 ,p_internal_location VARCHAR2
115 ,p_mailstop VARCHAR2
116 ,p_honors VARCHAR2
117 ,p_pre_name_adjunct VARCHAR2
118 ,p_hold_applicant_date_until DATE
119 ,p_benefit_group_id NUMBER
120 ,p_receipt_of_death_cert_date DATE
121 ,p_coord_ben_med_pln_no VARCHAR2
122 ,p_coord_ben_no_cvg_flag VARCHAR2
123 ,p_uses_tobacco_flag VARCHAR2
124 ,p_dpdnt_adoption_date DATE
125 ,p_dpdnt_vlntry_svce_flag VARCHAR2
126 ,p_date_of_death DATE
127 ,p_original_date_of_hire DATE
128 ,p_period_of_service_id NUMBER
129 ,p_town_of_birth VARCHAR2
130 ,p_region_of_birth VARCHAR2
131 ,p_country_of_birth VARCHAR2
132 ,p_global_person_id VARCHAR2
133 ,p_npw_number VARCHAR2
134 ,p_current_npw_flag VARCHAR2
135 ) is
136 --
137 -- Define cursor.
138 --
139 cursor per is select *
140 from per_people_v
141 where row_id = chartorowid(p_rowid);
142 --
143 cursor per1 is select *
144 from per_people_f
145 where rowid = chartorowid(p_rowid)
146 for update nowait;
147 --
148 -- Local variables.
149 --
150 per_rec per%rowtype;
151 per_rec1 per1%rowtype;
152 --
153 begin
154 open per;
155 fetch per into per_rec;
156 close per;
157 --
158 -- Fix to ensure column values are rtrim before
159 -- comparison (as forms truncates all char fields for trailing spaces.)
160 --
161 per_rec.last_name := rtrim(per_rec.last_name);
162 per_rec.applicant_number := rtrim(per_rec.applicant_number);
163 per_rec.current_emp_or_apl_flag := rtrim(per_rec.current_emp_or_apl_flag);
164 per_rec.expense_check_send_to_address :=
165 rtrim(per_rec.expense_check_send_to_address);
166 per_rec.known_as := rtrim(per_rec.known_as);
167 per_rec.per_information11 := rtrim(per_rec.per_information11);
168 per_rec.per_information16 := rtrim(per_rec.per_information16);
169 per_rec.registered_disabled_flag := rtrim(per_rec.registered_disabled_flag);
170 per_rec.attribute_category := rtrim(per_rec.attribute_category);
171 per_rec.attribute3 := rtrim(per_rec.attribute3);
172 per_rec.attribute6 := rtrim(per_rec.attribute6);
173 per_rec.attribute9 := rtrim(per_rec.attribute9);
174 per_rec.attribute12 := rtrim(per_rec.attribute12);
175 per_rec.attribute14 := rtrim(per_rec.attribute14);
176 per_rec.attribute17 := rtrim(per_rec.attribute17);
177 per_rec.attribute20 := rtrim(per_rec.attribute20);
178 per_rec.middle_names := rtrim(per_rec.middle_names);
179 per_rec.nationality := rtrim(per_rec.nationality);
180 per_rec.national_identifier := rtrim(per_rec.national_identifier);
181 per_rec.previous_last_name := rtrim(per_rec.previous_last_name);
182 per_rec.sex := rtrim(per_rec.sex);
183 per_rec.title := rtrim(per_rec.title);
184 per_rec.suffix := rtrim(per_rec.suffix);
185 per_rec.work_telephone := rtrim(per_rec.work_telephone);
186 per_rec.attribute1 := rtrim(per_rec.attribute1);
187 per_rec.attribute2 := rtrim(per_rec.attribute2);
188 per_rec.attribute4 := rtrim(per_rec.attribute4);
189 per_rec.attribute5 := rtrim(per_rec.attribute5);
190 per_rec.attribute7 := rtrim(per_rec.attribute7);
191 per_rec.attribute8 := rtrim(per_rec.attribute8);
192 per_rec.attribute10 := rtrim(per_rec.attribute10);
193 per_rec.attribute11 := rtrim(per_rec.attribute11);
194 per_rec.attribute13 := rtrim(per_rec.attribute13);
195 per_rec.attribute15 := rtrim(per_rec.attribute15);
196 per_rec.attribute16 := rtrim(per_rec.attribute16);
197 per_rec.attribute18 := rtrim(per_rec.attribute18);
198 per_rec.attribute19 := rtrim(per_rec.attribute19);
199 per_rec.attribute21 := rtrim(per_rec.attribute21);
200 per_rec.attribute22 := rtrim(per_rec.attribute22);
201 per_rec.attribute23 := rtrim(per_rec.attribute23);
202 per_rec.attribute24 := rtrim(per_rec.attribute24);
203 per_rec.attribute25 := rtrim(per_rec.attribute25);
204 per_rec.attribute26 := rtrim(per_rec.attribute26);
205 per_rec.attribute27 := rtrim(per_rec.attribute27);
206 per_rec.attribute28 := rtrim(per_rec.attribute28);
207 per_rec.attribute29 := rtrim(per_rec.attribute29);
208 per_rec.attribute30 := rtrim(per_rec.attribute30);
209 per_rec.per_information_category := rtrim(per_rec.per_information_category);
210 per_rec.current_applicant_flag := rtrim(per_rec.current_applicant_flag);
211 per_rec.current_employee_flag := rtrim(per_rec.current_employee_flag);
212 per_rec.email_address := rtrim(per_rec.email_address);
213 per_rec.employee_number := rtrim(per_rec.employee_number);
214 per_rec.first_name := rtrim(per_rec.first_name);
215 per_rec.full_name := rtrim(per_rec.full_name);
216 per_rec.marital_status := rtrim(per_rec.marital_status);
217 per_rec.per_information1 := rtrim(per_rec.per_information1);
218 per_rec.per_information2 := rtrim(per_rec.per_information2);
219 per_rec.per_information3 := rtrim(per_rec.per_information3);
220 per_rec.per_information4 := rtrim(per_rec.per_information4);
221 per_rec.per_information5 := rtrim(per_rec.per_information5);
222 per_rec.per_information6 := rtrim(per_rec.per_information6);
223 per_rec.per_information7 := rtrim(per_rec.per_information7);
224 per_rec.per_information8 := rtrim(per_rec.per_information8);
225 per_rec.per_information9 := rtrim(per_rec.per_information9);
226 per_rec.per_information10 := rtrim(per_rec.per_information10);
227 per_rec.per_information12 := rtrim(per_rec.per_information12);
228 per_rec.per_information13 := rtrim(per_rec.per_information13);
229 per_rec.per_information14 := rtrim(per_rec.per_information14);
230 per_rec.per_information15 := rtrim(per_rec.per_information15);
231 per_rec.per_information17 := rtrim(per_rec.per_information17);
232 per_rec.per_information18 := rtrim(per_rec.per_information18);
233 per_rec.per_information19 := rtrim(per_rec.per_information19);
234 per_rec.per_information20 := rtrim(per_rec.per_information20);
235 per_rec.per_information21 := rtrim(per_rec.per_information21);
236 per_rec.per_information22 := rtrim(per_rec.per_information22);
237 per_rec.per_information23 := rtrim(per_rec.per_information23);
238 per_rec.per_information24 := rtrim(per_rec.per_information24);
239 per_rec.per_information25 := rtrim(per_rec.per_information25);
240 per_rec.per_information26 := rtrim(per_rec.per_information26);
241 per_rec.per_information27 := rtrim(per_rec.per_information27);
242 per_rec.per_information28 := rtrim(per_rec.per_information28);
243 per_rec.per_information29 := rtrim(per_rec.per_information29);
244 per_rec.per_information30 := rtrim(per_rec.per_information30);
245 --
246 per_rec.work_schedule := rtrim(per_rec.work_schedule);
247 per_rec.correspondence_language := rtrim(per_rec.correspondence_language);
248 per_rec.student_status := rtrim(per_rec.student_status);
249 per_rec.fte_capacity := rtrim(per_rec.fte_capacity);
250 per_rec.on_military_service := rtrim(per_rec.on_military_service);
251 per_rec.second_passport_exists := rtrim(per_rec.second_passport_exists);
252 per_rec.background_check_status := rtrim(per_rec.background_check_status);
253 per_rec.background_date_check := trunc(per_rec.background_date_check);
254 per_rec.blood_type := rtrim(per_rec.blood_type);
255 per_rec.last_medical_test_date := trunc(per_rec.last_medical_test_date);
256 per_rec.last_medical_test_by := rtrim(per_rec.last_medical_test_by);
257 per_rec.rehire_recommendation := rtrim(per_rec.rehire_recommendation);
258 per_rec.rehire_reason := rtrim(per_rec.rehire_reason);
259 per_rec.resume_exists := rtrim(per_rec.resume_exists);
260 per_rec.resume_last_updated := trunc(per_rec.resume_last_updated);
261 per_rec.office_number := rtrim(per_rec.office_number);
262 per_rec.internal_location := rtrim(per_rec.internal_location);
263 per_rec.mailstop := rtrim(per_rec.mailstop);
264 per_rec.honors := rtrim(per_rec.honors);
265 per_rec.pre_name_adjunct := rtrim(per_rec.pre_name_adjunct);
266 per_rec.hold_applicant_date_until := trunc(per_rec.hold_applicant_date_until);
267 per_rec.benefit_group_id := rtrim(per_rec.benefit_group_id);
268 per_rec.receipt_of_death_cert_date := rtrim(per_rec.receipt_of_death_cert_date);
269 per_rec.coord_ben_med_pln_no := rtrim(per_rec.coord_ben_med_pln_no);
270 per_rec.coord_ben_no_cvg_flag := rtrim(per_rec.coord_ben_no_cvg_flag);
271 per_rec.uses_tobacco_flag := rtrim(per_rec.uses_tobacco_flag);
272 per_rec.dpdnt_adoption_date := rtrim(per_rec.dpdnt_adoption_date);
273 per_rec.dpdnt_vlntry_svce_flag := rtrim(per_rec.dpdnt_vlntry_svce_flag);
274 per_rec.date_of_death := trunc(per_rec.date_of_death);
275 per_rec.original_date_of_hire := trunc(per_rec.original_date_of_hire);
276 per_rec.town_of_birth := rtrim(per_rec.town_of_birth);
277 per_rec.region_of_birth := rtrim(per_rec.region_of_birth);
278 per_rec.country_of_birth := rtrim(per_rec.country_of_birth);
279 per_rec.global_person_id := rtrim(per_rec.global_person_id);
280 per_rec.npw_number := rtrim(per_rec.npw_number);
281 per_rec.current_npw_flag := rtrim(per_rec.current_npw_flag);
282
283 --
284 if ( ((per_rec.person_id = p_person_id)
285 or (per_rec.person_id is null
286 and (p_person_id is null)))
287 and ((per_rec.effective_start_date = p_effective_start_date)
288 or (per_rec.effective_start_date is null
289 and (p_effective_start_date is null)))
290 and ((per_rec.effective_end_date = p_effective_end_date)
291 or (per_rec.effective_end_date is null
292 and (p_effective_end_date is null)))
293 and ((per_rec.business_group_id = p_business_group_id)
294 or (per_rec.business_group_id is null
295 and (p_business_group_id is null)))
296 and ((per_rec.person_type_id = p_person_type_id)
297 or (per_rec.person_type_id is null
298 and (p_person_type_id is null)))
299 and ((per_rec.last_name = p_last_name)
300 or (per_rec.last_name is null
301 and (p_last_name is null)))
302 and ((per_rec.start_date = p_start_date)
303 or (per_rec.start_date is null
304 and (p_start_date is null)))
305 and ((per_rec.applicant_number = p_applicant_number)
306 or (per_rec.applicant_number is null
307 and (p_applicant_number is null)))
308 and ((per_rec.comment_id = p_comment_id)
309 or (per_rec.comment_id is null
310 and (p_comment_id is null)))
311 and ((per_rec.current_applicant_flag = p_current_applicant_flag)
312 or (per_rec.current_applicant_flag is null
313 and (p_current_applicant_flag is null)))
314 and ((per_rec.current_emp_or_apl_flag = p_current_emp_or_apl_flag)
315 or (per_rec.current_emp_or_apl_flag is null
316 and (p_current_emp_or_apl_flag is null)))
317 and ((per_rec.current_employee_flag = p_current_employee_flag)
318 or (per_rec.current_employee_flag is null
319 and (p_current_employee_flag is null)))
320 and ((per_rec.date_employee_data_verified = p_date_employee_data_verified)
321 or (per_rec.date_employee_data_verified is null
322 and (p_date_employee_data_verified is null)))
323 and ((per_rec.date_of_birth = p_date_of_birth)
324 or (per_rec.date_of_birth is null
325 and (p_date_of_birth is null)))
326 and ((per_rec.town_of_birth = p_town_of_birth)
327 or (per_rec.town_of_birth is null
328 and (p_town_of_birth is null)))
329 and ((per_rec.region_of_birth = p_region_of_birth)
330 or (per_rec.region_of_birth is null
331 and (p_region_of_birth is null)))
332 and ((per_rec.country_of_birth = p_country_of_birth)
333 or (per_rec.country_of_birth is null
334 and (p_country_of_birth is null)))
335 and ((per_rec.global_person_id = p_global_person_id)
336 or (per_rec.global_person_id is null
337 and (p_global_person_id is null)))
338 and ((per_rec.email_address = p_email_address)
339 or (per_rec.email_address is null
340 and (p_email_address is null)))
341 and ((per_rec.employee_number = p_employee_number)
342 or (per_rec.employee_number is null
343 and (p_employee_number is null)))
344 and ((per_rec.expense_check_send_to_address = p_expense_check_send_to_addr)
345 or (per_rec.expense_check_send_to_address is null
346 and (p_expense_check_send_to_addr is null)))
347 and ((per_rec.first_name = p_first_name)
348 or (per_rec.first_name is null
349 and (p_first_name is null)))
350 and ((per_rec.full_name = p_full_name)
351 or (per_rec.full_name is null
352 and (p_full_name is null)))
353 and ((per_rec.known_as = p_known_as)
354 or (per_rec.known_as is null
355 and (p_known_as is null)))
356 and ((per_rec.marital_status = p_marital_status)
357 or (per_rec.marital_status is null
358 and (p_marital_status is null)))
359 and ((per_rec.middle_names = p_middle_names)
360 or (per_rec.middle_names is null
361 and (p_middle_names is null)))
362 and ((per_rec.nationality = p_nationality)
363 or (per_rec.nationality is null
364 and (p_nationality is null)))
365 and ((per_rec.national_identifier = p_national_identifier)
366 or (per_rec.national_identifier is null
370 and (p_previous_last_name is null)))
367 and (p_national_identifier is null)))
368 and ((per_rec.previous_last_name = p_previous_last_name)
369 or (per_rec.previous_last_name is null
371 and ((per_rec.registered_disabled_flag = p_registered_disabled_flag)
372 or (per_rec.registered_disabled_flag is null
373 and (p_registered_disabled_flag is null)))
374 and ((per_rec.sex = p_sex)
375 or (per_rec.sex is null
376 and (p_sex is null)))
377 and ((per_rec.title = p_title)
378 or (per_rec.title is null
379 and (p_title is null)))
380 and ((per_rec.suffix = p_suffix)
381 or (per_rec.suffix is null
382 and (p_suffix is null)))
383 and ((per_rec.vendor_id = p_vendor_id)
384 or (per_rec.vendor_id is null
385 and (p_vendor_id is null)))
386 -- Commented out two following lines as work_telephone
387 -- is not present in the Person form. VT 09/15/97.
388 --and ((per_rec.work_telephone = p_work_telephone)
389 --or (per_rec.work_telephone is null))
390 -- FIX for WWBUG 436781
391 -- GP
392 -- This is being done as the work telephone can be derived from either the
393 -- PER_PEOPLE_F table or from the PER_PHONES table. If the PER_PHONES table
394 -- is used then the lock fails as the database value is different from the
395 -- form value and thus the lock assumes an update has occured so asks you
396 -- to requery the record. This never occurs if the work_telephone is entered
397 -- on the form as then the lock succeeds.
398 -- The only way this lock could fail now is if the record is cleared and in
399 -- the time it takes to clear someone else updates the record on the
400 -- database then the on-lock trigger wil fail as the per_rec.work_telephone
401 -- will have a value and the form will not. (Highly Unlikely).
402 --and (p_work_telephone is null)))
403 and ((per_rec.attribute_category = p_a_cat)
404 or (per_rec.attribute_category is null
405 and (p_a_cat is null)))
406 and ((per_rec.attribute1 = p_a1)
407 or (per_rec.attribute1 is null
408 and (p_a1 is null)))
409 and ((per_rec.attribute2 = p_a2)
410 or (per_rec.attribute2 is null
411 and (p_a2 is null)))
412 and ((per_rec.attribute3 = p_a3)
413 or (per_rec.attribute3 is null
414 and (p_a3 is null)))
415 and ((per_rec.attribute4 = p_a4)
416 or (per_rec.attribute4 is null
417 and (p_a4 is null)))
418 and ((per_rec.attribute5 = p_a5)
419 or (per_rec.attribute5 is null
420 and (p_a5 is null)))
421 and ((per_rec.attribute6 = p_a6)
422 or (per_rec.attribute6 is null
423 and (p_a6 is null)))
424 and ((per_rec.attribute7 = p_a7)
425 or (per_rec.attribute7 is null
426 and (p_a7 is null)))
427 and ((per_rec.attribute8 = p_a8)
428 or (per_rec.attribute8 is null
429 and (p_a8 is null)))
430 and ((per_rec.attribute9 = p_a9)
431 or (per_rec.attribute9 is null
432 and (p_a9 is null)))
433 and ((per_rec.attribute10 = p_a10)
434 or (per_rec.attribute10 is null
435 and (p_a10 is null)))
436 and ((per_rec.attribute11 = p_a11)
437 or (per_rec.attribute11 is null
438 and (p_a11 is null)))
439 and ((per_rec.attribute12 = p_a12)
440 or (per_rec.attribute12 is null
441 and (p_a12 is null)))
442 and ((per_rec.attribute13 = p_a13)
443 or (per_rec.attribute13 is null
444 and (p_a13 is null)))
445 and ((per_rec.attribute14 = p_a14)
446 or (per_rec.attribute14 is null
447 and (p_a14 is null)))
448 and ((per_rec.attribute15 = p_a15)
449 or (per_rec.attribute15 is null
450 and (p_a15 is null)))
451 and ((per_rec.attribute16 = p_a16)
452 or (per_rec.attribute16 is null
453 and (p_a16 is null)))
454 and ((per_rec.attribute17 = p_a17)
455 or (per_rec.attribute17 is null
456 and (p_a17 is null)))
457 and ((per_rec.attribute18 = p_a18)
458 or (per_rec.attribute18 is null
459 and (p_a18 is null)))
460 and ((per_rec.attribute19 = p_a19)
461 or (per_rec.attribute19 is null
462 and (p_a19 is null)))
463 and ((per_rec.attribute20 = p_a20)
464 or (per_rec.attribute20 is null
465 and (p_a20 is null))) )then
466 --
467 -- PL/SQL cannot handle an IF statement this length
468 -- so split the comparisons into more manageable 'chunks'
469 --
470 if ( ((per_rec.attribute21 = p_a21)
471 or (per_rec.attribute21 is null
472 and (p_a21 is null)))
473 and ((per_rec.attribute22 = p_a22)
474 or (per_rec.attribute22 is null
475 and (p_a22 is null)))
476 and ((per_rec.attribute23 = p_a23)
477 or (per_rec.attribute23 is null
478 and (p_a23 is null)))
479 and ((per_rec.attribute24 = p_a24)
480 or (per_rec.attribute24 is null
481 and (p_a24 is null)))
482 and ((per_rec.attribute25 = p_a25)
483 or (per_rec.attribute25 is null
484 and (p_a25 is null)))
485 and ((per_rec.attribute26 = p_a26)
486 or (per_rec.attribute26 is null
487 and (p_a26 is null)))
488 and ((per_rec.attribute27 = p_a27)
489 or (per_rec.attribute27 is null
490 and (p_a27 is null)))
491 and ((per_rec.attribute28 = p_a28)
492 or (per_rec.attribute28 is null
493 and (p_a28 is null)))
494 and ((per_rec.attribute29 = p_a29)
495 or (per_rec.attribute29 is null
496 and (p_a29 is null)))
497 and ((per_rec.attribute30 = p_a30)
501 or (per_rec.per_information_category is null
498 or (per_rec.attribute30 is null
499 and (p_a30 is null)))
500 and ((per_rec.per_information_category = p_i_cat)
502 and (p_i_cat is null)))
503 and ((per_rec.per_information1 = p_i1)
504 or (per_rec.per_information1 is null
505 and (p_i1 is null)))
506 and ((per_rec.per_information2 = p_i2)
507 or (per_rec.per_information2 is null
508 and (p_i2 is null)))
509 and ((per_rec.per_information3 = p_i3)
510 or (per_rec.per_information3 is null
511 and (p_i3 is null)))
512 and ((per_rec.per_information4 = p_i4)
513 or (per_rec.per_information4 is null
514 and (p_i4 is null)))
515 and ((per_rec.per_information5 = p_i5)
516 or (per_rec.per_information5 is null
517 and (p_i5 is null)))
518 and ((per_rec.per_information6 = p_i6)
519 or (per_rec.per_information6 is null
520 and (p_i6 is null)))
521 and ((per_rec.per_information7 = p_i7)
522 or (per_rec.per_information7 is null
523 and (p_i7 is null)))
524 and ((per_rec.per_information8 = p_i8)
525 or (per_rec.per_information8 is null
526 and (p_i8 is null)))
527 and ((per_rec.per_information9 = p_i9)
528 or (per_rec.per_information9 is null
529 and (p_i9 is null)))
530 and ((per_rec.per_information10 = p_i10)
531 or (per_rec.per_information10 is null
532 and (p_i10 is null)))
533 and ((per_rec.per_information11 = p_i11)
534 or (per_rec.per_information11 is null
535 and (p_i11 is null)))
536 and ((per_rec.per_information12 = p_i12)
537 or (per_rec.per_information12 is null
538 and (p_i12 is null)))
539 and ((per_rec.per_information13 = p_i13)
540 or (per_rec.per_information13 is null
541 and (p_i13 is null)))
542 and ((per_rec.per_information14 = p_i14)
543 or (per_rec.per_information14 is null
544 and (p_i14 is null)))
545 and ((per_rec.per_information15 = p_i15)
546 or (per_rec.per_information15 is null
547 and (p_i15 is null)))
548 and ((per_rec.per_information16 = p_i16)
549 or (per_rec.per_information16 is null
550 and (p_i16 is null)))
551 and ((per_rec.per_information17 = p_i17)
552 or (per_rec.per_information17 is null
553 and (p_i17 is null)))
554 and ((per_rec.per_information18 = p_i18)
555 or (per_rec.per_information18 is null
556 and (p_i18 is null)))
557 and ((per_rec.per_information19 = p_i19)
558 or (per_rec.per_information19 is null
559 and (p_i19 is null)))
560 and ((per_rec.per_information20 = p_i20)
561 or (per_rec.per_information20 is null
562 and (p_i20 is null)))
563 and ((per_rec.per_information21 = p_i21)
564 or (per_rec.per_information21 is null
565 and (p_i21 is null)))
566 and ((per_rec.per_information22 = p_i22)
567 or (per_rec.per_information22 is null
568 and (p_i22 is null)))
569 and ((per_rec.per_information23 = p_i23)
570 or (per_rec.per_information23 is null
571 and (p_i23 is null)))
572 and ((per_rec.per_information24 = p_i24)
573 or (per_rec.per_information24 is null
574 and (p_i24 is null)))
575 and ((per_rec.per_information25 = p_i25)
576 or (per_rec.per_information25 is null
577 and (p_i25 is null)))
578 and ((per_rec.per_information26 = p_i26)
579 or (per_rec.per_information26 is null
580 and (p_i26 is null)))
581 and ((per_rec.per_information27 = p_i27)
582 or (per_rec.per_information27 is null
583 and (p_i27 is null)))
584 and ((per_rec.per_information28 = p_i28)
585 or (per_rec.per_information28 is null
586 and (p_i28 is null)))
587 and ((per_rec.per_information29 = p_i29)
588 or (per_rec.per_information29 is null
589 and (p_i29 is null)))
590 and ((per_rec.per_information30 = p_i30)
591 or (per_rec.per_information30 is null
592 and (p_i30 is null)))
593 ) then
594 -- VT 09/11/96 added more !!!!!!!!!
595 if ( ((per_rec.work_schedule = p_work_schedule)
596 or (per_rec.work_schedule is null
597 and (p_work_schedule is null)))
598 and ((per_rec.correspondence_language = p_correspondence_language)
599 or (per_rec.correspondence_language is null
600 and (p_correspondence_language is null)))
601 and ((per_rec.student_status = p_student_status)
602 or (per_rec.student_status is null
603 and (p_student_status is null)))
604 and ((per_rec.fte_capacity = p_fte_capacity)
605 or (per_rec.fte_capacity is null
606 and (p_fte_capacity is null)))
607 and ((per_rec.on_military_service = p_on_military_service)
608 or (per_rec.on_military_service is null
609 and (p_on_military_service is null)))
610 and ((per_rec.second_passport_exists = p_second_passport_exists)
611 or (per_rec.second_passport_exists is null
612 and (p_second_passport_exists is null)))
613 and ((per_rec.background_check_status = p_background_check_status )
614 or (per_rec.background_check_status is null
615 and (p_background_check_status is null)))
616 and ((per_rec.background_date_check = p_background_date_check)
617 or (per_rec.background_date_check is null
618 and (p_background_date_check is null)))
619 and ((per_rec.blood_type = p_blood_type)
620 or (per_rec.blood_type is null
621 and (p_blood_type is null)))
622 and ((per_rec.last_medical_test_date = p_last_medical_test_date)
623 or (per_rec.last_medical_test_date is null
624 and (p_last_medical_test_date is null)))
625 and ((per_rec.last_medical_test_by = p_last_medical_test_by)
626 or (per_rec.last_medical_test_by is null
627 and (p_last_medical_test_by is null)))
628 and ((per_rec.rehire_recommendation = p_rehire_recommendation)
629 or (per_rec.rehire_recommendation is null
630 and (p_rehire_recommendation is null)))
631 and ((per_rec.rehire_reason = p_rehire_reason)
632 or (per_rec.rehire_reason is null
633 and (p_rehire_reason is null)))
634 and ((per_rec.resume_exists = p_resume_exists)
635 or (per_rec.resume_exists is null
636 and (p_resume_exists is null)))
637 and ((per_rec.resume_last_updated = p_resume_last_updated)
638 or (per_rec.resume_last_updated is null
639 and (p_resume_last_updated is null)))
640 and ((per_rec.office_number = p_office_number)
641 or (per_rec.office_number is null
642 and (p_office_number is null)))
643 and ((per_rec.internal_location = p_internal_location)
644 or (per_rec.internal_location is null
645 and (p_internal_location is null)))
646 and ((per_rec.mailstop = p_mailstop)
647 or (per_rec.mailstop is null
648 and (p_mailstop is null)))
649 and ((per_rec.honors = p_honors)
650 or (per_rec.honors is null
651 and (p_honors is null)))
652 and ((per_rec.pre_name_adjunct = p_pre_name_adjunct)
653 or (per_rec.pre_name_adjunct is null
654 and (p_pre_name_adjunct is null)))
655 and ((per_rec.hold_applicant_date_until = p_hold_applicant_date_until)
656 or (per_rec.hold_applicant_date_until is null
657 and (p_hold_applicant_date_until is null)))
658 and ((per_rec.benefit_group_id = p_benefit_group_id)
659 or (per_rec.benefit_group_id is null
660 and (p_benefit_group_id is null)))
661 and ((per_rec.receipt_of_death_cert_date = p_receipt_of_death_cert_date)
662 or (per_rec.receipt_of_death_cert_date is null
663 and (p_receipt_of_death_cert_date is null)))
664 and ((per_rec.coord_ben_med_pln_no = p_coord_ben_med_pln_no)
665 or (per_rec.coord_ben_med_pln_no is null
666 and (p_coord_ben_med_pln_no is null)))
667 and ((per_rec.coord_ben_no_cvg_flag = p_coord_ben_no_cvg_flag )
668 or ( per_rec.coord_ben_no_cvg_flag is null
669 and (p_coord_ben_no_cvg_flag is null)))
670 and ((per_rec.uses_tobacco_flag = p_uses_tobacco_flag)
671 or (per_rec.uses_tobacco_flag is null
672 and (p_uses_tobacco_flag is null)))
673 and (( per_rec.dpdnt_adoption_date = p_dpdnt_adoption_date)
674 or (per_rec.dpdnt_adoption_date is null
675 and (p_dpdnt_adoption_date is null)))
676 and ((per_rec.dpdnt_vlntry_svce_flag = p_dpdnt_vlntry_svce_flag)
677 or (per_rec.dpdnt_vlntry_svce_flag is null
678 and (p_dpdnt_vlntry_svce_flag is null)))
679 and ((per_rec.date_of_death = p_date_of_death)
680 or (per_rec.date_of_death is null
681 and (p_date_of_death is null)))
682 and ((per_rec.original_date_of_hire = p_original_date_of_hire)
683 or (per_rec.original_date_of_hire is null
684 and (p_original_date_of_hire is null)))
685 and ((per_rec.period_of_service_id = p_period_of_service_id)
686 or (per_rec.period_of_service_id is null
687 and (p_period_of_service_id is null)))
688 and ((per_rec.npw_number = p_npw_number)
689 or (per_rec.npw_number is null
690 and (p_npw_number is null)))
691 and ((per_rec.current_npw_flag = p_current_npw_flag)
692 or (per_rec.current_npw_flag is null
693 and (p_current_npw_flag is null)))
694
695 ) then
696 open per1;
697 fetch per1 into per_rec1;
698 close per1;
699 return; -- return record is locked and ok.
700 end if;
701 end if;
702 end if;
703 -- Record changed by another user.
704 --
705 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
706 app_exception.raise_exception ;
707 exception when no_data_found then
708 raise;
709 when others then raise;
710 end lock_row1;
711 --
712 END PER_PEOPLE13_PKG;
[Home] [Help]