[Home] [Help]
PACKAGE BODY: APPS.PAY_PYNZSOE_XMLP_PKG
Source
1 PACKAGE BODY PAY_PYNZSOE_XMLP_PKG AS
2 /* $Header: PYNZSOEB.pls 120.0 2007/12/13 12:22:45 amakrish noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 function BeforeReport return boolean is
7 l_param_display_payroll_name pay_payrolls_f.Payroll_Name%TYPE;
8 l_param_display_payroll_action hr_lookups.meaning%TYPE;
9 l_token number(1);
10
11 BEGIN
12
13
14 /*srw.user_exit('FND SRWINIT');*/null;
15
16
17 l_token := 1;
18 select payroll_name
19 into l_param_display_payroll_name
20 from pay_payrolls_f
21 where payroll_id = P_Payroll_Id;
22
23 CP_PAYROLL_NAME := l_param_display_payroll_name;
24
25
26 l_token := 2;
27 select fcl.meaning
28 into l_param_display_payroll_action
29 from pay_payroll_actions ppa,
30 hr_lookups fcl
31 where fcl.lookup_type = 'ACTION_TYPE'
32 and ppa.payroll_action_id = P_Payroll_Action_Id
33 and ppa.action_type = fcl.lookup_code;
34
35 CP_PAYMENT_RUN:= l_param_display_payroll_action;
36
37
38 --cp_where_clause := null;
39 cp_where_clause := ' ';
40
41 l_token := 3;
42 IF p_assignment_id is not null THEN
43 CP_WHERE_CLAUSE := ' and assignment_id = ' || to_char(p_assignment_id);
44 END IF;
45
46 l_token := 4;
47 IF p_location_id is not null THEN
48 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || ' and location_id = ' || to_char(p_location_id);
49 END IF;
50
51 l_token := 5;
52 IF p_organisation_name is not null THEN
53 CP_WHERE_CLAUSE := CP_WHERE_CLAUSE || ' and registered_employer = ''' || p_organisation_name ||'''';
54 END IF;
55
56
57 cp_order_by := null;
58
59 if p_sort_order_1 = 'FULL_NAME' then
60 p_sort_order_1 := 'nvl(order_name,full_name)';
61 end if;
62
63 if p_sort_order_2 = 'FULL_NAME' then
64 p_sort_order_2 := 'nvl(order_name,full_name)';
65 end if ;
66
67 if p_sort_order_3 = 'FULL_NAME' then
68 p_sort_order_3 := 'nvl(order_name,full_name)';
69 end if;
70
71 if p_sort_order_4 = 'FULL_NAME' then
72 p_sort_order_4 := 'nvl(order_name,full_name)';
73 end if ;
74
75
76
77 if p_sort_order_1 is not null then
78 cp_order_by := p_sort_order_1;
79 end if;
80
81 if p_sort_order_2 is not null then
82 if cp_order_by is not null then
83 cp_order_by := cp_order_by || ', ' || p_sort_order_2;
84 else
85 cp_order_by := p_sort_order_2;
86 end if;
87 end if;
88
89 if p_sort_order_3 is not null then
90 if cp_order_by is not null then
91 cp_order_by := cp_order_by || ', ' || p_sort_order_3;
92 else
93 cp_order_by := p_sort_order_3;
94 end if;
95 end if;
96
97 if p_sort_order_4 is not null then
98 if cp_order_by is not null then
99 cp_order_by := cp_order_by || ', ' || p_sort_order_4;
100 else
101 cp_order_by := p_sort_order_4;
102 end if;
103 end if;
104
105 if cp_order_by is not null then
106 cp_order_by := ' order by ' || cp_order_by;
107 else
108 cp_order_by := ' order by assignment_number';
109 end if;
110
111 return (TRUE);
112
113 RETURN NULL; EXCEPTION
114 WHEN USER_EXIT_FAILURE /*srw.user_exit_failure */THEN
115 BEGIN
116 /*srw.message(100, 'Foundation is not initialised');*/null;
117
118 raise_application_error(-20101,null);/*srw.program_abort;*/null;
119
120 END;
121
122 RETURN NULL; WHEN NO_DATA_FOUND THEN
123 IF l_token = 1 THEN
124 /*srw.message(100, 'Payroll ' || to_char(P_Payroll_Id) || ' does not exist.');*/null;
125
126 raise_application_error(-20101,null);/*srw.program_abort;*/null;
127
128 ELSIF l_token = 2 THEN
129 /*srw.message(200, 'Payroll Action Id ' || to_char(P_Payroll_Action_Id) || ' is not valid.');*/null;
130
131 raise_application_error(-20101,null);/*srw.program_abort;*/null;
132
133 ELSIF l_token in (3,4,5) THEN
134 /*srw.message(300, 'Where clause substitution failed.');*/null;
135
136 raise_application_error(-20101,null);/*srw.program_abort;*/null;
137
138 END IF;
139
140 RETURN NULL; WHEN OTHERS THEN
141 /*srw.message( 1000, 'Report encountered an undefined error.');*/null;
142
143 return (FALSE);
144
145 END;
146
147 function AfterReport return boolean is
148 begin
149
150 /*srw.user_exit('FND SRWEXIT');*/null;
151
152 return (TRUE);
153 end;
154
155 --function f_get_detailsformula(ass_number in varchar2, assignment_id in number, date_earned in date, home_office_ind in varchar2, person_id1 in number, location_id1 in number) return number is
156 function f_get_detailsformula(ass_number in varchar2, v_assignment_id in number, date_earned in date, home_office_ind in varchar2, person_id1 in number, location_id1 in number) return number is
157
158
159 l_address_line_1 varchar2(60);
160 l_address_line_2 varchar2(60);
161 l_address_line_3 varchar2(60);
162 l_town_city varchar2(60);
163 l_postcode varchar2(60);
164 l_country varchar2(60);
165 l_position_name varchar2(30);
166
167
168 BEGIN
169
170 BEGIN
171 IF ass_number is not null THEN
172 select substr(ppos.name,1,30)
173 into l_position_name
174 from per_assignments_f paf,
175 per_positions ppos
176 where paf.position_id = ppos.position_id
177 --and paf.assignment_id = assignment_id
178 and paf.assignment_id = v_assignment_id
179 and date_earned between paf.effective_start_date and paf.effective_end_date;
180
181 p_position_name := l_position_name;
182 END IF;
183 EXCEPTION
184 WHEN NO_DATA_FOUND THEN
185 p_position_name := 'Position name N/A';
186 END;
187
188 IF home_office_ind = 'H' THEN
189 pay_nz_soe_pkg.get_home_address(p_person_id => person_id1,
190 p_addr_line1 => l_address_line_1,
191 p_addr_line2 => l_address_line_2,
192 p_addr_line3 => l_address_line_3,
193 p_town_city => l_town_city,
194 p_postal_code => l_postcode,
195 p_country_name => l_country);
196
197 p_address_line_1 := l_address_line_1;
198 p_address_line_2 := l_address_line_2;
199 p_address_line_3 := l_address_line_3;
200 p_town_city := l_town_city;
201 p_postcode := l_postcode;
202 p_country := l_country;
203
204
205 ELSIF home_office_ind <> 'H' or home_office_ind is null THEN
206 IF location_id1 is not null THEN
207 pay_nz_soe_pkg.get_work_address(p_location_id => location_id1,
208 p_addr_line1 => l_address_line_1,
209 p_addr_line2 => l_address_line_2,
210 p_addr_line3 => l_address_line_3,
211 p_town_city => l_town_city,
212 p_postal_code => l_postcode,
213 p_country_name => l_country);
214 p_address_line_1 := l_address_line_1;
215 p_address_line_2 := l_address_line_2;
216 p_address_line_3 := l_address_line_3;
217 p_town_city := l_town_city;
218 p_postcode := l_postcode;
219 p_country := l_country;
220
221 END IF;
222 END IF;
223
224 RETURN(1);
225
226 END;
227
228 function f_get_cumulative_leave_balform(leave_balance_absence_type in varchar2, leave_balance_assignment_id in number, leave_balance_payroll_id in number, leave_balance_bus_grp_id in number, leave_balance_accrual_plan_id in number,
229 period_end_date in date) return number is
230 l_cumulative_bal number(10) := 0;
231
232 BEGIN
233 IF leave_balance_absence_type is not null THEN
234 l_cumulative_bal := HR_NZ_HOLIDAYS.GET_NET_ACCRUAL( leave_balance_assignment_id,
235 leave_balance_payroll_id,
236 leave_balance_bus_grp_id,
237 leave_balance_accrual_plan_id,
238 period_end_date);
239 p_cumulative_leave_bal := l_cumulative_bal;
240
241 return(l_cumulative_bal);
242 END IF;
243 RETURN NULL; END;
244
245 function cf_amount_paidformula(classification_name in varchar2, earnings_element_value in number) return number is
246
247 begin
248 IF classification_name = 'Employer Superannuation Contributions' THEN
249 return(0);
250 ELSIF classification_name = 'Deductions' THEN
251 return(earnings_element_value * -1);
252 ELSE
253 return(earnings_element_value);
254 END IF;
255 RETURN NULL; end;
256
257 function cf_get_miscellaneous_valuesfor(assignment_id in number, run_ass_action_id_link_from_q1 in number, date_earned in date) return number is
258 begin
259 pay_nz_soe_pkg.balance_totals( assignment_id,
260 run_ass_action_id_link_from_q1,
261 date_earned,
262 cp_gross_this_pay,
263 cp_other_deductions_this_pay,
264 cp_tax_deductions_this_pay,
265 cp_gross_ytd,
266 cp_other_deductions_ytd,
267 cp_tax_deductions_ytd,
268 cp_non_tax_allow_this_pay,
269 cp_non_tax_allow_ytd,
270 cp_pre_tax_deductions_this_pay,
271 cp_pre_tax_deductions_ytd);
272
273 cp_gross_this_pay := cp_gross_this_pay + cp_pre_tax_deductions_this_pay;
274 cp_gross_ytd := cp_gross_ytd + cp_pre_tax_deductions_ytd;
275
276 return 0;
277 end;
278
279 function CF_net_this_payFormula return Number is
280 begin
281
282
283 return (cp_gross_this_pay
284 + cp_non_tax_allow_this_pay
285 - cp_other_deductions_this_pay
286 - cp_tax_deductions_this_pay);
287 end;
288
289 function CF_net_ytdFormula return Number is
290 begin
291
292
293 return (cp_gross_ytd
294 + cp_non_tax_allow_ytd
295 - cp_other_deductions_ytd
296 - cp_tax_deductions_ytd);
297 end;
298
299 function CF_CURRENCY_FORMAT_MASKFormula return VARCHAR2 is
300
301
302
303 v_currency_code fnd_currencies.currency_code%type;
304 v_format_mask varchar2(100) := null;
305 v_field_length number(3) := 15;
306
307 begin
308 v_currency_code := pay_nz_soe_pkg.business_currency_code(p_business_group_id);
309 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
310
311 return v_format_mask;
312 end;
313
314 function G_Asg_Payments_Break_GGroupFil return boolean is
315 begin
316 /*srw.message(1000,'pay action id' || to_char(p_payroll_action_id));*/null;
317
318 return (TRUE);
319 end;
320
321 --Functions to refer Oracle report placeholders--
322
323 Function P_Address_Line_1_p return varchar2 is
324 Begin
325 return P_Address_Line_1;
326 END;
327 Function P_Address_Line_2_p return varchar2 is
328 Begin
329 return P_Address_Line_2;
330 END;
331 Function P_Address_Line_3_p return varchar2 is
332 Begin
333 return P_Address_Line_3;
334 END;
335 Function P_Town_City_p return varchar2 is
336 Begin
337 return P_Town_City;
338 END;
339 Function P_PostCode_p return varchar2 is
340 Begin
341 return P_PostCode;
342 END;
343 Function P_Country_p return varchar2 is
344 Begin
345 return P_Country;
346 END;
347 Function P_Position_Name_p return varchar2 is
348 Begin
349 return P_Position_Name;
350 END;
351 Function CP_non_tax_allow_this_pay_p return number is
352 Begin
353 return CP_non_tax_allow_this_pay;
354 END;
355 Function CP_non_tax_allow_ytd_p return number is
356 Begin
357 return CP_non_tax_allow_ytd;
358 END;
359 Function CP_gross_ytd_p return number is
360 Begin
361 return CP_gross_ytd;
362 END;
363 Function CP_gross_this_pay_p return number is
364 Begin
365 return CP_gross_this_pay;
366 END;
367 Function CP_other_deductions_ytd_p return number is
368 Begin
369 return CP_other_deductions_ytd;
370 END;
371 Function CP_other_deductions_this_pay_p return number is
372 Begin
373 return CP_other_deductions_this_pay;
374 END;
375 Function CP_tax_deductions_ytd_p return number is
376 Begin
377 return CP_tax_deductions_ytd;
378 END;
379 Function CP_pre_tax_deductions_this_pa return number is
380 Begin
381 return CP_pre_tax_deductions_this_pay;
382 END;
383 Function CP_pre_tax_deductions_ytd_p return number is
384 Begin
385 return CP_pre_tax_deductions_ytd;
386 END;
387 Function CP_tax_deductions_this_pay_p return number is
388 Begin
389 return CP_tax_deductions_this_pay;
390 END;
391 Function P_Cumulative_Leave_Bal_p return number is
392 Begin
393 return P_Cumulative_Leave_Bal;
394 END;
395 Function CP_Where_Clause_p return varchar2 is
396 Begin
397 return CP_Where_Clause;
398 END;
399 Function CP_Payroll_Name_p return varchar2 is
400 Begin
401 return CP_Payroll_Name;
402 END;
403 Function CP_Payment_Run_p return varchar2 is
404 Begin
405 return CP_Payment_Run;
406 END;
407 Function CP_ORDER_BY_p return varchar2 is
408 Begin
409 return CP_ORDER_BY;
410 END;
411 END PAY_PYNZSOE_XMLP_PKG ;