|
1
|
ALTER PROCEDURE dbo.rpt_PL_REQ_DOC_Inventory
|
|
2
|
@p_GD_CODE varchar(50) = NULL,
|
|
3
|
@p_GD_NAME nvarchar(500) = NULL,
|
|
4
|
@p_DEP_ID VARCHAR(20) = NULL,
|
|
5
|
@p_BRANCH_ID varchar(20) = NULL,
|
|
6
|
@p_USER_LOGIN VARCHAR(15)= NULL
|
|
7
|
AS
|
|
8
|
BEGIN
|
|
9
|
SELECT Row_number() over(order by PL.REQ_ID) AS STT,PL.REQ_REASON,PL.EFFEC_DT,PL.REQ_ID,PL.REQ_CODE,CB.BRANCH_CODE,CB.BRANCH_NAME,CD.DEP_CODE,CD.DEP_NAME,Temp2.GD_CODE,Temp2.GD_NAME,TU.TLFullName AS MAKER_NAME,Temp2.AMT_ETM,Temp2.AMT_TF,Temp2.AMT_RECIVE_TF AS AMT_RECEIVE_TF,
|
|
10
|
prp.NOTES AS TRANGTHAI_TOTRINH
|
|
11
|
FROM
|
|
12
|
(
|
|
13
|
SELECT Temp.REQ_ID,Temp.GD_CODE,Temp.GD_NAME,SUM(Temp.AMT_ETM) AS AMT_ETM,SUM(Temp.AMT_TF) AS AMT_TF,SUM(Temp.AMT_RECIVE_TF) AS AMT_RECIVE_TF FROM (
|
|
14
|
SELECT DT.REQ_ID,CG.GD_CODE,CG.GD_NAME,SUM(DT.TOTAL_AMT) AS AMT_ETM, 0 AMT_TF,0 AMT_RECIVE_TF FROM dbo.PL_REQUEST_DOC_DT DT
|
|
15
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.GOODS_ID
|
|
16
|
WHERE 1=1
|
|
17
|
AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
|
|
18
|
AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
|
|
19
|
GROUP BY DT.REQ_ID,CG.GD_CODE,CG.GD_NAME
|
|
20
|
UNION ALL
|
|
21
|
SELECT DT.REQ_DOC_ID AS REQ_ID,CG.GD_CODE,CG.GD_NAME,0 AS AMT_ETM, SUM(DT.TOTAL_AMT) AMT_TF,0 AMT_RECIVE_TF FROM dbo.PL_REQUEST_TRANSFER DT
|
|
22
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.FR_GOOD_ID
|
|
23
|
WHERE 1=1
|
|
24
|
AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
|
|
25
|
AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
|
|
26
|
GROUP BY DT.REQ_DOC_ID,CG.GD_CODE,CG.GD_NAME
|
|
27
|
UNION ALL
|
|
28
|
SELECT DT.REQ_DOC_ID AS REQ_ID,CG.GD_CODE,CG.GD_NAME,0 AS AMT_ETM, 0 AMT_TF,SUM(DT.TOTAL_AMT)AMT_RECIVE_TF FROM dbo.PL_REQUEST_TRANSFER DT
|
|
29
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=DT.TO_GOOD_ID
|
|
30
|
WHERE 1=1
|
|
31
|
AND (CG.GD_CODE LIKE '%' +@p_GD_CODE+'%' OR @p_GD_CODE ='' OR @p_GD_CODE IS NULL)
|
|
32
|
AND (CG.GD_NAME LIKE '%' +@p_GD_NAME+'%' OR @p_GD_NAME ='' OR @p_GD_NAME IS NULL)
|
|
33
|
GROUP BY DT.REQ_DOC_ID,CG.GD_CODE,CG.GD_NAME
|
|
34
|
) Temp
|
|
35
|
GROUP BY Temp.REQ_ID,Temp.GD_CODE,Temp.GD_NAME
|
|
36
|
) Temp2
|
|
37
|
LEFT JOIN dbo.PL_REQUEST_DOC PL ON Pl.REQ_ID=Temp2.REQ_ID
|
|
38
|
LEFT JOIN dbo.CM_BRANCH CB ON CB.BRANCH_ID=PL.BRANCH_CREATE
|
|
39
|
LEFT JOIN dbo.CM_DEPARTMENT CD ON CD.DEP_ID=PL.DEP_CREATE
|
|
40
|
LEFT JOIN dbo.TL_USER TU ON TU.TLNANME=PL.MAKER_ID
|
|
41
|
LEFT JOIN PL_REQUEST_PROCESS prp ON PL.REQ_ID = prp.REQ_ID AND PL.PROCESS_ID = prp.PROCESS_ID AND prp.STATUS = 'C'
|
|
42
|
LEFT JOIN CM_ALLCODE CA ON ca.CDTYPE = 'PL' AND ca.CDNAME = 'PROCESS_STATUS' AND ca.CDVAL = PL.PROCESS_ID
|
|
43
|
--ORDER BY A.CREATE_DT DESC
|
|
44
|
END
|