[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYGB45L_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYGB45L_XMLP_PKG AS
2 /* $Header: PAYGB45LB.pls 120.1 2007/12/24 12:42:09 amakrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 declare
8 l_test number;
9 l_ers_address varchar2(60);
10 begin
11 /*srw.user_exit('FND SRWINIT');*/null;
12 insert into fnd_sessions (session_id,effective_date)
13 select userenv('sessionid'),trunc(sysdate)
14 from dual
15 where not exists
16 (select 1
17 from fnd_sessions fs
18 where fs.session_id = userenv('sessionid'));
19
20 p_session_date:=sysdate;
21 p_date_today:=sysdate;
22
23
24 if P_ASSIGNMENT_ACTION_ID is null then
25 P_ACTION_RESTRICTION := 'AND act.payroll_action_id = '||P_PAYROLL_ACTION_ID;
26 else
27 P_ACTION_RESTRICTION := 'AND act.assignment_action_id = '||P_ASSIGNMENT_ACTION_ID;
28 end if;
29 select substr(pay_gb_eoy_archive.get_parameter
30 (p.legislative_parameters,'TAX_REF'),1,3),
31 substr(ltrim(substr(pay_gb_eoy_archive.get_parameter
32 (p.legislative_parameters,'TAX_REF'),4,11),'/') ,1,10),
33 substr(pay_gb_eoy_archive.get_arch_str
34 (p.payroll_action_id,
35 'X_EMPLOYERS_ADDRESS_LINE','0'),1,60),
36 substr(pay_gb_eoy_archive.get_arch_str
37 (p.payroll_action_id,
38 'X_EMPLOYERS_NAME','0'),1,40)
39 into C_TAX_DIST_NO, C_TAX_DIST_REF, l_ers_address, C_ERS_NAME
40 from pay_payroll_actions p
41 where p.payroll_action_id = P_PAYROLL_ACTION_ID;
42 split_employer_address(l_ers_address,
43 c_ers_addr_line1,
44 c_ers_addr_line2,
45 c_ers_addr_line3 );
46 end; return (TRUE);
47 end;
48
49 function c_format_dataformula(address_line1 in varchar2, address_line2 in varchar2, address_line3 in varchar2,
50 town_or_city in varchar2, county in varchar2, post_code in varchar2, taxable_pay in number,
51 previous_taxable_pay in number, tax_paid in number, previous_tax_paid in number, ni_number in varchar2,
52 termination_date in date, c_3_part in varchar2, w1_m1_indicator in varchar2, month_number in number,
53 week_number in number) return varchar2 is
54 --pragma autonomous_transaction;
55 begin
56 c_per_address_line1 := address_line1;
57 c_per_address_line2 := substr(rtrim(address_line2)||
58 rtrim(', '||address_line3,', '),1,60);
59
60 c_per_address_line3 := rpad(rtrim(town_or_city)||' '||
61 rtrim(county),37-NVL(length(post_code), 0))||
62 ' '||post_code;
63
64 c_total_pay_td := nvl(taxable_pay,0) + nvl(previous_taxable_pay,0);
65 c_total_tax_td := nvl(tax_paid,0) + nvl(previous_tax_paid,0);
66 c_ni12 := substr(ni_number,1,2);
67 c_ni34 := substr(ni_number,3,2);
68 c_ni56 := substr(ni_number,5,2);
69 c_ni78 := substr(ni_number,7,2);
70 c_ni9 := substr(ni_number,9,1);
71
72 c_date_of_leaving_dd := to_char(termination_date,'DD');
73 c_date_of_leaving_mm := to_char(termination_date,'MM');
74 c_date_of_leaving_yyyy := to_char(termination_date,'YYYY');
75
76 if substr(c_date_of_leaving_dd,1,1) = '0' then
77 c_date_of_leaving_dd := ' ' || substr(c_date_of_leaving_dd,2,2);
78 end if;
79
80 if substr(c_date_of_leaving_mm,1,1) = '0' then
81 c_date_of_leaving_mm := ' ' || substr(c_date_of_leaving_mm,2,2);
82 end if;
83 If c_3_part = 'TRUE' then
84
85 if w1_m1_indicator is null then
86 get_pounds_pence(c_total_pay_td,c_pay_td_pounds,c_pay_td_pence);
87 get_pounds_pence(c_total_tax_td,c_tax_td_pounds,c_tax_td_pence);
88 c_pay_in_emp_pounds := '';
89 c_pay_in_emp_pence := '';
90 c_tax_in_emp_pounds := '';
91 c_tax_in_emp_pence := '';
92 c_month_no := month_number;
93 c_week_no := week_number;
94 else
95 get_pounds_pence(taxable_pay,c_pay_in_emp_pounds,c_pay_in_emp_pence);
96 get_pounds_pence(tax_paid,c_tax_in_emp_pounds,c_tax_in_emp_pence);
97 c_pay_td_pounds := '';
98 c_pay_td_pence := '';
99 c_tax_td_pounds := '';
100 c_tax_td_pence := '';
101 c_month_no := '';
102 c_week_no := '';
103 end if;
104 Elsif c_3_part = 'FALSE' then
105
106 if w1_m1_indicator is null then
107 get_pounds_pence(c_total_pay_td,c_pay_td_pounds,c_pay_td_pence);
108 get_pounds_pence(c_total_tax_td,c_tax_td_pounds,c_tax_td_pence);
109 if nvl(previous_taxable_pay,0) = 0 and
110 nvl(previous_tax_paid,0) = 0 then
111 c_pay_in_emp_pounds := '';
112 c_pay_in_emp_pence := '';
113 c_tax_in_emp_pounds := '';
114 c_tax_in_emp_pence := '';
115 else
116 get_pounds_pence(taxable_pay,c_pay_in_emp_pounds,c_pay_in_emp_pence);
117 get_pounds_pence(tax_paid,c_tax_in_emp_pounds,c_tax_in_emp_pence);
118 end if;
119 c_month_no := month_number;
120 c_week_no := week_number;
121 else
122 get_pounds_pence(taxable_pay,c_pay_in_emp_pounds,c_pay_in_emp_pence);
123 get_pounds_pence(tax_paid,c_tax_in_emp_pounds,c_tax_in_emp_pence);
124 c_pay_td_pounds := '';
125 c_pay_td_pence := '';
126 c_tax_td_pounds := '';
127 c_tax_td_pence := '';
128 c_month_no := '';
129 c_week_no := '';
130 end if;
131
132 End if;
133 /* insert into log_msg values('sampath','paygb45l','C_TAX_TD_POUNDS',C_TAX_TD_POUNDS);
134 commit;*/
135 return NULL;
136
137
138 end;
139
140 procedure get_pounds_pence(p_total in number,
141 p_pounds in out NOCOPY number,
142 p_pence in out NOCOPY number) is
143 begin
144 if p_total <> 0 then
145 p_pounds := trunc(p_total);
146 p_pence := abs(100 * (p_total - p_pounds));
147 else
148 p_pounds := null;
149 p_pence := null;
150 end if;
151 end;
152
153 procedure split_employer_address(p_employer_address in varchar2,
154 p_emp_addr_line_1 in out NOCOPY varchar2,
155 p_emp_addr_line_2 in out NOCOPY varchar2,
156 p_emp_addr_line_3 in out NOCOPY varchar2) is
157
158 line_length constant number := 38;
159 out_line1 varchar2(38) := NULL;
160 out_line2 varchar2(38) := NULL;
161 current_char varchar2(1);
162 ind number;
163 remaining_chars number;
164 wrap_point number :=38;
165 p_remaining_address varchar2(60);
166
167 begin
168
169
170
171 if NVL(length(rtrim(p_employer_address)), 0) > 38 then
172
173 for ind in reverse 1..line_length LOOP
174
175 current_char := substr(p_employer_address,ind,1);
176
177 if ind = line_length and current_char = ',' then
178 wrap_point := line_length;
179 exit;
180 elsif ind = line_length and current_char <> ',' then
181 null;
182 elsif ind < line_length and current_char <> ',' then
183 null;
184 elsif ind < line_length and current_char = ',' then
185 wrap_point := ind;
186 exit;
187 end if;
188
189 end loop;
190
191
192 if wrap_point < 21 then
193
194
195
196 remaining_chars := 60 - wrap_point;
197
198 p_emp_addr_line_1 := substr(p_employer_address,1,wrap_point);
199
200
201 p_remaining_address := substr(p_employer_address,wrap_point + 1, remaining_chars);
202
203 wrap_point := 38;
204
205 for ind in reverse 1..line_length LOOP
206
207 current_char := substr(p_remaining_address,ind,1);
208
209 if ind = line_length and current_char = ',' then
210 wrap_point := line_length;
211 exit;
212 elsif ind = line_length and current_char <> ',' then
213 null;
214 elsif ind < line_length and current_char <> ',' then
215 null;
216 elsif ind < line_length and current_char = ',' then
217 wrap_point := ind;
218 exit;
219 end if;
220
221 end loop;
222
223 remaining_chars := 60 - wrap_point;
224
225 p_emp_addr_line_2 := ltrim(substr(p_remaining_address,1,wrap_point));
226 p_emp_addr_line_3 := ltrim(substr(p_remaining_address,wrap_point+1,remaining_chars));
227
228
229 else
230
231
232 remaining_chars := 60 - wrap_point;
233
234
235 p_emp_addr_line_1 := substr(p_employer_address,1,wrap_point);
236 p_emp_addr_line_2 := ltrim(substr(p_employer_address,wrap_point+1,remaining_chars));
237
238 end if;
239
240
241
242 else
243
244 p_emp_addr_line_1 := p_employer_address;
245 p_emp_addr_line_2 := NULL;
246
247 end if;
248
249 end;
250
251 function C_3_PARTFormula return VARCHAR2 is
252 begin
253
254 Declare
255 l_3_part number(1);
256 Begin
257
258
259 Select 1 into l_3_part
260 from ff_globals_f
261 where GLOBAL_NAME = 'P45_REPORT_TYPE'
262 and substr(GLOBAL_VALUE,1,1) = '3'
263 and sysdate between effective_start_date and effective_end_date;
264 return('TRUE');
265
266 Exception
267 When no_data_found then
268 return('FALSE');
269
270 End;
271 RETURN NULL; end;
272
273 function AfterReport return boolean is
274 begin
275 /*srw.user_exit('FND SRWEXIT');*/null;
276
277 return (TRUE);
278 end;
279
280 --Functions to refer Oracle report placeholders--
281
282 Function C_NI12_p return varchar2 is
283 Begin
284 return C_NI12;
285 END;
286 Function C_NI34_p return varchar2 is
287 Begin
288 return C_NI34;
289 END;
290 Function C_NI56_p return varchar2 is
291 Begin
292 return C_NI56;
293 END;
294 Function C_NI78_p return varchar2 is
295 Begin
296 return C_NI78;
297 END;
298 Function C_Ni9_p return varchar2 is
299 Begin
300 return C_Ni9;
301 END;
302 Function C_DATE_OF_LEAVING_DD_p return varchar2 is
303 Begin
304 return C_DATE_OF_LEAVING_DD;
305 END;
306 Function C_DATE_OF_LEAVING_MM_p return varchar2 is
307 Begin
308 return C_DATE_OF_LEAVING_MM;
309 END;
310 Function C_DATE_OF_LEAVING_YYYY_p return varchar2 is
311 Begin
312 return C_DATE_OF_LEAVING_YYYY;
313 END;
314 Function C_WEEK_NO_p return number is
315 Begin
316 return C_WEEK_NO;
317 END;
318 Function C_MONTH_NO_p return number is
319 Begin
320 return C_MONTH_NO;
321 END;
322 Function C_TOTAL_TAX_TD_p return number is
323 Begin
324 return C_TOTAL_TAX_TD;
325 END;
326 Function C_TOTAL_PAY_TD_p return number is
327 Begin
328 return C_TOTAL_PAY_TD;
329 END;
330 Function C_PER_ADDRESS_LINE1_p return varchar2 is
331 Begin
332 return C_PER_ADDRESS_LINE1;
333 END;
334 Function C_PER_ADDRESS_LINE2_p return varchar2 is
335 Begin
336 return C_PER_ADDRESS_LINE2;
337 END;
338 Function C_PER_ADDRESS_LINE3_p return varchar2 is
339 Begin
340 return C_PER_ADDRESS_LINE3;
341 END;
342 Function C_PER_ADDRESS_LINE4_p return varchar2 is
343 Begin
344 return C_PER_ADDRESS_LINE4;
345 END;
346 Function C_PAY_IN_EMP_POUNDS_p return number is
347 Begin
348 return C_PAY_IN_EMP_POUNDS;
349 END;
350 Function C_PAY_IN_EMP_PENCE_p return number is
351 Begin
352 return C_PAY_IN_EMP_PENCE;
353 END;
354 Function C_TAX_IN_EMP_POUNDS_p return number is
355 Begin
356 return C_TAX_IN_EMP_POUNDS;
357 END;
358 Function C_TAX_IN_EMP_PENCE_p return number is
359 Begin
360 return C_TAX_IN_EMP_PENCE;
361 END;
362 Function C_PAY_TD_POUNDS_p return number is
363 Begin
364 return C_PAY_TD_POUNDS;
365 END;
366 Function C_PAY_TD_PENCE_p return number is
367 Begin
368 return C_PAY_TD_PENCE;
369 END;
370 Function C_TAX_TD_POUNDS_p return number is
371 Begin
372 return C_TAX_TD_POUNDS;
373 END;
374 Function C_TAX_TD_PENCE_p return number is
375 Begin
376 return C_TAX_TD_PENCE;
377 END;
378 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
379 Begin
380 return C_BUSINESS_GROUP_NAME;
381 END;
382 Function C_REPORT_SUBTITLE_p return varchar2 is
383 Begin
384 return C_REPORT_SUBTITLE;
385 END;
386 Function C_FORMULA_ID_p return number is
387 Begin
388 return C_FORMULA_ID;
389 END;
390 Function C_MESSAGE_p return varchar2 is
391 Begin
392 return C_MESSAGE;
393 END;
394 Function C_NEW_PAGE_p return varchar2 is
395 Begin
396 return C_NEW_PAGE;
397 END;
398 Function C_ERS_ADDR_LINE1_p return varchar2 is
399 Begin
400 return C_ERS_ADDR_LINE1;
401 END;
402 Function C_ERS_ADDR_LINE2_p return varchar2 is
403 Begin
404 return C_ERS_ADDR_LINE2;
405 END;
406 Function C_ERS_ADDR_LINE3_p return varchar2 is
407 Begin
408 return C_ERS_ADDR_LINE3;
409 END;
410 Function C_TAX_DIST_NO_p return varchar2 is
411 Begin
412 return C_TAX_DIST_NO;
413 END;
414 Function C_TAX_DIST_REF_p return varchar2 is
415 Begin
416 return C_TAX_DIST_REF;
417 END;
418 Function C_ERS_NAME_p return varchar2 is
419 Begin
420 return C_ERS_NAME;
421 END;
422 END PAY_PAYGB45L_XMLP_PKG ;