1 PACKAGE BODY PAY_ORG_PAY_METH_USAGES_F_PKG as
2 /* $Header: pyopu01t.pkb 120.0.12010000.2 2009/04/14 06:45:54 parusia ship $ */
3 --
4 --
5 -----------------------------------------------------------------------------
6 -- Name --
7 -- opmu_end_date --
8 -- Purpose --
9 -- Returns the date effective end date of an OPMU that is about to be --
10 -- created. This takes into account future opmu's and also the end date --
11 -- of the opm. --
12 -- Arguments --
13 -- See below. --
14 -- Notes --
15 -- --
16 -----------------------------------------------------------------------------
17 --
18 function opmu_end_date
19 (
20 p_org_pay_method_usage_id number,
21 p_payroll_id number,
22 p_org_payment_method_id number,
23 p_session_date date,
24 p_validation_start_date date
25 ) return date is
26 --
27 v_next_opmu_start_date date;
28 v_max_payroll_end_date date;
29 v_max_opm_end_date date;
30 v_opmu_end_date date;
31 --
32 begin
33 --
34 -- Get the start date of the earliest future opmu if it exists.
35 begin
36 select min(opmu.effective_start_date)
37 into v_next_opmu_start_date
38 from pay_org_pay_method_usages_f opmu
39 where opmu.payroll_id = p_payroll_id
40 and opmu.org_payment_method_id = p_org_payment_method_id
41 and opmu.effective_end_date >= p_session_date
42 and opmu.org_pay_method_usage_id <> nvl(p_org_pay_method_usage_id,0);
43 exception
44 when no_data_found then null;
45 end;
46 --
47 -- If there are no future opmus , get the least of hte max end date of the
48 -- payroll and opm..
49 if v_next_opmu_start_date is null then
50 --
51 -- Get payroll end date
52 begin
53 select max(prl.effective_end_date)
54 into v_max_payroll_end_date
55 from pay_all_payrolls_f prl
56 where prl.payroll_id = p_payroll_id;
57 exception
58 when no_data_found then
59 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
60 hr_utility.set_message_token('PROCEDURE',
61 'pay_payrolls_f_pkg.opmu_end_date');
62 hr_utility.set_message_token('STEP','1');
63 hr_utility.raise_error;
64 end;
65 --
66 -- Get opm end date
67 begin
68 select max(opm.effective_end_date)
69 into v_max_opm_end_date
70 from pay_org_payment_methods_f opm
71 where opm.org_payment_method_id = p_org_payment_method_id;
72 exception
73 when no_data_found then
74 hr_utility.set_message(801,'HR_6153_ALL_PROCEDURE_FAIL');
75 hr_utility.set_message_token('PROCEDURE',
76 'pay_payrolls_f_pkg.opmu_end_date');
77 hr_utility.set_message_token('STEP','2');
78 hr_utility.raise_error;
79 end;
80 --
81 -- Use the most restrictive date.
82 v_opmu_end_date := least(v_max_payroll_end_date,v_max_opm_end_date);
83 --
84 else
85 --
86 -- Set the date to the day before the next opmu.
87 v_opmu_end_date := v_next_opmu_start_date - 1;
88 --
89 end if;
90 --
91 -- Trying to open up an opmu that would either overlap with an
92 -- existing opmu or extend beyond the lifetime of the opm or payroll
93 -- on which it is based.
94 if v_opmu_end_date < p_validation_start_date then
95 --
96 -- No future opmu was found.
97 if v_next_opmu_start_date is null then
98 --
99 -- Trying to extend the end date of the opmu past the end date
100 -- of the payroll.
101 if v_opmu_end_date = v_max_payroll_end_date then
102 --
103 hr_utility.set_message(801, 'HR_6868_PAY_NO_DNC_PAY');
104 --
105 -- Trying to extend the end date of the opmu past the end date
106 -- of the opm.
107 else
108 --
109 hr_utility.set_message(801, 'HR_6870_PAY_NO_DNC_PAYM');
110 --
111 end if;
112 --
113 -- Trying to extend the end date of the opmu such that it will
114 -- overlap with an existing opmu.
115 else
116 --
117 hr_utility.set_message(801, 'HR_6869_PAY_NO_DNC_OPMU');
118 --
119 end if;
120 --
121 hr_utility.raise_error;
122 --
123 end if;
124 --
125 return v_opmu_end_date;
126 --
127 end opmu_end_date;
128 --
129 -----------------------------------------------------------------------------
130 -- Name --
131 -- validate_delete_opmu --
132 -- Purpose --
133 -- Checks to see if it is valid to delete the opmu. --
134 -- Arguments --
135 -- See below. --
136 -- Notes --
137 -- --
138 -----------------------------------------------------------------------------
139 --
140 procedure validate_delete_opmu
141 (
142 p_payroll_id number,
143 p_org_payment_method_id number,
144 p_effective_start_date date,
145 p_effective_end_date date,
146 p_dt_delete_mode varchar2,
147 p_validation_start_date date,
148 p_validation_end_date date
149 ) is
150 --
151 cursor csr_dflt_pay_meth
152 (
153 p_payroll_id number,
154 p_org_payment_method_id number,
155 p_validation_start_date date,
156 p_validation_end_date date
157 ) is
158 select prl.payroll_id
159 from pay_all_payrolls_f prl
160 where prl.payroll_id = p_payroll_id
161 and prl.default_payment_method_id = p_org_payment_method_id
162 and prl.effective_start_date <= p_validation_end_date
163 and prl.effective_end_date >= p_validation_start_date;
164 --
165 cursor csr_ppm
166 (
167 p_payroll_id number,
168 p_org_payment_method_id number,
169 p_validation_start_date date,
170 p_validation_end_date date
171 ) is
172 select ppm.personal_payment_method_id
173 from per_all_assignments_f asg,
174 pay_personal_payment_methods_f ppm
175 where asg.payroll_id = p_payroll_id
176 and ppm.assignment_id = asg.assignment_id
177 and ppm.org_payment_method_id = p_org_payment_method_id
178 and ppm.effective_start_date <= asg.effective_end_date
179 and ppm.effective_end_date >= asg.effective_start_date
180 and ppm.effective_start_date <= p_validation_end_date
181 and ppm.effective_end_date >= p_validation_start_date
182 /* Checks added for bug 8419878 */
183 and asg.effective_start_date <= p_validation_end_date
184 and asg.effective_end_date >= p_validation_start_date;
185 --
186 cursor csr_pre_pay
187 (
188 p_payroll_id number,
189 p_org_payment_method_id number,
190 p_validation_start_date date,
191 p_validation_end_date date
192 ) is
193 select pp.pre_payment_id
194 from pay_payroll_actions pa,
195 pay_assignment_actions aa,
196 pay_pre_payments pp
197 where pa.payroll_id = p_payroll_id
198 and aa.payroll_action_id = pa.payroll_action_id
199 and pp.assignment_action_id = aa.assignment_action_id
200 and pp.org_payment_method_id = p_org_payment_method_id
201 and pa.action_type in ('P', 'U')
202 and pa.effective_date between p_validation_start_date
203 and p_validation_end_date;
204 --
205 v_dummy_id number;
206 v_validation_start_date date;
207 v_validation_end_date date;
208 --
209 begin
210 --
211 -- NB. the validation for 'DELETE_NEXT_CHANGE' and 'FUTURE_CHANGE is done
212 -- by opmu_end_date ie. it checks for future opmus etc ...
213 if p_dt_delete_mode in ('ZAP','DELETE') then
214 --
215 -- DT code sets the validation dates to the start and end of time when
216 -- doing a ZAP. This would result in a check over a too wide range of
217 -- dates so the actual start and end dates of the record being removed are
218 -- used NB. as the opmu cannot be updated there will only be one record.
219 if p_dt_delete_mode = 'ZAP' then
220 v_validation_start_date := p_effective_start_date;
221 v_validation_end_date := p_effective_end_date;
222 else
223 v_validation_start_date := p_validation_start_date;
224 v_validation_end_date := p_validation_end_date;
225 end if;
226 --
227 -- Check to see if the opmu is being removed during a time when it is
228 -- the default for the payroll.
229 open csr_dflt_pay_meth(p_payroll_id,
230 p_org_payment_method_id,
231 v_validation_start_date,
232 v_validation_end_date);
233 fetch csr_dflt_pay_meth into v_dummy_id;
234 if csr_dflt_pay_meth%found then
235 close csr_dflt_pay_meth;
236 hr_utility.set_message(801, 'HR_6932_PAY_PAST_DPM');
237 hr_utility.raise_error;
238 else
239 close csr_dflt_pay_meth;
240 end if;
241 --
242 -- Check to see if the opmu is being removed during a time when it is
243 -- has been used to allow the creation of a personal payment method.
244 open csr_ppm(p_payroll_id,
245 p_org_payment_method_id,
246 v_validation_start_date,
247 v_validation_end_date);
248 fetch csr_ppm into v_dummy_id;
249 if csr_ppm%found then
250 close csr_ppm;
251 hr_utility.set_message(801, 'HR_6497_PAY_DEL_PPM');
252 hr_utility.raise_error;
253 else
254 close csr_ppm;
255 end if;
256 --
257 -- Check to see if the opmu is being removed during a time when it is
258 -- has been used in a pre payment.
259 open csr_pre_pay(p_payroll_id,
260 p_org_payment_method_id,
261 v_validation_start_date,
262 v_validation_end_date);
263 fetch csr_pre_pay into v_dummy_id;
264 if csr_pre_pay%found then
265 close csr_pre_pay;
266 hr_utility.set_message(801, 'HR_6498_PAY_DEL_PREPAY');
267 hr_utility.raise_error;
268 else
269 close csr_pre_pay;
270 end if;
271 --
272 end if;
273 --
274 end validate_delete_opmu;
275 --
276 -----------------------------------------------------------------------------
277 -- Name --
278 -- Insert_Row --
279 -- Purpose --
280 -- Table handler procedure that supports the insert of an OPMU via the --
281 -- Define Payroll form. --
282 -- Arguments --
283 -- See below. --
284 -- Notes --
285 -- --
286 -----------------------------------------------------------------------------
287 --
288 PROCEDURE Insert_Row(X_Rowid IN OUT nocopy VARCHAR2,
289 X_Org_Pay_Method_Usage_Id IN OUT nocopy NUMBER,
290 X_Effective_Start_Date DATE,
291 X_Effective_End_Date IN OUT nocopy DATE,
292 X_Payroll_Id NUMBER,
293 X_Org_Payment_Method_Id NUMBER) IS
294 --
295 CURSOR C IS SELECT rowid FROM pay_org_pay_method_usages_f
296 WHERE org_pay_method_usage_id = X_Org_Pay_Method_Usage_Id;
297 --
298 CURSOR C2 IS SELECT pay_org_pay_method_usages_s.nextval
299 FROM dual;
300 --
301 BEGIN
302 --
303 if (X_Org_Pay_Method_Usage_Id is NULL) then
304 OPEN C2;
305 FETCH C2 INTO X_Org_Pay_Method_Usage_Id;
306 CLOSE C2;
307 end if;
308 --
309 -- Sets the correct ned date for the opmu taking into account the payroll,
310 -- opm and future opmus.
311 X_Effective_End_Date := pay_org_pay_meth_usages_f_pkg.opmu_end_date
312 (null, -- opmu id
313 X_Payroll_Id,
314 X_Org_Payment_Method_Id,
315 X_Effective_Start_Date, -- session date
316 X_Effective_Start_Date); -- validation start date
317 --
318 INSERT INTO pay_org_pay_method_usages_f
319 (org_pay_method_usage_id,
320 effective_start_date,
321 effective_end_date,
322 payroll_id,
323 org_payment_method_id)
324 VALUES
325 (X_Org_Pay_Method_Usage_Id,
326 X_Effective_Start_Date,
327 X_Effective_End_Date,
328 X_Payroll_Id,
329 X_Org_Payment_Method_Id);
330 --
331 OPEN C;
332 FETCH C INTO X_Rowid;
333 if (C%NOTFOUND) then
334 CLOSE C;
335 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
336 hr_utility.set_message_token('PROCEDURE',
337 'pay_org_pay_meth_usages_f_pkg.insert_row');
338 hr_utility.set_message_token('STEP','1');
339 hr_utility.raise_error;
340 end if;
341 CLOSE C;
342 --
343 END Insert_Row;
344 --
345 -----------------------------------------------------------------------------
346 -- Name --
347 -- Lock_Row --
348 -- Purpose --
349 -- Table handler procedure that supports the insert , update and delete --
350 -- of a formula by applying a lock on a formula in the Define Payroll --
351 -- form. --
352 -- Arguments --
353 -- See below. --
354 -- Notes --
355 -- None. --
356 -----------------------------------------------------------------------------
357 --
358 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
359 X_Org_Pay_Method_Usage_Id NUMBER,
360 X_Effective_Start_Date DATE,
361 X_Effective_End_Date DATE,
362 X_Payroll_Id NUMBER,
363 X_Org_Payment_Method_Id NUMBER) IS
364 --
365 CURSOR C IS SELECT * FROM pay_org_pay_method_usages_f
369 Recinfo C%ROWTYPE;
366 WHERE rowid = X_Rowid FOR UPDATE of Org_Pay_Method_Usage_Id
367 NOWAIT;
368 --
370 --
371 BEGIN
372 --
373 OPEN C;
374 FETCH C INTO Recinfo;
375 if (C%NOTFOUND) then
376 CLOSE C;
377 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
378 hr_utility.set_message_token('PROCEDURE',
379 'pay_org_pay_meth_usages_f_pkg.lock_row');
380 hr_utility.set_message_token('STEP','1');
381 hr_utility.raise_error;
382 end if;
383 CLOSE C;
384 --
385 if ( ( (Recinfo.org_pay_method_usage_id = X_Org_Pay_Method_Usage_Id)
386 OR ( (Recinfo.org_pay_method_usage_id IS NULL)
387 AND (X_Org_Pay_Method_Usage_Id IS NULL)))
388 AND ( (Recinfo.effective_start_date = X_Effective_Start_Date)
389 OR ( (Recinfo.effective_start_date IS NULL)
390 AND (X_Effective_Start_Date IS NULL)))
391 AND ( (Recinfo.effective_end_date = X_Effective_End_Date)
392 OR ( (Recinfo.effective_end_date IS NULL)
393 AND (X_Effective_End_Date IS NULL)))
394 AND ( (Recinfo.payroll_id = X_Payroll_Id)
395 OR ( (Recinfo.payroll_id IS NULL)
396 AND (X_Payroll_Id IS NULL)))
397 AND ( (Recinfo.org_payment_method_id = X_Org_Payment_Method_Id)
398 OR ( (Recinfo.org_payment_method_id IS NULL)
399 AND (X_Org_Payment_Method_Id IS NULL)))
400 ) then
401 return;
402 else
403 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
404 APP_EXCEPTION.RAISE_EXCEPTION;
405 end if;
406 --
407 END Lock_Row;
408 --
409 -----------------------------------------------------------------------------
410 -- Name --
411 -- Update_Row --
412 -- Purpose --
413 -- Table handler procedure that supports the update of an OPMU via the --
414 -- Define Payroll form. --
415 -- Arguments --
416 -- See below. --
417 -- Notes --
418 -- None. --
419 -----------------------------------------------------------------------------
420 --
421 PROCEDURE Update_Row(X_Rowid VARCHAR2,
422 X_Org_Pay_Method_Usage_Id NUMBER,
423 X_Effective_Start_Date DATE,
424 X_Effective_End_Date DATE,
425 X_Payroll_Id NUMBER,
426 X_Org_Payment_Method_Id NUMBER) IS
427 --
428 BEGIN
429 --
430 UPDATE pay_org_pay_method_usages_f
431 SET org_pay_method_usage_id = X_Org_Pay_Method_Usage_Id,
432 effective_start_date = X_Effective_Start_Date,
433 effective_end_date = X_Effective_End_Date,
434 payroll_id = X_Payroll_Id,
435 org_payment_method_id = X_Org_Payment_Method_Id
436 WHERE rowid = X_rowid;
437 --
438 if (SQL%NOTFOUND) then
439 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
440 hr_utility.set_message_token('PROCEDURE',
441 'pay_org_pay_meth_usages_f_pkg.update_row');
442 hr_utility.set_message_token('STEP','1');
443 hr_utility.raise_error;
444 end if;
445 --
446 END Update_Row;
447 --
448 -----------------------------------------------------------------------------
449 -- Name --
450 -- Delete_Row --
451 -- Purpose --
452 -- Table handler procedure that supports the delete of a OPMU via the --
453 -- Define Payroll form. --
454 -- Arguments --
455 -- See below. --
456 -- Notes --
457 -- --
458 -----------------------------------------------------------------------------
459 --
460 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
461 --
462 BEGIN
463 --
464 DELETE FROM pay_org_pay_method_usages_f
465 WHERE rowid = X_Rowid;
466 --
467 if (SQL%NOTFOUND) then
468 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
469 hr_utility.set_message_token('PROCEDURE',
470 'pay_org_pay_meth_usages_f_pkg.delete_row');
471 hr_utility.set_message_token('STEP','1');
472 hr_utility.raise_error;
473 end if;
474 --
475 END Delete_Row;
476 --
477 END PAY_ORG_PAY_METH_USAGES_F_PKG;
[Home] [Help]