[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_WTS_REPORT
Source
1 PACKAGE BODY PAY_NL_WTS_REPORT AS
2 /* $Header: paynlwts.pkb 120.8 2012/01/19 08:15:19 rpahune ship $ */
3
4 /*-------------------------------------------------------------------------------------
5 Function to get the last assignment_action_id for each person and payroll
6 --------------------------------------------------------------------------------------*/
7 FUNCTION GET_LAST_ASG_ACT_ID(l_person_id IN NUMBER,l_payroll_id IN NUMBER,l_date_earned IN DATE)
8 RETURN NUMBER IS
9 CURSOR csr_get_last_asg_act_id(l_person_id NUMBER,l_payroll_id NUMBER,l_date_earned DATE) IS
10 SELECT paa.assignment_action_id assignment_action_id
11 FROM
12 pay_assignment_actions paa,
13 pay_payroll_actions ppa,
14 per_all_assignments_f pas
15 WHERE
16 pas.person_id = l_person_id
17 AND pas.payroll_id = l_payroll_id
18 AND ppa.date_earned = l_date_earned
19 AND pas.assignment_id = paa.assignment_id
20 AND ppa.payroll_id = pas.payroll_id
21 AND paa.payroll_action_id = ppa.payroll_action_id
22 AND paa.action_status='C'
23 AND ppa.action_type in ('R','Q','V','B','I')
24 AND ppa.date_earned between pas.effective_start_date and pas.effective_end_date
25 and exists(select * from pay_run_results
26 where assignment_action_id=paa.assignment_action_id
30 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))))
27 and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'), Get_Element_Type_Id('Wage Tax Subsidy Education')
28 , Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed') , Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))
29 or element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
31 order by assignment_action_id desc;
32
33 l_asg_act_id NUMBER;
34 BEGIN
35 OPEN csr_get_last_asg_act_id(l_person_id,l_payroll_id,l_date_earned);
36 FETCH csr_get_last_asg_act_id INTO l_asg_act_id;
37 CLOSE csr_get_last_asg_act_id;
38 RETURN l_asg_act_id;
39 END;
40
41
42 /*-------------------------------------------------------------------------------------
43 Function to get the org_struct_version_id.
44 --------------------------------------------------------------------------------------*/
45 FUNCTION Get_org_struct_version_id(p_org_struct_id IN NUMBER,p_month_to IN VARCHAR2) RETURN NUMBER IS
46 cursor Csr_Get_Org_Struct_Version_Id(l_org_struct_id NUMBER,l_month_to VARCHAR2) IS
47 Select org_structure_version_id
48 From per_org_structure_versions posv
49 Where organization_structure_id=l_org_struct_id
50 and to_date(l_month_to,'MMYYYY') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time);
51 l_org_struct_ver_id per_org_structure_versions.org_structure_version_id%TYPE;
52 BEGIN
53 OPEN Csr_Get_Org_Struct_Version_Id(p_org_struct_id,p_month_to);
54 FETCH Csr_Get_Org_Struct_Version_Id INTO l_org_struct_ver_id;
55 CLOSE Csr_Get_Org_Struct_Version_Id;
56 RETURN l_org_struct_ver_id;
57 END Get_org_struct_version_id;
58
59
60 /*-------------------------------------------------------------------------------------
61 Function to get the Element_Type_id.
62 --------------------------------------------------------------------------------------*/
63 FUNCTION Get_Element_Type_Id(p_element_name IN VARCHAR2)
64 RETURN NUMBER IS
65 cursor Csr_Get_Element_Type_Id(l_element_name varchar2) IS
66 select element_type_id
67 from pay_element_types_F
68 where element_name = l_element_name
69 and legislation_code = 'NL';
70 l_Element_Type_Id pay_element_types_F.element_type_id%TYPE;
71 BEGIN
72 OPEN Csr_Get_Element_Type_Id(p_element_name);
73 FETCH Csr_Get_Element_Type_Id INTO l_Element_Type_Id;
74 CLOSE Csr_Get_Element_Type_Id;
75 RETURN l_Element_Type_Id;
76 END Get_Element_Type_Id;
77
78
79 /*-------------------------------------------------------------------------------------
80 Function to get the Input_Value_id.
81 --------------------------------------------------------------------------------------*/
82 FUNCTION Get_Input_Value_Id(p_input_value varchar2,p_element_type_id NUMBER)
83 RETURN NUMBER IS
84 cursor Csr_Get_Input_Value_Id(l_input_value varchar2,l_element_type_id NUMBER) IS
85 select round(input_value_id,2)
86 from pay_input_values_f
87 where name=l_input_value
88 and element_type_id=l_element_type_id
89 and legislation_code='NL';
90 l_Input_Value_Id NUMBER;
91 BEGIN
92 OPEN Csr_Get_Input_Value_Id(p_input_value,p_element_type_id);
93 FETCH Csr_Get_Input_Value_Id INTO l_Input_Value_Id;
94 CLOSE Csr_Get_Input_Value_Id;
95 RETURN l_Input_Value_Id;
96 END Get_Input_Value_Id;
97
98
99 /*-------------------------------------------------------------------------------------
100 Function to get the Defined_Balance_Id
101 --------------------------------------------------------------------------------------*/
102 FUNCTION Get_Defined_Balance_Id(p_balance_name IN VARCHAR2)
103 RETURN NUMBER IS
104 cursor Csr_Get_Defined_Balance_Id(l_balance_name Varchar2) is
105 select pdb.defined_balance_id
106 from pay_balance_dimensions pbd,
107 pay_balance_types pbt,
108 pay_defined_balances pdb
109 where pbt.balance_type_id = pdb.balance_type_id
110 and pbt.balance_name =l_balance_name
111 and pbd.balance_dimension_id = pdb.balance_dimension_id
112 and pbd.database_item_suffix='_PER_PAY_PTD'
113 and pbt.legislation_code='NL';
114 l_Defined_Balance_Id pay_defined_balances.defined_balance_id%TYPE;
115 BEGIN
116 OPEN Csr_Get_Defined_Balance_Id(p_balance_name);
117 FETCH Csr_Get_Defined_Balance_Id INTO l_Defined_Balance_Id;
118 CLOSE Csr_Get_Defined_Balance_Id;
119 RETURN l_Defined_Balance_Id;
120 END Get_Defined_Balance_Id;
121
122
123 /*-------------------------------------------------------------------------------------
124 Function to get the Subsidy Type
125 --------------------------------------------------------------------------------------*/
126 FUNCTION GET_SUBSIDY_TYPE_NAME(p_Subsidy_Element_Type_ID IN NUMBER)
127 RETURN VARCHAR2 IS
128
129 l_subsidy_name VARCHAR2(240);
130
131 BEGIN
132 IF p_Subsidy_Element_Type_ID IN (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
133 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')) THEN
134 RETURN hr_general.decode_lookup('NL_FORM_LABELS','LOW_WAGES');
135
136 ELSIF p_Subsidy_Element_Type_ID IN (Get_Element_Type_Id('Wage Tax Subsidy Education')
137 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
138 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level') --10160211
139 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')) THEN --10160211
140 RETURN hr_general.decode_lookup('NL_FORM_LABELS','EDUCATION');
141
142 ELSIF p_Subsidy_Element_Type_ID IN (Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
146 ELSIF p_Subsidy_Element_Type_ID IN (Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
143 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')) THEN
144 RETURN hr_general.decode_lookup('NL_FORM_LABELS','LONG_TERM_UNEMP');
145
147 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')) THEN
148 RETURN hr_general.decode_lookup('NL_FORM_LABELS','PAID_PARENTAL_LEAVE');
149 ELSE RETURN NULL;
150
151 END IF;
152
153 END GET_SUBSIDY_TYPE_NAME;
154
155
156 /*-------------------------------------------------------------------------------------
157 Function to get the Retro Wage Tax Subsidy Amount
158 --------------------------------------------------------------------------------------*/
159 FUNCTION get_retro_wts (p_asg_act_id IN NUMBER
160 ,p_element_type_id IN NUMBER
161 ,p_retro_date IN DATE)
162 RETURN NUMBER IS
163
164 CURSOR csr_get_retro_wts(l_asg_act_id NUMBER, l_element_type_id NUMBER, l_retro_date DATE) is
165 select DISTINCT
166 prr.run_result_id,
167 to_number(pay_nl_general.GET_RUN_RESULT_VALUE(paa.assignment_action_id,l_element_type_id,
168 decode(l_element_type_id,
169 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
170 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
171 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
172 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Pay Value',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id, 'M')) retro_wts
173 from pay_assignment_actions paa,
174 pay_assignment_actions paa1,
175 per_all_assignments_f pas,
176 per_all_assignments_f pas1,
177 pay_payroll_actions ppa,
178 pay_payroll_actions ppa1,
179 pay_run_results prr
180 where paa1.assignment_action_id = l_asg_act_id
181 and pas1.assignment_id = paa1.assignment_id
182 and pas.person_id = pas1.person_id
183 and paa.assignment_id = pas.assignment_id
184 and ppa1.payroll_action_id = paa1.payroll_action_id
185 and ppa.payroll_action_id = paa.payroll_action_id
186 and ppa.payroll_id = pas.payroll_id
187 and paa.action_status = 'C'
188 and ppa.action_type in ('R','Q','V','B','I')
189 and ppa.date_earned between pas.effective_start_date and pas.effective_end_date
190 and ppa.time_period_id = ppa1.time_period_id
191 and prr.assignment_action_id = paa.assignment_action_id
192 and prr.element_type_id = l_element_type_id
193 and nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) = l_retro_date;
194
195
196
197 l_retro_wts NUMBER := 0;
198 v_csr_get_retro_wts csr_get_retro_wts%ROWTYPE;
199
200 BEGIN
201
202 hr_utility.set_location('p_asg_sct_id-'||to_char(p_asg_act_id)||' p_element_type_id-'||to_char(p_element_type_id)||' p_retro_date-'||to_char(p_retro_date,'DD-MON-RRRR'),999);
203
204 FOR v_csr_get_retro_wts IN csr_get_retro_wts(p_asg_act_id, p_element_type_id, p_retro_date)
205 LOOP
206
207 l_retro_wts := l_retro_wts + v_csr_get_retro_wts.retro_wts;
208
209 END LOOP;
210
211 return l_retro_wts;
212
213 END get_retro_wts;
214
215
216 /*-------------------------------------------------------------------------------------
217 Procedure to generate XML data for WTS Report
218 --------------------------------------------------------------------------------------*/
219 procedure populate_wts_report_data(p_bg_id IN NUMBER,
220 p_eff_date IN VARCHAR2,
221 p_month_from IN VARCHAR2,
222 p_month_to IN VARCHAR2,
223 p_org_struct_id IN NUMBER,
224 p_org_struct IN VARCHAR2,
225 p_top_org_id IN NUMBER,
226 p_top_org IN VARCHAR2,
227 p_person_id IN NUMBER,
228 p_employee IN VARCHAR2,
229 p_inc_sub_emp IN VARCHAR2,
230 p_xfdf_blob OUT NOCOPY BLOB) IS
231 CURSOR csr_get_record_details is
232 SELECT DISTINCT
233 hou.name employer_name,
234 pap.full_name||'('||pap.employee_number||')' employee,
235 hoi.org_information3 tax_office_id,
236 hoi.org_information4 tax_reg,
237 ppa.business_group_id business_group_id,
238 hou1.name,
239 paa.person_id,
240 ppa.date_earned,
241 ppa.payroll_id
242 from
243 per_assignments_f paa,
244 pay_payroll_actions ppa,
245 per_people_f pap,
246 hr_organization_units hou,
247 hr_organization_units hou1,
248 hr_organization_information hoi,
249 pay_assignment_actions asg_act
250 where
251 ppa.business_group_id=p_bg_id
252 and paa.assignment_id = asg_act.assignment_id
253 and pap.person_id = paa.person_id
254 and ppa.payroll_action_id = asg_act.payroll_action_id
255 and asg_act.action_status='C'
256 and ppa.action_type in ('R','Q','V','B','I')
257 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
258 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
259 and paa.organization_id in
260 ((SELECT pose.organization_id_child
261 FROM per_org_structure_elements pose
265 union
262 WHERE pose.org_structure_version_id = GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to)
263 START with pose.organization_id_parent = nvl(p_top_org_id,p_bg_id)
264 CONNECT BY prior organization_id_child = organization_id_parent )
266 (select nvl(p_top_org_id,p_bg_id) from dual))
267 and pap.person_id=nvl(p_person_id,pap.person_id)
268 and ((p_top_org_id is NULL) or (nvl(p_inc_sub_emp,'N') = 'N' and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)=p_top_org_id) or (nvl(p_inc_sub_emp,'N') = 'Y'))
269 and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id) is
270 not null
271 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
272 and hou.business_group_id=p_bg_id
273 and hou1.organization_id = hoi.org_information3
274 and hou.organization_id=hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)
275 and hoi.organization_id=hou.organization_id
276 and hoi.org_information3 IS NOT NULL
277 and hoi.org_information4 IS NOT NULL
278 and hoi.org_information_context='NL_ORG_INFORMATION'
279 and exists(select * from pay_run_results
280 where assignment_action_id=asg_act.assignment_action_id
281 and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),
282 Get_Element_Type_Id('Wage Tax Subsidy Education') ,
283 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
284 ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
285 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
286 --10160211
287 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
288 --10160211
289 )
290 or element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
291 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
292 Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
293 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')
294 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
295 --10160211
296 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
297 --10160211
298 )))
299 order by employer_name , paa.person_id,ppa.payroll_id,ppa.date_earned;
300
301 CURSOR csr_get_wts_elements(l_asg_act_id NUMBER) IS
302 SELECT DISTINCT
303 pap.full_name||' ('||pap.employee_number||')' employee_name,
304 to_char(ppa.date_earned,'MonthYYYY') current_period,
305 paa.assignment_id,
306 pay.payroll_name payroll_name,
307 ppa.date_earned,
308 abs(pay_balance_pkg.get_value(decode(prr.element_type_id,
309 Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Defined_Balance_Id('Wage Tax Subsidy EVC'),
310 --10160211
311 Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),Get_Defined_Balance_Id('Wage Tax Subsidy Increasing Education Level'),
312 --10160211
313 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Defined_Balance_Id('Wage Tax Subsidy Low Wages'),
314 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Defined_Balance_Id('Wage Tax Subsidy Education'),
315 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Defined_Balance_Id('Wage Tax Subsidy Long Term Unemployed'),
316 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Defined_Balance_Id('Wage Tax Subsidy Paid Parental Leave')),asg_act.assignment_action_id)) Wage_Tax_Subsidy,
317 paa.assignment_number,
318 to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
319 prr.Element_Type_Id,decode(prr.element_type_id,
320 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
321 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Education')) ,
322 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
323 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Parental Leave Hours',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'N')) Working_Hours
324 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
325 prr.Element_Type_Id,decode(prr.element_type_id,
326 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
327 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Education')),
328 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
329 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'), Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Part_Time_Percentage
330 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
331 prr.Element_Type_Id,decode(prr.element_type_id,
332 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
333 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
334 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
338 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
335 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Wage_Limit
336 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
337 prr.Element_Type_Id,decode(prr.element_type_id,
339 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Education')),
340 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
341 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Basis_Salary,
342 prr.element_type_id Subsidy_Element_Type_ID,
343 decode(prr.element_type_id,
344 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),1,
345 Get_Element_Type_Id('Wage Tax Subsidy Education'),2,
346 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),3,
347 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),4
348 ,Get_Element_Type_Id('Wage Tax Subsidy EVC'),5
349 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),6 --10160211
350 ) Sequence
351 from
352 per_assignments_f paa,
353 pay_payroll_actions ppa,
354 per_people_f pap,
355 pay_assignment_actions asg_act,
356 pay_run_results prr,
357 pay_all_payrolls_f pay
358 where
359 ppa.business_group_id=p_bg_id
360 and asg_act.assignment_action_id = l_asg_act_id
361 and paa.assignment_id = asg_act.assignment_id
362 and pay.payroll_id = ppa.payroll_id
363 and pap.person_id = paa.person_id
364 and ppa.payroll_action_id = asg_act.payroll_action_id
365 and asg_act.action_status='C'
366 and ppa.action_type in ('R','Q','V','B','I')
367 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
368 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
369 and prr.assignment_action_id=asg_act.assignment_action_id
370 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
371 and prr.element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),
372 Get_Element_Type_Id('Wage Tax Subsidy Education'),
373 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),
374 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
375 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
376 --10160211
377 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
378 --10160211
379 )
380 and exists(select * from pay_run_results where assignment_action_id=asg_act.assignment_action_id and
381 element_type_id in
382 (Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),
383 Get_Element_Type_Id('Wage Tax Subsidy Education')
384 ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),
385 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
386 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
387 --10160211
388 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
389 --10160211
390 ))
391 and (to_number(pay_nl_general.get_run_result_value(asg_act.assignment_action_id,prr.Element_Type_Id,
392 decode(prr.element_type_id,
393 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
394 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
395 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
396 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))
397 ,Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Wage Tax Subsidy EVC'))
398 --10160211
399 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'))
400 --10160211
401 ),
402
403 prr.run_result_id,'M'))) is not null
404 order by employee_name, ppa.date_earned , Sequence;
405
406 CURSOR csr_get_retro_wts_elements(l_asg_act_id NUMBER) IS
407 SELECT DISTINCT
408 pap.full_name||' ('||pap.employee_number||')' employee_name,
409 get_retro_wts(prr.assignment_action_id, prr.element_type_id, nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned))*(-1) Retro_WTS,
410 paa.assignment_number,
411 pay.payroll_name payroll_name,
412 paa.assignment_id,
413 to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
414 decode(prr.element_type_id,
415 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
416 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
417 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
418 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Parental Leave Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'N')) Retro_Working_Hours
422 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
419 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
420 decode(prr.element_type_id,
421 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
423 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
424 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Part_Time_Percentage
425 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
426 decode(prr.element_type_id,
427 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
428 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
429 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
430 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Wage_Limit
431 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
432 decode(prr.element_type_id,
433 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
434 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
435 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
436 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Basis_Salary
437 ,to_char(nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned),'MonthYYYY') Retro_Period
438 ,nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) RDate,
439 nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) Retro_Date,
440 prr.element_type_id Retro_Subsidy_Element_Type_ID,
441 decode(prr.element_type_id,
442 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),1,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),3,
443 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),2,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),4
444 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC'),5
445 --10160211
446 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level'),6
447 --10160211
448 ) Sequence
449 from
450 per_assignments_f paa,
451 per_people_f pap,
452 pay_assignment_actions asg_act,
453 pay_run_results prr,
454 pay_payroll_actions ppa,
455 pay_all_payrolls_f pay
456 where
457 ppa.business_group_id=p_bg_id
458 and paa.assignment_id = asg_act.assignment_id
459 and pap.person_id = paa.person_id
460 and pay.payroll_id = ppa.payroll_id
461 and asg_act.assignment_action_id = l_asg_act_id
462 and ppa.payroll_action_id = asg_act.payroll_action_id
463 and asg_act.action_status='C'
464 and ppa.action_type in ('R','Q','V','B','I')
465 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
466 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
467 and prr.assignment_action_id=asg_act.assignment_action_id
468 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
469 and prr.element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
470 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
471 Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
472 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')
473 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
474 --10160211
475 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
476 --10160211
477 )
478 and exists(select * from pay_run_results where assignment_action_id=asg_act.assignment_action_id and
479 element_type_id
480 in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
481 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
482 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),
483 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')
484 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
485 --10160211
486 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
487 --10160211
488 ) )
489 order by employee_name, RDate , Sequence;
490 v_csr_get_record_details csr_get_record_details%ROWTYPE;
491 v_csr_get_wts_elements csr_get_wts_elements%ROWTYPE;
492 v_csr_get_retro_wts_elements csr_get_retro_wts_elements%ROWTYPE;
493
494 CURSOR csr_get_bg_name(l_bg_id IN NUMBER) is
495 SELECT name FROM per_business_groups
496 WHERE
497 BUSINESS_GROUP_ID = l_bg_id;
498
499 vCtr NUMBER := 0;
500 l_bg_name per_business_groups.NAME%TYPE;
501 l_asg_act_id NUMBER;
502 l_rp_tot_ed_subsidy NUMBER;
503 l_rp_tot_lw_subsidy NUMBER;
507 l_emp_total_subsidy NUMBER;
504 l_rp_tot_ltu_subsidy NUMBER;
505 l_rp_tot_ppl_subsidy NUMBER;
506 l_employer_name VARCHAR2(240);
508 l_subsidy_name VARCHAR2(240);
509 l_sub_employers VARCHAR2(10);
510 l_payroll_id NUMBER := NULL;
511 l_person_id NUMBER := NULL;
512 l_flag BOOLEAN := TRUE;
513 l_emp VARCHAR2(240):= ' ';
514 l_payroll VARCHAR2(240) := ' ';
515 l_period VARCHAR2(240) := ' ';
516 l_format VARCHAR2(40);
517
518 BEGIN
519
520 IF FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS') = ',.' THEN
521 execute immediate ('alter session set nls_numeric_characters ='',.''');
522 ELSE
523 execute immediate ('alter session set nls_numeric_characters =''.,''');
524 END IF;
525 l_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
526 execute immediate 'alter session set nls_date_format = ''' ||l_format ||'''';
527 --hr_utility.trace_on(null,'WTS_bug');
528 hr_utility.set_location('Inside populate_wts_report_data',2000);
529
530 OPEN csr_get_bg_name(p_bg_id);
531 FETCH csr_get_bg_name INTO l_bg_name;
532 CLOSE csr_get_bg_name;
533 hr_utility.set_location('Inside populate_wts_report_data: l_bg_name'||l_bg_name,2040);
534
535 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'BG_NAME';
536 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_bg_name;
537 vCtr := vCtr + 1;
538 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EFF_DATE';
539 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(fnd_date.canonical_to_date(p_eff_date));
540 vCtr := vCtr + 1;
541 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ORG_HIERARCHY';
542 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_org_struct;
543 vCtr := vCtr + 1;
544 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMPLOYER';
545 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_top_org;
546 IF p_inc_sub_emp = 'N' THEN
547 l_sub_employers := 'No';
548
549 ELSIF p_inc_sub_emp = 'Y' THEN
550 l_sub_employers := 'Yes';
551 END IF;
552
553
554 vCtr := vCtr + 1;
555 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUB_EMPLOYERS';
556 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_sub_employers;
557 vCtr := vCtr + 1;
558 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'MONTH_FROM';
559 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(to_date(p_month_from,'MMYYYY'),'Month YYYY');
560 vCtr := vCtr + 1;
561 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'MONTH_TO';
562 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')),'Month YYYY');
563 vCtr := vCtr + 1;
564 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
565 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_employee;
566 l_rp_tot_ed_subsidy := 0;
567 l_rp_tot_lw_subsidy := 0;
568 l_rp_tot_ltu_subsidy:= 0;
569 l_rp_tot_ppl_subsidy:= 0;
570 l_employer_name := ' ';
571 l_emp_total_subsidy := 0;
572
573 FOR v_csr_get_record_details IN csr_get_record_details
574 LOOP
575 hr_utility.set_location('Inside populate_wts_report_data: Each Record: Employer: '||v_csr_get_record_details.employer_name,2050);
576 IF ((NVL(l_payroll_id,-1) <> v_csr_get_record_details.payroll_id
577 AND l_payroll_id IS NOT NULL )
578 OR (NVL(l_person_id,-1)<> v_csr_get_record_details.person_id
579 AND l_person_id IS NOT NULL)) AND l_flag = FALSE THEN
580 vCtr := vCtr + 1;
581 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_TOTAL';
582 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_emp_total_subsidy,2),'99G999G999D90MI'); /*Bug 4506936*/
583 l_emp_total_subsidy:=0;
584 vCtr := vCtr + 1;
585 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
586 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
587 l_emp := ' ';
588 l_payroll := ' ';
589 l_period := ' ';
590 END IF;
591
592 IF v_csr_get_record_details.employer_name <> l_employer_name AND l_flag = FALSE THEN
593 vCtr := vCtr + 1;
594 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
595 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
596 END IF;
597 l_flag := FALSE;
598 IF v_csr_get_record_details.employer_name <> l_employer_name THEN
599 vCtr := vCtr + 1;
600 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
601 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
602 vCtr := vCtr + 1;
603 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMPLOYER1';
604 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.employer_name;
605 vCtr := vCtr + 1;
606 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_OFFICE1';
607 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.name;
608 vCtr := vCtr + 1;
609 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_REG_NUM1';
610 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.tax_reg;
611 END IF;
612 IF NVL(l_payroll_id,-1) <> v_csr_get_record_details.payroll_id
613 OR NVL(l_person_id,-1) <>v_csr_get_record_details.person_id THEN
614 vCtr := vCtr + 1;
615 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
616 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
617 END IF;
618 l_asg_act_id := GET_LAST_ASG_ACT_ID(v_csr_get_record_details.person_id,v_csr_get_record_details.payroll_id,v_csr_get_record_details.date_earned);
619
620 FOR v_csr_get_wts_elements
621 IN csr_get_wts_elements(l_asg_act_id)
622 LOOP
623 -- IF v_csr_get_wts_elements.Wage_Tax_Subsidy <> 0 THEN
624 l_subsidy_name := GET_SUBSIDY_TYPE_NAME(v_csr_get_wts_elements.Subsidy_Element_Type_ID);
625 vCtr := vCtr + 1;
626 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
627 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
628
629 IF l_emp <> v_csr_get_wts_elements.employee_name THEN
633 l_emp := v_csr_get_wts_elements.employee_name;
630 vCtr := vCtr + 1;
631 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
632 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.employee_name;
634 ELSE
635 vCtr := vCtr + 1;
636 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
637 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
638 END IF;
639
640 IF l_payroll <> v_csr_get_wts_elements.payroll_name THEN
641 vCtr := vCtr + 1;
642 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
643 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.payroll_name;
644 l_payroll := v_csr_get_wts_elements.payroll_name ;
645 ELSE
646 vCtr := vCtr + 1;
647 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
648 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
649 END IF;
650
651 IF l_period <> v_csr_get_wts_elements.current_period THEN
652 vCtr := vCtr + 1;
653 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
654 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.current_period;
655 l_period := v_csr_get_wts_elements.current_period;
656 ELSE
657 vCtr := vCtr + 1;
658 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
659 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
660 END IF;
661
662 vCtr := vCtr + 1;
663 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY_TYPE';
664 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_subsidy_name;
665 vCtr := vCtr + 1;
666 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_SALARY';
667 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Basis_Salary,2),'99G999G999D90MI');
668 vCtr := vCtr + 1;
669 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PART_TIME_PERC';
670 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Part_Time_Percentage,4),'999D9990MI');
671 vCtr := vCtr + 1;
672 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WORKING_LEAVE_HRS';
673 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Working_Hours,2),'999D90MI');
674 vCtr := vCtr + 1;
675 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WAGE_LIMIT';
676 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Wage_Limit,2),'99G999D90MI');
677 vCtr := vCtr + 1;
678 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY';
679 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2),'99G999D90MI');
680 vCtr := vCtr + 1;
681 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
682 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
683 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LOW_WAGES') THEN
684 l_rp_tot_lw_subsidy := l_rp_tot_lw_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
685 END IF;
686 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','EDUCATION') THEN
687 l_rp_tot_ed_subsidy := l_rp_tot_ed_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
688 END IF;
689 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LONG_TERM_UNEMP') THEN
690 l_rp_tot_ltu_subsidy := l_rp_tot_ltu_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
691 END IF;
692 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','PAID_PARENTAL_LEAVE') THEN
693 l_rp_tot_ppl_subsidy := l_rp_tot_ppl_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
694 END IF;
695 l_emp_total_subsidy:= l_emp_total_subsidy+ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
696 -- END IF;
697 END LOOP;
698 FOR v_csr_get_retro_wts_elements
699 IN csr_get_retro_wts_elements(l_asg_act_id)
700 LOOP
701 l_subsidy_name := GET_SUBSIDY_TYPE_NAME(v_csr_get_retro_wts_elements.Retro_Subsidy_Element_Type_ID); /* Bug 4517173*/
702 vCtr := vCtr + 1;
703 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
704 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
705
706 IF l_emp <> v_csr_get_retro_wts_elements.employee_name THEN
707 vCtr := vCtr + 1;
708 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
709 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.employee_name;
710 l_emp := v_csr_get_retro_wts_elements.employee_name;
711 ELSE
712 vCtr := vCtr + 1;
713 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
714 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
715 END IF;
716
717 IF l_payroll <> v_csr_get_retro_wts_elements.payroll_name THEN
718 vCtr := vCtr + 1;
719 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
720 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.payroll_name;
721 l_payroll := v_csr_get_retro_wts_elements.payroll_name;
722 ELSE
723 vCtr := vCtr + 1;
724 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
725 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
726 END IF;
727
728 IF l_period <> v_csr_get_retro_wts_elements.Retro_Period THEN
729 vCtr := vCtr + 1;
730 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
731 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.Retro_Period;
732 l_period := v_csr_get_retro_wts_elements.Retro_Period;
733 ELSE
734 vCtr := vCtr + 1;
735 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
736 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
737 END IF;
738
739 vCtr := vCtr + 1;
740 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY_TYPE';
741 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := GET_SUBSIDY_TYPE_NAME(v_csr_get_retro_wts_elements.Retro_Subsidy_Element_Type_ID);
742 vCtr := vCtr + 1;
743 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_SALARY';
747 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Part_Time_Percentage,4),'999D9990MI');
744 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Basis_Salary,2),'99G999G999D90MI');
745 vCtr := vCtr + 1;
746 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PART_TIME_PERC';
748 vCtr := vCtr + 1;
749 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WORKING_LEAVE_HRS';
750 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Working_Hours,2),'999G999D90MI');
751 vCtr := vCtr + 1;
752 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WAGE_LIMIT';
753 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Wage_Limit,2),'999G999D90MI');
754 vCtr := vCtr + 1;
755 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY';
756 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2),'999G999D90');
757 vCtr := vCtr + 1;
758 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
759 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
760 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LOW_WAGES') THEN
761 l_rp_tot_lw_subsidy := l_rp_tot_lw_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
762 END IF;
763 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','EDUCATION') THEN
764 l_rp_tot_ed_subsidy := l_rp_tot_ed_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
765 END IF;
766 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LONG_TERM_UNEMP') THEN
767 l_rp_tot_ltu_subsidy := l_rp_tot_ltu_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
768 END IF;
769 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','PAID_PARENTAL_LEAVE') THEN
770 l_rp_tot_ppl_subsidy := l_rp_tot_ppl_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
771 END IF;
772 l_emp_total_subsidy := l_emp_total_subsidy+ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
773 END LOOP;
774
775 l_person_id := v_csr_get_record_details.person_id;
776 l_payroll_id := v_csr_get_record_details.payroll_id;
777 l_employer_name := v_csr_get_record_details.employer_name;
778 END LOOP;
779
780 IF l_flag = FALSE THEN
781 vCtr := vCtr + 1;
782 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_TOTAL';
783 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_emp_total_subsidy,2),'999G999D90MI');
784 l_emp_total_subsidy:=0;
785 vCtr := vCtr + 1;
786 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
787 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
788
789 hr_utility.set_location('Outside populate_wts_report_data: Employer Loop',2100);
790 vCtr := vCtr + 1;
791 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
792 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
793 END IF;
794 vCtr := vCtr + 1;
795 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_LOW_WAGES';
796 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_lw_subsidy,2),'99G999G999G999D90MI');
797 vCtr := vCtr + 1;
798 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_EDUCATION';
799 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ed_subsidy,2),'99G999G999G999D90MI');
800 vCtr := vCtr + 1;
801 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_LONG_TERM';
802 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ltu_subsidy,2),'99G999G999G999D90MI');
803 vCtr := vCtr + 1;
804 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_PARENTAL';
805 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ppl_subsidy,2),'99G999G999G999D90MI');
806
807 hr_utility.set_location('Outside populate_wts_report_data: WritetoCLOB_rtf',2150);
808 pay_nl_xdo_Report.WritetoCLOB_rtf(p_xfdf_blob );
809
810 END populate_wts_report_data;
811
812
813 /*-------------------------------------------------------------------------------------
814 Procedure to generate XML data for WTS Report for PYXMLEMG
815 --------------------------------------------------------------------------------------*/
816 procedure populate_wts_report_data_1(p_bg_id IN NUMBER,
817 p_eff_date IN VARCHAR2,
818 p_month_from IN VARCHAR2,
819 p_month_to IN VARCHAR2,
820 p_org_struct_id IN NUMBER,
821 p_org_struct IN VARCHAR2,
822 p_top_org_id IN NUMBER,
823 p_top_org IN VARCHAR2,
824 p_person_id IN NUMBER,
825 p_employee IN VARCHAR2,
826 p_inc_sub_emp IN VARCHAR2,
827 p_dummy_employer IN VARCHAR2,
828 p_template_name IN VARCHAR2,
829 p_xml OUT NOCOPY CLOB) IS
830
831 CURSOR csr_get_record_details is
832 SELECT DISTINCT
833 hou.name employer_name,
834 pap.full_name||'('||pap.employee_number||')' employee,
835 hoi.org_information3 tax_office_id,
836 hoi.org_information4 tax_reg,
837 ppa.business_group_id business_group_id,
838 hou1.name,
839 paa.person_id,
840 ppa.date_earned,
841 ppa.payroll_id
842 from
843 per_assignments_f paa,
844 pay_payroll_actions ppa,
845 per_people_f pap,
846 hr_organization_units hou,
847 hr_organization_units hou1,
848 hr_organization_information hoi,
849 pay_assignment_actions asg_act
850 where
851 ppa.business_group_id=p_bg_id
852 and paa.assignment_id = asg_act.assignment_id
853 and pap.person_id = paa.person_id
854 and ppa.payroll_action_id = asg_act.payroll_action_id
855 and asg_act.action_status='C'
859 and paa.organization_id in
856 and ppa.action_type in ('R','Q','V','B','I')
857 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
858 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
860 ((SELECT pose.organization_id_child
861 FROM per_org_structure_elements pose
862 WHERE pose.org_structure_version_id = GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to)
863 START with pose.organization_id_parent = nvl(p_top_org_id,p_bg_id)
864 CONNECT BY prior organization_id_child = organization_id_parent )
865 union
866 (select nvl(p_top_org_id,p_bg_id) from dual))
867 and pap.person_id=nvl(p_person_id,pap.person_id)
868 and ((p_top_org_id is NULL) or (nvl(p_inc_sub_emp,'N') = 'N' and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)=p_top_org_id) or (nvl(p_inc_sub_emp,'N') = 'Y'))
869 and hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id) is
870 not null
871 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
872 and hou.business_group_id=p_bg_id
873 and hou1.organization_id = hoi.org_information3
874 and hou.organization_id=hr_nl_org_info.get_tax_org_id(GET_ORG_STRUCT_VERSION_ID(p_org_struct_id,p_month_to),paa.organization_id)
875 and hoi.organization_id=hou.organization_id
876 and hoi.org_information3 IS NOT NULL
877 and hoi.org_information4 IS NOT NULL
878 and hoi.org_information_context='NL_ORG_INFORMATION'
879 and exists(select *
880 from pay_run_results
881 where assignment_action_id=asg_act.assignment_action_id
882 and (element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
883 ,Get_Element_Type_Id('Wage Tax Subsidy Education')
884 ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
885 ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
886 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
887 --10160211
888 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
889 --10160211
890 )
891 or element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')
892 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')
893 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education')
894 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave')
895 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
896 --10160211
897 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
898 --10160211
899 )
900 )
901 )
902 order by employer_name , paa.person_id,ppa.payroll_id,ppa.date_earned;
903
904 CURSOR csr_get_wts_elements(l_asg_act_id NUMBER) IS
905 SELECT DISTINCT
906 pap.full_name||' ('||pap.employee_number||')' employee_name,
907 to_char(ppa.date_earned,'MonthYYYY') current_period,
908 paa.assignment_id,
909 pay.payroll_name payroll_name,
910 ppa.date_earned,
911 abs(pay_balance_pkg.get_value(decode(prr.element_type_id,
912 Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Defined_Balance_Id('Wage Tax Subsidy EVC'),
913 --10160211
914 Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),Get_Defined_Balance_Id('Wage Tax Subsidy Increasing Education Level'),
915 --10160211
916 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Defined_Balance_Id('Wage Tax Subsidy Low Wages'),
917 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Defined_Balance_Id('Wage Tax Subsidy Education'),
918 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Defined_Balance_Id('Wage Tax Subsidy Long Term Unemployed'),
919 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Defined_Balance_Id('Wage Tax Subsidy Paid Parental Leave')),asg_act.assignment_action_id)) Wage_Tax_Subsidy,
920 paa.assignment_number,
921 to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
922 prr.Element_Type_Id,decode(prr.element_type_id,
923 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
924 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Education')) ,
925 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
926 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Parental Leave Hours',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'N')) Working_Hours
927 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
928 prr.Element_Type_Id,decode(prr.element_type_id,
929 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
930 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Education')),
931 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
932 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'), Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Part_Time_Percentage
933 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
934 prr.Element_Type_Id,decode(prr.element_type_id,
935 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
936 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
940 prr.Element_Type_Id,decode(prr.element_type_id,
937 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
938 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Wage_Limit
939 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,
941 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
942 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Education')),
943 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
944 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'))),prr.run_result_id,'M')) Basis_Salary,
945 prr.element_type_id Subsidy_Element_Type_ID,
946 decode(prr.element_type_id
947 ,Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),1
948 ,Get_Element_Type_Id('Wage Tax Subsidy Education'),2
949 ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),3
950 ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),4
951 ,Get_Element_Type_Id('Wage Tax Subsidy EVC'),5
952 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),6 --10160211
953
954 ) Sequence
955 from
956 per_assignments_f paa,
957 pay_payroll_actions ppa,
958 per_people_f pap,
959 pay_assignment_actions asg_act,
960 pay_run_results prr,
961 pay_all_payrolls_f pay
962 where
963 ppa.business_group_id=p_bg_id
964 and asg_act.assignment_action_id = l_asg_act_id
965 and paa.assignment_id = asg_act.assignment_id
966 and pay.payroll_id = ppa.payroll_id
967 and pap.person_id = paa.person_id
968 and ppa.payroll_action_id = asg_act.payroll_action_id
969 and asg_act.action_status='C'
970 and ppa.action_type in ('R','Q','V','B','I')
971 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
972 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
973 and prr.assignment_action_id=asg_act.assignment_action_id
974 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
975 and prr.element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
976 ,Get_Element_Type_Id('Wage Tax Subsidy Education')
977 ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
978 ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
979 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
980 --10160211
981 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
982 --10160211
983 )
984 and exists(select *
985 from pay_run_results
986 where assignment_action_id=asg_act.assignment_action_id
987 and element_type_id in (Get_Element_Type_Id('Wage Tax Subsidy Low Wages')
988 ,Get_Element_Type_Id('Wage Tax Subsidy Education')
989 ,Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')
990 ,Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')
991 ,Get_Element_Type_Id('Wage Tax Subsidy EVC')
992 --10160211
993 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level')
994 --10160211
995 )
996 )
997 and (to_number(pay_nl_general.get_run_result_value(asg_act.assignment_action_id
998 ,prr.Element_Type_Id
999 ,decode(prr.element_type_id,
1000 Get_Element_Type_Id('Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Low Wages')),
1001 Get_Element_Type_Id('Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Education')),
1002 Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Long Term Unemployed')),
1003 Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Wage Tax Subsidy Paid Parental Leave')),
1004 Get_Element_Type_Id('Wage Tax Subsidy EVC'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Wage Tax Subsidy EVC'))
1005 --10160211
1006 ,Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'),Get_Input_Value_Id('Pay Value',Get_Element_Type_Id('Wage Tax Subsidy Increasing Education Level'))
1007 --10160211
1008 )
1009 ,prr.run_result_id
1010 ,'M'
1011 )
1012 )
1013 ) is not null
1014 order by employee_name, ppa.date_earned , Sequence;
1015
1016 CURSOR csr_get_retro_wts_elements(l_asg_act_id NUMBER) IS
1017 SELECT DISTINCT
1018 pap.full_name||' ('||pap.employee_number||')' employee_name,
1019 get_retro_wts(prr.assignment_action_id, prr.element_type_id, nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned))*(-1) Retro_WTS,
1020 paa.assignment_number,
1021 pay.payroll_name payroll_name,
1022 paa.assignment_id,
1023 to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
1024 decode(prr.element_type_id,
1025 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
1029 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
1026 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
1027 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Working Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
1028 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_value_Id('Parental Leave Hours',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'N')) Retro_Working_Hours
1030 decode(prr.element_type_id,
1031 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
1032 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
1033 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
1034 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Part time Percentage',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Part_Time_Percentage
1035 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
1036 decode(prr.element_type_id,
1037 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
1038 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
1039 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
1040 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Wage Limit',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Wage_Limit
1041 ,to_number(pay_nl_general.GET_RUN_RESULT_VALUE(prr.assignment_action_id,prr.Element_Type_Id,
1042 decode(prr.element_type_id,
1043 Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages')),
1044 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed')),
1045 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Education')),
1046 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),Get_Input_Value_Id('Basis Salary',Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'))), prr.run_result_id,'M')) Retro_Basis_Salary
1047 ,to_char(nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned),'MonthYYYY') Retro_Period
1048 ,nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) RDate,
1049 nvl(pay_nl_general.get_retro_period(prr.source_id,ppa.date_earned),ppa.date_earned) Retro_Date,
1050 prr.element_type_id Retro_Subsidy_Element_Type_ID,
1051 decode(prr.element_type_id
1052 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),1
1053 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),3
1054 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),2
1055 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),4
1056 ,Get_Element_Type_Id('Retro Wage Tax Subsidy EVC'),5
1057 --10160211
1058 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level'),6
1059 --10160211
1060 ) Sequence
1061 from
1062 per_assignments_f paa,
1063 per_people_f pap,
1064 pay_assignment_actions asg_act,
1065 pay_run_results prr,
1066 pay_payroll_actions ppa,
1067 pay_all_payrolls_f pay
1068 where
1069 ppa.business_group_id=p_bg_id
1070 and paa.assignment_id = asg_act.assignment_id
1071 and pap.person_id = paa.person_id
1072 and pay.payroll_id = ppa.payroll_id
1073 and asg_act.assignment_action_id = l_asg_act_id
1074 and ppa.payroll_action_id = asg_act.payroll_action_id
1075 and asg_act.action_status='C'
1076 and ppa.action_type in ('R','Q','V','B','I')
1077 and ppa.date_earned between to_date(p_month_from,'MMYYYY') AND LAST_DAY(to_date(P_MONTH_TO,'MMYYYY'))
1078 and ppa.date_earned between paa.effective_start_date and paa.effective_end_date
1079 and prr.assignment_action_id=asg_act.assignment_action_id
1080 and LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')) between pap.effective_start_date and pap.effective_end_date
1081 and prr.element_type_id in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
1082 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
1083 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),
1084 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),
1085 Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
1086 --10160211
1087 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
1088 --10160211
1089 )
1090 and exists(select *
1091 from pay_run_results
1092 where assignment_action_id=asg_act.assignment_action_id
1093 and element_type_id
1094 in (Get_Element_Type_Id('Retro Wage Tax Subsidy Low Wages'),
1095 Get_Element_Type_Id('Retro Wage Tax Subsidy Long Term Unemployed'),
1096 Get_Element_Type_Id('Retro Wage Tax Subsidy Education'),
1097 Get_Element_Type_Id('Retro Wage Tax Subsidy Paid Parental Leave'),
1098 Get_Element_Type_Id('Retro Wage Tax Subsidy EVC')
1099 --10160211
1100 ,Get_Element_Type_Id('Retro Wage Tax Subsidy Increasing Education Level')
1101 --10160211
1105 v_csr_get_record_details csr_get_record_details%ROWTYPE;
1102 )
1103 )
1104 order by employee_name, RDate , Sequence;
1106 v_csr_get_wts_elements csr_get_wts_elements%ROWTYPE;
1107 v_csr_get_retro_wts_elements csr_get_retro_wts_elements%ROWTYPE;
1108
1109 CURSOR csr_get_bg_name(l_bg_id IN NUMBER) is
1110 SELECT name FROM per_business_groups
1111 WHERE
1112 BUSINESS_GROUP_ID = l_bg_id;
1113
1114 vCtr NUMBER := 0;
1115 l_bg_name per_business_groups.NAME%TYPE;
1116 l_asg_act_id NUMBER;
1117 l_rp_tot_ed_subsidy NUMBER;
1118 l_rp_tot_lw_subsidy NUMBER;
1119 l_rp_tot_ltu_subsidy NUMBER;
1120 l_rp_tot_ppl_subsidy NUMBER;
1121 l_employer_name VARCHAR2(240);
1122 l_emp_total_subsidy NUMBER;
1123 l_subsidy_name VARCHAR2(240);
1124 l_sub_employers VARCHAR2(10);
1125 l_payroll_id NUMBER := NULL;
1126 l_person_id NUMBER := NULL;
1127 l_flag BOOLEAN := TRUE;
1128 l_emp VARCHAR2(240):= ' ';
1129 l_payroll VARCHAR2(240) := ' ';
1130 l_period VARCHAR2(240) := ' ';
1131 l_format VARCHAR2(40);
1132
1133 BEGIN
1134
1135 IF FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS') = ',.' THEN
1136 execute immediate ('alter session set nls_numeric_characters ='',.''');
1137 ELSE
1138 execute immediate ('alter session set nls_numeric_characters =''.,''');
1139 END IF;
1140 l_format := FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK');
1141 execute immediate 'alter session set nls_date_format = ''' ||l_format ||'''';
1142 --hr_utility.trace_on(null,'WTS_bug');
1143 hr_utility.set_location('Inside populate_wts_report_data',2000);
1144
1145 OPEN csr_get_bg_name(p_bg_id);
1146 FETCH csr_get_bg_name INTO l_bg_name;
1147 CLOSE csr_get_bg_name;
1148 hr_utility.set_location('Inside populate_wts_report_data: l_bg_name'||l_bg_name,2040);
1149
1150 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'BG_NAME';
1151 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_bg_name;
1152 vCtr := vCtr + 1;
1153 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EFF_DATE';
1154 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(fnd_date.canonical_to_date(p_eff_date));
1155 vCtr := vCtr + 1;
1156 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ORG_HIERARCHY';
1157 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_org_struct;
1158 vCtr := vCtr + 1;
1159 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMPLOYER';
1160 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_top_org;
1161 IF p_inc_sub_emp = 'N' THEN
1162 l_sub_employers := 'No';
1163
1164 ELSIF p_inc_sub_emp = 'Y' THEN
1165 l_sub_employers := 'Yes';
1166 END IF;
1167
1168
1169 vCtr := vCtr + 1;
1170 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUB_EMPLOYERS';
1171 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_sub_employers;
1172 vCtr := vCtr + 1;
1173 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'MONTH_FROM';
1174 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(to_date(p_month_from,'MMYYYY'),'Month YYYY');
1175 vCtr := vCtr + 1;
1176 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'MONTH_TO';
1177 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(LAST_DAY(to_date(P_MONTH_TO,'MMYYYY')),'Month YYYY');
1178 vCtr := vCtr + 1;
1179 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
1180 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_employee;
1181 l_rp_tot_ed_subsidy := 0;
1182 l_rp_tot_lw_subsidy := 0;
1183 l_rp_tot_ltu_subsidy:= 0;
1184 l_rp_tot_ppl_subsidy:= 0;
1185 l_employer_name := ' ';
1186 l_emp_total_subsidy := 0;
1187
1188 FOR v_csr_get_record_details IN csr_get_record_details
1189 LOOP
1190 hr_utility.set_location('Inside populate_wts_report_data: Each Record: Employer: '||v_csr_get_record_details.employer_name,2050);
1191 IF ((NVL(l_payroll_id,-1) <> v_csr_get_record_details.payroll_id
1192 AND l_payroll_id IS NOT NULL )
1193 OR (NVL(l_person_id,-1)<> v_csr_get_record_details.person_id
1194 AND l_person_id IS NOT NULL)) AND l_flag = FALSE THEN
1195 vCtr := vCtr + 1;
1196 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_TOTAL';
1197 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_emp_total_subsidy,2),'99G999G999D90MI'); /*Bug 4506936*/
1198 l_emp_total_subsidy:=0;
1199 vCtr := vCtr + 1;
1200 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
1201 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1202 l_emp := ' ';
1203 l_payroll := ' ';
1204 l_period := ' ';
1205 END IF;
1206
1207 IF v_csr_get_record_details.employer_name <> l_employer_name AND l_flag = FALSE THEN
1208 vCtr := vCtr + 1;
1209 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
1210 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1211 END IF;
1212 l_flag := FALSE;
1213 IF v_csr_get_record_details.employer_name <> l_employer_name THEN
1214 vCtr := vCtr + 1;
1215 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
1216 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
1217 vCtr := vCtr + 1;
1218 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMPLOYER1';
1219 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.employer_name;
1220 vCtr := vCtr + 1;
1221 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_OFFICE1';
1222 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.name;
1223 vCtr := vCtr + 1;
1224 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_REG_NUM1';
1225 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_record_details.tax_reg;
1226 END IF;
1227 IF NVL(l_payroll_id,-1) <> v_csr_get_record_details.payroll_id
1228 OR NVL(l_person_id,-1) <>v_csr_get_record_details.person_id THEN
1229 vCtr := vCtr + 1;
1230 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
1231 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
1232 END IF;
1236 IN csr_get_wts_elements(l_asg_act_id)
1233 l_asg_act_id := GET_LAST_ASG_ACT_ID(v_csr_get_record_details.person_id,v_csr_get_record_details.payroll_id,v_csr_get_record_details.date_earned);
1234
1235 FOR v_csr_get_wts_elements
1237 LOOP
1238 -- IF v_csr_get_wts_elements.Wage_Tax_Subsidy <> 0 THEN
1239 l_subsidy_name := GET_SUBSIDY_TYPE_NAME(v_csr_get_wts_elements.Subsidy_Element_Type_ID);
1240 vCtr := vCtr + 1;
1241 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1242 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
1243
1244 IF l_emp <> v_csr_get_wts_elements.employee_name THEN
1245 vCtr := vCtr + 1;
1246 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
1247 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.employee_name;
1248 l_emp := v_csr_get_wts_elements.employee_name;
1249 ELSE
1250 vCtr := vCtr + 1;
1251 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
1252 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1253 END IF;
1254
1255 IF l_payroll <> v_csr_get_wts_elements.payroll_name THEN
1256 vCtr := vCtr + 1;
1257 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
1258 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.payroll_name;
1259 l_payroll := v_csr_get_wts_elements.payroll_name ;
1260 ELSE
1261 vCtr := vCtr + 1;
1262 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
1263 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1264 END IF;
1265
1266 IF l_period <> v_csr_get_wts_elements.current_period THEN
1267 vCtr := vCtr + 1;
1268 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
1269 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_wts_elements.current_period;
1270 l_period := v_csr_get_wts_elements.current_period;
1271 ELSE
1272 vCtr := vCtr + 1;
1273 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
1274 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1275 END IF;
1276
1277 vCtr := vCtr + 1;
1278 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY_TYPE';
1279 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_subsidy_name;
1280 vCtr := vCtr + 1;
1281 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_SALARY';
1282 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Basis_Salary,2),'99G999G999D90MI');
1283 vCtr := vCtr + 1;
1284 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PART_TIME_PERC';
1285 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Part_Time_Percentage,4),'999D9990MI');
1286 vCtr := vCtr + 1;
1287 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WORKING_LEAVE_HRS';
1288 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Working_Hours,2),'999D90MI');
1289 vCtr := vCtr + 1;
1290 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WAGE_LIMIT';
1291 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Wage_Limit,2),'99G999D90MI');
1292 vCtr := vCtr + 1;
1293 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY';
1294 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2),'99G999D90MI');
1295 vCtr := vCtr + 1;
1296 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1297 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1298
1299 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LOW_WAGES') THEN
1300 l_rp_tot_lw_subsidy := l_rp_tot_lw_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
1301 END IF;
1302 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','EDUCATION') THEN
1303 l_rp_tot_ed_subsidy := l_rp_tot_ed_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
1304 END IF;
1305 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LONG_TERM_UNEMP') THEN
1306 l_rp_tot_ltu_subsidy := l_rp_tot_ltu_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
1307 END IF;
1308 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','PAID_PARENTAL_LEAVE') THEN
1309 l_rp_tot_ppl_subsidy := l_rp_tot_ppl_subsidy + ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
1310 END IF;
1311 l_emp_total_subsidy:= l_emp_total_subsidy+ROUND(v_csr_get_wts_elements.Wage_Tax_Subsidy,2);
1312 -- END IF;
1313 END LOOP;
1314 FOR v_csr_get_retro_wts_elements
1315 IN csr_get_retro_wts_elements(l_asg_act_id)
1316 LOOP
1317 l_subsidy_name := GET_SUBSIDY_TYPE_NAME(v_csr_get_retro_wts_elements.Retro_Subsidy_Element_Type_ID); /* Bug 4517173*/
1318 vCtr := vCtr + 1;
1319 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1320 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
1321
1322 IF l_emp <> v_csr_get_retro_wts_elements.employee_name THEN
1323 vCtr := vCtr + 1;
1324 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
1325 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.employee_name;
1326 l_emp := v_csr_get_retro_wts_elements.employee_name;
1327 ELSE
1328 vCtr := vCtr + 1;
1329 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_NAME';
1330 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1331 END IF;
1332
1333 IF l_payroll <> v_csr_get_retro_wts_elements.payroll_name THEN
1334 vCtr := vCtr + 1;
1335 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
1336 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.payroll_name;
1337 l_payroll := v_csr_get_retro_wts_elements.payroll_name;
1338 ELSE
1339 vCtr := vCtr + 1;
1340 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL';
1341 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1342 END IF;
1343
1344 IF l_period <> v_csr_get_retro_wts_elements.Retro_Period THEN
1345 vCtr := vCtr + 1;
1346 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
1347 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_csr_get_retro_wts_elements.Retro_Period;
1348 l_period := v_csr_get_retro_wts_elements.Retro_Period;
1349 ELSE
1350 vCtr := vCtr + 1;
1351 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PERIOD';
1352 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := NULL;
1353 END IF;
1354
1355 vCtr := vCtr + 1;
1356 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY_TYPE';
1357 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := GET_SUBSIDY_TYPE_NAME(v_csr_get_retro_wts_elements.Retro_Subsidy_Element_Type_ID);
1358 vCtr := vCtr + 1;
1359 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAX_SALARY';
1360 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Basis_Salary,2),'99G999G999D90MI');
1361 vCtr := vCtr + 1;
1362 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PART_TIME_PERC';
1363 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Part_Time_Percentage,4),'999D9990MI');
1364 vCtr := vCtr + 1;
1365 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WORKING_LEAVE_HRS';
1366 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Working_Hours,2),'999G999D90MI');
1367 vCtr := vCtr + 1;
1368 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'WAGE_LIMIT';
1369 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_Wage_Limit,2),'999G999D90MI');
1370 vCtr := vCtr + 1;
1371 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUBSIDY';
1372 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2),'999G999D90');
1373 vCtr := vCtr + 1;
1374 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMP_REC';
1375 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1376 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LOW_WAGES') THEN
1377 l_rp_tot_lw_subsidy := l_rp_tot_lw_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
1378 END IF;
1379 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','EDUCATION') THEN
1380 l_rp_tot_ed_subsidy := l_rp_tot_ed_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
1381 END IF;
1382 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','LONG_TERM_UNEMP') THEN
1383 l_rp_tot_ltu_subsidy := l_rp_tot_ltu_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
1384 END IF;
1385 IF l_subsidy_name = hr_general.decode_lookup('NL_FORM_LABELS','PAID_PARENTAL_LEAVE') THEN
1386 l_rp_tot_ppl_subsidy := l_rp_tot_ppl_subsidy + ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
1387 END IF;
1388 l_emp_total_subsidy := l_emp_total_subsidy+ROUND(v_csr_get_retro_wts_elements.Retro_WTS,2);
1389 END LOOP;
1390
1391 l_person_id := v_csr_get_record_details.person_id;
1392 l_payroll_id := v_csr_get_record_details.payroll_id;
1393 l_employer_name := v_csr_get_record_details.employer_name;
1394 END LOOP;
1395
1396 IF l_flag = FALSE THEN
1397 vCtr := vCtr + 1;
1398 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMP_TOTAL';
1399 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_emp_total_subsidy,2),'999G999D90MI');
1400 l_emp_total_subsidy:=0;
1401 vCtr := vCtr + 1;
1402 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
1403 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1404
1405 hr_utility.set_location('Outside populate_wts_report_data: Employer Loop',2100);
1406 vCtr := vCtr + 1;
1407 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYER';
1408 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
1409 END IF;
1410 vCtr := vCtr + 1;
1411 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_LOW_WAGES';
1412 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_lw_subsidy,2),'99G999G999G999D90MI');
1413 vCtr := vCtr + 1;
1414 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_EDUCATION';
1415 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ed_subsidy,2),'99G999G999G999D90MI');
1416 vCtr := vCtr + 1;
1417 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_LONG_TERM';
1418 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ltu_subsidy,2),'99G999G999G999D90MI');
1419 vCtr := vCtr + 1;
1420 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REP_TOT_PARENTAL';
1421 PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := TO_CHAR(ROUND(l_rp_tot_ppl_subsidy,2),'99G999G999G999D90MI');
1422
1423 hr_utility.set_location('Outside populate_wts_report_data: WritetoCLOB_rtf',2150);
1424 pay_nl_xdo_Report.WritetoCLOB_rtf_1(p_xml);
1425
1426 END populate_wts_report_data_1;
1427
1428
1429
1430
1431 PROCEDURE record_4712(p_file_id NUMBER) IS
1432 l_upload_name VARCHAR2(1000);
1433 l_file_name VARCHAR2(1000);
1434 l_start_date DATE := TO_DATE('01/01/0001', 'dd/mm/yyyy');
1435 l_end_date DATE := TO_DATE('31/12/4712', 'dd/mm/yyyy');
1436 BEGIN
1437 -- program_name will be used to store the file_name
1438 -- this is bcos the file_name in fnd_lobs contains
1439 -- the full patch of the doc and not just the file name
1440 SELECT program_name
1441 INTO l_file_name
1442 FROM fnd_lobs
1443 WHERE file_id = p_file_id;
1444 -- the delete will ensure that the patch is rerunnable
1445 DELETE FROM per_gb_xdo_templates
1446 WHERE file_name = l_file_name AND
1447 effective_start_date = l_start_date AND
1448 effective_end_date = l_end_date;
1449 INSERT INTO per_gb_xdo_templates
1450 (file_id,
1451 file_name,
1452 file_description,
1453 effective_start_date,
1454 effective_end_date)
1455 SELECT p_file_id, l_file_name, 'Template for year 0001-4712',
1456 l_start_date, l_end_date
1457 FROM fnd_lobs
1458 WHERE file_id = p_file_id;
1459 END;
1460 END PAY_NL_WTS_REPORT;