BPC Drillthrough for CMS Actuals

Actuals Activity

Current year Actuals data is pulled from 1 table:

GTRAN - G/L Transactions

For previous year Actuals, data is pulled from:

GTLYN - Last Year G/L Transactions


Opening Balances

Retrieved from GLMT

FinanceDrillThrough Table

For Activity and Opening Balances, data is written to the FinanceDrillThrough table which looks like this:

Column_name	Type	Length	Nullable
Entity	nvarchar	64	no
Account	numeric	9	no
Period	numeric	5	no
JournalType	nvarchar	2	yes
TranDate	datetime	8	yes
TranSource	nvarchar	4	no
Amount	numeric	9	no
RefNum	nvarchar	20	yes
RefDesc	nvarchar	60	no
RevEntry	nvarchar	2	yes
Century	numeric	5	yes
Year	numeric	5	yes
PostDate	datetime	8	yes
Batch	numeric	5	yes
MinorSalesCode	nvarchar	6	yes
MajorSalesCode	nvarchar	6	yes

Rollups

The GetFinanceDrillThrough stored procedure calls functions that explode rollups into base level members.

Pulling Data From CMS into BPC

Several Files (tables) in CMS are used when pulling data.

MAST - G/L Chart of Account Header
GLMT - G/L Chart of Account Totals for Year

Most of the data comes from GLMT. That is, activity and opening balance comes from GLMT. Properties like account type and company are retrieved from MAST.

Retrieving data from any system requires the same thing. Pulling Company(Entity), Account, Account Type (P&L, BS), Opening Balance and Activity. In some cases, Company is broken up as well such as Payroll and Operations. In those cases that breakdown must be retrieved as well.