creating statements for the business in which Customers are also Vendors
Proposal summary
The proposed solution is for a betting company. They own shops(Agents) that will need to be a customer and a Supplier in Netsuite. They would like to have a solution to generate the statements which includes all the invoices, bills and payments based on these customer and Supplier records.
Requirement
The business is a betting company. The business owns shops(“Agents”) that will need to be a customer and supplier(which we would need to link) in Netsuite as they are raising invoices for the bets and posting bills for the bet payouts and commissions.
They will want to generate a statement that shows the closing position of the Agent. This would need to be a custom pdf that they can generate from the customer record(via a button) and via a suitelet that allows them to generate and send out all the statements via email. The statement pdf would need to show all the bets, payouts and commissions during a specified period.
Once a week/month, they will need to run a netting process(from the suitelet) that works out if the Agent is in a Debit or Credit position by adding up all the open invoices and bills across the linked Customer and Supplier. It will then need to post credit memos and supplier credits to and apply them against all the open invoices and bills. An invoice will then need to be posted for the net position if the Agent is in a net position or a bill if the Agent was in a Credit position.
Due to the volumes, it would need to run this as a scheduled script in the background.
Our Solution
The proposed solution will connect the customers linked with Supplier records with other relationship native functionality. If the Other Relationship is created from Vendor/Customer, the default Currency set will be the Primary Currency set on the Vendor/Customer.
Action 1: In the customer record the solution will add a button to print the statement from the record. The statement will be created using script/ saved search. The solution will create a search to get all relevant information(invoices, vendor bills payments). The PDF will be created from Script.
Action 2: A suitelet will be designed to print weekly/monthly statements. The user can select current week/ monthly in the filter region using startdate and end date. If the current week selected the statements(invoices, vendor bills payments) for all agents from the current week will be sent to the email specified in the customer record. After selecting the filter, the user needs to click on the “Send statement” button to initiate the email action. And this process will be run in the background, as it takes time to send email for all the agents. We will send an email to the current user once the action is completed. Custom email fields can be designed if needed in customer records for this purpose.
Filters: Start date , end date
Suggestion: So if the user selects the date range, the user can see for which all agents the email is going to be sent for what amount. (More details in the additional recommendation section)
Action 3: We will create a new suitelet for the netting process. Users will be provided with two fields to select the date range (Start date and end date). So the user can select any date range for week or month etc. For better performance, we prefer to select the minimal date ranges. Eg: one week, or max 1 month. Agent selection filter can be also provided to select a single agent. Multiple selection of agents is feasible if required. This can be done for a limited max number. It is not considered in the current estimated effort.
One button in the suitelet for the netting process. In the netting process, the user needs to select the current weekly or monthly option in the filter region, select Agent(If required). A scheduled script will be initiated to send the netting statement. Sum all invoices, sum all bills and add credit memos and vendor credit. The invoices and bills need to be applied with credit memos and vendor credits. If the agent is in net position an invoice will be created. If the Agent is in a credit position, a bill will be created.
Filters: Start date , end date, select Agent(If required).
Suggestion: So if the user selects the date range, the user can see for which all agents the bill or invoice is going to be created and for what amount. (More details in the additional recommendation section)
Transaction Search That Show Net Receivables/Payables For Customers That Have Other Relationships As Vendors
Published 04/05/2018 03:17 AM | Updated 05/27/2021 02:54 PM | Answer Id: 71881
Create a Saved Search Version of Vendor Statements
Published 03/29/2019 12:30 PM | Updated 01/20/2021 12:13 AM | Answer Id: 83687
Assumptions
- Multi-subsidiary customers/vendors are not enabled in the Netsuite Account.
- We will be using the standard statement format for the PDF generation. Please provide if you need to use any other mockups. IF we are using the standard statement format, we will not be considering the aging section in the print as its values cannot be generated using script/saved search
- Currently no customer and vendor records are created in the account. During the customer creation the vendors should be using other relationship feature
- No return process involved in the customer or vendor side in the mentioned business. No need to consider this in the statement creation process.
- Could you please add an approximate transaction volume for a month to design proper scripts?
- Can we restrict the functionality for a max one month?
- Please mention the customer/vendor creation process. We are assuming it is a manual process in Netsuite through UI.
- We will be considering the invoices and bill for the agents in the specific date range to include in the statement. Please let us know if we need to consider any other transactions?
- Currently displaying data in the suitelet before sending the emails is not considered in estimation.
- If the business is using a multi-subsidiary customer, we may need to redesign the solution. In this case we cannot use the other relationship feature directly.
Additional Recommendations
For the “Send statement” and netting process, we can also display the agents involved in the selected date range.
“Send statement”: So if the user selects the date range, the user can see for which all agents the email is going to be sent for what amount.
“Netting process”: So if the user selects the date range, the user can see for which all agents the bill or invoice is going to be created and for what amount.
But to implement the same, we need to identify the maximum number of agents that can occur in the date interval? Eg: in a month what will be the approximate number of agents that can occur.
Once we can approximately predefine the limit and if the number of agents are 20-30(This number can be confirmed only during the development), then we will display the agent details in the suitelet page. So users can use check boxes to select the specific agent and the data will be processed only for those agents in the background. We can use extra filters like department/class/location wise executing to limit the data if you prefer the same.
If there are a large number of agents then we have to use only the button for sending invoice and netting process as per the provided current solution.
Budget and Time
| Task | Time Required |
| Analysis | 1 Hour |
| Button in customer record | 12 Hours |
| Suitelet for statement | 4 Hours |
| Suitelet for netting | 3 Hours |
| Schedule script for statement | 14 Hours |
| Schedule script for Netting | 20 Hours |
| Testing | 8 Hours |
| Deployment and Documentation | 4 Hours |
| Total | 66 Hours |