1 package pay_us_magtape_reporting AUTHID CURRENT_USER as
2 /* $Header: pyusmrep.pkh 115.26 2003/08/12 11:31:52 fusman ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_magtape_reporting
10
11 Purpose
12
13 The purpose of this package is to support the generation of magnetic tape
14 reports for US legilsative requirements. Specifically this covers federal
15 and state W2's and also State Quarterly Wage Listing's.
16
17 Notes
18
19 The generation of each magnetic tape report is a two stage process i.e.
20
21 1. Create a payroll action identifying the magnetic tape report being
22 generated. Populate a set of assignment actions with each one
23 identifying a person to be included in the report.
24
25 2. Submit a request to run the generic magnetic tape process which will
26 drive off the data created in stage one. This will result in the
27 production of a structured ascii file which can be transferred to
28 magnetic tape and sent to the relevant authority.
29
30 History
31 ----------------------------------------------------------------------
32 Date | Author | Ver | Remarks
33 -----------------------------------------------------------------------
34 10-Feb-1995 J.S.Hobbs 40.0 Created.
35
36 22 Jun 1995 allee Change pre_payment_id ->
37 chunk_number
38 04 Aug 1995 ALLEE added level_cnt NUMBER
39
40 29 Sep 1995 allee Changed chunk_number ->
41 Serial Number. Although the serial
42 number is a varchar2, ORACLE
43 implicitly does the data conversion.
44
45 09 Jan 1996 allee Changed serial_number -> tax_unit_id.
46 Sunil said it was in CASE.
47
48 11 Feb 1996 allee Made run_magtape and lookup_format
49 public Tuned the SQWL_employer cursor.
50 Made bal_db_item public for the
51 resubmission routine.
52
53 26 Feb 1996 allee Fixed #343239 by adding to the order by
54 clause in the employee level cursor.
55 17-APR-1998 bhoman Changes made to support SQWL
56 diskette reporting.
57 08-AUG-1998 vmehta Changed cursors for ohio/indiana W2
58 to make them
59 date tracked compliant.
60 Added new cursor to handle highly
61 compensated people for State W2s.
62 17-aug-1998 vmehta Changed cursors TIB4_TRANSMITTER and
63 OHSTW2_SUPPLEMENTAL to set up the
64 TRANSFER_SCHOOL_DISTRICT parameter to
65 get the 'OH' school_district_code.
66 30-OCT-1999 rpotnuru bug 976472.
67
68 03-DEC-1999 rpotnuru 110.5 bugs 1095096, 1085774.
69 Removed serial_number is null check
70 from sqwl_employeer and
71 sqwl_employee cursors. In case of
72 NYSQWL this column is populated
73 with 1 if the employee doesnt have
74 QTD balances for 4th qtr
75 while generating 4th qtr New York
76 SQWL report.
77 10-FEB-2000 ashgupta 40.27 Added code to take care of City of
78 Oakland Multi Wage Plan Changes. Enh
79 Req 1063413
80 16-MAR-2000 ashgupta 40.28 changed the sqwl_employer_s cursor
81 so as to select ' ' instead on null.
82 This has to be done since SQWL report
83 was failing on r11 database if null
84 was used. So to make both r10.7 and r11
85 aligned this change was done. Otherwise
86 null work fine in r10.7
87 12-JUN-2000 asasthan 115.5 in sync till Q2 2000 and includes
88 fnd_date changes
89
90 19-SEP-2001 tmehra 40.31 Changing sqwl_employee_s cursor to
91 remove TRANSFER_WAGE_PLAN parameter.
92 This parameter is now set in Employer
93 Formula and is passed on to the
94 Employee formula.
95
96 25-FEB-2002 asasthan 115.7 Modified sqwl_transmitter cursor
97 for performance Bug 2176726.
98 Three unions have been removed.
99 # As "FED" report_qualifier
100 is not true for SQWLs this condition
101 was removed..
102 # Also Transmitter is always
103 mandatory for SQWLs so the join
104 for null transmitter has been removed
105
106 04-MAR-2002 asasthan 115.8 Added New Cursors Bug 2103126
107 mmrf_sqwl_submitter cursor
108 mmrf_sqwl_employer cursor
109 mmrf_sqwl_employee cursor
110 16-MAY-2002 asasthan 115.9 Added business_group_id as
111 a paramter to the mmrf_sqwl_transmitter
112 cursor. This was required by funtion.
113 16-JUL-2002 sodhingr 115.10 Added RULE hint to the cursor
114 sqwl_employee for bug 2464463.
115 16-JUL-2002 sodhingr 115.10 Added RULE hint to the cursor
116 mmrf_sqwl_employee for bug 2464463
117 (performance issue for Georgia).
118 18-JUL-2002 sodhingr 115.12,13 Added RULE hint to the cursor
119 mmrf_sqwl_employee,sqwl_employee_s,
120 sqwl_employee_m for bug 2464463
121 22-AUG-2002 rpinjala 115.14 Changed the mmrf_sqwl_transmitter cursor
122 to add the transfer_report_category which is used
123 in the MMRF_SUBMITTER_DUMMY_SQWL so that header for
124 a02, a03 for CA RTM has diff. names.
125 28-AUG-2002 sodhingr 115.15 Removed the RULE hint from the following cursors
126 and remove the call to pay_magtape_generic.date_earned
127 instead checking the same condition in a subquery instead
128 of function call to improve the performance issue reported
129 in bug 2464463.
130 mmrf_sqwl_employee,sqwl_employee,sqwl_employee_s,sqwl_employee_m
131 10-SEP-2002 sodhingr 115.16 Changed the condition for effective start date as <= instead of just
132 equal to (=) in the following cursors.
133 mmrf_sqwl_employee,sqwl_employee,sqwl_employee_s,sqwl_employee_m
134 11-SEP-2002 sodhingr 15.17 Added the condition :
135 AND LEAST(SS.effective_end_date, PA.effective_date)
136 between PE.effective_start_date and PE.effective_end_date
137 for bug 2464463
138 01-OCT-2002 sodhingr 15.18 For bug 2604618,changed the following cursors.
139 mmrf_sqwl_employee,sqwl_employee_s,sqwl_employee_m
140 10-NOV-2002 sodhingr 115.19 Added new cursor mmrf_nysqwl_employer for NY MMREF as the
141 employers should be sorted by EIN not by the name(bug 2451245)
142 10-FEB-2002 sodhingr 115.20 changed the cursor mmrf_sqwl_transmitter, to remove the dependency
143 on W2 reporting rules for SQWL, bug 2752145
144 19-MAR-2003 sodhingr 115.21 changed the cursor sqwl_employee_jurisdiction to get the data from
145 archive tables instead of live table. This is changed for
146 bug 2852640
147 15-MAY-2003 fusman 115.22 changed the cursor sqwl_employer to add business_group_id
148
149 15-MAY-2003 tmehra 115.23 Removed the sqwl_employer_s and sqwl_employee_s cursors
150 as part of the RTS and RTM category merger for CA.
151 06-AUG-2003 fusman 115.24 3094891. Moved all the sqwl cursors to pay_us_sqwl_archive package header.
152 ============================================================================*/
153
154
155 -----------------------------------------------------------------------------
156 -- Name
157 -- lookup_format
158 -- Purpose
159 -- Find the format to be applied when generating the report.
160 -- Arguments
161 -- Notes
162 -- SQWLD - p_media_type parameter
163 -----------------------------------------------------------------------------
164 --
165 function lookup_format
166 (
167 p_period_end in date,
168 p_report_type in varchar2,
169 p_state in varchar2,
170 p_media_type in varchar2 := NULL
171 ) return varchar2;
172
173 -----------------------------------------------------------------------------
174 -- Name
175 -- bal_db_item
176 -- Purpose
177 -- Given the name of a balance DB item as would be seen in a fast formula
178 -- it returns the defined_balance_id of the balance it represents.
179 -- Arguments
180 -- Notes
181 -- A defined +balance_id is required by the PLSQL balance function.
182 -----------------------------------------------------------------------------
183 --
184 function bal_db_item
185 (
186 p_db_item_name varchar2
187 ) return number;
188
189 -----------------------------------------------------------------------------
190 -- Name
191 -- redo
192 -- Purpose
193 -- Calls the procedure run_magtape directly from SRS. This procedure
194 -- handles the error buffer and return code interface with SRS.
195 -- We are going to derive all the parameters from the vi
196 -- Arguments
197 -- Notes
198 -----------------------------------------------------------------------------
199 procedure redo
200 (
201 errbuf out nocopy varchar2,
202 retcode out nocopy number,
203 p_payroll_action_id in varchar2
204 );
205 --
206 -----------------------------------------------------------------------------
207 -- Name
208 -- run_magtape
209 -- Purpose
210 -- Submits the magnetic tape process to be run by the concurrent manager.
211 -- We also define the name of the output and the format here
212 -- Arguments
213 -- Notes
214 -- SQWLD - p_media_type parameter
215 -----------------------------------------------------------------------------
216 --
217
218
219 procedure run_magtape
220 (
221 p_effective_date date,
222 p_report_type varchar2,
223 p_payroll_action_id varchar2,
224 p_state varchar2,
225 p_reporting_year varchar2,
226 p_reporting_quarter varchar2,
227 p_trans_legal_co_id varchar2,
228 p_media_type in varchar2 := NULL
229 );
230
231 -----------------------------------------------------------------------------
232 -- Name
233 -- run
234 -- Purpose
235 -- This is the main procedure responsible for generating the list of
236 -- assignment actions and then submitting the request to produce the
237 -- magnetic tape report.
238 -- Arguments
239 -- errbuf - error message string passed back to SRS.
240 -- retcode - error code passed back to SRS ie.
241 -- 0 - Success
242 -- 1 - Warning
243 -- 2 - Error
244 -- p_business_group_id - business group the user is running under when the
245 -- report is generated.
246 -- p_report_type - either 'W2' or 'SQWL'
247 -- p_state - either 'FED' for federal or the state code of a
248 -- state eg. PA for Pennsylvania
249 -- p_quarter - identifies the quarter being reported eg. 03 is
250 -- the 1st quarter. This is defaulted to '12' for
251 -- the W2 Report
252 -- p_year - identifies the year being reported on.
253 -- p_trans_legal_co_id - identifies the Transmitter Tax Unit.
254 -- Notes
255 -- This procedure is invoked from the SRS screens.
256 -- SQWLD - p_media_type parameter
257 -----------------------------------------------------------------------------
258 --
259 procedure run
260 (
261 errbuf out nocopy varchar2,
262 retcode out nocopy number,
263 p_business_group_id in number,
264 p_report_type in varchar2,
265 p_state in varchar2,
266 p_quarter in varchar2,
267 p_year in varchar2,
268 p_trans_legal_co_id in number,
269 p_media_type in varchar2 := NULL
270 );
271 --
272 end pay_us_magtape_reporting;
[Home] [Help]