You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
115 lines
8.1 KiB
115 lines
8.1 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_SCHEDULAR_PUSH_TXN_BNI] Script Date: 9/27/2019 1:30:14 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[PROC_SCHEDULAR_PUSH_TXN_BNI](
|
|
@flag VARCHAR(100) = NULL
|
|
,@id VARCHAR(100)= NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON
|
|
BEGIN
|
|
IF @flag='push-list-bni'
|
|
BEGIN
|
|
SELECT TOP 20
|
|
refNumber = dbo.FNADecryptString(RT.controlNo)
|
|
,serviceType = CASE WHEN RT.pBank = 393369 THEN 'BNI' --##PT. BANK NEGARA INDONESIA (PERSERO),TBK
|
|
WHEN RT.pBank <> 393369 AND RT.pAmt BETWEEN 0 AND 500000000 THEN 'INTERBANK'
|
|
--WHEN RT.pBank <> 393369 AND RT.pAmt BETWEEN 25000000 AND 500000000 THEN 'CLR'
|
|
WHEN RT.pBank <> 393369 AND RT.pAmt > 500000000 THEN 'RTGS'
|
|
END
|
|
,trxDate = FORMAT(RT.approvedDate,'yyyy-MM-ddTHH:mm:ss')
|
|
,currency = RT.payoutCurr
|
|
,amount = RT.pAmt
|
|
,orderingName = RT.senderName
|
|
,orderingAddress1 = LEFT(TS.ADDRESS,50)
|
|
,orderingAddress2 = TS.ADDRESS2
|
|
,orderingPhoneNumber = TS.mobile
|
|
,beneficiaryAccount = RT.accountNo
|
|
,beneficiaryName = RT.receiverName
|
|
,beneficiaryAddress1 = TR.ADDRESS
|
|
,beneficiaryAddress2 = TR.ADDRESS2
|
|
,beneficiaryPhoneNumber = TR.mobile
|
|
,acctWithInstcode = 'A'
|
|
,acctWithInstName = CASE WHEN RT.pBank = 393369 THEN AM.routingCode + 'XXX' --##PT. BANK NEGARA INDONESIA (PERSERO),TBK
|
|
WHEN RT.pBank <> 393369 AND RT.pAmt BETWEEN 0 AND 500000000 THEN AM.agentCode
|
|
----WHEN RT.pBank <> 393369 AND RT.pAmt BETWEEN 25000000 AND 500000000 THEN AM.routingCode
|
|
WHEN RT.pBank <> 393369 AND RT.pAmt > 500000000 THEN AM.routingCode
|
|
END
|
|
,acctWithInstAddress1 = ''
|
|
,acctWithInstAddress2 = ''
|
|
,acctWithInstAddress3 = ''
|
|
,detailPayment1 = ''
|
|
,detailPayment2 = ''
|
|
,detailCharges = 'OUR'
|
|
,RT.ID
|
|
,RT.controlNo
|
|
INTO #TEMPLIST
|
|
FROM dbo.remitTran AS [RT] (NOLOCK)
|
|
INNER JOIN tranSenders AS [TS] (NOLOCK) ON TS.TRANID = RT.ID
|
|
INNER JOIN tranReceivers AS [TR] (NOLOCK) ON TR.TRANID = RT.ID
|
|
INNER JOIN agentMaster AS [AM] (NOLOCK) ON AM.agentId = RT.pBank
|
|
WHERE RT.pCountry = 'Indonesia'
|
|
AND RT.payStatus = 'Unpaid' AND RT.tranStatus = 'Payment'
|
|
AND RT.pAgent = 392227
|
|
--and 1=2
|
|
--and controlNo=dbo.FNAEncryptString('80553080423')
|
|
ORDER BY RT.ID desc
|
|
|
|
ALTER TABLE #TEMPLIST ADD IsAlreadyProcessed BIT
|
|
|
|
UPDATE T SET IsAlreadyProcessed=1 FROM #TEMPLIST T
|
|
INNER JOIN (SELECT CONTROLNO FROM Application_Log.DBO.vwTpApilogs(NOLOCK)
|
|
WHERE providerName='BNI' AND methodName='processPO'
|
|
GROUP BY controlNo HAVING COUNT(1) >= 1 ) V ON V.controlNo = T.refNumber
|
|
|
|
UPDATE rt SET rt.tranStatus='Hold' FROM remitTran rt (NOLOCK)
|
|
INNER JOIN #TEMPLIST T ON T.controlNo = rt.controlNo
|
|
WHERE RT.pCountry = 'Indonesia'
|
|
AND RT.payStatus = 'Unpaid' AND RT.tranStatus = 'Payment'
|
|
AND RT.pAgent = 392227 AND IsAlreadyProcessed=1
|
|
|
|
DELETE FROM #TEMPLIST WHERE IsAlreadyProcessed = 1
|
|
|
|
ALTER TABLE #TEMPLIST DROP COLUMN controlNo,IsAlreadyProcessed
|
|
|
|
SELECT * FROM #TEMPLIST
|
|
|
|
END
|
|
ELSE IF @flag='sync-list-Bni'
|
|
BEGIN
|
|
--SELECT TOP 1 '1234' AS TranId,'1234' AS refNumber,GETDATE() AS trxDate FROM dbo.remitTran AS RT(NOLOCK)
|
|
SELECT
|
|
refNumber = dbo.FNADecryptString(RT.controlNo)
|
|
,trxDate = FORMAT(RT.approvedDate,'yyyy-MM-dd-THH:mm:ss')
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
WHERE RT.pCountry = 'Indonesia'
|
|
AND RT.payStatus = 'Post'
|
|
AND RT.tranStatus = 'Payment'
|
|
AND RT.pAgent = 392227
|
|
END
|
|
ELSE IF @flag='mark-paid-bni'
|
|
BEGIN
|
|
UPDATE remitTran SET
|
|
payStatus = 'Paid'
|
|
,tranStatus = 'Paid'
|
|
,paidBy = 'system'
|
|
,paidDate = GETDATE()
|
|
,paidDateLocal = GETUTCDATE()
|
|
WHERE Id=@id and pAgent = 392227 and payStatus = 'Post' AND tranStatus = 'Payment'
|
|
END
|
|
ELSE IF @flag='mark-post-bni'
|
|
BEGIN
|
|
UPDATE remitTran SET
|
|
payStatus = 'Post'
|
|
,postedBy = 'Scheduler'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal= GETUTCDATE()
|
|
WHERE Id = @id and pAgent = 392227 and payStatus = 'Unpaid' AND tranStatus = 'Payment'
|
|
END
|
|
END
|
|
GO
|