1 Package Body pay_evp_bus as
2 /* $Header: pyevprhi.pkb 115.5 2002/12/10 18:44:32 dsaxby noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_evp_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_event_procedure_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_event_procedure_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 , pay_event_procedures evp
30 where evp.event_procedure_id = p_event_procedure_id
31 and pbg.business_group_id = evp.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 => 'event_procedure_id'
47 ,p_argument_value => p_event_procedure_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_event_procedure_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 , pay_event_procedures evp
90 where evp.event_procedure_id = p_event_procedure_id
91 and pbg.business_group_id (+) = evp.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 => 'event_procedure_id'
107 ,p_argument_value => p_event_procedure_id
108 );
109 --
110 if ( nvl(pay_evp_bus.g_event_procedure_id, hr_api.g_number)
111 = p_event_procedure_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 := pay_evp_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 pay_evp_bus.g_event_procedure_id:= p_event_procedure_id;
142 pay_evp_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_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_rec in pay_evp_shd.g_rec_type
177 ) IS
178 --
179 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
180 l_error EXCEPTION;
181 l_argument varchar2(30);
182 --
183 Begin
184 --
185 -- Only proceed with the validation if a row exists for the current
186 -- record in the HR Schema.
187 --
188 IF NOT pay_evp_shd.api_updating
189 (p_event_procedure_id => p_rec.event_procedure_id
190 ,p_object_version_number => p_rec.object_version_number
191 ) THEN
192 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
193 fnd_message.set_token('PROCEDURE ', l_proc);
194 fnd_message.set_token('STEP ', '5');
195 fnd_message.raise_error;
196 END IF;
197 --
198 if (nvl(p_rec.business_group_id, hr_api.g_number) <>
199 nvl(pay_evp_shd.g_old_rec.business_group_id,hr_api.g_number)
200 ) then
201 l_argument := 'business_group_id';
202 raise l_error;
203 END IF;
204 --
205 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
206 nvl(pay_evp_shd.g_old_rec.legislation_code, hr_api.g_varchar2)
207 then
208 l_argument := 'p_legislation_code';
209 raise l_error;
210 end if;
211 --
212 EXCEPTION
213 WHEN l_error THEN
214 hr_api.argument_changed_error
215 (p_api_name => l_proc
216 ,p_argument => l_argument);
217 WHEN OTHERS THEN
218 RAISE;
219 End chk_non_updateable_args;
220 --
221 -- ----------------------------------------------------------------------------
222 -- |---------------------------< chk_column >----------------------------|
223 -- ----------------------------------------------------------------------------
224 -- {Start of comments}
225 --
226 -- Description:
227 -- This procedure validates the column_name is a column
228 -- which exist on the table referred to
229 --
230 -- Prerequisites:
231 --
232 -- In Parameters:
233 -- A Pl/Sql record structure.
234 --
235 -- Post Success:
236 -- Processing continues.
237 --
238 -- Post Failure:
239 -- Error if column not recognised.
240 --
241 -- Access Status:
242 -- Internal Row Handler Use Only.
243 --
244 -- {End of comments}
245 --
246 Procedure chk_column
247 (p_rec in pay_evp_shd.g_rec_type
248 ) is
249 --
250 l_proc varchar2(72) := g_package||'chk_column';
251 l_error EXCEPTION;
252 l_argument varchar2(30);
253 l_dummy number(1);
254 --
255 cursor csr_chk_column is
256 select 1
257 from dual
258 where exists (
259 select 1
260 from pay_dated_tables dt,
261 fnd_tables tab,
262 fnd_columns col
263 where dt.dated_table_id = p_rec.dated_table_id
264 and dt.table_name = tab.table_name
265 and tab.table_id = col.table_id
266 and col.column_name = p_rec.column_name
267 );
268 --
269 Begin
270 --
271 hr_utility.set_location('Entering:'||l_proc, 5);
272 --
273 Open csr_chk_column;
274 Fetch csr_chk_column Into l_dummy;
275 If csr_chk_column%notfound Then
276 Close csr_chk_column;
277 --
278 -- The column does not belong to the table therefore we must error
279 --
280 fnd_message.set_name('PAY', 'HR_xxxx_INVALID_COLUMN_NAME');
281
282 fnd_message.raise_error;
283 End If;
284 Close csr_chk_column;
285 --
286 hr_utility.set_location(' Leaving:'||l_proc, 10);
287 --
288 end chk_column;
289 --
290 -- ----------------------------------------------------------------------------
291 -- |---------------------------< insert_validate >----------------------------|
292 -- ----------------------------------------------------------------------------
293 Procedure insert_validate
294 (p_rec in pay_evp_shd.g_rec_type
295 ) is
296 --
297 l_proc varchar2(72) := g_package||'insert_validate';
298 --
299 Begin
300 hr_utility.set_location('Entering:'||l_proc, 5);
301 --
302 -- Commenting this out as business group id is a nullable column
303 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
304 --
305 chk_column (p_rec => p_rec);
306 --
307 hr_utility.set_location(' Leaving:'||l_proc, 10);
308 --
309 End insert_validate;
310 --
311 -- ----------------------------------------------------------------------------
312 -- |---------------------------< update_validate >----------------------------|
313 -- ----------------------------------------------------------------------------
314 Procedure update_validate
315 (p_rec in pay_evp_shd.g_rec_type
316 ) is
317 --
318 l_proc varchar2(72) := g_package||'update_validate';
319 --
320 Begin
321 hr_utility.set_location('Entering:'||l_proc, 5);
322 --
323 -- Commenting this out as business group id is a nullable column
324 --hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
325 --
326 chk_non_updateable_args
327 (p_rec => p_rec
328 );
329 --
330 chk_column (p_rec => p_rec);
331 --
332 hr_utility.set_location(' Leaving:'||l_proc, 10);
333 --
334 End update_validate;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |---------------------------< delete_validate >----------------------------|
338 -- ----------------------------------------------------------------------------
339 Procedure delete_validate
340 (p_rec in pay_evp_shd.g_rec_type
341 ) is
342 --
343 l_proc varchar2(72) := g_package||'delete_validate';
344 --
345 Begin
346 hr_utility.set_location('Entering:'||l_proc, 5);
347 --
348 -- Call all supporting business operations
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End delete_validate;
352 --
353 end pay_evp_bus;