1 PACKAGE BODY pay_fi_bal_upload AS
2 /* $Header: pyfibupl.pkb 120.0.12000000.1 2007/04/26 11:57:45 dbehera 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 procedure get_expiry_date_info
10 (p_assignment_id in number
11 ,p_upload_date in date
12 ,p_itd_start_date out nocopy date
13 )
14 is
15
16 cursor csr_itd_start_date
17 is
18 select
19 greatest(min(asg.effective_start_date)
20 ,min(ptp.start_date))
21 from
22 per_all_assignments_f asg
23 ,per_time_periods ptp
24 where asg.assignment_id = p_assignment_id
25 and ptp.payroll_id = asg.payroll_id
26 and ptp.start_date <= asg.effective_end_date;
27
28 l_itd_start_date date;
29
30 begin
31
32 open csr_itd_start_date;
33 fetch csr_itd_start_date into l_itd_start_date;
34 close csr_itd_start_date;
35
36 l_itd_start_date := nvl(l_itd_start_date, END_OF_TIME);
37
38 --
39 -- Check to see if the start date is before the upload date.
40 --
41 if l_itd_start_date <= p_upload_date then
42 p_itd_start_date := l_itd_start_date;
43 else
44 p_itd_start_date := END_OF_TIME;
45 end if;
46
47 end get_expiry_date_info;
48
49 -----------------------------------------------------------------------------
50 -- NAME
51 -- expiry_date
52 -- PURPOSE
53 -- Returns the expiry date of a given dimension relative to a date.
54 -- ARGUMENTS
55 -- p_upload_date - the date on which the balance should be correct.
56 -- p_dimension_name - the dimension being set.
57 -- p_assignment_id - the assignment involved.
58 -- p_original_entry_id - Original_entry_id context.
59 -- USES
60 -- NOTES
61 -- This is used by pay_balance_upload.dim_expiry_date.
62 -- If the expiry date cannot be derived then it is set to the end of time
63 -- to indicate that a failure has occured. The process that uses the
64 -- expiry DATE knows this rule and acts accordingly.
65 -----------------------------------------------------------------------------
66 -- -------------------------------------------------------------------------
67 -- Funtion to return expiry date for supported Routes.
68 -- -------------------------------------------------------------------------
69 FUNCTION expiry_date
70 (p_upload_date IN DATE,
71 p_dimension_name IN VARCHAR2,
72 p_assignment_id IN NUMBER,
73 p_original_entry_id IN NUMBER)
74 RETURN DATE IS
75
76 -- period start date
77 --
78 CURSOR csr_start_of_date
79 (p_assignment_id NUMBER
80 ,p_upload_date DATE
81 ) IS
82 SELECT ptp.start_date
83 FROM per_all_assignments_f ass
84 ,per_time_periods ptp
85 WHERE ass.assignment_id = p_assignment_id
86 AND ass.effective_start_date <= p_upload_date
87 AND ass.effective_end_date >= p_upload_date
88 AND ptp.payroll_id = ass.payroll_id
89 AND p_upload_date BETWEEN ptp.start_date
90 AND ptp.end_date;
91
92 l_expiry_date DATE;
93 l_business_group_id PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID%TYPE;
94 l_itd_start_date date;
95 --l_oe_start_date date;
96 l_holiday_year date;
97 BEGIN
98
99 hr_utility.trace('Entering pay_ip_bal_upload.expiry_date');
100
101 --
102 -- Get the ITD start date.
103 --
104 get_expiry_date_info
105 (p_assignment_id => p_assignment_id
106 ,p_upload_date => p_upload_date
107 ,p_itd_start_date => l_itd_start_date
108 );
109 --
110 hr_utility.trace('Asg Start Date='||l_itd_start_date);
111
112 IF p_dimension_name IN ('ASSIGNMENT HOLIDAY PERIOD TO DATE') THEN
113
114 open csr_start_of_date(p_assignment_id, p_upload_date);
115 fetch csr_start_of_date into l_expiry_date;
116 close csr_start_of_date;
117 hr_utility.trace('Period Start Date=' || l_expiry_date);
118
119 ELSIF p_dimension_name IN ('ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER INCEPTION TO DATE') THEN
120 l_expiry_date := l_itd_start_date;
121 hr_utility.trace('Asg Start Date=' || l_expiry_date);
122
123 ELSIF p_dimension_name IN ('ASSIGNMENT HOLIDAY MONTH TO DATE') THEN
124 l_expiry_date := TRUNC(p_upload_date,'MM');
125 hr_utility.trace('Mth Start Date=' || l_expiry_date);
126
127 ELSIF p_dimension_name IN ('ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER YEAR TO DATE') THEN
128 l_expiry_date := TRUNC(p_upload_date,'Y');
129 hr_utility.trace('Year Start Date=' || l_expiry_date);
130
131 ELSIF p_dimension_name IN ('ASSIGNMENT HOLIDAY YEAR TO DATE') THEN
132
133 SELECT TO_DATE('0104'||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
145 ELSE
146 --
147 -- Dimension not supported.
148 --
149 l_expiry_date := END_OF_TIME;
150 hr_utility.trace('Dimension Not Supported. ' || p_dimension_name);
151
152 END IF;
153
154 l_expiry_date := nvl(greatest(l_itd_start_date
155 ,l_expiry_date
156 ), END_OF_TIME);
157
158 if (l_expiry_date <> END_OF_TIME) and (l_expiry_date > p_upload_date) then
159 hr_utility.trace('Expiry date is later than upload_date! expiry_date='||l_expiry_date);
160 --
161 l_expiry_date := END_OF_TIME;
162 end if;
163
164 hr_utility.trace('Exiting pay_ip_bal_upload.expiry_date');
165
166 RETURN l_expiry_date;
167
168 END expiry_date;
169
170 -----------------------------------------------------------------------------
171 -- NAME
172 -- is_supported
173 -- PURPOSE
174 -- Checks if the dimension is supported by the upload process.
175 -- ARGUMENTS
176 -- p_dimension_name - the balance dimension to be checked.
177 -- USES
178 -- NOTES
179 -- Only a subset of the FI dimensions are supported.
180 -- This is used by pay_balance_upload.validate_dimension.
181 -----------------------------------------------------------------------------
182 FUNCTION is_supported(p_dimension_name VARCHAR2) RETURN NUMBER IS
183 p_dimension_name_temp VARCHAR2(100);
184 BEGIN
185 -- See if the dimension is supported.
186 IF p_dimension_name IN ('ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER INCEPTION TO DATE',
187 'ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER YEAR TO DATE',
188 'ASSIGNMENT HOLIDAY MONTH TO DATE',
189 'ASSIGNMENT HOLIDAY PERIOD TO DATE',
190 'ASSIGNMENT HOLIDAY YEAR TO DATE')
191
192 THEN
193 RETURN 1;
194 ELSE
195 RETURN 0;
196 END IF;
197 hr_utility.trace('Exiting pay_fi_bal_upload.is_supported stub');
198 END is_supported;
199 -----------------------------------------------------------------------------
200 -- NAME
201 -- include_adjustment
202 -- PURPOSE
203 -- Given a dimension, and relevant contexts and details of an existing
204 -- balance adjustment, it will find out if the balance adjustment effects
205 -- the dimension to be set. Both the dimension to be set and the adjustment
206 -- are for the same assignment and balance.
207 -- ARGUMENTS
208 -- p_balance_type_id - the balance to be set.
209 -- p_dimension_name - the balance dimension to be set.
210 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
211 -- p_test_batch_line_id -
212 -- USES
213 -- NOTES
214 -- This is used by pay_balance_upload.get_current_value.
215 -----------------------------------------------------------------------------
216 FUNCTION include_adjustment
217 (
218 p_balance_type_id NUMBER
219 ,p_dimension_name VARCHAR2
220 ,p_original_entry_id NUMBER
221 ,p_upload_date DATE
222 ,p_batch_line_id NUMBER
223 ,p_test_batch_line_id NUMBER
224 ) RETURN NUMBER IS
225
226 CURSOR csr_bal_adj_st1 (p_test_batch_line_id NUMBER, p_batch_line_id NUMBER) IS
227 SELECT tba.source_text , tba.tax_unit_id
228 FROM pay_temp_balance_adjustments tba,
229 pay_balance_batch_lines bbl
230 WHERE tba.batch_line_id = p_test_batch_line_id
231 AND bbl.batch_line_id = p_batch_line_id
232 AND nvl(tba.balance_type_id ,0) = nvl(bbl.balance_type_id ,0)
233 AND nvl(tba.source_text ,0) = nvl(bbl.source_text ,0)
234 AND nvl(tba.tax_unit_id,0) = nvl(bbl.tax_unit_id,0)
235 AND tba.tax_unit_id IS NOT NULL
236 AND tba.source_text IS NOT NULL;
237
238 l_include_adj BOOLEAN := TRUE ;
239 v_cur_bal_adj_st1 csr_bal_adj_st1%ROWTYPE;
240
241 BEGIN
242 hr_utility.trace('Entering pay_fi_bal_upload.include_adjustment stub');
243
244 IF p_dimension_name IN ('ASSIGNMENT HOLIDAY PERIOD TO DATE',
245 'ASSIGNMENT HOLIDAY MONTH TO DATE',
246 'ASSIGNMENT HOLIDAY YEAR TO DATE') THEN
247
248 l_include_adj := TRUE;
249
250 ELSIF p_dimension_name IN ('ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER INCEPTION TO DATE','ASSIGNMENT EMPLOYMENT TYPE LEGAL EMPLOYER YEAR TO DATE') THEN
251
252 OPEN csr_bal_adj_st1(p_test_batch_line_id => p_test_batch_line_id,
253 p_batch_line_id => p_batch_line_id);
254
255 FETCH csr_bal_adj_st1 INTO v_cur_bal_adj_st1;
256
257 IF csr_bal_adj_st1%NOTFOUND THEN
258 l_include_adj := FALSE ;
259 END IF;
260
261 CLOSE csr_bal_adj_st1;
262
263 ELSE
264
265 NULL;
266
267 END IF;
268
269 hr_utility.trace('Exiting pay_fi_bal_upload.include_adjustment');
270
271 if l_include_adj then
272 RETURN 1;
273 else
274 RETURN 0;
275 end if;
276
277 END include_adjustment;
278 -----------------------------------------------------------------------------
279 -- NAME
280 -- validate_batch_lines
281 -- PURPOSE
282 -- Applies FI specific validation to the batch.
283 -- ARGUMENTS
284 -- p_batch_id - the batch to be validate_batch_linesd.
285 -- USES
286 -- NOTES
287 -- This is used by pay_balance_upload.validate_batch_lines.
288 -----------------------------------------------------------------------------
289 --
290 PROCEDURE validate_batch_lines(p_batch_id NUMBER) IS
291 BEGIN
292 hr_utility.trace('Entering pay_fi_bal_upload.validate_batch_lines stub');
293 hr_utility.trace('Exiting pay_fi_bal_upload.validate_batch_lines stub' );
294 END validate_batch_lines;
295
296 END pay_fi_bal_upload;
[Home] [Help]