Proposal summary
JCurve Solutions would like to create new a report to retrieve data from Purchase orders and Item receipts with getting details from inventory item Lot/ Serial item that are still available on date recall report. In case an item in one purchase order has an item receipt more than one time, it will show a separate line based on the receipt date. And print reports in Excel format.
Requirement
New report ‘Inventory Aging Report’ to retrieve data from Purchase orders and Item receipts with getting details from inventory item Lot/ Serial item that are still available on date recall report. In case an item in one purchase order has an item receipt more than one time, it will show a separate line based on the receipt date. And print reports in Excel format.
Assumptions:
- This report will retrieve data from Purchase order and Item receipt with get details form inventory item Lot/ Serial item that still available on date recall report
- In case item in one purchase order has item receipt more than one time, it will show separate line based on receipt date
- Print report as Excel format
Sample
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | As at date | Select as at date for report | Input range date to recall |
| 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 | Inventory aging report | Report Name | Fixed |
| 3 | As at date | Show date as at | Based on date select in the criteria |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Project ID | Project ID | Project ID from line level of Purchase order |
| 2 | Sale Type | Sale Type | Sale type from line level of Purchase order |
| 3 | Customer | Customer | Reference customer from reference so no in Purchase order line |
| 4 | Sale Man | Sale person | Reference sale rep from reference so no in Purchase order line |
| 5 | PO# | Purchase order No | Purchase order number |
| 6 | Supplier | Vendor Name | Vendor Name from Purchase order |
| 7 | Item Category | Item Category | Item category from line level of Purchase order |
| 8 | Status | Project Status | Reference to project status |
| 9 | Item Class | Item Class | Item class from item master data |
| 10 | Brand | Brand | Brand from item master data |
| 11 | Item / Part | Item | Item from Purchase order |
| 12 | Description | Description | Description of item from Purchase order |
| 13 | Unit | Unit | Unit of item from Purchase order |
| 14 | Receipt Date | Item receipt date | Item receipt date |
| 15 | Cost | Total cost based on item receipt | Quantity from item receipt * rate with cost that already landed to item receipt |
| 16 | Quantity | Quantity Receipt | Quantity from item receipt relate to item in purchase order |
| 17 | Age | Compare date of item receipt and report date | Calculate date different from item receipt with report date |
| 18 | 180 Days | Calculate date different from item receipt with report date >= 180 | |
| 19 | 270 Days | Calculate date different from item receipt with report date >= 270 | |
| 20 | 360 Days | Calculate date different from item receipt with report date >= 360 | |
| 21 | 450 Days | Calculate date different from item receipt with report date >= 450 | |
| 22 | 540 Days | Calculate date different from item receipt with report date >= 540 | |
| 23 | More than 540 Days | Calculate date different from item receipt with report date > 540 |
Our Solution
This requirement can be achieved by creating a new report using a saved search. And this search will retrieve data from Purchase orders and Item receipts with getting details from inventory item Lot/ Serial items that are still available on the date recall report. In case an item in one purchase order has an item receipt more than one time, it will show a separate line based on the receipt date.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Inventory Aging Report. Then upon clicking the “Inventory Aging Report”, 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., LTDI, and the As at date filter 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.
| Filters/ Criteria tabs Shown in Report Page: | ||
| # | Field | Field Name from NS records |
| 1 | Subsidiary | Subsidiary(subsidiary) list |
| 2 | As at date | Date(trandate) from item receipt |
| Backend Criteria for Report: | ||
| 1 | Type | Purchase Order |
| 2 | Applying Transaction | Item Receipt |
| Report Details: | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Inventory aging report | Fixed |
| 3 | As at date | Display the date selected in the criteria |
Please see the table below for the report columns for the detailed ‘Inventory Aging’ report.
| Report Column: | ||
| # | Field | Field Name from NS records |
| 1 | Project ID | Project Order from line level of Purchase order |
| 2 | Sale Type | Sale Type from line level of Purchase order |
| 3 | Customer | Customer from Reference SO NO line of Purchase order |
| 4 | Sale Man | Sales 1 from line level of Reference SO NO of purchase order |
| 5 | PO# | PO#(tranid) from the purchase order record |
| 6 | Supplier | Vendor Name(entity) from Purchase order |
| 7 | Item Category | Item category from line level of Purchase order |
| 8 | Status | Status of the project on a related Reference SO NO line in purchase order record [Purchase Order Search + Sales Order search(Project join fields)= Common field is Reference SO NO] |
| 9 | Item Class | Item class(custitem_itemclass) under custom subtab on item master record |
| 10 | Brand | Brand(custitem_brand) under custom subtab on item master record |
| 11 | Item/ Part | Item name from purchase order record |
| 12 | Description | Description(purchasedescription) from item master record |
| 13 | Unit | Unit from line level of purchase order |
| 14 | Receipt Date | Date from item receipt(Will show separate lines if one PO has multiple item receipts) |
| 15 | Cost | Formula field: Qty * rate from item receipt |
| 16 | Quantity | Quantity from line level of item receipt |
| 17 | Age | Calculate date difference from item receipt with the date selected in filter |
| 18 | 180 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >= 180 |
| 19 | 270 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >= 270 |
| 20 | 360 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >= 360 |
| 21 | 450 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >= 450 |
| 22 | 540 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >= 540 |
| 23 | More than 540 Days | Calculate amount(Qty*Rate) from item receipt based on date difference from item receipt with the date selected >540 |
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.
- 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 if a custom record to track the requests
- If the file size is greater than 10Mb & the total email size is less than 19Mb, the download process will take a long time. So that 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. According to the limitations of Netsuite, the total message size (including attachments) in the email must be 20MB or less.
- If the file size is greater than 19Mb, the download will take a long time, and all of the results will not load in a single file and email.
To overcome this, 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 to 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 splitted file download.
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.
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.