1 package body pay_jp_bal_upload as
2 /* $Header: pyjpupld.pkb 120.1 2006/04/24 00:54:12 ttagawa noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1995 All rights reserved
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyjpupld.pkb
9 DESCRIPTION
10 Provides support for the upload of balances based on UK dimensions.
11 EXTERNAL
12 expiry_date
13 include_adjustment
14 is_supported
15 validate_batch_lines
16 INTERNAL
17 MODIFIED (DD-MON-YYYY)
18 40.0 J.S.Hobbs 16-May-1995 created.
19 40.2 A.Snell 03-Oct-1995 added director logic
20 40.3 N.Bristow 06-Oct-1995 ITD dimensions not supported
21 for balance upload.
22 40.5 N.Bristow 17-Oct-1995 Changes to support ITD balances.
23 40.6 N.Bristow 19-Oct-1995 Uncomment exit.
24 40.7 A.Snell 28-Feb-1996 Bug 345309 mid year starters
25 40.8 J.Alloun 30-JUL-1996 Added error handling.
26 40.9 C.Barbieri 13-AUG-1996 Added ASG_TD_ITD dimension.
27 40.10 C.Barbieri 28-Oct-1996 Changed User Balance Name
28 Convenction.
29 40.10 C.Barbieri 28-Oct-1996 Changed User Balance naming.
30 40.11 R.Kamiyama 27-Jan-1997 Modified for JP dimensions.
31 40.11 R.Kamiyama 05-Jan-1997 Added select stmt to get
32 business_group_id to for JP functions.
33 40.11 R.Kamiyama 07-Mar-1997 Changed to BONUS_YEAR_STARTS from
34 JP_BONUS_YEAR_STARTS
35 40.12 R.Kamiyama 13-Mar-1998 Translated to JP dim name.
36 40.17 Y.Negoro 11-Nov-1998 Fix 665503.
37 110.01 Y.Negoro 13-Nov-1998 Create for R11
38 115.1 Y.Negoro 03-JUN-1999 Create for R11i
39 115.2 K.Yazawa 17-JUN-1999 Change the package name.
40 (hr_jpbal => hr_jprts)
41 115.4 Y.Tohya 06-Oct-1999 Fix 1020589.
42 115.5 K.Yazawa 08-Oct-1999 Remove Multi byte Character.
43 per_assignments_f => per_all_assignments_f
44 115.6 T.Tagawa 25-Sep-2002 2597843 ASG_ITD support.
45 Added code to avoid HR_6614_PAY_NO_TIME_PERIOD.
46 115.7 T.Tagawa 18-OCT-2002 2597843 IS_SUPPORTED function simplified(UTF8 support).
47 115.8 T.Tagawa 06-NOV-2002 2597843 Added code to avoid error if the payroll on actual
48 upload date is different from that on batch upload date.
49 115.9 M.Iwamoto 19-JAN-2003 2708491 Total Reward System Support.
50 115.10 T.Tagawa 15-MAY-2003 Added ASG_JULTD and new ASG_FYTD to is_supported.
51 Added new ASG_FYTD expiry date routine.
52 115.11 T.Tagawa 21-MAY-2003 show err commented out.
53 115.12 T.Tagawa 21-APR-2006 2656208 Re-built. All potential bugs fixed.
54 */
55 --
56 -- Constants
57 --
58 c_package constant varchar2(31) := 'pay_jp_bal_upload.';
59 START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
60 END_OF_TIME constant date := to_date('31/12/4712','DD/MM/YYYY');
61 --
62 -----------------------------------------------------------------------------
63 -- NAME
64 -- expiry_date
65 -- PURPOSE
66 -- Returns the expiry date of a given dimension relative to a date.
67 -- ARGUMENTS
68 -- p_upload_date - the date on which the balance should be correct.
69 -- p_dimension_name - the dimension being set.
70 -- p_assignment_id - the assignment involved.
71 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
72 -- USES
73 -- NOTES
74 -- This is used by pay_balance_upload.dim_expiry_date.
75 -- If the expiry date cannot be derived then it is set to the end of time
76 -- to indicate that a failure has occured. The process that uses the
77 -- expiry date knows this rulw and acts accordingly.
78 -----------------------------------------------------------------------------
79 --
80 function expiry_date(
81 p_upload_date in date,
82 p_dimension_name in varchar2,
83 p_assignment_id in number,
84 p_original_entry_id in number) return date
85 is
86 c_proc constant varchar2(61) := c_package || 'expiry_date';
87 --
88 l_business_group_id number;
89 l_payroll_id number;
90 l_ptp_start_date date;
91 l_asg_start_date date;
92 l_ee_start_date date;
93 l_legislation_code pay_balance_dimensions.legislation_code%type;
94 l_period_type pay_balance_dimensions.period_type%type;
95 l_start_date_code pay_balance_dimensions.start_date_code%type;
96 l_dim_start_date date;
97 l_expiry_date date;
98 begin
99 hr_utility.set_location('Entering: ' || c_proc, 10);
100 hr_utility.trace('dimension_name: ' || p_dimension_name);
101 hr_utility.trace('upload_date : ' || p_upload_date);
102 --
103 select business_group_id,
104 payroll_id
105 into l_business_group_id,
106 l_payroll_id
107 from per_all_assignments_f
108 where assignment_id = p_assignment_id
109 and p_upload_date
110 between effective_start_date and effective_end_date;
111 --
112 -- Calculate the expiry date for the specified dimension relative to the
113 -- upload date, taking into account any contexts where appropriate. Each of
114 -- the calculations also takes into account when the assignment is on a
115 -- payroll to ensure that a balance adjustment could be made at that point
116 -- if it were required.
117 --
118 -- Returns 1st Period Start Date.
119 -- Also check whether payroll period exists as of p_upload_date.
120 --
121 hr_utility.set_location(c_proc, 20);
122 --
123 select nvl(min(ptp2.start_date), END_OF_TIME)
124 into l_ptp_start_date
125 from per_time_periods ptp,
126 per_time_periods ptp2
127 where ptp.payroll_id = l_payroll_id
128 and p_upload_date
129 between ptp.start_date and ptp.end_date
130 and ptp2.payroll_id = ptp.payroll_id
131 and ptp2.start_date <= p_upload_date;
132 --
133 -- Returns the date on which the assignment transferred payroll prior to
134 -- the upload date NB. the payroll is the one the assignment is assigned to
135 -- on the upload date.
136 --
137 hr_utility.set_location(c_proc, 30);
138 --
139 select max(asg.effective_end_date) + 1
140 into l_asg_start_date
141 from per_all_assignments_f asg
142 where asg.assignment_id = p_assignment_id
143 and asg.effective_end_date < p_upload_date
144 and nvl(asg.payroll_id, -1) <> l_payroll_id;
145 --
146 if l_asg_start_date is null then
147 hr_utility.set_location(c_proc, 35);
148 --
149 select min(asg.effective_start_date)
150 into l_asg_start_date
151 from per_all_assignments_f asg
152 where asg.assignment_id = p_assignment_id;
153 end if;
154 --
155 -- In case of element level dimension
156 --
157 if p_original_entry_id is not null then
158 hr_utility.set_location(c_proc, 41);
159 --
160 select nvl(min(ee.effective_start_date), END_OF_TIME)
161 into l_ee_start_date
162 from pay_element_entries_f ee
163 where ( ee.element_entry_id = p_original_entry_id
164 or ee.original_entry_id = p_original_entry_id)
165 and ee.assignment_id = p_assignment_id
166 and ee.entry_type = 'E'
167 and ee.effective_start_date <= p_upload_date;
168 else
169 hr_utility.set_location(c_proc, 42);
170 --
171 l_ee_start_date := START_OF_TIME;
172 end if;
173 --
174 -- Returns the start date of balance dimension.
175 --
176 l_legislation_code := hr_api.return_legislation_code(l_business_group_id);
177 --
178 hr_utility.set_location(c_proc, 50);
179 --
180 select period_type,
181 start_date_code
182 into l_period_type,
183 l_start_date_code
184 from pay_balance_dimensions
185 where dimension_name = p_dimension_name
186 and nvl(business_group_id, l_business_group_id) = l_business_group_id
187 and nvl(legislation_code, l_legislation_code) = l_legislation_code;
188 --
189 hr_utility.set_location(c_proc, 51);
190 --
191 pay_balance_pkg.get_period_type_start(
192 P_PERIOD_TYPE => l_period_type,
193 P_EFFECTIVE_DATE => p_upload_date,
194 P_START_DATE => l_dim_start_date,
195 P_START_DATE_CODE => l_start_date_code,
196 P_PAYROLL_ID => l_payroll_id,
197 P_BUS_GRP => l_business_group_id);
198 --
199 hr_utility.set_location(c_proc, 60);
200 --
201 l_expiry_date := greatest(l_ptp_start_date, l_asg_start_date, l_ee_start_date, l_dim_start_date);
202 --
203 hr_utility.trace('PTP_START_DATE: ' || l_ptp_start_date);
204 hr_utility.trace('ASG_START_DATE: ' || l_asg_start_date);
205 hr_utility.trace('EE_START_DATE : ' || l_ee_start_date);
206 hr_utility.trace('DIM_START_DATE: ' || l_dim_start_date);
207 hr_utility.trace('EXPIRY_DATE : ' || l_expiry_date);
208 --
209 hr_utility.set_location('Leaving: ' || c_proc, 100);
210 return (l_expiry_date);
211 end expiry_date;
212 --
213 -----------------------------------------------------------------------------
214 -- NAME
215 -- is_supported
216 -- PURPOSE
217 -- Checks if the dimension is supported by the upload process.
218 -- ARGUMENTS
219 -- p_dimension_name - the balance dimension to be checked.
220 -- USES
221 -- NOTES
222 -- Only a subset of the UK dimensions are supported and these have been
223 -- picked to allow effective migration to release 10.
224 -- This is used by pay_balance_upload.validate_dimension.
225 -----------------------------------------------------------------------------
226 --
227 function is_supported
228 (
229 p_dimension_name in varchar2
230 ) return boolean
231 is
232 c_proc constant varchar2(61) := c_package || 'is_supported';
233 l_is_supported boolean;
234 l_description pay_balance_dimensions.description%type;
235 l_dimension_level pay_balance_dimensions.dimension_level%type;
236 l_period_type pay_balance_dimensions.period_type%type;
237 begin
238 hr_utility.set_location('Entering: ' || c_proc, 10);
239 --
240 -- This SQL can possiblly raise TOO_MANY_ROWS exception
241 -- when the same dimension name exists over multiple business groups.
242 -- Current temporary workaround is to use distinct.
243 -- If user defines the same dimension_name for dimensions with different parameters
244 -- over multiple business groups, following SQL still raises TOO_MANY_ROWS error.
245 -- In this case, this function will return "FALSE", which means this dimension is
246 -- not supported for Balance Initialization.
247 --
248 select distinct
249 description,
250 dimension_level,
251 period_type
252 into l_description,
253 l_dimension_level,
254 l_period_type
255 from pay_balance_dimensions dim,
256 per_business_groups_perf bg
257 where dim.dimension_name = p_dimension_name
258 and bg.business_group_id(+) = dim.business_group_id
259 and nvl(dim.legislation_code, bg.legislation_code) = 'JP';
260 --
261 -- See if the dimension is supported.
262 -- DATE_EARNED based dimensions cannot be supported because of PURGE process.
263 --
264 if l_dimension_level = 'ASG'
265 and nvl(l_period_type, 'RUN') not in ('RUN', 'PAYMENT')
266 and nvl(pay_core_utils.get_parameter('DATE_TYPE', l_description), 'DP') <> 'DE' then
267 l_is_supported := true;
268 else
269 l_is_supported := false;
270 end if;
271 --
272 hr_utility.set_location('Leaving: ' || c_proc, 100);
273 return (l_is_supported);
274 exception
275 when no_data_found then
276 return false;
277 when too_many_rows then
278 return false;
279 end is_supported;
280 --
281 -----------------------------------------------------------------------------
282 -- NAME
283 -- include_adjustment
284 -- PURPOSE
285 -- Given a dimension, and relevant contexts and details of an existing
286 -- balanmce adjustment, it will find out if the balance adjustment effects
287 -- the dimension to be set. Both the dimension to be set and the adjustment
288 -- are for the same assignment and balance.
289 -- ARGUMENTS
290 -- p_balance_type_id - the balance to be set.
291 -- p_dimension_name - the balance dimension to be set.
292 -- p_original_entry_id - ORIGINAL_ENTRY_ID context.
293 -- p_bal_adjustment_rec - details of an existing balance adjustment.
294 -- USES
295 -- NOTES
296 -- This is used by pay_balance_upload.get_current_value.
297 -----------------------------------------------------------------------------
298 --
299 function include_adjustment
300 (
301 p_balance_type_id in number
302 ,p_dimension_name in varchar2
303 ,p_original_entry_id in number
304 ,p_bal_adjustment_rec in pay_balance_upload.csr_balance_adjustment%rowtype -- pay_temp_balance_adjustments
305 ) return boolean
306 is
307 c_proc constant varchar2(61) := c_package || 'include_adjustment';
308 ret_val boolean;
309 begin
310 hr_utility.set_location('Entering: ' || c_proc, 10);
311 --
312 if (p_original_entry_id = p_bal_adjustment_rec.original_entry_id)
313 or (p_original_entry_id is null) then
314 -- or (p_original_entry_id is null
315 -- and p_bal_adjustment_rec.original_entry_id is null) then
316 ret_val := TRUE;
317 else
318 ret_val := FALSE;
319 end if;
320 --
321 hr_utility.set_location('Leaving: ' || c_proc, 100);
322 return (ret_val);
323 end include_adjustment;
324 --
325 -----------------------------------------------------------------------------
326 -- NAME
327 -- validate_batch_lines
328 -- PURPOSE
329 -- Applies UK specific validation to the batch.
330 -- ARGUMENTS
331 -- p_batch_id - the batch to be validate_batch_linesd.
332 -- USES
333 -- NOTES
334 -- This is used by pay_balance_upload.validate_batch_lines.
335 -----------------------------------------------------------------------------
336 --
337 procedure validate_batch_lines
338 (
339 p_batch_id in number
340 )
341 is
342 c_proc constant varchar2(61) := c_package || 'validate_batch_lines';
343 begin
344 hr_utility.set_location('Entering: ' || c_proc, 10);
345 --
346 hr_utility.set_location('Leaving: ' || c_proc, 100);
347 end validate_batch_lines;
348 --
349 end pay_jp_bal_upload;
[Home] [Help]