1 PACKAGE BODY per_pds_utils AS
2 /* $Header: pepdsutl.pkb 120.7 2012/01/10 10:21:21 srannama ship $ */
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 -- Bug 12431922
107 if procd_le_exists_fpd%found then
108 l_later_le_exists := true;
109 end if;
110 close procd_le_exists_fpd;
111 end if;
112 --
113 if l_later_le_exists then
114 fnd_message.set_name('BEN', 'BEN_94623_PREV_LE_EXISTS');
115 /* Commented and added as a part of Bug#10239011 Starts
116 fnd_message.set_token('DATE',fnd_date.date_to_chardate(p_new_start_date)); */
117 fnd_message.set_token('DATE',fnd_date.date_to_chardate(p_new_start_date, calendar_aware => FND_DATE.calendar_aware_alt));
118 /* Commented and added as a part of Bug#10239011 Ends */
119 fnd_message.raise_error;
120 end if;
121 -- End Benefits Validations
122 --
123 --
124 --
125 --
126 -- {Product teams should place code calls ABOVE THIS POINT}
127 --
128 hr_utility.set_location('Leaving:'|| l_proc, 9999);
129 --
130 EXCEPTION
131 --
132 WHEN OTHERS THEN
133 --
134 -- An unexpected error has occurred
135 -- No OUT parameters need to be set
136 -- No cursors need to be closed
137 --
138 ROLLBACK TO check_move_hire_date;
139 RAISE;
140 --
141 END check_move_hire_date;
142 --
143 -- ----------------------------------------------------------------------------
144 -- |----------------------------< Hr_Run_Alu_Ee >-----------------------------|
145 -- ----------------------------------------------------------------------------
146 -- {Start Of Comments}
147 --
148 -- Description:
149 -- This business support process provides a hook for the Payroll team to use
150 -- to invoke their new API to perform any processing that accounts for the
151 -- effect on Payroll Actions of changing hire date.
152 --
153 -- Prerequisites:
154 -- The person assignments and periods of service must already be present.
155 --
156 -- In Parameters:
157 -- Name Reqd Type Description
158 -- p_person_id Yes number Identifier for the person
159 -- p_old_start_date Yes date Old Hire Date
160 -- p_new_start_date Yes date New Hire Date
161 -- p_type Yes varchar2 Type of person
162 --
163 -- Post Success:
164 -- No error is raised if the new hire date is valid
165 --
166 -- Post Failure:
167 -- An error is raised and control returned if the new hire date is not
168 -- valid.
169 --
170 -- Access Status:
171 -- For Oracle Internal use only.
172 --
173 -- {End Of Comments}
174 --
175 PROCEDURE hr_run_alu_ee
176 (p_person_id IN NUMBER
177 ,p_old_start_date IN DATE
178 ,p_new_start_date IN DATE
179 ,p_type IN VARCHAR2
180 ) IS
181 --
182 l_proc VARCHAR2(80) := g_package||'hr_run_alu_ee';
183 --
184 BEGIN
185 --
186 hr_utility.set_location('Entering:'|| l_proc, 5);
187 --
188 -- Issue a savepoint.
189 --
190 SAVEPOINT hr_run_alu_ee;
191 --
192 hr_utility.set_location(l_proc, 10);
193 --
194 -- {PAY team should place code calls BELOW THIS POINT}
195 --
196 --
197 --
198 --
199 -- {PAY team should place code calls ABOVE THIS POINT}
200 --
201 hr_utility.set_location('Leaving:'|| l_proc, 9999);
202 --
203 EXCEPTION
204 --
205 WHEN OTHERS THEN
206 --
207 -- An unexpected error has occurred
208 -- No OUT parameters need to be set
209 -- No cursors need to be closed
210 --
211 ROLLBACK TO hr_run_alu_ee;
212 RAISE;
213 --
214 END hr_run_alu_ee;
215 --
216 -- ----------------------------------------------------------------------------
217 -- |-----------------------< Move_Elements_With_Fpd >-------------------------|
218 -- ----------------------------------------------------------------------------
219 -- {Start Of Comments}
220 --
221 -- Description:
222 -- This business procedure provides a hook for the Payroll team to use
223 -- to invoke their new API to handle the updation of element entries in
224 -- sync with changes to FPD.
225 --
226 -- Prerequisites:
227 -- The person assignments and periods of service must already be present.
228 --
229 -- In Parameters:
230 -- Name Reqd Type Description
231 -- p_assignment_id Yes number Assignment Identifier
232 -- p_periods_of_service_id Yes number Period Of Service Identifier
233 -- p_old_final_process_date Yes date Old Final Process Date
234 -- p_new_final_process_date Yes date New Final Process Date
235 --
236 -- Post Success:
237 -- Dates for both recurring and non-recurring element entries are in sync
238 -- with the new FPD
239 --
240 -- Post Failure:
241 -- An error is raised and control returned
242 --
243 -- Access Status:
244 -- For Oracle Internal use only.
245 --
246 -- {End Of Comments}
247 --
248 PROCEDURE move_elements_with_fpd
249 (p_assignment_id IN NUMBER
250 ,p_periods_of_service_id IN NUMBER
251 ,p_old_final_process_date IN DATE
252 ,p_new_final_process_date IN DATE
253 ) IS
254 --
255 l_proc VARCHAR2(80) := g_package||'move_elements_with_fpd';
256 --
257 BEGIN
258 --
259 hr_utility.set_location('Entering:'|| l_proc, 5);
260 --
261 -- Issue a savepoint.
262 --
263 SAVEPOINT move_elements_with_fpd;
264 --
265 hr_utility.set_location(l_proc, 10);
266 --
267 -- <Pay Code>
268 --
269 hrentmnt.move_fpd_entries
270 (
271 p_assignment_id => p_assignment_id
272 ,p_period_of_service_id => p_periods_of_service_id
273 ,p_new_final_process_date => p_new_final_process_date
274 ,p_old_final_process_date => p_old_final_process_date
275 );
276 --
277 -- <Pay Code>
278 --
279 hr_utility.set_location('Leaving:'|| l_proc, 9999);
280 --
281 EXCEPTION
282 --
283 WHEN OTHERS THEN
284 --
285 -- An unexpected error has occurred
286 -- No OUT parameters need to be set
287 -- No cursors need to be closed
288 --
289 ROLLBACK TO move_elements_with_fpd;
290 RAISE;
291 --
292 END move_elements_with_fpd;
293 --
294 END per_pds_utils;
[Home] [Help]