1 package body pay_hk_ir56
2 /* $Header: pyhkir56.pkb 120.3.12010000.6 2009/04/16 14:18:46 avenkatk ship $
3 **
4 ** Copyright (C) 2001 Oracle Corporation
5 ** All Rights Reserved
6 **
7 ** Change List
8 **
9 ** Date Author Bug Ver Description
10 ** ===============================================================================
11 ** 06-MAR-2001 sclarke N/A 115.0 Created
12 ** 14-MAR-2001 sclarke 115.1 fix start date returned when less than 01-apr
13 ** 16-MAR-2001 sclarke 115.2 added new function for tax year start, removed
17 ** modified p_periods to return actual_termination_date
14 ** return of zero values for emolument amounts.
15 ** 22-Jul-2002 srrajago 2461715 115.3 Included the column 'actual_termination_date'
16 ** in the cursor 'csr_balance'and
18 ** instead of period_end_date.
19 ** 12-Sep-2002 nsinghal 2563375 115.5 Create the New Cursor csr_Hire_date to get Start
20 ** date oF employmnet,If the period start date is
21 ** prior to employee's hire date.
22 ** 15-Sep-2002 nsinghal 2563375 115.6 Create the New Cursor csr_Hire_date to get Start
23 ** date oF employmnet,If the period start date is
24 ** prior to employee's hire date,function get_emoluments
25 ** will return hire_date.
26 ** 18-Sep-2002 nsinghal 2563375 115.7 Assign the Greatest of maximum of period start and
27 ** financial start dateto Start date ,before Comparing
28 ** with hire date to Check whether Hire Date is Greater
29 ** then Start date or not.
30 ** 22-Nov-2002 nanuradh 2678084 115.8 Period end date is taken if the termination date is null.
31 ** 02-Dec-2002 puchil 2689191 115.9 Changed the select statement for Cursor 'csr_Hire_date'.
32 ** 02-Dec-2002 srrajago 2689229 115.10 Included 'nocopy' option in all the 'out' parameters of the
33 ** function get_emoluments.
34 ** 14-Mar-2003 srrajago 2850738 115.11 Included the join paa.period_of_service_id = pps.period_of_service_id
35 ** in the cursor csr_hire_date so as to pick up the correct hire date
36 ** incase of rehire.
37 ** 30-May-2003 kaverma 2920731 115.12 Replaced tables per_all_assignments_f and per_all_people_f by secured views
38 ** per_assignments_f and per_people_f respectively form the queries
39 ** 24-Jul-2003 srrajago 3062419 115.13 Added two variables l_fin_start_date and l_fin_end_date for storing the
40 ** financial year start and end date respectively. p_periods value ( end_date
41 ** value only) modified so that it returns different values for IR56B and
42 ** IR56F and IR56G.
43 ** 12-Dec-2003 srrajago 3193217 115.14 Modified the entire logic in the function 'get_emoluments'. Introduced a new
44 ** procedure 'populate_defined_balance_ids'.
45 ** 12-Dec-2003 srrajago 3193217 115.15 Function 'get_emoluments' modified. Check for assignment_action_id passed being 0 or NULL
46 ** has been included.
47 ** 17-Dec-2003 srrajago 3193217 115.16 In the function 'get_emoluments' -> IF check -> Replaced '!=' with '<>' to remove GSCC error.
48 ** 09-Feb-2003 avenkatk 3417275 115.17 In the procedure 'populate_defined_balance_ids',removed references to the 4 IR56_Q quarter balances.
49 ** 14-JUN-2004 abhkumar 3626489 115.18 Removed gscc warnings.
50 ** 15-JUN-2004 abhkumar 3626489 115.19 Added hr_utility.debug_enabled to each of three functions.
51 ** 15-JUN-2004 abhkumar 3626489 115.20 Commented hr_utility.debug_enabled and initialised g_debug to FALSE.
52 ** 31-JAN-2005 JLin 3609072 115.21 Modified to be able to run the balance retrieval batch mode.
53 ** 14-Dec-2005 snimmala 4864213 115.22 Added a new function get_quarters_start_date and is used in the view
54 ** pay_hk_ir56_quarters_info_v.
55 ** 09-Jan-2005 vborhade 4688776 115.23 Modified procedure get_emoluments for period end date.
56 ** 27-Sep-2007 skshin 6432592 115.24 Modified function get_emoluments to display indirect result for IR56_B
57 ** 20-Mar-2009 pmatamsr 8348781 115.25 Added condition in 'get_emoluments' function to fetch null into period dates when IR56 balance
58 ** contains a zero value.
59 ** 03-Apr-2009 pmatamsr 8406450 115.26 Removed code fix done as part of bug 4688776 for non-recurring processing type in 'get_emoluments' function.
60 ** 04-Apr-2009 pmatamsr 8406450 115.27 Modified the code fix comments.
61 ** 16-Apr-2009 avenkatk 8406450 115.28 Added check for Balance Adjustments. If any IR56 is Balance adjusted, the periods dates are fetched
62 ** like Non recurring entries.
63 ** 16-Apr-2009 avenkatk 8406450 115.29 Resolved gscc failure
64 **
65 **
66 */
67 as
68
69 g_debug boolean;
70 /* Bug # 3609072 */
71 p_balance_value_tab pay_balance_pkg.t_balance_value_tab;
72 p_context_table pay_balance_pkg.t_context_tab;
73 p_result_table pay_balance_pkg.t_detailed_bal_out_tab;
74
75
76 FUNCTION get_emoluments
77 ( p_assignment_id in per_assignments_f.assignment_id%TYPE
78 , p_assignment_action_id in pay_assignment_actions.assignment_action_id%TYPE
79 , p_tax_unit_id in pay_assignment_actions.tax_unit_id%TYPE
80 , p_reporting_year in number) RETURN g_emol_details_tab IS
81
82 l_start_date DATE;
83 l_end_date DATE;
84 l_fin_start_date DATE;
85 l_fin_end_date DATE;
86 l_processing_type pay_element_types_f.processing_type%TYPE;
87 l_particulars hr_lookups.description%TYPE;
88 l_hire_date per_periods_of_service.date_start%TYPE;
89 l_actual_termination_date per_periods_of_service.actual_termination_date%TYPE;
90 i NUMBER := 1;
91
92 l_exists NUMBER := 0;
93
94 CURSOR csr_element_entry_dates
95 (p_balance_name pay_balance_types.balance_name%TYPE,
99 SELECT min(pee.effective_start_date),
96 p_fin_start_date date,
97 p_fin_end_date date)
98 IS
100 max(pee.effective_end_date),
101 min(pet.processing_type)
102 FROM pay_element_types_f pet,
103 pay_element_entries_f pee,
104 pay_element_links_f pel,
105 pay_balance_types pbt,
106 pay_balance_feeds_f pbf,
107 pay_input_values_f piv
108 WHERE pee.assignment_id = p_assignment_id
109 AND pee.element_link_id = pel.element_link_id
110 AND pbf.balance_type_id = pbt.balance_type_id
111 AND pbf.input_value_id = piv.input_value_id
112 AND piv.element_type_id = pel.element_type_id
113 AND pel.element_type_id = pet.element_type_id
114 AND pbt.balance_name = p_balance_name
115 AND ((pbf.legislation_code = 'HK' and pbf.business_group_id IS NULL) OR
116 (pbf.business_group_id = piv.business_group_id AND pbf.legislation_code IS NULL))
117 AND pee.effective_start_date <= p_fin_end_date
118 AND pee.effective_end_date >= p_fin_start_date;
119
120 CURSOR csr_particulars(p_balance_name pay_balance_types.balance_name%TYPE)
121 IS
122 SELECT hrl.description
123 FROM hr_lookups hrl
124 WHERE hrl.lookup_type = 'HK_IR56_BOX_DESC'
125 AND hrl.lookup_code = p_balance_name
126 AND to_date('3103'||p_reporting_year,'DDMMYYYY')
127 BETWEEN nvl(start_date_active,to_date('01010001','DDMMYYYY'))
128 AND nvl(end_date_active,to_date('31124712','DDMMYYYY'));
129
130 CURSOR csr_hire_date
131 IS
132 SELECT pps.date_start,
133 pps.actual_termination_date
134 FROM per_periods_of_service pps,
135 per_people_f ppf,
136 per_assignments_f paf
137 WHERE paf.person_id = ppf.person_id
138 AND pps.person_id = paf.person_id
139 AND paf.assignment_id = p_assignment_id
140 AND paf.period_of_service_id = pps.period_of_service_id; /* Bug No : 2850738 */
141
142 /* Bug 8406450 - Check if any Balance Ajdustments have been done for balance */
143
144 CURSOR csr_balance_adj_exist
145 (p_balance_name pay_balance_types.balance_name%TYPE,
146 p_fin_start_date date,
147 p_fin_end_date date)
148 IS
149 SELECT COUNT(pivf.input_value_id)
150 FROM pay_element_entries_f pee,
151 pay_element_types_f pet,
152 pay_input_values_f pivf,
153 pay_balance_types pbt,
154 pay_balance_feeds_f pbf
155 WHERE pee.assignment_id = p_assignment_id
156 AND pee.entry_type = 'B'
157 AND pee.element_type_id = pet.element_type_id
158 AND pet.element_type_id = pivf.element_type_id
159 AND pbf.input_value_id = pivf.input_value_id
160 AND pbf.balance_type_id = pbt.balance_type_id
161 AND pbt.balance_name = p_balance_name
162 AND pbt.legislation_code = 'HK'
163 AND ((pbf.legislation_code = 'HK' and pbf.business_group_id IS NULL) OR
164 (pbf.business_group_id = pivf.business_group_id AND pbf.legislation_code IS NULL))
165 AND pee.effective_start_date <= p_fin_end_date
166 AND pee.effective_end_date >= p_fin_start_date;
167
168 BEGIN
169
170 /*Bug# 3626489 g_debug := hr_utility.debug_enabled;*/
171 g_debug := FALSE;
172
173 IF g_debug THEN
174 hr_utility.trace('Leaving:' || 'pay_hk_ir56.get_emoluments');
175 hr_utility.trace('Values of the input parameters');
176 hr_utility.trace('------------------------------');
177 hr_utility.trace('Assignment id' || ' =>' || p_assignment_id);
178 hr_utility.trace('Assignment Action Id' || '=>' || p_assignment_action_id);
179 hr_utility.trace('Tax Unit Id' || ' =>' || p_tax_unit_id);
180 hr_utility.trace('Reporting Year' || ' =>' || p_reporting_year);
181 END IF;
182
183 populate_defined_balance_ids;
184
185 IF g_debug THEN
186 hr_utility.trace('Balance Name Balance Value');
187 hr_utility.trace('----------------------------------------------');
188 END IF;
189
190 IF ((p_assignment_action_id IS NOT NULL) AND (p_assignment_action_id <> 0)) THEN
191
192
193 /* Bug 3609072 */
194 p_context_table(1).tax_unit_id := p_tax_unit_id;
195
196 hr_utility.trace('Jay get_value');
197 pay_balance_pkg.get_value(p_assignment_action_id => p_assignment_action_id
198 ,p_defined_balance_lst => p_balance_value_tab
199 ,p_context_lst => p_context_table
200 ,p_output_table => p_result_table);
201
202
203 FOR i IN p_balance_value_tab.FIRST..p_balance_value_tab.LAST
204 LOOP
205 g_emol_details(i).balance_value := p_result_table(i).balance_value; /* Bug 3609072 */
206 END LOOP;
207
208 ELSE
209
210 FOR i IN p_balance_value_tab.FIRST..p_balance_value_tab.LAST
211 LOOP
212 IF (g_emol_details.EXISTS(i)) THEN
213 g_emol_details(i).balance_value := 0;
214 END IF;
215 END LOOP;
216
217 END IF;
218
219 /* Bug No : 3062419 => Financial Year Start Date and End Date are found from the Reporting Year as below */
220
221 l_fin_start_date := to_date('01/04/' || (p_reporting_year - 1),'DD/MM/YYYY');
222 l_fin_end_date := to_date('31/03/' || p_reporting_year,'DD/MM/YYYY');
223
227
224 OPEN csr_hire_date;
225 FETCH csr_hire_date INTO l_hire_date,l_actual_termination_date;
226 CLOSE csr_hire_date;
228 IF g_debug THEN
229 hr_utility.trace('Financial Year Start Date' || '=>' || l_fin_start_date);
230 hr_utility.trace('Financial Year End Date' || '=>' || l_fin_end_date);
231 hr_utility.trace('Hire Date' || ' =>' || l_hire_date);
232 hr_utility.trace('Actual Termination Date' || ' =>' || l_actual_termination_date);
233 hr_utility.trace(' ');
234 hr_utility.trace('Balance name Period Dates Particulars');
235 hr_utility.trace('------------------------------------------------------------------');
236 END IF;
237
238 FOR i IN g_emol_details.FIRST..g_emol_details.LAST
239 LOOP
240 IF (g_emol_details.exists(i)) THEN
241 OPEN csr_particulars(g_emol_details(i).balance_name);
242 FETCH csr_particulars INTO l_particulars;
243 IF csr_particulars%NOTFOUND THEN
244 g_emol_details(i).particulars := g_emol_details(i).balance_name;
245 ELSE
246 g_emol_details(i).particulars := l_particulars;
247 END IF;
248 CLOSE csr_particulars;
249
250 OPEN csr_element_entry_dates(g_emol_details(i).balance_name,l_fin_start_date,l_fin_end_date);
254 g_emol_details(i).period_dates := to_char(greatest(l_hire_date,l_fin_start_date),'DD/MM/YYYY')||' - '||
251 FETCH csr_element_entry_dates INTO l_start_date,l_end_date,l_processing_type;
252 /* bug 6432592 */
253 IF (l_start_date IS NULL) AND (g_emol_details(i).balance_value > 0) THEN
255 to_char(least(nvl(l_actual_termination_date,l_fin_end_date),
256 l_fin_end_date,
257 nvl(l_end_date,l_fin_end_date)),
258 'DD/MM/YYYY');
259 ELSIF (l_start_date IS NULL) THEN
260 g_emol_details(i).balance_value := NULL;
261 g_emol_details(i).period_dates := NULL;
262 /*Start of bug# 8348781 - If IR56 balance is zero,null is fetched to period dates*/
263 ELSIF (g_emol_details(i).balance_value = 0) THEN
264 g_emol_details(i).period_dates := NULL;
265 /*End of bug# 8348781*/
266 ELSE
267 -- Bug# 4688776
268 /*8406450 - Period end date should be least of either termination date or tax year end date
269 - Fix done for non-recurring processing type as part of bug 4688776 is removed */
270 IF (l_processing_type = 'N') THEN
271 g_emol_details(i).period_dates := to_char(greatest(l_hire_date,l_fin_start_date),'DD/MM/YYYY')||' - '||
272 to_char(least(nvl(l_actual_termination_date,l_fin_end_date),
273 l_fin_end_date),'DD/MM/YYYY');
274 ELSE
275 /* 8406450 - Added Check to see if any Balance Adjustments have been done.
276 Bal Adjustment entries should be treated as NR entries irrespective of processing type */
277
278 l_exists := 0;
279 OPEN csr_balance_adj_exist(g_emol_details(i).balance_name,l_fin_start_date,l_fin_end_date);
280 FETCH csr_balance_adj_exist INTO l_exists;
281 CLOSE csr_balance_adj_exist;
282
283 IF (l_exists <> 0)
284 THEN
285 g_emol_details(i).period_dates := to_char(greatest(l_hire_date,l_fin_start_date),'DD/MM/YYYY')||' - '||
286 to_char(least(nvl(l_actual_termination_date,l_fin_end_date),
287 l_fin_end_date),'DD/MM/YYYY');
288 ELSE
289 g_emol_details(i).period_dates := to_char(greatest(l_fin_start_date,
290 nvl(l_start_date,l_fin_start_date)),'DD/MM/YYYY') ||' - '||
291 to_char(least(nvl(l_actual_termination_date,l_fin_end_date),l_fin_end_date,
292 nvl(l_end_date,l_fin_end_date)),'DD/MM/YYYY');
293 END IF;
294 END IF;
295 END IF;
296 CLOSE csr_element_entry_dates;
297
298 IF g_debug THEN
299 hr_utility.trace(g_emol_details(i).balance_name || ' ' || g_emol_details(i).period_dates || ' ' ||
303 END LOOP;
300 g_emol_details(i).particulars || ' ' || g_emol_details(i).balance_value);
301 END IF;
302 END IF;
304
305 RETURN g_emol_details;
306
307 IF g_debug THEN
308 hr_utility.trace('Leaving:' || 'pay_hk_ir56.get_emoluments');
309 END IF;
310
311 EXCEPTION
312 WHEN others THEN
313 raise;
314 END;
315
316 PROCEDURE populate_defined_balance_ids
317 IS
318 /*Bug #3417275 - Removed references to 4 IR56_Q quarters balances from cursor csr_defined_balance_id */
319 CURSOR csr_defined_balance_id
320 IS
321 SELECT decode(pbt.balance_name,'IR56_A',1,'IR56_B',2,'IR56_C',3,'IR56_D',4,'IR56_E',5,
322 'IR56_F',6,'IR56_G',7,'IR56_H',8,'IR56_I',9,'IR56_J',10,
323 'IR56_K1',11,'IR56_K2',12,'IR56_K3',13,'IR56_L',14,'IR56_M',15
324 ) sort_index,
325 pbt.balance_name,
326 pdb.defined_balance_id defined_balance_id
327 FROM pay_balance_types pbt,
328 pay_balance_dimensions pbd,
329 pay_defined_balances pdb
330 WHERE pbt.balance_name IN ('IR56_A','IR56_B','IR56_C','IR56_D','IR56_E','IR56_F','IR56_G','IR56_H',
331 'IR56_I','IR56_J','IR56_K1','IR56_K2','IR56_K3','IR56_L','IR56_M')
332 AND pbd.database_item_suffix = '_ASG_LE_YTD'
333 AND pbt.balance_type_id = pdb.balance_type_id
334 AND pbd.balance_dimension_id = pdb.balance_dimension_id
335 AND pbt.legislation_code = 'HK'
336 ORDER BY sort_index;
337
338 i NUMBER := 0;
339
340 BEGIN
341
342 /* Bug# 3626489 g_debug := hr_utility.debug_enabled;*/
343 g_debug := FALSE;
344
345 IF g_debug THEN
346 hr_utility.trace('Entering:' || 'pay_hk_ir56.populate_defined_balance_ids');
347 END IF;
348
349 /* Bug 3609072 */
350 p_balance_value_tab.delete;
351 g_emol_details.delete;
352
353 /* Note :-
354 ------------------------------------------------------------
355 Storage Location of
356 Defined Balance ids Balance Name
357 with dimension '_ASG_LE_YTD'
358 ------------------------------------------------------------
359 1 IR56_A
360 2 IR56_B
361 3 IR56_C
362 4 IR56_D
363 5 IR56_E
364 6 IR56_F
365 7 IR56_G
366 8 IR56_H
367 9 IR56_I
368 10 IR56_J
369 11 IR56_K1
370 12 IR56_K2
371 13 IR56_K3
372 14 IR56_L
373 15 IR56_M
374 ------------------------------------------------------------ */
375
376 IF g_debug THEN
377 hr_utility.trace('Balance Name and its Defined Balance ids for IR56% Balances with dimension _ASG_LE_YTD');
378 hr_utility.trace('--------------------------------------------------------------------------------------');
379 END IF;
380
381 FOR csr_rec IN csr_defined_balance_id
382 LOOP
383 /* Bug 3609072 */
384 p_balance_value_tab(csr_rec.sort_index).defined_balance_id := csr_rec.defined_balance_id;
385 g_emol_details(csr_rec.sort_index).balance_name := csr_rec.balance_name;
386
387 IF g_debug THEN
388 hr_utility.trace(g_emol_details(csr_rec.sort_index).balance_name || ' ===>' ||
389 p_balance_value_tab(csr_rec.sort_index).defined_balance_id);
390 END IF;
391
392 END LOOP;
393 IF g_debug THEN
394 hr_utility.trace('---------------------------------------------------------------------------------------');
395 hr_utility.trace('Leaving:' || 'pay_hk_ir56.populate_defined_balance_ids');
396 END IF;
397
398 END populate_defined_balance_ids;
399
400 --
401 -- Get the start of the tax year
402 --
403 function get_tax_year_start
404 (p_assignment_id in number
405 ,p_calculation_date in date)
406 return date is
407 l_proc varchar2(72);
408 l_tax_year_start date := null;
409 begin
410
411 /* Bug# 3626489 g_debug := hr_utility.debug_enabled;*/
412 g_debug := FALSE;
413
414 l_proc := 'pay_hk_ir56.get_last_anniversary'; --3626489
415 hr_utility.trace('In: ' || l_proc) ;
416 hr_utility.trace(' p_assignment_id: ' || to_char(p_assignment_id)) ;
417 hr_utility.trace(' p_calculation_date: ' || to_char(p_calculation_date,'DD-MON-YYYY')) ;
418
419 l_tax_year_start := to_date('0104'||to_char(p_calculation_date,'YYYY'),'DDMMYYYY');
420 if (l_tax_year_start > p_calculation_date) then
421 l_tax_year_start := add_months(l_tax_year_start,-12);
422 end if;
423 hr_utility.trace(' return: ' || to_char(l_tax_year_start,'DD-MON-YYYY')) ;
424 hr_utility.trace('Out: ' || l_proc) ;
425 return l_tax_year_start;
426 end;
427
428 /*
429 * Bug 4864213 - Added the following function get_quarters_start_date to return the quaters start date
430 */
431
432 FUNCTION get_quarters_start_date(p_assignment_id in per_assignments_f.assignment_id%TYPE,
436 l_quarters_start_date pay_hk_ir56_quarters_actions_v.start_date%TYPE;
433 p_source_id in pay_hk_ir56_quarters_actions_v.l_source_id%TYPE)
434 RETURN DATE IS
435
437
438 CURSOR csr_get_quarters_start_date
439 IS
440 select min(start_date)
441 from pay_hk_ir56_quarters_actions_v
442 where assignment_id = p_assignment_id
443 and l_source_id = p_source_id;
444
445 BEGIN
446 l_quarters_start_date := null;
447
448 OPEN csr_get_quarters_start_date;
449 FETCH csr_get_quarters_start_date into l_quarters_start_date;
450 CLOSE csr_get_quarters_start_date;
451
452 IF l_quarters_start_date IS NOT NULL THEN
453 RETURN l_quarters_start_date;
454 END IF;
455
456 RETURN null;
457 END get_quarters_start_date;
458
459
460 end pay_hk_ir56;
[Home] [Help]