|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[MW_TRANSFER_CONFIRM_Search]
|
|
3
|
@p_TRANSFER_ID VARCHAR(15) = NULL, @p_TRN_DATE VARCHAR(20) = NULL, @p_TRN_TIME VARCHAR(50) = NULL, @p_QTY INT = NULL, @p_PRICE NUMERIC(18, 0) = NULL, @p_TOTAL_AMT NUMERIC(18, 2) = NULL,
|
|
4
|
@p_NOTES NVARCHAR(1000) = NULL, @p_AUTH_STATUS VARCHAR(1) = NULL, @p_MAKER_ID VARCHAR(15) = NULL, @p_CREATE_DT VARCHAR(20) = NULL, @p_CHECKER_ID VARCHAR(15) = NULL,
|
|
5
|
@p_APPROVE_DT VARCHAR(20) = NULL, @p_KT_AUTH_STATUS VARCHAR(1) = NULL, @p_KT_MAKER_ID VARCHAR(15) = NULL, @p_KT_CREATE_DT VARCHAR(20) = NULL, @p_KT_CHECKER_ID VARCHAR(15) = NULL,
|
|
6
|
@p_KT_APPROVE_DT VARCHAR(20) = NULL, @p_XmlData XML = NULL, @p_MAKER_LOGIN VARCHAR(15) = NULL, @p_LEVEL VARCHAR(50) = 'UNIT', @p_TOP INT = 10, @p_FROM_DATE VARCHAR(20) = NULL,
|
|
7
|
@p_TO_DATE VARCHAR(20) = NULL, @p_IS_UPDATE VARCHAR(1) = 'N', @p_BRANCH_CREATE varchar(15) = NULL, @p_WARE_HOUSE VARCHAR(15) = NULL,@p_TRANSFER_CODE NVARCHAR(30) = null
|
|
8
|
AS
|
|
9
|
BEGIN
|
|
10
|
--Luctv Bo Sung Doan Code Doc Du Lieu Bo Qua Commit Transaction
|
|
11
|
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
|
|
12
|
SELECT A.*, B.MAKER_ID, CON.MAKER_ID AS KT_MAKER_ID, B.CREATE_DT, B.KT_CREATE_DT,B.CHECKER_ID, B.KT_CHECKER_ID, B.APPROVE_DT, B.KT_APPROVE_DT,
|
|
13
|
B.AUTH_STATUS, B.KT_AUTH_STATUS, B.NOTES, B.CORE_NOTE,B.TRN_DATE, B.TRN_TIME, DP.DEP_CODE, DP.DEP_NAME, BR.BRANCH_CODE, BR.BRANCH_NAME,
|
|
14
|
AU.AUTH_STATUS_NAME AS AUTH_STATUS_NAME, TL.TLFullName AS CREATE_FULL_NAME, TL1.TLFullName AS KT_CREATE_FULL_NAME, (CASE WHEN CON.CHECKER_ID IS NULL THEN 'U' ELSE 'A' END) AS IS_RECIVE,
|
|
15
|
TL2.TLFullName AS APPROVE_NAME, TL1.TLFullName KT_APPROVE_NAME, B.WARE_HOUSE, AL.CONTENT WARE_HOUSE_NAME,CON.CHECKER_ID CONF_CHECKER_ID, CON.CREATE_DT CONF_CREATE_DT,
|
|
16
|
CON.MAKER_ID CONF_MAKER_ID,TL4.TLFullName CONFIRM_NAME, (CASE WHEN CON.CHECKER_ID IS NULL THEN N'Chưa xác nhận' ELSE N'Chưa xác nhận' END) AS RECIVE_NAME,B.TRANSFER_CODE
|
|
17
|
FROM MW_TRANSFER B
|
|
18
|
--INNER JOIN MW_TRANSFER_DT A ON A.TRANSFER_ID = B.TRANSFER_ID AND A.TO_BRN_ID = @p_BRANCH_CREATE : CHO NAY SHOW DATA TREN GIAO DIEN BI TRUNG NEN LUCTV THAY BANG DOAN CODE BEN DUOI
|
|
19
|
INNER JOIN
|
|
20
|
(
|
|
21
|
SELECT TRANSFER_ID, TO_BRN_ID, SUM(QTY) QTY,SUM(TOTAL_AMT)TOTAL_AMT
|
|
22
|
FROM MW_TRANSFER_DT
|
|
23
|
WHERE TO_BRN_ID =@p_BRANCH_CREATE
|
|
24
|
GROUP BY TRANSFER_ID, TO_BRN_ID
|
|
25
|
) A ON A.TRANSFER_ID = B.TRANSFER_ID
|
|
26
|
LEFT JOIN MW_TRANSFER_CONF CON ON CON.TRANSFER_ID = B.TRANSFER_ID AND CON.BRANCH_RECIVE = @p_BRANCH_CREATE
|
|
27
|
LEFT JOIN CM_DEPARTMENT DP ON B.FR_DEPT_ID = DP.DEP_ID
|
|
28
|
LEFT JOIN CM_BRANCH BR ON B.BRANCH_CREATE = BR.BRANCH_ID
|
|
29
|
LEFT JOIN CM_AUTH_STATUS AU ON B.KT_AUTH_STATUS = AU.AUTH_STATUS
|
|
30
|
LEFT JOIN TL_USER TL ON B.MAKER_ID = TL.TLNANME
|
|
31
|
LEFT JOIN TL_USER TL1 ON CON.MAKER_ID = TL1.TLNANME
|
|
32
|
LEFT JOIN TL_USER TL2 ON B.CHECKER_ID = TL2.TLNANME
|
|
33
|
LEFT JOIN TL_USER TL3 ON B.KT_CHECKER_ID = TL3.TLNANME
|
|
34
|
LEFT JOIN TL_USER TL4 ON CON.MAKER_ID = TL4.TLNANME
|
|
35
|
LEFT JOIN CM_ALLCODE AL ON B.WARE_HOUSE = AL.CDVAL AND AL.CDNAME='WARE_HOUSE_IN'
|
|
36
|
WHERE 1 = 1
|
|
37
|
AND (CONVERT (DATE, B.TRN_DATE, 103) >= CONVERT (DATE, @p_FROM_DATE, 103) OR @p_FROM_DATE IS NULL OR @p_FROM_DATE = '')
|
|
38
|
AND (CONVERT (DATE, B.TRN_DATE, 103) <= CONVERT (DATE, @p_TO_DATE, 103) OR @p_TO_DATE IS NULL OR @p_TO_DATE = '')
|
|
39
|
AND B.RECORD_STATUS='1'
|
|
40
|
AND (B.WARE_HOUSE= @p_WARE_HOUSE OR @p_WARE_HOUSE ='' OR @p_WARE_HOUSE IS NULL)
|
|
41
|
AND B.AUTH_STATUS='A'
|
|
42
|
AND B.KT_AUTH_STATUS ='A'
|
|
43
|
AND ((@p_IS_UPDATE = 'Y' AND CON.CHECKER_ID IS NOT NULL) OR (@p_IS_UPDATE = 'N' AND CON.CHECKER_ID IS NULL) OR @p_IS_UPDATE = '' OR @p_IS_UPDATE IS NULL)
|
|
44
|
ORDER BY B.CREATE_DT DESC;
|
|
45
|
END
|
|
46
|
|
|
47
|
|
|
48
|
|