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.
642 lines
31 KiB
642 lines
31 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
|
|
/****** Object: StoredProcedure [dbo].[PROC_TP_TXN_PUSH] Script Date: 7/18/2024 8:52:21 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
--EXEC PROC_TP_TXN_PUSH @FLAG = 'GET-LIST'
|
|
ALTER PROC [dbo].[PROC_TP_TXN_PUSH] (
|
|
@FLAG VARCHAR(30)
|
|
,@TRAN_ID BIGINT = NULL
|
|
,@PROVIDER VARCHAR(30) = NULL
|
|
,@doSyncAll CHAR(1) = NULL
|
|
,@statusDate DATETIME = NULL
|
|
,@message VARCHAR(500) = NULL
|
|
,@status VARCHAR(100) = NULL
|
|
,@user VARCHAR(20) = NULL
|
|
,@pCurrCostRate FLOAT = NULL
|
|
,@control2 VARCHAR(30) = NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
-- =============================================
|
|
--Modified on: 2021/08/03 -> status date for Dongav2
|
|
-- remove NEW_PAID_TRACK
|
|
--Change Request #394 ->Remove Instant Pay for during ProcessBank Deposit
|
|
-- Add BOC
|
|
-- Brac
|
|
--EXEC PROC_TP_TXN_PUSH @flag='add-api-comment',@PROVIDER = 'bracBank',@TRANID = '100644605',@ControlNo = '211298008',@message = 'Cancellation Completed',@status = '407'
|
|
--Added new @Flag = 'INSTANT-PAID_V2' for manual pay
|
|
-- =============================================
|
|
BEGIN
|
|
IF ISDATE(@statusDate) = 0
|
|
SET @statusDate = NULL
|
|
|
|
DECLARE @CONTROLNO VARCHAR(30)
|
|
,@TRANDATE VARCHAR(30)
|
|
,@customerId VARCHAR(200)
|
|
|
|
IF @FLAG = 'GET-LIST'
|
|
BEGIN
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
INTO #REMIT_TT
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
LEFT JOIN tranModifyLog(NOLOCK) tml ON tml.tranId = rt.id
|
|
WHERE 1 = 1
|
|
AND payStatus = 'unpaid'
|
|
AND tranStatus = 'Payment'
|
|
AND rt.CREATEDDATE >= '2020-02-15'
|
|
AND PSUPERAGENT IN (
|
|
394397
|
|
,394449
|
|
,394502
|
|
,394490
|
|
)
|
|
AND rt.pCountry IN (
|
|
'NEPAL'
|
|
,'INDIA'
|
|
,'PAKISTAN'
|
|
,'Mongolia'
|
|
)
|
|
AND (
|
|
tml.STATUS <> 'Resolved'
|
|
AND tml.fieldName = 'STATUS_SYNC'
|
|
)
|
|
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
INTO #remit_unpaid
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
WHERE 1 = 1
|
|
AND payStatus = 'unpaid'
|
|
AND tranStatus = 'Payment'
|
|
AND rt.CREATEDDATE >= '2020-02-15'
|
|
AND PSUPERAGENT IN (
|
|
394397
|
|
,394449
|
|
,394502
|
|
,394490
|
|
)
|
|
AND rt.pCountry IN (
|
|
'NEPAL'
|
|
,'INDIA'
|
|
,'PAKISTAN'
|
|
,'Mongolia'
|
|
)
|
|
|
|
DELETE
|
|
FROM #remit_unpaid
|
|
WHERE id IN (
|
|
SELECT id
|
|
FROM #REMIT_TT
|
|
)
|
|
|
|
SELECT rt.ID
|
|
,CONTROLNO = DBO.DECRYPTDB(rt.CONTROLNO) --+ DATEPART(SS,GETDATE())
|
|
,PAYMENTMETHOD
|
|
,PCOUNTRY
|
|
,rt.CREATEDDATE
|
|
,pbankname
|
|
,pbank
|
|
,pAmt
|
|
FROM REMITTRAN(NOLOCK) rt
|
|
INNER JOIN #remit_unpaid u ON rt.id = u.id
|
|
ORDER BY rt.ID DESC
|
|
-- update remitTran set tranStatus='Payment', payStatus='unpaid' where controlNo = dbo.FNAEncryptString('211744466')
|
|
END
|
|
ELSE IF @FLAG = 'UPDATE-POST'
|
|
BEGIN
|
|
UPDATE dbo.remitTran
|
|
SET payStatus = 'POST'
|
|
,postedBy = 'Scheduler'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal = GETDATE()
|
|
,pDateCostRate = @pCurrCostRate
|
|
,controlno2 = CASE
|
|
WHEN @control2 IS NOT NULL
|
|
THEN dbo.encryptdb(@control2)
|
|
ELSE controlno2
|
|
END
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
ELSE IF @FLAG = 'UPDATE-POST_RELEASE'
|
|
BEGIN
|
|
UPDATE dbo.remitTran
|
|
SET
|
|
--controlno2= CASE WHEN @control2 IS NOT NULL THEN dbo.encryptdb(@control2) ELSE controlno2 END
|
|
downloadedDate = GETDATE()
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
ELSE IF @FLAG = 'mark-paid'
|
|
BEGIN
|
|
DECLARE @statusDateOld DATETIME = CAST(@statusDate AS DATETIME)
|
|
|
|
--IF @PROVIDER = 'jmenepal'
|
|
-- AND @statusDate IS NOT NULL
|
|
-- SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
IF @PROVIDER = 'gmenepal'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
ELSE IF @PROVIDER = 'gccremit'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
ELSE IF @PROVIDER = 'sendMN'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
ELSE IF @PROVIDER = 'guavaPay'
|
|
AND @statusDate IS NOT NULL
|
|
SET @statusDate = DATEADD(HOUR, - 5, DATEADD(MINUTE, - 45, @statusDate))
|
|
|
|
UPDATE dbo.remitTran
|
|
SET tranStatus = 'Paid'
|
|
,payStatus = 'Paid'
|
|
,paidBy = 'Scheduler'
|
|
,paidDate = ISNULL(DATEADD(HOUR, + 9, @statusDate), GETDATE())
|
|
,paidDateLocal = ISNULL(@statusDate, DATEADD(HOUR, - 9, GETDATE())) -- GMT FORMAT
|
|
WHERE id = @TRAN_ID
|
|
|
|
SELECT @CONTROLNO = DBO.DECRYPTDB(CONTROLNO)
|
|
,@TRANDATE = PAIDDATE
|
|
FROM dbo.remitTran(NOLOCK)
|
|
WHERE id = @TRAN_ID
|
|
|
|
UPDATE tranModifyLog
|
|
SET resolvedBy = 'Scheduler'
|
|
,resolvedDate = GETDATE()
|
|
,STATUS = 'Resolved'
|
|
WHERE tranId = @TRAN_ID
|
|
AND fieldname = 'STATUS_SYNC'
|
|
|
|
DECLARE @senderId INT;
|
|
|
|
SELECT @senderId = customerId
|
|
FROM dbo.transenders(NOLOCK)
|
|
WHERE tranId = @TRAN_ID
|
|
|
|
PRINT @senderId
|
|
PRINT @TRAN_ID
|
|
|
|
DECLARE @referralId VARCHAR(100)
|
|
|
|
SELECT @referralId = ISNULL(referralId, referelCode)
|
|
FROM customerMaster
|
|
WHERE @customerId = @senderId
|
|
|
|
IF (
|
|
LEFT(@referralId, 3) NOT IN (
|
|
'MOB'
|
|
,'ADM'
|
|
,'CRP'
|
|
,'ONL'
|
|
)
|
|
)
|
|
BEGIN
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @senderId
|
|
,@TranId = @TRAN_ID
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
EXEC proc_InsertReferralRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @senderId
|
|
,@TranId = @TRAN_ID
|
|
|
|
IF NOT EXISTS (
|
|
(
|
|
SELECT 'X'
|
|
FROM tranSenders
|
|
WHERE customerId = @senderId
|
|
)
|
|
|
|
UNION
|
|
|
|
(
|
|
SELECT 'X'
|
|
FROM tranSendersTemp
|
|
WHERE customerId = @senderId
|
|
)
|
|
)
|
|
BEGIN
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @senderId
|
|
,@TranId = @TRAN_ID
|
|
END
|
|
END
|
|
|
|
EXEC FASTMONEYPRO_ACCOUNT.DBO.PROC_TRANSACTION_PAID_VOUCHER_ENTRY @controlNo = @CONTROLNO
|
|
,@tranDate = @TRANDATE
|
|
--IF EXISTS(SELECT 1 FROM FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER (NOLOCK) WHERE FIELD1 = @controlNo AND tran_type = 'j' AND FIELD2 = 'Remittance Voucher'
|
|
-- AND ISNULL(ACCT_TYPE_CODE, 'Send') = 'Paid')
|
|
--BEGIN
|
|
-- UPDATE FASTMONEYPRO_ACCOUNT.DBO.TRAN_MASTER SET TRAN_DATE = @TRANDATE
|
|
-- WHERE FIELD1 = @controlNo
|
|
-- AND tran_type = 'j'
|
|
-- AND FIELD2 = 'Remittance Voucher'
|
|
-- AND ISNULL(ACCT_TYPE_CODE, 'Send') = 'Paid'
|
|
--END
|
|
END
|
|
ELSE IF @FLAG = 'sync-list'
|
|
BEGIN
|
|
IF ISNULL(@doSyncAll, 'N') = 'N'
|
|
BEGIN
|
|
IF @PROVIDER = 'gccremit'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO2)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394449
|
|
ORDER BY ID DESC
|
|
--AND id=dbo.fnaencryptstring('211517849')
|
|
END
|
|
|
|
IF @PROVIDER = 'guavaPay'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO2)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394490
|
|
ORDER BY ID DESC
|
|
--AND id=dbo.fnaencryptstring('211517849')
|
|
END
|
|
|
|
IF @PROVIDER = 'guavaPay'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO2)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394490
|
|
ORDER BY ID DESC
|
|
--AND id=dbo.fnaencryptstring('211517849')
|
|
END
|
|
|
|
IF @PROVIDER = 'sendMN'
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO2)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394502
|
|
ORDER BY ID DESC
|
|
--AND id=dbo.fnaencryptstring('211517849')
|
|
END
|
|
|
|
IF @PROVIDER = 'sendMN'
|
|
BEGIN
|
|
SELECT id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-03-31'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394502
|
|
END
|
|
ELSE IF @PROVIDER = 'transfast'
|
|
BEGIN
|
|
SELECT TOP 50 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
--AND PCOUNTRY NOT IN ('NEPAL','VIETNAM')
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394130
|
|
--AND ID NOT IN (SELECT TRAN_ID FROM NEW_PAID_TRACK_NEW)
|
|
--AND id = 100466789
|
|
--AND CONTROLNO = DBO.FNAENCRYPTSTRING('33TF001950961')
|
|
ORDER BY ID DESC
|
|
END
|
|
ELSE IF @PROVIDER = 'gmenepal'
|
|
--BEGIN
|
|
-- SELECT TOP 75 id
|
|
-- ,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
-- ,pSuperAgent
|
|
-- ,PCOUNTRY
|
|
-- ,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
-- ,createdDate
|
|
-- ,paymentMethod
|
|
-- FROM REMITTRAN(NOLOCK)
|
|
-- WHERE 1 = 1
|
|
-- AND CREATEDDATE >= '2020-02-15'
|
|
-- AND payStatus = 'Post'
|
|
-- AND TRANSTATUS = 'Payment'
|
|
-- AND PSUPERAGENT = 394397
|
|
-- ORDER BY ID DESC
|
|
--END
|
|
BEGIN
|
|
SELECT TOP 75 id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394397
|
|
ORDER BY ID DESC
|
|
END
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
IF @PROVIDER = 'transfast'
|
|
BEGIN
|
|
SELECT id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-03-31'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394130
|
|
END
|
|
ELSE IF @PROVIDER = 'gmenepal'
|
|
BEGIN
|
|
SELECT id
|
|
,controlNo = DBO.DECRYPTDB(CONTROLNO)
|
|
,pSuperAgent
|
|
,PCOUNTRY
|
|
,partnerPin = DBO.DECRYPTDB(CONTROLNO)
|
|
,createdDate
|
|
,paymentMethod
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-02-15'
|
|
AND payStatus = 'Post'
|
|
AND TRANSTATUS = 'Payment'
|
|
AND PSUPERAGENT = 394397
|
|
ORDER BY ID DESC
|
|
END
|
|
END
|
|
END
|
|
ELSE IF @Flag = 'GET-RELEASE-LIST'
|
|
BEGIN
|
|
SELECT ID
|
|
,CONTROLNO = DBO.DECRYPTDB(CONTROLNO)
|
|
,CONTROLNO2 = DBO.DECRYPTDB(CONTROLNO2)
|
|
,PAYMENTMETHOD
|
|
,PCOUNTRY
|
|
,CREATEDDATE
|
|
,pbankname
|
|
,pbank
|
|
,sSuperAgent = pSuperAgent
|
|
,[partner] = 'gccremit'
|
|
,pagentcomm
|
|
FROM REMITTRAN(NOLOCK)
|
|
WHERE 1 = 1
|
|
AND CREATEDDATE >= '2020-03-31'
|
|
AND payStatus = 'POST'
|
|
AND tranStatus = 'Payment'
|
|
AND PSUPERAGENT = 394449
|
|
AND downloadedDate IS NULL
|
|
ORDER BY ID DESC
|
|
END
|
|
ELSE IF @Flag = 'INSTANT-PAID'
|
|
BEGIN
|
|
--UPDATE dbo.remitTran SET tranStatus = 'Paid',
|
|
-- payStatus = 'Paid',
|
|
-- paidBy = 'Scheduler',
|
|
-- paidDate = GETDATE(),
|
|
-- paidDateLocal = DATEADD(HOUR, -9, GETDATE()) -- GMT FORMAT
|
|
--WHERE id = @TRAN_ID
|
|
--SELECT @CONTROLNO = DBO.DECRYPTDB(CONTROLNO),
|
|
-- @TRANDATE = PAIDDATE
|
|
--FROM dbo.remitTran (NOLOCK)
|
|
--WHERE id = @TRAN_ID
|
|
--EXEC FASTMONEYPRO_ACCOUNT.DBO.PROC_TRANSACTION_PAID_VOUCHER_ENTRY @controlNo = @CONTROLNO, @tranDate = @TRANDATE
|
|
UPDATE dbo.remitTran
|
|
SET payStatus = 'POST'
|
|
,postedBy = 'Scheduler'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal = GETDATE()
|
|
WHERE id = @TRAN_ID
|
|
END
|
|
--ELSE IF @Flag = 'INSTANT-PAID'
|
|
--BEGIN
|
|
-- --UPDATE dbo.remitTran SET tranStatus = 'Paid',
|
|
-- UPDATE dbo.remitTran
|
|
-- SET payStatus = 'POST'
|
|
-- ,postedBy = 'Scheduler'
|
|
-- ,postedDate = GETDATE()
|
|
-- ,postedDateLocal = GETDATE()
|
|
-- WHERE id = @TRAN_ID
|
|
--END
|
|
ELSE IF @Flag = 'add-api-comment'
|
|
BEGIN
|
|
SELECT @CONTROLNO = (CONTROLNO)
|
|
----,@TRANDATE = PAIDDATE
|
|
FROM dbo.remitTran(NOLOCK)
|
|
WHERE id = @TRAN_ID
|
|
|
|
IF NOT EXISTS (
|
|
SELECT *
|
|
FROM tranModifyLog
|
|
WHERE tranId = @TRAN_ID
|
|
AND fieldName = 'STATUS_SYNC'
|
|
AND ISNULL(oldValue, '') = @status
|
|
)
|
|
BEGIN
|
|
INSERT INTO tranModifyLog (
|
|
tranId
|
|
,controlNo
|
|
,message
|
|
,createdBy
|
|
,createdDate
|
|
,MsgType
|
|
,STATUS
|
|
,needToSync
|
|
,fieldName
|
|
,oldValue
|
|
)
|
|
SELECT @TRAN_ID
|
|
,@CONTROLNO
|
|
,@message
|
|
,ISNULL(@user, 'syncApi')
|
|
,GETDATE()
|
|
,'C'
|
|
,'Not Resolved'
|
|
,1
|
|
,'STATUS_SYNC'
|
|
,@status
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE tranModifyLog
|
|
SET message = @message
|
|
,createdDate = GETDATE()
|
|
WHERE tranId = @TRAN_ID
|
|
AND fieldName = 'STATUS_SYNC'
|
|
AND ISNULL(oldValue, '') = @status
|
|
END
|
|
|
|
SELECT '0' ErrorCode
|
|
,'Comment Added' Msg
|
|
,0 Id
|
|
END
|
|
ELSE IF @Flag = 'INSTANT-PAID_V2'
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 'X'
|
|
FROM remitTran
|
|
WHERE id = @TRAN_ID
|
|
AND tranStatus IN (
|
|
'Cancel'
|
|
,'Paid'
|
|
)
|
|
)
|
|
BEGIN
|
|
SELECT '0' ErrorCode
|
|
,'Invalid Transaction Status!' Msg
|
|
,0 Id
|
|
RETURN
|
|
END
|
|
SELECT @customerId = customerId
|
|
FROM tranSenders
|
|
WHERE tranId = @TRAN_ID
|
|
DECLARE @refId VARCHAR(100)
|
|
|
|
SELECT @customerId = customerId
|
|
FROM vwTranSenders
|
|
WHERE tranId = @TRAN_ID
|
|
OR holdTranId = @TRAN_ID
|
|
|
|
SELECT @refId = ISNULL(referralId, referelCode)
|
|
FROM customerMaster
|
|
WHERE customerId = @customerId
|
|
|
|
UPDATE dbo.remitTran
|
|
SET tranStatus = 'Paid'
|
|
,payStatus = 'Paid'
|
|
,paidBy = 'Manual'
|
|
,paidDate = GETDATE()
|
|
,paidDateLocal = DATEADD(HOUR, 1, GETDATE()) -- GMT FORMAT
|
|
WHERE id = @TRAN_ID
|
|
AND (
|
|
tranStatus <> 'CANCEL'
|
|
OR payStatus <> 'PAID'
|
|
)
|
|
|
|
IF (
|
|
LEFT(@refId, 3) IN (
|
|
'MOB'
|
|
,'ADM'
|
|
,'CRP'
|
|
,'ONL'
|
|
)
|
|
)
|
|
BEGIN
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @customerId
|
|
,@TranId = @TRAN_ID
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
--print 'hh'
|
|
--print @refId
|
|
--print @customerId
|
|
--print @TRAN_ID
|
|
--print 'ewrqe'
|
|
--return
|
|
EXEC proc_InsertReferralRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @customerId
|
|
,@TranId = @TRAN_ID
|
|
,@ReferralId = @refId
|
|
|
|
IF NOT EXISTS (
|
|
(
|
|
SELECT 'X'
|
|
FROM tranSenders
|
|
WHERE customerId = @customerId
|
|
)
|
|
|
|
UNION
|
|
|
|
(
|
|
SELECT 'X'
|
|
FROM tranSendersTemp
|
|
WHERE customerId = @customerId
|
|
)
|
|
)
|
|
BEGIN
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @customerId
|
|
,@TranId = @TRAN_ID
|
|
END
|
|
END
|
|
|
|
EXEC proc_InsertRewardPoints @Flag = 'TRANSACTION'
|
|
,@CustomerId = @customerId
|
|
,@TranId = @TRAN_ID
|
|
|
|
SELECT '0' ErrorCode
|
|
,'Transaction paid successfully' Msg
|
|
,0 Id
|
|
END
|
|
END
|
|
--CREATE TABLE NEW_PAID_TRACK
|
|
--(
|
|
-- TRAN_ID BIGINT
|
|
-- ,PARTNER_NAME VARCHAR(50)
|
|
-- ,PAIDDATE DATETIME
|
|
-- ,PAIDDATE_LOCAL DATETIME
|
|
-- ,ORIGINAL_DATE DATETIME
|
|
--)
|