1 PACKAGE BODY pay_es_bal_upload AS
2 /* $Header: pyesbupl.pkb 120.1 2005/05/31 02:02:09 vbattu noship $ */
3 --
4 -- Date constants.
5 --
6 start_of_time CONSTANT DATE := TO_date('01/01/0001','DD/MM/YYYY');
7 end_of_time CONSTANT DATE := TO_date('31/12/4712','DD/MM/YYYY');
8 -----------------------------------------------------------------------------
9 -- NAME
10 -- expiry_date
11 -- PURPOSE
12 -- Returns the expiry date of a given dimension relative to a date.
13 -- ARGUMENTS
14 -- p_upload_date - the date on which the balance should be correct.
15 -- p_dimension_name - the dimension being set.
16 -- p_assignment_id - the assignment involved.
17 -- p_original_entry_id - Original_entry_id context.
18 -- USES
19 -- NOTES
20 -- This is used by pay_balance_upload.dim_expiry_date.
21 -- If the expiry date cannot be derived then it is set to the end of time
22 -- to indicate that a failure has occured. The process that uses the
23 -- expiry DATE knows this rule and acts accordingly.
24 -----------------------------------------------------------------------------
25 FUNCTION expiry_date (p_upload_date DATE
26 ,p_dimension_name VARCHAR2
27 ,p_assignment_id NUMBER
28 ,p_original_entry_id NUMBER) RETURN DATE IS
29 -- Returns the legislative start date
30 CURSOR csr_tax_start_date IS
31 SELECT rule_mode
32 FROM pay_legislation_rules
33 WHERE legislation_code ='ES'
34 AND rule_type = 'L';
35 -- Holds the legislative start date
36 l_leg_start pay_legislation_rules.rule_mode%TYPE;
37 -- Returns the start date of the first period of the tax year in which
38 -- the upload date falls.
39 -- 1) to_char(to_date(p_upload_date,'DD/MM/YYYY'),'YYYY')
40 -- retuned incorrect output depening on the date setting
41 -- of the env. to_date(p_upload_date,'DD/MM/YYYY') is not needed as
42 -- p_upload_date is a date!
43 -- 2) if the upload is being done on say 01-jan-2003, it would return null
44 -- record as below condition will not be fulfilled
45 -- ptp2.start_date between
46 -- to_date(l_leg_start||to_char(to_date(
47 -- p_upload_date,'DD/MM/YYYY'),'YYYY'),'DD/MM/YYYY')
48 -- and ptp.end_date;
49 -- But due to 1, condition 2 did not happen and it returned the
50 -- first period of the payroll which is incorrect.
51 -- if 1 is fixed , then condtion 2 would occur.
52 -- Returns the start DATE of the current period on the upload date.
53 CURSOR csr_period_start (p_assignment_id NUMBER
54 ,p_upload_date DATE) IS
55 SELECT nvl(ptp.start_date, end_of_time)
56 FROM per_time_periods ptp
57 ,per_assignments_f ass
58 WHERE ass.assignment_id = p_assignment_id
59 AND ptp.payroll_id = ass.payroll_id
60 AND p_upload_date between ass.effective_start_date and ass.effective_end_date
61 AND p_upload_date between ptp.start_date and ptp.end_date;
62 -- Returns the start date of the assignment.
63 CURSOR csr_asg_itd_start(p_assignment_id NUMBER
64 ,p_upload_date DATE) IS
65 SELECT nvl(min(ass.effective_start_date), end_of_time)
66 FROM per_assignments_f ass
67 WHERE ass.assignment_id = p_assignment_id
68 AND ass.payroll_id IS NOT NULL
69 AND ass.effective_start_date <= p_upload_date;
70 -- Generic start date variable.
71 l_start_date DATE;
72 -- Holds the assignment start date.
73 l_asg_itd_start_date DATE;
74 --Holds the LQTD start date.
75 l_lqtd_start_date DATE;
76 --Holds month start date
77 l_month_start_date DATE;
78 -- Holds the first regular payment date after the expiry date of the dimension.
79 l_regular_date DATE;
80 -- Holds the expiry date of the dimension.
81 l_expiry_date DATE;
82 -- Holds the business group of the dimension.
83 l_business_group_id NUMBER;
84 -- Holds the start date of the quarter.
85 l_qtr_start_date DATE;
86 -- Holds theearliest date an element entry
87 l_ele_itd_start_date DATE;
88 --Used for _ASG_LQTD expiry date calculation
89 BEGIN_date DATE;
90 end_date DATE;
91 CURSOR csr_payroll_start_date (p_assignment_id NUMBER
92 ,p_upload_date DATE) IS
93 SELECT nvl(ppf.effective_start_date, end_of_time)
94 FROM per_all_assignments_f ass
95 ,pay_all_payrolls_f ppf
96 WHERE ass.assignment_id = p_assignment_id
97 AND p_upload_date BETWEEN
98 nvl(ass.effective_start_date,p_upload_date) AND
99 nvl(ass.effective_end_date,p_upload_date)
100 AND ppf.payroll_id = ass.payroll_id
101 AND p_upload_date BETWEEN
102 nvl(ppf.effective_start_date,p_upload_date) AND
103 nvl(ppf.effective_end_date,p_upload_date);
104 --
105 -- Returns the earliest DATE on which the element entry exists.
106 --
107 CURSOR csr_ele_itd_start(p_assignment_id NUMBER
108 ,p_upload_date DATE
109 ,p_original_entry_id NUMBER) IS
110 SELECT nvl(min(EE.effective_start_date), end_of_time)
111 FROM pay_element_entries_f EE
112 WHERE EE.assignment_id = p_assignment_id
113 AND (EE.element_entry_id = p_original_entry_id OR
114 EE.original_entry_id = p_original_entry_id)
115 AND EE.effective_start_date <= p_upload_date;
116 --
117 --Holds the tax year start DATE for the upload process
118 l_tax_year DATE;
119 --------------------------------------------------------------------------------------
120 BEGIN -- Expiry_date - Main --
121 --------------------------------------------------------------------------------------
122 -- What is the start DATE of the assignment? All loading must come after this DATE.
123 OPEN csr_asg_itd_start(p_assignment_id
124 ,p_upload_date);
125 FETCH csr_asg_itd_start INTO l_asg_itd_start_date;
126 CLOSE csr_asg_itd_start;
127 -- RETURN the date on which the dimension expires.
128 IF substr(p_dimension_name, 31, 4) = 'USER' THEN
129 -- User balance
130 SELECT business_group_id
131 INTO l_business_group_id
132 FROM per_assignments_f
133 WHERE assignment_id = p_assignment_id;
134 l_expiry_date := hr_gbbal.dimension_reset_date(p_dimension_name
135 ,p_upload_date
136 ,l_business_group_id);
137 l_expiry_date := GREATEST(l_expiry_date, l_asg_itd_start_date);
138 ELSIF p_dimension_name IN ('ASSIGNMENT DEDUCTION KEY PERIOD TO DATE',
139 'ASSIGNMENT PAYMENT KEY PERIOD TO DATE') THEN
140 -- Calculate expiry DATE for '_PTD' dimensions.
141 -- What is the current period start DATE?
142 OPEN csr_period_start(p_assignment_id
143 ,p_upload_date);
144 FETCH csr_period_start INTO l_start_date;
145 CLOSE csr_period_start;
146 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date);
147 ELSIF p_dimension_name IN ('ASSIGNMENT DEDUCTION KEY CALENDAR YEAR TO DATE',
148 'ASSIGNMENT PAYMENT KEY CALENDAR YEAR TO DATE') THEN
149 OPEN csr_tax_start_date;
150 FETCH csr_tax_start_date INTO l_leg_start;
151 CLOSE csr_tax_start_date;
155 OPEN csr_payroll_start_date(p_assignment_id
152 -- Calculate expiry date for '_YTD' dimensions.
153 -- What is the current tax year start DATE?
154 -- calculate the the payroll start DATE
156 ,p_upload_date);
157 FETCH csr_payroll_start_date INTO l_start_date;
158 CLOSE csr_payroll_start_date;
159 -- calculate the tax year start date for the upload process
160 l_tax_year := to_date(l_leg_start || to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
161 -- calculate the expiry DATE
162 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date,l_tax_year );
163 ELSIF p_dimension_name IN ('ASSIGNMENT DEDUCTION KEY CALENDAR QUARTER TO DATE',
164 'ASSIGNMENT PAYMENT KEY CALENDAR QUARTER TO DATE') THEN
165 -- calculate the qtr start date
166 l_qtr_start_date := trunc(p_upload_date,'Q');
167 -- calculate the the payroll start date
168 OPEN csr_payroll_start_date(p_assignment_id
169 ,p_upload_date);
170 FETCH csr_payroll_start_date INTO l_start_date;
171 CLOSE csr_payroll_start_date;
172 -- calculate the expiry date
173 l_expiry_date := greatest(l_start_date, l_asg_itd_start_date, l_qtr_start_date );
174 END IF;
175 RETURN nvl(l_expiry_date,end_of_time);
176 EXCEPTION
177 WHEN no_data_found THEN
178 l_expiry_date := end_of_time;
179 RETURN l_expiry_date;
180 END expiry_date;
181 -----------------------------------------------------------------------------
182 -- NAME
183 -- is_supported
184 -- PURPOSE
185 -- Checks if the dimension is supported by the upload process.
186 -- ARGUMENTS
187 -- p_dimension_name - the balance dimension to be checked.
188 -- USES
189 -- NOTES
190 -- Only a subset of the ES dimensions are supported.
191 -- This is used by pay_balance_upload.validate_dimension.
192 -----------------------------------------------------------------------------
193 FUNCTION is_supported(p_dimension_name VARCHAR2) RETURN NUMBER IS
194 p_dimension_name_temp VARCHAR2(100);
195 BEGIN
196 -- See if the dimension is supported.
197 IF p_dimension_name IN ('ASSIGNMENT DEDUCTION KEY CALENDAR QUARTER TO DATE',
198 'ASSIGNMENT DEDUCTION KEY CALENDAR YEAR TO DATE',
199 'ASSIGNMENT DEDUCTION KEY PERIOD TO DATE',
200 'ASSIGNMENT PAYMENT KEY CALENDAR QUARTER TO DATE',
201 'ASSIGNMENT PAYMENT KEY CALENDAR YEAR TO DATE',
202 'ASSIGNMENT PAYMENT KEY PERIOD TO DATE',
203 'PERSON RUN' )
204 OR ( substr(p_dimension_name, 31, 4) = 'USER'
205 AND
206 substr(p_dimension_name, 40, 3) = 'ASG' ) THEN
207 RETURN 1;
208 ELSE
209 RETURN 0;
210 END IF;
211 hr_utility.trace('Exiting pay_es_bal_upload.is_supported stub');
212 END is_supported;
213 -----------------------------------------------------------------------------
214 -- NAME
215 -- include_adjustment
216 -- PURPOSE
217 -- Given a dimension, and relevant contexts and details of an existing
218 -- balance adjustment, it will find out if the balance adjustment effects
219 -- the dimension to be set. Both the dimension to be set and the adjustment
220 -- are for the same assignment and balance.
221 -- ARGUMENTS
222 -- p_balance_type_id - the balance to be set.
223 -- p_dimension_name - the balance dimension to be set.
224 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
225 -- p_bal_adjustment_rec - details of an existing balance adjustment.
226 -- p_test_batch_line_id -
227 -- USES
228 -- NOTES
229 -- This is used by pay_balance_upload.get_current_value.
230 -----------------------------------------------------------------------------
231 FUNCTION include_adjustment (p_balance_type_id NUMBER
232 ,p_dimension_name VARCHAR2
233 ,p_original_entry_id NUMBER
234 ,p_upload_date DATE
235 ,p_batch_line_id NUMBER
236 ,p_test_batch_line_id NUMBER) RETURN NUMBER IS
237 CURSOR csr_bal_adj(p_test_batch_line_id NUMBER
238 ,p_batch_line_id NUMBER) IS
239 SELECT tba.source_text,tba.source_text2,tba.balance_type_id
240 FROM pay_temp_balance_adjustments tba,
241 pay_balance_batch_lines bbl
242 WHERE tba.batch_line_id = p_test_batch_line_id
243 AND bbl.batch_line_id = p_batch_line_id;
244 l_source_text1 VARCHAR2(10);
245 l_source_text2 VARCHAR2(10);
246 l_RETURN NUMBER := 0; -- False
247 v_cur_bal_adj csr_bal_adj%ROWTYPE;
248 BEGIN
249 hr_utility.trace('Entering pay_es_bal_upload.include_adjustment stub');
250 --Select source text/source text2 of the current batch line
251 SELECT source_text ,source_text2
252 INTO l_source_text1 ,l_source_text2
253 FROM pay_balance_batch_lines
254 WHERE batch_line_id = p_batch_line_id;
255 --For context balances
256 IF (l_source_text1 IS NOT NULL) OR (l_source_text2 IS NOT NULL) THEN
257 OPEN csr_bal_adj(p_test_batch_line_id
258 ,p_batch_line_id);
259 FETCH csr_bal_adj INTO v_cur_bal_adj;
260 --Two different dimensions of the same balance and same context, hence adjustment needs to be done
261 IF v_cur_bal_adj.source_text=l_source_text1 AND v_cur_bal_adj.balance_type_id=p_balance_type_id THEN
262 l_RETURN := 1; -- True
263 ELSIF (v_cur_bal_adj.source_text2 = l_source_text2) AND (v_cur_bal_adj.balance_type_id = p_balance_type_id) THEN
264 l_RETURN := 1; -- True
265 END IF;
266 --When no other dimension of the same balance has been processed before
267 -- IF csr_bal_adj%NOTFOUND THEN
268 -- l_RETURN := 0; -- False
269 -- END IF;
270 CLOSE csr_bal_adj;
271 --For non context balances , adjustment should be done
272 ELSE
273 l_RETURN := 1;
274 END IF;
275 hr_utility.trace('Exiting pay_es_bal_upload.include_adjustment stub');
276 RETURN l_return;
277 END include_adjustment;
278 --
279 -----------------------------------------------------------------------------
280 -- NAME
281 -- get_tax_unit
282 -- PURPOSE
283 -- Returns the legal company an assignment is associated with at
284 -- particular point in time.
285 -- ARGUMENTS
286 -- p_assignment_id - the assignment
287 -- p_effective_date - the DATE on which the information is required.
288 -- USES
289 -- NOTES
290 -----------------------------------------------------------------------------
291 --
292 FUNCTION get_tax_unit (p_assignment_id NUMBER
293 ,p_effective_date DATE) RETURN NUMBER IS
294 --
295 CURSOR csr_get_wc_details IS
296 SELECT scl.segment2 work_center
297 FROM per_all_assignments_f paaf
298 ,hr_soft_coding_keyflex scl
299 WHERE paaf.assignment_id = p_assignment_id
300 AND paaf.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
301 AND p_effective_date BETWEEN effective_start_date
302 AND effective_end_date;
303 --
304 CURSOR csr_get_le_details (p_wc_organization_id NUMBER) IS
305 SELECT hoi.organization_id le_id
306 FROM hr_organization_information hoi
307 WHERE hoi.org_information1 = p_wc_organization_id
308 AND hoi.org_information_context = 'ES_WORK_CENTER_REF';
309 --
310 l_wc_id hr_all_organization_units.organization_id%TYPE;
311 l_tax_unit_id NUMBER;
312 --
313 BEGIN
314 --
315 hr_utility.trace('Entering pay_es_bal_upload.get_tax_unit');
316 --
317 l_tax_unit_id := NULL;
318 l_wc_id := NULL;
319 --
320 OPEN csr_get_wc_details;
321 FETCH csr_get_wc_details INTO l_wc_id;
322 CLOSE csr_get_wc_details;
323 --
324 IF l_wc_id IS NOT NULL THEN
325 OPEN csr_get_le_details(l_wc_id);
326 FETCH csr_get_le_details INTO l_tax_unit_id;
327 CLOSE csr_get_le_details;
328 END IF;
329 --
330 --
331 -- RETURN the tax unit.
332 --
333 RETURN (l_tax_unit_id);
334 --
335 hr_utility.trace('Exiting pay_es_bal_upload.get_tax_unit');
336 --
337 END get_tax_unit;
338 --
339 -----------------------------------------------------------------------------
340 -- NAME
341 -- validate_batch_lines
342 -- PURPOSE
343 -- Applies ES specific validation to the batch.
344 -- ARGUMENTS
345 -- p_batch_id - the batch to be validate_batch_linesd.
346 -- USES
347 -- NOTES
348 -- This is used by pay_balance_upload.validate_batch_lines.
349 -----------------------------------------------------------------------------
350 --
351 PROCEDURE validate_batch_lines(p_batch_id NUMBER) IS
352 BEGIN
353 hr_utility.trace('Entering pay_es_bal_upload.validate_batch_lines stub');
354 hr_utility.trace('Exiting pay_es_bal_upload.validate_batch_lines stub' );
355 END validate_batch_lines;
356 END pay_es_bal_upload;
[Home] [Help]