1 PACKAGE BODY PAY_PAYSGSOE_XMLP_PKG AS
2 /* $Header: PAYSGSOEB.pls 120.0 2007/12/13 12:23:31 amakrish noship $ */
3
4 function cf_gross_pay_currformula(assignment_action_id in number, person_id in number) return number is
5 v_gross_pay_curr number;
6 v_ass_act_id pay_assignment_actions.assignment_action_id%type;
7 cursor c_pay_ass_act_id(v_assignment_action_id pay_assignment_actions.assignment_action_id%type) is
8 select max(pai.locked_action_id) from pay_action_interlocks pai
9 where pai.locking_action_id = v_assignment_action_id;
10 begin
11 open c_pay_ass_act_id(assignment_action_id);
12 fetch c_pay_ass_act_id into v_ass_act_id;
13 if v_ass_act_id is NULL then
14 v_ass_act_id := assignment_action_id;
15 end if;
16 close c_pay_ass_act_id;
17
18
19
20 pay_sg_soe.balance_totals( p_assignment_action_id => v_ass_act_id,
21 p_person_id => person_id,
22 p_gross_pay_current => v_gross_pay_curr,
23 p_statutory_deductions_current => cp_stat_ded_curr,
24 p_other_deductions_current => cp_other_curr,
25 p_net_pay_current => cp_net_pay_curr,
26 p_non_payroll_current => cp_non_pay_curr,
27 p_gross_pay_ytd => cp_gross_pay_ytd,
28 p_statutory_deductions_ytd => cp_stat_ded_ytd,
29 p_other_deductions_ytd => cp_other_ytd,
30 p_net_pay_ytd => cp_net_pay_ytd,
31 p_non_payroll_ytd => cp_non_pay_ytd,
32 p_employee_cpf_current => cp_employee_cpf_curr,
33 p_employer_cpf_current => cp_employer_cpf_curr,
34 p_cpf_total_current => cp_total_cpf_curr,
35 p_employee_cpf_ytd => cp_employee_cpf_ytd,
36 p_employer_cpf_ytd => cp_employer_cpf_ytd,
37 p_cpf_total_ytd => cp_total_cpf_ytd
38 );
39 RETURN(v_gross_pay_curr);
40 end;
41
42 function cf_address_line1formula(expense_check_send_to_address in varchar2, person_id in number, location_id in number) return char is
43
44 v_address_line_1 hr_locations.address_line_1%type;
45 v_address_line_2 hr_locations.address_line_2%type;
46 v_address_line_3 hr_locations.address_line_3%type;
47 v_town_city hr_locations.town_or_city%type;
48 v_postal_code hr_locations.postal_code%type;
49 v_country fnd_territories_tl.territory_short_name%type;
50 begin
51 if expense_check_send_to_address = 'H' then
52 pay_sg_soe.get_home_address(person_id,
53 v_address_line_1,
54 v_address_line_2,
55 v_address_line_3,
56 v_town_city,
57 v_postal_code,
58 v_country);
59 else pay_sg_soe.get_work_address(location_id,
60 v_address_line_1,
61 v_address_line_2,
62 v_address_line_3,
63 v_town_city,
64 v_postal_code,
65 v_country);
66 end if;
67
68 cp_address_line2 := v_address_line_2;
69 cp_address_line3 := v_address_line_3;
70 cp_town := v_town_city;
71 cp_post_code := v_postal_code;
72 cp_country := v_country;
73
74 return v_address_line_1;
75 end;
76
77 function cf_get_absence_detailsformula(assignment_id_l in number, accrual_plan_id_l in number, period_end_date in date, period_start_date in date, payroll_id_l in number, business_group_id_l in number, effective_date_l in date) return number is
78
79 begin
80 cp_abs_this_period := per_accrual_calc_functions.get_absence
81 (p_assignment_id => assignment_id_l,
82 p_plan_id => accrual_plan_id_l,
83 p_calculation_date => period_end_date,
84 p_start_date => period_start_date);
85
86 cp_net_accrual := pay_sg_soe.net_accrual
87 (p_assignment_id => assignment_id_l,
88 p_plan_id => accrual_plan_id_l,
89 p_payroll_id => payroll_id_l,
90 p_business_group_id => business_group_id_l,
91 p_effective_date => effective_date_l);
92
93 return 0;
94 end;
95
96 function cf_hourly_rateformula(hours in number, amount in number) return number is
97 v_rate NUMBER;
98 begin
99 IF nvl(hours,0) <> 0 THEN
100 v_rate := amount/hours;
101 END IF;
102 return(v_rate);
103 end;
104
105 function CF_CURRENCY_FORMAT_MASKFormula return Char is
106
107 v_currency_code fnd_currencies.currency_code%type;
108 v_format_mask varchar2(100) := null;
109 v_field_length number(3) := 15;
110
111 begin
112 v_currency_code := pay_sg_soe.business_currency_code(p_business_group_id);
113 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
114
115 return v_format_mask;
116 end;
117
118 function CF_PERCENT_FORMAT_MASKFormula return Char is
119 v_mask varchar2(30);
120 begin
121 v_mask := '990D0';
122 return(v_mask);
123 end;
124
125 function CF_HOURS_FORMAT_MASKFormula return Char is
126 v_mask varchar2(30);
127 begin
128 v_mask := '990D0';
129 return(v_mask);
130 end;
131
132 function CF_RATE_FORMAT_MASKFormula return Char is
133 v_mask varchar2(30);
134 begin
135 v_mask := '990D90';
136 return(v_mask);
137 end;
138
139 function CF_FX_RATE_FORMAT_MASKFormula return Char is
140 v_mask varchar2(30);
141 begin
142 v_mask := '990D9990';
143 return(v_mask);
144 end;
145
146 function BeforeReport return boolean is
147 begin
148
149 construct_where_clause;
150 construct_order_by;
151
152 -- hr_standard.event('BEFORE REPORT');
153 return (TRUE);
154 end;
155
156 PROCEDURE construct_where_clause IS
157
158 begin
159
160 --cp_where_clause := null;
161 cp_where_clause :=' ';
162
163 if p_assignment_id is not null then
164 cp_where_clause := ' and assignment_id = ' || to_char(p_assignment_id);
165 end if;
166
167 if p_location_id is not null then
168 cp_where_clause := cp_where_clause || ' and location_id = ' || to_char(p_location_id);
169 end if;
170
171 if p_organization_name is not null then
172 cp_where_clause := cp_where_clause || ' and legal_employer = ' || '''' || p_organization_name || '''';
173 end if;
174 end;
175
176 PROCEDURE construct_order_by IS
177 begin
178 cp_order_by := null;
179
180
181 if p_sort_order_1 is not null then
182 cp_order_by := p_sort_order_1;
183 end if;
184
185 if p_sort_order_2 is not null then
186 if cp_order_by is not null then
187 cp_order_by := cp_order_by || ', ' || p_sort_order_2;
188 else
189 cp_order_by := p_sort_order_2;
190 end if;
191 end if;
192
193 if p_sort_order_3 is not null then
194 if cp_order_by is not null then
195 cp_order_by := cp_order_by || ', ' || p_sort_order_3;
196 else
197 cp_order_by := p_sort_order_3;
198 end if;
199 end if;
200
201 if p_sort_order_4 is not null then
202 if cp_order_by is not null then
203 cp_order_by := cp_order_by || ', ' || p_sort_order_4;
204 else
205 cp_order_by := p_sort_order_4;
206 end if;
207 end if;
208
209 if cp_order_by is not null then
210 cp_order_by := ' order by ' || cp_order_by;
211 else
212 cp_order_by := ' order by assignment_number';
213 end if;
214 end;
215
216 function AfterReport return boolean is
217 begin
218 -- hr_standard.event('AFTER REPORT');
219 return (TRUE);
220 end;
221
222 function cf_1formula(ELEMENT_REPORTING_NAME in varchar2) return char is
223 begin
224 IF ELEMENT_REPORTING_NAME IS NULL THEN
225 CP_DISPLAY_EARNINGS := 'FALSE';
226 ELSE
227 CP_DISPLAY_EARNINGS := 'TRUE';
228 END IF;
229 RETURN(NULL);
230 end;
231
232 function cf_deductions_existformula(ELEMENT_REPORTING_NAME1 in varchar2) return char is
233 begin
234 IF ELEMENT_REPORTING_NAME1 IS NULL THEN
235 CP_DISPLAY_DEDUCTIONS := 'FALSE';
236 ELSE
237 CP_DISPLAY_DEDUCTIONS := 'TRUE';
238 END IF;
239 RETURN(NULL);
240 end;
241
242 function cf_messages_existformula(PAY_ADVICE_MESSAGE in varchar2) return char is
243 begin
244 IF PAY_ADVICE_MESSAGE IS NULL THEN
245 CP_DISPLAY_MESSAGES := 'FALSE';
246 ELSE
247 CP_DISPLAY_MESSAGES := 'TRUE';
248 END IF;
249 RETURN(NULL);
250 end;
251
252 function cf_fx_amountformula(exchange_rate in number, amount in number) return number is
253 v_fx_amount number;
254 begin
255 if NVL(exchange_rate, 0) <> 0 then
256 v_fx_amount := amount/exchange_rate;
257 end if;
258 return v_fx_amount;
259 end;
260
261 --Functions to refer Oracle report placeholders--
262
263 Function CP_ADDRESS_LINE2_p return varchar2 is
264 Begin
265 return CP_ADDRESS_LINE2;
266 END;
267 Function CP_ADDRESS_LINE3_p return varchar2 is
268 Begin
269 return CP_ADDRESS_LINE3;
270 END;
271 Function CP_TOWN_p return varchar2 is
272 Begin
273 return CP_TOWN;
274 END;
275 Function CP_POST_CODE_p return varchar2 is
276 Begin
277 return CP_POST_CODE;
278 END;
279 Function CP_COUNTRY_p return varchar2 is
280 Begin
281 return CP_COUNTRY;
282 END;
283 Function CP_GROSS_PAY_YTD_p return number is
284 Begin
285 return CP_GROSS_PAY_YTD;
286 END;
287 Function CP_STAT_DED_CURR_p return number is
288 Begin
289 return CP_STAT_DED_CURR;
290 END;
291 Function CP_STAT_DED_YTD_p return number is
292 Begin
293 return CP_STAT_DED_YTD;
294 END;
295 Function CP_OTHER_CURR_p return number is
296 Begin
297 return CP_OTHER_CURR;
298 END;
299 Function CP_OTHER_YTD_p return number is
300 Begin
301 return CP_OTHER_YTD;
302 END;
303 Function CP_NON_PAY_CURR_p return number is
304 Begin
305 return CP_NON_PAY_CURR;
306 END;
307 Function CP_NON_PAY_YTD_p return number is
308 Begin
309 return CP_NON_PAY_YTD;
310 END;
311 Function CP_NET_PAY_CURR_p return number is
312 Begin
313 return CP_NET_PAY_CURR;
314 END;
315 Function CP_NET_PAY_YTD_p return number is
316 Begin
317 return CP_NET_PAY_YTD;
318 END;
319 Function CP_EMPLOYEE_CPF_CURR_p return number is
320 Begin
321 return CP_EMPLOYEE_CPF_CURR;
322 END;
323 Function CP_EMPLOYEE_CPF_YTD_p return number is
324 Begin
325 return CP_EMPLOYEE_CPF_YTD;
326 END;
327 Function CP_EMPLOYER_CPF_CURR_p return number is
328 Begin
329 return CP_EMPLOYER_CPF_CURR;
330 END;
331 Function CP_EMPLOYER_CPF_YTD_p return number is
332 Begin
333 return CP_EMPLOYER_CPF_YTD;
334 END;
335 Function CP_TOTAL_CPF_CURR_p return number is
336 Begin
337 return CP_TOTAL_CPF_CURR;
338 END;
339 Function CP_TOTAL_CPF_YTD_p return number is
340 Begin
341 return CP_TOTAL_CPF_YTD;
342 END;
343 Function CP_ABS_THIS_PERIOD_p return number is
344 Begin
345 return CP_ABS_THIS_PERIOD;
346 END;
347 Function CP_NET_ACCRUAL_p return number is
348 Begin
349 return CP_NET_ACCRUAL;
350 END;
351 Function CP_WHERE_CLAUSE_p return varchar2 is
352 Begin
353 return CP_WHERE_CLAUSE;
354 END;
355 Function CP_ORDER_BY_p return varchar2 is
356 Begin
357 return CP_ORDER_BY;
358 END;
359 Function CP_DISPLAY_EARNINGS_p return varchar2 is
360 Begin
361 return CP_DISPLAY_EARNINGS;
362 END;
363 Function CP_DISPLAY_DEDUCTIONS_p return varchar2 is
364 Begin
365 return CP_DISPLAY_DEDUCTIONS;
366 END;
367 Function CP_DISPLAY_MESSAGES_p return varchar2 is
368 Begin
369 return CP_DISPLAY_MESSAGES;
370 END;
371 END PAY_PAYSGSOE_XMLP_PKG ;
[Home] [Help]