USE [SAIB_Remttance]
GO
/****** Object:  StoredProcedure [dbo].[sp_View_StopPayment]    Script Date: 9/8/2026 04:29:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_View_StopPayment]       
 -- Add the parameters for the stored procedure here      
-- @Agent_ID int = 0,      
 @TopRecord int = -1,      
 @SRTRNo nvarchar(50) = null,      
 @Agent_ID int = 0,      
 @PayAgent int = 0,      
 @UserId nvarchar(50) = null,       
 @Agent_Branch_ID int = 0,      
 @TrStatusID int = 0,      
 @Customer_Name_F nvarchar(50) = null,      
 @Customer_Name_L nvarchar(50) = null,      
 @Beneficiary_Name_F nvarchar(50) = null,      
 @Beneficiary_Name_L nvarchar(50) = null,      
 @TrTypeID int = 0,      
 @CountryID int = 0,      
 @PayCountryID int = 0,      
 @LoginCountryID int = 0,      
 @LoginCityID int = 0,      
 @LoginLocationID int = 0,      
 @ListTrStatusID nvarchar(500) = null,      
 @DATETIME nvarchar(500) = null,      
 @UserGroupType int = 0,      
 @SessionAgent_ID int = 0, -- Following Parameter Not in use but added for csDatabase Class      
 @PayInCurrencyID int = 0,      
 @PayOutCurrencyID int = 0,      
 @CheckOutput int = 0, 
 -- Following Parameter Not in use but added for csDatabase Class      
 @ProcedureType int = 0, -- 0 for Select, 1 for update, 2 for Insert and 3 for Delete      
 @ViewType int = 0,  -- 0 for View, 1 for Process      
 @Filter nvarchar(max) = null,      
 @Script nvarchar(max) = null output      
 -- End      
AS      
BEGIN      
 -- SET NOCOUNT ON added to prevent extra result sets from      
 -- interfering with SELECT statements.      
 SET NOCOUNT ON;      
  
 if @Agent_ID = -1  
  begin  
   SET @Agent_ID = 0  
  end      
        
 DECLARE @TOPVALUE nvarchar(100)      
 If @TopRecord = -1      
  begin      
   SET @TOPVALUE = ' TOP (100) PERCENT'      
  end      
 Else      
  begin      
   SET @TOPVALUE = ' TOP (' + CAST(@TopRecord as nvarchar(10)) + ')'      
  end      

 SET @Filter = null      
 If @SRTRNo is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND (''' + @SRTRNo + ''' IN (ThirdPartyCode, Random_No))',' (''' + @SRTRNo + ''' IN (ThirdPartyCode, Random_No))')      
  End      
 If @PayAgent <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayAgent = ' + CAST(@PayAgent as nvarchar),' PayAgent = ' + CAST(@PayAgent as nvarchar))      
  End      
 If @UserId is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND UserId = N''' + @UserId + '''',' UserId = N''' + @UserId + '''')      
  End      
-- If @TrStatusID <> 0      
--  Begin      
--   SET @Filter = IsNull(@Filter + ' AND tbl_05_01_TrMaster.TrStatusID = ' + CAST(@TrStatusID as nvarchar),' tbl_05_01_TrMaster.TrStatusID = ' + CAST(@TrStatusID as nvarchar))      
--  End      
 If @TrStatusID <> 0  
  Begin  
   SET @Filter = IsNull(@Filter + ' AND tbl_04_06_TrStatus_Details.InternalStatusID = ' + CAST(@TrStatusID as nvarchar),' tbl_04_06_TrStatus_Details.InternalStatusID = ' + CAST(@TrStatusID as nvarchar))  
  End  
 else
  Begin  
		If @SRTRNo is not null 
		Begin
			SET @Filter = IsNull(@Filter + ' AND tbl_04_06_TrStatus_Details.InternalStatusID IN(1,2,3,4,5,7,8,13,29,37,38) ',' tbl_04_06_TrStatus_Details.InternalStatusID IN(1,2,3,4,5,7,8,13,29,37,38)')  
		End
		Else
		Begin
			SET @Filter = IsNull(@Filter + ' AND tbl_04_06_TrStatus_Details.InternalStatusID IN(1,2,3,4,5,7,8,13) ',' tbl_04_06_TrStatus_Details.InternalStatusID IN(1,2,3,4,5,7,8,13)')  
		End
  End  
 If @TrTypeID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND TrTypeID = ' + CAST(@TrTypeID as nvarchar),' TrTypeID = ' + CAST(@TrTypeID as nvarchar))      
  End      
 If @PayCountryID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayCountryID = ' + CAST(@PayCountryID as nvarchar),' PayCountryID = ' + CAST(@PayCountryID as nvarchar))      
  End      
 If @ListTrStatusID  is not null       
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_05_01_TrMaster.' + @ListTrStatusID,' tbl_05_01_TrMaster.' + @ListTrStatusID)      
  End      
 If @PayInCurrencyID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayINCurrency = ' + CAST(@PayInCurrencyID as nvarchar),' PayINCurrency = ' + CAST(@PayInCurrencyID as nvarchar))      
  End      
 If @PayOutCurrencyID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayOutCurrency = ' + CAST(@PayOutCurrencyID as nvarchar),' PayOutCurrency = ' + CAST(@PayOutCurrencyID as nvarchar))      
  End  
 SET @Filter = ' WHERE ' + @Filter      

	CREATE TABLE #TrMaster
	(
		[SRTRNo] [nvarchar](20),  
		[Ex_Branch_ID] [int] NOT NULL,
		[App_ID] [nvarchar](50) NOT NULL,
		[Random_No] [nvarchar](20) NULL,
		[TrTypeID] [int] NULL,
		[TrStatusID] [int] NULL,
		[Customer_Id] [int] NULL,
		[Benef_Id] [int] NULL,
		[UserId] [nvarchar](50) NULL,
		[Suspected] [bit] NULL,
		[SuspectedReason] [nvarchar] (250) NULL,
		[PayCountryID] [int] NULL,
		[PayCityID] [int] NULL,
		[PayLocationID] [int] NULL, 
		[PayAgent] [int] NULL,
		[Reference_No] [nvarchar](50) NULL,
		[Remark] [nvarchar] (MAX),
		[ThirdPartyCode] [nvarchar] (20),
		[PayINCurrency] [int] NULL,
		[PayOutCurrency] [int] NULL
	)

	SET @Script = 'INSERT INTO #TrMaster  
	SELECT
	IsNull(tbl_05_01_TrMaster.ThirdPartyCode,tbl_05_01_TrMaster.Random_No) AS SRTRNo,   
	tbl_05_01_TrMaster.Ex_Branch_ID,    
	tbl_05_01_TrMaster.App_ID,   
	tbl_05_01_TrMaster.Random_No,   
	tbl_05_01_TrMaster.TrTypeID,   
	tbl_05_01_TrMaster.TrStatusID,   
	tbl_05_01_TrMaster.Customer_Id,   
	tbl_05_01_TrMaster.Benef_Id,   
	tbl_05_01_TrMaster.UserId,   
	tbl_05_01_TrMaster.Suspected,
	tbl_05_01_TrMaster.SuspectedReason,
	tbl_05_01_TrMaster.PayCountryID,   
	tbl_05_01_TrMaster.PayCityID,   
	tbl_05_01_TrMaster.PayLocationID,   
	tbl_05_01_TrMaster.PayAgent,   
	tbl_05_01_TrMaster.Reference_No,  
	tbl_05_01_TrMaster.Remark,   
	tbl_05_01_TrMaster.ThirdPartyCode, 
	tbl_05_01_TrMaster.PayINCurrency,
	tbl_05_01_TrMaster.PayOutCurrency 
	FROM tbl_05_01_TrMaster(nolock) 
	INNER JOIN tbl_04_06_TrStatus_Details(nolock) ON tbl_05_01_TrMaster.TrStatusID = tbl_04_06_TrStatus_Details.TrStatusID   
 ' + IsNull(@Filter + ' AND (dbo.tbl_05_01_TrMaster.TrTypeID IN (1, 2, 3,16)) ORDER BY tbl_05_01_TrMaster.DtEntered DESC',      
 ' WHERE (dbo.tbl_05_01_TrMaster.TrTypeID IN (1, 2, 3,16)) ORDER BY tbl_05_01_TrMaster.DtEntered DESC')      

 Exec(@Script)  
  
if @CheckOutput = 1
	begin
		PRINT   @Script
		SELECT '#TrMaster' as dd, * FROM #TrMaster
	end

 SET @Filter = null      
 If @DATETIME  is not null       
  Begin      
   --SET @Filter = IsNull(@Filter + ' AND ' + @DATETIME,' ' + @DATETIME)  
    SET @Filter = IsNull(@Filter + ' AND ' + REPLACE(@DATETIME, 'TrDate', 'tbl_05_01_TrMaster_Temp.TrDate'),
                        ' ' + REPLACE(@DATETIME, 'TrDate', 'tbl_05_01_TrMaster_Temp.TrDate'))      
  End      
 SET @Filter = ' WHERE ' + @Filter      

	CREATE TABLE #Master
	(
		[SRTRNo] [nvarchar](20),  
		[Ex_Branch_ID] [int] NOT NULL,
		[App_ID] [nvarchar](50) NOT NULL,
		[Random_No] [nvarchar](20) NULL,
		[TrTypeID] [int] NULL,
		[TrStatusID] [int] NULL,
		[Customer_Id] [int] NULL,
		[Benef_Id] [int] NULL,
		[UserId] [nvarchar](50) NULL,
		[Suspected] [bit] NULL,
		[SuspectedReason] [nvarchar] (250) NULL,
		[PayCountryID] [int] NULL,
		[PayCityID] [int] NULL,
		[PayLocationID] [int] NULL, 
		[PayAgent] [int] NULL,
		[Reference_No] [nvarchar](50) NULL,
		[TrDate] [datetime] NULL,
		[Tr_Sequance] [int] NULL,
		[AgentLC_Amount] [decimal] (18, 4),  
		[AgentFC_Amount] [decimal] (18, 4),
		[Tr_Display] [nvarchar](50),
		[Remark] [nvarchar] (MAX),
		[ThirdPartyCode] [nvarchar] (20),
		[PayINCurrency] [int] NULL,
		[PayOutCurrency] [int] NULL
	)

	SET @Script = 'INSERT INTO #Master  
	SELECT
	IsNull(#TrMaster.ThirdPartyCode,#TrMaster.Random_No) AS SRTRNo,   
	#TrMaster.Ex_Branch_ID,    
	#TrMaster.App_ID,   
	#TrMaster.Random_No,   
	#TrMaster.TrTypeID,   
	#TrMaster.TrStatusID,   
	#TrMaster.Customer_Id,   
	#TrMaster.Benef_Id,   
	#TrMaster.UserId,   
	#TrMaster.Suspected,
	#TrMaster.SuspectedReason,
	#TrMaster.PayCountryID,   
	#TrMaster.PayCityID,   
	#TrMaster.PayLocationID,   
	#TrMaster.PayAgent,   
	#TrMaster.Reference_No,
	tbl_05_01_TrMaster.DtEntered,  
    -- Comment By Manoj Thakur   
	--tbl_05_02_TrDetails.Tr_Sequance,   
	--tbl_05_02_TrDetails.AgentLC_Amount,  
	--tbl_05_02_TrDetails.AgentFC_Amount,  
	--tbl_05_02_TrDetails.Tr_Display,  
	''1'',
	tbl_05_01_TrMaster_Temp.AgentLC_Amount,
	tbl_05_01_TrMaster_Temp.AgentFC_Amount,
	tbl_05_01_TrMaster.App_ID,
	#TrMaster.Remark,   
	#TrMaster.ThirdPartyCode, 
	#TrMaster.PayINCurrency,
	#TrMaster.PayOutCurrency 
	FROM #TrMaster INNER JOIN  
	tbl_05_01_TrMaster(nolock) ON #TrMaster.Random_No = tbl_05_01_TrMaster.Random_No
	INNER JOIN 
	tbl_05_01_TrMaster_Temp(nolock) ON tbl_05_01_TrMaster.Random_No = tbl_05_01_TrMaster_Temp.TransactionNumber 
	 -- Comment By Manoj Thakur
	--tbl_05_02_TrDetails ON #TrMaster.Ex_Branch_ID = tbl_05_02_TrDetails.Ex_Branch_ID AND #TrMaster.App_ID = tbl_05_02_TrDetails.App_ID COLLATE SQL_Latin1_General_CP1_CI_AS
 ' + IsNull(@Filter + '','ORDER BY tbl_05_01_TrMaster_Temp.TrDate DESC') 
 --AND (tbl_05_02_TrDetails.Tr_Sequance = 1) ORDER BY TrDate DESC',      
 --' WHERE (tbl_05_02_TrDetails.Tr_Sequance = 1) ORDER BY TrDate DESC')      

 Exec(@Script)  
  
if @CheckOutput = 1
	begin
		PRINT   @Script
		SELECT * FROM #Master
	end
  
    -- Insert statements for procedure here      
 SET @Filter = null      
 If @PayAgent <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayAgent = ' + CAST(@PayAgent as nvarchar),' PayAgent = ' + CAST(@PayAgent as nvarchar))      
  End      
 If @UserId is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND UserId = N''' + @UserId + '''',' UserId = N''' + @UserId + '''')      
  End      
-- If @TrStatusID <> 0      
--  Begin      
--   SET @Filter = IsNull(@Filter + ' AND #Master.TrStatusID = ' + CAST(@TrStatusID as nvarchar),' #Master.TrStatusID = ' + CAST(@TrStatusID as nvarchar))      
--  End      
 If @TrTypeID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND TrTypeID = ' + CAST(@TrTypeID as nvarchar),' TrTypeID = ' + CAST(@TrTypeID as nvarchar))      
  End      
 If @PayCountryID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayCountryID = ' + CAST(@PayCountryID as nvarchar),' PayCountryID = ' + CAST(@PayCountryID as nvarchar))      
  End      
 If @ListTrStatusID  is not null       
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_05_01_TrMaster.' + @ListTrStatusID,' tbl_05_01_TrMaster.' + @ListTrStatusID)      
  End      
 If @PayInCurrencyID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayINCurrency = ' + CAST(@PayInCurrencyID as nvarchar),' PayINCurrency = ' + CAST(@PayInCurrencyID as nvarchar))      
  End      
 If @PayOutCurrencyID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND PayOutCurrency = ' + CAST(@PayOutCurrencyID as nvarchar),' PayOutCurrency = ' + CAST(@PayOutCurrencyID as nvarchar))      
  End      
      
 SET @Filter = ' WHERE ' + @Filter      
      
 CREATE TABLE #tbl_05_01_TrMaster      
 (      
  [SRTRNo] [nvarchar](20),      
  [Ex_Branch_ID] [int] NOT NULL,      
  [App_ID] [nvarchar](50) NOT NULL,      
  [Tr_Display] [nvarchar] (50) NULL,      
  [TrTypeID] [int] NULL,      
  [TrStatusID] [int] NULL,      
  [Customer_Id] [int] NULL,      
  [Benef_Id] [int] NULL,      
  [UserId] [nvarchar](50) NULL,      
  [PayCountryID] [int] NULL,      
  [LoginCountryID] [int] NULL,      
  [LoginCityID] [int] NULL,      
  [LoginLocationID] [int] NULL,      
  [PayAgent] [int] NULL,      
  [Reference_No] [nvarchar](50) NULL,      
  [Tr_Status_Name] [nvarchar](50),       
  [Date] [nvarchar](20),      
  [TrDate] [datetime],      
  [Tr_Sequance] [int],      
  [Remark] [nvarchar](MAX),     
  [RptType] [nvarchar] (100),      
  [Show] [nvarchar] (15),      
  [SRTRNo_1] [nvarchar](20),      
  [ThirdPartyCode] [nvarchar](20)      
 )      
      
 SET @Script = 'INSERT INTO #tbl_05_01_TrMaster      
 SELECT       
 IsNull(#Master.ThirdPartyCode,#Master.Random_No) AS SRTRNo,       
 #Master.Ex_Branch_ID,        
 #Master.App_ID,       
 #Master.Tr_Display,      
 #Master.TrTypeID,       
 #Master.TrStatusID,       
 #Master.Customer_Id,       
 #Master.Benef_Id,       
 #Master.UserId,       
 #Master.PayCountryID,      
 #Master.PayCountryID as LoginCountryID,       
 #Master.PayCityID as LoginCityID,      
 #Master.PayLocationID as LoginLocationID,      
 #Master.PayAgent,       
 #Master.Reference_No,       
 tbl_04_06_TrStatus_Details.Tr_Status_Name,      
 CONVERT (nvarchar, #Master.TrDate, 101) as Date,       
 #Master.TrDate,       
 #Master.Tr_Sequance,       
 CASE       
  WHEN #Master.Suspected = 1 AND #Master.TrStatusID = 8       
  THEN #Master.SuspectedReason       
 ELSE #Master.Remark       
 END as Remark,      
 CASE       
  WHEN ' + CAST(@UserGroupType as nvarchar(1)) + ' = 8       
  THEN ''Remmitance Outwards''       
 ELSE       
  CASE       
   WHEN ' + CAST(@UserGroupType as nvarchar(1)) + ' = 9       
   THEN ''Remmitance Inwards''       
   END      
 END as RptType,      
 CASE       
  WHEN ' + CAST(@UserGroupType as nvarchar(1)) + ' = 9 AND #Master.TrStatusID = 3       
  THEN ''visible''       
 ELSE       
  CASE       
   WHEN ' + CAST(@UserGroupType as nvarchar(1)) + ' = 8       
   THEN ''visible''       
  ELSE       
   ''hidden''        
  END        
 END as Show,      
 #Master.Random_No AS SRTRNo_1,       
 #Master.ThirdPartyCode      
 FROM #Master INNER JOIN       
 tbl_04_06_TrStatus_Details(nolock) ON #Master.TrStatusID = tbl_04_06_TrStatus_Details.TrStatusID
	INNER JOIN  tbl_Agent_Details(nolock) ON #Master.PayAgent = tbl_Agent_Details.Agent_ID       
 ' + IsNull(@Filter + ' ORDER BY #Master.TrDate DESC',' ORDER BY #Master.TrDate DESC')      
            
 Exec(@Script)      
      
if @CheckOutput = 1
	begin
		PRINT   @Script
		SELECT 'tbl_05_01_TrMaster' as dd, * FROM #tbl_05_01_TrMaster      
	end
      
 SET @Filter = null      
 If @UserId is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_01_01_UserName.UserId = N''' + @UserId + '''',' tbl_01_01_UserName.UserId = N''' + @UserId + '''')      
  End      
 If @Agent_ID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_Agent_Branch_Details.Agent_ID = ' + CAST(@Agent_ID as nvarchar),' tbl_Agent_Branch_Details.Agent_ID = ' + CAST(@Agent_ID as nvarchar))      
  End      
 If @Agent_Branch_ID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_01_01_UserName.Agent_Branch_ID = ' + CAST(@Agent_Branch_ID as nvarchar),' tbl_01_01_UserName.Agent_Branch_ID = ' + CAST(@Agent_Branch_ID as nvarchar))      
  End      
 If @CountryID <> 0      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND tbl_Agent_Branch_Details.CountryID = ' + CAST(@CountryID as nvarchar),' tbl_Agent_Branch_Details.CountryID = ' + CAST(@CountryID as nvarchar))      
  End      
      
 SET @Filter = ' WHERE ' + @Filter      
      
 CREATE TABLE #tbl_Agent_Branch_Details      
 (      
  [UserId] [nvarchar](50) NULL,      
  [Agent_Branch_ID] [int] NULL,      
  [Agent_ID] [int] NULL,      
  [CountryID] [int] NULL      
 )      
      
 SET @Script = 'INSERT INTO #tbl_Agent_Branch_Details      
 SELECT tbl_01_01_UserName.UserId,       
 tbl_01_01_UserName.Agent_Branch_ID,       
 tbl_Agent_Branch_Details.Agent_ID,      
 tbl_Agent_Branch_Details.CountryID      
 FROM tbl_01_01_UserName(nolock) INNER JOIN       
 tbl_Agent_Branch_Details(nolock) ON tbl_01_01_UserName.Agent_Branch_ID = tbl_Agent_Branch_Details.AgentBranch_ID AND      
 tbl_01_01_UserName.Agent_ID = tbl_Agent_Branch_Details.Agent_ID       
 ' + IsNull(@Filter,'')      
            
 Exec(@Script)      
      
if @CheckOutput = 1
	begin
		PRINT   @Script
		SELECT * FROM #tbl_Agent_Branch_Details      
	end    
       
 SET @Filter = null      
 If @Customer_Name_F is not null       
  Begin      
   SET @Filter = IsNull(@Filter + ' AND Customer_Name_F LIKE N''%' + @Customer_Name_F + '%''',' Customer_Name_F LIKE N''%' + @Customer_Name_F + '%''')      
  End      
 If @Customer_Name_L is not null       
  Begin      
   SET @Filter = IsNull(@Filter + ' AND Customer_Name_L LIKE N''%' + @Customer_Name_L + '%''',' Customer_Name_L LIKE N''%' + @Customer_Name_L + '%''')      
  End      
 If @Beneficiary_Name_F is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND Beneficiary_Name_F LIKE N''%' + @Beneficiary_Name_F + '%''',' Beneficiary_Name_F LIKE N''%' + @Beneficiary_Name_F + '%''')      
  End      
 If @Beneficiary_Name_L is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND Beneficiary_Name_L LIKE N''%' + @Beneficiary_Name_L + '%''',' Beneficiary_Name_L LIKE N''%' + @Beneficiary_Name_L + '%''')      
  End      
       
 SET @Filter = ' WHERE ' + @Filter      
      
 CREATE TABLE #tbl_Main_Details      
 (      
  [Customer_Id] [int] NULL,      
  [Benef_Id] [int] NULL,      
  [Customer_Name_F] [nvarchar](50) NULL,      
  [Customer_Name_L] [nvarchar](50) NULL,      
  [Beneficiary_Name_F] [nvarchar](50) NULL,      
  [Beneficiary_Name_L] [nvarchar](50) NULL,      
 )      
      
  if  @Filter Is Not Null
	Begin
		SET @Script = 'INSERT INTO #tbl_Main_Details  
		SELECT tbl_04_01_Remmiter_Details.Customer_Id,   
		tbl_04_03_Benficiary_Details.Benef_Id,   
		Customer_Name_F,  
		Customer_Name_L,   
		Beneficiary_Name_F,   
		Beneficiary_Name_L  
		FROM tbl_04_01_Remmiter_Details(nolock) INNER JOIN   
		tbl_04_03_Benficiary_Details(nolock) ON tbl_04_01_Remmiter_Details.Customer_Id = tbl_04_03_Benficiary_Details.Customer_Id   
		 ' + IsNull(@Filter + '  ORDER BY tbl_04_01_Remmiter_Details.DtEntered DESC','  ORDER BY tbl_04_01_Remmiter_Details.DtEntered DESC')  
	end
else
	begin
		 SET @Script = 'INSERT INTO #tbl_Main_Details  
		 SELECT tbl_04_01_Remmiter_Details.Customer_Id,   
		 tbl_04_03_Benficiary_Details.Benef_Id,   
		 Customer_Name_F,  
		 Customer_Name_L,   
		 Beneficiary_Name_F,   
		 Beneficiary_Name_L  
		 FROM tbl_04_01_Remmiter_Details(nolock) INNER JOIN   
		 tbl_04_03_Benficiary_Details(nolock) ON tbl_04_01_Remmiter_Details.Customer_Id = tbl_04_03_Benficiary_Details.Customer_Id   
		INNER JOIN #Master ON tbl_04_01_Remmiter_Details.Customer_Id = #Master.Customer_Id AND 
                      tbl_04_03_Benficiary_Details.Benef_Id = #Master.Benef_Id
		 ' + IsNull(@Filter + '  ORDER BY tbl_04_01_Remmiter_Details.DtEntered DESC','  ORDER BY tbl_04_01_Remmiter_Details.DtEntered DESC')  
	end 
		  
	Exec(@Script)  
  
if @CheckOutput = 1
	begin
		PRINT   @Script
		SELECT '#tbl_Main_Details' as dd,  * FROM #tbl_Main_Details   
	end     
      
 SET @Filter = null;      
 If @SRTRNo is not null      
  Begin      
   SET @Filter = IsNull(@Filter + ' AND (''' + @SRTRNo + ''' IN (#tbl_05_01_TrMaster.ThirdPartyCode, SRTRNo_1))',' (''' + @SRTRNo + ''' IN (#tbl_05_01_TrMaster.ThirdPartyCode, SRTRNo_1))')      
  End      
 If @DATETIME  is not null       
  Begin      
   --SET @Filter = IsNull(@Filter + ' AND ' + @DATETIME,' ' + @DATETIME)
     SET @Filter = IsNull(@Filter + ' AND ' + REPLACE(@DATETIME, 'TrDate', '#tbl_05_01_TrMaster.TrDate'),
                        ' ' + REPLACE(@DATETIME, 'TrDate', '#tbl_05_01_TrMaster.TrDate'))
  End      
      
 SET @Filter = ' WHERE ' + @Filter      
       
 SET @Script = '      
 SELECT       
 ' + @TOPVALUE + '       
 #tbl_05_01_TrMaster.SRTRNo,      
 #tbl_05_01_TrMaster.Ex_Branch_ID,       
 #tbl_05_01_TrMaster.App_ID,      
 #tbl_05_01_TrMaster.Date,       
 #tbl_05_01_TrMaster.TrTypeID,       
 #tbl_05_01_TrMaster.Tr_Status_Name,       
 #tbl_05_01_TrMaster.PayAgent,       
 #tbl_05_01_TrMaster.TrStatusID,       
 --#tbl_Main_Details.Customer_Name_F,       
 --#tbl_Main_Details.Customer_Name_L,       
 --#tbl_Main_Details.Beneficiary_Name_F,       
 --#tbl_Main_Details.Beneficiary_Name_L,  
 temp.Customer_Name_F,         
 temp.Customer_Name_L,         
 temp.Beneficiary_Name_F,         
 temp.Beneficiary_Name_L,      
 #tbl_05_01_TrMaster.PayCountryID,       
 #tbl_Agent_Branch_Details.CountryID,      
 #tbl_Agent_Branch_Details.Agent_ID,       
 #tbl_05_01_TrMaster.Tr_Sequance,      
 #tbl_05_01_TrMaster.UserId,       
 #tbl_Agent_Branch_Details.Agent_Branch_ID,       
 #tbl_05_01_TrMaster.LoginCountryID,       
 #tbl_05_01_TrMaster.LoginCityID,       
 #tbl_05_01_TrMaster.LoginLocationID,       
 #tbl_05_01_TrMaster.Reference_No,       
 IsNull(#tbl_05_01_TrMaster.Remark,''None'') as Remark,      
 ''frmAddStopPayment.aspx?Branch='' + CAST(#tbl_05_01_TrMaster.Ex_Branch_ID as nvarchar(10)) + ''&AppID='' + #tbl_05_01_TrMaster.App_ID + ''&RefNo='' + IsNull(#tbl_05_01_TrMaster.Reference_No,'''') as hLink,      
 #tbl_05_01_TrMaster.Show,      
 tbl_04_04_TrType_Details.Tr_Description      
 FROM #tbl_05_01_TrMaster(nolock) INNER JOIN      
 #tbl_Agent_Branch_Details ON #tbl_05_01_TrMaster.UserId = #tbl_Agent_Branch_Details.UserId INNER JOIN      
 --#tbl_Main_Details ON #tbl_05_01_TrMaster.Customer_Id = #tbl_Main_Details.Customer_Id       
 --AND #tbl_05_01_TrMaster.Benef_Id = #tbl_Main_Details.Benef_Id 
 tbl_05_01_TrMaster_Temp temp on #tbl_05_01_TrMaster.SRTRNo = temp.TransactionNumber     
 INNER JOIN tbl_04_04_TrType_Details(nolock) ON #tbl_05_01_TrMaster.TrTypeID = tbl_04_04_TrType_Details.TrTypeID      
 ' + IsNull(@Filter,'')      
             
 Exec(@Script)       
END      
      
