[Home] [Help]
PACKAGE BODY: APPS.PE_FR_ADDITIONAL_ORG_RULES
Source
1 PACKAGE BODY pe_fr_additional_org_rules AS
2 /* $Header: pefrorgh.pkb 120.3.12000000.2 2007/02/28 10:38:42 spendhar ship $ */
3 g_package varchar2(80) := 'pe_fr_additional_org_rules';
4
5 PROCEDURE fr_validate_org_info_ins
6 (p_effective_date IN DATE
7 ,p_organization_id IN NUMBER
8 ,p_org_info_type_code IN VARCHAR2
9 ,p_org_information1 IN VARCHAR2
10 ,p_org_information2 IN VARCHAR2
11 ,p_org_information3 IN VARCHAR2
12 ,p_org_information4 IN VARCHAR2
13 ,p_org_information5 IN VARCHAR2
14 ,p_org_information6 IN VARCHAR2
15 ,p_org_information7 IN VARCHAR2
16 ,p_org_information8 IN VARCHAR2
17 ,p_org_information9 IN VARCHAR2
18 ,p_org_information10 IN VARCHAR2
19 ,p_org_information11 IN VARCHAR2
20 ,p_org_information12 IN VARCHAR2
21 ,p_org_information13 IN VARCHAR2
22 ,p_org_information14 IN VARCHAR2
23 ,p_org_information15 IN VARCHAR2
24 ,p_org_information16 IN VARCHAR2
25 ,p_org_information17 IN VARCHAR2
26 ,p_org_information18 IN VARCHAR2
27 ,p_org_information19 IN VARCHAR2
28 ,p_org_information20 IN VARCHAR2
29 ) IS
30 l_proc varchar2(72) := g_package || '.fr_validate_org_info_ins';
31 Begin
32 --
33 /* Added for GSI Bug 5472781 */
34 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
35 hr_utility.set_location('Leaving : '||l_proc , 10);
36 return;
37 END IF;
38 --
39 hr_utility.set_location('Entering '|| l_proc , 10);
40 pe_fr_additional_org_rules.fr_validate_org_info(
41 p_effective_date => p_effective_date
42 ,p_org_info_type_code => p_org_info_type_code
43 ,p_organization_id => p_organization_id
44 ,p_org_information1 => p_org_information1
45 ,p_org_information2 => p_org_information2
46 ,p_org_information3 => p_org_information3
47 ,p_org_information4 => p_org_information4
48 ,p_org_information5 => p_org_information5);
49 --
50 hr_utility.set_location('Leaving '||l_proc,800);
51 --
52 END fr_validate_org_info_ins;
53
54 PROCEDURE fr_validate_org_info_upd
55 (p_effective_date IN DATE
56 ,p_org_info_type_code IN VARCHAR2
57 ,p_org_information1 IN VARCHAR2
58 ,p_org_information2 IN VARCHAR2
59 ,p_org_information3 IN VARCHAR2
60 ,p_org_information4 IN VARCHAR2
61 ,p_org_information5 IN VARCHAR2
62 ,p_org_information6 IN VARCHAR2
63 ,p_org_information7 IN VARCHAR2
64 ,p_org_information8 IN VARCHAR2
65 ,p_org_information9 IN VARCHAR2
66 ,p_org_information10 IN VARCHAR2
67 ,p_org_information11 IN VARCHAR2
68 ,p_org_information12 IN VARCHAR2
69 ,p_org_information13 IN VARCHAR2
70 ,p_org_information14 IN VARCHAR2
71 ,p_org_information15 IN VARCHAR2
72 ,p_org_information16 IN VARCHAR2
73 ,p_org_information17 IN VARCHAR2
74 ,p_org_information18 IN VARCHAR2
75 ,p_org_information19 IN VARCHAR2
76 ,p_org_information20 IN VARCHAR2
77 ,p_org_information_id IN NUMBER
78 ,p_object_version_number IN NUMBER
79 ) IS
80 l_proc varchar2(72) := g_package || '.fr_validate_org_info_upd';
81 BEGIN
82 --
83 /* Added for GSI Bug 5472781 */
84 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'FR') THEN
85 hr_utility.set_location('Leaving : '||l_proc , 10);
86 return;
87 END IF;
88 --
89 hr_utility.set_location('Entering '||l_proc , 10);
90
91 pe_fr_additional_org_rules.fr_validate_org_info(
92 p_effective_date => p_effective_date
93 ,p_org_info_type_code => p_org_info_type_code
94 ,p_org_information1 => p_org_information1
95 ,p_org_information2 => p_org_information2
96 ,p_org_information3 => p_org_information3
97 ,p_org_information4 => p_org_information4
98 ,p_org_information5 => p_org_information5
99 ,p_org_information_id => p_org_information_id
100 ,p_object_version_number => p_object_version_number);
101
102 hr_utility.set_location('Leaving '||l_proc,800);
103 --
104 END fr_validate_org_info_upd;
105
106 PROCEDURE fr_validate_org_info
107 (p_effective_date IN DATE
108 ,p_org_info_type_code IN VARCHAR2
109 ,p_org_information1 IN VARCHAR2
110 ,p_org_information2 IN VARCHAR2
111 ,p_org_information3 IN VARCHAR2
112 ,p_org_information4 IN VARCHAR2
113 ,p_org_information5 IN VARCHAR2
114 ,p_organization_id IN NUMBER default null /* ins only */
115 ,p_org_information_id IN NUMBER default null /* upd only */
116 ,p_object_version_number IN NUMBER default null /* upd only */
117 )
118 IS
119 cursor csr_get_org_id (p_org_information_id number) is
120 select organization_id
121 from hr_organization_information
122 where org_information_id = p_org_information_id;
123
124 cursor csr_wa_check(
125 p_context varchar2
126 ,p_org_id hr_organization_information.organization_id%TYPE
127 ,p_org_info_id hr_organization_information.org_information_id%TYPE
128 ,p_wa_type hr_organization_information.org_information4%TYPE
129 ,p_wa_order hr_organization_information.org_information5%TYPE) is
130 select null
131 from hr_organization_information
132 where org_information_context = p_context
133 and organization_id = p_org_id
134 and org_information_id <> nvl(p_org_info_id, -1)
135 and org_information3 = p_wa_type
136 and org_information5 <> p_wa_order;
137
138 cursor csr_chk_overlaps(
139 p_context varchar2
140 ,p_org_id hr_organization_information.organization_id%TYPE
141 ,p_org_info_id hr_organization_information.org_information_id%TYPE
142 ,p_org_info1 hr_organization_information.org_information1%TYPE
143 ,p_org_info2 hr_organization_information.org_information2%TYPE
144 ,p_org_info3 hr_organization_information.org_information3%TYPE
145 ,p_sot varchar2
146 ,p_eot varchar2) is
147 select 'Y'
148 from hr_organization_information
149 where org_information_context = p_context
150 and organization_id = p_org_id
151 and org_information_id <> nvl(p_org_info_id, -1)
152 and p_org_info2 >= nvl(org_information1,p_sot)
153 and p_org_info1 <= nvl(org_information2,p_eot)
154 and (p_org_info3 is null or
155 p_org_info3 = nvl(org_information3,'NULL_CONTEXTUAL_SEG'))
156 and rownum < 2;
157
158 l_proc varchar2(72) := g_package || '.fr_validate_org_info';
159 l_sot varchar2(30);
160 l_eot varchar2(30);
161 l_org_id hr_organization_information.organization_id%TYPE;
162 l_org_info3 hr_organization_information.org_information3%TYPE;
163 l_overlap varchar2(1);
164
165 BEGIN
166 --
167 hr_utility.set_location('Entering '||l_proc , 10);
168 --
169 if p_org_info_type_code in ('FR_COMP_ACCRUAL_RATE'
170 ,'FR_COMP_CADRE_LIFE_INSURE'
171 ,'FR_COMP_COMP_PENSION_T2'
172 ,'FR_COMP_TRAINING_CONTRIB'
173 ,'FR_ESTAB_ACCRUAL_RATE'
174 ,'FR_ESTAB_APPRENT_TAX_LIABILITY'
175 ,'FR_ESTAB_OVERRIDE_FIXED_TERM'
176 ,'FR_ESTAB_PART_TIME_REBATE'
177 ,'FR_ESTAB_ROBIEN'
178 ,'FR_ESTAB_SALARY_TAX_LIABILITY'
179 ,'FR_ESTAB_SS_REBATE'
180 ,'FR_ESTAB_TRANSPORT_TAX'
181 ,'FR_ESTAB_WELFARE_TAX_LIABILITY'
182 ,'FR_ESTAB_WORK_ACCIDENT'
183 ,'FR_HISTORICAL_MONTHLY_REF_HRS'
184 ,'FR_ESTAB_AUBRY_II'
185 ,'FR_ESTAB_CONSTR_TAX_LIABILITY') THEN
186 --
187 -- derive values of variables
188 --
189 l_sot := fnd_date.date_to_canonical(hr_general.START_OF_TIME);
190 l_eot := fnd_date.date_to_canonical(hr_general.END_OF_TIME);
191 --
192 -- check date_from > date_to
193 --
194 IF p_org_information1 > p_org_information2
195 THEN
196 fnd_message.set_name('PER','PER_289697_START_BEFORE_END');
197 fnd_message.raise_error;
198 END IF;
199 --
200 -- Get the organization_id if update
201 --
202 IF p_org_information_id is not null
203 THEN
204 open csr_get_org_id (p_org_information_id);
205 fetch csr_get_org_id into l_org_id;
206 close csr_get_org_id;
207 ELSE
208 l_org_id := p_organization_id;
209 END IF;
210 --
211 -- check for overlaps
212 --
213 IF p_org_info_type_code = 'FR_ESTAB_TRANSPORT_TAX'
214 THEN
215 l_org_info3 := p_org_information3;
216 ELSIF p_org_info_type_code = 'FR_ESTAB_WORK_ACCIDENT'
217 THEN
218 l_org_info3 := nvl(p_org_information3,'NULL_CONTEXTUAL_SEG');
219 END IF;
220 open csr_chk_overlaps(p_org_info_type_code,l_org_id,
221 p_org_information_id,
222 nvl(p_org_information1,l_sot),
223 nvl(p_org_information2,l_eot),
224 l_org_info3, l_sot, l_eot);
225 fetch csr_chk_overlaps into l_overlap;
226 if csr_chk_overlaps%FOUND then
227 IF p_org_info_type_code = 'FR_ESTAB_TRANSPORT_TAX'
228 THEN fnd_message.set_name('PAY','PER_75066_TSPORT_TAX_OVERLAP');
229 ELSIF p_org_info_type_code = 'FR_ESTAB_WORK_ACCIDENT'
230 THEN fnd_message.set_name('PAY','PER_75067_WORK_ACCI_OVERLAP');
231 ELSE fnd_message.set_name('PAY','PER_75068_START_END_OVERLAP');
232 END IF;
233 close csr_chk_overlaps;
234 fnd_message.raise_error;
235 END IF; -- if csr_chk_overlaps%FOUND
236 close csr_chk_overlaps;
237 END IF; -- p_org_info_type_code in ...
238 hr_utility.set_location('Leaving '||l_proc,800);
239 --
240 END fr_validate_org_info;
241 END pe_fr_additional_org_rules;