Difference between revisions of "Configuring for Sage 300"

From DataSelf Knowledge Base
Jump to navigation Jump to search
Line 18: Line 18:
 
# Modify the DSA data source to utilize the fields.
 
# Modify the DSA data source to utilize the fields.
  
NOTE: The Sales reporting in the DataSelf BI for Sage 300 is based on the OEAUDH and OEAUDD tables. Therefore, if making new option fields for orders or OE invoices, it easier to set up the formulas for the option fields if they are also created in OEAUDH and OEAUDD. However, they can be copied in from OEINVH/D and OECRDH/D.
+
=== OEAUDH and OEAUDD ===
 +
 
 +
The Sales reporting in the DataSelf BI for Sage 300 is based on the OEAUDH and OEAUDD tables. Therefore, if making new option fields for orders or OE invoices, it easier to set up the formulas for the option fields if they are also created in OEAUDH and OEAUDD.  
 +
 
 +
However, in the ETL unique relationships have been created between the OEAUDH/D tables and the OEINVHO/DO and OECRDHO/DO tables. Create an option field formula in OEAUDH or OEAUDD the concatenates the values from both of the INV and CRD corresponding option field tables. Because they are unique relationships, one of the values will be NULL, therefore the results will be from at the most only one option table.
  
 
== GL Accounts ==
 
== GL Accounts ==
  
 
The GL Accounts table assumes that Segments 1, 2, and 3 are being used, and in that order. If this is not the case, the GLAMF_Accounts table will need additional formulas, and the corresponding views modified.
 
The GL Accounts table assumes that Segments 1, 2, and 3 are being used, and in that order. If this is not the case, the GLAMF_Accounts table will need additional formulas, and the corresponding views modified.

Revision as of 20:38, 6 March 2014

Notes for OEAUDH and OEAUDD

The Sales reporting is based on the history in OEAUDH/D, with supplementary history from OEINVH/D and OECRDH/D. If any of these tables are going to be purged, the ETL needs to first be set to archive the history. The history should be archived in a static table on the ETL as a backup. Then the tables being purged need to either union with the archived data (with a filter on DayEndNumber to prevent double-posting of overlapping invoices), or be set to Replace With Control (RWC) using DayEndNumber.

Option Fields

Some of the major option field tables are already prepared within the Out-of-the-Box data warehouse. To add an option field to a table, do the following within the ETL:

  1. Add the option table (such as ARCUSO_CustomerOptionFields) and CSOPTFD_OptionalFieldsValues to the Refresh Wizard, in the same first step of the main refresh batch (usually, batch 0).
  2. Either let the whole refresh run once, or manually refresh these option field tables so that you can work with them.
  3. Create filters on the option field tables, where OPTFIELD = (the option field name)
  4. Create formulas (Character 60) on the main table (such as ARCUS_Customers). Select the child table (such as ARCUSO_CustomerOptionFields). You will be able to use either the Value or the VDesc (from CSOPTFD). Open up the list under Value or VDesc and find the correct filter. Double-click on the filter to create the formula value. SAVE the formula: by default, we name the option fields being imported: OPTFIELD_FIELDNAME or OPTFIELD_FEILDNAME_VDesc.
  5. The ETL will automatically add the new fields to the main table (such as ARCUS_Customers).

Add the fields to SQL reporting views, DSA, and other report writers:

  1. Add these fields to the appropriate SQL views (such as _C_Customer and then to _B_SalesHeader and _B_Sales).
  2. Modify the DSA data source to utilize the fields.

OEAUDH and OEAUDD

The Sales reporting in the DataSelf BI for Sage 300 is based on the OEAUDH and OEAUDD tables. Therefore, if making new option fields for orders or OE invoices, it easier to set up the formulas for the option fields if they are also created in OEAUDH and OEAUDD.

However, in the ETL unique relationships have been created between the OEAUDH/D tables and the OEINVHO/DO and OECRDHO/DO tables. Create an option field formula in OEAUDH or OEAUDD the concatenates the values from both of the INV and CRD corresponding option field tables. Because they are unique relationships, one of the values will be NULL, therefore the results will be from at the most only one option table.

GL Accounts

The GL Accounts table assumes that Segments 1, 2, and 3 are being used, and in that order. If this is not the case, the GLAMF_Accounts table will need additional formulas, and the corresponding views modified.