1 Package Body per_asg_bus3 as
2 /* $Header: peasgrhi.pkb 120.44.12020000.3 2013/02/13 07:11:42 srannama ship $ */
3 --
4 --
5 -- ----------------------------------------------------------------------------
6 -- | Private Global Definitions |
7 -- ----------------------------------------------------------------------------
8 --
9 g_package varchar2(33) := ' per_asg_bus3.'; -- Global package name
10 g_debug boolean := hr_utility.debug_enabled;
11 --
12 --
13 -- ---------------------------------------------------------------------------
14 -- |--------------------< chk_cagr_grade_def_id >---------------------------|
15 -- ---------------------------------------------------------------------------
16 --
17 procedure chk_cagr_grade_def_id
18 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
19 ,p_effective_date in date
20 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
21 ,p_cagr_grade_def_id in per_all_assignments_f.cagr_grade_def_id%TYPE
22 ,p_collective_agreement_id in per_all_assignments_f.collective_agreement_id%TYPE
23 ,p_cagr_id_flex_num in per_all_assignments_f.cagr_id_flex_num%TYPE
24 )
25 is
26 --
27 l_proc varchar2(72) := g_package||'chk_cagr_grade_def_id';
28 l_api_updating boolean;
29 l_exists varchar2(1);
30 l_dynamic_insert_allowed varchar2(1);
31 l_cagr_grade_structure_id number;
32 --
36 where pcg.id_flex_num = p_cagr_id_flex_num
33 cursor csr_in_per_cagr_grades_def is
34 select null
35 from per_cagr_grades_def pcg
37 and pcg.cagr_grade_def_id = p_cagr_grade_def_id;
38 --
39 cursor csr_in_cagr_grade_structs is
40 select dynamic_insert_allowed, cagr_grade_structure_id
41 from per_cagr_grade_structures cgs
42 where cgs.id_flex_num = p_cagr_id_flex_num
43 and cgs.collective_agreement_id = p_collective_agreement_id;
44 --
45 cursor csr_in_cagr_grades is
46 select null
47 from per_cagr_grades pcg
48 where pcg.cagr_grade_def_id = p_cagr_grade_def_id
49 and pcg.cagr_grade_structure_id = l_cagr_grade_structure_id;
50 --
51 begin
52 hr_utility.set_location('Entering:'|| l_proc, 10);
53 --
54 if hr_multi_message.no_exclusive_error
55 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.CAGR_ID_FLEX_NUM'
56 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
57 ) then
58 --
59 -- Check mandatory parameters have been set
60 --
61 hr_api.mandatory_arg_error
62 (p_api_name => l_proc
63 ,p_argument => 'effective_date'
64 ,p_argument_value => p_effective_date
65 );
66 --
67 hr_utility.set_location(l_proc, 20);
68 --
69 If p_cagr_id_flex_num is null and p_cagr_grade_def_id is not null THEN
70 -- Error, must have id_flex_num
71 -- msg There must be a collective_agreement grade structure specified with a collective agreement grade definition
72 hr_utility.set_location(l_proc, 50);
73 hr_utility.set_message(800, 'PER_52806_CAGR_STRUCT_GRADE');
74 hr_utility.raise_error;
75 End if;
76 --
77 -- Only proceed with validation if :
78 -- a) The cagr_id_flex_num is changing
79 -- b) The grade_def_id is changing or new
80 --
81 l_api_updating := per_asg_shd.api_updating
82 (p_assignment_id => p_assignment_id
83 ,p_effective_date => p_effective_date
84 ,p_object_version_number => p_object_version_number);
85 hr_utility.set_location(l_proc, 30);
86 --
87 if ((l_api_updating
88 and (p_cagr_grade_def_id is not null)
89 and nvl(per_asg_shd.g_old_rec.cagr_id_flex_num, hr_api.g_number)
90 <> nvl(p_cagr_id_flex_num, hr_api.g_number))
91 or
92 (NOT l_api_updating and p_cagr_grade_def_id is not null)
93 or
94 (l_api_updating and (p_cagr_grade_def_id is not null) and
95 nvl(per_asg_shd.g_old_rec.cagr_grade_def_id, hr_api.g_number)
96 <> nvl(p_cagr_grade_def_id, hr_api.g_number))) THEN
97 --
98 hr_utility.set_location(l_proc, 40);
99 --
100 --
101 --It must be in per_cagr_grades_def
102 --
103 Open csr_in_per_cagr_grades_def;
104 Fetch csr_in_per_cagr_grades_def into l_exists;
105 if csr_in_per_cagr_grades_def%notfound then
106 close csr_in_per_cagr_grades_def;
107 -- msg The given grade definition does not exist for the grade structure
108 hr_utility.set_location(l_proc, 50);
109 hr_utility.set_message(800, 'PER_52807_GRADE_NOT_STRUCT');
110 hr_utility.raise_error;
111 Else
112 close csr_in_per_cagr_grades_def;
113 End If;
114 -- It must exist in per_cagr_grade_structures
115 Open csr_in_cagr_grade_structs;
116 Fetch csr_in_cagr_grade_structs Into l_dynamic_insert_allowed,
117 l_cagr_grade_structure_id;
118 If csr_in_cagr_grade_structs%notfound then
119 --
120 -- The combination is invalid
121 Close csr_in_cagr_grade_structs;
122 hr_utility.set_location(l_proc, 60);
123 -- msg This grade structure / collective agreement comb does not exist
124 hr_utility.set_message(800, 'PER_52808_INVALID_CAGR_GRADE');
125 hr_utility.raise_error;
126 Else
127 Close csr_in_cagr_grade_structs;
128 --
129 If l_dynamic_insert_allowed = 'N' THEN
130 -- Check that the grade id is a reference grade.
131 Open csr_in_cagr_grades;
132 Fetch csr_in_cagr_grades into l_exists;
133 If csr_in_cagr_grades%notfound THEN
134 -- msg This grade structure only allows selection of reference grades, you cannot create grades.
135 hr_utility.set_message(800, 'PER_52809_CAGR_ONLY_SELECT');
136 hr_utility.raise_error;
137 Else
138 Close csr_in_cagr_grades;
139 End If;
140 End If;
141 End if;
142 End if;
143 End If;
144 hr_utility.set_location(' Leaving:'|| l_proc, 90);
145 end chk_cagr_grade_def_id;
146 --
147 -- ---------------------------------------------------------------------------
148 -- |--------------------< chk_cagr_id_flex_num >----------------------------|
149 -- ---------------------------------------------------------------------------
150 --
151 procedure chk_cagr_id_flex_num
152 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
153 ,p_effective_date in date
154 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
155 ,p_cagr_id_flex_num in per_all_assignments_f.cagr_id_flex_num%TYPE
156 ,p_collective_agreement_id in per_all_assignments_f.collective_agreement_id%TYPE
157 ) is
158 --
162 l_business_group_id number;
159 l_proc varchar2(72) := g_package||'chk_cagr_id_flex_num';
160 l_api_updating boolean;
161 l_exists varchar2(1);
163 l_collective_agreement_id number;
164 --
165 cursor csr_in_fnd_id_flex is
166 select null
167 from fnd_id_flex_structures fnd
168 where fnd.id_flex_code = 'CAGR';
169 --
170 cursor csr_in_cagr_grade_structs is
171 select null
172 from per_cagr_grade_structures cgs
173 where cgs.id_flex_num = p_cagr_id_flex_num
174 and cgs.collective_agreement_id = p_collective_agreement_id;
175 --
176 begin
177 hr_utility.set_location('Entering:'|| l_proc, 10);
178 --
179 if hr_multi_message.no_exclusive_error
180 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
181 ) then
182 --
183 -- Check mandatory parameters have been set
184 --
185 hr_api.mandatory_arg_error
186 (p_api_name => l_proc
187 ,p_argument => 'effective_date'
188 ,p_argument_value => p_effective_date
189 );
190 --
191 hr_utility.set_location(l_proc, 20);
192 --
193 -- Only proceed with validation if :
194 -- a) The id_flex_num is changing or new
195 -- b) The value for collective_agreement_id is changing and id_flex_num is present
196 --
197 l_api_updating := per_asg_shd.api_updating
198 (p_assignment_id => p_assignment_id
199 ,p_effective_date => p_effective_date
200 ,p_object_version_number => p_object_version_number);
201 hr_utility.set_location(l_proc, 30);
202 --
203 if ((l_api_updating and (p_cagr_id_flex_num is not null) and
204 nvl(per_asg_shd.g_old_rec.cagr_id_flex_num, hr_api.g_number)
205 <> nvl(p_cagr_id_flex_num, hr_api.g_number))
206 or
207 (NOT l_api_updating and p_cagr_id_flex_num is not null)
208 or
209 (l_api_updating and (p_cagr_id_flex_num is not null) and
210 nvl(per_asg_shd.g_old_rec.collective_agreement_id, hr_api.g_number)
211 <> nvl(p_collective_agreement_id, hr_api.g_number))) THEN
212 --
213 hr_utility.set_location(l_proc, 40);
214 --
215 --
216 If p_cagr_id_flex_num is not null and p_collective_agreement_id is null THEN
217 -- msg There must be a collective agreement specified if a grade structure is specified
218 hr_utility.set_location(l_proc, 50);
219 hr_utility.set_message(800, 'PER_52806_CAGR_STRUCT_GRADE');
220 hr_utility.raise_error;
221 Else
222 -- It must exist on fnd_id_flex_structures (It cannot be null here)
223 Open csr_in_fnd_id_flex;
224 Fetch csr_in_fnd_id_flex Into l_exists;
225 If csr_in_fnd_id_flex%notfound then
226 --
227 -- The id_flex_num must exist, so error
228 --
229 Close csr_in_fnd_id_flex;
230 hr_utility.set_location(l_proc, 60);
231 -- msg This grade structure does not exist
232 hr_utility.set_message(800, 'PER_52810_INVALID_STRUCTURE');
233 hr_multi_message.add;
234 Else
235 Close csr_in_fnd_id_flex;
236 --
237 -- If there is a collective_agreement_id it must be on per_cagr_grade_structures
238 If p_collective_agreement_id is not null THEN
239 Open csr_in_cagr_grade_structs;
240 fetch csr_in_cagr_grade_structs into l_exists;
241 If csr_in_cagr_grade_structs%notfound then
242 --
243 -- The id_flex_num must exist here, so error
244 --
245 Close csr_in_cagr_grade_structs;
246 hr_utility.set_location(l_proc, 70);
247 -- msg This grade structure / id flex num combination is invalid
248 hr_utility.set_message(800, 'PER_52808_INVALID_CAGR_GRADE');
249 hr_utility.raise_error;
250 Else
251 Close csr_in_cagr_grade_structs;
252 End if;
253 End if;
254 End if;
255 End if;
256 End if;
257 End if;
258 hr_utility.set_location(' Leaving:'|| l_proc, 90);
259 end chk_cagr_id_flex_num;
260 --
261 -- ---------------------------------------------------------------------------
262 -- |--------------------< chk_contract_id >----------------------------------|
263 -- ---------------------------------------------------------------------------
264 --
265 procedure chk_contract_id
266 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
267 ,p_effective_date in date
268 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
269 ,p_contract_id in per_all_assignments_f.contract_id%TYPE
270 ,p_person_id in per_all_assignments_f.person_id%TYPE
271 ,p_validation_start_date in date
272 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
273 ) is
274 --
275 l_proc varchar2(72) := g_package||'chk_contract_id';
276 l_api_updating boolean;
277 l_business_group_id number;
278 l_effective_start_date date;
279 l_person_id number;
280 --
281 cursor csr_in_per_contracts is
282 select pc.effective_start_date, pc.business_group_id, pc.person_id
283 from per_contracts_f pc
284 where pc.contract_id = p_contract_id
285 and pc.effective_start_date =
289 and pc1.contract_id = pc.contract_id);
286 (select min(pc1.effective_start_date)
287 from per_contracts_f pc1
288 where pc1.contract_id = p_contract_id
290 --
291 begin
292 hr_utility.set_location('Entering:'|| l_proc, 10);
293 --
294 --
295 -- Check mandatory parameters have been set
296 --
297 hr_api.mandatory_arg_error
298 (p_api_name => l_proc
299 ,p_argument => 'effective_date'
300 ,p_argument_value => p_effective_date
301 );
302 hr_api.mandatory_arg_error
303 (p_api_name => l_proc
304 ,p_argument => 'validation_start_date'
305 ,p_argument_value => p_validation_start_date
306 );
307 hr_api.mandatory_arg_error
308 (p_api_name => l_proc
309 ,p_argument => 'person_id'
310 ,p_argument_value => p_person_id
311 );
312 --
313 hr_utility.set_location(l_proc, 20);
314 --
315 -- Only proceed with validation if :
316 -- a) The contract_id is changing or new
317 -- b) The value for contract_id is changing and not null
318 --
319 l_api_updating := per_asg_shd.api_updating
320 (p_assignment_id => p_assignment_id
321 ,p_effective_date => p_effective_date
322 ,p_object_version_number => p_object_version_number);
323 hr_utility.set_location(l_proc, 30);
324 --
325 if ((l_api_updating and
326 nvl(per_asg_shd.g_old_rec.contract_id, hr_api.g_number)
327 <> nvl(p_contract_id, hr_api.g_number) AND (p_contract_id is not null))
328 or
329 (NOT l_api_updating and p_contract_id is not null)) THEN
330 hr_utility.set_location(l_proc, 40);
331 --
332 -- It must exist on per_contracts
333 --
334 Open csr_in_per_contracts;
335 Fetch csr_in_per_contracts Into l_effective_start_date, l_business_group_id, l_person_id;
336 If csr_in_per_contracts%notfound then
337 --
338 -- The contract_id must exist, so error
339 --
340 Close csr_in_per_contracts;
341 -- msg This contract does not exist
342 hr_utility.set_message(800, 'PER_52812_INVALID_CONTRACT');
343 hr_utility.raise_error;
344 Else
345 Close csr_in_per_contracts;
346 --
347 -- It has been found but is it for the same person ?
348 --
349 if hr_multi_message.no_exclusive_error
350 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PERSON_ID'
351 ) then
352 If l_person_id <> p_person_id THEN
353 -- msg This contract does not belong to this person
354 hr_utility.set_message(800, 'PER_52813_CONTRACT_PERSON');
355 hr_utility.raise_error;
356 --
357 elsif l_business_group_id <> p_business_group_id THEN
358 -- It has been found but is it in the same business group ?
359 -- msg This contract is not in the same business group as the assignment
360 hr_utility.set_message(800, 'PER_52814_CONTRACT_IN_BG');
361 hr_multi_message.add
362 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.CONTRACT_ID'
363 );
364 --
365 elsif l_effective_start_date > p_validation_start_date THEN
366 -- It has been found, but does it exist from the beginning of the asg row ?
367 -- msg This contract does not exist for the lifetime of this assignment row
368 hr_utility.set_message(800, 'PER_52815_CONTRACT_AFTER_ASG');
369 hr_utility.raise_error;
370 --
371 End If;
372 End If; -- no exclusive error
373 End if;
374 --
375 hr_utility.set_location(l_proc, 80);
376 --
377 End if;
378 --
379 hr_utility.set_location(' Leaving:'|| l_proc, 90);
380 --
381 end chk_contract_id;
382 --
383 -- ---------------------------------------------------------------------------
384 -- |--------------------< chk_collective_agreement_id >----------------------|
385 -- ---------------------------------------------------------------------------
386 --
387 procedure chk_collective_agreement_id
388 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
389 ,p_effective_date in date
390 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
391 ,p_collective_agreement_id in per_all_assignments_f.collective_agreement_id%TYPE
392 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
393 ,p_establishment_id in per_all_assignments_f.establishment_id%TYPE
394 ) is
395 --
396 l_proc varchar2(72) := g_package||'chk_collective_agreement_id';
397 l_api_updating boolean;
398 l_exists varchar2(1);
399 l_business_group_id number;
400 l_legislation_code varchar2(150);
401 --
402 cursor csr_in_per_coll_agrs is
403 select business_group_id
404 from per_collective_agreements pca
405 where pca.collective_agreement_id = p_collective_agreement_id;
406 --
407 cursor csr_in_establishment_ca_v is
408 select business_group_id
409 from hr_estab_coll_agrs_v eca
410 where eca.establishment_organization_id = p_establishment_id
411 and eca.collective_agreement_id = p_collective_agreement_id;
412 --
413 begin
414 hr_utility.set_location('Entering:'|| l_proc, 10);
415 --
416 --
417 -- Check mandatory parameters have been set
418 --
419 hr_api.mandatory_arg_error
420 (p_api_name => l_proc
421 ,p_argument => 'effective_date'
425 --
422 ,p_argument_value => p_effective_date
423 );
424 hr_utility.set_location(l_proc, 20);
426 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
427 --
428 -- Only proceed with validation if :
429 -- a) The current g_old_rec is current and / or
430 -- b) The value for collective_agreement_id has changed or is new
431 -- or
432 -- c) if French, if either establishment_id or collective_id has changed
433 --
434 l_api_updating := per_asg_shd.api_updating
435 (p_assignment_id => p_assignment_id
436 ,p_effective_date => p_effective_date
437 ,p_object_version_number => p_object_version_number);
438 hr_utility.set_location(l_proc, 30);
439 --
440 if ((l_api_updating and
441 nvl(per_asg_shd.g_old_rec.collective_agreement_id, hr_api.g_number)
442 <> nvl(p_collective_agreement_id, hr_api.g_number))
443 or
444 (NOT l_api_updating)
445 or
446 (l_api_updating and
447 nvl(per_asg_shd.g_old_rec.establishment_id, hr_api.g_number)
448 <> nvl(p_establishment_id, hr_api.g_number) AND (l_legislation_code = 'FR')))
449 THEN
450 hr_utility.set_location(l_proc, 40);
451 --
452 -- If NOT French, it is not mandatory but must be valid if it exists
453 --
454 if l_legislation_code <> 'FR' and p_collective_agreement_id is not null THEN
455 hr_utility.set_location(l_proc, 50);
456 Open csr_in_per_coll_agrs;
457 Fetch csr_in_per_coll_agrs Into l_business_group_id;
458 If csr_in_per_coll_agrs%notfound then
459 --
460 -- The collective_agreement_id must be there, so error
461 --
462 Close csr_in_per_coll_agrs;
463 -- msg This collective agreement does not exist
464 hr_utility.set_message(800, 'PER_52816_COLLECTIVE_AGREEMENT');
465 hr_multi_message.add
466 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
467 );
468 Else
469 Close csr_in_per_coll_agrs;
470 --
471 -- It must also be in the same business group
472 If l_business_group_id <> p_business_group_id THEN
473 -- msg This collective agreement is not in your business group
474 hr_utility.set_message(800, 'PER_52817_COLLECTIVE_NOT_IN_BG');
475 hr_multi_message.add
476 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
477 );
478 End If;
479 End if;
480 --
481 elsif l_legislation_code = 'FR' and p_establishment_id is null and
482 p_collective_agreement_id is not null THEN
483 hr_utility.set_location(l_proc, 60);
484 --
485 -- msg You must supply a establishment with a collective agreement if french.
486 hr_utility.set_message(800, 'PER_52827_NEED_ESTAB');
487 hr_multi_message.add
488 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
489 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID'
490 );
491 --
492 elsif l_legislation_code = 'FR' and p_collective_agreement_id is not null THEN
493 -- If French, the given collective_agreement_id must be valid
494 hr_utility.set_location(l_proc, 70);
495 --
496 if hr_multi_message.no_exclusive_error
497 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID'
498 ) then
499 --
500 Open csr_in_establishment_ca_v;
501 Fetch csr_in_establishment_ca_v Into l_business_group_id;
502 If csr_in_establishment_ca_v%notfound then
503 --
504 -- The collective_agreement_id must be there, so error
505 --
506 Close csr_in_establishment_ca_v;
507 -- msg French legislations must supply a collective agreement in your establishment
508 hr_utility.set_message(800, 'PER_52828_CAGR_NOT_IN_ESTAB');
509 hr_multi_message.add
510 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
511 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID'
512 );
513 Elsif l_business_group_id <> p_business_group_id THEN
514 Close csr_in_establishment_ca_v;
515 -- msg This collective agreement is not in your business group
516 hr_utility.set_message(800, 'PER_52829_CAGR_NOT_IN_BG');
517 hr_multi_message.add
518 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.COLLECTIVE_AGREEMENT_ID'
519 );
520 End If;
521 End If; -- no exclusive error
522 End if;
523 hr_utility.set_location(l_proc, 80);
524 --
525 End if;
526 --
527 hr_utility.set_location(' Leaving:'|| l_proc, 90);
528 --
529 end chk_collective_agreement_id;
530 -- ---------------------------------------------------------------------------
531 -- |--------------------< chk_establishment_id >-----------------------------|
532 -- ---------------------------------------------------------------------------
533 --
534 procedure chk_establishment_id
535 (p_assignment_id in per_all_assignments_f.assignment_id%TYPE
536 ,p_effective_date in date
537 ,p_object_version_number in per_all_assignments_f.object_version_number%TYPE
538 ,p_establishment_id in per_all_assignments_f.establishment_id%TYPE
539 ,p_assignment_type in per_all_assignments_f.assignment_type%TYPE
540 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
541 ) is
542 --
543 l_proc varchar2(72) := g_package||'chk_establishment_id';
544 l_api_updating boolean;
545 l_exists varchar2(1);
546 l_legislation_code varchar2(150);
547 --
548
552 where hou.organization_id = p_establishment_id
549 cursor csr_estab_in_org_units is
550 select null
551 from hr_all_organization_units hou
553 and hou.business_group_id = p_business_group_id
554 and p_effective_date between date_from and nvl(date_to, p_effective_date);
555 --
556 cursor csr_estab_in_fr_estab_v is
557 select null
558 from hr_fr_establishments_v frv
559 where frv.organization_id = p_establishment_id
560 and frv.business_group_id = p_business_group_id;
561 --
562 begin
563 hr_utility.set_location('Entering:'|| l_proc, 10);
564 --
565 -- Check mandatory parameters have been set
566 --
567 hr_api.mandatory_arg_error
568 (p_api_name => l_proc
569 ,p_argument => 'assignment_type'
570 ,p_argument_value => p_assignment_type
571 );
572 hr_api.mandatory_arg_error
573 (p_api_name => l_proc
574 ,p_argument => 'effective_date'
575 ,p_argument_value => p_effective_date
576 );
577 --
578
579 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
580
581 --
582 -- Only proceed with validation if :
583 -- a) The current g_old_rec is current and
584 -- b) The value for establishment_id has changed
585 -- or
586 -- c) if French, if the assignment has changed to Employee
587 -- d) if French, the employee's establishment is changing to null
588 --
589 l_api_updating := per_asg_shd.api_updating
590 (p_assignment_id => p_assignment_id
591 ,p_effective_date => p_effective_date
592 ,p_object_version_number => p_object_version_number);
593 hr_utility.set_location(l_proc, 30);
594 --
595 if ((l_api_updating and
596 nvl(per_asg_shd.g_old_rec.establishment_id, hr_api.g_number)
597 <> nvl(p_establishment_id, hr_api.g_number)
598 AND (p_establishment_id is not null))
599 or
600 ((l_api_updating) AND (l_legislation_code = 'FR') AND (p_assignment_type = 'E')
601 AND (p_establishment_id is null) AND ( nvl(per_asg_shd.g_old_rec.establishment_id,
602 hr_api.g_number) <> nvl(p_establishment_id, hr_api.g_number)) )
603 or
604 (NOT l_api_updating )
605 or
606 (l_api_updating and
607 nvl(per_asg_shd.g_old_rec.assignment_type, hr_api.g_varchar2)
608 <> nvl(p_assignment_type, hr_api.g_varchar2) AND (l_legislation_code = 'FR')
609 and (p_assignment_type = 'E')))
610 THEN
611 hr_utility.set_location(l_proc, 40);
612 --
613 -- If NOT French, it is not mandatory but must be valid if it exists
614 --
615 if l_legislation_code <> 'FR' and p_establishment_id is not null THEN
616 hr_utility.set_location(l_proc, 50);
617 Open csr_estab_in_org_units;
618 Fetch csr_estab_in_org_units Into l_exists;
619 If csr_estab_in_org_units%notfound then
620 --
621 -- The establishment_id must be there, so error
622 --
623 Close csr_estab_in_org_units;
624 hr_utility.set_message(800, 'PER_52818_INVALID_ESTAB');
625 hr_multi_message.add
626 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID'
627 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
628 );
629 Else
630 Close csr_estab_in_org_units;
631 End If;
632 --
633 -- Commented out due to relaxation of business rules
634 -- elsif l_legislation_code = 'FR' and p_establishment_id is null
635 -- and p_assignment_type = 'E' THEN
636 -- -- Error, French Employees must have an Establishment_id
637 -- hr_utility.set_location(l_proc, 60);
638 -- --
639 -- hr_utility.set_message(800, 'PER_52830_EE_MUST_HAVE_ESTAB');
640 -- hr_utility.raise_error;
641 -- --
642 elsif l_legislation_code = 'FR' and p_establishment_id is not null THEN
643 -- If French, the given establishment_id must be valid
644 hr_utility.set_location(l_proc, 70);
645 --
646 Open csr_estab_in_fr_estab_v;
647 Fetch csr_estab_in_fr_estab_v Into l_exists;
648 If csr_estab_in_fr_estab_v%notfound then
649 --
650 -- The establishment_id must be there, so error
651 --
652 Close csr_estab_in_fr_estab_v;
653 hr_utility.set_message(800, 'PER_52818_INVALID_ESTAB');
654 hr_multi_message.add
655 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.ESTABLISHMENT_ID'
656 );
657 Else
658 Close csr_estab_in_fr_estab_v;
659 End If;
660 --
661 end if;
662 hr_utility.set_location(l_proc, 80);
663 --
664 end if;
665 --
666 hr_utility.set_location(' Leaving:'|| l_proc, 90);
667 end chk_establishment_id ;
668
669 -- ---------------------------------------------------------------------------
670 -- |--------------------< chk_notice_period >-----------------------------|
671 -- ---------------------------------------------------------------------------
672 --
673
674 procedure chk_notice_period
675 (
676 p_assignment_id IN per_all_assignments_f.assignment_id%TYPE,
677 p_notice_period IN per_all_assignments_f.notice_period%TYPE
678
679 )
680
681 is
682 --
683 l_proc varchar2(72) := g_package||'chk_notice_period';
684 --
685 begin
686 hr_utility.set_location('Entering:'|| l_proc, 10);
687
688 --
689 -- Only proceed with validation if :
690 -- a) Inserting or
694 ((p_assignment_id IS NOT NULL) AND
691 -- b) The value for notice_period has changed
692 --
693 IF ( (p_assignment_id IS NULL) OR
695 (per_asg_shd.g_old_rec.notice_period <> p_notice_period))) THEN
696
697 hr_utility.set_location('Entering:'|| l_proc, 20);
698
699 --
700 -- Check that notice_period is not null and changed is valid
701 --
702
703 IF (p_notice_period IS NOT NULL and p_notice_period < 0) THEN
704
705 hr_utility.set_location(l_proc, 30);
706 hr_utility.set_message(800,'HR_289363_NOTICE_PERIOD_INV');
707 hr_utility.raise_error;
708 END IF;
709
710 --
711 END IF;
712 --
713 hr_utility.set_location(' Leaving:'|| l_proc, 40);
714 exception
715 when app_exception.application_exception then
716 if hr_multi_message.exception_add
717 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD'
718 ) then
719 hr_utility.set_location(' Leaving:'|| l_proc, 50);
720 raise;
721 end if;
722 hr_utility.set_location(' Leaving:'|| l_proc, 60);
723
724 end chk_notice_period;
725
726 -- ---------------------------------------------------------------------------
727 -- |--------------------< chk_notice_period_uom >---------------------------|
728 -- ---------------------------------------------------------------------------
729 --
730
731
732 procedure chk_notice_period_uom
733 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
734 ,p_notice_period IN per_all_assignments_f.notice_period%TYPE
735 ,p_notice_period_uom IN per_all_assignments_f.notice_period_uom%TYPE
736 ,p_effective_date IN DATE
737 ,p_validation_start_date IN DATE
738 ,P_VALIDATION_END_DATE IN DATE
739 ) IS
740
741 -- Local declarations
742 l_proc VARCHAR2(72) := g_package||'chk_notice_period_uom';
743 l_uom_lookup fnd_lookups.lookup_type%TYPE;
744
745 BEGIN
746
747 hr_utility.set_location('Entering: '||l_proc,10);
748 --
749 if hr_multi_message.no_exclusive_error
750 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD'
751 ) then
752 --
753 -- Only proceed with validation if :
754 -- a) Inserting or
755 -- b) The value for notice_period_uom has changed
756 --
757 IF ( (p_assignment_id IS NULL) OR
758 ((p_assignment_id IS NOT NULL) AND
759 (per_asg_shd.g_old_rec.notice_period_uom <> p_notice_period_uom))) THEN
760
761 hr_utility.set_location('Entering:'|| l_proc, 20);
762
763 IF (p_notice_period IS NOT NULL AND p_notice_period_uom IS NULL ) then
764
765 hr_utility.set_location(l_proc, 30);
766 hr_utility.set_message(800, 'HR_289365_NOTICE_UOM_INV');
767 hr_multi_message.add
768 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD'
769 ,p_associated_column2 => 'PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD_UOM'
770 );
771 END IF;
772
773 hr_utility.set_location(l_proc, 40);
774
775 IF P_NOTICE_PERIOD_UOM IS NOT NULL THEN
776
777 l_uom_lookup := 'QUALIFYING_UNITS';
778 -- Check that the uom exists in HR_LOOKUPS
779
780 IF hr_api.not_exists_in_dt_hr_lookups
781 (p_effective_date => p_effective_date
782 ,p_lookup_type => l_uom_lookup
783 ,p_lookup_code => p_notice_period_uom
784 ,p_validation_start_date => p_validation_start_date
785 ,p_validation_end_date => p_validation_end_date) THEN
786
787 hr_utility.set_location(l_proc, 50);
788 hr_utility.set_message(800, 'HR_289365_NOTICE_UOM_INV');
789 hr_multi_message.add
790 (p_associated_column1 =>
791 'PER_ALL_ASSIGNMENTS_F.NOTICE_PERIOD_UOM'
792 );
793 END IF;
794 END IF;
795
796 END IF;
797 END IF;
798
799 hr_utility.set_location('Leaving: '||l_proc,100);
800 END chk_notice_period_uom;
801
802
803 -- ---------------------------------------------------------------------------
804 -- |--------------------< chk_employee_category >---------------------------|
805 -- ---------------------------------------------------------------------------
806 --
807
808
809 procedure chk_employee_category
810 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
811 ,p_employee_category IN per_all_assignments_f.employee_category%TYPE
812 ,p_effective_date IN DATE
813 ,p_validation_start_date IN DATE
814 ,P_VALIDATION_END_DATE IN DATE
815 ) IS
816
817 -- Local declarations
818 l_proc VARCHAR2(72) := g_package||'chk_notice_period_uom';
819 l_catg_lookup fnd_lookups.lookup_type%TYPE;
820 BEGIN
821
822 hr_utility.set_location('Entering:'|| l_proc, 20);
823
824 IF p_employee_category is NOT NULL THEN
825 --
826 -- Only proceed with validation if :
827 -- a) Inserting or
828 -- b) The value for employee category has changed
829 --
830 IF ( (p_assignment_id IS NULL) OR
831 ((p_assignment_id IS NOT NULL) AND
835
832 (nvl(per_asg_shd.g_old_rec.employee_category,hr_api.g_varchar2) <> p_employee_category))) THEN
833
834 hr_utility.set_location(l_proc, 40);
836 l_catg_lookup := 'EMPLOYEE_CATG';
837
838 -- Check that the uom exists in HR_LOOKUPS
839
840 IF hr_api.not_exists_in_dt_hr_lookups
841 (p_effective_date => p_effective_date
842 ,p_lookup_type => l_catg_lookup
843 ,p_lookup_code => p_employee_category
844 ,p_validation_start_date => p_validation_start_date
845 ,p_validation_end_date => p_validation_end_date) THEN
846
847 hr_utility.set_location(l_proc, 30);
848 hr_utility.set_message(800, 'HR_289366_EMPLOYEE_CATG_INV');
849 hr_utility.raise_error;
850 END IF;
851
852 END IF;
853 END IF;
854 hr_utility.set_location('Leaving: '||l_proc,100);
855 --
856 exception
857 when app_exception.application_exception then
858 if hr_multi_message.exception_add
859 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.EMPLOYEE_CATEGORY'
860 ,p_associated_column2 =>
861 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE'
862 ,p_associated_column3 =>
863 'PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE'
864 ) then
865 hr_utility.set_location(' Leaving:'|| l_proc, 110);
866 raise;
867 end if;
868 hr_utility.set_location(' Leaving:'|| l_proc, 120);
869
870 END chk_employee_category;
871 --
872 -- ---------------------------------------------------------------------------
873 -- |---------------------------< chk_pop_date_start >------------------------|
874 -- ---------------------------------------------------------------------------
875 --
876 PROCEDURE chk_pop_date_start
877 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
878 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
879 ,p_person_id IN per_all_assignments_f.person_id%TYPE
880 ,p_assignment_type IN per_all_assignments_f.assignment_type%TYPE
881 ,p_pop_date_start IN per_periods_of_placement.date_start%TYPE
882 ,p_validation_start_date IN DATE
883 ,p_validation_end_date IN DATE
884 ,p_effective_date IN DATE
885 ,p_object_version_number IN per_all_assignments_f.object_version_number%TYPE
886 ) IS
887 --
888 l_api_updating BOOLEAN;
889 l_exists VARCHAR2(1);
890 l_proc VARCHAR2(72):= g_package||'chk_pop_date_start';
891 l_actual_termination_date per_periods_of_placement.actual_termination_date%TYPE;
892 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
893 --
894 CURSOR csr_valid_placement is
895 SELECT pop.business_group_id,
896 pop.actual_termination_date
897 FROM per_periods_of_placement pop
898 WHERE pop.person_id = p_person_id
899 AND pop.date_start = p_pop_date_start
900 AND p_validation_start_date BETWEEN pop.date_start AND
901 NVL(actual_termination_date, hr_api.g_eot);
902 --
903 BEGIN
904 --
905 hr_utility.set_location('Entering:'|| l_proc, 10);
906 --
907 -- Check mandatory parameters have been set
908 --
909 hr_api.mandatory_arg_error
910 (p_api_name => l_proc
911 ,p_argument => 'person_id'
912 ,p_argument_value => p_person_id
913 );
914 --
915 hr_api.mandatory_arg_error
916 (p_api_name => l_proc
917 ,p_argument => 'validation_start_date'
918 ,p_argument_value => p_validation_start_date
919 );
920 --
921 hr_api.mandatory_arg_error
922 (p_api_name => l_proc
923 ,p_argument => 'validation_end_date'
924 ,p_argument_value => p_validation_end_date
925 );
926 --
927 hr_api.mandatory_arg_error
928 (p_api_name => l_proc
929 ,p_argument => 'effective_date'
930 ,p_argument_value => p_effective_date
931 );
932 --
933 hr_utility.set_location(l_proc, 20);
934 --
935 -- Check if the assignment is being updated.
936 --
937 l_api_updating := per_asg_shd.api_updating
938 (p_assignment_id => p_assignment_id
939 ,p_effective_date => p_effective_date
940 ,p_object_version_number => p_object_version_number
941 );
942 --
943 hr_utility.set_location(l_proc, 30);
944 --
945 IF NOT l_api_updating THEN
946 --
947 hr_utility.set_location(l_proc, 40);
948 --
949 -- Check that the assignment is an employee assignment.
950 --
951 IF p_assignment_type <> 'C' THEN
952 --
953 -- Check that period of service is not set
954 --
955 IF p_pop_date_start IS NOT NULL THEN
956 --
957 hr_utility.set_message(801, 'HR_289649_DATE_START_NOT_N');
958 hr_utility.raise_error;
959 --
960 END IF;
961 --
962 hr_utility.set_location(l_proc, 50);
963 --
964 ELSE
965 --
966 -- Check the mandatory parameter period of service for
970 (p_api_name => l_proc
967 -- an employee.
968 --
969 hr_api.mandatory_arg_error
971 ,p_argument => 'period_of_placement_date_start'
972 ,p_argument_value => p_pop_date_start);
973 --
974 hr_utility.set_location(l_proc, 60);
975 --
976 -- Check if the period_of_placement_date_start exists between
977 -- the period of placement date start and actual termination date.
978 --
979 OPEN csr_valid_placement;
980 FETCH csr_valid_placement INTO l_business_group_id, l_actual_termination_date;
981 --
982 IF csr_valid_placement%NOTFOUND THEN
983 --
984 CLOSE csr_valid_placement;
985 --
986 hr_utility.set_message(801, 'HR_289650_CWK_INV_PERIOD_OF_PL');
987 hr_utility.raise_error;
988 --
989 END IF;
990 --
991 CLOSE csr_valid_placement;
992 --
993 hr_utility.set_location(l_proc, 70);
994 --
995 -- Check that the period of placement is in the same business group
996 -- as the business group of the assignment.
997 --
998 IF p_business_group_id <> l_business_group_id THEN
999 --
1000 hr_utility.set_message(801, 'HR_289651_CWK_INV_POS_BG');
1001 hr_utility.raise_error;
1002 --
1003 END IF;
1004 --
1005 hr_utility.set_location(l_proc, 80);
1006 --
1007 -- Check if the period of placement has been closed before the
1008 -- validation end date.
1009 --
1010 IF p_validation_end_date > NVL(l_actual_termination_date, hr_api.g_eot) THEN
1011 --
1012 hr_utility.set_message(801, 'HR_6434_EMP_ASS_PER_CLOSED');
1013 hr_utility.raise_error;
1014 --
1015 END IF;
1016 --
1017 hr_utility.set_location(l_proc, 90);
1018 --
1019 END IF;
1020 --
1021 END IF;
1022 --
1023 hr_utility.set_location(' Leaving:'|| l_proc, 999);
1024 --
1025 END chk_pop_date_start;
1026 --
1027 -- ---------------------------------------------------------------------------
1028 -- |-------------------------< chk_vendor_id >-------------------------------|
1029 -- ---------------------------------------------------------------------------
1030 --
1031 PROCEDURE chk_vendor_id
1032 (p_assignment_id IN NUMBER
1033 ,p_assignment_type IN VARCHAR2
1034 ,p_vendor_id IN NUMBER
1035 ,p_business_group_id IN NUMBER
1036 ,p_object_version_number IN NUMBER
1037 ,p_effective_date IN DATE) IS
1038 --
1039 l_proc VARCHAR2(72) := g_package||'chk_vendor_id';
1040 l_vendor_id NUMBER;
1041 l_api_updating BOOLEAN;
1042
1043 CURSOR csr_chk_vendor_id IS
1044 SELECT pov.vendor_id
1045 FROM po_vendors pov
1046 WHERE pov.vendor_id = p_vendor_id
1047 AND p_effective_date BETWEEN
1048 NVL(pov.start_date_active, p_effective_date) AND
1049 NVL(pov.end_date_active, p_effective_date)
1050 AND pov.enabled_flag = 'Y';
1051
1052 BEGIN
1053
1054 IF g_debug THEN
1055 hr_utility.set_location('Entering: ' || l_proc, 10);
1056 END IF;
1057
1058 --
1059 -- Check that mandatory parameters have been set.
1060 --
1061 hr_api.mandatory_arg_error
1062 (p_api_name => l_proc
1063 ,p_argument => 'assignment_type'
1064 ,p_argument_value => p_assignment_type
1065 );
1066
1067 IF g_debug THEN
1068 hr_utility.set_location(l_proc, 20);
1069 END IF;
1070
1071 --
1072 -- Only proceed with validation if :
1073 -- a) The current g_old_rec is current and
1074 -- b) The value being validated has changed.
1075 --
1076 l_api_updating := per_asg_shd.api_updating
1077 (p_assignment_id => p_assignment_id
1078 ,p_effective_date => p_effective_date
1079 ,p_object_version_number => p_object_version_number);
1080
1081 IF g_debug THEN
1082 hr_utility.set_location(l_proc, 30);
1083 END IF;
1084
1085 IF ((l_api_updating AND
1086 NVL(per_asg_shd.g_old_rec.vendor_id, hr_api.g_number) <>
1087 NVL(p_vendor_id, hr_api.g_number)) OR
1088 (NOT l_api_updating)) THEN
1089
1090 IF g_debug THEN
1091 hr_utility.set_location(l_proc, 40);
1092 END IF;
1093
1094 IF p_vendor_id IS NOT NULL THEN
1095 --
1096 -- If the assignment is not a CWK assignment then
1097 -- raise an error.
1098 --
1099 IF p_assignment_type <> 'C' THEN
1100
1101 hr_utility.set_message(800, 'HR_289652_VENDOR_ID_NOT_NULL');
1102 hr_utility.raise_error;
1103
1104 END IF;
1105
1106 IF g_debug THEN
1107 hr_utility.set_location(l_proc, 50);
1108 END IF;
1109
1110 --
1111 -- Check that the vendor is valid.
1112 --
1113 OPEN csr_chk_vendor_id;
1114 FETCH csr_chk_vendor_id INTO l_vendor_id;
1115
1116 IF csr_chk_vendor_id%NOTFOUND THEN
1117
1118 CLOSE csr_chk_vendor_id;
1119
1123 END IF;
1120 hr_utility.set_message(800, 'HR_289653_INVALID_VENDOR_ID');
1121 hr_utility.raise_error;
1122
1124
1125 CLOSE csr_chk_vendor_id;
1126
1127 IF g_debug THEN
1128 hr_utility.set_location(l_proc, 60);
1129 END IF;
1130
1131 END IF;
1132
1133 IF g_debug THEN
1134 hr_utility.set_location(l_proc, 996);
1135 END IF;
1136
1137 END IF;
1138
1139 IF g_debug THEN
1140 hr_utility.set_location('Leaving: ' || l_proc, 997);
1141 END IF;
1142
1143 EXCEPTION
1144
1145 WHEN app_exception.application_exception THEN
1146
1147 IF hr_multi_message.exception_add
1148 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_ID') THEN
1149
1150 IF g_debug THEN
1151 hr_utility.set_location('Leaving: ' || l_proc, 998);
1152 END IF;
1153
1154 RAISE;
1155
1156 END IF;
1157
1158 IF g_debug THEN
1159 hr_utility.set_location('Leaving: ' || l_proc, 999);
1160 END IF;
1161
1162 END chk_vendor_id;
1163 --
1164 -- ---------------------------------------------------------------------------
1165 -- |-------------------------< chk_vendor_site_id >---------------------------|
1166 -- ---------------------------------------------------------------------------
1167 --
1168 PROCEDURE chk_vendor_site_id
1169 (p_assignment_id IN NUMBER
1170 ,p_assignment_type IN VARCHAR2
1171 ,p_vendor_site_id IN NUMBER
1172 ,p_object_version_number IN NUMBER
1173 ,p_effective_date IN DATE) IS
1174
1175 l_proc VARCHAR2(72) := g_package||'chk_vendor_site_id';
1176 l_vendor_site_id NUMBER;
1177 l_api_updating BOOLEAN;
1178
1179 CURSOR csr_chk_vendor_site_id IS
1180 SELECT povs.vendor_site_id
1181 FROM po_vendor_sites_all povs
1182 WHERE povs.vendor_site_id = p_vendor_site_id;
1183
1184 BEGIN
1185
1186 IF g_debug THEN
1187 hr_utility.set_location('Entering: ' || l_proc, 10);
1188 END IF;
1189
1190 --
1191 -- Check that mandatory parameters have been set.
1192 --
1193 hr_api.mandatory_arg_error
1194 (p_api_name => l_proc
1195 ,p_argument => 'assignment_type'
1196 ,p_argument_value => p_assignment_type
1197 );
1198
1199 IF g_debug THEN
1200 hr_utility.set_location(l_proc, 20);
1201 END IF;
1202
1203 --
1204 -- Only proceed with validation if :
1205 -- a) The current g_old_rec is current and
1206 -- b) The value being validated has changed.
1207 --
1208 l_api_updating := per_asg_shd.api_updating
1209 (p_assignment_id => p_assignment_id
1210 ,p_effective_date => p_effective_date
1211 ,p_object_version_number => p_object_version_number);
1212
1213 IF g_debug THEN
1214 hr_utility.set_location(l_proc, 30);
1215 END IF;
1216
1217 IF ((l_api_updating AND
1218 NVL(per_asg_shd.g_old_rec.vendor_site_id, hr_api.g_number) <>
1219 NVL(p_vendor_site_id, hr_api.g_number)) OR
1220 (NOT l_api_updating)) THEN
1221
1222 IF g_debug THEN
1223 hr_utility.set_location(l_proc, 40);
1224 END IF;
1225
1226 IF p_vendor_site_id IS NOT NULL THEN
1227 --
1228 -- If the assignment is not a CWK assignment then
1229 -- raise an error.
1230 --
1231 IF p_assignment_type <> 'C' THEN
1232
1233 hr_utility.set_message(800, 'HR_289652_VENDOR_ID_NOT_NULL');
1234 hr_utility.raise_error;
1235
1236 END IF;
1237
1238 IF g_debug THEN
1239 hr_utility.set_location(l_proc, 50);
1240 END IF;
1241
1242 --
1243 -- Check that the vendor site is valid.
1244 --
1245 OPEN csr_chk_vendor_site_id;
1246 FETCH csr_chk_vendor_site_id INTO l_vendor_site_id;
1247
1248 IF csr_chk_vendor_site_id%NOTFOUND THEN
1249
1250 CLOSE csr_chk_vendor_site_id;
1251
1252 hr_utility.set_message(800, 'HR_449038_INVALID_VENDOR_SITE');
1253 hr_utility.raise_error;
1254
1255 END IF;
1256
1257 CLOSE csr_chk_vendor_site_id;
1258
1259 IF g_debug THEN
1260 hr_utility.set_location(l_proc, 60);
1261 END IF;
1262
1263 END IF;
1264
1265 IF g_debug THEN
1266 hr_utility.set_location(l_proc, 996);
1267 END IF;
1268
1269 END IF;
1270
1271 IF g_debug THEN
1272 hr_utility.set_location('Leaving: ' || l_proc, 997);
1273 END IF;
1274
1275 EXCEPTION
1276
1277 WHEN app_exception.application_exception THEN
1278
1279 IF hr_multi_message.exception_add
1280 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_SITE_ID') THEN
1281
1282 IF g_debug THEN
1283 hr_utility.set_location('Leaving: ' || l_proc, 998);
1284 END IF;
1285
1286 RAISE;
1287
1288 END IF;
1289
1290 IF g_debug THEN
1291 hr_utility.set_location('Leaving: ' || l_proc, 999);
1292 END IF;
1293
1294 END chk_vendor_site_id;
1295 --
1296 -- ---------------------------------------------------------------------------
1300 PROCEDURE chk_po_header_id
1297 -- |-------------------------< chk_po_header_id >-----------------------------|
1298 -- ---------------------------------------------------------------------------
1299 --
1301 (p_assignment_id IN NUMBER
1302 ,p_assignment_type IN VARCHAR2
1303 ,p_po_header_id IN NUMBER
1304 ,p_business_group_id IN NUMBER
1305 ,p_object_version_number IN NUMBER
1306 ,p_effective_date IN DATE) IS
1307
1308 l_proc VARCHAR2(72) := g_package||'chk_po_header_id';
1309 l_po_header_id NUMBER;
1310 l_api_updating BOOLEAN;
1311
1312 --
1313 -- Validate that the PO exists within this business group and that
1314 -- there is at least one line available within this PO that can be
1315 -- selected.
1316 --
1317 CURSOR csr_chk_po_header_id IS
1318 SELECT poh.po_header_id
1319 FROM po_temp_labor_headers_v poh
1320 WHERE poh.po_header_id = p_po_header_id
1321 AND poh.business_group_id = p_business_group_id
1322 AND EXISTS
1323 (SELECT NULL
1324 FROM po_temp_labor_lines_v pol
1325 WHERE pol.po_header_id = p_po_header_id
1326 AND NOT EXISTS
1327 (SELECT NULL
1328 FROM per_all_assignments_f paaf
1329 WHERE (p_assignment_id IS NULL
1330 OR (p_assignment_id IS NOT NULL AND
1331 p_assignment_id <> paaf.assignment_id))
1332 AND paaf.assignment_type = 'C'
1333 AND paaf.po_line_id IS NOT NULL
1334 AND paaf.po_line_id = pol.po_line_id));
1335
1336 BEGIN
1337
1338 IF g_debug THEN
1339 hr_utility.set_location('Entering: ' || l_proc, 10);
1340 END IF;
1341
1342 --
1343 -- Check that mandatory parameters have been set.
1344 --
1345 hr_api.mandatory_arg_error
1346 (p_api_name => l_proc
1347 ,p_argument => 'assignment_type'
1348 ,p_argument_value => p_assignment_type
1349 );
1350
1351 IF g_debug THEN
1352 hr_utility.set_location(l_proc, 20);
1353 END IF;
1354
1355 --
1356 -- Only proceed with validation if :
1357 -- a) The current g_old_rec is current and
1358 -- b) The value being validated has changed.
1359 --
1360 l_api_updating := per_asg_shd.api_updating
1361 (p_assignment_id => p_assignment_id
1362 ,p_effective_date => p_effective_date
1363 ,p_object_version_number => p_object_version_number);
1364
1365 IF g_debug THEN
1366 hr_utility.set_location(l_proc, 30);
1367 END IF;
1368
1369 IF ((l_api_updating AND
1370 NVL(per_asg_shd.g_old_rec.po_header_id, hr_api.g_number) <>
1371 NVL(p_po_header_id, hr_api.g_number)) OR
1372 (NOT l_api_updating)) THEN
1373
1374 IF g_debug THEN
1375 hr_utility.set_location(l_proc, 40);
1376 END IF;
1377
1378 IF p_po_header_id IS NOT NULL THEN
1379 --
1380 -- If the assignment is not a CWK assignment then
1381 -- raise an error.
1382 --
1383 IF p_assignment_type <> 'C' THEN
1384
1385 hr_utility.set_message(800, 'HR_449039_PO_DETAILS_NOT_NULL');
1386 hr_utility.raise_error;
1387
1388 END IF;
1389
1390 IF g_debug THEN
1391 hr_utility.set_location(l_proc, 50);
1392 END IF;
1393
1394 --
1395 -- Validate that PO services procurement is installed and
1396 -- that PO details can be set against the assignment.
1397 --
1398 IF NOT (hr_po_info.full_cwk_enabled) THEN
1399
1400 IF g_debug THEN
1401 hr_utility.set_location(l_proc, 60);
1402 END IF;
1403
1404 hr_utility.set_message(800, 'HR_449040_FULL_CWK_NOT_INSTALL');
1405 hr_utility.raise_error;
1406
1407 END IF;
1408 --
1409 -- Check that the purchase order is valid.
1410 --
1411 OPEN csr_chk_po_header_id;
1412 FETCH csr_chk_po_header_id INTO l_po_header_id;
1413
1414 IF csr_chk_po_header_id%NOTFOUND THEN
1415
1416 CLOSE csr_chk_po_header_id;
1417
1418 hr_utility.set_message(800, 'HR_449041_PO_HEADER_NOT_NULL');
1419 hr_utility.raise_error;
1420
1421 END IF;
1422
1423 CLOSE csr_chk_po_header_id;
1424
1425 IF g_debug THEN
1426 hr_utility.set_location(l_proc, 70);
1427 END IF;
1428
1429 END IF;
1430
1431 IF g_debug THEN
1432 hr_utility.set_location(l_proc, 996);
1433 END IF;
1434
1435 END IF;
1436
1437 IF g_debug THEN
1438 hr_utility.set_location('Leaving: ' || l_proc, 997);
1439 END IF;
1440
1441 EXCEPTION
1442
1443 WHEN app_exception.application_exception THEN
1444
1445 IF hr_multi_message.exception_add
1446 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PO_HEADER_ID') THEN
1447
1448 IF g_debug THEN
1449 hr_utility.set_location('Leaving: ' || l_proc, 998);
1450 END IF;
1451
1452 RAISE;
1453
1454 END IF;
1455
1456 IF g_debug THEN
1457 hr_utility.set_location('Leaving: '|| l_proc, 999);
1458 END IF;
1462 -- ---------------------------------------------------------------------------
1459
1460 END chk_po_header_id;
1461 --
1463 -- |-------------------------< chk_po_line_id >-------------------------------|
1464 -- ---------------------------------------------------------------------------
1465 --
1466 PROCEDURE chk_po_line_id
1467 (p_assignment_id IN NUMBER
1468 ,p_assignment_type IN VARCHAR2
1469 ,p_po_line_id IN NUMBER
1470 ,p_object_version_number IN NUMBER
1471 ,p_effective_date IN DATE) IS
1472
1473 l_proc VARCHAR2(72) := g_package||'chk_po_line_id';
1474 l_po_line_id NUMBER;
1475 l_api_updating BOOLEAN;
1476
1477 --
1478 -- Validate that the PO line is valid and that is it unassigned.
1479 -- Additional validation, for example, verifying that the line
1480 -- matches the job, is performed in cross validation chk routines.
1481 --
1482 CURSOR csr_chk_po_line_id IS
1483 SELECT pol.po_line_id
1484 FROM po_temp_labor_lines_v pol
1485 WHERE pol.po_line_id = p_po_line_id
1486 AND NOT EXISTS
1487 (SELECT NULL
1488 FROM per_all_assignments_f paaf
1489 WHERE (p_assignment_id IS NULL
1490 OR (p_assignment_id IS NOT NULL AND
1491 p_assignment_id <> paaf.assignment_id))
1492 AND paaf.assignment_type = 'C'
1493 AND paaf.po_line_id IS NOT NULL
1494 AND paaf.po_line_id = p_po_line_id);
1495
1496 BEGIN
1497
1498 IF g_debug THEN
1499 hr_utility.set_location('Entering: ' || l_proc, 10);
1500 END IF;
1501
1502 --
1503 -- Check that mandatory parameters have been set.
1504 --
1505 hr_api.mandatory_arg_error
1506 (p_api_name => l_proc
1507 ,p_argument => 'assignment_type'
1508 ,p_argument_value => p_assignment_type
1509 );
1510
1511 IF g_debug THEN
1512 hr_utility.set_location(l_proc, 20);
1513 END IF;
1514
1515 --
1516 -- Only proceed with validation if :
1517 -- a) The current g_old_rec is current and
1518 -- b) The value being validated has changed.
1519 --
1520 l_api_updating := per_asg_shd.api_updating
1521 (p_assignment_id => p_assignment_id
1522 ,p_effective_date => p_effective_date
1523 ,p_object_version_number => p_object_version_number);
1524
1525 IF g_debug THEN
1526 hr_utility.set_location(l_proc, 30);
1527 END IF;
1528
1529 IF ((l_api_updating AND
1530 NVL(per_asg_shd.g_old_rec.po_line_id, hr_api.g_number) <>
1531 NVL(p_po_line_id, hr_api.g_number)) OR
1532 (NOT l_api_updating)) THEN
1533
1534 IF g_debug THEN
1535 hr_utility.set_location(l_proc, 40);
1536 END IF;
1537
1538 IF p_po_line_id IS NOT NULL THEN
1539 --
1540 -- If the assignment is not a CWK assignment then
1541 -- raise an error.
1542 --
1543 IF p_assignment_type <> 'C' THEN
1544
1545 hr_utility.set_message(800, 'HR_449039_PO_DETAILS_NOT_NULL');
1546 hr_utility.raise_error;
1547
1548 END IF;
1549
1550 IF g_debug THEN
1551 hr_utility.set_location(l_proc, 50);
1552 END IF;
1553
1554 --
1555 -- Validate that PO services procurement is installed and
1556 -- that PO details can be set against the assignment.
1557 --
1558 IF NOT (hr_po_info.full_cwk_enabled) THEN
1559
1560 IF g_debug THEN
1561 hr_utility.set_location(l_proc, 60);
1562 END IF;
1563
1564 hr_utility.set_message(800, 'HR_449040_FULL_CWK_NOT_INSTALL');
1565 hr_utility.raise_error;
1566
1567 END IF;
1568 --
1569 -- Check that the purchase order line is valid.
1570 --
1571 OPEN csr_chk_po_line_id;
1572 FETCH csr_chk_po_line_id INTO l_po_line_id;
1573
1574 IF csr_chk_po_line_id%NOTFOUND THEN
1575
1576 CLOSE csr_chk_po_line_id;
1577
1578 hr_utility.set_message(800, 'HR_449042_PO_LINE_NOT_NULL');
1579 hr_utility.raise_error;
1580
1581 END IF;
1582
1583 CLOSE csr_chk_po_line_id;
1584
1585 IF g_debug THEN
1586 hr_utility.set_location(l_proc, 70);
1587 END IF;
1588
1589 END IF;
1590
1591 IF g_debug THEN
1592 hr_utility.set_location(l_proc, 996);
1593 END IF;
1594
1595 END IF;
1596
1597 IF g_debug THEN
1598 hr_utility.set_location('Leaving: ' || l_proc, 997);
1599 END IF;
1600
1601 EXCEPTION
1602
1603 WHEN app_exception.application_exception THEN
1604
1605 IF hr_multi_message.exception_add
1606 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PO_LINE_ID') THEN
1607
1608 IF g_debug THEN
1609 hr_utility.set_location('Leaving: '|| l_proc, 998);
1610 END IF;
1611
1612 RAISE;
1613
1614 END IF;
1615
1616 IF g_debug THEN
1617 hr_utility.set_location('Leaving: ' || l_proc, 999);
1618 END IF;
1619
1620 END chk_po_line_id;
1621 --
1622 -- ---------------------------------------------------------------------------
1626 PROCEDURE chk_projected_assignment_end
1623 -- |-------------------------< chk_projected_assignment_end >-----------------|
1624 -- ---------------------------------------------------------------------------
1625 --
1627 (p_assignment_id IN NUMBER
1628 ,p_assignment_type IN VARCHAR2
1629 ,p_effective_start_date IN DATE
1630 ,p_projected_assignment_end IN DATE
1631 ,p_object_version_number IN NUMBER
1632 ,p_effective_date IN DATE) IS
1633
1634 l_proc VARCHAR2(72) := g_package||
1635 'chk_projected_assignment_end';
1636 l_api_updating BOOLEAN;
1637
1638 BEGIN
1639
1640 IF g_debug THEN
1641 hr_utility.set_location('Entering: ' || l_proc, 10);
1642 END IF;
1643
1644 --
1645 -- Check that mandatory parameters have been set.
1646 --
1647 hr_api.mandatory_arg_error
1648 (p_api_name => l_proc
1649 ,p_argument => 'assignment_type'
1650 ,p_argument_value => p_assignment_type
1651 );
1652
1653 IF g_debug THEN
1654 hr_utility.set_location(l_proc, 20);
1655 END IF;
1656
1657 --
1658 -- Only proceed with validation if :
1659 -- a) The current g_old_rec is current and
1660 -- b) The value being validated has changed.
1661 --
1662 l_api_updating := per_asg_shd.api_updating
1663 (p_assignment_id => p_assignment_id
1664 ,p_effective_date => p_effective_date
1665 ,p_object_version_number => p_object_version_number);
1666
1667 IF g_debug THEN
1668 hr_utility.set_location(l_proc, 30);
1669 END IF;
1670
1671 IF ((l_api_updating AND
1672 NVL(per_asg_shd.g_old_rec.projected_assignment_end, hr_api.g_date) <>
1673 NVL(p_projected_assignment_end, hr_api.g_date)) OR
1674 (NOT l_api_updating)) THEN
1675
1676 IF g_debug THEN
1677 hr_utility.set_location(l_proc, 40);
1678 END IF;
1679
1680 IF p_projected_assignment_end IS NOT NULL THEN
1681 --
1682 -- If the assignment is not a CWK assignment or the projected end is
1683 -- earlier than the start date raise an error.
1684 --
1685 -- R12, for global deployments, allow EMP asgs to have projected end date
1686 --
1687 IF p_assignment_type not in ('C','E')
1688 OR p_projected_assignment_end <
1689 NVL(p_effective_start_date, p_effective_date) THEN
1690
1691 hr_utility.set_message(800, 'HR_449043_PROJ_ASG_END');
1692 hr_utility.raise_error;
1693
1694 END IF;
1695
1696 IF g_debug THEN
1697 hr_utility.set_location(l_proc, 50);
1698 END IF;
1699
1700 END IF;
1701
1702 IF g_debug THEN
1703 hr_utility.set_location(l_proc, 996);
1704 END IF;
1705
1706 END IF;
1707
1708 IF g_debug THEN
1709 hr_utility.set_location('Leaving: ' || l_proc, 997);
1710 END IF;
1711
1712 EXCEPTION
1713
1714 WHEN app_exception.application_exception THEN
1715
1716 IF hr_multi_message.exception_add
1717 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.PROJECTED_ASSIGNMENT_END') THEN
1718
1719 IF g_debug THEN
1720 hr_utility.set_location('Leaving: ' || l_proc, 998);
1721 END IF;
1722
1723 RAISE;
1724
1725 END IF;
1726
1727 IF g_debug THEN
1728 hr_utility.set_location('Leaving: ' || l_proc, 999);
1729 END IF;
1730
1731 END chk_projected_assignment_end;
1732 --
1733 -- ---------------------------------------------------------------------------
1734 -- |-------------------------< chk_vendor_id_site_id >------------------------|
1735 -- ---------------------------------------------------------------------------
1736 --
1737 PROCEDURE chk_vendor_id_site_id
1738 (p_assignment_id IN NUMBER
1739 ,p_vendor_id IN NUMBER
1740 ,p_vendor_site_id IN NUMBER
1741 ,p_object_version_number IN NUMBER
1742 ,p_effective_date IN DATE) IS
1743
1744 l_proc VARCHAR2(72) := g_package||'chk_vendor_id_site_id';
1745 l_vendor_id NUMBER;
1746 l_api_updating BOOLEAN;
1747
1748 --
1749 -- Validate that the supplier site exists for the given
1750 -- supplier.
1751 --
1752 CURSOR csr_chk_vendor_for_site IS
1753 SELECT povs.vendor_id
1754 FROM po_vendor_sites_all povs
1755 WHERE povs.vendor_site_id = p_vendor_site_id;
1756
1757 BEGIN
1758
1759 IF g_debug THEN
1760 hr_utility.set_location('Entering: ' || l_proc, 10);
1761 END IF;
1762
1763 IF hr_multi_message.no_exclusive_error
1764 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_ID'
1765 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_SITE_ID'
1766 )
1767 THEN
1768
1769 --
1770 -- Only proceed with validation if :
1771 -- a) The current g_old_rec is current and
1772 -- b) The value being validated has changed.
1773 --
1774 l_api_updating := per_asg_shd.api_updating
1775 (p_assignment_id => p_assignment_id
1776 ,p_effective_date => p_effective_date
1780 hr_utility.set_location(l_proc, 20);
1777 ,p_object_version_number => p_object_version_number);
1778
1779 IF g_debug THEN
1781 END IF;
1782
1783 IF (l_api_updating
1784 AND
1785 ((NVL(per_asg_shd.g_old_rec.vendor_id, hr_api.g_number)
1786 <> NVL(p_vendor_id, hr_api.g_number))
1787 OR
1788 (NVL(per_asg_shd.g_old_rec.vendor_site_id, hr_api.g_number)
1789 <> NVL(p_vendor_site_id, hr_api.g_number))))
1790 OR
1791 NOT l_api_updating THEN
1792
1793 IF g_debug THEN
1794 hr_utility.set_location(l_proc, 30);
1795 END IF;
1796
1797 IF p_vendor_site_id IS NOT NULL AND p_vendor_id IS NULL THEN
1798 --
1799 -- Error. The vendor_id must always be set when the vendor_site_id
1800 -- is set.
1801 --
1802 IF g_debug THEN
1803 hr_utility.set_location(l_proc, 40);
1804 END IF;
1805
1806 hr_utility.set_message(800, 'HR_449044_ENTER_VENDOR_ID');
1807 hr_utility.raise_error;
1808
1809 END IF;
1810
1811 IF g_debug THEN
1812 hr_utility.set_location(l_proc, 50);
1813 END IF;
1814
1815 IF p_vendor_site_id IS NOT NULL AND p_vendor_id IS NOT NULL THEN
1816 --
1817 -- Validate the site exists for the given supplier.
1818 --
1819 OPEN csr_chk_vendor_for_site;
1820 FETCH csr_chk_vendor_for_site INTO l_vendor_id;
1821 CLOSE csr_chk_vendor_for_site;
1822
1823 IF l_vendor_id IS NULL OR
1824 l_vendor_id <> p_vendor_id THEN
1825
1826 IF g_debug THEN
1827 hr_utility.set_location(l_proc, 60);
1828 END IF;
1829
1830 hr_utility.set_message(800, 'HR_449045_NO_SITE_FOR_VENDOR');
1831 hr_utility.raise_error;
1832
1833 END IF;
1834
1835 IF g_debug THEN
1836 hr_utility.set_location(l_proc, 996);
1837 END IF;
1838
1839 END IF;
1840
1841 END IF;
1842
1843 END IF;
1844
1845 IF g_debug THEN
1846 hr_utility.set_location('Leaving: ' || l_proc, 997);
1847 END IF;
1848
1849 END chk_vendor_id_site_id;
1850 --
1851 -- ---------------------------------------------------------------------------
1852 -- |-------------------------< chk_po_header_id_line_id >---------------------|
1853 -- ---------------------------------------------------------------------------
1854 --
1855 PROCEDURE chk_po_header_id_line_id
1856 (p_assignment_id IN NUMBER
1857 ,p_po_header_id IN NUMBER
1858 ,p_po_line_id IN NUMBER
1859 ,p_object_version_number IN NUMBER
1860 ,p_effective_date IN DATE) IS
1861
1862 l_proc VARCHAR2(72) := g_package||'chk_po_header_id_line_id';
1863 l_po_header_id NUMBER;
1864 l_api_updating BOOLEAN;
1865
1866 --
1867 -- Validate that the PO line exists for the given
1868 -- PO.
1869 --
1870 CURSOR csr_chk_po_for_line IS
1871 SELECT pol.po_header_id
1872 FROM po_temp_labor_lines_v pol
1873 WHERE pol.po_header_id = p_po_header_id;
1874
1875 BEGIN
1876
1877 IF g_debug THEN
1878 hr_utility.set_location('Entering: ' || l_proc, 10);
1879 END IF;
1880
1881 IF hr_multi_message.no_exclusive_error
1882 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.PO_HEADER_ID'
1883 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PO_LINE_ID'
1884 )
1885 THEN
1886
1887 --
1888 -- Only proceed with validation if :
1889 -- a) The current g_old_rec is current and
1890 -- b) The value being validated has changed.
1891 --
1892 l_api_updating := per_asg_shd.api_updating
1893 (p_assignment_id => p_assignment_id
1894 ,p_effective_date => p_effective_date
1895 ,p_object_version_number => p_object_version_number);
1896
1897 IF g_debug THEN
1898 hr_utility.set_location(l_proc, 20);
1899 END IF;
1900
1901 IF (l_api_updating
1902 AND
1903 ((NVL(per_asg_shd.g_old_rec.po_header_id, hr_api.g_number)
1904 <> NVL(p_po_header_id, hr_api.g_number))
1905 OR
1906 (NVL(per_asg_shd.g_old_rec.po_line_id, hr_api.g_number)
1907 <> NVL(p_po_line_id, hr_api.g_number))))
1908 OR
1909 NOT l_api_updating THEN
1910
1911 IF g_debug THEN
1912 hr_utility.set_location(l_proc, 30);
1913 END IF;
1914
1915 IF p_po_line_id IS NOT NULL AND p_po_header_id IS NULL THEN
1916 --
1917 -- Error. The po_line_id must always be set when the po_header_id
1918 -- is set.
1919 --
1920 IF g_debug THEN
1921 hr_utility.set_location(l_proc, 40);
1922 END IF;
1923
1924 hr_utility.set_message(800, 'HR_449046_ENTER_PO_HEADER_ID');
1925 hr_utility.raise_error;
1926
1927 END IF;
1928
1929 IF g_debug THEN
1930 hr_utility.set_location(l_proc, 50);
1931 END IF;
1935 -- Validate the line exists for the given PO.
1932
1933 IF p_po_header_id IS NOT NULL AND p_po_line_id IS NOT NULL THEN
1934 --
1936 --
1937 OPEN csr_chk_po_for_line;
1938 FETCH csr_chk_po_for_line INTO l_po_header_id;
1939 CLOSE csr_chk_po_for_line;
1940
1941 IF l_po_header_id IS NULL OR
1942 l_po_header_id <> p_po_header_id THEN
1943
1944 IF g_debug THEN
1945 hr_utility.set_location(l_proc, 60);
1946 END IF;
1947
1948 hr_utility.set_message(800, 'HR_449047_NO_LINE_FOR_PO');
1949 hr_utility.raise_error;
1950
1951 END IF;
1952
1953 IF g_debug THEN
1954 hr_utility.set_location(l_proc, 996);
1955 END IF;
1956
1957 END IF;
1958
1959 END IF;
1960
1961 END IF;
1962
1963 IF g_debug THEN
1964 hr_utility.set_location('Leaving: ' || l_proc, 997);
1965 END IF;
1966
1967 END chk_po_header_id_line_id;
1968 --
1969 -- ---------------------------------------------------------------------------
1970 -- |-------------------------< chk_vendor_po_match >--------------------------|
1971 -- ---------------------------------------------------------------------------
1972 --
1973 PROCEDURE chk_vendor_po_match
1974 (p_assignment_id IN NUMBER
1975 ,p_vendor_id IN NUMBER
1976 ,p_vendor_site_id IN NUMBER
1977 ,p_po_header_id IN NUMBER
1978 ,p_object_version_number IN NUMBER
1979 ,p_effective_date IN DATE) IS
1980
1981 l_proc VARCHAR2(72) := g_package||'chk_vendor_po_match';
1982 l_vendor_id NUMBER;
1983 l_vendor_site_id NUMBER;
1984 l_api_updating BOOLEAN;
1985
1986 --
1987 -- Fetch the vendor and site for this PO.
1988 --
1989 CURSOR csr_chk_vendor_po_match IS
1990 SELECT NVL(poh.vendor_id, p_vendor_id) vendor_id
1991 ,NVL(poh.vendor_site_id, p_vendor_site_id) vendor_site_id
1992 FROM po_temp_labor_headers_v poh
1993 WHERE poh.po_header_id = p_po_header_id;
1994
1995 BEGIN
1996
1997 IF g_debug THEN
1998 hr_utility.set_location('Entering: ' || l_proc, 10);
1999 END IF;
2000
2001 IF hr_multi_message.no_exclusive_error
2002 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_ID'
2003 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_SITE_ID'
2004 ,p_check_column3 => 'PER_ALL_ASSIGNMENTS_F.PO_HEADER_ID'
2005 )
2006 THEN
2007
2008 --
2009 -- Only proceed with validation if :
2010 -- a) The current g_old_rec is current and
2011 -- b) The value being validated has changed.
2012 --
2013 l_api_updating := per_asg_shd.api_updating
2014 (p_assignment_id => p_assignment_id
2015 ,p_effective_date => p_effective_date
2016 ,p_object_version_number => p_object_version_number);
2017
2018 IF g_debug THEN
2019 hr_utility.set_location(l_proc, 20);
2020 END IF;
2021
2022 IF (l_api_updating
2023 AND
2024 ((NVL(per_asg_shd.g_old_rec.vendor_id, hr_api.g_number)
2025 <> NVL(p_vendor_id, hr_api.g_number))
2026 OR
2027 (NVL(per_asg_shd.g_old_rec.vendor_site_id, hr_api.g_number)
2028 <> NVL(p_vendor_site_id, hr_api.g_number))
2029 OR
2030 (NVL(per_asg_shd.g_old_rec.po_header_id, hr_api.g_number)
2031 <> NVL(p_po_header_id, hr_api.g_number))))
2032 OR
2033 NOT l_api_updating THEN
2034
2035 IF g_debug THEN
2036 hr_utility.set_location(l_proc, 30);
2037 END IF;
2038
2039 IF p_po_header_id IS NOT NULL
2040 AND (p_vendor_id IS NOT NULL OR p_vendor_site_id IS NOT NULL) THEN
2041
2042 IF g_debug THEN
2043 hr_utility.set_location(l_proc, 40);
2044 END IF;
2045
2046 --
2047 -- Verify that the Supplier on the PO matches the Supplier passed
2048 -- into the row handler.
2049 --
2050 OPEN csr_chk_vendor_po_match;
2051 FETCH csr_chk_vendor_po_match INTO l_vendor_id
2052 ,l_vendor_site_id;
2053 CLOSE csr_chk_vendor_po_match;
2054
2055 IF (p_vendor_id IS NOT NULL AND p_vendor_id <> l_vendor_id)
2056 OR (p_vendor_site_id IS NOT NULL AND
2057 p_vendor_site_id <> l_vendor_site_id) THEN
2058
2059 IF g_debug THEN
2060 hr_utility.set_location(l_proc, 50);
2061 END IF;
2062
2063 hr_utility.set_message(800, 'HR_449048_VENDOR_NOT_MATCH_PO');
2064 hr_utility.raise_error;
2065
2066 END IF;
2067
2068 END IF;
2069
2070 END IF;
2071
2072 END IF;
2073
2074 IF g_debug THEN
2075 hr_utility.set_location('Leaving: '|| l_proc, 997);
2076 END IF;
2077
2078 END chk_vendor_po_match;
2079 --
2080 -- ---------------------------------------------------------------------------
2081 -- |-------------------------< chk_po_job_match >-----------------------------|
2082 -- ---------------------------------------------------------------------------
2083 --
2084 PROCEDURE chk_po_job_match
2085 (p_assignment_id IN NUMBER
2086 ,p_job_id IN NUMBER
2087 ,p_po_line_id IN NUMBER
2091 l_proc VARCHAR2(72) := g_package||'chk_po_job_match';
2088 ,p_object_version_number IN NUMBER
2089 ,p_effective_date IN DATE) IS
2090
2092 l_job_id NUMBER;
2093 l_api_updating BOOLEAN;
2094
2095 --
2096 -- Fetch the job for this PO line.
2097 --
2098 CURSOR csr_chk_po_job_match IS
2099 SELECT NVL(pol.job_id, hr_api.g_number) job_id
2100 FROM po_temp_labor_lines_v pol
2101 WHERE pol.po_line_id = p_po_line_id;
2102
2103 BEGIN
2104
2105 IF g_debug THEN
2106 hr_utility.set_location('Entering: ' || l_proc, 10);
2107 END IF;
2108
2109 IF hr_multi_message.no_exclusive_error
2110 (p_check_column1 => 'PER_ALL_ASSIGNMENTS_F.JOB_ID'
2111 ,p_check_column2 => 'PER_ALL_ASSIGNMENTS_F.PO_LINE_ID'
2112 )
2113 THEN
2114
2115 --
2116 -- Only proceed with validation if :
2117 -- a) The current g_old_rec is current and
2118 -- b) The value being validated has changed.
2119 --
2120 l_api_updating := per_asg_shd.api_updating
2121 (p_assignment_id => p_assignment_id
2122 ,p_effective_date => p_effective_date
2123 ,p_object_version_number => p_object_version_number);
2124
2125 IF g_debug THEN
2126 hr_utility.set_location(l_proc, 20);
2127 END IF;
2128
2129 IF (l_api_updating
2130 AND
2131 ((NVL(per_asg_shd.g_old_rec.job_id, hr_api.g_number)
2132 <> NVL(p_job_id, hr_api.g_number))
2133 OR
2134 (NVL(per_asg_shd.g_old_rec.po_line_id, hr_api.g_number)
2135 <> NVL(p_po_line_id, hr_api.g_number))))
2136 OR
2137 NOT l_api_updating THEN
2138
2139 IF g_debug THEN
2140 hr_utility.set_location(l_proc, 30);
2141 END IF;
2142
2143 IF p_po_line_id IS NOT NULL AND p_job_id IS NOT NULL THEN
2144
2145 IF g_debug THEN
2146 hr_utility.set_location(l_proc, 40);
2147 END IF;
2148
2149 --
2150 -- Verify that the Job on the PO matches the Job on the assignment.
2151 --
2152 OPEN csr_chk_po_job_match;
2153 FETCH csr_chk_po_job_match INTO l_job_id;
2154 CLOSE csr_chk_po_job_match;
2155
2156 IF p_job_id <> l_job_id THEN
2157
2158 IF g_debug THEN
2159 hr_utility.set_location(l_proc, 50);
2160 END IF;
2161
2162 hr_utility.set_message(800, 'HR_449049_JOB_NOT_MATCH_PO');
2163 hr_utility.raise_error;
2164
2165 END IF;
2166
2167 END IF;
2168
2169 END IF;
2170
2171 END IF;
2172
2173 IF g_debug THEN
2174 hr_utility.set_location('Leaving: '|| l_proc, 997);
2175 END IF;
2176
2177 END chk_po_job_match;
2178 --
2179 -- ---------------------------------------------------------------------------
2180 -- |----------------------< chk_vendor_assignment_number >-------------------|
2181 -- ---------------------------------------------------------------------------
2182 --
2183 PROCEDURE chk_vendor_assignment_number
2184 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2185 ,p_assignment_type IN per_all_assignments_f.assignment_type%TYPE
2186 ,p_vendor_assignment_number IN per_all_assignments_f.vendor_assignment_number%TYPE
2187 ,p_business_group_id IN per_assignments_f.business_group_id%TYPE
2188 ,p_object_version_number IN per_all_assignments_f.object_version_number%TYPE
2189 ,p_effective_date IN DATE) IS
2190 --
2191 l_proc VARCHAR2(72):= g_package||'chk_vendor_assignment_number';
2192 l_api_updating BOOLEAN;
2193 --
2194 BEGIN
2195 --
2196 hr_utility.set_location('Entering:'|| l_proc, 10);
2197 --
2198 -- Check mandatory parameters have been set
2199 --
2200 hr_api.mandatory_arg_error
2201 (p_api_name => l_proc
2202 ,p_argument => 'assignment_type'
2203 ,p_argument_value => p_assignment_type
2204 );
2205 --
2206 hr_utility.set_location(l_proc, 20);
2207 --
2208 -- Only proceed with validation if :
2209 -- a) The current g_old_rec is current and
2210 -- b) The value for vacancy has changed
2211 --
2212 l_api_updating := per_asg_shd.api_updating
2213 (p_assignment_id => p_assignment_id
2214 ,p_effective_date => p_effective_date
2215 ,p_object_version_number => p_object_version_number);
2216 --
2217 hr_utility.set_location(l_proc, 30);
2218 --
2219 IF ((l_api_updating AND
2220 NVL(per_asg_shd.g_old_rec.vendor_assignment_number, hr_api.g_varchar2) <>
2221 NVL(p_vendor_assignment_number, hr_api.g_varchar2)) OR
2222 (NOT l_api_updating)) THEN
2223 --
2224 hr_utility.set_location(l_proc, 40);
2225 --
2226 -- If the vendor assignment number has been populated for an
2227 -- assignment that is not a CWK assignment then
2228 -- raise an error.
2229 --
2230 IF p_vendor_assignment_number IS NOT NULL AND
2231 p_assignment_type <> 'C' THEN
2232 --
2233 hr_utility.set_message(801, 'HR_289654_VEN_ASG_NO_NOT_NULL');
2234 hr_utility.raise_error;
2235 --
2236 END IF;
2237 --
2238 END IF;
2239 --
2243 --
2240 hr_utility.set_location(' Leaving:'|| l_proc, 997);
2241 --
2242 EXCEPTION
2244 WHEN app_exception.application_exception THEN
2245 --
2246 IF hr_multi_message.exception_add
2247 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_ASSIGNMENT_NUMBER') THEN
2248 --
2249 hr_utility.set_location(' Leaving:'|| l_proc, 998);
2250 --
2251 RAISE;
2252 --
2253 END IF;
2254 --
2255 hr_utility.set_location(' Leaving:'|| l_proc, 999);
2256 --
2257 END chk_vendor_assignment_number;
2258 --
2259 -- ---------------------------------------------------------------------------
2260 -- |-------------------------< chk_vendor_employee_number >-------------------|
2261 -- ---------------------------------------------------------------------------
2262 --
2263 PROCEDURE chk_vendor_employee_number
2264 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2265 ,p_assignment_type IN per_all_assignments_f.assignment_type%TYPE
2266 ,p_vendor_employee_number IN per_all_assignments_f.vendor_employee_number%TYPE
2267 ,p_business_group_id IN per_assignments_f.business_group_id%TYPE
2268 ,p_object_version_number IN per_all_assignments_f.object_version_number%TYPE
2269 ,p_effective_date IN DATE) IS
2270 --
2271 l_proc VARCHAR2(72) := g_package||'chk_vendor_employee_number';
2272 l_api_updating BOOLEAN;
2273 --
2274 BEGIN
2275 --
2276 hr_utility.set_location('Entering:'|| l_proc, 10);
2277 --
2278 -- Check mandatory parameters have been set
2279 --
2280 hr_api.mandatory_arg_error
2281 (p_api_name => l_proc
2282 ,p_argument => 'assignment_type'
2283 ,p_argument_value => p_assignment_type
2284 );
2285 --
2286 hr_utility.set_location(l_proc, 20);
2287 --
2288 -- Only proceed with validation if :
2289 -- a) The current g_old_rec is current and
2290 -- b) The value for vacancy has changed
2291 --
2292 l_api_updating := per_asg_shd.api_updating
2293 (p_assignment_id => p_assignment_id
2294 ,p_effective_date => p_effective_date
2295 ,p_object_version_number => p_object_version_number);
2296 --
2297 hr_utility.set_location(l_proc, 30);
2298 --
2299 IF ((l_api_updating AND
2300 NVL(per_asg_shd.g_old_rec.vendor_employee_number, hr_api.g_varchar2) <>
2301 NVL(p_vendor_employee_number, hr_api.g_varchar2)) OR
2302 (NOT l_api_updating)) THEN
2303 --
2304 hr_utility.set_location(l_proc, 40);
2305 --
2306 -- If the employee number has been populated for an
2307 -- assignment that is not a CWK assignment then
2308 -- raise an error.
2309 --
2310 IF p_vendor_employee_number IS NOT NULL AND
2311 p_assignment_type <> 'C' THEN
2312 --
2313 hr_utility.set_message(801, 'HR_289655_VEN_EMP_NO_NOT_NULL');
2314 hr_utility.raise_error;
2315 --
2316 END IF;
2317 --
2318 END IF;
2319 --
2320 hr_utility.set_location(' Leaving:'|| l_proc, 997);
2321 --
2322 EXCEPTION
2323 --
2324 WHEN app_exception.application_exception THEN
2325 --
2326 IF hr_multi_message.exception_add
2327 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.VENDOR_EMPLOYEE_NUMBER') THEN
2328 --
2329 hr_utility.set_location(' Leaving:'|| l_proc, 998);
2330 --
2331 RAISE;
2332 --
2333 END IF;
2334 --
2335 hr_utility.set_location(' Leaving:'|| l_proc, 999);
2336 --
2337 END chk_vendor_employee_number;
2338 --
2339 -- ---------------------------------------------------------------------------
2340 -- |--------------------< chk_work_at_home >-------------------------------|
2341 -- ---------------------------------------------------------------------------
2342 --
2343
2344
2345 procedure chk_work_at_home
2346 ( p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
2347 ,p_work_at_home IN per_all_assignments_f.work_at_home%TYPE
2348 ,p_effective_date IN DATE
2349 ,p_validation_start_date IN DATE
2350 ,P_VALIDATION_END_DATE IN DATE
2351 ) IS
2352
2353 -- Local declarations
2354
2355 l_proc VARCHAR2(72) := g_package||'chk_work_at_home';
2356 l_wah_lookup fnd_lookups.lookup_type%TYPE;
2357 BEGIN
2358 --
2359 hr_utility.set_location('Entering:'|| l_proc, 20);
2360 IF p_work_at_home is not null then
2361 --
2362 -- Only proceed with validation if :
2363 -- a) Inserting or
2364 -- b) The value for notice_period_uom has changed
2365 --
2366 IF ( (p_assignment_id IS NULL) OR
2367 ((p_assignment_id IS NOT NULL) AND
2368 (nvl(per_asg_shd.g_old_rec.work_at_home,hr_api.g_varchar2) <> p_work_at_home))) THEN
2369
2370 hr_utility.set_location(l_proc, 40);
2371
2372 l_wah_lookup := 'YES_NO';
2373 -- Check that the uom exists in HR_LOOKUPS
2374
2375 IF hr_api.not_exists_in_dt_hr_lookups
2376 (p_effective_date => p_effective_date
2377 ,p_lookup_type => l_wah_lookup
2378 ,p_lookup_code => p_work_at_home
2379 ,p_validation_start_date => p_validation_start_date
2383 hr_utility.set_message(800, 'HR_289364_WORK_AT_HOME_INV');
2380 ,p_validation_end_date => p_validation_end_date) THEN
2381
2382 hr_utility.set_location(l_proc, 40);
2384 hr_utility.raise_error;
2385 END IF;
2386 END IF;
2387 END IF;
2388
2389 hr_utility.set_location('Leaving: '||l_proc,100);
2390 exception
2391 when app_exception.application_exception then
2392 if hr_multi_message.exception_add
2393 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.WORK_AT_HOME'
2394 ) then
2395 hr_utility.set_location(' Leaving:'|| l_proc, 110);
2396 raise;
2397 end if;
2398 hr_utility.set_location(' Leaving:'|| l_proc, 120);
2399 END chk_work_at_home;
2400 --
2401 -- ---------------------------------------------------------------------------
2402 -- |--------------------< chk_grade_ladder_pgm_id >---------------------------|
2403 -- ---------------------------------------------------------------------------
2404 --
2405 procedure chk_grade_ladder_pgm_id
2406 ( p_grade_id in per_all_assignments_f.grade_id%TYPE
2407 ,p_grade_ladder_pgm_id in per_all_assignments_f.grade_ladder_pgm_id%TYPE
2408 ,p_business_group_id in per_all_assignments_f.business_group_id%TYPE
2409 ,p_effective_date in date
2410 ) IS
2411
2412 --
2413 -- cursor declare
2414 --
2415 cursor csr_pgm is
2416 select null
2417 from ben_pgm_f
2418 where business_group_id = p_business_group_id
2419 and pgm_typ_cd = 'GSP'
2420 and pgm_id = p_grade_ladder_pgm_id
2421 and p_effective_date
2422 between effective_start_date
2423 and effective_end_date;
2424
2425 --
2426 cursor csr_plip is
2427 select null
2428 from ben_plip_f plip
2429 ,ben_pl_f plan
2430 ,ben_pgm_f pgm
2431 where plan.mapping_table_name = 'PER_GRADES'
2432 and plan.mapping_table_pk_id = p_grade_id
2433 and plan.business_group_id = p_business_group_id
2434 and plan.pl_stat_cd = 'A'
2435 and p_effective_date
2436 between plan.effective_start_date and
2437 plan.effective_end_date
2438 and plan.pl_id = plip.pl_id
2439 and plip.business_group_id = p_business_group_id
2440 and plip.plip_stat_cd = 'A'
2441 and p_effective_date
2442 between plip.effective_start_date and
2443 plip.effective_end_date
2444 and pgm.pgm_id = p_grade_ladder_pgm_id
2445 and pgm.pgm_id = plip.pgm_id
2446 and pgm.pgm_typ_cd = 'GSP'
2447 and pgm.business_group_id = p_business_group_id
2448 and p_effective_date
2449 between pgm.effective_start_date and
2450 pgm.effective_end_date;
2451
2452 --
2453 l_proc VARCHAR2(72) := g_package||'chk_grade_ladder_pgm_id';
2454 l_exists varchar2(1);
2455 --
2456 BEGIN
2457 --
2458 hr_utility.set_location('Entering:'|| l_proc, 20);
2459 IF p_grade_ladder_pgm_id is not null and p_grade_id is null then
2460 --
2461 -- Only proceed with validation if :
2462 -- grade_ladder_pgm_id is valid
2463 --
2464 open csr_pgm;
2465 fetch csr_pgm into l_exists;
2466 if csr_pgm%notfound then
2467 close csr_pgm;
2468 hr_utility.set_location(l_proc, 30);
2469 --
2470 -- grade_ladder_pgm_id is no in ben_pgm_f table
2471 --
2472 -- Bug 2661569
2473 -- Changed the calls to hr_utility.set_message and hr_utility.raise_error
2474 hr_utility.set_message(801, 'HR_289561_GRADE_LADDER_INVALID');
2475 hr_utility.raise_error;
2476 else
2477 hr_utility.set_location(l_proc, 40);
2478 close csr_pgm;
2479 end if;
2480 ELSIF p_grade_ladder_pgm_id is not null and p_grade_id is not null then
2481 --
2482 -- Only proceed with validation if :
2483 -- grade_ladder_pgm_id and grade_id is valid
2484 --
2485 open csr_pgm;
2486 fetch csr_pgm into l_exists;
2487 if csr_pgm%notfound then
2488 close csr_pgm;
2489 hr_utility.set_location(l_proc, 50);
2490 --
2491 -- grade_ladder_pgm_id is no in ben_pgm_f table
2492 --
2493 -- Bug 2661569
2494 -- Changed the calls to hr_utility.set_message and hr_utility.raise_error
2495 hr_utility.set_message(801, 'HR_289561_GRADE_LADDER_INVALID');
2496 hr_utility.raise_error;
2497
2498 end if;
2499 close csr_pgm;
2500
2501 hr_utility.set_location(l_proc, 60);
2502
2503 open csr_plip;
2504 fetch csr_plip into l_exists;
2505 if csr_plip%notfound then
2506 hr_utility.set_location(l_proc, 70);
2507 close csr_plip;
2508 --
2509 -- The combination of grade_id and grade_ladder_pgm_id isn't in ben_plip_f
2510 --
2511 -- Bug 2661569
2512 -- Changed the calls to hr_utility.set_message and hr_utility.raise_error
2513 hr_utility.set_message(800, 'HR_289562_GRADE_NOT_IN_LADDER');
2514 hr_utility.raise_error;
2515 else
2516 close csr_plip;
2517 hr_utility.set_location(l_proc, 80);
2518 end if;
2519 END IF;
2520 hr_utility.set_location('Leaving: '||l_proc,100);
2521 exception
2522 when app_exception.application_exception then
2523 if hr_multi_message.exception_add
2524 (p_associated_column1 => 'PER_ALL_ASSIGNMENTS_F.GRADE_LADDER_PGM_ID'
2525 ) then
2526 hr_utility.set_location(' Leaving:'|| l_proc, 110);
2527 raise;
2528 end if;
2529 hr_utility.set_location(' Leaving:'|| l_proc, 120);
2530 END chk_grade_ladder_pgm_id;
2531 --
2532 -- ---------------------------------------------------------------------------
2533 -- |--------------------< access_to_primary_asg >----------------------------|
2534 -- ---------------------------------------------------------------------------
2535 --
2536 FUNCTION access_to_primary_asg
2537 (p_person_id IN NUMBER
2538 ,p_effective_date IN DATE
2539 ,p_assignment_type IN VARCHAR2)
2540 RETURN BOOLEAN IS
2541
2542 l_assignment_id NUMBER;
2543
2544 BEGIN
2545
2546 IF p_person_id IS NOT NULL AND
2547 p_effective_date IS NOT NULL AND
2548 p_assignment_type IS NOT NULL THEN
2549 --
2550 -- Retrieve the primary assignment from the assignment-level secure
2551 -- view.
2552 --
2553 SELECT paf.assignment_id
2554 INTO l_assignment_id
2555 FROM per_assignments_f2 paf
2556 WHERE paf.person_id = p_person_id
2557 AND p_effective_date BETWEEN
2558 paf.effective_start_date AND paf.effective_end_date
2559 AND paf.assignment_type = p_assignment_type
2560 AND paf.primary_flag = 'Y'
2561 AND rownum = 1;
2562
2563 ELSE
2564
2565 RAISE no_data_found;
2566
2567 END IF;
2568
2569 RETURN TRUE;
2570
2571 EXCEPTION
2572
2573 WHEN no_data_found THEN
2574
2575 RETURN FALSE;
2576
2577 END access_to_primary_asg;
2578 --
2579 end per_asg_bus3;
[Home] [Help]