[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYWSMEE2_PKG
Source
1 package body PAY_PAYWSMEE2_PKG as
2 /* $Header: pywsmee2.pkb 115.5 2003/02/13 17:17:30 swinton ship $ */
3 --
4 --------------------------------------------------------------------------------
5 g_coverage constant pay_input_values_f.name%type := 'Coverage';
6 g_ee_contributions constant pay_input_values_f.name%type := 'EE Contr';
7 g_er_contributions constant pay_input_values_f.name%type := 'ER Contr';
8 --------------------------------------------------------------------------------
9
10 procedure lock_row (
11 --
12 p_effective_date date,
13 p_element_entry_id number,
14 p_effective_start_date date,
15 p_effective_end_date date,
16 p_cost_allocation_keyflex_id number,
17 p_assignment_id number,
18 p_element_link_id number,
19 p_original_entry_id number,
20 p_creator_type varchar2,
21 p_entry_type varchar2,
22 p_comment_id number,
23 p_creator_id number,
24 p_reason varchar2,
25 p_target_entry_id number,
26 p_date_earned date,
27 p_personal_payment_method_id number,
28 p_attribute_category varchar2,
29 p_attribute1 varchar2,
30 p_attribute2 varchar2,
31 p_attribute3 varchar2,
32 p_attribute4 varchar2,
33 p_attribute5 varchar2,
34 p_attribute6 varchar2,
35 p_attribute7 varchar2,
36 p_attribute8 varchar2,
37 p_attribute9 varchar2,
38 p_attribute10 varchar2,
39 p_attribute11 varchar2,
40 p_attribute12 varchar2,
41 p_attribute13 varchar2,
42 p_attribute14 varchar2,
43 p_attribute15 varchar2,
44 p_attribute16 varchar2,
45 p_attribute17 varchar2,
46 p_attribute18 varchar2,
47 p_attribute19 varchar2,
48 p_attribute20 varchar2
49 -- --
50 ,
51 p_entry_information_category varchar2,
52 p_entry_information1 varchar2,
53 p_entry_information2 varchar2,
54 p_entry_information3 varchar2,
55 p_entry_information4 varchar2,
56 p_entry_information5 varchar2,
57 p_entry_information6 varchar2,
58 p_entry_information7 varchar2,
59 p_entry_information8 varchar2,
60 p_entry_information9 varchar2,
61 p_entry_information10 varchar2,
62 p_entry_information11 varchar2,
63 p_entry_information12 varchar2,
64 p_entry_information13 varchar2,
65 p_entry_information14 varchar2,
66 p_entry_information15 varchar2,
67 p_entry_information16 varchar2,
68 p_entry_information17 varchar2,
69 p_entry_information18 varchar2,
70 p_entry_information19 varchar2,
71 p_entry_information20 varchar2,
72 p_entry_information21 varchar2,
73 p_entry_information22 varchar2,
74 p_entry_information23 varchar2,
75 p_entry_information24 varchar2,
76 p_entry_information25 varchar2,
77 p_entry_information26 varchar2,
78 p_entry_information27 varchar2,
79 p_entry_information28 varchar2,
80 p_entry_information29 varchar2,
81 p_entry_information30 varchar2
82 ) is
83 --
84 cursor existing_row is
85 select *
86 from pay_element_entries_f
87 where element_entry_id = p_element_entry_id
88 and p_effective_date between effective_start_date and effective_end_date
89 for update of element_entry_id nowait;
90 --
91 locked_row existing_row%rowtype;
92 --
93 begin
94 --
95 open existing_row;
96 fetch existing_row into locked_row;
97 if (existing_row%notfound) then
98 close existing_row;
99 raise no_data_found;
100 else
101 close existing_row;
102 end if;
103 --
104 if (
105 ( locked_row.element_entry_id = p_element_entry_id
106 or ( locked_row.element_entry_id is null and p_element_entry_id is null))
107 and ( (locked_row.effective_start_date = p_effective_start_date)
108 or ( (locked_row.effective_start_date is null)
109 and (p_effective_start_date is null)))
110 and ( (locked_row.effective_end_date = p_effective_end_date)
111 or ( (locked_row.effective_end_date is null)
112 and (p_effective_end_date is null)))
113 and ( (locked_row.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
114 )
115 or ( (locked_row.cost_allocation_keyflex_id is null)
116 and (p_cost_allocation_keyflex_id is null)))
117 and ( (locked_row.assignment_id = p_assignment_id)
118 or ( (locked_row.assignment_id is null)
119 and (p_assignment_id is null)))
120 and ( (locked_row.element_link_id = p_element_link_id)
121 or ( (locked_row.element_link_id is null)
122 and (p_element_link_id is null)))
123 and ( (locked_row.original_entry_id = p_original_entry_id)
124 or ( (locked_row.original_entry_id is null)
125 and (p_original_entry_id is null)))
126 and ( (locked_row.creator_type = p_creator_type)
127 or ( (locked_row.creator_type is null)
128 and (p_creator_type is null)))
129 and ( (locked_row.entry_type = p_entry_type)
130 or ( (locked_row.entry_type is null)
131 and (p_entry_type is null)))
132 and ( (locked_row.comment_id = p_comment_id)
133 or ( (locked_row.comment_id is null)
134 and (p_comment_id is null)))
135 and ( (locked_row.creator_id = p_creator_id)
136 or ( (locked_row.creator_id is null)
137 and (p_creator_id is null)))
138 and ( (locked_row.reason = p_reason)
139 or ( (locked_row.reason is null)
140 and (p_reason is null)))
141 and ( (locked_row.target_entry_id = p_target_entry_id)
142 or ( (locked_row.target_entry_id is null)
143 and (p_target_entry_id is null)))
144 and ( (locked_row.attribute_category = p_attribute_category)
145 or ( (locked_row.attribute_category is null)
146 and (p_attribute_category is null)))
147 and ( (locked_row.attribute1 = p_attribute1)
148 or ( (locked_row.attribute1 is null)
149 and (p_attribute1 is null)))
150 and ( (locked_row.attribute2 = p_attribute2)
151 or ( (locked_row.attribute2 is null)
152 and (p_attribute2 is null)))
153 and ( (locked_row.attribute3 = p_attribute3)
154 or ( (locked_row.attribute3 is null)
155 and (p_attribute3 is null)))
156 and ( (locked_row.attribute4 = p_attribute4)
157 or ( (locked_row.attribute4 is null)
158 and (p_attribute4 is null)))
159 and ( (locked_row.attribute5 = p_attribute5)
160 or ( (locked_row.attribute5 is null)
161 and (p_attribute5 is null)))
162 and ( (locked_row.attribute6 = p_attribute6)
163 or ( (locked_row.attribute6 is null)
164 and (p_attribute6 is null)))
165 and ( (locked_row.attribute7 = p_attribute7)
166 or ( (locked_row.attribute7 is null)
167 and (p_attribute7 is null)))
168 and ( (locked_row.attribute8 = p_attribute8)
169 or ( (locked_row.attribute8 is null)
170 and (p_attribute8 is null)))
171 and ( (locked_row.attribute9 = p_attribute9)
172 or ( (locked_row.attribute9 is null)
173 and (p_attribute9 is null)))
174 and ( (locked_row.attribute10 = p_attribute10)
175 or ( (locked_row.attribute10 is null)
176 and (p_attribute10 is null)))
177 and ( (locked_row.attribute11 = p_attribute11)
178 or ( (locked_row.attribute11 is null)
179 and (p_attribute11 is null)))
180 and ( (locked_row.attribute12 = p_attribute12)
181 or ( (locked_row.attribute12 is null)
182 and (p_attribute12 is null)))
183 and ( (locked_row.attribute13 = p_attribute13)
184 or ( (locked_row.attribute13 is null)
185 and (p_attribute13 is null)))
186 and ( (locked_row.attribute14 = p_attribute14)
187 or ( (locked_row.attribute14 is null)
188 and (p_attribute14 is null)))
189 and ( (locked_row.attribute15 = p_attribute15)
190 or ( (locked_row.attribute15 is null)
191 and (p_attribute15 is null)))
192 and ( (locked_row.attribute16 = p_attribute16)
193 or ( (locked_row.attribute16 is null)
194 and (p_attribute16 is null)))
195 and ( (locked_row.attribute17 = p_attribute17)
196 or ( (locked_row.attribute17 is null)
197 and (p_attribute17 is null)))
198 and ( (locked_row.attribute18 = p_attribute18)
199 or ( (locked_row.attribute18 is null)
200 and (p_attribute18 is null)))
201 and ( (locked_row.attribute19 = p_attribute19)
202 or ( (locked_row.attribute19 is null)
203 and (p_attribute19 is null)))
204 and ( locked_row.attribute20 = p_attribute20
205 or ( locked_row.attribute20 is null and p_attribute20 is null))
206 -- begin --
207 and ( locked_row.entry_information_category = p_entry_information_category
208 or ( locked_row.entry_information_category is null and p_entry_information_category is null))
209 and ( locked_row.entry_information1 = p_entry_information1
210 or ( locked_row.entry_information1 is null and p_entry_information1 is null))
211 and ( locked_row.entry_information2 = p_entry_information2
212 or ( locked_row.entry_information2 is null and p_entry_information2 is null))
213 and ( locked_row.entry_information3 = p_entry_information3
214 or ( locked_row.entry_information3 is null and p_entry_information3 is null))
215 and ( locked_row.entry_information4 = p_entry_information4
216 or ( locked_row.entry_information4 is null and p_entry_information4 is null))
217 and ( locked_row.entry_information5 = p_entry_information5
218 or ( locked_row.entry_information5 is null and p_entry_information5 is null))
219 and ( locked_row.entry_information6 = p_entry_information6
220 or ( locked_row.entry_information6 is null and p_entry_information6 is null))
221 and ( locked_row.entry_information7 = p_entry_information7
222 or ( locked_row.entry_information7 is null and p_entry_information7 is null))
223 and ( locked_row.entry_information8 = p_entry_information8
224 or ( locked_row.entry_information8 is null and p_entry_information8 is null))
225 and ( locked_row.entry_information9 = p_entry_information9
226 or ( locked_row.entry_information9 is null and p_entry_information9 is null))
227 and ( locked_row.entry_information10 = p_entry_information10
228 or ( locked_row.entry_information10 is null and p_entry_information10 is null))
229 and ( locked_row.entry_information11 = p_entry_information11
230 or ( locked_row.entry_information11 is null and p_entry_information11 is null))
231 and ( locked_row.entry_information12 = p_entry_information12
232 or ( locked_row.entry_information12 is null and p_entry_information12 is null))
233 and ( locked_row.entry_information13 = p_entry_information13
234 or ( locked_row.entry_information13 is null and p_entry_information13 is null))
235 and ( locked_row.entry_information14 = p_entry_information14
236 or ( locked_row.entry_information14 is null and p_entry_information14 is null))
237 and ( locked_row.entry_information15 = p_entry_information15
238 or ( locked_row.entry_information15 is null and p_entry_information15 is null))
239 and ( locked_row.entry_information16 = p_entry_information16
240 or ( locked_row.entry_information16 is null and p_entry_information16 is null))
241 and ( locked_row.entry_information17 = p_entry_information17
242 or ( locked_row.entry_information17 is null and p_entry_information17 is null))
243 and ( locked_row.entry_information18 = p_entry_information18
244 or ( locked_row.entry_information18 is null and p_entry_information18 is null))
245 and ( locked_row.entry_information19 = p_entry_information19
246 or ( locked_row.entry_information19 is null and p_entry_information19 is null))
247 and ( locked_row.entry_information20 = p_entry_information20
248 or ( locked_row.entry_information20 is null and p_entry_information20 is null))
249 and ( locked_row.entry_information21 = p_entry_information21
250 or ( locked_row.entry_information21 is null and p_entry_information21 is null))
251 and ( locked_row.entry_information22 = p_entry_information22
252 or ( locked_row.entry_information22 is null and p_entry_information22 is null))
253 and ( locked_row.entry_information23 = p_entry_information23
254 or ( locked_row.entry_information23 is null and p_entry_information23 is null))
255 and ( locked_row.entry_information24 = p_entry_information24
256 or ( locked_row.entry_information24 is null and p_entry_information24 is null))
257 and ( locked_row.entry_information25 = p_entry_information25
258 or ( locked_row.entry_information25 is null and p_entry_information25 is null))
259 and ( locked_row.entry_information26 = p_entry_information26
260 or ( locked_row.entry_information26 is null and p_entry_information26 is null))
261 and ( locked_row.entry_information27 = p_entry_information27
262 or ( locked_row.entry_information27 is null and p_entry_information27 is null))
263 and ( locked_row.entry_information28 = p_entry_information28
264 or ( locked_row.entry_information28 is null and p_entry_information28 is null))
265 and ( locked_row.entry_information29 = p_entry_information29
266 or ( locked_row.entry_information29 is null and p_entry_information29 is null))
267 and ( locked_row.entry_information30 = p_entry_information30
268 or ( locked_row.entry_information30 is null and p_entry_information30 is null))
269 -- end --
270 and ( locked_row.date_earned = p_date_earned
271 or ( locked_row.date_earned is null and p_date_earned is null))
272 and ( locked_row.personal_payment_method_id = p_personal_payment_method_id
273 or ( locked_row.personal_payment_method_id is null and p_personal_payment_method_id is null))
274 ) then
275 return;
276 else
277 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
278 app_exception.raise_exception;
279 end if;
280 --
281 end lock_row;
282
283
284 procedure GET_INPUT_VALUE_DETAILS (
285 --
286 -- Returns the input value details for the element selected by an LOV
287 --
288 p_element_link_id number,
289 p_element_type_id number,
290 p_effective_date date,
291 p_input_currency_code varchar2,
292 p_contributions_used varchar2,
293 p_input_value_id1 in out nocopy number,
294 p_input_value_id2 in out nocopy number,
295 p_input_value_id3 in out nocopy number,
296 p_input_value_id4 in out nocopy number,
297 p_input_value_id5 in out nocopy number,
298 p_input_value_id6 in out nocopy number,
299 p_input_value_id7 in out nocopy number,
300 p_input_value_id8 in out nocopy number,
301 p_input_value_id9 in out nocopy number,
302 p_input_value_id10 in out nocopy number,
306 p_input_value_id14 in out nocopy number,
303 p_input_value_id11 in out nocopy number,
304 p_input_value_id12 in out nocopy number,
305 p_input_value_id13 in out nocopy number,
307 p_input_value_id15 in out nocopy number,
308 p_name1 in out nocopy varchar2,
309 p_name2 in out nocopy varchar2,
310 p_name3 in out nocopy varchar2,
311 p_name4 in out nocopy varchar2,
312 p_name5 in out nocopy varchar2,
313 p_name6 in out nocopy varchar2,
314 p_name7 in out nocopy varchar2,
315 p_name8 in out nocopy varchar2,
316 p_name9 in out nocopy varchar2,
317 p_name10 in out nocopy varchar2,
318 p_name11 in out nocopy varchar2,
319 p_name12 in out nocopy varchar2,
320 p_name13 in out nocopy varchar2,
321 p_name14 in out nocopy varchar2,
322 p_name15 in out nocopy varchar2,
323 p_uom1 in out nocopy varchar2,
324 p_uom2 in out nocopy varchar2,
325 p_uom3 in out nocopy varchar2,
326 p_uom4 in out nocopy varchar2,
327 p_uom5 in out nocopy varchar2,
328 p_uom6 in out nocopy varchar2,
329 p_uom7 in out nocopy varchar2,
330 p_uom8 in out nocopy varchar2,
331 p_uom9 in out nocopy varchar2,
332 p_uom10 in out nocopy varchar2,
333 p_uom11 in out nocopy varchar2,
334 p_uom12 in out nocopy varchar2,
335 p_uom13 in out nocopy varchar2,
336 p_uom14 in out nocopy varchar2,
337 p_uom15 in out nocopy varchar2,
338 p_hot_default_flag1 in out nocopy varchar2,
339 p_hot_default_flag2 in out nocopy varchar2,
340 p_hot_default_flag3 in out nocopy varchar2,
341 p_hot_default_flag4 in out nocopy varchar2,
342 p_hot_default_flag5 in out nocopy varchar2,
343 p_hot_default_flag6 in out nocopy varchar2,
344 p_hot_default_flag7 in out nocopy varchar2,
345 p_hot_default_flag8 in out nocopy varchar2,
346 p_hot_default_flag9 in out nocopy varchar2,
347 p_hot_default_flag10 in out nocopy varchar2,
348 p_hot_default_flag11 in out nocopy varchar2,
349 p_hot_default_flag12 in out nocopy varchar2,
350 p_hot_default_flag13 in out nocopy varchar2,
351 p_hot_default_flag14 in out nocopy varchar2,
352 p_hot_default_flag15 in out nocopy varchar2,
353 p_mandatory_flag1 in out nocopy varchar2,
354 p_mandatory_flag2 in out nocopy varchar2,
355 p_mandatory_flag3 in out nocopy varchar2,
356 p_mandatory_flag4 in out nocopy varchar2,
357 p_mandatory_flag5 in out nocopy varchar2,
358 p_mandatory_flag6 in out nocopy varchar2,
359 p_mandatory_flag7 in out nocopy varchar2,
360 p_mandatory_flag8 in out nocopy varchar2,
361 p_mandatory_flag9 in out nocopy varchar2,
362 p_mandatory_flag10 in out nocopy varchar2,
363 p_mandatory_flag11 in out nocopy varchar2,
364 p_mandatory_flag12 in out nocopy varchar2,
365 p_mandatory_flag13 in out nocopy varchar2,
366 p_mandatory_flag14 in out nocopy varchar2,
367 p_mandatory_flag15 in out nocopy varchar2,
368 p_formula_id1 in out nocopy number,
369 p_formula_id2 in out nocopy number,
370 p_formula_id3 in out nocopy number,
371 p_formula_id4 in out nocopy number,
372 p_formula_id5 in out nocopy number,
373 p_formula_id6 in out nocopy number,
374 p_formula_id7 in out nocopy number,
375 p_formula_id8 in out nocopy number,
376 p_formula_id9 in out nocopy number,
377 p_formula_id10 in out nocopy number,
378 p_formula_id11 in out nocopy number,
379 p_formula_id12 in out nocopy number,
380 p_formula_id13 in out nocopy number,
381 p_formula_id14 in out nocopy number,
382 p_formula_id15 in out nocopy number,
383 p_lookup_type1 in out nocopy varchar2,
384 p_lookup_type2 in out nocopy varchar2,
385 p_lookup_type3 in out nocopy varchar2,
386 p_lookup_type4 in out nocopy varchar2,
387 p_lookup_type5 in out nocopy varchar2,
388 p_lookup_type6 in out nocopy varchar2,
389 p_lookup_type7 in out nocopy varchar2,
390 p_lookup_type8 in out nocopy varchar2,
391 p_lookup_type9 in out nocopy varchar2,
392 p_lookup_type10 in out nocopy varchar2,
393 p_lookup_type11 in out nocopy varchar2,
394 p_lookup_type12 in out nocopy varchar2,
395 p_lookup_type13 in out nocopy varchar2,
396 p_lookup_type14 in out nocopy varchar2,
397 p_lookup_type15 in out nocopy varchar2,
398 p_value_set_id1 in out nocopy number,
399 p_value_set_id2 in out nocopy number,
400 p_value_set_id3 in out nocopy number,
401 p_value_set_id4 in out nocopy number,
402 p_value_set_id5 in out nocopy number,
403 p_value_set_id6 in out nocopy number,
404 p_value_set_id7 in out nocopy number,
405 p_value_set_id8 in out nocopy number,
406 p_value_set_id9 in out nocopy number,
407 p_value_set_id10 in out nocopy number,
408 p_value_set_id11 in out nocopy number,
409 p_value_set_id12 in out nocopy number,
410 p_value_set_id13 in out nocopy number,
411 p_value_set_id14 in out nocopy number,
412 p_value_set_id15 in out nocopy number,
413 p_min_value1 in out nocopy varchar2,
414 p_min_value2 in out nocopy varchar2,
415 p_min_value3 in out nocopy varchar2,
416 p_min_value4 in out nocopy varchar2,
417 p_min_value5 in out nocopy varchar2,
418 p_min_value6 in out nocopy varchar2,
419 p_min_value7 in out nocopy varchar2,
420 p_min_value8 in out nocopy varchar2,
421 p_min_value9 in out nocopy varchar2,
422 p_min_value10 in out nocopy varchar2,
423 p_min_value11 in out nocopy varchar2,
427 p_min_value15 in out nocopy varchar2,
424 p_min_value12 in out nocopy varchar2,
425 p_min_value13 in out nocopy varchar2,
426 p_min_value14 in out nocopy varchar2,
428 p_max_value1 in out nocopy varchar2,
429 p_max_value2 in out nocopy varchar2,
430 p_max_value3 in out nocopy varchar2,
431 p_max_value4 in out nocopy varchar2,
432 p_max_value5 in out nocopy varchar2,
433 p_max_value6 in out nocopy varchar2,
434 p_max_value7 in out nocopy varchar2,
435 p_max_value8 in out nocopy varchar2,
436 p_max_value9 in out nocopy varchar2,
437 p_max_value10 in out nocopy varchar2,
438 p_max_value11 in out nocopy varchar2,
439 p_max_value12 in out nocopy varchar2,
440 p_max_value13 in out nocopy varchar2,
441 p_max_value14 in out nocopy varchar2,
442 p_max_value15 in out nocopy varchar2,
443 p_default_value1 in out nocopy varchar2,
444 p_default_value2 in out nocopy varchar2,
445 p_default_value3 in out nocopy varchar2,
446 p_default_value4 in out nocopy varchar2,
447 p_default_value5 in out nocopy varchar2,
448 p_default_value6 in out nocopy varchar2,
449 p_default_value7 in out nocopy varchar2,
450 p_default_value8 in out nocopy varchar2,
451 p_default_value9 in out nocopy varchar2,
452 p_default_value10 in out nocopy varchar2,
453 p_default_value11 in out nocopy varchar2,
454 p_default_value12 in out nocopy varchar2,
455 p_default_value13 in out nocopy varchar2,
456 p_default_value14 in out nocopy varchar2,
457 p_default_value15 in out nocopy varchar2,
458 p_warning_or_error1 in out nocopy varchar2,
459 p_warning_or_error2 in out nocopy varchar2,
460 p_warning_or_error3 in out nocopy varchar2,
461 p_warning_or_error4 in out nocopy varchar2,
462 p_warning_or_error5 in out nocopy varchar2,
463 p_warning_or_error6 in out nocopy varchar2,
464 p_warning_or_error7 in out nocopy varchar2,
465 p_warning_or_error8 in out nocopy varchar2,
466 p_warning_or_error9 in out nocopy varchar2,
467 p_warning_or_error10 in out nocopy varchar2,
468 p_warning_or_error11 in out nocopy varchar2,
469 p_warning_or_error12 in out nocopy varchar2,
470 p_warning_or_error13 in out nocopy varchar2,
471 p_warning_or_error14 in out nocopy varchar2,
472 p_warning_or_error15 in out nocopy varchar2) is
473 --
474 -- If the element is a type A benefit plan and it has only
475 -- one coverage type, then we can help the user by defaulting
476 -- the coverage and contribution input values
477 --
478 cursor csr_default_coverage is
479 --
480 select coverage.meaning,
481 benefit.employee_contribution,
482 benefit.employer_contribution
483 --
484 from ben_benefit_contributions_f BENEFIT,
485 hr_lookups COVERAGE
486 --
487 where coverage.lookup_type = 'US_BENEFIT_COVERAGE'
488 and coverage.lookup_code = benefit.coverage_type
489 and benefit.element_type_id = p_element_type_id
490 and p_effective_date between benefit.effective_start_date
491 and benefit.effective_end_date
492 and 1 = (select count (*)
493 from ben_benefit_contributions_f BENEFIT2
494 where benefit2.element_type_id = p_element_type_id
495 and p_effective_date between benefit2.effective_start_date
496 and benefit2.effective_end_date)
497 ;
498 --
499 cursor SET_OF_INPUT_VALUES is
500 --
501 select type.input_value_id,
502 type_tl.name,
503 type.uom,
504 type.lookup_type,
505 type.value_set_id,
506 type.formula_id,
507 decode (type.hot_default_flag,
508 'N',link.warning_or_error,
509 nvl (link.warning_or_error,
510 type.warning_or_error)) WARNING_OR_ERROR,
511 type.mandatory_flag,
512 decode (p_contributions_used,
513 'Y',decode (type.name,
514 g_er_contributions, 'Y',
515 g_ee_contributions, 'Y',
516 type.hot_default_flag),
517 type.hot_default_flag) HOT_DEFAULT_FLAG,
518 decode(type.hot_default_flag,'N',link.min_value,
519 nvl(link.min_value,type.min_value)) MIN_VALUE,
520 decode(type.hot_default_flag,'N',link.max_value,
521 nvl(link.max_value,type.max_value)) MAX_VALUE,
522 pay_paywsmee_pkg.formatted_default ( link.default_value,
523 type.default_value,
524 type.uom,
525 type.hot_default_flag,
526 p_contributions_used,
527 type.name,
528 p_input_currency_code,
529 type.lookup_type,
530 type.value_set_id) DEFAULT_VALUE
531 --
532 from pay_input_values_f TYPE,
533 pay_input_values_f_tl TYPE_TL,
534 pay_link_input_values_f LINK
535 --
536 where type_tl.input_value_id = type.input_value_id
537 and userenv('LANG') = type_tl.language
538 and p_effective_date between type.effective_start_date
539 and type.effective_end_date
540 and p_effective_date between link.effective_start_date
541 and link.effective_end_date
542 and p_element_link_id = link.element_link_id
543 and type.input_value_id = link.input_value_id
544 order by type.display_sequence, type.name;
545 --
546 input_value_number integer;
547 v_coverage varchar2 (60);
548 v_EE_contr varchar2 (60);
549 v_ER_contr varchar2 (60);
550 --
551 begin
552 --
553 -- First, nullify all the entry values to ensure that we overwrite any
554 -- previous fetches
555 --
559 p_input_value_id4 := null;
556 p_input_value_id1 := null;
557 p_input_value_id2 := null;
558 p_input_value_id3 := null;
560 p_input_value_id5 := null;
561 p_input_value_id6 := null;
562 p_input_value_id7 := null;
563 p_input_value_id8 := null;
564 p_input_value_id9 := null;
565 p_input_value_id10 := null;
566 p_input_value_id11 := null;
567 p_input_value_id12 := null;
568 p_input_value_id13 := null;
569 p_input_value_id14 := null;
570 p_input_value_id15 := null;
571
572 --
573 p_name1 := null;
574 p_name2 := null;
575 p_name3 := null;
576 p_name4 := null;
577 p_name5 := null;
578 p_name6 := null;
579 p_name7 := null;
580 p_name8 := null;
581 p_name9 := null;
582 p_name10 := null;
583 p_name11 := null;
584 p_name12 := null;
585 p_name13 := null;
586 p_name14 := null;
587 p_name15 := null;
588
589 --
590 p_uom1 := null;
591 p_uom2 := null;
592 p_uom3 := null;
593 p_uom4 := null;
594 p_uom5 := null;
595 p_uom6 := null;
596 p_uom7 := null;
597 p_uom8 := null;
598 p_uom9 := null;
599 p_uom10 := null;
600 p_uom11 := null;
601 p_uom12 := null;
602 p_uom13 := null;
603 p_uom14 := null;
604 p_uom15 := null;
605
606 --
607 p_hot_default_flag1 := null;
608 p_hot_default_flag2 := null;
609 p_hot_default_flag3 := null;
610 p_hot_default_flag4 := null;
611 p_hot_default_flag5 := null;
612 p_hot_default_flag6 := null;
613 p_hot_default_flag7 := null;
614 p_hot_default_flag8 := null;
615 p_hot_default_flag9 := null;
616 p_hot_default_flag10 := null;
617 p_hot_default_flag11 := null;
618 p_hot_default_flag12 := null;
619 p_hot_default_flag13 := null;
620 p_hot_default_flag14 := null;
621 p_hot_default_flag15 := null;
622
623 --
624 p_mandatory_flag1 := null;
625 p_mandatory_flag2 := null;
626 p_mandatory_flag3 := null;
627 p_mandatory_flag4 := null;
628 p_mandatory_flag5 := null;
629 p_mandatory_flag6 := null;
630 p_mandatory_flag7 := null;
631 p_mandatory_flag8 := null;
632 p_mandatory_flag9 := null;
633 p_mandatory_flag10 := null;
634 p_mandatory_flag11 := null;
635 p_mandatory_flag12 := null;
636 p_mandatory_flag13 := null;
637 p_mandatory_flag14 := null;
638 p_mandatory_flag15 := null;
639
640 --
641 p_formula_id1 := null;
642 p_formula_id2 := null;
643 p_formula_id3 := null;
644 p_formula_id4 := null;
645 p_formula_id5 := null;
646 p_formula_id6 := null;
647 p_formula_id7 := null;
648 p_formula_id8 := null;
649 p_formula_id9 := null;
650 p_formula_id10 := null;
651 p_formula_id11 := null;
652 p_formula_id12 := null;
653 p_formula_id13 := null;
654 p_formula_id14 := null;
655 p_formula_id15 := null;
656
657 --
658 p_lookup_type1 := null;
659 p_lookup_type2 := null;
660 p_lookup_type3 := null;
661 p_lookup_type4 := null;
662 p_lookup_type5 := null;
663 p_lookup_type6 := null;
664 p_lookup_type7 := null;
665 p_lookup_type8 := null;
666 p_lookup_type9 := null;
667 p_lookup_type10 := null;
668 p_lookup_type11 := null;
669 p_lookup_type12 := null;
670 p_lookup_type13 := null;
671 p_lookup_type14 := null;
672 p_lookup_type15 := null;
673
674 --
675 p_value_set_id1 := null;
676 p_value_set_id2 := null;
677 p_value_set_id3 := null;
678 p_value_set_id4 := null;
679 p_value_set_id5 := null;
680 p_value_set_id6 := null;
681 p_value_set_id7 := null;
682 p_value_set_id8 := null;
683 p_value_set_id9 := null;
684 p_value_set_id10 := null;
685 p_value_set_id11 := null;
686 p_value_set_id12 := null;
687 p_value_set_id13 := null;
688 p_value_set_id14 := null;
689 p_value_set_id15 := null;
690
691 --
692 p_min_value1 := null;
693 p_min_value2 := null;
694 p_min_value3 := null;
695 p_min_value4 := null;
696 p_min_value5 := null;
697 p_min_value6 := null;
698 p_min_value7 := null;
699 p_min_value8 := null;
700 p_min_value9 := null;
701 p_min_value10 := null;
702 p_min_value11 := null;
703 p_min_value12 := null;
704 p_min_value13 := null;
705 p_min_value14 := null;
706 p_min_value15 := null;
707
708 --
709 p_max_value1 := null;
710 p_max_value2 := null;
711 p_max_value3 := null;
712 p_max_value4 := null;
713 p_max_value5 := null;
714 p_max_value6 := null;
715 p_max_value7 := null;
716 p_max_value8 := null;
717 p_max_value9 := null;
718 p_max_value10 := null;
719 p_max_value11 := null;
720 p_max_value12 := null;
721 p_max_value13 := null;
722 p_max_value14 := null;
723 p_max_value15 := null;
724
725 --
726 p_default_value1 := null;
727 p_default_value2 := null;
728 p_default_value3 := null;
729 p_default_value4 := null;
730 p_default_value5 := null;
731 p_default_value6 := null;
732 p_default_value7 := null;
733 p_default_value8 := null;
734 p_default_value9 := null;
735 p_default_value10 := null;
736 p_default_value11 := null;
737 p_default_value12 := null;
738 p_default_value13 := null;
739 p_default_value14 := null;
740 p_default_value15 := null;
741
742 --
743 p_warning_or_error1 := null;
744 p_warning_or_error2 := null;
745 p_warning_or_error3 := null;
746 p_warning_or_error4 := null;
747 p_warning_or_error5 := null;
748 p_warning_or_error6 := null;
749 p_warning_or_error7 := null;
750 p_warning_or_error8 := null;
751 p_warning_or_error9 := null;
752 p_warning_or_error10 := null;
753 p_warning_or_error11 := null;
754 p_warning_or_error12 := null;
755 p_warning_or_error13 := null;
756 p_warning_or_error14 := null;
757 p_warning_or_error15 := null;
758
759 --
760 -- Fetch all the input values and their properties
761 --
762 for fetched_input_value in set_of_input_values LOOP
763 --
764 input_value_number := set_of_input_values%rowcount; -- loop index flag
765 --
766 -- Now we need to put the input value details into the right parameters
767 -- to pass back to the form; the comments within the action for
768 -- input_value_number = 1 also apply for all the others
769 --
770 -- First, we need to override the type and link level information if
771 -- the element is a type A Benefit Plan
772 --
773 if p_contributions_used = 'Y' then
774 --
775 if fetched_input_value.name = g_coverage then
776 --
777 -- Get the benefit coverage information.
778 -- NB Because we know that the coverage input value will always be
779 -- ordered before the contribution entry values (because we default the
780 -- display sequence and prevent update), we can get the defaults for the
781 -- contributions as soon as we come across the coverage input value.
782 --
783 open csr_default_coverage;
784 fetch csr_default_coverage into v_coverage, v_EE_contr, v_ER_contr;
785 close csr_default_coverage;
786 --
787 -- Default the coverage entry value
788 --
789 fetched_input_value.default_value := v_coverage;
790 --
791 elsif fetched_input_value.name = g_ee_contributions then
792 --
793 -- Default the employee contribution entry value
794 --
795 fetched_input_value.default_value := hr_chkfmt.changeformat (v_EE_contr,
796 fetched_input_value.uom,
797 p_input_currency_code);
798 --
799 -- Format it as if it were a hot default
800 --
801 if fetched_input_value.default_value is not null then
802 fetched_input_value.default_value := '"'||fetched_input_value.default_value||'"';
803 end if;
804 --
805 elsif fetched_input_value.name = g_er_contributions then
806 --
807 -- Default the employer contribution entry value
808 --
809 fetched_input_value.default_value := hr_chkfmt.changeformat (v_ER_contr,
810 fetched_input_value.uom,
811 p_input_currency_code);
812 --
813 --
814 -- Format it as if it were a hot default
815 --
816 if fetched_input_value.default_value is not null then
817 fetched_input_value.default_value := '"'||fetched_input_value.default_value||'"';
818 end if;
819 --
820 end if;
821 --
822 end if;
823 --
824 if input_value_number = 1 then
825 --
826 -- assign the out parameters
827 --
828 p_input_value_id1 := fetched_input_value.input_value_id;
829 p_uom1 := fetched_input_value.uom;
830 p_name1 := fetched_input_value.name;
831 p_hot_default_flag1 := fetched_input_value.hot_default_flag;
832 p_mandatory_flag1 := fetched_input_value.mandatory_flag;
833 p_warning_or_error1 := fetched_input_value.warning_or_error;
834 p_lookup_type1 := fetched_input_value.lookup_type;
835 p_value_set_id1 := fetched_input_value.value_set_id;
836 p_formula_id1 := fetched_input_value.formula_id;
837 p_min_value1 := fetched_input_value.min_value;
838 p_max_value1 := fetched_input_value.max_value;
839 p_default_value1 := fetched_input_value.default_value;
840 --
841 elsif input_value_number =2 then
842 --
843 p_input_value_id2 := fetched_input_value.input_value_id;
844 p_uom2 := fetched_input_value.uom;
845 p_name2 := fetched_input_value.name;
846 p_hot_default_flag2 := fetched_input_value.hot_default_flag;
847 p_mandatory_flag2 := fetched_input_value.mandatory_flag;
848 p_warning_or_error2 := fetched_input_value.warning_or_error;
849 p_lookup_type2 := fetched_input_value.lookup_type;
850 p_value_set_id2 := fetched_input_value.value_set_id;
851 p_formula_id2 := fetched_input_value.formula_id;
852 p_min_value2 := fetched_input_value.min_value;
853 p_max_value2 := fetched_input_value.max_value;
854 p_default_value2 := fetched_input_value.default_value;
855 --
856 elsif input_value_number =3 then
860 p_name3 := fetched_input_value.name;
857 --
858 p_input_value_id3 := fetched_input_value.input_value_id;
859 p_uom3 := fetched_input_value.uom;
861 p_hot_default_flag3 := fetched_input_value.hot_default_flag;
862 p_mandatory_flag3 := fetched_input_value.mandatory_flag;
863 p_warning_or_error3 := fetched_input_value.warning_or_error;
864 p_lookup_type3 := fetched_input_value.lookup_type;
865 p_value_set_id3 := fetched_input_value.value_set_id;
866 p_formula_id3 := fetched_input_value.formula_id;
867 p_min_value3 := fetched_input_value.min_value;
868 p_max_value3 := fetched_input_value.max_value;
869 p_default_value3 := fetched_input_value.default_value;
870 --
871 elsif input_value_number =4 then
872 --
873 p_input_value_id4 := fetched_input_value.input_value_id;
874 p_uom4 := fetched_input_value.uom;
875 p_name4 := fetched_input_value.name;
876 p_hot_default_flag4 := fetched_input_value.hot_default_flag;
877 p_mandatory_flag4 := fetched_input_value.mandatory_flag;
878 p_warning_or_error4 := fetched_input_value.warning_or_error;
879 p_lookup_type4 := fetched_input_value.lookup_type;
880 p_value_set_id4 := fetched_input_value.value_set_id;
881 p_formula_id4 := fetched_input_value.formula_id;
882 p_min_value4 := fetched_input_value.min_value;
883 p_max_value4 := fetched_input_value.max_value;
884 p_default_value4 := fetched_input_value.default_value;
885 --
886 elsif input_value_number =5 then
887 --
888 p_input_value_id5 := fetched_input_value.input_value_id;
889 p_uom5 := fetched_input_value.uom;
890 p_name5 := fetched_input_value.name;
891 p_hot_default_flag5 := fetched_input_value.hot_default_flag;
892 p_mandatory_flag5 := fetched_input_value.mandatory_flag;
893 p_warning_or_error5 := fetched_input_value.warning_or_error;
894 p_lookup_type5 := fetched_input_value.lookup_type;
895 p_value_set_id5 := fetched_input_value.value_set_id;
896 p_formula_id5 := fetched_input_value.formula_id;
897 p_min_value5 := fetched_input_value.min_value;
898 p_max_value5 := fetched_input_value.max_value;
899 p_default_value5 := fetched_input_value.default_value;
900 --
901 elsif input_value_number =6 then
902 --
903 p_input_value_id6 := fetched_input_value.input_value_id;
904 p_uom6 := fetched_input_value.uom;
905 p_name6 := fetched_input_value.name;
906 p_hot_default_flag6 := fetched_input_value.hot_default_flag;
907 p_mandatory_flag6 := fetched_input_value.mandatory_flag;
908 p_warning_or_error6 := fetched_input_value.warning_or_error;
909 p_lookup_type6 := fetched_input_value.lookup_type;
910 p_value_set_id6 := fetched_input_value.value_set_id;
911 p_formula_id6 := fetched_input_value.formula_id;
912 p_min_value6 := fetched_input_value.min_value;
913 p_max_value6 := fetched_input_value.max_value;
914 p_default_value6 := fetched_input_value.default_value;
915 --
916 elsif input_value_number =7 then
917 --
918 p_input_value_id7 := fetched_input_value.input_value_id;
919 p_uom7 := fetched_input_value.uom;
920 p_name7 := fetched_input_value.name;
921 p_hot_default_flag7 := fetched_input_value.hot_default_flag;
922 p_mandatory_flag7 := fetched_input_value.mandatory_flag;
923 p_warning_or_error7 := fetched_input_value.warning_or_error;
927 p_min_value7 := fetched_input_value.min_value;
924 p_lookup_type7 := fetched_input_value.lookup_type;
925 p_value_set_id7 := fetched_input_value.value_set_id;
926 p_formula_id7 := fetched_input_value.formula_id;
928 p_max_value7 := fetched_input_value.max_value;
929 p_default_value7 := fetched_input_value.default_value;
930 --
931 elsif input_value_number =8 then
932 --
933 p_input_value_id8 := fetched_input_value.input_value_id;
934 p_uom8 := fetched_input_value.uom;
935 p_name8 := fetched_input_value.name;
936 p_hot_default_flag8 := fetched_input_value.hot_default_flag;
937 p_mandatory_flag8 := fetched_input_value.mandatory_flag;
938 p_warning_or_error8 := fetched_input_value.warning_or_error;
939 p_lookup_type8 := fetched_input_value.lookup_type;
940 p_value_set_id8 := fetched_input_value.value_set_id;
941 p_formula_id8 := fetched_input_value.formula_id;
942 p_min_value8 := fetched_input_value.min_value;
943 p_max_value8 := fetched_input_value.max_value;
944 p_default_value8 := fetched_input_value.default_value;
945 --
946 elsif input_value_number =9 then
947 --
948 p_input_value_id9 := fetched_input_value.input_value_id;
949 p_uom9 := fetched_input_value.uom;
950 p_name9 := fetched_input_value.name;
951 p_hot_default_flag9 := fetched_input_value.hot_default_flag;
952 p_mandatory_flag9 := fetched_input_value.mandatory_flag;
953 p_warning_or_error9 := fetched_input_value.warning_or_error;
954 p_lookup_type9 := fetched_input_value.lookup_type;
955 p_value_set_id9 := fetched_input_value.value_set_id;
956 p_formula_id9 := fetched_input_value.formula_id;
957 p_min_value9 := fetched_input_value.min_value;
958 p_max_value9 := fetched_input_value.max_value;
959 p_default_value9 := fetched_input_value.default_value;
960 --
961 elsif input_value_number =10 then
962 --
963 p_input_value_id10 := fetched_input_value.input_value_id;
964 p_uom10 := fetched_input_value.uom;
965 p_name10 := fetched_input_value.name;
966 p_hot_default_flag10 := fetched_input_value.hot_default_flag;
967 p_mandatory_flag10 := fetched_input_value.mandatory_flag;
968 p_warning_or_error10 := fetched_input_value.warning_or_error;
969 p_lookup_type10 := fetched_input_value.lookup_type;
970 p_value_set_id10 := fetched_input_value.value_set_id;
971 p_formula_id10 := fetched_input_value.formula_id;
972 p_min_value10 := fetched_input_value.min_value;
973 p_max_value10 := fetched_input_value.max_value;
974 p_default_value10 := fetched_input_value.default_value;
975 --
976 elsif input_value_number =11 then
977 --
978 p_input_value_id11 := fetched_input_value.input_value_id;
979 p_uom11 := fetched_input_value.uom;
980 p_name11 := fetched_input_value.name;
981 p_hot_default_flag11 := fetched_input_value.hot_default_flag;
982 p_mandatory_flag11 := fetched_input_value.mandatory_flag;
983 p_warning_or_error11 := fetched_input_value.warning_or_error;
984 p_lookup_type11 := fetched_input_value.lookup_type;
985 p_value_set_id11 := fetched_input_value.value_set_id;
986 p_formula_id11 := fetched_input_value.formula_id;
987 p_min_value11 := fetched_input_value.min_value;
988 p_max_value11 := fetched_input_value.max_value;
989 p_default_value11 := fetched_input_value.default_value;
990 --
991 elsif input_value_number =12 then
992 --
993 p_input_value_id12 := fetched_input_value.input_value_id;
994 p_uom12 := fetched_input_value.uom;
995 p_name12 := fetched_input_value.name;
996 p_hot_default_flag12 := fetched_input_value.hot_default_flag;
997 p_mandatory_flag12 := fetched_input_value.mandatory_flag;
998 p_warning_or_error12 := fetched_input_value.warning_or_error;
999 p_lookup_type12 := fetched_input_value.lookup_type;
1000 p_value_set_id12 := fetched_input_value.value_set_id;
1001 p_formula_id12 := fetched_input_value.formula_id;
1002 p_min_value12 := fetched_input_value.min_value;
1003 p_max_value12 := fetched_input_value.max_value;
1004 p_default_value12 := fetched_input_value.default_value;
1005 --
1006 elsif input_value_number =13 then
1007 --
1008 p_input_value_id13 := fetched_input_value.input_value_id;
1009 p_uom13 := fetched_input_value.uom;
1010 p_name13 := fetched_input_value.name;
1011 p_hot_default_flag13 := fetched_input_value.hot_default_flag;
1012 p_mandatory_flag13 := fetched_input_value.mandatory_flag;
1013 p_warning_or_error13 := fetched_input_value.warning_or_error;
1014 p_lookup_type13 := fetched_input_value.lookup_type;
1015 p_value_set_id13 := fetched_input_value.value_set_id;
1016 p_formula_id13 := fetched_input_value.formula_id;
1017 p_min_value13 := fetched_input_value.min_value;
1018 p_max_value13 := fetched_input_value.max_value;
1019 p_default_value13 := fetched_input_value.default_value;
1020 --
1021 elsif input_value_number =14 then
1022 --
1023 p_input_value_id14 := fetched_input_value.input_value_id;
1024 p_uom14 := fetched_input_value.uom;
1025 p_name14 := fetched_input_value.name;
1026 p_hot_default_flag14 := fetched_input_value.hot_default_flag;
1027 p_mandatory_flag14 := fetched_input_value.mandatory_flag;
1028 p_warning_or_error14 := fetched_input_value.warning_or_error;
1029 p_lookup_type14 := fetched_input_value.lookup_type;
1030 p_value_set_id14 := fetched_input_value.value_set_id;
1031 p_formula_id14 := fetched_input_value.formula_id;
1032 p_min_value14 := fetched_input_value.min_value;
1033 p_max_value14 := fetched_input_value.max_value;
1034 p_default_value14 := fetched_input_value.default_value;
1035 --
1036 elsif input_value_number =15 then
1037 --
1038 p_input_value_id15 := fetched_input_value.input_value_id;
1039 p_uom15 := fetched_input_value.uom;
1040 p_name15 := fetched_input_value.name;
1041 p_hot_default_flag15 := fetched_input_value.hot_default_flag;
1042 p_mandatory_flag15 := fetched_input_value.mandatory_flag;
1043 p_warning_or_error15 := fetched_input_value.warning_or_error;
1044 p_lookup_type15 := fetched_input_value.lookup_type;
1045 p_value_set_id15 := fetched_input_value.value_set_id;
1046 p_formula_id15 := fetched_input_value.formula_id;
1047 p_min_value15 := fetched_input_value.min_value;
1048 p_max_value15 := fetched_input_value.max_value;
1049 p_default_value15 := fetched_input_value.default_value;
1050 --
1051 exit; -- stop looping after the fifteenth input value
1052 --
1053 end if;
1054 --
1055 end loop;
1056 --
1057 end get_input_value_details;
1058 --------------------------------------------------------------------------------
1059
1060
1061 --------------------------------------------------------------------------------
1062 end PAY_PAYWSMEE2_PKG;