871228 - How to import Budget through DTW

1.How to import Budget Scenarios.

Prerequisite:

Relevant setting for Budget initialization is done from Business One Client

Administration --> System Initialization --> General Settings --> Budget Tab

Preparing data for DTW.

Source table: OBGS

"Numerator" - Can be left empty as it will be filled automatically when the Budget Scenario is created.

"BasicBudget" - Enter the correct code of the basis scenario that is used for defining the current scenario. Value as Long.

"Name" - Enter a name that does not exist yet for the same year

"StartofFiscalYear" - Enter start of fiscal year in the format "YYYYMMDD". Example: 20090101

2.How to import Budget Distribution Method.

Prerequisite:

Make sure Financials --> Budget Setup --> Budget Scenarios setup is completed in Business One client.

Preparing data for DTW.

Source table: OBGD

"Description" - Enter the correct name of the budget distribution method (Field name: BgdName).

"DivisionCode" -Can be left empty when creating a new distribution method.The correct budget code (Field Name: BgdCode) must be entered when updating an existing Distribution Method.

"BudgetAmount" - Enter the total of the 12 monthly factors for the Distribution Method (Field name: BgdTotal).

3.How to import new Budgets

Prepare data for DTW

Source table: OBGT (Header) & BGT1 (Rows) & BGT2 (Cost Accounting)

• Budget Header:
  • "Numerator" - This field is used as a reference in the child template. When adding new data, the value of this field should be user defined (e.g., 1,2,3, etc.) and make sure this field is from small to large.
  • "AccountCode" - Enter the correct G/L account code as defined in Chart of Accounts in the column "AccountCode" (Field name: AcctCode). Verify that this account is Relevant to Budget (Chart of Accounts->Account Details->Relevant to Budget is ticked). Otherwise, An error "[OBGT.AccCode] Row in Transaction with Account No" appears.
  • "BudgetScenario" - Enter the correct budget scenario ID number (Field name: Instance). This is the foreign key to the Budget Scenario (OBGS) table:
    SELECT T0.[AbsId] FROM [dbo].[OBGS]  T0
  • "DivisionCode" - Enter the correct code of the budget distribution method (Field name: BgdCode). This is the foreign key to the Distribution Method in the Budget Cost Assess. Mthd (OBGD) table:
    SELECT T0.[BgdCode] FROM OBGD T0
    For Manual Distribution Method enter -1
• Budget Rows:
  • "ParentKey" - This field serves as a link to the parent template and it is mandatory. Either when adding new data or updating existing data, the value of this field should be taken from the parent template. Enter the same value as the Numerator in the header file.
  • "LineNum" - Enter Line Number from the rows starting with 0. 
  • Ensure that the sum of the budget rows equals the total value set in the header.
• Budget Cost Accounting:
  • "ParentKey" - This field serves as a link to the parent template and it is mandatory. Either when adding new data or updating existing data, the value of this field should be taken from the parent template. Enter the same value as the Numerator in the header file.
  • "LineNum" - Enter Line Number from the rows starting with 0. 
  • "OcrCode" - Enter the budget distribution rule code - The value should exists in the Distribution Rules (OOCR) table.
  • "DimCode" - Enter the budget cost accounting dimension code - The value should be exists in the Cost Accounting Dimension (ODIM) table.

4.How to Update existing Budget.

Prepare data for DTW

Source table: OBGT (Header) & BGT1 (Lines) & BGT2 (Cost Accounting)

• Budget Header:
  • "Numerator" - REQUIRED - Enter the Internal Number of the existing budget here:
    SELECT T0.[AbsId] FROM [dbo].[OBGT] T0
    WHERE T0.[AcctCode] = ' /*Enter Account Code here*/ '
  • "AccountCode" - Can be left empty when updating existing budget.
  • "BudgetScenario" - Can be left empty if no change is required.
    This is the foreign key to the Budget Scenario (OBGS) table:
    SELECT T0.[AbsId] FROM [dbo].[OBGS]  T0
  • "DivisionCode" - Can be left empty if no change is required.
    This is the foreign key to the Distribution Method in the Budget Cost Assess. Mthd (OBGD) table:
    SELECT T0.[BgdCode] FROM OBGD T0
    For Manual Distribution Method enter -1
• Budget Rows:
  • "ParentKey" - This field serves as a link to the parent template and it is mandatory. Either when adding new data or updating existing data, the value of this field should be taken from the parent template. Enter the same value as the Numerator in the header file 
  • "LineNum" - Enter correct Line Number from the rows starting with 0.
  • Ensure that the sum of the budget rows equals the value set in the header.
• Budget Cost Accounting:
  • "ParentKey" - This field serves as a link to the parent template and it is mandatory. Either when adding new data or updating existing data, the value of this field should be taken from the parent template. Enter the same value as the Numerator in the header file.
  • "LineNum" - Enter Line Number from the rows starting with 0.
  • "OcrCode" - Enter the budget distribution rule code - The value should exists in the Distribution Rules (OOCR) table.
  • "DimCode" - Enter the budget cost accounting dimension code - The value should be exists in the Cost Accounting Dimension (ODIM) table.