|
1
|
ALTER PROC [dbo].[TR_REQ_PAY_Job_DT_By_REQ_Id]
|
|
2
|
@p_REQ_ID varchar(15)
|
|
3
|
AS
|
|
4
|
BEGIN
|
|
5
|
SELECT A.REQ_ID, A.REQ_CODE, A.FROM_DATE AS FRMDATE, A.TO_DATE AS TODATE,
|
|
6
|
CAST(DATEDIFF(DAY,A.FROM_DATE, A.TO_DATE) AS DECIMAL(18, 0)) AS NUMBER_DAY,
|
|
7
|
--U.TLFULLNAME AS FULLNAME, U.TLNANME AS TLNAME, D.TO_TRIP JOB_PLACE,
|
|
8
|
200000 DAY_RATE, 200000 * CAST(DATEDIFF(DAY,A.FROM_DATE, A.TO_DATE) AS DECIMAL(18, 0)) JOB_COST,
|
|
9
|
B.VEHICLE_TYPE TYPE_TRANS, 1 COST_MOVE, 1 COST_RESIDENCE, 1 COST_OTHER, 1 COST_AMT,
|
|
10
|
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
|
|
11
|
ELSE B.FROM_TRAIN+' - '+ B.TO_TRAIN END FOR XML PATH('')), 1, 3, '') AS 'JOB_PLACE',
|
|
12
|
STUFF((SELECT ', ' + CHAR(10) + G.CONTENT FOR XML PATH('')), 1, 3, '') AS 'TYPE_TRANS',
|
|
13
|
EMP.TLFullName AS 'FULLNAME'
|
|
14
|
FROM TR_REQUEST_JOB_FORM A
|
|
15
|
LEFT JOIN TR_REQUEST_JOB_TRAIN B ON A.REQ_ID = B.REQ_ID
|
|
16
|
LEFT JOIN TR_REQUEST_JOB_DETAIL C ON A.REQ_ID = C.REQ_ID
|
|
17
|
LEFT JOIN TR_REQ_JOB_TRIP D ON A.REQ_ID = D.REQ_ID
|
|
18
|
LEFT JOIN TL_USER U ON C.TLNANME = U.TLNANME
|
|
19
|
LEFT JOIN CM_PROVINCE E ON B.TO_TRAIN = E.PRO_ID
|
|
20
|
LEFT JOIN CM_PROVINCE F ON B.FROM_TRAIN = F.PRO_ID
|
|
21
|
LEFT JOIN CM_ALLCODE G ON B.VEHICLE_TYPE = G.CDVAL AND CDNAME = 'VEHICLE_TYPE' AND CDTYPE = 'TR'
|
|
22
|
LEFT JOIN TL_USER EMP ON A.EMP_ID = EMP.TLNANME
|
|
23
|
WHERE A.REQ_ID =@p_REQ_ID
|
|
24
|
END
|