1 PACKAGE BODY per_pds_utils AS
2 /* $Header: pepdsutl.pkb 120.4 2006/05/09 08:43:19 mkataria noship $ */
3 --
4 -- Package Variables
5 --
6 g_package VARCHAR2(30) := 'per_pds_utils.';
7 --
8 -- ----------------------------------------------------------------------------
9 -- |-------------------------< Check_Move_Hire_Date >-------------------------|
10 -- ----------------------------------------------------------------------------
11 -- {Start Of Comments}
12 --
13 -- Description:
14 -- This business support process provides a hook for other product teams to
15 -- invoke their specific code to handle the change of hire date event.
16 --
17 -- Prerequisites:
18 -- The person assignments and periods of service must already be present.
19 --
20 -- In Parameters:
21 -- Name Reqd Type Description
22 -- p_person_id Yes number Identifier for the person
23 -- p_old_start_date Yes date Old Hire Date
24 -- p_new_start_date Yes date New Hire Date
25 -- p_type Yes varchar2 Type of person
26 --
27 -- Post Success:
28 -- No error is raised if the new hire date is valid
29 --
30 -- Post Failure:
31 -- An error is raised and control returned if the new hire date is not
32 -- valid.
33 --
34 -- Access Status:
35 -- For Oracle Internal use only.
36 --
37 -- {End Of Comments}
38 --
39 PROCEDURE check_move_hire_date
40 (p_person_id IN NUMBER
41 ,p_old_start_date IN DATE
42 ,p_new_start_date IN DATE
43 ,p_type IN VARCHAR2
44 ) IS
45 /*Cursors for BEN validations*/
46 cursor procd_le_exists(p_person_id in number
47 ,p_effective_date in date) is
48 select 'Y'
49 from ben_per_in_ler pil
50 where pil.person_id = p_person_id
51 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
52 and pil.lf_evt_ocrd_dt < p_effective_date;
53 --
54 cursor procd_le_exists_fpd(p_person_id in number
55 ,p_effective_date in date
56 ,p_max_fpd in date) is
57 select 'Y'
58 from ben_per_in_ler pil
59 where pil.person_id = p_person_id
60 and pil.per_in_ler_stat_cd in ('STRTD','PROCD')
61 and pil.lf_evt_ocrd_dt < p_effective_date
62 and pil.lf_evt_ocrd_dt > p_max_fpd;
63 --
64 cursor max_fpd(p_person_id in number) is
65 select max(final_process_date)
66 from per_all_assignments_f asg,per_periods_of_service pps
67 where
68 asg.period_of_service_id = pps.period_of_service_id
69 and asg.effective_end_date <> hr_api.g_eot
70 and asg.person_id = p_person_id;
71 --
72 l_dummy varchar2(30);
73 l_max_fpd date;
74 l_later_le_exists boolean := false;
75 /* End cursors for BEN*/
76 --
77 l_proc VARCHAR2(80) := g_package||'check_move_hire_date';
78 --
79 BEGIN
80 --
81 hr_utility.set_location('Entering:'|| l_proc, 5);
82 --
83 -- Issue a savepoint even though this is a check routine to
84 -- handle potential DB writes that may be issued by routines
85 -- invoked from herein by any product team.
86 --
87 SAVEPOINT check_move_hire_date;
88 --
89 hr_utility.set_location(l_proc, 10);
90 --
91 -- {Product teams should place code calls BELOW THIS POINT}
92 -- Benefits Validations
93 open max_fpd(p_person_id);
94 fetch max_fpd into l_max_fpd;
95 close max_fpd;
96 if l_max_fpd is null then
97 open procd_le_exists(p_person_id, p_new_start_date);
98 fetch procd_le_exists into l_dummy;
99 if procd_le_exists%found then
100 l_later_le_exists := true;
101 end if;
102 close procd_le_exists;
103 else
104 open procd_le_exists_fpd(p_person_id, p_new_start_date,l_max_fpd);
105 fetch procd_le_exists_fpd into l_dummy;
106 if procd_le_exists%found then
107 l_later_le_exists := true;
108 end if;
109 close procd_le_exists_fpd;
110 end if;
111 --
112 if l_later_le_exists then
113 fnd_message.set_name('BEN', 'BEN_94623_PREV_LE_EXISTS');
114 fnd_message.set_token('DATE',fnd_date.date_to_chardate(p_new_start_date));
115 fnd_message.raise_error;
116 end if;
117 -- End Benefits Validations
118 --
119 --
120 --
121 --
122 -- {Product teams should place code calls ABOVE THIS POINT}
123 --
124 hr_utility.set_location('Leaving:'|| l_proc, 9999);
125 --
126 EXCEPTION
127 --
128 WHEN OTHERS THEN
129 --
130 -- An unexpected error has occurred
131 -- No OUT parameters need to be set
132 -- No cursors need to be closed
133 --
134 ROLLBACK TO check_move_hire_date;
135 RAISE;
136 --
137 END check_move_hire_date;
138 --
139 -- ----------------------------------------------------------------------------
140 -- |----------------------------< Hr_Run_Alu_Ee >-----------------------------|
141 -- ----------------------------------------------------------------------------
142 -- {Start Of Comments}
143 --
144 -- Description:
145 -- This business support process provides a hook for the Payroll team to use
146 -- to invoke their new API to perform any processing that accounts for the
147 -- effect on Payroll Actions of changing hire date.
148 --
149 -- Prerequisites:
150 -- The person assignments and periods of service must already be present.
151 --
152 -- In Parameters:
153 -- Name Reqd Type Description
154 -- p_person_id Yes number Identifier for the person
155 -- p_old_start_date Yes date Old Hire Date
156 -- p_new_start_date Yes date New Hire Date
157 -- p_type Yes varchar2 Type of person
158 --
159 -- Post Success:
160 -- No error is raised if the new hire date is valid
161 --
162 -- Post Failure:
163 -- An error is raised and control returned if the new hire date is not
164 -- valid.
165 --
166 -- Access Status:
167 -- For Oracle Internal use only.
168 --
169 -- {End Of Comments}
170 --
171 PROCEDURE hr_run_alu_ee
172 (p_person_id IN NUMBER
173 ,p_old_start_date IN DATE
174 ,p_new_start_date IN DATE
175 ,p_type IN VARCHAR2
176 ) IS
177 --
178 l_proc VARCHAR2(80) := g_package||'hr_run_alu_ee';
179 --
180 BEGIN
181 --
182 hr_utility.set_location('Entering:'|| l_proc, 5);
183 --
184 -- Issue a savepoint.
185 --
186 SAVEPOINT hr_run_alu_ee;
187 --
188 hr_utility.set_location(l_proc, 10);
189 --
190 -- {PAY team should place code calls BELOW THIS POINT}
191 --
192 --
193 --
194 --
195 -- {PAY team should place code calls ABOVE THIS POINT}
196 --
197 hr_utility.set_location('Leaving:'|| l_proc, 9999);
198 --
199 EXCEPTION
200 --
201 WHEN OTHERS THEN
202 --
203 -- An unexpected error has occurred
204 -- No OUT parameters need to be set
205 -- No cursors need to be closed
206 --
207 ROLLBACK TO hr_run_alu_ee;
208 RAISE;
209 --
210 END hr_run_alu_ee;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |-----------------------< Move_Elements_With_Fpd >-------------------------|
214 -- ----------------------------------------------------------------------------
215 -- {Start Of Comments}
216 --
217 -- Description:
218 -- This business procedure provides a hook for the Payroll team to use
219 -- to invoke their new API to handle the updation of element entries in
220 -- sync with changes to FPD.
221 --
222 -- Prerequisites:
223 -- The person assignments and periods of service must already be present.
224 --
225 -- In Parameters:
226 -- Name Reqd Type Description
227 -- p_assignment_id Yes number Assignment Identifier
228 -- p_periods_of_service_id Yes number Period Of Service Identifier
229 -- p_old_final_process_date Yes date Old Final Process Date
230 -- p_new_final_process_date Yes date New Final Process Date
231 --
232 -- Post Success:
233 -- Dates for both recurring and non-recurring element entries are in sync
234 -- with the new FPD
235 --
236 -- Post Failure:
237 -- An error is raised and control returned
238 --
239 -- Access Status:
240 -- For Oracle Internal use only.
241 --
242 -- {End Of Comments}
243 --
244 PROCEDURE move_elements_with_fpd
245 (p_assignment_id IN NUMBER
246 ,p_periods_of_service_id IN NUMBER
247 ,p_old_final_process_date IN DATE
248 ,p_new_final_process_date IN DATE
249 ) IS
250 --
251 l_proc VARCHAR2(80) := g_package||'move_elements_with_fpd';
252 --
253 BEGIN
254 --
255 hr_utility.set_location('Entering:'|| l_proc, 5);
256 --
257 -- Issue a savepoint.
258 --
259 SAVEPOINT move_elements_with_fpd;
260 --
261 hr_utility.set_location(l_proc, 10);
262 --
263 -- <Pay Code>
264 --
265 hrentmnt.move_fpd_entries
266 (
267 p_assignment_id => p_assignment_id
268 ,p_period_of_service_id => p_periods_of_service_id
269 ,p_new_final_process_date => p_new_final_process_date
270 ,p_old_final_process_date => p_old_final_process_date
271 );
272 --
273 -- <Pay Code>
274 --
275 hr_utility.set_location('Leaving:'|| l_proc, 9999);
276 --
277 EXCEPTION
278 --
279 WHEN OTHERS THEN
280 --
281 -- An unexpected error has occurred
282 -- No OUT parameters need to be set
283 -- No cursors need to be closed
284 --
285 ROLLBACK TO move_elements_with_fpd;
286 RAISE;
287 --
288 END move_elements_with_fpd;
289 --
290 END per_pds_utils;
[Home] [Help]