SELECT
	DISTINCT B.STUDENTID,
	'UNIVERSITAS TELKOM' NAMAINSTITUSI,
	'TELKOM UNIVERSITY' INSTITUTIONNAME,
	'Keputusan Menteri' IJINPENDIRIAN,
	'''A ''-3125/SK/BAN-PT/Akred/PT/XII/2016' AS AKREDITASINSTITUSI,
	'Ministerial Decree' LICENSEOFSTABLISHMENT,
	'No.309/E/O/2013' NOMORIJINPENDIRIAN,
	FACULTYNAME FAKULTAS,
	FACULTYENGLISHNAME FACULTY,
	INITCAP (PRODINAME) PROGRAMSTUDY,
	INITCAP (ENGLISHNAME2) STUDYPROGRAM,
	NVL (E .SPECIALIZATIONNAME,
	'-') SPECIALIZATIONNAME,
	NVL ( E .SPECIALIZATIONNAMEENGLISH,
	'-' ) SPECIALIZATIONNAMEENGLISH,
	F.CLASSIFICATION,
	F.CLASSIFICATIONENGLISHNAME,
	FF.NOMORAKREDITASI,
	FF.ACCREDIATIONINTER,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Vokasi'
		ELSE 'Akademik'
	END JENISPENDIDIKAN,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Vocation'
		ELSE 'Academic'
	END EDUCATIONTYPE,
	SUBSTR (STUDYPROGRAMNAME,
	1,
	2) JENJANGAKADEMIK,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Diploma'
		WHEN 'S1' THEN 'Bachelor'
		WHEN 'S2' THEN 'Master Degree'
		WHEN 'S3' THEN 'Doctoral'
	END DEGREE,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'KKNI Level 5'
		WHEN 'S1' THEN 'KKNI Level 6'
		ELSE 'KKNI Level 8'
	END JENJANGKUALIFIKASI,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Indonesia Qualification Level 5'
		WHEN 'S1' THEN 'Indonesia Qualification Level 6'
		ELSE 'Indonesia Qualification Level 8'
	END QUALIFICATION,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN '3 Tahun'
		WHEN 'S1' THEN '4 Tahun'
		WHEN 'S2' THEN '2 Tahun'
	END MASASTUDYREGULER,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN '3 Years'
		WHEN 'S1' THEN '4 Years'
		WHEN 'S2' THEN '2 Years'
	END REGULERSTUDYPERIOD,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Lulusan SMA atau Sederajat'
		WHEN 'S1' THEN 'Lulusan SMA atau Sederajat'
		WHEN 'S2' THEN 'Lulusan Sarjana'
	END SYARATPENERIMAAN,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'High School Graduate'
		WHEN 'S1' THEN 'High School Graduate'
		WHEN 'S2' THEN 'Bachelor Graduate'
	END ADMISSIONREQUIRMENT,
	'Penuh Waktu' TIPEPERKKULIAHAN,
	'Full Time' MODEOFSTUDY,
	CASE
		WHEN STUDYPROGRAMNAME LIKE '%(Internasional Class)%' THEN 'Bahasa Inggris'
		ELSE 'Bahasa Indonesia'
	END BAHASAPENGANTAR,
	CASE
		WHEN STUDYPROGRAMNAME LIKE '%(Internasional Class)%' THEN 'English'
		ELSE 'Indonesian'
	END LANGUAGEOFINSTRUCTION,
	'A=4; AB=3.5; B=3; BC=2.5; C=2; D=1; E=0' SISTEMPENILAIAN,
	NULL GRADINGSYSTEM,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Sarjana'
		WHEN 'S1' THEN 'Magister'
		WHEN 'S2' THEN 'Dokter'
	END KELANJUTANSTUDY,
	CASE
		SUBSTR (STUDYPROGRAMNAME,
		1,
		2) WHEN 'D3' THEN 'Bachelor'
		WHEN 'S1' THEN 'Master Degree'
		WHEN 'S2' THEN 'Doctoral'
	END ACCESSTOFURTHERESTUDY
FROM
	MASTERDATA.STUDENT B
JOIN MASTERDATA.STUDYPROGRAMSPECIALIZATION E ON
	( (E .STUDYPROGRAMID = B.STUDYPROGRAMID)
		AND ( B.CLASS2 = E.SPECIALIZATIONID) )
JOIN MASTERDATA.STUDYPROGRAM C ON
	( E .STUDYPROGRAMID = C.STUDYPROGRAMID )
JOIN (
	SELECT
		DISTINCT STUDYPROGRAMID,
		CASE
			WHEN STUDYPROGRAMID = '40' THEN 'S1 Manajemen (Manajemen Bisnis Telekomunikasi & Informatika)'
			WHEN STUDYPROGRAMID = '46' THEN 'S1 Administrasi Bisnis'
			ELSE STUDYPROGRAMNAME
		END PRODINAME,
		CASE
			WHEN STUDYPROGRAMID = '40' THEN 'MANAGEMENT (BUSINESS MANAGEMENT IN TELECOMMUNICATIONS & INFORMATICS)'
			WHEN STUDYPROGRAMID = '46' THEN 'BUSINESS ADMINISTRATION'
			ELSE ENGLISHNAME
		END ENGLISHNAME2
	FROM
		MASTERDATA.STUDYPROGRAM
	ORDER BY
		STUDYPROGRAMID ) DE ON
	( CASE
		WHEN B.STUDYPROGRAMID = '40' THEN '41'
		WHEN B.STUDYPROGRAMID = '46' THEN '44'
		WHEN B.STUDYPROGRAMID = '48' THEN '43'
		WHEN B.STUDYPROGRAMID = '97' THEN '91'
		WHEN B.CLASSIFICATIONID = '64' THEN '64'
		ELSE B.STUDYPROGRAMID
	END = DE.STUDYPROGRAMID )
JOIN MASTERDATA.FACULTIES D ON
	(C.FACULTYID = D .FACULTYID)
LEFT JOIN (
	SELECT
		STUDENTID,
		CASE
			WHEN CLASSIFICATION LIKE '%PJJ%' THEN STUDYPROGRAMNAME || ' - REGULER'
			WHEN CLASSIFICATION LIKE '%International%' THEN STUDYPROGRAMNAME || ' - REGULER'
			ELSE CLASSIFICATION
		END CLASSIFICATION,
		CASE
			WHEN CLASSIFICATION LIKE '%PJJ%' THEN CLASSIFICATIONENGLISHNAME || ' - REGULAR'
			WHEN CLASSIFICATION LIKE '%International%' THEN CLASSIFICATIONENGLISHNAME || ' - REGULAR'
			ELSE CLASSIFICATION
		END CLASSIFICATIONENGLISHNAME,
		STUDYPROGRAMID
	FROM
		(
		SELECT
			STUDENTID,
			B.CLASSIFICATIONID,
			STUDYPROGRAMNAME,
			CASE
				WHEN B.CLASSIFICATIONID IS NULL THEN ( STUDYPROGRAMNAME || ' - REGULER' )
				ELSE CLASSIFICATIONNAME
			END CLASSIFICATION,
			CASE
				WHEN B.CLASSIFICATIONID IS NULL THEN ( SUBSTR (STUDYPROGRAMNAME,
				1,
				2) || ' ' || ENGLISHNAME || ' - REGULAR' )
				WHEN CLASSIFICATIONNAME LIKE '%Pindahan%' THEN SUBSTR (STUDYPROGRAMNAME,
				1,
				2) || ' ' || ENGLISHNAME || ' - EXTENTION'
				ELSE SUBSTR (STUDYPROGRAMNAME,
				1,
				2) || ' ' || ENGLISHNAME
			END CLASSIFICATIONENGLISHNAME,
			C.STUDYPROGRAMID
		FROM
			MASTERDATA.STUDENT A
		LEFT JOIN MASTERDATA.STUDYPROGRAMCLASSIFICATION B ON
			( A .CLASSIFICATIONID = B.CLASSIFICATIONID
				AND A .STUDYPROGRAMID = B.STUDYPROGRAMID )
		JOIN MASTERDATA.STUDYPROGRAM C ON
			( A .STUDYPROGRAMID = C.STUDYPROGRAMID ) )
	WHERE
		STUDENTID = '2401222146' ) F ON
	( B.STUDENTID = F.STUDENTID
		AND C .STUDYPROGRAMID = F.STUDYPROGRAMID )
LEFT JOIN (
	SELECT
		NOMORAKREDITASI,
		STUDENTID,
		ACCREDIATIONINTER,
		B.STUDYPROGRAMID
	FROM
		(
		SELECT
			STUDYPROGRAMID,
			'"' || GRADE || '" - ' || ACCREDITATIONDECREE AS NOMORAKREDITASI,
			STARTDATE,
			ENDDATE
		FROM
			ACADEMIC.ACCREDITATION
		WHERE
			"LEVEL" = 'NASIONAL' ) A
	JOIN (
		SELECT
			B.STUDENTID,
			ACADEMICCOUNCILNAME,
			ACADEMICCOUNCILDATE,
			STUDYPROGRAMID
		FROM
			ACADEMIC.ACADEMICSTATUS A
		JOIN MASTERDATA.STUDENT B ON
			(A .STUDENTID = B.STUDENTID)
		WHERE
			B.STUDENTID = '2401222146'
			AND GRADE IN (
			SELECT
				MAX (GRADE)
			FROM
				ACADEMIC.ACADEMICSTATUS
			WHERE
				STUDENTID = '2401222146' )
			AND ACADEMICCOUNCILNAME IN ( 'SARJANA', 'DIPLOMA', 'MAGISTER', 'DOKTOR' ) ) B ON
		( A .STUDYPROGRAMID = B.STUDYPROGRAMID )
	LEFT JOIN (
		SELECT
			STUDYPROGRAMID,
			ACCREDITATIONID,
			ACCREDITATIONDECREE AS "ACCREDIATIONINTER",
			"LEVEL"
		FROM
			ACADEMIC.ACCREDITATION
		WHERE
			"LEVEL" = 'INTERNASIONAL' ) C ON
		( B.STUDYPROGRAMID = C.STUDYPROGRAMID )
	WHERE
		ACADEMICCOUNCILDATE BETWEEN STARTDATE AND ENDDATE ) FF ON
	( C.STUDYPROGRAMID = FF.STUDYPROGRAMID )
WHERE
	B.STUDENTID = '2401222146'
	AND B.CLASS2 = E.SPECIALIZATIONID