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
Deliverables
This requirement can be achieved by creating a new custom report page using transactionsaved searches. The searches will retrieve data from Account balances depending on the chart
of accounts that are chosen to appear in the report. The Sale type defined in the system will
be grouped under the Report section and used for the project.
Account Mapping Set up
Before proceeding with creating the custom report page,
• Need to set up a custom list for mapping the data accounts and determining their
display in each section of the search results.
• To accomplish this, will need to create a custom list that allows the selection of the
corresponding report mapping field in the chart of accounts record. By configuring the
report mapping field value for each chart of accounts record, to display the
corresponding values in the report.
• In addition, will need to create a custom field in the chart of accounts to facilitate the
configuration of the report mapping field value.
The saved search will include the following columns:
• Report Mapping field
• Account number
• Account Name
• Sale Type
• Item Category
• GABLE Amount (based on the “com conso short name” field)
• Non-GABLE amount
• Total
These columns will be derived from the GL Impact line of transactions.
For the BF (Beginning Balance) section, need to consider transactions with dates prior to the
selected “From” date.
For the Actual section, will include transactions with dates falling within the selected “From”
and “To” dates.
In the report, all transactions and amounts will be displayed in the foreign currency.
Custom Report Page
• The page will be having a HTML UI for showing the saved search results as shown
below.
• The Statement of Profit (Loss) report page can be accessed through the following
navigation path: Reports > Custom Reports > Statement of Profit (Loss).
The header section of the custom report page will include details on the following key
metrics: Profit Loss before Share and before Tax, Net Profit, Margin Sales, Mix Sales,
and Total Sales.
We will split the entire report into 3 pages,
• The custom report page will consist of three separate tabs: Sales and Services Report,
Cost of Sales and Services, and Expenses.
• Upon clicking the respective tabs, the custom report will be generated based on the
selected sheet.
The results displayed within the reports will be based on Year-to-Date (YTD) information.
Additionally, there will be a download option available, allowing users to download the
consolidated report in accordance with the provided reference Excel sheet.
By default, the page will present YTD information as of the date when the report is viewed.
Download Button:
• When you click the export/download 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
splited 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 to the NetSuite and by navigating to the custom record entry with the link
from the email.
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.
Assumption
• The saved searches will include the following columns: Report Mapping field, Account
number, Account Name, Sale Type, Item Category, GABLE Amount (based on the “com
conso short name” field), Non-GABLE amount, and Total. The search columns will be
derived from the GL Impact line of transactions, and the currency for the report
amount columns will be considered.
• All sales type sections will include the corresponding sale type accounts with all item
categories except Warranty and Maintenance. The Warranty and Maintenance
categories will be included in the MA sales type section.
• The New Sales Order section will display all sales order amounts within the date range,
grouped by Sales Type.
• The transactions displayed in the search results will be based on the project creation
date. All transaction types and details, not just sales orders, will be considered. The
search results will be based on both the “com conso” and account base values.
• The “APPROVED DATE & TIME” field in the Sales Order record will be verified to ensure
accurate search results.
• All transactions considered in the search results are directly linked to the project
record. This assumption allows us to fetch the search results based on the project
association. In case transactions are not directly linked to the project record, we will
rely on transaction dates to determine their inclusion in the report.
• Memorized transactions will not be considered in this search.
• The left side of the sheet will be frozen, allowing the right side to be moved.
• No filters will be provided for the custom page.
• The calculations provided in the Excel sheet for reference are correct.
Questions
• Our analysis reveals that the Opening Balance (G-Able) only enables certain sales
order records. Can you please verify whether the Opening Balance (G-Able) is
enabled for all transaction records?
• According to our understanding, the search results should display transactions based
on the project creation date. We consider all types and details of transactions, not
just sales orders. It is important to verify that all transactions are directly associated
with the project record to ensure accuracy. The search results are determined by
both the “comconso” and account base values.
• During our investigation of sales order records in the production account, we
discovered that some orders do not have values recorded in the “APPROVED DATE &
TIME” field. To ensure precise search results, we confirm that all transactions are
directly linked to the project record and that our search encompasses all transaction
types and details, not solely sales orders.
• By using the project creation date as the reference for the report date, the
transaction date will not affect the results.
• Do we need to include an additional column in the search results to display wither
the Sales Type or Item Category null value?
Risks
• If we solely rely on the Project Created date for the search results, it may affect the
accuracy of the data. It is crucial to verify that all transactions are directly linked to the
project record to ensure comprehensive and reliable results.
• 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, it must remove these files from the file
cabinet in the future to free up storage.
• There will be no filters available in the custom page. Can be included as per the user
suggestions.
• There will be no sorting inside the custom report page
• No pagination
• The data will be shown in three different sheets as shown inside the mock-up.
• The loading time of data may increase if the search result contains a large amount of
data.
Estimated Time
135*hrs