VIEW: APPS.IGSFV_PR_COHORT_INST_RANKS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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