BP master data show active, but the list of BP and query show inactive?

[beehoiphoebe_yeo]

From query, some BP validFor and frozenFor fields show as 'N', however, if I refer to the BP Master Data, the BP is active. At the list of Business Partner, the "Active' field is also showing 'No'. Why it is so? 

[LoHa]

Hi,

if you want it complete real icluding the dates you can make a function. It returns Y/N

USE [YOURNAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:	Check if Item/BP is active
-- =============================================
ALTER FUNCTION [dbo].[YOURNAME]
(
	-- Add the parameters for the function here
	 @ValidFor CHAR(1)
	,@ValidFrom DATE
	,@ValidTo DATE
	,@FrozenFor CHAR(1)
	,@FrozenFrom DATE
	,@FrozenTo DATE
	,@Date DATE
)
RETURNS Char
AS
BEGIN

RETURN CASE
	WHEN
	(
	@validFor = 'N' OR	(
						@validFrom IS NULL OR  @validFrom <= (@Date)
						) 
	AND  
	(@validTo IS NULL   OR  @validTo >= (@Date))
	) 
	AND  
	(
	@frozenFor = 'N' OR  @frozenFrom IS NOT NULL   
	AND  
	@frozenFrom > (@Date)  OR  @frozenTo IS NOT NULL 
	AND 
	@frozenTo < (@Date) 
	)
	THEN 'Y'
	ELSE 'N'
	END

END

--SELECT
--CardCode
--	, ValidFor 
--	, ValidFrom 
--	, ValidTo
--	, FrozenFor 
--	, FrozenFrom 
--	, FrozenTo
--	, GETDATE() 
--,(SELECT [dbo].[YOURNAME] (ValidFor,ValidFrom	, ValidTo, FrozenFor , FrozenFrom , FrozenTo, GETDATE() ))
--FROM
--OCRD
--WHERE CardCode = 'XXXX'

regards Lothar

[Johan_H]

Very nice, but the words cannon and mosquito come to mind 😉

[LoHa]

Hi Johan, normally I would say you are right, but at least the user is weakest part of the chain. Meanwhile I found every kind of possibilities in our DB 😉

[Johan_H]

We have one or two custom versions of "inactive". This method is good to add such custom scenarios.