1 PACKAGE pay_us_1099R_udfs AUTHID CURRENT_USER AS
2 /* $Header: py99udfs.pkh 120.0.12010000.1 2008/07/27 21:59:13 appldev ship $ */
3 /*
4 +======================================================================+
5 | Copyright (c) 1996 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +======================================================================+
9
10 Name : pay_us_1099R_udfs
11 Filename : py99udfs.pkh
12 Purpose : creates user defined functions for 1099R
13
14 Change List
15 -----------
16 Date Name Vers Bug No Description
17 ---- ---- ---- ------ -----------
18 3/10 HEKIM 40.0 Created.
19 23-MAY-97 M.Reid 40.1 Removed show errors.
20 12-NOV-02 D.Joshi 115.2 Added CFS_control Total
21 for all other Payable
22 18-nov-02 djoshi 115.3 Added dbdrv command
23 02-dec-02 djoshi 115.4 No Copy added to all
24 out Parameter
25 30-OCT-2003 jgoswami 115.6 Added GET_1099R_ITEM_DATA,
26 format_pub1220_address.
27 06-NOV-2003 jgoswami 115.7 Added format_1099r_wv_address
28 13-NOV-2003 jgoswami 115.8 3241256 Added GET_1099R_TRANSMITTER_VALUE
29 */
30 --
31 --
32 TYPE numeric_data_table IS TABLE OF NUMBER
33 INDEX BY BINARY_INTEGER;
34
35 gt_combined_filer_state_payees numeric_data_table;
36 gt_CFS_control_total_1 numeric_data_table;
37 gt_CFS_control_total_2 numeric_data_table;
38 gt_CFS_control_total_3 numeric_data_table;
39 gt_CFS_control_total_4 numeric_data_table;
40 gt_CFS_control_total_5 numeric_data_table;
41 gt_CFS_control_total_6 numeric_data_table;
42 gt_CFS_control_total_8 numeric_data_table;
43 gt_CFS_control_total_9 numeric_data_table;
44 gt_CFS_SIT_total numeric_data_table;
45 gt_CFS_LIT_total numeric_data_table;
46 --
47 FUNCTION init_global_1099R_tables(p_dummy in VARCHAR2) RETURN VARCHAR2;
48 --
49 FUNCTION get_1099R_state_payee_count(p_state in VARCHAR2) RETURN NUMBER;
50 --
51 FUNCTION state_1099R_specs( p_state in VARCHAR2,
52 p_amount_1 in NUMBER,
53 p_amount_2 in NUMBER,
54 p_amount_3 in NUMBER,
55 p_amount_4 in NUMBER,
56 p_amount_5 in NUMBER,
57 p_amount_6 in NUMBER,
58 p_amount_8 in NUMBER,
59 p_amount_9 in NUMBER,
60 p_SIT in NUMBER,
61 p_LIT in NUMBER,
62 p_SEIN in VARCHAR2,
63 p_state_taxable in NUMBER) RETURN VARCHAR2;
64 --
65 FUNCTION get_1099R_name_control (p_name in VARCHAR2) RETURN VARCHAR2;
66 --
67 FUNCTION get_1099R_NE_SEIN (p_SEIN in VARCHAR2) RETURN VARCHAR2;
68 --
69 FUNCTION get_1099R_state_total(p_state in VARCHAR2,
70 p_type in VARCHAR2 ) RETURN VARCHAR2;
71 --
72 FUNCTION combined_filer_1099R_state (p_state in VARCHAR2) RETURN VARCHAR2;
73 --
74 FUNCTION get_1099R_state_code (p_state in VARCHAR2) RETURN VARCHAR2;
75
76 FUNCTION Get_1099R_value(
77 p_assignment_action_id number, -- context
78 p_tax_unit_id number,-- context
79 sp_out_1 OUT nocopy varchar2,
80 sp_out_2 OUT nocopy varchar2,
81 sp_out_3 OUT nocopy varchar2,
82 sp_out_4 OUT nocopy varchar2,
83 sp_out_5 OUT nocopy varchar2,
84 sp_out_6 OUT nocopy varchar2,
85 sp_out_7 OUT nocopy varchar2,
86 sp_out_8 OUT nocopy varchar2,
87 sp_out_9 OUT nocopy varchar2,
88 sp_out_10 OUT nocopy varchar2)
89 RETURN VARCHAR2;
90
91 FUNCTION Get_1099R_ny_value(
92 p_assignment_action_id number, -- context
93 p_tax_unit_id number,-- context
94 p_state in varchar2)
95 RETURN VARCHAR2;
96
97
98 --
99 -- Function GET_1099R_ITEM_DATA is a generalized function which can be
100 -- used to get data for a given item_type.
101 -- Function to Get Payee Latest Address
102 --
103 /*
104 Parameters :
105 p_effective_date -
106 This parameter indicates the year for the function.
107 p_item_name - 'EE_ADDRESS'
108 identifies Employee Address required for
109 Employee record.
110 p_report_type - This parameter will have the type of the report.
111 eg: '1099R'
112 p_format - This parameter will have the format to be printed
113 on 1099R. eg:'PUB1220','MMREF'
114 ( Will be used when we move the formatting from formula to function)
115 p_record_name - This parameter will have the particular
116 record name. eg: B for PUB1220
117 p_validate - This parameter will check whether it wants to
118 validate the error condition or override the
119 checking.
120 'N'- Override
121 'Y'- Check
122 p_exclude_from_output -
123 This parameter gives the information on
124 whether the record has to be printed or not.
125 'Y'- Do not print.
126 'N'- Print.
127 sp_out_1 - This out parameter returns Employee Location Address
128 sp_out_2 - This out parameter returns Employee Deliver Address
129 sp_out_3 - This out parameter returns Employee City
130 sp_out_4 - This out parameter returns State
131 sp_out_5 - This out parameter returns Zip Code
132 sp_out_6 - This out parameter returns Zip Code Extension
133 sp_out_7 - This out parameter returns Foreign State/Province
134 sp_out_8 - This out parameter returns Foreign Postal Code
135 sp_out_9 - This out parameter returns Foreign Country Code
136 sp_out_10 - This parameter is returns Employee Number
137 */
138
139 FUNCTION GET_1099R_ITEM_DATA(
140 p_assignment_id IN number,
141 p_date_earned IN date,
142 p_tax_unit_id IN number,
143 p_effective_date IN varchar2,
144 p_item_name IN varchar2,
145 p_report_type IN varchar2,
146 p_format IN varchar2,
147 p_report_qualifier IN varchar2,
148 p_record_name IN varchar2,
149 p_input_1 IN varchar2,
150 p_input_2 IN varchar2,
151 p_input_3 IN varchar2,
152 p_input_4 IN varchar2,
153 p_input_5 IN varchar2,
154 p_validate IN varchar2,
155 p_exclude_from_output OUT nocopy varchar2,
156 sp_out_1 OUT nocopy varchar2,
157 sp_out_2 OUT nocopy varchar2,
158 sp_out_3 OUT nocopy varchar2,
159 sp_out_4 OUT nocopy varchar2,
160 sp_out_5 OUT nocopy varchar2,
161 sp_out_6 OUT nocopy varchar2,
162 sp_out_7 OUT nocopy varchar2,
163 sp_out_8 OUT nocopy varchar2,
164 sp_out_9 OUT nocopy varchar2,
165 sp_out_10 OUT nocopy varchar2
166 ) RETURN VARCHAR2 ;
167
168 --
169 -- Procedure to Format Employee Address
170 --
171 PROCEDURE format_pub1220_address(
172 p_name IN varchar2,
173 p_locality_company_id IN varchar2,
174 p_emp_number IN varchar2,
175 p_address_line_1 IN varchar2,
176 p_address_line_2 IN varchar2,
177 p_address_line_3 IN varchar2,
178 p_town_or_city IN varchar2,
179 p_state IN varchar2,
180 p_postal_code IN varchar2,
181 p_country IN varchar2,
182 p_country_name IN varchar2,
183 p_region_1 IN varchar2,
184 p_region_2 IN varchar2,
185 p_valid_address IN varchar2,
186 p_item_name IN varchar2,
187 p_report_type IN varchar2,
188 p_record_name IN varchar2,
189 p_validate IN varchar2,
190 p_local_code IN varchar2,
191 p_exclude_from_output OUT nocopy varchar2,
192 sp_out_1 IN OUT nocopy varchar2,
193 sp_out_2 IN OUT nocopy varchar2,
194 sp_out_3 IN OUT nocopy varchar2,
195 sp_out_4 IN OUT nocopy varchar2,
196 sp_out_5 IN OUT nocopy varchar2,
197 sp_out_6 IN OUT nocopy varchar2,
198 sp_out_7 IN OUT nocopy varchar2,
199 sp_out_8 IN OUT nocopy varchar2,
200 sp_out_9 IN OUT nocopy varchar2,
201 sp_out_10 IN OUT nocopy varchar2 ) ;
202 --
203 PROCEDURE format_1099r_wv_address(
204 p_name IN varchar2,
205 p_locality_company_id IN varchar2,
206 p_emp_number IN varchar2,
207 p_address_line_1 IN varchar2,
208 p_address_line_2 IN varchar2,
209 p_address_line_3 IN varchar2,
210 p_town_or_city IN varchar2,
211 p_state IN varchar2,
212 p_postal_code IN varchar2,
213 p_country IN varchar2,
214 p_country_name IN varchar2,
215 p_region_1 IN varchar2,
216 p_region_2 IN varchar2,
217 p_valid_address IN varchar2,
218 p_item_name IN varchar2,
219 p_report_type IN varchar2,
220 p_record_name IN varchar2,
221 p_validate IN varchar2,
222 p_local_code IN varchar2,
223 p_exclude_from_output OUT nocopy varchar2,
224 sp_out_1 IN OUT nocopy varchar2,
225 sp_out_2 IN OUT nocopy varchar2,
226 sp_out_3 IN OUT nocopy varchar2,
227 sp_out_4 IN OUT nocopy varchar2,
228 sp_out_5 IN OUT nocopy varchar2,
229 sp_out_6 IN OUT nocopy varchar2,
230 sp_out_7 IN OUT nocopy varchar2,
231 sp_out_8 IN OUT nocopy varchar2,
232 sp_out_9 IN OUT nocopy varchar2,
233 sp_out_10 IN OUT nocopy varchar2 ) ;
234 --
235 FUNCTION Get_1099R_Transmitter_Value(
236 p_payroll_action_id in varchar2,
237 p_state in varchar2,
238 sp_out_1 IN OUT nocopy varchar2,
239 sp_out_2 IN OUT nocopy varchar2,
240 sp_out_3 IN OUT nocopy varchar2,
241 sp_out_4 IN OUT nocopy varchar2,
242 sp_out_5 IN OUT nocopy varchar2,
243 sp_out_6 IN OUT nocopy varchar2,
244 sp_out_7 IN OUT nocopy varchar2,
245 sp_out_8 IN OUT nocopy varchar2,
246 sp_out_9 IN OUT nocopy varchar2,
247 sp_out_10 IN OUT nocopy varchar2)
248 RETURN VARCHAR2;
249
250 --
251 --
252 END pay_us_1099R_udfs;