View: BEN_PL_EXTRACT_IDENTIFIER_D

Product:	BEN - Advanced Benefits
Description:	This view displays records from BEN_PL_EXTRACT_IDENTIFIER_F with description of ID fields.
Implementation/DBA Data:	APPS.BEN_PL_EXTRACT_IDENTIFIER_D

View Text

SELECT PEI.PL_EXTRACT_IDENTIFIER_ID
, PEI.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_END_DATE
, PLN3.NAME
, DECODE (PEI.PLIP_ID
, NULL
, PLN.NAME
, PGM.NAME || ' - ' || PLN.NAME)
, DECODE (PEI.OIPL_ID
, NULL
, NULL
, PLN2.NAME || ' -' || OPT.NAME)
, PEI.THIRD_PARTY_IDENTIFIER
, HR_GENERAL.DECODE_ORGANIZATION (PEI.ORGANIZATION_ID)
, JBT.NAME
, HR_GENERAL.DECODE_POSITION_LATEST_NAME (PEI.POSITION_ID) POSITION
, GRP.CONCATENATED_SEGMENTS
, GRT.NAME GRADE_NAME
, PAYROLL.PAYROLL_NAME
, FND.MEANING
, PEI.HOME_ZIP
, PEI.LAST_UPDATE_DATE
, FUSER.USER_NAME
FROM BEN_PL_EXTRACT_IDENTIFIER_F PEI
, PER_GRADES_TL GRT
, PER_JOBS_TL JBT
, PAY_PEOPLE_GROUPS_KFV GRP
, PAY_ALL_PAYROLLS_F PAYROLL
, HR_LOOKUPS FND
, BEN_PL_F PLN
, BEN_PGM_F PGM
, BEN_PLIP_F PLIP
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PLN2
, BEN_PL_F PLN3
, FND_USER FUSER
WHERE GRT.GRADE_ID (+)= PEI.GRADE_ID
AND GRT.LANGUAGE (+)= USERENV('LANG')
AND JBT.JOB_ID (+)= PEI.JOB_ID
AND JBT.LANGUAGE (+)= USERENV('LANG')
AND GRP.PEOPLE_GROUP_ID (+)= PEI.PEOPLE_GROUP_ID
AND PAYROLL.PAYROLL_ID (+)= PEI.PAYROLL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL (PAYROLL.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL (PAYROLL.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND FND.LOOKUP_CODE (+)= PEI.HOME_STATE
AND FND.LOOKUP_TYPE (+)= 'US_STATE'
AND PLN3.PL_ID (+)= PEI.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL(PLN3.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL(PLN3.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PLIP.PLIP_ID (+)= PEI.PLIP_ID
AND PLN.PL_ID (+)= PLIP.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL(PLN.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL(PLN.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PGM.PGM_ID (+)= PLIP.PGM_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL(PGM.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL(PGM.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND OIPL.OIPL_ID (+)= PEI.OIPL_ID
AND OPT.OPT_ID (+)= OIPL.OPT_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL(OPT.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL(OPT.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND PLN2.PL_ID (+)= OIPL.PL_ID
AND PEI.EFFECTIVE_START_DATE BETWEEN NVL(PLN2.EFFECTIVE_START_DATE
, PEI.EFFECTIVE_START_DATE)
AND NVL(PLN2.EFFECTIVE_END_DATE
, PEI.EFFECTIVE_START_DATE)
AND FUSER.USER_ID (+)= PEI.LAST_UPDATED_BY

Columns