Updating Period Tables Manually in FDMEE from relational system

I had a colleague ask me this question some time back and it is only now that I have been able to do justice to this…The question is simple. Is it possible to update the periods in FDMEE without using the frontend which as you may know only allows you to add/edit one row at a time. The answer is a yes and in this blog I would be showing you how to update the period in FDMEE from the backend relational system.

This is a snapshot of the period Mappings with Global mappings shown. 

I have two entries, one for January 2016 and another for February 2016. In this demo, we would be updating the March 2016 entries in the table. 

The information about the Global Mappings for Period are stored in a table called as TPOVPERIOD. This table is present in the FDMEE schema as shown in the below snapshot.  

Observe the two entries for Jan-16 and Feb-16 shown above.

The structure of the table is as shown in the next snapshot.

PERIODKEY and PRIORPERIODKEY are formatted as dates.

PERIODDESC, PERIODTARGETM, PERIODTARGETQ, PERIODTARGEY, PERIODTARGETD and YEARTARGET are all VARCHAR columns.

I now update this table with an insert statement to insert the records for Mar-16 using the SQL statement as shown in the next snapshot. 

Observe that the PERIODKEY and PRIORPERIODKEY need to be casted as DATE in the above query. FDMEE does this internally using a Java method.

The TPOVPERIOD after updating the March 2016 records is as shown in the next snapshot.

 

I now check the Global Mapping for the Period in FDMEE and you will see that March 2016 is now present in the table as shown in the next snapshot.

This concludes the update of Period members in the Global mapping table for period.

Now, an application specific mapping for period is as shown in the next snapshot.

The mappings are for CoA application and has same mappings for Jan-16 and Feb-16.

The application specific mappings are stored in a table called as TPOVPERIODADAPTOR. The contents of this table is as shown in the next snapshot. 

Observe the second column in the above snapshot. The field INTSYSTEMKEY indicates the application to which the mappings belong. Observe the name CoA-CoA which corresponds to the Target application name from the Application period mapping tab. 

The metadata structure of the TPOVPERIODADAPTOR table is as shown in the next snapshot. 

The structure of the TPOVPERIODADAPTOR table is exactly same as TPOVPERIOD table with only a new column added to indicate the Target application name. 

I ran a select query on the TPOVPERIODADAPTOR table using the field names of TPOVPERIOD table and it worked perfectly as shown in the next snapshot. 

The INSERT statement to update the TPOVPERIODADAPTOR table is as shown in the next snapshot. This will update the table for Mar-16 for CoA application.

The TPOVPERIODADAPTOR table after updating the record for March is as shown in the next snapshot. 

Once the period mappings have been updated in the Application table, I checked the records in the Period Mapping in Data Management to find it reflected correctly as shown in the next snapshot.