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.
341 lines
11 KiB
341 lines
11 KiB
SET QUOTED_IDENTIFIER ON
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE PROC_GET_TRANSACTION_MONEYGRAM(
|
|
@controlNo VARCHAR(100) = NULL
|
|
,@tranId BIGINT = NULL
|
|
,@agentId VARCHAR(20) = NULL
|
|
,@flag VARCHAR(20)
|
|
)
|
|
AS
|
|
SET NOCOUNT ON;
|
|
BEGIN
|
|
IF @flag='tran'
|
|
BEGIN
|
|
DECLARE @decryptedControlNo VARCHAR(100),@partnerId BIGINT = NULL
|
|
SET @decryptedControlNo=dbo.FNADecryptString(@controlNo)
|
|
|
|
|
|
DECLARE @PartnerRemitPurposeMapping TABLE(DefaultValue VARCHAR(100) NOT NULL,PartnerValue VARCHAR(100) NULL)
|
|
DECLARE @PartnerFundSourceMapping TABLE(DefaultValue VARCHAR(100) NOT NULL,PartnerValue VARCHAR(100) NULL)
|
|
DECLARE @PartnerIdTypeMapping TABLE(DefaultValue VARCHAR(100) NOT NULL,PartnerValue VARCHAR(100) NULL)
|
|
DECLARE @PartnerRelationShipMapping TABLE(DefaultValue VARCHAR(100) NOT NULL,PartnerValue VARCHAR(100) NULL)
|
|
DECLARE @PartnerOccupationMapping TABLE(DefaultValue VARCHAR(100) NOT NULL,PartnerValue VARCHAR(100) NULL, Detail VARCHAR(200) NULL)
|
|
|
|
INSERT INTO @PartnerRemitPurposeMapping(DefaultValue,PartnerValue)
|
|
SELECT 'Family maintenance','MAINTENANCE_EXPENSES' UNION ALL
|
|
SELECT 'Savings','OTHER' UNION ALL
|
|
SELECT 'Salary / Commission','OTHER'
|
|
|
|
|
|
INSERT INTO @PartnerFundSourceMapping(DefaultValue,PartnerValue)
|
|
SELECT 'Salary / Wages','CASH' UNION ALL
|
|
SELECT 'Business','BUSINESS' UNION ALL
|
|
SELECT 'Savings','SAVINGS'
|
|
|
|
|
|
INSERT INTO @PartnerIdTypeMapping(DefaultValue,PartnerValue)
|
|
SELECT 'Business Registation','STA' UNION ALL
|
|
SELECT 'Alien Registration Card','ALN' UNION ALL
|
|
SELECT 'National ID','GOV' UNION ALL
|
|
SELECT 'Passport','PAS' UNION ALL
|
|
SELECT 'Driving License','DRV'
|
|
|
|
|
|
INSERT INTO @PartnerRelationShipMapping(DefaultValue,PartnerValue)
|
|
SELECT 'Father','FATHER' UNION ALL
|
|
SELECT 'Mother','MOTHER' UNION ALL
|
|
SELECT 'Wife','WIFE' UNION ALL
|
|
SELECT 'Brother','BROTHER' UNION ALL
|
|
SELECT 'Sister','SISTER' UNION ALL
|
|
SELECT 'Uncle','UNCLE' UNION ALL
|
|
SELECT 'Aunt','AUNT' UNION ALL
|
|
SELECT 'Friend','FRIEND' UNION ALL
|
|
SELECT 'Self','SELF'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO @PartnerOccupationMapping(DefaultValue,PartnerValue,Detail)
|
|
SELECT 'Govt. Employee','ADMIN' AS acvalue,'Administrative (Secretary, Paralegal)' AS aclabel
|
|
UNION ALL
|
|
SELECT '','AGRICULTURE','Agriculture (Farmer, Forestry, Animal Care)'
|
|
UNION ALL
|
|
SELECT 'Businessman','AUTOMOTIVE_SALES','Automotive Sales/Repair'
|
|
UNION ALL
|
|
SELECT 'Salaried','BANKING','Banking / Financial Services'
|
|
UNION ALL
|
|
SELECT 'Salaried','CLEAN_SERVICES','Cleaning Services (Maid, Janitorial)'
|
|
UNION ALL
|
|
SELECT 'Self employed','CLERGY','Clergy / Religious Leader'
|
|
UNION ALL
|
|
SELECT 'Self employed','COMPUTER','Computer / IT Services'
|
|
UNION ALL
|
|
SELECT 'Businessman','CONSTRUCT_WORKER','Construction Worker (Residential/Commercial)'
|
|
UNION ALL
|
|
SELECT 'Salaried','CUSTOMER_CARE','Customer Care / Call Center Support'
|
|
UNION ALL
|
|
SELECT 'Student','EDUCATION','Education (Teacher, Administrator, Writer)'
|
|
UNION ALL
|
|
SELECT 'Salaried','ENGINEER','Engineer'
|
|
UNION ALL
|
|
SELECT 'Salaried','FACTORY','Factory / Manufacturing / Production'
|
|
UNION ALL
|
|
SELECT 'Salaried','GENERAL_LABOR','General Labor (Mason, Construction)'
|
|
UNION ALL
|
|
SELECT 'Govt. Employee','GOV_EMPLOYEE','Government Employee / Military / Police'
|
|
UNION ALL
|
|
SELECT 'Salaried','HEALTH_SERVICES','Healthcare Services (Nurse/Lab/EMT)'
|
|
UNION ALL
|
|
SELECT 'Self employed','HOMEMAKER','Homemaker'
|
|
UNION ALL
|
|
SELECT 'Self employed','PROF_DRIVER','Professional Driver (Taxi/Trucking/Delivery)'
|
|
UNION ALL
|
|
SELECT 'Self employed','PUBLIC_OFFICIAL','Public Official, Legislator, etc.'
|
|
UNION ALL
|
|
SELECT 'Self employed','RESTAURANT','Restaurant / Food Services (Cook, Servers)'
|
|
UNION ALL
|
|
SELECT 'Salaried','RETAIL','Retail (Store Manager, Cashier, Stocker)'
|
|
UNION ALL
|
|
SELECT 'Retiree','RETIRED','Retired'
|
|
UNION ALL
|
|
SELECT 'Student','STUDENT','Student'
|
|
UNION ALL
|
|
SELECT 'Unemployed','UNEMPLOYED','Unemployed / Not in Labor Force'
|
|
|
|
|
|
DECLARE @pBankId VARCHAR(20) = NULL,@pBankBranchId VARCHAR(20)
|
|
|
|
SELECT @tranId=id,@partnerId=pSuperAgent,@pBankId=pBank, @pBankBranchId = pBankBranch FROM dbo.remitTrantemp(NOLOCK) WHERE controlNo=@controlNo
|
|
|
|
IF @tranId IS NULL
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Cannot process the transaction' Msg, @decryptedControlNo Id
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
SELECT '0' ErrorCode, 'Can process the transaction' Msg, @decryptedControlNo Id
|
|
END
|
|
|
|
DECLARE @pBankCode VARCHAR(20) = NULL , @pBankBranchCode VARCHAR(20) = NULL
|
|
|
|
IF EXISTS(SELECT 'A' FROM dbo.remitTrantemp(NOLOCK) WHERE id=@tranId AND purposeOfRemit IS NULL)
|
|
UPDATE dbo.remitTrantemp SET purposeOfRemit = 'Family maintenance' WHERE id=@tranId
|
|
|
|
IF EXISTS(SELECT 'A' FROM dbo.remitTrantemp(NOLOCK) WHERE id=@tranId AND relWithSender IS NULL)
|
|
UPDATE dbo.remitTrantemp SET relWithSender = 'Friend' WHERE id=@tranId
|
|
|
|
IF EXISTS(SELECT 'A' FROM tranSendersTemp(NOLOCK) WHERE tranId = @tranId AND city IS NULL)
|
|
BEGIN
|
|
UPDATE dbo.tranSendersTemp SET city = 'Seoul' WHERE tranId=@tranId
|
|
|
|
UPDATE customerMaster SET city = 'Seoul' WHERE city IS NULL AND approvedDate IS NOT NULL
|
|
END
|
|
|
|
SELECT @pBankCode = agentCode FROM dbo.agentMaster(nolock)
|
|
WHERE agentId = @pBankId AND agentType = '2903' AND isActive = 'Y'
|
|
|
|
SELECT @pBankBranchCode = agentCode FROM dbo.agentMaster(nolock)
|
|
WHERE agentId = @pBankBranchId AND agentType = '2904' AND isActive = 'Y'
|
|
|
|
|
|
SELECT
|
|
rt.id
|
|
,controlNo=dbo.FNADecryptString(rt.controlNo)
|
|
,rt.sCurrCostRate
|
|
,rt.sCurrHoMargin
|
|
,rt.serviceCharge
|
|
,rt.sCountry
|
|
,sCountryIso2=cms.countryCode
|
|
,sCountryIso3=cms.isoAlpha3
|
|
,rt.sSuperAgent
|
|
,rt.sSuperAgentName
|
|
,rt.sAgent
|
|
,rt.sAgentName
|
|
,rt.sBranch
|
|
,rt.sBranchName
|
|
,rt.pCountry
|
|
,pCountryIso2=cmp.countryCode
|
|
,pCountryIso3=cmp.isoAlpha3
|
|
,rt.pSuperAgent
|
|
,rt.pSuperAgentName
|
|
,rt.pAgent
|
|
,rt.pAgentName
|
|
,rt.pBranch
|
|
,rt.pBranchName
|
|
,rt.paymentMethod
|
|
,rcAgent.agentCode AS pBank
|
|
,rt.pBankName
|
|
,rt.pBankBranch
|
|
,rt.pBankBranchName
|
|
,'576667877' AS accountNo
|
|
,rt.externalBankCode
|
|
,rt.collMode
|
|
,rt.collCurr
|
|
,rt.tAmt
|
|
,rt.cAmt
|
|
,rt.pAmt
|
|
,rt.payoutCurr
|
|
,relWithSender=rws.PartnerValue
|
|
,purposeOfRemit=por.PartnerValue
|
|
,sourceOfFund = fs.PartnerValue
|
|
,rt.tranStatus
|
|
,rt.payStatus
|
|
,ContNo=dbo.FNADecryptString(rt.ContNo)
|
|
,controlNo2=dbo.FNADecryptString(rt.controlNo2)
|
|
,rt.senderName
|
|
,rt.receiverName
|
|
,isOnlineTxn= ''
|
|
,rt.sRouteId
|
|
,deliveryMethodId=CASE WHEN rt.paymentMethod ='Cash Payment' THEN '1'
|
|
WHEN rt.paymentMethod = 'Bank Deposit' THEN '2'
|
|
WHEN rt.paymentMethod='Mobile Wallet' THEN '13'
|
|
WHEN rt.paymentMethod = 'Home Delivery' THEN '12'
|
|
ELSE '0' END
|
|
,round(ex.pAmt/rt.pCurrCostRate,2) AS [UsdAmount]
|
|
,ex.FOREX_SESSION_ID AS forexSessionId
|
|
,thirdPartyType = 'NONE'
|
|
,receivingAgentID = rcAgent.routingCode
|
|
FROM dbo.remitTranTemp(NOLOCK) rt
|
|
INNER JOIN dbo.exRateCalcHistory(NOLOCK)ex ON ex.ControlNo = rt.controlNo
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cms ON rt.sCountry=cms.countryName
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cmp ON rt.pCountry=cmp.countryName
|
|
LEFT JOIN @PartnerRemitPurposeMapping por ON por.DefaultValue=rt.purposeOfRemit
|
|
LEFT JOIN @PartnerFundSourceMapping fs ON fs.DefaultValue=rt.sourceOfFund
|
|
LEFT JOIN @PartnerRelationShipMapping rws ON rws.DefaultValue=rt.relWithSender
|
|
LEFT JOIN dbo.agentMaster(NOLOCK) rcAgent ON rcAgent.agentId = rt.pBank
|
|
WHERE rt.id=@tranId
|
|
|
|
SELECT
|
|
ts.tranId
|
|
,ts.customerId
|
|
,ts.membershipId
|
|
,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.country
|
|
,countryIso2=c.countryCode
|
|
,countryIso3=c.isoAlpha3
|
|
,ts.address
|
|
,ts.STATE
|
|
,ts.district
|
|
,'610000' AS zipCode
|
|
,ts.city
|
|
,'' AS email
|
|
,ts.homePhone
|
|
,ts.workPhone
|
|
,mobile=ts.mobile
|
|
,phoneCode='82'
|
|
,ts.nativeCountry
|
|
,nativeCountryIso2=cmn.countryCode
|
|
,nativeCountryIso3=cmn.isoAlpha3
|
|
,dob=FORMAT(ts.dob, 'yyyy-MM-dd', 'en-US' )
|
|
,ts.placeOfIssue
|
|
,ts.customerType
|
|
,occupation = ISNULL(pom.PartnerValue,'STUDENT')
|
|
,idType=itp.PartnerValue
|
|
,ts.idNumber
|
|
,ts.idPlaceOfIssue
|
|
,issuedDate=FORMAT(ts.issuedDate, 'yyyy-MM-dd', 'en-US' )
|
|
,validDate =FORMAT(ts.validDate, 'yyyy-MM-dd', 'en-US' )
|
|
,gender= ''
|
|
,ts.salary
|
|
,ts.companyName
|
|
,ts.address2
|
|
,consumerId = '0'
|
|
FROM dbo.tranSendersTemp (NOLOCK)ts
|
|
INNER JOIN dbo.customerMaster(NOLOCK) cms ON ts.customerId=cms.customerId
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cmn ON ts.nativeCountry=cmn.countryName
|
|
INNER JOIN dbo.countryMaster(NOLOCK) c ON ts.country=c.countryName
|
|
LEFT JOIN @PartnerIdTypeMapping itp ON itp.DefaultValue=ts.idType
|
|
LEFT JOIN @PartnerOccupationMapping pom ON ts.occupation = pom.DefaultValue
|
|
WHERE ts.tranid=@tranId
|
|
|
|
|
|
|
|
SELECT
|
|
tr.tranId
|
|
,tr.customerId
|
|
,tr.membershipId
|
|
,tr.firstName
|
|
,tr.middleName
|
|
,tr.lastName1
|
|
,tr.lastName2
|
|
,tr.fullName
|
|
,tr.country
|
|
,countryIso2=c.countryCode
|
|
,countryIso3=c.isoAlpha3
|
|
,tr.address
|
|
,tr.STATE
|
|
,tr.district
|
|
,tr.zipCode
|
|
,tr.city
|
|
,'' AS email
|
|
,tr.homePhone
|
|
,tr.workPhone
|
|
,tr.mobile
|
|
,tr.nativeCountry
|
|
,nativeCountryIso2=cmn.countryCode
|
|
,nativeCountryIso3=cmn.isoAlpha3
|
|
,dob=FORMAT(tr.dob, 'yyyy-MM-dd', 'en-US' )
|
|
,tr.placeOfIssue
|
|
,tr.customerType
|
|
,tr.occupation
|
|
,idType = itp.PartnerValue
|
|
,idNumber = tr.idNumber
|
|
,tr.idPlaceOfIssue
|
|
,issuedDate=FORMAT(tr.issuedDate, 'yyyy-MM-dd', 'en-US' )
|
|
,validDate=FORMAT(tr.validDate, 'yyyy-MM-dd', 'en-US' )
|
|
,gender=''
|
|
,tr.address2
|
|
,ISNULL(ri.localFirstName,N'')+ISNULL(N' '+ri.localMiddleName,N'')+ISNULL(N' '+ri.localLastName1,N'')+ISNULL(N' '+ri.localLastName2,N'') AS nativename
|
|
FROM dbo.tranReceiversTemp (NOLOCK)tr
|
|
INNER JOIN dbo.countryMaster(NOLOCK) cmn ON tr.nativeCountry=cmn.countryName
|
|
INNER JOIN dbo.countryMaster(NOLOCK) c ON tr.country=c.countryName
|
|
INNER JOIN dbo.receiverInformation(NOLOCK) ri ON ri.receiverId=tr.customerId
|
|
LEFT JOIN @PartnerIdTypeMapping itp ON itp.DefaultValue=tr.idType
|
|
WHERE tr.tranid=@tranId
|
|
END
|
|
|
|
IF @flag='cancelTran'
|
|
BEGIN
|
|
SELECT
|
|
ROUND(pAmt/pCurrCostRate,2) AS sendAmount
|
|
,null AS feeAmount
|
|
,'USD' AS sendCurrency
|
|
,dbo.FNADecryptString(controlNo) AS referenceNumber
|
|
,'gmeltd' AS operatorName
|
|
,'P' AS reversalType /* overridden from application based on same day reversal logic */
|
|
,'WRONG_SERVICE' AS sendReversalReason
|
|
,'' AS feeRefund /* overridden from application based on same day reversal logic*/
|
|
,CAST(0 AS BIT) AS communicationRetryIndicator /*1 true, 0 false */
|
|
,postedDateLocal AS sendDate
|
|
FROM dbo.remitTran(NOLOCK) WHERE id = @tranId AND tranStatus = 'CancelRequest'
|
|
END
|
|
|
|
IF @flag='amendTran'
|
|
BEGIN
|
|
SELECT
|
|
dbo.FNADecryptString(rt.controlNo) AS referenceNumber
|
|
,'gmeltd' AS operatorName
|
|
,tr.firstName AS receiverFirstName
|
|
,tr.middleName AS receiverMiddleName
|
|
,tr.lastName1 AS receiverLastName
|
|
,tr.lastName2 AS receiverLastName2
|
|
FROM dbo.remitTran(NOLOCK) rt INNER JOIN dbo.tranReceivers tr ON rt.id = tr.tranId
|
|
WHERE rt.id = @tranId AND rt.tranStatus = 'ModificationRequest'
|
|
END
|
|
|
|
IF @flag='getBankCode'
|
|
BEGIN
|
|
SELECT agentCode AS BANKCODE FROM dbo.agentMaster(nolock) WHERE agentId = @agentId AND isActive = 'Y'
|
|
END
|
|
END
|
|
|
|
GO
|
|
|