eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PAY_NL_IZA_REPORT

Source


1 PACKAGE BODY PAY_NL_IZA_REPORT AS
2 /* $Header: paynliza.pkb 120.0 2005/05/29 02:40:37 appldev noship $ */
3 
4 level_cnt NUMBER;
5 
6 /*Counter for accessing the values in PAY_NL_XDO_REPORT.vXMLTable*/
7 vCtr NUMBER;
8 
9 /*-------------------------------------------------------------------------------
10 |Name           : populate_iza_report_data                                      |
11 |Type		: Procedure						        |
12 |Description    : Procedure to generate the Annual Tax Statement Report         |
13 ------------------------------------------------------------------------------*/
14 
15 procedure populate_iza_report_data(p_bg_id IN NUMBER,
16                                    p_bg_name IN VARCHAR2,
17                                    p_eff_date IN VARCHAR2,
18                                    p_org_struct_id IN NUMBER,
19                                    p_org_struct IN VARCHAR2,
20                                    p_process_month IN VARCHAR2,
21                                    p_employer_id IN NUMBER,
22                                    p_employer IN VARCHAR2,
23                                    p_xfdf_blob OUT NOCOPY BLOB) IS
24 
25 
26 /*Cursors to fetch required data */
27 
28 cursor csr_employer_data is
29 select 'EUR' currency , iza.payroll_center ,
30        iza.province_code||' '||hr_general.decode_lookup('NL_IZA_PROVINCE',iza.province_code) province,
31        lpad(employer_number,3,'0') employer_number , lpad(sub_employer_number,3,'0') sub_employer_number
32 from pay_nl_iza_upld_status iza
33 where iza.business_group_id = p_bg_id
34 and iza.organization_id = p_employer_id
35 and iza.process_year_month = last_day(to_date(p_process_month,'MMYYYY'));
36 
37 cursor csr_employee_data is
38 select lpad(iza.employer_number,3,'0')||lpad(iza.sub_employer_number,3,'0')||'-'||lpad(iza.employee_number,9,'0') exchange_number,iza.process_status,
39 iza.employee_name name,
40 iza.date_of_birth dob,
41 iza.participant_number participant_number,
42 iza.contribution_1 iza_amt_1,iza.contribution_2 iza_amt_2,
43 iza.correction_contribution_1 corr_amt_1,iza.correction_contribution_2 corr_amt_2,
44 iza.date_correction_1,iza.date_correction_2,
45 decode(iza.process_status,'MISSING',hr_general.decode_lookup('NL_IZA_REJECT_REASON',iza.process_status),hr_general.decode_lookup('NL_IZA_REJECT_REASON',iza.reject_reason)) explanation
46 from pay_nl_iza_upld_status iza
47 where iza.business_group_id = p_bg_id
48 and iza.organization_id = p_employer_id
49 and iza.process_year_month = last_day(to_date(p_process_month,'MMYYYY'));
50 
51 v_employer_data csr_employer_data%rowtype;
52 l_record_count number;
53 l_accepted_count number;
54 l_rejected_count number;
55 l_retro_count number;
56 l_missing_count number;
57 l_tape_amt_1 number;
58 l_accepted_amt_1 number;
59 l_rejected_amt_1 number;
60 l_retro_amt_1 number;
61 l_tape_amt_2 number;
62 l_accepted_amt_2 number;
63 l_rejected_amt_2 number;
64 l_retro_amt_2 number;
65 l_tape_corr_1 number;
66 l_accepted_corr_1 number;
67 l_rejected_corr_1 number;
68 l_retro_corr_1 number;
69 l_tape_corr_2 number;
70 l_accepted_corr_2 number;
71 l_rejected_corr_2 number;
72 l_retro_corr_2 number;
73 l_tape_total number;
74 l_accepted_total number;
75 l_rejected_total number;
76 l_retro_total number;
77 l_earliest_correction_date date;
78 l_earliest_correction_date1 varchar2(50);
79 
80 
81 /*Make calls to suppoting procedures to form the XML file*/
82 
83 begin
84         l_record_count := 0;
85 	l_accepted_count := 0;
86 	l_rejected_count := 0;
87 	l_retro_count := 0;
88 	l_missing_count := 0;
89 	l_tape_amt_1 := 0;
90 	l_accepted_amt_1 := 0;
91 	l_rejected_amt_1 := 0;
92         l_retro_amt_1 := 0;
93         l_tape_amt_2 := 0;
94 	l_accepted_amt_2 := 0;
95 	l_rejected_amt_2 := 0;
96         l_retro_amt_2 := 0;
97         l_tape_corr_1 := 0;
98 	l_accepted_corr_1 := 0;
99 	l_rejected_corr_1 := 0;
100 	l_retro_corr_1 := 0;
101 	l_tape_corr_2 := 0;
102 	l_accepted_corr_2 := 0;
103 	l_rejected_corr_2 := 0;
104 	l_retro_corr_2 := 0;
105         l_tape_total := 0;
106 	l_accepted_total := 0;
107 	l_rejected_total := 0;
108         l_retro_total := 0;
109         l_earliest_correction_date := hr_general.end_of_time;
110 
111 
112 	open csr_employer_data;
113 	fetch csr_employer_data into v_employer_data;
114 	close csr_employer_data;
115 
116  	PAY_NL_XDO_REPORT.vXMLTable.DELETE;
117  	vCtr := 0;
118 
119 /*Get all the XML tags and values*/
120         PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'BG_NAME';
121 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_bg_name;
122 	vCtr := vCtr + 1;
123 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EFF_DATE';
124 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(fnd_date.canonical_to_date(p_eff_date),'DD-Mon-YYYY');
125 	vCtr := vCtr + 1;
126 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ORG_HIERARCHY';
127 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_org_struct;
128 	vCtr := vCtr + 1;
129 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EMPLOYER';
130 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := p_employer;
131 	vCtr := vCtr + 1;
132 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PROCESS_MONTH';
133 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(to_date(p_process_month,'MMYYYY'),'YYYY / MM');
134 	vCtr := vCtr + 1;
135 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'CURRENCY';
136 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employer_data.currency;
137 	vCtr := vCtr + 1;
138 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PAYROLL_CENTER';
139 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employer_data.payroll_center;
140 	vCtr := vCtr + 1;
141 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PROVINCE';
142 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employer_data.province;
143 	vCtr := vCtr + 1;
144 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'IZA_CLIENT_NUMBER';
145 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employer_data.employer_number;
146 	vCtr := vCtr + 1;
147 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'SUB_EMPLOYER_NUMBER';
148 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employer_data.sub_employer_number;
149 
150 	for v_employee_data in csr_employee_data
151 	loop
152 	if (v_employee_data.process_status = 'REJECTED' or v_employee_data.process_status = 'MISSING') then
153 	vCtr := vCtr + 1;
154 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
155 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := null;
156 	vCtr := vCtr + 1;
157 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EXCHANGE_NUMBER';
158 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employee_data.exchange_number;
159 	vCtr := vCtr + 1;
160 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'NAME';
161 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employee_data.name;
162 	vCtr := vCtr + 1;
163 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'DOB';
164 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(v_employee_data.dob,'DD-MM-YY');
165 	vCtr := vCtr + 1;
166 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'PARTICIPANT_NUMBER';
167 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employee_data.participant_number;
168 	vCtr := vCtr + 1;
169 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'IZA_AMT_1';
170 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(v_employee_data.iza_amt_1,'99G990D00MI');
171 	vCtr := vCtr + 1;
172 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'IZA_AMT_2';
173 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(v_employee_data.iza_amt_2,'99G990D00MI');
174 	vCtr := vCtr + 1;
175 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'CORR_AMT_1';
176 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(v_employee_data.corr_amt_1,'99G990D00MI');
177 	vCtr := vCtr + 1;
178 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'CORR_AMT_2';
179 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(v_employee_data.corr_amt_2,'99G990D00MI');
180 	vCtr := vCtr + 1;
181 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EXPLANATION';
182 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := v_employee_data.explanation;
183 	vCtr := vCtr + 1;
184 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'G_CONTAINER_EMPLOYEE';
185 	PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := 'END';
186 	if v_employee_data.process_status = 'REJECTED' then
187 	   l_rejected_count := l_rejected_count + 1;
188 	   l_rejected_amt_1 := l_rejected_amt_1 + v_employee_data.iza_amt_1;
189 	   l_rejected_amt_2 := l_rejected_amt_2 + v_employee_data.iza_amt_2;
190 	   l_rejected_corr_1 := l_rejected_corr_1 + v_employee_data.corr_amt_1;
191 	   l_rejected_corr_2 := l_rejected_corr_2 + v_employee_data.corr_amt_2;
192 	else if v_employee_data.process_status = 'MISSING' then
193 	     l_missing_count := l_missing_count + 1;
194 	end if;
195 	end if;
196 	end if;
197 	if l_earliest_correction_date > v_employee_data.date_correction_1 and v_employee_data.process_status = 'PROCESSED' then
198 	   l_earliest_correction_date := v_employee_data.date_correction_1;
199 	end if;
200 	if l_earliest_correction_date > v_employee_data.date_correction_2 and v_employee_data.process_status = 'PROCESSED' then
201 		   l_earliest_correction_date := v_employee_data.date_correction_2;
202 	end if;
203 	if v_employee_data.process_status = 'PROCESSED' then
204 	     l_accepted_count := l_accepted_count + 1;
205 	     l_accepted_amt_1 := l_accepted_amt_1 + v_employee_data.iza_amt_1;
206 	     l_accepted_amt_2 := l_accepted_amt_2 + v_employee_data.iza_amt_2;
207 	     l_accepted_corr_1 := l_accepted_corr_1 + v_employee_data.corr_amt_1;
208 	     l_accepted_corr_2 := l_accepted_corr_2 + v_employee_data.corr_amt_2;
209 	end if;
210 	if ((v_employee_data.corr_amt_1 > 0 or v_employee_data.corr_amt_2 > 0) and v_employee_data.process_status = 'PROCESSED') then
211 	     l_retro_count := l_retro_count + 1;
212 	     l_retro_amt_1 := 0;
213 	     l_retro_amt_2 := 0;
214 	     l_retro_corr_1 := l_retro_corr_1 + v_employee_data.corr_amt_1;
215 	     l_retro_corr_2 := l_retro_corr_2 + v_employee_data.corr_amt_2;
216 	end if;
217 	end loop;
218 
219 /*Fetch XML file as a BLOB*/
220  vCtr := vCtr + 1;
221  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_COUNT';
222  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_accepted_count;
223  vCtr := vCtr + 1;
224  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_COUNT';
225  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_rejected_count;
226  vCtr := vCtr + 1;
227  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'MISSING_COUNT';
228  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_missing_count;
229  l_Record_count := l_accepted_count + l_rejected_count;
230  l_tape_amt_1 := l_accepted_amt_1 + l_rejected_amt_1;
231  l_tape_amt_2 := l_accepted_amt_2 + l_rejected_amt_2;
232  l_tape_corr_1 := l_accepted_corr_1 + l_rejected_corr_1;
233  l_tape_corr_2 := l_accepted_corr_2 + l_rejected_corr_2;
234  l_tape_total := l_tape_amt_1 + l_tape_amt_2 + l_tape_corr_1 + l_tape_corr_2;
235  l_accepted_total := l_accepted_amt_1 + l_accepted_amt_2 + l_accepted_corr_1 + l_accepted_corr_2;
236  l_rejected_total := l_rejected_amt_1 + l_rejected_amt_2 + l_rejected_corr_1 + l_rejected_corr_2;
237  l_retro_total := l_retro_amt_1 + l_retro_amt_2 + l_retro_corr_1 + l_retro_corr_2;
238  vCtr := vCtr + 1;
239  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RECORD_COUNT';
240  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_record_count;
241  vCtr := vCtr + 1;
242  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_COUNT';
243  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_retro_count;
244  vCtr := vCtr + 1;
245  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAPE_AMT_1';
246  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_tape_amt_1,'9G999G990D00MI');
247  vCtr := vCtr + 1;
248  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_AMT_1';
249  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_accepted_amt_1,'9G999G990D00MI');
250  vCtr := vCtr + 1;
251  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_AMT_1';
252  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_rejected_amt_1,'9G999G990D00MI');
253  vCtr := vCtr + 1;
254  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_AMT_1';
255  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_retro_amt_1,'9G999G990D00MI');
256  vCtr := vCtr + 1;
257  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAPE_AMT_2';
258  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_tape_amt_2,'9G999G990D00MI');
259  vCtr := vCtr + 1;
260  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_AMT_2';
261  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_accepted_amt_2,'9G999G990D00MI');
262  vCtr := vCtr + 1;
263  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_AMT_2';
264  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_rejected_amt_2,'9G999G990D00MI');
265  vCtr := vCtr + 1;
266  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_AMT_2';
267  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_retro_amt_2,'9G999G990D00MI');
268  vCtr := vCtr + 1;
269  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAPE_CORR_1';
270  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_tape_corr_1,'9G999G990D00MI');
271  vCtr := vCtr + 1;
272  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_CORR_1';
273  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_accepted_corr_1,'9G999G990D00MI');
274  vCtr := vCtr + 1;
275  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_CORR_1';
276  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_rejected_corr_1,'9G999G990D00MI');
277  vCtr := vCtr + 1;
278  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_CORR_1';
279  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_retro_corr_1,'9G999G990D00MI');
280  vCtr := vCtr + 1;
281  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAPE_CORR_2';
282  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_tape_corr_2,'9G999G990D00MI');
283  vCtr := vCtr + 1;
284  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_CORR_2';
285  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_accepted_corr_2,'9G999G990D00MI');
286  vCtr := vCtr + 1;
287  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_CORR_2';
288  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_rejected_corr_2,'9G999G990D00MI');
289  vCtr := vCtr + 1;
290  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_CORR_2';
291  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_retro_corr_2,'9G999G990D00MI');
292  vCtr := vCtr + 1;
293  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'TAPE_TOTAL';
294  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_tape_total,'99G999G999G990D00MI');
295  vCtr := vCtr + 1;
296  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'ACCEPTED_TOTAL';
297  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_accepted_total,'99G999G999G990D00MI');
298  vCtr := vCtr + 1;
299  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'REJECTED_TOTAL';
300  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_rejected_total,'99G999G999G990D00MI');
301  vCtr := vCtr + 1;
302  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'RETRO_TOTAL';
303  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := to_char(l_retro_total,'99G999G999G990D00MI');
304  vCtr := vCtr + 1;
305  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagName := 'EARLIEST_CORRECTION_DATE';
306  l_earliest_correction_date1 := to_char(l_earliest_correction_date,'DD-MM-YY');
307  if l_earliest_correction_date = hr_general.end_of_time then
308     l_earliest_correction_date1 := NULL;
309  end if;
310  PAY_NL_XDO_REPORT.vXMLTable(vCtr).TagValue := l_earliest_correction_date1;
311 
312  pay_nl_xdo_Report.WritetoCLOB_rtf(p_xfdf_blob );
313 
314 end populate_iza_report_data;
315 
316 
317 PROCEDURE record_4712(p_file_id NUMBER) IS
318 
319 	l_upload_name       VARCHAR2(1000);
320 	l_file_name         VARCHAR2(1000);
321 	l_start_date        DATE := TO_DATE('01/01/0001', 'dd/mm/yyyy');
322 	l_end_date          DATE := TO_DATE('31/12/4712', 'dd/mm/yyyy');
323 
324 BEGIN
325 	-- program_name will be used to store the file_name
326 	-- this is bcos the file_name in fnd_lobs contains
327 	-- the full patch of the doc and not just the file name
328 	SELECT program_name
329 	INTO l_file_name
330 	FROM fnd_lobs
331 	WHERE file_id = p_file_id;
332 
333 	-- the delete will ensure that the patch is rerunnable
334 	DELETE FROM per_gb_xdo_templates
335 	WHERE file_name = l_file_name AND
336 	effective_start_date = l_start_date AND
337 	effective_end_date = l_end_date;
338 
339 	INSERT INTO per_gb_xdo_templates
340 	(file_id,
341 	file_name,
342 	file_description,
343 	effective_start_date,
344 	effective_end_date)
345 	SELECT p_file_id, l_file_name, 'Template for year 0001-4712',
346 	l_start_date, l_end_date
347 	FROM fnd_lobs
348 	WHERE file_id = p_file_id;
349 END;
350 
351 
352 END PAY_NL_IZA_REPORT;