Proposal summary
JCurve Solutions would like to create new a report to retrieve data for data from the Inventory adjustment document. The details for PO No, Supplier, Reference Bill can track only the case of issue out. And print reports in Excel format.
Requirement
New report ‘Inventory adjustment report’ to retrieve data for data from the Inventory adjustment document.
The details for PO No, Supplier, Reference Bill can track only the case of issue out. In case of receive in, this value cannot show.
Data reference in Inventory adjustment related to the fixed assets will check from the inventory adjustment document that put as reference only. To receive a fixed asset will need to be managed separately and details of custodian need to reference in the inventory adjustment document which will not link to Fixed asset master.
Assumptions:
- This report will retrieve data for data from Inventory adjustment document
- The details for PO No, Supplier, Reference Bill can track only the case of issue out. In case of receive in, this value cannot show
- Data reference in Inventory adjustment related to fixed assets will check from inventory adjustment documents that are put as reference only. To receive to fixed asset will need to manage separately and details of custodian need to reference in inventory adjustment document which will not link to Fixed asset master
- Print report as Excel format
Sample Report:
Report Details – Criteria:
| Criteria | |||
| # | Field | Description | Details |
| 1 | Subsidiary | Select Subsidiary for report | name of Subsidiary |
| 2 | Date from … To … | Input date from to | Input range date to recall report filter from Inventory adjustment document |
| 3 | Adjustment Reason | Select Adjustment reason | Select Adjustment reason |
| 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 adjustment report | Report Name | Fixed |
| 3 | Date from … To … | Show period date | Based on the range date select in the criteria |
| Report Column | |||
| # | Field | Description | Details |
| 1 | Project ID | Project order | Project from Inventory adjustment (cseg_project_order) |
| 2 | Sale Type | Sale Type | Sale type from Inventory adjustment |
| 3 | Customer | Customer | Customer from Inventory adjustment (custbody_end_custome) |
| 4 | Salesman | Sale person | Sale person (custbody_sale_person2) |
| 5 | PO. No | Purchase order no of item | Reference PO no in lot /serial item (custitemnumber_gable_rev_refpo) |
| 6 | Supplier | Supplier of Purchase order | Reference Supplier from Purchase order |
| 7 | Item part | Item | The item from inventory adjustment |
| 8 | Serial/ Lot no | Serial/ Lot no | Lot/ Serial no from Inventory adjustment |
| 9 | Description | Description | Description from inventory adjustment |
| 10 | Item category | Item category | Item category from inventory adjustment |
| 11 | Ref. AP Bill | Reference AP bill | Reference AP bill link with a Purchase order |
| 12 | Request No | Reference document to create inventory adjustment | Reference no from inventory adjustment |
| 13 | Request by | Reference request person | Create by from inventory adjustment |
| 14 | IA Date | Date | Date from inventory adjustment |
| 15 | IA No | Inventory adjustment no | Document no from inventory adjustment |
| 16 | IA Amount | amount | Amount of inventory adjustment (EST. UNIT COST *Adjust QTY) |
| 17 | Com Conso | Com Conso | Com conso field from inventory adjustment |
| 18 | Custodian | Employee who own asset | Reference Custodian in case of issue to be fixed asset(custcol_ref_fa_custodiant) |
| 19 | Department | Department | Department field from inventory adjustment |
| 20 | Adjustment Reason | Adjustment Reason | Adjustment reason from inventory adjustment |
| 21 | GL account | Adjustment account | Adjustment account from inventory adjustment and need to mention in case it has record in Debit (add QTY) or Credit (reduce QTY) |
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 the Inventory adjustment document. The details for PO No, Supplier, Reference Bill can track only the inventory number that has the related reference PO. This field will be blank if the reference PO is not associated with the inventory number.
The navigation to the custom report can be added from the classic center. The user can navigate to Reports -> Custom Reports -> Inventory adjustment Report. Then upon clicking the “Inventory adjustment 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., LTD, the Adjustment Reason as All,From date as 1st Jan of the current year, and the To as today.
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 | Date from … To … | Input range date to filter the report based on Date(trandate) on Inventory adjustment document |
| 3 | Adjustment Reason | Adjustment reason(custbody_adjustmentreason) on Inventory Adjustment record |
| Backend Criteria for Report | ||
| 1 | Type | Inventory adjustment |
| Report Details | ||
| # | Field | Field Name from NS records |
| 1 | Company Name | Subsidiary(subsidiary) name selected in filter |
| 2 | Inventory adjustment report | Fixed |
| 3 | Date from … To … | Date(trandate) on Inventory adjustment document |
| Please see the table below for the report columns for the detailed ‘Inventory Adjustment’ report. | ||
| Report Columns | ||
| # | Field | Field Name from NS records |
| 1 | Project ID | Project Order(cseg_project_order) from Inventory adjustment |
| 2 | Sale Type | Sale type(class) from Inventory adjustment |
| 3 | Customer | Reference Customer(custbody_end_customer) from Inventory adjustment |
| 4 | Salesman | Saleman(custbody_sale_person2) from Inventory adjustment |
| 5 | PO. No | Reference PO no in lot /serial number record (custitemnumber_gable_rev_refpo) |
| 6 | Supplier | Reference Supplier from Purchase order(third-level join field) [Inventory Number search + Inventory Adj search = Common field is Inventory number] |
| 7 | Item part | Items from inventory adjustment |
| 8 | Serial / Lot no | Lot / Serial no from inventory detail on Inventory adjustment |
| 9 | Description | Item Description from inventory adjustment |
| 10 | Item category | Item category from inventory adjustment |
| 11 | Ref. AP Bill | Reference AP bill link with a Purchase order(third-level join field) [Inventory Number search + Inventory Adj search = Common field is Inventory number] |
| 12 | Request No | Reference no(custbody_refernece_no) from inventory adjustment |
| 13 | Request by | Create by(custbody_create_by) from inventory adjustment |
| 14 | IA Date | Date(trandate) on Inventory adjustment document |
| 15 | IA No | Document no(tranid) from inventory adjustment |
| 16 | IA Amount | Amount of inventory adjustment (EST. UNIT COST *Adjust QTY) |
| 17 | Com Conso | Com conso line field from inventory adjustment |
| 18 | Custodian | Reference Custodian in case of issue to be fixed asset(custcol_ref_fa_custodiant) – Line field |
| 19 | Department | Deparment(department) field from inventory adjustment |
| 20 | Adjustment Reason | Adjustment reason(custbody_adjustmentreason) from inventory adjustment |
| 21 | GL account | Adjustment account(account) from inventory adjustment and need to mention in case it has record in Debit (add QTY) or Credit (reduce QTY) |
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.
- In the Adjustment Reason filter, it will display all values under the Adjustment Reason field in the system as standard.
- When you select the Adjustment Reason filter, it will display a list of related inventory adjustment documents.
- The supplier and AP Bill will be displayed in the search if the serial/lot number has a reference Purchase order. If not, these columns will be displayed as blank.
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.