[Home] [Help]
PACKAGE BODY: APPS.PA_PAY_VALIDATE
Source
1 package body pa_pay_validate as
2 -- $Header: PAPAYVALB.pls 120.10 2012/02/15 06:38:37 speddi ship $
3
4 procedure Validate_details (G_start_date in VARCHAR2,
5 G_end_date in VARCHAR2,
6 x_person_id in number,
7 x_payroll_id in number,
8 x_err_code in out NOCOPY number
9 )
10 is
11 l_value_exists number;
12 l_request_id number(15);
13
14 l_group_id hr_organization_units.business_group_id%TYPE;
15
19
16 l_group_id2 hr_organization_units.business_group_id%TYPE;
17
18 l_payroll_id pa_pay_external_payroll.payroll_id%TYPE;
20 x_start_date Date;
21 x_end_date date;
22
23 cursor Interface_details(l_header_id in number) IS
24 select pay_element_type_code ,
25 person_id,
26 assignment_id
27 from pa_pay_ext_interface_details
28 where interface_header_id =l_header_id
29 and nvl(transfer_status_flag,'K') in ('P','R','K')
30 and person_id = nvl(x_person_id,person_id)
31 for update of rejection_code,
32 transfer_status_flag;
33
34 cursor Int_header_details IS
35 select interface_header_id,time_period_start_date,
36 time_period_end_date,payroll_batch_id,payroll_id,
37 payroll_name,business_group_id,business_group_name
38 from pa_pay_ext_interface_header
39 where time_period_start_date >=x_start_date and
40 time_period_end_date <=x_end_date
41 and payroll_id = nvl(x_payroll_id,payroll_id)
42 for update of rejection_code;
43
44
45 /* Start changes for bug#13531404 */
46
47 cursor Int_payroll_details IS
48 select payroll_name,interface_header_id
49 from pa_pay_ext_interface_header
50 where payroll_id is null
51 and time_period_start_date >=x_start_date and
52 time_period_end_date <=x_end_date
53 for update of payroll_id;
54
55 /* End changes for bug#13531404 */
56
57 BEGIN
58
59 x_start_date := fnd_date.canonical_to_date(G_start_date);
60 x_end_date := fnd_date.canonical_to_date(G_end_date);
61 l_request_id := FND_GLOBAL.CONC_REQUEST_ID();
62
63 /* Start changes for bug#13531404 */
64
65 FOR Pay_rec IN Int_payroll_details LOOP
66 --{
67 l_payroll_id := NULL;
68
69 BEGIN
70
71 select payroll_id into l_payroll_id from pa_pay_external_payroll
72 where payroll_name = Pay_rec.payroll_name;
73
74 exception
75 when others then
76 l_payroll_id := NULL;
77
78 END;
79
80 IF l_payroll_id is not null THEN
81 --{
82
83 update pa_pay_ext_interface_header
84 set payroll_id = l_payroll_id
85 where payroll_name = Pay_rec.payroll_name
86 and interface_header_id = Pay_rec.interface_header_id
87 and payroll_id is null;
88
89 --}
90 END IF;
91
92 --}
93 END LOOP;
94
95 l_payroll_id := NULL;
96
97 /* End changes for bug#13531404 */
98
99
100 write_log (LOG, '---------------PARAMETERS----------- ');
101 write_log (LOG, 'G_start_date -> ' || to_char(G_start_date));
102 write_log (LOG, 'G_end_date -> ' || to_char(G_end_date));
103 write_log (LOG, 'x_start_date -> ' || to_char(x_start_date));
104
105 write_log (LOG, 'x_end_date -> ' || to_char(x_end_date));
106 write_log (LOG, 'x_person_id -> ' || to_char(x_person_id));
107 write_log (LOG, 'x_payroll_id -> ' || to_char(x_payroll_id));
108
109
110
111 FOR Hdr_rec IN Int_header_details LOOP
112 --{
113
114 l_value_exists := 0;
115 l_group_id := NULL;
116
117
118 IF(Hdr_rec.business_group_id is null) THEN
119 --{
120 begin
121
122 select business_group_id into l_group_id from hr_organization_units
123 where name = Hdr_rec.business_group_name;
124
125 exception
126 when others then
127 l_group_id := NULL;
128 end;
129 --}
130 END IF;
131
132
133 /* Validation to check valid business group id */
134
135 Update pa_pay_ext_interface_header eih
136 set rejection_code = 'RC 150'
137 where interface_header_id = Hdr_rec.interface_header_id
138 and not exists (select null from hr_organization_units hou
139 where hou.business_group_id = nvl(Hdr_rec.business_group_id,l_group_id)
140 and hou.business_group_id = nvl(eih.business_group_id,l_group_id));
141
142 IF SQL%ROWCOUNT > 0 THEN
143 --{
144
145
146 update pa_pay_ext_interface_details
147 set rejection_code = 'RC 150',
148 transfer_status_flag = 'R',
149 request_id = l_request_id
150 where interface_header_id = Hdr_rec.interface_header_id;
151
152 x_err_code := 1;
153
154 --}
155 ELSE
156 --{
157
158 IF(Hdr_rec.payroll_id is null) THEN
159 --{
160
161 begin
162
163 select payroll_id into l_payroll_id from pa_pay_external_payroll
164 where payroll_name = Hdr_rec.payroll_name;
165
166 exception
167 when others then
168 l_payroll_id := NULL;
169 end;
170 --}
171 END IF;
172
173 /* Validation to check valid payroll */
174
175 Update pa_pay_ext_interface_header eih
176 set rejection_code = 'RC 155'
177 where interface_header_id = Hdr_rec.interface_header_id
178 and not exists (select null from pa_pay_external_payroll ppep
179 where ppep.business_group_id = nvl(Hdr_rec.business_group_id,l_group_id)
183 and active_flag = 'Y');
180 and ppep.payroll_id = nvl(eih.payroll_id,l_payroll_id)
181 and effective_start_date <= Hdr_rec.time_period_start_date
182 and nvl(effective_end_date,Hdr_rec.time_period_end_date+1) >= Hdr_rec.time_period_end_date
184
185 IF SQL%ROWCOUNT > 0 THEN
186 --{
187
188
189 update pa_pay_ext_interface_details
190 set rejection_code = 'RC 155',
191 transfer_status_flag = 'R',
192 request_id = l_request_id
193 where interface_header_id = Hdr_rec.interface_header_id;
194
195 x_err_code := 1;
196
197
198 --}
199 ELSE
200 --{
201 /* Validation to check whether payroll for the same period is processed */
202
203 select count(1) into l_value_exists from pa_pay_ext_interface_header
204 where time_period_start_date = Hdr_rec.time_period_start_date
205 and time_period_end_date = Hdr_rec.time_period_end_date
206 and payroll_batch_id = Hdr_rec.payroll_batch_id
207 and payroll_id = nvl(Hdr_rec.payroll_id,l_payroll_id)
208 and business_group_id = NVL(Hdr_rec.business_group_id,l_group_id)
209 and rejection_code is null;
210
211
212 IF l_value_exists > 1 THEN
213 --{
214
215
216
217 update pa_pay_ext_interface_header
218 set rejection_code = 'RC 160'
219 where current of Int_header_details;
220
221 update pa_pay_ext_interface_details
222 set rejection_code = 'RC 160',
223 transfer_status_flag = 'R',
224 request_id = l_request_id
225 where interface_header_id = Hdr_rec.interface_header_id;
226
227 x_err_code := 1;
228
229
230 --}
231
232 ELSE
233 --{
234
235 /* Validation to check whether any overlap in the pay periods */
236
237 select count(1) into l_value_exists from pa_pay_ext_interface_header
238 where payroll_id = nvl(Hdr_rec.payroll_id,l_payroll_id)
239 and business_group_id = NVL(Hdr_rec.business_group_id,l_group_id)
240 and rejection_code is null
241 and ((Hdr_rec.time_period_start_date between time_period_start_date and time_period_end_date)
242 OR (Hdr_rec.time_period_end_date between time_period_start_date and time_period_end_date)
243 OR (Hdr_rec.time_period_start_date <= time_period_start_date
244 and Hdr_rec.time_period_end_date >= time_period_end_date));
245
246
247
248 IF l_value_exists >1 THEN
249 --{
250
251
252 update pa_pay_ext_interface_header
253 set rejection_code = 'RC 165'
254 where current of Int_header_details;
255
256 update pa_pay_ext_interface_details
257 set rejection_code = 'RC 165',
258 transfer_status_flag = 'R',
259 request_id = l_request_id
260 where interface_header_id = Hdr_rec.interface_header_id;
261
262 x_err_code := 1;
263
264 --}
265 ELSE
266 --{
267
268 x_err_code := 0;
269
270 --}
271 END IF;
272
273 --}
274 END IF;
275
276 --}
277 END IF;
278
279 --}
280 END IF;
281
282
283 IF x_err_code = 0 THEN
284 --{
285
286
287 l_group_id2 := nvl(Hdr_rec.business_group_id,l_group_id);
288
289 FOR Details_rec IN Interface_details(Hdr_rec.INTERFACE_HEADER_ID) LOOP
290 --{
291
292
293 /* Validation to check Pay_element_code is available */
294
295 select count(1) into l_value_exists from pa_lookups
296 where lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
297 and lookup_code = Details_rec.pay_element_type_code
298 and enabled_flag = 'Y'
299 and NVL(predefined_flag,'O') IN ('E','I','S','T','L','O') /* Bug 12998980 */
300 --AND PAY_PERIOD_END_DATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE ,PAY_PERIOD_END_DATE);
301 and start_date_active <= Hdr_rec.time_period_start_date
302 and nvl(end_date_active,Hdr_rec.time_period_end_date+1) >= Hdr_rec.time_period_end_date;
303
304 IF l_value_exists = 0 THEN
305 --{
306
307 update pa_pay_ext_interface_details
308 set rejection_code = 'RC 170',
309 transfer_status_flag ='R',
310 request_id = l_request_id
311 where current of Interface_details;
312
313 x_err_code := 1;
314 --}
315
316 ELSE
317 --{
318
319 /* Validation to check whether the person is active or not */
320
321 select count(1) into l_value_exists
322 from per_all_people_f
323 where person_id = Details_rec.PERSON_ID
324 and nvl(current_employee_flag, 'N') = 'Y'
325 and effective_start_date <= Hdr_rec.time_period_start_date
326 and nvl(effective_end_date,Hdr_rec.time_period_end_date+1) >= Hdr_rec.time_period_end_date;
327
328 IF l_value_exists = 0 THEN
329 --{
330
331 update pa_pay_ext_interface_details
332 set rejection_code = 'RC 175',
333 transfer_status_flag = 'R' ,
334 request_id = l_request_id
335 where current of Interface_details ;
336
337 x_err_code := 1;
338
339
340 --}
341
342 ELSE
346
343 --{
344
345 /* Validation to check whether the person assignment is primary or not */
347 select count(1) into l_value_exists from
348 per_assignment_status_types s
349 , per_all_assignments_f a
350 WHERE
351 a.person_id = Details_rec.PERSON_ID
352 AND a.primary_flag = 'Y'
353 AND a.assignment_type in ('E', 'C')
354 AND a.assignment_status_type_id = s.assignment_status_type_id
355 AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
356 AND trunc(Hdr_rec.time_period_end_date) BETWEEN trunc( a.effective_start_date )
357 AND trunc( a.effective_end_date )
358 AND (( l_group_id2 = a.business_group_id+0) )
359 and a.assignment_id =Details_rec.assignment_id;
360
361
362 IF l_value_exists = 0 THEN
363 --{
364
365 update pa_pay_ext_interface_details
366 set rejection_code = 'RC 180',
367 transfer_status_flag = 'R',
368 request_id = l_request_id
369 where current of Interface_details ;
370
371 x_err_code := 1;
372
373 --}
374 ELSE
375 --{
376
377
378 /* Inserting a record into time period table */
379
380 select count(1) into l_value_exists from PA_PAY_EXTERNAL_TIME_PERIODS where
381 time_period_start_date = Hdr_rec.time_period_start_date
382 and time_period_end_date = Hdr_rec.time_period_end_date
383 and payroll_id = Hdr_rec.payroll_id;
384
385 IF(l_value_exists = 0) THEN
386 --{
387
388 Insert into PA_PAY_EXTERNAL_TIME_PERIODS(PAYROLL_ID,
389 TIME_PERIOD_ID,
390 TIME_PERIOD_START_DATE,
391 TIME_PERIOD_END_DATE,
392 LAST_UPDATE_DATE,
393 LAST_UPDATED_BY,CREATION_DATE,
394 CREATED_BY,LAST_UPDATE_LOGIN) values (Hdr_rec.payroll_id,
395 PA_PAY_EXTERNAL_TIME_PERIODS_S.nextval,
396 Hdr_rec.time_period_start_date,
397 Hdr_rec.time_period_end_date,
398 sysdate,-1,sysdate,-1,null);
399
400
401 --}
402 END IF;
403
404 update pa_pay_ext_interface_details
405 set transfer_status_flag = 'P',
406 request_id = l_request_id
407 where current of Interface_details;
408
409
410
411 --}
412 END IF;
413
414 --}
415 END IF;
416
417
418
419 --}
420 END IF;
421
422
423 --}
424 END LOOP;
425
426 /* Rejecting the other lines if any item gets rejected */
427
428 update pa_pay_ext_interface_details eid1
429 set transfer_status_flag = 'R',
430 rejection_code = nvl(rejection_code,'RC 185'),
431 request_id = l_request_id
432 where interface_header_id = Hdr_rec.interface_header_id
433 and exists (select null from pa_pay_ext_interface_details eid2
434 where eid1.interface_header_id = eid2.interface_header_id
435 and interface_header_id = Hdr_rec.interface_header_id
436 and transfer_status_flag = 'R');
437
438 --}
439 END IF;
440
441 --}
442 END LOOP;
443
444 END Validate_details;
445
446 PROCEDURE write_log (
447 p_message_type IN NUMBER,
448 p_message IN VARCHAR2) IS
449
450 buffer_overflow EXCEPTION;
451 PRAGMA EXCEPTION_INIT(buffer_overflow, -20000);
452
453 BEGIN
454 --FND_FILE.PUT_LINE(FND_FILE.LOG,to_char(sysdate,'HH:MI:SS: ')|| p_message);
455 pa_debug.write_file('LOG', 'pa.plsql.PA_PAY_VALIDATE: '|| p_message , 1);
456 EXCEPTION /* When exception occurs, program needs to be aborted. */
457 WHEN OTHERS THEN
458 raise;
459
460 END write_log;
461
462
463 END pa_pay_validate;