This blog post contains details of how to expand the basic Noetix Forecasting template (INV_Forecasts) to add in some of the nice new functionality in R12 such as grouping forecasts into Sets (i.e. all export forecasts could be in a set called EXPORTS and domestic forecasts one called DOMESTIC).
Looking at the existing Noetix view it accesses the Forecast data in a single table (MRP.MRP_FORECAST_DATES). The MRP Forecasting schema contains the additional tables MRP_Forecast_Designators (description and set information), MRP_Forecast_Items, MRP_Forecast_Updates, etc.
This change will add in the set and forecast description from the MRP_Forecast_Designators table. Looking at the possible joins between the two the easiest is;
This simply joins the two tables on the Forecast_Designator and Organziation_Id if they exist in the designators table.
Looking at the INV_Forecasts queries that make up this view using the SQL;
select n.view_label || ', ' ||
to_char(n.query_position) || ', ' ||
nvl(n.union_minus_intersection, 'null') || ', ' ||
n.view_comment "label,position,umi,comment"
from n_view_query_templates n
where n.view_label = 'INV_Forecasts'
Shows the following results;
label,position,umi,comment
INV_Forecasts, 1, null, forecast with day buckets
INV_Forecasts, 2, UNION ALL, forecast with week buckets
INV_Forecasts, 3, UNION ALL, forecast with period buckets
In order to get the view working we need to insert the new tables and columns into each of the queries (in effect doing the same thing three times).
Using the
XU2 column addition generation script I have blogged about previously with the values INV_Forecasts, Forecast_Designator, and Y (include tables) gives you a script that can be quickly and easily edited.
As I'm sure you're aware you need to add in the table prior to adding in the columns and that each of the column addition scripts needs to be in a separate file. Because of this I'm going to add the table in the script which adds the Forecast_Description column.
The changes required to add the new table (and columns) to the view are;
In the SELECT;
- MFD.Description Forecast_Description,
- MFD.Disable_Date Forecast_Disable_Date
- MFD.FORECAST_SET Forecast_Set
In the FROM clause;
- MRP.Mrp_Forecast_Designators MFD
And finally in the WHERE clause;
- AND FODAT.Forecast_Designator = MFD.Forecast_Designator(+)
- AND FODAT.Organization_Id = MFD.Organization_Id(+)
The three files are (all files stored in Google Docs);
inv_forecasts_forecast_description_xu2.sql
inv_forecasts_disable_date_xu2.sql
inv_forecasts_forecast_set_xu2.sql
Whilst we're here I'm going to add another two files to show who created the Forecast and when they did it (which I tend to find always useful - especially when identifying people to test changes!).
inv_forecasts_created_by_xu2.sql
inv_forecasts_creation_date_xu2.sql
You'll notice if you look in the "created_by" file that I'm using the AUTOJOIN column type in Noetix to quickly get this information. It can be a quick way of getting one field from another table but it does create problems if, for example, I wanted two fields (like the name and email address) and can introduce interdependencies between files that might cause problems if Noetix ever changes the way it works.
The next three changes I'm going to suggest are replacing the existing Forecast_Quantity column with the Current and Original Forecast Quantities from the forecast table. It is, frankly, rubbish to try and hide these columns and I feel represents a complete lack of understanding on how companies use forecasting; "How close are we to the forecast?" is a frequent question and so hiding the columns just makes absolutely no logical sense. To make this change requires three files;
inv_forecasts_current_forecast_quantity_xu2
inv_forecasts_original_forecast_quantity_xu2
inv_forecasts_forecast_quantity_upd_xu2
Once you've downloaded these files and added them into your install directory you need to add a call to them in the xu2 file (wnoetxu2.sql) and then you can run a regenerate and do some additional testing. Remember that the forecast description needs to be added first as it adds the table to the view (required by the other two) for example;
@inv_forecasts_created_by_xu2.sql@inv_forecasts_creation_date_xu2.sql@inv_forecasts_current_forecast_quantity_xu2.sql@inv_forecasts_forecast_description_xu2.sql -- Added MRP.MRP_FORECAST_DESIGNATORS(MFD)@inv_forecasts_forecast_quantity_upd_xu2.sql@inv_forecasts_disable_date_xu2.sql -- Requires MFD@inv_forecasts_forecast_set_xu2.sql -- requires MFD@inv_forecasts_original_forecast_quantity_xu2.sql
Hope this helps! Any questions leave a comment ...
