|
1
|
|
|
2
|
|
|
3
|
DECLARE @listTRADE TABLE
|
|
4
|
(
|
|
5
|
GOODS_ID VARCHAR(20),
|
|
6
|
TRADE_ID VARCHAR(20),
|
|
7
|
PLAN_ID VARCHAR(20),
|
|
8
|
AMT_APP DECIMAL(18,2),
|
|
9
|
AMT_EXE DECIMAL (18,2),
|
|
10
|
AMT_ETM DECIMAL (18,2),
|
|
11
|
AMT_TF DECIMAL (18,2),
|
|
12
|
AMT_RECEIVE_TF DECIMAL (18,2),
|
|
13
|
NOTES NVARCHAR(1000),
|
|
14
|
AMT_ETM_TMP DECIMAL (18,2)
|
|
15
|
|
|
16
|
)
|
|
17
|
INSERT INTO @listTRADE
|
|
18
|
( GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP)
|
|
19
|
SELECT * FROM
|
|
20
|
(
|
|
21
|
SELECT DISTINCT GOODS_ID,TRADE_ID,PLAN_ID,ISNULL(AMT_APP,0) AS AMT_APP,ISNULL(AMT_EXE,0) AS AMT_EXE,ISNULL(AMT_ETM,0) AS AMT_ETM,ISNULL(AMT_TF,0) AS AMT_TF,ISNULL(AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF,'' NOTES,ISNULL(AMT_ETM_TMP,0) AS AMT_ETM_TMP FROM dbo.PL_REQUEST_DOC_DT WHERE REQ_ID='PLRD00000207183'
|
|
22
|
UNION ALL
|
|
23
|
SELECT DISTINCT FR_GOOD_ID GOODS_ID,FR_TRADE_ID TRADE_ID,FR_PLAN_ID PLAN_ID,ISNULL(FR_AMT_APP,0) AMT_APP,ISNULL(FR_AMT_EXE,0) AMT_EXE,ISNULL(FR_AMT_ETM,0) AMT_ETM,ISNULL(FR_AMT_TF,0) AMT_TF,ISNULL(FR_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(FR_AMT_ETM_TMP,0) AMT_ETM_TMP
|
|
24
|
FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID='PLRD00000207183' AND ISNULL(FR_GOOD_ID,'') <> ''
|
|
25
|
UNION ALL
|
|
26
|
SELECT DISTINCT TO_GOOD_ID GOODS_ID,TO_TRADE_ID TRADE_ID,TO_PLAN_ID PLAN_ID,ISNULL(TO_AMT_APP,0) AMT_APP,ISNULL(TO_AMT_EXE,0) AMT_EXE,ISNULL(TO_AMT_ETM,0) AMT_ETM,ISNULL(TO_AMT_TF,0) AMT_TF,ISNULL(TO_AMT_RECEIVE_TF,0) AMT_RECEIVE_TF,'' NOTES,ISNULL(TO_AMT_ETM_TMP,0) AMT_ETM_TMP FROM dbo.PL_REQUEST_TRANSFER WHERE REQ_DOC_ID='PLRD00000207183'
|
|
27
|
) T GROUP BY GOODS_ID,TRADE_ID,PLAN_ID,AMT_APP,AMT_EXE,AMT_ETM,AMT_TF,AMT_RECEIVE_TF,NOTES,AMT_ETM_TMP
|
|
28
|
SELECT * FROM @listTRADE ----11052023_SECRETKEY
|
|
29
|
|
|
30
|
DECLARE @TABLE1 TABLE (
|
|
31
|
STT INT,
|
|
32
|
GD_NAME NVARCHAR(200),
|
|
33
|
AMT_APP DECIMAL(18, 2),
|
|
34
|
AMT_EXE DECIMAL(18, 2),
|
|
35
|
AMT_REMAIN DECIMAL(18, 2),
|
|
36
|
TOTAL_AMT DECIMAL(18, 2),
|
|
37
|
TOTAL_AMT_FN DECIMAL(18, 2),
|
|
38
|
CHECKER_NAME NVARCHAR(200),
|
|
39
|
XN_NAME VARCHAR(15),
|
|
40
|
TOTAL_AMT_RECIVE DECIMAL(18, 2)
|
|
41
|
)
|
|
42
|
INSERT INTO @TABLE1(STT,GD_NAME,AMT_APP,AMT_EXE,AMT_REMAIN,TOTAL_AMT,TOTAL_AMT_FN,CHECKER_NAME,XN_NAME,TOTAL_AMT_RECIVE)
|
|
43
|
SELECT ROW_NUMBER() OVER (ORDER BY CG.GD_NAME) AS STT ,CG.GD_NAME,
|
|
44
|
ISNULL(PT.AMT_APP,0) AS AMT_APP,
|
|
45
|
ISNULL(PT.AMT_ETM,0) + ISNULL(PT.AMT_ETM_TMP,0) AS AMT_EXE,
|
|
46
|
ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0)- ISNULL(PT.AMT_TF,0)-ISNULL(PT.AMT_ETM,0) - ISNULL(PT.AMT_ETM_TMP,0) AS AMT_REMAIN,
|
|
47
|
SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT,
|
|
48
|
ISNULL(PT.AMT_APP,0) + ISNULL(PT.AMT_RECEIVE_TF,0) - ISNULL(PT.AMT_TF,0) -ISNULL(PT.AMT_ETM,0) -ISNULL(PT.AMT_ETM_TMP,0) -SUM(ISNULL(PLDT.TOTAL_AMT,0)) AS TOTAL_AMT_FN,
|
|
49
|
CASE
|
|
50
|
WHEN PL.PROCESS_ID='APPROVE' THEN USC.TLFullName
|
|
51
|
|
|
52
|
ELSE N''
|
|
53
|
END AS CHECKER_NAME,
|
|
54
|
|
|
55
|
'' AS XN_NAME,
|
|
56
|
ISNULL(PT.AMT_RECEIVE_TF,0) AS AMT_RECEIVE_TF
|
|
57
|
FROM
|
|
58
|
dbo.PL_REQUEST_DOC_DT PLDT
|
|
59
|
|
|
60
|
LEFT JOIN @listTRADE PT ON PT.TRADE_ID=PLDT.TRADE_ID
|
|
61
|
LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PLDT.PLAN_ID
|
|
62
|
LEFT JOIN dbo.CM_GOODS CG ON CG.GD_ID=PLDT.GOODS_ID
|
|
63
|
LEFT JOIN dbo.TL_USER USC ON USC.TLNANME=PLDT.CHECKER_ID
|
|
64
|
LEFT JOIN dbo.PL_REQUEST_DOC PL ON PL.REQ_ID=PLDT.REQ_ID
|
|
65
|
WHERE PLDT.REQ_ID='PLRD00000207183'
|
|
66
|
GROUP BY PT.GOODS_ID,PT.AMT_APP,PT.AMT_ETM,PT.AMT_RECEIVE_TF,PT.AMT_TF,CG.GD_NAME,PLDT.REQ_ID,USC.TLFullName,PL.PROCESS_ID,PT.AMT_ETM_TMP
|
|
67
|
SELECT * FROM @TABLE1
|