VIEW: APPS.PER_COBRA_COV_ENROLLMENTS_V

Source

View Text - Preformatted

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 , CCB.COBRA_COVERAGE_BENEFIT_ID , CCB.EFFECTIVE_START_DATE , CCB.EFFECTIVE_END_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, PER_COBRA_COVERAGE_BENEFITS_F CCB, 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') AND CCB.COBRA_COVERAGE_BENEFIT_ID = (select max(ccb2.cobra_coverage_benefit_id) from per_cobra_coverage_benefits_f ccb2 where ccb2.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id and s.effective_date between ccb2.effective_start_date and ccb2.effective_end_date )

View Text - HTML Formatted

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
, CCB.COBRA_COVERAGE_BENEFIT_ID
, CCB.EFFECTIVE_START_DATE
, CCB.EFFECTIVE_END_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
, PER_COBRA_COVERAGE_BENEFITS_F CCB
, 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')
AND CCB.COBRA_COVERAGE_BENEFIT_ID = (SELECT MAX(CCB2.COBRA_COVERAGE_BENEFIT_ID)
FROM PER_COBRA_COVERAGE_BENEFITS_F CCB2
WHERE CCB2.COBRA_COVERAGE_ENROLLMENT_ID = CCE.COBRA_COVERAGE_ENROLLMENT_ID
AND S.EFFECTIVE_DATE BETWEEN CCB2.EFFECTIVE_START_DATE
AND CCB2.EFFECTIVE_END_DATE )