VIEW: APPS.IGS_AD_OUTCOME_LETTERS_V

Source

View Text - Preformatted

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' )

View Text - HTML Formatted

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' )