| Oracle® Retail Assortment Planning Implementation Guide Release 14.1.1 E62994-01 |
|
 Previous |
 Next |
| Oracle® Retail Assortment Planning Implementation Guide Release 14.1.1 E62994-01 |
|
Previous |
Next |
This chapter describes the script integration of Assortment Planning with the Fashion Planning Bundle applications.
Integration scripts are used for moving data between applications. The following rules apply to integration scripts:
The exportMeasure utility is used to export data in comma-separated values (CSV) format. This maintains the consistency of start and width attributes across different applications.
Data exported from a source application is placed in the destination domain input directory.
Measure names should not be hard-coded. The names are included in a text file named <source application>_<destination application>_transferList.txt. For example, AP_IP_transferList.txt includes the measure names exported from AP to IP.
Each transfer file includes three columns that correspond to the following data:
Source measure name
Destination measure name
Measure base intersection
When the transfer file is used for a load script, it contains the name of the measure to be loaded. The file names correspond to the measure names.
Do not hard code domain paths. The paths are entered as command-line arguments.
The Batch Script Architecture (BSA) is utilized to provide enterprise-ready architecture for parallel process control, log consolidation, and dependency checks. See the Oracle Retail Batch Script Architecture Implementation Guide.
Table 6-1 lists the integration scripts for AP.
Table 6-1 Integration Scripts
| Application | Script Name |
|---|---|
|
MFP/DW |
ap_load_measures.ksh |
|
IP |
exportToIP_noODI.ksh |
|
Allocation |
exportToAlloc.ksh |
|
SPO |
importfromSPO.ksh, exportToPPK.ksh |
|
RMS |
AP_SIB_WS.sh, AP_SIB_TORMS.sh, ap_map_rms_atv_ids.ksh, |
For information on these scripts, see "Batch Environment Scripts" in Chapter 7.
This script loads the data for the measures listed in the base_importlist.txt file. The list of measures captures all the data expected to be loaded to AP from other applications. During implementation, this list should be divided into files that should be loaded daily, weekly, monthly, yearly, and so on.
Script
ap_load_measures.ksh
importdata.ksh
Usage
importdata.ksh <measurelist>
Table 6-2 Load Measure Data
| Argument | Description | Notes |
|---|---|---|
|
measurelist |
Sets the location of the file that contains the list of measures to be loaded. |
By default, these files are provided with the package in the |
Example
importdata.ksh base_importlist.txt
Notes
ap_load_measures.ksh is a wrapper script to call the importdata.ksh to load the data listed in the base_importlist.txt file.
This script uses the RPAS loadmeasure utility. See the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client for details on this utility.
The script ignores any missing or empty measure load files.
All measure files are placed into the domain's input folder. Master domain input directory if the domain structure is Global domain.
All errors and messages are processed by BSA. Refer to the BSA log files for information.
importdata.ksh can be utilized to set up the daily, weekly, monthly, and yearly data load process.
This script exports the AP data required for IP. AP exports the data in flat file format which can then be imported directly by IP using the standard RPAS loadmeasure utility.
Script
exportToIP_noODI.ksh
Usage
exportToIP_noODI.ksh <maxprocesses> <destination> <measurelist>
Table 6-3 Export to IP Script Arguments
| Argument | Description | Notes |
|---|---|---|
|
maxprocesses |
Sets the maximum number of export processes to run in parallel. |
The default is 1. |
|
destination |
Sets the path to the input folder of the destination domain where the exported files are placed. |
The export script creates the CSV file containing the exported measure data in the input folder of the destination domain. |
|
measurelist |
Sets the location of the file which contains the list of measures to be exported. |
By default, this file is provided with the package in the |
Example
exportToIP_noODI.ksh 4 /{domainpath}/itemplan AP_IP_transferList.txt
Notes
This script is optional and should be used only if exports to IP are required.
The script uses the RPAS exportData utility to export measure data from the domain. It also uses the RPAS mace utility to preprocess the AP data for the IP export. See the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client for details on these utilities.
By default, the script generates the output CSV file with a .rpl extension. This can be changed to other file extension types depending on the destination domain's requirement for this data import. See the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client for the data file extension types.
This script calls a rule group called AP_to_IP. This rule group preprocesses the AP data for the IP export. See Chapter 7 for more details about this rule group.
This script exports the AP Receipt Units measure for use by Allocation. AP exports the data in a flat file formatted for use within Allocation. Allocation then uses Oracle Retail Extract Transform and Load (RETL) to extract this information.
Script
exportToAlloc.ksh
Usage
exportToAlloc.ksh <domain> <excluded diff>
Table 6-4 Export to Allocation Script Arguments
| Argument | Description | Values Allowed |
|---|---|---|
|
domain |
Sets the path to the current domain. |
-d {current domain}. Master domain in Global domain environment. |
|
excluded diff |
Sets the no color differentiator for the output file. |
-e {excluded diff} Default value: None |
Export File Naming Convention
The output file name starts with lower-case p and follows a standard naming convention:
p<diff count><product level><domain ID> where
<diff count> represents the number of differentiators included in the output. Only a single differentiator which is color is exported. Hence this value is always 1; 0 if -e exclude differentiator is not used.
<product level> is a four-character sequence representing the product level at which the export is prepared. The valid values are itpt (item), scls (subclass), clss (class), and dept (department).
<domain ID> is a constant with a value 01.
Example
Products: item1, item2, item3, item4
Diff/Color: Red, Blue, Purple, Blue
Receipt U: 10, 11, 12, 13, 14
When executed with "exportToAlloc.ksh -d {domain} -e Blue" would result in 2 outputs:
p0itpt.01 file containing: item1 Red 10, item3 Purple 13
p1itpt.01 file containing: item2 Blue 11, item4 Blue 14
Export File Format
This script loads the data from Oracle Retail Size Profile Optimization (SPO). Measure data files from SPO need to be placed in the <domain>/input directory. This script calls importdata.ksh with a list of measure names defined in SPO_AP_transferList.txt.
Script
importFromSPO.ksh
Usage
None
Import List of File Names
Table 6-6 Import from SPO
| SPO export file name | AP import file name |
|---|---|
|
sztyarchspl1up |
sztyarchspl1p |
|
sztyarchspl2up |
sztyarchsp2p |
|
sztyarchspl3up |
sztyarchspl3p |
|
sztyarchspl4up |
sztyarchspl3p |
|
eptpackdef |
szwpeptpackdefu |
Notes
This script uses the RPAS loadmeasure utility through importdata.ksh. See the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client for details on this utility.
This script renames the SPO file names to AP file names that are expected by the measure defined in SPO_AP_transferList.txt. Measure name corresponds to file name expected during the import process.
Size profiles and prepack definition data can be entered manually through the Size & Pack Profile Maintenance workbook in AP.
This script is used to send input data to the Prepack Optimization module in SPO. This is not to be confused with the pack optimization in AP which is used to optimize receipt drops into predefined prepacks and eaches.
Script
exportToPPK.ksh
Usage
exportToPPK.ksh <maxprocesses> <destination> <measurelist>
Table 6-7 Export to SPO Arguments
| Argument | Description | Notes |
|---|---|---|
|
maxprocesses |
Sets the maximum number of export processes to run in parallel. |
The default is 1. |
|
destination |
Sets the path to the input folder of the destination domain where the exported file is placed. |
The export script creates the file containing the exported measures in the input folder of the destination domain. |
|
measurelist |
Sets the location of the file which contains the list of measures to be exported. |
By default, these files are provided with the package in the |
Notes
This script is optional and should be used only if export to SPO (Prepack Optimization module) is required.
The Prepack Optimization module is available as a plug-in which is licensed with SPO. Licensed customers can configure the plug-in with AP.
The script uses the RPAS exportData utility to export measure data from the domain. It also uses the RPAS mace utility to preprocess the AP data for the IP export. See the Oracle Retail Predictive Application Server Administration Guide for the Fusion Client for details on these utilities.
Slow item build is a process which enables the interaction of RMS and AP concerning the Dynamic Position Management (DPM) of new style-colors and styles created in AP. This is a four step process:
The user creates DPM styles or style-colors in the AP workbook. An AP batch process counts the number of DPM style/style-colors and sends a request to RMS to reserve item numbers for the new style/style-colors through a web service call. The web service call returns the reserved item numbers. AP renames the DPM positions according to the returned item numbers. This batch process is implemented in the AP_SIB_WS.sh shell script. For more information, see "AP Slow Item Build Web Service."
The attribute information for the new style/style-colors is extracted from the AP domain and uploaded into the RMS staging tables. This process is implemented in the AP_SIB_TORMS.sh shell script. For more information, see "AP Slow Item Build to RMS."
The RMS user decides which new style/style-colors are to be formalized. A list of style/style-color names is delivered from RMS to AP. The RPAS informalPositionManager utility can be used to formalize the style/style-color positions.
In addition, there are a number of scripts supplied to facilitate the translation and storage of RMS UDA_Value IDs to the corresponding attribute values in AP. For information on these scripts, see "Miscellaneous Scripts."
This script performs the following:
Check all the new informal style and style-color positions in the domain and reserve their item numbers through the RMS web service.
Using the returned item numbers, rename the new informal positions.
After renaming, flag all the renamed positions as old so that the positions do not get sent again.
Script
AP_SIB_WS.sh
Usage
AP_SIB_WS.sh -d DomainPath -dimName DimensionName -expireDays ExpireDays -wallet WalletPath -wsURL WebServiceURL -sqnURL WebServiceQnameURL [-wsOutFile OutFile]
Table 6-8 AP_SIB_WS.sh Arguments
| Argument | Description |
|---|---|
|
-d |
Domain path |
|
-dimName |
Dimension name (style or style-color) |
|
-expireDays |
Expiration in days. The item numbers reserved in RMS through this web service call are guaranteed to be available until the expiration is reached. |
|
-wallet |
Wallet directory path |
|
-wsURL |
Web service URL |
|
-sqnURL |
Web service qname URL |
|
-wsOutFile |
Optional argument used to store the ItemManagementService returned reserved item numbers |
Note
The user name and password used to access the RMS ItemManagement web service must be stored in a wallet. The wallet can be created with the following commands:
save_credential.sh -a user -u user -p ims -l testwallet save_credential.sh -a password -u password -p ims -l testwallet
The wallet must have the following credentials after it is created. To check the credentials, run the following command:
dump_credentials.sh testwallet
The following is an example of the output from the command:
Application level key partition name:ims User Name Alias:password User Name:password User Name Alias:user User Name:user
This script performs the following:
Extract all informal position names for the style/style-color dimensions.
Export the information for the positions from Step 1 and format into a predefined template. This template is in XML format and contains a relevant subset of fields available in the RMS staging tables.
Archive the template into a file to be consumed by RMS. (The archived XML is saved with a ".ods" extension. This is the format used by Oracle Open Office spreadsheet files, and can be opened and read with Open Office.)
In AP, product attribute values are stored along a one-dimensional hierarchy (POS1) associated with an attribute hierarchy (ATT). This is done to allow flexibility in defining and changing attribute values. For Slow Item Build, these values must be copied into one-dimensional measures (see below for more information.) Many examples of these measures and transforms are found in the Batch/Slow_Item_Build rule group. This rule group must be executed prior to running the AP_SIB_TORMS.sh script in order for values to be communicated to RMS.
Script
AP_SIB_TORMS.sh
Usage
AP_SIB_TORMS.sh -d DomainPath -configFile ConfigFilePath -ODSFile sample.ods -dimName DimensionName [-create]
Table 6-9 AP_SIB_TORMS.sh Arguments
| Argument | Description |
|---|---|
|
-d |
Domain path |
|
-configFile |
Configuration file path |
|
-ODSFile |
sample.ods (empty ods file) |
|
-dimName |
Dimension name of extracted dimensions (style or style-color) |
|
-create |
Optional argument that indicates that only style/style-colors that have never been sent to RMS will be exported:
|
Configuration File
The configuration file is used to specify what data needs to be extracted from the domain, formatted into the spreadsheet, and uploaded to RMS. The script will only export new informal style/style-color positions in the domain. The contents of this configuration file will vary from application to application, depending on what information is available and important to send to RMS. The configuration file format, with an example entry, is as follows:
| Table | Column | Measure | Dimension | Comments |
|---|---|---|---|---|
| ITEM_MASTER | DIFF_1 | BWWPClrAttrValTx | NA | Color |
The configuration file is a text file that can have multiple lines. Each line describes a column. Every line can have five fields separated by a space. The five columns are described below:
Table is the target database table name in RMS in the staging area.
Column is the column name in the database table.
Measure must contain a valid one-dimensional measure name. The measure must be based on the primaryKey dimension, typically style or style-color. It also indicates this column is for measure content export. The measure column cannot be blank.
Dimension contains a valid dimension name that is equal to or above the primaryKey dimension. The dimension column cannot be blank.
Comments can have a value of "Y," "N," or other strings. "Y" means this column is a primary key for the output table. "N" means that the column is not a primary key for the output table. A string not equal to "Y" or "N" means this column is to be filled with the string constants.
Following is an example of a control file:
ITEM_MASTER itemnum NA skup Y ITEM_MASTER subclass NA scls N ITEM_MASTER class NA clss N ITEM_MASTER color APWPColorTX NA N ITEM_MASTER comment NA NA test ATTRIBUTE itemnum NA skup Y ATTRIBUTE User_Defined_ATTR NA NA Silhouette,Vendor ATTRIBUTE VALUE APWPSilhouetteTx,APWPVendorTx NA N
The control file requires the actual RPAS dimension names. In AP, the style-color dimension has a name of "skup" and a label of "Style-Color." Likewise, the style dimension has a name of "skug" and a label of "Style." References to "skup" or "skug" should be read as referring to style-color and style, respectively.
This example configuration file will generate two tables. One table is named ITEM_MASTER, and the other table is named ATTRIBUTE. The ITEM _MASTER table will have five columns:
The line, ITEM_MASTER itemnum NA skup Y, specifies a column named itemnum. The values in the column are skup position names. This column contains the primary key of the ITEM_MASTER table.
The line, ITEM_MASTER itemnum NA skup Y, specifies a column named subclass. The values in the column are corresponding subclass position name per skup.
The line, ITEM_MASTER class NA clss N, specifies a column named class. The values in the column are corresponding class position name per skup.
The line, ITEM_MASTER color APWPColorTX NA N, specifies a column named color. The values in the column are the corresponding color per skup. The color information is stored in the measure APWPColorTx, which must be a one dimensional string measure based on skup.
The attribute table is specified by the following three lines:
ATTRIBUTE itemnum NA skup Y ATTRIBUTE User_Defined_ATTR NA NA Silhouette,Vendor ATTRIBUTE VALUE APWPSilhouetteTx,APWPVendorTx NA N
The first line specifies a column named itemnum. This column contains the skup position names.
The second line specifies a column named User_Defined_ATTR. The column will be filled with the values from the comment field: Silhouette or Vendor.
The third line specifies a column named Value. This column contains the corresponding value per style from either the APWPSilhouetteTx or APWPVendorTx measure. Both of the measures must be one dimensional measures based on style. Multiple measures names can be comma separated to put into the third line to have a Value field that outputs multiple measure contents. The User_Defined_ATTR field must have comma-separated attribute names corresponding to the measure names.
In the above example, each skup position will be exported twice; one for the export of Silhouette and another for the export of Vendor.
For two new informal skup numbers 1000 and 2000, the exported table will look like the following:
ITEM_MASTER table:
| itemnum | subclass | class | color | comment |
|---|---|---|---|---|
| 1000 | 100 | 10 | green | test |
| 2000 | 200 | 20 | yellow | test |
Attribute table:
| itemnum | User_Defined_ATTR | VALUE |
|---|---|---|
| 1000 | Silhouette | A-Line |
| 1000 | Vendor | Kate Spade |
| 2000 | Silhouette | A-line |
| 2000 | Vendor | Kate Spade |
In the nightly batch process of slow item build, this script will be called four times with different options:
AP_SIB_TORMS.sh -d . -dimName skup -configFile create_skup.txt -ODSFile create_skup.ods
AP_SIB_TORMS.sh -d . -dimName skup -configFile update_skup.txt -ODSFile update_skup.ods
AP_SIB_TORMS.sh -d . -dimName skug -configFile create_skug.txt -ODSFile create_skug.ods
AP_SIB_TORMS.sh -d . -dimName skug -configFile update_skug.txt -ODSFile update_skug.ods
In the RMS application, the user_defined_attr and its value must be RMS positionIDs for the file to be successfully loaded. For the integration to work, the previous example of the control file must be modified to the following:
ITEM_MASTER itemnum NA skup Y ITEM_MASTER subclass NA scls N ITEM_MASTER class NA clss N ITEM_MASTER color APWPColorTX NA N ITEM_MASTER comment NA NA test ATTRIBUTE itemnum NA skup Y ATTRIBUTE User_Defined_ATTR NA NA 1,2 ATTRIBUTE VALUE APWPSilhouetteNm,APWPVendorNm NA N
The resulting Attribute table will look like the following:
| itemnum | User_Defined_ATTR | VALUE |
|---|---|---|
| 1000 | 1 | 1 |
| 1000 | 2 | 1 |
| 2000 | 1 | 1 |
| 2000 | 2 | 1 |
There are several scripts included in the Slow Item Build feature that are designed to facilitate the transfer of information from RMS to AP. There are other scripts that are designed to help manage a common set of product attributes.
This script will formalize any informal (DPM) positions found along the Picklist Position (POS1) hierarchy. In AP, attribute values are defined as a list of values associated with a particular attribute. An example could be the attribute "Fabric," with attribute values of "Wool," "Cotton," and "Polyester." These values are defined dynamically in the Assortment Setup workbook. In this case, the attribute (from the ATT hierarchy) is "Fabric," and the options are defined along a list dimension. Occasionally an attribute will have an exceptionally large number of options, exceeding the size of the list dimension. This is accommodated by making the list dimension (ppos) DPM-enabled. This script just formalizes any DPM positions that exist along the ppos dimension.
Script
ap_formalize_ppos.ksh
Usage
ap_formalize_ppos.ksh
Arguments
None
This script reads a file of RMS UDA Values and converts them to the corresponding attribute values in AP. The script does this by matching the string values of the attribute values to the string values of the UDA values within a given attribute/UDA. (So it is possible to have an attribute value for "Floral" in both the "Pattern" and "Fragrance" attributes.)
Script
ap_map_rms_atv_ids.ksh
Usage
ap_map_rms_atv_ids.ksh -r <path to RMS_Attribute_Value_ID_File>
Table 6-10 ap_map_rms_atv_ids.ksh Argument
| Argument | Description |
|---|---|
|
-r |
The path to a file which contains the RMS Attribute Value information. This file is expected to be a comma-separated values text file, with the following format: Attribute ID, Attribute Value ID, Attribute Value Label |
Note
In RMS, these will likely be coming from the UDA tables, but that is not a requirement. This script assumes that the Attribute ID is common between RMS and AP, and that the Attribute Value Labels also match. This script matches on label per attribute ID, and retains the position of the matching value on the ppos dimension in the POS1 hierarchy. It then prepares a load file for the measure ADDYAttrValTx to store the RMS Attribute Value ID, and loads that measure file.
This script is very similar to ap_map_rms_atv_ids.ksh, but instead takes existing attributes defined for a product in RMS and translates those to the corresponding attribute for the corresponding product in AP. It uses the same lookup mechanism to create a measure load file for attributes assigned to a product (SLWPProdAttrTx) and then loads the file.
Script
ap_map_rms_prodattribs.ksh
Usage
ap_map_rms_prodattribs.ksh -r <path to RMS_Product Attribute_File>
Table 6-11 ap_map_rms_prodattribs.ksh Argument
| Argument | Description |
|---|---|
|
-r |
The path to a file which contains the RMS product attribute assignments. This file is expected to be a comma-separated values text file, with the following format: Product ID, Attribute Name ID, RMS Attribute Value ID |
Note
This script assumes the Product ID and Attribute ID are common between RMS and AP. It also assumes that the RMS attribute value IDs are already stored in AP. For each row where this is not the case, a notice is written to the log and the script proceeds to the next line.
This script produces a file that lists all AP attribute values that lack an RMS attribute value ID. This is meant to facilitate setting up the corresponding attributes in RMS. The listing of the unmapped attribute values is written to the domain's output directory.
Script
ap_rms_unmapped_attrib_vals.ksh
Usage
ap_rms_unmapped_attrib_vals.ksh
Arguments
None
