Proposal Summary

This proposal covers the scope of creating the Report for Margin to the Netsuite Production account of RSP Supply. We will be creating a custom page for margin reports and users can download the report after its creation on a scheduled basis.

Requirement 

RSP Supply needs a report for Margin with the item name with Transaction’s Quantity, Date, Document Number, Document Type, Document Status, Amount per item, Ship Date, Sales Rep, Vendor and Manufacturer. The Transactions to be considered are – Sales Orders, Cash Sales, Purchase Order, Bills and Invoices which are available as Sales Orders’ Related Record. The Report should have filters- Sales Order Date, Tran date, Ship Date.

Our Solution

Adding PO-related records

We will run a script at the save action of the vendor bill and bill payment to find out related sales orders. Once found, the vendor bill and bill payment details will be attached to the “SO Related records” tab in the sales order.

Note: This will be applicable if and only if the bill and bill payments related PO is a dropship PO. Because dropship PO has only the connection with the sales order.

Margin Report

We will be creating a custom page for the “Margin report”. From this page users can generate the margin report based on filters and the users can download the generated report. 

In the margin report custom page it will have the following elements.

• Filters: Sales Order Date, Tran date, Ship Date  (start date and end date)
• Button: “Generate report”

In the Margin report page users can provide the necessary filters. After providing the filters, users can click on the “Generate report” button on the page. On the click, we will create a custom record to store the request and based on that another backend scheduled script will be initiated which will generate the report in the background.

The file of the report will be added to the file cabinet and an email will be sent to the logged in user along with the file link.

● The user has to open the email and click on the download link provided in the email.

● By clicking the link the user is able to download the file.

NOTE: This will be done in a schedule script, so the email may take some time to send. It depends on the amount of data to be processed and speed of the scheduled scripts executing in the account.

Scheduled Script Background – Report details : We will be creating multiple Saved Searches with all the required fields and these saved search reports will be connected together with NetSuite Script to create the report.

This report will be having Transaction’s Quantity, Date, Document Type, Document Number, Document Status, Amount per item, Ship Date, Sales Rep, Vendor and Manufacturer. The Transactions to be considered are – Sales Orders, Cash Sales, Purchase Order, Bills , Invoices and bill payments. Related SO Name and SO date is provided as the main identifier. The transactions we are considering will be from the ‘Related records’ of the Sales Order Also, filters will be provided with the  Sales Order Date, Tran date,and Ship Date. The Report we’re using to generate the final report is as below:

If a particular sales order(SO40440) has multiple related records, for example, multiple POs(PO17925,PO17926) , then the same related SO’s line will be repeated again along with the new PO line. 

The final report format is added below : https://docs.google.com/spreadsheets/d/1zg79Rvok4cO-gY8O1WktNiwVRRmxQrhDQDr2W9g17Pc/edit#gid=0

Assumptions

1. We’ve Only considered Sales Orders, Cash Sales, Purchase Order, Bills and Invoice Transactions for this Margin Report
2. If a particular sales order(SO40440) has multiple related records, for example, multiple POs(PO17925,PO17926) , then the same related SO’s line will be repeated again along with the new PO line. 

3. We are not displaying the data to the user from the margin report page. We will be providing the option to download the file only.
4. The report will not be generated real time. The time can vary based on the date range in which  the data is requested. The approximate time can vary from minutes to hours. (Ideal case minimum 1 minute – maximum 1 hour)

Risks 

• The user might receive the final email after some time from initiation or it might not be an instant action.
• File cabinet The maximum allowed size of a downloadable file in the file cabinet will be 10MB.
• Even if we are using the scheduled script for the report, we might have to set a limit for the maximum date range for the date filters that the user can request. Eg: SO date between 1 year, 2 year etc. So the final confirmation for the maximum allowed range can only be identified during the development stage.