1 Package Body hr_tpc_bus as
2 /* $Header: hrtpcrhi.pkb 115.0 2004/01/09 04:37 vkarandi noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hr_tpc_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_topic_id number default null;
15 -- ----------------------------------------------------------------------------
16 -- |-----------------------< chk_non_updateable_args >------------------------|
17 -- ----------------------------------------------------------------------------
18 -- {Start Of Comments}
19 --
20 -- Description:
21 -- This procedure is used to ensure that non updateable attributes have
22 -- not been updated. If an attribute has been updated an error is generated.
23 --
24 -- Pre Conditions:
25 -- g_old_rec has been populated with details of the values currently in
26 -- the database.
27 --
28 -- In Arguments:
29 -- p_rec has been populated with the updated values the user would like the
30 -- record set to.
31 --
32 -- Post Success:
33 -- Processing continues if all the non updateable attributes have not
34 -- changed.
35 --
36 -- Post Failure:
37 -- An application error is raised if any of the non updatable attributes
38 -- have been altered.
39 --
40 -- {End Of Comments}
41 -- ----------------------------------------------------------------------------
42 Procedure chk_non_updateable_args
43 (p_rec in hr_tpc_shd.g_rec_type
44 ) IS
45 --
46 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
47 --
48 Begin
49 --
50 -- Only proceed with the validation if a row exists for the current
51 -- record in the HR Schema.
52 --
53 IF NOT hr_tpc_shd.api_updating
54 (p_topic_id => p_rec.topic_id
55 ,p_object_version_number => p_rec.object_version_number
56 ) THEN
57 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
58 fnd_message.set_token('PROCEDURE ', l_proc);
59 fnd_message.set_token('STEP ', '5');
60 fnd_message.raise_error;
61 END IF;
62 --
63 -- EDIT_HERE: Add checks to ensure non-updateable args have
64 -- not been updated.
65 if nvl(p_rec.topic_key, hr_api.g_varchar2) <>
66 nvl(hr_tpc_shd.g_old_rec.topic_key,hr_api.g_varchar2
67 ) then
68 hr_api.argument_changed_error
69 (p_api_name => l_proc
70 ,p_argument => 'TOPIC_KEY'
71 ,p_base_table => hr_tpc_shd.g_tab_nam
72 );
73 end if;
74 --
75 End chk_non_updateable_args;
76 -- ----------------------------------------------------------------------------
77 -- ------------------------------< CHK_TOPIC_KEY>------------------------------
78 -- ----------------------------------------------------------------------------
79 -- {Start Of Comments}
80 --
81 -- Description:
82 -- This procedure ensures a valid topic key is entered
83 -- Pre Conditions:
84 -- g_rec has been populated with details of the values
85 -- from the ins or the upd procedures
86 --
87 -- In Arguments:
88 -- p_topic_key
89
90 -- Post Success:
91 -- Processing continues if topic key is not null and unique
92 --
93 -- Post Failure:
94 -- An application error is raised if topic key is null or exists already
95 --
96 -- {End Of Comments}
97 -- ----------------------------------------------------------------------------
98 procedure chk_topic_key
99 (p_topic_key in varchar2
100 )
101 is
102 --
103 -- Declare cursors and local variables
104 --
105 -- Cursor to check if the hierarchy key provided in the insert is already
106 -- present
107 CURSOR csr_tpc_key is
108 select
109 distinct 'found'
110 From
111 hr_ki_topics tpc
112 where
113 tpc.topic_key = p_topic_key;
114
115 -- Variables for API Boolean parameters
116 l_proc varchar2(72) := g_package ||'chk_topic_key';
117 l_found_flag varchar2(10);
118
119 Begin
120 hr_utility.set_location(' Entering:' || l_proc,10);
121 --
122 hr_api.mandatory_arg_error
123 (p_api_name => l_proc
124 ,p_argument => 'TOPIC_KEY'
125 ,p_argument_value => p_topic_key
126 );
127
128 OPEN csr_tpc_key;
129 FETCH csr_tpc_key into l_found_flag;
130
131 IF csr_tpc_key%FOUND then
132 hr_utility.set_location(' Topic Key already present:' || l_proc,20);
133 CLOSE csr_tpc_key;
134 fnd_message.set_name( 'PER','PER_449930_TPC_KEY_DUPLICATE');
135 fnd_message.raise_error;
136 END IF;
137
138 CLOSE csr_tpc_key;
139
140
141 --
142 hr_utility.set_location(' Leaving:' || l_proc,30);
143 Exception
144 when app_exception.application_exception then
145 IF hr_multi_message.exception_add
146 (p_associated_column1 => 'HR_KI_TOPICS.TOPIC_KEY'
147 ) THEN
148 hr_utility.set_location(' Leaving:'|| l_proc,40);
149 raise;
150 END IF;
151
152 hr_utility.set_location(' Leaving:'|| l_proc,50);
153 --
154 End chk_topic_key ;
155
156 -- ----------------------------------------------------------------------------
157 -- -------------------------------< CHK_HANDLER>-------------------------------
158 -- ----------------------------------------------------------------------------
159 -- {Start Of Comments}
160 --
161 -- Description:
162 -- This procedure ensures a valid handler is entered
163 -- Pre Conditions:
164 -- g_rec has been populated with details of the values
165 -- from the ins or the upd procedures
166 --
167 -- In Arguments:
168 -- handler
169
170 -- Post Success:
171 -- Processing continues if handler is not null.
172 --
173 -- Post Failure:
174 -- An application error is raised if handler is null.
175 --
176 -- {End Of Comments}
177 -- ----------------------------------------------------------------------------
178 procedure chk_handler(p_handler in varchar2)
179 is
180 l_proc varchar2(72) := g_package ||'chk_handler';
181 Begin
182 hr_utility.set_location(' Entering:' || l_proc,10);
183
184 hr_api.mandatory_arg_error
185 (p_api_name => l_proc
186 ,p_argument => 'HANDLER'
187 ,p_argument_value => p_handler
188 );
189
190 hr_utility.set_location(' Leaving:' || l_proc,20);
191
192 Exception
193 when app_exception.application_exception then
194 IF hr_multi_message.exception_add
195 (p_associated_column1 => 'HR_KI_TOPICS.HANDLER'
196 ) THEN
197 hr_utility.set_location(' Leaving:'|| l_proc,30);
198 raise;
199 END IF;
200
201 hr_utility.set_location(' Leaving:'|| l_proc,40);
202 --
203 End chk_handler;
204
205
206 -- ----------------------------------------------------------------------------
207 -- -------------------------------< CHK_DELETE>--------------------------------
208 -- ----------------------------------------------------------------------------
209 -- {Start Of Comments}
210 --
211 -- Description:
212 -- This procedure ensures that a delete occurs only if there are no child
213 -- rows for a record in hr_ki_topics. The tables that contain child rows are
214 -- hr_ki_hierarchy_node_maps,hr_ki_topic_integrations,hr_ki_topics_tl.
215
216 -- Pre Conditions:
217 -- g_rec has been populated with details of the values
218 -- from the ins or the upd procedures
219 --
220 -- In Arguments:
221 -- p_topic_id
222
223 -- Post Success:
224 -- Processing continues if there are no child records.
225 --
226 -- Post Failure:
227 -- An application error is raised if there are any child rows from any of the
228 -- above mentioned tables.
229 --
230 -- {End Of Comments}
231 -- ----------------------------------------------------------------------------
232
233 procedure chk_delete(p_topic_id in varchar2)
234 is
235
236 CURSOR csr_hnm_id is
237 select
238 distinct 'found'
239 From
240 hr_ki_hierarchy_node_maps hnm
241 where
242 hnm.topic_id = p_topic_id;
243
244 CURSOR csr_tis_id is
245 select
246 distinct 'found'
247 From
248 hr_ki_topic_integrations tis
249 where
250 tis.topic_id = p_topic_id;
251
252 CURSOR csr_ttl_id is
253 select
254 distinct 'found'
255 From
256 hr_ki_topics_tl ttl
257 where
258 ttl.topic_id = p_topic_id;
259
260 l_found varchar2(30);
261 l_proc varchar2(72) := g_package ||'chk_delete';
262
263 Begin
264
265 hr_utility.set_location(' Entering:' || l_proc,10);
266
267 open csr_hnm_id;
268 fetch csr_hnm_id into l_found;
269
270 if csr_hnm_id%FOUND then
271 close csr_hnm_id;
272 fnd_message.set_name( 'PER','PER_449931_TPC_NMAP_MAIN_EXIST');
273 fnd_message.raise_error;
274 end if;
275
276 close csr_hnm_id;
277
278 open csr_tis_id;
279 fetch csr_tis_id into l_found;
280
281 if csr_tis_id%FOUND then
282 close csr_tis_id;
283 fnd_message.set_name( 'PER','PER_449932_TPC_TPIN_MAIN_EXIST');
284 fnd_message.raise_error;
285 end if;
286
287 close csr_tis_id;
288
289 open csr_ttl_id;
290 fetch csr_ttl_id into l_found;
291
292 if csr_ttl_id%FOUND then
293 close csr_ttl_id;
294 fnd_message.set_name( 'PER','PER_449933_TPC_TPTL_MAIN_EXIST');
295 fnd_message.raise_error;
296 end if;
297
298 close csr_ttl_id;
299
300 hr_utility.set_location(' Leaving:'|| l_proc,20);
301
302 Exception
303 when app_exception.application_exception then
304 IF hr_multi_message.exception_add
305 (p_associated_column1 => 'HR_KI_TOPICS.TOPIC_ID'
306 )THEN
307 hr_utility.set_location(' Leaving:'|| l_proc,30);
308 raise;
309 END IF;
310 hr_utility.set_location(' Leaving:'|| l_proc,40);
311
312 End chk_delete;
313
314 --
315 -- ----------------------------------------------------------------------------
316 -- |---------------------------< insert_validate >----------------------------|
317 -- ----------------------------------------------------------------------------
318 Procedure insert_validate
319 (p_rec in hr_tpc_shd.g_rec_type
320 ) is
321 --
322 l_proc varchar2(72) := g_package||'insert_validate';
323 --
324 Begin
325 hr_utility.set_location('Entering:'||l_proc, 5);
326 --
327 -- Call all supporting business operations
328 --
329 --
330 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
331 --
332 -- Validate Dependent Attributes
333 chk_topic_key(p_rec.topic_key);
334 chk_handler(p_rec.handler);
335 --
336 --
337 hr_utility.set_location(' Leaving:'||l_proc, 10);
338 End insert_validate;
339 --
340 -- ----------------------------------------------------------------------------
341 -- |---------------------------< update_validate >----------------------------|
342 -- ----------------------------------------------------------------------------
343 Procedure update_validate
344 (p_rec in hr_tpc_shd.g_rec_type
345 ) is
346 --
347 l_proc varchar2(72) := g_package||'update_validate';
348 --
349 Begin
350 hr_utility.set_location('Entering:'||l_proc, 5);
351 --
352 -- Call all supporting business operations
353 --
354 --
355 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
356 -- Validate Dependent Attributes
357 --
358 chk_non_updateable_args
359 (p_rec => p_rec
360 );
361
362 chk_handler(p_rec.handler);
363 --
364 --
365 hr_utility.set_location(' Leaving:'||l_proc, 10);
366 End update_validate;
367 --
368 -- ----------------------------------------------------------------------------
369 -- |---------------------------< delete_validate >----------------------------|
370 -- ----------------------------------------------------------------------------
371 Procedure delete_validate
372 (p_rec in hr_tpc_shd.g_rec_type
373 ) is
374 --
375 l_proc varchar2(72) := g_package||'delete_validate';
376 --
377 Begin
378 hr_utility.set_location('Entering:'||l_proc, 5);
379 --
380 -- Call all supporting business operations
381 chk_delete(p_rec.topic_id);
382 --
383 hr_utility.set_location(' Leaving:'||l_proc, 10);
384 End delete_validate;
385 --
386 end hr_tpc_bus;