Writeback to Sage 300

Warning! With a “Budget and Writeback” licence, you are able to view the Workset tab and use the Writeback functionality.

Once the required fact has been selected, the SQL query to be executed for each amended fact will be displayed.

The SQL query will be executed for all amended facts. You can include dimension values from your cube by including them in the SQL statement, surrounded by percent signs (%).

The amended budget, forecast or scenario figures must be included from your cube by including the fact as %VALUE%

There are 3 supported write back queries for Sage 300

GL Budgets
Information Manager Infosets
IC Item Projected Sales

Note:

No new records will be created in the Sage 300 database with the default queries.

to update a GL budget for an account/fiscal year/fiscal period combination – the budget record must already exist in Sage 300. So you might need to import zero for all accounts/fiscal year/fiscal periods required.
to update an information manager infoset, the initial record must be inserted for the key combination and date required. So you might need to use an import/export into Infosets, or the Information Manager Excel add-in to create new records
to update IC Item projected sales, the initial record(s) must be imported into the IC reorder table

Example SQL queries:

Example SQL query to update GL Budgets:

For a specific site, the sample cube would need adapting.

Included dimensions included in percentage signs %xxx%, and are highlighted in yellow

The budget, forecast or scenario data entered is always called %VALUE% shown in green below

The SQL query is shown in blue text below

Hard coded fields in red (currency, budget set and currency type) would need amending per customer site

DECLARE @SQL VARCHAR(MAX);

DECLARE @columnname VARCHAR(10);

DECLARE @period VARCHAR(2);

DECLARE @currency VARCHAR(3);

DECLARE @fscdsg VARCHAR(1);

DECLARE @curntype VARCHAR(1);

SET @currency = N'CAD';

SET @fscdsg = N'1';

SET @curntype = N'F';

SET @period = N'%PERIOD%';

SET @columnname = N'NETPERD' +

case left(@period,1) when '0' then RIGHT(rtrim(@period),1) else RIGHT(rtrim(@period),2) end;

SET @SQL = 'UPDATE GLAFS set ' + @columnname + ' = %VALUE% WHERE RTRIM(ACCTID) = ''%UNFORMATTEDACCOUNT%'' AND FSCSYR = ''%FISCALYEAR%'' AND FSCSCURN = ''' + @currency +

''' AND FSCSDSG = ''' + @fscdsg + ''' AND CURNTYPE = ''' + @curntype + ''';'

EXEC (@SQL);
Example SQL query to update Customer/Item Budgets:

This query is designed to update a Customer/Item budget stored in an Information Manager infoset. There are facts which can be entered, Budget Quantity, Budget Sales and Budget Cost

Included dimensions included in percentage signs %xxx%, and are highlighted in yellow

The budget, forecast or scenario data entered is always called %VALUE% shown in green below

The SQL query is shown in blue text below

Hard coded variables which might require changing per customer site are shown in red text below

a. SQL Query for fact BUDQTY
Copy

SQL Query for BUDQTY
```
DECLARE @SQL VARCHAR(MAX);
DECLARE @infokey as VARCHAR(10) = CONVERT(VARCHAR(10),'%INFOKEY%');
DECLARE @optfield as VARCHAR(10) = N'BUDQTY';
SET @SQL = 'UPDATE EBINFOO set VALUE = %VALUE% WHERE INFOKEY = ' + @infokey +' AND RTRIM(OPTFIELD) = ''' + @optfield +  ''' ;' ;
EXEC (@SQL);
```
b. SQL Query for fact BUDCOST

DECLARE @SQL VARCHAR(MAX);

DECLARE @infokey as VARCHAR(10) = CONVERT(VARCHAR(10),'%INFOKEY%');

DECLARE @optfield as VARCHAR(10) = N'BUDCOST';

SET @SQL = 'UPDATE EBINFOO set VALUE = %VALUE% WHERE INFOKEY = ' + @infokey +

' AND RTRIM(OPTFIELD) = ''' + @optfield + ''' ;' ;

EXEC (@SQL);

c. SQL Query for fact BUDVALUE

DECLARE @SQL VARCHAR(MAX);

DECLARE @infokey as VARCHAR(10) = CONVERT(VARCHAR(10),'%INFOKEY%');

DECLARE @optfield as VARCHAR(10) = N'BUDCOST';

SET @SQL = 'UPDATE EBINFOO set VALUE = %VALUE% WHERE INFOKEY = ' + @infokey +

' AND RTRIM(OPTFIELD) = ''' + @optfield + ''' ;' ;

EXEC (@SQL);
Example SQL query to update IC Item Projected Sales Budgets:

This query is designed to update a Customer/Item budget stored in an Information Manager infoset. There are facts which can be entered, Budget Quantity, Budget Sales and Budget Cost

Included dimensions included in percentage signs %xxx%, and are highlighted in yellow

The budget, forecast or scenario data entered is always called %VALUE% shown in green below

The SQL query is shown in blue text below

DECLARE @SQL VARCHAR(MAX);

DECLARE @periodstart as VARCHAR(8) = CONVERT(VARCHAR(9),'%PERIODSTART%',112);

DECLARE @itemno as VARCHAR(24) = N'%ITEMNO%';

DECLARE @location as VARCHAR(10) = N'%LOCATION%';

SET @SQL = 'UPDATE ICREORD set SALESPROJ = %VALUE% WHERE RTRIM(ITEMNO) = ''' + @itemno +

''' AND LOCATION = ''' + @location + ''' and PERIODSTRT = ' + @periodstart + ' ;' ;

EXEC (@SQL);