[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYCNSOE_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYCNSOE_XMLP_PKG AS
2 /* $Header: PAYCNSOEB.pls 120.1 2008/01/07 13:13:19 srikrish noship $ */
3
4 function BeforeReport return boolean is
5 begin
6
7 P_START_DATE_t := to_date(substr(P_START_DATE,1,10),'YYYY/MM/DD');
8 P_END_DATE_t := to_date(substr(P_END_DATE,1,10),'YYYY/MM/DD');
9 P_START_DATE_DISP := to_char(P_START_DATE_t,'DD-MON-YY');
10 P_END_DATE_DISP := to_char(P_END_DATE_t,'DD-MON-YY');
11 construct_order_by;
12 get_parameters_name;
13 /*srw.user_exit('FND SRWINIT');*/null;
14
15 return (TRUE);
16 end;
17
18 function AfterReport return boolean is
19 begin
20 /*srw.user_exit('FND SRWEXIT');*/null;
21
22 return (TRUE);
23 end;
24
25 function CF_business_groupFormula return VARCHAR2 is
26 v_business_group hr_all_organization_units.name%type;
27
28 begin
29 v_business_group := hr_reports.get_business_group(p_business_group_id);
30 return v_business_group;
31 end;
32
33 function CF_legislation_codeFormula return VARCHAR2 is
34
35 v_legislation_code hr_organization_information.org_information9%type := null;
36
37 cursor legislation_code
38 (c_business_group_id hr_organization_information.organization_id%type) is
39
40 select org_information9
41 from hr_organization_information
42 where organization_id = c_business_group_id
43 and org_information9 is not null
44 and org_information_context = 'Business Group Information';
45 begin
46 open legislation_code (p_business_group_id);
47 fetch legislation_code into v_legislation_code;
48 close legislation_code;
49
50 return v_legislation_code;
51 end;
52
53 function cf_currency_format_maskformula(cf_legislation_code in varchar2) return varchar2 is
54
55 v_currency_code fnd_currencies.currency_code%type;
56 v_format_mask varchar2(100) := null;
57 v_field_length number(3) := 14;
58
59 cursor currency_format_mask
60 (c_territory_code in fnd_currencies.issuing_territory_code%type) is
61 select currency_code
62 from fnd_currencies
63 where issuing_territory_code = c_territory_code;
64
65 begin
66 open currency_format_mask (cf_legislation_code);
67 fetch currency_format_mask into v_currency_code;
68 close currency_format_mask;
69
70 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
71
72 return v_format_mask;
73 end;
74
75 PROCEDURE set_currency_format_mask IS
76 BEGIN
77
78 /*SRW.SET_FORMAT_MASK(CF_currency_format_mask);*/null;
79
80
81 END;
82
83 function P_BUSINESS_GROUP_IDValidTrigge return boolean is
84 begin
85 return (TRUE);
86 end;
87
88 function CF_Net_This_PayFormula return Number is
89 begin
90
91 RETURN ( nvl(CP_TAXABLE_THIS_PAY,0)
92 + nvl(CP_NON_TAXABLE_THIS_PAY,0)
93 - nvl(CP_VOLUNTARY_THIS_PAY,0)
94 - nvl(CP_STATUTORY_THIS_PAY,0)
95 );
96
97 end;
98
99 function CF_Net_YTDFormula return Number is
100 begin
101
102 RETURN ( nvl(CP_Taxable_YTD,0)
103 + nvl(CP_Non_Taxable_YTD,0)
104 - nvl(CP_Voluntary_YTD,0)
105 - nvl(CP_Statutory_YTD,0)
106 );
107
108 end;
109
110 function cf_balancesformula(balance_org_name in varchar2, balances_this_Pay in number, balances_YTD in number) return number is
111 begin
112 IF balance_org_name = 'Taxable Earnings'
113 THEN
114 CP_Taxable_This_Pay := balances_this_Pay ;
115 CP_Taxable_YTD := balances_YTD ;
116
117 CP_Non_Taxable_This_Pay:=0;
118 CP_Non_Taxable_YTD:=0;
119 CP_Voluntary_This_Pay :=0;
120 CP_Voluntary_YTD:=0;
121 CP_Statutory_This_Pay :=0;
122 CP_Statutory_YTD:=0;
123 ELSIF balance_org_name = 'Non Taxable Earnings'
124 THEN
125 CP_Non_Taxable_This_Pay := balances_this_pay ;
126 CP_Non_Taxable_YTD := balances_YTD ;
127
128 CP_Taxable_This_Pay:=0;
129 CP_Taxable_YTD:=0;
130 CP_Voluntary_This_Pay :=0;
131 CP_Voluntary_YTD:=0;
132 CP_Statutory_This_Pay :=0;
133 CP_Statutory_YTD:=0;
134 ELSIF balance_org_name = 'Voluntary Deductions'
135 THEN
136 CP_Voluntary_This_Pay := balances_this_pay ;
137 CP_Voluntary_YTD := balances_YTD ;
138
139 CP_Taxable_This_Pay:=0;
140 CP_Taxable_YTD:=0;
141 CP_Non_Taxable_This_Pay :=0;
142 CP_Non_Taxable_YTD:=0;
143 CP_Statutory_This_Pay :=0;
144 CP_Statutory_YTD:=0;
145 ELSIF balance_org_name = 'Statutory Deductions'
146 THEN
147 CP_Statutory_This_Pay := balances_this_pay ;
148 CP_Statutory_YTD := balances_YTD ;
149
150 CP_Taxable_This_Pay:=0;
151 CP_Taxable_YTD:=0;
152 CP_Non_Taxable_This_Pay :=0;
153 CP_Non_Taxable_YTD:=0;
154 CP_Voluntary_This_Pay :=0;
155 CP_Voluntary_YTD:=0;
156 else
157
158 CP_Statutory_This_Pay := 0 ;
159 CP_Statutory_YTD := 0 ;
160
161 CP_Taxable_This_Pay:=0;
162 CP_Taxable_YTD:=0;
163 CP_Non_Taxable_This_Pay :=0;
164 CP_Non_Taxable_YTD:=0;
165 CP_Voluntary_This_Pay :=0;
166 CP_Voluntary_YTD:=0;
167 END IF;
168 RETURN (0);
169 end;
170
171 PROCEDURE construct_order_by IS
172 BEGIN
173
174 IF P_SORT_ORDER_1 IS NOT NULL THEN
175 CP_ORDER_BY := 'ORDER BY ' || P_SORT_ORDER_1 ;
176 IF P_SORT_ORDER_2 IS NOT NULL THEN
177 CP_ORDER_BY := CP_ORDER_BY ||', ' || P_SORT_ORDER_2 ;
178 IF P_SORT_ORDER_3 IS NOT NULL THEN
179 CP_ORDER_BY := CP_ORDER_BY || ', ' || P_SORT_ORDER_3 ;
180 IF P_SORT_ORDER_4 IS NOT NULL THEN
181 CP_ORDER_BY := CP_ORDER_BY || ', ' || P_SORT_ORDER_4 ;
182 END IF ; END IF ; END IF ; ELSE
183 CP_ORDER_BY := 'ORDER BY EMPLOYEE_NUMBER' ;
184 END IF ;
185 END;
186
187 PROCEDURE get_parameters_name IS
188
189 CURSOR csr_payroll_name IS
190 SELECT ppf.payroll_name
191 FROM pay_payrolls_f ppf
192 WHERE ppf.payroll_id = P_PAYROLL_ID
193 AND ppf.business_group_id = P_BUSINESS_GROUP_ID
194 -- AND P_START_DATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
195 AND P_START_DATE_T BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
196
197 CURSOR csr_consolidation_name IS
198 SELECT pcs.consolidation_set_name
199 FROM pay_consolidation_sets pcs
200 WHERE pcs.consolidation_set_id = P_CONSOLIDATION_SET_ID
201 AND pcs.business_group_id = P_BUSINESS_GROUP_ID ;
202
203 CURSOR csr_business_group IS
204 SELECT pbg.name
205 FROM per_business_groups pbg
206 WHERE pbg.organization_id = P_BUSINESS_GROUP_ID ;
207
208
209 BEGIN
210
211 OPEN csr_payroll_name;
212 FETCH csr_payroll_name INTO cp_payroll_name;
213 CLOSE csr_payroll_name;
214
215 OPEN csr_consolidation_name;
216 FETCH csr_consolidation_name INTO cp_consolidation_set_name;
217 CLOSE csr_consolidation_name;
218
219 OPEN csr_business_group;
220 FETCH csr_business_group INTO cp_business_group_name;
221 CLOSE csr_business_group;
222
223 cp_payroll_location := hr_general.decode_lookup('CN_PAYOUT_LOCATION', P_PAYOUT_LOCATION);
224
225 cp_start_date := P_START_DATE_t ;
226 cp_end_date := P_END_DATE_t ;
227
228 cp_sort_order_1 := hr_general.decode_lookup('CN_SOE_SORT_BY', P_SORT_ORDER_1);
229 cp_sort_order_2 := hr_general.decode_lookup('CN_SOE_SORT_BY', P_SORT_ORDER_2);
230 cp_sort_order_3 := hr_general.decode_lookup('CN_SOE_SORT_BY', P_SORT_ORDER_3);
231 cp_sort_order_4 := hr_general.decode_lookup('CN_SOE_SORT_BY', P_SORT_ORDER_4);
232
233
234
235
236
237 END;
238
239 --Functions to refer Oracle report placeholders--
240
241 Function CP_Taxable_This_Pay_p return number is
242 Begin
243 return CP_Taxable_This_Pay;
244 END;
245 Function CP_Taxable_YTD_p return number is
246 Begin
247 return CP_Taxable_YTD;
248 END;
249 Function CP_Non_Taxable_This_Pay_p return number is
250 Begin
251 return CP_Non_Taxable_This_Pay;
252 END;
253 Function CP_Non_Taxable_YTD_p return number is
254 Begin
255 return CP_Non_Taxable_YTD;
256 END;
257 Function CP_Voluntary_This_Pay_p return number is
258 Begin
259 return CP_Voluntary_This_Pay;
260 END;
261 Function CP_Voluntary_YTD_p return number is
262 Begin
263 return CP_Voluntary_YTD;
264 END;
265 Function CP_Statutory_This_Pay_p return number is
266 Begin
267 return CP_Statutory_This_Pay;
268 END;
269 Function CP_Statutory_YTD_p return number is
270 Begin
271 return CP_Statutory_YTD;
272 END;
273 Function CP_ORDER_BY_p return varchar2 is
274 Begin
275 return CP_ORDER_BY;
276 END;
277 Function CP_Payroll_name_p return varchar2 is
278 Begin
279 return CP_Payroll_name;
280 END;
281 Function CP_Consolidation_Set_name_p return varchar2 is
282 Begin
283 return CP_Consolidation_Set_name;
284 END;
285 Function CP_Payroll_Location_p return varchar2 is
286 Begin
287 return CP_Payroll_Location;
288 END;
289 Function CP_Business_Group_name_p return varchar2 is
290 Begin
291 return CP_Business_Group_name;
292 END;
293 Function CP_Start_Date_p return date is
294 Begin
295 return CP_Start_Date;
296 END;
297 Function CP_End_date_p return date is
298 Begin
299 return CP_End_date;
300 END;
301 Function CP_Sort_Order_1_p return varchar2 is
302 Begin
303 return CP_Sort_Order_1;
304 END;
305 Function CP_Sort_Order_2_p return varchar2 is
306 Begin
307 return CP_Sort_Order_2;
308 END;
309 Function CP_Sort_Order_3_p return varchar2 is
310 Begin
311 return CP_Sort_Order_3;
312 END;
313 Function CP_Sort_Order_4_p return varchar2 is
314 Begin
315 return CP_Sort_Order_4;
316 END;
317 END PAY_PAYCNSOE_XMLP_PKG ;
318