Download or Create Orders Using Spreadsheet

This document explains how to download orders into order entry starting with a spreadsheet. This is just one way to create orders using a spreadsheet. You may also open an order and import line items from a spreadsheet. Contact us for changes in this setup and other options to automate the creation of open orders.

Order Processing (Module 6)

· Electronic data interchange (Task 8)

· Download / import trx processing DWNSEL (Sub-task 4)

o Once a spreadsheet is saved properly, DWNSEL may be used in sequence:

1. Load download file into import master file maintenance DWNMUL

2. Import master file maintenance (Change/Delete) DWNMNT

3. Reports Selection DWNRPT

4. Create Open Orders from import master file records DWNORD

5. Clear Import master file after Creating Open Orders DWNCLR

6. Automated change order status (move to 'Accounting' status) CHGSTS

Before getting started a few parameters should be looked at:

Order processing (Module 6)

System management functions (Task 32)
- Modify parameter files (Sub-task 22)
  - 3 EDI

----------------------------------------------------------

Importing download to build orders parameters

DWNMUL'FIELD'LIST=13 ; How many fields expected for DWNMUL import

to build DWNMAS data file and then orders. 12 was typical, 13 originally and in newer files to include 'Task' in reserve3/13.

DWN'RESV1'LABEL=Bill code/R ; Reserve field 1 label (field 16 in import master)

DWN'RESV2'LABEL=Phase ; Reserve field 2 label (field 17 in import master)

DWN'RESV3'LABEL=Task ; Reserve field 3 label (field 18 in import master)

DWN'ONE'ITEM'PER=Y ; Create order line items one-to-one w/download

-----------------

STEP 1:

Spreadsheet must be saved in u/mvs/dsk0/25000x (x being the company #). The spreadsheet must be saved as a .csv format.

The spreadsheet must have 13 columns, A through M need to be:

A DATE

B FIELD 2 ON SHIPPING SCREEN (Field 2 in import master)

C USER Item memo notes (Field 9 User Unit in import master)

D FIELD 1 ON SHIPPING SCREEN (Field 8 in import master)

E ITEM NUMBER (Field 16 in import master)

F COPIES (# of originals will default to 1)

G SIZE X

H SIZE Y

I PRODUCT TYPE

J MEDIA TYPE (Field 4 Type Data in import master)

K RESERVE 1with the above parameter setting this would be Bill code/R

L RESERVE 2 with the above parameter setting this would be Phase

M RESERVE 3 with the above parameter setting this would be Task

NOTE: Any of these columns can be blank IF the field on the order can be blank.

NOTE: COLUMN K, L, M, the reserve fields are put into the memo notes with an *asterisk at the beginning of the line. If you have the O/E general parameter

EXT’MEMO’SKIP’PREFIX=* then the memo notes won’t print on invoices & shippers.

INT’MEMO’SKIP’PREFIX=* then the memo notes won’t print on internal documents.

STEP 2:

DOWNLOADING THE SPREADSHEET:

Order Processing (Module 6)
- Electronic data interchange (Task 8)
  - Download / import trx processing DWNSEL (Sub-task 4)
    - Load download file into import master file maintenance DWNMUL (Sub-task 1)
    - This program reads in a GENERAL IMPORT file from Selected Customers and writes the Records to the Import Work file, for processing at a later date. An Audit Listing is printed as the records are being converted.

The following Information is REQUIRED for this program.

1. Customer #

2. Branch #

3. Alt Ship to #

Do you wish to continue? (YES/NO): ___

Please enter - starting Customer #: _____

- ending Customer #:

Please enter - starting Location #: __

- ending Location #:

Please enter - starting Alt Ship to #: 00000

- ending Alt Ship to #: 00000

Please enter - starting Pricing Ship to #: _____

- ending Pricing Ship to #:

NOTE: Enter key for ALL may NOT be used in the starting & ending customer #, Location #, Alt Ship to #, or Pricing Ship to #.

Please enter - starting Input Data Date: ______

- ending Input Data Date:

NOTE: The program will use the following computer default filename, OR you may ENTER your own filename at the Prompt.

Default Name = 0053100000.csv

Enter optional filename (lower case) or press ENTER: ______________

Strip -'s from fourth character of item #'s ?: N

The system will show you the selections you made-

You Selected Customer Number: 00531

Customer Name : OTHER NEW CUSTOMER

Alt Ship To Number: 00000

Location Number: 01

Location Name : Main Warehouse

Import File Name : 1777300001.csv w/o strip of dash@4

Is the ABOVE Information Correct ??????Y

Left Arrow to select again or ENTER key to continue:

You will then get a printer selection screen for the O/E - Import Detail Audit List

NEXT SCREEN:

Updating Import Master File

-------------------------------------------

# OF RECORDS THAT CAN BE ADDED = 50011 original, 49 sets(8.5x11)

# OF RECORDS THAT CAN BE ADDED = 2030

It will quickly scroll through the lines of the spreadsheet.

You will then get a report of the list of items that were downloaded.

NOTE: If you get a message that you have a bad item something is wrong with the spreadsheet.

BAD ITEM=1 CR TO CONTINUE

BAD ITEM-8.5 CR TO CONTINUE

NOTE: Take a look at the O/E - Import Detail Audit List to see if there are any corrections needed.

STEP 3:

The downloaded spreadsheet is now in Import master file maintenance DWNMNT (Sub-task 2). If any corrections are needed they can be made in this subtask.
Reports Selection DWNRPT (Sub-task 3)
- Print Detail Report 1
- Print Summary Report 2
- Print Product Summary 3
Once all corrections are completed the orders may be created.

STEP 4:

CREATING ORDERS:

Create Open Order from import master file records DWNORD (Sub-task 4)
This program reads in a previously Import data file and creates new orders in the Order Entry & Editing files. The program will also print a control report showing Order #, Customer #, Item #'s, etc for your review.
User options will be: Customer #, Usage date range, Branch, Cashier # and the type of orders you want to create (By Project # or By Project #, Date and User). If by Project # only is selected, you must enter a date to be used on the orders, otherwise the Usage Date will be the order & invoice date.
This puts the items from the import master file maintenance DWNMNT; on the orders in item # order. Not in the same order they are on the spreadsheet.

Do you wish to continue? (YES/NO): ___

Please Enter Customer #: 531

Please enter - starting Usage Date: 09/01/11

- ending Usage Date: 09/30/11

Enter Location #: 01

Enter your Cashier #: 0017

Create Order Option: _

Enter S=Create Orders by Project #

D=Create Orders by Project #, Date, User

Enter the Order Date: 09/30/11

ANY CHANGES?: N

NOTE: This program will give you the option to move

the input plot name field to the Open order file

P/O field, answer below.

Move the PlotName field to the OpenOrder P/O field? YES/NO? ___

o You will now get a printer selection screen for the O/E - Create Open Orders Control Report.

READING IMPORT MASTER FILE....................

o It should then create the orders in Order Entry & editing (Task 1)

STEP 5:

o You have the option to go into each order, to edit, or select the action INVOICE & print the invoices.

o You may also use Sub-task 6 Automated change order status (move to “Accounting” status) CHGSTS.

1. Change open orders file

This changes the status of open orders in `1. Order entry & editing'

--------------------------------------------------------------------

Options: Status to change FROM and TO.

i.e. Change all orders with status `Warehouse/Printed' to a status of `Accounting/Shipped' (change picking ticket orders to invoices ready for batch printing)

Ranges : Order #, Order date, Ship date, Route, Division #, Salesman #, Customer #, Truck Code and opened-by.

WARNING: This will skip orders that other users are working on in order entry & editing. Any changes to orders such as adjustments for shipped or backordered items MUST be done prior to changing TO a status of ACCOUNTING.

o Change open orders file (1)

o 1 Change order status: FROM 1 Sales, TO 6 Accounting. Accounting is invoiced. This assigns an invoice # & invoice date. Line items can’t be added, changed or deleted until the invoice is moved to the invoiced order holding file.

o Once the orders all have the status of accounting, they may be batch printed in Task 1 Order entry & editing, Sub-task 4 Print open order documents, 1 Order documents. The orders won’t move to Task 2 Invoiced order holding file unless they are printed.

o Once the orders are all invoiced and printed, they may be moved to Task 2 Invoiced order holding file either automatically during backup or manually using Task 25 Move invoiced orders (1->2).

o Once the orders are all in the Invoiced Order holding file, they can be posted just like any other order.

STEP 6:

Clear Import master file after Creating Open Orders DWNCLR (Sub-task 5)

This program reads Import Master File and removes records from the Import master file maintenance DWNMNT (Sub-task2). It might be desirable to erase the old records so invoices can’t be created twice or if the spreadsheet is going to be downloaded again after corrections to the spreadsheet.

User options will be: Customer #, Usage date range,

Sequence # and Location.

Do you wish to continue? (YES/NO): ___

Please Enter Customer #:

Please enter - starting Usage Date:

- ending Usage Date:

Please enter - starting Sequence #:

- ending Sequence #:

Enter Location #: