[Home] [Help]
PACKAGE BODY: APPS.PAY_PYSG8SAD_XMLP_PKG
Source
1 PACKAGE BODY PAY_PYSG8SAD_XMLP_PKG AS
2 /* $Header: PYSG8SADB.pls 120.0 2007/12/13 12:23:46 amakrish noship $ */
3
4 function AfterReport return boolean is
5 t number;
6
7 begin
8
9 t := DELETE_ARCHIVE_DATA(p_payroll_action_id);
10 /*srw.user_exit('FND SRWEXIT');*/null;
11
12 return (TRUE);
13
14 end;
15
16 function CF_business_groupFormula return VARCHAR2 is
17 v_business_group hr_all_organization_units.name%type;
18
19 begin
20 v_business_group := hr_reports.get_business_group(p_business_group_id);
21 return v_business_group;
22 end;
23
24 function BeforeReport return boolean is
25 begin
26
27
28 return (TRUE);
29 end;
30
31 function CF_legislation_codeFormula return VARCHAR2 is
32 v_cpf_cap varchar2(10);
33 v_sys_date varchar2(11);
34 v_legislation_code hr_organization_information.org_information9%type := null;
35
36 cursor cpf_cap_method
37 (c_legal_entity_id hr_organization_information.organization_id%type) is
38 select org_information7
39 from hr_organization_information
40 where org_information_context = 'SG_LEGAL_ENTITY'
41 and organization_id = c_legal_entity_id;
42
43 cursor legislation_code
44 (c_business_group_id hr_organization_information.organization_id%type) is
45 select org_information9
46 from hr_organization_information
47 where organization_id = c_business_group_id
48 and org_information9 is not null;
49
50
51 begin
52
53 open legislation_code (p_business_group_id);
54 fetch legislation_code into v_legislation_code;
55 close legislation_code;
56
57 open cpf_cap_method(p_legal_entity);
58 fetch cpf_cap_method into v_cpf_cap;
59 close cpf_cap_method;
60
61 If v_cpf_cap = 'NOSA' then
62 CP_CPF_CAP_NO := 'X';
63 Else
64 CP_CPF_CAP_YES := 'X';
65 End If;
66 v_sys_date := to_char(sysdate,'DD-MON-YYYY');
67 CP_SYS_DATE := v_sys_date;
68 CP_BASIS_END := to_char(P_BASIS_END,'DD-MON-YYYY');
69
70 return v_legislation_code;
71 End;
72
73 function cf_currency_format_maskformula(cf_legislation_code in varchar2) return varchar2 is
74
75 v_currency_code fnd_currencies.currency_code%type;
76 v_format_mask varchar2(100) := null;
77 v_field_length number(3) := 15;
78
79 cursor currency_format_mask
80 (c_territory_code in fnd_currencies.issuing_territory_code%type) is
81 select currency_code
82 from fnd_currencies
83 where issuing_territory_code = c_territory_code;
84
85 begin
86 open currency_format_mask (cf_legislation_code);
87 fetch currency_format_mask into v_currency_code;
88 close currency_format_mask;
89
90 v_format_mask := fnd_currency.get_format_mask(v_currency_code, v_field_length);
91
92 return v_format_mask;
93 end;
94
95 function cf_monthly_detailsformula(assignment_action_id in number, date_earned in varchar2) return number is
96 begin
97
98 IF assignment_action_id IS NOT NULL THEN
99 BEGIN
100
101 PAY_BALANCE_PKG.SET_CONTEXT
102 ( P_CONTEXT_NAME => 'ASSIGNMENT_ACTION_ID'
103 ,P_CONTEXT_VALUE => assignment_action_id );
104 PAY_BALANCE_PKG.SET_CONTEXT
105 ( P_CONTEXT_NAME => 'TAX_UNIT_ID'
106 ,P_CONTEXT_VALUE => P_LEGAL_ENTITY );
107 PAY_BALANCE_PKG.SET_CONTEXT
108 ( P_CONTEXT_NAME => 'DATE_EARNED'
109 ,P_CONTEXT_VALUE => date_earned );
110
111 CP_OW := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
112 ( P_DATABASE_NAME => 'X_ORDINARY_EARNINGS_PER_LE_MONTH'
113 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
114 ,P_LEGISLATION_CODE => 'SG' ));
115
116 CP_OW_CPF_ER := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
117 ( P_DATABASE_NAME => 'X_EMPLOYER_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH'
118 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
119 ,P_LEGISLATION_CODE => 'SG' ));
120 CP_OW_CPF_EE := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
121 ( P_DATABASE_NAME => 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ORDINARY_EARNINGS_PER_LE_MONTH'
122 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
123 ,P_LEGISLATION_CODE => 'SG' ));
124 CP_OW_APR_FUND := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
125 ( P_DATABASE_NAME => 'X_IR8S_MOA_403_PER_LE_MONTH'
126 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
127 ,P_LEGISLATION_CODE => 'SG' ));
128 CP_AW := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
129 ( P_DATABASE_NAME => 'X_ADDITIONAL_EARNINGS_PER_LE_MONTH'
130 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
131 ,P_LEGISLATION_CODE => 'SG' ));
132
133 CP_AW_CPF_ER := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
134 ( P_DATABASE_NAME => 'X_EMPLOYER_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH'
135 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
136 ,P_LEGISLATION_CODE => 'SG' ));
137
138 CP_AW_CPF_EE := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
139 ( P_DATABASE_NAME => 'X_EMPLOYEE_CPF_CONTRIBUTIONS_ADDITIONAL_EARNINGS_PER_LE_MONTH'
140 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
141 ,P_LEGISLATION_CODE => 'SG' ));
142
143 CP_AW_APR_FUND := to_number(PAY_BALANCE_PKG.RUN_DB_ITEM
144 ( P_DATABASE_NAME => 'X_IR8S_MOA_407_PER_LE_MONTH'
145 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
146 ,P_LEGISLATION_CODE => 'SG' ));
147
148 END;
149 END IF;
150 return 0;
151 EXCEPTION
152 WHEN NO_DATA_FOUND THEN
153 RAISE;
154 WHEN OTHERS THEN
155 RAISE;
156
157 end;
158
159 function cf_refund_detailsformula(ASSIGNMENT_ACTION_ID2 in number, ASS_EXTRA_ID in varchar2) return number is
160 l_aw_fr_date varchar2(28);
161 l_aw_to_date varchar2(28);
162 l_refund_date varchar2(28);
163 l_er_date varchar2(28);
164 l_ee_date varchar2(28);
165
166 BEGIN
167 BEGIN
168
169 PAY_BALANCE_PKG.SET_CONTEXT
170 ( P_CONTEXT_NAME => 'ASSIGNMENT_ACTION_ID'
171 ,P_CONTEXT_VALUE => ASSIGNMENT_ACTION_ID2 );
172 PAY_BALANCE_PKG.SET_CONTEXT
173 ( P_CONTEXT_NAME => 'TAX_UNIT_ID'
174 ,P_CONTEXT_VALUE => P_LEGAL_ENTITY );
175 PAY_BALANCE_PKG.SET_CONTEXT
176 ( P_CONTEXT_NAME => 'SOURCE_ID'
177 ,P_CONTEXT_VALUE => ASS_EXTRA_ID );
178
179 CP_AW_AMT := PAY_BALANCE_PKG.RUN_DB_ITEM
180 ( P_DATABASE_NAME => 'X_MOA410'
181 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
182 ,P_LEGISLATION_CODE => 'SG' );
183
184 l_aw_fr_date := PAY_BALANCE_PKG.RUN_DB_ITEM
185 ( P_DATABASE_NAME => 'X_DTM502'
186 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
187 ,P_LEGISLATION_CODE => 'SG' );
188 CP_AW_FR_DATE := to_char(fnd_date.canonical_to_date(l_aw_fr_date),'DD-MON-YYYY');
189
190 l_aw_to_date := PAY_BALANCE_PKG.RUN_DB_ITEM
191 ( P_DATABASE_NAME => 'X_DTM503'
192 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
193 ,P_LEGISLATION_CODE => 'SG' );
194 CP_AW_TO_DATE := to_char(fnd_date.canonical_to_date(l_aw_fr_date),'DD-MON-YYYY');
195
196 l_refund_date := PAY_BALANCE_PKG.RUN_DB_ITEM
197 ( P_DATABASE_NAME => 'X_DTM504'
198 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
199 ,P_LEGISLATION_CODE => 'SG' );
200 CP_REFUND_DATE := to_char(fnd_date.canonical_to_date(l_refund_date),'DD-MON-YYYY');
201
202 CP_ER_CONTRIB := PAY_BALANCE_PKG.RUN_DB_ITEM
203 ( P_DATABASE_NAME => 'X_MOA411'
204 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
205 ,P_LEGISLATION_CODE => 'SG' );
206
207 CP_ER_INTR := PAY_BALANCE_PKG.RUN_DB_ITEM
208 ( P_DATABASE_NAME => 'X_MOA412'
209 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
210 ,P_LEGISLATION_CODE => 'SG' );
211
212 l_er_date := PAY_BALANCE_PKG.RUN_DB_ITEM
213 ( P_DATABASE_NAME => 'X_DTM505'
214 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
215 ,P_LEGISLATION_CODE => 'SG' );
216 CP_ER_DATE := to_char(fnd_date.canonical_to_date(l_er_date),'DD-MON-YYYY');
217
218 CP_EE_CONTRIB := PAY_BALANCE_PKG.RUN_DB_ITEM
219 ( P_DATABASE_NAME => 'X_MOA413'
220 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
221 ,P_LEGISLATION_CODE => 'SG' );
222
223 CP_EE_INTR := PAY_BALANCE_PKG.RUN_DB_ITEM
224 ( P_DATABASE_NAME => 'X_MOA414'
225 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
226 ,P_LEGISLATION_CODE => 'SG' );
227
228 l_ee_date := PAY_BALANCE_PKG.RUN_DB_ITEM
229 ( P_DATABASE_NAME => 'X_DTM506'
230 ,P_BUS_GROUP_ID => P_BUSINESS_GROUP_ID
231 ,P_LEGISLATION_CODE => 'SG' );
232 CP_EE_DATE := to_char(fnd_date.canonical_to_date(l_ee_date),'DD-MON-YYYY');
233
234
235 END;
236 return 0;
237
238 END;
239
240 --function delete_archive_data(t_payroll_action_id in number)(t_payroll_action_id in number) return number is
241 function delete_archive_data(t_payroll_action_id in number) return number is
242
243 cursor c_get_asact_id
244 (c_pact_id in pay_payroll_actions.payroll_action_id%type) is
245 select distinct assignment_action_id
246 from pay_assignment_actions
247 where payroll_action_id = t_payroll_action_id;
248
249 cursor c_get_arch_item_id
250 (c_asact_id pay_assignment_actions.assignment_action_id%type) is
251 select distinct archive_item_id
252 from ff_archive_items
253 where context1= c_asact_id;
254
255
256 TYPE t_asact_id_tab IS TABLE OF pay_assignment_actions.assignment_action_id%type;
257 asact_id_list t_asact_id_tab;
258
259 TYPE t_arch_item_id_tab IS TABLE OF ff_archive_items.archive_item_id%type;
260 arc_item_id_list t_arch_item_id_tab;
261
262 v_archive_item_id ff_archive_items.archive_item_id%type;
263
264
265 BEGIN
266 for process_rec in c_get_asact_id(t_payroll_action_id)
267 loop
268 open c_get_arch_item_id(process_rec.assignment_action_id);
269 loop
270 fetch c_get_arch_item_id into v_archive_item_id;
271 exit when c_get_arch_item_id%NOTFOUND;
272 delete from ff_archive_item_contexts where archive_item_id = v_archive_item_id;
273 delete from ff_archive_items where archive_item_id = v_archive_item_id;
274 end loop;
275 close c_get_arch_item_id;
276 end loop;
277 pay_archive.remove_report_actions(t_payroll_action_id);
278 return(0);
279
280 END;
281
282 --Functions to refer Oracle report placeholders--
283
284 Function CP_OW_p return number is
285 Begin
286 return CP_OW;
287 END;
288 Function CP_OW_CPF_ER_p return number is
289 Begin
290 return CP_OW_CPF_ER;
291 END;
292 Function CP_OW_CPF_EE_p return number is
293 Begin
294 return CP_OW_CPF_EE;
295 END;
296 Function CP_OW_APR_FUND_p return number is
297 Begin
298 return CP_OW_APR_FUND;
299 END;
300 Function CP_AW_p return number is
301 Begin
302 return CP_AW;
303 END;
304 Function CP_AW_CPF_ER_p return number is
305 Begin
306 return CP_AW_CPF_ER;
307 END;
308 Function CP_AW_CPF_EE_p return number is
309 Begin
310 return CP_AW_CPF_EE;
311 END;
312 Function CP_AW_APR_FUND_p return number is
313 Begin
314 return CP_AW_APR_FUND;
315 END;
316 Function CP_AW_AMT_p return number is
317 Begin
318 return CP_AW_AMT;
319 END;
320 Function CP_AW_FR_DATE_p return varchar2 is
321 Begin
322 return CP_AW_FR_DATE;
323 END;
324 Function CP_AW_TO_DATE_p return varchar2 is
325 Begin
326 return CP_AW_TO_DATE;
327 END;
328 Function CP_REFUND_DATE_p return varchar2 is
329 Begin
330 return CP_REFUND_DATE;
331 END;
332 Function CP_ER_CONTRIB_p return number is
333 Begin
334 return CP_ER_CONTRIB;
335 END;
336 Function CP_ER_INTR_p return number is
337 Begin
338 return CP_ER_INTR;
339 END;
340 Function CP_ER_DATE_p return varchar2 is
341 Begin
342 return CP_ER_DATE;
343 END;
344 Function CP_EE_CONTRIB_p return number is
345 Begin
346 return CP_EE_CONTRIB;
347 END;
348 Function CP_EE_INTR_p return number is
349 Begin
350 return CP_EE_INTR;
351 END;
352 Function CP_EE_DATE_p return varchar2 is
353 Begin
354 return CP_EE_DATE;
355 END;
356 Function CP_CPF_CAP_YES_p return varchar2 is
357 Begin
358 return CP_CPF_CAP_YES;
359 END;
360 Function CP_ASG_SET_NAME_p return varchar2 is
361 Begin
362 return CP_ASG_SET_NAME;
363 END;
364 Function CP_EMP_NO_p return varchar2 is
365 Begin
366 return CP_EMP_NO;
367 END;
368 Function CP_CPF_CAP_NO_p return varchar2 is
369 Begin
370 return CP_CPF_CAP_NO;
371 END;
372 Function CP_SYS_DATE_p return varchar2 is
373 Begin
374 return CP_SYS_DATE;
375 END;
376 Function CP_BASIS_END_p return varchar2 is
377 Begin
378 return CP_BASIS_END;
379 END;
380 END PAY_PYSG8SAD_XMLP_PKG ;