IME-LONDON
/
MOBILE_API
8
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
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.
59 Commits
3 Branches
0 Tags
4.7 MiB
 
 
 
Branch: master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
MOBILE_API/DB/SPs/PROC_REPROCESS_HOLD_TXN.sql

499 lines
19 KiB
Raw Permalink Blame History

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
/**
exec PROC_REPROCESS_HOLD_TXN @flag= 'list'
exec PROC_REPROCESS_HOLD_TXN @flag= 'check_reprocess_req_type', @Agent = '393227', @controlNo = '80579837605'
SELECT * FROM Application_Log.dbo.vwTpApilogs(NOLOCK) WHERE controlNo like '%80551679985%'
*/
ALTER PROCEDURE [dbo].[PROC_REPROCESS_HOLD_TXN](
@flag VARCHAR(50) = NULL
,@User VARCHAR(50) = NULL
,@controlNo VARCHAR(50) = NULL
,@controlNo2 VARCHAR(50) = NULL
,@Agent VARCHAR(50) = NULL
,@Country VARCHAR(50) = NULL
,@tranId VARCHAR(50) = NULL
,@ApproveTxnRequried CHAR(1) = NULL
,@partnerPin VARCHAR(100) = NULL
,@PartnerTranId VARCHAR(100) = NULL
)AS
BEGIN
DECLARE @EncryptedControlNo VARCHAR(100)
SET @EncryptedControlNo = dbo.FNAEncryptString(@controlNo)
DECLARE @temptable TABLE ( [ControlNo] varchar(500), [id] bigint, [branch] varchar(500), [country] varchar(500), [senderId] Bigint, [sender] varchar(500), [receiverId] bigint, [receiver] varchar(1000), [amt] Money, [txnDate] datetime, [tranType] varchar(10), [txncreatedBy] varchar(500), [isThirdPartyTran] Bigint, [pAgentName] varchar(500), [Partner] Bigint )
IF @flag = 'list'
BEGIN
INSERT INTO @temptable(
ControlNo,id,branch,country,senderId,sender,receiverId,receiver,amt,txnDate,tranType,txncreatedBy,isThirdPartyTran,pAgentName,Partner
)
SELECT DISTINCT
ControlNo = dbo.fnaDecryptstring(trn.controlNo)
,trn.id
,branch = am.agentName
,country = trn.pCountry
,senderId = sen.customerId
,sender = sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName1, '') + ISNULL( ' ' + sen.lastName2, '')
,receiverId = rec.customerId
,receiver = rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName1, '') + ISNULL( ' ' + rec.lastName2, '')
,amt = CAST(trn.cAmt AS DECIMAL(18, 2))
,txnDate = CAST(trn.createdDate AS DATE)
,tranType = CASE WHEN sRouteId='w' THEN 'WALLET' WHEN sRouteId='a' THEN 'AUTO-DEBIT' ELSE '-' END
,txncreatedBy = trn.createdBy
,CASE WHEN trn.pAgent=1100 OR trn.pAgent = 1043 THEN 1 ELSE 0 END isThirdPartyTran
,trn.pSuperAgentName AS pAgentName
,trn.pSuperAgent AS [Partner]
FROM remitTranTemp trn WITH(NOLOCK)
INNER JOIN agentMaster am WITH(NOLOCK) ON trn.sBranch = am.agentId
INNER JOIN tranSendersTemp sen WITH(NOLOCK) ON trn.id = sen.tranId
INNER JOIN tranReceiversTemp rec WITH(NOLOCK) ON trn.id = rec.tranId
WHERE trn.tranStatus IN ('Hold','Compliance Hold','OFAC Hold','OFAC/Compliance Hold') AND
trn.payStatus = 'Unpaid' AND
trn.approvedBy IS NULL
UNION ALL
SELECT DISTINCT
ControlNo = dbo.fnaDecryptstring(trn.controlNo)
,trn.id
,branch = am.agentName
,country = trn.pCountry
,senderId = sen.customerId
,sender = sen.firstName + ISNULL( ' ' + sen.middleName, '') + ISNULL( ' ' + sen.lastName1, '') + ISNULL( ' ' + sen.lastName2, '')
,receiverId = rec.customerId
,receiver = rec.firstName + ISNULL( ' ' + rec.middleName, '') + ISNULL( ' ' + rec.lastName1, '') + ISNULL( ' ' + rec.lastName2, '')
,amt = CAST(trn.cAmt AS DECIMAL(18, 2))
,txnDate = CAST(trn.createdDate AS DATE)
,tranType = CASE WHEN sRouteId='w' THEN 'WALLET' WHEN sRouteId='a' THEN 'AUTO-DEBIT' ELSE '-' END
,txncreatedBy = trn.createdBy
,CASE WHEN trn.pAgent=1100 OR trn.pAgent = 1043 THEN 1 ELSE 0 END isThirdPartyTran
,trn.pAgentName
,trn.pSuperAgent AS [Partner]
FROM remitTran trn WITH(NOLOCK)
INNER JOIN agentMaster am WITH(NOLOCK) ON trn.sBranch = am.agentId
INNER JOIN tranSenders sen WITH(NOLOCK) ON trn.id = sen.tranId
INNER JOIN tranReceivers rec WITH(NOLOCK) ON trn.id = rec.tranId
WHERE trn.tranStatus IN ('Hold','Compliance Hold','OFAC Hold','OFAC/Compliance Hold') AND
trn.payStatus = 'Unpaid' AND
trn.approvedBy IS NOT NULL
--601360,392226,
SELECT * FROM @temptable WHERE country = ISNULL(@Country,country) AND [Partner] = ISNULL(@Agent,[Partner]) AND [Partner] IN (393227,590853)
END
IF @flag ='check_reprocess_req_type'
BEGIN
--DECLARE @PartnerTranId VARCHAR(100), @PartnerPin VARCHAR(100)
IF @Agent = '601360' --Exim
BEGIN
DECLARE @EximResponse NVARCHAR(MAX) = NULL, @EximResponseXml XML = NULL
SELECT TOP(1) @EximResponse = responseXml FROM Application_log.dbo.vwTpApiLogs(NOLOCK) WHERE controlNo = @controlNo AND providerName = 'eximBank' ORDER BY rowId DESC
SET @EximResponse = REPLACE(@EximResponse,'<?xml version="1.0" encoding="UTF-8"?>','')
SET @EximResponseXml = @EximResponse
--No sendTxn, only account valadition method called
IF @EximResponse IS NULL
BEGIN
SELECT @Agent AS Agent,'local' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
--Send Method is called and some error has occured.
IF @EximResponse IS NOT NULL AND @EximResponseXml IS NOT NULL
BEGIN
DECLARE @eximerrTbl TABLE(ErrorCode VARCHAR(100), Msg NVARCHAR(300))
INSERT INTO @eximerrTbl(ErrorCode,Msg)
SELECT MAX(CASE WHEN sn=1 THEN xx.Error ELSE '' END) ErrorCode ,MAX(CASE WHEN sn= 2 THEN xx.Error ELSE '' END ) Msg FROM (
SELECT ROW_NUMBER() OVER (ORDER BY x.error ASC) sn, Error FROM(
SELECT
Error = T.c.value('(.)[1]', 'varchar(MAX)')
FROM @EximResponseXml.nodes('/ResponseMessage/Error/*') T(c)
)x
)xx
IF EXISTS(SELECT 'x' FROM @eximerrTbl WHERE ErrorCode NOT IN ('00','0'))
BEGIN
SELECT @Agent AS Agent,'local' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
END
--txn already post success at exim bank
SELECT @Agent AS Agent,'none' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
IF @Agent = '392226' --bni
BEGIN
DECLARE @bniResponse NVARCHAR(MAX) = NULL, @bniResponseXml XML = NULL
SELECT TOP(1) @bniResponse = responseXml FROM Application_log.dbo.vwTpApiLogs(NOLOCK)
WHERE controlNo = @controlNo AND providerName = 'BNI' AND methodName = 'processPO' ORDER BY rowId DESC
SET @bniResponse = REPLACE(@bniResponse,'<?xml version="1.0" encoding="UTF-8"?>','')
SET @bniResponseXml = @bniResponse
--No sendTxn api called
IF @bniResponse IS NULL
BEGIN
SELECT @Agent AS Agent,'local' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
--Send Method is called and some error has occured.
IF @bniResponse IS NOT NULL AND @bniResponseXml IS NOT NULL
BEGIN
DECLARE @bnierrTbl TABLE(ErrorCode VARCHAR(100), Msg NVARCHAR(300))
INSERT INTO @bnierrTbl(ErrorCode,Msg)
SELECT MAX(CASE WHEN sn=1 THEN xx.Error ELSE '' END) ErrorCode ,MAX(CASE WHEN sn= 2 THEN xx.Error ELSE '' END ) Msg FROM (
SELECT ROW_NUMBER() OVER (ORDER BY x.error ASC) sn, Error FROM(
SELECT
Error = T.c.value('(.)[1]', 'varchar(MAX)')
FROM @bniResponseXml.nodes('/DbResponse/*') T(c)
)x WHERE x.Error <> ''
)xx
IF EXISTS(SELECT 'x' FROM @bnierrTbl)
BEGIN
SELECT @Agent AS Agent,'local' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
END
SELECT @Agent AS Agent,'none' AS ReprocessType,NULL AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'N' AS ApproveTxnRequried
RETURN
END
IF @Agent = '393227' --contact
BEGIN
DECLARE @newOutGoingResponse NVARCHAR(MAX), @payOutGoingResponse NVARCHAR(MAX)
DECLARE @newOutGoingResponseXml XML, @payOutGoingResponseXml XML
SELECT
TOP (1) @newOutGoingResponse = responseXml
FROM Application_Log.dbo.vwTpApilogs(NOLOCK)
WHERE controlNo = @controlNo AND methodName = 'NewOutgoing' ORDER BY rowId DESC
SET @newOutGoingResponseXml = @newOutGoingResponse
SELECT
TOP(1) @payOutGoingResponse = responseXml
FROM Application_Log.dbo.vwTpApilogs(NOLOCK)
WHERE controlNo = @controlNo AND methodName = 'PayOutgoing' ORDER BY rowId DESC
SET @payOutGoingResponseXml = @payOutGoingResponse
DECLARE @NewOutGoingErr VARCHAR(MAX) = NULL, @NewGoingErrCode VARCHAR(200) = NULL
SELECT @PartnerPin = DocId, @PartnerTranId = DocId, @NewOutGoingErr= x.Error, @NewGoingErrCode = x.ErrorCode FROM (
SELECT
T.c.value('@ERR_TEXT', 'varchar(MAX)') AS Error
,T.c.value('@RE', 'varchar(MAX)') AS ErrorCode
,T.c.value('@ID', 'varchar(MAX)') AS DocId
,T.c.value('@trnReference', 'varchar(MAX)') AS ControlNo
FROM @newOutGoingResponseXml.nodes('/RESPONSE') T(c)
)x --WHERE x.ControlNo = @controlNo
--Remote call for reprocess when payoutgoing is called
PRINT 'Remote call for reprocess when payoutgoing is called'
IF @payOutGoingResponse IS NOT NULL AND @newOutGoingResponse IS NOT NULL
BEGIN
DECLARE @PayOutGoingErr VARCHAR(MAX) = NULL, @PayoutGoingErrCode VARCHAR(200) = NULL
--Get payoutgoing error
SELECT
@PayOutGoingErr = T.c.value('@ERR_TEXT', 'varchar(MAX)')
,@PayoutGoingErrCode= T.c.value('@RE', 'varchar(MAX)')
FROM @payOutGoingResponseXml.nodes('/RESPONSE') T(c)
--If Error is null ie. No valid xml in PayoutGoing response
PRINT 'If Error is null ie. No valid xml in PayoutGoing response'
IF @PayOutGoingErr IS NULL AND @PayoutGoingErrCode IS NULL
BEGIN
--Can reporcess the txn
PRINT 'Can reporcess the txn'
IF @PartnerPin IS NOT NULL
BEGIN
SELECT
@Agent AS Agent
,'remote' AS ReprocessType
,'reprocess' AS NextMethod
,@PartnerPin AS PartnerPin
,@PartnerTranId AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
, @EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried
,'http://contact.gmeremit.com.kr:7006/Contact' AS BaseUrl, '0D9FB8DC-DCA6-44D5-A1C0-01F4531936B2' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo --AND id = @TranId
RETURN
END
END
--Ther is error and valid xml response in payOutGoing response
PRINT 'Ther is error and valid xml response in payOutGoing response'
IF @PayOutGoingErr IS NOT NULL AND (@PayoutGoingErrCode IS NOT NULL AND @PayoutGoingErrCode <> '0')
BEGIN
IF @PartnerPin IS NOT NULL
BEGIN
SELECT
@Agent AS Agent
,'remote' AS ReprocessType
,'reprocess' AS NextMethod
,@PartnerPin AS PartnerPin
,@PartnerTranId AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
, @EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried
,'http://contact.gmeremit.com.kr:7006/Contact' AS BaseUrl, '0D9FB8DC-DCA6-44D5-A1C0-01F4531936B2' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo --AND id = @TranId
RETURN
END
END
END
--Remote call for reprocess when newOutGoing only is called
PRINT 'Remote call for reprocess when newOutGoing only is called'
IF @payOutGoingResponse IS NULL AND @newOutGoingResponse IS NOT NULL
BEGIN
--DocId is available go reprocess the txn
PRINT 'DocId is available go reprocess the txn'
IF @PartnerPin IS NOT NULL
BEGIN
SELECT
@Agent AS Agent
,'remote' AS ReprocessType
,'reprocess' AS NextMethod
,@PartnerPin AS PartnerPin
,@PartnerTranId AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
,@EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried
,'http://contact.gmeremit.com.kr:7006/Contact' AS BaseUrl, '0D9FB8DC-DCA6-44D5-A1C0-01F4531936B2' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo --AND id = @TranId
RETURN
END
PRINT @NewGoingErrCode
PRINT @TranId
PRINT @EncryptedControlNo
IF @PartnerPin IS NULL AND @NewGoingErrCode IN ('-6121')
BEGIN
SELECT
@Agent AS Agent
,'local' AS ReprocessType
,'cancelLocal' AS NextMethod
,'' AS PartnerPin
,'' AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
,@EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried
,'' AS BaseUrl, '' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo --AND id = @TranId
RETURN
END
---Only new out going has been called with error on response
IF @PartnerPin IS NULL
BEGIN
PRINT 'Only new out going has been called with error on response'
SELECT
@Agent AS Agent
,'local' AS ReprocessType
,'sendTransaction' AS NextMethod
,'' AS PartnerPin
,'' AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
,@EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried,'' AS BaseUrl, '' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo --AND id = @TranId
RETURN
END
END
IF @newOutGoingResponse IS NULL AND @payOutGoingResponse IS NULL
BEGIN
SELECT
@Agent AS Agent
,'local' AS ReprocessType
,'SendTransaction' AS NextMethod
,@PartnerPin AS PartnerPin
,@PartnerTranId AS PartnerTranId
,id AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
,@EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried ,'' AS BaseUrl, '' AS [Authorization],@controlNo AS ControlNo
FROM dbo.remitTran(NOLOCK) WHERE controlNo = @EncryptedControlNo
RETURN
END
SELECT
@Agent AS Agent
,'none' AS ReprocessType
,'sendTransaction' AS NextMethod
,'' AS PartnerPin
,'' AS PartnerTranId
,'' AS TranId
,@Agent AS payOutPartnerId
,'Contact' AS [Partner]
,@EncryptedControlNo AS EncryptControlNo
, 'N' AS ApproveTxnRequried ,'' AS BaseUrl, '' AS [Authorization],@controlNo AS ControlNo
RETURN
END
IF @Agent = '590853' --lyhour
BEGIN
DECLARE @LyhourResponse NVARCHAR(MAX) = NULL, @LyhourResponseJson NVARCHAR(MAX) = NULL
SELECT TOP(1) @LyhourResponse = responseXml FROM Application_log.dbo.vwTpApiLogs(NOLOCK) WHERE controlNo = @controlNo AND providerName = 'Lyhour' ORDER BY rowId DESC
IF @LyhourResponse IS NOT NULL AND @LyhourResponse = 'Null response from Api during placing send money'
BEGIN
SELECT @Agent AS Agent,'remote' AS ReprocessType,'SendTransaction' AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'Y' AS ApproveTxnRequried, 'http://lyhour.gmeremit.com.kr:3443/lyhour' AS BaseUrl, '122F15BE-030D-4FA2-98B9-E9FCB581AAA6' AS [Authorization]
RETURN
END
SET @LyhourResponseJson = @LyhourResponse
-- PRINT (@LyhourResponse)
--No sendTxn, process to send to partner via api call
IF @LyhourResponse IS NULL
BEGIN
SELECT @Agent AS Agent,'remote' AS ReprocessType,'SendTransaction' AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'Y' AS ApproveTxnRequried, 'http://lyhour.gmeremit.com.kr:3443/lyhour' AS BaseUrl, '122F15BE-030D-4FA2-98B9-E9FCB581AAA6' AS [Authorization]
RETURN
END
IF @LyhourResponse IS NOT NULL
BEGIN
DECLARE @lyErrTbl TABLE(httpStatusCode VARCHAR(100),source VARCHAR(100), [message] VARCHAR(200), innerException VARCHAR(200))
INSERT INTO @lyErrTbl(httpStatusCode,source, [message], innerException)
SELECT
JSON_VALUE(@LyhourResponseJson,'$.httpStatusCode') AS httpStatusCode
,JSON_VALUE(@LyhourResponseJson,'$.source') AS source
,JSON_VALUE(@LyhourResponseJson,'$.message[0]') AS [message]
,JSON_VALUE(@LyhourResponseJson,'$.innerException[0]') AS innerException
IF EXISTS(SELECT 'x' FROM @lyErrTbl WHERE httpStatusCode IS NOT NULL)
BEGIN
SELECT @Agent AS Agent,'remote' AS ReprocessType,'SendTransaction' AS NextMethod, '' AS PartnerPin, '' AS PartnerTranId, '' AS TranId, @Agent AS payOutPartnerId, @EncryptedControlNo AS EncryptControlNo, 'Y' AS ApproveTxnRequried, 'http://lyhour.gmeremit.com.kr:3443/lyhour' AS BaseUrl, '122F15BE-030D-4FA2-98B9-E9FCB581AAA6' AS [Authorization]
RETURN
END
END
END
END
IF @flag = 'reprocess-local'
BEGIN
BEGIN TRAN
UPDATE Application_Log.dbo.ApplicationLogger SET controlNo = controlNo + 'A' WHERE controlNo = @controlNo
UPDATE dbo.remitTran SET tranStatus = 'Payment' WHERE controlNo = @EncryptedControlNo
COMMIT TRAN
IF @@TRANCOUNT = 0
BEGIN
SELECT '0' ErrorCode, 'Reprocessed completed. Please monitor the transaction.' Msg, NULL Id
RETURN
END
SELECT '1' ErrorCode, 'Reprocessed failed.' Msg, NULL Id
RETURN
END
IF @flag = 'reprocess-remote'
BEGIN
IF @ApproveTxnRequried IS NOT NULL AND @ApproveTxnRequried = 'Y'
BEGIN
BEGIN TRAN
DECLARE @holdTranId BIGINT
SELECT @holdTranId = id FROM remitTranTemp(nolock) WHERE controlNo = @EncryptedControlNo
--Put approve logic here
exec [proc_ApproveHoldedTXN] @flag='approve',@user='admin',@id = @holdTranId
UPDATE dbo.remitTran SET
tranStatus = 'Payment',
payStatus='Post',
controlNo2 = dbo.FNAEncryptString(@controlNo),
controlNo = dbo.FNAEncryptString(@partnerPin),
ContNo = @PartnerTranId
WHERE holdTranId = @holdTranId
COMMIT TRAN
IF @@TRANCOUNT = 0
BEGIN
SELECT '0' ErrorCode, 'Reprocessed completed. Please monitor the transaction.' Msg, NULL Id
RETURN
END
END
ELSE
BEGIN
BEGIN TRAN
UPDATE dbo.remitTran SET
tranStatus = 'Payment',
payStatus='Post',
postedBy=@User,
postedDate= GETDATE(),
postedDateLocal=GETDATE(),
ContNo = ISNULL(@PartnerTranId,'')
WHERE controlNo = @EncryptedControlNo
COMMIT TRAN
IF @@TRANCOUNT = 0
BEGIN
SELECT '0' ErrorCode, 'Reprocessed completed. Please monitor the transaction.' Msg, NULL Id
RETURN
END
END
END
IF @flag = 'country'
BEGIN
SELECT countryId,countryName,* FROM dbo.countryMaster(NOLOCK) WHERE isOperativeCountry= 'Y' AND ISNULL(isActive,'Y') = 'Y'
END
IF @flag = 'partner'
BEGIN
SELECT agentId,agentName FROM dbo.agentMaster(NOLOCK) WHERE parentId= '0' AND isActive = 'Y' AND agentId NOT IN (1001,1008,1009)
END
END
GO