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