[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_BAL_UPLOAD
Source
4 -- Global Variables.
1 package body pay_kr_bal_upload as
2 /* $Header: pykrupld.pkb 120.0 2005/05/29 06:32:03 appldev noship $ */
3 --
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 g_eot date := hr_api.g_eot;
8 g_sot date := hr_api.g_sot;
9 --------------------------------------------------------------------------------
10 function expiry_date(p_upload_date date,
11 p_dimension_name varchar2,
12 p_assignment_id number,
13 p_original_entry_id number) return date
14 --------------------------------------------------------------------------------
15 is
16 --
17 l_start_date date;
18 l_expiry_date date;
19 --
20 cursor csr_asg_start_date
21 (p_assignment_id number
22 ,p_upload_date date
23 ,p_expiry_date date
24 ) is
28 where ASS.assignment_id = p_assignment_id
25 select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
26 g_eot)
27 from per_assignments_f ASS
29 and ASS.effective_start_date <= p_upload_date
30 and ASS.effective_end_date >= p_expiry_date
31 and ASS.payroll_id is not null;
32 --
33 cursor csr_asg_ptd
34 is
35 select nvl(ptp.start_date,g_eot)
36 from per_time_periods ptp,
37 per_assignments_f pa
38 where pa.assignment_id = p_assignment_id
39 and p_upload_date
40 between pa.effective_start_date and pa.effective_end_date
41 and ptp.payroll_id = pa.payroll_id
42 and p_upload_date
43 between ptp.start_date and ptp.end_date;
44 --
45 cursor csr_asg_itd
46 is
47 select nvl(min(pa.effective_start_date),g_eot)
48 from per_assignments_f pa
49 where pa.assignment_id = p_assignment_id
50 and pa.effective_start_date <= p_upload_date
51 and exists (
52 select null
53 from per_time_periods PTP
54 where PTP.payroll_id = pa.payroll_id
55 and pa.effective_start_date between
56 PTP.start_date and PTP.end_date);
57 --
58 cursor csr_asg_fytd
59 is
60 select nvl(add_months(fnd_date.canonical_to_date(org_information11),
61 (floor(floor(months_between(p_upload_date,
62 fnd_date.canonical_to_date(org_information11)))/12)*12)),
63 g_eot)
64 from hr_organization_information hoi,
65 per_assignments_f pa
66 where pa.assignment_id = p_assignment_id
67 and p_upload_date
68 between pa.effective_start_date and pa.effective_end_date
69 and hoi.organization_id = pa.business_group_id
70 and hoi.org_information_context = 'Business Group Information';
71 --
72 cursor csr_asg_fqtd(p_fytd_date date,
73 p_upload_date date)
74 is
75 select nvl(add_months(p_fytd_date,
76 (floor(floor(months_between(p_upload_date,
77 p_fytd_date))/3)*3)),
78 g_eot)
79 from sys.dual;
80 --
81 cursor csr_asg_hdtd
82 is
83 select nvl(add_months(ppos.date_start,
84 (floor(floor(months_between(p_upload_date,
85 ppos.date_start))/12)*12)),
86 g_eot)
87 from per_periods_of_service ppos,
88 per_assignments_f pa
89 where pa.assignment_id = p_assignment_id
90 and p_upload_date
91 between pa.effective_start_date and pa.effective_end_date
92 and ppos.period_of_service_id = pa.period_of_service_id;
93 --
94 --
95 -- This cursor takes the assignment, the expiry_date and the upload_date
96 -- and returns the next regular_payment_date after the expiry_date for
97 -- that particular payroll.
98 --
99 CURSOR csr_regular_payment
100 (
101 l_assignment_id NUMBER,
102 l_upload_date DATE,
103 l_expiry_date DATE
104 )
105 IS
106 SELECT MIN(ptp.regular_payment_date)
107 FROM per_time_periods ptp, per_assignments_f paf
108 WHERE paf.assignment_id = l_assignment_id
109 AND l_upload_date BETWEEN paf.effective_start_date
110 AND paf.effective_end_date
111 AND ptp.payroll_id = paf.payroll_id
112 AND ptp.regular_payment_date BETWEEN l_expiry_date
113 AND l_upload_date;
114 --
115 begin
116 --
117 if p_dimension_name like '_ASG_MTD%' then
118 l_start_date := trunc(p_upload_date,'MM');
119 elsif p_dimension_name like '_ASG_YTD%' then
120 l_start_date := trunc(p_upload_date,'YYYY');
121 elsif p_dimension_name like '_ASG_QTD%' then
122 l_start_date := trunc(p_upload_date,'Q');
123 elsif p_dimension_name like '_ASG_PTD%' then
124 open csr_asg_ptd;
125 fetch csr_asg_ptd into l_start_date;
126 close csr_asg_ptd;
127 elsif (p_dimension_name like '_ASG_ITD%' or
128 p_dimension_name like '_ASG_WG_ITD%' ) then
129 open csr_asg_itd;
130 fetch csr_asg_itd into l_start_date;
131 close csr_asg_itd;
132 elsif p_dimension_name like '_ASG_FYTD%' then
133 open csr_asg_fytd;
134 fetch csr_asg_fytd into l_start_date;
135 close csr_asg_fytd;
136 elsif p_dimension_name like '_ASG_FQTD%' then
137 declare
138 l_fytd_date date;
139 begin
140 -- We need the financial year start to be able
141 -- to work out the quarter start.
142 open csr_asg_fytd;
143 fetch csr_asg_fytd into l_fytd_date;
144 close csr_asg_fytd;
145 open csr_asg_fqtd(l_fytd_date, p_upload_date);
146 fetch csr_asg_fqtd into l_start_date;
147 close csr_asg_fqtd;
148 end;
149 elsif p_dimension_name like '_ASG_HDTD%' then
150 open csr_asg_hdtd;
151 fetch csr_asg_hdtd into l_start_date;
152 close csr_asg_hdtd;
153 end if;
154 --
155 open csr_asg_start_date(p_assignment_id
156 ,p_upload_date
157 ,l_start_date);
158 fetch csr_asg_start_date into l_expiry_date;
159 close csr_asg_start_date;
160 --
164 --
161 -- For PTD's use the regular payment
162 -- date.
163 if p_dimension_name like '_ASG_PTD%' then
165 -- return the date on which the dimension expires.
166 --
167 OPEN csr_regular_payment
168 (p_assignment_id,
169 p_upload_date,
170 l_expiry_date);
171 FETCH csr_regular_payment
172 INTO l_expiry_date;
173
174 CLOSE csr_regular_payment;
175 end if;
176 --
177 return (l_expiry_date);
178 --
179 end expiry_date;
180 --------------------------------------------------------------------------------
181 function is_supported(p_dimension_name varchar2) return number
182 --------------------------------------------------------------------------------
183 is
184 --
185 l_support number := 0;
186 --
187 begin
188 --
189 if g_debug then
190 hr_utility.trace('Entering pay_kr_bal_upload.is_supported');
191 end if;
192
193 if p_dimension_name in
194 ('_ASG_MTD',
195 '_ASG_MTD_MTH',
196 '_ASG_MTD_BON',
197 '_ASG_MTD_SEP',
198 '_ASG_QTD',
199 '_ASG_YTD',
200 '_ASG_YTD_MTH',
201 '_ASG_YTD_BON',
202 '_ASG_YTD_SEP',
203 '_ASG_PTD',
204 '_ASG_PTD_MTH',
205 '_ASG_PTD_BON',
206 '_ASG_PTD_SEP',
207 '_ASG_FYTD',
208 '_ASG_FQTD',
209 '_ASG_HDTD',
210 '_ASG_ITD',
211 '_ASG_WG_ITD') then
212 l_support := 1; -- TRUE
213 else
214 l_support := 0; -- FALSE
215 end if;
216 --
217 return (l_support);
218 --
219 if g_debug then
220 hr_utility.trace('Exiting pay_kr_bal_upload.is_supported');
221 end if;
222 --
223 end is_supported;
224 --------------------------------------------------------------------------------
225 procedure validate_batch_lines(p_batch_id number)
226 --------------------------------------------------------------------------------
227 is
228 begin
229 --
230 if g_debug then
231 hr_utility.trace('Entering pay_kr_bal_upload.validate_batch_lines');
232 end if;
233 --
234 if g_debug then
235 hr_utility.trace('Exiting pay_kr_bal_upload.validate_batch_lines');
236 end if;
237 --
238 end validate_batch_lines;
239 --------------------------------------------------------------------------------
240 function include_adjustment(p_balance_type_id number,
241 p_dimension_name varchar2,
242 p_original_entry_id number,
243 p_upload_date date,
244 p_batch_line_id number,
245 p_test_batch_line_id number)
246 return number
247 --------------------------------------------------------------------------------
248 is
249 --
250 l_include_adj number := 0;
251 l_balance_type_id number;
252 l_tax_unit_id number;
253 l_run_type_id number;
254 l_bal_adj_tax_unit_id number;
255 l_bal_adj_run_type_id number;
256 l_bal_adj_original_entry_id number;
257 l_source_text varchar2(60);
258 l_bal_adj_source_text varchar2(60);
259 --
260 cursor csr_tax_unit(
261 p_batch_line_id number)
262 is
263 select pbbl.tax_unit_id,
264 pbbl.run_type_id,
265 pbbl.source_text
266 from
267 pay_balance_batch_lines pbbl
268 where pbbl.batch_line_id = p_batch_line_id;
269 --
270 cursor csr_bal_adj(
271 p_test_batch_line_id number)
272 is
273 select tax_unit_id,
274 original_entry_id,
275 run_type_id,
276 source_text
277 from pay_temp_balance_adjustments
278 where batch_line_id = p_test_batch_line_id;
279 --
280 cursor csr_is_included(
281 p_balance_type_id number,
282 p_run_type_id number,
283 p_bal_adj_run_type_id number,
284 p_source_text varchar2,
285 p_bal_adj_source_text varchar2
286 )
287 is
288 select pbt.balance_type_id
289 from pay_balance_types pbt
290 where pbt.balance_type_id = p_balance_type_id
291 and nvl(p_run_type_id, nvl(p_bal_adj_run_type_id,-1))
292 = nvl(p_bal_adj_run_type_id,-1)
293 and nvl(p_source_text, nvl(p_bal_adj_source_text,'XX'))
294 = nvl(p_bal_adj_source_text,'XX');
295 --
296 begin
297 --
298 if g_debug then
299 hr_utility.trace('Entering pay_kr_bal_upload.include_adjustment');
300 end if;
301 --
302 open csr_tax_unit(p_batch_line_id => p_batch_line_id);
303 fetch csr_tax_unit
304 into l_tax_unit_id,
305 l_run_type_id,
306 l_source_text;
307 close csr_tax_unit;
308 --
309 open csr_bal_adj(p_test_batch_line_id => p_test_batch_line_id);
310 fetch csr_bal_adj
311 into l_bal_adj_tax_unit_id,
312 l_bal_adj_original_entry_id,
313 l_bal_adj_run_type_id,
314 l_bal_adj_source_text;
315 close csr_bal_adj;
316 --
317 open csr_is_included(
318 p_balance_type_id => p_balance_type_id,
319 p_run_type_id => l_run_type_id,
320 p_bal_adj_run_type_id => l_bal_adj_run_type_id,
321 p_source_text => l_source_text,
322 p_bal_adj_source_text => l_bal_adj_source_text);
323
327 --
324 fetch csr_is_included
325 into l_balance_type_id;
326 close csr_is_included;
328 if l_balance_type_id is not null then
329 l_include_adj := 1; --TRUE
330 else
331 l_include_adj := 0; --FALSE
332 end if;
333 --
334 return (l_include_adj);
335 --
336 if g_debug then
337 hr_utility.trace('Exiting pay_kr_bal_upload.include_adjustment');
338 end if;
339 --
340 end include_adjustment;
341 end pay_kr_bal_upload;