| Oracle® Retail Merchandise Financial Planning Operations Guide Release 14.1 E55750-01 |
|
 Previous |
 Next |
| Oracle® Retail Merchandise Financial Planning Operations Guide Release 14.1 E55750-01 |
|
Previous |
Next |
This chapter describes the overall MFP data flow and integration with RMS, Oracle Retail Analytics (RA), markdown optimization applications, and data warehouses.
For information about ODI Enabled Integration for the Fashion Planning Bundle, see Chapter 4, "Fashion Planning Bundle and ODI Integration". For information about script integration for the bundle, see Chapter 5, "Script Integration".
Figure 6-1 shows the integration of MFP with other applications and the flow of data between the applications.
These descriptions explain some of the data flows in Figure 6-1. For information about the data flows among MFP, IP, and AP, see Chapter 4, "Fashion Planning Bundle and ODI Integration" and Chapter 5, "Script Integration".
|
Note: The integration between RMS and MFP includes only hierarchy, on order, and inventory data. All other data required by MFP are not part of the RMS/MFP integration. |
Data for the following hierarchies is imported into MFP from RMS:
Product (PROD) hierarchy
Location (LOC) hierarchy
Calendar (CLND) hierarchy
|
Note: Non-stockholding company stores and non-inventory items which are non-merchandise items, consignment, concession and deposit returns are sent from RMS to MFP but are not utilized in MFP. |
Hierarchies are the structures used by an organization to define the relationships that exist between measures of data, products, locations, time, and other dimensions. These dimensions are represented within the Fashion Planning Bundle applications as hierarchies that correspond to an organization's structure, including all roll-ups.
The Product hierarchy provides the parent-child merchandise level relationships that are available within an application. The Location hierarchy provides the parent-child-location level relationships that are available within an application. Application data is presented at an intersection level of the Product, Location, and Calendar hierarchies.
In addition to the hierarchy files, MFP receives on order and inventory from RMS. These files are based at the week/style-color/store level and then aggregated to the planning levels in the MFP domain.
Fore more information about the RMS integration, see RMS to MFP Transformation. For additional details on the RMS/MFP integration from the perspective of RMS, see the RPAS/MFP - RMS Integration chapter of the RMS Operations Guide - Volume 1.
The following data are imported into MFP from a data warehouse application:
Historic sales
Inventory
MFP Retail sends the current plan markdown budget to a markdown optimization application such as Oracle Retail Markdown Optimization.
For more information, see Markdown Optimization Integration.
This section details the process that is required to transform the extracted RMS files to create load ready files for the MFP solution.
The RETLforRpas directory and MFPIntegration directory should be installed under the same directory. Both directory structures should overlap because MFP uses the schema and environment files of the RETLforRpas release. The RETLforRpas is part of the RMS release while the MFPIntegration directory is part of the MFP release.
On some flavors of Unix, you may not have the nawk executable. You may only have the awk executable. In that case a soft link is needed to be created with the name nawk which will point to the awk executable.
RETL should be installed in the machine.
The following environment variables need to be set in the environment:
RMSE_RPAS_HOME points to the RMS release of RPAS integration scripts directory.
export RMSE_RPAS_HOME=$MFP_HOME
The DATA_DIR is the input and output directory. The RMS input files has to be copied to the DATA_DIR directory. After the MFP transformation, the output files also will be stored in the same location. Once the processing is done, the processed input files will be transfer to the processed directory in the DATA_DIR.
RMSE_RPAS_HOME points to the RMS release of RPAS integration scripts directory.
export RMSE_RPAS_HOME=$MFP_HOME
As the RMS environment scripts use the RDF_HOME variable internally, this needs to be set.
export RDF_HOME=$MFP_HOME
The RMS Schema directory
export RMS_RPAS_SCHEMA_DIR=$RMSE_RPAS_HOME/rfx/schema/
The MFP Schema directory
export MFP_SCHEMA_DIR=$MFP_HOME/rfx/schema/
The intermediate data files are stored in the MFP_TEMP_DIR, If the input files are very huge, it is advisable to use a special Temp directory, rather than using the default OS temp directory. By default /tmp is taken as MFP_TEMP_DIR if this variable is not set.
export MFP_TEMP_DIR=$MFP_HOME/data
In addition to the variable described above, the following optional environment variables can be set:
ADD_AT_SIGN_TO_WH_DESC: This variable is used by the mfp_lochier.ksh. In the warehouse records if @ sign needs to be prefixed with warehouse name description field then this should be set to True. By default this feature is disabled. If this feature is required the following command should be executed in the environment.
export ADD_AT_SIGN_TO_WH_DESC=True
PROCESS_WAREHOUSE_DATA: This variable is used by the mfp_lochier.ksh. By default this variable is set to True. If you do not want the mfp_lochier.ksh to process the warehouse data then this variable must be set as False.
export PROCESS_WAREHOUSE_DATA=False
ENABLE_INVENTORY_RETAIL
ENABLE_ON_ORDER_RETAIL
These two variables are used by the mfp_inventory.ksh and mfp_on_order.ksh. The setting of these variables is dependent on the type of MFP being used. If you are using MFP Retail, you need to set these values to true to get the retail version of the inventory and on order files. By default both these variables are set to True. Use the following command in environment to disable them if you are instead using MFP Cost.
export ENABLE_INVENTORY_RETAIL=False
export
ENABLE_ON_ORDER_RETAIL=False
ENABLE_INVENTORY_COST
ENABLE_ON_ORDER_COST
These two variables are used by the mfp_inventory.ksh and mfp_on_order.ksh. The setting of these variables is dependent on the type of MFP being used. If you are using MFP Cost, you need to set these values to true to get the cost version of the inventory and on order files. By default both these variables are set to True. Use the following command in environment to disable them if you are instead using MFP Retail.
export ENABLE_INVENTORY_COST=False
export
ENABLE_ON_ORDER_COST=False
This section outlines the scripts that will be run to transform the RMS files to load into MFP. These scripts reside in the $MFP_HOME/rfx/src directory. As previously stated, the input files and output files are in the $DATA_DIR.
Script name: mfp_calhier.ksh
Description: Transforms exported calendar hierarchy data from RMS to MFP-loadable format.
Input data file(s): rmse_rpas_clndmstr.dat
Output file(s): clnd.csv.dat
Params: None
Script name: mfp_prodhier.ksh
Description: Transforms exported product hierarchy data from RMS to MFP-loadable format.
Input file(s): rmse_rpas_item_master.dat, rmse_rpas_merchhier.dat
Output file(s): prod.csv.dat
Params: None
Script name: mfp_lochier.ksh
Description: Transforms exported location hierarchy data from RMS to MFP-loadable format.
Input data file(s): rmse_rpas_orghier.dat, rmse_rpas_store.dat and rmse_rpas_wh.dat
Output file(s): loc.csv.dat
Params: None
Script name: mfp_inventory.ksh
Description: Transforms the extracted Inventory data from RMS to an MFP-loadable format.
Required input data file: rmse_mfp_inventory.W.dat
Optional Input data file: rmse_mfp_inventory.I.dat
Output file(s):
Params: None
Script name: mfp_on_order.ksh
Description: Transforms the extracted On-order data from RMS into an MFP-loadable format.
Required input data file(s): rmse_mfp_onorder.dat
Output file(s): mfp_inventory_retail.csv.ovr, mfp_inventory_cost.csv.ovr
Params: None
Once the processing is done, the processed input files are transferred to the processed directory in the $DATA_DIR. The output files need to be transferred to the MFP domains input directory in order to load with the standard load scripts. For more about the standard load scripts, see Batch Designs.
The exportToMDO.ksh script is located in the MFP_HOME/bin folder. It is used to create MFP Retail current plan markdown budget extract for exports to markdown optimization applications such as Oracle Retail Markdown Optimization (MDO).
The script takes three arguments:
The location for the destination directory.
The RPAS name for a dimension along the location hierarchy. All location dimensions above or at the lowest stored dimension, channel (CHNL), are valid. You must provide a valid dimension.
The RPAS name for a dimension along the product hierarchy. All product dimensions above or at the lowest stored dimension, subclass (SCLS), are valid. You must provide a valid dimension.
The script generates the total value of current approved markdowns. This is the sum of the promotional, permanent, and clearance markdowns at the base intersection. This is derived from the product and location dimensions that you supply.
|
Note: The script assumes the calendar dimension to be month. |
The markdowns, along with position names along the dimensions, are written to the ASH_BUDGET_TBL.dat file in the destination directory that you provide. It is a pipe delimited file that contains the following information:
Product key
Location key
Fiscal year
Fiscal month
Total markdown
The product key (also known as the merchandise key) and location key are the position names for the product and location. The year is a four digit year. The month is an index of the fiscal month. For instance, if you start your fiscal year on February 1, the calendar month of December 2011 would be 2011|11.
Oracle Retail Analytics is a business intelligence solution for the retail industry. Retail Analytics offers an integrated view of retail data from various source systems, and it allows users to create analytical reports for areas such as merchandising and marketing, supply chain management, and corporate planning and performance management. Retail Analytics supports as-is, as-was, and point in time analysis methods and reporting.
Retail Analytics extracts data from the source systems and then transforms and loads the data into the Oracle Business Intelligence Repository to support reporting and analysis. MFP is an application from which Retail Analytics extracts data. Retail Analytics uses Oracle Data Integrator (ODI) for extraction, transformation, and loading. ODI programs that extract information from MFP are packaged with the Oracle Retail Analytics application. At the time of Retail Analytics installation, these ODI programs are deployed to the MFP instance. For more information about Oracle Retail Analytics and the ODI extraction programs, see the following guides:
Oracle Retail Analytics Implementation Guide
Oracle Retail Analytics Installation Guide
Oracle Retail Analytics Operations Guide
Before running either of the MFP to RA integration packages for the first time, domain support data must be initialized by running the following commands once per MFP domain:
mfp_integration_setup.ksh -d "{mfpcost-domain-path}"
mfp_integration_setup.ksh -d "{mfpretail-domain-path}"
This script generates mappings between the MFP Week positions and RA Week Number IDs. Similarly, it generates mappings between the MFP Channel positions and RA Channel Number IDs. Therefore, in an MFP domain, if Week positions are added to the Calendar hierarchy or if Channels are added to the Location hierarchy, then the mfp_integration_setup.ksh script must be run again to update the support data.This script is located in the RA media pack.
The following data integration points for each application-to-application package are described in this section:
SDE_MFPCostFact Package (MFP Cost to RA) is described in Data Mappings for SDE_MFPCostFact Package.
SDE_MFPRetailFact Package (MFP Retail to RA) is described in Data Mappings for SDE_MFPRetailFact Package.
The SDE_MFPCostFact package clears the data in the RA staging tables-
W_RTL_MFPCPC_SC_CH_WK_FS and W_RTL_MFPOPC_SC_CH_WK_FS, and then populates data records wherever MFP's "Newly Approved" flag is "True" (mowpappcpnewb for Current Plan and mowpappopnewb for Original Plan).
Table 6-1 MFP Cost to RA Current Plan Cost (CPC) Data
| MFP Cost Expression | W_RTL_MFPCPC_SC_CH_WK_FS Table |
|---|---|
|
{Part of SCLS after last underscore.} |
PROD_SC_NUM |
|
{Part of CLSS after last underscore.} |
PROD_CL_NUM |
|
DEPT |
PROD_DP_NUM |
|
RAWEEKNUM |
MFP_WK_NUM |
|
RACHANNELNUM |
CHANNEL_NUM |
|
BUCPSLSU |
MFPCPC_SLS_QTY |
|
BUCPSLSR |
MFPCPC_SLS_RTL_AMT |
|
BUCPSLSC |
MFPCPC_SLS_COST_AMT |
|
(BUCPSLSR / (1 + BUCPVATP)) * BUCPVATP |
MFPCPC_TAX_RTL_AMT |
|
BUCPSLSR / ( 1 + BUWPVATP ) |
MFPCPC_SLSTE_RTL_AMT |
|
BUCPGMPV |
MFPCPC_PROF_COST_AMT |
|
BUCPBOPC |
MFPCPC_BOH_COST_AMT |
|
BUCPBOPU |
MFPCPC_BOH_QTY |
|
BUCPEOPC |
MFPCPC_EOH_COST_AMT |
|
BUCPEOPU |
MFPCPC_EOH_QTY |
|
BUCPRECC |
MFPCPC_INVRC_COST_AMT |
|
BUCPRECU |
MFPCPC_INVRC_QTY |
|
BUCPCHRINKC |
MFPCPC_SHRINK_COST_AMT |
|
BUCPCHRINKU |
MFPCPC_SHRINK_QTY |
|
BUCPMISCOUTC |
MFPCPC_MISCO_COST_AMT |
|
BUCPMISCOUTU |
MFPCPC_MISCO_QTY |
|
BUCPMISCINC |
MFPCPC_MISCI_COST_AMT |
|
BUCPMISCINU |
MFPCPC_MISCI_QTY |
|
BUCPDEVALC |
MFPCPC_DVAL_COST_AMT |
|
RASOURCENUM |
DATASOURCE_NUM_ID |
|
RACURRENCYCODE |
DOC_CURR_CODE |
|
{Part of SCLS after last underscore.} || '~' || RAWEEKNUM || '~' || RACHANNELNUM |
INTEGRATION_ID |
Table 6-2 MFP Cost to RA Original Plan Cost (OPC) Data
| MFP Cost Expression | W_RTL_MFPOPC_SC_CH_WK_FS Table |
|---|---|
|
{Part of SCLS after last underscore.} |
PROD_SC_NUM |
|
{Part of CLSS after last underscore.} |
PROD_CL_NUM |
|
DEPT |
PROD_DP_NUM |
|
RAWEEKNUM |
MFP_WK_NUM |
|
RACHANNELNUM |
CHANNEL_NUM |
|
BUCPSLSU |
MFPOPC_SLS_QTY |
|
BUCPSLSR |
MFPOPC_SLS_RTL_AMT |
|
BUCPSLSC |
MFPOPC_SLS_COST_AMT |
|
(BUCPSLSR / (1 + BUCPVATP)) * BUCPVATP |
MFPOPC_TAX_RTL_AMT |
|
BUCPSLSR / ( 1 + BUWPVATP ) |
MFPOPC_SLSTE_RTL_AMT |
|
BUCPGMPV |
MFPOPC_PROF_COST_AMT |
|
BUCPBOPC |
MFPOPC_BOH_COST_AMT |
|
BUCPBOPU |
MFPOPC_BOH_QTY |
|
BUCPEOPC |
MFPOPC_EOH_COST_AMT |
|
BUCPEOPU |
MFPOPC_EOH_QTY |
|
BUCPRECC |
MFPOPC_INVRC_COST_AMT |
|
BUCPRECU |
MFPOPC_INVRC_QTY |
|
BUCPCHRINKC |
MFPOPC_SHRINK_COST_AMT |
|
BUCPCHRINKU |
MFPOPC_SHRINK_QTY |
|
BUCPMISCOUTC |
MFPOPC_MISCO_COST_AMT |
|
BUCPMISCOUTU |
MFPOPC_MISCO_QTY |
|
BUCPMISCINC |
MFPOPC_MISCI_COST_AMT |
|
BUCPMISCINU |
MFPOPC_MISCI_QTY |
|
BUCPDEVALC |
MFPOPC_DVAL_COST_AMT |
|
RASOURCENUM |
DATASOURCE_NUM_ID |
|
RACURRENCYCODE |
DOC_CURR_CODE |
|
{Part of SCLS after last underscore.} || '~' || RAWEEKNUM || '~' || RACHANNELNUM |
INTEGRATION_ID |
The SDE_MFPRetailFact package clears the data in the RA staging tables
W_RTL_MFPCPR_SC_CH_WK_FS and W_RTL_MFPOPR_SC_CH_WK_FS, and then populates data records wherever MFP's "Newly Approved" flag is "True" (mowpappcpnewb for Current Plan and mowpappopnewb for Original Plan).
Table 6-3 MFP Retail to RA Current Plan Retail (CPR) Data
| MFP Retail Expression | W_RTL_MFPCPR_SC_CH_WK_FS Table |
|---|---|
|
{Part of SCLS after last underscore.} |
PROD_SC_NUM |
|
{Part of CLSS after last underscore.} |
PROD_CL_NUM |
|
DEPT |
PROD_DP_NUM |
|
RAWEEKNUM |
MFP_WK_NUM |
|
RACHANNELNUM |
CHANNEL_NUM |
|
BUCPSLSREGR |
MFPCPR_SLSRG_RTL_AMT |
|
BUCPSLSPROR |
MFPCPR_SLSPR_RTL_AMT |
|
BUCPSLSCLRR |
MFPCPR_SLSCL_RTL_AMT |
|
BUCPSLSREGU |
MFPCPR_SLSRG_QTY |
|
BUCPSLSPROU |
MFPCPR_SLSPR_QTY |
|
BUCPSLSCLRU |
MFPCPR_SLSCL_QTY |
|
BUCPVATR |
MFPCPR_TAX_RTL_AMT |
|
(BUCPSLSREGR + BUCPSLSCLRR + BUCPSLSPROR) / (1 + BUWPVATP) |
MFPCPR_SLSTE_RTL_AMT |
|
BUCPIGMPV |
MFPCPR_MARGIN_RTL_AMT |
|
BUCPICOGSC |
MFPCPR_COGS_COST_AMT |
|
BUCPMKDPERMR |
MFPCPR_MKDNPM_RTL_AMT |
|
BUCPMKDPROMOR |
MFPCPR_MKDNPR_RTL_AMT |
|
BUCPMKDCLRR |
MFPCPR_MKDNCL_RTL_AMT |
|
BUCPMKUPR |
MFPCPR_MKUP_RTL_AMT |
|
BUCPBOPC |
MFPCPR_BOH_COST_AMT |
|
BUCPBOPR |
MFPCPR_BOH_RTL_AMT |
|
BUCPBOPU |
MFPCPR_BOH_QTY |
|
BUCPEOPC |
MFPCPR_EOH_COST_AMT |
|
BUCPEOPR |
MFPCPR_EOH_RTL_AMT |
|
BUCPEOPU |
MFPCPR_EOH_QTY |
|
BUCPRECC |
MFPCPR_INVRC_COST_AMT |
|
BUCPRECR |
MFPCPR_INVRC_RTL_AMT |
|
BUCPRECU |
MFPCPR_INVRC_QTY |
|
BUCPRHRINKR |
MFPCPR_SHRINK_RTL_AMT |
|
BUCPRHRINKU |
MFPCPR_SHRINK_QTY |
|
BUCPMISCOUTR |
MFPCPR_MISCO_RTL_AMT |
|
BUCPMISCOUTU |
MFPCPR_MISCO_QTY |
|
BUCPMISCINR |
MFPCPR_MISCI_RTL_AMT |
|
BUCPMISCINU |
MFPCPR_MISCI_QTY |
|
RASOURCENUM |
DATASOURCE_NUM_ID |
|
RACURRENCYCODE |
DOC_CURR_CODE |
|
{Part of SCLS after last underscore.} || '~' || RAWEEKNUM || '~' || RACHANNELNUM |
INTEGRATION_ID |
Table 6-4 MFP Retail to RA Original Plan Retail (OPR) Data
| MFP Retail Expression | W_RTL_MFPOPR_SC_CH_WK_FS Table |
|---|---|
|
{Part of SCLS after last underscore.} |
PROD_SC_NUM |
|
{Part of CLSS after last underscore.} |
PROD_CL_NUM |
|
DEPT |
PROD_DP_NUM |
|
RAWEEKNUM |
MFP_WK_NUM |
|
RACHANNELNUM |
CHANNEL_NUM |
|
BUCPSLSREGR |
MFPOPR_SLSRG_RTL_AMT |
|
BUCPSLSPROR |
MFPOPR_SLSPR_RTL_AMT |
|
BUCPSLSCLRR |
MFPOPR_SLSCL_RTL_AMT |
|
BUCPSLSREGU |
MFPOPR_SLSRG_QTY |
|
BUCPSLSPROU |
MFPOPR_SLSPR_QTY |
|
BUCPSLSCLRU |
MFPOPR_SLSCL_QTY |
|
BUCPVATR |
MFPOPR_TAX_RTL_AMT |
|
(BUCPSLSREGR + BUCPSLSCLRR + BUCPSLSPROR) / (1 + BUWPVATP) |
MFPOPR_SLSTE_RTL_AMT |
|
BUCPIGMPV |
MFPOPR_MARGIN_RTL_AMT |
|
BUCPICOGSC |
MFPOPR_COGS_COST_AMT |
|
BUCPMKDPERMR |
MFPOPR_MKDNPM_RTL_AMT |
|
BUCPMKDPROMOR |
MFPOPR_MKDNPR_RTL_AMT |
|
BUCPMKDCLRR |
MFPOPR_MKDNCL_RTL_AMT |
|
BUCPMKUPR |
MFPOPR_MKUP_RTL_AMT |
|
BUCPBOPC |
MFPOPR_BOH_COST_AMT |
|
BUCPBOPR |
MFPOPR_BOH_RTL_AMT |
|
BUCPBOPU |
MFPOPR_BOH_QTY |
|
BUCPEOPC |
MFPOPR_EOH_COST_AMT |
|
BUCPEOPR |
MFPOPR_EOH_RTL_AMT |
|
BUCPEOPU |
MFPOPR_EOH_QTY |
|
BUCPRECC |
MFPOPR_INVRC_COST_AMT |
|
BUCPRECR |
MFPOPR_INVRC_RTL_AMT |
|
BUCPRECU |
MFPOPR_INVRC_QTY |
|
BUCPRHRINKR |
MFPOPR_SHRINK_RTL_AMT |
|
BUCPRHRINKU |
MFPOPR_SHRINK_QTY |
|
BUCPMISCOUTR |
MFPOPR_MISCO_RTL_AMT |
|
BUCPMISCOUTU |
MFPOPR_MISCO_QTY |
|
BUCPMISCINR |
MFPOPR_MISCI_RTL_AMT |
|
BUCPMISCINU |
MFPOPR_MISCI_QTY |
|
RASOURCENUM |
DATASOURCE_NUM_ID |
|
RACURRENCYCODE |
DOC_CURR_CODE |
|
{Part of SCLS after last underscore.} || '~' || RAWEEKNUM || '~' || RACHANNELNUM |
INTEGRATION_ID |
These configuration instructions are relevant only for the integration between Retail Analytics and Merchandise Financial Planning (MFP). F or more information about Retail Analytics and its use of ODI, see the Oracle Retail Analytics documentation set.
Customers who use RPAS JDBC with Oracle Data Integrator (ODI) must perform the following steps:
Install the RPAS JDBC drivers on the ODI server:
Extract the file jdbcclient.tar.gz to create the jdbcclient directory:
cd "$RPAS_HOME" unzip -q jdbcclient.tar.zip tar xf jdbcclient.tar
Copy the following four files to the ODI server under the directory $ODI_HOME/drivers:
jdbcclient/driver/lib/ORjc.jar jdbcclient/driver/lib/ORssl14.jar jdbcclient/driver/lib/iaik_jce_full.jar jdbcclient/spy/lib/ORy.jar
If the jdbcclient directory is on the same machine as the ODI server, execute the following commands:
cd "$RPAS_HOME/jdbcclient/driver/lib" cp -p ORjc.jar ORssl14.jar iaik_jce_full.jar "$ODI_HOME/drivers" cp -p ../../spy/lib/ORy.jar "$ODI_HOME/drivers"
From $ODI_HOME/bin directory, stop and restart the ODI agent.
agentstop.sh -port=xxxx agent.sh -port=1055 -name=<agent_name>
|
Note: The startup scripts are set to automatically add all *.jar files in the ODI_HOME/drivers directory to the agent CLASSPATH. |
Retrieve the TECH_RPASJDBC.xml file from $RPAS_HOME/ODI directory and import the file to ODI work repository. For importing instructions, see the Oracle Retail Analytics Installation Guide.
POView is designed around Database views which depend on various tables in RMS. The application pulls Open and Pending Purchase Orders from RMS. This section covers the following topics:
To install and set up the Fusion Client for MFP to use POView:
Install Oracle Retail Extract, Transform, and Load (RETL) for RPAS.
Transform the hierarchy, on-order, and inventory extracts from RMS to MFP readable format.
There are separate scripts, each for product hierarchy, calendar, location, inventory data, and on-order data. The scripts are available in the MFP CD under
MFP_HOME/rfx/src. For example, after running mfp_calhier.ksh, the
rmse_rpas_clndmstr.dat file is transformed into MFP readable format in the clnd.csv.dat file.
Build the MFP Cost and Retail domains with the transformed hierarchy files.
Load the hierarchy files into the MFP Cost and Retail domains using the loadHier command.
Load On-order and Inventory files into MFP Cost and Retail by running the loadmeasure utility.
Install Fusion Client using the installer and connect to the MFP domain. Detailed steps for installing Fusion Client are available in the Oracle Retail Predictive Application Server Installation Guide.
Install POView using the installer. The database objects needed for POView will be created during installation or manually. In case the datasource is not setup/available, users can manually create the objects post install. Detailed steps for installing POView are available in the Oracle Retail Predictive Application Server Installation Guide. See the Installing Solution Plug-ins section.
Reinstall Fusion Client with Solution Plugin set to POView.
For more details on setup and configuration of POView, refer to the Oracle Retail Predictive Application Server Configuration Tools User Guide or Oracle Retail Predictive Application Server Installation Guide.
The following figure shows the interaction between RMS and POView.
The following table shows the list of RMS tables used by POView:
Table 6-5 Tables used by POView
|
DEPS |
|
CLASS |
|
SUBCLASS |
|
ITEM_MASTER |
|
COMPHEAD |
|
CHAIN |
|
STORE |
|
WH |
|
STORE_HIERARCHY |
|
CHANNELS |
|
ORDHEAD |
|
ORDLOC |
The following Database Objects need to be created in RMS:
|
Note: RMS uses a relational database, hence the SQL statements. |
Example 6-1 SQL to create Custom Data Types
CREATE OR REPLACE TYPE POVIEW_DEPARTMENTIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_CLASS1IDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_SUBCLASSIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_CHAINIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_CHANNELIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_AREAIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_DISTRICTIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_REGIONIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_LOCATIONIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_SUBCLASSCLASS1IDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_SUBCLASSDEPARTMENTIDS AS TABLE OF NUMBER(10); CREATE OR REPLACE TYPE POVIEW_CLASS1DEPARTMENTIDS AS TABLE OF NUMBER(10);
|
Note: When a user launches PO View, they have selected a list of departments, classes, channels, etc. The corresponding IDs are temporarily held in the placeholders created by the above SQL. This information is then used to populate the views below that are used to hold the information required for the PO View dialog box. |
Example 6-2 SQL for POVIEW_ITEM_MASTER_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_ITEM_MASTER_VIEW" ("ITEM", "ITEM_DESC",
"DEPT", "DEPT_NAME", "CLASS", "CLASS_NAME", "SUBCLASS", "SUB_NAME", "STAND-ARD_
UOM") AS
Select Im.Item,im.item_desc, Im.Dept,Dp.Dept_Name, Im.Class,Cl.Class_Name,
Im.Subclass,Sc.Sub_Name, Im.Standard_Uom
From Item_Master Im, Deps Dp, Class Cl, Subclass Sc
Where Im.Dept = Dp.Dept And Im.Dept = cl.dept and IM.CLASS = cl.class and
Im.Dept = sc.dept and im.class = sc.class and
im.subclass = sc.subclass;
Example 6-3 SQL for POVIEW_ORDHEAD_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_ORDHEAD_VIEW" ("NOT_AFTER_DATE", "NOT_BEFORE_
DATE", "OTB_EOW_DATE", "EARLIEST_SHIP_DATE", "LATEST_SHIP_DATE", "COMMENT_DESC",
"SUPPLIER", "CURRENCY_CODE", "ORDER_NO", "STATUS") AS
Select Oh.Not_After_Date, Oh.Not_Before_Date, Oh.Otb_Eow_Date,
Oh.Earliest_Ship_Date, Oh.Latest_Ship_Date, Oh.Comment_Desc, Oh.Supplier,
oh.currency_code, oh.order_no, oh.status from ordhead oh;
Example 6-4 SQL for POVIEW_ORDLOC_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_ORDLOC_VIEW" ("ORDER_NO", "QTY_ORDERED",
"QTY_RECEIVED", "QTY_CANCELLED", "ESTIMATED_INSTOCK_DATE", "ITEM", "LOCATION",
"LOC_TYPE", "UNIT_COST", "UNIT_RETAIL") AS
Select Ol.Order_No, Ol.Qty_Ordered, Ol.Qty_Received, Ol.Qty_Cancelled,
Ol.Estimated_Instock_Date, Ol.Item, Ol.Location, Ol.Loc_Type,
ol.unit_cost, ol.unit_retail from ordloc ol;
Example 6-5 SQL for POVIEW_STORE_HIERARCHY_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_STORE_HIERARCHY_VIEW" ("COMPANY", "CHAIN",
"AREA", "DISTRICT", "REGION", "STORE") AS
Select SH.COMPANY, Sh.Chain, Sh.Area, Sh.District, sh.region, SH.STORE from
STORE_HIERARCHY SH;
Example 6-6 SQL for POVIEW_STORE_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_STORE_VIEW" ("STORE", "CHANNEL_ID",
"CURRENCY_CODE", "STORE_NAME10") AS
Select S.STORE, S.CHANNEL_ID, S.CURRENCY_CODE, S.STORE_NAME10 From STORE S;
Example 6-7 SQL for POVIEW_SUPS_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_SUPS_VIEW" ("SUPPLIER", "SUP_NAME") AS
Select sp.supplier, sp.sup_name from sups sp;
Example 6-8 SQL for POVIEW_WH_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_WH_VIEW" ("WH", "WH_NAME", "CHANNEL_ID",
"ORG_HIER_TYPE", "ORG_HIER_VALUE", "CURRENCY_CODE") AS
Select wh.wh, wh.WH_NAME, WH.CHANNEL_ID, WH.ORG_HIER_TYPE, WH.ORG_HIER_VALUE,
WH.CURRENCY_CODE from wh wh;
Example 6-9 SQL for POVIEW_AREA_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_AREA_VIEW" ("AREA_NAME", "AREA") AS
Select Area_Name, Area From Area;
Example 6-10 SQL for POVIEW_CHAIN_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_CHAIN_VIEW" ("CHAIN_NAME", "CHAIN") AS
Select Chain_Name, Chain From CHAIN;
Example 6-11 SQL for POVIEW_CHANNELS_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_CHANNELS_VIEW" ("CHANNEL_NAME", "CHANNEL_ID")
AS
Select Channel_Name, Channel_Id From Channels;
Example 6-12 SQL for POVIEW_COMPHEAD_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_COMPHEAD_VIEW" ("CO_NAME", "COMPANY") AS
Select Co_Name, Company From Comphead;
Example 6-13 SQL for POVIEW_DEPS_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_DEPS_VIEW" ("DEPT_NAME", "DEPT") AS
Select Dept_Name, Dept From Deps;
Example 6-14 SQL for POVIEW_DISTRICT_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_DISTRICT_VIEW" ("DISTRICT_NAME", "DISTRICT")
AS
Select District_Name, District From District;
Example 6-15 SQL for POVIEW_REGION_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_DISTRICT_VIEW" ("DISTRICT_NAME", "DISTRICT")
AS
Select District_Name, District From District;
Example 6-16 SQL for POVIEW_CLASS_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_CLASS_VIEW" ("CLASS_NAME", "CLASS", "DEPT")
AS
Select Class_Name, Class, Dept From Class;
Example 6-17 SQL for POVIEW_SUBCLASS_VIEW
CREATE OR REPLACE FORCE VIEW "POVIEW_SUBCLASS_VIEW" ("SUB_NAME", "SUBCLASS",
"DEPT", "CLASS") AS
Select Sub_Name, Subclass, Dept, Class From Subclass;
|
Note: The above set of views (6 - 2 to 6 - 17) collect the building blocks of the data required to populate the PO View dialog box. The view below (6 - 18) collates all the data together into the form that will be displayed in the PO View dialog box. |
Example 6-18 SQL for POVIEW
CREATE OR REPLACE FORCE VIEW "POVIEW" ("ORDER_NO", "NOT_AFTER_DATE", "NOT_BEFORE_
DATE", "OTB_EOW_DATE", "EARLIEST_SHIP_DATE", "LATEST_SHIP_DATE", "QTY_ORDERED",
"QTY_RECEIVED", "QTY_CANCELLED", "ESTIMATED_INSTOCK_DATE", "COMMENT_DESC",
"ITEM", "LOC_TYPE", "SUPPLIER", "SUP_NAME", "ORDER_COST_CURRENCY_CODE", "UNIT_
COST", "UNIT_RETAIL", "ITEM_DESC", "DEPARTMENT", "DEPT_NAME", "CLASS1", "CLASS_
NAME", "SUBCLASS", "SUB_NAME", "STANDARD_UOM", "LOCATION", "LOC_NAME", "COMPANY",
"CHAIN", "AREA", "DISTRICT", "REGION", "CHANNEL", "ORG_HIER_TYPE", "ORG_HIER_
VALUE", "RETAIL_CURRENCY_CODE") AS
SELECT OL.ORDER_NO,
OH.NOT_AFTER_DATE,
OH.NOT_BEFORE_DATE,
OH.OTB_EOW_DATE,
OH.EARLIEST_SHIP_DATE,
OH.LATEST_SHIP_DATE,
OL.QTY_ORDERED,
OL.QTY_RECEIVED,
OL.QTY_CANCELLED,
OL.ESTIMATED_INSTOCK_DATE,
OH.COMMENT_DESC,
OL.ITEM,
OL.LOC_TYPE,
OH.SUPPLIER,
SP.SUP_NAME,
OH.CURRENCY_CODE ORDER_COST_CURRENCY_CODE,
OL.UNIT_COST,
Ol.Unit_Retail,
im.item_desc,
Im.Dept Department,
IM.DEPT_NAME,
Im.Class Class1,
IM.CLASS_NAME,
Im.Subclass,
IM.SUB_NAME,
IM.STANDARD_UOM,
S.STORE LOCATION,
S.STORE_NAME10 LOC_NAME,
SH.COMPANY COMPANY,
Sh.Chain Chain,
Sh.Area Area,
Sh.District District,
sh.region region,
S.CHANNEL_ID Channel,
NULL ORG_HIER_TYPE,
NULL ORG_HIER_VALUE,
S.CURRENCY_CODE RETAIL_CURRENCY_CODE
FROM Poview_Ordloc_View OL,
Poview_Ordhead_View OH,
Poview_Sups_View SP,
Poview_Item_Master_View IM,
Poview_Store_View S,
Poview_Store_Hierarchy_View SH
WHERE OH.STATUS = 'A'
AND OL.QTY_ORDERED - (OL.QTY_RECEIVED + NVL(OL.QTY_CANCELLED, 0)) > 0
AND OL.LOC_TYPE = 'S'
AND OL.ORDER_NO = OH.ORDER_NO
AND OH.SUPPLIER = SP.SUPPLIER
AND OL.ITEM = IM.ITEM
AND OL.LOCATION = S.STORE
AND S.STORE = SH.STORE
UNION ALL
SELECT OL.ORDER_NO,
OH.NOT_AFTER_DATE,
OH.NOT_BEFORE_DATE,
OH.OTB_EOW_DATE,
OH.EARLIEST_SHIP_DATE,
OH.LATEST_SHIP_DATE,
OL.QTY_ORDERED,
OL.QTY_RECEIVED,
OL.QTY_CANCELLED,
OL.ESTIMATED_INSTOCK_DATE,
OH.COMMENT_DESC,
OL.ITEM,
OL.LOC_TYPE,
OH.SUPPLIER,
SP.SUP_NAME,
OH.CURRENCY_CODE ORDER_COST_CURRENCY_CODE,
OL.UNIT_COST,
Ol.Unit_Retail,
im.item_desc,
Im.Dept Department,
IM.DEPT_NAME,
Im.Class Class1,
IM.CLASS_NAME,
Im.Subclass,
IM.SUB_NAME,
IM.STANDARD_UOM,
WH.WH LOCATION,
WH.WH_NAME LOC_NAME,
NULL COMPANY,
Null Chain,
Null Area,
Null District,
Null region,
WH.CHANNEL_ID Channel,
WH.ORG_HIER_TYPE ORG_HIER_TYPE,
WH.ORG_HIER_VALUE ORG_HIER_VALUE,
WH.CURRENCY_CODE RETAIL_CURRENCY_CODE
FROM Poview_Ordloc_View OL,
Poview_Ordhead_View OH,
Poview_Sups_View SP,
Poview_Item_Master_View IM,
Poview_Wh_View WH
WHERE OH.STATUS = 'A'
AND OL.QTY_ORDERED - (OL.QTY_RECEIVED + NVL(OL.QTY_CANCELLED, 0)) > 0
AND OL.LOC_TYPE = 'W'
AND OL.ORDER_NO = OH.ORDER_NO
AND OH.SUPPLIER = SP.SUPPLIER
And Ol.Item = Im.Item
And Ol.Location = Wh.Wh;
