[Home] [Help]
PACKAGE BODY: APPS.PQH_PLG_BUS
Source
1 Package Body pqh_plg_bus as
2 /* $Header: pqplgrhi.pkb 115.5 2002/12/12 23:13:49 sgoyal ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pqh_plg_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_process_log_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 -- process_log_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_process_log_id(p_process_log_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_process_log_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_plg_shd.api_updating
47 (p_process_log_id => p_process_log_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_process_log_id,hr_api.g_number)
52 <> pqh_plg_shd.g_old_rec.process_log_id) then
53 --
54 -- raise error as PK has changed
55 --
56 pqh_plg_shd.constraint_error('PQH_PROCESS_LOG_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_process_log_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 pqh_plg_shd.constraint_error('PQH_PROCESS_LOG_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_process_log_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_txn_table_route_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_process_log_id PK
89 -- p_txn_table_route_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_txn_table_route_id (p_process_log_id in number,
102 p_txn_table_route_id in number,
103 p_object_version_number in number) is
104 --
105 l_proc varchar2(72) := g_package||'chk_txn_table_route_id';
106 l_api_updating boolean;
107 l_dummy varchar2(1);
108 --
109 cursor c1 is
110 select null
111 from pqh_table_route a
112 where a.table_route_id = p_txn_table_route_id;
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'||l_proc,5);
117 --
118 l_api_updating := pqh_plg_shd.api_updating
119 (p_process_log_id => p_process_log_id,
120 p_object_version_number => p_object_version_number);
121 --
122 if (l_api_updating
123 and nvl(p_txn_table_route_id,hr_api.g_number)
124 <> nvl(pqh_plg_shd.g_old_rec.txn_table_route_id,hr_api.g_number)
125 or not l_api_updating) and
126 p_txn_table_route_id is not null then
127 --
128 -- check if txn_table_route_id value exists in pqh_table_route table
129 --
130 open c1;
131 --
132 fetch c1 into l_dummy;
133 if c1%notfound then
134 --
135 close c1;
136 --
137 -- raise error as FK does not relate to PK in pqh_table_route
138 -- table.
139 --
140 pqh_plg_shd.constraint_error('PQH_PROCESS_LOG_FK2');
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_txn_table_route_id;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |------< chk_master_process_log_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_process_log_id PK
165 -- p_master_process_log_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_master_process_log_id (p_process_log_id in number,
178 p_master_process_log_id in number,
179 p_object_version_number in number) is
180 --
181 l_proc varchar2(72) := g_package||'chk_master_process_log_id';
182 l_api_updating boolean;
183 l_dummy varchar2(1);
184 --
185 cursor c1 is
186 select null
187 from pqh_process_log a
188 where a.process_log_id = p_master_process_log_id;
189 --
190 Begin
191 --
192 hr_utility.set_location('Entering:'||l_proc,5);
193 --
194 l_api_updating := pqh_plg_shd.api_updating
195 (p_process_log_id => p_process_log_id,
196 p_object_version_number => p_object_version_number);
197 --
198 if (l_api_updating
199 and nvl(p_master_process_log_id,hr_api.g_number)
200 <> nvl(pqh_plg_shd.g_old_rec.master_process_log_id,hr_api.g_number)
201 or not l_api_updating) and
202 p_master_process_log_id is not null then
203 --
204 -- check if master_process_log_id value exists in pqh_process_log table
205 --
206 open c1;
207 --
208 fetch c1 into l_dummy;
209 if c1%notfound then
210 --
211 close c1;
212 --
213 -- raise error as FK does not relate to PK in pqh_process_log
214 -- table.
215 --
216 pqh_plg_shd.constraint_error('PQH_PROCESS_LOG_FK1');
217 --
218 end if;
219 --
220 close c1;
221 --
222 end if;
223 --
224 hr_utility.set_location('Leaving:'||l_proc,10);
225 --
226 End chk_master_process_log_id;
227 --
228 -- ----------------------------------------------------------------------------
229 -- |------< chk_message_type_cd >------|
230 -- ----------------------------------------------------------------------------
231 --
232 -- Description
233 -- This procedure is used to check that the lookup value is valid.
234 --
235 -- Pre Conditions
236 -- None.
237 --
238 -- In Parameters
239 -- process_log_id PK of record being inserted or updated.
240 -- message_type_cd Value of lookup code.
241 -- effective_date effective date
242 -- object_version_number Object version number of record being
243 -- inserted or updated.
244 --
245 -- Post Success
246 -- Processing continues
247 --
248 -- Post Failure
249 -- Error handled by procedure
250 --
251 -- Access Status
252 -- Internal table handler use only.
253 --
254 Procedure chk_message_type_cd(p_process_log_id in number,
255 p_message_type_cd in varchar2,
256 p_effective_date in date,
257 p_object_version_number in number) is
258 --
259 l_proc varchar2(72) := g_package||'chk_message_type_cd';
260 l_api_updating boolean;
261 --
262 Begin
263 --
264 hr_utility.set_location('Entering:'||l_proc, 5);
265 --
266 l_api_updating := pqh_plg_shd.api_updating
267 (p_process_log_id => p_process_log_id,
268 p_object_version_number => p_object_version_number);
269 --
270 if (l_api_updating
271 and p_message_type_cd
272 <> nvl(pqh_plg_shd.g_old_rec.message_type_cd,hr_api.g_varchar2)
273 or not l_api_updating) then
274 --
275 -- check if value of lookup falls within lookup type.
276 --
277 --
278 if hr_api.not_exists_in_hr_lookups
279 (p_lookup_type => 'PQH_MESSAGE_TYPE',
280 p_lookup_code => p_message_type_cd,
281 p_effective_date => p_effective_date) then
282 --
283 -- raise error as does not exist as lookup
284 --
285 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
286 hr_utility.raise_error;
287 --
288 end if;
289 --
290 end if;
291 --
292 hr_utility.set_location('Leaving:'||l_proc,10);
293 --
294 end chk_message_type_cd;
295 --
296 --
297 -- ----------------------------------------------------------------------------
298 -- |---------------------------< insert_validate >----------------------------|
299 -- ----------------------------------------------------------------------------
300 Procedure insert_validate(p_rec in pqh_plg_shd.g_rec_type
301 ,p_effective_date in date) is
302 --
303 l_proc varchar2(72) := g_package||'insert_validate';
304 --
305 Begin
306 hr_utility.set_location('Entering:'||l_proc, 5);
307 --
308 -- Call all supporting business operations
309 --
310 chk_process_log_id
311 (p_process_log_id => p_rec.process_log_id,
312 p_object_version_number => p_rec.object_version_number);
313 --
314 chk_txn_table_route_id
315 (p_process_log_id => p_rec.process_log_id,
316 p_txn_table_route_id => p_rec.txn_table_route_id,
317 p_object_version_number => p_rec.object_version_number);
318 --
319 chk_master_process_log_id
320 (p_process_log_id => p_rec.process_log_id,
321 p_master_process_log_id => p_rec.master_process_log_id,
322 p_object_version_number => p_rec.object_version_number);
323 --
324 chk_message_type_cd
325 (p_process_log_id => p_rec.process_log_id,
326 p_message_type_cd => p_rec.message_type_cd,
327 p_effective_date => p_effective_date,
328 p_object_version_number => p_rec.object_version_number);
329 --
330 --
331 --
332 --
333 hr_utility.set_location(' Leaving:'||l_proc, 10);
334 End insert_validate;
335 --
336 -- ----------------------------------------------------------------------------
337 -- |---------------------------< update_validate >----------------------------|
338 -- ----------------------------------------------------------------------------
339 Procedure update_validate(p_rec in pqh_plg_shd.g_rec_type
340 ,p_effective_date in date) is
341 --
342 l_proc varchar2(72) := g_package||'update_validate';
343 --
344 Begin
345 hr_utility.set_location('Entering:'||l_proc, 5);
346 --
347 -- Call all supporting business operations
348 --
349 chk_process_log_id
350 (p_process_log_id => p_rec.process_log_id,
351 p_object_version_number => p_rec.object_version_number);
352 --
353 chk_txn_table_route_id
354 (p_process_log_id => p_rec.process_log_id,
355 p_txn_table_route_id => p_rec.txn_table_route_id,
356 p_object_version_number => p_rec.object_version_number);
357 --
358 chk_master_process_log_id
359 (p_process_log_id => p_rec.process_log_id,
360 p_master_process_log_id => p_rec.master_process_log_id,
361 p_object_version_number => p_rec.object_version_number);
362 --
363 chk_message_type_cd
364 (p_process_log_id => p_rec.process_log_id,
365 p_message_type_cd => p_rec.message_type_cd,
366 p_effective_date => p_effective_date,
367 p_object_version_number => p_rec.object_version_number);
368 --
369 --
370 --
371 --
372 hr_utility.set_location(' Leaving:'||l_proc, 10);
373 End update_validate;
374 --
375 -- ----------------------------------------------------------------------------
376 -- |---------------------------< delete_validate >----------------------------|
377 -- ----------------------------------------------------------------------------
378 Procedure delete_validate(p_rec in pqh_plg_shd.g_rec_type
379 ,p_effective_date in date) is
380 --
381 l_proc varchar2(72) := g_package||'delete_validate';
382 --
383 Begin
384 hr_utility.set_location('Entering:'||l_proc, 5);
385 --
386 -- Call all supporting business operations
387 --
388 hr_utility.set_location(' Leaving:'||l_proc, 10);
389 End delete_validate;
390 --
391 end pqh_plg_bus;