F Appendix: Promotion and Markdown Manage LPO Recommendation
This table provides information about table columns and calculations for Promotion and Markdown Optimization recommendations.
| Column Heading | Default (Y/N) | Definition | DB Reference / Calculation |
|---|---|---|---|
| Flag | Y | Users can flag the recommendation for further review. | PRO_RUN_RECOM_OPT_RESULT.FCST_REVIEW_CODE |
| Notes | Y | Users can enter any comment for the recommendation. For example, Additional Budget received so overriding to a deeper markdown | PRO_RUN_RECOM_OPT_RESULT.FCST_NOTE |
| Ovrd Status | Y | Shows whether the item has any pending recalculate. It shows which user requested it. | PRO_RUN_RECOM_OPT_RESULT.CHANGED_BY_USER |
| Ext Code, Name, Descr of - Item / Division / Group / Department / Class / Sub Class / Style / Style Color | Y | Product Hierarchy | CONCAT(RSE_PROD_HIER.PROD_EXT_CODE,'-',RSE_PROD_HIER.NAME)Based on RSE_PROD_HIER.HIER_LEVEL_ID |
| Item Descr | Y | Item description, external code, and name separated in different columns. | RSE_PROD_HIER.DESCRRSE_PROD_HIER.HIER_LEVEL_ID=8 |
| Brand Name | Y | RSE_PROD_GRP.NAME | |
| Price Zone | Y | Price zone of the item recommendation. Corresponds to the run setup level. |
RSE_PRICE_ZONE and RSE_PRICE_ZONE_GRP |
| Chain Ext Code/Name/Descr > Area > Region > District > Location Ext Code / Name / Descr | Y | Location Hierarchy. N/A with Price Zones. | RSE_LOC_HIER_DH |
| Rec Status | Y | Recommendation status of the item, which says Ready for Review> Reviewed > Approved/Submitted/Taken > Export Failed | PRO_RUN_RECOM_OPT_RESULT.RECOMMENDATION_STATUS_IDJoin With PRO_RECOMMENDATION_STATUS on ID to get UI_NAME. |
| Season | Y |
Season. For example, Fall 2024 Merchandise |
PRO_SEASON.NAME |
| Company - Dynamic level (clean up) | Y | Location of the item recommendation. (For example, Region, Channel, and so on). Corresponds to the run location setup level. Will be chain when using price zones. PRO_DB_PARAMETER_VW; #PRO_LOC_HIER_PROCESSING_LVL = 1 (RSE_HIER_LVL) | RSE_LOC_HIER |
| Tkt Price | Y | Ticket Price of the item. Reflects TRAN TYPE 0, 4, 8. Rolled up from leaf node levels (for example, SKU-STORE) using Most Common Value | PRO_RUN_RECOM_OPT_RESULT.CURRENT_PRICE |
| Reg Price | Y | Regular Price of the item. Reflects TRAN TYPE 0, 4. Rolled up from leaf node levels (for example, SKU-STORE) using Most Common Value | PRO_RUN_RECOM_OPT_RESULT.LAST_REGULAR_PRICE |
| Orig Price | Y | Original Price of the item. Reflects TRAN TYPE 0. Rolled up from leaf node levels (for example, SKU-STORE) using Most Common Value | PRO_RUN_RECOM_OPT_RESULT.REGULAR_PRICE |
| Eff Price | Y | Effective Price of the item - it will reflect either the recommended or the overridden/user added price. If the recommendation is rejected, then it will reflect the ticket price of the item. | Determined during run-time and displayed in UI. |
| Eff Rec Type | Y | Effective Recommendation Type of the item. It will be recommended, or the overridden/user added or rejected action. | Determined during run-time and displayed in UI. |
| Eff Start Dt | Y | Effective start date of the recommendation. Reflect user date override as well. | PRO_RUN_RECOM_OPT_RESULT.EFF_START_DT |
| Eff End Dt | Y | Effective end date of the recommendation. Not applicable for Markdowns. Reflect user date override as well. | PRO_RUN_RECOM_OPT_RESULT.EFF_END_DT |
| Eff % off Tkt | Y | Effective Price as % off of Ticket Price. | Determined during run-time and displayed in UI. |
| Eff % off Orig | Y | Effective Price as % off of Original Price. | Determined during run-time and displayed in UI. |
| Eff % off Regular | Y | Effective Price as % off of Regular Price. | Determined during run-time and displayed in UI. |
| Rec Price | Y | Recommended Price of the Item | PRO_RUN_RECOM_OPT_RESULT.RECOM_PRICE |
| Rec Type | Y | Recommendation Type of the Item. Markdown, Promotion, No Recommendation, Non-forecasted | PRO_RUN_RECOM_OPT_RESULT.PRICE_RECOM_TYPE_ID Join with PRO_PRICE_RECOM_TYPE on ID to get DESCR |
| Rec % off Tkt | Y | Recommended Price as % off of the Ticket Price. | PRO_RUN_RECOM_OPT_RESULT |
| Rec % off Orig | Y | Recommended Price as % off of the Original Price. | PRO_RUN_RECOM_OPT_RESULT |
| Mkdn Cnt | Y | Markdown Number. If the item is marked down, this number will go up by 1. | PRO_RUN_RECOM_OPT_RESULT.MKDN_COUNT |
| Promo Cnt | Y | Promotion Number. If the item is promoted, this number will go up by 1. | PRO_RUN_RECOM_OPT_RESULT.PROMO_COUNT |
| Price Rung | Y | Rungs that are defined in the rules table for rule name PRO_RUNG_TIER_DISC_PCT. It is calculated with respect to the basis defined in PRO_DISC_TIER_BASIS. | PRO_RUN_RECOM_OPT_RESULT.DISC_TIER |
| Proj Sls Units | Y | Projected Sales Units for the Effective Week, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_FORECASTED_SALES |
| Proj Sls Rev | Y | Projected Sales Revenue for the Effective Week. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_REVENUE |
| Proj GM | Y | Projected Gross Margin $ for the Effective Week, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_GROSS_MARGIN |
| Proj Sls Units (Last Act to EW) | Y | Projected Sales Units from the Last Actual Week (or Processing Week) and until the Effective Week, including any applied overrides. It does not include the Last Actual Week and Effective Week. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_FORECASTED_SALES |
| Proj Inv Beg of EW | Y | Projected inventory position at the beginning of the Effective Week, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_FORECASTED_SALES+OPTIMAL_REMAINING_INVENTORYPRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_OPT_SALES_QTY |
| Last Act Inv | Y | Inventory for the Last Actual Week (or Processing Week). It is the sum of all inventory components that are configured to be used for optimization. | PRO_RUN_RECOM_OPT_RESULT.INV_QTY_LAD |
| Total Buy | Y | It is sum of Cumulative Sls Units and the Last Act Inv. Cumulative Sls Units is calculated from the first receipt date. | PRO_RUN_RECOM_OPT_RESULT.INITIAL_INVENTORY |
| Promo Bdgt Used | Y | This is calculated as the Proj Sls Units * (ticket price - recom price). | PRO_RUN_RECOM_OPT_RESULT.PROMO_BUDGET_USED |
| Mkdn Bdgt Used | Y | This is calculated as the Proj Inv Beg of EW * (ticket price - recom price) | PRO_RUN_RECOM_OPT_RESULT.MKDN_BUDGET_USED |
| Start Dt | Y | Start date of the Item. For example, 2% ST or 3 weeks from first receipt date. | PRO_RUN_RECOM_OPT_RESULT.MODEL_START_DT |
| Exit Dt | Y | Exit date of the item. | PRO_RUN_RECOM_OPT_RESULT.EXIT_DT |
| Run Id and Name | Y | ID of optimization run generating rec. | CONCAT(PRO_RUN_HDR.ID,'-',PRO_RUN_HDR.NAME) |
| Optimization Date | Y | Date of optimization run. | PRO_RUN_HDR.LAST_EXECUTION_DATE |
| Recalculation Date | Y | Date when the item is recalculated. | PRO_RUN_RECOM_OPT_RESULT.RECALCULATE_DT |
| Recalculation User | Y | User who last requested the recalculate. | PRO_RUN_RECOM_OPT_RESULT.RECALCULATE_BY_USER |
| Exported On Date | Y | Date when the item is Approved. | PRO_RUN_RECOM_OPT_RESULT.EXPORTED_ON_DT |
| Approved By User | Y | User who approved the recommendation. | PRO_RUN_RECOM_OPT_RESULT.EXPORTED_BY_USER |
| Net Sales Units (Last Week) | Y | Number of product units sold in the last completed week. | PRO_RUN_RECOM_DENORMALIZE.SLS_QTY_LAST_WEEK |
| Curr Sls Units | Y | Projected Sales Units for the Effective Week at Current Price. | PRO_RUN_RECOM_OPT_RESULT.CURRENT_FORECASTED_SALES |
| Curr Sls Rev | Y | Projected Sales Revenue for the Effective Week at Current Price. | PRO_RUN_RECOM_OPT_RESULT.CURRENT_REVENUE |
| Curr GM | Y | Projected Sales Margin for the Effective Week at Current Price. | PRO_RUN_RECOM_OPT_RESULT.CURRENT_GROSS_MARGIN |
| Ovrd Price | N | Overridden price for the item. | PRO_RUN_RECOM_OPT_RESULT.USR_OVRD_RECOM_PRICE |
| Ovrd Price Type | N | Override price type for the item. | PRO_RUN_RECOM_OPT_RESULT.USR_OVRD_PRICE_RECOM_TYPE_ID JOIN WITHPRO_PRICE_RECOM_TYPE ON ID to get DESCR |
| Rec Accepted? | N | Y or No flag to denote whether recommendation was accepted as is or not. | PRO_RUN_RECOM_OPT_RESULT.ACCEPTED_FLG |
| Proj Inv End of EW | N | Projected inventory at the end of the effective week, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.OPTIMAL_REMAINING_INVENTORY |
| Final Ship Dt | N | Denotes the calculated final ship date of the item. Last inventory change date that is older than x weeks (configuration) is assigned as the final ship date for the item | PRO_RUN_RECOM_OPT_RESULT.FINAL_SHIP_DT |
| Proj Inv EOL | N | Projected inventory at end of the life, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.PROJ_OPT_EOL_INV |
| Proj ST EOL | N | Projected sell through at end of life, including any applied overrides. | 1-(PRO_RUN_RECOM_OPT_RESULT.PROJ_OPT_EOL_INV / PRO_RUN_RECOM_OPT_RESULT.INITIAL_INVENTORY) |
| Curr Proj Inv EOL | N | Projected inventory with current price at end of life, including any applied overrides. | PRO_RUN_RECOM_OPT_RESULT.PROJ_CURR_EOL_INV |
| Curr Proj ST EOL | N |
Projected sell through at end of life with current price, including any applied overrides. |
1-(PRO_RUN_RECOM_OPT_RESULT.PROJ_CURR_EOL_INV / PRO_RUN_RECOM_OPT_RESULT.INITIAL_INVENTORY) |
| Proj Sls Units EOL | N | Total projected sales units at end of life, including any applied overrides. It is calculated as the sum of cumulative sales units until the last actual week and projected sales units for the remainder of the life at the optimal price path, including any applied overrides. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_QTY + PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_OPT_SALES_QTY |
| Proj Rev EOL | N | Total projected revenue at end of life, including any applied overrides. It is calculated as the sum of cumulative sales revenue until the last actual week and projected sales revenue for the remainder of the life at the optimal price path. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_AMT + PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_OPT_REVENUE |
| Proj GM EOL | N | Total projected gross margin at end of life, including any applied overrides. It is calculated as the sum of cumulative sales gross margin until the last actual week and projected sales gross margin for the remainder of the life at the optimal price path. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_GMARGING+ PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_OPT_GMARGIN |
| Curr Proj Sls Units EOL | N | Total projected sales units at end of life with current price, including any applied overrides. It is calculated as the sum of cumulative sales units until the last actual week and projected sales units for the remainder of the life at the current ticket price. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_QTY + PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_CURR_SALES_QTY |
| Curr Proj Rev EOL | N | Total projected revenue at end of life with current price, including any applied overrides. It is calculated as the sum of cumulative sales revenue until the last actual week and projected sales revenue for the remainder of the life at the current ticket price. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_AMT + PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_CURR_REVENUE |
| Curr Proj GM EOL | N | Total projected gross margin at end of life with current price, including any applied overrides. It is calculated as the sum of cumulative sales gross margin until the last actual week and projected sales gross margin for the remainder of the life at the current ticket price. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_GMARGING+ PRO_RUN_RECOM_OPT_RESULT.TOT_PROJ_CURR_GMARGIN |
| Sls Units T/D | N | Net Sales Units till last loaded week. | PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_QTY |
| Sls Units 1W Ago | N | Net Sales Units 1 week ago. | PRO_RUN_RECOM_DENORMALIZE.SLS_QTY_LAST_WEEK |
|
Sls Units 2W Ago |
N |
Net Sales Units 2 week ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_QTY_2_AGO |
|
Sls Units 3W Ago |
N |
Net Sales Units 3 week ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_QTY_3_AGO |
|
Sls Amt 4W Ago |
N |
Net Sls Amount/Revenue 4 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_AMT_4_AGO |
|
Sls Amt T/D |
N |
Net Sls Amount/Revenue till last loaded week. |
PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_SLS_AMT |
|
Sls Amt 1W Ago |
N |
Net Sls Amount/Revenue 1 week ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_AMT_LAST_WEEK |
|
Sls Amt 2W Ago |
N |
Net Sls Amount/Revenue 2 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_AMT_2_AGO |
|
Sls Amt 3W Ago |
N |
Net Sls Amount/Revenue 3 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_AMT_3_AGO |
|
Sls Amt 4W Ago |
N |
Net Sls Amount/Revenue 4 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.SLS_AMT_4_AGO |
|
GM T/D |
N |
Net Gross Margin till last loaded week. |
PRO_RUN_RECOM_DENORMALIZE.CUM_TOT_GMARGING |
|
GM 1W Ago |
N |
Net Gross Margin 1 week ago. |
PRO_RUN_RECOM_DENORMALIZE.GMARGING_LAST_WEEK |
|
GM 2W Ago |
N |
Net Gross Margin 2 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.GMARGING_AMT_2_AGO |
|
GM 3W Ago |
N |
Net Gross Margin 3 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.GMARGING_AMT_3_AGO |
|
GM 4W Ago |
N |
Net Gross Margin 4 weeks ago. |
PRO_RUN_RECOM_DENORMALIZE.GMARGING_AMT_4_AGO |
|
Target ST EOL |
N |
Business rule - percentage of sell-through for each product at end of clearance season. |
PRO_RUN_RECOM_OPT_RESULT.TARGET_ST_PCT |
|
ProdAttrValue1 - Prod AttrValue 5 |
N |
Product attributes selected by the user in the user filter. For example, Brand. |
RSE_PROD_ATTR |
|
Currency Attribute 1 - 30 |
Y |
User Defined Attribute of Currency Type. |
Flex Fact (W_RTL_FLEX_FACT) |
|
Date Attribute 1 - 15 |
Y |
User Defined Attribute of Date type. |
Flex Fact (W_RTL_FLEX_FACT) |
|
Number Attribute 1 - 40 |
Y |
User Defined Attribute of Number Type. |
Flex Fact (W_RTL_FLEX_FACT) |
|
Percentage Attribute 1 - 15 |
Y |
User Defined Attribute of percentage Type. |
Flex Fact (W_RTL_FLEX_FACT) |
|
Text Attribute 1 - 30 |
Y |
User Defined Attribute of Text Type. |
Flex Fact (W_RTL_FLEX_FACT) |