|
1
|
|
|
2
|
ALTER PROC [dbo].[rpt_TR_REQ_PAY_Job_DT_ById]
|
|
3
|
@p_REQ_PAY_ID varchar(15) = NULL,
|
|
4
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
5
|
@p_DEP_ID varchar(15) = NULL,
|
|
6
|
@p_CHECKR_ID varchar(15) = NULL,
|
|
7
|
@p_CHECKR_ID_KT varchar(15) = NULL,
|
|
8
|
@p_APPROVE_DT_KT varchar(15) = NULL
|
|
9
|
AS
|
|
10
|
BEGIN
|
|
11
|
SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 1)) AS STT, JF.REQ_CODE, A.FULLNAME, A.JOB_PLACE,
|
|
12
|
FORMAT (A.FRMDATE, 'dd/MM/yyyy ') AS FRMDATE, FORMAT (A.TODATE, 'dd/MM/yyyy ') AS TODATE,
|
|
13
|
CAST(DATEDIFF(day,A.FRMDATE, A.TODATE) as decimal(18, 0)) + 1 AS NUMBER_DAY,
|
|
14
|
A.DAY_RATE, A.JOB_COST, A.COST_RESIDENCE, A.TYPE_TRANS, A.JOB_COST, A.COST_OTHER, A.COST_MOVE,
|
|
15
|
A.DAY_RATE + A.JOB_COST + A.COST_RESIDENCE + A.JOB_COST + A.COST_OTHER AS TOTAL_AMT, CEL.POS_NAME AS POS_NAME
|
|
16
|
FROM TR_REQ_PAY_Job_DT A
|
|
17
|
LEFT JOIN TR_REQUEST_JOB_FORM JF ON A.REQ_ID = JF.REQ_ID
|
|
18
|
LEFT JOIN CM_EMPLOYEE_LOG CEL ON CEL.USER_DOMAIN = JF.EMP_ID
|
|
19
|
--LEFT JOIN TR_REQ_PAY_JOB J ON A.REQ_ID = J.REQ_ID
|
|
20
|
LEFT JOIN TR_REQ_PAYMENT TR ON A.REQ_PAY_ID = TR.REQ_PAY_ID
|
|
21
|
LEFT JOIN CM_BRANCH BR ON BR.BRANCH_ID = @p_BRANCH_CREATE
|
|
22
|
LEFT JOIN TL_USER T ON T.TLNANME = A.FULLNAME
|
|
23
|
WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
24
|
|
|
25
|
SELECT TOP 1 N'Từ ngày ' + CAST(FORMAT (A.FRMDATE, 'dd/MM/yyyy ') as nvarchar) + ', ' AS FRMDATE
|
|
26
|
FROM TR_REQ_PAY_Job_DT A
|
|
27
|
WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
28
|
ORDER BY A.FRMDATE ASC
|
|
29
|
|
|
30
|
SELECT TOP 1 N'đến ngày ' + CAST(FORMAT (A.TODATE, 'dd/MM/yyyy ') as nvarchar) AS TODATE
|
|
31
|
FROM TR_REQ_PAY_Job_DT A
|
|
32
|
WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
33
|
ORDER BY A.TODATE DESC
|
|
34
|
|
|
35
|
SELECT TOP 1 B.REQ_PAY_CODE, ISNULL(A.AMT_PAY,0) AS AMT_PAY
|
|
36
|
FROM TR_REQ_PAYMENT_DT A
|
|
37
|
LEFT JOIN TR_REQ_PAYMENT B ON A.PAY_ID = B.REQ_PAY_ID
|
|
38
|
WHERE A.PAY_ID =@p_REQ_PAY_ID
|
|
39
|
|
|
40
|
DECLARE @AMT_PAY DECIMAL(18,2)
|
|
41
|
SELECT TOP 1 @AMT_PAY = ISNULL(A.AMT_PAY,0)
|
|
42
|
FROM TR_REQ_PAYMENT_DT A
|
|
43
|
WHERE A.PAY_ID =@p_REQ_PAY_ID
|
|
44
|
|
|
45
|
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_AMT),0) AS SUM_COST_AMT FROM TR_REQ_PAY_Job_DT A WHERE A.REQ_PAY_ID =@p_REQ_PAY_ID
|
|
46
|
END
|