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