[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYGBP45_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYGBP45_XMLP_PKG AS
2 /* $Header: PAYGBP45B.pls 120.1 2008/01/07 15:57:25 srikrish noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 DECLARE
6 L_TEST NUMBER;
7 L_ERS_ADDRESS VARCHAR2(60);
8 BEGIN
9 --HR_STANDARD.EVENT('BEFORE REPORT');
10 INSERT INTO FND_SESSIONS
11 (SESSION_ID
12 ,EFFECTIVE_DATE)
13 SELECT
14 USERENV('sessionid'),
15 TRUNC(SYSDATE)
16 FROM
17 DUAL
18 WHERE not exists (
19 SELECT
20 1
21 FROM
22 FND_SESSIONS FS
23 WHERE FS.SESSION_ID = USERENV('sessionid') );
24 P_SESSION_DATE := SYSDATE;
25 P_DATE_TODAY := SYSDATE;
26 IF P_ASSIGNMENT_ACTION_ID IS NULL THEN
27 P_ACTION_RESTRICTION := 'AND act.payroll_action_id = ' || P_PAYROLL_ACTION_ID;
28 ELSE
29 P_ACTION_RESTRICTION := 'AND act.assignment_action_id = ' || P_ASSIGNMENT_ACTION_ID;
30 END IF;
31 SELECT
32 SUBSTR(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(P.LEGISLATIVE_PARAMETERS
33 ,'TAX_REF')
34 ,1
35 ,3),
36 SUBSTR(LTRIM(SUBSTR(PAY_GB_EOY_ARCHIVE.GET_PARAMETER(P.LEGISLATIVE_PARAMETERS
37 ,'TAX_REF')
38 ,4
39 ,11)
40 ,'/')
41 ,1
42 ,10),
43 SUBSTR(PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(P.PAYROLL_ACTION_ID
44 ,'X_EMPLOYERS_ADDRESS_LINE'
45 ,'0')
46 ,1
47 ,60),
48 SUBSTR(PAY_GB_EOY_ARCHIVE.GET_ARCH_STR(P.PAYROLL_ACTION_ID
49 ,'X_EMPLOYERS_NAME'
50 ,'0')
51 ,1
52 ,40)
53 INTO C_TAX_DIST_NO,C_TAX_DIST_REF,L_ERS_ADDRESS,C_ERS_NAME
54 FROM
55 PAY_PAYROLL_ACTIONS P
56 WHERE P.PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
57 SPLIT_EMPLOYER_ADDRESS(L_ERS_ADDRESS
58 ,C_ERS_ADDR_LINE1
59 ,C_ERS_ADDR_LINE2
60 ,C_ERS_ADDR_LINE3);
61 END;
62 RETURN (TRUE);
63 END BEFOREREPORT;
64
65 FUNCTION C_FORMAT_DATA_FORMULA(TITLE IN VARCHAR2
66 ,ADDRESS_LINE1 IN VARCHAR2
67 ,ADDRESS_LINE2 IN VARCHAR2
68 ,ADDRESS_LINE3 IN VARCHAR2
69 ,TOWN_OR_CITY IN VARCHAR2
70 ,COUNTY IN VARCHAR2
71 ,COUNTRY IN VARCHAR2
72 ,POST_CODE IN VARCHAR2
73 ,TAXABLE_PAY IN NUMBER
74 ,PREVIOUS_TAXABLE_PAY IN NUMBER
75 ,TAX_PAID IN NUMBER
76 ,PREVIOUS_TAX_PAID IN NUMBER
77 ,NI_NUMBER IN VARCHAR2
78 ,TERMINATION_DATE IN DATE
79 ,C_3_PART IN VARCHAR2
80 ,W1_M1_INDICATOR IN VARCHAR2) RETURN VARCHAR2 IS
81 BEGIN
82 C_TITLE := SUBSTR(HR_GENERAL.DECODE_LOOKUP('TITLE'
83 ,TITLE)
84 ,1
85 ,5);
86 C_PER_ADDRESS_LINE1 := ADDRESS_LINE1;
87 C_PER_ADDRESS_LINE2 := SUBSTR(RTRIM(ADDRESS_LINE2) || RTRIM(', ' || ADDRESS_LINE3
88 ,', ')
89 ,1
90 ,60);
91 C_PER_ADDRESS_LINE3 := RTRIM(TOWN_OR_CITY) || ' ' || RTRIM(COUNTY);
92 C_PER_ADDRESS_LINE4 := COUNTRY || ' ' || POST_CODE;
93 C_TOTAL_PAY_TD := NVL(TAXABLE_PAY
94 ,0) + NVL(PREVIOUS_TAXABLE_PAY
95 ,0);
96 C_TOTAL_TAX_TD := NVL(TAX_PAID
97 ,0) + NVL(PREVIOUS_TAX_PAID
98 ,0);
99 C_NI12 := SUBSTR(NI_NUMBER
100 ,1
101 ,2);
102 C_NI34 := SUBSTR(NI_NUMBER
103 ,3
104 ,2);
105 C_NI56 := SUBSTR(NI_NUMBER
106 ,5
107 ,2);
108 C_NI78 := SUBSTR(NI_NUMBER
109 ,7
110 ,2);
111 C_NI9 := SUBSTR(NI_NUMBER
112 ,9
113 ,1);
114 C_DATE_OF_LEAVING_DD := TO_CHAR(TERMINATION_DATE
115 ,'DD');
116 C_DATE_OF_LEAVING_MM := TO_CHAR(TERMINATION_DATE
117 ,'MM');
118 C_DATE_OF_LEAVING_YYYY := TO_CHAR(TERMINATION_DATE
119 ,'YYYY');
120 IF SUBSTR(C_DATE_OF_LEAVING_DD
121 ,1
122 ,1) = '0' THEN
123 C_DATE_OF_LEAVING_DD := ' ' || SUBSTR(C_DATE_OF_LEAVING_DD
124 ,2
125 ,2);
126 END IF;
127 IF SUBSTR(C_DATE_OF_LEAVING_MM
128 ,1
129 ,1) = '0' THEN
130 C_DATE_OF_LEAVING_MM := ' ' || SUBSTR(C_DATE_OF_LEAVING_MM
131 ,2
132 ,2);
133 END IF;
134 IF C_3_PART = 'TRUE' THEN
135 IF W1_M1_INDICATOR IS NULL THEN
136 GET_POUNDS_PENCE(C_TOTAL_PAY_TD
137 ,C_PAY_TD_POUNDS
138 ,C_PAY_TD_PENCE);
139 GET_POUNDS_PENCE(C_TOTAL_TAX_TD
140 ,C_TAX_TD_POUNDS
141 ,C_TAX_TD_PENCE);
142 C_PAY_IN_EMP_POUNDS := '';
143 C_PAY_IN_EMP_PENCE := '';
144 C_TAX_IN_EMP_POUNDS := '';
145 C_TAX_IN_EMP_PENCE := '';
146 ELSE
147 GET_POUNDS_PENCE(TAXABLE_PAY
148 ,C_PAY_IN_EMP_POUNDS
149 ,C_PAY_IN_EMP_PENCE);
150 GET_POUNDS_PENCE(TAX_PAID
151 ,C_TAX_IN_EMP_POUNDS
152 ,C_TAX_IN_EMP_PENCE);
153 C_PAY_TD_POUNDS := '';
154 C_PAY_TD_PENCE := '';
155 C_TAX_TD_POUNDS := '';
156 C_TAX_TD_PENCE := '';
157 END IF;
158 ELSIF C_3_PART = 'FALSE' THEN
159 IF W1_M1_INDICATOR IS NULL THEN
160 GET_POUNDS_PENCE(C_TOTAL_PAY_TD
161 ,C_PAY_TD_POUNDS
162 ,C_PAY_TD_PENCE);
163 GET_POUNDS_PENCE(C_TOTAL_TAX_TD
164 ,C_TAX_TD_POUNDS
165 ,C_TAX_TD_PENCE);
166 IF NVL(PREVIOUS_TAXABLE_PAY
167 ,0) = 0 AND NVL(PREVIOUS_TAX_PAID
168 ,0) = 0 THEN
169 C_PAY_IN_EMP_POUNDS := '';
170 C_PAY_IN_EMP_PENCE := '';
171 C_TAX_IN_EMP_POUNDS := '';
172 C_TAX_IN_EMP_PENCE := '';
173 ELSE
174 GET_POUNDS_PENCE(TAXABLE_PAY
175 ,C_PAY_IN_EMP_POUNDS
176 ,C_PAY_IN_EMP_PENCE);
177 GET_POUNDS_PENCE(TAX_PAID
178 ,C_TAX_IN_EMP_POUNDS
179 ,C_TAX_IN_EMP_PENCE);
180 END IF;
181 ELSE
182 GET_POUNDS_PENCE(TAXABLE_PAY
183 ,C_PAY_IN_EMP_POUNDS
184 ,C_PAY_IN_EMP_PENCE);
185 GET_POUNDS_PENCE(TAX_PAID
186 ,C_TAX_IN_EMP_POUNDS
187 ,C_TAX_IN_EMP_PENCE);
188 C_PAY_TD_POUNDS := '';
189 C_PAY_TD_PENCE := '';
190 C_TAX_TD_POUNDS := '';
191 C_TAX_TD_PENCE := '';
192 END IF;
193 END IF;
194
195 RETURN NULL;
196 END C_FORMAT_DATA_FORMULA;
197
198 PROCEDURE GET_POUNDS_PENCE(P_TOTAL IN NUMBER
199 ,P_POUNDS IN OUT NOCOPY NUMBER
200 ,P_PENCE IN OUT NOCOPY NUMBER) IS
201 BEGIN
202 IF P_TOTAL <> 0 THEN
203 P_POUNDS := TRUNC(P_TOTAL);
204 P_PENCE := ABS(100 * (P_TOTAL - P_POUNDS));
205 ELSE
206 P_POUNDS := NULL;
207 P_PENCE := NULL;
208 END IF;
209 END GET_POUNDS_PENCE;
210
211 PROCEDURE SPLIT_EMPLOYER_ADDRESS(P_EMPLOYER_ADDRESS IN VARCHAR2
212 ,P_EMP_ADDR_LINE_1 IN OUT NOCOPY VARCHAR2
213 ,P_EMP_ADDR_LINE_2 IN OUT NOCOPY VARCHAR2
214 ,P_EMP_ADDR_LINE_3 IN OUT NOCOPY VARCHAR2) IS
215 LINE_LENGTH CONSTANT NUMBER DEFAULT 38;
216 OUT_LINE1 VARCHAR2(38) := NULL;
217 OUT_LINE2 VARCHAR2(38) := NULL;
218 CURRENT_CHAR VARCHAR2(1);
219 IND NUMBER;
220 REMAINING_CHARS NUMBER;
221 WRAP_POINT NUMBER := 38;
222 P_REMAINING_ADDRESS VARCHAR2(60);
223 BEGIN
224 IF NVL(LENGTH(RTRIM(P_EMPLOYER_ADDRESS))
225 ,0) > 38 THEN
226 FOR ind IN REVERSE 1 .. LINE_LENGTH LOOP
227 CURRENT_CHAR := SUBSTR(P_EMPLOYER_ADDRESS
228 ,IND
229 ,1);
230 IF IND = LINE_LENGTH AND CURRENT_CHAR = ',' THEN
231 WRAP_POINT := LINE_LENGTH;
232 EXIT;
233 ELSIF IND = LINE_LENGTH AND CURRENT_CHAR <> ',' THEN
234 NULL;
235 ELSIF IND < LINE_LENGTH AND CURRENT_CHAR <> ',' THEN
236 NULL;
237 ELSIF IND < LINE_LENGTH AND CURRENT_CHAR = ',' THEN
238 WRAP_POINT := IND;
239 EXIT;
240 END IF;
241 END LOOP;
242 IF WRAP_POINT < 21 THEN
243 REMAINING_CHARS := 60 - WRAP_POINT;
244 P_EMP_ADDR_LINE_1 := SUBSTR(P_EMPLOYER_ADDRESS
245 ,1
246 ,WRAP_POINT);
247 P_REMAINING_ADDRESS := SUBSTR(P_EMPLOYER_ADDRESS
248 ,WRAP_POINT + 1
249 ,REMAINING_CHARS);
250 WRAP_POINT := 38;
251 FOR ind IN REVERSE 1 .. LINE_LENGTH LOOP
252 CURRENT_CHAR := SUBSTR(P_REMAINING_ADDRESS
253 ,IND
254 ,1);
255 IF IND = LINE_LENGTH AND CURRENT_CHAR = ',' THEN
256 WRAP_POINT := LINE_LENGTH;
257 EXIT;
258 ELSIF IND = LINE_LENGTH AND CURRENT_CHAR <> ',' THEN
259 NULL;
260 ELSIF IND < LINE_LENGTH AND CURRENT_CHAR <> ',' THEN
261 NULL;
262 ELSIF IND < LINE_LENGTH AND CURRENT_CHAR = ',' THEN
263 WRAP_POINT := IND;
264 EXIT;
265 END IF;
266 END LOOP;
267 REMAINING_CHARS := 60 - WRAP_POINT;
268 P_EMP_ADDR_LINE_2 := LTRIM(SUBSTR(P_REMAINING_ADDRESS
269 ,1
270 ,WRAP_POINT));
271 P_EMP_ADDR_LINE_3 := LTRIM(SUBSTR(P_REMAINING_ADDRESS
272 ,WRAP_POINT + 1
273 ,REMAINING_CHARS));
274 ELSE
275 REMAINING_CHARS := 60 - WRAP_POINT;
276 P_EMP_ADDR_LINE_1 := SUBSTR(P_EMPLOYER_ADDRESS
277 ,1
278 ,WRAP_POINT);
279 P_EMP_ADDR_LINE_2 := LTRIM(SUBSTR(P_EMPLOYER_ADDRESS
280 ,WRAP_POINT + 1
281 ,REMAINING_CHARS));
282 END IF;
283 ELSE
284 P_EMP_ADDR_LINE_1 := P_EMPLOYER_ADDRESS;
285 P_EMP_ADDR_LINE_2 := NULL;
286 END IF;
287 END SPLIT_EMPLOYER_ADDRESS;
288
289 FUNCTION C_3_PARTFORMULA RETURN VARCHAR2 IS
290 BEGIN
291 DECLARE
292 L_3_PART NUMBER(1);
293 BEGIN
294 SELECT
295 1
296 INTO L_3_PART
297 FROM
298 FF_GLOBALS_F
299 WHERE GLOBAL_NAME = 'P45_REPORT_TYPE'
300 AND SUBSTR(GLOBAL_VALUE
301 ,1
302 ,1) = '3'
303 AND sysdate between EFFECTIVE_START_DATE
304 AND EFFECTIVE_END_DATE;
305 RETURN ('TRUE');
306 EXCEPTION
307 WHEN NO_DATA_FOUND THEN
308 RETURN ('FALSE');
309 END;
310 RETURN NULL;
311 END C_3_PARTFORMULA;
312
313 FUNCTION AFTERREPORT RETURN BOOLEAN IS
314 BEGIN
315 --HR_STANDARD.EVENT('AFTER REPORT');
316 RETURN (TRUE);
317 END AFTERREPORT;
318
319 FUNCTION C_TITLE_P RETURN VARCHAR2 IS
320 BEGIN
321 RETURN C_TITLE;
322 END C_TITLE_P;
323
324 FUNCTION C_NI12_P RETURN VARCHAR2 IS
325 BEGIN
326 RETURN C_NI12;
327 END C_NI12_P;
328
329 FUNCTION C_NI34_P RETURN VARCHAR2 IS
330 BEGIN
331 RETURN C_NI34;
332 END C_NI34_P;
333
334 FUNCTION C_NI56_P RETURN VARCHAR2 IS
335 BEGIN
336 RETURN C_NI56;
337 END C_NI56_P;
338
339 FUNCTION C_NI78_P RETURN VARCHAR2 IS
343
340 BEGIN
341 RETURN C_NI78;
342 END C_NI78_P;
344 FUNCTION C_NI9_P RETURN VARCHAR2 IS
345 BEGIN
346 RETURN C_NI9;
347 END C_NI9_P;
348
349 FUNCTION C_DATE_OF_LEAVING_DD_P RETURN VARCHAR2 IS
350 BEGIN
351 RETURN C_DATE_OF_LEAVING_DD;
352 END C_DATE_OF_LEAVING_DD_P;
353
354 FUNCTION C_DATE_OF_LEAVING_MM_P RETURN VARCHAR2 IS
355 BEGIN
356 RETURN C_DATE_OF_LEAVING_MM;
357 END C_DATE_OF_LEAVING_MM_P;
358
359 FUNCTION C_DATE_OF_LEAVING_YYYY_P RETURN VARCHAR2 IS
360 BEGIN
361 RETURN C_DATE_OF_LEAVING_YYYY;
362 END C_DATE_OF_LEAVING_YYYY_P;
363
364 FUNCTION C_TOTAL_TAX_TD_P RETURN NUMBER IS
365 BEGIN
366 RETURN C_TOTAL_TAX_TD;
367 END C_TOTAL_TAX_TD_P;
368
369 FUNCTION C_TOTAL_PAY_TD_P RETURN NUMBER IS
370 BEGIN
371 RETURN C_TOTAL_PAY_TD;
372 END C_TOTAL_PAY_TD_P;
373
374 FUNCTION C_PER_ADDRESS_LINE1_P RETURN VARCHAR2 IS
375 BEGIN
376 RETURN C_PER_ADDRESS_LINE1;
377 END C_PER_ADDRESS_LINE1_P;
378
379 FUNCTION C_PER_ADDRESS_LINE2_P RETURN VARCHAR2 IS
380 BEGIN
381 RETURN C_PER_ADDRESS_LINE2;
382 END C_PER_ADDRESS_LINE2_P;
383
384 FUNCTION C_PER_ADDRESS_LINE3_P RETURN VARCHAR2 IS
385 BEGIN
386 RETURN C_PER_ADDRESS_LINE3;
387 END C_PER_ADDRESS_LINE3_P;
388
389 FUNCTION C_PER_ADDRESS_LINE4_P RETURN VARCHAR2 IS
390 BEGIN
391 RETURN C_PER_ADDRESS_LINE4;
392 END C_PER_ADDRESS_LINE4_P;
393
394 FUNCTION C_PAY_IN_EMP_POUNDS_P RETURN NUMBER IS
395 BEGIN
396 RETURN C_PAY_IN_EMP_POUNDS;
397 END C_PAY_IN_EMP_POUNDS_P;
398
399 FUNCTION C_PAY_IN_EMP_PENCE_P RETURN NUMBER IS
400 BEGIN
401 RETURN C_PAY_IN_EMP_PENCE;
402 END C_PAY_IN_EMP_PENCE_P;
403
404 FUNCTION C_TAX_IN_EMP_POUNDS_P RETURN NUMBER IS
408
405 BEGIN
406 RETURN C_TAX_IN_EMP_POUNDS;
407 END C_TAX_IN_EMP_POUNDS_P;
409 FUNCTION C_TAX_IN_EMP_PENCE_P RETURN NUMBER IS
410 BEGIN
411 RETURN C_TAX_IN_EMP_PENCE;
412 END C_TAX_IN_EMP_PENCE_P;
413
414 FUNCTION C_PAY_TD_POUNDS_P RETURN NUMBER IS
415 BEGIN
416 RETURN C_PAY_TD_POUNDS;
417 END C_PAY_TD_POUNDS_P;
418
419 FUNCTION C_PAY_TD_PENCE_P RETURN NUMBER IS
420 BEGIN
421 RETURN C_PAY_TD_PENCE;
422 END C_PAY_TD_PENCE_P;
423
424 FUNCTION C_TAX_TD_POUNDS_P RETURN NUMBER IS
425 BEGIN
426 RETURN C_TAX_TD_POUNDS;
427 END C_TAX_TD_POUNDS_P;
428
429 FUNCTION C_TAX_TD_PENCE_P RETURN NUMBER IS
430 BEGIN
431 RETURN C_TAX_TD_PENCE;
432 END C_TAX_TD_PENCE_P;
433
434 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
435 BEGIN
436 RETURN C_BUSINESS_GROUP_NAME;
437 END C_BUSINESS_GROUP_NAME_P;
438
439 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
440 BEGIN
441 RETURN C_REPORT_SUBTITLE;
442 END C_REPORT_SUBTITLE_P;
443
444 FUNCTION C_FORMULA_ID_P RETURN NUMBER IS
445 BEGIN
446 RETURN C_FORMULA_ID;
447 END C_FORMULA_ID_P;
448
449 FUNCTION C_MESSAGE_P RETURN VARCHAR2 IS
450 BEGIN
451 RETURN C_MESSAGE;
452 END C_MESSAGE_P;
453
454 FUNCTION C_ERS_ADDR_LINE1_P RETURN VARCHAR2 IS
455 BEGIN
456 RETURN C_ERS_ADDR_LINE1;
457 END C_ERS_ADDR_LINE1_P;
458
459 FUNCTION C_ERS_ADDR_LINE2_P RETURN VARCHAR2 IS
460 BEGIN
461 RETURN C_ERS_ADDR_LINE2;
462 END C_ERS_ADDR_LINE2_P;
463
464 FUNCTION C_ERS_ADDR_LINE3_P RETURN VARCHAR2 IS
465 BEGIN
466 RETURN C_ERS_ADDR_LINE3;
467 END C_ERS_ADDR_LINE3_P;
468
469 FUNCTION C_ERS_NAME_P RETURN VARCHAR2 IS
470 BEGIN
471 RETURN C_ERS_NAME;
472 END C_ERS_NAME_P;
473
474 FUNCTION C_TAX_DIST_NO_P RETURN VARCHAR2 IS
475 BEGIN
476 RETURN C_TAX_DIST_NO;
477 END C_TAX_DIST_NO_P;
478
479 FUNCTION C_TAX_DIST_REF_P RETURN VARCHAR2 IS
480 BEGIN
481 RETURN C_TAX_DIST_REF;
482 END C_TAX_DIST_REF_P;
483
484 END PAY_PAYGBP45_XMLP_PKG;