View: IGS_AD_OUTCOME_LETTERS_V

View Text

SELECT HZ.PARTY_ID
, HZ.PARTY_NAME
, HZ.PARTY_NUMBER
, HZ.PARTY_TYPE
, HZ.ADDRESS1
, HZ.ADDRESS2
, HZ.ADDRESS3
, HZ.ADDRESS4
, HZ.ANALYSIS_FY
, HZ.CATEGORY_CODE
, HZ.CITY
, HZ.COMPETITOR_FLAG
, HZ.COUNTRY
, HZ.COUNTY
, HZ.CURR_FY_POTENTIAL_REVENUE
, HZ.CUSTOMER_KEY
, HZ.DO_NOT_MAIL_FLAG
, HZ.DUNS_NUMBER
, HZ.EMAIL_ADDRESS
, HZ.EMPLOYEES_TOTAL
, HZ.FISCAL_YEAREND_MONTH
, HZ.GROUP_TYPE
, HZ.GSA_INDICATOR_FLAG
, HZ.HQ_BRANCH_IND
, HZ.JGZZ_FISCAL_CODE
, HZ.KNOWN_AS
, HZ.KNOWN_AS2
, HZ.KNOWN_AS3
, HZ.KNOWN_AS4
, HZ.KNOWN_AS5
, HZ.LANGUAGE_NAME
, HZ.LAST_ORDERED_DATE
, HZ.MISSION_STATEMENT
, HZ.NEXT_FY_POTENTIAL_REVENUE
, HZ.ORGANIZATION_NAME_PHONETIC
, HZ.ORIG_SYSTEM_REFERENCE
, HZ.PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME
, HZ.PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME
, HZ.PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT
, HZ.PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE
, HZ.POSTAL_CODE
, HZ.PROVINCE
, HZ.REFERENCE_USE_FLAG
, HZ.SALUTATION
, HZ.SIC_CODE
, HZ.SIC_CODE_TYPE
, HZ.STATE
, HZ.STATUS
, HZ.TAX_NAME
, HZ.TAX_REFERENCE
, HZ.THIRD_PARTY_FLAG
, HZ.TOTAL_NUM_OF_ORDERS
, HZ.TOTAL_ORDERED_AMOUNT
, HZ.URL
, HZ.VALIDATED_FLAG
, TO_DATE(NULL) WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED
, ACAIV.PREFERENCE_NUMBER
, PS.COURSE_CD
, PS.TITLE
, PS.RESPONSIBLE_ORG_UNIT_CD
, OU.PARTY_NAME DESCRIPTION
, PS.RESPONSIBLE_OU_START_DT
, PS.COURSE_TYPE
, ACAIV.LOCATION_CD
, ACAIV.ATTENDANCE_MODE
, ACAIV.ATTENDANCE_TYPE
, ACAIV.ADM_OUTCOME_STATUS
, ACAIV.ENTRY_STATUS
, ACAIV.ENTRY_STATUS_NAME
, ACAIV.ENTRY_STATUS_DESC
, ACAIV.ENTRY_LEVEL
, ACAIV.ENTRY_LEVEL_NAME
, ACAIV.ENTRY_LEVEL_DESC
, ACAIV.SCH_APL_TO_ID
, ACAIV.SCHOOL_APPLYING_TO
, ACAIV.SCH_APL_TO_DESC
, ACAIV.EXPECTED_COMPLETION_YR
, ACAIV.EXPECTED_COMPLETION_PERD
, ACAIV.EDU_GOAL_PRIOR_ENROLL
, ACAIV.EDU_GOAL_PRIOR_ENROLL_DESC
, LOC.DESCRIPTION
, AM.DESCRIPTION
, ATY.DESCRIPTION
, AAV.ACAD_ALTERNATE_CODE
, AAV.ACAD_CAL_TYPE
, AAV.ACAD_CI_SEQUENCE_NUMBER
, AAV.ADM_CAL_TYPE
, AAV.ADM_CI_SEQUENCE_NUMBER
, AAV.APPL_DT
, AAV.COMMENCEMENT_PERIOD
, AAV.SPCL_GRP_1
, AAV.SPCL_GRP_1_NAME
, AAV.SPCL_GRP_1_DESC
, AAV.SPCL_GRP_2
, AAV.SPCL_GRP_2_NAME
, AAV.SPCL_GRP_2_DESC
, AAV.ADMISSION_PROCESS_CAT
, OUS.S_ADM_OUTCOME_STATUS
, ACAIV.ADM_CNDTNL_OFFER_STATUS
, ACAIV.CNDTNL_OFFER_CNDTN
, ACAIV.CNDTNL_OFFER_SATISFIED_DT
, ACAIV.CNDTNL_OFFER_MUST_BE_STSFD_IND
, ACAIV.WAITLIST_STATUS
, ACAIV.WAITLIST_STATUS_DESC
, ACAIV.WAITLIST_RANK
, ACAIV.FUTURE_ACAD_ALTERNATE_CODE
, ACAIV.FUTURE_ACAD_CAL_TYPE
, ACAIV.FUTURE_ACAD_CI_SEQUENCE_NUMBER
, ACAIV.FUTURE_ADM_ALTERNATE_CODE
, ACAIV.FUTURE_ADM_CAL_TYPE
, ACAIV.FUTURE_ADM_CI_SEQUENCE_NUMBER
, ACAIV.PREVIOUS_TERM_ADM_APPL_NUMBER
, ACAIV.PREVIOUS_TERM_SEQUENCE_NUMBER
, ACAIV.FUTURE_TERM_ADM_APPL_NUMBER
, ACAIV.FUTURE_TERM_SEQUENCE_NUMBER
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, IGS_CO_GEN_003.GET_PROGRAM_COMPLETION_DT( ACAIV.COURSE_CD
, ACAIV.CRV_VERSION_NUMBER
, ACAIV.ACAD_CAL_TYPE
, AAV.ADM_CAL_TYPE
, AAV.ADM_CI_SEQUENCE_NUMBER
, ACAIV.ATTENDANCE_TYPE
, ACAIV.ATTENDANCE_MODE
, ACAIV.LOCATION_CD)
, IGS_CO_GEN_003.GET_PRG_APPL_INST_DFF_VALUES ( ACAIV.PERSON_ID
, ACAIV.ADMISSION_APPL_NUMBER
, ACAIV.NOMINATED_COURSE_CD
, ACAIV.SEQUENCE_NUMBER)
, IGS_CO_GEN_003.GET_STUDENT_CITIZENSHIP_STATUS(ACAIV.PERSON_ID)
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR')
, PERSID.GROUP_ID
, ACAIV.ADMISSION_APPL_NUMBER
, ACAIV.NOMINATED_COURSE_CD
, ACAIV.SEQUENCE_NUMBER
, AA.APPLICATION_ID
, ACAIV.ADM_OFFER_RESP_STATUS
, ORS.S_ADM_OFFER_RESP_STATUS
, ACAIV.OFFER_RESPONSE_DT
FROM HZ_PARTIES HZ
, IGS_PE_HZ_PARTIES PE
, IGS_AD_APPL_ALL AA
, IGS_AD_APPL_STAT AST
, IGS_AD_PS_APPL_INST_APLINST_V ACAIV
, IGS_AD_APPL_ADMAPPL_V AAV
, IGS_PS_VER_ALL PS
, HZ_PARTIES OU
, IGS_PE_HZ_PARTIES PE1
, IGS_AD_LOCATION_ALL LOC
, IGS_EN_ATD_MODE_ALL AM
, IGS_EN_ATD_TYPE_ALL ATY
, IGS_AD_OU_STAT OUS
, IGS_AD_OFR_RESP_STAT ORS
, IGS_PE_PRSID_GRP_MEM_ALL PERSID
WHERE HZ.PARTY_ID = PE.PARTY_ID
AND PERSID.PERSON_ID(+) =PE.PARTY_ID
AND NVL(PE.DECEASED_IND
, 'N') <> 'Y'
AND AA.PERSON_ID = PE.PARTY_ID
AND AA.ADM_APPL_STATUS = AST.ADM_APPL_STATUS
AND ( AST.S_ADM_APPL_STATUS = 'RECEIVED' OR AST.S_ADM_APPL_STATUS = 'COMPLETED')
AND ACAIV.ADM_OUTCOME_STATUS = OUS.ADM_OUTCOME_STATUS
AND ACAIV.ADM_OFFER_RESP_STATUS = ORS.ADM_OFFER_RESP_STATUS
AND ACAIV.PERSON_ID = AA.PERSON_ID
AND ACAIV.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND AAV.PERSON_ID = ACAIV.PERSON_ID
AND AAV.ADMISSION_APPL_NUMBER = ACAIV.ADMISSION_APPL_NUMBER
AND ACAIV.COURSE_CD = PS.COURSE_CD
AND ACAIV.CRV_VERSION_NUMBER = PS.VERSION_NUMBER
AND ACAIV.LOCATION_CD = LOC.LOCATION_CD (+)
AND ACAIV.ATTENDANCE_MODE = AM.ATTENDANCE_MODE (+)
AND ACAIV.ATTENDANCE_TYPE = ATY.ATTENDANCE_TYPE (+)
AND PS.RESPONSIBLE_ORG_UNIT_CD = PE1.OSS_ORG_UNIT_CD
AND PE1.PARTY_ID = OU.PARTY_ID
AND NOT EXISTS (SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP APCS
WHERE APCS.ADMISSION_CAT = AAV.ADMISSION_CAT
AND APCS.S_ADMISSION_PROCESS_TYPE = AAV.S_ADMISSION_PROCESS_TYPE
AND APCS.S_ADMISSION_STEP_TYPE = 'AUTO-OFFER'
AND APCS.STEP_GROUP_TYPE = 'OUTCOME'
AND APCS.STEP_GROUP_TYPE <> 'TRACK' )

Columns