Configuring for Sage 300
Sales Tables OEAUDH and OEAUDD
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.
When 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.
?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.
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:
- 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).
- Either let the whole refresh run once, or manually refresh these option field tables so that you can work with them.
- Create filters on the option field tables, where OPTFIELD = (the option field name)
- 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.
- The ETL will automatically add the new fields to the main table (such as ARCUS_Customers).
Add the (?option) fields to SQL reporting views, DSA, and other report writers:
- Add these fields to the appropriate SQL views (such as _C_Customer and then to _B_SalesHeader and _B_Sales).
- Modify the DSA data source to utilize the fields.
The Out of the Box reporting is in Functional Currency. Most Sage 300 transaction tables have amount fields available for Source, as well as Functional. To add Source currency functionality, add these Source amount fields to the table imports, and add them as additional measures to the reporting.
Note that for Sales Order and Purchase Order, Functional Currency ("FC") fields are sparse. The Out of the Box measures convert the amounts to Functional by multiplying by the Rate (currency conversion) field. Formula fields for the corresponding Source fields can be calculated by following the formulas in the FC fields, but omitting the Rate multiplier.
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.