|
1
|
ALTER PROCEDURE [dbo].[TR_REQ_GET_ENTRY_TRANSFER_EXTERNAL]
|
|
2
|
@p_REQ_PAY_ID varchar(15)= NULL,
|
|
3
|
@p_CHECKER_ID_KT varchar(15) = NULL
|
|
4
|
AS
|
|
5
|
|
|
6
|
DECLARE @l_sourceRef VARCHAR(16), @l_Month VARCHAR(2), @l_Year VARCHAR(2)
|
|
7
|
SET @l_Month = RIGHT('0' + CAST(DATEPART(MM, GETDATE()) AS VARCHAR(2)), 2);
|
|
8
|
SET @l_Year = RIGHT(YEAR(GETDATE()), 2);
|
|
9
|
|
|
10
|
SELECT
|
|
11
|
'AMSOT' + @l_Month + @l_Year + RIGHT(A.REQ_TRANS_OUTSIDE_REC_ID, 7) AS sourceRef,
|
|
12
|
C.BRANCH_CODE AS brn,
|
|
13
|
C.BRANCH_CODE AS offsetBrn,
|
|
14
|
B.ACC_NO_TRANSFER AS offsetAcc,
|
|
15
|
'VND' AS offsetCcy,
|
|
16
|
A.TRADE_AMT AS offsetAmt,
|
|
17
|
A.TRADE_DESC AS narrative,
|
|
18
|
A.CITAD1_ID AS awi1,
|
|
19
|
A.BANK_RECEIVE_ID AS awi2,
|
|
20
|
A.BANK_RECEIVE_ID AS awi3,
|
|
21
|
REQ.MAKER_ID_KT AS awi4,
|
|
22
|
A.ACC_NO_RECEIVE AS utlbnf1,
|
|
23
|
A.BRANCH_RECEIVE_NAME as utlbnf2,
|
|
24
|
A.BANK_RECEIVE_ID AS remBank,
|
|
25
|
A.BANK_RECEIVE_ID AS econt1,
|
|
26
|
A.CITAD1_ID + ' - ' + A.BANK_RECEIVE_ID AS econt2,
|
|
27
|
'OC' AS econt3,
|
|
28
|
'0' AS feeAmt,
|
|
29
|
'0' AS vatAmt
|
|
30
|
|
|
31
|
FROM TR_REQ_PAY_TRANS_OUTSIDE_REC A
|
|
32
|
LEFT JOIN TR_REQ_PAY_TRANS_OUTSIDE_TRANS B ON A.REQ_PAY_ID = B.REQ_PAY_ID
|
|
33
|
LEFT JOIN CM_BRANCH C ON B.BRANCH_ID = C.BRANCH_ID
|
|
34
|
LEFT JOIN TR_REQ_PAYMENT REQ ON A.REQ_PAY_ID = REQ.REQ_PAY_ID
|
|
35
|
WHERE A.REQ_PAY_ID = @p_REQ_PAY_ID
|
|
36
|
AND (A.IS_TRANSFER IS NULL OR A.IS_TRANSFER <> '')
|