Automatic ACH Payment

Proposal summary

We have to create an import system to create payments as per the uploaded CSV file. The Order # will be the key for searching the invoices.

Requirement 

The client needs an automatic process to create payments according to the CSV file uploaded into the Netsuite. The CSV file will be exported from the Faire system. 

1. The user will be downloading the data as a CSV file from a 3rd party
2. The User will be able to upload the file into Netsuite through a User Interface
3. Upon submitting the file to Netsuite, an automatic process should start execution

• The order number will be the PO# in the invoice
• Check the PO# is added in the invoice
• If yes Create a payment record for the same
• If we couldn’t find the Invoice record, then the line should be added in the error file.

Our Solution

We will create a page to upload the CSV file into Netsuite.

The user will be uploading the file to Netsuite & submit it.

In the backend, we will fetch the CSV data and will check the invoice with PO# available in Netsuite.

If the type is refund, we have to create a credit memo with the amount added in the Amount column(Column I).

There will not be any deduction from Shopify discount in the case of a refund.

In the type is charge then we have to create the Payment record apply this payment to the invoice record which have PO#  (Invoice record)= Order# (In csv file).

The amount for the payment record will be the amount deducted from the shopify amount. Ie, the Net (Column K) will be the amount for payment.

So the shopify amount will need to be deducted from the invoice. We have to add the discount item to the Invoice with the particular amount. Deduction amount will be the column Fee (Column J)

If the invoice is not found with PO#=  Order # then the Order # line will be added to the error file.

This error file we can send to a particular employee in the evening. 

Model of the first page

Assumptions

1. If the Order # in the CSV file matches with more than one invoice, then the payments will be created for all those invoices
2. We are fetching & processing the data according to the heading of the CSV file. So we are assuming that the csv will have a unique heading. Also, we are hardcoding the mapping of csv files with the Netsuite fields. So we assume that all CSV files will have a unique heading.
3. We are fetching the shopify deduction amount from the Fee column (Column J)