Field Name

Description

File Name

Enter the file name. You should attach the suffix .xls to the file name.

Generate

• Regular Spoolfile - generates a regular report that gets sent to your spool file where you can access it through the ODS link in the top right-hand corner. If you choose this option none of the other fields are required.

• Spreadsheet File - sends the information to a spreadsheet program. You can then choose to print it or email it. It also gets sent to the IFS folder if you enter a Destination Folder.

• PDF File - generates the report as a .PDF file that you can print or email. It also gets sent to the IFS folder if you enter a Destination Folder.

Hold

If you place a hold on the report, you can release it later by clicking the options arrow on the Spoolfile Reports option (via the ODS link) and selecting Release.

Destination Folder

This is the IFS directory where you want the spreadsheet to be placed. Once the spreadsheet is generated and sent to this destination you can retrieve it via the Windows Explorer, if you are mapped to the folder. It can also be accessed via WRKLNK command on the IBM iSeries. You cannot use directory /QDLS.

If you do not enter a destination folder, you must enter an email address. If no destination folder is entered, a temporary one is created that is automatically deleted when the email is sent.

EMAIL File To

Enter the email addresses of the person or people you want to receive the report.

The following guidelines pertain to the email feature:

• You do not have to assign a directory if you email only, the system will automatically use /home/dancik/reports/temp.

• A Java Kit must be installed on your server (automatically installed via the IBM V5R3 Upgrade).

• The HTTP Server, TOMCAT and APACHE servers must be running.

• If you use the ODS system these servers will already be running.

Note:  You can only enter one email address per line.

Parameter

Description

Manufacturer

The manufacturer of the item. If left blank, all manufacturers are included.

Product Line

Product lines link the items within a manufacturer into groups that can be researched or queried on. For natural products such as marble and stone, the manufacturer code and product line codes represent groups and categories as opposed to actual brand name product lines.

Product lines are created through the File Management Application or through option FIL 12 on the iSeries.

Item Class 1, 2, and 3

A user-defined two-character code for classifying the item. You have up to three Class fields in which to classify the item. Class 1 is the most general of the three item classes. It is also the most important because it is used in the pricing and salesperson assignment programs. Price lists and promotional pricing can be assigned to a customer for specific item Class 1 entries. A customer can also be assigned multiple salespeople based upon Item Class 1.

Use Item Class 2 and 3 to further classify items.

Trim Class

If the item is a trim piece, enter a code to identify the class of trim piece. Some examples of trim pieces are:

• For ceramic tile, you can use codes such as BN for bullnose and AN for angles.

• For wood, trim classes can include items such as reducer strips and stair nosings.

• You can consider samples as trim.

• Trim class can also be used to identify items that are not core items, but support the sales of core items. Many reports allow you to omit trim or analyze it separately.

Note:  Trim classes are created through the Classification Codes File and are assigned to items via the Item File.

Item Policy

Enter an item policy or use the drop down box to select one. Entering an item policy narrows the report to just the items associated with that policy.

Color Number

The color number, along with the Manufacturer and Pattern codes, are part of the item number.

Color Name

To make searching by color easier, enter the actual color instead of the manufacturer’s designation. For example, if a manufacturer calls an item “snow” that is actually white, the color name should be “white.” If color is not important or applicable to an item, you can use this field for another category such as style, texture, type, or finish. For example, tools can be categorized by type, such as trowel or saw.

Pattern Number

Use this field to further limit the report to specific pattern numbers. Pattern numbers are characters 8-16 of an item number.

Pattern Name

Enter the pattern name of the item. Pattern names should be standardize to make searching easier. Following are suggested pattern names: Some examples are:

• 4X4 - 4 1/4” (for tile)

• 6X6OCT - 6" x 6" Octagon

• Berber - (for carpet)

We strongly recommend that you use a consistent system for color and pattern name fields. You can use the item search function to search by color or pattern or both. So, for example, you can find products by entering Blue 4 x 4, White Berber, or Thinset 20LB.

Price Class

A price class is a group of items from the same manufacturer that your company prices identically for your customers.

Cost Class

A group of items from the same manufacturer that have identical costs from your supplier.

Package Class

Packaging classes allow behind the scene calculations between unit of measures. When done correctly, this should create an unbroken chain allowing the system to get from any unit of measure to any other.

Note:  The Packaging File is used to establish packaging classes. Packaging classes are assigned to items with the Item File.

Inventory Code

This setting denotes whether the inventory is stocked, not stocked, or special ordered.

Some examples are:

• Y - for stocked items.

• N - items that are not stocked.

• S - if the item is not stocked and is a mandatory special order item.

Component Code

The component code denotes whether the inventory is a type such as serial, or rolled goods.

Some examples are:

• B - If the item is a component, for example part of a kit, and also has components within it. (B - both)

• D - Use this code for items usually sold in many designs or patterns with the same color number.

• F - Fund only item; used solely for billing and crediting fund accounts.

• K - Kit items

• N - (or leave blank) if the item has no components and is not a rolled good or serial numbered item.

• R - The item is stocked in rolls, such as carpet or vinyl.

• S - The item is stocked by serial number, such as for special lots, shades, and large tools.

• Y - The item is a component of another item, for example, a dot part of a dot and octagon pattern, or part of a kit that cannot be sold on its own.

• L - “Labor/Expense” items. The system treats an “L” coded item similar to an “F” (Fund) item, which is mainly that all inventory files are bypassed.

Commodity Level

A one-character code describing an item’s level of relative value. Examples can be H for high-level or luxury items, M for medium-level items, and L for low-value or commodity items.

ABC Code

Use this field to select items based on ranking. For example, items can be classified from best (A) to worst (D).

Examples of nonranking ABC codes can be:

• S - support items, which are items that support the sales of other (hopefully A rated) items.

• N - new items. New items need special considerations until sales patterns are established.

Wear Code

Industry standard codes should be used when possible, such as 1, 2, or 3. The codes could also indicate whether the product is suited for uses such as residential, commercial, or industrial.

Cost Center

This should represent the major divisions of products and services in your company, such as ceramic tile vs. vinyl or. wood. Cost centers are then further subdivided by your item class entries.

Cost centers provide a direct interface with the General Ledger system.

Note:  Cost centers are defined using the Cost Center File Maintenance program.

Freight Class

This field classifies items for bills of lading. It is used to scan bar coded labels as shipments are loaded. Data collected at the point of shipment is used to produce bills of lading, when applicable. This field is also used if you download shipping data into an automated truck loading, shipping, or freight system.

Freight class codes must first be set up in the Classification Codes File.

Qty Break Group

Represents a group of items that are accumulated for quantity breaks on pricing. You can use quantity break group pricing to group products in order to achieve a quantity break based on a combined total quantity instead of each line item quantity.

Some examples are:

• O - Quantity breaks are only accumulated for items on the same order number.

• S - Quantity breaks are accumulated for items with the same ship date, same customer, or same invoice run.

• C - Quantity breaks are accumulated for items for the same customer and same invoice run.

Note:  Quantity break group codes must first be set up in the Classification Codes File.

String Search

You can limit the report to specific items (i.e. tile) by performing a string search. A string search looks for the search string within any descriptors of the item. A string search only displays records that contain the string.

Warehouse

Company

Enter a warehouse and/or company code to limit the report. Leaving these fields blank directs the system to include all warehouses and/or all companies. For example, if the warehouse field is left blank and the Company field has an entry, the report is generated for the warehouses within that company.

Select Sort

Select one of the 12 sorting options. The sort option dictates how the information is arranged on the report.

Select Summary/Detail

The detail version includes a sub-total for each item series for each warehouse.

The columns on the detailed report are:

• Warehouse

• Item

• Description

• Qty on Hand

• Last Cost

• Last Cost Value - Last cost x Qty on Hand

• Average Cost - Avg cost x Qty on Hand

• Available

Select Regular/Trim

Decide if you want to run the report for regular items, trim items, or both.

Note:   Items are designated as trim pieces through the Item File.

Select Version

This option provides seven variations of the report.

• Version 1 - Regular cost - Shows the last cost of the item as entered during receiving (using INB 1 or INV 5) and the Average cost of the item.   

• Version 2 - Standard Cost Value and Freight Cost - The standard cost is the cost of the item less freight and handling charges. This version combines the standard cost and the Freight Cost. The Freight cost includes all freight, duty, broker fees, and so on. Both the standard cost and the freight cost are pulled from the Cost File.

• Version 3 - Last Cost Value - This version reports on the last cost of the item when it was received. Freight is shown separately and then combined with the last cost to provide a total.

• Version 4 - Accounting Cost Value

—  House costs are used when you need to alter a cost for the purpose of sales analysis reporting or sales commissions. For example, on a commodity product that has fluctuating costs, you may prefer to base commissions and reports on a single stable cost. When items are to be sold at great dis­counts, or even below cost, you can artificially create an acceptable profit margin by entering a low house cost. This could be used for commission, thereby creating an incentive to sell these low or negative margin items. House cost are entered in the Cost File or at the SKU level via the SKU File.

—   Accounting Costs are used if an additional cost, such as LIFO or FIFO (remove “or FIFO”) cost, is required. The accounting cost can also be entered on an SKU by SKU basis using the SKU File. If you require LIFO costs based upon historical costs, you can store them here.

• Version 5 - Aging by last receipt date and last cost - This version shows inventory values by the receipt date and cost (remove) of the items based on the last receipt date. The aging time frames on the report start at last month and go all the way back to over three years.

• Version 6 - Aging by last receipt and average cost - This version shows inventory values by last receipt date and the average receipt (remove) cost of the item. The aging time frames on the report start at last month and go all the way back to over three years.

• Version 7 - This version allows you to run the inventory value report for a prior period. This report displays quantity on hand and two forms (out of six possibilities) of cost information.

Field Name

 Description/Instructions

LOT #

Do not confuse this field with the serial number or shade/dye lot fields. This field is usually left blank.

SHADE

Enter up to four shades or leave blank all shades.

LOCATION

This can be a useful parameter if you have designated locations for certain types of specials.

STATUS CODE

By using the various inventory adjustment and write-down programs to update the status codes, this can be an effective way to limit specials.

RECEIPT DATE

Enter a range of receipt dates in order to limit the specials list to serial numbers received before, after, or between specified dates.

Parameter

Description

Stock Activity Reports

Choose to run the report for a date span or for the last 18 months.

Selecting the 18 month option presents more report parameters a little further into the process. Some of these parameters are:

• choice for last cost and list price

• ability to divide roll counts into > and < certain lengths.

• discontinued date

• sorting options

• Include NonStock, Omit NonStock, or Only NonStock

• Include Disc, Omit Disc, or Only Disc

• I/O ABC Codes

Date Span

You can use one or both of these fields to limit the report to a specific time frame. If you enter a “from” date but no “to” date the report is run from the from date to the present.

Manufacturer Code

The manufacturer of the item. If left blank, all manufacturers are included.

Product Line

Product lines link the items within a manufacturer into groups that can be researched or queried on. For natural products such as marble and stone, the manufacturer code and product line codes represent groups and categories as opposed to actual brand name product lines.

Product lines are created through the File Management Application or through option FIL 12 on the iSeries.

Warehouse Code

or Company Code

or Warehouse Group Code

If you enter a single warehouse code the report is limited to that warehouse.

If you enter a company code, the report is run for all warehouses in that company.

Warehouse groups are established groups of warehouses that allow you to include or omit warehouses from the report. Groups can be created by geography, size, or various or parameters.

Note:   Warehouse groups are created and maintained through the Warehouse Group Code, for All Warehouses in that Group field in the Purchasing Reorder Reports (PUR 5).

Cost Center

Cost centers should represent the major divisions of products and services in your company, such as ceramic tile vs. vinyl or. wood. Cost centers are then further subdivided by your item class entries.

Cost centers can provide a direct interface with the General Ledger system.

Note:   Cost centers are defined using the Cost Center File Maintenance program

Item Class

Every item can be assigned three item classes in the Item File. Item classes are user-defined two-character codes for classifying the item.Class 1 is the most general of the three item classes. Some examples are VT for vinyl tile and WD for wood. Use Item Class 2 and 3 to further classify items.

If an item class is entered, all three item classes are searched. Therefore if your item class 1, 2, and 3 codes are all unique, this parameter can access any item class.

Supplier Code

You can narrow the report to one supplier by entering a code into this field or you can run the report for all suppliers by leaving it blank.

The report searches for and displays the supplier entered in the Usual Supplier# field in the Item File.

Reorder/Buying Category

These three-character code represents the buyer or the buying category. They are used to group products for reporting.

Note:   These codes must be set up in the Classification Codes File - FIL 19 via the RC category.

Note:   Items are assigned to reorder/buying categories by Product Line.

Item Rating/ABC Code

Use this field to select items based on ranking. For example, items can be classified from best (A) to worst (D).

Examples of nonranking ABC codes can be:

• S - support items, which are items that support the sales of other (hopefully A rated) items.

• N - new items. New items need special considerations until sales patterns are established.

Color Number

The color number, along with the Manufacturer and Pattern codes, are part of the item number. The color number is the four characters of the item number that immediately follow the manufacturer code.

Enter up to four characters of the color number to limit the report to that particular color number. Optionally, you can leave this field blank to include all color numbers.

Parameter

Description

Sort By

Select from one of nine sorting options. Each of the sorting variations is useful for different versions of the report. For example, option 3 - to sort Stock Activity by Supplier Item facilitates more effective consignment inventory reporting to your suppliers.

Trim

Items are designated as trim pieces through the Trim Class field in the Item File.

• Enter 1 to omit items with a trim class entry.

• Enter 2 to include only items with a trim class entry.

• Enter 3 to include items regardless of their trim class entry.

Stock

The Inventory field in the Item File designates whether an item is a stock or non-stock item.

• Enter 1 to include only stock items (Inventory field is Y).

• Enter 2 to include only non-stock items (Inventory field is N or S).

• Enter 3 to include both stock and non-stock items.

Parameters

Description

Print Quantities

Select a UOM to display the item quantities in. If the UOM entered is different from the native UOM, as specified in the Item File, the system uses the Packaging File to make the necessary UOM conversions.

Suppress Zeros

Use this option to drop zeros from the report and make it easier to read. This report includes a lot of information on each page, and you may find it easier to see the meaningful figures if the zeros are not shown.

Pattern Numbers

Use this field to further limit the report to specific pattern numbers. Pattern numbers are characters 8-16 of an item number.

Limit to Items with at least this many orders

In the past number of months

These fields allows you to target faster moving inventory and eliminate clutter on the report by not showing slow moving inventory.

Omit up to three Warehouses

This option is only shown if the option to include all warehouses (or all warehouses within a company) was selected.

Include/Omit ABC Codes

Use this field to limit the report to specific ABC codes.

Include/Omit Cost Centers

Use this field to limit the report to specific cost centers.

Discontinued Items

Choose to include or exclude discounted items.

For the purpose of this report, an item is considered discontinued if the Item File record contains a date in the Date Discontinued field or has a policy code of DI.

Print Last Cost

Print List Price

Print Turns/GMROI

To print the most landed current cost, activate Print Last Cost.

To print the current list price activate the Print List Price field.

The third option, if activated, shows the turn and earn ratio and the gross margin return on inventory investment (GMROI) ratio for an item.

Inventory turns are based on dollar value followed by inventory turns based upon quantity. The inventory turns are calculated as follows:

• Inventory Value Turns = Annual Cost of Sales divided by Average On-hand Inventory Value.

The GMROI is an industry standard statistical index calculated as follows:

• GMROI = Gross Profit Amount x 100, divided by Average Onhand Inventory Value.

For rolled goods, list available quantity as less than versus greater than or equal to the entered amount.

Identify rolled goods inventory that is either over/equal to or under a specified amount.

If an entry is made in this field (i.e. 50 LF), the report separates the inventory into two groups: less than or greater than the entered amount.

Parameter

Description

Manufacturer

The manufacturer of the item. If left blank, all manufacturers are included.

Product Line

Product lines link the items within a manufacturer into groups that can be researched or queried on. For natural products such as marble and stone, the manufacturer code and product line codes represent groups and categories as opposed to actual brand name product lines.

Product lines are created through the File Management Application or through option FIL 12 on the iSeries.

Item Class 1, 2, and 3

A user-defined two-character code for classifying the item. You have up to three Class fields in which to classify the item. Class 1 is the most general of the three item classes. It is also the most important because it is used in the pricing and salesperson assignment programs. Price lists and promotional pricing can be assigned to a customer for specific item Class 1 entries. A customer can also be assigned multiple salespeople based upon Item Class 1.

Use Item Class 2 and 3 to further classify items.

Trim Class

If the item is a trim piece, enter a code to identify the class of trim piece. Some examples of trim pieces are:

• For ceramic tile, you can use codes such as BN for bullnose and AN for angles.

• For wood, trim classes can include items such as reducer strips and stair nosings.

• You can consider samples as trim.

• Trim class can also be used to identify items that are not core items, but support the sales of core items. Many reports allow you to omit trim or analyze it separately.

Note:   Trim classes are created through the Classification Codes File and are assigned to items via the Item File.

Item Policy

Enter an item policy or use the drop down box to select one. Entering an item policy narrow the report to just the items associated with that policy.

Leave these fields blank to include all item policies.

Color Number

The color number, along with the Manufacturer and Pattern codes, are part of the item number.

Color Name

To make searching by color easier, enter the actual color instead of the manufacturer’s designation. For example, if a manufacturer calls an item “snow” that is actually white, the color name should be “white.” If color is not important or applicable to an item, you can use this field for another category such as style, texture, type, or finish. For example, tools can be categorized by type, such as trowel or saw.

Pattern Name

Enter the pattern name of the item. Pattern names should be standardize to make searching easier. Following are suggested pattern names: Some examples are:

• 4X4 - 4 1/4” (for tile)

• 6X6OCT - 6" x 6" Octagon

• Berber - (for carpet)

We strongly recommend that you use a consistent system for color and pattern name fields. You can use the item search function to search by color or pattern or both. So, for example, you can find products by entering Blue 4 x 4, White Berber, or Thinset 20LB.

Price Class

A price class is a group of items from the same manufacturer that your company prices identically for your customers.

Cost Class

A group of items from the same manufacturer that have identical costs from your supplier.

Package Class

Packaging classes allow behind the scene calculations between unit of measures. When done correctly, this should create an unbroken chain allowing the system to get from any unit of measure to any other.

Note:  The Packaging File is used to establish packaging classes. Packaging classes are assigned to items with the Item File.

Inventory Code

This setting denotes whether the inventory is stocked, not stocked, or special ordered.

Some examples are:

• Y - for stocked items.

• N - items that are not stocked.

• S - if the item is not stocked and is a mandatory special order item.

Component Code

The component code denotes whether the inventory is a type such as serial, or rolled goods.

Some examples are:

• B - If the item is a component, for example part of a kit, and also has components within it. (B - both)

• D - Use this code for items usually sold in many designs or patterns with the same color number.

• F - Fund only item; used solely for billing and crediting fund accounts.

• K - Kit items

• N - (or leave blank) if the item has no components and is not a rolled good or serial numbered item.

• R - The item is stocked in rolls, such as carpet or vinyl.

• S - The item is stocked by serial number, such as for special lots, shades, and large tools.

• Y - The item is a component of another item, for example, a dot part of a dot and octagon pattern, or part of a kit that cannot be sold on its own.

• L - “Labor/Expense” items. The system treats an “L” coded item similar to an “F” (Fund) item, which is mainly that all inventory files are bypassed.

Commodity Level

A one-character code describing an item’s level of relative value. Examples can be H for high-level or luxury items, M for medium-level items, and L for low-value or commodity items.

ABC Code

Use this field to select items based on ranking. For example, items can be classified from best (A) to worst (D).

Examples of nonranking ABC codes can be:

• S - support items, which are items that support the sales of other (hopefully A rated) items.

• N - new items. New items need special considerations until sales patterns are established.

Wear Code

Industry standard codes should be used when possible, such as 1, 2, or 3. The codes could also indicate whether the product is suited for uses such as residential, commercial, or industrial.

Cost Center

This should represent the major divisions of products and services in your company, such as ceramic tile vs. vinyl or. wood. Cost centers are then further subdivided by your item class entries.

Cost centers provide a direct interface with the General Ledger system.

Note:   Cost centers are defined using the Cost Center File Maintenance program.

Freight Class

This field classifies items for bills of lading. It is used to scan bar coded labels as shipments are loaded. Data collected at the point of shipment is used to produce bills of lading, when applicable. This field is also used if you download shipping data into an automated truck loading, shipping, or freight system.

Note:   Freight class codes must first be set up in the Classification Codes File.

Qty Break Group

Represents a group of items that are accumulated for quantity breaks on pricing. You can use quantity break group pricing to group products in order to achieve a quantity break based on a combined total quantity instead of each line item quantity.

Some examples are:

• O - Quantity breaks are only accumulated for items on the same order number.

• S - Quantity breaks are accumulated for items with the same ship date, same customer, or same invoice run.

• C - Quantity breaks are accumulated for items for the same customer and same invoice run.

Note:   Quantity break group codes must first be set up in the Classification Codes File.

String Search

You can limit the report to specific items (i.e. tile) by performing a string search. A string search looks for the search string within any descriptors of the item. A string search only displays records that contain the string.

Select Sort

Choose one of the three sorting options.

Select Quantity

Use this field to narrow the results to a specific quantity.

Warehouse

Company

Enter a warehouse and/or company code to limit the report. Leaving these fields blank directs the system to include all warehouses and/or all companies. For example, if the warehouse field is left blank and the Company field has an entry, the report is generated for the warehouses within that company.

Omit Records

Activate this option if you do not want to include items with no inventory available or scheduled for receipt.

List Sub Totals for

Enter 1 to sum inventory records for each item by warehouse.

Enter 2 to also include lot# to item totals by warehouse. A lot# column is added to the report.

The Lot# field is used in Order Entry to order special/non-stock items. Option 2 can be used to organize the report so special order items can be easily reviewed.

List Serial Numbers

Includes the item serial numbers. Used primarily for rolled goods.

Select Regular/Trim

Decide if you want to run the report for regular items, trim items, or both.

Note:   Items are designated as trim pieces through the Item File.

U/M

Choose the Unit of Measure from the drop down box or type one directly into the box.

Report Version

Negative quantity is calculated as Onhand Qty - Allocated + Scheduled.

Discontinued Items

For the purpose of this report, an item is considered discontinued if the Item File record contains a date in the Date Discontinued field or has a policy code of DI.