|
1
|
ALTER PROCEDURE dbo.PL_TRADEDETAIL_ById_new_V2
|
|
2
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
3
|
@p_DEPT_ID varchar(15) = NULL,
|
|
4
|
@p_GD_NAME NVARCHAR(200)=NULL,
|
|
5
|
@p_GD_CODE VARCHAR(20)= NULL,
|
|
6
|
@p_COST_ID VARCHAR(20) = NULL,
|
|
7
|
@p_PL_TYPE_ID VARCHAR(20) = NULL,
|
|
8
|
@p_GOOD_TYPE_ID VARCHAR(15) = NULL,
|
|
9
|
@p_YEAR VARCHAR(10) = NULL
|
|
10
|
AS
|
|
11
|
BEGIN -- PAGING
|
|
12
|
-- PAGING BEGIN
|
|
13
|
|
|
14
|
SELECT A.TRADE_ID,
|
|
15
|
A.PLAN_ID,
|
|
16
|
A.GOODS_ID,
|
|
17
|
A.GOODS_CODE,
|
|
18
|
A.GOODS_NAME,
|
|
19
|
A.GOODS_TYPE,
|
|
20
|
A.UNIT_ID,
|
|
21
|
A.M1,
|
|
22
|
A.M2,
|
|
23
|
A.M3,
|
|
24
|
A.M4,
|
|
25
|
A.M5,
|
|
26
|
A.M6,
|
|
27
|
A.M7,
|
|
28
|
A.M8,
|
|
29
|
A.M9,
|
|
30
|
A.M10,
|
|
31
|
A.M11,
|
|
32
|
A.M12,
|
|
33
|
ISNULL(A.QUANTITY,0) AS QUANTITY,
|
|
34
|
ISNULL( A.QUANTITY_EXE,0) AS QUANTITY_EXE,
|
|
35
|
A.PRICE,
|
|
36
|
A.START_DT_AMORT,
|
|
37
|
A.MONTH_AMORT,
|
|
38
|
A.END_DT_AMORT,
|
|
39
|
A.RATE_AMORT,
|
|
40
|
A.NOTES,
|
|
41
|
A.RECORD_STATUS,
|
|
42
|
A.MAKER_ID,
|
|
43
|
A.CREATE_DT,
|
|
44
|
A.AUTH_STATUS,
|
|
45
|
A.CHECKER_ID,
|
|
46
|
A.APPROVE_DT,
|
|
47
|
CC.DVDM_NAME AS COST_NAME,
|
|
48
|
PT.PLAN_TYPE_NAME,
|
|
49
|
ISNULL(A.QUANTITY_ETM,0) AS QUANTITY_ETM,
|
|
50
|
ISNULL( A.AMT_APP,0) AS AMT_APP,
|
|
51
|
ISNULL( A.AMT_EXE,0) AS AMT_EXE,
|
|
52
|
ISNULL( A.AMT_ETM,0)
|
|
53
|
+
|
|
54
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
55
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
56
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
57
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
58
|
AND DDT.TRADE_ID = A.TRADE_ID) AS AMT_ETM,
|
|
59
|
ISNULL( A.AMT_TF,0)
|
|
60
|
+
|
|
61
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
62
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
63
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
64
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
65
|
AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_TF,
|
|
66
|
ISNULL( A.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,
|
|
67
|
CG.GD_NAME,CG.GD_CODE,B.UNIT_NAME,
|
|
68
|
ISNULL(A.QUANTITY,0) - ISNULL(A.QUANTITY_EXE,0) AS QUANTITY_REMAIN,
|
|
69
|
ISNULL(A.QUANTITY,0)-ISNULL(A.QUANTITY_ETM,0) AS QUANTITY_REMAIN_ETM,
|
|
70
|
ISNULL(A.AMT_APP,0) + ISNULL( A.AMT_RECEIVE_TF,0)- ISNULL( A.AMT_TF,0)- ISNULL(A.AMT_EXE,0)
|
|
71
|
-
|
|
72
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
73
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
74
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
75
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
76
|
AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_REMAIN,
|
|
77
|
ISNULL(A.AMT_APP,0) + ISNULL( A.AMT_RECEIVE_TF,0)- ISNULL( A.AMT_TF,0)-ISNULL(A.AMT_ETM,0)
|
|
78
|
-
|
|
79
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
80
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
81
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
82
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
83
|
AND DDT.TRADE_ID = A.TRADE_ID)
|
|
84
|
-
|
|
85
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
86
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
87
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
88
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
89
|
AND DDT.FR_TRADE_ID = A.TRADE_ID) AS AMT_REMAIN_ETM
|
|
90
|
-- SELECT END
|
|
91
|
|
|
92
|
FROM PL_TRADEDETAIL A
|
|
93
|
LEFT JOIN PL_MASTER PLM ON PLM.PLAN_ID = A.PLAN_ID
|
|
94
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=A.GOODS_ID
|
|
95
|
LEFT JOIN CM_UNIT B ON CG.UNIT_ID = B.UNIT_ID
|
|
96
|
LEFT JOIN dbo.CM_DVDM CC ON CC.DVDM_ID=PLM.COST_ID
|
|
97
|
LEFT JOIN dbo.CM_PLAN_TYPE PT ON PT.PLAN_TYPE_ID=PLM.PLAN_TYPE_ID
|
|
98
|
WHERE PLM.BRANCH_ID = @p_BRANCH_ID AND PLM.DEPT_ID = @p_DEPT_ID
|
|
99
|
AND (CG.GD_NAME LIKE N'%' + @p_GD_NAME +N'%' OR @p_GD_NAME='' OR @p_GD_NAME IS NULL)
|
|
100
|
AND (CG.GD_CODE LIKE N'%' + @p_GD_CODE +N'%' OR @p_GD_CODE='' OR @p_GD_CODE IS NULL)
|
|
101
|
AND (PLM.COST_ID = @p_COST_ID OR @p_COST_ID IS NULL OR @p_COST_ID = '')
|
|
102
|
AND (PLM.PLAN_TYPE_ID = @p_PL_TYPE_ID OR @p_PL_TYPE_ID IS NULL OR @p_PL_TYPE_ID = '')
|
|
103
|
AND (CG.GD_TYPE_ID = @p_GOOD_TYPE_ID OR @p_GOOD_TYPE_ID IS NULL OR @p_GOOD_TYPE_ID = '')
|
|
104
|
AND CG.RECORD_STATUS ='1' ---2022912 KHI XEM CHI TIET KE HOACH, CHI LOAD NHUNG NGAN SACH CO RECORD_STATUS =1
|
|
105
|
AND (PLM.YEAR = @p_YEAR OR @p_YEAR IS NULL OR @p_YEAR = '')
|
|
106
|
-- PAGING END
|
|
107
|
|
|
108
|
END -- PAGING
|