|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[CM_SUPPLIER_Search]
|
|
3
|
@p_SUP_CODE varchar(15) = NULL,
|
|
4
|
@p_SUP_NAME Nvarchar(300) = NULL,
|
|
5
|
@p_SUP_TYPE_ID varchar(15) = NULL,
|
|
6
|
@p_REGION_ID varchar(15) = NULL,
|
|
7
|
@p_ADDR nvarchar(200) = NULL,
|
|
8
|
@p_EMAIL varchar(50) = NULL,
|
|
9
|
@p_TAX_NO varchar(20) = NULL,
|
|
10
|
@p_TEL varchar(20) = NULL,
|
|
11
|
@p_CONTACT_PERSON nvarchar(200) = NULL,
|
|
12
|
@p_DISCIPLINES NVARCHAR(15) = NULL,
|
|
13
|
@p_NOTES nvarchar(1000) = NULL,
|
|
14
|
@p_RECORD_STATUS varchar(1) = NULL,
|
|
15
|
@p_MAKER_ID varchar(20) = NULL,
|
|
16
|
@p_CREATE_DT VARCHAR(20) = NULL,
|
|
17
|
@p_AUTH_STATUS varchar(50) = NULL,
|
|
18
|
@p_CHECKER_ID varchar(20) = NULL,
|
|
19
|
@p_APPROVE_DT VARCHAR(20) = NULL,
|
|
20
|
@p_TRN_TYPE VARCHAR(50) = NULL,
|
|
21
|
@p_REF_ID VARCHAR(20) = NULL,-----'PYCMS;GOOD_ID'
|
|
22
|
@p_TOTAL_AMT DECIMAL(18,0),
|
|
23
|
@P_TOP INT = 10
|
|
24
|
AS
|
|
25
|
BEGIN
|
|
26
|
------thieuvq -BEGIN 231219------
|
|
27
|
--SELECT * FROM TR_CONTRACT A
|
|
28
|
--INNER JOIN TR_CONTRACT_DT B ON A.CONTRACT_ID = B.CONTRACT_ID AND B.GOODS_ID = ''
|
|
29
|
--SELECT * FROM TR_CONTRACT_DT
|
|
30
|
--SELECT * FROM CM_GOODS
|
|
31
|
----SELECT * FROM wsiSplit('TR_REQ_DOC_RE;GOO000000000452',';') WHERE value LIKE 'GOO%'
|
|
32
|
--SELECT DATEADD(MONTH,3,GETDATE())
|
|
33
|
|
|
34
|
SET @P_TOP =1000
|
|
35
|
|
|
36
|
IF @p_REF_ID <> '' AND @p_REF_ID IS NOT NULL
|
|
37
|
BEGIN
|
|
38
|
|
|
39
|
IF(@p_TRN_TYPE='PO-PYC')
|
|
40
|
BEGIN
|
|
41
|
SELECT A.*,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME, '' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT
|
|
42
|
FROM CM_SUPPLIER A
|
|
43
|
INNER JOIN dbo.TR_REQUEST_DOC_DT TR ON TR.SUP_ID=A.SUP_ID AND TR.REQ_DOC_ID=@p_REF_ID
|
|
44
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
|
|
45
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
|
|
46
|
WHERE 1 = 1
|
|
47
|
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')
|
|
48
|
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')
|
|
49
|
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')
|
|
50
|
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
|
|
51
|
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')
|
|
52
|
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')
|
|
53
|
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')
|
|
54
|
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')
|
|
55
|
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')
|
|
56
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
57
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
58
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
59
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
60
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
61
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
62
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
63
|
AND A.RECORD_STATUS = '1'
|
|
64
|
END
|
|
65
|
---CO THE TRUYEN GOODS_ID XUONG HOAC TRUYEN HANG MUC --> XUONG NAY LAY RA GOODS_ID
|
|
66
|
|
|
67
|
ELSE IF(@p_TOTAL_AMT <= (SELECT LIMIT_VALUE FROM dbo.TL_SYSROLE_LIMIT WHERE ROLE_ID='MSTT'))
|
|
68
|
BEGIN
|
|
69
|
SELECT A.*,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT
|
|
70
|
FROM CM_SUPPLIER A
|
|
71
|
|
|
72
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
|
|
73
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
|
|
74
|
WHERE 1 = 1
|
|
75
|
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')
|
|
76
|
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')
|
|
77
|
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')
|
|
78
|
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
|
|
79
|
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')
|
|
80
|
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')
|
|
81
|
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')
|
|
82
|
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')
|
|
83
|
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')
|
|
84
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
85
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
86
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
87
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
88
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
89
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
90
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
91
|
AND A.RECORD_STATUS = '1'
|
|
92
|
END
|
|
93
|
ELSE
|
|
94
|
SELECT A.*,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT
|
|
95
|
FROM CM_SUPPLIER A
|
|
96
|
LEFT JOIN
|
|
97
|
(
|
|
98
|
SELECT A.SUP_ID FROM TR_CONTRACT A
|
|
99
|
INNER JOIN TR_CONTRACT_DT B ON A.CONTRACT_ID = B.CONTRACT_ID --AND B.GOODS_ID = @GD_ID
|
|
100
|
WHERE B.GOODS_ID = @p_REF_ID AND A.APPROVE_DT IS NOT NULL AND
|
|
101
|
((DATEADD(MONTH,6,A.APPROVE_DT) >= GETDATE() AND (A.REQUEST_ID = '' or A.REQUEST_ID IS NULL)) ---LAY HD KY CACH DAY 6 THANG
|
|
102
|
OR A.REQUEST_ID IS not NULL OR A.REQUEST_ID <> '')---HOP DONG NGUYEN TAC THI KHONG BAT BUOC NHAP SO PHIEU YEU CAU MUA SAM
|
|
103
|
) SUP ON SUP.SUP_ID = A.SUP_ID
|
|
104
|
LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
|
|
105
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
|
|
106
|
WHERE 1 = 1
|
|
107
|
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')
|
|
108
|
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')
|
|
109
|
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')
|
|
110
|
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
|
|
111
|
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')
|
|
112
|
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')
|
|
113
|
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')
|
|
114
|
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')
|
|
115
|
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')
|
|
116
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
117
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
118
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
119
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
120
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
121
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
122
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
123
|
AND A.RECORD_STATUS = '1'
|
|
124
|
END
|
|
125
|
ELSE
|
|
126
|
------END 231219
|
|
127
|
IF(@P_TOP = NULL OR @P_TOP = '' OR @P_TOP = 0)
|
|
128
|
SELECT A.*,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT
|
|
129
|
FROM CM_SUPPLIER A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
|
|
130
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
|
|
131
|
WHERE 1 = 1
|
|
132
|
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')
|
|
133
|
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')
|
|
134
|
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')
|
|
135
|
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
|
|
136
|
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')
|
|
137
|
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')
|
|
138
|
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')
|
|
139
|
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')
|
|
140
|
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')
|
|
141
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
142
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
143
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
144
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
145
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
146
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
147
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
148
|
AND A.RECORD_STATUS = '1'
|
|
149
|
ELSE
|
|
150
|
SELECT TOP(@P_TOP)A.*,B.AUTH_STATUS_NAME,C.CONTENT AS DISCIPLINES_NAME,'' AS TK_GL, '' AS TK_GL_NAME, '' AS TK_GL_OUT, '' AS TK_GL_NAME_OUT
|
|
151
|
FROM CM_SUPPLIER A LEFT JOIN CM_AUTH_STATUS B ON A.AUTH_STATUS = B.AUTH_STATUS
|
|
152
|
LEFT JOIN CM_ALLCODE C ON C.CDVAL = A.DISCIPLINES AND C.CDNAME = 'DISCIPLINES'
|
|
153
|
WHERE 1 = 1
|
|
154
|
AND (A.SUP_CODE like + '%' + @p_SUP_CODE + '%' OR @p_SUP_CODE IS NULL OR @p_SUP_CODE = '')
|
|
155
|
AND (A.SUP_NAME like '%' + @p_SUP_NAME + '%' OR @p_SUP_NAME IS NULL OR @p_SUP_NAME = '')
|
|
156
|
AND (A.SUP_TYPE_ID LIKE '%' + @p_SUP_TYPE_ID OR @p_SUP_TYPE_ID IS NULL OR @p_SUP_TYPE_ID = '')
|
|
157
|
AND (A.REGION_ID LIKE '%' + @p_REGION_ID + '%' OR @p_REGION_ID IS NULL OR @p_REGION_ID = '')
|
|
158
|
AND (A.EMAIL LIKE '%' + @p_EMAIL + '%' OR @p_EMAIL IS NULL OR @p_EMAIL = '')
|
|
159
|
AND (A.TAX_NO LIKE '%' + @p_TAX_NO + '%' OR @p_TAX_NO IS NULL OR @p_TAX_NO = '')
|
|
160
|
AND (A.TEL LIKE '%' + @p_TEL + '%' OR @p_TEL IS NULL OR @p_TEL = '')
|
|
161
|
AND (A.DISCIPLINES LIKE '%' + @p_DISCIPLINES + '%' OR @p_DISCIPLINES IS NULL OR @p_DISCIPLINES = '')
|
|
162
|
AND (A.CONTACT_PERSON LIKE '%' + @p_CONTACT_PERSON + '%' OR @p_CONTACT_PERSON IS NULL OR @p_CREATE_DT = '')
|
|
163
|
AND (A.NOTES LIKE '%' + @p_NOTES + '%' OR @p_NOTES IS NULL OR @p_NOTES = '')
|
|
164
|
AND (A.RECORD_STATUS LIKE '%' + @p_RECORD_STATUS + '%' OR @p_RECORD_STATUS IS NULL OR @p_RECORD_STATUS = '')
|
|
165
|
AND (A.MAKER_ID LIKE '%' + @p_MAKER_ID + '%' OR @p_MAKER_ID IS NULL OR @p_MAKER_ID = '')
|
|
166
|
AND (DATEDIFF(DAY,A.CREATE_DT ,CONVERT(DATETIME, @p_CREATE_DT, 103)) =0 OR @p_CREATE_DT IS NULL OR @p_CREATE_DT = '')
|
|
167
|
AND (A.AUTH_STATUS LIKE '%' + @p_AUTH_STATUS + '%' OR @p_AUTH_STATUS IS NULL OR @p_AUTH_STATUS = '')
|
|
168
|
AND (A.CHECKER_ID LIKE '%' + @p_CHECKER_ID + '%' OR @p_CHECKER_ID IS NULL OR @p_CHECKER_ID = '')
|
|
169
|
AND (DATEDIFF(DAY,A.APPROVE_DT ,CONVERT(DATETIME, @p_APPROVE_DT, 103)) = 0 OR @p_APPROVE_DT IS NULL OR @p_APPROVE_DT = '')
|
|
170
|
AND A.RECORD_STATUS = '1'
|
|
171
|
END
|
|
172
|
--EXEC CM_SUPPLIER_Search '','','','','','','','','','','','','','','','',100
|