Before conducting this process, you should have the following:

• Access to the two company Westpac accounts
• Google Sheets with the Zuora to Lockbox extension installed
• Admin access to Zuora
• Access to the Zuora Marketplace app called Lockbox
• Access to the remittance@jetinteractive.com.au email address where our payment remittance notifications are sent.

 

Part 1: Download payment records

Open your Westpac business account via the link here.

 

Log in using your details.

 

Navigate to the first account using the top header bar and click into the Account Activity section.

 

Next to the account summary, choose Exports and reports.

 

Choose to export the list of transactions.

 

Select the account, and the file format of CSV. The date format should remain as DD/MM/CCYY.

The date range should start on the last day this report was generated, and go to the last available date (yesterday).

Eg: If the last report you did was Friday 13th January (14/01/2022) and it is now Monday 17th January, the date range will be 14/01/2022 to 16/01/2022.

When you do the new report on Tuesday, the date range will be 17/01/2022 to 17/01/2022 (the last generated date was the 17th, and you cannot export data from today.

 

Click Export and the csv file will download to your computer.

Repeat these steps for the second Westpac account.

 

 

Part 2: Format data

Head to Google Sheets and Import & Upload your first csv.

   

 

Choose to create a new spreadsheet and select Import.

 

When the file has been imported successfully, hit Open Now.

 

From the Extensions menu, choose the Westpac to Zuora Lockbox, and select Format.

 

This will automatically create two new sheets in your spreadsheet - one for any BPAY payments (call Lockbox Upload), and one for EFT.

 

BPAY

The BPAY/lockbox upload sheet will not need any further action for formatting.

If there is data in this sheet, just download the sheet as a csv.

 

EFT

The EFT sheet will have the fields for Account Number and Invoice Number blank.

You will need to fill out these fields before downloading the sheet. 

Account = Zuora Account Number - this is the account number that appears in the client's Zuora account, and will be one digit longer then their Jet Hub account (eg: 1104 will be 11049).

Invoice Number = Zuora Invoice Number - this is the invoice number this payment is to be applied to.

 

IMPORTANT - If you have a payment that will be applied to multiple invoices, you will need to copy the payment details to multiple lines for each payment, and adjust the values in the Amount column to equal the total value of the payment.

Some of the entries should be easy to locate many businesses include their account name in the payment description (eg: Alsco = account 2933) but you may need to use the remittance notices sent to remittance@jetinteractive.com.au to find these details and allocate the funds correctly.

If you are unable to locate any matching invoice values or match a payment to a single account name without any doubt, do not process the payment.

Wait for the customer to contact us and provide correct payment remittance information

 

Once all fields are filled out in your EFT sheet, download this as a csv.

Repeat all of the above steps for both of the Westpac accounts. You will likely only have one sheet with data in it per account (one for BPAY and one for EFT).

 

Part 3: Upload payments

Open Zuora and choose Configurable Lockbox from the left menu panel.

Click into the lockbox ID 1425212 - 8 Jul 2019.

 

Select Lockboxes from the tabs along the top. Next to the name New Upload - Invoice Number, choose the 3 dots on the right and select Upload.

 

Uploda the csv file that contains your completed payments.

Repeat this process for each downloaded payment sheet that you have.

 

Select Runs from the top tab options, and click refresh on the top right. Keep hitting refresh until all the documents you have uploaded are in a Finished status.

As per the image below, your number of records processed should also equal the number of successful payments applied.

 

If these numbers are not equal, click the 3 dots to the right of the finished payment run, and choose View Payments. 

You will be able to see the list of payments, and scroll to the right to see any error messages you are getting.

 

You can then fix the errors in your payments sheet, redownload and try your upload again.

 

 

One all payments are uploaded, head to the Payments tab in Zuora.

 

Cast your eye over the most recent payments that show here. These should match your upload amounts and count of payments.