View: PER_CAGR_ENTITLEMENTS_V2

Product:	PER - Human Resources
Description:
Implementation/DBA Data:	APPS.PER_CAGR_ENTITLEMENTS_V2

View Text

SELECT ENT.ASSIGNMENT_ID
, ENT.CAGR_ENTITLEMENT_ITEM_ID
, ENT.ITEM_NAME
, ENT.CATEGORY_NAME
, SUBSTR(HR_GENERAL.DECODE_LOOKUP('CAGR_CATEGORIES'
, ENT.CATEGORY_NAME)
, 1
, 80)
FROM ((SELECT EN.CAGR_ENTITLEMENT_ITEM_ID
, EI.ITEM_NAME
, ASG.ASSIGNMENT_ID
, EI.CATEGORY_NAME
FROM PER_CAGR_ENTITLEMENTS EN
, PER_COLLECTIVE_AGREEMENTS CA
, PER_ALL_ASSIGNMENTS_F ASG
, PER_CAGR_ENTITLEMENT_ITEMS EI
, FND_SESSIONS SES
WHERE EN.STATUS = 'A'
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN EN.START_DATE
AND NVL(EN.END_DATE
, HR_GENERAL.END_OF_TIME)
AND EN.COLLECTIVE_AGREEMENT_ID = CA.COLLECTIVE_AGREEMENT_ID
AND SES.EFFECTIVE_DATE >= CA.START_DATE
AND CA.STATUS = 'A'
AND EN.COLLECTIVE_AGREEMENT_ID = ASG.COLLECTIVE_AGREEMENT_ID
AND SES.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND EN.CAGR_ENTITLEMENT_ITEM_ID = EI.CAGR_ENTITLEMENT_ITEM_ID) UNION (SELECT RR.CAGR_ENTITLEMENT_ITEM_ID
, ITEMS.ITEM_NAME
, RR.ASSIGNMENT_ID
, ITEMS.CATEGORY_NAME
FROM PER_CAGR_RETAINED_RIGHTS RR
, PER_CAGR_ENTITLEMENTS ENTS
, PER_COLLECTIVE_AGREEMENTS CAGR
, PER_CAGR_ENTITLEMENT_ITEMS ITEMS
, FND_SESSIONS SESS
WHERE SESS.SESSION_ID = USERENV('SESSIONID')
AND SESS.EFFECTIVE_DATE BETWEEN RR.START_DATE
AND NVL(RR.END_DATE
, HR_GENERAL.END_OF_TIME)
AND RR.COLLECTIVE_AGREEMENT_ID = CAGR.COLLECTIVE_AGREEMENT_ID
AND SESS.EFFECTIVE_DATE >= CAGR.START_DATE
AND CAGR.STATUS = 'A'
AND RR.CAGR_ENTITLEMENT_ID = ENTS.CAGR_ENTITLEMENT_ID
AND ENTS.STATUS = 'A'
AND SESS.EFFECTIVE_DATE BETWEEN ENTS.START_DATE
AND NVL(ENTS.END_DATE
, HR_GENERAL.END_OF_TIME)
AND RR.CAGR_ENTITLEMENT_ITEM_ID = ITEMS.CAGR_ENTITLEMENT_ITEM_ID)) ENT ORDER BY ITEM_NAME

Columns