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