[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_REM_REPORT
Source
1 PACKAGE BODY PAY_NL_REM_REPORT as
2 /* $Header: paynlremrep.pkb 120.0.12020000.18 2013/03/29 09:28:18 sgmaram noship $ */
3
4 EOL VARCHAR2(5) := FND_GLOBAL.NEWLINE();
5 X NUMBER := 0;
6
7 FUNCTION get_lookup_value(p_lookup_type IN VARCHAR2
8 ,p_lookup_code IN VARCHAR2
9 ,p_language IN VARCHAR2 DEFAULT 'US')
10 RETURN VARCHAR2 IS
11
12 CURSOR c_get_lookup_value (c_lookup_type IN VARCHAR2
13 ,c_lookup_code IN VARCHAR2
14 ,c_language IN VARCHAR2) IS
15 SELECT meaning
16 FROM fnd_lookup_values
17 WHERE lookup_type = c_lookup_type
18 AND language = c_language
19 AND lookup_code = c_lookup_code;
20
21 BEGIN
22 FOR r_get_lookup_value in c_get_lookup_value(p_lookup_type,p_lookup_code,p_language)
23 LOOP
24 RETURN r_get_lookup_value.meaning;
25 END LOOP;
26 RETURN NULL;
27
28 EXCEPTION
29 WHEN TOO_MANY_ROWS THEN
30 RETURN NULL;
31 WHEN NO_DATA_FOUND THEN
32 RETURN NULL;
33 WHEN OTHERS THEN
34 fnd_file.put_line(fnd_file.output,'Exception in get_lookup_value SQL-ERRM :'||SQLERRM);
35
36 END get_lookup_value;
37
38 -- ==============================================================================
39 -- Function to replace unallowed characters in XML Values
40 -- ==============================================================================
41 FUNCTION clean_XML(P_STRING IN VARCHAR2)
42 RETURN VARCHAR2 AS
43 l_string varchar2(1000);
44 BEGIN
45 l_string := p_string;
46 l_string := REPLACE(l_string, '&', '&'||'amp;');
47 l_string := REPLACE(l_string, '<', '&'||'lt;'); --#60
48 l_string := REPLACE(l_string, '>', '&'||'gt;'); --#62
49 l_string := REPLACE(l_string, '''','&'||'apos;');
50 l_string := REPLACE(l_string, '"', '&'||'quot;');
51 RETURN l_string;
52 EXCEPTION
53 WHEN others THEN
54 fnd_file.put_line(fnd_file.output,'Exception in clean_XML SQL-ERRM :'||SQLERRM);
55 RETURN l_string;
56 END clean_XML;
57
58 -- ==============================================================================
59 -- Procedure to append the details to main report
60 -- ==============================================================================
61 PROCEDURE write_to_clob(p_xmltable IN txmltable
62 ,p_clob OUT NOCOPY CLOB) IS
63
64 l_xml_element VARCHAR2(32767);
65 l_str VARCHAR2(80);
66 l_string VARCHAR2(32767) := NULL;
67 l_string1 VARCHAR2(80) := NULL;
68 BEGIN
69
70 fnd_file.put_line(fnd_file.log,'+====write_to_clob==========================================+');
71
72 l_str := '<?xml version="1.0" ?>'||EOL;
73 dbms_lob.createtemporary(p_clob, FALSE, DBMS_LOB.CALL);
74 dbms_lob.open(p_clob, DBMS_LOB.LOB_READWRITE);
75
76
77 IF p_XMLTable.COUNT > 0 THEN
78 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
79 LOOP
80 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
81 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>'||EOL;
82 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
83 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>'||EOL;
84 ELSE
85 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
86 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
87 '</'||p_XMLTable(table_counter).tagname || '>'||EOL;
88 END IF;
89 dbms_lob.writeappend(p_clob, length(l_xml_element), l_xml_element);
90 END LOOP;
91 ELSE
92 dbms_lob.writeAppend(p_clob, length(l_str), l_str );
93 END IF;
94
95 fnd_file.put_line(fnd_file.log,'X====WRITE_to_clob==========================================X');
96
97 EXCEPTION
98 WHEN others THEN
99 fnd_file.put_line(fnd_file.output,'Exception in write_to_clob SQL-ERRM : '||SQLERRM);
100 hr_utility.raise_error;
101 END write_to_clob;
102
103
104 PROCEDURE Tag (pTagName IN VARCHAR2
105 ,pTagValue IN VARCHAR2)
106 IS
107 BEGIN
108 IF pTagValue IS NOT NULL THEN
109 xXMLTable(X).TagName := pTagName;
110 xXMLTable(X).TagValue := pTagValue;
111 X := X + 1;
112 END IF;
113 END Tag;
114
115 -- =============================================================================
116 -- get_balance_value - Function to get Balance Vaue
117 -- =============================================================================
118 FUNCTION get_balance_value( p_bal_dim VARCHAR2
119 ,p_assignment_action_id in number
120 ,p_context_id NUMBER DEFAULT NULL
121 ,p_context_value VARCHAR2 DEFAULT NULL
122 ,p_retro_period VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
123
124 --=============================================================================
125 /* Cursor to retrieve Defined Balance Id */
126 CURSOR csr_def_bal_id(c_bal_dim VARCHAR2) IS
127 SELECT u.creator_id
128 FROM ff_user_entities u,
129 ff_database_items d
130 WHERE d.user_name = c_bal_dim
131 AND u.user_entity_id = d.user_entity_id
132 AND (u.legislation_code = 'NL' )
133 AND (u.business_group_id IS NULL )
134 AND u.creator_type = 'B';
135 --=============================================================================
136
137 l_balance_amount NUMBER := 0;
138 l_balance_id pay_balance_types.balance_type_id%TYPE;
139
140 BEGIN
141
142 OPEN csr_def_bal_id(p_bal_dim);
143 FETCH csr_def_bal_id INTO l_balance_id;
144 CLOSE csr_def_bal_id;
145
146 IF p_retro_period IS NULL THEN
147 IF p_context_id IS NULL AND p_context_value IS NULL THEN
148 IF l_balance_id IS NOT NULL THEN
149 IF p_assignment_action_id IS NOT NULL THEN
150 l_balance_amount := pay_balance_pkg.get_value
151 (p_defined_balance_id => l_balance_id
152 ,p_assignment_action_id => p_assignment_action_id);
153 l_balance_amount := nvl(l_balance_amount,0);
154 END IF;
155 END IF;
156 ELSE
157 IF l_balance_id IS NOT NULL THEN
158 IF p_assignment_action_id IS NOT NULL THEN
159 l_balance_amount := pay_balance_pkg.get_value
160 (l_balance_id
161 ,p_assignment_action_id
162 ,NULL
163 ,NULL
164 ,p_context_id
165 ,p_context_value
166 ,NULL
167 ,NULL);
168 l_balance_amount := nvl(l_balance_amount,0);
169 END IF;
170 END IF;
171 END IF;
172 ELSE
173 l_balance_amount := pay_balance_pkg.get_value
174 (l_balance_id
175 ,p_assignment_action_id
176 ,NULL
177 ,NULL
178 ,p_context_id
179 ,p_context_value
180 ,NULL
181 ,NULL
182 ,NULL
183 ,NULL
184 ,NULL
185 ,NULL
186 ,NULL
187 ,p_retro_period
188 ,NULL
189 ,NULL
190 ,NULL
191 ,NULL
192 ,NULL);
193 END IF;
194 RETURN l_balance_amount;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 fnd_file.put_line(fnd_file.output,'Exception in get_balance_value SQL-ERRM : '||SQLERRM);
199 RAISE;
200 RETURN NULL;
201
202 END get_balance_value;
203
204 -- =============================================================================
205 -- get_payroll_run_details - Function to get Payroll run details of Employee
206 -- =============================================================================
207 FUNCTION get_payroll_run_details(assact_id IN NUMBER,
208 p_emp_payroll_details IN OUT NOCOPY emp_payroll_details_rec)
209 RETURN NUMBER
210 IS
211
212 /*Variables declaration to calculate */
213 l_non_std_format VARCHAR2(20);
214 l_wage_in_money_tax_si_value NUMBER;
215 l_wage_in_kind_std_tax_value NUMBER;
216 l_tips_fund_tax_std_si_value NUMBER;
217 l_pre_si_and_pre_tax_value NUMBER := 0;
218 l_si_income_std_tax_value NUMBER;
219 l_zvw_base_std NUMBER;
220 l_std_tax_income NUMBER;
221 l_std_tax_deduction_value NUMBER;
222 l_std_ded_zfw NUMBER;
223 l_total_pay NUMBER;
224 l_labour_tax_deduction_value NUMBER;
225 l_lcld NUMBER;
226 l_non_spl_format VARCHAR2(20);
227 l_wage_in_money_spl_tax_value NUMBER;
228 l_wage_in_kind_spl_tax_value NUMBER;
229 l_tips_fund_tax_spl_si_value NUMBER;
230 l_spl_pre_tax NUMBER := 0;
231 l_si_income_spl_tax_value NUMBER;
232 l_zvw_base_spl NUMBER;
233 l_spl_tax_income NUMBER;
234 l_spl_tax_deduction_value NUMBER;
235 l_ee_si_cont_spl_tax_val_zfw NUMBER;
236 l_spl_total_pay NUMBER;
237 l_foreigner_rule_si_std_value NUMBER;
238 l_foreigner_rule_si_spl_value NUMBER;
239 l_pre_tax_deductions_value NUMBER;
240 l_real_si_days NUMBER;
241 l_ee_si_cont_std_tax_val_zfw NUMBER;
242 l_net_ee_si_cont_value_zfw NUMBER;
243
244 --=============================================================================
245 CURSOR csr_get_context_id(c_ass_act_id IN NUMBER) IS
246 SELECT ff.context_id context_id
247 ,pact.context_value context_value
248 ,decode (context_value
249 ,'ZFW'
250 ,0
251 ,'ZW'
252 ,1
253 ,'WEWE'
254 ,2
255 ,'WEWA'
256 ,3
257 ,'WAOD'
258 ,4
259 ,'WAOB'
260 ,5
261 ,6) seq
262 FROM ff_contexts ff
263 ,pay_action_contexts pact
264 WHERE ff.context_name = 'SOURCE_TEXT'
265 AND ff.context_id = pact.context_id
266 AND pact.assignment_action_id = c_ass_act_id
267 ORDER BY decode (context_value
268 ,'ZFW'
269 ,0
270 ,'ZW'
271 ,1
272 ,'WEWE'
273 ,2
274 ,'WEWA'
275 ,3
276 ,'WAOD'
277 ,4
278 ,'WAOB'
279 ,5
280 ,6);
281 --=============================================================================
282 CURSOR csr_get_context_bal_id IS
283 SELECT ff.context_id
284 FROM ff_contexts ff
285 WHERE ff.context_name = 'SOURCE_TEXT';
286 --=============================================================================
287
288 l_context_id NUMBER;
289 l_context_si_id NUMBER;
290 l_context_value VARCHAR2(20);
291 l_seq NUMBER;
292 l_presi_pre_tax_ded_value NUMBER;
293 l_std_pre_tax_ded NUMBER;
294 l_spl_pre_tax_ded NUMBER;
295 l_zvw_refund NUMBER;
296
297 BEGIN
298
299 OPEN csr_get_context_id(assact_id);
300 FETCH csr_get_context_id
301 INTO l_context_si_id,l_context_value,l_seq;
302 CLOSE csr_get_context_id;
303
304 OPEN csr_get_context_bal_id;
305 FETCH csr_get_context_bal_id
306 INTO l_context_id;
307 CLOSE csr_get_context_bal_id;
308
309
310 /*******************************************
311 Fetch Balance values for various balances
312 ********************************************/
313 l_zvw_refund := NVL(get_balance_value('ZVW_REFUND_ASG_RUN',assact_id),0);
314 l_foreigner_rule_si_std_value := get_balance_value('FOREIGNER_RULE_SI_INCOME_STANDARD_ADJUSTMENT_ASG_RUN',assact_id);
315 l_foreigner_rule_si_spl_value := get_balance_value('FOREIGNER_RULE_SI_INCOME_SPECIAL_ADJUSTMENT_ASG_RUN',assact_id);
316 l_wage_in_money_tax_si_value := get_balance_value('WAGE_IN_MONEY_STANDARD_TAX_SI_ASG_RUN',assact_id);
317 l_wage_in_money_spl_tax_value := get_balance_value('WAGE_IN_MONEY_SPECIAL_TAX_SI_ASG_RUN',assact_id);
318 l_wage_in_kind_std_tax_value := get_balance_value('WAGE_IN_KIND_STANDARD_TAX_SI_ASG_RUN',assact_id);
319 l_wage_in_kind_spl_tax_value := get_balance_value('WAGE_IN_KIND_SPECIAL_TAX_SI_ASG_RUN',assact_id);
320 l_tips_fund_tax_std_si_value := get_balance_value('TIPS_AND_FUND_PAYMENTS_STANDARD_TAX_SI_ASG_RUN',assact_id);
321 l_tips_fund_tax_spl_si_value := get_balance_value('TIPS_AND_FUND_PAYMENTS_SPECIAL_TAX_SI_ASG_RUN',assact_id);
322 l_pre_tax_deductions_value := get_balance_value('PRE_TAX_ONLY_DEDUCTIONS_ASG_RUN',assact_id);
323 l_si_income_std_tax_value := get_balance_value('SI_INCOME_STANDARD_TAX_ASG_RUN',assact_id);
324 l_si_income_spl_tax_value := get_balance_value('SI_INCOME_SPECIAL_TAX_ASG_RUN',assact_id);
325 l_lcld := NVL(get_balance_value('LIFE_CYCLE_LEAVE_DISCOUNT_ASG_RUN',assact_id),0);
326 l_std_tax_deduction_value := NVL(get_balance_value('STANDARD_TAX_DEDUCTION_ASG_RUN',assact_id),0)
327 + NVL(get_balance_value('STANDARD_TAX_CORRECTION_ASG_RUN',assact_id),0) - l_lcld;
328 l_spl_tax_deduction_value := NVL(get_balance_value('SPECIAL_TAX_DEDUCTION_ASG_RUN',assact_id),0)
329 + NVL(get_balance_value('SPECIAL_TAX_CORRECTION_ASG_RUN',assact_id),0);
330 l_labour_tax_deduction_value := get_balance_value('LABOUR_TAX_REDUCTION_ASG_RUN',assact_id);
331 l_zvw_base_std := get_balance_value('ZVW_INCOME_STANDARD_TAX_ASG_RUN',assact_id);
332 l_zvw_base_spl := get_balance_value('ZVW_INCOME_SPECIAL_TAX_ASG_RUN',assact_id);
333 l_presi_pre_tax_ded_value := get_balance_value('PRE_TAX_DEDUCTIONS_ASG_RUN',assact_id)
334 - get_balance_value('WAGE_DEDUCTION_STANDARD_TAX_SI_ASG_RUN',assact_id)
335 - get_balance_value('WAGE_DEDUCTION_SPECIAL_TAX_SI_ASG_RUN',assact_id);
336 l_pre_si_and_pre_tax_value := NVL(l_presi_pre_tax_ded_value,0) - NVL(l_pre_tax_deductions_value,0);
337 l_wage_in_money_tax_si_value := NVL(l_wage_in_money_tax_si_value,0) - NVL(l_foreigner_rule_si_std_value,0);
338 l_wage_in_money_spl_tax_value := NVL(l_wage_in_money_spl_tax_value,0) - NVL(l_foreigner_rule_si_spl_value,0);
339
340 IF l_context_si_id IS NOT NULL THEN
341 l_real_si_days := get_balance_value('REAL_SOCIAL_INSURANCE_DAYS_ASG_SIT_RUN',assact_id,l_context_si_id,l_context_value);
342 ELSE
343 l_real_si_days := 0;
344 END IF;
345
346 l_ee_si_cont_std_tax_val_zfw := get_balance_value('EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_SIT_RUN',assact_id,l_context_id,'ZVWL');
347 l_ee_si_cont_spl_tax_val_zfw := get_balance_value('EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_SIT_RUN',assact_id,l_context_id,'ZVWL');
348 l_net_ee_si_cont_value_zfw := get_balance_value('NET_EMPLOYEE_SI_CONTRIBUTION_ASG_SIT_RUN',assact_id,l_context_id,'ZVWL');
349
350
351 l_std_ded_zfw := NVL(l_ee_si_cont_std_tax_val_zfw
352 ,0) + NVL(l_net_ee_si_cont_value_zfw
353 ,0);
354
355 l_spl_tax_income := get_balance_value('SPECIAL_TAXABLE_INCOME_ASG_RUN',assact_id);
356 l_std_tax_income := get_balance_value('STANDARD_TAXABLE_INCOME_ASG_RUN',assact_id);
357
358 /***************************************************
359 End of fetching Balance Values
360 ****************************************************/
361
362 l_total_pay := (l_wage_in_money_tax_si_value + l_wage_in_money_spl_tax_value)
363 - (l_pre_si_and_pre_tax_value)
364 - (l_std_tax_deduction_value + l_spl_tax_deduction_value)
365 - (l_std_ded_zfw + l_ee_si_cont_spl_tax_val_zfw)
366 + l_zvw_refund;
367
368 IF ( NVL(l_real_si_days,0)
369 +NVL(l_wage_in_money_tax_si_value,0)
370 +NVL(l_wage_in_kind_std_tax_value,0)
371 +NVL(l_tips_fund_tax_std_si_value,0)
372 +NVL(l_si_income_std_tax_value,0)
373 +NVL(l_std_tax_deduction_value,0)
374 +NVL(l_labour_tax_deduction_value,0)
375 +NVL(l_std_tax_income,0)
376 +NVL(l_std_ded_zfw,0)) = 0 THEN
377 IF (NVL(l_pre_si_and_pre_tax_value,0)<> 0) OR (NVL(l_total_pay,0) <> 0) THEN
378 l_spl_pre_tax := l_pre_si_and_pre_tax_value;
379 l_spl_total_pay := l_total_pay;
380 ELSE
381 l_spl_pre_tax := 0;
382 l_spl_total_pay := 0;
383 END IF;
384
385 l_non_std_format := 'FALSE';
386 ELSE
387 l_spl_pre_tax := 0;
388 l_spl_total_pay := 0;
389 l_non_std_format := 'TRUE';
390 END IF;
391
392 IF (NVL(l_wage_in_money_spl_tax_value,0)
393 +NVL(l_wage_in_kind_spl_tax_value,0)
394 +NVL(l_tips_fund_tax_spl_si_value,0)
395 +NVL(l_si_income_spl_tax_value,0)
396 +NVL(l_spl_tax_deduction_value,0)
397 +NVL(l_spl_tax_income,0)
398 +NVL(l_ee_si_cont_spl_tax_val_zfw,0)
399 +NVL(l_spl_pre_tax,0)
400 +NVL(l_spl_total_pay,0)) = 0 THEN
401 l_non_spl_format := 'FALSE';
402 ELSE
403 l_non_spl_format := 'TRUE';
404 END IF;
405
406 /************************************************************
407 Final Column Values
408 ************************************************************/
409 IF l_non_std_format = 'TRUE' OR l_non_spl_format = 'TRUE' THEN
410 p_emp_payroll_details.column3 := l_wage_in_money_tax_si_value+l_wage_in_money_spl_tax_value;
411 p_emp_payroll_details.column4 := l_wage_in_kind_std_tax_value+l_wage_in_kind_spl_tax_value;
412 p_emp_payroll_details.column5 := l_tips_fund_tax_std_si_value+l_tips_fund_tax_spl_si_value;
413 p_emp_payroll_details.column7 := l_pre_si_and_pre_tax_value+l_spl_pre_tax;
414 p_emp_payroll_details.column8 := l_si_income_std_tax_value+l_si_income_spl_tax_value;
415 p_emp_payroll_details.column12 := l_zvw_base_std+l_zvw_base_spl;
416 p_emp_payroll_details.column14 := l_std_tax_income+l_spl_tax_income;
417 p_emp_payroll_details.column15 := l_std_tax_deduction_value+l_spl_tax_deduction_value;
418 p_emp_payroll_details.column16 := l_std_ded_zfw+l_ee_si_cont_spl_tax_val_zfw;
419 p_emp_payroll_details.column17 := l_total_pay+l_spl_total_pay;
420 p_emp_payroll_details.column18 := l_labour_tax_deduction_value;
421 p_emp_payroll_details.column19 := l_lcld;
422 p_emp_payroll_details.values_present := 'Y';
423 ELSE
424 p_emp_payroll_details.values_present := 'N';
425 END IF;
426 RETURN 1;
427 EXCEPTION
428 WHEN OTHERS THEN
429 fnd_file.put_line(fnd_file.output,'Exception in get_payroll_run_details SQL-ERRM : '||SQLERRM);
430 RAISE;
431 RETURN 0;
432 END get_payroll_run_details;
433
434 -- =============================================================================
435 -- get_retro_details - Function to get Retro Details of Employee
436 -- =============================================================================
437 FUNCTION get_retro_details(assact_id IN NUMBER
438 ,asg_id IN NUMBER
439 ,retro_period IN DATE
440 ,p_end_of_year VARCHAR2
441 ,p_reporting_date DATE
442 ,p_emp_retro_details IN OUT NOCOPY emp_payroll_details_rec)
443 RETURN NUMBER IS
444
445 /*Variables to be manipulated */
446 l_retro_std_format VARCHAR2(10);
447 l_retro_period_display VARCHAR2(70);
448 l_wage_in_money_std_retro NUMBER;
449 l_wage_in_kind_std_retro NUMBER;
450 l_tips_fund_tax_std_retro NUMBER;
451 l_retro_pre_si NUMBER:= 0;
452 l_retro_si_income_std_tax NUMBER;
453 l_zvw_base_std_r NUMBER;
454 l_std_tax_income_retro NUMBER;
455 l_retro_std_tax_ded NUMBER;
456 l_retro_ded_zfw NUMBER;
457 l_total_pay_r NUMBER;
458 l_retro_labour_tax_ded NUMBER;
459 l_lcld_r NUMBER;
460 l_retro_spl_format VARCHAR2(20);
461 l_wage_in_money_spl_retro NUMBER;
462 l_wage_in_kind_spl_retro NUMBER;
463 l_tips_fund_tax_spl_retro NUMBER;
464 l_spl_ded_befor_tax NUMBER := 0;
465 l_retro_si_income_spl_tax NUMBER;
466 l_zvw_base_spl_r NUMBER;
467 l_spl_tax_income_retro NUMBER;
468 l_retro_spl_tax_ded_sum NUMBER;
469 l_retro_ee_sic_spl_zfw NUMBER;
470 l_spl_total_pay_r NUMBER;
471
472
473
474
475 l_retro_std_ded_quarter NUMBER;
476 l_context_id NUMBER;
477 l_retro_period DATE;
478 l_zvw_input_value NUMBER;
479 l_std_zvw_correction NUMBER;
480 l_spl_zvw_correction NUMBER;
481 l_zvw_refund_r NUMBER;
482 l_retro_spl_tax_ded NUMBER;
483 l_retro_net_ee_sic_zfw NUMBER;
484 l_retro_ee_sic_zfw NUMBER;
485 l_zvw_correction NUMBER;
486 l_tips_fund_tax_spl_si_retro NUMBER;
487 l_tips_fund_tax_spl_imp_ret NUMBER;
488 l_retro_pre_tax_ded NUMBER;
489 l_zvw_base_spl_r_ID NUMBER;
490 l_zvw_base_std_r_ID NUMBER;
491 l_lcld_r_ID NUMBER;
492
493 --=============================================================================
494 CURSOR cur_get_zvw_input_value(p_element_name IN VARCHAR2) IS
495 SELECT
496 piv.input_value_id
497 FROM
498 pay_element_types_f pet,
499 pay_input_values_f piv
500 WHERE pet.element_name = p_element_name
501 AND pet.element_type_id = piv.element_type_id
502 AND pet.legislation_code = 'nl'
503 AND piv.name = 'pay value';
504 --=============================================================================
505 CURSOR cur_get_deleted_zvw_entry(p_input_value IN NUMBER) IS
506 SELECT
507 NVL(fnd_number.canonical_to_number(prrv.result_value),0)
508 FROM
509 pay_run_results prr,
510 pay_run_result_values prrv,
511 pay_element_entries_f pee
512 WHERE prr.assignment_action_id = assact_id
513 AND prr.run_result_id = prrv.run_result_id
514 AND prrv.input_value_id = p_input_value
515 AND prr.element_entry_id = pee.element_entry_id (+)
516 AND prr.element_entry_id IS NOT NULL
517 AND pee.element_entry_id IS NULL;
518 --=============================================================================
519 CURSOR cur_get_context_bal_id IS
520 SELECT
521 ff.context_id
522 FROM
523 ff_contexts ff
524 WHERE ff.context_name = 'SOURCE_TEXT';
525 --=============================================================================
526 CURSOR cur_get_retro_end_date IS
527 SELECT
528 ptp.end_date,
529 ptp.period_name
530 FROM
531 per_time_periods ptp,
532 per_all_assignments_f paa
533 WHERE paa.assignment_id = asg_id
534 AND ptp.end_date between paa.effective_start_date
535 AND paa.effective_end_date
536 AND paa.payroll_id = ptp.payroll_id
537 AND ptp.start_date = retro_period;
538 --=============================================================================
539 BEGIN
540
541 l_std_zvw_correction := 0;
542 l_spl_zvw_correction := 0;
543
544 OPEN cur_get_context_bal_id;
545 FETCH cur_get_context_bal_id
546 INTO l_context_id;
547 CLOSE cur_get_context_bal_id;
548
549 OPEN cur_get_retro_end_date;
550 FETCH cur_get_retro_end_date
551 INTO l_retro_period,l_retro_period_display;
552 CLOSE cur_get_retro_end_date;
553
554 /****************************************************************
555 Fetch Balance Values for Calculation
556 *****************************************************************/
557 l_zvw_refund_r := NVL(get_balance_value('RETRO_ZVW_REFUND_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0);
558 l_wage_in_money_std_retro := get_balance_value('RETRO_WAGE_IN_MONEY_STANDARD_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
559 l_wage_in_money_spl_retro := get_balance_value('RETRO_WAGE_IN_MONEY_SPECIAL_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
560 l_wage_in_kind_std_retro := get_balance_value('RETRO_WAGE_IN_KIND_STANDARD_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
561 l_wage_in_kind_spl_retro := get_balance_value('RETRO_WAGE_IN_KIND_SPECIAL_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
562 l_tips_fund_tax_std_retro := get_balance_value('RETRO_TIPS_AND_FUND_PAYMENTS_STANDARD_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
563 l_tips_fund_tax_spl_retro := get_balance_value('RETRO_TIPS_AND_FUND_PAYMENTS_SPECIAL_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
564 l_retro_pre_tax_ded := get_balance_value('RETRO_PRE_TAX_ONLY_DEDUCTIONS_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
565 l_retro_pre_si := NVL(get_balance_value('RETRO_PRE_TAX_DEDUCTIONS_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0)
566 - NVL(l_retro_pre_tax_ded,0)
567 - NVL(get_balance_value('RETRO_WAGE_DEDUCTION_STANDARD_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0)
568 - NVL(get_balance_value('RETRO_WAGE_DEDUCTION_SPECIAL_TAX_SI_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period)
569 ,0);
570 l_retro_si_income_std_tax := get_balance_value('RETRO_SI_INCOME_STANDARD_TAX_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
571 l_retro_si_income_spl_tax := get_balance_value('RETRO_SI_INCOME_SPECIAL_TAX_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
572
573 l_std_tax_income_retro := NVL(get_balance_value('RETRO_STANDARD_TAXABLE_INCOME_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0)
574 + NVL(get_balance_value('RETRO_STANDARD_TAXABLE_INCOME_CURRENT_QUARTER_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0);
575 l_spl_tax_income_retro := NVL(get_balance_value('RETRO_SPECIAL_TAXABLE_INCOME_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0);
576 l_retro_std_tax_ded := get_balance_value('RETRO_STANDARD_TAX_DEDUCTION_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
577 l_retro_std_ded_quarter := get_balance_value('RETRO_STANDARD_TAX_DEDUCTION_CURRENT_QUARTER_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
578 l_retro_spl_tax_ded := get_balance_value('RETRO_SPECIAL_TAX_DEDUCTION_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
579 l_lcld_r := NVL(get_balance_value('RETRO_LIFE_CYCLE_LEAVE_DISCOUNT_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period),0);
580
581 l_retro_std_tax_ded := NVL(l_retro_std_tax_ded,0) + NVL(l_retro_std_ded_quarter,0) - l_lcld_r;
582 l_retro_spl_tax_ded_sum := NVL(l_retro_spl_tax_ded,0);
583 l_retro_net_ee_sic_zfw := get_balance_value('RETRO_NET_EMPLOYEE_SI_CONTRIBUTION_ASG_BDATE_SIT_RUN',assact_id,l_context_id,'ZVWL',l_retro_period);
584 l_retro_ee_sic_zfw := get_balance_value('RETRO_EMPLOYEE_SI_CONTRIBUTION_STANDARD_TAX_ASG_BDATE_SIT_RUN',assact_id,l_context_id,'ZVWL',l_retro_period);
585 l_retro_ded_zfw := NVL(l_retro_ee_sic_zfw,0) + NVL(l_retro_net_ee_sic_zfw,0);
586 l_retro_ee_sic_spl_zfw := get_balance_value('RETRO_EMPLOYEE_SI_CONTRIBUTION_SPECIAL_TAX_ASG_BDATE_SIT_RUN',assact_id,l_context_id,'ZVWL',l_retro_period);
587 l_retro_labour_tax_ded := get_balance_value('RETRO_LABOUR_TAX_REDUCTION_ASG_BDATE_RUN',assact_id,NULL,NULL,l_retro_period);
588 l_zvw_base_std_r_ID := pay_nl_general.get_element_type_id('Retro ZVW Income Standard Tax',p_reporting_date);
589 l_zvw_base_spl_r_id := pay_nl_general.get_element_type_id('Retro ZVW Income Special Tax',p_reporting_date);
590
591 l_zvw_base_std_r := NVL(pay_nl_general.get_retro_sum_element(retro_period
592 ,NULL
593 ,l_zvw_base_std_r_ID
594 ,NULL
595 ,p_end_of_year
596 ,assact_id)
597 ,0);
598
599 IF l_zvw_correction <> assact_id THEN
600 OPEN cur_get_zvw_input_value('Retro ZVW Income Standard Tax');
601 FETCH cur_get_zvw_input_value
602 INTO l_zvw_input_value;
603 CLOSE cur_get_zvw_input_value;
604
605 OPEN cur_get_deleted_zvw_entry(l_zvw_input_value);
606 FETCH cur_get_deleted_zvw_entry
607 INTO l_std_zvw_correction;
608 CLOSE cur_get_deleted_zvw_entry;
609
610 l_zvw_base_std_r := l_zvw_base_std_r + l_std_zvw_correction;
611 END IF;
612
613 l_zvw_base_spl_r := NVL(pay_nl_general.get_retro_sum_element(retro_period
614 ,NULL
615 ,l_zvw_base_spl_r_ID
616 ,NULL
617 ,p_end_of_year
618 ,assact_id)
619 ,0);
620 IF l_zvw_correction <> ASSACT_ID THEN
621 OPEN cur_get_zvw_input_value('Retro ZVW Income Special Tax');
622 FETCH cur_get_zvw_input_value
623 INTO l_zvw_input_value;
624 CLOSE cur_get_zvw_input_value;
625 OPEN cur_get_deleted_zvw_entry(l_zvw_input_value);
626 FETCH cur_get_deleted_zvw_entry
627 INTO l_spl_zvw_correction;
628 CLOSE cur_get_deleted_zvw_entry;
629 l_zvw_base_spl_r := l_zvw_base_spl_r + l_spl_zvw_correction;
630 END IF;
631
632 IF (l_std_zvw_correction <> 0) OR (l_spl_zvw_correction <> 0) THEN
633 l_zvw_correction := assact_id;
634 END IF;
635
636 l_total_pay_r := (l_wage_in_money_std_retro + l_wage_in_money_spl_retro)
637 - (l_retro_pre_si)
638 - (l_retro_std_tax_ded + l_retro_spl_tax_ded_sum)
639 - (l_retro_ded_zfw + l_retro_ee_sic_spl_zfw)
640 + l_zvw_refund_r;
641
642 IF (NVL(l_wage_in_money_std_retro,0)
643 +NVL(l_wage_in_kind_std_retro,0)
644 +NVL(l_tips_fund_tax_std_retro,0)
645 +NVL(l_retro_pre_tax_ded,0)
646 +NVL(l_retro_pre_si,0)
647 +NVL(l_retro_si_income_std_tax,0)
648 +NVL(l_std_tax_income_retro,0)
649 +NVL(l_retro_std_tax_ded,0)
650 +NVL(l_retro_ded_zfw,0)
651 +NVL(l_retro_labour_tax_ded,0)) = 0 THEN
652 IF (l_retro_pre_si IS NOT NULL AND l_retro_pre_si <> 0) OR (l_total_pay_r IS NOT NULL AND l_total_pay_r <> 0) THEN
653 l_spl_ded_befor_tax := l_retro_pre_si;
654 l_spl_total_pay_r := l_total_pay_r;
655 ELSE
656 l_spl_ded_befor_tax := 0;
657 l_spl_total_pay_r := 0;
658 END IF;
659 l_retro_std_format := 'FALSE';
660 ELSE
661 IF l_retro_pre_si <> 0 THEN
662 l_spl_ded_befor_tax := 0;
663 END IF;
664 l_spl_total_pay_r := 0;
665 l_retro_std_format := 'TRUE';
666 END IF;
667
668 IF (NVL(l_wage_in_money_spl_retro,0)
669 +NVL(l_wage_in_kind_spl_retro,0)
670 +NVL(l_tips_fund_tax_spl_si_retro,0)
671 +NVL(l_tips_fund_tax_spl_imp_ret,0)
672 +NVL(l_tips_fund_tax_spl_retro,0)
673 +NVL(l_retro_si_income_spl_tax,0)
674 +NVL(l_spl_tax_income_retro,0)
675 +NVL(l_retro_spl_tax_ded_sum,0)
676 +NVL(l_retro_ee_sic_spl_zfw,0)
677 +NVL(l_spl_ded_befor_tax,0)
678 +NVL(l_spl_total_pay_r,0)) = 0 THEN
679 l_retro_spl_format := 'FALSE';
680 ELSE
681 l_retro_spl_format := 'TRUE';
682 END IF;
683
684 l_retro_period_display := 'R' || l_retro_period_display;
685
686 /*Add All Values to return */
687 IF l_retro_std_format = 'TRUE' OR l_retro_spl_format = 'TRUE' THEN
688 p_emp_retro_details.column1 := l_retro_period_display;
689 p_emp_retro_details.column3 := l_wage_in_money_std_retro+l_wage_in_money_spl_retro;
690 p_emp_retro_details.column4 := l_wage_in_kind_std_retro+l_wage_in_kind_spl_retro;
691 p_emp_retro_details.column5 := l_tips_fund_tax_std_retro+l_tips_fund_tax_spl_retro;
692 p_emp_retro_details.column7 := l_retro_pre_si+l_spl_ded_befor_tax;
693 p_emp_retro_details.column8 := l_retro_si_income_std_tax+l_retro_si_income_spl_tax;
694 p_emp_retro_details.column12 := l_zvw_base_std_r+l_zvw_base_spl_r;
695 p_emp_retro_details.column14 := l_std_tax_income_retro+l_spl_tax_income_retro;
696 p_emp_retro_details.column15 := l_retro_std_tax_ded+l_retro_spl_tax_ded_sum;
697 p_emp_retro_details.column16 := l_retro_ded_zfw+l_retro_ee_sic_spl_zfw;
698 p_emp_retro_details.column17 := l_total_pay_r+l_spl_total_pay_r;
699 p_emp_retro_details.column18 := l_retro_labour_tax_ded;
700 p_emp_retro_details.column19 := l_lcld_r;
701 p_emp_retro_details.values_present := 'Y';
702 ELSE
703 p_emp_retro_details.values_present := 'N';
704 END IF;
705
706 RETURN 1;
707 EXCEPTION
708 WHEN OTHERS THEN
709 fnd_file.put_line(fnd_file.output,'Exception in get_retro_details SQL-ERRM : '||SQLERRM);
710 RAISE;
711 RETURN 0;
712 END get_retro_details;
713
714 -- =============================================================================
715 -- generate - XML generation at File level
716 -- =============================================================================
717 PROCEDURE generate( p_business_group_id NUMBER
718 ,p_pay_period VARCHAR2 DEFAULT NULL
719 ,p_dummy VARCHAR2
720 ,p_end_of_year VARCHAR2
721 ,p_org_struct_id VARCHAR2
722 ,p_org_struct_name VARCHAR2
723 ,p_top_org_id NUMBER DEFAULT NULL
724 ,p_dummy_emp VARCHAR2
725 ,p_sub_emp VARCHAR2
726 ,p_top_org_name VARCHAR2 DEFAULT NULL
727 ,p_person_id NUMBER DEFAULT NULL
728 ,p_employee_name VARCHAR2 DEFAULT NULL
729 ,p_effective_date VARCHAR2
730 ,p_template_name VARCHAR2
731 ,p_xml OUT NOCOPY CLOB)
732 IS
733
734 l_xml CLOB;
735 l_start_date DATE;
736 l_valid BOOLEAN;
737 l_org_struct_version_id per_org_structure_versions.org_structure_version_id%TYPE;
738 l_reporting_date DATE;
739 l_reporting_start_date DATE;
740 l_temp_date_to DATE;
741 l_emp_count NUMBER := 0;
742 l_total_emp_count NUMBER := 0;
743 l_employer_count NUMBER := 0;
744 l_emp_count1 NUMBER := 0;
745 l_wage_table_status VARCHAR2(20) := 'A';
746 l_previous VARCHAR2(20) := 'N';
747 l_single_employee VARCHAR2(20) := 'N';
748 l_single_employer VARCHAR2(20) := 'Y';
749
750 /*******************************************************
751 Variables to Store Organization Details
752 ********************************************************/
753 l_tax_ref_no hr_organization_information.org_information1%TYPE;
754 l_hr_tax_unit hr_all_organization_units.organization_id%TYPE;
755 l_tax_reg_no hr_organization_information.org_information4%TYPE;
756 l_tax_rep_name hr_organization_information.org_information3%TYPE;
757 l_tax_reg_no1 VARCHAR2(14);
758 l_tax_org_id NUMBER;
759 l_bg_name VARCHAR2(100);
760 l_org_address VARCHAR2(3000);
761
762
763 /********************************************************
764 Variables to Store Employee Details
765 ********************************************************/
766 l_ee_address VARCHAR2(3000);
767 l_ee_house_number VARCHAR2(50);
768 l_ee_house_number_add VARCHAR2(100);
769 l_ee_street_name VARCHAR2(100);
770 l_ee_address_line1 VARCHAR2(100);
771 l_ee_address_line2 VARCHAR2(100);
772 l_ee_address_line3 VARCHAR2(100);
773 l_ee_postal_code VARCHAR2(20);
774 l_ee_country VARCHAR2(100);
775 l_ee_city VARCHAR2(100);
776
777
778 /*********************************************************
779 Record Type to hold Employee Totals
780 *********************************************************/
781 TYPE TOTALS_REC IS RECORD (
782 column3 NUMBER :=0.00,
783 column4 NUMBER :=0.00,
784 column5 NUMBER :=0.00,
785 column6 NUMBER :=0.00,
786 column7 NUMBER :=0.00,
787 column8 NUMBER :=0.00,
788 column12 NUMBER :=0.00,
789 column14 NUMBER :=0.00,
790 column15 NUMBER :=0.00,
791 column16 NUMBER :=0.00,
792 column17 NUMBER :=0.00,
793 column18 NUMBER :=0.00,
794 column19 NUMBER :=0.00
795 );
796
797 /**********************************************************
798 Create a variable of record type to display values
799 **********************************************************/
800 emp_totals totals_rec;
801 org_totals totals_rec;
802 rep_totals totals_rec;
803 reset_values totals_rec;
804 emp_payroll_details emp_payroll_details_rec;
805 emp_retro_details emp_payroll_details_rec;
806 reset_payroll_details emp_payroll_details_rec;
807 reset_retro_details emp_payroll_details_rec;
808
809
810 --=============================================================================
811 /***********************************************************
812 Cursor to get Business Group Name
813 ***********************************************************/
814 CURSOR cur_get_bg_name IS
815 SELECT hrou.name
816 FROM hr_all_organization_units hrou
817 WHERE hrou.organization_id = p_business_group_id;
818 --=============================================================================
819 /***********************************************************
820 Cursor to fetch employer address
821 ***********************************************************/
822 CURSOR cur_employer_address IS
823 SELECT hlc.loc_information14 house_number
824 ,hlc.loc_information15 house_number_add
825 ,hlc.address_line_1 address_1
826 ,hlc.address_line_2 address_2
827 ,hlc.address_line_3 address_3
828 ,hlc.region_1 street_name
829 ,decode (length (hlc.postal_code)
830 ,6
831 ,concat (substr (hlc.postal_code
832 ,1
833 ,4)
834 ,concat (' '
835 ,substr (hlc.postal_code
836 ,5
837 ,2)))
838 ,hlc.postal_code) postcode
839 ,hr_general.decode_lookup ('HR_NL_CITY'
840 ,hlc.town_or_city) city
841 ,hlc.country country
842 FROM hr_locations hlc
843 ,hr_organization_units hou
844 WHERE hou.business_group_id = p_business_group_id
845 AND hou.organization_id = l_tax_org_id
846 AND hlc.location_id = hou.location_id;
847 --=============================================================================
848 CURSOR cur_emp_address(c_person_id per_all_people_f.person_id%TYPE) IS
849 SELECT pad.add_information13 house_number
850 ,pad.add_information14 house_no_add
851 ,pad.region_1 street_name
852 ,pad.address_line1 address_line1
853 ,pad.address_line2 address_line2
854 ,pad.address_line3 address_line3
855 ,decode (length (pad.postal_code)
856 ,6
857 ,concat (substr (pad.postal_code
858 ,1
859 ,4)
860 ,concat (' '
861 ,substr (pad.postal_code
862 ,5
863 ,2)))
864 ,pad.postal_code) postcode
865 ,hr_general.decode_lookup ('HR_NL_CITY'
866 ,pad.town_or_city) city
867 ,pad.country country
868 FROM per_addresses pad
869 WHERE pad.person_id = c_person_id
870 AND l_reporting_date BETWEEN date_from
871 AND nvl (date_to
872 ,hr_general.end_of_time)
873 AND pad.primary_flag = 'Y';
874 --=============================================================================
875 /***********************************************************
876 Cursor to get Organization Structure Version id
877 ***********************************************************/
878 CURSOR get_org_struct_version_id(c_org_struct_id IN NUMBER) IS
879 SELECT org_structure_version_id
880 FROM per_org_structure_versions posv
881 WHERE organization_structure_id = c_org_struct_id
882 AND l_reporting_date BETWEEN posv.date_from
883 AND nvl (posv.date_to
884 ,hr_general.end_of_time);
885 --=============================================================================
886
887 /***********************************************************
888 Cursor to get Employer Details
889 ***********************************************************/
890 CURSOR cur_employer_details IS
891 SELECT DISTINCT
892 hou1.name employer_name
893 ,hou1.organization_id tax_org_id
894 FROM hr_all_organization_units hou1
895 WHERE (
896 nvl (p_sub_emp
897 ,'Y') = 'N'
898 AND hou1.organization_id = nvl (p_top_org_id
899 ,p_business_group_id)
900 )
901 OR (
902 nvl (p_sub_emp
903 ,'Y') = 'Y'
904 AND EXISTS
905 (
906 SELECT hou1.organization_id
907 FROM per_org_structure_elements pose
908 WHERE (
909 pose.organization_id_child = hou1.organization_id
910 OR pose.organization_id_parent = hou1.organization_id
911 )
912 AND pose.org_structure_version_id = l_org_struct_version_id
913 )
914 AND hou1.organization_id IN
915 (
916 (
917 SELECT nvl (p_top_org_id
918 ,p_business_group_id)
919 FROM dual
920 )
921 UNION
922 (
923 SELECT (p_business_group_id)
924 FROM dual
925 )
926 UNION
927 (
928 SELECT e.organization_id
929 FROM hr_organization_information e
930 WHERE e.organization_id IN
931 (
932 SELECT pose.organization_id_child employer
933 FROM per_org_structure_elements pose
934 WHERE pose.org_structure_version_id = l_org_struct_version_id
935 START WITH pose.organization_id_parent = nvl (p_top_org_id
936 ,p_business_group_id)
937 CONNECT BY NOCYCLE PRIOR pose.organization_id_child = pose.organization_id_parent
938 )
939 AND (
940 (
941 e.org_information_context = 'NL_ORG_INFORMATION'
942 AND e.org_information3 IS NOT NULL
943 AND e.org_information4 IS NOT NULL
944 )
945 OR (
946 e.org_information_context = 'NL_LE_TAX_DETAILS'
947 AND e.org_information1 IS NOT NULL
948 AND e.org_information2 IS NOT NULL
949 )
950 )
951 )
952 )
953 )
954 ;
955 --=============================================================================
956 /***********************************************************
957 CURSOR to fetch NUM IV override
958 ***********************************************************/
959 CURSOR cur_numiv_override(c_asg_id NUMBER) is
960 SELECT aei_information1 numiv_override
961 FROM per_assignment_extra_info
962 WHERE assignment_id = c_asg_id
963 AND aei_information_category = 'NL_NUMIV_OVERRIDE';
964 --=============================================================================
965 CURSOR csr_le_hr_mapping_chk IS
966 SELECT hoi.org_information1 tax_ref_no
967 ,hoi.org_information2 org_id
968 ,hoi.org_information3 tax_rep_name
969 FROM hr_organization_information hoi
970 ,hr_organization_information hoi1
971 WHERE hoi.org_information_context = 'NL_LE_TAX_DETAILS'
972 AND hoi.organization_id = hoi1.organization_id
973 AND hoi1.organization_id = l_tax_org_id
974 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
975 AND hoi1.org_information_context = 'CLASS';
976 --=============================================================================
977 /***********************************************************
978 Cursor to fetch Tax Registration Number
979 ***********************************************************/
980 CURSOR csr_tax_reg_no(c_employer_id IN NUMBER,
981 c_business_group_id IN NUMBER) IS
982 SELECT hoi.org_information4 tax_reg_number
983 FROM hr_organization_units hou
984 ,hr_organization_information hoi
985 WHERE hoi.org_information_context = 'NL_ORG_INFORMATION'
986 AND hou.business_group_id = c_business_group_id
987 AND hou.organization_id = hoi.organization_id
988 AND hou.organization_id = c_employer_id;
989 --=============================================================================
990 /***********************************************************
991 Cursor to fetch Person Details
992 ***********************************************************/
993 CURSOR csr_person_details(c_tax_org_id NUMBER) IS
994 SELECT DISTINCT
995 paa.person_id person_id
996 ,paa.assignment_number assignmentno
997 ,paa.assignment_id assignmentid
998 ,papf.last_name
999 || ' '
1000 || papf.pre_name_adjunct
1001 || ' '
1002 || papf.per_information1 employee_name
1003 ,papf.national_identifier sofi
1004 ,papf.date_of_birth dateofbirth
1005 ,paa.assignment_sequence assignment_sequence
1006 FROM pay_payroll_actions ppa
1007 ,pay_assignment_actions asg_act
1008 ,per_assignments_f paa
1009 ,pay_all_payrolls_f ppf
1010 ,per_periods_of_service pps
1011 ,hr_all_organization_units hou1
1012 ,per_all_people_f papf
1013 WHERE ppa.business_group_id = p_business_group_id
1014 AND ppa.action_type IN ('R','B','I'
1015 ,'Q','V')
1016 AND ppa.action_status = 'C'
1017 AND ppa.payroll_action_id = asg_act.payroll_action_id
1018 AND asg_act.action_status = 'C'
1019 AND ppa.effective_date BETWEEN l_reporting_start_date
1020 AND l_reporting_date
1021 AND ppa.effective_date BETWEEN paa.effective_start_date
1022 AND paa.effective_end_date
1023 AND ppa.effective_date BETWEEN ppf.effective_start_date
1024 AND ppf.effective_end_date
1025 AND paa.assignment_id = asg_act.assignment_id
1026 AND pps.person_id = paa.person_id
1027 AND pps.period_of_service_id = paa.period_of_service_id
1028 AND paa.business_group_id = ppa.business_group_id
1029 AND paa.payroll_id = ppf.payroll_id
1030 AND ppf.prl_information_category = 'NL'
1031 AND hou1.organization_id = nvl (paa.establishment_id
1032 ,ppf.prl_information1)
1033 AND l_reporting_date BETWEEN ppf.effective_start_date
1034 AND ppf.effective_end_date
1035 AND papf.person_id = paa.person_id
1036 AND ( (NVL(paa.establishment_id,ppf.prl_information1) = c_tax_org_id) OR
1037 NVL(paa.establishment_id,ppf.prl_information1) IN
1038 ( SELECT DISTINCT
1039 pose1.organization_id_child employer
1040 FROM per_org_structure_elements pose1
1041 WHERE pose1.org_structure_version_id = l_org_struct_version_id
1042 AND pose1.organization_id_parent = c_tax_org_id
1043 AND pose1.organization_id_child NOT IN
1044 (
1045 SELECT DISTINCT e1.organization_id
1046 FROM hr_organization_information e1
1047 WHERE e1.organization_id = pose1.organization_id_child
1048 AND (
1049 (
1050 e1.org_information_context = 'NL_ORG_INFORMATION'
1051 AND e1.org_information3 IS NOT NULL
1052 AND e1.org_information4 IS NOT NULL
1053 )
1054 OR (
1055 e1.org_information_context = 'NL_LE_TAX_DETAILS'
1056 AND e1.org_information1 IS NOT NULL
1057 AND e1.org_information2 IS NOT NULL
1058 )
1059 )
1060 )
1061 )) AND
1062 paa.person_id=nvl(p_person_id,paa.person_id);
1063 --=============================================================================
1064 /***********************************************************
1065 To get Labour Handicap Discount
1066 ***********************************************************/
1067 CURSOR cur_get_lhd_details(c_assignment_id per_all_assignments_f.assignment_id%TYPE) IS
1068 SELECT paei.aei_information3 lhd_type
1069 ,fnd_date.canonical_to_date (paei.aei_information1) date_from
1070 ,fnd_date.canonical_to_date (paei.aei_information2) date_to
1071 FROM per_assignment_extra_info paei
1072 WHERE paei.aei_information_category = 'NL_LHI'
1073 AND fnd_date.canonical_to_date (aei_information1) < l_reporting_date
1074 AND NVL(fnd_date.canonical_to_date (aei_information2),hr_general.end_of_time) > l_reporting_start_date
1075 AND paei.assignment_id = c_assignment_id
1076 AND paei.aei_information3 IN ('1','2','3','4')
1077 ORDER BY fnd_date.canonical_to_date (paei.aei_information1);
1078 --=============================================================================
1079 /***********************************************************
1080 To get Temporary Tax Discount
1081 ***********************************************************/
1082 CURSOR cur_get_ttd_details(c_assignment_id per_all_assignments_f.assignment_id%TYPE) IS
1083 SELECT paei.aei_information3 ttd_type
1084 ,FND_DATE.CANONICAL_TO_DATE(paei.aei_information1) date_from
1085 ,FND_DATE.CANONICAL_TO_DATE(paei.aei_information2) date_to
1086 FROM per_assignment_extra_info paei
1087 WHERE paei.aei_information_category = 'NL_TTD'
1088 AND FND_DATE.CANONICAL_TO_DATE(aei_information1) < l_reporting_date
1089 AND NVL(fnd_date.canonical_to_date (aei_information2),hr_general.end_of_time) > l_reporting_start_date
1090 AND paei.assignment_id = c_assignment_id
1091 AND paei.aei_information3 = '2'
1092 ORDER BY paei.aei_information1;
1093 --=============================================================================
1094 CURSOR cur_wage_table_det(c_assignment_id per_all_assignments_f.assignment_id%TYPE) IS
1095 SELECT DISTINCT
1096 paa2.assignment_id assignmentid
1097 ,paa2.effective_start_date effectivefrom
1098 ,paa2.effective_end_date effectiveto
1099 ,sck2.segment4 wagetaxdiscount
1100 FROM per_all_assignments_f paa1
1101 ,per_all_assignments_f paa2
1102 ,hr_soft_coding_keyflex sck1
1103 ,hr_soft_coding_keyflex sck2
1104 WHERE l_reporting_date > paa2.effective_start_date
1105 AND (
1106 (
1107 paa2.effective_start_date =
1108 (
1109 SELECT min (effective_start_date)
1110 FROM per_all_assignments_f
1111 WHERE assignment_id = paa2.assignment_id
1112 )
1113 )
1114 OR (
1115 paa2.effective_start_date - paa1.effective_end_date = 1
1116 AND (
1117 sck2.segment4 <> sck1.segment4
1118 OR sck2.segment9 <> sck1.segment9
1119 )
1120 )
1121 )
1122 AND paa2.effective_end_date > l_reporting_start_date
1123 AND paa2.assignment_id = paa1.assignment_id
1124 AND paa1.soft_coding_keyflex_id = sck1.soft_coding_keyflex_id
1125 AND paa2.soft_coding_keyflex_id = sck2.soft_coding_keyflex_id
1126 AND paa2.assignment_id = c_assignment_id
1127 AND sck2.segment4 IS NOT NULL
1128 ORDER BY effectivefrom;
1129 --=============================================================================
1130 CURSOR cur_pay_period(c_assignment_id per_all_assignments_f.assignment_id%TYPE) IS
1131 SELECT ppa.payroll_action_id pact_id
1132 ,ppa.effective_date eff_date
1133 ,paa.assignment_action_id assact_id
1134 ,paa.assignment_id asg_id
1135 ,concat(concat(ptp.period_num,' '),TO_CHAR(ptp.START_DATE,'YYYY')) period_name
1136 ,ptp.start_date start_date
1137 FROM pay_payroll_actions ppa
1138 ,pay_assignment_actions paa
1139 ,per_time_periods ptp
1140 WHERE paa.payroll_action_id = ppa.payroll_action_id
1141 AND paa.action_status IN ('C','S')
1142 AND ppa.effective_date BETWEEN l_reporting_start_date
1143 AND l_reporting_date
1144 AND (
1145 (
1146 ppa.action_type IN ('R','B','I'
1147 ,'Q')
1148 AND paa.source_action_id IS NOT NULL
1149 )
1150 OR ppa.action_type = 'V'
1151 )
1152 AND paa.assignment_id = c_assignment_id
1153 AND ptp.payroll_id = ppa.payroll_id
1154 AND ppa.date_earned between ptp.start_date and ptp.end_date
1155 ORDER BY period_name
1156 ,pact_id;
1157 --=============================================================================
1158 CURSOR cur_retro_period_det(c_assignment_id per_all_assignments_f.assignment_id%TYPE,
1159 c_pact_id pay_payroll_actions.payroll_action_id%TYPE,
1160 c_assact_id pay_assignment_actions.assignment_action_id%TYPE
1161 ) IS
1162 SELECT ptp.start_date retro_period
1163 ,ppa.payroll_action_id pact_id
1164 ,ppa.effective_date eff_date
1165 ,paa.assignment_action_id assact_id
1166 ,paa.assignment_id asg_id
1167 ,concat(concat('R '||ptp.period_num,' '),TO_CHAR(ptp.START_DATE,'YYYY')) period_name
1168 FROM pay_payroll_actions ppa
1169 ,pay_assignment_actions paa
1170 ,pay_run_results prr
1171 ,per_time_periods ptp
1172 WHERE paa.payroll_action_id = ppa.payroll_action_id
1173 AND paa.source_action_id IS NOT NULL
1174 AND paa.action_status IN ('C','S')
1175 AND prr.assignment_action_id = paa.assignment_action_id
1176 AND ptp.start_date IS NOT NULL
1177 AND ppa.payroll_action_id = c_pact_id
1178 AND ppa.action_type IN ('R','B','I'
1179 ,'Q','V')
1180 AND ppa.effective_date BETWEEN l_reporting_start_date
1181 AND l_reporting_date
1182 AND ppa.payroll_id = ptp.payroll_id
1183 AND prr.start_date BETWEEN ptp.start_date
1184 AND ptp.end_date
1185 AND paa.assignment_action_id IN
1186 (
1187 SELECT paa1.assignment_action_id
1188 FROM pay_assignment_actions paa1
1189 WHERE paa1.payroll_action_id = ppa.payroll_action_id
1190 AND paa1.assignment_id = paa.assignment_id
1191 )
1192 AND paa.assignment_id = c_assignment_id
1193 AND ppa.payroll_action_id = c_pact_id
1194 AND paa.assignment_action_id = c_assact_id
1195 GROUP BY ptp.start_date
1196 ,ptp.period_num
1197 ,ppa.payroll_action_id
1198 ,ppa.effective_date
1199 ,paa.assignment_action_id
1200 ,paa.assignment_id
1201 ORDER BY retro_period;
1202 --=============================================================================
1203
1204
1205 r_employer_details cur_employer_details%ROWTYPE;
1206
1207 /*Variables to store employee details */
1208 r_person_details csr_person_details%ROWTYPE;
1209 r_employer_address cur_employer_address%ROWTYPE;
1210 r_emp_address cur_emp_address%ROWTYPE;
1211
1212 /*Tax Tables */
1213 r_wage_table_det cur_wage_table_det%ROWTYPE;
1214 r_pay_period cur_pay_period%ROWTYPE;
1215 r_retro_period_det cur_retro_period_det%ROWTYPE;
1216 r_get_lhd_details cur_get_lhd_details%ROWTYPE;
1217 r_get_ttd_details cur_get_ttd_details%ROWTYPE;
1218 y NUMBER;
1219 l_numiv_override per_all_assignments_f.assignment_number%TYPE;
1220 l_emp_seq per_all_assignments_f.assignment_number%TYPE;
1221
1222 BEGIN
1223
1224 /***********************************************************
1225 Get Business Group Name
1226 ***********************************************************/
1227 OPEN cur_get_bg_name;
1228 FETCH cur_get_bg_name INTO l_bg_name;
1229 CLOSE cur_get_bg_name;
1230
1231 /*Set Reporting Dates based on pay period */
1232 IF (p_pay_period) IS NULL THEN
1233 l_reporting_date := fnd_date.canonical_to_date(p_end_of_year);
1234 l_reporting_start_date := TRUNC(fnd_date.canonical_to_date(p_end_of_year),'Y');
1235 ELSE
1236 l_reporting_date := LAST_DAY(fnd_date.canonical_to_date(p_pay_period));
1237 l_reporting_start_date := TRUNC(fnd_date.canonical_to_date(p_pay_period),'MM');
1238 END IF;
1239
1240 /*Fecth Org Struct Version ID */
1241 OPEN get_org_struct_version_id(p_org_struct_id);
1242 FETCH get_org_struct_version_id
1243 INTO l_org_struct_version_id;
1244 CLOSE get_org_struct_version_id;
1245
1246 /*****************************************************************************
1247 Start of XML
1248 ******************************************************************************/
1249 Tag('DUTCH_REM','_START_');
1250
1251 /*Display Parameters */
1252 Tag('BG_NAME',l_bg_name);
1253 Tag('EFFECTIVE_DATE',fnd_date.date_to_displaydate(fnd_date.canonical_to_date(p_effective_date)));
1254 Tag('P_PAYROLL_PERIOD',p_pay_period);
1255 Tag('P_YEAR',to_char(fnd_date.canonical_to_date(p_end_of_year),'YYYY'));
1256 Tag('P_ORG_STRUCT_NAME',p_org_struct_name);
1257 Tag('P_TOP_ORG_NAME',p_top_org_name);
1258 Tag('P_SUB_EMP',get_lookup_value('HR_NL_YES_NO',p_sub_emp,userenv('lang')));
1259 Tag('P_EMPLOYEE_NAME',p_employee_name);
1260
1261 /*Reset Report Totals to zero */
1262 rep_totals := reset_values;
1263
1264
1265 /*Fetch Organisation details */
1266 OPEN cur_employer_details;
1267 <<Employer>>
1268 LOOP
1269 FETCH cur_employer_details INTO r_employer_details;
1270 EXIT WHEN cur_employer_details%NOTFOUND;
1271
1272 /*Reset Organization Values to 0*/
1273 org_totals := reset_values;
1274
1275 l_emp_count := 0;
1276 l_tax_org_id := r_employer_details.tax_org_id;
1277
1278
1279 /*Fetch Employee Details for Employer */
1280 OPEN csr_person_details(l_tax_org_id);
1281 <<Employee>>
1282 LOOP
1283 FETCH csr_person_details INTO r_person_details;
1284 EXIT WHEN csr_person_details%NOTFOUND;
1285
1286 l_emp_count := l_emp_count+1;
1287 l_total_emp_count := l_total_emp_count+1;
1288
1289 /*Display Org details only if any employees exist in it */
1290 IF l_emp_count = 1 THEN
1291 Tag('EMPLOYER','_START_');
1292 Tag('EMPLOYER_NAME',r_employer_details.employer_name);
1293 END IF;
1294
1295 /*Reset All Totals to zero */
1296 EMP_TOTALS := reset_values;
1297
1298 /*Employee Details Starts here for Payroll */
1299 Tag('EMPLOYEE','_START_');
1300 Tag('EMPLOYEE_NAME',r_person_details.EMPLOYEE_NAME);
1301 Tag('SOFI',r_person_details.SOFI);
1302
1303
1304 /*Reset the Values */
1305 l_ee_house_number := '';
1306 l_ee_house_number_add := '';
1307 l_ee_street_name := '';
1308 l_ee_address_line1 := '';
1309 l_ee_address_line2 := '';
1310 l_ee_address_line3 := '';
1311 l_ee_city := '';
1312 l_ee_country := '';
1313 l_ee_postal_code := '';
1314 l_ee_address := '';
1315
1316 /*Fetch Employee Address */
1317 OPEN cur_emp_address(r_person_details.person_id);
1318 FETCH cur_emp_address INTO r_emp_address;
1319
1320 IF cur_emp_address%FOUND THEN
1321 IF (r_emp_address.street_name) IS NOT NULL THEN
1322 l_ee_address := l_ee_address || r_emp_address.street_name;
1323 END IF;
1324 IF (r_emp_address.house_number) IS NOT NULL THEN
1325 l_ee_address := l_ee_address || ', ' || r_emp_address.house_number;
1326 END IF;
1327 IF (r_emp_address.house_nO_add) IS NOT NULL THEN
1328 l_ee_address := l_ee_address || ', ' || r_emp_address.house_nO_add;
1329 END IF;
1330 IF (r_emp_address.address_line1) IS NOT NULL THEN
1331 l_ee_address := l_ee_address || ', ' || r_emp_address.address_line1;
1332 END IF;
1333 IF (r_emp_address.address_line2) IS NOT NULL THEN
1334 l_ee_address := l_ee_address || ', ' || r_emp_address.address_line2;
1335 END IF;
1336 IF (r_emp_address.address_line3) IS NOT NULL THEN
1337 l_ee_address := l_ee_address || ', ' || r_emp_address.address_line3;
1338 END IF;
1339
1340 Tag('EMPLOYEE_ADDRESS',l_ee_address);
1341 Tag('EMP_POSTAL_CODE',r_emp_address.postcode);
1342 Tag('EMP_CITY',r_emp_address.city);
1343 Tag('EMP_COUNTRY',r_emp_address.country);
1344 END IF;
1345 Tag('DOB',r_person_details.dateofbirth);
1346 Tag('ASSIGNMENT_NO',r_person_details.assignmentno);
1347 CLOSE cur_emp_address;
1348
1349
1350 /*NUMIV manipulation */
1351 l_emp_seq := NULL;
1352 l_emp_seq := r_person_details.assignment_sequence;
1353 l_numiv_override := l_emp_seq;
1354
1355 OPEN CUR_numiv_override(r_person_details.assignmentid);
1356 FETCH CUR_numiv_override INTO l_numiv_override;
1357 CLOSE CUR_numiv_override;
1358
1359 Tag('NUMIV',nvl(l_numiv_override,l_emp_seq));
1360
1361 l_emp_count1:=0;
1362 l_wage_table_status :='A';
1363
1364 /*Wage Tax Table details Starts here */
1365 OPEN cur_wage_table_det(r_person_details.assignmentid);
1366 l_temp_date_to := NULL;
1367 LOOP
1368 FETCH cur_wage_table_det INTO r_wage_table_det;
1369
1370 IF cur_wage_table_det%notfound THEN
1371 IF l_temp_date_to<> hr_general.end_of_time AND l_temp_date_to IS NOT NULL AND l_wage_table_status <> 'N' THEN
1372 Tag('WAGE_TABLE','_START_');
1373 Tag('WAGE_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1374 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1375 fnd_date.date_to_chardate(l_temp_date_to+1));
1376 Tag('WAGE_TABLE','_END_');
1377 l_wage_table_status := 'N';
1378 END IF;
1379 IF l_emp_count1=0 THEN
1380 Tag('WAGE_TABLE','_START_');
1381 Tag('WAGE_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang')));
1382 Tag('WAGE_TABLE','_END_');
1383 END IF;
1384 EXIT;
1385 END IF;
1386
1387 IF l_temp_date_to+1<r_wage_table_det.effectivefrom AND l_wage_table_status<>'N' THEN
1388 Tag('WAGE_TABLE','_START_');
1389 Tag('WAGE_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1390 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1391 fnd_date.date_to_chardate(l_temp_date_to+1));
1392 Tag('WAGE_TABLE','_END_');
1393 l_wage_table_status := 'N';
1394 l_emp_count1 := l_emp_count1+1;
1395 END IF;
1396
1397 IF r_wage_table_det.wagetaxdiscount = 'Y' AND l_wage_table_status<>'Y' THEN
1398 l_temp_date_to := NVL(r_wage_table_det.effectiveto,hr_general.end_of_time);
1399 Tag('WAGE_TABLE','_START_');
1400 Tag('WAGE_TAX_DISCOUNT', get_lookup_value('HR_NL_YES_NO','Y',userenv('lang'))||', '||
1401 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1402 fnd_date.date_to_chardate(r_wage_table_det.effectivefrom));
1403 Tag('WAGE_TABLE','_END_');
1404 l_wage_table_status := 'Y';
1405 l_emp_count1 := l_emp_count1+1;
1406 END IF;
1407
1408 IF r_wage_table_det.effectivefrom IS NOT NULL AND r_wage_table_det.wagetaxdiscount='N' AND l_wage_table_status<>'N' AND l_emp_count1>0 THEN
1409 l_temp_date_to := NVL(r_wage_table_det.effectiveto,hr_general.end_of_time);
1410 Tag('WAGE_TABLE','_START_');
1411 Tag('WAGE_TAX_DISCOUNT', get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1412 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1413 fnd_date.date_to_chardate(r_wage_table_det.effectivefrom));
1414 Tag('WAGE_TABLE','_END_');
1415 l_wage_table_status := 'N';
1416 l_emp_count1 := l_emp_count1+1;
1417 END IF;
1418 END LOOP;
1419 CLOSE cur_wage_table_det;
1420 /*Wage Tax Table Details Ends here */
1421
1422 /*Labour Handicap Discount Details for employee */
1423 l_temp_date_to := NULL;
1424 l_previous := 'N';
1425 OPEN cur_get_lhd_details(r_person_details.assignmentid);
1426 LOOP
1427 FETCH cur_get_lhd_details INTO r_get_lhd_details;
1428
1429 IF cur_get_lhd_details%notfound THEN
1430 IF l_temp_date_to<> hr_general.end_of_time AND l_temp_date_to IS NOT NULL THEN
1431 Tag('LHD_TABLE','_START_');
1432 Tag('LABOUR_HANDICAP_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1433 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1434 fnd_date.date_to_chardate(l_temp_date_to+1));
1435 Tag('LHD_TABLE','_END_');
1436 END IF;
1437 IF l_temp_date_to IS NULL THEN
1438 Tag('LHD_TABLE','_START_');
1439 Tag('LABOUR_HANDICAP_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang')));
1440 Tag('LHD_TABLE','_END_');
1441 END IF;
1442 EXIT;
1443 END IF;
1444
1445 IF l_temp_date_to+1<r_get_lhd_details.DATE_FROM THEN
1446 Tag('LHD_TABLE','_START_');
1447 Tag('LABOUR_HANDICAP_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1448 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1449 fnd_date.date_to_chardate(l_temp_date_to+1));
1450 Tag('LHD_TABLE','_END_');
1451 l_previous := 'N';
1452 END IF;
1453
1454
1455 l_temp_date_to := NVL(r_get_lhd_details.DATE_TO,hr_general.end_of_time);
1456
1457 IF l_previous <> 'Y' THEN
1458 Tag('LHD_TABLE','_START_');
1459 Tag('LABOUR_HANDICAP_DISCOUNT',get_lookup_value('HR_NL_YES_NO','Y',userenv('lang'))||', '||
1460 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1461 fnd_date.date_to_chardate(r_get_lhd_details.DATE_FROM));
1462 Tag('LHD_TABLE','_END_');
1463 l_previous := 'Y';
1464 END IF;
1465
1466 END LOOP;
1467 CLOSE cur_get_lhd_details;
1468
1469 /*Temporary Tax Discount Details for employee */
1470 l_temp_date_to := NULL;
1471 l_previous := 'N';
1472 OPEN cur_get_ttd_details(r_person_details.assignmentid);
1473 LOOP
1474 FETCH cur_get_ttd_details INTO r_get_ttd_details;
1475
1476 IF cur_get_ttd_details%NOTFOUND THEN
1477 IF l_temp_date_to<> hr_general.end_of_time AND l_temp_date_to IS NOT NULL THEN
1478 Tag('TTD_TABLE','_START_');
1479 Tag('TEMP_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1480 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1481 fnd_date.date_to_chardate(l_temp_date_to+1));
1482 Tag('TTD_TABLE','_END_');
1483 END IF;
1484 IF l_temp_date_to IS NULL THEN
1485 Tag('TTD_TABLE','_START_');
1486 Tag('TEMP_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang')));
1487 Tag('TTD_TABLE','_END_');
1488 END IF;
1489
1490 EXIT;
1491 END IF;
1492
1493 IF l_temp_date_to+1<r_get_ttd_details.DATE_FROM THEN
1494 Tag('TTD_TABLE','_START_');
1495 Tag('TEMP_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','N',userenv('lang'))||', '||
1496 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1497 fnd_date.date_to_chardate(l_temp_date_to+1));
1498 Tag('TTD_TABLE','_END_');
1499 l_previous := 'N';
1500 END IF;
1501
1502 l_temp_date_to := NVL(r_get_ttd_details.DATE_TO,hr_general.end_of_time);
1503
1504 IF l_previous <> 'Y' THEN
1505 Tag('TTD_TABLE','_START_');
1506 Tag('TEMP_TAX_DISCOUNT',get_lookup_value('HR_NL_YES_NO','Y',userenv('lang'))||', '||
1507 get_lookup_value('NL_FORM_LABELS','COMMENCING_FROM',userenv('lang'))||' '||
1508 fnd_date.date_to_chardate(r_get_ttd_details.date_from));
1509 Tag('TTD_TABLE','_END_');
1510 l_previous := 'Y';
1511 END IF;
1512 END LOOP;
1513 CLOSE cur_get_ttd_details;
1514
1515 /*Employee QuickPay Details */
1516 OPEN cur_pay_period(r_person_details.assignmentid);
1517 <<Period>>
1518 LOOP
1519 FETCH cur_pay_period INTO r_pay_period;
1520
1521 EXIT Period WHEN cur_pay_period%NOTFOUND;
1522
1523
1524 /*Reset Record Values to 0 */
1525 emp_payroll_details := reset_payroll_details;
1526
1527 /*Fetch Payroll Details */
1528 y := get_payroll_run_details(r_pay_period.assact_id,
1529 p_emp_payroll_details=>emp_payroll_details);
1530
1531 /*Display row details only if values are present */
1532 IF emp_payroll_details.values_present = 'Y' THEN
1533 /*Display in XML */
1534 Tag('PERIOD','_START_');
1535 Tag('COLUMN1',r_pay_period.period_name);
1536 Tag('COLUMN3',to_char(ROUND(emp_payroll_details.column3,2),g_number_format));
1537 Tag('COLUMN4',to_char(ROUND(emp_payroll_details.column4,2),g_number_format));
1538 Tag('COLUMN5',to_char(ROUND(emp_payroll_details.column5,2),g_number_format));
1539 Tag('COLUMN6',to_char(ROUND(emp_payroll_details.column6,2),g_number_format));
1540 Tag('COLUMN7',to_char(ROUND(emp_payroll_details.column7,2),g_number_format));
1541 Tag('COLUMN8',to_char(ROUND(emp_payroll_details.column8,2),g_number_format));
1542 Tag('COLUMN12',to_char(ROUND(emp_payroll_details.column12,2),g_number_format));
1543 Tag('COLUMN14',to_char(ROUND(emp_payroll_details.column14,2),g_number_format));
1544 Tag('COLUMN15',to_char(ROUND(emp_payroll_details.column15,2),g_number_format));
1545 Tag('COLUMN16',to_char(ROUND(emp_payroll_details.column16,2),g_number_format));
1546 Tag('COLUMN17',to_char(ROUND(emp_payroll_details.column17,2),g_number_format));
1547 Tag('COLUMN18',to_char(ROUND(emp_payroll_details.column18,2),g_number_format));
1548 Tag('COLUMN19',to_char(ROUND(emp_payroll_details.column19,2),g_number_format));
1549 Tag('PERIOD','_END_');
1550
1551 /*Add the values to employee totals */
1552 emp_totals.column3 := emp_totals.column3 + emp_payroll_details.column3;
1553 emp_totals.column4 := emp_totals.column4 + emp_payroll_details.column4;
1554 emp_totals.column5 := emp_totals.column5 + emp_payroll_details.column5;
1555 emp_totals.column6 := emp_totals.column6 + emp_payroll_details.column6;
1556 emp_totals.column7 := emp_totals.column7 + emp_payroll_details.column7;
1557 emp_totals.column8 := emp_totals.column8 + emp_payroll_details.column8;
1558 emp_totals.column12 := emp_totals.column12 + emp_payroll_details.column12;
1559 emp_totals.column14 := emp_totals.column14 + emp_payroll_details.column14;
1560 emp_totals.column15 := emp_totals.column15 + emp_payroll_details.column15;
1561 emp_totals.column16 := emp_totals.column16 + emp_payroll_details.column16;
1562 emp_totals.column17 := emp_totals.column17 + emp_payroll_details.column17;
1563 emp_totals.column18 := emp_totals.column18 + emp_payroll_details.column18;
1564 emp_totals.column19 := emp_totals.column19 + emp_payroll_details.column19;
1565
1566 END IF;
1567
1568 /*Call to fetch employee retro details */
1569 OPEN cur_retro_period_det(r_pay_period.asg_id,r_pay_period.pact_id,r_pay_period.assact_id);
1570 <<RetroPeriod>>
1571 LOOP
1572
1573 FETCH cur_retro_period_det INTO r_retro_period_det;
1574
1575 EXIT RetroPeriod WHEN cur_retro_period_det%NOTFOUND;
1576
1577 /*Reset Retro Details to Zero */
1578 emp_retro_details := reset_retro_details;
1579
1580 Y := get_retro_details(r_retro_period_det.ASSACT_ID
1581 ,r_retro_period_det.ASG_ID
1582 ,r_retro_period_det.retro_period
1583 ,P_end_of_year
1584 ,L_Reporting_Date
1585 ,p_emp_retro_details => emp_retro_details);
1586
1587 /*Display row details only if values are present */
1588 IF emp_retro_details.values_present = 'Y' THEN
1589 /*Display in XML */
1590 Tag('PERIOD','_START_');
1591 Tag('COLUMN1',r_retro_period_det.period_name);
1592 Tag('COLUMN3',to_char(ROUND(emp_retro_details.column3,2),g_number_format));
1593 Tag('COLUMN4',to_char(ROUND(emp_retro_details.column4,2),g_number_format));
1594 Tag('COLUMN5',to_char(ROUND(emp_retro_details.column5,2),g_number_format));
1595 Tag('COLUMN6',to_char(ROUND(emp_retro_details.column6,2),g_number_format));
1596 Tag('COLUMN7',to_char(ROUND(emp_retro_details.column7,2),g_number_format));
1597 Tag('COLUMN8',to_char(ROUND(emp_retro_details.column8,2),g_number_format));
1598 Tag('COLUMN12',to_char(ROUND(emp_retro_details.column12,2),g_number_format));
1599 Tag('COLUMN14',to_char(ROUND(emp_retro_details.column14,2),g_number_format));
1600 Tag('COLUMN15',to_char(ROUND(emp_retro_details.column15,2),g_number_format));
1601 Tag('COLUMN16',to_char(ROUND(emp_retro_details.column16,2),g_number_format));
1602 Tag('COLUMN17',to_char(ROUND(emp_retro_details.column17,2),g_number_format));
1603 Tag('COLUMN18',to_char(ROUND(emp_retro_details.column18,2),g_number_format));
1604 Tag('COLUMN19',to_char(ROUND(emp_retro_details.column19,2),g_number_format));
1605 Tag('PERIOD','_END_');
1606
1607 /*Add the values to employee totals */
1608 emp_totals.column3 := emp_totals.column3 + emp_retro_details.column3;
1609 emp_totals.column4 := emp_totals.column4 + emp_retro_details.column4;
1610 emp_totals.column5 := emp_totals.column5 + emp_retro_details.column5;
1611 emp_totals.column6 := emp_totals.column6 + emp_retro_details.column6;
1612 emp_totals.column7 := emp_totals.column7 + emp_retro_details.column7;
1613 emp_totals.column8 := emp_totals.column8 + emp_retro_details.column8;
1614 emp_totals.column12 := emp_totals.column12 + emp_retro_details.column12;
1615 emp_totals.column14 := emp_totals.column14 + emp_retro_details.column14;
1616 emp_totals.column15 := emp_totals.column15 + emp_retro_details.column15;
1617 emp_totals.column16 := emp_totals.column16 + emp_retro_details.column16;
1618 emp_totals.column17 := emp_totals.column17 + emp_retro_details.column17;
1619 emp_totals.column18 := emp_totals.column18 + emp_retro_details.column18;
1620 emp_totals.column19 := emp_totals.column19 + emp_retro_details.column19;
1621 END IF;
1622
1623 END LOOP RetroPeriod;
1624 CLOSE cur_retro_period_det;
1625 /*EndCall to fetch employee Retro Payroll Details Standard and Special */
1626
1627 END LOOP Period;
1628 CLOSE cur_pay_period;
1629
1630 /*Display Employee Totals */
1631 Tag('EMP_TOT_COL3',to_char(ROUND(emp_totals.column3,2),g_number_format));
1632 Tag('EMP_TOT_COL4',to_char(ROUND(emp_totals.column4,2),g_number_format));
1633 Tag('EMP_TOT_COL5',to_char(ROUND(emp_totals.column5,2),g_number_format));
1634 Tag('EMP_TOT_COL6',to_char(ROUND(emp_totals.column6,2),g_number_format));
1635 Tag('EMP_TOT_COL7',to_char(ROUND(emp_totals.column7,2),g_number_format));
1636 Tag('EMP_TOT_COL8',to_char(ROUND(emp_totals.column8,2),g_number_format));
1637 Tag('EMP_TOT_COL12',to_char(ROUND(emp_totals.column12,2),g_number_format));
1638 Tag('EMP_TOT_COL14',to_char(ROUND(emp_totals.column14,2),g_number_format));
1639 Tag('EMP_TOT_COL15',to_char(ROUND(emp_totals.column15,2),g_number_format));
1640 Tag('EMP_TOT_COL16',to_char(ROUND(emp_totals.column16,2),g_number_format));
1641 Tag('EMP_TOT_COL17',to_char(ROUND(emp_totals.column17,2),g_number_format));
1642 Tag('EMP_TOT_COL18',to_char(ROUND(emp_totals.column18,2),g_number_format));
1643 Tag('EMP_TOT_COL19',to_char(ROUND(emp_totals.column19,2),g_number_format));
1644 Tag('EMPLOYEE','_END_');
1645
1646 /*Add Employee totals to Org totals */
1647 org_totals.column3 := org_totals.column3 + emp_totals.column3;
1648 org_totals.column4 := org_totals.column4 + emp_totals.column4;
1649 org_totals.column5 := org_totals.column5 + emp_totals.column5;
1650 org_totals.column6 := org_totals.column6 + emp_totals.column6;
1651 org_totals.column7 := org_totals.column7 + emp_totals.column7;
1652 org_totals.column8 := org_totals.column8 + emp_totals.column8;
1653 org_totals.column12 := org_totals.column12 + emp_totals.column12;
1654 org_totals.column14 := org_totals.column14 + emp_totals.column14;
1655 org_totals.column15 := org_totals.column15 + emp_totals.column15;
1656 org_totals.column16 := org_totals.column16 + emp_totals.column16;
1657 org_totals.column17 := org_totals.column17 + emp_totals.column17;
1658 org_totals.column18 := org_totals.column18 + emp_totals.column18;
1659 org_totals.column19 := org_totals.column19 + emp_totals.column19;
1660
1661 END LOOP Employee;
1662 CLOSE csr_person_details;
1663
1664 IF l_emp_count >0 THEN
1665 l_employer_count := l_employer_count+1;
1666
1667 /*Display Totals only if employees exists */
1668 Tag('ORG_TOT_COL3',to_char(ROUND(org_totals.column3,2),g_number_format));
1669 Tag('ORG_TOT_COL4',to_char(ROUND(org_totals.column4,2),g_number_format));
1670 Tag('ORG_TOT_COL5',to_char(ROUND(org_totals.column5,2),g_number_format));
1671 Tag('ORG_TOT_COL6',to_char(ROUND(org_totals.column6,2),g_number_format));
1672 Tag('ORG_TOT_COL7',to_char(ROUND(org_totals.column7,2),g_number_format));
1673 Tag('ORG_TOT_COL8',to_char(ROUND(org_totals.column8,2),g_number_format));
1674 Tag('ORG_TOT_COL12',to_char(ROUND(org_totals.column12,2),g_number_format));
1675 Tag('ORG_TOT_COL14',to_char(ROUND(org_totals.column14,2),g_number_format));
1676 Tag('ORG_TOT_COL15',to_char(ROUND(org_totals.column15,2),g_number_format));
1677 Tag('ORG_TOT_COL16',to_char(ROUND(org_totals.column16,2),g_number_format));
1678 Tag('ORG_TOT_COL17',to_char(ROUND(org_totals.column17,2),g_number_format));
1679 Tag('ORG_TOT_COL18',to_char(ROUND(org_totals.column18,2),g_number_format));
1680 Tag('ORG_TOT_COL19',to_char(ROUND(org_totals.column19,2),g_number_format));
1681
1682 /*Fetch Employer Basic Details */
1683 OPEN cur_employer_address;
1684 FETCH cur_employer_address INTO r_employer_address;
1685
1686 IF cur_employer_address%FOUND THEN
1687 /*Append various details into address */
1688 IF (r_employer_address.street_name) IS NOT NULL THEN
1689 l_org_address := l_org_address || r_employer_address.street_name;
1690 END IF;
1691 IF (r_employer_address.house_number) IS NOT NULL THEN
1692 l_org_address := l_org_address || ', ' || r_employer_address.house_number;
1693 END IF;
1694 IF (r_employer_address.house_number_add) IS NOT NULL THEN
1695 l_org_address := l_org_address || ', ' || r_employer_address.house_number_add;
1696 END IF;
1697 IF (r_employer_address.address_1) IS NOT NULL THEN
1698 l_org_address := l_org_address || ', ' || r_employer_address.address_1;
1699 END IF;
1700 IF (r_employer_address.address_2) IS NOT NULL THEN
1701 l_org_address := l_org_address || ', ' || r_employer_address.address_2;
1702 END IF;
1703 IF (r_employer_address.address_3) IS NOT NULL THEN
1704 l_org_address := l_org_address || ', ' || r_employer_address.address_3;
1705 END IF;
1706
1707 /*End of Appending */
1708
1709 Tag('ORG_ADDRESS',l_org_address);
1710 Tag('ORG_POSTAL_CODE',r_employer_address.postcode);
1711 Tag('ORG_CITY',r_employer_address.city);
1712 Tag('ORG_COUNTRY',r_employer_address.country);
1713 END IF;
1714 CLOSE cur_employer_address;
1715
1716 /*Fetch Tax Registration Number for Employer */
1717 OPEN csr_le_hr_mapping_chk;
1718 FETCH csr_le_hr_mapping_chk INTO l_tax_ref_no,l_hr_tax_unit,l_tax_rep_name;
1719 CLOSE csr_le_hr_mapping_chk;
1720
1721 IF l_tax_ref_no IS NOT NULL THEN
1722 l_tax_reg_no1 := l_tax_ref_no;
1723 ELSE
1724 OPEN csr_tax_reg_no(NVL(l_hr_tax_unit,l_tax_org_id),p_business_group_id);
1725 FETCH csr_tax_reg_no INTO l_tax_reg_no;
1726 CLOSE csr_tax_reg_no;
1727
1728 l_tax_reg_no1 := l_tax_reg_nO;
1729 END IF;
1730
1731 Tag('ORG_TAX_REG_NO',l_tax_reg_no1);
1732 Tag('EMPLOYER','_END_');
1733
1734 END IF;
1735
1736
1737 /*Calculate Report totals from org totals */
1738 rep_totals.column3 := rep_totals.column3 + org_totals.column3;
1739 rep_totals.column4 := rep_totals.column4 + org_totals.column4;
1740 rep_totals.column5 := rep_totals.column5 + org_totals.column5;
1741 rep_totals.column6 := rep_totals.column6 + org_totals.column6;
1742 rep_totals.column7 := rep_totals.column7 + org_totals.column7;
1743 rep_totals.column8 := rep_totals.column8 + org_totals.column8;
1744 rep_totals.column12 := rep_totals.column12 + org_totals.column12;
1745 rep_totals.column14 := rep_totals.column14 + org_totals.column14;
1746 rep_totals.column15 := rep_totals.column15 + org_totals.column15;
1747 rep_totals.column16 := rep_totals.column16 + org_totals.column16;
1748 rep_totals.column17 := rep_totals.column17 + org_totals.column17;
1749 rep_totals.column18 := rep_totals.column18 + org_totals.column18;
1750 rep_totals.column19 := rep_totals.column19 + org_totals.column19;
1751
1752 END LOOP Employer;
1753
1754 CLOSE cur_employer_details;
1755
1756 IF l_employer_count>0 THEN
1757 IF l_employer_count > 1 AND l_total_emp_count>1 THEN
1758 Tag('ORG_TOTALS','Y');
1759 ELSE
1760 Tag('ORG_TOTALS','N');
1761 END IF;
1762 Tag('REP_TOT_COL3',to_char(ROUND(rep_totals.column3,2),g_number_format));
1763 Tag('REP_TOT_COL4',to_char(ROUND(rep_totals.column4,2),g_number_format));
1764 Tag('REP_TOT_COL5',to_char(ROUND(rep_totals.column5,2),g_number_format));
1765 Tag('REP_TOT_COL6',to_char(ROUND(rep_totals.column6,2),g_number_format));
1766 Tag('REP_TOT_COL7',to_char(ROUND(rep_totals.column7,2),g_number_format));
1767 Tag('REP_TOT_COL8',to_char(ROUND(rep_totals.column8,2),g_number_format));
1768 Tag('REP_TOT_COL12',to_char(ROUND(rep_totals.column12,2),g_number_format));
1769 Tag('REP_TOT_COL14',to_char(ROUND(rep_totals.column14,2),g_number_format));
1770 Tag('REP_TOT_COL15',to_char(ROUND(rep_totals.column15,2),g_number_format));
1771 Tag('REP_TOT_COL16',to_char(ROUND(rep_totals.column16,2),g_number_format));
1772 Tag('REP_TOT_COL17',to_char(ROUND(rep_totals.column17,2),g_number_format));
1773 Tag('REP_TOT_COL18',to_char(ROUND(rep_totals.column18,2),g_number_format));
1774 Tag('REP_TOT_COL19',to_char(ROUND(rep_totals.column19,2),g_number_format));
1775
1776 IF l_total_emp_count>1 THEN
1777 Tag('REP_TOTALS','Y');
1778 ELSE
1779 Tag('REP_TOTALS','N');
1780 END IF;
1781 END IF;
1782
1783 Tag('DUTCH_REM','_END_');
1784
1785 write_to_clob(p_xmltable => xXMLtable
1786 ,p_clob => l_xml);
1787
1788 p_xml := l_xml;
1789 dbms_lob.freeTemporary(l_xml);
1790
1791
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 fnd_file.put_line(fnd_file.output,'Exception in generate SQL-ERRM : '||SQLERRM);
1795 raise;
1796 END generate;
1797
1798 -- =============================================================================
1799 -- get_employee_high_wages - Employees with High Wages
1800 -- =============================================================================
1801 PROCEDURE get_employee_high_wages(errbuf OUT NOCOPY VARCHAR2
1802 ,retcode OUT NOCOPY NUMBER
1803 ,p_business_group_id NUMBER
1804 ,p_end_of_year VARCHAR2
1805 ,p_org_id NUMBER
1806 ,p_employer_name VARCHAR2
1807 ,p_threshold_limit NUMBER
1808 ,p_percentage NUMBER)
1809 IS
1810
1811 --Local Variables
1812 l_reporting_start_date DATE;
1813 l_reporting_end_date DATE;
1814 l_wage_tax_income NUMBER;
1815 l_income NUMBER := 0;
1816 l_tax_total NUMBER := 0;
1817 l_seperator VARCHAR2(10):= ',';
1818 l_format VARCHAR2(30) := 'FM9999999990D00';
1819 l_prev_sofi_number per_all_people_f.national_identifier%TYPE := -9999;
1820 l_multiple_index NUMBER :=0;
1821 l_per_index NUMBER :=0;
1822 i NUMBER := 0;
1823 l_prev_rank NUMBER := 0;
1824
1825 --=============================================================================
1826 /***********************************************************
1827 Cursor to fetch Person Details
1828 ***********************************************************/
1829 CURSOR csr_person_details IS
1830 SELECT DISTINCT
1831 papf.national_identifier sofi_number
1832 FROM per_all_people_f papf
1833 ,per_all_assignments_f paa
1834 ,per_periods_of_service pps
1835 ,pay_payroll_actions ppa
1836 ,pay_assignment_actions asg_act
1837 ,pay_all_payrolls_f ppf
1838 WHERE ppa.business_group_id = p_business_group_id
1839 AND nvl (paa.establishment_id
1840 ,ppf.prl_information1) = p_org_id
1841 AND papf.effective_start_date <= l_reporting_end_date
1842 AND papf.effective_end_date >= l_reporting_start_date
1843 AND papf.person_id = paa.person_id
1844 AND pps.person_id = paa.person_id
1845 AND pps.period_of_service_id = paa.period_of_service_id
1846 AND asg_act.assignment_id = paa.assignment_id
1847 AND ppa.payroll_action_id = asg_act.payroll_action_id
1848 AND ppa.action_type IN ('R','B','I'
1849 ,'Q','V')
1850 AND ppa.action_status IN ('C','S')
1851 AND ppa.effective_date BETWEEN l_reporting_start_date
1852 AND l_reporting_end_date
1853 AND ppa.effective_date BETWEEN paa.effective_start_date
1854 AND paa.effective_end_date
1855 AND ppa.effective_date BETWEEN ppf.effective_start_date
1856 AND ppf.effective_end_date
1857 AND ppf.payroll_id = ppa.payroll_id
1858 AND ppf.prl_information_category = 'NL'
1859 ORDER BY papf.national_identifier;
1860 --=============================================================================
1861 /***********************************************************
1862 Cursor to fetch Employee in Cross Employers Details
1863 ***********************************************************/
1864 CURSOR csr_other_emp_details(c_sofi_number per_all_people_f.national_identifier%TYPE) IS
1865 SELECT employee_number,
1866 employee_name,
1867 sofi_number,
1868 hire_date,
1869 end_date,
1870 assignment_id,
1871 person_id,org_id,
1872 employer_name,
1873 RANK() OVER (PARTITION BY sofi_number ORDER BY person_id) rank
1874 FROM
1875 (
1876 SELECT DISTINCT
1877 papf.employee_number employee_number
1878 ,REPLACE(papf.last_name
1879 || ' '
1880 || papf.pre_name_adjunct
1881 || ' '
1882 || papf.per_information1
1883 ,l_seperator
1884 ,' ') employee_name
1885 ,papf.national_identifier sofi_number
1886 ,papf.effective_start_date hire_date
1887 ,papf.effective_end_date end_date
1888 ,paa.assignment_id assignment_id
1889 ,paa.person_id person_id
1890 ,NVL (paa.establishment_id
1891 ,ppf.prl_information1) org_id
1892 , REPLACE(hou1.name,l_seperator,' ') employer_name
1893 FROM per_all_people_f papf
1894 ,per_all_assignments_f paa
1895 ,per_periods_of_service pps
1896 ,pay_payroll_actions ppa
1897 ,pay_assignment_actions asg_act
1898 ,pay_all_payrolls_f ppf
1899 ,hr_all_organization_units hou1
1900 WHERE ppa.business_group_id = p_business_group_id
1901 AND papf.effective_start_date <= l_reporting_end_date
1902 AND papf.effective_end_date >= l_reporting_start_date
1903 AND papf.current_employee_flag = 'Y'
1904 AND papf.person_id = paa.person_id
1905 AND pps.person_id = paa.person_id
1906 AND pps.period_of_service_id = paa.period_of_service_id
1907 AND asg_act.assignment_id = paa.assignment_id
1908 AND ppa.payroll_action_id = asg_act.payroll_action_id
1909 AND ppa.action_type IN ('R','B','I'
1910 ,'Q','V')
1911 AND ppa.action_status IN ('C','S')
1912 AND ppa.effective_date BETWEEN l_reporting_start_date
1913 AND l_reporting_end_date
1914 AND ppa.effective_date BETWEEN paa.effective_start_date
1915 AND paa.effective_end_date
1916 AND ppa.effective_date BETWEEN ppf.effective_start_date
1917 AND ppf.effective_end_date
1918 AND ppf.payroll_id = ppa.payroll_id
1919 AND ppf.prl_information_category = 'NL'
1920 AND papf.national_identifier = c_sofi_number
1921 AND NVL (paa.establishment_id,ppf.prl_information1)=hou1.organization_id
1922 AND paa.primary_flag = 'Y'
1923 )
1924 ORDER BY RANK DESC,end_date DESC;
1925 --=============================================================================
1926
1927 r_person_details csr_person_details%ROWTYPE;
1928 TYPE person_details_type IS RECORD (
1929 employee_number per_all_people_f.employee_number%TYPE,
1930 employee_name per_all_people_f.full_name%TYPE,
1931 sofi_number per_all_people_f.national_identifier%TYPE,
1932 hire_date DATE,
1933 end_date DATE,
1934 income NUMBER,
1935 tax NUMBER,
1936 employer_name hr_all_organization_units.name%TYPE );
1937
1938 TYPE person_details IS TABLE OF person_details_type
1939 INDEX BY BINARY_INTEGER;
1940
1941 t_single_personid_details person_details;
1942 t_multiple_personid_details person_details;
1943 t_individual_person_details person_details;
1944 r_other_emp_details csr_other_emp_details%ROWTYPE;
1945
1946 BEGIN
1947
1948 --Get Start and End date of year
1949 l_reporting_start_date := TRUNC(fnd_date.canonical_to_date(p_end_of_year),'Y');
1950 l_reporting_end_date := fnd_date.canonical_to_date(p_end_of_year);
1951
1952 --Open Cursor to fetch Employee Details in Organization
1953 <<Employee>>
1954 FOR r_person_details IN csr_person_details
1955 LOOP
1956 OPEN csr_other_emp_details(r_person_details.sofi_number);
1957 FETCH csr_other_emp_details INTO r_other_emp_details;
1958
1959 IF r_other_emp_details.rank =1 AND csr_person_details%FOUND THEN
1960 l_wage_tax_income := pay_nl_eoy_pkg.get_prev_year_tax_income(r_other_emp_details.assignment_id,
1961 add_months(l_reporting_end_date,12));
1962
1963 IF l_wage_tax_income>=p_threshold_limit THEN
1964 t_single_personid_details(l_per_index).employee_number := r_other_emp_details.employee_number;
1965 t_single_personid_details(l_per_index).employee_name := r_other_emp_details.employee_name;
1966 t_single_personid_details(l_per_index).sofi_number := r_other_emp_details.sofi_number;
1967 t_single_personid_details(l_per_index).hire_date := r_other_emp_details.hire_date;
1968 IF NVL(r_other_emp_details.end_date,hr_general.end_of_time)<>hr_general.end_of_time THEN
1969 t_single_personid_details(l_per_index).end_date := r_other_emp_details.end_date;
1970 END IF;
1971 t_single_personid_details(l_per_index).income := l_wage_tax_income;
1972 t_single_personid_details(l_per_index).tax := (l_wage_tax_income-p_threshold_limit)*p_percentage/100;
1973 l_per_index := l_per_index+1;
1974 END IF;
1975 ELSIF r_other_emp_details.rank>1 AND csr_person_details%FOUND THEN
1976 i :=0;
1977 l_income :=0;
1978 t_individual_person_details.DELETE;
1979
1980 l_prev_rank :=0;
1981
1982 <<multiple_emps>>
1983 LOOP
1984 IF csr_other_emp_details%FOUND AND l_prev_rank<>r_other_emp_details.rank THEN
1985 l_wage_tax_income := pay_nl_eoy_pkg.get_prev_year_tax_income(r_other_emp_details.assignment_id,
1986 add_months(l_reporting_end_date,12));
1987 t_individual_person_details(i).employee_number := r_other_emp_details.employee_number;
1988 t_individual_person_details(i).employee_name := r_other_emp_details.employee_name;
1989 t_individual_person_details(i).employer_name := r_other_emp_details.employer_name;
1990 t_individual_person_details(i).sofi_number := r_other_emp_details.sofi_number;
1991 t_individual_person_details(i).hire_date := r_other_emp_details.hire_date;
1992 IF NVL(r_other_emp_details.end_date,hr_general.end_of_time)<>hr_general.end_of_time THEN
1993 t_individual_person_details(i).end_date := r_other_emp_details.end_date;
1994 END IF;
1995 t_individual_person_details(i).income := l_wage_tax_income;
1996 i := i+1;
1997 END IF;
1998
1999 l_prev_rank := r_other_emp_details.rank;
2000
2001 FETCH csr_other_emp_details INTO r_other_emp_details;
2002
2003 IF csr_other_emp_details%NOTFOUND THEN
2004 FOR j IN t_individual_person_details.FIRST..t_individual_person_details.LAST
2005 LOOP
2006 l_income := l_income+t_individual_person_details(j).income;
2007 END LOOP;
2008
2009 IF l_income>=p_threshold_limit THEN
2010 FOR j IN t_individual_person_details.FIRST..t_individual_person_details.LAST
2011 LOOP
2012 t_multiple_personid_details(l_multiple_index):=t_individual_person_details(j);
2013 l_multiple_index:=l_multiple_index+1;
2014 END LOOP;
2015 END IF;
2016 EXIT;
2017 END IF;
2018 END LOOP multiple_emps;
2019 END IF;
2020 CLOSE csr_other_emp_details;
2021 END LOOP Employee;
2022
2023 /*Display Top headings for employees having single person id */
2024 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_UNIT_OF_CONTRIBUTION'
2025 ,'YEAR'
2026 ,userenv('lang'))||' : '
2027 ||l_seperator||TO_CHAR(l_reporting_end_date,'YYYY'));
2028
2029 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2030 ,'WR_EMPLOYER_NAME'
2031 ,userenv('lang'))||' : '
2032 ||l_seperator||REPLACE(p_employer_name,l_seperator,' '));
2033 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2034 ,'THRESHOLD'
2035 ,userenv('lang'))||' : '
2036 ||l_seperator||REPLACE(p_threshold_limit,l_seperator,' '));
2037 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2038 ,'PERCENTAGE'
2039 ,userenv('lang'))||' : '
2040 ||l_seperator||REPLACE(p_percentage,l_seperator,' '));
2041
2042 /*Display only if rows exists */
2043 IF t_single_personid_details.COUNT >0 THEN
2044 /*Display Heading */
2045 fnd_file.put_line(fnd_file.output,' ');
2046
2047 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2048 ,'EMPLOYEE_NUMBER'
2049 ,userenv('lang'))
2050 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2051 ,'BSN'
2052 ,userenv('lang'))
2053 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2054 ,'FULL_NAME'
2055 ,userenv('lang'))
2056 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2057 ,'COMMENCING_FROM'
2058 ,userenv('lang'))
2059 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2060 ,'DATE_ENDING'
2061 ,userenv('lang'))
2062 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2063 ,'INCOME_DETAILS_FOR_TAXATION'
2064 ,userenv('lang'))
2065 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2066 ,'PSEHHGLN'
2067 ,userenv('lang')));
2068 /*End of Displaying Heading */
2069
2070 l_tax_total :=0;
2071 /*Display Employee Details */
2072 FOR i IN t_single_personid_details.FIRST..t_single_personid_details.LAST
2073 LOOP
2074 fnd_file.put_line(fnd_file.output,t_single_personid_details(i).employee_number||l_seperator
2075 ||t_single_personid_details(i).sofi_number||l_seperator
2076 ||t_single_personid_details(i).employee_name||l_seperator
2077 ||t_single_personid_details(i).hire_date||l_seperator
2078 ||t_single_personid_details(i).end_date||l_seperator
2079 ||t_single_personid_details(i).income||l_seperator
2080 ||t_single_personid_details(i).tax);
2081 l_tax_total := l_tax_total+t_single_personid_details(i).tax;
2082 END LOOP;
2083 /*End of Displaying Details */
2084
2085 fnd_file.put_line(fnd_file.output,' ');
2086
2087 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2088 ,'TOTAL'
2089 ,userenv('lang'))
2090 ||l_seperator
2091 ||l_seperator
2092 ||l_seperator
2093 ||l_seperator
2094 ||l_seperator
2095 ||l_seperator
2096 ||to_char(l_tax_total,l_format));
2097 END IF;
2098
2099 IF t_multiple_personid_details.COUNT>0 THEN
2100 fnd_file.put_line(fnd_file.output,' ');
2101 /*Display Top headings for employees having multiple person id */
2102 fnd_file.put_line(fnd_file.output,' ');
2103 fnd_message.set_name('PER','HR_51410_WEB_DUP_PERSON_NAME');
2104 fnd_file.put_line(fnd_file.output,fnd_message.get);
2105 fnd_file.put_line(fnd_file.output,' ');
2106 /*Display Heading */
2107 fnd_file.put_line(fnd_file.output,get_lookup_value('NL_FORM_LABELS'
2108 ,'BSN'
2109 ,userenv('lang'))
2110 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2111 ,'WR_EMPLOYER_NAME'
2112 ,userenv('lang'))
2113 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2114 ,'EMPLOYEE_NUMBER'
2115 ,userenv('lang'))
2116 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2117 ,'FULL_NAME'
2118 ,userenv('lang'))
2119 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2120 ,'COMMENCING_FROM'
2121 ,userenv('lang'))
2122 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2123 ,'DATE_ENDING'
2124 ,userenv('lang'))
2125 ||l_seperator||get_lookup_value('NL_FORM_LABELS'
2126 ,'INCOME_DETAILS_FOR_TAXATION'
2127 ,userenv('lang')));
2128
2129 /*Display Employee Details */
2130 FOR i IN t_multiple_personid_details.FIRST..t_multiple_personid_details.LAST
2131 LOOP
2132 fnd_file.put_line(fnd_file.output,t_multiple_personid_details(i).sofi_number||l_seperator
2133 ||t_multiple_personid_details(i).employer_name||l_seperator
2134 ||t_multiple_personid_details(i).employee_number||l_seperator
2135 ||t_multiple_personid_details(i).employee_name||l_seperator
2136 ||t_multiple_personid_details(i).hire_date||l_seperator
2137 ||t_multiple_personid_details(i).end_date||l_seperator
2138 ||t_multiple_personid_details(i).income);
2139
2140 END LOOP;
2141 END IF;
2142 /*End of Displaying Details */
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 fnd_file.put_line(fnd_file.output,SQLERRM);
2147 END get_employee_high_wages;
2148
2149
2150 END;
2151