sql-generate-grant-act-detail

SELECT A.REFERENCE_NO, B.NO_PEKERJA, C.GELARAN_RASMI, C.NAMA, E.DESKRIPSI,
             (SELECT D.DESCRIPTION FROM RMC_RESEARCH_ALLIANCE D  WHERE D.PTJ_RA_CODE =
             (SELECT PTJ_RA FROM RMC_RARG WHERE STAF_FK=B.STAF_PK AND STATUS_AKTIF ='Y' AND TKH_HAPUS IS NULL AND D.TKH_HAPUS IS NULL)) AS RA,
             (SELECT E.DESKRIPSI FROM HR_FAKULTI E WHERE SUBSTR(B.KOD_PTJ,0,3) = E.KOD_FAKULTI) AS FAKULTI,
             CASE
               WHEN B.TARAF_JAWATAN_SUB_FK = 1
                 THEN B.TKH_PENCEN
               WHEN B.TARAF_JAWATAN_SUB_FK IN (2,3,13)
                 THEN B.TKH_TAMAT_KONTRAK
             END,
             F.EMAIL_RASMI,
             DECODE (C.JANTINA,'1','MALE','2','FEMALE') AS GENDER,
             G.COST_CENTER_CODE,
             NVL(F.NO_TEL_BIMBIT,'-'), NVL(F.NO_TEL_PEJABAT,'-'), B.STAF_PK, C.MAKLUMAT_PERIBADI_PK,
             CASE
                   WHEN (RARG.RG_FK IS NOT NULL OR RARG.RG_FK != 0) AND (RARG.PTJ_COE IS NULL OR TRIM(RARG.PTJ_COE) = '0' OR RARG.COE_ROLE NOT IN ('L','M'))
                     THEN
                           UPPER(TRIM(RG.DESCRIPTION))  || ' - ' || DECODE(RG.CLASSIFICATION,1,'SF',2,'MF')  || ' - ' || DECODE(RG.CLASSIFICATION,1,UPPER(TRIM(FAC.DESKRIPSI)),2,UPPER(TRIM(RA.DESCRIPTION)))
                   ELSE
                           CASE
                             WHEN COE.TYPE_ID = 1
                               THEN UPPER(TRIM(COE.COE_DESCRIPTION)) || ' - ' || 'COE' || ' - ' || UPPER(TRIM(RA.DESCRIPTION))
                             WHEN  COE.TYPE_ID = 2
                               THEN  UPPER(TRIM(COE.COE_DESCRIPTION)) || ' - ' || 'RC' || ' - ' || UPPER(TRIM(COEINS.INSTITUT_DESCRIPTION))
                             WHEN  COE.TYPE_ID = 3
                               THEN  UPPER(TRIM(COE.COE_DESCRIPTION)) || ' - ' || 'HiCOE' || ' - ' || UPPER(TRIM(COE.COE_DESCRIPTION))
                             ELSE
                               ''
                           END
               END AS RESEARCH_CLASSIFICATION,
             I.ACTIVITY_NO, I.APPLICATION_PK, I.STATUS_FK, TO_CHAR(A.TKH_CIPTA,'HH:MI:SS AM') AS MASA, A.TKH_CIPTA,
             I.APPLICANT_FK, I.APPLICANT_GUEST_FK, I.APPLICANT_STUDENT_ID
             ,G.COST_CENTER_PK, I.DECLARATION,
             (SELECT MAX (STAF_FK) FROM RMC_ACT_SUBMISSION WHERE APPLICATION_FK = I.APPLICATION_PK AND STATUS_FK IN (467,465,466,469,470)) AS TDPI_PK,
             (SELECT MAX (STAF_FK) FROM RMC_ACT_SUBMISSION WHERE APPLICATION_FK = I.APPLICATION_PK AND STATUS_FK IN (458,459,460)) AS CLERK_PK,
             (SELECT MAX (STAF_FK) FROM RMC_ACT_SUBMISSION WHERE APPLICATION_FK = I.APPLICATION_PK AND STATUS_FK IN (8,11,13)) AS OFFICER_PK,
             (SELECT MAX (STAF_FK) FROM RMC_ACT_SUBMISSION WHERE APPLICATION_FK = I.APPLICATION_PK AND STATUS_FK IN (462,463)) AS DEPUTY_PK, A.PTYPEDETAIL_FK
              ,A.PRO_DATESTART, A.PRO_DATEEND
             FROM RMC_PRO A
             LEFT JOIN HR_STAF B ON B.MAKLUMAT_PERIBADI_FK = A.MAKLUMAT_PERIBADI_FK AND B.TKH_HAPUS IS NULL
             JOIN HR_MAKLUMAT_PERIBADI C ON C.MAKLUMAT_PERIBADI_PK = A.MAKLUMAT_PERIBADI_FK AND C.TKH_HAPUS IS NULL
             LEFT JOIN HR_KOD_JAWATAN D ON D.KOD_JAWATAN = B.KOD_JAWATAN
             LEFT JOIN HR_KOD_JENIS_JAWATAN E ON E.KOD_JENIS_JAWATAN = D.KOD_JENIS_JAWATAN AND E.KOD_KLASIFIKASI = D.KOD_KLASIFIKASI
             LEFT JOIN HR_ALAMAT F ON F.ALAMAT_PK = C.ALAMAT_PEJABAT_FK
             LEFT JOIN RMC_COST_CENTER G ON G.REFERENCE_NO = A.REFERENCE_NO AND G.TKH_HAPUS IS NULL
             LEFT JOIN RMC_PRO2 H ON H.REFERENCE_NO = A.REFERENCE_NO AND H.TKH_HAPUS IS NULL
             LEFT JOIN RMC_RARG RARG ON RARG.STAF_FK=B.STAF_PK AND RARG.TKH_HAPUS IS NULL AND RARG.STATUS_AKTIF='Y'
             LEFT JOIN RMC_RG RG ON RG.RG_PK=RARG.RG_FK AND RG.TKH_HAPUS IS NULL AND RG.STATUS_AKTIF='Y'
             LEFT JOIN RMC_RESEARCH_ALLIANCE RA ON RA.PTJ_RA_CODE=RARG.PTJ_RA AND RA.TKH_HAPUS IS NULL
             LEFT JOIN HR_FAKULTI FAC ON FAC.KOD_FAKULTI=SUBSTR(NVL(B.KOD_PTJ_ASAL,B.KOD_PTJ), 0, 3) AND FAC.TKH_HAPUS IS NULL
             LEFT JOIN RMC_COE COE ON COE.PTJ_COE=RARG.PTJ_COE AND COE.TKH_HAPUS IS NULL AND COE.STATUS_AKTIF='Y'
             LEFT JOIN RMC_INSTITUT COEINS ON COEINS.INSTITUT_PK=COE.INSTITUT_FK
             LEFT JOIN RMC_ACT_APPLICATION I ON I.COST_CENTER_CODE = G.COST_CENTER_CODE
             WHERE A.TKH_HAPUS IS NULL
             AND A.REFERENCE_NO = 'PY/2019/00001';