Implementation Balance - SL Open Invoices

Getting Started

If you have unpaid subcontract invoices at the time of conversion be sure to import your Subcontract Open Invoices to carry over those outstanding balances and maintain accurate tracking of amounts still owed to subcontractors.

Template Info

• Required Source Data
  • Header Table
    • Record Key
    • Vendor #
    • Invoice #
    • Invoice Date
    • Due Date
    • Description
    • Invoice Total
  • Line Table
    • Record Key
    • Subcontract #
    • SL Item #
    • Job #
    • Phase #
    • Cost Type #
    • Description
    • Unit of Measure
    • Invoiced Units
    • Invoiced Unit Cost
    • Invoiced Amount
    • Retainage Amount
• Optional Source Data
  • N/A
• Additional Tips
  • The Record Key column is used to link the Header and Line records together for each invoice, so make sure the numbers are unique and assigned appropriately.
  • If your Open AP Invoices are related to Subcontracts or Purchase Orders then they need to be removed from this data load. Those invoices must be coded to the respective SL/PO numbers.
• Basic Steps
  • Download TAD Template
  • Copy the Source Data
  • Import the Records
  • Post Open Batch
  • Validate Imported Data

Download TAD Template

Open your source data file in Excel.

Click the Templates button on the left side of the ribbon.

If you've signed into TAD before, then you should receive the message below. Click Yes.

If you have not signed into TAD before, follow the Get Connected instructions.

Select the Implementation Balance – AP Open Invoices template.

Click the Download Selected button.

This template includes multiple tables, so make sure to check the Skip Remaining Parameter Entry box and click the Download button to download them all at once.

You’ll see multiple worksheets, as shown below.

The AP Entry Header and AP Entry Lines sheets are used to import the AP invoices.

The AP Vendors sheet is used to pull the default 1099 and Pay Method values.

Copy the Source Data

Copy and paste the source data into the AP Entry Header sheet. If your source data layout matches the example above, this will be a single copy and paste since the AP Entry Header columns are already in the same order.

Copy and paste the Description, Gross Amount, and Retainage Amount values from the source data into the AP Entry Lines sheet. This should also be a single copy and paste since the AP Entry Lines columns are already in the same order.

Enter your Suspense or Clearing GL Account in the GLAcct column.

Import the Records

Go to the AP Entry Header worksheet.

Click the submenu of the Format Table button on the ribbon and choose the Regenerate Excel Formulas button. This will apply XLOOKUP and other formulas to multiple columns pulling default values from the AP Vendors worksheet.

The Mth column defaults to the current month. Update this value if you're loading balances as of the prior month.

Click the Format Table button to format the data you copied and to apply missing default values.

The Result column will show Formatted for every record. You will see the default Company number applied.

Go to the AP Entry Lines worksheet.

Click the submenu of the Format Table button on the ribbon and choose the Regenerate Excel Formulas button. This will apply XLOOKUP and other formulas to multiple columns pulling default values from the AP Vendors worksheet.

The Mth column defaults to the current month. Update this value if you're loading balances as of the prior month.

The Mth value should match the Mth value on the AP Entry Header worksheet.

Click the Format Table button to format the data you copied and to apply missing default values.

The Result column will show Formatted for every record. You will see the default Company number applied.

Go to the AP Entry Header worksheet.

Click the Get Batch # button to assign the next batch number to both the Header and Lines worksheets.

The Result column will show Batch #XX Assigned for every record.

Click the Assign Batch Sequence button to assign a sequential value to each record on the Header and Lines worksheets. This process assumes one line per invoice and that each invoice appears on the same row in both tables.

The Result column will show Batch Sequence Assigned for every record.

Click the Validate Import button.

The Result column will show Validated for every record. If this says Failure, then the ErrorMsg column will provide the error.

Click the Execute Import button.

The Result column will show Imported for every record.

Go to the AP Entry Lines worksheet.

The Result column will show Batch Sequence Assigned for every record since that was the last action on the worksheet.

Click the Validate Import button.

The Result column will show Validated for every record. If this says Failure, then the ErrorMsg column will provide the error.

Click the Execute Import button.

The Result column will show Imported for every record.

That completes all steps needed in TAD. You can now open the batch in Vista to validate and post.

Post Open Batch

Before opening the AP Transaction Entry batch, you need to turn off Interfacing to the General Ledger. This prevents the batch from posting GL activity, which we do not want when loading these balances.

In Vista, go to Accounts Payable → Programs → AP Company Parameters.

On the GL Expense Posting tab, write down your existing value for the GL Expense Interface Level. Then select None, click Save and minimize the form.

If your invoices are coded to Jobs, turn off the JC Interface Level on the Subledgers tab, as you do not want job cost detail generated from this batch.

Now you can open the batch in AP Transaction Entry.

With the batch open, go to File → Process Batch, then click the Validate button followed by the Post button.

Once the batch posts, make sure to change your Interface Levels back to their normal values.

Validate Imported Data

You can validate the Open Payables by running the AP Open Payables report in Vista.

Enter your Company and the Through Month that you just imported.

Make all other parameters look like the image below.

Verify the Payable Amount column equals the total amount of invoices you imported.

If you imported Retainage Amounts, validate those against your Retainage pay type.