1 PACKAGE PAY_US_PSD_XML AUTHID CURRENT_USER AS
2 /* $Header: payuspsdxml.pkh 120.3.12020000.8 2012/12/11 19:32:48 skchalla noship $ */
3
4 /*
5 ===========================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +============================================================================+
10 Name
11 pay_us_psd_xml
12 File
13 payuspsdxml.pkh
14
15 Purpose
16
17 The purpose of this package is to support the generation of XML for the process
18 PSD Periodic Wage Listing Report. 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 four stage process i.e.
25 1. Create a payroll action for the report.
26 2. Select all the GREs that lie under that business group and whose employees have PSD tax
27 deductions and check if 'PSD Reporting Rules' are defined for all such GREs.
28 Also check if these PSD Reporting Rules contain all the employer PSD codes of the employees
29 who have PSD tax deductions. If not then ist all such GREs and PSD codes in the log file.
30 3. Identify all the assignments to be reported and record an assignment action against
31 the payroll action for each one of them.
32 3. Run the "PSD Periodic Wage Listing Report" process to use this package.
33
34
35 History
36 Date Author Verion Bug Details
37 ============================================================================
38 22-jun-2012 PRACAGRA 115.0 11712075 Initial Version Created
39 06-jun-2012 PRACAGRA 115.1 11712075 Added changes to include TCD
40 Code in report parameters.
41 09-jun-2012 PRACAGRA 115.3 11712075 Removed GSCC errors.
42 26-jun-2012 PRACAGRA 115.5 14373899 Modified hoi.org_information10 to
43 substr(hoi.org_information10,1,6)
44 because of the changes done to
45 'PAY_US_PSD_CODES' valueset ID.
46 24-aug-2012 PRACAGRA 115.6 14379256 Modified the code to accomodate
47 multiple jurisdiction codes for single asg.
48 19-sep-2012 PRACAGRA 115.7 14640336 Modified the act cursor.
49 22-Nov-2012 SKCHALLA 115.8 14799833 Modified the cursors for the Yearly reporting.
50 26-Nov-2012 PKODURI 115.9 14799833 Corrected package creation stmt.
51 11-Dec-2012 SKCHALLA 115.10 15944697 Modified the Cursors to take casre of GRE change employees
52 ============================================================================
53 */
54
55 level_cnt NUMBER;
56
57 -- Sets up the tax unit context for the Submitter
58
59 /* Context and Parameter Set in the cursor are
60
61 Context :
62 --------------------------------------
63 PAYROLL_ACTION_ID - Payroll action Id of PSD Periodic Wage Listing Report
64 TAX_UNIT_ID - Submitter's Tax Unit ID
65 ASSIGNMENT_ID - Required for call to function - context not used
66 in the for Submitter
67 DATE EARNED - Always set to Effective date ie. in this case
68 for Mag tapes to last date of selected quarter or month.
69 */
70
71 g_min_chunk number:= -1;
72 g_archive_flag varchar2(1) := 'N';
73 TYPE char240_data_type_table IS TABLE OF VARCHAR2(240)
74 INDEX BY BINARY_INTEGER;
75 TYPE number_data_type_table IS TABLE OF NUMBER
76 INDEX BY BINARY_INTEGER;
77
78
79 TYPE jurisdiction_rec IS RECORD
80 ( balance_name varchar2(80)
81 ,balance_type_id number
82 ,dbi_name varchar2(80)
83 ,qtd_def_bal_id number
84 ,month_def_bal_id number
85
86 );
87
88 TYPE jurisdiction_tab IS TABLE OF
89 jurisdiction_rec
90 INDEX BY BINARY_INTEGER;
91
92 ltr_psd_tax_bal jurisdiction_tab;
93
94 CURSOR psd_xml_transmitter
95 IS
96 SELECT 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
97 'TR_TAX_UNIT_ID=P', hoi.organization_id,
98 'TR_DATE_EARNED=P', ppa.effective_date,
99 'BUSINESS_GROUP_ID=P', ppa.business_group_id,
100 'TRANSFER_STATE_CODE=P', substr (sr.jurisdiction_code, 1 , 2),
101 'ROOT_XML_TAG=P', '<LOCAL_PSD_EXTRACT>'
102 FROM pay_state_rules sr,
103 hr_organization_information hoi,
104 pay_payroll_actions ppa
105 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value ('TRANSFER_PAYROLL_ACTION_ID')
106 AND ppa.report_qualifier = 'LOCAL'
107 AND hoi.organization_id = pay_magtape_generic.get_parameter_value ('TRANSFER_TRANS_LEGAL_CO_ID')
108 AND sr.state_code = pay_magtape_generic.get_parameter_value ('TRANSFER_STATE')
109 AND hoi.org_information_context = 'W2 Reporting Rules'
110 AND ppa.report_type = 'PSD_MAG_XML'
111 AND to_char (ppa.effective_date, 'YYYY') = pay_magtape_generic.get_parameter_value ('TRANSFER_REPORTING_YEAR');
112
113
114 -- Cursor for employer.
115
116
117
118 /*SELECT DISTINCT
119 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
120 'TAX_UNIT_ID=P' , AA.tax_unit_id,
121 'TAX_UNIT_NAME=P', hou.name,
122 'ER_PSD_CODE=P' , substr(hoi.ORG_INFORMATION10,1,6)
123 FROM hr_all_organization_units hou,
124 pay_payroll_actions ppa,
125 pay_assignment_actions aa,
126 hr_organization_information hoi
127 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
128 ('TRANSFER_PAYROLL_ACTION_ID')
129 AND ppa.report_type = 'PSD_MAG_XML'
130 AND to_char(ppa.effective_date, 'YYYY') =
131 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
132 AND aa.payroll_action_id = ppa.payroll_action_id
133 --AND aa.tax_unit_id = pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
134 AND hou.organization_id = AA.tax_unit_id
135 and hoi.organization_id = hou.organization_id
136 and hoi.ORG_INFORMATION_CONTEXT ='PSD Reporting Rules'
137 union
138 --To consider WAH employees.
139 SELECT DISTINCT
140 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
141 'TAX_UNIT_ID=P' , AA.tax_unit_id,
142 'TAX_UNIT_NAME=P', hou.name,
143 'ER_PSD_CODE=P' , substr(puar.jurisdiction_code, 11,6)
144 FROM hr_all_organization_units hou,
145 pay_payroll_actions ppa,
146 pay_assignment_actions aa,
147 hr_organization_information hoi,
148 per_all_assignments_f paf,
149 pay_us_asg_reporting puar
150 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
151 ('TRANSFER_PAYROLL_ACTION_ID')
152 AND ppa.report_type = 'PSD_MAG_XML'
153 AND to_char(ppa.effective_date, 'YYYY') =
154 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
155 AND aa.payroll_action_id = ppa.payroll_action_id
156 AND aa.assignment_id = paf.assignment_id
157 AND paf.work_at_home = 'Y'
158 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
159 AND puar.assignment_id = paf.assignment_id
160 AND length(puar.jurisdiction_code)= 16
161 AND puar.tax_unit_id = AA.tax_unit_id
162 AND hou.organization_id = AA.tax_unit_id
163 and hoi.organization_id = hou.organization_id
164 and hoi.ORG_INFORMATION_CONTEXT ='PSD Reporting Rules';*/
165
166 CURSOR psd_xml_employer
167 IS
168 SELECT DISTINCT
169 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
170 'TAX_UNIT_ID=P' , AA.tax_unit_id,
171 'TAX_UNIT_NAME=P', hou.name,
172 'ER_PSD_CODE=P' , substr(puar.jurisdiction_code, 11,6)
173 FROM hr_all_organization_units hou,
174 pay_payroll_actions ppa,
175 pay_assignment_actions aa,
176 per_all_assignments_f paf,--Added for the Bug 15944697
177 hr_organization_information hoi,
178 pay_us_asg_reporting puar
179 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
180 ('TRANSFER_PAYROLL_ACTION_ID')
181 AND ppa.report_type = 'PSD_MAG_XML'
182 AND to_char(ppa.effective_date, 'YYYY') =
183 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
184 AND aa.payroll_action_id = ppa.payroll_action_id
185 AND to_number(aa.serial_number) = paf.person_id
186 AND pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
187 BETWEEN paf.effective_start_date and paf.effective_end_date
188 AND puar.assignment_id = paf.assignment_id
189 AND length(puar.jurisdiction_code)= 16
190 AND puar.tax_unit_id = AA.tax_unit_id
191 AND hou.organization_id = AA.tax_unit_id
192 and hoi.organization_id = hou.organization_id
193 and hoi.ORG_INFORMATION_CONTEXT ='PSD Reporting Rules'
194 and pay_magtape_generic.get_parameter_value('TRANSFER_PERIOD_TYPE') <> 'Yearly' --Added for the Bug 14799833
195 --order by 6,8--hou.name, substr(puar.jurisdiction_code, 11,6)
196 UNION ALL --Added for the Bug 14799833
197 SELECT DISTINCT
198 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
199 'TAX_UNIT_ID=P' , AA.tax_unit_id,
200 'TAX_UNIT_NAME=P', hou.name,
201 'ER_PSD_CODE=P' , substr(puar.jurisdiction_code, 11,6)
202 FROM hr_all_organization_units hou,
203 pay_payroll_actions ppa,
204 pay_assignment_actions aa,
205 per_all_assignments_f paf,--Added for the Bug 15944697
206 hr_organization_information hoi,
207 pay_us_asg_reporting puar
208 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
209 ('TRANSFER_PAYROLL_ACTION_ID')
210 AND ppa.report_type = 'YREND'
211 AND to_char(ppa.effective_date, 'YYYY') =
212 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
213 AND to_number(aa.serial_number) = paf.person_id
214 AND pay_magtape_generic.date_earned(ppa.effective_date,paf.assignment_id)
215 BETWEEN paf.effective_start_date and paf.effective_end_date
216 AND puar.assignment_id = paf.assignment_id
217 AND length(puar.jurisdiction_code)= 16
218 AND puar.tax_unit_id = AA.tax_unit_id
219 AND hou.organization_id = AA.tax_unit_id
220 and hoi.organization_id = hou.organization_id
221 and hoi.ORG_INFORMATION_CONTEXT ='PSD Reporting Rules'
222 and aa.tax_unit_id
223 = substr(ppa.legislative_parameters,
224 instr(ppa.legislative_parameters,
225 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
226 and pay_magtape_generic.get_parameter_value
227 ('TRANSFER_PERIOD_TYPE') = 'Yearly'
228 order by 6,8;--hou.name, substr(puar.jurisdiction_code, 11,6);
229 --
230 --
231 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
232 -- for an employee. The date_earned context is set to be the least of the
233 -- end of the period being reported and the maximum end date of the
234 -- assignment. This ensures that personal information ie. name etc... is
235 -- current relative to the period being reported on.
236 --
237 ---- Modified for the Bug 15944697
238 CURSOR psd_xml_employee
239 IS
240 SELECT DISTINCT
241 'TRANSFER_ACT_ID=P', AA.assignment_action_id
242 FROM per_all_people_f PE,
243 per_all_assignments_f SS,
244 pay_assignment_actions AA,
245 pay_payroll_actions PA ,
246 pay_us_asg_reporting puar
247 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
248 ('TRANSFER_PAYROLL_ACTION_ID')
249 AND AA.payroll_action_id = PA.payroll_action_id
250 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
251 ('TAX_UNIT_ID')
252 --AND SS.assignment_id = AA.assignment_id
253 AND SS.person_id = to_number(AA.serial_number)
254 AND PE.person_id = SS.person_id
255 AND pay_magtape_generic.date_earned(PA.effective_date,SS.assignment_id)
256 BETWEEN SS.effective_start_date and SS.effective_end_date
257 AND pay_magtape_generic.date_earned(PA.effective_date,SS.assignment_id)
258 BETWEEN PE.effective_start_date and PE.effective_end_date
259 AND puar.assignment_id = SS.assignment_id
260 AND substr(puar.jurisdiction_code, 11, 6) = pay_magtape_generic.get_parameter_value('ER_PSD_CODE');
261 /* and exists (select 1 from pay_us_asg_reporting puar
262 where puar.assignment_id = SS.assignment_id
263 and substr(puar.jurisdiction_code, 11, 6) = pay_magtape_generic.get_parameter_value('ER_PSD_CODE')
264 )*/
265
266 CURSOR psd_xml_curr_act_id IS
267 /*SELECT 'TRANSFER_ACT_ID=P',
268 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
269 FROM DUAL;*/
270
271 /*SELECT DISTINCT 'TRANSFER_ACT_ID=P',
272 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'),
273 'TRANSFER_EE_JD_CODE=P', puar.jurisdiction_code
274 FROM pay_us_asg_reporting puar,
275 pay_assignment_actions paa
276 WHERE puar.assignment_id=paa.assignment_id
277 AND paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
278 AND length(puar.jurisdiction_code)=16
279 AND EXISTS ( select null
280 from ff_archive_items fai,
281 ff_archive_item_contexts faic,
282 FF_CONTEXTS FC
283 WHERE fai.context1 = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
284 and fai.archive_item_id = faic.archive_item_id
285 and faic.CONTEXT_ID = FC.CONTEXT_ID
286 AND FC.CONTEXT_NAME='JURISDICTION_CODE'
287 AND FAIC.CONTEXT = puar.jurisdiction_code
288 ); */
289
290 -- Modified for Bug 14640336
291
292 SELECT DISTINCT 'TRANSFER_ACT_ID=P',
293 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID'),
294 'TRANSFER_EE_JD_CODE=P', puar.jurisdiction_code
295 FROM pay_us_asg_reporting puar,
296 pay_assignment_actions paa,
297 per_all_people_f pap,
298 per_all_assignments_f paf,
299 pay_payroll_actions ppa
300 WHERE puar.assignment_id=paf.assignment_id
301 AND paf.person_id=pap.person_id
302 AND pap.person_id=paa.serial_number
303 AND paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
304 AND length(puar.jurisdiction_code)=16
305 AND paa.payroll_action_id = ppa.payroll_action_id
306 AND ( ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PpA.payroll_action_id,
307 'TRANSFER_PERIOD_TYPE') <> 'Yearly'
308 and EXISTS ( select null
309 from ff_archive_items fai,
310 ff_archive_item_contexts faic,
311 FF_CONTEXTS FC
312 WHERE fai.context1 = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
313 and fai.archive_item_id = faic.archive_item_id
314 and faic.CONTEXT_ID = FC.CONTEXT_ID
315 AND FC.CONTEXT_NAME='JURISDICTION_CODE'
316 AND FAIC.CONTEXT = puar.jurisdiction_code))
317 OR ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PpA.payroll_action_id,
318 'TRANSFER_PERIOD_TYPE') = 'Yearly'
319 AND EXISTS ( select null
320 from ff_archive_items fai,
321 ff_archive_item_contexts faic,
322 FF_CONTEXTS FC,
323 PAY_ACTION_INTERLOCKS pai
324 WHERE fai.context1 = pai.locked_action_id
325 and pai.locking_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
326 and fai.archive_item_id = faic.archive_item_id
327 and faic.CONTEXT_ID = FC.CONTEXT_ID
328 AND FC.CONTEXT_NAME='JURISDICTION_CODE'
329 AND FAIC.CONTEXT = puar.jurisdiction_code))
330 );
331 /* OR ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PpA.payroll_action_id,
332 'TRANSFER_PERIOD_TYPE') = 'Yearly'
333 AND EXISTS ( select null
334 from PAY_ACTION_INTERLOCKS pai
335 WHERE pai.locking_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
336 AND EXISTS ( SELECT NULL
337 from PAY_US_LOCALITY_W2_V v
338 where v.assignment_action_id = pai.locked_action_id
339 and v.JURISDICTION = puar.jurisdiction_code
340 and v.W2_LOCAL_INCOME_TAX <> 0
341 )
342 )
343 )
344 );*/
345
346 CURSOR GET_XML_VER IS
347 SELECT 'ROOT_XML_TAG=P',
348 '<LOCAL_PSD_EXTRACT>',
349 'PAYROLL_ACTION_ID=P',
350 pay_magtape_generic.get_parameter_value(
351 'TRANSFER_PAYROLL_ACTION_ID')
352 FROM dual;
353
354 FUNCTION bal_db_item
355 (
356 p_db_item_name VARCHAR2
357 )
358 RETURN NUMBER;
359
360
361
362 FUNCTION get_parameter
363 (
364 name IN VARCHAR2,
365 parameter_list IN VARCHAR2
366 )
367 RETURN VARCHAR2;
368
369
370 FUNCTION get_balance_value
371 (
372 p_defined_balance_id in number,
373 p_balcall_aaid in number
374 )
375 RETURN NUMBER;
376
377
378 PROCEDURE get_report_parameters
379 (
380 p_pactid IN NUMBER,
381 p_year_start IN OUT nocopy DATE,
382 p_year_end IN OUT nocopy DATE,
383 p_state_abbrev IN OUT nocopy VARCHAR2,
384 p_state_code IN OUT nocopy VARCHAR2,
385 p_report_type IN OUT nocopy VARCHAR2,
386 p_business_group_id IN OUT nocopy NUMBER,
387 p_period_type IN OUT nocopy VARCHAR2,
388 p_period_start IN OUT nocopy DATE,
389 p_period_end IN OUT nocopy DATE,
390 p_tax_coll_dist_code IN OUT nocopy VARCHAR2
391 );
392
393 FUNCTION preprocess_check(
394 p_pactid NUMBER,
395 p_business_group_id NUMBER,
396 p_tax_coll_dist_code VARCHAR2
397 )
398 RETURN BOOLEAN;
399
400
401 PROCEDURE range_cursor (
402 p_pactid IN NUMBER,
403 p_sqlstr OUT nocopy VARCHAR2
404 );
405
406 PROCEDURE action_creation(
407 p_pactid IN NUMBER,
408 p_stperson IN NUMBER,
409 p_endperson IN NUMBER,
410 p_chunk IN NUMBER );
411
412 procedure archive_gre_data(p_payroll_action_id in number,
413 p_tax_unit_id in number);
414
415
416 procedure action_archinit(p_payroll_action_id in number);
417
418 procedure archive_data(p_assactid in number, p_effective_date in date);
419
420 --
421 -- Follwing Procedures are used for constructing XML for Submitter or RA Record
422 --
423 PROCEDURE transmitter_record_start;
424
425 PROCEDURE transmitter_record_end;
426
427 --
428 -- Follwing Procedures are used for constructing XML for Employer or RE Record
429 --
430 PROCEDURE psd_xml_employer_start;
431
432 PROCEDURE psd_xml_employer_end;
433
434 --
435 -- Follwing Procedures are used for constructing XML for Employee
436 --
437 PROCEDURE psd_xml_employee_build;
438
439
440 /****************************************************************************
441 Name : WRITE_TO_MAGTAPE_LOB
442 Description : This procedure appends passed BLOB parameter to
443 pay_mag_tape.g_blob_value
444 *****************************************************************************/
445
446 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB);
447
448 /****************************************************************************
449 Name : WRITE_TO_MAGTAPE_LOB
450 Description : This procedure appends passed varchar2 parameter to
451 pay_mag_tape.g_blob_value
452 *****************************************************************************/
453
454 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2);
455
456
457 END pay_us_psd_xml;
[Home] [Help]