|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[TR_PO_GOODS_Search_v2]
|
|
3
|
@P_PO_TYPE INT = NULL,
|
|
4
|
@p_GD_CODE varchar(20) = NULL,
|
|
5
|
@p_GD_NAME nvarchar(200) = NULL,
|
|
6
|
@p_GD_TYPE_ID varchar(20) = NULL,
|
|
7
|
@p_GD_GROUP_ID VARCHAR(20) =NULL,
|
|
8
|
@p_DESCRIPTION nvarchar(2000) = NULL,
|
|
9
|
@p_SUP_ID varchar(15) = NULL,
|
|
10
|
@p_PRICE decimal(18) = NULL,
|
|
11
|
@p_UNIT_ID varchar(15) = NULL,
|
|
12
|
@P_PLAN_ID NVARCHAR(15) = NULL,
|
|
13
|
@P_CONTRACT_ID NVARCHAR(15) = NULL,
|
|
14
|
@P_CONTRACT_CODE NVARCHAR(15) = NULL,
|
|
15
|
@P_CONTRACT_NAME NVARCHAR(100) = NULL,
|
|
16
|
@p_TR_REQ_ID varchar(20) = NULL,
|
|
17
|
@P_TOP INT = null
|
|
18
|
AS
|
|
19
|
BEGIN -- PAGING
|
|
20
|
DECLARE @PLAN_CODE VARCHAR(15) = (SELECT PLAN_CODE FROM PL_MASTER WHERE PLAN_ID = @P_PLAN_ID)
|
|
21
|
DECLARE @PLAN_NAME NVARCHAR(1000) = (SELECT PLAN_NAME FROM PL_MASTER WHERE PLAN_ID = @P_PLAN_ID)
|
|
22
|
DECLARE @BRANCHCODE VARCHAR(15) = (SELECT BRANCH_CODE FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
|
|
23
|
ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)
|
|
24
|
DECLARE @BRANCHNAME NVARCHAR(1000) = (SELECT BRANCH_NAME FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
|
|
25
|
ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)
|
|
26
|
DECLARE @BRANCH_ADDR NVARCHAR(1000) = (SELECT ADDR FROM PL_MASTER T1 INNER JOIN CM_BRANCH T2
|
|
27
|
ON T1.BRANCH_ID = T2.BRANCH_ID WHERE T1.PLAN_ID = @P_PLAN_ID)
|
|
28
|
DECLARE @BRANCHID VARCHAR(100) = (SELECT T1.BRANCH_ID FROM PL_MASTER T1 WHERE T1.PLAN_ID = @P_PLAN_ID)
|
|
29
|
DECLARE @l_CONTRACT_DESC NVARCHAR(max)='',
|
|
30
|
@l_CONTRACT_ID VARCHAR(15)='', @l_CONTRACT_CODE VARCHAR(15)='', @l_CONTRACT_NAME NVARCHAR(200)='',
|
|
31
|
@l_TRADE_ID VARCHAR(15)='', @l_QUANTITY numeric(18,0) = '1', @l_QUANTITY_EXE decimal(18,0), @l_QUANTITY_REMAIN decimal(18,0)
|
|
32
|
|
|
33
|
SET @l_QUANTITY_EXE = 0
|
|
34
|
SET @l_QUANTITY_REMAIN = 0
|
|
35
|
--- IF TOP = 3 TIM KIEM HANG HOA PO - HOP DONG
|
|
36
|
IF(@p_TR_REQ_ID IS NOT NULL AND @p_TR_REQ_ID <> '' )--Trong ke hoach - lAY TRONG PLAN_TRADEDETAIL
|
|
37
|
BEGIN
|
|
38
|
-- PAGING BEGIN
|
|
39
|
SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
|
|
40
|
H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],X.DESCRIPTION AS [DESCRIPTION],
|
|
41
|
H.SUP_ID AS [SUP_ID], X.REQDT_ID, X.QUANTITY AS QUANTITY,X.PRICE AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
|
|
42
|
,F.PLAN_ID AS PLAN_ID,F.PLAN_CODE AS PLAN_CODE,F.PLAN_NAME AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
|
|
43
|
@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE, HH.HH_NAME AS GD_NAME_REAL,
|
|
44
|
@l_CONTRACT_NAME AS [CONTRACT_NAME]
|
|
45
|
, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
|
|
46
|
'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
|
|
47
|
E.BRANCH_ID AS BRANCH_ID,E.BRANCH_CODE AS BRANCH_CODE, E.BRANCH_NAME AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
|
|
48
|
,H.RECORD_STATUS, H.HH_ID,X.HANGHOA_ID,(X.QUANTITY*X.PRICE) as TOTAL_AMT
|
|
49
|
-- SELECT END
|
|
50
|
FROM TR_REQUEST_DOC_DT X
|
|
51
|
--LEFT JOIN CM_GOODS A ON A.GD_ID = B.GOODS_ID
|
|
52
|
|
|
53
|
--LEFT JOIN PL_REQUEST_DOC D ON X.PL_REQDT_ID
|
|
54
|
LEFT JOIN PL_REQUEST_DOC_DT PT ON X.PL_REQDT_ID= PT.REQDT_ID
|
|
55
|
LEFT JOIN TR_REQUEST_DOC_PL_DT PLDT ON PLDT.REQPL_DT_ID = X.REQPL_DT_ID
|
|
56
|
LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = PLDT.HH_ID
|
|
57
|
LEFT JOIN PL_TRADEDETAIL B ON PT.TRADE_ID = B.TRADE_ID
|
|
58
|
LEFT JOIN PL_MASTER F ON F.PLAN_ID = B.PLAN_ID
|
|
59
|
LEFT JOIN CM_BRANCH E ON E.BRANCH_ID = F.BRANCH_ID
|
|
60
|
LEFT JOIN CM_HANGHOA H ON H.HH_ID = X.HANGHOA_ID
|
|
61
|
LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID
|
|
62
|
LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID
|
|
63
|
LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID
|
|
64
|
WHERE 1 = 1
|
|
65
|
AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
|
|
66
|
AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
|
|
67
|
AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
|
|
68
|
AND (X.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
69
|
AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
|
|
70
|
AND (X.REQ_DOC_ID = @p_TR_REQ_ID OR @p_TR_REQ_ID IS NULL OR @p_TR_REQ_ID = '')
|
|
71
|
AND (X.TRAN_TYPE_ID NOT IN (SELECT TRN_TYPE FROM dbo.CM_TRAN_TYPE WHERE NOTES='XK') OR X.TRAN_TYPE_ID IS NULL OR X.TRAN_TYPE_ID='')
|
|
72
|
--AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
|
|
73
|
|
|
74
|
--select * from CM_HANGHOA
|
|
75
|
--AND (B.PLAN_ID = @P_PLAN_ID OR @P_PLAN_ID IS NULL OR @P_PLAN_ID = '')
|
|
76
|
--[TR_PO_GOODS_Search_v2]
|
|
77
|
--RETURN
|
|
78
|
-- PAGING END
|
|
79
|
END
|
|
80
|
IF(@P_CONTRACT_ID IS NOT NULL AND @P_CONTRACT_ID <>'')
|
|
81
|
BEGIN
|
|
82
|
-- PAGING BEGIN
|
|
83
|
SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
|
|
84
|
H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],H.DESCRIPTION AS [DESCRIPTION],
|
|
85
|
H.SUP_ID AS [SUP_ID],X.QUANTITY AS QUANTITY,X.PRICE AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
|
|
86
|
,'' AS PLAN_ID,'' AS PLAN_CODE,'' AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
|
|
87
|
@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE,
|
|
88
|
@l_CONTRACT_NAME AS [CONTRACT_NAME]
|
|
89
|
, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
|
|
90
|
'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
|
|
91
|
'' AS BRANCH_ID,'' AS BRANCH_CODE, '' AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME
|
|
92
|
,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
|
|
93
|
--E.BRANCH_ID, E.BRANCH_CODE, E.BRANCH_NAME, @BRANCH_ADDR AS R_ADDR
|
|
94
|
-- SELECT END
|
|
95
|
FROM dbo.TR_CONTRACT_DT X
|
|
96
|
LEFT JOIN CM_HANGHOA H ON H.HH_ID = X.GOODS_ID
|
|
97
|
LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID
|
|
98
|
LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID
|
|
99
|
LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID
|
|
100
|
-- SELECT END
|
|
101
|
WHERE 1 = 1
|
|
102
|
AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
|
|
103
|
AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
|
|
104
|
AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
|
|
105
|
--AND (X LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
106
|
AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
|
|
107
|
AND (X.CONTRACT_ID = @P_CONTRACT_ID OR @P_CONTRACT_ID IS NULL OR @P_CONTRACT_ID = '')
|
|
108
|
AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
|
|
109
|
-- PAGING END
|
|
110
|
--RETURN
|
|
111
|
END
|
|
112
|
ELSE IF(@p_TR_REQ_ID IS NULL OR @p_TR_REQ_ID = '' )--Trong ke hoach - lAY TRONG PLAN_TRADEDETAIL
|
|
113
|
BEGIN
|
|
114
|
-- PAGING BEGIN
|
|
115
|
SELECT H.HH_ID AS [GD_ID],H.HH_CODE AS [GD_CODE], H.HH_NAME [GD_NAME], H.HH_TYPE_ID AS [GD_TYPE_ID],
|
|
116
|
H.USE_BRANCH AS [USE_BRANCH],ISNULL(H.AMORT_RATE,0) AS [AMORT_RATE],H.DESCRIPTION AS [DESCRIPTION],
|
|
117
|
H.SUP_ID AS [SUP_ID],NULL AS QUANTITY,NULL AS PRICE ,@l_CONTRACT_DESC AS [CONTRAC_DESC], '' TRADE_ID
|
|
118
|
,'' AS PLAN_ID,'' AS PLAN_CODE,''AS PLAN_NAME,D.UNIT_ID AS UNIT_ID, D.UNIT_CODE AS UNIT_CODE, D.UNIT_NAME AS UNIT_NAME,
|
|
119
|
@l_CONTRACT_ID AS CONTRACT_ID,@l_CONTRACT_CODE AS CONTRACT_CODE,
|
|
120
|
@l_CONTRACT_NAME AS [CONTRACT_NAME]
|
|
121
|
, 0.0 AS QUANTITY_EXE, 0.0 AS QUANTITY_REMAIN, --THIEUVQ THEM 22/09/2014
|
|
122
|
'' AS CD_ID, --THIEUVQ LAY CHI TIET HOP DONG 14092016
|
|
123
|
'' AS BRANCH_ID,'' AS BRANCH_CODE,'' AS BRANCH_NAME, @BRANCH_ADDR AS R_ADDR, TY.HH_TYPE_CODE, TY.HH_TYPE_NAME
|
|
124
|
,HG.HH_GROUP_ID,HG.HH_GROUP_CODE,HG.HH_GROUP_NAME
|
|
125
|
--E.BRANCH_ID, E.BRANCH_CODE, E.BRANCH_NAME, @BRANCH_ADDR AS R_ADDR
|
|
126
|
-- SELECT END
|
|
127
|
FROM CM_HANGHOA H
|
|
128
|
LEFT JOIN CM_HANGHOA_TYPE TY ON H.HH_TYPE_ID = TY.HH_TYPE_ID
|
|
129
|
LEFT JOIN dbo.CM_HANGHOA_GROUP HG ON HG.HH_GROUP_ID=TY.HH_GROUP_ID
|
|
130
|
LEFT JOIN CM_UNIT D ON H.UNIT_ID = D.UNIT_ID
|
|
131
|
WHERE 1 = 1
|
|
132
|
AND (H.HH_CODE LIKE '%' + @p_GD_CODE + '%' OR @p_GD_CODE IS NULL OR @p_GD_CODE = '')
|
|
133
|
AND (H.HH_NAME LIKE '%' + @p_GD_NAME + '%' OR @p_GD_NAME IS NULL OR @p_GD_NAME = '')
|
|
134
|
AND (H.HH_TYPE_ID LIKE '%' + @p_GD_TYPE_ID + '%' OR @p_GD_TYPE_ID IS NULL OR @p_GD_TYPE_ID = '')
|
|
135
|
AND (H.SUP_ID LIKE '%' + @p_SUP_ID + '%' OR @p_SUP_ID IS NULL OR @p_SUP_ID = '')
|
|
136
|
AND (HG.HH_GROUP_ID LIKE '%' + @p_GD_GROUP_ID + '%' OR @p_GD_GROUP_ID IS NULL OR @p_GD_GROUP_ID = '')
|
|
137
|
AND (H.RECORD_STATUS ='1' AND H.AUTH_STATUS ='A')
|
|
138
|
-- PAGING END
|
|
139
|
--RETURN
|
|
140
|
END
|
|
141
|
|
|
142
|
END -- PAGING
|
|
143
|
--EXEC CM_GOODS_Search '','','','','',0,'','','','','','','','',100s
|