VIEW: APPS.IGS_AD_POSTADM_MISS_ITM_LTR_V

Source

View Text - Preformatted

SELECT hz.party_id person_id, hz.party_name party_name, hz.party_number person_number, hz.party_type party_type, hz.address1 address_line1, hz.address2 address_line2, hz.address3 address_line3, hz.address4 address_line4, hz.analysis_fy analysis_fy, hz.category_code category_code, hz.city city, hz.competitor_flag competitor_flag, hz.country country, hz.county county, hz.curr_fy_potential_revenue curr_fy_potential_revenue, hz.customer_key customer_key, hz.do_not_mail_flag do_not_mail_flag, hz.duns_number duns_number, hz.email_address email_address, hz.employees_total employees_total, hz.fiscal_yearend_month fiscal_yearend_month, hz.group_type group_type, hz.gsa_indicator_flag gsa_indicator_flag, hz.hq_branch_ind hq_branch_ind, hz.jgzz_fiscal_code jgzz_fiscal_code, hz.known_as preferred_given_name, hz.known_as2 known_as2, hz.known_as3 known_as3, hz.known_as4 known_as4, hz.known_as5 known_as5, hz.language_name language_name, hz.last_ordered_date last_ordered_date, hz.mission_statement mission_statement, hz.next_fy_potential_revenue next_fy_potential_revenue, hz.organization_name_phonetic organization_name_phonetic, hz.orig_system_reference orig_system_reference, hz.person_academic_title person_academic_title, hz.person_first_name given_names, hz.person_first_name_phonetic person_first_name_phonetic, hz.person_iden_type person_iden_type, hz.person_identifier person_identifier, hz.person_last_name surname, hz.person_last_name_phonetic person_last_name_phonetic, hz.person_middle_name middle_name, hz.person_name_suffix suffix, hz.person_pre_name_adjunct prefix, hz.person_previous_last_name person_previous_last_name, hz.person_title title, hz.postal_code postal_code, hz.province province, hz.reference_use_flag reference_use_flag, hz.salutation salutation, hz.sic_code sic_code, hz.sic_code_type sic_code_type, hz.state state, hz.status status, hz.tax_name tax_name, hz.tax_reference tax_reference, hz.third_party_flag third_party_flag, hz.total_num_of_orders total_num_of_orders, hz.total_ordered_amount total_ordered_amount, hz.url url, hz.validated_flag validated_flag, TO_DATE(null) wh_update_date, hz.year_established year_established, aa.adm_cal_type adm_cal_type, aa.adm_ci_sequence_number adm_ci_sequence_number, aa.acad_cal_type acad_cal_type, aa.acad_ci_sequence_number acad_ci_sequence_number, apai.admission_appl_number adm_appl_number, apai.nominated_course_cd nominated_course_cd, apai.sequence_number appl_sequence_number, apai.adm_doc_status adm_doc_status, ps.responsible_org_unit_cd org_unit_cd , ps.title course_title, itm.description item_description, itm.completion_due_dt completion_due_date, step.description step_description, TO_CHAR( step.action_dt,'DD-MON-YYYY') step_due_date, igs_co_gen_003.get_per_addr_for_corr(hz.party_id) person_name_address, loc.description location_desc, am.description attendence_mode_desc, aty.description attendence_type_desc, persid.group_id pers_group_id, ous.s_adm_outcome_status s_adm_outcome_status, apai.adm_outcome_status adm_outcome_status, aa.admission_cat || '/' || lkup.meaning adm_process_cat, TO_CHAR (SYSDATE, 'FMDD Month RRRR') todays_date, aa.application_id FROM hz_parties hz, igs_pe_hz_parties pe, igs_ad_appl_all aa, igs_ad_appl_stat ast, igs_ad_ps_appl_inst_all apai, igs_pe_prsid_grp_mem_all persid, igs_ad_ou_stat ous, igs_ps_ver_all ps, igs_ad_aplins_admreq ar, igs_tr_item_v itm, igs_tr_type trt, igs_tr_status stat, igs_tr_step_v step, igs_lookups_view lkup, igs_ad_prcs_cat_all apc, igs_ad_location_all loc, igs_en_atd_mode_all am, igs_en_atd_type_all aty 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 = hz.party_id AND aa.adm_appl_status = ast.adm_appl_status AND ast.s_adm_appl_status = 'RECEIVED' AND apai.person_id = aa.person_id AND apai.admission_appl_number = aa.admission_appl_number AND apai.adm_outcome_status = ous.adm_outcome_status AND apai.course_cd = ps.course_cd AND apai.crv_version_number = ps.version_number AND ar.person_id = apai.person_id AND ar.admission_appl_number = apai.admission_appl_number AND ar.course_cd = apai.nominated_course_cd AND ar.sequence_number = apai.sequence_number AND ar.tracking_id = itm.tracking_id AND itm.tracking_status = stat.tracking_status AND itm.tracking_type = trt.tracking_type AND trt.s_tracking_type = 'POST_ADMISSION' AND stat.s_tracking_status = 'ACTIVE' AND step.tracking_id = itm.tracking_id AND step.tracking_step_id IN ( SELECT trst.tracking_step_id FROM igs_tr_step trst WHERE trst.step_group_id IS NULL AND trst.step_completion_ind = 'N' AND trst.by_pass_ind = 'N' AND trst.tracking_id = itm.tracking_id UNION SELECT trst.tracking_step_id FROM igs_tr_step trst WHERE trst.tracking_id = itm.tracking_id AND trst.step_group_id IN ( SELECT DISTINCT trst.step_group_id FROM igs_tr_step trst WHERE trst.step_group_id IS NOT NULL AND trst.step_completion_ind = 'N' AND trst.by_pass_ind = 'N' AND trst.tracking_id = itm.tracking_id MINUS SELECT DISTINCT trst.step_group_id FROM igs_tr_step trst WHERE trst.step_group_id IS NOT NULL AND trst.step_completion_ind = 'Y' AND trst.tracking_id = itm.tracking_id ) ) AND apc.admission_cat = aa.admission_cat AND apc.s_admission_process_type = aa.s_admission_process_type AND lkup.lookup_type = 'ADMISSION_PROCESS_TYPE' AND aa.s_admission_process_type = lkup.lookup_code AND apai.location_cd = loc.location_cd (+) AND apai.attendance_mode = am.attendance_mode (+) AND apai.attendance_type = aty.attendance_type (+) AND NOT EXISTS (SELECT 'X' FROM igs_ad_prcs_cat_step apcs WHERE apcs.admission_cat = aa.admission_cat AND apcs.s_admission_process_type = aa.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 PERSON_ID
, HZ.PARTY_NAME PARTY_NAME
, HZ.PARTY_NUMBER PERSON_NUMBER
, HZ.PARTY_TYPE PARTY_TYPE
, HZ.ADDRESS1 ADDRESS_LINE1
, HZ.ADDRESS2 ADDRESS_LINE2
, HZ.ADDRESS3 ADDRESS_LINE3
, HZ.ADDRESS4 ADDRESS_LINE4
, HZ.ANALYSIS_FY ANALYSIS_FY
, HZ.CATEGORY_CODE CATEGORY_CODE
, HZ.CITY CITY
, HZ.COMPETITOR_FLAG COMPETITOR_FLAG
, HZ.COUNTRY COUNTRY
, HZ.COUNTY COUNTY
, HZ.CURR_FY_POTENTIAL_REVENUE CURR_FY_POTENTIAL_REVENUE
, HZ.CUSTOMER_KEY CUSTOMER_KEY
, HZ.DO_NOT_MAIL_FLAG DO_NOT_MAIL_FLAG
, HZ.DUNS_NUMBER DUNS_NUMBER
, HZ.EMAIL_ADDRESS EMAIL_ADDRESS
, HZ.EMPLOYEES_TOTAL EMPLOYEES_TOTAL
, HZ.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, HZ.GROUP_TYPE GROUP_TYPE
, HZ.GSA_INDICATOR_FLAG GSA_INDICATOR_FLAG
, HZ.HQ_BRANCH_IND HQ_BRANCH_IND
, HZ.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE
, HZ.KNOWN_AS PREFERRED_GIVEN_NAME
, HZ.KNOWN_AS2 KNOWN_AS2
, HZ.KNOWN_AS3 KNOWN_AS3
, HZ.KNOWN_AS4 KNOWN_AS4
, HZ.KNOWN_AS5 KNOWN_AS5
, HZ.LANGUAGE_NAME LANGUAGE_NAME
, HZ.LAST_ORDERED_DATE LAST_ORDERED_DATE
, HZ.MISSION_STATEMENT MISSION_STATEMENT
, HZ.NEXT_FY_POTENTIAL_REVENUE NEXT_FY_POTENTIAL_REVENUE
, HZ.ORGANIZATION_NAME_PHONETIC ORGANIZATION_NAME_PHONETIC
, HZ.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, HZ.PERSON_ACADEMIC_TITLE PERSON_ACADEMIC_TITLE
, HZ.PERSON_FIRST_NAME GIVEN_NAMES
, HZ.PERSON_FIRST_NAME_PHONETIC PERSON_FIRST_NAME_PHONETIC
, HZ.PERSON_IDEN_TYPE PERSON_IDEN_TYPE
, HZ.PERSON_IDENTIFIER PERSON_IDENTIFIER
, HZ.PERSON_LAST_NAME SURNAME
, HZ.PERSON_LAST_NAME_PHONETIC PERSON_LAST_NAME_PHONETIC
, HZ.PERSON_MIDDLE_NAME MIDDLE_NAME
, HZ.PERSON_NAME_SUFFIX SUFFIX
, HZ.PERSON_PRE_NAME_ADJUNCT PREFIX
, HZ.PERSON_PREVIOUS_LAST_NAME PERSON_PREVIOUS_LAST_NAME
, HZ.PERSON_TITLE TITLE
, HZ.POSTAL_CODE POSTAL_CODE
, HZ.PROVINCE PROVINCE
, HZ.REFERENCE_USE_FLAG REFERENCE_USE_FLAG
, HZ.SALUTATION SALUTATION
, HZ.SIC_CODE SIC_CODE
, HZ.SIC_CODE_TYPE SIC_CODE_TYPE
, HZ.STATE STATE
, HZ.STATUS STATUS
, HZ.TAX_NAME TAX_NAME
, HZ.TAX_REFERENCE TAX_REFERENCE
, HZ.THIRD_PARTY_FLAG THIRD_PARTY_FLAG
, HZ.TOTAL_NUM_OF_ORDERS TOTAL_NUM_OF_ORDERS
, HZ.TOTAL_ORDERED_AMOUNT TOTAL_ORDERED_AMOUNT
, HZ.URL URL
, HZ.VALIDATED_FLAG VALIDATED_FLAG
, TO_DATE(NULL) WH_UPDATE_DATE
, HZ.YEAR_ESTABLISHED YEAR_ESTABLISHED
, AA.ADM_CAL_TYPE ADM_CAL_TYPE
, AA.ADM_CI_SEQUENCE_NUMBER ADM_CI_SEQUENCE_NUMBER
, AA.ACAD_CAL_TYPE ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER ACAD_CI_SEQUENCE_NUMBER
, APAI.ADMISSION_APPL_NUMBER ADM_APPL_NUMBER
, APAI.NOMINATED_COURSE_CD NOMINATED_COURSE_CD
, APAI.SEQUENCE_NUMBER APPL_SEQUENCE_NUMBER
, APAI.ADM_DOC_STATUS ADM_DOC_STATUS
, PS.RESPONSIBLE_ORG_UNIT_CD ORG_UNIT_CD
, PS.TITLE COURSE_TITLE
, ITM.DESCRIPTION ITEM_DESCRIPTION
, ITM.COMPLETION_DUE_DT COMPLETION_DUE_DATE
, STEP.DESCRIPTION STEP_DESCRIPTION
, TO_CHAR( STEP.ACTION_DT
, 'DD-MON-YYYY') STEP_DUE_DATE
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID) PERSON_NAME_ADDRESS
, LOC.DESCRIPTION LOCATION_DESC
, AM.DESCRIPTION ATTENDENCE_MODE_DESC
, ATY.DESCRIPTION ATTENDENCE_TYPE_DESC
, PERSID.GROUP_ID PERS_GROUP_ID
, OUS.S_ADM_OUTCOME_STATUS S_ADM_OUTCOME_STATUS
, APAI.ADM_OUTCOME_STATUS ADM_OUTCOME_STATUS
, AA.ADMISSION_CAT || '/' || LKUP.MEANING ADM_PROCESS_CAT
, TO_CHAR (SYSDATE
, 'FMDD MONTH RRRR') TODAYS_DATE
, AA.APPLICATION_ID
FROM HZ_PARTIES HZ
, IGS_PE_HZ_PARTIES PE
, IGS_AD_APPL_ALL AA
, IGS_AD_APPL_STAT AST
, IGS_AD_PS_APPL_INST_ALL APAI
, IGS_PE_PRSID_GRP_MEM_ALL PERSID
, IGS_AD_OU_STAT OUS
, IGS_PS_VER_ALL PS
, IGS_AD_APLINS_ADMREQ AR
, IGS_TR_ITEM_V ITM
, IGS_TR_TYPE TRT
, IGS_TR_STATUS STAT
, IGS_TR_STEP_V STEP
, IGS_LOOKUPS_VIEW LKUP
, IGS_AD_PRCS_CAT_ALL APC
, IGS_AD_LOCATION_ALL LOC
, IGS_EN_ATD_MODE_ALL AM
, IGS_EN_ATD_TYPE_ALL ATY
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 = HZ.PARTY_ID
AND AA.ADM_APPL_STATUS = AST.ADM_APPL_STATUS
AND AST.S_ADM_APPL_STATUS = 'RECEIVED'
AND APAI.PERSON_ID = AA.PERSON_ID
AND APAI.ADMISSION_APPL_NUMBER = AA.ADMISSION_APPL_NUMBER
AND APAI.ADM_OUTCOME_STATUS = OUS.ADM_OUTCOME_STATUS
AND APAI.COURSE_CD = PS.COURSE_CD
AND APAI.CRV_VERSION_NUMBER = PS.VERSION_NUMBER
AND AR.PERSON_ID = APAI.PERSON_ID
AND AR.ADMISSION_APPL_NUMBER = APAI.ADMISSION_APPL_NUMBER
AND AR.COURSE_CD = APAI.NOMINATED_COURSE_CD
AND AR.SEQUENCE_NUMBER = APAI.SEQUENCE_NUMBER
AND AR.TRACKING_ID = ITM.TRACKING_ID
AND ITM.TRACKING_STATUS = STAT.TRACKING_STATUS
AND ITM.TRACKING_TYPE = TRT.TRACKING_TYPE
AND TRT.S_TRACKING_TYPE = 'POST_ADMISSION'
AND STAT.S_TRACKING_STATUS = 'ACTIVE'
AND STEP.TRACKING_ID = ITM.TRACKING_ID
AND STEP.TRACKING_STEP_ID IN ( SELECT TRST.TRACKING_STEP_ID
FROM IGS_TR_STEP TRST
WHERE TRST.STEP_GROUP_ID IS NULL
AND TRST.STEP_COMPLETION_IND = 'N'
AND TRST.BY_PASS_IND = 'N'
AND TRST.TRACKING_ID = ITM.TRACKING_ID UNION SELECT TRST.TRACKING_STEP_ID
FROM IGS_TR_STEP TRST
WHERE TRST.TRACKING_ID = ITM.TRACKING_ID
AND TRST.STEP_GROUP_ID IN ( SELECT DISTINCT TRST.STEP_GROUP_ID
FROM IGS_TR_STEP TRST
WHERE TRST.STEP_GROUP_ID IS NOT NULL
AND TRST.STEP_COMPLETION_IND = 'N'
AND TRST.BY_PASS_IND = 'N'
AND TRST.TRACKING_ID = ITM.TRACKING_ID MINUS SELECT DISTINCT TRST.STEP_GROUP_ID
FROM IGS_TR_STEP TRST
WHERE TRST.STEP_GROUP_ID IS NOT NULL
AND TRST.STEP_COMPLETION_IND = 'Y'
AND TRST.TRACKING_ID = ITM.TRACKING_ID ) )
AND APC.ADMISSION_CAT = AA.ADMISSION_CAT
AND APC.S_ADMISSION_PROCESS_TYPE = AA.S_ADMISSION_PROCESS_TYPE
AND LKUP.LOOKUP_TYPE = 'ADMISSION_PROCESS_TYPE'
AND AA.S_ADMISSION_PROCESS_TYPE = LKUP.LOOKUP_CODE
AND APAI.LOCATION_CD = LOC.LOCATION_CD (+)
AND APAI.ATTENDANCE_MODE = AM.ATTENDANCE_MODE (+)
AND APAI.ATTENDANCE_TYPE = ATY.ATTENDANCE_TYPE (+)
AND NOT EXISTS (SELECT 'X'
FROM IGS_AD_PRCS_CAT_STEP APCS
WHERE APCS.ADMISSION_CAT = AA.ADMISSION_CAT
AND APCS.S_ADMISSION_PROCESS_TYPE = AA.S_ADMISSION_PROCESS_TYPE
AND APCS.S_ADMISSION_STEP_TYPE = 'AUTO-OFFER'
AND APCS.STEP_GROUP_TYPE = 'OUTCOME'
AND APCS.STEP_GROUP_TYPE <> 'TRACK' )