[Home] [Help]
PACKAGE BODY: APPS.PQH_BVR_BUS
Source
1 Package Body pqh_bvr_bus as
2 /* $Header: pqbvrrhi.pkb 115.10 2002/12/05 19:30:27 rpasapul ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bvr_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_budget_version_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 -- budget_version_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_budget_version_id(p_budget_version_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_version_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := pqh_bvr_shd.api_updating
47 (p_budget_version_id => p_budget_version_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_version_id,hr_api.g_number)
52 <> pqh_bvr_shd.g_old_rec.budget_version_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bvr_shd.constraint_error('PQH_BUDGET_VERSIONS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_version_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bvr_shd.constraint_error('PQH_BUDGET_VERSIONS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_version_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_budget_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_budget_version_id PK
89 -- p_budget_id ID of FK column
90 -- p_object_version_number object version number
91 --
92 -- Post Success
93 -- Processing continues
94 --
95 -- Post Failure
96 -- Error raised.
97 --
98 -- Access Status
99 -- Internal table handler use only.
100 --
101 Procedure chk_budget_id (p_budget_version_id in number,
102 p_budget_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_budget_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_budgets a
112 where a.budget_id = p_budget_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_bvr_shd.api_updating
119 (p_budget_version_id => p_budget_version_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_budget_id,hr_api.g_number)
124 <> nvl(pqh_bvr_shd.g_old_rec.budget_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_budget_id is not null then
127 --
128 -- check if budget_id value exists in pqh_budgets table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in pqh_budgets
138 -- table.
139 --
140 pqh_bvr_shd.constraint_error('PQH_BUDGET_VERSIONS_FK1');
141 --
142 end if;
143 --
144 close c1;
145 --
146 end if;
147 --
148 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 End chk_budget_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_xfer_to_other_apps_cd >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure is used to check that the lookup value is valid.
158 --
159 -- Pre Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- budget_version_id PK of record being inserted or updated.
164 -- xfer_to_other_apps_cd Value of lookup code.
165 -- effective_date effective date
166 -- object_version_number Object version number of record being
167 -- inserted or updated.
168 --
169 -- Post Success
170 -- Processing continues
171 --
172 -- Post Failure
173 -- Error handled by procedure
174 --
175 -- Access Status
176 -- Internal table handler use only.
177 --
178 Procedure chk_xfer_to_other_apps_cd(p_budget_version_id in number,
179 p_xfer_to_other_apps_cd in varchar2,
180 p_effective_date in date,
181 p_object_version_number in number) is
182 --
183 l_proc varchar2(72) := g_package||'chk_xfer_to_other_apps_cd';
184 l_api_updating boolean;
185 --
186 Begin
187 --
188 hr_utility.set_location('Entering:'||l_proc, 5);
189 --
190 l_api_updating := pqh_bvr_shd.api_updating
191 (p_budget_version_id => p_budget_version_id,
192 p_object_version_number => p_object_version_number);
193 --
194 if (l_api_updating
195 and p_xfer_to_other_apps_cd
196 <> nvl(pqh_bvr_shd.g_old_rec.xfer_to_other_apps_cd,hr_api.g_varchar2)
197 or not l_api_updating)
198 and p_xfer_to_other_apps_cd is not null then
199 --
200 -- check if value of lookup falls within lookup type.
201 --
202 if hr_api.not_exists_in_hr_lookups
203 (p_lookup_type => 'YES_NO',
204 p_lookup_code => p_xfer_to_other_apps_cd,
205 p_effective_date => p_effective_date) then
206 --
207 -- raise error as does not exist as lookup
208 --
209 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
210 hr_utility.raise_error;
211 --
212 end if;
213 --
214 end if;
215 --
216 hr_utility.set_location('Leaving:'||l_proc,10);
217 --
218 end chk_xfer_to_other_apps_cd;
219 --
220 -- ----------------------------------------------------------------------------
221 -- |------< chk_transfered_to_gl_flag >------|
222 -- ----------------------------------------------------------------------------
223 --
224 -- Description
225 -- This procedure is used to check that the lookup value is valid.
226 --
227 -- Pre Conditions
228 -- None.
229 --
230 -- In Parameters
231 -- budget_version_id PK of record being inserted or updated.
232 -- transfered_to_gl_flag Value of lookup code.
233 -- effective_date effective date
234 -- object_version_number Object version number of record being
235 -- inserted or updated.
236 --
237 -- Post Success
238 -- Processing continues
239 --
240 -- Post Failure
241 -- Error handled by procedure
242 --
243 -- Access Status
244 -- Internal table handler use only.
245 --
246 Procedure chk_transfered_to_gl_flag(p_budget_version_id in number,
247 p_transfered_to_gl_flag in varchar2,
248 p_effective_date in date,
249 p_object_version_number in number) is
250 --
251 l_proc varchar2(72) := g_package||'chk_transfered_to_gl_flag';
252 l_api_updating boolean;
253 --
254 Begin
255 --
256 hr_utility.set_location('Entering:'||l_proc, 5);
257 --
258 l_api_updating := pqh_bvr_shd.api_updating
259 (p_budget_version_id => p_budget_version_id,
260 p_object_version_number => p_object_version_number);
261 --
262 if (l_api_updating
263 and p_transfered_to_gl_flag
264 <> nvl(pqh_bvr_shd.g_old_rec.transfered_to_gl_flag,hr_api.g_varchar2)
265 or not l_api_updating)
266 and p_transfered_to_gl_flag is not null then
267 --
268 -- check if value of lookup falls within lookup type.
269 --
270 if hr_api.not_exists_in_hr_lookups
271 (p_lookup_type => 'YES_NO',
272 p_lookup_code => p_transfered_to_gl_flag,
273 p_effective_date => p_effective_date) then
274 --
275 -- raise error as does not exist as lookup
276 --
277 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
278 hr_utility.raise_error;
279 --
280 end if;
281 --
282 end if;
283 --
284 hr_utility.set_location('Leaving:'||l_proc,10);
285 --
286 end chk_transfered_to_gl_flag;
287 --
288 -- ----------------------------------------------------------------------------
289 -- |---------------------------< insert_validate >----------------------------|
290 -- ----------------------------------------------------------------------------
291 Procedure insert_validate(p_rec in pqh_bvr_shd.g_rec_type
292 ,p_effective_date in date) is
293 --
294 l_proc varchar2(72) := g_package||'insert_validate';
295 --
296 Begin
297 hr_utility.set_location('Entering:'||l_proc, 5);
298 --
299 -- Call all supporting business operations
300 --
301 chk_budget_version_id
302 (p_budget_version_id => p_rec.budget_version_id,
303 p_object_version_number => p_rec.object_version_number);
304 --
305 chk_budget_id
306 (p_budget_version_id => p_rec.budget_version_id,
307 p_budget_id => p_rec.budget_id,
308 p_object_version_number => p_rec.object_version_number);
309 --
310 chk_xfer_to_other_apps_cd
311 (p_budget_version_id => p_rec.budget_version_id,
312 p_xfer_to_other_apps_cd => p_rec.xfer_to_other_apps_cd,
313 p_effective_date => p_effective_date,
314 p_object_version_number => p_rec.object_version_number);
315 --
316 chk_transfered_to_gl_flag
317 (p_budget_version_id => p_rec.budget_version_id,
318 p_transfered_to_gl_flag => p_rec.transfered_to_gl_flag,
319 p_effective_date => p_effective_date,
320 p_object_version_number => p_rec.object_version_number);
321 --
322 --
323 --
324 hr_utility.set_location(' Leaving:'||l_proc, 10);
325 End insert_validate;
326 --
327 -- ----------------------------------------------------------------------------
328 -- |---------------------------< update_validate >----------------------------|
329 -- ----------------------------------------------------------------------------
330 Procedure update_validate(p_rec in pqh_bvr_shd.g_rec_type
331 ,p_effective_date in date) is
332 --
333 l_proc varchar2(72) := g_package||'update_validate';
334 --
335 Begin
336 hr_utility.set_location('Entering:'||l_proc, 5);
340 chk_budget_version_id
337 --
338 -- Call all supporting business operations
339 --
341 (p_budget_version_id => p_rec.budget_version_id,
342 p_object_version_number => p_rec.object_version_number);
343 --
344 chk_budget_id
345 (p_budget_version_id => p_rec.budget_version_id,
346 p_budget_id => p_rec.budget_id,
347 p_object_version_number => p_rec.object_version_number);
348 --
349 chk_xfer_to_other_apps_cd
350 (p_budget_version_id => p_rec.budget_version_id,
351 p_xfer_to_other_apps_cd => p_rec.xfer_to_other_apps_cd,
352 p_effective_date => p_effective_date,
353 p_object_version_number => p_rec.object_version_number);
354 --
355 chk_transfered_to_gl_flag
356 (p_budget_version_id => p_rec.budget_version_id,
357 p_transfered_to_gl_flag => p_rec.transfered_to_gl_flag,
358 p_effective_date => p_effective_date,
359 p_object_version_number => p_rec.object_version_number);
360 --
361 --
362 --
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 End update_validate;
365 --
366 -- ----------------------------------------------------------------------------
367 -- |---------------------------< delete_validate >----------------------------|
368 -- ----------------------------------------------------------------------------
369 Procedure delete_validate(p_rec in pqh_bvr_shd.g_rec_type
370 ,p_effective_date in date) is
371 --
372 l_proc varchar2(72) := g_package||'delete_validate';
373 --
374 CURSOR csr_bvr IS
375 SELECT transfered_to_gl_flag,
376 gl_status
377 FROM pqh_budget_versions
378 WHERE budget_version_id = p_rec.budget_version_id;
379
380 l_gl_flag pqh_budget_versions.transfered_to_gl_flag%TYPE := '';
381 l_gl_status pqh_budget_versions.gl_status%TYPE := '';
382
383 Begin
384 hr_utility.set_location('Entering:'||l_proc, 5);
385 --
386 -- Call all supporting business operations
387 --
388 --
389 --
390 OPEN csr_bvr;
391 FETCH csr_bvr INTO l_gl_flag,l_gl_status;
392 CLOSE csr_bvr;
393
394 --
395 hr_utility.set_location('transfered_to_gl_flag: '||l_gl_flag, 10);
396 hr_utility.set_location('gl_status: '||l_gl_status, 10);
397 --
398
399 IF (l_gl_flag IS NOT NULL) AND (l_gl_status IS NOT NULL) THEN
400 -- this is a posted version, delete not allowed
401 hr_utility.set_message(8302,'PQH_GL_REC_EXISTS');
402 hr_utility.raise_error;
403 --
404 END IF;
405
406 --
407
408 hr_utility.set_location(' Leaving:'||l_proc, 100);
409
410 End delete_validate;
411
412 --
413 end pqh_bvr_bus;