View: PAY_W2_MAGNETIC_TAPE_REPORTS

Product:	PAY - Payroll
Description:	- Retrofitted
Implementation/DBA Data:	APPS.PAY_W2_MAGNETIC_TAPE_REPORTS

View Text

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))

Columns