|
1
|
DECLARE @REQ_ID VARCHAR(20) = 'PLRD00000206101'
|
|
2
|
SELECT TB.TRADE_ID,
|
|
3
|
TB.TOTAL_AMT AS TOTAL_AMT_EXE,
|
|
4
|
ROUND(ISNULL(PT.AMT_APP,0),0) + ROUND(ISNULL(PT.AMT_RECEIVE_TF,0),0) - ROUND(ISNULL(PT.AMT_TF,0),0) - ROUND(ISNULL(PT.AMT_ETM,0),0)
|
|
5
|
-
|
|
6
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
7
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
8
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
9
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
10
|
AND doc.REQ_ID <> @REQ_ID
|
|
11
|
AND DDT.TRADE_ID = TB.TRADE_ID)
|
|
12
|
-
|
|
13
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
14
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
15
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
16
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
17
|
AND doc.REQ_ID <> @REQ_ID
|
|
18
|
AND DDT.FR_TRADE_ID = TB.TRADE_ID) AS TOTAL_AMT_REMAIN,
|
|
19
|
ISNULL(PLTF.TOTAL_AMT,0) AS TOTAL_TF
|
|
20
|
FROM
|
|
21
|
(
|
|
22
|
SELECT TEMP1.TRADE_ID, TEMP1.REQ_ID, SUM(TEMP1.TOTAL_AMT) AS TOTAL_AMT FROM dbo.PL_REQUEST_DOC_DT TEMP1
|
|
23
|
WHERE TEMP1.REQDT_TYPE='I' AND TEMP1.REQ_ID=@REQ_ID
|
|
24
|
GROUP BY TEMP1.TRADE_ID,TEMP1.REQ_ID
|
|
25
|
) TB
|
|
26
|
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.TRADE_ID
|
|
27
|
LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PT.PLAN_ID
|
|
28
|
LEFT JOIN
|
|
29
|
(
|
|
30
|
SELECT SUM(TEMP2.TOTAL_AMT) AS TOTAL_AMT, TEMP2.FR_TRADE_ID, TEMP2.REQ_DOC_ID FROM dbo.PL_REQUEST_TRANSFER TEMP2
|
|
31
|
GROUP BY TEMP2.FR_TRADE_ID, TEMP2.REQ_DOC_ID
|
|
32
|
) PLTF ON PLTF.FR_TRADE_ID=PT.TRADE_ID AND PLTF.REQ_DOC_ID=TB.REQ_ID
|