View: GHR_COMPLAINTS_V

View Text

SELECT COM.ROWID ROW_ID
, COM.COMPLAINT_ID COMPLAINT_ID
, COM.DISCRIM_ORG_ID DISCRIM_ORG_ID
, COM.COMPLAINT_TYPE_CODE COMPLAINT_TYPE_CODE
, COM.COUNSELOR_ID COUNSELOR_ID
, COM.REPRESENTATIVE_ID REPRESENTATIVE_ID
, COM.RMO_ID RMO_ID
, COM.INVESTIGATOR_ID INVESTIGATOR_ID
, COM.DISCRIM_ACTION_DATE DISCRIM_ACTION_DATE
, COM.EEO_OFFICE_CONTACT_DATE EEO_OFFICE_CONTACT_DATE
, COM.EEO_OFFICE_LETTER_DATE EEO_OFFICE_LETTER_DATE
, COM.COUNSELOR_CONTACT_DATE COUNSELOR_CONTACT_DATE
, COM.COUNSELOR_CONTACT_NLT_DATE COUNSELOR_CONTACT_NLT_DATE
, COM.FINAL_INTERVIEW_DATE FINAL_INTERVIEW_DATE
, COM.FINAL_INTERVIEW_NLT_DATE FINAL_INTERVIEW_NLT_DATE
, COM.FINAL_INTERVIEW_LETTER_DATE FINAL_INTERVIEW_LETTER_DATE
, COM.RIGHT_TO_FILE_NOTICE_DATE RIGHT_TO_FILE_NOTICE_DATE
, COM.RIGHT_TO_FILE_NLT_DATE RIGHT_TO_FILE_NLT_DATE
, COM.RIGHT_TO_FILE_LETTER_DATE RIGHT_TO_FILE_LETTER_DATE
, COM.COUNSELOR_REPORT_DATE COUNSELOR_REPORT_DATE
, COM.FREEZE_FLAG FREEZE_FLAG
, COM.REMANDED REMANDED
, COM.DRP_CLOSURE DRP_CLOSURE
, COM.CLOSED_DATE CLOSED_DATE
, COM.NATURE_OF_CLOSURE_CODE NATURE_OF_CLOSURE_CODE
, COM.CLOSURE_STAGE_CODE CLOSURE_STAGE_CODE
, COM.FORMAL_COM_FILE_DATE FORMAL_COM_FILE_DATE
, COM.FORMAL_COM_FILE_NLT_DATE FORMAL_COM_FILE_NLT_DATE
, COM.FORMAL_COM_RCV_DATE FORMAL_COM_RCV_DATE
, COM.FORMAL_COM_NLT_RCV_DATE FORMAL_COM_NLT_RCV_DATE
, COM.FORMAL_COM_RCV_ACK_DATE FORMAL_COM_RCV_ACK_DATE
, COM.FORMAL_COM_RCV_ACK_LTR_DATE FORMAL_COM_RCV_ACK_LTR_DATE
, COM.FORMAL_COM_ACCEPT_LTR_DATE FORMAL_COM_ACCEPT_LTR_DATE
, COM.FORMAL_COM_ACCEPT_NLT_DATE FORMAL_COM_ACCEPT_NLT_DATE
, COM.DCSN_W_HEARING_REQ_DATE DCSN_W_HEARING_REQ_DATE
, COM.DCSN_W_HEARING_REQ_NLT_DATE DCSN_W_HEARING_REQ_NLT_DATE
, COM.DCSN_WO_HEARING_REQ_DATE DCSN_WO_HEARING_REQ_DATE
, COM.DCSN_WO_HEARING_REQ_NLT_DATE DCSN_WO_HEARING_REQ_NLT_DATE
, COM.INVESTIGATOR_REQ_DATE INVESTIGATOR_REQ_DATE
, COM.INVESTIGATOR_ASSIGNED_DATE INVESTIGATOR_ASSIGNED_DATE
, COM.INVESTIGATION_START_DATE INVESTIGATION_START_DATE
, COM.INVESTIGATION_END_DATE INVESTIGATION_END_DATE
, COM.INVESTIGATION_END_NLT_DATE INVESTIGATION_END_NLT_DATE
, COM.ORG_RECOMM_DECISION_CODE ORG_RECOMM_DECISION_CODE
, COM.ORG_RECOMM_DECISION_ACTN_CODE ORG_RECOMM_DECISION_ACTN_CODE
, COM.AGENCY_RCV_ROI_DATE AGENCY_RCV_ROI_DATE
, COM.AGENCY_RCV_ROI_NLT_DATE AGENCY_RCV_ROI_NLT_DATE
, COM.COMPLAINANT_RCV_ROI_DATE COMPLAINANT_RCV_ROI_DATE
, COM.COMPLAINANT_RCV_ROI_NLT_DATE COMPLAINANT_RCV_ROI_NLT_DATE
, COM.INFORMAL_ADJUSTMENT_CODE INFORMAL_ADJUSTMENT_CODE
, COM.FINAL_INTERVIEW_NOTICE_DATE FINAL_INTERVIEW_NOTICE_DATE
, COM.PROPOSED_DISPOSITION_DATE PROPOSED_DISPOSITION_DATE
, COM.HIRE_RETRO_BACKPAY HIRE_RETRO_BACKPAY
, COM.HIRE_RETRO_BACKPAY_LSPAY_DATE HIRE_RETRO_BACKPAY_LSPAY_DATE
, COM.PROMOTION_RETRO_BACKPAY PROMOTION_RETRO_BACKPAY
, COM.RESCIND_DSCPLNRY_ACTN_BACKPAY RESCIND_DSCPLNRY_ACTN_BACKPAY
, COM.REINSTATEMENT_BACKPAY REINSTATEMENT_BACKPAY
, COM.OTHER_BACKPAY OTHER_BACKPAY
, COM.FRONT_PAY FRONT_PAY
, COM.FRONT_PAY_DATE FRONT_PAY_DATE
, COM.ATTORNEY_FEE ATTORNEY_FEE
, COM.ATTORNEY_FEE_PAY_DATE ATTORNEY_FEE_PAY_DATE
, COM.INVESTIGATIVE_TRANSCRIPT_COST INVESTIGATIVE_TRANSCRIPT_COST
, COM.JUDGE_REQ_DATE JUDGE_REQ_DATE
, COM.JUDGE_REPORT_RCV_DATE JUDGE_REPORT_RCV_DATE
, COM.JUDGE_TRANSCRIPT_COST JUDGE_TRANSCRIPT_COST
, COM.EEOC_HEARING_NUMBER EEOC_HEARING_NUMBER
, COM.RECOMMENDED_DECISION_CODE RECOMMENDED_DECISION_CODE
, COM.EXAMINERS_RECOMM_STATUS_CODE EXAMINERS_RECOMM_STATUS_CODE
, COM.INVESTIGATORS_RECOMMENDATION INVESTIGATORS_RECOMMENDATION
, COM.HEARING_DATE HEARING_DATE
, COM.HEARING_FINDING HEARING_FINDING
, COM.AGENCY_ACTION_CODE AGENCY_ACTION_CODE
, COM.FINAL_DCSN_DATE FINAL_DCSN_DATE
, COM.RIGHT_TO_APPEAL_NOTICE_DATE RIGHT_TO_APPEAL_NOTICE_DATE
, COM.RIGHT_TO_APPEAL_LETTER_DATE RIGHT_TO_APPEAL_LETTER_DATE
, COM.SPL_CORRESP_CODE SPL_CORRESP_CODE
, COM.SPL_CORRESP_RCV_DATE SPL_CORRESP_RCV_DATE
, COM.SPL_CORRESP_ANSWER_DATE SPL_CORRESP_ANSWER_DATE
, COM.APPEALS_CASE_NUMBER APPEALS_CASE_NUMBER
, COM.APPEAL_DATE APPEAL_DATE
, COM.EEOC_REQ_CASE_FILE_DATE EEOC_REQ_CASE_FILE_DATE
, COM.CASE_FILE_SENT_TO_EEOC_DATE CASE_FILE_SENT_TO_EEOC_DATE
, COM.APPEAL_DCSN_DATE APPEAL_DCSN_DATE
, COM.APPEALS_FINDING APPEALS_FINDING
, COM.INFORMAL_NARRATION INFORMAL_NARRATION
, COM.REOPENING_REQUESTED_BY REOPENING_REQUESTED_BY
, COM.REOPENING_REQUESTED_DATE REOPENING_REQUESTED_DATE
, COM.REOPENING_GRANTED_CODE REOPENING_GRANTED_CODE
, COM.REOPENING_DECISION_DATE REOPENING_DECISION_DATE
, COM.CIVIL_ACTN_CASE_NUMBER CIVIL_ACTN_CASE_NUMBER
, COM.CIVIL_ACTN_FILED_DATE CIVIL_ACTN_FILED_DATE
, COM.CIVIL_ACTN_COURT_DCSN CIVIL_ACTN_COURT_DCSN
, COM.CIVIL_ACTN_COURT_DCSN_DATE CIVIL_ACTN_COURT_DCSN_DATE
, COM.MODIFIED_DSCPLNRY_ACTN_BACKPAY MODIFIED_DSCPLNRY_ACTN_BACKPAY
, COM.NON_HIRE_RETRO_BACKPAY NON_HIRE_RETRO_BACKPAY
, COM.NON_PROMO_RETRO_BACKPAY NON_PROMO_RETRO_BACKPAY
, COM.PERFORMANCE_EVAL_MOD_BACKPAY PERFORMANCE_EVAL_MOD_BACKPAY
, COM.PERSNL_PUR_ADVRMAT_BACKPAY PERSNL_PUR_ADVRMAT_BACKPAY
, COM.REASSIGNMENT_BACKPAY REASSIGNMENT_BACKPAY
, COM.OBJECT_VERSION_NUMBER
, COM.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, COM.ATTRIBUTE1 ATTRIBUTE1
, COM.ATTRIBUTE2 ATTRIBUTE2
, COM.ATTRIBUTE3 ATTRIBUTE3
, COM.ATTRIBUTE4 ATTRIBUTE4
, COM.ATTRIBUTE5 ATTRIBUTE5
, COM.ATTRIBUTE6 ATTRIBUTE6
, COM.ATTRIBUTE7 ATTRIBUTE7
, COM.ATTRIBUTE8 ATTRIBUTE8
, COM.ATTRIBUTE9 ATTRIBUTE9
, COM.ATTRIBUTE10 ATTRIBUTE10
, COM.ATTRIBUTE11 ATTRIBUTE11
, COM.ATTRIBUTE12 ATTRIBUTE12
, COM.ATTRIBUTE13 ATTRIBUTE13
, COM.ATTRIBUTE14 ATTRIBUTE14
, COM.ATTRIBUTE15 ATTRIBUTE15
, COM.ATTRIBUTE16 ATTRIBUTE16
, COM.ATTRIBUTE17 ATTRIBUTE17
, COM.ATTRIBUTE18 ATTRIBUTE18
, COM.ATTRIBUTE19 ATTRIBUTE19
, COM.ATTRIBUTE20 ATTRIBUTE20
, COM.ATTRIBUTE21 ATTRIBUTE21
, COM.ATTRIBUTE22 ATTRIBUTE22
, COM.ATTRIBUTE23 ATTRIBUTE23
, COM.ATTRIBUTE24 ATTRIBUTE24
, COM.ATTRIBUTE25 ATTRIBUTE25
, COM.ATTRIBUTE26 ATTRIBUTE26
, COM.ATTRIBUTE27 ATTRIBUTE27
, COM.ATTRIBUTE28 ATTRIBUTE28
, COM.ATTRIBUTE29 ATTRIBUTE29
, COM.ATTRIBUTE30 ATTRIBUTE30
, COM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, COM.LAST_UPDATED_BY LAST_UPDATED_BY
, COM.CREATION_DATE CREATION_DATE
, COM.CREATED_BY CREATED_BY
, COM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, ORG.NAME DISC_ORG_NAME
, PERX_COUNSEL.FULL_NAME COUNSELOR_FULL_NAME
, PERX_COUNSEL.NATIONAL_IDENTIFIER COUNSELOR_NATIONAL_IDENTIFIER
, PERX_REP.FULL_NAME REPRESENTATIVE_FULL_NAME
, PERX_RMO.FULL_NAME RMO_FULL_NAME
, PERX_RMO.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, PERX_INVEST.FULL_NAME INVESTIGATOR_FULL_NAME
, PERX_INVEST.NATIONAL_IDENTIFIER INVESTIGATOR_NATIONAL_IDENT
, HR_GENERAL.DECODE_LOOKUP('GHR_US_COMPLAINT_TYPE'
, COM.COMPLAINT_TYPE_CODE) COMPLAINT_TYPE_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_NATURE_CLOSING'
, COM.NATURE_OF_CLOSURE_CODE) NOC_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_STAGE_CLOSING'
, COM.CLOSURE_STAGE_CODE) CLOSURE_STAGE_MEANING
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, COM.INFORMAL_ADJUSTMENT_CODE) INFORMAL_ADJUSTMENT_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_AGENCY_ACTION'
, COM.AGENCY_ACTION_CODE) AGENCY_ACTION_MEANING
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, COM.SPL_CORRESP_CODE) SPC_CORRESP_MEANING
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, COM.REOPENING_GRANTED_CODE) REOPENING_GRANTED_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_EEOC_RECOM_DEC'
, COM.RECOMMENDED_DECISION_CODE) RECOMMENDED_DECISION_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_EEOC_RECOM_DEC_ACT'
, COM.EXAMINERS_RECOMM_STATUS_CODE) EXAMINERS_RCMMNDTN_STATUS_MEAN
, HR_GENERAL.DECODE_LOOKUP('GHR_US_FRM_RECOM_DEC'
, COM.ORG_RECOMM_DECISION_CODE) ORG_RECOMM_DECISION_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_FRM_RECOM_DEC_ACT'
, COM.ORG_RECOMM_DECISION_ACTN_CODE) ORG_RECOMM_DECISION_ACT_MEAN
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, COM.REMANDED) REMANDED_MEANING
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, COM.DRP_CLOSURE) DRP_CLOSURE_MEANING
, HR_GENERAL.DECODE_LOOKUP('GHR_US_INV_RECOM_DEC'
, COM.INVESTIGATORS_RECOMMENDATION) INVEST_RECOMM_MEANING
FROM GHR_COMPLAINTS COM
, HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_X PERX_COUNSEL
, PER_PEOPLE_X PERX_REP
, PER_PEOPLE_X PERX_RMO
, PER_PEOPLE_X PERX_INVEST
WHERE ORG.ORGANIZATION_ID(+) = COM.DISCRIM_ORG_ID
AND PERX_COUNSEL.PERSON_ID(+) = COM.COUNSELOR_ID
AND PERX_REP.PERSON_ID(+) = COM.REPRESENTATIVE_ID
AND PERX_RMO.PERSON_ID(+) = COM.RMO_ID
AND PERX_INVEST.PERSON_ID(+) = COM.INVESTIGATOR_ID

Columns