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