1 package pay_us_mmref_local AUTHID CURRENT_USER as
2 /* $Header: pyusmmle.pkh 120.0.12020000.2 2012/11/22 10:53:59 skchalla ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +============================================================================+
8 Name
9 pay_us_mmref_reporting
10
11 Purpose
12
13 The purpose of this package is to support the generation of local
14 Magnetic media in MMREF - 1 Format. This magnetic tapes are for
15 us legilsative requirements. Currently we supprt the CCA and RITA
16 Reporting.
17
18 Notes
19 The generation of each magnetic tape report is a two stage process i.e.
20 1. Check if the year end pre-processor has been run for all the GREs.
21 If not, then error out without processing further.
22 2. Create a payroll action for the report. Identify all the assignments
23 to be reported and record an assignment action against the payroll
24 action for each one of them.
25 3. Run the generic magnetic tape process which will
26 drive off the data created in stage two. 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
31 History
32 Date Author Verion Bug Details
33 ---------------------------------------------------------------------------
34 22-jan-02 djoshi 115.0 Created
35 04-Nov-02 ppanda 115.1 For locality changes made to
36 get_report_parameters procedure
37 Cursor lc_mmrf_submitter modified to
38 have new parameter for State_code and
39 locality_code
40 15-Nov-02 ppanda 115.2 File is gscc compliant
41 02-Dec-02 ppanda 115.3 Nocopy hint added to OUT and IN OUT parameters
42 28-feb-03 djoshi 115.5 Changed the code for Locals . Cursor
43 OH_LC_EMPLOYEE was changed.
44 22-nov-12 skchalla 115.11 Bug 14286448, Corrections for GRE name length issue
45 ============================================================================*/
46
47 -- 'level_cnt' will allow the cursors to select function results,
48 -- whether it is a standard fuction such as to_char or a function
49 -- defined in a package (with the correct pragma restriction).
50
51 level_cnt NUMBER;
52
53 -- Sets up the tax unit context for the Submitter
54
55 /* Context and Parameter Set in the cursor are
56
57 Context :
58 --------------------------------------
59 TAX_UNIT_ID - Submitter's Tax Unit ID
60 JURISDICTION_CODE - Set to Dummy Value as This is federal Cursor
61 ASSIGNMENT_ID - Required for call to function - context not used
62 in the for Submitter
63 Date Earned - Always set to Effective date ie. in this case
64 for Mag tapes to 31-DEC-YYYY, in case of SQWL
65 this will be diffrent.
66 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
67
68 Parameters :
69 Transfer_HIGH_COUNT
70 TRANSFER_SCHOOL_DISTRICT
71 TRANSFER_COUNTY
72 TRANSFER_2678_FILER
73 -- Following two parameters added for New locality
74 TRANSFER_LOCALITY_CODE
75 TRANSFER_STATE_CODE
76 */
77
78 /* Transmitter for the Local Megnetic Media in MMREF Format */
79
80 CURSOR lc_mmrf_submitter
81 IS
82 SELECT 'TAX_UNIT_ID=C' , HOI.organization_id,
83 'JURISDICTION_CODE=C', SR.jurisdiction_code,
84 'TRANSFER_JD=P', SR.jurisdiction_code,
85 'ASSIGNMENT_ID=C' , '-1',
86 'DATE_EARNED=C', fnd_date.date_to_canonical(ppa.effective_date),
87 'TRANSFER_HIGH_COUNT=P', '0',
88 'TRANSFER_SCHOOL_DISTRICT=P', '-5',
89 'TRANSFER_COUNTY=P', '-1',
90 'TRANSFER_2678_FILER=P', HOI.org_information8,
91 'PAYROLL_ACTION_ID=C', PPA.payroll_action_id,
92 'BUSINESS_GROUP_ID=C',PPA.business_group_id,
93 'TRANSFER_LOCALITY_CODE=P',
94 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
95 'LC'),
96 -- substr(PPA1.legislative_parameters,instr(PPA1.legislative_parameters,'LOCALITY_CODE=')
97 -- + length('LOCALITY_CODE=')),
98 'TRANSFER_STATE_CODE=P', substr(sr.jurisdiction_code,1,2)
99 FROM pay_state_rules SR,
100 hr_organization_information HOI,
101 pay_payroll_actions PPA,
102 pay_payroll_actions PPA1
103 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
104 ('TRANSFER_PAYROLL_ACTION_ID')
105 AND ppa1.effective_date = ppa.effective_date
106 --AND ppa1.report_qualifier = sr.state_code
107 --CPE
108 AND substr(ppa1.report_qualifier,1,2) = sr.state_code
109 AND HOI.organization_id =
110 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
111 AND SR.state_code =
112 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
113 AND HOI.org_information_context = 'W2 Reporting Rules'
114 AND PPA.report_type = 'YREND'
115 AND HOI.ORGANIZATION_ID =
116 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=')
117 + length('TRANSFER_GRE='))
118 AND to_char(PPA.effective_date,'YYYY') =
119 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
120 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
121
122
123
124 /* Context and Parameter Set in the cursor are
125
126 Context :
127 --------------------------------------
128 TAX_UNIT_ID - Tax Unit ID of GRE
129 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
130
131 Parameters :
132 TAX_UNIT_ID - Id of the GRE
133 TAX_UNIT_NAME - Name of GRE
134 */
135
136 --
137 --
138 --
139
140 CURSOR lc_mmrf_employer
141 IS
142 SELECT DISTINCT 'PAYROLL_ACTION_ID=C', ppa.payroll_action_id,
143 'TAX_UNIT_ID=C' , AA.tax_unit_id,
144 'TAX_UNIT_ID=P' , AA.tax_unit_id,
145 'TAX_UNIT_NAME=P' , substr(hou.name,1,80),-- Bug# 14286448 Gre length issue
146 'TRANSFER_EMP_CODE=P', 'R'
147 FROM
148 hr_all_organization_units hou,
149 pay_payroll_actions ppa,
150 pay_assignment_actions AA
151 WHERE AA.payroll_action_id = pay_magtape_generic.get_parameter_value
152 ('TRANSFER_PAYROLL_ACTION_ID')
153 AND ppa.report_type = 'YREND'
154 AND to_char(ppa.effective_date,'YYYY') =
155 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
156 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
157 AND AA.tax_unit_id =
158 substr(ppa.legislative_parameters,
159 instr(ppa.legislative_parameters,
160 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
161 AND hou.organization_id = AA.tax_unit_id
162 order by substr(hou.name,1,80); -- Bug# 14286448 Gre length issue
163
164
165
166 --
167 --
168 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
169 -- for an employee. The date_earned context is set to be the least of the
170 -- end of the period being reported and the maximum end date of the
171 -- assignment. This ensures that personal information ie. name etc... is
172 -- current relative to the period being reported on.
173 --
174
175
176 CURSOR lc_mmrf_employee
177 IS
178 SELECT
179 'ASSIGNMENT_ACTION_ID=C', AI.locked_action_id, -- YREND assignment action
180 'ASSIGNMENT_ID=C', AA.assignment_id,
181 'DATE_EARNED=C',
182 fnd_date.date_to_canonical(pay_magtape_generic.date_earned(PA.effective_date,
183 AA.assignment_id)),
184 'JURISDICTION_CODE=C',
185 pay_magtape_generic.get_parameter_value('TRANSFER_JD'),
186 'YE_ASSIGNMENT_ACTION_ID=P',AI.locked_action_id
187 FROM per_all_people_f PE,
188 per_all_assignments_f SS,
189 pay_action_interlocks AI,
190 pay_assignment_actions AA,
191 pay_payroll_actions PA
192 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
193 ('TRANSFER_PAYROLL_ACTION_ID') AND
194 AA.payroll_action_id = PA.payroll_action_id AND
195 AA.tax_unit_id = pay_magtape_generic.get_parameter_value
196 ('TAX_UNIT_ID') AND
197 AI.locking_action_id = AA.assignment_action_id AND
198 SS.assignment_id = AA.assignment_id AND
199 PE.person_id = SS.person_id AND
200 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
201 SS.effective_start_date and SS.effective_end_date AND
202 pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id) BETWEEN
203 PE.effective_start_date and PE.effective_end_date
204 ORDER BY PE.last_name, PE.first_name, PE.middle_names;
205
206
207 /* RITA and CCA Cursor */
208
209
210 CURSOR oh_lc_employee
211 IS
212 SELECT
213 'JURISDICTION_CODE=C',ltrim(rtrim(faic.context)),
214 'TRANSFER_YE_JURISDICTION_CODE=P',ltrim(rtrim(faic.context)),
215 'TRANSFER_YE_REC_TYPE=P','1'
216 from
217 ff_archive_items fai,
218 ff_contexts fc, -- JD
219 ff_database_items fdi,
220 ff_archive_item_contexts faic, -- JD
221 pay_payroll_actions ppa,
222 pay_assignment_actions paa
223 where
224 paa.assignment_action_id =
225 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID')
226 and paa.assignment_action_id = fai.context1
227 and fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
228 and fdi.user_entity_id = fai.user_entity_id
229 and faic.archive_item_id = fai.archive_item_id
230 and fc.context_name = 'JURISDICTION_CODE'
231 and faic.context_id = fc.context_id
232 and value <> '0'
233 and paa.payroll_action_id = ppa.payroll_action_id
234 and exists
235 (
236 /* Code has to join like becuase puctif is not
237 maintained as truely date tracked table
238 */
239 SELECT '1'
240 FROM pay_us_city_tax_info_f puctif
241 WHERE rtrim(ltrim(faic.context)) = puctif.jurisdiction_code
242 AND puctif.effective_start_date < ppa.effective_date
243 AND puctif.effective_end_date >= ppa.effective_date
244 AND puctif.city_information1 like
245 pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE')||'%'
246 )
247 order by faic.context ;
248
249
250
251 FUNCTION bal_db_item
252 (
253 p_db_item_name VARCHAR2
254 ) RETURN NUMBER;
255
256 PROCEDURE get_report_parameters
257 (
258 p_pactid IN NUMBER,
259 p_year_start IN OUT nocopy DATE,
260 p_year_end IN OUT nocopy DATE,
261 p_state_abbrev IN OUT nocopy VARCHAR2,
262 p_state_code IN OUT nocopy VARCHAR2,
263 p_report_type IN OUT nocopy VARCHAR2,
264 p_business_group_id IN OUT nocopy NUMBER,
265 -- Following parameter added for Locality Code
266 p_locality_code IN OUT nocopy VARCHAR2
267 );
268
269 FUNCTION get_balance_value (
270 p_balance_name VARCHAR2,
271 p_tax_unit_id NUMBER,
272 p_state_abbrev VARCHAR2,
273 p_assignment_id NUMBER,
274 p_effective_date DATE
275 ) RETURN NUMBER;
276
277 FUNCTION preprocess_check
278 (
279 p_pactid NUMBER,
280 p_year_start DATE,
281 p_year_end DATE,
282 p_business_group_id NUMBER,
283 p_state_abbrev VARCHAR2,
284 p_state_code VARCHAR2,
285 p_report_type VARCHAR2
286 ) RETURN BOOLEAN;
287
288 PROCEDURE range_cursor (
289 p_pactid IN NUMBER,
290 p_sqlstr OUT nocopy VARCHAR2
291 );
292
293 PROCEDURE create_assignment_act(
294 p_pactid IN NUMBER,
295 p_stperson IN NUMBER,
296 p_endperson IN NUMBER,
297 p_chunk IN NUMBER );
298
299 FUNCTION check_er_data (
300 p_pactid NUMBER,
301 p_ein_user_id NUMBER
302 ) RETURN varchar2;
303
304
305 FUNCTION check_state_er_data (
306 p_pactid NUMBER,
307 p_tax_unit NUMBER,
308 p_jurisdictions varchar2
309 ) RETURN varchar2;
310
311
312 PROCEDURE archive_eoy_data(
313 p_pactid IN NUMBER,
314 p_tax_id IN NUMBER );
315
316
317 PROCEDURE archive_state_eoy_data(
318 p_pactid IN NUMBER,
319 p_tax_id IN NUMBER,
320 p_state_code IN VARCHAR2);
321
322 FUNCTION check_state_data (
323 p_payroll_action_id NUMBER,
324 p_transfer_state varchar2
325 ) RETURN varchar2;
326
327
328 END pay_us_mmref_local;
[Home] [Help]