| Oracle® Retail Allocation Operations Guide Release 15.0 E65743-01 |
|
 Previous |
 Next |
The module works in conjunction with the Oracle Retail Extract Transform and Load (RETL) framework. This architecture optimizes a high performance data processing tool that allows database batch processes to take advantage of parallel processing capabilities.
The RETL framework runs and parses through the valid operators composed in XML scripts.
This chapter provides an overview of Oracle Retail Allocation RETL processing and defines the export file from Curve/Plan to Oracle Retail Allocation that is used when exporting Curve/Financial Plan values. For more information on RETL, see the product's Programmer's Guide.
|
Note: In this chapter, some examples refer to RETL programs that are not related to Oracle Retail Allocation. References to these programs are included for illustration purposes only. |
|
Note: The RETL loads into Allocation are point to point integration between Oracle Retail product, and are not designed to support generic uploads from other systems. |
The extracts from RETL may contain up to four levels of plan/profile. They consist of the department level, class level, subclass level and item level. All of these levels are contained in a single normalized file. Each record in the Curve file has a dedicated 'space' and distinct position for department, class, subclass, item, store, diff1, diff2, diff3, diff4 and size profile quantity values and each record in the Plan file has a dedicated 'space' and distinct position for department, class, subclass, item, store, diff1, diff2, diff3, diff4, EOW date and plan quantity values. It is crucial that the records are mapped using the correct positions and space/padding rules for each data value.
Regardless of the level of financial plan/profile, each record must include a store, diff value in one of the four diff value fields and a quantity value (including an EOW date for Plan only).
Department-level financial plan or profiles include a department data value in the dedicated department field. The class, subclass and item fields do not contain any values. They remain empty.
Class-level financial plan or profiles include a department and class data value in the dedicated department and class fields. The subclass and item fields do not contain any values. They remain empty.
Subclass-level financial plan or profiles include a department, class and subclass data value in the dedicated department, class and subclass fields. The item fields do not contain any values. They remain empty.
All of the department, class and subclass record exports contain only the non-aggregate diff values mapped from the specific diff value in ITEM_MASTER to the corresponding diff value in the export file. It is crucial that the non-aggregate diffs are mapped to the correct diff_id in the export file.
Item-level profiles include aggregate level IDs in the dedicated item field. The department, class and subclass fields do not contain any values. They remain empty. The item level export records contains both the aggregate and non-aggregate diff values mapped from the specific diff id in ITEM_MASTER to the associated diff position in the export file.
Item-Level financial plan or profiles include item data value in the dedicated item field. The department, class and subclass fields do not contain any values. They remain empty.
All the data values must start in the beginning of the corresponding field, and padding comes after the data to fill all the dedicated space for that data field.
The diagram below illustrates the extraction processing architecture. The plan/size profile architecture adheres to what is shown in the diagram:
The architecture relies upon two distinct stages, each of which is described in the passages that follow.
Stage 1 involves importing plan/profile data and looking up required information in the RMS ITEM_MASTER table (item-level plans/profiles only). The resulting output from this stage is a temporary table that contains any item-level and department/class/subclass-level plans/profiles.
The detailed flow is as follows:
Insert dept-level plans/profiles directly into the staging table.
Insert class-level plans/profiles directly into the staging table.
Insert subclass-level plans/profiles directly into the staging table.
The item-level plans/profiles require lookups with the ITEM_MASTER table. The processing logic for transaction-level items is to do a three-way left outerjoin on the ITEM_MASTER table to retrieve each parent and grandparent item aggregate indicators. The Item file then lookups its item id in the item master table and uses the STYLE set as the parent or grandparent item id whose ITEM_AGGREGATE_IND = 'Y'. These item level plans/profiles are then inserted into the staging table.
Error Handling
Any item records that do not have a parent and grandparent are flagged as warnings. Any items in the incoming data file that do not match an item in the ITEM_MASTER table are flagged as errors.
Stage 2 involves inserting and updating the plan or profile records into the final destination ALC_PLAN, ALC_RECEIPT_PLAN, or ALC_SIZE_PROFILE table respectively.
The detailed processing is as follows:
Update quantity when matched department, class, subclass, style, store, size1, size2, size3, size4.
Otherwise, insert record.
This section covers configuration.
Before trying to configure and run Oracle Retail Allocation RETL, install RETL version 13.2, which is required to run Oracle Retail Allocation RETL. Run the 'verify_retl' script (included as part of the RETL installation) to ensure that RETL is working properly before proceeding.
Oracle Retail Allocation RETL should be installed and run as the RETL user.
Additionally, the permissions should be set up as per the RETL Programmer's Guide. Oracle Retail Allocation RETL reads, creates, deletes and updates data for tables. If these permissions are not set up properly, processing fails.
See the RETL Programmer's Guide for RETL environment variables that must be set up for your version of RETL. You need to set ALCHOME to your base directory for Oracle Retail Allocation RETL. This is the top level directory that you selected during the installation process (see Oracle Retail Allocation Installation Guide) in your .kshrc, you should add a line such as the following:
export ALCHOME=<base directory path for ALLOCATION RETL>
Execute the setup-security-credential.sh script after the installation from RFX_HOME/bin directory. This script provides the options for adding/updating the database credentials. Enter the values for the following parameters:
dbuseralias
username
A secure wallet file (cwallet.sso) is created under "RFX_HOME/etc/security" directory of RETL installation.
On the Oracle Retail Allocation side, make sure to review the environmental parameters in the alc_config.env file before executing the batch module. Depending upon your local settings, the variables may need to be changed.
Configure Oracle Retail Extract, Transform and Load
Log in to the UNIX server with a UNIX account that runs the RETL scripts.
Change directory to $ALCHOME/rfx/etc.
Modify the alc_config.env script:
Change the DBNAME variable to the name of the Oracle Retail Allocation database. For example:
export DBNAME=int9i
Set the user alias fields such as RETL_WALLET_ALIAS and ORACLE_WALLET_ALIAS.
Set the other variables namely: RFX_HOME, RFX_TMP, TNS_ADMIN, ALCHOME, ORACLE_HOME, JAVA_HOME, PATH, LD_LIBRARY_PATH, SPO_GID_FILENAME.
Update the rfx.conf, vdate.txt files with the DB information and other local settings necessary.
This section covers running the module.
RETL uses a schema file to specify the format of an incoming or outgoing dataset. The schema file defines each column's data type and format, which is then used within RETL to format/handle the data. Schema file names are hard-coded within each module because they do not change on a day-to-day basis. All schema files end with '.schema' and are placed in the 'rfx/schema' directory. For more information about schema files, see the latest RETL Programmer's Guide.
The input data schema file for the Oracle Retail Allocation module is named as alcl_plan.schema for Plan and as alcl_size_profile.schema for profile and is shown later in this chapter.
In contrast to the way in which multi-threading is defined in UNIX, Oracle Retail Allocation uses 'multi-threading' to refer to the running of a single RETL program multiple times on separate groups of data simultaneously. Multi-threading can reduce the total amount of processing time.
For this Oracle Retail Allocation module, multi-threading is mandatory, and the file-based module has to be run once for each input file.
The alcl_pan / alcl_size_profile module requires the following two input parameters:
The uniquely named thread number. Note that the thread number is used internally and is not related to any output file or table name.
The following example illustrates a scenario in which the retailer runs the alcl_size_profile.ksh module three times for three input files:
The load batches, example alcl_size_profile.ksh, loads its data from $ALC_HOME/dataalcl_size_profile.ksh profile_01.dat 1alcl_size_profile.ksh profile_03.dat 3
The transform batches alct_plan and alct_size_profile do not take any input parameters. They execute the flat files in the path $ALC_HOME/data alcl_size_profile.ksh profile_02.dat 2. Running only alct_size_profile.ksh at the prompt executes the transform scripts.
Program Features
The extraction programs are written in the RETL framework and include the following features:
Business virtual date
Program return code
Program status control files
Restart and recovery
Message logging
Program error file
Reject files
The business virtual date must be placed in the vdate.txt file in the $ALCHOME/rfx/etc directory prior to running the RETL module.
RETL programs use one return code to indicate successful completion. If the program successfully runs, a zero (0) is returned. If the program fails, a non-zero is returned.
To prevent a program from running while the same program is already running against the same set of data, the Oracle Retail Allocation RETL code utilizes a program status control file. At the beginning of each module, alc_config.env is run. It checks for the existence of the program status control file. If the file exists, then a message stating, '${PROGRAM_NAME} has already started', is logged and the module exits. If the file does not exist, a program status control file is created and the module executes.
If the module fails at any point, the program status control file is not removed, and the user is responsible for removing the control file before re-running the module.
File Naming Conventions
The naming convention of the program status control file allows a program whose input is a text file to be run multiple times at the same time against different files.
The name and directory of the program status control file is set in the configuration file (alc_config.env). The directory defaults to $ALCHOME/error. The naming convention for the program status control file itself defaults to the following dot separated file name:
The program name
The input filename
'status'
The business virtual date for which the module was run
For example, the program status control file for the alcl_size_profile.ksh program (with an input file name of alcl_size_profile_01.dat specified as the first argument on the command line) would be named as follows for the batch run of January 5, 2001:
$ALCHOME/error/alcl_size_profile.alcl_size_profile_01.dat.status.20010105
The Oracle Retail Allocation RETL module imports data from a flat file, performs transformations if necessary and then loads the data into the applicable Oracle Retail Allocation table.
This module uses a single RETL flow and does not require the use of restart and recovery. If the extraction process fails for any reason, the problem can be fixed, and the entire process can be run from the beginning without the loss of data. For a module that takes a text file as its input, the following two choices are available that enable the module to be re-run from the beginning:
Re-run the module with the entire input file.
Re-run the module with only the records that were not processed successfully the first time.
Message logs are written while the module is running in a format described in this section.
Daily Log File
Every RETL program writes a message to the daily log file when it starts, while it is running, and when it finishes. The name and directory of the daily log file is set in the configuration file (alc_config.env). The directory defaults to $ALCHOME/log. All log files are encoded UTF-8.
The naming convention of the daily log file defaults to the following 'dot' separated file name:
The business virtual date for which the module is run.
'.log'
For example, the location and the name of the log file for the business virtual date of January 5, 2001 would be the following:
$ALCHOME/log/20010105.log
Format
As the following examples illustrate, every message written to a log file has the name of the program, a timestamp, and either an informational or error message:
alct_size_profile 16:06:30: Program started.Number of input files processed = 1. Number of output files produced = 1 alcl_size_profile 13:20:01: Program started for thread 1… alcl_size_profile 13:20:05: Analyzing temp table rmsint1201.alcl_size_profile_temp_1 alcl_size_profile 13:20:13: Merging into alc_size_profile alcl_size_profile 13:20:27: Program completed successfully for thread 1. alct_size_profile 16:06:30: Program completed without errors
If a program finishes unsuccessfully, an error file is usually written that indicates where the problem occurred in the process. There are some error messages written to the log file, such as 'No output file specified', that require no further explanation written to the error file.
In addition to the daily log file, each program also writes its own detail flow and error messages. Rather than clutter the daily log file with these messages, each program writes out its errors to a separate error file unique to each execution.
The name and directory of the program error file is set in the configuration file (alc_config.env). The directory defaults to $ALCHOME/error. All errors and all routine processing messages for a given program and input file on a given day go into this error file (for example, it contains both the stderr and stdout from the call to RETL). All error files are encoded UTF-8.
The naming convention for the program's error file defaults to the following 'dot' separated file name:
The program name
The business virtual date for which the module was run
For example, all errors and detail log information for the alcl_size_profile program would be placed in the following file for the batch run of January 5, 2001:
$ALCHOME/error/alcl_size_profile.alcl_size_profile_01.dat.20010105
The error file for the transform batch contains the name of the files processed with exit status of each file. If any of the file has bad record like width of some field exceeding the maximum allowed, this error file shows the bad records also.
The naming convention for the error file of the transform batch is
Program name_err.business virtual date for which the scripts were run.
For example, alct_size_profile_err.20061005
The Oracle Retail Allocation module may produce a reject file if it encounters data related problems, such as the inability to find data on required lookup tables. A given module tries to process all data and then indicates that records were rejected. All data problems are thus identified in one pass and corrected. The module can then be re-run to successful completion. If a module does reject records, the reject file is not removed. The user is responsible for removing the reject file before re-running the module.
The following examples illustrate typical run and debugging situations for each type of program. The file names referenced in the example below (log, error, and so on) assume that the module is run on the business virtual date of March 9, 2001.
Example for running Transform batch Run alct_size_profile.ksh
Change the directory to $ALC_HOME/src
In the prompt enter,
$alct_size_profile.ksh
If the module runs successfully, the following results,
alct_size_profile.ksh: 16:37:45 Data transform (awk) starting. Input file: /home/pachaia/RPAS12_Agal/data/d1clss.01 alct_size_profile.ksh: 16:37:45 Transform completed. File: d1clss.01 d1clss.01 processing completed. Exit status: 0 alct_size_profile.ksh: 16:37:45 Data transform (awk) starting. Input file: /home/pachaia/RPAS12_Agal/data/d1itpt.01 alct_size_profile.ksh: 16:37:45 Transform completed. File: d1itpt.01 d1itpt.01 processing completed. Exit status: 0 alct_size_profile completed with 1 ERRORS: Thu Oct 5 16:37:45 CDT 2006 If the module does not run successfully, the following results,
****** STARTING alct_size_profile: Thu Oct 5 16:37:45 CDT 2006 ******
****** STARTING alct_size_profile: Thu Oct 5 16:37:45 CDT 2006 ****** alct_size_profile.ksh: 16:37:45 Transform completed. File: d1clss.01 d1clss.01 processing completed. Exit status: 0 alct_size_profile.ksh: 16:37:45 Data transform (awk) starting. Input file: /home/pachaia/RPAS12_Agal/data/d1dept.01 ERROR - too many DIFF IDs in current record of Diff Profile File.
alct_size_profile.ksh: 16:37:45 Data transform (awk) starting. Input file: /home/pachaia/RPAS12_Agal/data/d1clss.01
Diff Profile file name: d1dept.01 Number of Diffs found: 5 Maximum number allowed: 4 DIFF ID string: < _hh1dif_jh2dif_kh3dif_lh4dif _mh5dif 000000006> Bad Record:
Record number 2
1414 1000000001 _hh1dif_jh2dif_kh3dif_lh4dif_mh5dif 000000006000 alct_size_profile.ksh: 16:37:45 Transform completed. File: d1dept.01 d1dept.01 processing completed. Exit status: 2 alct_size_profile.ksh: 16:37:45 Data transform (awk) starting. Input file: /home/pachaia/RPAS12_Agal/data/d1itpt.01 alct_size_profile.ksh: 16:37:45 Transform completed. File: d1itpt.01 d1itpt.01 processing completed. Exit status: 0 alct_size_profile completed with 1 ERRORS: Thu Oct 5 16:37:45 CDT 2006
Run alcl_size_profile.ksh:
Change directory to $ALCHOME/rfx/src.
At a UNIX prompt, enter:
alcl_size_profile.ksh <input datafile 1> <thread #> ...
If the module runs successfully, the following results:
Log file: Today's log file, 20010309.log, contains the messages described above in the 'Format' passage of the 'Daily log file' section.
Data: The ALC_SIZE_PROFILE table exists in the Oracle Retail Allocation database and contains the extract records.
Error File: The program's error file, alcl_size_profile.20010309, contains the standard RETL flow (ending with "All threads complete" and "Flow ran successfully") and no error messages.
Program status control: The program status control file, alcl_size_profile.status.20010309, does not exist.
Reject File: No reject files exist.
If the module does not run successfully, the following results:
Log file: Today's log file, 20010309.log, may not contain the "Program completed successfully…" message.
Data: The ALC_SIZE_PROFILE table exists in the Oracle Retail Allocation database but may not contain all the records from the profile file interface.
Error file: The program's error file, alcl_size_profile.20010309, may contain an error message.
Program status control: The program status control file, alcl_size_profile.status.20010309, may exist.
Reject file: The reject file, items_not_found.dat, may exist in the $ALCHOME/data directory.
Bookmark file: The bookmark file, alcl_size_profile.bkm.20010309, does not exist because this module does not utilize restart and recovery.
Re-run the Module:
Determine and fix the problem causing the error.
Remove the program's status control file.
Remove the reject file (if it exists) from the $ALCHOME/data directory.
Change directory to $ALCHOME/rfx/src. At a UNIX prompt, enter:
% alcl_size_profile.ksh <input datafile 1> <thread #>
This section serves as a reference to the Oracle Retail Allocation program.
By reviewing this section and the section, 'API flat file specification', the retailer should be able to track down to the table and column level, all the extraction data that flows into Oracle Retail Allocation.
Table 10-1 Extraction Data
| Program Name | Tables /Files Extracted |
Fields Extracted | Target File or Table | Target Field | Field Type | Field Length | NOTES |
|---|---|---|---|---|---|---|---|
|
alcl_size_profile.ksh |
ITEM_MASTER |
item |
alc_size_profile |
style |
VAR |
25 |
|
|
profile file |
dept |
dept |
NUMBER |
4 |
|||
|
class |
class |
NUMBER |
4 |
||||
|
subclass |
subclass |
NUMBER |
4 |
||||
|
store |
store |
NUMBER |
10 |
||||
|
size1 |
size1 |
VAR |
10 |
||||
|
size2 |
size2 |
VAR |
10 |
||||
|
size3 |
size3 |
VAR |
10 |
||||
|
size4 |
size4 |
VAR |
10 |
||||
|
qty |
qty |
NUMBER |
17 |
||||
|
ALC_PLAN.KSH |
ITEM_MASTER |
item |
ALC_PLAN |
style |
VARCHAR2(25) |
25 |
|
|
item_parent |
Use item_parent as style if Item_parent_aggregate_ind= 'Y' |
||||||
|
item_grandparent |
Use item_grandparent as Style if item_grandparent_aggregate_ind = 'Y' |
||||||
|
item_aggregate_ind |
|||||||
|
item_parent_aggregate_ind |
|||||||
|
item_grandparent_aggregate_ind |
|||||||
|
profile file |
dept |
dept |
NUMBER(4) |
4 |
|||
|
class |
class |
NUMBER(4) |
4 |
||||
|
subclass |
subclass |
NUMBER(4) |
4 |
||||
|
store |
store |
NUMBER(10) |
20 |
||||
|
size1 |
size1 |
VARCHAR2(10) |
48 |
||||
|
size2 |
size2 |
VARCHAR2(10) |
48 |
||||
|
size3 |
size3 |
VARCHAR2(10) |
48 |
||||
|
size4 |
size4 |
VARCHAR2(10) |
48 |
||||
|
qty |
qty |
NUMBER(12,4) |
12 |
||||
|
ALC_RECEIPT_PLAN.KSH |
ITEM_MASTER |
item |
ALC_PLAN |
style |
VARCHAR2(25) |
25 |
|
|
item_parent |
Use item_parent as style if Item_parent_aggregate_ind= 'Y' |
||||||
|
item_grand parent |
Use item_grand parent as Style if item_grandparent_aggregate_ind = 'Y' |
||||||
|
item_aggregate_ind |
|||||||
|
item_parent_aggregate_ind |
|||||||
|
item_grandparent_aggregate_ind |
|||||||
|
profile file |
dept |
dept |
NUMBER (4) |
4 |
|||
|
class |
class |
NUMBER (4) |
4 |
||||
|
subclass |
subclass |
NUMBER (4) |
4 |
||||
|
store |
store |
NUMBER (10) |
20 |
||||
|
size1 |
size1 |
VAR CHAR2 (10) |
48 |
||||
|
size2 |
size2 |
VAR CHAR2 (10) |
48 |
||||
|
size3 |
size3 |
VAR CHAR2 (10) |
48 |
||||
|
size4 |
size4 |
VAR CHAR2 (10) |
48 |
||||
|
qty |
qty |
NUMBER (12,4) |
12 |
This section describes the API specification.
Plan Data File Layout
Plan data is required in Allocation for allocating items to the store based on plan requirements at the location. The data is extracted by the predictive application server and provided to the allocation interfaces as a flat file.
This segment describes the file format that Predictive Application System provides for uploading plan data into RMS database for Allocation.
File Name: p1prodlevel.NN
Example: p1scls.01 (subclass, domain 01)
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
141410001000 1000000014 _CCOLOR31_SMEDIUM
20051225000000137500
Table 10-2 Plan Data File Layout
| Field Name | Start Position | Width | Format | Content |
|---|---|---|---|---|
|
Product ID |
1 |
25 char |
Alpha |
141410001000 |
|
Location ID |
26 |
20 char |
Alpha |
10000000014 |
|
Diff IDs |
46 |
48 char |
Alpha |
_CCOLOR31_SMEDIUM |
|
EOW Date |
94 |
8 char |
Alpha |
20051225 |
|
Quantity |
102 |
12 char |
Numeric |
000000137500 |
The file name should start with p1 followed by four characters for product level and the domain number. The four product level acceptable are:
- itpt - for item
- scls - for subclass
- clss - for class
- dept - for department
The domain ID should be numeric.
The Product ID, Location ID and Diff IDs fields are left justified and blank filled.
The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters are present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.
The EOW Date field is in the format YYYYMMDD.
The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (e.g. 13.75 would appear in the record as 000000137500).
Total length of each record is 113.
When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.
Receipt Plan Data File Layout
Receipt plan data is required in Allocation for allocating items to the store based on receipt plan requirements at the location. The data is extracted by the predictive application server and provided to the allocation interfaces as a flat file.
This segment describes the file format that Predictive Application System provides for uploading receipt plan data into RMS database for Allocation.
File Name: p1prodlevel.NN
Example: p1scls.01 (subclass, domain 01)
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123
141410001000 1000000014 _CCOLOR31_SMEDIUM
20051225000000137500
Table 10-3 Receipt Plan Data File Layout
| Field Name | Start Position | Width | Format | Content |
|---|---|---|---|---|
|
Product ID |
1 |
25 char |
Alpha |
141410001000 |
|
Location ID |
26 |
20 char |
Alpha |
10000000014 |
|
Diff IDs |
46 |
48 char |
Alpha |
_CCOLOR31_SMEDIUM |
|
EOW Date |
94 |
8 char |
Alpha |
20051225 |
|
Quantity |
102 |
12 char |
Numeric |
000000137500 |
The file name should start with p1 followed by four characters for product level and the domain number. The four product level acceptable are:
- itpt - for item
- scls - for subclass
- clss - for class
- dept - for department
The domain ID should be numeric.
The Product ID, Location ID and Diff IDs fields are left justified and blank filled.
The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters are present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.
The EOW Date field is in the format YYYYMMDD.
The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (e.g. 13.75 would appear in the record as 000000137500).
Total length of each record is 113.
When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.
Size Curve File Layout
Allocation application allocates quantities at the style-color level for fashion merchandise like Blue t-shirts; Dark Blue pants etc and then distributes the allocated quantity according to the size based on the size curve for the location. The size curve data can be inputted into the application directly using the GUI interface in the application or through upload from data provided by the predictive application server.
This section describes in detail the file format structure for the upload.
File Name: dXprodlevel.NN
Example: File Name: d1scls.01 (diff 1, subclass, domain 01)
Record:
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345
100045078 1000000002 CCOLOR01_S30x32 000000137500
Table 10-4 Size Curve File Layout
| Field Name | Start Position | Width | Format | Content |
|---|---|---|---|---|
|
Product ID |
1 |
25 char |
Alpha |
100045078 |
|
Location ID |
26 |
20 char |
Alpha |
1000000002 |
|
Diff IDs |
46 |
48 char |
Alpha |
_CCOLOR01_S30x32 |
|
Quantity |
94 |
12 char |
Numeric |
000000137500 |
The file name should start with letter d, X is diff number being sent followed by four characters for product level and the domain number. The four product level acceptable are:
- itpt - for item
- scls - for subclass
- clss - for class
- dept - for department
The domain ID should be numeric.
The Product ID, Location ID and Diff IDs fields are left justified and blank filled.
The number of separate Diffs in the Diff IDs field is in the range: 0-4. The first character of each Diff is an "_" (underscore) and the second character is the Diff Type. No underscore characters is present in the Diff ID field other than the character that immediately precedes each separate Diff Type within the field. Each Diff in the Diff IDs field is lesser than 12 characters in length, including the leading underscore character and the Diff Type.
The Quantity field is a right-justified, zero-padded numeric and the decimal point is omitted, but the quantity has a 4-digit decimal fraction part (e.g. 13.75 would appear in the record as 000000137500).
Total length of each record is 105.
When uploading data the system updates the quantity if the record exists for the hierarchy/location/Diff_id/EOW data combination or it appends the record into the tables.
Schema file (alcl_size_profile.schema)
This section describes the RETL schema file (alcl_size_profile.schema) used in the RETL script that loads the Curve export file into Oracle Retail.
Allocation's ALC_SIZE_PROFILE table:
<RECORD type="fixed" len="115" final_delimiter="0x0A"> <!-- start pos 5 --> <FIELD name="CLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 9 --> <FIELD name="SUBCLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 13 --> <FIELD name="ITEM" len="25" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 38 --> <FIELD name="STORE" len="20" datatype="string" nullable="false"/> <!-- start pos 58 --> <FIELD name="DIFF_TYPE_1" len="1" datatype="string" nullable="false" nullvalue=""/> <!-- start pos 59 --> <FIELD name="DIFF1" len="10" datatype="string" nullable="false" nullvalue=""/> <!-- start pos 69 --> <FIELD name="DIFF_TYPE_2" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 70 --> <FIELD name="DIFF2" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 80 --> <FIELD name="DIFF_TYPE_3" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 81 --> <FIELD name="DIFF3" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 91 --> <FIELD name="DIFF_TYPE_4" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 92 --> <FIELD name="DIFF4" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 102 --> <FIELD name="QTY" len="14" datatype="dfloat" nullable="false"/> <!-- end pos 114 --> </RECORD> <!-- start pos 1 --> <FIELD name="DEPT" len="4" datatype="string" nullable="true" nullvalue=""/>
Schema file (alcl_plan.schema)
This section describes the RETL schema file (alcl_plan.schema) used in the RETL script that loads the plan export file into Oracle Retail.
Allocation's ALC_PLAN table.
<RECORD type="fixed" len="123" final_delimiter="0x0A"> <!-- start pos 5 --> <FIELD name="CLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 9 --> <FIELD name="SUBCLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 13 --> <FIELD name="ITEM_ID" len="25" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 38 --> <FIELD name="STORE" len="20" datatype="string" nullable="false"/> <!-- start pos 58 --> <FIELD name="DIFF_TYPE_1" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 59 --> <FIELD name="DIFF1_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 69 --> <FIELD name="DIFF_TYPE_2" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 70 --> <FIELD name="DIFF2_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 80 --> <FIELD name="DIFF_TYPE_3" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 81 --> <FIELD name="DIFF3_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 91 --> <FIELD name="DIFF_TYPE_4" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 92 --> <FIELD name="DIFF4_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 102 --> <FIELD name="EOW_DATE" len="8" datatype="date" nullable="false"/> <!-- start pos 110 --> <FIELD name="QTY" len="14" datatype="dfloat" nullable="false"/> <!-- end pos 122 --> </RECORD> <!-- start pos 1 --> <FIELD name="DEPT" len="4" datatype="string" nullable="true" nullvalue=""/>
Schema File (alcl_receipt_plan.schema)
This section describes the RETL schema file (alcl_receipt_plan.schema) used in the RETL script that loads the receipt plan export file into Oracle Retail.
Allocation's ALC_RECEIPT_PLAN table:
<RECORD type="fixed" len="123" final_delimiter="0x0A"> <!-- start pos 5 --> <FIELD name="CLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 9 --> <FIELD name="SUBCLASS" len="4" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 13 --> <FIELD name="ITEM_ID" len="25" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 38 --> <FIELD name="STORE" len="20" datatype="string" nullable="false"/> <!-- start pos 58 --> <FIELD name="DIFF_TYPE_1" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 59 --> <FIELD name="DIFF1_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 69 --> <FIELD name="DIFF_TYPE_2" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 70 --> <FIELD name="DIFF2_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 80 --> <FIELD name="DIFF_TYPE_3" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 81 --> <FIELD name="DIFF3_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 91 --> <FIELD name="DIFF_TYPE_4" len="1" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 92 --> <FIELD name="DIFF4_ID" len="10" datatype="string" nullable="true" nullvalue=""/> <!-- start pos 102 --> <FIELD name="EOW_DATE" len="8" datatype="date" nullable="false"/> <!-- start pos 110 --> <FIELD name="QTY" len="14" datatype="dfloat" nullable="false"/> <!-- end pos 122 --> </RECORD> <!-- start pos 1 --> <FIELD name="DEPT" len="4" datatype="string" nullable="true" nullvalue=""/>
RETL scripts are required to support receipt plan logic (what the store is expected to own) at the store/week level. This rule provides fashion retailers the option to choose different plans coming from different source data feeds.
Large size fashion retailers can use Assortment Planning data as the pre-season source to determine store or warehouse needs for seasonal items. The data file sent from the Assortment Planning system is used to generate the gross need in the Allocation system in order to create pre-allocations.
Once the selling season begins, retailers have the option to switch to a different rule such as Plan, Forecast or Historical data to generate store demand.
Script Names
alct_receipt_plan.ksh
alct_receipl_plan.ksh
Table Name
alc_receipt_plan
Allocation users have the option to select a specified store size profile to be used for the Allocation. Using the RPAS Store Size Profile Optimization application, users have the capability to create seasonal store size profiles and multiple store size profiles created in SPO (called GIDs). These are displayed to the Allocation user as options to be used.
Depending on what is being allocated and expected arrival date in the stores, the Allocation user has the option to view and select the desired store size profile date to be used.
All item and locations use the same store size profile data per allocation. There cannot be unique buy item/location records within a single allocation.
SPO assigns a numeric generation ID number (GID) to specifically created store size profile data. This ID, along with a user defined name should be displayed in the Allocation user interface.
Only those GIDs populated from SPO to Allocation are displayed in the user interface.
The retailer is responsible for updating the Allocation table on a frequent basis or as needed.
SPO GID text files (spo_gid_label.txt) are passed along with the batch of Size Profile Hierarchy dat file. The text file is used as the GID for that batch of dat file. Running RETL for SPO imports data to three tables after extraction.
The RETL for SPO data file format is as follows:
<Beginning of file>
<GID>
<GID_DESC>
<End of File>
The following are examples:
GID1
Winter 2014
Table 10-5 RETL for SPO Data Physical Tables
| Table Head | Description |
|---|---|
|
ALC_GID_HEADER |
The ALC_GID_HEADER table holds all generation ID descriptions. |
|
ALC_GID_PROFILE |
The ALC_GID_PROFILE table holds all generation ID profile IDs. |
|
ALC_SIZE_PROFILE |
The ALC_SIZE_PROFILE table holds all size profiles at Style/Color, Style, Subclass, Class and Department levels. |
