[Home] [Help]
PACKAGE BODY: APPS.PAY_US_PSD_XML
Source
1 PACKAGE BODY pay_us_psd_xml AS
2 /* $Header: payuspsdxml.pkb 120.3.12020000.15 2012/12/11 19:23:04 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.pkb
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 19-jun-2012 PRACAGRA 115.5 11712075 Removed the extra spaces from GRE name in the file name.
43 26-jun-2012 PRACAGRA 115.6 14373899 Modified hoi.org_information10 to
44 substr(hoi.org_information10,1,6)
45 because of the changes done to
46 'PAY_US_PSD_CODES' valueset ID.
47 24-aug-2012 PRACAGRA 115.7 14379256 Modified the code to accomodate
48 multiple jurisdiction codes for single asg.
49 27-Aug-2012 SKCHALLA 115.8 14379256 Removed the 'Continue' key word.
50 19-Sep-2012 PRACAGRA 115.9 14640336 Replaced asg_id with person_id
51 in get_jurisdiction cursors.
52 21-Sep-2012 PRACAGRA 115.10 Modified the report file name to omit
53 any special characters in the gre name.
54 28-Sep-2012 SKCHALLA 115.11 14690909 Modified the logic to get the person ID,
55 as archiver is failed to archive the employee
56 having assignment history.
57 28-Sep-2012 SKCHALLA 115.12 14690909 Modified the person_id logic used in the above version.
58 22-Nov-2012 SKCHALLA 115.13 14799833 Modified for the Yearly reporting.
59 05-Dec-2012 SKCHALLA 115.14 14799833 Modified range cursor and preprocess_check
60 for Yearly reporting.
61 05-Dec-2012 SKCHALLA 115.15 14799833 Modified preprocess_check for Yearly reporting.
62 06-Dec-2012 SKCHALLA 115.16 14799833 Modified preprocess_check for Yearly reporting.
63 11-Dec-2012 SKCHALLA 115.17 15944697 Modified the ranage cursor for Yearly reporting
64 ============================================================================
65 */
66 --
67 -- Global Variables
68 --
69 g_proc_name varchar2(240);
70 g_debug boolean;
71 g_document_type varchar2(50);
72 function chk_gre_archive (p_payroll_action_id number) return boolean;
73 function get_user_entity_id (p_dbi_name in varchar2) return number;
74
75 /****************************************************************************
76 Name : HR_UTILITY_TRACE
77 Description : This procedure prints debug messages.
78 *****************************************************************************/
79
80 PROCEDURE HR_UTILITY_TRACE
81 (
82 P_TRC_DATA varchar2
83 ) AS
84 BEGIN
85 IF g_debug THEN
86 hr_utility.trace(p_trc_data);
87 END IF;
88 END HR_UTILITY_TRACE;
89
90 /*
91 -------------------------------------------------------------------------------------------------------
92 -- Name : bal_db_item
93 -- Purpose : Given the name of a balance DB item as would be seen in a
94 -- fast formula it returns the defined_balance_id of the balance it represents.
95 -- Arguments
96 -- INPUT: p_db_item_name
97 -- RETURNS : l_defined_balance_id
98 -- Notes
99 -- A defined_balance_id is required by the PLSQL balance function.
100 --------------------------------------------------------------------------------------------------------
101 */
102 FUNCTION bal_db_item (p_db_item_name VARCHAR2
103 ) RETURN NUMBER IS
104 -- Get the defined_balance_id for the specified balance DB item.
105 CURSOR csr_defined_balance IS
106 SELECT TO_NUMBER(UE.creator_id)
107 FROM ff_database_items DI,
108 ff_user_entities UE
109 WHERE DI.user_name = p_db_item_name
110 AND UE.user_entity_id = DI.user_entity_id
111 AND UE.creator_type = 'B'
112 AND UE.legislation_code = 'US';
113
114 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
115 BEGIN
116 hr_utility.set_location
117 ('pay_us_psd_xml.bal_db_item - opening cursor', 10);
118 -- Open the cursor
119 OPEN csr_defined_balance;
120 -- Fetch the value
121 FETCH csr_defined_balance
122 INTO l_defined_balance_id;
123 IF csr_defined_balance%NOTFOUND THEN
124 CLOSE csr_defined_balance;
125 hr_utility.set_location
126 ('pay_us_psd_xml.bal_db_item - no rows found from cursor', 20);
127 hr_utility.raise_error;
128 ELSE
129 hr_utility.set_location
130 ('pay_us_psd_xml.bal_db_item - fetched from cursor', 30);
131 CLOSE csr_defined_balance;
132 END IF;
133 -- Return the value to the call
134 RETURN (l_defined_balance_id);
135 END bal_db_item;
136
137 /* Name : get_user_entity_id
138 Purpose : This gets the user_entity_id for a specific database item name.
139 Arguments : p_dbi_name -> database item name.
140 Notes :
141 */
142
143 function get_user_entity_id (p_dbi_name in varchar2)
144 return number is
145 l_user_entity_id ff_database_items.user_entity_id%type;
146
147 begin
148
149 select user_entity_id
150 into l_user_entity_id
151 from ff_database_items
152 where user_name = p_dbi_name;
153
154 return l_user_entity_id;
155
156 exception
157 when others then
158 hr_utility.trace('Error while getting the user_entity_id'
159 || to_char(sqlcode));
160 raise hr_utility.hr_error;
161
162 end get_user_entity_id;
163
164 -------------------------------------------------------------------------
165 -- Name : get_parameter
166 --
167 --Purpose
168 -- Get the value of the specified parameter from the parameter list
169 --Arguments
170 -- name Name of the parameter whose value needs to be returned
171 -- parameter_list List of all the parameters
172 --Note
173 -------------------------------------------------------------------------
174
175
176 function get_parameter(name in varchar2,
177 parameter_list varchar2) return varchar2
178 is
179 start_ptr number;
180 end_ptr number;
181 token_val pay_payroll_actions.legislative_parameters%type;
182 par_value pay_payroll_actions.legislative_parameters%type;
183 begin
184 --
185 token_val := name||'=';
186 --
187 start_ptr := instr(parameter_list, token_val) + length(token_val);
188 end_ptr := instr(parameter_list, ' ',start_ptr);
189
190 --
191 /* if there is no spaces use then length of the string */
192 if end_ptr = 0 then
193 end_ptr := length(parameter_list)+1;
194 end if;
195 --
196 /* Did we find the token */
197 if instr(parameter_list, token_val) = 0 then
198 par_value := NULL;
199 else
200 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
201 end if;
202 --
203 return par_value;
204 --
205 end get_parameter;
206
207
208 /*********************************************************************
209 Name : get_defined_balance_id
210 Purpose : This function returns the defined_balance_id for a given
211 Balance Name and Dimension.
212 The function is used to get the defined_balance_id
213 of the Balance Names stored in the lookup
214 'PSD WAGE LISTING RPT BALANCES '.
215 Arguments :
216 Notes :
217 *********************************************************************/
218 FUNCTION get_defined_balance_id(
219 p_business_group_id in number
220 ,p_balance_name in varchar2
221 ,p_balance_dimension in varchar2)
222 RETURN NUMBER
223 IS
224
225 cursor c_get_defined_balance_id (
226 cp_business_group_id in number,
227 cp_balance_name in varchar2,
228 cp_balance_dimension in varchar2 ) is
229 select pdb.defined_balance_id
230 from pay_defined_balances pdb,
231 pay_balance_dimensions pbd,
232 pay_balance_types pbt
233 where pbt.balance_name = cp_balance_name
234 and pbd.database_item_suffix= cp_balance_dimension
235 and pbt.balance_type_id = pdb.balance_type_id
236 and pbd.balance_dimension_id = pdb.balance_dimension_id
237 and ((pbt.legislation_code = 'US' and
238 pbt.business_group_id is null)
239 or (pbt.legislation_code is null and
240 pbt.business_group_id = cp_business_group_id))
241 and ((pdb.legislation_code ='US' and
242 pdb.business_group_id is null)
243 or (pdb.legislation_code is null and
244 pdb.business_group_id = cp_business_group_id));
245
246 ln_defined_balance_id NUMBER;
247
248 BEGIN
249 hr_utility.trace('opened c_get_defined_balance');
250 open c_get_defined_balance_id(p_business_group_id,
251 p_balance_name,
252 p_balance_dimension);
253
254 fetch c_get_defined_balance_id into ln_defined_balance_id;
255 if c_get_defined_balance_id%notfound then
256 hr_utility.trace('Defined balance Id not found');
257 -- Do not error out if the defined_balance_id does not exist
258 -- Pass Null instead.
259 end if;
260 close c_get_defined_balance_id;
261 hr_utility.trace('ln_defined_balance_id = ' ||
262 to_char(ln_defined_balance_id));
263
264 return (ln_defined_balance_id);
265
266 END get_defined_balance_id;
267
268 /******************************************************************
269 Name : get_balance_value
270 Purpose : This procedure calls the get_value function for
271 balance calls.
272 Arguments :
273 Notes :
274 ******************************************************************/
275 FUNCTION get_balance_value(
276 p_defined_balance_id in number
277 ,p_balcall_aaid in number)
278 RETURN NUMBER
279 IS
280 lv_error_message VARCHAR2(200);
281 ln_bal_value NUMBER;
282
283 BEGIN
284
285 if p_defined_balance_id is not null then
286 ln_bal_value := fnd_number.number_to_canonical(
287 nvl(pay_balance_pkg.get_value(
288 p_defined_balance_id,
289 p_balcall_aaid),0));
290 end if;
291 return (ln_bal_value);
292
293 EXCEPTION
294 when others then
295 return (null);
296 END get_balance_value;
297
298
299 /****************************************************************************
300 Name : PRINT_BLOB
301 Description : This procedure prints contents of BLOB passed as parameter.
302 *****************************************************************************/
303
304 PROCEDURE PRINT_BLOB(p_blob BLOB) IS
305 BEGIN
306 IF g_debug THEN
307 pay_ac_utility.print_lob(p_blob);
308 END IF;
309 END PRINT_BLOB;
310
311
312 /****************************************************************************
313 Name : WRITE_TO_MAGTAPE_LOB
314 Description : This procedure appends passed BLOB parameter to
315 pay_mag_tape.g_blob_value
316 *****************************************************************************/
317
318 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_blob BLOB) IS
319 BEGIN
320 IF dbms_lob.getLength (p_blob) IS NOT NULL THEN
321 pay_core_files.write_to_magtape_lob (p_blob);
322 END IF;
323 END WRITE_TO_MAGTAPE_LOB;
324
325
326 /****************************************************************************
327 Name : WRITE_TO_MAGTAPE_LOB
328 Description : This procedure appends passed varchar2 parameter to
329 pay_mag_tape.g_blob_value
330 *****************************************************************************/
331
332 PROCEDURE WRITE_TO_MAGTAPE_LOB(p_data varchar2) IS
333 BEGIN
334 pay_core_files.write_to_magtape_lob (p_data);
335 END WRITE_TO_MAGTAPE_LOB;
336
337 -----------------------------------------------------------------------------
338 -- Name: get_report_parameters
339 -- Purpose
340 -- The procedure gets the 'parameter' for which the report is being
341 -- run i.e., the period, state and business organization.
342 -- Arguments
343 -- p_year_start Start Date of the year for which the report
344 -- has been requested
345 -- p_year_end End date of the year
346 -- p_business_group_id Business group for which the report is being run
347 -- p_state_abbrev Two digit state abbreviation (or 'FED' for federal
348 -- report)
349 -- p_state_code State code (NULL for federal)
350 -- p_report_type PSD_LOCAL_XML
351 -- p_period_type Where the report is run on Quarterly or Monthly basis
352 -- p_year_start Start Date of the period for which the report
353 -- has been requested
354 -- p_year_end End date of the period
355 -- Notes
356 ----------------------------------------------------------------------------
357 PROCEDURE get_report_parameters
358 ( p_pactid IN NUMBER,
359 p_year_start IN OUT NOCOPY DATE,
360 p_year_end IN OUT NOCOPY DATE,
361 p_state_abbrev IN OUT NOCOPY VARCHAR2,
362 p_state_code IN OUT NOCOPY VARCHAR2,
363 p_report_type IN OUT NOCOPY VARCHAR2,
364 p_business_group_id IN OUT NOCOPY NUMBER,
365 p_period_type IN OUT NOCOPY VARCHAR2,
366 p_period_start IN OUT NOCOPY DATE,
367 p_period_end IN OUT NOCOPY DATE,
368 p_tax_coll_dist_code IN OUT NOCOPY VARCHAR2
369 ) IS
370 l_state_code pay_us_states.state_code%type;
371 l_period_start varchar2(200);
372 l_period_end varchar2(200);
373 BEGIN
374 hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 10);
375 hr_utility.trace('Payroll_Action_Id '|| to_char(p_pactid));
376
377 SELECT ppa.start_date,
378 ppa.effective_date,
379 ppa.business_group_id,
380 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
381 'TRANSFER_STATE'),
382 ppa.report_type,
383 pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
384 'TRANSFER_PERIOD_TYPE'),
385 decode (pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
386 'TRANSFER_TAX_COLL_DIST_CODE'),'NULL',NULL, pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
387 'TRANSFER_TAX_COLL_DIST_CODE'))
388 INTO p_year_start,
389 p_year_end,
390 p_business_group_id,
391 p_state_abbrev,
392 p_report_type,
393 p_period_type,
394 p_tax_coll_dist_code
395 FROM pay_payroll_actions ppa
396 WHERE ppa.payroll_action_id = p_pactid;
397 --
398 select state_code into l_state_code
399 from pay_us_states pus
400 where pus.state_abbrev = p_state_abbrev;
401
402 p_state_code := l_state_code;
403 /* SELECT fnd_date.canonical_to_date(pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
404 'TRANSFER_DATE'))
405 INTO p_period_end
406 FROM pay_payroll_actions ppa
407 WHERE ppa.payroll_action_id = p_pactid;*/
408
409 SELECT to_date(pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
410 'TRANSFER_DATE'), 'DD-MON-YYYY')
411 INTO p_period_end
412 FROM pay_payroll_actions ppa
413 WHERE ppa.payroll_action_id = p_pactid;
414
415 if (p_period_type='Quarterly') then
416 p_period_start:= trunc(p_period_end,'Q');
417 elsif (p_period_type='Monthly') then
418 p_period_start:= trunc(p_period_end,'MM');
419 else --Bug 14799833
420 p_period_start:= trunc(p_period_end,'YY');
421 end if;
422
423
424 hr_utility.set_location('pay_us_psd_xml.get_report_parameters', 15);
425 hr_utility.trace('Parameter Values ');
426 hr_utility.trace('Year Start '|| to_char(p_year_start,'dd-mon-yyyy'));
427 hr_utility.trace('Year End '|| to_char(p_year_end,'dd-mon-yyyy'));
428 hr_utility.trace('Business Group Id '|| to_char(p_business_group_id));
429 hr_utility.trace('p_state_abbrev '|| p_state_abbrev);
430 hr_utility.trace('p_state_code '|| p_state_code);
431 hr_utility.trace('p_report_type '|| p_report_type);
432 hr_utility.trace('p_period_type '|| p_period_type);
433 hr_utility.trace('p_period_start '|| p_period_start);
434 hr_utility.trace('p_period_end '|| p_period_end);
435 hr_utility.trace('p_tax_coll_dist_code '|| p_tax_coll_dist_code);
436
437 hr_utility.set_location
438 ('PAY_US_PSD_XML.get_report_parameters', 40);
439 EXCEPTION
440 WHEN OTHERS THEN
441 hr_utility.trace('get_report_parameters procedure Raised Exception ');
442 hr_utility.trace('ERROR '||substr(SQLERRM,1,40));
443 hr_utility.trace(substr(SQLERRM,41,90));
444 END get_report_parameters;
445 --
446 -- Follwing Procedure is used for Submitter Record
447 --
448 PROCEDURE transmitter_record_start IS
449 l_final_xml_string VARCHAR2(32000);
450 EOL VARCHAR2(10);
451 p_payroll_action_id NUMBER;
452 p_tax_unit_id NUMBER;
453 p_jurisdiction_code VARCHAR2(200);
454 p_state_code NUMBER;
455 p_state_abbreviation VARCHAR2(200);
456 status VARCHAR2(200);
457 p_date_earned DATE;
458 p_reporting_year VARCHAR2(200);
459 p_final_xml_string VARCHAR2(32767);
460 BEGIN
461 --{
462 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
463 p_tax_unit_id := pay_magtape_generic.get_parameter_value('TR_TAX_UNIT_ID');
464 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
465 p_date_earned := pay_magtape_generic.get_parameter_value('TR_DATE_EARNED');
466 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
467 --
468 -- Following Procedure Call will form the RA Record Structure in XML format
469 --
470 pay_us_psd_generic_extract.populate_arch_transmitter(
471 p_payroll_action_id
472 ,p_tax_unit_id
473 ,p_date_earned
474 ,p_reporting_year
475 ,p_jurisdiction_code
476 ,p_state_code
477 ,p_state_abbreviation
478 ,status
479 ,p_final_xml_string);
480 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
481 || p_final_xml_string);
482 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
483 --}
484 END transmitter_record_start;
485
486 PROCEDURE transmitter_record_end is
487 l_final_xml CLOB;
488 l_final_xml_string VARCHAR2(32000);
489 l_is_temp_final_xml VARCHAR2(2);
490
491 BEGIN
492 l_final_xml_string := '</TRANSMITTER>';
493 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
494 || l_final_xml_string);
495 WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
496 -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
497 END;
498
499 --
500 -- Follwing Procedure is used for Employer Record
501 --
502 PROCEDURE psd_xml_employer_start
503 IS
504 l_final_xml_string VARCHAR2(32000);
505 EOL VARCHAR2(10);
506 p_payroll_action_id NUMBER;
507 p_tax_unit_id NUMBER;
508 p_jurisdiction_code VARCHAR2(200);
509 p_state_code NUMBER;
510 p_state_abbreviation VARCHAR2(200);
511 status VARCHAR2(200);
512 p_date_earned DATE;
513 p_reporting_year VARCHAR2(200);
514 p_final_xml_string VARCHAR2(32767);
515 BEGIN
516 --{
517 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
518
519 --p_tax_unit_id := pay_magtape_generic.get_parameter_value('TR_TAX_UNIT_ID');
520
521 p_tax_unit_id := pay_magtape_generic.get_parameter_value('TAX_UNIT_ID');
522 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
523 p_date_earned := pay_magtape_generic.get_parameter_value('TR_DATE_EARNED');
524 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
525 --
526 -- Following Procedure Call will form the RE Record Structure in XML format
527 --
528
529 pay_us_psd_generic_extract.populate_arch_employer(
530 p_payroll_action_id
531 ,p_tax_unit_id
532 ,p_date_earned
533 ,p_reporting_year
534 ,p_jurisdiction_code
535 ,p_state_code
536 ,p_state_abbreviation
537 ,status
538 ,p_final_xml_string);
539 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
540 || p_final_xml_string);
541 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
542 --}
543 END psd_xml_employer_start;
544
545 PROCEDURE psd_xml_employer_end is
546 l_final_xml CLOB;
547 l_final_xml_string VARCHAR2(32000);
548 l_is_temp_final_xml VARCHAR2(2);
549
550 BEGIN
551 l_final_xml_string := '</EMPLOYER>';
552 HR_UTILITY_TRACE('end_of_file l_final_xml_string = '
553 || l_final_xml_string);
554 WRITE_TO_MAGTAPE_LOB(l_final_xml_string);
555 -- pay_core_files.write_to_magtape_lob(l_final_xml_string);
556 END psd_xml_employer_end;
557
558 --
559 -- Follwing Procedure is used for Employee Record
560 --
561 PROCEDURE psd_xml_employee_build
562 IS
563 l_final_xml_string VARCHAR2(32000);
564 EOL VARCHAR2(10);
565 p_payroll_action_id NUMBER;
566 p_assignment_action_id NUMBER;
567 p_assignment_id NUMBER;
568 p_tax_unit_id NUMBER;
569 p_jurisdiction_code VARCHAR2(200);
570 p_state_code NUMBER;
571 p_state_abbreviation VARCHAR2(200);
572 p_period_type VARCHAR2(200);
573 p_period_start DATE;
574 p_period_end DATE;
575 status VARCHAR2(200);
576 p_date_earned DATE;
577 p_reporting_year VARCHAR2(200);
578 p_final_xml_string VARCHAR2(32767);
579 yepp_act_id number; --added for Bug 14799833
580
581
582 CURSOR c_get_params IS
583 SELECT paa.tax_unit_id,
584 paa.payroll_action_id,
585 paa.assignment_action_id,
586 paa.assignment_id,
587 ppa.effective_date/*,
588 pay_us_psd_xml.get_parameter('TRANSFER_REPORTING_YEAR',ppa.legislative_parameters),
589 pay_us_psd_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters),
590 pay_us_psd_xml.get_parameter('TRANSFER_PERIOD_TYPE',ppa.legislative_parameters) */ --Modified for Bug 14799833
591 FROM pay_assignment_actions paa,
592 pay_payroll_actions ppa
593 where ppa.payroll_action_id = paa.payroll_action_id
594 -- and ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
595 and paa.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
596 and ppa.report_type in ('PSD_MAG_XML')
597 and ppa.action_type = 'X'
598 and ppa.action_status = 'P';
599
600 --Added for Bug 14799833
601 CURSOR c_get_params_ytd (act_id number)IS
602 SELECT paa.tax_unit_id,
603 paa.payroll_action_id,
604 paa.assignment_action_id,
605 paa.assignment_id,
606 ppa.effective_date
607 FROM pay_assignment_actions paa,
608 pay_payroll_actions ppa
609 where ppa.payroll_action_id = paa.payroll_action_id
610 and paa.assignment_action_id = act_id--pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
611 and ppa.report_type ='YREND'
612 and ppa.action_type = 'X';
613
614 CURSOR c_get_report_params_ytd IS
615 SELECT pay_us_psd_xml.get_parameter('TRANSFER_REPORTING_YEAR',ppa.legislative_parameters),
616 pay_us_psd_xml.get_parameter('TRANSFER_STATE',ppa.legislative_parameters),
617 pay_us_psd_xml.get_parameter('TRANSFER_PERIOD_TYPE',ppa.legislative_parameters)
618 FROM pay_payroll_actions ppa
619 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
620 and ppa.report_type in ('PSD_MAG_XML')
621 and ppa.action_type = 'X'
622 and ppa.action_status = 'P';
623
624 CURSOR c_get_yepp_act_id IS
625 /* SELECT AA1.assignment_action_id
626 FROM pay_assignment_actions AA,
627 pay_payroll_actions PA,
628 pay_assignment_actions AA1,
629 pay_payroll_actions PA1
630 WHERE AA.assignment_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
631 AND AA.payroll_action_id = PA.payroll_action_id
632 AND AA.tax_unit_id = AA1.tax_unit_id
633 AND AA1.assignment_id = AA.assignment_id
634 AND AA1.payroll_action_id = PA1.payroll_action_id
635 AND PA1.report_type = 'YREND'
636 and pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PA.payroll_action_id,
637 'TRANSFER_PERIOD_TYPE')= 'Yearly'
638 and to_char(pa1.effective_date, 'YYYY') = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(PA.payroll_action_id,
639 'TRANSFER_REPORTING_YEAR') */
640 SELECT pai.locked_action_id
641 FROM pay_action_interlocks pai
642 where pai.locking_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
643
644 /*cursor c_get_jurisdiction (p_assignment_id number
645 ,p_tax_unit_id number) is
646 select puar.jurisdiction_code
647 from pay_us_asg_reporting puar
648 where puar.assignment_id = p_assignment_id
649 and puar.tax_unit_id = p_tax_unit_id
650 and length(puar.jurisdiction_code)=16;*/
651
652 l_year_start DATE;
653 l_year_end DATE;
654 l_business_group_id NUMBER;
655 l_state_abbrev VARCHAR2(200);
656 l_state_code VARCHAR2(200);
657 l_report_type VARCHAR2(200);
658 l_period_type VARCHAR2(200);
659 l_period_start DATE;
660 l_period_end DATE;
661 l_tax_coll_dist_code VARCHAR2(2);
662 BEGIN
663 --{
664 -- Fetch All parameters value set by Transmitter Cursor and Conc. Program
665 HR_UTILITY_TRACE('Constructing XML for Employee ->');
666 HR_UTILITY_TRACE('EE ASGN ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')));
667 HR_UTILITY_TRACE('TRANSFER_PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')));
668 HR_UTILITY_TRACE('PAYROLL_ACTION_ID :'||to_char(pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')));
669
670 --Added for Bug 14799833
671
672 OPEN c_get_report_params_ytd;
673 FETCH c_get_report_params_ytd INTO
674 p_reporting_year,
675 p_state_abbreviation,
676 p_period_type;
677 IF(c_get_report_params_ytd%NOTFOUND) then
678 HR_UTILITY_TRACE('No data in from the cursor c_get_report_params_ytd');
679 END IF;
680
681 CLOSE c_get_report_params_ytd;
682
683
684 IF p_period_type = 'Yearly' then
685
686 open c_get_yepp_act_id;
687 fetch c_get_yepp_act_id into yepp_act_id;
688 if c_get_yepp_act_id%notfound then
689 HR_UTILITY_TRACE('Yepp action id not found');
690 end if;
691 p_assignment_action_id := yepp_act_id;
692 close c_get_yepp_act_id;
693
694 END IF;
695
696 IF p_period_type <> 'Yearly' then
697
698 OPEN c_get_params;
699 FETCH c_get_params INTO
700 p_tax_unit_id,
701 p_payroll_action_id,
702 p_assignment_action_id,
703 p_assignment_id,
704 p_date_earned;
705 IF(c_get_params%NOTFOUND) then
706 HR_UTILITY_TRACE('No data in from the cursor c_get_params');
707 END IF;
708
709 CLOSE c_get_params;
710
711 ELSE
712
713 OPEN c_get_params_ytd(p_assignment_action_id);
714 FETCH c_get_params_ytd INTO
715 p_tax_unit_id,
716 p_payroll_action_id,
717 p_assignment_action_id,
718 p_assignment_id,
719 p_date_earned;
720 IF(c_get_params_ytd%NOTFOUND) then
721 HR_UTILITY_TRACE('No data in from the cursor c_get_params_ytd');
722 END IF;
723
724 CLOSE c_get_params_ytd;
725 END IF;
726
727
728 /*
729 p_tax_unit_id := pay_magtape_generic.get_parameter_value('YE_TAX_UNIT_ID');
730 p_payroll_action_id := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
731 p_assignment_action_id :=
732 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
733 p_ye_assignment_action_id :=
734 pay_magtape_generic.get_parameter_value('YE_ASSIGNMENT_ACTION_ID');
735 p_assignment_id := pay_magtape_generic.get_parameter_value('EE_ASSIGNMENT_ID');
736 p_date_earned := pay_magtape_generic.get_parameter_value('EE_DATE_EARNED');
737 p_reporting_year := pay_magtape_generic.get_parameter_value('TRANSFER_REPORTING_YEAR');
738 p_period_type := pay_magtape_generic.get_parameter_value('TRANSFER_PERIOD_TYPE');
739
740 p_jurisdiction_code := pay_magtape_generic.get_parameter_value('EE_LOCALITY_JD_CODE');
741 */
742
743 select pay_magtape_generic.get_parameter_value('TRANSFER_EE_JD_CODE')
744 into p_jurisdiction_code
745 from dual;
746
747 HR_UTILITY_TRACE('Prameter Used for Employee level XML');
748 HR_UTILITY_TRACE('p_tax_unit_id :'||to_char(p_tax_unit_id));
749 HR_UTILITY_TRACE('p_payroll_action_id :'||to_char(p_payroll_action_id));
750 HR_UTILITY_TRACE('p_assignment_action_id :'||to_char(p_assignment_action_id));
751 HR_UTILITY_TRACE('p_assignment_id :'||to_char(p_assignment_id));
752 HR_UTILITY_TRACE('p_date_earned :'||to_char(p_date_earned,'DD-MON-YYYY'));
753 HR_UTILITY_TRACE('p_reporting_year :'||p_reporting_year);
754 HR_UTILITY_TRACE('p_state_abbreviation :'||p_state_abbreviation);
755 HR_UTILITY_TRACE('p_period_type :'||p_period_type);
756 HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
757
758 get_report_parameters( to_char(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')),--Modified for Bug#14799833
759 l_year_start,
760 l_year_end,
761 l_state_abbrev,
762 l_state_code,
763 l_report_type,
764 l_business_group_id,
765 l_period_type,
766 l_period_start,
767 l_period_end,
768 l_tax_coll_dist_code
769 );
770 /*
771 open c_get_jurisdiction( p_assignment_id, p_tax_unit_id );
772 fetch c_get_jurisdiction into p_jurisdiction_code;
773 close c_get_jurisdiction;*/
774
775 HR_UTILITY_TRACE('p_jurisdiction_code :'||p_jurisdiction_code);
776 HR_UTILITY_TRACE('Year Start :'||to_char(l_year_start,'dd-mon-yyyy'));
777 HR_UTILITY_TRACE('Year End :'||to_char(l_year_end,'dd-mon-yyyy'));
778 HR_UTILITY_TRACE('State Abbreviation :'||l_state_abbrev);
779 HR_UTILITY_TRACE('State Code :'||l_state_code);
780 HR_UTILITY_TRACE('Report Type :'||l_report_type);
781 HR_UTILITY_TRACE('Period Type :'||l_period_type);
782 HR_UTILITY_TRACE('Period Start :'||l_period_start);
783 HR_UTILITY_TRACE('Period End :'||l_period_end);
784 HR_UTILITY_TRACE('Tax Collection District Code :'||l_tax_coll_dist_code);
785
786 --
787 -- Following Procedure Call will form the RE Record Structure in XML format
788 --
789
790
791 pay_us_psd_generic_extract.populate_arch_employee(
792 p_payroll_action_id
793 ,p_assignment_action_id
794 ,p_tax_unit_id
795 ,p_assignment_id
796 ,p_date_earned
797 ,p_reporting_year
798 ,p_jurisdiction_code
799 ,l_state_code
800 ,l_state_abbrev
801 ,status
802 ,p_final_xml_string
803 );
804
805 --HR_UTILITY_TRACE('end_of_file p_final_xml_string = '
806 -- || p_final_xml_string);
807 WRITE_TO_MAGTAPE_LOB(p_final_xml_string);
808 --}
809 EXCEPTION
810 WHEN OTHERS THEN
811 HR_UTILITY_TRACE('Error Encountered in psd_xml_employee_build');
812 HR_UTILITY_TRACE(sqlerrm);
813 END psd_xml_employee_build;
814
815 --Name
816 -- preprocess_check
817 --Purpose
818 -- This function checks if
819
820
821 FUNCTION preprocess_check
822 (
823 p_pactid NUMBER,
824 p_business_group_id NUMBER,
825 p_tax_coll_dist_code VARCHAR2
826 )
827 RETURN BOOLEAN IS
828 l_asg_id per_assignments_f.assignment_id%type;
829 l_tax_unit_id hr_organization_information.organization_id%type;
830 l_psd_rules_exist varchar2(2);
831 l_psd_code varchar2(50);
832 l_psd_code_exsists varchar2(2);
833 l_gre_name hr_all_organization_units.name%type;
834 l_effective_date date;
835 l_wah_flag varchar2(2);
836 l_prev_psd_code varchar2(50):=null;
837 message_text VARCHAR2(32000):= null;
838 message_preprocess VARCHAR2(2000) := null;
839 l_period_type varchar2(100);
840 l_transmitter number;
841 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
842 l_no_of_gres_picked NUMBER(15) := 0;
843 l_package_error_status char(1) := 'N';
844
845 -- Cursor to get all the GREs belonging to the given business group
846
847 CURSOR c_get_gre IS
848 SELECT DISTINCT puar.tax_unit_id, hou.name
849 FROM pay_us_asg_reporting puar,
850 hr_all_organization_units hou
851 WHERE length(puar.jurisdiction_code)=16 and
852 puar.tax_unit_id = hou.organization_id and
853 hou.business_group_id = p_business_group_id and
854 substr(puar.jurisdiction_code, 11,6) not in ('880000','990000') and
855 substr(puar.jurisdiction_code, 11,2)=nvl(p_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2))
856 --Added for the Bug 14799833
857 AND NOT EXISTS (
858 SELECT 'Y'
859 FROM hr_organization_information
860 WHERE organization_id = hou.organization_id
861 AND org_information_context = '1099R Magnetic Report Rules')/*
862 AND ( ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
863 'TRANSFER_PERIOD_TYPE') <> 'Yearly'
864 ) OR
865 ( pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
866 'TRANSFER_PERIOD_TYPE') = 'Yearly'
867 AND EXISTS ( select null
868 from pay_payroll_actions ppa
869 where to_number(substr(LEGISLATIVE_PARAMETERS,1,instr(LEGISLATIVE_PARAMETERS,' ',1)-1)) = puar.tax_unit_id
870 and ppa.report_type = 'YREND'
871 and ppa.effective_date = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
872 'TRANSFER_DATE')
873 )
874 )
875 )*/;
876
877
878 -- Cursor to check if psd rules are defined for that gre
879
880 CURSOR c_check_psd_rules_for_gre(l_tax_unit_id number) IS
881 SELECT 'Y'
882 FROM hr_organization_information hoi
883 WHERE hoi.organization_id = l_tax_unit_id AND
884 hoi.org_information_context = 'PSD Reporting Rules'/* AND
885 NOT EXISTS (
886 SELECT 'Y'
887 FROM hr_organization_information
888 WHERE organization_id = hoi.organization_id
889 AND org_information_context = '1099R Magnetic Report Rules')*/;
890
891 -- Cursor to get all the psd codes under a gre for which PSD balances exist
892
893 CURSOR c_get_psd_codes_for_gre(l_tax_unit_id number) IS
894 SELECT puar.assignment_id, substr(puar.jurisdiction_code,11,6)
895 FROM pay_us_asg_reporting puar
896 WHERE puar.tax_unit_id =l_tax_unit_id and
897 length(puar.jurisdiction_code)=16 and
898 substr(puar.jurisdiction_code, 11,6) not in ('880000','990000') and
899 substr(puar.jurisdiction_code, 11,2)=nvl(p_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2))
900 order by substr(puar.jurisdiction_code,11,6);
901
902
903 -- Cursor to check if employee selected is work at home employee
904
905 CURSOR c_get_wah_flag(l_asg_id number, l_effective_date date) IS
906 SELECT paf.work_at_home
907 FROM per_all_assignments_f paf
908 WHERE assignment_id = l_asg_id
909 AND l_effective_date between paf.effective_start_date and paf.effective_end_date;
910
911 -- Cursor to check if that particular psd code is defined in the psd rules for that gre
912
913 CURSOR c_check_psd_code_for_gre(l_psd_code varchar2, l_tax_unit_id number) IS
914 SELECT 'Y'
915 FROM hr_organization_information hoi
916 WHERE hoi.organization_id = l_tax_unit_id AND
917 hoi.org_information_context = 'PSD Reporting Rules' AND
918 substr(hoi.org_information10,1,6)=l_psd_code;
919
920 -- Cursor to get payroll_action_ids of the pre-process for the given GRE.
921 -- This will also serve as a check to make sure that all GREs have been
922 -- archived
923 CURSOR c_gre_payroll_action (cp_gre NUMBER)
924 IS
925 SELECT payroll_action_id
926 FROM pay_payroll_actions
927 WHERE report_type = 'YREND'
928 AND effective_date = pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid,
929 'TRANSFER_DATE')
930 AND business_group_id+0 = p_business_group_id
931 AND SUBSTR(legislative_parameters,
932 INSTR(legislative_parameters, 'TRANSFER_GRE=') +
933 LENGTH('TRANSFER_GRE=')) = TO_CHAR(cp_gre)
934 AND action_status = 'C';
935
936
937 begin
938
939
940 select ppa.effective_date
941 into l_effective_date
942 from pay_payroll_actions ppa
943 where ppa.payroll_action_id=p_pactid;
944
945 select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid
946 ,'TRANSFER_PERIOD_TYPE') into l_period_type
947 from dual;
948
949 select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(p_pactid
950 ,'TRANSFER_TRANS_LEGAL_CO_ID') into l_transmitter
951 from dual;
952
953 IF l_period_type <> 'Yearly' then
954 --Get a list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
955 open c_get_gre;
956 loop
957 fetch c_get_gre into l_tax_unit_id, l_gre_name;
958 exit when c_get_gre%NOTFOUND;
959
960 hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
961 hr_utility.set_location( 'preprocess_check', 10);
962
963 -- Check whether 'PSD Reporting Rules' are defined for the GRE.
964 open c_check_psd_rules_for_gre(l_tax_unit_id);
965 FETCH c_check_psd_rules_for_gre INTO l_psd_rules_exist;
966 hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
967
968 if c_check_psd_rules_for_gre%FOUND then
969 /*--Archiving the Employer data.
970 hr_utility.set_location( 'preprocess_check', 20);
971 archive_gre_data(p_payroll_action_id => p_pactid,
972 p_tax_unit_id => l_tax_unit_id);
973 */
974 hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
975
976 --Commenting the below code as it is sufficient to display GRE name
977
978 /*open c_get_psd_codes_for_gre(l_tax_unit_id);
979 loop
980 fetch c_get_psd_codes_for_gre into l_asg_id, l_psd_code;
981 exit when c_get_psd_codes_for_gre%NOTFOUND;
982 hr_utility.trace('l_asg_id: '||l_asg_id);
983
984 --Check the WAH flag for the employee assignment.
985
986 open c_get_wah_flag(l_asg_id , l_effective_date);
987 fetch c_get_wah_flag into l_wah_flag;
988 close c_get_wah_flag;
989
990 hr_utility.trace('l_wah_flag '||l_wah_flag);
991
992 if(l_wah_flag='Y') then
993 --If the employee is Working at home then we dont need to check
994 --his work PSD code under GRE level PSD Reporting Rules.
995 null;
996 else
997 --Checking if assignment Work PSD code is defined under Employer
998 --PSD codes in PSD Reporting Rules at GRE level.
999 open c_check_psd_code_for_gre(l_psd_code, l_tax_unit_id);
1000 fetch c_check_psd_code_for_gre into l_psd_code_exsists;
1001
1002 if (l_psd_code_exsists='Y') then
1003 null;
1004 else
1005 if (l_psd_code = l_prev_psd_code) then
1006 --Do not print the message for same PSD code under same GRE again.
1007 null;
1008 else
1009 hr_utility.trace('Pre process rules: WARNING');
1010 message_preprocess := 'PSD Periodic Wage Listing Report - One or more Employer PSD codes are missing in PSD Reporting Rules for a GRE. ';
1011 message_text := 'Please define the PSD Reporting rules for the PSD Code '||l_psd_code || ' for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1012 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1013 pay_core_utils.push_token('record_name',message_preprocess);
1014 pay_core_utils.push_token('description',message_text);
1015 end if; --l_psd_code = l_prev_psd_code
1016 l_prev_psd_code := l_psd_code;
1017 end if; --PSD code exists or not
1018 l_psd_code_exsists :='N';
1019 close c_check_psd_code_for_gre;
1020 end if; --Work at home flag
1021 l_wah_flag:='N';
1022 end loop; --Ending the loop for list of all PSD codes under the GRE.
1023 close c_get_psd_codes_for_gre;
1024 l_prev_psd_code:=null;*/
1025 else
1026 --
1027 hr_utility.trace('Pre process rules returned false');
1028 message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules are missing for at least one GRE. ';
1029 message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1030 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
1031 pay_core_utils.push_token('record_name',message_preprocess);
1032 pay_core_utils.push_token('description',message_text);
1033
1034 end if; --PSD Reporting Rules exist or not
1035 close c_check_psd_rules_for_gre;
1036 end loop; -- Ending the loop for list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
1037 close c_get_gre;
1038
1039 ELSIF l_period_type = 'Yearly' then
1040
1041 hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1042 hr_utility.set_location( 'preprocess_check', 10);
1043
1044 /* Check if Archiver has been run for Transmitter */
1045 OPEN c_gre_payroll_action (l_transmitter);
1046 FETCH c_gre_payroll_action INTO l_payroll_action_id;
1047
1048 IF c_gre_payroll_action%NOTFOUND THEN
1049 hr_utility.trace('Transmitter not Archvied ');
1050 CLOSE c_gre_payroll_action;
1051 /* message to user -- Transmitter has not been archived */
1052 message_preprocess := 'PSD Periodic Wage Listing Report - ';
1053 message_text := 'Transmitter not Archived';
1054 hr_utility.trace('Transmitter has not been archived');
1055 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1056 pay_core_utils.push_token('record_name',message_preprocess);
1057 pay_core_utils.push_token('description',message_text);
1058 -- hr_utility.raise_error;
1059 END IF;
1060 CLOSE c_gre_payroll_action;
1061
1062 /* end of Transmitter Checking */
1063
1064 hr_utility.set_location('pay_us_mmref_reporting.preprocess_check', 20);
1065 open c_get_gre;
1066 loop
1067 fetch c_get_gre into l_tax_unit_id, l_gre_name;
1068 exit when c_get_gre%NOTFOUND;
1069
1070 /* Get the payroll_action_id of the archvier for given GRE */
1071
1072 OPEN c_gre_payroll_action (l_tax_unit_id);
1073 FETCH c_gre_payroll_action INTO l_payroll_action_id;
1074
1075 /* Check for the Gre That have been Archived */
1076
1077 IF c_gre_payroll_action%FOUND THEN
1078
1079 hr_utility.trace('GRE:' || TO_CHAR(l_tax_unit_id));
1080 hr_utility.trace('Payroll_action_id - '|| to_char(l_payroll_action_id));
1081 hr_utility.trace('No. of GREs picked so far - '|| to_char(l_no_of_gres_picked));
1082
1083 l_no_of_gres_picked := l_no_of_gres_picked + 1;
1084
1085 -- Check whether 'PSD Reporting Rules' are defined for the GRE.
1086 open c_check_psd_rules_for_gre(l_tax_unit_id);
1087 FETCH c_check_psd_rules_for_gre INTO l_psd_rules_exist;
1088 hr_utility.trace('c_check_psd_rules_for_gre%rowcount '||c_check_psd_rules_for_gre%rowcount);
1089
1090 if c_check_psd_rules_for_gre%FOUND then
1091
1092 hr_utility.trace('PSD Reporting Rules exist for GRE: '||l_gre_name);
1093 else
1094 --
1095 --hr_utility.trace('Pre process rules returned false');
1096 message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules are missing for at least one GRE. ';
1097 message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_tax_unit_id;
1098 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
1099 pay_core_utils.push_token('record_name',message_preprocess);
1100 pay_core_utils.push_token('description',message_text);
1101 end if; --PSD Reporting Rules exist or not
1102 close c_check_psd_rules_for_gre;
1103
1104 ELSE
1105 /* A warning is logged if GRE is not archived */
1106 l_package_error_status := 'N';
1107 message_preprocess := 'PSD Periodic Wage Listing Report - ';
1108 message_text := 'Please Archive GRE With ID := ' || to_char(l_tax_unit_id) ;
1109 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA_WARNING','P');
1110 pay_core_utils.push_token('record_name',message_preprocess);
1111 pay_core_utils.push_token('description',message_text);
1112
1113 END IF;
1114
1115 CLOSE c_gre_payroll_action;
1116 end loop; -- Ending the loop for list of all GREs under pay_us_asg_reporting which have assignments with PSD Tax deductions.
1117 close c_get_gre;
1118
1119
1120 IF l_package_error_status = 'Y' THEN
1121
1122 hr_utility.trace('Error Condition Found');
1123 message_text := 'Error Condition detected ' ;
1124 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1125 pay_core_utils.push_token('record_name',message_preprocess);
1126 pay_core_utils.push_token('description',message_text);
1127
1128 message_text := 'Pay Message line and log have more Details' ;
1129 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1130 pay_core_utils.push_token('record_name',message_preprocess);
1131 pay_core_utils.push_token('description',message_text);
1132 hr_utility.raise_error;
1133 END IF;
1134
1135 IF l_no_of_gres_picked = 0 THEN
1136 --It means that no archived GRE was
1137 --found for the Organization. This is an error.
1138
1139 message_text := 'No GRE was picked for Magnetic Tape';
1140 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1141 pay_core_utils.push_token('record_name',message_preprocess);
1142 pay_core_utils.push_token('description',message_text);
1143 hr_utility.raise_error;
1144 END IF;
1145
1146 END IF;
1147 return (TRUE);
1148
1149 end preprocess_check;
1150
1151
1152
1153
1154 /****************************************************************************
1155 Name : RANGE_CURSOR
1156 Description : This procedure prepares range of persons to be processed for process
1157 Local YearEnd Interface Extract. This procedure defines a SQL statement
1158 to fetch all the people to be included in the generic XML extract. This SQL
1159 statement is used to define the 'chunks' for multi-threaded operation
1160 Arguments
1161 p_pactid payroll action id for the report
1162 p_sqlstr the SQL statement to fetch the people
1163 *****************************************************************************/
1164
1165 PROCEDURE range_cursor ( p_pactid IN NUMBER,
1166 p_sqlstr OUT nocopy VARCHAR2
1167 )
1168 IS
1169 l_year_start DATE;
1170 l_year_end DATE;
1171 l_business_group_id NUMBER;
1172 l_state_abbrev pay_us_states.state_abbrev%type;
1173 l_state_code pay_us_states.state_code%type;
1174 l_report_type VARCHAR2(200);
1175 l_period_type VARCHAR2(200);
1176 l_period_start DATE;
1177 l_period_end DATE;
1178 l_tax_coll_dist_code VARCHAR2(2);
1179 l_tax_coll_dist_code_or_all VARCHAR2(3);
1180 l_trans_legal_co_id hr_organization_information.organization_id%type;
1181 l_tax_unit_id hr_organization_information.organization_id%type;
1182 l_gre_name hr_all_organization_units.name%type;
1183 l_archive boolean:= FALSE;
1184 l_w2_reporting_rules_exist number;
1185 l_psd_reporting_rules_exist number;
1186 l_report_file_name pay_payroll_actions.magnetic_file_name%type;
1187 l_transfer_month_quarter varchar2(30);
1188 /* Local variables used for checking W2 and PSD Reporting Rules */
1189 message_text VARCHAR2(32000):= null;
1190 message_preprocess VARCHAR2(2000) := null;
1191
1192 cursor c_w2_reporting_rules(cp_tax_unit_id in number) is
1193 select '1' from hr_organization_information
1194 where organization_id = cp_tax_unit_id
1195 and org_information_context = 'W2 Reporting Rules';
1196
1197 cursor c_psd_reporting_rules(cp_tax_unit_id in number) is
1198 select '1' from hr_organization_information
1199 where organization_id = cp_tax_unit_id
1200 and org_information_context = 'PSD Reporting Rules';
1201
1202
1203 CURSOR c_get_gre IS
1204 SELECT DISTINCT puar.tax_unit_id
1205 FROM pay_us_asg_reporting puar,
1206 hr_all_organization_units hou,
1207 hr_organization_information hoi
1208 WHERE length(puar.jurisdiction_code)=16 and
1209 substr(puar.jurisdiction_code, 11,2)=nvl(l_tax_coll_dist_code, substr(puar.jurisdiction_code, 11,2)) and
1210 puar.tax_unit_id = hou.organization_id and
1211 hou.business_group_id = l_business_group_id and
1212 hoi.organization_id= puar.tax_unit_id and
1213 hoi.org_information_context = 'PSD Reporting Rules' AND
1214 NOT EXISTS (
1215 SELECT 'Y'
1216 FROM hr_organization_information
1217 WHERE organization_id = hoi.organization_id
1218 AND org_information_context = '1099R Magnetic Report Rules');
1219
1220
1221 BEGIN
1222 -- hr_utility.trace_on(null,'LOCALXML');
1223 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 10);
1224
1225
1226
1227 get_report_parameters( p_pactid,
1228 l_year_start,
1229 l_year_end,
1230 l_state_abbrev,
1231 l_state_code,
1232 l_report_type,
1233 l_business_group_id,
1234 l_period_type,
1235 l_period_start,
1236 l_period_end,
1237 l_tax_coll_dist_code
1238 );
1239
1240 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 20);
1241
1242 update pay_payroll_actions
1243 set effective_date = l_period_end
1244 WHERE payroll_action_id = p_pactid;
1245
1246 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_TRANS_LEGAL_CO_ID',legislative_parameters)
1247 INTO l_trans_legal_co_id
1248 FROM pay_payroll_actions
1249 WHERE payroll_action_id = p_pactid;
1250
1251 hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1252
1253 SELECT name
1254 INTO l_gre_name
1255 FROM hr_all_organization_units
1256 WHERE organization_id = l_trans_legal_co_id;
1257
1258 hr_utility.trace('Transmitter Name l_gre_name: '||l_gre_name);
1259
1260 SELECT pay_us_payroll_utils.get_parameter('TRANSFER_MONTH/QUARTER',legislative_parameters)
1261 INTO l_transfer_month_quarter
1262 FROM pay_payroll_actions
1263 WHERE payroll_action_id = p_pactid;
1264
1265 hr_utility.trace('l_transfer_month_quarter: '||l_transfer_month_quarter);
1266
1267 SELECT decode(l_tax_coll_dist_code,NULL,'ALL','0'||l_tax_coll_dist_code)
1268 INTO l_tax_coll_dist_code_or_all
1269 FROM dual;
1270
1271 hr_utility.trace('l_tax_coll_dist_code_or_all: '||l_tax_coll_dist_code_or_all);
1272
1273 SELECT report_file_name
1274 INTO l_report_file_name
1275 FROM pay_payroll_actions
1276 WHERE payroll_action_id = p_pactid;
1277
1278 /* The name of the report should be of the following format:
1279 Monthly: PSDPWLGRENMALLMMMDDYYYY_/PSDPWLGRENM012MMMDDYYYY_n_P.*
1280 Quarterly: PSDPWLGRENMALLQMMDDYYYY_/PSDPWLGRENM012QMMDDYYYY_n_P.*
1281 Yearly: PSDPWLGRENMALLYMMDDYYYY_/PSDPWLGRENM012YMMDDYYYY_n_P.*
1282 WHERE TCD Code = ALL or 12
1283 */
1284
1285 l_report_file_name:=l_report_file_name||upper(substr(replace(translate(l_gre_name,'`~!@#$%^&*-_=+{}()[]\|:;"'',.<>/?', ' '),' '),1,5))||l_tax_coll_dist_code_or_all||substr(l_period_type,1,1)||replace(to_char(l_period_end,'MM-DD-YYYY'),'-','');
1286 hr_utility.trace('l_report_file_name: '||l_report_file_name);
1287
1288 UPDATE pay_payroll_actions
1289 SET report_file_name = l_report_file_name
1290 WHERE payroll_action_id = p_pactid;
1291
1292 UPDATE pay_payroll_actions
1293 SET magnetic_file_name = l_report_file_name
1294 WHERE payroll_action_id = p_pactid;
1295
1296 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 30);
1297
1298 hr_utility.trace('Checking pre process rules');
1299 if(pay_us_psd_xml.preprocess_check(p_pactid,l_business_group_id, l_tax_coll_dist_code)) then
1300 hr_utility.trace('Pre process rules returned true');
1301 else
1302 hr_utility.trace('Pre process rules returned false');
1303 NULL;
1304 /* hr_utility.trace('Pre process rules returned false');
1305 message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing for at least one GRE.';
1306 message_text := 'Please define the PSD Reporting Rules for the GRE '||l_gre_name||' GRE ID: '||l_trans_legal_co_id;
1307 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1308 pay_core_utils.push_token('record_name',message_preprocess);
1309 pay_core_utils.push_token('description',message_text);*/
1310 end if; -- End of pre process rules check
1311
1312 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 40);
1313 hr_utility.trace( 'Value of the l_period_type is: '||l_period_type);
1314
1315 IF l_report_type = 'PSD_MAG_XML' THEN
1316
1317 IF l_period_type <> 'Yearly' then
1318
1319 p_sqlstr := 'SELECT distinct ASG.person_id
1320 FROM hr_organization_information HOI,
1321 per_all_assignments_f ASG,
1322 pay_us_asg_reporting puar,
1323 pay_state_rules SR
1324 WHERE SR.state_code = '''||l_state_abbrev||'''
1325 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
1326 AND substr(puar.jurisdiction_code, 11,2)=nvl('''||l_tax_coll_dist_code||''',substr(puar.jurisdiction_code, 11,2))
1327 AND ASG.assignment_type = ''E''
1328 AND ASG.effective_start_date <= ''' || l_period_end ||'''
1329 AND ASG.effective_end_date >= ''' || l_period_start || '''
1330 AND ASG.business_group_id + 0 = ''' || l_business_group_id || '''
1331 AND HOI.organization_id = puar.tax_unit_id
1332 AND HOI.ORG_INFORMATION_CONTEXT = ''PSD Reporting Rules''
1333 AND asg.assignment_id = puar.assignment_id
1334 AND not exists (select ''x''
1335 from hr_organization_information HOI2
1336 where HOI2.organization_id = puar.tax_unit_id
1337 AND HOI2.ORG_INFORMATION_CONTEXT = ''1099R Magnetic Report Rules''
1338 AND HOI2.ORG_INFORMATION2 is not null)
1339 AND ASG.payroll_id is not null
1340 AND :payroll_action_id is not null
1341 ORDER BY ASG.person_id';
1342 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 50);
1343 else
1344 --Modified for the Bug 15944697
1345 p_sqlstr := 'SELECT DISTINCT to_number(paa.serial_number)
1346 FROM pay_assignment_actions paa,
1347 pay_payroll_actions ppa,
1348 per_all_assignments_f paf,
1349 pay_payroll_actions ppa1,
1350 pay_us_asg_reporting puar,
1351 pay_state_rules SR
1352 WHERE ppa1.payroll_action_id = :payroll_action_id
1353 AND ppa.business_group_id+0 = ppa1.business_group_id
1354 AND ppa1.effective_date = ppa.effective_date
1355 AND ppa.report_type = ''YREND''
1356 AND ppa.payroll_action_id = paa.payroll_action_id
1357 AND paf.person_id = to_number(paa.serial_number)
1358 AND paf.assignment_type = ''E''
1359 AND SR.state_code = '''||l_state_abbrev||'''
1360 AND puar.jurisdiction_code like substr(SR.jurisdiction_code ,1,2)||''%''
1361 AND substr(puar.jurisdiction_code, 11,2)=nvl('''||l_tax_coll_dist_code||''',substr(puar.jurisdiction_code, 11,2))
1362 AND puar.assignment_id = paf.assignment_id
1363 AND paf.effective_start_date <= ppa.effective_date
1364 AND paf.effective_end_date >= ppa.start_date
1365 AND paa.action_status = ''C''
1366 AND NOT EXISTS ( SELECT ''x''
1367 FROM hr_organization_information hoi
1368 WHERE hoi.organization_id = paa.tax_unit_id
1369 AND hoi.org_information_context = ''1099R Magnetic Report Rules''
1370 )
1371 AND EXISTS ( SELECT ''x''
1372 FROM hr_organization_information hoi
1373 WHERE hoi.organization_id = paa.tax_unit_id
1374 AND hoi.org_information_context = ''PSD Reporting Rules''
1375 )
1376 ORDER BY to_number(paa.serial_number)';
1377 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 50);
1378 end if;
1379 END IF;
1380 hr_utility.trace( p_sqlstr);
1381
1382 hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1383
1384 open c_w2_reporting_rules(l_trans_legal_co_id);
1385
1386 fetch c_w2_reporting_rules into l_w2_reporting_rules_exist;
1387 hr_utility.trace('inside c_w2_reporting_rules ');
1388 if c_w2_reporting_rules%NOTFOUND then
1389
1390 message_preprocess := 'PSD Periodic Wage Listing Report - W2 Reporting Rules Missing';
1391 message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1392
1393
1394 hr_utility.trace('W2 Reporting rules have not been setup');
1395
1396 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1397 pay_core_utils.push_token('record_name',message_preprocess);
1398 pay_core_utils.push_token('description',message_text);
1399
1400 end if;
1401 close c_w2_reporting_rules;
1402
1403 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 60);
1404 hr_utility.trace('Finished with W2 Reporting Rules check ');
1405
1406 open c_psd_reporting_rules(l_trans_legal_co_id);
1407
1408 fetch c_psd_reporting_rules into l_psd_reporting_rules_exist;
1409 hr_utility.trace('inside c_psd_reporting_rules ');
1410 if c_psd_reporting_rules%NOTFOUND then
1411
1412 message_preprocess := 'PSD Periodic Wage Listing Report - PSD Reporting Rules Missing';
1413 message_text := 'Define these for tax unit id '||to_char(l_trans_legal_co_id);
1414
1415
1416 hr_utility.trace('PSD Reporting rules have not been setup');
1417
1418 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
1419 pay_core_utils.push_token('record_name',message_preprocess);
1420 pay_core_utils.push_token('description',message_text);
1421
1422 end if;
1423 close c_psd_reporting_rules;
1424
1425 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 70);
1426 hr_utility.trace('Finished with PSD Reporting Rules check ');
1427
1428
1429 l_archive := chk_gre_archive(p_pactid);
1430
1431 hr_utility.trace('after gre archive ');
1432
1433 hr_utility.trace('g_archive_flag: '||g_archive_flag);
1434
1435
1436 if g_archive_flag = 'N' then
1437
1438 hr_utility.trace('range_cursor archiving transmitter data');
1439 hr_utility.trace('l_trans_legal_co_id: '||l_trans_legal_co_id);
1440 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1441
1442 archive_gre_data(p_payroll_action_id => p_pactid,
1443 p_tax_unit_id => l_trans_legal_co_id);
1444
1445 hr_utility.trace('range_cursor archived transmitter data for trans_legal_co_id: '||l_trans_legal_co_id);
1446
1447 IF l_period_type <> 'Yearly' then --Added for the Bug 14799833, skip the employer archiver for Yearly
1448
1449 open c_get_gre;
1450 loop
1451 fetch c_get_gre into l_tax_unit_id;
1452 exit when c_get_gre%notfound;
1453 if (l_tax_unit_id=l_trans_legal_co_id) then
1454 --Transmitter data is already archived so we dont need to archive it again.
1455 null;
1456 else
1457 hr_utility.trace('range_cursor archiving employer data');
1458 hr_utility.trace('l_tax_unit_id: '||l_tax_unit_id);
1459 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 80);
1460
1461 archive_gre_data(p_payroll_action_id => p_pactid,
1462 p_tax_unit_id => l_tax_unit_id);
1463
1464 hr_utility.trace('range_cursor archived employer data for tax_unit_id: '||l_tax_unit_id);
1465 end if;
1466 end loop;
1467 close c_get_gre;
1468
1469 END IF;
1470
1471 hr_utility.trace('range_cursor done with archiving employer data ');
1472
1473 end if;
1474 hr_utility.set_location( 'pay_us_psd_xml.range_cursor', 90);
1475
1476
1477 END range_cursor;
1478
1479
1480 /****************************************************************************
1481 Name : ACTION_CREATION
1482 Description : This procedure creates assignment actions for the payroll action associated
1483 process <PSD Periodic Wage Listing Report>
1484
1485 The procedure processes assignments in 'chunks' to facilitate multi-threaded
1486 operation. The chunk is defined by the size and the starting and ending person id.
1487 An interlock is also created against the year-end pre-processor assignment action
1488 to prevent rolling back of the archiver.
1489
1490 *****************************************************************************/
1491
1492 PROCEDURE action_creation(p_pactid in number,
1493 p_stperson in number,
1494 p_endperson in number,
1495 p_chunk in number)
1496 IS
1497
1498 /* Variables used to hold the select columns from the SQL statement.*/
1499
1500 l_person_id per_people_f.person_id%type;
1501 l_assignment_id per_assignments_f.assignment_id%type;
1502 l_tax_unit_id hr_organization_units.organization_id%type;
1503 l_effective_end_date date;
1504
1505 /* Variables used to hold the values used as bind variables within the
1506 SQL statement. */
1507
1508 l_business_group_id number;
1509 l_period_start date;
1510 l_period_end date;
1511 l_tax_coll_dist_code varchar2(2);
1512
1513 /* Variables used to hold the details of the payroll and assignment actions
1514 that are created. */
1515
1516 l_payroll_action_created boolean := false;
1517 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
1518 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1519
1520 /* Variable holding the balance to be tested. */
1521
1522 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
1523
1524 /* Indicator variables used to control how the people are grouped. */
1525
1526 l_group_by_gre boolean := FALSE;
1527
1528 /* Local variables to select only those employees those PSD Withheld <>0*/
1529
1530 l_psd_def_bal_id pay_defined_balances.defined_balance_id%type;
1531 l_psd_user_entity_id ff_database_items.user_entity_id%type;
1532 l_psd_balance_value number(15,2):=0;
1533 l_aaid pay_assignment_actions.assignment_action_id%type;
1534 l_eff_date date;
1535 l_jurisdiction_code VARCHAR2(30);
1536
1537 /* Variables used to hold the current values returned within the loop for
1538 checking against the new values returned from within the loop on the
1539 next iteration. */
1540
1541 l_prev_person_id per_people_f.person_id%type;
1542 l_prev_asg_id per_assignments_f.assignment_id%type;
1543 l_prev_tax_unit_id hr_organization_units.organization_id%type;
1544
1545 /* general process variables */
1546
1547
1548
1549 l_year_start DATE;
1550 l_year_end DATE;
1551 l_state_abbrev pay_us_states.state_abbrev%type;
1552 l_state_code pay_us_states.state_code%type;
1553 l_report_type VARCHAR2(30);
1554 l_period_type VARCHAR2(200);
1555 l_range_person BOOLEAN;
1556 -- l_transfer_gre_id number;
1557 lockingactid number;
1558
1559
1560 CURSOR c_action is
1561 SELECT
1562 ASG.person_id person_id,
1563 ASG.assignment_id assignment_id,
1564 paa.tax_unit_id tax_unit_id,
1565 ppa.effective_date effective_end_date
1566 FROM per_all_assignments_f ASG,
1567 pay_assignment_actions paa,
1568 pay_payroll_actions ppa
1569 WHERE ppa.effective_date between l_period_start
1570 and l_period_end
1571 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1572 and paa.payroll_action_id = ppa.payroll_action_id
1573 and paa.assignment_id = ASG.assignment_id
1574 /*added to ignore skipped assignment */
1575 and paa.action_status <> 'S'
1576 and asg.effective_end_date >= l_period_start
1577 and asg.effective_start_date <= l_period_end
1578
1579 and ASG.business_group_id + 0 = l_business_group_id
1580 and ASG.person_id between p_stperson and p_endperson
1581 and ASG.assignment_type = 'E'
1582 and ASG.primary_flag = 'Y'
1583 and ASG.business_group_id = ppa.business_group_id -- 5152728
1584 and exists (select '1'
1585 from pay_us_asg_reporting puar,
1586 pay_state_rules SR
1587 where SR.state_code = l_state_abbrev
1588 and substr(SR.jurisdiction_code ,1,2) =
1589 substr(puar.jurisdiction_code,1,2)
1590 and ASG.assignment_id = puar.assignment_id
1591 )
1592 ORDER BY 1, 3, 4 DESC, 2 ;
1593
1594 CURSOR c_action_person_on is
1595 SELECT
1596 ASG.person_id person_id,
1597 ASG.assignment_id assignment_id,
1598 paa.tax_unit_id tax_unit_id,
1599 ppa.effective_date effective_end_date
1600 FROM per_all_assignments_f ASG,
1601 pay_assignment_actions paa,
1602 pay_payroll_actions ppa,
1603 pay_population_ranges ppr
1604 WHERE ppa.effective_date between l_period_start
1605 and l_period_end
1606 and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
1607 and paa.payroll_action_id = ppa.payroll_action_id
1608 and paa.assignment_id = ASG.assignment_id
1609 /*added to ignore skipped assignment */
1610 and paa.action_status <> 'S'
1611 and asg.effective_end_date >= l_period_start
1612 and asg.effective_start_date <= l_period_end
1613
1614 and ASG.business_group_id + 0 = l_business_group_id
1615 -- and ASG.person_id between stperson and endperson
1616 and ppr.payroll_action_id = p_pactid
1617 and ppr.chunk_number = p_chunk
1618 and asg.person_id = ppr.person_id
1619 and ASG.assignment_type = 'E'
1620 and ASG.primary_flag = 'Y'
1621 and ASG.business_group_id = ppa.business_group_id -- 5152728
1622 and exists (select '1'
1623 from pay_us_asg_reporting puar,
1624 pay_state_rules SR
1625 where SR.state_code = l_state_abbrev
1626 and substr(SR.jurisdiction_code ,1,2) =
1627 substr(puar.jurisdiction_code,1,2)
1628 and ASG.assignment_id = puar.assignment_id
1629 )
1630 ORDER BY 1, 3, 4 DESC, 2 ;
1631
1632 --Added for the bug 14799833
1633 CURSOR c_action_ytd IS
1634 SELECT ASG.person_id person_id,
1635 ASG.assignment_id assignment_id,
1636 paa.tax_unit_id tax_unit_id,
1637 ppa.effective_date effective_end_date,
1638 paa.assignment_action_id act_id
1639 FROM per_all_assignments_f ASG,
1640 pay_assignment_actions paa,
1641 pay_payroll_actions ppa
1642 WHERE ppa.effective_date = l_period_end
1643 and ppa.report_type = 'YREND'
1644 and paa.payroll_action_id = ppa.payroll_action_id
1645 and paa.assignment_id = ASG.assignment_id
1646 /*added to ignore skipped assignment */
1647 and paa.action_status <> 'S'
1648 and asg.effective_end_date >= l_period_start
1649 and asg.effective_start_date <= l_period_end
1650
1651 and ASG.business_group_id + 0 = l_business_group_id
1652 and ASG.person_id between p_stperson and p_endperson
1653 and ASG.assignment_type = 'E'
1654 and ASG.primary_flag = 'Y'
1655 and ASG.business_group_id = ppa.business_group_id
1656 and exists ( SELECT NULL
1657 FROM ff_archive_items fai,
1658 ff_archive_item_contexts faic,
1659 FF_CONTEXTS FC
1660 WHERE fai.context1 = paa.assignment_action_id
1661 AND fai.archive_item_id = faic.archive_item_id
1662 AND faic.CONTEXT_ID = FC.CONTEXT_ID
1663 AND FC.CONTEXT_NAME='JURISDICTION_CODE'
1664 AND length(FAIC.CONTEXT) = 16 )
1665 ORDER BY 1, 3, 4 DESC, 2 ;
1666
1667 CURSOR c_action_ytd_person_on IS
1668 SELECT ASG.person_id person_id,
1669 ASG.assignment_id assignment_id,
1670 paa.tax_unit_id tax_unit_id,
1671 ppa.effective_date effective_end_date,
1672 paa.assignment_action_id act_id
1673 FROM per_all_assignments_f ASG,
1674 pay_assignment_actions paa,
1675 pay_payroll_actions ppa,
1676 pay_population_ranges ppr
1677 WHERE ppa.effective_date= l_period_end
1678 and ppa.report_type = 'YREND'
1679 and paa.payroll_action_id = ppa.payroll_action_id
1680 and paa.assignment_id = ASG.assignment_id
1681 /*added to ignore skipped assignment */
1682 and paa.action_status <> 'S'
1683 and asg.effective_end_date >= l_period_start
1684 and asg.effective_start_date <= l_period_end
1685
1686 and ASG.business_group_id + 0 = l_business_group_id
1687 --and ASG.person_id between p_stperson and p_endperson
1688 and ppr.payroll_action_id = p_pactid
1689 and ppr.chunk_number = p_chunk
1690 and asg.person_id = ppr.person_id
1691 and ASG.assignment_type = 'E'
1692 and ASG.primary_flag = 'Y'
1693 and ASG.business_group_id = ppa.business_group_id
1694 and exists ( SELECT NULL
1695 FROM ff_archive_items fai,
1696 ff_archive_item_contexts faic,
1697 FF_CONTEXTS FC
1698 WHERE fai.context1 = paa.assignment_action_id
1699 AND fai.archive_item_id = faic.archive_item_id
1700 AND faic.CONTEXT_ID = FC.CONTEXT_ID
1701 AND FC.CONTEXT_NAME='JURISDICTION_CODE'
1702 AND length(FAIC.CONTEXT) = 16 )
1703 ORDER BY 1, 3, 4 DESC, 2 ;
1704
1705 /* --Added for Bug 14799833
1706 CURSOR c_get_ytd_values(l_assignment_action_id number,l_jd_code varchar2) IS
1707 select v.W2_LOCAL_INCOME_TAX
1708 from PAY_US_LOCALITY_W2_V v
1709 where v.assignment_action_id = l_assignment_action_id
1710 and v.JURISDICTION = l_jd_code
1711 and v.W2_LOCAL_INCOME_TAX <> 0;
1712 */
1713 CURSOR c_get_latest_asg(l_person_id number ) IS
1714 SELECT /*+ORDERED*/
1715 PAA.ASSIGNMENT_ACTION_ID,
1716 PPA.EFFECTIVE_DATE
1717 FROM PER_ALL_ASSIGNMENTS_F PAF,
1718 PAY_ASSIGNMENT_ACTIONS PAA,
1719 PAY_PAYROLL_ACTIONS PPA ,
1720 PAY_ACTION_CLASSIFICATIONS PAC
1721 WHERE PAF.PERSON_ID = l_person_id
1722 AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
1723 AND PAA.TAX_UNIT_ID = l_tax_unit_id
1724 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
1725 AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
1726 AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
1727 AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
1728 AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
1729 AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
1730 AND PAA.SOURCE_ACTION_ID IS NULL)
1731 OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
1732 AND PAA.SOURCE_ACTION_ID IS NOT NULL )
1733 OR (PPA.ACTION_TYPE = 'V'
1734 AND PPA.RUN_TYPE_ID IS NULL
1735 AND PAA.RUN_TYPE_ID IS NOT NULL
1736 AND PAA.SOURCE_ACTION_ID IS NULL))
1737 ORDER BY PAA.ACTION_SEQUENCE DESC;
1738
1739 -- Modified for Bug 14640336
1740 cursor c_get_jurisdiction (cp_person_id number
1741 ,cp_tax_unit_id number) is
1742 select distinct puar.jurisdiction_code
1743 from pay_us_asg_reporting puar,
1744 per_all_Assignments_f paf
1745 where puar.assignment_id = paf.assignment_id
1746 and paf.person_id = cp_person_id
1747 and puar.tax_unit_id = cp_tax_unit_id
1748 and length(puar.jurisdiction_code)=16
1749 and substr(puar.jurisdiction_code,11,2)=nvl(l_tax_coll_dist_code,substr(puar.jurisdiction_code,11,2) );
1750
1751
1752 BEGIN
1753
1754 /* Get the report type, report qualifier, business group id and the
1755 gre for which the report has to be run */
1756
1757
1758 hr_utility.trace('In action_creation');
1759 hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1760 hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1761 hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1762 hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1763
1764
1765 hr_utility.trace('getting payroll action data');
1766
1767 get_report_parameters( p_pactid,
1768 l_year_start,
1769 l_year_end,
1770 l_state_abbrev,
1771 l_state_code,
1772 l_report_type,
1773 l_business_group_id,
1774 l_period_type,
1775 l_period_start,
1776 l_period_end,
1777 l_tax_coll_dist_code
1778 );
1779 l_range_person := FALSE;
1780 l_range_person := pay_ac_utility.range_person_on(
1781 p_report_type => 'PSD_MAG_XML'
1782 ,p_report_format => 'PSD_LOCAL_XML'
1783 ,p_report_qualifier => 'LOCAL'
1784 ,p_report_category => 'XML');
1785
1786 if l_range_person then
1787 hr_utility.trace('l_range_person : TRUE');
1788
1789 if l_period_type <> 'Yearly' then --Added for Bug#14799833
1790 open c_action_person_on;
1791 else
1792 open c_action_ytd_person_on;
1793 end if;
1794 else
1795 hr_utility.trace('l_range_person : FALSE');
1796
1797 if l_period_type <> 'Yearly' then --Added for Bug#14799833
1798 open c_action;
1799 else
1800 open c_action_ytd;
1801 end if;
1802
1803 end if;
1804
1805 hr_utility.trace('In action_creation');
1806 hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
1807 hr_utility.trace('In action_creation, p_stperson= '||p_stperson);
1808 hr_utility.trace('In action_creation, p_endperson= '||p_endperson);
1809 hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
1810 hr_utility.trace('In action_creation, l_business_group_id= '||l_business_group_id);
1811 hr_utility.trace('In action_creation, l_period_start= '||l_period_start);
1812 hr_utility.trace('In action_creation, l_period_end= '||l_period_end);
1813 hr_utility.trace('In action_creation, l_state_abbrev= '||l_state_abbrev);
1814 hr_utility.trace('In action_creation, l_tax_coll_dist_code= '||l_tax_coll_dist_code);
1815 hr_utility.trace('In action_creation, l_period_type= '||l_period_type);
1816
1817 loop
1818 if l_range_person then
1819
1820 if l_period_type <> 'Yearly' then --Added for Bug#14799833
1821 hr_utility.trace('cursor1');
1822 fetch c_action_person_on INTO l_person_id,
1823 l_assignment_id,
1824 l_tax_unit_id,
1825 l_effective_end_date;
1826
1827 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1828
1829 exit when c_action_person_on%NOTFOUND;
1830
1831 else
1832 hr_utility.trace('cursor2');
1833 fetch c_action_ytd_person_on INTO l_person_id,
1834 l_assignment_id,
1835 l_tax_unit_id,
1836 l_effective_end_date,
1837 l_assignment_action_id;
1838
1839 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1840 hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1841
1842 exit when c_action_ytd_person_on%NOTFOUND;
1843 end if;
1844
1845 else
1846
1847 if l_period_type <> 'Yearly' then --Added for Bug#14799833
1848 hr_utility.trace('cursor3');
1849 fetch c_action INTO l_person_id,
1850 l_assignment_id,
1851 l_tax_unit_id,
1852 l_effective_end_date;
1853
1854 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1855
1856 exit when c_action%NOTFOUND;
1857
1858 else
1859 hr_utility.trace('cursor4');
1860 fetch c_action_ytd INTO l_person_id,
1861 l_assignment_id,
1862 l_tax_unit_id,
1863 l_effective_end_date,
1864 l_assignment_action_id;
1865
1866 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1867 hr_utility.trace('l_assignment_action_id = '|| to_char(l_assignment_action_id));
1868
1869 exit when c_action_ytd%NOTFOUND;
1870
1871 end if;
1872
1873 end if;
1874
1875 open c_get_latest_asg(l_person_id );
1876 fetch c_get_latest_asg into l_aaid, l_eff_date;
1877 hr_utility.trace('l_aaid in action creation '||to_char(l_aaid));
1878 close c_get_latest_asg;
1879
1880 open c_get_jurisdiction( l_person_id, l_tax_unit_id ); -- Modified for Bug 14640336
1881 loop -- To consider all the jurisdiction codes for given asg_id in the table pay_us_asg_reporting
1882 fetch c_get_jurisdiction into l_jurisdiction_code;
1883 exit when c_get_jurisdiction%notfound;
1884
1885
1886 pay_balance_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
1887
1888 pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction_code);
1889
1890 hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction_code));
1891
1892 --Archiving this balance to get the value of PSD Withheld
1893
1894 hr_utility.trace('l_period_type in action creation: '||l_period_type);
1895
1896 --check if PSD Withheld balances are <>0
1897
1898 --Archiving the balance value according to the period type
1899 if (l_period_type='Monthly') then
1900 hr_utility.trace('l_period_type in action creation: '||l_period_type);
1901 l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
1902 'PSD Withheld',
1903 '_PER_JD_GRE_MONTH');
1904 l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
1905 l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1906 ,p_balcall_aaid => l_aaid);
1907 hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1908 elsif (l_period_type='Quarterly') then
1909 hr_utility.trace('l_period_type in action creation: '||l_period_type);
1910 l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
1911 'PSD Withheld',
1912 '_PER_JD_GRE_QTD');
1913 l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
1914 l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
1915 ,p_balcall_aaid => l_aaid);
1916 hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1917 elsif (l_period_type='Yearly') then --Added for the Bug 14799833
1918 hr_utility.trace('l_period_type in action creation: '||l_period_type);
1919 l_psd_user_entity_id:= get_user_entity_id('A_PSD_WITHHELD_PER_JD_GRE_YTD');
1920 l_psd_balance_value := hr_us_w2_rep.get_w2_arch_bal (l_assignment_action_id
1921 ,'A_PSD_WITHHELD_PER_JD_GRE_YTD'
1922 ,l_tax_unit_id
1923 ,l_jurisdiction_code
1924 ,16);
1925 /* OPEN c_get_ytd_values(l_assignment_action_id,l_jurisdiction_code);
1926 Fetch c_get_ytd_values into l_psd_balance_value;
1927 close c_get_ytd_values;
1928 Commented for improving the performance*/
1929
1930 hr_utility.trace('l_assignment_action_id in action creation: '||l_assignment_action_id);
1931 hr_utility.trace('l_psd_bal_value in action creation: '||l_psd_balance_value);
1932 end if;
1933
1934 if (l_psd_balance_value=0) then
1935 NULL;--continue;
1936 else
1937 exit;
1938 end if;
1939 end loop; -- list of jurisdiction codes fetched by c_get_jurisdiction
1940 close c_get_jurisdiction;
1941
1942 --Creating assignment actions only when PSD Withheld <>0
1943
1944 if (l_psd_balance_value=0 or l_psd_balance_value is null) then
1945 hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1946 else
1947 hr_utility.trace('In action_creation, l_psd_balance_value= '||l_psd_balance_value||' for assignment id= '||l_assignment_id );
1948 -- If the new row is the same as the previous row according to the way
1949 -- the rows are grouped then discard the row ie. grouping by GRE
1950 -- requires a single row for each person / GRE combination.
1951 --
1952 hr_utility.set_location ('action_creation',4);
1953 l_group_by_gre :=TRUE;
1954 if (l_group_by_gre and
1955 l_person_id = l_prev_person_id and
1956 l_tax_unit_id = l_prev_tax_unit_id
1957 ) then
1958 --
1959 -- Do nothing.
1960 --
1961 hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1962 hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1963 hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1964 hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1965 hr_utility.set_location ('action_creation',5);
1966 null;
1967 --
1968 -- If the balance is non zero then an assignment action is created to
1969 -- indicate their inclusion in the magnetic tape report.
1970 --
1971 else
1972 hr_utility.trace('In action_creation, l_person_id= '||l_person_id);
1973 hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
1974 hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
1975 hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
1976 hr_utility.set_location ('action_creation',6);
1977 --
1978 -- If the payroll action has not been created yet i.e. this is the
1979 -- first assignment action then create it.
1980 --
1981 --
1982 -- Create the assignment action to represnt the person / tax unit
1983 -- combination.
1984 --
1985 select pay_assignment_actions_s.nextval
1986 into lockingactid
1987 from dual;
1988 hr_utility.set_location ('action_creation',8);
1989
1990 /*SELECT pay_us_payroll_utils.get_parameter('TRANSFER_GRE',legislative_parameters)
1991 INTO l_transfer_gre_id
1992 FROM pay_payroll_actions
1993 WHERE payroll_action_id = p_pactid;
1994 hr_utility.trace('In action_creation, l_transfer_gre_id= '||l_transfer_gre_id);*/
1995
1996 hr_utility.set_location ('action_creation',9);
1997 -- insert into pay_assignment_actions.
1998
1999 hr_utility.trace('In action_creation, lockingactid= '||lockingactid);
2000 hr_utility.trace('In action_creation, l_assignment_id= '||l_assignment_id);
2001 hr_utility.trace('In action_creation, p_pactid= '||p_pactid);
2002 hr_utility.trace('In action_creation, p_chunk= '||p_chunk);
2003 hr_utility.trace('In action_creation, l_tax_unit_id= '||l_tax_unit_id);
2004
2005 hr_nonrun_asact.insact(lockingactid,l_assignment_id,
2006 p_pactid,p_chunk,l_tax_unit_id);
2007
2008 hr_utility.set_location ('action_creation',10);
2009
2010 -- Addded for Bug 14640336
2011 update pay_assignment_actions
2012 set serial_number=l_person_id
2013 where assignment_action_id=lockingactid;
2014
2015 hr_utility.set_location ('action_creation',11);
2016
2017 --Creating the Interlock if report type is Yearly Bug#14799833
2018
2019 IF (l_period_type='Yearly') then
2020
2021 hr_nonrun_asact.insint(lockingactid, l_assignment_action_id);
2022 hr_utility.set_location(
2023 'pay_us_mmref_reporting.create_assignement_act', 90);
2024 hr_utility.trace('Interlock Created - ');
2025 hr_utility.trace('Locking Action : '|| to_char(lockingactid));
2026 hr_utility.trace('Locked Action : '|| to_char(l_assignment_action_id));
2027
2028 END IF;
2029
2030 --
2031 -- Record the current values for the next time around the loop.
2032 --
2033 l_prev_person_id := l_person_id;
2034 l_prev_asg_id := l_assignment_id;
2035 l_prev_tax_unit_id := l_tax_unit_id;
2036 hr_utility.trace('In action_creation, l_prev_person_id= '||l_prev_person_id);
2037 hr_utility.trace('In action_creation, l_prev_asg_id= '||l_prev_asg_id);
2038 hr_utility.trace('In action_creation, l_prev_tax_unit_id= '||l_prev_tax_unit_id);
2039 --
2040
2041 end if; /* l_person and l_tax_unit are different from the previous values */
2042 end if; /*l_psd_balance_value= 0 check*/
2043 l_psd_balance_value :=0;
2044 END LOOP;
2045
2046 end action_creation;
2047
2048
2049 /* Name : chk_gre_archive
2050 Purpose : Function to check if the employer level data has been archived
2051 or not.
2052 Arguments :
2053 Notes :
2054 */
2055
2056 function chk_gre_archive (p_payroll_action_id number) return boolean is
2057
2058 l_flag varchar2(1);
2059
2060 cursor c_chk_payroll_action is
2061 select 'Y'
2062 from dual
2063 where exists (select null
2064 from ff_archive_items fai
2065 where fai.context1 = p_payroll_action_id
2066 and archive_type = 'PPA');
2067 begin
2068
2069 hr_utility.trace('chk_gre_archive - checking g_archive_flag');
2070
2071 if g_archive_flag = 'Y' then
2072 hr_utility.trace('chk_gre_archive - g_archive_flag is Y');
2073 return (TRUE);
2074 else
2075
2076 hr_utility.trace('chk_gre_archive - opening cursor');
2077
2078 open c_chk_payroll_action;
2079 fetch c_chk_payroll_action into l_flag;
2080 if c_chk_payroll_action%FOUND then
2081 hr_utility.trace('chk_gre_archive - found in cursor');
2082 g_archive_flag := 'Y';
2083 else
2084 hr_utility.trace('chk_gre_archive - not found in cursor');
2085 g_archive_flag := 'N';
2086 end if;
2087
2088 hr_utility.trace('chk_gre_archive - closing cursor');
2089 close c_chk_payroll_action;
2090 if g_archive_flag = 'Y' then
2091 hr_utility.trace('chk_gre_archive - returning true');
2092 return (TRUE);
2093 else
2094 hr_utility.trace('chk_gre_archive - returning false');
2095 return(FALSE);
2096 end if;
2097 end if;
2098 end chk_gre_archive;
2099
2100
2101 /* Name : archive_gre_data
2102 Purpose : This performs the US specific employer data archiving.
2103 Arguments :
2104 Notes :
2105 */
2106
2107 procedure archive_gre_data(p_payroll_action_id in number,
2108 p_tax_unit_id in number)
2109 is
2110
2111 CURSOR c_get_psd_rules
2112 IS
2113 select
2114 substr(hoi.org_information10,1,6) value1,
2115 hoi.org_information20 value2
2116 from hr_organization_information hoi
2117 where hoi.organization_id = p_tax_unit_id
2118 and hoi.org_information_context || '' = 'PSD Reporting Rules';
2119
2120
2121 psd_rules_rec c_get_psd_rules%ROWTYPE;
2122
2123 l_user_entity_id ff_database_items.user_entity_id%type;
2124 l_jurisdiction_code varchar2(100);
2125 l_user_entity_id_tab pay_us_psd_xml.number_data_type_table;
2126 l_value_tab pay_us_psd_xml.char240_data_type_table;
2127 l_object_version_number number;
2128 l_archive_item_id number;
2129 l_some_warning boolean;
2130 l_arch_gre_step number := 0;
2131 l_archive_level varchar2(240);
2132
2133 l_from number;
2134 l_to number;
2135 l_length number;
2136 l_value1 varchar2(240);
2137 l_value2 varchar2(240);
2138 l_value3 varchar2(240);
2139 l_value4 varchar2(240);
2140 l_value5 varchar2(240);
2141 l_value6 varchar2(240);
2142 l_value7 varchar2(240);
2143 l_value8 varchar2(240);
2144 l_value9 varchar2(240);
2145 l_value10 varchar2(240);
2146 l_value11 varchar2(240);
2147 l_value12 varchar2(240);
2148 l_value13 varchar2(240);
2149 l_value14 varchar2(240);
2150 l_value15 varchar2(240);
2151 l_value16 varchar2(240);
2152 l_value17 varchar2(240);
2153 l_value18 varchar2(240);
2154 l_value19 varchar2(240);
2155 l_value20 varchar2(240);
2156
2157 l_year_start DATE;
2158 l_year_end DATE;
2159 l_business_group_id NUMBER;
2160 l_state_abbrev pay_us_states.state_abbrev%type;
2161 l_state_code pay_us_states.state_code%type;
2162 l_report_type VARCHAR2(200);
2163 l_period_type VARCHAR2(200);
2164 l_period_start DATE;
2165 l_period_end DATE;
2166 l_tax_coll_dist_code VARCHAR2(2);
2167
2168 begin
2169 l_arch_gre_step :=10;
2170 /* get the state code for the 'PA' */
2171
2172 select state_code
2173 into l_state_code
2174 from pay_us_states
2175 where state_abbrev =
2176 (select pay_us_get_item_data_pkg.GET_CPROG_PARAMETER_VALUE(ppa.payroll_action_id,
2177 'TRANSFER_STATE')
2178 from pay_payroll_actions ppa
2179 where ppa.payroll_action_id = p_payroll_action_id);
2180
2181
2182 l_arch_gre_step :=20;
2183
2184 begin
2185
2186 l_arch_gre_step := 30;
2187
2188
2189 get_report_parameters( p_payroll_action_id,
2190 l_year_start,
2191 l_year_end,
2192 l_state_abbrev,
2193 l_state_code,
2194 l_report_type,
2195 l_business_group_id,
2196 l_period_type,
2197 l_period_start,
2198 l_period_end,
2199 l_tax_coll_dist_code
2200 );
2201
2202
2203 /* Archive the Employer country code */
2204
2205 l_arch_gre_step := 40;
2206
2207 l_archive_level := 'ER';
2208
2209
2210 begin
2211 select hrl.country,
2212 substr(hou.name,1,240),
2213 substr(hoi.org_information1,1,240)
2214 into l_value1,
2215 l_value2,
2216 l_value3
2217 from hr_locations hrl,
2218 hr_all_organization_units hou,
2219 hr_organization_information hoi
2220 where hou.organization_id = p_tax_unit_id
2221 and hoi.organization_id = hou.organization_id
2222 and hoi.org_information_context||'' = 'Employer Identification'
2223 and hrl.location_id = hou.location_id;
2224
2225 exception
2226 when no_data_found then
2227 l_value1 := null;
2228 l_value2 := null;
2229 l_value3 := null;
2230 hr_utility.trace('Error in archive_gre_data at step : ' ||
2231 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2232 end;
2233 l_arch_gre_step := 50;
2234
2235 /* Initialise the PL/SQL tables */
2236
2237 l_user_entity_id_tab.delete;
2238 l_value_tab.delete;
2239
2240 /* Assign values to the PL/SQL tables */
2241
2242 l_arch_gre_step := 60;
2243
2244 l_user_entity_id_tab(1) := get_user_entity_id('A_TAX_UNIT_COUNTRY_CODE');
2245 l_user_entity_id_tab(2) := get_user_entity_id('A_TAX_UNIT_NAME');
2246 l_user_entity_id_tab(3) := get_user_entity_id('A_TAX_UNIT_EMPLOYER_IDENTIFICATION_NUMBER');
2247 l_value_tab(1) := l_value1;
2248 l_value_tab(2) := l_value2;
2249 l_value_tab(3) := l_value3;
2250
2251 l_arch_gre_step := 70;
2252
2253 for i in 1..3 loop
2254 ff_archive_api.create_archive_item(
2255 p_archive_item_id => l_archive_item_id
2256 ,p_user_entity_id => l_user_entity_id_tab(i)
2257 ,p_archive_value => l_value_tab(i)
2258 ,p_archive_type => 'PA'
2259 ,p_action_id => p_payroll_action_id
2260 ,p_legislation_code => 'US'
2261 ,p_object_version_number => l_object_version_number
2262 ,p_context_name1 => 'TAX_UNIT_ID'
2263 ,p_context1 => p_tax_unit_id
2264 ,p_some_warning => l_some_warning
2265 );
2266 end loop;
2267
2268 l_arch_gre_step := 80;
2269
2270
2271 /* Archive the W2 Reporting Rules data */
2272 begin
2273 select
2274 --hoi.org_information6 value1,
2275 hoi.org_information8 value2,
2276 hoi.org_information9 value3,
2277 hoi.org_information10 value4,
2278 hoi.org_information11 value5,
2279 hoi.org_information12 value6,
2280 hoi.org_information13 value7,
2281 hoi.org_information14 value8,
2282 hoi.org_information15 value9,
2283 hoi.org_information16 value10,
2284 --hoi.org_information2 value11,
2285 --hoi.org_information3 value12,
2286 --hoi.org_information4 value13,
2287 --hoi.org_information5 value14,
2288 --hoi.org_information7 value15, /* Job Development Fee (AL) */
2289 hoi.org_information1 value16,
2290 hoi.org_information19 value19, -- Bug 6928011 access code (PR)
2291 hoi.org_information20 value20 --Bug 13255564
2292 into
2293 --l_value1,
2294 l_value2,
2295 l_value3,
2296 l_value4,
2297 l_value5,
2298 l_value6,
2299 l_value7,
2300 l_value8,
2301 l_value9,
2302 l_value10,
2303 --l_value11,
2304 --l_value12,
2305 --l_value13,
2306 --l_value14,
2307 --l_value15, /* Job Development Fee (AL) */
2308 l_value16,
2309 l_value19, -- Bug 6928011 access code (PR)
2310 l_value20
2311 from hr_organization_information hoi
2312 where hoi.organization_id = p_tax_unit_id
2313 and hoi.org_information_context || '' = 'W2 Reporting Rules';
2314 exception
2315 when no_data_found then
2316 --l_value1 := null;
2317 l_value2 := null;
2318 l_value3 := null;
2319 l_value4 := null;
2320 l_value5 := null;
2321 l_value6 := null;
2322 l_value7 := null;
2323 l_value8 := null;
2324 l_value9 := null;
2325 l_value10 := null;
2326 --l_value11 := null;
2327 --l_value12 := null;
2328 --l_value13 := null;
2329 --l_value14 := null;
2330 --l_value15 := null; /* Job Development Fee (AL) */
2331 l_value16 := null;
2332 l_value19 := null; -- Bug 6928011 access code ( PR)
2333 l_value20 := null;
2334 hr_utility.trace('Error in archive_gre_data at step :' ||
2335 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2336 end;
2337
2338 l_arch_gre_step := 90;
2339 /* Initialise the PL/SQL tables */
2340
2341 l_user_entity_id_tab.delete;
2342 l_value_tab.delete;
2343
2344 /* Assign values to the PL/SQL tables */
2345
2346 l_arch_gre_step := 100;
2347
2348 l_user_entity_id_tab(1) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W2_2678_FILER');
2349 l_user_entity_id_tab(2) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_COMPANY_NAME');
2350 l_user_entity_id_tab(3) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_CONTACT_NAME');
2351 l_user_entity_id_tab(4) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_NOTIFICATION_METHOD');
2352 l_user_entity_id_tab(5) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_PREPARER');
2353 l_user_entity_id_tab(6) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TERMINATED_GRE_INDICATOR');
2354 l_user_entity_id_tab(7) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_THIRD_PARTY_SICK_PAY');
2355 l_user_entity_id_tab(8) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_OTHER_EIN');
2356 l_user_entity_id_tab(9) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_TAX_JURISDICTION');
2357 l_user_entity_id_tab(10) := get_user_entity_id('A_LC_W2_REPORTING_RULES_ORG_W_2_TRANSMITTER');
2358 l_user_entity_id_tab(11) := get_user_entity_id ('A_LC_W2_REPORTING_RULES_ORG_ACCESS_CODE');
2359 l_user_entity_id_tab(12) := get_user_entity_id ('A_LC_W2_REPORTING_RULES_ORG_KIND_OF_EMPLOYER');
2360
2361 l_value_tab(1) := l_value2;
2362 l_value_tab(2) := l_value3;
2363 l_value_tab(3) := l_value4;
2364 l_value_tab(4) := l_value5;
2365 l_value_tab(5) := l_value6;
2366 l_value_tab(6) := l_value7;
2367 l_value_tab(7) := l_value8;
2368 l_value_tab(8) := l_value9;
2369 l_value_tab(9) := l_value10;
2370 l_value_tab(10) := l_value16;
2371 l_value_tab(11) := l_value19;
2372 l_value_tab(12) := l_value20;
2373
2374 l_arch_gre_step := 110;
2375
2376
2377 for i in 1..12 loop
2378 ff_archive_api.create_archive_item(
2379 p_archive_item_id => l_archive_item_id
2380 ,p_user_entity_id => l_user_entity_id_tab(i)
2381 ,p_archive_value => l_value_tab(i)
2382 ,p_archive_type => 'PA'
2383 ,p_action_id => p_payroll_action_id
2384 ,p_legislation_code => 'US'
2385 ,p_object_version_number => l_object_version_number
2386 ,p_context_name1 => 'TAX_UNIT_ID'
2387 ,p_context1 => p_tax_unit_id
2388 ,p_some_warning => l_some_warning
2389 );
2390 end loop;
2391
2392 l_arch_gre_step := 120;
2393
2394 begin
2395 select hoi.org_information4 value1,
2396 hoi.org_information8 value2
2397 into l_value1,
2398 l_value2
2399 from hr_organization_information hoi
2400 where hoi.organization_id = p_tax_unit_id
2401 and hoi.org_information_context || '' = 'Federal Tax Rules';
2402 exception
2403 when no_data_found then
2404 l_value1 := null;
2405 l_value2 := null;
2406 hr_utility.trace('Error in archive_gre_data at step : ' ||
2407 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2408 end;
2409
2410 /* Initialise the PL/SQL tables */
2411
2412 l_user_entity_id_tab.delete;
2413 l_value_tab.delete;
2414
2415 /* Assign values to the PL/SQL tables */
2416
2417 l_arch_gre_step := 130;
2418 l_user_entity_id_tab(1) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_TYPE_OF_EMPLOYMENT');
2419 l_user_entity_id_tab(2) := get_user_entity_id('A_LC_FEDERAL_TAX_RULES_ORG_GOVERNMENT_EMPLOYER');
2420 l_value_tab(1) := l_value1;
2421 l_value_tab(2) := l_value2;
2422
2423 l_arch_gre_step := 150;
2424
2425 for i in 1..2 loop
2426 ff_archive_api.create_archive_item(
2427 p_archive_item_id => l_archive_item_id
2428 ,p_user_entity_id => l_user_entity_id_tab(i)
2429 ,p_archive_value => l_value_tab(i)
2430 ,p_archive_type => 'PA'
2431 ,p_action_id => p_payroll_action_id
2432 ,p_legislation_code => 'US'
2433 ,p_object_version_number => l_object_version_number
2434 ,p_context_name1 => 'TAX_UNIT_ID'
2435 ,p_context1 => p_tax_unit_id
2436 ,p_some_warning => l_some_warning
2437 );
2438 end loop;
2439
2440
2441 l_arch_gre_step := 180;
2442 /* Archive the FIPS Code for a state code */
2443 /* Archive the company SIT state id */
2444 /* Archive the company SUI state id */
2445
2446 begin
2447 select to_char(rules.fips_code) value1,
2448 ltrim(rtrim(target.org_information3)) value2,
2449 ltrim(rtrim(target.org_information2)) value3
2450 into l_value1,
2451 l_value2,
2452 l_value3
2453 from pay_state_rules rules,
2454 pay_us_states pus,
2455 hr_organization_information target
2456 where substr(rules.jurisdiction_code, 1, 2) =l_state_code
2457 and target.organization_id = p_tax_unit_id
2458 and target.org_information_context || '' = 'State Tax Rules'
2459 and target.org_information1 = pus.state_abbrev
2460 and pus.state_code = l_state_code;
2461 exception
2462 when no_data_found then
2463 l_value1 := null;
2464 l_value2 := null;
2465 l_value3 := null;
2466 hr_utility.trace('Error in archive_gre_data at step : ' ||
2467 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2468 end;
2469
2470 /* Initialise the PL/SQL tables */
2471
2472 l_user_entity_id_tab.delete;
2473 l_value_tab.delete;
2474
2475 /* Assign values to the PL/SQL tables */
2476
2477 l_user_entity_id_tab(1) := get_user_entity_id('A_FIPS_CODE_JD');
2478 l_user_entity_id_tab(2) := get_user_entity_id('A_STATE_TAX_RULES_ORG_SIT_COMPANY_STATE_ID');
2479 l_user_entity_id_tab(3) := get_user_entity_id('A_STATE_TAX_RULES_ORG_SUI_COMPANY_STATE_ID');
2480 l_value_tab(1) := l_value1;
2481 l_value_tab(2) := l_value2;
2482 l_value_tab(3) := l_value3;
2483
2484 l_arch_gre_step := 190;
2485 for i in 1..3 loop
2486 ff_archive_api.create_archive_item(
2487 p_archive_item_id => l_archive_item_id
2488 ,p_user_entity_id => l_user_entity_id_tab(i)
2489 ,p_archive_value => l_value_tab(i)
2490 ,p_archive_type => 'PA'
2491 ,p_action_id => p_payroll_action_id
2492 ,p_legislation_code => 'US'
2493 ,p_object_version_number => l_object_version_number
2494 ,p_context_name1 => 'TAX_UNIT_ID'
2495 ,p_context1 => p_tax_unit_id
2496 ,p_context_name2 => 'JURISDICTION_CODE'
2497 ,p_context2 => l_state_code ||'-000-0000'
2498 ,p_some_warning => l_some_warning
2499 );
2500 end loop;
2501
2502 g_archive_flag := 'Y';
2503
2504 exception
2505 when others then
2506 g_archive_flag := 'N';
2507 Raise_application_error(-20001,'Error in archive_gre_data after step : ' ||
2508 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2509 end;
2510
2511
2512 begin
2513
2514 for psd_rules_rec in c_get_psd_rules
2515 loop
2516
2517 /*select
2518 substr(hoi.org_information10,1,6) value1,
2519 hoi.org_information20 value2
2520 into
2521 l_value1,
2522 l_value2
2523 from hr_organization_information hoi
2524 where hoi.organization_id = p_tax_unit_id
2525 and hoi.org_information_context || '' = 'PSD Reporting Rules';
2526 exception*/
2527
2528 if c_get_psd_rules%notfound then
2529 l_value1 := null;
2530 l_value2 := null;
2531 hr_utility.trace('Error in archive_gre_data at step :' ||
2532 to_char(l_arch_gre_step) || 'sqlcode : ' || to_char(sqlcode));
2533 end if;
2534
2535
2536 /* Initialise the PL/SQL tables */
2537
2538 l_user_entity_id_tab.delete;
2539 l_value_tab.delete;
2540
2541 /* Assign values to the PL/SQL tables */
2542
2543 l_arch_gre_step := 50;
2544
2545 l_user_entity_id_tab(1) := get_user_entity_id('A_PSD_REPORTING_RULES_ORG_EMPLOYER_PSD_CODE');
2546 l_user_entity_id_tab(2) := get_user_entity_id('A_PSD_REPORTING_RULES_ORG_EMPLOYER_TAX_ACCOUNT_NUMBER');
2547
2548 l_value_tab(1) := psd_rules_rec.value1;
2549 l_value_tab(2) := psd_rules_rec.value2;
2550
2551 l_jurisdiction_code :=l_state_code||'-000000-' || psd_rules_rec.value1;
2552
2553 for i in 1..2 loop
2554 ff_archive_api.create_archive_item(
2555 p_archive_item_id => l_archive_item_id
2556 ,p_user_entity_id => l_user_entity_id_tab(i)
2557 ,p_archive_value => l_value_tab(i)
2558 ,p_archive_type => 'PA'
2559 ,p_action_id => p_payroll_action_id
2560 ,p_legislation_code => 'US'
2561 ,p_object_version_number => l_object_version_number
2562 ,p_context_name1 => 'TAX_UNIT_ID'
2563 ,p_context1 => p_tax_unit_id
2564 ,p_context_name2 => 'JURISDICTION_CODE'
2565 ,p_context2 => l_jurisdiction_code
2566 ,p_some_warning => l_some_warning
2567 );
2568 end loop;
2569 end loop;
2570 end; ----Archiving PSD Reporting Rules End.
2571
2572 g_archive_flag := 'Y';
2573 exception
2574 when others then
2575 g_archive_flag := 'N';
2576
2577 end archive_gre_data;
2578
2579
2580 /*
2581 Name : ACTION_ARCHINIT
2582 Description : This performs the context initialization
2583 Arguments
2584 p_payroll_action_id payroll action id for the report
2585
2586 */
2587
2588 PROCEDURE action_archinit(
2589 p_payroll_action_id in number) is
2590
2591 l_year_start DATE;
2592 l_year_end DATE;
2593 l_business_group_id NUMBER;
2594 l_state_abbrev pay_us_states.state_abbrev%type;
2595 l_state_code pay_us_states.state_code%type;
2596 l_report_type VARCHAR2(200);
2597 l_period_type VARCHAR2(200);
2598 l_period_start DATE;
2599 l_period_end DATE;
2600 l_tax_coll_dist_code VARCHAR2(2);
2601
2602 l_jurisdiction_code VARCHAR2(30);
2603 --l_tax_unit_id hr_organization_units.organization_id%type;
2604 l_archive boolean:= FALSE;
2605 --l_formula_name ff_formulas_f.formula_name%TYPE;
2606 l_step number;
2607 --ld_end_date DATE;
2608 --ld_start_date DATE;
2609 --ln_business_group_id NUMBER;
2610 --ln_person_id NUMBER := 0;
2611 --ln_asg_set NUMBER := 0;
2612 --ln_ssn NUMBER;
2613 --ln_year NUMBER := 0;
2614 ln_psd_count NUMBER := 0;
2615
2616 lv_balance_name VARCHAR2(80);
2617 ln_balance_type_id pay_balance_types.balance_type_id%type;
2618
2619 cursor c_get_min_chunk is
2620 select min(paa.chunk_number)
2621 from pay_assignment_actions paa
2622 where paa.payroll_action_id = p_payroll_action_id;
2623
2624
2625 cursor c_get_psd_balances is
2626 select flv.meaning, pbt.balance_type_id
2627 from pay_balance_types pbt,
2628 fnd_application fa,
2629 fnd_lookup_values flv,
2630 fnd_lookup_types flt
2631 where pbt.legislation_code = 'US'
2632 and (
2633 pbt.balance_name = substr(flv.meaning,
2634 1 , decode(instr(flv.meaning, 'MONTH'),
2635 0, length(flv.meaning),
2636 instr(flv.meaning, 'MONTH')-2))
2637 or
2638 pbt.balance_name = substr(flv.meaning,
2639 1 , decode(instr(flv.meaning, 'QTD'),
2640 0, length(flv.meaning),
2641 instr(flv.meaning, 'QTD')-2))
2642 )
2643 and fa.application_short_name = 'PAY'
2644 and flv.lookup_type = flt.lookup_type
2645 and flT.application_id = 801
2646 and flt.lookup_type = 'PSD WAGE LISTING RPT BALANCES'
2647 and flv.enabled_flag = 'Y'
2648 and flv.language = 'US';
2649
2650
2651
2652 BEGIN
2653
2654 hr_utility.set_location ('action_archinit',1);
2655
2656
2657 hr_utility.trace ('action_archinit getting report parameters ');
2658
2659 get_report_parameters( p_payroll_action_id,
2660 l_year_start,
2661 l_year_end,
2662 l_state_abbrev,
2663 l_state_code,
2664 l_report_type,
2665 l_business_group_id,
2666 l_period_type,
2667 l_period_start,
2668 l_period_end,
2669 l_tax_coll_dist_code
2670 );
2671
2672 hr_utility.trace ('action_archinit got report parameters ');
2673
2674 l_jurisdiction_code := '99-999-9999';
2675
2676 hr_utility.set_location ('action_archinit getting min chunk number',10);
2677
2678 open c_get_min_chunk;
2679 fetch c_get_min_chunk into g_min_chunk;
2680 l_step := 10;
2681 if c_get_min_chunk%NOTFOUND then
2682 g_min_chunk := -1;
2683 raise_application_error(-20001,'action_archinit: Assignment actions not created!!!');
2684 end if;
2685 close c_get_min_chunk;
2686
2687 hr_utility.set_location ('action_archinit min chunk is ' || to_char(g_min_chunk),12);
2688
2689
2690 l_archive := chk_gre_archive(p_payroll_action_id);
2691
2692
2693 hr_utility.trace ('action_archinit g_archive_flag is ' || g_archive_flag);
2694
2695 open c_get_psd_balances;
2696 loop
2697 fetch c_get_psd_balances into lv_balance_name, ln_balance_type_id;
2698 if c_get_psd_balances%notfound then
2699 hr_utility.set_location('action_archinit c_get_psd_balances not found', 170);
2700 exit;
2701 end if;
2702 hr_utility.set_location('action_archinit', 180);
2703
2704 ln_psd_count := ln_psd_count + 1;
2705
2706 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name
2707 := lv_balance_name;
2708
2709
2710 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id
2711 := ln_balance_type_id;
2712 hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2713 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2714 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2715 hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2716 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2717 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2718
2719
2720 IF (instr(lv_balance_name, 'MONTH') <> 0) THEN
2721
2722 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).qtd_def_bal_id
2723 := NULL;
2724 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).month_def_bal_id
2725 := get_defined_balance_id(l_business_group_id,
2726 substr(lv_balance_name, 1 , (instr(lv_balance_name, 'MONTH')-2)),
2727 '_PER_JD_GRE_MONTH');
2728
2729 select USER_ENTITY_NAME
2730 into pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name
2731 from ff_user_entities
2732 where creator_id= (pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).month_def_bal_id)
2733 and CREATOR_TYPE='B';
2734
2735 ELSIF (instr(lv_balance_name, 'QTD') <> 0) THEN
2736 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).month_def_bal_id
2737 := NULL;
2738 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).qtd_def_bal_id
2739 := get_defined_balance_id(l_business_group_id,
2740 substr(lv_balance_name, 1 , (instr(lv_balance_name, 'QTD')-2)),
2741 '_PER_JD_GRE_QTD');
2742 select USER_ENTITY_NAME
2743 into pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name
2744 from ff_user_entities
2745 where creator_id= (pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).qtd_def_bal_id)
2746 and CREATOR_TYPE='B';
2747 END IF;
2748
2749 hr_utility.set_location('action_archinit', 190);
2750 hr_utility.trace('PSD DBI Name= ' ||
2751 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).dbi_name);
2752 hr_utility.trace('lv_balance_name= ' ||lv_balance_name);
2753 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name= ' ||
2754 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_name);
2755 hr_utility.trace('ln_balance_type_id= ' ||ln_balance_type_id);
2756 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id= ' ||
2757 pay_us_psd_xml.ltr_psd_tax_bal(ln_psd_count).balance_type_id);
2758
2759 end loop;
2760 close c_get_psd_balances;
2761
2762 hr_utility.trace('PSD Balance Loop Count = ' ||
2763 pay_us_psd_xml.ltr_psd_tax_bal.count);
2764
2765 END action_archinit;
2766
2767
2768
2769 /*
2770 Name : ARCHIVE_DATA
2771 Description : This process archives the employee level data for
2772 PSD Periodic Wage Listing Report
2773 p_assactid assignment action id
2774 p_effective_date effective date
2775
2776 */
2777
2778 procedure archive_data(p_assactid in number, p_effective_date in date) is
2779
2780
2781 l_year_start DATE;
2782 l_year_end DATE;
2783 l_business_group_id NUMBER;
2784 l_state_abbrev pay_us_states.state_abbrev%type;
2785 l_state_code pay_us_states.state_code%type;
2786 l_report_type VARCHAR2(200);
2787 l_period_type VARCHAR2(200);
2788 l_period_start DATE;
2789 l_period_end DATE;
2790 l_tax_coll_dist_code VARCHAR2(2);
2791
2792 l_aaid pay_assignment_actions.assignment_action_id%type;
2793 l_asgid pay_assignment_actions.assignment_id%type;
2794 l_eff_date date;
2795 l_date_earned date;
2796 l_taxunitid pay_assignment_actions.tax_unit_id%type;
2797 l_chunk number;
2798 l_payroll_action_id number;
2799 l_context_no number := 60;
2800 l_count number := 0;
2801 l_def_bal_id pay_defined_balances.defined_balance_id%TYPE;
2802 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
2803
2804 /* Local variables to select only those employees those PSD Withheld <>0*/
2805
2806 l_psd_def_bal_id pay_defined_balances.defined_balance_id%type;
2807 l_psd_user_entity_id ff_database_items.user_entity_id%type;
2808 l_psd_balance_value number(15,2):=0;
2809
2810 /* Local variables to archive PSD balances*/
2811 lv_balance_name VARCHAR2(80);
2812 ln_balance_type_id pay_balance_types.balance_type_id%type;
2813 lv_balance_dimension VARCHAR2(80);
2814 ln_qtd_def_bal_id pay_defined_balances.defined_balance_id%type;
2815 ln_month_def_bal_id pay_defined_balances.defined_balance_id%type;
2816 ln_city_wk_def_bal_id pay_defined_balances.defined_balance_id%type;
2817 ln_psd_count NUMBER := 0;
2818 ln_bal_value NUMBER(15,2) := 0;
2819
2820 l_user_entity_id ff_database_items.user_entity_id%type;
2821 l_jurisdiction VARCHAR2(30);
2822
2823 l_user_entity_id_tab pay_us_psd_xml.number_data_type_table;
2824 -- l_user_entity_tab pay_us_psd_xml.char240_data_type_table;
2825 l_value_tab pay_us_psd_xml.char240_data_type_table;
2826 l_object_version_number number;
2827 l_archive_item_id number;
2828 l_some_warning boolean;
2829
2830
2831 /* for archiving employee data*/
2832
2833 l_person_id number;
2834 l_first_name varchar2(240);
2835 l_middle_name varchar2(240);
2836 l_last_name varchar2(240);
2837 l_employee_number varchar2(240);
2838 l_suffix varchar2(240);
2839 l_national_identifier varchar2(240);
2840
2841
2842 /* for archiving employee address data*/
2843
2844 l_address_line1 varchar2(240);
2845 l_address_line2 varchar2(240);
2846 l_address_line3 varchar2(240);
2847 l_town_or_city varchar2(80);
2848 l_province_code varchar2(80);
2849 l_postal_code varchar2(80);
2850 l_telephone_number varchar2(80);
2851 l_country_code varchar2(80);
2852
2853 -- Modified for Bug 14640336
2854 cursor c_get_jurisdiction (cp_person_id number
2855 ,cp_tax_unit_id number) is
2856 select distinct puar.jurisdiction_code
2857 from pay_us_asg_reporting puar,
2858 per_all_Assignments_f paf
2859 where puar.assignment_id = paf.assignment_id
2860 and paf.person_id=cp_person_id
2861 and puar.tax_unit_id = cp_tax_unit_id
2862 and length(puar.jurisdiction_code)=16;
2863
2864 cursor c_get_city is
2865 select distinct pcty.jurisdiction_code pcty
2866 from pay_us_emp_city_tax_rules_f pcty,
2867 per_assignments_f paf1,
2868 per_assignments_f paf
2869 where paf.assignment_id = l_asgid
2870 and paf.effective_end_date >= l_period_start
2871 and paf.effective_start_date <= l_period_end
2872 and paf1.person_id = paf.person_id
2873 and paf1.effective_end_date >= l_period_start
2874 and paf1.effective_start_date <= l_period_end
2875 and pcty.assignment_id = paf1.assignment_id
2876 and pcty.effective_start_date <= l_period_end
2877 and pcty.effective_end_date >= l_period_start;
2878
2879
2880 CURSOR c_get_latest_asg(p_assignment_id number ) IS
2881 SELECT /*+ORDERED*/
2882 PAA.ASSIGNMENT_ACTION_ID,
2883 PPA.EFFECTIVE_DATE
2884 FROM PER_ALL_ASSIGNMENTS_F PAF1,
2885 PER_ALL_ASSIGNMENTS_F PAF ,
2886 PAY_ASSIGNMENT_ACTIONS PAA,
2887 PAY_PAYROLL_ACTIONS PPA ,
2888 PAY_ACTION_CLASSIFICATIONS PAC
2889 WHERE PAF1.ASSIGNMENT_ID = p_assignment_id
2890 AND PAF.PERSON_ID = PAF1.PERSON_ID
2891 AND PAA.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
2892 AND PAA.TAX_UNIT_ID = l_taxunitid
2893 AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
2894 AND PPA.ACTION_TYPE = PAC.ACTION_TYPE
2895 AND PAC.CLASSIFICATION_NAME = 'SEQUENCED'
2896 AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE AND PAF.EFFECTIVE_END_DATE
2897 AND PPA.EFFECTIVE_DATE BETWEEN l_period_start AND l_period_end
2898 AND ((NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NULL
2899 AND PAA.SOURCE_ACTION_ID IS NULL)
2900 OR (NVL(PAA.RUN_TYPE_ID, PPA.RUN_TYPE_ID) IS NOT NULL
2901 AND PAA.SOURCE_ACTION_ID IS NOT NULL )
2902 OR (PPA.ACTION_TYPE = 'V'
2903 AND PPA.RUN_TYPE_ID IS NULL
2904 AND PAA.RUN_TYPE_ID IS NOT NULL
2905 AND PAA.SOURCE_ACTION_ID IS NULL))
2906 ORDER BY PAA.ACTION_SEQUENCE DESC;
2907
2908
2909 CURSOR c_get_employee_data(l_asgid number ) IS
2910 select PEOPLE.person_id,
2911 PEOPLE.first_name,
2912 PEOPLE.middle_names,
2913 PEOPLE.last_name,
2914 PEOPLE.employee_number,
2915 PEOPLE.suffix,
2916 replace(PEOPLE.national_identifier,' ')
2917 from per_all_assignments_f ASSIGN
2918 ,per_all_people_f PEOPLE
2919 where ASSIGN.assignment_id =l_asgid
2920 and PEOPLE.person_id = ASSIGN.person_id
2921 and l_date_earned BETWEEN ASSIGN.effective_start_date
2922 AND ASSIGN.effective_end_date
2923 and l_date_earned BETWEEN PEOPLE.effective_start_date
2924 AND PEOPLE.effective_end_date;
2925
2926 begin
2927 hr_utility.set_location ('archive_data',1);
2928 hr_utility.trace('p_assactid is : ' || to_char(p_assactid));
2929
2930
2931 --Added by skchalla for PSD year end reporting bug 14799833
2932 IF pay_magtape_generic.get_parameter_value
2933 ('TRANSFER_PERIOD_TYPE') <> 'Yearly' Then
2934
2935 SELECT aa.assignment_id,
2936 pay_magtape_generic.date_earned (p_effective_date,aa.assignment_id),
2937 aa.tax_unit_id,
2938 aa.chunk_number,
2939 aa.payroll_action_id
2940 into l_asgid,
2941 l_date_earned,
2942 l_taxunitid,
2943 l_chunk,
2944 l_payroll_action_id
2945 FROM pay_assignment_actions aa
2946 WHERE aa.assignment_action_id = p_assactid;
2947
2948 hr_utility.trace('l_asgid is : ' || to_char(l_asgid));
2949 hr_utility.trace('l_date_earned is : ' || to_char(l_date_earned));
2950 hr_utility.trace('l_taxunitid is : ' || to_char(l_taxunitid));
2951 hr_utility.trace('l_chunk is : ' || to_char(l_chunk));
2952 hr_utility.trace('l_payroll_action_id is : ' || to_char(l_payroll_action_id));
2953
2954 hr_utility.trace ('archive_data getting report parameters ');
2955
2956 get_report_parameters( l_payroll_action_id,
2957 l_year_start,
2958 l_year_end,
2959 l_state_abbrev,
2960 l_state_code,
2961 l_report_type,
2962 l_business_group_id,
2963 l_period_type,
2964 l_period_start,
2965 l_period_end,
2966 l_tax_coll_dist_code
2967 );
2968
2969 hr_utility.trace ('archive_data got report parameters ');
2970
2971 hr_utility.trace('g_min_chunk is : ' || to_char(g_min_chunk));
2972 hr_utility.trace('g_archive_flag is : ' || to_char(g_archive_flag));
2973 /* if l_chunk = g_min_chunk and g_archive_flag = 'N' then
2974
2975 hr_utility.trace('archive_data archiving employer data');
2976
2977 archive_gre_data(
2978 p_payroll_action_id => l_payroll_action_id,
2979 p_tax_unit_id => l_taxunitid);
2980
2981 hr_utility.trace('archive_data archived employer data');
2982 end if;*/
2983
2984 hr_utility.set_location ('archive_data',2);
2985
2986 hr_utility.trace('assignment '|| to_char(l_asgid));
2987 hr_utility.trace('date_earned '|| to_char(l_date_earned));
2988 hr_utility.trace('tax_unit_id '|| to_char(l_taxunitid));
2989
2990 open c_get_latest_asg(l_asgid );
2991 fetch c_get_latest_asg into l_aaid, l_eff_date;
2992 hr_utility.trace('l_aaid in archive code '||to_char(l_aaid));
2993 close c_get_latest_asg;
2994
2995 -- Added for Bug 14640336
2996 /* select person_id into l_person_id
2997 from per_all_assignments_f
2998 where assignment_id=l_asgid;*/
2999
3000 --Modified for the Bug 14690909
3001 select serial_number into l_person_id
3002 from pay_assignment_actions
3003 where assignment_action_id=p_assactid;
3004
3005 -- Modified for Bug 14640336
3006 open c_get_jurisdiction( l_person_id, l_taxunitid );
3007 loop
3008 fetch c_get_jurisdiction into l_jurisdiction;
3009 exit when c_get_jurisdiction%notfound;
3010
3011 pay_balance_pkg.set_context('TAX_UNIT_ID',l_taxunitid);
3012
3013 pay_balance_pkg.set_context ('JURISDICTION_CODE',l_jurisdiction);
3014
3015 hr_utility.trace('l_jurisdiction '|| to_char(l_jurisdiction));
3016
3017 --Archiving this balance to get the value of PSD Withheld
3018
3019 hr_utility.trace('l_period_type in archive data: '||l_period_type);
3020
3021 --check if PSD Withheld balances are <>0
3022
3023 --Archiving the balance value according to the period type
3024 if (l_period_type='Monthly') then
3025 hr_utility.trace('l_period_type in archive data: '||l_period_type);
3026 l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
3027 'PSD Withheld',
3028 '_PER_JD_GRE_MONTH');
3029 l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_MONTH');
3030 l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
3031 ,p_balcall_aaid => l_aaid);
3032 hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
3033 elsif (l_period_type='Quarterly') then
3034 hr_utility.trace('l_period_type in archive data: '||l_period_type);
3035 l_psd_def_bal_id := get_defined_balance_id(l_business_group_id,
3036 'PSD Withheld',
3037 '_PER_JD_GRE_QTD');
3038 l_psd_user_entity_id:= get_user_entity_id('PSD_WITHHELD_PER_JD_GRE_QTD');
3039 l_psd_balance_value := get_balance_value(p_defined_balance_id => l_psd_def_bal_id
3040 ,p_balcall_aaid => l_aaid);
3041 hr_utility.trace('l_psd_bal_value in archive data: '||l_psd_balance_value);
3042 end if; --period type
3043
3044 if (l_psd_balance_value<>0) then
3045
3046 if pay_us_psd_xml.ltr_psd_tax_bal.count > 0 then
3047 for k in pay_us_psd_xml.ltr_psd_tax_bal.first..
3048 pay_us_psd_xml.ltr_psd_tax_bal.last loop
3049
3050 lv_balance_name :=
3051 pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name;
3052 ln_balance_type_id :=
3053 pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id;
3054 ln_qtd_def_bal_id :=
3055 pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id;
3056 ln_month_def_bal_id :=
3057 pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id;
3058
3059 hr_utility.trace('k ='||k);
3060 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name ='|| pay_us_psd_xml.ltr_psd_tax_bal(k).balance_name);
3061 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).balance_type_id);
3062 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).qtd_def_bal_id);
3063 hr_utility.trace('pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id ='||pay_us_psd_xml.ltr_psd_tax_bal(k).month_def_bal_id);
3064 hr_utility.trace('lv_balance_name ='||lv_balance_name);
3065 hr_utility.trace('ln_balance_type_id ='||ln_balance_type_id);
3066 hr_utility.trace('ln_qtd_def_bal_id='||ln_qtd_def_bal_id);
3067 hr_utility.trace('ln_month_def_bal_id ='||ln_month_def_bal_id);
3068 hr_utility.trace('DBI ='||pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name);
3069
3070 --if p_action_type in ('U', 'P') then
3071
3072 if ln_qtd_def_bal_id is NULL then
3073 ln_bal_value := get_balance_value(
3074 p_defined_balance_id => ln_month_def_bal_id
3075 ,p_balcall_aaid => l_aaid);
3076 hr_utility.trace('ln_bal_value (in month) ='||ln_bal_value);
3077 elsif ln_month_def_bal_id is NULL then
3078 ln_bal_value := get_balance_value(
3079 p_defined_balance_id => ln_qtd_def_bal_id
3080 ,p_balcall_aaid => l_aaid);
3081 hr_utility.trace('ln_bal_value (in qtd) ='||ln_bal_value);
3082 end if;
3083 hr_utility.trace('ln_bal_value = '|| ln_bal_value);
3084
3085 l_user_entity_id:= get_user_entity_id('A_'||(pay_us_psd_xml.ltr_psd_tax_bal(k).dbi_name));
3086
3087 ff_archive_api.create_archive_item(
3088 p_archive_item_id => l_archive_item_id
3089 ,p_user_entity_id => l_user_entity_id
3090 ,p_archive_value => to_char(ln_bal_value)
3091 ,p_archive_type => ''--'AAP'
3092 ,p_action_id => p_assactid
3093 ,p_legislation_code => 'US'
3094 ,p_object_version_number => l_object_version_number
3095 ,p_context_name1 => 'TAX_UNIT_ID'
3096 ,p_context1 => l_taxunitid
3097 ,p_context_name2 => 'JURISDICTION_CODE'
3098 ,p_context2 => l_jurisdiction
3099 ,p_some_warning => l_some_warning
3100 );
3101 ln_bal_value := 0;
3102
3103 end loop; -- ltr_psd_tax_bal
3104 end if; -- ltr_psd_tax_bal
3105
3106 --Archiving this balance to get the value of R/NR flag
3107 ln_city_wk_def_bal_id := get_defined_balance_id(l_business_group_id,
3108 'City WK PSD Withheld',
3109 '_PER_JD_GRE_RUN');
3110
3111 l_user_entity_id:= get_user_entity_id('A_CITY_WK_PSD_WITHHELD_PER_JD_GRE_RUN');
3112
3113 ln_bal_value := get_balance_value(p_defined_balance_id => ln_city_wk_def_bal_id
3114 ,p_balcall_aaid => l_aaid);
3115
3116
3117 ff_archive_api.create_archive_item(
3118 p_archive_item_id => l_archive_item_id
3119 ,p_user_entity_id => l_user_entity_id
3120 ,p_archive_value => to_char(ln_bal_value)
3121 ,p_archive_type => ''--'AAP'
3122 ,p_action_id => p_assactid
3123 ,p_legislation_code => 'US'
3124 ,p_object_version_number => l_object_version_number
3125 ,p_context_name1 => 'TAX_UNIT_ID'
3126 ,p_context1 => l_taxunitid
3127 ,p_context_name2 => 'JURISDICTION_CODE'
3128 ,p_context2 => l_jurisdiction
3129 ,p_some_warning => l_some_warning
3130 );
3131
3132 ln_bal_value := 0;
3133
3134 else
3135 null;
3136 end if; --psd bal value <>0
3137
3138 end loop; -- Ending the loop of multipile jurisdiction codes for 1 asg id
3139 close c_get_jurisdiction;
3140
3141 begin
3142
3143 open c_get_employee_data(l_asgid);
3144 fetch c_get_employee_data into l_person_id,
3145 l_first_name,
3146 l_middle_name,
3147 l_last_name,
3148 l_employee_number,
3149 l_suffix,
3150 l_national_identifier;
3151
3152 close c_get_employee_data;
3153
3154 exception
3155 when no_data_found then
3156 l_first_name := null;
3157 l_middle_name := null;
3158 l_last_name := null;
3159 l_employee_number := null;
3160 l_national_identifier := null;
3161 l_suffix := null;
3162 end;
3163
3164 begin
3165
3166
3167 hr_utility.trace('Initialised Pl/SQL table');
3168 l_user_entity_id_tab.delete;
3169 l_value_tab.delete;
3170
3171 -- l_index := 0;
3172
3173 /* Assign values to the PL/SQL tables */
3174 l_count:=0;
3175 l_count:= l_count+1;
3176 l_user_entity_id_tab(1) := get_user_entity_id('A_PER_NATIONAL_IDENTIFIER');
3177 l_value_tab(1) := to_char(l_national_identifier);
3178 l_count:= l_count+1;
3179 l_user_entity_id_tab(2) := get_user_entity_id('A_PER_FIRST_NAME');
3180 l_value_tab(2) := to_char(l_first_name);
3181 l_count:= l_count+1;
3182 l_user_entity_id_tab(3) := get_user_entity_id('A_PER_MIDDLE_NAMES');
3183 l_value_tab(3) := to_char(l_middle_name);
3184 l_count:= l_count+1;
3185 l_user_entity_id_tab(4) := get_user_entity_id('A_PER_LAST_NAME');
3186 l_value_tab(4) := to_char(l_last_name);
3187 l_count:= l_count+1;
3188 l_user_entity_id_tab(5) := get_user_entity_id('A_PER_SUFFIX');
3189 l_value_tab(5) := to_char(l_suffix);
3190
3191 for i in 1..l_count loop
3192
3193
3194 ff_archive_api.create_archive_item(
3195 -- p_validate => 'TRUE'
3196 p_archive_item_id => l_archive_item_id
3197 ,p_user_entity_id => l_user_entity_id_tab(i)
3198 ,p_archive_value => l_value_tab(i)
3199 ,p_archive_type => ''--'AAP'
3200 ,p_action_id => p_assactid
3201 ,p_legislation_code => 'US'
3202 ,p_object_version_number => l_object_version_number
3203 ,p_context_name1 => 'TAX_UNIT_ID'
3204 ,p_context1 => l_taxunitid
3205 ,p_some_warning => l_some_warning
3206 );
3207 end loop;
3208
3209 exception
3210 when others then
3211 hr_utility.trace('Problem in inserting employee data');
3212 end;
3213
3214 end if;
3215 exception
3216 when others then
3217 hr_utility.trace('Problem in archive_data');
3218
3219
3220 end archive_data;
3221
3222 BEGIN
3223
3224 g_proc_name := 'PAY_US_PSD_XML.';
3225 g_debug := hr_utility.debug_enabled;
3226 g_document_type := 'LOCAL_PSD_XML';
3227
3228 END pay_us_psd_xml;