1 PACKAGE PAY_US_MMREF_LOCAL_XML AUTHID CURRENT_USER AS
2 /* $Header: payusw2mmref1xml.pkh 120.3.12020000.2 2012/10/30 15:16:51 pkoduri ship $ */
3
4 /*
5 ===========================================================================+
6 | Copyright (c) 1993 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +============================================================================+
10 Name
11 pay_us_mmref_local_xml
12 File
13 payusw2mmref1xml.pkh
14
15 Purpose
16
17 The purpose of this package is to support the generation of XML for the process
18 Local W-2 Generic MMREF-1. This package includes all the cursors, procedures and functions
19 used to comply with the payroll CORE multi-thtread enhancement architecture.
20
21 Currently this is not meant for any specific locality magnetic tape.
22
23 Notes
24 The generation of each magnetic tape report is a two stage process i.e.
25 1. Check if the year end pre-processor has been run for all the GREs. If not, then error
26 out without processing further.
27 2. Create a payroll action for the report. Identify all the assignments to be reported and record
28 an assignment action against the payroll action for each one of them.
29 3. Run the "Local W-2 Generic MMREF-1 XML" process to use this package.
30
31
32 History
33 Date Author Verion Bug Details
34 ============================================================================
35 07-NOV-2006 PPANDA 115.0 Initial Version Created
36 02-JAN-2009 SVANNIAN 115.3 Changed the local_w2_xml_employee cursor to pick
37 up employees with only SD taxes also.
38 New Procedure added to check for NON PA Earnings/Withheld.
39 28-OCT-2010 asgugupt 115.4 Modified cursor local_w2_xml_employee
40 30-OCT-12 pkoduri 115.5 14286448 Corrections for GRE name length issue.
41 ============================================================================
42 */
43
44 -- 'level_cnt' will allow the cursors to select function results,
45 -- whether it is a standard fuction such as to_char or a function
46 -- defined in a package (with the correct pragma restriction).
47
48 level_cnt NUMBER;
49
50 -- Sets up the tax unit context for the Submitter
51
52 /* Context and Parameter Set in the cursor are
53
54 Context :
55 --------------------------------------
56 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
57 TAX_UNIT_ID - Submitter's Tax Unit ID
58 ASSIGNMENT_ID - Required for call to function - context not used
59 in the for Submitter
60 DATE EARNED - Always set to Effective date ie. in this case
61 for Mag tapes to 31-DEC-YYYY, in case of SQWL
62 this will be diffrent.
63 Parameters :
64 TRANSFER_2678_FILER
65 -- Following two parameters added for New locality
66 TRANSFER_LOCALITY_CODE
67 TRANSFER_STATE_CODE
68 */
69
70 /* Transmitter for the Local Megnetic Media in MMREF Format */
71
72 CURSOR local_w2_xml_transmitter
73 IS
74 SELECT 'PAYROLL_ACTION_ID=P', PPA.payroll_action_id,
75 'TR_TAX_UNIT_ID=P' , HOI.organization_id,
76 'TR_DATE_EARNED=P', PPA.effective_date,
77 'TRANSFER_2678_FILER=P', HOI.org_information8,
78 'BUSINESS_GROUP_ID=P', PPA.business_group_id,
79 'TRANSFER_LOCALITY_CODE=P',
80 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
81 'LC'),
82 'TRANSFER_STATE_CODE=P', substr(sr.jurisdiction_code,1,2),
83 'ROOT_XML_TAG=P', '<LOCAL_W2_EXTRACT>'
84 FROM pay_state_rules SR,
85 hr_organization_information HOI,
86 pay_payroll_actions PPA,
87 pay_payroll_actions PPA1
88 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
89 ('TRANSFER_PAYROLL_ACTION_ID')
90 AND ppa1.effective_date = ppa.effective_date
91 AND ppa1.report_qualifier = 'LOCAL'
92 AND HOI.organization_id =
93 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
94 AND SR.state_code =
95 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
96 AND HOI.org_information_context = 'W2 Reporting Rules'
97 AND PPA.report_type = 'YREND'
98 AND HOI.ORGANIZATION_ID =
99 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=')
100 + length('TRANSFER_GRE='))
101 AND to_char(PPA.effective_date,'YYYY') =
102 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
103 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
104
105 /* Context and Parameter Set in the cursor are
106 Parameters :
107 ----------------------------------------------------
108 TAX_UNIT_ID - Tax Unit ID of GRE
109 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
110 TAX_UNIT_NAME - Name of GRE
111 */
112 --
113 CURSOR local_w2_xml_Employer
114 IS
115 SELECT DISTINCT
116 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
117 'TAX_UNIT_ID=P' , AA.tax_unit_id,
118 'TAX_UNIT_NAME=P', substr(hou.name,1,80) -- Bug# 14286448 Gre length issue
119 FROM hr_all_organization_units hou,
120 pay_payroll_actions ppa,
121 pay_assignment_actions aa
122 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
123 ('TRANSFER_PAYROLL_ACTION_ID')
124 AND ppa.report_type = 'YREND'
125 AND to_char(ppa.effective_date, 'YYYY') =
126 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
127 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
128 AND aa.tax_unit_id = substr(ppa.legislative_parameters,
129 instr(ppa.legislative_parameters,
130 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
131 AND hou.organization_id = AA.tax_unit_id
132 ORDER BY substr(hou.name,1,80); -- Bug# 14286448 Gre length issue
133 --
134 --
135 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
136 -- for an employee. The date_earned context is set to be the least of the
137 -- end of the period being reported and the maximum end date of the
138 -- assignment. This ensures that personal information ie. name etc... is
139 -- current relative to the period being reported on.
140 --
141 CURSOR local_w2_xml_employee
142 IS
143 SELECT DISTINCT
144 'TRANSFER_ACT_ID=P', AA.assignment_action_id
145 FROM ff_archive_items FAI,
146 ff_contexts FC, -- JD
147 ff_database_items FDI,
148 ff_archive_item_contexts FAIC,
149 per_all_people_f PE,
150 per_all_assignments_f SS,
151 pay_action_interlocks AI,
152 pay_assignment_actions AA1, -- for YE Archiver Assignment Actions
153 pay_assignment_actions AA,
154 pay_payroll_actions PA
155 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
156 ('TRANSFER_PAYROLL_ACTION_ID')
157 AND AA.payroll_action_id = PA.payroll_action_id
158 AND AI.locking_action_id = AA.assignment_action_id
159 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
160 ('TAX_UNIT_ID')
161 AND SS.assignment_id = AA.assignment_id
162 AND PE.person_id = SS.person_id
163 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
164 BETWEEN SS.effective_start_date and SS.effective_end_date
165 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
166 BETWEEN PE.effective_start_date and PE.effective_end_date
167 AND AI.locked_action_id = fai.context1
168 AND AI.locked_action_id = AA1.assignment_action_id
169 AND fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
170 AND fdi.user_entity_id = fai.user_entity_id
171 AND faic.archive_item_id = fai.archive_item_id
172 AND fc.context_name = 'JURISDICTION_CODE'
173 AND faic.context_id = fc.context_id
174 AND substr(rtrim(ltrim(faic.context)),1,2) =
175 pay_magtape_generic.get_parameter_value('TRANSFER_STATE_CODE')
176 AND value <> '0'
177 AND ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') = 'NULL'
178 OR
179 ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') <> 'NULL'
180 AND EXISTS
181 ( SELECT '1'
182 FROM pay_us_city_tax_info_f puctif
183 WHERE substr(rtrim(ltrim(faic.context)),1,2)||'-000-'||substr(rtrim(ltrim(faic.context)),8,4) =
184 substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4)
185 AND puctif.effective_start_date < PA.effective_date
186 AND puctif.effective_end_date >= PA.effective_date
187 AND substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4) =
188 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2) ||'-000-'||
189 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),8,11)
190 )
191 )
192 )
193 union
194 SELECT DISTINCT
195 'TRANSFER_ACT_ID=P', AA.assignment_action_id
196 FROM ff_archive_items FAI,
197 ff_contexts FC, -- JD
198 ff_database_items FDI,
199 ff_archive_item_contexts FAIC,
200 per_all_people_f PE,
201 per_all_assignments_f SS,
202 pay_action_interlocks AI,
203 pay_assignment_actions AA1, -- for YE Archiver Assignment Actions
204 pay_assignment_actions AA,
205 pay_payroll_actions PA
206 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
207 ('TRANSFER_PAYROLL_ACTION_ID')
208 AND AA.payroll_action_id = PA.payroll_action_id
209 AND AI.locking_action_id = AA.assignment_action_id
210 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
211 ('TAX_UNIT_ID')
212 AND SS.assignment_id = AA.assignment_id
213 AND PE.person_id = SS.person_id
214 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
215 BETWEEN SS.effective_start_date and SS.effective_end_date
216 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
217 BETWEEN PE.effective_start_date and PE.effective_end_date
218 AND AI.locked_action_id = fai.context1
219 AND AI.locked_action_id = AA1.assignment_action_id
220 AND fdi.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
221 AND fdi.user_entity_id = fai.user_entity_id
222 AND faic.archive_item_id = fai.archive_item_id
223 AND fc.context_name = 'JURISDICTION_CODE'
224 AND faic.context_id = fc.context_id
225 AND substr(rtrim(ltrim(faic.context)),1,2) =
226 pay_magtape_generic.get_parameter_value('TRANSFER_STATE_CODE')
227 AND value <> '0'
228 AND ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') = 'NULL'
229 OR
230 ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') <> 'NULL'
231 AND EXISTS
232 ( SELECT '1'
233 from PAY_US_CITY_SCHOOL_DSTS puctif
234 WHERE
235 puctif.state_code = pay_magtape_generic.get_parameter_value('TRANSFER_STATE_CODE')
236 and puctif.state_code ||'-'||
237 puctif.county_code || '-'|| puctif.city_code = pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE')
238 and pay_magtape_generic.get_parameter_value('TRANSFER_STATE_CODE') || '-'|| puctif.school_dst_code = ltrim(rtrim(faic.context))
239 )
240 )
241 )
242 ;
243
244 /* CURSOR local_w2_xml_employee
245 IS
246 SELECT DISTINCT
247 'TRANSFER_ACT_ID=P', AA.assignment_action_id,
248 'YE_ASSIGNMENT_ACTION_ID=P', AA1.assignment_action_id, -- YREND assignment action
249 'YE_TAX_UNIT_ID=P', AA1.TAX_UNIT_ID,
250 'EE_ASSIGNMENT_ID=P', AA.assignment_id,
251 'EE_DATE_EARNED=P', pay_magtape_generic.date_earned(PA.effective_date,
252 AA.assignment_id),
253 'EE_LOCALITY_JD_CODE=P', substr(ltrim(rtrim(faic.context)),1,2)||'-000-'||substr(ltrim(rtrim(faic.context)),8,4)
254 FROM ff_archive_items FAI,
255 ff_contexts FC, -- JD
256 ff_database_items FDI,
257 ff_archive_item_contexts FAIC,
258 per_all_people_f PE,
259 per_all_assignments_f SS,
260 pay_action_interlocks AI,
261 pay_assignment_actions AA1, -- for YE Archiver Assignment Actions
262 pay_assignment_actions AA,
263 pay_payroll_actions PA
264 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
265 ('TRANSFER_PAYROLL_ACTION_ID')
266 AND AA.payroll_action_id = PA.payroll_action_id
267 AND AI.locking_action_id = AA.assignment_action_id
268 AND SS.assignment_id = AA.assignment_id
269 AND PE.person_id = SS.person_id
270 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
271 BETWEEN SS.effective_start_date and SS.effective_end_date
272 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
273 BETWEEN PE.effective_start_date and PE.effective_end_date
274 AND AI.locked_action_id = fai.context1
275 AND AI.locked_action_id = AA1.assignment_action_id
276 AND fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
277 AND fdi.user_entity_id = fai.user_entity_id
278 AND faic.archive_item_id = fai.archive_item_id
279 AND fc.context_name = 'JURISDICTION_CODE'
280 AND faic.context_id = fc.context_id
281 AND substr(rtrim(ltrim(faic.context)),1,2) =
282 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2)
283 AND value <> '0'
284 AND ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') IS NULL
285 OR ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') IS NOT NULL
286 AND EXISTS
287 ( SELECT '1'
288 FROM pay_us_city_tax_info_f puctif
289 WHERE substr(rtrim(ltrim(faic.context)),1,2)||'-000-'||substr(rtrim(ltrim(faic.context)),8,4) =
290 substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4)
291 AND puctif.effective_start_date < PA.effective_date
292 AND puctif.effective_end_date >= PA.effective_date
293 AND substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4) =
294 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2) ||'-000-'||
295 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),8,11)
296 )
297 )
298 );
299 */
300
301 CURSOR local_w2_xml_curr_act_id IS
302 SELECT 'TRANSFER_ACT_ID=P',
303 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
304 FROM DUAL;
305
306 CURSOR GET_XML_VER IS
307 SELECT 'ROOT_XML_TAG=P',
308 '<LOCAL_MAG>',
309 'PAYROLL_ACTION_ID=P',
310 pay_magtape_generic.get_parameter_value(
311 'TRANSFER_PAYROLL_ACTION_ID')
312 FROM dual;
313
314 FUNCTION bal_db_item
315 (
316 p_db_item_name VARCHAR2
317 ) RETURN NUMBER;
318
319 PROCEDURE get_report_parameters
320 (
321 p_pactid IN NUMBER,
322 p_year_start IN OUT nocopy DATE,
323 p_year_end IN OUT nocopy DATE,
324 p_state_abbrev IN OUT nocopy VARCHAR2,
325 p_state_code IN OUT nocopy VARCHAR2,
326 p_report_type IN OUT nocopy VARCHAR2,
327 p_business_group_id IN OUT nocopy NUMBER,
328 -- Following parameter added for Locality Code
329 p_locality_code IN OUT nocopy VARCHAR2
330 );
331
332 FUNCTION get_balance_value (
333 p_balance_name VARCHAR2,
334 p_tax_unit_id NUMBER,
335 p_state_abbrev VARCHAR2,
336 p_assignment_id NUMBER,
337 p_effective_date DATE
338 ) RETURN NUMBER;
339
340 /****************************************************************************
341 Name : RANGE_CURSOR
342 Description : This procedure prepares range of persons to be processed for process
343 Local YearEnd Interface Extract
344 *****************************************************************************/
345 PROCEDURE range_cursor (
346 p_pactid IN NUMBER,
347 p_sqlstr OUT nocopy VARCHAR2
348 );
349
350
351 PROCEDURE create_assignment_act(
352 p_pactid IN NUMBER,
353 p_stperson IN NUMBER,
354 p_endperson IN NUMBER,
355 p_chunk IN NUMBER );
356
357 CURSOR LOCAL_CURR_ACT_ID IS
358 SELECT 'TRANSFER_ACT_ID=P',
359 pay_magtape_generic.get_parameter_value( 'TRANSFER_ACT_ID' )
360 FROM DUAL;
361
362 CURSOR LOCAL_MAG_ASG_ACT IS
363 SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
364 FROM pay_assignment_actions paa
365 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value(
366 'TRANSFER_PAYROLL_ACTION_ID');
367 --
368 -- Follwing Procedures are used for constructing XML for Submitter or RA Record
369 --
370 PROCEDURE transmitter_record_start;
371
372 PROCEDURE transmitter_record_end;
373
374 --
375 -- Follwing Procedures are used for constructing XML for Employer or RE Record
376 --
377 PROCEDURE local_w2_xml_employer_start;
378
379 PROCEDURE local_w2_xml_employer_end;
380
381 --
382 -- Follwing Procedures are used for constructing XML for Employee
383 --
384 PROCEDURE local_w2_xml_employee_build;
385
386
387 /****************************************************************************
388 Name : WRITE_TO_MAGTAPE_LOB
389 Description : This procedure appends passed BLOB parameter to
390 pay_mag_tape.g_blob_value
391 *****************************************************************************/
392
393 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB);
394
395 /****************************************************************************
396 Name : WRITE_TO_MAGTAPE_LOB
397 Description : This procedure appends passed varchar2 parameter to
398 pay_mag_tape.g_blob_value
399 *****************************************************************************/
400
401 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2);
402
403 FUNCTION GET_PARAMETER(name IN VARCHAR2,
404 parameter_list IN VARCHAR2) RETURN VARCHAR2;
405
406 PROCEDURE local_non_pa_emp_data(p_pactid IN varchar2,
407 p_assignment_id IN varchar2 ,
408 on_visa in out nocopy varchar2,
409 non_pa_res in out nocopy varchar2 ,
410 p_reporting_year In varchar2);
411
412 END pay_us_mmref_local_xml;
[Home] [Help]