VIEW: APPS.IGS_AD_MISSING_ITEMS_LETTER_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 , PS.TITLE , AA.ACAD_CAL_TYPE , AA.ACAD_CI_SEQUENCE_NUMBER , AA.ADM_CAL_TYPE , AA.ADM_CI_SEQUENCE_NUMBER , APAI.ADMISSION_APPL_NUMBER , APAI.NOMINATED_COURSE_CD , APAI.SEQUENCE_NUMBER , APAI.ADM_DOC_STATUS , ITM.DESCRIPTION , ITM.COMPLETION_DUE_DT , STEP.DESCRIPTION , TO_CHAR (SYSDATE,'MonthFMDD,RRRR') , TO_CHAR(STEP.ACTION_DT,'DD-MON-YYYY') , LOC.DESCRIPTION , AM.DESCRIPTION , ATY.DESCRIPTION , igs_co_gen_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID) , PS.RESPONSIBLE_ORG_UNIT_CD , PERSID.GROUP_ID , APAI.ADM_OUTCOME_STATUS , OUS.S_ADM_OUTCOME_STATUS , AA.ADMISSION_CAT || '/' || LKUP.MEANING , aa.application_id FROM HZ_PARTIES HZ, IGS_PE_HZ_PARTIES PE, IGS_AD_APPL_ALL AA, IGS_PE_PRSID_GRP_MEM_ALL PERSID, IGS_AD_APPL_STAT AST, IGS_AD_PS_APPL_INST_ALL APAI, 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_AD_PRCS_CAT_ALL APC, IGS_AD_LOCATION_ALL LOC, IGS_EN_ATD_MODE_ALL AM, IGS_EN_ATD_TYPE_ALL ATY, IGS_LOOKUPS_VIEW LKUP, IGS_AD_DOC_STAT adc 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 APAI.ADM_OUTCOME_STATUS = OUS.ADM_OUTCOME_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.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 = 'ADM_PROCESSING' 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 adc.S_ADM_DOC_STATUS != 'SATISFIED' AND adc.ADM_DOC_STATUS = apai.ADM_DOC_STATUS 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
, 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
, PS.TITLE
, AA.ACAD_CAL_TYPE
, AA.ACAD_CI_SEQUENCE_NUMBER
, AA.ADM_CAL_TYPE
, AA.ADM_CI_SEQUENCE_NUMBER
, APAI.ADMISSION_APPL_NUMBER
, APAI.NOMINATED_COURSE_CD
, APAI.SEQUENCE_NUMBER
, APAI.ADM_DOC_STATUS
, ITM.DESCRIPTION
, ITM.COMPLETION_DUE_DT
, STEP.DESCRIPTION
, TO_CHAR (SYSDATE
, 'MONTHFMDD
, RRRR')
, TO_CHAR(STEP.ACTION_DT
, 'DD-MON-YYYY')
, LOC.DESCRIPTION
, AM.DESCRIPTION
, ATY.DESCRIPTION
, IGS_CO_GEN_003.GET_PER_ADDR_FOR_CORR(HZ.PARTY_ID)
, PS.RESPONSIBLE_ORG_UNIT_CD
, PERSID.GROUP_ID
, APAI.ADM_OUTCOME_STATUS
, OUS.S_ADM_OUTCOME_STATUS
, AA.ADMISSION_CAT || '/' || LKUP.MEANING
, AA.APPLICATION_ID
FROM HZ_PARTIES HZ
, IGS_PE_HZ_PARTIES PE
, IGS_AD_APPL_ALL AA
, IGS_PE_PRSID_GRP_MEM_ALL PERSID
, IGS_AD_APPL_STAT AST
, IGS_AD_PS_APPL_INST_ALL APAI
, 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_AD_PRCS_CAT_ALL APC
, IGS_AD_LOCATION_ALL LOC
, IGS_EN_ATD_MODE_ALL AM
, IGS_EN_ATD_TYPE_ALL ATY
, IGS_LOOKUPS_VIEW LKUP
, IGS_AD_DOC_STAT ADC
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 APAI.ADM_OUTCOME_STATUS = OUS.ADM_OUTCOME_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.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 = 'ADM_PROCESSING'
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 ADC.S_ADM_DOC_STATUS != 'SATISFIED'
AND ADC.ADM_DOC_STATUS = APAI.ADM_DOC_STATUS
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' )