PPS Planning - Current Period

Over the past 2 years one of the biggest mysteries to a lot of PPS Planning users is the storage of the current period which is set in the Business Planning Modeler. This has been discussed and a number of posts on the PPS forum have clearly demonstrated how one can get to it. However I have yet to see a full set of code to bring this setting into your environment for use. I use the current period for my hourly PPS updates from our ERP system so as to make sure that we do not reload old data and speed up the process. So part of my ETL process interrogates the XML blob in the table BizAppNodes and returns the current period ID and Label for each model. These records are inserted into a table that I keep up to date and then use this in my ETL process. Here is the code:

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 = 'FinanceModel'

SELECT distinct ModelLabel

,CurrentPeriodId

,T.[MonthLabel]

FROM

(

SELECT DISTINCT

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

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

tab.col.value('@EffectiveDate', 'varchar(30)') as EffectiveDate

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

) AS TABLE1

LEFT OUTER JOIN [dbo].[D_Time] T ON T.[Month] =
TABLE1.[CurrentPeriodId]

The output is the model name, period id and period label for each model in the application.

- Paul Steynberg