Organization Mapping
The location dimension and organization hierarchy data is loaded mainly from the ORGANIZATION.csv
file
or from RMFCS. The primary data warehouse table for location data is W_INT_ORG_D
while the hierarchy comes
from W_INT_ORG_DH
, but several other tables are used to pre-calculate the values before export. The mapping
below is used by the interface program to move data from the data warehouse to RDX. W_DOMAIN_MEMBER_LKP_TL
is the holding table for translatable description strings. W_INT_ORG_ATTR_D
is for location attributes.
Other tables ending in TL
are for lookup strings for specific entities like store names. The mappings are
separated by store and warehouse, when different logic is used based on the location type. Only virtual warehouses are exported
here, physical warehouse records are excluded from the export.
Measure | Target Table | Target Column | Data Source |
---|---|---|---|
Location |
W_PDS_ORGANIZATION_D |
LOCATION |
W_INT_ORG_D.ORG_NUM |
Location Label |
W_PDS_ORGANIZATION_D |
LOC_NAME |
W_INT_ORG_D.ORG_NUM||'' ''||W_INT_ORG_D_TL.ORG_NAME |
District |
W_PDS_ORGANIZATION_D |
DISTRICT |
'WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_DS_NUM (stores) |
District Label |
W_PDS_ORGANIZATION_D |
DISTRICT_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Region |
W_PDS_ORGANIZATION_D |
REGION |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_RG_NUM (stores) |
Region Label |
W_PDS_ORGANIZATION_D |
REGION_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Area |
W_PDS_ORGANIZATION_D |
AREA |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_AR_NUM (stores) |
Area Label |
W_PDS_ORGANIZATION_D |
AREA_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Chain |
W_PDS_ORGANIZATION_D |
CHAIN |
''WH''||W_INT_ORG_D.ORG_NUM (warehouses), W_INT_ORG_DH_RTL_TMP.ORG_CH_NUM (stores) |
Chain Label |
W_PDS_ORGANIZATION_D |
CHAIN_NAME |
W_INT_ORG_D_TL.ORG_NAME (warehouses), W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME (stores) |
Company |
W_PDS_ORGANIZATION_D |
COMPANY |
W_INT_ORG_DH.ORG_TOP_NUM |
Company Label |
W_PDS_ORGANIZATION_D |
CO_NAME |
W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME |
Company Primary Currency |
W_PDS_ORGANIZATION_D |
COMPANY_CURRENCY |
RA_SRC_CURR_PARAM_G.COMPANY_CURRENCY |
Location Type Code |
W_PDS_ORGANIZATION_D |
LOC_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR42_NAME |
Location Type |
W_PDS_ORGANIZATION_D |
LOC_TYPE_NAME |
''Warehouse'' or "Store" |
Physical Warehouse ID for VWH |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH |
W_INT_ORG_ATTR_D.ORG_ATTR14_NAME |
Physical Warehouse Name |
W_PDS_ORGANIZATION_D |
PHYSICAL_WH_NAME |
W_INT_ORG_D_TL.ORG_NAME |
Channel ID |
W_PDS_ORGANIZATION_D |
CHANNEL_ID |
NVL(TO_CHAR(W_INT_ORG_ATTR_D.ORG_ATTR5_NUM_VALUE) ''NA'') |
Channel Desc |
W_PDS_ORGANIZATION_D |
CHANNEL_NAME |
NVL(W_INT_ORG_ATTR_D.ORG_ATTR5_NAME ''UNASSIGNED'') |
Store Class |
W_PDS_ORGANIZATION_D |
STORE_CLASS |
CASE NVL(W_INT_ORG_ATTR_D.ORG_ATTR41_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_INT_ORG_ATTR_D.ORG_ATTR41_NAME END |
Store Class Desc |
W_PDS_ORGANIZATION_D |
STORE_CLASS_DESCRIPTION |
CASE NVL(W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME END |
Store Format |
W_PDS_ORGANIZATION_D |
STORE_FORMAT |
CASE NVL(W_INT_ORG_ATTR_D.ORG_ATTR22_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_INT_ORG_ATTR_D.ORG_ATTR22_NAME END |
Store Format Desc |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_NAME |
CASE NVL(W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME,''-1'') WHEN ''-1'' THEN ''NA'' ELSE W_DOMAIN_MEMBER_LKP_TL.DOMAIN_MEMBER_NAME END |
Store Close Date |
W_PDS_ORGANIZATION_D |
STORE_CLOSE_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR3_DATE |
Store Open Date |
W_PDS_ORGANIZATION_D |
STORE_OPEN_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR2_DATE |
Store Remodel Date |
W_PDS_ORGANIZATION_D |
REMODEL_DATE |
W_INT_ORG_ATTR_D.ORG_ATTR1_DATE |
Location Currency Code |
W_PDS_ORGANIZATION_D |
CURRENCY |
W_INT_ORG_D.W_CURR_CODE |
Store Type |
W_PDS_ORGANIZATION_D |
STORE_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR23_NAME |
Stockholding Flag |
W_PDS_ORGANIZATION_D |
STOCKHOLDING_IND |
NVL(W_INT_ORG_ATTR_D.ORG_ATTR19_NAME, ’Y’) |
Default Warehouse ID |
W_PDS_ORGANIZATION_D |
DEFAULT_WH_ID |
W_INT_ORG_ATTR_D.ORG_ATTR20_NAME |
Store Format Description |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR21_NAME |
Store Format ID |
W_PDS_ORGANIZATION_D |
STORE_FORMAT_ID |
W_INT_ORG_ATTR_D.ORG_ATTR22_NAME |
Store UPS Disst |
W_PDS_ORGANIZATION_D |
STORE_UPS_DIST |
W_INT_ORG_ATTR_D.ORG_ATTR24_NAME |
Time Zone |
W_PDS_ORGANIZATION_D |
TIME_ZONE |
W_INT_ORG_ATTR_D.ORG_ATTR25_NAME |
Transfer Zone ID |
W_PDS_ORGANIZATION_D |
TRANSFER_ZONE_ID |
W_INT_ORG_ATTR_D.ORG_ATTR26_NAME |
Transfer Zone Description |
W_PDS_ORGANIZATION_D |
TRANSFER_ZONE_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR27_NAME |
VAT Region ID |
W_PDS_ORGANIZATION_D |
VAT_REGION_ID |
W_INT_ORG_ATTR_D.ORG_ATTR28_NAME |
VAT Include Flag |
W_PDS_ORGANIZATION_D |
VAT_INCLUDE_FLG |
W_INT_ORG_ATTR_D.ORG_ATTR29_NAME |
Virtual Warehouse Flag |
W_PDS_ORGANIZATION_D |
VIRTUAL_WH_FLG |
W_INT_ORG_ATTR_D.ORG_ATTR30_NAME |
Transfer Entity ID |
W_PDS_ORGANIZATION_D |
TRANSFER_ENTITY_ID |
W_INT_ORG_ATTR_D.ORG_ATTR31_NAME |
Transfer Entity Description |
W_PDS_ORGANIZATION_D |
TRANSFER_ENTITY_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR32_NAME |
Wholesale/Franchise Cust Type |
W_PDS_ORGANIZATION_D |
WF_CUST_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR35_NAME |
Wholesale/Franchise Group ID |
W_PDS_ORGANIZATION_D |
WF_GROUP_ID |
W_INT_ORG_ATTR_D.ORG_ATTR36_NAME |
Wholesale/Franchise Group Name |
W_PDS_ORGANIZATION_D |
WF_GROUP_NAME |
W_INT_ORG_ATTR_D.ORG_ATTR37_NAME |
Wholesale/Franchise Cust ID |
W_PDS_ORGANIZATION_D |
WF_CUST_ID |
W_INT_ORG_ATTR_D.ORG_ATTR38_NAME |
Wholesale/Franchise Cust Name |
W_PDS_ORGANIZATION_D |
WF_CUST_NAME |
W_INT_ORG_ATTR_D.ORG_ATTR39_NAME |
Sister Store ID |
W_PDS_ORGANIZATION_D |
SISTER_STORE_ID |
W_INT_ORG_ATTR_D.ORG_ATTR40_NAME |
Store Class Type |
W_PDS_ORGANIZATION_D |
STORE_CLASS_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR41_NAME |
Store Class Desc |
W_PDS_ORGANIZATION_D |
STORE_CLASS_DESC |
W_INT_ORG_ATTR_D.ORG_ATTR44_NAME |
Customer Order Location Indicator |
W_PDS_ORGANIZATION_D |
CUST_ORDER_LOC_IND |
W_INT_ORG_ATTR_D.ORG_ATTR48_NAME |
Customer Order Shipping Indicator |
W_PDS_ORGANIZATION_D |
CUST_ORDER_SHIP_IND |
W_INT_ORG_ATTR_D.ORG_ATTR49_NAME |
Gift Wrapping Indicator |
W_PDS_ORGANIZATION_D |
GIFT_WRAPPING_IND |
W_INT_ORG_ATTR_D.ORG_ATTR50_NAME |
Location Language ISO Code |
W_PDS_ORGANIZATION_D |
LANG_ISO_CODE |
W_INT_ORG_ATTR_D.ORG_ATTR51_NAME |
WH Delivery Policy |
W_PDS_ORGANIZATION_D |
WH_DELIVERY_POLICY |
W_INT_ORG_ATTR_D.ORG_ATTR54_NAME |
WH Redistribution Indicator |
W_PDS_ORGANIZATION_D |
WH_REDIST_IND |
W_INT_ORG_ATTR_D.ORG_ATTR55_NAME |
WH Replenishment Indicator |
W_PDS_ORGANIZATION_D |
WH_REPL_IND |
W_INT_ORG_ATTR_D.ORG_ATTR56_NAME |
WH Finisher Indicator |
W_PDS_ORGANIZATION_D |
WH_FINISHER_IND |
W_INT_ORG_ATTR_D.ORG_ATTR57_NAME |
Virtual WH Type |
W_PDS_ORGANIZATION_D |
VIRTUAL_WH_TYPE |
W_INT_ORG_ATTR_D.ORG_ATTR58_NAME |
DUNS Number |
W_PDS_ORGANIZATION_D |
DUNS_NUMBER |
W_INT_ORG_ATTR_D.ORG_ATTR59_NAME |
DUNS Location |
W_PDS_ORGANIZATION_D |
DUNS_LOC |
W_INT_ORG_ATTR_D.ORG_ATTR60_NAME |
Selling Area Sq. Ft. |
W_PDS_ORGANIZATION_D |
SELLING_AREA |
W_INT_ORG_ATTR_D.ORG_ATTR1_NUM_VALUE |
Linear Distance |
W_PDS_ORGANIZATION_D |
LINEAR_DISTANCE |
W_INT_ORG_ATTR_D.ORG_ATTR2_NUM_VALUE |
Total Sq. Ft. |
W_PDS_ORGANIZATION_D |
TOTAL_AREA |
W_INT_ORG_ATTR_D.ORG_ATTR3_NUM_VALUE |
WH Inbound Handling Days |
W_PDS_ORGANIZATION_D |
INBOUND_HANDLING_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR6_NUM_VALUE |
Stop Order Days |
W_PDS_ORGANIZATION_D |
STOP_ORDER_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR7_NUM_VALUE |
Start Order Days |
W_PDS_ORGANIZATION_D |
START_ORDER_DAYS |
W_INT_ORG_ATTR_D.ORG_ATTR8_NUM_VALUE |
Flexible Attribute 1 |
W_PDS_ORGANIZATION_D |
FLEX1_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX1_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX1_CHAR_VALUE) |
Flexible Attribute 2 |
W_PDS_ORGANIZATION_D |
FLEX2_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX2_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX2_CHAR_VALUE) |
Flexible Attribute 3 |
W_PDS_ORGANIZATION_D |
FLEX3_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX3_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX3_CHAR_VALUE) |
Flexible Attribute 4 |
W_PDS_ORGANIZATION_D |
FLEX4_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX4_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX4_CHAR_VALUE) |
Flexible Attribute 5 |
W_PDS_ORGANIZATION_D |
FLEX5_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX5_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX5_CHAR_VALUE) |
Flexible Attribute 6 |
W_PDS_ORGANIZATION_D |
FLEX6_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX6_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX6_CHAR_VALUE) |
Flexible Attribute 7 |
W_PDS_ORGANIZATION_D |
FLEX7_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX7_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX7_CHAR_VALUE) |
Flexible Attribute 8 |
W_PDS_ORGANIZATION_D |
FLEX8_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX8_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX8_CHAR_VALUE) |
Flexible Attribute 9 |
W_PDS_ORGANIZATION_D |
FLEX9_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX9_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX9_CHAR_VALUE) |
Flexible Attribute 10 |
W_PDS_ORGANIZATION_D |
FLEX10_CHAR_VALUE |
COALESCE(W_ORGANIZATION_FLEX_D.FLEX10_CHAR_VALUE,W_INT_ORG_ATTR_D.FLEX10_CHAR_VALUE) |
Flexible Attribute 11 |
W_PDS_ORGANIZATION_D |
FLEX11_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX11_CHAR_VALUE |
Flexible Attribute 12 |
W_PDS_ORGANIZATION_D |
FLEX12_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX12_CHAR_VALUE |
Flexible Attribute 13 |
W_PDS_ORGANIZATION_D |
FLEX13_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX13_CHAR_VALUE |
Flexible Attribute 14 |
W_PDS_ORGANIZATION_D |
FLEX14_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX14_CHAR_VALUE |
Flexible Attribute 15 |
W_PDS_ORGANIZATION_D |
FLEX15_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX15_CHAR_VALUE |
Flexible Attribute 16 |
W_PDS_ORGANIZATION_D |
FLEX16_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX16_CHAR_VALUE |
Flexible Attribute 17 |
W_PDS_ORGANIZATION_D |
FLEX17_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX17_CHAR_VALUE |
Flexible Attribute 18 |
W_PDS_ORGANIZATION_D |
FLEX18_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX18_CHAR_VALUE |
Flexible Attribute 19 |
W_PDS_ORGANIZATION_D |
FLEX19_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX19_CHAR_VALUE |
Flexible Attribute 20 |
W_PDS_ORGANIZATION_D |
FLEX20_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX20_CHAR_VALUE |
Flexible Attribute 21 |
W_PDS_ORGANIZATION_D |
FLEX21_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX21_CHAR_VALUE |
Flexible Attribute 22 |
W_PDS_ORGANIZATION_D |
FLEX22_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX22_CHAR_VALUE |
Flexible Attribute 23 |
W_PDS_ORGANIZATION_D |
FLEX23_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX23_CHAR_VALUE |
Flexible Attribute 24 |
W_PDS_ORGANIZATION_D |
FLEX24_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX24_CHAR_VALUE |
Flexible Attribute 25 |
W_PDS_ORGANIZATION_D |
FLEX25_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX25_CHAR_VALUE |
Flexible Attribute 26 |
W_PDS_ORGANIZATION_D |
FLEX26_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX26_CHAR_VALUE |
Flexible Attribute 27 |
W_PDS_ORGANIZATION_D |
FLEX27_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX27_CHAR_VALUE |
Flexible Attribute 28 |
W_PDS_ORGANIZATION_D |
FLEX28_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX28_CHAR_VALUE |
Flexible Attribute 29 |
W_PDS_ORGANIZATION_D |
FLEX29_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX29_CHAR_VALUE |
Flexible Attribute 30 |
W_PDS_ORGANIZATION_D |
FLEX30_CHAR_VALUE |
W_ORGANIZATION_FLEX_D.FLEX30_CHAR_VALUE |