1 PACKAGE pay_us_fls_reporting_pkg AS
2 /* $Header: pyusflsp.pkh 120.1.12000000.1 2007/01/18 02:26:20 appldev noship $*/
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_fls_reporting_pkg
21
22 Description : Generate FLS periodic magnetic reports according to
23 FLS requirements.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ------ ------- -----------
31 20-JAN-2006 asasthan 115.12 4969824 Changed action cursor
32 Now uses person_id instead of asg_id
33 29-SEP-2004 ahanda 115.11 4092186 Changed cursor fls_header_wages_rec
34 for MA
35 02-SEP-2004 meshah 115.10 Fixed gscc error
36 18-AUG-2004 ahanda 115.9 3832605 Changed the driving cursor
37 and added a new function -
38 get_jurisdiction_name.
39 18-FEB-2004 ssmukher 115.7 3343962 Performance Changes
40 06-FEB-2002 ahanda 115.6 Added dbdrv command.
41 31-JUL-2001 ahanda 115.5 Added conditions to check for
42 category and tax_unit_id in
43 cursor fls_header_wages_rec.
44 18-APR-2001 ahanda 115.4 Modified cursor fls_ein_jd_values
45 to pass FEIN without /, - and space.
46 02-APR-2001 ahanda 115.3 Modified functions
47 - get_tax_exists
48 - get_fls_agency_code
49 Removed cursor
50 - fls_wages_rec
51 Renamed cursor fls_header_rec
52 to fls_header_wages_rec
53 12-MAR-2001 asasthan 115.2 Modified functions:
54 - get_fls_agency_code
55 - get_fls_tax_type_values
56 Modified cursor fls_header_rec to
57 have another parameter
58 TRANSFER_RESIDENT_JD.
59 22-FEB-2001 ahanda 115.1 Changed cursors to add function
60 call.
61 28-JAN-2001 ahanda 115.0 Created.
62
63 *******************************************************************/
64
65 /*******************************************************************
66 ** Functions used in the FLS Tape Procss
67 ** Defining the Functions before cursors as the functions are also
68 ** used in cursors.
69 *******************************************************************/
70
71 /*******************************************************************
72 ** Function to check if the Tax Exists for the Jurisdiction
73 ** This function is used for State, County, City and School
74 *******************************************************************/
75 FUNCTION get_tax_exists( p_jurisdiction_code in varchar2
76 ,p_effective_date in varchar2
77 ,p_tax_type in varchar2
78 ,p_tax_type_resp in varchar2 default NULL
79 )
80 RETURN VARCHAR2;
81
82 /*******************************************************************
83 ** Function gets the Organization Short Name which is written in
84 ** in the Header Record.
85 ** The return values in the function function for Org4 and Org5
86 ** are hard coded for Oracle In-House Implementation. The function
87 ** need to be changed to gets the values from the Org Developer DF
88 ** once it is implemented.
89 *******************************************************************/
90 FUNCTION get_fls_org_information(
91 p_tax_unit_id in number
92 ,p_payroll_action_id in number
93 ,p_effective_date in varchar2
94 )
95 RETURN VARCHAR2;
96
97 /*******************************************************************
98 ** Function gets the FLS agency codes for the passed Vertex
99 ** Jurisdiction code and effective date.
100 ** The Agency Codes are stored in the JIT table:
101 ** - PAY_US_FEDERAL_TAX_INFO_F
102 ** - PAY_US_STATE_TAX_INFO_F
103 ** - PAY_US_COUNTY_TAX_INFO_F
104 ** - PAY_US_CITY_TAX_INFO_F
105 *******************************************************************/
106 FUNCTION get_fls_agency_code( p_jurisdiction_code in varchar2
107 ,p_effective_date in varchar2
108 ,p_resident_jurisdiction in varchar2
109 ,p_tax_type_code in varchar2
110 )
111 RETURN VARCHAR2;
112
113 /*******************************************************************
114 ** Function gets the Jurisdiction Code Name for the passed
115 ** Jurisdiction code
116 *******************************************************************/
117 FUNCTION get_jurisdiction_name(p_jurisdiction_code in varchar2
118 ,p_resident_jurisdiction in varchar2
119 )
120 RETURN VARCHAR2;
121
122 /*******************************************************************
123 ** This function populates the PL/SQL table with the values for
124 ** all Jurisdictions in a GRE. This PL/SQL table is then used to
125 ** for getting the values in the Tape.
126 *******************************************************************/
127 FUNCTION get_fls_jd_values( p_tax_unit_id in number
128 ,p_payroll_action_id in number
129 )
130 RETURN NUMBER;
131
132 /*******************************************************************
133 ** The function is used to retreive the values from the PL/SQL
134 ** table for the passed FLS tax Type and Jurisdiction Code.
135 *******************************************************************/
136 FUNCTION get_fls_tax_type_values(
137 p_tax_type in varchar2
138 ,p_jurisdiction in varchar2
139 ,p_resident_jurisdiction in varchar2
140 )
141 RETURN VARCHAR2;
142
143
144 /*******************************************************************/
145 -- 'level_cnt' will allow the cursors to select function results,
146 -- whether it is a standard fuction such as to_char or a function
147 -- defined in a package (with the correct pragma restriction).
148 level_cnt NUMBER;
149 /******************************************************************
150 ** Driving Cursors for Flat file Generation
151 ******************************************************************/
152
153 /*******************************************************************
154 ** The cursor retreives all the GRE which should not be in the Tape.
155 *******************************************************************/
156 cursor fls_ein_jd_values is
157 select 'TRANSFER_TAX_UNIT_ID=P', hoi.organization_id,
158 'TRANSFER_TAX_EIN=P', replace(
159 replace(
160 replace(hoi.org_information1,
161 '-'),
162 '/'),
163 ' '),
164 'TRANSFER_EFFECTIVE_DATE=P', to_char(ppa.effective_date,'MM/DD/YYYY'),
165 'TRANSFER_PAYROLL_ACTION_ID=P', ppa.payroll_action_id
166 from pay_payroll_actions ppa
167 ,hr_organization_information hoi
168 where ppa.payroll_action_id =
169 pay_magtape_generic.get_parameter_value(
170 'TRANSFER_PAYROLL_ACTION_ID')
171 and exists (select 'x' from pay_assignment_Actions paa
172 where paa.payroll_action_id = ppa.payroll_action_id
173 and paa.tax_unit_id = hoi.organization_id
174 )
175 and hoi.org_information_context = 'Employer Identification'
176 ;
177
178 /*******************************************************************
179 ** The cursor retreives all the Jurisdictions which should not be
180 ** in the Tape. The Tax Unit ID and Payroll Action ID are passed
181 ** from the previous cursor.
182 **
183 ** The cursor retreives all the tax Types which should be
184 ** reported in the Tape.
185 ** A function is called for State, County and City to check if the
186 ** tax exists for that Jurisdiction.
187 **
188 ** Lookup US_FLS_TAX_TYPES:
189 ** The Description columns for the lookup stores the following
190 ** information:
191 ** First Char: Order in which the Tax Type needs to be reported
192 ** Next 2 Chars: When the header need to be printed i.e. agency
193 ** changes at the FEDERAL Level
194 ** Next 2 Chars: When the header need to be printed i.e. agency
195 ** changes at the STATE level. This is not required
196 ** as of now but might be implemented for later.
197 ** Next 2 Chars: When the header need to be printed i.e. agency
198 ** changes at the LOCAL level. This is not required
199 ** as of now but might be implemented for later.
200 *******************************************************************/
201 cursor fls_header_wages_rec is
202 select distinct
203 'TRANSFER_JURISDICTION_CODE=P', pai.jurisdiction_code,
204 'TRANSFER_RESIDENT_JD=P',
205 -- If the length is 11 then we have Resident JD in
206 -- action_information30
207 decode(length(ltrim(rtrim(pai.action_information30))),
208 11, pai.action_information30),
209 'TRANSFER_TAX_TYPE_CODE=P', fcl.lookup_code,
210 'TRANSFER_TAX_TYPE_DESC=P', fcl.description
211 from pay_action_information pai,
212 fnd_common_lookups fcl
213 where fcl.lookup_type = 'US_FLS_TAX_TYPES'
214 and pai.action_information_category in ('US FEDERAL',
215 'US STATE',
216 'US COUNTY',
217 'US CITY',
218 'US SCHOOL DISTRICT'
219 )
220 and pai.tax_unit_id = pay_magtape_generic.get_parameter_value(
221 'TRANSFER_TAX_UNIT_ID')
222 and exists (select 'x'
223 from pay_assignment_actions paa
224 where paa.payroll_action_id =
225 pay_magtape_generic.get_parameter_value(
226 'TRANSFER_PAYROLL_ACTION_ID')
227 and paa.tax_unit_id =
228 pay_magtape_generic.get_parameter_value(
229 'TRANSFER_TAX_UNIT_ID')
230 and paa.serial_number = pai.action_context_id
231 )
232 and pay_us_fls_reporting_pkg.get_tax_exists(
233 pai.jurisdiction_code,
234 pay_magtape_generic.get_parameter_value
235 ('TRANSFER_EFFECTIVE_DATE'),
236 fcl.lookup_code) = 'Y'
237 order by pai.jurisdiction_code,
238 decode(length(ltrim(rtrim(pai.action_information30))),
239 11, pai.action_information30),
240 substr(fcl.description,1,1);
241
242
243
244 /*******************************************************************
245 ** The cursor gets EE:R and NR records and ER rows to be reported
246 ** on the Tax Record.
247 *******************************************************************/
248 cursor fls_tax_rec is
249 select 'TRANSFER_PAYMENT_RESPONSIBILITY=P' , 'EE',
250 'TRANSFER_RESIDENCY=P' , 'R'
251 from dual
252 /* Do not pick EE for FUTA if JD is Federal. Need to
253 check JD and Tax Type as FUTA and SUI have the
254 same Tax Type i.e. UI */
255 where pay_us_fls_reporting_pkg.get_tax_exists(
256 pay_magtape_generic.get_parameter_value
257 ('TRANSFER_JURISDICTION_CODE'),
258 pay_magtape_generic.get_parameter_value
259 ('TRANSFER_EFFECTIVE_DATE'),
260 pay_magtape_generic.get_parameter_value
261 ('TRANSFER_TAX_TYPE_CODE'),
262 'EE') = 'Y'
263 UNION ALL
264 select 'TRANSFER_PAYMENT_RESPONSIBILITY=P' , 'ER',
265 'TRANSFER_RESIDENCY=P' , 'R'
266 from dual
267 /* Return ER for all Jurisdiction where Tax Type is
268 not Income Tax, OPT or EIC */
269 where pay_magtape_generic.get_parameter_value
270 ('TRANSFER_TAX_TYPE_CODE') not in ('IT', 'OPT', 'EIC')
271 and pay_us_fls_reporting_pkg.get_tax_exists(
272 pay_magtape_generic.get_parameter_value
273 ('TRANSFER_JURISDICTION_CODE'),
274 pay_magtape_generic.get_parameter_value
275 ('TRANSFER_EFFECTIVE_DATE'),
276 pay_magtape_generic.get_parameter_value
277 ('TRANSFER_TAX_TYPE_CODE'),
278 'ER') = 'Y'
279 UNION ALL
280 select 'TRANSFER_PAYMENT_RESPONSIBILITY=P' , 'EE',
281 'TRANSFER_RESIDENCY=P' , 'NR'
282 from dual
283 /* Check if JD is for local i.e. county_code should not
284 be 000, if <> 000 then return true else check if JD
285 is is school, if yes then retrun false.
286 All other cases return false
287 */
288 where decode(substr(pay_magtape_generic.get_parameter_value
289 ('TRANSFER_JURISDICTION_CODE'),4,3), '000',
290 decode(length(pay_magtape_generic.get_parameter_value
291 ('TRANSFER_JURISDICTION_CODE')), 8, -1, -1),
292 1) = 1
293 and pay_us_fls_reporting_pkg.get_tax_exists(
294 pay_magtape_generic.get_parameter_value
295 ('TRANSFER_JURISDICTION_CODE'),
296 pay_magtape_generic.get_parameter_value
297 ('TRANSFER_EFFECTIVE_DATE'),
298 pay_magtape_generic.get_parameter_value
299 ('TRANSFER_TAX_TYPE_CODE'),
300 'EE') = 'Y'
301 -- UNION ALL
302 -- select 'TRANSFER_PAYMENT_RESPONSIBILITY=P' , 'ER',
303 -- 'TRANSFER_RESIDENCY=P' , 'NR'
304 -- from dual
305 -- /* Return ER/NR for all local JD where Tax Type is
306 -- not Income Tax or EIC */
307 -- where pay_magtape_generic.get_parameter_value
308 -- ('TRANSFER_TAX_TYPE_CODE') not in ('IT', 'EIC')
309 -- and decode(substr(pay_magtape_generic.get_parameter_value
310 -- ('TRANSFER_JURISDICTION_CODE'),4,3), '000',
311 -- decode(length(pay_magtape_generic.get_parameter_value
312 -- ('TRANSFER_JURISDICTION_CODE')), 8, 1, -1),
313 -- 1) = 1
314 ;
315
316
317 /*******************************************************************
318 ** Range Code to pick all the distinct assignment_ids which
319 ** are to be reported.
320 *******************************************************************/
321 PROCEDURE range_cursor( p_payroll_action_id in number
322 ,p_sql_string out nocopy varchar2); -- Bug 3343962
323
324 /*******************************************************************
325 ** Action Creation Code to create assignment actions for all the
326 ** the assignment_ids which are to be reported.
327 *******************************************************************/
328 PROCEDURE action_creation( p_payroll_action_id in number
329 ,p_start_person in number
330 ,p_end_person in number
331 ,p_chunk in number);
332
333
334 /*******************************************************************
335 ** PL/SQL Record to store the archived values.
336 *******************************************************************/
337 TYPE action_info_rec IS RECORD
338 ( jurisdiction_code pay_action_information.jurisdiction_code%type
339 ,action_information1 NUMBER(14,2) := 0
340 ,action_information2 NUMBER(14,2) := 0
341 ,action_information3 NUMBER(14,2) := 0
342 ,action_information4 NUMBER(14,2) := 0
343 ,action_information5 NUMBER(14,2) := 0
344 ,action_information6 NUMBER(14,2) := 0
345 ,action_information7 NUMBER(14,2) := 0
346 ,action_information8 NUMBER(14,2) := 0
347 ,action_information9 NUMBER(14,2) := 0
348 ,action_information10 NUMBER(14,2) := 0
349 ,action_information11 NUMBER(14,2) := 0
350 ,action_information12 NUMBER(14,2) := 0
351 ,action_information13 NUMBER(14,2) := 0
352 ,action_information14 NUMBER(14,2) := 0
353 ,action_information15 NUMBER(14,2) := 0
354 ,action_information16 NUMBER(14,2) := 0
355 ,action_information17 NUMBER(14,2) := 0
356 ,action_information18 NUMBER(14,2) := 0
357 ,action_information19 NUMBER(14,2) := 0
358 ,action_information20 NUMBER(14,2) := 0
359 ,action_information21 NUMBER(14,2) := 0
360 ,action_information22 NUMBER(14,2) := 0
361 ,action_information23 NUMBER(14,2) := 0
362 ,action_information24 NUMBER(14,2) := 0
363 ,action_information25 NUMBER(14,2) := 0
364 ,action_information26 NUMBER(14,2) := 0
365 ,action_information27 NUMBER(14,2) := 0
366 ,action_information28 NUMBER(14,2) := 0
367 ,action_information29 NUMBER(14,2) := 0
368 ,action_information30 VARCHAR2(100)
369 );
370
371 /*******************************************************************
372 ** PL/SQL table of record to store the archived values.
373 *******************************************************************/
374 TYPE action_info_tab IS TABLE OF
375 action_info_rec
376 INDEX BY BINARY_INTEGER;
377
378 ltr_action_info action_info_tab;
379
380 END pay_us_fls_reporting_pkg;
[Home] [Help]