Problem description: EFP does not distinguish GL Natural Account from COA Categories when they have the same number.

For instance in ERP: 

  • GL Natural Account: 5020 which represents Salaries – Clerical
  • COA Category: 5020 which represents General & Admin Expense
  • In EFP, there is only one of the two. User has the issue for all categories and GL natural accounts that have the same numbers.

Solution: This is a known issue in EFP r1.3. It can be fixed by following below steps and it is fixed in EFP r1.4.

1. Go to SQL Database Engine where your EFP DBs are located. -> Backup 3 EFP DBs: 

  • pcDATA_EFP
  • pcETL_EFP

2. Go to pcINTEGRATOR -> Tables -> dbo.SqlSource_Dimension -> download the excel attachment "SQL Source Dimension"

There are three rows of SourceString need to be updated as the attached excel SQL Source Dimension.

WHERE        (DimensionID = - 1) AND (SourceTypeBM & 1027 > 0) AND (PropertyID IN (3, 5))

3. Open a new query window, truncate 3 tables from pcDATA_EFP: 

  • dbo.FACT_Financials_default_partition
  • dbo.S_DS_Account
  • dbo.S_HS_Account_Account


TRUNCATE Table FACT_Financials_default_partition
TRUNCATE Table S_DS_Account
TRUNCATE Table S_HS_Account_Account

4. Re-create Account View

  • Check your SourceID from pcINTEGRATOR -> table dbo.Source (Financials model)
  • pcINTEGRATOR -> Programmability -> right click on dbo.spCreate_Dimension_View_Generic -> click on Execute Stored Procedure... -> type the source ID and "-1" in @DimensionID like below: 

5. Go to pcETL_EFP -> Table -> dbo.ClosedPeriod -> update all ClosedPeriod_Counter to be 0 and UpdateYN to be 1.

You can use below 3 lines query to do that too by run it from a new query window.

Update [pcETL_EFP].[dbo].[ClosedPeriod]
SET CLosedPeriod_Counter = 0,  UpdateYN = 1

6. Run night load job manually. Go to SQL Server Agent -> right click on pcDATA_EFP_Load -> select Start Job at Step... -> Start button.