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