USE [prince]
GO
/****** Object:  StoredProcedure [Common].[CPN_SearchCode]    Script Date: 11/23/2025 09:19:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [Common].[CPN_SearchCode]

    @CPN_Code            _TINY_,
    @CPN_SubscriberType  _INT_,	    
	@CPN_LKP_Section	 _INT_	    

AS
BEGIN

    SET NOCOUNT ON;
 
    
    
    DECLARE @MAXID  [Common]._TINY_
    
    SET @MAXID = (SELECT [CPN_ID] 
                    FROM [Common].[CompanyPerson]
                   WHERE [CPN_Code] = (SELECT MIN([CPN_Code])  
										FROM [Common].[CompanyPerson]
									   WHERE (CPN_IsDisabled = 0) AND
											 (CPN_IsHidden = 0) AND
											 (CPN_IS_Subscriber = 1) AND 
											-- ([CPN_SubscriberType] IN (0,1)) AND 
											 ([CPN_SubscriberType] = @CPN_SubscriberType) AND  
											 ([CPN_Code] LIKE @CPN_Code + '%')) AND
						(CPN_IsDisabled = 0) AND
						(CPN_IsHidden = 0) AND
						(CPN_IS_Subscriber = 1) AND 
						([CPN_SubscriberType] = @CPN_SubscriberType) 
				 )
    
    IF @MAXID IS NULL
       SET @MAXID = '-123456879'
    
    
    SELECT
            [V_CPN_ID] AS [CPN_ID] ,    
			[CPN_Name] ,
			[CPN_LKP_Title] ,
			ISNULL((SELECT LKP_Caption FROM [Common].[Lookup] WHERE LKP_ID = [CPN_LKP_Title]),'') AS [CPN_Title],		
			[CPN_Code] ,
			(SELECT PGM_CPG_ID FROM Common.CompanyPersonGroupMembership WHERE PGM_CPN_ID = V_CPN_ID) AS [CPN_LKP_Group] ,        
			(SELECT CPG_Name FROM Common.CompanyPersonGroup WHERE CPG_ID IN 
             (SELECT PGM_CPG_ID FROM Common.CompanyPersonGroupMembership WHERE PGM_CPN_ID = V_CPN_ID))  AS CPN_GroupName,        
			[CPN_LKP_PriceLevel] as [CPN_CostLevel] ,
			(SELECT LKP_Caption FROM [Common].[Lookup] WHERE LKP_ID = [CPN_LKP_PriceLevel]) AS CPN_CostLevelCaption,                
			[CPN_SCP_ID] , [SCP_Name] ,
			[CPN_CostChange] ,
			[CPN_IS_Credit] ,
			[CPN_MaxCredit] ,
			[Phone1] ,
			[Phone2] ,
			[Phone3] ,
			[Phone4] ,        
			[Phone5] ,
			[Phone6] ,        
			[Mobile_NO] ,
			[Fax_NO] ,
			[Address1] ,
			[Address2] ,
			[CPN_Description] ,
			[CPN_Type] ,
			[CPN_SubscriberType],
			[CPN_NationalCode] ,
			[CPN_EconomicCode] ,
			[CPN_DefaultDiscountType] ,
			[CPN_DefalutDiscountValue] ,
			[CPN_DefaultServiceType] ,
			[CPN_DefalutServiceValue] ,			        
			[CPN_MarriageDate] ,        
			[CPN_Exempt_TaxTol],
			[CPN_IsHiddenDesc],
			(SELECT [ACN_CurrentAmount] FROM [Common].[Account] WHERE (ACN_HolderID = V_CPN_ID) AND (ACN_TBL_Holder = 15)) AS [ACN_CurrentAmount]        
            ,CPN_LKP_Section
		FROM [Common].[View_CPN_Info]
			LEFT OUTER JOIN [Common].[SaffronScope] ON [CPN_SCP_ID] = [SCP_ID]
		WHERE (CPN_IsDisabled = 0) AND (CPN_IsHidden = 0) AND ([V_CPN_ID] = @MAXID) AND 
            ((ISNULL([CPN_LKP_Section],0) = 0) OR (@CPN_LKP_Section IS NULL) OR ([CPN_LKP_Section] = @CPN_LKP_Section))AND
            (CPN_IS_Subscriber = 1) AND 
            ([CPN_SubscriberType] = @CPN_SubscriberType) 
    
 END
