Directions for the AP Invoice Upload Spreadsheet
The AP Invoice Upload Spreadsheet is used to enter Accounts Payable historical invoices (only, no GL Entry) to the OGSQL system. This spreadsheet is designed to assist in creating the necessary text file needed for the Import Data from ASCII file option on the Exceptional Processing Menu.
Steps to follow
• If necessary, reset the spreadsheet
• Click Reset
• Click Reset again
• Populate columns B through AD with the appropriate data (definitions of fields and data type at end of instructions).
• Click the Check Data button
The data entered will be checked for errors based on field size and data content. If a large number of rows have been entered, this may take some time. Please be patient.
If Check is chosen, the user will be returned to the spreadsheet to make changes.
Errors will be marked on the spreadsheet in red
Hover over the cell with the mouse to see a comment with more detailed information on the error
• Correct any errors and click the Check Data button again. Repeat as necessary until all errors have been corrected.
• If no errors are detected or if ignore is chosen, a dialog box displays to save the text file. Click the button next to the file name[…] and navigate to the location of the directory to save the file
• Click the scan button […]
• Find the location of the directory where the file will be stored. The needed file name will display in the File Name field.
• Click OK
• Back up your database.
• Log in to OGSQL.
• Select your company code if necessary and click OK.
• Place a check next to the AP Invoice option
• Click the Scan button […] and brows to the directory where the file is saved.
• Verify Advanced Selection settings
The advanced selection option allows the user to select a specific directory that will apply to all files selected.
There are also several other options that the user may choose from.
o Rename Files after Import – If multiple files are being uploaded at one time, this option will rename each file when the upload is complete. This is helpful in auditing that all uploads are completed.
o Validate Selected File Names – This option will validate that the file name selected is what the program is requiring.
o Overwrite Duplicate Records – If the file contains a key field that is already in the database, the data in the database will be overwritten by the data in the file. Otherwise, an error on import will be received on duplicate records.
o Report Overwritten Duplicate Records – when the upload is complete, a report of all records overwritten will be outputted.
o Recognize OMIT_ME – Normally, a column of data is considered omitted on an import line if there is nothing there, not even a blank, after its preceding comma and before its trailing comma (if any). If the user has difficulty making a .CSV file, then instead they can put OMIT_ME in that positions and check this box, and that data cell will be treated as omitted.
Omitting something has a different result than putting in a blank or zero. If the user omits something on a brand new record being imported (not a re-write), then the default value for that column is used instead. If they omit something on a rewrite import, then that data item is left untouched when the rewrite is done. This is different than when a blank/zero is included: that will always change the contents of the resulting record to blank/zero.
o Prorate Expense Deck Interests – If an Expense deck participant’s interest changes and causes the total to be higher or lower than the original, this option would prorate the rest of the participant’s interest so that the original total interest would remain the same.
o Prorate Revenue Deck Interests – If a Revenue deck participant’s interest changes and causes the total to be higher or lower than the original, this option would prorate the rest of the participant’s interest so that the original total interest would remain the same.
o View Generic Import Formats – When enabled, the user will be able to view all imports whether they are applicable to the database or not. Use of imports that do not apply to the database before the options required are enabled will cause an error on import.
• Click OK
• Click Import
• Invoice Code – Enter up to a 12 character Invoice Number for the invoice. Required
• Voucher Code – Enter up to a 7 character Voucher code. When importing, leave blank to have the voucher number generated by the import program. If a voucher number is specified, but it must be unused (the first time it is imported here), so that voucher will be generated
• Payment Status – Choose from Payable (not yet fully paid, further payments allowed), Suspend (not fully paid, further payments suspended) or Paid (has been fully paid).
• Invoice Date – Enter the date when the vendor issued the invoice.
• Acceptance Date – Enter the as-of date for acceptance into Accounts Payable
• Due Date – Enter the Full payment due vendor by this date. When importing, this will default to the same as Invoice Date. If using the import to update a record, use NULL to remove the existing date in the record (which will default it back to the invoice date).
• Currency Code – Enter the Currency Code for the Invoice Amount Entered. This code must be available in Currency Code Maintenance at the time of the upload. If this is not a multicurrency Database, use USD.
• Posting Exchange Rate – Enter the exchange rate code that will be applicable to the invoice amount at the time of posting. This Exchange Rate Code must be available in Exchange Rate Code Maintenance at the time of upload.
• Payment Exchange Rate – Enter the Exchange rate code that will applicable to the Payment when entered.
This Exchange Rate Code must be available in Exchange Rate Code Maintenance at the time of upload.
• Invoice Amount – Enter the amount requested by the vendor.
• Discount Allowed – Enter the amount that the Vendor may allow for a discount
• Discount Cutoff Date – Enter the cutoff date applicable to the discount application (or, if "take all discounts"
for this vendor regardless of date).
• Discount Allowed A – Enter any Additional Discount Allowed
• Discount Allowed B – Enter any Additional Discount Allowed
• Discount Allowed C – Enter any Additional Discount Allowed
• Description – Enter a Description for the invoice up to 30 characters or 50 characters if option 253 is enabled.
• Paid Amount - Total amount paid by any method (check, wire, ACH). This field allows 15 characters with up to 2 characters to the right of the decimal
• Discount Taken Amount - Total discount taken on Payment. This field allows 15 characters with up to 2 characters to the right of the decimal
• Discount Taken Amount A - Additional Discount Taken Amount on payment. This field allows 15 characters with up to 2 characters to the right of the decimal
• Discount Taken Amount B - Additional Discount Taken Amount on payment. This field allows 15 characters with up to 2 characters to the right of the decimal
• Discount Taken Amount C - Additional Discount Taken Amount on payment. This field allows 15 characters with up to 2 characters to the right of the decimal
• Payment Type – Enter the 1099 Box to report payments in. Choose from RENTS, ROYALTIES, OTHER INCOME, MEDICAL PAYMENTS, NONEMPLOYEE, GROSS ATTORNEY FEES or leave blank if not a 1099 amount.
• DocVue ID – If DocVue is enabled, enter any applicable DocVue ID.
• Accrual Date - Enter the accrual date in mm/dd/yy format. If using the import to update a record, use NULL to remove the existing date in the record.
• Include in Accrual Report – Enter a Y or N to indicate if the record should be included in an Accrual Report.
• Alternate Reference – Use only if option 303 is enabled in OGsql. Enter the alternate reference code as required.
• VAT Number – Use only if option 303 is enabled in OGsql. Enter the Value Added Tax number as required.
