[Home] [Help]
PACKAGE BODY: APPS.PQH_WFS_BUS
Source
1 Package Body pqh_wfs_bus as
2 /* $Header: pqwfsrhi.pkb 115.7 2003/04/02 20:02:19 srajakum noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wfs_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_worksheet_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 -- worksheet_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_worksheet_fund_src_id(p_worksheet_fund_src_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_worksheet_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_wfs_shd.api_updating
47 (p_worksheet_fund_src_id => p_worksheet_fund_src_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_worksheet_fund_src_id,hr_api.g_number)
52 <> pqh_wfs_shd.g_old_rec.worksheet_fund_src_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_wfs_shd.constraint_error('PQH_WORKSHEET_FUND_SRCS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_worksheet_fund_src_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_wfs_shd.constraint_error('PQH_WORKSHEET_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_worksheet_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_worksheet_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_worksheet_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_wfs_shd.api_updating
119 (p_worksheet_fund_src_id => p_worksheet_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_wfs_shd.g_old_rec.cost_allocation_keyflex_id,hr_api.g_number)
125 or not l_api_updating) and p_cost_allocation_keyflex_id is not null then
126 --
127 -- check if cost_allocation_keyflex_id value exists in pay_cost_allocation_keyflex table
128 --
129 open c1;
130 --
131 fetch c1 into l_dummy;
132 if c1%notfound then
133 --
134 close c1;
135 --
136 -- raise error as FK does not relate to PK in pay_cost_allocation_keyflex
137 -- table.
138 --
139 hr_utility.set_message(8302,'PQH_INVALID_COST_KEYFLEX');
140 hr_utility.raise_error;
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_worksheet_bdgt_elmnt_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_worksheet_fund_src_id PK
165 -- p_worksheet_bdgt_elmnt_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_worksheet_bdgt_elmnt_id (p_worksheet_fund_src_id in number,
178 p_worksheet_bdgt_elmnt_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_worksheet_bdgt_elmnt_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_worksheet_bdgt_elmnts a
188 where a.worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_wfs_shd.api_updating
195 (p_worksheet_fund_src_id => p_worksheet_fund_src_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_worksheet_bdgt_elmnt_id,hr_api.g_number)
200 <> nvl(pqh_wfs_shd.g_old_rec.worksheet_bdgt_elmnt_id,hr_api.g_number)
201 or not l_api_updating) then
202 --
203 -- check if worksheet_bdgt_elmnt_id value exists in pqh_worksheet_bdgt_elmnts 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_worksheet_bdgt_elmnts
213 -- table.
214 --
215 pqh_wfs_shd.constraint_error('PQH_WORKSHEET_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_worksheet_bdgt_elmnt_id;
226 --
227 -- ----------------------------------------------------------------------------
228 -- |---------------------------< chk_duplicate_src >----------------------------|
229 -- ----------------------------------------------------------------------------
230 Procedure chk_duplicate_src (p_worksheet_bdgt_elmnt_id in number,
231 p_worksheet_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_worksheet_fund_srcs
246 where worksheet_bdgt_elmnt_id = p_worksheet_bdgt_elmnt_id
247 and worksheet_fund_src_id <> nvl(p_worksheet_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 and p_award_id is null
282 and p_task_id is null
283 and p_expenditure_type is null
284 and 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 -- Additional check
295 --
296 Procedure chk_distribution_percentage(p_worksheet_fund_src_id in number,
297 p_distribution_percentage in number,
298 p_object_version_number in number) is
299 --
300 l_proc varchar2(72) := g_package||'chk_distribution_percentage';
301 l_api_updating boolean;
302 --
303 Begin
304 --
305 hr_utility.set_location('Entering:'||l_proc,5);
306 --
307 l_api_updating := pqh_wfs_shd.api_updating
308 (p_worksheet_fund_src_id => p_worksheet_fund_src_id,
309 p_object_version_number => p_object_version_number);
310 --
311 if (l_api_updating
312 and nvl(p_distribution_percentage,hr_api.g_number)
313 <> nvl(pqh_wel_shd.g_old_rec.distribution_percentage,hr_api.g_number)
314 or not l_api_updating)
315 and p_distribution_percentage is not null then
316 --
317 -- check if worksheet_budget_set_id value exists in pqh_worksheet_budget_sets table
318 --
319 If p_distribution_percentage < 0 then
320 hr_utility.set_message(8302,'PQH_INVALID_DISTRIB_PERCENT');
321 hr_utility.raise_error;
322 End if;
323 --
324 end if;
325 --
326 hr_utility.set_location('Leaving:'||l_proc,10);
327 --
328 End chk_distribution_percentage;
329 --
330 -- ----------------------------------------------------------------------------
331 -- |---------------------------< insert_validate >----------------------------|
332 -- ----------------------------------------------------------------------------
333 Procedure insert_validate(p_rec in pqh_wfs_shd.g_rec_type) is
334 --
335 l_proc varchar2(72) := g_package||'insert_validate';
336 --
337 Begin
338 hr_utility.set_location('Entering:'||l_proc, 5);
339 --
340 -- Call all supporting business operations
341 --
342 chk_worksheet_fund_src_id
343 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
344 p_object_version_number => p_rec.object_version_number);
345 --
346 chk_cost_allocation_keyflex_id
347 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
348 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
349 p_object_version_number => p_rec.object_version_number);
350 --
351 chk_worksheet_bdgt_elmnt_id
352 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
353 p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
354 p_object_version_number => p_rec.object_version_number);
355 --
356 --
357 chk_distribution_percentage
358 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
359 p_distribution_percentage => p_rec.distribution_percentage,
360 p_object_version_number => p_rec.object_version_number);
361 --
362 chk_duplicate_src (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
363 p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
364 p_project_id => p_rec.project_id,
365 p_award_id => p_rec.award_id,
366 p_task_id => p_rec.task_id,
367 p_expenditure_type => p_rec.expenditure_type,
368 p_organization_id => p_rec.organization_id,
369 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
370 --
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 10);
373 End insert_validate;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------< update_validate >----------------------------|
377 -- ----------------------------------------------------------------------------
378 Procedure update_validate(p_rec in pqh_wfs_shd.g_rec_type) is
379 --
380 l_proc varchar2(72) := g_package||'update_validate';
381 --
382 Begin
383 hr_utility.set_location('Entering:'||l_proc, 5);
384 --
385 -- Call all supporting business operations
386 --
387 chk_worksheet_fund_src_id
388 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
389 p_object_version_number => p_rec.object_version_number);
390 --
391 chk_cost_allocation_keyflex_id
392 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
393 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
394 p_object_version_number => p_rec.object_version_number);
395 --
399 p_object_version_number => p_rec.object_version_number);
396 chk_worksheet_bdgt_elmnt_id
397 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
398 p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
400 --
401 --
402 chk_distribution_percentage
403 (p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
404 p_distribution_percentage => p_rec.distribution_percentage,
405 p_object_version_number => p_rec.object_version_number);
406 --
407 chk_duplicate_src (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
408 p_worksheet_fund_src_id => p_rec.worksheet_fund_src_id,
409 p_project_id => p_rec.project_id,
410 p_award_id => p_rec.award_id,
411 p_task_id => p_rec.task_id,
412 p_expenditure_type => p_rec.expenditure_type,
413 p_organization_id => p_rec.organization_id,
414 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
415 --
416 --
417 --
418 hr_utility.set_location(' Leaving:'||l_proc, 10);
419 End update_validate;
420 --
421 -- ----------------------------------------------------------------------------
422 -- |---------------------------< delete_validate >----------------------------|
423 -- ----------------------------------------------------------------------------
424 Procedure delete_validate(p_rec in pqh_wfs_shd.g_rec_type) is
425 --
426 l_proc varchar2(72) := g_package||'delete_validate';
427 --
428 Begin
429 hr_utility.set_location('Entering:'||l_proc, 5);
430 --
431 -- Call all supporting business operations
432 --
433 hr_utility.set_location(' Leaving:'||l_proc, 10);
434 End delete_validate;
435 --
436 end pqh_wfs_bus;