[Home] [Help]
PACKAGE BODY: APPS.PER_CN_ASG_LEG_HOOK
Source
1 PACKAGE BODY per_cn_asg_leg_hook AS
2 /* $Header: pecnlhas.pkb 120.2 2006/06/05 07:42:37 rpalli noship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : CHECK_EMP_ASG --
7 -- Type : Procedure --
8 -- Access : Private --
9 -- Description : Procedure is the driver procedure for the validation--
10 -- of the assignment. --
11 -- This procedure is the hook procedure for the --
12 -- employee assignment. --
13 -- Parameters : --
14 -- IN : p_employer_id IN VARCHAR2 --
15 -- p_tax_area_code IN VARCHAR2 --
16 -- p_sic_area_code IN VARCHAR2 --
17 -- p_salary_payout_location IN VARCHAR2 --
18 -- p_special_tax_exmp_category IN VARCHAR2 --
19 -- p_effective_date IN DATE --
20 -- p_person_id IN NUMBER DEFAULT null --
21 -- p_assignment_id IN NUMBER DEFAULT null --
22 -- --
23 -- Change History : --
24 --------------------------------------------------------------------------
25 -- Rev# Date Userid Description --
26 --------------------------------------------------------------------------
27 -- 1.0 19/12/02 Vikram.N Created this procedure --
28 -- 1.1 16/01/03 statkar Bug 2749867 changes --
29 -- 1.2 31/01/03 statkar Added code to prevent the validation for --
30 -- default values. Bug 2676440 --
31 -- 1.3 19/09/03 statkar Added the hr_utility.chk_product_install --
32 -- check for installed CN leg (3145322) --
33 -- 1.4 18/08/04 snekkala Added code to check the Salary Payout --
34 -- Location and Special Tax Exemption --
35 -- Category
36 -- 1.5 27/04/06 vjayacha Added code to disable validations in case --
37 -- default values are passed for employer_id --
38 -- 1.6 05/06/06 rpalli Bug#5241993.Removed code for mandatory --
39 -- checks for parameters p_tax_area_code, --
40 -- p_employer_id and p_sic_area_code as they --
41 -- are handled automatically. --
42 --------------------------------------------------------------------------
43 PROCEDURE check_emp_asg(p_employer_id IN VARCHAR2
44 ,p_tax_area_code IN VARCHAR2
45 ,p_sic_area_code IN VARCHAR2
46 ,p_salary_payout_locn IN VARCHAR2 DEFAULT NULL
47 ,p_special_tax_exmp_category IN VARCHAR2 DEFAULT NULL
48 ,p_effective_date IN DATE
49 ,p_person_id IN NUMBER DEFAULT null
50 ,p_assignment_id IN NUMBER DEFAULT null) AS
51
52 l_proc VARCHAR2(72);
53 l_business_group_id per_all_people_f.business_group_id%TYPE;
54 l_trunc_effective_date DATE;
55
56 CURSOR csr_business_group_per(p_person_id NUMBER,p_effective_date DATE) IS
57 SELECT business_group_id
58 FROM per_all_people_f
59 WHERE person_id = p_person_id
60 AND p_effective_date BETWEEN effective_start_date
61 AND effective_end_date;
62
63 CURSOR csr_business_group_ass(p_assignment_id NUMBER,p_effective_date DATE) IS
64 SELECT business_group_id
65 FROM per_all_assignments_f
66 WHERE assignment_id = p_assignment_id
67 AND p_effective_date BETWEEN effective_start_date
68 AND effective_end_date;
69
70 g_trace boolean;
71
72 BEGIN
73
74 g_trace := hr_cn_assignment_api.g_trace;
75 l_proc := g_package||'check_emp_asg';
76 --
77 hr_cn_api.set_location(g_trace,'Entering: '||l_proc, 10);
78
79 --
80 -- Bug 3145322 Check the leg-specific validations only if the legislation
81 -- is installed
82 --
83 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'CN') THEN
84 hr_utility.trace ('CN Legislation not installed. Not performing the validations');
85 hr_cn_api.set_location(g_trace,'Leaving: '||l_proc, 15);
86 RETURN;
87 END IF;
88 --
89 -- Check for the mandatory arguments
90 --
91 g_trace := TRUE;
92 hr_cn_api.set_location(g_trace,l_proc,20);
93
94 --
95 -- Check for valid lookups
96 --
97 hr_cn_api.set_location(g_trace,l_proc,40);
98 IF p_tax_area_code <> hr_api.g_varchar2 THEN
99 hr_cn_api.check_lookup (
100 p_lookup_type => 'CN_TAX_AREA',
101 p_argument => 'P_TAX_AREA_CODE',
102 p_argument_value => p_tax_area_code
103 );
104 END IF;
105 --
106 --
107 -- Bug 2955433 SIC Area Code now is made mandatory
108 --
109 hr_cn_api.set_location(g_trace,l_proc,50);
110 IF p_sic_area_code <> hr_api.g_varchar2
111 THEN
112 hr_cn_api.check_lookup (
113 p_lookup_type => 'CN_SIC_AREA',
114 p_argument => 'P_SIC_AREA_CODE',
115 p_argument_value => p_sic_area_code
116 );
117 END IF;
118
119 --
120 -- Bug 3828396 Added code to check Salary Payout Location and Special Tax Exemption category
121 --
122 hr_cn_api.set_location(g_trace,l_proc,60);
123 IF p_salary_payout_locn <> hr_api.g_varchar2
124 THEN
125 hr_cn_api.check_lookup (
126 p_lookup_type => 'CN_PAYOUT_LOCATION',
127 p_argument => 'P_SALARY_PAYOUT_LOCN',
128 p_argument_value => p_salary_payout_locn
129 );
130 END IF;
131
132 hr_cn_api.set_location(g_trace,l_proc,70);
133 IF p_special_tax_exmp_category <> hr_api.g_varchar2
134 THEN
135 hr_cn_api.check_lookup (
136 p_lookup_type => 'CN_SPL_TAX_EXMP_CATEGORY',
137 p_argument => 'P_SPECIAL_TAX_EXMP_CATEGORY',
138 p_argument_value => p_special_tax_exmp_category
139 );
140 END IF;
141 --
142 -- Bug 3828396 changes end
143 --
144 --
145 --Trunc of date will return date without the time part.
146 --
147 hr_cn_api.set_location(g_trace,l_proc,80);
148 l_trunc_effective_date := TRUNC(p_effective_date);
149
150 IF p_assignment_id IS null THEN
151
152 hr_cn_api.check_person(p_person_id, 'CN', l_trunc_effective_date);
153
154 OPEN csr_business_group_per(p_person_id,l_trunc_effective_date);
155 FETCH csr_business_group_per INTO l_business_group_id;
156 CLOSE csr_business_group_per;
157
158 ELSIF p_person_id IS null THEN
159
160 hr_cn_api.check_assignment(p_assignment_id, 'CN', l_trunc_effective_date);
161
162 OPEN csr_business_group_ass(p_assignment_id,l_trunc_effective_date);
163 FETCH csr_business_group_ass INTO l_business_group_id;
164 CLOSE csr_business_group_ass;
165
166 END IF;
167
168 IF p_employer_id <> hr_api.g_varchar2
169 THEN
170
171 hr_cn_api.check_organization
172 (p_organization_id => p_employer_id
173 ,p_business_group_id => l_business_group_id
174 ,p_legislation_code => 'CN'
175 ,p_effective_date => l_trunc_effective_date
176 ) ;
177
178 hr_cn_api.check_org_class
179 (p_organization_id => p_employer_id
180 ,p_classification => 'PER_EMPLOYER_INFO_CN'
181 ) ;
182
183 hr_cn_api.check_org_type
184 (p_organization_id => p_employer_id
185 ,p_type => 'HR_LEGAL'
186 );
187 END IF;
188
189 hr_cn_api.set_location(g_trace,l_proc,120);
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF csr_business_group_per%ISOPEN OR csr_business_group_ass%ISOPEN THEN
194 CLOSE csr_business_group_per;
195 CLOSE csr_business_group_ass;
196 END IF;
197 RAISE;
198
199 END check_emp_asg;
200
201
202 --------------------------------------------------------------------------
203 -- --
204 -- Name : CHECK_EMP_ASG_UPDATE --
205 -- Type : Procedure --
206 -- Access : Public --
207 -- Description : Procedure is the driver procedure for the validation--
208 -- of the assignment. --
209 -- This procedure is the hook procedure for update --
210 -- employee assignment. --
211 -- Parameters : --
212 -- IN : p_segment1 IN VARCHAR2 --
213 -- p_segment20 IN VARCHAR2 --
214 -- p_segment21 IN VARCHAR2 --
215 -- p_segment22 IN VARCHAR2 --
216 -- p_segment23 IN VARCHAR2 --
217 -- p_effective_date IN DATE --
218 -- p_assignment_id IN NUMBER --
219 -- --
220 -- Change History : --
221 --------------------------------------------------------------------------
222 -- Rev# Date Userid Description --
223 --------------------------------------------------------------------------
224 -- 1.0 19/12/02 Vikram.N Created this procedure --
225 -- 1.1 18/08/04 snekkala Added p_segment22 and p_segment23 --
226 --------------------------------------------------------------------------
227 PROCEDURE check_emp_asg_update (p_segment1 IN VARCHAR2
228 ,P_segment20 IN VARCHAR2
229 ,p_segment21 IN VARCHAR2
230 ,P_segment22 IN VARCHAR2
231 ,p_segment23 IN VARCHAR2
232 ,p_effective_date IN DATE
233 ,p_assignment_id IN NUMBER ) AS
234
235
236 l_proc VARCHAR2(72);
237
238 BEGIN
239
240 l_proc := g_package || 'check_emp_asg_update';
241 --
242 hr_cn_api.set_location(hr_cn_assignment_api.g_trace,'Entering: '||l_proc,10);
243
244 check_emp_asg
245 (p_employer_id => p_segment1
246 ,p_tax_area_code => p_segment20
247 ,p_sic_area_code => p_segment21
248 ,p_salary_payout_locn => p_segment22
249 ,p_special_tax_exmp_category => p_segment23
250 ,p_effective_date => p_effective_date
251 ,p_assignment_id => p_assignment_id);
252
253
254 hr_cn_api.set_location(hr_cn_assignment_api.g_trace,'Leaving: '||l_proc,20);
255
256 END check_emp_asg_update;
257
258 --------------------------------------------------------------------------
259 -- --
260 -- Name : CHECK_EMP_ASG_CREATE --
261 -- Type : Procedure --
262 -- Access : Public --
263 -- Description : Procedure is the driver procedure for the validation--
264 -- of the assignment. --
265 -- This procedure is the hook procedure for create --
266 -- secondary employee assignment. --
267 -- Parameters : --
268 -- IN : p_scl_segment1 IN VARCHAR2 --
269 -- p_scl_segment20 IN VARCHAR2 --
270 -- p_scl_segment21 IN VARCHAR2 --
271 -- p_scl_segment22 IN VARCHAR2 --
272 -- p_scl_segment23 IN VARCHAR2 --
273 -- p_effective_date IN DATE --
274 -- p_person_id IN NUMBER --
275 -- --
276 -- Change History : --
277 --------------------------------------------------------------------------
278 -- Rev# Date Userid Description --
279 --------------------------------------------------------------------------
280 -- 1.0 19/12/02 Vikram.N Created this procedure --
281 -- 1.1 18/08/04 snekkala Added p_scl_segment22 and p_scl_segment23 --
282 --------------------------------------------------------------------------
283
284 PROCEDURE check_emp_asg_create (p_scl_segment1 IN VARCHAR2
285 ,P_scl_segment20 IN VARCHAR2
286 ,p_scl_segment21 IN VARCHAR2
287 ,P_scl_segment22 IN VARCHAR2
288 ,p_scl_segment23 IN VARCHAR2
289 ,p_effective_date IN DATE
290 ,p_person_id IN NUMBER ) AS
291
292
293 l_proc VARCHAR2(72);
294
295
296 BEGIN
297
298 l_proc := g_package||'check_emp_asg_create';
299 hr_cn_api.set_location(hr_cn_assignment_api.g_trace,'Entering: '||l_proc,10);
300
301 check_emp_asg
302 (p_employer_id => p_scl_segment1
303 ,p_tax_area_code => p_scl_segment20
304 ,p_sic_area_code => p_scl_segment21
305 ,p_salary_payout_locn => p_scl_segment22
306 ,p_special_tax_exmp_category => p_scl_segment23
307 ,p_effective_date => p_effective_date
308 ,p_person_id => p_person_id);
309
310
311 hr_cn_api.set_location(hr_cn_assignment_api.g_trace,'Leaving: '||l_proc,20);
312
313 END check_emp_asg_create;
314
315 BEGIN
316
317 g_package := 'per_cn_asg_leg_hook.';
318
319 END per_cn_asg_leg_hook;