generate info

append_sql = "AND A.REFERENCE_NO = '" + reference_no + "'";

string GET_RESEARCHER_INFO = @" 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, DECODE (B.TARAF_JAWATAN_SUB_FK,1,'PERMANENT',2,'CONTRACT',3,'TEMPORARY',13,'TEMPORARY',12,'CONTRACT') AS SERVICE_TYPE, CASE WHEN B.TARAF_JAWATAN_SUB_FK = 1 THEN B.TKH_PENCEN WHEN B.TARAF_JAWATAN_SUB_FK IN (2,3,13,12) THEN B.TKH_TAMAT_KONTRAK END AS SERVICE_DUE_DATE, (SELECT X.NAMA_PARAMETER_EN FROM HR_LPPT_KLASIFIKASI I, SMU_PARAMETER X WHERE I.STAF_FK = B.STAF_PK AND I.STATUS_AKTIF ='Y' AND I.TKH_HAPUS IS NULL AND X.NILAI_MARKAH = I.LPPT_KATEGORI AND X.KUMPULAN_FK = 19 AND X.KOD IN ('1041','1042','1043','1044')) AS KLASIFIKASI, F.EMAIL_RASMI, DECODE (C.JANTINA,'1','MALE','2','FEMALE') AS GENDER, G.COST_CENTER_CODE, CASE WHEN H.PHASE IS NOT NULL THEN EXTRACT (YEAR FROM (A.TKH_CIPTA)) || H.PHASE WHEN H.PHASE IS NULL THEN '-' END AS PHASE, F.NO_TEL_BIMBIT, F.NO_TEL_PEJABAT, I.CLAIM_FIN_ID, I.UTMFIN_REFNO, I.PAYMENT_TYPE, I.CLAIM_PK, I.STATUS_FK, B.STAF_PK, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'COUNTER') AS TKH_COUNTER, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'PAYMENT CLERK') AS TKH_PAYMENT_CLERK, I.TKH_CIPTA, TO_CHAR(A.TKH_CIPTA,'HH:MI:SS AM') as MASA, (SELECT TKH_CIPTA FROM RMC_CLAIM_SUBMIT WHERE STATUS_FK = 358 AND CLAIM_FK = I.CLAIM_PK AND ROWNUM <= 1) AS SUBMIT_DATE, (SELECT TO_CHAR(TKH_CIPTA,'HH:MI:SS AM') FROM RMC_CLAIM_SUBMIT WHERE STATUS_FK = 358 AND CLAIM_FK = I.CLAIM_PK AND ROWNUM <= 1) AS SUBMIT_TIME, I.ASIGNEE_FK, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'OFFICER') AS TKH_OFFICER, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'HIGHER OFFICER') AS TKH_HIGHER_OFFICER, C.MAKLUMAT_PERIBADI_PK, I.METRIC_NO, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'APPOINTMENT OFFICER') AS TKH_APPOINTMENT_OFFICER, CASE WHEN J.PAYMENT_METHOD IS NOT NULL THEN J.PAYMENT_METHOD WHEN J.PAYMENT_METHOD IS NULL THEN M.PAYMENT_METHOD END AS PAYMENT_METHOD, (SELECT MAX(TKH_CIPTA) FROM RMC_CLAIM_SUBMIT WHERE STATUS_FK = 358 AND CLAIM_FK = I.CLAIM_PK) AS RESUBMIT_DATE, (SELECT TO_CHAR(MAX(TKH_CIPTA),'HH:MI:SS AM') FROM RMC_CLAIM_SUBMIT WHERE STATUS_FK = 358 AND CLAIM_FK = I.CLAIM_PK) AS RESUBMIT_TIME, L.PROGRESS_TRACK, 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.CLAIM_NO, I.APP_RSGSPB_FK, NVL(MTH.MONTH,0) AS MONTH, NVL(MTH.YEAR,0) AS YEAR, MTH.NAME, MTH.IC_NUMBER, MTH.RECIPIENT_TYPE, (SELECT MAX (X.CLAIM_SUBMIT_PK) FROM RMC_CLAIM_SUBMIT X, RMC_STATUS Y WHERE X.STATUS_FK = Y.STATUS_PK AND X.CLAIM_FK = I.CLAIM_PK AND X.TKH_HAPUS IS NULL AND Y.DESCRIPTION = 'RMC DIRECTOR') AS TKH_RMC_DIRECTOR, (SELECT * FROM (SELECT STATUS_FK FROM RMC_CLAIM_SUBMIT WHERE CLAIM_FK = '' AND STATUS_FK NOT IN (432,431) AND TKH_HAPUS IS NULL ORDER BY CLAIM_SUBMIT_PK DESC) WHERE ROWNUM = 1) AS STATUS_PREVIOUS, (SELECT PTYPEDETAIL_NAME FROM RMC_PTYPEDETAIL WHERE PTYPEDETAIL_PK = A.PTYPEDETAIL_FK) AS PROJECT_TYPE, (SELECT SUBSPONSOR_NAME FROM RMC_SUBSPONSOR_DETAIL WHERE SUBSPONSOR_PK = A.SUBSPONSOR_FK) AS SPONSOR_DETAIL, A.PRO_DATESTART, A.PRO_DATEEND, (SELECT DESCRIPTION FROM RMC_PRO_ACTIVATED WHERE CODE = A.PRO_ACTIVATED) AS PROJ_STATUS, CA.ACTIVITY_APPLICATION_FK, NVL(FD.REFERENCE_NO,'-') AS DISBURSEMENT_REFERENCE_NO, NVL(FJ.REFERENCE_NO,'-') AS JOURNAL_REFERENCE_NO, NVL(FJ.JOURNAL_NO,'-') AS JOURNAL_NO, NVL(FV.REFERENCE_NO,'-') AS VOUCHER_REFERENCE_NO 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_CLAIM_APPLICATION I ON I.REFERENCE_NO = A.REFERENCE_NO AND I.TKH_HAPUS IS NULL AND I.CLAIM_PK = '' LEFT JOIN FS.FS_PAYMENT@SMUDEV K ON K.REFERENCE_NO = I.UTMFIN_REFNO LEFT JOIN FS.FS_REFERRAL_CLAIM_RECIPIENT@SMUDEV M ON M.REFERRAL_CLAIM_ID = K.ID LEFT JOIN FS.FS_PAYMENT_RECIPIENT@SMUDEV J ON J.PAYMENT_ID = K.ID LEFT JOIN RMC_STATUS L ON L.STATUS_PK = I.STATUS_FK 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_CLAIM_MONTH MTH ON MTH.CLAIM_FK=I.CLAIM_PK AND MTH.TKH_HAPUS IS NULL LEFT JOIN RMC_CLAIM_ACTIVITY CA ON CA.CLAIM_FK = I.CLAIM_PK AND CA.TKH_HAPUS IS NULL LEFT JOIN FS.FS_DISBURSEMENT_ITEM@SMUDEV FDI ON FDI.VOUCHER_ID = K.VOUCHER_ID AND FDI.M_ST=1 LEFT JOIN FS.FS_DISBURSEMENT@SMUDEV FD ON FD.ID=FDI.DISBURSEMENT_ID AND FD.M_ST=1 LEFT JOIN FS.FS_VOUCHER@SMUDEV FV ON FV.ID = K.VOUCHER_ID LEFT JOIN FS.FS_JOURNAL@SMUDEV FJ ON FJ.SOURCE_NO = K.REFERENCE_NO WHERE A.TKH_HAPUS IS NULL AND " + append_sql;