1 PACKAGE BODY pay_mx_bal_upload AS
2 /* $Header: pymxupld.pkb 120.1 2005/12/28 14:29 vmehta 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 --------------------------------------------------------------------------
10 -- Name : EXPIRY_DATE --
11 -- Type : FUNCTION --
12 -- Access : Public --
13 -- Description : Function to return the expiry date of a --
14 -- given dimension relative to a upload date --
15 -- Parameters : --
16 -- IN : p_upload_date DATE --
17 -- the date on which the balance should be correct --
18 -- p_dimension_name VARCHAR2 --
19 -- the dimension being set --
20 -- p_assignment_id NUMBER --
21 -- the assignment involved --
22 -- p_original_entry_id NUMBER --
23 -- ORIGINAL_ENTRY_ID context --
24 -- OUT : N/A --
25 -- RETURN : Date --
26 --------------------------------------------------------------------------
27 FUNCTION expiry_date
28 (p_upload_date IN DATE,
29 p_dimension_name IN VARCHAR2,
30 p_assignment_id IN NUMBER,
31 p_original_entry_id IN NUMBER)
32 RETURN DATE IS
33
34 --
35 -- Returns the earliest date on which the assignment exists
36 --
37 cursor csr_ele_itd_start
38 (
39 p_assignment_id number
40 ,p_upload_date date
41 ) is
42
43 select nvl(min(ASG.effective_start_date), END_OF_TIME)
44 from per_all_assignments_f ASG
45 where ASG.assignment_id = p_assignment_id
46 and ASG.effective_start_date <= p_upload_date;
47
48 --
49 -- Returns the start date of the current period on the upload date.
50 --
51 cursor csr_period_start
52 (
53 p_assignment_id number
54 ,p_upload_date date
55 ) is
56 select nvl(PTP.start_date, END_OF_TIME)
57 from per_time_periods PTP
58 ,per_assignments_f ASS
59 where ASS.assignment_id = p_assignment_id
60 and p_upload_date between ASS.effective_start_date
61 and ASS.effective_end_date
62 and PTP.payroll_id = ASS.payroll_id
63 and p_upload_date between PTP.start_date
64 and PTP.end_date;
65
66 --
67 -- Returns the assignment start date
68 --
69
70 cursor csr_asg_start_date
71 (p_assignment_id number
72 ,p_upload_date date
73 ,p_expiry_date date
74 ) is
75 select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
76 END_OF_TIME)
77 from per_all_assignments_f ASS
78 where ASS.assignment_id = p_assignment_id
79 and ASS.effective_start_date <= p_upload_date
80 and ASS.effective_end_date >= p_expiry_date;
81
82 --
83 -- Returns the bimonth date relative to upload date
84 --
85
86 cursor csr_get_bimonth_date
87 (p_upload_date date
88 ) is
89 select ADD_MONTHS(TRUNC(p_upload_date, 'Y'),
90 TO_CHAR(p_upload_date, 'MM') -
91 DECODE(MOD(TO_NUMBER(TO_CHAR(p_upload_date,'MM')),2),
92 1, 1,
93 0, 2)
94 )
95 from dual ;
96
97 -- Holds the start of the period for the upload date.
98 l_prd_start_date date;
99
100 -- Holds the start of the tax month for the upload date.
101 l_tax_month_start_date date;
102
103 -- Holds the start of the tax year for the upload date.
104 l_tax_yr_start_date date;
105
106 -- Holds the start of the bimonth date for the upload date.
107 l_bimonth_date date;
108
109 l_expiry_date date;
110
111 Begin
112
113 if p_dimension_name in ( 'ASSIGNMENT WITHIN GRE INCEPTION TO DATE' ) then
114
115 --
116 -- What is the earliest date on which the element entry exists and the
117 -- assignment belongs to a specific legal company ??
118 --
119 open csr_ele_itd_start(p_assignment_id
120 ,p_upload_date
121 );
122 fetch csr_ele_itd_start into l_expiry_date;
123 close csr_ele_itd_start;
124 --
125
126 elsif p_dimension_name in
127 ( 'PERSON WITHIN PAYROLL AND GRE PERIOD TO DATE',
128 'ASSIGNMENT WITHIN GRE PERIOD TO DATE' ) then
129
130 --
131 -- What is the current period start date ?
132 --
133 open csr_period_start(p_assignment_id
134 ,p_upload_date);
135 fetch csr_period_start into l_prd_start_date;
136 close csr_period_start;
137
138 open csr_asg_start_date(p_assignment_id
139 ,p_upload_date
140 ,l_prd_start_date);
141 fetch csr_asg_start_date into l_expiry_date;
142 close csr_asg_start_date;
143
144
145 elsif p_dimension_name in
146 ( 'PERSON WITHIN GRE MONTH TO DATE',
147 'ASSIGNMENT WITHIN GRE MONTH TO DATE' ) then
148
149 l_tax_month_start_date := trunc(p_upload_date, 'MM');
150 open csr_asg_start_date(p_assignment_id
151 ,p_upload_date
152 ,l_tax_month_start_date);
153 fetch csr_asg_start_date into l_expiry_date;
154 close csr_asg_start_date;
155
156
157 elsif p_dimension_name in
158 ('PERSON WITHIN GRE YEAR TO DATE',
159 'ASSIGNMENT WITHIN GRE YEAR TO DATE' ) then
160
161 --
162 -- What is the start date of the tax year ?
163 --
164 l_tax_yr_start_date := trunc(p_upload_date, 'Y');
165 open csr_asg_start_date(p_assignment_id
166 ,p_upload_date
167 ,l_tax_yr_start_date);
168 fetch csr_asg_start_date into l_expiry_date;
169 close csr_asg_start_date;
170
171 elsif p_dimension_name in
172 ( 'PERSON WITHIN GOVERNMENT REPORTING ENTITY FOR SOCIAL SECURITY BI-MONTH' ) then
173 -- nearest two month period from the upload date
174 -- upload date = 17-Feb-2005 then expiry_date= 01-Jan-2005
175 -- upload date = 20-Mar-2005 then expiry_date= 01-Mar-2005
176
177 open csr_get_bimonth_date(p_upload_date);
178 fetch csr_get_bimonth_date into l_bimonth_date;
179 close csr_get_bimonth_date;
180
181 open csr_asg_start_date(p_assignment_id
182 ,p_upload_date
183 ,l_bimonth_date);
184 fetch csr_asg_start_date into l_expiry_date;
185 close csr_asg_start_date;
186
187 end if;
188
189 --
190 -- return the date on which the dimension expires.
191 --
192
193 RETURN l_expiry_date;
194
195 END expiry_date;
196
197
198 --------------------------------------------------------------------------
199 -- Name : IS_SUPPORTED --
200 -- Type : FUNCTION --
201 -- Access : Public --
202 -- Description : Function to check if the specified dimension is --
203 -- supported for Mexico Localization --
204 -- Parameters : --
205 -- IN : p_dimension_name VARCHAR2 --
206 -- OUT : N/A --
207 -- RETURN : Number --
208 --------------------------------------------------------------------------
209 FUNCTION is_supported ( p_dimension_name VARCHAR2)
210 RETURN NUMBER
211 IS
212 BEGIN
213 --
214 -- hr_utility.trace('Entering pay_mx_bal_upload.is_supported');
215 --
216 -- See if the dimension is supported.
217 --
218 if p_dimension_name IN
219 ('PERSON WITHIN GRE YEAR TO DATE'
220 ,'PERSON WITHIN GRE MONTH TO DATE'
221 ,'PERSON WITHIN PAYROLL AND GRE PERIOD TO DATE'
222 ,'ASSIGNMENT WITHIN GRE YEAR TO DATE'
223 ,'ASSIGNMENT WITHIN GRE MONTH TO DATE'
224 ,'ASSIGNMENT WITHIN GRE PERIOD TO DATE'
225 ,'PERSON WITHIN GOVERNMENT REPORTING ENTITY FOR SOCIAL SECURITY BI-MONTH'
226 ,'ASSIGNMENT WITHIN GRE INCEPTION TO DATE' )
227 then
228 RETURN (1); -- denotes TRUE
229 else
230 RETURN (0); -- denotes FALSE
231 end if;
232 --
233 -- hr_utility.trace('Exiting pay_mx_bal_upload.is_supported');
234 --
235 END is_supported;
236
237
238 -- Function to check if adjustment is required for a particular Dimension.
239 -- p_test_batch_line_id identifies the adjustment that has already been processed
240 -- p_batch_line_id identifies the adjustment currently being processed.
241 --------------------------------------------------------------------------
242 -- --
243 -- Name : INCLUDE_ADJUSTMENT --
244 -- Type : FUNCTION --
245 -- Access : Public --
246 -- Description : Function to perform balance adjustment Given a --
247 -- dimension, and relevant contexts and details of an --
248 -- existing balance adjustment, it will find out --
249 -- if the balance adjustment effects the dimension to --
250 -- be set. Both the dimension to be set and the --
251 -- adjustment are for the same assignment and balance. --
252 -- The adjustment also lies between the expiry date of --
253 -- the new balance and the date on which it is to set. --
254 -- Parameters : --
255 -- IN : p_balance_type_id NUMBER --
256 -- p_dimension_name VARCHAR2 --
257 -- p_original_entry_id NUMBER --
258 -- p_upload_date DATE --
259 -- p_batch_line_id NUMBER --
260 -- p_test_batch_line_id NUMBER --
261 -- OUT : N/A --
262 -- RETURN : Number --
263 --------------------------------------------------------------------------
264
265 FUNCTION include_adjustment
266 (
267 p_balance_type_id NUMBER
268 ,p_dimension_name VARCHAR2
269 ,p_original_entry_id NUMBER
270 ,p_upload_date DATE
271 ,p_batch_line_id NUMBER
272 ,p_test_batch_line_id NUMBER
273 )
274 RETURN NUMBER
275 IS
276
277 -- Does the balance adjustment effect the new balance dimension.
278 CURSOR csr_is_included( p_balance_type_id NUMBER
279 , p_source_id NUMBER
280 , p_original_entry_id NUMBER
281 , p_bal_adj_source_id NUMBER
282 , p_bal_adj_original_entry_id NUMBER
283 ) IS
284 SELECT BT.balance_type_id
285 FROM pay_balance_types BT
286 WHERE BT.balance_type_id = p_balance_type_id
287 --
288 -- JURISDICTION_CODE context NB. if the jurisdiction code is
289 -- used then only those adjustments which are for the same
290 -- jurisdiction code can be included.
291 --
292 and ((p_source_id is null) or
293 (p_source_id is not null and p_source_id = p_bal_adj_source_id))
294 --
295 --
296 -- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
297 -- date of the dimension in the same way as the QTD dimension. Any
298 -- existing balance adjustments that lie between the upload date
299 -- and the expiry date are all included. There is no special
300 -- criteria that has to be met.
301 --
302 AND 1 = 1;
303
304 l_bal_type_id pay_balance_types.balance_type_id%TYPE;
305
306 -- Get the jurisdiction code from the original balance batch line
307 --
308 cursor csr_get_source_id(p_batch_line_id number)
309 is
310 select pbbl.source_id
311 from pay_balance_batch_lines pbbl
312 ,hr_organization_units hou
313 where pbbl.batch_line_id = p_batch_line_id
314 and pbbl.source_id=hou.organization_id;
315
316 -- Get jurisdiction code and original_entry_id for previously tested adjustments
317 CURSOR csr_get_tested_adjustments(p_test_batch_line_id NUMBER) IS
318 SELECT source_id
319 ,original_entry_id
320 FROM pay_temp_balance_adjustments
321 WHERE batch_line_id = p_test_batch_line_id;
322
323 -- The balance returned by the include check.
324
325 l_adj_orig_entry_id pay_temp_balance_adjustments.original_entry_id%TYPE;
326 l_source_id number;
327 l_adj_source_id number;
328
329 BEGIN
330
331
332 OPEN csr_get_source_id(p_batch_line_id);
333 FETCH csr_get_source_id INTO l_source_id;
334 CLOSE csr_get_source_id;
335 --
336
337 OPEN csr_get_tested_adjustments(p_test_batch_line_id);
338 FETCH csr_get_tested_adjustments
339 INTO l_adj_source_id
340 , l_adj_orig_entry_id;
341 CLOSE csr_get_tested_adjustments;
342
343 -- Does the balance adjustment effect the new balance ?
344
345 hr_utility.trace('balance_type_id = '||TO_CHAR(p_balance_type_id));
346
347 --hr_utility.trace('jurisdiction_id = '||TO_CHAR(l_jurisdiction_code));
348 hr_utility.trace('original_entry_id = '||TO_CHAR(p_original_entry_id));
349
350 -- hr_utility.trace('BA jurisdiction_id = '||TO_CHAR(l_adj_jurisdiction_code));
351 hr_utility.trace('BA original_entry_id = '||TO_CHAR(l_adj_orig_entry_id));
352
353 OPEN csr_is_included(p_balance_type_id
354 ,l_source_id
355 ,p_original_entry_id
356 ,l_adj_source_id
357 ,l_adj_orig_entry_id
358 );
359 FETCH csr_is_included INTO l_bal_type_id;
360 CLOSE csr_is_included;
361
362 --hr_utility.trace('Exiting pay_mx_bal_upload.include_adjustment_test');
363
364 -- Adjustment does contribute to the new balance.
365
366 IF l_bal_type_id IS NOT NULL THEN
367 RETURN (1); --TRUE
368
369 -- Adjustment does not contribute to the new balance.
370 ELSE
371 RETURN (0); --FALSE
372
373 END IF;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 IF csr_is_included%ISOPEN THEN
378 CLOSE csr_is_included;
379 END IF;
380
381 IF csr_get_source_id%ISOPEN THEN
382 CLOSE csr_is_included;
383 END IF;
384
385 IF csr_get_tested_adjustments%ISOPEN THEN
386 CLOSE csr_get_tested_adjustments;
387 END IF;
388
389 RAISE;
390 END include_adjustment;
391
392 --------------------------------------------------------------------------
393 -- --
394 -- Name : VALIDATE_BATCH_LINES --
395 -- Type : PROCEDURE --
396 -- Access : Public --
397 -- Description : Procedure to apply MX specific validation --
398 -- and/or user-defined validation to the batch --
399 -- --
400 -- Parameters : --
401 -- IN : p_batch_id NUMBER --
402 -- OUT : N/A --
403 --------------------------------------------------------------------------
404 PROCEDURE validate_batch_lines (p_batch_id NUMBER)
405 IS
406
407 BEGIN
408 --
409 hr_utility.trace('Entering pay_mx_bal_upload.validate_batch_lines');
410 --
411 hr_utility.trace('Exiting pay_mx_bal_upload.validate_batch_lines');
412 --
413 END validate_batch_lines;
414
415 END pay_mx_bal_upload;
416
[Home] [Help]