[Home] [Help]
PACKAGE BODY: APPS.PAY_PAYHKMPF_XMLP_PKG
Source
1 PACKAGE BODY PAY_PAYHKMPF_XMLP_PKG AS
2 /* $Header: PAYHKMPFB.pls 120.0 2007/12/13 12:17:12 amakrish noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 function AfterReport return boolean is
7 begin
8 /*srw.user_exit('FND SRWEXIT');*/null;
9
10 return (TRUE);
11 end;
12
13 function BeforeReport return boolean is
14 BEGIN
15
16 p_global_sequence_no := 0;
17
18 /*srw.user_exit('FND SRWINIT');*/null;
19
20
21
22 if p_surcharge_parti_mand is null then
23 p_surcharge_parti_mand_t := 0;
24 else
25 p_surcharge_parti_mand_t := p_surcharge_parti_mand;
26 end if;
27
28 if p_surcharge_parti_vol is null then
29 p_surcharge_parti_vol_t := 0;
30 else
31 p_surcharge_parti_vol_t := p_surcharge_parti_vol;
32 end if;
33
34 if p_surcharge_partii_mand is null then
35 p_surcharge_partii_mand_t := 0;
36 else
37 p_surcharge_partii_mand_t := p_surcharge_partii_mand;
38 end if;
39
40 if p_surcharge_partii_vol is null then
41 p_surcharge_partii_vol_t := 0;
42 else
43 p_surcharge_partii_vol_t := p_surcharge_partii_vol;
44 end if;
45
46 P_CONTRIBUTIONS_START_DATE_T := to_char(p_contributions_start_date,'dd/mm/yyyy');
47 P_CONTRIBUTIONS_END_DATE_T := to_char(p_contributions_end_date,'dd/mm/yyyy');
48 effective_date := to_char(p_contributions_end_date,'dd-mon-yyyy');
49 construct_where_clause;
50 construct_scheme_employer;
51
52 return (TRUE);
53
54 EXCEPTION
55 when USER_EXIT_FAILURE /*srw.user_exit_failure */then
56 begin
57 /*srw.message(100, 'Foundation is not initialised');*/null;
58
59 raise_application_error(-20101,null);/*srw.program_abort;*/null;
60
61 end;
62 END;
63
64 PROCEDURE construct_where_clause IS
65 BEGIN
66
67
68 --cp_where_current := null;
69 cp_where_current := ' ';
70
71
72
73 if p_scheme_id is not null then
74 cp_where_current := cp_where_current || ' and scheme_id = ' || to_char(p_scheme_id);
75 end if;
76
77 if p_legal_employer_id is not null then
78 cp_where_current := cp_where_current || ' and tax_unit_id = ' || to_char(p_legal_employer_id);
79 end if;
80
81
82
83 if p_contributions_start_date is not null and
84 p_contributions_end_date is not null then
85 cp_where_current := cp_where_current || ' and effective_date between ' || '''' ||
86 to_char(p_contributions_start_date,'dd-mon-yyyy') || '''' || ' and ' || '''' ||
87 to_char(p_contributions_end_date,'dd-mon-yyyy') || '''' ;
88 end if;
89
90 END;
91
92 PROCEDURE construct_scheme_employer IS
93 BEGIN
94
95
96 BEGIN
97 select substr(hoi.org_information2, 1, 52) scheme_name,
98 substr(hoi.org_information1, 1, 17) scheme_registration_no,
99 substr(hoi.org_information5, 1, 32) contact_person,
100 substr(hoi.org_information6, 1, 17) employer_participation_no
101 into cp_scheme_name,
102 cp_scheme_reg_number,
103 cp_contact,
104 cp_participation_no
105 from hr_organization_information hoi
106 where hoi.org_information20 = p_scheme_id
107 and hoi.org_information_context = 'HK_MPF_SCHEMES';
108
109 EXCEPTION
110 when no_data_found then
111 cp_scheme_name := null;
112 cp_scheme_reg_number := null;
113 cp_contact := null;
114 cp_participation_no := null;
115 END;
116
117
118 BEGIN
119 select substr(hou.name, 1, 32) employer_name,
120 hl.address_line_1 ||
121 decode(hl.address_line_2, null, null, ', ' || hl.address_line_2) ||
122 decode(hl.address_line_3, null, null, ', ' || hl.address_line_3) ||
123 decode(hl.town_or_city, null, null, ', ' || hl.town_or_city) address,
124 substr(hl.telephone_number_1, 1, 32) telephone_number
125 into cp_employer_name,
126 cp_address,
127 cp_telephone_no
128 from hr_organization_units hou,
129 hr_locations hl
130 where hou.organization_id = p_legal_employer_id
131 and hou.location_id = hl.location_id;
132
133 cp_address := substr(cp_address, 1, 70);
134 EXCEPTION
135 when no_data_found then
136 cp_employer_name := null;
137 cp_address := null;
138 cp_telephone_no := null;
139 END;
140
141 END;
142
143 function cf_total_mandatoryformula(er_mandatory in number, ee_mandatory in number) return number is
144 begin
145 return er_mandatory + ee_mandatory;
146 end;
147
148 function cf_total_voluntaryformula(er_voluntary in number, ee_voluntary in number) return number is
149 begin
150 return er_voluntary + ee_voluntary;
151 end;
152
153 function cf_part1_mandatoryformula(CS_er_mandatory in number, CS_ee_mandatory in number) return number is
154 begin
155 --return CS_er_mandatory + CS_ee_mandatory + p_surcharge_parti_mand;
156 return CS_er_mandatory + CS_ee_mandatory + p_surcharge_parti_mand_t;
157 end;
158
159 function cf_part1_voluntaryformula(CS_er_voluntary in number, CS_ee_voluntary in number) return number is
160 begin
161 --return CS_er_voluntary + CS_ee_voluntary + p_surcharge_parti_vol;
162 return CS_er_voluntary + CS_ee_voluntary + p_surcharge_parti_vol_t;
163 end;
164
165 function CF_sequence_noFormula return Number is
166 begin
167
168
169
170 if cp_display_sequence_1 is null then
171 cp_display_sequence_1 := 0;
172 end if;
173
174 cp_display_sequence_1 := cp_display_sequence_1 + 1;
175 p_global_sequence_no := p_global_sequence_no + 1;
176
177 return 0;
178
179 end;
180
181 function CF_CURRENCY_FORMAT_MASKFormula return Number is
182
183 v_currency_code fnd_currencies.currency_code%type;
184 v_format_mask varchar2(100) := null;
185 v_field_length number(3);
186
187 begin
188
189
190 v_currency_code := pay_hk_soe_pkg.business_currency_code(p_business_group_id);
191
192
193 v_field_length := 12;
194 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
195 cp_currency_format_mask_12 := v_format_mask;
196
197 v_field_length := 14;
198 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
199 cp_currency_format_mask_14 := v_format_mask;
200
201 return 0;
202
203 end;
204
205 function cf_total_mandatory1formula(er_mandatory1 in number, ee_mandatory1 in number) return number is
206 begin
207 return er_mandatory1 + ee_mandatory1;
208 end;
209
210 function cf_total_voluntary1formula(er_voluntary1 in number, ee_voluntary1 in number) return number is
211 begin
212 return er_voluntary1 + ee_voluntary1;
213 end;
214
215 function cf_partii_mandatoryformula(CS_er_mandatory1 in number, CS_ee_mandatory1 in number) return number is
216 begin
217 --return CS_er_mandatory1 + CS_ee_mandatory1 + p_surcharge_partii_mand;
218 return CS_er_mandatory1 + CS_ee_mandatory1 + p_surcharge_partii_mand_t;
219 end;
220
221 function cf_partii_voluntaryformula(CS_er_voluntary1 in number, CS_ee_voluntary1 in number) return number is
222 begin
223 --return CS_er_voluntary1 + CS_ee_voluntary1 + p_surcharge_partii_vol;
224 return CS_er_voluntary1 + CS_ee_voluntary1 + p_surcharge_partii_vol_t;
225 end;
226
227 function cf_overall_mandatoryformula(CF_parti_mandatory in number, CF_partii_mandatory in number) return number is
228 begin
229 return CF_parti_mandatory + CF_partii_mandatory;
230 end;
231
232 function cf_overall_voluntaryformula(CF_parti_voluntary in number, CF_partii_voluntary in number) return number is
233 begin
234 return CF_parti_voluntary + CF_partii_voluntary;
235 end;
236
237 function CF_sequence_no1Formula return Number is
238 begin
239
240
241
242 if cp_display_sequence_2 is null then
243 cp_display_sequence_2 := p_global_sequence_no;
244 end if;
245
246 cp_display_sequence_2 := cp_display_sequence_2 + 1;
247 p_global_sequence_no := p_global_sequence_no + 1;
248
249 return 0;
250
251 end;
252
253 --Functions to refer Oracle report placeholders--
254
255 Function CP_display_sequence_1_p return number is
256 Begin
257 return CP_display_sequence_1;
258 END;
259 Function CP_display_sequence_2_p return number is
260 Begin
261 return CP_display_sequence_2;
262 END;
263 Function CP_where_current_p return varchar2 is
264 Begin
265 return CP_where_current;
266 END;
267 Function CP_scheme_name_p return varchar2 is
268 Begin
269 return CP_scheme_name;
270 END;
271 Function CP_scheme_reg_number_p return varchar2 is
272 Begin
273 return CP_scheme_reg_number;
274 END;
275 Function CP_employer_name_p return varchar2 is
276 Begin
277 return CP_employer_name;
278 END;
279 Function CP_contact_p return varchar2 is
280 Begin
281 return CP_contact;
282 END;
283 Function CP_address_p return varchar2 is
284 Begin
285 return CP_address;
286 END;
287 Function CP_telephone_no_p return varchar2 is
288 Begin
289 return CP_telephone_no;
290 END;
291 Function CP_participation_no_p return varchar2 is
292 Begin
293 return CP_participation_no;
294 END;
295 Function CP_CURRENCY_FORMAT_MASK_12_p return varchar2 is
296 Begin
297 return CP_CURRENCY_FORMAT_MASK_12;
298 END;
299 Function CP_CURRENCY_FORMAT_MASK_14_p return varchar2 is
300 Begin
301 return CP_CURRENCY_FORMAT_MASK_14;
302 END;
303 END PAY_PAYHKMPF_XMLP_PKG ;