View: IGSFV_PR_COHORT_INST_RANKS

View Text

SELECT CO.PERSON_ID
, CO.COURSE_CD
, CO.AS_OF_RANK_GPA
, CO.COHORT_RANK
, CO.COHORT_OVERRIDE_RANK
, DECODE ( PC.DFLT_DISPLAY_TYPE
, 'N_OF_N'
, NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) || ' ' || FND_MESSAGE.GET_STRING ('IGS'
, 'IGS_PR_MSG_OF') || ' ' || CPOP.COHORT_POPULATION
, 'PERCENTILE'
, LTRIM (TO_CHAR (TO_DATE (CEIL ((NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) * 100)/(CPOP.COHORT_POPULATION + 1))
, 'J')
, 'JTH')
, '0') || ' ' || FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_PRCNTL')
, 'VIGINTILE'
, LTRIM (TO_CHAR (TO_DATE (CEIL ((NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) * 20)/(CPOP.COHORT_POPULATION + 1))*100/20
, 'J')
, 'JTH')
, '0') || ' ' || FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_PRCNTL')
, 'DECILE'
, LTRIM (TO_CHAR (TO_DATE (CEIL ((NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) * 10)/(CPOP.COHORT_POPULATION + 1))*100/10
, 'J')
, 'JTH')
, '0') || ' ' || FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_PRCNTL')
, 'QUINTILE'
, LTRIM (TO_CHAR (TO_DATE (CEIL ((NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) * 5)/(CPOP.COHORT_POPULATION + 1))*100/5
, 'J')
, 'JTH')
, '0') || ' ' || FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_PRCNTL')
, 'QUARTILE'
, LTRIM (TO_CHAR (TO_DATE (CEIL ((NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK) * 4)/(CPOP.COHORT_POPULATION + 1))*100/4
, 'J')
, 'JTH')
, '0') || ' ' || FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_PRCNTL')
, 'TOP_THIRD'
, DECODE ((SELECT 'X'
FROM DUAL
WHERE (((CPOP.COHORT_POPULATION - NVL (CO.COHORT_OVERRIDE_RANK
, CO.COHORT_RANK))/CPOP.COHORT_POPULATION) * 100) > 67)
, 'X'
, FND_MESSAGE.GET_STRING('IGS'
, 'IGS_PR_RNK_TP_TRD')) )
, CO.COMMENTS
, HP.PARTY_NUMBER
, PS.TITLE
, CO.CREATED_BY
, CO.CREATION_DATE
, CO.LAST_UPDATED_BY
, CO.LAST_UPDATE_DATE FROM IGS_PR_COHINST_RANK CO
, IGS_EN_STDNT_PS_ATT_ALL ST
, HZ_PARTIES HP
, IGS_PS_VER_ALL PS
, IGS_PR_COHORT_INST CI
, IGS_PR_COHORT PC
, (SELECT COHI.COHORT_NAME COHORT_NAME
, COHI.LOAD_CAL_TYPE LOAD_CAL_TYPE
, COHI.LOAD_CI_SEQUENCE_NUMBER LOAD_CI_SEQUENCE_NUMBER
, COUNT (*) COHORT_POPULATION
FROM IGS_PR_COHINST_RANK COHI GROUP BY COHI.COHORT_NAME
, COHI.LOAD_CAL_TYPE
, COHI.LOAD_CI_SEQUENCE_NUMBER) CPOP
WHERE CO.PERSON_ID = HP.PARTY_ID
AND CO.PERSON_ID = ST.PERSON_ID
AND CO.COURSE_CD = ST.COURSE_CD
AND PS.COURSE_CD = ST.COURSE_CD
AND PS.VERSION_NUMBER = ST. VERSION_NUMBER
AND CI.COHORT_NAME = CO.COHORT_NAME
AND CI.LOAD_CAL_TYPE = CO.LOAD_CAL_TYPE
AND CI.LOAD_CI_SEQUENCE_NUMBER = CO. LOAD_CI_SEQUENCE_NUMBER
AND CI.COHORT_NAME = PC.COHORT_NAME
AND CPOP.COHORT_NAME = PC.COHORT_NAME WITH READ ONLY

Columns