[Home] [Help]
PACKAGE BODY: APPS.PQH_WEL_BUS
Source
1 Package Body pqh_wel_bus as
2 /* $Header: pqwelrhi.pkb 115.4 2002/12/13 00:36:31 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_wel_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_worksheet_bdgt_elmnt_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_bdgt_elmnt_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_bdgt_elmnt_id(p_worksheet_bdgt_elmnt_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_worksheet_bdgt_elmnt_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_wel_shd.api_updating
47 (p_worksheet_bdgt_elmnt_id => p_worksheet_bdgt_elmnt_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_worksheet_bdgt_elmnt_id,hr_api.g_number)
52 <> pqh_wel_shd.g_old_rec.worksheet_bdgt_elmnt_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_wel_shd.constraint_error('PQH_WORKSHEET_BDGT_ELMNTS_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_worksheet_bdgt_elmnt_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_wel_shd.constraint_error('PQH_WORKSHEET_BDGT_ELMNTS_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_worksheet_bdgt_elmnt_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_worksheet_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_worksheet_bdgt_elmnt_id PK
89 -- p_worksheet_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_worksheet_budget_set_id (p_worksheet_bdgt_elmnt_id in number,
102 p_worksheet_budget_set_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_worksheet_budget_set_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_worksheet_budget_sets a
112 where a.worksheet_budget_set_id = p_worksheet_budget_set_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_wel_shd.api_updating
119 (p_worksheet_bdgt_elmnt_id => p_worksheet_bdgt_elmnt_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_worksheet_budget_set_id,hr_api.g_number)
124 <> nvl(pqh_wel_shd.g_old_rec.worksheet_budget_set_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if worksheet_budget_set_id value exists in pqh_worksheet_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_worksheet_budget_sets
137 -- table.
138 --
139 pqh_wel_shd.constraint_error('PQH_WORKSHEET_BDGT_ELMNTS_FK2');
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_worksheet_budget_set_id;
150 --
151 --
152 ----------------------------------------------------------------------------
153 -- Additional checks
154 ----------------------------------------------------------------------------
155 --
156 --
157 Procedure chk_element_type_id (p_worksheet_bdgt_elmnt_id in number,
158 p_element_type_id in number,
159 p_object_version_number in number) is
160 --
161 l_proc varchar2(72) := g_package||'chk_element_type_id';
162 l_api_updating boolean;
163 l_dummy varchar2(1);
164 --
165 cursor c1 is
166 select null
167 from pay_element_types_f a
168 where a.element_type_id = p_element_type_id;
169 --
170 Begin
171 --
172 hr_utility.set_location('Entering:'||l_proc,5);
173 --
174 l_api_updating := pqh_wel_shd.api_updating
175 (p_worksheet_bdgt_elmnt_id => p_worksheet_bdgt_elmnt_id,
176 p_object_version_number => p_object_version_number);
177 --
178 if (l_api_updating
179 and nvl(p_element_type_id,hr_api.g_number)
180 <> nvl(pqh_wel_shd.g_old_rec.element_type_id,hr_api.g_number)
181 or not l_api_updating) then
182 --
183 -- check if worksheet_budget_set_id value exists in pqh_worksheet_budget_sets table
184 --
185 open c1;
186 --
187 fetch c1 into l_dummy;
188 if c1%notfound then
189 --
190 close c1;
191 --
192 -- raise error as FK does not relate to PK in pay_element_types_f
193 -- table.
194 --
195 hr_utility.set_message(8302,'PQH_INVALID_WKS_ELEMENT_TYPE');
196 hr_utility.raise_error;
197 --
198 end if;
199 --
200 close c1;
201 --
202 end if;
203 --
204 hr_utility.set_location('Leaving:'||l_proc,10);
205 --
206 End chk_element_type_id;
207 --
208 --
209 --
210 Procedure chk_distribution_percentage(p_worksheet_bdgt_elmnt_id in number,
211 p_distribution_percentage in number,
212 p_object_version_number in number) is
213 --
214 l_proc varchar2(72) := g_package||'chk_distribution_percentage';
215 l_api_updating boolean;
216 --
217 Begin
218 --
219 hr_utility.set_location('Entering:'||l_proc,5);
220 --
221 l_api_updating := pqh_wel_shd.api_updating
222 (p_worksheet_bdgt_elmnt_id => p_worksheet_bdgt_elmnt_id,
223 p_object_version_number => p_object_version_number);
224 --
225 if (l_api_updating
226 and nvl(p_distribution_percentage,hr_api.g_number)
227 <> nvl(pqh_wel_shd.g_old_rec.distribution_percentage,hr_api.g_number)
228 or not l_api_updating)
229 and p_distribution_percentage is not null then
230 --
231 -- check if worksheet_budget_set_id value exists in pqh_worksheet_budget_sets table
232 --
233 If p_distribution_percentage < 0 then
234 hr_utility.set_message(8302,'PQH_INVALID_DISTRIB_PERCENT');
235 hr_utility.raise_error;
236 End if;
237 --
238 end if;
239 --
240 hr_utility.set_location('Leaving:'||l_proc,10);
241 --
242 End chk_distribution_percentage;
243 --
244 -- ----------------------------------------------------------------------------
245 -- |---------------------------< insert_validate >----------------------------|
246 -- ----------------------------------------------------------------------------
247 Procedure insert_validate(p_rec in pqh_wel_shd.g_rec_type) is
248 --
249 l_proc varchar2(72) := g_package||'insert_validate';
250 --
251 Begin
252 hr_utility.set_location('Entering:'||l_proc, 5);
253 --
254 -- Call all supporting business operations
255 --
256 chk_worksheet_bdgt_elmnt_id
257 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
258 p_object_version_number => p_rec.object_version_number);
259 --
260 chk_worksheet_budget_set_id
261 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
262 p_worksheet_budget_set_id => p_rec.worksheet_budget_set_id,
263 p_object_version_number => p_rec.object_version_number);
264 --
265 --
266 chk_distribution_percentage
267 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
268 p_distribution_percentage => p_rec.distribution_percentage,
269 p_object_version_number => p_rec.object_version_number);
270 --
271 chk_element_type_id
272 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
273 p_element_type_id => p_rec.element_type_id,
274 p_object_version_number => p_rec.object_version_number);
275 --
276 --
277 --
278 hr_utility.set_location(' Leaving:'||l_proc, 10);
279 End insert_validate;
280 --
281 -- ----------------------------------------------------------------------------
285 --
282 -- |---------------------------< update_validate >----------------------------|
283 -- ----------------------------------------------------------------------------
284 Procedure update_validate(p_rec in pqh_wel_shd.g_rec_type) is
286 l_proc varchar2(72) := g_package||'update_validate';
287 --
288 Begin
289 hr_utility.set_location('Entering:'||l_proc, 5);
290 --
291 -- Call all supporting business operations
292 --
293 chk_worksheet_bdgt_elmnt_id
294 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
295 p_object_version_number => p_rec.object_version_number);
296 --
297 chk_worksheet_budget_set_id
298 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
299 p_worksheet_budget_set_id => p_rec.worksheet_budget_set_id,
300 p_object_version_number => p_rec.object_version_number);
301 --
302 --
303 chk_distribution_percentage
304 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
305 p_distribution_percentage => p_rec.distribution_percentage,
306 p_object_version_number => p_rec.object_version_number);
307 --
308 chk_element_type_id
309 (p_worksheet_bdgt_elmnt_id => p_rec.worksheet_bdgt_elmnt_id,
310 p_element_type_id => p_rec.element_type_id,
311 p_object_version_number => p_rec.object_version_number);
312 --
313 --
314 --
315 hr_utility.set_location(' Leaving:'||l_proc, 10);
316 End update_validate;
317 --
318 -- ----------------------------------------------------------------------------
319 -- |---------------------------< delete_validate >----------------------------|
320 -- ----------------------------------------------------------------------------
321 Procedure delete_validate(p_rec in pqh_wel_shd.g_rec_type) is
322 --
323 l_proc varchar2(72) := g_package||'delete_validate';
324 --
325 Begin
326 hr_utility.set_location('Entering:'||l_proc, 5);
327 --
328 -- Call all supporting business operations
329 --
330 hr_utility.set_location(' Leaving:'||l_proc, 10);
331 End delete_validate;
332 --
333 end pqh_wel_bus;