[Home] [Help]
PACKAGE BODY: APPS.PQH_DFS_BUS
Source
1 Package Body pqh_dfs_bus as
2 /* $Header: pqdfsrhi.pkb 115.11 2003/04/02 20:02:02 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_dfs_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_dflt_fund_src_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 -- dflt_fund_src_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_dflt_fund_src_id(p_dflt_fund_src_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_dflt_fund_src_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_dfs_shd.api_updating
47 (p_dflt_fund_src_id => p_dflt_fund_src_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_dflt_fund_src_id,hr_api.g_number)
52 <> pqh_dfs_shd.g_old_rec.dflt_fund_src_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_dfs_shd.constraint_error('PQH_DFLT_FUND_SRCS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_dflt_fund_src_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_dfs_shd.constraint_error('PQH_DFLT_FUND_SRCS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_dflt_fund_src_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_cost_allocation_keyflex_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_dflt_fund_src_id PK
89 -- p_cost_allocation_keyflex_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_cost_allocation_keyflex_id (p_dflt_fund_src_id in number,
102 p_cost_allocation_keyflex_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_cost_allocation_keyflex_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pay_cost_allocation_keyflex a
112 where a.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_dfs_shd.api_updating
119 (p_dflt_fund_src_id => p_dflt_fund_src_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_cost_allocation_keyflex_id,hr_api.g_number)
124 <> nvl(pqh_dfs_shd.g_old_rec.cost_allocation_keyflex_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_cost_allocation_keyflex_id is not null then
127 --
128 -- check if cost_allocation_keyflex_id value exists in pay_cost_allocation_keyflex 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 pay_cost_allocation_keyflex
138 -- table.
139 --
140 pqh_dfs_shd.constraint_error('PQH_DFLT_FUND_SRCS_FK2');
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_cost_allocation_keyflex_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_dflt_budget_element_id >------|
154 -- ----------------------------------------------------------------------------
155 --
156 -- Description
157 -- This procedure checks that a referenced foreign key actually exists
158 -- in the referenced table.
159 --
160 -- Pre-Conditions
161 -- None.
162 --
163 -- In Parameters
164 -- p_dflt_fund_src_id PK
165 -- p_dflt_budget_element_id ID of FK column
166 -- p_object_version_number object version number
167 --
168 -- Post Success
169 -- Processing continues
170 --
171 -- Post Failure
172 -- Error raised.
173 --
174 -- Access Status
175 -- Internal table handler use only.
176 --
177 Procedure chk_dflt_budget_element_id (p_dflt_fund_src_id in number,
178 p_dflt_budget_element_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_dflt_budget_element_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_dflt_budget_elements a
188 where a.dflt_budget_element_id = p_dflt_budget_element_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_dfs_shd.api_updating
195 (p_dflt_fund_src_id => p_dflt_fund_src_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_dflt_budget_element_id,hr_api.g_number)
200 <> nvl(pqh_dfs_shd.g_old_rec.dflt_budget_element_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if dflt_budget_element_id value exists in pqh_dflt_budget_elements table
204 --
205 open c1;
206 --
207 fetch c1 into l_dummy;
208 if c1%notfound then
209 --
210 close c1;
211 --
212 -- raise error as FK does not relate to PK in pqh_dflt_budget_elements
213 -- table.
214 --
215 pqh_dfs_shd.constraint_error('PQH_DFLT_FUND_SRCS_FK1');
216 --
217 end if;
218 --
219 close c1;
220 --
221 end if;
222 --
223 hr_utility.set_location('Leaving:'||l_proc,10);
224 --
225 End chk_dflt_budget_element_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |---------------------------< chk_duplicate_src >----------------------------|
229 -- ----------------------------------------------------------------------------
230 Procedure chk_duplicate_src (p_dflt_budget_element_id in number,
231 p_dflt_fund_src_id in number,
232 p_project_id in number,
233 p_award_id in number,
234 p_task_id in number,
235 p_expenditure_type in varchar2,
236 p_organization_id in number,
237 p_cost_allocation_keyflex_id in number) is
238 --
239 l_proc varchar2(72) := g_package||'chk_duplicate_src';
240 --
241 l_dummy varchar2(1) ;
242
243 cursor csr_src is
244 select 'X'
245 from pqh_dflt_fund_srcs
246 where dflt_budget_element_id = p_dflt_budget_element_id
247 and dflt_fund_src_id <> nvl(p_dflt_fund_src_id,0)
248 and nvl(cost_allocation_keyflex_id,0) = nvl(p_cost_allocation_keyflex_id,0)
249 and nvl(project_id,0) = nvl(p_project_id,0)
250 and nvl(award_id,0) = nvl(p_award_id,0)
251 and nvl(task_id,0) = nvl(p_task_id,0)
252 and nvl(expenditure_type,0) = nvl(p_expenditure_type,0)
253 and nvl(organization_id,0) = nvl(p_organization_id,0)
254 ;
255
256 Begin
257 --
258 hr_utility.set_location('Entering:'||l_proc, 5);
259 --
260 open csr_src;
261 fetch csr_src into l_dummy;
262 close csr_src;
263
264 if nvl(l_dummy ,'Y') = 'X' then
265 --
266 hr_utility.set_message(8302,'PQH_DUPLICATE_BUDGET_SRCS');
267 hr_utility.raise_error;
268 --
269 end if;
270 if p_cost_allocation_keyflex_id is not null then
271 if p_project_id is not null
272 or p_award_id is not null
273 or p_task_id is not null
274 or p_expenditure_type is not null
275 or p_organization_id is not null then
276 hr_utility.set_message(8302,'PQH_BUDGET_SRC_GL_GMS');
277 hr_utility.raise_error;
278 end if;
279 else
280 if p_project_id is null
281 or p_award_id is null
282 or p_task_id is null
283 or p_expenditure_type is null
284 or p_organization_id is null then
285 hr_utility.set_message(8302,'PQH_BUDGET_SRC_MANDATORY');
286 hr_utility.raise_error;
287 end if;
288 end if;
289 --
290 hr_utility.set_location('Leaving:'||l_proc,10);
291 --
292 end chk_duplicate_src;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |---------------------------< chk_sum >----------------------------|
296 -- ----------------------------------------------------------------------------
297 Procedure chk_sum (p_dflt_budget_element_id in number) is
298 --
299 l_proc varchar2(72) := g_package||'chk_sum';
300 --
301
302 l_sum number(15,2) := 0;
303
304 cursor csr_element is
305 select SUM(NVL(dflt_dist_percentage,0))
306 from pqh_dflt_fund_srcs
307 where dflt_budget_element_id = p_dflt_budget_element_id;
308
309 Begin
310 --
311 hr_utility.set_location('Entering:'||l_proc, 5);
312 --
313 open csr_element;
314 fetch csr_element into l_sum;
315 close csr_element;
316
317 if l_sum > 100 then
318 -- sum cannot be more then 100
319 --
320 hr_utility.set_message(8302,'PQH_WKS_INVALID_SRCS_SUM');
321 hr_utility.raise_error;
322 --
323 end if;
324
325 --
326 hr_utility.set_location('Leaving:'||l_proc,10);
327 --
328 end chk_sum;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |---------------------------< chk_percentage >----------------------------|
332 -- ----------------------------------------------------------------------------
333 Procedure chk_percentage (p_dflt_dist_percentage in number) is
334 --
335 l_proc varchar2(72) := g_package||'chk_percentage';
336 --
337
338 Begin
339 --
340 hr_utility.set_location('Entering:'||l_proc, 5);
341 --
342 if NVL(p_dflt_dist_percentage,0) < 0 then
343 -- percentage cannot be less then zero
344 --
345 hr_utility.set_message(8302,'PQH_WKS_INVALID_SRC_PERCENT');
346 hr_utility.raise_error;
347 --
348 end if;
349
350 --
351 hr_utility.set_location('Leaving:'||l_proc,10);
352 --
353 end chk_percentage;
354 --
355 -- ----------------------------------------------------------------------------
356 -- |---------------------------< insert_validate >----------------------------|
357 -- ----------------------------------------------------------------------------
358 Procedure insert_validate(p_rec in pqh_dfs_shd.g_rec_type) is
359 --
360 l_proc varchar2(72) := g_package||'insert_validate';
361 --
362 Begin
363 hr_utility.set_location('Entering:'||l_proc, 5);
364 --
365 -- Call all supporting business operations
366 --
367 chk_dflt_fund_src_id
368 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
369 p_object_version_number => p_rec.object_version_number);
370 --
371 chk_cost_allocation_keyflex_id
372 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
373 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
374 p_object_version_number => p_rec.object_version_number);
375 --
376 chk_dflt_budget_element_id
377 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
378 p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
379 p_object_version_number => p_rec.object_version_number);
380 --
381 chk_duplicate_src
382 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
383 p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
384 p_project_id => p_rec.project_id,
385 p_award_id => p_rec.award_id,
386 p_task_id => p_rec.task_id,
387 p_expenditure_type => p_rec.expenditure_type,
388 p_organization_id => p_rec.organization_id,
389 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
390 --
391 --
392 chk_percentage
393 (p_dflt_dist_percentage => p_rec.dflt_dist_percentage );
394 --
395 hr_utility.set_location(' Leaving:'||l_proc, 10);
396 End insert_validate;
397 --
398 -- ----------------------------------------------------------------------------
399 -- |---------------------------< update_validate >----------------------------|
400 -- ----------------------------------------------------------------------------
401 Procedure update_validate(p_rec in pqh_dfs_shd.g_rec_type) is
402 --
403 l_proc varchar2(72) := g_package||'update_validate';
404 --
405 Begin
406 hr_utility.set_location('Entering:'||l_proc, 5);
407 --
408 -- Call all supporting business operations
409 --
410 chk_dflt_fund_src_id
411 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
412 p_object_version_number => p_rec.object_version_number);
413 --
414 chk_cost_allocation_keyflex_id
415 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
416 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
417 p_object_version_number => p_rec.object_version_number);
418 --
419 chk_dflt_budget_element_id
420 (p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
421 p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
422 p_object_version_number => p_rec.object_version_number);
423 --
424 chk_duplicate_src
425 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
426 p_dflt_fund_src_id => p_rec.dflt_fund_src_id,
427 p_project_id => p_rec.project_id,
428 p_award_id => p_rec.award_id,
429 p_task_id => p_rec.task_id,
430 p_expenditure_type => p_rec.expenditure_type,
431 p_organization_id => p_rec.organization_id,
432 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
433 --
434 --
435 chk_percentage
436 (p_dflt_dist_percentage => p_rec.dflt_dist_percentage );
437 --
438 --
439 --
440 hr_utility.set_location(' Leaving:'||l_proc, 10);
441 End update_validate;
442 --
443 -- ----------------------------------------------------------------------------
444 -- |---------------------------< delete_validate >----------------------------|
445 -- ----------------------------------------------------------------------------
446 Procedure delete_validate(p_rec in pqh_dfs_shd.g_rec_type) is
447 --
448 l_proc varchar2(72) := g_package||'delete_validate';
449 --
450 Begin
451 hr_utility.set_location('Entering:'||l_proc, 5);
452 --
453 -- Call all supporting business operations
454 --
455 hr_utility.set_location(' Leaving:'||l_proc, 10);
456 End delete_validate;
457 --
458 end pqh_dfs_bus;