DB_Script/Back/PROC_API_BANK_BRANCH_SETUP.sql

��USE [FastMoneyPro_Remit]
GO
/****** Object:  StoredProcedure [dbo].[PROC_API_BANK_BRANCH_SETUP]    Script Date: 4/8/2024 6:22:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[PROC_API_BANK_BRANCH_SETUP] (
	@FLAG VARCHAR(40)
	--grid parameters
	,@user VARCHAR(80) = NULL
	,@pageSize VARCHAR(50) = NULL
	,@pageNumber VARCHAR(50) = NULL
	,@sortBy VARCHAR(50) = NULL
	,@sortOrder VARCHAR(50) = NULL
	,@API_PARTNER INT = NULL
	,@PAYMENT_TYPE INT = NULL
	,@bankId VARCHAR(30) = NULL
	,@countryId INT = NULL
	,@paymentMode INT = NULL
	,@BRANCH_NAME VARCHAR(30) = NULL
	,@isActive VARCHAR(5) = NULL
	,@rowId VARCHAR(10) = NULL
	,@XML NVARCHAR(MAX) = NULL
	,@BANK_COUNTRY VARCHAR(50) = NULL
	,@Branch_Country VARCHAR(50) = NULL
	,@BANK_CURRENCY VARCHAR(5) = NULL
	,@API_PARTNER_ID INT = NULL
	,@CityId BIGINT = NULL
	,@PaymentMethod INT = NULL
	)
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;
----------------------------------------
--JME-544 -> added @FLAG = 'getBranch-new'
--        -> Change logic to use API_BANK_LIST_MASTER
-- #1574 -> Brac Bank Branch Sync
-- #11358 -> show routing number for branch 
-----------------------------------------
BEGIN
	DECLARE @table VARCHAR(MAX)
		,@select_field_list VARCHAR(MAX)
		,@extra_field_list VARCHAR(MAX)
		,@sql_filter VARCHAR(MAX)
		,@NEW_RECORD INT = 0
		,@NEW_BRANCH_RECORD INT = 0
		,@MSG VARCHAR(200)

	IF @FLAG = 'S'
	BEGIN
		SET @sortBy = 'BANK_ID'
		SET @sortOrder = 'desc'
		SET @table = '( SELECT BANK_ID,
								API_PARTNER = AM.AGENTNAME,
								API_PARTNER_ID,
								BANK = CM.CountryId,
								BANK_NAME,
								BANK_CODE1,
								BANK_CODE2,
								BANK_COUNTRY,
								IS_ACTIVE = CASE WHEN IS_ACTIVE = 1 THEN ''YES'' ELSE ''NO'' END,
								PAYMENT_TYPE = typeTitle,
								PAYMENT_TYPE_ID
						FROM API_BANK_LIST AB(NOLOCK)
						INNER JOIN AGENTMASTER AM(NOLOCK) ON AM.AGENTID = AB.API_PARTNER_ID AND AM.PARENTID = 0
						INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.CountryName = AB.BANK_COUNTRY
						INNER JOIN SERVICETYPEMASTER SM(NOLOCK) ON SM.serviceTypeId = AB.PAYMENT_TYPE_ID '
		SET @sql_filter = ''
		SET @table = @table + ')x'

		IF @API_PARTNER <> 0
			SET @sql_filter = @sql_filter + ' AND API_PARTNER_ID =  ''' + CAST(@API_PARTNER AS VARCHAR) + ''''
		IF @BANK_COUNTRY <> 0
			SET @sql_filter = @sql_filter + ' AND BANK =  ''' + CAST(@BANK_COUNTRY AS VARCHAR) + ''''
		IF @PAYMENT_TYPE <> 0
			SET @sql_filter = @sql_filter + ' AND PAYMENT_TYPE_ID =  ''' + CAST(@PAYMENT_TYPE AS VARCHAR) + ''''
		SET @select_field_list = '
				 BANK_ID,API_PARTNER_ID,API_PARTNER,BANK_NAME,BANK_CODE1,BANK_CODE2,BANK_COUNTRY,IS_ACTIVE,PAYMENT_TYPE'

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber
	END


	IF @FLAG = 'SListNewBank'
	BEGIN
		SET @sortBy = 'MASTER_BANK_ID'
		SET @sortOrder = 'desc'
		SET @table = '( SELECT MASTER_BANK_ID,
								BANK_COUNTRY = AM.CountryName,
								BANK = AM.CountryId,
								BANK_NAME,
								SUPPORT_CURRENCY,
								IS_ACTIVE = CASE WHEN IS_ACTIVE = 1 THEN ''YES'' ELSE ''NO'' END,
								PAYMENT_TYPE = typeTitle,
								PAYMENT_TYPE_ID
						FROM API_BANK_LIST_MASTER AB(NOLOCK)
						INNER JOIN COUNTRYMASTER AM(NOLOCK) ON AM.CountryName = AB.BANK_COUNTRY
						INNER JOIN SERVICETYPEMASTER SM(NOLOCK) ON SM.serviceTypeId = AB.PAYMENT_TYPE_ID '
		SET @sql_filter = ''
		SET @table = @table + ')x'

		IF @BANK_COUNTRY <> 0
			SET @sql_filter = @sql_filter + ' AND BANK =  ''' + CAST(@BANK_COUNTRY AS VARCHAR) + ''''

		IF @PAYMENT_TYPE <> 0
			SET @sql_filter = @sql_filter + ' AND PAYMENT_TYPE_ID =  ''' + CAST(@PAYMENT_TYPE AS VARCHAR) + ''''
		SET @select_field_list = '
				 MASTER_BANK_ID,BANK,BANK_COUNTRY,BANK_NAME,SUPPORT_CURRENCY,IS_ACTIVE,PAYMENT_TYPE'

		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber
	END


	IF @FLAG = 'SBranchList'
	BEGIN
		SET @sortBy = 'BRANCH_ID'
		SET @sortOrder = 'desc'
		SET @table = '( SELECT BRANCH_ID,
								BRANCH_COUNTRY = CM.CountryName,
								BranchC = CM.CountryId,
								BANK_ID = AM.Bank_Name,
								bankName  = AM.BANK_ID,
								BRANCH_NAME,
								IS_ACTIVE = CASE WHEN AB.IS_ACTIVE = 1 THEN ''YES'' ELSE ''NO'' END,
								BRANCH_CODE1,
								BRANCH_CODE2

						FROM API_BANK_BRANCH_LIST AB(NOLOCK)
						INNER JOIN API_BANK_LIST AM(NOLOCK) ON AM.BANK_ID = AB.BANK_ID 
						INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.CountryName = AB.BRANCH_COUNTRY'
		SET @sql_filter = ''
		SET @table = @table + ')x'

		--IF @API_PARTNER <> 0
		--	SET @sql_filter = @sql_filter + ' AND API_PARTNER_ID =  ''' + CAST(@API_PARTNER AS VARCHAR) + ''''
		IF @Branch_Country <> 0
			SET @sql_filter = @sql_filter + ' AND BranchC =  ''' + CAST(@Branch_Country AS VARCHAR) + ''''
		IF @bankId <> 0
			SET @sql_filter = @sql_filter + ' AND bankName =  ''' + CAST(@bankId as VARCHAR) + ''''
		SET @select_field_list = '
				 BRANCH_ID,BRANCH_COUNTRY,BranchC,BANK_ID,BRANCH_NAME,BRANCH_CODE1,BRANCH_CODE2, IS_ACTIVE'
		print @table
		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber
	END

	IF @FLAG = 'API-PARTNER'
	BEGIN
		SELECT *
		FROM (
			SELECT '' [value]
				,'SELECT' [text]
			
			UNION ALL
			
			SELECT AGENTID [value]
				,AGENTNAME [text]
			FROM agentMaster(NOLOCK)
			WHERE PARENTID = 0
				AND AGENTID NOT IN (
					1001
					,393877
					)
			) X
		ORDER BY X.[value] ASC
	END

	IF @FLAG = 'Bank-Country'
	BEGIN
		SELECT *
		FROM (
			SELECT '' [value]
				,'SELECT' [text]
			
			UNION ALL
			
			SELECT countryId [value]
				,CountryName [text]
			FROM CountryMaster(NOLOCK)
			WHERE ISNULL(isOperativeCountry, 'Y') = 'Y'
			) X
		ORDER BY X.[value] ASC
	END

		IF @FLAG = 'Bank'
	BEGIN
		SELECT *
		FROM (
			SELECT '' [value]
				,'SELECT' [text]
			
			UNION ALL
			
			SELECT BANK_ID [value]
				,BANK_NAME [text]
			FROM API_BANK_LIST(NOLOCK)
			WHERE ISNULL(IS_ACTIVE, '1') =  '1'
			) X
		ORDER BY X.[value] ASC
	END
	IF @FLAG = 'PAYOUT-METHOD'
	BEGIN
		SELECT *
		FROM (
			SELECT '' [value]
				,'SELECT' [text]
			
			UNION ALL
			
			SELECT serviceTypeId [value]
				,typeTitle [text]
			FROM SERVICETYPEMASTER(NOLOCK)
			WHERE ISNULL(ISACTIVE, 'Y') = 'Y'
			) X
		ORDER BY X.[value] ASC
	END

	IF @FLAG = 'S-BankBranch'
	BEGIN
		SET @sortBy = 'BRANCH_ID'
		SET @sortOrder = 'desc'
		SET @table = '( SELECT BRANCH_ID,
								cm.countryName countryName,
								BRANCH_NAME,
								BRANCH_CODE1,
								BRANCH_STATE,
								BRANCH_ADDRESS,
								BRANCH_PHONE,
								BRANCH_COUNTRY,
								IS_ACTIVE = CASE WHEN AB.IS_ACTIVE = 1 THEN ''YES'' ELSE ''NO'' END
					FROM API_BANK_BRANCH_LIST AB(NOLOCK)
					INNER JOIN dbo.API_BANK_LIST ABL(NOLOCK) ON ABL.BANK_ID=AB.BANK_ID
					INNER JOIN dbo.countryMaster CM(NOLOCK) ON ABL.BANK_COUNTRY=CM.countryName
					WHERE AB.BANK_ID = ''' + @bankId + ''' '
		SET @sql_filter = ''
		SET @table = @table + ')x'

		IF @BRANCH_NAME <> ''
			SET @sql_filter = @sql_filter + ' AND BRANCH_NAME LIKE  ''%' + CAST(@BRANCH_NAME AS VARCHAR) + '%'''
		--IF @PAYMENT_TYPE <> 0
		--	SET @sql_filter = @sql_filter+' AND PAYMENT_TYPE_ID =  '''+CAST(@PAYMENT_TYPE AS VARCHAR)+''''
		SET @select_field_list = '
				 countryName,BRANCH_ID,BRANCH_NAME,BRANCH_CODE1,BRANCH_STATE,BRANCH_ADDRESS,BRANCH_PHONE,BRANCH_COUNTRY,IS_ACTIVE'
        print @table
		EXEC dbo.proc_paging @table
			,@sql_filter
			,@select_field_list
			,@extra_field_list
			,@sortBy
			,@sortOrder
			,@pageSize
			,@pageNumber
	END

	IF @FLAG = 'getBranchByAgentIdForDDL'
	BEGIN
		DECLARE @COUNTRY_ID INT
			,@COLL_MODE INT

		SELECT @COUNTRY_ID = CM.COUNTRYID
			,@COLL_MODE = PAYMENT_TYPE_ID
		FROM API_BANK_LIST ABL(NOLOCK)
		INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYNAME = ABL.BANK_COUNTRY
		WHERE BANK_ID = @bankId

		IF @COUNTRY_ID = 151
		BEGIN
			SELECT NULL agentId
				,agentName = 'Any Branch'

			RETURN
		END

		SELECT BRANCH_ID agentId
			,BRANCH_NAME agentName
		FROM dbo.API_BANK_BRANCH_LIST
		WHERE BANK_ID = @bankId
			AND IS_ACTIVE = 1 --AND PAYMENT_TYPE_ID=@PAYMENT_TYPE;
	END

	IF @FLAG = 'getBranch-new'
	BEGIN
		IF @countryId IN (
				151
				,203
				)
		BEGIN
			SELECT NULL agentId
				,agentName = 'Any Branch'

			RETURN
		END

		SELECT ABM.BRANCH_ID agentId
		, agentName = CASE WHEN  '+ @countryId + ' <> '151' THEN ABM.BRANCH_NAME + ' - ' + CAST(ABM.BRANCH_CODE1 AS VARCHAR) ELSE ABM.BRANCH_NAME END  
		--	,ABM.BRANCH_NAME agentName
		FROM dbo.API_BANK_BRANCH_LIST ABM(NOLOCK)
		INNER JOIN API_BANK_LIST ABL(NOLOCK) ON ABM.BANK_ID = ABL.BANK_ID
		INNER JOIN API_BANK_LIST_MASTER API(NOLOCK) ON API.JME_BANK_CODE = ABL.JME_BANK_CODE
		WHERE API.MASTER_BANK_ID = @bankId
			AND ABM.IS_ACTIVE = 1 --AND PAYMENT_TYPE_ID=@PAYMENT_TYPE;
	END

	IF @FLAG = 'enable-disable-bank'
	BEGIN
		IF @isActive = 'YES'
		BEGIN
			UPDATE API_BANK_LIST
			SET IS_ACTIVE = 0
			WHERE BANK_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been disabled successfully.' Msg
				,@rowId id
		END
		ELSE
		BEGIN
			UPDATE API_BANK_LIST
			SET IS_ACTIVE = 1
			WHERE BANK_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been enabled successfully.' Msg
				,@rowId id
		END
	END

	IF @FLAG = 'enable-disable-bankNew'
	BEGIN
		IF @isActive = 'YES'
		BEGIN
			UPDATE API_BANK_LIST_MASTER
			SET IS_ACTIVE = 0
			WHERE MASTER_BANK_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been disabled successfully.' Msg
				,@rowId id
		END
		ELSE
		BEGIN
			UPDATE API_BANK_LIST_MASTER
			SET IS_ACTIVE = 1
			WHERE MASTER_BANK_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been enabled successfully.' Msg
				,@rowId id
		END
	END

	IF @FLAG = 'enable-disable-bankBranch'
	BEGIN
		IF @isActive = 'YES'
		BEGIN
			UPDATE dbo.API_BANK_BRANCH_LIST
			SET IS_ACTIVE = 0
			WHERE BRANCH_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been disabled successfully.' Msg
				,@rowId id
		END
		ELSE
		BEGIN
			UPDATE dbo.API_BANK_BRANCH_LIST
			SET IS_ACTIVE = 1
			WHERE BRANCH_ID = @rowId

			SELECT '0' ErrorCode
				,'Record has been enabled successfully.' Msg
				,@rowId id
		END
	END

	IF @FLAG = 'countryList'
	BEGIN
		SELECT countryCode value
			,countryName TEXT
		FROM dbo.countryMaster
	END

	IF @FLAG = 'syncBank'
	BEGIN
		IF OBJECT_ID('tempdb..#apiBanks') IS NOT NULL
			DROP TABLE #apiBanks

		DECLARE @XMLDATA XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2)

		IF(@API_PARTNER_ID='394414')
		BEGIN
		SELECT IDENTITY(INT, 1, 1) AS rowId
			        ,p.value('(CodeId)[1]', 'VARCHAR(20)') AS 'CodeId'
					,p.value('(CodeName)[1]', 'VARCHAR(150)') AS 'CodeName'					
				    ,p.value('(AdditionalData1)[1]', 'VARCHAR(150)') AS 'bankcode2'		
		INTO #apiBanks1
		FROM @XMLDATA.nodes('/ArrayOfBankResponse/BankResponse') AS apiBanks(p)

		
		UPDATE ABL
		SET ABL.TRANSFER_MODE = 'RTP' , BANK_CODE2= t.bankcode2
		FROM API_BANK_LIST ABL(NOLOCK)
		INNER JOIN #apiBanks1 T ON T.CodeId = ABL.BANK_CODE1
		INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYNAME = ABL.BANK_COUNTRY
		WHERE ABL.API_PARTNER_ID = @API_PARTNER_ID
			AND CM.COUNTRYID = '16'
			AND ABL.PAYMENT_TYPE_ID = 2
			AND ABL.IS_ACTIVE = 1

			SELECT @NEW_RECORD = COUNT(1)
		FROM #apiBanks1

		SET @MSG = 'Bank synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records updated in system.'

		EXEC proc_errorHandler 0
			,@MSG
			,NULL

			RETURN;
		END
		ELSE
		BEGIN
		SELECT IDENTITY(INT, 1, 1) AS rowId
			,p.value('(LocationIdField)[1]', 'VARCHAR(20)') AS 'Id'
			,p.value('(AgentField)[1]', 'VARCHAR(150)') AS 'Name'
			,p.value('(ext_Bank_IDField)[1]', 'VARCHAR(150)') AS 'ext_Bank_IDField'
		INTO #apiBanks
		FROM @XMLDATA.nodes('/ArrayOfBankResponse/BankResponse') AS apiBanks(p)

		---- Check If Bank Details Already Exist for cash payment or not as a payer data
		UPDATE #apiBanks
		SET Name = LTRIM(RTRIM(Name))
			,Id = LTRIM(RTRIM(Id))

		INSERT INTO tbl_bank_test
		SELECT *
		FROM #apiBanks

		RETURN

		UPDATE dbo.API_BANK_LIST
		SET PAYMENT_TYPE_ID = 0
		FROM #apiBanks ab
		WHERE LTRIM(RTRIM(ab.Id)) = LTRIM(RTRIM(BANK_CODE1))
			AND LTRIM(RTRIM(ab.Name)) = LTRIM(RTRIM(BANK_NAME))
			AND PAYMENT_TYPE_ID = 1

		--DELETE EXISTING DATA FROM TEMP TABLEf
		DELETE TMP
		FROM #apiBanks TMP
		INNER JOIN API_BANK_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BANK_CODE1)) = LTRIM(RTRIM(TMP.Id))
			AND LTRIM(RTRIM(ABL.BANK_NAME)) = LTRIM((TMP.Name))
		WHERE LTRIM(RTRIM(BANK_COUNTRY)) = LTRIM(RTRIM(@BANK_COUNTRY))
			AND API_PARTNER_ID = @API_PARTNER_ID

		--INSERT NEW DATA INTO MAIN TABLE
		INSERT INTO API_BANK_LIST (
			API_PARTNER_ID
			,BANK_NAME
			,BANK_CODE1
			,SUPPORT_CURRENCY
			,BANK_COUNTRY
			,PAYMENT_TYPE_ID
			,IS_ACTIVE
			)
		SELECT @API_PARTNER_ID
			,Name
			,Id
			,LTRIM(RTRIM(@BANK_CURRENCY))
			,LTRIM(RTRIM(@BANK_COUNTRY))
			,2
			,1
		FROM #apiBanks

		SELECT @NEW_RECORD = COUNT(1)
		FROM #apiBanks

		SET @MSG = 'Bank synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records inserted in system.'

		EXEC proc_errorHandler 0
			,@MSG
			,NULL

		END
	END

	IF @FLAG = 'syncBankBranch'
	BEGIN
		DECLARE @BANK_ID INT ,
			@BANK_CODE1 INT  
		IF @API_PARTNER_ID = '394414'
		BEGIN 
		  PRINT @bankId;
			IF  EXISTS ( SELECT * FROM API_BANK_LIST WHERE bank_id=@bankId)
			BEGIN 
				IF OBJECT_ID('tempdb..#TempBranchBrac') IS NOT NULL
				DROP TABLE #TempBranchBrac

				DECLARE @XMLBRANCH XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2) 
	
				SELECT IDENTITY(INT, 1, 1) AS rowId
					,p.value('(CodeId)[1]', 'VARCHAR(20)') AS 'CodeId'
					,p.value('(CodeName)[1]', 'VARCHAR(150)') AS 'BranchName'
					,p.value('(Address)[1]', 'VARCHAR(150)') AS 'BranchAddress'
					,p.value('(ParentCodeId)[1]', 'VARCHAR(150)') AS 'BankId'
					,p.value('(ParentName)[1]', 'VARCHAR(150)') AS 'BankName'
				    ,p.value('(AdditionalData1)[1]', 'VARCHAR(150)') AS 'RoutingNo'		
					,p.value('(LastUpdatedDate)[1]', 'VARCHAR(150)') AS 'LastUpdatedDate'
				INTO #TempBranchBrac
				FROM @XMLBRANCH.nodes('/ArrayOfBracBranchDatum/BracBranchDatum') AS apiBranches(p)

				DELETE TMP
				FROM #TempBranchBrac TMP
				INNER JOIN dbo.API_BANK_BRANCH_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BRANCH_CODE1)) = LTRIM(RTRIM(TMP.RoutingNo))
				AND LTRIM(RTRIM(abl.BRANCH_NAME)) = LTRIM(RTRIM(TMP.BranchName))
				AND ABL.PAYMENT_TYPE_ID = 2 and ABl.BANK_ID=@bankId

				--SELECT @BANK_ID = BANK_ID , @BANK_COUNTRY = BANK_COUNTRY
				--FROM dbo.API_BANK_LIST
				--WHERE CAST(BANK_CODE1 as varchar) = CAST( @bankId as varchar) and CAST( API_PARTNER_ID as varchar)= CAST(@API_PARTNER as varchar) --@BANK_CODE1

				INSERT INTO API_BANK_BRANCH_LIST (
											  BANK_ID
											 ,BRANCH_NAME
											 ,BRANCH_CODE1
											 ,BRANCH_CODE2
											 ,BRANCH_STATE
											 ,BRANCH_DISTRICT
											 ,BRANCH_ADDRESS
											 ,BRANCH_PHONE
											 ,BRANCH_EMAIL
											 ,BRANCH_COUNTRY
											 ,IS_ACTIVE
											 ,PAYMENT_TYPE_ID
											 ,CREATED_ON
										 )

										SELECT  @bankId
											   ,BranchName
											   ,RoutingNo
											   ,CodeId
											   ,NULL
											   ,NULL
											   ,NULL
											   ,BankId
											   ,BankName
											   ,'BANGLADESH'
											   ,1
											   ,2
											   ,GETDATE()
										FROM #TempBranchBrac T

			SELECT @NEW_RECORD = COUNT(1)
			FROM #TempBranchBrac

			SET @MSG = 'Bank Branch synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records inserted in system.'
			END
			ELSE
			BEGIN
			SET @MSG='Invalid bank code';
		END
		END
		ELSE 
		BEGIN
			IF OBJECT_ID('tempdb..#apiBanksBranch') IS NOT NULL
				DROP TABLE #apiBanksBranch

			DECLARE @XMLBranchData XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2)
				,@banksId BIGINT = NULL
				,@City_Id BIGINT = NULL

			SELECT IDENTITY(INT, 1, 1) AS rowId
				,p.value('(BankBranchID)[1]', 'VARCHAR(200)') AS 'BranchCode'
				,p.value('(BankID)[1]', 'VARCHAR(200)') AS 'BankCode'
				,p.value('(BankBranchName)[1]', 'VARCHAR(200)') AS 'BranchName'
			INTO #apiBanksBranch
			FROM @XMLBranchData.nodes('/ArrayOfBankBranchResponse/BankBranchResponse') AS apiBanksBranch(p)

			--DELETE EXISTING DATA FROM TEMP TABLE
			DELETE TMP
			FROM #apiBanksBranch TMP
			INNER JOIN dbo.API_BANK_BRANCH_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BRANCH_CODE1)) = LTRIM(RTRIM(TMP.BranchCode))
				AND LTRIM(RTRIM(abl.BRANCH_NAME)) = LTRIM(RTRIM(TMP.BranchName))
				AND ABL.PAYMENT_TYPE_ID = 2

			SELECT @banksId = BANK_ID
			FROM dbo.API_BANK_LIST
			WHERE BANK_CODE1 = @bankId

			SELECT @City_Id = CITY_ID
			FROM dbo.API_CITY_LIST
			WHERE CITY_CODE = @CityId

			--INSERT NEW DATA INTO MAIN TABLE
			INSERT INTO API_BANK_BRANCH_LIST (
				BRANCH_NAME
				,BANK_ID
				,BRANCH_CODE1
				,BRANCH_DISTRICT
				,BRANCH_COUNTRY
				,IS_ACTIVE
				,PAYMENT_TYPE_ID
				)
			SELECT LTRIM(RTRIM(BranchName))
				,@banksId
				,LTRIM(RTRIM(BranchCode))
				,@City_Id
				,LTRIM(RTRIM(@BANK_COUNTRY))
				,1
				,2
			FROM #apiBanksBranch

			SELECT @NEW_RECORD = COUNT(1)
			FROM #apiBanksBranch

			SET @MSG = 'Bank Branch synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records inserted in system.'
		END

		EXEC proc_errorHandler 0
			,@MSG
			,NULL
	END

	IF @FLAG = 'payerForJmeNepal'
	BEGIN
		IF OBJECT_ID('tempdb..#apiBankBranchForJMEData') IS NOT NULL
			DROP TABLE #apiBankBranchForJMEData

		IF OBJECT_ID('tempdb..#jmeTempBankList') IS NOT NULL
			DROP TABLE #jmeTempBankList

		DECLARE @XMLBankPayerFORJMEData XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2)

		SELECT IDENTITY(INT, 1, 1) AS rowId
			,p.value('(aGENTField)[1]', 'VARCHAR(200)') AS 'BankName'
			,p.value('(lOCATIONIDField)[1]', 'VARCHAR(200)') AS 'BranchCode'
			,p.value('(bRANCHField)[1]', 'VARCHAR(200)') AS 'BranchName'
			,p.value('(aDDRESSField)[1]', 'VARCHAR(200)') AS 'BranchAddress'
			,p.value('(cITYField)[1]', 'VARCHAR(200)') AS 'CityName'
			,p.value('(cURRENCYField)[1]', 'VARCHAR(200)') AS 'CurrencyCode'
		INTO #apiBankBranchForJMEData
		FROM @XMLBankPayerFORJMEData.nodes('/ArrayOfJmeNepalResponse/JmeNepalResponse') AS apiBankPayerData(p)

		UPDATE #apiBankBranchForJMEData
		SET BankName = REPLACE(LTRIM(RTRIM(BankName)), '&amp', '&')
			,BranchCode = LTRIM(RTRIM(BranchCode))
			,BranchName = LTRIM(RTRIM(BranchName))
			,CityName = LTRIM(RTRIM(CityName))
			,CurrencyCode = LTRIM(RTRIM(CurrencyCode))

		--DELETE EXISTING DATA FROM TEMP TABLE
		DELETE P
		FROM #apiBankBranchForJMEData P
		INNER JOIN dbo.API_BANK_BRANCH_LIST(NOLOCK) B ON LTRIM(RTRIM(b.BRANCH_CODE1)) = BranchCode
			AND LTRIM(RTRIM(b.BRANCH_NAME)) = BranchName
			AND B.PAYMENT_TYPE_ID = 2
			AND B.BRANCH_COUNTRY = @BANK_COUNTRY
		INNER JOIN dbo.API_BANK_LIST abl(NOLOCK) ON abl.BANK_ID = B.BANK_ID
			AND abl.API_PARTNER_ID = @API_PARTNER_ID

		--INSERT NEW DATA INTO BANK AND BANK BRANCH TABLE
		IF EXISTS (
				SELECT 1
				FROM #apiBankBranchForJMEData
				)
		BEGIN
			SELECT p.BankName
				,p.CurrencyCode
			INTO #jmeTempBankList
			FROM #apiBankBranchForJMEData p
			GROUP BY p.BankName
				,p.CurrencyCode

			--SELECT 'jmeBankTempCode'+CONVERT(VARCHAR(150),ROW_NUMBER() OVER (ORDER BY jb.BankName)) AS rowNo,jb.BankName,jb.CurrencyCode FROM #jmeTempBankList jb
			--RETURN
			DELETE j
			FROM #jmeTempBankList j
			INNER JOIN dbo.API_BANK_LIST(NOLOCK) abl ON LTRIM(RTRIM(abl.BANK_NAME)) = j.BankName
				AND abl.API_PARTNER_ID = @API_PARTNER_ID

			INSERT INTO dbo.API_BANK_LIST (
				API_PARTNER_ID
				,BANK_NAME
				,BANK_CODE1
				,BANK_CODE2
				,BANK_STATE
				,BANK_DISTRICT
				,BANK_ADDRESS
				,BANK_PHONE
				,BANK_EMAIL
				,SUPPORT_CURRENCY
				,BANK_COUNTRY
				,PAYMENT_TYPE_ID
				,IS_ACTIVE
				,JME_BANK_CODE
				)
			SELECT @API_PARTNER_ID
				,BankName
				,''
				,''
				,''
				,''
				,''
				,''
				,''
				,CurrencyCode
				,'Nepal'
				,'0'
				,1
				,'' --'jmeBankTempCode'+CONVERT(VARCHAR(50),ROW_NUMBER() OVER (ORDER BY jb.BankName)) AS rowNo
			FROM #jmeTempBankList

			INSERT INTO API_BANK_BRANCH_LIST (
				BRANCH_NAME
				,BANK_ID
				,BRANCH_CODE1
				,BRANCH_DISTRICT
				,BRANCH_COUNTRY
				,IS_ACTIVE
				,PAYMENT_TYPE_ID
				)
			SELECT abjd.BranchName
				,ABL.BANK_ID
				,abjd.BranchCode
				,abjd.CityName
				,LTRIM(RTRIM(@BANK_COUNTRY))
				,1
				,2
			FROM #apiBankBranchForJMEData abjd
			INNER JOIN dbo.API_BANK_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BANK_NAME)) = abjd.BankName
				AND ABL.API_PARTNER_ID = @API_PARTNER_ID

			SELECT @NEW_RECORD = COUNT(1)
			FROM #apiBankBranchForJMEData

			SELECT @NEW_BRANCH_RECORD = COUNT(1)
			FROM #jmeTempBankList
		END

		SET @MSG = 'Jme Nepal . Total Bank ' + CAST(@NEW_BRANCH_RECORD AS VARCHAR) + ' And Bank Branch ' + CAST(@NEW_RECORD AS VARCHAR) + ' new records add successfully.'

		EXEC proc_errorHandler 0
			,@MSG
			,NULL
	END

	IF @FLAG = 'syncGMEBank'
	BEGIN
		IF OBJECT_ID('tempdb..#apiGMEBanks') IS NOT NULL
			DROP TABLE #apiGMEBanks

		DECLARE @XMLGMEDATA XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2)

		SELECT IDENTITY(INT, 1, 1) AS rowId
			,p.value('(bankCodeField)[1]', 'VARCHAR(25)') AS 'bankCode'
			,p.value('(bankNameField)[1]', 'VARCHAR(150)') AS 'Name'
			,p.value('(addressField)[1]', 'VARCHAR(250)') AS 'Address'
			,p.value('(cityField)[1]', 'VARCHAR(100)') AS 'City'
		INTO #apiGMEBanks
		FROM @XMLGMEDATA.nodes('/ArrayOfGMEBANKRESPONSE/GMEBANKRESPONSE') AS apiGMEBanks(p)

		---- Check If Bank Details Already Exist for cash payment or not as a payer data
		UPDATE #apiGMEBanks
		SET Name = LTRIM(RTRIM(Name))
			,bankCode = LTRIM(RTRIM(bankCode))
			,Address = LTRIM(RTRIM(Address))
			,City = LTRIM(RTRIM(City))

		--UPDATE dbo.API_BANK_LIST SET PAYMENT_TYPE_ID=0
		--FROM #apiBanks ab WHERE LTRIM(RTRIM(ab.Id))=LTRIM(RTRIM(BANK_CODE1)) AND LTRIM(RTRIM(ab.Name))=LTRIM(RTRIM(BANK_NAME)) AND PAYMENT_TYPE_ID=1
		--DELETE EXISTING DATA FROM TEMP TABLEf
		DELETE TMP
		FROM #apiGMEBanks TMP
		INNER JOIN API_BANK_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BANK_CODE1)) = TMP.bankCode
			AND LTRIM(RTRIM(ABL.BANK_NAME)) = TMP.Name
			AND LTRIM(RTRIM(BANK_COUNTRY)) = LTRIM(RTRIM(@BANK_COUNTRY))
			AND API_PARTNER_ID = @API_PARTNER_ID

		--INSERT NEW DATA INTO MAIN TABLE
		INSERT INTO API_BANK_LIST (
			API_PARTNER_ID
			,BANK_NAME
			,BANK_CODE1
			,SUPPORT_CURRENCY
			,BANK_COUNTRY
			,PAYMENT_TYPE_ID
			,IS_ACTIVE
			,BANK_ADDRESS
			,BANK_DISTRICT
			)
		SELECT @API_PARTNER_ID
			,Name
			,bankCode
			,LTRIM(RTRIM(@BANK_CURRENCY))
			,LTRIM(RTRIM(@BANK_COUNTRY))
			,2
			,1
			,Address
			,City
		FROM #apiGMEBanks

		SELECT @NEW_RECORD = COUNT(1)
		FROM #apiGMEBanks

		SET @MSG = 'Bank synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records inserted in system.'

		EXEC proc_errorHandler 0
			,@MSG
			,NULL
	END

	IF @FLAG = 'syncGMEBankBranch'
	BEGIN
		IF OBJECT_ID('tempdb..#apiGMEBanksBranch') IS NOT NULL
			DROP TABLE #apiGMEBanksBranch

		DECLARE @XMLGMEBranchData XML = CONVERT(XML, replace(@XML, '&', '&amp;'), 2)
			,@gmebanksId BIGINT = NULL

		SELECT IDENTITY(INT, 1, 1) AS rowId
			,p.value('(bankBranchCodeField)[1]', 'VARCHAR(100)') AS 'BranchCode'
			,p.value('(bankBranchField)[1]', 'VARCHAR(250)') AS 'BranchName'
			,p.value('(branchAddressField)[1]', 'VARCHAR(100)') AS 'BranchAddress'
			,p.value('(branchCityField)[1]', 'VARCHAR(100)') AS 'BranchCityName'
			,p.value('(locationIdField)[1]', 'VARCHAR(200)') AS 'BranchLocation'
		INTO #apiGMEBanksBranch
		FROM @XMLGMEBranchData.nodes('/ArrayOfGMEBBRANCH/GMEBBRANCH') AS apiGMEBanksBranch(p)

		UPDATE #apiGMEBanksBranch
		SET BranchCode = LTRIM(RTRIM(BranchCode))
			,BranchName = LTRIM(RTRIM(BranchName))
			,BranchAddress = LTRIM(RTRIM(BranchAddress))
			,BranchCityName = LTRIM(RTRIM(BranchCityName))
			,BranchLocation = LTRIM(RTRIM(BranchLocation))

		SELECT @gmebanksId = BANK_ID
		FROM dbo.API_BANK_LIST
		WHERE BANK_CODE1 = @bankId

		--DELETE EXISTING DATA FROM TEMP TABLE
		DELETE TMP
		FROM #apiGMEBanksBranch TMP
		INNER JOIN dbo.API_BANK_BRANCH_LIST ABL(NOLOCK) ON LTRIM(RTRIM(ABL.BRANCH_CODE1)) = TMP.BranchCode
			AND LTRIM(RTRIM(abl.BRANCH_NAME)) = TMP.BranchName
			AND ABL.PAYMENT_TYPE_ID = 2
			AND LTRIM(RTRIM(ABL.BANK_ID)) = @gmebanksId

		--INSERT NEW DATA INTO MAIN TABLE
		INSERT INTO API_BANK_BRANCH_LIST (
			BRANCH_NAME
			,BANK_ID
			,BRANCH_CODE1
			,BRANCH_ADDRESS
			,BRANCH_DISTRICT
			,BRANCH_COUNTRY
			,IS_ACTIVE
			,PAYMENT_TYPE_ID
			)
		SELECT BranchName
			,@gmebanksId
			,BranchCode
			,BranchAddress
			,BranchCityName
			,LTRIM(RTRIM(@BANK_COUNTRY))
			,1
			,2
		FROM #apiGMEBanksBranch

		SELECT @NEW_RECORD = COUNT(1)
		FROM #apiGMEBanksBranch

		SET @MSG = 'Bank Branch synced successfully.' + CAST(@NEW_RECORD AS VARCHAR) + ' new records inserted in system.'

		EXEC proc_errorHandler 0
			,@MSG
			,NULL
	END

	IF @FLAG = 'BANKLIST'
	BEGIN
		DECLARE @COUNTRY_NAME VARCHAR(30) = 'Philippines'
			,@PROVIDER_ID INT = 394130 --TF

		SELECT DISTINCT CM.countryId
			,CM.countryCode
			,CM.countryName
			,CCM.currencyCode
			,agentId = @PROVIDER_ID
			,agentName = ''
		FROM COUNTRYMASTER CM(NOLOCK)
		INNER JOIN COUNTRYCURRENCY CC(NOLOCK) ON CC.COUNTRYID = CM.COUNTRYID
		INNER JOIN CURRENCYMASTER CCM(NOLOCK) ON CCM.currencyId = CC.currencyId
		WHERE COUNTRYNAME = @COUNTRY_NAME
			AND CURRENCYCODE <> 'JPY'

		SELECT BANK_CODE = BANK_CODE1
			,PROVIDER_ID = @PROVIDER_ID
			,BANK_NAME
		FROM API_BANK_LIST ABL(NOLOCK)
		WHERE BANK_COUNTRY = @COUNTRY_NAME
			AND API_PARTNER_ID = @PROVIDER_ID
		ORDER BY BANK_NAME
	END
END
	--EXEC PROC_API_BANK_BRANCH_SETUP @flag='BANKLIST'