DB_Script/proc_GetHoldedTxnForApprovedByAdminCompliance.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[proc_GetHoldedTxnForApprovedByAdminCompliance]    Script Date: 2/6/2024 10:04:21 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   --  
   ,TST.address   saddress   --  
   ,CM.mobile       smobile    --  
   ,CM.email		semail
   ,tst.city       scity    --  
   ,'GB'     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 '6'  
		WHEN RTT.pCountry = 'SINGAPORE' and RTT.paymentMethod = 'Bank Deposit' THEN '6'  
		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 '' 
     --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_CODE2
        WHEN TRT.country = 'PAKISTAN' THEN PBID.BANK_CODE1
		WHEN TRT.country = 'BANGLADESH' THEN PBID.BANK_CODE2
		WHEN TRT.country = 'SRI LANKA' THEN PBID.BANK_CODE2

    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