Merch 1208 Dcog

Oracle® Retail Merchandising
Data Conversion Operations Guide

Release 12.0.8
July 2008
Oracle® Retail Merchandising Data Conversion Operations Guide, Release 12.0.8
Copyright © 2008, Oracle. All rights reserved.

Primary Author: Susan McKibbon
The Programs (which include both the software and documentation) contain proprietary
information; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial
property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the
extent required to obtain interoperability with other independently created software or as specified
by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any
problems in the documentation, please report them to us in writing. This document is not
warranted to be error-free. Except as may be expressly permitted in your license agreement for
these Programs, no part of these Programs may be reproduced or transmitted in any form or by
any means, electronic or mechanical, for any purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the
Programs on behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and
technical data delivered to U.S. Government customers are "commercial computer software" or
"commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-
specific supplemental regulations. As such, use, duplication, disclosure, modification, and
adaptation of the Programs, including documentation and technical data, shall be subject to the
licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent
applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—
Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other
inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate
fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the
Programs are used for such purposes, and we disclaim liability for any damages caused by such
use of the Programs.
Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation
and/or its affiliates. Other names may be trademarks of their respective owners.
The Programs may provide links to Web sites and access to content, products, and services from
third parties. Oracle is not responsible for the availability of, or any content provided on, third-
party Web sites. You bear all risks associated with the use of such content. If you choose to
purchase any products or services from a third party, the relationship is directly between you and
the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or
(b) fulfilling any of the terms of the agreement with the third party, including delivery of products
or services and warranty obligations related to purchased products or services. Oracle is not
responsible for any loss or damage of any sort that you may incur from dealing with any third
party.
ii
Contents
Preface ............................................................................................................................. vii
Audience ............................................................................................................................... vii
Related Documents.............................................................................................................. vii
Customer Support................................................................................................................ vii
Conventions.......................................................................................................................... vii
1 Data Conversion Overview ......................................................................................... 1
Data Conversion Process .......................................................................................................1
Data Conversion Approach ...................................................................................................1
Prerequisites and Assumptions ............................................................................................2
How to Use This Guide..........................................................................................................2
2 Master Script (DC_LOAD_MAIN.KSH) ....................................................................... 5
Configuration File Definition (DC_LOAD.CFG)................................................................5
Directories.........................................................................................................................6
Variables............................................................................................................................7
Sequence File Definition.........................................................................................................7
Library File Description (DC_LOAD.LIB)...........................................................................8
Master Script Technical Flow ................................................................................................9
Running KSH Scripts............................................................................................................10
Preparation .....................................................................................................................10
Running a Script.............................................................................................................10
3 Core............................................................................................................................. 11
Data Flow ...............................................................................................................................12
Prerequisites ..........................................................................................................................12
File Format and External Oracle Tables.............................................................................13
File Format ......................................................................................................................13
External Oracle Table Definition .................................................................................13
Customer—DC_CUSTOMER Table ...................................................................................14
Terms ......................................................................................................................................15
DC_TERMS_HEAD Table ............................................................................................15
DC_TERMS_DETAIL Table .........................................................................................16
Freight.....................................................................................................................................18
DC_FREIGHT_TYPE Table ..........................................................................................18
DC_FREIGHT_TERMS Table.......................................................................................18
DC_FREIGHT_SIZE Table............................................................................................19
VAT.........................................................................................................................................20
DC_VAT_CODES Table................................................................................................20
DC_VAT_CODE_RATES Table ...................................................................................21
DC_VAT_REGION Table .............................................................................................21
UDA ........................................................................................................................................22
DC_UDA Table ..............................................................................................................22
iii
DC_UDA_VALUES Table ............................................................................................23
Ticket Type.............................................................................................................................24
DC_TICKET_TYPE_HEAD Table ...............................................................................24
DC_TICKET_TYPE_DETAIL Table.............................................................................25
Diff IDs—DC_DIFF_IDS Table ...........................................................................................26
DC_LOAD_CORE.KSH Segment Wrapper / Load Script..............................................27
Post-Loading Requirements ................................................................................................31
4 Merchandise Hierarchy ............................................................................................. 33
Data Flow ...............................................................................................................................34
Prerequisites ..........................................................................................................................34
File Format ......................................................................................................................35
Department—DC_DEPS Table............................................................................................36
Merchandise Hierarchy Defaults—DC_MERCH_DEFAULTS Table............................44
Class—DC_CLASS Table.....................................................................................................46
Subclass—DC_SUBCLASS Table........................................................................................47
VAT Departments—DC_VAT_DEPS Table ......................................................................48
UDA Item Defaults—DC_UDA_ITEM_DEFAULTS Table.............................................49
DC_LOAD_MERCH.KSH Segment Wrapper / Load Script..........................................50
Post-Loading Requirements ................................................................................................54
5 Organizational Hierarchy .......................................................................................... 55
Prerequisites ..........................................................................................................................55
Warehouse .............................................................................................................................56
Data Flow........................................................................................................................56
File Format and External Oracle Tables......................................................................57
DC_WH_ADDR Table ..................................................................................................58
DC_PWH Table..............................................................................................................60
DC_VWH Table .............................................................................................................65
DC_TRANSIT_TIMES Table ........................................................................................70
DC_LOAD_WH_ORG.KSH Segment Wrapper / Load Script................................72
Store ........................................................................................................................................75
Data Flow........................................................................................................................76
File Format and External Oracle Tables......................................................................76
DC_REGION Table........................................................................................................77
DC_DISTRICT Table .....................................................................................................77
DC_STORE_ADDR Table .............................................................................................79
DC_STORE_ADD Table................................................................................................80
DC_STORE_DEPT_AREA Table .................................................................................86
DC_LOAD_STORE_ORG.KSH Segment Wrapper / Load Script ..........................87
Post-Loading Requirements .........................................................................................88
6 Suppliers..................................................................................................................... 91
iv
Data Flow ...............................................................................................................................91
Prerequisites ..........................................................................................................................92
File Format ......................................................................................................................92
Suppliers—DC_SUPS Table ................................................................................................93
Supplier Address—DC_SUP_ADDR Table.....................................................................102
Supplier Import Attributes—DC_SUP_IMPORT_ATTR Table....................................104
DC_LOAD_SUPPLIER.KSH Segment Wrapper / Load Script ....................................107
LOAD_SUPPLIER........................................................................................................107
LOAD_SUP_ADDR .....................................................................................................108
LOAD_ SUP_IMPORT_ATTR....................................................................................108
Post-Loading Requirements ..............................................................................................108
7 Items.......................................................................................................................... 109
Prerequisites ........................................................................................................................109
Fashion Items.......................................................................................................................110
Data Flow......................................................................................................................110
File Format and External Oracle Tables....................................................................111
DC_STYLE Table..........................................................................................................112
DC_FASHION_SKU Table .........................................................................................116
DC_FASHION_XREF Table .......................................................................................119
DC_LOAD_FASHION_ITEM.KSH Segment Wrapper / Load Script .................121
Hardline Items.....................................................................................................................125
Data Flow......................................................................................................................125
DC_HARDLINES Table..............................................................................................126
DC_HARDLINES_XREF Table ..................................................................................129
DC_LOAD_HARDLINE_ITEM.KSH Segment Wrapper / Load Script ..............131
Grocery Items ......................................................................................................................134
Data Flow......................................................................................................................134
DC_PRODUCT_LINE Table.......................................................................................135
DC_PRODUCT Table ..................................................................................................139
DC_GROCERY_VARIANT Table .............................................................................145
DC_LOAD_GROCERY_ITEMS.KSH Segment Wrapper / Load Script ..............147
Pack Items ............................................................................................................................151
Data Flow......................................................................................................................151
DC_ORDERABLE_PACK Table ................................................................................153
DC_SELLABLE_PACK Table.....................................................................................158
DC_PACK_COMPONENT Table..............................................................................161
DC_PACK_XREF Table ..............................................................................................162
v
DC_LOAD_PACKS.KSH Segment Wrapper / Load Script ..................................163
Item Supplier .......................................................................................................................171
Data Flow......................................................................................................................171
Prerequisites .................................................................................................................171
DC_ITEM_SUPPLIER Table.......................................................................................173
DC_ITEM_SUPP_COUNTRY Table..........................................................................175
DC_PRICE_HIST Table...............................................................................................180
DC_ITEM_SUPP_COUNTRY_DIM Table ...............................................................181
DC_LOAD_ITEM_SUPPLIER.KSH Segment Wrapper / Load Script .................185
Post-Loading Requirements .......................................................................................189
Item Location .......................................................................................................................190
Data Flow......................................................................................................................190
Prerequisites .................................................................................................................191
DC_ITEM_LOC Table .................................................................................................192
DC_LOAD_ITEM_LOCATION.KSH Segment Wrapper / Load Script ..............197
Others ...................................................................................................................................203
Data Flow......................................................................................................................203
Prerequisites .................................................................................................................204
DC_UDA_ITEM_LOV Table ......................................................................................205
DC_UDA_ITEM_DATE Table ...................................................................................206
DC_UDA_ITEM_FF Table ..........................................................................................207
DC_VAT_ITEM Table .................................................................................................208
DC_ITEM_SEASONS Table .......................................................................................210
DC_ITEM_TICKET Table ...........................................................................................211
DC_LOAD_ITEM_OTHER.KSH Segment Wrapper / Load Script......................212
8 Optional Data............................................................................................................ 217
Core Tables ..........................................................................................................................217
Merchandise Hierarchy Tables .........................................................................................217
Organizational Hierarchy Tables......................................................................................217
Supplier Tables....................................................................................................................218
Items Tables .........................................................................................................................218
A 219
Appendix: Seed Data Installation ................................................................................ 219
vi
Preface
This guide is a reference for the data conversion operations required to migrate from
legacy retail management systems to the Oracle Retail Merchandising software.
This guide describes the data conversion operations that begin with flat files produced
from the databases of legacy applications. It details the content and format of each flat
file required to perform the data conversion, as well as the tables created and populated
by the conversion scripts.
Audience
This guide is for the members of the implementation team who plan and execute the
migration of data at the retailer’s site. The team includes the retailer’s systems
management, database management, systems analysis, and operations personnel. It also
includes Oracle Retail and consultant support staff who assist in the implementation.
Related Documents
For more information, see the following document in the Oracle Retail Merchandising
Release 12.0.8 documentation set:
Oracle Retail Merchandising Batch Schedule
These documents provide additional information to implement and operate the
Merchandising applications:
Oracle Retail Merchandising System (RMS) documentation
Oracle Retail Price Management (RPM) documentation
Oracle Retail Security Manager (RSM) documentation
Customer Support
https://metalink.oracle.com
When contacting Customer Support, please provide the following:
Product version and program/module name
Functional and technical description of the problem (include business impact)
Detailed step-by-step instructions to re-create
Exact error message received
Screen shots of each step you take
Conventions
Navigate: This is a navigate statement. It tells you how to get to the start of the procedure
and ends with a screen shot of the starting point and the statement “the Window Name
window opens.”
Note: This is a note. It is used to call out information that is

important, but not necessarily part of the procedure.
This is a code sample
It is used to display examples of code
A hyperlink appears like this.
Preface vii
viii
1
Data Conversion Overview
This chapter is a brief introduction to the overall process to convert legacy data to the
tables required by the Oracle Retail Merchandising applications. You perform the
conversion using a data conversion toolset designed specifically for this purpose.
This chapter describes the components of the data conversion toolset and the sequence of
data conversion. It also notes some basic assumptions and prerequisites for performing
the data conversion.
Data Conversion Process

The conversion processing performed with the data conversion toolset is one part of the
total scope of the migration from legacy systems to the Merchandising applications.
Before actual data conversion can begin, the implementation team must complete
analysis, mapping, preparation, and extraction of the legacy data into the flat files
required for conversion. The Oracle Retail implementation team members perform this
work with the retailer’s systems management, database management, systems analysis,
and operations staff.
The data conversion toolset assumes clean data that conforms to the data structures
detailed in this guide.
Data Conversion Approach

The overall approach of this data conversion toolset is to use one data loading script for
each table or functional area, based on input files provided by the legacy system. These
scripts are executed in sequence through a master wrapper script (UNIX shell script).
This toolset assumes that all data loaded is clean, so there is no data validation during
data load. If there are any issues with data during conversion load, you must manually
view log files to determine the cause and correct the data.
Data Conversion Operations Guide 1

Prerequisites and Assumptions
The following scripts are included in this data conversion toolset:

Master script DC_LOAD_MAIN.KSH
This is the master script that starts the segment load scripts for each functional area.
This KSH script can run other KSH or SQL scripts that are not in covered in the
Oracle Retail Merchandising conversion toolset, such as future/custom validation
scripts. You can customize this script, and you can configure it to load any specified
number of tables at one time.
Segment load scripts
For each functional area, the segment load scripts build the external tables by calling
the database conversion table create scripts (SQL) and load data from the flat files by
calling the SQL load script. The segment load script can also be configured to load
only the data, skipping the table creation.
External Oracle table create scripts
For each functional area, the database conversion table create scripts contain the table
column definitions, the target data file (*.dat file), the Oracle directory where the data
files are located, and the location where the bad, discard, and log files are created.
The load script has internal functions that select from the external tables and insert
into Oracle Merchandising tables.
Note: Before you begin using the data conversion toolset,

you must install the Merchandising applications and load all
seed data. See Appendix A for more information.
Prerequisites and Assumptions

The following prerequisites and assumptions apply to the data conversion processes
described in this guide:
Transformation of legacy data is not included as part of the data conversion toolset.
Data loaded in flat files is assumed to be clean data. There is no data validation
included in this toolset.
Database constraints should be turned off.
All database triggers must be evaluated to determine which need to be turned off
during the conversion effort.
How to Use This Guide

This guide describes:
The master script used to run the auto-loading process
The available conversion auto-loading programs and processing involved
There are functional and technical descriptions of all programs included in the data
conversion toolset. The program descriptions are organized by functional areas:
Core
Merchandise Hierarchy
Organizational Hierarchy
Suppliers
Items
2 Oracle Retail Merchandising

How to Use This Guide
Note: Data conversion must be performed in order by

functional area, according to the organization of this guide
and the prerequisites stated for each functional area.
The description of each program includes the following information:

Program purpose and functionality
Technical specifications
Field level definitions
Flat file layouts
To perform data conversion, follow this guide starting with Chapter 2. This guide has the
following chapters:
Chapter Description
2 Master Script This chapter describes the master script, the main tool used
(DC_LOAD_MAIN.KSH) to run the auto-loading process. It describes configuration
and setup tasks required before you can use the data
conversion toolset. It also details how to customize the
toolset for your specific data conversion needs.
3 Core These chapters describe in detail all the programs and files
4 Merchandise Hierarchy required to load data for each of the functional areas.
5 Organizational Hierarchy Each chapter also contains a Prerequisites section that lists
all tasks that must be completed prior to running the tools
6 Suppliers
for that functional area. Some chapters also have a Post-
7 Items Loading Requirements section that describes tasks that
must be done before data conversion is considered
complete for that functional area.
8 Optional Data This chapter describes additional optional data that you can
load manually for each of the functional areas. Optional
data can be loaded after auto-loading is complete.

2
Master Script (DC_LOAD_MAIN.KSH)
The configurable master script executes the data load for each functional area in the data
conversion toolset. It feeds from a sequence file (*.seq) that lists other scripts or
executable programs used to load data to the RMS tables. The script can execute the
following:
Other Korn shell (ksh) scripts (segment wrapper scripts to load data to RMS tables)
SQL scripts (to provide custom validation or table counts after loads)
Single- or multi-threaded RMS batch programs (to support conversion of functional
areas such as organization; for example, running storeadd)
The sequence file allows you to customize the execution of programs. You can customize
the master script, and you can configure it to load any specified number of tables at one
time. The script can be configured to run each segment wrapper script independently,
allowing you to load smaller sets of data.
Note: Although the master script allows you to run all

functional areas in succession, it is not intended for this
purpose. There are prerequisites and post-loading
requirements for each functional area, as well as other
dependencies across functional areas. Refer to Chapters 3
through 7 for additional details about loading requirements.
Common functions such as error handling and messaging (writing information to log
files) are handled in a separate library file called in the dc_load_main.ksh script. Global
variables, script, data, and other directories are defined in a separate configuration file.
Because most clients will be loading data into the Oracle tables with constraints disabled,
it is advisable to add custom SQL scripts for validation. Calls to these SQL scripts can be
added to the sequence file after the load script. Suggested validation is noted in each
section, to assist in developing and adding validation scripts to ensure a successful
conversion. These validations will be done after the individual load completes and prior
to enabling the constraints. The nature and quantity of load issues indicated by the load
validations will help determine, along with analysis of the log files, whether a reload
should be considered.
Configuration File Definition (DC_LOAD.CFG)

The configuration file contains all directory paths for the executable KSH scripts, SQL
scripts, and so on. It also contains log and temp directories needed by the
dc_load_main.ksh script and the segment scripts. The directories are stored in variables
accessible to the calling script through the export command. This configuration script
must be set up prior to start of data conversion runs.
Note: Before you begin using the data conversion toolset,

you must install the merchandising applications and load all
seed data. Refer to Appendix A for more information.

Configuration File Definition (DC_LOAD.CFG)
Directories
Create a separate set of UNIX directories to hold the data conversion toolset components.
The following directories specific to data conversion should be configured before
running the master script. All data and log directories must have read and write
privileges.
Directory Name Description
Oracle data directory orclDataDir This is the directory name defined within the
database that points to a system directory that
contains the data files used by the external tables
(dataDir).
Oracle log directory orclLogDir This is the directory name defined within the
database that points to a system directory that
contains the log files generated by the external
tables.
Note: In some instances, an entry in the external
table log may be repeated several times, because the
external table may be used in several inserts.
Data directory dataDir This directory contains the data files (*.dat) used to
load information to the external Oracle tables.
Data completed dataCompDir This directory contains processed data files.
directory
Bad file directory badDir This directory contains files with rejected records
(*.bad files).
Discard file directory dscDir This directory contains discarded files (*.dsc). This
directory can be the same as the bad file directory.
Script directory scriptDir This directory contains all the scripts used in the
conversion process.
Log directory logDir This directory contains the conversion script
execution logs.
Note: This directory is different from the orclLogDir.
The logDir contains the daily logs generated by the
conversion scripts. The orclLogDir contains logs
generated by the external tables.
Status directory statusDir This directory contains the status files created after
each load. This directory can be the same as the log
directory.
RMS bin directory rmsBinDir This is the directory where the RMS batch
executables are installed.
Other directories can be created as needed.

Sequence File Definition
Variables
The following variables are shared across all conversion scripts:
Variable Description
connectStr Oracle username/password information to connect to the database

dataExt File extension for data files, default .dat
badExt File extension for bad files, default .bad
dscExt File extension for discard files, default .dsc
statusExt File extension for status files, default .status
seqExt File extension for sequence files, default .seq
Other variables are used as needed.
Sequence File Definition

The sequence file (*.seq) is a file that lists executables to be run in sequence. This file can
specify KSH scripts, SQL scripts, RMS batch programs, or other executables. The
dc_load_main.ksh script reads from this file, so this file must be configured prior to
running the master script.
The lines of the file have the following format:
<tag> <script-name> <script-parameters>
Tags are as follows:
Tag Description
PGM Use this tag to run KSH scripts or other executable scripts or applications. If no
path is defined with the script name, it is assumed that the script resides in the
script directory defined in the configuration file (*.cfg).
SQL Use this tag to run SQL scripts. If no path is included with the script name, it is
assumed that the script resides in the script directory defined in the
configuration file.
RMS Use this tag to run RMS batch programs. Although these are executable, RMS
tables must be referenced to automatically thread the execution. It is assumed
that the batch executable is located in the bin directory unless specified
otherwise (when using customized programs) and has an entry in the restart
control tables (except for prepost).
> Use this tag before text lines to display custom messages to both the log file and
screen.
# Use this tag before text lines to include remarks in the sequence file that are
ignored during execution.
Example:
# This section will load the supplier information to the RMS tables.
> Running LOAD_SUPPLIER.KSH…
PGM load_supplier.ksh
> Loading supplier information completed.
# Now validate if the data is loaded successfully.
SQL dc_validate_supplier.sql

Library File Description (DC_LOAD.LIB)
Note: The sequence file should terminate with a new line.

The tag, program name, and parameters are all separated by
spaces.
Library File Description (DC_LOAD.LIB)

The dc_load.lib file is a collection of common functions used by the dc_load_main.ksh
and segment load scripts used in the conversion process. The functions are as follows:
checkCfg—This function is called in by the load scripts to check whether the
configuration file contains sufficient information to run the conversion load.
checkError—This function is called inside execKsh and execSql, after executing the
script read from the sequence file. It checks the process status and writes the message
to the log file.
checkFile—This function checks whether the file passed in meets certain file attribute
conditions. Using options, this function checks the following:
– File existence (option –e)
– Read access (option –r)
– File size (if greater than 0, using option -s)
– If executable (option –x)
For conversion files defined in the configuration file, attribute checks are performed
according to type:
– For data (*.dat) and sequence (*.seq) files, files are checked for existence, size, and
read access.
– For bad (*.bad), discard (*.dsc), and status (*.status) files, only existence is
checked.
This function is also used to check whether a program is executable. It returns 1 if
one of the set conditions fails.
getAvailThread—This function gets the minimum thread value for the passed-in
batch program from the restart_program_status table where the status is ‘ready for
start’. It uses an embedded SQL SELECT to get the information.
refreshThreads—This function updates the restart_program_status table to ‘ready
for start’ status for threads the previously completed successfully.
execPgm—This function is called from the main script to execute KSH or other
executable scripts, as read from the *.seq file. The program file passed in is verified
first, prior to execution, using the checkFile function. If the file is valid, the script is
invoked as it would be in the command line. All messages displayed by the called
script are written to the log.
execSql—This function is called from the main script to execute SQL scripts only, as
read from the *.seq file. The SQL file passed in is verified first, prior to execution,
using the checkFile function. The sqlplus –s command is used to execute the SQL
script, passing the connect string defined in the env file and the script name. All
messages displayed by the called script are written to the log file.
execRms—This function is called from the main script to execute RMS batch
programs, threaded according to the restart control tables. Because this script allows
execution of custom RMS batch programs, the function checks whether the file is
valid, using the checkFile function with the option –x. If no path is defined, it uses
the default directory for the RMS executables defined in the load configuration file. If
the file is found to be valid, the function checks the type of batch program it will run.

Master Script Technical Flow
For prepost batch, the function extracts the main batch and the prepost indicator
from the seqData2 information and executes the batch.
For file- or table-based batch programs, the function uses more complex logic to take
advantage of the multi-threading capabilities of the batch. File-based programs are
dependent on input files to load information to the RMS tables. The script checks
whether at least an input file exists. If so, the script loops through the file list,
refreshes the restart_program_status table using the refreshThreads function, and
attempts to get an available thread using the getAvailThread function. If a thread is
found, it moves the input file to a process directory (defined in the *.cfg file),
appends the thread number, and executes the batch. These steps are repeated until
all files in the input file directory (also defined in the *.cfg file) are processed. Only
files with the correct file prefix (for example, POSU for posupld files) are processed.
For table-based batch programs, the function checks whether a driver value is
defined. If none is defined, the batch is not threaded, or it is threaded using its
parameters. In this case, the function checks the seqData2 information passed in to
the function. If seqData2 contains no data, the batch is executed immediately. If the
parameter variable (from the seqData2 value) contains information, the function
builds a parameter list (paramLst array) and loops through the parameter list. If the
parameter list has values, the script starts the processing by obtaining an available
thread through the refreshThreads and getAvailThread functions, and executing the
batch by passing the parameter values required. Table-based batch programs are
handled by obtaining the number of threads from the restart control, refreshing the
threads, and looping through each available thread.
Simultaneous execution of the batch (multithreading) is achieved through a
subprocess (‘&’ appended to the batch execution line).
Master Script Technical Flow

The dc_load_main.ksh script first calls the dc_load.cfg configuration file and the
dc_load.lib library file to set up the environment variables, such as directories, and to
define common functions. Most of the actual logic resides in the library.
The main section first validates whether the sequence file passed in is valid (if it exists, is
readable, and has data).
If the file is valid, the script proceeds to read the information from the file line by line:
The script assumes that the first value it reads is the tag value and stores this value in
the tag variable.
The second value contains the program name (program name only, or with specific
path names for files not in the script directory defined in the configuration file). This
information is stored in the seqData1 variable.
The third value contains the rest of the program parameters and is stored in the
seqData2 variable.
Using a case statement, the script checks each tag and executes the appropriate command
or function:
Empty lines, and those with the ‘#’ tag, are ignored.
Text lines after the ‘>’ tag are displayed to the screen and written to the log file. This
tag is used to indicate the current processing point.
For lines with the PGM tag, the script calls the execPgm library function.
Similarly, for lines with SQL and RMS tags, the script calls the execSql and execRms
library functions.

Running KSH Scripts
Running KSH Scripts

This section describes the preparations for running KSH scripts and the commands to
run scripts.
Preparation
Before running a KSH script, ensure that the file has the proper permissions:
-rwxrwx-r-x
Delete the status (*.status), discard (*.dsc), and bad (*.bad) files.
The environment path variable (PATH) must include the directory where the conversion
scripts will be executed. The UNIX administrator can set this by using a script, or the user
can export the path by doing one of the following (where > represents the UNIX or Linux
command line prompt):
Option 1
> cd $MMHOME/external/scripts (or the actual script directory)
> export PATH=$PATH:.
Option 2
Add the following line to the user .profile file:
export PATH=$PATH:$MMHOME/external/scripts (or the actual script directory)
Running a Script
Run the master script using the following syntax (where > represents the UNIX or Linux
command line prompt):
> dc_load_main.ksh –q <sequence-file-name>
Note the use of ‘ksh’ in the command. This prevents the program from exiting the session
after it has completed execution.
To run individual segment wrapper scripts, the ‘–q <sequence-file-name>’ portion of the
command line is not required. For example:
> dc_load_core.ksh
Note: When the KSH script calls SQL scripts to load external
tables, it is common to encounter the following error. This is
because there may not be an external Oracle table to DROP if
the table does not exist in the database. No additional action
is required.
ERROR at line 1:
ORA-00942: table or view does not exist
The data loading process does not truncate RMS tables; it
only DROPs external Oracle tables. The KSH script can be
used to load data to the same table in multiple phases.

3
Core
This chapter describes data conversion for the following RMS tables, listed in the order
that they must be loaded:
CUSTOMER
TERMS_HEAD
TERMS_DETAIL
FREIGHT_TYPE
FREIGHT_TERMS
FREIGHT_SIZE
VAT_CODES
VAT_CODE_RATES
VAT_REGION
UDA
UDA_VALUES
TICKET_TYPE_HEAD
TICKET_TYPE_DETAIL
DIFF_IDS
The following programs are included in this functional area:
Main wrapper script dc_load_main.ksh
This main script is used across all functional areas to call segment load scripts. Refer
to Chapter 2 for details.
Segment load script dc_load_core.ksh
This wrapper calls the external Oracle table create and load scripts.
External Oracle table create scripts:
– dbc_create_customer_tab.sql
– dbc_terms_head_tab.sql
– dbc_terms_detail_tab.sql
– dbc_create_freight_type_tab.sql
– dbc_create_freight_terms_tab.sql
– dbc_create_freight_size_tab.sql
– dbc_create_vat_codes_tab.sql
– dbc_create_vat_code_rates_tab.sql
– dbc_create_vat_region_tab.sql
– dbc_create_uda_tab.sql
– dbc_create_uda_values_tab.sql
– dbc_create_ticket_type_head_tab.sql
– dbc_create_ticket_type_detail_tab.sql
– dbc_diff_ids_tab.sql

Data Flow
Data Flow
The following diagram shows the data flow for the Core functional area:
Prerequisites
Before you begin using the data conversion toolset for the Core functional area, there are
tables that must be loaded manually, because of data dependencies for auto-loading
within this functional area. Manual data loading can be done online through
Merchandising applications (RMS, RPM), or scripts can be created. Manual data loading
is not included as part of this data conversion toolset. Check with your database
administrator to determine the best approach for your data conversion needs.
Note: It is assumed that you have already installed the

Merchandising applications and loaded all installation seed
data. Refer to Appendix A for more information.
The following table lists the tables that require manual data loading and indicates
whether each table is required or optional:
Table Required / Optional / Comments
CALENDAR Required
Note: Calendar data is loaded as part of installation; however, the
data provided may not match the calendar that fits your business
operation. Consider revising the calendar data script.
Tip: CALENDAR.MONTH_454 = ‘1’ is January (not fiscal year).
HALF Required
TSF_ENTITY Required when MLE is turned on
BANNER Required when multi-channel is turned on
CHANNELS Required
SEASONS Optional
PHASES Optional
DIFF_TYPE Required

File Format and External Oracle Tables
Table Required / Optional / Comments
TSFZONE Required
STORE_FORMAT Required
BUYER Optional
MERCHANT Optional
CVB_HEAD Optional
CVB_DETAIL Optional
ELC_COMP Required only if upcharges will be loaded
STATE Required only if using addresses in U.S. locations

The following topics describe the flat file formats that must be created with data from the
legacy system. These files must be formatted based on definitions provided before data
can be loaded. The data fields for each flat file must created in the order listed.
The dc_load_core.ksh script calls each of the SQL scripts in a specific order. The SQL
scripts create external Oracle tables from flat file feeds and load data into the Oracle
Retail Merchandising database.
File Format
In the table definitions that follow, the File Format columns Field Name, Data Type, and
Max Length define the structure of the source file.
Note: Data files must be in UNIX file format and encoded as

UTF-8. If a caret-M (^M) can be seen when the file is viewed
in a UNIX session, it indicates that the file is in a DOS or
Windows format and will cause errors when data is loaded.
Character fields cannot contain carriage returns, because the load process will process a
carriage return as an indication of a new record.
External Oracle Table Definition

In the table definitions that follow, the External Oracle Table Definition columns Field
Name and Data Type (including length) define the physical external table.

Customer—DC_CUSTOMER Table
Customer—DC_CUSTOMER Table
File name: DC_CUSTOMER.DAT
Table create SQL script: DBC_CREATE_CUSTOMER_TAB.SQL
External Oracle table created: DC_CUSTOMER
Suggested post-loading validation (sequence after dc_load_core.ksh):
Ensure that CUSTOMER.CUST_ID is unique.
Ensure that CUSTOMER.CUST_STATE is a valid STATE.STATE.
Ensure that CUSTOMER.CUST_COUNTRY_ID is a valid COUNTRY.COUNTRY_ID.
Capture the count from CUSTOMER and compare to flat file DC_CUSTOMER.DAT
to ensure that all rows are loaded.
File Format External Oracle Table Definition
Field Name Data Type Max Req’d Description Field Name Data Type
Length
CUST_ID Alpha- 10 Y Code that uniquely CUST_ID VARCHAR2(10)

numeric identifies the customer.
CUST_NAME Alpha- 120 Y Customer name. CUST_NAME VARCHAR2(120)
numeric
CONTACT_ Alpha- 120 N Contact name, if different CONTACT_ VARCHAR2(120)
NAME numeric from the customer name. NAME
CUST_TITLE Alpha- 250 N Title associated with CUST_TITLE VARCHAR2(250)
numeric customer (for example,
Mr., Mrs., Dr.).
CUST_ADD1 Alpha- 240 Y Customer street address CUST_ADD1 VARCHAR2(240)
numeric line 1.
CUST_ADD2 Alpha- 240 N Customer street address CUST_ADD2 VARCHAR2(240)
numeric line 2. Optional.
CUST_CITY Alpha- 120 Y Customer address city. CUST_CITY VARCHAR2(120)
numeric
CUST_STATE Alpha- 3 N Customer address state. CUST_STATE VARCHAR2(3)
numeric This must be a valid state
in the STATE table.
CUST_ Alpha- 3 Y Customer address country CUST_ VARCHAR2(3)
COUNTRY_ID numeric ID. COUNTRY_ID
CUST_POST Alpha- 30 N Customer address postal CUST_POST VARCHAR2(30)
numeric code.
DAY_PHONE Alpha- 20 Y Customer daytime DAY_PHONE VARCHAR2(20)
numeric telephone number.
EVE_PHONE Alpha- 20 N Customer evening EVE_PHONE VARCHAR2(20)
numeric telephone number.

Terms
Terms
DC_TERMS_HEAD Table
File name: DC_TERMS_HEAD.DAT
Table create SQL script: DBC_CREATE_TERMS_HEAD_TAB.SQL
External Oracle table created: DC_TERMS_HEAD
Ensure that TERMS_HEAD.TERMS is unique.
Capture the count from TERMS_HEAD and compare to flat file
DC_TERMS_HEAD.DAT to ensure that all rows are loaded.
Length
TERMS Alpha- 15 Y Unique identifier of TERMS VARCHAR2(15)

numeric supplier payment terms
record.
TERMS_CODE Alpha- 50 Y Code that represents the TERMS_CODE VARCHAR2(50)
numeric supplier payment terms in
Oracle Financials.
TERMS_DESC Alpha- 240 Y Description of the supplier TERMS_DESC VARCHAR2(240)
numeric payment terms (for
example, 2.5% 30 Days).
RANK Alpha- 10 Y Rank to rate invoice RANK NUMBER(10)
numeric payment terms against
purchase order terms.
These rankings are defined
in the client’s financial
system. These rankings are
used in “best terms”
calculation. When terms are
compared, the term with
the higher rank (meaning
lower number—1 is the
highest rank) is the best
term. This must be a whole
number greater than zero.

Terms
DC_TERMS_DETAIL Table
File name: DC_TERMS_DETAIL.DAT
Table create SQL script: DBC_CREATE_TERMS_DETAIL_TAB.SQL
External Oracle table created: DC_TERMS_DETAIL
Ensure that TERMS_DETAIL.TERMS is a valid TERMS_HEAD.TERMS.
Ensure that each combination of TERMS_DETAIL.TERMS and
TERMS_DETAIL.TERMS_SEQ is unique.
Capture the count from TERMS_DETAIL and compare to flat file
DC_TERMS_DETAIL.DAT to ensure that all rows are loaded.
Note: Column order for this file does not match the RMS
TERMS_DETAIL table.
Length
TERMS Alpha- 15 Y Unique identifier of supplier TERMS VARCHAR2(15)

numeric payment terms record.
TERMS_SEQ Numeric 10 Y Order sequence in which to TERMS_SEQ NUMBER(10)
apply the discount percent.
DUEDAYS Numeric 3 Y Number of days until DUEDAYS NUMBER(3,)
payment is due.
DUE_MAX_ Numeric 12,4 Y Maximum payment amount DUE_MAX_ NUMBER(12,4)
AMOUNT due by a given date. AMOUNT
DUE_DOM Numeric 2 Y Day of month used to DUE_DOM NUMBER(2)
calculate due date of invoice
payment line. For example, 1
represents the first day of the
month.
DUE_MM_ Numeric 3 Y Number of months ahead DUE_MM_FWD NUMBER(3)
FWD used to calculate due date of
invoice payment line.
DISCDAYS Numeric 3 Y Number of days in which DISCDAYS NUMBER(3)
payment must be made in
order to receive the discount.
PERCENT Numeric 12,4 Y Percent of discount if PERCENT NUMBER(12,4)
payment is made within
specified time period.
DISC_DOM Numeric 2 Y Day of month used to DISC_DOM NUMBER(2)
calculate discount date of
invoice payment line. For
example, 1 represents the first
day of the month.
DISC_MM_ Numeric 3 Y Number of months ahead DISC_MM_FWD NUMBER(3)
FWD used to calculate discount
date of invoice payment line.

Terms
ENABLED_ Alpha- 1 Y Whether payment terms are ENABLED_FLAG VARCHAR2(1)

FLAG numeric valid or invalid within the
application.
CUTOFF_ Numeric 2 Y Day of the month after which CUTOFF_DAY NUMBER(2)
DAY Oracle Payables schedules
payment, using the day after
the current month.
FIXED_ Alpha- 7 N Fixed due date. FIXED_DATE DATE
DATE numeric Date format is
‘DDMONYYYY’ (for example,
02JAN2007).
START_ Alpha- 7 N Date in which the payment START_DATE_ DATE
DATE_ numeric terms become active ACTIVE
ACTIVE Date format is
‘DDMONYYYY’.
END_DATE_ Alpha- 7 N Date in which the payment END_DATE_ DATE
ACTIVE numeric terms become inactive ACTIVE
Date format is
‘DDMONYYYY’.

Freight
Freight
DC_FREIGHT_TYPE Table
File name: DC_FREIGHT_TYPE.DAT
Table create SQL script: DBC_CREATE_FREIGHT_TYPE_TAB.SQL
External Oracle table created: DC_FREIGHT_TYPE
Ensure that FREIGHT_TYPE.FREIGHT_TYPE is unique.
Capture the count from FREIGHT_TYPE and compare to flat file
DC_FREIGHT_TYPE.DAT to ensure that all rows are loaded.
Length
FREIGHT_ Alpha- 6 Y Unique identifier of the FREIGHT_TYPE VARCHAR2(6)

METHOD numeric freight method.
FREIGHT_ Alpha- 250 Y Description of the freight FREIGHT_ VARCHAR2(250)
METHOD_ numeric method. Examples are TYPE_DESC
DESC ‘Full Container Load’ and
‘Less than Container
Load’.
DC_FREIGHT_TERMS Table
File name: DC_FREIGHT_TERMS.DAT
Table create SQL script: DBC_CREATE_FREIGHT_TERMS_TAB.SQL
External Oracle table created: DC_FREIGHT_TERMS
Ensure that FREIGHT_TERMS.FREIGHT_TERMS is unique.
Capture the count from FREIGHT_TERMS and compare to flat file
DC_FREIGHT_TERMS.DAT to ensure that all rows are loaded.
Field Name Data Max Req’d Description Field Name Data Type
Type Length
FREIGHT_ Alpha- 30 Y Unique identifier of freight FREIGHT_ VARCHAR2(30)

TERMS numeric terms record. TERMS
TERM_DESC Alpha- 240 Y Description of the freight terms. TERM_DESC VARCHAR2(240)
numeric Examples include a percentage
of total cost or a flat fee.
START_ Alpha- 9 N Date on which the freight terms START_DATE_ DATE
DATE_ numeric become active. ACTIVE
ACTIVE Date format is ‘DDMONYYYY’
(for example, 02JAN2007).

Freight
END_DATE_ Alpha- 9 N Date on which the freight terms END_DATE_ DATE

ACTIVE numeric become inactive. ACTIVE
Date format is ‘DDMONYYYY’.
Active_flag Alphanu 1 Y Whether freight terms are valid ENABLED_ VARCHAR2(1)
meric or invalid within the application. FLAG
Default = ‘N’.
DC_FREIGHT_SIZE Table
File name: DC_FREIGHT_SIZE.DAT
Table create SQL script: DBC_CREATE_FREIGHT_SIZE_TAB.SQL
External Oracle table created: DC_FREIGHT_SIZE
Ensure that FREIGHT_SIZE.FREIGHT_SIZE is unique.
Capture count from FREIGHT_SIZE and compare to flat file
DC_FREIGHT_SIZE.DAT to ensure that all rows are loaded.
Length
FREIGHT_ Alpha- 6 Y Unique identifier of the freight FREIGHT_SIZE VARCHAR2(6)

SIZE numeric size record.
FREIGHT_ Alpha- 250 Y Freight size description (for FREIGHT_SIZE_ VARCHAR2(250)
SIZE_DESC numeric example, ’40 foot container’). DESC

VAT
VAT
DC_VAT_CODES Table
File name: DC_VAT_CODES.DAT
Table create SQL script: DBC_CREATE_VAT_CODES_TAB.SQL
External Oracle table created: DC_VAT_CODES
Ensure that VAT_CODES.VAT_CODE is unique.
Capture the count from VAT_CODES and compare to flat file
DC_VAT_CODES.DAT to ensure that all rows are loaded.
Length
VAT_CODE Alpha- 6 Y Unique identifier of value added VAT_CODE VARCHAR2(6)

numeric tax code, used to determine which
items are subject to VAT.
Valid values are:
S - Standard
C - Composite
Z - Zero
E- Exempt
VAT_CODE_ Alpha- 120 Y Value added tax code description. VAT_CODE_ VARCHAR2(120)
DESC numeric DESC

VAT
DC_VAT_CODE_RATES Table
File name: DC_VAT_CODE_RATES.DAT
Table create SQL script: DBC_CREATE_VAT_CODE_RATES_TAB.SQL
External Oracle table created: DC_VAT_CODE_RATES
Ensure that VAT_CODE_RATES.VAT_CODE is a valid VAT_CODES.VAT_CODE.
Capture the count from VAT_CODE_RATES and compare to flat file
DC_VAT_CODE_RATES.DAT to ensure that all rows are loaded.
Type Length
VAT_CODE Alpha- 6 Y Unique identifier of value VAT_CODE VARCHAR2(6)

numeric added tax code.
ACTIVE_ Alpha- 9 Y Date on which VAT rate ACTIVE_DATE DATE
DATE numeric becomes active.
Date format is ‘DDMONYYYY’
VAT_RATE Numeric 20,10 Y VAT rate as a percentage. VAT_RATE NUMBER(20,10)
DC_VAT_REGION Table
File name: DC_VAT_REGION.DAT
Table create SQL script: DBC_CREATE_VAT_REGION_TAB.SQL
External Oracle table created: DC_VAT_REGION
Ensure that VAT_REGION.VAT_REGION is unique.
Capture the count from VAT_REGION and compare to flat file
DC_VAT_REGION.DAT to ensure that all rows are loaded.
Length
VAT_ Numeric 4 Y Unique identifier of VAT VAT_REGION NUMBER(4)

REGION region. VAT region is
determined by the VAT
authority.
VAT_REGION_ Alpha- 120 Y Name/description of the VAT_REGION_ VARCHAR2(120)
NAME numeric VAT region. NAME
VAT_REGION_ Alpha- 6 Y VAT region type. Valid VAT_REGION_ VARCHAR2(6)
TYPE numeric values include: TYPE
E for base EU members
M for EU members
N for nonmembers of the EU

UDA
UDA
DC_UDA Table
File name: DC_UDA.DAT
Table create SQL script: DBC_CREATE_UDA_TAB.SQL
Ensure that UDA.UDA_ID is unique.
Capture the count from UDA and compare to flat file DC_UDA.DAT to ensure that
all rows are loaded.
Length
UDA_ID Numeric 5 Y Unique identifier of user- UDA_ID NUMBER(5)

defined attribute.
UDA_DESC Alpha- 120 Y Description of user-defined UDA_DESC VARCHAR2(120)
numeric attribute. UDAs do not have
specific processing within
RMS.
DISPLAY_ Alpha- 2 Y How the UDA will be DISPLAY_TYPE VARCHAR2(2)
TYPE numeric displayed to the user online.
Valid values are:
LV - List of values
FF - Free-form text
DT - Date
Note: A UDA with DISPLAY_
TYPE ‘LV’ must also have a
corresponding UDA record in
DC_UDA_VALUES.DAT.
DATA_TYPE Alpha- 12 N Data type associated with the DATA_TYPE VARCHAR2(12)
numeric UDA.
If display_type =’DT’, the
data_type should be ‘DATE’. If
no value is provided in the flat
file, the default value is
‘DATE’.
If display_type = ‘FF’, the
data_type should be ‘ALPHA’.
If no value is provided in the
flat file, the default value is
‘ALPHA’.
If display_type = ‘LV’, the
data_type can either be ‘NUM’
or ‘ALPHA’. If no value is
provided in the flat file, the
default value is ‘ALPHA’.

UDA
DATA_ Numeric 3 N Maximum length of the UDA DATA_LENGTH VARCHAR2(3)

LENGTH values. This field should not be
populated for a ‘DT’ display
type. It is optional for ‘FF’ and
‘LV’ display types.
For ‘LV’, this constrains what is
stored in UDA_VALUES.
UDA_VALUE_DESCRIPTION.
For ‘FF’, this constrains what is
stored in UDA_ITEM_FF.
UDA_TEXT.
SINGLE_ Alpha- 1 Y Whether this UDA can have SINGLE_ VARCHAR2(1)
VALUE_IND numeric only one value associated with VALUE_IND
an item. If ‘Y’, only one value
of the UDA can be associated
with an item.
DC_UDA_VALUES Table
File name: DC_UDA_VALUES.DAT
Table create SQL script: DBC_CREATE_UDA_VALUES_TAB.SQL
External Oracle table created: DC_UDA_VALUES
Ensure that UDA_VALUES.UDA_ID is a valid UDA.UDA_ID.
Capture the count from UDA_VALUES and compare to flat file
DC_UDA_VALUES.DAT to ensure that all rows are loaded.
Length
UDA_ID Numeric 5 Y Unique identifier of user- UDA_ID NUMBER(5)

defined attribute. This
applies only to UDAs with
‘LV’ display type. This ties the
record to the appropriate
DC_UDA record.
UDA_VALUE_ Alpha- 250 Y Description of the UDA value. UDA_VALUE_ VARCHAR2(250)
DESC numeric DESC

Ticket Type
Ticket Type
DC_TICKET_TYPE_HEAD Table
File name: DC_TICKET_TYPE_HEAD.DAT
Table create SQL script: DBC_CREATE_TICKET_TYPE_HEAD_TAB.SQL
External Oracle table created: DC_TICKET_TYPE_HEAD
Ensure that TICKET_TYPE_HEAD.TICKET_TYPE_ID is unique.
Capture the count from TICKET_TYPE_HEAD and compare to flat file
DC_TICKET_TYPE_HEAD.DAT to ensure that all rows are loaded.
Length
TICKET_ Alpha- 4 Y Unique identifier of ticket or TICKET_TYPE_ID VARCHAR2(4)

TYPE_ID numeric label type.
TICKET_ Alpha- 120 Y Description of ticket or label TICKET_TYPE_ VARCHAR2(120)
TYPE_DESC numeric type. DESC
SHELF_ Alpha- 1 Y Whether this is a shelf edge SEL_IND VARCHAR2(1)
EDGE_IND numeric label.
Default = ‘N’

Ticket Type
DC_TICKET_TYPE_DETAIL Table
File name: DC_TICKET_TYPE_DETAIL.DAT
Table create SQL script: DBC_CREATE_TICKET_TYPE_DETAIL_TAB.SQL
External Oracle table created: DC_TICKET_TYPE_DETAIL
Ensure that TICKET_TYPE_DETAIL.TICKET_TYPE_ID is a valid
TICKET_TYPE_HEAD.TICKET_TYPE_ID.
Ensure that TICKET_TYPE_DETAIL.TICKET_ITEM_ID (if not NULL) is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘TCKT’.
Ensure that TICKET_TYPE_DETAIL.UDA_ID (if not NULL) is a valid
UDA.UDA_ID.
Capture the count from TICKET_TYPE_DETAIL and compare to flat file
DC_TICKET_TYPE_DETAIL.DAT to ensure that all rows are loaded.
Type Length
TICKET_ Alpha- 4 Y Unique identifier of ticket or label TICKET_TYPE_ID VARCHAR2(4)

TYPE_ID numeric type. This ties the record to the
appropriate DC_TICKET_TYPE_
HEAD record.
TICKET_ Alpha- 4 N Identifier of type of TICKET_ITEM_ID VARCHAR2(4)
ITEM_ID numeric information/attribute to be
displayed on ticket or label type.
Valid values come from the
TCKT CODE_TYPE. If this field
is populated, the UDA_ID field
should not be populated.
UDA_ID Numeric 5 N If the information to be displayed UDA_ID NUMBER(5)
on the ticket or label type is a
user defined attribute (UDA), this
field should be populated with
the UDA_ID. This value comes
from the UDA.UDA_ID field. If
this field is populated, the
TICKET_ITEM_ID field should
not be populated.
Note: If any records are in the BAD or DISCARD file, the

RMS table must be truncated and the entire file must be
rerun. No new records within a sequence group can be
added to the RMS table through the scripts.

Diff IDs—DC_DIFF_IDS Table
Diff IDs—DC_DIFF_IDS Table

File name: DC_DIFF_IDS.DAT
Table create SQL script: DBC_CREATE_DIFF_IDS_TAB.SQL
External Oracle table created: DC_DIFF_IDS
Ensure that DIFF_IDS.DIFF_ID is unique.
Ensure that DIFF_IDS.DIFF_TYPE is a valid CODE_DETAIL.CODE where
CODE_DETAIL.CODE_TYPE = ‘DIFF’.
Capture the count from DIFF_IDS and compare to flat file DC_DIFF_IDS.DAT to
ensure all that rows are loaded.
Type Length
DIFF_ID Alpha- 10 Y Unique identifier of the DIFF_ID VARCHAR2(10)

numeric differentiator.
DIFF_TYPE Alpha- 6 Y Differentiator group DIFF_TYPE VARCHAR2(6)
numeric associated to the
differentiator. Valid values
are from the DIFF_TYPE
column in the DIFF_TYPE
table. Examples are ‘C’ for
Color and ‘F’ for Flavor.
DIFF_DESC Alpha- 120 Y Description of the DIFF_DESC VARCHAR2(120)
numeric differentiator. Examples are
‘Red’, ‘Stripe’, ‘Strawberry’.
INDUSTRY_ Alpha- 10 N Unique number that INDUSTRY_ VARCHAR2(10)
CODE numeric represents all possible CODE
combinations of sizes,
according to the National
Retail Federation. Optional.
INDUSTRY_ Alpha- 10 N Unique number that INDUSTRY_ VARCHAR2(10)
SUBGROUP numeric represents all different color SUBGROUP
range groups, according to
the National Retail
Federation. Optional.

DC_LOAD_CORE.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:
1. It calls the create table scripts to create the external Oracle tables.
2. It calls the load data script to insert data from the external Oracle tables to the RMS
tables.
The script can be configured to create the tables and load data, or just load data at run
time. The create table scripts are called only if a parameter option (-c) is passed from the
command line. By default (without the option -c), this script loads the data.
The dc_load_core.ksh script utilizes a common library file and configuration file. The
library file contains functions common across all segment wrapper scripts. The
configuration file defines the directories used by the data conversion scripts.
For a specific function to process the load, the script checks a status file. If the data file is
valid, one unique status file (*.status) is generated per function, if it does not yet exist, to
signal that a load has started. If the file already exists, the script skips the load and writes
a message to the log file. For the data file to be valid, it must satisfy these requirements:
It must exist in the data directory defined in the common configuration file
It must allow read access.
It must contain information (have a size greater than 0).
The next topics describe the load functions that are included in the load script.
LOAD_CUSTOMER
This function contains a PL/SQL block that selects from the DC_ CUSTOMER external
table and inserts the data to the RMS CUSTOMER table. All the columns from the
external Oracle table defined previously map directly to the RMS table. The fields that
are not required are NULL.
The function returns a Boolean value.
Required file to load: dc_customer.dat
LOAD_TERMS_HEAD
This function contains a PL/SQL block that selects from the DC_ TERMS_HEAD external
table and inserts the data to the RMS TERMS_HEAD table. All the columns from the
external Oracle table defined previously map directly to the RMS table. The fields that
are not required are NULL.
Required file to load: dc_terms_head.dat
LOAD_TERMS_DETAIL
This function contains a PL/SQL block that selects from the DC_ TERMS_DETAIL
external table and inserts the data to the RMS TERMS_DETAIL table. All the columns
from the external Oracle table defined previously map directly to the RMS table. The
fields that are not required are NULL.
Required files to load: dc_terms_head.dat, dc_terms_detail.dat
LOAD_FREIGHT_TYPE
This function contains a PL/SQL block that selects from the DC_FREIGHT_TYPE
external table and inserts the data to the RMS FREIGHT_TYPE table. All the columns

from the external Oracle table defined previously map directly to the RMS table. All are
required.
Required file to load: dc_freight_type.dat
LOAD_FREIGHT_TERMS
This function contains a PL/SQL block that selects from the DC_FREIGHT_TERMS
external table and inserts the data to the RMS FREIGHT_TERMS table. All the columns
from the external Oracle table defined previously map directly to the RMS table.
Required file to load: dc_freight_terms.dat
LOAD_FREIGHT_SIZE
This function contains a PL/SQL block that selects from the DC_FREIGHT_SIZE external
table and inserts the data to the RMS FREIGHT_SIZE table. All the columns from the
external Oracle table defined previously map directly to the RMS table. All are required.
Required file to load: dc_freight_size.dat
LOAD_VAT_CODES
This function contains a PL/SQL block that selects from the DC_VAT_CODE,
DC_VAT_CODE_RATE,S and DC_VAT_REGION external tables and inserts the data to
the RMS VAT_CODE, VAT_CODE_RATES, and VAT_REGION tables. All the columns
from the external Oracle tables defined previously map directly to the RMS tables. All are
required.
Required file to load: dc_vat_codes.dat
LOAD_VAT_CODE_RATES
This function contains a PL/SQL block that selects from the DC_VAT_CODE_RATES
external table and inserts the data to the RMS VAT_CODE_RATES table. All the columns
following table defines the default values in the RMS table if no information is provided
in the data file (external table field values are NULL or not defined).
DC_VAT_CODE_RATES to VAT_CODE_RATES Column Defaults
Field Name (RMS Table) Default Value Comments
CREATE_DATE SYSDATE Date the record was created in RMS. This defaults
to the system date.
CREATE_ID Oracle User User who created the record in RMS. This defaults
to the Oracle User.
Required files to load: dc_vat_codes.dat (required to verify if VAT_CODES was loaded

previously), dc_vat_code_rates.dat

LOAD_VAT_REGION
This function contains a PL/SQL block that selects from the DC_VAT_REGION external
table and inserts the data to the RMS VAT_REGION table. All the columns from the
external Oracle table defined previously map directly to the RMS table. All are required.
Required file to load: dc_vat_region.dat
LOAD_UDA
This function contains a PL/SQL block that selects from the DC_UDA external table and
inserts the data into the RMS UDA table. All the columns from the external Oracle table
defined previously map directly to the RMS table. The following table defines the default
values in the RMS table if no information is provided in the data file (external table field
values are NULL or not defined).
The function returns a Boolean value
DC_UDA to UDA Column Defaults
MODULE ITEM Functional area of RMS to which this applies.

The only valid value is ‘ITEM’.
DATA_TYPE ALPHA (FF/LV*) If display_type =’DT’, the data type should
DATE (DT) be ‘DATE’. If no value is provided in the flat
file, the default value is ‘DATE’.
NUM(LV*)
If display_type = ‘FF’, the data_type should
be ‘ALPHA’. If no value is provided in the
flat file, the default value is ‘ALPHA’.
If display_type = ‘LV’, the data_type can
either be ‘NUM’ or ‘ALPHA’. If no value is
provided in the flat file, the default value is
‘ALPHA’.
Required file to load: dc_uda.dat
LOAD_UDA_VALUES
This function contains a PL/SQL block that selects from the DC_UDA_VALUES external
table and inserts the data into the RMS UDA_VALUES table. UDA_VALUES should
contain information if the DATA_TYPE value in the UDA table is LV. All the columns
following table defines the default values in the RMS table if no information is provided
The function returns a Boolean value
DC_UDA_VALUES to UDA_VALUES Column Defaults
UDA_VALUE Sequence generated Per UDA_ID
Required files to load: dc_uda.dat (required to check if UDA is loaded previously),

dc_uda_values.dat

LOAD_TICKET_TYPE_HEAD
This function contains a PL/SQL block that selects from the DC_TICKET_TYPE_HEAD
external table and inserts the data into the RMS TICKET_TYPE_HEAD table. All the
columns from the external Oracle table defined previously map directly to the RMS table.
Required files to load: dc_ticket_type_head.dat
LOAD_TICKET_TYPE_DETAIL
This function contains a PL/SQL block that selects from the DC_TICKET_TYPE_DETAIL
external table and inserts the data into the RMS TICKET_TYPE_DETAIL table. All the
columns from the external Oracle table defined previously map directly to the RMS table.
The following table defines the default values in the RMS table if no information is
provided in the data file (external table field values are NULL or not defined). There
should be a value in TICKET_ITEM_ID or UDA_ID, but not both.
DC_TICKET_TYPE_DETAIL to TICKET_TYPE_DETAIL Column Defaults
SEQ_NO Sequence generated Per TICKET_TYPE_ID
Required files to load: dc_ticket_type_head.dat (required to check if

TICKET_TYPE_HEAD is loaded previously), dc_ticket_type_detail.dat
LOAD_DIFF_IDS
This function contains a PL/SQL block that selects from the DC_DIFF_IDS external table
and inserts the data to the RMS DIFF_IDS table. All the columns from the external Oracle
table defined previously map directly to the RMS table. All are required.
provided in the data file (external table field values are NULL or not defined). There
should be a value in TICKET_ITEM_ID or UDA_ID, but not both.
DC_DIFF_IDS to DIFF_IDS Column Defaults
CREATE_DATETIME sysdate Date/time the record was created in RMS.

This defaults to the system date.
LAST_UPDATE_ID Oracle User User who last updated the record in RMS.
This defaults to the Oracle User.
LAST_UPDATE_DATETIME sysdate Date/time the record was last modified in
RMS. This defaults to the system date.
Required file to load: dc_diff_ids.dat

Post-Loading Requirements
After using the data conversion toolset for this functional area, there are additional tables
that must be loaded manually before you proceed with data conversion for subsequent
functional areas, because of data dependencies.
Manual data loading can be performed online through Merchandising applications
(RMS, RPM), or scripts can be created. Manual data loading is not included as part of this
data conversion toolset. Check with your database administrator to determine the best
approach for your data conversion needs.
The following table lists the tables that require manual data loading and indicates
whether each table is required or optional:
Table Required / Optional
DIFF_GROUP_HEAD Required
DIFF_GROUP_DETAIL Required
DIFF_RANGE_HEAD Optional
DIFF_RANGE_DETAIL Optional

4
This chapter describes data conversion for the following RMS/RPM tables, listed in the
order that they must be loaded:
DEPS
CLASS
SUBCLASS
STOCK_LEDGER_INSERTS
VAT_DEPS
UDA_ITEM_DEFAULTS
RPM_DEPT_AGGREGATION
This main script is used across all functional areas to call segment load script. Refer
Segment load script dc_load_merch.ksh
This wrapper calls the external Oracle table create and load scripts listed below.
– dbc_create_merch_defaults_tab.sql
– dbc_create_deps_tab.sql
– dbc_create_class_tab.sql
– dbc_create_subclass_tab.sql
– dbc_create_vat_deps.sql
– dbc_create_uda_item_def.sql

Data Flow
Data Flow
The following diagram shows the data flow for the Merchandise Hierarchy functional
area:
Prerequisites
Before you begin using the data conversion toolset for Merchandise Hierarchy, you must
complete data conversion for the Core functional area.
There are tables that must be loaded manually, because of data dependencies for auto-
loading within this functional area. Manual data loading can be done online through
The following required tables must be loaded manually:
COMPHEAD
DIVISION
GROUPS
You must retrieve the assigned data value UDA_VALUES.UDA_VALUE to create the
DC_UDA_ITEM_DEFAULT.DAT flat file (see the section “UDA Item Defaults—
DC_UDA_ITEM_DEFAULTS Table” in this chapter).


The dc_wh_org.ksh script calls each of the SQL scripts in a specific order. The SQL scripts
create external Oracle tables from flat file feeds and load data into the Oracle Retail
Merchandising database.
File Format



Department—DC_DEPS Table
File name: DC_DEPS.DAT
Table create SQL script: DBC_CREATE_DEPS_TAB.SQL
External Oracle table created: DC_DEPS
This table is used to load the RMS DEPS and the RPM RPM_DEPT_AGGREGATION
tables.
Suggested post-loading validation (sequence after dc_load_merch.ksh):
Ensure that DEPS.DEPT is unique.
Ensure that DEPS.GROUP_NO is a valid GROUPS.GROUP_NO.
Ensure DEPS.BUYER (if not NULL) is a valid BUYER.BUYER.
Ensure DEPS.MERCH (if not NULL) is a valid MERCHANT.MERCH.
Capture the counts from DEPS and RPM_DEPT_AGGREGATION and compare to
flat file DC_DEPS.DAT to ensure that all rows are loaded.
Length
MERCH_HIER_4 Integer 4 Y Unique identifier of the DEPT NUMBER(4)

merchandise hierarchy
level 4.
MERCH_HIER_4_ Alpha- 120 Y Description of the DEPT_NAME VARCHAR2(120)
DESC numeric merchandise hierarchy
level 4.
BUYER Integer 4 N Buyer for this merchandise BUYER NUMBER(4)
hierarchy level 4. Valid
values are from the BUYER
field in the BUYER table in
RMS.
MERCHANDISER Integer 4 N Merchandiser for this MERCH NUMBER(4)
merchandise hierarchy level
4. Valid values are from the
MERCH column in the
MERCHANT table in RMS.
PROFIT_CALC_ Integer 1 N Method of accounting for PROFIT_ NUMBER(1)
TYPE the stock ledger. Valid CALC_TYPE
values are:
1 - Direct cost
2 - Retail inventory
If no value is specified in the
taken from the
MERCH_HIER_4_
PROFIT_CALC_TYPE field
in the DC_MERCH_
DEFAULTS file.

MERCHANDISE_ Integer 1 N Type of merchandise in this PURCHASE_ NUMBER(1)

TYPE merchandise hierarchy level TYPE
4. Valid values are:
0 - Normal merchandise
1 - Consignment stock
2 - Concession items
taken from the
MERCH_HIER_4_
PURCHASE_TYPE field in
the DC_MERCH_
DEFAULTS file.
MERCH_HIER_3 Integer 4 Y Identifier of the GROUP_NO NUMBER(4)
3 of which merchandise
hierarchy level 4 is a
member. Valid values are in
the GROUP_NO field in the
GROUPS table in RMS.
MERCH_HIER_4_ Numeric 12,4 N Budgeted intake or markup MRKUP_PCT NUMBER(12,4)
MRKUP_PCT percentage. The intake
percentage, which is the
percent of total take that is
income, and the markup
percent are calculated from
this percent base on the
value given as the
MARKUP_CALC_TYPE.
taken from the
MARKUP_PCT field in the
DC_MERCH_DEFAULTS
file.
TOTAL_ Numeric 24,4 Total market amount TOTAL_ NUMBER(24,4)
MARKET_AMT expected for this MARKET_
merchandise hierarchy AMT
level 4. Optional.

MARKUP_CALC_ Alpha- 2 N How markup is calculated MARKUP_ VARCHAR2(2)

TYPE numeric for this merchandise CALC_TYPE
values are:
C - Cost
R - Retail
taken from the
MERCH_HIER_4_
MARKUP_CALC_TYPE
field in the DC_MERCH_
DEFAULTS file.
OTB_CALC_TYPE Alpha- 1 N How open to buy is OTB_CALC_ VARCHAR2(1)
numeric calculated for this TYPE
4. Valid values are:
C - Cost
R - Retail
taken from the MERCH_
HIER_4_OTB_CALC_TYPE
DEFAULTS file.

MAX_AVG_ Integer 5 N Maximum count of days MAX_AVG_ NUMBER(5)

COUNTER with acceptable data to COUNTER
include in projected sales for
items within the
merchandise hierarchy 4.
This provides a way to
weight the existing sales
value in the IF_RPM_
SMOOTHED_AVG table
against new values received.
The purpose of this table is
to populate projected sales
in RPM.
If the item has a relatively
minimal seasonal swing,
this value can be set higher,
so that the value will remain
stable and take many
anomalies to move the
value. If the item has a
relatively strong seasonal
swing, the counter should
be set to a lower number, so
that the value is more easily
moved to reflect a trend or
seasonal shift.
Required if RPM is used.
Default value is 1.
AVG_ Numeric 12,4 N Tolerance percentage used AVG_ NUMBER(12,4)
TOLERANCE_ in averaging sales for items. TOLERANCE_
PCT Used to determine if a sales PCT
amount received falls within
a reasonable range to be
considered in the
calculation. Values that fall
outside the range are
considered outliers and are
capped at the high or low of
the range. This is used by
ReSA to populate the
IF_RPM_SMOOTHED_
AVG table. The purpose of
this table is to populate
projected sales in RPM.
This value sets up a range
for appropriate data. The
value should be entered as a
whole integer; for example,
70 represents 70%.
Required if RPM is used.
Default value is 1.

VAT_IN_RETAIL Alpha- 1 N Whether retail is held with DEPT_VAT_ VARCHAR2(1)

numeric or without VAT. If VAT is INCL_IND
not turned on in RMS, then
this value should be ‘N’.
taken from the MERCH_
HIER_4_VAT_IN_RETAIL
DEFAULTS file.
VAT_REGION Integer 4 Y* Unique identifier of VAT VAT_REGION NUMBER(4)
region. VAT region is
determined by the VAT
authority. This value
should tie to a value from
the DC_VAT_REGION.DAT
file.
This field is required when
VAT is turned on in RMS.
VAT_TYPE Alpha- 1 Y* Whether the VAT rate is VAT_TYPE VARCHAR2(1)
numeric used for purchasing (Cost),
selling (Retail), or both.
Valid values are from the
‘VTTP’ code type: ‘C’, ‘R’,
or ‘B’.
VAT_CODE Alphanum 6 Y* Unique identifier of value VAT_CODE VARCHAR2(6)
eric added tax code. Valid
values are:
S - Standard
C- Composite
Z - Zero
E - Exemprt
This value should
correspond to a value from
the dc_vat_codes.dat file.
LOWEST_ Integer 6 N Lowest level at which a LOWEST_ NUMBER(6)
STRATEGY_ strategy can be defined. STRATEGY_
LEVEL Valid values are: LEVEL
0 - Merchandise hierarchy 4
flat file, the default value
is 0.

WORKSHEET_ Integer 6 N Which merchandise WORKSHEET_ NUMBER(6)

LEVEL hierarchy level is used to LEVEL
build worksheets for pricing
strategies in RPM. This
value should be at or above
the value in the
LOWEST_STRATEGY_
LEVEL. Valid values are:
is 0.
HISTORICAL_ Integer 6 Which period is used by the HISTORICAL_ NUMBER(6)
SALES_PERIOD merchandise extract to RPM SALES_LEVEL
when extracting historical
sales. Valid values are:
0 - Week
1 - Month
2 - Half year
3 - Year
is 0.
REGULAR_ Integer 6 N Whether regular price sales REGULAR_ NUMBER(6)
SALES_IND are included as part of the SALES_IND
historical sales extracted by
the merchandise extract to
RPM. Valid values are:
0 - Do not include
1 - Include
is 0.
CLEARANCE_ Integer 6 N Whether clearance price CLEARANCE_ NUMBER(6)
SALES_IND sales are included as part of SALES_IND
the historical sales extracted
by the merchandise extract
to RPM. Valid values are:
0 - Do not include
1 - Include
is 0.

PROMOTIONAL_ Integer 6 N Whether promotional price PROMOTIONA NUMBER(6)

SALES_IND sales are included as part of L_
the historical sales extracted SALES_IND
by the merchandise extract
to RPM. Valid values are:
0 - Do not include
1 - Include
is 0.
INCLUDE_WH_ Integer 6 N Indicator used by the INCLUDE_WH_ NUMBER(6)
ON_HAND merchandise extract to RPM ON_HAND
to determine whether to
include the warehouse on
hand quantity when
calculating sell thru and
price change impact.
is 0.
INCLUDE_WH_ Integer 6 N Indicator used by the INCLUDE_WH_ NUMBER(6)
ON_ORDER merchandise extract to RPM ON_ORDER
to determine whether to
include the warehouse on
order quantity when
calculating the total on
order quantity.
is 0.
PRICE_CHANGE_ Integer 6 N Calculation method for the PRICE_CHANG NUMBER(6)
AMOUNT_ price change amount E_
CALC_TYPE column on the Worksheet AMOUNT_
and Worksheet status CALC_TYPE
screens. Valid values are:
0 - Current-New
1 - New-Current
is 0.
RETAIL_CHG_ Integer 4 N Defines a window of recent RETAIL_CHG_ NUMBER(4)
HIGHLIGHT_ price changes. The HIGHLIGHT_
DAYS worksheet will highlight DAYS
past price changes that fall
within this window of time.
COST_CHG_ Integer 4 N Defines a window of recent COST_CHG_ NUMBER(4)
HIGHLIGHT_ cost changes. The worksheet HIGHLIGHT_
DAYS will highlight past cost DAYS
changes that fall within this
window of time.

PEND_COST_ Integer 4 N How many days forward PEND_COST_ NUMBER(4)

CHG_WINDOW_ the worksheet will look to CHG_
DAYS find upcoming cost changes. WINDOW_
DAYS
PEND_COST_ Integer 4 N Defines a window of PEND_COST_ NUMBER(4)
CHG_ upcoming cost changes. The CHG_
HIGHLIGHT_ worksheet will highlight HIGHLIGHT_
DAYS upcoming cost changes that DAYS
fall within this window of
time.

Merchandise Hierarchy Defaults—DC_MERCH_DEFAULTS Table

File name: DC_MERCH_DEFAULTS.DAT
Table create SQL script: DBC_CREATE_MERCH_DEFAULTS_TAB.SQL
External Oracle table created: DC_MERCH_DEFAULTS
The purpose of this table is a place to indicate more system-wide defaults.
DEFAULT_ALL_MERCH_HIER_5 and 6 are used to default class or subclass values
(create only one class or subclass per department or class). If DEFAULT_CLASS is ‘Y’,
only one class and subclass is inserted per department. If DEFAULT_SUBCLASS is ‘Y’,
one subclass is inserted for each class. If defaulting is used, the corresponding
DC_SUBCLASS.DAT and DC_CLASS.DAT (if applicable) files are assumed to be empty.
Length
DEFAULT_ Alpha- 1 Y Whether to default the DEFAULT_ VARCHAR2(1)

MERCH_ numeric merchandise hierarchy levels 5 CLASS
HIER_5 and 6 (RMS class and subclass)
records based on each
merchandise hierarchy level 4
(RMS department). Valid values
are ‘Y’ and ‘N’.
DEFAULT_ Alpha- 1 Y Whether to default the DEFAULT_ VARCHAR2(1)
MERCH_ numeric merchandise hierarchy level 6 SUBCLASS
HIER_6 (RMS subclass) records. If
DEFAULT_MERCH_HIER_5
(RMS class) is ‘Y’es, then it is
assumed that MERCH_HIER_6
values will be defaulted as well.
Valid values are ‘Y’ and ‘N’.
MERCH_ Integer 1 Y Default value to be used for all DEPT_PROFIT_ NUMBER
HIER_4_ MERCH_HIER_4 records that do CALC_TYPE
PROFIT_ not have a profit calc type value
CALC_TYPE specified. Valid values are:
1 - Direct cost
2 - Retail inventory
MERCH_ Integer 1 N Purchase type default value for DEPT_ NUMBER
HIER_4_ MERCH_HIER_4 records that do PURCHASE_
PURCHASE_ not have a merchandise type TYPE
TYPE value specified. Valid values are:
0 - Normal merchandise
1 - Consignment stock
2 - Concession items

MERCH_ Integer 12,4 Y Whether the field specifies the DEPT_MRKUP_ NUMBER
HIER_4_ intake, or markup is determined PCT
MRKUP_ by the value of the DC_DEPS.
PCT MARKUP_CALC_TYPE field.
A value of ‘R’ indicates that this
field specifies the budgeted
intake, which is synonymous
with markup percent of retail.
A value of ‘C’ indicates that this
field specifies the budgeted
markup, which is synonymous
with markup percent of cost.
If no value is specified in the flat
file, the default value is taken
from the MARKUP_PCT field in
the DC_MERCH_DEFAULTS
file.
MERCH_ Alpha- 2 Y Whether the markup calculation DEPT_ VARCHAR2
HIER_4_ numeric type should be ‘C’ost or ‘R’etail MARKUP_
MARKUP_ for MERCH_HIER_4 records. CALC_TYPE
CALC_TYPE Valid values are:
C - Cost
R – Retail
MERCH_ Alpha- 1 Y Whether the open to buy (OTB) DEPT_OTB_ VARCHAR2
HIER_4_OTB_ numeric calculation type should be ‘C’ost CALC_TYPE
CALC_TYPE or ‘R’etail for MERCH_HIER_4
records. Valid values are:
C - Cost
R – Retail
MERCH_ Alpha- 1 Y Whether retail is held with VAT DEPT_VAT_ VARCHAR2(1)
HIER_4_VAT_ numeric in the MERCH_HIER_4 level. If INCL_IND
IN_RETAIL VAT is not turned on in RMS,
MERCH_ Alpha- 1 Y Whether retail is held with VAT CLASS_VAT_ VARCHAR2(1)
HIER_5_VAT_ numeric in the MERCH_HIER_5 level. If INCL_IND
IN_RETAIL VAT is not turned on in RMS,

Class—DC_CLASS Table
Class—DC_CLASS Table
File name: DC_CLASS.DAT
Table create SQL script: DBC_CREATE_CLASS_TAB.SQL
External Oracle table created: DC_CLASS
Ensure that the CLASS.DEPT/CLASS.CLASS combination is unique.
Ensure that CLASS.DEPT is a valid DEPS.DEPT.
Capture the count from CLASS and compare to flat file DC_CLASS.DAT to ensure
that all rows are loaded.
Length
MERCH_ Integer 4 Y Identifier of the merchandise DEPT NUMBER(4)

HIER_4 hierarchy level 4 of which
merchandise hierarchy level 5 is
a member. Valid values are in
the DEPT field in the DEPS table.
MERCH_ Integer 4 Y Unique identifier of the CLASS NUMBER(4)
HIER_5 merchandise hierarchy level 5.
MERCH_ Alpha- 120 Y Description of the merchandise CLASS_NAME VARCHAR2(120)
HIER_5_ numeric hierarchy level 5.
NAME
VAT_IN_ Alpha- 1 N Whether retail is held with VAT. CLASS_VAT_ VARCHAR2(1)
RETAIL numeric If VAT is not turned on in RMS, IND
If no value is specified in the flat
file, the value is defaulted from
the MERCH_HIER_5_VAT_IN_
RETAIL field in the
DC_MERCH_DEFAULTS file.

Subclass—DC_SUBCLASS Table
Subclass—DC_SUBCLASS Table
File name: DC_SUBCLASS.DAT
Table create SQL script: DBC_CREATE_SUBCLASS_TAB.SQL
External Oracle table created: DC_SUBCLASS
Ensure that the SUBCLASS.DEPT/SUBCLASS.CLASS/SUBCLASS.SUBCLASS
combination is unique.
Ensure that the SUBCLASS..DEPT/SUBCLASS.CLASS combination exists in CLASS.
Capture the count from SUBCLASS and compare to flat file DC_SUBCLASS.DAT to
ensure that all rows are loaded.
Length

the DEPT field in the DEPS table.
MERCH_ Integer 4 Y Identifier of the merchandise CLASS NUMBER(4)
the CLASS field in the CLASS
table.
MERCH_ Integer 4 Y Unique identifier of the SUBCLASS NUMBER(4)
MERCH_ Alpha- 120 Y Description of the merchandise SUBCLASS_ VARCHAR2(120)
HIER_6_ numeric hierarchy level 6. NAME
NAME

VAT Departments—DC_VAT_DEPS Table
VAT Departments—DC_VAT_DEPS Table

File name: DC_VAT_DEPS.DAT
Table create SQL script: DBC_CREATE_VAT_DEPS.SQL
External Oracle table created: DC_VAT_DEPS
Ensure that every VAT_DEPS.VAT_REGION/VAT_DEPS.DEPT/
VAT_DEPS.VAT_TYPE combination is unique.
Ensure that VAT_DEPS.VAT_REGION is a valid VAT_REGION.VAT_REGION.
Ensure VAT_DEPS.DEPT is a valid DEPS.DEPT.
Ensure VAT_DEPS.VAT_CODE is a valid VAT_CODES.VAT_CODE.
Capture the count from VAT.DEPS and compare to flat file DC_VAT_DEPS.DAT to
File Format External Oracle Table

Definition
Length
MERCH_ Integer 4 Y Unique identifier of the DEPT NUMBER(4)

VAT_REGION Integer 4 Y* Unique identifier of VAT region. VAT_REGION NUMBER(4)
VAT region is determined by the
VAT authority. This value should
correspond to a value in the
DC_VAT_REGION.DAT file.
This field is required when VAT is
turned on in RMS.
VAT_TYPE Alpha- 1 Y* Whether the VAT rate is used for VAT_TYPE VARCHAR2(1)
numeric purchasing (Cost), selling (Retail),
or both. Valid values are from the
‘VTTP’ code type: ‘C’, ‘R’, or ‘B’.
turned on in RMS.
VAT_CODE Alpha- 6 Y* Unique identifier of VAT code. VAT_CODE VARCHAR2(6)
numeric Valid values include:
S - Standard
C - Composite
Z - Zero
E - Exempt
Other values can also be entered.
This value should correspond to a
value from the DC_VAT_CODES.
DAT file.
turned on in RMS.

UDA Item Defaults—DC_UDA_ITEM_DEFAULTS Table
UDA Item Defaults—DC_UDA_ITEM_DEFAULTS Table

File name: DC_UDA_ITEM_DEFAULTS.DAT
Table create SQL script: DBC_CREATE_UDA_ITEM_DEF.SQL
External Oracle table created: DC_UDA_ITEM_DEFAULTS
Ensure that the UDA_ITEM_DEFAULTS.UDA_ID/
UDA_ITEM_DEFAULTS.SEQ_NO combination is unique.
Ensure that UDA_ITEM_DEFAULTS.UDA_ID is a valid UDA.UDA_ID.
Ensure that UDA_ITEM_DEFAULTS.DEPT is a valid DEPS.DEPT.
Ensure that UDA_ITEM_DEFAULTS.DEPT/UDA_ITEM_DEFAULTS.CLASS
combination exists on CLASS (if UDA_ITEM_DEFAULTS.CLASS is not NULL).
Ensure that UDA_ITEM_DEFAULTS.DEPT/UDA_ITEM_DEFAULTS.CLASS/
UDA_ITEM_DEFAULTS.SUBCLASS combination exists on SUBCLASS (if
UDA_ITEM_DEFAULTS.SUBCLASS is not NULL).
Ensure that UDA_ITEM_DEFAULTS.UDA_ID/
UDA_ITEM_DEFAULTS.UDA_VALUE combination exists in UDA_VALUES (if
UDA_ITEM_DEFAULTS.UDA_VALUE is not NULL).
Capture the count from UDA_ITEM_DEFAULTS and compare to flat file
DC_UDA_ITEM_DEFAULTS.DAT to ensure that all rows are loaded.

Definition
Type Length
UDA_ID Integer 5 Y Unique identifier of the user- UDA_ID NUMBER(5)

defined attribute (UDA) to be
defaulted. Valid values are in the
UDA_ID field in the UDA table.
MERCH_ Integer 4 Y Merchandise hierarchy level 4 DEPT NUMBER(4)
HIER_4 associated with the defaulted UDA.
MERCH_ Integer 4 N Merchandise hierarchy level 5 CLASS NUMBER(4)
HIER_5 associated with the defaulted UDA.
Optional, but required if the
MERCH_HIER_6 field is populated.
MERCH_ Integer 4 N Merchandise hierarchy level 6 SUBCLASS NUMBER(4)
HIER_6 associated to the defaulted UDA.
Optional.
UDA_VALUE Integer 3 Y Only the UDA_ID DISPLAY_TYPE UDA_VALUE NUMBER(3)
of ‘LV’ is defaulted to the hierarchy
specified; therefore, UDA_VALUE
is required. Valid values are in the
UDA_VALUE field in the
UDA_VALUES table for the
UDA_ID specified.

DC_LOAD_MERCH.KSH Segment Wrapper / Load Script


tables.
The dc_load_merch.ksh script utilizes a common library file and configuration file. The
LOAD_DEPS
This function contains a PL/SQL block that selects from the DC_DEPS external table and
inserts the data to the RMS DEPS table. The following table defines the default values in
the RMS table if no information is provided in the data file (external table field values are
NULL or not defined).
If the DC_MERCH_DEFAULTS.DEFAULT_CLASS_IND is ‘Y’, only one class and
subclass are inserted for each department. The DC_DEPS and DC_MERCH_DEFAULT
tables are used to insert into the RMS CLASS and SUBCLASS tables. The ID and name
fields are the same as department for all classes and subclasses.
DC_DEPS to DEPS Column Defaults
PROFIT_CALC_TYPE DC_MERCH_DEFAULTS. If DC_DEPS.PROFIT_CALC_TYPE is

DEPT_PROFIT_CALC_TYP NULL, use the value from the
E MERCH_DEFAULTS table.
PURCHASE_TYPE NVL(SYSTEM.DEFAULTS. If DC_DEPS.PURCHASE_TYPE is
DEPT_PURCHASE_TYPE, NULL, use the value from the
0) MERCH_DEFAULTS table. If that is
NULL, then default to 0.

BUD_INT DC_MERCH_DEFAULTS. If DC_DEPS.MARKUP_CALC_TYPE is

DEPT_BUD_INT ‘R’, use DC_DEPS.MRKUP_PCT to
populate the DEPS.BUD_INT RMS
field.
If DC_DEPS.MRKUP_PCT is NULL,
use MERCH_DEFAULTS.
DEPT_MRKUP_PCT to populate
DEPS.BUD_INT.
If DC_DEPS.MARKUP_CALC_TYPE is
‘C’, populate the DEPS.BUD_INT RMS
field using the following equation:
BUD_INT =
round(DC_DEPS.MRKUP_PCT *
100/DC_DEPS.MRKUP_PCT + 100),4)
BUD_MKUP DC_MERCH_DEFAULTS. If DC_DEPS.MARKUP_CALC_TYPE is
DEPT_BUD_MKUP ‘C’, use DC_DEPS.MRKUP_PCT to
populate the DEPS.BUD_MKUP RMS
field.
If DC_DEPS.MRKUP_PCT is NULL,
use MERCH_DEFAULTS.
DEPT_MRKUP_PCT to populate
DEPS.BUD_MKUP.
If DC_DEPS.MARKUP_CALC_TYPE is
‘R’, populate the DEPS.BUD_MKUP
RMS field using the following
equation:
BUD_MKUP =
round(DC_DEPS.MRKUP_PCT *
100/(100 –
DC_DEPS.MRKUP_PCT),4)
MARKUP_CALC_TYPE DC_MERCH_DEFAULTS. If DC_DEPS.MARKUP_CALC_TYPE is
DEPT_MARKUP_CALC_T NULL, use the value from the
YPE MERCH_DEFAULTS table.
OTB_CALC_TYPE DC_MERCH_DEFAULTS. If DC_DEPS.OTB_CALC_TYPE is
DEPT_OTB_CALC_TYPE NULL, use the value from the
MERCH_DEFAULTS table.
DEPT_VAT_INCL_IND DC_MERCH_DEFAULTS. If DC_DEPS.DEPT_VAT_INCL_IND is
DEPT_VAT_INCL_IND NULL, use the value from the
MERCH_DEFAULTS table.
Required files to load: dc_merch_defaults.dat, dc_deps.dat

LOAD_CLASS
This function contains a PL/SQL block that selects from the DC_CLASS external table
and inserts the data to the RMS CLASS and possibly SUBCLASS tables.
Note: This load will not run if the subclasses are defaulted in
the LOAD_DEPS table (that is, no dc_class.dat file exists).
The dc_load_merch.ksh script determines whether to run
this function when the dc_class.dat file does not exist. The
script first gets the indicators from the DC_MERCH_
DEFAULTS table. If the DEFAULT_CLASS indicator is not
set to ‘Y’, the records from DC_CLASS are loaded into the
RMS CLASS table. If the DEFAULT_SUBCLASS indicator is
set to ‘Y’, only one subclass is inserted for each class. The
subclass ID defaults to the class ID value.
The following table defines the default value in the RMS table if no information is
provided in the data file (external table field values are NULL or not defined).
DC_CLASS to CLASS Column Defaults
CLASS_VAT_INCL_IND SYSTEM_DEFAULTS. If DC_CLASS.

CLASS_VAT_INCL_IND CLASS_VAT_INCL_IND is
NULL, use the value from
MERCH_DEFAULTS.
Required files to load: dc_merch_defaults.dat, dc_class.dat
LOAD _SUBCLASS
This function contains a PL/SQL block that selects from the DC_SUBCLASS external
table and inserts the data to the RMS SUBCLASS.
Note: This load will not be run if the subclasses are

defaulted in the LOAD_DEPS or LOAD_CLASSES functions
(that is, no dc_subclass.dat file). The dc_load_merch.ksh
script will determine whether to run/not run this function.
The script first gets the indicators from the DC_MERCH_
DEFAULTS table. If the DEFAULT_SUBCLASS indicator is
not set to ‘Y’, the function selects from DC_SUBCLASS and
inserts into the RMS SUBCLASS table.

Required files to load: dc_merch_defaults.dat, dc_subclass.dat
LOAD_STOCK_LEDGER_INS
This function creates records in the RMS STOCK_LEDGER_INSERTS table for every new
department and subclass loaded. The function performs an insert/select from the
DC_DEPS and DC_SUBCLASS tables to insert the appropriate information (with
type_code ‘D’ or ‘B’, respectively) into the STOCK_LEDGER_INSERTS table.
Required files to load: dc_deps.dat, dc_subclass.dat

LOAD_VAT_DEPS
This function selects data from the DC_VAT_DEPS table and inserts the records into the
RMS VAT_DEPS table, if the system options vat_ind is set to Y. All the columns from the
external Oracle table defined above map directly to the RMS table.
Required file to load: dc_vat_deps.dat
LOAD_UDA_ITEM_DEF
This function contains a PL/SQL block that selects from the DC_UDA_ITEM_
DEFAULTS external table and inserts the data to the RMS UDA_ITEM_DEFAULTS table.
All the columns from the external Oracle table defined above map directly to the RMS
table. The following table defines the default value in the RMS table if no information is
DC_UDA_ITEM_DEFAULTS to UDA_ITEM_DEFAULTS Column Defaults
SEQ_NO SEQ_NO + 1 Based on dept(class(subclass))

Use analytic function.
HIERARCHY_VALUE 1,2 or 3 If subclass is not NULL then 3; if class is not NULL
then 2; if dept is not NULL then 1.
REQUIRED_IND N Value defaults to N if the file value is empty.
Required file to load: dc_uda_item_defaults.dat
LOAD_RPM_DEPT_AGGREGATION
This function selects data from the DC_DEPS table and inserts the records into the
RPM_DEPT_AGGREGATION table. The following table defines the default values in the
RMS table if no information is provided in the data file (external table field values are
NULL or not defined).
DC_DEPS to RPM_DEPT_AGGREGATION Column Defaults
DEPT_AGGREGATION_ID Generated SEQ_NO

LOWEST_STRATEGY_LEVEL 0 Value defaults to 0 if the file value is
empty.
WORKSHEET_LEVEL 0 Value defaults to 0 if the file value is
empty.
HISTORICAL_SALES_LEVEL 0 Value defaults to 0 if the file value is
empty.
REGULAR_SALES_IND 0 Value defaults to 0 if the file value is
empty.
CLEARANCE_SALES_IND 0 Value defaults to 0 if the file value is
empty.
PROMOTIONAL_SALES_IND 0 Value defaults to 0 if the file value is
empty.

INCLUDE_WH_ON_HAND 0 Value defaults to 0 if the file value is

empty.
INCLUDE_WH_ON_ORDER 0 Value defaults to 0 if the file value is
empty.
PRICE_CHANGE_AMOUNT_ 0 Value defaults to 0 if the file value is
CALC_TYPE empty.
Required file to load: dc_deps.dat
Manual data loading can be done online through Merchandising applications (RMS,
RPM), or scripts can be created. Manual data loading is not included as part of this data
conversion toolset. Check with your database administrator to determine the best
The following are required tables that require manual data loading:
RPM_MERCH_RETAIL_DEF
HIERARCHY_PERMISSION (Retail Security Manager [RSM] table)
Additionally, all department UDA defaults must be set up manually where
UDA_ITEM_DEFAULTS.REQUIRED_IND = ‘Y’.

5
Organizational hierarchy data conversion is described in two sections in this chapter, and
data must be loaded in this order:
Warehouse
Store
Prerequisites
Before you begin using the data conversion toolset for Organizational Hierarchy, you
must complete data conversion for the following functional areas:
Core
CHAIN
AREA

Warehouse
Warehouse
This section describes data conversion for the following RMS tables, listed in the order
ADDR
WH
WH_ADD
STOCK_LEDGER_INSERTS
TRANSIT_TIMES (applicable to both store and warehouses)
COST_ZONE
COST_ZONE_GROUP_LOC
Segment load script dc_load_wh_org.ksh
– dbc_create_wh_addr_tab.sql
– dbc_create_vwh_tab.sql
– dbc_create_pwh_tab.sql
– dbc_create_transit_times_tab.sql
Data Flow
The following diagram shows the data flow for the Organizational Hierarchy Warehouse
functional area:

Warehouse

The dc_wh_org.ksh script calls each of the SQL scripts in a specific order. The SQL
File Format



Warehouse
DC_WH_ADDR Table
File name: DC_WH_ADDR.DAT
Table create SQL script: DBC_CREATE_WH_ADDR_TAB.SQL
External Oracle table created: DC_WH_ADDR
Suggested post-loading validation (sequence after dc_load_wh_org.ksh):
Ensure that ADDR.KEY_VALUE_1 is a valid WH.WH. If
SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’, ensure that
WH.STOCKHOLDING_IND = ‘N’.
Ensure that ADDR.STATE is a valid STATE.STATE.
Ensure that ADDR.COUNTRY_ID is a valid COUNTRY.COUNTRY_ID.
Capture counts from ADDR where ADDR.MODULE = ‘WH’ and compare to flat file
DC_WH_ADDR.DAT to ensure that all rows are loaded.
Length
WAREHOUSE_ Alpha- 20 Y Primary identifier for the KEY_VALUE_ VARCHAR2(20)

ID numeric warehouse for which the 1
address record applies.
In a multi-channel
environment, only the
physical warehouse should
contain address records, so
this field will contain
physical warehouse IDs.
ADDR_TYPE Alpha- 2 Y Type of address for this ADDR_TYPE VARCHAR2(2)
numeric warehouse. Valid values are:
01 - Business
02 - Postal
03 - Returns
04 - Order
05 - Invoice
06 - Remittance
Additional address types
can be defined in the RMS
ADD_TYPE table.
The required address types
for a warehouse are
definable in the RMS
ADD_TYPE_MODULE
table, where
MODULE=’WH’.
PRIMARY_ Alpha- 1 Y Whether this address is the PRIMARY_ VARCHAR2(1)
ADDR_IND numeric primary for the warehouse ADDR_IND
and address type. Valid
values are ‘Y’ (primary) and
‘N’ (non-primary).

Warehouse
CONTACT_ Alpha- 120 N Name of the primary contact CONTACT_ VARCHAR2(120)

NAME numeric person at this warehouse NAME
address.
CONTACT_ Alpha- 20 N Phone number of the contact CONTACT_ VARCHAR2(20)
PHONE numeric person at this warehouse PHONE
address.
CONTACT_ Alpha- 20 N Fax number of this CONTACT_ VARCHAR2(20)
FAX numeric warehouse address. FAX
CONTACT_ Alpha- 100 N E-mail address of the contact CONTACT_ VARCHAR2(100)
EMAIL numeric person at this warehouse EMAIL
address.
CONTACT_ Alpha- 20 N Telex number of the contact CONTACT_ VARCHAR2(20)
TELEX numeric person at this warehouse TELEX
address.
ADDR_LINE_1 Alpha- 240 Y First line of this address of ADD_1 VARCHAR2(240)
numeric this warehouse and address
type.
ADDR_LINE_3 Alpha- 240 N Second line of this address ADD_2 VARCHAR2(240)
numeric of this warehouse and
address type.
ADDR_LINE_2 Alpha- 240 N Third line of this address of ADD_3 VARCHAR2(240)
type.
CITY Alpha- 120 Y City of this address of this CITY VARCHAR2(120)
numeric warehouse and address
type.
COUNTY Alpha- 250 N County of this address of COUNTY VARCHAR2(250)
type.
STATE Alpha- 3 N State of this address of this STATE VARCHAR2(3)
numeric warehouse and address
type. Values in this column
must exist in the RMS
STATE table.
POSTAL_CODE Alpha- 30 N Postal code (for example, POST VARCHAR2(30)
numeric ZIP code) of this address for
this warehouse and address
type.
COUNTRY_ID Alpha- 3 Y Country code of this address COUNTRY_ID VARCHAR2(3)
numeric of this warehouse and
address type. Values in this
column must exist in the
RMS COUNTRIES table.

Warehouse
DC_PWH Table
File name: DC_PWH.DAT
Table create SQL script: DBC_CREATE_PWH_TAB.SQL
External Oracle table created: DC_PWH
Ensure that WH.WH is unique.
If WH.ORG_HIER_TYPE has a value of 1, ensure that WH.ORG_HIER_VALUE is a
valid COMPHEAD.COMPANY.
valid CHAIN.CHAIN.
valid AREA.AREA.
valid REGION.REGION.
valid DISTRICT.DISTRICT.
valid STORE.STORE (move to after running storeadd.pc).
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’ and
WH.STOCKHOLDING_IND = ‘Y’, ensure that WH.CHANNEL_ID is a valid
CHANNELS.CHANNEL_ID.
Ensure that WH.VAT_REGION is a valid VAT_REGION.VAT_REGION if
SYSTEM_OPTIONS.VAT_IND = ‘Y’ and WH.STOCKHOLIDNG_IND = ‘Y’.
Ensure that WH.CURRENCY_CODE is a valid CURRENCIES.CURRENCY_CODE.
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’ and
WH.STOCKHOLDING_IND = ‘N’, ensure that WH.PRIMARY_VWH is a valid
WH.WH with WH.STOCKHOLDING_IND = ‘Y’.
Ensure that WH.ORG_UNIT_ID (if not NULL) is a valid ORG_UNIT.ORG_UNIT_ID.
Ensure that WH.TSF_ENTITY_ID is a valid TSF_ENTITYT.TSF_ENTITY_ID if
SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = ‘Y’ and
WH.STOCKHOLIDNG_IND = ‘Y’.
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’, capture counts from WH where
WH.STOCKHOLDING_IND = ‘N’ and compare to flat file DC_PWH.DAT to ensure
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘N’, capture counts from WH and
compare to flat file DC_PWH.DAT to ensure that all rows are loaded.

Warehouse
Length
WAREHOUSE_ Integer 10 Y Unique identifier of the WH NUMBER(10)

ID warehouse being defined. In a
multi-channel environment,
this contains the physical
warehouse ID. In a single-
channel environment, there is
no distinction between
physical and virtual
warehouses.
This value must be unique
across all warehouses
(physical and virtual) and
stores.
WAREHOUSE_ Alpha- 150 Y Name of the warehouse being WH_NAME VARCHAR2(150)
NAME numeric defined.
PRIMARY_ Integer 10 N (Applicable only in a multi- PRIMARY_VWH NUMBER(10)
VIRTUAL_ channel environment.
WH_ID Required in a multi-channel
environment.)
Identifier of the primary
virtual warehouse within this
physical warehouse. The
value must be a valid virtual
warehouse loaded in the
VWH file that exists within
this physical warehouse.
The primary VWH is used
throughout RMS in various
transactions for which only a
physical warehouse has been
specified.
CURRENCY_ Alpha- 3 Y Currency in which all cost CURRENCY_ VARCHAR2(3)
CODE numeric and retail values for this CODE
warehouse will be
represented.
Valid values must exist in the
RMS CURRENCIES table.
BREAK_PACK_ Alpha- 1 N Whether this warehouse is BREAK_PACK_ VARCHAR2(1)
IND numeric capable of distributing less IND
than the supplier case
quantity (supplier pack size).

Warehouse
REDIST_WH_ Alpha- 1 N Whether this warehouse is REDIST_WH_ VARCHAR2(1)

IND numeric considered a redistribution IND
warehouse, which is a
dummy warehouse usable for
creating purchase orders in
advance of knowing the final
order to locations. This flag is
only used by the RMS Order
Redistribution report, as a
query criterion for displaying
POs that require
redistribution to the final
locations.
DELIVERY_ Alpha- 6 Y Warehouse delivery policy for DELIVERY_ VARCHAR2(6)
POLICY numeric shipments from the POLICY
warehouse.
Valid values are:
NEXT - Next day
NDD - Next delivery day
NEXT indicates that
deliveries are made on the
next warehouse open day.
NDD indicates that deliveries
are made only on scheduled
days.
FORECAST_ Alpha- 1 N Whether this warehouse is FORECAST_ VARCHAR2(1)
WH_IND numeric forecastable. Value values are WH_IND
‘Y’ and ‘N’. Only warehouses
with a value of ‘Y’ will be
visible to the forecasting tool
(RDF).
In a multi-channel
environment, this parameter
only needs to be defined for
virtual warehouses, so that it
can be passed as NULL for
the physical warehouse.
REPL_IND Alpha- 1 N Whether this warehouse can REPL_IND VARCHAR2(1)
numeric be used to replenish other
locations. Valid values are ‘Y’
and ‘N’. ‘Y’ indicates that
inventory from this
warehouse can be used to
replenish other locations.
In a multi-channel
environment, this parameter
only needs to be defined for
virtual warehouses, so that it
can be passed as NULL for

Warehouse
REPL_WH_ Integer 10 N Replenishable warehouse that REPL_WH_IND NUMBER(10)

LINK is linked to this virtual
warehouse. This link implies
that the virtual warehouse is
included in the net inventory
calculations for the
replenishable warehouse.
This field is should only be
definable in a single-channel
environment and where the
value in the repl_ind field is
‘N’.
IB_IND Alpha- 1 N Whether the warehouse is an IB_IND VARCHAR2(1)
numeric investment buy warehouse.
IB_WH_LINK Integer 10 N Investment buy warehouse IB_WH_LINK NUMBER(10)
that is linked to the virtual
investment buy warehouse.
This field should only contain
a value when the IB_IND is
‘N’.
AUTO_IB_ Alpha- 1 N Whether the investment buy's AUTO_IB_ VARCHAR2(1)
CLEAR numeric inventory should be CLEAR
automatically transferred to
the turn (replenishable)
warehouse when an order is
received by the turn
warehouse.
INBOUND_ Integer 2 Y Number of days that the INBOUND_ NUMBER(2)
HANDLING_ warehouse requires to receive HANDLING_
DAYS any item and get it to the shelf DAYS
so that it is ready to pick.
Valid value is a number from
0 to 99.
WH_NAME_ Alpha- 150 N Secondary description of the WH_NAME_ VARCHAR2(150)
SECONDARY numeric warehouse. This value is SECONDARY
used to support multi-
language, where the primary
description may contain
characters not easily sortable.
EMAIL Alpha- 100 N Primary e-mail for the EMAIL VARCHAR2(100)
numeric warehouse.

Warehouse
VAT_REGION Integer 4 N Required when VAT_IND in VAT_REGION NUMBER(4)

SYSTEM_OPTIONS is ‘Y’.
Contains the warehouse VAT
region, used by RMS to
determine the VAT rates
applicable at this location.
ORG_HIER_ Integer 4 N For reporting purposes, this ORG_HIER_ NUMBER(4)
TYPE field, along with the TYPE
ORG_HIER_VALUE field, can
be used to define a level and
value of the organizational
hierarchy with which this
warehouse is associated. This
field defines the level of the
organizational hierarchy
defined in the
ORG_HIER_VALUE field.
Valid values are:
1 - Company
10 - Chain
20 - Area
30 - Region
40 - District
50 – Store
ORG_HIER_ Integer 10 N (See ORG_HIER_TYPE ORG_HIER_ NUMBER(10)
VALUE description.) VALUE
ID of the organizational
hierarchy value as defined by
the ORG_HIER_TYPE. For
example, if the
ORG_HIER_TYPE is ‘20’
(area), this field should
contain a valid area ID.
DUNS_LOC Alpha- 4 N Dun and Bradstreet number DUNS_LOC VARCHAR2(4)
numeric used to identify the
warehouse location. This is
reference-only data.
DUNS_ Alpha- 9 N Dun and Bradstreet number DUNS_NUMBER VARCHAR2(9)
NUMBER numeric used to identify the
warehouse. This is reference-
only data.

Warehouse
DC_VWH Table
File name: DC_VWH.DAT
This VWH.DAT file contains the virtual warehouse locations details for each physical
warehouse. This file is to be created and loaded into RMS only when multi-channel
functionality is enabled (SYSTEM_OPTIONS. MULTICHANNEL_IND = ‘Y’). Otherwise,
this file is not necessary, and only the DC_PWH.DAT file is required.
Table create SQL script: DBC_CREATE_VWH_TAB.SQL
External Oracle table created: DC_VWH
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’, ensure that
WH.PHYSICAL_WH is a valid WH.WH with WH.STOCKHOLDING_IND = ‘N’.
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘Y’, capture counts from WH where
WH.STOCKHOLDING_IND = ‘Y’ and compare to flat file DC_VWH.DAT to ensure
Length
VIRTUAL_WH_ Integer 10 Y Unique identifier for the virtual WH NUMBER(10)

ID warehouse. This value must be
unique across all warehouses
(physical and virtual) and
stores.
VIRTUAL_WH_ Alpha- 150 Y Name for the virtual WH_NAME VARCHAR2(150)
NAME numeric warehouse being defined.
PHYSICAL_ Integer 10 Y ID of the physical warehouse in PHYSICAL_ NUMBER(10)
WAREHOUSE_ which this virtual warehouse WH
ID resides. To be valid, the
physical warehouse must
already exist in RMS and be
loaded separately from the
physical warehouse file.

Warehouse
RESTRICTED_ Alpha- 1 N Indicator used to restrict virtual RESTRICTED_ VARCHAR2(1)

IND numeric warehouses from receiving IND
stock during an inbound type
transaction (such as positive
SOH inventory adjustment, PO
over-receipt), when stock needs
to be prorated across virtual
warehouses within a physical
warehouse, because a virtual
warehouse in the physical
warehouse has not been
identified for the transaction.
The indicator restricts the
virtual warehouse from
receiving stock unless all valid
virtual warehouses determined
by the system are restricted;
then the stock will be
distributed across those
restricted virtual warehouses.
This indicator is used only in a
multi-channel environment. It
is always set to ‘N’ in a single-
channel environment.

Warehouse
PROTECTED_ Alpha- 1 N Whether the virtual warehouse PROTECTED_ VARCHAR2(1)

IND numeric is affected last in transactions IND
where inventory is removed, or
affected first in short-shipment
type transactions where
inventory is being added. The
indicator is used in any
outbound or inventory removal
type transactions (such as
returns to vendor [RTV],
negative stock on hand [SOH]
inventory adjustments), when
the system has to distribute the
transaction quantity across
virtual warehouses within a
physical warehouse for one of
these reasons:
A virtual warehouse has
not been specified or could
not be derived.
A virtual warehouse does
not have enough stock to
cover the transaction
quantity, and stock needs
to be pulled from other
virtual warehouses within
The indicator is also used for
inbound type transactions
where there is some sort of
short-shipment (for example, a
short-shipment for a PO). The
indicator determines which
virtual warehouses have their
order quantity fulfilled first
with the receipt quantity. Note
that this indicator does not
guarantee that stock will not be
pulled from the virtual
warehouse; it is only used to
ensure that the virtual
warehouse is affected last.
FORECAST_ Alpha- 1 N Whether this warehouse is FORECAST_ VARCHAR2(1)
WH_IND numeric forecastable. Value values are WH_IND
‘Y’ and ‘N’. Only warehouses
with a value of ‘Y’ will be
visible to the forecasting tool
(RDF).

Warehouse
REPL_IND Alpha- 1 N Whether this warehouse can be REPL_IND VARCHAR2(1)

numeric used to replenish other
locations. Valid values are ‘Y’
and ‘N’. ‘Y’ indicates that
inventory from this warehouse
can be used to replenish other
locations.
REPL_WH_ Integer 10 N Replenishable warehouse that REPL_WH_ NUMBER(10)
LINK is linked to this virtual LINK
replenishable warehouse.
IB_IND Alpha- 1 N Whether the warehouse is an IB_IND VARCHAR2(1)
numeric investment buy warehouse.
IB_WH_LINK Integer 10 N Investment buy warehouse that IB_WH_LINK NUMBER(10)
is linked to the virtual
calculations for the investment
buy warehouse.
This field should only contain a
value when the IB_IND is
equal to ‘N’.
AUTO_IB_ Alpha- 1 N Whether the investment buy AUTO_IB_ VARCHAR2(1)
CLEAR numeric inventory should be CLEAR
automatically transferred to the
turn (replenishable) warehouse
when an order is received by
the turn warehouse.
FINISHER_IND Alpha- 1 N Whether the virtual warehouse FINISHER_ VARCHAR2(1)
numeric performs finishing. Valid IND
values are ‘Y’ and ‘N’.
Each channel must have at least
one virtual warehouse that is
not a finisher location
(FINISHER_IND=’N’).
WH_NAME_ Alpha- 150 N Secondary description of the WH_NAME_ VARCHAR2(150)
SECONDARY numeric warehouse. This value is used SECONDARY
to support multi-language,
where the primary description
may contain characters not
easily sortable.

Warehouse
CHANNEL_ID Integer 4 Y Channel to which this virtual CHANNEL_ID NUMBER(4)

warehouse is assigned. Within
a given physical warehouse,
each virtual warehouse must
belong to a different channel.
Valid channel IDs must exist in
RMS and should be defined
before warehouses are created.
TSF_ENTITY_ID Integer 10 N Legal entity to which this TSF_ENTITY_ NUMBER(10)
virtual warehouse belongs. ID
This field is only required
when the system is operating
with multiple legal entities.
Valid values must exist in the
RMS tsf_entity table prior to
loading warehouses.

Warehouse
DC_TRANSIT_TIMES Table
File name: DC_TRANSIT_TIMES.DAT
Table create SQL script: DBC_CREATE_TRANSIT_TIMES_TAB.SQL
External Oracle table created: DC_TRANSIT_TIMES
Note: Although the RMS TRANSIT_TIMES table is loaded as

part of warehouse functionality, the origin field may contain
Store or Warehouse. Similarly, the destination field may
contain Store or Warehouse.
Suggested post-load validation (sequence after dc_load_wh_org.ksh):

Ensure that TRANSIT_TIMES.TRANSIT_TIMES_ID is unique.
Ensure that TRANSIT_TIMES.DEPT is a valid DEPS.DEPT.
Ensure that TRANSIT_TIMES.DEPT/TRANSIT_TIMES.CLASS combination exists
on CLASS (if TRANSIT_TIMES.CLASS is not NULL).
Ensure that TRANSIT_TIMES.DEPT/TRANSIT_TIMES.CLASS/
TRANSIT_TIMES.SUBCLASS combination exists on SUBCLASS (if
TRANSIT_TIMES.SUBCLASS is not NULL).
TRANSIT_TIMES.DESTINATION is a valid WH.WH, where
WH.STOCKHOLDING_IND = ‘N’ when TRANSIT_TIMES.DESTINATION_TYPE =
‘WH’.
If SYSTEM_OPTION.MULTICHANNEL_IND = ‘N’, ensure that
TRANSIT_TIMES.DESTINATION is a valid WH.WH when
TRANSIT_TIMES.DESTINATION_TYPE = ‘WH’.
Capture the count from TRANSIT_TIMES and compare to flat file
DC_TRANSIT_TIMES.DAT to ensure that all rows are loaded.
Length
TRANSIT_ Integer 10 Y Unique identifier of the record. TRANSIT_ NUMBER(10)

TIMES_ID This value can be sequence TIMES_ID
generated but must be unique
per record loaded.
MERCH_HIER_4 Integer 4 Y Identifier of the 4th level (from DEPT NUMBER(4)
the top down) of the
(department in the base
configuration) to which the
transit times record applies.
ORIGIN Integer 10 Y Identifier of the supplier or ORIGIN NUMBER(10)
location from which a
shipment would originate. The
identifier is a supplier ID, or a
location ID with location ID
type, depending on the value
specified in the origin_type
field.

Warehouse
DESTINATION Integer 10 Y Identifier of the location from DESTINATION NUMBER(10)

which a shipment would be
destined. The identifier is a
store ID or a warehouse ID,
depending on the value
specified in the destination_
type field.
ORIGIN_TYPE Alpha- 2 Y Identifier of the type of value ORIGIN_TYPE VARCHAR2(2)
numeric specified in the origin field.
Valid values are:
ST - Stores
WH - Warehouses
SU - Suppliers
DESTINATION_ Alpha- 2 Y Identifier of the type of value DESTINATION_ VARCHAR2(2)
TYPE numeric specified in the DESTINATION TYPE
field. Valid values are:
ST - Stores
WH - Warehouses
TRANSIT_TIME Integer 4 Y Number of days it takes for a TRANSIT_TIME NUMBER(4)
shipment from the origin
location or supplier to arrive at
the destination location. This
value must be expressed in
terms of a whole number of
days.
MERCH_HIER_5 Integer 4 N Identifier of the 5th level (from CLASS NUMBER(4)
merchandise hierarchy (class in
the base configuration) to
which the transit times record
applies.
Specifying a value in this field
is optional, except when a
value is provided in the
MERCH_HIER_6 field. If a
value is not specified in this
field, the records are applicable
to all items that fall under the
4th level of the merchandise
hierarchy. A value should be
specified in this field when the
transit days vary across items
under the 5th level of the
merchandise hierarchy.

Warehouse
MERCH_HIER_6 Integer 4 N Identifier of the 6th level (from SUBCLASS NUMBER(4)

(subclass in the base
configuration) to which the
transit times record applies.
Specifying a value in this field
is optional. If a value is not
specified in this field, the
records are applicable to all
items that fall under the 4th (or
5th when populated) level of
the merchandise hierarchy. A
value should be specified in
this field when the transit days
vary across items under the 6th
level of the merchandise
hierarchy.
DC_LOAD_WH_ORG.KSH Segment Wrapper / Load Script

tables.
The dc_load_wh_org.ksh script utilizes a common library file and configuration file. The

Warehouse
LOAD_WH_ADDR
This function contains a PL/SQL block that selects from the DC_WH_ADDR external
table and inserts the data to the RMS ADDR table.
The table below defines the default values in the RMS table if no information is provided
DC_WH_ADDR to ADDR Column Defaults
Column Name (RMS Table) Default Value Comments
ADDR_KEY System-generated Use ADDR sequence

MODULE WH
SEQ_NO 1
PUBLISH_IND N
Required file to load: dc_wh_addr.dat
LOAD_WH
This function serves several purposes:
It inserts data into the WH table by selecting all columns from the DC_VWH and
DC_PWH external tables, or both, and uses the defaults specified below for the
columns that are not in the DC_PWH or DC_VWH tables, or that are NULL in the
external tables.
Both DC_VWH and DC_PWH tables are considered for loading data only when
SYSTEM_OPTIONS. MULTICHANNEL_IND = ‘Y’. Otherwise, only data from the
DC_PWH table is loaded.
It inserts data into the WH_ADD table. There are four total columns to be populated.
It populates the WH_ADD pricing location with the warehouse ID (virtual
warehouse ID when multi-channel is on) and the PRICING_LOC_CURR with the
warehouse CURRENCY_CODE.
It inserts data into the STOCK_LEDGER_INSERTS table. If SYSTEM_OPTIONS.
MULTICHANNEL_IND = ‘Y’, it inserts the virtual warehouse number. Otherwise, it
inserts the physical warehouse number.
Note:
When multi-channel is not enabled, there is only one file for DC_PWH data
(DC_PWH.DAT). This function populates the WH, WH_ADD, and
STOCK_LEDGER_INSERTS tables accordingly.
When multi-channel is enabled, there are two files for DC_PWH and DC_VWH data
(DC_PWH.DAT and DC_VWH.DAT). Each physical warehouse (PWH) may have
one or more virtual warehouses (VWH), so there can be one-to-many mappings
between DC_PWH and DC_VWH tables. Data from both the DC_PWH and
DC_VWH tables is used to insert physical warehouse records into the WH table first;
then all related virtual warehouse records are inserted into the WH table. For inserts
into the WH_ADD and STOCK_LEDGER_INSERTS tables, only virtual warehouse
data is used.

Warehouse
DC_PWH to WH, WH_ADD, STOCK_LEDGER_INSERTS Column Defaults
RESTRICTED_IND N
PROTECTED_IND N
BREAK_PACK_IND Y If NULL
REDIST_WH_IND Y If NULL
FORECAST_WH_IND Y If NULL
REPL_IND If multichannel = Y, then override file
value with ‘N’; otherwise, default to ‘Y’
IB_IND N
STOCKHOLDING_IND N if multi-channel, Y if not multi-channel
AUTO_IB_CLEAR N
FINISHER_IND N This can only be ‘Yes’ for virtual
warehouses in a multi-channel
environment, so always set it to ‘N’
PHYSICAL_WH WAREHOUSE_ID
DC_VWH to WH, WH_ADD, STOCK_LEDGER_INSERTS Column Defaults
STOCKHOLDING_IND Y
REDIST_WH_IND N If NULL
PROTECTED_IND N If NULL
FORECAST_WH_IND Y If NULL
REPL_IND N If NULL
IB_IND N If NULL
AUTO_IB_CLEAR N If NULL
FINISHER_IND N WAREHOUSE_ID
VAT_REGION From physical warehouse
CURRENCY_CODE From physical warehouse
ORG_HIER_TYPE From physical warehouse
ORG_HIER_VALUE From physical warehouse
DELIVERY_POLICY From physical warehouse
EMAIL From physical warehouse
DUNS_NUMBER From physical warehouse
DUNS_LOC From physical warehouse
INBOUND_HANDLING_DAYS From physical warehouse

Store
BREAK_PACK_IND From physical warehouse

REDIST_WH_IND From physical warehouse
Required files to load: dc_pwh.dat, dc_vwh.dat
LOAD_TRANSIT_TIMES
This function contains a PL/SQL block that selects from the DC_TRANSIT_TIMES
external table and inserts the data to the RMS TRANSIT_TIMES table.
Required file to load: dc_transit_times.dat
INSERT_COST_ZONE_LOCS
This function inserts data into the COST_ZONE and COST_ZONE_GROUP_LOC tables
for the ‘L’ cost level ZONE_GROUP_ID, by selecting all columns from the DC_PWH
external table. First it retrieves the ZONE_GROUP_ID for the ‘L’ cost_level from the
COST_ZONE_GROUP table; then it uses this ZONE_GROUP_ID to insert records for all
the physical warehouses in the DC_PWH external table into the COST_ZONE and
COST_ZONE_GROUP_LOC tables. The columns in these tables map to the DC_PWH
table as follows:
zone_ID = wh
location = wh
description = wh_name
loc_type = ‘W’
base_cost_ind = ‘N’
The same insert is performed in the COST_ZONE_GROUP_LOC table for virtual
warehouses, if the SYSTEM_OPTIONS multichannel_ind is set to ‘Y’. In this insert , the
values are retrieved from the DC_VWH table, and the zone_id is set to the physical_wh
column value.
Required file to load: dc_pwh.dat, dc_vwh.dat (if multi-channel is active)
Store
REGION
DISTRICT
STORE_ADD
STORE_DEPT_AREA
ADDR

Store

This main script is used across all functional area to call segment load scripts. Refer
Segment load script dc_load_store_org.ksh
– dbc_create_region_tab.sql
– dbc_create_district_tab.sql
– dbc_create_store_add_tab.sql
– dbc_create_store_addr_tab.sql
– dbc_create_store_dept_area.sql
Data Flow
The following diagram shows the data flow for the Organizational Hierarchy Store
functional area:

The dc_load_store_org.ksh script calls each of the SQL scripts in a specific order. The
SQL scripts create external Oracle tables from flat file feeds and load data into the Oracle
File Format


Store

DC_REGION Table
File name: DC_REGION.DAT
Table create SQL script: DBC_CREATE_REGION_TAB.SQL
External Oracle table created: DC_REGION
Suggested post-loading validation (sequence after dc_load_store_org.ksh):
Ensure that REGION.REGION is unique.
Ensure that REGION.AREA is a valid AREA.AREA.
Ensure that REGION.CURRENCY_CODE (if not NULL) is a valid
CURRENCIES.CURRENCY_CODE.
Capture the count from REGION and compare to flat file DC_REGION.DAT to
Length
REGION Integer 10 Y Unique ID for the region. REGION NUMBER(10)

REGION_ Alpha- 120 Y Name of the region. REGION_ VARCHAR2(120)
NAME numeric NAME
AREA Integer 10 Y ID of the area in which the AREA NUMBER(10)
region falls.
MGR_NAME Alpha- 120 N Name of the region manager. MGR_NAME VARCHAR2(120)
numeric
CURRENCY_ Alpha- 3 N Currency under which the CURRENCY_ VARCHAR2(3)
CODE numeric region operates. Valid values are CODE
in the RMS CURRENCIES table.
DC_DISTRICT Table
File name: DC_DISTRICT.DAT
Table create SQL script: DBC_CREATE_DISTRICT_TAB.SQL
External Oracle table created: DC_DISTRICT
Suggested post-load validation (sequence after dc_load_store_org.ksh):
Ensure that DISTRICT.DISTRICT is unique.
Ensure that DISTRICT.REGION is a valid REGION.REGION.
Ensure that DISTRICT.CURRENCY_CODE (if not NULL) is a valid
Capture the count from DISTRICT and compare to flat file DC_DISTRICT.DAT to

Store
Length
DISTRICT Integer 10 Y Unique ID for the organization DISTRICT NUMBER(10)

district.
DISTRICT_ Alpha- 120 Y Name of the district. DISTRICT_ VARCHAR2(120)
NAME numeric NAME
REGION Integer 10 Y Unique ID for the region under REGION NUMBER(10)
which the district falls.
MGR_NAME Alpha- 120 N Name of the district manager. MGR_NAME VARCHAR2(120)
numeric
CURRENCY_ Alpha- 3 N Currency under which the CURRENCY_ VARCHAR2(3)
CODE numeric district operates. Valid values CODE
are in the RMS CURRENCIES
table.

Store
DC_STORE_ADDR Table
File name: DC_STORE_ADDR.DAT
Table create SQL script: DBC_CREATE_STORE_ADDR_TAB.SQL
External Oracle table created: DC_STORE_ADDR
Ensure that ADDR.KEY_VALUE_1 is a valid STORE_ADD.STORE.
Capture the count from ADDR where ADDR.MODULE = ‘ST’ and compare to flat
file DC_STORE_ADDR.DAT to ensure that all rows are loaded.
Length
STORE_ID Alpha- 20 Y Store ID for the address. KEY_VALUE_1 VARCHAR2(20)

numeric
ADDR_TYPE Alpha- 2 Y Type of address for this store. ADDR_TYPE VARCHAR2(2)
numeric Valid values are:
01 - Business
02 - Postal
03 - Returns
04 - Order
05 - Invoice
06 - Remittance
Additional address types can
be defined in the RMS
ADD_TYPE table.
The required address types for
a store are definable in the RMS
ADD_TYPE_MODULE table,
where MODULE = ‘ST’
PRIMARY_ Alpha- 1 Y Whether this is the primary PRIMARY_ VARCHAR2(1)
ADDR_IND numeric address for the address type. ADDR_IND
CONTACT_ Alpha- 120 N Name of the contact at this CONTACT_ VARCHAR2(120)
NAME numeric address. NAME
CONTACT_ Alpha- 20 N Phone number of the contact at CONTACT_ VARCHAR2(20)
PHONE numeric the address. PHONE
CONTACT_ Alpha- 20 N Fax number of the contact at CONTACT_FAX VARCHAR2(20)
FAX numeric the address.
CONTACT_ Alpha- 100 N E-mail of the contact at the CONTACT_ VARCHAR2(100)
EMAIL numeric address. EMAIL
CONTACT_ Alpha- 20 N Telex number of the contact at CONTACT_ VARCHAR2(20)
TELEX numeric the address. TELEX
ADDR_ Alpha- 240 Y First line of the address. ADD_1 VARCHAR2(240)
LINE_1 numeric

Store
ADDR_ Alpha- 240 N Second line of the address. ADD_2 VARCHAR2(240)

LINE_2 numeric
ADDR_ Alpha- 240 N Third line of the address. ADD_3 VARCHAR2(240)
LINE_3 numeric
CITY Alpha- 120 Y City of the address. CITY VARCHAR2(120)
numeric
COUNTY Alpha- 250 N County in which the city is COUNTY VARCHAR2(250)
numeric located.
STATE Alpha- 3 N State in which the city is STATE VARCHAR2(3)
numeric located.
POSTAL_ Alpha- 30 N ZIP code of the address. POST VARCHAR2(30)
CODE numeric
COUNTRY_ Alpha- 3 Y Country ID. Valid values are in COUNTRY_ID VARCHAR2(3)
ID numeric the RMS COUNTRY table.
DC_STORE_ADD Table
File name: DC_STORE_ADD.DAT
Table create SQL script: DBC_CREATE_STORE_ADD_TAB.SQL
External Oracle table created: DC_STORE_ADD
Ensure that STORE_ADD.STORE is unique and does not exist on STORE.
STORE_ADD.CHANNEL_ID is a valid CHANNELS.CHANNEL_ID.
Ensure that STORE_ADD.VAT_REGION is a valid VAT_REGION.VAT_REGION, if
SYSTEM_OPTIONS.VAT_IND = ‘Y’.
Ensure that STORE_ADD.TSFZONE (if not NULL) is a valid
TSFZONE.TRANSFER_ZONE.
Ensure that STORE_ADD.CURRENCY_CODE is a valid
Ensure that STORE_ADD.LANG is a valid LANG..LANG.
Ensure that STORE_ADD.DISTRICT is a valid DISTRICT.DISTRICT.
Ensure that STORE_ADD.DEFAULT_WH (if not NULL) is a valid WH.WH, where
WH.STOCKHOLDING_IND = ‘Y’.
Ensure that STORE_ADD.ORG_UNIT_ID (if not NULL) is a valid
ORG_UNIT.ORG_UNIT_ID.
Ensure that STORE_ADD.TSF_ENTITY_ID is a valid
TSF_ENTITYT.TSF_ENTITY_ID, if
SYSTEM_OPTIONS.INTERCOMPANY_TRANSFER_IND = ‘Y’.
Ensure that STORE_ADD.STORE_FORMAT (if not NULL) is a valid
STORE_FORMAT.STORE_FORMAT.
Ensure that STORE_ADD.SISTER_STORE (if not NULL) is a valid
STORE_ADD.STORE or a valid STORE.STORE.
Capture the count from STORE_ADD and compare to flat file
DC_STORE_ADD.DAT to ensure that all rows are loaded.

Store
Length
STORE Integer 10 Y Unique ID of the STORE NUMBER(10)

store.
STORE_NAME Alpha- 150 Y Name of the store. STORE_NAME VARCHAR2(150)
numeric
STORE_NAME10 Alpha- 10 Y 10-character STORE_NAME10 VARCHAR2(10)
numeric abbreviation of the
store name.
STORE_NAME3 Alpha- 3 Y 3-character STORE_NAME3 VARCHAR2(3)
numeric abbreviation of the
store name.
STORE_CLASS Alpha- 1 Y Code letter indicating STORE_CLASS VARCHAR2(1)
numeric the class of which the
store is a member: A,
B, C, D, or E.
STORE_MGR_ Alpha- 120 Y Name of the store STORE_MGR_ VARCHAR2(120)
NAME numeric manager. NAME
STORE_OPEN_ DATE 7 Y Date the store STORE_OPEN_ DATE(7)
DATE opened. DATE
Date format is
‘DDMONYYYY’ (for
example, 02JAN2007)
STOCKHOLDING_ Alpha- 1 N Whether the store STOCKHOLDING_ VARCHAR2(1)
IND numeric can hold stock, IND
default ‘N’ if the
multi-channel
indicator is ‘Y’.
Override to ‘Y’ when
multi-channel is set
to ‘N’.
DISTRICT Integer 10 Y ID of the district in DISTRICT NUMBER(10)
which the store is
located. Must be a
valid district ID.
START_ORDER_ Integer 3 Y Days before the store START_ORDER_ NUMBER(3)
DAYS open date that the DAYS
store can start
accepting orders.
CURRENCY_CODE Alpha- 3 Y Currency in which CURRENCY_CODE VARCHAR2(3)
numeric the store operates.
Must be a valid
currency code in the
RMS CURRENCIES
table.

Store
LANG Integer 6 Y Language in which LANG NUMBER(6)

the store operates.
Must be a valid
language code that
exists in the RMS
LANG table
COPY_REPL_IND Alpha- 1 N Whether the COPY_REPL_IND VARCHAR2(1)
numeric replenishment
information set up
for the like store will
be copied (‘Y’ or ‘N’,
default ‘N’).
TRAN_NO_ Alpha- 6 Y Whether the TRAN_NO_ VARCHAR2(6)
GENERATED numeric transaction ID is GENERATED
unique by store or by
store/register (‘S’ or
‘R’).
INTEGRATED_ Alpha- 1 Y Whether the POS at INTEGRATED_ VARCHAR2(1)
POS_IND numeric the store is integrated POS_IND
(‘Y’ or ‘N’).
COPY_ACTIVITY_ Alpha- 1 N Whether the like COPY_ACTIVITY_ VARCHAR2(1)
IND numeric store’s closing date IND
schedule should be
copied in the creation
of a new store based
on a like store (‘Y’ or
‘N’, default ‘N’).
COPY_DLVRY_ Alpha- 1 N Whether the like COPY_DLVRY_IND VARCHAR2(1)
IND numeric store’s delivery
schedule should be
copied in the creation
of a new store based
on a like store (‘Y’ or
‘N’, default ‘N’).
STORE_NAME_ Alpha- 150 N Secondary name of STORE_NAME_ VARCHAR2(150)
SECONDARY numeric the store. This field SECONDARY
can be populated
only when
SYSTEM_OPTIONS.
SECONDARY_
DESC_IND = 'Y'.
STORE_CLOSE_ DATE 7 N Date the store closed. STORE_CLOSE_ DATE(7)
DATE Date format is DATE
example,
02JAN2007).
ACQUIRED_DATE DATE 7 N Date the store was ACQUIRED_DATE DATE(7)
acquired.
Date format is
‘DDMONYYYY’.

Store
REMODEL_DATE DATE 7 N Last date the store REMODEL_DATE DATE(7)

was remodelled.
Date format is
‘DDMONYYYY’.
FAX_NUMBER Alpha- 20 N Fax number of the FAX_NUMBER VARCHAR2(20)
numeric contact at the store.
PHONE_NUMBER Alpha- 20 N Phone number of the PHONE_NUMBER VARCHAR2(20)
numeric store.
EMAIL Alpha- 100 N E-mail of the contact EMAIL VARCHAR2(100)
numeric at the store.
TOTAL_ Integer 8 N Total square feet of TOTAL_ NUMBER(8)
SQUARE_FT the store. SQUARE_FT
SELLING_ Integer 8 N Square feet dedicated SELLING_ NUMBER(8)
SQUARE_FT to selling SQUARE_FT
merchandise.
LINEAR_ Integer 8 N Total merchandise LINEAR_ NUMBER(8)
DISTANCE area of the store. DISTANCE
VAT_REGION Integer 4 N Number of the value VAT_REGION NUMBER(4)
added tax region in
which this store is
located.
Required if
SYSTEM_OPTIONS.
VAT_IND = ‘Y’, even
if VAT_INCLUDE_
IND = ‘N’.
Valid values are
found in the RMS
VAT_REGION table.
VAT_INCLUDE_ Alpha- 1 N Whether value added VAT_INCLUDE_ VARCHAR2(1)
IND numeric tax is included in the IND
retail prices for the
store. Valid values
are 'Y' or 'N', default
‘N’.
CHANNEL_ID Integer 4 N In a multi-channel CHANNEL_ID NUMBER(4)
environment, this
contains the channel
with which the store
is associated. Valid
values can be found
in the CHANNELS
table.
This is required for a
multi-channel
environment; the
value must be a valid
channel ID.

Store
STORE_FORMAT Integer 4 N Number indicating STORE_FORMAT NUMBER(4)

the format of the
store. Valid values
are found in the store
format table.
MALL_NAME Alpha- 120 N Name of the mall in MALL_NAME VARCHAR2(120)
numeric which the store is
located.
TRANSFER_ZONE Integer 4 N Transfer zone in TRANSFER_ZONE NUMBER(4)
which the store is
located. Valid values
are located in the
TSFZONE table.
DEFAULT_WH Integer 10 N Number of the DEFAULT_WH NUMBER(10)
warehouse that can
be used as the default
for creating cross-
dock masks. This
determines which
stores are associated
with or sourced from
a warehouse. It holds
only virtual
warehouses in a
multi-channel
environment.
Otherwise, this field
is NULL.
STOP_ORDER_ Integer 3 N Number of days STOP_ORDER_ NUMBER(3)
DAYS before a store closing DAYS
that the store will
stop accepting
orders. This column
is used when the
STORE_CLOSE_
DATE is defined.
DUNS_NUMBER Alpha- 9 N Dun and Bradstreet DUNS_NUMBER VARCHAR2(9)
numeric number to identify
the store.
DUNS_LOC Alpha- 4 N Dun and Bradstreet DUNS_LOC VARCHAR2(4)
numeric number to identify
the location.
SISTER_STORE Integer 10 N Store number used to SISTER_STORE NUMBER(10)
relate the current
store to the historical
data of an existing
store.

Store
TSF_ENTITY_ID Integer 10 N Legal entity in which TSF_ENTITY_ID NUMBER(10)

the store is located.
This field is required
in a multi-channel
environment.
Foreign key to the
TSF_ENTITY table

Store
DC_STORE_DEPT_AREA Table
File name: DC_STORE_DEPT_AREA.DAT
Table create SQL script: DBC_CREATE_STORE_DEPT_AREA_TAB.SQL
External Oracle table created: DC_STORE_DEPT_AREA
Ensure that STORE_DEPT_AREA.STORE is a valid STORE_ADD.STORE or a valid
STORE.STORE.
Ensure that STORE_DEPT_AREA.DEPT is a valid DEPS.DEPT.
Capture the count from STORE_DEPT_AREA and compare to flat file
DC_STORE_DEPT_AREA.DAT to ensure that all rows are loaded.
Length
STORE Integer 10 Y Unique ID of the store. STORE NUMBER(10)

MERCH_ Integer 4 Y Fourth level of the DEPT NUMBER(4)
HIER_4 merchandise hierarchy,
referred to as department
in the base configuration.
This field must contain a
valid value from the RMS
DEPS table.
EFFECTIVE_ DATE 7 Y Date on which the area is EFFECTIVE_DATE DATE(7)
DATE effective for the store and
hierarchy.
Date format is
example, 02JAN2007).
AREA NUMBER 12,4 Y Area in the store used by AREA NUMBER(12,4)
the hierarchy value
(department).

Store
DC_LOAD_STORE_ORG.KSH Segment Wrapper / Load Script

tables.
The dc_load_store_org.ksh script utilizes a common library file and configuration file.
The library file contains functions common across all segment wrapper scripts. The
LOAD_ REGION
This function contains a PL/SQL block that selects from the DC_REGION external table
and inserts the data to the RMS REGION table.
Required file to load: dc_region.dat
LOAD_ DISTRICT
This function contains a PL/SQL block that selects from the DC_DISTRICT external table
and inserts the data to the RMS DISTRICT table.
Required file to load: dc_district.dat
LOAD_STORE_ADDRESS
This function contains a PL/SQL block that selects from the DC_STORE_ADDR external
table and inserts the data to the RMS ADDR table.
DC_STORE_ADDR to ADDR Column Defaults
ADDR_KEY System-generated
MODULE ST
SEQ_NO 1
PUBLISH_IND N
Required file to load: dc_store_addr.dat

Store
LOAD_ STORE_ADD
This function contains a PL/SQL block that selects from the DC_STORE_ADD external
table and inserts the data to the RMS STORE_ADD table.
DC_STORE_ADD to STORE_ADD Column Defaults
STOCKHOLDING_IND Y The field defaults to Y when multi-channel

is off. When multi-channel is on, the field
defaults to Y when the value is NULL.
COPY_REPL_IND N Y or N
COPY_ACTIVITY_IND N Y or N
COPY_DLVRY_IND N Y or N
VAT_INCLUDE_IND N Y or N
Required file to load: dc_store_add.dat
LOAD_ STORE_DEPT_AREA
This function contains a PL/SQL block that selects from the DC_STORE_DEPT_AREA
external table and inserts the data to the RMS STORE_DEPT_AREA table.
Required file to load: dc_store_dept_area.dat
The following are required tables that require manual data loading:
DEPT_CHRG_HEAD
DEPT_CHRG_DETAIL
STORE_HIERARCHY
COST_ZONE_GROUP (zone level pricing)
Note: Location level COST_ZONE_GROUP should have

been created by the seed data installation. Refer to
Appendix A for more information.
COST_ZONE
COST_ZONE_GROUP_LOC

Store
RPM requirements:
– RPM_ZONE_GROUP_TYPE
– RPM_ZONE_GROUP
– RPM_ZONE
– RPM_ZONE_LOCATION
STOREADD.PC Batch
Run the storeadd.pc batch program at the end, to load store data from the RMS
STORE_ADD table into RMS. When a store record is added to the RMS STORE_ADD
table, the store data is accessible in the system only after the storeadd.pc batch program
is run. The batch program loops through each record in the STORE_ADD table and
performs all the necessary inserts into the different RMS tables. This program adds all
information necessary for a new store to function properly. For details about storeadd.pc,
please refer to the Oracle Retail Merchandising System Operations Guide.
WHADD.PC Batch
Run the whadd.pc batch program at the end, to load data from the RMS WH_ADD table
into RMS. This batch program inserts pricing/zone information for new warehouses,
virtual warehouses, and internal finishers. It reads from the WH_ADD table and inserts
into the PRICE_ZONE and PRICE_ZONE_GROUP_STORE tables for each retrieved
record. Successfully processed records are deleted from the WH_ADD table. For more
information about the whadd.pc batch program, refer to the Oracle Retail Merchandising
System Operations Guide.

6
Suppliers
This chapter describes data conversion for the following RMS tables, listed in the order
SUPS
ADDR (for supplier addresses)
SUP_IMPORT_ATTR
The following programs are included in the Suppliers functional area:
Segment load script dc_load_supplier.ksh
– dbc_create_sups_tab.sql
– dbc_create_sup_addr_tab.sql
– dbc_create_sup_import_attr_tab.sql
Data Flow
The following diagram shows the data flow for the Suppliers functional area:

Prerequisites
Prerequisites
Before you begin using the data conversion toolset for Suppliers, you must complete data
conversion for the following functional areas:
Core
PARTNER (required types: ‘AG’=agents, ‘BK’=advising or issuing banks,
‘FA’=factory)
OUTLOC (required types: ‘DP’=discharge ports, ‘LP’=lading ports)

The dc_load_supplier.ksh script calls each of the SQL scripts in a specific order. The SQL
File Format



Suppliers—DC_SUPS Table
File name: DC_SUPS.DAT
Table create SQL script: DBC_CREATE_SUPS_TAB.SQL
External Oracle table created: DC_SUPS
Suggested post-loading validation (sequence after dc_load_supplier.ksh):
Ensure that SUPS.SUPPLIER is unique.
SUPS.EDI_CHANNEL_ID (if not NULL) is a valid CHANNELS.CHANNEL_ID.
Ensure that SUPS.CURRENCY_CODE is a valid CURRENCIES.CURRENCY_CODE.
Ensure that SUPS.TERMS is a valid TERMS_HEAD.TERMS.
Ensure that SUPS.FREIGHT_TERMS is a valid FREIGHT_TERMS.FREIGHT_TERMS.
Ensure that SUPS.LANG (if not NULL) is a valid LANG.LANG.
Ensure that SUPS.VAT_REGION is a valid VAT_REGION.VAT_REGION if
SYSTEM_OPTIONS.VAT_IND = ‘Y’.
Capture supplier number from SUPS where SUPS.BRACKET_COSTING_IND = ‘Y’
to ensure that SUP_BRACKET_COST rows are added manually.
Capture supplier number from SUPS where SUPS.RET_ALLOW_IND = ‘Y’ to ensure
that row for the supplier with ADDR_TYPE = ‘03’ exists in ADDR.
Capture the count from SUPS and compare to flat file DC_SUPS.DAT to ensure that
all rows are loaded.
Length
SUPPLIER Integer 10 Y Unique number for the SUPPLIER NUMBER(10)

supplier.
SUP_NAME Alpha- 240 Y Name of the supplier. SUP_NAME VARCHAR2(240)
numeric
SUP_NAME_ Alpha- 240 N Secondary name of the SUP_NAME_ VARCHAR2(240)
SECONDARY numeric supplier. This field can SECONDARY
only be populated when
SYSTEM_OPTIONS.
SECONDARY_DESC_IND
= 'Y'.
CONTACT_ Alpha- 120 Y Name of contact at the CONTACT_ VARCHAR2(120)
NAME numeric supplier. NAME
CONTACT_ Alpha- 20 Y Phone number of the CONTACT_ VARCHAR2(20)
PHONE numeric contact at the supplier. PHONE
CONTACT_ Alpha- 20 N Fax number of the contact CONTACT_FAX VARCHAR2(20)
FAX numeric at the supplier.
CONTACT_ Alpha- 20 N Pager number of the CONTACT_ VARCHAR2(20)
PAGER numeric contact at the supplier. PAGER

QC_IND Alpha- 1 Y Whether orders from this QC_IND VARCHAR2(1)

numeric supplier default as
requiring quality control.
A value of 'Y' means that
all orders from this
supplier require quality
control, unless overridden
by the user when the order
is created. An 'N' in this
field means that quality
control will not be
required, unless indicated
by the user during order
creation.
QC_PCT Float 12,4 N Percentage of items per QC_PCT NUMBER(12,4)
receipt that will be marked
for quality checking.
QC_FREQ Integer 2 N Frequency with which QC_FREQ NUMBER(2)
items per receipt will be
marked for quality
checking.
VC_IND Alpha- 1 Y Whether orders from this VC_IND VARCHAR2(1)
numeric supplier default as
requiring vendor control.
A value of 'Y' means that
all orders from this
supplier will require
vendor control. 'N' means
that vendor control will
not be required.
VC_PCT Float 12,4 N Percentage of items per VC_PCT NUMBER(12,4)
receipt that will be marked
for vendor checking.
VC_FREQ Integer 2 N Frequency with which VC_FREQ NUMBER(2)
items per receipt will be
marked for vendor
checking.
CURRENCY_ Alpha- 3 Y Currency the supplier uses CURRENCY_ VARCHAR2(3)
CODE numeric for business transactions. CODE
Valid values are in the
RMS CURRENCIES table.
LANG Integer 6 N Supplier's preferred LANG NUMBER(6)
language. This field is
provided for custom
purchase orders in a
specified language.
Valid values are stored in
the LANG table in RMS.

TERMS Alpha- 15 Y Indicator identifying the TERMS VARCHAR2(15)

numeric sales terms that default
when an order is created
for the supplier. These
terms specify when
payment is due and if there
are any discounts for early
payment.
RMS TERMS_HEAD table.
FREIGHT_ Alpha- 30 Y Indicator that references FREIGHT_TERMS VARCHAR2(30)
TERMS numeric the freight terms that
default when a order is
created for the supplier.
RMS FREIGHT_TERMS
table.
RET_ALLOW_ Alpha- 1 Y Whether the supplier RET_ALLOW_ VARCHAR2(1)
IND numeric accepts returns. Valid IND
values are 'Y' and 'N'.
RET_AUTH_ Alpha- 1 Y Whether returns must be RET_AUTH_REQ VARCHAR2(1)
REQ numeric accompanied by an
authorization number
when sent back to the
vendor. Valid values are 'Y'
and 'N'.
RET_MIN_ Numeric 20,4 N Contains a value if the RET_MIN_DOL_ NUMBER(20,4)
DOL_AMT supplier requires a AMT
minimum dollar amount to
be returned in order to
accept the return. Returns
of less than this amount
will not be processed by
the system. This field is
stored in the supplier's
currency.
RET_COURIER Alpha- 250 N Name of the courier that RET_COURIER VARCHAR2(250)
numeric should be used for all
returns to the supplier.
DEFAULT_ Numeric 12,4 N Percentage multiplied by HANDLING_PCT NUMBER(12,4)
HANDLING_ the total order cost to
PCT determine the handling
cost for the return.
EDI_PO_IND Alpha- 1 Y Whether purchase orders EDI_PO_IND VARCHAR2(1)
numeric will be sent to the supplier
through Electronic Data
Interchange. Valid values
are 'Y' and 'N'.

EDI_PO_CHG Alpha- 1 Y Whether purchase order EDI_PO_CHG VARCHAR2(1)

numeric changes will be sent to the
supplier through
Electronic Data
are 'Y' and 'N'.
EDI_PO_ Alpha- 1 Y Whether this supplier will EDI_PO_ VARCHAR2(1)
CONFIRM numeric send acknowledgment of a CONFIRM
purchase orders sent
through Electronic Data
are 'Y' and 'N'.
EDI_ASN Alpha- 1 Y Whether this supplier will EDI_ASN VARCHAR2(1)
numeric send Advance Shipment
Notifications electronically.
Valid values are 'Y' and 'N'.
EDI_SALES_ Alpha- 1 N EDI sales report frequency EDI_SALES_RPT_ VARCHAR2(1)
RPT_FREQ numeric for this supplier. Valid FREQ
values are:
D - Sales and stock
information will be
downloaded daily.
W - Sales and stock
information will be
downloaded weekly.
EDI_SUPP_ Alpha- 1 Y Whether the supplier will EDI_SUPP_ VARCHAR2(1)
AVAILABLE_ numeric send availability through AVAILABLE_IND
IND EDI.
EDI_ Alpha- 1 Y Whether contracts will be EDI_ VARCHAR2(1)
CONTRACT_ numeric sent to the supplier CONTRACT_IND
IND through EDI.

EDI_ Integer 4 N Used only in a multi- EDI_CHANNEL_ NUMBER(4)

CHANNEL_ID channel environment. ID
If the supplier is an EDI
supplier and supports
vendor-initiated ordering,
this field contains the
channel ID of the channel
to which all inventory for
these types of orders will
flow. This field is used
when a vendor-initiated
order is created for a
physical warehouse, to
determine the virtual
warehouse within the
physical warehouse to
which the inventory will
flow. The virtual
warehouse belonging to
the indicated channel will
be used.
REPLEN_ Alpha- 1 Y Whether contract orders REPLEN_ VARCHAR2(1)
APPROVAL_ numeric for the supplier should be APPROVAL_IND
IND created in approved status.

SHIP_ Alpha- 6 N Method used to ship the SHIP_METHOD VARCHAR2(6)

METHOD numeric items on the purchase
order from the country of
origin to the country of
import. Valid values are:
10 - Vessel, non-container
11 - Vessel, container
12 - Border water-borne
(only Mexico and
Canada)
20 - Rail, non-container
21 - Rail, container
30 - Truck, non-container
31 - Truck, container
32 - Auto
33 - Pedestrian
34 - Road, other (includes
foot- and animal-
borne)
40 - Air, non-container
41 - Air, container
50 - Mail
60 - Passenger, hand-
carried
70 - Fixed transportation
installation
PAYMENT_ Alpha- 6 N How the purchase order PAYMENT_ VARCHAR2(6)
METHOD numeric will be paid. Valid options METHOD
are:
LC - Letter of credit
WT - Wire transfer
OA - Open account
CONTACT_ Alpha- 20 N Telex number for the CONTACT_ VARCHAR2(20)
TELEX numeric contact at the supplier. TELEX
CONTACT_ Alpha- 100 N E-mail for the contact at CONTACT_ VARCHAR2(100)
EMAIL numeric the supplier. EMAIL
SETTLEMENT_ Alpha- 1 Y Payment process method SETTLEMENT_ VARCHAR2(1)
CODE numeric used for this supplier. CODE
Valid values are:
E - Evaluated Receipts
Settlement (ERS)
N - Not applicable
‘E’ causes an accounts
payable transaction to be
written for an item
received from this
supplier.

PRE_MARK_ Alpha- 1 Y Whether the supplier has PRE_MARK_IND VARCHAR2(1)

IND numeric agreed to break a
warehouse order into
separate boxes (and mark
them for individual stores),
so that the warehouse can
ship directly to the stores.
Valid values are ‘Y’ and
‘N’.
AUTO_APPR_ Alpha- 1 Y Whether the supplier's AUTO_APPR_ VARCHAR2(1)
INVC_IND numeric invoice matches can be INVC_IND
approved automatically for
payment. Valid values are
‘Y’ and ‘N’. This field is
populated only if Invoice
Matching is installed.
FREIGHT_ Alpha- 1 Y Whether a supplier is FREIGHT_ VARCHAR2(1)
CHARGE_IND numeric allowed to charge freight CHARGE_IND
costs to the client. This
field is populated only if
Invoice Matching is
installed. Valid values are
'Y' and 'N'.
BACKORDER_ Alpha- 1 Y Whether back orders or BACKORDER_ VARCHAR2(1)
IND numeric partial shipments will be IND
accepted.
VAT_REGION Integer 4 N Unique identifying VAT_REGION NUMBER(4)
number for the VAT region
in the system.
RMS VAT_REGION table.
INV_MGMT_ Alpha- 6 N Whether supplier INV_MGMT_LVL VARCHAR2(6)
LVL numeric inventory management
information can be set up
at the supplier/
department level, or just at
the supplier level. Also
determines whether orders
created through
replenishment for this
supplier should be split by
department. If the supplier
is returns-only, this field
will be NULL. Otherwise,
this field must have a
value. Values include:
D - Department. Split
orders by department.
S - Supplier. Split orders
by supplier.

SERVICE_ Alpha- 1 Y Whether the supplier's SERVICE_PERF_ VARCHAR2(1)

PERF_REQ_ numeric services (for example, shelf REQ_IND
IND stocking) must be
confirmed as performed
before paying an invoice
from that supplier. Valid
values are 'Y' (all service
non-merchandise lines on
an invoice from this
supplier must be
confirmed before the
invoice can be paid) and
'N' (services do not need to
be confirmed).
DELIVERY_ Alpha- 6 Y Delivery policy of the DELIVERY_ VARCHAR2(6)
POLICY numeric supplier. Next Day (NEXT) POLICY
indicates that, if a location
is closed, the supplier will
deliver on the next day.
Next Valid Delivery Day
(NDD) indicates that the
supplier will wait until the
next scheduled delivery
day before delivering.
DLVY code in
CODE_HEAD/
CODE_DETAIL.
Default = NEXT
COMMENT_ Alpha- 2000 N Any comments associated COMMENT_ VARCHAR2(2000)
DESC numeric with the supplier. DESC
DEFAULT_ Integer 4 N Default lead time for the DEFAULT_ITEM_ NUMBER(4)
ITEM_LEAD_ supplier. The lead time is LEAD_TIME
TIME the time the supplier needs
between receiving an order
and having the order ready
to ship.
This value is defaulted to
item/supplier
relationships.
DUNS_ Alpha- 9 N Dun and Bradstreet DUNS_NUMBER VARCHAR2(9)
NUMBER numeric number to identify the
supplier.
DUNS_ Alpha- 4 N Dun and Bradstreet DUNS_LOC VARCHAR2(4)
LOC numeric number to identify the
location of the supplier.

BRACKET_ Alpha- 1 Y Whether the supplier uses BRACKET_ VARCHAR2(1)

COSTING_IND numeric bracket costing pricing COSTING_IND
structures. Valid values are
‘Y’ and ‘N’, default ‘N’.
Note: If set to ‘Y’, bracket
costing data must be
loaded manually.
DEFAULT_ Alpha- 6 N Status in which any VMI_ORDER_ VARCHAR2(6)
VMI_ORDER_ numeric inbound POs from this STATUS
STATUS supplier are created.
A NULL value indicates
that the supplier is not a
VMI supplier. Orders from
these suppliers will be
created in worksheet
status.
Default = ‘A’
DSD_IND Alpha- 1 Y Whether the supplier can DSD_IND VARCHAR2(1)
numeric ship direct to store.
Valid values are 'Y' and 'N',
default ‘N’.

Supplier Address—DC_SUP_ADDR Table

File name: DC_SUP_ADDR.DAT
Table create SQL script: DBC_CREATE_SUP_ADDR_TAB.SQL
External Oracle table created: DC_SUP_ADDR
Ensure that ADDR.KEY_VALUE_1 is a valid SUPS.SUPPLIER.
Ensure that every SUPS.SUPPLIER with SUPS.RET_ALLOW_IND = ‘Y’ has a row in
ADDR with ADDR.MODULE = ‘SUPP’ and ADDR.ADDR_TYPE = ‘03’.
Ensure that every SUPS.SUPPLIER has a row in ADDR with ADDR.MODULE =
‘SUPP’, and ADDR.ADDR_TYPE in the set of all
ADD_TYPE_MODULE.ADDRESS_TYPE, with ADD_TYPE_MODULE.MODULE =
‘SUPP’ and ADD_TYPE_MODULE.MANDATORY_IND = ‘Y’.
Ensure every ADDR.ADDR_TYPE where ADDR.MODULE = ‘SUPP’ is a valid
ADD_TYPE_MODULE.ADDRESS_TYPE with ADD_TYPE_MODULE.MODULE =
‘SUPP’.
Capture the count from ADDR where ADDR.MODULE = ‘SUPP’ and compare to flat
file DC_SUP_ADDR.DAT to ensure that all rows are loaded.
Length
SUPPLIER_ID Integer 10 Y Unique ID of the supplier. KEY_VALUE_1 NUMBER(10)

ADDR_TYPE Alpha- 2 Y Type of address for this ADDR_TYPE VARCHAR2(2)
numeric supplier. Valid values are:
01 - Business
02 - Postal
03 - Returns
04 - Order
05 - Invoice
06 - Remittance
Additional address types
can be defined in the RMS
ADD_TYPE table.
The required address
types for a supplier are
definable in the RMS
ADD_TYPE_MODULE
table where MODULE =
‘SUPP’.
PRIMARY_ Alpha- 1 Y Whether the address is the PRIMARY_ VARCHAR2(1)
ADDR_IND numeric primary address for this ADDR_IND
address type.
CONTACT_ Alpha- 120 Y Name of the contact at this CONTACT_ VARCHAR2(120)
NAME numeric address. NAME

CONTACT_ Alpha- 20 N Phone number of the CONTACT_ VARCHAR2(20)

PHONE numeric contact at this address. PHONE
CONTACT_ Alpha- 20 N Telex number of the CONTACT_TELEX VARCHAR2(20)
TELEX numeric contact at this address.
CONTACT_ Alpha- 20 N Fax number of the contact CONTACT_FAX VARCHAR2(20)
FAX numeric at this address.
CONTACT_ Alpha- 100 N E-mail of the contact at CONTACT_ VARCHAR2(100)
EMAIL numeric this address. EMAIL
ADDR_LINE_1 Alpha- 240 Y First line of the address. ADD_1 VARCHAR2(240)
numeric
ADDR_LINE _2 Alpha- 240 N Second line of the address. ADD_2 VARCHAR2(240)
numeric
ADDR_LINE _3 Alpha- 240 N Third line of the address. ADD_3 VARCHAR2(240)
numeric
CITY Alpha- 120 Y Name of the city of this CITY VARCHAR2(120)
numeric address.
COUNTY Alpha- 250 N County of the address. COUNTY VARCHAR2(250)
numeric
STATE Alpha- 3 N State abbreviation of the STATE VARCHAR2(3)
numeric address.
POSTAL_ Alpha- 30 N ZIP code. POST VARCHAR2(30)
CODE numeric
COUNTRY_ID Alpha- 3 Y Country code. Valid COUNTRY_ID VARCHAR2(3)
numeric values are in the
COUNTRY table.

Supplier Import Attributes—DC_SUP_IMPORT_ATTR Table

File name: DC_SUP_IMPORT_ATTR.DAT
Table create SQL script: DBC_CREATE_SUP_IMPORT_ATTR_TAB.SQL
External Oracle table created: DC_SUP_IMPORT_ATTR
Ensure that SUP_IMPORT.ATTR.AGENT is a valid PARTNER.PARTNER_ID with
PARTNER_TYPE = ‘AG’.
Ensure that SUP_IMPORT.ATTR.ADVISING_BANK is a valid
PARTNER.PARTNER_ID with PARTNER_TYPE = ‘BK’.
Ensure that SUP_IMPORT.ATTR.ISSUING_BANK is a valid
PARTNER.PARTNER_ID with PARTNER_TYPE = ‘BK’.
Ensure that SUP_IMPORT.ATTR.LADING_PORT is a valid OUTLOC.OUTLOC_ID
with OUTLOC.OUTLOC_TYPE = ‘LP’.
Ensure that SUP_IMPORT.ATTR.DISCHARGE_PORT is a valid
OUTLOC.OUTLOC_ID with OUTLOC.OUTLOC_TYPE = ‘DP’.
Ensure that SUP_IMPORT_ATTR.PLACE_OF_EXPIRY is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘LCPE’.
Ensure that SUP_IMPORT_ATTR.DRAFTS_AT is a valid CODE_DETAIL.CODE
where CODE_DETAIL.CODE_TYPE = ‘LCDA’.
Ensure that SUP_IMPORT_ATTR.PRESENTATION_TERMS is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘LCPT’.
Ensure that SUP_IMPORT_ATTR.PARTNER_1 is a valid PARTNER.PARTNER_ID
with the same partner type as SUP_IMPORT_ATTR.PARTNER_TYPE_1.
Capture the count from SUP_IMPORT_ATTR and compare to flat file
DC_SUP_IMPORT_ATTR.DAT to ensure that all rows are loaded.
Length
SUPPLIER Integer 10 Y Unique ID of the supplier. SUPPLIER NUMBER(10)

AGENT Alpha- 10 N Agent associated with the AGENT VARCHAR2(10)
numeric supplier.
ADVISING_ Alpha- 10 N Bank advising the Letter of ADVISING_ VARCHAR2(10)
BANK numeric Credit. BANK
ISSUING_ Alpha- 10 N Bank issuing the letter of ISSUING_BANK VARCHAR2(10)
BANK numeric credit.
LADING_ Alphanum 5 N Identification number of LADING_PORT VARCHAR2(5)
PORT eric the supplier's Lading Port.
DISCHARGE_ Alpha- 5 N Identification number of DISCHARGE_ VARCHAR2(5)
PORT numeric the supplier's discharge PORT
port.

MFG_ID Alpha- 18 N Manufacturer's tax MFG_ID VARCHAR2(18)

numeric identification number.
RELATED_ Alpha- 1 Y Whether the supplier is RELATED_IND VARCHAR2(1)
IND numeric related to the company.
BENEFICIARY_ Alpha- 1 Y Whether this supplier can BENEFICIARY_ VARCHAR2(1)
IND numeric be a beneficiary. Valid IND
values are 'Y' and 'N'.
WITH_ Alpha- 1 Y Conditional payment on WITH_ VARCHAR2(1)
RECOURSE_ numeric the part of the bank, as RECOURSE_IND
IND instructed by the buyer.
REVOCABLE_ Alpha- 1 Y Whether the letter of credit REVOCABLE_ VARCHAR2(1)
IND numeric is revocable. If this is 'Y', IND
the letter of credit can be
amended or cancelled at
any time by the buyer or
buyer's bank. If this is 'N',
the letter of credit has to
have both buyer and seller
approval to do anything.
VARIANCE_ Numeric 12,4 N Allowed currency variance VARIANCE_PCT NUMBER(12,4)
PCT percentage for the letter of
credit. For example, if the
variance percent is 5, the
letter of credit can be
underpaid or overpaid by 5
percent.
LC_NEG_DAYS Integer 3 N Number of days to LC_NEG_DAYS NUMBER(3)
negotiate documents.
PLACE_OF_ Alpha- 6 N Place where the letter of PLACE_OF_ VARCHAR2(6)
EXPIRY numeric credit will expire. Valid EXPIRY
values are:
01 - Issuing Bank
02 - Advising Bank
03 - Miami
04 - New York
05 – Los Angeles
DRAFTS_AT Alpha- 6 N Terms of draft (or when DRAFTS_AT VARCHAR2(6)
numeric payment is to be made) for
the letter of credit. Valid
values are:
01 - At sight
02 - 30 Days
03 - 60 Days

PRESENTATION_ Alpha- 6 N Terms of presentation (for PRESENTATION_ VARCHAR2(6)

TERMS numeric example, ‘to the order of TERMS
any bank’ or ‘to XYZ
Bank’).
Valid values are:
P - By payment
A - By acceptance
N - By negotiation
FACTORY Alphanum 10 N Factory partner ID for the FACTORY VARCHAR2(10)
eric factory partner type.
PARTNER_ Alpha- 6 N Partner type of the first PARTNER_ VARCHAR2(6)
TYPE_1 numeric additional partner. Valid TYPE_1
values are in the RMS
PARTNER table.
PARTNER_1 Alpha- 10 N Partner ID of the first PARTNER_1 VARCHAR2(10)
numeric additional partner. Valid
PARTNER table.
PARTNER_ Alpha- 6 N Partner type of the second PARTNER_ VARCHAR2(6)
PARTNER table.
PARTNER_2 Alpha- 10 N Partner ID of the second PARTNER_2 VARCHAR2(10)
PARTNER table.
PARTNER_ Alpha- 6 N Partner type of the third PARTNER_ VARCHAR2(6)
PARTNER table.
PARTNER_3 Alpha- 10 N Partner ID of the third PARTNER_3 VARCHAR2(10)
PARTNER table.

DC_LOAD_SUPPLIER.KSH Segment Wrapper / Load Script
DC_LOAD_SUPPLIER.KSH Segment Wrapper / Load Script

tables.
The dc_load_supplier.ksh script utilizes a common library file and configuration file. The
LOAD_SUPPLIER
This function selects from the DC_ SUPS external table and inserts the data to the RMS
SUPS table. All the columns from the external Oracle table defined previously map
directly to the RMS table. The following table lists columns that do not exist in the
DC_SUPS table and must be defaulted as described.
DC_SUPS to SUPS Column Defaults
SUP_STATUS A
AUTO_APPR_DBT_MEMO_IND Y If NULL in external table
PREPAY_INVC_IND Y
DELIVERY_POLICY NEXT If NULL in external table
BRACKET_COSTING_IND N If NULL in external table
DSD_IND N If NULL in external table
EDI_INVC_IND Y
DBT_MEMO_CODE Y
INVC_PAY_LOC C
INVC_RECEIVE_LOC C
ADDINVC_GROSS_NET G
VMI_ORDER_STATUS A If NULL in external table
Required file to load: dc_sups.dat

LOAD_SUP_ADDR
This function selects from the DC_ SUP_ADDR external table and inserts the data to the
RMS ADDR table. All the columns from the external Oracle table defined previously
map directly to the RMS table. The following table lists columns that do not exist in the
DC_ SUP_ADDR table and must be defaulted as described.
DC_SUP_ADDRESS to ADDR Column Defaults
ADDR_KEY Sequence generated

MODULE SUPP
SEQ_NO 1
ADDR_TYPE See the note that follows.
PUBLISH_IND N
Note: For each input supplier, the address records are

created depending on the mandatory address types in the
ADD_TYPE_MODULE table.
Required file to load: dc_sup_addr.dat
LOAD_ SUP_IMPORT_ATTR
This function selects from the DC_ SUP_IMPORT_ATTR external table and inserts the
data to the RMS SUP_IMPORT_ATTR table. All the columns from the external Oracle
table defined above will directly map to the RMS table.
Required file to load: dc_sup_import_attr.dat
After using the data conversion toolset for this functional area, the
SUP_BRACKET_COST table must be loaded manually. This table is required for
suppliers that have bracket costing. It must be loaded before you proceed with data
conversion for subsequent functional areas, because of data dependencies.

7
Items
Because different types of items have different data structures, the Items functional area
is organized based on item types, as follows:
Fashion Items
Hardline Items
Grocery Items
Pack Items
Item Supplier
Item Location
Others
Note the following:
Break-to-sell items are not supported in this data conversion toolset.
2- to 3-tier non-pack items are both orderable and sellable.
Pack items are divided into sellable only and orderable (sellable is optional).
Prerequisites
Before you begin using the data conversion toolset for Items, you must complete data
conversion for the following functional areas:
Core
Suppliers

Fashion Items
Fashion Items
ITEM_MASTER
VAT_ITEM
UDA_ITEM_LOV
ITEM_CHRG_HEAD
ITEM_CHRG_DETAIL
This main script is used across all functional areas to call segment load script. Refer
Segment load script dc_load_fashion_item.ksh
– dbc_create_style_tab.sql
– dbc_create_fashion_sku_tab.sql
– dbc_create_fashion_xref_tab.sql
Data Flow
The following diagram shows the data flow for for the Fashion Items functional area:

Fashion Items

can be loaded. The data fields for each flat file must be created in the order listed.
The dc_load_fashion_item.ksh script calls each of the SQL scripts in a specific order. The
File Format



Fashion Items
DC_STYLE Table
File name: DC_STYLE.DAT
Table create SQL script: DBC_CREATE_STYLE_TAB.SQL
External Oracle table created: DC_STYLE
Suggested post-loading validation (sequence after dc_load_fashion_item.ksh:
Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL <
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.PACK_IND = ‘N’, and compare
to flat file DC_STYLE.DAT to ensure that all rows are loaded.
Ensure that ITEM_MASTER.DEPT/ITEM_MASTER.CLASS/
ITEM_MASTER.SUBCLASS combination exists in SUBCLASS.
Ensure that ITEM_MASTER.DIFF_1 (if not NULL) is a valid DIFF_IDS.DIFF_ID or
DIFF_GROUP_HEAD.DIFF_GROUP_ID.
Length
STYLE Alpha- 20 Y ID that uniquely identifies the ITEM VARCHAR2(25)

numeric style.
STYLE_DESC Alpha- 250 Y Description of the style. ITEM_DESC VARCHAR2(250)
numeric
STYLE_ Alpha- 120 N Short description of the style. SHORT_DESC VARCHAR2(120)
SHORT_DESC numeric Default = First 120 characters of
SKU_DESC.
STYLE_ Alpha- 250 N Secondary description of the ITEM_ VARCHAR2(250)
DESC_ numeric item for Yomi requirement. DESC_
SECONDARY SECONDARY
the DEPT field in the DEPS table
in RMS.
MERCH_ Integer 4 Y Identifier of the merchandise CLASS NUMBER(4)
HIER_5 hierarchy level 5 which is a
member of merchandise
hierarchy level 4. Valid values
are in the CLASS field in the
CLASS table in RMS.

Fashion Items
MERCH_ Integer 4 Y Identifier of the merchandise SUBCLASS NUMBER(4)

HIER_6 hierarchy level 6 which is a
are in the SUBCLASS field in the
SUBCLASS table in RMS.
SIZE_1_ Alpha- 10 Y Size group ID of the first size SIZE_1_ VARCHAR2(10)
GROUP numeric that differentiates the style from GROUP
its ITEM_PARENT (for example,
men's pant sizes or a value such
as 6 oz). Valid values are in the
DIFF_GROUP and DIFF_IDS
tables.
SIZE_2_ Alpha- 10 N Size group ID of the second size SIZE_2_ VARCHAR2(10)
GROUP numeric that differentiates the style from GROUP
its ITEM_PARENT. Valid values
are in the DIFF_GROUP and
DIFF_IDS tables.
COLOR_ Alpha- 10 N ID of the color grouping of the COLOR_ VARCHAR2(10)
GROUP numeric style that differentiates the style GROUP
from its ITEM_PARENT (for
example, pastel colors). Valid
values are in the DIFF_GROUP
and DIFF_IDS tables.
OTHER_ Alpha- 10 N ID of the other grouping of the OTHER_ VARCHAR2(10)
DIFF_GROUP numeric style that differentiates the style GROUP
from its ITEM_PARENT. Valid
values are in the DIFF_GROUP
ITEM_ Alpha- 1 N Default = ‘N’ ITEM_ VARCHAR2(1)
AGGREGATE numeric Indicator for the item AGGREGATE
aggregating up to specific
groupings, such as style/color, is
achieved by adding this
indicator for each grouping in
the table. This item aggregate
indicator allows the user to
specify whether the item can
aggregate by numbers.
Aggregation allows the system
to support allocations at a
style/grouping level. The
remainder of the diffs that are
not a part of the aggregate group
represent the curve portion of
the allocation algorithm.

Fashion Items
SIZE_1_ Alpha- 1 N Default = ‘N’ SIZE_1_ VARCHAR2(1)

SIZE_2_ Alpha- 1 N Default = ‘N’ SIZE_2_ VARCHAR2(1)
COLOR_ Alpha- 1 N Default = ‘N’ COLOR_ VARCHAR2(1)
specify if the item may aggregate
by numbers. Aggregation allows
the system to support allocations
at a style/grouping level. The

Fashion Items
OTHER_ Alpha- 1 N Default = ‘N’ OTHER_ VARCHAR2(1)

DIFF_ numeric Indicator for the item AGGREGATE
AGGREGATE aggregating up to specific
remainder of the diffs not a part
of the aggregate group represent
the curve portion of the
allocation algorithm.
STYLE_ Alpha- 2000 N Comments associated with the STYLE_ VARCHAR2(2000)
COMMENTS numeric style. COMMENTS
Note: There should be only as many aggregate indicators

populated as there are corresponding diff values.
For example, if diffs 1 and 2 contain values, then only diff aggregate 1 and diff
aggregate 2 should be populated with a Y or N. The diff 3 and diff 4 aggregate indicators
should be NULL.
For item aggregation, the item can only aggregate by up to 1 less than the total number of
diff groups specified. For example, if an item has three diff groups associated with it, the
user can aggregate by as many as two of those groups.

Fashion Items
DC_FASHION_SKU Table
File name: DC_FASHION_SKU.DAT
Table create SQL script: DBC_CREATE_FASHION_SKU_TAB.SQL
External Oracle table created: DC_FASHION_SKU
Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL =
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.PACK_IND = ‘N’, and compare
to flat file DC_FASHION_SKU.DAT to ensure that all rows are loaded.
Length
SKU Alpha- 20 Y ID that uniquely identifies ITEM VARCHAR2(25)

numeric the stock keeping unit.
PRIMARY_SKU_ Alpha- 1 Y Not in RMS PRIMARY_SKU_ VARCHAR2(1)
IND numeric ITEM_MASTER, needed IND
for defaulting style.
STYLE Alpha- 20 Y Style associated with the ITEM_PARENT VARCHAR2(25)
numeric SKU.
SKU_DESC Alpha- 250 Y Description of the SKU. ITEM_DESC VARCHAR2(250)
numeric
SHORT_ Alpha- 120 Y Short description of the SHORT_DESC VARCHAR2(120)
DESC numeric SKU.
Default = First 120
characters of SKU_DESC
SKU_DESC_ Alpha- 250 N Secondary description of ITEM_DESC_ VARCHAR2(250)
SECONDARY numeric the SKU for Yomi SECONDARY
requirement.
COST_ZONE_ Integer Y Cost zone group COST_ZONE_ NUMBER(4)
GROUP_ID associated with the item. GROUP_ID
This field is only required
when ELC_IND (landed
cost indicator) is set to 'Y'
in the SYSTEM_OPTIONS
table.
STANDARD_ Alpha- 4 Y Unit of measure in which STANDARD_ VARCHAR2(4)
UOM numeric stock of the item is tracked UOM
at a corporate level.

Fashion Items
UOM_CONV_ Numeric 12,10 N Conversion factor between UOM_CONV_ NUMBER(20,10)

FACTOR an each and the FACTOR
STANDARD_UOM, when
the STANDARD_UOM is
not in the quantity class
(for example, if
STANDARD_UOM = lb
and 1 lb = 10 eaches, this
factor is 10). This factor is
used to convert sales and
stock data when an item is
retailed in eaches, but does
not have eaches as its
standard unit of measure.
STORE_ORDER_ Alpha- 1 Y Unit type in which STORE_ORDER_ VARCHAR2(1)
MULT numeric merchandise shipped from MULT
the warehouses to the
stores must be specified.
Valid values are:
C - Cases
I - Inner
E - Eaches
SKU_ Alpha- 2000 N Comments associated with SKU_COMMENT VARCHAR2(2000)
COMMENTS numeric the SKU S
MERCHANDISE_ Alpha- 1 N Indicates if the item is a MERCHANDISE_ VARCHAR2(1)
IND numeric merchandise item (Y, N). IND
Default = ‘Y’
FORECAST_IND Alpha- 1 N Indicates if this item will FORECAST_IND VARCHAR2(1)
numeric be interfaced to an
external forecasting
system (Y, N).
Default = ‘Y’
SIZE_1 Alpha- 10 N Size ID of the first size that SIZE_1 VARCHAR2(10)
numeric differentiates the SKU
from its Style (for
example, 34 waist). Valid
values are in the
DIFF_GROUP and
DIFF_ID tables.
SIZE_2 Alpha- 10 N Size ID of the first size that SIZE_2 VARCHAR2(10)
from its style (for example,
32 length). Valid values
are in the DIFF_GROUP
and DIFF_ID tables.

Fashion Items
COLOR Alpha- 10 N Color ID of the color that COLOR VARCHAR2(10)

from its Style (for
example, red). Valid
values are in the
DIFF_GROUP and
DIFF_ID tables.
OTHER_ Alpha- 10 N ID of the differentiator OTHER_ VARCHAR2(10)
VARIANT numeric that differentiates the SKU VARIANT
from its style (for example,
stone-washed). Valid
values are in the
DIFF_GROUP and
DIFF_ID tables.

Fashion Items
DC_FASHION_XREF Table
File name: DC_FASHION_XREF.DAT
Table create SQL script: DBC_CREATE_FASHION_XREF_TAB.SQL
External Oracle table created: DC_FASHION_XREF
Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL >
ITEM_MASTER.TRAN_LEVEL, and compare to flat file DC_FASHION_XREF.DAT
to ensure that all rows are loaded.
Ensure that ITEM_MASTER.ITEM is unique.
Ensure that ITEM_MASTER.ITEM_PARENT (if not NULL) is a valid
ITEM_MASTER.ITEM with ITEM_MASTER.ITEM_LEVEL = item level of the child
less 1.
Ensure that ITEM_MASTER.ITEM_GRANDPARENT (if not NULL) is a valid
ITEM_MASTER.ITEM with ITEM_MASTER.ITEM_LEVEL = item level of the
grandchild less 2.
Ensure that ITEM_MASTER.COST_ZONE_GROUP_ID is a valid
COST_ZONE_GROUP..ZONE_GROUP_ID if SYSTEM_OPTIONS.ELC_IND = ‘Y’.
Ensure that ITEM_MASTER.STANDARD_UOM is a valid UOM_CLASS.UOM with
UOM_CLASS.UOM_CLASS is not ‘MISC’.
Ensure that ITEM_MASTER.UOM_CONV_FACTOR is not NULL if UOM_CLASS
of ITEM_MASTER.STANDARD_UOM is not ‘QTY’.
Ensure that ITEM_MASTER.RETAIL_ZONE_GROUP_ID is a valid
PRICE_ZONE_GROUP.ZONE_GROUP_ID.
Ensure that ITEM_MASTER.PACKAGE_UOM (if not NULL) is a valid
UOM_CLASS.UOM.
Ensure that ITEM_MASTER.RETAIL_LABEL_TYPE (if not NULL) is a valid
CODE_DETAIL.CODE, where CODE_DETAIL.CODE_TYPE = ‘RTLT’.
Ensure that ITEM_MASTER.HANDLING_TEMP (if not NULL) is a valid
CODE_DETAIL.CODE, where CODE_DETAIL.CODE_TYPE = ‘HTMP’.
Ensure that ITEM_MASTER.HANDLING_SENSITIVITY (if not NULL) is a valid
CODE_DETAIL.CODE, where CODE_DETAIL.CODE_TYPE = ‘HSEN’.
Ensure that ITEM_ITEM_NUMBER_TYPE is a valid CODE_DETAIL.CODE, where
CODE_DETAIL.CODE_TYPE = ‘UPCT’.
Length
XREF_ITEM Alpha- 25 Y ID that uniquely identifies ITEM VARCHAR2(25)

numeric the scanning barcode
associated with a product.
XREF_DESC Alpha- 250 Y Description of the item. ITEM_DESC VARCHAR2(250)
numeric
XREF_SHORT_ Alpha- 120 N Default = First 120 SHORT_DESC VARCHAR2(120)
DESC numeric characters of XREF_DESC

Fashion Items
XREF_DESC_ Alpha- 250 N Secondary description of ITEM_DESC_ VARCHAR2(250)

requirement.
SKU Alpha- 25 Y Stock keeping unit ITEM_PARENT VARCHAR2(25)
numeric associated with the
XREF_ITEM.
STYLE Alpha- 25 Y Style associated with the ITEM_ VARCHAR2(25)
numeric XREF_ITEM. GRANDPARENT
XREF_ Alpha- 2000 N Comments associated with STYLE_ VARCHAR2(2000)
COMMENTS numeric the XREF_ITEM. COMMENTS
PRIMARY_ Alpha- 1 N Indicates that XREF_ITEM PRIMARY_REF_ VARCHAR2(1)
REF_ITEM_ numeric is the primary item for the IND
IND stock keeping unit.
Note: There can only be
one primary xref item for a
SKU.
Default = ‘N’
ITEM_ Alpha- 6 Y Code specifying what type ITEM_NUMBER_ VARCHAR2(6)
NUMBER_ numeric the XREF_ITEM is. Valid TYPE
TYPE values for this field are in
the code type UPCT in the
CODE_HEAD and
CODE_DETAIL tables.

Fashion Items
DC_LOAD_FASHION_ITEM.KSH Segment Wrapper / Load Script

tables.
The dc_load_fashion_item.ksh script utilizes a common library file and configuration file.
LOAD_ STYLE_SKU
This function contains a PL/SQL block that selects from the DC_STYLE and the
DC_FASHION_SKU external tables and inserts the data to the RMS ITEM_MASTER
table.
Styles
For styles, the following table defines the default values in the RMS table if no
information is provided in the data file (external table field values are NULL or not
defined).
DC_STYLE and DC_FASHION_SKU to ITEM_MASTER Column Defaults
ITEM_NUMBER_TYPE ITEM
ITEM_LEVEL 1
TRAN_LEVEL 2
SHORT_DESC SUBSTR 120 characters from ITEM_DESC If NULL
DESC_UP Upper ITEM_DESC
STATUS A
CREATE_DATETIME SYSDATE
LAST_UPDATE_ID Current user ID
LAST_UPDATE_DATETIME SYSDATE
RETAIL_ZONE_GROUP_ID Lookup in PRICE_ZONE_GROUP table
ITEM_AGGREGATE_IND N If NULL

Fashion Items
DIFF_1_AGGREGATE_IND N If NULL
PERISHABLE_IND N
SKUs
For SKUs, the following table defines the default values in the RMS table if no
defined).
DC_FASHION_SKU to ITEM_MASTER Column Defaults
ITEM_LEVEL 2
TRAN_LEVEL 2
SHORT_DESC SUBSTR 120 characters from SKU_DESC
STATUS A
RETAIL_ZONE_GROUP_ID Lookup from PRICE_ZONE_GROUP table
ORDERABLE_IND Y
SELLABLE_IND Y
MERCHANDISE_IND Y If NULL
FORECAST_IND Y If NULL
INVENTORY_IND Y
ITEM_AGGREGATE_IND N
DIFF_1_AGGREGATE_IND N
PRIMARY_REF_ITEM_IND N
CONST_DIMEN_IND N
GIFT_WRAP_IND N
SHIP_ALONE_IND N

Fashion Items
ITEM_XFORM_IND N
PACK_IND N
SIMPLE_PACK_IND N
CATCH_WEIGHT_IND N
CONTAINS_INNER_IND N
PERISHABLE_IND N
Required files to load: dc_style.dat, dc_fashion_sku.dat
INSERT_ITEM_DEFAULTS
This function inserts item defaults from the merchandise hierarchy specifications for
VAT, UDAs and item charges. Using bulk collect, this function retrieves into a PL/SQL
table the ITEM, DEPT, CLASS, and SUBCLASS values from the DC_STYLE table and
from DC_STYLE joined with DC_FASHION_SKU.
If the VAT indicator is turned on in SYSTEM_OPTIONS, the function retrieves SKU
information and calls the VAT_SQL.DEFAULT_VAT_ITEM to default data into RMS
VAT_ITEM table.
It also retrieves style information and calls UDA_SQL.INSERT_DEFAULTS and
ITEM_CHARGE_SQL.DC_DEFAULT_CHRGS. It retrieves SKU information and calls
UDA_SQL.INSERT_DEFAULTS and ITEM_CHARGE_SQL.DC_DEFAULT_CHRGS.
These functions default data into the RMS UDA_ITEM_LOV, ITEM_CHRG_HEAD, and
ITEM_CHRG_DETAIL tables.
Required files to load: dc_style.dat, dc_fashion_sku.dat

Fashion Items
LOAD_XREF
This function contains a PL/SQL block that selects from the DC_FASHION_XREF and
the DC_FASHION_SKU external tables and inserts the data to the RMS ITEM_MASTER
table.
Most of the columns from the external Oracle table defined above directly map to the
RMS table. The following table defines the default values in the RMS table if no
defined).
DC_FASHION_XREF and DC_FASHION_SKU to ITEM_MASTER Column Defaults
ITEM_LEVEL 3
TRAN_LEVEL 2
SHORT_DESC SUBSTR 120 characters from ITEM_DESC
PRIMARY_REF_ITEM_IND N If NULL
PERISHABLE_IND N
Required files to load: dc_style.dat, dc_fashion_sku.dat,dc_fashion_xref.dat

Hardline Items
Hardline Items
ITEM_MASTER
VAT_ITEM
UDA_ITEM_LOV
ITEM_CHRG_HEAD
ITEM_CHRG_DETAIL
The following programs are included in this functional area.
Segment load script dc_load_hardline_item.ksh. This wrapper calls the external
Oracle table create and load scripts listed below.
– dbc_create_hardlines_tab.sql
– dbc_create_hardlines_xref_tab.sql
Data Flow
The following diagram shows the data flow for the Hardline Items functional area:

The dc_load_hardline_item.ksh script calls each of the SQL scripts in a specific order.
The SQL scripts create external Oracle tables from flat file feeds and load data into the
Oracle Retail Merchandising database.

Hardline Items
File Format


DC_HARDLINES Table
File name: DC_HARDLINES.DAT
Table create SQL script: DBC_CREATE_HARDLINES_TAB.SQL
External Oracle table created: DC_HARDLINES
Suggested post-loading validation (sequence after dc_load_hardline_item.ksh:
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.ITEM_PARENT is NULL and
ITEM_MASTER.PACK_IND = ‘N’, and compare to flat file DC_HARDLINES.DAT to
Length
SKU Alpha- 20 Y ID that uniquely ITEM VARCHAR2(25)

numeric identifies the stock
keeping unit.
SKU_DESC Alpha- 120 Y Description of the SKU. ITEM_DESC VARCHAR2(250)
numeric
SKU_SHORT_ Alpha- N Short description of the SHORT_DESC VARCHAR2(120)
DESC numeric SKU.
Default = First 120 char
of SKU_DESC
SKU_DESC_ Alpha- 250 N Secondary description of ITEM_DESC_ VARCHAR2(250)
requirement.

Hardline Items
MERCH_HIER_4 Integer 4 Y Identifier of the DEPT NUMBER(4)

level 4 of which
level 5 is a member.
DEPT field in the DEPS
table in RMS.
MERCH_HIER_5 Integer 4 Y Identifier of the CLASS NUMBER(4)
level 5 which is a
values are in the CLASS
field in the CLASS table
in RMS.
MERCH_HIER_6 Integer 4 Y Identifier of the SUBCLASS NUMBER(4)
level 6 which is a
values are in the
SUBCLASS field in the
COST_ZONE_ Integer Y Cost zone group COST_ZONE_ NUMBER(4)
GROUP_ID associated with the item. GROUP_ID
This field is only
required when ELC_IND
(landed cost indicator) is
set to 'Y' in the
SYSTEM_OPTIONS
table.
UOM_CONV_ Floating 12,10 N Conversion factor UOM_CONV_ NUMBER(20,10)
FACTOR Point between an each and the FACTOR
STANDARD_UOM,
when the
STANDARD_UOM is
not in the quantity class.
(For example, if
STANDARD_UOM = lb
and 1 lb = 10 eaches, this
factor is 10.) This factor
is used to convert sales
and stock data when an
item is retailed in eaches,
but does not have eaches
as its standard unit of
measure.
STANDARD_ Alpha- 4 Y Unit of measure in which STANDARD_ VARCHAR2(4)
UOM numeric stock of the item is UOM
tracked at a corporate
level.

Hardline Items
STORE_ORDER_ Alpha- 1 Y Unit type in which STORE_ORD_ VARCHAR2(1)

MULT numeric merchandise shipped MULT
from the warehouses to
the stores must be
specified. Valid values
are:
C - Cases
I - Inner
E - Eaches
SKU_ Alpha- 2000 N Comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the SKU.
MERCHANDISE_ Alpha- 1 N Whether the item is a MERCHANDISE_ VARCHAR2(1)
IND numeric merchandise item (Y or IND
N).
Default = ‘Y’
FORECAST_IND Alpha- 1 N Whether this item will be FORECAST_IND VARCHAR2(1)
numeric interfaced to an external
forecasting system (‘Y’ or
‘N’).
Default = ‘Y’

Hardline Items
DC_HARDLINES_XREF Table
File name: DC_HARDLINES_XREF.DAT
Table create SQL script: DBC_CREATE_HARDLINES_XREF_TAB.SQL
External Oracle table created: DC_HARDLINES_XREF
Suggested post-loading validation (sequence after dc_load_hardline_item.ksh:
Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL >
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.ITEM_GRANDPARENT is
NULL, and compare to flat file DC_HARDLINES_XREF.DAT to ensure that all rows
are loaded.
less 1.
Ensure that ITEM_MASTER.UOM_CONV_FACTOR is not NULL if UOM_CLASS of
ITEM_MASTER.STANDARD_UOM is not ‘QTY’.
Ensure that ITEM_ITEM_NUMBER_TYPE is a valid CODE_DETAIL.CODE, where
CODE_DETAIL.CODE_TYPE = ‘UPCT’.
Length
XREF_ITEM Alpha- 25 Y ID that uniquely ITEM VARCHAR2(25)

numeric identifies the scanning
bar code associated with a
product.
numeric
XREF_SHORT_ Alpha- 120 N Default = 120 char of SHORT_DESC VARCHAR2(120)
DESC numeric XREF_DESC
XREF_DESC_ Alpha- 250 N Secondary description of ITEM_DESC_ VARCHAR2(250)
requirement.
SKU Alpha- 25 Y Stock keeping unit ITEM_PARENT VARCHAR2(25)
XREF_ITEM.
XREF_ Alpha- 2000 N Comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the XREF_ITEM.

Hardline Items
PRIMARY_REF_ Alpha- 1 N Whether XREF_ITEM is PRIMARY_REF_ VARCHAR2(1)

ITEM_IND numeric the primary item for the ITEM_IND
stock keeping unit.
Note: There can only be
one primary xref item for
a SKU.
Default = ‘N’
ITEM_ Alpha- 6 Y Code specifying what ITEM_NUMBER_ VARCHAR2(6)
NUMBER_TYPE numeric type the XREF_ITEM is. TYPE
Valid values for this field
are in the code type UPCT
in the CODE_HEAD and
CODE_DETAIL tables.

Hardline Items
DC_LOAD_HARDLINE_ITEM.KSH Segment Wrapper / Load Script

tables.
The dc_load_hardline_item.ksh script utilizes a common library file and configuration
file. The library file contains functions common across all segment wrapper scripts. The
LOAD_HARDLINES
This function contains a PL/SQL block that selects from the DC_HARDLINES external
table and inserts the data to the RMS ITEM_MASTER table.
DC_HARDLINES to ITEM_MASTER Column Defaults
ITEM_LEVEL 1
TRAN_LEVEL 1
SHORT_DESC RTRIM of SUBSTRB 120 characters from If NULL
ITEM_DESC
STATUS A
RETAIL_ZONE_GROUP_ID Look up PRICE_ZONE_GROUP table
ORDERABLE_IND Y
SELLABLE_IND Y
INVENTORY_IND Y

Hardline Items
CONST_DIMEN_IND N
GIFT_WRAP_IND N
SHIP_ALONE_IND N
ITEM_XFORM_IND N
PACK_IND N
SIMPLE_PACK_IND N
CATCH_WEIGHT_IND N
PERISHABLE_IND N
Required file to load: dc_hardlines.dat
INSERT_ITEM_DEFAULTS
VAT, UDAs, and item charges. Using bulk collect, it retrieves into a PL/SQL table the
ITEM, DEPT, CLASS, and SUBCLASS values from the DC_HARDLINES table.
If the VAT indicator is turned on in SYSTEM_OPTIONS, this function retrieves SKU
information and calls the VAT_SQL.DEFAULT_VAT_ITEM to default data into the RMS
VAT_ITEM table.
It retrieves item information and calls UDA_SQL.INSERT_DEFAULTS and
ITEM_CHARGE_SQL.DC_DEFAULT_CHRGS. These functions default data into the
RMS UDA_ITEM_LOV, ITEM_CHRG_HEAD, and ITEM_CHRG_DETAIL tables.
Required file to load: dc_hardlines.dat

Hardline Items
LOAD_HARDLINES_XREF
This function contains a PL/SQL block that selects from the DC_HARDLINES_XREF
external tables and inserts the data to the RMS ITEM_MASTER table.
Most of the columns from the external Oracle table defined above map directly to the
defined).
DC_HARDLINES_XREF to ITEM_MASTER Column Defaults
ITEM_LEVEL 2
TRAN_LEVEL 1
SHORT_DESC RTRIM of SUBSTR b 120 characters from If NULL
ITEM_DESC
STATUS A
PERISHABLE_IND N
Required files to load: dc_hardlines.dat, dc_hardlines_xref.dat

Grocery Items
Grocery Items
ITEM_MASTER
VAT_ITEM
UDA_ITEM_LOV
ITEM_CHRG_HEAD
ITEM_CHRG_DETAIL
Segment load script dc_load_grocery_items.ksh.
– dbc_create_product_line_tab.sql
– dbc_create_product_tab.sql
– dbc_create_grocery_variant_tab.sql
Data Flow
The following diagram shows the data flow for the Grocery Items functional area:
DATA FILES RMS TABLES

PREPARATION SCRIPTS CONVERSION TABLES
dc_product_line.dat LOAD PROCESS item_master
dc_product.dat dbc_create_product_line_tab.sql dc_product_line vat_item
dc_grocery_variant.dat dbc_create_product_tab.sql dc_product dc_load_grocery_items.ksh uda_item_lov
dbc_create_grocery_variant_tab.sql dc_grocery_variant item_chrg_head
item_chrg_detail

The dc_load_grocery_items.ksh script calls each of the SQL scripts in a specific order.

Grocery Items
File Format


DC_PRODUCT_LINE Table
File name: DC_PRODUCT_LINE.DAT
Table create SQL script: DBC_CREATE_PRODUCT_LINE_TAB.SQL
External Oracle table created: DC_PRODUCT_LINE
Suggested post-loading validation (sequence after dc_load_grocery_items.ksh:
Length
PRODUCT_LINE Alpha- 25 Y ID that uniquely identifies ITEM VARCHAR2(25)

numeric the product line.
PRODUCT_LINE_ Alpha- 250 Y Description of the product ITEM_DESC VARCHAR2(250)
DESC numeric line.
PRODUCT_LINE_ Alpha- 120 N Short description of the SHORT_DESC VARCHAR2(120)
SHORT_DESC numeric product line.
Default = First 120
characters of ITEM_DESC
PRODUCT_LINE_ Alpha- 250 N Secondary description of ITEM_DESC_ VARCHAR2(250)
DESC_ numeric product line. SECONDARY
SECONDARY

Grocery Items
MERCH_HIER_4 Integer 4 Y Identifier of the DEPT NUMBER(4)

level 4 of which
5 is a member. Valid values
are in the DEPT field in the
DEPS table in RMS.
MERCH_HIER_5 Integer 4 Y Identifier of the CLASS NUMBER(4)
level 5 which is a member
of merchandise hierarchy
level 4. Valid values are in
the CLASS field in the
CLASS table in RMS.
MERCH_HIER_6 Integer 4 Y Identifier of the SUBCLASS NUMBER(4)
level 6 which is a member
of merchandise hierarchy
level 5. Valid values are in
the SUBCLASS field in the
DIFF_GROUP_1_ Alpha- 10 N Flavor group ID that DIFF_1 VARCHAR2(10)
FLAVOR numeric differentiates the product
line. Valid values are found
in the DIFF_GROUP and
DIFF_IDS tables in RMS.
DIFF_GROUP_2_ Alpha- 10 N Size group ID that DIFF_2 VARCHAR2(10)
SIZE numeric differentiates the product
DIFF_IDS tables in RMS.
OTHER_GROUP_3 Alpha- 10 N ID of a grouping that DIFF_3 VARCHAR2(10)
numeric differentiates the product
DIFF_IDS tables.
OTHER_GROUP_4 Alpha- 10 N ID of a grouping that DIFF_4 VARCHAR2(10)
numeric differentiates the product
DIFF_IDS tables.

Grocery Items
ITEM_ Alpha- 1 N Default = ‘N’ ITEM_ VARCHAR2(1)

AGGREGATE numeric Indicator for the item AGGREGATE_
aggregating up to specific IND
groupings such as product
line/flavor. This item
aggregate indicator allows
the user to specify if the
item may aggregate by
numbers. Aggregation
allows the system to
support allocations at a
product line/grouping
level. The remainder of the
differentiators that are not a
part of the aggregate group
represent the curve portion
of the allocation algorithm.
FLAVOR_ Alpha- 1 N Default = ‘N’ DIFF_1_ VARCHAR2(1)
AGGREGATE_ numeric Indicator for the item AGGREGATE_
IND aggregating up to a product IND
line/flavor level. This
specify if the item may
Aggregation allows the
system to support
allocations at a product
line/grouping level. The
remainder of the
SIZE_ Alpha- 1 N Default = ‘N’ DIFF_2_ VARCHAR2(1)
AGGREGATE_ numeric Indicator for the item AGGREGATE_
IND aggregating up to a product IND
line/size grouping level.
This indicator allows the
user to specify if the item
may aggregate by numbers.
system to support
remainder of the

Grocery Items
OTHER_ Alpha- 1 N Default = ‘N’ DIFF_3_ VARCHAR2(1)

GROUP_3__ numeric Indicator for the item AGGREGATE_
AGGREGATE_ aggregating up to a product IND
IND line/other grouping level.
This indicator allows the
user to specify if the item
may aggregate by numbers.
system to support
remainder of the
OTHER_ Alpha- 1 N Default = ‘N’ DIFF_4_ VARCHAR2(1)
GROUP_4_ numeric Indicator for the item AGGREGATE_
AGGREGATE_ aggregating up to a product IND
IND line/grouping level. This
specify if the item may
system to support
remainder of the
PRODUCT_LINE_ Alpha- 2000 N Any comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the product line.
Note: The same number of aggregate indicators should be

populated as the number of corresponding diff values.
For example, if diffs 1 and 2 contain values, then only diff aggregate 1 and diff
aggregate 2 should be populated with a Y or N. The diff 3 and 4 aggregate indicators
should be NULL.
For item aggregation, the item can only aggregate by up to 1 less than the total number of
diff groups specified. For example, if an item has three diff groups associated with it, the
user can aggregate by as many as two of those groups.

Grocery Items
DC_PRODUCT Table
File name: DC_PRODUCT.DAT
Table create SQL script: DBC_CREATE_PRODUCT_TAB.SQL
External Oracle table created: DC_PRODUCT
Separate post-loading validation is not required for the DC_PRODUCT table. The
validations for the DC_GROCERY_VARIANT table (described later in this chapter) also
will validate the rows loaded to the DC_PRODUCT table.
Length
PRODUCT Alpha- 25 Y Brief description of ITEM VARCHAR2(25)

numeric the product.
PRIMARY_ Alpha- 1 N Not in the RMS PRIMARY_ VARCHAR2(1)
PRODUCT_IND numeric ITEM_MASTER PRODUCT_IND
table. Needed for
defaulting product
line.
PRODUCT_LINE Alpha- 25 Y Product line ITEM_PARENT VARCHAR2(25)
product.
PRODUCT_ Alpha- 250 Y Description of the ITEM_DESC VARCHAR2(250)
DESC numeric product.
PRODUCT_ Alpha- 120 N Default = First 120 SHORT_DESC VARCHAR2(120)
SHORT_DESC numeric characters of
ITEM_DESC
(PRODUCT_DESC)
PRODUCT_ Alpha- 250 N Secondary ITEM_DESC_ VARCHAR2(250)
DESC_ numeric description of the SECONDARY
SECONDARY product.
COST_ZONE_ Integer 4 Y Cost zone group COST_ZONE_ NUMBER(4)
GROUP_ID associated with the GROUP_ID
product. This field is
only required when
ELC_IND (landed
cost indicator) is set
to 'Y' in the
SYSTEM_OPTIONS
table within RMS.

Grocery Items
UOM_CONV_ Numeric 20,10 N Conversion factor UOM_CONV_ NUMBER(20,10)

FACTOR between an each and FACTOR
the
STANDARD_UOM
when the
STANDARD_UOM is
not in the quantity
class. (For example, if
STANDARD_UOM =
lb and 1 lb = 10
eaches, this factor is
10.) This factor is
used to convert sales
and stock data when
an item is retailed in
eaches, but does not
have eaches as its
standard unit of
measure.
STANDARD_ Alpha- 4 Y Unit of measure in STANDARD_ VARCHAR2(4)
UOM numeric which stock of the UOM
product is tracked at
a corporate level.
STORE_ORD_ Alpha- 1 Y Unit type in which STORE_ORD_ VARCHAR2(1)
MULT numeric products shipped MULT
from the warehouses
to the stores must be
specified. Valid
values are:
C - Cases
I - Inner
E - Eaches
MERCHANDISE_ Alpha- 1 N Default = ‘Y’ MERCHANDISE_ VARCHAR2(1)
IND numeric Indicates if the IND
product is a
merchandise item (Y
or N).
FORECAST_IND Alpha- 1 N Default = ‘Y’ FORECAST_IND VARCHAR2(1)
numeric Indicates if this
product will be
interfaced to an
external forecasting
system (Y or N).

Grocery Items
DIFF_1_FLAVOR Alpha- 10 N Flavor ID of the DIFF_1 VARCHAR2(10)

numeric flavor that
differentiates the
product from its
product line. Valid
values are in the
DIFF_GROUP and
DIFF_IDS tables in
RMS.
DIFF_2_SIZE Alpha- 10 N Size ID of the size DIFF_2 VARCHAR2(10)
numeric that differentiates the
product from its
product line. Valid
values are in the
DIFF_GROUP and
DIFF_IDS tables in
RMS.
OTHER_ DIFF_3 Alpha- 10 N ID of the DIFF_3 VARCHAR2(10)
numeric differentiator that
differentiates the
product from its
product line. Valid
values are in the
DIFF_GROUP and
DIFF_IDS tables in
RMS.
OTHER DIFF_4 Alpha- 10 N ID of the DIFF_4 VARCHAR2(10)
numeric differentiator that
differentiates the
product from its
product line. Valid
values are in the
DIFF_GROUP and
DIFF_IDS tables in
RMS.
CATCH_ Alpha- 1 N Default = ‘N’ CATCH_ VARCHAR2(1)
WEIGHT_IND numeric Whether the item WEIGHT_IND
should be weighed
when it arrives at a
location.
Valid values for this
field are 'Y' and 'N'.
HANDLING_ Alpha- 6 N Temperature HANDLING_ VARCHAR2(6)
TEMP numeric information TEMP
associated with the
item. Valid values for
this field are in the
code type HTMP in
the CODE_HEAD
and CODE_DETAIL
tables.

Grocery Items
HANDLING_ Alpha- 6 N Sensitivity HANDLING_ VARCHAR2(6)

SENSITIVITY numeric information SENSITIVITY
associated with the
item. Valid values for
this field are in the
code type HSEN in
the CODE_HEAD
and CODE_DETAIL
tables.
WASTE_TYPE Alpha- 6 N Identifies wastage WASTE_TYPE VARCHAR2(6)
numeric type as either sales or
spoilage wastage.
Sales wastage occurs
during processes that
make an item saleable
(for example, fat is
trimmed off at
customer request).
Spoilage wastage
occurs during the
product’s shelf life
(for example,
evaporation causes
the product to weigh
less after a period of
time).
Valid values are:
SP - Spoilage
SL - Sales
Wastage is not
applicable to pack
items.
WASTE_PCT Numeric 12,4 N Average percent of WASTE_PCT NUMBER(12,4)
wastage for the item
over its shelf life.
Used in inflating the
retail price for
wastage items.
DEFAULT_ Numeric 12,4 N Default daily wastage DEFAULT_ NUMBER(12,4)
WASTE_PCT percent for spoilage WASTE_PCT
type wastage items.
This value defaults to
all item locations and
represents the
average amount of
wastage that occurs
on a daily basis.

Grocery Items
PACKAGE_SIZE Numeric 12,4 N Size of the product PACKAGE_SIZE NUMBER(12,4)

printed on any
packaging (for
example, 24 ounces).
This field is used for
reporting purposes,
as well as by Retail
Price Management to
determine same-sized
and different-sized
items.
PACKAGE_UOM Alpha- 4 N Unit of measure PACKAGE_UOM VARCHAR2(4)
package size. This
field is used for
reporting purposes,
and by Retail Price
Management to
determine same-sized
and different-sized
items.
DEPOSIT_ITEM_ Alpha- 6 N Deposit item DEPOSIT_ITEM_ VARCHAR2(6)
TYPE numeric component type. A TYPE
NULL value in this
field indicates that
this item is not part of
a deposit item
relationship. The
possible values are:
E - Contents
A - Container
Z - Crate
T - Returned item
(empty bottle)
P - Complex pack
(with deposit
items)
The returned item is
flagged only to
enable these items to
be mapped to a
separate general
ledger account if
required.
CONTAINER_ Alpha- 25 N Container item CONTAINER_ VARCHAR2(25)
ITEM numeric number for a contents ITEM
item. This field is
only populated and
required if the
DEPOSIT_ITEM_TYP
E = 'E'.

Grocery Items
DEPOSIT_IN_ Alpha- 6 N Indicates if the DEPOSIT_IN_ VARCHAR2(6)

PRICE_UOM numeric deposit amount is PRICE_PER_UOM
included in the price
per UOM calculation
for a contents item
ticket. This value is
only required if the
DEPOSIT_ITEM_TYP
E = 'E'. Valid values
are:
I - Include deposit
amount
E - Exclude deposit
amount
RETAIL_LABEL_ Alpha- 6 N Indicates any special RETAIL_LABEL_ VARCHAR2(6)
TYPE numeric label type associated TYPE
with an item (for
example, prepriced or
cents off). This field is
used for reporting
purposes only.
Values for this field
are defined by the
RTLT code in code
detail.
RETAIL_LABEL_ Numeric 20,4 N Value associated with RETAIL_LABEL_ NUMBER(20,4)
VALUE the retail label type. VALUE
PRODUCT_ Alpha- 2000 N Comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the product.
PERISHABLE_IND Alpha- 1 N Indicates whether a PERISHABLE_IN VARCHAR2(1)
numeric grocery item is D
perishable.
Valid values are Y
and N. Default is N.
For non-grocery
items, this indicator
always is set to N.

Grocery Items
DC_GROCERY_VARIANT Table
File name: DC_GROCERY_VARIANT.DAT
Table create SQL script: DBC_CREATE_GROCERY_VARIANT_TAB.SQL
External Oracle table created: DC_GROCERY_VARIANT
Suggested post-loading validation (sequence after dc_load_grocery_items.ksh:
less 1.
Ensure that ITEM_MASTER.ITEM_GRANDPARENT (if not NULL) is a valid
ITEM_MASTER.ITEM with ITEM_MASTER.ITEM_LEVEL = item level of the
grandchild less 2.
Ensure that ITEM_MASTER.UOM_CONV_FACTOR is not NULL if UOM_CLASS
of ITEM_MASTER.STANDARD_UOM is not ‘QTY’.
UOM_CLASS.UOM.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘RTLT’.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘HTMP’.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘HSEN’.
Ensure that ITEM_MASTER.ITEM_NUMBER_TYPE is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘UPCT’.
Ensure that ITEM_MASTER.CONTAINER_ITEM is a valid ITEM_MASTER.ITEM if
ITEM_MASTER.DEPOSIT_ITEM_TYPE = ‘E’.
Ensure that ITEM_MASTER.FORMAT_ID and ITEM_MASTER.PREFIX are not
NULL if ITEM_MASTER.ITEM_NUMBER_TYPE = ‘VPLU’.
Ensure that ITEM_MASTER.FORMAT_ID is a valid VAR_UPC_EAN.FORMAT_ID if
ITEM_MASTER.ITEM_NUMBER_TYPE = ‘VPLU’.

Grocery Items
Field Name Data Type Max Length Req’d Description Field Name Data Type
VARIANT Alpha- 25 Y ID that uniquely ITEM VARCHAR2(25)

numeric identifies the scanning
barcode associated
with a product.
VARIANT_ Alpha- 6 Y Code specifying what ITEM_NUMBER_ VARCHAR2(6)
NUMBER_TYPE numeric type the variant item is. TYPE
Valid values for this
field are in the code
type UPCT in the
CODE_HEAD and
CODE_DETAIL tables.
VAR_WGT_ Alpha- 1 N Format ID that FORMAT_ID VARCHAR2(1)
PLU_FORMAT numeric corresponds to the
item's variable UPC.
This value is only used
for items with variable
UPCs.
VAR_WGT_ Integer 2 N Prefix for variable PREFIX NUMBER(2)
PLU_PREFIX weight UPCs. The
prefix determines the
format of the eventual
UPC and is used to
decode variable weight
UPCs that are
uploaded from the
POS. It is the client’s
responsibility to
download this value to
their scale systems.
VARIANT_ Alpha- 250 Y Description of the ITEM_DESC VARCHAR2(250)
DESC numeric variant.
VARIANT_ Alpha- 120 N Short description of the SHORT_DESC VARCHAR2(120)
SHORT_DESC numeric variant.
Default = First 120
characters of
ITEM_DESC
VARIANT_ Alpha- 250 N Secondary description ITEM_DESC_ VARCHAR2(250)
DESC_ numeric of the variant. SECONDARY
SECONDARY
PRODUCT Alpha- 25 Y ID of the product ITEM_PARENT VARCHAR2(25)
variant.
PRODUCT_ Alpha- 25 Y ID of the product line ITEM_ VARCHAR2(25)
LINE numeric associated with the GRANDPARENT
variant.

Grocery Items
PRIMARY_REF_ Alpha- 1 N Default = ‘N’ PRIMARY_REF_ VARCHAR2(1)

ITEM_IND numeric Indicates if the variant ITEM_IND
is the primary variant
for the product.
VARIANT_ Alpha- 2000 N Comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the variant.
DC_LOAD_GROCERY_ITEMS.KSH Segment Wrapper / Load Script

tables.
The dc_load_grocery_items.ksh script utilizes a common library file and configuration
LOAD_PRODUCT_LINE
This function contains a PL/SQL block that selects from the DC_PRODUCT_LINE and
DC_PRODUCT external tables and inserts the data to the RMS ITEM_MASTER table.
DC_PRODUCT_LINE and DC_PRODUCT to ITEM_MASTER Column Defaults
ITEM_LEVEL 1
TRAN_LEVEL 2
SHORT_DESC RTRIM/SUBSTRB 120 If NULL
characters from ITEM_DESC
STATUS A

Grocery Items
ITEM_AGGREGATE_IND N If NULL
PERISHABLE_IND N Use the PERISHABLE_IND
value set for the primary
product on the DC_PRODUCT
table. If not defined, default to
N.
Required file to load: dc_product_line.dat, dc_product.dat
LOAD_PRODUCT
This function contains a PL/SQL block that selects from the DC_PRODUCT external
table and inserts the data to the RMS ITEM_MASTER table.
DC_PRODUCT_LINE and DC_PRODUCT to ITEM_MASTER Column Defaults
ITEM_LEVEL 2
TRAN_LEVEL 2
STATUS A
RETAIL_ZONE_GROUP_ID Lookup from
PRICE_ZONE_GROUP table
ORDERABLE_IND Y
SELLABLE_IND Y
INVENTORY_IND Y

Grocery Items
CONST_DIMEN_IND N
GIFT_WRAP_IND N
SHIP_ALONE_IND N
ITEM_XFORM_IND N
PACK_IND N
SIMPLE_PACK_IND N
CATCH_WEIGHT_IND N If NULL
value as set on the
DC_PRODUCT table. If not
defined, default to N.
LOAD_GROCERY_VARIANT
This function contains a PL/SQL block that selects from the DC_GROCERY_VARIANT
external table and inserts the data to the RMS ITEM_MASTER table.
DC_GROCERY_VARIANT and DC_HARDLINES to ITEM_MASTER Column Defaults
ITEM_LEVEL 3
TRAN_LEVEL 2
value set for the parent product
on the DC_PRODUCTtable. If
not defined, default to N.
Required files to load: dc_product_line.dat, dc_product.dat, dc_grocery_variant.dat

Grocery Items
DEFAULT_GROCERY
This function defaults data in the VAT_ITEM, UDA_ITEM_LOV, and
ITEM_CHRG_HEAD/DETAIL tables for newly created products and product lines. It
includes the following logic:
If the VAT indicator is turned on in system_options, it uses bulk collect to retrieve
into a PL/SQL table the item/department values from the DC_PRODUCT table. It
calls the PL/SQL function VAT_SQL.DEFAULT_VAT_ITEM to insert the
department VAT defaults into the RMS VAT_ITEM table, by selecting from the
vat_deps and vat_code_rates for each item in the DC_PRODUCT table.
It also uses bulk collect to retrieve into a PL/SQL table the item/dept/class/subclass
values from the DC_PRODUCT and DC_PRODUCT_LINE tables. It calls
UDA_SQL.INSERT_DEFAULTS to insert the department UDA defaults into the RMS
uda_item_lov table, by selecting from uda_item_defaults and uda for each item in
the DC_PRODUCT and DC_PRODUCT_LINE tables.
It calls ITEM_CHARGE_SQL.DEFAULT_CHRGS to insert the department charge
defaults into the RMS ITEM_CHRG_HEAD and ITEM_CHRG_DETAIL tables, by
selecting from dept_chrg_head and dept_chrg_detail for each item in the
DC_PRODUCT and DC_PRODUCT_LINE tables.

Pack Items
Pack Items
ITEM_MASTER
PACKITEM
PACKITEM_BREAKOUT
PRICE_HIST
UDA_ITEM_LOV
RPM_ITEM_ZONE_PRICE
VAT_ITEM
ITEM_CHRG_HEAD
ITEM_CHRG_DETAIL
The following programs are included in the Pack Items functional area:
Segment load script dc_load_packs.ksh
This wrapper calls the external Oracle table create scripts listed below.
– dbc_create_orderable_pack_tab.sql
– dbc_create_sellable_pack_tab.sql
– dbc_create_pack_component_tab.sql
– dbc_create_pack_xref_tab.sql
Data Flow
The following diagram shows the data flow for the Pack Items functional area:

Pack Items

The dc_load_packs.ksh script calls each of the SQL scripts in a specific order. The SQL
File Format



Pack Items
DC_ORDERABLE_PACK Table
File name: DC_ORDERABLE_PACK.DAT
This file contains all orderable packs that are either sellable or non-sellable. These packs
can be simple packs or complex packs in RMS.
Table create SQL script: DBC_CREATE_ORDERABLE_PACK_TAB.SQL
External Oracle table created: DC_ORDERABLE_PACK
Suggested post-loading validation (sequence after dc_load_packs.ksh:
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.PACK_IND = ‘Y’ and
ITEM_MASTER.ORDERABLE_IND = ‘Y’, and compare to flat file
DC_ORDERABLE_PACK.DAT to ensure that all rows are loaded.
COST_ZONE_GROUP..ZONE_GROUP_ID if SYSTEM_OPTIONS.ELC_IND = ‘Y’
and ITEM_MASTER.PACK_IND = ‘Y’ and ITEM_MASTER.ORDERABLE_IND = ‘Y’.
UOM_CLASS.UOM.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘RTLT’.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘HTMP’.
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘HSEN’.
Length
PACKID Alpha- 25 Y Unique identifier of the pack ITEM VARCHAR2(25)

numeric item
DIFF_ 1 Alpha- 10 N ID of the differentiator that DIFF_1 VARCHAR2(10)
numeric differentiates the SKU from
its Style (for example, stone-
washed). Valid values are
found in the DIFF_GROUP

Pack Items

its Style. Valid values are
DIFF_3 Alpha- 10 N ID of the differentiator that DIFF_3 VARCHAR2(10)
MERCH_HIER_4 Integer 4 Y Identifier of the merchandise DEPT NUMBER(4)
hierarchy level 4 that is a
values are in the DEPT field
in the DEPS table in RMS.
MERCH_HIER_5 Integer 4 Y Identifier of the merchandise CLASS NUMBER(4)
values are in the CLASS field
in the CLASS table in RMS.
MERCH_HIER_6 Integer 4 Y Identifier of the merchandise SUBCLASS NUMBER(4)
values are in the SUBCLASS
field in the SUBCLASS table
in RMS.
PACK_ Alpha- 250 Y Description of the pack item. ITEM_DESC VARCHAR2(250)
DESCRIPTION numeric
PACK_SHORT_ Alpha- 120 N Short description of the pack SHORT_DESC VARCHAR2(120)
DESC numeric item.
Default = First 120 char of
PACK_DESC
PACK_ Alpha- 250 N Secondary description of the ITEM_DESC_ VARCHAR2(250)
SECONDARY_ numeric SKU for Yomi requirement. SECONDARY
DESC

Pack Items
COST_ZONE_ Integer 4 N NULL if PACK_TYPE = COST_ZONE_ NUMBER(4)

GROUP_ID ‘B’uyer; otherwise NOT GROUP_ID
NULL
Cost zone group associated
with the item. This field is
only required when
ELC_IND (landed cost
indicator) is set to 'Y' in the
SYSTEM_OPTIONS table.
PACKAGE_SIZE Numeric 12,4 N Size of the product printed PACKAGE_ NUMBER(12,4)
on any packaging (for SIZE
example, 24 ounces). This
field is used for reporting
purposes, as well as by
Retail Price Management to
determine same-sized and
different-sized items.
PACKAGE_ Alpha- 4 N Unit of measure associated PACKAGE_ VARCHAR2(4)
UOM numeric with the package size. This UOM
purposes, and by Retail Price
Management to determine
same-sized and different-
sized items.
STORE_ORD_ Alpha- 1 N Unit type in which products STORE_ORD_ VARCHAR2(1)
MULT numeric shipped from the MULT
warehouses to the stores
must be specified. Valid
values are:
C - Cases
I - Inner
E - Eaches
Default = ‘E’
MFG_REC_ Numeric 20,4 N Manufacturer’s MFG_REC_ NUMBER(20,4)
RETAIL recommended retail price RETAIL
for the item. Used for
information only. Must be in
the primary currency.
NULL if SELLABLE_
IND = ‘N’
RETAIL_ Alpha- 6 N Any special label type RETAIL_ VARCHAR2(6)
LABEL_TYPE numeric associated with an item (for LABEL_TYPE
example, prepriced or cents
off). This field is used for
reporting purposes only.
Values for this field are
defined by the RTLT code on
code detail.
NULL if SELLABLE_IND =
‘N’

Pack Items
RETAIL_ Numeric 20,4 N The value associated with RETAIL_ NUMBER(20,4)

LABEL_VALUE the retail label type. LABEL_VALUE
NULL if SELLABLE_
IND = ‘N’
HANDLING_ Alpha- 6 N Temperature information HANDLING_ VARCHAR2(6)
TEMP numeric associated with the item. TEMP
Valid values for this field are
in the code type HTMP in
the CODE_HEAD and
CODE_DETAIL tables.
HANDLING_ Alpha- 6 N Sensitivity information HANDLING_ VARCHAR2(6)
SENSITIVITY numeric associated with the item. SENSITIVITY
in the code type HSEN in the
CODE_HEAD and
CODE_DETAIL tables.
CATCH_ Alpha- 1 Y Whether the item should be CATCH_ VARCHAR2(1)
WEIGHT_IND numeric weighed when it arrives at a WEIGHT_IND
location.
'Y' and 'N'.
SIMPLE_ Alpha- 1 Y Whether the pack item SIMPLE_ VARCHAR2(1)
PACK_IND numeric contains all the same items PACK_IND
within (simple), or the pack
item has different items
(complex). Valid values are
‘Y’ or ‘N’.
SELLABLE_IND Alpha- 1 Y Whether the pack item is SELLABLE_ VARCHAR2(1)
numeric sellable to a customer. IND
PACK_TYPE Alpha- 1 N Whether the pack item is a PACK_TYPE VARCHAR2(1)
numeric vendor pack or a buyer
pack.
Valid values are:
B - Buyer
V - Vendor
Required (‘V’ or ‘B’) for a
complex pack:
‘V’ for a simple pack
‘B’ for a buyer pack that is
either “Assembled as a pack
after receipt and ordered as
individual items,” or
“Vendor pack,” where the
pack is ordered.

Pack Items
ORDER_AS_ Alpha- 1 N Whether a pack item is ORDER_AS_ VARCHAR2(1)

TYPE numeric receivable at the component TYPE
level or at the pack level (for
a buyer pack only). This
field is required if the pack
item is an orderable buyer
pack. This field must be
NULL if the pack is sellable
only or a vendor pack.
Valid values are:
E - Eaches (component level)
P - Pack (buyer pack only)
Identifies whether a buyer
pack should be ordered as
the components of the pack
(E), or the pack item should
be ordered (P). For example,
pack A contains 6 of item B
and 6 of item C. If this field
is P, the order would be
placed for item A. If this
field is E, when ordering 1
unit of A, the supplier
would actually receive the
order for 6 B items and 6 C
items.
PACK_ Alpha- 2000 N Any comments associated COMMENTS VARCHAR2(2000)
COMMENTS numeric with the pack item
CATCH_ Alpha- 6 N How catch weight items are ORDER_TYPE VARCHAR2(6)
WEIGHT_ numeric ordered. Valid values are in
ORDER_TYPE the CODE_DETAIL table
with a code type ‘ORDT’.
NOT NULL if
CATCH_WEIGHT_IND =
‘Y’
CATCH_ Alpha- 6 N Method for selling catch SALE_TYPE VARCHAR2(6)
WEIGHT_SALE_ numeric weight items in store
TYPE locations. Valid values are in
the CODE_DETAIL table
with a code type ‘STYP’.
NULL if non-sellable.

Pack Items
DC_SELLABLE_PACK Table
File name: DC_SELLABLE_PACK.DAT
This file contains all sellable packs that are non-orderable. These packs can only be
complex packs in RMS.
Table create SQL script: DBC_CREATE_SELLABLE_PACK_TAB.SQL
External Oracle table created: DC_SELLABLE_PACK
ITEM_MASTER.TRAN_LEVEL and ITEM_MASTER.PACK_IND = ‘Y’ and
ITEM_MASTER.ORDERABLE_IND = ‘N’, and compare to flat file
DC_SELLABLE_PACK.DAT to ensure that all rows are loaded.
Length
PACKID Alpha- 25 Y Unique identifier of the pack ITEM VARCHAR2(25)

numeric item
numeric differentiates the SKU from its
Style (for example, stone-
washed). Valid values are in
the DIFF_GROUP and
DIFF_IDS tables.
Style. Valid values are in the
tables.
tables.
tables.
MERCH_HIER_4 Integer 4 Y Identifier of the merchandise DEPT NUMBER(4)
are in the DEPT field in the
DEPS table in RMS.

Pack Items
MERCH_HIER_5 Integer 4 Y Identifier of the merchandise CLASS NUMBER(4)

are in the CLASS field in the
CLASS table in RMS.
MERCH_HIER_6 Integer 4 Y Identifier of the merchandise SUBCLASS NUMBER(4)
are in the SUBCLASS field in
the SUBCLASS table in RMS.
PACK_ Alpha- 250 Y Description of the pack item. ITEM_DESC VARCHAR2(250)
DESCRIPTION numeric
PACK_SHORT_ Alpha- 120 N Short description of the pack SHORT_ VARCHAR2(120)
DESC numeric item. DESC
Default = First 120 char of
PACK_DESC
PACK_ Alpha- 250 N Secondary description of the ITEM_DESC_ VARCHAR2(250)
SECONDARY_ numeric SKU for Yomi requirement. SECONDARY
DESC
PACKAGE_SIZE Numeric 12,4 N Size of the product printed on PACKAGE_ NUMBER(12,4)
any packaging (for example, 24 SIZE
ounces). This field is used for
reporting purposes, as well as
by RPM to determine same-
sized and different-sized
items.
PACKAGE_ Alpha- 4 N Unit of measure associated PACKAGE_ VARCHAR2(4)
UOM numeric with the package size. This UOM
purposes, and by RPM to
determine same sized and
different sized items.
MFG_REC_ Numeric 20,4 N Manufacturer’s recommended MFG_REC_ NUMBER(20,4)
RETAIL retail price for the item. Used RETAIL
for information only. Needs to
be in the primary currency.
‘N’
RETAIL_LABEL_ Alpha- 6 N Any special label type RETAIL_ VARCHAR2(6)
TYPE numeric associated with an item (for LABEL_TYPE
example, prepriced or cents
off). This field is used for
reporting purposes only.
Values for this field are
defined by the RTLT code on
code detail.
‘N’

Pack Items
RETAIL_LABEL_ Numeric 20,4 N Value associated with the retail RETAIL_ NUMBER(20,4)
VALUE label type. LABEL_
NULL if SELLABLE_IND = VALUE
‘N’
HANDLING_ Alpha- 6 N Temperature information HANDLING_ VARCHAR2(6)
TEMP numeric associated with the item. Valid TEMP
values for this field are in the
code type HTMP in the
CODE_HEAD and
CODE_DETAIL tables.
HANDLING_ Alpha- 6 N Sensitivity information HANDLING_ VARCHAR2(6)
SENSITIVITY numeric associated with the item. Valid SENSITIVITY
values for this field are in the
code type HSEN in the
CODE_HEAD and
CODE_DETAIL tables.
UNIT_RETAIL Numeric 20,4 Y Item’s current unit retail in the UNIT_ NUMBER(20,4)
system’s primary currency. RETAIL
PACK_ Alpha- 2000 N Comments related to the pack COMMENTS VARCHAR2(2000)
COMMENTS numeric item.

Pack Items
DC_PACK_COMPONENT Table
File name: DC_PACK_COMPONENT.DAT
Table create SQL script: DBC_CREATE_PACK_COMPONENT_TAB.SQL
External Oracle table created: DC_PACK_COMPONENT
Capture counts from PACK_ITEM and compare to flat file
DC_PACK_COMPONENT.DAT to ensure that all rows are loaded.
Ensure that PACK_ITEM.PACK_NO is a valid ITEM_MASTER.ITEM where
ITEM_MASTER.PACK_IND = ‘Y’.
Ensure that PACK_ITEM.ITEM is a valid ITEM_MASTER.ITEM where
ITEM_MASTER.TRAN_LEVEL = ITEM_MASTER.ITEM_LEVEL.
Length
PACK_ID Alpha- 25 Y ID of the pack item PACK_NO VARCHAR2(25)

numeric
ITEM Alpha- 25 Y ID of the item contained in the ITEM VARCHAR2(25)
numeric pack
ITEM_QTY Numeric 12,4 Y Quantity of the item within the PACK_ITEM_QTY NUMBER(12,4)
pack

RMS table must be truncated the entire file must be rerun.
No new records within a sequence group can be added to
the RMS table through the scripts.

Pack Items
DC_PACK_XREF Table
File name: DC_PACK_XREF.DAT
Table create SQL script: DBC_CREATE_PACK_XREF_TAB.SQL
External Oracle table created: DC_PACK_XREF
less 1.
Ensure that ITEM_MASTER.ITEM_NUMBER_TYPE is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘UPCT’.
Ensure that ITEM_MASTER.FORMAT_ID and ITEM_MASTER.PREFIX are not
NULL if ITEM_MASTER.ITEM_NUMBER_TYPE = ‘VPLU’.
Ensure that ITEM_MASTER.FORMAT_ID is a valid VAR_UPC_EAN.FORMAT_ID if
ITEM_MASTER.ITEM_NUMBER_TYPE = ‘VPLU’.
Length
XREF_PACK Alpha- 25 Y ID that uniquely identifies ITEM VARCHAR2(25)

numeric the scanning barcode
associated with a product.
numeric
XREF_SHORT_ Alpha- 120 N Default = 120 char of SHORT_DESC VARCHAR2(120)
DESC numeric XREF_DESC.
XREF_SECOND_ Alpha- 250 Y Secondary description of the ITEM_DESC_ VARCHAR2(250)
DESC numeric SKU for Yomi requirement. SECONDARY
PACK_ID Alpha- 25 Y Pack item associated with ITEM_PARENT VARCHAR2(25)
numeric the xref item.
XREF_ Alpha- 2000 N Comments attached to the COMMENTS VARCHAR2(2000)
COMMENTS numeric xref item.
PRIMARY_REF_ Alpha- 1 N There can be many xref PRIMARY_ VARCHAR2(1)
ITEM_IND numeric items for a pack item; this REF_IND
indicates whether this is the
primary xref item.
Default = ‘N’
ITEM_NUMBER_ Alpha- 6 Y Code specifying what type ITEM_ VARCHAR2(6)
TYPE numeric the XREF_PACK is. Valid NUMBER_
values for this field are in the TYPE
code type UPCT in the
CODE_HEAD and
CODE_DETAIL tables.

Pack Items
VAR_WGT_PLU_ Alpha- 6 N Format ID that corresponds FORMAT_ID VARCHAR2(1)

FORMAT numeric to the item’s variable UPC.
This value is only used for
items with variable UPCs.
VAR_WGT_PLU_ Integer 2 N Prefix for variable weight PREFIX NUMBER(2)
PREFIX UPCs. The prefix
determines the format of the
eventual UPC and is used to
decode variable weight
UPCs that are uploaded
from the POS.
DC_LOAD_PACKS.KSH Segment Wrapper / Load Script

This ksh script is called by the Master Script dc_load_main.ksh and serves two purposes:
tables.
command line. By default (without the option -c) this script loads the data.
The dc_load_packs.ksh script utilizes a common library file and configuration file. The

Pack Items
LOAD_ORDERABLE_PACK
This function contains a PL/SQL block that selects from the DC_ORDERABLE_PACK
DC_ORDERABLE_PACK to ITEM_MASTER Column Defaults
ITEM_LEVEL 1
TRAN_LEVEL 1
SHORT_DESC SUBSTRB 120 characters If NULL
from ITEM_DESC
STATUS A
MFG_REC_RETAIL Ensure that SELLABLE_IND = Y,
otherwise NULL
RETAIL_ZONE_GROUP_ID Lookup from Ensure that SELLABLE_IND = Y,
PRICE_ZONE_GROUP otherwise NULL
table
COST_ZONE_GROUP_ID Ensure that ORDERABLE_IND = Y and
PACK_TYPE != B, otherwise NULL
STANDARD_UOM EA
STORE_ORD_MULT If NULL, E
ORDERABLE_IND Y
INVENTORY_IND Y
PACK_TYPE Ensure V if simple pack
ORDER_AS_TYPE Ensure PACK_TYPE = B and

SIMPLE_PACK_IND = N, otherwise
NULL

Pack Items
CONST_DIMEN_IND N
GIFT_WRAP_IND N
SHIP_ALONE_IND N
ITEM_XFORM_IND N
PACK_IND Y
MERCHANDISE_IND Y
FORECAST_IND N
PERISHABLE_IND N
Required file to load: dc_orderable_pack.dat

Pack Items
LOAD_SELLABLE_PACK
This function contains a PL/SQL block that selects from the DC_SELLABLE_PACK
DC_SELLABLE_PACK to ITEM_MASTER Column Defaults
ITEM_LEVEL 1
TRAN_LEVEL 1
STATUS A
RETAIL_ZONE_GROUP_ID Lookup from Since always sellable
PRICE_ZONE_GROUP table
COST_ZONE_GROUP_ID NULL Since always non-orderable
STANDARD_UOM EA
STORE_ORD_MULT E
ORDERABLE_IND N
INVENTORY_IND Y
PACK_TYPE NULL
Since always non-orderable
ORDER_AS_TYPE NULL Since always non-orderable
CONST_DIMEN_IND N
GIFT_WRAP_IND N
SHIP_ALONE_IND N
ITEM_XFORM_IND N

Pack Items
PACK_IND Y
SIMPLE_PACK_IND N Since always non-orderable
CATCH_WEIGHT_IND N Since always non-orderable
ORDER_TYPE NULL Since not catch weight
SALE_TYPE NULL Since not catch weight
MERCHANDISE_IND Y
FORECAST_IND N
PERISHABLE_IND N
Required file to load: dc_sellable_pack.dat
LOAD_PACK_COMPONENT
This function contains a PL/SQL block that selects from the DC_PACK_COMPONENT
external tables and inserts the data to the RMS PACKITEM and
PACKITEM_BREAKOUT tables.
Because inner packs are not supported as part of the data conversion toolset, the RMS
tables PACKITEM and PACKITEM_BREAKOUT have the same data after loading.
Note: If the loading of DC_PACK_COMPONENT results in

any bad data, the PACKITEM and PACKITEM_BREAKOUT
tables should be truncated. The bad data should be fixed in
the original data file and loaded again. This ensures that the
correct seq_no is generated and inserted into RMS tables.
It is assumed that all component items in the DC_PACK_COMPONENT table have been
loaded as approved items with data in the ITEM_MASTER and ITEM_SUPP_COUNTRY
tables, and that the components for each of the packs in DC_SELLABLE_PACK and
DC_ORDERABLE_PACK are included in this table. If not, the data will be inconsistent.
defined).
DC_PACK_COMPONENT to PACKITEM and PACKITEM_BREAKOUTColumn Defaults
SEQ_NO SEQ_NO + 1 for each unique

item, use analytic function
Required file to load: dc_pack_component.dat

Pack Items
LOAD_PACK_XREF
This function contains a PL/SQL block that selects from the DC_PACK_XREF and
DC_PACK external tables and inserts the data to the RMS ITEM_MASTER table.
defined).
DC_PACK_XREF and DC_PACK to ITEM_MASTER Column Defaults
ITEM_LEVEL 2
TRAN_LEVEL 1
SHORT_DESC SUBSTR 120 characters from If NULL
ITEM_DESC
STATUS A
PERISHABLE_IND N
Required file to load: dc_pack_xref.dat
INSERT_SELLABLE_PRICE_HIST
This function inserts the 0 tran_type, 0 reason, 0 location record into the RMS
PRICE_HIST table only for sellable non-orderable packs. (All other items have this
record inserted with the ITEM_SUPPLIER load script.) It retrieves the items from the
DC_SELLABLE_PACK table. For each item, it calls the
PACKITEM_ADD_SQL.BUILD_COMP_COST_RETAIL function to retrieve the
UNIT_COST and UNIT_RETAIL in the primary currency. It uses these values for the 0
record in PRICE_HIST for the UNIT_COST and UNIT_RETAIL.
The pack’s UNIT_COST and UNIT_RETAIL are determined from the pack components.
It is assumed that all component items in the DC_PACK_COMPONENT table have been
loaded as approved items with data in the ITEM_MASTER and ITEM_SUPP_COUNTRY
tables, and that the components for each of the packs in DC_SELLABLE_PACK are
included in this table. If not, the data will be inconsistent.

Pack Items
DC_SELLABLE_PACK to PRICE_HIST Column Defaults
ACTION_DATE VDATE
TRAN_TYPE 0
LOC 0
REASON 0
SELLING_UNIT_RETAIL UNIT_RETAIL
SELLING_UOM ‘EA’
INSERT_SELLABLE_RPM_IZP
This function selects from the DC_SELLABLE_PACK external table and joins with
RPM_MERCH_RETAIL_DEF to insert data to the RPM_ITEM_ZONE_PRICE table.
This function retrieves the regular zone group ID for the department of the items in the
DC_SELLABLE_PACK table, and joins with the RPM_MERCH_RETAIL_DEF_EXPL
view to get the regular RPM GROUP_ZONE_ID for the item’s department/class/
subclass. It performs a bulk collect of this data and loops through the results to insert into
the RPM_ITEM_ZONE_PRICE table. For the insert/select, it joins DC_SELLABLE_PACK
for each item and the RPM_ZONE for the department’s ZONE_GROUP_ID.
The function retrieves the primary currency from SYSTEM_OPTIONS table. If the zone
currency and the primary currency are different, UNIT_RETAIL is converted to the zone
currency. The following table indicates the data retrieved for value insert.
DC_SELLABLE_PACK to RPM_ITEM_ZONE_PRICE Column Defaults
ITEM_ZONE_PRICE_ID Use sequence

ITEM DC_SELLABLE_PACK.ITEM
ZONE_ID RPM_ZONE.ZONE_ID For the department
ZONE_GROUP_ID
STANDARD_RETAIL DC_SELLABLE_PACK.
UNIT_RETAIL
STANDARD_RETAIL_CURRENCY RPM_ZONE.CURRENCY_CODE For the department
ZONE_GROUP_ID
STANDARD_UOM ‘EA’
SELLING_RETAIL DC_SELLABLE_PACK.UNIT_
RETAIL
SELLING_ RETAIL_CURRENCY RPM_ZONE.CURRENCY_CODE For the department
ZONE_GROUP_ID
SELLING_UOM ‘EA’
MULTI_UNIT_CURRENCY RPM_ZONE.CURRENCY_CODE For the department
ZONE_GROUP_ID

Pack Items
DEFAULT_PACKS
UDAs, VAT (if SYSTEM_OPTIONS.VAT_IND = ‘Y’) and for ITEM CHARGES (non-
buyer packs only).
This retrieves the ITEM, DEPT, CLASS and SUBCLASS values from
DC_ORDERBLE_PACK and DC_SELLABLE_PACK. Calls
UDA_SQL.INSERT_DEFAULTS for both sellable and orderable packs. If
SYSTEM_OPTIONS.VAT_IND = ‘Y’, then it calls VAT_SQL.DEFAULT_VAT_ITEM for
both sellable and orderable packs.
This also retrieves SKU and dept information for non-buyer packs. Calls
ITEM_CHARGE_SQL.DEFAULT_CHARGES.
Required files to load: dc_orderable_pack.dat, dc_sellable_pack.dat

Item Supplier
Item Supplier
This section describes data conversion for the following tables, listed in the order in
which they must be loaded:
ITEM_SUPPLIER
ITEM_SUPP_COUNTRY
RPM_ITEM_ZONE_PRICE
PRICE_HIST
ITEM_SUPP_COUNTRY_DIM
The following programs are included in this functional area.
Segment load script dc_load_item_supplier.ksh
– dbc_create_item_supp_tab.sql
– dbc_create_isc_tab.sql
– dbc_create_price_hist_tab.sql
– dbc_create_isc_dim_tab.sql
Data Flow
The following diagram shows the data flow for the Item Supplier functional area:
Prerequisites
Before you begin using the data conversion toolset for Item Supplier, you must complete
data conversion for the following:
Fashion Items
Hardlines
Grocery Items
Pack Items

Item Supplier

can be loaded. The data fields for each flat file must be created in the order listed.
The dc_load_item_supplier.ksh script calls each of the SQL scripts in a specific order.
File Format



Item Supplier
DC_ITEM_SUPPLIER Table
File name: DC_ITEM_SUPPLIER.DAT
Table create SQL script: DBC_CREATE_ITEM_SUPPLIER_TAB.SQL
External Oracle table created: DC_ITEM_SUPPLIER
Note: DC_ITEM_SUPPLIER must have a row/record for

every item level, including below-transaction level
(reference items).
Suggested post-loading validation (sequence after dc_load_item_supplier.ksh:

Capture counts from ITEM_SUPPLIER and compare to flat file
DC_ITEM_SUPPLIER.DAT to ensure that all rows are loaded.
Ensure that ITEM_SUPPLIER.ITEM is a valid ITEM_MASTER.ITEM.
Ensure that ITEM_SUPPLIER.SUPPLIER is a valid SUPS.SUPPLIER.
Ensure that ITEM_SUPPLIER.PALLET_NAME is a valid CODE_DETAIL.CODE
where CODE_TYPE = ‘PALN’.
Ensure that ITEM_SUPPLIER.CASE_NAME is a valid CODE_DETAIL.CODE where
CODE_TYPE = ‘CASN’.
Ensure that ITEM_SUPPLIER.INNER_NAME is a valid CODE_DETAIL.CODE
where CODE_TYPE = ‘INRN’.
Length
SKU Alpha- 25 Y ID of the stock keeping ITEM VARCHAR2(25)

numeric unit.
SUPPLIER Integer 10 Y ID of the supplier that SUPPLIER NUMBER(10)
supplies the SKU.
PALLET_NAME Alpha- 6 N Code referencing the name PALLET_NAME VARCHAR2(6)
numeric used to refer to the pallet.
Valid codes are defined in
the PALN code type.
Examples are flat, pallet.
Default from System
Options.
CASE_NAME Alpha- 6 N Code referencing the name CASE_NAME VARCHAR2(6)
numeric used to refer to the case.
the CASN code type.
Examples are pack, box,
and bag.
Default from System
Options.

Item Supplier
INNER_NAME Alpha- 6 N Code referencing the name INNER_NAME VARCHAR2(6)

numeric used to refer to the inner.
the INRN code type.
Examples are sub-case,
sub-pack.
Default from System
Options.
DIRECT_SHIP_ Alpha- 1 N Whether any item DIRECT_SHIP_ VARCHAR2(1)
IND numeric associated with this IND
supplier is eligible for a
direct shipment from the
supplier to the customer.
Default = ‘N’
VPN Alpha- 30 N Vendor product number VPN VARCHAR2(30)
numeric associated with the SKU.
CONCESSION_ Numeric 12,4 N Margin that the supplier CONCESSION_ NUMBER(12,4)
RATE receives on sale of the RATE
item. If the SKU is a
concession item, this field
is required.
SUPP_LABEL Alpha- 15 N Supplier label. This field SUPP_LABEL VARCHAR2(15)
numeric can only have a value if
the item is a style.
CONSIGNMENT_ Numeric 12,4 N Consignment rate for this CONSIGNMENT_ NUMBER(12,4)
RATE item for the supplier. If the RATE
item is a consignment
item, this field is required.
PRIMARY_SUPP_ Alpha- 1 N Whether this supplier is PRIMARY_SUPP_ VARCHAR2(1)
IND numeric the primary supplier for IND
the item. Each item can
have only one primary
supplier. Valid values are
‘Y’ and ‘N’.
Lowest Supplier ID = ‘Y’,
otherwise default = ‘N’.
Note: This column must
either be populated for all
records, or it must be
NULL for all records.
Note: If a record is in the BAD or DISCARD file and the

PRIMARY_SUPP_IND is NULL in the file, then the record
must be populated with 'N' to be loaded, or the RMS table
must be truncated and the entire file must be rerun.

Item Supplier
DC_ITEM_SUPP_COUNTRY Table
File name: DC_ITEM_SUPP_COUNTRY.DAT
Table create SQL script: DBC_CREATE_ISC_TAB.SQL
External Oracle table created: DC_ITEM_SUPP_COUNTRY
Note: The DC_ITEM_SUPP_COUNTRY table must have

rows/records for item levels that are transaction level or
above. There should not be any data for below-transaction-
level items.

Capture counts from ITEM_SUPP_COUNTRY and compare to flat file
DC_ITEM_SUPP_COUNTRY.DAT to ensure that all rows are loaded.
Ensure that ITEM_SUPPLIER.ITEM is a valid ITEM_MASTER.ITEM.
Ensure that ITEM_SUPPLIER.SUPPLIER is a valid SUPS.SUPPLIER.
Ensure that ITEM_SUPP_COUNTRY.ITEM/ITEM_SUPP_COUNTRY.SUPPLIER
combination exists on ITEM_SUPPLIER.
Ensure that ITEM_SUPP_COUNTRY.ORIGIN_COUNTRY_ID is a valid
COUNTRY.COUNTRY_ID.
Ensure that ITEM_SUPP_COUNTRY.PACKING_METHOD is a valid
CODE_DETAIL.CODE where CODE_TYPE = ‘PKMT’.
Length
SKU Alpha- 25 Y ID of the stock keeping unit. ITEM VARCHAR2(25)

numeric
supplies the SKU.
ORIGIN_ Alpha- 3 Y ID of the country where the ORIGIN_ VARCHAR2(3)
COUNTRY_ID numeric item is manufactured or COUNTRY_
originates. ID
UNIT_COST Numeric 20,4 Y Current corporate unit cost UNIT_COST NUMBER(20,4)
for the SKU from the
supplier/
origin country. This field is
stored in the supplier’s
currency.
SUPP_PACK_ Numeric 12,4 Y Multiples in which orders SUPP_PACK_ NUMBER(12,4)
SIZE must be placed for the SIZE
supplier for the item.
INNER_PACK_ Numeric 12,4 Y Break pack size for this item INNER_PACK_ NUMBER(12,4)
SIZE from the supplier SIZE

Item Supplier
ROUND_LVL Alpha- 6 N Used to determine how order ROUND_LVL VARCHAR2(6)

numeric quantities are rounded to
case, layer, and pallet
Valid values are:
C
L
P
CL
LP
CLP
Default from System
Options.
ROUND_TO_ Numeric 12,4 N Inner rounding threshold ROUND_TO_ NUMBER(12,4)
INNER_PCT value. During rounding, this INNER_PCT
value is used to determine
whether to round partial
inner quantities up or down.
If the inner-fraction is less
than the threshold
proportion, it is rounded
down; if not, it is rounded
up.
For example, with an inner
size of 10 and a threshold of
80%, inner quantities 18, 29
and 8 are rounded up to 20,
30, and 10, respectively.
Quantities of 12, 27, and 35
are rounded down to 10, 20,
and 30. Quantities are never
rounded down to zero; a
quantity of 7, in the example
above, is rounded up to 10.
This column is maintained
only to default to the
Item/Supplier/Country/
Location level.
Default from System
Options.

Item Supplier
ROUND_TO_ Numeric 12,4 N Case rounding threshold ROUND_TO_ NUMBER(12,4)

CASE_PCT value. During rounding, this CASE_PCT
whether to round partial case
quantities up or down.
If the case-fraction is less
than the threshold
up. For example, with a case
size of 10 and a threshold of
80%, case quantities 18, 29,
and 8 are rounded up to 20,
30, and 10. Quantities of 12,
27, and 35 are rounded down
to 10, 20, and 30. Quantities
are never rounded down to
zero; a quantity of 7, in the
example above, is rounded
up to 10.
Location level.
Default from System
Options.
ROUND_TO_ Numeric 12,4 N Layer rounding threshold ROUND_TO_ NUMBER(12,4)
LAYER_PCT value. During rounding, this LAYER_PCT
layer quantities up or down.
If the layer-fraction in
question is less than the
Threshold proportion, it is
rounded down; if not, it is
rounded up.
Default from System
Options.

Item Supplier
ROUND_TO_ Numeric 12,4 N Pallet rounding threshold ROUND_TO_ NUMBER(12,4)

PALLET_PCT value. During rounding, this PALLET_PCT
pallet quantities up or down.
If the pallet -fraction is less
than the threshold
up. For example, with a
pallet size of 10 and a
threshold of 80%, pallet
quantities 18, 29 and 8 are
rounded up to 20, 30, and 10.
Quantities of 12, 27, and 35
are rounded down to 10, 20,
and 30. Quantities are never
rounded down to zero; a
quantity of 7, in the example
above, is rounded up to 10.
Location level.
Default from System
Options.
MIN_ORDER_ Numeric 12,4 N Minimum allowable order MIN_ORDER_ NUMBER(12,4)
QTY quantity for the item from the QTY
supplier. This parameter is
used for order quantity
validations.
MAX_ORDER_ Numeric 12,4 N Maximum allowable order MAX_ORDER_ NUMBER(12,4)
QTY quantity for the item from the QTY
supplier. This parameter is
used for order quantity
validations.
PRIMARY_ Alpha- 1 N Whether this country is the PRIMARY_ VARCHAR2(1)
COUNTRY_IND numeric primary country for the COUNTRY_
item/supplier. Each IND
item/supplier combination
must have only one primary
country. Valid values are ‘Y’
and ‘N’.
First Alpha Country ID = ‘Y’,
otherwise default = ‘N’. This
column must either be
entered for all records or null
for all records.
TI Numeric 12,4 Y Number of shipping units TI NUMBER(12,4)
(cases) that make up one tier
of a pallet. Multiply TI x HI
to get total number of units
(cases) for a pallet.

Item Supplier
HI Numeric 12,4 Y Number of tiers that make up HI NUMBER(12,4)

a complete pallet (height).
Multiply TI x HI to get total
number of units (cases) for a
pallet.
COST_UOM Alpha- 4 N A cost UOM is held to allow COST_UOM VARCHAR2(4)
numeric costs to be managed in a
UOM separate from the
standard UOM.
Default to standard UOM
(ITEM_MASTER).
LEAD_TIME Integer 4 N Number of days that will LEAD_TIME NUMBER(4)
elapse between the date an
order is written and the
delivery to the store or
warehouse from the supplier.
Default from SUPS.
PACKING_ Alpha- 6 N Whether the packing method PACKING_ VARCHAR2(6)
METHOD numeric of the item in the container is METHOD
flat or hanging. Values for
this field are stored in the
PKMT code.
Default from System
Options.
DEFAULT_UOP Alpha- 6 N Contains the default unit of DEFAULT_ VARCHAR2(6)
numeric purchase for the item/ UOP
supplier/country. Valid
values include:
Standard units of measure
C - Case
P - Pallet
Default = ‘C’
Note: If a record is in the BAD or DISCARD file and the

PRIMARY_SUPP_IND is NULL in the file, then the record
must be populated with 'N' to be loaded, or the RMS table
must be truncated and the entire file must be rerun.

Item Supplier
DC_PRICE_HIST Table
File name: DC_PRICE_HIST.DAT
Table create SQL script: DBC_CREATE_PRICE_HIST_TAB.SQL
External Oracle table created: DC_PRICE_HIST
Note: The DC_PRICE_HIST table must have rows/records

for item levels that are transaction level or above. There
cannot be any data for below-transaction-level items.

Capture counts from PRICE_HIST where PRICE_HIST.TRAN_TYPE = 0 and
PRICE_HIST.REASON = 0 and PRICE_HIST.LOC = 0, and compare to flat file
DC_PRIC_HIST.DAT to ensure that all rows are loaded.
Ensure that PRICE_HIST.ITEM is a valid ITEM_MASTER.ITEM where
ITEM_MASTER.ITEM_LEVEL <=ITEM.MASTER.TRAN_LEVEL.
Length
ITEM Alpha- 25 Y ID of the stock keeping ITEM VARCHAR2(25)

numeric unit.
UNIT_RETAIL Numeric 20,4 Y Item’s current unit retail in UNIT_RETAIL NUMBER(20,4)
the system’s primary
currency.
SELLING_UOM Alpha- 4 Y Item’s current selling unit SELLING_UOM VARCHAR2(4)
numeric of measure for the item.

Item Supplier
DC_ITEM_SUPP_COUNTRY_DIM Table
File name: DC_ITEM_SUPP_COUNTRY_DIM.DAT
Table create SQL script: DBC_CREATE_ISC_DIM_TAB.SQL
External Oracle table created: DC_ITEM_SUPP_COUNTRY_DIM
Capture counts from ITEM_SUPP_COUNTRY_DIM and compare to flat file
DC_ITEM_SUPP_COUNTRY_DIM.DAT to ensure that all rows are loaded.
Ensure that ITEM_SUPP_COUNTRY_DIM.ITEM/SUPPLIER/
ORIGIN_COUNTRY_ID combination exists in ITEM_SUPP_COUNTRY.
Ensure that ITEM_SUPP_COUNTRY_DIM.DIM_OBJECT is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘DIMO’.
Ensure that ITEM_SUPP_COUNTRY_DIM.PRESENTATION_METHOD is a valid
CODE_DETAIL.CODE where CODE_DETAIL.CODE_TYPE = ‘PCKT’.
Ensure that ITEM_SUPP_COUNTRY_DIM.LWH_UOM is a valid
UOM_CLASS.UOM with UOM_CLASS.UOM_CLASS = ‘DIMEN’.
Ensure that ITEM_SUPP_COUNTRY_DIM.WEIGHT_UOM is a valid
UOM_CLASS.UOM with UOM_CLASS.UOM_CLASS = ‘MASS’.
Ensure that ITEM_SUPP_COUNTRY_DIM.LIQUID_VOLUME_UOM is a valid
UOM_CLASS.UOM with UOM_CLASS.UOM_CLASS = ‘LVOL’.
Length
ITEM Alpha- 25 Y ID of the stock keeping ITEM VARCHAR2(25)

numeric unit.
supplies the SKU.
ORIGIN_ Alpha- 3 Y ID of the country in ORIGIN_ VARCHAR2(3)
COUNTRY_ID numeric which the item is COUNTRY_ID
manufactured or
originates.
DIM_OBJECT Alpha- 6 Y Type of dimension DIM_OBJECT VARCHAR2(6)
numeric object being defined.
Valid values exist in the
code head/code details
tables in RMS in code
type DIMO:
EA – Each
IN – Inner
CA – Case
PA – Pallet
The 2-letter code should
be included in the file.

Item Supplier
PRESENTATION_ Alpha- 6 N How the product is PRESENTATION_ VARCHAR2(6)

METHOD numeric presented. Valid values METHOD
exist in the RMS code
head/code details
tables with code type
‘PCKT’.
LENGTH Numeric 12,4 N Length of the packaging LENGTH NUMBER(12,4)
used when defining
volume.
This field is not
required but should be
populated when the
width, height, and
LWH_UOM are
defined.
WIDTH Numeric 12,4 N Width of the packaging WIDTH NUMBER(12,4)
used when defining
volume.
This field is not
populated when the
length, height, and
LWH_UOM are
defined.
HEIGHT Numeric 12,4 N Height of the packaging HEIGHT NUMBER(12,4)
used when defining
volume.
This field is not
populated when the
length, width, and
LWH_UOM are
defined.
LWH_UOM Alpha- 4 N Unit of measure that the LWH_UOM VARCHAR2(4)
numeric length, height, and
width values are
defined in.
This field is not
populated when the
length, width, and
height are defined.
Default from System
Options.

Item Supplier
WEIGHT Numeric 12,4 N Gross weight of the WEIGHT NUMBER(12,4)

product.
This field is not
populated in
conjunction with the
NET_WEIGHT,
WEIGHT_UOM,
TARE_WEIGHT, and
TARE_TYPE values.
NET_ WEIGHT Numeric 12,4 N Net weight of the NET_ WEIGHT NUMBER(12,4)
product.
This field is not
populated in
WEIGHT, WEIGHT_
UOM, TARE_WEIGHT,
and TARE_TYPE
values.
WEIGHT_UOM Alpha- 4 N Weight UOM in which WEIGHT_UOM VARCHAR2(4)
numeric the weight, net weight,
and tare weight are
defined.
This field is not
populated in
WEIGHT, NET_
WEIGHT, TARE_
WEIGHT, and TARE_
TYPE values.
Default from System
Options.
LIQUID_ Numeric 12,4 N Liquid volume of the LIQUID_ NUMBER(12,4)
VOLUME item. This field is not VOLUME
required, but when
used, should be used in
LIQUID_VOLUME_
UOM field.
LIQUID_ Alpha- 4 N Liquid volume unit of LIQUID_ VARCHAR2(4)
VOLUME_UOM numeric measure. VOLUME_UOM
STAT_CUBE Numeric 12,4 N Dimensions of the STAT_CUBE NUMBER(12,4)
statistical case.

Item Supplier
TARE_WEIGHT Numeric 12,4 N Weight of the tare. TARE_WEIGHT NUMBER(12,4)

This field is not
populated in
WEIGHT, NET_
WEIGHT, WEIGHT_
UOM, and TARE_TYPE
values.
TARE_TYPE Alpha- 6 N Whether the tare is TARE_TYPE VARCHAR2(6)
numeric considered wet or dry.
Valid values are:
D - Dry
W - Wet
This field is not
populated in
WEIGHT, NET_
WEIGHT, WEIGHT_
UOM, and TARE_
WEIGHT values.

Item Supplier
DC_LOAD_ITEM_SUPPLIER.KSH Segment Wrapper / Load Script

tables.
The dc_load_item_supplier.ksh script utilizes a common library file and configuration
It must exist in the data directory defined in the common configuration file.
LOAD_ITEM_SUPPLIER
This function contains a PL/SQL block that selects from the DC_ITEM_SUPPLIER
external table and inserts the data to the RMS ITEM_SUPPLIER table.
DC_ITEM_SUPPLIER to ITEM_SUPPLIER Column Defaults

PRIMARY_SUPP_IND ‘N’ If NULL
Lowest Supplier ID = ‘Y’,
otherwise default = ‘N’
Note: The table requires that
all records contain
PRIMARY_SUPP_IND
information, or all records can
have this indicator set to
NULL.
PALLET_NAME From SYSTEM_OPTIONS If NULL
CASE_NAME From SYSTEM_OPTIONS If NULL
INNER_NAME From SYSTEM_OPTIONS If NULL
Required file to load: dc_item_supplier.dat

Item Supplier
LOAD_ITEM_SUPP_COUNTRY
This function contains a PL/SQL block that selects from the
DC_ITEM_SUPP_COUNTRY external table and inserts the data to the RMS
ITEM_SUPP_COUNTRY table.
DC_ITEM_SUPP_COUNTRY to ITEM_SUPP_COUNTRY Column Defaults

PRIMARY_SUPP_IND From ITEM_SUPPLIER If NULL
PRIMARY_COUNTRY_IND ‘N’ If NULL
First Alpha Country ID = ‘Y’,
otherwise default = ‘N’
Note: The table requires that
all records contain this
indicator, or all records can
have this indicator set to
NULL.
ROUND_LVL From SYSTEM_OPTIONS If NULL
ROUND_TO_INNER_PCT From SYSTEM_OPTIONS If NULL
ROUND_TO_CASE_PCT From SYSTEM_OPTIONS If NULL
ROUND_TO_LAYER_PCT From SYSTEM_OPTIONS If NULL
ROUND_TO_PALLET_PCT From SYSTEM_OPTIONS If NULL
PACKING_METHOD From SYSTEM_OPTIONS If NULL
DEFAULT_UOP ’C’ If NULL

LEAD_TIME From SUPS If NULL
Required file to load: dc_item_supp_country.dat
INSERT_RPM_ITEM_ZONE_PRICE
This function selects from the DC_PRICE_HIST external table and joins with
ITEM_MASTER and RPM_MERCH_RETAIL_DEF to insert data to the RPM
RPM_ITEM_ZONE_PRICE table.
The function retrieves the regular zone group ID for the department of the items in the
DC_PRICE_HIST table and joins data with the ITEM_MASTER and
RPM_MERCH_RETAIL_DEF tables. It performs a bulk collect of this data and loops
through the results to insert into the RPM_ITEM_ZONE_PRICE table. For the
insert/select, join DC_PRICE_HIST for each item and RPM_ZONE for the department’s
ZONE_GROUP_ID.

Item Supplier
The following table indicates the values retrieved for data insert. This function uses the
primary currency from the SYSTEM_OPTIONS table. If the zone currency and the
primary currency are different, the function converts the UNIT_RETAIL to the zone
currency.
DC_PRICE_HIST to RPM_ITEM_ZONE_PRICE Column Defaults
ITEM_ZONE_PRICE_ID Use sequence

ITEM DC_PRICE_HIST.ITEM
ZONE_ID RPM_ZONE.ZONE_ID For the department
ZONE_GROUP_ID
STANDARD_RETAIL DC_PRICE_HIST.UNIT_RETAIL
STANDARD_ RPM_ZONE.CURRENCY_CODE For the department
RETAIL_CURRENCY ZONE_GROUP_ID
STANDARD_UOM DC_PRICE_HIST.UOM
SELLING_RETAIL DC_PRICE_HIST.UNIT_RETAIL
SELLING_ RETAIL_CURRENCY RPM_ZONE.CURRENCY_CODE For the department
ZONE_GROUP_ID
SELLING_UOM DC_PRICE_HIST.UOM
MULTI_UNIT_CURRENCY RPM_ZONE.CURRENCY_CODE For the department
ZONE_GROUP_ID
Required file to load: dc_price_hist.dat

Item Supplier
INSERT_PRICE_HIST
This function inserts the 0 tran_type, 0 reason, 0 location record into the RMS
PRICE_HIST table:
It gets the UNIT_COST value from the primary supplier and primary country record
in the DC_ITEM_SUPP_COUNTRY table for each item.
It gets the UNIT RETAIL, SELLING UOM values from the DC_PRICE_HIST table.
It gets the primary currency from the SYSTEM_OPTIONS table and the supplier’s
currency from the SUPS table. If these values are different, it converts the
UNIT_COST to the primary currency (uses one insert/select for records where the
supplier currency equals the primary currency (no conversion necessary), uses a
second for where they are unequal and calls CURRENCY_SQL.CONVERT_VALUE).
The following table defines the default values in the RMS table when no data is retrieved:
PRICE_HIST Column Defaults
Column Name (RMS Table) Default Value
ACTION_DATE VDATE
POST_DATE VDATE
SELLING_UNIT_RETAIL DC_PRICE_HIST.UNIT_RETAIL
Required file to load: dc_price_hist.dat
LOAD_ITEM_SUPP_COUNTRY_DIM
This function contains a PL/SQL block that selects from the
DC_ITEM_SUPP_COUNTRY_DIM external table and inserts the data to the RMS
ITEM_SUPP_COUNTRY_DIM table.
Most of the columns from the external Oracle table listed above directly map to the RMS
table. The following table defines the default values in the RMS table if no information is
DC_ITEM_SUPP_COUNTRY_DIM to ITEM_SUPP_COUNTRY_DIM Column Defaults

LWH_UOM From SYSTEM_OPTIONS If NULL
WEIGHT_UOM From SYSTEM_OPTIONS If NULL
Required file to load: dc_item_supp_country_dim.dat

Item Supplier
After using the data conversion toolset for Item Supplier, you must manually load the
ITEM_SUPP_COUNTRY_BRACKET_COST table. This table is required if the supplier
has bracket costing.
Manual data loading can be done online through Merchandising applications (RMS or
RPM), or you can create scripts. Manual data loading is not included as part of this data

Item Location
Item Location
This section describes data conversion for the following RMS/RPM tables, listed in the
order that they must be loaded:
ITEM_LOC
ITEM_LOC_SOH
RPM_FUTURE_RETAIL
ITEM_SUPP_COUNTRY_LOC
FUTURE_COST
PRICE_HIST
Note: Only data with corresponding RMS ITEM_MASTER

records are loaded. Additionally, only items with
ITEM_SUPP_COUNTRY data are loaded into the
ITEM_SUPP_COUNTY_LOC table.

Segment load script dc_load_item_location.ksh
This wrapper calls the external Oracle table create script dbc_create_item_loc_tab.sql.
External Oracle table create script dbc_create_item_loc_tab.sql
Data Flow
The following diagram shows the data flow for the Item Location functional area:

Item Location
Prerequisites
Before you begin using the data conversion toolset for Item Location, you must complete
data conversion for Items and Item Supplier:
Fashion Items
Hardlines
Grocery Items
Pack Items
Item Supplier

The dc_load_item_location.ksh script calls each of the SQL scripts in a specific order. The
File Format


Name, Data Type, and length define the physical external table.

Item Location
DC_ITEM_LOC Table
File name: DC_ITEM_LOC.DAT
Table create SQL script: DBC_CREATE_ITEM_LOC_TAB.SQL
External Oracle table created: DC_ITEM_LOC
Suggested post-loading validation (sequence after dc_load_item_location.ksh:
Ensure that ITEM_SEASONS.ITEM is a valid ITEM_MASTER.ITEM where
ITEM_MASTER.ITEM_LEVEL <=ITEM_MASTER.TRAN_LEVEL.
Ensure that ITEM_SEASONS.SEASON_ID/PHASE_ID combination exists in
PHASES.
Ensure that ITEM_LOC.ITEM is a valid ITEM_MASTER.ITEM where
Ensure that ITEM_LOC_SOH.ITEM is a valid ITEM_MASTER.ITEM where
ITEM_MASTER.ITEM_LEVEL = ITEM_MASTER.TRAN_LEVEL.
Ensure that ITEM_LOC.LOC is a valid V_LOCATION.LOCATION_ID with
V_LOCATION.STOCKHOLDING_IND = ‘Y’.
Ensure that ITEM_LOC_SOH.ITEM/LOC combination exists on ITEM_LOC.
Ensure that ITEM_LOC.ITEM_PARENT/ITEM)GRANDPARENT for the item are
the same as ITEM_MASTER.ITEM_PARENT, ITEM_GRANDPARENT.
Ensure that ITEM_LOC.SELLING_UOM is a valid UOM_CLASS.UOM.
Ensure that ITEM_LOC.PROMO_SELLING_UOM (if not NULL) is a valid
UOM_CLASS.UOM.
Ensure that ITEM_LOC.MULTI_SELLING_UOM (if not NULL) is a valid
UOM_CLASS.UOM.
Ensure that ITEM_LOC.SOURCE_WH is a valid WH.WH where
STOCKHOLDING_IND = ‘Y’ if ITEM_LOC.SOURCE_METHOD = ‘W’.
Ensure that ITEM_LOC.PRIMARY_COST_PACK (if not NULL) is valid
ITEM_MASTER.ITEM with ITEM_MASTER.SIMPLE_PACK_IND = ‘Y’ and that the
ITEM_LOC.ITEM = PACKITEM.ITEM when ITEM_LOC.PRIMARY_COST_PACK =
PACKITEM.PACK_NO.
Length
SKU Alpha- 25 Y Unique identifier for ITEM VARCHAR2(25)

numeric the stock keeping unit
(item, product, article,
or other).
LOCATION Integer 10 Y Identifier for the store, LOCATION NUMBER(10)
warehouse, or external
finisher.
LOC_TYPE Alpha- 1 Y Type of location. LOC_TYPE VARCHAR2(1)
numeric Valid values are:
S - Store
W - Warehouse
E - External finisher

Item Location
PRIMARY_LOC_ Alpha- 1 N Note: This is not in thePRIMARY_LOC_ VARCHAR2(1)

IND numeric RMS table. It is needed IND
for inserting into
ITEM_SUPP_
COUNTRY_LOC.
Populate for all
ITEM_LOCs or leave
NULL for all
ITEM_LOCs.
Valid values are:
Y - This is the primary
location used for
insertion to
ITEM_SUPP_
COUNTRY_LOC.
N - Not the primary
location.
SELLING_UNIT_ Numeric 20,4 N Current selling unit SELLING_UNIT_ NUMBER(20,4)
RETAIL retail for the item/ RETAIL
location. This value
should contain the
current regular unit
retail or clearance unit
retail but should not
reflect any
promotional retails.
for sellable items but
not required for non-
sellable items.
This should be in
location currency.
SELLING_UOM Alpha- 4 N Unit of measure in SELLING_UOM VARCHAR2(4)
numeric which the current
selling unit retail is
defined. Value values
must exist in the RMS
UOM_CLASS table,
and a conversion must
exist between the
item’s standard UOM
and the selling UOM.
To convert between
UOMs in different
UOM classes, ‘case’
type dimensions must
be defined at the
item/supp/country
level for the UOM.
for sellable items but
not required for non-
sellable items.

Item Location
TAXABLE_IND Alpha- 1 N Whether the item is TAXABLE_IND VARCHAR2(1)

numeric taxable at a store
location.
Defaults to ‘N’ when
NULL. Any value
passed in is
overwritten with an
‘N’ value for
warehouse locations.
LOCAL_SKU_ Alpha- 250 N A location-specific LOCAL_ITEM_ VARCHAR2(250)
DESC numeric description for the DESC
item that differs from
the item’s primary
description.
LOCAL_SHORT_ Alpha- 120 N Shortened location- LOCAL_SHORT_ VARCHAR2(120)
DESC numeric specific description for DESC
the item. This field can
be used by point of
sale systems or other
systems where display
space is limited.
Defaults to 120
characters of the
LOCAL_ITEM_DESC
when NULL.
TI Numeric 12,4 N Number of cartons on TI NUMBER(12,4)
a layer of a pallet for
the item/location
(tiers).
HI Numeric 12,4 N Number of layers on a HI NUMBER(12,4)
pallet for the
item/location (height).
STORE_ORD_ Alpha- 1 Y Case pack multiple in STORE_ORD_ VARCHAR2(1)
MULT numeric which this item must MULT
be shipped from a
warehouse to the
location.

Item Location
TICKET_MEAS_ Numeric 12,4 N Size of an each in MEAS_OF_EACH NUMBER(12,4)

OF_EACH terms of the ticketing
UOM (for example, 12
oz.). This value is
used in ticketing only.
TICKET_MEAS_ Numeric 12,4 N Size to be used on the MEAS_OF_PRICE NUMBER(12,4)
OF_PRICE ticket in terms of the
ticketing UOM. For
example, to have a
ticket label print the
price per ounce, this
value would be 1. To
show the price per 100
grams, this value
would be 100. This
value is used in
ticketing only.
TICKET_UOM Alpha- 4 N Unit of measure to be UOM_OF_PRICE VARCHAR2(4)
numeric used on tickets for this
item. This value is
used in conjunction
with the ticket
measure of each and
ticket measure of price
fields.
PRIMARY_COST_ Alpha- 25 N Item number that is a PRIMARY_COST_ VARCHAR2(25)
PACK numeric simple pack containing PACK
the item in the item
column for this record.
If populated, the cost
of the future cost table
is driven from the
simple pack and the
deals and cost changes
for the simple pack.
INBOUND_ Integer 2 N Number of days INBOUND_ NUMBER(2)
HANDLING_ required to put away HANDLING_DAYS
DAYS or cross-dock an item
at a warehouse.
This value is used for
warehouse locations
only.
SOURCE_WH Integer 10 N Value used when SOURCE_WH NUMBER(10)
doing manual store-
level replenishment
using the inventory
request APIs.
Required if SOURCE_
METHOD = ‘W’;
NULL otherwise.

Item Location
SOURCE_ Alpha- 1 N How inventory for this SOURCE_ VARCHAR2(1)

METHOD numeric item is sourced to a METHOD
store when doing
manual store-level
replenishment using
the inventory request
APIs.
Valid values are:
W - Warehouse
S - Supplier
MULT_UNITS Numeric 12,4 N Number of qualifying MULT_UNITS NUMBER(12,4)
units, if multi-unit
pricing is currently
being used for this
item/location. For
example, if the item is
multi-priced as 3 for
$25, this field contains
the 3.
MULTI_UNIT_ Numeric 20,4 N Multi-retail price, if MULTI_UNIT_ NUMBER(20,4)
RETAIL multi-unit pricing is RETAIL
currently being used
for this item/location.
For example, if the
item is multi-priced as
3 for $25, this field
contains the 25.
This should be in
location currency.
MULTI_SELLING_ Alpha- 4 N Unit of measure in MULTI_SELLING_ VARCHAR2(4)
UOM numeric which the multi-unit UOM
retail is defined, if
multi-unit pricing is
currently being used
for this item/location.
NOMINAL_ Numeric 12,4 N Nominal weight for a AVERAGE_ NUMBER(12,4)
WEIGHT simple pack catch WEIGHT
weight item.
Required for a simple
pack catch weight
item. NULL for all
others.
Note: If the PRIMARY_LOC_IND field is NULL, any records

that are not loaded and are placed in the BAD or DISCARD
file must have an ‘N’ value for this field to rerun. The
alternative method is to truncate the RMS table and rerun
the entire file.

Item Location
DC_LOAD_ITEM_LOCATION.KSH Segment Wrapper / Load Script

tables.
The dc_load_item_location.ksh script utilizes a common library file and configuration
LOAD_ITEM_LOC
This function contains a PL/SQL block that selects from the DC_ITEM_LOC external
table and inserts the data to the RMS ITEM_LOC table. It joins the external table with a
virtual table that is a union of store and warehouse, so that only stockholding
warehouses are included. This function performs two inserts, as follows:
The primary supplier and primary country fields are populated if the item is
orderable. First, it populates the RMS ITEM_LOC table with the values from
DC_ITEM_LOC joined with a virtual table that selects the primary supplier and the
supplier’s primary country for the item from THE ITEM_SUPP_COUNTRY table.
Also, it joins the table with ITEM_MASTER to get the ORDER_AS_TYPE value for
the RECEIVE_AS_TYPE column. This is populated only for buyer packs.
For the sellable only items, there is no primary supplier or primary country. This is
done by limiting the insert to items that do not exist in the RMS
ITEM_SUPP_COUNTRY table.
The following table defines the default value in the RMS table if no information is
DC_ITEM_LOC to ITEM_LOC Column Defaults

TAXABLE_IND ‘N’ If NULL
CLEAR_IND ‘N’
STORE_PRICE_IND ‘N’
RPM_IND ‘N’

Item Location
LOCAL_SHORT_DESC RTRIM of SUBSTRB 120 char If NULL

of LOCAL_ITEM_DESC
ITEM_MASTER.SHORT_
DESC when LOCAL_ITEM_
DESC is NULL.
REGULAR_UNIT_RETAIL SELLING_UNIT_RETAIL
UNIT_RETAIL SELLING_UNIT_RETAIL
MULTI_UNIT_RETAIL SELLING_UNIT_RETAIL If NULL
STATUS_UPDATE_DATE SYSDATE
STATUS ‘A’
LOCAL_ITEM_DESC Default to ITEM_DESC Populated with
ITEM_MASTER.ITEM_DESC
when it is NULL
RECEIVE_AS_TYPE ITEM_MASTER.ORDER_ If item is a buyer pack,
AS_TYPE PACK_TYPE=’B’, and if the
location is a warehouse,
LOC_TYPE=’W’
ITEM_PARENT ITEM_MASTER.ITEM_
PARENT
ITEM_GRANDPARENT ITEM_MASTER.ITEM_
GRANDPARENT
Required file to load: dc_item_loc.dat
INSERT_ITEM_LOC_SOH
table and inserts the data to the RMS ITEM_LOC_SOH table. It joins the external Oracle
table with a virtual table that is a union of store and warehouse, so that only
stockholding warehouses are included. It joins the external table with ITEM_MASTER to
insert only transactional items (ITEM_LEVEL = TRAN_LEVEL). This function performs
two inserts, as follows:
It joins with RMS ITEM_SUPP_COUNTRY and SUPS tables to get the UNIT_COST
and supplier currency, to convert the UNIT_COST into location currency.
For sellable only items, it does not join with the RMS ITEM_SUPP_COUNTRY and
SUPS tables. It creates an insert statement that excludes items that exist in
ITEM_SUPP_COUNTRY and sets UNIT_COST to NULL.

Item Location
DC_ITEM_LOC to ITEM_LOC_SOH Column Defaults

STOCK_ON_HAND 0
IN_TRANSIT_QTY 0
PACK_COMP_INTRAN 0
PACK_COMP_SOH 0
TSF_RESERVED_QTY 0
PACK_COMP_RESV 0
TSF_EXPECTED_QTY 0
PACK_COMP_EXP 0
RTV_QTY 0
NON_SELLABLE_QTY 0
CUSTOMER_RESV 0
CUSTOMER _BACKORDER 0
PACK_COMP_CUST_RESV 0
PACK_COMP_CUST_BACK 0
PACK_COMP_ NON_SELLABLE 0
ITEM_PARENT ITEM_MASTER.ITEM_
PARENT
ITEM_GRANDPARENT ITEM_MASTER.ITEM_
GRANDPARENT
AV_COST ITEM_SUPP_COUNTRY.
UNIT_COST of the primary
supplier/primary country
converted to location currency
PRIMARY_SUPP ITEM_SUPP_COUNTRY.SUPPLIER
with PRIMARY_SUPP_IND = ‘Y’
for item
PRIMARY_CTRY ITEM_SUPP_COUNTRY.ORIGIN_
COUNTRY_ID with
PRIMARY_SUPP_IND = ‘Y” and
PRIMARY_COUNTRY_IND = ‘Y’
for item
AVERAGE_WEIGHT NULL Only defined if item
is a simple pack catch
weight item.

Item Location
INSERT_RPM_FUTURE_RETAIL
table and inserts the data into the RPM RPM_FUTURE_RETAIL table.
Many of the columns from the external Oracle table defined above map directly to the
RPM table. The exception is to retrieve dept, class, and subclass values for each item from
the ITEM_MASTER table. The currency code is retrieved from the STORE or WH table,
based on the location and the location type.
The RPM_FUTURE_RETAIL table is loaded for sellable transaction level items only.
Even though SELLING_UNIT_RETAIL and SELLING_UOM are not required fields in
the DC_ITEM_LOC table, they are required for sellable items. Without the values,
inserting into RPM_FUTURE_RETAIL table will fail.
Warehouse locations are conditionally inserted into the RPM_FUTURE_RETAIL table,
based on the RPM system option RECOGNIZE_WH_AS_LOCATIONS. This uses one
insert for stores and checks this system option before the insert for warehouses.
Warehouses must be stockholding locations.
The following table defines the default values in the RPM table if no information is
DC_ITEM_LOC to RPM_FUTURE_RETAIL Column Defaults
FUTURE_RETAIL_ID Sequence
MULTI_UNIT_RETAIL_ SELLING_UNIT_RETAIL_ Populate if
CURRENCY CURRENCY MULTI_UNIT_RETAIL
is NOT NULL
SELLING_UNIT_RETAIL_ Lookup STORE or WH currency
CURRENCY
ACTION_DATE VDATE
ZONE_NODE_TYPE If LOC_TYPE = ‘S’ then 0
If LOC_TYPE = ‘W’ then 2

Item Location
INSERT_ITEM_SUPP_COUNTRY_LOC
This function inserts the data into the RMS ITEM_SUPP_COUNTRY_LOC table.
The DC_ ITEM_LOC external Oracle table is joined with the RMS
ITEM_SUPP_COUNTRY table to insert data into the RMS ITEM_SUPP_COUNTRY_LOC
table for the item’s primary supplier/primary country. The function also joins the
external Oracle table with a virtual table that is a union of store and warehouse, so that
only stockholding warehouses are included.
DC_ITEM_LOC to ITEM_SUPP_COUNTRY_LOC Column Defaults

PICKUP_LEAD_TIME LEAD_TIME If NULL
PRIMARY_LOC_IND If NULL, lowest loc ID = ‘Y’,
otherwise default = ‘N’.
The table requires that all
records contain
PRIMARY_LOC_IND
information, or all records can
have this indicator set to NULL.
ROUND_LVL ITEM_SUPP_COUNTRY.
ROUND_LVL
ROUND_TO_INNER_PCT ITEM_SUPP_COUNTRY.
ROUND_TO_INNER_PCT
ROUND_TO_CASE_PCT ITEM_SUPP_COUNTRY.
ROUND_TO_CASE_PCT
ROUND_TO_LAYER_PCT ITEM_SUPP_COUNTRY.
ROUND_TO_LAYER_PCT
ROUND_TO_PALLET_PCT ITEM_SUPP_COUNTRY.
ROUND_TO_PALLET_PCT
INSERT_FUTURE_COST
This function selects from the DC_ITEM_LOC external table, joined with the RMS
ITEM_SUPP_COUNTRY_LOC table, and inserts data into the RMS FUTURE_COST table
for the item’s primary supplier/primary country. Data is inserted into the
RMS_FUTURE_COST table for sellable items only.
This function uses the UNIT_COST from the RMS ITEM_SUPP_COUNTRY_LOC table as
the value for all the cost columns. It joins the external table with a virtual table that is a
union of store and warehouse, so that only stockholding warehouses are included.

Item Location
DC_ITEM_LOC to FUTURE_COST Column Defaults
ACTIVE_DATE VDATE
START_IND Y
CALC_DATE VDATE
INSERT_PRICE_HIST
This function selects from the DC_ITEM_LOC external table, joined with the RMS
PRICE_HIST table for the item’s 0 tran_type, 0 reason, 0 location record, to insert data
into the RMS PRICE_HIST table for each item/location combination.
The UNIT_COST is already in the primary currency in the 0 PRICE_HIST record, so it
must be converted to local currency. The function retrieves the CURRENCY_CODE from
the RMS STORE or WH table, based on the location and the LOC_TYPE. It retrieves only
stockholding warehouses. This function performs the following inserts:
The location currency (STORE/WH) is equal to the primary currency
The location currency is different from the primary currency, so it requires the
conversion function to convert UNIT_COST.
DC_ITEM_LOC to PRICE_HIST Column Defaults
MULTI_SELLING_UOM SELLING_UOM If NULL

SELLING_UNIT_RETAIL UNIT_RETAIL If NULL
MULTI_UNIT_RETAIL UNIT_RETAIL If NULL
ACTION_DATE VDATE
TRAN_TYPE 0
REASON 0

Others
Others
UDA_ITEM_LOV
UDA_ITEM_DATE
UDA_ITEM_FF
VAT_ITEM
ITEM_SEASONS
ITEM_TICKET
Segment load script dc_load_item_other.ksh
– dbc_create_uda_item_lov.sql
– dbc_create_uda_item_date.sql
– dbc_create_uda_item_ff.sql
– dbc_create_vat_item.sql
– dbc_create_item_seasons.sql
– dbc_create_item_ticket.sql
Data Flow
The following diagram shows the data flow for the Items–Others functional area:
DATA FILES
PREPARATION SCRIPTS CONVERSION TABLES RMS/RPM TABLES
dc_uda_item_lov.dat
dc_uda_item_date.dat dbc_create_uda_item_lov_tab.sql dc_uda_item_lov uda_item_lov
LOAD PROCESS
dc_uda_item_ff.dat dbc_create_uda_item_date_tab.sql dc_uda_item_date uda_item_date
dc_uda_vat_item.dat dbc_create_uda_item_ff_tab.sql dc_uda_item_ff uda_item_ff
dc_load_item_other.ksh
dc_item_seasons.dat dbc_create_vat_item_tab.sql dc_vat_item vat_item
dc_item_ticket.dat dbc_create_item_seasons_tab.sql dc_item_seasons item_seasons
dbc_create_item_ticket_tab.sql dc_item_ticket item_ticket

Others
Prerequisites
Before you begin using the data conversion toolset for Item Others, you must complete
data conversion for Items, Item Supplier, and Item Location:
Fashion Items
Hardlines
Grocery Items
Pack Items
Item Supplier
Item Location

The dc_load_item_other.ksh script calls each of the SQL scripts in a specific order. The
File Format



Others
DC_UDA_ITEM_LOV Table
File name: DC_UDA_ITEM_LOV.DAT
Table create SQL script: DBC_CREATE_UDA_ITEM_LOV_TAB.SQL
External Oracle table created: DC_UDA_ITEM_LOV
Suggested post-loading validation (sequence after dc_load_item_other.ksh:
Ensure that UDA_ITEM_LOV.ITEM is a valid ITEM_MASTER.ITEM where
Ensure that UDA_ITEM_LOV.UDA_ID/UDA_VALUE combination exists in
UDA_VALUES.
Ensure that any UDA_ITEM_LOV.ITEM with a UDA_ITEM_LOV.UDA_ID where
UDA.SINGE_VALUE_IND = ‘Y’ has no other UDA_ITEM_LOV rows.
Length
ITEM Alpha- 25 Y Item number of item associated ITEM VARCHAR2(25)

numeric with the user-defined attribute
(UDA). Valid values are any
item from the item files: style,
SKU, or pack.
UDA_ID Integer 5 Y UDA associated with the item, UDA_ID NUMBER(5)
where the UDA is a list of values
(UDA has a DISPLAY_TYPE of
‘LV’).
UDA_ID field in the dc_uda.dat
file.
UDA_VALUE Integer 3 Y List of values value of the UDA. UDA_VALUE NUMBER(3)
UDA_VALUE field in the
UDA_VALUES table in RMS for
the UDA_ID in this file.

Others
DC_UDA_ITEM_DATE Table
File name: DC_UDA_ITEM_DATE.DAT
Table create SQL script: DBC_CREATE_UDA_ITEM_DATE_TAB.SQL
External Oracle table created: DC_UDA_ITEM_DATE
Ensure that UDA_ITEM_DATE.ITEM is a valid ITEM_MASTER.ITEM, where
Ensure that UDA_ITEM_DATE.UDA_ID is a valid UDA.UDA_ID with
UDA.DISPLAY_TYPE of ‘DT’.
Ensure that any UDA_ITEM_DATE.ITEM with a UDA_ITEM_DATE.UDA_ID where
UDA.SINGE_VALUE_IND = ‘Y’ has no other UDA_ITEM_DATE rows.
Length
ITEM Alpha- 25 Y Item number of item associated with ITEM VARCHAR2(25)

numeric UDA. Valid values are any item from
the item files: style, SKU, or pack.
UDA_ID Integer 5 Y User-defined attribute associated UDA_ID NUMBER(5)
with the item, where the UDA is a
date (UDA has a DISPLAY_TYPE of
‘DT’).
Valid values come from the UDA_ID
field in the dc_uda.dat file.
UDA_DATE Alpha- 11 Y Date value associated with the UDA. UDA_DATE DATE
numeric Valid values are dates in the date
format.
Date format is ‘DDMONYYYY’ (for
example, 02JAN2007).

Others
DC_UDA_ITEM_FF Table
File name: DC_UDA_ITEM_FF.DAT
Table create SQL script: DBC_CREATE_UDA_ITEM_FF_TAB.SQL
External Oracle table created: DC_UDA_ITEM_FF
Ensure that UDA_ITEM_FF.ITEM is a valid ITEM_MASTER.ITEM where
Ensure that UDA_ITEM_FF.UDA_ID is a valid UDA.UDA_ID with
UDA.DISPLAY_TYPE of ‘FF’.
Ensure that any UDA_ITEM_FF.ITEM with a UDA_ITEM_FF.UDA_ID where
UDA.SINGE_VALUE_IND = ‘Y’ has no other UDA_ITEM_FF rows.
Length
ITEM Alpha- 25 Y Item number of item associated with ITEM VARCHAR2(25)

numeric UDA. Valid values are any item
from the item files: style, SKU, or
pack.
UDA_ID Integer 5 Y User-defined attribute associated UDA_ID NUMBER(5)
with the item, where the UDA is
free-form text (UDA has a
DISPLAY_TYPE of ‘FF’).
UDA_ID field in the dc_uda.dat file.
UDA_TEXT Alpha- 250 Y Text value associated with the UDA. UDA_TEXT VARCHAR2(250)
numeric

Others
DC_VAT_ITEM Table
File name: DC_VAT_ITEM.DAT
Table create SQL script: DBC_CREATE_VAT_ITEM_TAB.SQL
External Oracle table created: DC_VAT_ITEM
Ensure that VAT_ITEM.ITEM is a valid ITEM_MASTER.ITEM where
Ensure that VAT_ITEM.VAT_REGION is a valid VAT_REGION.VAT_REGION.
Ensure that VAT_ITEM.VAT_CODE/VAT_RATE is a valid combination in
VAT_CODE_RATES, where VAT_ITEM.ACTIVE_DATE >=
VAT_CODE_RATES.ACTIVE_DATE, and no other row on VAT_CODE_RATES
exists for the combination with a greater ACTIVE_DATE that is still <=
VAT_ITEM.ACTIVE_DATE.
Length
ITEM Alpha- 25 Y Item number of item associated ITEM VARCHAR2(25)

numeric with the VAT region. Valid
values are any item from the item
files: style, SKU, or pack.
VAT_ Integer 4 Y Unique identifier of VAT region VAT_REGION NUMBER(4)
REGION associated with the item. Valid
values come from the
VAT_REGION field in the
dc_vat_region.dat file.
VAT_TYPE Alpha- 1 Y Whether the VAT rate is used for VAT_TYPE VARCHAR2(1)
numeric purchasing (cost), selling (retail),
or both. Valid values are from
the ‘VTTP’ code type: ‘C’, ‘R’,
or ‘B’.
VAT_CODE Alpha- 6 Y Unique identifier of value-added VAT_CODE VARCHAR2(6)
numeric tax code, used to determine
which items are subject to VAT.
Valid values are:
S - Standard
C - Composite
Z - Zero
E - Exempt
VAT_CODE column in the
dc_vat_codes.dat file.

Others
VAT_RATE Numeric 20,10 Y Rate of the VAT for the item/ VAT_RATE NUMBER(20,10)
VAT region combination.
VAT_RATE column in the
dc_vat_code_rates.dat file. These
values exist in the
VAT_CODE_RATES table.
ACTIVE_ Alpha- 11 Y Date the item/VAT region ACTIVE_DATE DATE
DATE numeric combination is active.
Date format is ‘DDMONYYYY’

Others
DC_ITEM_SEASONS Table
File name: DC_ITEM_SEASONS.DAT
Table create SQL script: DBC_CREATE_ITEM_SEASONS_TAB.SQL
External Oracle table created: DC_ITEM_SEASONS
Ensure that ITEM_SEASONS.ITEM is a valid ITEM_MASTER.ITEM where
Ensure that ITEM_SEASONS.SEASON_ID/PHASE_ID combination exists in
PHASES.
Capture count from ITEM_SEASONS and compare to flat file
DC_ITEM_SEASONS.DAT to ensure that all rows are loaded.

Definition
Length
ITEM Alpha- 25 Y Item number of item. Valid values ITEM VARCHAR2(25)

numeric are any item from the item files:
style, SKU, or pack.
SEASON_ID Integer 3 Y Identifier of the product season SEASON_ID NUMBER(3)
associated to the item.
SEASON_ID field of the SEASONS
table in RMS.
PHASE_ID Integer 3 Y Identifier of the season phase PHASE_ID NUMBER(3)
associated with the item.
PHASE_ID field from the PHASES
table in RMS, for the given
SEASON_ID.


Others
DC_ITEM_TICKET Table
File name: DC_ITEM_TICKET.DAT
Table create SQL script: DBC_CREATE_ITEM_TICKET_TAB.SQL
External Oracle table created: DC_ITEM_TICKET
Suggested post-loading validation (sequence after dc_load_item_other.ksh):
Ensure that ITEM_TICKET.ITEM is a valid ITEM_MASTER.ITEM, where
Ensure that ITEM_TICKET.TICKET_TYPE_ID is a valid
TICKET_TYPE_HEAD.TICKET_TYPE_ID.
Capture the count from ITEM_TICKET and compare to flat file
DC_ITEM_TICKET.DAT to ensure that all rows are loaded.
Length
ITEM Alpha- 25 Y Item number of item. Valid ITEM VARCHAR2(25)

numeric values are any item from the item
files: style, SKU, or pack.
TICKET_ Alpha- 4 Y Unique identifier of ticket or label TICKET_TYPE_ VARCHAR2(4)
TYPE_ID numeric type associated with item. ID
TICKET_TYPE_ID field in the
DC_TICKET_TYPE_HEAD file.
PRINT_ON_ Alpha- 1 N Whether this type of ticket PRINT_ON_ VARCHAR2(1)
PC_IND numeric should be printed for this item PC_IND
when a permanent price change
goes into effect.
Valid values are ‘Y’ and ‘N’. If no
value is specified in the file, the
value defaults to N.
PO_PRINT_ Alpha- 1 N When the ticket type for the PO_PRINT_ VARCHAR2(1)
TYPE numeric given item should be printed, TYPE
upon the approval or receipt of
the purchase order.
Valid values are ‘A’ and ‘R’.
ADDL_ Numeric 12,4 N Additional percentage of tickets TICKET_ NUMBER(12,4)
OVER_PCT that should be printed for a given OVER_PCT
event. For example, if the event
is receiving a purchase order, this
field holds the percentage of
tickets greater than the purchase
order quantity that should be
printed.
If no value is specified in the file,
the value defaults to the value
from the TICKET_OVER_PCT
field in the RMS
SYSTEM_OPTIONS table.

Others
DC_LOAD_ITEM_OTHER.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh. It serves two purposes:
tables.
The script can be configured to create the tables and load data, or just to load data at run
The dc_load_item_other.ksh script utilizes a common library file and configuration file.
LOAD_UDA_ITEM_LOV
This function contains a PL/SQL block that selects from the DC_UDA_ITEM_LOV
external table and inserts the data to the RMS UDA_ITEM_LOV table.
DC_UDA_ITEM_LOV to UDA_ITEM_LOV Column Defaults
LAST_UPDATE_ID Current user ID User who last updated the

record in RMS. This defaults to
the Oracle User.
LAST_UPDATE_DATETIME SYSDATE Date/time the record was last
modified in RMS. This
defaults to the system date.
CREATE_DATETIME SYSDATE Date/time the record was
created in RMS. This defaults
to the system date.
Required file to load: dc_uda_item_lov.dat

Others
LOAD_UDA_ITEM_DATE
This function contains a PL/SQL block that selects from the DC_UDA_ITEM_DATE
external table and inserts the data to the RMS UDA_ITEM_DATE table.
DC_UDA_ITEM_DATE to UDA_ITEM_DATE Column Defaults

the Oracle User.
to the system date.
Required file to load: dc_uda_item_date.dat
LOAD_UDA_ITEM_FF
This function contains a PL/SQL block that selects from the DC_UDA_ITEM_FF external
table and inserts the data to the RMS UDA_ITEM_FF table.
DC_UDA_ITEM_FF to UDA_ITEM_FF Column Defaults

the Oracle User.
to the system date.
Required file to load: dc_uda_item_ff.dat

Others
LOAD_VAT_ITEM
This function contains a PL/SQL block that selects from the DC_VAT_ITEM external
table and inserts the data to the RMS VAT_ITEM table.
DC_VAT_ITEM to VAT_ITEM Column Defaults

the Oracle User.
to the system date.
CREATE_ID Current user id User who created the record in
RMS. This defaults to the
Oracle User.
CREATE_DATE SYSDATE Date the record was created in
RMS. This defaults to the
system date.
Required file to load: dc_vat_item.dat
LOAD_ITEM_SEASONS
This function contains a PL/SQL block that selects from the DC_ITEM_SEASONS
external table and inserts the data to the RMS ITEM_SEASONS table.
DC_ITEM_SEASONS to ITEM_SEASONS Column Defaults
Column Name Default Value Comments

(RMS Table)

the Oracle User.
to the system date.
ITEM_SEASON_SEQ_NO Sequence generated Sequence is per item.
Required file to load: dc_item_seasons.dat

Others
LOAD_ITEM_TICKET
This function contains a PL/SQL block that selects from the DC_ITEM_TICKET external
table and inserts the data to the RMS ITEM_TICKET table.
DC_ITEM_TICKET to ITEM_TICKET Column Defaults

the Oracle User.
to the system date.
PRINT_ON_PC_IND N If no value is specified in the
file, the value defaults to N.
TICKET_OVER_PCT SYSTEM_OPTIONS. If no value is specified in the
TICKET_OVER_PCT file, the value defaults to the
value from the
TICKET_OVER_PCT field in
SYSTEM_OPTIONS.
Required file to load: dc_item_ticket.dat

8
Optional Data
Additional tables can be loaded for each of the functional areas handled by this data
conversion toolset. Populating these tables is optional and based on your own business
operational needs.
Note: Data conversion for these optional tables must be

performed manually. These tables must be loaded after
successful conversion of all data as described in the
preceding chapters. This is because these optional tables
have data referential integrities across functional areas.
The following sections list the optional tables for each of the functional area included in
this data conversion toolset. Tables should be loaded in the order that they are listed.
Core Tables
DIFF_RATIO_HEAD
DIFF_RATIO_DETAIL
SOURCE_DLVRY_SCHED
SOURCE_DLVRY_SCHED_DAYS
SOURCE_DLVRY_SCHED_EXC
STOP_SHIP
TRANSIT_TIMES
Merchandise Hierarchy Tables

There is no additional data to be loaded manually.
Organizational Hierarchy Tables

STORE_ATTRIBUTES
WH_ATTRIBUTES
STORE_SHIP_DATE
WH_DEPT
WH_DEPT_EXPL
WH_BLACKOUT
WH_STORE_ASSIGN

Supplier Tables
Supplier Tables
SUP_ATTRIBUTES
SUP_INV_MGMT
SUP_REPL_DAY
SUPP_PREISSUE
SUPS_MIN_FAIL
Items Tables
PACK_TMPL_HEAD
PACK_TMPL_DETAIL
ITEM_ATTRIBUTES
ITEM_SUPP_UOM
ITEM_LOC_TRAITS
SUB_ITEMS_HEAD
SUB_ITEMS_DETAIL
ITEM_FORECAST
REPL_ITEM_LOC
REPL_DAY
MASTER_REPL_ATTR

A
Appendix: Seed Data Installation
Load seed data at the time of installation. The following table outlines data installation
scripts supplied by Oracle and the tables populated by these scripts. Note that some
tables populated by these scripts may be modified for final configuration, or updated
with additional values prior to implementation.
Script Name Scripts/Packages Called Tables Inserted
RIBDATA.SQL Calls ALL_RIB_TABLE_VALUES.SQL to RIB_ERRORS

insert into these tables: RIB_LANG
RIB_TYPE_SETTINGS
RIB_SETTINGS
Calls RIB_DOCTYPES.SQL, which RIB_DOCTYPES
launches a SQL loader session to insert
into these tables:
RMSUOM.SQL UOM_CLASS
RMSCOUNTRIES.SQL COUNTRY
RMSSTATES.SQL STATE
RMSCURRENCIES.SQL CURRENCIES
STATICIN.SQL Inserts directly into these tables: SYSTEM_OPTIONS
ADD_TYPE
ADD_TYPE_MODULE
COST_CHG_REASON
DUMMY
DEAL_COMP_TYPE
DOC_LINK
DYNAMIC_HIER_CODE
INV_STATUS_TYPES
INV_STATUS_CODES
LANG
MC_REJECTION_REASONS
ORDER_TYPES
PRICE_ZONE_GROUP
SAFETY_STOCK_LOOKUP
TRAN_DATA_CODES
TRAN_DATA_CODES_REF
TSF_TYPE
VEHICLE_ROUND
COST_ZONE_GROUP
Calls ELC_COMP_PRE_HTSUPLD.SQL CVB_HEAD
to insert into these tables: ELC_COMP

Calls GENERAL_DATA_INSTALL_SQL. VAT_REGION

VAT_CODE_REGION to insert into these VAT_CODES
tables:
VAT_CODE_RATES
Calls GENERAL_DATA_INSTALL_SQL. ADD_TYPE
ADD_TYPE to insert into this table:
Calls GENERAL_DATA_INSTALL_SQL. ADD_TYPE_MODULE
ADD_TYPE_MODULE to insert into this
table:
Calls GENERAL_DATA_INSTALL. UNIT_OPTIONS
UNIT_OPTIONS to insert into this table:
Calls GENERAL_DATA_INSTALL_SQL. CVB_HEAD
ELC_COMP_EXPENSES to insert into this CVB_DETAIL
table:
Calls GENERAL_DATA_INSTALL_SQL. ELC_COMP
ELC_COMP_EXPENSES,
GENERAL_DATA_INSTALL_SQL.
UP_CHARGE and
GENERAL_DATA_INSTALL_SQL.
BACKHAUL_ALLOWANCE to insert
into this table:
ADD_FILTER_POLICY.SQL Calls the DBMS_RLS.ADD_POLICY

function to implement fine-grained access
control.
NAVIGATE.SQL NAV_ELEMENT
NAV_SERVER
NAV_COMPONENT
EVIEW
NAV_ICON
NAVROLE.SQL NAV_ELEMENT_MODE_ROLE
CODES.SQL CODE_HEAD
CODE_DETAIL
CODE_DETAIL_TRANS
POPULATE_SEC_FORM_ SEC_FORM_ACTION
ACTION.SQL
RESTART.SQL RESTART_PROGRAM_STATUS
RESTART_CONTROL+C11
RTK_ERRORS.SQL RTK_ERRORS
RTK_REPORTS.SQL RTK_REPORTS
TL_COLUMNS.SQL TL_COLUMNS
WIZARD.SQL WIZARD_TEXT
CONTEXT.SQL CONTEXT_HELP
POPULATE_FORM_ FORM_LINKS
LINKS.SQL

POPULATE_FORM_ FORM_LINKS_ROLE
LINKS_ROLE.SQL
UOM_X_CONVERSION.SQL UOM_X_CONVERSION
VAR_UPC_EAN_LOAD.SQL VAR_UPC_EAN
MULTIVIEW_DATA.SQL MULTIVIEW_SAVED_45
MULTIVIEW_DEFAULT_45
RMSUOMCONV1.SQL UOM_CONVERSION
RMSUOMCONV2.SQL UOM_CONVERSION
CALENDAR.SQL HALF
CALENDAR
SYSTEM_VARIABLES
PERIOD
SA_SYSTEM_REQUIRED.SQL Calls SA_METADATA.SQL to insert into POS_TENDER_TYPE_HEAD
these tables: SA_CC_VAL
SA_REFERENCE
SA_ERROR_CODES
SA_EXPORT_OPTIONS
SA_ERROR_IMPACT
Calls SA_METADATA.SQL, which in SA_REALM_TYPE
turn calls SA_REALM_TYPE.SQL to insert
into this table:
Calls SA_METADATA.SQL, which in SA_REALM
turn calls SA_REALM.SQL to insert into
this table:
Calls SA_METADATA.SQL, which in SA_PARM_TYPE
turn calls SA_PARM_TYPE.SQL to insert
into this table:
Calls SA_METADATA.SQL, which in SA_PARM
turn calls SA_PARM.SQL to insert into
this table:
RMS12RTM.SQL Calls ENTRY_TYPE.SQL to insert into this ENTRY_TYPE
table:
Calls ENTRY_STATUS.SQL to insert into ENTRY_STATUS
this table:
Calls OGA.SQL to insert into this table: OGA
Calls TARIFF_TREATMENT.SQL to insert TARIFF_TREATMENT
into this table:
QUOTA_CATEGORY.SQL QUOTA_CATEGORY
Calls COUNTRY_TARIFF_TREATMENT. COUNTRY_TARIFF_TREATMENT
SQL to insert into this table:
Calls HTS_HEADINGS.SQL to insert into HTS_CHAPTER
this table:

BASE_FORM_MENU_ Calls all the XML.SQL scripts for each FORM_ELEMENTS

ELEMENTS.SQL form present in the application to insert FORM_ELEMENTS_TEMP
into these tables:
FORM_ELEMENTS_LANGS_TEMP
FORM_MENU_LINK
MENU_ELEMENTS
MENU_ELEMENTS_TEMP
MENU_ELEMENTS_LANGS_TEMP

Merch 1208 Dcog

Uploaded by

Copyright:

Available Formats

Merch 1208 Dcog

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Merch 1208 Dcog

Uploaded by

Copyright:

Available Formats

Oracle® Retail Merchandising

Data Conversion Operations Guide

Copyright © 2008, Oracle. All rights reserved.

Note: This is a note. It is used to call out information that is

A hyperlink appears like this.

Data Conversion Process

Data Conversion Approach

Data Conversion Operations Guide 1

The following scripts are included in this data conversion toolset:

Note: Before you begin using the data conversion toolset,

Prerequisites and Assumptions

How to Use This Guide

2 Oracle Retail Merchandising

Note: Data conversion must be performed in order by

The description of each program includes the following information:

Data Conversion Operations Guide 3

Note: Although the master script allows you to run all

Configuration File Definition (DC_LOAD.CFG)

Note: Before you begin using the data conversion toolset,

Data Conversion Operations Guide 5

Directory Name Description

Other directories can be created as needed.

6 Oracle Retail Merchandising

connectStr Oracle username/password information to connect to the database

Other variables are used as needed.

Sequence File Definition

Data Conversion Operations Guide 7

Note: The sequence file should terminate with a new line.

Library File Description (DC_LOAD.LIB)

8 Oracle Retail Merchandising

Master Script Technical Flow

Data Conversion Operations Guide 9

Running KSH Scripts

10 Oracle Retail Merchandising

Data Conversion Operations Guide 11

Note: It is assumed that you have already installed the

Table Required / Optional / Comments

12 Oracle Retail Merchandising

Table Required / Optional / Comments

File Format and External Oracle Tables

Note: Data files must be in UNIX file format and encoded as

External Oracle Table Definition

Data Conversion Operations Guide 13

File Format External Oracle Table Definition

CUST_ID Alpha- 10 Y Code that uniquely CUST_ID VARCHAR2(10)

14 Oracle Retail Merchandising

File Format External Oracle Table Definition

TERMS Alpha- 15 Y Unique identifier of TERMS VARCHAR2(15)

Data Conversion Operations Guide 15

File Format External Oracle Table Definition

TERMS Alpha- 15 Y Unique identifier of supplier TERMS VARCHAR2(15)

16 Oracle Retail Merchandising

File Format External Oracle Table Definition

ENABLED_ Alpha- 1 Y Whether payment terms are ENABLED_FLAG VARCHAR2(1)

Data Conversion Operations Guide 17

File Format External Oracle Table Definition

FREIGHT_ Alpha- 6 Y Unique identifier of the FREIGHT_TYPE VARCHAR2(6)

File Format External Oracle Table Definition

FREIGHT_ Alpha- 30 Y Unique identifier of freight FREIGHT_ VARCHAR2(30)