[Home] [Help]
PACKAGE BODY: APPS.PAY_US_HR_HELPDESK
Source
1 PACKAGE BODY PAY_US_HR_HELPDESK AS
2 /* $Header: payushrhd.pkb 120.3 2010/05/21 13:16:58 pmatamsr noship $ */
3
4 procedure GET_USPAY_DETAILS (p_per_id number,
5 p_bg_id number,
6 p_eff_date date,
7 p_leg_code varchar2,
8 p_pyrl_dtls out nocopy HR_PERSON_RECORD.PAYROLL_RECORD,
9 p_error out nocopy varchar2)
10 is
11
12 -- Cursor declaration for US Payroll details starts here
13
14 -- Cursor to fetch basic details for Payroll Archive process
15
16 cursor csr_uspay_req (l_person_id number,l_eff_date date) is
17 select distinct
18 ppa.payroll_action_id
19 ,paa.assignment_id
20 ,paa.assignment_action_id
21 ,paf.location_id
22 from pay_payroll_actions ppa
23 ,pay_assignment_actions paa
24 ,per_assignments_f paf
25 ,per_people_f ppf
26 ,hr_lookups hl
27 where ppa.action_type = 'X'
28 and ppa.action_status = 'C'
29 and ppa.report_type = hl.meaning
30 and hl.lookup_type = 'PAYSLIP_REPORT_TYPES'
31 and hl.lookup_code = 'US'
32 and ppa.payroll_action_id = paa.payroll_action_id
33 and paa.assignment_id = paf.assignment_id
34 and paf.person_id = ppf.person_id
35 and ppf.person_id = l_person_id
36 and ppa.effective_date = (select max(ppa1.effective_date)
37 from pay_payroll_actions ppa1
38 ,pay_assignment_actions paa1
39 ,hr_lookups hl1
40 where ppa1.effective_date <= l_eff_date
41 and ppa1.action_type = 'X'
42 and ppa1.action_status = 'C'
43 and ppa1.report_type = hl1.meaning
44 and hl1.lookup_type = 'PAYSLIP_REPORT_TYPES'
45 and hl1.lookup_code = 'US'
46 and ppa1.payroll_action_id = paa1.payroll_action_id
47 and paa1.assignment_id = paa.assignment_id
48 and ppa1.business_group_id = ppa.business_group_id)
49 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date;
50
51 -- Cursor to fetch Work Location State details
52
53 cursor csr_state_det(p_location_id number) is
54 select region_2,location_code,
55 (select state_name from pay_us_states where state_abbrev = region_2)
56 from hr_locations_all
57 where location_id = p_location_id;
58
59 -- Cursor to fetch legislation_code
60
61 cursor csr_leg_code(p_bus_grp_id number) is
62 select to_char(org_information9) from
63 hr_organization_information where organization_id = p_bus_grp_id
64 and org_information_context = 'Business Group Information';
65
66 -- Cursor to fetch run_type
67
68 cursor csr_run_type(p_assignment_action_id NUMBER) is
69 select prt.run_type_name
70 from pay_assignment_actions paa_xfr
71 ,pay_assignment_actions paa_prepay
72 ,pay_payroll_actions ppa_prepay
73 ,pay_run_types_f prt
74 where paa_xfr.assignment_action_id = p_assignment_action_id
75 and (INSTR(paa_xfr.serial_number, 'PY') <> 0
76 or INSTR(paa_xfr.serial_number, 'UY') <> 0)
77 and paa_xfr.source_action_id is not null
78 and fnd_number.canonical_to_number(SUBSTR(paa_xfr.serial_number, 3)) = paa_prepay.assignment_action_id
79 and paa_prepay.payroll_action_id = ppa_prepay.payroll_action_id
80 and ppa_prepay.action_type in ('R', 'Q')
81 and paa_prepay.run_type_id = prt.run_type_id
82 and prt.legislation_code = 'US';
83
84 -- Cursor to fetch all required details on US Payroll by HR Helpdesk
85
86 cursor csr_uspay_det(p_asg_action_id number,p_asg_id number,l_eff_date date,p_state_code varchar2,p_state_desc varchar2,p_loc_name varchar2)
87 is
88 SELECT
89 organization_name
90 ,job
91 ,to_char(payment_date,'YYYY-MM-DD')
92 ,Period_type
93 ,location_name
94 ,employee_address1 || employee_address2 || employee_address3 || ' ' || employee_city || ' ' || employee_state || ' ' || employee_zip_code
95 ,payroll_name
96 ,'USD'
97 ,to_char(ending_date,'YYYY-MM-DD')
98 ,'Federal'
99 ,(select status from pay_us_emp_w4dtl_action_info_v
100 where action_context_id = p_asg_action_id
101 and tax_jurisdiction = 'Federal'
102 and trunc(effective_date) <= l_eff_date) STATUS
103
104 ,(select exemptions from pay_us_emp_w4dtl_action_info_v
105 where action_context_id = p_asg_action_id
106 and assignment_id = p_asg_id
107 and trunc(effective_date) <= l_eff_date
108 and tax_jurisdiction = 'Federal') EXEMPTIONS
109
110 ,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
111 where action_context_id = p_asg_action_id
112 and assignment_id = p_asg_id
113 and trunc(effective_date) <= l_eff_date
114 and tax_jurisdiction = 'Federal') ADDNL_TAX_AMOUNT
115
116 ,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
117 where action_context_id = p_asg_action_id
118 and assignment_id = p_asg_id
119 and trunc(effective_date) <= l_eff_date
120 and tax_jurisdiction = 'Federal') OVERRIDE_TAX_AMOUNT
121
122 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
123 where action_context_id = p_asg_action_id
124 and assignment_id = p_asg_id
125 and trunc(effective_date) <= l_eff_date
126 and tax_jurisdiction = 'Federal') OVERRIDE_TAX_PERCENTAGE
127 ,p_state_code
128
129 ,(select exemptions from pay_us_emp_w4dtl_action_info_v
130 where action_context_id = p_asg_action_id
131 and assignment_id = p_asg_id
132 and trunc(effective_date) <= l_eff_date
133 and tax_jurisdiction = p_state_desc) STEXEMPTIONS
134
135 ,(select additional_tax_amount from pay_us_emp_w4dtl_action_info_v
136 where action_context_id = p_asg_action_id
137 and assignment_id = p_asg_id
138 and trunc(effective_date) <= l_eff_date
139 and tax_jurisdiction = p_state_desc) STADDNL_TAX_AMOUNT
140
141 ,(select override_tax_amount from pay_us_emp_w4dtl_action_info_v
142 where action_context_id = p_asg_action_id
143 and assignment_id = p_asg_id
144 and trunc(effective_date) <= l_eff_date
145 and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_AMOUNT
146
147 ,(select override_tax_percentage from pay_us_emp_w4dtl_action_info_v
148 where action_context_id = p_asg_action_id
149 and assignment_id = p_asg_id
150 and trunc(effective_date) <= l_eff_date
151 and tax_jurisdiction = p_state_desc) STOVERRIDE_TAX_PERCENTAGE
152
153 ,(select gross_earnings
154 from pay_ac_emp_sum_action_info_v
155 where action_context_id = p_asg_action_id
156 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_EARNINGS_CV
157
158 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
159 from pay_ac_emp_sum_action_info_v
160 where action_context_id = p_asg_action_id
161 and action_information_category = 'AC SUMMARY CURRENT') TAXABLE_GROSS_CV
162
163 ,(select taxes
164 from pay_ac_emp_sum_action_info_v
165 where action_context_id = p_asg_action_id
166 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_TAXES_CV
167
168 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
169 from pay_ac_emp_sum_action_info_v
170 where action_context_id = p_asg_action_id
171 and action_information_category = 'AC SUMMARY CURRENT') TOTAL_DEDUCTIONS_CV
172
173 ,(select net_pay
174 from pay_ac_emp_sum_action_info_v
175 where action_context_id = p_asg_action_id
176 and action_information_category = 'AC SUMMARY CURRENT') NET_PAY_CV
177
178 ,(select gross_earnings
179 from pay_ac_emp_sum_action_info_v
180 where action_context_id = p_asg_action_id
181 and action_information_category = 'AC SUMMARY YTD') TOTAL_EARNINGS_YTD
182
183 ,(select (nvl(gross_earnings, 0) - nvl(pretax_deductions, 0))
184 from pay_ac_emp_sum_action_info_v
185 where action_context_id = p_asg_action_id
186 and action_information_category = 'AC SUMMARY YTD') TAXABLE_GROSS_YTD
187
188 ,(select taxes
189 from pay_ac_emp_sum_action_info_v
190 where action_context_id = p_asg_action_id
191 and action_information_category = 'AC SUMMARY YTD') TOTAL_TAXES_YTD
192
193 ,(select (nvl(pretax_deductions, 0) + nvl(after_tax_deductions, 0))
194 from pay_ac_emp_sum_action_info_v
195 where action_context_id = p_asg_action_id
196 and action_information_category = 'AC SUMMARY YTD') TOTAL_DEDUCTIONS_YTD
197
198 ,(select net_pay
199 from pay_ac_emp_sum_action_info_v
200 where action_context_id = p_asg_action_id
201 and action_information_category = 'AC SUMMARY YTD') NET_PAY_YTD
202
203 from pay_employee_action_info_v peai
204 where action_context_id = p_asg_action_id
205 and assignment_id = p_asg_id
206 and trunc(effective_date) <= l_eff_date
207 and location_name = p_loc_name;
208
209 -- Cursor declaration for US Payroll details ends here
210
211 -- Variable declarations for US Payroll details starts here
212
213 p_location_name varchar2(100);
214
215 p_assignment_id per_all_assignments_f.assignment_id%type;
216 p_assg_action_id pay_assignment_actions.assignment_action_id%type;
217 p_pyrl_action_id pay_payroll_actions.payroll_action_id%type;
218
219 p_cnt number;
220
221 p_location_id number;
222 p_state_code varchar2(100);
223 p_state_desc varchar2(100);
224 lv_run_typ_nm pay_run_types_f.run_type_name%TYPE;
225
226 -- Variable declarations for US Payroll details ends here
227
228 begin
229 p_cnt := 1;
230
231 open csr_uspay_req(p_per_id,p_eff_date);
232 loop
233 fetch csr_uspay_req into p_pyrl_action_id,p_assignment_id,p_assg_action_id,p_location_id;
234 exit when csr_uspay_req%notfound;
235
236 open csr_state_det(p_location_id);
237 fetch csr_state_det into p_state_code,p_location_name,p_state_desc;
238 close csr_state_det;
239
240 lv_run_typ_nm := NULL;
241
242 open csr_run_type(p_assg_action_id);
243 fetch csr_run_type into lv_run_typ_nm;
244 close csr_run_type;
245
246 if lv_run_typ_nm is NULL then
247 lv_run_typ_nm := 'Regular Standard Run';
248 end if;
249
250 p_pyrl_dtls(p_cnt).RUN_TYPE := lv_run_typ_nm;
251
252 open csr_uspay_det(p_assg_action_id,p_assignment_id,p_eff_date, p_state_code,p_state_desc,p_location_name);
253 fetch csr_uspay_det into p_pyrl_dtls(p_cnt).COMPANY,
254 p_pyrl_dtls(p_cnt).JOB_TITLE,
255 p_pyrl_dtls(p_cnt).PAYMENT_DATE,
256 p_pyrl_dtls(p_cnt).PAY_FREQUENCY,
257 p_pyrl_dtls(p_cnt).TAX_LOCATION,
258 p_pyrl_dtls(p_cnt).ADDRESS,
259 p_pyrl_dtls(p_cnt).PAY_GROUP,
260 p_pyrl_dtls(p_cnt).CURRENCY_CODE,
261 p_pyrl_dtls(p_cnt).PERIOD_END,
262 p_pyrl_dtls(p_cnt).TAX_JURISDICTION,
263 p_pyrl_dtls(p_cnt).MARITAL_STATUS,
264 p_pyrl_dtls(p_cnt).FED_EXEMPTIONS,
265 p_pyrl_dtls(p_cnt).FED_ADDNL_TAX_AMOUNT,
266 p_pyrl_dtls(p_cnt).FED_OVERRIDE_TAX_AMOUNT,
267 p_pyrl_dtls(p_cnt).FED_OVERRIDE_TAX_PERCENTAGE,
268 p_pyrl_dtls(p_cnt).STATE_CODE,
269 p_pyrl_dtls(p_cnt).ST_EXEMPTIONS,
270 p_pyrl_dtls(p_cnt).ST_ADDNL_TAX_AMOUNT,
271 p_pyrl_dtls(p_cnt).ST_OVERRIDE_TAX_AMOUNT,
272 p_pyrl_dtls(p_cnt).ST_OVERRIDE_TAX_PERCENTAGE,
273 p_pyrl_dtls(p_cnt).TOTAL_EARNINGS_CV,
274 p_pyrl_dtls(p_cnt).TAX_GROSS_CV,
275 p_pyrl_dtls(p_cnt).TOTAL_TAXES_CV,
276 p_pyrl_dtls(p_cnt).TOTAL_DED_CV,
277 p_pyrl_dtls(p_cnt).TOTAL_NETPAY_CV,
278 p_pyrl_dtls(p_cnt).TOTAL_EARNINGS_YTD,
279 p_pyrl_dtls(p_cnt).TAX_GROSS_YTD,
280 p_pyrl_dtls(p_cnt).TOTAL_TAXES_YTD,
281 p_pyrl_dtls(p_cnt).TOTAL_DED_YTD,
282 p_pyrl_dtls(p_cnt).TOTAL_NETPAY_YTD;
283 exit when csr_uspay_det%notfound;
284 close csr_uspay_det;
285 p_pyrl_dtls(p_cnt).LEGISLATION_CODE := p_leg_code;
286 p_cnt := p_cnt+1;
287
288 end loop;
289 close csr_uspay_req;
290
291
292
293 exception when others
294 then
295 p_error := 'FROM US PAYROLL'||substr(SQLERRM,1,1500);
296 end GET_USPAY_DETAILS;
297
298 end PAY_US_HR_HELPDESK;