Proposal summary
JCurve Solutions would like to create new a report to retrieve data for data from Item fulfillment. The data relate to the purchase side (PO No, Supplier, Ref. AP Bill, QTY PO, QTY Receipt) can be displayed only if the item in item fulfillment puts the reference to So No, Reference SO line with the same item code. And print reports in Excel format.
Requirement
New report ‘Item fulfillment pending invoice’ to retrieve data for data from Item fulfillment. The data relate to the purchase side (PO No, Supplier, Ref. AP Bill, QTY PO, QTY Receipt) can be displayed only if the item in item fulfillment puts the reference to So No, Reference SO line with the same item code.
Assumptions:
- This report will retrieve data for data from Item fulfillment
- The data relating to the purchase side (PO No, Supplier, Ref. AP Bill, QTY PO, QTY Receipt) can be displayed only if the item in item fulfillment put the reference to So No, Reference SO line with the same item code
- In case it has 2 Ref. AP Bill with the same PO no, it will show the latest AP Bill reference document.
- Fulfillment amount will use actual cost in case of inventory item but will use PO cost in case of service item
- The selling price will show the current calculate selling price amount relate to revenue arrangement of that sale order but it will not match with the amount that record in revenue recognition
- This report will show Current status of document within the selected date
- Print report as Excel format
Sample Report:
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | As at | Select as at Date to filter transaction | Input as at date to recall report compare with transaction date |
| Report Details | |||
| # | Field | Description | Details |
| 1 | Company Name | Show Subsidiary name that select for report | name of Subsidiary from custrecord_rapid_tht_company_legalname |
| 2 | Item fulfillment pending invoice | Report Name | Fixed |
| 3 | As at | Show date range to recall report | Based on date range to select in the criteria |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Project ID | Project | Project from Item Fulfillment |
| 2 | Sale type | Sale type | Sale Type from Item Fulfillment |
| 3 | Customer | Customer | Customer Name from Item Fulfillment |
| 4 | Saleman | Reference to Sale person from order | Sale person reference from Sale order document |
| 5 | PO. No | PO no | Reference to Purchase order document no reference to sale order noand sale order line with same item code |
| 6 | Supplier | Vendor relate to Purchase transaction | Vendor Name in Purchaser order reference with Sale order No. andsale order line with same item code |
| 7 | Item part | Item | Item Code from Item Fulfillment |
| 8 | Description | Description | Description of Item from Item Fulfillment |
| 9 | Item category | Item category | Item Category of Item from Item Fulfillment |
| 10 | Ref. AP Bill | Reference Vendor bill no | Reference to Vendor bill document no reference to sale order no and sale order line with same item code |
| 11 | QTY PO | Quantity order based on Purchase order | Show quantity in Purchase order reference to sale order no and sale order line with same item code |
| 12 | QTY Receipt | Quantity receives based on Purchase order | Show cumulative receive quantity based on purchase order reference to sale order no and sale order line with same item code |
| 13 | QTY Fulfill | Quantity based on item fulfillment | Quantity from Item fulfillment |
| 14 | Fulfil amount | Amount from item fulfill | Amount of creating item fulfillment |
| 15 | Fulfill Date | Fulfillment Date | Date of item fulfillment |
| 16 | Fulfill No | Fulfillment document no | Document No |
| 17 | Aging Fulfill | Compare date of item fulfillment with date of recall report | Date diff from item fulfillment date with date of report |
| 18 | Com conso | Select com conso from item fulfillment | Comcoso checkbox from Item Fulfillment |
| 19 | Selling price | Show calculate selling price of item | Calculate selling price of this item based on QTY in item fulfillment |
Our Solution
This requirement can be achieved by creating a new report using a transaction saved search. And this search will retrieve data for data from Item fulfillment. The data relate to the purchase side (PO No, Supplier, Ref. AP Bill, QTY PO, QTY Receipt) can be displayed only if the item in item fulfillment put the reference to So No, Reference SO line with the same item code
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Item fulfillment pending invoice. Then upon clicking the “Item fulfillment pending invoice”, the user will be redirected to a report page.
The report will first display a detailed report based on the default filters, such as the subsidiary as G-ABLE CO., LTD, and the As of Date as today, and then show a summary of the total amount in the report’s last row.
The fields given below will be indicated on the header part of the report.
| Criteria/ Available Filter: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As at | Date(trandate) from item fulfillment record |
| Backend Criteria for Report | ||
| 1 | Type | Item Fulfillment |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Item fulfillment pending invoice | Fixed |
| 4 | As at | Based on the date(trandate) select in the criteria |
Please see the table below for the report column details for the detailed ‘Item fulfillment pending invoice’ report. And the fields that are facing difficulties fetching values in searches are marked in red.
| Report Column | ||
| # | Field | Field Name from NS records |
| 1 | Project ID | Project Order(cseg_project_order) field from item fulfillment |
| 2 | Sale type | Sales Type(class) field from item fulfillment |
| 3 | Customer | Customer(entity) field from item fulfillment record |
| 4 | Salesman | Sale person reference(salesrep) from Sale order document |
| 5 | PO. No | Purchase order document no reference to sale order noand sale order line with same item code(Purchase Order Search) |
| 6 | Supplier | Vendor Name(entity) in Purchaser order reference with Sale order No. and sale order line with same item code (Purchase Order Search) |
| 7 | Item part | Item code from item fulfillment |
| 8 | Description | Item description from item record/ item fulfillment(Sales Order Search) |
| 9 | Item category | Item category from item record/ item fulfillment |
| 10 | Ref. AP Bill | Related AP bill of Purchaser order reference with Sale order No. and sale order line with same item code (Purchase Order Search) |
| 11 | QTY PO | Quantity of an item in purchase order reference to the Sales Order Number (Purchase Order Search) |
| 12 | QTY Receipt | Quantity of an item in item receipt reference to the Sales Order Number (Purchase Order Search) |
| 13 | QTY Fulfill | Quantity of an item in item fulfillment reference to the Sales Order Number |
| 14 | Fulfill amount | Amount from item fulfillment |
| 15 | Fulfill Date | Date(trandate) from item fulfillment |
| 16 | Fulfill No | Document Number of an item fulfillment |
| 17 | Aging Fulfill | Date difference between item fulfillment date and date selected in the date filter |
| 18 | Com conso | Comcoso checkbox from Item Fulfillment |
| 19 | Selling price | Revenue Amount line from Revenue Arrangement Record reference to the Sales Order Number(Revenue Arrangement Search) |
Finally, on the report page, an Export button will be added to download the detailed report in Excel format.
When you click the export button, the report will be downloaded in Excel format based on the report’s size in the following cases:
- If the file size is less than 10Mb (based on predefined line count), the excel file will be downloaded directly from Netsuite. And in real-time, the excel file will be exported by split files based on the line counts that can be included in the single file.
- Otherwise, the Report Download will take place through a scheduled process where there is a delay in the process based on the availability of the Netsuite Queue. For that will make use of a custom record to track the requests
- If the file size is greater than 10Mb, then we will split the file into multiple files with predefined line counts in a single file due to the size of each individual attachment cannot exceed 10Mb. And the generated multiple files will be emailed to the initiator’s email if the file count is smaller than or equal to the specific count that we can attach in the email.
- If the size of files is greater than the specific count, we will generate multiple files with predefined line counts in a single file. And all generated files will be saved in NetSuite’s file cabinet and attached to the corresponding custom record entry which tracks that Report generation process.
Once all export functions have been completed in NetSuite, a single email with the custom record link will be sent to the download initiator. The initiator can export files by login into the NetSuite and by navigating to the custom record entry with the link from the email.
The summary line on the Excel file will be calculated based on the result lines included in the corresponding file in the case of split file download
Note: Exported multiple files will be attached to the email is a very exceptional case. i.e. Most of the export function occurs in real-time or the files attached to the custom record.
Assumptions
- Each report page can include 1000 lines, with the 1001st line displaying a summary of the Total Amount of all results in the report.
- Use the four searches ‘Item Fulfillment, Sales Order search, Purchases Order Search and Revenue Arrangement’.
Risks
- The line counts in the single file to progress with the scheduled process can be confirmed during the development phase.
- If the file is more than 19Mb, the user should go to NetSuite and manually download the file from the account.
- The file cabinet will be stored with many files in the future. This will have an impact on the NetSuite account’s file cabinet. So, users have to remove these files from a file cabinet in the future to free up the storage.
- The report on the UI will be displayed as a normal NetSuite Table layout without any styles. It will follow the standard behavior
- We won’t be able to find all of these columns in a single search. To overcome this, we should generate separate searches for each type of transaction. Then, using the script, we need to join all of these search results to display all columns on one page.
- And we required a common identifier column for all searches to be connected in order to join them. Here, the identifier can be used as the item ID and the Reference SO No from the line-level fields. It will be difficult to join the data using these line fields. Because, if we use the item and reference sales orders, the one transaction may have the same item on multiple lines. So the results will be mixed up throughout all items, references, and transactions, and the results will be inaccurate. So we couldn’t guarantee the precision of the report.
- Also, there will be another risk of combining 4 searches based on the page index within the suitelet. Because there will be a chance to reach the script execution limit/time limit. So the script may end up with an error without providing the result. The occurrence of this scenario can be confirmed only at the time of development