1 PACKAGE BODY PER_PERRPADD_XMLP_PKG AS
2 /* $Header: PERRPADDB.pls 120.1 2007/12/06 11:27:40 amakrish noship $ */
3
4 function cf_addr_chkformula(gre_name in varchar2, person_id in number, CS_no_of_addr in number, address_id in number, add_date_to in date, effective_start_date in date, effective_end_date in date,
5 date_from in date, address_line1 in varchar2, location_address in varchar2, town_or_city in varchar2, address_line2 in varchar2, address_line3 in varchar2) return number is
6 begin
7 if (CP_prev_gre_name <> gre_name) then
8 CP_print_gre := 0;
9 end if;
10
11 if (CP_prev_person_id <> person_id
12 or CP_prev_person_id is NULL) then
13 CP_prev_person_id := NULL;
14 CP_prev_date_to := NULL;
15 CP_keep_count := 0;
16 CP_missing_flag := 0;
17 CP_reason := NULL;
18 CP_reason1 := NULL;
19 CP_addr_count := CS_no_of_addr;
20 end if;
21 if CP_addr_count = 0 then
22 CP_prev_date_to := NULL;
23 CP_addr_count := CS_no_of_addr;
24 CP_keep_count := 0;
25 CP_missing_flag := 0;
26 end if;
27
28 if address_id IS NOT NULL then
29 CP_keep_count := CP_keep_count + 1;
30 end if;
31
32 CP_missing_st := NULL;
33 CP_missing_st1 := NULL;
34 CP_missing_end := NULL;
35 CP_missing_end1 := NULL;
36
37
38 if address_id = cp_old_address_id then
39 cp_keep_count := 1;
40 CP_addr_count := CP_addr_count -1 ;
41 CP_prev_date_to := add_date_to;
42 return (null);
43 end if;
44
45
46 if nvl(CS_no_of_addr, 0) = 0 then
47 CP_missing_st := effective_start_date;
48 if effective_start_date < input_start_date then
49 CP_missing_st := input_start_date;
50 end if;
51 CP_missing_end := effective_end_date;
52 if effective_end_date > input_end_date then
53 CP_missing_end := input_end_date;
54 end if;
55 CP_missing_flag := 1;
56 fnd_message.set_name('PER','PER_INV_ADD_NO_ADDRESS');
57 CP_reason := fnd_message.get();
58 end if;
59
60
61 if CS_no_of_addr = 1 then
62
63 if (date_from > effective_start_date) and
64 (date_from > input_start_date)
65 then
66 CP_missing_st := effective_start_date;
67 if effective_start_date < input_start_date then
68 CP_missing_st := input_start_date;
69 end if;
70 CP_missing_end := date_from - 1;
71 if (date_from - 1) > input_end_date then
72 CP_missing_end := input_end_date;
73 end if;
74 CP_missing_flag := 1;
75 fnd_message.set_name('PER','PER_INV_ADD_NO_WHOLE_PERIOD');
76 CP_reason := fnd_message.get();
77
78 end if;
79
80
81 if (add_date_to < effective_end_date) and
82 (add_date_to < input_end_date)
83 then
84 CP_missing_st1 := add_date_to + 1;
85 if (add_date_to + 1) < input_start_date then
86 CP_missing_st1 := input_start_date;
87 end if;
88 CP_missing_end1 := effective_end_date;
89 if effective_end_date > input_end_date then
90 CP_missing_end1 := input_end_date;
91 end if;
92 CP_missing_flag := 1;
93 fnd_message.set_name('PER','PER_INV_ADD_NO_WHOLE_PERIOD');
94 CP_reason1 := fnd_message.get();
95 end if;
96 end if;
97
98
99 if CS_no_of_addr > 1 then
100
101 if (CP_prev_date_to IS NULL) then
102 if (date_from > effective_start_date) and
103 (date_from > input_start_date) then
104 CP_missing_st := effective_start_date;
105 if effective_start_date < input_start_date then
106 CP_missing_st := input_start_date;
107 end if;
108 CP_missing_end := date_from - 1;
109 if (date_from - 1) > input_end_date then
110 CP_missing_end := input_end_date;
111 end if;
112 CP_missing_flag := 1;
113 fnd_message.set_name('PER','PER_INV_ADD_NO_WHOLE_PERIOD');
114 CP_reason := fnd_message.get();
115 end if;
116
117 if additional_verification = 'MMREF' then
118 if (length(address_line1) > 22 OR
119 length(location_address) > 22 OR
120 length(town_or_city) > 22) then
121 if CP_missing_st is NULL then
122 if input_start_date > date_from then
123 CP_missing_st := input_start_date;
124 else
125 CP_missing_st := date_from;
126 end if;
127 if input_end_date < add_date_to then
128 CP_missing_end := input_end_date;
129 else
130 CP_missing_end := add_date_to;
131 end if;
132 fnd_message.set_name('PER','PER_INV_ADD_NON_MMREF');
133 CP_reason := fnd_message.get();
134 else
135 if input_start_date > date_from then
136 CP_missing_st1 := input_start_date;
137 else
138 CP_missing_st1 := date_from;
139 end if;
140 if input_end_date < add_date_to then
141 CP_missing_end1 := input_end_date;
142 else
143 CP_missing_end1 := add_date_to;
144 end if;
145 fnd_message.set_name('PER','PER_INV_ADD_NON_MMREF');
146 CP_reason1 := fnd_message.get();
147 end if;
148 CP_missing_flag := 1;
149 end if;
150 end if;
151
152
153
154 if additional_verification = 'LENGTH' and line_length is not NULL then
155 if (length(address_line1) > line_length or
156 length(address_line2) > line_length or
157 length(address_line3) > line_length) then
158 if input_start_date > date_from then
159 CP_missing_st := input_start_date;
160 else
161 CP_missing_st := date_from;
162 end if;
163 if input_end_date < add_date_to then
164 CP_missing_end := input_end_date;
165 else
166 CP_missing_end := add_date_to;
167 end if;
168 CP_missing_flag := 1;
169 fnd_message.set_name('PER','PER_INV_ADD_TOO_LONG');
170 CP_reason := fnd_message.get();
171 end if;
172 end if;
173
174
175
176 else
177
178 if (CP_prev_date_to >= date_from) then
179 CP_missing_st := date_from;
180 if date_from < input_start_date then
181 CP_missing_st := input_start_date;
182 end if;
183 CP_missing_end := CP_prev_date_to;
184 if CP_prev_date_to > input_end_date then
185 CP_missing_end := input_end_date;
186 end if;
187 CP_missing_flag := 1;
188 fnd_message.set_name('PER','PER_INV_ADD_OVERLAP');
189 CP_reason := fnd_message.get();
190 else
191
192 if (CP_prev_date_to < date_from - 1) then
193 CP_missing_st := CP_prev_date_to + 1;
194 if (CP_prev_date_to + 1) < input_start_date then
195 CP_missing_st := input_start_date;
196 end if;
197 CP_missing_end := date_from - 1;
198 if (date_from - 1) > input_end_date then
199 CP_missing_end := input_end_date;
200 end if;
201 CP_missing_flag := 1;
202 fnd_message.set_name('PER','PER_INV_ADD_GAP_EXIST');
203 CP_reason := fnd_message.get();
204 end if;
205 end if;
206
207 if (CP_keep_count = CS_no_of_addr) and
208 (add_date_to < effective_end_date) and
209 (add_date_to < input_end_date) then
210 CP_missing_st1 := add_date_to + 1;
211 if (add_date_to + 1 ) < input_start_date then
212 CP_missing_st1 := input_start_date;
213 end if;
214 CP_missing_end1 := effective_end_date;
215 if effective_end_date > input_end_date then
216 CP_missing_end1 := input_end_date;
217 end if;
218 CP_missing_flag := 1;
219 fnd_message.set_name('PER','PER_INV_ADD_NO_WHOLE_PERIOD');
220 CP_reason1 := fnd_message.get();
221 end if;
222 end if;
223
224 end if;
225
226
227
228 if CP_missing_st is NULL then
229 if additional_verification = 'MMREF' then
230 if (length(address_line1) > 22 OR
231 length(location_address) > 22 OR
232 length(town_or_city) > 22) then
233 if input_start_date > date_from then
234 CP_missing_st := input_start_date;
235 else
236 CP_missing_st := date_from;
237 end if;
238 if input_end_date < add_date_to then
239 CP_missing_end := input_end_date;
240 else
241 CP_missing_end := add_date_to;
242 end if;
243 CP_missing_flag := 1;
244 fnd_message.set_name('PER','PER_INV_ADD_NON_MMREF');
245 CP_reason := fnd_message.get();
246 end if;
247 end if;
248 end if;
249
250
251
252
253 if CP_missing_st is NULL then
254 if additional_verification = 'LENGTH' and line_length is not NULL then
255 if (length(address_line1) > line_length or
256 length(address_line2) > line_length or
257 length(address_line3) > line_length) then
258 if input_start_date > date_from then
259 CP_missing_st := input_start_date;
260 else
261 CP_missing_st := date_from;
262 end if;
263 if input_end_date < add_date_to then
264 CP_missing_end := input_end_date;
265 else
266 CP_missing_end := add_date_to;
267 end if;
268 CP_missing_flag := 1;
269 fnd_message.set_name('PER','PER_INV_ADD_TOO_LONG');
270 CP_reason := fnd_message.get();
271 end if;
272 end if;
273 end if;
274
275 CP_prev_date_to := add_date_to;
276 if cp_missing_flag = 1 and cp_print_gre = 0 then
277 cp_print_gre := 1;
278 end if;
279
280 if cp_missing_flag = 1 and cp_print_gre is null then
281 cp_print_gre := 1;
282 end if;
283
284 if cp_missing_flag <> 1 and cp_print_gre is null then
285 cp_print_gre := 0;
286 end if;
287
288 CP_prev_person_id := person_id;
289 CP_prev_gre_name := gre_name;
290 cp_old_address_id := address_id;
291 CP_addr_count := CP_addr_count - 1;
292
293 return(null);
294
295 end;
296
297 function INPUT_END_DATEValidTrigger return boolean is
298 begin
299 if ((input_end_date IS NULL)
300 OR (input_start_date > input_end_date) )
301 then
302 /*srw.message('101', 'Start Date cannot be greater than End Date');*/null;
303
304 return (FALSE);
305 else
306 return (TRUE);
307 end if;
308 RETURN NULL; end;
309
310 function INPUT_START_DATEValidTrigger return boolean is
311 begin
312 if ((input_start_date IS NULL)
313 or (input_start_date > input_end_date)) then
314
315 /*srw.message('100', 'Start Date Cannot be greater than End Date');*/null;
316
317 return(FALSE);
318 else
319 return (TRUE);
320 end if;
321 RETURN NULL; end;
322
323 function AfterPForm return boolean
324 is
325
326
327
328 begin
329
330
331
332
333
334
335 return (TRUE);
336 end;
337
338 function CF_gre_nameFormula return VARCHAR2
339 is
340
341 cursor c_gre_name is
342 select name
343 from hr_organization_units
344 where organization_id = gre_id;
345
346 lv_gre_name hr_all_organization_units.name%TYPE;
347
348 begin
349
350 if gre_id IS not NULL then
351 open c_gre_name;
352 fetch c_gre_name into lv_gre_name;
353 close c_gre_name;
354
355 else
356 lv_gre_name := ' ';
357 end if;
358
359 return(lv_gre_name);
360
361 end;
362
363 function CF_bus_grpFormula return VARCHAR2
364 is
365
366 Cursor c_business_group is
367 select name
368 from per_business_groups
369 where business_group_id = business_id;
370
371 lv_business_group hr_all_organization_units.name%TYPE;
372
373 begin
374 if business_id IS NOT NULL then
375 open c_business_group;
376 fetch c_business_group into lv_business_group;
377 close c_business_group;
378 else
379 lv_business_group := ' ';
380 end if;
381
382 return (lv_business_group);
383
384 end;
385
386 function BeforeReport return boolean is
387 begin
388 LP_INPUT_START_DATE :=INPUT_START_DATE;
389 LP_INPUT_END_DATE:=INPUT_END_DATE;
390 --hr_standard.event('BEFORE REPORT');
391
392 return (TRUE);
393 end;
394
395 function AfterReport return boolean is
396 begin
397
398 --hr_standard.event('AFTER REPORT');
399
400 return (TRUE);
401 end;
402
403 function CF_header_notesFormula return Char is
404 begin
405 if additional_verification = 'MMREF' then
406 return (
407 'Employees with an error "The address does not comply with MMREF-1 address length stndards and will be truncated" will not cause the file to be rejected by the SSA.' ||
408 'This is a warning message that the employee''s address will be truncated to fit the layout standards.');
409 else
410 return(' ');
411 end if;
412
413 end;
414
415 function ADDITIONAL_VERIFICATIONValidTr return boolean is
416 begin
417 return (TRUE);
418 end;
419
420 function LINE_LENGTHValidTrigger return boolean is
421 begin
422 return (TRUE);
423 end;
424
425 --Functions to refer Oracle report placeholders--
426
427 Function CP_print_gre_p return number is
428 Begin
429 return CP_print_gre;
430 END;
431 Function CP_missing_flag_p return number is
432 Begin
433 return CP_missing_flag;
434 END;
435 Function CP_old_address_id_p return number is
436 Begin
437 return CP_old_address_id;
438 END;
439 Function CP_prev_person_id_p return number is
440 Begin
441 return CP_prev_person_id;
442 END;
443 Function CP_prev_name_p return varchar2 is
444 Begin
445 return CP_prev_name;
446 END;
447 Function CP_temp_id_p return number is
448 Begin
449 return CP_temp_id;
450 END;
451 Function CP_prev_gre_name_p return varchar2 is
452 Begin
453 return CP_prev_gre_name;
454 END;
455 Function CP_addr_count_p return number is
456 Begin
457 return CP_addr_count;
458 END;
459 Function CP_missing_st_p return date is
460 Begin
461 return CP_missing_st;
462 END;
463 Function CP_missing_end_p return date is
464 Begin
465 return CP_missing_end;
466 END;
467 Function CP_reason_p return varchar2 is
468 Begin
469 return CP_reason;
470 END;
471 Function CP_reason1_p return varchar2 is
472 Begin
473 return CP_reason1;
474 END;
475 Function CP_missing_st1_p return date is
476 Begin
477 return CP_missing_st1;
478 END;
479 Function CP_missing_end1_p return date is
480 Begin
481 return CP_missing_end1;
482 END;
483 Function CP_keep_count_p return number is
484 Begin
485 return CP_keep_count;
486 END;
487 Function CP_prev_date_to_p return date is
488 Begin
489 return CP_prev_date_to;
490 END;
491 END PER_PERRPADD_XMLP_PKG ;
[Home] [Help]