SELECT F.TRX_NUMBER, I.TRX_ABBRV, F.TRX_DATE, F.LINE_NUMBER, F.SUB_LINE_NUMBER,
       J.STAGE, K.WH_CODE, K.WH_NAME, L.PD_SUB_SCT_CODE, L.PD_SUB_SCT_NAME,
       J.TRX_NUMBER, E.BATCH_NUMBER, F.RLSE_DATE, E.LINE, M.NMBR,
       CASE WHEN (E.CAPA_RQST_FLG = :yesFlag       -- YesNo.Yes.name()
                    OR E.CAPA_QC_FLG = :yesFlag
                    OR E.CAPA_TRBL_FLG =  :yesFlag )
            THEN  :yesFlag
            ELSE :noFlag         -- YesNo.No.name()
       END AS CAPA_UNFNSHD_FLG, E.QC_RQST_FLG AS QC_UNFNSHD_FLG,
       CASE WHEN E.CNFDNTL_FLG =  :yesFlag AND G.ID IS NULL
            THEN :noFlag
            ELSE  :yesFlag
       END AS MAY_VIEW_EBR_FLG, E.STTS, A.EBRT047_ID, E.ID AS EBRO002_ID, J.ID AS MBR_ID, J.TYP, E.BATCH_GRP, E.CNFDNTL_FLG, O.ID AS MBR_SUB_SCT_ID
FROM EBRT013 A
        LEFT JOIN EBRT012 B ON B.ID = A.EBRT012_ID
        LEFT JOIN EBRT007 C ON C.ID = B.EBRT007_ID
        LEFT JOIN EBRT006 D ON D.ID = C.EBRT006_ID
        LEFT JOIN EBRO002 E ON E.ID = D.EBRO002_ID
        LEFT JOIN EBRO001 F ON F.ID = E.EBRO001_ID
        LEFT JOIN EMSM940 G ON G.COS_ID = F.COS_ID
                               AND G.SCT_ID = E.SCT_ID
                               AND (G.SUB_SCT_ID IS NULL OR G.SUB_SCT_ID = E.SUB_SCT_ID)
                               AND :systemDate BETWEEN G.VLD_DATE_FROM AND G.VLD_DATE_TO
                               AND G.USER_ID = :userLoginId
                               AND G.ACTV_FLG = :activeFlag        -- ActiveState.Active.name()
        LEFT JOIN EMSM591 H ON H.ID = A.GDS_ID
        LEFT JOIN BMAS010 I ON I.ID = F.TRX_CODE_ID
        LEFT JOIN EMSM905 J ON J.ID = E.MBR_ID

        LEFT JOIN EMSM906 N ON N.EMSM905_ID = E.MBR_ID
                            AND N.WH_ID = E.SCT_ID
        LEFT JOIN EMSM907 O ON O.PD_SUB_SCT_ID = E.SUB_SCT_ID
                            AND O.EMSM906_ID = N.ID

        LEFT JOIN EMSM580 K ON K.ID = E.SCT_ID
        LEFT JOIN EMSM582 L ON L.ID = E.SUB_SCT_ID
        LEFT JOIN EBRT047 M ON M.ID = A.EBRT047_ID
WHERE A.GDS_ID = :wipId
      AND E.BATCH_NUMBER = :batchNumber
GROUP BY F.TRX_NUMBER, I.TRX_ABBRV, F.TRX_DATE, F.LINE_NUMBER, F.SUB_LINE_NUMBER,
         J.STAGE, K.WH_CODE, K.WH_NAME, L.PD_SUB_SCT_CODE, L.PD_SUB_SCT_NAME,
         J.TRX_NUMBER, E.BATCH_NUMBER, F.RLSE_DATE, E.LINE, M.NMBR,
         E.CAPA_RQST_FLG, E.CAPA_QC_FLG, E.CAPA_TRBL_FLG, CAPA_UNFNSHD_FLG,
         E.QC_RQST_FLG, E.CNFDNTL_FLG, E.STTS, A.EBRT047_ID, E.ID
ORDER BY F.TRX_DATE DESC, I.TRX_ABBRV, F.TRX_NUMBER, F.LINE_NUMBER, F.SUB_LINE_NUMBER, E.LINE, M.NMBR, J.ID, J.TYP, E.BATCH_GRP, E.CNFDNTL_FLG, O.ID