[Home] [Help]
PACKAGE BODY: APPS.PAY_ZA_ELE_TAX_FILE
Source
1 PACKAGE BODY PAY_ZA_ELE_TAX_FILE AS
2 /* $Header: PYZAIRPM.pkb 120.3.12020000.7 2013/04/24 07:40:37 abdash ship $ */
3
4 G_BUFFER_LINE VARCHAR2(4000);
5 G_DIR1 VARCHAR2(100);
6 G_DIR2 VARCHAR2(100);
7 G_DIR3 VARCHAR2(100);
8 G_ACTION_CONTEXT_ID varchar2(2000);
9 G_CERTIFICATE_NUMBER varchar2(2000);
10
11
12 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
13 l_payroll_actions varchar2(4000);
14 BEGIN
15 IF (P_PAYROLL_ACTION_ID is not null) THEN
16 C_PAYROLL_ACTION_ID := 'and paa.payroll_action_id = '||P_PAYROLL_ACTION_ID;
17 C_ACTION_CONTEXT_ID := 'and pai.action_context_id = '||P_PAYROLL_ACTION_ID;
18 ELSE
19 l_payroll_actions :=
20 '(select ppa.payroll_action_id '||
21 'from pay_payroll_actions ppa '||
22 'where ppa.business_group_id = '||P_BUSINESS_GROUP_ID||
23 ' and ppa.action_type = ''X'' '||
24 ' and ppa.report_type = ''ZA_TYE'' '||
25 ' and ppa.action_status = ''C'' '||
26 ' and pay_za_tye_archive_pkg.get_parameter(''TAX_YEAR'',ppa.legislative_parameters)= '||P_TAX_YEAR||
27 ' and NVL(pay_za_tye_archive_pkg.get_parameter(''PERIOD_RECON'',ppa.legislative_parameters),''02'')= NVL('||P_PERIOD_RECON||',''02'')'||
28 ' and pay_za_tye_archive_pkg.get_parameter(''CERT_TYPE'',ppa.legislative_parameters)= '''||P_CERTIFICATE_TYPE||''''||
29 ' and pay_za_tye_archive_pkg.get_parameter(''LEGAL_ENTITY'',ppa.legislative_parameters)= '||P_LEGAL_ENTITY_ID||' ) ';
30 C_PAYROLL_ACTION_ID := 'and paa.payroll_action_id in '||l_payroll_actions;
31 C_ACTION_CONTEXT_ID := 'and pai.action_context_id in '||l_payroll_actions;
32 END IF;
33 RETURN true;
34 END BEFOREREPORT;
35
36 PROCEDURE CAL_CTRL_TOTAL(CODE number,VALUE varchar2) IS
37 L_VAL varchar2(200);
38 L_4116_VAL varchar2(200);
39 cursor c_4116_val is
40 select pai.action_information13
41 from pay_action_information pai
42 where pai.action_context_id = G_ACTION_CONTEXT_ID
43 and pai.action_context_type = 'AAP'
44 and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
45 and pai.action_information28 is null
46 and pai.action_information1 = G_CERTIFICATE_NUMBER;
47
48 BEGIN
49 open c_4116_val;
50 fetch c_4116_val into L_4116_VAL;
51 close c_4116_val;
52
53 IF ((CODE = '4005') AND (trim(VALUE) is null) AND
54 (trim(L_4116_VAL) is not null) AND
55 ((G_DIR1 is not null AND G_DIR1<>'To Be Advised') OR
56 (G_DIR2 is not null AND G_DIR2<>'To Be Advised') OR
57 (G_DIR3 is not null AND G_DIR3<>'To Be Advised') )) THEN
58 IF (P_TAX_YEAR = '2013' and P_PERIOD_RECON = '08') THEN
59 G_FLAG := '1';
60 L_VAL := '1';
61 ELSE
62 G_FLAG := '0';
63 L_VAL := '0';
64 END IF;
65 ELSE
66 L_VAL := VALUE;
67 END IF;
68
69 IF (trim(L_VAL) is not null) and CODE <> 3696 THEN
70 -- Control Total Code
71 CTRL_TOTAL_CODE := CTRL_TOTAL_CODE + CODE;
72 -- Control Total Value
73 IF (CODE >= 3601 and CODE <= 4497) THEN
74 CTRL_TOTAL_VALUE := CTRL_TOTAL_VALUE + L_VAL;
75 G_BUFFER_LINE := G_BUFFER_LINE||CODE||','||L_VAL||',';
76 END IF;
77 ELSIF (CODE = 9999) THEN
78 -- Control Total Code
79 CTRL_TOTAL_CODE := CTRL_TOTAL_CODE + CODE;
80
81 ELSIF (CODE = 3696) THEN
82 if L_VAL <> 0 then
83 CTRL_TOTAL_CODE := CTRL_TOTAL_CODE + CODE;
84 -- Control Total Value
85 CTRL_TOTAL_VALUE := CTRL_TOTAL_VALUE + L_VAL;
86 G_BUFFER_LINE := G_BUFFER_LINE||CODE||','||L_VAL||',';
87 end if;
88 END IF;
89 END CAL_CTRL_TOTAL;
90
91 FUNCTION EMPLOYER_CTRL_TOTAL(TRADE_NAME varchar2,
92 TEST_LIVE varchar2,
93 PAYE_NUMBER varchar2,
94 SDL_NUMBER varchar2,
95 UIF_NUMBER varchar2,
96 EMPLOYER_CONTACT_NAME varchar2,
97 EMPLOYER_CONTACT_PHONE varchar2,
98 EMPLOYER_EMAIL varchar2,
99 PAYROLL_SOFTWARE varchar2,
100 TRANSACTION_YEAR varchar2,
101 PERIOD_OF_RECONCILIATION varchar2,
102 TRADE_CLASSIFICATION varchar2,
103 PHYSICAL_ADD_UNIT_NUM varchar2,
104 PHYSICAL_ADD_COMPLEX varchar2,
105 PHYSICAL_ADD_STREET varchar2,
106 PHYSICAL_ADD_FORM varchar2,
107 PHYSICAL_ADD_SUBURB varchar2,
108 PHYSICAL_ADD_CITY varchar2,
109 POSTAL_CODE varchar2)RETURN varchar2 IS
110 BEGIN
111 CTRL_TOTAL_REC := CTRL_TOTAL_REC + 1;
112 CAL_CTRL_TOTAL(2010,TRADE_NAME);
113 CAL_CTRL_TOTAL(2015,TEST_LIVE);
114 CAL_CTRL_TOTAL(2020,PAYE_NUMBER);
115 CAL_CTRL_TOTAL(2022,SDL_NUMBER);
116 CAL_CTRL_TOTAL(2024,UIF_NUMBER);
117 CAL_CTRL_TOTAL(2025,EMPLOYER_CONTACT_NAME);
118 CAL_CTRL_TOTAL(2026,EMPLOYER_CONTACT_PHONE);
119 CAL_CTRL_TOTAL(2027,EMPLOYER_EMAIL);
120 CAL_CTRL_TOTAL(2028,PAYROLL_SOFTWARE);
121 CAL_CTRL_TOTAL(2030,TRANSACTION_YEAR);
122 CAL_CTRL_TOTAL(2031,PERIOD_OF_RECONCILIATION);
123 CAL_CTRL_TOTAL(2035,TRADE_CLASSIFICATION);
124 CAL_CTRL_TOTAL(2061,PHYSICAL_ADD_UNIT_NUM);
125 CAL_CTRL_TOTAL(2062,PHYSICAL_ADD_COMPLEX);
126 CAL_CTRL_TOTAL(2063,PHYSICAL_ADD_STREET);
127 CAL_CTRL_TOTAL(2064,PHYSICAL_ADD_FORM);
128 CAL_CTRL_TOTAL(2065,PHYSICAL_ADD_SUBURB);
129 CAL_CTRL_TOTAL(2066,PHYSICAL_ADD_CITY);
130 CAL_CTRL_TOTAL(2080,POSTAL_CODE);
131 CAL_CTRL_TOTAL(9999,null);
132
133 return null;
134
135 END EMPLOYER_CTRL_TOTAL;
136
137
138 FUNCTION EMPLOYEE_CTRL_TOTAL(CERTIFICATE_NUMBER varchar2,
139 CERTIFICATE_TYPE varchar2,
140 NATURE_OF_PERSON varchar2,
141 YEAR_OF_ASSESSMENT varchar2,
142 SUR_OR_TRADING_NAME varchar2,
143 FIRST_TWO_NAMES varchar2,
144 EMP_INITIALS varchar2,
145 IDENTITY_NUMBER varchar2,
146 PASSPORT_NUMBER varchar2,
147 COUNTRY_OF_ISSUE varchar2,
148 DATE_OF_BIRTH varchar2,
149 IT_REF_NUMBER varchar2,
150 EMP_NUMBER varchar2,
151 DATE_EMPLOYED_FROM varchar2,
152 DATE_EMPLOYED_TO varchar2,
153 PAY_PERIODS_IN_YEAR varchar2,
154 PAY_PERIODS_IN_WORKED varchar2,
155 DIRECTIVE_NUMBER1 varchar2,
156 DIRECTIVE_NUMBER2 varchar2,
157 DIRECTIVE_NUMBER3 varchar2,
158 BANK_ACC_TYPE varchar2,
159 BANK_ACC_NUMBER varchar2,
160 BANK_BR_NUMBER varchar2,
161 BANK_NAME varchar2,
162 BANK_BR_NAME varchar2,
163 BANK_ACC_NAME varchar2,
164 BANK_ACC_RELATIONSHIP varchar2) RETURN varchar2 IS
165 BEGIN
166 CTRL_TOTAL_REC := CTRL_TOTAL_REC + 1;
167 CAL_CTRL_TOTAL(3010,CERTIFICATE_NUMBER);
168 CAL_CTRL_TOTAL(3015,CERTIFICATE_TYPE);
169 CAL_CTRL_TOTAL(3020,NATURE_OF_PERSON);
170 CAL_CTRL_TOTAL(3025,YEAR_OF_ASSESSMENT);
171 CAL_CTRL_TOTAL(3030,SUR_OR_TRADING_NAME);
172 CAL_CTRL_TOTAL(3040,FIRST_TWO_NAMES);
173 CAL_CTRL_TOTAL(3050,EMP_INITIALS);
174 CAL_CTRL_TOTAL(3060,IDENTITY_NUMBER);
175 CAL_CTRL_TOTAL(3070,PASSPORT_NUMBER);
176 CAL_CTRL_TOTAL(3075,COUNTRY_OF_ISSUE);
177 CAL_CTRL_TOTAL(3080,DATE_OF_BIRTH);
178 CAL_CTRL_TOTAL(3100,IT_REF_NUMBER);
179 CAL_CTRL_TOTAL(3160,EMP_NUMBER);
180 CAL_CTRL_TOTAL(3170,DATE_EMPLOYED_FROM);
181 CAL_CTRL_TOTAL(3180,DATE_EMPLOYED_TO);
182 CAL_CTRL_TOTAL(3200,PAY_PERIODS_IN_YEAR);
183 CAL_CTRL_TOTAL(3210,PAY_PERIODS_IN_WORKED);
184 CAL_CTRL_TOTAL(3230,DIRECTIVE_NUMBER1);
185 CAL_CTRL_TOTAL(3230,DIRECTIVE_NUMBER2);
186 CAL_CTRL_TOTAL(3230,DIRECTIVE_NUMBER3);
187 CAL_CTRL_TOTAL(3240,BANK_ACC_TYPE);
188 CAL_CTRL_TOTAL(3241,BANK_ACC_NUMBER);
189 CAL_CTRL_TOTAL(3242,BANK_BR_NUMBER);
190 CAL_CTRL_TOTAL(3243,BANK_NAME);
191 CAL_CTRL_TOTAL(3244,BANK_BR_NAME);
192 CAL_CTRL_TOTAL(3245,BANK_ACC_NAME);
193 CAL_CTRL_TOTAL(3246,BANK_ACC_RELATIONSHIP);
194 CAL_CTRL_TOTAL(9999,null);
195
196 return null;
197 END EMPLOYEE_CTRL_TOTAL;
198
199 FUNCTION EMPLOYEE_CONT_CTRL_TOTAL(EMP_EMAIL varchar2,
200 HOME_PHONE varchar2,
201 BUS_PHONE varchar2,
202 FAX_NUMBER varchar2,
203 CELL_NUMBER varchar2,
204 BUS_UNIT_NUMBER varchar2,
205 BUS_COMPLEX varchar2,
206 BUS_STREET_NUM varchar2,
207 BUS_FORM varchar2,
208 BUS_SUBURB varchar2,
209 BUS_CITY varchar2,
210 BUS_POSTAL_CODE varchar2,
211 RES_UNIT_NUMBER varchar2,
212 RES_COMPLEX varchar2,
213 RES_STREET_NUM varchar2,
214 RES_FORM varchar2,
215 RES_SUBURB varchar2,
216 RES_CITY varchar2,
217 RES_POSTAL_CODE varchar2,
218 POS_RES_ADD_SAME varchar2,
219 POS_LINE1 varchar2,
220 POS_LINE2 varchar2,
221 POS_LINE3 varchar2,
222 POS_CODE varchar2) RETURN varchar2 IS
223
224 BEGIN
225 CAL_CTRL_TOTAL(3125,EMP_EMAIL);
226 CAL_CTRL_TOTAL(3135,HOME_PHONE);
227 CAL_CTRL_TOTAL(3136,BUS_PHONE);
228 CAL_CTRL_TOTAL(3137,FAX_NUMBER);
229 CAL_CTRL_TOTAL(3138,CELL_NUMBER);
230 CAL_CTRL_TOTAL(3144,BUS_UNIT_NUMBER);
231 CAL_CTRL_TOTAL(3145,BUS_COMPLEX);
232 CAL_CTRL_TOTAL(3146,BUS_STREET_NUM);
233 CAL_CTRL_TOTAL(3147,BUS_FORM);
234 CAL_CTRL_TOTAL(3148,BUS_SUBURB);
235 CAL_CTRL_TOTAL(3149,BUS_CITY);
236 CAL_CTRL_TOTAL(3150,BUS_POSTAL_CODE);
237 CAL_CTRL_TOTAL(3211,RES_UNIT_NUMBER);
238 CAL_CTRL_TOTAL(3212,RES_COMPLEX);
239 CAL_CTRL_TOTAL(3213,RES_STREET_NUM);
240 CAL_CTRL_TOTAL(3214,RES_FORM);
241 CAL_CTRL_TOTAL(3215,RES_SUBURB);
242 CAL_CTRL_TOTAL(3216,RES_CITY);
243 CAL_CTRL_TOTAL(3217,RES_POSTAL_CODE);
244 CAL_CTRL_TOTAL(3218,POS_RES_ADD_SAME);
245 CAL_CTRL_TOTAL(3221,POS_LINE1);
246 CAL_CTRL_TOTAL(3222,POS_LINE2);
247 CAL_CTRL_TOTAL(3223,POS_LINE3);
248 CAL_CTRL_TOTAL(3229,POS_CODE);
249
250 return null;
251 END EMPLOYEE_CONT_CTRL_TOTAL;
252
253 FUNCTION EMPLOYEE_INC_CTRL_TOTAL(INC_CODE1 varchar2,
254 INC_VAL1 varchar2,
255 INC_CODE2 varchar2,
256 INC_VAL2 varchar2,
257 INC_CODE3 varchar2,
258 INC_VAL3 varchar2,
259 INC_CODE4 varchar2,
260 INC_VAL4 varchar2,
261 INC_CODE5 varchar2,
262 INC_VAL5 varchar2,
263 INC_CODE6 varchar2,
264 INC_VAL6 varchar2,
265 INC_CODE7 varchar2,
266 INC_VAL7 varchar2,
267 INC_CODE8 varchar2,
268 INC_VAL8 varchar2,
269 INC_CODE9 varchar2,
270 INC_VAL9 varchar2,
271 INC_CODE10 varchar2,
272 INC_VAL10 varchar2,
273 INC_CODE11 varchar2,
274 INC_VAL11 varchar2,
275 INC_CODE12 varchar2,
276 INC_VAL12 varchar2,
277 INC_CODE13 varchar2,
278 INC_VAL13 varchar2) RETURN varchar2 IS
279 BEGIN
280 G_BUFFER_LINE:='';
281 CAL_CTRL_TOTAL(INC_CODE1,INC_VAL1);
282 CAL_CTRL_TOTAL(INC_CODE2,INC_VAL2);
283 CAL_CTRL_TOTAL(INC_CODE3,INC_VAL3);
284 CAL_CTRL_TOTAL(INC_CODE4,INC_VAL4);
285 CAL_CTRL_TOTAL(INC_CODE5,INC_VAL5);
286 CAL_CTRL_TOTAL(INC_CODE6,INC_VAL6);
287 CAL_CTRL_TOTAL(INC_CODE7,INC_VAL7);
288 CAL_CTRL_TOTAL(INC_CODE8,INC_VAL8);
289 CAL_CTRL_TOTAL(INC_CODE9,INC_VAL9);
290 CAL_CTRL_TOTAL(INC_CODE10,INC_VAL10);
291 CAL_CTRL_TOTAL(INC_CODE11,INC_VAL11);
292 CAL_CTRL_TOTAL(INC_CODE12,INC_VAL12);
293 CAL_CTRL_TOTAL(INC_CODE13,INC_VAL13);
294
295 return G_BUFFER_LINE;
296 END EMPLOYEE_INC_CTRL_TOTAL;
297
298 FUNCTION EMPLOYEE_GRO_CTRL_TOTAL(NON_TAX_INCOME varchar2,
299 RFI_INCOME varchar2,
300 NRFI_INCOME varchar2) RETURN varchar2 IS
301 BEGIN
302 CAL_CTRL_TOTAL(3696,NON_TAX_INCOME);
303 CAL_CTRL_TOTAL(3697,RFI_INCOME);
304 CAL_CTRL_TOTAL(3698,NRFI_INCOME);
305
306 return null;
307 END EMPLOYEE_GRO_CTRL_TOTAL;
308
309 FUNCTION EMPLOYEE_DED_CTRL_TOTAL(DED_CODE1 varchar2,
310 DED_VAL1 varchar2,
311 DED_CODE2 varchar2,
312 DED_VAL2 varchar2,
313 DED_CODE3 varchar2,
314 DED_VAL3 varchar2,
315 DED_CODE4 varchar2,
316 DED_VAL4 varchar2,
317 DED_CODE5 varchar2,
318 DED_VAL5 varchar2,
319 DED_CODE6 varchar2,
320 DED_VAL6 varchar2,
321 DED_CODE7 varchar2,
322 DED_VAL7 varchar2,
323 DED_CODE8 varchar2,
324 DED_VAL8 varchar2,
325 DED_CODE9 varchar2,
326 DED_VAL9 varchar2,
327 DED_CODE10 varchar2,
328 DED_VAL10 varchar2,
329 DED_CODE11 varchar2,
330 DED_VAL11 varchar2,
331 DED_CODE12 varchar2,
332 DED_VAL12 varchar2,
333 DED_CODE13 varchar2,
334 DED_VAL13 varchar2,
335 DIR1 varchar2,
336 DIR2 varchar2,
337 DIR3 varchar2,
338 ACTION_CONTEXT_ID varchar2,
339 CERTIFICATE_NUMBER varchar2 ) RETURN varchar2 IS
340
341
342
343 BEGIN
344 G_FLAG := 0;
345 G_BUFFER_LINE:='';
346 G_DIR1 := DIR1;
347 G_DIR2 := DIR2;
348 G_DIR3 := DIR3;
349 G_ACTION_CONTEXT_ID := ACTION_CONTEXT_ID;
350 G_CERTIFICATE_NUMBER := CERTIFICATE_NUMBER;
351 CAL_CTRL_TOTAL(DED_CODE1,DED_VAL1);
352 CAL_CTRL_TOTAL(DED_CODE2,DED_VAL2);
353 CAL_CTRL_TOTAL(DED_CODE3,DED_VAL3);
354 CAL_CTRL_TOTAL(DED_CODE4,DED_VAL4);
355 CAL_CTRL_TOTAL(DED_CODE5,DED_VAL5);
356 CAL_CTRL_TOTAL(DED_CODE6,DED_VAL6);
357 CAL_CTRL_TOTAL(DED_CODE7,DED_VAL7);
358 CAL_CTRL_TOTAL(DED_CODE8,DED_VAL8);
359 CAL_CTRL_TOTAL(DED_CODE9,DED_VAL9);
360 CAL_CTRL_TOTAL(DED_CODE10,DED_VAL10);
361 CAL_CTRL_TOTAL(DED_CODE11,DED_VAL11);
362 CAL_CTRL_TOTAL(DED_CODE12,DED_VAL12);
363 CAL_CTRL_TOTAL(DED_CODE13,DED_VAL13);
364
365 return G_BUFFER_LINE;
366 END EMPLOYEE_DED_CTRL_TOTAL;
367
368 FUNCTION EMPLOYEE_TAX_CTRL_TOTAL(TOTAL_DEDUCTION varchar2,
369 SITE varchar2,
370 PAYE varchar2,
371 PAYE_RET_LUM_BEN varchar2,
372 UIF_CONTRIBUTION varchar2,
373 SDL_CONTRIBUTION varchar2,
374 TOT_TAX_UIF_SDL varchar2,
375 REASON_CODE_IT3 varchar2,
376 MED_TAX_CR varchar2) RETURN varchar2 IS
377 BEGIN
378 CAL_CTRL_TOTAL(4497,TOTAL_DEDUCTION);
379 CAL_CTRL_TOTAL(4101,SITE);
380 CAL_CTRL_TOTAL(4102,PAYE);
381 CAL_CTRL_TOTAL(4115,PAYE_RET_LUM_BEN);
382 CAL_CTRL_TOTAL(4141,UIF_CONTRIBUTION);
383 CAL_CTRL_TOTAL(4142,SDL_CONTRIBUTION);
384 CAL_CTRL_TOTAL(4149,TOT_TAX_UIF_SDL);
385 CAL_CTRL_TOTAL(4150,REASON_CODE_IT3);
386 CAL_CTRL_TOTAL(4116,MED_TAX_CR);
387
388 return null;
389 END EMPLOYEE_TAX_CTRL_TOTAL;
390
391 FUNCTION GET_G_FLAG return number IS
392 BEGIN
393 return G_FLAG;
394 END GET_G_FLAG;
395
396
397
398 END PAY_ZA_ELE_TAX_FILE;