[Home] [Help]
136: --
137: cursor entry_record_exists(p_update_ee_id in number,
138: p_effective_end_date in date) is
139: select effective_end_date
140: from pay_element_entries_f
141: where element_entry_id = p_update_ee_id
142: and effective_start_date = p_effective_date
143: and effective_end_date = p_effective_end_date;
144: --
204: link_id,
205: lookup_type,
206: uom,
207: input_curr
208: from pay_element_entries_f pee,
209: pay_element_links_f pel,
210: pay_element_types_f pet,
211: pay_input_values_f piv,
212: per_all_assignments_f asg
309:
310: /* bug 6655722
311: select max(pee.effective_end_date)
312: into max_effend
313: from pay_element_entries_f pee
314: where pee.element_entry_id = update_ee_id;
315: */
316: --
317: begin
330: -- end date.
331:
332: SELECT effective_end_date
333: INTO l_effective_end_date
334: FROM pay_element_entries_f
335: WHERE element_entry_id = update_ee_id
336: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
337: --
338: /*
335: WHERE element_entry_id = update_ee_id
336: AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
337: --
338: /*
339: delete from pay_element_entries_f pee
340: where pee.element_entry_id = update_ee_id
341: and pee.effective_start_date > val_date;
342: */
343: --
397: --
398: close get_entry_values;
399:
400: -- Bug 7194700
401: -- Check if a record exists in PAY_ELEMENT_ENTRIES_F with the same effective_start_date
402: -- and effective_end_date. Proceed with the Update and Insert DMLs only if no such
403: -- record is there.
404:
405: OPEN entry_record_exists(update_ee_id,l_effective_end_date);
409: --
410: -- Now, update the effective_end_date of existing entry.
411: -- Note : using val_date.
412: hr_utility.set_location(c_indent,40);
413: update pay_element_entries_f pee
414: set pee.effective_end_date = (p_effective_date - 1)
415: where pee.element_entry_id = update_ee_id
416: and val_date between
417: pee.effective_start_date and pee.effective_end_date;
423: -- Bugfix 3110853
424: -- Derive the OVN before inserting
425: --
426: ovn := dt_api.get_object_version_number (
427: 'PAY_ELEMENT_ENTRIES_F',
428: 'ELEMENT_ENTRY_ID',
429: update_ee_id
430: );
431: --
428: 'ELEMENT_ENTRY_ID',
429: update_ee_id
430: );
431: --
432: insert into pay_element_entries_f (
433: element_entry_id,
434: effective_start_date,
435: effective_end_date,
436: cost_allocation_keyflex_id,
583: 0,
584: 0,
585: pee.created_by,
586: pee.creation_date
587: from pay_element_entries_f pee
588: where pee.element_entry_id = update_ee_id
589: and (p_effective_date - 1) between
590: pee.effective_start_date and pee.effective_end_date;
591: --
757: select pee.element_entry_id,
758: pel.element_link_id,
759: pee.effective_start_date
760: into stop_ee_id, link_id, stop_ee_start_date
761: from pay_element_entries_f pee,
762: pay_element_links_f pel,
763: per_all_assignments_f asg
764: where asg.assignment_id = p_assignment_id
765: and p_date_earned between
795: into v_error_flag
796: from sys.dual
797: where exists (
798: select null
799: from pay_element_entries_f pee
800: where pee.assignment_id = p_assignment_id
801: and pee.element_link_id = link_id
802: and pee.target_entry_id = stop_ee_id
803: and pee.effective_start_date <= c_eot
827: -- Set the Continuous Calc override flag, so that the trigger points
828: -- are not fired.
829: pay_continuous_calc.g_override_cc := TRUE;
830: --
831: delete from pay_element_entries_f pee
832: where pee.element_entry_id = stop_ee_id
833: and pee.effective_start_date > p_date_earned;
834: --
835: update pay_element_entries_f pee
831: delete from pay_element_entries_f pee
832: where pee.element_entry_id = stop_ee_id
833: and pee.effective_start_date > p_date_earned;
834: --
835: update pay_element_entries_f pee
836: set pee.effective_end_date = p_date_earned,
837: pee.prev_upd_action_id = pee.updating_action_id,
838: pee.updating_action_id = p_assignment_action_id,
839: pee.updating_action_type = 'S'
2946: and pay_pac.effective_date between
2947: pay_asg.effective_start_date and pay_asg.effective_end_date
2948: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
2949: and exists (select null from
2950: pay_element_entries_f p_pee,
2951: pay_element_entry_values_f p_pev,
2952: pay_element_entry_values_f p_pev2,
2953: pay_input_values_f p_piv,
2954: pay_input_values_f p_piv2
2977: where p_pbg.business_group_id = pay_pac.business_group_id
2978: and p_pbg.legislation_code = p_plr.legislation_code
2979: and TRANSLATE(upper(p_plr.rule_type), ''-'',''_'') = ''PAI_END_DATE'')
2980: and not exists (select null
2981: from pay_element_entries_f p_pe2
2982: where p_pe2.assignment_id = pay_asg.assignment_id
2983: and p_pe2.element_type_id =
2984: (select to_number(p_plr2.rule_mode)
2985: from pay_legislation_rules p_plr2, per_business_groups_perf p_pbg2
3005: pay_asg.effective_start_date and pay_asg.effective_end_date
3006: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3007: and exists
3008: (select null
3009: from pay_element_entries_f pay_pee,
3010: pay_element_types_f pay_pet,
3011: pay_element_entry_values_f pay_pev,
3012: pay_element_entry_values_f pay_pev2,
3013: pay_input_values_f pay_piv,
3055: and pay_inc.include_or_exclude = ''I''
3056: and pay_pos.period_of_service_id = pay_asg.period_of_service_id
3057: and exists
3058: (select null
3059: from pay_element_entries_f pay_pee,
3060: pay_element_types_f pay_pet,
3061: pay_element_entry_values_f pay_pev,
3062: pay_element_entry_values_f pay_pev2,
3063: pay_input_values_f pay_piv,