View: PAY_GB_TAX_CREDITS_V

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

View Text

SELECT DISTINCT ENT.ELEMENT_ENTRY_ID
, LNK.ELEMENT_LINK_ID
, IPV1.INPUT_VALUE_ID DAILY_AMOUNT_IPV_ID
, EEV1.SCREEN_ENTRY_VALUE DAILY_AMOUNT
, IPV2.INPUT_VALUE_ID REFERENCE_IPV_ID
, EEV2.SCREEN_ENTRY_VALUE REFERENCE
, IPV3.INPUT_VALUE_ID START_DATE_IPV_ID
, EEV3.SCREEN_ENTRY_VALUE START_DATE
, SUBSTR(CL1.MEANING
, INSTR(CL1.MEANING
, '(')+1
, (INSTR(CL1.MEANING
, ')')-INSTR(CL1.MEANING
, '('))-1) IPV_DATE_FORMAT
, IPV4.INPUT_VALUE_ID END_DATE_IPV_ID
, EEV4.SCREEN_ENTRY_VALUE END_DATE
, IPV5.INPUT_VALUE_ID TOTAL_AMOUNT_IPV_ID
, EEV5.SCREEN_ENTRY_VALUE TOTAL_AMOUNT
, IPV6.INPUT_VALUE_ID STOP_DATE_IPV_ID
, EEV6.SCREEN_ENTRY_VALUE STOP_DATE
, ENT.ASSIGNMENT_ID
, ENT.EFFECTIVE_START_DATE
, ENT.EFFECTIVE_END_DATE
, ENT.OBJECT_VERSION_NUMBER
FROM PAY_ELEMENT_ENTRY_VALUES_F EEV1
, PAY_ELEMENT_ENTRY_VALUES_F EEV2
, PAY_ELEMENT_ENTRY_VALUES_F EEV3
, PAY_ELEMENT_ENTRY_VALUES_F EEV4
, PAY_ELEMENT_ENTRY_VALUES_F EEV5
, PAY_ELEMENT_ENTRY_VALUES_F EEV6
, HR_LOOKUPS CL1
, PAY_ELEMENT_ENTRIES_F ENT
, PAY_ELEMENT_LINKS_F LNK
, PAY_INPUT_VALUES_F IPV1
, PAY_INPUT_VALUES_F IPV2
, PAY_INPUT_VALUES_F IPV3
, PAY_INPUT_VALUES_F IPV4
, PAY_INPUT_VALUES_F IPV5
, PAY_INPUT_VALUES_F IPV6
, PAY_ELEMENT_TYPES_F ELE
, FND_SESSIONS SES
WHERE ELE.ELEMENT_NAME='TAX CREDIT'
AND CL1.LOOKUP_CODE = IPV3.UOM
AND CL1.LOOKUP_TYPE = 'UNITS'
AND SES.SESSION_ID=USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN ENT.EFFECTIVE_START_DATE
AND ENT.EFFECTIVE_END_DATE
AND LNK.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND ENT.ELEMENT_LINK_ID=LNK.ELEMENT_LINK_ID
AND ENT.ELEMENT_ENTRY_ID=EEV1.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV2.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV3.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV4.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV5.ELEMENT_ENTRY_ID
AND ENT.ELEMENT_ENTRY_ID=EEV6.ELEMENT_ENTRY_ID
AND EEV1.INPUT_VALUE_ID=IPV1.INPUT_VALUE_ID
AND IPV1.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV1.NAME='DAILY AMOUNT'
AND EEV2.INPUT_VALUE_ID=IPV2.INPUT_VALUE_ID
AND IPV2.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV2.NAME='REFERENCE'
AND EEV3.INPUT_VALUE_ID=IPV3.INPUT_VALUE_ID
AND IPV3.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV3.NAME='START DATE'
AND EEV4.INPUT_VALUE_ID=IPV4.INPUT_VALUE_ID
AND IPV4.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV4.NAME='END DATE'
AND EEV5.INPUT_VALUE_ID=IPV5.INPUT_VALUE_ID
AND IPV5.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV5.NAME='TOTAL AMOUNT'
AND EEV6.INPUT_VALUE_ID=IPV6.INPUT_VALUE_ID
AND IPV6.ELEMENT_TYPE_ID=ELE.ELEMENT_TYPE_ID
AND IPV6.NAME='STOP DATE'
AND SES.EFFECTIVE_DATE BETWEEN EEV1.EFFECTIVE_START_DATE
AND EEV1.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV2.EFFECTIVE_START_DATE
AND EEV2.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV3.EFFECTIVE_START_DATE
AND EEV3.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV4.EFFECTIVE_START_DATE
AND EEV4.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV5.EFFECTIVE_START_DATE
AND EEV5.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN EEV6.EFFECTIVE_START_DATE
AND EEV6.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV1.EFFECTIVE_START_DATE
AND IPV1.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV2.EFFECTIVE_START_DATE
AND IPV2.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV3.EFFECTIVE_START_DATE
AND IPV3.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV4.EFFECTIVE_START_DATE
AND IPV4.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV5.EFFECTIVE_START_DATE
AND IPV5.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IPV6.EFFECTIVE_START_DATE
AND IPV6.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN LNK.EFFECTIVE_START_DATE
AND LNK.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN ELE.EFFECTIVE_START_DATE
AND ELE.EFFECTIVE_END_DATE
AND NOT EXISTS( SELECT 'Y'
FROM PAY_ELEMENT_ENTRIES_F ENT2
WHERE ENT2.ELEMENT_ENTRY_ID=ENT.ELEMENT_ENTRY_ID
AND ENT2.EFFECTIVE_START_DATE=(ENT.EFFECTIVE_END_DATE+1) )

Columns