9.08. Inventory Reports

Reports available for stocks can be found in the Material Movements menu. All reports can be edited in order to comply with the organization's requirements. Details in Reporting.

Storage Balance Report by Warehouse

The Storage Balance Report and Storage Balance Report on Warehouses reports display stock movements, initial stock and final stock, both in quantity and value for any given time period. The lines displayed by the Storage Balance Report on Warehouse are grouped and totaled by warehouse.

Starting with v16.02 the Storage Balance Report is no longer available, leaving only the Storage Balance Report by Warehouse report which incorporates both reports functionalities from previous versions.

Report parameters:

• Organization, Warehouse, Product, etc. - selection filters for the information displayed in the report;

• Accounting Schema, Costing Method - determines the source and calculation method for the cost information displayed in the report;

• Date (beginning date - ending date) - stock movements, the beginning and respectively, the ending stock will be determined according to the selected dates (by default the report will display stock movements for the current date);

• Delete empty lines - the report will not display lines for which all stock values are 0;

• With Movement on In/Out columns

  • • checked - movements are allocated as quantity out;

    • not checked - movements are allocated as quantity in.

Report columns:

• Beginning Movement and Cost Qty - the initial stock quantity;

• Movement and Cost Qty in - quantity entered into stock;

• Movement and Cost Qty out - quantity that exited stock;

• Ending Movement and Cost Qty - the final stock quantity;

• Movement quantities are taken from transactions, while cost quantities are taken from the costs.

• Movement and cost amounts are calculated by multiplying quantities and corresponding costs.

Note: If all documents have been posted correctly the two quantities should be equal.

Product List

The Product List report displays a list of all products within the system. For every product, the report will display the name, category, unit of measure and description.

To limit the products displayed in the report the following selection filters can be used:

• Product Category, Subcategory - only products belonging to this category will be displayed

• Product Type - only product of this type will be displayed (item, service, expense or resource)

• Active - if selected only products marked as active will be displayed. If not, the report will display products not marked as Active.

Product List - UnSold

The report displays products that have not been sold for a number of days. To determine the days without sale, the report checks product shipments from warehouses where the current product stock is above 0. The report follows the following rules:

• displays products for which the days without sale is greater than the Days No parameter;

• displays products where the current stock is above 0;

• displays products that are on locators with the Available to Promise checkbox;

• voided and reversed shipments are not used to determine the days without sale;

Report parameters:

• Organization - only stock movements from warehouses belonging to this organization are checked;

• Warehouse - only stock movements from this warehouse are checked;

• Locator - only stock movements from this locator are checked;

• Product Category, Attribute Set, Product - used to select the products displayed in the report;

• Days No - the report will display products for which the days without sale is greater than the value entered here;

• Internal Use Inventory - if checked internal inventory use will also be taken into consideration when determining the last movement date;

• Stock Movements - if checked, inventory move document will also be taken into consideration when determining the last movement date;

• Consolidate to product - the number of days without movement will be done at organization level instead of warehouse level;

Report columns:

• Days No = days without stock movements, including days without stock, calculated starting the last date when a shipment was made;

• Days out of Stock = number of days for which the product had no stock;

• Days without sale = Days No - Days out of Stock.

Document Reports

The Production Report, Internal Use Inventory Report, Internal Inventory Use/Receipt with Costs, Inventory Movements Report reports display a list of details from their respective documents along with cost information taken from the accounting schema.

Report parameters:

• Include Lines without cost - if not checked the report will only display document details for which the product cost is not 0;

In order to select which internal inventory documents to display when running the Internal Use/Receipt with Cost Price report, the following checkboxes need to be edited:

• for internal material receipt: Production = checked and Internal Use Inventory = not checked;

• for internal material use: Production = not checked and Internal Use Inventory = checked.

Confirmation Reports

The Open Confirmations Report and Open Confirmations Report - Detail reports display confirmations not yet completed for material receipts and shipments. The first report contains a line for each confirmation document. The detailed report contains a line for each confirmation line.

Report parameters:

• Warehouse, Confirmation Type, Business Partner - selection filters for the confirmations displayed

• Order - only confirmations for a shipment or receipt generated based on this order will be displayed.

Inventory Aging

The Inventory Report - Ageing Intervals and Inventory Report - Ageing Intervals (Current) reports display storage quantities and values grouped by age intervals, relative to a date. The history report allows you change the report date. Starting with v16.02 the only remaining ageing report will be the Inventory Report - Ageing Intervals as it covers both previous reports functionalities.

Report parameters:

• Organization, Warehouse, Product, etc. - selection filters for the information displayed in the report;

• Date - date used to determine the intervals (e.g. 30-60 60 to 30 days ago);

Report columns:

• Qty, Qty 0-30, Qty 30-60 etc. - total stock quantity registered within each interval;

• Amt, Amt 0-30 etc. - qty* product cost calculated for each interval.

Inventory Report - Quantity at Date

The Inventory Report - Quantity at Date report displays product stock quantities for any given date.

Report parameters:

• Organization, Warehouse, Product, etc. - selection filters for the information displayed in the report;

• Date - select the date for which the stock quantity is displayed;

• Detailed Information - if checked the report will display attribute set instance stock quantities;

• Delete empty lines - if checked the report will only display lines for which the available qty is not equal to 0;

• Entry Document Detailing - if checked, the report will display the Client/Warehouse Stock Doc/Date columns;

• Properties section - allows you to filter report information based on product attribute set information.

Report columns:

• On Hand Qty = physical stock calculated at the specified date (by default the current date).

• Client/Warehouse Stock Doc/Date - displays the date/document corresponding to the first product entry transaction within the client/warehouse;

  • • Client Sock Doc/Date does not take into account product entries generated by internal inventory movements;

    • if Detailed Information is checked, the last entry corresponding to each stock instance will be displayed;

    • Limitation: if Detailed Info is checked, for instances corresponding to custody warehouses, the Client Stoc Date/Doc the one corresponding to the internal receipt used to update the custody stock;

Physical Q/V Storage Report

The Physical Q/V Storage Report, Physical Q/V Storage Report Batch/Lot, Physical Q/V Storage Report PMP reports display for products, storage quantities and values for any given date. The Batch/Lot report displays storage information for attribute set instances. Quantities and values displayed by the Physical Q/V Storage Report and the Physical Q/V Storage Report Batch/Lot are taken from the accounting schema. Quantities and values displayed by the Physical Q/V Storage Report PMP are taken from VAC cost transactions. The reports take into consideration landed costs. In order to obtain accurate reports it is necessary to post all the documents that generated stock movements.

Starting with v16.02 all report variations have been merged into Physical Q/V Storage at Date Report. The report displays data grouped by product or detailed by attribute set. The amounts are determined based on the the costing method applicable to each product, including VAC. Additionally, the stock entry date and corresponding document is displayed at both organization and warehouse level.

Starting with v16.06, the report will also display stock information for inactivated products (without the Active checkbox).

Report parameters:

• Group by Purchase Price - if checked the report lines will be split according to purchase price;

• Delete empty lines - if checked, the report lines with all quantities 0 will not be displayed;

• Date - date for which stock quantities and values are calculated (current date by default).

• Entry Document Detailing - if checked, the report will display the Client/Warehouse Stock Doc/Date columns;

Report columns:

• Qty - stock quantity;

• Price - product unit entry price (unit cost);

• Amt - quantity * unit cost;

• Scriptic Amount

• PO Price

• Active/Stock - accounting element used for the corresponding stock transaction;

• Client/Warehouse Stock Doc/Date - displays the date/document corresponding to the first product entry transaction within the client/warehouse;

  • Client Sock Doc/Date does not take into account product entries generated by internal inventory movements;

    • if Detailed Information is checked, the last entry corresponding to each stock instance will be displayed;

  • Limitation: if Detailed Info is checked, for instances corresponding to custody warehouses, the Client Stoc Date/Doc the one corresponding to the internal receipt used to update the custody stock;

Current Storage Report

The Current Storage Report report displays the on hand, free on hand, available, reserved and in progress quantities for product instances, including lot and serial number information, grouped and totaled by product.

• groups product info without instance attributes;

• the report will display "In progress" quantities for products with attribute sets;

• the report will not display lines where the OnHand Quantity = Reserved Quantity = Ordered Quantity=0 (empty lines);

The Current Storage Detail - no 0 report displays the on hand, free on hand, available, reserved and in progress quantities for product instances, including lot and serial number information, grouped and totaled by product.

Report parameters:

• Warehouse, Warehouse Key, Locator - search filters for the warehouses displayed in the report;

  • Product, Product Category, Lot etc. - search filters for the products displayed in the report;

  • Has On Hand Quantity - determines whether lines with On Hand Qty = 0 will be shown;

Report columns:

• On Hand Qty = product stock quantity calculated at the report date (current date by default);

• Qty Free on hand = On Hand Qty - Quantity Reserved - Qty in Progress;

  • offers an indication for the stock quantity that can be used or delivered without affecting current transactions;

  • Quantity Reserved = total quantity on customer orders not yet delivered (excluding quantities on shipments not yet completed, which add up to Qty in Progress);

  • Qty Available = On Hand Qty - Quantity Reserved;

  • Ordered Quantity = total quantity on purchase orders not yet received;

  • Qty in Progress = total quantities on documents that can influence stock(inventory transfer, shipments) and can generate confirmations, with the following document status: "draft", "in progress", "invalid", "blocked credit limit".

Storage in Progress Report

The Storage in Progress Report report displays details from documents that can generate confirmations and have one of the following document statuses: "Draft", "In Progress", "Invalid", "Blocked Credit Limit".

Report parameters:

• Document Type name - select the type of documents that will be displayed in the report;

• Movement Date, Warehouse, Product, Product Category - selection filters for the document details displayed in the report.

Stock Transaction Reports

The Storage Transactions Report - Summary report displays a list of transactions that represent stock movements. For every product moved, the report displays the quantity, warehouse, movement date and type.

The Storage Transactions Report - Amounts report displays a list of transactions that represent stock movements. For every product moved, the report displays the quantity, warehouse, current vendor and purchasing prices. The report contains, among others, the following columns:

• Vendor Invoice Price - product taken from the last vendor invoice

• List price - purchasing list price (Purchasing tab, Product window);

• PO price - purchasing standard price (Purchasing tab, Product window);

The Storage Transactions Report - Business Partner report displays a list of document details which represent stock movements. The document details displayed in the report can be filtered by organization, business partner, product, date, project and resource. The report may also contain details from documents in "draft" state.

The In-Out Transaction and In-Out Transaction Invoice reports display all the associated transactions for an inventory document and respectively, an invoice.

The Material Reference Report report displays all the associated stock transactions for a document of the following types: order, invoice or receipt.

The Storage vs Transaction report verifies if all stock transactions have been correctly registered in storage tables. Storage tables are used to generate stock reports, without having to go through the time consuming process of checking transaction records. If all transactions have been registered correctly the report will not display any lines.

The Storage Transactions Report - Detail and Storage Movements Report - Detail report display a list of transactions that can influence stock. The transactions are grouped by product and ordered by date. The bold lines display the initial and ending quantities for the product stock, according to the selected date interval.

• Report parameters:

  • Delete Empty Lines - the report will not display lines for which all quantities are 0;

  • Exclude no transaction records -

• Report columns:

  • Qty In or Qty Out - stock quantity moved;

  • Doc No. - document that generated the transaction.

Difference quantity - value operational vs accounting.

It is a process that returns the list of items for witch there are quantitative or value differences between the storage balance operational report and the analytical balance detailed on products accounting report, as well as the list of products that come from documents that have not been posted into accounting and influence stocks.

The report can be run with the following parameters:

• • organization - a single organization or a group of organizations can be designated(if a global verification is desired);

  • warehouse - a single warehouse can be designated, in witch case the verification is carried out only for that warehouse;

  • time period - designate the time period for which the verification is carried out, this being done on the last day of that period;

  • accounting schema - it is implicitly filled with the basic accounting schema set out in the tenant definition; if there are exceptions at organization level, then it is recommended to select the correct base schema for the organization selected at the above parameter; in case of such exceptions the report will not run globally because the values will be distorted!

  • aggregation - the accounting aggregation shall be chosen from the base accounting schema that contains at least the following accounting parameters: organization (default), account (default), product, cell;

  • account - an accounting account may be designated, in which case the verification is made only for that account; if not designated, the account is determined from the accounting definition of the product:

    • from the accounting schema selected above,

    • from the Product Asset field, if the warehouse on which the verification is made has nothing filled on the field with the same name (without cross-account),

    • or Product Asset 1 if it matches the Product Asset entry from the warehouse on which the verification is made (cross-account),

    • or Product Asset 2 if it matches the Product Asset entry from the warehouse on which the verification is made (cross-account).

  • product category - a category of products can be indicated, in which case the verification is only performed for products in hat category;

  • product subcategory - behaves the same as product category;

  • product - an product can be indicated, in which case the verification is only performed for that product.

Differences determination algorithm

• • determine documents that are not posted or that have a posting error (only documents affecting the stock):

    • MatchPO ,with the "cost Update" checked,

    • MatchINV,

    • Receipt,

    • Shipment,

    • Invoice,

    • Movement,

    • Physical Inventory ,

    • Issue to Project,

    • Production.

  • if exists, then:

    • the products in the contents of these documents are recorded to be excepted from verification (clearly there will be differences) and are moved into a temporary table (A) with the indication "posting error" to be presented in the final report;

• the list of stockable products is determined according to the category, subcategory, product, account filters, if specified;

• the articles specified in paragraph 2 will be removed from this list.

• the list of verified warehouses (clear or according to the selected organization) is determined.

• for each product is calculated:

  • the quantitative and value stock at the end of the specified period, on each warehouse in accordance with the "Warehouses storage balance" report procedure;

  • the stock account for each pair [product + warehouse] is determined;

  • final value balance (with the same filters) on the indicated aggregation directly from Fact_Acct_Balance, to which the RapFin rules for determining D/A balances apply, from the account specified at point b and for all cells in that warehouse;

  • final quantitative balance (with the same filters) on the indicated aggregation directly from Fact_Acct_Balance, direct summation from the specified account at point b and for all cells in that warehouse.

  • compares the values between them and the quantities between them:

    • if they are equal, proceeds to the next product;

    • if there are differences, all the data obtained for that product is recorded into a temporary table (B);

  • the report with the data in the temporary tables (A - products from documents that were not posted into accounting) and (B - products with operational and accounting differences) will be listed.

For the repair of "A" category products, the corresponding documents should be posted in chronological order.

For the repair of "B" category products, it is necessary to analyze in detail what is the cause that has led to these differences and address it. In the extreme, it is possible to register a detailed GL journal on organization, account, product and cell, with the indication of differences, so the equality between operational and accounting may be reduced.