Database Specifications

The following tables are used to store data in the KWI Back Office. These tables can be referenced when running database queries, or if you have general questions regarding how data is stored and retrieved.

Tip: If you are looking for a specific table or field name, use the table of contents or press Command/CNTL F on your keyboard to enter your search criteria into the browser.

Are you looking for data loaders? Click here.

Table Name: article_media

This table stores the URLs for the images and videos displayed in carousel format in the Item Details view.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	UPC/Article #	article_am	Character	10		{R} UPC #
3	Sequence	sequence_am	Numeric	6		0 is the primary image.
4	URL	url_am	Character	255		Sequence 0 URL should always be an image, not a video.
5	URL Type	type_am	Character	1		I = image V = video
6	Create Date	createdate_am	Timestamp			Auto generated create date
7	Modify Date	modifydate_am	Timestamp			Auto generated modify date

Table Name: sequence (Auto-Increment Tracking)

This table tracks basic auto-incremented integer values.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Sequence Number	num_seq	Numeric	11		Sequence number
3	Sequence Number type	type_seq	Character	255		Sequence number type
4	Program used	prog_seq	Character	255		Program used for the sequence
5	Time Stamp	inserted_seq	Timestamp			Date/Time

Table Name: batch_row (batch information)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_br	Numeric	11
3	Table Name	table_br	Character	45
4	First Rowid	begin_rowid_br	Numeric	11
5	Last Rowid	end_rowid_br	Numeric	11
6	Batch Type	type_br	Character	45
7	Batch Status	batch_status_br	Character	45
8	Batch ID	batch_id_br	Character	100
9	Date/Time	created_br	TimeStamp

Table Name: siz4 (case pack)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Casepack #	nam_sz4	Character	8		{R}
3	Field 1	sz1_sz4	Numeric	2
4	Field 2	sz2_sz4	Numeric	2
5	Field 3	sz3_sz4	Numeric	2
6	Field 4	sz4_sz4	Numeric	2
7	Field 5	sz5_sz4	Numeric	2
8	Field 6	sz6_sz4	Numeric	2
9	Field 7	sz7_sz4	Numeric	2
10	Field 8	sz8_sz4	Numeric	2
11	Field 9	sz9_sz4	Numeric	2
12	Field 10	sz10_sz4	Numeric	2
13	Field 11	sz11_sz4	Numeric	2
14	Field 12	sz12_sz4	Numeric	2
15	Field 13	sz13_sz4	Numeric	2
16	Field 14	sz14_sz4	Numeric	2
17	Field 15	sz15_sz4	Numeric	2
18	Field 16	sz16_sz4	Numeric	2
19	Field 17	sz17_sz4	Numeric	2
20	Field 18	sz18_sz4	Numeric	2
21	Field 19	sz19_sz4	Numeric	2
22	Field 20	sz20_sz4	Numeric	2
23	Field 21	sz21_sz4	Numeric	2
24	Field 22	sz22_sz4	Numeric	2
25	Field 23	sz23_sz4	Numeric	2
26	Field 24	sz24_sz4	Numeric	2
27	Field 25	sz25_sz4	Numeric	2
28	Field 26	sz26_sz4	Numeric	2
29	Field 27	sz27_sz4	Numeric	2
30	Field 28	sz28_sz4	Numeric	2
31	Field 29	sz29_sz4	Numeric	2
32	Field 30	sz30_sz4	Numeric	2
33	Field 31	sz31_sz4	Numeric	2
34	Field 32	sz32_sz4	Numeric	2
35	Field 33	sz33_sz4	Numeric	2
36	Field 34	sz34_sz4	Numeric	2
37	Field 35	sz35_sz4	Numeric	2
38	Field 36	sz36_sz4	Numeric	2
39	Field 37	sz37_sz4	Numeric	2
40	Field 38	sz38_sz4	Numeric	2
41	Field 39	sz39_sz4	Numeric	2
42	Field 40	sz40_sz4	Numeric	2
43	Field 41	sz41_sz4	Numeric	2
44	Field 42	sz42_sz4	Numeric	2
45	Field 43	sz43_sz4	Numeric	2
46	Field 44	sz44_sz4	Numeric	2
47	Field 45	sz45_sz4	Numeric	2
48	Field 46	sz46_sz4	Numeric	2
49	Field 47	sz47_sz4	Numeric	2
50	Field 48	sz48_sz4	Numeric	2
51	Field 49	sz49_sz4	Numeric	2
52	Field 50	sz50_sz4	Numeric	2
53	Casepack Description	des_sz4	Character	15
54	Size Table Code	md_sz4	Character	3		{R} Requires supporting table (mod – Size Table Maintenance)

Table Name: cash

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_ca	Numeric	6		{R}
3	Date	ym_ca	Date	8		{R} mm/dd/yy Default **/**/**
4	Price	pr_ca	Numeric	13	2	{R} Amount of paid in / out
5	Cash Reason Code	code_ca	Numeric	9		{R}
6	Employee #	emp_ca	Numeric	6		{R} Employee that processed the paid in / out
7	Transaction #	trn_ca	Numeric	6
8	Terminal #	trm_ca	Numeric	6

Table Name: clk (clock)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_ck	Numeric	6		{R}
3	Date	ym_ck	Date	8		{R} mm/dd/yy Default **/**/**
4	Time	tm_ck	Time	8		{R} hh:mm:ss
5	Employee #	emp_ck	Numeric	6		{R}
6	Status	stat_ck	Character	1		{R} I = In / O = Out
7	Open Field	flag_ck	Character	3
8	Edit Code	code_ck	Character	1
9	User Login	usr_ck	Character	256
10	Last Modified Date	cd_ck	Date	8		{R} mm/dd/yy Default **/**/**
11	Reason for Edit	reas_ck	Character	30
12	Transaction	trn_ck	Numeric	6
13	Terminal	trm_ck	Numeric	6

Table Name: conditions

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_cond	Numeric	6		{R}
3	Description	desc_cond	Character	25		{R}

Table Name: coupons

Actual/Virtual Coupon Promotions. Requires at least two tables (coupons and promos), the other tables may be required for some of the promotion types.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Coupon ID	id_cpn	Character	56		Coupon IDs can be up to 56 characters in length. Referenced in the promos table. This number is used as the barcode for coupons.
3	Description	desc_cpn	Character	50
4	Active Flag	act_cpn	Character	1
5	Creator	cusr_cpn	Character	256		Username of the creator
6	Create Date	cd_cpn	Date			{R} mm/dd/yy
7	Last Updated Date	lud_cpn	Date			{R} mm/dd/yy
8	Type	typ_cpn	Numeric	6		{R} Related back to supporting table (couptype): 0 = Actual 1 = Virtual
9	Customer Number	cust_cpn	Character	10		Placeholder for future use
10	Dollar Off Amount	doff_cpn	Numeric	11	2	Placeholder for future use
11	Single Use Indicator	singleuse_cpn	Character	1		Y = Single Use
12	Multi scan indicator	compound_cpn	Character	1		Y = The coupon can be scanned multiple times on one transaction

Table Name: promos

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion ID	id_pro	Numeric	6
3	Description	desc_pro	Character	50
4	Type	typ_pro	Numeric	6		Related back to supporting table (promtype): 1 = % off Promo 2 = $ off Promo 3 = Buy X Get X Free 4 = Buy X Get Y Free 5 = Buy X Get Y for % OFF 7 = Buy X Get X for % OFF 8 = % off Promo based on item price range 9 = % off Promo based on item quantity 10 = Buy X Get X for $ OFF 11 = Buy X Get Y for $ OFF 12 = Buy X Get X for a set price 13 = Buy X Get Y for a set price
5	Coupon ID	cpn_pro	Character	56		Maps back to id_cpn in the coupons table
6	Start Date	sd_pro	Date			{R} mm/dd/yy
7	End Date	ed_pro	Date			{R} mm/dd/yy
8	Active Flag	act_pro	Character	1		Y = active N = non-active
9	Last Update Date	lud_pro	Date			{R} mm/dd/yy
10	Create Date	cd_pro	Date			{R} mm/dd/yy
11	Open Field	cpnf_pro	Character	1		Placeholder for future use
12	Creator	cusr_pro	Character	256		Username of the creator
13	Price Selection Flag	pflg_pro	Character	1		R = Applies to full price items only M = Applies to markdown items only Blank = All (applies to both retail and markdown priced items)
14	Stackable Flag	stackable_pro	Character	1		Flag a coupon as stackable, meaning it can be stacked on top of other coupons/promotions. This is currently reserved for future use.
15	Single-use Flag	single_use_pro	Character	1		Flag a coupon as a single- use coupon. The individual coupons belonging to this coupon will be deactivated after a single-use.
16	Customer Association Flag	cust_associate_pro	Character	1		Flag a coupon to only be redeemable by the customer that it is associated with. This flag is only supported for single-use coupons.

Table Name: promosk (Promo Sku/Hierarchy)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion ID	pro_psk	Numeric	6		Maps back to id_pro in the promos table
3	Buy/Get Indicator	bgi_psk	Character	1		Indicates whether the row is a Buy Group (B) or a Get Group (G). For promos that don’t have Buy and Get Groups, the value should be blank.
4	Article	art_psk	Character	10		Lowest level of hierarchy, if populated it will override any other hierarchy listed.
5	Department	coll_psk	Character	2
6	Class	type_psk	Character	3
7	Sub-Class	pat_psk	Character	3
8	S-Class	scl_psk	Character	3
9	Attribute 1	cl1_psk	Character	3
10	Attribute 2	cl2_psk	Character	3
11	Vendor	ven_psk	Character	5
12	Style	styl_psk	Character	20
13	Color	colr_psk	Character	6
14	Size	siz_psk	Character	8
15	Season	sea_psk	Character	3
16	Percentage Off	poff_psk	Numeric	11	2	Format 0.00
17	Dollar Amount Off	doff_psk	Numeric	11	2	Format 0.00
18	Quantity of Buy Item	quan_psk	Numeric	6
19	Discount Type (% or $)	pdi_psk	Character	1		P = Percent D = Dollar
20	Inclusive Flag	inclusive_psk	Character	1		Y = Inclusive N = Exclusive This flag is for the Buy Group. Inclusive means the Buy Quantity has to be satisfied by any of the Buy Groups. Exclusive means the Quantity of each Buy Group has to be satisfied.
21	Quantity of Get Item	get_quan_psk	Numeric	6
22	Price of Get Item	get_price_psk	Numeric	11	2
23	Get group priority flag	get_priority_psk	Character	1		Flag to allow the Get group to satisfy the buy/get requirement if the Buy group does not. This is for BXGY coupons only.

Table Name: promost (Promo Store)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	ID	id_prs	Numeric	6		Placeholder for future use
3	Client	clnt_prs	Numeric	6		Store type (st_clnt from the stores table), i.e. 100 for retail, 101 for outlet. If the promo is not at the client level, default to -1.
4	Store	stc_prs	Numeric	6		If the promo is not at the store level, default to -1.
5	Promotion ID	pro_prs	Numeric	6		Maps back to id_pro in the promos table

Table Name: promrule (Promo Rules)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	ID	id_prr	Numeric	6		Placeholder for future use
3	Promotion ID	pro_prr	Numeric	6		Maps back to id_pro in the promos table
4	Promo Type	typ_prr	Numeric	6		1 = % off Promo 2 = $ off Promo
5	From Amount	famt_prr	Numeric	11	2
6	To Amount	tamt_prr	Numeric	11	2
7	Percentage Off	poff_prr	Numeric	11	2
8	Dollar Amount Off	doff_prr	Numeric	11	2
9	Discount Type (% or $)	pdi_prr	Character	1		P = Percent Off D = Dollar Off
10	Each Flag	each_prr	Character	1		Y = Discount applies to each item for $ off N = Discount applies to the total of the transaction for $ off

Table Name: couptype (Coupon Type)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Type #	typ_cpt	Numeric	6		0 = Actual 1 = Virtual
3	Description	desc_cpt	Character	50

Table Name: promtype (Promo Type)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Type #	typ_prt	Numeric	6		1 = % off promo 2 = $ off Promo 3 = Buy X Get X free 4 = Buy X Get Y Free 5 = Buy X Get Y for % OFF 7 = Buy X Get X for % OFF 8 = % off Promo based on item price range 9 = % off Promo based on item quantity 10 = Buy X Get X for $ OFF 11 = Buy X Get Y for $ OFF 12 = Buy X Get X for a set price 13 = Buy X Get Y for a set price
3	Description	desc_prt	Character	50

Table Name: coup_single_use (Single-use Individual Coupon IDs)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Single-use coupon ID	id_csu	Character	256		Unique coupon ID. This number is used as the barcode for coupons.
3	Active Code	act_csu	Character	1		Will only work if the code is not ‘N’. Gets deactivated upon successful redemption.
4	Customer Number	cust_csu	Character	10		Customer number associated with this coupon. The coupon can only be redeemed by the customer to whom it is associated. This will be ignored unless promos.cust_associate_pro is set to ‘Y’.
5	Parent Coupon ID	parented_csu	Character	56		Reference to coupons.id_cpn.
6	Load Batch ID	batchid_csu	Character	10		Batch ID of coupon list load
7	Load Batch Date	date_csu	Date			Date of coupon list load
8	User	creator_csu	Character	45

Table Name: coup_single_use_arc (Archive for Expired Single-use Coupons)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Single-use coupon ID	id_csua	Character	256		Unique coupon ID. This number is used as the barcode for coupons.
3	Active Code	act_csua	Character	1		Will only work if the code is not ‘N’. Gets deactivated upon successful redemption
4	Customer Number	cust_csua	Character	10		Customer number associated with this coupon. The coupon can only be redeemed by the customer to whom it is associated. This will be ignored unless promos.cust_associate_pro is set to ‘Y’.
5	Parent Coupon ID	parented_csua	Character	10		Reference to coupons.id_cpn.
6	Expiration date of the coupon	promos_expire_date	Date			This is the expiration date from the promos table. It is stored in this table as a reference for the purging process.

Table Name: cust (Customer)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Customer #	num_cp	Character	10		{R} Must be full numeric 10 bytes
3	First Name	fn_cp	Character	20
4	Middle initial	mi_cp	Character	1
5	Last Name	ln_cp	Character	30
6	Address1	add_cp	Character	40
7	Address2	add1_cp	Character	40
8	City	ct_cp	Character	40
9	State	st_cp	Character	40
10	Zip	zip_cp	Character	20
11	Home Phone	tel1_cp	Character	16
12	Business Phone	tel2_cp	Character	16
13	On Mailing List	onlst_cp	Character	1		Y/N Default is Y
14	Home Store	stc_cp	Numeric	6		Default is 0 -Auto populates store level- required with integration
15	Gender	sex_cp	Character	1		M/F/U -Auto populates for CRM clients
16	Marital Status	mart_cp	Character	1		M/S (Married/Single)
17	Occupation	occ_cp	Character	20
18	Anniversary Date	anv_cp	Date	8		mm/dd/yy Default **/**/**
19	Birthday	birth_cp	Date	8		mm/dd/yy Default **/**/**
20	Spouse’s Name	snm_cp	Character	15
21	Spouse’s Birthday	sbd_cp	Dater	8		mm/dd/yy Default **/**/**
22	Open Field #1	p01_cp	Character	8		VALUE-NUMERIC
23	Open Field #2	p02_cp	Character	8		VALUE-NUMERIC
24	Open Field #3	p03_cp	Character	8		VALUE-NUMERIC
25	Open Field #4	p04_cp	Character	8		VALUE-NUMERIC
26	Open Field #5	p05_cp	Character	8		CODES-Drop Down
27	Open Field #6	p06_cp	Character	8		CODES-Drop Down
28	Open Field #7	p07_cp	Character	8		CODES-Drop Down
29	Open Field #8	p08_cp	Character	8		CODES-Drop Down
30	Open Field #9	p09_cp	Character	8		FLAG-Y/N
31	Open Field #10	p10_cp	Character	8		FLAG-Y/N
32	Open Field #11	p11_cp	Character	8		FLAG-Y/N
33	Open Field #12	p12_cp	Character	8		FLAG-Y/N
34	Loyalty Enrolled	p13_cp	Character	8		FLAG-Y/N
35	Open Field #14	p14_cp	Character	8		FLAG-Y/N
36	Open Field #15	p15_cp	Character	8
37	Open Field #16	p16_cp	Character	8
38	Modify Date	cd_cp	Date	8		mm/dd/yy Defaults to today’s date
39	Customer Status	amd_cp	Character	1		A = active, C= modify, D = deactive Default is A
40	Home Store #2	hs_cp	Numeric	4		Default is 0
41	Open Field #17	p17_cp	Character	5		FLAG-Y/N
42	Open Field #18	p18_cp	Character	3		FLAG-Y/N
43	CRM DQS	post_cp	Character	10		CRM-used for DQS
44	CRM Open	sfx_cp	Character	5		CRM-Zip Radius
45	Active	act_cp	Character	1		Default = A
46	Email Address	email_cp	Text	65,535
47	Open	nom_cp	Numeric	6		0
48	Open for Loyalty 1	loy1_cp	Date	8		mm/dd/yy (Default is **/**/**)
49	Open for Loyalty 2	loy2_cp	Date	8		mm/dd/yy (Default is **/**/**)
50	Country	cn_cp	Character	3
51	Telephone #3	tel3_cp	Character	10
52	Telephone #4	tel4_cp	Character	10
53	Open	acct_cp	Character	40
54	Open	act2_cp	Character	1
55	Valid Mailing Address	mail_cp	Character	1		Y/N
56	Employee #	emp_cp	Numeric	6		Employee who entered the record
57	Create Date	md_cp	Date	8		mm/dd/yy (Default is **/**/**)
58	Deactivated Date	dd_cp	Date	8		mm/dd/yy (Default is **/**/**)
59	Open field	cct_cp	Character	2
60	Open field	ccn_cp	Character	20
61	Credit Card Expiration	ccexp_cp	Character	4		mmyy
62	Credit Card Validation	ccvv_cp	Numeric	6
63	Rental Flag	rent_cp	Character	1		Y/N (customer can rent)
64	Open Date	scd_cp	Date	8		mm/dd/yy (Default is **/**/**)
65	State ID Number	id_cp	Character	20
66	Rental Customer Flag	mr_cp	Character	1		Y/N (Y=customer is a part of the rental system)
67	Late Fee Flag	lf_cp	Character	1		Y/N
68	Cell Phone	cell_cp	Character	10
69	Address and Zip Code Validation Flag	psf_cp	Character	3		Address/zip code used for the KWI CRM group – data has been validated Valid=PS2/Invalid=PS3
70	Phone Validation Flag	pnf_cp	Character	3		Phone number used for the KWI CRM group – data has been validated Valid=PN2 Invalid=PN3
71	Email Validation Flag	emf_cp	Character	3		Email address used for the KWI CRM group – data has been validated Valid=EM2 Invalid=EM3
72	Alias Cust #	new_cp	Character	10		If the customer # was merged, the state of the merged customer #
73	Email Opt In/out	opt_cp	Character	1		Email Opt-In Y(1)= Want email mailings N(2)=Do not want email mailings 0=null
74	CRM Loyalty	point_cp	Numeric	8		Client defined field-Only Required for CRM loyalty
75	CRM Field	scf_cp	Character	3		Used by KWI CRM group only
76	CRM DQS	qas_cp	Character	16		Used by KWI CRM group only
77	CRM Loyalty	loysd_cp	Date	8		Used by KWI CRM group only mm/dd/yy (Default is **/**/**)
78	CRM Field	geost_cp	Numeric	6		Used by KWI CRM group only
79	CRM Field	frsst_cp	Numeric	6		Used by KWI CRM group only
80	CRM Field	carr_cp	Character	5		Used by KWI CRM group only
81	CRM Loyalty	elite_cp	Date	8		mm/dd/yy (Default is **/**/**)
82	CRM Field	pre_cp	Character	6		Used by KWI CRM group only
83	CRM DQS	qasname_cp	Character	16		Used by KWI CRM group only
84	CRM DQS	qasphone_cp	Character	16		Used by KWI CRM group only
85	CRM DQS	qasemail_cp	Character	16		Used by KWI CRM group only
86	Address Opt In/Out	addopt_cp	Character	1		Address Opt-In Y(1)= Want mailings N(2)=Do not want mailings 0=null
87	Phone Opt In/Out	telopt_cp	Character	1		Phone Opt-In Y(1)= Want telecommunications N(2)=Do not want telecommunications 0=null
88	CRM Field	emopt_cp	Character	1		Field No longer used
89	CRM Field	party_cp	Date	8		Used by KWI CRM group only
90	CRM Field	mcc_cp	Character	64		RESERVED FIELD DO NOT REUSE
91	CRM Field	prospect_cp	Character	1		Used by KWI CRM group only
92	CRM Loyalty	pointsbal_cp	Numeric	8		Used by KWI CRM group only
93	CRM Field	oktomerge_cp	Character	1		Used by KWI CRM group only
94	Business Name	busnam_cp	Character	50		Client defined
95	CRM Field	emailopenflag_cp	Character	1		Y=Open Email N= Did Not Open Email
96	CRM Field	emailopendate_cp	Date	8		mm/dd/yy (Default is **/**/**)
97	CRM Field	emailclickflag_cp	Character	1		Y=Clicked Thru Email N=Did Not Click Thru Email
98	CRM Field	emailclickdate_cp	Date	8		mm/dd/yy (Default is **/**/**)
99	CRM Field	emailpurchflag_cp	Character	1		Y=Made Purchase N=Did Not Purchase
100	CRM Field	emailpurchdate_cp	Date	8		mm/dd/yy (Default is **/**/**)
101	CRM Field	affiliatesubscribeflag _cp	Character	1		Y=Affiliate Subscriber N= Not Affiliate Subscriber
102	CRM Field	affiliatesubscribedat e_cp	Date	8		mm/dd/yy default **/**/**
103	CRM Field	webpurchaseflag_cp	Character	1		Y=Made Web Purchase N=Did Not Make Web Purchase
104	CRM Field	webpurchasedate_c p	Date	8		mm/dd/yy (Default is **/**/**)
105	Clienteling Field	gbid_cp	Numeric	10
106	CRM Field	custcreatesource_cp	Character	20
107	CRM Field	emailcreatesource_c p	Character	20
108	Email Create Date	emailcreatedate_cp	Date	8		mm/dd/yy Auto populated once email field is populated
109	Cell Phone Opt In/Out	smsopt_cp	Character	1		Cell Phone Opt-In Y(1)= Want telecommunications? N(2)=Do not want telecommunications 0=null
110	Cell Validation Flag	cellf_cp	Character	3		Cell Phone for KWI CRM group – data has been validated Valid=CP2 Invalid=CP3
111	CRM Field	cmts_cp	Character	254		Notes field
112	CRM Field	imp_CT_cp	Character	8		Canadian Email Implied Consent- Inquiry/Purchase/Expired
113	Email Opt Date	emopt_dt_cp	Date	8		mm/dd/yy (Default is **/**/**)
114	Email Opt Out Date	emoptout_dt_cp	Date	8		mm/dd/yy (Default is **/**/**)
115	External Customer ID	ext_num_cp	Character	30		External Customer ID
116	External Master Customer ID	ext_new_cp	Character	30		For records merged externally, represents the go forward external Master record ID.
117	Email Opt Out Date	ext_master_cp	Character	1		Y indicates the record is the external Master record. N indicates a non-Master record.
118	Customer Group Row ID	custgrp_cp	Number	6		The customer group Row ID. When creating a customer group, check the customer maintenance screen to verify the correct Row ID

Table Name: attrib (Attribute Parent Table)

Customer attributes consist of a header and detail record.

Example: If the system has one attribute called (VIP – Notes). The parent table (attrib) will have one record, the Child table (attr) will have one record for each customer who has a VIP – note.

If the client sets-up multiple attributes in the Attrib table by creating a Type has Type Code “CN” populated        in both tables, this means that the client has a value of “type” First Child Name (CN) populated with the first child’s name in the attr table in the comm_at field.

Set up in Back Office under Attribute Parent Table

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Type code	typ_att	Character	10
3	Type description	desc_att	Character	50
4	Parent or child flag	vt_att	Character	1		“V” = Parent record “T” = Child record
5	Group code	grp_att	Character	10

Table Name: attr (Attribute Child Table)

The table is populated when a customer is associated to the attribute at POS or thru a file feed. The two tables tie together by the type code (typ_at).

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Customer #	cust_at	Character	10
3	Type code	typ_at	Character	10		Relates to attrib  type_att
4	Value of attribute	val_at	Character	10
5	Comment	comm_at	Character	50
6	Create date – (Open)	ym_at	Date	8		{R} mm/dd/yy default **/**/**
7	Active flag	act_at	Character	1
8	Last modified date	cd_at	Date	8		{R} mm/dd/yy default **/**/**

Table Name: segment (Segment Parent Table)

Set up in Back Office under Segment Parent Table. There are 16 segment fields that correspond to the open fields in the cust table. These values can be Y/N selections (6 available), drop downs (4 available), dates field (2 available) or numeric value fields (4 available) that can be selected from set-up at POS.

The two tables link via the child table with the parent table, by giving the field name given in the parent table reference the name in the child table. The child table then gives the field name a value. 

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Field Name	fld_sgm	Character	3
3	Field Description	dscr_sgm	Character	20
4	POS Field	pos_field_sgm	Character	50

Table Name: seg (Segment Child Table)

Set up in Back Office under Segment Child Table

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Field Name	fld_seg	Character	3		Relates to segment fld_segm
3	Value	val_seg	Character	20
4	Value Description	dscr_seg	Character	20

Table Name: points (Customer Points)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Customer #	num_oi	Character	10
3	Date	ym_oi	Date	8		{R} mm/dd/yy default **/**/**
4	Points	pts_oi	Numeric	8
5	User ID	usrid_oi	Character	256		User or system who updated
6	Reason code / Comment	reas_oi	Character	40		Reason for updating

Table Name: deleted_sku (Deleted SKU)

Logs the history of deleted, merged, or replaced articles.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Old UPC/Article #	oldsku_dlsk	Character	15		The original SKU that was merged to the new SKU
3	New UPC/Article #	newsku_dlsk	Character	15		The new SKU to which the original SKU was merged
4	Date/Time	dt_dlsk	Timestam p			The date/time of the merge
5	Process	process_dlsk	Character	64		The process that performed the merge
6	User	user_dlsk	Character	255		The user who ran the merge

Table Name: discount

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_dis	Numeric	6		KWI six-digit store number – matches back to the Stores table
3	Date	ym_dis	Date			{R} mm/dd/yy
4	UPC	art_dis	Character	10		{R} UPC #
5	Reason Code	reas_dis	Numeric	6		KWI discount code
6	Discount Amount	amt_dis	Numeric	13	2
7	Time	tm_dis	Time			{R} hh:mm (Default as 12:01) Military Time
8	Transaction Number	trn_dis	Numeric	6		The transaction # the sales record is associated to. There is a unique transaction number for each sale.
9	Terminal – Register Number	trm_dis	Numeric	6		The register terminal # the sales record is associated to. Each register has a unique number.
10	Discount ID	idnm_dis	Character	20		Open field that is sometimes used to identify discounts (i.e. Price Match)
11	Comment	comm_dis	Character	60		Open field
12	Open Field	tbl_row_ref_dis	Numeric	11		Not Used
13	Open Field	tbl_typ_ref_dis	Character	1		Not Used
14	Open Field	tx_dis	Numeric	8	3	Not Used
15	Discount Type	type_dis	Numeric	1		0 = regular discount 1 = price override
16	Discount Type	pctype_dis	Character	45		Price Override, Transaction % Discount, Transaction $ Discount, Item % Discount, Item $ Discount
17	Discount Rate	pcrate_dis	Numeric	13	2	The actual discount rate applied manually as a percent or a dollar (based on pctype_dis)

Table Name: distr (Distributions Warehouse)

(Used for EDI 856 – advanced shipping notification. Warehouse Distribution header)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Distribution Document #	num_di	Numeric	6		{R}
3	Store #	stc_di	Numeric	6		{R}
4	Date	ym_di	Date	8		{R} mm/dd/yy
5	Bill of Lading #	bol_di	Character	17
6	PO #	po_di	Character	10
7	UCC 128 Label #	ucc_di	Character	20
8	Active Flag	act_di	Character	1
9	Invoice #	inv_di	Character	20
10	Warehouse #	whc_di	Numeric	6
11	Open Field	seal_di	Numeric	6
12	Weight	wgt_di	Numeric	6
13	Open Field	bon_di	Numeric	9
14	Original Distribution #	orig_di	Numeric	9
15	Login ID	log_di	Character	40
16	Packed By	pkdby_di	Character	20
17	Delivery Date	delym_di	Date	8		{R} mm/dd/yy Default **/**/**
18	Comments 1	cmnt1_di	Character	55
19	Comments 2	cmnt2_di	Character	55
20	Comments 3	cmnt3_di	Character	55
21	Master Carton #	master_di	Character	25
22	Transaction	trn_di	Numeric	6

Table Name: dis (Warehouse Distribution Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Article Number	art_ds	Character	10
3	Quantity	quan_ds	Numeric	6		Default 0
4	Distribution Document Number	num_ds	Numeric	6
5	Web Order Line #	inum_ds	Numeric	9		Default 0

Table Name: employee

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Employee #	num_em	Numeric	6
3	Employee Name	nam_em	Character	40
4	Employee Department	dpt_em	Character	3
5	Rate Code	code_em	Character	3
6	Hourly Rate	hrt_em	Numeric	11	2	{R} 999999.99 Default 0.00
7	Payroll Period Rate	prt_em	Numeric	11	2	{R} 0.00
8	Commission %	com1_em	Numeric	11	2	{R} 0.00
9	Key Money	com2_em	Numeric	11	2	{R} 0.00
10	Store	stc_em	Numeric	6		{R} Must be valid store number
11	Last Updated	ed_em	Date	8		{R} mm/dd/yy
12	Threshold amount	avgs_em	Numeric	11	2	{R} 0.00
13	Social Security	ss_em	Numeric	9		{R} default = 0
14	Address line 1	add1_em	Character	30
15	Address line 2	add2_em	Character	30
16	City	ct_em	Character	2
17	State	st_em	Character	20
18	Zip	zip_em	Character	10
19	Employee status	ms_em	Character	1
20	Open Field #1	dep_em	Character	2
21	Open Field #2	ben_em	Character	2
22	Hired Date	hire_em	Date	8		{R} mm/dd/yy Default **/**/**
23	Open Field #3	pos_em	Numeric	2		{R} default = 0
24	Open Field #4	posit_em	Character	10
25	Open Field #5	hdt_em	Date	8		{R} mm/dd/yy Default **/**/**
26	Password	pass_em	Character	64
27	Security level	seclv_em	Numeric	2
28	Open Field #6	freq_em	Character	3
29	Open Field #7	idbar_em	Character	8
30	Open Field #8	act_em	Character	1		Y / N
31	MPOS Security Group	group_em	Character	10
32	Language	lang_em	Numeric	6		{R} default = 1 – For KWI Internal usage only
33	Store Group	stg_em	Numeric	5		{R} default = 0 – For KWI Internal usage only
34	Email	email_em	Character	255		{R} default = 0 – For KWI Internal usage only
35	Encrypted Password	encpass_em	Character	265
36	Active code	active_em	Character	1

Table Name: fop (Form of Payment)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_fp	Numeric	6		KWI six-digit store number – matches back to the Stores tbl
3	Date	ym_fp	Date	8		{R} mm/dd/yy
4	Price	pr_fp	Numeric	11	2	The total value of that form of payment – inclusive. For sales records, the value should always be a negative (-) number. For return records, the value should always be a positive number.
5	Open Field	acct_fp	Character	70
6	Tender Type	code_fp	Character	1		S = Sale R = Return
7	Credit Card Type	num_fp	Numeric	2		1 = Cash 2 = Check 4 = Visa 5 = M/C 6 = Amex 7 = Discover 8 = JCB 9 = Diners 11 = Trade-In 12 = ATM/Debit 13 = GC Issue/Redeemed – Traditional Registers Only 14 = GC Issued – Traditional Registers Only 15 = GC Redeemed – Traditional Registers Only 16 = Store Credit Issued – positive value 17 – Store Credit Redeemed – negative value 18 = Mall GC 19 = House Account 20 = Coupons 21 = Travelers Check 22 = Foreign Currency 23 = Mail Check 24 = GC Issued/Redeemed – Cloud 9 POS only 25 = Comp 26 = City Ledger 27 = Master Ledger 28 = Foreign Travelers Check   29 = Foreign Gift Card 32 = Synchrony 33 = Theory Edit 43 = VIP Event 60 = Shopatron 61 = Bill Me Later 62 = Store Manager Fund 63 = Visa/MC Combo 64 = Square 65 = Market Place 69 = Store Credit 70 = PayPal 71 = Wire Transfer 72 = Amazon Order 73 = Amazon Pay 74 = Affirm 80 = Borderfree 81 = ApplePay 101 = Manufacturer Coupon 102 = AfterPay 103 = WeChat 104 = AliPay 105 = China Union Pay 106 = Raymark Gift Card 107 = Promo Tender 108 = Alliance 109 = Alliance Pay 110 = Klarna 111 = Postmates 112 = Quadpay 113 = Sezzle 114 = Venmo 115 = Magic Band 116 = Disney Rewards 117 = Disney Gift Card 118 = Department Charge 119 = Doordash 120 = PO 121 = Globale 122 = Bolt 123 = [Client Specific] Loyalty 124 = SPREE
8	Transaction #	trn_fp	Numeric	6		The transaction number to which the tender is associated.
9	Terminal – Register #	trm_fp	Numeric	6		The register terminal # to which the tender is associated.
10	Time	tm_fp	Time	5		hh:mm
11	Expiration Date	expdt_fp	Date	8		mm/dd/yy – Credit card expiration date
12	Keyed or Swipe	kyind_fp	Character	1		Y or N – credit card was manually keyed in (N) or swiped/inserted (Y)
13	Authorization Code	acode_fp	Character	12		The credit card authorization code.
14	Sequence Number	seqn_fp	Numeric	10		KWI always defaults to 999
15	Customer Name	ccname_fp	auth_mo de_fp	40		The customer name on the credit card.
16	Web order Line #	inum_fp	Numeric	9		Stores the web order number line number if the client is utilizing an eCommerce interface.
17	EMV Payment Type	payment_type_fp	Character	24
18	EMV Requirement	auth_mode_fp	Character	60		Authorization mode
19	EMV Requirement	aid_fp	Character	60		Application Identifier
20	EMV Requirement	tvr_fp	Character	60		Terminal Verification Results
21	EMV Requirement	iad_fp	Character	60		Issuer Application Data
22	EMV Requirement	tsi_fp	Character	60		Transaction Status Indicator
23	EMV Requirement	arc_fp	Character	60		Application Response Code
24	EMV Requirement	cvm_fp	Character	60		Cardholder Verification Method (i.e.: Verified by PIN, Sig, or No Sig)
25	Auth Type	auth_type_fp	Character	10		Tracks the authorization type. For example, if the card was swiped/keyed or was recognized via an account token.
26	Wallet Type	wallet_fp	Character	10		Wallet type identifier Example: ApplePay, GooglePay, Paypal, WeChat
27	Employee #	emp_fp	Numeric	6
28	Customer #	cust_fp	Character	10
29	Transaction Type	Transtype_fp	Character	1		M = Media Exchange. Else blank
30	Create Date	created_fp	Timestamp

Table Name: gift

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_gc	Numeric	6		KWI six-digit store number – matches back to the Stores table
3	Date	ym_gc	Date	8		{R} mm/dd/yy
4	Amount	pr_gc	Numeric	13	2	Either the issued or redeemed amount of the Gift Card and or Gift Certificate and or Store Credit.
5	Gift Certificate #	num_gc	Character	16		Gift Certificate #
6	Code	code_gc	Character	2		01 = Gift Card Redemption (shows as a negative value) 02 = Gift Card Issued (shows as a positive value) 03 = Store Credit (Positive value = Store Credit issued, Negative value = Store Credit redeemed)
7	Employee #	emp_gc	Numeric	6		The employee number who either issued or redeemed the Gift Card and or Gift Certificate and or Store Credit.
8	Time	tm_gc	Time	5		hh:mm
9	Transaction #	trn_gc	Numeric	6		The transaction number that issued or redeemed the Gift Card or Gift Certificate or Store Credit is associated to.
10	Terminal #	trm_gc	Numeric	6		The register terminal # the issued or redeemed the Gift Card or Gift Certificate or Store Credit associated to.
11	Customer #	cust_gc	Character	10		The Customer # the issued or redeemed the Gift Card and or Gift Certificate and or Store Credit associated to.
12	Merge Date	pdt_gc	Date	8		mm/dd/yy – KWI CRM only feature – not relevant unless you are a KWI CRM customer (add on services) {R} Default **/**/**
13	Tender Type	tend_gc	Numeric	6		Defaults: 1 = Cash 2 = Check 4 = Visa 5 = M/C 6 = Amex 7 = Discover 8 = JCB 9 = Diners 11 = Trade-In 12 = ATM/Debit 13 = GC Issue/Redeemed – Traditional Registers Only 14 = GC Issued – Traditional Registers Only 15 = GC Redeemed – Traditional Registers Only 16 = Store Credit Issued – positive value 17 – Store Credit Redeemed – negative value 18 = Mall GC 19 = House Account 20 = Coupons 21 = Travelers Check 22 = Foreign Currency 23 = Mail Check 24 = GC Issued/Redeemed – Cloud 9 POS only 25 = Comp 26 = City Ledger 27 = Master Ledger 28 = Foreign Travelers Check 29 = Foreign Gift Card 32 = Synchrony 33 = Theory Edit 43 = VIP Event 60 = Shopatron 61 = Bill Me Later 62 = Store Manager Fund 63 = Visa/MC Combo 64 = Square 65 = Market Place 69 = Store Credit 70 = PayPal 71 = Wire Transfer 72 = Amazon Order 73 = Amazon Pay 74 = Affirm 80 = Borderfree 81 = ApplePay 101 = Manufacturer Coupon 102 AfterPay 103 = WeChat 104 = AliPay 105 = China Union Pay 106 = Raymark Gift Card 107 = Promo Tender 108 = Alliance 109 = Alliance Pay 110 = Klarna
14	Keyed / Swiped	kyind_gc	Character	1		Y or N
15	Web Order Line #	inum_gc	Numeric	9
16	External Ref ID	externalid_gc	Character	25
17	Line-Item ID	lineItemId_gc	Character	265		Unique line-item ID, used for CSV
18	Link to mPOS Item	mpos_rowid_gc	Numeric	11		Rowid link to the mpos item table
19	Link to mPOS Transaction	trans_rowid_gc	Numeric	11		Rowid link to the mpos trans table
20	Record Create Date/time	created_gc	Timestamp

Table Name: regis (Gift Registry)

Field #	Field Name	KWI Table Field Name	Type	Length	Decimal	Instructions
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Registry Number	num_rs	Numeric	6		{R}
3	Store Number	stc_rs	Numeric	6		{R}
4	Customer Number	cust_rs	Character	10		{R}
5	First Name (Male)	fnm_rs	Character	20		{R}
6	First Name (Female)	fnf_rs	Character	20		{R}
7	Last Name	ln_rs	Character	30		{R}
8	Address	add_rs	Character	30		{R}
9	City	ct_rs	Character	20		{R}
10	State	st_rs	Character	2		{R}
11	Zip Code	zip_rs	Character	10		{R}
12	Telephone Number	tel_rs	Character	10
13	Gender	sex_rs	Character	1
14	Comments 1	comm_rs	Character	70
15	Date of Birth	dob_rs	Date	8
16	Shower Date	shwr_rs	Date	8
17	Comments 2	comm1_rs	Character	70
18	Comments 3	comm2_rs	Character	70
19	Comments 4	comm3_rs	Character	70
20	Comments 5	comm4_rs	Character	70
21	Comments 6	comm5_rs	Character	70
22	Comments 7	comm6_rs	Character	70
23	Comments 8	comm7_rs	Character	70
24	Comments 9	comm8_rs	Character	70
25	Comments 10	comm9_rs	Character	70
26	Employee Number	emp_rs	Numeric	6
27	Active Code	act_rs	Character	1		{R}

Table Name: reg (Gift Registry Details)

Field #	Field Name	KWI Table Field Name	Type	Length	Decimal	Description
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Registry ID Number	num_rg	Numeric	6		{R}
3	UPC Number (Article)	art_rg	Character	10		{R}
4	Quantity	quan_rg	Numeric	6
5	Available	avail_rg	Character	1
6		qp_rg	Numeric	6
7	Store Number	stc_rg	Numeric	6
8	Date	ym_rg	Date
9	Time	tm_rg	Time
10	Transaction Number	trn_rg	Numeric	6
11	Terminal Number	trm_rg	Numeric	6
12	Last Name	ln_rg	Character	25
13	First Name	fn_rg	Character	25

Table Name: chist (Cost History)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Client number	cl_ch	Numeric	6
3	UPC	art_ch	Character	10
4	Date of change	ym_ch	Date	8		{R} mm/dd/yy
5	New cost price	pr_ch	Numeric	13	2
6	Database Field Updated in Item Master	nm_ch	Character	3		KWI item master field name that was updated.

Table Name: rhist (Retail History)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Client number	cl_rh	Numeric	6
3	UPC	art_rh	Character	10
4	Date of change	ym_rh	Date	8		{R} mm/dd/yy
5	New retail price	rp_rh	Numeric	13	2
6	Markdown price	mp_rh	Numeric	13	2
7	Active Code	act_rh	Character	1		Not Currently used in KWI
8	Description	desc_rh	Character	25		Not Currently used in KWI
9	Date	dt2_rh	Date	8		Not Currently used in KWI

Table Name: inv_threshold (Inventory Thresholds)

This table defines the minimum inventory thresholds by store, client, and hierarchy.

Field #	Field Name	KWI Field Name	Type	Length	Decimal		Description / Format
1	Rowid	Rowid	Numeric	9			KWI Internal (auto-increments unique field)
2	Store #	stc_ith	Numeric	6			Required if setting thresholds by store. Client 6-digit KWI store number.
3	Client	clnt_ith	Numeric	6			Required if setting thresholds by client. Client 3-digit client designation (store type).
4	Article #	art_ith	Character	10			Required if setting thresholds by article. Client 10-digit article number.
5	Department Code	coll_ith	Character	2			KWI Department Code of up to 2 characters.
6	Class Code	type_ith	Character	3			KWI Class Code of up to 3 characters.
7	Sub-Class Code	pat_ith	Character	3			KWI Sub-Class Code of up to 3 characters.
8	Sub- Department Code	scl_ith	Character	3			KWI Sub-Department Code of up to 3 characters.
9	Level 5 Code	cl1_ith	Character	3			KWI Attribute 1 (Level 5 Code) of up to 3 characters.
10	Level 6 Code	cl2_ith	Character	3			KWI Attribute 2 (Level 6 Code) of up to 3 characters.
11	Level 7 Code	cl3_ith	Character	3			KWI Attribute 3 (Level 7 Code) of up to 3 characters.
12	Vendor #	ven_ith	Character	5			KWI Vendor Code of up to 5 characters.
13	Vendor Style #	styl_ith	Character	20			Item Style number.
14	Color Code	colr_ith	Character	6			KWI Color Code of up to 6 characters.
15	Size	Siz_ith	Character	8		Item Size.
16	Quantity	qty_ith	Character	6		Threshold Quantity.

Table Name: invloads (Inventory Load Information)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_br	Numeric	6
3	Load Date	loaddt_vl	Date
4	Load Time	loadtm_vl	Time

Table Name: invoice (Invoice Header)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Invoice Number	num_in	Numeric	6
3	Store	stc_in	Numeric	6
4	Date	ym_in	Date	8		{R} mm/dd/yy
5	Vendor Invoice Number	iv_in	Character	22
6	KWI PO Number	po_in	Character	8
7	Vendor Number	vend_in	Character	30
8	Invoice Total	tot_in	Numeric	13	2
9	Freight Value	frt_in	Numeric	13	2
10	Total Quantity	acct_in	Character	8
11	Status	stat_in	Character	1		U = unpaid
12	Date Paid	ympd_in	Date	8		{R} mm/dd/yy
13	Description	memo_in	Character	32
14	Due Date	due_in	Date
15	Actual Total Value	atot_in	Numeric	13	2
16	Actual Freight	afrt_in	Numeric	13	2
17	Customs Value	cstm_in	Numeric	13	2
18	Actual Customs Value	acstm_in	Numeric	13	2
19	Duty Value	duty_in	Numeric	13	2
20	Actual Duty Value	aduty_in	Numeric	13	2
21	Insurance	insr_in	Numeric	13	2
22	Actual Insurance	ainsr_in	Numeric	13	2
23	VAT Value	vat_in	Numeric	13	2
24	Actual VAT value	avat_in	Numeric	13	2
25	Modified/actual Merchandise Cost	mcost_in	Numeric	13	2
26	Allowance or Charge Indicator	SAC01_in	Character	1		Code which indicates an allowance or charge for the service specified. C = Charge
27	Tax Amount	tax_in	Numeric	13	2
28	Brokerage Fee	brokerage_in	Numeric	13	2
29	Credit Amount	credit_in	Numeric	13	2
30	Discount Amount	discount_in	Numeric	13	2
31	Allowance or Charge Method of Handling Code	SAC12meth_in	Character	3		Code indicating method of handling for an allowance or charge 02= Off Invoice
32	Terms Type Code	ITD01code_in	Character	3		Code identifying type of payment terms
33	Terms Basis Date Code	ITD02date_in	Date			Code identifying the beginning of the terms period
34	Terms Discount Percent	ITD03pct_in	Character	7		Terms discount percentage, expressed as a percent, available to the purchaser if an invoice is paid on or before the Terms Discount Due Date. The percent is sent with a decimal point only when needed, e.g., 10.5% is sent as “10.5”, and 2% is sent as “2”.
35	Terms Discount Days Due	ITD05days_in	Numeric	3		Number of days in the terms discount period by which payment is due if terms discount is earned
36	Terms Net Due Date	ITD06date_in	Date			Date when total invoice amount becomes due expressed in format CCYYMMDD
37	Terms Net Days	ITD07days_in	Numeric	3		Number of days until total invoice amount is due (discount not applicable)
38	Terms Discount Amount	ITD08amt_in	Numeric	13	2	Total amount of terms discount
39	Terms Deferred Due Date	ITD09date_in	Date			Date deferred payment or percent of invoice payable is due expressed in format CCYYMMDD
40	Deferred Amount Due	ITD10amt_in	Numeric	13	2	Deferred amount due for payment
41	Percent of Invoice Payable	ITD11amt_in	Numeric	13	2	Amount of invoice payable expressed in percent
42	Description	ITD12dscr_in	Character	80		A free-form description to clarify the related data elements and their content
43	Amount	tds01amt_in	Numeric	13	2	Monetary amount

Table Name: ivc (Invoice Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Article Number	art_iv	Character	10
3	Quantity	quan_iv	Numeric	9
4	Invoice Number	num_iv	Numeric	6
5	Purchase Order Cost	pr_iv	Numeric	13	2
6	Duty Value	duty_iv	Numeric	13	2
7	Freight Value	frt_iv	Numeric	13	2
8	Customs Value	cstm_iv	Numeric	13	2
9	Insurance Value	insr_iv	Numeric	13	2
10	Vat Value	vat_iv	Numeric	13	2

Table Name: coll (Department)

The color table is used to create new departments or update current departments.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Department Code	collc	Character	2		{R}
3	Department Name	colln	Character	20		{R}
4	Open Field	collper	Numeric	9	2

Table Name: types (Class)

The types table is used to create new classes or update current classes.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Class Code	typec	Character	3
3	Class Name	type	Character	20

Table Name: patterns (Sub-class)

The patterns table is used to create new sub classes or update current sub classes.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Sub Class Code	patc	Character	3		{R}
3	Sub Class Name	pat	Character	20		{R}

Table Name: sclass (Sub-department, Level 4)

The sclass table is used to create new sub departments or update current sub departments.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Level 4 #	scl	Character	3		{R}
3	Level 4 Name	scln	Character	20		{R}

Table Name: class1 (Attribute 1, Level 5)

The class1 table is used to create new attribute 1 codes or update current attribute 1 codes.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Level 5 #	cl1	Character	3		{R}
3	Level 5 Name	cl1n	Character	20		{R}

Table Name: class2 (Attribute 2, Level 6)

The class2 table is used to create new attribute 2 codes or update current attribute 2 codes.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Level 6 #	cl2	Character	3		{R}
3	Level 6 Name	cl2n	Character	20		{R}

Table Name: class3 (Attribute 3, Level 7)

The class3 table is used to create new attribute 3 codes or update current attribute 3 codes.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Level 7 #	cl3	Character	3		{R}
3	Level 7 Name	cl3n	Character	20		{R}

Table Name: color

The color table is used to create new colors or update current colors.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Color Code	colc	Character	6		{R}
3	Color Name	coln	Character	30		{R} If using a color group, this field value will also be populated in the colorg table.
4	Color Group #	colgg	Character	3		Populate only if using color group. This must relate to the colorg table.
5	Color Description 1	colsdsc1	Character	10		Client defined
6	Color Description 2	colsdsc2	Character	10		Client defined

Table Name: colorg (Color Group)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Color Group Code	colgc	Character	3
3	Color Group Name	colgn	Character	30

Table Name: articles (Item Master)

The table contains all items and corresponding information pertaining to the items. The value in the article field (UPC) must be either 10, 12, 13 digits or left blank. If the value is 10 digits, KWI loads all 10 digits into article. If the value is 12 digits, KWI loads the first digit into upc1 (field 32) and the next 10 digits into the article field. If the value is 13 digits, KWI loads the first 2 digits into upc1 and the next 10 digits into the article field. If the value is blank, KWI will create a 10-digit SKU value to populate the article field.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC (article)	article	Character	10		{R}
3	Cost (wholesale, landed or average) Inventory is valued at this cost.	wp	Numeric	13	2	{R} 99999999.99
4	Retail Price	rp	Numeric	13	2	{R} 99999999.99
5	Markdown Price	mp	Numeric	13	2	{R} 99999999.99         Default = 0
6	Class – Level 2	Type_c	Character	3		{R} Requires supporting table Default 000 (table types)
7	Vendor #	model_c	Character	5		{R} Requires supporting table (table models)
8	Mark Down Date	md	Date			mm/dd/yy {R} default **/**/**
9	Season	season	Character	3		{R}
10	Active Code	act	Character	1		{R} Y/N Used for items that are discontinued. If N, will not show item on various reports.
11	Description (Reports)	descr	Character	40		{R}
12	Department	collect	Character	2		{R} Requires supporting table (table coll)
13	Description (POS)	descr2	Character	15		{R}
14	Cost #2 (first)	cp	Numeric	13	2	{R} 99999999.99 Default = 0 Used for Average costing
15	Vendor Style #	venitem	Character	20		{R}
16	Last Modified Date	cd	Date			{R} mm/dd/yy use current date as default for new data
17	Size	size1	Character	8		{R} N/S for blank sizes POS limitation of 5 bytes
18	Color	colr	Character	30		Non-table field – Name vendor calls it.
19	Color #	color_c	Character	6		{R} Requires supporting table Default 000000 (table color)
20	Sub Class – Level 3	pat_c	Character	3		{R} Requires supporting table Default 000 (table patterns)
21	Label Ticket	descr3	Character	50		Name of KWI barcode ticket
22	Outlet Price	sp	Numeric	13	2	{R} 99999999.99 Default = 0 client 101-
23	Outlet Mark Down Price	smp	Numeric	13	2	{R} 99999999.99 Default = 0
24	Outlet Mark Down Date	smd	Date			{R} mm/dd/yy default **/**/**
25	Unit Pack Quantity	ppqty	Numeric	6		{R} 1 The amount by which you must order from the vendor (six pack of soda ppqty = 6)
26	Open Field #1	venitem2	Character	20		Warehouse Bin location (For clients with a warehouse. Can be used for alternate UPC/SKU)
27	Open Date	scd	Date			{R} mm/dd/yy default **/**/**
28	Open Price Field #1	fp	Numeric	13	2	{R} Default = 0
29	Open Price Field #2 (Outlet Cost)	scp	Numeric	13	2	{R} Default = 0
30	Original Retail	sfp	Numeric	13	2	{R} Default = 0
31	Open Field #2	stat	Character	1		Client defined. By default, this is used for ordering items – Y = Item can be ordered – Default N = Item cannot be ordered
32	Level #4 sub department	sclass_c	Character	3		{R} Requires supporting table Default 000 (table sclass)
33	Open	upc1	Character	2		Use to maintain 1st byte of 12-byte UPC
34	Level #5 attribute 1	cl1_c	Character	3		{R} Requires supporting table Default 000 (table class1)
35	Level #6 attribute 2	cl2_c	Character	3		{R} Requires supporting table Default 000 (Table class2)
36	Open	act2	Character	1		Client Defined
37	Cubic Size	cubic	Numeric	5		{R} Default = 0
38	Open	stat2	Character	1		Client Defined:
39	Discount flag	stat3	Character	1		0 = item cannot be discounted 1 = item can be discounted Default is 1
40	Item Create date	origcd	Date			{R} mm/dd/yy default **/**/**
41	Level #7 attribute 3	cl3_c	Character	3		{R} Requires supporting table Default 000 (table class3)
42	Available on Web	stat4	Character	1		Used for web items Y/N
43	Rental flag	stat5	Character	1		Used by movie rental Y= item can be rented
44	Sell Thru flag	stat6	Character	1		Used by movie rental Y= item can be sold
45	Open	ppqty2	Numeric	6		Default =1
46	Open	cwp	Numeric	13	2	Client defined
47	Open	length1	Numeric	3	0	Default = 0 Client defined
48	Open	width1	Numeric	3	0	Default = 0 Client defined
49	Open	heal	Numeric	3	0	Default = 0 Client defined
50	Open	arch	Numeric	3	0	Default = 0 Client defined
51	Open	fabric1	Character	30		Client defined
52	Open	fabric2	Character	30		Client defined
53	Open	fabric3	Character	30		Client defined
54	Open	fabric4	Character	30		Client defined
55	Open	fabric5	Character	30		Client defined
56	Open	fabric6	Character	30		Client defined
57	Open	fabric7	Character	30		Client defined
58	Open	fabric8	Character	30		Client defined
59	Open	fabric9	Character	30		Client defined
60	Open	fabric10	Character	30		Client defined
61	Open – markdown price field	wpmp	Numeric	11	2	Default = 0 Client defined
62	Open – markdown date field	wpmd	Character	8		mm/dd/yy Default = **/**/**
63	Open – markdown price field	wpsmp	Numeric	11	2	Default = 0 Client defined
64	Open – markdown date field	wpsmd	Character	8		mm/dd/yy Default = **/**/**
65	Case weight	casewt	Numeric	11	2	Client defined
66	Case cubic measure	cascubic	Numeric	11	2	Client defined
67	Link to Picture .png	picture	Character	256		400 x 400 jpeg RECOMMENDED
68	Duty Code	difccd	Character	8		Client defined
69	Vat Code	vatcd	Character	4		Client defined
70	British Pound invoice cost	gbpivc	Numeric	11	2	Default = 0 – Client defined
71	British Pound plan dollars	gbplan	Numeric	11	2	Default = 0 – Client defined
72	British Pound average cost	gbpavg	Numeric	11	2	Default = 0 – Client defined
73	British Pound retail price	gbprp	Numeric	11	2	Default = 0 – Client defined
74	Registration Required	reg_req	Character	1		Y/N
75	Wait List	waitlist	Character	1		Y/N
76	Open	meetdates	Character	60
77	Course first meet date	firstmeet_dt		8		mm/dd/yy – default **/**/**
78	Course Last meet date	lastmeet_dt		8		mm/dd/yy – default **/**/**
79	Garment origin	origingarment_c	Character	10		{R} Linked to Origin Country table Default to 0
80	Delivery Month	delivery	Character	30		Client defined
81	Manufacture	manufact	Character	10		{R} Linked to table Manufacture table Default to 0
82	Rise	rise	Character	30		Client defined
83	Fabric Code	fabric_c	Character	10		{R} Linked to Fabric table         Default to 0
84	Color Season	colorsea	Character	10		Client defined
85	Channel	channel	Character	10		Client defined
86	Fabric origin	originfabric_c	Character	10		{R} Linked to Origin Country table Default to 0
87	Sub-Class	subcl	Character	10		{R} Client defined – not related to KWI hierarchy table. Linked to subcl table– Default to 0
88	Pattern	pattern2	Character	30		Client defined
89	Style/season	stylesea	Character	10		Client defined
90	Retail Year	retailyear	Numeric	10		Default to 0
91	Life Cycle	lifecycle	Character	30		Client defined
92	Dimension	dimension	Character	30		Client defined
93	Base Currency	bcrp	Character	10		Client defined
94	Country or Origin	c_origin	Character	60		Client defined
95	Fabric Content	f_content	Character	240		Client defined
96	Fabric Care Instructions	f_care	Character	240		Client defined
97	Romance Copy	r_copy	Character	240		Client defined
98	Size Break Code	mod_c	Character	3		{R} Default to 000
99	Validation code	validate	Character	1		Client defined Y/N
100	Employee Price	empprice	Numeric	11	2	Employee Price
101	Backorder Eligible Indicator	bkord_eligible	Character	1		Indicate Y/N. Y value indicates to send records associated with this SKU/article in On-Order Data Feed that will be used by Web for back orders. Please consult with your CSM regarding this functionality. NOTE: This field/function applies only to Web processes and does not apply to POS.

Table Name: nmart (Non-Merchandise)

Field #	Field Name	KWI Field Name	Type	Length	Dec	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Non-Merch Item #	art_nma	Character	4		{R}
3	Retail Price	rp_nma	Numeric	13	2	{R} 99999999.99
4	Department Description	sty_nma	Character	20		{R}
5	Report Description	des_nma	Character	40		{R}
6	Last Modified Date	cd_nma	Date			{R} mm/dd/yy
7	POS Description	ds3_nma	Character	15		{R}
8	Active Flag	act_nma	Character	1

Table Name: kafka_names (Kafka Event Queue)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Queue Type	Type_kn	Varchar	10		{R} S – System consumer    A – Application consumer
2	Queue Name	Name_kn	Varchar	100

Table Name: kits

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Kit parent sku	mas_kt	Character	10
3	child sku	art_kt	Character	10
4	Quanity	quan_kt	Numeric	7	3

Table Name: loader_archive

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Program name	program_la	Character	256		The program/script that is running the data load
3	File name	processed_file_la	Character	256		The file name that is being loaded
3	Start time	start_time_la	Datetime			The start time of the load
5	End time	end_time_la	Datetime		3	The end time of the load
6	Loader results	process_msg_la	Character	4096	3	The results from the load

Table Name: lowest_selling_price

This table gets refreshed nightly with the lowest selling price by SKU/UPC within a configurable number of days (default is 365) and the lowest selling price is greater than or equal to a configurable threshold (default is 0.01). This table is used when doing a blind return if the client is set up to honor the lowest selling price.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	UPC (article)	article_lsp	Character	10
3	Lowest Selling Price	price_lsp	Numeric	13	2	The lowest sale price of the article.
4	Lowest Selling Date	salesdate_lsp	Date			The date of the actual sale.
5	Create Date	createdate_lsp	Timestamp
6	Modify Date	modifydate_lsp	Timestamp

Table Name: loyalty

This table is used to store transactional loyalty data from the KWI Loyalty Platform.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Store	stc_llt	Numeric	6
3	Date	ym_llt	Date
4	Terminal	trm_llt	Numeric	6
5	Transaction #	trn_llt	Numeric	6
6	Program Name	progname_llt	Character	40
7	Program Description	progdesc_llt	Character	256
8	Program Alias Name	progalias_llt	Character	40
9	Points Redeemed	points_used_llt	Numeric	6
10	Points Earned	points_earned_llt	Numeric	6
11	Points Deducted	points_deducted_llt	Numeric	6
12	Available Points	points_balance_llt	Numeric	6
13	Available Points Value	points_value_llt	Numeric	11	2
14	Pending Points	pending_balance_llt	Numeric	6
15	Mermber Tier Level	tier_llt	Character	50

Table Name: lay (Layaway or Special Order)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Article Number	art_ly	Character	10
3	Customer Number	num_ly	Character	10
4	Layaway Type	code_ly	Character	1		C = layaway Create X = Layaway Cancel T = layaway Tax P = layaway Pickup M= layaway Payment
5	Payment	pr_ly	Numeric	11	2
6	Date	ym_ly	Date			Date mm/dd/yy
7	Time	tm_ly	Time			Time: hh:mm:ss
8	Store	stc_ly	Numeric	6
9	Unused field	coll_ly	Character	1
10	Employee	emp_ly	Numeric	6
11	Account Number	acct_ly	Character	10
12	Item Quantity	quan_ly	Numeric	6
13	Terminal #	trm_ly	Numeric	6
14	Transaction #	trn_ly	Numeric	6

Table Name: last_processed (Last Scheduled Processed)

This is an internal log table to keep track of scheduled processes.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique  field)
2	Process	type_lp	Character	20		The run process that we are logging
3	Process rowed	rowid_lp	Numeric	11		The last rowid processed
4	Date	date_lp	Date			The last date processed

Table Name: manifest (Manifest Parent)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	store_mfst	Numeric	6		{R}
3	Vendor	vendor_mfst	Numeric	6		{R}
4	CreateDate	createDate_mfst	Date			{R} mm/dd/yy default **/**/**
5	ShipDate	shipDate_mfst	Date			{R} mm/dd/yy default **/**/**
6	ActiveCode	active_mfst	Character	1		{R}

Table Name: mani (Manifest Child)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	ParentRowID	parent_rowid_mani	Numeric	11		{R} LINKED to manifest.rowid
3	RepairNumber	repair_mani	Numeric	6		{R} LINKED to repairs.num_repr

Table Name: material

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_mat	Numeric	6		{R}
3	Description	desc_mat	Character	50		{R}

Table Name: max_orders (Maximum Number of Open and Total Fulfill It Orders by Store)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	rowid	rowid	Numeric	11
2	Store	stc_mo	Numeric	6		Store
3	Client	clnt_mo	Numeric	6		Client
4	Maximum open orders allowed	maxopen_mo	Numeric	6		Maximum number of open orders allowed
5	Maximum orders allowed	totallowed_mo	Numeric	6		Maximum number of total orders allowed

Table Name: usr (Back Office User credentials)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	ID	usrid	Character	256		{R}
3	Name	usrname	Character	30		{R}
4	Password	usrpass	Character	10		{R}
5	Security Group	usrgrou	Character	10		{R}
6	Language	usrlang	Numeric	6		1 = English
7	User Store	usrstr	Numeric	6		{R} Must be valid store number on table stores
8	User Group	usrstg	Numeric	5		{R} {0} Must be valid store group on table stg
9	Open	usrexcld	Character	1		leave blank
10	Email	usreml	Character	50
11	Open	usrven	Character	5
12	Order Approval Limit	usrordap	Numeric	13	2
13	Internal User	usrsystem	Numeric	11		Identify an internal user (i.e., kwi user)

Table Name: media

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_me	Numeric	6
3	Date	ym_me	Date			mm/dd/yy
4	Form of Payment #	media.code_me	Numeric	6		Defaults: 1 = Cash 2 = Check 3 = Traveler’s Check 6 = MC 7 = Visa 8 = AMEX 9 = Discover 11 = JCB 10 = Diners 12 = Store Credit 13 = Gift Cert 14 = Mall GC 32 = Synchrony
5	Amount	pr_me	Numeric	11	2
6	Transaction Number	trn_me	Numeric	6
7	Terminal Number	trm_me	Numeric	6
8	Time	tm_me	Time	5		hh:mm

Table Name: minmax

Required for min max function: store, UPC, min, max

For store pricing: store, UPC, store level retail, date record added, and active flag

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store	stc_mm	Numeric	6
3	UPC	art_mm	Character	10
4	Min	min_mm	Numeric	6
5	Max	max_mm	Numeric	6
6	Store Level Retail	rp_mm	Numeric	13	2	99999999999.99
7	Store Level Markdown	mp_mm	Numeric	13	2	99999999999.99
8	Date Record Added	cd_mm	Date			mm/dd/yy
9	Active Flag – for pricing	act_mm	Character	1		Y/N
10	Store Cost	wp_mm	Numeric	13	2	99999999999.99
11	Store First Cost	cp_mm	Numeric	13	2
12	Threshold	thr_mm	Numeric	6
13	Comments	comm_mm	Character	40
14	Outlet First Cost	scp_mm	Numeric	13	2
15	Replenishment	show_mm	Character	1		Y/N
16	Initial Quantities	iqty_mm	Numeric	6

Table Name: latefo (Movie Rental Late Fees Owed)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Cutomer #	cust_lo	Character	10
3	Movie serial #	ser_lo	Character	10
4	Amount owed	pr_lo	Numeric	13	2
5	Date movie returned	ym_lo	Date			**/**/**
6	Paid Flag	stat_lo	Character	1		Y= owed N = paid

Table Name: latefp (Movie Rental Late Fees Paid)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_lp	Numeric	6
3	Date fee paid	ym_lp	Date			**/**/**
4	Time	tm_lp	Time			hh:mm
5	Employee #	emp_lp	Numeric	6
6	Amount paid	pr_lp	Numeric	13	2
7	Customer #	cust_lp	Character	10
8	Code	code_lp	Character	6
9	Register terminal #	term_lp	Numeric	6
10	Transaction #	tran_lp	Numeric	6
11	Status	stat_lp	Character	1		Y = open, N= closed

Table Name: serial (Movie Rental Title Copies)

Maintains information on individual copies of a movie title.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Serial number	num_sn	Character	10
3	Article #	art_sn	Character	10
4	Date created	cd_sn	Date			**/**/**
5	Dated modified	md_sn	Date			**/**/**
6	Time modified	tm_sm	Time			hh:mm:ss
7	Location	loc_sn	Character	1		W = Warehouse A = Store R = rented
8	Customer	cust_sn	Character	10
9	Store	stc_sn	Numeric	6
10	Employee number	emp_sn	Numeric	6
11	Due Date	due_sn	Date			**/**/**

Table Name: rental (Movie Rental Title History)

Maintains complete information about a single rental – check in / out of store.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store	stc_rn	Numeric	6
3	Date Rented	ym_rn	Date			**/**/**
4	Time	tm_rn	Time			hh:mm:ss
5	Employee	emp_rn	numeric	6
6	Amount Paid	pr_rn	numeric	13	2
7	customer	cust_rn	Character	10
8	Open	code_rn	Character	6
9	Register terminal	term_rn	Numeric	6
10	Transaction #	tran_rn	Numeric	6
11	Movie serial #	ser_rn	Character	10
12	Stat	stat_rn	Character	1		Y = unprocessed R = Rented – Out N = Returned – In
13	Date of return	rym_rn	Date			**/**/**
14	Due date	due_rn	Date			**/**/**

Table Name: renret (Movie Rental Title Returns)

Maintains information about a title returns only.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_rr	Numeric	6
3	Date movie returned (Entered by employee)	ym_rr	Date			**/**/** (allows employee to back-date returns when needed after 12:00am)
4	Date movie returned	rdt_rr	Date			**/**/**
5	Time movie returned	rtm_rr	Time			hh:mm
6	Employee	emp_rr	numeric	6
7	Movie serial #	ser_rr	Character	10
8	Status	stat_rr	Character	1		Y/N Y = open N = close (matched with rental)

Table Name: mpos_product_verification (mPOS Product Authentication)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Session rowid	session_rowid_mpv	Numeric	11		Rowid from mpos_client_session
3	Transaction rowid	transaction_rowid_mpv	Numeric	11		Rowid from mpos_transaction
4	Unique order ID	order_id_mpv	Character	45		Unique order ID
5	Auth phone #	verification_phone_mpv	Character	45		The verification phone number for the authentication system
6	API request data	payload_mpv	Medium Text			Request data from API
7	Status of job submission	submit_queue_success_mpv	Character	45		Status of the job submission

Table Name: rtvtrn (Return to Vendor)

Table Name: rtvtr (Return to Vendor Details)

Table Name: pallets

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Pallet Number	num_ll	Numeric	6		The number associated with the pallet.
3	Store Number	stc_ll	Numeric	6		KWI Store Number
4	Pallet Distribution Date	ym_ll	Date			MM/DD/YYYY. The date the pallet was distributed.
5	Pallet Quantity	qty_ll	Numeric	9		The total quantity on the pallet.
6	Warehouse Location Number	skn_ll	Character	20		Location number within the warehouse.
7	Season	sea_ll	Character	3		The season associated with the pallet.
8	Total Cost	wp_ll	Numeric	12	2	The total cost of the items on the pallet.
9	Manifest	desc_ll	Character	30		The manifest number associated with the pallet.
10	Warehouse	whc_ll	Numeric	6		KWI Warehouse location number
11	Division	col_ll	Character	2		Division code. Maps to level 1 of the item hierarchy (i.e., department)
12	Department	typ_ll	Character	3		Department code. Maps to level 2 of the item hierarchy (i.e., class)
13	Distribution Doc #	dis_ll	Numeric	6		The distribution number associated with the pallet. The Distribution Doc # will correspond to an existing record in the distribution table (unless the value is 0).
14	Open Field	tqty_ll	Numeric	9		Client defined field
15	Active Code	act_ll	Character	1		The Active Code will be A, C or V. A: Allocate C: Committed V: Available
16	Classification	descr_ll	Character	40		The classification code of the pallet
17	Modify Date	mdt_ll	Date			The date of the last update of the pallet record. This field cannot be added when creating new records modified for existing records.
18	User	usrid_ll	Character	256		The user who last updated the pallet record. This field cannot be modified for existing records.
19	Closed Date	cs_11	Character	6		Client defined field for internal date identification
20	LPN/ Location	loc_ll	Character	30		Pallet Location identifier

Table Name: pay

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store #	stc_py	Numeric	6		{R}
3	Date	ym_py	Date	8		{R} mm/dd/yy
4	Employee #	emp_py	Numeric	6		{R}
5	Social Security #	ssn_py	Numeric	9		{R}
6	Hours Worked Type 1 – Regular	pt1_py	Numeric	13	2	{R}
7	Hours Worked Type 2 – Overtime	pt2_py	Numeric	13	2
8	Hours Worked Type 3 – Sick	pt3_py	Numeric	13	2
9	Hours Worked Type 4 – Regular	pt4_py	Numeric	13	2
10	Hours Worked Type 5 – Vacation	pt5_py	Numeric	13	2
11	Hours Worked Type 6 – Holiday	pt6_py	Numeric	13	2
12	Hours Worked Type 7 – Personal	pt7_py	Numeric	13	2
13	Hours Worked Type 8 – Open	pt8_py	Numeric	13	2
14	Hours Worked Type 9 – Open	pt9_py	Numeric	13	2
15	Hours Worked Type 10 – Open	pt10_py	Numeric	13	2
16	Hours Worked Type 11 – Open	pt11_py	Numeric	13	2
17	Hours Worked Type 12 – Open	pt12_py	Numeric	13	2
18	Hours Worked Type 13 – Open	pt13_py	Numeric	13	2
19	Hours Worked Type 14 – Open	pt14_py	Numeric	13	2
20	Hours Worked Type 15 – Open	pt15_py	Numeric	13	2
21	Terminal Register #	trm_py	Numeric	9
22	Transaction #	trn_py	Numeric	9
23	Internal Employee ID	empcode_py	Character	20

Table Name: plans

The plans table is used to create new plans or update current plans.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store #	stc_pl	Numeric	6		{R}
3	Level #1 (collect)	lvl1_pl	Character	3		Department #
4	Level #2 (types)	lvl2_pl	Character	3		Class #
5	Level #3 (patterns)	lvl3_pl	Character	3		Sub Class #
6	Period Ending Date	ym_pl	Date			{R} mm/dd/yy
7	Sales Dollars	sd_pl	Numeric	13	2	{R} 99999999.99
8	Units	su_pl	Numeric	6		Default is 0
9	On Hand Dollars	id_pl	Numeric	13	2	99999999.99 Default is 0.00
10	On Hand Units	iu_pl	Numeric	6		Default 0
11	Open Field #1	md_pl	Numeric	13	2	Client defined field
12	Open Field #2	rd_pl	Numeric	13	2	Client defined field
13	Open Field #3	mu_pl	Numeric	6		Client defined field
14	Open Field #4	ru_pl	Numeric	6		Client defined field
15	Forecast Dollars	fd_pl	Numeric	13	2
16	Create Date	createdate_pl	Date			Default is **/**/**
17	Modify Date	modifydate_pl	Date			Default is **/**/**
18	Description	descr_pl	Character	255		For Coalition 6X location only
19	Message	msg_pl	Character	255		For Coalition 6X location only
20	Level #3/Open	lvl4_pl	Character	3
21	District Recovery %	dist_recp_pl	Numeric	13	2

Table Name: future (Future Pricing)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	First Cost Price	cp_fm	Numeric	13	2	{R} 0
3	Retail price (client 100)	rp_fm	Numeric	13	2	{R} 0
4	Retail Markdown price (client 100)	mp_fm	Numeric	13	2	{R} 0
5	Outlet Retail price (client 101)	sp_fm	Numeric	13	2	{R} 0
6	Outlet Retail Markdown price (client 101)	smp_fm	Numeric	13	2	{R} 0
7	Outlet Markdown Date	smd_fm	Date			{R} mm/dd/yy Default **/**/**
8	Article #	art_fm	Character	10		{R} 0
9	Effective Date	md_fm	Date			{R} mm/dd/yy Default **/**/**. The effective date for the price update.
10	Store #	stc_fm	Numeric	6		{R} 0
11	Used to flag records to be written to min max table	slpm_fm	Character	1		Client specific field
12	User ID	usr_fm	Character	256
13	Create Date	cd_fm	Date			{R} mm/dd/yy Default **/**/**
14	Open Retail Price Field #1 (client 102 option)	fp_fm	Numeric	13	2
15	Open Price Field #2 (1st Cost for Outlet location)	scp_fm	Numeric	13	2
16	Open Retail Markdown Price Field #1 (client 102 option)	sfp_fm	Numeric	13	2

Table Name: mkdwn (Markdown Pricing)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store	stc_md	Numeric	6		{R}
3	Article #	art_md	Character	10		{R}
4	Date	ym_md	Character	8		{R} mm/dd/yy Default **/**/** Must be day before prices are to change/start.
5	Mark Down Price	mp_md	Numeric	13	2	{R} 0
6	Quantity on hand	qoh_md	Numeric	6
7	(Retail Price – Markdown Price) * quantity on hand	dol_md	Numeric	13	2
8	Cost	wp_md	Numeric	13	2
9	Cost of mark down	wpdol_md	Numeric	13	2
10	Retail Cost	rp_md	Numeric	13	2
11	Cost of Retail	rpdol_md	Numeric	13	2
12	User ID	usr_md	Character	256

Table Name: mixmatch (MixMatch Promotion)

Multiple Pricing requires 3 tables (promotion, stores where promotion will apply, articles in the promotion)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion #	num_mh	Numeric	6
3	Promotion Name	nam_mh	Character	30
4	Download Date	cd_mh	Date			{R} mm/dd/yy
5	End Date	ed_mh	Date			{R} mm/dd/yy
6	Quantity	quan_mh	Numeric	6
7	Price	mp_mh	Numeric	13	2
8	Last Modified Date	lmd_md	Date
9	Active Code	act_mh	Character	1

Table Name: mmtchst (Stores)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion #	num_mst	Numeric	6
3	Store #	stc_mst	Numeric	6

Table Name: mmtchart (Articles)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion #	num_ma	Numeric	6
3	Article	art_ma	Character	10

Table Name: log_table (Procedure Log)

Internal table included here for informational purposes only.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto- increments unique field)
2	Update Time	updatedatetime	Timestamp
3	Run log messages	message _lsp	Character	128
4	Process	data	Character	128

Table Name: promo (Promotions)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Promotion Name	nam_pm	Character	40
3	Store	stc_pm	Numeric	6
4	Article	art_pm	Character	10
5	Sale Price	mp_pm	Numeric	13	2
6	Download date	cd_pm	Date	8		{R} mm/dd/yy Day sale is to start on
7	End date	ed_pm	Date	8		{R} mm/dd/yy
8	Active code	act_pm	Character	1		Y / N
9	Last Modified date	lmd_pm	Date	8		mm/dd/yy

Table Name: orders

Purchase Orders consist of a header and detail record.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_or	Numeric	6		{R}
3	Purchase Order #	num_or	Numeric	6		Key Field
4	Ship Date	dlv_or	Date			{R} mm/dd/yy
5	Cancel Date	can_or	Date			{R} mm/dd/yy
6	Order Date	dat_or	Date			{R} mm/dd/yy
7	Vendor #	ven_or	Character	5		{R}
8	Ship Via	ship_or	Character	20
9	Reference #	spec_or	Character	40
10	Active Code	act_or	Character	1		Default of “Y” if open or if loading receiving into warehouse or store “N” if closed
11	Approval	apr_or	Date			{R} mm/dd/yy Default **/**/**
12	PO Type	typ_or	Character	1
13	Comment	spec2_or	Character	40
14	Internal PO #	oms_or	Character	20
15	Freight Terms	frt_or	Character	20
16	Open Field	log_or	Character	20
17	Terms	trm_or	Character	20
18	Comment2	comm_or	Character	40
19	New	edi_or	Character	1
20	Modify Date	mod_or	Date	8		{R} mm/dd/yy Default **/**/**
21	New	modst_or	Character	1
22	Use alternate shipping	uas_or	Character	1		Y/N
23	Additional Comments	comm3_or	Character	90
24	Rep ID	repid_or	Character	256
25	Session ID	sesid_or	Character	40
26	Approval ID	appid_or	Character	20
27	Approval Date	aprdt_or	Date	8		{R} mm/dd/yy Default **/**/**
28	Approver	aprov_or	Character	20
29	Open Flag	cfrm_or	Charater	1		Y/N
30	Discount Field	disc_or	Character	20
31	WMS Indicator	wms_or	Character	1		Y/N
32	Wharfage Cost	wharf_od	Numeric	13	2	Charge by ocean carriers to cover the port authority’s cost of using a wharf to unload cargo from a vessel.
33	Freight Cost	freight_od	Numeric	13	2
34	Admin Cost	admin_od	Numeric	13	2
35	Duty Cost	duty_od	Numeric	13	2
36	Container Cost	contain_od	Numeric	13	2

Table Name: ord (Purchase Order Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC	art_od	Character	10		{R}
3	Quantity	quan_od	Numeric	6		{R}
4	Purchase Order #	num_od	Numeric	6		Key Field
5	Item cost	wp_od	Numeric	13	2	99999999.99
6	Comments	com_od	Character	20
7	Item Cost 2	wp2_od	Numeric	13	2	99999999.99

Table Name: ord2 (Manager Approval Purchase Order Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC	art_2d	Character	10		{R}
3	Quantity	quan_2d	Numeric	6		{R}
4	Purchase Order #	num_2d	Numeric	6		Key Field
5	Item cost	wp_2d	Numeric	13	2	99999999.99
6	Comments	com_2d	Character	20
7	Item Cost 2	wp2_2d	Numeric	13	2	99999999.99
8	Wharfage Cost	wharf_2d	Numeric	13	2
9	Freight Cost	freight_2d	Numeric	13	2
10	Admin Cost	Admin_2d	Numeric	13	2
11	Duty Cost	duty_2d	Numeric	13	2
12	Container Cost	contain_2d	Numeric	13	2

Table Name: pick (Pre-Distribution Orders Header)

Purchase orders by pre-distribution consist of a header and detail record.

Back Office Workflow:

• Create PO and pre-distribution records in KWI (PO Entry with Pre-Distribution)
• Receive merchandise at warehouse using PO number
• Run suggested distribution – this will make distribution records based on what was created in Pick/Pickl by the PO.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Number	num_pk	Numeric	6
3	Store #	stc_pk	Numeric	6		{R}
4	Date	ym_pk	Date			{R} mm/dd/yy Default **/**/**
5		bol_pk	Character	15
6	PO Number	po_pk	Numeric	6
7	Carton #	ucc_pk	Character	20
8	Active Code	act_pk	Character	1
9	Warehouse #	whc_pk	Numeric	6
10	Created Login	log_pk	Character	20

Table Name: pickl (Pre-Distribution Orders Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	UPC	art_pl	Character	10		{R}
3	Quantity	quan_pl	Numeric	6		{R}
4	Number	num_pl	Numeric	6		Key Field – links to pick table num_pk field

Table Name: purch (Store Receiving)

KWI populates both purchase order and distribution # – translation needed with distributions to identify which records are receiving against PO or distribution.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Article Number/UPC	art_pu	Character	10		{R}
3	Store Number	stc_pu	Numeric	6	2	{R}
4	Date	ym_pu	Date			{R} mm/dd/yy
5	Quantity Received	qr_pu	Numeric	6		{R}
6	Purchase Order #	qs_pu	Numeric	6
7	KWI Distribution/Receiving #	dis_pu	Numeric	6
8	ASN Receiving #	rec_pu	Numeric	6
9	Employee #	emp_pu	Numeric	6
10	Terminal #	trm_pu	Numeric	9
11	Transaction #	trn_pu	Numeric	9
12	Web Order Line #	inum_pu	Numeric	9
13	UCC #	ucc_pu	Character	25

Table Name: porec (Warehouse Receiving Header)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Receiving #	num_po	Numeric	6
3	Store #	stc_po	Numeric	6		{R} KWI Warehouse Number
4	Date	ym_po	Date			{R} mm/dd/yy
5	Purchase Order #	pon_po	Numeric	6
6	Distribution Number	dis_po	Numeric	6
7	Open	code_po	Character	10
8	User log in ID	log_po	Character	20
9	Employee ID	emp_po	Numeric	6

Table Name: pr (Warehouse Receiving Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Article #	art_pr	Character	10		{R}
3	Quantity	quan_pr	Numeric	6		{R}
4	Receiving #	num_pr	Numeric	6

Table Name: repairs (Repairs Parent Table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	RepairNumber	num_repr	Numeric	6		{R} LINKED to cust.num_cp
3	Customer	cust_repr	Character	10		{R}
4	CreateDate	cd_repr	Date			{R} mm/dd/yy default **/**/**
5	Status	status_repr	Character	2		{R} LINKED to repair_status.code_rstat
6	Style	style_repr	Character	20
7	Article	art_repr	Character	10
8	SKU Type	stype_repr	Character	3		{R} {Soft} LINK to 1 of 7 hierarchy levels
9	Est.Cost	est_cost_repr	Numeric	13	2
10	PriorRepair#	prior_num_repr	Character	20
11	Employee#	emp_repr	Numeric	6		{R} LINKED to employee.num_em
12	Vendor#	vendor_repr	Numeric	6		{R} LINKED to repair_location.num_rloc
13	Notes	notes_repr	Character	256
14	RevisedCost	revised_cost_repr	Numeric	13	2
15	FinalCost	final_cost_repr	Numeric	13	2	{R}
16	ShippingCost	shipping_repr	Numeric	13	2
17	Store#	store_repr	Numeric	6		LINKED to stores.st_num
18	Ownership Code	itm_own_repr	Character	3		LINKED to repair_itm_own.code_rio

Table Name: repair_conds (Repair Conditions Child Table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_rcond	Numeric	6		{R} LINKED to repairs.num_repr
3	Condition	cond_rcond	Numeric	6		{R} LINKED to conditions.num_cond

Table Name: repair_detail (Repair Detail Child Table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	RepairNumber	num_rdet	Numeric	6		{R} LINKED to repairs.num_repr
3	TypeNumber	rtype_rdet	Numeric	6		{R} LINKED to repair_type.num_rtp
4	Cost	cost_rdet	Numeric	13	2
5	ActiveCode	active_rdet	Character	1
6	MaterialNumber	material_rdet	Numeric	6		{R} LINKED to material.num_mat
7	Quantity	qty_rdet	Numeric	4

Table Name: repair_cost

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Material#	material_rcst	Numeric	6		{R} LINKED to material.num_mat
3	TypeNumber	rtype_rcst	Numeric	6		{R} LINKED to repair_type.num_rtp
4	Cost	cost_rcst	Numeric	13	2	(R)
5	EstimateFlag	estimate_rcst	Character	1		‘Y’ for estimate needed from vendor

Table Name: repair_location

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Vendor #	num_rloc	Numeric	6		{R}
3	Name	name_rloc	Character	50		{R}
4	Address1	add1_rloc	Character	50		{R}
5	Address2	add2_rloc	Character	50		{R}
6	City	ct_rloc	Character	20		{R}
7	State	st_rloc	Character	2		{R}
8	Zip Code	zip_rloc	Character	10		{R}
9	Country Code	co_rloc	Character	5		{R}
10	Email	email_rloc	Character	60		{R}
11	Telephone #	tel_rloc	Character	16

Table Name: repair_status

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Code #	code_rstat	Character	2		{R}
3	Description	desc_rstat	Character	256		{R}

Table Name: repair_sto_loc (Repair Store Locations)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Store#	store_rsloc	Numeric	6		(R) LINKED to stores.st_num
3	Location	reploc_rsloc	Numeric	6		(R) LINKED to repair_location.num_rloc {R}

Table Name: repair_itm_own (Repair Ownership Details Child Table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Ownership Code	code_rio	Character	3
3	Ownership Description	desc_rio	Character	256

Table Name: repair_type

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Number	num_rtp	Numeric	6		{R}
3	Description	desc_rtp	Character	256		{R}
4	SKU Type	stype_rtp	Character	3		{R}

Table Name: credit (Returns)

Record assumed quantity of + 1 (a return of 3 items would cause 3 records to be created in this table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC/Article #	art_cr	Character	10		{R} UPC #
3	Credit Value	pr_cr	Numeric	13	2	{R} Value must be a positive number – this is the net value of the item returned
4	Store Number	stc_cr	Numeric	6		{R} KWI six-digit store number – matches back to the Stores table
5	Date	ym_cr	Date			{R} mm/dd/yy
6	Employee #	emp_cr	Numeric	6		{R} The employee # of the person who processed the return
7	Customer #	num_cr	Character	10		The customer # associated to the return.
8	Reason Code	reas_cr	Character	2		Client defined reason code numbers
9	2nd Reason Code	cs2_cr	Character	10		Optional field – client defined
10	Time	tm_cr	Time			hh:mm (local time of store)
11	Transaction Number	trn_cr	Numeric	6		The transaction # to which the return record is associated.
12	Terminal – Register Number	trm_cr	Numeric	6		The register terminal # to which the return record is associated.
13	Zip Code	zip_cr	Character	10		Client defined field – optional
14	Tax	tx_cr	Numeric	8	3	The tax amount charged against the item returned.
15	Serial #	serl_cr	Character	30		Client Defined field – optional (leave blank)
16	Original Store Number	orig_cr	Numeric	6		The KWI store # to which the original sale was linked.
17	Original Sales Date	oym_cr	Date			mm/dd/yy
18	Run date / last mod. date	pdt_cr	Date			mm/dd/yy – CRM only (leave blank)
19	Cashier #	cshr_cr	Numeric	6		The cashier # that processed the return/
20	Sale Person #	salp_cr	Numeric	6		The salesperson # associated to the return.
21	Original Transaction #	otrn_cr	Numeric	6		The original sales transaction #.
22	Original Terminal #	otrm_cr	Numeric	6		The original sales terminal #.
23	Had Receipt Y/N	recpt_cr	Character	1		Default to Y
24	Original employee	oemp_cr	Numeric	6		The original salesperson #.
25	Web order #	webno_cr	Numeric	9		Stores the web order number if the client is utilizing an eCommerce interface.
26	Zip Code 2	zip2_cr	Character	10		Client defined field – optional
27	Purchasing Employee	purchemp_cr	Numeric	9		The Employee # who purchased the item.
28	Web Order Line #	inum_cr	Numeric	9		Stores the web order number line number if the client is utilizing an eCommerce interface.
29	Return Origin	retor_cr	Character	30
30	Return Type	coll_cr	Character	1
31	Original Price	opr_cr	Numeric	13	2
32	Original Tax	otx_cr	Numeric	8	3
33	Return Authorization	rauth_cr	Character	11
34	Item Weight	weight_cr	Numeric	13	2	Used for items priced by weight
35	Item Comment	comment_cr	Character	256
36	BOSS order	bossorder_cr	Character	1		If a Buy Online Ship from store transaction, set to Y.
37	Internal store ID	internalstoreid_cr	Character	10		Internal Store ID
38	Item tax ID	itax_cr	Numeric	9		Item tax ID. Links to the tax_details table.
39	KWI Store #	origstc_cr	Numeric	6		Open field, can be used as a secondary store number
40	Ship to State	shiptostate_cr	Character	2		Open field, can be used to log the state to which the item was shipped
41	Ship to City	shiptocity_cr	Character	40		Open field, can be used to log the city to which the item was shipped
42	Line Item ID	lineItemId_cr	Character	265		Unique line item ID, used for CSV
43	Link to mPOS Item	mpos_rowid_cr	Numeric	11		Rowid link to the mpos item table
44	Link to mPOS Transaction	trans_rowid_cr	Numeric	11		Rowid link to the mpos trans table
45	Record Create Date/Time	created_cr	Timestamp

Table Name: ukcredit (Unknown Returns)

Record assumed quantity of +1

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC / Article #	uartcr	Character	15
3	Credit Value	uprcr	Numeric	13	2
4	Store Number	ustccr	Character	6
5	Date	uymcr	Date			mm/dd/yy
6	Employee #	uempcr	Numeric	6
7	Customer #	unumcr	Character	10
8	Reason Code	ureascr	Character	2
9	2nd Reason Code	ucs2cr	Character	10
10	Time	utmcr	Time			hh:mm
11	Transaction Number	utrncr	Numeric	6
12	Terminal – Register Number	utrmcr	Numeric	6
13	Zip Code	uzipcr	Character	10
14	Tax	utxcr	Numeric	8	3
15­	Serial #	userlcr	Character	30
16	Description (entered at POS)	udesccr	Character	50
17	Original Selling Store	uorigcr	Numeric	6
18	Original Sales Date	uoymcr	Date			mm/dd/yy
19	Promotion Date	updtcr	Date			mm/dd/yy
20	Cashier	ucshrcr	Numeric	6
21	Salesperson	usalpcr	Numeric	6
22	Original Sales Transaction #	uotrncr	Numeric	6
23	Original Terminal #	uotrmcr	Numeric	6
24	Had Receipt	urecptcr	Character	1
25	Original Salesperson #	uoempcr	Numeric	6
26	Web order #	uwebnocr	Numeric	9
27	Zip Code 2	uzip2cr	Character	10
28	Purchasing Employee	upurchempcr	Numeric	9
29	Web Order Line #	uinumcr	Numeric	9
30	Return Origin	uretorcr	Character	30
31	Return Type	ucollcr	Character	1
32	Original Price	uoprcr	Numeric	13	2
33	Original Tax	uotxcr	Numeric	8	3
34	Return Authorization	urauthcr	Character	11
35	Item Weight	uweightcr	Numeric	13	2	Used for items priced by weight
36	Item Comment	ucommentcr	Character	256
37	BOSS order	ubossordercr	Character	1		If transaction is a Buy Online Ship from store, set to Y
38	Internal store ID	uinternalstoreidcr	Character	10		Internal Store ID
39	Item tax ID	uitaxcr	Numeric	9		Item tax ID. Links to the tax_details table
40	KWI Store #	uorigstccr	Numeric	6		Open field, can be used as a secondary store number
41	Ship to State	ushiptostatecr	Character	2		Open field, can be used to log the state to which the item was shipped
42	Ship to City	ushiptocitycr	Character	40		Open field, can be used to log the city to which the item was shipped
43	Line-Item ID	lineItemIdcr	Character	265		Unique line item ID, used for CSV
44	Link to mPOS Item	mposrowidcr	Numeric	11		Rowid link to the mpos item table
45	Link to mPOS Transaction	transrowidcr	Numeric	11		Rowid Link to the mpos trans table

Table Name: Sales (Sales by Item)

Record assumed quantity of +1 (a sale of 3 items would cause 3 records to be created in this table).

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC	sal_art	Character	10		{R} UPC #
3	Customer #	colsiz	Character	10		The Customer # associated to the sale.
4	KWI Code	sal_code	Character	1		{R} Default as 1
5	Selling Price	sal_pr	Numeric	11	2	{R} The net value of the item sold
6	Selling Date	sal_dt	Date			{R} mm/dd/yy
7	Time	sal_tm	Time			{R} hh:mm (Default as 12:01) Military Time
8	Store #	sal_snum	Numeric	6		KWI six-digit store number – matches back to the Stores table
9	Sale Type	sal_coll	Character	1		{R} Default as R R = Regular S = Send Sale (Ship It) E = Employee T = Tax Exempt L = Lay Away/Special Order X = Fulfill It
10	Employee	sal_emp	Numeric	6		The employee # of who processed the sale. This number must match the payroll number.
11	Promotion #	sal_prm	Character	10		Default as 0. This field is an open field not linked to the promo table. It is the promotion number associated with the sale.
12	Discount Reason Code	sal_disc	Numeric	6		Default as 0
13	Transaction Number	sal_trn	Numeric	9		The transaction # to which the sales record is associated. This is a unique transaction number for each sale. The receipt is unique by date, terminal, and transaction number.   IMPORTANT NOTE: Some KWI Back Office reports only display up to 6 characters.
14	Terminal – Register Number	sal_trm	Numeric	6		The register terminal # to which the sales record is associated. Each register has a unique number. Cloud 9 POS has its own number. This can be used for historical purposes.
15	Zip Code	sal_zip	Character	10		Client defined field – optional
16	Tax	sal_tax	Numeric	8	3	The tax amount charged against the item sold.
17	Serial #	sal_serl	Character	30		Client defined field – optional
18	Gift Basket Number	sal_gift	Character	3		Client defined field – optional
19	Coupon Number	sal_pnum	Numeric	6		Client defined field – optional
20	Sales Merge Date	sal_pdt	Date			mm/dd/yy Used by KWI CRM Group
21	Cashier Number	sal_cshr	Numeric	6		The employee # of the person who processed the sale. (This is the same # as row 9 – Employee.)
22	Salesperson Number	sal_salp	Numeric	6		The salesperson tied to the sales record.
23	Discount Level	sal_dlvl	Numeric	6		Client defined field – optional
24	Zip Code 2	sal_zip2	Character	10		Client defined field – optional
25	Purchasing Employee	sal_purchemp	Numeric	9		The Employee # purchasing the item. Can leave blank for historical information. Default = 0
26	Web Order Line #	sal_inum	Numeric	9		Stores the web order number line number if the client is utilizing an eCommerce interface.
27	Internal Use	sal_geninfo1	Character	30
28	Internal Use	sal_geninfo2	Character	30
29	Transaction Code Data	sal_transcode	Character	30		Stores transaction coding data gathered at the POS. (Client defined)
30	Item Weight	sal_weight	Numeric	13	2	Used for items priced by weight
31	Item Comment	sal_comment	Character	256
32	BOSS order	sal_bossorder	Character	1		If a Buy Online Ship from store transaction, set to Y
33	Internal store ID	sal_internalsto reid	Character	10		Internal Store ID
34	Item tax ID	sal_itax	Numeric	9		Item tax ID. Links to the tax_details table.
35	KWI Store #	sal_origsnum	Numeric	6		Open field, can be used as a secondary store number
36	Ship to State	sal_shiptostate	Character	2		Open field, can be used to log the state to which the item was shipped
37	Gift Item indicator	sal_isgift	Character	1		Y if this item is a gift
38	Ship to City	sal_shiptocity	Character	40		Open field, can be used to log the city to which the item was shipped
39	Line Item ID	sal_lineItemId	Character	265		Unique line item ID, used for CSV
40	Link to mPOS Item	sal_mpos_rowid	Numeric	11		Rowid link to the mpos item table
41	Link to mPOS Transaction	sal_trans_rowid	Numeric	11		Rowid link to the mpos trans table
42	Record Create Date/Time	sal_created	Timestamp

Table Name: sales_details

Field #	Field Name	KWI Field Name	Type	Length	Decimal		Description / Format
1	Rowid	Rowid	Numeric	9			KWI Internal (auto-increments unique field)
2	Store #	stc_sd	Numeric	6			KWI 6-digit store number – matches back to the Stores table.
3	Date	ym_sd	Date				The sales date.
4	Terminal	trm_sd	Numeric	11			The register terminal # to which the sales record is associated.
5	Transaction	trn_sd	Numeric	11			The transaction # to which the sales record is associated.
6	Line Number	inum_sd	Numeric	11			Links to the Sales table (KWI field name sal_inum).
7	Open Field	openfield_sd	Character	1			Open field; can be used to flag a line item of a sale.
8	Fulfilling Store	fulfillstore_sd	Numeric	11			The store that fulfilled the item (for Fulfill It orders).

Table Name: nmc (Non-Merchandise Sales)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Non- Merchandise Sale Code	code_nm	Character	6		{R} Shipping, gift cards, warranty repairs are accounted for here.
3	Amount	pr_nm	Numeric	13	2	{R} Without dollar sign Do not include commas or $ anywhere in the file.
4	Store	stc_nm	Numeric	6		{R}
5	Date	ym_nm	Date			{R} Date mm/dd/yy
6	Transaction #	trn_nm	Numeric	6
7	Terminal	trm_nm	Numeric	6
8	Customer #	cust_nm	Character	10		{R} Default = 0
9	Spa Employee	semp_nm	Numeric	6		{R} Default = 0
10	Employee	emp_nm	Numeric	6		{R} Default = 0
11	Spa Service	art_nm	Character	4
12	Time	tm_nm	Time			hh:mm
13	Merge Date – CRM field	pdt_nm	Date			Date mm/dd/yy default = **/**/**
14	Tax	tax_nm	Numeric	8	3
15	Line-item number	inum_nm	Numeric	9
16	Quantity	quan_nm	Numeric	6
17	Comment	comment_nm	Character	256
18	BOSS order	bossorder_nm	Character	1		If a Buy Online Ship from store transaction, set to Y
19	Internal store ID	internalstoreid_nm	Character	10		Internal Store ID
20	Item tax ID	itax_nm	Numeric	9		Item tax ID. Links to the tax details table.
21	Zip Code	sal_zip	Character	10		Client defined field – optional
22	KWI Store #	origstc_nm	Numeric	6		Open field, can be used as a secondary store number
23	Ship to State	shiptostate_nm	Character	2		Open field, can be used to log the state to which the item was shipped
24	Gift Item indicator	isgift_nm	Character	1		Y if this item is a gift
25	Ship to City	shiptocity_nm	Character	40		Open field, can be used to log the city to which the item was shipped
26	Line-Item ID	lineItemId_nm	Character	265		Unique line-item ID, used for CSV
27	Link to mPOS Item	mpos_rowid_nm	Numeric	11		Rowid link to the mpos item table
28	Link to mPOS transaction	trans_rowid_nm	Numeric	11		Rowid link to the mpos trans table
29	Record Create Date/Time	created_nm	Timestamp

Table Name: uknmc (Unknown Non-Merchandise Sales)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Non- Merchandise Sale Code	ucodenm	Character	6		{R} Shipping, gift card, warranty repair is accounted for here.
3	Amount	uprnm	Numeric	13	2	{R} Without dollar sign No commas or $ anywhere in document.
4	Store	ustcnm	Numeric	6		{R} Store
5	Date	uymnm	Date			{R} Date mm/dd/yy
6	Transaction #	utrnnm	Numeric	6		{R} Transaction ID
7	Terminal	utrmnm	Numeric	6		{R} Terminal ID
8	Customer #	ucustnm	Character	10		{R} Default = 0
9	Spa Employee	usempnm	Numeric	6		{R} Default = 0
10	Employee	uempnm	Numeric	6		{R} Default = 0
11	Spa Service	uartnm	Character	4		4-digit non-merchandise SKU number, reference to the nmart table
12	Time	utmnm	Time			hh:mm
13	Merge Date – CRM field	updtnm	Date			Date mm/dd/yy default = **/**/**
14	Tax	utaxnm	Numeric	8	3	The tax amount charge against the item sold
15	Line-item number	uinumnm	Numeric	9		Store the web order number line number if the client is utilizing an eCommerce interface
16	Quantity	uiuquannm	Numeric	6		Quantity of the item
17	Comment	ucommentnm	Character	256		Comment about the item
18	BOSS order	ubossordernm	Character	1		If transaction is a Buy Online Ship from store, set to Y
19	Internal store ID	uinternalstoreidnm	Character	10		Internal Store ID
20	Zip Code	uzipnm	Character	10		Client defined field – optional
21	KWI Store #	uorigstcnm	Numeric	6		Open field, can be used as a secondary store number
22	Ship to State	ushiptostatenm	Character	2		Open field, can be used to log the state to which the item was shipped
23	Gift Item indicator	uisgiftnm	Character	1		Y if this item is a gift
24	Ship to City	ushiptocitynm	Character	40		Open field, can be used to log the city to which the item was shipped
25	Line-Item ID	lineItemIdnm	Character	265		Unique line-item ID, used for CSV
26	Link to mPOS Item	mposrowidnm	Numeric	11		Rowid link to the mpos item table
27	Link to mPOS Transaction	transrowidnm	Numeric	11		Rowid link to the mpos trans table

Table Name: close (Sales Summary)

2 records per register per store – 2^nd record for tax & non-merch sales

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_cl	Numeric	6		{R} KWI six-digit store number – matches back to the Stores table
3	Date	ym_cl	Date			{R} mm/dd/yy – KWI to populate when creating close from sales
4	Time	tm_cl	Time			{R} hh:mm – KWI to populate when creating close from sales
5	Cash	c0814	Numeric	11	2	KWI to populate when creating close from sales – gross amount
6	Check	c0824	Numeric	11	2	KWI to populate when creating close from sales – gross amount
7	Travelers Check	c0834	Numeric	11	2	KWI to populate when creating close from sales – gross amount
8	Debit Card	c0844	Numeric	11	2	KWI to populate when creating close from sales – gross amount
9	House Account	c0854	Numeric	11	2	KWI to populate when creating close from sales – gross amount
10	Master Card	c0864	Numeric	11	2	KWI to populate when creating close from sales – gross amount
11	Visa	c0874	Numeric	11	2	KWI to populate when creating close from sales – gross amount
12	American Express	c0884	Numeric	11	2	KWI to populate when creating close from sales – gross amount
13	Paid Out / Non- Sale	c0894	Numeric	11	2	Petty Cash – KWI to populate when creating close from sales – gross amount
14	Deposit A	c08C4	Numeric	11	2	Client defined field
15	Deposit A – Pay	c08D4	Numeric	11	2	Client defined field
16	Deposit B	c08E4	Numeric	11	2	Client defined field
17	Deposit B – Pay	c08F4	Numeric	11	2	Client defined field
18	Non – Merchandise	c0914	Numeric	11	2	Non-Merchandise (i.e., shipping charges, alteration) KWI to populate when creating close from sales
19	Discount	c0924	Numeric	11	2	Discount value given for the day.
20	Coupon	c0934	Numeric	11	2	Client defined field
21	Net Tax	c0944	Numeric	11	2	Net tax amount collected for store/day
22	Discover	c0A14	Numeric	11	2	KWI to populate when creating close from sales – gross amount
23	Dinners Club	c0A24	Numeric	11	2	KWI to populate when creating close from sales – gross amount
24	JCB	c0A34	Numeric	11	2	KWI to populate when creating close from sales – gross amount
25	Gift Card Redeemed	c0A44	Numeric	11	2	KWI to populate when creating close from sales – gross amount
26	Store Credit Redeemed	c0A54	Numeric	11	2	Can be positive (redeemed) or negative(issues)
27	Filler 4	c0A64	Numeric	11	2	Not Used
28	Filler 5	c0A74	Numeric	11	2	Not Used
29	Mail Check	c0A84	Numeric	11	2	Client defined field – optional
30	Gift Card Sold	c0A94	Numeric	11	2	Gift Card Sold gross amount
31	Layaway Sales	c1914	Numeric	11	2	Layaway Sales value
32	Layaway Refunds	c1924	Numeric	11	2	Layaway Refunds value
33	Open	c1934	Numeric	11	2	Client defined field – optional
34	Layaway Tax	c1944	Numeric	11	2	Layaway Tax value
35	Layaway Payment	c1954	Numeric	11	2	Layaway Payment value
36	Open	c08A4	Numeric	11	2	Client defined field
37	Open	c08B4	Numeric	11	2	Client defined field
38	Open	c0954	Numeric	11	2	Client defined field
39	Open	c0964	Numeric	11	2	Client defined field
40	Open	c0974	Numeric	11	2	Client defined field
41	Open	c0984	Numeric	11	2	Client defined field
42	Open	c0994	Numeric	11	2	Client defined field
43	Open	c09A4	Numeric	11	2	Client defined field
44	Open	c09B4	Numeric	11	2	Client defined field
45	Open	c09C4	Numeric	11	2	Client defined field
46	Open	c0AA4	Numeric	11	2	Client defined field
47	Open	c0AB4	Numeric	11	2	Client defined field
48	Open	c0AC4	Numeric	11	2	Client defined field
49	Open	c0AD4	Numeric	11	2	Client defined field
50	Open	c09D4	Numeric	11	2	Client defined field
51	Mall Gift Certificate	mgc_cl	Numeric	11	2	Client defined field
52	Open	cantx_cl	Numeric	11	2	Client defined field
53	Open	hac1_cl	Numeric	11	2	Client defined field
54	Open	hac2_cl	Numeric	11	2	Client defined field
55	Master Ledger	hac3_cl	Numeric	11	2	Client defined field
56	Canadian Tax PST	pst_cl	Numeric	11	2	Client defined field
57	Open	emp_cl	Numeric	6		Client defined field
58	Foreign travelers check	ftrvc_cl	Numeric	11	2	Client defined field
59	Foreign gift card	fgcrd_cl	Numeric	11	2	Client defined field
60	Pay Pal	pypal_cl	Numeric	11	2	Client defined field
61	Send Sale Flag	txsrc_cl	Character	1		KWI use only.
62	Terminal Number	trm_cl	Numeric	6		Register terminal number
63	Transaction Number	trn_cl	Numeric	9		Register transaction number
64	Canadian Penny Diff	penny_cl	Numeric	11	2	For Canadian penny difference
65	Synchrony	sync_cl	Numeric	11	2
66	Amazon Order	amz_order_cl	Numeric	11	2
67	Amazon Pay	amz_pay_cl	Numeric	11	2
68	Affirm Payment	affirm_cl	Numeric	11	2
69	Apple Pay	app_pay_cl	Numeric	11	2
70	Manufacturer Coupon	mfgcoup_cl	Numeric	11	2	Manufacturer Coupon
71	After Pay	afterpay_cl	Numeric	11	2	Afterpay tender
72	WeChat	wech_cl	Numeric	11	2	WeChat tender
73	AliPay	alipay_cl	Numeric	11	2	Alipay tender
74	China Union Pay	ext_cup_cl	Numeric	11	2	External China UnionPay tender
75	Raymark Gift Card	raymark_cl	Numeric	11	2	Raymark gift card tender
76	Promo	promo_cl	Numeric	11	2	Promo tender
77	Alliance	tender108_cl	Numeric	11	2	Alliance
78	Alliance Bill Pay	tender109_cl	Numeric	11	2	Alliance Bill Pay
79	Klarna Payments	tender110_cl	Numeric	11	2	Klarna Payments
80	Postmates	tender111_cl	Numeric	11	2	Postmates
81	Quadpay	tender112_cl	Numeric	11	2	Quadpay
82	Sezzle	tender113_cl	Numeric	11	2	Sezzle
83	Venmo	tender114_cl	Numeric	11	2	Venmo
84	Magic Band	tender115_cl	Numeric	11	2	Magic Band (Disney)
85	Disney Rewards	tender116_cl	Numeric	11	2	Disney Rewards
86	Disney Gift card	tender117_cl	Numeric	11	2	Disney Giftcard
87	Department Charge	tender118_cl	Numeric	11	2	Department Charge
88	Open field	tender119_cl	Numeric	11	2	Doordash
89	Open field	tender120_cl	Numeric	11	2	PO
90	Open field	tender121_cl	Numeric	11	2	Globale
91	Open field	tender122_cl	Numeric	11	2	Bolt
92	Open field	tender123_cl	Numeric	11	2	Client Specific Loyalty
93	Open field	tender124_cl	Numeric	11	2	SPREE Tender
94	Open field	tender125_cl	Numeric	11	2	Reserved for a future tender
95	Open field	tender126_cl	Numeric	11	2	Reserved for a future tender
96	Open field	tender127_cl	Numeric	11	2	Reserved for a future tender
97	Open field	tender128_cl	Numeric	11	2	Reserved for a future tender
98	Open field	tender129_cl	Numeric	11	2	Reserved for a future tender
99	Open field	tender130_cl	Numeric	11	2	Reserved for a future tender
100	Open field	tender131_cl	Numeric	11	2	Reserved for a future tender
101	Open field	tender132_cl	Numeric	11	2	Reserved for a future tender
102	Open field	tender133_cl	Numeric	11	2	Reserved for a future tender
103	Open field	tender134_cl	Numeric	11	2	Reserved for a future tender
104	Open field	tender135_cl	Numeric	11	2	Reserved for a future tender
105	Open field	tender136_cl	Numeric	11	2	Reserved for a future tender
106	Open field	tender137_cl	Numeric	11	2	Reserved for a future tender
107	Open field	tender138_cl	Numeric	11	2	Reserved for a future tender
108	Open field	tender139_cl	Numeric	11	2	Reserved for a future tender
109	Open field	tender140_cl	Numeric	11	2	Reserved for a future tender

Table Name: uksales (Unknown Sales)

Record assumed quantity of +1

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC Number	usalart	Character	15
3	Customer #	ucolsiz	Character	10
4	KWI code	usalcode	Character	1		(no longer used)
5	Selling Price	usalpr	Numeric	11	2
6	Selling Date	usaldt	Date			mm/dd/yy
7	Selling Time	usaltm	Time			hh:mm
8	Store #	usalsnum	Numeric	6
9	Sale type	usalcoll	Character	1
10	Employee #	usalemp	Numeric	6
11	Promotion #	usalprm	Character	10
12	Discount Reason code	usaldisc	Numeric	6
13	Transaction #	usaltrn	Numeric	6
14	Terminal Register #	usaltrm	Numeric	6
15	zip code	usalzip	Character	10
16	Tax	usaltax	Numeric	8	3
17	Serial #	usalserl	Character	30
18	Description (entered at POS)	usaldesc	Character	50
19	Gift Basket	usalgift	Character	3
20	Promotion number for coupon related sales	usalpnum	Numeric	6
21	Promotion date	usalpdt	Date			mm/dd/yy
22	Cashier	usalcshr	Numeric	6
23	Sales Person	usalsalp	Numeric	6
24	Discount Level	usaldlvl	Numeric	6
25	Zip Code 2	usalzip2	Character	10
26	Purchasing Employee	usalpurchemp	Numeric	9
27	Web Order Line #	usalinum	Numeric	9
28	Internal Use	usalgeninfo1	Character	30
29	Internal Use	usalgeninfo2	Character	30
30	Transaction Code Data	usaltranscode	Character	30
31	Item Weight	usalweight	Numeric	13	2	Used for items priced by weight
32	Item Comment	usalcomment	Character	256
33	BOSS order	usalbossorder	Character	1		If transaction is a Buy Online Ship from store, set to Y
34	Internal store ID	usalinternalstoreid	Character	10		Internal Store ID
35	Item tax ID	usalitax	Numeric	9		Item tax ID. Links to the tax_details table
36	KWI Store #	usalorigsnum	Numeric	6		Open field, can be used as a secondary store number
37	Ship to State	usalshiptostate	Character	2		Open field, can be used to log the state to which the item was shipped
38	Gift Item indicator	usalisgift	Character	1		Y if this item is a gift
39	Ship to City	usalshiptocity	Character	40		Open field, can be used to log the city to which the item was shipped
40	Line-Item ID	usallineItemId	Character	265		Unique line-item ID, used for CSV
41	Link to mPOS Item	usalmposrowid	Numeric	11		Rowid link to the mpos item table
42	Link to the mPOS Transaction	usaltransrowid	Numeric	11		Rowid link to the mpos trans table

Table Name: schedule

This table is used to set up scheduled processes.

Field #	Field Name		KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid		Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Process		process_id_sdl	Character	64		The process that runs
3	Command		request_sdl	Character	15000		The command to run the process
4	Status		status_sdl	Character	30		The status of the schedule: Initiated, Started, Finished, Canceled, or Failed
5		Request Time	request_time_s dl	Timestamp			The time at which the request was made
6		Schedule time	schedule_time_ sdl	Timestamp			The time at which the process is scheduled
7		Start time	start_time_sdl	Timestamp			The time the process started
8		End time	end_time_sdl	Timestamp			The time the process ended
9		User	user_sdl	Character	255		The user who initiated the request
10		Attempts	attempts_sdl	Numeric	11		The # of attempts to run the process
11		Email indicator	email_sdl	Character	1		This is set to Y if the user should receive an email when the process completes

Table Name: ssal (Send Sales)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Sendsale #	num_ss	Numeric	6		(R) Default 0
3	Transaction Type	typ_ss	Character	1		(C)atalog, (S)endsale, (W)
4	Originating Store	ostc_ss	Numeric	6		(R)
5	Customer #	cust_ss	Character	10		(R)
6	Shipping Address 1	shpa1_ss	Character	40		(R)
7	Shipping Address 2	shpa2_ss	Character	40
8	Shipping Address 3	shpa3_ss	Character	40
9	Shipping Address City	shpct_ss	Character	40		(R)
10	Shipping Address State	shpst_ss	Character	50		(R)
11	Shipping Address Zipcode	shpzp_ss	Character	40		(R)
12	Shipping Address Phone#	shpph_ss	Character	16
13	E-mail address	cmail_ss	Character	65		(R)
14	Article #	art_ss	Character	10		(R)
15	Price	pr_ss	Numeric	13	2	(R)
16	Tax	tax_ss	Numeric	7	3	(R)
17	Shipping Charge	schg_ss	Numeric	13	2	(R)
18	Terminal	otrm_ss	Numeric	6
19	Transaction	otrn_ss	Numeric	6
20	Date	odt_ss	Date			(R)
21	Time	otm_ss	Time			0:00:00
22	Employee	oemp_ss	Numeric	6
23	Promo Name	oprm_ss	Character	10
24	Discount Code	odsc_ss	Numeric	6
25	FOP	oser_ss	Character	15
26	UPS	ups_ss	Character	30
27	Shipping Date	shpdt_ss	Date			(R)
28	Transaction Status	stat_ss	Character	1		‘P’ the transaction is pending ‘A’ the transaction has been completed ‘E’ the store has expired expired (timed out) and the transaction has been forwarded to the next fulfilling store ‘ C’ the transaction has been canceled ‘D’ the transaction was declined by the store
29	Processing Code	reas_ss	Character	1		Y – submitted
30	FOP #	fop_ss	Character	10
31	Auth Code	acode_ss	Character	12
32	Approval Status	astat_ss	Character	2
33	Catalog Order #	cator_ss	Numeric	9
34	Catalog Line #	catln_ss	Numeric	6
35	CC Expiration Date	expdt_ss	Character	4
36	Cashier	cshr_ss	Numeric	6
37	Salesperson	salp_ss	Numeric	6
38	Tax Rate	trate_ss	Numeric	7	4	Default 0.0000
39	Discount Amount	dsamt_ss	Numeric	13	2	Default 0.00
40	Coupon	coup_ss	Character	20
41	Post Update Indicator	pstu_ss	Character	1
42	Customer’s First Name	fn_ss	Character	20
43	Customer’s Last Name	ln_ss	Character	30
44	Auth Method	ameth_ss	Character	10
45	Auth Code	anum_ss	Character	10
46	NMC Tax	nmctx_ss	Numeric	8	3	Default 0.0000
47	Last 4 digits of Credit Card #	acct_ss	Character	16
48	New Application Flag	newflag_ss	Character	1		Used for internal purposes
49	Reason Code	codereas_ss	Character	1
50	Shipping Method	shpmtd_ss	Character	50
51	Shipping Date	send_dt_ss	Date
52	Shipping Time	send_tm_ss	Time
53	Order Number	onum_ss	Numeric	6
54	Updated Shipping Method	shpmtd2_ss	Character	6
55	Shipping Label	slabel_ss	Character	50
56	OMS Order #	oms2_ss	Character	20
57	OMS Item #	omsitem2_ss	Character	20
58	Internal Order #	internalordnum_ss	Character	20
59	Link Item ID	lineItemId_ss	Character	265		Unique line-item ID, used for CSV
60	Link to mPOS Item	mpos_rowid_ss	Numeric	11		Rowid link to the mpos item table
61	Link to mPOS Transaction	trans_rowid_ss	Numeric	11		Rowid link the the mpos trans table
62	Record Load Date/Time	loaddatetime_ss	Timestamp

Table Name: ssalst (Send Sales Store)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	SS Transaction #	num_sst	Numeric	6		(R)
3	Fulfilling store	sstc_sst	Numeric	6		(R)
4	Date assigned	sdat_sst	Date			(R)
5	Time assigned	stim_sst	Time			00:00:00
6	Status of transaction	stat_sst	Character	1		‘P’ the transaction is pending ‘A’ the transaction has been completed. ‘E’ the store has expired (timed out) and the transaction has been forwarded to the next fulfilling store. ‘C’ the transaction has been canceled. ‘D’ the transaction was declined by the store ‘K’ the transaction was reassigned manually
7	Terminal # (Not used)	trm_sst	Numeric	6
8	Transaction # (Not used	trn_sst	Numeric	6		Default 0
9	Date authorized	txdt_sst	Date			Default 0
10	Time authorized	txtm_sst	Time			00:00:00

Table Name: geocode (Send Sale Taxes)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_geo	Numeric	6
3	Geography Code	code_geo	Character	9
4	State code	st_geo	Character	2
5	Zip Code	zip_geo	Character	10
6	City Name	ct_geo	Character	40
7	State tax rate	rate_geo	Numeric	7	6
8	Load Date	ym_geo	Date			The date that the row was loaded to the geocode table.
9	City Sales Tax rate	cty_geo	Numeric	7	6
10	County Name	cou_geo	Character	40
11	County tax rate	coun_geo	Numeric	7	6
12	Full tax exemption flag	flag_geo	Character	2
13	County number	counum_geo	Character	20
14	City number	citynum_geo	Character	20
15	MTA name	mtanam_geo	Character	40		Metropolitan Transit Authority name
16	MTA number	mtanum_geo	Character	20		Metropolitan Transit Authority number
17	SPD name	spdnam_geo	Character	40		Special-Purpose District name
18	SPD number	spdnum_geo	Character	20		Special-Purpose District number

Table Name: sftp_info (SFTP Information)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Process Tag	sftp_tag	Character	30		Tag identifier
3	IP address	sftp_ip	Character	60
4	User ID	sftp_user	Character	80
5	Password	sftp_passwd	Character	80
6	Port	sftp_port	Character	10
7	Description	sftp_description	Character	128
8	Directory	sftp_directory	Character	256		Dropoff/Pickup directory

Table Name: mod_tbl (Size Table)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Model Code	nam_md	Character	3		{R}
3	Size 1	sz1_md	Character	8
4	Size 2	sz2_md	Character	8
5	Size 3	sz3_md	Character	8
6	Size 4	sz4_md	Character	8
7	Size 5	sz5_md	Character	8
8	Size 6	sz6_md	Character	8
9	Size 7	sz7_md	Character	8
10	Size 8	sz8_md	Character	8
11	Size 9	sz9_md	Character	8
12	Size 10	sz10_md	Character	8
13	Size 11	sz11_md	Character	8
14	Size 12	sz12_md	Character	8
15	Size 13	sz13_md	Character	8
16	Size 14	sz14_md	Character	8
17	Size 15	sz15_md	Character	8
18	Size 16	sz16_md	Character	8
19	Size 17	sz17_md	Character	8
20	Size 18	sz18_md	Character	8
21	Size 19	sz19_md	Character	8
22	Size 20	sz20_md	Character	8
23	Model Description	des_md	Character	25
24	Size 21	sz21_md	Character	8
25	Size 22	sz22_md	Character	8
26	Size 23	sz23_md	Character	8
27	Size 24	sz24_md	Character	8
28	Size 25	sz25_md	Character	8
29	Size 26	sz26_md	Character	8
30	Size 27	sz27_md	Character	8
31	Size 28	sz28_md	Character	8
32	Size 29	sz29_md	Character	8
33	Size 30	sz30_md	Character	8
34	Size 31	sz31_md	Character	8
35	Size 32	sz32_md	Character	8
36	Size 33	sz33_md	Character	8
37	Size 34	sz34_md	Character	8
38	Size 35	sz35_md	Character	8
39	Size 36	sz36_md	Character	8
40	Size 37	sz37_md	Character	8
41	Size 38	sz38_md	Character	8
42	Size 39	sz39_md	Character	8
43	Size 40	sz40_md	Character	8
44	Size 41	sz41_md	Character	8
45	Size 42	sz42_md	Character	8
46	Size 43	sz43_md	Character	8
47	Size 44	sz44_md	Character	8
48	Size 45	sz45_md	Character	8
49	Size 46	sz46_md	Character	8
50	Size 47	sz47_md	Character	8
51	Size 48	sz48_md	Character	8
52	Size 49	sz49_md	Character	8
53	Size 50	sz50_md	Character	8
54	Size 51	sz51_md	Character	8
55	Size 52	sz52_md	Character	8
56	Size 53	sz53_md	Character	8
57	Size 54	sz54_md	Character	8
58	Size 55	sz55_md	Character	8
59	Size 56	sz56_md	Character	8

Table Name: sos

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store	stc_so	Numeric	6		Store number
3	Article	art_so	Character	10		UPC number
4	On Hand	f1_so	Numeric	6
5	On Order	f2_so	Numeric	6
6	Min Qty.	f3_so	Numeric	6
7	Max Qty.	f4_so	Numeric	6
8	Sold Last 8 Wks	f5_so	Numeric	6
9	Sold Last 4 Wks.	f6_so	Numeric	6
10	Sold 11 Month Ago	f7_so	Numeric	6
11	Sold 10 Months Ago	f8_so	Numeric	6
12	Sold 9 Months Ago	f9_so	Numeric	6
13	Sold 8 Months Ago	f10_so	Numeric	6
14	Sold 7 Months Ago	f11_so	Numeric	6
15	Sold 6 Months Ago	f12_so	Numeric	6
16	Sold 5 Months Ago	f13_so	Numeric	6
17	Sold 4 Months Ago	f14_so	Numeric	6
18	Sold 3 Months Ago	f15_so	Numeric	6
19	Sold 2 Months Ago	f16_so	Numeric	6
20	Sold Last Month	f17_so	Numeric	6
21	Sold This Month	f18_so	Numeric	6
22	Sold 7 Wks Ago	f19_so	Numeric	6
23	Sold 6 Wks Ago	f20_so	Numeric	6
24	Sold 5 Wks Ago	f21_so	Numeric	6
25	Sold 4 Wks Ago	f22_so	Numeric	6
26	Sold 3 Wks Ago	f23_so	Numeric	6
27	Sold 2 Wks Ago	f24_so	Numeric	6
28	Sold Last Week	f25_so	Numeric	6
29	This week sales	f26_so	Numeric	6
30	In transit	f27_so	Numeric	6		Store to Store Transfer, Warehouse to store Distribution and ASN
31	Remaining to distro	f28_so	Numeric	6
32	Sold 8 Wks Ago	f29_so	Numeric	6
33	Sold 9 Wks Ago	f30_so	Numeric	6
34	Sold 10 Wks Ago	f31_so	Numeric	6
35	Sold 11 Wks Ago	f32_so	Numeric	6
36	Sold 12 Wks Ago	f33_so	Numeric	6
37	Sold 13 Wks Ago	f34_so	Numeric	6
38	Sold 14 Wks Ago	f35_so	Numeric	6
39	Sold 15 Wks Ago	f36_so	Numeric	6
40	Sold 16 Wks Ago	f37_so	Numeric	6
41	Sold 17 Wks Ago	f38_so	Numeric	6
42	Sold 18 Wks Ago	f39_so	Numeric	6
43	Sold 19 Wks Ago	f40_so	Numeric	6
44	Sold 20 Wks Ago	f41_so	Numeric	6
45	Sold 21 Wks Ago	f42_so	Numeric	6
46	Sold 22 Wks Ago	f43_so	Numeric	6
47	Sold 23 Wks Ago	f44_so	Numeric	6
48	Sold 24 Wks Ago	f45_so	Numeric	6
49	Sold 25 Wks Ago	f46_so	Numeric	6
50	Sold 26 Wks Ago	f47_so	Numeric	6
51	Sold 27 Wks Ago	f48_so	Numeric	6
52	Sold 28 Wks Ago	f49_so	Numeric	6
53	Sold 29 Wks Ago	f50_so	Numeric	6
54	Sold 30 Wks Ago	f51_so	Numeric	6
55	Sold 31 Wks Ago	f52_so	Numeric	6
56	Sold 32 Wks Ago	f53_so	Numeric	6
57	Sold 33 Wks Ago	f54_so	Numeric	6
58	Sold 34 Wks Ago	f55_so	Numeric	6
59	Sold 35 Wks Ago	f56_so	Numeric	6
60	Sold 36 Wks Ago	f57_so	Numeric	6
61	Sold 37 Wks Ago	f58_so	Numeric	6
62	Sold 38 Wks Ago	f59_so	Numeric	6
63	Sold 39 Wks Ago	f60_so	Numeric	6
64	Sold 40 Wks Ago	f61_so	Numeric	6
65	Sold 41 Wks Ago	f62_so	Numeric	6
66	Sold 42 Wks Ago	f63_so	Numeric	6
67	Sold 43 Wks Ago	f64_so	Numeric	6
68	Sold 44 Wks Ago	f65_so	Numeric	6
69	Sold 45 Wks Ago	f66_so	Numeric	6
70	Sold 46 Wks Ago	f67_so	Numeric	6
71	Sold 47 Wks Ago	f68_so	Numeric	6
72	Sold 48 Wks Ago	f69_so	Numeric	6
73	Sold 49 Wks Ago	f70_so	Numeric	6
74	Sold 50 Wks Ago	f71_so	Numeric	6
75	Sold 51 Wks Ago	f72_so	Numeric	6
76	Sold 52 Wks Ago	f73_so	Numeric	6
77	Sold 12 Months Ago	f74_so	Numeric	6
78	First Recv. Date	f75_so	Date			mm/dd/yy
79	Last Recv. Date	f76_so	Date			mm/dd/yy
80	Sold 53 Wks Ago	f77_so	Numeric	6
81	Sold 54 Wks Ago	f78_so	Numeric	6
82	Sold 55 Wks Ago	f79_so	Numeric	6
83	Sold 56 Wks Ago	f80_so	Numeric	6
84	Sold 57 Wks Ago	f81_so	Numeric	6
85	Sold 58 Wks Ago	f82_so	Numeric	6
86	Sold 59 Wks Ago	f83_so	Numeric	6
87	Sold 60 Wks Ago	f84_so	Numeric	6
88	Sold 61 Wks Ago	f85_so	Numeric	6
89	Sold 62 Wks Ago	f86_so	Numeric	6
90	Sold 63 Wks Ago	f87_so	Numeric	6
91	Sold 64 Wks Ago	f88_so	Numeric	6
92	Sold 65 Wks Ago	f89_so	Numeric	6
93	Sold 66 Wks Ago	f90_so	Numeric	6
94	Sold 67 Wks Ago	f91_so	Numeric	6
95	Sold 68 Wks Ago	f92_so	Numeric	6
96	Sold 69 Wks Ago	f93_so	Numeric	6
97	Sold 70 Wks Ago	f94_so	Numeric	6
98	Sold 71 Wks Ago	f95_so	Numeric	6
99	Sold 72 Wks Ago	f96_so	Numeric	6
100	Sold 73 Wks Ago	f97_so	Numeric	6
101	Sold 74 Wks Ago	f98_so	Numeric	6
102	Sold 75 Wks Ago	f99_so	Numeric	6
103	Sold 76 Wks Ago	f100_so	Numeric	6
104	Sold 77 Wks Ago	f101_so	Numeric	6
105	Sold 78 Wks Ago	f102_so	Numeric	6
106	Sold 79 Wks Ago	f103_so	Numeric	6
107	Last Yr Season TD	f104_so	Numeric	6
108	YTD Received	f105_so	Numeric	6
109	YTD Sales	f106_so	Numeric	6
110	Season TD Received	f107_so	Numeric	6
111	Season TD Sales	f108_so	Numeric	6
112	INV DELTA (trickle)	f109_so	Numeric	6
113	Threshold (thr_mm from minmax)	f110_so	Numeric	6
114	INTRAN (distro)	f111_so	Numeric	6
115	INTRAN (transfer)	f112_so	Numeric	6
116	LY (net) Sales to Date (365 days)	f113_so	Numeric	6
117	Lifetime Receiving’s	f114_so	Numeric	6
118	INTRAN (w/o ASNs)	f115_so	Numeric	6		Store to Store Transfer and Warehouse to Store Distributions. Does not include ASN data

Table Name: stock

On Hand Quantity – based on a Physical Inventory

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	UPC	art_st	Character	10		{R}
3	Quantity	quan_st	Numeric	6		{R}
4	Date	ym_st	Date			{R} mm/dd/yy For go live make date go live date.
5	Store #	stc_st	Numeric	6		{R}
6	Location	loc_st	Numeric	6

Table Name: stores

This table is used to create new stores or update current stores.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	st_num	Numeric	6		{R} First 3 numbers to be a 3-digit KWI prefilx If the store number already exists, the KWI store number will consist of the 3-digit KWI prefix plus a zero padded existing store number. Example: KWI prefix = “111”, Store Number = “16”, KWI Store Number = “111016”
3	Store Name	st_nam	Character	30		{R}
4	Address	st_ad	Character	30		{R}
5	City	st_cy	Character	20		{R}
6	State	st_st	Character	2		{R}
7	Zip Code	st_zp	Character	10		{R}
8	Store Phone #	st_tel	Character	20		{R}
9	Store Code	st_poll	Character	10		{R} Short Store name used for ticker
10	Grouping #1	st_clnt	Numeric	6		{R} Stores can be grouped according to your business needs by district, regions, mall, square footage, franchise, or corporation. Grouping #1 is used to group flash sales. Some defaults are: 100 = Regular Store 101 = Outlet Store 999 = Warehouse 998 = Outlet Warehouse 800 = Close retail store 801 = Closed Outlet Store 0     = Phantom Store (Client Table)
11	Grouping #2	st_rep	Numeric	6		{R} Make the same as above (Used for Regions or Districts) (Rep Table)
12	Size Curve code	st_sz	Character	3		{R} Default 000 (siz3 table)
13	Actual Opening Date	st_odt	Date			{R} mm/dd/yy (date of first sales, or actual opening date)
14	EDI Store #	st_num2	Numeric	6		{R} 0
15	Square Footage	st_size	Numeric	6		{R} 0
16	Units in Store	st_qty	Numeric	6		{R} 0
17	Exchange Rate	st_ex	Numeric	15	5	{R} 1.0
18	Email	st_email	Character	60
19	Grouping #3	st_reg	Character	6
20	Comp Opening Date	st_odt2	Date			{R} mm/dd/yy Default **/**/**
21	GL Store #	st_num3	Character	10
22	Sub Group #1	st_rep1	Numeric	6		{R) 0
23	Sub Group #2	st_rep2	Numeric	6		{R) 0
24	Sub Group #3	st_rep3	Numeric	6		{R) 0
25	Ledger Code	st_num4	Character	4
26	Open	st_tax	Character	11
27	Class Code 1	st_mod1	Character	5		{R} 0 (models table)
28	Class Code 2	st_mod2	Character	5		{R} 0 (models table)
29	Class Code 3	st_mod3	Character	5		{R} 0 (models table)
30	City Tax	st_cttax	Numeric	7	3
31	State Tax	st_sttax	Numeric	7	3
32	Open	st_thrsh	Numeric	13	2
33	Open	st_dflt	Numeric	1		{R) 0
34	Pre-pack code	st_siz	Character	3		{R} 000 (siz table)
35	Open	st_bnam	Character	30
36	Open	st_bnum	Character	20
37	New Field	st_nod	Numeric	6
38	New Field	st_tm	Time			hh:mm
39	New Field	st_close	Date			{R} mm/dd/yy Default **/**/**
40	New Field	st_man	Character	40
41	New Field	st_vol	Numeric	9
42	New Field	st_invd	Numeric	13	2
43	New Field	st_shk	Numeric	13	2
44	Address Line 2	st_ad2	Character	30
45	Country/State code	st_cs	Character	5
46	Denotes the store’s sales volume / index	st_vol2	Character	10
47	Store Fax Number	st_fax	Character	10
48	Selling Capacity	st_cap	Character	11
49	Stockroom Capacity	st_stock	Character	10
50	Total Square Footage	st_foot	Numeric	6
51	French Store Indicator	st_fdescr	Character	1
52	Country Code	st_cou	Character	3
53	Tax Threshold	st_thrsh2	Numeric	13	2
54	Additional Tax Rate	st_tax2	Numeric	7	3
55	Send Sale Indicator	st_sndsal	Character	1		Denotes whether the store uses Fulfill It (send sale) Y/N
56	Register’s O/S	st_regos	Character	254		Identifies the operating system on the register for each store (i.e. 5x, 6x etc.).
57	Charge Send Group Number	st_csgnum	Numeric	6
58	Web Inventory Indicator	st_webinv	Character	1		Denotes whether to nclude in web interface Y/N
59	Consignment Flag	st_consign	Character	1
60	mPOS Only Flag	st_mposonly	Character	1		Used for internal purposes
61	Store GL #1	st_gl1	Character	20
62	Store GL #2	st_gl2	Character	20
63	Store GL #3	st_gl3	Character	20
64	Store GL #4	st_gl4	Character	20
65	Store GL #5	st_gl5	Character	20
66	Address Line 3	st_ad3	Character	30
67	EDI Qual ID	st_qualid	Character	20
68	Social Media Link 1	st_social_media1	Character	256
69	Social Media Link 2	st_social_media2	Character	256
70	Social Media Link 3	st_social_media3	Character	256
71	Time zone	st_timezone	Character	40		Time zone where the store resides
72	Store pickup	st_pickup	Character	1		Store pickup available for Fulfill It
73	Local Delivery	st_locdlv	Character	1		Local delivery available for Fulfill It
74	Curbside Pickup	st_curb	Character	1		Curbside pickup available for Fulfill It
75	Store EDI Qualifier	st_qualid_fulfill	Character	20		EDI qualifier for store-initiated fulfillment
76	Web EDI Qualifier	st_qualid_web	Character	20		EDI qualifier for web-initiated fulfillment
77	Country Code	st_cn	Character	3
78	Ecomm Indicator	st_ecomm	Character	1		Denotes whether the store is an Ecomm store (Y/N)
79	Foreign Key	st_foreign	Numeric	6		Foreign Web Fulfillment Store #
80	Backorder Eligible Indicator	st_bkord_eligible	Character	1		(Y/N). Y value indicates to send records associated with this SKU/article in On-Order Data Feed that will be used by Web for back orders. Please consult with your CSM regarding this functionality. NOTE: This field/function applies only to Web processes and does not apply to POS.

Table Name: client (Store Group 1)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Client Number	cl_num	Numeric	6		{R} 100 = Retail Store 101 = Outlet Store 102 = POP UP store 333 = Kit Location 600 = Bulk Retail 601 = Bulk Outlet 999 = Warehouse 998 = Outlet Warehouse 0 = Phantom Store
3	Corporation Name	cl_corp	Character	30
4	Client Name	cl_nam	Character	30
5	Address	cl_ad	Character	30
6	City	cl_cy	Character	20
7	State	cl_st	Character	2
8	Zip	cl_zp	Character	9
9	Telephone	cl_tel	Character	10
10	Rep Number	cl_rep	Numeric	6		{R} 0

Table Name: rep (Store Group 2)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Rep Number	rep_num	Numeric	6		{R}
3	Corporation Name	rep_corp	Character	30
4	Rep Name	rep_nam	Character	30
5	Address	rep_ad	Character	30
6	City	rep_cy	Character	20
7	State	rep_st	Character	2
8	Zip	rep_zp	Character	10
9	Telephone	rep_tel	Character	10

Table Name: tax

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Store #	stc_tx	Numeric	6		{R} KWI six-digit store number – matches back to the Stores table
3	Date	ym_tx	Date			{R} mm/dd/yy
4	Time	tm_tx	Time			{R} hh:mm
5	Gross Sale	pr_tx	Numeric	11	2	{R} The total gross amount of the transaction. Allow negative amounts for returns.
6	Tax	tx_tx	Numeric	11	2	{R} The total tax amount associated to the transaction
7	Tax code	code_tx	Numeric	6		{R} Defaulted to 0
8	Transaction Number	trn_tx	Numeric	6		{R} The transaction # to which the record is associated.
9	Terminal (register) Number	trm_tx	Numeric	6		{R} The register terminal # to which the record associated.
10	Web order line #	inum_tx	Numeric	9		Stores the web order number line number if the client is utilizing an eCommerce interface.

Table Name: tax_details

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Tran table name	table_itx	Character	30		Table that this row is related to. For example: sales, credit, nmc
3	Store #	stc_itx	Numeric	6		KWI 6-digit store number – matches back to the Stores table
4	Date	ym_itx	Date			{R} mm/dd/yy
5	Terminal	trm_itx	Numeric	6		The register terminal # to which the sales record is associated. Each register has a unique number. mPOS has its own number. This can be used for history.
6	Transaction	trn_itx	Numeric	6		The transaction # to which the sales record is associated. There is a unique transaction number for each sale. The receipt is unique by date, terminal, and transaction number.
7	Item Tax ID	itax_itx	Numeric	9		Links to the sales/credit/nmc tables
8	Description	desc_itx	Character	30		Description
9	Item Price	pr_itx	Numeric	11	2	{R} the net value of the item sold
10	Item #	itm_itx	Character	30		{R} UPC #
11	Tax Rate	rate_itx	Numeric	11	5	The tax rate for the item.
12	Tax Exempt ID	exemptid_itx	Character	20		Captures the tax-exempt ID

Table Name: timezone

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Time zone Code	code_tz	Character	60
3	Time zone name	name_tz	Character	80

Table Name: traffic

Field #	Rowid	KWI Field Name	Numeric		Length	Decimal		KWI Internal (auto- increments unique field)
1	Rowid	Rowid	Numeric		9			KWI Internal (auto- increments unique field)
2	Store	stc_tc	Numeric		6			{R}
3	Date	ym_tc	Date					{R} Date mm/dd/yy
4	Hour #1	h01_tc	Numeric		13	2
5	Hour #2	h02_tc	Numeric		13	2
6	Hour #3	h03_tc	Numeric		13	2
7	Hour #4	h04_tc	Numeric		13	2
8	Hour #5	h05_tc	Numeric		13	2
9	Hour #6	h06_tc		Numeric	13		2
10	Hour #7	h07_tc		Numeric	13		2
11	Hour #8	h08_tc		Numeric	13		2
12	Hour #9	h09_tc		Numeric	13		2
13	Hour #10	h10_tc		Numeric	13		2
14	Hour #11	h11_tc		Numeric	13		2
15	Hour #12	h12_tc		Numeric	13		2
16	Hour #13	h13_tc		Numeric	13		2
17	Hour #14	h14_tc		Numeric	13		2
18	Hour #15	h15_tc		Numeric	13		2
19	Hour #16	h16_tc		Numeric	13		2
20	Hour #17	h17_tc		Numeric	13		2
21	Hour #18	h18_tc		Numeric	13		2
22	Hour #19	h19_tc		Numeric	13		2
23	Hour #20	h20_tc		Numeric	13		2
24	Hour #21	h21_tc		Numeric	13		2
25	Hour #22	h22_tc		Numeric	13		2
26	Hour #23	h23_tc		Numeric	13		2
27	Hour #24	h24_tc		Numeric	13		2

Table Name: transaction_status

This table logs the status of transactions.

Field #	Rowid	Rowid	Numeric	Length	Decimal	KWI Internal (auto- increments unique field)
1	Rowid	Rowid	Numeric	6		KWI Internal (auto- increments unique field)
2	Store	stc_ts	Date			Store
3	Date	ym_ts	Numeric	6		Date
4	Terminal #	trm_ts	Numeric	6	2	Terminal #
5	Transaction #	trn_ts	Character	1	2	Transaction #
6	Offline indicator	isOffline_ts	Character	1	2	Y if the transaction was in offline mode
7	Terminated indicator	isKill_ts	Character	32	2	Y if the transaction was terminated
8	Receipt Choice	receipt_ts	Numeric	11	2	Receipt choice (PRINT, EMAIL, BOTH)

Table Name: transfer

Store To Store: 2 records are required to complete the transfer for Store to Store In the example below a transfer is taking place from store #1 to Store #2.

Store To Phantom location: Transfers to phantom locations will only have 1 record (out: “From” and “At” locations are the same with “To” being the phantom location)

Store To Warehouse: One record is written to the transfer table

Record Description	From Location	To Location	Transacted at
Out	Store #1	Store #2	Store #1
In	Store #1	Store #2	Store #2

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Article Number	art_tr	Character	10		{R}
3	Quantity	quan_tr	Numeric	6		{R}
4	From location	orig_tr	Numeric	6		{R} Transfer out from location and transfer at location will match
5	To Location	dest_tr	Numeric	6		{R}
6	Date Transferred	ym_tr	Date			{R} mm/dd/yy
7	Transfer Number	num_tr	Numeric	9
8	Transacted at	reg_tr	Numeric	6		{R}
9	Reason	reas_tr	Character	1		Not used
10	Employee Number	emp_tr	Numeric	6
11	Time	tm_tr	Time			{R} hh:mm
12	Transaction Number	trn_tr	Numeric	9
13	Terminal Number	trm_tr	Numeric	9
14	Comment Field	comm_tr	Character	256
15	Box Number	uid_tr	Character	14
16	Suggested Transfer Number	tmsug_tr	Numeric	6
17	Line Number	inum_tr	Numeric	9
18	Customer Number	cust_tr	Character	10

Table Name: whtran (Warehouse Transfer Header)

Created because of receiving store transfers into the warehouse.

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Warehouse Transfer ID	num_wh	Numeric	6
3	From Store	stc_wh	Numeric	6		{R}
4	Date	ym_wh	Date			{R} mm/dd/yy
5	Warehouse location	whc_wh	Numeric	6		{R}
6	Reference #	ref_wh	Numeric	6
7	Open Field #1	comm_wh	Character	60
8	Open Field #2	reas_wh	Character	10
9	Transaction Number	trn_wh	Numeric	6

Table Name: wht (Warehouse Transfer Detail)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Article #	art_wt	Character	10		{R}
3	Quantity	quan_wt	Numeric	6		{R}
4	Warehouse transfer ID	num_wt	Numeric	6
5	Line Number	inum_wt	Numeric	9

Table Name: models (Vendors)

The models table contains vendor (supplier) information for merchandise items.

{R} = Required field

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto- increments unique field)
2	Vendor Number	modelc	Character	5		{R}
3	Vendor Name	model	Character	20		{R}
4	Address1	modela1	Character	30
5	Address2	modela2	Character	30
6	City.	modelct	Character	20
7	State	modelst	Character	2
8	Zip code	modelzi	Character	10
9	Country	modelco	Character	5
10	Phone #	modelte	Character	20
11	Fax #	modelfa	Character	20
12	Terms	modeltr	Character	20
13	Vendor Contact	modelcon	Character	20
14	Rep Name	modelrna	Character	20
15	Rep Address	modelrad	Character	30
16	Rep City	modelrct	Character	20
17	Rep State	modelrst	Character	2
18	Rep Zip	modelrzp	Character	10
19	Rep Phone #	modelrtl	Character	20
20	Rep Fax #	modelrfx	Character	20
21	Discount	modeldis	Character	20
22	Freight Terms	modelfrt	Character	20
23	EDI ID	modelnsd	Character	20
24	Terms	modelnst	Character	20
25	Vendor Minimum	modelmin	Numeric	13	2	0.00
26	Order To Rep	modelotr	Character	3
27	Return Policy	modelret	Character	20
28	Special Instructions #1	modelsi1	Character	60
29	Special Instructions #2	modelsi2	Character	60
30	Special Instructions #3	modelsi3	Character	60
31	Free Freight Minimum	modelfmn	Numeric	13	2	{R} 0.00
32	EDI Flag	modeledi	Character	1
33	Open Field 1	modelcod	Character	12
34	Open Field 2	modelcol	Character	2		Department for 832 EDI
35	Open Field 3	modelpat	Character	3
36	Open Field 4	modeltyp	Character	3
37	Open Field 5	modelscl	Character	3
38	Open Field 6	modelfto	Character	20
39	Open Field 7	modelfti	Character	20
40	E-mail address	modeleml	Character	50
41	Open Field 8	modelap	Character	6
42	Open Field 9	modelid	Character	20
43	Open Field 10	modelldt	Numeric	2
44	Phone extension	modelext	Numeric	4
45	Open Field 11	modelrxt	Numeric	4
46	Special Instructions #4	modelsi4	Character	60
47	Comments	modelrem	Character	60
48	Cost Factor – Brokerage %	modelbr	Numeric	9	2
49	Cost Factor – Freight %	modelfgt	Numeric	9	2
50	Cost Factor – Duty Rate %	modeldut	Numeric	9	2
51	Cost Factor – Other %	modelotd	Numeric	13	2
52	Cost Factor – Other Dollars	modelotp	Numeric	9	2
53	Open	modelbac	Character	1
54	Open	modelshw	Character	30
55	Open	modelrep	Character	30
56	Special Instructions #5	modelsi5	Character	60
57	Special Instructions #6	modelsi6	Character	60
58	Special Instructions #7	modelsi7	Character	60
59	Special Instructions #8	modelsi8	Character	60
60	Currency code	modelcu	Character	3		Linked to ‘curren’ table
61	Country-State	modelcs	Character	5
62	Foreign/Domestic flag	modelfd	Character	1
63	Tin	modelvt	Character	20
64	CST #	modelcst	Character	20
65	Inco-Term	modelit	Character	3		Linked to ‘incoterm’ table
66	Pay-term	modelpt	Character	4		Linked to ‘payterm’ table
67	Flag to include in 852	modelrpt	Character	1
68	EDI VAN	modvan	Character	20		Value Added Network for EDI
69	EDI 852 Control Flag	modedi852	Character	1		Controls what info we send in the EDI 852 file. Options: B – Both Sales amount and Inventory available S – Sales amount only I – Inventory available only ​(Default is B)
70	Number of days to deactivate an open order	models_ord_deact	Numeric	6		Controls the number of days to close out an open order for each vendor.

Table Name: artven (Secondary Vendor)

One record per secondary vendor per store)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_ve	Numeric	6		System generated sequential number
3	Store	stc_ve	Numeric	6		{R} KWI store number
4	Vendor Number	modc_ve	Character	5		{R} KWI vendor number
5	Update Date	updated_ve	Datetime			Time stamp of the most recent row update

Table Name artvc (Secondary Vendor Items)

One record per secondary vendor per item)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Number	num_vc	Numeric	6		Must match table artven – num_ve values
3	Article number	art_vc	Character	10		{R}
4	Cost	cost_vc	Numeric	13	2	{R}
5	Pre-pack Quantity	ppqty_vc	Numeric	6		{R}
6	Active code	act_vc	Character	1		{R} Y/N
7	Part Number	partno_vc	Character	25
8	Update Date	updated_vc	Datetime			Time stamp of the most recent row update.

Table Name: pending (Web Site Orders)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	9		KWI Internal (auto-increments unique field)
2	Order Number	num_pn	Numeric	9		Merchant order number
3	Order Source	src_pn	Character	10
4	Order Date	orddt_pn	Date			Date order was placed
5	Order Time	ordtm_pn	Time			Time order was placed
6	Order Sales Tax	ordtx_pn	Numeric	11	2	Total order tax amount
7	Order Ship Charge	ordsc_pn	Numeric	11	2	Total order shipping
8	Order Total	ordpr_pn	Numeric	11	2	Merchandise Total from field 32
9	Bill to First Name	bname_pn	Character	30		First name of purchaser
10	Bill to Street Address 1	badd1_pn	Character	30		Street address line one
11	Bill to Street Address 2	badd2_pn	Character	30		Street address line two
12	Bill to City	bcity_pn	Character	30		City of purchaser
13	Bill to State	bstt_pn	Character	50		State of purchaser
14	Bill to Zip	bzip_pn	Character	10		Zip code of purchaser
15	Bill To Country	bcntr_pn	Character	10		Country code of purchaser
16	Bill to Telephone	bphne_pn	Character	14		Daytime telephone of purchaser Format: exactly how it appears (without formatting)
17	Bill to Email Address	bemal_pn	Character	60		Email address of purchaser
18	Do Not Promote	dnp_pn	Character	10
19	Payment Type #1	cctyp_pn	Character	20		Type of payment used: Visa, MasterCard, Amex, Discover, Diners Club, Gift Card or Pay Pal.
20	Payment Type #1Number	ccnum_pn	Character	25		Purchasing card number Last 4 digits Only – G/C Full Number
21	Payment Type #1Expires	ccexp_pn	Character	10		Expiration date of purchasing card (mm-month; yy-year)
22	Payment Type #1Authorization Code	acode_pn	Character	15		Auth code obtained from payment processor
23	Payment Type #1 Authorization Date	adate_pn	Date			Date credit transaction was authorized
24	Vendor Code	vendr_pn	Character	10
25	Item Reference #	inum_pn	Numeric	9	0	Line item # of each Order. Incremental Counter that starts at one and increases per each line (if order # is 500, then line items would be 1, 2, 3, 4, 5 within order # 500) for order # 501, line items would be 1, 2, 3 within order number 501
26	Item ID (sku)	art_pn	Character	10		Item sku – For Appeasement use the word “APPEASE”
27	Item Description	desc_pn	Character	30		Description
28	Personalization	prs_pn	Character	10		Pass 0 since it is for Client.
29	Personalization Charge	prspr_pn	Numeric	11	2	Charge for personalizing or Gift Wrap No Charge = 0.00 This charge is at the line item. non- merchandise charge gift wrap / personalization is not refundable.
30	Size	size_pn	Character	10
31	Item Color	colr_pn	Character	10
32	Style	styl_pn	Character	20
33	Item Price	rp_pn	Numeric	11	2	Item Price will override the article/item table. Allows web site to have different price than Retail systems.
34	Item Sales Tax	itax_pn	Numeric	11	2	Item Tax
35	Item Ship Charge	isc_pn	Numeric	11	2
36	Item Quantity	qty_pn	Numeric	6		Quantity ordered
37	Item Ship Method	smeth_pn	Character	10		Standard, two days, overnight
38	Ship to First Name	sname_pn	Character	20		Recipient First name
39	Ship to Street Address 1	sadd1_pn	Character	30		Street Address of recipient
40	Ship to Street Address 2	sadd2_pn	Character	30		Street Address of recipient
41	Ship to City	scity_pn	Character	20		City of recipient
42	Ship to State	sstte_pn	Character	50		State of recipient
43	Ship to Zip	szip_pn	Character	10		Zip code of recipient
44	Ship to Telephone	sphne_pn	Character	14		Daytime telephone of recipient
45	Ship to Country	scntr_pn	Character	15		Country code of recipient
46	Item level has been processed with a confirm record Ship / Return, Cancel.	act_pn	Character	1		Y = Open N = Closed R = Return C = Cancel
47	Customer #	cust_pn	Character	10		Client Customer # to populate KWI customer open field.
48	Payment Type #1 Total	cctot_pn	Numeric	11	2	Payment type total
49	Payment Type #2	c2typ_pn	Character	20		Type of payment used: Visa, MasterCard, Amex, Discover, Diners Club, Gift Card or Pay Pal
50	Payment Type #2 Number	c2num_pn	Character	25		Purchasing card number Last 4 digits Only – G/C full number
51	Payment Type #2 Expires	c2exp_pn	Character	10		Does not apply for Gift Cards
52	Payment Type #2 Authorization Code	acod2_pn	Character	15		Does not apply for Gift Cards
53	Payment Type #2 Authorization Date	adat2_pn	Date			Does not apply for Gift Cards
54	Payment Type #2 Total	c2tot_pn	Numeric	11	2	Payment type total
55	Decrementing Location	dec_pn	Character	6		Leave Blank The Decrementing location will be sent in the notification file
56	Type of order	otyp_pn	Character	20		If web order then “WEB”
57	Ship to Email Address	semal_pn	Character	60		Email address of Recipient
58	Opt Out	opt_pn	Character	1		Send Y/N to turn on/off opt in/out status for customers.
59	Payment Type #3	c3typ_pn	Character	20		Type of payment used: Visa, MasterCard, Amex, Discover, Diners Club, Gift Card or Pay Pal
60	Payment Type #3 Number	c3num_pn	Character	25		Purchasing card number Last 4 digits Only – G/C full #
61	Payment Type #3 Expires	c3exp_pn	Character	10		Does not apply for Gift Cards
62	Payment Type #3 Authorization Code	acod3_pn	Character	15		Does not apply for Gift Cards
63	Payment Type #3 Authorization Date	adat3_pn	Date			Does not apply for Gift Cards
64	Payment Type #3 Total	c3tot_pn	Numeric	11	2	Payment type total
65	Bill to Last Name	blnam_pn	Character	30		Last Name of purchaser
66	Ship to Last Name	slnam_pn	Character	30		Recipient Last name
67	Order header has been processed with a Ship confirm	act2_pn	Character	1		Y/N
68	Order Header has been processed with a Return	act3_pn	Character	1		Y/N
69	Shipping Tax	shtx_pn	Numeric	11	2
70	Sales Promotion Number	prm_pn	Character	10
71	Actual Shipping Charges	actsh_pn	Numeric	7	2
72	Actual Shipping Weight	actwt_pn	Numeric	9	2
73	Ship Date	shdt_pn	Date			Actual Ship Date
74	Item Ship Method	ishme_pn	Character	5		Item Ship Method
75	Pkg Tracking Number	pkgnm_pn	Character	16		Pkg Tracking Number
76	Return Date	rtndt_pn	Date			Return Date
77	Form of Payment Remainder 1	ccrem_pn	Numeric	13	2	Form of Payment Remainder 1
78	Form of Payment Remainder 2	c2rem_pn	Numeric	13	2	Form of Payment Remainder 2
79	Form of Payment Remainder 3	c3rem_pn	Numeric	13	2	Form of Payment Remainder 3
80	Payment Type #4	c4typ_pn	Character	20
81	Payment Type #4 Number	c4num_pn	Character	25
82	Payment Type #4 Expires	c4exp_pn	Character	10
83	Payment Type #4Authorization Code	acod4_pn	Character	15
84	Payment Type #4 Authorization Date	adat4_pn	Date			{R} Default = **/**/**
85	Payment Type #4 Total	c4tot_pn	Numeric	11	2
86	Form of Payment Remainder 4	c4rem_pn	Numeric	13	2
87	Payment Type #5	c5typ_pn	Character	20
88	Payment Type #5 Number	c5num_pn	Character	25
89	Payment Type #5 Expires	c5exp_pn	Character	10
90	Payment Type #5 Authorization Code	acod5_pn	Character	15
91	Payment Type #5 Authorization Date	adat5_pn	Date			{R} Default = **/**/**
92	Payment Type #5 Total	c5tot_pn	Numeric	11	2
93	Form of Payment Remainder 5	c5rem_pn	Numeric	13	2
94	Order Level Shiipping Charge Booked	oscbk_pn	Character	1		Y/N
95	Gift Card /Certificate Number	gcnum_pn	Character	20		For issuing of a Gift Card/Certificate
96	Originating Store Number	ostc_pn	Numeric	6
97	Shipping Return Fee	retsc_pn	Numeric	11	2
98	KWI Field	kwiln_pn	Numeric	6		{R} Default = 0
99	Original Order Number	oms_pn	Numeric	9
100	Back Ordered Ref #	BOrefnum_pn	Character	30
101	Discount Order Amt	orddiscamt_pn	Numeric	13	2
102	Discount Item Amt	itmdiscamt_pn	Numeric	13	2
103	Discount Ship Amt	shipdiscamt_pn	Numeric	13	2
104	Discount Ship Reason Code	shipdiscreascod e_pn	Character	8
105	Discount Ship Msg	shipdiscmsg_pn	Character	60
106	Loyalty Flag	loyaltyflag_pn	Character	1
107	Discount Order Msg	orddiscmsg_pn	Character	60
108	Discount Order Reason Code	orddiscreascode _pn	Character	8
109	Discount Item Msg	itmdiscmsg_pn	Character	60
110	Discount Item Reason Code	itmdiscreascode _pn	Character	8
111	Unique OMS Number	oms2_pn	Character	12
112	FulfillIT Number	numss_pn	Character	6
113	Salesperson	salesperson_pn	Numeric	6
114	Clerk	clerk_pn	Numeric	6
115	Child Customer #	childcust_pn	Numeric	10
116	Internal Order Number	internalordnum_ pn	Character Character	240
117	Ship To Code	shiptocode_pn	Character	20
118	Gift Message	giftmessage_pn	Character	250
119	Gift Wrap	giftwrap_pn	Character	20
120	External Warehouse	ATSRB_pn	Character	10		Indicates whether the item will be supplied from an external warehouse (ATS or RB)
121	Bill to Company Name	bcompanynam_ pn	Character	60
122	Ship to Company Name	scompanynam_ pn	Character	60
123	Date/Time of load	loaddatetime_p n	TimeStamp
124	OMS Order Number	omsitem2_pn	Character	20		OMS order number (i.e., internal/alternate order number)
125	Personalization Information	prsjson_pn	JSON			JSON object representing a personalization to an item

Table Name: pending_returns (Web Site Returns)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	Pending Rowid	pending_rowid_pnr	Numeric	11		Reference to the rowid in the pending table
3	Order Number	num_pnr	Numeric	9		Merchant order number
4	Item Reference #	inum_pnr	Numeric	9	0	Line item # of each Order. Incremental Counter that starts at one and increases per each line (if order # is 500, then line items would be 1, 2, 3, 4, 5 within order # 500) for order # 501, line items would be 1, 2, 3 within order number 501
5	Return Item Price	retrp_pnr	Numeric	11	2	Item Price
6	Return Item Sales Tax	retitax_pnr	Numeric	11	2	Item Tax
7	Return Item Ship Charge	retshppr_pnr	Numeric	11	2
8	Return Shipping Tax	retshptx_pnr	Numeric	11	2
9	Payment Type #1	retcctyp1_pnr	Character	20
10	Payment Type #1 Total	retcctot1_pnr	Numeric	11	2
11	Payment Type #1 Number	retccnum1_pnr	Character	25
12	Payment Type #2	retcctyp2_pnr	Character	20
13	Payment Type #2 Total	retcctot2_pnr	Numeric	11	2
14	Payment Type #2 Number	retccnum2_pnr	Character	25
15	Payment Type #3	retcctyp3_pnr	Character	20
16	Payment Type #3 Total	retcctot3_pnr	Numeric	11	2
17	Payment Type #3 Number	retccnum3_pnr	Character	25
18	Payment Type #4	retcctyp4_pnr	Character	20
19	Payment Type #4 Total	retcctot4_pnr	Numeric	11	2
20	Payment Type #4 Number	retccnum4_pnr	Character	25
21	Payment Type #5	retcctyp5_pnr	Character	20
22	Payment Type #5 Total	retcctot5_pnr	Numeric	11	2
23	Payment Type #5 Number	retccnum5_pnr	Character	25

Warehouse Inventory File Layout (whinv)

The file is a pipe ( | ) delimited ASCII file. The fields are as follows:

Field 1: Client (Values are 100, 101 or 999)

• 100 – Full price Retail Stores with this client number own the inventory
• 101 – Outlet Stores with this client number own this inventory
• 999 – This inventory is not allocated

Field 2: Style Number

Field 3: Color Code Field 4: Size Code

Field 5: UPC Number (The entire 12-digit UPC is needed)

Field 6: Warehouse On-Hand

Field 7: Leave as a 0, filler field

Field 8: Leave as a 0, filler field

Field 9: Due In, this is the qty that is due into the warehouse within the next 2 weeks. It is not a mandatory field. If you do not wish to use it, leave at zero.

Field 10: Warehouse On-Order

Table Name: warehouse_bins (Warehouse Bin Location)

Field #	Field Name	KWI Field Name	Type	Length	Decimal	Description / Format
1	Rowid	Rowid	Numeric	11		KWI Internal (auto-increments unique field)
2	UPC/Article #	art_wb	Character	10		KWI 10-digit sku ID – matches back to the articles table
3	Store #	stc_wb	Numeric	6		KWI 6-digit store number – matches back to the stores table
4	bin_wb	ym_itx	Character	30		Bin ID

Appendix

Tables Updated During Sales Processing

The following tables are updated during the sales process.

• SALES
• CREDIT
• TAX
• NMC
• FOP
• MEDIA
• GIFT
• UKSALES – For POS Register Only
• UKCREDIT – For POS Register Only
• CLOSE

Store – Receiving, Transfers and Inventory

• STOCK
• TRANSFER
• PURCH