[Home] [Help]
PACKAGE BODY: APPS.PAY_IE_SOE
Source
4 l_sql long;
1 PACKAGE BODY PAY_IE_SOE AS
2 /* $Header: pyiesoer.pkb 120.3 2012/01/19 09:45:45 rpahune ship $ */
3 g_package CONSTANT varchar2(33) := ' PAY_IE_SOE.';
5 g_debug CONSTANT boolean := hr_utility.debug_enabled;
6 function setParameters(p_person_id in number, p_assignment_id in number, p_effective_date date) return varchar2 is
7 p_payroll_exists varchar2(10);
8 a_assignment_id number;
9 p_assignment_action_id number;
10 p_run_assignment_action_id number;
11 p_paye_prsi_action_id number;
12 p_payroll_action_id number;
13 p_date_earned varchar2(20);
14 CURSOR c_assignment IS
15 SELECT asg.assignment_id
16 FROM per_all_assignments_f asg
17 WHERE asg.person_id=p_person_id
18 AND p_effective_date between asg.effective_start_date and asg.effective_end_date;
19
20 CURSOR c_action_id(a_asg_id number) IS
21 select to_number(substr(max(to_char(pa.effective_date,'J')||lpad(aa.assignment_action_id,15,'0')),8))
22 from pay_payroll_actions pa,
23 pay_assignment_actions aa
24 where pa.action_type in ('U','P','Q','R')
25 and aa.action_status IN ('C','S') --10225372
26 and pa.payroll_action_id = aa.payroll_action_id
27 and aa.assignment_id = a_asg_id
28 and pa.effective_date <= p_effective_date;
29
30
31
32 begin
33 --
34 if g_debug then
35 hr_utility.set_location('Entering pay_soe_glb.setParameters', 10);
36 end if;
37 --
38 -- NOTE:
39 -- This overridden version of setParameters is not yet fully implemented
40 -- at GLB level.
41 --
42 a_assignment_id := p_assignment_id;
43 if p_assignment_id is null then
47 end if;
44 open c_assignment;
45 fetch c_assignment into a_assignment_id;
46 close c_assignment;
48
49 open c_action_id(a_assignment_id);
50 fetch c_action_id into p_assignment_action_id;
51 close c_action_id;
52
53
54 if g_debug then
55 hr_utility.set_location('Leaving pay_soe_glb.setParameters', 20);
56 end if;
57 return setParameters(p_assignment_action_id);
58 end;
59 --
60 function setParameters(p_assignment_action_id number) return varchar2 is
61 begin
62 return PAY_SOE_GLB.setParameters(p_assignment_action_id) ; -- l_parameters;
63 end;
64 function Employee(p_assignment_action_id number) return long is
65 begin
66 l_sql:=
67 'Select org.name COL01
68 ,job.name COL02
69 ,loc.location_code COL03
70 ,grd.name COL04
71 ,pay.payroll_name COL05
72 ,pos.name COL06
73 ,peo.national_identifier COL07
74 ,pg.group_name COL08
75 ,asg.assignment_number COL09
76 ,peo.full_name COL10 --Removed Title for 4303921
77 from per_all_people_f peo
78 ,per_all_assignments_f asg
79 ,hr_all_organization_units_vl org
80 ,per_jobs_vl job
81 ,per_all_positions pos
82 ,hr_locations loc
83 ,per_grades_vl grd
84 ,pay_payrolls_f pay
85 ,pay_people_groups pg
86 where asg.assignment_id = :assignment_id
87 and :effective_date between asg.effective_start_date and asg.effective_end_date
88 and asg.person_id = peo.person_id
89 and :effective_date between peo.effective_start_date and peo.effective_end_date
90 and asg.position_id = pos.position_id(+)
91 and asg.job_id = job.job_id(+)
92 and asg.location_id = loc.location_id(+)
93 and asg.grade_id = grd.grade_id(+)
94 and asg.people_group_id = pg.people_group_id(+)
95 and asg.payroll_id = pay.payroll_id(+)
96 and :effective_date between pay.effective_start_date(+) and pay.effective_end_date(+)
97 and asg.organization_id = org.organization_id
98 and :effective_date between org.date_from and nvl(org.date_to, :effective_date)';
99 return l_sql;
100 end Employee;
101 function PAYE_Info(p_assignment_action_id NUMBER) return long is
102 begin
103 l_sql:=
104 'select PSPD.D_INFO_SOURCE COL01
105 ,fnd_date.canonical_to_date(PSPD.CERTIFICATE_ISSUE_DATE) COL02
106 ,PSPD.D_TAX_BASIS COL03
107 ,PSPD.D_TAX_ASSESS_BASIS COL04
108 ,PTM.PERIOD_NUM COL16
109 ,Decode(PTPR.BASIC_PERIOD_TYPE,''CM'',PSPD.MONTHLY_STD_RATE_CUT_OFF*PTPR.NUMBER_PER_FISCAL_YEAR/12,''W'',PSPD.WEEKLY_STD_RATE_CUT_OFF*PTPR.NUMBER_PER_FISCAL_YEAR/52) COL17
110 ,Decode(PTPR.BASIC_PERIOD_TYPE,''CM'',PSPD.MONTHLY_TAX_CREDIT*PTPR.NUMBER_PER_FISCAL_YEAR/12,''W'',PSPD.WEEKLY_TAX_CREDIT*PTPR.NUMBER_PER_FISCAL_YEAR/52) COL18
111 from PAY_IE_SOE_PAYE_DETAILS_V PSPD
112 ,PAY_PAYROLL_ACTIONS PPA
113 ,PAY_ASSIGNMENT_ACTIONS PAAS
114 ,PER_TIME_PERIODS PTM
115 ,PER_TIME_PERIOD_TYPES PTPT
116 ,PAY_ALL_PAYROLLS_F pap
117 ,per_time_period_rules PTPR
118 where PSPD.assignment_action_id :action_clause
119 and PSPD.assignment_action_id=PAAS.ASSIGNMENT_ACTION_ID
120 and PAAS.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
121 and PAP.PAYROLL_ID = PTM.PAYROLL_ID
122 and PPA.DATE_EARNED BETWEEN PTM.START_DATE AND PTM.END_DATE -- 4906850
123 and PPA.PAYROLL_ID=PAP.PAYROLL_ID
124 AND PAP.PERIOD_TYPE=PTPT.PERIOD_TYPE
125 AND PTPT.NUMBER_PER_FISCAL_YEAR=PTPR.NUMBER_PER_FISCAL_YEAR';
126 return l_sql;
127 end PAYE_Info;
128 function PRSI_Info(p_assignment_action_id NUMBER) return long is
129 begin
130 l_sql:=
131 'select PSPD.CONTRIBUTION_CLASS COL01
132 , OVERRIDDEN_SUBCLASS COL02
133 from PAY_IE_SOE_PRSI_DETAILS_V PSPD
134 where PSPD.assignment_action_id :action_clause';
135 return l_sql;
136 end PRSI_Info;
137 --
138 function Elements1(p_assignment_action_id number, P_ELEMENT_SET_NAME varchar2) return long is
139 begin
140 l_sql:=
141 'SELECT
142 NVL(PET.REPORTING_NAME, PET.ELEMENT_TYPE_ID) COL01
143 , NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME) COL02
144 ,to_char(decode(NVL(PET.REPORTING_NAME,PET.ELEMENT_NAME),''BIK Arrearage'',
145 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE*(-1))) ,
146 SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PRV.RESULT_VALUE))),fnd_currency.get_format_mask(:G_CURRENCY_CODE,40)) COL16
147 , decode(count(*),1,''1'',''2'') COL17 -- destination indicator
148 , decode(count(*),1,max(prr.run_result_id),max(pet.element_type_id)) COL18
149 FROM PAY_ELEMENT_TYPES_F PET
150 ,PAY_ELEMENT_TYPES_F_TL PETTL
151 ,PAY_ELEMENT_CLASSIFICATIONS PEC
155 ,PAY_RUN_RESULTS PRR
152 ,PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
153 ,PAY_INPUT_VALUES_F PIV
154 ,PAY_RUN_RESULT_VALUES PRV
156 ,PAY_ASSIGNMENT_ACTIONS PAA
157 ,PAY_ELEMENT_SET_MEMBERS PESM
158 ,PAY_ELEMENT_SETS PES
159 WHERE PAA.ASSIGNMENT_ACTION_ID :action_clause
160 AND PEC.LEGISLATION_CODE = ''IE''
161 AND PEC.BUSINESS_GROUP_ID IS NULL
162 AND PEC.Classification_id = PECTL.classification_id
163 AND PECTL.LANGUAGE = userenv(''LANG'')
164 AND PET.element_type_id = PETTL.element_type_id
165 AND PETTL.LANGUAGE = userenv(''LANG'')
166 AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
167 AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
168 AND PRR.STATUS IN (''P'',''PA'')
169 AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
170 AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
171 AND PRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
172 AND PRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
173 AND ((PIV.NAME =''Pay Value'' AND PIV.UOM = ''M'')
174 OR (PET.ELEMENT_NAME IN (''IE BIK Arrearage Details'',''IE BIK Arrearage Recovery Details'')
175 AND PET.LEGISLATION_CODE= ''IE'' AND PET.BUSINESS_GROUP_ID is NULL
176 AND PIV.NAME in (''BIK Arrearage'',''BIK Arrearage Recovered''))
177 OR (PET.ELEMENT_NAME IN (''IE PAYE at higher rate'',''IE PAYE at standard rate'')
178 AND PET.LEGISLATION_CODE= ''IE'' AND PET.BUSINESS_GROUP_ID is NULL AND PIV.NAME =''Value'' ))
179 AND PET.ELEMENT_NAME NOT IN (''IE PRSI'',''IE Net tax'')
180 AND EXISTS (SELECT 1 FROM DUAL WHERE
181 ((fnd_number.canonical_to_number(PRV.RESULT_VALUE) >0
182 AND PET.ELEMENT_NAME IN (''IE PRSI K Employee Lump Sum'',''IE PRSI M Employee Lump Sum'' ))
183 OR (PET.ELEMENT_NAME NOT IN (''IE PRSI K Employee Lump Sum'',''IE PRSI M Employee Lump Sum''))))
184 AND :effective_date BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
185 AND :effective_date BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
186 AND PET.ELEMENT_TYPE_ID = PESM.ELEMENT_TYPE_ID
187 AND PESM.ELEMENT_SET_ID = PES.ELEMENT_SET_ID
188 AND ( PES.BUSINESS_GROUP_ID IS NULL
189 OR PES.BUSINESS_GROUP_ID = :business_group_id )
190 AND ( PES.LEGISLATION_CODE IS NULL
191 OR PES.LEGISLATION_CODE = '':legislation_code'')
192 AND PES.ELEMENT_SET_NAME = '''|| P_ELEMENT_SET_NAME || '''
193 GROUP BY
194 PRR.ASSIGNMENT_ACTION_ID
195 , NVL(PET.REPORTING_NAME, PET.ELEMENT_NAME)
196 , NVL(PET.REPORTING_NAME, PET.ELEMENT_TYPE_ID)';
197 return l_sql;
198 end Elements1;
199 function Elements4(p_assignment_action_id number) return long is
200 begin
201 return Elements1(p_assignment_action_id,pay_soe_util.getConfig('ELEMENTS2'));
202 end Elements4;
203 --
204 function set_cutoff_prompt(p_assignment_action_id NUMBER) return Varchar2 is
205 l_cutoff_prompt varchar2(50);
206 cursor get_cutoff is
207 select Decode(PTPR.BASIC_PERIOD_TYPE,'CM','Monthly Cutoff','W','Weekly Cutoff')
208 from PAY_PAYROLL_ACTIONS PPA
209 ,PAY_ASSIGNMENT_ACTIONS PAAS
210 ,PER_TIME_PERIODS PTM
211 ,PER_TIME_PERIOD_TYPES PTPT
212 ,PAY_ALL_PAYROLLS_F pap
213 ,per_time_period_rules PTPR
214 where PAAS.ASSIGNMENT_ACTION_ID = p_assignment_action_id
215 and PAAS.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
216 and PAP.PAYROLL_ID = PTM.PAYROLL_ID
217 and PPA.DATE_EARNED BETWEEN PTM.START_DATE AND PTM.END_DATE -- 4906850
218 and PPA.PAYROLL_ID=PAP.PAYROLL_ID
219 AND PAP.PERIOD_TYPE=PTPT.PERIOD_TYPE
220 AND PTPT.NUMBER_PER_FISCAL_YEAR=PTPR.NUMBER_PER_FISCAL_YEAR;
221 begin
222 open get_cutoff;
223 fetch get_cutoff into l_cutoff_prompt;
224 close get_cutoff;
225 return l_cutoff_prompt;
226 end set_cutoff_prompt;
227 function set_credit_prompt(p_assignment_action_id NUMBER) return Varchar2 is
228 l_credit_prompt varchar2(50);
229 cursor get_credit is
230 select Decode(PTPR.BASIC_PERIOD_TYPE,'CM','Monthly Credit','W','Weekly Credit')
231 from PAY_PAYROLL_ACTIONS PPA
232 ,PAY_ASSIGNMENT_ACTIONS PAAS
233 ,PER_TIME_PERIODS PTM
234 ,PER_TIME_PERIOD_TYPES PTPT
235 ,PAY_ALL_PAYROLLS_F pap
236 ,per_time_period_rules PTPR
237 where Paas.assignment_action_id = p_assignment_action_id
238 and PAAS.PAYROLL_ACTION_ID=PPA.PAYROLL_ACTION_ID
239 and PAP.PAYROLL_ID = PTM.PAYROLL_ID
240 and PPA.DATE_EARNED BETWEEN PTM.START_DATE AND PTM.END_DATE -- 4906850
241 and PPA.PAYROLL_ID=PAP.PAYROLL_ID
242 AND PAP.PERIOD_TYPE=PTPT.PERIOD_TYPE
243 AND PTPT.NUMBER_PER_FISCAL_YEAR=PTPR.NUMBER_PER_FISCAL_YEAR;
244 begin
245 open get_credit;
246 fetch get_credit into l_credit_prompt;
247 close get_credit;
248 return l_credit_prompt;
249 end set_credit_prompt;
250
251 function Tax_PRSI_Info(p_assignment_action_id NUMBER) return long is
252
253 Cursor c_pay_run(a_asg_action_id number) is
254 select aa.assignment_action_id from pay_assignment_actions aa,pay_action_interlocks pai
255 where locking_action_id=a_asg_action_id
256 and aa.assignment_action_id=locked_action_id
257 and aa.source_action_id is not null
258 and aa.action_status = 'C';
259
260 Cursor c_PRSI(a_asg_action_id number) is
261 select PSPD.CONTRIBUTION_CLASS con
262 , OVERRIDDEN_SUBCLASS ovr
263 from PAY_IE_SOE_PRSI_DETAILS_V PSPD
264 where PSPD.assignment_action_id=a_asg_action_id;
265
269 IS
266 /* Added for bug 4287903 */
267 /* Added number_per_fiscal_year for 4354386*/
268 CURSOR c_period_num_and_type (a_asg_action_id NUMBER)
270 SELECT ptm.period_num, ptpr.basic_period_type, ptpr.number_per_fiscal_year
271 FROM pay_payroll_actions ppa,
272 pay_assignment_actions paas,
273 per_time_periods ptm,
274 per_time_period_types ptpt,
275 pay_all_payrolls_f pap,
276 per_time_period_rules ptpr
277 WHERE paas.assignment_action_id = a_asg_action_id
278 AND paas.payroll_action_id = ppa.payroll_action_id
279 AND pap.payroll_id = ptm.payroll_id
280 AND ppa.date_earned BETWEEN ptm.start_date AND ptm.end_date -- 4906850
281 AND ppa.payroll_id = pap.payroll_id
282 AND pap.period_type = ptpt.period_type
283 AND ptpt.number_per_fiscal_year = ptpr.number_per_fiscal_year;
284
285
286 v_contribution_class varchar2(60);
287 v_overridden_class varchar2(60);
288 v_period_num per_time_periods.period_num%TYPE;
289 v_basic_period_type per_time_period_rules.basic_period_type%TYPE;
290 v_number_per_fiscal_year per_time_period_rules.number_per_fiscal_year%TYPE;
291
292 a_assignment_action_id number;
293
294 begin
295 a_assignment_action_id:=p_assignment_action_id;
296 Open c_pay_run(a_assignment_action_id);
297 fetch c_pay_run into a_assignment_action_id;
298 close c_pay_run;
299
300 Open c_PRSI(a_assignment_action_id);
301 fetch c_PRSI into v_contribution_class,v_overridden_class;
302 close c_PRSI;
303
304 OPEN c_period_num_and_type (a_assignment_action_id);
305 FETCH c_period_num_and_type INTO v_period_num, v_basic_period_type, v_number_per_fiscal_year;
306 CLOSE c_period_num_and_type ;
307
308 /*Changed for Bug 4287903 */
309 l_sql:=
310 'select PSPD.D_INFO_SOURCE COL01
311 ,fnd_date.canonical_to_date(PSPD.CERTIFICATE_ISSUE_DATE) COL02
312 ,PSPD.D_TAX_BASIS COL03
313 ,PSPD.D_TAX_ASSESS_BASIS COL04
314 ,'|| v_period_num|| ' COL07
315 ,Decode('''|| v_basic_period_type|| ''',''CM'',PSPD.MONTHLY_STD_RATE_CUT_OFF*' || v_number_per_fiscal_year || '/12,''W'',PSPD.WEEKLY_STD_RATE_CUT_OFF*' || v_number_per_fiscal_year || '/52) COL05
316 ,Decode('''|| v_basic_period_type|| ''',''CM'',PSPD.MONTHLY_TAX_CREDIT*' || v_number_per_fiscal_year || '/12,''W'',PSPD.WEEKLY_TAX_CREDIT*' || v_number_per_fiscal_year || '/52) COL06
317 ,''' ||v_contribution_class || ''' COL08
318 ,''' ||v_overridden_class || ''' COL09
319 from PAY_IE_SOE_PAYE_DETAILS_V PSPD
320 where PSPD.assignment_action_id :action_clause';
321
322 return l_sql;
323 end Tax_PRSI_Info;
324
325 END PAY_IE_SOE;