View: PAY_US_EMP_TIME_IN_COUNTY_V

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

View Text

SELECT PEF.ASSIGNMENT_ID
, CTR.STATE_CODE
, CTR.COUNTY_CODE
, PUC.COUNTY_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_COUNTY_TAX_RULES_F CTR
, PAY_US_COUNTIES 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 (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 SUBSTR(CTR.JURISDICTION_CODE
, 1
, 7) = SUBSTR(PEV1.SCREEN_ENTRY_VALUE
, 1
, 7)
AND PEV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND PIV1.NAME = 'JURISDICTION')
AND (PEV1.ELEMENT_ENTRY_ID = PEV2.ELEMENT_ENTRY_ID
AND PEV1.EFFECTIVE_START_DATE = PEV2.EFFECTIVE_START_DATE
AND PEV1.EFFECTIVE_END_DATE = PEV2.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
, PUC.COUNTY_NAME
, CTR.JURISDICTION_CODE
, PEF.EFFECTIVE_START_DATE
, PEF.EFFECTIVE_END_DATE

Columns