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