[Home] [Help]
PACKAGE BODY: APPS.PQH_BFS_BUS
Source
1 Package Body pqh_bfs_bus as
2 /* $Header: pqbfsrhi.pkb 115.10 2003/04/02 20:01:46 srajakum ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_bfs_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_budget_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 -- budget_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_budget_fund_src_id(p_budget_fund_src_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_budget_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_bfs_shd.api_updating
47 (p_budget_fund_src_id => p_budget_fund_src_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_budget_fund_src_id,hr_api.g_number)
52 <> pqh_bfs_shd.g_old_rec.budget_fund_src_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_bfs_shd.constraint_error('PQH_BUDGET_FUND_SRC_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_budget_fund_src_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_bfs_shd.constraint_error('PQH_BUDGET_FUND_SRC_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_budget_fund_src_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_budget_element_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_fund_src_id PK
89 -- p_budget_element_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_element_id (p_budget_fund_src_id in number,
102 p_budget_element_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_budget_element_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_budget_elements a
112 where a.budget_element_id = p_budget_element_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_bfs_shd.api_updating
119 (p_budget_fund_src_id => p_budget_fund_src_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_budget_element_id,hr_api.g_number)
124 <> nvl(pqh_bfs_shd.g_old_rec.budget_element_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if budget_element_id value exists in pqh_budget_elements 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 pqh_budget_elements
137 -- table.
138 --
139 pqh_bfs_shd.constraint_error('PQH_BUDGET_FUND_SRCS_FK1');
140 --
141 end if;
142 --
143 close c1;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 End chk_budget_element_id;
150 --
151 --
152 -- ----------------------------------------------------------------------------
153 -- |---------------------------< chk_duplicate_src >----------------------------|
154 -- ----------------------------------------------------------------------------
155 Procedure chk_duplicate_src (p_budget_element_id in number,
156 p_budget_fund_src_id in number,
157 p_project_id in number,
158 p_award_id in number,
159 p_task_id in number,
160 p_expenditure_type in varchar2,
161 p_organization_id in number,
162 p_cost_allocation_keyflex_id in number) is
163 --
164 l_proc varchar2(72) := g_package||'chk_duplicate_src';
165 --
166 l_dummy varchar2(1) ;
167
168 cursor csr_src is
169 select 'X'
170 from pqh_budget_fund_srcs
171 where budget_element_id = p_budget_element_id
172 and budget_fund_src_id <> nvl(p_budget_fund_src_id,0)
173 and nvl(cost_allocation_keyflex_id,0) = nvl(p_cost_allocation_keyflex_id,0)
174 and nvl(project_id,0) = nvl(p_project_id,0)
175 and nvl(award_id,0) = nvl(p_award_id,0)
176 and nvl(task_id,0) = nvl(p_task_id,0)
177 and nvl(expenditure_type,0) = nvl(p_expenditure_type,0)
178 and nvl(organization_id,0) = nvl(p_organization_id,0)
179 ;
180
181 Begin
182 --
183 hr_utility.set_location('Entering:'||l_proc, 5);
184 --
185 open csr_src;
186 fetch csr_src into l_dummy;
187 close csr_src;
188
189 if nvl(l_dummy ,'Y') = 'X' then
190 --
191 hr_utility.set_message(8302,'PQH_DUPLICATE_BUDGET_SRCS');
192 hr_utility.raise_error;
193 --
194 end if;
195 if p_cost_allocation_keyflex_id is not null then
196 if p_project_id is not null
197 or p_award_id is not null
198 or p_task_id is not null
199 or p_expenditure_type is not null
200 or p_organization_id is not null then
201 hr_utility.set_message(8302,'PQH_BUDGET_SRC_GL_GMS');
202 hr_utility.raise_error;
203 end if;
204 else
205 if p_project_id is null
206 or p_award_id is null
207 or p_task_id is null
208 or p_expenditure_type is null
209 or p_organization_id is null then
210 hr_utility.set_message(8302,'PQH_BUDGET_SRC_MANDATORY');
211 hr_utility.raise_error;
212 end if;
213 end if;
214 --
215 hr_utility.set_location('Leaving:'||l_proc,10);
216 --
217 end chk_duplicate_src;
218 --
219 -- ----------------------------------------------------------------------------
220 -- |------< chk_cost_allocation_keyflex_id >------|
221 -- ----------------------------------------------------------------------------
222 --
223 Procedure chk_cost_allocation_keyflex_id (p_budget_fund_src_id in number,
224 p_cost_allocation_keyflex_id in number,
225 p_object_version_number in number) is
226 --
227 l_proc varchar2(72) := g_package||'chk_cost_allocation_keyflex_id';
228 l_api_updating boolean;
229 l_dummy varchar2(1);
230 --
231 cursor c1 is
232 select null
233 from pay_cost_allocation_keyflex a
234 where a.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
235 --
236 Begin
237 --
238 hr_utility.set_location('Entering:'||l_proc,5);
239 --
240 l_api_updating := pqh_bfs_shd.api_updating
241 (p_budget_fund_src_id => p_budget_fund_src_id,
242 p_object_version_number => p_object_version_number);
243 --
244 if (l_api_updating
245 and nvl(p_cost_allocation_keyflex_id,hr_api.g_number)
246 <> nvl(pqh_bfs_shd.g_old_rec.cost_allocation_keyflex_id,hr_api.g_number)
247 or not l_api_updating) and p_cost_allocation_keyflex_id is not null then
248 --
249 -- check if cost_allocation_keyflex_id value exists in pay_cost_allocation_keyflex table
250 --
251 open c1;
252 --
253 fetch c1 into l_dummy;
254 if c1%notfound then
255 --
256 close c1;
257 --
258 -- raise error as FK does not relate to PK in pqh_budget_elements
259 -- table.
260 --
261 hr_utility.set_message(8302,'PQH_INVALID_COST_KEYFLEX');
262 hr_utility.raise_error;
263 --
264 end if;
265 --
266 close c1;
267 --
268 end if;
269 --
270 hr_utility.set_location('Leaving:'||l_proc,10);
271 --
272 End chk_cost_allocation_keyflex_id;
273
274 -- ----------------------------------------------------------------------------
275 -- |---------------------------< insert_validate >----------------------------|
276 -- ----------------------------------------------------------------------------
277 Procedure insert_validate(p_rec in pqh_bfs_shd.g_rec_type) is
278 --
279 l_proc varchar2(72) := g_package||'insert_validate';
280 --
281 Begin
282 hr_utility.set_location('Entering:'||l_proc, 5);
283 --
284 -- Call all supporting business operations
285 --
286 chk_budget_fund_src_id
287 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
288 p_object_version_number => p_rec.object_version_number);
289 --
290 chk_budget_element_id
291 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
292 p_budget_element_id => p_rec.budget_element_id,
293 p_object_version_number => p_rec.object_version_number);
294 --
295 chk_cost_allocation_keyflex_id
296 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
297 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
298 p_object_version_number => p_rec.object_version_number);
299 --
300 chk_duplicate_src (p_budget_element_id => p_rec.budget_element_id,
301 p_budget_fund_src_id => p_rec.budget_fund_src_id,
302 p_project_id => p_rec.project_id,
303 p_award_id => p_rec.award_id,
304 p_task_id => p_rec.task_id,
305 p_expenditure_type => p_rec.expenditure_type,
306 p_organization_id => p_rec.organization_id,
307 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
308 --
309 hr_utility.set_location(' Leaving:'||l_proc, 10);
310 End insert_validate;
311 --
312 -- ----------------------------------------------------------------------------
313 -- |---------------------------< update_validate >----------------------------|
314 -- ----------------------------------------------------------------------------
315 Procedure update_validate(p_rec in pqh_bfs_shd.g_rec_type) is
316 --
317 l_proc varchar2(72) := g_package||'update_validate';
318 --
319 Begin
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 -- Call all supporting business operations
323 --
324 chk_budget_fund_src_id
325 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
326 p_object_version_number => p_rec.object_version_number);
327 --
328 chk_budget_element_id
329 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
330 p_budget_element_id => p_rec.budget_element_id,
331 p_object_version_number => p_rec.object_version_number);
332 --
333 chk_cost_allocation_keyflex_id
334 (p_budget_fund_src_id => p_rec.budget_fund_src_id,
335 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id,
336 p_object_version_number => p_rec.object_version_number);
337 --
338 chk_duplicate_src (p_budget_element_id => p_rec.budget_element_id,
339 p_budget_fund_src_id => p_rec.budget_fund_src_id,
340 p_project_id => p_rec.project_id,
341 p_award_id => p_rec.award_id,
342 p_task_id => p_rec.task_id,
343 p_expenditure_type => p_rec.expenditure_type,
344 p_organization_id => p_rec.organization_id,
345 p_cost_allocation_keyflex_id => p_rec.cost_allocation_keyflex_id);
346 --
347 --
348 hr_utility.set_location(' Leaving:'||l_proc, 10);
349 End update_validate;
350 --
351 -- ----------------------------------------------------------------------------
352 -- |---------------------------< delete_validate >----------------------------|
353 -- ----------------------------------------------------------------------------
354 Procedure delete_validate(p_rec in pqh_bfs_shd.g_rec_type) is
355 --
356 l_proc varchar2(72) := g_package||'delete_validate';
357 --
358 Begin
359 hr_utility.set_location('Entering:'||l_proc, 5);
360 --
361 -- Call all supporting business operations
362 --
363 hr_utility.set_location(' Leaving:'||l_proc, 10);
364 End delete_validate;
365 --
366 end pqh_bfs_bus;