[Home] [Help]
PACKAGE BODY: APPS.PAY_PMA_BUS
Source
1 Package Body pay_pma_bus as
2 /* $Header: pypmarhi.pkb 115.2 2002/12/11 11:12:57 ssivasu2 noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pma_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_source_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- source_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_source_id(p_source_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_source_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pay_pma_shd.api_updating
47 (p_source_id => p_source_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_source_id,hr_api.g_number)
52 <> pay_pma_shd.g_old_rec.source_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pay_pma_shd.constraint_error('PAY_CA_PMED_ACCOUNTS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_source_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pay_pma_shd.constraint_error('PAY_CA_PMED_ACCOUNTS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_source_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_organization_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table. It also checks that the Organization is a
83 -- valid Provincial Medical Carrier.
84 --
85 -- Pre-Conditions
86 -- None.
87 --
88 -- In Parameters
89 -- p_source_id PK
90 -- p_organization_id ID of FK column
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_organization_id (p_source_id in number,
103 p_organization_id in number,
104 p_object_version_number in number) is
105 --
106 l_proc varchar2(72) := g_package||'chk_organization_id';
107 l_api_updating boolean;
108 l_dummy varchar2(1);
109 --
110 cursor c1 is
111 select null
112 from hr_all_organization_units a
113 where a.organization_id = p_organization_id;
114 --
115 cursor c2 (p_org_id NUMBER) is
116 select null
117 from hr_organization_information ogi
118 where ogi.organization_id = p_org_id
119 and ogi.org_information1 = 'CA_PMED'
120 and ogi.org_information_context = 'CLASS'
121 and ogi.org_information2 = 'Y';
122 --
123 Begin
124 --
125 hr_utility.set_location('Entering:'||l_proc,5);
126 --
127 l_api_updating := pay_pma_shd.api_updating
128 (p_source_id => p_source_id,
129 p_object_version_number => p_object_version_number);
130 --
131 if (l_api_updating
132 and nvl(p_organization_id,hr_api.g_number)
133 <> nvl(pay_pma_shd.g_old_rec.organization_id,hr_api.g_number)
134 or not l_api_updating) then
135 --
136 -- check if organization_id value exists in hr_all_organization_units table
137 --
138 open c1;
139 --
140 fetch c1 into l_dummy;
141 if c1%notfound then
142 --
143 close c1;
144 --
145 -- raise error as FK does not relate to PK in hr_all_organization_units
146 -- table.
147 --
148 pay_pma_shd.constraint_error('PAY_CA_PMED_ACCOUNTS_FK1');
149 --
150 end if;
151 --
152 close c1;
153 --
154 open c2(p_organization_id);
155 fetch c2 into l_dummy;
156 if c2%notfound then
157 close c2;
158 hr_utility.set_message(800,'PAY_74031_NOT_PMED_CARRIER');
159 hr_utility.raise_error;
160 end if;
161 close c2;
162 --
163 end if;
164 --
165 hr_utility.set_location('Leaving:'||l_proc,10);
166 --
167 End chk_organization_id;
168 --
169 -- ----------------------------------------------------------------------------
170 -- |-----------------------< chk_account_number >-----------------------------|
171 -- ----------------------------------------------------------------------------
172 --
173 -- Descriiption :
174 -- This check procedure ensures that the account number is unique within
175 -- organization.
176 --
177 -- Pre-conditions :
178 -- p_organization_id is valid
179 --
180 -- In Arguments :
181 -- p_source_id
182 -- p_object_version_number
183 -- p_organization_id
184 -- p_account_number
185 --
186 -- Post Success :
187 -- Processing continues
188 --
189 -- Post Failure :
190 -- An application error will be raised and processing is terminated
191 --
192 -- Access Status :
193 -- Internal Table Handler Use only.
194 --
195 -- {End of Comments}
196 --
197 -- ---------------------------------------------------------------------------
198 procedure chk_account_number
199 (p_source_id in pay_ca_pmed_accounts.source_id%TYPE
200 ,p_object_version_number in pay_ca_pmed_accounts.object_version_number%TYPE
201 ,p_organization_id in pay_ca_pmed_accounts.organization_id%TYPE
202 ,p_account_number in pay_ca_pmed_accounts.account_number%TYPE
203 ) is
204 --
205 l_proc varchar2(72) := g_package||'chk_account_number';
206 l_api_updating boolean;
207 l_dummy NUMBER;
208 --
209 CURSOR csr_get_ac_num (p_org_id NUMBER,
210 p_ac_num VARCHAR2) IS
211 SELECT 1
212 FROM pay_ca_pmed_accounts pma
213 WHERE pma.organization_id = p_org_id
214 AND pma.account_number = p_ac_num;
215 --
216 begin
217 hr_utility.set_location('Entering:'||l_proc, 10);
218
219 l_api_updating := pay_pma_shd.api_updating
220 (p_source_id => p_source_id,
221 p_object_version_number => p_object_version_number);
222 --
223 if NOT l_api_updating THEN
224 open csr_get_ac_num(p_organization_id,
225 p_account_number);
226 fetch csr_get_ac_num INTO l_dummy;
227 if csr_get_ac_num%FOUND THEN
228 close csr_get_ac_num;
229 hr_utility.set_message(800,'PAY_74032_AC_NO_NOT_UNIQUE');
230 hr_utility.raise_error;
231 end if;
232 close csr_get_ac_num;
233 end if;
234 --
235 hr_utility.set_location(' Leaving:'||l_proc, 20);
236 --
237 end chk_account_number;
238 -- ----------------------------------------------------------------------------
239 -- |---------------------------< chk_enabled >--------------------------------|
240 -- ----------------------------------------------------------------------------
241 --
242 -- Descriiption :
243 -- This check procedure ensures that the ENABLED flag has a value of
244 -- either 'Y' or 'N'.
245 --
246 -- Pre-conditions :
247 -- None
248 --
249 -- In Arguments :
250 -- p_enabled
251 --
252 -- Post Success :
253 -- Processing continues
254 --
255 -- Post Failure :
256 -- An application error will be raised and processing is terminated
257 --
258 -- Access Status :
259 -- Internal Table Handler Use only.
260 --
261 -- {End of Comments}
262 --
263 -- ---------------------------------------------------------------------------
264 procedure chk_enabled
265 (p_enabled in pay_ca_pmed_accounts.enabled%TYPE
266 ) is
267 --
268 l_proc varchar2(72) := g_package||'chk_enabled';
269 --
270 begin
271 hr_utility.set_location('Entering:'||l_proc, 10);
272
273 if (p_enabled <> 'Y' AND
274 p_enabled <> 'N') THEN
275 hr_utility.set_message(800,'HR_PAY_YES_NO');
276 hr_utility.raise_error;
277 end if;
278 --
279 hr_utility.set_location(' Leaving:'||l_proc, 3);
280 --
281 end chk_enabled;
282 --
283 -- ----------------------------------------------------------------------------
284 -- |---------------------------< insert_validate >----------------------------|
285 -- ----------------------------------------------------------------------------
286 Procedure insert_validate(p_rec in pay_pma_shd.g_rec_type) is
287 --
288 l_proc varchar2(72) := g_package||'insert_validate';
289 --
290 Begin
291 hr_utility.set_location('Entering:'||l_proc, 5);
292 --
293 -- Call all supporting business operations
294 --
295 chk_source_id
296 (p_source_id => p_rec.source_id,
297 p_object_version_number => p_rec.object_version_number);
298 --
299 chk_organization_id
300 (p_source_id => p_rec.source_id,
301 p_organization_id => p_rec.organization_id,
302 p_object_version_number => p_rec.object_version_number);
303 --
304 chk_account_number
305 (p_source_id => p_rec.source_id,
306 p_object_version_number => p_rec.object_version_number,
307 p_organization_id => p_rec.organization_id,
308 p_account_number => p_rec.account_number);
309 --
310 chk_enabled
311 (p_enabled => p_rec.enabled);
312 --
313 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
314 --
315 hr_utility.set_location(' Leaving:'||l_proc, 10);
316 End insert_validate;
317 --
318 -- ----------------------------------------------------------------------------
319 -- |---------------------------< update_validate >----------------------------|
320 -- ----------------------------------------------------------------------------
321 Procedure update_validate(p_rec in pay_pma_shd.g_rec_type) is
322 --
323 l_proc varchar2(72) := g_package||'update_validate';
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 -- Call all supporting business operations
329 --
330 chk_source_id
331 (p_source_id => p_rec.source_id,
332 p_object_version_number => p_rec.object_version_number);
333 --
334 chk_organization_id
335 (p_source_id => p_rec.source_id,
336 p_organization_id => p_rec.organization_id,
337 p_object_version_number => p_rec.object_version_number);
338 --
339 chk_account_number
340 (p_source_id => p_rec.source_id,
341 p_object_version_number => p_rec.object_version_number,
342 p_organization_id => p_rec.organization_id,
343 p_account_number => p_rec.account_number);
344 --
345 chk_enabled
346 (p_enabled => p_rec.enabled);
347 --
348 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
349 --
350 hr_utility.set_location(' Leaving:'||l_proc, 10);
351 End update_validate;
352 --
353 -- ----------------------------------------------------------------------------
354 -- |---------------------------< delete_validate >----------------------------|
355 -- ----------------------------------------------------------------------------
356 Procedure delete_validate(p_rec in pay_pma_shd.g_rec_type) is
357 --
358 l_proc varchar2(72) := g_package||'delete_validate';
359 --
360 Begin
361 hr_utility.set_location('Entering:'||l_proc, 5);
362 --
363 -- Call all supporting business operations
364 --
365 hr_utility.set_location(' Leaving:'||l_proc, 10);
366 End delete_validate;
367 --
368 --
369 -- ---------------------------------------------------------------------------
370 -- |---------------------< return_legislation_code >-------------------------|
371 -- ---------------------------------------------------------------------------
372 --
373 function return_legislation_code
374 (p_source_id in number) return varchar2 is
375 --
376 -- Declare cursor
377 --
378 cursor csr_leg_code is
379 select a.legislation_code
380 from per_business_groups a,
381 pay_ca_pmed_accounts b
382 where b.source_id = p_source_id
383 and a.business_group_id = b.business_group_id;
384 --
385 -- Declare local variables
386 --
387 l_legislation_code varchar2(150);
388 l_proc varchar2(72) := g_package||'return_legislation_code';
389 --
390 begin
391 --
392 hr_utility.set_location('Entering:'|| l_proc, 10);
393 --
394 -- Ensure that all the mandatory parameter are not null
395 --
396 hr_api.mandatory_arg_error(p_api_name => l_proc,
397 p_argument => 'source_id',
398 p_argument_value => p_source_id);
399 --
400 open csr_leg_code;
401 --
402 fetch csr_leg_code into l_legislation_code;
403 --
404 if csr_leg_code%notfound then
405 --
406 close csr_leg_code;
407 --
408 -- The primary key is invalid therefore we must error
409 --
410 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
411 hr_utility.raise_error;
412 --
413 end if;
414 --
415 close csr_leg_code;
416 --
417 hr_utility.set_location(' Leaving:'|| l_proc, 20);
418 --
419 return l_legislation_code;
420 --
421 end return_legislation_code;
422 --
423 end pay_pma_bus;