SQWL Process in Oracle Payroll

State Quarterly Wage Listing

The new concurrent program 'State Quarterly Wage Listing (Enhanced)' is delivered to provide multiple options for producing output when filing quarterly wage reports to the state (or multiple states). The current SQWL report produces one output file containing all GRE's or FEIN's. This works fine for states that accept the data on magnetic media such as CD or DVD. However many states provide or mandate electronic or internet-only filing and many only allow or support one GRE to be filed at a time. In the past, if you had more than one GRE operating in one or more of these states, you had to manually manipulate the output file in order to be able to submit the report to the state. The enhanced SQWL report will provide the following options which will allow you to meet the various filing methods required by the different states:

• All GRE's - output file per GRE: Choose this option if you want to run the SQWL process for all GREs at one time and produce a separate output file per GRE. The process will internally derive all eligible GRE's and will spawn child (or individual concurrent) SQWL processes to produce each GRE-specific output file.

• Single GRE: Choose this option if you want to run SQWL process for a specific GRE.

• All GRE's - single output file: Choose this option if you want to run the SQWL process in the existing format which produces one single output file with the data for all GRE's without any GRE level breakup.

• The following parameters are mandatory:

• State

• Quarter

• Report Output

• Government Reporting Entity

• Format

• Transmitter GRE

• The following parameters are not mandatory:

• Create Audit Report

• Establishment Hierarchy

• Hierarchy Version

The new concurrent program has the parameter 'Report Output' to facilitate the above options.

You enter the following parameters before submitting the new SQWL process:

The 'Government Reporting Entity' parameter will be enabled when the user selects the Report Output as 'Single GRE'; otherwise it will be in disabled mode.

The 'Format' parameter LOV will not display any value if you attempt to re-run the same SQWL process without running the Rollback process for the previous run. You must roll back the earlier process if you want to run the SQWL process again for the same State, Quarter, GRE and Format.

To be able to run the SQWL process for a GRE, the following criteria must be met:

• The SUI Account Number in Work Structures > GRE > Others >State Tax Rules contains a value.
• The GRE is not defined as a 1099-R GRE.
• The GRE must have valid payroll runs during that quarter.

Note: If you use a custom request set, do not use the old SQWL process (State Quarterly Wage Listing) as it will create inconsistencies in the system which will result in data corruption. Going forward, customers must use this new concurrent program for SQWL reporting. The existing SQWL process will be used internally to submit the SQWL process for each GRE by the main concurrent program and will no longer be available to customers in the SRS.

A new concurrent program, 'Rollback US SQWL Process' has been created to roll back the earlier SQWL concurrent processes. The new SQWL Rollback process rolls back all the GRE-specific SQWL child processes (one spawned for each GRE) when you choose the parent SQWL process id to roll back. It can also be used for rolling back individual child processes by providing a single child process id. When you select the child SQWL process to roll back, and it is the only child process remaining from the original run, then the parent SQWL process will also be rolled back.

The report type for the new concurrent program is 'SQWL_PARENT' and you can identify the Parent SQWL process with this report type in the View >Payroll Process Results screen.

Note: You cannot delete the parent SQWL process as long as there are child SQWL processes present. The delete button is enabled for the parent process only when there are no child processes present. The delete button is always enabled for the child processes.

pay_us_sqwl_archive.range_cursor

--SQWL Balance Value

SELECT   (SELECT REPLACE (fai.VALUE, '-')

            FROM apps.ff_archive_items fai, apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_PER_NATIONAL_IDENTIFIER') ssn,

         (SELECT NAME

            FROM hr_tax_units_v

           WHERE tax_unit_id = paa.tax_unit_id) gre,

         (   (SELECT fai.VALUE

                FROM apps.ff_archive_items fai, apps.ff_database_items fdi

               WHERE fai.context1 = paa.assignment_action_id

                 AND fdi.user_entity_id = fai.user_entity_id

                 AND fdi.user_name = 'A_PER_LAST_NAME')

          || ', '

          || (SELECT fai.VALUE

                FROM apps.ff_archive_items fai, apps.ff_database_items fdi

               WHERE fai.context1 = paa.assignment_action_id

                 AND fdi.user_entity_id = fai.user_entity_id

                 AND fdi.user_name = 'A_PER_FIRST_NAME')

          || ' '

          || (SELECT fai.VALUE

                FROM apps.ff_archive_items fai, apps.ff_database_items fdi

               WHERE fai.context1 = paa.assignment_action_id

                 AND fdi.user_entity_id = fai.user_entity_id

                 AND fdi.user_name = 'A_PER_MIDDLE_NAMES')

         ) employee_name,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SUI_ER_GROSS_PER_JD_GRE_QTD')

                                                           sui_er_gross_wages,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SUI_ER_SUBJ_WHABLE_PER_JD_GRE_QTD')

                                                  sui_er_subject_withholdable,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SIT_WITHHELD_PER_JD_GRE_QTD')

                                                                 sit_withheld,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SIT_PRE_TAX_REDNS_PER_JD_GRE_QTD')

                                                              sit_pretax_redn,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SIT_SUBJ_NWHABLE_PER_JD_GRE_QTD')

                                                             sit_subj_nwhable,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SIT_SUBJ_WHABLE_PER_JD_GRE_QTD')

                                                              sit_subj_whable,

         (SELECT fai.VALUE

            FROM apps.ff_archive_items fai,

                 apps.ff_database_items fdi

           WHERE fai.context1 = paa.assignment_action_id

             AND fdi.user_entity_id = fai.user_entity_id

             AND fdi.user_name = 'A_SIT_GROSS_PER_JD_GRE_QTD') sit_gross

    -- ,PPA.PAYROLL_ACTION_ID

FROM     pay_payroll_actions ppa, pay_assignment_actions paa

   WHERE ppa.action_type = 'X'

     AND ppa.report_type = 'SQWL'

     AND ppa.effective_date = '31-MAR-2015'

     AND ppa.report_qualifier = 'NY' --ME State

     AND paa.payroll_action_id = ppa.payroll_action_id

ORDER BY 1;

-------------------------------------------------------------------------------

select * from pay_us_srs_retry_rollback_v psrv

    WHERE psrv.process_type = 'X'

          AND psrv.effective_date 

          between trunc(to_date('2015', 'YYYY'),'Y') and add_months(trunc(to_date('2015', 'YYYY'),'Y'),12)-1

          AND psrv.business_group_id + 0= :P_BG_ID

          AND (    psrv.report_type in ('SQWL','SQWL_PARENT'))              

          AND NOT EXISTS

          (select null

          from pay_assignment_actions paa,

          pay_action_interlocks int

          where psrv.payroll_action_id

          = paa.payroll_action_id

          and paa.assignment_action_id

          = int.locked_action_id)

          and psrv.payroll_action_id not in 

                   (select 1 --nvl(pay_core_utils.get_parameter('TRANSFER_PARENT_PAYROLL_ACTION',ppa1.legislative_parameters), -1)

                           from pay_payroll_actions ppa1,

                              pay_assignment_actions paa1,

                              pay_action_interlocks int1

                              where ppa1.payroll_action_id = paa1.payroll_action_id

                                  and paa1.assignment_action_id = int1.locked_action_id                                        

                                      and ppa1.effective_date between trunc(to_date('2015', 'YYYY'),'Y') 

                                                and add_months(trunc(to_date('2015', 'YYYY'),'Y'),12)-1

                                      and ppa1.report_type IN ('SQWL','SMWL') )

          ORDER BY psrv.effective_date desc,

            psrv.payroll_action_id;