View: PAY_US_EMP_TIME_IN_CITY_V

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

View Text

SELECT PEF.ASSIGNMENT_ID
, CTR.STATE_CODE
, CTR.COUNTY_CODE
, CTR.CITY_CODE
, PUC.CITY_NAME
, CTR.JURISDICTION_CODE
, SUM(FND_NUMBER.CANONICAL_TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE
FROM PAY_ELEMENT_ENTRIES_F PEF
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
, PAY_US_EMP_CITY_TAX_RULES_F CTR
, PAY_US_CITY_NAMES PUC
, 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 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 PEF.ASSIGNMENT_ID = CTR.ASSIGNMENT_ID
AND CTR.EFFECTIVE_START_DATE <= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND CTR.EFFECTIVE_END_DATE >= (SELECT SS.EFFECTIVE_DATE
FROM FND_SESSIONS SS
WHERE SS.SESSION_ID = USERENV('SESSIONID'))
AND CTR.STATE_CODE = PUC.STATE_CODE
AND CTR.COUNTY_CODE = PUC.COUNTY_CODE
AND CTR.CITY_CODE = PUC.CITY_CODE
AND ( PUC.PRIMARY_FLAG = 'Y' OR SUBSTR( PUC.CITY_CODE
, 1
, 1) = 'U')
AND (PEV1.ELEMENT_ENTRY_ID = PEF.ELEMENT_ENTRY_ID
AND PEV1.EFFECTIVE_START_DATE = PEF.EFFECTIVE_START_DATE
AND PEV1.EFFECTIVE_END_DATE = PEF.EFFECTIVE_END_DATE
AND PEV1.SCREEN_ENTRY_VALUE IS NOT NULL
AND CTR.JURISDICTION_CODE = SUBSTR(PEV1.SCREEN_ENTRY_VALUE
, 1
, 11)
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') GROUP BY PEF.ASSIGNMENT_ID
, CTR.STATE_CODE
, CTR.COUNTY_CODE
, CTR.CITY_CODE
, PUC.CITY_NAME
, CTR.JURISDICTION_CODE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE

Columns