1 package body pay_kw_bal_upload as
2 -- $Header: pykwbaup.pkb 120.0 2006/04/09 23:44:25 adevanat noship $
3
4 -- Copyright (c) 1999 Oracle Corporation
5 -- All rights reserved
6
7 -- Date Author Bug/CR Num Notes
8 -- -----------+--------+----------+-----------------------------------------
9 -- 15-Feb-06 Anand MD Initial Version
10
11
12 g_package constant varchar2(240) := 'pay_kw_bal_upload.';
13
14 -- Date constants.
15 --
16 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
17 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
18
19
20 -----------------------------------------------------------------------------
21 -- NAME
22 -- expiry_date
23 -- PURPOSE
24 -- Returns the expiry date of a given dimension relative to a date.
25 -- ARGUMENTS
26 -- p_upload_date - the date on which the balance should be correct.
27 -- p_dimension_name - the dimension being set.
28 -- p_assignment_id - the assignment involved.
29 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
30 -- USES
31 -- NOTES
32 -- This is used by pay_balance_upload.dim_expiry_date.
33 -- If the expiry date cannot be derived then it is set to the end of time
34 -- to indicate that a failure has occured. The process that uses the
35 -- expiry date knows this rule and acts accordingly.
36 -----------------------------------------------------------------------------
37 function expiry_date
38 (
39 p_upload_date date,
40 p_dimension_name varchar2,
41 p_assignment_id number,
42 p_original_entry_id number
43 ) return date is
44
45 -- Returns the legislative start date
46 cursor csr_tax_start_date is
47 select rule_mode
48 from pay_legislation_rules
49 where legislation_code='KW'
50 and rule_type = 'L';
51
52
53 -- Holds the legislative start date
54 l_leg_start pay_legislation_rules.rule_mode%TYPE;
55
56 -- Returns the start date of the current period on the upload date.
57 --
58 cursor csr_period_start
59 (
60 p_assignment_id number
61 ,p_upload_date date
62 ) is
63 select nvl(PTP.start_date, END_OF_TIME)
64 from per_time_periods PTP
65 ,per_assignments_f ASS
66 where ASS.assignment_id = p_assignment_id
67 and p_upload_date between ASS.effective_start_date
68 and ASS.effective_end_date
69 and PTP.payroll_id = ASS.payroll_id
70 and p_upload_date between PTP.start_date
71 and PTP.end_date;
72
73
74 -- Returns the start date of the assignment on the upload date.
75 CURSOR csr_asg_start_date
76 (
77 p_assignment_id NUMBER,
78 p_upload_date DATE
79 ) IS
80 SELECT NVL(MIN(ass.effective_start_date), END_OF_TIME)
81 FROM per_assignments_f ass
82 WHERE ass.assignment_id = p_assignment_id
83 AND ass.payroll_id IS NOT NULL
84 AND ass.effective_start_date <= p_upload_date;
85
86
87 -- Returns the start date of the payroll
88 cursor csr_payroll_start_date (
89 p_assignment_id number,
90 p_upload_date date
91 ) is
92 select
93 nvl(ppf.effective_start_date, END_OF_TIME)
94 from
95 per_all_assignments_f ass,
99 and p_upload_date between
96 pay_all_payrolls_f ppf
97 where
98 ass.assignment_id = p_assignment_id
100 nvl(ass.effective_start_date,p_upload_date) and
101 nvl(ass.effective_end_date,p_upload_date)
102 and ppf.payroll_id = ass.payroll_id
103 and p_upload_date between
104 nvl(ppf.effective_start_date,p_upload_date) and
105 nvl(ppf.effective_end_date,p_upload_date);
106
107
108
109 l_start_date DATE;
110
111 -- Holds assignment start date
112 l_asg_start_date DATE;
113
114 -- Holds the start of the calendar year for the upload date.
115 l_cal_yr_start_date DATE;
116
117 -- Holds the start of the statutory year for the upload date.
118 l_tax_yr_start_date DATE;
119
120
121 --Holds the tax year start date for the upload process
122 l_tax_year DATE;
123
124 -- Holds the expiry date of the dimension.
125 l_expiry_date DATE;
126
127 BEGIN
128
129
130
131 -- Calculate the expiry date for the specified dimension relative to the
132 -- upload date, taking into account any contexts where appropriate. Each of
133 -- the calculations also takes into account when the assignment is on a
134 -- payroll to ensure that a balance adjustment could be made at that point
135 -- if it were required.
136 open csr_asg_start_date(p_assignment_id, p_upload_date);
137 fetch csr_asg_start_date into l_asg_start_date;
138 close csr_asg_start_date;
139
140 IF p_dimension_name in ('_ASG_PTO_YTD','_ASG_PTO_SM_YTD','_ASG_PTO_DE_YTD','_ASG_PTO_HD_YTD','_ASG_PTO_DE_SM_YTD','_ASG_PTO_DE_HD_YTD') THEN
141
142 -- What is the current tax year start date?
143 OPEN csr_tax_start_date;
144 FETCH csr_tax_start_date INTO l_leg_start;
145 CLOSE csr_tax_start_date;
146
147 -- calculate the the payroll start date
148 OPEN csr_payroll_start_date(p_assignment_id, p_upload_date);
149 FETCH csr_payroll_start_date INTO l_start_date;
150 CLOSE csr_payroll_start_date;
151
152 -- calculate the tax year start date for the upload process
153 l_tax_year := to_date(l_leg_start || to_char(p_upload_date,'YYYY'),'DD/MM/YYYY');
154
155 -- calculate the expiry date
156 l_expiry_date := greatest(l_start_date, l_asg_start_date,
157 l_tax_year );
158
159
160 END IF;
161
162
163 RETURN nvl(l_expiry_date,END_OF_TIME);
164
165 EXCEPTION
166 WHEN no_data_found THEN
167 l_expiry_date := END_OF_TIME;
168 RETURN l_expiry_date;
169
170 END expiry_date;
171 -----------------------------------------------------------------------------
172 -- NAME
173 -- is_supported
174 -- PURPOSE
175 -- Checks if the dimension is supported by the upload process.
176 -- ARGUMENTS
177 -- p_dimension_name - the balance dimension to be checked.
178 -- USES
179 -- NOTES
180 -- This is used by pay_balance_upload.validate_dimension.
181 -----------------------------------------------------------------------------
182 --
183 function is_supported
184 (
185 p_dimension_name varchar2
186 ) return number is
187 begin
188 --
189 hr_utility.trace('Entering pay_kw_bal_upload.is_supported');
190 --
191 -- See if the dimension is supported.
192 --
193 if p_dimension_name in
194 ('_ASG_PTO_YTD'
195 ,'_ASG_PTO_SM_YTD'
196 ,'_ASG_PTO_DE_YTD'
197 ,'_ASG_PTO_HD_YTD'
198 ,'_ASG_PTO_DE_SM_YTD'
199 ,'_ASG_PTO_DE_HD_YTD'
200 ) then
201 return (1); -- denotes TRUE
202 else
203 return (0); -- denotes FALSE
204 end if;
205 --
206 hr_utility.trace('Exiting pay_kw_bal_upload.is_supported');
207 --
208 end is_supported;
209 --
210
211 -----------------------------------------------------------------------------
212 -- NAME
213 -- include_adjustment
214 -- PURPOSE
215 -- Given a dimension, and relevant contexts and details of an existing
216 -- balance adjustment, it will find out if the balance adjustment effects
217 -- the dimension to be set. Both the dimension to be set and the adjustment
218 -- are for the same assignment and balance.
219 -- ARGUMENTS
220 -- p_balance_type_id - the balance to be set.
221 -- p_dimension_name - the balance dimension to be set.
222 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
223 -- p_bal_adjustment_rec - details of an existing balance adjustment.
224 -- p_test_batch_line_id -
225 -- USES
226 -- NOTES
227 -- This is used by pay_balance_upload.get_current_value.
228 -----------------------------------------------------------------------------
229 function include_adjustment
230 (
231 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
237 ) return number is
238
239
240 CURSOR csr_bal_adj(p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
241 SELECT tba.source_text,tba.balance_type_id
242 FROM pay_temp_balance_adjustments tba,
243 pay_balance_batch_lines bbl
244 WHERE tba.batch_line_id = p_test_batch_line_id
245 AND bbl.batch_line_id = p_batch_line_id;
246
247 l_source_text1 varchar2(10);
248 l_return Number := 0;--True
249 v_cur_bal_adj csr_bal_adj%rowtype;
250 begin
251 hr_utility.trace('Entering pay_kw_bal_upload.include_adjustment stub');
252
253 --Select source text of the current batch line
254 select source_text into l_source_text1 from pay_balance_batch_lines where batch_line_id=p_batch_line_id;
255
256 --For context balances
257 if l_source_text1 is not null then
258 open csr_bal_adj(p_test_batch_line_id,p_batch_line_id);
259 FETCH csr_bal_adj INTO v_cur_bal_adj;
260
261 --Two different dimensions of the same balance and same context, hence adjustment needs to be done
262 if v_cur_bal_adj.source_text=l_source_text1 and v_cur_bal_adj.balance_type_id=p_balance_type_id then
263 l_return := 1;
264 end if;
265
266 --If 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
272
273 --For non context balances , adjustment should be done
274 else
275 l_return := 1;
276 end if;
277
278
279
280
281
282 hr_utility.trace('Exiting pay_kw_bal_upload.include_adjustment stub');
283
284 return l_return;
285
286
287 end include_adjustment;
288 --
289 -----------------------------------------------------------------------------
290 -- name
291 -- validate_batch_lines
292 -- purpose
293 -- applies bf specific validation to the batch.
294 -- arguments
295 -- p_batch_id - the batch to be validate_batch_lines.
296 -- uses
297 -- notes
298 -- this is used by pay_balance_upload.validate_batch_lines.
299 -----------------------------------------------------------------------------
300
301 procedure validate_batch_lines( p_batch_id number ) is
302 begin
303
304 hr_utility.trace('Entering '||g_package||'validate_batch_lines');
305
306 hr_utility.trace('Exiting '||g_package||'validate_batch_lines');
307
308 end validate_batch_lines;
309
310 end pay_kw_bal_upload;
[Home] [Help]