ALTER   PROC [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]
@p_REQ_PAY_ID varchar(15) = NULL,
@p_BRANCH_CREATE varchar(15) = NULL,
@p_DEP_ID varchar(15) = NULL,
@p_CHECKR_ID varchar(15) = NULL,
@p_CHECKR_ID_KT varchar(15) = NULL,
@p_APPROVE_DT_KT varchar(15) = NULL
AS
BEGIN
	SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS STT, JF.REQ_CODE, A.FULLNAME, A.JOB_PLACE, 
	FORMAT (A.FRMDATE, 'dd/MM/yyyy ') AS FRMDATE, FORMAT (A.TODATE, 'dd/MM/yyyy ') AS TODATE, 
	A.NUMBER_DAY,
	A.DAY_RATE, A.JOB_COST, A.COST_RESIDENCE, A.TYPE_TRANS, A.COST_AMT, A.COST_OTHER, A.COST_MOVE,
	A.COST_MOVE + A.JOB_COST + A.COST_RESIDENCE + A.COST_OTHER AS TOTAL_AMT, CEL.POS_NAME AS POS_NAME
	FROM TR_REQ_PAY_Job_DT A 
	LEFT JOIN TR_REQUEST_JOB_FORM JF ON A.REQ_ID = JF.REQ_ID
	LEFT JOIN  CM_EMPLOYEE_LOG CEL ON CEL.USER_DOMAIN = JF.EMP_ID
	--LEFT JOIN TR_REQ_PAY_JOB J ON A.REQ_ID = J.REQ_ID
	LEFT JOIN TR_REQ_PAYMENT TR ON A.REQ_PAY_ID = TR.REQ_PAY_ID
	LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = @p_BRANCH_CREATE
	LEFT JOIN TL_USER T ON T.TLNANME = A.FULLNAME
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
	
	SELECT TOP 1 N'Từ ngày ' + CAST(FORMAT (A.FRMDATE, 'dd/MM/yyyy ') as nvarchar) + ', ' AS FRMDATE
	FROM TR_REQ_PAY_Job_DT A
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
	ORDER BY A.FRMDATE ASC

	SELECT TOP 1 N'đến ngày ' + CAST(FORMAT (A.TODATE, 'dd/MM/yyyy ') as nvarchar) AS TODATE
	FROM TR_REQ_PAY_Job_DT A
	WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
	ORDER BY A.TODATE DESC

	SELECT TOP 1 B.REQ_PAY_CODE, ISNULL(A.AMT_PAY,0) AS AMT_PAY, C.REQ_PAY_CODE AS REQ_PAY_ADVANCE_CODE
	FROM TR_REQ_PAYMENT_DT A
	LEFT JOIN TR_REQ_PAYMENT B ON A.PAY_ID = B.REQ_PAY_ID
	LEFT JOIN TR_REQ_PAYMENT C ON A.PAY_ADV_ID = C.REQ_PAY_ID
	WHERE A.PAY_ID =@p_REQ_PAY_ID
	
	DECLARE @AMT_PAY DECIMAL(18,2)
	SELECT TOP 1 @AMT_PAY = ISNULL(A.AMT_PAY,0)
	FROM TR_REQ_PAYMENT_DT A
	WHERE A.PAY_ID =@p_REQ_PAY_ID

	SELECT @AMT_PAY - ISNULL(SUM(A.COST_AMT),0) AS 'TIENHOANUNGCHITHEM', ISNULL(SUM(A.JOB_COST),0) AS SUM_JOB_COST, ISNULL(SUM(A.COST_RESIDENCE),0) AS SUM_COST_RESIDENCE, ISNULL(SUM(A.COST_MOVE),0) AS SUM_COST_MOVE, ISNULL(SUM(A.COST_OTHER),0) AS SUM_COST_OTHER, ISNULL(SUM(A.COST_AMT),0) AS SUM_COST_AMT FROM TR_REQ_PAY_Job_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
END