1 PACKAGE BODY pay_us_mmref_local_xml AS
2 /*REM $Header: payusw2mmref1xml.pkb 120.3.12020000.1 2012/06/28 16:39:10 appldev ship $
3 REM +======================================================================+
4 REM | Copyright (c) 1997 Oracle Corporation |
5 REM | Redwood Shores, California, USA |
6 REM | All rights reserved. |
7 REM +======================================================================+
8 REM Name
9 REM pay_us_mmref_local_xml
10 REM File
11 REM payusw2mmref1xml.pkb
12 REM
13 REM Purpose
14 REM
15 REM The purpose of this package is to support the generation of XML for the process
16 REM Local W-2 Generic MMREF-1. This package includes all the cursors, procedures and functions
17 REM used to comply with the payroll CORE multi-thtread enhancement architecture.
18 REM
19 REM Currently this is not meant for any specific locality magnetic tape.
20 REM
21 REM Notes
22 REM The generation of each magnetic tape report is a two stage process i.e.
23 REM 1. Check if the year end pre-processor has been run for all the GREs. If not, then error
24 REM out without processing further.
25 REM 2. Create a payroll action for the report. Identify all the assignments to be reported and record
26 REM an assignment action against the payroll action for each one of them.
27 REM 3. Run the "Local W-2 Generic MMREF-1 XML" process to use this package.
28 REM
29 REM Change History
30 REM ============================================================================
31 REM 07-NOV-2006 PPANDA 115.0 Initial Version Created
32 REM 02-JAN-2009 SVANNIAN 115.1 Changed the action creation cursor to pick up Employees with SD Taxes.
33 REM ============================================================================
34 REM
35 */
36 --
37 -- Global Variables
38 --
39 g_proc_name varchar2(240);
40 g_debug boolean;
41 g_document_type varchar2(50);
42
43 /****************************************************************************
44 Name : HR_UTILITY_TRACE
45 Description : This procedure prints debug messages.
46 *****************************************************************************/
47
48 PROCEDURE HR_UTILITY_TRACE
49 (
50 P_TRC_DATA varchar2
51 ) AS
52 BEGIN
53 IF g_debug THEN
54 hr_utility.trace(p_trc_data);
55 END IF;
56 END HR_UTILITY_TRACE;
57
58 /*
59 -------------------------------------------------------------------------------------------------------
60 -- Name : bal_db_item
61 -- Purpose : Given the name of a balance DB item as would be seen in a
62 -- fast formula it returns the defined_balance_id of the balance it represents.
63 -- Arguments
64 -- INPUT: p_db_item_name
65 -- RETURNS : l_defined_balance_id
66 -- Notes
67 -- A defined_balance_id is required by the PLSQL balance function.
68 --------------------------------------------------------------------------------------------------------
69 */
70 FUNCTION bal_db_item (
71 p_db_item_name VARCHAR2
72 ) RETURN NUMBER IS
73 -- Get the defined_balance_id for the specified balance DB item.
74 CURSOR csr_defined_balance IS
75 SELECT TO_NUMBER(UE.creator_id)
76 FROM ff_database_items DI,
77 ff_user_entities UE
78 WHERE DI.user_name = p_db_item_name
79 AND UE.user_entity_id = DI.user_entity_id
80 AND UE.creator_type = 'B'
81 AND UE.legislation_code = 'US';
82
83 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
84 BEGIN
85 hr_utility.set_location
86 ('pay_us_mmref_local_xml.bal_db_item - opening cursor', 10);
87 -- Open the cursor
88 OPEN csr_defined_balance;
89 -- Fetch the value
90 FETCH csr_defined_balance
91 INTO l_defined_balance_id;
92 IF csr_defined_balance%NOTFOUND THEN
93 CLOSE csr_defined_balance;
94 hr_utility.set_location
95 ('pay_us_mmref_local_xml.bal_db_item - no rows found from cursor', 20);
96 hr_utility.raise_error;
97 ELSE
98 hr_utility.set_location
99 ('pay_us_mmref_local_xml.bal_db_item - fetched from cursor', 30);
100 CLOSE csr_defined_balance;
101 END IF;
102 -- Return the value to the call
103 RETURN (l_defined_balance_id);
104 END bal_db_item;
105
106 -----------------------------------------------------------------------------
107 -- Name: get_report_parameters
108 -- Purpose
109 -- The procedure gets the 'parameter' for which the report is being
110 -- run i.e., the period, state and business organization.
111 -- Arguments
112 -- p_year_start Start Date of the period for which the report
113 -- has been requested
114 -- p_year_end End date of the period
115 -- p_business_group_id Business group for which the report is being run
116 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
117 -- report)
118 -- p_state_code State code (NULL for federal)
119 -- p_report_type W2_LOCAL_XML
120 --
121 -- p_locality_code This parameter will have the jurisdiction
122 --
123 -- Notes
124 ----------------------------------------------------------------------------
125 PROCEDURE get_report_parameters
126 ( p_pactid IN NUMBER,
127 p_year_start IN OUT NOCOPY DATE,
128 p_year_end IN OUT NOCOPY DATE,
129 p_state_abbrev IN OUT NOCOPY VARCHAR2,
130 p_state_code IN OUT NOCOPY VARCHAR2,
131 p_report_type IN OUT NOCOPY VARCHAR2,
132 p_business_group_id IN OUT NOCOPY NUMBER,
133 p_locality_code IN OUT NOCOPY VARCHAR2
134 ) IS
135 l_state_code varchar2(200);
136 BEGIN
137 hr_utility.set_location
138 ('pay_us_mmref_local_xml.get_report_parameters', 10);
139 hr_utility.trace('Payroll_Action_Id '|| to_char(p_pactid));
140 SELECT ppa.start_date,
141 ppa.effective_date,
142 ppa.business_group_id,
143 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
144 'TRANSFER_STATE'),
145 ppa.report_type,
146 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
147 'LC')
148 INTO p_year_start,
149 p_year_end,
150 p_business_group_id,
151 p_state_abbrev,
152 p_report_type,
153 p_locality_code
154 FROM pay_payroll_actions ppa
155 WHERE ppa.payroll_action_id = p_pactid;
156 --
157 select state_code into l_state_code
158 from pay_us_states pus
159 where pus.state_abbrev = p_state_abbrev;
160
161 p_state_code := l_state_code;
162
163 /*
164 if p_locality_code = 'NULL' then
165 p_locality_code := l_state_code||'000-0000';
166 end if;
167 */
168 hr_utility.set_location('pay_us_mmref_local_xml.get_report_parameters', 15);
169 hr_utility.trace('Parameter Values ');
170 hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
171 hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
172 hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
173 hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
174 hr_utility.trace('p_state_code '|| p_state_code);
175 hr_utility.trace('p_report_type '|| p_report_type);
176 hr_utility.trace('p_locality_code '|| p_locality_code);
177 hr_utility.set_location
178 ('pay_us_mmref_local_xml.get_report_parameters', 40);
179 EXCEPTION
180 WHEN OTHERS THEN
181 hr_utility.trace('get_report_parameters procedure Raised Exception ');
182 hr_utility.trace('ERROR '||substr(SQLERRM,1,40));
183 hr_utility.trace(substr(SQLERRM,41,90));
184 END get_report_parameters;
185
186 -------------------------------------------------------------------------
187 -- Name : get_balance_value
188 --
189 --Purpose
190 -- Get the value of the specified balance item
191 --Arguments
192 -- p_balance_name Name of the balnce
193 -- p_tax_unit_id GRE name for the context
194 -- p_state_code State for context
195 -- p_assignment_id Assignment for whom the balance is to be
196 -- retrieved
197 -- p_effective_date effective_date
198 --Note
199 -- This procedure set is a wrapper for setting the GRE/Jurisdiction context
200 -- needed by the pay_balance_pkg.get_value to get the actual balance
201 -------------------------------------------------------------------------
202 FUNCTION get_balance_value (
203 p_balance_name VARCHAR2,
204 p_tax_unit_id NUMBER,
205 p_state_abbrev VARCHAR2,
206 p_assignment_id NUMBER,
207 p_effective_date DATE
208 ) RETURN NUMBER IS
209 l_jurisdiction_code VARCHAR2(20);
210 BEGIN
211 hr_utility.set_location
212 ('pay_us_mmref_local_xml.get_balance_value', 10);
213 pay_balance_pkg.set_context('TAX_UNIT_ID', p_tax_unit_id);
214 IF p_state_abbrev <> 'FED' THEN
215 SELECT jurisdiction_code
216 INTO l_jurisdiction_code
217 FROM pay_state_rules
218 WHERE state_code = p_state_abbrev;
219 hr_utility.set_location
220 ('pay_us_mmref_local_xml.get_balance_value', 15);
221 pay_balance_pkg.set_context('JURISDICTION_CODE', l_jurisdiction_code);
222 END IF;
223 hr_utility.trace(p_balance_name);
224 hr_utility.trace('Context');
225 hr_utility.trace('Tax Unit Id: '|| p_tax_unit_id);
226 hr_utility.trace('Jurisdiction: '|| l_jurisdiction_code);
227 hr_utility.set_location
228 ('pay_us_mmref_local_xml.get_balance_value', 20);
229 RETURN pay_balance_pkg.get_value(bal_db_item(p_balance_name),
230 p_assignment_id,
231 p_effective_date);
232 END get_balance_value;
233
234 /****************************************************************************
235 Name : RANGE_CURSOR
236 Description : This procedure prepares range of persons to be processed for process
237 Local YearEnd Interface Extract. This procedure defines a SQL statement
238 to fetch all the people to be included in the generic XML extract. This SQL
239 statement is used to define the 'chunks' for multi-threaded operation
240 Arguments
241 p_pactid payroll action id for the report
242 p_sqlstr the SQL statement to fetch the people
243 *****************************************************************************/
244
245 PROCEDURE range_cursor ( p_pactid IN NUMBER,
246 p_sqlstr OUT nocopy VARCHAR2
247 )
248 IS
249 p_year_start DATE;
250 p_year_end DATE;
251 p_business_group_id NUMBER;
252 p_state_abbrev VARCHAR2(200);
253 p_state_code VARCHAR2(200);
254 p_report_type VARCHAR2(200);
255 p_locality_code VARCHAR2(200);
256 BEGIN
257 -- hr_utility.trace_on(null,'LOCALXML');
258 hr_utility.set_location( 'pay_us_mmref_local_xml.range_cursor', 10);
259
260 get_report_parameters( p_pactid,
261 p_year_start,
262 p_year_end,
263 p_state_abbrev,
264 p_state_code,
265 p_report_type,
266 p_business_group_id,
267 p_locality_code
268 );
269 hr_utility.set_location( 'pay_us_mmref_local_xml.range_cursor', 20);
270
271
272 IF p_report_type = 'W2_MAG_XML' THEN
273 p_sqlstr := '
274 SELECT DISTINCT
275 to_number(paa.serial_number)
276 FROM ff_archive_item_contexts faic,
277 ff_archive_items fai,
278 ff_database_items fdi,
279 pay_assignment_actions paa,
280 pay_payroll_actions ppa,
281 per_all_assignments_f paf,
282 pay_payroll_actions ppa1,
283 pay_us_states pus
284 WHERE ppa1.payroll_action_id = :payroll_action_id
285 AND ppa.business_group_id+0 = ppa1.business_group_id
286 AND ppa1.effective_date = ppa.effective_date
287 AND ppa.report_type = ''YREND''
288 AND ppa.payroll_action_id = paa.payroll_action_id
289 AND paf.assignment_id = paa.assignment_id
290 AND paf.assignment_type = ''E''
291 AND fdi.user_name = ''A_STATE_ABBREV''
292 AND fdi.user_entity_id = fai.user_entity_id
293 AND fai.archive_item_id = faic.archive_item_id
294 AND fai.context1 = paa.assignment_action_id
295 AND pus.STATE_ABBREV = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa1.payroll_action_id,
296 ''TRANSFER_STATE'')
297 AND fai.value = pus.STATE_ABBREV
298 AND paf.effective_start_date <= ppa.effective_date
299 AND paf.effective_end_date >= ppa.start_date
300 AND paa.action_status = ''C''
301 AND nvl(hr_us_w2_rep.get_w2_arch_bal( paa.assignment_action_id,
302 ''A_W2_STATE_WAGES'',
303 paa.tax_unit_id,
304 faic.context , 2), 0) > 0
305 AND EXISTS ( /*+ INDEX(pustif PAY_US_STATE_TAX_INFO_F_N1) */
306 SELECT ''x''
307 FROM pay_us_state_tax_info_f pustif
308 WHERE substr(faic.context,1,2) = pustif.state_code
309 AND ppa.effective_date between pustif.effective_start_date
310 AND pustif.effective_end_date
311 AND pustif.sit_exists = ''Y'')
312 AND NOT EXISTS (
313 SELECT ''x''
314 FROM hr_organization_information hoi
315 WHERE hoi.organization_id = paa.tax_unit_id
316 AND hoi.org_information_context = ''1099R Magnetic Report Rules''
317 )
318 ORDER BY to_number(paa.serial_number)';
319 hr_utility.set_location( 'pay_us_mmref_local_xml.range_cursor', 40);
320 END IF;
321 hr_utility.trace( substr(p_sqlstr,1, 50));
322 hr_utility.set_location( 'pay_us_mmref_local_xml.range_cursor', 50);
323 END range_cursor;
324 --
325
326 /****************************************************************************
327 Name : CREATE_ASSIGNMENT_ACT
328 Description : This procedure creates assignment actions for the payroll action associated
329 process <Local YearEnd Interface Extract>
330
331 The procedure processes assignments in 'chunks' to facilitate multi-threaded
332 operation. The chunk is defined by the size and the starting and ending person id.
333 An interlock is also created against the year-end pre-processor assignment action
334 to prevent rolling back of the archiver.
335
336 *****************************************************************************/
337
338 PROCEDURE create_assignment_act(
339 p_pactid IN NUMBER,
340 p_stperson IN NUMBER,
341 p_endperson IN NUMBER,
342 p_chunk IN NUMBER
343 )
344 IS
345 -- Cursor to get the assignments for Local YearEnd Interface Extract. Gets only those employees
346 -- which have wages for the specified state.This cursor excludes the 1099R GREs.
347 --
348 CURSOR c_local ( c_state_code VARCHAR2,
349 c_locality_code VARCHAR2)
350 IS
351 SELECT
352 to_number(paa.serial_number),
353 paf.assignment_id,
354 paa.tax_unit_id,
355 paf.effective_end_date,
356 paa.assignment_action_id,
357 sum(fai1.value)
358 FROM
359 pay_assignment_actions paa, -- YREND PAA
360 pay_payroll_actions ppa, -- YREND PPA
361 per_all_assignments_f paf,
362 pay_payroll_actions ppa1,
363 ff_contexts fc1 , --for city context
364 ff_archive_items fai1, -- city
365 ff_archive_item_contexts faic1, -- city_context
366 ff_database_items fdi1 --database_items for City_withheld
367 --,pay_us_city_tax_info_f puctif
368 WHERE ppa1.payroll_action_id = p_pactid
369 AND ppa.business_group_id+0 = ppa1.business_group_id
370 AND ppa1.effective_date = ppa.effective_date
371 AND ppa.report_type = 'YREND'
372 AND ppa.payroll_action_id = paa.payroll_action_id
373 AND paf.assignment_id = paa.assignment_id
374 AND paf.assignment_type = 'E'
375 AND fc1.context_name = 'JURISDICTION_CODE'
376 AND faic1.context_id = fc1.context_id
377 AND fdi1.user_name = 'A_CITY_WITHHELD_PER_JD_GRE_YTD'
378 AND fdi1.user_entity_id = fai1.user_entity_id
379 AND fai1.context1 = paa.assignment_action_id
380 AND fai1.archive_item_id = faic1.archive_item_id
381 AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
382 AND (c_locality_code IS NULL OR
383 ( c_locality_code IS NOT NULL
384 AND EXISTS ( SELECT 'x' from pay_us_city_tax_info_f puctif
385 WHERE substr(puctif.jurisdiction_code,1,2)||'-000-'||
386 substr(puctif.jurisdiction_code,8,4)
387 = substr(ltrim(rtrim(faic1.context)),1,2)||'-000-'||
388 substr(ltrim(rtrim(faic1.context)),8,4)
389 AND puctif.jurisdiction_code like substr(c_locality_code,1,2)||'%'||
390 substr(c_locality_code,8,4)||'%'
391 AND puctif.effective_start_date < ppa.effective_date
392 AND puctif.effective_end_date >= ppa.effective_date
393 )
394 )
395 )
396 AND paf.effective_start_date <= ppa.effective_date
397 AND paf.effective_end_date >= ppa.start_date
398 AND paa.action_status = 'C'
399 AND paa.serial_number BETWEEN p_stperson AND p_endperson
400 AND paf.person_id BETWEEN p_stperson AND p_endperson
401 AND NOT EXISTS
402 (
403 SELECT 'x'
404 FROM hr_organization_information hoi
405 WHERE hoi.organization_id = paa.tax_unit_id
406 AND hoi.org_information_context = '1099R Magnetic Report Rules'
407 )
408 AND rtrim(ltrim(fai1.value)) <> '0'
409 GROUP BY paa.serial_number,
410 paf.assignment_id,
411 paa.tax_unit_id,
412 paf.effective_end_date,
413 paa.assignment_action_id
414 union all
415 SELECT
416 to_number(paa.serial_number),
417 paf.assignment_id,
418 paa.tax_unit_id,
419 paf.effective_end_date,
420 paa.assignment_action_id,
421 sum(fai1.value)
422 FROM
423 pay_assignment_actions paa, -- YREND PAA
424 pay_payroll_actions ppa, -- YREND PPA
425 per_all_assignments_f paf,
426 pay_payroll_actions ppa1,
427 ff_contexts fc1 , --for city context
428 ff_archive_items fai1, -- city
429 ff_archive_item_contexts faic1, -- city_context
430 ff_database_items fdi1
431
432 WHERE ppa1.payroll_action_id = p_pactid
433 AND ppa.business_group_id+0 = ppa1.business_group_id
434 AND ppa1.effective_date = ppa.effective_date
435 AND ppa.report_type = 'YREND'
436 AND ppa.payroll_action_id = paa.payroll_action_id
437 AND paf.assignment_id = paa.assignment_id
438 AND paf.assignment_type = 'E'
439 AND fc1.context_name = 'JURISDICTION_CODE'
440 AND faic1.context_id = fc1.context_id
441 AND fdi1.user_name = 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
442 AND fdi1.user_entity_id = fai1.user_entity_id
443 AND fai1.context1 = paa.assignment_action_id
444 AND fai1.archive_item_id = faic1.archive_item_id
445 AND ltrim(rtrim(faic1.context)) like c_state_code||'%'
446 AND (c_locality_code IS NULL OR
447 ( c_locality_code IS NOT NULL
448 AND EXISTS ( SELECT 'x' from PAY_US_CITY_SCHOOL_DSTS puctif
449 WHERE
450 puctif.state_code = c_state_code
451 and puctif.state_code ||'-'||
452 puctif.county_code || '-'|| puctif.city_code = c_locality_code
453 and c_state_code || '-'|| puctif.school_dst_code = ltrim(rtrim(faic1.context))
454
455 )
456 )
457 )
458 AND paf.effective_start_date <= ppa.effective_date
459 AND paf.effective_end_date >= ppa.start_date
460 AND paa.action_status = 'C'
461 AND paa.serial_number BETWEEN p_stperson AND p_endperson
462 AND paf.person_id BETWEEN p_stperson AND p_endperson
463 AND NOT EXISTS
464 (
465 SELECT 'x'
466 FROM hr_organization_information hoi
467 WHERE hoi.organization_id = paa.tax_unit_id
468 AND hoi.org_information_context = '1099R Magnetic Report Rules'
469 )
470 AND rtrim(ltrim(fai1.value)) <> '0'
471 GROUP BY paa.serial_number,
472 paf.assignment_id,
473 paa.tax_unit_id,
474 paf.effective_end_date,
475 paa.assignment_action_id
476 ORDER BY 1, 3, 4 DESC, 2;
477 --
478 -- LOCAL VARIABLES
479 --
480 l_year_start DATE;
481 l_year_end DATE;
482 l_effective_end_date DATE;
483 l_state_abbrev VARCHAR2(3);
484 l_state_code VARCHAR2(2);
485 l_report_type VARCHAR2(30);
486 l_business_group_id NUMBER;
487 l_person_id NUMBER;
488 l_prev_person_id NUMBER;
489 l_assignment_id NUMBER;
490 l_assignment_action_id NUMBER;
491 l_value NUMBER;
492 l_tax_unit_id NUMBER;
493 l_prev_tax_unit_id NUMBER;
494 lockingactid NUMBER;
495 l_group_by_gre BOOLEAN;
496 l_w2_box17 NUMBER; --City or Locality Wages
497 l_gre_id NUMBER;
498 l_error_flag VARCHAR2(10);
499 l_locality_code VARCHAR2(200);
500 --
501 BEGIN
502 -- Set the local variable to correct Value
503 l_gre_id := -1;
504 l_error_flag := 'N';
505
506 -- Get the report parameters. These define the report being run.
507 hr_utility.set_location( 'pay_us_mmref_local_xml.create_assignement_act', 10);
508 get_report_parameters( p_pactid,
509 l_year_start,
510 l_year_end,
511 l_state_abbrev,
512 l_state_code,
513 l_report_type,
514 l_business_group_id,
515 l_locality_code
516 );
517 IF l_locality_code = 'NULL' THEN
518 l_locality_code := NULL;
519 END IF;
520
521 --Currently all reports group by GRE
522 l_group_by_gre := TRUE;
523 --Open the appropriate cursor
524 hr_utility.set_location( 'pay_us_mmref_local_xml.create_assignement_act', 20);
525 hr_utility.trace('LOCALITY_CODE : '|| l_locality_code);
526 IF l_report_type = 'W2_MAG_XML' THEN
527 OPEN c_local(l_state_code,
528 l_locality_code);
529 END IF;
530 LOOP
531 FETCH c_local INTO l_person_id,
532 l_assignment_id,
533 l_tax_unit_id,
534 l_effective_end_date,
535 l_assignment_action_id,
536 l_w2_box17;
537 hr_utility.set_location(
538 'pay_us_mmref_local_xml.create_assignement_act', 40);
539 EXIT WHEN c_local%NOTFOUND;
540 --Based on the groupin criteria, check if the record is the same
541 --as the previous record.
542 --Grouping by GRE requires a unique person/GRE combination for
543 --each record.
544 IF ( (l_group_by_gre AND
545 l_person_id = l_prev_person_id AND
546 l_tax_unit_id = l_prev_tax_unit_id
547 )
548 OR
549 ( NOT l_group_by_gre AND
550 l_person_id = l_prev_person_id
551 )
552 ) THEN
553 --{
554 --Do Nothing
555 hr_utility.set_location(
556 'pay_us_mmref_local_xml.create_assignement_act', 50);
557 NULL;
558 --}
559 ELSE
560 --{
561 --Create the assignment action for the record
562 hr_utility.trace('Assignment Fetched - ');
563 hr_utility.trace('Assignment Id : '|| to_char(l_assignment_id));
564 hr_utility.trace('Person Id : '|| to_char(l_person_id));
565 hr_utility.trace('tax unit id : '|| to_char(l_tax_unit_id));
566 hr_utility.trace('Effective End Date : '|| to_char(l_effective_end_date));
567 IF (l_report_type = 'W2_MAG_XML') then
568 SELECT pay_assignment_actions_s.nextval
569 INTO lockingactid
570 FROM dual;
571 hr_utility.set_location(
572 'pay_us_mmref_local_xml.create_assignement_act', 60);
573 hr_nonrun_asact.insact( lockingactid,
574 l_assignment_id,
575 p_pactid,
576 p_chunk,
577 l_tax_unit_id);
578 hr_utility.set_location(
579 'pay_us_mmref_local_xml.create_assignement_act', 70);
580 hr_nonrun_asact.insint(lockingactid,
581 l_assignment_action_id);
582 hr_utility.set_location(
583 'pay_us_mmref_local_xml.create_assignement_act', 80);
584 hr_utility.trace('Interlock Created - ');
585 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
586 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
587 --Store the current person/GRE for comparision during the
588 --next iteration.
589 l_prev_person_id := l_person_id;
590 l_prev_tax_unit_id := l_tax_unit_id;
591 END IF;
592 ENd IF;
593 END LOOP;
594 IF l_report_type = 'W2_MAG_XML' THEN
595 CLOSE c_local;
596 END IF;
597
598 IF l_error_flag = 'Y' THEN
599 hr_utility.trace('Error Flag was set to Y');
600 hr_utility.raise_error;
601 END IF;
602
603 END create_assignment_act;
604 --
605 -- Follwing Procedure is used for Submitter Record
606 --
607 PROCEDURE transmitter_record_start IS
608 l_final_xml_string VARCHAR2(32000);
609 EOL VARCHAR2(10);
610 p_payroll_action_id NUMBER;
611 p_tax_unit_id NUMBER;
612 p_jurisdiction_code VARCHAR2(200);
613 p_state_code NUMBER;
614 p_state_abbreviation VARCHAR2(200);
615 p_locality_code VARCHAR2(200);
616 status VARCHAR2(200);
617 p_date_earned DATE;
618 p_reporting_year VARCHAR2(200);
619 p_final_xml_string VARCHAR2(32767);
620 BEGIN
621 --{
622 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
623 p_tax_unit_id := pay_magtape_generic.get_parameter_value('TR_TAX_UNIT_ID');
624 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
625 p_date_earned := pay_magtape_generic.get_parameter_value('TR_DATE_EARNED');
626 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
627 --
628 -- Following Procedure Call will form the RA Record Structure in XML format
629 --
630 pay_us_w2_generic_extract.populate_arch_transmitter(
631 p_payroll_action_id
632 ,p_tax_unit_id
633 ,p_date_earned
634 ,p_reporting_year
635 ,p_jurisdiction_code
636 ,p_state_code
637 ,p_state_abbreviation
638 ,p_locality_code
639 ,status
640 ,p_final_xml_string);
641 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
642 || p_final_xml_string);
643 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
644 --}
645 END transmitter_record_start;
646
647 PROCEDURE transmitter_record_end is
648 l_final_xml CLOB;
649 l_final_xml_string VARCHAR2(32000);
650 l_is_temp_final_xml VARCHAR2(2);
651
652 BEGIN
653 l_final_xml_string := '</TRANSMITTER>';
654 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
655 || l_final_xml_string);
656 WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
657 -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
658 END;
659
660 --
661 -- Follwing Procedure is used for Employer Record
662 --
663 PROCEDURE local_w2_xml_employer_start
664 IS
665 l_final_xml_string VARCHAR2(32000);
666 EOL VARCHAR2(10);
667 p_payroll_action_id NUMBER;
668 p_tax_unit_id NUMBER;
669 p_jurisdiction_code VARCHAR2(200);
670 p_state_code NUMBER;
671 p_state_abbreviation VARCHAR2(200);
672 p_locality_code VARCHAR2(200);
673 status VARCHAR2(200);
674 p_date_earned DATE;
675 p_reporting_year VARCHAR2(200);
676 p_final_xml_string VARCHAR2(32767);
677 BEGIN
678 --{
679 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
680 p_tax_unit_id := pay_magtape_generic.get_parameter_value('TR_TAX_UNIT_ID');
681 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
682 p_date_earned := pay_magtape_generic.get_parameter_value('TR_DATE_EARNED');
683 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
684 --
685 -- Following Procedure Call will form the RE Record Structure in XML format
686 --
687 pay_us_w2_generic_extract.populate_arch_employer(
688 p_payroll_action_id
689 ,p_tax_unit_id
690 ,p_date_earned
691 ,p_reporting_year
692 ,p_jurisdiction_code
693 ,p_state_code
694 ,p_state_abbreviation
695 ,p_locality_code
696 ,status
697 ,p_final_xml_string);
698 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
699 || p_final_xml_string);
700 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
701 --}
702 END local_w2_xml_employer_start;
703
704 PROCEDURE local_w2_xml_employer_end is
705 l_final_xml CLOB;
706 l_final_xml_string VARCHAR2(32000);
707 l_is_temp_final_xml VARCHAR2(2);
708
709 BEGIN
710 l_final_xml_string := '</EMPLOYER>';
711 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
712 || l_final_xml_string);
713 WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
714 -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
715 END local_w2_xml_employer_end;
716
717 --
718 -- Follwing Procedure is used for Employer Record
719 --
720 PROCEDURE local_w2_xml_employee_build
721 IS
722 l_final_xml_string VARCHAR2(32000);
723 EOL VARCHAR2(10);
724 p_payroll_action_id NUMBER;
725 p_ye_assignment_action_id NUMBER;
726 p_assignment_action_id NUMBER;
727 p_assignment_id NUMBER;
728 p_tax_unit_id NUMBER;
729 p_jurisdiction_code VARCHAR2(200);
730 p_state_code NUMBER;
731 p_state_abbreviation VARCHAR2(200);
732 p_locality_code VARCHAR2(200);
733 status VARCHAR2(200);
734 p_date_earned DATE;
735 p_reporting_year VARCHAR2(200);
736 p_final_xml_string VARCHAR2(32767);
737
738 CURSOR c_get_params IS
739 SELECT paa1.assignment_action_id, -- archiver asg action Id
740 paa1.tax_unit_id, -- archiver Tax Unit Id
741 paa1.payroll_action_id, -- archiver payroll action id
742 ppa.payroll_action_id, -- Main Payroll Action Id
743 paa.assignment_action_id, -- Main Asg Action Id
744 paa.assignment_id,
745 ppa.effective_date, -- Date Earned
746 pay_us_mmref_local_xml.get_parameter('TRANSFER_REPORTING_YEAR',
747 ppa.legislative_parameters),
748 pay_us_mmref_local_xml.get_parameter('LC',ppa.legislative_parameters),
749 pay_us_mmref_local_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters)
750 FROM pay_assignment_actions paa,
751 pay_payroll_actions ppa,
752 pay_action_interlocks pai,
753 pay_assignment_actions paa1,
754 pay_payroll_actions ppa1
755 where ppa.payroll_action_id = paa.payroll_action_id
756 and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
757 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
758 and pai.locking_action_id = paa.assignment_action_id
759 and pai.locked_action_id = paa1.assignment_action_id
760 and paa1.payroll_action_id = ppa1.payroll_action_id
761 and ppa1.report_type = 'YREND'
762 and ppa1.action_type = 'X'
763 and ppa1.action_status = 'C'
764 and ppa1.effective_date = ppa.effective_date;
765
766 l_year_start DATE;
767 l_year_end DATE;
768 l_business_group_id NUMBER;
769 l_state_abbrev VARCHAR2(200);
770 l_state_code VARCHAR2(200);
771 l_report_type VARCHAR2(200);
772 l_locality_code VARCHAR2(200);
773 l_main_payroll_action_id NUMBER;
774 BEGIN
775 --{
776 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
777 HR_UTILITY_TRACE('Constructing XML for Employee ->');
778 HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
779 OPEN c_get_params;
780 FETCH c_get_params INTO p_ye_assignment_action_id,
781 p_tax_unit_id,
782 p_payroll_action_id,
783 l_main_payroll_action_id,
784 p_assignment_action_id,
785 p_assignment_id,
786 p_date_earned,
787 p_reporting_year,
788 p_locality_code,
789 p_state_abbreviation;
790 CLOSE c_get_params;
791 /*
792 p_tax_unit_id := pay_magtape_generic.get_parameter_value('YE_TAX_UNIT_ID');
793 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
794 p_assignment_action_id :=
795 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
796 p_ye_assignment_action_id :=
797 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID');
798 p_assignment_id := pay_magtape_generic.get_parameter_value('EE_ASSIGNMENT_ID');
799 p_date_earned := pay_magtape_generic.get_parameter_value('EE_DATE_EARNED');
800 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
801 p_locality_code := pay_magtape_generic.get_parameter_value('TRANSFER_LOCALITY_CODE');
802 p_jurisdiction_code := pay_magtape_generic.get_parameter_value('EE_LOCALITY_JD_CODE');
803 */
804 HR_UTILITY_TRACE('Prameter Used for Employee level XML');
805 HR_UTILITY_TRACE('p_tax_unit_id YE :'||to_char(p_tax_unit_id));
806 HR_UTILITY_TRACE('p_payroll_action_id YE :'||to_char(p_payroll_action_id));
807 HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(l_main_payroll_action_id));
808 HR_UTILITY_TRACE('p_ye_assignment_action_id :'||to_char(p_ye_assignment_action_id));
809 HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
810 HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
811 HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
812 HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
813 HR_UTILITY_TRACE('p_locality_code :'||p_locality_code);
814 -- HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
815
816 get_report_parameters( l_main_payroll_action_id,
817 l_year_start,
818 l_year_end,
819 l_state_abbrev,
820 l_state_code,
821 l_report_type,
822 l_business_group_id,
823 l_locality_code
824 );
825
826 HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
827 HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
828 HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
829 HR_UTILITY_TRACE('State Code :'||l_state_code);
830 HR_UTILITY_TRACE('Report Type :'||l_report_type);
831 HR_UTILITY_TRACE('Locality Code :'||l_locality_code);
832
833 --
834 -- Following Procedure Call will form the RE Record Structure in XML format
835 --
836 pay_us_w2_generic_extract.populate_arch_employee(
837 p_payroll_action_id
838 ,p_ye_assignment_action_id
839 ,p_tax_unit_id
840 ,p_assignment_id
841 ,p_date_earned
842 ,p_reporting_year
843 ,p_jurisdiction_code
844 ,l_state_code
845 ,l_state_abbrev
846 ,l_locality_code
847 ,status
848 ,p_final_xml_string
849 );
850
851 --HR_UTILITY_TRACE('end_of_file p_final_xml_string = '
852 -- || p_final_xml_string);
853 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
854 --}
855 EXCEPTION
856 WHEN OTHERS THEN
857 HR_UTILITY_TRACE('Error Encountered in local_w2_xml_employee_build');
858 HR_UTILITY_TRACE(sqlerrm);
859 END local_w2_xml_employee_build;
860
861 /****************************************************************************
862 Name : PRINT_BLOB
863 Description : This procedure prints contents of BLOB passed as parameter.
864 *****************************************************************************/
865
866 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
867 BEGIN
868 IF g_debug THEN
869 pay_ac_utility.print_lob(p_blob);
870 END IF;
871 END PRINT_BLOB;
872
873
874 /****************************************************************************
875 Name : WRITE_TO_MAGTAPE_LOB
876 Description : This procedure appends passed BLOB parameter to
877 pay_mag_tape.g_blob_value
878 *****************************************************************************/
879
880 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
881 BEGIN
882 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
883 pay_core_files.write_to_magtape_lob (p_blob);
884 END IF;
885 END WRITE_TO_MAGTAPE_LOB;
886
887
888 /****************************************************************************
889 Name : WRITE_TO_MAGTAPE_LOB
890 Description : This procedure appends passed varchar2 parameter to
891 pay_mag_tape.g_blob_value
892 *****************************************************************************/
893
894 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
895 BEGIN
896 pay_core_files.write_to_magtape_lob (p_data);
897 END WRITE_TO_MAGTAPE_LOB;
898
899
900 ------------------------------ get_parameter -------------------------------
901 function get_parameter(name in varchar2,
902 parameter_list varchar2) return varchar2
903 is
904 start_ptr number;
905 end_ptr number;
906 token_val pay_payroll_actions.legislative_parameters%type;
907 par_value pay_payroll_actions.legislative_parameters%type;
908 begin
909 --
910 token_val := name||'=';
911 --
912 start_ptr := instr(parameter_list, token_val) + length(token_val);
913 end_ptr := instr(parameter_list, ' ',start_ptr);
914
915 --
916 /* if there is no spaces use then length of the string */
917 if end_ptr = 0 then
918 end_ptr := length(parameter_list)+1;
919 end if;
920 --
921 /* Did we find the token */
922 if instr(parameter_list, token_val) = 0 then
923 par_value := NULL;
924 else
925 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
926 end if;
927 --
928 return par_value;
929 --
930 end get_parameter;
931 PROCEDURE local_non_pa_emp_data(p_pactid IN varchar2 ,
932 p_assignment_id in varchar2 ,
933 on_visa in out nocopy varchar2 ,
934 non_pa_res in out nocopy varchar2 ,
935 p_reporting_year in varchar2) IS
936
937 l_year number ;
938 p_person_id number ;
939 l_non_pa_res varchar2(5);
940 l_on_visa varchar2(5);
941 -- l_year_start := '01-JAN-' ||p_reporting_year ;
942 -- l_year_end := '31-DEC-' || p_reporting_year ;
943
944 cursor c_get_person_id (p_assignment_id in number) is
945 select person_id from per_all_assignments_f
946 where assignment_id = p_assignment_id ;
947
948 cursor c_get_non_pa_emp_det( p_person_id in number ) is
949 select 'Y' from dual
950 where not exists
951 ( select person_id from per_addresses
952 where person_id = p_person_id
953 and region_2 = 'PA'
954 and primary_flag = 'Y'
955 and l_year between to_number(to_char(trunc(date_from,'yyyy'),'yyyy')) and to_number(nvl(to_char(trunc(date_to,'yyyy'),'yyyy') , '4712')));
956
957
958 cursor c_get_visa_details( p_person_id in number ) is
959 select 'Y' from per_people_extra_info
960 where person_id = p_person_id
961 and information_type = 'PER_US_VISA_DETAILS'
962 and pei_information_category = 'PER_US_VISA_DETAILS' ;
963
964 BEGIN
965 -- l_non_pa_res := non_pa_res ;
966 l_year := to_number(p_reporting_year) ;
967
968 open c_get_person_id(p_assignment_id) ;
969 fetch c_get_person_id into p_person_id ;
970 -- exit when c_get_person_id%notfound ;
971 close c_get_person_id ;
972
973 open c_get_non_pa_emp_det(p_person_id) ;
974 fetch c_get_non_pa_emp_det into l_non_pa_res ;
975 close c_get_non_pa_emp_det ;
976
977 open c_get_visa_details(p_person_id) ;
978 fetch c_get_visa_details into l_on_visa ;
979 close c_get_visa_details ;
980
981 non_pa_res := l_non_pa_res ;
982 on_visa := l_on_visa ;
983
984
985 END local_non_pa_emp_data;
986 BEGIN
987 -- hr_utility.trace_on(null, 'USLOCALW2');
988 g_proc_name := 'PAY_US_MMREF_LOCAL_XML.';
989 g_debug := hr_utility.debug_enabled;
990 g_document_type := 'LOCAL_W2_XML';
991
992 END pay_us_mmref_local_xml;
[Home] [Help]