1 PACKAGE hr_us_w2_rep AS
2 /* $Header: pyusw2pg.pkh 120.1.12010000.2 2008/08/06 08:43:43 ubhat 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 =============================================================================
63
64 */
65 TYPE tax_info_record IS RECORD (
66 tax_unit_id number(15),
67 tax_value varchar2(1));
68
69 TYPE newyork_tax_tabrec IS TABLE OF tax_info_record
70 INDEX BY BINARY_INTEGER;
71 ltr_newyork_tax_table newyork_tax_tabrec;
72
73 TYPE box17_rec IS RECORD
74 ( tax_unit_id number
75 , state_abbrev varchar2(2)
76 , value varchar2(10)
77 );
78
79 TYPE box17_table IS TABLE OF
80 box17_rec
81 INDEX BY BINARY_INTEGER;
82
83 ltr_box17 box17_table;
84
85
86 FUNCTION get_user_entity_id(w2_balance_name in varchar2)
87 RETURN NUMBER;
88 PRAGMA RESTRICT_REFERENCES(get_user_entity_id, WNDS,WNPS);
89
90 FUNCTION get_context_id(w2_context_name in varchar2)
91 RETURN NUMBER;
92 PRAGMA RESTRICT_REFERENCES(get_context_id, WNDS,WNPS);
93
94 FUNCTION get_w2_bal_amt(w2_asg_act_id in number,
95 w2_balance_name in varchar2,
96 w2_tax_unit_id in varchar2,
97 w2_jurisdiction_code in varchar2,
98 w2_jurisdiction_level in number)
99 RETURN NUMBER;
100 PRAGMA RESTRICT_REFERENCES(get_w2_bal_amt, WNDS,WNPS);
101
102 FUNCTION get_w2_arch_bal(w2_asg_act_id in number,
103 w2_balance_name in varchar2,
104 w2_tax_unit_id in number DEFAULT NULL,
105 w2_jurisdiction_code in varchar2 DEFAULT NULL,
106 --w2_tax_unit_id in number,
107 --w2_jurisdiction_code in varchar2,
108 w2_jurisdiction_level in number)
109 RETURN NUMBER;
110 PRAGMA RESTRICT_REFERENCES(get_w2_arch_bal, WNDS);
111
112 FUNCTION get_w2_organization_id(w2_asg_id in number,
113 w2_effective_date in date)
114 RETURN NUMBER;
115 PRAGMA RESTRICT_REFERENCES(get_w2_organization_id, WNDS,WNPS);
116
117 FUNCTION get_w2_location_id(w2_asg_id in number,
118 w2_effective_date in date)
119 RETURN NUMBER;
120 PRAGMA RESTRICT_REFERENCES(get_w2_location_id, WNDS,WNPS);
121
122 FUNCTION get_w2_postal_code(w2_person_id in number,
123 w2_effective_date in date)
124 RETURN VARCHAR2;
125 PRAGMA RESTRICT_REFERENCES(get_w2_postal_code, WNDS,WNPS);
126
127 FUNCTION get_w2_state_ein ( w2_tax_unit_id in number,
128 w2_state_abbrev in varchar2)
129 RETURN varchar2;
130 PRAGMA RESTRICT_REFERENCES(get_w2_state_ein, WNDS,WNPS);
131
132 FUNCTION get_w2_state_uin ( w2_tax_unit_id in number,
133 w2_state_abbrev in varchar2)
134 RETURN varchar2;
135 PRAGMA RESTRICT_REFERENCES(get_w2_state_uin, WNDS,WNPS);
136
137 FUNCTION get_w2_high_comp_amt (w2_rownum in number,
138 w2_restrict in number,
139 w2_bal_amt in number)
140 RETURN number;
141 PRAGMA RESTRICT_REFERENCES(get_w2_high_comp_amt, WNDS,WNPS);
142
143 FUNCTION get_w2_box_15 (w2_asg_act_id in number,
144 w2_balance_name in varchar2,
145 w2_tax_unit_id in number,
146 w2_jurisdiction_code in varchar2,
147 w2_jurisdiction_level in number
148 ) RETURN varchar2;
149 PRAGMA RESTRICT_REFERENCES(get_w2_box_15, WNDS,WNPS);
150
151 FUNCTION get_w2_box_15 (w2_asg_act_id in number,
152 w2_balance_name in varchar2,
153 w2_tax_unit_id in number,
154 w2_jurisdiction_code in varchar2,
155 w2_jurisdiction_level in number,
156 w2_effective_date in date ) RETURN varchar2;
157 PRAGMA RESTRICT_REFERENCES(get_w2_box_15, WNDS,WNPS);
158
159 FUNCTION get_w2_tax_unit_item ( w2_tax_unit_id in number,
160 w2_payroll_action_id in number,
161 w2_tax_unit_item in varchar2)
162 RETURN varchar2;
163 PRAGMA RESTRICT_REFERENCES(get_w2_tax_unit_item, WNDS,WNPS);
164
165 FUNCTION get_tax_unit_addr_line (w2_tax_unit_id in number,
166 w2_addr_item in varchar2)
167 RETURN varchar2 ;
168 PRAGMA RESTRICT_REFERENCES(get_tax_unit_addr_line, WNDS,WNPS);
169
170 FUNCTION get_tax_unit_bg(w2_tax_unit_id in number)
171 RETURN number;
172 PRAGMA RESTRICT_REFERENCES(get_tax_unit_bg, WNDS,WNPS);
173
174 FUNCTION get_per_item (w2_assignment_action_id in number,
175 w2_per_item in varchar2)
176 RETURN VARCHAR2;
177 PRAGMA RESTRICT_REFERENCES(get_per_item, WNDS,WNPS);
178
179 FUNCTION get_state_item (w2_tax_unit_id in number,
180 w2_jurisdiction_code in varchar2,
181 w2_payroll_action_id in number,
182 w2_state_item in varchar2)
183 RETURN VARCHAR2;
184 PRAGMA RESTRICT_REFERENCES(get_state_item, WNDS,WNPS);
185
186 FUNCTION get_leav_reason (w2_leaving_reason in varchar2)
187 RETURN VARCHAR2;
188 PRAGMA RESTRICT_REFERENCES(get_leav_reason, WNDS,WNPS);
189
190 PROCEDURE GET_COUNTY_TAX_INFO ( p_jurisdiction_code in Varchar2 ,
191 p_tax_year in number,
192 p_tax_rate out nocopy number,
193 P_mh_tax_rate out nocopy number,
194 P_mh_tax_limit out nocopy number,
195 P_occ_mh_tax_limit out nocopy number,
196 P_occ_mh_wage_limit out nocopy number,
197 P_mh_tax_wage_limit out nocopy number
198 );
199
200
201 PROCEDURE get_agent_tax_unit_id( p_business_group_id in number,
202 p_year in number,
203 p_agent_tax_unit_id out nocopy number,
204 p_error_mesg out nocopy varchar2 ) ;
205
206 FUNCTION get_w2_userra_bal(w2_asg_act_id in number,
207 w2_tax_unit_id in number DEFAULT NULL,
208 w2_jurisdiction_code in varchar2 DEFAULT NULL,
209 w2_jurisdiction_level in number,
210 p_userra_code in varchar2
211 )
212 RETURN NUMBER;
213 PRAGMA RESTRICT_REFERENCES(get_w2_userra_bal, WNDS,WNPS);
214
215 FUNCTION get_w2_box17_label (p_tax_unit_id in number,
216 p_state_abbrev in varchar2)
217 return varchar2;
218
219 FUNCTION get_last_deffer_year ( p_ass_action_id in number)
220
221 return varchar2;
222
223 FUNCTION get_w2_employee_number(w2_nat_ident in varchar2, w2_effective_date in date)
224 RETURN varchar2;
225 FUNCTION get_w2_worker_compensation(w2_asg_id in number, w2_effective_date in date)
226 RETURN varchar2;
227 FUNCTION get_w2_location_cd(w2_asg_id in number, w2_effective_date in date)
228 RETURN varchar2;
229 end hr_us_w2_rep;
230