1 PACKAGE hr_us_w2_rep AUTHID CURRENT_USER AS
2 /* $Header: pyusw2pg.pkh 120.3.12020000.1 2012/06/28 19:38:05 appldev ship $ */
3
4
5
6 /*
7 Name : hr_us_w2_rep (Header)
8 File : pyusw2pg.pkh
9 Description : This package declares functions and procedures which are
10 used to return values for the W2 US Payroll reports.
11
12 Change List
13 -----------
14
15 Version Date Author Bug No. Description of Change
16 -------+---------+-----------+---------+----------------------------------
17 40.0 13-MAY-98 ssarma Date Created
18 40.1 22-JUL-99 ssarma Added the following functions :
19 get_w2_tax_item, get_tax_unit_addr_line,
20 get_tax_unit_bg, get_per_item,
21 get_state_item.
22 115.6 10-AUG-01 kthirmiy Added a new function get_leav_reason
23 to get the termination reason meaning
24 to fix the bug 1482168.
25 115.8 16-SEP-01 ssarma Overloaded function get_w2_box_15
26 115.9 17-SEP-01 ssarma Removed default for effective date from
27 function get_w2_box_15.
28 115.10 30-NOV-01 meshah add dbdrv.
29 115.11 17-May-02 fusman add checkfile dbdrv.
30 115.11 24-JUN-02 rsirigir As per bug 2429333 from
31 FUNCTION get_w2_arch_bal
32 (w2_tax_unit_id in number,
33 to
34 FUNCTION get_w2_arch_bal
35 (w2_tax_unit_id in number DEFAULT NULL,
36 115.12 24-JUN-02 rsirigir As per bug 2429333 from
37 FUNCTION get_w2_arch_bal
38 (w2_jurisdiction_code in varchar2,
39 to
40 FUNCTION get_w2_arch_bal
41 (w2_jurisdiction_code
42 varchar2 DEFAULT NULL,
43 115.15 06-AUG-02 ppanda For fixing Bug #2145804 and 2207317
44 a procedure get_county_tax_info added
45 115.16 10-SEP-02 kthirmiy Added a procedure get_agent_tax_unit_id
46 for the Agent reporting Enhancement
47 115.17 13-Nov-2002 fusman 2625264 Added Pl/sql table to check the optional
48 reporting parameter of fed wages in state wages
49 for NY
50 115.18 13-Nov-2002 fusman Moved the PL/SQL declaration to package header.
51 Removed WNPS from RESTRICT_REFERENCES for
52 get_w2_arch_bal function.
53 115.20 02-DEC-2002 asasthan nocopy chnages for gscc compliance
54 115.23 12-AUG-2003 rsethupa 2631650 Rolled back the changes intoduced in version
55 115.22
56 115.24 26-AUG-2003 meshah Added in a new function
57 get_w2_box17_label. This function is
58 called from the pay_us_locality_w2_v.
59 115.25 27-SEP-2007 sausingh 5517938 Added a new function get_last_deffer_year
60 to display first year of designated roth
61 contribution
62 115.27 14-Sep-2009 kagangul 8353425 Added a new function get_w2_employee_name.
63 =============================================================================
64
65 */
66 TYPE tax_info_record IS RECORD (
67 tax_unit_id number(15),
68 tax_value varchar2(1));
69
70 TYPE newyork_tax_tabrec IS TABLE OF tax_info_record
71 INDEX BY BINARY_INTEGER;
72 ltr_newyork_tax_table newyork_tax_tabrec;
73
74 TYPE box17_rec IS RECORD
75 ( tax_unit_id number
76 , state_abbrev varchar2(2)
77 , value varchar2(10)
78 );
79
80 TYPE box17_table IS TABLE OF
81 box17_rec
82 INDEX BY BINARY_INTEGER;
83
84 ltr_box17 box17_table;
85
86
87 FUNCTION get_user_entity_id(w2_balance_name in varchar2)
88 RETURN NUMBER;
89 PRAGMA RESTRICT_REFERENCES(get_user_entity_id, WNDS,WNPS);
90
91 FUNCTION get_context_id(w2_context_name in varchar2)
92 RETURN NUMBER;
93 PRAGMA RESTRICT_REFERENCES(get_context_id, WNDS,WNPS);
94
95 FUNCTION get_w2_bal_amt(w2_asg_act_id in number,
96 w2_balance_name in varchar2,
97 w2_tax_unit_id in varchar2,
98 w2_jurisdiction_code in varchar2,
99 w2_jurisdiction_level in number)
100 RETURN NUMBER;
101 PRAGMA RESTRICT_REFERENCES(get_w2_bal_amt, WNDS,WNPS);
102
103 FUNCTION get_w2_arch_bal(w2_asg_act_id in number,
104 w2_balance_name in varchar2,
105 w2_tax_unit_id in number DEFAULT NULL,
106 w2_jurisdiction_code in varchar2 DEFAULT NULL,
107 --w2_tax_unit_id in number,
108 --w2_jurisdiction_code in varchar2,
109 w2_jurisdiction_level in number)
110 RETURN NUMBER;
111 PRAGMA RESTRICT_REFERENCES(get_w2_arch_bal, WNDS);
112
113 FUNCTION get_w2_organization_id(w2_asg_id in number,
114 w2_effective_date in date)
115 RETURN NUMBER;
116 PRAGMA RESTRICT_REFERENCES(get_w2_organization_id, WNDS,WNPS);
117
118 FUNCTION get_w2_location_id(w2_asg_id in number,
119 w2_effective_date in date)
120 RETURN NUMBER;
121 PRAGMA RESTRICT_REFERENCES(get_w2_location_id, WNDS,WNPS);
122
123 FUNCTION get_w2_postal_code(w2_person_id in number,
124 w2_effective_date in date)
125 RETURN VARCHAR2;
126 PRAGMA RESTRICT_REFERENCES(get_w2_postal_code, WNDS,WNPS);
127
128 FUNCTION get_w2_employee_name(w2_person_id IN NUMBER, w2_effective_date IN DATE)
129 RETURN VARCHAR2;
130 PRAGMA RESTRICT_REFERENCES(get_w2_employee_name, WNDS,WNPS);
131
132 FUNCTION get_w2_state_ein ( w2_tax_unit_id in number,
133 w2_state_abbrev in varchar2)
134 RETURN varchar2;
135 PRAGMA RESTRICT_REFERENCES(get_w2_state_ein, WNDS,WNPS);
136
137 FUNCTION get_w2_state_uin ( w2_tax_unit_id in number,
138 w2_state_abbrev in varchar2)
139 RETURN varchar2;
140 PRAGMA RESTRICT_REFERENCES(get_w2_state_uin, WNDS,WNPS);
141
142 FUNCTION get_w2_high_comp_amt (w2_rownum in number,
143 w2_restrict in number,
144 w2_bal_amt in number)
145 RETURN number;
146 PRAGMA RESTRICT_REFERENCES(get_w2_high_comp_amt, WNDS,WNPS);
147
148 FUNCTION get_w2_box_15 (w2_asg_act_id in number,
149 w2_balance_name in varchar2,
150 w2_tax_unit_id in number,
151 w2_jurisdiction_code in varchar2,
152 w2_jurisdiction_level in number
153 ) RETURN varchar2;
154 PRAGMA RESTRICT_REFERENCES(get_w2_box_15, WNDS,WNPS);
155
156 FUNCTION get_w2_box_15 (w2_asg_act_id in number,
157 w2_balance_name in varchar2,
158 w2_tax_unit_id in number,
159 w2_jurisdiction_code in varchar2,
160 w2_jurisdiction_level in number,
161 w2_effective_date in date ) RETURN varchar2;
162 PRAGMA RESTRICT_REFERENCES(get_w2_box_15, WNDS,WNPS);
163
164 FUNCTION get_w2_tax_unit_item ( w2_tax_unit_id in number,
165 w2_payroll_action_id in number,
166 w2_tax_unit_item in varchar2)
167 RETURN varchar2;
168 PRAGMA RESTRICT_REFERENCES(get_w2_tax_unit_item, WNDS,WNPS);
169
170 FUNCTION get_tax_unit_addr_line (w2_tax_unit_id in number,
171 w2_addr_item in varchar2)
172 RETURN varchar2 ;
173 PRAGMA RESTRICT_REFERENCES(get_tax_unit_addr_line, WNDS,WNPS);
174
175 FUNCTION get_tax_unit_bg(w2_tax_unit_id in number)
176 RETURN number;
177 PRAGMA RESTRICT_REFERENCES(get_tax_unit_bg, WNDS,WNPS);
178
179 FUNCTION get_per_item (w2_assignment_action_id in number,
180 w2_per_item in varchar2)
181 RETURN VARCHAR2;
182 PRAGMA RESTRICT_REFERENCES(get_per_item, WNDS,WNPS);
183
184 FUNCTION get_state_item (w2_tax_unit_id in number,
185 w2_jurisdiction_code in varchar2,
186 w2_payroll_action_id in number,
187 w2_state_item in varchar2)
188 RETURN VARCHAR2;
189 PRAGMA RESTRICT_REFERENCES(get_state_item, WNDS,WNPS);
190
191 FUNCTION get_leav_reason (w2_leaving_reason in varchar2)
192 RETURN VARCHAR2;
193 PRAGMA RESTRICT_REFERENCES(get_leav_reason, WNDS,WNPS);
194
195 PROCEDURE GET_COUNTY_TAX_INFO ( p_jurisdiction_code in Varchar2 ,
196 p_tax_year in number,
197 p_tax_rate out nocopy number,
198 P_mh_tax_rate out nocopy number,
199 P_mh_tax_limit out nocopy number,
200 P_occ_mh_tax_limit out nocopy number,
201 P_occ_mh_wage_limit out nocopy number,
202 P_mh_tax_wage_limit out nocopy number
203 );
204
205
206 PROCEDURE get_agent_tax_unit_id( p_business_group_id in number,
207 p_year in number,
208 p_agent_tax_unit_id out nocopy number,
209 p_error_mesg out nocopy varchar2 ) ;
210
211 FUNCTION get_w2_userra_bal(w2_asg_act_id in number,
212 w2_tax_unit_id in number DEFAULT NULL,
213 w2_jurisdiction_code in varchar2 DEFAULT NULL,
214 w2_jurisdiction_level in number,
215 p_userra_code in varchar2
216 )
217 RETURN NUMBER;
218 PRAGMA RESTRICT_REFERENCES(get_w2_userra_bal, WNDS,WNPS);
219
220 FUNCTION get_w2_box17_label (p_tax_unit_id in number,
221 p_state_abbrev in varchar2)
222 return varchar2;
223
224 FUNCTION get_last_deffer_year ( p_ass_action_id in number)
225
226 return varchar2;
227
228 FUNCTION get_w2_employee_number(w2_nat_ident in varchar2, w2_effective_date in date)
229 RETURN varchar2;
230 FUNCTION get_w2_worker_compensation(w2_asg_id in number, w2_effective_date in date)
231 RETURN varchar2;
232 FUNCTION get_w2_location_cd(w2_asg_id in number, w2_effective_date in date)
233 RETURN varchar2;
234 end hr_us_w2_rep;
235