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.
370 lines
22 KiB
370 lines
22 KiB
USE [FastMoneyPro_Remit]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[Proc_UpdateBranchCode] Script Date: 4/8/2024 6:24:00 AM ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
ALTER PROC [dbo].[Proc_UpdateBranchCode]
|
|
@flag VARCHAR(200)
|
|
,@pCountryId INT = NULL
|
|
,@pCountryName VARCHAR(50) = NULL
|
|
,@countryId VARCHAR(50) = NULL
|
|
,@bankId INT = NULL
|
|
,@branchCode VARCHAR(50) = NULL
|
|
,@branchId BIGINT = NULL
|
|
,@branchName VARCHAR(50) = NULL
|
|
,@editedBranchName VARCHAR(50) = NULL
|
|
,@user varchar(20) = NULL
|
|
,@partnerId VARCHAR(50) = NULL
|
|
,@param varchar(25) = NULL
|
|
,@bankName VARCHAR(50) = NULL
|
|
,@bankCountry Varchar(50) = NULL
|
|
,@bankState VARCHAR(50) = NULL
|
|
,@bankDistrict Varchar(50) = NULL
|
|
,@bankAddress VARCHAR(50) = NULL
|
|
,@bankPhone Varchar(50) = NULL
|
|
,@bankEmail VARCHAR(50) = NULL
|
|
,@sCurrency Varchar(50) = NULL
|
|
,@MASTER_BANK_ID INT = NULL
|
|
,@is_Active BIT = NULL
|
|
,@pMode INT = NULL
|
|
, @bankCode1 VARCHAR(50) = NULL
|
|
,@bankCode2 VARCHAR(50) = NULL
|
|
AS
|
|
-- #13526 - UPDATE BRANCH CODE - ADD PARTNER FILTER
|
|
BEGIN
|
|
IF @FLAG = 'getPartnerByCountry'
|
|
BEGIN
|
|
select bankId = 0
|
|
, 0 NS
|
|
, FLAG = 'E'
|
|
, AGENTNAME = 'Select Partner'
|
|
|
|
UNION
|
|
|
|
SELECT bankId= TPC.AgentId,
|
|
0 NS,
|
|
FLAG = 'E',
|
|
AGENTNAME = AM.AGENTNAME
|
|
FROM TblPartnerwiseCountry TPC(NOLOCK)
|
|
INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYId = TPC.CountryId
|
|
INNER JOIN AGENTMASTER(NOLOCK) AM ON AM.agentId = TPC.AGENTID
|
|
WHERE CM.COUNTRYID = @pCountryId
|
|
AND (TPC.PaymentMethod = 2 or TPC.PaymentMethod is null)
|
|
--ORDER BY agentName
|
|
END
|
|
ELSE IF @Flag = 'GetPartnetName'
|
|
BEGIN
|
|
SELECT AGENTNAME
|
|
FROM AGENTMASTER
|
|
WHERE agentId = @Partnerid
|
|
|
|
END
|
|
ELSE IF @Flag = 'GetbankMasterId'
|
|
BEGIN
|
|
SELECT MASTER_BANK_ID
|
|
FROM API_BANK_LIST_MASTER
|
|
WHERE BANK_NAME = @Partnerid
|
|
|
|
END
|
|
ELSE IF @Flag = 'receiverListPMode'
|
|
BEGIN
|
|
select bankId = 0
|
|
, 0 NS
|
|
, FLAG = 'E'
|
|
, AGENTNAME = 'Select PMode'
|
|
|
|
FROM receiverInformation RI(NOLOCK)
|
|
INNER JOIN countryMaster CM(NOLOCK) ON CM.countryname = RI.country
|
|
WHERE CM.COUNTRYID = @pCountryId
|
|
AND (paymentMode = 2 or paymentMode is null)
|
|
END
|
|
IF @FLAG = 'getBankByPartner'
|
|
BEGIN
|
|
select bankId = 0
|
|
, 0 NS
|
|
, FLAG = 'E'
|
|
, AGENTNAME = 'Select Bank'
|
|
, bank_name = ''
|
|
|
|
UNION
|
|
|
|
SELECT bankId=AL.BANK_ID,
|
|
0 NS,
|
|
FLAG = 'E',
|
|
AGENTNAME = AL.BANK_NAME, --+ ' || ' + AL.BANK_CODE1 ,
|
|
al.bank_name
|
|
FROM API_BANK_LIST AL(NOLOCK)
|
|
INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYNAME = AL.BANK_COUNTRY
|
|
WHERE CM.COUNTRYID = @pCountryId
|
|
AND AL.IS_ACTIVE = 1
|
|
AND AL.PAYMENT_TYPE_ID = 2
|
|
and AL.API_PARTNER_ID = @partnerId
|
|
ORDER BY BANK_NAME
|
|
END
|
|
ELSE IF @FLAG = 'getBranchByBankAndCountry'
|
|
BEGIN
|
|
select bankId = 0
|
|
, 0 NS
|
|
, FLAG = 'E'
|
|
, AGENTNAME = 'Select Branch'
|
|
, BRANCH_NAME = ''
|
|
|
|
union
|
|
|
|
SELECT bankId=AL.BRANCH_ID,
|
|
0 NS,
|
|
FLAG = 'E',
|
|
AGENTNAME = AL.BRANCH_NAME + ' || ' + AL.BRANCH_CODE1,
|
|
branch_name
|
|
FROM API_BANK_BRANCH_LIST AL(NOLOCK)
|
|
INNER JOIN COUNTRYMASTER CM(NOLOCK) ON CM.COUNTRYNAME = AL.BRANCH_COUNTRY
|
|
WHERE CM.COUNTRYID = @pCountryId
|
|
AND AL.BANK_ID = @bankId
|
|
AND AL.IS_ACTIVE = 1
|
|
AND AL.PAYMENT_TYPE_ID = 2
|
|
ORDER BY BRANCH_NAME
|
|
END
|
|
|
|
IF @FLAG = 'getBankMasterByCountry'
|
|
BEGIN
|
|
-- Select default row with 'Select Partner' when bankId is 0
|
|
SELECT
|
|
bankId = 0
|
|
, 0 NS
|
|
, FLAG = 'E'
|
|
, AGENTNAME= 'Select All Banks'
|
|
|
|
UNION
|
|
|
|
SELECT
|
|
bankId= TPC.Master_Bank_Id,
|
|
0 NS,
|
|
FLAG = 'E',
|
|
AGENTNAME= TPC.Bank_Name
|
|
FROM
|
|
API_BANK_LIST_MASTER TPC(NOLOCK)
|
|
INNER JOIN
|
|
COUNTRYMASTER CM(NOLOCK) ON CM.countryName = TPC.Bank_Country
|
|
where CM.countryid = @pCountryId
|
|
END
|
|
ELSE IF @FLAG = 'updateBranchCode'
|
|
BEGIN
|
|
IF EXISTS(select 1 from API_BANK_BRANCH_LIST WHERE BANK_ID = @bankId and BRANCH_ID = @branchId)
|
|
BEGIN
|
|
INSERT INTO API_BANK_BRANCH_LIST_LOG
|
|
SELECT BANK_ID,BRANCH_ID,BRANCH_CODE1,@branchCode,@user,GETDATE() FROM API_BANK_BRANCH_LIST
|
|
WHERE BANK_ID = @bankId and BRANCH_ID = @branchId
|
|
|
|
UPDATE API_BANK_BRANCH_LIST SET BRANCH_CODE1 = @branchCode,BRANCH_NAME = @editedBranchName , IS_ACTIVE = @IS_ACTIVE where BANK_ID = @bankId and BRANCH_ID = @branchId
|
|
|
|
SELECT 0 ERRORCODE,'BranchCode updated successfully' Msg,null
|
|
END
|
|
END
|
|
ELSE IF @flag = 'insertBranch'
|
|
BEGIN
|
|
SELECT @pCountryName = COUNTRYNAME FROM countryMaster WHERE COUNTRYID = @pcountryId
|
|
IF NOT EXISTS(SELECT 'A',* FROM API_BANK_LIST WHERE BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Bank Does not exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_BRANCH_LIST WHERE BANK_ID = @bankId AND BRANCH_COUNTRY = @pCountryName AND BRANCH_CODE1 = @branchCode AND BRANCH_NAME = @branchName)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Branch with same name and code already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_BRANCH_LIST WHERE BANK_ID = @bankId AND BRANCH_COUNTRY = @pCountryName AND BRANCH_CODE1 = @branchCode)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Branch with same country and code already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO API_BANK_BRANCH_LIST (BANK_ID,BRANCH_NAME,BRANCH_CODE1,BRANCH_COUNTRY,IS_ACTIVE,PAYMENT_TYPE_ID)
|
|
VALUES (@bankId,@branchName,@branchCode,@pCountryName,@IS_ACTIVE ,2)
|
|
|
|
SELECT '0' ErrorCode,'Branch Inserted Successfully' Msg,@bankId id
|
|
|
|
|
|
END
|
|
ELSE IF @flag = 'insertBank'
|
|
BEGIN
|
|
SELECT @pCountryName = COUNTRYNAME FROM countryMaster WHERE COUNTRYID = @pcountryId
|
|
--IF NOT EXISTS(SELECT 'A',* FROM API_BANK_LIST_MASTER WHERE MASTER_BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName)
|
|
--BEGIN
|
|
-- SELECT '1' ErrorCode,'Bank Does not exists' Msg,@bankId id
|
|
-- RETURN
|
|
--END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_LIST_MASTER WHERE MASTER_BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName AND BANK_NAME = @bankName)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Bank with same name already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_LIST_MASTER WHERE MASTER_BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName AND PAYMENT_TYPE_ID = @pMode)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Bank with same country already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO API_BANK_LIST_MASTER(
|
|
BANK_NAME,
|
|
BANK_COUNTRY,
|
|
SUPPORT_CURRENCY,
|
|
JME_BANK_CODE,
|
|
BANK_STATE,
|
|
BANK_ADDRESS,
|
|
BANK_DISTRICT,
|
|
BANK_PHONE,
|
|
BANK_EMAIL,
|
|
IS_ACTIVE,
|
|
PAYMENT_TYPE_ID
|
|
)
|
|
VALUES (
|
|
@bankName,
|
|
(SELECT cm.CountryName FROM countryMaster cm WHERE cm.CountryID = @pCountryName),
|
|
@sCurrency,
|
|
'JME000' + CAST(IDENT_CURRENT('API_BANK_LIST_MASTER') AS VARCHAR),
|
|
@bankState,
|
|
@bankAddress,
|
|
@bankDistrict,
|
|
@bankPhone,
|
|
@bankEmail,
|
|
@is_Active,
|
|
@pMode
|
|
);
|
|
SELECT '0' ErrorCode,'Bank Inserted Successfully' Msg,@bankId id
|
|
|
|
|
|
END
|
|
|
|
ELSE IF @FLAG = 'updateBank'
|
|
BEGIN
|
|
UPDATE API_BANK_LIST_MASTER
|
|
SET
|
|
BANK_NAME = @bankName,
|
|
BANK_COUNTRY = (SELECT cm.CountryName FROM countryMaster cm WHERE cm.CountryID = @pCountryName),
|
|
SUPPORT_CURRENCY = @sCurrency,
|
|
BANK_STATE = @bankState,
|
|
BANK_ADDRESS = @bankAddress,
|
|
BANK_DISTRICT = @bankDistrict,
|
|
BANK_PHONE = @bankPhone,
|
|
BANK_EMAIL = @bankEmail,
|
|
IS_ACTIVE = @is_Active,
|
|
PAYMENT_TYPE_ID = @pMode
|
|
WHERE
|
|
MASTER_BANK_ID = @bankId;
|
|
|
|
SELECT '0' AS ErrorCode, 'Bank Updated Successfully' AS Msg
|
|
END
|
|
|
|
|
|
ELSE IF @flag = 'insertBankMapping'
|
|
|
|
BEGIN
|
|
|
|
SELECT @pCountryName = COUNTRYNAME FROM countryMaster WHERE COUNTRYID = @pcountryId
|
|
--IF NOT EXISTS(SELECT 'A',* FROM API_BANK_LIST_MASTER WHERE MASTER_BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName)
|
|
--BEGIN
|
|
-- SELECT '1' ErrorCode,'Bank Does not exists' Msg,@bankId id
|
|
-- RETURN
|
|
--END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_LIST WHERE BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName AND BANK_CODE1 = @bankCode1 AND BANK_NAME = @bankName)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Bank with same name and code already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
IF EXISTS (SELECT 'A',* FROM API_BANK_LIST WHERE BANK_ID = @bankId AND BANK_COUNTRY = @pCountryName AND BANK_CODE1 = @branchCode)
|
|
BEGIN
|
|
SELECT '1' ErrorCode,'Bank with same country and code already exists' Msg,@bankId id
|
|
RETURN
|
|
END
|
|
|
|
INSERT INTO API_BANK_LIST(
|
|
BANK_NAME,
|
|
API_PARTNER_ID,
|
|
BANK_COUNTRY,
|
|
SUPPORT_CURRENCY,
|
|
JME_BANK_CODE,
|
|
BANK_CODE1,
|
|
BANK_CODE2,
|
|
BANK_ADDRESS,
|
|
IS_ACTIVE,
|
|
PAYMENT_TYPE_ID
|
|
)
|
|
VALUES (
|
|
(SELECT ablm.BANK_NAME FROM API_BANK_LIST_MASTER ablm WHERE ablm.Master_Bank_Id = @bankName),
|
|
@partnerId,
|
|
(SELECT cm.CountryName FROM CountryMaster cm INNER JOIN API_BANK_LIST_MASTER ablm ON ablm.Bank_Country = cm.countryName WHERE ablm.Master_Bank_Id = @bankName OR ablm.Bank_Country = @pCountryName),
|
|
@sCurrency,
|
|
'JME000' + CAST((SELECT ablm.Master_Bank_Id FROM API_BANK_LIST_MASTER ablm WHERE ablm.Master_Bank_Id = @bankName) AS VARCHAR),
|
|
@bankCode1,
|
|
@bankCode2,
|
|
@bankAddress,
|
|
@is_Active,
|
|
@pMode
|
|
);
|
|
|
|
SELECT '0' ErrorCode,'Bank Inserted Successfully' Msg,@bankId id
|
|
|
|
|
|
END
|
|
|
|
ELSE IF @FLAG = 'updateBankMapping'
|
|
BEGIN
|
|
UPDATE API_BANK_LIST
|
|
SET
|
|
BANK_NAME = (SELECT ablm.BANK_NAME FROM API_BANK_LIST_MASTER ablm WHERE ablm.Master_Bank_Id = @bankName),
|
|
BANK_COUNTRY = (SELECT cm.CountryName FROM CountryMaster cm INNER JOIN API_BANK_LIST_MASTER ablm ON ablm.Bank_Country = cm.countryName WHERE ablm.Master_Bank_Id = @bankName OR ablm.Bank_Country = @pCountryName),
|
|
API_PARTNER_ID = @partnerId,
|
|
SUPPORT_CURRENCY = @sCurrency,
|
|
BANK_CODE1 = @bankCode1,
|
|
BANK_CODE2 = @bankCode2,
|
|
BANK_ADDRESS = @bankAddress,
|
|
IS_ACTIVE = @is_Active,
|
|
PAYMENT_TYPE_ID = @pMode
|
|
WHERE
|
|
BANK_ID = @bankId;
|
|
|
|
SELECT '0' AS ErrorCode, 'Bank Updated Successfully' AS Msg
|
|
END
|
|
|
|
ELSE IF @flag = 'editBankMapping'
|
|
BEGIN
|
|
SELECT * FROM Api_Bank_lIST WITH(NOLOCK) WHERE Bank_Id = @bankId
|
|
END
|
|
ELSE IF @flag = 'editBank'
|
|
BEGIN
|
|
SELECT * FROM API_BANK_LIST_MASTER WITH(NOLOCK) WHERE MASTER_BANK_ID = @bankId
|
|
END
|
|
|
|
ELSE IF @flag = 'editBranch'
|
|
BEGIN
|
|
SELECT * FROM API_BANK_BRANCH_LIST WITH(NOLOCK) WHERE BRANCH_ID = @bankId
|
|
END
|
|
|
|
ELSE IF @FLAG = 'deleteBankMapping'
|
|
BEGIN
|
|
DELETE FROM Api_Bank_lIST WHERE BANK_ID = @bankId
|
|
SELECT '0' ErrorCode ,'Bank has been deleted successfully.' Msg ,
|
|
null id;
|
|
|
|
END
|
|
|
|
ELSE IF @FLAG = 'deleteBank'
|
|
BEGIN
|
|
DELETE FROM API_BANK_LIST_MASTER WHERE MASTER_BANK_ID = @bankId
|
|
SELECT '0' ErrorCode ,'Bank has been deleted successfully.' Msg ,
|
|
null id;
|
|
|
|
END
|
|
|
|
|
|
ELSE IF @FLAG = 'deleteBranch'
|
|
BEGIN
|
|
DELETE FROM API_BANK_BRANCH_LIST WHERE BRANCH_ID = @bankId
|
|
SELECT '0' ErrorCode ,'Branch has been deleted successfully.' Msg ,
|
|
null id;
|
|
|
|
END
|
|
end
|