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.
87 lines
2.5 KiB
87 lines
2.5 KiB
alter PROCEDURE Proc_Get_Transaction_Cebuana(
|
|
@controlNo VARCHAR(100) = NULL
|
|
,@flag VARCHAR(20)
|
|
)AS
|
|
BEGIN
|
|
DECLARE @decryptedControlNo VARCHAR(100),@tranId BIGINT,@partnerId BIGINT
|
|
SELECT @tranId=id,@partnerId=pSuperAgent FROM dbo.vwRemitTran(NOLOCK) WHERE controlNo=@controlNo
|
|
IF @flag='tran'
|
|
BEGIN
|
|
|
|
SET @decryptedControlNo=dbo.FNADecryptString(@controlNo)
|
|
SELECT '0' ErrorCode, 'Can process the transaction' Msg, @decryptedControlNo Id
|
|
|
|
SELECT rt.id
|
|
,controlNo=dbo.FNADecryptString(rt.controlNo)
|
|
,sCountryIso3=cms.isoAlpha3
|
|
,pCountryIso3=cmp.isoAlpha3
|
|
,rt.pAgent
|
|
,rt.pAgentName
|
|
,rt.pBranch
|
|
,rt.pBranchName
|
|
,rt.paymentMethod
|
|
,BankCode= am.BANKCODE
|
|
,rt.pBankName
|
|
,rt.pBankBranch
|
|
,rt.pBankBranchName
|
|
,rt.accountNo
|
|
,rt.externalBankCode
|
|
,rt.collMode
|
|
,rt.collCurr
|
|
,pAmt =FORMAT(rt.pAmt, '0.00')
|
|
,rt.payoutCurr
|
|
,createdDate =FORMAT(rt.createdDate,'yyyy-MM-dd hh:mm:ss')
|
|
FROM dbo.vwRemitTran(NOLOCK) rt
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cms ON rt.sCountry=cms.countryName
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cmp ON rt.pCountry=cmp.countryName
|
|
INNER JOIN agentMaster(NOLOCK) am ON am.agentId=rt.pBank
|
|
WHERE rt.id=@tranId
|
|
|
|
SELECT firstName=(SELECT firstName FROM dbo.FNASplitName(ts.firstName) )
|
|
,middleName=(SELECT middleName FROM dbo.FNASplitName(ts.firstName) )
|
|
,lastName1=(SELECT lastName1 FROM dbo.FNASplitName(ts.firstName) )
|
|
,lastName2=(SELECT lastName2 FROM dbo.FNASplitName(ts.firstName) )
|
|
,ts.fullName
|
|
,ts.address
|
|
,ts.STATE
|
|
,ts.address2
|
|
FROM dbo.vwtranSenders (NOLOCK)ts
|
|
WHERE ts.tranid=@tranId
|
|
|
|
SELECT tr.firstName
|
|
,tr.middleName
|
|
,tr.lastName1
|
|
,tr.lastName2
|
|
,tr.fullName
|
|
,tr.address
|
|
,tr.address2
|
|
FROM dbo.vwtranReceivers (NOLOCK)tr
|
|
WHERE
|
|
tr.tranid=@tranId
|
|
END
|
|
ELSE IF @flag='getpayout'
|
|
BEGIN
|
|
SELECT paymentMethod , controlNo=dbo.FNADecryptString(controlNo),controlNo2=dbo.FNADecryptString(controlNo2) FROM dbo.remitTran(NOLOCK) WHERE id=@tranId
|
|
END
|
|
ELSE IF @flag='update'
|
|
BEGIN
|
|
SELECT controlNo=dbo.FNADecryptString(tr.controlNo)
|
|
,ts.firstName
|
|
,ts.lastName1
|
|
,tl.message
|
|
FROM dbo.remitTran(NOLOCK) tr
|
|
INNER JOIN dbo.tranReceivers (NOLOCK) ts ON ts.tranId = tr.id
|
|
left JOIN tranmodifylog tl ON tl.tranId = tr.id
|
|
WHERE tr.id=@tranId and [status]='Not Resolved'
|
|
END
|
|
IF @flag='Cancel'
|
|
BEGIN
|
|
SELECT TOP 1 controlNo=dbo.FNADecryptString(tr.controlNo)
|
|
,cancelReason=tr.cancelReason
|
|
FROM dbo.remitTran(NOLOCK) tr
|
|
WHERE tr.id=@tranId
|
|
END
|
|
|
|
END
|
|
|
|
|