Get CurrentPeriod value for each model inside a model site from Planning DB


While most of Planning system’s meta data is stored in native relational tables in the Planning database, there are some meta data is stored in xml format in the database, for example, Current Period for each model inside a specific model site. You may need to get CurrentPeriod value for the model from the Planning databases. Here is a sample SQL query to get the CurrentPeriod for each model inside a specific model site. The following example uses model site label of “Corporate”. Please note that this is only valid for the current released version of Planning system, the future release may change the way how these meta data is stored.


DECLARE @xmlblob xml


SELECT @xmlblob = CAST(CAST ( ba.xmlblob AS varbinary(MAX)) as xml)


FROM BizAppNodes ba


WHERE VersionEndDateTime = ‘9999-12-31 00:00:00.000’


AND BizAppNodeLabel = ‘Corporate’


 


SELECT DISTINCT


tab.col.value(‘../../@Label’, ‘varchar(30)’) as ModelLabel,


tab.col.value(‘@CurrentPeriodId’, ‘varchar(30)’) as CurrentPeriodId


FROM @xmlblob.nodes (‘/BizModelSite/Models/ArrayOfBizModel/BizModel/EffectiveDatedCurrentPeriods/EffectiveDatedCurrentPeriod’) as tab(col)


Comments (0)