VIEW: APPS.PAY_W2_MAGNETIC_TAPE_REPORTS

Source

View Text - Preformatted

SELECT PA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID , PA.EFFECTIVE_DATE EFFECTIVE_DATE , PA.ACTION_STATUS ACTION_STATUS , DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS, 11,5)), 'W2' , 'W2', 'SQWL', 'State Quarterly Wage Listing', '1099R', decode(ltrim(substr(PA.LEGISLATIVE_PARAMETERS,17,5)), 'FED', 'Federal 1099R Magnetic Report', 'State 1099R Magnetic Report'), 'MWSMR','Multiple Worksite Magnetic Report', 'Invalid Report     Type') REPORT_TYPE , DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS, 17,5)), 'FED', 'Federal', SR.NAME) STATE , DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS, 11,5)), 'W2' , TO_CHAR(PA.EFFECTIVE_DATE,'YYYY'), 'SQWL', TO_CHAR(PA.EFFECTIVE_DATE,'MMYY'), '1099R', to_char(PA.EFFECTIVE_DATE,'YYYY'), 'MWSMR', to_char(PA.effective_date,'MMYY'), 'Invalid Report Type') PERIOD FROM PAY_PAYROLL_ACTIONS PA, PAY_STATE_RULES SR WHERE /* ONLY SEARCH FOR MAGNETIC REPORT ACTIONS. */ PA.ACTION_TYPE = 'X' /* ONLY SEARCH FOR PAYROLL ACTIONS USED FOR US MAGNETIC TAPE REPORTING. */ AND SUBSTR(PA.LEGISLATIVE_PARAMETERS,1,9) = 'USMAGTAPE' /* THE STATE IS NOT FEDERAL SO JOIN TO THE STATE TABLE AND GET THE STATE NAME. */ AND ((LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS,17,5)) != 'FED' AND LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS,17,5)) = SR.STATE_CODE) OR /* THE STATE IS FEDERAL SO JOIN TO STATE TABLE USING 'AL' IE. AN ARBITRARY STATE. THIS VALUE IS FILTERED OUT FROM THE SELECT LIST BY A DECODE WHERE THE STRING 'Federal' IS SHOWN INSTEAD. */ (LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS,17,5)) = 'FED' AND 'AL' = SR.STATE_CODE))

View Text - HTML Formatted

SELECT PA.PAYROLL_ACTION_ID PAYROLL_ACTION_ID
, PA.EFFECTIVE_DATE EFFECTIVE_DATE
, PA.ACTION_STATUS ACTION_STATUS
, DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 11
, 5))
, 'W2'
, 'W2'
, 'SQWL'
, 'STATE QUARTERLY WAGE LISTING'
, '1099R'
, DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 17
, 5))
, 'FED'
, 'FEDERAL 1099R MAGNETIC REPORT'
, 'STATE 1099R MAGNETIC REPORT')
, 'MWSMR'
, 'MULTIPLE WORKSITE MAGNETIC REPORT'
, 'INVALID REPORT TYPE') REPORT_TYPE
, DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 17
, 5))
, 'FED'
, 'FEDERAL'
, SR.NAME) STATE
, DECODE(LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 11
, 5))
, 'W2'
, TO_CHAR(PA.EFFECTIVE_DATE
, 'YYYY')
, 'SQWL'
, TO_CHAR(PA.EFFECTIVE_DATE
, 'MMYY')
, '1099R'
, TO_CHAR(PA.EFFECTIVE_DATE
, 'YYYY')
, 'MWSMR'
, TO_CHAR(PA.EFFECTIVE_DATE
, 'MMYY')
, 'INVALID REPORT TYPE') PERIOD
FROM PAY_PAYROLL_ACTIONS PA
, PAY_STATE_RULES SR
WHERE /* ONLY SEARCH FOR MAGNETIC REPORT ACTIONS. */ PA.ACTION_TYPE = 'X' /* ONLY SEARCH FOR PAYROLL ACTIONS USED FOR US MAGNETIC TAPE REPORTING. */
AND SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 1
, 9) = 'USMAGTAPE' /* THE STATE IS NOT FEDERAL SO JOIN TO THE STATE TABLE
AND GET THE STATE NAME. */
AND ((LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 17
, 5)) != 'FED'
AND LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 17
, 5)) = SR.STATE_CODE) OR /* THE STATE IS FEDERAL SO JOIN TO STATE TABLE USING 'AL' IE. AN ARBITRARY STATE. THIS VALUE IS FILTERED OUT
FROM THE SELECT LIST BY A DECODE
WHERE THE STRING 'FEDERAL' IS SHOWN INSTEAD. */ (LTRIM(SUBSTR(PA.LEGISLATIVE_PARAMETERS
, 17
, 5)) = 'FED'
AND 'AL' = SR.STATE_CODE))