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.
377 lines
35 KiB
377 lines
35 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[PROC_SCHEDULER_PUSH_TXN_CONTACT] Script Date: 9/27/2019 1:30:14 PM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
CREATE PROCEDURE [dbo].[PROC_SCHEDULER_PUSH_TXN_CONTACT](
|
|
@flag VARCHAR(100) = NULL
|
|
,@id VARCHAR(100)= NULL
|
|
,@ControlNo VARCHAR(100)=NULL
|
|
)
|
|
AS
|
|
SET NOCOUNT ON
|
|
BEGIN
|
|
IF @flag='push-list-contact'
|
|
BEGIN
|
|
|
|
CREATE TABLE #TEMPLIST(
|
|
sIDwhom VARCHAR(20),sIDtype VARCHAR(50),sCountry VARCHAR(50),sName VARCHAR(100),sLastName VARCHAR(100),sAddress VARCHAR(100),sIDnumber VARCHAR(100),
|
|
sIDdate VARCHAR(20),sBirthday VARCHAR(20),sPhone VARCHAR(20),sZipCode VARCHAR(100),sRegion VARCHAR(100),sCity VARCHAR(100),sBirthPlace VARCHAR(100),
|
|
sIDexpireDate VARCHAR(20),sResident VARCHAR(100),sCountryStay VARCHAR(100),sCountryC VARCHAR(2),sIDwhomCode VARCHAR(20),
|
|
|
|
bName VARCHAR(100),bLastName VARCHAR(100),bBirthday VARCHAR(100),bCountry VARCHAR(2),bIDdate VARCHAR(20),bIDwhom VARCHAR(20),
|
|
bResident VARCHAR(100),bBirthPlace VARCHAR(100),bPhone VARCHAR(20),
|
|
|
|
trnService VARCHAR(2),trnPickupPoint VARCHAR(10),trnAmount DECIMAL(10,2),trnDate VARCHAR(20),trnCurrency VARCHAR(3),trnSendPoint VARCHAR(10),trnRate FLOAT,trnReference VARCHAR(100),
|
|
trnTerminalNumber VARCHAR(1),trnPayoutAmount FLOAT,trnPayoutCurrency VARCHAR(3),trnPayoutRate FLOAT,bAccount VARCHAR(100),bBankName VARCHAR(100),
|
|
|
|
--- ONLY FOR RUS
|
|
tCardNumber VARCHAR(100),trnRecipientCurrency VARCHAR(3),sResidentC VARCHAR(2),sIDtypeCode VARCHAR(100),
|
|
|
|
---ONLY FOR RUSS
|
|
tRuBic VARCHAR(100),tRuAcc VARCHAR(100),
|
|
|
|
pCountry VARCHAR(50),createdDate DATETIME ,controlNo VARCHAR(20)
|
|
)
|
|
|
|
INSERT INTO #TEMPLIST(sIDwhom,sIDtype,sCountry,sName,sLastName,sAddress,sIDnumber,sIDdate,sBirthday,sPhone,sZipCode,sRegion,sCity,sBirthPlace,sIDexpireDate,sResident
|
|
,sCountryStay,sCountryC
|
|
,bName,bLastName,bBirthday,bCountry,bIDdate,bIDwhom,bResident,bBirthPlace,bPhone
|
|
,trnService,trnPickupPoint,trnAmount,trnDate,trnCurrency,trnSendPoint,trnRate,trnReference,trnTerminalNumber,trnPayoutAmount,trnPayoutCurrency,trnPayoutRate
|
|
,pCountry,createdDate,controlNo
|
|
)
|
|
----## FOR CASH PAYMENT TRANSACTION ##----
|
|
SELECT TOP 10
|
|
sIDwhom = 'Government'
|
|
,sIDtype = TS.idType
|
|
,sCountry = dbo.FunCountryCode(TS.country)
|
|
,sName = (SELECT FSN.lastName1 FROM dbo.FNASplitName(Ts.firstName) AS FSN) --LastName
|
|
,sLastName = (SELECT FSN.firstName FROM dbo.FNASplitName(Ts.firstName) AS FSN) ---firstName
|
|
,sAddress = ISNULL(TS.address,TS.city)
|
|
,sIDnumber = TS.idNumber
|
|
,sIDdate = FORMAT(TS.issuedDate,'yyyyMMdd')
|
|
,sBirthday = FORMAT(TS.dob,'yyyyMMdd')
|
|
,sPhone = [dbo].FunContactAPI_MobileFormat(TS.mobile)
|
|
,sZipCode = TS.zipCode
|
|
,sRegion = TS.address
|
|
,sCity = ISNULL(TS.city,'ulaanbaatar')
|
|
,sBirthPlace = TS.nativeCountry
|
|
,sIDexpireDate = FORMAT(TS.validDate,'yyyyMMdd')
|
|
,sResident = CASE WHEN TS.nativeCountry = 'South Korea' THEN 1 ELSE 0 END ------Required---0 not resident, 1 resident
|
|
,sCountryStay = dbo.FunCountryCode(TS.country)
|
|
,sCountryC = dbo.FunCountryCode(TS.nativeCountry)
|
|
|
|
,bName = TR.lastName1+ISNULL(' '+tr.lastName2,'')
|
|
,bLastName = TR.firstName+ISNULL(' '+TR.middleName,'')
|
|
,bBirthday = FORMAT(TR.dob,'yyyyMMdd')
|
|
,bCountry = dbo.FunCountryCode(TR.country)
|
|
,bIDdate = FORMAT(tr.issuedDate,'yyyyMMdd')
|
|
,bIDwhom = 'Governmnet'
|
|
,bResident = CASE WHEN TR.country = RT.pCountry THEN 1 ELSE 0 END
|
|
,bBirthPlace = TR.country
|
|
,bPhone = [dbo].FunContactAPI_MobileFormat(TR.mobile)
|
|
|
|
,trnService = '2' -- CASH PAYMENT
|
|
---,trnClAmount='456.66'
|
|
,trnPickupPoint = AM.agentCode
|
|
,trnAmount = ROUND(RT.pAmt / ISNULL(RT.pcurrcostRate,1),2)
|
|
,trnDate = FORMAT(RT.approvedDate,'yyyyMMdd')
|
|
,trnCurrency = 'USD' --- settlement currency
|
|
,trnSendPoint = 'TAWX'------Given by contact for GME
|
|
,trnRate = RT.customerRate------to be checked
|
|
,trnReference = dbo.FNADecryptString(RT.controlNo)
|
|
,trnTerminalNumber= '1'
|
|
,trnPayoutAmount = CASE WHEN RT.payoutCurr='MNT' THEN CONVERT(FLOAT,RT.pAmt) ELSE '' END
|
|
,trnPayoutCurrency = CASE WHEN RT.payoutCurr='MNT' THEN RT.payoutCurr ELSE '' END
|
|
,trnPayoutRate = CASE WHEN RT.payoutCurr='MNT' THEN RT.pcurrcostRate ELSE '' END
|
|
|
|
,RT.pCountry,RT.createdDate,rt.controlNo
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN tranSenders TS (NOLOCK) ON TS.tranId = RT.id
|
|
INNER JOIN tranReceivers TR (NOLOCK) ON TR.tranId = RT.id
|
|
INNER JOIN agentMaster AM (NOLOCK) ON AM.agentId = RT.pBank
|
|
LEFT JOIN agentMaster BM (NOLOCK) ON BM.agentId = RT.pBankBranch
|
|
WHERE RT.approvedBy IS NOT NULL AND RT.payStatus = 'Unpaid'
|
|
AND RT.tranStatus = 'Payment' and RT.pAgent = 393228
|
|
AND RT.paymentMethod ='CASH PAYMENT'
|
|
AND TS.issuedDate IS NOT NULL AND TS.dob IS NOT NULL
|
|
AND ISNULL(TS.validDate,'2000-01-01') >= CASE WHEN TS.idType <> 'National ID' THEN CAST(GETDATE() AS DATE) ELSE '2000-01-01' END
|
|
|
|
----## FOR BANK DEPOSIT TRANSACTION ##----
|
|
INSERT INTO #TEMPLIST(sIDwhom,sIDtype,sCountry,sName,sLastName,sAddress,sIDnumber,sIDdate,sBirthday,sPhone,sZipCode,sRegion,sCity,sBirthPlace,sIDexpireDate,sResident
|
|
,sCountryStay,sCountryC
|
|
,bName,bLastName,bBirthday,bCountry,bIDdate,bIDwhom,bResident,bBirthPlace,bPhone
|
|
,trnService,trnPickupPoint,trnAmount,trnDate,trnCurrency,trnSendPoint,trnRate,trnReference,trnTerminalNumber,trnPayoutAmount,trnPayoutCurrency,trnPayoutRate
|
|
,pCountry,createdDate,controlNo
|
|
,bAccount,bBankName,tRuBic,tRuAcc
|
|
)
|
|
SELECT TOP 10
|
|
sIDwhom = 'Government'
|
|
,sIDtype = TS.idType
|
|
,sCountry = dbo.FunCountryCode(TS.country)
|
|
,sName = (SELECT FSN.lastName1 FROM dbo.FNASplitName(Ts.firstName) AS FSN) --LastName
|
|
,sLastName = (SELECT FSN.firstName FROM dbo.FNASplitName(Ts.firstName) AS FSN) ---firstName
|
|
,sAddress = ISNULL(TS.address,TS.city)
|
|
,sIDnumber = TS.idNumber
|
|
,sIDdate = FORMAT(TS.issuedDate,'yyyyMMdd')
|
|
,sBirthday = FORMAT(TS.dob,'yyyyMMdd')
|
|
,sPhone = [dbo].FunContactAPI_MobileFormat(TS.mobile)
|
|
,sZipCode = TS.zipCode
|
|
,sRegion = TS.address
|
|
,sCity = ISNULL(TS.city,'ulaanbaatar')
|
|
,sBirthPlace = TS.nativeCountry
|
|
,sIDexpireDate = FORMAT(TS.validDate,'yyyyMMdd')
|
|
,sResident = CASE WHEN TS.nativeCountry = 'South Korea' THEN 1 ELSE 0 END ------Required---0 not resident, 1 resident
|
|
,sCountryStay = dbo.FunCountryCode(TS.country)
|
|
,sCountryC = dbo.FunCountryCode(TS.nativeCountry)
|
|
|
|
,bName = TR.lastName1+ISNULL(' '+tr.lastName2,'')
|
|
,bLastName = TR.firstName+ISNULL(' '+TR.middleName,'')
|
|
,bBirthday = FORMAT(TR.dob,'yyyyMMdd')
|
|
,bCountry = dbo.FunCountryCode(TR.country)
|
|
,bIDdate = FORMAT(tr.issuedDate,'yyyyMMdd')
|
|
,bIDwhom = 'Governmnet'
|
|
,bResident = CASE WHEN TR.country = RT.pCountry THEN 1 ELSE 0 END
|
|
,bBirthPlace = TR.country
|
|
,bPhone = [dbo].FunContactAPI_MobileFormat(TR.mobile)
|
|
|
|
,trnService = '5' --BANK DEPOSIT
|
|
---,trnClAmount='456.66'
|
|
,trnPickupPoint = CASE WHEN RT.payoutCurr = 'MNT' THEN AM.routingCode ELSE AM.agentCode END
|
|
,trnAmount = ROUND(RT.pAmt / ISNULL(RT.pcurrcostRate,1),2)
|
|
,trnDate = FORMAT(RT.approvedDate,'yyyyMMdd')
|
|
,trnCurrency = 'USD' --- settlement currency
|
|
,trnSendPoint = 'TAWX'------Given by contact for GME
|
|
,trnRate = RT.customerRate------to be checked
|
|
,trnReference = dbo.FNADecryptString(RT.controlNo)
|
|
,trnTerminalNumber= '1'
|
|
,trnPayoutAmount = CASE WHEN RT.payoutCurr = 'MNT' THEN CONVERT(FLOAT,RT.pAmt) ELSE '' END
|
|
,trnPayoutCurrency = CASE WHEN RT.payoutCurr = 'MNT' THEN RT.payoutCurr ELSE '' END
|
|
,trnPayoutRate = CASE WHEN RT.payoutCurr = 'MNT' THEN RT.pcurrcostRate ELSE '' END
|
|
|
|
,rt.pCountry,RT.createdDate,rt.controlNo
|
|
|
|
,bAccount = RT.AccountNo
|
|
,bBankName = RT.pBankName
|
|
|
|
----ONLY FOR RUSSIA BANK DEPOSIT
|
|
,tRuBic = CASE WHEN RT.payoutCurr = 'RUB' THEN BM.agentCode ELSE '' END
|
|
,tRuAcc = CASE WHEN RT.payoutCurr = 'RUB' THEN RT.AccountNo ELSE '' END
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN tranSenders TS (NOLOCK) ON TS.tranId = RT.id
|
|
INNER JOIN tranReceivers TR (NOLOCK) ON TR.tranId = RT.id
|
|
INNER JOIN agentMaster AM (NOLOCK) ON AM.agentId = RT.pBank
|
|
LEFT JOIN agentMaster BM (NOLOCK) ON BM.agentId = RT.pBankBranch
|
|
WHERE RT.approvedBy IS NOT NULL AND RT.payStatus = 'Unpaid'
|
|
AND RT.tranStatus = 'Payment' and RT.pAgent = 393228
|
|
AND RT.paymentMethod ='BANK DEPOSIT'
|
|
AND TS.issuedDate IS NOT NULL AND TS.dob IS NOT NULL
|
|
AND ISNULL(TS.validDate,'2000-01-01') >= CASE WHEN TS.idType <> 'National ID' THEN CAST(GETDATE() AS DATE) ELSE '2000-01-01' END
|
|
|
|
----## FOR CARD PAYMENT TRANSACTION ##----
|
|
INSERT INTO #TEMPLIST(trnDate,trnCurrency,trnAmount,trnSendPoint,trnPickupPoint,trnService,sName,sLastName,sBirthday,sCountryC,sZipCode,sRegion,sCity,sAddress
|
|
,sPhone,sIDtype,sIDnumber,sIDdate,sIDwhom,bName,bLastName,bPhone,sIDwhomCode,sResidentC,sBirthPlace,sIDtypeCode,sCountry,tCardNumber,trnRecipientCurrency
|
|
,trnReference,trnTerminalNumber
|
|
,pCountry,createdDate,controlNo
|
|
)
|
|
SELECT TOP 10
|
|
sIDwhom = 'Government'
|
|
,sIDwhomCode = ''
|
|
,sIDtype = TS.idType
|
|
,sCountry = dbo.FunCountryCode(TS.country)
|
|
,sName = (SELECT FSN.lastName1 FROM dbo.FNASplitName(Ts.firstName) AS FSN) --LastName
|
|
,sLastName = (SELECT FSN.firstName FROM dbo.FNASplitName(Ts.firstName) AS FSN) ---firstName
|
|
,sAddress = ISNULL(TS.address,TS.city)
|
|
,sIDnumber = TS.idNumber
|
|
,sIDdate = FORMAT(TS.issuedDate,'yyyyMMdd')
|
|
,sBirthday = FORMAT(TS.dob,'yyyyMMdd')
|
|
,sPhone = [dbo].FunContactAPI_MobileFormat(TS.mobile)
|
|
,sZipCode = TS.zipCode
|
|
,sRegion = TS.address
|
|
,sCity = ISNULL(TS.city,'ulaanbaatar')
|
|
,sBirthPlace = TS.nativeCountry
|
|
,sCountryC = dbo.FunCountryCode(TS.nativeCountry)
|
|
|
|
,bName = TR.lastName1+ISNULL(' '+tr.lastName2,'')
|
|
,bLastName = TR.firstName+ISNULL(' '+TR.middleName,'')
|
|
,bPhone = [dbo].FunContactAPI_MobileFormat(TR.mobile)
|
|
|
|
,trnService = '10' --CARD PAYMENT
|
|
---,trnClAmount='456.66'
|
|
,trnPickupPoint = CASE WHEN RT.payoutCurr = 'MNT' THEN AM.routingCode ELSE AM.agentCode END
|
|
,trnAmount = ROUND(RT.pAmt / ISNULL(RT.pcurrcostRate,1),2)
|
|
,trnDate = FORMAT(RT.approvedDate,'yyyyMMdd')
|
|
,trnCurrency = 'USD' --- settlement currency
|
|
,trnSendPoint = 'TAWX'------Given by contact for GME
|
|
,trnReference = dbo.FNADecryptString(RT.controlNo)
|
|
,trnTerminalNumber= '1'
|
|
|
|
,rt.pCountry,RT.createdDate,rt.controlNo
|
|
|
|
---- ONLY FOR RUSSIA CARD PAYMENT
|
|
,tCardNumber = RT.AccountNo
|
|
,trnRecipientCurrency = CASE WHEN RT.payoutCurr = 'RUB' THEN RT.payoutCurr ELSE '' END
|
|
,sResidentC = dbo.FunCountryCode(TS.country)
|
|
,sIDtypeCode = 'GB.1'
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN tranSenders TS (NOLOCK) ON TS.tranId = RT.id
|
|
INNER JOIN tranReceivers TR (NOLOCK) ON TR.tranId = RT.id
|
|
INNER JOIN agentMaster AM (NOLOCK) ON AM.agentId = RT.pBank
|
|
LEFT JOIN agentMaster BM (NOLOCK) ON BM.agentId = RT.pBankBranch
|
|
WHERE RT.approvedBy IS NOT NULL AND RT.payStatus = 'Unpaid'
|
|
AND RT.tranStatus = 'Payment' and RT.pAgent = 393228
|
|
AND RT.paymentMethod ='CARD PAYMENT'
|
|
AND TS.issuedDate IS NOT NULL AND TS.dob IS NOT NULL
|
|
AND ISNULL(TS.validDate,'2000-01-01') >= CASE WHEN TS.idType <> 'National ID' THEN CAST(GETDATE() AS DATE) ELSE '2000-01-01' END
|
|
|
|
--AND 1=2
|
|
--DELETE FROM #TEMP WHERE pCountry ='MONGOLIA' AND createdDate > '2019-07-10 14:00:00'
|
|
|
|
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='contact' AND methodName = 'PayOutgoing'
|
|
GROUP BY controlNo HAVING COUNT(1) >= 1 ) V ON V.controlNo = T.trnReference
|
|
|
|
UPDATE rt SET rt.tranStatus = 'Hold' FROM remitTran rt (NOLOCK)
|
|
INNER JOIN #TEMPLIST T ON T.controlNo = rt.controlNo
|
|
WHERE RT.payStatus = 'Unpaid' AND RT.tranStatus = 'Payment'
|
|
AND RT.pAgent = 393228 AND IsAlreadyProcessed = 1
|
|
|
|
DELETE FROM #TEMPLIST WHERE IsAlreadyProcessed = 1
|
|
|
|
ALTER TABLE #TEMPLIST DROP COLUMN controlNo,IsAlreadyProcessed
|
|
|
|
ALTER TABLE #TEMPLIST DROP COLUMN pCountry,createdDate
|
|
|
|
SELECT * FROM #TEMPLIST
|
|
|
|
RETURN
|
|
END
|
|
ELSE IF @flag='modification-request'
|
|
BEGIN
|
|
SELECT TOP 1
|
|
sIDwhom = 'Government'
|
|
,sIDtype = TS.idType
|
|
,sCountry = dbo.FunCountryCode(TS.country)
|
|
,sName = (SELECT FSN.lastName1 FROM dbo.FNASplitName(Ts.firstName) AS FSN) --LastName
|
|
,sLastName = (SELECT FSN.firstName FROM dbo.FNASplitName(Ts.firstName) AS FSN) ---firstName
|
|
,sSurName = ''
|
|
,sAddress = ISNULL(TS.address,TS.city)
|
|
,sIDnumber = TS.idNumber
|
|
,sIDdate = FORMAT(TS.issuedDate,'yyyyMMdd')
|
|
,sBirthday = FORMAT(TS.dob,'yyyyMMdd')
|
|
,sPhone = [dbo].FunContactAPI_MobileFormat(TS.mobile)
|
|
,sZipCode = TS.zipCode
|
|
,sRegion = TS.address
|
|
,sCity = ISNULL(TS.city,'ulaanbaatar')
|
|
,sBirthPlace = TS.nativeCountry
|
|
,sIDexpireDate = FORMAT(TS.validDate,'yyyyMMdd')
|
|
,sResident = CASE WHEN TS.nativeCountry = 'South Korea' THEN 1 ELSE 0 END ------Required---0 not resident, 1 resident
|
|
,sCountryStay = dbo.FunCountryCode(TS.country)
|
|
,sCountryC = dbo.FunCountryCode(TS.nativeCountry)
|
|
|
|
,bName = TR.lastName1+ISNULL(' '+tr.lastName2,'')
|
|
,bLastName = TR.firstName+ISNULL(' '+TR.middleName,'')
|
|
,bSurName = ''
|
|
,bBirthday = FORMAT(TR.dob,'yyyyMMdd')
|
|
,bCountry = dbo.FunCountryCode(TR.country)
|
|
,bIDdate = FORMAT(tr.issuedDate,'yyyyMMdd')
|
|
,bIDwhom = 'Governmnet'
|
|
,bResident = CASE WHEN TR.country = RT.pCountry THEN 1 ELSE 0 END
|
|
,bBirthPlace = TR.country
|
|
,bPhone = [dbo].FunContactAPI_MobileFormat(TR.mobile)
|
|
|
|
,trnService = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' THEN '5' WHEN RT.paymentMethod ='Transfer To Card' THEN '10' ELSE '2' END
|
|
---,trnClAmount='456.66'
|
|
,trnPickupPoint = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' AND RT.payoutCurr='MNT' THEN AM.routingCode ELSE AM.agentCode END
|
|
,trnAmount = ROUND(RT.pAmt / ISNULL(RT.pcurrcostRate,1),2)
|
|
,trnDate = FORMAT(RT.approvedDate,'yyyyMMdd')
|
|
,trnCurrency = 'USD' --- settlement currency
|
|
,trnSendPoint = 'TAWX'------Given by contact for GME
|
|
,trnRate = RT.customerRate------to be checked
|
|
,trnReference = dbo.FNADecryptString(RT.controlNo)
|
|
,trnTerminalNumber= '1'
|
|
,TransactionID = RT.contNo
|
|
,trnPayoutAmount = CASE WHEN RT.payoutCurr='MNT' THEN CONVERT(FLOAT,RT.pAmt) ELSE '' END
|
|
,trnPayoutCurrency = CASE WHEN RT.payoutCurr='MNT' THEN RT.payoutCurr ELSE '' END
|
|
,trnPayoutRate = CASE WHEN RT.payoutCurr='MNT' THEN RT.pcurrcostRate ELSE '' END
|
|
,bAccount = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' THEN RT.AccountNo ELSE '' END
|
|
,bBankName = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' THEN RT.pBankName ELSE '' END
|
|
,providerName = 'CONTACT'
|
|
,rt.payStatus
|
|
,GmeControlNo = dbo.FNADecryptString(controlNo)
|
|
|
|
---- ONLY FOR RUSSIA CARD PAYMENT
|
|
--,tCardNumber = CASE WHEN RT.paymentMethod ='Transfer To Card' THEN RT.AccountNo ELSE '' END
|
|
--,trnRecipientCurrency = CASE WHEN RT.paymentMethod ='Transfer To Card' AND RT.payoutCurr = 'RUB' THEN RT.payoutCurr ELSE '' END
|
|
--,sResidentC = CASE WHEN RT.paymentMethod ='Transfer To Card' THEN dbo.FunCountryCode(TS.country) ELSE '' END
|
|
--,sIDtypeCode = CASE WHEN RT.payoutCurr = 'RUB' THEN 'GB.1' ELSE '' END
|
|
|
|
----ONLY FOR RUSSIA BANK DEPOSIT
|
|
,tRuBic = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' AND RT.payoutCurr = 'RUB' THEN BM.agentCode ELSE '' END
|
|
,tRuAcc = CASE WHEN RT.paymentMethod ='BANK DEPOSIT' AND RT.payoutCurr = 'RUB' THEN RT.AccountNo ELSE '' END
|
|
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
INNER JOIN tranSenders TS (NOLOCK) ON TS.tranId = RT.id
|
|
INNER JOIN tranReceivers TR (NOLOCK) ON TR.tranId = RT.id
|
|
INNER JOIN agentMaster AM (NOLOCK) ON AM.agentId = RT.pBank
|
|
LEFT JOIN agentMaster BM (NOLOCK) ON BM.agentId = RT.pBankBranch
|
|
WHERE RT.approvedBy IS NOT NULL AND RT.payStatus in('Post')
|
|
and RT.pAgent = 393228
|
|
AND TS.issuedDate is not null and TS.dob IS NOT NULL
|
|
AND RT.tranStatus = 'ModificationRequest'
|
|
AND RT.controlNo = DBO.FNAEncryptString(@ControlNo)
|
|
RETURN
|
|
END
|
|
ELSE IF @flag='sync-list-Contact'
|
|
BEGIN
|
|
SELECT RT.id AS TranId, RT.ContNo AS DocId
|
|
FROM dbo.remitTran AS RT(NOLOCK)
|
|
WHERE RT.pAgent = 393228
|
|
AND RT.tranStatus='Payment'
|
|
and RT.payStatus='Post'
|
|
END
|
|
ELSE IF @flag='mark-paid-contact'
|
|
BEGIN
|
|
UPDATE remitTran
|
|
SET payStatus = 'Paid'
|
|
,tranStatus = 'Paid'
|
|
,paidDate = getdate()
|
|
,paidDateLocal = GETUTCDATE()
|
|
,paidBy = 'Scheduler'
|
|
WHERE id = @id AND payStatus = 'Post'
|
|
AND tranStatus = 'payment' AND pAgent = 393228
|
|
|
|
SELECT '0' ErrorCode,'Update success' Msg, NULL Id
|
|
END
|
|
ELSE IF @flag='mark-post-contact'
|
|
BEGIN
|
|
IF EXISTS(SELECT TOP 1 'X' FROM remitTran(NOLOCK) WHERE controlNo = Dbo.FNAEncryptString(@ControlNo) AND pAgent = 393228 AND tranStatus = 'Modification')
|
|
BEGIN
|
|
UPDATE remitTran SET
|
|
tranStatus = 'Payment'
|
|
WHERE controlNo = Dbo.FNAEncryptString(@ControlNo)
|
|
AND pAgent = 393228 AND tranStatus = 'Modification' AND payStatus = 'Post'
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
UPDATE remitTran SET
|
|
payStatus = 'Post'
|
|
,postedBy = 'system'
|
|
,postedDate = GETDATE()
|
|
,postedDateLocal= GETUTCDATE()
|
|
,controlNo2 = controlNo
|
|
,ContNo = @id
|
|
WHERE controlNo = Dbo.FNAEncryptString(@ControlNo)
|
|
AND pAgent = 393228 AND tranStatus = 'payment' AND payStatus = 'Unpaid'
|
|
END
|
|
SELECT '0' ErrorCode,'Update success' Msg, NULL Id
|
|
END
|
|
END
|
|
|
|
GO
|