1 PACKAGE BODY hr_nl_asg_extra_info_checks AS
2 /* $Header: penlaeiv.pkb 120.4 2011/03/22 18:38:15 abraghun ship $ */
3 --
4 --
5 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
6 -- Sets the Profile PER_ASSIGNMENT_ID to the Current Assignment
7 -- for Valueset to validate correctly.
8 -- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
9 --
10 PROCEDURE set_asg_id (p_assignment_id in number) IS
11 BEGIN
12
13 /* Added for GSI Bug 5472781 */
14 IF hr_utility.chk_product_install('Oracle Human Resources', 'NL') THEN
15 --
16 --
17 -- Set the Profile ID
18 --
19 FND_PROFILE.put('PER_ASSIGNMENT_ID',p_assignment_id);
20
21 END IF;/* Added for GSI Bug 5472781 */
22
23 END set_asg_id;
24
25 PROCEDURE CHECK_NUMIV_OVERRIDE(P_ASSIGNMENT_EXTRA_INFO_ID in NUMBER
26 ,P_AEI_INFORMATION1 in VARCHAR2) IS
27
28 CURSOR csr_assignments IS
29 SELECT paaf1.assignment_id
30 FROM per_all_assignments_f paaf1
31 , per_assignment_extra_info paei
32 , per_all_assignments_f paaf
33 WHERE paei.assignment_extra_info_id=P_ASSIGNMENT_EXTRA_INFO_ID
34 AND paei.information_type='NL_NUMIV_OVERRIDE'
35 AND paaf.assignment_id=paei.assignment_id
36 AND paaf.person_id=paaf1.person_id
37 AND paaf1.assignment_id <>paaf.assignment_id
38 AND paaf1.assignment_type = 'E'; --11723380
39
40 CURSOR csr_numiv_override (c_asg_id number)is
41 SELECT aei_information1 NUMIV_OVERRIDE
42 , paaf.assignment_sequence ASSIGNMENT_SEQUENCE
43 FROM per_assignment_extra_info paei
44 , per_all_assignments_f paaf
45 WHERE paaf.assignment_id = c_asg_id
46 AND paei.aei_information_category(+) = 'NL_NUMIV_OVERRIDE'
47 AND paei.assignment_id(+)= paaf.assignment_id;
48
49 l_numiv_override NUMBER;
50 l_assignment_sequence NUMBER;
51 l_override_present BOOLEAN:= FALSE;
52
53
54 BEGIN
55 l_override_present:=false;
56 FOR csr_assignments_rec IN csr_assignments
57 LOOP
58
59 OPEN csr_numiv_override(csr_assignments_rec.assignment_id);
60 FETCH csr_numiv_override INTO l_numiv_override,l_assignment_sequence;
61 CLOSE csr_numiv_override;
62
63 IF (nvl(P_AEI_INFORMATION1,500) = nvl(l_numiv_override,501) AND l_numiv_override IS NOT NULL AND P_AEI_INFORMATION1 IS NOT NULL )
64 OR (P_AEI_INFORMATION1 = l_assignment_sequence AND l_numiv_override is null)
65 THEN
66 l_override_present:= TRUE;
67
68 END IF;
69
70 END LOOP;
71
72 IF l_override_present THEN
73 hr_utility.set_message(800,'HR_373547_NUMIV_OVERRIDE');
74 hr_utility.raise_error;
75 END IF;
76
77 END;
78
79
80 FUNCTION ASG_CHECK_NUMIV_OVERRIDE(
81 P_ASSIGNMENT_ID in NUMBER
82 ,P_AEI_INFORMATION1 in VARCHAR2
83 ) return number IS
84
85
86
87 cursor csr_assignments is
88 SELECT paaf1.assignment_id
89 FROM per_all_assignments_f paaf1
90 , per_all_assignments_f paaf
91 WHERE paaf.assignment_id=P_ASSIGNMENT_ID
92 AND paaf.person_id=paaf1.person_id
93 AND paaf1.assignment_id <>paaf.assignment_id
94 AND paaf1.assignment_type = 'E'; --11723380
95
96 CURSOR csr_numiv_override (c_asg_id number)is
97 SELECT aei_information1 NUMIV_OVERRIDE
98 , paaf.assignment_sequence ASSIGNMENT_SEQUENCE
99 FROM per_assignment_extra_info paei, per_all_assignments_f paaf
100 WHERE paaf.assignment_id = c_asg_id
101 AND paei.aei_information_category(+) = 'NL_NUMIV_OVERRIDE'
102 AND paei.assignment_id(+)= paaf.assignment_id;
103
104 l_numiv_override NUMBER;
105 l_assignment_sequence NUMBER;
106 l_override_present BOOLEAN:= FALSE;
107
108
109 BEGIN
110
111 l_override_present:=false;
112 FOR csr_assignments_rec in csr_assignments
113 LOOP
114 hr_utility.set_location('P_AEI_INFORMATION1'||P_AEI_INFORMATION1,10);
115 OPEN csr_numiv_override(csr_assignments_rec.assignment_id);
116 FETCH csr_numiv_override INTO l_numiv_override,l_assignment_sequence;
117 CLOSE csr_numiv_override;
118
119 IF (nvl(P_AEI_INFORMATION1,500) = nvl(l_numiv_override,501) and l_numiv_override is not null and P_AEI_INFORMATION1 is not null )
120 OR (P_AEI_INFORMATION1 = l_assignment_sequence and l_numiv_override is null) THEN
121 RETURN 1;
122 END IF;
123 END LOOP;
124 RETURN 0;
125 END ASG_CHECK_NUMIV_OVERRIDE;
126 END hr_nl_asg_extra_info_checks;
[Home] [Help]