4.4.3. Exporting data

Database connection allows you to export Streamline's outcomes back to your database. In this article, we describe how you can:

• export Streamline's current recommendations on purchasing and replenishment;
• export demand and revenue forecasts, purchase plan, and inventory projections; and
• export the Inventory planning tab table.

Database connection allows you to export current replenishment orders into the database of your ERP system or an intermediate database by executing an SQL-query. This query should be given in the Export planned orders tab of the Database connection dialog (see figure below).

If you are going to use an intermediate database, here is a query to create the planned_orders table:

CREATE TABLE [dbo].[planned_orders](
        id [int] IDENTITY(1,1) PRIMARY KEY,
	item_code [nvarchar](250) NOT NULL,
	location [nvarchar](250) NULL,
	reorder_point [INT] NULL,
	quantity_ordered [INT] NOT NULL,
	order_type [nvarchar](250) NULL,
	source_from [nvarchar](250) NULL,
	timestamp [datetime] NULL,
)

You can download this query as an SQL-file.

There are several marks that you can use in this query. They are substituted by the corresponding data when the query is executed. The marks, their description, and the substitution source are given in the table below.

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.

Below, is an example of an SQL-query that exports all possible data accompanying an exported order line.

INSERT INTO planned_orders
( item_code, location, reorder_point, quantity_ordered,
  order_type, source_from, timestamp )
 
VALUES
( :itemcode, :location, :minpoint, :orderqty,
 :ordertype, :sourcefrom, :timestamp )

You can download this query as an SQL-file.

The Test section of the tab allows you to test your query using the data entered into the fields of this section. These values will substitute the marks in your query. To run the test, click the Execute button.

Streamline allows you to export:

• Final forecast report
• Projected revenue report
• Ordering plan
• Projected inventory report

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:

• Item code;

• Location;

• Forecast date – the date of the export if you use Streamline 5.x.x; or the forecast As of date in case of Streamline 4.x.x;

• Period start – the start date of the currently exported data aggregation period (longer period);

• Period end – the end date of the currently exported period;

• Demand forecast – the final forecast for the currently exported period;

• Purchase plan – the suggested quantity to order (replenishment or purchase order) that should be ordered by the beginning of the currently exported period;

• Inventory projection – the inventory level at the end of the currently exported period; and

• Projected revenue – the projected revenue for the currently exported period.

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,
)

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 that 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.

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 entered into the fields of this section. 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:

1. Go to the Inventory planning tab.
2. Click on a little black triangle next to the Export table button of the toolbar.
3. Select the Export to database option from the dropdown (see figure below).

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:

• Entire Purchase plan;
• Demand forecast section; and
• Projected inventory levels section;

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] [nvarchar](250) 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 that 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.

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 entered into the fields of this section. 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:

1. Go to the Inventory planning tab.
2. Click on a little black triangle next to the Export table button of the toolbar.
3. Select the Export to database option from the dropdown (see figure below).

Next: Intermediate database

Download PDF