Customization DS1552- Mass Import AP Apply Records

Customization DS1552

Mass Import AP Apply Records

Problem Definition:

ACME issues pre-payments in GP to Vendors. After the payables invoices are posted in GP, users need to apply them to the pre-payment. The list of invoices are in an Excel file and ACME would like a way to quickly import the Apply information into GP to make the Apply process more efficient.

Design Features:

Auto-Apply from Excel

Navigation: Apply Payables Documents >> Additional >> Auto-Apply from Excel

A screenshot of a computer Description automatically generated

This window can only be accessed if there is a Vendor and Document Number displayed in the Apply Payables Documents window with an Unapplied Amount greater than $0.

Field Function
File Enter or browse from the button the Excel file to import
Auto-Apply Processes the Excel file. The file will open and GP will read the lines from top to bottom, using the logic on the next page to do the Apply. After the routine runs, the Apply Payables Documents window will refresh and all of the lines that could be applied will be marked, and the Apply Amount will be updated.
Cancel Closes the window without processing the file

Excel File Read/Write

The Exel file ACME will use for this process is displayed below:

A screenshot of a computer Description automatically generated

The import enhancement will use columns B, E, and I for the import.

B = Vendor ID

E = Invoice Number

I = Payment Number

The enhancement will begin reading lines on Row 2.

The import routine will perform the following validation during processing. If a line does not pass the validation, and thus cannot be applied, the error “Could Not Apply” will be written back to the Excel file in Column J. The validations considered in the utility will be:

  1. The invoice is posted
  2. The document type is Invoice
  3. The invoice is not on hold
  4. Both documents are in the same currency
  5. Apply amount will be greater than $0

If a line passes the validation, it will be applied. Lines will be applied until the Unapplied Amount on the payment is $0.

Assumptions/Requirements

  1. The functionality described above will not work with eConnect, any software that uses eConnect, or any software that directly writes to, updates, or deletes from SQL tables.
  2. The functionality described above is intended for the GP Desktop client.
  3. National Accounts functionality is NOT used.
  4. Advanced Distribution functionality is NOT used.
  5. Unless otherwise noted in this document, reporting is not included in this estimate.
  6. Unless otherwise noted in this document, Word Template functionality is not addressed.
  7. Unless otherwise noted in this document, the enhancement will not integrate with 3rd party products. Some examples of 3rd party products would be:
  • An ISV plug-in product including WilloWare products
  • A dexterity customization designed by another developer
  • Dynamics GP Modules including, but not limited to:
  • Project Accounting
  • MDA
  • Analytical Accounting
  • Copy functionality found in SOP, POP and Inventory
  • Field Service
  • Extended Pricing
  • Manufacturing

For information on this design, or any other WilloWare customization or product, please contact us at:

www.willoware.com/contact-me/