Purchase Requisition Import

Purchase requisition information may be imported from an Excel workbook (template).

Creating a Purchase Requisition Template:

For first-time users, contact SGA to provide a workbook template, containing the required Summary and Detail worksheets.  The Excel workbook (template) consists of two worksheets: Summary and Detail. Many templates may be created, named, and saved for various purposes, as follows:

Summary worksheet

Detail worksheet

Above is an example of a saved template for employee expense reimbursements, listing the appropriate items and account#’s (both Summary and Detail tabs). Additional lines may be added and account#’s may be duplicated on multiple lines. The template may be named and saved for one or more employees.

Using a template to enter employee expenses :

Open the appropriate saved template file and enter the details.

Summary worksheet contains the front-end purchase requisition information (vendor, ship to info, discount, shipping, etc.).  Entry on the Summary worksheet will override the normal defaults on the Purchase Requisition screen.  If nothing is entered on the Summary worksheet, the normal defaults will be populated on the entry screen upon import.

Branch: Enter the branch# (not the name).

Vendor#: Enter the vendor# (not the name).

Discount and Shipping/Handling: If discount and shipping is to be keyed on the worksheet, each of these requires entry into columns B, C, and D (shown above).   

Column B: select Dollars or Percent

Column C: enter dollars as xx.xx and percents as 1 for 1%, 1.5 for 1-1/2%, etc.

Column D: enter G/L account#.

Discount and shipping info may be imported from the spreadsheet or keyed on the Purchase Req entry screen at the time of import.  

Comments: Comments entered here will be printed on the purchase requisition.   

Trans Mo/Yr:  If Month/Year is entered here, this month will be used on each line upon import, unless month/year is overridden on one or more detail lines.  Enter month/year with either a dash or slash (mm-yyyy or mm/yyyy).

Detail worksheet for the detail line/s of the requisition (remarks, amount, etc.).  

Trans Mo/Yr: Enter month/year with either a dash or slash (mm-yyyy or mm/yyyy). This will override current month upon import and Trans Month/Year on Summary worksheet.  If Trans Mo/Yr is left blank on both Summary and Detail worksheets, current month/year will be used upon import.

Purchase Date: Enter date with either dashes or slashes (mm-dd-yyyy or mm/dd/yyyy). If left blank, the current date will be used upon import.

Item#: Optional.

Quantity: Required.

Unit Price: Unit Price or Unit Price Code is required.  Enter a flat amount into this column.

Unit Price Code:  A unit price table may be setup in A/P - Setup - Unit Price.  For example, add a unit price table code named  "MILEAGE" with the current mileage rate of $.xx. The user would then enter "MILEAGE" in the Unit Price Code column on the worksheet and leave Unit Price blank.  Upon import, the unit price will be auto-added from the table file and mileage dollars calculated.  The effective date on the table file kicks in based on the purchase date entered on the requisition. See How to Setup a Unit Price Code below.

Total: The Total column is a worksheet calculation only.  Enter the amount in the Unit Price column, not in the Total column.

Remarks: Required.

Account#: Required. G/L account# will be validated upon import. This validation includes the user account ranges allowed. This column is formatted on the worksheet to add the dashes when account# is entered. To change the placement of the dashes in the account#, click to highlight  the Account# column, right-click - Format Cells.  Enter the format of the account# with zeros and dashes in Type. Click OK.

Note: You may add additional columns on the Detail worksheet for notes, descriptions, etc., as well as hide, delete, or rearrange columns, as long as the required columns are defined on the Import screen. Required columns are Quantity, Unit Price/Code, Remarks, and Account#.

If using a template, unused lines may remain on the Detail worksheet. The import will skip any lines that do not have Unit Price or Unit Price Code. In the following example, the employee has expenses for Conference Fee, Mileage, and Refreshments. It's not necessary to delete the unused lines for Conference Expense, Travel Expense, and Incidentals. The import will ignore these unused lines if there is no Unit Price or Unit Price Code.

Import will ignore unused lines.

Blank lines on the Detail worksheet are NOT allowed; the import will stop when a blank line is encountered. The following example is INCORRECT. The import will stop when it encounters the blank line (Line#5) and Line #6 would not be imported.

Import will stop when it encounters a blank line!!

Save the workbook for later importing.

How to import using a template

Menu option is Purchase Requisition - click the New Req button.  Click the Import button.  Workbook path, filename, and column definition can be saved as a View.  Once saved, you can simply select the view name and click the Import button.  The workbook information is validated, imported, and loaded onto the screen.  Warnings and/or invalidations are displayed on an error log.  If any required entry is missing or invalid, the spreadsheet will not be imported.  The spreadsheet must be correct before importing.  The front-end purchase requisition entry screen may be edited and/or additional attachments added before clicking the OK button.  Click OK to see the Detail entry screen.  

How to define the workbook and save a view

Click the New Req button. Click the Import button. Click the Browse button to locate the Excel workbook to import.  The Detail Columns are defined on the lower part of this screen.  Add a line to define each column you wish to import from your spreadsheet by clicking the Add icon, click in the Item line and select an item from the drop-down.  Select the column for this item.  See note above for the columns that are required. Once defined, click the Save View icon at the top of this screen and name the View to save the path, filename, and column definition.  These are saved as a view for all users or an individual user.  

How to setup a Unit Price code

Menu option is A/P – Setup – Unit Price. The effective date on the table file kicks in based on the purchase date entered on the requisition.