|
1
|
|
|
2
|
ALTER PROCEDURE [dbo].[rpt_TR_REQ_DOC_Inventory]
|
|
3
|
@p_REQ_PAY_CODE varchar(50) = NULL,
|
|
4
|
@p_REQ_TYPE varchar(15) = NULL,
|
|
5
|
@p_FromDate VARCHAR(20) = NULL,
|
|
6
|
@p_ToDate VARCHAR(20) = NULL,
|
|
7
|
----@p_PO_CODE varchar(15) = NULL,
|
|
8
|
--@p_REQ_REASON nvarchar(MAX) = NULL,
|
|
9
|
--@p_BRANCH_ID varchar(15) = NULL,
|
|
10
|
--@p_DEP_ID varchar(15) = NULL,
|
|
11
|
@p_BRANCH_ID varchar(15) = NULL,
|
|
12
|
@p_LEVEL varchar(10) = NULL,
|
|
13
|
@p_BRANCH_CREATE varchar(15) = NULL,
|
|
14
|
@p_REF_ID varchar(15) = NULL,
|
|
15
|
@p_BRANCH_LOGIN VARCHAR(15) = NULL,
|
|
16
|
@p_USER_LOGIN VARCHAR(15)= NULL,
|
|
17
|
@p_SO_TO_TRINH VARCHAR(15) = NULL
|
|
18
|
AS
|
|
19
|
|
|
20
|
declare @tmp table(BRANCH_ID varchar(15))
|
|
21
|
insert into @tmp SELECT BRANCH_ID FROM [dbo].[CM_BRANCH_GETCHILDID](@p_BRANCH_LOGIN)
|
|
22
|
DECLARE @DEP_ID_LOGIN VARCHAR(15), @BRANCH_TYPE_LOGIN VARCHAR(15), @ROLE_USER_LOGIN VARCHAR(15)
|
|
23
|
SET @DEP_ID_LOGIN=(SELECT SECUR_CODE FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
24
|
SET @BRANCH_TYPE_LOGIN=(SELECT BRANCH_TYPE FROM CM_BRANCH WHERE BRANCH_ID =@p_BRANCH_LOGIN)
|
|
25
|
SET @ROLE_USER_LOGIN = (SELECT RoleName FROM TL_USER WHERE TLNANME =@p_USER_LOGIN)
|
|
26
|
BEGIN
|
|
27
|
|
|
28
|
|
|
29
|
SELECT
|
|
30
|
ROW_NUMBER() OVER (ORDER BY A.REQ_ID DESC) AS STT,
|
|
31
|
U.TLFullName AS NV_MS,
|
|
32
|
A.REQ_CODE AS SP_MS,
|
|
33
|
A.CREATE_DT AS NHANPHIEU,
|
|
34
|
A.APPROVE_DT AS DUYETPHIEU,
|
|
35
|
-------
|
|
36
|
CMS.DMMS_NAME AS DV_YC_MS,
|
|
37
|
PLRD.REQ_CODE AS SO_TTCT,
|
|
38
|
PLRD.REQ_NAME AS TEN_TTCT,
|
|
39
|
PLRD.REQ_CONTENT AS ND_HH,
|
|
40
|
--------------------
|
|
41
|
DT.QUANTITY AS SL,
|
|
42
|
UN.UNIT_NAME AS DVT,
|
|
43
|
DT.PRICE_ETM AS DG_VAT,
|
|
44
|
DT.TOTAL_AMT_ETM AS THANH_TIEN_VAT,
|
|
45
|
DT.TOTAL_AMT AS CP_MSTT,
|
|
46
|
ABS(DT.TOTAL_AMT - DT.TOTAL_AMT_ETM) AS CP_MSTK,
|
|
47
|
S.SUP_NAME AS NCC,
|
|
48
|
THH.HH_TYPE_NAME AS LOAI_HH,
|
|
49
|
HH.HH_NAME AS TEN_HH,
|
|
50
|
CT.CONTRACT_NAME AS LOAI_HD,
|
|
51
|
'' AS NGAY_TT_DOT1,
|
|
52
|
'' AS TT_DOT1,
|
|
53
|
'' AS NGAY_TT_DOT2,
|
|
54
|
'' AS TT_DOT2,
|
|
55
|
'' AS NGAY_TT_DOT3,
|
|
56
|
'' AS TT_DOT3,
|
|
57
|
'' AS TIENDO_TT,
|
|
58
|
'' AS CP_BL_BH,
|
|
59
|
'' AS FROMDATE,
|
|
60
|
'' AS TODATE
|
|
61
|
FROM TR_REQUEST_DOC A
|
|
62
|
LEFT JOIN PL_REQUEST_DOC PLRD ON A.PL_REQ_ID=PLRD.REQ_ID
|
|
63
|
LEFT JOIN
|
|
64
|
(
|
|
65
|
SELECT DMMS_ID,DEP_NAME+'-'+BRANCH_NAME AS DMMS_NAME FROM
|
|
66
|
dbo.CM_DMMS
|
|
67
|
LEFT JOIN dbo.CM_BRANCH ON CM_BRANCH.BRANCH_ID=CM_DMMS.BRANCH_ID
|
|
68
|
LEFT JOIN dbo.CM_DEPARTMENT ON CM_DEPARTMENT.DEP_ID = CM_DMMS.DEP_ID
|
|
69
|
UNION ALL
|
|
70
|
SELECT DVDM_ID AS DMMS_ID ,DVDM_NAME AS DMMS_NAME
|
|
71
|
FROM dbo.CM_DVDM
|
|
72
|
)CMS ON CMS.DMMS_ID=A.DMMS_ID
|
|
73
|
LEFT JOIN TL_USER U ON U.TLNANME = A.USER_DVMS
|
|
74
|
LEFT JOIN TR_REQUEST_DOC_DT DT ON A.REQ_ID = DT.REQ_DOC_ID
|
|
75
|
LEFT JOIN CM_SUPPLIER S ON S.SUP_ID = DT.SUP_ID
|
|
76
|
LEFT JOIN CM_HANGHOA HH ON HH.HH_ID = DT.HANGHOA_ID
|
|
77
|
LEFT JOIN CM_HANGHOA_TYPE THH ON THH.HH_TYPE_ID = HH.HH_TYPE_ID
|
|
78
|
LEFT JOIN CM_UNIT UN ON UN.UNIT_ID = HH.UNIT_ID
|
|
79
|
LEFT JOIN TR_CONTRACT CT ON CT.REQ_DOC_ID = A.REQ_ID
|
|
80
|
--LEFT JOIN TR_CONTRACT_PAYMENT P ON P.CONTRACT_ID = CT.CONTRACT_ID
|
|
81
|
WHERE 1=1
|
|
82
|
--Thiếu những field không biết lấy như nào nên em để lại
|
|
83
|
--AND(A.REQ_CODE LIKE N'%'+@p_REQ_PAY_CODE+'%' or @p_REQ_PAY_CODE='' OR @p_REQ_PAY_CODE IS NULL)
|
|
84
|
AND(A.REQ_TYPE LIKE N'%'+@p_REQ_TYPE+'%' or @p_REQ_TYPE='' OR @p_REQ_TYPE IS NULL)
|
|
85
|
--AND CONVERT(DATE, A.CREATE_DT, 103) >= CONVERT(DATE, @p_FromDate, 103)
|
|
86
|
--AND CONVERT(DATE, A.CREATE_DT, 103) <= CONVERT(DATE, @p_ToDate, 103)
|
|
87
|
AND((@p_LEVEL='ALL' AND A.BRANCH_CREATE IN(SELECT BRANCH_ID FROM @tmp))
|
|
88
|
OR((@p_LEVEL='UNIT' AND A.BRANCH_CREATE=@p_BRANCH_ID)OR(@p_BRANCH_ID='' OR @p_BRANCH_ID IS NULL)))
|
|
89
|
AND (A.PROCESS_ID ='APPROVE')
|
|
90
|
--ORDER BY A.CREATE_DT DESC
|
|
91
|
END
|