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.
640 lines
51 KiB
640 lines
51 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[proc_GetHoldedTxnForApprovedByAdminCompliance] Script Date: 3/6/2024 9:28:59 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: <Author,Anoj Kattel>
|
|
-- Create date: <Create Date,2019/04/23>
|
|
-- Description: <Description,This sp is used for get transaction going for approv by admin>
|
|
-- Modified on: 2021/06/03 -> Add mapping for Dongav2
|
|
-- -> Add mapping for BOC
|
|
--#5402 fix REMITTENCETYPE for BOC
|
|
--EXEC proc_GetHoldedTxnForApprovedByAdminCompliance @user = 'scheduler', @tranId = '100644593', @callFro = null
|
|
--select * FROM remittran where holdtranid='10385635'
|
|
-- =============================================
|
|
ALTER PROCEDURE [dbo].[proc_GetHoldedTxnForApprovedByAdminCompliance]
|
|
(
|
|
-- Add the parameters for the stored procedure here
|
|
@user VARCHAR(50),
|
|
@tranId VARCHAR(100),
|
|
@callFro VARCHAR(30)
|
|
)
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
-- #9387 - Cebuana
|
|
-- #12184 - Wing Cambodia Integration
|
|
SET NOCOUNT ON;
|
|
DECLARE @PartnerId VARCHAR(100),
|
|
@tranStatus VARCHAR(100)
|
|
|
|
SELECT @PartnerId=pSuperAgent,@tranStatus=tranStatus FROM dbo.remitTran WHERE id=@tranId
|
|
|
|
--IF @tranStatus <> 'Hold'
|
|
--BEGIN
|
|
--IF @callFro!='txnHoldRelease'
|
|
-- BEGIN
|
|
-- SELECT 'NotForTPAPI' ErrorCode, @tranStatus msg,@tranId id
|
|
-- END
|
|
--END
|
|
BEGIN TRY
|
|
|
|
IF @PartnerId='394130' --- for transfast
|
|
BEGIN
|
|
SELECT DISTINCT
|
|
RTT.id tranId
|
|
,'transfast' processId
|
|
,RTT.createdDate txnDate
|
|
,AU.userName userName
|
|
,ISNULL(am.parentId,0) partnerId
|
|
,ISNULL(cm.customerId,0) customerId
|
|
,isFirstTran 'Y'
|
|
,'' sfirstName
|
|
,'' smiddleName
|
|
,'' slastName1
|
|
,'' slastName2
|
|
,CM.fullName sfullName
|
|
,CM.idIssueDate sissuedDate
|
|
,CM.idExpiryDate svalidDate
|
|
,CM.dob sdob
|
|
,TST.email semail
|
|
,CASE WHEN SCC.CITY_CODE = '113179' THEN '113071'
|
|
ELSE SCC.CITY_CODE END scity
|
|
,CASE WHEN SSC.STATE_CODE = 'JP037' THEN 'JP036'
|
|
ELSE SSC.STATE_CODE END sstate
|
|
--,SSC.STATE_CODE sstate
|
|
,CM.zipCode szipCode
|
|
,SNCC.countryCode snativeCountry
|
|
,CASE CM.idType
|
|
WHEN 'Business Registation' THEN 'BZ'
|
|
WHEN 'DRIVERS LICENSE' THEN 'DL'
|
|
WHEN 'National ID' THEN 'G2'
|
|
WHEN 'PASSPORT' THEN 'PA'
|
|
WHEN 'Tohon' THEN 'BZ'
|
|
ELSE 'PA'
|
|
END sidType --- map idtype name and code with transfast sender idtype value
|
|
,CM.idNumber sidNumber
|
|
,REPLACE(CM.mobile,'+','') smobile
|
|
,CASE
|
|
WHEN CM.customerType='4700'
|
|
THEN 1
|
|
ELSE 0 END IsIndividual
|
|
,ISNULL(CM.address,CM.city) saddress
|
|
,ISNULL(cm.occupation,0) occupationId
|
|
,ISNULL(OCU.detailTitle,0) occupationName
|
|
,'JP' sCountry
|
|
--------- receiver details
|
|
,ISNULL(TRT.id,0) receiverId
|
|
,TRT.firstName rfirstName
|
|
,TRT.middleName rmiddleName
|
|
,TRT.lastName1 rlastName1
|
|
,TRT.firstName
|
|
+ISNULL(' ' +TRT.middleName,'')
|
|
+ISNULL(' '+TRT.lastName1,'')
|
|
+ISNULL(' '+TRT.lastName2,'')
|
|
rfullName
|
|
,CASE RCON.countryCode
|
|
WHEN 'LK' THEN --------- for sri lanka
|
|
CASE TRT.idType
|
|
WHEN 'Driver License' THEN '188'
|
|
WHEN 'Business Registation' THEN '189'
|
|
WHEN 'National ID' THEN '195'
|
|
WHEN 'Passport' THEN '197'
|
|
ELSE '195'
|
|
END
|
|
|
|
WHEN 'ID' THEN -- indonesia
|
|
CASE TRT.idType
|
|
WHEN 'Driver License' THEN '286'
|
|
WHEN 'Passport' THEN '287'
|
|
WHEN 'National ID' THEN '288'
|
|
ELSE '287'
|
|
END
|
|
|
|
WHEN 'PH' THEN -- PHILIPPINES
|
|
CASE TRT.idType
|
|
WHEN 'Tohon' THEN '307'
|
|
WHEN 'Driver License' THEN '310'
|
|
WHEN 'Passport' THEN '316'
|
|
WHEN 'Alien Registration Card' THEN '326'
|
|
ELSE '316'
|
|
END
|
|
|
|
WHEN 'PK' THEN --- pakistan
|
|
CASE TRT.idType
|
|
WHEN 'Passport' THEN '270'
|
|
WHEN 'Business Registation' THEN '272'
|
|
WHEN 'Driver License' THEN '269'
|
|
WHEN 'National ID' THEN '271'
|
|
ELSE '270'
|
|
END
|
|
|
|
WHEN 'BD' THEN --- bangladesh
|
|
CASE TRT.idType
|
|
WHEN 'Passport' THEN '12'
|
|
WHEN 'National ID' THEN '13'
|
|
WHEN 'Driver License' THEN '14'
|
|
WHEN 'Business Registation' THEN '16'
|
|
ELSE '430'
|
|
END
|
|
|
|
WHEN 'IN' THEN --- india
|
|
CASE TRT.idType
|
|
WHEN 'Passport' THEN '136'
|
|
WHEN 'Business Registation' THEN '137'
|
|
WHEN 'Alien Registration Card' THEN '140'
|
|
WHEN 'Driver License' THEN '142'
|
|
WHEN 'National ID' THEN '329'
|
|
ELSE '136'
|
|
END
|
|
END
|
|
ridType --- map with transfast receiver idtype id code
|
|
,TRT.idNumber ridNumber
|
|
,TRT.validDate rvalidDate
|
|
,TRT.dob rdob
|
|
,TRT.homePhone rhomePhone
|
|
,TRT.mobile rmobile
|
|
,RNC.countryCode rnativeCountry
|
|
,ISNULL(TRT.city,TRT.address) rcity
|
|
,TRT.address raddress
|
|
,TRT.email remail
|
|
,raccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
,RCON.countryCode rcountry
|
|
,'' branchId
|
|
--,CASE RTT.paymentMethod
|
|
-- WHEN 'Bank Deposit' THEN APBL.BRANCH_CODE
|
|
-- WHEN 'Cash Payment' THEN ABBL.BRANCH_CODE1 END branchId -------- Need To Map With Transfast PayingBranchId
|
|
,CASE RTT.paymentMethod
|
|
WHEN 'Bank Deposit' THEN PBD.PAYER_CODE
|
|
WHEN 'Cash Payment' THEN ABL.BANK_CODE1 END payerId -------- Need To Map With Transfast PayerId
|
|
,TRT.branchName branchName
|
|
,AU.city city
|
|
,ISNULL(RTT.pAgent,0) pAgent
|
|
,RTT.pAgentName pAgentName
|
|
,RTT.pBankType pBankType
|
|
,CASE RTT.paymentMethod
|
|
WHEN 'Bank Deposit' THEN ISNULL(ABL.BANK_CODE1,'')
|
|
ELSE '' END pBank
|
|
,ISNULL(abl.BANK_CODE2,0) pBankLocation
|
|
,CASE RTT.paymentMethod
|
|
WHEN 'Bank Deposit' THEN ISNULL(ABBL.BRANCH_CODE1,'')
|
|
ELSE '' END pBankBranchId
|
|
,RTT.pBankName pBankName
|
|
,RTT.payoutCurr payoutCurr
|
|
,RTT.collCurr collCurr
|
|
,ISNULL(RTT.cAmt,0) cAmt
|
|
,ISNULL(RTT.pAmt,0) pAmt
|
|
,ISNULL(RTT.tAmt,0) tAmt
|
|
,serviceCharge serviceCharge
|
|
,ISNULL(RTT.pAgentComm,0) pAgentComm
|
|
,CASE RCON.countryCode
|
|
WHEN 'LK' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Educational expenses' THEN '2'
|
|
WHEN 'Medical Expenses' THEN '3'
|
|
WHEN 'Business travel' THEN '7'
|
|
WHEN 'Trading' THEN '14'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Purchase of land / property' THEN '23'
|
|
WHEN 'Utility payment' THEN '24'
|
|
WHEN 'Rent' THEN '25'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Trading' THEN '27'
|
|
WHEN 'Salary / Commission' THEN '29'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
ELSE '1'
|
|
END
|
|
|
|
WHEN 'ID' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Educational expenses' THEN '2'
|
|
WHEN 'Medical Expenses' THEN '3'
|
|
WHEN 'Purchase of land / property' THEN '9'
|
|
WHEN 'Trading' THEN '14'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Utility payment' THEN '24'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
ELSE '1'
|
|
END
|
|
|
|
WHEN 'PH' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Educational expenses' THEN '2'
|
|
WHEN 'Medical Expenses' THEN '3'
|
|
WHEN 'Business travel' THEN '7'
|
|
WHEN 'Trading' THEN '14'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Purchase of land / property' THEN '23'
|
|
WHEN 'Utility payment' THEN '24'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Trading' THEN '27'
|
|
WHEN 'Salary / Commission' THEN '29'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
ELSE '1'
|
|
END
|
|
|
|
WHEN 'PK' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Educational expenses' THEN '2'
|
|
WHEN 'Medical Expenses' THEN '3'
|
|
WHEN 'Trading' THEN '14'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Purchase of land / property' THEN '23'
|
|
WHEN 'Utility payment' THEN '24'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
ELSE '1'
|
|
END
|
|
WHEN 'BD' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Educational expenses' THEN '2'
|
|
WHEN 'Medical Expenses' THEN '3'
|
|
WHEN 'Purchase of land / property' THEN '9'
|
|
WHEN 'Trading' THEN '14'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Utility payment' THEN '24'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
ELSE '1'
|
|
END
|
|
|
|
WHEN 'IN' THEN
|
|
CASE RTT.purposeOfRemit
|
|
WHEN 'Family maintenance' THEN '1'
|
|
WHEN 'Savings' THEN '18'
|
|
WHEN 'Purchase of land / property' THEN '23'
|
|
WHEN 'Educational expenses' THEN '24'
|
|
WHEN 'Rent' THEN '25'
|
|
WHEN 'Personal travels and tours' THEN '26'
|
|
WHEN 'Trading' THEN '27'
|
|
WHEN 'Utility payment' THEN '28'
|
|
WHEN 'Salary / Commission' THEN '29'
|
|
WHEN 'Loan payment / Interest' THEN '30'
|
|
WHEN 'Medical Expenses' THEN '32'
|
|
ELSE '1'
|
|
END
|
|
END purposeOfRemit ----------- Map With Transfast code
|
|
,CASE RTT.sourceOfFund
|
|
WHEN 'Own business' THEN '1'
|
|
WHEN 'Business' THEN '2'
|
|
WHEN 'Salary / Wages' THEN '3'
|
|
WHEN 'Return from Investment' THEN '4'
|
|
WHEN 'Loan from bank' THEN '5'
|
|
WHEN 'Lottery' THEN '6'
|
|
WHEN 'Part time job' THEN '7'
|
|
WHEN 'Pension' THEN '8'
|
|
WHEN 'Savings or accumulated' THEN '10'
|
|
ELSE '9'
|
|
END sourceOfFund
|
|
,rel.detailTitle relationName
|
|
,cm.remarks remarks
|
|
,ISNULL(RTT.sAgent,0) sAgent
|
|
,RTT.sAgentName sAgentName
|
|
,ISNULL(RTT.sSuperAgent,0) sSuperAgent
|
|
,cm.ipAddress ipAddress
|
|
,AU.countryId countryId
|
|
,RSC.STATE_CODE rstate ----- Receiver State Code
|
|
,ISNULL(RTT.sBranch,0) sBranch
|
|
,RTT.pLocation pLocation
|
|
,CASE RTT.paymentMethod
|
|
WHEN 'Bank Deposit' THEN 'C'
|
|
WHEN 'Cash Payment' THEN '2'
|
|
END paymentMethod -------- map with transfast payoutmethod id
|
|
|
|
,dbo.decryptDb(RTT.controlNo) controlNo
|
|
,RCC.CITY_CODE rcityCode ----- Receiver City Code
|
|
,RTC.TOWN_CODE rTownCode ----- Receiver Town Code
|
|
,cm.sessionId sessionId
|
|
,'true' IsRealtime
|
|
,bankAccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
,'CA' formOfPaymentId
|
|
,CM.SSNNO ssnno
|
|
,RTT.customerrate Rate
|
|
,'N' IsRealtime
|
|
FROM remitTran RTT WITH(NOLOCK)
|
|
INNER JOIN dbo.tranSenders TST(NOLOCK) ON TST.TRANID = RTT.ID
|
|
INNER JOIN customerMaster CM(NOLOCK) ON CM.customerId = TST.customerId
|
|
INNER JOIN dbo.tranReceivers TRT(NOLOCK) ON TRT.TRANID = RTT.id
|
|
LEFT JOIN applicationUsers AU(NOLOCK) ON AU.USERNAME = RTT.CREATEDBY
|
|
LEFT JOIN dbo.agentMaster AM(NOLOCK) ON AM.agentId=RTT.pAgent
|
|
LEFT JOIN API_BANK_LIST ABL (NOLOCK) ON ABL.BANK_ID=RTT.pBank
|
|
LEFT JOIN dbo.API_BANK_BRANCH_LIST ABBL (NOLOCK) ON ABBL.BRANCH_ID=RTT.pBankBranch -- OR ABBL.BRANCH_NAME=RTT.pBankBranchName
|
|
LEFT JOIN dbo.staticDataValue OCU (NOLOCK) ON OCU.valueId=cm.occupation
|
|
LEFT JOIN dbo.staticDataValue REL (NOLOCK) ON REL.detailTitle=RTT.relWithSender
|
|
--LEFT JOIN dbo.staticDataValue IDT (NOLOCK) ON IDT.valueId=TRT.idType
|
|
LEFT JOIN dbo.countryStateMaster CSM(NOLOCK) ON CSM.stateId=cm.state
|
|
LEFT JOIN dbo.API_CITY_LIST SCC (NOLOCK) ON SCC.CITY_NAME=CM.city
|
|
LEFT JOIN dbo.API_STATE_LIST SSC (NOLOCK) ON SSC.STATE_ID=SCC.STATE_ID
|
|
LEFT JOIN dbo.API_STATE_LIST RSC (NOLOCK) ON RSC.STATE_NAME=TRT.STATE
|
|
LEFT JOIN dbo.API_CITY_LIST RCC (NOLOCK) ON SCC.CITY_NAME=TRT.city
|
|
LEFT JOIN dbo.API_TOWN_LIST RTC (NOLOCK) ON RTC.TOWN_ID=RTT.pLocation
|
|
LEFT JOIN dbo.countryMaster SNCC (NOLOCK) ON SNCC.countryId=CM.country
|
|
LEFT JOIN dbo.countryMaster RCON (NOLOCK) ON RCON.countryName=TRT.country
|
|
LEFT JOIN dbo.countryMaster RNC (NOLOCK) ON RNC.countryName=TRT.NativeCountry OR RNC.countryId = TRT.NativeCountry
|
|
LEFT JOIN dbo.PAYER_BANK_DETAILS PBD(NOLOCK) ON PBD.PAYER_ID = RTT.PayerId
|
|
--LEFT JOIN dbo.API_PAYOUT_LOACTION APL(NOLOCK) ON APL.Id=RTT.PayerId
|
|
--LEFT JOIN dbo.API_PAYOUT_BRANCH_LOACTION APBL (NOLOCK) ON APBL.Id=RTT.PayerBranchId
|
|
WHERE RTT.id =@tranId
|
|
END
|
|
|
|
IF @PartnerId= '394397' ----- For GME NEPAL
|
|
BEGIN
|
|
SELECT
|
|
RTT.id tranId ----
|
|
,'gmenepal' processId
|
|
,CONVERT(VARCHAR, RTT.createdDate, 110) txnDate
|
|
,AU.userName userName -- notes -- Filds are use for jme nepal send model
|
|
,ISNULL(AM.parentId,0) partnerId ---- use for to get thirdparty api partner services
|
|
,ISNULL(CM.customerId,0) customerId
|
|
,isFirstTran 'Y' ----
|
|
,'' sfirstName
|
|
,'' smiddleName
|
|
,'' slastName1
|
|
,'' slastName2
|
|
,CM.fullName sfullName --
|
|
,TST.address saddress --
|
|
,CM.mobile smobile --
|
|
,tst.city scity --
|
|
,RTT.sCountry sCountry --
|
|
,TST.idType sidType --
|
|
,TST.idNumber sidNumber --
|
|
,TRT.fullName rfullName --
|
|
,TRT.address raddress --
|
|
,TRT.mobile rmobile --
|
|
,ISNULL(TRT.city,TRT.address) rcity --
|
|
,TRT.country rcountry --
|
|
,RTT.sourceOfFund sourceOfFund --
|
|
,RTT.relwithsender relationName --
|
|
,RTT.purposeOfRemit purposeOfRemit --
|
|
,ISNULL(RTT.cAmt,0) cAmt --
|
|
,ISNULL(RTT.pAmt,0) pAmt --
|
|
,ISNULL(RTT.tAmt,0) tAmt --
|
|
,RTT.paymentMethod paymentMethod --
|
|
,RTT.pBankName pBankName --
|
|
,ISNULL(PBID.BANK_CODE1,0) pBank --
|
|
,pBankLocation = CASE WHEN PBID.BANK_CODE1='11006083' THEN PBID.BANK_CODE2 ELSE ISNULL(PBBID.BRANCH_CODE1,'0') END
|
|
,ISNULL(PBBID.BRANCH_NAME,'') pBankBranchName --
|
|
,raccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
|
|
,CM.idIssueDate sissuedDate --
|
|
,CM.idExpiryDate svalidDate --
|
|
,CM.dob sdob --
|
|
,'' semail
|
|
,'' szipCode
|
|
,CM.nativeCountry snativeCountry --
|
|
,0 occupationId
|
|
,CASE WHEN CM.occupation = '11383' THEN CM.occupationother ELSE ISNULL(OCU.detailTitle,0) END occupationName --
|
|
--------- receiver details
|
|
,'' receiverId
|
|
,'' rfirstName
|
|
,'' rmiddleName
|
|
,'' rlastName1
|
|
,'' ridType
|
|
,'' ridNumber
|
|
,'' rvalidDate
|
|
,'' rdob
|
|
,'' rhomePhone
|
|
--,RE.countryCode
|
|
,'' rnativeCountry
|
|
,'' remail
|
|
,'' branchId
|
|
,'' branchName
|
|
,'' city
|
|
,0 pAgent
|
|
,'' pAgentName
|
|
,'' pBankType
|
|
,RTT.payoutCurr
|
|
,RTT.collCurr
|
|
,RTT.SERVICECHARGE serviceCharge --
|
|
,'' pAgentComm
|
|
,'' relationId
|
|
,'' remarks
|
|
,0 sAgent
|
|
,'' sAgentName
|
|
,0 sSuperAgent
|
|
,'' ipAddress
|
|
,0 countryId
|
|
,'' rstate
|
|
,0 sBranch
|
|
,'' pLocation
|
|
,dbo.decryptDb(RTT.controlNo) controlNo --
|
|
,0 exRate
|
|
,'' rcityCode
|
|
,CM.sessionId sessionId ----
|
|
,'true' IsRealtime ----
|
|
,bankAccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
,'Y' IsRealtime
|
|
,RTT.company exRateConfirmId
|
|
,RTT.pCurrCostRate SettlementDollarRate
|
|
FROM remitTran RTT WITH(NOLOCK)
|
|
INNER JOIN dbo.tranSenders TST(NOLOCK) ON TST.TRANID = RTT.ID
|
|
INNER JOIN customerMaster CM(NOLOCK) ON CM.customerId = TST.customerId
|
|
INNER JOIN dbo.tranReceivers TRT(NOLOCK) ON TRT.TRANID = RTT.ID
|
|
LEFT JOIN applicationUsers AU(NOLOCK) ON AU.USERNAME = RTT.CREATEDBY
|
|
INNER JOIN dbo.agentMaster AM(NOLOCK) ON AM.agentId=RTT.pAgent
|
|
LEFT JOIN API_BANK_LIST PBID (NOLOCK) ON PBID.BANK_ID=RTT.pBank
|
|
LEFT JOIN dbo.API_BANK_BRANCH_LIST PBBID (NOLOCK) ON PBBID.BRANCH_ID=RTT.pBankBranch
|
|
LEFT JOIN dbo.staticDataValue OCU (NOLOCK) ON OCU.valueId=TST.occupation
|
|
LEFT JOIN dbo.staticDataValue REL (NOLOCK) ON REL.valueId=CM.relationId
|
|
WHERE RTT.id = @tranId
|
|
END
|
|
|
|
IF @PartnerId= '394449' ----- For GCC REMIT
|
|
BEGIN
|
|
SELECT
|
|
RTT.id tranId ----
|
|
,'gccremit' processId
|
|
,CONVERT(VARCHAR, RTT.createdDate, 110) txnDate
|
|
,AU.userName userName -- notes -- Filds are use for jme nepal send model
|
|
,ISNULL(AM.parentId,0) partnerId ---- use for to get thirdparty api partner services
|
|
,ISNULL(CM.customerId,0) customerId
|
|
,isFirstTran 'Y' ----
|
|
,CM.firstName sfirstName
|
|
,CM.middleName smiddleName
|
|
,CM.lastName1 slastName1
|
|
,CM.lastName2 slastName2
|
|
,CM.fullName sfullName --
|
|
,ISNULL(TST.address, CM.address) saddress --
|
|
,CM.mobile smobile --
|
|
,CM.email semail
|
|
,tst.city scity --
|
|
,sCON.countryCode sCountry --
|
|
,CASE TST.idType
|
|
WHEN 'PASSPORT' THEN '2'
|
|
WHEN 'DRIVER LICENSE' THEN '4'
|
|
WHEN 'Biometric Residence Permit' THEN '5'
|
|
|
|
|
|
|
|
ELSE '2'
|
|
END As sidType --
|
|
,TST.idNumber sidNumber --
|
|
,TRT.fullName rfullName --
|
|
,TRT.address raddress --
|
|
,TRT.mobile rmobile --
|
|
,ISNULL(TRT.city,TRT.address) rcity --
|
|
,TRT.country rcountry --
|
|
,CASE RTT.sourceOfFund
|
|
WHEN 'Business Income' THEN '5'
|
|
|
|
WHEN 'Salary' THEN '2'
|
|
|
|
ELSE '5'
|
|
END sourceOfFund
|
|
--,RTT.relwithsender relationName --
|
|
, CASE RTT.relwithsender
|
|
WHEN 'Business Partner' THEN '7' -- RTR001 FAMILY
|
|
WHEN 'Friend' THEN '3'
|
|
WHEN 'Parents' THEN '1'
|
|
WHEN 'Self' THEN '5'
|
|
WHEN 'Husband/Wife' THEN '12'
|
|
ELSE '1'
|
|
END relationName --
|
|
,CASE RTT.purposeOfRemit
|
|
WHEN 'Family Support' THEN '1'
|
|
WHEN 'Education Support' THEN '4'
|
|
WHEN 'Investment' THEN '8'
|
|
WHEN 'Loan Payment' THEN '9'
|
|
WHEN 'Medical Support' THEN '2'
|
|
WHEN 'Salary Payments' THEN '10'
|
|
WHEN 'Personal Savings' THEN '6'
|
|
|
|
ELSE '1'
|
|
END AS purposeOfRemit
|
|
,ISNULL(RTT.cAmt,0) cAmt --
|
|
,ISNULL(RTT.pAmt,0) pAmt --
|
|
,ISNULL(RTT.tAmt,0) tAmt --
|
|
,CASE
|
|
WHEN RTT.pCountry = 'INDIA' and RTT.paymentMethod = 'Bank Deposit' THEN '9'
|
|
WHEN RTT.pCountry = 'BANGLADESH' and RTT.paymentMethod = 'Bank Deposit' THEN '3'
|
|
WHEN RTT.pCountry = 'HONG KONG' and RTT.paymentMethod = 'Bank Deposit' THEN '2'
|
|
WHEN RTT.pCountry = 'PAKISTAN' and RTT.paymentMethod = 'Bank Deposit' THEN '2'
|
|
WHEN RTT.pCountry = 'SINGAPORE' and RTT.paymentMethod = 'Bank Deposit' THEN '2'
|
|
WHEN RTT.pCountry = 'SRI LANKA' and RTT.paymentMethod = 'Bank Deposit' THEN '2'
|
|
WHEN RTT.pCountry = 'UNITED ARAB EMIRATES' and RTT.paymentMethod = 'Bank Deposit' THEN '2'
|
|
--WHEN RTT.pCountry = 'UNITED STATES OF AMERICA' and RTT.paymentMethod = 'Bank Deposit' THEN '3'
|
|
WHEN RTT.pCountry = 'AUSTRIA' and RTT.paymentMethod = 'Bank Deposit' THEN '3'
|
|
--WHEN 'Cash Payment' THEN '1'
|
|
--WHEN 'Mobile Wallet' THEN '!4'
|
|
END paymentMethod
|
|
,RTT.pBankName pBankName --
|
|
,ISNULL(PBID.BANK_CODE1,0) pBank --
|
|
,pBankLocation = CASE WHEN PBID.BANK_CODE1='11006083' THEN PBID.BANK_CODE2 ELSE ISNULL(PBBID.BRANCH_CODE1,'0') END
|
|
,ISNULL(PBBID.BRANCH_NAME,'') pBankBranchName --
|
|
,raccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
|
|
,CM.idIssueDate sissuedDate --
|
|
,CM.idExpiryDate svalidDate --
|
|
,CM.dob sdob --
|
|
,'' semail
|
|
,sCON.countryCode szipCode
|
|
,CM.nativeCountry snativeCountry --
|
|
,0 occupationId
|
|
,CASE WHEN CM.occupation = '11383' THEN CM.occupationother ELSE ISNULL(OCU.detailTitle,0) END occupationName --
|
|
--------- receiver details
|
|
--,'' receiverId
|
|
--,'' rfirstName
|
|
--,'' rmiddleName
|
|
--,'' rlastName1
|
|
|
|
,ISNULL(TRT.id,0) receiverId
|
|
,TRT.firstName rfirstName
|
|
,TRT.middleName rmiddleName
|
|
,TRT.lastName1 rlastName1
|
|
,TRT.idType ridType
|
|
,'' ridNumber
|
|
,'' rvalidDate
|
|
,'' rdob
|
|
,'' rhomePhone
|
|
--,RE.countryCode
|
|
,RCON.countryCode rnativeCountry
|
|
,TRT.email remail
|
|
--,CASE when rtt.sCountry = 'INDIA' and branchId = 'INSII00002-9' then ''
|
|
, CASE
|
|
WHEN TRT.country = 'INDIA' THEN PBID.BANK_CODE1 -- 'INSIB00001-9'
|
|
WHEN TRT.country = 'PAKISTAN' THEN PBID.BANK_CODE1
|
|
WHEN TRT.country = 'SINGAPORE' THEN PBID.BANK_CODE1
|
|
WHEN TRT.country = 'BANGLADESH' THEN PBID.BANK_CODE1
|
|
WHEN TRT.country = 'SRI LANKA' THEN PBBID.BRANCH_CODE1
|
|
WHEN TRT.country = 'United Arab Emirates' THEN PBID.BANK_CODE1
|
|
WHEN TRT.country = 'AUSTRIA' THEN PBID.BANK_CODE1
|
|
WHEN TRT.country = 'HONG KONG' THEN PBBID.BRANCH_CODE1
|
|
|
|
END AS branchId
|
|
--End branchid
|
|
--,'INSII00002-9' branchId
|
|
|
|
-- ,Branchid = CASE
|
|
-- WHEN TRT.country = 'INDIA' then branchId = 'INSII00002-9'
|
|
--WHEN TRT.country = 'Pakistan' then branchId = 'PKBOP00001-6'
|
|
--END
|
|
,'' branchName
|
|
,'' city
|
|
,0 pAgent
|
|
,'' pAgentId
|
|
,PBBID.BRANCH_CODE1 pAgentName
|
|
,'' pBankType
|
|
,RTT.payoutCurr
|
|
,RTT.collCurr
|
|
,RTT.SERVICECHARGE serviceCharge --
|
|
,'' pAgentComm
|
|
,TRT.relationType relationId
|
|
,'' remarks
|
|
,0 sAgent
|
|
,'' sAgentName
|
|
,0 sSuperAgent
|
|
,'' ipAddress
|
|
,0 countryId
|
|
,'' rstate
|
|
,0 sBranch
|
|
,'' pLocation
|
|
,dbo.decryptDb(RTT.controlNo) controlNo --
|
|
,0 exRate
|
|
,'' rcityCode
|
|
,CM.sessionId sessionId ----
|
|
,'true' IsRealtime ----
|
|
,bankAccountNo = CASE WHEN RTT.paymentMethod = 'Bank Deposit' THEN RTT.accountNo ELSE '' END
|
|
,'Y' IsRealtime
|
|
,RTT.company exRateConfirmId
|
|
,RTT.pCurrCostRate SettlementDollarRate
|
|
FROM remitTran RTT WITH(NOLOCK)
|
|
INNER JOIN dbo.tranSenders TST(NOLOCK) ON TST.TRANID = RTT.ID
|
|
INNER JOIN customerMaster CM(NOLOCK) ON CM.customerId = TST.customerId
|
|
INNER JOIN dbo.tranReceivers TRT(NOLOCK) ON TRT.TRANID = RTT.ID
|
|
LEFT JOIN applicationUsers AU(NOLOCK) ON AU.USERNAME = RTT.CREATEDBY
|
|
INNER JOIN dbo.agentMaster AM(NOLOCK) ON AM.agentId=RTT.pAgent
|
|
LEFT JOIN API_BANK_LIST PBID (NOLOCK) ON PBID.BANK_ID=RTT.pBank
|
|
LEFT JOIN dbo.API_BANK_BRANCH_LIST PBBID (NOLOCK) ON PBBID.BRANCH_ID=RTT.pBankBranch
|
|
LEFT JOIN dbo.staticDataValue OCU (NOLOCK) ON OCU.valueId=TST.occupation
|
|
LEFT JOIN dbo.staticDataValue REL (NOLOCK) ON REL.valueId=CM.relationId
|
|
LEFT JOIN dbo.countryMaster RCON (NOLOCK) ON RCON.countryName=TRT.Country
|
|
|
|
LEFT JOIN dbo.countryMaster sCON (NOLOCK) ON sCON.countryName=TST.nativeCountry
|
|
--LEFT JOIN countryMaster RCC (NOLOCK) ON RCC.countryId= RTT.ID
|
|
WHERE RTT.id = @tranId
|
|
END
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
IF @@TRANCOUNT > 0
|
|
ROLLBACK TRANSACTION
|
|
SELECT 1 error_code, ERROR_MESSAGE() mes, NULL id
|
|
END CATCH
|
|
END
|
|
|
|
|
|
|
|
|
|
|