Customization DS1261
Historical Stock Status with Lot Numbers
Acme, Inc uses Microsoft Dynamics GP. Dynamics GP provides a Historical Stock Status Report by Item Number, but Acme needs Lot Number level detail.
A new Historical Stock Status-Lot Number report (a Report Writer report) will be created within Dynamics GP.
The details of the report can be found in the Design Features section.
Historical Lot Report
Navigation>>Reports>>Inventory>>Historical Inventory Reports
This window will be used to enter restriction criteria and print the report.
|
Field |
Function |
|
Report Selection |
Currently there is only one option:
|
|
As of Date |
The user will enter the “As of Date”. The report will pull through both Quantity On Hand and Standard Cost based on the “As of Date”. This is a required field and will default to the current user date. |
|
Item Number From |
The user will enter an Item Number or select it from the lookup to start the Item Number range. |
|
Item Number To |
The user will enter an Item Number or select it from the lookup to end the Item Number range. |
|
Site From |
The user will enter a Site or select it from the lookup to start the Site Range. |
|
Site To |
The user will enter a Site or select it from the lookup to end the Site Range. |
|
Sort By |
The user will select to sort the report by Item Number or Site ID. This selection only changes the Sort Order of the Historical Inventory Valuation Report. |
|
Print Button |
The user will select the PRINT button to print the selected report. |
|
Done Button |
The user will select the DONE button to exit the window. |
The user does not need to enter an Item Number or Site Range. Selecting the PRINT button with these fields blank will print the report for all Item Numbers in all Sites. Item Numbers with ZERO on hand quantity will NOT print. Items with positive and/or negative quantities will print.
When the PRINT button is selected, the user will be prompted to either print the report to:
- Screen
- Printer
- File
The report fields are listed below:
- Date
- Item Number
- Item Description
- Site
- Lot Number
- Date Received*
- Quantity on Hand
- Days Aged*
- Manufacture Date
- Expiration Date
- Standard Cost
- Extended Cost (Standard Cost x Quantity on Hand)
The Total Inventory Value will be printed at the bottom of the last page.
PLEASE NOTE:
- The column Date Received will display the most recent Date Received prior to the As of Date as there is the potential within Dynamics GP for multiple Receipt Dates per Lot Number
- The column Days Aged will equal the (As of Date – Date Received)
Calculations
There is no user interface for this part of the enhancement. SQL queries and/or DEX calculation routines will be utilized to determine reporting quantities and costs. The basis for the calculations will be the “As of Date” entered in the Historical Inventory Reports window. The enhancement will begin with the current On Hand Quantities for all Items/Sites selected in the range. The enhancement calculation code will then “reverse” all Inventory Transactions which have a Document Date after the “As of Date” entered.
Backdated transactions MUST have their Document Date backdated prior to posting. It is the Document Date that will determine whether a transaction should be included or excluded from the on-hand calculation.
Once the enhancement has determined the accurate on-hand quantities based on the “As of Date,” it will determine the appropriate Historical Standard Cost per Item. The Historical Standard Cost will also be based on the “As of Date.” All historical on-hand quantities of an Item will be valued at the same Standard Cost. For example:
On hand balance of item 123 as of 12/31/2012 is zero.
100 pieces of item 123 received on 01/01/2013 @ standard cost $1.00
101 pieces of item 123 received on 01/15/2013 @ standard cost $2.00
99 pieces of item 123 received on 1/20/2013 @ standard cost $1.25
No quantities have been “sold” or “used”.
If the Historical Inventory Valuation Report is run with an As of Date of:
- 01/31/2018 – 300 pieces on hand @ 1.25 per unit = $375.00 total
- 01/16/2018 – 201 pieces on hand @ 2.00 per unit = $402.00 total
- 01/01/2018 – 100 pieces on hand @ 1.00 per unit = $100.00 total
Sample Report
Assumptions/Requirements
- Multi-Bin is NOT enabled.
- Multi-Currency is NOT enabled.
- Inventory Quantity Types other than On Hand are NOT required to print on either Historical report.
- All Inventory Items with an Item Type of Sales Inventory or Discontinued must have a Periodic Valuation Method. (Items must be set to Standard Cost.)
- Maintain Transaction History must be selected for all Items.
- This report will not necessarily tie to the General Ledger.
If you are interested in this customization or any other project or product, please contact us at: