View: PER_COBRA_COV_ENROLLMENTS_V

Product:	PER - Human Resources
Description:	Used to support user interface
Implementation/DBA Data:	APPS.PER_COBRA_COV_ENROLLMENTS_V

View Text

SELECT CCE.ROWID ROW_ID
, CCE.COBRA_COVERAGE_ENROLLMENT_ID COBRA_COVERAGE_ENROLLMENT_ID
, CCE.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, CCE.ASSIGNMENT_ID ASSIGNMENT_ID
, CCE.CONTACT_RELATIONSHIP_ID CONTACT_RELATIONSHIP_ID
, CCE.PERIOD_TYPE PERIOD_TYPE
, CCE.QUALIFYING_EVENT QUALIFYING_EVENT
, CCE.QUALIFYING_DATE QUALIFYING_DATE
, L4.MEANING QUALIFYING_EVENT_MEANING
, AP.FULL_NAME DEPENDENT_NAME
, L1.MEANING DEPENDENT_RELATIONSHIP
, CCE.COVERAGE_END_DATE COVERAGE_END_DATE
, CCE.COVERAGE_START_DATE COVERAGE_START_DATE
, CQE.EVENT_COVERAGE EVENT_COVERAGE
, CQE.ELECTOR ELECTOR
, CCE.TERMINATION_REASON TERMINATION_REASON
, L3.MEANING TERMINATION_REASON_MEANING
, CCS.COBRA_COVERAGE_STATUS_TYPE STATUS
, CCS.EFFECTIVE_DATE STATUS_DATE
, L2.MEANING STATUS_MEANING
, SCP.DATE_DUE FIRST_PAYMENT_DUE_DATE
, CCE.GRACE_DAYS GRACE_DAYS
, CCE.COMMENTS COMMENTS
, CCE.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CCE.ATTRIBUTE1 ATTRIBUTE1
, CCE.ATTRIBUTE2 ATTRIBUTE2
, CCE.ATTRIBUTE3 ATTRIBUTE3
, CCE.ATTRIBUTE4 ATTRIBUTE4
, CCE.ATTRIBUTE5 ATTRIBUTE5
, CCE.ATTRIBUTE6 ATTRIBUTE6
, CCE.ATTRIBUTE7 ATTRIBUTE7
, CCE.ATTRIBUTE8 ATTRIBUTE8
, CCE.ATTRIBUTE9 ATTRIBUTE9
, CCE.ATTRIBUTE10 ATTRIBUTE10
, CCE.ATTRIBUTE11 ATTRIBUTE11
, CCE.ATTRIBUTE12 ATTRIBUTE12
, CCE.ATTRIBUTE13 ATTRIBUTE13
, CCE.ATTRIBUTE14 ATTRIBUTE14
, CCE.ATTRIBUTE15 ATTRIBUTE15
, CCE.ATTRIBUTE16 ATTRIBUTE16
, CCE.ATTRIBUTE17 ATTRIBUTE17
, CCE.ATTRIBUTE18 ATTRIBUTE18
, CCE.ATTRIBUTE19 ATTRIBUTE19
, CCE.ATTRIBUTE20 ATTRIBUTE20
, CCE.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CCE.LAST_UPDATED_BY LAST_UPDATED_BY
, CCE.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CCE.CREATED_BY CREATED_BY
, CCE.CREATION_DATE CREATION_DATE
FROM HR_LOOKUPS L1
, HR_LOOKUPS L2
, HR_LOOKUPS L3
, HR_LOOKUPS L4
, PER_SCHED_COBRA_PAYMENTS SCP
, PER_COBRA_COVERAGE_STATUSES CCS
, PER_ALL_PEOPLE_F AP
, PER_CONTACT_RELATIONSHIPS CR
, PER_COBRA_QFYING_EVENTS_F CQE
, PER_COBRA_COV_ENROLLMENTS CCE
, FND_SESSIONS S
WHERE L4.LOOKUP_TYPE = 'US_COBRA_EVENT'
AND L4.LOOKUP_CODE = CCE.QUALIFYING_EVENT
AND L3.LOOKUP_TYPE(+) = 'US_COBRA_TERM_REASON'
AND L3.LOOKUP_CODE(+) = CCE.TERMINATION_REASON
AND L2.LOOKUP_CODE = CCS.COBRA_COVERAGE_STATUS_TYPE
AND L2.LOOKUP_TYPE = 'US_COBRA_STATUS'
AND L1.LOOKUP_TYPE(+) = 'CONTACT'
AND L1.LOOKUP_CODE(+) = CR.CONTACT_TYPE
AND SCP.COBRA_COVERAGE_ENROLLMENT_ID(+) = CCE.COBRA_COVERAGE_ENROLLMENT_ID
AND ( SCP.DATE_DUE = ( SELECT MIN(SCP1.DATE_DUE)
FROM PER_SCHED_COBRA_PAYMENTS SCP1
WHERE SCP1.COBRA_COVERAGE_ENROLLMENT_ID = SCP.COBRA_COVERAGE_ENROLLMENT_ID ) OR SCP.DATE_DUE IS NULL )
AND CCS.COBRA_COVERAGE_ENROLLMENT_ID = CCE.COBRA_COVERAGE_ENROLLMENT_ID
AND CCS.COBRA_COVERAGE_STATUS_ID = ( SELECT MAX(CCS1.COBRA_COVERAGE_STATUS_ID)
FROM PER_COBRA_COVERAGE_STATUSES CCS1
WHERE CCS1.COBRA_COVERAGE_ENROLLMENT_ID = CCE.COBRA_COVERAGE_ENROLLMENT_ID
AND CCS1.EFFECTIVE_DATE = ( SELECT MAX(CCS2.EFFECTIVE_DATE)
FROM PER_COBRA_COVERAGE_STATUSES CCS2
WHERE CCS2.EFFECTIVE_DATE <= S.EFFECTIVE_DATE
AND CCS2.COBRA_COVERAGE_ENROLLMENT_ID = CCE.COBRA_COVERAGE_ENROLLMENT_ID ) )
AND AP.PERSON_ID(+) = CR.CONTACT_PERSON_ID
AND CR.CONTACT_RELATIONSHIP_ID(+) = CCE.CONTACT_RELATIONSHIP_ID
AND CQE.QUALIFYING_EVENT = CCE.QUALIFYING_EVENT
AND CQE.LEGISLATION_CODE = 'US'
AND CQE.BUSINESS_GROUP_ID IS NULL
AND CCE.QUALIFYING_DATE BETWEEN CQE.EFFECTIVE_START_DATE
AND CQE.EFFECTIVE_END_DATE
AND S.SESSION_ID = USERENV('SESSIONID')

Columns