1 PACKAGE PAY_US_MMREF_LOCAL_XML AS
2 /* $Header: payusw2mmref1xml.pkh 120.0.12000000.1 2007/02/23 10:17:25 sackumar noship $ */
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 ============================================================================
37 */
38
39 -- 'level_cnt' will allow the cursors to select function results,
40 -- whether it is a standard fuction such as to_char or a function
41 -- defined in a package (with the correct pragma restriction).
42
43 level_cnt NUMBER;
44
45 -- Sets up the tax unit context for the Submitter
46
47 /* Context and Parameter Set in the cursor are
48
49 Context :
50 --------------------------------------
51 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
52 TAX_UNIT_ID - Submitter's Tax Unit ID
53 ASSIGNMENT_ID - Required for call to function - context not used
54 in the for Submitter
55 DATE EARNED - Always set to Effective date ie. in this case
56 for Mag tapes to 31-DEC-YYYY, in case of SQWL
57 this will be diffrent.
58 Parameters :
59 TRANSFER_2678_FILER
60 -- Following two parameters added for New locality
61 TRANSFER_LOCALITY_CODE
62 TRANSFER_STATE_CODE
63 */
64
65 /* Transmitter for the Local Megnetic Media in MMREF Format */
66
67 CURSOR local_w2_xml_transmitter
68 IS
69 SELECT 'PAYROLL_ACTION_ID=P', PPA.payroll_action_id,
70 'TR_TAX_UNIT_ID=P' , HOI.organization_id,
71 'TR_DATE_EARNED=P', PPA.effective_date,
72 'TRANSFER_2678_FILER=P', HOI.org_information8,
73 'BUSINESS_GROUP_ID=P', PPA.business_group_id,
74 'TRANSFER_LOCALITY_CODE=P',
75 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
76 'LC'),
77 'TRANSFER_STATE_CODE=P', substr(sr.jurisdiction_code,1,2),
78 'ROOT_XML_TAG=P', '<LOCAL_W2_EXTRACT>'
79 FROM pay_state_rules SR,
80 hr_organization_information HOI,
81 pay_payroll_actions PPA,
82 pay_payroll_actions PPA1
83 WHERE PPA1.payroll_action_id = pay_magtape_generic.get_parameter_value
84 ('TRANSFER_PAYROLL_ACTION_ID')
85 AND ppa1.effective_date = ppa.effective_date
86 AND ppa1.report_qualifier = 'LOCAL'
87 AND HOI.organization_id =
88 pay_magtape_generic.get_parameter_value('TRANSFER_TRANS_LEGAL_CO_ID')
89 AND SR.state_code =
90 pay_magtape_generic.get_parameter_value('TRANSFER_STATE')
91 AND HOI.org_information_context = 'W2 Reporting Rules'
92 AND PPA.report_type = 'YREND'
93 AND HOI.ORGANIZATION_ID =
94 substr(PPA.legislative_parameters,instr(PPA.legislative_parameters,'TRANSFER_GRE=')
95 + length('TRANSFER_GRE='))
96 AND to_char(PPA.effective_date,'YYYY') =
97 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
98 AND to_char(PPA.effective_date,'DD-MM') = '31-12';
99
100 /* Context and Parameter Set in the cursor are
101 Parameters :
102 ----------------------------------------------------
103 TAX_UNIT_ID - Tax Unit ID of GRE
104 PAYROLL_ACTION_ID - Payroll action Id of Year End Pre-processor
105 TAX_UNIT_NAME - Name of GRE
106 */
107 --
108 CURSOR local_w2_xml_Employer
109 IS
110 SELECT DISTINCT
111 'PAYROLL_ACTION_ID=P', ppa.payroll_action_id,
112 'TAX_UNIT_ID=P' , AA.tax_unit_id,
113 'TAX_UNIT_NAME=P', hou.name
114 FROM hr_all_organization_units hou,
115 pay_payroll_actions ppa,
116 pay_assignment_actions aa
117 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value
118 ('TRANSFER_PAYROLL_ACTION_ID')
119 AND ppa.report_type = 'YREND'
120 AND to_char(ppa.effective_date, 'YYYY') =
121 pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR')
122 AND to_char(ppa.effective_date,'DD-MM') = '31-12'
123 AND aa.tax_unit_id = substr(ppa.legislative_parameters,
124 instr(ppa.legislative_parameters,
125 'TRANSFER_GRE=') + length('TRANSFER_GRE='))
126 AND hou.organization_id = AA.tax_unit_id
127 ORDER BY hou.name;
128 --
129 --
130 -- Sets up the assignment_action_id, assignment_id, and date_earned contexts
131 -- for an employee. The date_earned context is set to be the least of the
132 -- end of the period being reported and the maximum end date of the
133 -- assignment. This ensures that personal information ie. name etc... is
134 -- current relative to the period being reported on.
135 --
136 CURSOR local_w2_xml_employee
137 IS
138 SELECT DISTINCT
139 'TRANSFER_ACT_ID=P', AA.assignment_action_id
140 FROM ff_archive_items FAI,
141 ff_contexts FC, -- JD
142 ff_database_items FDI,
143 ff_archive_item_contexts FAIC,
144 per_all_people_f PE,
145 per_all_assignments_f SS,
146 pay_action_interlocks AI,
147 pay_assignment_actions AA1, -- for YE Archiver Assignment Actions
148 pay_assignment_actions AA,
149 pay_payroll_actions PA
150 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
151 ('TRANSFER_PAYROLL_ACTION_ID')
152 AND AA.payroll_action_id = PA.payroll_action_id
153 AND AI.locking_action_id = AA.assignment_action_id
154 AND AA.tax_unit_id = pay_magtape_generic.get_parameter_value
155 ('TAX_UNIT_ID')
156 AND SS.assignment_id = AA.assignment_id
157 AND PE.person_id = SS.person_id
158 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
159 BETWEEN SS.effective_start_date and SS.effective_end_date
160 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
161 BETWEEN PE.effective_start_date and PE.effective_end_date
162 AND AI.locked_action_id = fai.context1
163 AND AI.locked_action_id = AA1.assignment_action_id
164 AND fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
165 AND fdi.user_entity_id = fai.user_entity_id
166 AND faic.archive_item_id = fai.archive_item_id
167 AND fc.context_name = 'JURISDICTION_CODE'
168 AND faic.context_id = fc.context_id
169 AND substr(rtrim(ltrim(faic.context)),1,2) =
170 pay_magtape_generic.get_parameter_value('TRANSFER_STATE_CODE')
171 AND value <> '0'
172 AND ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') = 'NULL'
173 OR
174 ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') <> 'NULL'
175 AND EXISTS
176 ( SELECT '1'
177 FROM pay_us_city_tax_info_f puctif
178 WHERE substr(rtrim(ltrim(faic.context)),1,2)||'-000-'||substr(rtrim(ltrim(faic.context)),8,4) =
179 substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4)
180 AND puctif.effective_start_date < PA.effective_date
181 AND puctif.effective_end_date >= PA.effective_date
182 AND substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4) =
183 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2) ||'-000-'||
184 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),8,11)
185 )
186 )
187 );
188
189 /* CURSOR local_w2_xml_employee
190 IS
191 SELECT DISTINCT
192 'TRANSFER_ACT_ID=P', AA.assignment_action_id,
193 'YE_ASSIGNMENT_ACTION_ID=P', AA1.assignment_action_id, -- YREND assignment action
194 'YE_TAX_UNIT_ID=P', AA1.TAX_UNIT_ID,
195 'EE_ASSIGNMENT_ID=P', AA.assignment_id,
196 'EE_DATE_EARNED=P', pay_magtape_generic.date_earned(PA.effective_date,
197 AA.assignment_id),
198 'EE_LOCALITY_JD_CODE=P', substr(ltrim(rtrim(faic.context)),1,2)||'-000-'||substr(ltrim(rtrim(faic.context)),8,4)
199 FROM ff_archive_items FAI,
200 ff_contexts FC, -- JD
201 ff_database_items FDI,
202 ff_archive_item_contexts FAIC,
203 per_all_people_f PE,
204 per_all_assignments_f SS,
205 pay_action_interlocks AI,
206 pay_assignment_actions AA1, -- for YE Archiver Assignment Actions
207 pay_assignment_actions AA,
208 pay_payroll_actions PA
209 WHERE PA.payroll_action_id = pay_magtape_generic.get_parameter_value
210 ('TRANSFER_PAYROLL_ACTION_ID')
211 AND AA.payroll_action_id = PA.payroll_action_id
212 AND AI.locking_action_id = AA.assignment_action_id
213 AND SS.assignment_id = AA.assignment_id
214 AND PE.person_id = SS.person_id
215 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
216 BETWEEN SS.effective_start_date and SS.effective_end_date
217 AND pay_magtape_generic.date_earned(PA.effective_date,AA.assignment_id)
218 BETWEEN PE.effective_start_date and PE.effective_end_date
219 AND AI.locked_action_id = fai.context1
220 AND AI.locked_action_id = AA1.assignment_action_id
221 AND fdi.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
222 AND fdi.user_entity_id = fai.user_entity_id
223 AND faic.archive_item_id = fai.archive_item_id
224 AND fc.context_name = 'JURISDICTION_CODE'
225 AND faic.context_id = fc.context_id
226 AND substr(rtrim(ltrim(faic.context)),1,2) =
227 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2)
228 AND value <> '0'
229 AND ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') IS NULL
230 OR ( pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE') IS NOT NULL
231 AND EXISTS
232 ( SELECT '1'
233 FROM pay_us_city_tax_info_f puctif
234 WHERE substr(rtrim(ltrim(faic.context)),1,2)||'-000-'||substr(rtrim(ltrim(faic.context)),8,4) =
235 substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4)
236 AND puctif.effective_start_date < PA.effective_date
237 AND puctif.effective_end_date >= PA.effective_date
238 AND substr(puctif.jurisdiction_code,1,2)||'-000-'||substr(puctif.jurisdiction_code,8,4) =
239 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),1,2) ||'-000-'||
240 substr(pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE'),8,11)
241 )
242 )
243 );
244 */
245
246 CURSOR local_w2_xml_curr_act_id IS
247 SELECT 'TRANSFER_ACT_ID=P',
248 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
249 FROM DUAL;
250
251 CURSOR GET_XML_VER IS
252 SELECT 'ROOT_XML_TAG=P',
253 '<LOCAL_MAG>',
254 'PAYROLL_ACTION_ID=P',
255 pay_magtape_generic.get_parameter_value(
256 'TRANSFER_PAYROLL_ACTION_ID')
257 FROM dual;
258
259 FUNCTION bal_db_item
260 (
261 p_db_item_name VARCHAR2
262 ) RETURN NUMBER;
263
264 PROCEDURE get_report_parameters
265 (
266 p_pactid IN NUMBER,
267 p_year_start IN OUT nocopy DATE,
268 p_year_end IN OUT nocopy DATE,
269 p_state_abbrev IN OUT nocopy VARCHAR2,
270 p_state_code IN OUT nocopy VARCHAR2,
271 p_report_type IN OUT nocopy VARCHAR2,
272 p_business_group_id IN OUT nocopy NUMBER,
273 -- Following parameter added for Locality Code
274 p_locality_code IN OUT nocopy VARCHAR2
275 );
276
277 FUNCTION get_balance_value (
278 p_balance_name VARCHAR2,
279 p_tax_unit_id NUMBER,
280 p_state_abbrev VARCHAR2,
281 p_assignment_id NUMBER,
282 p_effective_date DATE
283 ) RETURN NUMBER;
284
285 /****************************************************************************
286 Name : RANGE_CURSOR
287 Description : This procedure prepares range of persons to be processed for process
288 Local YearEnd Interface Extract
289 *****************************************************************************/
290 PROCEDURE range_cursor (
291 p_pactid IN NUMBER,
292 p_sqlstr OUT nocopy VARCHAR2
293 );
294
295
296 PROCEDURE create_assignment_act(
297 p_pactid IN NUMBER,
298 p_stperson IN NUMBER,
299 p_endperson IN NUMBER,
300 p_chunk IN NUMBER );
301
302 CURSOR LOCAL_CURR_ACT_ID IS
303 SELECT 'TRANSFER_ACT_ID=P',
304 pay_magtape_generic.get_parameter_value( 'TRANSFER_ACT_ID' )
305 FROM DUAL;
306
307 CURSOR LOCAL_MAG_ASG_ACT IS
308 SELECT 'TRANSFER_ACT_ID=P', paa.assignment_action_id
309 FROM pay_assignment_actions paa
310 WHERE payroll_action_id = pay_magtape_generic.get_parameter_value(
311 'TRANSFER_PAYROLL_ACTION_ID');
312 --
313 -- Follwing Procedures are used for constructing XML for Submitter or RA Record
314 --
315 PROCEDURE transmitter_record_start;
316
317 PROCEDURE transmitter_record_end;
318
319 --
320 -- Follwing Procedures are used for constructing XML for Employer or RE Record
321 --
322 PROCEDURE local_w2_xml_employer_start;
323
324 PROCEDURE local_w2_xml_employer_end;
325
326 --
327 -- Follwing Procedures are used for constructing XML for Employee
328 --
329 PROCEDURE local_w2_xml_employee_build;
330
331
332 /****************************************************************************
333 Name : WRITE_TO_MAGTAPE_LOB
334 Description : This procedure appends passed BLOB parameter to
335 pay_mag_tape.g_blob_value
336 *****************************************************************************/
337
338 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB);
339
340 /****************************************************************************
341 Name : WRITE_TO_MAGTAPE_LOB
342 Description : This procedure appends passed varchar2 parameter to
343 pay_mag_tape.g_blob_value
344 *****************************************************************************/
345
346 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2);
347
348 FUNCTION GET_PARAMETER(name IN VARCHAR2,
349 parameter_list IN VARCHAR2) RETURN VARCHAR2;
350
351 END pay_us_mmref_local_xml;
[Home] [Help]