[Home] [Help]
PACKAGE BODY: APPS.PQH_DEL_BUS
Source
1 Package Body pqh_del_bus as
2 /* $Header: pqdelrhi.pkb 115.7 2002/12/05 19:31:43 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_del_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_dflt_budget_element_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_budget_element_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_budget_element_id(p_dflt_budget_element_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_dflt_budget_element_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_del_shd.api_updating
47 (p_dflt_budget_element_id => p_dflt_budget_element_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_dflt_budget_element_id,hr_api.g_number)
52 <> pqh_del_shd.g_old_rec.dflt_budget_element_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_del_shd.constraint_error('PQH_DFLT_BUDGET_ELEMENTS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_dflt_budget_element_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_del_shd.constraint_error('PQH_DFLT_BUDGET_ELEMENTS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_dflt_budget_element_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_dflt_budget_set_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_budget_element_id PK
89 -- p_dflt_budget_set_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_dflt_budget_set_id (p_dflt_budget_element_id in number,
102 p_dflt_budget_set_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_dflt_budget_set_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_dflt_budget_sets a
112 where a.dflt_budget_set_id = p_dflt_budget_set_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_del_shd.api_updating
119 (p_dflt_budget_element_id => p_dflt_budget_element_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_dflt_budget_set_id,hr_api.g_number)
124 <> nvl(pqh_del_shd.g_old_rec.dflt_budget_set_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if dflt_budget_set_id value exists in pqh_dflt_budget_sets 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_dflt_budget_sets
137 -- table.
138 --
139 pqh_del_shd.constraint_error('PQH_DFLT_BUDGET_ELEMENTS_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_dflt_budget_set_id;
150 --
151 --
152 -- ----------------------------------------------------------------------------
153 -- |---------------------------< chk_duplicate_elements >----------------------------|
154 -- ----------------------------------------------------------------------------
155 Procedure chk_duplicate_elements (p_dflt_budget_set_id in number,
156 p_dflt_budget_element_id in number,
157 p_element_type_id in number) is
158 --
159 l_proc varchar2(72) := g_package||'chk_duplicate_elements';
160 --
161 l_dummy varchar2(1) ;
162
163 cursor csr_element is
164 select 'X'
165 from pqh_dflt_budget_elements
166 where dflt_budget_set_id = p_dflt_budget_set_id
167 and dflt_budget_element_id <> nvl(p_dflt_budget_element_id,0)
168 and element_type_id = p_element_type_id;
169
170 Begin
171 --
172 hr_utility.set_location('Entering:'||l_proc, 5);
173 --
174 open csr_element;
175 fetch csr_element into l_dummy;
176 close csr_element;
177
178 if nvl(l_dummy ,'Y') = 'X' then
179 --
180 hr_utility.set_message(8302,'PQH_DUPLICATE_BUDGET_ELEMENTS');
181 hr_utility.raise_error;
182 --
183 end if;
184
185 --
186 hr_utility.set_location('Leaving:'||l_proc,10);
187 --
188 end chk_duplicate_elements;
189 --
190 -- ----------------------------------------------------------------------------
191 -- |---------------------------< chk_sum >----------------------------|
192 -- ----------------------------------------------------------------------------
193 Procedure chk_sum (p_dflt_budget_set_id in number) is
194 --
195 l_proc varchar2(72) := g_package||'chk_sum';
196 --
197
198 l_sum number(15,2) := 0;
199
200 cursor csr_element is
201 select SUM(NVL(dflt_dist_percentage,0))
202 from pqh_dflt_budget_elements
203 where dflt_budget_set_id = p_dflt_budget_set_id;
204
205 Begin
206 --
207 hr_utility.set_location('Entering:'||l_proc, 5);
208 --
209 open csr_element;
210 fetch csr_element into l_sum;
211 close csr_element;
212
213 if l_sum > 100 then
214 -- sum cannot be more then 100
215 --
216 hr_utility.set_message(8302,'PQH_WKS_INVALID_ELMNT_SUM');
217 hr_utility.raise_error;
218 --
219 end if;
220
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 end chk_sum;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |---------------------------< chk_percentage >----------------------------|
228 -- ----------------------------------------------------------------------------
229 Procedure chk_percentage (p_dflt_dist_percentage in number) is
230 --
231 l_proc varchar2(72) := g_package||'chk_percentage';
232 --
233
234 Begin
235 --
236 hr_utility.set_location('Entering:'||l_proc, 5);
237 --
238 if NVL(p_dflt_dist_percentage,0) < 0 then
239 -- percentage cannot be less then zero
240 --
241 hr_utility.set_message(8302,'PQH_WKS_INVALID_ELMNT_PERCENT');
242 hr_utility.raise_error;
243 --
244 end if;
245
246 --
247 hr_utility.set_location('Leaving:'||l_proc,10);
248 --
249 end chk_percentage;
250 --
251 --
252 -- ----------------------------------------------------------------------------
253 -- |---------------------------< insert_validate >----------------------------|
254 -- ----------------------------------------------------------------------------
255 Procedure insert_validate(p_rec in pqh_del_shd.g_rec_type) is
256 --
257 l_proc varchar2(72) := g_package||'insert_validate';
258 --
259 Begin
260 hr_utility.set_location('Entering:'||l_proc, 5);
261 --
262 -- Call all supporting business operations
263 --
264 chk_dflt_budget_element_id
265 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
266 p_object_version_number => p_rec.object_version_number);
267 --
268 chk_dflt_budget_set_id
269 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
270 p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
271 p_object_version_number => p_rec.object_version_number);
272 --
273 chk_duplicate_elements
274 (p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
275 p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
276 p_element_type_id => p_rec.element_type_id);
277 --
278 --
279 chk_percentage
280 (p_dflt_dist_percentage => p_rec.dflt_dist_percentage );
281 --
282 hr_utility.set_location(' Leaving:'||l_proc, 10);
283 End insert_validate;
284 --
285 -- ----------------------------------------------------------------------------
286 -- |---------------------------< update_validate >----------------------------|
287 -- ----------------------------------------------------------------------------
288 Procedure update_validate(p_rec in pqh_del_shd.g_rec_type) is
289 --
290 l_proc varchar2(72) := g_package||'update_validate';
291 --
292 Begin
293 hr_utility.set_location('Entering:'||l_proc, 5);
294 --
295 -- Call all supporting business operations
296 --
297 chk_dflt_budget_element_id
298 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
299 p_object_version_number => p_rec.object_version_number);
300 --
301 chk_dflt_budget_set_id
302 (p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
303 p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
304 p_object_version_number => p_rec.object_version_number);
305 --
306 chk_duplicate_elements
307 (p_dflt_budget_set_id => p_rec.dflt_budget_set_id,
308 p_dflt_budget_element_id => p_rec.dflt_budget_element_id,
309 p_element_type_id => p_rec.element_type_id);
310 --
311 chk_percentage
312 (p_dflt_dist_percentage => p_rec.dflt_dist_percentage );
313 --
314 --
315 --
316 hr_utility.set_location(' Leaving:'||l_proc, 10);
317 End update_validate;
318 --
319 -- ----------------------------------------------------------------------------
320 -- |---------------------------< delete_validate >----------------------------|
321 -- ----------------------------------------------------------------------------
322 Procedure delete_validate(p_rec in pqh_del_shd.g_rec_type) is
323 --
324 l_proc varchar2(72) := g_package||'delete_validate';
325 --
326 Begin
327 hr_utility.set_location('Entering:'||l_proc, 5);
328 --
329 -- Call all supporting business operations
330 --
331 hr_utility.set_location(' Leaving:'||l_proc, 10);
332 End delete_validate;
333 --
334 end pqh_del_bus;