[Home] [Help]
PACKAGE BODY: APPS.PQH_TJR_BUS
Source
1 Package Body pqh_tjr_bus as
2 /* $Header: pqtjrrhi.pkb 115.3 2002/12/12 21:47:19 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_tjr_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_txn_job_requirement_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_txn_job_requirement_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pqh_txn_job_requirements tjr
30 where tjr.txn_job_requirement_id = p_txn_job_requirement_id
31 and pbg.business_group_id = tjr.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'txn_job_requirement_id'
47 ,p_argument_value => p_txn_job_requirement_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_txn_job_requirement_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , pqh_txn_job_requirements tjr
90 where tjr.txn_job_requirement_id = p_txn_job_requirement_id
91 and pbg.business_group_id = tjr.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'txn_job_requirement_id'
107 ,p_argument_value => p_txn_job_requirement_id
108 );
109 --
110 if ( nvl(pqh_tjr_bus.g_txn_job_requirement_id, hr_api.g_number)
111 = p_txn_job_requirement_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := pqh_tjr_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 pqh_tjr_bus.g_txn_job_requirement_id := p_txn_job_requirement_id;
142 pqh_tjr_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |------------------------------< chk_df >----------------------------------|
150 -- ----------------------------------------------------------------------------
151 --
152 -- Description:
153 -- Validates all the Descriptive Flexfield values.
154 --
155 -- Prerequisites:
156 -- All other columns have been validated. Must be called as the
157 -- last step from insert_validate and update_validate.
158 --
159 -- In Arguments:
160 -- p_rec
161 --
162 -- Post Success:
163 -- If the Descriptive Flexfield structure column and data values are
164 -- all valid this procedure will end normally and processing will
165 -- continue.
166 --
167 -- Post Failure:
168 -- If the Descriptive Flexfield structure column value or any of
169 -- the data values are invalid then an application error is raised as
170 -- a PL/SQL exception.
171 --
172 -- Access Status:
173 -- Internal Row Handler Use Only.
174 --
175 -- ----------------------------------------------------------------------------
176 procedure chk_df
177 (p_rec in pqh_tjr_shd.g_rec_type
178 ) is
179 --
180 l_proc varchar2(72) := g_package || 'chk_df';
181 --
182 begin
183 hr_utility.set_location('Entering:'||l_proc,10);
184 --
185 if ((p_rec.txn_job_requirement_id is not null) and (
186 nvl(pqh_tjr_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
187 nvl(p_rec.attribute_category, hr_api.g_varchar2) or
188 nvl(pqh_tjr_shd.g_old_rec.attribute1, hr_api.g_varchar2) <>
189 nvl(p_rec.attribute1, hr_api.g_varchar2) or
190 nvl(pqh_tjr_shd.g_old_rec.attribute2, hr_api.g_varchar2) <>
191 nvl(p_rec.attribute2, hr_api.g_varchar2) or
192 nvl(pqh_tjr_shd.g_old_rec.attribute3, hr_api.g_varchar2) <>
193 nvl(p_rec.attribute3, hr_api.g_varchar2) or
194 nvl(pqh_tjr_shd.g_old_rec.attribute4, hr_api.g_varchar2) <>
195 nvl(p_rec.attribute4, hr_api.g_varchar2) or
196 nvl(pqh_tjr_shd.g_old_rec.attribute5, hr_api.g_varchar2) <>
200 nvl(pqh_tjr_shd.g_old_rec.attribute7, hr_api.g_varchar2) <>
197 nvl(p_rec.attribute5, hr_api.g_varchar2) or
198 nvl(pqh_tjr_shd.g_old_rec.attribute6, hr_api.g_varchar2) <>
199 nvl(p_rec.attribute6, hr_api.g_varchar2) or
201 nvl(p_rec.attribute7, hr_api.g_varchar2) or
202 nvl(pqh_tjr_shd.g_old_rec.attribute8, hr_api.g_varchar2) <>
203 nvl(p_rec.attribute8, hr_api.g_varchar2) or
204 nvl(pqh_tjr_shd.g_old_rec.attribute9, hr_api.g_varchar2) <>
205 nvl(p_rec.attribute9, hr_api.g_varchar2) or
206 nvl(pqh_tjr_shd.g_old_rec.attribute10, hr_api.g_varchar2) <>
207 nvl(p_rec.attribute10, hr_api.g_varchar2) or
208 nvl(pqh_tjr_shd.g_old_rec.attribute11, hr_api.g_varchar2) <>
209 nvl(p_rec.attribute11, hr_api.g_varchar2) or
210 nvl(pqh_tjr_shd.g_old_rec.attribute12, hr_api.g_varchar2) <>
211 nvl(p_rec.attribute12, hr_api.g_varchar2) or
212 nvl(pqh_tjr_shd.g_old_rec.attribute13, hr_api.g_varchar2) <>
213 nvl(p_rec.attribute13, hr_api.g_varchar2) or
214 nvl(pqh_tjr_shd.g_old_rec.attribute14, hr_api.g_varchar2) <>
215 nvl(p_rec.attribute14, hr_api.g_varchar2) or
216 nvl(pqh_tjr_shd.g_old_rec.attribute15, hr_api.g_varchar2) <>
217 nvl(p_rec.attribute15, hr_api.g_varchar2) or
218 nvl(pqh_tjr_shd.g_old_rec.attribute16, hr_api.g_varchar2) <>
219 nvl(p_rec.attribute16, hr_api.g_varchar2) or
220 nvl(pqh_tjr_shd.g_old_rec.attribute17, hr_api.g_varchar2) <>
221 nvl(p_rec.attribute17, hr_api.g_varchar2) or
222 nvl(pqh_tjr_shd.g_old_rec.attribute18, hr_api.g_varchar2) <>
223 nvl(p_rec.attribute18, hr_api.g_varchar2) or
224 nvl(pqh_tjr_shd.g_old_rec.attribute19, hr_api.g_varchar2) <>
225 nvl(p_rec.attribute19, hr_api.g_varchar2) or
226 nvl(pqh_tjr_shd.g_old_rec.attribute20, hr_api.g_varchar2) <>
227 nvl(p_rec.attribute20, hr_api.g_varchar2) ))
228 or (p_rec.txn_job_requirement_id is null) then
229 --
230 -- Only execute the validation if absolutely necessary:
231 -- a) During update, the structure column value or any
232 -- of the attribute values have actually changed.
233 -- b) During insert.
234 --
235 hr_dflex_utility.ins_or_upd_descflex_attribs
236 (p_appl_short_name => 'PER'
237 ,p_descflex_name => 'PER_JOB_REQUIREMENTS'
238 ,p_attribute_category => p_rec.attribute_category
239 ,p_attribute1_name => 'ATTRIBUTE1'
240 ,p_attribute1_value => p_rec.attribute1
241 ,p_attribute2_name => 'ATTRIBUTE2'
242 ,p_attribute2_value => p_rec.attribute2
243 ,p_attribute3_name => 'ATTRIBUTE3'
244 ,p_attribute3_value => p_rec.attribute3
245 ,p_attribute4_name => 'ATTRIBUTE4'
246 ,p_attribute4_value => p_rec.attribute4
247 ,p_attribute5_name => 'ATTRIBUTE5'
248 ,p_attribute5_value => p_rec.attribute5
249 ,p_attribute6_name => 'ATTRIBUTE6'
250 ,p_attribute6_value => p_rec.attribute6
251 ,p_attribute7_name => 'ATTRIBUTE7'
252 ,p_attribute7_value => p_rec.attribute7
253 ,p_attribute8_name => 'ATTRIBUTE8'
254 ,p_attribute8_value => p_rec.attribute8
255 ,p_attribute9_name => 'ATTRIBUTE9'
256 ,p_attribute9_value => p_rec.attribute9
257 ,p_attribute10_name => 'ATTRIBUTE10'
258 ,p_attribute10_value => p_rec.attribute10
259 ,p_attribute11_name => 'ATTRIBUTE11'
260 ,p_attribute11_value => p_rec.attribute11
261 ,p_attribute12_name => 'ATTRIBUTE12'
262 ,p_attribute12_value => p_rec.attribute12
263 ,p_attribute13_name => 'ATTRIBUTE13'
264 ,p_attribute13_value => p_rec.attribute13
265 ,p_attribute14_name => 'ATTRIBUTE14'
266 ,p_attribute14_value => p_rec.attribute14
267 ,p_attribute15_name => 'ATTRIBUTE15'
268 ,p_attribute15_value => p_rec.attribute15
269 ,p_attribute16_name => 'ATTRIBUTE16'
270 ,p_attribute16_value => p_rec.attribute16
271 ,p_attribute17_name => 'ATTRIBUTE17'
272 ,p_attribute17_value => p_rec.attribute17
273 ,p_attribute18_name => 'ATTRIBUTE18'
274 ,p_attribute18_value => p_rec.attribute18
275 ,p_attribute19_name => 'ATTRIBUTE19'
276 ,p_attribute19_value => p_rec.attribute19
277 ,p_attribute20_name => 'ATTRIBUTE20'
278 ,p_attribute20_value => p_rec.attribute20
279 );
280 end if;
281 --
282 hr_utility.set_location(' Leaving:'||l_proc,20);
283 end chk_df;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |-----------------------< chk_non_updateable_args >------------------------|
287 -- ----------------------------------------------------------------------------
288 -- {Start Of Comments}
289 --
290 -- Description:
291 -- This procedure is used to ensure that non updateable attributes have
292 -- not been updated. If an attribute has been updated an error is generated.
293 --
294 -- Pre Conditions:
295 -- g_old_rec has been populated with details of the values currently in
296 -- the database.
297 --
298 -- In Arguments:
299 -- p_rec has been populated with the updated values the user would like the
300 -- record set to.
301 --
302 -- Post Success:
303 -- Processing continues if all the non updateable attributes have not
304 -- changed.
305 --
306 -- Post Failure:
307 -- An application error is raised if any of the non updatable attributes
308 -- have been altered.
309 --
310 -- {End Of Comments}
311 -- ----------------------------------------------------------------------------
312 Procedure chk_non_updateable_args
313 (p_rec in pqh_tjr_shd.g_rec_type
314 ) IS
315 --
316 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
317 l_error EXCEPTION;
318 l_argument varchar2(30);
319 --
320 Begin
321 --
322 -- Only proceed with the validation if a row exists for the current
323 -- record in the HR Schema.
324 --
325 IF NOT pqh_tjr_shd.api_updating
326 (p_txn_job_requirement_id => p_rec.txn_job_requirement_id
327 ,p_object_version_number => p_rec.object_version_number
328 ) THEN
329 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
330 fnd_message.set_token('PROCEDURE ', l_proc);
331 fnd_message.set_token('STEP ', '5');
332 fnd_message.raise_error;
333 END IF;
334 --
335 -- EDIT_HERE: Add checks to ensure non-updateable args have
336 -- not been updated.
337 --
338 EXCEPTION
339 WHEN l_error THEN
340 hr_api.argument_changed_error
341 (p_api_name => l_proc
342 ,p_argument => l_argument);
343 WHEN OTHERS THEN
344 RAISE;
345 End chk_non_updateable_args;
346 --
347 -- ----------------------------------------------------------------------------
348 -- |---------------------------< insert_validate >----------------------------|
349 -- ----------------------------------------------------------------------------
350 Procedure insert_validate
351 (p_rec in pqh_tjr_shd.g_rec_type
352 ) is
353 --
354 l_proc varchar2(72) := g_package||'insert_validate';
355 --
356 Begin
357 hr_utility.set_location('Entering:'||l_proc, 5);
358 --
359 -- Call all supporting business operations
360 --
361 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
362 --
363 --
364 pqh_tjr_bus.chk_df(p_rec);
365 --
366 hr_utility.set_location(' Leaving:'||l_proc, 10);
367 End insert_validate;
368 --
369 -- ----------------------------------------------------------------------------
370 -- |---------------------------< update_validate >----------------------------|
371 -- ----------------------------------------------------------------------------
372 Procedure update_validate
373 (p_rec in pqh_tjr_shd.g_rec_type
374 ) is
375 --
376 l_proc varchar2(72) := g_package||'update_validate';
377 --
378 Begin
379 hr_utility.set_location('Entering:'||l_proc, 5);
380 --
381 -- Call all supporting business operations
382 --
383 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
384 --
385 chk_non_updateable_args
386 (p_rec => p_rec
387 );
388 --
389 --
390 pqh_tjr_bus.chk_df(p_rec);
391 --
392 hr_utility.set_location(' Leaving:'||l_proc, 10);
393 End update_validate;
394 --
395 -- ----------------------------------------------------------------------------
396 -- |---------------------------< delete_validate >----------------------------|
397 -- ----------------------------------------------------------------------------
398 Procedure delete_validate
399 (p_rec in pqh_tjr_shd.g_rec_type
400 ) is
401 --
402 l_proc varchar2(72) := g_package||'delete_validate';
403 --
404 Begin
405 hr_utility.set_location('Entering:'||l_proc, 5);
406 --
407 -- Call all supporting business operations
408 --
409 hr_utility.set_location(' Leaving:'||l_proc, 10);
410 End delete_validate;
411 --
412 end pqh_tjr_bus;