1 package body PAY_PAYWSMEE_PKG as
2 /* $Header: paywsmee.pkb 120.7 2007/01/29 17:04:05 alogue noship $ */
3 --
4 -- NOTE *** If these constants are ever updated, the changed must also
5 -- *** be applied to those constants defined in PAY_PAYWSMEE2_PKG
6 -- *** This can be done by updating the file pywsmee2.pkb
7 g_coverage constant pay_input_values_f.name%type := 'Coverage';
8 g_ee_contributions constant pay_input_values_f.name%type := 'EE Contr';
9 g_er_contributions constant pay_input_values_f.name%type := 'ER Contr';
10
11 -- private package global vars for element entry type cache
12 TYPE g_element_link_id_typ IS TABLE OF pay_element_entries_f.element_link_id%TYPE
13 INDEX BY BINARY_INTEGER;
14 TYPE g_binary_integer_typ IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
15 TYPE g_entry_type_typ IS TABLE OF pay_element_entries_f.entry_type%TYPE
16 INDEX BY BINARY_INTEGER;
17 g_element_link_id_tab g_element_link_id_typ;
18 g_entry_type_start_tab g_binary_integer_typ;
19 g_entry_type_stop_tab g_binary_integer_typ;
20 g_entry_type_tab g_entry_type_typ;
21 g_assignment_id number := null;
22 g_effective_date date := null;
23
24 --------------------------------------------------------------------------------
25 function FORMATTED_DEFAULT (
26 --
27 -- Used by get_input_value_details to format default values
28 --
29 p_link_default varchar2,
30 p_type_default varchar2,
31 p_uom varchar2,
32 p_hot_default varchar2,
33 p_contributions_used varchar2,
34 p_input_value_name varchar2,
35 p_input_currency_code varchar2,
36 p_lookup_type varchar2,
37 p_value_set_id number default null) return varchar2 is
38 --
39 l_formatted_value varchar2 (80) := null;
40 l_db_value varchar2 (80) := null;
41 --
42 begin
43 --
44 if p_contributions_used = 'Y'
45 and p_input_value_name in (g_coverage, g_ee_contributions, g_er_contributions)
46 then
47 -- Type A benefit plans have certain input value defaults calculated outside
48 -- this package
49 l_formatted_value := null;
50 else
51 --
52 if p_hot_default = 'Y' then
53 l_db_value := nvl (p_link_default, p_type_default);
54 else
55 l_db_value := p_link_default;
56 end if;
57 --
58 if p_lookup_type is null
59 and p_value_set_id is null
60 then
61 -- Convert the default value from database to display format
62 l_formatted_value := hr_chkfmt.changeformat (l_db_value, p_uom, p_input_currency_code);
63 elsif p_lookup_type is not null then
64 -- Get the user meaning for the lookup code stored
65 l_formatted_value := hr_general.decode_lookup (p_lookup_type, l_db_value);
66 elsif p_value_set_id is not null then
67 -- Get the user meaning for the value set value stored
68 l_formatted_value := pay_input_values_pkg.decode_vset_value(p_value_set_id,
69 l_db_value);
70 end if;
71 --
72 if p_hot_default = 'Y' then
73 -- Hot defaults are denoted by speech marks
74 l_formatted_value := '"'||l_formatted_value||'"';
75 end if;
76 --
77 end if;
78 --
79 return l_formatted_value;
80 --
81 end formatted_default;
82 --------------------------------------------------------------------------------
83 procedure fetch_payroll_period_info (
84 --
85 --*****************************************************************************
86 -- Fetch information about the payroll period for the context assignment
87 --*****************************************************************************
88 --
89 p_payroll_id in out nocopy number, -- Payroll ID of the context assignment
90 p_effective_date in date, -- form session effective date
91 p_display_period OUT nocopy varchar2,-- period name and its start-end dates
92 p_period_status OUT nocopy varchar2,-- open or closed flag
93 p_start_date OUT nocopy date, -- the period start date
94 p_end_date OUT nocopy date -- the period end date
95 ) is
96 --
97 -- Define how to retrieve payroll period information
98 --
99 cursor asgt_payroll_period is
100 select start_date,
101 end_date,
102 status,
103 period_name||
104 ' ('||
105 fnd_date.date_to_displaydate (start_date)||
106 ' - '||
107 fnd_date.date_to_displaydate (end_date)||
108 ')' DISPLAY_PERIOD
109 from per_time_periods
110 where payroll_id = p_payroll_id
111 and p_effective_date between start_date and end_date;
112 --
113 begin
114 --
115 if p_payroll_id is not null then -- only fetch info if assignment has a payroll
116 --
117 -- Fetch the payroll period information
118 --
119 open asgt_payroll_period;
120 --
121 fetch asgt_payroll_period into
122 p_start_date,
123 p_end_date,
124 p_period_status,
125 p_display_period;
126 --
127 close asgt_payroll_period;
128 --
129 else -- no payroll information exists so nullify 'out' parameters
130 --
131 p_start_date := null;
132 p_end_date := null;
133 --
134 end if;
135 --
136 end fetch_payroll_period_info;
137 --------------------------------------------------------------------------------
138 procedure populate_context_items (
139 --
140 --******************************************************************************
141 -- Populate form initialisation information
142 --******************************************************************************
143 --
144 p_effective_date in date, -- Form session date
145 p_business_group_id in number, -- User's business group
146 p_customized_restriction_id in number, -- customization identifier
147 p_assignment_id in number, -- Context assignment
148 p_payroll_id in out nocopy number, -- Payroll id for the asg
149 p_display_period in out nocopy varchar2,-- Period displayed details
150 p_period_status in out nocopy varchar2,-- Open/closed flag 4 period
151 p_start_date in out nocopy date, -- Start of period
152 p_end_date in out nocopy date, -- End of period
153 p_cost_allocation_structure in out nocopy varchar2,-- Keyflex structure
154 p_pay_value_name in out nocopy varchar2,
155 p_processing_type in out nocopy varchar2,-- customization
156 p_entry_type in out nocopy varchar2,-- customization
157 p_element_set in out nocopy number -- customization
158 ) is
159 --
160 -- Define how to retrieve Keyflex structure information
161 --
162 cursor keyflex_structure is
163 select cost_allocation_structure
164 from per_business_groups_perf
165 where business_group_id = p_business_group_id;
166 --
167 -- Define how to retrieve assignment's payroll information
168 --
169 cursor payroll is
170 select payroll_id
171 from per_assignments_f
172 where assignment_id = p_assignment_id
173 and p_effective_date between effective_start_date
174 and effective_end_date;
175 --
176 -- Define how to retrieve customization details
177 --
178 cursor type_customization is
179 select value
180 from pay_restriction_values
181 where restriction_code = 'ELEMENT_TYPE'
182 and customized_restriction_id = p_customized_restriction_id;
183 --
184 cursor set_customization is
185 select fnd_number.canonical_to_number( value )
186 from pay_restriction_values
187 where restriction_code = 'ELEMENT_SET'
188 and customized_restriction_id = p_customized_restriction_id;
189 --
190 cursor entry_type_customization is
191 select value
192 from pay_restriction_values
193 where restriction_code = 'ENTRY_TYPE'
194 and customized_restriction_id = p_customized_restriction_id;
195 --
196 begin
197 --
198 -- Fetch Keyflex information
199 --
200 open keyflex_structure;
201 fetch keyflex_structure into p_cost_allocation_structure;
202 close keyflex_structure;
203 --
204 -- Fetch assignment's payroll ID
205 --
206 open payroll;
207 fetch payroll into p_payroll_id;
208 close payroll;
209 --
210 -- Using the newly fetched payroll ID, fetch the current payroll period details
211 --
212 fetch_payroll_period_info ( p_payroll_id,
213 p_effective_date,
214 p_display_period,
215 p_period_status,
216 p_start_date,
217 p_end_date );
218 --
219 -- Find local translation of pay value name
220 --
221 p_pay_value_name := hr_general.pay_value;
222 --
223 -- Find processing type customization
224 --
225 open type_customization;
226 fetch type_customization into p_processing_type;
227 close type_customization;
228 --
229 -- Find element set customization
230 --
231 open set_customization;
232 fetch set_customization into p_element_set;
233 close set_customization;
234 --
235 -- Find entry type customization
236 --
237 open entry_type_customization;
238 fetch entry_type_customization into p_entry_type;
239 close entry_type_customization;
240 --
241 end populate_context_items;
242 --------------------------------------------------------------------------------
243 function PROCESSED (
244 --
245 -- Returns 'Y' if the element entry has already been processed in a payroll
246 -- run. Used by the pay_paywsmee_element_entries view and others.
247 --
248 p_element_entry_id number,
249 p_original_entry_id number,
250 p_processing_type varchar2,
251 p_entry_type varchar2,
252 p_effective_date date) return varchar2 is
253 --
254 processed varchar2(1) := 'N';
255 --
256 -- Define how to determine if the entry is processed
257 --
258 cursor nonrecurring_entries (adjust_ee_source in varchar2) is
259 select 'Y'
260 from pay_run_results prr,
261 pay_element_entries_f pee
262 where pee.element_entry_id = p_element_entry_id
263 and p_effective_date between pee.effective_start_date
264 and pee.effective_end_date
265 and prr.source_id = decode(pee.entry_type,
266 'A', decode (adjust_ee_source,
267 'T', pee.target_entry_id,
268 pee.element_entry_id),
269 'R', decode (adjust_ee_source,
270 'T', pee.target_entry_id,
271 pee.element_entry_id),
272 pee.element_entry_id)
273 and prr.entry_type = pee.entry_type
274 and prr.source_type = 'E'
275 and prr.status <> 'U'
276 -- change 115.9
277 and NOT EXISTS
278 (SELECT 1
279 FROM PAY_RUN_RESULTS sub_rr
280 WHERE sub_rr.source_id = prr.run_result_id
281 and sub_rr.source_type in ('R', 'V'))
282 ;
283 --
284 --
285 -- Retropay by Element Entry for unprocessed nonrecurring entry
286 --
287 cursor nonrecurring_retro_entry is
288 select 'Y'
289 from pay_element_entries_f oee,
290 pay_element_entries_f ree
291 where oee.element_entry_id = p_element_entry_id
292 and p_effective_date between oee.effective_start_date
293 and oee.effective_end_date
294 and ree.assignment_id = oee.assignment_id
295 and ree.source_id = oee.element_entry_id
296 and ree.entry_type = 'E'
297 and ree.creator_type = 'EE';
298 --
299 -- Bug 522510, recurring entries are considered as processed in the Date Earned period,
300 -- not Date Paid period - where run results exists.
301
302 cursor recurring_entries is
303 --
304 select 'Y'
305 from pay_run_results RESULT,
306 pay_assignment_actions ASGT_ACTION,
307 pay_payroll_actions PAY_ACTION,
308 per_time_periods PERIOD
309 where result.source_id = nvl (p_original_entry_id, p_element_entry_id)
310 and result.status <> 'U'
311 and result.source_type = 'E'
312 and result.assignment_action_id = asgt_action.assignment_action_id
313 and asgt_action.payroll_action_id = pay_action.payroll_action_id
314 and pay_action.payroll_id = period.payroll_id
315 and pay_action.date_earned between period.start_date and period.end_date
316 and p_effective_date between period.start_date and period.end_date
317 -- change 115.12
318 and NOT EXISTS
319 (SELECT 1
320 FROM PAY_RUN_RESULTS rev_result
321 WHERE rev_result.source_id = result.run_result_id
322 and rev_result.source_type in ('R', 'V'));
323 --
324 -- Retropay by Element Entry for unprocessed recurring entry
325 --
326 cursor recurring_retro_entry is
327 select /*+ ORDERED INDEX(ree PAY_ELEMENT_ENTRIES_F_N50)*/
328 'Y'
329 from pay_element_entries_f oee,
330 pay_element_entries_f ree,
331 pay_assignment_actions paa,
332 pay_payroll_actions pac,
333 per_time_periods period
334 where oee.element_entry_id = p_element_entry_id
335 and p_effective_date between oee.effective_start_date
336 and oee.effective_end_date
337 and p_effective_date between period.start_date and period.end_date
338 and pac.payroll_id = period.payroll_id
339 and pac.date_earned between period.start_date and period.end_date
340 and ree.assignment_id = oee.assignment_id
341 and ree.source_id = oee.element_entry_id
342 and ree.entry_type = 'D'
343 and ree.creator_type = 'EE'
344 and paa.assignment_action_id = ree.source_asg_action_id
345 and pac.payroll_action_id = paa.payroll_action_id
346 and pac.effective_date between oee.effective_start_date
347 and oee.effective_end_date;
348 --
349 adjust_ee_source varchar2(1);
350 begin
351 --
352 if (p_entry_type in ('S','D','A','R') or p_processing_type = 'N') then
353 --
354 begin
355 select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/ plr.rule_mode
356 into adjust_ee_source
357 from pay_legislation_rules plr,
358 per_business_groups pbg,
359 per_assignments_f paf,
360 pay_element_entries_f pee
361 where pee.element_entry_id = p_element_entry_id
362 and p_effective_date between pee.effective_start_date
363 and pee.effective_end_date
364 and paf.assignment_id = pee.assignment_id
365 and p_effective_date between paf.effective_start_date
366 and paf.effective_end_date
367 and paf.business_group_id = pbg.business_group_id
368 and pbg.legislation_code = plr.legislation_code
369 and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
370 --
371 exception
372 when no_data_found then
373 adjust_ee_source := 'A';
374 end;
375 --
376 open nonrecurring_entries(adjust_ee_source);
377 fetch nonrecurring_entries into processed;
378 close nonrecurring_entries;
379 --
380 if (processed = 'N') then
381 open nonrecurring_retro_entry;
385 --
382 fetch nonrecurring_retro_entry into processed;
383 close nonrecurring_retro_entry;
384 end if;
386 else
387 --
388 open recurring_entries;
389 fetch recurring_entries into processed;
390 close recurring_entries;
391 --
392 if (processed = 'N') then
393 open recurring_retro_entry;
394 fetch recurring_retro_entry into processed;
395 close recurring_retro_entry;
396 end if;
397 --
398 end if;
399 --
400 return processed;
401 --
402 end processed;
403 --------------------------------------------------------------------------------
404 PROCEDURE delete_entry_caches IS
405 BEGIN
406 -- no need to delete the g_element_link_id_tab structure as it is always deleted
407 -- at the end of population
408 g_entry_type_start_tab.DELETE;
409 g_entry_type_stop_tab.DELETE;
410 g_entry_type_tab.DELETE;
411 -- Bugfix 4601302
412 -- nullify g_assignment_id and g_effective_date to ensure cache is rebuilt
413 -- next time
414 g_assignment_id := null;
415 g_effective_date := null;
416 --
417 END delete_entry_caches;
418 --------------------------------------------------------------------------------
419 PROCEDURE populate_entry_type_cache
420 (p_assignment_id IN NUMBER,
421 p_effective_date IN DATE) IS
422 --
423 CURSOR csr_entry is
424 SELECT DISTINCT
425 pee.element_link_id,
426 pee.entry_type
427 FROM pay_element_entries_f pee
428 WHERE pee.assignment_id = p_assignment_id
429 AND p_effective_date
430 BETWEEN pee.effective_start_date and pee.effective_end_date
431 ORDER BY 1,2;
432 --
433 BEGIN
434 -- check to see if the assignment is cached
435 IF p_assignment_id <> nvl(g_assignment_id, p_assignment_id+1) OR
436 p_effective_date <> nvl(g_effective_date, p_effective_date+1) THEN
437 -- the assignment_id/effective_date combo don't match so populate
438 -- the cache but before we do that complete the following:
439 -- 1) clear caches
440 delete_entry_caches;
441 -- 2) set the private global assignment and effective_date comparision
442 g_assignment_id := p_assignment_id;
443 g_effective_date := p_effective_date;
444 -- now go populate the cache structures
445 -- perform a BULK collect
446 OPEN csr_entry;
447 FETCH csr_entry BULK COLLECT INTO g_element_link_id_tab, g_entry_type_tab;
448 CLOSE csr_entry;
449 -- -------------------------------------------------------------------
450 -- cache strategy
451 --
452 -- the cache uses 3 structures:
453 -- 1) g_entry_type_tab stores the entry types
454 -- 2) g_entry_type_start_tab stores the starting index position of the
455 -- element link entry types
456 -- 3) g_entry_type_stop_tab stores the end index position of the
457 -- element link entry types
458 --
459 -- g_entry_type_start_tab g_entry_type_stop_tab g_entry_type_tab
460 -- [23] 55 [23] 57 [55] B
461 -- [56] D
462 -- [57] S
463 --
464 -- so from above, the element_link_id of 23 is the index position
465 -- within each of the start/stop arrays. The start/stop arrays point
466 -- to the start/end indexes of the entry type table.
467 -- -------------------------------------------------------------------
468 -- populate the cache looping through each row returned by the BULK collect
469 FOR i IN g_element_link_id_tab.FIRST..g_element_link_id_tab.LAST LOOP
470 -- has the element_link already been placed in the cache?
471 -- we do this by checking to see if a start position exists
472 -- using the element_link_id as an index
473 IF NOT g_entry_type_start_tab.EXISTS(g_element_link_id_tab(i)) THEN
474 -- the element link was not found so set the start position
475 g_entry_type_start_tab(g_element_link_id_tab(i)) := i;
476 END IF;
477 -- always set the end position
478 g_entry_type_stop_tab(g_element_link_id_tab(i)) := i;
479 END LOOP;
480 -- as we don't need the g_element_link_id_tab contents delete it
481 -- to free up memory
482 g_element_link_id_tab.DELETE;
483 ELSE
484 -- the cache is already populated for the assignment_id/effective_date
485 -- combo so just return
486 RETURN;
487 END IF;
488 EXCEPTION
489 WHEN OTHERS THEN
490 -- an unexpected error has occurred so clean up and raise the error
491 IF csr_entry%ISOPEN THEN
492 -- as the csr_entry is still open close it
493 CLOSE csr_entry;
494 END IF;
495 -- clear down any caches which may have been populated to free the
496 -- memory
497 delete_entry_caches;
498 -- raise the error
499 RAISE;
500 END populate_entry_type_cache;
501 --------------------------------------------------------------------------------
502 function normal_exists (
503 --
504 -- Returns 'Y' if a normal entry exists for this assignment and element type.
505 -- Used by the view pay_paywsmee_element_entries
506 --
507 p_element_link_id number,
508 p_assignment_id number,
509 p_effective_date date) return varchar2 is
510 --
511 BEGIN
512 -- populate or use the current cache
513 populate_entry_type_cache
517 FOR i IN g_entry_type_start_tab(p_element_link_id)..
514 (p_assignment_id => p_assignment_id,
515 p_effective_date => p_effective_date);
516 -- check to see if an entry_type of E exists
518 g_entry_type_stop_tab(p_element_link_id) LOOP
519 -- does any entry_type of E exist?
520 IF g_entry_type_tab(i) = 'E' THEN
521 RETURN('Y');
522 END IF;
523 END LOOP;
524 RETURN('N');
525 EXCEPTION
526 WHEN OTHERS THEN
527 -- caused by the element_link_id not existing as an element entry for the
528 -- assignment/effective date combo
529 RETURN('N');
530 END normal_exists;
531 --------------------------------------------------------------------------------
532 FUNCTION additional_exists (
533 --
534 -- Returns 'Y' if an additional entry of this type exists for this assignment
535 -- Used by the view pay_paywsmee_element_entries
536 --
537 p_element_link_id number,
538 p_assignment_id number,
539 p_effective_date date) RETURN VARCHAR2 IS
540 --
541 BEGIN
542 -- populate or use the current cache
543 populate_entry_type_cache
544 (p_assignment_id => p_assignment_id,
545 p_effective_date => p_effective_date);
546 -- check to see if an entry_type of D exists
547 FOR i IN g_entry_type_start_tab(p_element_link_id)..
548 g_entry_type_stop_tab(p_element_link_id) LOOP
549 -- does any entry_type of D exist?
550 IF g_entry_type_tab(i) = 'D' THEN
551 RETURN('Y');
552 END IF;
553 END LOOP;
554 RETURN('N');
555 EXCEPTION
556 WHEN OTHERS THEN
557 -- caused by the element_link_id not existing as an element entry for the
558 -- assignment/effective date combo
559 RETURN('N');
560 END additional_exists;
561 --------------------------------------------------------------------------------
562 function is_entry_included (
563 --
564 -- Returns TRUE if this entry is included in the quickpay run
565 --
566 p_assignment_action_id number,
567 p_element_entry_id number) return varchar2 is
568 --
569 -- Enhancement 3368211
570 -- csr_included now in 2 parts, the first part handles the QuickPay
571 -- Inclusions model, the second handles the new QuickPay Exclusions model.
572 --
573 cursor csr_included (
574 p_asgt_act_id number,
575 p_ee_id number,
576 p_use_qpay_excl_model varchar2
577 )
578 is
579 /*
580 * QuickPay Inclusions model
581 */
582 select 'Y'
583 from pay_quickpay_inclusions incl
584 where p_use_qpay_excl_model = 'N'
585 and incl.assignment_action_id = p_asgt_act_id
586 and incl.element_entry_id = p_ee_id
587 union all
588 /*
589 * QuickPay Exclusions model
590 */
591 select 'Y'
592 from dual
593 where p_use_qpay_excl_model = 'Y'
594 and not exists
595 (select ''
596 from pay_quickpay_exclusions excl
597 where excl.assignment_action_id = p_asgt_act_id
598 and excl.element_entry_id = p_ee_id
599 );
600 -- select 'Y'
601 -- from pay_element_types_f ety
602 -- , pay_element_links_f elk
603 -- , pay_element_entries_f ent
604 -- , pay_payroll_actions pya
605 -- , pay_assignment_actions asa
606 -- where p_use_qpay_excl_model = 'Y'
607 -- /*
608 -- * Ensure entry does not exist in list of exclusions
609 -- */
610 -- and not exists (
611 -- select 'x'
612 -- from pay_quickpay_exclusions excl
613 -- where excl.assignment_action_id = p_asgt_act_id
614 -- and excl.element_entry_id = p_ee_id
615 -- )
616 -- /*
617 -- * Element Type:
618 -- * Only include those which can be processed in the run.
619 -- */
620 -- and ety.process_in_run_flag = 'Y'
621 -- and ety.element_type_id = elk.element_type_id
622 -- and pya.date_earned between ety.effective_start_date
623 -- and ety.effective_end_date
624 -- /*
625 -- * Element Link:
626 -- * Only include those that exist as of QuickPay date earned.
627 -- */
628 -- and elk.element_link_id = ent.element_link_id
629 -- and pya.date_earned between elk.effective_start_date
630 -- and elk.effective_end_date
631 -- /*
632 -- * Element Entry:
633 -- * Do not include balance adjustment, replacement adjustment
634 -- * or additive adjustment.
635 -- */
636 -- and ent.element_entry_id = p_ee_id
637 -- and ent.entry_type not in ('B', 'A', 'R')
638 -- and ent.assignment_id = asa.assignment_id
639 -- and ent.effective_start_date <= pya.date_earned
640 -- and ent.effective_end_date >= decode(ety.proration_group_id, null, pya.date_earned,
641 -- pay_interpreter_pkg.prorate_start_date
642 -- (asa.assignment_action_id,
646 -- * Non-recurring entries can only be included if they have not
643 -- ety.proration_group_id
644 -- ))
645 -- /*
647 -- * been processed.
648 -- */
649 -- and ( ( ( (ety.processing_type = 'N'
650 -- )
651 -- /*
652 -- * Recurring, additional or override entries can only be
653 -- * included if they have not been processed. (These types of
654 -- * recurring entry are handled as if they were non-recurring.)
655 -- */
656 -- or ( ety.processing_type = 'R'
657 -- and ent.entry_type <> 'E'
658 -- )
659 -- )
660 -- and (not exists ( select 'x'
661 -- from pay_run_results pr1
662 -- , pay_assignment_actions asa2
663 -- where pr1.source_id = ent.element_entry_id
664 -- and pr1.source_type = 'E'
665 -- and pr1.status <> 'U'
666 -- and pr1.assignment_action_id = asa2.assignment_action_id
667 -- and ( asa2.source_action_id <> p_asgt_act_id
668 -- or ( asa2.assignment_action_id <> p_asgt_act_id
669 -- and not exists ( select 'x'
670 -- from pay_assignment_actions asa3
671 -- where asa3.assignment_action_id = pr1.assignment_action_id
672 -- and asa3.source_action_id = p_asgt_act_id
673 -- )
674 -- )
675 -- )
676 -- )
677 -- or exists (select null
678 -- from pay_run_results pr1
679 -- where pr1.source_id = ent.element_entry_id
680 -- and pr1.source_type = 'E'
681 -- and pr1.status = 'U'
682 -- )
683 -- )
684 -- )
685 -- /*
686 -- * Include other recurring entries.
687 -- * i.e. Those which are not additional or overrides entries.
688 -- */
689 -- or ( ety.processing_type = 'R'
690 -- and ent.entry_type = 'E'
691 -- )
692 -- )
693 -- /*
694 -- * Payroll Action:
695 -- * Ensure the action is for a QuickPay Run.
696 -- */
697 -- and pya.action_type = 'Q'
698 -- and pya.payroll_action_id = asa.payroll_action_id
699 -- /*
700 -- * Assignment Action:
701 -- */
702 -- and asa.assignment_action_id = p_asgt_act_id;
703 --
704 l_included varchar2(1) := 'N';
705 --
706 begin
707 --
708 open csr_included (
709 p_assignment_action_id,
710 p_element_entry_id,
711 pay_qpq_api.use_qpay_excl_model
712 );
713 fetch csr_included into l_included;
714 close csr_included;
715 return l_included;
716 --
717 end is_entry_included;
718 --------------------------------------------------------------------------------
719 function overridden (
720 --
721 -- Returns 'Y' if the entry is overridden. Used by pay_paywsmee_elements_lov
722 -- and pay_paywsmee_element_entries
723 --
724 p_element_link_id number,
725 p_assignment_id number,
726 p_effective_date date) return varchar2 is
727 --
728 BEGIN
729 -- populate or use the current cache
730 populate_entry_type_cache
731 (p_assignment_id => p_assignment_id,
732 p_effective_date => p_effective_date);
733 -- check to see if an entry_type of S exists
734 FOR i IN g_entry_type_start_tab(p_element_link_id)..
735 g_entry_type_stop_tab(p_element_link_id) LOOP
736 -- does any entry_type of S exist?
737 IF g_entry_type_tab(i) = 'S' THEN
738 RETURN('Y');
739 END IF;
740 END LOOP;
741 RETURN('N');
742 EXCEPTION
743 WHEN OTHERS THEN
744 -- caused by the element_link_id not existing as an element entry for the
745 -- assignment/effective date combo
746 RETURN('N');
747 END overridden;
748 --------------------------------------------------------------------------------
749 function personal_payment_method (
750 --
751 p_personal_payment_method_id number,
752 p_assignment_id number,
753 p_effective_date date) return varchar2 is
754 --
755 cursor personal_payment_method is
756 select ppm.payee_type,
757 ppm.payee_id,
758 opm_tl.org_payment_method_name
759 ||' : '||pay_type_tl.payment_type_name PAYMENT_TYPE
760 from pay_personal_payment_methods_f PPM,
764 pay_payment_types PAY_TYPE
761 pay_org_payment_methods_f_tl OPM_TL,
762 pay_org_payment_methods_f OPM,
763 pay_payment_types_tl PAY_TYPE_TL,
765 where personal_payment_method_id = p_personal_payment_method_id
766 and ppm.org_payment_method_id = opm.org_payment_method_id
767 and opm_tl.org_payment_method_id = opm.org_payment_method_id
768 and USERENV('LANG') = opm_tl.language
769 and pay_type.payment_type_id = opm.payment_type_id
770 and pay_type_tl.payment_type_id = pay_type.payment_type_id
771 and userenv('LANG') = pay_type_tl.language
772 and p_effective_date between opm.effective_start_date
773 and opm.effective_end_date
774 and p_effective_date between ppm.effective_start_date
775 and ppm.effective_end_date;
776 --
777 l_payee_id number;
778 l_payee_type varchar2 (255);
779 l_payment_type varchar2 (500);
780 --
781 cursor organization is
782 select name
783 from hr_all_organization_units
784 where organization_id = l_payee_id;
785 --
786 cursor person is
787 select full_name
788 from per_all_people_f
789 where person_id = l_payee_id
790 and p_effective_date between effective_start_date
791 and effective_end_date;
792 --
793 l_third_party_name varchar2 (255) := null;
794 l_separator varchar2 (1) := null;
795 --
796 begin
797 --
798 -- Open cursors if a personal payment method is passed in
799 --
800 if p_personal_payment_method_id is not null then
801 --
802 -- Get the PPM details
803 --
804 open personal_payment_method;
805 fetch personal_payment_method into l_payee_type, l_payee_id, l_payment_type;
806 close personal_payment_method;
807 --
808 if l_payee_type = 'P' then
809 --
810 -- Get the name of the person who is the third party payee
811 --
812 open person;
813 fetch person into l_third_party_name;
814 close person;
815 --
816 elsif l_payee_type = 'O' then
817 --
818 -- Get the name of the organization which is the third party payee
819 --
820 open organization;
821 fetch organization into l_third_party_name;
822 close organization;
823 --
824 end if;
825 --
826 end if;
827 --
828 if l_third_party_name is not null then
829 l_separator := ' ';
830 else
831 l_separator := null;
832 end if;
833 --
834 return (l_third_party_name || l_separator || l_payment_type);
835 --
836 end personal_payment_method;
837 --------------------------------------------------------------------------------
838 function adjusted (
839 --
840 -- Returns 'Y' if there is any entry for this link and assignment with an
841 -- adjustment. Used by pay_paywsmee_element_entries and
842 -- pay_paywsmee_elements_lov
843 --
844 p_element_link_id number,
845 p_assignment_id number,
846 p_effective_date date) return varchar2 is
847 --
848 BEGIN
849 -- populate or use the current cache
850 populate_entry_type_cache
851 (p_assignment_id => p_assignment_id,
852 p_effective_date => p_effective_date);
853 -- check to see if an entry_type of 'B', 'R' or 'A' exists
854 FOR i IN g_entry_type_start_tab(p_element_link_id)..
855 g_entry_type_stop_tab(p_element_link_id) LOOP
856 -- does any entry_type of 'B', 'R' or 'A' exist?
857 IF g_entry_type_tab(i) in ('B', 'R', 'A') THEN
858 RETURN('Y');
859 END IF;
860 END LOOP;
861 RETURN('N');
862 EXCEPTION
863 WHEN OTHERS THEN
864 -- caused by the element_link_id not existing as an element entry for the
865 -- assignment/effective date combo
866 RETURN('N');
867 END adjusted;
868 --------------------------------------------------------------------------------
869 procedure update_original_if_MIX
870 (
871 -- used by entry.insert_row to nulify creator_type
872 -- for MIX entry when creating additional entries or overrides
873 --
874 p_assignment_id number,
875 p_element_type_id number,
876 p_effective_start_date date,
877 p_session_date date
878 ) is
879 --
880 l_element_entry_id number;
881 l_creator_type varchar2(10);
882 --
883 cursor csr_original_entry is
884 select peef.element_entry_id, peef.creator_type
885 from pay_element_entries_f peef,
886 pay_element_links_f pelf,
887 pay_element_types_f petf
888 where petf.element_type_id = p_element_type_id
889 and pelf.element_type_id = petf.element_type_id
890 and peef.element_link_id = pelf.element_link_id
891 and peef.assignment_id = p_assignment_id
892 and p_effective_start_date between peef.effective_start_date
893 and peef.effective_end_date
894 and p_effective_start_date between pelf.effective_start_date
895 and pelf.effective_end_date
896 and p_effective_start_date between petf.effective_start_date
900
897 and petf.effective_end_date;
898 --
899 begin
901 open csr_original_entry;
902 fetch csr_original_entry into l_element_entry_id, l_creator_type;
903 close csr_original_entry;
904 --
905 if l_creator_type = 'H' then
906 hr_utility.trace('updating');
907 hr_entry_api.update_element_entry
908 (
909 p_dt_update_mode =>'CORRECTION',
910 p_session_date =>p_session_date,
911 p_creator_type => 'F',
912 p_creator_id => null,
913 p_element_entry_id =>l_element_entry_id
914 );
915 end if;
916 --
917 end update_original_if_MIX;
918
919 procedure GET_ENTRY_VALUE_DETAILS (
920 --
921 -- Returns the element entry values along with all their inherited properties
922 -- for each element entry selected by a query in the form
923 --
924 p_element_entry_id number,
925 p_element_link_id number,
926 p_effective_date date,
927 p_ee_effective_start_date date,
928 p_ee_effective_end_date date,
929 p_element_type_id number,
930 p_business_group_id number,
931 p_contributions_used varchar2,
932 p_input_currency_code varchar2,
933 p_input_value_id1 in out nocopy number,
934 p_input_value_id2 in out nocopy number,
935 p_input_value_id3 in out nocopy number,
936 p_input_value_id4 in out nocopy number,
937 p_input_value_id5 in out nocopy number,
938 p_input_value_id6 in out nocopy number,
939 p_input_value_id7 in out nocopy number,
940 p_input_value_id8 in out nocopy number,
941 p_input_value_id9 in out nocopy number,
942 p_input_value_id10 in out nocopy number,
943 p_input_value_id11 in out nocopy number,
944 p_input_value_id12 in out nocopy number,
945 p_input_value_id13 in out nocopy number,
946 p_input_value_id14 in out nocopy number,
947 p_input_value_id15 in out nocopy number,
948 p_name1 in out nocopy varchar2,
949 p_name2 in out nocopy varchar2,
950 p_name3 in out nocopy varchar2,
951 p_name4 in out nocopy varchar2,
952 p_name5 in out nocopy varchar2,
953 p_name6 in out nocopy varchar2,
954 p_name7 in out nocopy varchar2,
955 p_name8 in out nocopy varchar2,
956 p_name9 in out nocopy varchar2,
957 p_name10 in out nocopy varchar2,
958 p_name11 in out nocopy varchar2,
959 p_name12 in out nocopy varchar2,
960 p_name13 in out nocopy varchar2,
961 p_name14 in out nocopy varchar2,
962 p_name15 in out nocopy varchar2,
963 p_uom1 in out nocopy varchar2,
964 p_uom2 in out nocopy varchar2,
965 p_uom3 in out nocopy varchar2,
966 p_uom4 in out nocopy varchar2,
967 p_uom5 in out nocopy varchar2,
968 p_uom6 in out nocopy varchar2,
969 p_uom7 in out nocopy varchar2,
970 p_uom8 in out nocopy varchar2,
971 p_uom9 in out nocopy varchar2,
972 p_uom10 in out nocopy varchar2,
973 p_uom11 in out nocopy varchar2,
974 p_uom12 in out nocopy varchar2,
975 p_uom13 in out nocopy varchar2,
976 p_uom14 in out nocopy varchar2,
977 p_uom15 in out nocopy varchar2,
978 p_hot_default_flag1 in out nocopy varchar2,
979 p_hot_default_flag2 in out nocopy varchar2,
980 p_hot_default_flag3 in out nocopy varchar2,
981 p_hot_default_flag4 in out nocopy varchar2,
982 p_hot_default_flag5 in out nocopy varchar2,
983 p_hot_default_flag6 in out nocopy varchar2,
984 p_hot_default_flag7 in out nocopy varchar2,
985 p_hot_default_flag8 in out nocopy varchar2,
986 p_hot_default_flag9 in out nocopy varchar2,
987 p_hot_default_flag10 in out nocopy varchar2,
988 p_hot_default_flag11 in out nocopy varchar2,
989 p_hot_default_flag12 in out nocopy varchar2,
990 p_hot_default_flag13 in out nocopy varchar2,
991 p_hot_default_flag14 in out nocopy varchar2,
992 p_hot_default_flag15 in out nocopy varchar2,
993 p_mandatory_flag1 in out nocopy varchar2,
994 p_mandatory_flag2 in out nocopy varchar2,
995 p_mandatory_flag3 in out nocopy varchar2,
996 p_mandatory_flag4 in out nocopy varchar2,
997 p_mandatory_flag5 in out nocopy varchar2,
998 p_mandatory_flag6 in out nocopy varchar2,
999 p_mandatory_flag7 in out nocopy varchar2,
1000 p_mandatory_flag8 in out nocopy varchar2,
1001 p_mandatory_flag9 in out nocopy varchar2,
1002 p_mandatory_flag10 in out nocopy varchar2,
1003 p_mandatory_flag11 in out nocopy varchar2,
1004 p_mandatory_flag12 in out nocopy varchar2,
1005 p_mandatory_flag13 in out nocopy varchar2,
1006 p_mandatory_flag14 in out nocopy varchar2,
1007 p_mandatory_flag15 in out nocopy varchar2,
1008 p_formula_id1 in out nocopy number,
1009 p_formula_id2 in out nocopy number,
1010 p_formula_id3 in out nocopy number,
1014 p_formula_id7 in out nocopy number,
1011 p_formula_id4 in out nocopy number,
1012 p_formula_id5 in out nocopy number,
1013 p_formula_id6 in out nocopy number,
1015 p_formula_id8 in out nocopy number,
1016 p_formula_id9 in out nocopy number,
1017 p_formula_id10 in out nocopy number,
1018 p_formula_id11 in out nocopy number,
1019 p_formula_id12 in out nocopy number,
1020 p_formula_id13 in out nocopy number,
1021 p_formula_id14 in out nocopy number,
1022 p_formula_id15 in out nocopy number,
1023 p_lookup_type1 in out nocopy varchar2,
1024 p_lookup_type2 in out nocopy varchar2,
1025 p_lookup_type3 in out nocopy varchar2,
1026 p_lookup_type4 in out nocopy varchar2,
1027 p_lookup_type5 in out nocopy varchar2,
1028 p_lookup_type6 in out nocopy varchar2,
1029 p_lookup_type7 in out nocopy varchar2,
1030 p_lookup_type8 in out nocopy varchar2,
1031 p_lookup_type9 in out nocopy varchar2,
1032 p_lookup_type10 in out nocopy varchar2,
1033 p_lookup_type11 in out nocopy varchar2,
1034 p_lookup_type12 in out nocopy varchar2,
1035 p_lookup_type13 in out nocopy varchar2,
1036 p_lookup_type14 in out nocopy varchar2,
1037 p_lookup_type15 in out nocopy varchar2,
1038 p_value_set_id1 in out nocopy number,
1039 p_value_set_id2 in out nocopy number,
1040 p_value_set_id3 in out nocopy number,
1041 p_value_set_id4 in out nocopy number,
1042 p_value_set_id5 in out nocopy number,
1043 p_value_set_id6 in out nocopy number,
1044 p_value_set_id7 in out nocopy number,
1045 p_value_set_id8 in out nocopy number,
1046 p_value_set_id9 in out nocopy number,
1047 p_value_set_id10 in out nocopy number,
1048 p_value_set_id11 in out nocopy number,
1049 p_value_set_id12 in out nocopy number,
1050 p_value_set_id13 in out nocopy number,
1051 p_value_set_id14 in out nocopy number,
1052 p_value_set_id15 in out nocopy number,
1053 p_min_value1 in out nocopy varchar2,
1054 p_min_value2 in out nocopy varchar2,
1055 p_min_value3 in out nocopy varchar2,
1056 p_min_value4 in out nocopy varchar2,
1057 p_min_value5 in out nocopy varchar2,
1058 p_min_value6 in out nocopy varchar2,
1059 p_min_value7 in out nocopy varchar2,
1060 p_min_value8 in out nocopy varchar2,
1061 p_min_value9 in out nocopy varchar2,
1062 p_min_value10 in out nocopy varchar2,
1063 p_min_value11 in out nocopy varchar2,
1064 p_min_value12 in out nocopy varchar2,
1065 p_min_value13 in out nocopy varchar2,
1066 p_min_value14 in out nocopy varchar2,
1067 p_min_value15 in out nocopy varchar2,
1068 p_max_value1 in out nocopy varchar2,
1069 p_max_value2 in out nocopy varchar2,
1070 p_max_value3 in out nocopy varchar2,
1071 p_max_value4 in out nocopy varchar2,
1072 p_max_value5 in out nocopy varchar2,
1073 p_max_value6 in out nocopy varchar2,
1074 p_max_value7 in out nocopy varchar2,
1075 p_max_value8 in out nocopy varchar2,
1076 p_max_value9 in out nocopy varchar2,
1077 p_max_value10 in out nocopy varchar2,
1078 p_max_value11 in out nocopy varchar2,
1079 p_max_value12 in out nocopy varchar2,
1080 p_max_value13 in out nocopy varchar2,
1081 p_max_value14 in out nocopy varchar2,
1082 p_max_value15 in out nocopy varchar2,
1083 p_screen_entry_value1 in out nocopy varchar2,
1084 p_screen_entry_value2 in out nocopy varchar2,
1085 p_screen_entry_value3 in out nocopy varchar2,
1086 p_screen_entry_value4 in out nocopy varchar2,
1087 p_screen_entry_value5 in out nocopy varchar2,
1088 p_screen_entry_value6 in out nocopy varchar2,
1089 p_screen_entry_value7 in out nocopy varchar2,
1090 p_screen_entry_value8 in out nocopy varchar2,
1091 p_screen_entry_value9 in out nocopy varchar2,
1092 p_screen_entry_value10 in out nocopy varchar2,
1093 p_screen_entry_value11 in out nocopy varchar2,
1094 p_screen_entry_value12 in out nocopy varchar2,
1095 p_screen_entry_value13 in out nocopy varchar2,
1096 p_screen_entry_value14 in out nocopy varchar2,
1097 p_screen_entry_value15 in out nocopy varchar2,
1098 p_entry_value_id1 in out nocopy number,
1099 p_entry_value_id2 in out nocopy number,
1100 p_entry_value_id3 in out nocopy number,
1101 p_entry_value_id4 in out nocopy number,
1102 p_entry_value_id5 in out nocopy number,
1103 p_entry_value_id6 in out nocopy number,
1104 p_entry_value_id7 in out nocopy number,
1105 p_entry_value_id8 in out nocopy number,
1106 p_entry_value_id9 in out nocopy number,
1107 p_entry_value_id10 in out nocopy number,
1108 p_entry_value_id11 in out nocopy number,
1109 p_entry_value_id12 in out nocopy number,
1110 p_entry_value_id13 in out nocopy number,
1111 p_entry_value_id14 in out nocopy number,
1112 p_entry_value_id15 in out nocopy number,
1113 p_default_value1 in out nocopy varchar2,
1114 p_default_value2 in out nocopy varchar2,
1115 p_default_value3 in out nocopy varchar2,
1116 p_default_value4 in out nocopy varchar2,
1117 p_default_value5 in out nocopy varchar2,
1118 p_default_value6 in out nocopy varchar2,
1119 p_default_value7 in out nocopy varchar2,
1120 p_default_value8 in out nocopy varchar2,
1121 p_default_value9 in out nocopy varchar2,
1125 p_default_value13 in out nocopy varchar2,
1122 p_default_value10 in out nocopy varchar2,
1123 p_default_value11 in out nocopy varchar2,
1124 p_default_value12 in out nocopy varchar2,
1126 p_default_value14 in out nocopy varchar2,
1127 p_default_value15 in out nocopy varchar2,
1128 p_warning_or_error1 in out nocopy varchar2,
1129 p_warning_or_error2 in out nocopy varchar2,
1130 p_warning_or_error3 in out nocopy varchar2,
1131 p_warning_or_error4 in out nocopy varchar2,
1132 p_warning_or_error5 in out nocopy varchar2,
1133 p_warning_or_error6 in out nocopy varchar2,
1134 p_warning_or_error7 in out nocopy varchar2,
1135 p_warning_or_error8 in out nocopy varchar2,
1136 p_warning_or_error9 in out nocopy varchar2,
1137 p_warning_or_error10 in out nocopy varchar2,
1138 p_warning_or_error11 in out nocopy varchar2,
1139 p_warning_or_error12 in out nocopy varchar2,
1140 p_warning_or_error13 in out nocopy varchar2,
1141 p_warning_or_error14 in out nocopy varchar2,
1142 p_warning_or_error15 in out nocopy varchar2
1143 ) is
1144 --
1145 -- Bugfix 468639
1146 -- fetched_entry_value_rec and fetched_entry_value added
1147 -- to avoid truncation of lookup meanings to 60 chars
1148 --
1149 TYPE fetched_entry_value_rec IS RECORD
1150 (
1151 element_entry_value_id pay_element_entry_values_f.element_entry_value_id%TYPE
1152 ,screen_entry_value VARCHAR2(80) -- to avoid truncation of lookup meaning
1153 ,input_value_id pay_element_entry_values_f.input_value_id%TYPE
1154 ,name pay_input_values_f_tl.name%TYPE
1155 ,uom pay_input_values_f.uom%TYPE
1156 ,hot_default_flag pay_input_values_f.hot_default_flag%TYPE
1157 ,mandatory_flag pay_input_values_f.mandatory_flag%TYPE
1158 ,warning_or_error pay_input_values_f.warning_or_error%TYPE
1159 ,lookup_type pay_input_values_f.lookup_type%TYPE
1160 ,value_set_id pay_input_values_f.value_set_id%TYPE
1161 ,formula_id pay_input_values_f.formula_id%TYPE
1162 ,min_value pay_input_values_f.min_value%TYPE
1163 ,max_value pay_input_values_f.max_value%TYPE
1164 ,default_value VARCHAR2(82) -- to avoid truncation of lookup meaning + hot default quotes
1165 );
1166 --
1167 fetched_entry_value fetched_entry_value_rec;
1168 --
1169 v_coverage_type ben_benefit_contributions_f.coverage_type%type := null;
1170 v_ER_contr_default ben_benefit_contributions_f.employer_contribution%type := null;
1171 v_EE_contr_default ben_benefit_contributions_f.employee_contribution%type := null;
1172 --
1173 cursor BENEFIT_PLAN_DEFAULTS is
1174 --
1175 select employee_contribution,
1176 employer_contribution
1177 --
1178 from ben_benefit_contributions_f
1179 --
1180 where p_effective_date between effective_start_date
1181 and effective_end_date
1182 and element_type_id = p_element_type_id
1183 and business_group_id = p_business_group_id
1184 and coverage_type = v_coverage_type;
1185 --
1186 cursor SET_OF_ENTRY_VALUES is
1187 --
1188 select entry.element_entry_value_id,
1189 entry.screen_entry_value,
1190 entry.input_value_id,
1191 type_tl.name,
1192 type.uom,
1193 type.hot_default_flag,
1194 type.mandatory_flag,
1195 decode (type.hot_default_flag,
1196 'N', link.warning_or_error,
1197 nvl (link.warning_or_error,
1198 type.warning_or_error)) WARNING_OR_ERROR,
1199 type.lookup_type,
1200 type.value_set_id,
1201 type.formula_id,
1202 decode(type.hot_default_flag,'N',link.min_value,
1203 nvl(link.min_value,type.min_value)) MIN_VALUE,
1204 decode(type.hot_default_flag,'N',link.max_value,
1205 nvl(link.max_value,type.max_value)) MAX_VALUE,
1206 decode (type.hot_default_flag,
1207 'N', link.default_value,
1208 nvl (link.default_value,
1209 type.default_value)) DEFAULT_VALUE
1210 from pay_element_entry_values_f ENTRY,
1211 pay_link_input_values_f LINK,
1212 pay_input_values_f_tl TYPE_TL,
1213 pay_input_values_f TYPE
1214 where entry.element_entry_id = p_element_entry_id
1215 and link.element_link_id = p_element_link_id
1216 and link.input_value_id = entry.input_value_id
1217 and type.input_value_id = entry.input_value_id
1218 and type_tl.input_value_id = type.input_value_id
1219 and userenv('LANG') = type_tl.language
1220 and p_effective_date between link.effective_start_date
1221 and link.effective_end_date
1222 -- Bugfix 4438706
1223 -- Fetch the entry values that match the effective start and end
1224 -- dates of the entry, not the ones as at the effective date (could
1225 -- be the wrong values if form is running in QuickPay mode).
1226 -- and p_effective_date between entry.effective_start_date
1230 and p_effective_date between type.effective_start_date
1227 -- and entry.effective_end_date
1228 and entry.effective_start_date = p_ee_effective_start_date
1229 and entry.effective_end_date = p_ee_effective_end_date
1231 and type.effective_end_date
1232 order by type.display_sequence, type_tl.name;
1233 --
1234 entry_value_number integer;
1235 --
1236 begin
1237 --
1238 -- Retrieve all the existing element entry values for the element entry
1239 --
1240 -- Bugfix 468639
1241 -- fetch set_of_entry_values into pre-defined record fetched_entry_values
1242 -- in order to allow lookup meanings to be held as 80 character strings
1243 --
1244 OPEN set_of_entry_values;
1245 LOOP
1246 FETCH set_of_entry_values INTO fetched_entry_value;
1247 EXIT WHEN set_of_entry_values%NOTFOUND;
1248 --
1249 entry_value_number := set_of_entry_values%rowcount; -- loop index flag
1250 --
1251 -- If the element is a type A benefit plan, then replace the
1252 -- element type/link level defaults with the defaults retrieved
1253 -- from ben_benefit_contributions_f.
1254 --
1255 if p_contributions_used = 'Y' then
1256 --
1257 -- If the element is a type A benefit plan then get the
1258 -- default values for the EE Contr and ER Contr input values
1259 -- NB The 'Coverage' input value will always be ordered before
1260 -- the ER/EE Contr input values.
1261 --
1262 if fetched_entry_value.name = 'Coverage' then
1263 --
1264 v_coverage_type := fetched_entry_value.screen_entry_value;
1265 open benefit_plan_defaults;
1266 fetch benefit_plan_defaults into v_EE_contr_default, v_ER_contr_default;
1267 close benefit_plan_defaults;
1268 --
1269 elsif fetched_entry_value.name = 'EE Contr' then
1270 --
1271 fetched_entry_value.hot_default_flag := 'Y';
1272 fetched_entry_value.default_value := v_EE_contr_default;
1273 --
1274 elsif fetched_entry_value.name = 'ER Contr' then
1275 --
1276 fetched_entry_value.hot_default_flag := 'Y';
1277 fetched_entry_value.default_value := v_ER_contr_default;
1278 --
1279 end if;
1280 --
1281 end if;
1282 --
1283 if fetched_entry_value.lookup_type is null
1284 and fetched_entry_value.value_set_id is null
1285 then
1286 --
1287 -- If the entry value is not a lookup, then format it for display
1288 --
1289 --
1290 -- sbilling
1291 -- PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value is stored as varchar2(60),
1292 -- hr_chkfmt.changeformat() could return a 80 byte string which
1293 -- will not fit on PAY_ELEMENT_ENTRY_VALUES_F.screen_entry_value,
1294 -- therefore use substrb() to truncate this to 60 characters before
1295 -- storing on dB,
1296 -- nb. substrb() must be used as the dB could be set up as multibyte,
1297 -- in which case each byte must be returned as 2 bytes
1298 --
1299 fetched_entry_value.screen_entry_value :=
1300 substrb(hr_chkfmt.changeformat(
1301 fetched_entry_value.screen_entry_value,
1302 fetched_entry_value.uom,
1303 p_input_currency_code), 1, 60);
1304 --
1305 fetched_entry_value.default_value := hr_chkfmt.changeformat(
1306 fetched_entry_value.default_value,
1307 fetched_entry_value.uom,
1308 p_input_currency_code);
1309 --
1310 elsif fetched_entry_value.lookup_type is not null then
1311 --
1312 -- If the entry value is a lookup, then decode it for display
1313 --
1314 fetched_entry_value.default_value := hr_general.decode_lookup(
1315 fetched_entry_value.lookup_type,
1316 fetched_entry_value.default_value);
1317 --
1318 --
1319 -- sbilling
1320 -- same argument as above
1321 --
1322 -- Bugfix 468639
1323 -- substrb at 80 chars in order to retain full lookup meaning
1324 --
1325 fetched_entry_value.screen_entry_value :=
1326 substrb( hr_general.decode_lookup(
1327 fetched_entry_value.lookup_type,
1328 fetched_entry_value.screen_entry_value), 1, 80);
1329 --
1330 elsif fetched_entry_value.value_set_id is not null then
1331 --
1332 -- If the entry value is a value set value, then decode it for display
1333 --
1334 fetched_entry_value.default_value :=
1335 pay_input_values_pkg.decode_vset_value(
1336 fetched_entry_value.value_set_id,
1337 fetched_entry_value.default_value);
1338 --
1339 fetched_entry_value.screen_entry_value :=
1340 pay_input_values_pkg.decode_vset_value(
1341 fetched_entry_value.value_set_id,
1342 fetched_entry_value.screen_entry_value);
1343 --
1344 end if;
1345 --
1346 -- If the default value is hot-defaulted, then denote it with speech marks
1347 --
1348 if fetched_entry_value.hot_default_flag = 'Y'
1349 and fetched_entry_value.default_value is not null then
1350 fetched_entry_value.default_value := '"'||fetched_entry_value.default_value||'"';
1354 --
1351 end if;
1352 --
1353 if entry_value_number = 1 then
1355 -- Assign the out parameters
1356 --
1357 p_entry_value_id1 := fetched_entry_value.element_entry_value_id;
1358 p_screen_entry_value1 := fetched_entry_value.screen_entry_value;
1359 p_input_value_id1 := fetched_entry_value.input_value_id;
1360 p_uom1 := fetched_entry_value.uom;
1361 p_name1 := fetched_entry_value.name;
1362 p_hot_default_flag1 := fetched_entry_value.hot_default_flag;
1363 p_mandatory_flag1 := fetched_entry_value.mandatory_flag;
1364 p_warning_or_error1 := fetched_entry_value.warning_or_error;
1365 p_lookup_type1 := fetched_entry_value.lookup_type;
1366 p_value_set_id1 := fetched_entry_value.value_set_id;
1367 p_formula_id1 := fetched_entry_value.formula_id;
1368 p_min_value1 := fetched_entry_value.min_value;
1369 p_max_value1 := fetched_entry_value.max_value;
1370 p_default_value1 := fetched_entry_value.default_value;
1371 --
1372 elsif entry_value_number =2 then
1373 --
1374 p_entry_value_id2 := fetched_entry_value.element_entry_value_id;
1375 p_screen_entry_value2 := fetched_entry_value.screen_entry_value;
1376 p_input_value_id2 := fetched_entry_value.input_value_id;
1377 p_uom2 := fetched_entry_value.uom;
1378 p_name2 := fetched_entry_value.name;
1379 p_hot_default_flag2 := fetched_entry_value.hot_default_flag;
1380 p_mandatory_flag2 := fetched_entry_value.mandatory_flag;
1381 p_warning_or_error2 := fetched_entry_value.warning_or_error;
1382 p_lookup_type2 := fetched_entry_value.lookup_type;
1383 p_value_set_id2 := fetched_entry_value.value_set_id;
1384 p_formula_id2 := fetched_entry_value.formula_id;
1385 p_min_value2 := fetched_entry_value.min_value;
1386 p_max_value2 := fetched_entry_value.max_value;
1387 p_default_value2 := fetched_entry_value.default_value;
1388 --
1389 elsif entry_value_number =3 then
1390 --
1391 p_entry_value_id3 := fetched_entry_value.element_entry_value_id;
1392 p_screen_entry_value3 := fetched_entry_value.screen_entry_value;
1393 p_input_value_id3 := fetched_entry_value.input_value_id;
1394 p_uom3 := fetched_entry_value.uom;
1395 p_name3 := fetched_entry_value.name;
1396 p_hot_default_flag3 := fetched_entry_value.hot_default_flag;
1397 p_mandatory_flag3 := fetched_entry_value.mandatory_flag;
1398 p_warning_or_error3 := fetched_entry_value.warning_or_error;
1399 p_lookup_type3 := fetched_entry_value.lookup_type;
1400 p_value_set_id3 := fetched_entry_value.value_set_id;
1401 p_formula_id3 := fetched_entry_value.formula_id;
1402 p_min_value3 := fetched_entry_value.min_value;
1403 p_max_value3 := fetched_entry_value.max_value;
1404 p_default_value3 := fetched_entry_value.default_value;
1405 --
1406 elsif entry_value_number =4 then
1407 --
1411 p_uom4 := fetched_entry_value.uom;
1408 p_entry_value_id4 := fetched_entry_value.element_entry_value_id;
1409 p_screen_entry_value4 := fetched_entry_value.screen_entry_value;
1410 p_input_value_id4 := fetched_entry_value.input_value_id;
1412 p_name4 := fetched_entry_value.name;
1413 p_hot_default_flag4 := fetched_entry_value.hot_default_flag;
1414 p_mandatory_flag4 := fetched_entry_value.mandatory_flag;
1415 p_warning_or_error4 := fetched_entry_value.warning_or_error;
1416 p_lookup_type4 := fetched_entry_value.lookup_type;
1417 p_value_set_id4 := fetched_entry_value.value_set_id;
1418 p_formula_id4 := fetched_entry_value.formula_id;
1419 p_min_value4 := fetched_entry_value.min_value;
1420 p_max_value4 := fetched_entry_value.max_value;
1421 p_default_value4 := fetched_entry_value.default_value;
1422 --
1423 elsif entry_value_number =5 then
1424 --
1425 p_entry_value_id5 := fetched_entry_value.element_entry_value_id;
1426 p_screen_entry_value5 := fetched_entry_value.screen_entry_value;
1427 p_input_value_id5 := fetched_entry_value.input_value_id;
1428 p_uom5 := fetched_entry_value.uom;
1429 p_name5 := fetched_entry_value.name;
1430 p_hot_default_flag5 := fetched_entry_value.hot_default_flag;
1431 p_mandatory_flag5 := fetched_entry_value.mandatory_flag;
1432 p_warning_or_error5 := fetched_entry_value.warning_or_error;
1433 p_lookup_type5 := fetched_entry_value.lookup_type;
1434 p_value_set_id5 := fetched_entry_value.value_set_id;
1435 p_formula_id5 := fetched_entry_value.formula_id;
1436 p_min_value5 := fetched_entry_value.min_value;
1437 p_max_value5 := fetched_entry_value.max_value;
1438 p_default_value5 := fetched_entry_value.default_value;
1439 --
1440 elsif entry_value_number =6 then
1441 --
1442 p_entry_value_id6 := fetched_entry_value.element_entry_value_id;
1443 p_screen_entry_value6 := fetched_entry_value.screen_entry_value;
1444 p_input_value_id6 := fetched_entry_value.input_value_id;
1445 p_uom6 := fetched_entry_value.uom;
1446 p_name6 := fetched_entry_value.name;
1447 p_hot_default_flag6 := fetched_entry_value.hot_default_flag;
1448 p_mandatory_flag6 := fetched_entry_value.mandatory_flag;
1449 p_warning_or_error6 := fetched_entry_value.warning_or_error;
1450 p_lookup_type6 := fetched_entry_value.lookup_type;
1451 p_value_set_id6 := fetched_entry_value.value_set_id;
1452 p_formula_id6 := fetched_entry_value.formula_id;
1453 p_min_value6 := fetched_entry_value.min_value;
1454 p_max_value6 := fetched_entry_value.max_value;
1455 p_default_value6 := fetched_entry_value.default_value;
1456 --
1457 elsif entry_value_number =7 then
1458 --
1459 p_entry_value_id7 := fetched_entry_value.element_entry_value_id;
1460 p_screen_entry_value7 := fetched_entry_value.screen_entry_value;
1461 p_input_value_id7 := fetched_entry_value.input_value_id;
1462 p_uom7 := fetched_entry_value.uom;
1463 p_name7 := fetched_entry_value.name;
1464 p_hot_default_flag7 := fetched_entry_value.hot_default_flag;
1465 p_mandatory_flag7 := fetched_entry_value.mandatory_flag;
1466 p_warning_or_error7 := fetched_entry_value.warning_or_error;
1467 p_lookup_type7 := fetched_entry_value.lookup_type;
1468 p_value_set_id7 := fetched_entry_value.value_set_id;
1469 p_formula_id7 := fetched_entry_value.formula_id;
1470 p_min_value7 := fetched_entry_value.min_value;
1471 p_max_value7 := fetched_entry_value.max_value;
1472 p_default_value7 := fetched_entry_value.default_value;
1473 --
1474 elsif entry_value_number =8 then
1475 --
1476 p_entry_value_id8 := fetched_entry_value.element_entry_value_id;
1477 p_screen_entry_value8 := fetched_entry_value.screen_entry_value;
1478 p_input_value_id8 := fetched_entry_value.input_value_id;
1479 p_uom8 := fetched_entry_value.uom;
1480 p_name8 := fetched_entry_value.name;
1481 p_hot_default_flag8 := fetched_entry_value.hot_default_flag;
1482 p_mandatory_flag8 := fetched_entry_value.mandatory_flag;
1483 p_warning_or_error8 := fetched_entry_value.warning_or_error;
1484 p_lookup_type8 := fetched_entry_value.lookup_type;
1485 p_value_set_id8 := fetched_entry_value.value_set_id;
1486 p_formula_id8 := fetched_entry_value.formula_id;
1487 p_min_value8 := fetched_entry_value.min_value;
1488 p_max_value8 := fetched_entry_value.max_value;
1489 p_default_value8 := fetched_entry_value.default_value;
1490 --
1491 elsif entry_value_number =9 then
1492 --
1493 p_entry_value_id9 := fetched_entry_value.element_entry_value_id;
1494 p_screen_entry_value9 := fetched_entry_value.screen_entry_value;
1495 p_input_value_id9 := fetched_entry_value.input_value_id;
1496 p_uom9 := fetched_entry_value.uom;
1497 p_name9 := fetched_entry_value.name;
1498 p_hot_default_flag9 := fetched_entry_value.hot_default_flag;
1499 p_mandatory_flag9 := fetched_entry_value.mandatory_flag;
1500 p_warning_or_error9 := fetched_entry_value.warning_or_error;
1501 p_lookup_type9 := fetched_entry_value.lookup_type;
1502 p_value_set_id9 := fetched_entry_value.value_set_id;
1503 p_formula_id9 := fetched_entry_value.formula_id;
1504 p_min_value9 := fetched_entry_value.min_value;
1505 p_max_value9 := fetched_entry_value.max_value;
1506 p_default_value9 := fetched_entry_value.default_value;
1507 --
1511 p_screen_entry_value10 := fetched_entry_value.screen_entry_value;
1508 elsif entry_value_number =10 then
1509 --
1510 p_entry_value_id10 := fetched_entry_value.element_entry_value_id;
1512 p_input_value_id10 := fetched_entry_value.input_value_id;
1513 p_uom10 := fetched_entry_value.uom;
1514 p_name10 := fetched_entry_value.name;
1515 p_hot_default_flag10 := fetched_entry_value.hot_default_flag;
1516 p_mandatory_flag10 := fetched_entry_value.mandatory_flag;
1517 p_warning_or_error10 := fetched_entry_value.warning_or_error;
1518 p_lookup_type10 := fetched_entry_value.lookup_type;
1519 p_value_set_id10 := fetched_entry_value.value_set_id;
1520 p_formula_id10 := fetched_entry_value.formula_id;
1521 p_min_value10 := fetched_entry_value.min_value;
1522 p_max_value10 := fetched_entry_value.max_value;
1523 p_default_value10 := fetched_entry_value.default_value;
1524 --
1525 elsif entry_value_number =11 then
1526 --
1527 p_entry_value_id11 := fetched_entry_value.element_entry_value_id;
1528 p_screen_entry_value11 := fetched_entry_value.screen_entry_value;
1529 p_input_value_id11 := fetched_entry_value.input_value_id;
1530 p_uom11 := fetched_entry_value.uom;
1531 p_name11 := fetched_entry_value.name;
1532 p_hot_default_flag11 := fetched_entry_value.hot_default_flag;
1533 p_mandatory_flag11 := fetched_entry_value.mandatory_flag;
1534 p_warning_or_error11 := fetched_entry_value.warning_or_error;
1535 p_lookup_type11 := fetched_entry_value.lookup_type;
1536 p_value_set_id11 := fetched_entry_value.value_set_id;
1537 p_formula_id11 := fetched_entry_value.formula_id;
1538 p_min_value11 := fetched_entry_value.min_value;
1539 p_max_value11 := fetched_entry_value.max_value;
1540 p_default_value11 := fetched_entry_value.default_value;
1541 --
1542 elsif entry_value_number =12 then
1543 --
1544 p_entry_value_id12 := fetched_entry_value.element_entry_value_id;
1545 p_screen_entry_value12 := fetched_entry_value.screen_entry_value;
1546 p_input_value_id12 := fetched_entry_value.input_value_id;
1547 p_uom12 := fetched_entry_value.uom;
1548 p_name12 := fetched_entry_value.name;
1549 p_hot_default_flag12 := fetched_entry_value.hot_default_flag;
1550 p_mandatory_flag12 := fetched_entry_value.mandatory_flag;
1551 p_warning_or_error12 := fetched_entry_value.warning_or_error;
1552 p_lookup_type12 := fetched_entry_value.lookup_type;
1553 p_value_set_id12 := fetched_entry_value.value_set_id;
1554 p_formula_id12 := fetched_entry_value.formula_id;
1555 p_min_value12 := fetched_entry_value.min_value;
1556 p_max_value12 := fetched_entry_value.max_value;
1557 p_default_value12 := fetched_entry_value.default_value;
1558 --
1559 elsif entry_value_number =13 then
1560 --
1561 p_entry_value_id13 := fetched_entry_value.element_entry_value_id;
1562 p_screen_entry_value13 := fetched_entry_value.screen_entry_value;
1563 p_input_value_id13 := fetched_entry_value.input_value_id;
1564 p_uom13 := fetched_entry_value.uom;
1565 p_name13 := fetched_entry_value.name;
1566 p_hot_default_flag13 := fetched_entry_value.hot_default_flag;
1567 p_mandatory_flag13 := fetched_entry_value.mandatory_flag;
1568 p_warning_or_error13 := fetched_entry_value.warning_or_error;
1569 p_lookup_type13 := fetched_entry_value.lookup_type;
1570 p_value_set_id13 := fetched_entry_value.value_set_id;
1571 p_formula_id13 := fetched_entry_value.formula_id;
1572 p_min_value13 := fetched_entry_value.min_value;
1573 p_max_value13 := fetched_entry_value.max_value;
1574 p_default_value13 := fetched_entry_value.default_value;
1575 --
1576 elsif entry_value_number =14 then
1577 --
1578 p_entry_value_id14 := fetched_entry_value.element_entry_value_id;
1579 p_screen_entry_value14 := fetched_entry_value.screen_entry_value;
1580 p_input_value_id14 := fetched_entry_value.input_value_id;
1581 p_uom14 := fetched_entry_value.uom;
1582 p_name14 := fetched_entry_value.name;
1583 p_hot_default_flag14 := fetched_entry_value.hot_default_flag;
1584 p_mandatory_flag14 := fetched_entry_value.mandatory_flag;
1585 p_warning_or_error14 := fetched_entry_value.warning_or_error;
1586 p_lookup_type14 := fetched_entry_value.lookup_type;
1587 p_value_set_id14 := fetched_entry_value.value_set_id;
1588 p_formula_id14 := fetched_entry_value.formula_id;
1589 p_min_value14 := fetched_entry_value.min_value;
1590 p_max_value14 := fetched_entry_value.max_value;
1591 p_default_value14 := fetched_entry_value.default_value;
1592 --
1593 elsif entry_value_number =15 then
1594 --
1595 p_entry_value_id15 := fetched_entry_value.element_entry_value_id;
1596 p_screen_entry_value15 := fetched_entry_value.screen_entry_value;
1597 p_input_value_id15 := fetched_entry_value.input_value_id;
1598 p_uom15 := fetched_entry_value.uom;
1599 p_name15 := fetched_entry_value.name;
1600 p_hot_default_flag15 := fetched_entry_value.hot_default_flag;
1601 p_mandatory_flag15 := fetched_entry_value.mandatory_flag;
1602 p_warning_or_error15 := fetched_entry_value.warning_or_error;
1603 p_lookup_type15 := fetched_entry_value.lookup_type;
1604 p_value_set_id15 := fetched_entry_value.value_set_id;
1605 p_formula_id15 := fetched_entry_value.formula_id;
1606 p_min_value15 := fetched_entry_value.min_value;
1607 p_max_value15 := fetched_entry_value.max_value;
1608 p_default_value15 := fetched_entry_value.default_value;
1609 --
1610 else
1611 exit;
1612 --
1613 end if;
1614 --
1615 end LOOP;
1616 --
1617 CLOSE set_of_entry_values;
1618 --
1619 end get_entry_value_details;
1620 -------------------------------------------------------------------------------
1621 function get_original_date_earned (
1622 --
1623 -- get_original_date_earned added as part of Enhancement 3665715.
1624 -- Returns the original date earned date pertaining to a retropay entry.
1625 --
1626 p_element_entry_id in number) return date
1627 --
1628 is
1629 --
1630 l_original_date_earned date;
1631 --
1632 cursor csr_original_date_earned (ee_id number) is
1633 select PAY_ACT.date_earned
1634 from pay_entry_process_details ENTRY_PROC,
1635 pay_assignment_actions ASGT_ACT,
1636 pay_payroll_actions PAY_ACT
1637 where ENTRY_PROC.element_entry_id = ee_id
1638 and ENTRY_PROC.source_asg_action_id = ASGT_ACT.assignment_action_id
1639 and ASGT_ACT.payroll_action_id = PAY_ACT.payroll_action_id;
1640 --
1641 begin
1642 --
1643 open csr_original_date_earned (p_element_entry_id);
1644 fetch csr_original_date_earned into l_original_date_earned;
1645 close csr_original_date_earned;
1646 --
1647 return l_original_date_earned;
1648 --
1649 end get_original_date_earned;
1650 -------------------------------------------------------------------------------
1651 end PAY_PAYWSMEE_PKG;
[Home] [Help]