1 PACKAGE BODY PAY_DK_BAL_UPLOAD AS
2 /* $Header: pydkbalupl.pkb 120.1 2007/03/13 07:12:31 saurai noship $ */
3
4 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
5 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
6
7
8 procedure get_expiry_date_info
9 (p_assignment_id in number
10 ,p_upload_date in date
11 ,p_itd_start_date out nocopy date
12 )
13 is
14
15 cursor csr_itd_start_date
16 is
17 select
18 greatest(min(asg.effective_start_date)
19 ,min(ptp.start_date))
20 from
21 per_all_assignments_f asg
22 ,per_time_periods ptp
23 where asg.assignment_id = p_assignment_id
24 and ptp.payroll_id = asg.payroll_id
25 and ptp.start_date <= asg.effective_end_date;
26
27 l_itd_start_date date;
28
29 begin
30
31 open csr_itd_start_date;
32 fetch csr_itd_start_date into l_itd_start_date;
33 close csr_itd_start_date;
34
35 l_itd_start_date := nvl(l_itd_start_date, END_OF_TIME);
36
37 --
38 -- Check to see if the start date is before the upload date.
39 --
40 if l_itd_start_date <= p_upload_date then
41 p_itd_start_date := l_itd_start_date;
42 else
43 p_itd_start_date := END_OF_TIME;
44 end if;
45
46 end get_expiry_date_info;
47
48 -----------------------------------------------------------------------------
49 -- NAME
50 -- expiry_date
51 -- PURPOSE
52 -- Returns the expiry date of a given dimension relative to a date.
53 -- ARGUMENTS
54 -- p_upload_date - the date on which the balance should be correct.
55 -- p_dimension_name - the dimension being set.
56 -- p_assignment_id - the assignment involved.
57 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
58 -- USES
59 -- NOTES
60 -- This is used by pay_balance_upload.dim_expiry_date.
61 -- If the expiry date cannot be derived then it is set to the end of time
62 -- to indicate that a failure has occured. The process that uses the
63 -- expiry date knows this rule and acts accordingly.
64 -----------------------------------------------------------------------------
65
66 function expiry_date
67 (
68 p_upload_date date,
69 p_dimension_name varchar2,
70 p_assignment_id number,
71 p_original_entry_id number
72 ) return date is
73
74 -- period start date
75 --
76 CURSOR csr_start_of_date
77 (p_assignment_id NUMBER
78 ,p_upload_date DATE
79 ) IS
80 SELECT ptp.start_date
81 FROM per_all_assignments_f ass
82 ,per_time_periods ptp
83 WHERE ass.assignment_id = p_assignment_id
84 AND ass.effective_start_date <= p_upload_date
85 AND ass.effective_end_date >= p_upload_date
86 AND ptp.payroll_id = ass.payroll_id
87 AND p_upload_date BETWEEN ptp.start_date
88 AND ptp.end_date;
89
90 l_itd_start_date date;
91 l_oe_start_date date;
92 l_holiday_year date;
93 l_expiry_date DATE;
94
95
96 begin
97
98 --fnd_file.put_line(fnd_file.log,'Entered PROCEDURE expiry_date -->'||p_dimension_name);
99
100 --
101 -- Get the ITD start date.
102 --
103 get_expiry_date_info
104 (p_assignment_id => p_assignment_id
105 ,p_upload_date => p_upload_date
106 ,p_itd_start_date => l_itd_start_date
107 );
108
109 --
110 hr_utility.trace('Asg Start Date='||l_itd_start_date);
111 --
112
113 if(p_dimension_name = 'ASSIGNMENT CALENDAR HALF YEAR TO DATE'
114 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
115 -- or p_dimension_name = 'PERSON WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
116 )
117 then
118 --fnd_file.put_line(fnd_file.log,' Validate upload date -->'||to_char(p_upload_date, 'MM'));
119 if to_char(p_upload_date, 'MM') in ('01','02','03','04','05','06')
120 then
121 --fnd_file.put_line(fnd_file.log,' Return date -->'||to_date('01/01/'||to_char(p_upload_date,'yyyy'),'DD/MM/YYYY'));
122 l_expiry_date := to_date('01/01/'||to_char(p_upload_date,'yyyy'),'DD/MM/YYYY');
123 else
124 --fnd_file.put_line(fnd_file.log,' Return date -->'||to_date('01/07/'||to_char(p_upload_date,'yyyy'),'DD/MM/YYYY'));
125 l_expiry_date := to_date('01/07/'||to_char(p_upload_date,'yyyy'),'DD/MM/YYYY');
126 end if;
127 elsif(p_dimension_name = 'ASSIGNMENT HOLIDAY YEAR TO DATE'
128 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR TO DATE'
129 )
130 then
131 --fnd_file.put_line(fnd_file.log,' Validate upload date -->'||to_char(p_upload_date, 'MM'));
132
133 SELECT TO_DATE('0105'||TO_CHAR(p_upload_date,'YYYY'),'DD/MM/YYYY')
134 INTO l_holiday_year
135 FROM DUAL;
136
137 IF p_upload_date >= l_holiday_year THEN
138 l_expiry_date := l_holiday_year;
139 ELSE
140 l_expiry_date := ADD_MONTHS(l_holiday_year , -12);
141 END IF;
142
143 hr_utility.trace('HY Start Date=' || l_expiry_date);
144 --fnd_file.put_line(fnd_file.log,' l_expiry_date -->'||l_expiry_date);
145 else
146 return END_OF_TIME;
147 end if;
148
149 l_expiry_date := nvl(greatest(l_itd_start_date
150 ,l_expiry_date
151 ,nvl(l_oe_start_date, l_expiry_date)
152 ), END_OF_TIME);
153
154 if (l_expiry_date <> END_OF_TIME) and (l_expiry_date > p_upload_date) then
155 hr_utility.trace('Expiry date is later than upload_date! expiry_date='||l_expiry_date);
156 --
157 l_expiry_date := END_OF_TIME;
158 end if;
159
160 --fnd_file.put_line(fnd_file.log,' l_expiry_date -->'||l_expiry_date);
161 hr_utility.trace('Final Expiry Date=' || l_expiry_date);
162
163 RETURN l_expiry_date;
164
165 end expiry_date;
166
167
168 -----------------------------------------------------------------------------
169 -- NAME
170 -- is_supported
171 -- PURPOSE
172 -- Checks if the dimension is supported by the upload process.
173 -- ARGUMENTS
174 -- p_dimension_name - the balance dimension to be checked.
175 -- USES
176 -- NOTES
177 -- Only a subset of the DK dimensions are supported.
178 -- This is used by pay_balance_upload.validate_dimension.
179 -----------------------------------------------------------------------------
180
181 function is_supported
182 (
183 p_dimension_name varchar2
184 ) return number is
185 begin
186 -- hr_utility.trace('Entering pay_kd_bal_upload.is_supported');
187 --fnd_file.put_line(fnd_file.log,' Entered PROCEDURE IS_SUPPORTED'||p_dimension_name);
188 if (p_dimension_name in
189 (
190 --'_ASG_LE_HYTD',
191 -- '_PER_LE_HYTD',
192 '_ASG_HYTD' ,
193 '_ASG_HOLIDAY_YTD'
194 --'_ASG_LE_HOLIDAY_YTD'
195 )
196 or
197 (
198 substr(p_dimension_name, 31, 4) = 'USER'
199 and
200 substr(p_dimension_name, 40, 3) = 'ASG'
201 ))
202 or
203 (p_dimension_name = 'ASSIGNMENT CALENDAR HALF YEAR TO DATE'
204 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
205 -- or p_dimension_name = 'PERSON WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
206 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR TO DATE'
207 or p_dimension_name = 'ASSIGNMENT HOLIDAY YEAR TO DATE'
208 )
209 then
210 --fnd_file.put_line(fnd_file.log,' condition is true');
211 return 1;
212 else
213 --fnd_file.put_line(fnd_file.log,' condition is false');
214 return 0;
215 end if;
216 --fnd_file.put_line(fnd_file.log,' Exiting pay_dk_bal_upload.is_supported');
217 end is_supported;
218
219
220 -----------------------------------------------------------------------------
221 -- NAME
222 -- include_adjustment
223 -- PURPOSE
224 -- Given a dimension, and relevant contexts and details of an existing
225 -- balance adjustment, it will find out if the balance adjustment effects
226 -- the dimension to be set. Both the dimension to be set and the adjustment
227 -- are for the same assignment and balance.
228 -- ARGUMENTS
229 -- p_balance_type_id - the balance to be set.
230 -- p_dimension_name - the balance dimension to be set.
231 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
232 -- p_bal_adjustment_rec - details of an existing balance adjustment.
233 -- p_test_batch_line_id -
234 -- USES
235 -- NOTES
236 -- This is used by pay_balance_upload.get_current_value.
237 -----------------------------------------------------------------------------
238
239 function include_adjustment
240 (
241 p_balance_type_id number,
242 p_dimension_name varchar2,
243 p_original_entry_id number,
244 p_upload_date date,
245 p_batch_line_id number,
246 p_test_batch_line_id number
247 ) return number is
248 l_source_text varchar2(10);
249 l_return number := 0;--TRUE;--True
250 l_original_entry_id Number;
251 l_include_adj BOOLEAN := TRUE ;
252 Begin
253 --fnd_file.put_line(fnd_file.log,' Entering pay_dk_bal_upload.include_adjustment');
254 if (p_dimension_name = 'ASSIGNMENT CALENDAR HALF YEAR TO DATE'
255 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
256 -- or p_dimension_name = 'PERSON WITHIN LEGAL EMPLOYER CALENDAR HALF YEAR TO DATE'
257 -- or p_dimension_name = 'ASSIGNMENT WITHIN LEGAL EMPLOYER HOLIDAY YEAR TO DATE'
258 or p_dimension_name = 'ASSIGNMENT HOLIDAY YEAR TO DATE'
259 )
260 then l_include_adj := TRUE;
261 else
262
263 NULL;
264 end if;
265
266 --fnd_file.put_line(fnd_file.log,' Exiting pay_dk_bal_upload.include_adjustment l_return:'||l_return);
267
268 if l_include_adj then
269 l_return := 1;
270 else
271 l_return := 0;
272 end if;
273
274 Return l_return;
275 end include_adjustment;
276
277 -----------------------------------------------------------------------------
278 -- NAME
279 -- validate_batch_lines
280 -- PURPOSE
281 -- Applies DK specific validation to the batch.
282 -- ARGUMENTS
283 -- p_batch_id - the batch to be validate_batch_linesd.
284 -- USES
285 -- NOTES
286 -- This is used by pay_balance_upload.validate_batch_lines.
287 -----------------------------------------------------------------------------
288 --
289 PROCEDURE validate_batch_lines(p_batch_id NUMBER) IS
290 BEGIN
291 hr_utility.trace('Entering pay_fi_bal_upload.validate_batch_lines stub');
292 hr_utility.trace('Exiting pay_fi_bal_upload.validate_batch_lines stub' );
293 END validate_batch_lines;
294
295
296 END PAY_DK_BAL_UPLOAD;
297
[Home] [Help]