[Home] [Help]
PACKAGE BODY: APPS.PQH_TTM_BUS
Source
1 Package Body pqh_ttm_bus as
2 /* $Header: pqttmrhi.pkb 115.4 2002/12/12 21:26:03 rpasapul noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_ttm_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_transaction_template_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 -- transaction_template_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_transaction_template_id(p_transaction_template_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_transaction_template_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_ttm_shd.api_updating
47 (p_transaction_template_id => p_transaction_template_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_transaction_template_id,hr_api.g_number)
52 <> pqh_ttm_shd.g_old_rec.transaction_template_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_ttm_shd.constraint_error('PQH_TRANSACTION_TEMPLATE_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_transaction_template_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_ttm_shd.constraint_error('PQH_TRANSACTION_TEMPLATE_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_transaction_template_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_template_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_transaction_template_id PK
89 -- p_template_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_template_id (p_transaction_template_id in number,
102 p_template_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_template_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_templates a
112 where a.template_id = p_template_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_ttm_shd.api_updating
119 (p_transaction_template_id => p_transaction_template_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_template_id,hr_api.g_number)
124 <> nvl(pqh_ttm_shd.g_old_rec.template_id,hr_api.g_number)
125 or not l_api_updating) then
126 --
127 -- check if template_id value exists in pqh_templates 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_templates
137 -- table.
138 --
139 pqh_ttm_shd.constraint_error('PQH_TRANSACTION_TEMPLATE_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_template_id;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_transaction_category_id >------|
153 -- ----------------------------------------------------------------------------
154 --
155 -- Description
156 -- This procedure checks that a referenced foreign key actually exists
157 -- in the referenced table.
158 --
159 -- Pre-Conditions
160 -- None.
161 --
162 -- In Parameters
163 -- p_transaction_template_id PK
164 -- p_transaction_category_id ID of FK column
165 -- p_object_version_number object version number
166 --
167 -- Post Success
168 -- Processing continues
169 --
170 -- Post Failure
171 -- Error raised.
172 --
173 -- Access Status
174 -- Internal table handler use only.
175 --
176 Procedure chk_transaction_category_id (p_transaction_template_id in number,
177 p_transaction_category_id in number,
178 p_object_version_number in number) is
179 --
180 l_proc varchar2(72) := g_package||'chk_transaction_category_id';
181 l_api_updating boolean;
182 l_dummy varchar2(1);
183 --
184 cursor c1 is
185 select null
186 from pqh_transaction_categories a
187 where a.transaction_category_id = p_transaction_category_id;
188 --
189 Begin
190 --
191 hr_utility.set_location('Entering:'||l_proc,5);
192 --
193 l_api_updating := pqh_ttm_shd.api_updating
194 (p_transaction_template_id => p_transaction_template_id,
195 p_object_version_number => p_object_version_number);
196 --
197 if (l_api_updating
198 and nvl(p_transaction_category_id,hr_api.g_number)
199 <> nvl(pqh_ttm_shd.g_old_rec.transaction_category_id,hr_api.g_number)
200 or not l_api_updating) then
201 --
202 -- check if transaction_category_id value exists in pqh_transaction_categories table
203 --
204 open c1;
205 --
206 fetch c1 into l_dummy;
207 if c1%notfound then
208 --
209 close c1;
210 --
211 -- raise error as FK does not relate to PK in pqh_transaction_categories
212 -- table.
213 --
214 pqh_ttm_shd.constraint_error('PQH_TRANSACTION_TEMPLATE_FK1');
215 --
216 end if;
217 --
218 close c1;
219 --
220 end if;
221 --
222 hr_utility.set_location('Leaving:'||l_proc,10);
223 --
224 End chk_transaction_category_id;
225 --
226 -- ----------------------------------------------------------------------------
227 -- |------< chk_enable_flag >------|
228 -- ----------------------------------------------------------------------------
229 --
230 -- Description
231 -- This procedure is used to check that the lookup value is valid.
232 --
233 -- Pre Conditions
234 -- None.
235 --
236 -- In Parameters
237 -- transaction_template_id PK of record being inserted or updated.
238 -- enable_flag Value of lookup code.
239 -- effective_date effective date
240 -- object_version_number Object version number of record being
241 -- inserted or updated.
242 --
243 -- Post Success
244 -- Processing continues
245 --
246 -- Post Failure
247 -- Error handled by procedure
248 --
249 -- Access Status
250 -- Internal table handler use only.
251 --
252 Procedure chk_enable_flag(p_transaction_template_id in number,
253 p_enable_flag in varchar2,
254 p_effective_date in date,
255 p_object_version_number in number) is
256 --
257 l_proc varchar2(72) := g_package||'chk_enable_flag';
258 l_api_updating boolean;
259 --
260 Begin
261 --
262 hr_utility.set_location('Entering:'||l_proc, 5);
263 --
264 l_api_updating := pqh_ttm_shd.api_updating
265 (p_transaction_template_id => p_transaction_template_id,
266 p_object_version_number => p_object_version_number);
267 --
268 if (l_api_updating
269 and p_enable_flag
270 <> nvl(pqh_ttm_shd.g_old_rec.enable_flag,hr_api.g_varchar2)
271 or not l_api_updating)
272 and p_enable_flag is not null then
273 --
274 -- check if value of lookup falls within lookup type.
275 --
276 if hr_api.not_exists_in_hr_lookups
277 (p_lookup_type => 'YES_NO',
278 p_lookup_code => p_enable_flag,
279 p_effective_date => p_effective_date) then
280 --
281 -- raise error as does not exist as lookup
282 --
283 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
284 hr_utility.raise_error;
285 --
286 end if;
287 --
288 end if;
289 --
290 hr_utility.set_location('Leaving:'||l_proc,10);
291 --
292 end chk_enable_flag;
293 --
294 -- ----------------------------------------------------------------------------
295 -- |---------------------------< insert_validate >----------------------------|
296 -- ----------------------------------------------------------------------------
297 Procedure insert_validate(p_rec in pqh_ttm_shd.g_rec_type
298 ,p_effective_date in date) is
299 --
300 l_proc varchar2(72) := g_package||'insert_validate';
301 --
302 Begin
303 hr_utility.set_location('Entering:'||l_proc, 5);
304 --
305 -- Call all supporting business operations
306 --
307 chk_transaction_template_id
308 (p_transaction_template_id => p_rec.transaction_template_id,
309 p_object_version_number => p_rec.object_version_number);
310 --
311 chk_template_id
312 (p_transaction_template_id => p_rec.transaction_template_id,
313 p_template_id => p_rec.template_id,
314 p_object_version_number => p_rec.object_version_number);
315 --
316 chk_transaction_category_id
317 (p_transaction_template_id => p_rec.transaction_template_id,
318 p_transaction_category_id => p_rec.transaction_category_id,
319 p_object_version_number => p_rec.object_version_number);
320 --
321 chk_enable_flag
322 (p_transaction_template_id => p_rec.transaction_template_id,
323 p_enable_flag => p_rec.enable_flag,
324 p_effective_date => p_effective_date,
325 p_object_version_number => p_rec.object_version_number);
326 --
327 --
328 --
329 hr_utility.set_location(' Leaving:'||l_proc, 10);
330 End insert_validate;
331 --
332 -- ----------------------------------------------------------------------------
333 -- |---------------------------< update_validate >----------------------------|
334 -- ----------------------------------------------------------------------------
335 Procedure update_validate(p_rec in pqh_ttm_shd.g_rec_type
336 ,p_effective_date in date) is
337 --
338 l_proc varchar2(72) := g_package||'update_validate';
339 --
340 Begin
341 hr_utility.set_location('Entering:'||l_proc, 5);
342 --
343 -- Call all supporting business operations
344 --
345 chk_transaction_template_id
346 (p_transaction_template_id => p_rec.transaction_template_id,
347 p_object_version_number => p_rec.object_version_number);
348 --
349 chk_template_id
350 (p_transaction_template_id => p_rec.transaction_template_id,
351 p_template_id => p_rec.template_id,
352 p_object_version_number => p_rec.object_version_number);
353 --
354 chk_transaction_category_id
355 (p_transaction_template_id => p_rec.transaction_template_id,
356 p_transaction_category_id => p_rec.transaction_category_id,
357 p_object_version_number => p_rec.object_version_number);
358 --
359 chk_enable_flag
360 (p_transaction_template_id => p_rec.transaction_template_id,
361 p_enable_flag => p_rec.enable_flag,
362 p_effective_date => p_effective_date,
363 p_object_version_number => p_rec.object_version_number);
364 --
365 --
366 --
367 hr_utility.set_location(' Leaving:'||l_proc, 10);
368 End update_validate;
369 --
370 -- ----------------------------------------------------------------------------
371 -- |---------------------------< delete_validate >----------------------------|
372 -- ----------------------------------------------------------------------------
373 Procedure delete_validate(p_rec in pqh_ttm_shd.g_rec_type
374 ,p_effective_date in date) is
375 --
376 l_proc varchar2(72) := g_package||'delete_validate';
377 --
378 Begin
379 hr_utility.set_location('Entering:'||l_proc, 5);
380 --
381 -- Call all supporting business operations
382 --
383 hr_utility.set_location(' Leaving:'||l_proc, 10);
384 End delete_validate;
385 --
386 end pqh_ttm_bus;