Chapter	Description
	Master Script	This chapter describes the master script, the main tool used 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.
1. 2. 3.	Core Merchandise Hierarchy Organizational Hierarchy Suppliers Items Multiple Sets of Books (MSOB)	These chapters describe in detail all the programs and files required to load data for each of the functional areas. Each chapter also contains a Prerequisites section that lists all tasks that must be completed prior to running the tools for that functional area. Some chapters also have a Post-Loading Requirements section that describes tasks that must be done before data conversion is considered complete for that functional area.
	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.
Appendix	Seed Data Installation	This appendix describes the scripts used to load seed data at the time of installation.

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 directory	dataCompDir	This directory contains processed data files.
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.

Variable	Description
connectStr	Oracle Wallet alias to be provided 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.

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.
BRPGM	Used to run Brazil specific ksh scripts or other executable file. If no path is included with the script name, it assumes 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.

Core

This chapter describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ 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

§ TSF_ENTITY

§ FIF_GL_SETUP

§ ORG_UNIT

§ BRAND

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_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_create_diffids_tab.sql

§ dbc_create_tsf_entity_tab.sql

§ dbc_create_fif_gl_setup_tab.sql

§ dbc_create_org_unit_tab.sql

§ dbc_create_brand_tab.sql

Data Flow

The following diagram shows the data flow for the Core functional area:

Data Flow for Core Functional Area

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. For more information, see Appendix: Seed Data Installation.

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
BANNER	Required when multi-channel is turned on
CHANNELS	Required
SEASONS	Optional
PHASES	Optional
DIFF_TYPE	Required
TSFZONE	Required
STORE_FORMAT	Required
BUYER	Optional
MERCHANT	Optional
CVB_HEAD	Optional
CVB_DETAIL	Optional
ELC_COMP	Required only if upcharges are loaded
STATE	Required only if using addresses in U.S. locations

File Format and External Oracle Tables

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 be 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.

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
TERMS	Alpha-numeric	15	Y	Unique identifier of supplier payment terms record.	TERMS	VARCHAR2(15)
TERMS_CODE	Alpha-numeric	50	Y	Code that represents the supplier payment terms in Oracle Financials.	TERMS_CODE	VARCHAR2(50)
TERMS_DESC	Alpha-numeric	240	Y	Description of the supplier payment terms. For example, 2.5% 30 Days.	TERMS_DESC	VARCHAR2(240)
RANK	Alpha-numeric	10	Y	Rank to rate invoice payment terms against purchase order terms. These rankings are defined in the retailer’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.	RANK	NUMBER(10)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
TERMS	Alpha-numeric	15	Y	Unique identifier of supplier payment terms record. This ties the detail record to the appropriate dc_terms_head record.	TERMS	VARCHAR2(15)
TERMS_SEQ	Numeric	10	Y	Order sequence in which to apply the discount percent.	TERMS_SEQ	NUMBER(10)
DUEDAYS	Numeric	3	Y	Number of days until payment is due.	DUEDAYS	NUMBER(3,)
DUE_MAX_AMOUNT	Numeric	12,4	Y	Maximum payment amount due by a given date.	DUE_MAX_AMOUNT	NUMBER(12,4)
DUE_DOM	Numeric	2	Y	Day of month used to calculate due date of invoice payment line. For example, 1 represents the 1st day of the month.	DUE_DOM	NUMBER(2)
DUE_MM_FWD	Numeric	3	Y	Number of months ahead used to calculate due date of invoice payment line.	DUE_MM_FWD	NUMBER(3)
DISCDAYS	Numeric	3	Y	Number of days in which payment must be made in order to receive the discount.	DISCDAYS	NUMBER(3)
PERCENT	Numeric	12,4	Y	Percent of discount if payment is made within specified time period.	PERCENT	NUMBER(12,4)
DISC_DOM	Numeric	2	Y	Day of month used to calculate discount date of invoice payment line. For example, 1 represents the 1st day of the month.	DISC_DOM	NUMBER(2)
DISC_MM_FWD	Numeric	3	Y	Number of months ahead used to calculate discount date of invoice payment line.	DISC_MM_FWD	NUMBER(3)
ENABLED_FLAG	Alpha-numeric	1	Y	Indicates whether payment terms are valid or invalid within the application.	ENABLED_FLAG	VARCHAR2(1)
CUTOFF_DAY	Numeric	2	Y	Day of the month after which Oracle Payables schedules payment using the day after the current month.	CUTOFF_DAY	NUMBER(2)
FIXED_DATE	Alpha-numeric	7	N	Fixed due date.	FIXED_DATE	DATE
START_DATE_ACTIVE	Alpha-numeric	7	N	Date in which the payment terms become active.	START_DATE_ACTIVE	DATE
END_DATE_ACTIVE	Alpha-numeric	7	N	Date in which the payment terms become inactive.	END_DATE_ACTIVE	DATE

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req Ind	Description	Field Name	Data Type
Freight_Method	Alpha-numeric	6	Y	Unique identifier of the freight method.	FREIGHT_TYPE	VARCHAR2(6)
Freight_Method_Desc	Alpha-numeric	250	Y	Description of the freight method. Examples are Full Container Load and Less than Container Load.	FREIGHT_TYPE_DESC	VARCHAR2(250)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
FREIGHT_TERMS	Alpha-numeric	30	Y	Unique identifier of freight terms record.	FREIGHT_TERMS	VARCHAR2(30)
TERM_DESC	Alpha-numeric	240	Y	Description of the freight terms. Examples include a percentage of total cost or a flat fee.	TERM_DESC	VARCHAR2(240)
START_DATE_ACTIVE	Alpha-numeric	9	N	Date on which the freight terms become active. Date format is DDMONYYYY (for example, 02JAN2011).	START_DATE_ACTIVE	DATE
END_DATE_ACTIVE	Alpha-numeric	9	N	Date on which the freight terms become inactive. Date format is DDMONYYYY.	END_DATE_ACTIVE	DATE
ACTIVE_FLAG	Alpha-numeric	1	Y	Indicates whether freight terms are valid or invalid within the application. Default = N.	ENABLED_FLAG	VARCHAR2(1)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
FREIGHT_SIZE	Alpha-numeric	6	Y	Unique identifier of the freight size record.	FREIGHT_SIZE	VARCHAR2(6)
FREIGHT_SIZE_DESC	Alpha-numeric	250	Y	Freight size description (for example, 40 foot container).	FREIGHT_SIZE_DESC	VARCHAR2(250)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

§ VAT-related tables are only inserted if vat_ind on system_options is Y and default_tax_type is not GTAX (SVAT is used).

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
VAT_CODE	Alpha-numeric	6	Y	Unique identifier of value added tax code, used to determine which items are subject to VAT. For example, the Valid values includes: S – Standard Z – Zero	VAT_CODE	VARCHAR2(6)
VAT_CODE_DESC	Alpha-numeric	120	Y	Value added tax code description.	VAT_CODE_DESC	VARCHAR2(120)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

§ VAT-related tables are only inserted if vat_ind on system_options is Y and default_tax_type is not GTAX (SVAT is used).

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
VAT_CODE	Alpha-numeric	6	Y	Unique identifier of value added tax code. This ties the record to the appropriate dc_vat_codes record.	VAT_CODE	VARCHAR2(6)
ACTIVE_DATE	Alpha-numeric	9	Y	Date on which VAT rate becomes active. Date format is DDMONYYYY (for example, 02JAN2011).	ACTIVE_DATE	DATE
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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

§ VAT-related tables are only inserted if vat_ind on system_options is Y and default_tax_type is not GTAX (SVAT is used).

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
VAT_REGION	Numeric	4	Y	Unique identifier of VAT region. VAT region is determined by the VAT authority.	VAT_REGION	NUMBER(4)
VAT_REGION_NAME	Alpha-numeric	120	Y	Name/description of the VAT region.	VAT_REGION_NAME	VARCHAR2(120)
VAT_REGION_TYPE	Alpha-numeric	6	Y	VAT region type. Valid values include E for base EU members, M for EU members, and N for non members of the EU.	VAT_REGION_TYPE	VARCHAR2(6)
ACQUISITION_VAT_IND	Alpha-numeric	1	Y	Indicates if acquisition VAT is applicable to the VAT region. Valid values are Y and N.	ACQUISITION_VAT_IND	VARCHAR2(1)
REVERSE_VAT_THRESHOLD	Numeric	20, 4	N	This holds the invoice-level total value limit. The reverse charge VAT rule only applies if the total value of items are subject to reverse charge VAT exceeds the threshold for an invoice. This value is expressed in the country currency of the vat_region, which typically only belongs to one country.	REVERSE_VAT_THRESHOLD	NUMBER(20, 4)

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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
UDA_ID	Numeric	5	Y	Unique identifier of user-defined attribute.	UDA_ID	NUMBER(5)
UDA_DESC	Alpha-numeric	120	Y	Description of user-defined attribute. UDAs do not have specific processing within RMS.	UDA_DESC	VARCHAR2(120)
DISPLAY_TYPE	Alpha-numeric	2	Y	How the UDA displays 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.	DISPLAY_TYPE	VARCHAR2(2)
DATA_TYPE	Alpha-numeric	12	N	Data type associated with the 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.	DATA_TYPE	VARCHAR2(12)
DATA_LENGTH	Numeric	3	N	Maximum length of the UDA 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.	DATA_LENGTH	VARCHAR2(3)
SINGLE_VALUE_IND	Alpha-numeric	1	Y	Indicates whether this UDA can have only one value associated with an item. If Y, only one value of the UDA can be associated with an item.	SINGLE_VALUE_IND	VARCHAR2(1)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
UDA_ID	Numeric	5	Y	Unique identifier of user-defined attribute. This applies only to UDAs with LV display type. This ties the record to the appropriate dc_uda record.	UDA_ID	NUMBER(5)
UDA_VALUE_DESC	Alpha-numeric	250	Y	Description of the UDA value.	UDA_VALUE_DESC	VARCHAR2(250)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
TICKET_TYPE_ID	Alpha-numeric	4	Y	Unique identifier of ticket or label type.	TICKET_TYPE_ID	VARCHAR2(4)
TICKET_TYPE_DESC	Alpha-numeric	120	Y	Description of ticket or label type.	TICKET_TYPE_DESC	VARCHAR2(120)
SHELF_EDGE_IND	Alpha-numeric	1	Y	Indicates whether this is a shelf edge label. Default = N.	SEL_IND	VARCHAR2(1)

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

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
TICKET_TYPE_ID	Alpha-numeric	4	Y	Unique identifier of ticket or label type. This ties the record to the appropriate dc_ticket_ type_ head record.	TICKET_TYPE_ID	VARCHAR2(4)
TICKET_ITEM_ID	Alpha-numeric	4	N	Identifier of type of 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.	TICKET_ITEM_ID	VARCHAR2(4)
UDA_ID	Numeric	5	N	If the information to be displayed 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.	UDA_ID	NUMBER(5)

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

File name: DC_DIFF_IDS.DAT

Table create SQL script: DBC_CREATE_DIFF_IDS_TAB.SQL

External Oracle table created: DC_DIFF_IDS

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
DIFF_ID	Alpha-numeric	10	Y	Unique identifier of the differentiator.	DIFF_ID	VARCHAR2(10)
DIFF_TYPE	Alpha-numeric	6	Y	Differentiator group 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_TYPE	VARCHAR2(6)
DIFF_DESC	Alpha-numeric	120	Y	Description of the differentiator. Examples are Red, Stripe, and Strawberry.	DIFF_DESC	VARCHAR2(120)
INDUSTRY_CODE	Alpha-numeric	10	N	Unique number that represents all possible combinations of sizes, according to the National Retail Federation. Optional.	INDUSTRY_ CODE	VARCHAR2(10)
INDUSTRY_SUBGROUP	Alpha-numeric	10	N	Unique number that represents all different color range groups, according to the National Retail Federation. Optional.	INDUSTRY_SUBGROUP	VARCHAR2(10)

TSF Entities - DC_TSF_ENTITY Table

File name: DC_TSF_ENTITY.DAT

Table create SQL script: DBC_CREATE_TSF_ENTITY_TAB.SQL

External Oracle table created: DC_TSF_ENTITY

Suggested post-loading validation (sequence after dc_load_core.ksh): Ensure that TSF_ENTITY.TSF_ENTITY_ID is unique.

FILE FORMAT						EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type		Max Length	Req. Ind	Description	Field Name	Data Type
TSF_ENTITY_ID		Numeric	10	Y	Unique identifier of the transfer entity.	TSF_ENTITY_ID	NUMBER(10)
TSF_ENTITY_DESC		Alpha-numeric	120	Y	Description of the transfer entity.	TSF_ENTITY_DESC	VARCHAR2(120)

Set of Books - DC_TSF_FIF_GL_SETUP Table

File name: DC_TSF_FIF_GL_SETUP.DAT

Table create SQL script: DBC_CREATE_FIF_GL_SETUP_TAB.SQL

External Oracle table created: DC_FIF_GL_SETUP

Suggested post-loading validation (sequence after dc_load_core.ksh): Ensure that FIF_GL_SETUP.SET_OF_BOOKS_ID is unique.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
SET_OF_BOOKS_ID	Numeric	15	Y	Unique identifier for the set of books.	SET_OF_BOOKS_ID	NUMBER(10)
LAST_UPDATE_ID	Numeric	15	Y	Last updated ID for transactions.	SET_OF_BOOKS_ID	VARCHAR2(120)
SEQUENCE1_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE1_DESC	VARCHAR2(20)
SEQUENCE2_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE2_DESC	VARCHAR2(20)
SEQUENCE3_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE3_DESC	VARCHAR2(20)
SEQUENCE4_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE4_DESC	VARCHAR2(20)
SEQUENCE5_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE5_DESC	VARCHAR2(20)
SEQUENCE6_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE6_DESC	VARCHAR2(20)
SEQUENCE7_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE7_DESC	VARCHAR2(20)
SEQUENCE8_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE8_DESC	VARCHAR2(20)
SEQUENCE9_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE9_DESC	VARCHAR2(20)
SEQUENCE10_DESC	Alpha-numeric	20	N	Contains description for sequence columns on the interface cross-reference form.	SEQUENCE10_DESC	VARCHAR2(20)
CATEGORY_ID	Numeric	38	Y	Oracle category ID. Default for purchase order feed.	CATEGORY_ID	NUMBER(38)
DELIVER_TO_LOCATION_ID	Numeric	15	Y	Oracle location ID. Default for purchase order feed.	DELIVER_TO_LOCATION_ID	NUMBER(15)
DESTINATION_ORGANIZATION_ID	Numeric	38	Y	Oracle Organization ID. Default for purchase order feed.	DESTINATION_ORGANIZATION_ID	NUMBER(38)
PERIOD_NAME	Alpha-numeric	15	N	User entered accounting period name as defined in financial applications.	PERIOD_NAME	VARCHAR2(15)
SET_OF_BOOKS_DESC	Alpha-numeric	120	Y	Set of books description.	SET_OF_BOOKS_ DESC	VARCHAR2(120)
CURRENCY_CODE	Alpha-numeric	3	Y	Currency code for the Set of Books ID.	CURRENCY_CODE	VARCHAR2(3)

Organization Unit - DC_ORG_UNIT Table

File name: DC_ORG_UNIT.DAT

Table create SQL script: DBC_CREATE_ORG_UNIT_TAB.SQL

External Oracle table created: DC_FIF_GL_SETUP

ORG_UNIT

Suggested post-loading validation (sequence after dc_load_core.ksh): Ensure that ORG.UNIT.ORG_UNIT_ID is unique.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
ORG_UNIT_ID	Numeric	10	Y	Unique identifier for the Organization ID.	ORG_UNIT_ID	NUMBER(15)
DESCRIPTION	Alpha-numeric	120	Y	Description of the organization unit.	DESCRIPTION	VARCHAR2(120)
SET_OF_BOOKS_ID	Numeric	15	N	Set of books ID.	SET_OF_BOOKS_ID	NUMBER(15)

Brand – DC_BRAND Table

File name: DC_BRAND.DAT

Table create SQL script: DBC_CREATE_BRAND_TAB.SQL

External Oracle table created: DC_BRAND

Suggested post-loading validation (sequence after dc_load_core.ksh):

§ Capture the count from BRAND and compare to flat file DC_BRAND.DAT to ensure that all rows are loaded.

§ DC_BRAND.DAT and DBC_CREATE_BRAND_TAB.SQL: The SQL script is called by the DC_LOAD_CORE.KSH and creates the DC_BRAND oracle external table.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
BRAND_NAME	Alpha-numeric	30	Y	Brand Name.	BRAND_NAME	VARCHAR2(30)
BRAND_DESCRIPTION	Alpha-numeric	120	Y	Brand Description.	BRAND_DESCRIPTION	VARCHAR2(120)

Segment Wrapper / Load Script (DC_LOAD_CORE.KSH)

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create the external Oracle tables.

§ 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_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 and TERMS_HEAD_TL 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_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.

The function returns a Boolean value.

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 and FREIGHT_TYPE_TL table. All the columns from the external Oracle table defined previously map directly to the RMS table. All are required.

The function returns a Boolean value.

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 and FREIGHT_TERMS_TL table. All the columns from the external Oracle table defined previously map directly to the RMS table.

The function returns a Boolean value.

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 and FREIGHT_SIZE_TL table. All the columns from the external Oracle table defined previously map directly to the RMS table. All are required.

The function returns a Boolean value.

Required file to load: dc_freight_size.dat

LOAD_VAT_CODES

This function contains a PL/SQL block that selects from the DC_VAT_CODES external table and inserts the data to the RMS VAT_CODES table if vat_ind on system_options is Y and default_tax_type is not GTAX (SVAT is used). All the columns from the external oracle table defined above will directly map to the RMS table and are required.

The function returns a Boolean value.

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 if vat_ind on system_options is ‘Y’ and default_tax_type is not GTAX (SVAT is used). All the columns from the external oracle table defined above will directly map to the RMS table. The table below defines the default value in the RMS tables if no information is provided in the data file (external table field value is NULL or not defined).

The function returns a Boolean value.

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 if vat_ind on system_options is Y and default_tax_type is not GTAX (SVAT is used). All the columns from the external oracle table defined above will directly map to the RMS table and all are required.

The function returns a Boolean value.

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

Field Name (RMS Table)	Default Value	Comments
MODULE	ITEM	Functional area of RMS to which this applies. The only valid value is ITEM.
DATA_TYPE	ALPHA (FF/LV) DATE (DT) NUM(LV)	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.

Field Name (RMS Table)

Default Value

Comments

MODULE

ITEM

Functional area of RMS to which this applies. The only valid value is ITEM.

DATA_TYPE

ALPHA (FF/LV*)

DATE (DT)

NUM(LV*)

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.

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 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_VALUES to UDA_VALUES Column Defaults

Field Name (RMS Table)	Default Value	Comments
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.

The function returns a Boolean value.

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.

The function returns a Boolean value.

DC_TICKET_TYPE_DETAIL to TICKET_TYPE_DETAIL Column Defaults

Field Name (RMS Table)	Default Value	Comments
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.

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.

The function returns a Boolean value.

DC_diff_ids to diff_ids Column Defaults

Field Name (RMS Table)	Default Value	Comments
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

LOAD_TSF_ENTITY

This function contains a PL/SQL block that selects from the DC_TSF_ENTITY external table and inserts the data to the RMS TSF_ENTITY table. All the columns from the external Oracle table defined previously map directly to the RMS table. All fields are required.

The function returns a Boolean value.

Required file to load: dc_tsf_entity.dat

LOAD_FIF_GL_SETUP

This function contains a PL/SQL block that selects from the DC_FIF_GL_SETUP external table and inserts the data to the RMS FIF_GL_SETUP table. All the columns from the external Oracle table defined previously map directly to the RMS table. SET_OF_BOOKS_ID.LAST_UPDATE_ID, CATEGORY_ID, DELIVER_TO_LOCATION_ID, DESTINATION_ORGANIZATION_ID, SET_OF_BOOKS_ID, and CURRENCY_CODE are required fields.

The function returns a Boolean value.

Required file to load: dc_fif_gl_setup.dat

LOAD_ORG_UNIT

This function contains a PL/SQL block that selects from the DC_ORG_UNIT external table and inserts the data to the RMS ORG_UNIT table. All the columns from the external Oracle table defined previously map directly to the RMS table. All fields are required.

The function returns a Boolean value.

Required file to load: dc_org_unit.dat

LOAD_BRAND_ITEM

This function contains a PL/SQL block that selects from the DC_BRAND external table and inserts the data to the RMS BRAND table. All the columns from the external Oracle table defined previously map directly to the RMS table. All fields are required.

This function returns a Boolean value.

Required file to load: dc_brand.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

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
MERCH_HIER_4	Integer	4	Y	Unique identifier of the merchandise hierarchy level 4.	DEPT	NUMBER(4)
MERCH_HIER_4_DESC	Alpha-numeric	120	Y	Description of the merchandise hierarchy level 4.	DEPT_NAME	VARCHAR2(120)
BUYER	Integer	4	N	Buyer for this merchandise hierarchy level 4. Valid values are from the BUYER field in the BUYER table in RMS.	BUYER	NUMBER(4)
MERCHANDISER	Integer	4	N	Merchandiser for this merchandise hierarchy level 4. Valid values are from the MERCH column in the MERCHANT table in RMS.	MERCH	NUMBER(4)
PROFIT_CALC_TYPE	Integer	1	N	Method of accounting for the stock ledger. Valid values are: 1 - Direct cost 2 - Retail inventory If no value is specified in the flat file, the default value is taken from the MERCH_HIER_4_ PROFIT_CALC_TYPE field in the DC_MERCH_ DEFAULTS file.	PROFIT_CALC_TYPE	NUMBER(1)
MERCHANDISE_TYPE	Integer	1	N	Type of merchandise in this merchandise hierarchy level 4. Valid values are: 0 - Normal merchandise 1 - Consignment stock 2 - Concession items If no value is specified in the flat file, the default value is taken from the MERCH_HIER_4_PURCHASE_TYPE field in the DC_MERCH_DEFAULTS file.	PURCHASE_TYPE	NUMBER(1)
MERCH_HIER_3	Integer	4	Y	Identifier of the merchandise hierarchy level 3 of which merchandise hierarchy level 4 is a member. Valid values are in the GROUP_NO field in the GROUPS table in RMS.	GROUP_NO	NUMBER(4)
MERCH_HIER_4_MRKUP_PCT	Numeric	12,4	N	Budgeted intake or markup 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. 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.	MRKUP_PCT	NUMBER(12,4)
TOTAL_MARKET_AMT	Numeric	24,4	N	Total market amount expected for this merchandise hierarchy level 4. Optional.	TOTAL_MARKET_AMT	NUMBER(24,4)
MARKUP_CALC_TYPE	Alpha-numeric	2	N	Indicates how markup is calculated for this merchandise hierarchy level 4. Valid values are: C - Cost R - Retail If no value is specified in the flat file, the default value is taken from the MERCH_HIER_4_ MARKUP_CALC_TYPE field in the DC_MERCH_DEFAULTS file.	MARKUP_CALC_TYPE	VARCHAR2(2)
OTB_CALC_TYPE	Alpha-numeric	1	N	Indicates how open to buy is calculated for this merchandise hierarchy level 4. Valid values are: C - Cost R - Retail If no value is specified in the flat file, the default value is taken from the MERCH_HIER_4_OTB_CALC_TYPE field in the DC_MERCH_DEFAULTS file.	OTB_CALC_TYPE	VARCHAR2(1)
MAX_AVG_COUNTER	Integer	5	N	Maximum count of days with acceptable data to include in projected sales for items within the merchandise hierarchy 4. This provides a way to weigh 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.	MAX_AVGCOUNTER	NUMBER(5)
AVG_TOLERANCE_PCT	Numeric	12,4	N	Tolerance percentage used in averaging sales for items. Used to determine if a sales 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.	AVG_TOLERANCE_PCT	NUMBER(12,4)
VAT_IN_RETAIL	Alpha-numeric	1	N	Indicates whether retail is held with or without VAT. If VAT is not turned on in RMS, then this value should be N. If no value is specified in the flat file, the default value is taken from the MERCH_ HIER_4_VAT_IN_RETAIL field in the DC_MERCH_ DEFAULTS file.	DEPT_VAT_INCL_IND	VARCHAR2(1)
LOWEST_STRATEGY_LEVEL	Integer	6	N	Lowest level at which a strategy can be defined. Valid values are: 0 - Merchandise hierarchy 4 1 - Merchandise hierarchy 5 2 - Merchandise hierarchy 6 If no value is specified in the flat file, the default value is 0.	LOWEST_STRATEGY_LEVEL	NUMBER(6)
WORKSHEET_LEVEL	Integer	6	N	Indicates the merchandise hierarchy level used to build worksheets for pricing strategies in RPM. This value should be at or above the value in the LOWEST_STRATEGY_ LEVEL. Valid values are: 0 - Merchandise hierarchy 4 1 - Merchandise hierarchy 5 2 - Merchandise hierarchy 6 If no value is specified in the flat file, the default value is 0.	WORKSHEET_LEVEL	NUMBER(6)
HISTORICAL_SALES_PERIOD	Integer	6		Indicates the period used by the merchandise extract to RPM when extracting historical sales.Valid values are: 0 – Week 1 – Month 2 - Half year 3 – Year If no value is specified in the flat file, the default value is 0.	HISTORICAL_SALES_LEVEL	NUMBER(6)
REGULAR_SALES_IND	Integer	6	N	Indicates whether regular price sales are included as part of the historical sales extracted by the merchandise extract to RPM. Valid values are: 0 - Do not include 1 – Include If no value is specified in the flat file, the default value is 0.	REGULAR_SALES_IND	NUMBER(6)
CLEARANCE_SALES_IND	Integer	6	N	Indicates whether clearance price sales are included as part of the historical sales extracted by the merchandise extract to RPM. Valid values are: 0 - Do not include 1 – Include If no value is specified in the flat file, the default value is 0.	CLEARANCE_SALES_IND	NUMBER(6)
PROMOTIONAL_SALES_IND	Integer	6	N	Indicates whether promotional price sales are included as part of the historical sales extracted by the merchandise extract to RPM. Valid values are: 0 - Do not include 1 – Include If no value is specified in the flat file, the default value is 0.	PROMOTIONAL_SALES_IND	NUMBER(6)
INCLUDE_WH_ON_HAND	Integer	6	N	Indicator used by the merchandise extract to RPM to determine whether to include the warehouse on hand quantity when calculating sell thru and price change impact. If no value is specified in the flat file, the default value is 0.	INCLUDE_WH_ON_HAND	NUMBER(6)
INCLUDE_WH_ON_ORDER	Integer	6	N	Indicator used by the merchandise extract to RPM to determine whether to include the warehouse on order quantity when calculating the total on order quantity. If no value is specified in the flat file, the default value is 0.	INCLUDE_WH_ON_ORDER	NUMBER(6)
PRICE_CHANGE_AMOUNT_CALC_TYPE	Integer	6	N	Calculation method for the price change amount column on the Worksheet and Worksheet status screens. Valid values are: 0 - Current-New 1 - New-Current If no value is specified in the flat file, the default value is 0.	PRICE_CHANGE_AMOUNT_CALC_TYPE	NUMBER(6)
RETAIL_CHG_HIGHLIGHT_DAYS	Integer	4	N	Defines a window of recent price changes. The worksheet will highlight past price changes that fall within this window of time.	RETAIL_CHG_HIGHLIGHT_DAYS	NUMBER(4)
COST_CHG_HIGHLIGHT_DAYS	Integer	4	N	Defines a window of recent cost changes. The worksheet highlights past cost changes that fall within this window of time.	COST_CHG_HIGHLIGHT_DAYS	NUMBER(4)
PEND_COST_CHG_WINDOW_DAYS	Integer	4	N	Indicates how many days forward the worksheet looks to find upcoming cost changes.	PEND_COST_CHG_WINDOW_DAYS	NUMBER(4)
PEND_COST_CHG_HIGHLIGHT_DAYS	Integer	4	N	Defines a window of upcoming cost changes. The worksheet highlights upcoming cost changes that fall within this window of time.	PEND_COST_CHG_HIGHLIGHT_DAYS	NUMBER(4)

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
Default_merch_heir_5	Alpha-numeric	1	Y	Indicates whether to default the merchandise hierarchy levels 5 and 6 (RMS class and subclass) records based on each merchandise hierarchy level 4 (RMS department). Valid values are Y and N.	DEFAULT_CLASS	VARCHAR2(1)
Default_ merch_heir_6	Alpha-numeric	1	Y	Indicates whether to default the merchandise hierarchy level 6 (RMS subclass) records. If DEFAULT_MERCH_HIER_5 (RMS class) is Yes, then it is assumed that MERCH_HIER_6 values are defaulted as well. Valid values are Y and N.	DEFAULT_SUBCLASS	VARCHAR2(1)
Merch_heir_4_profit_calc_type	Integer	1	Y	Default value to be used for all MERCH_HIER_4 records that do not have a profit calc type value specified. Valid values are: 1 - Direct cost 2 - Retail inventory	DEPT_PROFIT_CALC_TYPE	NUMBER
Merch_heir_4_purchase_type	Integer	1	N	Purchase type default value for MERCH_HIER_4 records that do not have a merchandise type value specified. Valid values are: 0 - Normal merchandise 1 - Consignment stock 2 - Concession items	DEPT_PURCHASE_TYPE	NUMBER
Merch_heir_4_MRKUP_PCT	Integer	12,4	Y	Indicates whether the field specifies the intake or markup is determined by the value of the DC_DEPS. 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.	DEPT_MRKUP_PCT	NUMBER
Merch_heir_4_markup_calc_type	Alpha-numeric	2	Y	Indicates whether the markup calculation type should be Cost or Retail for MERCH_HIER_4 records. Valid values are: C – Cost R – Retail	DEPT_MARKUP_CALC_TYPE	VARCHAR2
Merch_heir_4_otb_calc_type	Alpha-numeric	1	Y	Indicates whether the open to buy (OTB) calculation type should be Cost or Retail for MERCH_HIER_4 records. Valid values are: C – Cost R – Retail	DEPT_OTB_CALC_TYPE	VARCHAR2
Merch_hier_4_VAT_IN_RETAIL	Alpha-numeric	1	Y	Indicates whether retail is held with VAT in the MERCH_HIER_4 level. If VAT is not turned on in RMS, this value should be N.	DEPT_VAT_INCL_IND	VARCHAR2(1)
Merch_hier_5_VAT_IN_RETAIL	Alpha-numeric	1	Y	Indicates whether retail is held with VAT in the MERCH_HIER_5 level. If VAT is not turned on in RMS, this value should be N.	CLASS_VAT_INCL_IND	VARCHAR2(1)

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
MERCH_HIER_4	Integer	4	Y	Identifier of the merchandise hierarchy level 4 of which merchandise hierarchy level 5 is a member. Valid values are in the DEPT field in the DEPS table.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	Y	Unique identifier of the merchandise hierarchy level 5.	CLASS	NUMBER(4)
MERCH_HIER_5_NAME	Alpha-numeric	120	Y	Description of the merchandise hierarchy level 5.	CLASS_NAME	VARCHAR2(120)
VAT_IN_RETAIL	Alpha-numeric	1	N	Indicates whether retail is held with VAT. If VAT is not turned on in RMS, this value should be N. 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.	CLASS_VAT_IND	VARCHAR2(1)

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
MERCH_HIER_4	Integer	4	Y	Identifier of the merchandise hierarchy level 4 of which merchandise hierarchy level 6 is a member. Valid values are in the DEPT field in the DEPS table.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	Y	Identifier of the merchandise hierarchy level 5 of which merchandise hierarchy level 6 is a member. Valid values are in the CLASS field in the CLASS table.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	Y	Unique identifier of the merchandise hierarchy level 6.	SUBCLASS	NUMBER(4)
MERCH_HIER_6_NAME	Alpha-numeric	120	Y	Description of the merchandise hierarchy level 6.	SUBCLASS_NAME	VARCHAR2(120)

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
Merch_heir_4	Integer	4	Y	Unique identifier of the merch hierarchy level 4.	DEPT	NUMBER(4)
VAT_REGION	Integer	4	Y*	Unique identifier of VAT region. VAT region is determined by the VAT authority. This value should to a value in the DC_VAT_REGION.DAT file.	VAT_REGION	NUMBER(4)
VAT_TYPE	Alpha-numeric	1	Y*	Indicates whether the VAT rate is used for purchasing (Cost), selling (Retail), or both. Valid values are from the VTTP code type: C, R, or B.	VAT_TYPE	VARCHAR2(1)
VAT_CODE	Alpha-numeric	6	Y*	Unique identifier of VAT code. Valid values include: S= - StandardZ= - Zero. This value should correspond to a value from the DC_VAT_CODES DAT file.	VAT_CODE	VARCHAR2(6)
REVERSE_VAT_IND	Alpha-numeric	1	Y	Indicates if the department is subjected to reverse charge VAT. Valid values are ‘Y’ or ‘N’.	REVERSE_VAT_IND	VARCHAR2(1)

FILE FORMAT	EXTERNAL ORACLE TABLE DEFINITION
UDA_ID	Integer	5	Y	Unique identifier of the user -defined attributes (UDA) to be defaulted. Valid values are in the UDA_ID field in the UDA table.	UDA_ID	NUMBER(5)
MERCH_HIER_4	Integer	4	Y	Merchandise hierarchy level 4 associated with the defaulted UDA.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	N	Merchandise hierarchy level 5 associated with the defaulted UDA. Optional, but required if the MERCH_HIER_6 field is populated.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	N	Merchandise hierarchy level 6 associated to the defaulted UDA. Optional.	SUBCLASS	NUMBER(4)
UDA_VALUE	Integer	3	Y	Only the UDA_ID display_type 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.	UDA_VALUE	NUMBER(3)

Field Name (RMS Table)	Default Value	Comments
PROFIT_CALC_TYPE	DC_MERCH_DEFAULTS DEPT_PROFIT_CALC_TYPE	If dc_deps.profit_calc_type is NULL, use the value from the merch_defaults table.
PURCHASE_TYPE	NVL(SYSTEM.DEFAULTS DEPT_PURCHASE_TYPE, 0)	If dc_deps.purchase_type is NULL, use the value from the merch_defaults table. If that is NULL, then default to 0.
BUD_INT	DC_MERCH_DEFAULTS DEPT_BUD_INT	If DC_DEPS.MARKUP_CALC_TYPE is 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 DEPT_BUD_MKUP	If DC_DEPS.MARKUP_CALC_TYPE is 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 DEPT_MARKUP_CALC_TYPE	If dc_deps.markup_calc_type is NULL, use the value from the merch_defaults table.
OTB_CALC_TYPE	DC_MERCH_DEFAULTS DEPT_OTB_CALC_TYPE	If dc_deps.otb_calc_type is NULL, use the value from the merch_defaults table.
DEPT_VAT_INCL_IND	DC_MERCH_DEFAULTS DEPT_VAT_INCL_IND	If dc_deps.dept_vat_incl_ind is NULL, use the value from the merch_defaults table.

Field Name (RMS Table)	Default Value	Comments
CLASS_VAT_INCL_IND	SYSTEM_DEFAULTS.CLASS_VAT_INCL_IND	If dc_class. class_vat_incl_ind is NULL, use the value from merch_defaults.

Field Name (RMS Table)	Default Value	Comments
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.

Field Name (RMS Table)	Default Value	Comments
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_ CALC_TYPE	0	Value defaults to 0 if the file value is empty.

Organizational Hierarchy

This chapter describes the organization hierarchy data conversion. Data must be loaded in the following 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

§ Merchandise Hierarchy

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.

The following required tables must be loaded manually:

§ CHAIN

§ AREA

Warehouse

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ ADDR

§ WH

§ WH_ADD

§ STOCK_LEDGER_INSERTS

§ TRANSIT_TIMES (applicable to both store and warehouses)

§ COST_ZONE

§ COST_ZONE_GROUP_LOC

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_wh_org.ksh

This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

– 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:

Data Flow for the Organizational Hierarchy Warehouse Functional Area

File Format and External Oracle Tables

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

In the table definitions that follow, the File Format columns Field Name, Data Type, and Max Length define the structure of the source file.

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.

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.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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
WAREHOUSE_ID	Alpha-numeric	20	Y	Primary identifier for the warehouse for which the address record applies. In a multi-channel environment, only the physical warehouse should contain address records, so this field will contain physical warehouse IDs.	KEY_VALUE_1	VARCHAR2(20)
ADDR_TYPE	Alpha-numeric	2	Y	Type of address for this 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.	ADDR_TYPE	VARCHAR2(2)
PRIMARY_ADDR_IND	Alpha-numeric	1	Y	Indicates whether this address is the primary for the warehouse and address type. Valid values are Y (primary) and N (non-primary).	PRIMARY_ADDR_IND	VARCHAR2(1)
CONTACT_NAME	Alpha-numeric	120	N	Name of the primary contact person at this warehouse address.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	N	Phone number of the contact person at this warehouse address.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Fax number of this warehouse address.	CONTACT_FAX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	E-mail address of the contact person at this warehouse address.	CONTACT_EMAIL	VARCHAR2(100)
CONTACT_TELEX	Alpha-numeric	20	N	Telex number of the contact person at this warehouse address.	CONTACT_TELEX	VARCHAR2(20)
ADDR_LINE_1	Alpha-numeric	240	Y	First line of the address of this warehouse and address type.	ADD_1	VARCHAR2(240)
ADDR_LINE_2	Alpha-numeric	240	N	Second line of the address of this warehouse and address type.	ADD_2	VARCHAR2(240)
ADDR_LINE_3	Alpha-numeric	240	N	Third line of the address of this warehouse and address type.	ADD_3	VARCHAR2(240)
CITY	Alpha-numeric	120	Y	City of this address of the warehouse and address type.	CITY	VARCHAR2(120)
COUNTY	Alpha-numeric	250	N	County of the address of this warehouse and address type.	COUNTY	VARCHAR2(250)
STATE	Alpha-numeric	3	N	State of the address of this warehouse and address type. Values in this column must exist in the RMS STATE table.	STATE	VARCHAR2(3)
POSTAL_CODE	Alpha-numeric	30	N	Postal code (for example, ZIP code) of the address for this warehouse and address type.	POST	VARCHAR2(30)
COUNTRY_ID	Alpha-numeric	3	Y	Country code of the address of this warehouse and address type. Values in this column must exist in the RMS COUNTRIES table.	COUNTRY_ID	VARCHAR2(3)
JURISDICTION_CODE	Alpha-numeric	10	N	Jurisdiction code for the address	JURISDICTION_CODE	VARCHAR2(10)

DC_PWH Table

File name: DC_PWH.DAT

Table create SQL script: DBC_CREATE_PWH_TAB.SQL

External Oracle table created: DC_pwh

Suggested post-loading validation (sequence after dc_load_wh_org.ksh):

§ 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.

§ If WH.ORG_HIER_TYPE has a value of 10, ensure that WH.ORG_HIER_VALUE is a valid CHAIN.CHAIN.

§ If WH.ORG_HIER_TYPE has a value of 20, ensure that WH.ORG_HIER_VALUE is a valid AREA.AREA.

§ If WH.ORG_HIER_TYPE has a value of 30, ensure that WH.ORG_HIER_VALUE is a valid REGION.REGION.

§ If WH.ORG_HIER_TYPE has a value of 40, ensure that WH.ORG_HIER_VALUE is a valid DISTRICT.DISTRICT.

§ If WH.ORG_HIER_TYPE has a value of 50, ensure that WH.ORG_HIER_VALUE is a valid STORE.STORE.

§ Ensure that WH.VAT_REGION is a valid VAT_REGION.VAT_REGION, if WH.STOCKHOLIDNG_IND = Y.

§ Ensure that WH.CURRENCY_CODE is a valid CURRENCIES.CURRENCY_CODE.

§ 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 WH.STOCKHOLIDNG_IND = Y.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
WAREHOUSE_ ID	Integer	10	Y	Unique identifier of the 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.	WH	NUMBER(10)
WAREHOUSE_ NAME	Alpha-numeric	150	Y	Name of the warehouse being defined.	WH_NAME	VARCHAR2(150)
PRIMARY_ VIRTUAL_ WH_ID	Integer	10	N	(Applicable only in a multi-channel environment. 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.	PRIMARY_ VWH	NUMBER(10)
CURRENCY_ CODE	Alpha-numeric	3	Y	Currency in which all cost and retail values for this warehouse are represented. Valid values must exist in the RMS CURRENCIES table.	CURRENCY_CODE	VARCHAR2(3)
BREAK_PACK_ IND	Alpha-numeric	1	N	Indicates whether this warehouse is capable of distributing less than the supplier case quantity (supplier pack size). Valid values are Y and N.	BREAK_ PACK_ IND	VARCHAR2(1)
REDIST_WH_ IND	Alpha-numeric	1	N	Indicates whether this warehouse is considered a redistribution 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. Valid values are Y and N.	REDIST_WH_IND	VARCHAR2(1)
DELIVERY_ POLICY	Alpha-numeric	6	Y	Warehouse delivery policy for shipments from the 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.	DELIVERY_ POLICY	VARCHAR2(6)
FORECAST_ WH_IND	Alpha-numeric	1	N	Indicates whether the warehouse can be forecasted. Value are Y and N. Only warehouses with a value of Y are 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.	FORECAST_ WH_IND	VARCHAR2(1)
REPL_IND	Alpha-numeric	1	N	Indicates whether this warehouse can 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 the physical warehouse.	REPL_IND	VARCHAR2(1)
REPL_WH_ LINK	Integer	10	N	Replenishable warehouse that 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.	REPL_WH_ IND	NUMBER(10)
IB_IND	Alpha-numeric	1	N	Indicates whether the warehouse is an investment buy warehouse.	IB_IND	VARCHAR2(1)
IB_WH_LINK	Integer	10	N	Investment buy warehouse that is linked to the virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the investment buy warehouse. This field should only contain a value when the IB_IND is N.	IB_WH_ LINK	NUMBER(10)
AUTO_IB_ CLEAR	Alpha-numeric	1	N	Indicates whether the investment buy inventory should be automatically transferred to the turn (replenishable) warehouse when an order is received by the turn warehouse. Valid values are Y and N.	AUTO_IB_ CLEAR	VARCHAR2(1)
INBOUND_ HANDLING_ DAYS	Integer	2	Y	Number of days that the warehouse requires to receive any item and get it to the shelf so that it is ready to pick. Valid value is a number from 0 to 99.	INBOUND_ HANDLING_DAYS	NUMBER(2)
WH_NAME_ SECONDARY	Alpha-numeric	150	N	Secondary description of the warehouse. This value is used to support multi-language, where the primary description may contain characters not easily sortable.	WH_NAME_SECON DARY	VARCHAR2(150)
EMAIL	Alpha-numeric	100	N	Primary e-mail for the warehouse.	EMAIL	VARCHAR2(100)
VAT_REGION	Integer	4	N	Required when VAT_IND in SYSTEM_OPTIONS is Y. Contains the warehouse VAT region, used by RMS to determine the VAT rates applicable at this location.	VAT_ REGION	NUMBER(4)
ORG_HIER_ TYPE	Integer	4	N	For reporting purposes, this field, along with the 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_ TYPE	NUMBER(4)
ORG_HIER_ VALUE	Integer	10	N	(See ORG_HIER_TYPE description). 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.	ORG_HIER_ VALUE	NUMBER(10)
DUNS_LOC	Alpha-numeric	4	N	Dun and Bradstreet number used to identify the warehouse location. This is reference-only data.	DUNS_LOC	VARCHAR2(4)
DUNS_ NUMBER	Alpha-numeric	9	N	Dun and Bradstreet number used to identify the warehouse. This is reference-only data.	DUNS_ NUMBER	VARCHAR2(9)
ORG_UNIT_ID	Numeric	15	N	Unique identifier for the Oracle Organizational ID.	ORG_UNIT_ ID	NUMBER(15)

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

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
VIRTUAL_ WH_ID	Integer	10	Y	Unique identifier for the virtual warehouse. This value must be unique across all warehouses (physical and virtual) and stores.	WH	NUMBER(10)
VIRTUAL_WH_ NAME	Alpha-numeric	150	Y	Name for the virtual warehouse being defined.	WH_NAME	VARCHAR2 (150)
PHYSICAL_ WAREHOUSE_ ID	Integer	10	Y	ID of the physical warehouse in which this virtual warehouse resides. To be valid, the physical warehouse must already exist in RMS and be loaded separately from the physical warehouse file.	PHYSICAL_WH	NUMBER(10)
RESTRICTED_ IND	Alpha-numeric	1	N	Indicator used to restrict virtual warehouses from receiving 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 stocks are 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.	RESTRICTED_ IND	VARCHAR2(1)
PROTECTED_ IND	Alpha-numeric	1	N	Indicates whether the virtual warehouse is affected last in transactions 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 physical warehouse. 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.	PROTECTED_ IND	VARCHAR2(1)
FORECAST_ WH_IND	Alpha-numeric	1	N	Indicates whether this warehouse is forecastable. Value values are Y and N. Only warehouses with a value of Y will be visible to the forecasting tool (RDF).	FORECAST_ WH_IND	VARCHAR2(1)
REPL_IND	Alpha-numeric	1	N	Indicates whether this warehouse can 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.	REPL_IND	VARCHAR2(1)
REPL_WH_ LINK	Integer	10	N	Replenishable warehouse that is linked to this virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the replenishable warehouse.	REPL_WH_ LINK	NUMBER(10)
IB_IND	Alpha-numeric	1	N	Indicates whether the warehouse is an investment buy warehouse.	IB_IND	VARCHAR2(1)
IB_WH_LINK	Integer	10	N	Investment buy warehouse that is linked to the virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the investment buy warehouse. This field should only contain a value when the IB_IND is equal to N.	IB_WH_LINK	NUMBER(10)
AUTO_IB_ CLEAR	Alpha-numeric	1	N	Indicates whether the investment buy inventory should be automatically transferred to the turn (replenishable) warehouse when an order is received by the turn warehouse. Valid values are Y and N.	AUTO_IB_ CLEAR	VARCHAR2(1)
FINISHER_IND	Alpha-numeric	1	N	Indicates whether the virtual warehouse performs finishing. Valid values are Y and N. Each channel must have at least one virtual warehouse that is not a finisher location (FINISHER_IND=N).	FINISHER_ IND	VARCHAR2(1)
WH_NAME_ SECONDARY	Alpha-numeric	150	N	Secondary description of the warehouse. This value is used to support multi-language, where the primary description may contain characters not easily sortable.	WH_NAME_ SECONDARY	VARCHAR2(150)
CHANNEL_ID	Integer	4	Y	Channel to which this virtual 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.	CHANNEL_ID	NUMBER(4)
TSF_ENTITY_ID	Integer	10	N	Legal entity to which this virtual warehouse belongs. 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.	TSF_ENTITY_ ID	NUMBER(10)
ORG_UNIT_ID	Numeric	15	N	Unique identifer for the Oracle Organizational ID.	ORG_UNIT_ID	NUMBER(15)
CUSTOMER_ORDER_LOC_IND	Alpha-numeric	1	N	Defines whether the virtual warehouse is customer orderable or not. Valid values:Y, N If not specified, it is defaulted to N.	CUSTOMER_ORDER_LOC_IND	VARCHAR2(1)

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).

§ Capture the count from TRANSIT_TIMES and compare to flat file DC_TRANSIT_TIMES.DAT to ensure that all rows are loaded.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
TRANSIT_ TIMES_ID	Integer	10	Y	Unique identifier of the record. This value can be sequence generated but must be unique per record loaded.	TRANSIT_ TIMES_ID	NUMBER(10)
MERCH_HIER_4	Integer	4	Y	Identifier of the fourth level (from the top down) of the merchandise hierarchy (department in the base configuration) to which the transit time record applies.	DEPT	NUMBER(4)
ORIGIN	Integer	10	Y	Identifier of the supplier or 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.	ORIGIN	NUMBER(10)
DESTINATION	Integer	10	Y	Identifier of the location from 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.	DESTINATION	NUMBER(10)
ORIGIN_ TYPE	Alpha-numeric	2	Y	Identifier of the type of value specified in the origin field. Valid values are: ST - Stores WH - Warehouses SU - Suppliers	ORIGIN_TYPE	VARCHAR2(2)
DESTINATION_ TYPE	Alpha-numeric	2	Y	Identifier of the type of value specified in the DESTINATION field. Valid values are: ST - Stores WH - Warehouses	DESTINATION_ TYPE	VARCHAR2(2)
TRANSIT_TIME	Integer	4	Y	Number of days it takes for a 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.	TRANSIT_TIME	NUMBER(4)
MERCH_HIER_5	Integer	4	N	Identifier of the fifth level (from the top down) of the merchandise hierarchy (class in the base configuration) to which the transit time 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 fifth level of the merchandise hierarchy.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	N	Identifier of the sixth level (from the top down) of the merchandise hierarchy (subclass in the base configuration) to which the transit time 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 sixth level of the merchandise hierarchy.	SUBCLASS	NUMBER(4)

DC_LOAD_WH_ORG.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS tables.

The dc_load_wh_org.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.

§ 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_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 in the data file (external table field values are NULL or not defined).

The function returns a Boolean value.

DC_wh_ADDr to addr Column Defaults

Column Name (RMS Table)	Default Value	Comments
ADDR_KEY	System-generated	Use addr sequence.
MODULE	WH	N/A
SEQ_NO	1	N/A
PUBLISH_IND	N	N/A

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. 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. 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.

Note: 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.

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_PWH to WH, WH_ADD, STOCK_LEDGER_INSERTS Column Defaults

Column Name (RMS Table)	Default Value	Comments
RESTRICTED_IND	N	N/A
PROTECTED_IND	N	N/A
BREAK_PACK_IND	Y	If NULL
REDIST_WH_IND	Y	If NULL
FORECAST_WH_IND	Y	If NULL
REPL_IND	NA	If multichannel = Y, then override file value with N; otherwise, default to Y.
IB_IND	N	N/A
STOCKHOLDING_IND		N if multi-channel, Y if not multi-channel
AUTO_IB_CLEAR	N	N/A
FINISHER_IND	N	This can only be Yes for virtual warehouses in a multi-channel environment, so always set it to N.
PHYSICAL_WH	NA	WAREHOUSE_ID
ORG_ENTITY_TYPE	R	If multichannel = Y, then override file value with N; otherwise, default to Y.

DC_VWH to WH, WH_ADD, STOCK_LEDGER_INSERTS Column Defaults

Column Name (RMS Table)	Default Value	Comments
STOCKHOLDING_IND	Y	N/A
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	NA	From physical warehouse
CURRENCY_CODE	NA	From physical warehouse
ORG_HIER_TYPE	NA	From physical warehouse
ORG_HIER_VALUE	NA	From physical warehouse
DELIVERY_POLICY	NA	From physical warehouse
EMAIL	NA	From physical warehouse
DUNS_NUMBER	NA	From physical warehouse
DUNS_LOC	NA	From physical warehouse
INBOUND_HANDLING_DAYS	NA	From physical warehouse
BREAK_PACK_IND	NA	From physical warehouse
REDIST_WH_IND	NA	From physical warehouse
ORG_ENTITY_TYPE	R	Values: R for regular warehouse, M for importer location and X for exporter location. Default value (if NULL in External Table) is R.
CUSTOMER_ORDER_LOC_IND	N	If NULL

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. 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

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ REGION

§ DISTRICT

§ STORE_ADD

§ STORE_DEPT_AREA

§ ADDR

§ WF_CUSTOMER

§ WF_CUSTOMER_GROUP

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_store_org.ksh

This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

– 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

– dbc_create_wf_customer_tab.sql

– dbc_create_wf_customer_group_tab.sql

Data Flow

The following diagram shows the data flow for the Organizational Hierarchy Store functional area:

Data Flow for the Organizational Hierarchy Store Functional Area

File Format and External Oracle Tables

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 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.

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.

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 ensure that all rows are loaded.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
REGION	Integer	10	Y	Unique ID for the region.	REGION	NUMBER(10)
REGION_ NAME	Alpha-numeric	120	Y	Name of the region.	REGION_ NAME	VARCHAR2(120)
AREA	Integer	10	Y	ID of the area in which the region falls.	AREA	NUMBER(10)
MGR_NAME	Alpha-numeric	120	N	Name of the region manager.	MGR_ NAME	VARCHAR2(120)
CURRENCY_ CODE	Alpha-numeric	3	N	Currency under which the region operates. Valid values are in the RMS CURRENCIES table.	CURRENCY_CODE	VARCHAR2(3)

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 CURRENCIES.CURRENCY_CODE.

§ Capture the count from DISTRICT and compare to flat file DC_DISTRICT.DAT to ensure that all rows are loaded.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
DISTRICT	Integer	10	Y	Unique ID for the organization district.	DISTRICT	NUMBER(10)
DISTRICT_NAME	Alpha-numeric	120	Y	Name of the district.	DISTRICT_NAME	VARCHAR2(120)
REGION	Integer	10	Y	Unique ID for the region under which the district falls.	REGION	NUMBER(10)
MGR_NAME	Alpha-numeric	120	N	Name of the district manager.	MGR_NAME	VARCHAR2(120)
CURRENCY_CODE	Alpha-numeric	3	N	Currency under which the district operates. Valid values are in the RMS CURRENCIES table.	CURRENCY_CODE	VARCHAR2(3)

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

Suggested post-loading validation (sequence after dc_load_store_org.ksh):

§ Ensure that ADDR.KEY_VALUE_1 is a valid STORE_ADD.STORE.

§ Ensure that ADDR.STATE is a valid STATE.STATE.

§ Ensure that ADDR.COUNTRY_ID is a valid COUNTRY.COUNTRY_ID.

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
STORE_ID	Alpha-numeric	20	Y	Store ID for the address.	KEY_VALUE_1	VARCHAR2(20)
ADDR_TYPE	Alpha-numeric	2	Y	Type of address for this store. 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 for company stores and WFST for franchise stores.	ADDR_TYPE	VARCHAR2(2)
PRIMARY_ADDR_IND	Alpha-numeric	1	Y	Indicates whether this is the primary address for the address type.	PRIMARY_ADDR_IND	VARCHAR2(1)
CONTACT_NAME	Alpha-numeric	120	N	Name of the contact at this address.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	N	Phone number of the contact at the address.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Fax number of the contact at the address.	CONTACT_FAX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	E-mail of the contact at the address.	CONTACT_EMAIL	VARCHAR2(100)
CONTACT_TELEX	Alpha-numeric	20	N	Telex number of the contact at the address.	CONTACT_TELEX	VARCHAR2(20)
ADDR_LINE_1	Alpha-numeric	240	Y	First line of the address.	ADD_1	VARCHAR2(240)
ADDR_LINE_2	Alpha-numeric	240	N	Second line of the address.	ADD_2	VARCHAR2(240)
ADDR_LINE_3	Alpha-numeric	240	N	Third line of the address.	ADD_3	VARCHAR2(240)
CITY	Alpha-numeric	120	Y	City of the address.	CITY	VARCHAR2(120)
COUNTY	Alpha-numeric	250	N	County in which the city is located.	COUNTY	VARCHAR2(250)
STATE	Alpha-numeric	3	N	State in which the city is located.	STATE	VARCHAR2(3)
POSTAL_CODE	Alpha-numeric	30	N	ZIP code of the address.	POST	VARCHAR2(30)
COUNTRY_ID	Alpha-numeric	3	Y	Country ID. Valid values are in the RMS COUNTRY table.	COUNTRY_ID	VARCHAR2(3)
JURISDICTION_CODE	Alpha-numeric	10	N	Jurisdiction code for the address	JURISDICTION_CODE	VARCHAR2(10)

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

Suggested post-loading validation (sequence after dc_load_store_org.ksh):

§ Ensure that STORE_ADD.STORE is unique and does not exist on STORE.

§ Ensure that STORE_ADD.TSFZONE (if not NULL) is a valid TSFZONE.TRANSFER_ZONE.

§ Ensure that STORE_ADD.CURRENCY_CODE is a valid CURRENCIES.CURRENCY_CODE.

§ 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.STORE_FORMAT (if not NULL) is a valid STORE_FORMAT.STORE_FORMAT.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
STORE	Integer	10	Y	Unique ID of the store.	STORE	NUMBER(10)
STORE_NAME	Alpha-numeric	150	Y	Name of the store.	STORE_NAME	VARCHAR2(150)
STORE_NAME10	Alpha-numeric	10	Y	10-character abbreviation of the store name.	STORE_NAME10	VARCHAR2(10)
STORE_NAME3	Alpha-numeric	3	Y	3-character abbreviation of the store name.	STORE_NAME3	VARCHAR2(3)
STORE_CLASS	Alpha-numeric	1	Y	Code letter indicating the class of which the store is a member: A, B, C, D, or E.	STORE_CLASS	VARCHAR2(1)
STORE_MGR_NAME	Alpha-numeric	120	Y	Name of the store manager.	STORE_MGR_NAME	VARCHAR2(120)
STORE_OPEN_DATE	Alpha-numeric	9	Y	Date the store opened. Date format is DDMONYYYY (for example, 02JAN2011).	STORE_OPEN_DATE	DATE
STOCKHOLDING_IND	Alpha-numeric	1	N	Indicates whether the store can hold stock, default N.	STOCKHOLDING_IND	VARCHAR2(1)
DISTRICT	Integer	10	Y	ID of the district in which the store is located. Must be a valid district ID.	DISTRICT	NUMBER(10)
START_ORDER_DAYS	Integer	3	Y	Days before the store open date that the store can start accepting orders.	START_ORDER_DAYS	NUMBER(3)
CURRENCY_CODE	Alpha-numeric	3	Y	Currency in which the store operates. Must be a valid currency code in the RMS CURRENCIES table.	CURRENCY_CODE	VARCHAR2(3)
LANG	Integer	6	Y	Language in which the store operates. Must be a valid language code that exists in the RMS LANG table.	LANG	NUMBER(6)
COPY_REPL_IND	Alpha-numeric	1	N	Indicates whether the replenishment information set up for the like store will be copied (Y or N, default N).	COPY_REPL_IND	VARCHAR2(1)
TRAN_NO_GENERATED	Alpha-numeric	6	Y	Indicates whether the transaction ID is unique by store or by store/register (S or R).	TRAN_NO_GENERATED	VARCHAR2(6)
INTEGRATED_POS_IND	Alpha-numeric	1	Y	Indicates whether the POS at the store is integrated(Y or N).	INTEGRATED_POS_IND	VARCHAR2(1)
COPY_ACTIVITY_IND	Alpha-numeric	1	N	Indicates whether the like store’s closing date schedule should be copied in the creation of a new store based on a like store (Y or N, default N).	COPY_ACTIVITY_IND	VARCHAR2(1)
COPY_DLVRY_IND	Alpha-numeric	1	N	Indicates whether the like store’s delivery schedule should be copied in the creation of a new store based on a like store (Y or N, default N).	COPY_DLVRY_IND	VARCHAR2(1)
STORE_NAME_SECONDARY	Alpha-numeric	150	N	Secondary name of the store. This field can be populated only when system_options. secondary_desc_ind = Y.	STORE_NAME_SECONDARY	VARCHAR2(150)
STORE_CLOSE_DATE	Alpha-numeric	9	N	Date the store closed. Date format is DDMONYYYY (for example, 02JAN2011).	STORE_CLOSE_DATE	DATE(7)
ACQUIRED_DATE	Alpha-numeric	9	N	Date the store was acquired. Date format is DDMONYYYY.	ACQUIRED_DATE	DATE(7)
REMODEL_DATE	Alpha-numeric	9	N	Last date the store was remodeled. Date format is DDMONYYYY.	REMODEL_DATE	DATE(7)
FAX_NUMBER	Alpha-numeric	20	N	Fax number of the contact at the store.	FAX_NUMBER	VARCHAR2(20)
PHONE_NUMBER	Alpha-numeric	20	N	Phone number of the store.	PHONE_NUMBER	VARCHAR2(20)
EMAIL	Alpha-numeric	100	N	E-mail of the contact at the store.	EMAIL	VARCHAR2(100)
TOTAL_SQUARE_FT	Integer	8	N	Total square feet of the store.	TOTAL_SQUARE_FT	NUMBER(8)
SELLING_SQUARE_FT	Integer	8	N	Square feet dedicated to selling merchandise.	SELLING_SQUARE_FT	NUMBER(8)
LINEAR_DISTANCE	Integer	8	N	Total merchandise area of the store.	LINEAR_DISTANCE	NUMBER(8)
VAT_REGION	Integer	4	N	Number of the value added tax region in which this store is located. Required if vat_include_ind = N. Valid values are found in the RMS VAT_REGION table.	VAT_REGION	NUMBER(4)
VAT_INCLUDE_IND	Alpha-numeric	1	N	Indicates whether value added tax is included in the retail prices for the store. Valid values are Y or N, default N.	VAT_INCLUDE_IND	VARCHAR2(1)
CHANNEL_ID	Integer	4	N	In a multi-channel 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.	CHANNEL_ID	NUMBER(4)
STORE_FORMAT	Integer	4	N	Number indicating the format of the store. Valid values are found in the store format table.	STORE_FORMAT	NUMBER(4)
MALL_NAME	Alpha-numeric	120	N	Name of the mall in which the store is located.	MALL_NAME	VARCHAR2(120)
TRANSFER_ZONE	Integer	4	N	Transfer zone in which the store is located. Valid values are located in the tsfzone table.	TRANSFER_ZONE	NUMBER(4)
DEFAULT_WH	Integer	10	N	Number of the 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.	DEFAULT_WH	NUMBER(10)
STOP_ORDER_DAYS	Integer	3	N	Number of days before a store closing that the store will stop accepting orders. This column is used when the store_close_ date is defined.	STOP_ORDER_DAYS	NUMBER(3)
DUNS_NUMBER	Alpha-numeric	9	N	Dun and Bradstreet number to identify the store.	DUNS_NUMBER	VARCHAR2(9)
DUNS_LOC	Alpha-numeric	4	N	Dun and Bradstreet number to identify the location.	DUNS_LOC	VARCHAR2(4)
SISTER_STORE	Integer	10	N	Store number used to relate the current store to the historical data of an existing store.	SISTER_STORE	NUMBER(10)
TSF_ENTITY_ID	Integer	10	N	Legal entity in which the store is located. This field is required in a multi-channel environment. Foreign key to the TSF_ENTITY table	TSF_ENTITY_ID	NUMBER(10)
ORG_UNIT_ID	Numeric	15	N	Unique identifier for the Oracle Organizational ID.	ORG_UNIT_ID	NUMBER(15)
STORE_TYPE	Alpha-numeric	6	N	Type of store. Valid values are: C – Company F - Franchise	STORE_TYPE	VARCHAR2(6)
WF_CUSTOMER_ID	Integer	10	N	Unique ID of the franchise store. This column will be null if the store_type = C.	WF_CUSTOMER_ID	NUMBER(10)
AUTO_APPROVE_ORDERS_IND	Alpha-numeric	1	N	Indicates whether electronic orders to this store should be approved automatically if the order can be fulfilled. Valid values are Y and N.	AUTO_APPROVE_ORDERS_IND	VARCHAR2(1)
TIMEZONE_NAME	Alpha-numeric	64	Y	Name of the Time Zone in which the store is located.	TIMEZONE_ NAME	VARCHAR2(64)
CUSTOMER_ORDER_LOC	Alpha-numeric	1	N	Defines whether the store is customer orderable or not. Valid values: Company Stores : Y, N Franchise Stores : Y, N	CUSTOMER_ORDER_LOC_IND	VARCHAR2(1)

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

Suggested post-loading validation (sequence after dc_load_store_org.ksh):

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
STORE	Integer	10	Y	Unique ID of the store.	STORE	NUMBER(10)
MERCH_HIER_4	Integer	4	Y	Fourth level of the merchandise hierarchy, referred to as department in the base configuration. This field must contain a valid value from the RMS DEPS table.	DEPT	NUMBER(4)
EFFECTIVE_DATE	Date	7	Y	Date on which the area is effective for the store and hierarchy. Date format is DDMONYYYY (for example, 02JAN2011).	EFFECTIVE_DATE	DATE(7)
AREA	Number	12,4	Y	Area in the store used by the hierarchy value (department).	AREA	NUMBER(12,4)

DC_WF_CUSTOMER Table

File name: DC_WF_CUSTOMER.DAT

Table create SQL script: DBC_CREATE_WF_CUSTOMER_TAB.SQL

External Oracle table created: DC_WF_CUSTOMER

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
WF_ CUSTOMER_ ID	Integer	10	Y	Unique ID of the franchise store.	WF_CUSTOMER_ID	NUMBER(10)
WF_ CUSTOMER_ NAME	Alpha-numeric	120	Y	Name of the franchise store.	WF_CUSTOMER_NAME	VARCHAR2(120)
CREDIT_IND	Alpha-numeric	1	Y	Indicates whether the franchise store has good credit standing. Valid values are Y and N.	CREDIT_IND	VARCHAR2(1)
WF_ CUSTOMER_ GROUP_ID	Integer	10	Y	Unique ID of a customer group associated with the franchise store.	WF_CUSTOMER_GROUP_ID	NUMBER(10)

DC_WF_CUSTOMER_GROUP Table

File name: DC_WF_CUSTOMER_GROUP.DAT

Table create SQL script: DBC_CREATE_WF_CUSTOMER_GROUP_TAB.SQL

External Oracle table created: DC_WF_CUSTOMER_GROUP

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
WF_ CUSTOMER_ GROUP_ID	Integer	10	Y	Unique ID of the customer group.	WF_CUSTOMER_GROUP_ID	NUMBER(10)
WF_ CUSTOMER_ GROUP_ NAME	Alpha-numeric	120	Y	Name of the customer group.	WF_CUSTOMER_GROUP_NAME	VARCHAR2(120)

Segment Wrapper / Load Script (DC_LOAD_STORE_ORG.KSH)

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS 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 configuration file defines the directories used by the data conversion scripts.

§ 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_ 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.

The table below 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_STORE_ADDR to ADDR Column Defaults

Column Name (RMS Table)	Default Value	Comments
ADDR_KEY	System-generated	NA
MODULE	ST	NA
SEQ_NO	1	NA
PUBLISH_IND	N	NA

Required file to load: dc_store_addr.dat

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.

The table below 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_STORE_ADD to STORE_ADD Column Defaults

Column Name (RMS Table)	Default Value	Comments
STOCKHOLDING_IND	Y	Y or N
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
CUSTOMER_ORDER_LOC_IND	N	Defaults to N and NULL for company stores and stockholding franchise stores.

Required file to load: dc_store_add.dat

LOAD_STORE_DEPT_AREA ()

This function contains a PL/SQL block that selects from 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

LOAD_WF_CUSTOMER

This function selects all columns from the DC_WF_CUSTOMER external table and inserts data into the WF_CUSTOMER table. The DC_WF_CUSTOMER external table maps exactly to the RMS WF_CUSTOMER table.

Required file to load: dc_wf_customer.dat

LOAD_WF_CUSTOMER_GROUP

This function selects all columns from the DC_WF_CUSTOMER_GROUP external table and inserts data into the WF_CUSTOMER_GROUP table. The DC_WF_CUSTOMER_GROUP external table maps exactly to the RMS WF_CUSTOMER_GROUP table.

Required file to load: dc_wf_customer_group.dat

Post-Loading Requirements

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.

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. See “Appendix: Seed Data Installation” for more information.

§ COST_ZONE

§ COST_ZONE_GROUP_LOC

§ RPM requirements:

– RPM_ZONE_GROUP_TYPE

– RPM_ZONE_GROUP

– RPM_ZONE

– RPM_ZONE_LOCATION

Suppliers

This chapter describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ SUPS

§ ADDR (for supplier addresses)

§ SUP_IMPORT_ATTR

§ PARTNER

The following programs are included in the Suppliers 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_supplier.ksh

§ This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

– dbc_create_sups_tab.sql

– dbc_create_sup_addr_tab.sql

– dbc_create_sup_import_attr_tab.sql

– dbc_create_country_attrib_tab.sql

Data Flow

The following diagram shows the data flow for the Suppliers functional area:

Data Flow for the Suppliers Functional Area

Prerequisites

Before you begin using the data conversion toolset for Suppliers, you must complete data conversion for the following functional areas:

§ Core

§ Merchandise Hierarchy

§ Organizational Hierarchy

The following required tables must be loaded manually:

§ partner (required types: AG=agents, BK=advising or issuing banks, FA=factory)

§ outloc (required types: DP=discharge ports, LP=lading ports)

File Format and External Oracle Tables

The dc_load_supplier.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.

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.

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.

§ 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.

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
SUPPLIER	Integer	10	Y	Unique number for the supplier.	SUPPLIER	NUMBER(10)
SUP_NAME	Alpha-numeric	240	Y	Name of the supplier.	SUP_NAME	VARCHAR2(240)
SUP_NAME_SECONDARY	Alpha-numeric	240	N	Secondary name of the supplier. This field can only be populated when SYSTEM_OPTIONS. SECONDARY_DESC_IND = Y.	SUP_NAME_SECONDARY	VARCHAR2(240)
CONTACT_NAME	Alpha-numeric	120	Y	Name of contact at the supplier.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	Y	Phone number of the contact at the supplier.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Fax number of the contact at the supplier.	CONTACT_FAX	VARCHAR2(20)
CONTACT_PAGER	Alpha-numeric	20	N	Pager number of the contact at the supplier.	CONTACT_PAGER	VARCHAR2(20)
QC_IND	Alpha-numeric	1	Y	Indicates whether orders from this 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_IND	VARCHAR2(1)
QC_PCT	Float	12,4	N	Percentage of items per receipt that will be marked for quality checking.	QC_PCT	NUMBER(12,4)
QC_FREQ	Integer	2	N	Frequency with which items per receipt will be marked for quality checking.	QC_FREQ	NUMBER(2)
VC_IND	Alpha-numeric	1	Y	Indicates whether orders from this 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_IND	VARCHAR2(1)
VC_PCT	Float	12,4	N	Percentage of items per receipt that will be marked for vendor checking.	VC_PCT	NUMBER(12,4)
VC_FREQ	Integer	2	N	Frequency with which items per receipt will be marked for vendor checking.	VC_FREQ	NUMBER(2)
CURRENCY_CODE	Alpha-numeric	3	Y	Currency the supplier uses for business transactions. Valid values are in the RMS CURRENCIES table.	CURRENCY_CODE	VARCHAR2(3)
LANG	Integer	6	N	Supplier’s preferred language. This field is provided for custom purchase orders in a specified language. Valid values are stored in the LANG table in RMS.	LANG	NUMBER(6)
TERMS	Alpha-numeric	15	Y	Indicator identifying the 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. Valid values are in the RMS TERMS_HEAD table.	TERMS	VARCHAR2(15)
FREIGHT_TERMS	Alpha-numeric	30	Y	Indicator that references the freight terms that default when an order is created for the supplier. Valid values are in the RMS FREIGHT_TERMS table.	FREIGHT_TERMS	VARCHAR2(30)
RET_ALLOW_IND	Alpha-numeric	1	Y	Indicates whether the supplier accepts returns. Valid values are Y and N.	RET_ALLOW_IND	VARCHAR2(1)
RET_AUTH_REQ	Alpha-numeric	1	Y	Indicates whether returns must be accompanied by an authorization number when sent back to the vendor. Valid values are Y and N.	RET_AUTH_REQ	VARCHAR2(1)
RET_MIN_DOL_AMT	Numeric	20,4	N	Contains a value if the supplier requires a 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_MIN_DOL_AMT	NUMBER(20,4)
RET_COURIER	Alpha-numeric	250	N	Name of the courier that should be used for all returns to the supplier.	RET_COURIER	VARCHAR2(250)
DEFAULT_ HANDLING_PCT	Numeric	12,4	N	Percentage multiplied by the total order cost to determine the handling cost for the return.	HANDLING_PCT	NUMBER(12,4)
EDI_PO_IND	Alpha-numeric	1	Y	Indicates whether purchase orders will be sent to the supplier through Electronic Data Interchange. Valid values are Y and N.	EDI_PO_IND	VARCHAR2(1)
EDI_PO_CHG	Alpha-numeric	1	Y	Indicates whether purchase order changes will be sent to the supplier through Electronic Data Interchange. Valid values are Y and N.	EDI_PO_CHG	VARCHAR2(1)
EDI_PO_CONFIRM	Alpha-numeric	1	Y	Indicates whether this supplier will send acknowledgment of a purchase orders sent through Electronic Data Interchange. Valid values are Y and N.	EDI_PO_CONFIRM	VARCHAR2(1)
EDI_ASN	Alpha-numeric	1	Y	Indicates whether this supplier will send Advance Shipment Notifications electronically. Valid values are Y and N.	EDI_ASN	VARCHAR2(1)
EDI_SALES_RPT_FREQ	Alpha-numeric	1	N	EDI sales report frequency for this supplier. Valid values are: D - Sales and stock information will be downloaded daily. W - Sales and stock information will be downloaded weekly.	EDI_SALES_RPT_FREQ	VARCHAR2(1)
EDI_SUPP_AVAILABLE_IND	Alpha-numeric	1	Y	Indicates whether the supplier will send availability through EDI.	EDI_SUPP_AVAILABLE_IND	VARCHAR2(1)
EDI_CONTRACT_IND	Alpha-numeric	1	Y	Indicates whether contracts will be sent to the supplier through EDI.	EDI_CONTRACT_IND	VARCHAR2(1)
EDI_CHANNEL_ID	Integer	4	N	Used only in a multi-channel environment. 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.	EDI_CHANNEL_ID	NUMBER(4)
REPLEN_APPROVAL_IND	Alpha-numeric	1	Y	Indicates whether contract orders for the supplier should be created in approved status. Valid values are Y and N.	REPLEN_APPROVAL_IND	VARCHAR2(1)
SHIP_METHOD	Alpha-numeric	6	N	Unique number for the supplier.	SHIP_METHOD	VARCHAR2(6)
PAYMENT_METHOD	Alpha-numeric	6	N	Name of the supplier.	PAYMENT_METHOD	VARCHAR2(6)
CONTACT_TELEX	Alpha-numeric	20	N	Secondary name of the supplier. This field can only be populated when SYSTEM_OPTIONS. SECONDARY_DESC_IND = Y.	CONTACT_TELEX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	Name of contact at the supplier.	CONTACT_EMAIL	VARCHAR2(100)
SETTLEMENT_CODE	Alpha-numeric	1	Y	Phone number of the contact at the supplier.	SETTLEMENTCODE	VARCHAR2(1)
PRE_MARK_IND	Alpha-numeric	1	Y	Fax number of the contact at the supplier.	PRE_MARK_IND	VARCHAR2(1)
AUTO_APPR_INVC_IND	Alpha-numeric	1	Y	Pager number of the contact at the supplier.	AUTO_APPR_INVC_IND	VARCHAR2(1)
FREIGHT_CHARGE_IND	Alpha-numeric	1	Y	Indicates whether orders from this 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.	FREIGHT_CHARGE_IND	VARCHAR2(1)
BACKORDER_IND	Alpha-numeric	1	Y	Percentage of items per receipt that will be marked for quality checking.	BACKORDER_IND	VARCHAR2(1)
VAT_REGION	Integer	4	N	Frequency with which items per receipt will be marked for quality checking.	VAT_REGION	NUMBER(4)
INV_MGMT_LVL	Alpha-numeric	6	N	Indicates whether orders from this 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.	INV_MGMT_ LVL	VARCHAR2(6)
SERVICE_PERF_REQ_IND	Alpha-numeric	1	Y	Percentage of items per receipt that will be marked for vendor checking.	SERVICE_PERF_REQ_IND	VARCHAR2(1)
DELIVERY_POLICY	Alpha-numeric	6	Y	Frequency with which items per receipt will be marked for vendor checking.	DELIVERY_POLICY	VARCHAR2(6)
COMMENT_DESC	Alpha-numeric	2000	N	Currency the supplier uses for business transactions. Valid values are in the RMS CURRENCIES table.	COMMENT_DESC	VARCHAR2(2000)
DEFAULT_ITEM_LEAD_TIME	Integer	4	N	Supplier’s preferred language. This field is provided for custom purchase orders in a specified language. Valid values are stored in the LANG table in RMS.	DEFAULT_ITEM_LEAD_TIME	NUMBER(4)
DUNS_NUMBER	Alpha-numeric	9	N	Indicator identifying the 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. Valid values are in the RMS TERMS_HEAD table.	DUNS_NUMBER	VARCHAR2(9)
DUNS_LOC	Alpha-numeric	4	N	Indicator that references the freight terms that default when an order is created for the supplier. Valid values are in the RMS FREIGHT_TERMS table.	DUNS_LOC	VARCHAR2(4)
DEFAULT_ VMI_ORDER_STATUS	Alpha-numeric	6	N	Indicates whether returns must be accompanied by an authorization number when sent back to the vendor. Valid values are Y and N.	VMI_ORDER_STATUS	VARCHAR2(6)
DSD_IND	Alpha-numeric	1	Y	Contains a value if the supplier requires a 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.	DSD_IND	VARCHAR2(1)
SUPPLIER_PARENT	Numeric	10	N	This has a value of Supplier number for the Supplier Site. For Suppliers, this field will be NULL.	SUPPLIER_PARENT	NUMBER(10)
SUP_QTY_LEVEL	Alpha-numeric	6		This field is not nullable. Valid values are CA (cases) and EA (eaches). Default = EA	SUP_QTY_LEVEL	VARCHAR2(6)

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

Suggested post-loading validation (sequence after dc_load_supplier.ksh):

§ Ensure that ADDR.KEY_VALUE_1 is a valid SUPS.SUPPLIER.

§ Ensure that ADDR.STATE is a valid STATE.STATE.

§ Ensure that ADDR.COUNTRY_ID is a valid COUNTRY.COUNTRY_ID.

§ 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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
SUPPLIER_ID	Integer	10	Y	Unique ID of the supplier.	KEY_VALUE_1	NUMBER(10)
ADDR_TYPE	Alpha-numeric	2	Y	Type of address for this 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.	ADDR_TYPE	VARCHAR2(2)
PRIMARY_ADDR_IND	Alpha-numeric	1	Y	Indicates whether the address is the primary address for this address type.	PRIMARY_ADDR_IND	VARCHAR2(1)
CONTACT_NAME	Alpha-numeric	120	Y	Name of the contact at this address.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	N	Phone number of the contact at this address.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_TELEX	Alpha-numeric	20	N	Telex number of the contact at this address.	CONTACT_TELEX	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Fax number of the contact at this address.	CONTACT_FAX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	E-mail of the contact at this address.	CONTACT_EMAIL	VARCHAR2(100)
ADDR_LINE_1	Alpha-numeric	240	Y	First line of the address.	ADD_1	VARCHAR2(240)
ADDR_LINE _2	Alpha-numeric	240	N	Second line of the address.	ADD_2	VARCHAR2(240)
ADDR_LINE _3	Alpha-numeric	240	N	Third line of the address.	ADD_3	VARCHAR2(240)
CITY	Alpha-numeric	120	Y	Name of the city of this address.	CITY	VARCHAR2(120)
COUNTY	Alpha-numeric	250	N	County of the address.	COUNTY	VARCHAR2(250)
STATE	Alpha-numeric	3	N	State abbreviation of the address.	STATE	VARCHAR2(3)
POSTAL_CODE	Alpha-numeric	30	N	ZIP code.	POST	VARCHAR2(30)
COUNTRY_ID	Alpha-numeric	3	Y	Country code. Valid values are in the COUNTRY table.	COUNTRY_ID	VARCHAR2(3)
JURISDICTION_CODE	Alpha-numeric	10	N	Jurisdiction code for the address	JURISDICTION_CODE	VARCHAR2(10)

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

Suggested post-loading validation (sequence after dc_load_supplier.ksh):

§ 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.

§ Ensure that SUP_IMPORT_ATTR.PARTNER_2 is a valid PARTNER.PARTNER_ID with the same partner type as SUP_IMPORT_ATTR.PARTNER_TYPE_2.

§ Ensure that SUP_IMPORT_ATTR.PARTNER_3 is a valid PARTNER.PARTNER_ID with the same partner type as SUP_IMPORT_ATTR.PARTNER_TYPE_3.

§ Capture the count from SUP_IMPORT_ATTR and compare to flat file DC_SUP_IMPORT_ATTR.DAT to ensure that all rows are loaded.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
SUPPLIER	Integer	10	Y	Unique ID of the supplier.	SUPPLIER	NUMBER(10)
AGENT	Alpha-numeric	10	N	Agent associated with the supplier.	AGENT	VARCHAR2(10)
ADVISING_BANK	Alpha-numeric	10	N	Bank advising the Letter of Credit.	ADVISING_BANK	VARCHAR2(10)
ISSUING_BANK	Alpha-numeric	10	N	Bank issuing the letter of credit.	ISSUING_BANK	VARCHAR2(10)
LADING_PORT	Alpha-numeric	5	N	Identification number of the supplier's Lading Port.	LADING_PORT	VARCHAR2(5)
DISCHARGE_PORT	Alpha-numeric	5	N	Identification number of the supplier's discharge port.	DISCHARGE_PORT	VARCHAR2(5)
MFG_ID	Alpha-numeric	18	N	Manufacturer's tax identification number.	MFG_ID	VARCHAR2(18)
RELATED_IND	Alpha-numeric	1	Y	Indicates whether the supplier is related to the company. Valid values are Y and N.	RELATED_ IND	VARCHAR2(1)
BENEFICIARY_IND	Alpha-numeric	1	Y	Indicates whether this supplier can be a beneficiary. Valid values are Y and N.	BENEFICIARY_IND	VARCHAR2(1)
WITH_RECOURSE_IND	Alpha-numeric	1	Y	Conditional payment on the part of the bank, as instructed by the buyer. Valid values are Y and N.	WITH_RECOURSE_IND	VARCHAR2(1)
REVOCABLE_IND	Alpha-numeric	1	Y	Indicates whether the letter of credit is revocable. If this is Y, 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.	REVOCABLE_IND	VARCHAR2(1)
VARIANCE_PCT	Numeric	12,4	N	Allowed currency variance 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.	VARIANCE_PCT	NUMBER(12,4)
LC_NEG_DAYS	Integer	3	N	Number of days to negotiate documents.	LC_NEG_DAYS	NUMBER(3)
PLACE_OF_EXPIRY	Alpha-numeric	6	N	Place where the letter of credit will expire. Valid values are: 01 - Issuing Bank 02 - Advising Bank 03 - Miami 04 - New York 05 – Los Angeles	PLACE_OF_EXPIRY	VARCHAR2(6)
DRAFTS_AT	Alpha-numeric	6	N	Terms of draft (or when payment is to be made) for the letter of credit. Valid values are: 01 - At sight 02 - 30 Days 03 - 60 Days	DRAFTS_AT	VARCHAR2(6)
PRESENTATION_TERMS	Alpha-numeric	6	N	Terms of presentation (for example, “to the order of any bank” or “to XYZ Bank”). Valid values are: P - By payment A - By acceptance N - By negotiation	PRESENTATION_TERMS	VARCHAR2(6)
FACTORY	Alpha-numeric	10	N	Factory partner ID for the factory partner type.	FACTORY	VARCHAR2(10)
PARTNER_TYPE_1	Alpha-numeric	6	N	Partner type of the first additional partner. Valid values are in the RMS PARTNER table.	PARTNER_TYPE_1	VARCHAR2(6)
PARTNER_1	Alpha-numeric	10	N	Partner ID of the first additional partner. Valid values are in the RMS PARTNER table.	PARTNER_1	VARCHAR2(10)
PARTNER_TYPE_2	Alpha-numeric	6	N	Partner type of the second additional partner. Valid values are in the RMS PARTNER table.	PARTNER_TYPE_2	VARCHAR2(6)
PARTNER_2	Alpha-numeric	10	N	Partner ID of the second additional partner. Valid values are in the RMS PARTNER table.	PARTNER_2	VARCHAR2(10)
PARTNER_TYPE_3	Alpha-numeric	6	N	Partner type of the third additional partner. Valid values are in the RMS PARTNER table.	PARTNER_TYPE_3	VARCHAR2(6)
PARTNER_3	Alpha-numeric	10	N	Partner ID of the third additional partner. Valid values are in the RMS PARTNER table.	PARTNER_3	VARCHAR2(10)

DC_LOAD_SUPPLIER.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS table.

The dc_load_supplier.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.

§ 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_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.

The function returns a Boolean value.

DC_SUPs to SUPS Column Defaults

Field Name (RMS Table)	Default Value	Comments
SUP_STATUS	A	N/A
AUTO_APPR_DBT_MEMO_IND	Y	If NULL in external table
PREPAY_INVC_IND	Y	N/A
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	N/A
DBT_MEMO_CODE	Y	N/A
INVC_PAY_LOC	C	N/A
INVC_RECEIVE_LOC	C	N/A
ADDINVC_GROSS_NET	G	N/A
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.

The function returns a Boolean value.

DC_SUP_ADDRESS to ADDR Column Defaults

Field Name (RMS Table)	Default Value	Comments
ADDR_KEY	Sequence generated	N/A
MODULE	SUPP	N/A
SEQ_NO	1	N/A
ADDR_TYPE	See the note that follows.	N/A
PUBLISH_IND	N	N/A

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.

The function returns a Boolean value.

Required file to load: dc_sup_import_attr.dat

Post-Loading Requirements

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.

Partner

This section describes data conversion for the RMS PARTNER table. The following programs are included in this functional area:

The following programs are included in this functional area:

§ Main wrapper dc_load_main.ksh

This main script is used across all functional areas to call segment load scripts. See Chapter 2, “Master Script (DC_LOAD_MAIN.KSH),” for details.

§ Segment load script dc_load_partner.ksh

This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

§ dbc_create_partner_tab.sql

§ dbc_create_partner_addr_tab.sql

Data Flow

The following diagram shows the data flow for the MSOB Partner functional area:

Data Flow for the MSOB Partner Functional Area

File Format and External Oracle Tables

The dc_load_partnerwh_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

In the table definitions that follow, the File Format columns Field Name, Data Type, and Max Length define the structure of the source file.

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.

DC_PARTNER Table

File name: DC_PARTNER.DAT

Table create SQL script: DBC_CREATE_PARTNER_TAB.SQL

External Oracle table created: DC_PARTNER

Suggested post-loading validation (sequence after dc_load_partner.ksh):

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
PARTNER_TYPE	Alpha-numeric	6	Y	Specifies type of partner. E.g. Bank ‘BK’, etc.	PARTNER_TYPE	VARCHAR2 (6)
PARTNER_ID	Alpha-numeric	10	Y	Unique ID for the partner.	PARTNER_ID	VARCHAR2(10)
PARTNER_DESC	Alpha-numeric	240	Y	Description or name of partner	PARTNER_DESC	VARCHAR2(240)
CURRENCY_CODE	Alpha-numeric	3	Y	Currency for business transaction.	CURRENCY_CODE	VARCHAR2(3)
LANG	Integer	6	N	Partner’s preferred language.	LANG	NUMBER(6)
STATUS	Alpha-numeric	1	Y	Determines whether the partner is currently active.	STATUS	VARCHAR2(1)
CONTACT_NAME	Alpha-numeric	120	Y	Name of partner’s representative contact.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	Y	Phone number.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Fax Number.	CONTACT_FAX	VARCHAR2(20)
CONTACT_TELEX	Alpha-numeric	20	N	Telex Number.	CONTACT_TELEX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	Email ID.	CONTACT_EMAIL	VARCHAR2(100)
MFG_ID	Alpha-numeric	18	N	Manufacturer’s Tax Identification Number.	MFG_ID	VARCHAR2(18)
PRINCIPLE_COUNTRY_ID	Alpha-numeric	3	N	Country ID to which Partner is assigned.	PRINCIPLE_COUNTRY_ID	VARCHAR2(3)
LINE_OF_CREDIT	Integer	20,4	N	The line of credit the company has at the bank in Partner’s currency.	LINE_OF_CREDIT	NUMBER(20,4)
OUTSTAND_CREDIT	Integer	20,4	N	Total amount of credit that the company has used / charged against in the Partner’s currency.	OUTSTAND_CREDIT	NUMBER(20,4)
OPEN_CREDIT	Integer	20,4	N	Total amount that the company can still charge against in the Partner’s currency.	OPEN_CREDIT	NUMBER(20,4)
YTD_CREDIT	Integer	20,4	N	Total amount of credit the company has used this year to date.	YTD_CREDIT	NUMBER(20,4)
YTD_DRAWDOWNS	Integer	20,4	N	The year to date payments the bank has made on behalf of the company.	YTD_DRAWDOWNS	NUMBER(20,4)
TAX_ID	Alpha-numeric	18	N	Unique Tax Identification Number.	TAX_ID	VARCHAR2(18)
TERMS	Alpha-numeric	15	Y	Payment Terms for partner.	TERMS	VARCHAR2(15)
SERVICE_PERF_REQ_IND	Alpha-numeric	1	Y	Indicates if the expense vendor’s services must be confirmed as performed before paying an invoice.	SERVICE_PERF_REQ_IND	VARCHAR2(1)
INVC_PAY_LOC	Alpha-numeric	6	N	Indicates where the invoices from this vendor are paid.	INCV_PAY_LOC	VARCHAR2(6)
INVC_RECEIVE_LOC	Alpha-numeric	6	N	Indicates where the invoices from this vendor are received.	INVC_RECEIVE_LOC	VARCHAR2(6)
IMPORT_COUNTRY_IND	Alpha-numeric	3	N	Import country of the import authority.	IMPORT_COUNTRY_IND	VARCHAR2(3)
PRIMARY_IA_IND	Alpha-numeric	1	Y	Indicates whether the import authority is a primary import authority.	PRIMARY_IA_IND	VARCHAR2(1)
COMMENT_DESC	Alpha-numeric	2000	N	Contains any comments associated with partner.	COMMENT_DESC	VARCHAR2(2000)
TSF_ENTITY_ID	Integer	10	N	ID of transfer entity with which External finisher is associated.	TSF_ENTITY_ID	NUMBER(10)
VAT_REGION	Integer	4	N	Vat region with which partner is associated.	VAT_REGION	NUMBER(4)
ORG_UNIT_ID	Integer	15	N	Organization Unit ID.	ORG_UNIT_ID	NUMBER(15)
PARTNER_NAME_SECONDARY	Alpha-numeric	240	N	This will hold the secondary name of the partner.	PARTNER_NAME_SECONDARY	VARCHAR2(240)
AUTO_RCV_STOCK_IND	Alpha-numeric	1	Y	This will indicate whether the system will update the stock for the external finisher when the 1st leg of the transfer is shipped. Valid values are 'Y'es or 'N'o.	AUTO_RECEIVE_IND	VARCHAR2(1)

DC_PARTNER_ADDR Table

File name: DC_PARTNER_ADDR.DAT

Table create SQL script: DBC_CREATE_PARTNER_ADDR_TAB.SQL

External Oracle table created: DC_PARTNER_ADDR

Suggested post-loading validation (sequence after dc_load_partner.ksh):

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
KEY_VALUE_1	Alpha-numeric	20	Y	This column contains specific ID or type that the address is attached to. If the module is Partner, then key_value_1 holds the type of Partner [BANK (BK),Freight Forwarder (FF), Factory (FA), Agent (AG), Broker (BR), and Importer (IM)], else it will hold the supplier number.	KEY_VALUE_1	VARCHAR2(20)
KEY_VALUE_2	Alpha-numeric	20	N	If the module is Partner (PTNR), then this field will contain the partners ID, else this field will be null.	KEY_VALUE_2	VARCHAR2(20)
ADDR_TYPE	Alpha-numeric	2	Y	This column contains a unique number used to distinguish between different addresses.	ADDR_TYPE	VARCHAR2(2)
PRIMARY_ADDR_IND	Alpha-numeric	1	Y	Indicates if this address is the primary for the partner and address type. Valid values are ‘Y’ (primary) and ‘N’ (non-primary).	PRIMARY_ADDR_IND	VARCHAR2(1)
CONTACT_NAME	Alpha-numeric	120	N	Contains the name for the primary contact person at this partner address.	CONTACT_NAME	VARCHAR2(120)
CONTACT_PHONE	Alpha-numeric	20	N	Contains the phone number for the contact person at this partner address.	CONTACT_PHONE	VARCHAR2(20)
CONTACT_FAX	Alpha-numeric	20	N	Contains the fax number for this partner address.	CONTACT_FAX	VARCHAR2(20)
CONTACT_EMAIL	Alpha-numeric	100	N	Contains the e-mail address for the contact person at this partner address.	CONTACT_EMAIL	VARCHAR2(100)
CONTACT_TELEX	Alpha-numeric	20	N	Contains the telex number for the contact person at this partner address.	CONTACT_TELEX	VARCHAR2(20)
ADDR_LINE_1	Alpha-numeric	240	Y	Contains the first line of this address for this partner and address type.	ADD_1	VARCHAR2(240)
ADDR_LINE_2	Alpha-numeric	240	N	Contains the second line of this address for this partner and address type.	ADD_2	VARCHAR2(240)
ADDR_LINE_3	Alpha-numeric	240	N	Contains the third line of this address for this partner and address type.	ADD_3	VARCHAR2(240)
CITY	Alpha-numeric	120	Y	Contains the city of this address for this partner and address type.	CITY	VARCHAR2(120)
COUNTY	Alpha-numeric	250	N	Contains the county of this address for this partner and address type.	COUNTY	VARCHAR2(250)
STATE	Alpha-numeric	3	N	Contains the state of this address for this partner and address type. Values in this column must exist in the RMS STATE table.	STATE	VARCHAR2(3)
POSTAL_CODE	Alpha-numeric	30	N	Contains the postal code (e.g. Zip Code) of this address for this warehouse and address type.	POST	VARCHAR2(30)
COUNTRY_ID	Alpha-numeric	3	Y	Contains the country code of this address for this warehouse and address type. Values in this column must exist in the RMS COUNTRIES table.	COUNTRY_ID	VARCHAR2(3)
JURISDICTION_CODE	Alpha-numeric	10	N	Jurisdiction code for the address.	JURISDICTION_CODE	VARCHAR2(10)

DC_LOAD_PARTNER.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh. It serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS tables.

The script can be configured to create the tables and load data, or just to 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_wh.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.

§ 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 topic describes the load functions that are included in the load script.

LOAD_PARTNER

This function contains a PL/SQL block that selects from the DC_PARTNER external table and inserts the data to the RMS PARTNER table. All the columns from the external Oracle table defined previously map directly to the RMS table.

The following fields are required:

· PARTNER_ID

· PARTNER_TYPE

· PARTNER_DESC

· CURRENCY_CODE

· STATUS

· CONTACT_NAME

· CONTACT_PHONE

· TERMS

· SERVICE_PERF_REQ_IND

· PRIMARY_IA_IND

The function returns a Boolean value.

Required file to load: dc_partner.dat

LOAD_PARTNER_ADDRESS

This function inserts data into the ADDR table by selecting all columns from the DC_PARTNER_ADDR external table and uses the defaults specified below for the columns that are not in DC_PARTNER_ADDR.

ADDR Column Defaults for partner

Field Name (RMS Table)	Default Value	Comments
ADDR_KEY	system generated	NA
MODULE	PTNR	NA
SEQ_NO	1	NA
PUBLISH_IND	N	NA

Required file to load: dc_partner_addr.dat

DBC_CREATE_COUNTRY_ATTRIB Table

File name: DC_COUNTRY_ATTRIB.DAT

Table create SQL script: DBC_CREATE_COUNTRY_ATTRIB_TAB.SQL – This SQL script is called by the dc_load_COUNTRY_ATTRIB.Ksh and will create the DC_COUNTRY_ATTRIB oracle external table with the following definition:

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req. Ind	Description	Field Name	Data Type
COUNTRY_ID	Alpha-numeric	3	Y	This contains the code which uniquely identifies the country.	COUNTRY_ID	VARCHAR2 (3)

DC_COUNTRY_ATTRIB.KSH Segment Wrapper / Load Script

This ksh script will be called by the dc_load_main.ksh and serves two purposes:

§ Call the create table scripts to create the Oracle external tables.

§ Call the load data script to insert data from the Oracle external tables to the RMS tables.

The script calls internal functions (defined within the script) that insert-select from the external 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 will only be called if a parameter option (-c) is passed in from the command line. By default (without the option -c) this script will load the data. The dc_load_partner.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).

ERROR HANDLING: All functions should include the exception part of the PL/SQL block and handle WHEN OTHERS by assigning the sqlerrm to the KSH variable and return (see sample in Segment Wrapper Script Design Doc).

COMMIT: In each function, follow each insert statement with a commit command.

The following functions should be defined in the declaration of the script:

§ LOAD_COUNTRY_ATTRIB – This function selects from the DC_COUNTRY_ATTRIB external table and inserts the data to the RMS COUNTRY_ATTRIB table. All the columns from the external oracle table defined above will directly map to the RMS table. The table below lists columns that do not exist on DC_ COUNTRY_ATTRIB and will need to be defaulted as described. The function returns a Boolean value.

The COUNTRY_ATTRIB field is required:

Required file to load: dc_country_attrib.dat

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

§ Merchandise Hierarchy

§ Organizational Hierarchy

§ Suppliers

Fashion Items

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ ITEM_MASTER

§ VAT_ITEM (only if system_optinos.vat_ind is Y and default_tax_type is not GTAX)

§ UDA_ITEM_LOV

§ ITEM_CHRG_HEAD

§ ITEM_CHRG_DETAIL

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 script. Refer to Chapter 2 for details.

§ Segment load script dc_load_fashion_item.ksh

This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

* 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 the Fashion Items functional area:

Data Flow for the Fashion Items Functional Area

File Format and External Oracle Tables

The dc_load_fashion_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.

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.

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.

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.

§ Ensure that ITEM_MASTER.DIFF_2 (if not NULL) is a valid DIFF_IDS.DIFF_ID or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

§ Ensure that ITEM_MASTER.DIFF_3 (if not NULL) is a valid DIFF_IDS.DIFF_ID or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

§ Ensure that ITEM_MASTER.DIFF_4 (if not NULL) is a valid DIFF_IDS.DIFF_ID or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
STYLE	Alpha-numeric	20	Y	ID that uniquely identifies the style.	ITEM	VARCHAR2(25)
STYLE_DESC	Alpha-numeric	250	Y	Description of the style.	ITEM_DESC	VARCHAR2(250)
STYLE_SHORT_DESC	Alpha-numeric	120	N	Short description of the style.Default = First 120 characters of sku_desc.	SHORT_DESC	VARCHAR2(120)
STYLE_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of the item for Yomi requirement.	ITEM_DESC_SECONDARY	VARCHAR2(250)
MERCH_HIER_4	Integer	4	Y	Identifier of the merchandise hierarchy level 4 of which merchandise hierarchy level 5 is a member. Valid values are in the DEPT field in the DEPS table in RMS.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	Y	Identifier of the merchandise 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.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	Y	Identifier of the merchandise hierarchy level 6 which is a member of merchandise hierarchy level 5. Valid values are in the SUBCLASS field in the SUBCLASS table in RMS.	SUBCLASS	NUMBER(4)
SIZE_1_GROUP	Alpha-numeric	10	Y	Size group ID of the first size that differentiates the style from 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_1_GROUP	VARCHAR2(10)
SIZE_2_GROUP	Alpha-numeric	10	N	Size group ID of the second size that differentiates the style from its item_parent. Valid values are in the diff_group and diff_ids tables.	SIZE_2_GROUP	VARCHAR2(10)
COLOR_GROUP	Alpha-numeric	10	N	ID of the color grouping of the style that differentiates the style from its item_parent (for example, pastel colors). Valid values are in the diff_group and diff_ids tables.	COLOR_GROUP	VARCHAR2(10)
OTHER_DIFF_GROUP	Alpha-numeric	10	N	ID of the other grouping of the style that differentiates the style from its item_parent. Valid values are in the diff_group and diff_ids tables.	OTHER_GROUP	VARCHAR2(10)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
ITEM_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item 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.	ITEM_AGGREGATE	VARCHAR2(1)
SIZE_1_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	SIZE_1_AGGREGATE	VARCHAR2(1)
SIZE_2_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	SIZE_2_AGGREGATE	VARCHAR2(1)
COLOR_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item 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 if the item may aggregate by numbers. Aggregation allows the system to support allocations at a style/grouping level. The remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	COLOR_AGGREGATE	VARCHAR2(1)
OTHER_DIFF_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	OTHER_AGGREGATE	VARCHAR2(1)
STYLE_COMMENTS	Alpha-numeric	2000	N	Comments associated with the style.	STYLE_COMMENTS	VARCHAR2(2000)
PERISHABLE_IND	Alpha-numeric	1	N	A grocery item attribute used to indicate whether an item is perishable or not.	PERISHABLE_IND	VARCHAR2(1)
PRODUCT_CLASSIFICATION	Alpha-numeric	6	N	This Column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric	30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

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 diff 4 aggregate indicators should be NULL.

For item aggregation, the item can aggregate only by up to 1 less than the total number of differentiator groups specified. For example, if an item has three differentiator groups associated with it, the user can aggregate by as many as two of those groups.

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

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_FASHION_SKU.DAT to ensure that all rows are loaded.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
SKU	Alpha-numeric	20	Y	ID that uniquely identifies the stock keeping unit.	ITEM	VARCHAR2(25)
PRIMARY_SKU_IND	Alpha-numeric	1	Y	Not in RMS item_master, needed for defaulting style.	PRIMARY_SKU_IND	VARCHAR2(1)
STYLE	Alpha-numeric	20	Y	Style associated with the SKU.	ITEM_PARENT	VARCHAR2(25)
SKU_DESC	Alpha-numeric	250	Y	Description of the SKU.	ITEM_DESC	VARCHAR2(250)
SHORT_DESC	Alpha-numeric	120	Y	Short description of the SKU. Default = First 120 characters of sku_desc	SHORT_DESC	VARCHAR2(120)
SKU_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of the SKU for Yomi requirement.	ITEM_DESC_SECONDARY	VARCHAR2(250)
COST_ZONE_GROUP_ID	Integer	-	Y	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.	COST_ZONE_GROUP_ID	NUMBER(4)
STANDARD_UOM	Alpha-numeric	4	Y	Unit of measure in which stock of the item is tracked at a corporate level.	STANDARD_UOM	VARCHAR2(4)
UOM_CONV_FACTOR	Numeric	12,10	N	Conversion factor between an each and 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.	UOM_CONV_FACTOR	NUMBER(20,10)
STORE_ORDER_MULT	Alpha-numeric	1	Y	Unit type in which merchandise shipped from the warehouses to the stores must be specified. Valid values are: C - Cases I - Inner E - Eaches	STORE_ORDER_MULT	VARCHAR2(1)
SKU_COMMENTS	Alpha-numeric	2000	N	Comments associated with the SKU.	SKU_COMMENTS	VARCHAR2(2000)
MERCHANDISE_IND	Alpha-numeric	1	N	Indicates if the item is a merchandise item (Y, N). Default = Y	MERCHANDISE_IND	VARCHAR2(1)
FORECAST_IND	Alpha-numeric	1	N	Indicates if this item will be interfaced to an external forecasting system (Y, N). Default = Y	FORECAST_IND	VARCHAR2(1)
SIZE_1	Alpha-numeric	10	N	Size ID of the first size that differentiates the SKU from its Style (for example, 34 waist). Valid values are in the diff_group and diff_id tables.	SIZE_1	VARCHAR2(10)
SIZE_2	Alpha-numeric	10	N	Size ID of the first size that differentiates the SKU from its style (for example, 32 length). Valid values are in the diff_group and diff_id tables.	SIZE_2	VARCHAR2(10)
COLOR	Alpha-numeric	10	N	Color ID of the color that differentiates the SKU from its style (for example, red). Valid values are found in the diff_group and diff_id tables.	COLOR	VARCHAR2(10)
OTHER_VARIANT	Alpha-numeric	10	N	ID of the differentiator that differentiates the SKU from its style (for example, stone-washed). Valid values are in the diff_group and diff_id tables.	OTHER_VARIANT	VARCHAR2(10)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
PERISHABLE_IND	Alpha-numeric	1	N	A grocery item attribute used to indicate whether an item is perishable or not.	PERISHABLE_IND	VARCHAR2(1)
PRODUCT_CLASSIFICATION	Alpha-numeric	6	N	This Column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric	30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

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

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 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.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.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req	Description	Field Name	Data Type
XREF_ITEM	Alpha-numeric	25	Y	The ID that uniquely identifies the scanning barcode associated with a product.	ITEM	VARCHAR2(25)
XREF_ DESC	Alpha-numeric	250	Y	Description of the item.	ITEM_DESC	VARCHAR2(250)
XREF_SHORT_DESC	Alpha-numeric	120	N	Default = First 120 characters of xref_desc.	SHORT_DESC	VARCHAR2(120)
XREF_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of the SKU for Yomi requirement.	ITEM_DESC_SECONDARY	VARCHAR2(250)
SKU	Alpha-numeric	25	Y	Stock keeping unit associated with the xref_item.	ITEM_PARENT	VARCHAR2(25)
STYLE	Alpha-numeric	25	Y	Style associated with the xref_item.	ITEM_GRANDPARENT	VARCHAR2(25)
XREF_COMMENTS	Alpha-numeric	2000	N	Comments associated with the xref_item.	STYLE_COMMENTS	VARCHAR2(2000)
PRIMARY_REF_ITEM_IND	Alpha-numeric	1	N	Indicates that xref_item is the primary item for the stock keeping unit. Note – there can only be one primary xref item for a SKU. Default = N.	PRIMARY_REF_IND	VARCHAR2(1)
ITEM_NUMBER_TYPE	Alpha-numeric	6	Y	Code specifying what type the xref_item is. Valid values for this field are in the code type UPCT on the code_head and code_detail tables. Examples are UPC, EAN and others.	ITEM_NUMBER_TYPE	VARCHAR2(6)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_ TYPE	VARCHAR2(6)
PERISHABLE_IND	Alpha-numeric	1	N	A grocery item attribute used to indicate whether an item is perishable or not.	PERISHABLE_IND	VARCHAR2(1)
PRODUCT_CLASSIFICATION	Alpha-numeric	6	N	This column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric	30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

DC_LOAD_FASHION_ITEM.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS tables.

The dc_load_fashion_item.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.

§ 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_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).

The function returns a Boolean value.

DC_STYLE and DC_FASHION_SKU to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_NUMBER_TYPE	ITEM	NA
ITEM_LEVEL	1	NA
TRAN_LEVEL	2	NA
SHORT_DESC	substr 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
ITEM_AGGREGATE_IND	N	If NULL
DIFF_1_AGGREGATE_IND	N	If NULL
DIFF_2_AGGREGATE_IND	N	If NULL
DIFF_3_AGGREGATE_IND	N	If NULL
DIFF_4_AGGREGATE_IND	N	If NULL
PERISHABLE_IND	N	N/A

SKUs

For SKUs, 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_FASHION_SKU to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_NUMBER_TYPE	ITEM	NA
ITEM_LEVEL	2	NA
TRAN_LEVEL	2	NA
SHORT_DESC	substr 120 characters from SKU_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
ORDERABLE_IND	Y	NA
SELLABLE_IND	Y	NA
MERCHANDISE_IND	Y	If NULL
FORECAST_IND	Y	If NULL
INVENTORY_IND	Y	NA
ITEM_AGGREGATE_IND	N	NA
DIFF_1_AGGREGATE_IND	N	NA
DIFF_2_AGGREGATE_IND	N	NA
DIFF_3_AGGREGATE_IND	N	NA
DIFF_4_AGGREGATE_IND	N	NA
PRIMARY_REF_ITEM_IND	N	NA
CONST_DIMEN_IND	N	NA
GIFT_WRAP_IND	N	NA
SHIP_ALONE_IND	N	NA
ITEM_XFORM_IND	N	NA
PACK_IND	N	NA
SIMPLE_PACK_IND	N	NA
CATCH_WEIGHT_IND	N	NA
CONTAINS_INNER_IND	N	NA
PERISHABLE_IND	N	NA

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 DC_HARDLINES from the DC_STYLE table and from DC_STYLE joined with DC_FASHION_SKU.

If the VAT indicator is turned on in system_options and default tax type is not GTAX (SVAT is used), 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

INSERT_FASHION_DEFAULTS

This function inserts item defaults from the merchandise hierarchy specifications for VAT,UDAs and ITEM Charges. Create two cursors to retrieve using bulk collect into a PL/SQL table the ITEM, DEPT, CLASS and SUBCLASS values from DC_STYLE and from DC_STYLE joined with DC_FASHION_SKU.

If vat is turned on in system_options and default tax type is not GTAX (SVAT is used), retreive sku information and call the VAT_SQL.DEFAULT_VAT_ITEM.

Retrieve style information and call UDA_SQL.INSERT_DEFAULTS and ITEM_CHARGE_SQL.DC_DEFAULT_CHRGS. Retreive sku information and call UDA_SQL.INSERT_DEFAULTS and ITEM_CHARGE_SQL.DEFAULT_CHRGS.

Required file to load: dc_style.dat, dc_fashion_sku.dat

LOAD_XREF_ITEMS

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 information is provided in the data file (external table field values are NULL or not defined).

The function returns a Boolean value.

DC_FASHION_XREF and DC_FASHION_SKU to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_LEVEL	3	NA
TRAN_LEVEL	2	NA
SHORT_DESC	substr 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
PRIMARY_REF_ITEM_ IND	N	If NULL
CREATE_DATETIME	SYSDATE	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_ DATETIME	SYSDATE	NA
PERISHABLE_IND	N	NA

Required files to load: dc_style.dat, dc_fashion_sku.dat,dc_fashion_xref.dat

Hardline Items

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ ITEM_MASTER

§ VAT_ITEM (only if system_optinos.vat_ind is Y and default_tax_type is not GTAX)

§ UDA_ITEM_LOV

§ ITEM_CHRG_HEAD

§ ITEM_CHRG_DETAIL

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. See Chapter 2, “Master Script (DC_LOAD_MAIN.KSH),” for details.

§ Segment load script dc_load_hardline_item.ksh. This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

– 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.

Data Flow for the Hardline Functional Area

File Format and External Oracle Tables

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.

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.

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.

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):

§ Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL = 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 ensure that all rows are loaded.

§ Ensure that ITEM_MASTER.DEPT/ITEM_MASTER.CLASS/
ITEM_MASTER.SUBCLASS combination exists in SUBCLASS.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req	Description	Field Name	Data Type
SKU	Alpha-numeric	20	Y	ID that uniquely identifies the stock keeping unit.	ITEM	VARCHAR2(25)
SKU_DESC	Alpha-numeric	120	Y	Description of the SKU.	ITEM_DESC	VARCHAR2(250)
SKU_SHORT_DESC	Alpha-numeric		N	Short description of the SKU. Default = First 120 characters of sku_desc.	SHORT_DESC	VARCHAR2(120)
SKU_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of the SKU for Yomi requirement.	ITEM_DESC_SECONDARY	VARCHAR2(250)
MERCH_HIER_4	Integer	4	Y	Identifier of the merchandise hierarchy level 4 of which merchandise hierarchy level 5 is a member. Valid values are in the DEPT field in the DEPS table in RMS.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	Y	Identifier of the merchandise 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.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	Y	Identifier of the merchandise hierarchy level 6 which is a member of merchandise hierarchy level 5. Valid values are in the SUBCLASS field in the SUBCLASS table in RMS.	SUBCLASS	NUMBER(4)
COST_ZONE_GROUP_ID	Integer		Y	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.	COST_ZONE_GROUP_ID	NUMBER(4)
UOM_CONV_FACTOR	Floating Point	12,10	N	Conversion factor between an each and 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.	UOM_CONV_FACTOR	NUMBER(20,10)
STANDARD_UOM	Alpha-numeric	4	Y	Unit of measure in which stock of the item is tracked at a corporate level.	STANDARD_UOM	VARCHAR2(4)
STORE_ORDER_MULT	Alpha-numeric	1	Y	Unit type in which merchandise shipped from the warehouses to the stores must be specified. Valid values are: C - Cases I - Inner E - Eaches	STORE_ORD_MULT	VARCHAR2(1)
SKU_COMMENTS	Alpha-numeric	2000	N	Comments associated with the SKU.	COMMENTS	VARCHAR2(2000)
MERCHANDISE_IND	Alpha-numeric	1	N	Indicates whether the item is a merchandise item (Y or N). Default = Y.	MERCHANDISE_IND	VARCHAR2(1)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
FORECAST_IND	Alpha-numeric	1	N	Indicates whether this item will be interfaced to an external forecasting system (Y or N). Default = Y.	FORECAST_IND	VARCHAR2(1)

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.

§ 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.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_ITEM_NUMBER_TYPE is a valid CODE_DETAIL.CODE, where CODE_DETAIL.CODE_TYPE = UPCT.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Req	Description	Field Name	Data Type
XREF_ITEM	Alpha-numeric	25	Y	ID that uniquely identifies the scanning bar code associated with a product.	ITEM	VARCHAR2(25)
XREF_DESC	Alpha-numeric	250	Y	Description of the item.	ITEM_DESC	VARCHAR2(250)
XREF_SHORT_DESC	Alpha-numeric	120	N	Default = 120 characters of xref_desc.	SHORT_DESC	VARCHAR2(120)
XREF_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of the SKU for Yomi requirement.	ITEM_DESC_SECONDARY	VARCHAR2(250)
SKU	Alpha-numeric	25	Y	Stock keeping unit associated with the xref_item.	ITEM_PARENT	VARCHAR2(25)
XREF_COMMENTS	Alpha-numeric	2000	N	Comments associated with the xref_item.	COMMENTS	VARCHAR2 (2000)
PRIMARY_REF_ITEM_IND	Alpha-numeric	1	N	Indicates whether xref_item is the primary item for the stock keeping unit. Note: There can only be one primary xref item for a SKU. Default = N	PRIMARY_REF_ITEM_IND	VARCHAR2(1)
ITEM_NUMBER_TYPE	Alpha-numeric	6	Y	Code specifying what type the xref_item is. Valid values for this field are in the code type UPCT in the code_head and code_detail tables.	ITEM_NUMBER_TYPE	VARCHAR2(6)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)

DC_LOAD_HARDLINE_ITEM.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS 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 configuration file defines the directories used by the data conversion scripts.

§ 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_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.

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_hardlines to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_NUMBER_TYPE	ITEM	NA
ITEM_LEVEL	1	NA
TRAN_LEVEL	1	NA
SHORT_DESC	rtrim of substr b 120 characters from ITEM_DESC.	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
ORDERABLE_IND	Y	NA
SELLABLE_IND	Y	NA
INVENTORY_IND	Y	NA
MERCHANDISE_IND	Y	If NULL
FORECAST_IND	Y	If NULL
ITEM_AGGREGATE_IND	N	NA
DIFF_1_AGGREGATE_IND	N	NA
DIFF_2_AGGREGATE_IND	N	NA
DIFF_3_AGGREGATE_IND	N	NA
DIFF_4_AGGREGATE_IND	N	NA
PRIMARY_REF_ITEM_IND	N	NA
CONST_DIMEN_IND	N	NA
GIFT_WRAP_IND	N	NA
SHIP_ALONE_IND	N	NA
ITEM_XFORM_IND	N	NA
PACK_IND	N	NA
SIMPLE_PACK_IND	N	NA
CATCH_WEIGHT_IND	N	NA
CONTAINS_INNER_IND	N	NA
PERISHABLE_IND	N	NA

Required file to load: dc_hardlines.dat

INSERT_ITEM_DEFAULTS

This function inserts item defaults from the merchandise hierarchy specifications for 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 and default_tax_type is NOT GTAX (i.e. SVAT is used), 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

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 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_hardlines_XREF to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_LEVEL	2	NA
TRAN_LEVEL	1	NA
SHORT_DESC	rtrim of substr b 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
PRIMARY_REF_ITEM_IND	N	If NULL
PERISHABLE_IND	N	NA

Required files to load: dc_hardlines.dat, dc_hardlines_xref.dat

Grocery Items

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ ITEM_MASTER

§ VAT_ITEM (only if system_optinos.vat_ind is Y and default_tax_type is not GTAX)

§ UDA_ITEM_LOV

§ ITEM_CHRG_HEAD

§ ITEM_CHRG_DETAIL

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. See Chapter 2, “Master Script (DC_LOAD_MAIN.KSH),” for details.

§ Segment load script dc_load_grocery_items.ksh.

§ This wrapper calls the external Oracle table create and load scripts listed below.

§ External Oracle table create scripts:

– 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 Flow for the Grocery Items Functional Area

File Format and External Oracle Tables

The dc_load_grocery_items.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.

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.

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):

§ 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.

§ Ensure that ITEM_MASTER.DIFF_2 (if not NULL) is a valid DIFF_IDS.DIFF_ID
or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

§ Ensure that ITEM_MASTER.DIFF_3 (if not NULL) is a valid DIFF_IDS.DIFF_ID
or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

§ Ensure that ITEM_MASTER.DIFF_4 (if not NULL) is a valid DIFF_IDS.DIFF_ID
or DIFF_GROUP_HEAD.DIFF_GROUP_ID.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
PRODUCT_LINE	Alpha-numeric	25	Y	ID that uniquely identifies the product line.	ITEM	VARCHAR2(25)
PRODUCT_LINE_DESC	Alpha-numeric	250	Y	Description of the product line.	ITEM_DESC	VARCHAR2(250)
PRODUCT_LINE_SHORT_DESC	Alpha-numeric	120	N	Short description of the product line. Default = First 120 characters of item_desc	SHORT_DESC	VARCHAR2(120)
PRODUCT_LINE_DESC_SECONDARY	Alpha-numeric	250	N	Secondary description of product line.	ITEM_DESC_SECONDARY	VARCHAR2(250)
MERCH_HIER_4	Integer	4	Y	Identifier of the merchandise hierarchy level 4 of which merchandise hierarchy level 5 is a member. Valid values are in the DEPT field in the DEPS table in RMS.	DEPT	NUMBER(4)
MERCH_HIER_5	Integer	4	Y	Identifies the merchandise 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.	CLASS	NUMBER(4)
MERCH_HIER_6	Integer	4	Y	Identifies the merchandise hierarchy level 6 which is a member of merchandise hierarchy level 5. Valid values are in the SUBCLASS field in the SUBCLASS table in RMS.	SUBCLASS	NUMBER(4)
DIFF_GROUP_1_FLAVOR	Alpha-numeric	10	N	Flavor group ID that differentiates the product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_1	VARCHAR2(10)
DIFF_GROUP_2_SIZE	Alpha-numeric	10	N	Size group ID that differentiates the product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_2	VARCHAR2(10)
OTHER_GROUP_3	Alpha-numeric	10	N	ID of a grouping that differentiates the product line. Valid values are found in the diff_group and diff_ids tables.	DIFF_3	VARCHAR2(10)
OTHER_GROUP_4	Alpha-numeric	10	N	ID of a grouping that differentiates the product line. Valid values are found in the diff_group and diff_ids tables.	DIFF_4	VARCHAR2(10)
ITEM_AGGREGATE	Alpha-numeric	1	N	Default = N Indicator for the item aggregating up to specific 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 differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	ITEM_AGGREGATE_IND	VARCHAR2(1)
FLAVOR_AGGREGATE_IND	Alpha-numeric	1	N	Default = N Indicator for the item aggregating up to a product line/flavor level. This 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	DIFF_1_AGGREGATE_IND	VARCHAR2(1)
SIZE_AGGREGATE_IND	Alpha-numeric	1	N	Default = N Indicator for the item aggregating up to a product line/size grouping level. This 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	DIFF_2_AGGREGATE_IND	VARCHAR2(1)
OTHER_GROUP_3__AGGREGATE_IND	Alpha-numeric	1	N	Default = N Indicator for the item aggregating up to a product line/other grouping level. This 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	DIFF_3_AGGREGATE_IND	VARCHAR2(1)
OTHER_GROUP_4_AGGREGATE_IND	Alpha-numeric	1	N	Default = N Indicator for the item aggregating up to a product line/grouping level. This 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 remaining differentiators that are not a part of the aggregate group represent the curve portion of the allocation algorithm.	DIFF_4_AGGREGATE_IND	VARCHAR2(1)
PRODUCT_LINE_COMMENTS	Alpha-numeric	2000	N	Comments associated with the product line.	COMMENTS	VARCHAR2(2000)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
PRODUCT_CLASSIFICATION	Alpha-numeric	6	N	This Column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric	30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

Note: The same number of aggregate indicators should be populated as the number of corresponding differentiator 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 differentiator groups specified. For example, if an item has three differentiator groups associated with it, the user can aggregate by as many as two of those groups.

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 (later in this chapter) will also validate the rows loaded to the DC_PRODUCT table.

FILE FORMAT					EXTERNAL ORACLE TABLE DEFINITION
Field Name	Data Type	Max Length	Required	Description	Field Name	Data Type
PRODUCT	Alpha-numeric	25	Y	ID that identifies the product.	ITEM	VARCHAR2(25)
PRIMARY_ PRODUCT_IND	Alpha-numeric	1	N	Not in the RMS ITEM_MASTER table, needed for defaulting product line.	PRIMARY_ PRODUCT_IND	VARCHAR2(1)
PRODUCT_LINE	Alpha-numeric	25	Y	Product line associated with the product.	ITEM_PARENT	VARCHAR2(25)
PRODUCT_ DESC	Alpha-numeric	250	Y	Description of the product.	ITEM_DESC	VARCHAR2(250)
PRODUCT_ SHORT_ DESC	Alpha-numeric	120	N	Default = First 120 characters of item_desc (product_desc)	SHORT_DESC	VARCHAR2(120)
PRODUCT_ DESC_ SECON DARY	Alpha-numeric	250	N	Secondary description of the product.	ITEM_DESC_ SECONDARY	VARCHAR2(250)
COST_ ZONE_ GROUP_ID	Integer	4	Y	Cost zone group associated with the product. This field is only required when elc_ind (landed cost indicator) is set to Y in the system_options table within RMS.	COST_ZONE_ GROUP_ID	NUMBER(4)
UOM_ CONV_ FACTOR	Numeric	20,10	N	Conversion factor between an each and 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.	UOM_CONV_ FACTOR	NUMBER(20,10)
STAN DARD_ UOM	Alpha-numeric	4	Y	Unit of measure in which stock of the product is tracked at a corporate level.	STANDARD_ UOM	VARCHAR2(4)
STORE_ ORD_ MULT	Alpha-numeric	1	Y	Unit type in which products shipped from the warehouses to the stores must be specified. Valid values are: C - Cases I - Inner E - Eaches	STORE_ORD_ MULT	VARCHAR2(1)
MERCHAN DISE_IND	Alpha-numeric	1	N	Default = Y Indicates if the product is a merchandise item (Y or N).	MERCHAN DISE_IND	VARCHAR2(1)
FORECAST_IND	Alpha-numeric	1	N	Default = Y Indicates if this product will be interfaced to an external forecasting system (Y or N).	FORECAST_IND	VARCHAR2(1)
DIFF_1_ FLAVOR	Alpha-numeric	10	N	Flavor ID of the flavor that differentiates the product from its product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_1	VARCHAR2(10)
DIFF_2_ SIZE	Alpha-numeric	10	N	Size ID of the size that differentiates the product from its product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_2	VARCHAR2(10)
OTHER_ DIFF_3	Alpha-numeric	10	N	ID of the differentiator that differentiates the product from its product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_3	VARCHAR2(10)
OTHER DIFF_4	Alpha-numeric	10	N	ID of the differentiator that differentiates the product from its product line. Valid values are in the diff_group and diff_ids tables in RMS.	DIFF_4	VARCHAR2(10)
CATCH_ WEIGHT_ IND	Alpha-numeric	1	N	Default = N Indicates whether the item should be weighed when it arrives at a location. Valid values for this field are Y and N.	CATCH_ WEIGHT_IND	VARCHAR2(1)
HANDLING_TEMP	Alpha-numeric	6	N	Temperature information associated with the item. Valid values for this field are in the code type HTMP in the code_head and code_detail tables.	HANDLING_ TEMP	VARCHAR2(6)
HANDLING_SENSITIVITY	Alpha-numeric	6	N	Sensitivity information associated with the item. Valid values for this field are in the code type HSEN in the code_head and code_detail tables.	HANDLING_ SENSITIVITY	VARCHAR2(6)
WASTE_TYPE	Alpha-numeric	6	N	Identifies wastage 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_TYPE	VARCHAR2(6)
WASTE_PCT	Alpha-numeric	12,4	N	Average percent of wastage for the item over its shelf life. Used in inflating the retail price for wastage items.	WASTE_PCT	NUMBER(12,4)
DEFAULT_ WASTE_PCT	Alpha-numeric	12,4	N	Default daily wastage percent for spoilage type wastage items. This value defaults to all item locations and represents the average amount of wastage that occurs on a daily basis.	DEFAULT_ WASTE_PCT	NUMBER(12,4)
PACKAGE_ SIZE	Alpha-numeric	12,4	N	Size of the product 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_SIZE	NUMBER(12,4)
PACKAGE_ UOM	Alpha-numeric	4	N	Unit of measure associated with the package size. This field is used for reporting purposes, and by Retail Price Management to determine same-sized and different-sized items.	PACKAGE_ UOM	VARCHAR2(4)
DEPOSIT_ ITEM_TYPE	Alpha-numeric	6	N	Deposit item component type. A 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.	DEPOSIT_ITEM_TYPE	VARCHAR2(6)
CONTAINER_ITEM	Alpha-numeric	25	N	Container item number for a contents item. This field is only populated and required if the DEPOSIT_ITEM_ TYPE = E.	CONTAINER_ ITEM	VARCHAR2(25)
DEPOSIT_IN_ PRICE_UOM	Alpha-numeric	6	N	Indicates if the deposit amount is included in the price per UOM calculation for a contents item ticket. This value is only required if the DEPOSIT_ITEM_TYPE = E. Valid values are: I - Include deposit amount E - Exclude deposit amount	DEPOSIT_IN_ PRICE_PER_ UOM	VARCHAR2(6)
RETAIL_ LABEL_TYPE	Alpha-numeric	6	N	Indicates any special label type associated with an item (for example, pre-priced 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_TYPE	VARCHAR2(6)
RETAIL_ LABEL_ VALUE	Numeric	20,4	N	Value associated with the retail label type.	RETAIL_LABEL_VALUE	NUMBER(20,4)
PRODUCT_ COMMENTS	Alpha-numeric	2000	N	Comments associated with the product.	COMMENTS	VARCHAR2 (2000)
PERISHABLE_IND	Alpha-numeric	1	N	This field indicates whether the item is perishable or not, Valid values for this field are Y, N. Default = N	PERISHABLE_ IND	VARCHAR2(1)
AIP_CASE_TYPE	Alpha-numeric	6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
PRODUCT_CLASSIFICATION	Alpha-numeric	6	N	This Column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric	30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

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):

§ 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.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_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.

FILE FORMAT						EXTERNAL ORACLE TABLE DEFINITION
Field Name		Data Type	Max Length	Req	Description	Field Name	Data Type
VARIANT	Alpha-numeric		25	Y	ID that uniquely identifies the scanning barcode associated with a product.	ITEM	VARCHAR2(25)
VARIANT_NUMBER_TYPE	Alpha-numeric		6	Y	Code specifying what type the variant item is. Valid values for this field are in the code type UPCT in the code_head and code_detail tables.	ITEM_NUMBER_TYPE	VARCHAR2(6)
VAR_WGT_PLU_FORMAT	Alpha-numeric		1	N	Format ID that corresponds to the item's variable UPC. This value is only used for items with variable UPCs.	FORMAT_ID	VARCHAR2(1)
VAR_WGT_PLU_PREFIX	Integer		2	N	Prefix for variable 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.	PREFIX	NUMBER(2)
VARIANT_DESC	Alpha-numeric		250	Y	Description of the variant.	ITEM_DESC	VARCHAR2(250)
VARIANT_SHORT_DESC	Alpha-numeric		120	N	Short description of the variant. Default = First 120 characters of item_desc	SHORT_DESC	VARCHAR2(120)
VARIANT_DESC_SECONDARY	Alpha-numeric		250	N	Secondary description of the variant.	ITEM_DESC_SECONDARY	VARCHAR2(250)
PRODUCT	Alpha-numeric		25	Y	ID of the product associated with the variant.	ITEM_PARENT	VARCHAR2(25)
PRODUCT_LINE	Alpha-numeric		25	Y	ID of the product line associated with the variant.	ITEM_GRANDPARENT	VARCHAR2(25)
PRIMARY_REF_ITEM_IND	Alpha-numeric		1	N	Default = N Indicates if the variant is the primary variant for the product.	PRIMARY_REF_ITEM_IND	VARCHAR2(1)
VARIANT_COMMENTS	Alpha-numeric		2000	N	Comments associated with the variant.	COMMENTS	VARCHAR2(2000)
AIP_CASE_TYPE	Alpha-numeric		6	N	Only used if AIP is integrated. Determines which case sizes to extract against an item in the AIP interface. Applicable only to non-pack orderable items.	AIP_CASE_TYPE	VARCHAR2(6)
PRODUCT_CLASSIFICATION	Alpha-numeric		6	N	This Column contains item combinability codes (with code type PCLA) which provide a way to define which items can be combined (packed or boxed) together and communicate the same to WMS.	PRODUCT_CLASSIFICATION	VARCHAR2(6)
BRAND_NAME	Alpha-numeric		30	N	This is used to associate a brand to an item.	BRAND_NAME	VARCHAR2(30)

DC_LOAD_GROCERY_ITEMS.KSH Segment Wrapper / Load Script

This ksh script is called by the master script dc_load_main.ksh and serves two purposes:

§ It calls the create table scripts to create external Oracle tables.

§ It calls the load data script to insert data from external Oracle tables to RMS tables.

The dc_load_grocery_items.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.

§ 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_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.

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_product_line and dc_product to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_NUMBER_TYPE	ITEM	NA
ITEM_LEVEL	1	NA
TRAN_LEVEL	2	NA
SHORT_DESC	RTRIM/substrb 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
ITEM_AGGREGATE_IND	N	If NULL
DIFF_1_AGGREGATE_IND	N	If NULL
DIFF_2_AGGREGATE_IND	N	If NULL
DIFF_3_AGGREGATE_IND	N	If NULL
DIFF_4_AGGREGATE_IND	N	If NULL
PERISHABLE_IND	N	If NULL

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.

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_product_line and dc_product to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_NUMBER_TYPE	ITEM	NA
ITEM_LEVEL	2	NA
TRAN_LEVEL	2	NA
SHORT_DESC	RTRIM/substrb 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
STATUS	A	NA
CREATE_DATETIME	sysdate	NA
LAST_UPDATE_ID	Current user ID	NA
LAST_UPDATE_DATETIME	sysdate	NA
ORDERABLE_IND	Y	NA
SELLABLE_IND	Y	NA
INVENTORY_IND	Y	NA
MERCHANDISE_IND	Y	If NULL
FORECAST_IND	Y	If NULL
ITEM_AGGREGATE_IND	N	NA
DIFF_1_AGGREGATE_IND	N	NA
DIFF_2_AGGREGATE_IND	N	NA
DIFF_3_AGGREGATE_IND	N	NA
DIFF_4_AGGREGATE_IND	N	NA
PRIMARY_REF_ITEM_IND	N	NA
CONST_DIMEN_IND	N	NA
GIFT_WRAP_IND	N	NA
SHIP_ALONE_IND	N	NA
ITEM_XFORM_IND	N	NA
PACK_IND	N	NA
SIMPLE_PACK_IND	N	NA
CATCH_WEIGHT_IND	N	If NULL
CONTAINS_INNER_IND	N	NA
PERISHABLE_IND	N	NA

Required file to load: dc_product_line.dat, dc_product.dat

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.

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_GROCERY_variant and dc_hardlines to ITEM_MASTER Column Defaults

Column Name (RMS Table)	Default Value	Comments
ITEM_LEVEL	3	NA
TRAN_LEVEL	2	NA
SHORT_DESC	RTRIM/substrb 120 characters from ITEM_DESC	If NULL
DESC_UP	Upper ITEM_DESC	NA
PRIMARY_REF_ITEM_IND	N	If NULL
PERISHABLE_IND	N	If NULL

Required files to load: dc_product_line.dat, dc_product.dat, dc_grocery_variant.dat

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 and default_tax_type is NOT GTAX (i.e. SVAT is used), 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.

Required file to load: dc_product_line.dat, dc_product.dat

Pack Items

This section describes data conversion for the following RMS tables, listed in the order that they must be loaded:

§ ITEM_MASTER

§ PACKITEM

§ PACKITEM_BREAKOUT

§ PRICE_HIST

§ UDA_ITEM_LOV

§ RPM_ITEM_ZONE_PRICE

§ VAT_ITEM (only if system_optinos.vat_ind is Y and default_tax_type is not GTAX)

§ ITEM_CHRG_HEAD

§ ITEM_CHRG_DETAIL

The following programs are included in the Pack Items 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_packs.ksh

This wrapper calls the external Oracle table create scripts listed below.

§ External Oracle table create scripts:

– 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:

Data Flow for the Pack Items Functional Area

File Format and External Oracle Tables

The dc_load_packs.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.

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.

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):

§ Capture counts from ITEM_MASTER where ITEM_MASTER.ITEM_LEVEL = 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.

§ Ensure that ITEM_MASTER.COST_ZONE_GROUP_ID is a valid COST_ZONE_GROUP.ZONE_GROUP_ID if SYSTEM_OPTIONS.ELC_IND = Y and ITEM_MASTER.PACK_IND = Y and ITEM_MASTER.ORDERABLE_IND = Y. Ensure that ITEM_MASTER.DEPT/ITEM_MASTER.CLASS/
ITEM_MASTER.SUBCLASS combination exists in SUBCLASS.