Jira Code : MAMS-101
Proposal summary
This proposal describes the Automatic Purchase order creation based on Sales of items and report of
the sales data.
Requirement
The client would like to view the sales and stock details of items for a particular time period on a
custom page. And based on the sales of items, need to create purchase orders automatically for the
selected items.
Our Solution
We will create one custom page which will show the items and related sales details.
Once the user selects the items and clicks “Generate purchase order” button, the purchase orders
will be created with entered quantity.
Order Management page:
Search Button
When the user clicks search button, the sales data will be filtered based on the from date, to date, and location and the data will show on the custom page.
Generate purchase order Button:
Once the user clicks the generate purchase order button, the user navigates to the Custom record to view the processed status. The purchase orders will be created for the selected items in the back end.
Custom record setup:
The custom record will contain the following fields:
• Date: processed date
• Status: The status of the process, like Not started, Processing, Failed, and Completed
• Success file: Once the purchase order creation completes, a file will be created with a new purchase order number and the file will attach to the custom record.
• Error file: If the script failed to create purchase orders due to an error, a file will be created with errors and the file will add to the custom record.
Cancel Button:
Upon clicking the cancel button, the user will be navigated to the home dashboard.
Selection Criteria Fields:
The user will be able to enter the following fields for the filtering out of the sales data:
• Location: This will be a list field. Users can choose the location in this field. If this field is blank, then the custom page will show details in all locations by default. Once the user chooses the location, the data on the custom page will be filtered by location.
• From Date: The date in this field will be today’s date by default. Users can choose the date in the “From date” and sales orders having trandate after this “from date” will be filtered.
• To Date: The date in this field will be today’s date by default. Users can choose the date in the “To date” and sales orders having trandate up to this “To date” will be filtered.
• Vendor: This field will be a multiple-select field. Users can select multiple vendors in this field. Once the user selects the vendors and clicks the filter button, the table will show the items having the preferred vendor as selected vendors.
Table fields:
The table will show the item details and sales order quantity for each item separately.
• Checkbox: Users can enable this checkbox to create a purchase order for this item.
• Item: The Item field will show all items which are sold on the selected date
. • Description: The description of the item will be shown in this field.
• Location: The location of the sales order will be shown in this field.
• Quantity sold: This field will show the sum of all invoice order quantities of the corresponding item with the date range selected in “From Date” to “To Date”. The record considering will be invoice, cash sale(Both standalone and sales order related). We will not be considering the cash refund for decreasing quantity sold. No other records will be considered for this line calculation
• Quantity on hand: The quantity on hand of the item in the respective location with current Date data
• Quantity available: The available quantity from the item record for the corresponding location with current Date data
• Quantity On Order: The quantity on order from the item record for the corresponding location will show in this field with current Date data. The status of the purchase order that we consider is that is not received or is pending approval.
• Vendor: The preferred vendor from the item record will show in this field by default. Users can select a different vendor in this field manually. Once the user clicks submit button, the purchase order will be created using a vendor from this field. If the vendor field is blank, then the user cannot submit the form and the script will show an alert to select the vendor.
• Reorder Point : The Reorder Point is the quantity level at which you need to reorder or build more of this item. It will be sourced from the corresponding item record based on the Sales order location.
• Stock level : The preferred stock level is the optimum quantity to maintain in stock of an item. The quantity you enter here is used to determine your replenishment needs on the Order Items page. It is the quantity you want to have in stock after an order is placed.
• Quantity: The total sales quantity of the item will show in this field by default. Users can change the quantity manually. When the user clicks submit button, purchase orders will be created with this quantity.
Custom Page details:
• Mark All and unmark all buttons will appear at the top to easily select the lines.
• The page size and page index will display on the custom page. One page will contain a maximum of 100 lines. Users can click on the page index to move to the next page.
Sales Data:
• The sales data will be filtered by the selection criteria (From date to date and location) entered by the user.
• We will display the sales by considering only approved sales orders.
• The data will be sorted based on the item name.
Page Validations:
• The entered quantity in the table should be greater than zero. We will add validation for the quantity field.
• If the user clicks “Generate purchase order” button, without selecting any line the following alert will show.
• The From date should not be greater than the to date.
• The default date of from date and To date will be today’s date
. • Location will be blank by default.
• The vendor field in the table will be a mandatory field. If the user clicks the “Generate purchase order” button with a blank vendor field, an alert will show in the field.
• Vendor field will be validated based on the location selected.
Purchase order creation:
• If the user selected multiple item lines of the same location and vendor, the script would create a consolidated purchase order with multiple lines for each item according to location and vendor
• The status of the new purchase order will be “Pending approval.”
• Field mapping will be defined in the development stage.
• A scheduled script will be running to create the purchase order in NetSuite.
Email notification:
If an error happens in the creation of purchase orders, the email with the error file will be sent to the user who clicks the “Generate purchase order” button to notify the purchase order creation failure.
Assumptions:
• We will consider only approved purchase order to calculate the order quantity and show it on the custom page.
• The script will consider items under all subsidiaries and locations.
• This customization is applicable only to inventory items.
• The custom page will be updated in the development stage. • The Date in the purchase order will be the created date of the same.
• Document number of a purchase order will be auto generated by NetSuite.
• No restriction to the custom page. We can add restrictions if you specify the roles or users.
• The currency in the purchase order will be the default subsidiary of the vendor.
• Pricing and unit of measure of line level items will be sourced automatically in purchase order and we will not be altering this field
• If the item doesn’t have a preferred vendor, the vendor in the table will be blank. Users can select a vendor in the table manually. And the purchase order will be created for that vendor if the script doesn’t have any errors.
• Script will ignore other mandatory fields in the purchase order.
• Field mapping of the purchase order will be defined at the development stage.
Risk
• The NetSuite will enforce concurrency limits on Suitelet.
• The failure during the purchase order creation process is notified to the user via email. So, users have to review the same and reprocess the failed one again.
• The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
• The update or deletion of the existing purchase order is not possible through this custom page.
• We considered paginated results and data selected on one page will not be passed to the next page. For example, if we select data lines on the first page and navigate to the second page without clicking the “Generate purchase order” button, the selected lines on the second page will be considered for purchase order creation.
• We used the advanced layout for the page design.