1 Package Body pay_txr_bus as
2 /* $Header: pytxrrhi.pkb 120.0 2005/05/29 09:09 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_txr_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_jurisdiction_code varchar2(11) default null;
15 g_tax_type varchar2(30) default null;
16 g_tax_category varchar2(30) default null;
17 g_classification_id number default null;
18 g_taxability_rules_date_id number default null;
19 g_secondary_classification_id number default null;
20 --
21 -- ---------------------------------------------------------------------------
22 -- |----------------------< set_security_group_id >--------------------------|
23 -- ---------------------------------------------------------------------------
24 --
25 Procedure set_security_group_id
26 (p_jurisdiction_code in varchar2
27 ,p_tax_type in varchar2 default null
28 ,p_tax_category in varchar2 default null
29 ,p_classification_id in number default null
30 ,p_taxability_rules_date_id in number
31 ,p_secondary_classification_id in number default null
32 ,p_associated_column1 in varchar2 default null
33 ,p_associated_column2 in varchar2 default null
34 ,p_associated_column3 in varchar2 default null
35 ,p_associated_column4 in varchar2 default null
36 ,p_associated_column5 in varchar2 default null
37 ) is
38 --
39 -- Declare cursor
40 --
41 -- EDIT_HERE In the following cursor statement add join(s) between
42 -- pay_taxability_rules and PER_BUSINESS_GROUPS_PERF
43 -- so that the security_group_id for
44 -- the current business group context can be derived.
45 -- Remove this comment when the edit has been completed.
46 cursor csr_sec_grp is
47 select pbg.security_group_id,
48 pbg.legislation_code
49 from per_business_groups_perf pbg
50 , pay_taxability_rules txr
51 -- , EDIT_HERE table_name(s) 333
52 where txr.jurisdiction_code = p_jurisdiction_code
53 and nvl(txr.tax_type, 'X') = nvl(p_tax_type, 'X')
54 and nvl(txr.tax_category, 'X') = nvl(p_tax_category, 'X')
55 and nvl(txr.classification_id, 0) = nvl(p_classification_id, 0)
56 and nvl(txr.secondary_classification_id, 0) =
57 nvl(p_secondary_classification_id, 0)
58 and txr.taxability_rules_date_id = p_taxability_rules_date_id;
59 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
60 --
61 -- Declare local variables
62 --
63 l_security_group_id number;
64 l_proc varchar2(72) := g_package||'set_security_group_id';
65 l_legislation_code varchar2(150);
66 --
67 begin
68 --
69 hr_utility.set_location('Entering:'|| l_proc, 10);
70 --
71 -- Ensure that all the mandatory parameter are not null
72 --
73 hr_api.mandatory_arg_error
74 (p_api_name => l_proc
75 ,p_argument => 'jurisdiction_code'
76 ,p_argument_value => p_jurisdiction_code
77 );
78 /*
79 hr_api.mandatory_arg_error
80 (p_api_name => l_proc
81 ,p_argument => 'tax_type'
82 ,p_argument_value => p_tax_type
83 );
84 hr_api.mandatory_arg_error
85 (p_api_name => l_proc
86 ,p_argument => 'tax_category'
87 ,p_argument_value => p_tax_category
88 );
89 hr_api.mandatory_arg_error
90 (p_api_name => l_proc
91 ,p_argument => 'classification_id'
92 ,p_argument_value => p_classification_id
93 );
94 */
95 hr_api.mandatory_arg_error
96 (p_api_name => l_proc
97 ,p_argument => 'taxability_rules_date_id'
98 ,p_argument_value => p_taxability_rules_date_id
99 );
100 --
101 open csr_sec_grp;
102 fetch csr_sec_grp into l_security_group_id
103 , l_legislation_code;
104 --
105 if csr_sec_grp%notfound then
106 --
107 close csr_sec_grp;
108 --
109 -- The primary key is invalid therefore we must error
110 --
111 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
112 hr_multi_message.add
113 (p_associated_column1
114 => nvl(p_associated_column1,'JURISDICTION_CODE')
115 ,p_associated_column2
116 => nvl(p_associated_column2,'TAX_TYPE')
117 ,p_associated_column3
118 => nvl(p_associated_column3,'TAX_CATEGORY')
119 ,p_associated_column4
120 => nvl(p_associated_column4,'CLASSIFICATION_ID')
121 ,p_associated_column5
122 => nvl(p_associated_column5,'TAXABILITY_RULES_DATE_ID')
123 );
124 --
125 else
126 close csr_sec_grp;
127 --
128 -- Set the security_group_id in CLIENT_INFO
129 --
130 hr_api.set_security_group_id
131 (p_security_group_id => l_security_group_id
132 );
133 --
134 -- Set the sessions legislation context in HR_SESSION_DATA
135 --
136 hr_api.set_legislation_context(l_legislation_code);
137 end if;
138 --
139 hr_utility.set_location(' Leaving:'|| l_proc, 20);
140 --
141 end set_security_group_id;
142 --
143 -- ---------------------------------------------------------------------------
144 -- |---------------------< return_legislation_code >-------------------------|
145 -- ---------------------------------------------------------------------------
146 --
147 Function return_legislation_code
148 (p_jurisdiction_code in varchar2
149 ,p_tax_type in varchar2 default null
150 ,p_tax_category in varchar2 default null
151 ,p_classification_id in number default null
152 ,p_taxability_rules_date_id in number
153 ,p_secondary_classification_id in number default null
154 )
155 Return Varchar2 Is
156 --
157 -- Declare cursor
158 --
159 -- EDIT_HERE In the following cursor statement add join(s) between
160 -- pay_taxability_rules and PER_BUSINESS_GROUPS_PERF
161 -- so that the legislation_code for
162 -- the current business group context can be derived.
163 -- Remove this comment when the edit has been completed.
164 cursor csr_leg_code is
165 select pbg.legislation_code
166 from per_business_groups_perf pbg
167 , pay_taxability_rules txr
168 -- , EDIT_HERE table_name(s) 333
169 where txr.jurisdiction_code = p_jurisdiction_code
170 and nvl(txr.tax_type, 'X') = nvl(p_tax_type, 'X')
171 and nvl(txr.tax_category, 'X') = nvl(p_tax_category, 'X')
172 and nvl(txr.classification_id, 0) = nvl(p_classification_id, 0)
173 and nvl(txr.secondary_classification_id, 0) =
174 nvl(p_secondary_classification_id, 0)
175 and txr.taxability_rules_date_id = p_taxability_rules_date_id;
176 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
177 --
178 -- Declare local variables
179 --
180 l_legislation_code varchar2(150);
181 l_proc varchar2(72) := g_package||'return_legislation_code';
182 --
183 Begin
184 --
185 hr_utility.set_location('Entering:'|| l_proc, 10);
186 --
187 -- Ensure that all the mandatory parameter are not null
188 --
189 hr_api.mandatory_arg_error
190 (p_api_name => l_proc
191 ,p_argument => 'jurisdiction_code'
192 ,p_argument_value => p_jurisdiction_code
193 );
194 /*
195 hr_api.mandatory_arg_error
196 (p_api_name => l_proc
197 ,p_argument => 'tax_type'
198 ,p_argument_value => p_tax_type
199 );
200 hr_api.mandatory_arg_error
201 (p_api_name => l_proc
202 ,p_argument => 'tax_category'
203 ,p_argument_value => p_tax_category
204 );
205 hr_api.mandatory_arg_error
206 (p_api_name => l_proc
207 ,p_argument => 'classification_id'
208 ,p_argument_value => p_classification_id
209 );
210 */
211 hr_api.mandatory_arg_error
212 (p_api_name => l_proc
213 ,p_argument => 'taxability_rules_date_id'
214 ,p_argument_value => p_taxability_rules_date_id
215 );
216 --
217 if (( nvl(pay_txr_bus.g_jurisdiction_code, hr_api.g_varchar2)
218 = p_jurisdiction_code)
219 and ( nvl(pay_txr_bus.g_tax_type, hr_api.g_varchar2)
220 = nvl(p_tax_type, hr_api.g_varchar2))
221 and ( nvl(pay_txr_bus.g_tax_category, hr_api.g_varchar2)
222 = nvl(p_tax_category, hr_api.g_varchar2))
223 and ( nvl(pay_txr_bus.g_classification_id, hr_api.g_number)
224 = nvl(p_classification_id, hr_api.g_number))
225 and ( nvl(pay_txr_bus.g_secondary_classification_id, hr_api.g_number)
226 = nvl(p_secondary_classification_id, hr_api.g_number))
227 and ( nvl(pay_txr_bus.g_taxability_rules_date_id, hr_api.g_number)
228 = p_taxability_rules_date_id)) then
229 --
230 -- The legislation code has already been found with a previous
231 -- call to this function. Just return the value in the global
232 -- variable.
233 --
234 l_legislation_code := pay_txr_bus.g_legislation_code;
235 hr_utility.set_location(l_proc, 20);
236 else
237 --
238 -- The ID is different to the last call to this function
239 -- or this is the first call to this function.
240 --
241 open csr_leg_code;
242 fetch csr_leg_code into l_legislation_code;
243 --
244 if csr_leg_code%notfound then
245 --
246 -- The primary key is invalid therefore we must error
247 --
248 close csr_leg_code;
249 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
250 fnd_message.raise_error;
251 end if;
252 hr_utility.set_location(l_proc,30);
253 --
254 -- Set the global variables so the values are
255 -- available for the next call to this function.
256 --
257 close csr_leg_code;
258 pay_txr_bus.g_jurisdiction_code := p_jurisdiction_code;
259 pay_txr_bus.g_tax_type := p_tax_type;
260 pay_txr_bus.g_tax_category := p_tax_category;
261 pay_txr_bus.g_classification_id := p_classification_id;
262 pay_txr_bus.g_secondary_classification_id := p_secondary_classification_id;
263 pay_txr_bus.g_taxability_rules_date_id := p_taxability_rules_date_id;
264 pay_txr_bus.g_legislation_code := l_legislation_code;
265 end if;
266 hr_utility.set_location(' Leaving:'|| l_proc, 40);
267 return l_legislation_code;
268 end return_legislation_code;
269 --
270 -- ----------------------------------------------------------------------------
271 -- |-----------------------< chk_non_updateable_args >------------------------|
272 -- ----------------------------------------------------------------------------
273 -- {Start Of Comments}
274 --
275 -- Description:
276 -- This procedure is used to ensure that non updateable attributes have
277 -- not been updated. If an attribute has been updated an error is generated.
278 --
279 -- Pre Conditions:
280 -- g_old_rec has been populated with details of the values currently in
281 -- the database.
282 --
283 -- In Arguments:
284 -- p_rec has been populated with the updated values the user would like the
285 -- record set to.
286 --
287 -- Post Success:
288 -- Processing continues if all the non updateable attributes have not
289 -- changed.
290 --
291 -- Post Failure:
292 -- An application error is raised if any of the non updatable attributes
293 -- have been altered.
294 --
295 -- {End Of Comments}
296 -- ----------------------------------------------------------------------------
297 Procedure chk_non_updateable_args
298 (p_effective_date in date
299 ,p_rec in pay_txr_shd.g_rec_type
300 ) IS
301 --
302 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
303 --
304 Begin
305 --
306 -- Only proceed with the validation if a row exists for the current
307 -- record in the HR Schema.
308 --
309 IF NOT pay_txr_shd.api_updating
310 (p_jurisdiction_code => p_rec.jurisdiction_code
311 ,p_tax_type => p_rec.tax_type
312 ,p_tax_category => p_rec.tax_category
313 ,p_classification_id => p_rec.classification_id
314 ,p_taxability_rules_date_id => p_rec.taxability_rules_date_id
315 ,p_secondary_classification_id => p_rec.secondary_classification_id
316 ) THEN
317 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
318 fnd_message.set_token('PROCEDURE ', l_proc);
319 fnd_message.set_token('STEP ', '5');
320 fnd_message.raise_error;
321 END IF;
322 --
323 -- EDIT_HERE: Add checks to ensure non-updateable args have
324 -- not been updated.
325 --
326 End chk_non_updateable_args;
327 --
328 -- ----------------------------------------------------------------------------
329 -- |---------------------------< insert_validate >----------------------------|
330 -- ----------------------------------------------------------------------------
331 Procedure insert_validate
332 (p_effective_date in date
336 l_proc varchar2(72) := g_package||'insert_validate';
333 ,p_rec in pay_txr_shd.g_rec_type
334 ) is
335 --
337 --
338 Begin
339 hr_utility.set_location('Entering:'||l_proc, 5);
340 --
341 -- Call all supporting business operations
342 --
343 --
344 -- EDIT_HERE: As this table does not have a mandatory business_group_id
345 -- column, ensure client_info is populated by calling a suitable
346 -- ???_???_bus.set_security_group_id procedure, or add one of the following
347 -- comments:
348 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
349 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
350 --
351 -- Validate Dependent Attributes
352 --
353 --
354 hr_utility.set_location(' Leaving:'||l_proc, 10);
355 End insert_validate;
356 --
357 -- ----------------------------------------------------------------------------
358 -- |---------------------------< update_validate >----------------------------|
359 -- ----------------------------------------------------------------------------
360 Procedure update_validate
361 (p_effective_date in date
362 ,p_rec in pay_txr_shd.g_rec_type
363 ) is
364 --
365 l_proc varchar2(72) := g_package||'update_validate';
366 --
367 Begin
368 hr_utility.set_location('Entering:'||l_proc, 5);
369 --
370 -- Call all supporting business operations
371 --
372 --
373 -- EDIT_HERE: As this table does not have a mandatory business_group_id
374 -- column, ensure client_info is populated by calling a suitable
375 -- ???_???_bus.set_security_group_id procedure, or add one of the following
376 -- comments:
377 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
378 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
379 --
380 -- Validate Dependent Attributes
381 --
382 chk_non_updateable_args
383 (p_effective_date => p_effective_date
384 ,p_rec => p_rec
385 );
386 --
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 10);
389 End update_validate;
390 --
391 -- ----------------------------------------------------------------------------
392 -- |---------------------------< delete_validate >----------------------------|
393 -- ----------------------------------------------------------------------------
394 Procedure delete_validate
395 (p_rec in pay_txr_shd.g_rec_type
396 ) is
397 --
398 l_proc varchar2(72) := g_package||'delete_validate';
399 --
400 Begin
401 hr_utility.set_location('Entering:'||l_proc, 5);
402 --
403 -- Call all supporting business operations
404 --
405 hr_utility.set_location(' Leaving:'||l_proc, 10);
406 End delete_validate;
407 --
408 end pay_txr_bus;