Documentation Manual

Cohesion Technology was originally developed to provide an application framework for developing enterprise software.

Many of the ideas were formed from ideas developed for the Defense Advanced Research Projects Agency (DARPA).

Project development originally started in 2000 and has evolved since that time.

The following primary requirements still hold:

• Maximum Performance
• Strong Security
• Lower Maintenance Costs
• Platform Independence

The following features were added to help meet and exceed the above requirements:

• Record Based Caching
• Role Based Security
• Database Object Mapping

The current core framework including the generic components now consists of 150K lines of source code after removing comments and blank lines.

Most of the major modules now support large scale e-commerce systems.

The system has kept to the original objectives pretty well, and many of the minor support features have become very robust over time.

Because many errors can be caught at compile time, there have been a few major releases where the things that worked well were were made standard and things that didn't work as well were factored out to minimize any legacy code.

For example a simple caching system has evolved into a multi-level secure write-through cache that supports a variety of caching strategies based on the overall number of records, and access statistics along with several reports to evaluate cache effectiveness.

There has been several applications constituting well over 100K lines of custom development built on top of Cohesion Technology so the framework has proven to be very mature.

1.1  Technology - Technical Overview

The actual technology used is rarely important to the end user but is a factor in overall cost.

Cohesion is built using proven technologies and tools with very broad development support.

Building on the wrong technologies could result in diminished future support and performance options.

Why Java?

The Java programming language was chosen above others primarily because it is a type-safe language.

Type-safe languages are evaluated during compile time verses runtime whether call parameters are correct.

Other type-safe languages include C and C++. The source code is compiled to an executable form before being run.

Scripting languages can't be checked for validity until runtime, which means everything has to be tested when a major change is made instead of catching most of the problems at compile time.

Example scripting languages are PHP, Python, Javascript, and Perl. Each of these has their strengths and weaknesses, but for large enterprise development, compile-time checking is a must.

Java J2EE

The entire system is written using the proven Java development language and utilizes the J2EE Servlet stack.

By choice it does not use all of the functionality offered in J2EE.

It was recently learned that e-Bay also adopted J2EE but only selected the same parts.

A large number of third-party libraries are also used for database drivers, e-mail, etc.

Portability

Maximum portability has been maintained by staying away from proprietary or server specific extensions.

Has been successfully run with a variety of leading application servers on a variety of operating systems.

Performance

By keeping the integration simple, the memory foot print is also far less than what would be expected.

Smaller servers written on our platform are often deployed with as little as 30MB of Java runtime memory.

A very large, very active system may be deployed successfully with upwards of 150MB and run without incident.

Keeping the memory size down means it is easier to deploy multiple application servers.

Lower memory usage means more efficient garbage collection and better overall performance.

1.2  Database - Database Design

There are several good database servers to choose from, but our database of choice is Microsoft SQL Server.

One reason being that the query optimization and performance for complex joins has proven to be very fast.

The second reason is the long-standing support for foreign keys. MySQL now supports foreign keys as well.

MySQL is used for simpler databases or ones with smaller tables, and does a good job with high-transaction rates.

Foreign Keys

A foreign key is a database constraint that is defined between two database tables.

Constraints typically represent real world relationships.

For example every address record in a database exists within one country. By defining a foreign key relationship to a countries table it would not be possible to add an address with a new country code unless it was first added to the countries table. In addition, a country could not be removed from the countries table unless there were not any addresses that used that country. So the database maintains referential integrity of the data so you never get references to data that doesn't exist.

Persistent Objects

There are now several persistent object schemes available for storing object data in a relational database.

Some schemes start with the object, and map the field/data to a small number of generic tables.

Cohesion instead starts with the database table with all of the required fields then generates an object that maps to a record.

So when a change is needed, the database is modified and the object is regenerated from the database schema information.

This provides the maximum level of type-safe error checking at compile time. If a database field changes in anyway the code won't compile correctly until it is fixed. This is a huge advantage over having to manually review and then test every piece of code that might use that database field.

The way fields get mapped to the database records has also proven to be highly resilient to adding new database fields to a live database, making upgrades very simple.

1.3  Editor - Generic Cohesion Editor

The generic "Cohesion Editor" is used to create, edit, search, and delete database records in a uniform way. User access rights control what operations can be peformed on each database table.

Search - Available when the current user has select privileges on the table. Boolean searches treat values of yes, true, and 1 are all treated as true. Date searches allows dates in 'mm/dd/yy' format. Date searches can also be relative to the current date by entering a numeric value.

New - Available when the current user has insert privileges on the table. Fields marked with a * have to be entered when creating a new record. Some fields may appear during record creation that will not be editable after the record is created if the field is required and the account does not have edit privileges on that field.

Edit - Available when the current user has update privileges on the table. Only fields that the can be edited by the current user will be editable. When a record is being viewed or modified, any related data is displayed on the right side of the screen.

Delete - Available when the current user has delete privileges on the table.

Next - Will take the editor to the next sequential record in the database. Available when the current user has select privileges on the table.

Prev - Will take the editor to the previous sequential record in the database. Available when the current user has select privileges on the table.

First - Will take the editor to the first sequential record in the database. Available when the current user has select privileges on the table.

Last - Will take the editor to the last sequential record in the database. Available when the current user has select privileges on the table.

Records often depend on records from other tables before they can be created, e.g. an address record is not valid unless it is associated with a person. This often requires a records to be created in a specific order, e.g. the person record has to exist before address records. When records are deleted, it can require deletion of other records, e.g. the person can not be deleted until the address record is deleted also.

1.4  Reports - Generic Query Reporter

A generic reporting tool has been developed to make it easy and consistent to manage multiple reports.

Any database query can be turned into a report.

Report Options

• Column Visibility
• Column Sorting
• Paging of Long Queries
• Adding a Column Total
• Showing Sub-Totals
• Cell Formatting
• Grid Form Elements
• Drill-Down Links

Date Reports

Another standard report is a date report.

This report is ideally suited to summarizing data grouped by a common date interval such as a day, week, month, or year.

The date report will also do a trailing summary for any number of days. Most reports default to viewing the last 10 days activity.

Depending on the date resolution, additional fields can be displayed. For example at a yearly view you may only get monthly summaries, but when you drill all the way down to a day, you may see the individual records.

1.5  Caching - Advanced Record Level Cache

The Cohesion Cache supports multi-level access controls that limit the visibility to database table rows based on account.

This allows two separate accounts to access the same table and share no records or only some records in common between the accounts.

The cache has been highly tuned to reduce round-trip requests to the database.

In some cases a Cache pre-fetch method may need to be used to speed up data processing.

Sparse cache fetch queries are used where possible to request the minimum information needed.

Data that is not accessed is free'd up from the cache until it is accessed again.

Very small tables may be pulled entirely into cache since it may be easier, but larger tables may only have one record up to a small percentage of the records available in cache at any given time.

Cache coherency is maintained across multiple servers by using a shared broadcast channel.

1.6  Security - Ground-Up Access Controls

Security was designed from the very beginning instead of trying to fit it in at the end.

Role Based Security

A role based security system is provided that defines access levels to one or more modules.

Each module contains a collection of pages, tables, reports, and tools.

Security profiles are defined for each level that can be supported within a module.

Often there may be only one level defined for a module since all users have access to the same things within it.

A profile is managed with a hierarchy of check boxes for different access rights.

If a role doesn't have access to a module, then it will not appear in the menu options at all.

None of the tools or tables will be accessible and any external references to the module will not be linked.

The contact directory is used to store all contact information for all users, customers, clients, suppliers, etc.

A unified contact directory makes assures that information does not need to be stored into any other location.

Tools are provided to consolidate contact information that may have been duplicated.

Special Fields

Each contact contains a primary and secondary e-mail address, name information, password, and notes.

Many tables contain a contact reference and will be shown anytime the respective contact is viewed.

Contact records also have tags to identify sub-sets, for example the source of the contact information.

Addresses

Addresses are stored in a separate table so each contact may have an unlimited number of Addresses.

Each address has a latitude and longitude so that it may be geographically located.

Phones

Phone numbers are stored in a separate table so each contact may have an unlimited phone numbers.

2.1  Reports

2.2  Tables

2.2.1  Addresses - Contact Address Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Contact Id	int		Y	Y	Foreign Key to Contacts
2	Address Type Id	short		Y	Y	Foreign Key to Address Types
3	Residential	boolean		Y
4	Address	char(255)		Y
5	City	char(80)		Y	Y
6	State	char(2)		Y	Y	Foreign Key to States
7	Postal Code	char(20)		Y	Y
8	Country	char(2)		Y	Y	Foreign Key to Countries
9	Verified	boolean
10	Avs	short		Y

2.2.2  Contacts - Contact Directory Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Last Name	char(40)	Y	Y	Y	Name Field
2	First Name	char(40)	Y	Y	Y	Name Field
3	E-Mail	char(80)	Y	Y	Y
4	E Mail2	char(80)	Y	Y	Y
5	Company Name	char(60)	Y	Y	Y	Name Field
6	Web Site Url	char(80)	Y
7	Referred By	char(80)		Y	Y
8	Notes	char(1000)	Y
9	Categories	char(255)	Y
10	Password	char(32)	Y
11	Unsubscribe	short		Y
12	Mail Errors	short		Y
13	Created Date	Date		Y	Y
14	Modified Date	Date		Y	Y

2.2.3  Phones - Contact Phone Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Contact Id	int		Y	Y	Foreign Key to Contacts
2	Phone Type Id	short		Y	Y	Foreign Key to Phone Types
3	Phone Number	char(64)		Y	Y
4	Extension	char(32)		Y	Y

The orders module contains information about all orders that are in process.

Orders may be created manually, or from a variety of sources.

Order States

Orders can arrive in any of the following three states:

• Payment - Any order that is awaiting payment information in order to complete. An example would be an order with a PayPal payment that has not yet cleared. If an order sits in the Payment state for longer than 7 days, it will automatically be canceled.

• Queued - Orders with complete payment information but they require a manual review or have an outstanding balance. Examples would include any order that was not automatically processed because of missing CVV2 information, order size, bad AVS matching, etc.

• Processed - Orders that have complete payment information or have been manually processed will go to a processed state. If the order is processed and would be shipped from inventory it will be advanced directly to fulfillment. Otherwise the order has to be received and processed accordingly.

Up to this point, the processing has been automatic. An order manager is responsible for processing any queued orders and then manually receiving any processed orders. An order in-process may be in any of the following states:

• Received - An order in received status typically requires items to either be ordered or drop-shipped in order to be completed. The Received status is the status any order would be moved to in order to make any modifications including changing items, processing payments, or shipping partials.

• On Order - This is not a distinct status, the order is actually still in a Received status, but any drop-ships or special orders are currently pending so nothing can really be done with this order other until inventory is received. If any action needs to be taken on behalf of this order it would instead appear in Received status.

• Fulfillment - Orders in fulfillment are ready to be shipped or are awaiting a tracking number. When an order first goes to fulfillment it will be queued to be printed. Once the packing slip and pick list have been printed it will not appear with the default print option selected. Only orders on the fulfillment report can be queried by external shipping software.

• In Transit - Once a tracking number is provided, the order automatically moves to a shipped status and no longer appears on the fulfillment report. In Transit is considered shipped, but no delivery date has been confirmed. This list can be reviewed for orders that may have been provided with an invalid tracking number. Orders may also be fulfilled with a reference number that can't be tracked with the carrier.

• Shipped - All orders In Transit or with a delivery date are in a shipped status. Tracking numbers may be provided directly from a shipping terminal, hand-entered into the order, or provided by a supplier in the purchasing module.

3.1  Reports

3.1.1  Daily - Daily Order Vitals

This is a date period report that shows sales for the trailing 10 days by default.

The total revenue and daily averages is displayed for the selected time period.

Average order size and gross profit margin is also calculated for the selected time period.

3.1.2  Payment - Pending Payments on Processed Orders

Safeguards have been created to not allow orders to be processed with a pending payment.

This report provides an audit function to display orders that may have been processed with an incorrect payment total.

Their are so many things that could happen to an order during processing that mistakes may still happen.

One potential issue is that the transaction number for a refund was just never property recorded.

3.1.3  Sessions - Current Site Activity

The sessions report shows all the currently active users on the system including people who are logged in.

The application server supports all the back-office functions as well as the main catalog site.

Different sessions are given different timeouts to keep the list smaller. For example a user who clicks once has a session timeout set to 5 minutes, but as soon as they access a second page it bumps it up to 10 minutes. Web statistics often show that 40% or more visitors typically only view one page. Crawlers are given a very short time since they do not preserve session keys across requests.

The session manager has several features built into it to stop sites from downloading or mirroring the entire site and will actually lockout abusive clients.

3.2  Tools

3.2.1  Fulfillment - Automatically Advance Orders

This displays the status of every item required to fill the currently open orders.

It makes recommendations based on what it knows about each item for generating purchase orders and moving orders to fulfillment.

Different recommendation types get color coded to make it easy to spot the different pending scenarios.

This same information is also displayed on each order when it is viewed in the pending report.

Anything that can be shipped from inventory is automatically moved to the fulfillment queue.

3.2.2  Pending - Pending Order Manager

This is the primary page used by customer service personnel.

This page has several sub-reports to show orders at each stage of fulfillment.

3.2.3  Returns - Return Requisition Manager

Customer returns creates a purchase requisition with the notes so that receiving will know what to do with the returned item.

A payment is queued up for the return so that once the item is received the credit can be processed on the order.

A list of pending returns is shown by default.

3.3  Tables

3.3.1  Orders - Customer Order Records

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Partner Id	short		Y		Foreign Key to Partners
2	Site Id	int		Y	Y	Foreign Key to Sites
3	Customer Id	int		Y	Y	Foreign Key to Contacts
4	Address Id	int	Y			Foreign Key to Addresses
5	Order Date	Date		Y	Y
6	Order State Id	short		Y	Y	Foreign Key to Order States
7	Shipping Method Id	short		Y	Y	Foreign Key to Methods
8	Statement Id	int				Foreign Key to Statements
9	Back Order Id	int		Y	Y	Foreign Key to Orders
10	External Number	char(50)		Y
11	Shipping Cost	double	Y	Y
12	Shipping Total	double	Y	Y
13	Tax Total	double
14	Discount Total	double
15	Order Total	double		Y	Y
16	Customer Total	double			Y
17	Tax Rate	double	Y
18	Tax Shipping	boolean		Y
19	Fulfillment Date	Date		Y
20	Delivered Date	Date		Y	Y
21	Shipping Weight	double
22	Tracking Number	char(120)	Y	Y
23	Customer Notes	char(8000)	Y	Y	Y
24	Shipping Notes	char(8000)	Y
25	Internal Notes	char(8000)
26	Source Key	char(80)
27	Created Addr	char(15)
28	Created Date	Date
29	Modified Date	Date
30	Created By	int
31	Modified By	int

3.3.2  Returns - Item Return (RMA) Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Order Id	int		Y	Y	Foreign Key to Orders
2	Requisition Id	int		Y	Y	Foreign Key to Requisitions
3	Return Type Id	short	Y	Y	Y	Foreign Key to Return Types
4	Credit Amount	double	Y	Y	Y
5	Credit Ship Back	boolean	Y	Y	Y
6	Created Date	Date
7	Modified Date	Date
8	Created By	int
9	Modified By	int

The profit module primarily groups reports on orders that have been shipped.

Example reports include sales-tax collected, and a volume report that can break down sales performance by supplier, brand, line, or item.

4.1  Reports

4.1.1  Regions - Orders by Geographic Regions

Attempts to show a graphic with dots representing an order.

Only addresses that can be geo-located will get displayed.

Brighter colors are shown for areas with more order activity.

4.1.2  Sales Tax - California Sales Tax

Displays total tax collected for all time up to the selected date.

The total is a cumulative total for every order processed up to a given date that can be compared to the last total used to determine franchise tax payments.

A cumulative total is shown since the system does not create daily account transactions like a double-entry accounting system would keep.

4.1.3  Volume - Products Shipped Revenue

Reports on total sales based on line, supplier, brand, or individual item for the selected time period.

The Inventory Manager provides inventory reports and tools to set minimum order amounts and stock level triggers for purchasing.

Standard sized bar-code labels can be printed for all items that are received into inventory.

Warehouse records are used to define warehouse shipping destinations and default values for purchase orders.

Inventory can be maintained for multiple warehouses, although this feature has not had active use for some time.

Virtual warehouses can also be used to partition physical inventory and reserve it for specific sales.

5.1  Tools

5.1.1  Inventory - Adjust Current Inventory

Reports on current inventory totals and value.

Allows for individual inventory items to be adjusted.

5.1.2  Labels - Print Bar Coded Stock Labels

Create bar-coded labels that can be printed at time of receiving.

5.1.3  Order Levels - Order Stocking Levels

Displays a list of the items sold that should be considered for stocking.

Minimum stock and minimum order are used to auto fill purchase orders for stock.

5.2  Tables

5.2.1  Warehouses - Inventory Locations

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Warehouse Name	char(80)		Y	Y	Name Field
2	Supplier Id	short		Y	Y	Foreign Key to Suppliers
3	Delivery Address	char(255)	Y	Y
4	Requisition Note	char(2000)	Y
5	Customer Note	char(2000)	Y
6	Logo Url	char(80)		Y

The Purchasing Manager allows purchase orders to be created and received.

A shopping cart purchase order system is used to create purchase requisitions for individual suppliers.

A drop-ship requisition tool will generate drop-ship requisitions for each supplier and customer order based on the supplier and catalog item drop-ship options.

A warning is shown if the size of the order is less than the minimum order size set in the supplier profile.

Drop-ship requisitions can be created for large orders from suppliers where the items would normally be special ordered.

New supplier requisitions are populated with all special order items and items that are below minimum stock levels.

Inactive purchase orders should be closed by entering a completed date.

New requisitions appear on the receiving report with the default print item selected until they are printed for the first time.

Purchase requisitions are produced in PDF files based on the items ordered and the information provided in the supplier and warehouse profiles.

Purchase requisitions remain open until all the items are received or the purchase order is closed manually.

Drop-ship requisitions remain open until a tracking number is provided or the purchase order is closed manually.

6.1  Tools

6.1.1  Drop Ships - Create Drop Ship Requisitions

Use this report to create all drop-ship orders.

Only orders in received status will appear.

Only order-items that do not have a requisition will appear.

One requisition is created per customer per supplier.

Items that do not have options and have inventory in stock will not appear on this report.

If any item on an order is drop-shipped from a supplier, all items on that order from that supplier will be drop-shipped.

To drop-ship and direct ship items from the same supplier split the order by creating a back-order first.

Suppliers that have 'Do Not Drop Ship' checked will not appear by default.

The Do Not Drop Ship list is available for special case drop-ships.

6.1.2  Purchasing - Issues Purchase Orders

The purchasing tool provides a way to create new stocking purchase requisitions.

Use the Drop Ship tool in order to create drop-ship purchase orders.

Purchase requisitions are later received into inventory.

6.1.3  Receiving - Receive Inventory

Receive inventory and print requisitions.

Drop-ship orders are automatically generated one per order.

New requisitions will not be marked as printed.

The printed flag will be set when the print option is selected.

It is important to capture the total freight for the requisition.

The tracking number is needed for drop-ship orders.

Canceling a requisition opens the related orders back up for new drop-ships.

6.2  Tables

6.2.1  Requisition Items

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Requisition Id	int			Y	Foreign Key to Requisitions
2	Item Id	int			Y	Foreign Key to Items
3	Options	char(255)			Y
4	Drop Ship	boolean			Y
5	Ordered	int			Y
6	Received	int			Y
7	Price	double			Y
8	Modified Date	Date			Y
9	Modified By	int

6.2.2  Requisitions - Purchase Order Requisitions

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Printed	boolean			Y
2	Warehouse Id	short			Y	Foreign Key to Warehouses
3	Supplier Id	short		Y	Y	Foreign Key to Suppliers
4	Statement Id	int				Foreign Key to Statements
5	Closed Date	Date	Y	Y	Y
6	Total Freight	double	Y
7	Tracking Number	char(120)	Y
8	Sales Number	char(50)	Y
9	Invoice Number	char(50)	Y
10	Requisition Note	char(255)	Y
11	Created By	int		Y
12	Modified By	int		Y
13	Created Date	Date		Y
14	Modified Date	Date		Y

The suppliers module manages all information for suppliers and brands.

A supplier profile record contains a primary supplier directory contact.

Tools are used to import and export catalog items per supplier.

Some tools closely overlap with tools in the Catalog Manager module. Tools in the Supplier module are more specific to managing the fields that would be provided from a supplier including name, description, cost, and msrp.

Inventory numbers from a supplier can be updated and maintained in addition to in-stock numbers to affect availability and catalog sorting.

A shipping matrix can be setup for each supplier to only quote shipping rates for carriers they use.

Suppliers have a ship-from postal code used for estimating shipping costs for drop-ship orders.

Several fields are provided to define lead-times, drop-ship options, order amounts, etc.

Note fields are provided to track credit terms and supplier specific information used by a purchase manager.

Supplier fields can be used to estimate lead-times for special orders or for drop-shipping.

Every catalog item has one supplier, and one brand record. At least one brand record is typically created for each supplier.

Brand records contain fields to tell how the brand might affect the catalog item name.

Brand settings are used to enable map pricing tools.

7.1  Reports

7.1.1  Contacts - Supplier Contact Directory

This shows the contact directory for all suppliers. This information has to be correct setup correctly for the purchase requisitions to work.

Every supplier has a primary point of contact. If the contact is the warehouse, then that is where the product needs to be ordered and received.

7.2  Tools

7.2.1  Exporter - Supplier Catalog Export

Exports the current supplier catalog in excel format.

This can be used to make bulk updates to an existing supplier, e.g. raising all the supplier prices by 10% and then uploading the changes.

Options are provided to export just the items in stock for a selected supplier, or limited to the fields typically provided by a supplier, or the complete item records.

7.2.2  Importer - Import Supplier Catalogs

The add items tool will let you add new items, update specific fields, and deactivate old items.

A vndNo field is required in order to use supplier catalogs.

In order to deactivate old items, the upload file should contain a complete supplier catalog.

You can't add items that don't have a lineId field.

Current allowed upload fields are:

vndNo lineId itemName ourCost listPrice shippingWeight description onHand

Once a supplier catalog has been imported use utilities in the catalog module to update specific fields.

If the item name is all upper case, the system will automatically change it to mixed case.

7.2.3  Nuke!!! - Remove Obsolete Catalog Items

This is provided primarily as a way to back out catalog items imported by accident.

Once an item has any sales history, or certain other history like eBay listing history, it can never be deleted.

7.2.4  Pictures - Supplier Picture Loader

This tool is ideally suited to loading catalog item pictures in bulk.

Many suppliers will refer you to their website to download images from or provide a CD of images that you may be able to host somwhere.

Using firefox, you can drag and drop the images into the text fields and update the item pictures all at once.

The server will retrieve the image from the provided URL and add the image to the item.

Picture changes propagate to Amazon and eBay if configured.

7.3  Tables

7.3.1  Brands - Product Suppliers/Vendors

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Brand Name	char(80)	Y	Y	Y	Name Field
2	Mfr Name	char(50)	Y	Y	Y	Name Field
3	Web Site Url	char(80)	Y	Y	Y
4	Inactive	boolean	Y	Y
5	Is Brand	boolean	Y	Y	Y
6	Prepend	boolean	Y
7	Map Prices	boolean	Y
8	Description	char(190)	Y	Y
9	Notes	char(255)	Y
10	Lead Time Days	short	Y	Y
11	Item Count	int		Y
12	Searches	int
13	Created Date	Date		Y
14	Modified Date	Date		Y
15	Created By	int		Y
16	Modified By	int		Y

7.3.2  Suppliers - Product Supplier Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Supplier Type Id	short	Y			Foreign Key to Supplier Types
2	Supplier Name	char(80)	Y	Y	Y	Name Field
3	Web Site Url	char(80)	Y	Y
4	Contact Id	int	Y			Foreign Key to Contacts
5	Account Number	char(40)	Y	Y
6	Sales Number	char(40)	Y	Y
7	Fax Number	char(40)	Y
8	Inactive	boolean	Y	Y	Y
9	Fax Requisitions	boolean	Y
10	E Mail Requistions	boolean	Y
11	Hide Order Prices	boolean	Y
12	No Vendor Numbers	boolean	Y
13	Inventory On Premises	boolean	Y
14	Carry Entire Catalog	boolean	Y
15	Hide Items On Site	boolean	Y
16	Has On Hand Feed	boolean	Y
17	Is Drop Ship Only	boolean	Y
18	Do Not Drop Ship	boolean	Y
19	Ships International	boolean	Y	Y	Y
20	Drop Ship Surcharge	double	Y
21	Minimum Drop Ship	double	Y
22	Minimum Order Fee	double	Y
23	Minimum Order Amount	double	Y
24	Credit Terms	char(255)	Y
25	Requisition Notes	char(500)	Y	Y	Y
26	Supplier Notes	char(1000)	Y
27	Maintenance Notes	char(1000)	Y
28	Shipping Methods	char(20)	Y
29	Ship From Postal	char(15)	Y
30	Ship From State	char(2)	Y			Foreign Key to States
31	Lead Time Days	short	Y
32	Transit Days	short	Y
33	Restock Days	short	Y
34	Item Count	int			Y
35	Brand Count	int			Y
36	Catalog Mail Days	short
37	Last Catalog Date	Date
38	Import Mappings	text	Y
39	Created Date	Date
40	Modified Date	Date
41	Created By	int
42	Modified By	int

The catalog module contains reports and tools for managing catalog products and categories. The supplier module has tools to manage the information a supplier would provide verses the catalog module provides tools to manage the extended data that would be used for a complete online catalog.

Every catalog product item record is associated with one category line record, a supplier record, and a brand record.

Items get many defaults from the line, supplier, and brand, but can override defaults for specific items.

Catalog Lines

Catalog product lines are created to organize products into related groups.

Catalog lines are in a hierarchy so that each line may have sub-lines.

For compatibility with external systems, lines that contain sub-lines should not contain items. Items should only be found at the lowest level of the line hierarchy.

There is no limit to the depth of the catalog lines, but should be kept to 3 or 4 levels at the most.

Some top-level lines are defined to be a summary of items across the entire catalog that share a common attribute, for example a top level line could be created for all items that have color options. Each item would still be categorized into the appropriate line, but would also show under this special interest line.

Catalog Items

An item record contains over 100 separate attribute fields to support catalog, inventory, and shipping functions.

Fields can be grouped as follows:

• Catalog - Item name, description, product line, titles, supplier, brand, etc.

• Shipping - Shipping weight, standard box dimensions, and override box dimensions.

• Pricing - Item cost, selling price, list price, Minimum Advertised Price (MAP).

• Availability - In stock, supplier stock, restock dates, and lead-time override from supplier.

Suppliers

Each item can have only one primary supplier record. Item lead-times are determined from that supplier.

Brands

Each item also has a brand record. Every supplier should have at least one brand defined even if it is just an in-house brand.

Brands can be flagged whether they should be displayed with the item details.

Auto pricing settings are also done by brand.

8.1  Reports

8.1.1  Catalog - View Online Catalog

8.1.2  Pictures - Missing Item Pictures

Pictures is handled in the catalog area since they are not always provided by the supplier.

This actually supports several different functions.

By default it displays the total number of items by line and the number of missing pictures within that catalog line.

There are several options to review pictures by size so that both missing and poor quality pictures can be identified.

When viewing an item on this page, the picture manager appears that allows multiple pictures to be uploaded and manipulated.

Pictures are uploaded at their default resolution and the system automatically scales them based on the sizes later requested.

Pictures above a minimum size are also watermarked automatically to help protect your pictures from being copied by others.

Images that have been uploaded can be rotated, flipped, trimmed, and reordered to make up a folder with multiple pictures.

Since the time for dynamic processing of images can be excessive, images get processed and cached in a reverse http accelerator.

Every image URL contains a unique time code so that anytime an image is updated, the old image becomes obsolete.

New pictures automatically get updated to feed channels like Amazon and eBay.

A request for an obsolete picture displays a company logo by default.

8.2  Tools

8.2.1  Kits - Catalog Item Kit Manager

Kits can be created from one or more catalog items.

The new kit item has all the same attributes as any other item.

8.2.2  Organize - Catalog Item Organizer

The organizer makes it easy to view a set of items by either line, supplier, brand, or search and change the respective information in bulk.

There are drop-downs at the bottom of the page that always default to empty, if they are set, that attribute will get changed for all the selected items.

The default page also shows a small report of lines that have items they shouldn't since all items should be placed in the lowest level lines in the hierarchy.

8.3  Tables

8.3.1  Items - Available Catalog Items

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Item Name	char(80)	Y	Y	Y	Name Field
2	Amazon Title	char(100)	Y	Y
3	Auction Title	char(55)	Y	Y
4	Parent Item Id	int			Y	Foreign Key to Items
5	Related Item Id	int				Foreign Key to Items
6	Line Id	short	Y	Y	Y	Foreign Key to Lines
7	Supplier Id	short	Y	Y		Foreign Key to Suppliers
8	Brand Id	short	Y	Y		Foreign Key to Brands
9	Vnd No	char(20)	Y	Y
10	Mfr No	char(20)	Y	Y
11	Sku No	char(20)	Y	Y
12	Minimum Quantity	short	Y
13	Package Quantity	short	Y
14	Is Not Searchable	boolean	Y
15	Is Close Out	boolean	Y
16	Is Bulk Package	boolean	Y
17	Is Not Taxable	boolean	Y
18	Is Drop Ship Only	boolean	Y
19	Is Limited Supply	boolean	Y
20	Is Sellable	boolean	Y		Y
21	Is Feedable	boolean
22	Inactive	boolean	Y	Y
23	Our Cost	double	Y	Y	Y
24	Map Price	double	Y	Y
25	Web Price	double	Y	Y	Y
26	List Price	double	Y	Y
27	Core Price	double	Y	Y
28	Match Price	double	Y
29	Description	char(4000)	Y	Y
30	Features	char(2000)	Y
31	Amazon Defines	char(255)	Y
32	Internal Notes	text	Y
33	Shelf Location	char(8)	Y
34	Minimum Stock	int	Y	Y
35	Minimum Order	int	Y	Y
36	Lead Time Days	short	Y	Y
37	Is Oversized	boolean	Y
38	Is Truck Freight	boolean	Y
39	Is No Free Ship	boolean	Y
40	Is Sheet Metal	boolean	Y
41	Ship Separate	boolean	Y
42	Package Stats	boolean		Y
43	Shipping Weight	double	Y	Y
44	Flat Rate Shipping	double	Y	Y
45	Box Id	short	Y			Foreign Key to Boxes
46	Box Width	short	Y
47	Box Height	short	Y
48	Box Length	short	Y
49	On Hand	int		Y
50	On Order	int		Y
51	In Stock	int		Y	Y
52	Units Sold	int		Y
53	Units Sold30	int		Y	Y
54	Score	int
55	Kits	short		Y
56	Clicks	int			Y
57	Page Views	int
58	Lead Days	short		Y
59	Width	short
60	Height	short
61	Restock Date	Date	Y
62	Created Date	Date		Y
63	Modified Date	Date		Y
64	Created By	int		Y
65	Modified By	int		Y

8.3.2  Lines - Product Line Categories

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Path Name	char(120)			Y	Name Field
2	Leaf Name	char(80)	Y	Y		Name Field
3	Path Info	char(40)	Y	Y
4	Description	char(190)	Y
5	Comment	char(120)	Y
6	Amazon Defines	char(255)	Y
7	Auction Category Id	int	Y			Foreign Key to Categories
8	Store Category Id	long	Y	Y
9	Parent Line Id	short	Y			Foreign Key to Lines
10	Item Count	short			Y
11	Line Count	short			Y
12	Guide Text	text	Y
13	Page Title	char(160)	Y	Y
14	Meta Keywords	char(1000)	Y
15	Meta Description	char(2000)	Y
16	Body Content	text	Y
17	Min Price	double		Y
18	Max Price	double		Y
19	Created Date	Date		Y
20	Modified Date	Date		Y
21	Created By	int		Y
22	Modified By	int		Y

The pricing module provides pricing and margin related reports and re-pricing tools.

This module is more related to managing revenue than dealing with core catalog fields.

Pricing profiles can be created to automatically price items related to their cost.

Competitive price matching records can be created to track competing price matrices.

Sales pricing records can be created to provide an item override price for a fixed time period.

9.1  Reports

9.1.1  Auto Price

The automatic pricing profiles will maintain pricing ratios on items even after the cost or list prices change.

9.1.2  Profit - Items Sorted by Profit

This tool is used to view all the active catalog items sorted by the total profit margin.

This is ideally used to find items that may have incorrect pricing or cost data so that they can be corrected.

9.2  Tools

9.2.1  Margins - Brand Profile Pricing

Recommends item prices based on criteria set in the brand record.

The system will add an additional 5% off to the minimum discount on items that are in stock.

The total discounts are applied, then the price is raised if the minimum margin is not set.

The minimum price is calculated by computing the price based on the minimum margin.

The maximum price is calculated by computing the minimum discount off of the list price.

If the prices is not between the minimum and maximum a new price is calculated from the web margin.

Make the min margin and web margin the same to move up the minimum price.

Increasing the discount will move the maximum price down to the minimum margin price.

List markup is a percentage, so 200, would mean 200% of the cost.

A new price is recommended based on the calculated minimum and maximum.

Recommendations for items not between the minimum and maximum are shown in red.

If the maximum price is less than the minimum price, the minimum price is used.

9.2.2  Pricing - Catalog Pricing

The pricing tool allows the pricing, pictures and descriptions for all the items to be set by the supplier and brand.

You do not need to enter the brand names on the items, but if the wrong brand name is there, just delete it. Brand names are added to the item names automatically on the next update.

Image url's to files can be entered into the src field to update images, A full image URL begins with http://. Images should be in jpg format, and should be the highest resolution you can find. Try to use manufacturer images, and not images created by other retailers.

If you are using Firefox, you can actually drag and drop the image into the source field and the complete URL will be copied.

9.3  Tables

9.3.1  Profiles

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Profile Name	char(50)	Y	Y	Y	Name Field
2	Auto Price Web	boolean	Y	Y	Y
3	Auto Price List	boolean	Y	Y	Y
4	Use Web Margin	boolean	Y	Y	Y
5	List Markup	short	Y	Y	Y
6	Web Margin	short	Y	Y	Y
7	Min Margin	short	Y	Y	Y
8	Min Discount	short	Y	Y	Y
9	Min Cost	double	Y	Y	Y
10	Max Cost	double	Y	Y	Y

9.3.2  Rules

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Profile Id	short	Y	Y	Y	Foreign Key to Profiles
2	Supplier Id	short	Y	Y	Y	Foreign Key to Suppliers
3	Brand Id	short	Y	Y	Y	Foreign Key to Brands
4	Line Id	short	Y	Y	Y	Foreign Key to Lines

9.3.3  Sales

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Item Id	int	Y	Y	Y
2	Start Date	Date	Y	Y	Y
3	Hours	int	Y	Y	Y
4	Sales Price	double	Y	Y	Y
5	Original Price	double	Y	Y	Y
6	Inactive	boolean	Y	Y	Y

Variants and item grouping requires several tools to help identify and manage groups.

A variant is defined on an item title with any text found past the last ' - ' in an item title. That is a dash character surrounded by spaces.

Example variants would be "Widget - Blue", and "Widget - Red". If the items were created with a consistent naming scheme, they will appear on candidate lists to group them together.

Variants can have multiple dimensions by separating with commas, for example Size and Color, so the format would be "Widget - Large, Blue".

Once grouped, variants will not display separately within the catalog. This way if you have an item with 100 different variants, only the lead item will be displayed leaving room for your other products to display.

The variant catalog grouping can be turned off in particular lines for cases where the number of variants or the number of other products is small.

When a variant item is displayed, the related items can be directly selected.

A variant group can share a single description and picture. A report will show all variants where the price, description, or attributes other than the title change within the group to help maintain consistency.

10.1  Reports

10.1.1  Variants - Variant Validation Checker

Provides multiple reports and tools to check all of the variants in the catalog.

Checks for consistency between items in groups, also allows viewing all groups that have a variation in price, weight, cost, features, or descriptions since most groups would only change the name and maybe the picture.

10.2  Tools

10.2.1  Explode - Duplicates an Item Into Related Groups

The item exploder is used to manage groups of items with variants.

Item groups have one or more themes, like Hand, or Hand/Color, and the individual items have variants for each of the themes, like Right, Left and Red, Green, Blue.

An item name consists of [name] - [variants]. There has to be a space on both sides of the dash in order to have a variant, and only the characters after the last dash in the name will be used for the variant part. You can have a dash after the variant separator as long as there are no spaces around it.

A variant may have multiple fields for each of the themes defined in the group. The separate fields should be separated by a comma and space.

This tool will not make any changes to the item unless the commit flag is checked. It will automatically match the existing list by name and variant, and if not found, it will create a new item. If the update existing flag is set, it will automatically modify the variants on any pre-existing records.

The variant list is filled in automatically based on the item group. If a variant is removed from the list, any item not matched will be marked inactive. Simply add the new variant back in to reactive the item.

Be careful when using update existing when there may be unique attributes defined for each item in the group other than in the item name.

10.2.2  Groups - Group Related Items

Searches the catalog for items that follow the format required for variants but are not currently in a group.

When an item is viewed, a list of candidate items are displayed that appear to have the same base name.

Groups can also be created by entering a list of item record id's.

The shipping services manager contains all of the interfaces for the different shipping carriers.

Shipping Rates

Direct rate & track interfaces are used for the following carriers:

FedEx USPS UPS

In addition fixed zone based rates can be used if absolutely necessary, the only case so far has been for freight calculations.

Item shipping options can require that items ship in separate boxes.

In addition dimensions can be used to also quote multiple boxes.

If the item is a drop-ship order, the rates are quoted from the suppliers shipping location.

If the order contains a mix of drop-ship and special order, the default shipping location is used.

Address Verification

Address verification is used to determine the correct rates to use for business and residential.

Address verification is used with UPS and FedEx to quote the correct rates.

Package Tracking

All valid tracking numbers are tracked until a delivery date can be confirmed.

Tracking utilities will show the tracking information in the order manager.

An order with an invalid tracking number will eventually be marked as delivered.

A tracking status interface is also provided for users so they can be directed to your website instead of a third-party.

Once an order is delivered a followup message is sent to the customer thanking them for their order.

Shipping Terminal

Software has not been created to generate and manage shipping labels since there are other packages that do an excellent job.

Instead the system can be integrated with the shipping terminal software in use.

A queue is provided to the shipping software so that the shipping terminal can only pull up orders that are in the fulfillment stage.

When an order is shipped, the shipping terminal posts back the shipping details including cost and tracking number.

The package information is used to then mark the order as shipped and removes it from the shipping queue.

Current integration includes FedEx Worldship and Dazzle. UPS has been integrated and works similar.

11.1  Reports

11.1.1  Activity - Tracking Packages In Transit

See the status of all the orders that are currently in the process of being shipped.

Also can be used to see a list of invalid tracking numbers so that they can be corrected.

11.1.2  Addresses - Shipping Address Verification

Displays the shipping address verification responses for any address found in the contact directory.

11.1.3  Compare - Shipping History Comparison

Used to look for issues with data entry or package tracking.

Can happen if tracking numbers get mixed up.

11.1.4  Expenses - Shipping Expenses

Summarizes total shipping expenses.

This report is not 100% accurate because of multiple packages but is pretty close.

11.2  Tools

11.2.1  Quote - Quote Shipping Charges

Quotes shipping rates for a given rate and displays all of the rates and surcharges.

Used primarily as a diagnostic tool to figure out why rates are getting quoted a particular way.

11.2.2  Tracking - Track Packages

Displays tracking information for the reference number provided.

11.3  Tables

11.3.1  Boxes - Standard Shipping Box Records

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Box Name	char(80)	Y	Y	Y	Name Field
2	Box Width	double	Y	Y	Y
3	Box Height	double	Y	Y	Y
4	Box Length	double	Y	Y	Y
5	Box Dimension	double
6	Box Price	double	Y	Y	Y
7	Box Weight	double	Y	Y	Y
8	Default Weight	double	Y	Y	Y

11.3.2  Carriers - Shipping Carrier Profile Records

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Carrier Name	char(5)	Y	Y	Y	Name Field
2	Inactive	boolean	Y	Y	Y
3	Free Shipping	double	Y	Y	Y
4	Max Discount	double	Y	Y	Y
5	Handling Fee	double	Y	Y	Y
6	Expedited Fee	double	Y	Y	Y
7	Intnl Fee	double	Y	Y	Y
8	Max Value	double	Y	Y	Y
9	Max Weight	double	Y	Y	Y
10	Account	char(20)	Y	Y
11	Shipping Note	char(500)	Y	Y

11.3.3  Dazzles - Dazzle Terminal Package Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Order Id	int		Y	Y
2	Shipping Method Id	short		Y	Y
3	Shipping Method	char(80)		Y	Y
4	Postage Amount	double		Y	Y
5	Tracking Number	char(50)		Y	Y
6	Postmark Date	Date		Y	Y
7	Transaction Date	Date		Y	Y
8	Group Code	char(50)		Y	Y
9	Insured Value	double		Y	Y
10	Insurance Fee	double		Y	Y
11	Status	char(50)		Y	Y
12	Full Xml	text
13	Weight	double		Y	Y

11.3.4  Methods - Shipping Method Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Carrier Id	short	Y			Foreign Key to Carriers
2	Shipping Method	char(80)	Y	Y	Y
3	Short Name	char(80)	Y	Y	Y	Name Field
4	Inactive	boolean	Y	Y	Y
5	Is International	boolean	Y	Y	Y
6	Is Standard	boolean	Y	Y	Y
7	Is Freight	boolean	Y	Y	Y
8	Description	char(1000)	Y
9	Service Url	char(150)	Y	Y	Y
10	Days Min	short	Y	Y	Y
11	Days Max	short	Y	Y	Y
12	Max Value	double	Y	Y	Y
13	Max Weight	double	Y	Y	Y
14	Max Length	short	Y	Y	Y
15	Max Width	short	Y	Y	Y
16	Max Height	short	Y	Y	Y
17	Max Dimension	short	Y	Y	Y
18	Lowest Rate	double	Y	Y	Y
19	Service	short	Y	Y	Y

11.3.5  Packages - Package Tracking Number Records

Privileges: Select / Delete

#	Label	Type	Edit	Search	List	Description
1	Tracking Number	char(24)		Y	Y
2	Order Id	int		Y	Y	Foreign Key to Orders
3	Method Id	short		Y	Y	Foreign Key to Methods
4	Statement Id	int		Y	Y
5	Errors	short		Y	Y
6	Shipping Cost	double		Y	Y
7	Shipping Date	Date		Y	Y
8	Scheduled Date	Date		Y	Y
9	Delivery Date	Date		Y	Y
10	Actual Weight	double		Y	Y
11	Billed Weight	double		Y	Y
12	Address	char(160)		Y	Y
13	City	char(80)		Y	Y
14	State	char(2)		Y	Y	Foreign Key to States
15	Postal Code	char(20)		Y	Y
16	Country	char(2)		Y	Y	Foreign Key to Countries
17	Notes	text
18	Created Date	Date		Y	Y
19	Modified Date	Date		Y	Y

11.3.6  World Ships - UPS Terminal Package Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description

11.3.7  Zones - Ground Freight Zone Records

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Method Id	short		Y	Y	Foreign Key to Methods
2	Zone Name	char		Y	Y	Name Field
3	Minimum Fee	double	Y	Y	Y
4	Residential Fee	double	Y	Y	Y
5	Rate1	double	Y	Y	Y
6	Rate2	double	Y	Y	Y
7	Rate3	double	Y	Y	Y
8	Rate4	double	Y	Y	Y
9	Weight2	double	Y	Y	Y
10	Weight3	double	Y	Y	Y
11	Weight4	double	Y	Y	Y

Manages the different gateways that may be used to process payments.

A terminal application can be used to process individual payment records.

Different reports can be used to report transactions over time by merchant.

Statements

A statement is used to group together multiple payments. A single order or invoice can then reference a single statement.

By default a payment refund can never be more than what was originally collected on the same statement.

Payment Gateways

Every gateway supports a fixed set of transaction types.

A default gateway that does not use an external server exists for processing manual payments.

Payments can be setup with a future release date so that they will not be processed until a future date.

The advanced PayPal interface can be used with any account enabled for third-party access.

New gateways can be configured without having to change any other software.

12.1  Reports

12.1.1  Processed - Revenue Received

Shows all payments processed within the specified time interval.

12.2  Tools

12.2.1  Payments - Record Payments Manually

Used to add or apply a payment to a statement.

Once a payment is in processed state, it can no longer be modified.

The states for a payment are:

Queued - Newly created payment Authorized - From a merchant gateway. Processed - Transaction is complete. Declined - Returned from a merchant. Cancelled - Read only, reduces the total. Voided - Read only, reduces the total.

A new payment hat has a ref number and a processed date will be switched to processed status.

The merchant can be selected for the new payment to be added.

If the payment type is Credit, a credit card will be selected from the account and cleared if other than type Credit.

If add to total is selected, it will either create a new payment, or modify an existing payment if it is considered reusable.

Can also be used to effectively add a reference number to an existing payment.

If the applied payment amount matches one that is not processed, that payment will be modified with the new information.

If the applied payment amount is less than one that is not processed, it will be added, and the other payment will be offset.

Applied payments will fail if the payment would modify the statement total and add to total is not checked.

If adjusting the total, any open payments will be closed and added to the new payment.

12.2.2  Pending - Pending Transactions

Displays payments that have been released for processing.

12.2.3  Terminal - Payment Processing Terminal

Allows all of the basic payment processing steps to be executed.

Can only be used to process an existing payment.

12.3  Tables

12.3.1  Chargebacks - Chargeback Tracking Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Payment Id	int	Y			Foreign Key to Payments
2	Case Number	char(50)	Y	Y	Y
3	Reference	char(50)	Y	Y	Y
4	Reason	int	Y	Y	Y
5	Processed	Date	Y	Y	Y
6	Reversed	boolean	Y	Y	Y
7	Notes	char(255)	Y	Y	Y
8	Created Date	Date		Y
9	Modified Date	Date		Y

12.3.2  Credit Cards - Customer Credit Cards

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Contact Id	int				Foreign Key to Contacts
2	Address Id	int				Foreign Key to Addresses
3	Card Holder	char(80)		Y	Y
4	Card Number	char(19)		Y
5	Expiration Month	short		Y	Y
6	Expiration Year	short		Y	Y
7	Created Date	Date		Y
8	Modified Date	Date		Y
9	Created By	int		Y
10	Modified By	int		Y

12.3.3  Merchants - Merchant Accounts

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Merchant Name	char(80)				Name Field
2	Merchant Type Id	short				Foreign Key to Merchant Types
3	Payment Type Id	short				Foreign Key to Payment Types
4	Last Sync	Date

12.3.4  Payments - Payment Transaction Records

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Statement Id	int		Y		Foreign Key to Statements
2	Status Type Id	short		Y	Y	Foreign Key to Status Types
3	Contact Id	int		Y	Y	Foreign Key to Contacts
4	Credit Card Id	int		Y	Y	Foreign Key to Credit Cards
5	Merchant Id	short		Y		Foreign Key to Merchants
6	Memo	char(80)		Y
7	Amount	double		Y	Y
8	Fee	double		Y
9	Auth	char(80)		Y
10	Ref	char(80)	Y	Y	Y
11	Parent	char(80)	Y
12	Processed Date	Date		Y	Y
13	Release Date	Date		Y
14	Verified Cvv2	boolean		Y
15	Verified Address	boolean		Y
16	Verified Postal	boolean		Y
17	Listing Id	long		Y
18	Buyer	char(50)		Y
19	Closing Date	Date		Y
20	Customer Note	char(500)		Y
21	Created Date	Date		Y
22	Modified Date	Date		Y
23	Created By	int		Y
24	Modified By	int		Y

The customer relationship manager provides a series of tabs to manage customer related information across all of the other modules.

The directory only handles the storage of contact information and is a core module in the system. The other CRM functions couldn't be part of the directory module without making all of those modules part of the core set.

13.1  Reports

13.1.1  Customer - Customer Profile

An integrated customer profile page that provides tabs for data from across all different system modules.

13.1.2  History - Call Log History

View customer history contact information.

13.1.3  Pending - Customer Scheduled Followups

Display a queue of pending followup contact dates.

13.2  Tables

13.2.1  Logs - Contact Call Log Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Log Type Id	short	Y		Y	Foreign Key to Log Types
2	Contact Id	int			Y	Foreign Key to Contacts
3	Followup Date	Date	Y	Y	Y
4	Notes	char(8000)	Y
5	Created By	int		Y
6	Modified By	int		Y
7	Created Date	Date		Y
8	Modified Date	Date		Y

The Amazon interface handles all catalog products, inventory, and pictures management.

For the most part, the Amazon interface runs on auto-pilot and is rarely used other than to track large updates.

Catalog

The catalog is checked periodically for any changes that should be propagated to the Amazon catalog.

A channel feed profile record is used to filter items that are allowed to go into the catalog.

Images provided to Amazon listings are at a reduced resolution since their terms of use do not allow for watermarking images. This provides some marginal protection against people using high resolution images that you own.

Orders

Orders are also downloaded from Amazon as soon as they are made available.

New orders are created based on the Amazon orders, and an acknowledgment is sent to Amazon as soon as the order is received.

Once an order is shipped, the tracking information is also sent to Amazon.

If an order needs to be refunded in part or in whole, it needs to be done in the Amazon Seller Central. Their is the possibility of automating this, but the preference has been to process these manually.

Product Templates

The template type is set in the line records, but can be overridden in item records if needed. Typically similar items will all be grouped within a catalog line, so this is rarely required.

Fields are provided in every catalog item, and line record that can override default values of any attribute. These have been rarely used since all of the required fields are already pulled from the catalog items.

Amazon Unique Fields

Accurate lead time to ship products is a pretty important field to Amazon since your account is scored based on how well you perform with respect to the time you stated you would ship an item. There are extensive lead-time related profile fields in the supplier, and overrides for individual items to help provide more accurate times based on availability and shipping location.

Items also contain a restock date for items that can be pre-purchased prior to general availability, or that may not be available for an extended period of time.

14.1  Reports

14.1.1  Documents - Amazon Document Queue

Amazon uses a store and forward method to upload and download large documents containing XML data.

This report shows the current queues, and the status information associated with each document upload and response.

14.2  Interfaces

14.3  Tables

14.3.1  Definitions - Data Attribute Schema Mapping

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Template Type Id	short		Y		Foreign Key to Template Types
2	Header Name	char(50)		Y	Y	Name Field
3	Binding Key	char(50)		Y	Y
4	Default Val	char(50)		Y	Y
5	Description	char(1000)
6	Accepted Values	char(2500)
7	Example	char(255)		Y
8	Required	char(120)		Y
9	Is Required	boolean		Y
10	Is Desired	boolean		Y
11	Inactive	boolean		Y

14.3.2  Reports - Amazon Generated Report Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Parent Report Id	long		Y	Y	Foreign Key to Reports
2	Is Upload	boolean		Y	Y
3	Is Processed	boolean		Y	Y
4	Report Type	char(50)		Y	Y	Name Field
5	Size	int		Y	Y
6	Messages	int		Y	Y
7	Errors	short		Y	Y
8	Warnings	short		Y	Y
9	Activated	short		Y	Y
10	Report	char(8000)		Y
11	Created Date	Date		Y	Y

14.3.3  Shadow Items - Amazon Item Sync Detail Record

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Item Id	int		Y	Y	Foreign Key to Items
2	Refresh Date	Date		Y
3	Error	text
4	Code	int		Y

14.3.4  Shadow Orders - Amazon Order Sync Detail Record

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Report Id	long		Y	Y	Foreign Key to Reports
2	Order Item Id	int		Y	Y	Foreign Key to Order Items
3	Adjustment Type Id	short		Y	Y	Foreign Key to Adjustment Types
4	Cancel Type Id	short		Y	Y	Foreign Key to Cancel Types
5	Confirmed	Date		Y	Y
6	Adjusted	Date		Y
7	Cancelled	Date		Y
8	Shipped	Date		Y	Y
9	Confirm Md5	char(32)		Y
10	Adjust Md5	char(32)		Y
11	Cancel Md5	char(32)		Y
12	Posted Date	Date		Y
13	Principal	double		Y
14	Shipping	double		Y
15	Shipping Hb	double		Y
16	Commission	double		Y

14.3.5  Uploads - Pending Uploads Awaiting Report ID

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Is Manual	boolean		Y	Y
2	Upload Type	char(50)		Y	Y	Name Field
3	Status	char(50)		Y	Y
4	Report Id	long		Y	Y	Foreign Key to Reports
5	Status Id	long		Y	Y	Foreign Key to Reports
6	Messages	int		Y	Y
7	Created Date	Date		Y	Y

The eBay interface automates most of the listing management associated with managing and eBay store and auctions.

A single item template and most of the defaults are defined in the account record.

Images provided to eBay are high quality watermarked images.

Store Listings

A channel feed profile limits the items that will be displayed within an eBay store.

Catalog lines are automatically replicated in the eBay store so items stay in the same category. If there are too many catalog lines, the bottom level is collapsed to not exceed the eBay limit.

Auction Listings

Auctions are created by defining a product profile from any item in the catalog.

Auctions have their own pricing and title fields. Inventory fields will either restrict the number of listings, or continue to list the item as long as the feed profile requirements are met.

An auction type profile can define the default auction types to use and can provide additional descriptive information.

Chinese, fixed price, and dutch auctions are supported.

If defined a time window restricts the times of day that auctions will be created.

Catalog Item Aliases

An aliases table is used to create multiple auction titles from a single item.

Since the auction title is only 55 characters and is the primary search field, it may not be possible to get all the keywords into a single listing title.

By creating aliases for the item, it can be listed with different keywords to generate more sales.

Other than the auction title, everything else about the listing is the same as a base listing.

Listing Revisions

Items go onto a revision queue if anything changes, from experience the maximum throughput for revisions is about 1500 an hour.

A change to the item template or boilerplate text will queue revisions for every active store listings.

Store listings can't be revised within 12 hours of end, or if it has any sales history.

Auctions are not revised since their time-frame is shorter and there are more restrictions on them.

Payment Disputes

If an order is not paid for within the number of days specified in the e-bay account profile, a non-paying-bidder dispute is automatically created.

Custom Checkout

A custom checkout can be utilized to allow for more payment methods and to quote other shipping methods.

If the listing uses calculated shipping, the e-bay shipping calculator is used to maintain parity with the eBay checkout.

A large dynamic button is displayed within the listing template for the checkout page. It automatically changes when the listing has been completed.

Revenue Reporting

An additional revenue report is provided that can evaluate overall profit by brand, supplier, line, or individual item that factors in all the costs associated with marketing the item over time related to actual sales.

15.1  Reports

15.1.1  Account - Account Summary, Auth & Auth

Displays account summary information.

Also allows for auth & auth updates to enable account access.

15.1.2  Categories - Listing Category to Catalog Lines

Displays the mapping between product lines and the eBay auction category to use.

15.1.3  Profit - Auction Net Profit

Displays detailed profit history based on supplier, brand, or item for the time period specified.

15.1.4  Transactions - Completed Auction Orders

Summary report of eBay transactions tied to order status.

15.2  Tools

15.2.1  Disputes - Non Paying Bidder Disputes

Submits disputes to eBay, although this is done automatically now.

15.2.2  Scheduler - Listing Submission Scheduler

Lists the next available item based on the selected queue.

15.3  Monitor

15.3.1  Listings - Active/Cancelled Listings

Displays all the active listings by their category of auctions, store items, or item aliases.

Also displays the pending cancel queue and previously canceled listings.

15.3.2  Pending - Pending Listings Queue

Displays items that are waiting to be listed.

There are three pending listing queues:

• Pending Auctions

• Pending Store Item Listings

• Pending Store Alias Listings

15.3.3  Revisions - Pending Revision Queue

Displays all listings that are awaiting revisions.

15.3.4  Usage - Complete API Call Logging

Also displays totals for listings in each of the tracked states including revisions.

15.4  Tables

15.4.1  Accounts - eBay Account Profile Record

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Account Name	char(20)		Y	Y	Name Field
2	Feed Id	short				Foreign Key to Feeds
3	Is Suspended	boolean
4	Is Sandbox	boolean
5	Is Read Write	boolean
6	List Null Titles	boolean			Y
7	Handling Fee	double	Y
8	Pay Pal Email	char(80)
9	Payment Methods	char(80)
10	Location	char(80)
11	Ship To	char(80)
12	Skype Info	char(80)
13	Shipping Profile	char(50)
14	Item Template	text
15	Return Policy	text	Y
16	Auction Items Block	short			Y
17	Store Items Block	short			Y
18	Alias Items Block	short			Y
19	Revise Items Block	short			Y
20	Cancel Items Block	short			Y
21	Window Start Hour	short	Y		Y
22	Window Run Hours	short	Y		Y
23	Dispute Days	short
24	Disputes Count	short
25	Entries Count	short
26	Events Count	short
27	Transactions Count	short
28	Orders Count	short
29	Feedback Page	int
30	Disputes Sync	Date			Y
31	Entries Sync	Date			Y
32	Events Sync	Date			Y
33	Listings Sync	Date
34	Transactions Sync	Date			Y
35	Orders Sync	Date			Y
36	Api Call Limit	int			Y
37	Expiration Date	Date			Y
38	Auth And Auth	char(2000)
39	Store Key	long
40	Inactive	boolean

15.4.2  Api Calls - API Call Tracking Record

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Account Id	short		Y	Y	Foreign Key to Accounts
2	Item Id	int		Y	Y	Foreign Key to Items
3	Listing Id	long		Y	Y	Foreign Key to Listings
4	Call Name	char(50)		Y	Y	Name Field
5	Ack	char(50)		Y	Y
6	Message	text
7	Call Time	Date		Y	Y
8	Call Millis	int		Y	Y
9	Errors	int		Y	Y
10	Request Xml	text

15.4.3  Api Errors - API Call Error Record

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Api Call Id	int		Y	Y	Foreign Key to Api Calls
2	Code	int		Y
3	Severity	char(50)		Y	Y
4	Error	char(255)		Y	Y

15.4.4  Disputes - Bidder Dispute Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Transaction Id	long		Y	Y	Foreign Key to Transactions
2	Created Time	Date		Y	Y
3	Credit Eligibility	char(50)		Y	Y
4	Explanation	char(120)		Y	Y
5	Message	char(2000)		Y	Y
6	Modified Time	Date		Y	Y
7	Reason	char(120)		Y	Y
8	Record Type	char(50)		Y	Y	Name Field
9	Resolution	char(255)		Y	Y
10	State	char(120)		Y	Y
11	Status	char(120)		Y	Y
12	Escalation	boolean		Y	Y
13	Other Party Name	char(50)		Y	Y	Name Field
14	Other Party Role	char(50)		Y	Y
15	Purchase Protection	char(50)		Y	Y
16	User Role	char(50)		Y	Y

15.4.5  Listings - Listing Shadow Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Account Id	short		Y	Y	Foreign Key to Accounts
2	Item Id	int		Y		Foreign Key to Items
3	Queue Type Id	short		Y	Y	Foreign Key to Queue Types
4	Auction Type Id	short		Y	Y	Foreign Key to Auction Types
5	Duration Type Id	short		Y	Y	Foreign Key to Duration Types
6	Status Type Id	short		Y		Foreign Key to Status Types
7	Bid Count	int		Y
8	Our Cost	double		Y
9	Web Price	double		Y
10	Current Price	double		Y
11	Buy It Now Price	double		Y
12	Total Fees	double		Y
13	Quantity Listed	int		Y
14	Quantity Sold	int		Y	Y
15	Start Time	Date		Y
16	End Time	Date		Y	Y
17	Modified Date	Date		Y
18	Cancel Date	Date		Y
19	Revisions	short		Y

15.4.6  Order Payments

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Payment Id	int		Y	Y	Foreign Key to Payments
2	Listing Id	long		Y	Y

15.4.7  Products - Auction Listing Profile Records

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Item Id	int		Y	Y	Foreign Key to Items
2	Template Id	short	Y	Y	Y	Foreign Key to Templates
3	Inventory	short	Y	Y	Y
4	Lot Size	short	Y	Y	Y
5	Start Price	double	Y	Y	Y
6	Buy It Now Price	double	Y	Y	Y
7	Reserve Price	double	Y	Y
8	Flat Rate Shipping	double	Y	Y
9	Sub Title	char(55)	Y	Y
10	More Description	text	Y

15.4.8  Shipping Services - Shipping Method Mapping Records

Privileges: Select / Update

#	Label	Type	Edit	Search	List	Description
1	Service Name	char(80)		Y		Name Field
2	Service Key	char(50)		Y	Y
3	Method Id	short	Y	Y	Y	Foreign Key to Methods
4	Inactive	boolean	Y	Y	Y
5	Time Min	int		Y	Y
6	Time Max	int		Y	Y
7	Surcharge	boolean		Y	Y
8	Is Expedited	boolean		Y	Y
9	Is International	boolean		Y	Y
10	Min Dimension	double	Y	Y	Y
11	Max Dimension	double	Y	Y	Y
12	Max Weight	double	Y	Y	Y
13	Max Value	double	Y	Y	Y

15.4.9  Store Templates

Privileges: Select / Insert / Update / Delete

#	Label	Type	Edit	Search	List	Description
1	Template Id	short	Y	Y	Y	Foreign Key to Templates
2	Supplier Id	short	Y	Y	Y	Foreign Key to Suppliers
3	Brand Id	short	Y	Y	Y	Foreign Key to Brands
4	Item Id	int	Y	Y	Y	Foreign Key to Items
5	Created Date	Date		Y	Y
6	Modified Date	Date		Y	Y
7	Created By	int		Y
8	Modified By	int		Y

15.4.10  Templates - Listing Type Profile Records

Privileges: Select / Insert / Update

#	Label	Type	Edit	Search	List	Description
1	Template Name	char(80)	Y	Y	Y	Name Field
2	Account Id	short	Y	Y	Y	Foreign Key to Accounts
3	Auction Type Id	short	Y		Y	Foreign Key to Auction Types
4	Duration Type Id	short	Y		Y	Foreign Key to Duration Types
5	Best Offer Minimum	double	Y		Y
6	More Description	char(1000)	Y
7	Is Used Items	boolean	Y	Y	Y
8	Ship To	char(80)	Y

15.4.11  Transactions - Auction Order Shadow Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Listing Id	long		Y	Y	Foreign Key to Listings
2	User Id	int		Y	Y	Foreign Key to Users
3	Statement Id	int		Y		Foreign Key to Statements
4	Order Item Id	int		Y		Foreign Key to Order Items
5	Paid Time	Date		Y	Y
6	Shipped Time	Date		Y
7	Last Time Modified	Date		Y
8	Created Date	Date		Y	Y
9	Quantity Purchased	int		Y
10	Current Price	double		Y	Y
11	Transaction Price	double		Y	Y
12	Total Price	double		Y	Y
13	Adjustment Amount	double		Y
14	Amount Paid	double		Y	Y
15	Final Value Fee	double		Y
16	Dutch Auction Bid	double		Y
17	Sales Tax Amount	double		Y
18	Sales Tax Percent	double		Y
19	Vat Percent	double		Y
20	Cancelled	boolean		Y
21	Get It Fast	boolean		Y
22	Best Offer Sale	boolean		Y
23	Allow Payment Edit	boolean		Y
24	Payment Edited	boolean		Y
25	Buyer Selected Shipping	boolean		Y
26	Shipping Included In Tax	boolean		Y
27	Third Party Checkout	boolean		Y
28	Change Payment Instructions	boolean		Y
29	Feedback Left Id	int		Y
30	Feedback Received Id	int		Y
31	Deposit Type	char(50)		Y		Name Field
32	Buyer Paid Status	char(50)		Y
33	Paid Status	char(50)		Y
34	Cart Id	char(50)		Y
35	Seller Paid Status	char(50)		Y
36	Checkout Status	char(50)		Y
37	Complete Status	char(50)		Y	Y
38	Payment Status	char(50)		Y
39	Payment Method	char(50)		Y	Y
40	Shipping Service	char(50)		Y
41	Shipping Insurance Cost	double		Y
42	Shipping Service Additional Cost	double		Y
43	Shipping Service Cost	double		Y	Y
44	Shipping Surcharge	double		Y
45	Calculated Shipping Discount	double		Y
46	Default Shipping Cost	double		Y
47	Calculated Shipping Rate	double		Y
48	Flat Shipping Discount	double		Y
49	Insurance Fee	double		Y
50	Expedited Service	boolean		Y
51	Shipping Service Priority	int		Y
52	Shipping Time Max	int		Y
53	Shipping Time Min	int		Y
54	Apply Shipping Discount	boolean		Y
55	Insurance Wanted	boolean		Y
56	Promotional Shipping Discount	boolean		Y
57	Seller Manager Sales Record Number	long		Y
58	Listing Transaction Id	long		Y
59	Shipment Tracking Number	char(50)		Y
60	Shipping Rate Error Message	char(50)		Y
61	Shipping Rate Type	char(50)		Y		Name Field
62	Shipping Service Used	char(50)		Y
63	Shipping Type	char(50)		Y	Y	Name Field
64	Sales Tax State	char(50)		Y		Name Field
65	Buyer Message	char(255)		Y
66	Payment Instructions	char(255)		Y

15.4.12  Users - Bidder User Name Records

Privileges: Select

#	Label	Type	Edit	Search	List	Description
1	Contact Id	int		Y	Y	Foreign Key to Contacts
2	User Name	char(80)		Y	Y	Name Field
3	E-Mail	char(80)		Y	Y
4	Eias tOken	char(255)		Y