DBA Data[Home] [Help]

PACKAGE BODY: APPS.PER_PEOPLE13_PKG

Source


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