[Home] [Help]
PACKAGE BODY: APPS.PER_IE_VEHICLE_ALLOC_RULES
Source
1 PACKAGE BODY per_ie_vehicle_alloc_rules AS
2 /* $Header: peievehd.pkb 120.0.12000000.4 2007/02/23 09:24:23 rbhardwa ship $ */
3
4 PROCEDURE element_end_date_update (
5 p_vehicle_allocation_id IN NUMBER,
6 p_effective_date IN DATE) IS
7
8 l_proc VARCHAR2(30) := 'PER_IE_VEHICLE_ALLOC_RULES';
9
10 CURSOR csr_vehicle_element_entry IS
11 SELECT pee.element_entry_id,
12 max(pee.object_version_number),
13 max(ppa.effective_date),
14 max(ppa.date_earned) -- 5872123
15 FROM pay_element_types_f pet,
16 pay_element_entry_values_f peev,
17 pay_input_values_f piv,
18 pay_element_entries_f pee,
19 pqp_vehicle_allocations_f pva,
20 pay_element_links_f pel,
21 pay_payroll_actions ppa,
22 pay_assignment_actions paas,
23 pay_run_results prr,
24 per_all_assignments_f paa
25 WHERE pee.assignment_id = pva.assignment_id
26 AND p_effective_date BETWEEN
27 pee.effective_start_date AND pee.effective_end_date
28 AND pel.element_type_id = pet.element_type_id -- Bug No.3648575
29 AND pel.element_link_id = pee.element_link_id -- Bug No.3648575
30 AND pee.element_type_id + 0 = pet.element_type_id -- Bug No.3648575
31 AND pet.element_name = 'IE BIK Company Vehicle'
32 AND p_effective_date BETWEEN
33 pet.effective_start_date AND pet.effective_end_date -- Bug No.3648575
34 AND peev.element_entry_id = pee.element_entry_id
35 AND p_effective_date BETWEEN
36 peev.effective_start_date AND peev.effective_end_date
37 AND peev.screen_entry_value =to_char(p_vehicle_allocation_id)
38 AND peev.input_value_id = piv.input_value_id
39 AND piv.name = 'Vehicle Allocation'
40 AND p_effective_date BETWEEN
41 piv.effective_start_date AND piv.effective_end_date
42 AND pva.vehicle_allocation_id = p_vehicle_allocation_id
43 AND p_effective_date BETWEEN
44 pva.effective_start_date AND pva.effective_end_date
45 AND p_effective_date BETWEEN
46 pel.effective_start_date AND pel.effective_end_date
47 AND prr.element_entry_id=pee.element_entry_id
48 AND prr.element_type_id=pet.element_type_id
49 AND prr.assignment_action_id=paas.assignment_action_id
50 AND paas.payroll_action_id=ppa.payroll_action_id
51 AND paas.assignment_id=paa.assignment_id
52 AND paa.payroll_id=ppa.payroll_id
53 AND ppa.action_type in ('R','Q')
54 AND ppa.action_status='C'
55 AND paa.assignment_id=pva.assignment_id
56 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
57 AND ppa.effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
58 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
59 AND ppa.effective_date BETWEEN peev.effective_start_date AND peev.effective_end_date
60 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
61 AND ppa.effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
62 group by pee.element_entry_id;
63
64 CURSOR csr_pay_period(p_max_effective_date in DATE,p_max_date_earned in DATE) IS -- 5872123
65 SELECT min(ptp.end_date)
66 FROM per_time_periods ptp,
67 per_all_assignments_f paa,
68 pqp_vehicle_allocations_f pva
69 WHERE ptp.payroll_id = paa.payroll_id
70 AND paa.assignment_id = pva.assignment_id
71 AND pva.vehicle_allocation_id = p_vehicle_allocation_id
72 -- AND ptp.regular_payment_date>p_max_effective_date; -- 5872123
73 AND ptp.end_date>p_max_date_earned
74 AND p_max_date_earned not between ptp.start_date and ptp.end_date;
75
76
77 l_effective_end_date DATE;
78 l_effective_start_date DATE;
79 l_element_entry_id NUMBER;
80 l_object_version_number NUMBER := 0;
81 l_period_end_date DATE;
82 l_delete_warning BOOLEAN;
83 l_max_effective_date DATE;
84 l_max_date_earned DATE; -- 5872123
85
86 BEGIN
87
88 /* Added for GSI Bug 5472781 */
89 IF hr_utility.chk_product_install('Oracle Human Resources', 'IE') THEN
90
91 hr_utility.set_location('Entering ' || l_proc,10);
92 hr_utility.set_location('p_vehicle_allocation_id = ' ||
93 p_vehicle_allocation_id,10);
94 hr_utility.set_location('p_effective_date = ' || p_effective_date,10);
95
96 OPEN csr_vehicle_element_entry;
97 FETCH csr_vehicle_element_entry
98 INTO l_element_entry_id,
99 l_object_version_number,
100 l_max_effective_date,
101 l_max_date_earned; -- 5872123
102
103 hr_utility.set_location('l_effective_start_date = ' ||
104 to_char(l_element_entry_id),30);
105 hr_utility.set_location('l_effective_end_date = ' ||
106 to_char(l_object_version_number),30);
107 hr_utility.set_location('l_effective_end_date = ' ||
108 to_char(l_max_effective_date),30);
109
110 IF csr_vehicle_element_entry%FOUND THEN
111 OPEN csr_pay_period(l_max_effective_date,l_max_date_earned); -- 5872123
112 FETCH csr_pay_period INTO l_period_end_date;
113 CLOSE csr_pay_period;
114
115 hr_utility.set_location('l_element_entry_id = ' || l_element_entry_id,20);
116 hr_utility.set_location('l_period_end_date = ' || l_period_end_date,20);
117 hr_utility.set_location('Calling delete_element_entry',25);
118
119 pay_element_entry_api.delete_element_entry (
120 p_validate => FALSE,
121 p_datetrack_delete_mode => 'DELETE',
122 p_effective_date => l_period_end_date,
123 p_element_entry_id => l_element_entry_id,
124 p_object_version_number => l_object_version_number,
125 p_effective_start_date => l_effective_start_date,
126 p_effective_end_date => l_effective_end_date,
127 p_delete_warning => l_delete_warning
128 );
129
130 hr_utility.set_location('l_effective_start_date = ' ||
131 l_effective_start_date,30);
132 hr_utility.set_location('l_effective_end_date = ' ||
133 l_effective_end_date,30);
134 END IF;
135
136 CLOSE csr_vehicle_element_entry;
137 hr_utility.set_location('Leaving ' || l_proc,40);
138
139 END IF; /* Added for GSI Bug 5472781 */
140
141 END element_end_date_update;
142 END per_ie_vehicle_alloc_rules;