|
1
|
DECLARE @REQ_ID VARCHAR(20) = 'PLRD00000206101'
|
|
2
|
SELECT TB.TRADE_ID ,SUM(TB.TOTAL_AMT) AS TOTAL_AMT_EXE,
|
|
3
|
SUM(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))/COUNT(TB.TRADE_ID)
|
|
4
|
-
|
|
5
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
6
|
FROM dbo.PL_REQUEST_DOC_DT DDT
|
|
7
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_ID = DOC.REQ_ID
|
|
8
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
9
|
AND doc.REQ_ID <> @REQ_ID
|
|
10
|
AND DDT.TRADE_ID = TB.TRADE_ID)
|
|
11
|
-
|
|
12
|
(SELECT ISNULL(SUM(DDT.TOTAL_AMT),0)
|
|
13
|
FROM dbo.PL_REQUEST_TRANSFER DDT
|
|
14
|
LEFT JOIN dbo.PL_REQUEST_DOC DOC ON DDT.REQ_DOC_ID = DOC.REQ_ID
|
|
15
|
WHERE DOC.PROCESS_ID NOT IN ('','SIGN','APPNEW','REJECT','APPROVE','SETTLMENT')
|
|
16
|
AND doc.REQ_ID <> @REQ_ID
|
|
17
|
AND DDT.FR_TRADE_ID = TB.TRADE_ID) AS TOTAL_AMT_REMAIN,
|
|
18
|
SUM(ISNULL(PLTF.TOTAL_AMT,0)) AS TOTAL_TF
|
|
19
|
FROM dbo.PL_REQUEST_DOC_DT TB
|
|
20
|
LEFT JOIN dbo.PL_TRADEDETAIL PT ON PT.TRADE_ID=TB.TRADE_ID
|
|
21
|
LEFT JOIN dbo.PL_MASTER PM ON PM.PLAN_ID=PT.PLAN_ID
|
|
22
|
LEFT JOIN dbo.PL_REQUEST_TRANSFER PLTF ON PLTF.FR_BRN_ID=PM.BRANCH_ID AND PLTF.FR_TRADE_ID=PT.TRADE_ID AND PLTF.REQ_DOC_ID=TB.REQ_ID
|
|
23
|
WHERE TB.REQDT_TYPE='I' AND TB.REQ_ID=@REQ_ID
|
|
24
|
GROUP BY TB.TRADE_ID
|