Proposal summary
We will be generating journal entries by aggregating sales transactions using different combinations. We will be creating one journal entry daily to store daily store credit.
Requirement
- Journal entry for sales data
Design Bundles requires require to create transaction in NetSuite for their sales via integration with Design Bundles. In-house built system. Currently there are 5,000 – 10,000 transactions a day. Design Bundles require to be able to report their sales by VAT type, customer type and payment type in addition to class and location standard NetSuite segments.
- Journal entry for store credit
A journal will be created daily that updates the current store credit liability.
Our Solution
We are developing the Restlet APIs for posting the details from the Design Bundles in to Netsuite. Design Bundles can initiate the Restlets URL (API) from your end with necessary details, which will invoke the Restlet script in NetSuite and creates journal entries in Netsuite.
For authentication between Netsuite and the Design Bundles we are using the TBA(OAUth 1.0) authentication. Detailed explanation for the authentication details including how to create the signature are added in the document below.
1) Journal entry for sales data
We assume that Macrofin will create following custom fields and enable segments on transaction line as part of the standard implementation.
- Class (4 options)
- VAT (Value-added Tax) (2 Options)
- B2B/B2C (2 Options)
- Payment Type (5 Options)
Design Bundles will create and map above mentioned classification and custom fields in their system.
Design Bundles will initiate the Restlet API with sales transactions. We assume that there will be singe request initiating per day.The data which fall in any of those combinations of above custom fields, are aggregated and are entered into a journal entry. This means that initially each journal will have up to 80 lines but this may increase slightly as more classes or payment types are added.
We will maintain a custom record in Netsuite to store the order data that we received from Papier. In case of any failure, we will store the failed data with reason in this custom record.
Sales transactions are aggregated on the basis of location also. Sales transactions which share same location will be aggregated into one journal entry. Likewise, if 30 locations are there, 30 journal entries will be created per day.
2) Journal entry for store credit
A journal will be created daily that updates the current store credit liability. We assume that, design bundles will send the details with a single amount through Restlet API. So that amount will be debited and credited in the given accounts. So, for this purpose, only single journal will be created daily. We also assume that there will be singe request initiating per day.
We will maintain a custom record in Netsuite to store the order data that we received from Papier. In case of any failure, we will store the failed data with reason in this custom record.
Note: We are not maintaining any aggregate or classifications based on the Class, VAT, B2B/B2C, Payment Type for the store credit journal entry. We assume that this will be a single amount per day.
Page Break
Mandatory Fields and Other Fields needed in the Journal entry are as follows.
| Mandatory Fields | Other Fields |
| Currency | Posting Period |
| Exchange Rate | Reversal Date |
| Subsidiary | Defer Date |
| Date | Memo |
| Account | Credit |
| Debit | |
| Name | |
| Sales Channel | |
| Location | |
| Schedule | |
| Last Date | |
| End Date | |
| Residual |
Mandatory fields data should be provided in the journal entry. Otherwise, journal entry cannot be created. If there are multiple subsidiaries, multiple journal entries will be created.
As per the NetSuite syntax, sales transactions can be aggregated together only if they have same subsidiary, Exchange rate and Date.
Assumptions
- We assume that Design bundles will be providing all the details needed for the journal creation in the JSON format. And we assume that they will be posting the data using the Restlet API.
- We assume data regarding custom fields will be included in the JSON request. And we also assume that each sales transaction will be separate JSON objects.
- Restlet script of Netsuite have a maximum size limit of 10 MB while posting the data. So, Design bundles transactions data should comprise within the limit when posting the data in the API request. If there are more than 10 MB data, then it needs to send as separate Restlet API request. We are creating journals based on the data in the single request. If same combinations come in another request, we will create new journals.
Risks
- For the sales credit journal, the amount is not classifying based on the customer. This will be a single amount entry for now.
- In case of any failure for the record to be created in Netsuite(due to invalid details, missing mandatory fields etc), we will store the failed data with reason in the custom records. We will not consider the failed requests again for re-processing.
- While doing the aggregation based on the combinations there can be slight decimal point differences when we add up.
- Restlet script charteristics
- 5,000 usage units per script
- Maximum 10MB per string used as RESTlet input or output
Time: 50 hours