1 PACKAGE BODY PAY_SE_BAL_UPLOAD AS
2 /* $Header: pysebalupl.pkb 120.0.12000000.1 2007/07/23 10:28:24 rravi noship $ */
3
4 -- Date Constants
5 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
6 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
7
8 ----------------------------------------------------------------------------
9 -- Name : EXPIRY_DATE
10 -- Type : FUNCTION
11 -- Access : Public
12 -- Description : Function to return the expiry date of the given
13 -- balance dimension relative to a date.
14 -- Arguments :
15 -- IN :
16 -- p_upload_date - the date on which the balance should be correct.
17 -- p_dimension_name - the dimension being set.
18 -- p_assignment_id - the assignment involved.
19 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
20 -- OUT : N/A
21 -- RETURN : Date
22 --
23 -- NOTES
24 -- This is used by pay_balance_upload.dim_expiry_date.
28 --------------------------------------------------------------------------
25 -- If the expiry date cannot be derived then it is set to the end of time
26 -- to indicate that a failure has occured. The process that uses the
27 -- expiry date knows this rulw and acts accordingly.
29
30 FUNCTION expiry_date
31 (p_upload_date IN DATE,
32 p_dimension_name IN VARCHAR2,
33 p_assignment_id IN NUMBER,
34 p_original_entry_id IN NUMBER)
35 RETURN DATE IS
36
37 -- Returns the start date of the current period on the upload date.
38 CURSOR csr_period_start( p_assignment_id NUMBER
39 , p_upload_date DATE
40 ) IS
41 SELECT NVL(PTP.start_date, END_OF_TIME)
42 FROM per_time_periods PTP
43 ,per_assignments_f ASS
44 WHERE ASS.assignment_id = p_assignment_id
45 AND p_upload_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date
46 AND PTP.payroll_id = ASS.payroll_id
47 AND p_upload_date BETWEEN PTP.start_date AND PTP.end_date;
48
49 CURSOR csr_asg_start_date( p_assignment_id NUMBER
50 , p_upload_date DATE
51 , p_expiry_date DATE
52 ) IS
53 SELECT NVL(GREATEST(MIN(ASS.effective_start_date), p_expiry_date),END_OF_TIME)
54 FROM per_assignments_f ASS
55 WHERE ASS.assignment_id = p_assignment_id
56 AND ASS.effective_start_date <= p_upload_date
57 AND ASS.effective_end_date >= p_expiry_date;
58
59 CURSOR csr_earning_year( csr_v_assignment_id number,
60 csr_v_upload_date date)
61 IS
62 SELECT SUBSTR (hoi4.org_information1, 4, 2)
63 ,SUBSTR (hoi4.org_information2, 4, 2)
64 FROM hr_organization_units o1
65 ,hr_organization_information hoi1
66 ,hr_organization_information hoi2
67 ,hr_organization_information hoi3
68 ,hr_organization_information hoi4
69 , (SELECT TRIM (scl.segment2) AS org_id
70 ,business_group_id AS bus_id
71 FROM per_all_assignments_f asg
72 ,hr_soft_coding_keyflex scl
73 WHERE asg.assignment_id = csr_v_assignment_id
74 AND asg.soft_coding_keyflex_id =
75 scl.soft_coding_keyflex_id
76 AND csr_v_upload_date BETWEEN asg.effective_start_date
77 AND asg.effective_end_date) x
78 WHERE o1.business_group_id = x.bus_id
79 AND hoi1.organization_id = o1.organization_id
80 AND hoi1.organization_id = x.org_id
81 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
82 AND hoi1.org_information_context = 'CLASS'
83 AND o1.organization_id = hoi2.org_information1
84 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
85 AND hoi2.organization_id = hoi3.organization_id
86 AND hoi3.org_information_context = 'CLASS'
87 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
88 AND hoi3.organization_id = hoi4.organization_id
89 AND hoi4.org_information_context = 'SE_HOLIDAY_YEAR_DEFN'
90 AND hoi4.org_information1 IS NOT NULL;
91
92
93 CURSOR csr_saved_year_limit( csr_v_assignment_id number,
94 csr_v_upload_date date)
95 IS
96
97 SELECT HOI4.ORG_INFORMATION2
98 FROM hr_organization_units o1
99 ,hr_organization_information hoi1
100 ,hr_organization_information hoi2
101 ,hr_organization_information hoi3
102 ,hr_organization_information hoi4
103 , (SELECT TRIM (scl.segment2) AS org_id
104 ,business_group_id AS bus_id
105 FROM per_all_assignments_f asg
106 ,hr_soft_coding_keyflex scl
107 WHERE asg.assignment_id = csr_v_assignment_id
108 AND asg.soft_coding_keyflex_id =
109 scl.soft_coding_keyflex_id
110 AND csr_v_upload_date BETWEEN asg.effective_start_date
111 AND asg.effective_end_date) x
112 WHERE o1.business_group_id = x.bus_id
113 AND hoi1.organization_id = o1.organization_id
114 AND hoi1.organization_id = x.org_id
115 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
116 AND hoi1.org_information_context = 'CLASS'
117 AND o1.organization_id = hoi2.org_information1
118 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
119 AND hoi2.organization_id = hoi3.organization_id
120 AND hoi3.org_information_context = 'CLASS'
121 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
122 AND hoi3.organization_id = hoi4.organization_id
123 AND hoi4.org_information_context = 'SE_LE_HOLIDAY_PAY_DETAILS'
124 AND hoi4.org_information1 IS NOT NULL;
125
126
127
128 l_prd_start_date DATE; -- Holds the start of the period for the upload date.
129 l_expiry_date DATE; -- Holds the expiry date of the dimension.
130 l_cal_yr_start_date DATE; -- Holds the start date of the calendar year for the upload date.
131 l_bi_month_start_date DATE; -- Holds the start date of the bi-monthly period for the upload date.
132 l_start_month CHAR(2);
133 l_end_month CHAR(2);
134 l_earn_end_year NUMBER;
135 l_saved_year_limit NUMBER;
136
137 BEGIN
138 -- Calculate the expiry date for the specified dimension relative to the
139 -- upload date, taking into account any contexts where appropriate. Each of
140 -- the calculations also takes into account when the assignment is on a
141 -- payroll to ensure that a balance adjustment could be made at that point
142 -- if it were required.
143
144
145
146 IF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY HOLIDAY YEAR',
147 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR')
148 THEN
149
150
151 OPEN csr_earning_year( p_assignment_id
152 , p_upload_date
156 CLOSE csr_earning_year;
153 );
154 FETCH csr_earning_year
155 INTO l_start_month,l_end_month;
157
158 --
159 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
160
161 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||l_earn_end_year,'DD/MM/YYYY');
162
163 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY EARNING YEAR',
164 'ASSIGNMENT WITHIN LEGAL EMPLOYER EARNING YEAR')
165 THEN
166
167
168
169 OPEN csr_earning_year( p_assignment_id
170 , p_upload_date
171 );
172 FETCH csr_earning_year
173 INTO l_start_month,l_end_month;
174 CLOSE csr_earning_year;
175
176 --
177 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
178
179 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-1),'DD/MM/YYYY');
180
181
182 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT THIRD YEAR') THEN
183
184
185
186 OPEN csr_earning_year( p_assignment_id
187 , p_upload_date
188 );
189 FETCH csr_earning_year
190 INTO l_start_month,l_end_month;
191 CLOSE csr_earning_year;
192
193 --
194 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
195
196 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-2),'DD/MM/YYYY');
197
198
199 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FOURTH YEAR') THEN
200
201
202
203 OPEN csr_earning_year( p_assignment_id
204 , p_upload_date
205 );
206 FETCH csr_earning_year
207 INTO l_start_month,l_end_month;
208 CLOSE csr_earning_year;
209
210 --
211 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
212
213 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-3),'DD/MM/YYYY');
214
215 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FIFTH YEAR') THEN
216
217
218
219 OPEN csr_earning_year( p_assignment_id
220 , p_upload_date
221 );
222 FETCH csr_earning_year
223 INTO l_start_month,l_end_month;
224 CLOSE csr_earning_year;
225
226 --
227 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
228
229 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-4),'DD/MM/YYYY');
230
231
232 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SIXTH YEAR') THEN
233
234
235
236 OPEN csr_earning_year( p_assignment_id
237 , p_upload_date
238 );
239 FETCH csr_earning_year
240 INTO l_start_month,l_end_month;
241 CLOSE csr_earning_year;
242
243 --
244 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
245
246 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-5),'DD/MM/YYYY');
247
248
249 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SEVENTH YEAR') THEN
250
251
252
253 OPEN csr_earning_year( p_assignment_id
254 , p_upload_date
255 );
256 FETCH csr_earning_year
257 INTO l_start_month,l_end_month;
258 CLOSE csr_earning_year;
259
260 --
261 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
262
263 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-6),'DD/MM/YYYY');
264
265
266 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE HOLIDAY YEAR') THEN
267
268
269
270 OPEN csr_earning_year( p_assignment_id
271 , p_upload_date
272 );
273 FETCH csr_earning_year
274 INTO l_start_month,l_end_month;
275 CLOSE csr_earning_year;
276
277 --
278 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
279
280 OPEN csr_saved_year_limit(p_assignment_id
281 , p_upload_date);
282 FETCH csr_saved_year_limit INTO l_saved_year_limit;
283
284 CLOSE csr_saved_year_limit;
285 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-l_saved_year_limit+1),'DD/MM/YYYY');
286
287
288 ELSIF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE EARNING YEAR') THEN
289
290
291
292 OPEN csr_earning_year( p_assignment_id
293 , p_upload_date
294 );
295 FETCH csr_earning_year
296 INTO l_start_month,l_end_month;
297 CLOSE csr_earning_year;
298
299 --
300 l_earn_end_year:=GET_EARN_END_YEAR(l_start_month,l_end_month,p_upload_date );
301
302 OPEN csr_saved_year_limit(p_assignment_id
303 , p_upload_date);
304 FETCH csr_saved_year_limit INTO l_saved_year_limit;
305
306 CLOSE csr_saved_year_limit;
307 l_expiry_date:=TO_DATE('01/'||l_start_month||'/'||(l_earn_end_year-l_saved_year_limit),'DD/MM/YYYY');
308
309
310 ELSIF p_dimension_name IN ('ASSIGNMENT LAST 13 MONTHS DIMENSION FOR SE LEGISLATION') THEN
314 END IF;
311 l_expiry_date:=trunc(ADD_MONTHS(p_upload_date,-12),'MM');
312 ELSIF p_dimension_name IN ('LEGAL EMPLOYER MONTH') THEN
313 l_expiry_date:=trunc(p_upload_date,'MM');
315
316 RETURN l_expiry_date;
317
318
319
320 EXCEPTION
321 WHEN no_data_found THEN
322 l_expiry_date := END_OF_TIME;
323 RETURN l_expiry_date;
324
325 END expiry_date;
326
327 --------------------------------------------------------------------------
328 -- Name : IS_SUPPORTED
329 -- Type : FUNCTION
330 -- Access : Public
331 -- Description : Function to check if the specified dimension is
332 -- supported by the upload process.
333 --
334 -- Arguments :
335 -- IN : p_dimension_name - the balance dimension to be checked.
336 -- OUT : N/A
337 -- RETURN : Number
338 --
339 -- NOTES
340 -- Only a subset of the NO dimensions are supported.
341 -- This is used by pay_balance_upload.validate_dimension.
342 --------------------------------------------------------------------------
343
344 FUNCTION is_supported
345 (p_dimension_name varchar2)
346 RETURN number IS
347
348 BEGIN
349
350
351 IF p_dimension_name in ('ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY HOLIDAY YEAR',
352 'ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY EARNING YEAR',
353 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SEVENTH YEAR',
354 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SIXTH YEAR',
355 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FIFTH YEAR',
356 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FOURTH YEAR',
357 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT THIRD YEAR',
358 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE HOLIDAY YEAR',
359 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE EARNING YEAR',
360 'ASSIGNMENT LAST 13 MONTHS DIMENSION FOR SE LEGISLATION',
361 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR',
362 'ASSIGNMENT WITHIN LEGAL EMPLOYER EARNING YEAR',
363 'LEGAL EMPLOYER MONTH') THEN
364 RETURN 1;
365 ELSE
366 RETURN 0;
367 END IF;
368
369 END is_supported;
370
371 --------------------------------------------------------------------------
372 -- Name : INCLUDE_ADJUSTMENT
373 -- Type : FUNCTION
374 -- Access : Public
375 -- Description : Given a dimension, and relevant contexts and details of an existing
376 -- balanmce adjustment, it will find out if the balance adjustment effects
377 -- the dimension to be set. Both the dimension to be set and the adjustment
378 -- are for the same assignment and balance. The adjustment also lies between
379 -- the expiry date of the new balance and the date on which it is to set.
380 --
381 -- Arguments :
382 -- IN :
383 -- p_balance_type_id - the balance to be set.
384 -- p_dimension_name - the balance dimension to be set.
385 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
386 -- p_upload_date
387 -- p_batch_line_id
388 -- p_test_batch_line_id
389 -- OUT : N/A
390 -- RETURN : Number
391 --
392 -- Notes
393 -- all the NO dimensions affect each other when they share the same context
394 -- values so there is no special support required for individual dimensions.
395 -- this is used by pay_balance_upload.get_current_value.
396 --------------------------------------------------------------------------
397 FUNCTION include_adjustment
398 (
399 p_balance_type_id NUMBER
400 ,p_dimension_name VARCHAR2
401 ,p_original_entry_id NUMBER
402 ,p_upload_date DATE
403 ,p_batch_line_id NUMBER
404 ,p_test_batch_line_id NUMBER
405 ) RETURN number IS
406
407 CURSOR csr_bal_adj (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
408 SELECT tba.source_text , tba.tax_unit_id
409 FROM pay_temp_balance_adjustments tba,
410 pay_balance_batch_lines bbl
411 WHERE tba.balance_type_id = bbl.balance_type_id
412 AND tba.batch_line_id = p_test_batch_line_id
413 AND bbl.batch_line_id = p_batch_line_id
414 AND nvl(tba.source_text ,0) = nvl(bbl.source_text ,0);
415
416
417 l_include_adj Number := 1 ; -- True
418 v_cur_bal_adj_st1 csr_bal_adj%ROWTYPE;
419
420 BEGIN
421
422
423 -- When not to allow adjustment
424 -- Suppose,we want _ASG_ELE_CODE_PTD (Source_text 'test1' and balance value 7000)
425 -- _ASG_ELE_CODE_PTD (Source_text 'test2' and balance value 4000)
426 -- Here we expect _ASG_PTD to be 11000 after upload
427 -- In this case,if adjustment is allowed (True)
428 -- _ASG_PTD = 4000
429 -- (Source_text 'test1' and YTD balance value 7000)
430 -- (Source_text 'test2' and YTD balance value -3000)
431 -- balance values will be adjusted to get make the balance satisy last entry
432 -- Hence donot allow adjustment.
433
434 IF p_dimension_name IN ('ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY HOLIDAY YEAR',
435 'ASSIGNMENT WITHIN LEGAL EMPLOYER ABSENCE CATEGORY EARNING YEAR',
436 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SEVENTH YEAR',
437 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT SIXTH YEAR',
438 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FIFTH YEAR',
439 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT FOURTH YEAR',
440 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT THIRD YEAR',
441 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE HOLIDAY YEAR',
442 'ASSIGNMENT WITHIN LEGAL EMPLOYER SAVED HOLIDAY LIMIT BEFORE EARNING YEAR',
443 'ASSIGNMENT LAST 13 MONTHS DIMENSION FOR SE LEGISLATION',
444 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR',
445 'ASSIGNMENT WITHIN LEGAL EMPLOYER EARNING YEAR',
446 'LEGAL EMPLOYER MONTH') THEN
447 l_include_adj := 0 ;
448 /* OPEN csr_bal_adj(p_test_batch_line_id => p_test_batch_line_id,
449 p_batch_line_id => p_batch_line_id);
450
451 FETCH csr_bal_adj INTO v_cur_bal_adj_st1;
452
453 IF csr_bal_adj%NOTFOUND THEN
454 l_include_adj := 0 ; -- False
455 END IF;
456 CLOSE csr_bal_adj;
457 ELSE
458 NULL;*/
459 END IF;
460
461
462 RETURN l_include_adj;
463
464 END include_adjustment;
465
466 PROCEDURE validate_batch_lines ( p_batch_id IN NUMBER) is
467 BEGIN
468 null;
469 END;
470
471 FUNCTION GET_EARN_END_YEAR(p_start_month varchar2,
472 p_end_month varchar2,
473 p_upload_date date)
474 RETURN NUMBER IS
475
476 BEGIN
477
478 IF p_start_month = '01' AND p_end_month = '12'
479 THEN
480
481 RETURN TO_NUMBER (TO_CHAR (p_upload_date, 'YYYY'));
482 ELSE
483 IF TO_NUMBER (TO_CHAR (p_upload_date, 'MM')) <
484 TO_NUMBER (p_start_month)
485 THEN
486
487 RETURN TO_NUMBER (TO_CHAR (p_upload_date, 'YYYY') - 1);
488 ELSE
489
490 RETURN TO_NUMBER (TO_CHAR (p_upload_date, 'YYYY'));
491 END IF;
492 END IF;
493
494 END;
495
496 END PAY_SE_BAL_UPLOAD;
[Home] [Help]