User Guide
1. General Information
2. Starting Up
3. Connecting data
4. Demand and Sales Forecasting
5. Inventory Planning
6. Reference
1. General Information
2. Starting Up
3. Connecting data
4. Demand and Sales Forecasting
5. Inventory Planning
6. Reference
Database connection allows you to export Streamline's outcomes back to your database. In this article, we describe how you can:
Database connection allows you to export current purchase and replenishment orders into the database of your system by executing an SQL-query. This query should be given in the Export periodic order tab of the Database connection dialog (see figure below).
There are five marks which you can use in this query. They are substituted by the corresponding data when the query is executed. The marks, corresponding substitution, and the substitution source are given in the table below.
Mark | Substitution | Substitution source |
---|---|---|
:itemcode | Item code or Supplier's item code | Planned orders preview dialog |
:orderqty | Qty | |
:location | Location | |
:dcsource | DC | Inventory planning tab |
:minpoint | Reorder point |
The marks :location
, :dcsource
, and :minpoint
are optional. They should be used in the corresponding cases. For example, if your project contains several DCs, the :dcsource
mark allows you to export and recognize the item supply source: if the result of the substitution is not empty, the line belongs to a replenishment order, otherwise, it is a purchase order line.
Streamline makes the substitutions and executes this query when you click the Create button in the Planned orders preview dialog. This query is executed for every order line that is checked in the mentioned dialog.
The Test section of the tab allows you to test your query using the data you enter into the fields this section contain. These values will substitute the marks in your query. To run the test, click the Execute button.
Streamline allows you to export:
All of these reports can be exported with a single SQL-query. You can also choose which one (or a set) of the reports you need to export.
Streamline allows you to export data rows with the following columns:
This data should go into a table of your database. If you don't have it, create it. Below, we show an example of SQL-query that creates such a table.
CREATE TABLE [dbo].[export_forecast_report]( [item_code] [nvarchar](250) NOT NULL, [location] [nvarchar](250) NULL, [forecast_date] [smalldatetime] NULL, [period_start_date] [DATE] NULL, [period_end_date] [DATE] NULL, [demand_forecast] [INT] NULL, [purchase_plan] [INT] NULL, [inventory_projection] [INT] NULL, [projected_revenue] [DECIMAL](18, 3) NULL, ) GO
You can download this query as an SQL-file. In our example, the table has columns for all the data you can export.
After the table has been created, the next step is writing an SQL-query that will export the data into the table. There are a set of marks which you can use in this query to export a particular piece of data. Those marks are substituted by the corresponding data when the query is executed. The marks, corresponding substitution, and the substitution source are given in the table below.
Mark | Substitution | Substitution source |
---|---|---|
:itemcode | Item code | Inventory planning tab |
:location | Location | |
:forecastdate | Current date | OS system |
:startdate | Period start date | Event model |
:enddate | Period end date | |
:forecast | The value of the corresponding cell of the report | Final forecast report |
:purchaseplan | Ordering plan | |
:invprojection | Projected inventory report | |
:projrevenue | Projected revenue report |
Below, we give an SQL-query example that exports data of all the reports.
INSERT INTO export_forecast_report ( [item_code], [location], [forecast_date], [period_start_date], [period_end_date], [demand_forecast], [purchase_plan], [inventory_projection], [projected_revenue]) VALUES ( :itemcode, :location, :forecastdate, :startdate, :enddate, :forecast, :purchaseplan, :invprojection, :projrevenue );
You can download this query as an SQL-file. This query is designed to insert data into the table we have created before.
Then, insert your query into the Export inventory forecast tab (see figure below).
The Execute on exporting planned orders option sets up Streamline to trigger the execution of your query as you click the Create button of the Planned orders preview dialog.
The Test section of the tab allows you to test your query using the data you enter into the fields this section contain. These values will substitute the marks in your query. To run the test, click the Execute button.
After all, click the Save button.
Now, to export your reports:
Streamline allows you to export almost the entire report of the All items filter of the Inventory planning tab into a database. These capabilities don't include exporting the following parts of the report as:
However, all of these you can export using the Export inventory forecast tab. In this section, we show how all the other columns of the report can be exported.
The exported data should get into a table of your database. Thus, it should be created beforehand. Below, we give an example query that creates such a table.
CREATE TABLE [dbo].[export_inventory_report]( [item_code] [nvarchar](250) NOT NULL, [item_description] [nvarchar](250) NULL, [location] [nvarchar](250) NOT NULL, [distribution_center] [INT] NULL, [bom_type] [nvarchar](250) NOT NULL, [supplier] [nvarchar](250) NOT NULL, [model_type] [nvarchar](250) NULL, [inventory_on_hand] [DECIMAL](18, 3) NULL, [on_hand_supply_days] [INT] NULL, [on_hand_supply_month] [INT] NULL, [backorder] [INT] NULL, [in_transition] [DECIMAL](18, 3) NULL, [leadtime] [INT] NULL, [order_cycle] [INT] NULL, [min_lot] [INT] NULL, [max_lot] [INT] NULL, [rounding] [INT] NULL, [service_level] [DECIMAL](18, 3) NULL, [minimum_display_quantity] [INT] NULL, [safety_stock] [nvarchar](250) NULL, [debt_received] [DECIMAL](18, 3) NULL, [debt_accumulated] [DECIMAL](18, 3) NULL, [debt_passed] [INT] NULL, [shelf_life] [INT] NULL, [shelf_discard] [INT] NULL, [purchase_price] [DECIMAL](18, 3) NULL, [gross_margin] [DECIMAL](18, 3) NULL, [turn_earn_index] [DECIMAL](18, 3) NULL, [note] [nvarchar](250) NOT NULL, [order_now_quantity] [DECIMAL](18, 3) NULL, [order_now_net_order_quantity] [DECIMAL](18, 3) NULL, [order_now_excess_order] [DECIMAL](18, 3) NULL, [order_now_purchase_value] [DECIMAL](18, 3) NULL, [order_now_days_of_supply] [INT] NULL, [order_now_margin] [DECIMAL](18, 3) NULL, [next_order_by] [DATE] NULL, [dc_fill_rate] [DECIMAL](18, 3) NULL, [reorder_point] [INT] NULL, [reorder_amount] [INT] NULL, [stockout] [INT] NULL, [overstock] [INT] NULL, [write_offs] [INT] NULL, ) GO
You can also download this query as an SQL-file.
After the table has been created, the next step is writing an SQL-query that will export the data into the table. There are a set of marks which you can use in this query to export a particular piece of data. Those marks are substituted by the corresponding data when the query is executed. The marks, corresponding substitution, and the substitution source are given in the table below.
Mark | Substitution (column) | Substitution source |
---|---|---|
:itemcode | Item code | Table of the Inventory planning tab |
:itemdescr | Description | |
:bomtype | Manufacturing status | |
:distcenter | DC | |
:supplier | Supplier | |
:modeltype | Model type | |
:onhand | On hand | |
:dcfillrate | DC fill rate | |
:ohsupplydays | Days of supply | |
:ohsupplymonth | Months of supply | |
:backorder | To ship | |
:intransition | To receive | |
:leadtime | Lead time, days | |
:ordercycle | Order cycle, periods | |
:minlot | Min lot | |
:maxlot | Max lot | |
:rounding | Rounding | |
:servicelevel | Service level | |
:minshelf | Display qty | |
:shelflife | Shelf life, periods | |
:shelfdiscard | Shelf life exceeding, % | |
:safestock | Safety stock | |
:purchaseprice | Purchase price | |
:margin | Gross margin | |
:debtreceived | Safety stock debt Received | |
:debtaccumulated | Safety stock debt Accumulated | |
:debtpassed | Safety stock debt Passed | |
:turnearn | Turn-earn index | |
:qty | Current order Qty | |
:posupplydays | Current order Days of supply | |
:pomargin | Current order Margin | |
:netorder | Current order Net order | |
:excessorder | Current order Excess order | |
:purchasevalue | Current order Order value | |
:nextorderby | Next order date | |
:reorderpoint | Reorder point | |
:maxinventory | Max inventory | |
:stockout | Stockout | |
:overstock | Overstock | |
:writeoff | Write-offs |
Below, we give an example query that exports all the columns of the Inventory planning tab table.
INSERT INTO [dbo].[export_inventory_report]( [item_code] , [item_description], [location], [distribution_center], [bom_type], [supplier], [model_type], [inventory_on_hand], [on_hand_supply_days], [on_hand_supply_month], [backorder], [in_transition], [leadtime], [order_cycle], [min_lot], [max_lot], [rounding], [service_level], [minimum_display_quantity], [safety_stock], [debt_received], [debt_accumulated], [debt_passed], [shelf_life], [shelf_discard], [purchase_price], [gross_margin], [turn_earn_index], [note], [order_now_quantity], [order_now_net_order_quantity], [order_now_excess_order], [order_now_purchase_value], [order_now_days_of_supply], [order_now_margin], [next_order_by], [dc_fill_rate], [reorder_point], [reorder_amount], [stockout], [overstock], [write_offs]) VALUES ( :itemcode, :itemdescr, :location, :distcenter, :bomtype, :supplier, :modeltype, :onhand, :ohsupplydays , :ohsupplymonth, :backorder, :intransition, :leadtime, :ordercycle, :minlot, :maxlot, :rounding, :servicelevel, :minshelf, :safestock, :debtreceived, :debtaccumulated, :debtpassed, :shelflife, :shelfdiscard, :purchaseprice, :margin, :turnearn, :note, :qty, :netorder, :excessorder, :purchasevalue, :posupplydays, :pomargin, :nextorderby, :dcfillrate, :reorderpoint, :maxinventory, :stockout, :overstock, :writeoff );
You can also download it as an SQL-file.
Then, insert your query into the Export inventory report tab (see figure below).
The Insert stub button allows you to insert a mark that corresponds to a particular column of the table. To see a list of the columns, click a little black triangle next to this button. To insert a mark, choose the column in the list.
The Execute on exporting planned orders option sets up Streamline to trigger the execution of your query as you click the Create button of the Planned orders preview dialog.
The Test section of the tab allows you to test your query using the data you enter into the fields this section contain. These values will substitute the marks in your query. To run the test, click the Execute button.
After all, click the Save button.
Now, to export the inventory report: