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.
352 lines
17 KiB
352 lines
17 KiB
|
|
--DROP TABLE #temp
|
|
-- Step 1: Get all the transactions in temp table
|
|
SELECT REFNO
|
|
,1 sCurrCostRate ,0 sMargin,0 sMargin1
|
|
,CASE WHEN RECEIVERCOUNTRY IN ('VIETNAM') THEN 0
|
|
WHEN RECEIVERCOUNTRY = 'NEPAL' THEN xm_exRate
|
|
ELSE xm_exRate
|
|
END pCurrCostRate
|
|
,CASE WHEN RECEIVERCOUNTRY IN ('VIETNAM') THEN 0
|
|
WHEN RECEIVERCOUNTRY = 'NEPAL' THEN ROUND(xm_exRate - today_dollar_rate, 4)
|
|
ELSE ROUND(xm_exRate - bonus_value_amount, 4)
|
|
END pCurrMargin
|
|
,0 pCurrMargin1
|
|
--,CASE WHEN RECEIVERCOUNTRY IN ('NEPAL', 'VIETNAM') THEN round((ExchangeRate*Today_Dollar_rate), 4) ELSE ROUND(bonus_value_amount, 4) END pCurrCostRate
|
|
--, CASE WHEN RECEIVERCOUNTRY IN ('NEPAL', 'VIETNAM') THEN 0 ELSE -1*ROUND(xm_exRate - bonus_value_amount, 4) END pCurrMargin, 0 pCurrMargin1
|
|
,CASE WHEN RECEIVERCOUNTRY IN ('VIETNAM') THEN Today_Dollar_rate
|
|
WHEN RECEIVERCOUNTRY = 'NEPAL' THEN today_dollar_rate
|
|
ELSE bonus_value_amount
|
|
END custRate
|
|
,SCharge,senderCommission,'GBP' sCommCurr,agent_receiverSCommission pAgentComm,pAgentCommCurr = CASE WHEN RECEIVERCOUNTRY IN ('VIETNAM') THEN 'VND' ELSE 'GBP' END
|
|
,'United Kingdom' sCountry1,393877 sSupAgent,'IME London' SSupAgentName
|
|
,sAgent = case when agentid=11000035 then 394395 else 394390 end
|
|
,sAgentName = case when agentid=11000035 then 'IME Mobile' else 'IME London Head Office' end
|
|
,sBranch = case when agentid=11000035 then 394395 else 394390 end
|
|
,SBRANCHNAME = case when agentid=11000035 then 'IME Mobile' else 'IME London Head Office' end
|
|
,RECEIVERCOUNTRY
|
|
,psagentName = CASE WHEN rbankname IN ('Best Remit', 'GME REMIT', 'GME Remittance', 'GME Remittance - GBP Sett Ac', 'IME PAY/GME', 'NPR Deposit') THEN 'GME Nepal'
|
|
WHEN rbankname IS NULL THEN 'GME Nepal'
|
|
WHEN rbankname IN ('GCC - UAE', 'GCC AUS', 'GCC India', 'GCC Pakistan', 'GCC UAE', 'GCC_Philippines', 'GCC_Portugal', 'GCC_USA', 'GCCBangladesh',
|
|
'GCCHK', 'GCCINDIA', 'GCCPak', 'GCCSRI', 'GCCSRILANKA', 'GCCUAE') THEN 'GCC'
|
|
WHEN rbankname IN (' TRANGLO - AUSTRALIA', 'TRANGLO - BANGLADESH', 'TRANGLO - INDIA', 'TRANGLO - PAKISTAN', 'TRANGLO - SRILANKA', 'Tranglo-Nepal') THEN 'Tranglo Sdn. Bhd.'
|
|
WHEN rbankname LIKE '%TRANGLO - AUSTRALIA' THEN 'Tranglo Sdn. Bhd.'
|
|
WHEN rbankname IN ('IME REMIT') THEN 'IME REMIT'
|
|
WHEN rbankname IN ('Global IME') THEN 'Global IME Bank'
|
|
WHEN rbankname IN ('MACHHAPUCHHRE BANK') THEN 'MACHHAPUCHHRE BANK'
|
|
WHEN rbankname IN ('CT Xpress') THEN 'CT Xpress'
|
|
WHEN rbankname IN ('CITIZEN BANK INTERNATIONAL') THEN 'CITIZEN BANK INTERNATIONAL'
|
|
END
|
|
, psagetn = CASE WHEN rbankname IN ('Best Remit', 'GME REMIT', 'GME Remittance', 'GME Remittance - GBP Sett Ac', 'IME PAY/GME', 'NPR Deposit') THEN 394397
|
|
WHEN rbankname IS NULL THEN '394397'
|
|
WHEN rbankname IN ('GCC - UAE', 'GCC AUS', 'GCC India', 'GCC Pakistan', 'GCC UAE', 'GCC_Philippines', 'GCC_Portugal', 'GCC_USA', 'GCCBangladesh',
|
|
'GCCHK', 'GCCINDIA', 'GCCPak', 'GCCSRI', 'GCCSRILANKA', 'GCCUAE') THEN 394452
|
|
WHEN rbankname IN (' TRANGLO - AUSTRALIA', 'TRANGLO - BANGLADESH', 'TRANGLO - INDIA', 'TRANGLO - PAKISTAN', 'TRANGLO - SRILANKA', 'Tranglo-Nepal') THEN 394465
|
|
WHEN rbankname LIKE '%TRANGLO - AUSTRALIA' THEN 394450
|
|
WHEN rbankname IN ('IME REMIT') THEN 394454
|
|
WHEN rbankname IN ('Global IME') THEN 394453
|
|
WHEN rbankname IN ('MACHHAPUCHHRE BANK') THEN 394464
|
|
WHEN rbankname IN ('CT Xpress') THEN 394473
|
|
WHEN rbankname IN ('CITIZEN BANK INTERNATIONAL') THEN 394455
|
|
END
|
|
, pagentName = CASE WHEN rbankname IN ('Best Remit', 'GME REMIT', 'GME Remittance', 'GME Remittance - GBP Sett Ac', 'NPR Deposit') THEN 'GME Nepal Agent'--394398
|
|
WHEN rbankname IS NULL THEN 'GME Nepal Agent'--394398
|
|
WHEN rbankname = 'IME PAY/GME' THEN 'IME Remit'--394469
|
|
|
|
WHEN rbankname = 'GCC - UAE' THEN 'GCC Remit'--394456
|
|
WHEN rbankname = 'GCC AUS' THEN 'GCC Remit'--394470
|
|
WHEN rbankname = 'GCC India' THEN 'GCC Remit'--394453
|
|
WHEN rbankname = 'GCC Pakistan' THEN 'GCC Remit'--394455
|
|
WHEN rbankname = 'GCC UAE' THEN 'GCC Remit'--394456
|
|
WHEN rbankname = 'GCC_Philippines' THEN 'GCC Remit'--394457
|
|
WHEN rbankname = 'GCC_Portugal' THEN 'GCC Remit'--394458
|
|
WHEN rbankname = 'GCC_USA' THEN 'GCC Remit'--394459
|
|
WHEN rbankname = 'GCCBangladesh' THEN 'GCC Remit'--394454
|
|
WHEN rbankname = 'GCCHK' THEN 'GCC Remit'--394460
|
|
WHEN rbankname = 'GCCINDIA' THEN 'GCC Remit'--394453
|
|
WHEN rbankname = 'GCCPak' THEN 'GCC Remit'--394455
|
|
WHEN rbankname = 'GCCSRI' THEN 'GCC Remit'--394461
|
|
WHEN rbankname = 'GCCSRILANKA' THEN 'GCC Remit'--394461
|
|
WHEN rbankname = 'GCCUAE' THEN 'GCC Remit'--394456
|
|
|
|
WHEN rbankname LIKE '%TRANGLO - AUSTRALIA' THEN 'TRANGLO SDN BHD'--394483
|
|
WHEN rbankname = 'TRANGLO - BANGLADESH' THEN 'TRANGLO SDN BHD'--394478
|
|
WHEN rbankname = 'TRANGLO - INDIA' THEN 'TRANGLO - INDIA'--394479
|
|
WHEN rbankname = 'TRANGLO - PAKISTAN' THEN 'TRANGLO SDN BHD'--394480
|
|
WHEN rbankname = 'TRANGLO - SRILANKA' THEN 'TRANGLO SDN BHD'--394481
|
|
WHEN rbankname = 'Tranglo-Nepal' THEN 'TRANGLO SDN BHD'--394482
|
|
|
|
WHEN rbankname = 'IME REMIT' THEN 'IME Remit Settling Agent'--394468
|
|
|
|
WHEN rbankname = 'IME REMIT' THEN 'IME Remit Settling Agent'--394468
|
|
WHEN rbankname = 'Global IME' THEN 'Global IME'--394466
|
|
|
|
WHEN rbankname = 'MACHHAPUCHHRE BANK' THEN 'MACHHAPUCHHRE'--394467
|
|
|
|
WHEN rbankname = 'CT Xpress' THEN 'CT Xpress Sett Agent'--394476
|
|
WHEN rbankname = 'CITIZEN BANK INTERNATIONAL' THEN 'CITIZEN BANK INTERNATIONAL Sett Agent'--394475
|
|
END
|
|
, pagent = CASE WHEN rbankname IN ('Best Remit', 'GME REMIT', 'GME Remittance', 'GME Remittance - GBP Sett Ac', 'NPR Deposit') THEN 394398
|
|
WHEN rbankname IS NULL THEN 394398
|
|
WHEN rbankname = 'IME PAY/GME' THEN 394454
|
|
|
|
WHEN rbankname = 'GCC - UAE' THEN 394459
|
|
WHEN rbankname = 'GCC AUS' THEN 394459
|
|
WHEN rbankname = 'GCC India' THEN 394459
|
|
WHEN rbankname = 'GCC Pakistan' THEN 394459
|
|
WHEN rbankname = 'GCC UAE' THEN 394459
|
|
WHEN rbankname = 'GCC_Philippines' THEN 394459
|
|
WHEN rbankname = 'GCC_Portugal' THEN 394459
|
|
WHEN rbankname = 'GCC_USA' THEN 394459
|
|
WHEN rbankname = 'GCCBangladesh' THEN 394459
|
|
WHEN rbankname = 'GCCHK' THEN 394459
|
|
WHEN rbankname = 'GCCINDIA' THEN 394459
|
|
WHEN rbankname = 'GCCPak' THEN 394459
|
|
WHEN rbankname = 'GCCSRI' THEN 394459
|
|
WHEN rbankname = 'GCCSRILANKA' THEN 394459
|
|
WHEN rbankname = 'GCCUAE' THEN 394459
|
|
|
|
WHEN rbankname LIKE '%TRANGLO - AUSTRALIA' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - BANGLADESH' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - INDIA' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - PAKISTAN' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - SRILANKA' THEN 394460
|
|
WHEN rbankname = 'Tranglo-Nepal' THEN 394460
|
|
|
|
WHEN rbankname = 'IME REMIT' THEN 394454
|
|
|
|
WHEN rbankname = 'IME REMIT' THEN 394454
|
|
WHEN rbankname = 'Global IME' THEN 394453
|
|
|
|
WHEN rbankname = 'MACHHAPUCHHRE BANK' THEN 394467
|
|
|
|
WHEN rbankname = 'CT Xpress' THEN 394452
|
|
WHEN rbankname = 'CITIZEN BANK INTERNATIONAL' THEN 394475
|
|
END
|
|
|
|
, pbranch = CASE WHEN rbankname IN ('Best Remit', 'GME REMIT', 'GME Remittance', 'GME Remittance - GBP Sett Ac', 'NPR Deposit') THEN 394398
|
|
WHEN rbankname IS NULL THEN 394398
|
|
WHEN rbankname = 'IME PAY/GME' THEN 394469
|
|
|
|
WHEN rbankname = 'GCC - UAE' THEN 394459
|
|
WHEN rbankname = 'GCC AUS' THEN 394459
|
|
WHEN rbankname = 'GCC India' THEN 394459
|
|
WHEN rbankname = 'GCC Pakistan' THEN 394459
|
|
WHEN rbankname = 'GCC UAE' THEN 394459
|
|
WHEN rbankname = 'GCC_Philippines' THEN 394459
|
|
WHEN rbankname = 'GCC_Portugal' THEN 394459
|
|
WHEN rbankname = 'GCC_USA' THEN 394459
|
|
WHEN rbankname = 'GCCBangladesh' THEN 394459
|
|
WHEN rbankname = 'GCCHK' THEN 394459
|
|
WHEN rbankname = 'GCCINDIA' THEN 394459
|
|
WHEN rbankname = 'GCCPak' THEN 394459
|
|
WHEN rbankname = 'GCCSRI' THEN 394459
|
|
WHEN rbankname = 'GCCSRILANKA' THEN 394461
|
|
WHEN rbankname = 'GCCUAE' THEN 394456
|
|
|
|
WHEN rbankname LIKE '%TRANGLO - AUSTRALIA' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - BANGLADESH' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - INDIA' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - PAKISTAN' THEN 394460
|
|
WHEN rbankname = 'TRANGLO - SRILANKA' THEN 394460
|
|
WHEN rbankname = 'Tranglo-Nepal' THEN 394460
|
|
|
|
WHEN rbankname = 'IME REMIT' THEN 394454
|
|
|
|
WHEN rbankname = 'Global IME' THEN 394453
|
|
|
|
WHEN rbankname = 'MACHHAPUCHHRE BANK' THEN 394467
|
|
|
|
WHEN rbankname = 'CT Xpress' THEN 394452
|
|
WHEN rbankname = 'CITIZEN BANK INTERNATIONAL' THEN 394451
|
|
END
|
|
,paymentType = CASE paymentType WHEN 'Cash Pay' THEN 'CASH PAYMENT'
|
|
WHEN 'Wallet' THEN 'Mobile Wallet'
|
|
ELSE 'BANK DEPOSIT' END
|
|
,ben_bank_id ben_bank_id_BANK,ben_bank_name ben_bank_name_BANK,ben_bank_id,ben_bank_name,rBankACNo
|
|
,collMode = 'Bank Deposit'
|
|
,deposittype = CASE WHEN SenderBankName IN ('Debit Card', 'Debit Card (Good rate)', 'Debit Card - Worldpay (MOTO)', 'Debit Card Payment', 'Debit Card(Good Rate)') THEN 'DEBIT_CARD'
|
|
WHEN SenderBankName IN ('E-banking', 'E-banking (Good rate)', 'E-banking(Good Rate)', 'E-Banking/ (Good rate)', 'E-banking/Online Banking', 'E-banking/Online banking (best rate)') THEN 'EBANKING'
|
|
ELSE 'ONLINE' END
|
|
,'GBP' collCurr,paidAmt,(PAIDAMT-ISNULL(SCharge,0)) tamt,receiveAmt, TotalRoundAmt,receiveCType,ReceiverRelation,reason_for_remittance,source_of_income
|
|
,'Hold' tStatus,'Unpaid' pStatus,dot+dottime sTime,sempid,confirmDate,approve_by
|
|
,tType = CASE WHEN BRANCH = 'IME London - CSR Panel' THEN 'I'
|
|
WHEN BRANCH = 'Webonline Branch' THEN 'M'
|
|
WHEN BRANCH IN ('Mobile APP API - IOS', 'Mobile APP API - Android') THEN 'M'
|
|
END
|
|
,isonlinTxn=
|
|
CASE WHEN BRANCH = 'IME London - CSR Panel' THEN ''
|
|
WHEN BRANCH = 'Webonline Branch' THEN 'O'
|
|
WHEN BRANCH IN ('Mobile APP API - IOS', 'Mobile APP API - Android') THEN 'M'
|
|
END
|
|
,m.SenderName,m.ReceiverName,0 agentCrossSettRate
|
|
,0 agentFxGain,null controlNo2,CASE WHEN ISNULL(SCharge, 0) = 0 THEN 0 ELSE 1 END isScMaunal, 0 ScMaunal, NULL promotionCode,Tranno --txn
|
|
,m.SenderName firstName, m.SenderName fullName, 'United Kingdom' sCountry, senderstate, sendercity,senderaddress,senderemail,sender_mobile,SenderPhoneno,SenderNativeCountry,senderFax,senderPassport,ID_Issue_date,senderVisa,ip_address,date_of_birth,SENDERZIPCODE,customer_sno
|
|
--receiver
|
|
,m.receivername rfirstName,m.receivername rFullName,receivercountry rCountry,receiveraddress,receiver_mobile,ReceiverIDDescription,ReceiverID,ReceiverRelation rRel,receiver_sno
|
|
INTO #TEMP
|
|
FROM IME_UK_DB.dbo.moneySend m
|
|
LEFT JOIN remitTran rt on rt.uploadLogId= m.Tranno
|
|
WHERE m.status='paid' and RT.controlno is null
|
|
|
|
--WHERE DOT between '2019-09-01' and '2019-09-20 23:59:59'
|
|
|
|
select refno,* from #TEMP
|
|
|
|
--Step 2: Insert into tran table (Temp)
|
|
INSERT INTO FastMoneyPro_Remit.dbo.remitTran
|
|
(
|
|
controlNo,sCurrCostRate,sCurrHoMargin,sCurrAgentMargin,pCurrCostRate,pCurrHoMargin,pCurrAgentMargin,customerRate,
|
|
serviceCharge,sAgentComm,sAgentCommCurrency,pAgentComm,pAgentCommCurrency,sCountry,sSuperAgent,sSuperAgentName,sAgent,sAgentName,sBranch,SBRANCHNAME,
|
|
pCountry,pSuperAgent,pSuperAgentName,pAgent,pAgentName,pBranch,pBranchName,
|
|
paymentMethod,
|
|
pBank,pBankName,pBankBranch,pBankBranchName,accountNo,
|
|
collMode,collCurr,cAmt,TAMT,pAmt,payoutCurr,relWithSender,purposeOfRemit,sourceOfFund,tranStatus,
|
|
payStatus,createdDate,createdBy,approvedDate,approvedBy,tranType,senderName,receiverName,agentCrossSettRate,
|
|
agentFxGain,controlNo2,isScMaunal,originalSC,promotionCode,uploadLogId,deposittype
|
|
)
|
|
|
|
SELECT REFNO
|
|
, sCurrCostRate,0 sMargin,0 sMargin1
|
|
, pCurrCostRate
|
|
, pCurrMargin, 0 pCurrMargin1
|
|
, custRate
|
|
,SCharge,senderCommission, sCommCurr,pAgentComm, pAgentCommCurr, sCountry1, sSupAgent, SSupAgentName,sAgent,sAgentName,sBranch,SBRANCHNAME
|
|
,RECEIVERCOUNTRY
|
|
,psagetn
|
|
,psagentName
|
|
,pagent
|
|
,pagentName
|
|
,pbranch
|
|
,''
|
|
,paymentType
|
|
,ben_bank_id_BANK = case when isnumeric(ben_bank_id) = 1 then ben_bank_id else 0 end,ben_bank_name ben_bank_name_BANK,ben_bank_id = case when isnumeric(ben_bank_id) = 1 then ben_bank_id else 0 end,ben_bank_name,rBankACNo
|
|
,collMode,collCurr,paidAmt, tamt,floor(receiveAmt) --TotalRoundAmt
|
|
,receiveCType,ReceiverRelation,reason_for_remittance,source_of_income
|
|
,tStatus, pStatus, sTime,sempid,confirmDate,approve_by, tType,SenderName,ReceiverName,0 agentCrossSettRate
|
|
, agentFxGain, controlNo2, isScMaunal, ScMaunal, promotionCode,Tranno, deposittype
|
|
from #temp
|
|
|
|
|
|
INSERT INTO FastMoneyPro_Remit.dbo.tranSenders
|
|
(
|
|
tranId,customerId,firstName,fullName,country,[state],city,address,email,
|
|
homePhone,mobile,nativeCountry,idType,idNumber,issuedDate,validDate,ipAddress,dob,zipCode,extCustomerId
|
|
)
|
|
|
|
SELECT
|
|
r.id,0,t.firstName, fullName, t.sCountry, senderstate, sendercity,senderaddress,senderemail,sender_mobile,SenderPhoneno
|
|
,SenderNativeCountry,senderFax,senderPassport,ID_Issue_date,senderVisa,ip_address,date_of_birth,SENDERZIPCODE,customer_sno
|
|
from #TEMP t
|
|
inner join FastMoneyPro_Remit.dbo.remitTran r (nolock)on r.uploadLogId=t.Tranno
|
|
|
|
|
|
--ALTER TABLE tranReceivers
|
|
--ADD oldSysCustId BIGINT NULL
|
|
|
|
INSERT INTO FastMoneyPro_Remit.dbo.tranReceivers
|
|
(
|
|
tranId,customerId,firstName,fullName,country,[address],mobile,idType,idNumber,relationType,oldSysCustId
|
|
)
|
|
|
|
SELECT
|
|
r.id,0,rfirstName,rFullName,receivercountry rCountry,receiveraddress,receiver_mobile,ReceiverIDDescription,ReceiverID,ReceiverRelation rRel,receiver_sno
|
|
from #TEMP t
|
|
inner join FastMoneyPro_Remit.dbo.remitTran r (nolock)on r.uploadLogId=t.Tranno
|
|
|
|
--Step 3: update TranSendersTemp, TranReceiversTemp customer and receiver id
|
|
|
|
UPDATE T SET T.CUSTOMERID = C.CUSTOMERID, T.MEMBERSHIPID = C.MEMBERSHIPID
|
|
FROM FastMoneyPro_Remit.dbo.tranSenders T(NOLOCK)
|
|
inner join FastMoneyPro_Remit.dbo.remitTran r (nolock)on r.id=t.tranId
|
|
INNER JOIN #TEMP tt on tt.Tranno= r.uploadLogId
|
|
LEFT JOIN FastMoneyPro_Remit.dbo.customerMaster C(NOLOCK) ON C.OBPID = T.extCustomerId
|
|
|
|
|
|
UPDATE T SET T.CUSTOMERID=R.RECEIVERID, T.MEMBERSHIPID=R.MEMBERSHIPID
|
|
FROM FastMoneyPro_Remit.dbo.TRANRECEIVERs T(NOLOCK)
|
|
inner join FastMoneyPro_Remit.dbo.remitTran rt (nolock)on rt.id=t.tranId
|
|
INNER JOIN #TEMP tt on tt.Tranno= rt.uploadLogId
|
|
LEFT JOIN FastMoneyPro_Remit.dbo.RECEIVERINFORMATION R(NOLOCK) ON R.tempRId = T.oldSysCustId
|
|
|
|
--Step 4: update sending agent and sending referral code
|
|
select distinct createdby into #temp from remittrantemp
|
|
|
|
select t.createdby, am.agentname, am.agentId from #temp t
|
|
inner join applicationusers a on a.username = t.createdby
|
|
inner join agentmaster am on am.agentid = a.agentid
|
|
|
|
--if all users exists
|
|
update t set t.sagentname = am.agentname, t.sagent = am.agentId,
|
|
t.sbranchname = am.agentname, t.sbranch = am.agentId
|
|
from FastMoneyPro_Remit.dbo.remittran t(NOLOCK)
|
|
inner join FastMoneyPro_Remit.dbo.applicationusers a(NOLOCK) on a.username = t.createdby
|
|
inner join FastMoneyPro_Remit.dbo.agentmaster am(NOLOCK) on am.agentid = a.agentid
|
|
|
|
INSERT INTO FastMoneyPro_Remit.dbo.controlNoList(controlNo)
|
|
SELECT controlno from FastMoneyPro_Remit.dbo.remittran rt
|
|
INNER JOIN #TEMP tt on tt.Tranno= rt.uploadLogId
|
|
--Step 5: update real transaction status from inficare
|
|
|
|
update r set r.paiddate = s.paidDate, r.paidby = s.paidby,
|
|
r.transtatus = case when s.transstatus in ('Cancel', 'CancelHOLD') then 'Cancel'
|
|
when s.transstatus = 'Payment' and s.status = 'Paid' then 'Paid'
|
|
when s.transstatus = 'Payment' and s.status = 'Post' then 'Post'
|
|
when s.transstatus = 'Payment' and s.status = 'Un-Paid' then 'Payment'
|
|
when s.transstatus = 'Commit-Hold' and s.status = 'Un-Paid' then 'Payment'
|
|
end
|
|
,r.paystatus = case when s.transstatus = 'Payment' and s.status = 'Paid' then 'Paid'
|
|
else 'unpaid'
|
|
end
|
|
,r.cancelapproveddate = s.cancel_date, r.cancelapprovedby = 'system', r.holdtranid=r.id
|
|
from FastMoneyPro_Remit.dbo.remittran r
|
|
inner join IME_UK_DB.dbo.moneySend s on s.tranno = r.uploadlogid
|
|
INNER JOIN #TEMP tt on tt.Tranno= r.uploadLogId
|
|
--where r.CREATEDDATE BETWEEN '2019-04-01' and GETDATE()
|
|
|
|
|
|
|
|
INSERT INTO FastMoneyPro_Remit.dbo.tranViewHistory (
|
|
agentId
|
|
,tranViewType
|
|
,createdBy
|
|
,createdDate
|
|
,tranId
|
|
,remarks
|
|
,controlnumber
|
|
)
|
|
SELECT
|
|
'394390'
|
|
,'TransactionNotes'
|
|
,'system'
|
|
,GETDATE()
|
|
,rt.id
|
|
,t.comments
|
|
,rt.controlno
|
|
|
|
FROM IME_UK_DB.dbo. TransactionNotes t inner join FastMoneyPro_Remit.dbo.remittran rt on t.RefNo= rt.controlno
|
|
INNER JOIN #TEMP tt on tt.Tranno= rt.uploadLogId
|
|
|
|
INSERT INTO FastMoneyPro_Remit.dbo.tranModifyLog (
|
|
tranId
|
|
,controlNo
|
|
,message
|
|
,createdBy
|
|
,createdDate
|
|
,MsgType
|
|
,status
|
|
,resolvedDate
|
|
)
|
|
SELECT
|
|
rt.id
|
|
,dbo.encryptdb(rt.controlno )
|
|
,t.comments
|
|
,t.uploadBy
|
|
,t.DatePosted
|
|
,'C'
|
|
,'Resolved'
|
|
,GETDATE()
|
|
FROM IME_UK_DB.dbo.TransactionNotes t
|
|
inner join FastMoneyPro_Remit.dbo.remittran rt on t.RefNo= rt.controlno
|
|
INNER JOIN #TEMP tt on tt.Tranno= rt.uploadLogId
|
|
|