Customer Schedule Excel Loader

Customer Schedule Maintenance loader allows you to quickly and efficiently create customer shipping and planning schedules in QAD. You can download from QAD database existing planning, shipping or required schedule or copy-paste customer schedule into Excel spreadsheet and upload it into QAD with just a click of a button.

Customer Schedule Maintenance loader can also automatically add new items to scheduled order prior to creating a schedule.

User Authentication
Connecting to QAD Database
Downloading Schedule from QAD
Validating Schedule before upload into QAD
Uploading Schedule into QAD
Clear the spreadsheet
Find errors on the spreadsheet

User Authentication

Customer Schedule Maintenance loader can only be used by users with valid QAD User ID and password and access to 7.5.2 Customer Shipping Schedule Maintenance. User Authentication is based on native QAD Authentication logic (user id, password, menus and site security).

Every time user will open Customer Schedule Maintenance loader and at the first attempt to connect to QAD database to upload or download information he/she will be required to enter QAD user ID and password.


Connecting to QAD database

Before using a loader user should click on [SETUP] button to specify what QAD database Customer Schedule Maintenance loader should be connecting to.

In the pop-up window select a database and click on [SELECT].

Connection needs to be setup only once, after opening template, unless user will need to switch to a different database.

Information about QAD database user is connected to will be always available in the template header.



Downloading Schedule from QAD

To download schedule from QAD you need to specify the following fields in the header:

  • Ship-from – schedule site
  • Ship-to – customer ship-to address
  • Bill-to – sales order bill-to (optional)
  • Order – sales order number
  • Release ID – schedule release ID (optional)
  • Start Date –first effective date for downloaded schedule
  • Schedule Type – type of customer schedule to be downloaded. All three types of customer schedules: planning, shipping and required shipment schedule can be downloaded.

and then click on [DOWNLOAD] button.

Customer Schedule Maintenance loader will attempt to automatically identify sales order by ship-from and ship-to addresses. If more than one sales order exists for ship-from/ship-to combination sales order number will be required.

If schedule, that matches parameters specified in the header, is found it will be downloaded into Excel.

During download specified release ID will be downloaded. If release ID is left blank during download it will be automatically populated. If more than one active release ID, that matches header selection criteria, exist the word “multiple” will be displayed in Release ID field.

Ship-to and Bill-to names will be automatically populated on download.

The following schedule details are downloaded:

  • Line – sales order line number.
  • Customer Item – customer item number. Customer item field value depends on the field “Customer Ref Is Customer Item” value in 7.3.24 Customer Schedules Control. If field “Customer Ref Is Customer Item” is set to “yes” Customer Item will be equal to Customer Reference for all sales order lines.
  • PO Number – PO number field for sales order line.
  • Customer Ref – Customer reference field for sales order line.  Logic for this field is similar to PO Number field. Also Customer Ref number can be used in place of Item Number if field “Customer Ref Is Customer Item” is set to “yes” in 7.3.24 Customer Schedules Control.
  • Model – Model/year value for sales order line.
  • Price List – item price list from the scheduled order.
  • Pack Qty – Standard pack quantity.
  • Comments – Schedule line comments.
  • Item Number – internal QAD item number.
  • Item Description
  • UM – item unit of measure
  • Scheduled quantities – number of scheduled quantities during download is limited by 30.


Validating Schedule Before Upload into QAD

To validate data against QAD before schedule upload you need to click on [VALIDATE] button.

Validate function looks for valid line item in the sales order specified in the header and returns line number, PO, Customer Ref, Model, Price List, Std Pack Qty and lien comments, if any.

 

Valid sales order lines should have expiry date blank or greater than Start Date specified in the header.

If “Add new lines” field is set to “yes” and item is not found in sales order line number will not be assigned to the item as a result of validation.
If “Add new lines” field is set to “no” and item is not found in sales order line error “Item not found” will be returned from QAD.

If any errors are found during validation, error message will be displayed in Status window.

And cells where errors are found will be colored in red. You can mouse over the cell to review the error message.

You will need to fix errors and repeat validation until no errors are received and message “Validated Successfully” is displayed in Status window.



Uploading Schedule into QAD

To upload customer schedule into QAD you need to specify the following fields in the header:

  • Ship-from – schedule site (optional if existing sales order is specified)
  • Ship-to – customer ship-to address (optional if existing sales order is specified)
  • Order – existing sales order number.
  • Start Date – start effective date for a new schedule
  • Schedule Type – type of customer schedule to be created. Only planning and shipping schedules can be created using Customer Schedule Maintenance loader. Required shipment should be generated after upload using QAD’s 7.5.5 Required Ship Schedule Update.
  • Schedule date type – schedule date type to be used for new schedule. QAD is using this date type during required shipping schedule generation to determine whether adjustment (back off) for transportation lead time is required during ship date calculation. If the value is Delivery, shipment dates are adjusted by the number of days specified in the sales order in the field Transport Days. If the value is Ship, shipment dates are not adjusted for transportation lead time.
  • Cumulative – cumulative indicator for the schedule. It indicates whether quantities in the schedule are cumulative or discrete.
  • Allow new lines – allow or disallow automatically adding new lines to sales orders during schedule upload. If value in this field is “no” and item does not exist in sales order specified, error message will be displayed and no schedule will be created. If value of this field is “yes” and item does not exist in sales order, new line will be added to the sales order and schedule will be created.
  • Ship SDP – a code specifying the day(s) of the week or month when shipments or deliveries are required by the customer. Ship/Delivery patterns are used during required shipping schedule calculation to identify actual shipping dates.
  • Netting logic – schedule netting logic. This field is used when adding new line to sales ordre and only when “Allow new lines” field is set to “yes.
  • Salesperson – salesperson code for the item. This field is used when adding new line to sales ordre and only when “Allow new lines” field is set to “yes.

And then click on [UPLOAD] button.

For each scheduled quantity Date and Period the quantity covers need to be specified in the line header. If date is not specified for a quantity column, column will be skipped and will not be added to the schedule. Period (or bucket code) should be D (Day), W (Week) or M (Month). There is a limit of 30 columns for schedules quantities.

When field “Add new lines” in the header is set to “no” only schedule quantities for specified items are uploaded.

If item does not exist in sales order specified in the header, error will be returned from QAD.

You can delete the line and repeat the upload.

Item Line, Customer Item, PO Number, Customer Ref and Model are identified automatically during validation or upload. For upload to be successful sales order should have only one active line for each item, PO number, customer reference and model combination. Otherwise, error will be returned.

As a result of schedule update new release ID will be created automatically in QAD. Release ID will be generated in the format YYYYMMDD-nnn.

When field “Add new lines” in the header is set to “yes” in addition to scheduled quantities you will need to specify PO Number, Customer Ref and Model for new sales order line (if used).

When adding new SO lines you will also have a chance to specify Price List and Pack Qty (standard pack or case size quantity).

Comment can be added all scheduled lines on upload.

Customer Item, Item Description, and UM (unit of measure) are read only fields that are not uploaded into QAD.

Customer Ref number can be used in place of Item Number during SO upload.

If field “Customer Ref Is Customer Item” is set to “yes” in 7.3.24 Customer Schedules Control customer number should be defined in Customer Item Maintenance. You can specify then customer item number in Item Number field. QAD will replace the value you entered in the Item Number field with the corresponding internal item number. Or you can leave Item Number field blank and specify customer number in Customer Ref field and QAD will find matching internal item number automatically.

If field “Customer Ref Is Customer Item” is set to “no” in 7.3.24 Customer Schedules Control value in the field Customer Ref is optional and Item Number field cannot be left blank. Item entered in Item Number field should either exists in Item Master or be a valid customer reference defined in 1.16

Clear the Spreadsheet

To clear spreadsheet from the data you need to click on [CLEAR] button.



Find Errors on the Spreadsheet

When data validation is performed error messages are returned to Excel and recorded as cell comments for cells where error was found. Click on [FIND ERROR] button will move cursor to the next cell with error, so you do not need to scroll up/down a spreadsheet looking for errors.
This function is especially helpful when there are multiple lines on a spreadsheet and multiple errors that are not all visible on a screen without scrolling up/down or left /right.

Click to go back to Homepage