View: PAY_US_EMP_TIME_IN_STATE_V

Product:	PAY - Payroll
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PAY_US_EMP_TIME_IN_STATE_V

View Text

SELECT PEF.ASSIGNMENT_ID
, PEST.STATE_CODE
, PUS.STATE_NAME
, PEST.JURISDICTION_CODE
, PEST.FILING_STATUS_CODE
, FCL.MEANING
, PEST.WITHHOLDING_ALLOWANCES
, PEST.SIT_ADDITIONAL_TAX
, PEST.SIT_OVERRIDE_AMOUNT
, PEST.SIT_OVERRIDE_RATE
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)) TIME_IN_STATE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F PEF
, PAY_US_EMP_STATE_TAX_RULES_F PEST
, FND_COMMON_LOOKUPS FCL
, PAY_US_STATES PUS
, PAY_STATE_RULES PSR
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_ENTRY_VALUES_F PEV1
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
WHERE PEF.CREATOR_TYPE = 'UT'
AND PEF.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PEL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PET.ELEMENT_NAME = 'VERTEX'
AND PEST.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEST.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND PEF.ASSIGNMENT_ID = PEST.ASSIGNMENT_ID
AND PEST.STATE_CODE = PUS.STATE_CODE
AND PSR.STATE_CODE = PUS.STATE_ABBREV
AND (PEF.ELEMENT_ENTRY_ID = PEV1.ELEMENT_ENTRY_ID
AND PEF.EFFECTIVE_START_DATE = PEV1.EFFECTIVE_START_DATE
AND PEF.EFFECTIVE_END_DATE = PEV1.EFFECTIVE_END_DATE
AND PEV1.SCREEN_ENTRY_VALUE IS NOT NULL
AND PEST.STATE_CODE = SUBSTR(PEV1.SCREEN_ENTRY_VALUE
, 1
, 2)
AND PEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'JURISDICTION')
AND (PEV2.ELEMENT_ENTRY_ID = PEV1.ELEMENT_ENTRY_ID
AND PEV2.EFFECTIVE_START_DATE = PEV1.EFFECTIVE_START_DATE
AND PEV2.EFFECTIVE_END_DATE = PEV1.EFFECTIVE_END_DATE
AND PEV2.SCREEN_ENTRY_VALUE IS NOT NULL
AND PIV2.INPUT_VALUE_ID = PEV2.INPUT_VALUE_ID
AND PIV2.NAME = 'PERCENTAGE')
AND FCL.APPLICATION_ID = 800
AND FCL.LOOKUP_TYPE = PSR.FS_LOOKUP_TYPE
AND LPAD(FCL.LOOKUP_CODE
, 2
, 0) = PEST.FILING_STATUS_CODE GROUP BY PEF.ASSIGNMENT_ID
, PEST.STATE_CODE
, PUS.STATE_NAME
, PEST.JURISDICTION_CODE
, PEST.FILING_STATUS_CODE
, FCL.MEANING
, PEST.WITHHOLDING_ALLOWANCES
, PEST.SIT_ADDITIONAL_TAX
, PEST.SIT_OVERRIDE_AMOUNT
, PEST.SIT_OVERRIDE_RATE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE

Columns