ALTER   PROC [dbo].[TR_REQ_PAY_Job_DT_By_REQ_Id]
@p_REQ_ID varchar(15)
AS
BEGIN
	SELECT A.REQ_ID, A.REQ_CODE, A.FROM_DATE AS FRMDATE, A.TO_DATE AS TODATE,
	CAST(DATEDIFF(DAY,A.FROM_DATE, A.TO_DATE) AS DECIMAL(18, 0)) AS NUMBER_DAY,
	--U.TLFULLNAME AS FULLNAME, U.TLNANME AS TLNAME, D.TO_TRIP JOB_PLACE, 
	200000 DAY_RATE, 200000 * CAST(DATEDIFF(DAY,A.FROM_DATE, A.TO_DATE) AS DECIMAL(18, 0)) JOB_COST, 
	B.VEHICLE_TYPE TYPE_TRANS, 1 COST_MOVE, 1 COST_RESIDENCE, 1 COST_OTHER, 1 COST_AMT,
	STUFF((SELECT ', ' + CHAR(10) +	CASE WHEN(F.PRO_NAME IS NOT NULL OR E.PRO_NAME IS NOT NULL) THEN F.PRO_NAME + ' - ' + E.PRO_NAME 
	ELSE B.FROM_TRAIN+' - '+ B.TO_TRAIN END FOR XML PATH('')), 1, 3, '') AS 'JOB_PLACE',
	STUFF((SELECT ', ' + CHAR(10) + G.CONTENT FOR XML PATH('')), 1, 3, '') AS 'TYPE_TRANS',
	EMP.TLFullName AS 'FULLNAME'
	FROM TR_REQUEST_JOB_FORM A 
	LEFT JOIN TR_REQUEST_JOB_TRAIN B ON A.REQ_ID = B.REQ_ID
	LEFT JOIN TR_REQUEST_JOB_DETAIL C ON A.REQ_ID = C.REQ_ID
	LEFT JOIN TR_REQ_JOB_TRIP D ON A.REQ_ID = D.REQ_ID
	LEFT JOIN TL_USER U ON C.TLNANME = U.TLNANME
	LEFT JOIN CM_PROVINCE E ON B.TO_TRAIN = E.PRO_ID
	LEFT JOIN CM_PROVINCE F ON B.FROM_TRAIN = F.PRO_ID
	LEFT JOIN CM_ALLCODE G ON B.VEHICLE_TYPE = G.CDVAL AND CDNAME = 'VEHICLE_TYPE' AND CDTYPE = 'TR'
	LEFT JOIN TL_USER EMP ON A.EMP_ID = EMP.TLNANME
	WHERE A.REQ_ID =@p_REQ_ID
END