[Home] [Help]
PACKAGE BODY: APPS.PER_ASSIGNMENTS_V7_PKG
Source
1 PACKAGE BODY PER_ASSIGNMENTS_V7_PKG as
2 /* $Header: peasg07t.pkb 120.2 2005/12/05 14:01:08 rthiagar noship $ */
3 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
4 X_Assignment_Id IN OUT NOCOPY NUMBER,
5 X_Effective_Start_Date DATE,
6 X_Effective_End_Date DATE,
7 X_Business_Group_Id NUMBER,
8 X_Recruiter_Id NUMBER,
9 X_Grade_Id NUMBER,
10 X_Position_Id NUMBER,
11 X_Job_Id NUMBER,
12 X_Assignment_Status_Type_Id IN OUT NOCOPY NUMBER,
13 X_Payroll_Id NUMBER,
14 X_Location_Id NUMBER,
15 X_Person_Referred_By_Id NUMBER,
16 X_Supervisor_Id NUMBER,
17 X_Special_Ceiling_Step_Id NUMBER,
18 X_Person_Id NUMBER,
19 X_Employee_Number VARCHAR2,
20 X_Recruitment_Activity_Id NUMBER,
21 X_Source_Organization_Id NUMBER,
22 X_Organization_Id NUMBER,
23 X_People_Group_Id NUMBER,
24 X_Soft_Coding_Keyflex_Id NUMBER,
25 X_Vacancy_Id NUMBER,
26 X_Pay_Basis_Id NUMBER,
27 X_Assignment_Sequence IN OUT NOCOPY NUMBER,
28 X_Assignment_Type VARCHAR2,
29 X_Primary_Flag IN OUT NOCOPY VARCHAR2,
30 X_Application_Id NUMBER,
31 X_Assignment_Number IN OUT NOCOPY VARCHAR2,
32 X_Change_Reason VARCHAR2,
33 X_Comment_Id NUMBER,
34 X_Date_Probation_End DATE,
35 X_Default_Code_Comb_Id NUMBER,
36 X_Employment_Category VARCHAR2,
37 X_Frequency VARCHAR2,
38 X_Internal_Address_Line VARCHAR2,
39 X_Manager_Flag VARCHAR2,
40 X_Normal_Hours NUMBER,
41 X_Perf_Review_Period NUMBER,
42 X_Perf_Review_Period_Frequency VARCHAR2,
43 X_Period_Of_Service_Id NUMBER,
44 X_Probation_Period NUMBER,
45 X_Probation_Unit VARCHAR2,
46 X_Sal_Review_Period NUMBER,
47 X_Sal_Review_Period_Frequency VARCHAR2,
48 X_Set_Of_Books_Id NUMBER,
49 X_Source_Type VARCHAR2,
50 X_Time_Normal_Finish VARCHAR2,
51 X_Time_Normal_Start VARCHAR2,
52 X_Ass_Attribute_Category VARCHAR2,
53 X_Ass_Attribute1 VARCHAR2,
54 X_Ass_Attribute2 VARCHAR2,
55 X_Ass_Attribute3 VARCHAR2,
56 X_Ass_Attribute4 VARCHAR2,
57 X_Ass_Attribute5 VARCHAR2,
58 X_Ass_Attribute6 VARCHAR2,
59 X_Ass_Attribute7 VARCHAR2,
60 X_Ass_Attribute8 VARCHAR2,
61 X_Ass_Attribute9 VARCHAR2,
62 X_Ass_Attribute10 VARCHAR2,
63 X_Ass_Attribute11 VARCHAR2,
64 X_Ass_Attribute12 VARCHAR2,
65 X_Ass_Attribute13 VARCHAR2,
66 X_Ass_Attribute14 VARCHAR2,
67 X_Ass_Attribute15 VARCHAR2,
68 X_Ass_Attribute16 VARCHAR2,
69 X_Ass_Attribute17 VARCHAR2,
70 X_Ass_Attribute18 VARCHAR2,
71 X_Ass_Attribute19 VARCHAR2,
72 X_Ass_Attribute20 VARCHAR2,
73 X_Ass_Attribute21 VARCHAR2,
74 X_Ass_Attribute22 VARCHAR2,
75 X_Ass_Attribute23 VARCHAR2,
76 X_Ass_Attribute24 VARCHAR2,
77 X_Ass_Attribute25 VARCHAR2,
78 X_Ass_Attribute26 VARCHAR2,
79 X_Ass_Attribute27 VARCHAR2,
80 X_Ass_Attribute28 VARCHAR2,
81 X_Ass_Attribute29 VARCHAR2,
82 X_Ass_Attribute30 VARCHAR2,
83 X_Last_Update_Date DATE,
84 X_Last_Updated_By NUMBER,
85 X_Last_Update_Login NUMBER,
86 X_Created_By NUMBER,
87 X_Creation_Date DATE,
88 X_Title VARCHAR2
89 ) IS
90 CURSOR C IS SELECT rowid FROM PER_ASSIGNMENTS_F
91 WHERE assignment_id = X_Assignment_Id;
92 CURSOR C2 IS SELECT per_assignments_s.nextval FROM sys.dual;
93 cursor c3 is
94 select past.assignment_status_type_id
95 from per_assignment_status_types past
96 , per_ass_status_type_amends pasa
97 , per_business_groups pbg
98 where pasa.assignment_status_type_id(+) = past.assignment_status_type_id
99 and pasa.business_group_id(+) = X_Business_Group_id
100 and pbg.business_group_id = X_Business_Group_id
101 and nvl(past.business_group_id,X_Business_Group_id) = X_Business_Group_id
102 and nvl(past.legislation_code, pbg.legislation_code) = pbg.legislation_code
103 and nvl(pasa.active_flag,past.active_flag) = 'Y'
104 and nvl(pasa.default_flag,past.default_flag) = 'Y'
105 and nvl(pasa.per_system_status,past.per_system_status) = 'ACTIVE_ASSIGN';
106 --
107 BEGIN
108 hr_utility.set_location('per_assignments_v7_pkg.insert_row',10);
109 if (X_Assignment_Id is NULL) then
110 OPEN C2;
111 FETCH C2 INTO X_Assignment_Id;
112 CLOSE C2;
113 end if;
114 hr_utility.set_location('per_assignments_v7_pkg.insert_row',20);
115 open c3;
116 fetch c3 into X_assignment_status_type_id;
117 close c3;
118 --
119 hr_utility.set_location('per_assignments_v7_pkg.insert_row',30);
120 hr_assignment.gen_new_ass_sequence(X_person_id
121 ,X_Assignment_type
122 ,X_assignment_sequence);
123 --
124 hr_utility.set_location('per_assignments_v7_pkg.insert_row',40);
125 hr_assignment.gen_new_ass_number(X_Assignment_Id
126 ,X_business_group_id
127 ,X_employee_number
128 ,X_assignment_sequence
129 ,X_assignment_number);
130 --
131 -- Check for current primary
132 --
133 hr_utility.set_location('per_assignments_v7_pkg.insert_row',50);
134 per_assignments_v7_pkg.check_primary(X_person_id => X_person_id
135 ,X_primary_flag => X_primary_flag);
136 --
137 hr_utility.set_location('per_assignments_v7_pkg.insert_row',60);
138 INSERT INTO PER_ASSIGNMENTS_F(
139 assignment_id,
140 effective_start_date,
141 effective_end_date,
142 business_group_id,
143 recruiter_id,
144 grade_id,
145 position_id,
146 job_id,
147 assignment_status_type_id,
148 payroll_id,
149 location_id,
150 person_referred_by_id,
151 supervisor_id,
152 special_ceiling_step_id,
153 person_id,
154 recruitment_activity_id,
155 source_organization_id,
156 organization_id,
157 people_group_id,
158 soft_coding_keyflex_id,
159 vacancy_id,
160 pay_basis_id,
161 assignment_sequence,
162 assignment_type,
163 primary_flag,
164 application_id,
165 assignment_number,
166 change_reason,
167 comment_id,
168 date_probation_end,
169 default_code_comb_id,
170 employment_category,
171 frequency,
172 internal_address_line,
173 manager_flag,
174 normal_hours,
175 perf_review_period,
176 perf_review_period_frequency,
177 period_of_service_id,
178 probation_period,
179 probation_unit,
180 sal_review_period,
181 sal_review_period_frequency,
182 set_of_books_id,
183 source_type,
184 time_normal_finish,
185 time_normal_start,
186 ass_attribute_category,
187 ass_attribute1,
188 ass_attribute2,
189 ass_attribute3,
190 ass_attribute4,
191 ass_attribute5,
192 ass_attribute6,
193 ass_attribute7,
194 ass_attribute8,
195 ass_attribute9,
196 ass_attribute10,
197 ass_attribute11,
198 ass_attribute12,
199 ass_attribute13,
200 ass_attribute14,
201 ass_attribute15,
202 ass_attribute16,
203 ass_attribute17,
204 ass_attribute18,
205 ass_attribute19,
206 ass_attribute20,
207 ass_attribute21,
208 ass_attribute22,
209 ass_attribute23,
210 ass_attribute24,
211 ass_attribute25,
212 ass_attribute26,
213 ass_attribute27,
214 ass_attribute28,
215 ass_attribute29,
216 ass_attribute30,
217 last_update_date,
218 last_updated_by,
219 last_update_login,
220 created_by,
221 creation_date,
222 title
223 ) VALUES (
224 X_Assignment_Id,
225 X_Effective_Start_Date,
226 X_Effective_End_Date,
227 X_Business_Group_Id,
228 X_Recruiter_Id,
229 X_Grade_Id,
230 X_Position_Id,
231 X_Job_Id,
232 X_Assignment_Status_Type_Id,
233 X_Payroll_Id,
234 X_Location_Id,
235 X_Person_Referred_By_Id,
236 X_Supervisor_Id,
237 X_Special_Ceiling_Step_Id,
238 X_Person_Id,
239 X_Recruitment_Activity_Id,
240 X_Source_Organization_Id,
241 X_Organization_Id,
242 X_People_Group_Id,
243 X_Soft_Coding_Keyflex_Id,
244 X_Vacancy_Id,
245 X_Pay_Basis_Id,
246 X_Assignment_Sequence,
247 X_Assignment_Type,
248 X_Primary_Flag,
249 X_Application_Id,
250 X_Assignment_Number,
251 X_Change_Reason,
252 X_Comment_Id,
253 X_Date_Probation_End,
254 X_Default_Code_Comb_Id,
255 X_Employment_Category,
256 X_Frequency,
257 X_Internal_Address_Line,
258 X_Manager_Flag,
259 X_Normal_Hours,
260 X_Perf_Review_Period,
261 X_Perf_Review_Period_Frequency,
262 X_Period_Of_Service_Id,
263 X_Probation_Period,
264 X_Probation_Unit,
265 X_Sal_Review_Period,
266 X_Sal_Review_Period_Frequency,
267 X_Set_Of_Books_Id,
268 X_Source_Type,
269 X_Time_Normal_Finish,
270 X_Time_Normal_Start,
271 X_Ass_Attribute_Category,
272 X_Ass_Attribute1,
273 X_Ass_Attribute2,
274 X_Ass_Attribute3,
275 X_Ass_Attribute4,
276 X_Ass_Attribute5,
277 X_Ass_Attribute6,
278 X_Ass_Attribute7,
279 X_Ass_Attribute8,
280 X_Ass_Attribute9,
281 X_Ass_Attribute10,
282 X_Ass_Attribute11,
283 X_Ass_Attribute12,
284 X_Ass_Attribute13,
285 X_Ass_Attribute14,
286 X_Ass_Attribute15,
287 X_Ass_Attribute16,
288 X_Ass_Attribute17,
289 X_Ass_Attribute18,
290 X_Ass_Attribute19,
291 X_Ass_Attribute20,
292 X_Ass_Attribute21,
293 X_Ass_Attribute22,
294 X_Ass_Attribute23,
295 X_Ass_Attribute24,
296 X_Ass_Attribute25,
297 X_Ass_Attribute26,
298 X_Ass_Attribute27,
299 X_Ass_Attribute28,
300 X_Ass_Attribute29,
301 X_Ass_Attribute30,
302 X_Last_Update_Date,
303 X_Last_Updated_By,
304 X_Last_Update_Login,
305 X_Created_By,
306 X_Creation_Date,
307 X_Title
308 );
309 OPEN C;
310 FETCH C INTO X_Rowid;
311 if (C%NOTFOUND) then
312 CLOSE C;
313 RAISE NO_DATA_FOUND;
314 end if;
315 CLOSE C;
316 --
317 -- add the person to the appropriate security lists
318 --
319 hr_utility.set_location('per_assignments_v7_pkg.insert_row',70);
320 hr_security_internal.add_to_person_list
321 (p_effective_date => sysdate
322 ,p_assignment_id => X_Assignment_Id);
323 --
324 hr_utility.set_location('per_assignments_v7_pkg.insert_row',80);
325 END Insert_Row;
326 --
327 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
328 X_Assignment_Id NUMBER,
329 X_Effective_Start_Date DATE,
330 X_Effective_End_Date DATE,
331 X_Business_Group_Id NUMBER,
332 X_Recruiter_Id NUMBER,
333 X_Grade_Id NUMBER,
334 X_Position_Id NUMBER,
335 X_Job_Id NUMBER,
336 X_Assignment_Status_Type_Id NUMBER,
337 X_Payroll_Id NUMBER,
338 X_Location_Id NUMBER,
342 X_Person_Id NUMBER,
339 X_Person_Referred_By_Id NUMBER,
340 X_Supervisor_Id NUMBER,
341 X_Special_Ceiling_Step_Id NUMBER,
343 X_Source_Organization_Id NUMBER,
344 X_Recruitment_Activity_Id NUMBER,
345 X_Organization_Id NUMBER,
346 X_People_Group_Id NUMBER,
347 X_Soft_Coding_Keyflex_Id NUMBER,
348 X_Vacancy_Id NUMBER,
349 X_Pay_Basis_Id NUMBER,
350 X_Assignment_Sequence NUMBER,
351 X_Assignment_Type VARCHAR2,
352 X_Primary_Flag VARCHAR2,
353 X_Application_Id NUMBER,
354 X_Assignment_Number VARCHAR2,
355 X_Change_Reason VARCHAR2,
356 X_Comment_Id NUMBER,
357 X_Date_Probation_End DATE,
358 X_Default_Code_Comb_Id NUMBER,
359 X_Employment_Category VARCHAR2,
360 X_Frequency VARCHAR2,
361 X_Internal_Address_Line VARCHAR2,
362 X_Manager_Flag VARCHAR2,
363 X_Normal_Hours NUMBER,
364 X_Perf_Review_Period NUMBER,
365 X_Perf_Review_Period_Frequency VARCHAR2,
366 X_Period_Of_Service_Id NUMBER,
367 X_Probation_Period NUMBER,
368 X_Probation_Unit VARCHAR2,
369 X_Sal_Review_Period NUMBER,
370 X_Sal_Review_Period_Frequency VARCHAR2,
371 X_Set_Of_Books_Id NUMBER,
372 X_Source_Type VARCHAR2,
373 X_Time_Normal_Finish VARCHAR2,
374 X_Time_Normal_Start VARCHAR2,
375 X_Ass_Attribute_Category VARCHAR2,
376 X_Ass_Attribute1 VARCHAR2,
377 X_Ass_Attribute2 VARCHAR2,
378 X_Ass_Attribute3 VARCHAR2,
379 X_Ass_Attribute4 VARCHAR2,
380 X_Ass_Attribute5 VARCHAR2,
381 X_Ass_Attribute6 VARCHAR2,
382 X_Ass_Attribute7 VARCHAR2,
383 X_Ass_Attribute8 VARCHAR2,
384 X_Ass_Attribute9 VARCHAR2,
385 X_Ass_Attribute10 VARCHAR2,
386 X_Ass_Attribute11 VARCHAR2,
387 X_Ass_Attribute12 VARCHAR2,
388 X_Ass_Attribute13 VARCHAR2,
389 X_Ass_Attribute14 VARCHAR2,
390 X_Ass_Attribute15 VARCHAR2,
391 X_Ass_Attribute16 VARCHAR2,
392 X_Ass_Attribute17 VARCHAR2,
393 X_Ass_Attribute18 VARCHAR2,
394 X_Ass_Attribute19 VARCHAR2,
395 X_Ass_Attribute20 VARCHAR2,
396 X_Ass_Attribute21 VARCHAR2,
397 X_Ass_Attribute22 VARCHAR2,
398 X_Ass_Attribute23 VARCHAR2,
399 X_Ass_Attribute24 VARCHAR2,
400 X_Ass_Attribute25 VARCHAR2,
401 X_Ass_Attribute26 VARCHAR2,
402 X_Ass_Attribute27 VARCHAR2,
403 X_Ass_Attribute28 VARCHAR2,
404 X_Ass_Attribute29 VARCHAR2,
405 X_Ass_Attribute30 VARCHAR2,
406 X_Title VARCHAR2
407 ) IS
408 CURSOR C IS
409 SELECT *
410 FROM PER_ASSIGNMENTS_F
411 WHERE rowid = X_Rowid
412 FOR UPDATE of Assignment_Id NOWAIT;
413 Recinfo C%ROWTYPE;
414 BEGIN
415 hr_utility.set_location('per_assignments_v7_pkg.lock_row',10);
416 OPEN C;
417 FETCH C INTO Recinfo;
418 if (C%NOTFOUND) then
419 CLOSE C;
420 RAISE NO_DATA_FOUND;
421 end if;
422 CLOSE C;
423 hr_utility.set_location('per_assignments_v7_pkg.lock_row',20);
424 if (
425 ( (Recinfo.assignment_id = X_Assignment_Id)
426 OR ( (Recinfo.assignment_id IS NULL)
427 AND (X_Assignment_Id IS NULL)))
428 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
429 OR ( (Recinfo.effective_start_date IS NULL)
430 AND (X_Effective_Start_Date IS NULL)))
434 AND ( (Recinfo.business_group_id = X_Business_Group_Id)
431 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
432 OR ( (Recinfo.effective_end_date IS NULL)
433 AND (X_Effective_End_Date IS NULL)))
435 OR ( (Recinfo.business_group_id IS NULL)
436 AND (X_Business_Group_Id IS NULL)))
437 AND ( (Recinfo.recruiter_id = X_Recruiter_Id)
438 OR ( (Recinfo.recruiter_id IS NULL)
439 AND (X_Recruiter_Id IS NULL)))
440 AND ( (Recinfo.grade_id = X_Grade_Id)
441 OR ( (Recinfo.grade_id IS NULL)
442 AND (X_Grade_Id IS NULL)))
443 AND ( (Recinfo.position_id = X_Position_Id)
444 OR ( (Recinfo.position_id IS NULL)
445 AND (X_Position_Id IS NULL)))
446 AND ( (Recinfo.job_id = X_Job_Id)
447 OR ( (Recinfo.job_id IS NULL)
448 AND (X_Job_Id IS NULL)))
449 AND ( (Recinfo.assignment_status_type_id = X_Assignment_Status_Type_Id)
450 OR ( (Recinfo.assignment_status_type_id IS NULL)
451 AND (X_Assignment_Status_Type_Id IS NULL)))
452 AND ( (Recinfo.payroll_id = X_Payroll_Id)
453 OR ( (Recinfo.payroll_id IS NULL)
454 AND (X_Payroll_Id IS NULL)))
455 AND ( (Recinfo.location_id = X_Location_Id)
456 OR ( (Recinfo.location_id IS NULL)
457 AND (X_Location_Id IS NULL)))
458 AND ( (Recinfo.person_referred_by_id = X_Person_Referred_By_Id)
459 OR ( (Recinfo.person_referred_by_id IS NULL)
460 AND (X_Person_Referred_By_Id IS NULL)))
461 AND ( (Recinfo.supervisor_id = X_Supervisor_Id)
462 OR ( (Recinfo.supervisor_id IS NULL)
463 AND (X_Supervisor_Id IS NULL)))
464 AND ( (Recinfo.special_ceiling_step_id = X_Special_Ceiling_Step_Id)
465 OR ( (Recinfo.special_ceiling_step_id IS NULL)
466 AND (X_Special_Ceiling_Step_Id IS NULL)))
467 AND ( (Recinfo.person_id = X_Person_Id)
468 OR ( (Recinfo.person_id IS NULL)
469 AND (X_Person_Id IS NULL)))
470 AND ( (Recinfo.recruitment_activity_id = X_Recruitment_Activity_Id)
471 OR ( (Recinfo.recruitment_activity_id IS NULL)
472 AND (X_Recruitment_Activity_Id IS NULL)))
473 AND ( (Recinfo.source_organization_id = X_Source_Organization_Id)
474 OR ( (Recinfo.source_organization_id IS NULL)
475 AND (X_Source_Organization_Id IS NULL)))
476 AND ( (Recinfo.organization_id = X_Organization_Id)
477 OR ( (Recinfo.organization_id IS NULL)
478 AND (X_Organization_Id IS NULL)))
479 AND ( (Recinfo.people_group_id = X_People_Group_Id)
480 OR ( (Recinfo.people_group_id IS NULL)
481 AND (X_People_Group_Id IS NULL)))
482 AND ( (Recinfo.soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id)
483 OR ( (Recinfo.soft_coding_keyflex_id IS NULL)
484 AND (X_Soft_Coding_Keyflex_Id IS NULL)))
485 AND ( (Recinfo.vacancy_id = X_Vacancy_Id)
486 OR ( (Recinfo.vacancy_id IS NULL)
487 AND (X_Vacancy_Id IS NULL)))
488 AND ( (Recinfo.pay_basis_id = X_Pay_Basis_Id)
489 OR ( (Recinfo.pay_basis_id IS NULL)
490 AND (X_Pay_Basis_Id IS NULL)))
491 AND ( (Recinfo.assignment_sequence = X_Assignment_Sequence)
492 OR ( (Recinfo.assignment_sequence IS NULL)
493 AND (X_Assignment_Sequence IS NULL)))
494 AND ( (Recinfo.assignment_type = X_Assignment_Type)
495 OR ( (Recinfo.assignment_type IS NULL)
496 AND (X_Assignment_Type IS NULL)))
497 AND ( (Recinfo.primary_flag = X_Primary_Flag)
498 OR ( (Recinfo.primary_flag IS NULL)
499 AND (X_Primary_Flag IS NULL)))
500 AND ( (Recinfo.application_id = X_Application_Id)
501 OR ( (Recinfo.application_id IS NULL)
502 AND (X_Application_Id IS NULL)))
503 AND ( (Recinfo.assignment_number = X_Assignment_Number)
504 OR ( (Recinfo.assignment_number IS NULL)
505 AND (X_Assignment_Number IS NULL)))
506 AND ( (Recinfo.change_reason = X_Change_Reason)
507 OR ( (Recinfo.change_reason IS NULL)
508 AND (X_Change_Reason IS NULL)))
509 AND ( (Recinfo.comment_id = X_Comment_Id)
510 OR ( (Recinfo.comment_id IS NULL)
511 AND (X_Comment_Id IS NULL)))
512 AND ( (Recinfo.date_probation_end = X_Date_Probation_End)
513 OR ( (Recinfo.date_probation_end IS NULL)
514 AND (X_Date_Probation_End IS NULL)))
515 AND ( (Recinfo.default_code_comb_id = X_Default_Code_Comb_Id)
516 OR ( (Recinfo.default_code_comb_id IS NULL)
517 AND (X_Default_Code_Comb_Id IS NULL)))
518 AND ( (Recinfo.employment_category = X_Employment_Category)
519 OR ( (Recinfo.employment_category IS NULL)
520 AND (X_Employment_Category IS NULL)))
521 AND ( (Recinfo.frequency = X_Frequency)
522 OR ( (Recinfo.frequency IS NULL)
523 AND (X_Frequency IS NULL)))
524 AND ( (Recinfo.internal_address_line = X_Internal_Address_Line)
525 OR ( (Recinfo.internal_address_line IS NULL)
526 AND (X_Internal_Address_Line IS NULL)))
527 AND ( (Recinfo.manager_flag = X_Manager_Flag)
528 OR ( (Recinfo.manager_flag IS NULL)
529 AND (X_Manager_Flag IS NULL)))
530 AND ( (Recinfo.normal_hours = X_Normal_Hours)
534 OR ( (Recinfo.perf_review_period IS NULL)
531 OR ( (Recinfo.normal_hours IS NULL)
532 AND (X_Normal_Hours IS NULL)))
533 AND ( (Recinfo.perf_review_period = X_Perf_Review_Period)
535 AND (X_Perf_Review_Period IS NULL)))
536 AND ( (Recinfo.perf_review_period_frequency =
537 X_Perf_Review_Period_Frequency)
538 OR ( (Recinfo.perf_review_period_frequency IS NULL)
539 AND (X_Perf_Review_Period_Frequency IS NULL)))
540 AND ( (Recinfo.period_of_service_id = X_Period_Of_Service_Id)
541 OR ( (Recinfo.period_of_service_id IS NULL)
542 AND (X_Period_Of_Service_Id IS NULL)))
543 AND ( (Recinfo.probation_period = X_Probation_Period)
544 OR ( (Recinfo.probation_period IS NULL)
545 AND (X_Probation_Period IS NULL)))
546 AND ( (Recinfo.probation_unit = X_Probation_Unit)
547 OR ( (Recinfo.probation_unit IS NULL)
548 AND (X_Probation_Unit IS NULL)))
549 AND ( (Recinfo.sal_review_period = X_Sal_Review_Period)
550 OR ( (Recinfo.sal_review_period IS NULL)
551 AND (X_Sal_Review_Period IS NULL)))
552 AND ( (Recinfo.sal_review_period_frequency =
553 X_Sal_Review_Period_Frequency)
554 OR ( (Recinfo.sal_review_period_frequency IS NULL)
555 AND (X_Sal_Review_Period_Frequency IS NULL)))
556 AND ( (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
557 OR ( (Recinfo.set_of_books_id IS NULL)
558 AND (X_Set_Of_Books_Id IS NULL)))
559 AND ( (Recinfo.source_type = X_Source_Type)
560 OR ( (Recinfo.source_type IS NULL)
561 AND (X_Source_Type IS NULL)))
562 AND ( (Recinfo.time_normal_finish = X_Time_Normal_Finish)
563 OR ( (Recinfo.time_normal_finish IS NULL)
564 AND (X_Time_Normal_Finish IS NULL)))
565 AND ( (Recinfo.time_normal_start = X_Time_Normal_Start)
566 OR ( (Recinfo.time_normal_start IS NULL)
567 AND (X_Time_Normal_Start IS NULL)))
568 AND ( (Recinfo.ass_attribute_category = X_Ass_Attribute_Category)
569 OR ( (Recinfo.ass_attribute_category IS NULL)
570 AND (X_Ass_Attribute_Category IS NULL)))
571 )
572 then
573 if (
574 ( (Recinfo.ass_attribute1 = X_Ass_Attribute1)
575 OR ( (Recinfo.ass_attribute1 IS NULL)
576 AND (X_Ass_Attribute1 IS NULL)))
577 AND ( (Recinfo.ass_attribute2 = X_Ass_Attribute2)
578 OR ( (Recinfo.ass_attribute2 IS NULL)
579 AND (X_Ass_Attribute2 IS NULL)))
580 AND ( (Recinfo.ass_attribute3 = X_Ass_Attribute3)
581 OR ( (Recinfo.ass_attribute3 IS NULL)
582 AND (X_Ass_Attribute3 IS NULL)))
583 AND ( (Recinfo.ass_attribute4 = X_Ass_Attribute4)
584 OR ( (Recinfo.ass_attribute4 IS NULL)
585 AND (X_Ass_Attribute4 IS NULL)))
586 AND ( (Recinfo.ass_attribute5 = X_Ass_Attribute5)
587 OR ( (Recinfo.ass_attribute5 IS NULL)
588 AND (X_Ass_Attribute5 IS NULL)))
589 AND ( (Recinfo.ass_attribute6 = X_Ass_Attribute6)
590 OR ( (Recinfo.ass_attribute6 IS NULL)
591 AND (X_Ass_Attribute6 IS NULL)))
592 AND ( (Recinfo.ass_attribute7 = X_Ass_Attribute7)
593 OR ( (Recinfo.ass_attribute7 IS NULL)
594 AND (X_Ass_Attribute7 IS NULL)))
595 AND ( (Recinfo.ass_attribute8 = X_Ass_Attribute8)
596 OR ( (Recinfo.ass_attribute8 IS NULL)
597 AND (X_Ass_Attribute8 IS NULL)))
598 AND ( (Recinfo.ass_attribute9 = X_Ass_Attribute9)
599 OR ( (Recinfo.ass_attribute9 IS NULL)
600 AND (X_Ass_Attribute9 IS NULL)))
601 AND ( (Recinfo.ass_attribute10 = X_Ass_Attribute10)
602 OR ( (Recinfo.ass_attribute10 IS NULL)
603 AND (X_Ass_Attribute10 IS NULL)))
604 AND ( (Recinfo.ass_attribute11 = X_Ass_Attribute11)
605 OR ( (Recinfo.ass_attribute11 IS NULL)
606 AND (X_Ass_Attribute11 IS NULL)))
607 AND ( (Recinfo.ass_attribute12 = X_Ass_Attribute12)
608 OR ( (Recinfo.ass_attribute12 IS NULL)
609 AND (X_Ass_Attribute12 IS NULL)))
610 AND ( (Recinfo.ass_attribute13 = X_Ass_Attribute13)
611 OR ( (Recinfo.ass_attribute13 IS NULL)
612 AND (X_Ass_Attribute13 IS NULL)))
613 AND ( (Recinfo.ass_attribute14 = X_Ass_Attribute14)
614 OR ( (Recinfo.ass_attribute14 IS NULL)
615 AND (X_Ass_Attribute14 IS NULL)))
616 AND ( (Recinfo.ass_attribute15 = X_Ass_Attribute15)
617 OR ( (Recinfo.ass_attribute15 IS NULL)
618 AND (X_Ass_Attribute15 IS NULL)))
619 AND ( (Recinfo.ass_attribute16 = X_Ass_Attribute16)
620 OR ( (Recinfo.ass_attribute16 IS NULL)
621 AND (X_Ass_Attribute16 IS NULL)))
622 AND ( (Recinfo.ass_attribute17 = X_Ass_Attribute17)
623 OR ( (Recinfo.ass_attribute17 IS NULL)
624 AND (X_Ass_Attribute17 IS NULL)))
625 AND ( (Recinfo.ass_attribute18 = X_Ass_Attribute18)
626 OR ( (Recinfo.ass_attribute18 IS NULL)
627 AND (X_Ass_Attribute18 IS NULL)))
628 AND ( (Recinfo.ass_attribute19 = X_Ass_Attribute19)
629 OR ( (Recinfo.ass_attribute19 IS NULL)
630 AND (X_Ass_Attribute19 IS NULL)))
634 AND ( (Recinfo.ass_attribute21 = X_Ass_Attribute21)
631 AND ( (Recinfo.ass_attribute20 = X_Ass_Attribute20)
632 OR ( (Recinfo.ass_attribute20 IS NULL)
633 AND (X_Ass_Attribute20 IS NULL)))
635 OR ( (Recinfo.ass_attribute21 IS NULL)
636 AND (X_Ass_Attribute21 IS NULL)))
637 AND ( (Recinfo.ass_attribute22 = X_Ass_Attribute22)
638 OR ( (Recinfo.ass_attribute22 IS NULL)
639 AND (X_Ass_Attribute22 IS NULL)))
640 AND ( (Recinfo.ass_attribute23 = X_Ass_Attribute23)
641 OR ( (Recinfo.ass_attribute23 IS NULL)
642 AND (X_Ass_Attribute23 IS NULL)))
643 AND ( (Recinfo.ass_attribute24 = X_Ass_Attribute24)
644 OR ( (Recinfo.ass_attribute24 IS NULL)
645 AND (X_Ass_Attribute24 IS NULL)))
646 AND ( (Recinfo.ass_attribute25 = X_Ass_Attribute25)
647 OR ( (Recinfo.ass_attribute25 IS NULL)
648 AND (X_Ass_Attribute25 IS NULL)))
649 AND ( (Recinfo.ass_attribute26 = X_Ass_Attribute26)
650 OR ( (Recinfo.ass_attribute26 IS NULL)
651 AND (X_Ass_Attribute26 IS NULL)))
652 AND ( (Recinfo.ass_attribute27 = X_Ass_Attribute27)
653 OR ( (Recinfo.ass_attribute27 IS NULL)
654 AND (X_Ass_Attribute27 IS NULL)))
655 AND ( (Recinfo.ass_attribute28 = X_Ass_Attribute28)
656 OR ( (Recinfo.ass_attribute28 IS NULL)
657 AND (X_Ass_Attribute28 IS NULL)))
658 AND ( (Recinfo.ass_attribute29 = X_Ass_Attribute29)
659 OR ( (Recinfo.ass_attribute29 IS NULL)
660 AND (X_Ass_Attribute29 IS NULL)))
661 AND ( (Recinfo.ass_attribute30 = X_Ass_Attribute30)
662 OR ( (Recinfo.ass_attribute30 IS NULL)
663 AND (X_Ass_Attribute30 IS NULL)))
664 AND ( (Recinfo.title = X_Title)
665 OR ( (Recinfo.title IS NULL)
666 AND (X_Title IS NULL)))
667 ) then
668 return;
669 end if;
670 else
671 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
672 APP_EXCEPTION.RAISE_EXCEPTION;
673 end if;
674 hr_utility.set_location('per_assignments_v7_pkg.lock_row',30);
675 END Lock_Row;
676 PROCEDURE Update_Row(X_Rowid VARCHAR2,
677 X_Assignment_Id NUMBER,
678 X_Effective_Start_Date DATE,
679 X_Effective_End_Date DATE,
680 X_Business_Group_Id NUMBER,
681 X_Recruiter_Id NUMBER,
682 X_Grade_Id NUMBER,
683 X_Position_Id NUMBER,
684 X_Job_Id NUMBER,
685 X_Assignment_Status_Type_Id NUMBER,
686 X_Payroll_Id NUMBER,
687 X_Location_Id NUMBER,
688 X_Person_Referred_By_Id NUMBER,
689 X_Supervisor_Id NUMBER,
690 X_Special_Ceiling_Step_Id NUMBER,
691 X_Person_Id NUMBER,
692 X_Recruitment_Activity_Id NUMBER,
693 X_Source_Organization_Id NUMBER,
694 X_Organization_Id NUMBER,
695 X_People_Group_Id NUMBER,
696 X_Soft_Coding_Keyflex_Id NUMBER,
697 X_Vacancy_Id NUMBER,
698 X_Pay_Basis_Id NUMBER,
699 X_Assignment_Sequence NUMBER,
700 X_Assignment_Type VARCHAR2,
701 X_Primary_Flag VARCHAR2,
702 X_Application_Id NUMBER,
703 X_Assignment_Number VARCHAR2,
704 X_Change_Reason VARCHAR2,
705 X_Comment_Id NUMBER,
706 X_Date_Probation_End DATE,
707 X_Default_Code_Comb_Id NUMBER,
708 X_Employment_Category VARCHAR2,
709 X_Frequency VARCHAR2,
710 X_Internal_Address_Line VARCHAR2,
711 X_Manager_Flag VARCHAR2,
712 X_Normal_Hours NUMBER,
713 X_Perf_Review_Period NUMBER,
714 X_Perf_Review_Period_Frequency VARCHAR2,
715 X_Period_Of_Service_Id NUMBER,
716 X_Probation_Period NUMBER,
717 X_Probation_Unit VARCHAR2,
718 X_Sal_Review_Period NUMBER,
719 X_Sal_Review_Period_Frequency VARCHAR2,
720 X_Set_Of_Books_Id NUMBER,
721 X_Source_Type VARCHAR2,
722 X_Time_Normal_Finish VARCHAR2,
723 X_Time_Normal_Start VARCHAR2,
724 X_Ass_Attribute_Category VARCHAR2,
728 X_Ass_Attribute4 VARCHAR2,
725 X_Ass_Attribute1 VARCHAR2,
726 X_Ass_Attribute2 VARCHAR2,
727 X_Ass_Attribute3 VARCHAR2,
729 X_Ass_Attribute5 VARCHAR2,
730 X_Ass_Attribute6 VARCHAR2,
731 X_Ass_Attribute7 VARCHAR2,
732 X_Ass_Attribute8 VARCHAR2,
733 X_Ass_Attribute9 VARCHAR2,
734 X_Ass_Attribute10 VARCHAR2,
735 X_Ass_Attribute11 VARCHAR2,
736 X_Ass_Attribute12 VARCHAR2,
737 X_Ass_Attribute13 VARCHAR2,
738 X_Ass_Attribute14 VARCHAR2,
739 X_Ass_Attribute15 VARCHAR2,
740 X_Ass_Attribute16 VARCHAR2,
741 X_Ass_Attribute17 VARCHAR2,
742 X_Ass_Attribute18 VARCHAR2,
743 X_Ass_Attribute19 VARCHAR2,
744 X_Ass_Attribute20 VARCHAR2,
745 X_Ass_Attribute21 VARCHAR2,
746 X_Ass_Attribute22 VARCHAR2,
747 X_Ass_Attribute23 VARCHAR2,
748 X_Ass_Attribute24 VARCHAR2,
749 X_Ass_Attribute25 VARCHAR2,
750 X_Ass_Attribute26 VARCHAR2,
751 X_Ass_Attribute27 VARCHAR2,
752 X_Ass_Attribute28 VARCHAR2,
753 X_Ass_Attribute29 VARCHAR2,
754 X_Ass_Attribute30 VARCHAR2,
755 X_Last_Update_Date DATE,
756 X_Last_Updated_By NUMBER,
757 X_Last_Update_Login NUMBER,
758 X_Title VARCHAR2,
759 X_Current_application_id NUMBER DEFAULT NULL
760 ) IS
761 --
762 cursor org is
763 select organization_id
764 from per_all_assignments_f
765 where assignment_id=X_Assignment_Id
766 and X_Effective_Start_Date between effective_start_date
767 and effective_end_date;
768 --
769 l_old_org_id per_all_assignments_f.organization_id%TYPE;
770 --
771 BEGIN
772 hr_utility.set_location('per_assignments_v7_pkg.update_row',10);
773 if X_Current_application_id = 810
774 then
775 hr_utility.set_location('per_assignments_v7_pkg.update_row',20);
776 per_assignments_v7_pkg.get_future_changes(X_assignment_id
777 ,X_effective_start_date
778 ,X_Current_application_id);
779 hr_utility.set_location('per_assignments_v7_pkg.update_row',30);
780 end if;
781 --
782 -- get the current organization
783 --
784 open org;
785 fetch org into l_old_org_id;
786 if org%NOTFOUND then
787 close org;
788 else
789 hr_utility.set_location('per_assignments_v7_pkg.update_row',40);
790 close org;
791 end if;
792 --
793 hr_utility.set_location('per_assignments_v7_pkg.update_row',50);
794 UPDATE PER_ASSIGNMENTS_F
795 SET
796 assignment_id = X_Assignment_Id,
797 effective_start_date = X_Effective_Start_Date,
798 effective_end_date = X_Effective_End_Date,
799 business_group_id = X_Business_Group_Id,
800 recruiter_id = X_Recruiter_Id,
801 grade_id = X_Grade_Id,
802 position_id = X_Position_Id,
803 job_id = X_Job_Id,
804 assignment_status_type_id = X_Assignment_Status_Type_Id,
805 payroll_id = X_Payroll_Id,
806 location_id = X_Location_Id,
807 person_referred_by_id = X_Person_Referred_By_Id,
808 supervisor_id = X_Supervisor_Id,
809 special_ceiling_step_id = X_Special_Ceiling_Step_Id,
810 person_id = X_Person_Id,
811 recruitment_activity_id = X_Recruitment_Activity_Id,
812 source_organization_id = X_Source_Organization_Id,
813 organization_id = X_Organization_Id,
814 people_group_id = X_People_Group_Id,
815 soft_coding_keyflex_id = X_Soft_Coding_Keyflex_Id,
816 vacancy_id = X_Vacancy_Id,
817 pay_basis_id = X_Pay_Basis_Id,
818 assignment_sequence = X_Assignment_Sequence,
819 assignment_type = X_Assignment_Type,
820 primary_flag = X_Primary_Flag,
821 application_id = X_Application_Id,
822 assignment_number = X_Assignment_Number,
823 change_reason = X_Change_Reason,
827 employment_category = X_Employment_Category,
824 comment_id = X_Comment_Id,
825 date_probation_end = X_Date_Probation_End,
826 default_code_comb_id = X_Default_Code_Comb_Id,
828 frequency = X_Frequency,
829 internal_address_line = X_Internal_Address_Line,
830 manager_flag = X_Manager_Flag,
831 normal_hours = X_Normal_Hours,
832 perf_review_period = X_Perf_Review_Period,
833 perf_review_period_frequency = X_Perf_Review_Period_Frequency,
834 period_of_service_id = X_Period_Of_Service_Id,
835 probation_period = X_Probation_Period,
836 probation_unit = X_Probation_Unit,
837 sal_review_period = X_Sal_Review_Period,
838 sal_review_period_frequency = X_Sal_Review_Period_Frequency
839 ,
840 set_of_books_id = X_Set_Of_Books_Id,
841 source_type = X_Source_Type,
842 time_normal_finish = X_Time_Normal_Finish,
843 time_normal_start = X_Time_Normal_Start,
844 ass_attribute_category = X_Ass_Attribute_Category,
845 ass_attribute1 = X_Ass_Attribute1,
846 ass_attribute2 = X_Ass_Attribute2,
847 ass_attribute3 = X_Ass_Attribute3,
848 ass_attribute4 = X_Ass_Attribute4,
849 ass_attribute5 = X_Ass_Attribute5,
850 ass_attribute6 = X_Ass_Attribute6,
851 ass_attribute7 = X_Ass_Attribute7,
852 ass_attribute8 = X_Ass_Attribute8,
853 ass_attribute9 = X_Ass_Attribute9,
854 ass_attribute10 = X_Ass_Attribute10,
855 ass_attribute11 = X_Ass_Attribute11,
856 ass_attribute12 = X_Ass_Attribute12,
857 ass_attribute13 = X_Ass_Attribute13,
858 ass_attribute14 = X_Ass_Attribute14,
859 ass_attribute15 = X_Ass_Attribute15,
860 ass_attribute16 = X_Ass_Attribute16,
861 ass_attribute17 = X_Ass_Attribute17,
862 ass_attribute18 = X_Ass_Attribute18,
863 ass_attribute19 = X_Ass_Attribute19,
864 ass_attribute20 = X_Ass_Attribute20,
865 ass_attribute21 = X_Ass_Attribute21,
866 ass_attribute22 = X_Ass_Attribute22,
867 ass_attribute23 = X_Ass_Attribute23,
868 ass_attribute24 = X_Ass_Attribute24,
869 ass_attribute25 = X_Ass_Attribute25,
870 ass_attribute26 = X_Ass_Attribute26,
871 ass_attribute27 = X_Ass_Attribute27,
872 ass_attribute28 = X_Ass_Attribute28,
873 ass_attribute29 = X_Ass_Attribute29,
874 ass_attribute30 = X_Ass_Attribute30,
875 last_update_date = X_Last_Update_Date,
876 last_updated_by = X_Last_Updated_By,
877 last_update_login = X_Last_Update_Login,
878 title = X_Title
879 WHERE rowid = X_rowid;
880 if (SQL%NOTFOUND) then
881 RAISE NO_DATA_FOUND;
882 end if;
883 hr_utility.set_location('per_assignments_v7_pkg.update_row',60);
884 --
885 -- update the security lists
886 --
887 if (l_old_org_id = X_Business_Group_Id
888 and X_Organization_Id <> X_Business_Group_Id) then
889 hr_utility.set_location('per_assignments_v7_pkg.update_row',70);
890 hr_security_internal.clear_from_person_list(X_Person_Id);
891 end if;
892 hr_utility.set_location('per_assignments_v7_pkg.update_row',80);
893 --2468956: change the date from sysdate to x_effective_start_date in following
894 hr_security_internal.add_to_person_list(X_Effective_Start_Date,X_Assignment_Id);
895 hr_utility.set_location('per_assignments_v7_pkg.update_row',90);
896 --
897 END Update_Row;
898 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
899 BEGIN
900 hr_utility.set_location('per_assignments_v7_pkg.delete_row',10);
901 DELETE FROM PER_ASSIGNMENTS_F
902 WHERE rowid = X_Rowid;
903 if (SQL%NOTFOUND) then
904 RAISE NO_DATA_FOUND;
905 end if;
906 hr_utility.set_location('per_assignments_v7_pkg.delete_row',20);
907 END Delete_Row;
908 --
909 PROCEDURE get_location_address (X_location_id NUMBER
910 ,X_Address_Line_1 IN OUT NOCOPY VARCHAR2
911 ,X_Address_Line_2 IN OUT NOCOPY VARCHAR2
912 ,X_Address_Line_3 IN OUT NOCOPY VARCHAR2
913 ,X_Bill_to_site_flag IN OUT NOCOPY VARCHAR2
914 ,X_Country IN OUT NOCOPY VARCHAR2
918 ,X_Inactive_date IN OUT NOCOPY DATE
915 ,X_Description IN OUT NOCOPY VARCHAR2
916 ,X_Designated_receiver_id IN OUT NOCOPY NUMBER
917 ,X_In_organization_flag IN OUT NOCOPY VARCHAR2
919 ,X_Inventory_organization_id IN OUT NOCOPY NUMBER
920 ,X_Office_site_flag IN OUT NOCOPY VARCHAR2
921 ,X_Postal_Code IN OUT NOCOPY VARCHAR2
922 ,X_Receiving_site_flag IN OUT NOCOPY VARCHAR2
923 ,X_Region_1 IN OUT NOCOPY VARCHAR2
924 ,X_Region_2 IN OUT NOCOPY VARCHAR2
925 ,X_Region_3 IN OUT NOCOPY VARCHAR2
926 ,X_Ship_to_location_id IN OUT NOCOPY NUMBER
927 ,X_Ship_to_site_flag IN OUT NOCOPY VARCHAR2
928 ,X_Style IN OUT NOCOPY VARCHAR2
929 ,X_Tax_name IN OUT NOCOPY VARCHAR2
930 ,X_Telephone_number_1 IN OUT NOCOPY VARCHAR2
931 ,X_Telephone_number_2 IN OUT NOCOPY VARCHAR2
932 ,X_Telephone_number_3 IN OUT NOCOPY VARCHAR2
933 ,X_Town_or_city IN OUT NOCOPY VARCHAR2
934 ,X_Attribute_category IN OUT NOCOPY VARCHAR2
935 ,X_Attribute1 IN OUT NOCOPY VARCHAR2
936 ,X_Attribute2 IN OUT NOCOPY VARCHAR2
937 ,X_Attribute3 IN OUT NOCOPY VARCHAR2
938 ,X_Attribute4 IN OUT NOCOPY VARCHAR2
939 ,X_Attribute5 IN OUT NOCOPY VARCHAR2
940 ,X_Attribute6 IN OUT NOCOPY VARCHAR2
941 ,X_Attribute7 IN OUT NOCOPY VARCHAR2
942 ,X_Attribute8 IN OUT NOCOPY VARCHAR2
943 ,X_Attribute9 IN OUT NOCOPY VARCHAR2
944 ,X_Attribute10 IN OUT NOCOPY VARCHAR2
945 ,X_Attribute11 IN OUT NOCOPY VARCHAR2
946 ,X_Attribute12 IN OUT NOCOPY VARCHAR2
947 ,X_Attribute13 IN OUT NOCOPY VARCHAR2
948 ,X_Attribute14 IN OUT NOCOPY VARCHAR2
949 ,X_Attribute15 IN OUT NOCOPY VARCHAR2
950 ,X_Attribute16 IN OUT NOCOPY VARCHAR2
951 ,X_Attribute17 IN OUT NOCOPY VARCHAR2
952 ,X_Attribute18 IN OUT NOCOPY VARCHAR2
953 ,X_Attribute19 IN OUT NOCOPY VARCHAR2
954 ,X_Attribute20 IN OUT NOCOPY VARCHAR2)
955 IS
956 cursor location
957 IS
958 select Address_Line_1
959 ,Address_Line_2
960 ,Address_Line_3
961 ,bill_to_site_flag
962 ,Country
963 ,Description
964 ,Designated_receiver_id
965 ,In_organization_flag
966 ,Inactive_date
967 ,Inventory_organization_id
968 ,Office_site_flag
969 ,Postal_Code
970 ,Receiving_site_flag
971 ,Region_1
972 ,Region_2
973 ,Region_3
974 ,Ship_to_location_id
975 ,Ship_to_site_flag
976 ,Style
977 ,Tax_name
978 ,Telephone_number_1
979 ,Telephone_number_2
980 ,Telephone_number_3
981 ,Town_or_city
982 ,Attribute_category
983 ,Attribute1
984 ,Attribute2
985 ,Attribute3
986 ,Attribute4
987 ,Attribute5
988 ,Attribute6
989 ,Attribute7
990 ,Attribute8
991 ,Attribute9
992 ,Attribute10
993 ,Attribute11
994 ,Attribute12
995 ,Attribute13
996 ,Attribute14
997 ,Attribute15
998 ,Attribute16
999 ,Attribute17
1000 ,Attribute18
1001 ,Attribute19
1002 ,Attribute20
1003 from hr_locations
1004 where location_id = X_location_id;
1005 --
1006 BEGIN
1007 open location;
1008 fetch location into X_Address_Line_1
1009 ,X_Address_Line_2
1010 ,X_Address_Line_3
1011 ,X_Bill_to_site_flag
1012 ,X_Country
1013 ,X_Description
1014 ,X_Designated_receiver_id
1015 ,X_In_organization_flag
1016 ,X_Inactive_date
1017 ,X_Inventory_organization_id
1018 ,X_Office_site_flag
1019 ,X_Postal_Code
1020 ,X_Receiving_site_flag
1021 ,X_Region_1
1022 ,X_Region_2
1023 ,X_Region_3
1024 ,X_Ship_to_location_id
1025 ,X_Ship_to_site_flag
1026 ,X_Style
1027 ,X_Tax_name
1028 ,X_Telephone_number_1
1029 ,X_Telephone_number_2
1030 ,X_Telephone_number_3
1031 ,X_Town_or_city
1032 ,X_Attribute_category
1033 ,X_Attribute1
1034 ,X_Attribute2
1035 ,X_Attribute3
1039 ,X_Attribute7
1036 ,X_Attribute4
1037 ,X_Attribute5
1038 ,X_Attribute6
1040 ,X_Attribute8
1041 ,X_Attribute9
1042 ,X_Attribute10
1043 ,X_Attribute11
1044 ,X_Attribute12
1045 ,X_Attribute13
1046 ,X_Attribute14
1047 ,X_Attribute15
1048 ,X_Attribute16
1049 ,X_Attribute17
1050 ,X_Attribute18
1051 ,X_Attribute19
1052 ,X_Attribute20;
1053 close location;
1054 END;
1055 procedure check_primary(X_person_id NUMBER
1056 ,X_primary_flag IN OUT NOCOPY VARCHAR2)
1057 IS
1058 cursor primary_exists
1059 is
1060 select 'Y'
1061 from sys.dual
1062 where exists (select 1 from
1063 per_assignments_f
1064 where person_id =X_person_id
1065 and sysdate between effective_start_date
1066 and effective_end_date);
1067 --
1068 l_dummy VARCHAR2(1);
1069 begin
1070 open primary_exists;
1071 fetch primary_exists into l_dummy;
1072 if primary_exists%found
1073 then
1074 X_primary_flag := 'N';
1075 else
1076 X_primary_flag := 'Y';
1077 end if;
1078 close primary_exists;
1079 end;
1080 --
1081 procedure get_previous_changes(X_assignment_id NUMBER
1082 ,X_effective_start_date DATE
1083 ,X_current_application_id NUMBER DEFAULT NULL) is
1084 --
1085 cursor get_previous_changes is
1086 select 'Y'
1087 from per_assignments_f p
1088 where p.effective_start_date < x_effective_start_date
1089 and p.effective_end_date <> hr_general.end_of_time
1090 and p.assignment_id = x_assignment_id
1091 and p.effective_start_date = (select max(p1.effective_start_date)
1092 from per_assignments_f p1
1093 where p1.assignment_id = p.assignment_id);
1094 --
1095 l_dummy VARCHAR2(1);
1096 --
1097 begin
1098 hr_utility.set_location('Entering: get_previous_changes',10);
1099 open get_previous_changes;
1100 fetch get_previous_changes into l_dummy;
1101 if get_previous_changes%found
1102 then
1103 close get_previous_changes;
1104 fnd_message.set_name('PER','HR_6839_EMP_REF_DATE_CHG');
1105 app_exception.raise_exception;
1106 end if;
1107 close get_previous_changes;
1108 hr_utility.set_location('Leaving: get_previous_changes',20);
1109 end;
1110 --
1111 procedure get_future_changes (x_assignment_id NUMBER
1112 ,x_effective_start_date DATE
1113 ,x_current_application_id NUMBER DEFAULT NULL) is
1114 cursor get_future_changes is
1115 select 'Y'
1116 from per_assignments_f p
1117 where trunc(p.effective_start_date) > trunc(x_effective_start_date) -- #1862029
1118 and p.assignment_id = x_assignment_id;
1119 --
1120 l_dummy VARCHAR2(1);
1121 --
1122 begin
1123 open get_future_changes;
1124 fetch get_future_changes into l_dummy;
1125 if get_future_changes%found
1126 then
1127 -- VT 09/22/99 #999960 there is no need to check for Oracle Project
1128 -- #1394091
1129 -- if nvl(x_current_application_id,0) <> 275 then
1130 close get_future_changes;
1131 fnd_message.set_name('PER','HR_7510_PER_FUT_CHANGE');
1132 app_exception.raise_exception;
1133 -- end if;
1134 --
1135 end if;
1136 close get_future_changes;
1137 end;
1138 END PER_ASSIGNMENTS_V7_PKG;