View: IGS_CO_OU_CO_V

Product:	IGS - Student System
Description:	Joins all references for an outgoing correspondence item
Implementation/DBA Data:	APPS.IGS_CO_OU_CO_V

View Text

SELECT OC.ROWID
, OC.PERSON_ID PERSON_ID
, PER.PERSON_NUMBER
, IGS_CO_GEN_001.CORP_GET_COR_CAT(OC.PERSON_ID
, NVL(OCR.COURSE_CD
, CORI.COURSE_CD)) CORRESPONDENCE_CAT
, CORI.CORRESPONDENCE_TYPE CORRESPONDENCE_TYPE
, CORI.REFERENCE_NUMBER REFERENCE_NUMBER
, OC.ISSUE_DT ISSUE_DT
, OC.DT_SENT DT_SENT
, OC.UNKNOWN_RETURN_DT UNKNOWN_RETURN_DT
, OC.ADDR_TYPE ADDR_TYPE
, OC.COMMENTS
, OC.LETTER_REFERENCE_NUMBER
, OC.SPL_SEQUENCE_NUMBER
, ADT.DESCRIPTION ADT_DESCRIPTION
, OC.TRACKING_ID TRACKING_ID
, CORI.CREATE_DT CREATE_DT
, CORI.ORIGINATOR_PERSON_ID ORIGINATOR_PERSON_ID
, CORI.REQUEST_NUM REQUEST_NUM
, CORI.S_JOB_NAME S_JOB_NAME
, CORI.REQUEST_JOB_ID REQUEST_JOB_ID
, CORI.OUTPUT_NUM OUTPUT_NUM
, CORI.REQUEST_JOB_RUN_ID REQUEST_JOB_RUN_ID
, NVL(OCR.CAL_TYPE
, CORI.CAL_TYPE) CAL_TYPE
, NVL(OCR.CI_SEQUENCE_NUMBER
, CORI.CI_SEQUENCE_NUMBER) CI_SEQUENCE_NUMBER
, NVL(OCR.COURSE_CD
, CORI.COURSE_CD) COURSE_CD
, NVL(OCR.CV_VERSION_NUMBER
, CORI.CV_VERSION_NUMBER) CV_VERSION_NUMBER
, NVL(OCR.UNIT_CD
, CORI.UNIT_CD) UNIT_CD
, NVL(OCR.UV_VERSION_NUMBER
, CORI.UV_VERSION_NUMBER) UV_VERSION_NUMBER
, OCR.S_OTHER_REFERENCE_TYPE S_OTHER_REFERENCE_TYPE
, OCR.OTHER_REFERENCE OTHER_REFERENCE
, OC.COMMENTS OC_COMMENTS
, CORI.COMMENTS CORI_COMMENTS
, OC.CREATED_BY CREATED_BY
, OC.CREATION_DATE CREATION_DATE
, OC.LAST_UPDATED_BY LAST_UPDATED_BY
, OC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, OC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM IGS_CO_OU_CO OC
, IGS_CO_ITM CORI
, IGS_CO_ADDR_TYPE ADT
, IGS_CO_OU_CO_REF OCR
, IGS_PE_PERSON PER
WHERE CORI.CORRESPONDENCE_TYPE = OC.CORRESPONDENCE_TYPE
AND CORI.REFERENCE_NUMBER = OC.REFERENCE_NUMBER
AND ADT.ADDR_TYPE(+) = OC.ADDR_TYPE
AND OCR.PERSON_ID(+) = OC.PERSON_ID
AND OCR.CORRESPONDENCE_TYPE(+) = OC.CORRESPONDENCE_TYPE
AND OCR.REFERENCE_NUMBER(+) = OC.REFERENCE_NUMBER
AND OCR.ISSUE_DT(+) = OC.ISSUE_DT
AND PER.PERSON_ID = OC.PERSON_ID

Columns