|
1
|
ALTER PROCEDURE dbo.TR_REQUEST_SHOP_DOC_DT_ById
|
|
2
|
@p_REQ_ID varchar(15),
|
|
3
|
@BRANCH_LOGIN varchar(15) = NULL,
|
|
4
|
@ASSET_TYPE varchar(15) = NULL
|
|
5
|
AS
|
|
6
|
DECLARE @l_CCLD_VALUE decimal(18,0) = 0
|
|
7
|
SET @l_CCLD_VALUE = (SELECT ParaValue FROM SYS_PARAMETERS WHERE ParaKey = 'ASSET_VALUE')
|
|
8
|
IF @@ERROR <> 0 SET @l_CCLD_VALUE = 0
|
|
9
|
SELECT
|
|
10
|
A.REQDT_ID,
|
|
11
|
A.REQ_DOC_ID,
|
|
12
|
A.PLAN_ID,
|
|
13
|
A.TRADE_ID,
|
|
14
|
A.ASS_GROUP_ID,
|
|
15
|
A.DESCRIPTION,
|
|
16
|
A.REASON,
|
|
17
|
A.UNIT_ID,
|
|
18
|
A.QUANTITY,
|
|
19
|
A.PRICE,
|
|
20
|
A.TOTAL_AMT,
|
|
21
|
A.NOTES,
|
|
22
|
A.RECEIVE_BRANCH,
|
|
23
|
A.RECEIVE_ADDR,
|
|
24
|
A.RECEIVE_PERSON,
|
|
25
|
A.RECEIVE_TEL,
|
|
26
|
A.RECORD_STATUS,
|
|
27
|
A.MAKER_ID,
|
|
28
|
A.CREATE_DT,
|
|
29
|
A.AUTH_STATUS,
|
|
30
|
A.CHECKER_ID,
|
|
31
|
A.APPROVE_DT,
|
|
32
|
A.NOTES_DVCM,
|
|
33
|
A.QUANTITY_USE,
|
|
34
|
A.RECEIVE_SUBBRANCH,
|
|
35
|
A.RECEIVE_DEP,
|
|
36
|
A.RECEIVE_EMAIL,
|
|
37
|
A.REQ_DT_TYPE,A.PRICE AS PRICE_D,E.BRANCH_CODE, E.BRANCH_ID,E.BRANCH_NAME, C.UNIT_CODE, C.UNIT_ID, C.UNIT_NAME, F.PLAN_CODE, F.PLAN_NAME,
|
|
38
|
G.BRANCH_CODE AS R_BRANCH_CODE, G.BRANCH_NAME AS R_BRANCH_NAME,A.QTY_ETM,EMP.EMP_CODE, EMP.EMP_ID,EMP.EMP_NAME,
|
|
39
|
AG.GROUP_CODE AS ASS_GROUP_CODE,
|
|
40
|
AG.GROUP_NAME AS ASS_GROUP_NAME,
|
|
41
|
|
|
42
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_ID ELSE CBE.BRANCH_ID END AS BRANCH_OLD,
|
|
43
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_CODE ELSE CBE.BRANCH_CODE END AS BRANCH_OLD_CODE,
|
|
44
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN E.BRANCH_NAME ELSE CBE.BRANCH_NAME END AS BRANCH_OLD_NAME,
|
|
45
|
|
|
46
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_ID ELSE CDE.DEP_ID END AS DEP_OLD,
|
|
47
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_CODE ELSE CDE.DEP_CODE END AS DEP_OLD_CODE,
|
|
48
|
CASE WHEN A.EMP_ID IS NULL OR A.EMP_ID = '' THEN CDR.DEP_NAME ELSE CDE.DEP_NAME END AS DEP_OLD_NAME,
|
|
49
|
--CBE.BRANCH_ID AS BRANCH_OLD, CBE.BRANCH_CODE AS BRANCH_OLD_CODE, CBE.BRANCH_NAME AS BRANCH_OLD_NAME,
|
|
50
|
CDE.DEP_ID AS DEP_OLD, CDE.DEP_CODE AS DEP_OLD_CODE, CDE.DEP_NAME AS DEP_OLD_NAME,
|
|
51
|
|
|
52
|
CD.DEP_CODE AS R_DEP_CODE,CD.DEP_NAME AS R_DEP_NAME, AG.DVCM_ID AS ID_DVCM,
|
|
53
|
R.REQ_CODE, R.REQ_NAME, A.ALLOCATED,
|
|
54
|
(CASE
|
|
55
|
WHEN A.REQ_DT_TYPE = 'BUYNEW' THEN N'Mua mới'
|
|
56
|
WHEN A.TYPE_XL = 'CPTK' THEN N'Xuất kho'
|
|
57
|
WHEN A.TYPE_XL = 'CPDC' THEN N'Tận dụng/Điều chuyển'
|
|
58
|
END) AS REQ_DT_TYPE_NAME
|
|
59
|
FROM TR_REQUEST_SHOP_DOC_DT A
|
|
60
|
LEFT JOIN CM_UNIT C On A.UNIT_ID=C.UNIT_ID
|
|
61
|
LEFT JOIN dbo.TR_REQUEST_SHOP_DOC R ON R.REQ_ID=A.REQ_DOC_ID
|
|
62
|
LEFT JOIN PL_MASTER F ON F.PLAN_ID = A.PLAN_ID
|
|
63
|
LEFT JOIN CM_BRANCH E ON E.BRANCH_ID = R.BRANCH_ID
|
|
64
|
LEFT JOIN dbo.CM_DEPARTMENT CDR ON CDR.DEP_ID=R.DEP_ID
|
|
65
|
LEFT JOIN CM_BRANCH G ON G.BRANCH_ID = A.RECEIVE_BRANCH
|
|
66
|
LEFT JOIN CM_EMPLOYEE EMP ON EMP.EMP_ID = A.EMP_ID
|
|
67
|
LEFT JOIN CM_BRANCH CBE ON CBE.BRANCH_ID = EMP.BRANCH_ID
|
|
68
|
LEFT JOIN dbo.CM_DEPARTMENT CDE ON CDE.DEP_ID=EMP.DEP_ID
|
|
69
|
LEFT JOIN ASS_GROUP AG ON A.ASS_GROUP_ID = AG.GROUP_ID
|
|
70
|
LEFT JOIN dbo.CM_DEPARTMENT CK ON CK.DEP_ID=A.RECEIVE_SUBBRANCH AND CK.KHOI_ID = 'K'
|
|
71
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=A.RECEIVE_SUBBRANCH
|
|
72
|
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=A.RECEIVE_DEP
|
|
73
|
|
|
74
|
WHERE A.REQ_DOC_ID=@p_REQ_ID AND (A.RECEIVE_BRANCH = @BRANCH_LOGIN OR @BRANCH_LOGIN IS NULL OR @BRANCH_LOGIN = '')
|
|
75
|
--AND ((@ASSET_TYPE = 'TSCD' AND A.PRICE >= @l_CCLD_VALUE) OR (@ASSET_TYPE = 'CCLD' AND A.PRICE < @l_CCLD_VALUE) OR @ASSET_TYPE IS NULL OR @ASSET_TYPE = '')
|
|
76
|
|
|
77
|
ORDER BY A.CREATE_DT DESC
|