Query

General

Use Queries to select the group of parts you want to work with. You create a query either in the Conditions dialog box or you can write an SQL inspired query, we are talking about the WHERE Clause part of an SELECT statement. This will make it more flexible to find the parts you want to take a closer look at. It is also possible to get the query result aggregated so that you will get the Forecast Table and Forecast Graph show the aggregated result of the query. You can the manipulate all parts in the query the same way that you will manipulate a group selection. The other option is to list out all parts matching the query you can then click on one of the matching parts this will select this part and you can use the GUI to manipulate the forecast for this part. Below you can se witch operators and fields that are supported.

Operators

A unary operator uses only one operand. A unary operator typically appears with its operand in the following format:

operator operand
Operator Explanation
Unary + Makes operand positive
Unary - Negates operand
* Multiplication
/ Division
+ Addition
- Subtraction
< Less than test
<= Less than or equal
= Equality test
> Greater than test
>= Greater than or equal test
!= Inequality test
<> Inequality test
; or , List operator, depends on the local settings of the PC
LIKE The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE
AND Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE.
OR Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE.
TO_DATE Converts from text to a date, TO_DATE(string1, 'format_mask')

Supported TO_DATE format mask parameters

Parameter Explanation
YYYY 4-digit year
MM Month (01-12; JAN = 01)
DD Day of week (1-7)
HH24 Hour of day (0-23).
HH Hour of day (1-12)
MI Minute (0-59).
SS Second (0-59).
A.M. Meridian indicator, before midday
P.M. Meridian indicator, after midday

Fields

Query Name In Demand Planning
PART_NO The part number
PART_DESCRIPTION The parts description
FLOW_ID The flow id
FLOW_DESCRIPTION The flows description
FLOW_TYPE base or combined flow
CLASSIFICATION The classification group of the part (new parts, intermittent, season, level...)
SEASON_PROFILE_ID The parts season profile (the id / number), see Detail View
PERIOD PROFILE_ID The parts period profile, see Detail View
MOVABLE_HOLIDAY The parts movable holiday, see Detail View
APPROVED If the part is approved for IPR or not
ADJUSTMENT FACTOR The parts adjustment factor. See Detail View
ALPHA The parts standard alpha value, see Detail View
BETA The parts standard beta value, see Detail View
RHO The parts standard rho value, see Detail View
DELTA The parts delta value, see Detail View
FORECAST_MODEL The parts standard forecast model, see Detail View
HOLIDAY_MIXED_FACTOR The parts holiday mixed factor. See Detail View
PERIODICAL_LEAD_TIME The parts periodical lead time, see Detail View
FORECAST_COMMENT The parts forecast comment, see forecast comment
MANUAL_YEARLY_FORECAST The parts standard manual yearly, see Detail View
MOV_AV_PER The parts standard moving average periods, see Detail View
TRACKING_SIGNAL The parts tracking signal, see Detail View
PREDECESSOR The parts predecessor. See Detail View
INHERITANCE The parts inheritance setting. See Detail View
INHERITANCE_START The parts inheritance start period. See Detail View
PHASE_IN_LENGTH The parts phase in length. See Detail View
PHASE_IN_START The parts phase in start. See Detail View
PHASE_OUT_LENGTH The parts phase out length. See Detail View
PHASE_OUT_STOP The parts phase out stop. See Detail View
INHERITANCE_STOP The parts inheritance stop period. See Detail View
INHERIT_PERIOD_PROFILE The parts setting of the period profile inheritance flag (DNA icon). See Detail View
INHERIT_SEASON_PROFILE The parts setting of the season profile inheritance flag (DNA icon). See Detail View
INHERIT_WEEKLY_PROFILE The parts setting of the weekly profile inheritance flag (DNA icon). See Detail View
INHERIT_MOVABLE_HOLIDAY The parts setting of the movable holiday inheritance flag (DNA icon). See Detail View
ADJUSTMENT FACTOR The parts adjustment factor. See Detail View
MAE The parts MAE (mean absolute error), see Detail View
MAE_COEFFICIENT The pars MAE coefficient, how large proportion of the average historical forecast (in the same interval as the forecast error measures are computed)  is the MAE of the part.
MAPE The parts MAPE (mean absolute percentage error), see Detail View
ME The parts ME (mean error), see Detail View
RME The parts RME (Relative Mean Error), see Detail View
MAE_VALUE The parts Value of MAE, see Detail View
PVE The parts PVE (percent variation explained), see Detail View
MSE The parts MSE (mean squared error), see Detail View
ADJUSTMENT_FACTOR The pars adjustment factor, see Detail View
WMAPE The parts WMAPE (weighted mean absolute percentage error), see Detail View
EXPECTED_DEMAND_SIZE The parts expected demand size, see Detail View
INTER_ARRIVAL_TIME The parts inter arrival time, see Detail View
PERCENT_USED How much of the planned forecasted quantity that is sold until now in the period. See Aggregate Daily
INVENTORY_VALUE The parts inventory value / cost price, see Detail View
INVENTORY_UNIT The parts inventory unit, see Detail View
LEAD_TIME The parts lead time, see Detail View
MARKET_SEGMENT The parts market segment part, see Detail View
PLANNED_SALES_PRICE The parts sales price, see Detail View
NET_WEIGHT The parts net weight, see Detail View
PART_FLOW_VALUE The parts flow value, see Detail View
CREATED_DATE The date the part was created, see Detail View
HISTORICAL_PERIODS The length of the parts Demand vector since start until the current period, will only look at the parts demand, eventually inherited demand is not taken into consideration.
TOTAL_HISTORICAL_PERIODS The length of the parts Adjusted Demand vector since start until the current period. Will include the inherited demand as well
TRACKING_SIGNAL The parts tracking signal, see Detail View
TRANSACTION_START_DATE The parts transaction start date (sales start date), see Detail View
MOVING_AVERAGE_PERIODS The parts moving average periods, see Detail View
CONFIRMED_PERIODS_AGO Number of periods since the part was marked as confirmed
CONFIRMED_DAYS_AGO Number of days since the part was confirmed
CONFIRMED_DATE The date the part was confirmed
CONFIRMED_STATUS The status of the part confirmed or not confirmed
FORECAST_CHANGED_BY Which user has changed the forecast latest. See Detail View
VARIANCE The parts variance in demand, see Detail View
STATUS The parts status, see Detail View
STATUS_CHANGED_BY The user that changed the parts status last.
STATUS_CHANGED_DATE The date the parts status was changed.
SUM_FORECAST The sum of the parts adjusted forecast.
MAE_RANGE (Length, Offset)

The parts MAE (mean absolute error), see Detail View. This calculates the mean absolute error measurement for a specific time period.

MAE_VALUE_RANGE (Length, Offset) The parts Value of MAE, see Detail View. This calculates the Value of MAE error measurement for a specific time period.
MAPE_RANGE (Length, Offset) The parts MAPE (mean absolute percentage error), see Detail View. This calculates the mean absolute percentage error measurement for a specific time period.
ME_RANGE (Length, Offset) The parts ME (mean error), see Detail View. This calculates the mean error measurement for a specific time period.
PVE_RANGE (Length, Offset) The parts PVE (percent variation explained), see Detail View. This calculates the percent variation explained error measurement for a specific time period.
RME_RANGE (Length, Offset) The parts RME (Relative Mean Error), see Detail View. This calculates the Relative Mean Error measurement for a specific time period.
WMAPE_RANGE (Length, Offset) The parts WMAPE (weighted mean absolute percentage error), see Detail View. This calculates the weighted mean absolute percentage error measurement for a specific time period.
VARIANCE_RANGE (Length, Offset) The parts variance in demand, see Detail View . This calculates the variance error measurement for a specific time period.
TRACKING_SIGNAL_RANGE (Length, Offset) The parts tracking signal, see Detail View. This calculates the tracking signal error measurement for a specific time period.
MAPE_RANGE (Length, Offset) The parts MAPE (mean absolute percentage error), see Detail View. This calculates the mean absolute percentage error measurement for a specific time period.
PREFORMANCE_FACTOR_RANGE (Length, Offset) Forecast parts with the higher performance factor needs extra attention. Comparative to the demand variance, forecasted accuracy is low for such items. This calculates the performance factor error measurement for a specific time period.
FILL_RATE_SERVICE_RANGE(Lenght, Offset) Computes the fill rate service of the part see Detail View
SERVICE_LEVEL(Length, Offset) Computes the service (success) level of the part see Detail View

In addition you can use any of the user selected groups that are using in a query, the field name of the group is then used as query name. If you use the any of the standard available groups these will be the names.

PART_PRODUCT_FAMILY

PART_PRODUCT_CODE

ABC_CLASS

TYPE_CODE_DB

LEAD_TIME_CODE_DB

PLANNER_BUYER

ASSET_CLASS

PRIME_COMMODITY

SECOUND_COMMODITY

DEMAND_MODEL

LIFECYCLE_STAGE

FREQUENCY_CLASS

And any other that you include yourself is available.

Note that for the fields Forecast Model, Season Profile, Period Profile and Weekly Profile you must use the ID / number when you write your query. For group fields with description you most use the ID for the group when writing the query.

Custom Fields: This allows user to add custom fields both in the  detail view and  query  based on the users’ business requirements. In order to add custom fields, User needs to navigate to forecast_cust_field_ setup_tab and specify the following custom field related information in the table columns manually.

Table Column Name

Description

FORECAST_SERVER_ID

The specific forecast server ID, where custom field is added.

TABLE_VIEW_NAME

The specific database table, where custom field is retrieved.

FIELD_NAME

The specific custom field that is retrieving from database

DESCIPTION

The specific custom field name that need to be appear in detail view and  query.

ROWVERSION

The specific date and time, where user insert/update the custom field

In order to appear the inserted/updated custom fields in the detail view and  query, User needs to commit the changes done in the forecast_cust_field_ setup_tab and restart the demand plan server

Note- The user needs to run the POST_Demand_DemandServerUser.sql script, Once the changes have been committed in the forecast_cust_field_ setup_tab. (The script is stored in the following file location demand\source\demand\database\POST_Demand_DemandServerUser.sql ")