Product Monthly Utilization Report, Asset Utilization Report, and Asset Info View Report
Reference No: R2-7777
Version No: 06.00.04-00
Details
Support for the following three new custom reports has been provided to display the utilization and statistics of a Product on monthly basis, the utilization and statistics of Assets along with the information of the Assets from the Reports module.
The details are explained in the sample workflow given below:
Product Monthly Utilization Report
Prerequisites
Perform the following the steps:
Select the Maintenance module.
Edit the existing Serial Item [JBL DUAT] by selecting the Statistics check box and tagging the Report [AUX] and Department [AUDIO], as shown in Figure 1.0.
Figure 1.0 Statistics check box selected in the Serial Item [JBL DUAT]
Click OK.
Save and Exit the Maintenance module.
Select the Configuration module > Configure Reports button. The Report Assistant Configuration window is displayed. Configure the Report Definition as follows:
REP_PRODUCTMONTHLYUTILISATION with the Category PRODUCT
REP_ASSETUTILISATIONVIEW with the Category ASSET
REP_ASSETINFOVIEW with the Category ASSET
Exit the Configuration module.
Relaunch the R2 application.
Sample Workflow
Select the Account module > tag the company [JBL].
Create a Rental Reservation Order [ORD-80] for the duration of 10 days with the Start Date [10-July-2019] and End Date [20-July-2019].
Select the Dates tab > Edit Dates button.
Enter the Load In Date [11-July-2019] and Pickup Date [19-July-2019], as shown in Figure 2.0.
Search and add the Serial Item [JBL DUAT] with the Qty [1] and Unit Price [100.00], as shown in Figure 2.0.
Figure 2.0 Rental Reservation Order [ORD-80]
Fill and Ship the first Serial Item [JBL DUAT] with the Asset [JBL DUAT#01].
Search and add another Qty of Serial Item [JBL DUAT] with the Unit Price [100.00].
Perform a right-click > Assign > Sub-Rental > User Assign > Use Existing Purchase Orders > OK.
Tag to the existing Purchase Order [ID323001] and release the Purchase Order [ID323001]. The Item [JBL DUAT] Status is displayed as S-Assign, as shown in Figure 2.1.
Figure 2.1 Serial Item [JBL DUAT] in S-Assign Status
Click the Done icon to Invoice the Order [ORD-80]. The Invoice [INV-15] is generated.
Select the Account module > tag the company [JBL].
Click the WorkOrder icon and create a Service Work Order [SWO-17].
Select the Equipment tab and tag the Asset ID / Product ID [JBL DUAT#03], as shown in Figure 2.2.
Figure 2.2 Service Work Order [SOW-17]
In the Information tab, tag the Start Date [15-July-2019].
Save and exit the Service Work Order [SWO-17].
Connect to the R2 schema and execute the below Statistics program by entering the Serial Item [JBL DUAT], as shown below:
Program format
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(ProductID, StartDateCount , EndDateCount);
Parameter Details
The Parameter details are as follows:
Product ID is the ID of the Item or the Product
StartDateCount And EndDateCount is the Statistics Period. By default it will run for past 90 days and future 90 days with respect to the current day.
Sample Execution
The Sample Execution can be executed for a Single Product or All the Products, as follows:
Single Product
Run the below Asset Utilization Statistics program if you want to execute for a single product with a certain duration.
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(‘ProductID’, 60,60);
where the Product ID is the ID of the Item or the Product and 60 is the days with respect to the current day.
All Products
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics;
By default, the above program is used to execute all the products for past 90 days and future 90 days with respect to the current day.
All Products with defined duration
Run the below program to execute all the products for certain duration with respect to the current day.
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(null, 60,60);
NOTE: Set the Windows Scheduler to run the batch file comprising the above mentioned commands as required on daily basis, when no users are logged in.
Select the Reports module. The Reports Generator window is displayed.
Select the File menu > Custom Reports option. The Report Assistant - Reports Menu window is displayed.
Select the Category [PRODUCT] with the Reports [REP_PRODUCTMONTHLYUTILISATION].
Select the Data To Grid check box and click Run.
The REP_PRODUCTMONTHLYUTILISATION Filter with the chosen filters DEPARTMENT and SKU is displayed, as shown in Figure 2.3.
Choose the filters for the DEPARTMENT [AUDIO] and SKU [JBL DUAT], as shown in Figure 2.3.
Figure 2.3 Report Assistant - Reports Menu window > Run > REP_PRODUCTMONTHLYUTILISATION Filter
Click OK.
The Report [REP_PRODUCTMONTHLYUTILISATION] gets printed to the Grid, as shown in Figure 2.4.
Figure 2.4 REP_PRODUCTMONTHLYUTILISATION
Click Excel button to export the Report [REP_PRODUCTMONTHLYUTILISATION], as shown in Figure 2.5.
Figure 2.5 Report [REP_PRODUCTMONTHLYUTILISATION]
The Report [REP_PRODUCTMONTHLYUTILISATION] comprises of the following fields, as given in Table 1.0.
Table 1.0 REP_PRODUCTMONTHLYUTILISATION Column Details
Column Name | Description |
YEAR | This column displays the Year. |
OUTDAYS | This column displays the total number of Out Days from the Load In Date to the Pickup Date. |
REVENUE | This column displays the total Revenue generated for the Item [JBL DUAT]. NOTE: The value in the Revenue column is displayed only when the Order is Invoiced. |
CHARGEDAYS | This column displays the Item's total Charge Days from the Invoice. NOTE: The value in the Charge Days column is displayed only when the Order is Invoiced. |
FIRSTDAYOFMONTH | This column displays the first day of the month. |
TRANSITDAYS | This column displays the Item's total number of transit days considered from the Order line's Ship Date to Load In Date and Pickup Date to Return Date. |
MONTH | This column displays the name of the Month. |
SUBRENTOUTDAY | This column displays the Item's total number of Sub Rent Out days considered from the Order line's Load In Date to Pickup Date for the Item is Sub-Rent Status. |
MAINTENANCEDAY | This column displays the total number of days in Service considered from Scheduled Date and End Date from the Service Work Order. |
SUBRENTREVENUE | This column displays the Total Sub Rent Revenue generated for the Item [JBL DUAT] in Status [Sub-Rent]. NOTE: The value in the Revenue column is displayed only when the Order is Invoiced. |
ESTIMATEDOUTQTY | This column displays the Cumulative Stock for the month in which the report is generated. |
SUBRENTCHARGEDAY | This column displays the Total Charge Days for the Item [JBL DUAT] from the Invoice for Sub Rent Status. |
SUBRENTTRANSITDAY | This column displays the total number of Sub Rent Transit days considered from the Order line's Ship Date to Load In Date and Pickup Date to Return Date. |
SKU | This column displays the Name of the Product ID. |
DEPARTMENT | This column displays the Department ID [AUDIO] from Item. |
REPORTGROUP | This column displays the Report Group [AUX] from Item. |
SITENAME | This column displays the Site Name [San Francisco]. |
DESCRIPTION | This column displays the Item Description. |
Exit the Report [REP_PRODUCTMONTHLYUTILISATION].
Asset Utilization View Report
Use the above Sample workflow to generate the Report [REP_ASSETUTILISATIONVIEW].
Connect to the R2 schema and execute the below Statistics program by entering the Serial Item [JBL DUAT], as shown below:
Program format
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(ProductID, StartDateCount , EndDateCount);
Parameter Details
The Parameter details are as follows:
Product ID is the ID of the Item or the Product
StartDateCount And EndDateCount is the Statistics Period. By default it will run for past 90 days and future 90 days with respect to the current day.
Sample Execution
The Sample Execution can be executed for a Single Product or All the Products, as follows:
Single Product
Run the below Asset Utilization Statistics program if you want to execute for a single product with a certain duration.
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(‘ProductID’, 60,60);
where the Product ID is the ID of the Item or the Product and 60 is the days with respect to the current day.
All Products
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics;
By default, the above program is used to execute all the products for past 90 days and future 90 days with respect to the current day.
All Products with defined duration
Run the below program to execute all the products for certain duration with respect to the current day.
exec AssetUtilizationStatistics_Pkb.AssetUtilizationStatistics(null, 60,60);
NOTE: Set the Windows Scheduler to run the batch file comprising the above mentioned commands as required on daily basis, when no users are logged in.
Select the Reports module. The Reports Generator window is displayed.
Select the File menu > Custom Reports option. The Report Assistant - Reports Menu window is displayed.
Select the Category [PRODUCT] with the Report [REP_ASSETUTILISATIONVIEW].
Select the Data To Grid check box and click Run.
The REP_ASSETUTILISATIONVIEW Filter with the chosen filters DEPARTMENT and ASSETID is displayed, as shown in Figure 3.0.
Choose the filters for the SKU [JBL DUAT#01] and DEPARTMENT [AUDIO] , as shown in Figure 3.0.
Figure 3.0 Report Assistant - Reports Menu window > Run > REP_ASSETUTILISATIONVIEW Filter
Click OK.
The Report [REP_ASSETUTILISATIONVIEW] gets printed to the Grid, as shown in Figure 3.1.
Figure 3.1 REP_ASSETUTILISATIONVIEW
Click Excel button to export the Report [REP_ASSETUTILISATIONVIEW], as shown in Figure 3.2.
Figure 3.2 Report [REP_ASSETUTILISATIONVIEW]
The Report [REP_ASSETUTILISATIONVIEW] comprises of the following fields, as given in Table 2.0.
Table 2.0 REP_ASSETUTILISATIONVIEWColumn Details
Column Name | Description |
ASSETID | This column displays the Name of the Asset [JBL DUAT#01]. |
ASSETREVENUE | This column displays the Revenue generated for the Asset [JBL DUAT#01]. NOTE: The value in the Revenue column is displayed only when the Order is Invoiced. |
CHARGEDAYS | This column displays the Asset's Charge Days. NOTE: The value in the Charge Days column is displayed only when the Order is Invoiced. |
DEPARTMENT | This column displays the Department ID [AUDIO] for the Item. |
MAINTENANCEDAY | This column displays the total number of days in the Service considered from Scheduled Date to End Date from the Service Work Order. |
OUTDAYS | This column displays the Total number of Out Days considered from the Order line's Load In Date to the Pickup Date. |
PRODUCTDESCRIPTION | This column displays the Item Description. |
PRODUCTID | This column displays the Product ID. |
REPORTGROUP | This column displays the Report Group [AUX] for the Item. |
SITEID | This column displays the Site ID [SANFRANCISCO]. |
SITENAME | This column displays the Site Name [San Francisco]. |
TRANSACTIONDATE | This column displays the days of the selected report duration. |
TRANSITDAYS | This column displays the total number of days in Transit considered from the Order line's Ship Date to Load In Date and Pickup Date to Return Date. |
Exit the Report [REP_ASSETUTILISATIONVIEW].
Asset Info View Report
Prerequisites
Perform the following the steps:
Select the Maintenance module.
Edit the existing Serial Item's Asset [JBL DUAT#01] by providing the MFG Serial# [JBLDUAT#01], Shelf [JBLDUATSHELF#1], and Bin Number [JBLDUATBIN#1], as shown in Figure 4.0.
Figure 4.0 Edit the Serial Item's Asset [JBL DUAT#01] by providing the MFG Serial# [JBLDUAT#01], Shelf [JBLDUATSHELF#1], and Bin Number [JBLDUATBIN#1]
Click OK.
Save and Exit the Maintenance module.
Sample Workflow
Select the Reports module. The Reports Generator window is displayed.
Select the File menu > Custom Reports option. The Report Assistant - Reports Menu window is displayed.
Select the Category [PRODUCT] with the Report [REP_ASSETINFOVIEW].
Select the Data To Grid check box and click Run.
The REP_ASSETINFOVIEW Filter with the chosen filters DEPARTMENT and ASSETID is displayed, as shown in Figure 5.0.
Choose the filters for the SKU [JBL DUAT#01] and DEPARTMENT [AUDIO] , as shown in Figure 5.0.
Figure 5.0 Report Assistant - Reports Menu window > Run > REP_ASSETUTILISATIONVIEW Filter
Click OK.
The Report [REP_ASSETINFOVIEW] gets printed to the Grid, as shown in Figure 5.1.
Figure 5.1 REP_ASSETINFOVIEW
Click Excel button to export the Report [REP_ASSETINFOVIEW], as shown in Figure 5.2.
Figure 5.2 Report [REP_ASSETINFOVIEW]
The Report [REP_ASSETUTILISATIONVIEW] comprises of the following fields, as given in Table 3.0.
Table 3.0 REP_ASSETINFOVIEW Column Details
Column Name | Description |
ASSETBINNUMBER | This column displays the Asset's Bin Number [JBLDUATBIN#1]. |
ASSETID | This column displays the Name of the Asset ID [JBL DUAT#01]. |
ASSETMFGSERIALNUM | This column displays the Asset's Manufacturing Serial Number [JBLDUAT#01]. |
ASSETSHELFLOCATION | This column displays the Asset's Shelf Location Number [JBLDUATSHELF]. |
DEPARTMENT | This column displays the Department ID [AUDIO] for the Item. |
PRODUCTDESCRIPTION | This column displays the Item Description. |
PRODUCTID | This column displays the Product ID. |
REPORTGROUP | This column displays the Report Group [AUX] for the Item. |
SITEID | This column displays the Site ID [SANFRANCISCO]. |
SITENAME | This column displays the Site Name [San Francisco]. |
Exit the Report [REP_ASSETINFOVIEW].
Points to Note:
The above workflow is applicable for the Non-Serial Items also.
The Quote Order is not considered for Utilization.
If the Load In date is blank, then the Ship date is considered, and if the Pickup date is blank, then the Return date is considered.
On the same day, in the Order line, the Transit day and Out day is applied, then any one of the days that is greater than the other [either the Transit day or the Out day] is considered. If both Transit day and Out day are in the same duration, then that day is considered as Out.If the line status is Assign Later [Transfer, Purchase, Sub Rent], Not available, and Mark as Deleted the Order lines Transit and Out day is not considered in Rep_ProductMonthlyUtilisationView Utilization report, if the Invoice is generated for these lines, then the Revenue and Charge Days will be considered.
Estimate Work Order: The Estimate Work Order is not considered for Maintenance day.
Billing Order: The Product Revenue is considered for the Billing Order. If any new lines are added to the Billing Order but not synchronized then the Revenue, Transit days, and Out days will be considered from the same Order.
Operational Order: The Transit day and Out days are considered for the Operational Order. If any new lines are added to the Operational Order but not synchronized then the Revenue, Transit days, and Out days will be considered from the same Order.