1 PACKAGE pay_us_get_item_data_pkg AS
2 /* $Header: pyusgitd.pkh 120.0.12000000.1 2007/01/18 02:29:56 appldev noship $ */
3
4 /*===========================================================================+
5 | Copyright (c) 2001 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +============================================================================+
9
10 Name
11 pay_us_get_item_data_pkg
12
13 Purpose
14 The purpose of this package is to derive data and Validate data to support
15 the generation of magnetic tape W2 / SQWL reports for US legilsative
16 requirements.
17
18 This mainly derives following data for W2 / SQWL
19 Contact Person Information
20 Employer Address
21 Employee Address
22 Contact Person Address
23
24 Referenced By: Package pay_us_reporting_utils_pkg
25
26 Notes
27
28 History
29 Date Developer Ver Bug # Description
30 --------- --------- ------ --------- ---------------------------------------------------------
31 14-Jul-03 ppanda 115.0 Created
32 11-Sep-03 ppanda 115.2 A new column added to record structure person_name_address
33 This change is the result of YE report requirements for
34 country short name
35 */
36
37 /* --------------------------------------------------------------
38 Name : GET_CONTACT_PERSON_INFO
39 Purpose : Purpose of this function is to get contact person details
40 required for Submitter Record
41 Parameters :
42 p_effective_date -
43 This parameter indicates the year for the function.
44 p_item_name - 'CR_PERSON'
45 identifies Contact Persons details in the Submitter record.
46 p_report_type - This parameter will have the type of the report.
47 eg: 'W2' or 'SQWL'
48 p_format - This parameter will have the format to be printed
49 on W2. eg:'MMREF'
50 p_record_name - This parameter will have the particular
51 record name. eg: RA,RF,RE,RT etc.
52 p_validate - This parameter will check whether it wants to
53 validate the error condition or override the checking.
54 'N'- Override
55 'Y'- Check
56 p_exclude_from_output - This parameter gives the information on
57 whether the record has to be printed or not.
58 'Y'- Do not print.
59 'N'- Print.
60 p_person_id IN person_id of Contact Person
61 p_contact_prsn_name OUT Contact Person Name --out_1
62 p_contact_prsn_phone OUT Contact Person Phone --out_2
63 p_contact_prsn_extension OUT Contact Person Extension --out_3
64 p_contact_prsn_email OUT Contact Person Email --out_4
65 p_contact_prsn_fax OUT Contact Person Fax --out_5
66 p_contact_prsn_first_name OUT Contact Person First Name --out_6
67 p_contact_prsn_middle_name OUT Contact Person Middle Name --out_7
68 p_contact_prsn_last_name OUT Contact Person Last Name --out_8
69
70 Error checking
71
72 Special Note :
73 ---------------------------------------------------------------- */
74 FUNCTION GET_CONTACT_PERSON_INFO(
75 p_assignment_id IN number,
76 p_date_earned IN date,
77 p_tax_unit_id IN number,
78 p_effective_date IN varchar2,
79 p_item_name IN varchar2,
80 p_report_type IN varchar2,
81 p_format IN varchar2,
82 p_report_qualifier IN varchar2,
83 p_record_name IN varchar2,
84 p_person_id IN varchar2,
85 p_validate IN varchar2,
86 p_exclude_from_output OUT nocopy varchar2,
87 p_contact_prsn_name OUT nocopy varchar2,
88 p_contact_prsn_phone OUT nocopy varchar2,
89 p_contact_prsn_extension OUT nocopy varchar2,
90 p_contact_prsn_email OUT nocopy varchar2,
91 p_contact_prsn_fax OUT nocopy varchar2,
92 p_contact_prsn_first_name OUT nocopy varchar2,
93 p_contact_prsn_middle_name OUT nocopy varchar2,
94 p_contact_prsn_last_name OUT nocopy varchar2
95 ) RETURN VARCHAR2;
96 --
97 -- Function to Get Employee Address
98 --
99 /*
100 Parameters :
101 p_effective_date -
102 This parameter indicates the year for the function.
103 p_item_name - 'EE_ADDRESS'
104 identifies Employee Address required for
105 Employee record.
106 p_report_type - This parameter will have the type of the report.
107 eg: 'W2' or 'SQWL'
108 p_format - This parameter will have the format to be printed
109 on W2. eg:'MMREF'
110 p_record_name - This parameter will have the particular
111 record name. eg: RE
112 p_validate - This parameter will check whether it wants to
113 validate the error condition or override the
114 checking.
115 'N'- Override
116 'Y'- Check
117 p_exclude_from_output -
118 This parameter gives the information on
119 whether the record has to be printed or not.
120 'Y'- Do not print.
121 'N'- Print.
122 sp_out_1 - This out parameter returns Employee Location Address
123 sp_out_2 - This out parameter returns Employee Deliver Address
124 sp_out_3 - This out parameter returns Employee City
125 sp_out_4 - This out parameter returns State
126 sp_out_5 - This out parameter returns Zip Code
127 sp_out_6 - This out parameter returns Zip Code Extension
128 sp_out_7 - This out parameter returns Foreign State/Province
129 sp_out_8 - This out parameter returns Foreign Postal Code
130 sp_out_9 - This out parameter returns Foreign Country Code
131 sp_out_10 - This parameter is returns Employee Number
132 */
133 FUNCTION GET_MMREF_EMPLOYEE_ADDRESS(
134 p_assignment_id IN number,
135 p_date_earned IN date,
136 p_tax_unit_id IN number,
137 p_effective_date IN varchar2,
138 p_item_name IN varchar2,
139 p_report_type IN varchar2,
140 p_format IN varchar2,
141 p_report_qualifier IN varchar2,
142 p_record_name IN varchar2,
143 p_input_1 IN varchar2,
144 p_input_2 IN varchar2,
145 p_input_3 IN varchar2,
146 p_input_4 IN varchar2,
147 p_input_5 IN varchar2,
148 p_validate IN varchar2,
149 p_exclude_from_output OUT nocopy varchar2,
150 sp_out_1 OUT nocopy varchar2,
151 sp_out_2 OUT nocopy varchar2,
152 sp_out_3 OUT nocopy varchar2,
153 sp_out_4 OUT nocopy varchar2,
154 sp_out_5 OUT nocopy varchar2,
155 sp_out_6 OUT nocopy varchar2,
156 sp_out_7 OUT nocopy varchar2,
157 sp_out_8 OUT nocopy varchar2,
158 sp_out_9 OUT nocopy varchar2,
159 sp_out_10 OUT nocopy varchar2
160 ) RETURN VARCHAR2;
161 --
162 -- Function GET_ER_ADDRESS to Get Employer Address
163 --
164 /*
165 Parameters :
166 p_effective_date -
167 This parameter indicates the year for the function.
168 p_item_name - 'ER_ADDRESS'
169 identifies Employee Address required for
170 Employee record.
171 p_report_type - This parameter will have the type of the report.
172 eg: 'W2' or 'SQWL'
173 p_format - This parameter will have the format to be printed
174 on W2. eg:'MMREF'
175 p_record_name - This parameter will have the particular
176 record name. eg: RE
177 p_validate - This parameter will check whether it wants to
178 validate the error condition or override the
179 checking.
180 'N'- Override
181 'Y'- Check
182 p_exclude_from_output -
183 This parameter gives the information on
184 whether the record has to be printed or not.
185 'Y'- Do not print.
186 'N'- Print.
187 sp_out_1 - This out parameter returns Employer Location Address
188 sp_out_2 - This out parameter returns Employer Deliver Address
189 sp_out_3 - This out parameter returns Employer City
190 sp_out_4 - This out parameter returns State
191 sp_out_5 - This out parameter returns Zip Code
192 sp_out_6 - This out parameter returns Zip Code Extension
193 sp_out_7 - This out parameter returns Foreign State/Province
194 sp_out_8 - This out parameter returns Locality Company ID
195 (Currently only Philadelphia Local W2 uses this)
196 sp_out_9 - This out parameter returns Foreign Country Code
197 sp_out_10 - This parameter is returns Organization Name
198 */
199 FUNCTION get_mmref_employer_address (
200 p_assignment_id IN number,
201 p_date_earned IN date,
202 p_tax_unit_id IN number,
203 p_effective_date IN varchar2,
204 p_item_name IN varchar2,
205 p_report_type IN varchar2,
206 p_format IN varchar2,
207 p_report_qualifier IN varchar2,
208 p_record_name IN varchar2,
209 p_input_1 IN varchar2,
210 p_input_2 IN varchar2,
211 p_input_3 IN varchar2,
212 p_input_4 IN varchar2,
213 p_input_5 IN varchar2,
214 p_validate IN varchar2,
215 p_exclude_from_output OUT nocopy varchar2,
216 sp_out_1 OUT nocopy varchar2,
217 sp_out_2 OUT nocopy varchar2,
218 sp_out_3 OUT nocopy varchar2,
219 sp_out_4 OUT nocopy varchar2,
220 sp_out_5 OUT nocopy varchar2,
221 sp_out_6 OUT nocopy varchar2,
222 sp_out_7 OUT nocopy varchar2,
223 sp_out_8 OUT nocopy varchar2,
224 sp_out_9 OUT nocopy varchar2,
225 sp_out_10 OUT nocopy varchar2
226 ) RETURN VARCHAR2;
227 --{
228 -- This record structure is used for Person/Employee Address
229 -- MMREF Reporting and YE reports would use this record data type
230 --
231 TYPE person_name_address IS RECORD (
232 full_name per_all_people_f.full_name%type := NULL,
233 employee_number per_all_people_f.employee_number%type := NULL,
234 addr_line_1 per_addresses.address_line1%type := NULL,
235 addr_line_2 per_addresses.address_line2%type := NULL,
236 addr_line_3 per_addresses.address_line3%type := NULL,
237 city per_addresses.town_or_city%type := NULL,
238 province_state varchar2(240) := NULL,
239 postal_code varchar2(240) := NULL,
240 country varchar2(240) := NULL,
241 country_name varchar2(240) := NULL,
242 region_1 per_addresses.region_1%type := NULL,
243 region_2 per_addresses.region_2%type := NULL,
244 valid_address varchar2(3) := 'N');
245 --
246 -- Function to get Person/Employee Address
247 --
248 FUNCTION GET_PERSON_NAME_ADDRESS(
249 p_report_type IN varchar2,
250 p_person_id IN number,
251 p_assignment_id IN number,
252 p_period_end_date IN date,
253 p_effective_date IN date,
254 p_validate IN varchar2,
255 p_source_type IN varchar2)
256 RETURN person_name_address;
257 --}
258 --{
259 -- This record structure is used for Organization Address
260 -- MMREF Reporting and YE reports would use this record data type
261 --
262 TYPE organization_name_address IS RECORD (
263 org_name hr_organization_units.name%type := NULL,
264 addr_line_1 hr_locations_all.address_line_1%type := NULL,
265 addr_line_2 hr_locations_all.address_line_2%type := NULL,
266 addr_line_3 hr_locations_all.address_line_3%type := NULL,
267 city hr_locations_all.town_or_city%type := NULL,
268 province_state varchar2(240) := NULL,
269 postal_code varchar2(240) := NULL,
270 country varchar2(240) := NULL,
271 region_1 hr_locations_all.region_1%type := NULL,
272 region_2 hr_locations_all.region_2%type := NULL,
273 valid_address varchar2(3) := 'N');
274 --
275 -- Function to get Organization/Employer Address
276 --
277 FUNCTION GET_ORGANIZATION_NAME_ADDRESS(
278 p_organization_id IN number,
279 p_effective_date IN date,
280 p_validate IN varchar2,
281 p_source_type IN varchar2)
282 RETURN organization_name_address;
283 --}
284 --{
285 --
286 -- Function to get Concurrent Program Parameter Value
287 --
288 FUNCTION GET_CPROG_PARAMETER_VALUE(
289 p_mag_payroll_action_id IN varchar2, -- Context
290 p_parameter_name IN varchar2)
291 RETURN varchar2;
292 --}
293 --
294 --BEGIN
295 --hr_utility.trace_on(null,'GETITEMDATA');
296 END pay_us_get_item_data_pkg;
297 --End of Package Specification
[Home] [Help]