1 PACKAGE BODY HR_PAY_INTERFACE_PKG AS
2 /* $Header: pegpipkg.pkb 120.6 2006/01/04 06:24:05 sgelvi noship $ */
3 --
4 -- OAB Benefit view functionality
5 --
6 -- non split globals
7 g_eepyc_rec hr_pay_interface_oab_value_v%ROWTYPE;
8 g_erpyc_rec hr_pay_interface_oab_value_v%ROWTYPE;
9 g_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
10 TYPE g_coverage_type_typ IS TABLE OF ben_opt_f.name%TYPE INDEX BY BINARY_INTEGER;
11 g_coverage_type_tab g_coverage_type_typ;
12 --
13 -- split view globals
14 g_split_eepyc_rec hr_pay_interface_oab_value1_v%ROWTYPE;
15 g_split_erpyc_rec hr_pay_interface_oab_value2_v%ROWTYPE;
16 g_split_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
17 --
18 -- Procedures/functions for the non-split oab view
19 --
20 --
21 FUNCTION GET_COVERAGE_TYPE(P_OIPL_ID IN NUMBER) RETURN VARCHAR2 AS
22 l_name ben_opt_f.name%TYPE;
23 -- define the cursor
24 CURSOR CSR1 IS SELECT opt.name /*coverage_type*/
25 FROM ben_opt_f opt,
26 ben_oipl_f cop
27 WHERE opt.opt_id = cop.opt_id
28 AND cop.oipl_id = P_OIPL_ID
29 AND cop.effective_start_date =
30 (SELECT max(cop1.effective_start_date)
31 FROM ben_oipl_f cop1
32 WHERE cop1.oipl_id = cop.oipl_id
33 AND cop1.effective_start_date <= hr_pay_interface_pkg.get_extract_date)
34 AND opt.effective_start_date =
35 (SELECT max(opt1.effective_start_date)
36 FROM ben_opt_f opt1
37 WHERE opt1.opt_id = opt.opt_id
38 AND opt1.effective_start_date <= hr_pay_interface_pkg.get_extract_date);
39 BEGIN
40 -- check to see if a P_OIPL_ID exists if not short circuit out returning NULL
41 IF P_OIPL_ID IS NULL THEN
42 RETURN(NULL);
43 END IF;
44 -- check to see if the OIPL_ID has already been cached
45 BEGIN
46 RETURN(g_coverage_type_tab(P_OIPL_ID));
47 EXCEPTION
48 WHEN OTHERS THEN
49 -- not found so select it
50 OPEN CSR1;
51 FETCH CSR1 INTO l_name;
52 IF CSR1%NOTFOUND THEN
53 -- not found close cursor and return NULL
54 CLOSE CSR1;
55 RETURN(NULL);
56 END IF;
57 CLOSE CSR1;
58 -- found so place in cache and return
59 g_coverage_type_tab(P_OIPL_ID) := l_name;
60 RETURN(l_name);
61 END;
62 EXCEPTION
63 WHEN OTHERS THEN
64 -- unexpected error so ensure no cursors are OPEN and return NULL
65 IF CSR1%ISOPEN THEN
66 CLOSE CSR1;
67 END IF;
68 RETURN(NULL);
69 END GET_COVERAGE_TYPE;
70 --
71 PROCEDURE select_rec(p_prtt_enrt_rslt_id IN
72 ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
73 p_effective_start_date IN DATE,
74 p_effective_end_date IN DATE) IS
75 CURSOR csr_pay_interface_oab_value_v(c_acty_typ_cd
76 ben_prtt_rt_val.acty_typ_cd%TYPE) IS
77 SELECT pi.*
78 FROM hr_pay_interface_oab_value_v pi
79 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
80 AND pi.acty_typ_cd = c_acty_typ_cd
81 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
82 BEGIN
83 -- set the g_prtt_enrt_rslt_id global
84 g_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
85 -- get the eepyc record
86 OPEN csr_pay_interface_oab_value_v('EEPYC');
87 FETCH csr_pay_interface_oab_value_v INTO g_eepyc_rec;
88 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
89 -- null record as it was not found
90 g_eepyc_rec.prtt_enrt_rslt_id := NULL;
91 END IF;
92 CLOSE csr_pay_interface_oab_value_v;
93 -- get the erpyc record
94 OPEN csr_pay_interface_oab_value_v('ERPYC');
95 FETCH csr_pay_interface_oab_value_v INTO g_erpyc_rec;
96 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
97 -- null record as it was not found
98 g_erpyc_rec.prtt_enrt_rslt_id := NULL;
99 END IF;
100 CLOSE csr_pay_interface_oab_value_v;
101 EXCEPTION
102 WHEN OTHERS THEN
103 -- unexpected error, close cursor if open and set
104 -- both eepyc and erpyc records to null
105 IF csr_pay_interface_oab_value_v%ISOPEN THEN
106 CLOSE csr_pay_interface_oab_value_v;
107 END IF;
108 -- null both records
109 g_eepyc_rec.prtt_enrt_rslt_id := NULL;
110 g_erpyc_rec.prtt_enrt_rslt_id := NULL;
111 END select_rec;
112 --
113 FUNCTION eepyc_erpyc_exist
114 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
115 p_effective_start_date IN DATE,
116 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
117 --
118 CURSOR csr_pay_interface_oab_value_v IS
119 SELECT 1
120 FROM hr_pay_interface_oab_value_v pi
121 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
122 AND pi.acty_typ_cd IN ('EEPYC','ERPYC')
123 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
124 --
125 l_dummy NUMBER(1);
126 --
127 BEGIN
128 OPEN csr_pay_interface_oab_value_v;
129 FETCH csr_pay_interface_oab_value_v INTO l_dummy;
130 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
131 CLOSE csr_pay_interface_oab_value_v;
132 RETURN('N');
133 END IF;
134 CLOSE csr_pay_interface_oab_value_v;
135 -- if we have got this far then at least 1 row was found
136 RETURN('Y');
137 EXCEPTION
138 WHEN OTHERS THEN
139 -- unexpected error
140 IF csr_pay_interface_oab_value_v%ISOPEN THEN
141 CLOSE csr_pay_interface_oab_value_v;
142 END IF;
143 RETURN('N');
144 END eepyc_erpyc_exist;
145 --
146 FUNCTION get_eepyc_varchar2
147 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
148 p_column_name IN VARCHAR2,
149 p_effective_start_date IN DATE,
150 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
151 --
152 l_column_name VARCHAR2(30) := p_column_name;
153 --
154 BEGIN
155 IF p_prtt_enrt_rslt_id IS NULL THEN
156 RETURN(NULL);
157 END IF;
158 -- is the current record in the cache?
159 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
160 (g_prtt_enrt_rslt_id IS NULL) THEN
161 -- row is NOT cached so select information
162 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
163 END IF;
164 -- has the eepyc record been populated?
165 IF g_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
166 -- no row was found so return NULL
167 RETURN(NULL);
168 ELSE
169 -- return the varchar2 column required
170 IF l_column_name = 'CONTRIBUTION_AMOUNT' THEN
171 RETURN(g_eepyc_rec.CONTRIBUTION_AMOUNT);
172 ELSIF l_column_name = 'ACTY_TYP_CD' THEN
173 RETURN(g_eepyc_rec.ACTY_TYP_CD);
174 ELSIF l_column_name = 'ASSIGNMENT_NUMBER' THEN
175 RETURN(g_eepyc_rec.ASSIGNMENT_NUMBER);
176 ELSIF l_column_name = 'RATE_PERIOD' THEN
177 RETURN(g_eepyc_rec.RATE_PERIOD);
178 ELSIF l_column_name = 'RT_TYP_CD' THEN
179 RETURN(g_eepyc_rec.RT_TYP_CD);
180 ELSE
181 -- column unknown
182 RETURN(NULL);
183 END IF;
184 END IF;
185 END get_eepyc_varchar2;
186 --
187 FUNCTION get_eepyc_number
188 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
189 p_column_name IN VARCHAR2,
190 p_effective_start_date IN DATE,
191 p_effective_end_date IN DATE) RETURN NUMBER IS
192 --
193 l_column_name VARCHAR2(30) := p_column_name;
194 --
195 BEGIN
196 IF p_prtt_enrt_rslt_id IS NULL THEN
197 RETURN(NULL);
198 END IF;
199 -- is the current record in the cache?
200 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
201 (g_prtt_enrt_rslt_id IS NULL) THEN
202 -- row is NOT cached so select information
203 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
204 END IF;
205 -- has the eepyc record been populated?
206 IF g_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
207 -- no row was found so return NULL
208 RETURN(NULL);
209 ELSE
210 -- return the varchar2 column required
211 IF l_column_name = 'ASSIGNMENT_ID' THEN
212 RETURN(g_eepyc_rec.ASSIGNMENT_ID);
213 ELSIF l_column_name = 'RATE_AMOUNT' THEN
214 RETURN(g_eepyc_rec.RATE_AMOUNT);
215 ELSIF l_column_name = 'MAX_ANNUAL_AMOUNT' THEN
216 RETURN(g_eepyc_rec.MAX_ANNUAL_AMOUNT);
217 ELSIF l_column_name = 'PRTT_ENRT_RSLT_ID' THEN
218 RETURN(g_eepyc_rec.PRTT_ENRT_RSLT_ID);
219 ELSIF l_column_name = 'PRTT_RT_VAL_ID' THEN
220 RETURN(g_eepyc_rec.PRTT_RT_VAL_ID);
221 ELSIF l_column_name = 'ELEMENT_ENTRY_VALUE_ID' THEN
222 RETURN(g_eepyc_rec.ELEMENT_ENTRY_VALUE_ID);
223 ELSIF l_column_name = 'PERSON_ID' THEN
224 RETURN(g_eepyc_rec.PERSON_ID);
225 ELSIF l_column_name = 'PER_IN_LER_ID' THEN
226 RETURN(g_eepyc_rec.PER_IN_LER_ID);
227 ELSE
228 -- column unknown
229 RETURN(NULL);
230 END IF;
231 END IF;
232 END get_eepyc_number;
233 --
234 FUNCTION get_eepyc_date
235 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
236 p_column_name IN VARCHAR2,
237 p_effective_start_date IN DATE,
238 p_effective_end_date IN DATE) RETURN DATE IS
239 --
240 l_column_name VARCHAR2(30) := p_column_name;
241 --
242 BEGIN
243 IF p_prtt_enrt_rslt_id IS NULL THEN
244 RETURN(NULL);
245 END IF;
246 -- is the current record in the cache?
247 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
248 (g_prtt_enrt_rslt_id IS NULL) THEN
249 -- row is NOT cached so select information
250 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
251 END IF;
252 -- has the eepyc record been populated?
253 IF g_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
254 -- no row was found so return NULL
255 RETURN(NULL);
256 ELSE
257 -- return the varchar2 column required
258 IF l_column_name = 'EFFECTIVE_START_DATE' THEN
259 RETURN(g_eepyc_rec.EFFECTIVE_START_DATE);
260 ELSIF l_column_name = 'EFFECTIVE_END_DATE' THEN
261 RETURN(g_eepyc_rec.EFFECTIVE_END_DATE);
262 ELSIF l_column_name = 'RT_STRT_DT' THEN
263 RETURN(g_eepyc_rec.RT_STRT_DT);
264 ELSIF l_column_name = 'RT_END_DT' THEN
265 RETURN(g_eepyc_rec.RT_END_DT);
266 ELSIF l_column_name = 'MIN_RT_STRT_DT' THEN
267 RETURN(g_eepyc_rec.MIN_RT_STRT_DT);
268 ELSIF l_column_name = 'MAX_RT_END_DT' THEN
269 RETURN(g_eepyc_rec.MAX_RT_END_DT);
270 ELSIF l_column_name = 'ELEMENT_ENTRY_ESD' THEN
271 RETURN(g_eepyc_rec.ELEMENT_ENTRY_ESD);
272 ELSIF l_column_name = 'ELEMENT_ENTRY_EED' THEN
273 RETURN(g_eepyc_rec.ELEMENT_ENTRY_EED);
274 ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
275 RETURN(g_eepyc_rec.OABV_LAST_UPDATE_DATE);
276 ELSE
277 -- column unknown
278 RETURN(NULL);
279 END IF;
280 END IF;
281 END get_eepyc_date;
282 --
283 FUNCTION get_erpyc_varchar2
284 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
285 p_column_name IN VARCHAR2,
286 p_effective_start_date IN DATE,
287 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
288 --
289 l_column_name VARCHAR2(30) := p_column_name;
290 --
291 BEGIN
292 IF p_prtt_enrt_rslt_id IS NULL THEN
293 RETURN(NULL);
294 END IF;
295 -- is the current record in the cache?
296 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
297 (g_prtt_enrt_rslt_id IS NULL) THEN
298 -- row is NOT cached so select information
299 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
300 END IF;
301 -- has the erpyc record been populated?
302 IF g_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
303 -- no row was found so return NULL
304 RETURN(NULL);
305 ELSE
306 -- return the varchar2 column required
307 IF l_column_name = 'CONTRIBUTION_AMOUNT' THEN
308 RETURN(g_erpyc_rec.CONTRIBUTION_AMOUNT);
309 ELSIF l_column_name = 'ACTY_TYP_CD' THEN
310 RETURN(g_erpyc_rec.ACTY_TYP_CD);
311 ELSIF l_column_name = 'ASSIGNMENT_NUMBER' THEN
312 RETURN(g_erpyc_rec.ASSIGNMENT_NUMBER);
313 ELSIF l_column_name = 'RATE_PERIOD' THEN
314 RETURN(g_erpyc_rec.RATE_PERIOD);
315 ELSIF l_column_name = 'RT_TYP_CD' THEN
316 RETURN(g_erpyc_rec.RT_TYP_CD);
317 ELSE
318 -- column unknown
319 RETURN(NULL);
320 END IF;
321 END IF;
322 END get_erpyc_varchar2;
323 --
324 FUNCTION get_erpyc_number
325 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
326 p_column_name IN VARCHAR2,
327 p_effective_start_date IN DATE,
328 p_effective_end_date IN DATE) RETURN NUMBER IS
329 --
330 l_column_name VARCHAR2(30) := p_column_name;
331 --
332 BEGIN
333 IF p_prtt_enrt_rslt_id IS NULL THEN
334 RETURN(NULL);
335 END IF;
336 -- is the current record in the cache?
337 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
338 (g_prtt_enrt_rslt_id IS NULL) THEN
339 -- row is NOT cached so select information
340 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
341 END IF;
342 -- has the erpyc record been populated?
343 IF g_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
344 -- no row was found so return NULL
345 RETURN(NULL);
346 ELSE
347 -- return the varchar2 column required
348 IF l_column_name = 'ASSIGNMENT_ID' THEN
349 RETURN(g_erpyc_rec.ASSIGNMENT_ID);
350 ELSIF l_column_name = 'RATE_AMOUNT' THEN
351 RETURN(g_erpyc_rec.RATE_AMOUNT);
352 ELSIF l_column_name = 'MAX_ANNUAL_AMOUNT' THEN
353 RETURN(g_erpyc_rec.MAX_ANNUAL_AMOUNT);
354 ELSIF l_column_name = 'PRTT_ENRT_RSLT_ID' THEN
355 RETURN(g_erpyc_rec.PRTT_ENRT_RSLT_ID);
356 ELSIF l_column_name = 'PRTT_RT_VAL_ID' THEN
357 RETURN(g_erpyc_rec.PRTT_RT_VAL_ID);
358 ELSIF l_column_name = 'ELEMENT_ENTRY_VALUE_ID' THEN
359 RETURN(g_erpyc_rec.ELEMENT_ENTRY_VALUE_ID);
360 ELSIF l_column_name = 'PERSON_ID' THEN
361 RETURN(g_erpyc_rec.PERSON_ID);
362 ELSIF l_column_name = 'PER_IN_LER_ID' THEN
363 RETURN(g_erpyc_rec.PER_IN_LER_ID);
364 ELSE
365 -- column unknown
366 RETURN(NULL);
367 END IF;
368 END IF;
369 END get_erpyc_number;
370 --
371 FUNCTION get_erpyc_date
372 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
373 p_column_name IN VARCHAR2,
374 p_effective_start_date IN DATE,
375 p_effective_end_date IN DATE) RETURN DATE IS
376 --
377 l_column_name VARCHAR2(30) := p_column_name;
378 --
379 BEGIN
380 IF p_prtt_enrt_rslt_id IS NULL THEN
381 RETURN(NULL);
382 END IF;
383 -- is the current record in the cache?
384 IF (p_prtt_enrt_rslt_id <> g_prtt_enrt_rslt_id) OR
385 (g_prtt_enrt_rslt_id IS NULL) THEN
386 -- row is NOT cached so select information
387 select_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
388 END IF;
389 -- has the erpyc record been populated?
390 IF g_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
391 -- no row was found so return NULL
392 RETURN(NULL);
393 ELSE
394 -- return the varchar2 column required
395 IF l_column_name = 'EFFECTIVE_START_DATE' THEN
396 RETURN(g_erpyc_rec.EFFECTIVE_START_DATE);
397 ELSIF l_column_name = 'EFFECTIVE_END_DATE' THEN
398 RETURN(g_erpyc_rec.EFFECTIVE_END_DATE);
399 ELSIF l_column_name = 'RT_STRT_DT' THEN
400 RETURN(g_erpyc_rec.RT_STRT_DT);
401 ELSIF l_column_name = 'RT_END_DT' THEN
402 RETURN(g_erpyc_rec.RT_END_DT);
403 ELSIF l_column_name = 'MIN_RT_STRT_DT' THEN
404 RETURN(g_erpyc_rec.MIN_RT_STRT_DT);
405 ELSIF l_column_name = 'MAX_RT_END_DT' THEN
406 RETURN(g_erpyc_rec.MAX_RT_END_DT);
407 ELSIF l_column_name = 'ELEMENT_ENTRY_ESD' THEN
408 RETURN(g_erpyc_rec.ELEMENT_ENTRY_ESD);
409 ELSIF l_column_name = 'ELEMENT_ENTRY_EED' THEN
410 RETURN(g_erpyc_rec.ELEMENT_ENTRY_EED);
411 ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
412 RETURN(g_erpyc_rec.OABV_LAST_UPDATE_DATE);
413 ELSE
414 -- column unknown
415 RETURN(NULL);
416 END IF;
417 END IF;
418 END get_erpyc_date;
419 --
420 -- Procedure/Function for split views
421 --
422 PROCEDURE select_split_eepyc_rec(p_prtt_enrt_rslt_id IN
423 ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
424 p_effective_start_date IN DATE,
425 p_effective_end_date IN DATE) IS
426 CURSOR csr_pay_interface_oab_value_v IS
427 SELECT pi.*
428 FROM hr_pay_interface_oab_value1_v pi
429 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
430 AND pi.acty_typ_cd = 'EEPYC'
431 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
432 BEGIN
433 -- set the g_split_prtt_enrt_rslt_id global
434 g_split_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
435 g_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
436 --get the eepyc record
437 OPEN csr_pay_interface_oab_value_v;
438 FETCH csr_pay_interface_oab_value_v INTO g_split_eepyc_rec;
439
440 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
441 -- null record as it was not found
442 g_split_eepyc_rec.prtt_enrt_rslt_id := NULL;
443 END IF;
444 CLOSE csr_pay_interface_oab_value_v;
445 EXCEPTION
446 WHEN OTHERS THEN
447 -- unexpected error, close cursor if open and set
448 -- eepyc record to null
449 IF csr_pay_interface_oab_value_v%ISOPEN THEN
450 CLOSE csr_pay_interface_oab_value_v;
451 END IF;
452 -- null eepyc record
453 g_split_eepyc_rec.prtt_enrt_rslt_id := NULL;
454 END select_split_eepyc_rec;
455 --
456 PROCEDURE select_split_erpyc_rec(p_prtt_enrt_rslt_id
457 IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
458 p_effective_start_date IN DATE,
459 p_effective_end_date IN DATE) IS
460 CURSOR csr_pay_interface_oab_value_v IS
461 SELECT pi.*
462 FROM hr_pay_interface_oab_value2_v pi
463 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
464 AND pi.acty_typ_cd = 'ERPYC'
465 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
466 BEGIN
467 -- set the g_split_prtt_enrt_rslt_id global
468 g_split_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
469 g_prtt_enrt_rslt_id := p_prtt_enrt_rslt_id;
470 --get the erpyc record
471 OPEN csr_pay_interface_oab_value_v;
472 FETCH csr_pay_interface_oab_value_v INTO g_split_erpyc_rec;
473 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
474 -- null record as it was not found
475 g_split_erpyc_rec.prtt_enrt_rslt_id := NULL;
476 END IF;
477 CLOSE csr_pay_interface_oab_value_v;
478 EXCEPTION
479 WHEN OTHERS THEN
480 -- unexpected error, close cursor if open and set
481 -- erpyc record to null
482 IF csr_pay_interface_oab_value_v%ISOPEN THEN
483 CLOSE csr_pay_interface_oab_value_v;
484 END IF;
485 -- null erpyc record
486 g_split_erpyc_rec.prtt_enrt_rslt_id := NULL;
487 END select_split_erpyc_rec;
488 --
489 FUNCTION split_eepyc_exist
490 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
491 p_effective_start_date IN DATE,
492 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
493 --
494 CURSOR csr_pay_interface_oab_value_v IS
495 SELECT 1
496 FROM hr_pay_interface_oab_value1_v pi
497 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
498 AND pi.acty_typ_cd = 'EEPYC'
499 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
500 --
501 l_dummy NUMBER(1);
502 --
503 BEGIN
504 OPEN csr_pay_interface_oab_value_v;
505 FETCH csr_pay_interface_oab_value_v INTO l_dummy;
506 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
507 CLOSE csr_pay_interface_oab_value_v;
508 RETURN('N');
509 END IF;
510 CLOSE csr_pay_interface_oab_value_v;
511 -- if we have got this far then at least 1 row was found
512 RETURN('Y');
513 EXCEPTION
514 WHEN OTHERS THEN
515 -- unexpected error
516 IF csr_pay_interface_oab_value_v%ISOPEN THEN
517 CLOSE csr_pay_interface_oab_value_v;
518 END IF;
519 RETURN('N');
520 END split_eepyc_exist;
521 --
522 FUNCTION split_erpyc_exist
523 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
524 p_effective_start_date IN DATE,
525 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
526 --
527 CURSOR csr_pay_interface_oab_value_v IS
528 SELECT 1
529 FROM hr_pay_interface_oab_value2_v pi
530 WHERE pi.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
531 AND pi.acty_typ_cd = 'ERPYC'
532 AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date;
533 --
534 l_dummy NUMBER(1);
535 --
536 BEGIN
537 OPEN csr_pay_interface_oab_value_v;
538 FETCH csr_pay_interface_oab_value_v INTO l_dummy;
539 IF csr_pay_interface_oab_value_v%NOTFOUND THEN
540 CLOSE csr_pay_interface_oab_value_v;
541 RETURN('N');
542 END IF;
543 CLOSE csr_pay_interface_oab_value_v;
544 -- if we have got this far then at least 1 row was found
545 RETURN('Y');
546 EXCEPTION
547 WHEN OTHERS THEN
548 -- unexpected error
549 IF csr_pay_interface_oab_value_v%ISOPEN THEN
550 CLOSE csr_pay_interface_oab_value_v;
551 END IF;
552 RETURN('N');
553 END split_erpyc_exist;
554 --
555 FUNCTION get_split_eepyc_varchar2
556 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
557 p_column_name IN VARCHAR2,
558 p_effective_start_date IN DATE,
559 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
560 --
561 l_column_name VARCHAR2(30) := p_column_name;
562 --
563 BEGIN
564 IF p_prtt_enrt_rslt_id IS NULL THEN
565 RETURN(NULL);
566 END IF;
567 -- is the current record in the cache?
568 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
569 (g_prtt_enrt_rslt_id IS NULL) THEN
570 -- row is NOT cached so select information
571 select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
572 END IF;
573 -- has the eepyc record been populated?
574 IF g_split_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
575 -- no row was found so return NULL
576 RETURN(NULL);
577 ELSE
578 -- return the varchar2 column required
579 IF l_column_name = 'CONTRIBUTION_AMOUNT' THEN
580 RETURN(g_split_eepyc_rec.CONTRIBUTION_AMOUNT);
581 ELSIF l_column_name = 'ACTY_TYP_CD' THEN
582 RETURN(g_split_eepyc_rec.ACTY_TYP_CD);
583 ELSIF l_column_name = 'ASSIGNMENT_NUMBER' THEN
584 RETURN(g_split_eepyc_rec.ASSIGNMENT_NUMBER);
585 ELSIF l_column_name = 'RATE_PERIOD' THEN
586 RETURN(g_split_eepyc_rec.RATE_PERIOD);
587 ELSIF l_column_name = 'RT_TYP_CD' THEN
588 RETURN(g_split_eepyc_rec.RT_TYP_CD);
589 ELSE
590 -- column unknown
591 RETURN(NULL);
592 END IF;
593 END IF;
594 END get_split_eepyc_varchar2;
595 --
596 FUNCTION get_split_eepyc_number
597 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
598 p_column_name IN VARCHAR2,
599 p_effective_start_date IN DATE,
600 p_effective_end_date IN DATE) RETURN NUMBER IS
601 --
602 l_column_name VARCHAR2(30) := p_column_name;
603 --
604 BEGIN
605 IF p_prtt_enrt_rslt_id IS NULL THEN
606 RETURN(NULL);
607 END IF;
608 -- is the current record in the cache?
609 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
610 (g_prtt_enrt_rslt_id IS NULL) THEN
611 -- row is NOT cached so select information
612 select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
613 END IF;
614 -- has the eepyc record been populated?
615 IF g_split_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
616 -- no row was found so return NULL
617 RETURN(NULL);
618 ELSE
619 -- return the varchar2 column required
620 IF l_column_name = 'ASSIGNMENT_ID' THEN
621 RETURN(g_split_eepyc_rec.ASSIGNMENT_ID);
622 ELSIF l_column_name = 'RATE_AMOUNT' THEN
623 RETURN(g_split_eepyc_rec.RATE_AMOUNT);
624 ELSIF l_column_name = 'MAX_ANNUAL_AMOUNT' THEN
625 RETURN(g_split_eepyc_rec.MAX_ANNUAL_AMOUNT);
626 ELSIF l_column_name = 'PRTT_ENRT_RSLT_ID' THEN
627 RETURN(g_split_eepyc_rec.PRTT_ENRT_RSLT_ID);
628 ELSIF l_column_name = 'PRTT_RT_VAL_ID' THEN
629 RETURN(g_split_eepyc_rec.PRTT_RT_VAL_ID);
630 ELSIF l_column_name = 'ELEMENT_ENTRY_VALUE_ID' THEN
631 RETURN(g_split_eepyc_rec.ELEMENT_ENTRY_VALUE_ID);
632 ELSIF l_column_name = 'PERSON_ID' THEN
633 RETURN(g_split_eepyc_rec.PERSON_ID);
634 ELSIF l_column_name = 'PER_IN_LER_ID' THEN
635 RETURN(g_split_eepyc_rec.PER_IN_LER_ID);
636 ELSE
637 -- column unknown
638 RETURN(NULL);
639 END IF;
640 END IF;
641 END get_split_eepyc_number;
642 --
643 FUNCTION get_split_eepyc_date
644 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
645 p_column_name IN VARCHAR2,
646 p_effective_start_date IN DATE,
647 p_effective_end_date IN DATE) RETURN DATE IS
648 --
649 l_column_name VARCHAR2(30) := p_column_name;
650 --
651 BEGIN
652 IF p_prtt_enrt_rslt_id IS NULL THEN
653 RETURN(NULL);
654 END IF;
655 -- is the current record in the cache?
656 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
657 (g_prtt_enrt_rslt_id IS NULL) THEN
658 -- row is NOT cached so select information
659 select_split_eepyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
660 END IF;
661 -- has the eepyc record been populated?
662 IF g_split_eepyc_rec.prtt_enrt_rslt_id IS NULL THEN
663 -- no row was found so return NULL
664 RETURN(NULL);
665 ELSE
666 -- return the varchar2 column required
667 IF l_column_name = 'EFFECTIVE_START_DATE' THEN
668 RETURN(g_split_eepyc_rec.EFFECTIVE_START_DATE);
669 ELSIF l_column_name = 'EFFECTIVE_END_DATE' THEN
670 RETURN(g_split_eepyc_rec.EFFECTIVE_END_DATE);
671 ELSIF l_column_name = 'RT_STRT_DT' THEN
672 RETURN(g_split_eepyc_rec.RT_STRT_DT);
673 ELSIF l_column_name = 'RT_END_DT' THEN
674 RETURN(g_split_eepyc_rec.RT_END_DT);
675 ELSIF l_column_name = 'MIN_RT_STRT_DT' THEN
676 RETURN(g_split_eepyc_rec.MIN_RT_STRT_DT);
677 ELSIF l_column_name = 'MAX_RT_END_DT' THEN
678 RETURN(g_split_eepyc_rec.MAX_RT_END_DT);
679 ELSIF l_column_name = 'ELEMENT_ENTRY_ESD' THEN
680 RETURN(g_split_eepyc_rec.ELEMENT_ENTRY_ESD);
681 ELSIF l_column_name = 'ELEMENT_ENTRY_EED' THEN
682 RETURN(g_split_eepyc_rec.ELEMENT_ENTRY_EED);
683 ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
684 RETURN(g_split_eepyc_rec.OABV_LAST_UPDATE_DATE);
685 ELSE
686 -- column unknown
687 RETURN(NULL);
688 END IF;
689 END IF;
690 END get_split_eepyc_date;
691 --
692 FUNCTION get_split_erpyc_varchar2
693 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
694 p_column_name IN VARCHAR2,
695 p_effective_start_date IN DATE,
696 p_effective_end_date IN DATE) RETURN VARCHAR2 IS
697 --
698 l_column_name VARCHAR2(30) := p_column_name;
699 --
700 BEGIN
701 IF p_prtt_enrt_rslt_id IS NULL THEN
702 RETURN(NULL);
703 END IF;
704 -- is the current record in the cache?
705 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
706 (g_prtt_enrt_rslt_id IS NULL) THEN
707 -- row is NOT cached so select information
708 select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
709 END IF;
710 -- has the erpyc record been populated?
711 IF g_split_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
712 -- no row was found so return NULL
713 RETURN(NULL);
714 ELSE
715 -- return the varchar2 column required
716 IF l_column_name = 'CONTRIBUTION_AMOUNT' THEN
717 RETURN(g_split_erpyc_rec.CONTRIBUTION_AMOUNT);
718 ELSIF l_column_name = 'ACTY_TYP_CD' THEN
719 RETURN(g_split_erpyc_rec.ACTY_TYP_CD);
720 ELSIF l_column_name = 'ASSIGNMENT_NUMBER' THEN
721 RETURN(g_split_erpyc_rec.ASSIGNMENT_NUMBER);
722 ELSIF l_column_name = 'RATE_PERIOD' THEN
723 RETURN(g_split_erpyc_rec.RATE_PERIOD);
724 ELSIF l_column_name = 'RT_TYP_CD' THEN
725 RETURN(g_split_erpyc_rec.RT_TYP_CD);
726 ELSE
727 -- column unknown
728 RETURN(NULL);
729 END IF;
730 END IF;
731 END get_split_erpyc_varchar2;
732 --
733 FUNCTION get_split_erpyc_number
734 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
735 p_column_name IN VARCHAR2,
736 p_effective_start_date IN DATE,
737 p_effective_end_date IN DATE) RETURN NUMBER IS
738 --
739 l_column_name VARCHAR2(30) := p_column_name;
740 --
741 BEGIN
742 IF p_prtt_enrt_rslt_id IS NULL THEN
743 RETURN(NULL);
744 END IF;
745 -- is the current record in the cache?
746 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
747 (g_prtt_enrt_rslt_id IS NULL) THEN
748 -- row is NOT cached so select information
749 select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
750 END IF;
751 -- has the erpyc record been populated?
752 IF g_split_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
753 -- no row was found so return NULL
754 RETURN(NULL);
755 ELSE
756 -- return the varchar2 column required
757 IF l_column_name = 'ASSIGNMENT_ID' THEN
758 RETURN(g_split_erpyc_rec.ASSIGNMENT_ID);
759 ELSIF l_column_name = 'RATE_AMOUNT' THEN
760 RETURN(g_split_erpyc_rec.RATE_AMOUNT);
761 ELSIF l_column_name = 'MAX_ANNUAL_AMOUNT' THEN
762 RETURN(g_split_erpyc_rec.MAX_ANNUAL_AMOUNT);
763 ELSIF l_column_name = 'PRTT_ENRT_RSLT_ID' THEN
764 RETURN(g_split_erpyc_rec.PRTT_ENRT_RSLT_ID);
765 ELSIF l_column_name = 'PRTT_RT_VAL_ID' THEN
766 RETURN(g_split_erpyc_rec.PRTT_RT_VAL_ID);
767 ELSIF l_column_name = 'ELEMENT_ENTRY_VALUE_ID' THEN
768 RETURN(g_split_erpyc_rec.ELEMENT_ENTRY_VALUE_ID);
769 ELSIF l_column_name = 'PERSON_ID' THEN
770 RETURN(g_split_erpyc_rec.PERSON_ID);
771 ELSIF l_column_name = 'PER_IN_LER_ID' THEN
772 RETURN(g_split_erpyc_rec.PER_IN_LER_ID);
773 ELSE
774 -- column unknown
775 RETURN(NULL);
776 END IF;
777 END IF;
778 END get_split_erpyc_number;
779 --
780 FUNCTION get_split_erpyc_date
781 (p_prtt_enrt_rslt_id IN ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE,
782 p_column_name IN VARCHAR2,
783 p_effective_start_date IN DATE,
784 p_effective_end_date IN DATE) RETURN DATE IS
785 --
786 l_column_name VARCHAR2(30) := p_column_name;
787 --
788 BEGIN
789 IF p_prtt_enrt_rslt_id IS NULL THEN
790 RETURN(NULL);
791 END IF;
792 -- is the current record in the cache?
793 IF (p_prtt_enrt_rslt_id <> g_split_prtt_enrt_rslt_id) OR
794 (g_prtt_enrt_rslt_id IS NULL) THEN
795 -- row is NOT cached so select information
796 select_split_erpyc_rec(p_prtt_enrt_rslt_id,p_effective_start_date,p_effective_end_date);
797 END IF;
798 -- has the erpyc record been populated?
799 IF g_split_erpyc_rec.prtt_enrt_rslt_id IS NULL THEN
800 -- no row was found so return NULL
801 RETURN(NULL);
802 ELSE
803 -- return the varchar2 column required
804 IF l_column_name = 'EFFECTIVE_START_DATE' THEN
805 RETURN(g_split_erpyc_rec.EFFECTIVE_START_DATE);
806 ELSIF l_column_name = 'EFFECTIVE_END_DATE' THEN
807 RETURN(g_split_erpyc_rec.EFFECTIVE_END_DATE);
808 ELSIF l_column_name = 'RT_STRT_DT' THEN
809 RETURN(g_split_erpyc_rec.RT_STRT_DT);
810 ELSIF l_column_name = 'RT_END_DT' THEN
811 RETURN(g_split_erpyc_rec.RT_END_DT);
812 ELSIF l_column_name = 'MIN_RT_STRT_DT' THEN
813 RETURN(g_split_erpyc_rec.MIN_RT_STRT_DT);
814 ELSIF l_column_name = 'MAX_RT_END_DT' THEN
815 RETURN(g_split_erpyc_rec.MAX_RT_END_DT);
816 ELSIF l_column_name = 'ELEMENT_ENTRY_ESD' THEN
817 RETURN(g_split_erpyc_rec.ELEMENT_ENTRY_ESD);
818 ELSIF l_column_name = 'ELEMENT_ENTRY_EED' THEN
819 RETURN(g_split_erpyc_rec.ELEMENT_ENTRY_EED);
820 ELSIF l_column_name = 'OABV_LAST_UPDATE_DATE' THEN
821 RETURN(g_split_erpyc_rec.OABV_LAST_UPDATE_DATE);
822 ELSE
823 -- column unknown
824 RETURN(NULL);
825 END IF;
826 END IF;
827 END get_split_erpyc_date;
828
829 ---------------------------------------------------------------
830 PROCEDURE disable_ele_entry_delete
831 ---------------------------------------------------------------
832 IS
833 --
834 -- This procedure returns an error if an attempt is being made to
835 -- perform a Datetrack purge of an element entry. Certain types of Datetrack
836 -- delete are allowable, so we cannot simply prevent all deletes against
837 -- pay_element_entries_f.
838 --
839 -- If we try to access pay_element_entries_f from a delete trigger on
840 -- the same table, we hit a mutating table error. So we must perform
841 -- the delete check against a different table to that against which the
842 -- delete trigger is created.
843 --
844 -- We are making use of the validation rule that states that each
845 -- element entry must have at least one entry value. Within a commit unit,
846 -- rows are deleted from pay_element_entries_f before they are deleted from
847 -- pay_element_entry_values_f.
848 --
849 -- So the delete trigger is created against pay_element_entry_values_f, and
850 -- this procedure looks at pay_element_entries_f. The user is attempting a
851 -- Datetrack purge if there are no parent element entries that match
852 -- the element entry value that is being deleted.
853 --
854 -- This procedure uses the global g_ele_start_date to verify if the
855 -- element entry is current or futured dated.
856 --
857 -- This procedure uses the global g_ele_entry_id and g_ele_link_id
858 -- which are assigned in the set_ele_var_ids procedure.
859 -- The second part of the validation checks the link from the deleted element
860 -- entry to ensure that the element type does not contain either Y,E or D in
861 -- the attribute1 column. If it does then an error is raised.
862 -- Attribute1 in pay_element_types_f is from this point
863 -- to be used as a flag to identify that it is a deduction element
864 --
865 dummy integer;
866 l_attrib1 pay_element_entries_f.attribute1%TYPE := NULL;
867
868 BEGIN
869 --
870 IF g_ele_start_date <= TRUNC(sysdate) THEN
871
872 SELECT COUNT(element_entry_id)
873 INTO dummy
874 FROM pay_element_entries_f
875 WHERE element_entry_id = g_ele_entry_id;
876 --
877 IF (dummy = 0) THEN
878 SELECT pt.attribute1
879 INTO l_attrib1
880 FROM pay_element_types_f pt,
881 pay_element_links_f pl
882 WHERE
883 pl.element_link_id = g_ele_link_id AND
884 pt.element_type_id = pl.element_type_id AND
885 pt.effective_start_date = (SELECT max(pt2.effective_start_date)
886 FROM pay_element_types_f pt2
887 WHERE
888 (pt2.effective_start_date <=
889 TRUNC(sysdate))
890 AND
891 (pt2.element_type_id =
892 pt.element_type_id)) AND
893 pl.effective_start_date = (SELECT max(pt2.effective_start_date)
894 FROM pay_element_links_f pt2
895 WHERE
896 (pt2.effective_start_date <=
897 TRUNC(sysdate))
898 AND
899 (pt2.element_link_id =
900 pl.element_link_id));
901 IF l_attrib1 IN ('Y','E','D') THEN
902 hr_utility.set_message (800, 'PER_ELE_ENTRY_DISABLE_DELETE');
903 hr_utility.raise_error;
904 END IF;
905 END IF;
906 END IF;
907 END disable_ele_entry_delete;
908
909
910 --
911 -------------------------------------------------------------------------
912 PROCEDURE set_ele_var_ids(p_ele_link_id
913 pay_element_entries_f.element_link_id%TYPE,
914 p_ele_entry_id
915 pay_element_entries_f.element_entry_id%TYPE,
916 p_ele_start_date
917 pay_element_entries_f.effective_start_date%TYPE,
918 p_ele_person_id
919 per_all_people_f.person_id%TYPE)
920 -------------------------------------------------------------------------
921 -- This procedure stores the global variables for
922 -- g_ele_link_id, g_ele_entry_id, g_ele_start_date, g_ele_person_id
923 -- These are used by the disable_ele_entry_delete
924 -- to identify element entries and the element links.
925 IS
926 BEGIN
927 g_ele_link_id := p_ele_link_id;
928 g_ele_entry_id := p_ele_entry_id;
929 g_ele_start_date := p_ele_start_date;
930 g_ele_person_id := p_ele_person_id;
931 END set_ele_var_ids;
932 --
933 ---------------------------------------------------------------
934 procedure disable_emp_number_update (p_old_emp_number varchar2 default null,
935 p_new_emp_number varchar2 default null)
936 ---------------------------------------------------------------
937 is
938 -- This procedure returns an error if an attempt is being made to
939 -- update an employee number.
940 -- A change in employee number would result in the
941 -- creation of a new employee record in the vendors payroll.
942 --
943 begin
944 --
945 if p_old_emp_number is not null
946 and p_new_emp_number is not null
947 and p_old_emp_number <> p_new_emp_number then
948 hr_utility.set_message (800, 'PER_EMP_NUMBER_DISABLE_UPDATE');
949 hr_utility.raise_error;
950 end if;
951 --
952 end disable_emp_number_update;
953 --
954 -- -------------------------------------------------------------------------
955 procedure chk_reporting_name_uniqueness
956 -- -------------------------------------------------------------------------
957 is
958 -- This procedure checks to make sure that the reporting name is unique
959 -- within the business_group and legislation. If it isn't, then an error
960 -- is raised. This used to be a constraint on the database, but it was
961 -- removed for R10.
962 -- NOTE : This is called from a statement level 'AFTER' trigger, with the values
963 -- being stored globally from the row level 'BEFORE' trigger.
964 --
965 CURSOR csr_count_same_rep_name IS
966 SELECT 1
967 FROM pay_element_types_f et
968 WHERE et.business_group_id
969 = hr_pay_interface_pkg.g_reporting_details_rec_var.business_group_id
970 AND (NVL(et.legislation_code,-99)
971 = NVL(hr_pay_interface_pkg.g_reporting_details_rec_var.legislation_code,-99))
972 AND UPPER(et.reporting_name)
973 = UPPER(hr_pay_interface_pkg.g_reporting_details_rec_var.reporting_name)
974 AND (et.element_type_id
975 <> hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id
976 OR hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id ='')
977 -- Have to ensure the reporting name doesn't exist on date-tracked
978 -- rows (even though reporting_name isn't datetracked, it still is
979 -- possible to achieve by altering a datetrack row at the same time)
980 AND (hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
981 between et.effective_start_date and et.effective_end_date
982 OR hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
983 between et.effective_start_date and et.effective_end_date
984 OR (hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
985 < et.effective_start_date
986 AND hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
987 > et.effective_end_date)
988 );
989 --
990 l_dummy VARCHAR2(1);
991 begin
992 --
993 OPEN csr_count_same_rep_name;
994 FETCH csr_count_same_rep_name INTO l_dummy;
995 IF csr_count_same_rep_name%FOUND THEN
996 hr_utility.set_message (800, 'PER_REPORTING_NAME_NOT_UNIQUE');
997 --
998 -- There is a element which has the same reporting_name and
999 -- is in the same business_group and legislation_code.
1000 -- This is an error which will prevent the row from being inserted
1001 -- or updated.
1002 --
1003 CLOSE csr_count_same_rep_name;
1004 --
1005 -- Clear out the global record structure
1006 --
1007 hr_pay_interface_pkg.g_reporting_details_rec_var.reporting_name
1008 := '';
1009 hr_pay_interface_pkg.g_reporting_details_rec_var.business_group_id
1010 := '';
1011 hr_pay_interface_pkg.g_reporting_details_rec_var.legislation_code
1012 := '';
1013 hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id
1014 := '' ;
1015 hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
1016 := '' ;
1017 hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
1018 := '' ;
1019 --
1020 -- Raise an application error
1021 --
1022 hr_utility.raise_error;
1023 --
1024 ELSE
1025 --
1026 -- Clear out the global record structure
1027 --
1028 hr_pay_interface_pkg.g_reporting_details_rec_var.reporting_name
1029 := '';
1030 hr_pay_interface_pkg.g_reporting_details_rec_var.business_group_id
1031 := '';
1032 hr_pay_interface_pkg.g_reporting_details_rec_var.legislation_code
1033 := '';
1034 hr_pay_interface_pkg.g_reporting_details_rec_var.element_type_id
1035 := '' ;
1036 hr_pay_interface_pkg.g_reporting_details_rec_var.effective_start_date
1037 := '' ;
1038 hr_pay_interface_pkg.g_reporting_details_rec_var.effective_end_date
1039 := '' ;
1040 --
1041 CLOSE csr_count_same_rep_name;
1042 --
1043 END IF;
1044 --
1045 end chk_reporting_name_uniqueness;
1046 ---------------------------------------------------------------------------
1047 function get_hot_default(p_input_value_id in number,
1048 p_element_link_id in number)
1049 return varchar2 is hot_default_value varchar2(60);
1050 ----------------------------------------------------------------------------
1051 --
1052 -- This function returns the hot default screen value for
1053 -- an element entry input value.
1054 --
1055 -- Firstly, it looks at the element link level -
1056 -- if there is no value here it will then look at the element type level.
1057 -- If there are no values at either level it will return null.
1058 --
1059 -- The max effective start date select statement are there to get the
1060 -- latest row before sysdate
1061 --
1062 begin
1063 select decode(pliv.default_value,
1064 null,
1065 piv.default_value,
1066 pliv.default_value) screen_entry_value
1067 into hot_default_value
1068 from pay_input_values_f piv,
1069 pay_link_input_values_f pliv
1070 where pliv.element_link_id = p_element_link_id
1071 and pliv.effective_start_date =
1072 (select max(pliv2.effective_start_date)
1073 from pay_link_input_values_f pliv2
1074 where pliv2.effective_start_date <= trunc(sysdate)
1075 and pliv2.element_link_id = p_element_link_id
1076 and pliv2.input_value_id = p_input_value_id)
1077 and pliv.input_value_id = p_input_value_id
1078 and piv.effective_start_date =
1079 (select max(piv2.effective_start_date)
1080 from pay_input_values_f piv2
1081 where piv2.effective_start_date <= trunc(sysdate)
1082 and piv2.input_value_id = p_input_value_id
1083 and hot_default_flag = 'Y')
1084 and piv.input_value_id = p_input_value_id
1085 and piv.hot_default_flag = 'Y';
1086 --
1087 return hot_default_value;
1088
1089 end get_hot_default;
1090 -----------------------------------------------------------------------
1091 procedure set_extract_date (p_payroll_extract_date date)
1092 ------------------------------------------------------------------------
1093 is
1094 -- This procedure sets the g_payroll_extract_date variable to the given date.
1095 --
1096 begin
1097 g_payroll_extract_date := p_payroll_extract_date;
1098 hr_adp.g_adp_extract_date := p_payroll_extract_date;
1099 hr_ceridian.g_cer_extract_date := p_payroll_extract_date;
1100 --
1101 end set_extract_date;
1102 --
1103 -----------------------------------------------------------------------
1104 function get_extract_date return date
1105 ------------------------------------------------------------------------
1106 is
1107 -- This function returns the g_payroll_extract_date set by the call to
1108 -- set_payroll_extract_date. If set_payroll_extract_date is never called, it
1109 -- returns the sysdate as g_payroll_extract_date.
1110 --
1111 begin
1112 g_payroll_extract_date := nvl(g_payroll_extract_date, sysdate);
1113 RETURN g_payroll_extract_date;
1114 --
1115 end get_extract_date;
1116 -------------------------------------------------------------------
1117 procedure disable_ppm_update (p_old_priority varchar2 default null,
1118 p_new_priority varchar2 default null)
1119 -------------------------------------------------------------------
1120 is
1121 -- This procedure returns an error if an attempt is being made to
1122 -- update personal payment method priority.
1123 -- A change in priority would result in the
1124 -- creation of a new EFT record in the third party payroll system.
1125 --
1126 begin
1127 --
1128 if p_old_priority is not null
1129 and p_new_priority is not null
1130 and p_old_priority <> p_new_priority then
1131 hr_utility.set_message (800, 'PER_PPM_PRI_DISABLE_UPDATE');
1132 hr_utility.raise_error;
1133 end if;
1134 --
1135 end disable_ppm_update;
1136 --
1137 ---------------------------------------------------------------------
1138 procedure disable_ppm_delete_purge
1139 ---------------------------------------------------------------------
1140 is
1141 -- This procedure returns an error if an attempt is being made to
1142 -- delete a personal payment method.
1143 --
1144 CURSOR csr_ppm_delete_purge is
1145 select 1
1146 from pay_personal_payment_methods_f
1147 where personal_payment_method_id =
1148 HR_PAY_INTERFACE_PKG.g_personal_payment_method_id;
1149 --
1150 CURSOR csr_ppm_post_fpd IS
1151 SELECT 1
1152 FROM per_periods_of_service pps,
1153 per_all_assignments_f paa
1154 WHERE
1155 HR_PAY_INTERFACE_PKG.g_ppm_ass_id = paa.assignment_id
1156 AND paa.person_id = pps.person_id
1157 AND ( pps.final_process_date IS NOT NULL
1158 OR pps.last_standard_process_date IS NOT NULL)
1159 AND HR_PAY_INTERFACE_PKG.g_ppm_start_date > trunc(SYSDATE);
1160 --
1161 l_purge integer := NULL;
1162 l_terminate integer := NULL;
1163 --
1164 begin
1165 --
1166 if (HR_PAY_INTERFACE_PKG.g_personal_payment_method_id IS NOT NULL) AND
1167 HR_PAY_INTERFACE_PKG.g_ppm_start_date <= trunc(SYSDATE) then
1168 open csr_ppm_post_fpd;
1169 fetch csr_ppm_post_fpd into l_terminate;
1170 close csr_ppm_post_fpd;
1171 hr_pay_interface_pkg.g_ppm_start_date := NULL;
1172 hr_pay_interface_pkg.g_ppm_ass_id := NULL;
1173
1174 if l_terminate is NULL then
1175 open csr_ppm_delete_purge;
1176 fetch csr_ppm_delete_purge into l_purge;
1177 close csr_ppm_delete_purge;
1178
1179 HR_PAY_INTERFACE_PKG.g_personal_payment_method_id := NULL;
1180 if l_purge IS NULL then
1181 hr_utility.set_message (800, 'PER_PRS_PAY_MTD_DISABLE_DEL');
1182 hr_utility.raise_error;
1183 -- else
1184 -- HR_PAY_INTERFACE_PKG.g_personal_payment_method_id := NULL;
1185 end if;
1186 end if;
1187 else
1188 HR_PAY_INTERFACE_PKG.g_personal_payment_method_id := NULL;
1189 end if;
1190 --
1191 end disable_ppm_delete_purge;
1192
1193 procedure disable_asg_cost_delete_purge
1194 ---------------------------------------------------------------------
1195 is
1196 --
1197 -- This procedure returns an error if an attempt is being made to
1198 -- delete an assignment costing.
1199 --
1200 CURSOR csr_asg_cost_delete_purge is
1201 select 1
1202 from pay_cost_allocations_f
1203 where cost_allocation_id =
1204 HR_PAY_INTERFACE_PKG.g_cost_allocation_id;
1205 --
1206 CURSOR csr_asg_cost_post_fpd IS
1207 SELECT 1
1208 FROM per_periods_of_service pps,
1209 per_all_assignments_f paa
1210 WHERE
1211 HR_PAY_INTERFACE_PKG.g_asg_cost_ass_id = paa.assignment_id
1212 AND paa.person_id = pps.person_id
1213 AND (pps.final_process_date IS NOT NULL
1214 OR pps.last_standard_process_date IS NOT NULL)
1215 AND HR_PAY_INTERFACE_PKG.g_asg_cost_start_date > trunc(SYSDATE);
1216 --
1217 l_purge integer := NULL;
1218 l_terminate integer := NULL;
1219 --
1220 begin
1221 --
1222 if (HR_PAY_INTERFACE_PKG.g_cost_allocation_id IS NOT NULL) AND
1223 (HR_PAY_INTERFACE_PKG.g_asg_cost_start_date <= trunc(SYSDATE)) then
1224
1225 open csr_asg_cost_post_fpd;
1226 fetch csr_asg_cost_post_fpd into l_terminate;
1227 close csr_asg_cost_post_fpd;
1228 hr_pay_interface_pkg.g_asg_cost_start_date := NULL;
1229 hr_pay_interface_pkg.g_asg_cost_ass_id := NULL;
1230
1231 if l_terminate is NULL then
1232
1233 open csr_asg_cost_delete_purge;
1234 fetch csr_asg_cost_delete_purge into l_purge;
1235 close csr_asg_cost_delete_purge;
1236
1237 HR_PAY_INTERFACE_PKG.g_cost_allocation_id := NULL;
1238 if l_purge IS NULL then
1239 hr_utility.set_message (800, 'PER_ASG_COST_INF_DIS_PRG_DEL');
1240 hr_utility.raise_error;
1241 -- else
1242 -- HR_PAY_INTERFACE_PKG.g_cost_allocation_id := NULL;
1243 end if;
1244 end if;
1245 else
1246 HR_PAY_INTERFACE_PKG.g_cost_allocation_id := NULL;
1247 end if;
1248 --
1249 end disable_asg_cost_delete_purge;
1250
1251 end HR_PAY_INTERFACE_PKG ;
[Home] [Help]