[Home] [Help]
PACKAGE BODY: APPS.PAY_KW_PIFSS_REPORT
Source
1 package body pay_kw_pifss_report AS
2 /* $Header: pykwpifn.pkb 120.10 2012/01/19 10:27:55 rpahune ship $ */
3 --------------------
4 FUNCTION get_def_bal_id (p_bal_name in varchar2) return number is
5 l_def_bal_id number;
6 cursor get_bal_id (l_bal_name varchar2) IS
7 select u.creator_id
8 from ff_user_entities u,
9 ff_database_items d
10 where d.user_name = l_bal_name
11 and u.user_entity_id = d.user_entity_id
12 and u.legislation_code = 'KW'
13 and u.business_group_id is null
14 and u.creator_type = 'B';
15 begin
16 open get_bal_id (p_bal_name);
17 fetch get_bal_id into l_def_bal_id;
18 close get_bal_id;
19 return l_def_bal_id;
20 end get_def_bal_id;
21 --------------------
22 FUNCTION get_new_count (
23 p_employer in number
24 ,p_month in varchar2
25 ,p_year in varchar2
26 ,p_nationality in varchar2) RETURN number IS
27 l_temp_effective_date date;
28 l_new_count number;
29 l_temp_pid number;
30 CURSOR csr_get_new_emp (l_employer_id number,l_effective_date date , l_nationality varchar2) IS
31 SELECT distinct asg.person_id
32 FROM per_assignments_f asg
33 ,pay_assignment_actions paa
34 ,pay_payroll_actions ppa
35 ,hr_soft_coding_keyflex hscl
36 ,per_periods_of_service pos
37 ,per_people_f pef
38 WHERE asg.assignment_id = paa.assignment_id
39 AND paa.payroll_action_id = ppa.payroll_action_id
40 AND pos.period_of_service_id = asg.period_of_service_id
41 AND ppa.action_type in ('R','Q')
42 AND ppa.action_status = 'C'
43 AND paa.action_status IN ('C','S') --10375683
44 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
45 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
46 AND trunc(pos.date_start, 'MM') = trunc(l_effective_date, 'MM')
47 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
48 AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
49 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
50 AND hscl.segment1 = to_char(l_employer_id)
51 AND pef.person_id = asg.person_id
52 AND pef.nationality = l_nationality;
53 begin
54 l_temp_effective_date := to_date('01'||'-'||p_month||'-'||p_year,'dd-mm-yyyy');
55 vNEWCtr := 1;
56 l_new_count := 0;
57 open csr_get_new_emp(p_employer,l_temp_effective_date,p_nationality);
58 fetch csr_get_new_emp into l_temp_pid;
59 IF csr_get_new_emp % FOUND THEN
60 close csr_get_new_emp;
61 open csr_get_new_emp(p_employer,l_temp_effective_date,p_nationality);
62 LOOP
63 fetch csr_get_new_emp into vNEWTable(vNEWCtr).person_id;
64 EXIT WHEN csr_get_new_emp % NOTFOUND;
65 vNEWCtr := vNEWCtr + 1;
66 l_new_count := l_new_count + 1;
67 END LOOP;
68 ELSE
69 l_new_count := 0;
70 END IF;
71 close csr_get_new_emp;
72 return l_new_count;
73 end get_new_count;
74 --------------------
75 FUNCTION get_total_count (
76 p_employer in number
77 ,p_month in varchar2
78 ,p_year in varchar2
79 ,p_nationality in varchar2) RETURN number IS
80 l_temp_effective_date date;
81 l_total_count number;
82 l_temp_pid number;
83 CURSOR csr_get_tot_emp (l_employer_id number,l_effective_date date , l_nationality varchar2) IS
84 SELECT distinct asg.person_id
85 FROM per_assignments_f asg
86 ,pay_assignment_actions paa
87 ,pay_payroll_actions ppa
88 ,hr_soft_coding_keyflex hscl
89 ,per_people_f pef
90 WHERE asg.assignment_id = paa.assignment_id
91 AND paa.payroll_action_id = ppa.payroll_action_id
92 AND ppa.action_type in ('R','Q')
93 AND ppa.action_status = 'C'
94 AND paa.action_status IN ('C','S')
95 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
96 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
97 AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
98 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
99 AND hscl.segment1 = to_char(l_employer_id)
100 AND pef.person_id = asg.person_id
101 AND pef.nationality = l_nationality;
102 begin
103 l_temp_effective_date := to_date('01'||'-'||p_month||'-'||p_year,'dd-mm-yyyy');
104 vTOTCtr := 1;
105 l_total_count := 0;
106 open csr_get_tot_emp(p_employer,l_temp_effective_date,p_nationality);
107 fetch csr_get_tot_emp into l_temp_pid;
108 IF csr_get_tot_emp % FOUND THEN
109 close csr_get_tot_emp;
110 open csr_get_tot_emp(p_employer,l_temp_effective_date,p_nationality);
111 LOOP
112 fetch csr_get_tot_emp into vTOTTable(vTOTCtr).person_id;
113 EXIT WHEN csr_get_tot_emp % NOTFOUND;
114 vTOTCtr := vTOTCtr + 1;
115 l_total_count := l_total_count + 1;
116 END LOOP;
117 ELSE
118 l_total_count := 0;
119 END IF;
120 close csr_get_tot_emp;
121 return l_total_count;
122 end get_total_count;
123 --------------------
124 FUNCTION get_change_count (
125 p_employer in number
126 ,p_month in varchar2
127 ,p_year in varchar2
128 ,p_nationality in varchar2) RETURN number IS
129 l_temp_effective_date date;
130 l_temp_prev_effective_date date;
131 l_change_count number;
132 l_temp_pid number;
133 l_defined_balance_id_net_asg number;
134 l_cur_asact_id number;
135 l_prev_asact_id number;
136 j number;
137 l_n_c number;
138 -- Cursor to get person ids of employees neither new nor terminated.
139 CURSOR csr_get_change_emp(l_employer_id number, l_effective_date date , l_nationality varchar2) IS
140 SELECT distinct asg.person_id
141 FROM per_assignments_f asg
142 ,pay_assignment_actions paa
143 ,pay_payroll_actions ppa
144 ,hr_soft_coding_keyflex hscl
145 ,per_periods_of_service pos
146 ,per_people_f pef
147 WHERE asg.assignment_id = paa.assignment_id
148 AND paa.payroll_action_id = ppa.payroll_action_id
149 AND pos.period_of_service_id = asg.period_of_service_id
150 AND ppa.action_type in ('R','Q')
151 AND ppa.action_status = 'C'
152 AND paa.action_status IN ('C','S')
153 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
154 AND trunc(pos.date_start, 'MM') <> trunc(l_effective_date, 'MM')
155 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') <> TRUNC(l_effective_date, 'MM')
156 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
157 AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
158 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
159 AND hscl.segment1 = to_char(l_employer_id)
160 AND pef.person_id = asg.person_id
161 AND pef.nationality = l_nationality;
162 --Cursor to get assignment_action_id of total employees
163 cursor get_assact_id_tot (p_org_id number,p_date date,p_person_id number , l_nationality varchar2) is
164 select paa.assignment_action_id from per_assignments_f asg
165 ,pay_assignment_actions paa
166 ,pay_payroll_actions ppa
167 ,hr_soft_coding_keyflex hscl
168 ,per_periods_of_service pos
169 ,per_people_f pef
170 where rownum < 2
171 and asg.assignment_id = paa.assignment_id
172 and asg.person_id = p_person_id
173 and paa.payroll_action_id = ppa.payroll_action_id
174 and ppa.action_type in ('R','Q')
175 and ppa.action_status = 'C'
176 and paa.action_status IN ('C','S')
177 and trunc(ppa.date_earned,'MM') = trunc(p_date, 'MM')
178 and trunc(p_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
179 and trunc(p_date,'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
180 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
181 and hscl.segment1 = to_char(p_org_id)
182 and pef.person_id = asg.person_id
183 and pef.nationality = l_nationality
184 order by asg.person_id;
185 --Cursor to get assignment_action_id of employees' previous month
186 cursor get_assact_id_tot_prev_month (p_org_id number,p_prev_month_date date,p_person_id number , l_nationality varchar2) is
187 select paa.assignment_action_id from per_assignments_f asg
188 ,pay_assignment_actions paa
189 ,pay_payroll_actions ppa
190 ,hr_soft_coding_keyflex hscl
191 ,per_periods_of_service pos
192 ,per_people_f pef
193 where rownum < 2
194 and asg.assignment_id = paa.assignment_id
195 and asg.person_id = p_person_id
196 and paa.payroll_action_id = ppa.payroll_action_id
197 and ppa.action_type in ('R','Q')
198 and ppa.action_status = 'C'
199 and paa.action_status IN ('C','S')
200 and trunc(ppa.date_earned,'MM') = trunc(p_prev_month_date, 'MM')
201 and trunc(p_prev_month_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
202 and trunc(p_prev_month_date,'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
203 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
204 and hscl.segment1 = to_char(p_org_id)
205 and pef.person_id = asg.person_id
206 and pef.nationality = l_nationality
207 order by asg.person_id;
208 begin
209 l_temp_effective_date := to_date('01'||'-'||p_month||'-'||p_year,'dd-mm-yyyy');
210 l_temp_prev_effective_date := add_months(l_temp_effective_date,-1);
211 vCHANGECtr := 1;
212 l_change_count := 0;
213 j := 1;
214 l_n_c := 0 ;
215 pay_balance_pkg.set_context('TAX_UNIT_ID',p_employer);
216 pay_balance_pkg.set_context('DATE_EARNED',FND_DATE.DATE_TO_CANONICAL(l_temp_effective_date));
217 l_defined_balance_id_net_asg := get_def_bal_id('SUBJECT_TO_SOCIAL_INSURANCE_ASG_RUN');
218 if csr_get_change_emp % ISOPEN then
219 close csr_get_change_emp;
220 open csr_get_change_emp(p_employer,l_temp_effective_date,p_nationality);
221 else
222 open csr_get_change_emp(p_employer,l_temp_effective_date,p_nationality);
223 end if;
224 fetch csr_get_change_emp into l_temp_pid;
225 IF csr_get_change_emp % FOUND THEN
226 if csr_get_change_emp % ISOPEN then
227 close csr_get_change_emp;
228 open csr_get_change_emp(p_employer,l_temp_effective_date,p_nationality);
229 else
230 open csr_get_change_emp(p_employer,l_temp_effective_date,p_nationality);
231 end if;
232 LOOP
233 fetch csr_get_change_emp into vCHANGETable(vCHANGECtr).person_id;
234 EXIT WHEN csr_get_change_emp % NOTFOUND;
235 vCHANGECtr := vCHANGECtr + 1;
236 END LOOP;
237 END IF;
238 if csr_get_change_emp%ISOPEN then
239 close csr_get_change_emp;
240 end if;
241 IF vCHANGETable.count <> 0 then
242 FOR i in vCHANGETable.first..vCHANGETable.last
243 LOOP
244 open get_assact_id_tot(p_employer , l_temp_effective_date , vCHANGETable(i).person_id,p_nationality);
245 fetch get_assact_id_tot into l_cur_asact_id;
246 if get_assact_id_tot % notfound then
247 l_cur_asact_id := 0;
248 end if;
249 close get_assact_id_tot;
250 open get_assact_id_tot_prev_month(p_employer , l_temp_prev_effective_date , vCHANGETable(i).person_id,p_nationality);
251 fetch get_assact_id_tot_prev_month into l_prev_asact_id;
252 if get_assact_id_tot_prev_month % notfound then
253 l_prev_asact_id := 0;
254 l_n_c := 1 ;
255 end if;
256 close get_assact_id_tot_prev_month;
257 IF l_n_c <> 1 and pay_balance_pkg.get_value(l_defined_balance_id_net_asg,l_cur_asact_id) <> pay_balance_pkg.get_value(l_defined_balance_id_net_asg,l_prev_asact_id) THEN
258 l_change_count := l_change_count + 1;
259 vCHANGE_FINALTable(j).person_id := vCHANGETable(i).person_id;
260 j := j + 1;
261 END IF;
262 l_n_c := 0 ;
263 END LOOP;
264 END IF;
265 return l_change_count;
266 end get_change_count;
267 --------------------
268 FUNCTION get_term_count (
269 p_employer in number
270 ,p_month in varchar2
271 ,p_year in varchar2
272 ,p_nationality in varchar2) RETURN number IS
273 l_temp_effective_date date;
274 l_term_count number;
275 l_temp_pid number;
276 CURSOR csr_get_term_emp(l_employer_id number,l_effective_date date , l_nationality varchar2) IS
277 SELECT distinct asg.person_id
278 FROM per_assignments_f asg
279 ,pay_assignment_actions paa
280 ,pay_payroll_actions ppa
281 ,hr_soft_coding_keyflex hscl
282 ,per_periods_of_service pos
283 ,per_people_f pef
284 WHERE asg.assignment_id = paa.assignment_id
285 AND paa.payroll_action_id = ppa.payroll_action_id
286 AND pos.period_of_service_id = asg.period_of_service_id
287 AND ppa.action_type in ('R','Q')
288 AND ppa.action_status = 'C'
289 AND paa.action_status IN ('C','S')
290 AND trunc(ppa.date_earned,'MM') = TRUNC(l_effective_date, 'MM')
291 AND trunc(NVL(pos.actual_termination_date, to_date('31-12-4712','DD-MM-YYYY')),'MM') = TRUNC(l_effective_date, 'MM')
292 AND trunc(l_effective_date, 'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
293 AND trunc(l_effective_date, 'MM') between trunc(pef.effective_start_date,'MM') and pef.effective_end_date
294 AND hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
295 AND hscl.segment1 = to_char(l_employer_id)
296 AND pef.person_id = asg.person_id
297 AND pef.nationality = l_nationality;
298 begin
299 l_temp_effective_date := to_date('01'||'-'||p_month||'-'||p_year,'dd-mm-yyyy');
300 vTERMCtr := 1;
301 l_term_count := 0;
302 open csr_get_term_emp(p_employer,l_temp_effective_date,p_nationality);
303 fetch csr_get_term_emp into l_temp_pid;
304 IF csr_get_term_emp % FOUND THEN
305 close csr_get_term_emp;
306 open csr_get_term_emp(p_employer,l_temp_effective_date,p_nationality);
307 LOOP
308 fetch csr_get_term_emp into vTERMTable(vTERMCtr).person_id;
309 EXIT WHEN csr_get_term_emp % NOTFOUND;
310 vTERMCtr := vTERMCtr + 1;
311 l_term_count := l_term_count + 1;
312 END LOOP;
313 ELSE
314 l_term_count := 0;
315 END IF;
316 close csr_get_term_emp;
317 return l_term_count;
318 end get_term_count;
319 --------------------
320 FUNCTION get_change_indicator( p_person_id in number) RETURN varchar2 IS
321 l_indicator varchar2(1);
322 i number;
323 begin
324 if vTERMTable.count <> 0 then
325 FOR i in vTERMTable.first..vTERMTable.last
326 LOOP
327 if vTERMTable(i).person_id = p_person_id then
328 l_indicator := 'T';
329 exit;
330 end if;
331 END LOOP;
332 end if;
333 if l_indicator is not null then
334 null;
335 else
336 if vNEWTable.count <> 0 then
337 FOR i in vNEWTable.first..vNEWTable.last
338 LOOP
339 if vNEWTable(i).person_id = p_person_id then
340 l_indicator := 'N';
341 exit;
342 end if;
343 END LOOP;
344 end if;
345 end if;
346 if l_indicator is not null then
347 null;
348 else
349 if vCHANGE_FINALTable.count <> 0 then
350 FOR i in vCHANGE_FINALTable.first..vCHANGE_FINALTable.last
351 LOOP
352 if vCHANGE_FINALTable(i).person_id = p_person_id then
353 l_indicator := 'C';
354 exit;
355 end if;
356 END LOOP;
357 end if;
358 end if;
359 return l_indicator;
360 end get_change_indicator;
361 --------------------
362 FUNCTION get_parameter(
363 p_parameter_string IN VARCHAR2
364 ,p_token IN VARCHAR2
365 ,p_segment_number IN NUMBER DEFAULT NULL ) RETURN VARCHAR2
366 IS
367 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
368 l_start_pos NUMBER;
369 l_delimiter varchar2(1);
370 BEGIN
371 l_delimiter :=' ';
372 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
373 IF l_start_pos = 0 THEN
374 l_delimiter := '|';
375 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
376 end if;
377 IF l_start_pos <> 0 THEN
378 l_start_pos := l_start_pos + length(p_token||'=');
379 l_parameter := substr(p_parameter_string,
380 l_start_pos,
381 instr(p_parameter_string||' ',
382 ',',l_start_pos)
383 - l_start_pos);
384 IF p_segment_number IS NOT NULL THEN
385 l_parameter := ':'||l_parameter||':';
386 l_parameter := substr(l_parameter,
387 instr(l_parameter,':',1,p_segment_number)+1,
388 instr(l_parameter,':',1,p_segment_number+1) -1
389 - instr(l_parameter,':',1,p_segment_number));
390 END IF;
391 END IF;
392 RETURN l_parameter;
393 END get_parameter;
394 ---------------
395 FUNCTION get_deduction_detail(p_report_type in varchar2,
396 p_assignment_action_id in number,
397 p_assignment_id in number,
398 p_date in date) RETURN varchar2 IS
399 l_ded_detail_string varchar2(2000) := null;
400 l_seq_num number := 1;
401 l_install_num number := 1;
402 l_temp_amount number(9,3) :=0;
403 l_temp_ded_type varchar2(100);
404 l_temp_start_date date;
405 l_temp_end_date date;
406 l_element_type_id number;
407 l_setup_ded number;
408 l_ded_type varchar2(100);
409
410 l_ele_start_date date;
411
412 CURSOR csr_get_ded_details (l_assignment_id number , l_assignment_action_id number, l_effective_date date) IS
413 SELECT rrv.RESULT_VALUE val,pee.entry_information3 type,fnd_date.canonical_to_date(pee.entry_information5) start_d
414 ,fnd_date.canonical_to_date(pee.entry_information6) end_d ,pet.element_type_id , pet.effective_start_date
415 FROM pay_element_types_f pet,
416 pay_element_entries_f pee,
417 pay_run_results prr,
418 pay_run_result_values rrv,
419 pay_input_values_f piv
420 WHERE rrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
421 AND prr.assignment_action_id = l_assignment_action_id
422 AND prr.ELEMENT_ENTRY_ID = pee.ELEMENT_ENTRY_ID
423 AND piv.element_type_id = pet.element_type_id
424 AND piv.name = 'Pay Value'
425 AND rrv.input_value_id = piv.input_value_id
426 AND pee.assignment_id = l_assignment_id
427 AND TRUNC(l_effective_date,'MM') between trunc(pee.effective_start_date,'MM') and nvl(pee.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
428 AND TRUNC(l_effective_date,'MM') between trunc(piv.effective_start_date,'MM') and nvl(piv.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
429 AND pee.element_type_id = pet.element_type_id
430 AND pee.entry_information3 is not null
431 AND rrv.result_value is not null
432 AND TRUNC(l_effective_date,'MM') between trunc(pet.effective_start_date,'MM') and nvl(pet.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'));
433
434 CURSOR csr_get_install_number (l_assignment_id number , l_type_id number, l_start_date date) IS
435 select pev.screen_entry_value
436 from pay_element_types_f pet,
437 pay_element_entries_f pee,
438 pay_element_entry_values_f pev
439 where pet.element_type_id = l_type_id
440 AND trunc(pet.effective_start_date,'MM') = trunc(l_start_date,'MM')
441 AND pee.element_type_id = pet.element_type_id
442 AND pee.assignment_id = l_assignment_id
443 AND trunc(l_start_date,'MM') between pee.effective_start_date and pee.effective_end_date
444 AND pee.element_entry_id = pev.element_entry_id
445 AND trunc(l_start_date,'MM') between pev.effective_start_date and pev.effective_end_date;
446
447 CURSOR csr_get_ded_table(p_row varchar2,l_date date) IS
448 SELECT i.value
449 FROM pay_user_column_instances_f i,
450 pay_user_rows_f r,
451 pay_user_columns c,
452 pay_user_tables t
453 WHERE UPPER(t.user_table_name) = UPPER('KW_DEDUCTION_MAPPING')
454 AND t.legislation_code = 'KW'
455 AND t.user_table_id = r.user_table_id
456 AND t.user_table_id = c.user_table_id
457 AND UPPER(c.user_column_name) = UPPER('DEDUCTION_TYPE')
458 AND c.legislation_code = 'KW'
459 AND r.row_low_range_or_name = p_row
460 AND r.user_row_id = i.user_row_id
461 AND c.user_column_id = i.user_column_id
462 AND l_date BETWEEN r.effective_start_date AND r.effective_end_date
463 AND l_date BETWEEN i.effective_start_date AND i.effective_end_date;
464
465 begin
466 open csr_get_ded_details (p_assignment_id,p_assignment_action_id,p_date);
467 fetch csr_get_ded_details into l_temp_amount,l_temp_ded_type,l_temp_start_date,l_temp_end_date,l_element_type_id,l_ele_start_date;
468 if csr_get_ded_details % found then
469 close csr_get_ded_details;
470 open csr_get_ded_details (p_assignment_id,p_assignment_action_id,p_date);
471 LOOP
472 fetch csr_get_ded_details into l_temp_amount,l_temp_ded_type,l_temp_start_date,l_temp_end_date,l_element_type_id,l_ele_start_date;
473 exit when csr_get_ded_details % notfound;
474 open csr_get_install_number(p_assignment_id , l_element_type_id , l_ele_start_date);
475 fetch csr_get_install_number into l_setup_ded;
476 close csr_get_install_number;
477 if l_setup_ded is not null then
478 l_install_num := round(l_temp_amount/l_setup_ded);
479 else
480 l_install_num := 1;
481 end if;
482
483 l_ded_type := null;
484
485 /* Code to pick up the user table equivalent of deduction type */
486 OPEN csr_get_ded_table(l_temp_ded_type,p_date);
487 FETCH csr_get_ded_table into l_ded_type;
488 CLOSE csr_get_ded_table;
489
490 IF l_ded_type is not null then
491 if p_report_type ='KW_PIFSS_REPORT' then
492 l_ded_detail_string := l_ded_detail_string || LPAD(nvl(l_ded_type,'0'),2,'0') || LPAD(l_seq_num,2,'0') || LPAD(to_char(l_temp_amount*1000 ),7,'0')||LPAD(nvl(to_char(l_temp_start_date,'YYYYMMDD'),' '),8,' ') || LPAD(l_install_num,2,'0');
493 else
494 l_ded_detail_string := l_ded_detail_string || LPAD(nvl(l_ded_type,'0'),2,'0') || LPAD(to_char(l_temp_amount*1000 ),7,'0') || LPAD(l_install_num,2,'0');
495 end if;
496 l_seq_num := l_seq_num + 1;
497 END IF;
498 END LOOP;
499 else
500 if p_report_type='KW_PIFSS_REPORT' then
501 l_ded_detail_string := LPAD(' ',21,' ');
502 else
503 l_ded_detail_string := LPAD(' ',11,' ');
504 end if;
505 end if;
506 return l_ded_detail_string;
507 end get_deduction_detail;
508 -----------------------------
509 FUNCTION get_amount_cont (p_employer_id number, p_assact_cur_id number , p_person_id number , p_effective_date date) return varchar2 IS
510
511 CURSOR csr_get_first_assact_id (l_employer_id number, l_date date ,l_person_id number) IS
512 select paa.assignment_action_id
513 from per_assignments_f asg
514 ,pay_assignment_actions paa
515 ,pay_payroll_actions ppa
516 ,hr_soft_coding_keyflex hscl
517 ,per_periods_of_service pos
518 where rownum < 2
519 and asg.assignment_id = paa.assignment_id
520 and asg.person_id = l_person_id
521 and paa.payroll_action_id = ppa.payroll_action_id
522 and ppa.action_type in ('R','Q')
523 and ppa.action_status = 'C'
524 and paa.action_status in ('C','S')
525 and trunc(ppa.date_earned,'MM') = trunc(l_date, 'MM')
526 and trunc(l_date,'MM') between trunc(asg.effective_start_date,'MM') and asg.effective_end_date
527 and hscl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
528 and hscl.segment1 = to_char(l_employer_id)
529 order by asg.person_id;
530
531 CURSOR csr_get_start_date(l_person_id number , l_effective_date date) IS
532 Select start_date
533 From per_people_f
534 Where person_id = l_person_id
535 And l_effective_date between effective_start_date and effective_end_date;
536
537 CURSOR csr_soc_bal_id(l_employer_id number) IS
538 SELECT ORG_INFORMATION1
539 FROM HR_ORGANIZATION_INFORMATION
540 WHERE Organization_id = l_employer_id
541 AND org_information_context = 'KW_SI_DETAILS';
542
543 l_ret_string varchar2(2000);
544 l_cont_wage_id number;
545 l_subject_to_social_id number;
546 l_basic_social_id number;
547 l_supplementary_social_id number;
548 l_additional_social_id number;
549 l_first_assact_id number;
550 l_temp_start_date date;
551 l_act_first_date date;
552 l_first_sal number(12,3);
553 l_cur_sal number(12,3);
554 l_first_social number(12,3);
555 l_cur_social number(12,3);
556 l_test_cont_sal number(12,3);
557 l_soc_bal_id number;
558 l_tot_earn_id number;
559
560 begin
561
562 l_subject_to_social_id := get_def_bal_id('SUBJECT_TO_SOCIAL_INSURANCE_ASG_RUN');
563 l_cont_wage_id:= get_def_bal_id('CONTRIBUTORY_WAGE_ASG_YTD');
564 l_basic_social_id := get_def_bal_id('EMPLOYEE_BASIC_SOCIAL_INSURANCE_ASG_RUN');
565 l_supplementary_social_id := get_def_bal_id('EMPLOYEE_SUPPLEMENTARY_SOCIAL_INSURANCE_ASG_RUN');
566 l_additional_social_id := get_def_bal_id('ADDITIONAL_SOCIAL_INSURANCE_ASG_RUN');
567 l_tot_earn_id := get_def_bal_id('TOTAL_EARNINGS_ASG_RUN');
568
569 OPEN csr_soc_bal_id(p_employer_id);
570 FETCH csr_soc_bal_id into l_soc_bal_id;
571 CLOSE csr_soc_bal_id;
572
573 pay_balance_pkg.set_context('TAX_UNIT_ID', p_employer_id);
574 pay_balance_pkg.set_context('DATE_EARNED', p_effective_date);
575 open csr_get_start_date (p_person_id , p_effective_date);
576 fetch csr_get_start_date into l_temp_start_date;
577 close csr_get_start_date;
578 If l_temp_start_date < trunc(p_effective_date,'YYYY') then
579 l_act_first_date := trunc(p_effective_date,'YYYY');
580 --l_cont_wage_id := l_subject_to_social_id;
581 Else
582 l_act_first_date := l_temp_start_date;
583 End If;
584 open csr_get_first_assact_id (p_employer_id ,l_act_first_date, p_person_id );
585 fetch csr_get_first_assact_id into l_first_assact_id;
586 close csr_get_first_assact_id ;
587 l_test_cont_sal := pay_balance_pkg.get_value(l_cont_wage_id,l_first_assact_id);
588 if l_test_cont_sal <= to_number(pay_kw_general.get_table_bands(p_effective_date,'Kuwait Social Insurance','MIN_LOW')) then
589 l_test_cont_sal := to_number(pay_kw_general.get_table_bands(p_effective_date,'Kuwait Social Insurance','MIN_LOW'));
590 elsif l_test_cont_sal >= to_number(pay_kw_general.get_table_bands(p_effective_date,'Kuwait Social Insurance','MAX_HIGH')) then
591 l_test_cont_sal := to_number(pay_kw_general.get_table_bands(p_effective_date,'Kuwait Social Insurance','MAX_HIGH'));
592 end if;
593 l_first_sal := l_test_cont_sal*1000;
594
595 /*
596 l_cur_sal := pay_balance_pkg.get_value(l_subject_to_social_id ,p_assact_cur_id)*1000;
597 */
598
599 /*** Changed after kuwait annual report 55 ****/
600 l_cur_sal := pay_balance_pkg.get_value(l_tot_earn_id,p_assact_cur_id)*1000;
601
602 /* l_first_social := (pay_balance_pkg.get_value(l_basic_social_id ,l_first_assact_id) +
603 pay_balance_pkg.get_value(l_supplementary_social_id ,l_first_assact_id) +
604 pay_balance_pkg.get_value(l_additional_social_id ,l_first_assact_id) ) * 1000;
605 */
606
607 /*** Changed after kuwait annual report 103 ***/
608
609 If l_soc_bal_id is not null then
610 l_first_social := pay_balance_pkg.get_value(l_soc_bal_id,l_first_assact_id)*1000;
611 Else
612 l_first_social := 0;
613 End If;
614
615 /* l_cur_social := (pay_balance_pkg.get_value(l_basic_social_id ,p_assact_cur_id) +
616 pay_balance_pkg.get_value(l_supplementary_social_id ,p_assact_cur_id) +
617 pay_balance_pkg.get_value(l_additional_social_id ,p_assact_cur_id)) * 1000;
618 */
619
620 If l_soc_bal_id is not null then
621 l_cur_social := pay_balance_pkg.get_value(l_soc_bal_id,p_assact_cur_id)*1000;
622 Else
623 l_cur_social := 0;
624 End If;
625
626
627 l_ret_string := LPAD(to_char(nvl(l_first_sal,0)),9,'0') || LPAD(to_char (nvl(l_cur_sal,0)),9,'0') || LPAD(to_char(nvl(l_first_social,0)),7,'0') || LPAD( to_char(nvl(l_cur_social,0)),7,'0');
628 return LPAD(to_char(nvl(l_first_sal,0)),9,'0') || LPAD(to_char (nvl(l_cur_sal,0)),9,'0') || LPAD(to_char(nvl(l_first_social,0)),7,'0') || LPAD( to_char(nvl(l_cur_social,0)),7,'0');
629 --return l_ret_string ;
630 end get_amount_cont;
631 -----------------------------
632 END pay_kw_pifss_report;