[Home] [Help]
PACKAGE BODY: APPS.OTA_TSP_BUS
Source
1 Package Body ota_tsp_bus as
2 /* $Header: ottsp01t.pkb 120.0 2005/05/29 07:54:07 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ota_tsp_bus.'; -- Global package name
9 g_legislation_code varchar2(150) default null;
10 --
11 --
12 -- ----------------------------------------------------------------------------
13 -- |-----------------------------< chk_ddf >----------------------------------|
14 -- ----------------------------------------------------------------------------
15 --
16 -- Description:
17 -- Validates all the Developer Descriptive Flexfield values.
18 --
19 -- Prerequisites:
20 -- All other columns have been validated. Must be called as the
21 -- last step from insert_validate and update_validate.
22 --
23 -- In Arguments:
24 -- p_rec
25 --
26 -- Post Success:
27 -- If the Developer Descriptive Flexfield structure column and data values
28 -- are all valid this procedure will end normally and processing will
29 -- continue.
30 --
31 -- Post Failure:
32 -- If the Developer Descriptive Flexfield structure column value or any of
33 -- the data values are invalid then an application error is raised as
34 -- a PL/SQL exception.
35 --
36 -- Access Status:
37 -- Internal Row Handler Use Only.
38 --
39 -- ----------------------------------------------------------------------------
40 procedure chk_ddf
41 (p_rec in ota_tsp_shd.g_rec_type
42 ) is
43 --
44 l_proc varchar2(72) := g_package || 'chk_ddf';
45 --
46 begin
47 hr_utility.set_location('Entering:'||l_proc,10);
48 --
49 if ((p_rec.skill_provision_id is not null) and (
50 nvl(ota_tsp_shd.g_old_rec.tsp_information_category, hr_api.g_varchar2) <>
51 nvl(p_rec.tsp_information_category, hr_api.g_varchar2) or
52 nvl(ota_tsp_shd.g_old_rec.tsp_information1, hr_api.g_varchar2) <>
53 nvl(p_rec.tsp_information1, hr_api.g_varchar2) or
54 nvl(ota_tsp_shd.g_old_rec.tsp_information2, hr_api.g_varchar2) <>
55 nvl(p_rec.tsp_information2, hr_api.g_varchar2) or
56 nvl(ota_tsp_shd.g_old_rec.tsp_information3, hr_api.g_varchar2) <>
57 nvl(p_rec.tsp_information3, hr_api.g_varchar2) or
58 nvl(ota_tsp_shd.g_old_rec.tsp_information4, hr_api.g_varchar2) <>
59 nvl(p_rec.tsp_information4, hr_api.g_varchar2) or
60 nvl(ota_tsp_shd.g_old_rec.tsp_information5, hr_api.g_varchar2) <>
61 nvl(p_rec.tsp_information5, hr_api.g_varchar2) or
62 nvl(ota_tsp_shd.g_old_rec.tsp_information6, hr_api.g_varchar2) <>
63 nvl(p_rec.tsp_information6, hr_api.g_varchar2) or
64 nvl(ota_tsp_shd.g_old_rec.tsp_information7, hr_api.g_varchar2) <>
65 nvl(p_rec.tsp_information7, hr_api.g_varchar2) or
66 nvl(ota_tsp_shd.g_old_rec.tsp_information8, hr_api.g_varchar2) <>
67 nvl(p_rec.tsp_information8, hr_api.g_varchar2) or
68 nvl(ota_tsp_shd.g_old_rec.tsp_information9, hr_api.g_varchar2) <>
69 nvl(p_rec.tsp_information9, hr_api.g_varchar2) or
70 nvl(ota_tsp_shd.g_old_rec.tsp_information10, hr_api.g_varchar2) <>
71 nvl(p_rec.tsp_information10, hr_api.g_varchar2) or
72 nvl(ota_tsp_shd.g_old_rec.tsp_information11, hr_api.g_varchar2) <>
73 nvl(p_rec.tsp_information11, hr_api.g_varchar2) or
74 nvl(ota_tsp_shd.g_old_rec.tsp_information12, hr_api.g_varchar2) <>
75 nvl(p_rec.tsp_information12, hr_api.g_varchar2) or
76 nvl(ota_tsp_shd.g_old_rec.tsp_information13, hr_api.g_varchar2) <>
77 nvl(p_rec.tsp_information13, hr_api.g_varchar2) or
78 nvl(ota_tsp_shd.g_old_rec.tsp_information14, hr_api.g_varchar2) <>
79 nvl(p_rec.tsp_information14, hr_api.g_varchar2) or
80 nvl(ota_tsp_shd.g_old_rec.tsp_information15, hr_api.g_varchar2) <>
81 nvl(p_rec.tsp_information15, hr_api.g_varchar2) or
82 nvl(ota_tsp_shd.g_old_rec.tsp_information16, hr_api.g_varchar2) <>
83 nvl(p_rec.tsp_information16, hr_api.g_varchar2) or
84 nvl(ota_tsp_shd.g_old_rec.tsp_information17, hr_api.g_varchar2) <>
85 nvl(p_rec.tsp_information17, hr_api.g_varchar2) or
86 nvl(ota_tsp_shd.g_old_rec.tsp_information18, hr_api.g_varchar2) <>
87 nvl(p_rec.tsp_information18, hr_api.g_varchar2) or
88 nvl(ota_tsp_shd.g_old_rec.tsp_information19, hr_api.g_varchar2) <>
89 nvl(p_rec.tsp_information19, hr_api.g_varchar2) or
90 nvl(ota_tsp_shd.g_old_rec.tsp_information20, hr_api.g_varchar2) <>
91 nvl(p_rec.tsp_information20, hr_api.g_varchar2) ))
92 or (p_rec.skill_provision_id is null) then
93 --
94 -- Only execute the validation if absolutely necessary:
95 -- a) During update, the structure column value or any
96 -- of the attribute values have actually changed.
97 -- b) During insert.
98 --
99 hr_dflex_utility.ins_or_upd_descflex_attribs
100 (p_appl_short_name => 'OTA'
101 ,p_descflex_name => 'OTA_SKILL_PROVISIONS'
102 ,p_attribute_category => p_rec.tsp_information_category
103 ,p_attribute1_name => 'TSP_INFORMATION1'
104 ,p_attribute1_value => p_rec.tsp_information1
105 ,p_attribute2_name => 'TSP_INFORMATION2'
106 ,p_attribute2_value => p_rec.tsp_information2
107 ,p_attribute3_name => 'TSP_INFORMATION3'
108 ,p_attribute3_value => p_rec.tsp_information3
109 ,p_attribute4_name => 'TSP_INFORMATION4'
110 ,p_attribute4_value => p_rec.tsp_information4
111 ,p_attribute5_name => 'TSP_INFORMATION5'
112 ,p_attribute5_value => p_rec.tsp_information5
113 ,p_attribute6_name => 'TSP_INFORMATION6'
114 ,p_attribute6_value => p_rec.tsp_information6
115 ,p_attribute7_name => 'TSP_INFORMATION7'
116 ,p_attribute7_value => p_rec.tsp_information7
117 ,p_attribute8_name => 'TSP_INFORMATION8'
118 ,p_attribute8_value => p_rec.tsp_information8
119 ,p_attribute9_name => 'TSP_INFORMATION9'
120 ,p_attribute9_value => p_rec.tsp_information9
121 ,p_attribute10_name => 'TSP_INFORMATION10'
122 ,p_attribute10_value => p_rec.tsp_information10
123 ,p_attribute11_name => 'TSP_INFORMATION11'
124 ,p_attribute11_value => p_rec.tsp_information11
125 ,p_attribute12_name => 'TSP_INFORMATION12'
126 ,p_attribute12_value => p_rec.tsp_information12
127 ,p_attribute13_name => 'TSP_INFORMATION13'
128 ,p_attribute13_value => p_rec.tsp_information13
129 ,p_attribute14_name => 'TSP_INFORMATION14'
130 ,p_attribute14_value => p_rec.tsp_information14
131 ,p_attribute15_name => 'TSP_INFORMATION15'
132 ,p_attribute15_value => p_rec.tsp_information15
133 ,p_attribute16_name => 'TSP_INFORMATION16'
134 ,p_attribute16_value => p_rec.tsp_information16
135 ,p_attribute17_name => 'TSP_INFORMATION17'
136 ,p_attribute17_value => p_rec.tsp_information17
137 ,p_attribute18_name => 'TSP_INFORMATION18'
138 ,p_attribute18_value => p_rec.tsp_information18
139 ,p_attribute19_name => 'TSP_INFORMATION19'
140 ,p_attribute19_value => p_rec.tsp_information19
141 ,p_attribute20_name => 'TSP_INFORMATION20'
142 ,p_attribute20_value => p_rec.tsp_information20
143 );
144 end if;
145 --
146 hr_utility.set_location(' Leaving:'||l_proc,20);
147 end chk_ddf;
148
149 -- ----------------------------------------------------------------------------
150 -- |----------------------< check_analysis_criteria_id >----------------------|
151 -- ----------------------------------------------------------------------------
152 Procedure check_analysis_criteria_id (p_analysis_criteria_id number) is
153 --
154 l_proc varchar2(30) := 'check_analysis_criteria_id';
155 --
156 Begin
157 --
158 hr_utility.set_location('Entering '||l_proc, 10);
159 --
160 if p_analysis_criteria_id is null then
161 --
162 fnd_message.set_name('OTA', 'OTA_13637_TSP_INFO_FIELD_NULL');
163 fnd_message.raise_error;
164 --
165 end if;
166 --
167 hr_utility.set_location('Leaving '||l_proc, 10);
168 --
169 end check_analysis_criteria_id;
170 --
171 -- ----------------------------------------------------------------------------
172 -- |---------------------------< insert_validate >----------------------------|
173 -- ----------------------------------------------------------------------------
174 Procedure insert_validate(p_rec in ota_tsp_shd.g_rec_type) is
175 --
176 l_proc varchar2(72) := g_package||'insert_validate';
177 --
178 Begin
179 hr_utility.set_location('Entering:'||l_proc, 5);
180 --
181 -- Call all supporting business operations
182 --
186 ota_tsp_bus.chk_ddf(p_rec);
183 check_analysis_criteria_id (p_rec.analysis_criteria_id);
184 --
185 --
187 hr_utility.set_location(' Leaving:'||l_proc, 10);
188 End insert_validate;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< update_validate >----------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure update_validate(p_rec in ota_tsp_shd.g_rec_type) is
194 --
195 l_proc varchar2(72) := g_package||'update_validate';
196 --
197 Begin
198 hr_utility.set_location('Entering:'||l_proc, 5);
199 --
200 -- Call all supporting business operations
201 --
202 --
203 check_analysis_criteria_id (p_rec.analysis_criteria_id);
204 --
205 --
206 ota_tsp_bus.chk_ddf(p_rec);
207 hr_utility.set_location(' Leaving:'||l_proc, 10);
208 End update_validate;
209 --
210 -- ----------------------------------------------------------------------------
211 -- |---------------------------< delete_validate >----------------------------|
212 -- ----------------------------------------------------------------------------
213 Procedure delete_validate(p_rec in ota_tsp_shd.g_rec_type) is
214 --
215 l_proc varchar2(72) := g_package||'delete_validate';
216 --
217 Begin
218 hr_utility.set_location('Entering:'||l_proc, 5);
219 --
220 -- Call all supporting business operations
221 --
222 hr_utility.set_location(' Leaving:'||l_proc, 10);
223 End delete_validate;
224 --
225
226 --
227 -- ---------------------------------------------------------------------------
228 -- |----------------------< set_security_group_id >--------------------------|
229 -- ---------------------------------------------------------------------------
230 -- {Start Of Comments}
231 --
232 -- Description:
233 -- Sets the security_group_id in CLIENT_INFO for the appropriate business
234 -- group context.
235 --
236 -- Prerequisites:
237 -- The primary key identified by p_SKILL_PROVISION_ID
238 -- already exists.
239 --
240 -- In Arguments:
241 -- p_skill_provision_id
242 --
243 --
244 -- Post Success:
245 -- The security_group_id will be set in CLIENT_INFO.
246 --
247 -- Post Failure:
248 -- An error is raised if the value does not exist.
249 --
250 -- Access Status:
251 -- Internal Development Use Only.
252 --
253 -- {End Of Comments}
254 -- ---------------------------------------------------------------------------
255 Procedure set_security_group_id
256 (p_skill_provision_id in number
257 ,p_associated_column1 in varchar2 default null
258 ) is
259 --
260 -- Declare cursor
261 --
262 cursor csr_sec_grp is
263 select pbg.security_group_id,
264 pbg.legislation_code
265 from per_business_groups_perf pbg
266 , ota_skill_provisions tsp
267 ,ota_activity_versions tav
268 where tav.activity_version_id = tsp.activity_version_id
269 and pbg.business_group_id = tav.business_group_id
270 and tsp.skill_provision_id = p_skill_provision_id;
271 --
272 -- Declare local variables
273 --
274 l_security_group_id number;
275 l_proc varchar2(72) := g_package||'set_security_group_id';
276 l_legislation_code varchar2(150);
277 --
278 begin
279 --
280 hr_utility.set_location('Entering:'|| l_proc, 10);
281 --
282 -- Ensure that all the mandatory parameter are not null
283 --
284 hr_api.mandatory_arg_error
285 (p_api_name => l_proc
286 ,p_argument => 'skill_provision_id'
287 ,p_argument_value => p_skill_provision_id
288 );
289 --
290 open csr_sec_grp;
291 fetch csr_sec_grp into l_security_group_id
292 , l_legislation_code;
293 --
294 if csr_sec_grp%notfound then
295 --
296 close csr_sec_grp;
297 --
301 hr_multi_message.add
298 -- The primary key is invalid therefore we must error
299 --
300 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
302 (p_associated_column1
303 => nvl(p_associated_column1,'SKILL_PROVISION_ID')
304 );
305 --
306 else
307 close csr_sec_grp;
308 --
309 -- Set the security_group_id in CLIENT_INFO
310 --
311 hr_api.set_security_group_id
312 (p_security_group_id => l_security_group_id
313 );
314 --
315 -- Set the sessions legislation context in HR_SESSION_DATA
316 --
317 hr_api.set_legislation_context(l_legislation_code);
318 end if;
319 --
320 hr_utility.set_location(' Leaving:'|| l_proc, 20);
321 --
322 end set_security_group_id;
323
324 --
325 -- ----------------------------------------------------------------------------
326 -- |-----------------------< return_legislation_code >-------------------------|
327 -- ----------------------------------------------------------------------------
328 --
329 -- {Start Of Comments}
330 --
331 -- Description:
332 -- This function will be used by the user hooks. This will be used
333 -- of by the user hooks of ota_skill_provisions usiness process.
334 --
335 -- Pre Conditions:
336 -- This function will be called by the user hook packages.
337 --
338 -- In Arguments:
339 -- p_skill_provison_id
340 --
341 -- Post Success:
342 -- Processing continues.
343 --
344 -- Post Failure:
345 -- Errors out
346 --
347 -- Developer Implementation Notes:
348 --
349 -- Access Status:
350 -- Internal Development Use Only.
351 --
352 -- {End Of Comments}
353 --------------------------------------------------------------------------------
354 --
355
356 Function return_legislation_code
357 ( p_skill_provision_id in number
358 ) return varchar2 is
359 --
360 -- Declare cursor
361 --
362 cursor csr_leg_code is
363 select pbg.legislation_code
364 from per_business_groups_perf pbg
365 , ota_skill_provisions tsp
366 ,ota_activity_versions tav
367 where tav.activity_version_id = tsp.activity_version_id
368 and pbg.business_group_id = tav.business_group_id
369 and tsp.skill_provision_id = p_skill_provision_id;
370
371
372 l_proc varchar2(72) := g_package||'return_legislation_code';
373 l_legislation_code varchar2(150);
374 --
375 Begin
376 hr_utility.set_location('Entering:'||l_proc, 5);
377 --
378 -- Ensure that all the mandatory parameters are not null
379 --
380 hr_api.mandatory_arg_error (p_api_name => l_proc,
381 p_argument => 'skill_provision_id',
382 p_argument_value => p_skill_provision_id);
383 open csr_leg_code;
384 fetch csr_leg_code into l_legislation_code;
385 if csr_leg_code%notfound then
386 close csr_leg_code;
387 --
388 -- The primary key is invalid therefore we must error out
389 --
390 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
391 hr_utility.raise_error;
392 end if;
393 --
394 close csr_leg_code;
395 return l_legislation_code;
396 --
397 hr_utility.set_location(' Leaving:'||l_proc, 10);
398 --
399 End return_legislation_code;
400 end ota_tsp_bus;