ALTER    PROC dbo.rpt_REQ_DELIVERY_MASTER_Excel
    @p_EMP_ID VARCHAR(50)  = NULL,
    @p_DEP_ID	VARCHAR(50) = NULL,
    @p_fromdate VARCHAR(20) = NULL,
    @p_todate VARCHAR(20) = NULL,
    @p_BILL_STATUS VARCHAR(20) = NULL,
    @p_LEVEL VARCHAR(15) = NULL
AS
SELECT ROW_NUMBER() OVER (ORDER BY A.REQ_CODE) AS STT,
        A.FROM_DATE  
        ,A.REQ_CODE
        ,DT.REQ_DT_ID AS BILL_ID --,DT.BILL_ID
        ,ca.CONTENT AS TYPE_MAIL_NAME
        ,DT.EMP_REVEICE_NAME
        ,DT.PHONE
        ,DT.LOCATION
        ,ca1.CONTENT AS DV_CHUYEN_PHAT
        ,DICH_VU.ADD_SEVICE_NAME
        ,DT.QUANTITY
        ,DT.TRAN_COST,
    CASE 
       WHEN DT.BILL_STATUS IN ('TRANSPORT_AXIS', 'TRANSPORT_RECEIVE_TO_DELIVERY', 'PACKAGE_CONFIRM_GO_DELIVERY', 'TRANSPORT_INTERNAL') 
       THEN N'Đang vận chuyển'
       WHEN DT.BILL_STATUS IN ('ASSIGN_DELIVERY', 'DELIVERING')
       THEN N'Hàng đang đi giao/Đang hoàn (dựa vào is_return)'
      ELSE dss.STATUS_NAME
   END AS STATUS_NAME
	FROM  DELI_REQ_DELIVERY_MASTER A
  INNER JOIN DELI_REQ_DELIVERY_DT DT ON A.REQ_ID = DT.REQ_ID
 LEFT JOIN CM_ALLCODE ca ON ca.CDVAL = A.TYPE_MAIL AND ca.CDNAME = 'TYPE_MAIL' AND ca.CDTYPE = 'DELI'
 LEFT JOIN CM_ALLCODE ca1 ON ca1.CDVAL = A.TRAN_SV AND ca1.CDNAME = 'TRAN_SV' AND ca1.CDTYPE = 'DELI'
OUTER APPLY (  
        SELECT 
            STRING_AGG(B.ADD_SEVICE_NAME, ', ') AS ADD_SEVICE_NAME
        FROM DELI_ADD_SERVICE ASE
          INNER JOIN DELI_CM_ADD_SEVICE B ON ASE.ADD_ID = B.ID_ADD
          WHERE ASE.REQ_ID = A.REQ_ID
    ) AS DICH_VU
 LEFT JOIN DELI_SYNC_STATUS dss ON DT.BILL_STATUS = dss.STATUS_CODE
	WHERE 1=1
    AND (A.AUTH_STATUS= 'A') -- chỉ lấy những phiếu có trạng thái đã duyệt 
    AND(DT.BILL_ID IS NOT NULL AND DT.BILL_ID <> '') -- Chỉ lấy những phiếu có bill_id
    AND (A.DEP_ID = @p_DEP_ID OR  @p_DEP_ID IS NULL OR @p_DEP_ID ='')
    AND (A.EMP_ID = @p_EMP_ID OR  @p_EMP_ID IS NULL OR @p_EMP_ID ='')
    AND (@p_fromdate IS NULL OR @p_fromdate = '' OR CONVERT(DATE, A.FROM_DATE, 103) >= CONVERT(DATE, @p_fromdate, 103))
    AND (@p_todate IS NULL OR @p_todate = '' OR CONVERT(DATE, A.FROM_DATE, 103) <= CONVERT(DATE, @p_todate, 103))
   AND (
    (@p_BILL_STATUS = 'TRANS_GO' AND DT.BILL_STATUS IN ('TRANSPORT_AXIS', 'TRANSPORT_RECEIVE_TO_DELIVERY', 'PACKAGE_CONFIRM_GO_DELIVERY', 'TRANSPORT_INTERNAL'))
     OR(@p_BILL_STATUS = 'TRANS_GIAO' AND DT.BILL_STATUS IN ('DELIVERING', 'ASSIGN_DELIVERY'))
    OR (DT.BILL_STATUS LIKE '%' + @p_BILL_STATUS + '%' OR @p_BILL_STATUS IS NULL OR @p_BILL_STATUS = '')
)