1 PACKAGE BODY hr_template_existence AS
2 /* $Header: pytmplex.pkb 115.0 99/07/17 06:38:07 porting ship $ */
3 /*
4 */
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1993 Oracle Corporation. *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation, *
18 * 500 Oracle Parkway, Redwood City, CA, 94065. *
19 * *
20 ******************************************************************
21
22 Name : hr_template_existence
23
24 Filename : pytmplex.pkb
25
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 23-MAY-1996 H.Parichabutr 40.0 Created. For upgrades to earnings
32 and dedns current as of today - ie.
33 sysdate.
34 25-JUL-1996 hparicha 40.1 Revised spr_exists, now returns
35 existing spr id AND the formula
36 id that it uses...p_ff_id param
37 is now an output.
38 06-AUG-1996 hparicha 40.2 Added functions for existence of link input values,
39 element entry values, and run result values for
40 upgrading existing earnings and deductions.
41
42 3rd Oct 1996 hparicha 40.3 398791. Added parameters for
43 effective date to be used in
44 all existence comparisons.
45
46 3rd Jan 1997 mreid 40.4 434903 - moved header line.
47
48 */
49 --
50 /*
51
52 This file contains functions that check for the existence of the following payroll objects:
53
54 (*) Element Type
55
56 (*) Input Value
57
58 (*) Balances
59
60 (*) Defined Balances
61
62 (*) Balance Feeds
63
64 (*) Status Processing Rules
65
66 (*) Formula Result Rules
67
68
69
70 This package is called from the involuntary, earnings, and deduction generator packages before creating any record...this makes template elements re-generatable and upgradeable !!!
71
72
73
74 These functions should check for existence by doing select count(*). If none are found, then return zero.
75
76 Calling function will perform insertion if value returned is zero. If the object does exist, then this function
77
78 will perform a select for the id of the record found; this id is returned as the value from the function. The calling function then knows any non-zero value returned from the function is the id of the existing record.
79
80
81
82 */
83
84
85
86 function bal_feed_exists ( p_bal_id in number,
87 p_iv_id in number,
88 p_bg_id in number,
89 p_eff_date in date default sysdate)
90 return number is
91
92 already_exists number := 0;
93
94 begin
95
96 hr_utility.set_location('hr_template_existence.bal_feed_exists',10);
97
98 select bf.balance_feed_id
99 into already_exists
100 from pay_balance_feeds_f bf
101 where bf.balance_type_id = p_bal_id
102 and bf.input_value_id = p_iv_id
103 and bf.business_group_id = p_bg_id
104 and p_eff_date between bf.effective_start_date
105 and bf.effective_end_date;
106
107 hr_utility.set_location('hr_template_existence.bal_feed_exists',20);
108
109 return already_exists;
110
111 exception when NO_DATA_FOUND then
112
113 hr_utility.set_location('hr_template_existence.bal_feed_DOES_NOT_exist',999);
114
115 return already_exists; -- ie. zero.
116
117 end bal_feed_exists;
118
119
120
121 function result_rule_exists ( p_spr_id in number,
122 p_frr_name in varchar2,
123 p_iv_id in number,
124 p_ele_id in number,
125 p_bg_id in number,
126 p_eff_date in date default sysdate) return number is
127
128 already_exists number := 0;
129
130 begin
131
132 hr_utility.set_location('hr_template_existence.result_rule_exists',10);
133
134 select frr.formula_result_rule_id
135 into already_exists
136 from pay_formula_result_rules_f frr
137 where frr.status_processing_rule_id = p_spr_id
138 and frr.result_name = p_frr_name
139 and frr.business_group_id = p_bg_id
140 and nvl(frr.input_value_id, nvl(p_iv_id, 0)) = nvl(p_iv_id, 0)
141 and nvl(frr.element_type_id, nvl(p_ele_id, 0)) = nvl(p_ele_id, 0)
142 and p_eff_date between frr.effective_start_date and frr.effective_end_date;
143
144 -- Note, the above sql checks for result rules which have been
145 -- created without providing an element type id...just an input value id...
146
147 hr_utility.set_location('hr_template_existence.result_rule_exists',20);
148
149 return already_exists;
150
151 exception when NO_DATA_FOUND then
152
153 hr_utility.set_location('hr_template_existence.result_rule_DOES_NOT_exist',999);
154
155 return already_exists; -- ie. zero.
156
157 end result_rule_exists;
158
159
160 --
161 -- Check for existence of "standard" Status Processing Rule - ie. proc rule where
162 -- assignment status type is null. This function returns the existing spr id, and the
163 -- formula id on that spr (or null if none).
164 --
165 function spr_exists ( p_ele_id in number,
166 p_ff_id out number,
167 p_val_date in date,
168 p_bg_id in number,
169 p_eff_date in date default sysdate) return number is
170
171 already_exists number := 0;
172
173 begin
174
175 hr_utility.set_location('hr_template_existence.spr_exists',10);
176
177 select spr.status_processing_rule_id,
178 spr.formula_id
179 into already_exists,
180 p_ff_id
181 from pay_status_processing_rules_f spr
182 where spr.element_type_id = p_ele_id
183 and spr.assignment_status_type_id IS NULL
184 and spr.business_group_id = p_bg_id
185 and p_val_date between spr.effective_start_date
186 and spr.effective_end_date;
187
188 hr_utility.set_location('hr_template_existence.spr_exists',20);
189
190 return already_exists;
191
192 exception when NO_DATA_FOUND then
193
194 hr_utility.set_location('hr_template_existence.spr_DOES_NOT_exist',999);
195
196 p_ff_id := NULL;
197 return already_exists; -- ie. zero.
198
199 end spr_exists;
200
201
202
203 function ele_ff_exists ( p_ele_name in varchar2,
204 p_bg_id in number,
205 p_ff_name out varchar2,
206 p_ff_text out varchar2,
207 p_eff_date in date default sysdate) return number is
208
209 already_exists number := 0;
210
211 begin
212
213 hr_utility.set_location('hr_template_existence.ele_ff_exists',10);
214
215 select ff.formula_id,
216 ff.formula_name,
217 ff.formula_text
218 into already_exists,
219 p_ff_name,
220 p_ff_text
221 from pay_element_types_f pet,
222 pay_status_processing_rules_f spr,
223 ff_formulas_f ff
224 where upper(pet.element_name) = upper(p_ele_name)
225 and pet.business_group_id = p_bg_id
226 and p_eff_date between pet.effective_start_date
227 and pet.effective_end_date
228 and spr.element_type_id = pet.element_type_id
229 and spr.assignment_status_type_id is null
230 and spr.business_group_id = p_bg_id
231 and p_eff_date between spr.effective_start_date
232 and spr.effective_end_date
233 and ff.formula_id = spr.formula_id
234 and ff.business_group_id = p_bg_id
235 and p_eff_date between ff.effective_start_date
236 and ff.effective_end_date;
237
238 hr_utility.set_location('hr_template_existence.ele_ff_exists',20);
239
240 return already_exists;
241
242 exception when NO_DATA_FOUND then
243
244 hr_utility.set_location('hr_template_existence.ele_ff_DOES_NOT_exist',999);
245 return already_exists; -- ie. zero.
246
247 end ele_ff_exists;
248
249
250
251 function defined_bal_exists ( p_bal_id in number,
252 p_dim_id in number,
253 p_bg_id in number,
254 p_eff_date in date default sysdate) return number is
255
256 already_exists number := 0;
257
258 begin
259
260 hr_utility.set_location('hr_template_existence.defined_bal_exists',10);
261
262 SELECT pdb.defined_balance_id
263 INTO already_exists
264 FROM pay_defined_balances pdb
265 WHERE pdb.balance_type_id = p_bal_id
266 AND pdb.balance_dimension_id = p_dim_id
267 AND pdb.business_group_id = p_bg_id;
268
269 hr_utility.set_location('hr_template_existence.defined_bal_exists',20);
270
271 return already_exists;
272
273 exception when NO_DATA_FOUND then
274
275 hr_utility.set_location('hr_template_existence.defined_bal_DOES_NOT_exist',999);
276
277 return already_exists; -- ie. zero.
278
279 end defined_bal_exists;
280
281
282
283 function iv_name_exists ( p_ele_id in number,
284 p_iv_name in varchar2,
285 p_bg_id in number,
286 p_eff_date in date default sysdate) return number is
287
288 already_exists number := 0;
289
290 begin
291
292 hr_utility.set_location('hr_template_existence.iv_name_exists',10);
293
294 SELECT piv.input_value_id
295 INTO already_exists
296 FROM pay_input_values_f piv
297 WHERE piv.name = p_iv_name
298 AND piv.element_type_id = p_ele_id
299 AND piv.business_group_id = p_bg_id
300 AND p_eff_date between piv.effective_start_date and piv.effective_end_date;
301
302 hr_utility.set_location('hr_template_existence.iv_name_exists',20);
303
304 return already_exists;
305
306 exception when NO_DATA_FOUND then
307
308 hr_utility.set_location('hr_template_existence.iv_name_DOES_NOT_exist',999);
309
310 return already_exists; -- ie. zero.
311
312 end iv_name_exists;
313
314
315
316
317
318 function ele_exists ( p_ele_name in varchar2,
319 p_bg_id in number,
320 p_eff_date in date default sysdate) return number is
321
322 already_exists number := 0;
323
324 begin
325
326 hr_utility.set_location('hr_template_existence.ele_exists',10);
327
328 SELECT pet.element_type_id
329 INTO already_exists
330 FROM pay_element_types_f pet
331 WHERE pet.element_name = p_ele_name
332 AND pet.business_group_id = p_bg_id
333 AND p_eff_date between pet.effective_start_date and pet.effective_end_date;
334
335 hr_utility.set_location('hr_template_existence.ele_exists',20);
336
337 return already_exists;
338
339 exception when NO_DATA_FOUND then
340
341 hr_utility.set_location('hr_template_existence.ele_name_DOES_NOT_exist',999);
342
343 return already_exists; -- ie. zero.
344
345 end ele_exists;
346
347
348
349 function bal_exists ( p_bal_name in varchar2,
350 p_bg_id in number,
351 p_eff_date in date default sysdate) return number is
352
353 -- Note: Pass in bal name as mixed case; finds exact match only.
354
355 already_exists number := 0;
356
357 begin
358
359 hr_utility.set_location('hr_template_existence.bal_exists',10);
360
361 SELECT pbt.balance_type_id
362 INTO already_exists
363 FROM pay_balance_types pbt
364 WHERE pbt.balance_name = p_bal_name
365 AND pbt.business_group_id = p_bg_id;
366
367 hr_utility.set_location('hr_template_existence.bal_exists',20);
368
369 return already_exists;
370
371 exception when NO_DATA_FOUND then
372
373 hr_utility.set_location('hr_template_existence.bal_DOES_NOT_exist',999);
374
375 return already_exists; -- ie. zero.
376
377 end bal_exists;
378
379
380 function upg_link_iv_exists (
381 p_element_link_id IN NUMBER,
382 p_input_val_id IN NUMBER,
383 p_eff_date in date default sysdate) RETURN NUMBER IS
384
385 count_exists number;
386
387 begin
388
389 select count(0)
390 into count_exists
391 from pay_link_input_values_f liv
392 where liv.element_link_id = p_element_link_id
393 and liv.input_value_id = p_input_val_id;
394
395 return count_exists;
396
397 end upg_link_iv_exists;
398
399
400 function upg_entry_val_exists (
401 p_element_entry_id IN NUMBER,
402 p_input_val_id IN NUMBER,
403 p_eff_date in date default sysdate) RETURN NUMBER IS
404
405 entry_val_exists number := 0;
406
407 begin
408
409 select distinct pev.element_entry_value_id
410 into entry_val_exists
411 from pay_element_entry_values_f pev
412 where pev.element_entry_id = p_element_entry_id
413 and pev.input_value_id = p_input_val_id;
414
415 return entry_val_exists;
416
417 exception when no_data_found then
418
419 entry_val_exists := 0;
420 return entry_val_exists;
421
422 end upg_entry_val_exists;
423
424
425 function upg_result_val_exists (
426 p_run_result_id IN NUMBER,
427 p_input_val_id IN NUMBER,
428 p_eff_date in date default sysdate) RETURN NUMBER IS
429
430 count_exists number;
431
432 begin
433
434 select count(0)
435 into count_exists
436 from pay_run_result_values rrv
437 where rrv.run_result_id = p_run_result_id
438 and rrv.input_value_id = p_input_val_id;
439
440 return count_exists;
441
442 end upg_result_val_exists;
443
444
445 END hr_template_existence;
446
[Home] [Help]