QAD 2017 Enterprise Edition > User Guides > Lean Manufacturing > Using External Applications > Kanban Workbenches > Importing Kanban Workbench Data
  
Importing Kanban Workbench Data
You can use Kanban Workbench Import (17.2.17) in two ways:
• After loading the data into an external application, updating it as needed, and saving it to a comma-delimited import file such as a comma-separated values (CSV) file, use Kanban Workbench Import to load data from the file into the system.
• In an import file, set up data records to create new loops, then import the file using Kanban Workbench Import. The system creates a new kanban master record for each loop defined, as well as supporting records as needed. See Kanban Master Field Mapping.
As long as the individual records are correctly formatted, you can perform both types of tasks using the same file.
Important: For the system to create a new loop based on imported data, the following import file values must already exist:
• Item number, defined in Item Master Maintenance (1.4.1)
• Supermarket site and, for process loops, process site, defined in Site Maintenance (1.1.13)
• For process loops, process ID, defined in Process Maintenance (17.1.3)
• For supplier loops, supplier address, assigned as part of a business relation

Kanban Workbench Import (17.2.17)
Import Directory
Optionally enter the path to the directory where this program looks for files to import. This value defaults from Kanban Control.
When the field is blank, the system looks for files in the working directory.
File Name
Enter the name of the file that contains the data to be imported into the system. Be sure to use the complete file name, including the extension.
This must be a comma-delimited file. For example, if the external application saves files in comma-separated values format, the file will have an extension of .csv.
Update
Specify whether the program immediately updates the database or just generates a report showing simulated before and after values. You can use this report to make sure that the results are what you expect before updating the database.
Card Reconciliation
Specify whether the system reconciles the number of cards in the loop with the optimum number. The value defaults from Kanban Control. See Card Reconciliation.
When the field is Yes:
• On updated loops, the system activates or creates new cards if more are needed, or inactivates excess cards, based on settings in Kanban Master Maintenance and Kanban Control. Additionally, if the loop needs to be regenerated because of a change to certain key values such as the kanban quantity, reconciliation creates a new set of cards.
• On loops created based on the imported file, the system creates the number of cards specified in the file.
When Card Reconciliation is No, no reconciliation takes place for existing or new loops.
Print New Cards
Specify whether you want to print cards that were activated or created as part of the reconciliation process. This can be set to Yes only when Card Reconciliation is Yes.
This field displays only when Update is Yes.
Kanban Workbench Import File Definition shows the structure of the import file that Kanban Workbench Import requires to work properly. The values in these fields are the same values you can update using the kanban workbenches.
Important: The first record in the import file must contain only the literal string kanban workbench. Each subsequent record must include the fields shown in the table. Sample Kanban Workbench Import File shows an example of a correctly formatted import file. It contains a record that will update an existing loop as well as one that will create a new loop.
Note: Fields shown in bold type are used only when the record is intended to create a new kanban loop—that is, when knb_primary_key is blank. Leave them blank when the record is intended to update an existing loop.

Kanban Workbench Import File Definition
 
Field Name
Format
Description
knb_primary_key
decimal
Kanban Loop ID: Internal keyid of knb_mstr record exported from the system. When this field is blank, the system attempts to create a new kanban loop.
part
character
Item number
step
integer
Process step
site
character
Supermarket site
supermarket_id
character
Supermarket ID
source_type
character
Source type (enter number representing language detail value):
• 1 - Supplier
• 2 - Inv
• 3 - Process
source_ref1
character
Value varies by source type:
• Supplier: Supplier address
• Process: Process site
• Inventory: Source site
source_ref2
character
Value varies by source type:
• Supplier: PO number
• Process: Process ID
• Inventory: Supermarket ID
source_ref3
character
PO line; used only when source type is supplier
epei_rev
decimal
EPEI
daily_demand_rev
decimal
Daily demand
demand_pct_rev
decimal
Demand percent
replenishment_rev
integer
Replenishment time (in seconds)
fifo_time_rev
integer
Internal FIFO time (in seconds)
fifo_ext_time_rev
integer
External FIFO time (in seconds)
safety_stock_rev
decimal
Safety stock
safety_time_rev
decimal
Safety days
var_factor_rev
decimal
Variability
order_qty_rev
decimal
Order quantity
order_qty_mult_rev
decimal
Order quantity multiple
kanban_qty_rev
decimal
Kanban quantity
number_of_cards_rev
integer
Number of cards
buffer_maximum_rev
decimal
Buffer maximum
order_point_rev
decimal
Order point
epei_auto_calc_rev
logical
EPEI auto calculate
epei_conv_factor
decimal
EPEI conversion factor: Used to change the EPEI display value into days (as stored in the database)
no_of_resources
decimal
Number of resources
min_process_epei
decimal
Minimum process EPEI
lead_time_method
integer
Lead time method (enter number representing language detail value):
• 1 - Fixed
• 2 - Variable
load_limit
decimal
Load limit
min_process_item_epei
decimal
Minimum item EPEI
item_yield_pct_rev
decimal
Item yield percent
run_out_option_rev
integer
Run-out option (enter number representing language detail value):
• 0 - No
• 1 - Yes
fraction_kanban_percent_rev
decimal
Fractional kanban
card_reporting_rev
integer
Card reporting (enter number representing language detail value):
• 1 - Standard
• 2 - Add
• 3 - Remove

Sample Kanban Workbench Import File
Field Mapping for New Loops
To create a new loop definition based on the import file, the system generates a number of database table records. Some fields are populated with values from the import file; others default to predetermined values. This section identifies the source of each field in these records and relates the tables to menu programs.
Kanban Items
Kanban Item Field Mapping shows how the system creates new kanban items when they are needed. These are the same records defined in Kanban Item Master Maintenance (17.1.1).

Kanban Item Field Mapping
 
Kanban Item Master Maint Field
Source or Default
Notes
Item
Import file: part
Validated against existing items defined in Item Master Maintenance
Step
Import file: step
If not included in import file, set to 0
Routing Code
Blank
 
BOM Code
Blank
 
Container Type
Blank
 
Minimum Item EPEI
Import file: min_process_ item_epei
 
Cost Allocation
100%
 
Average Inv Calc Method
Standard
 
Run-Out Option
No
 
Run-Out Setup
0   00:00:00
 
Run-Out Material
Blank
 
Run-Out Quantity
0.0
 
UM
Blank
 
Run-Out Quantity Per
0.0
 
Run-Out UM Conversion Factor
0.0000
 
Comments
No
 
Kanban Supermarkets
Supermarket Field Mapping shows how the system creates new supermarkets when they are required. These are the same records defined in Supermarket Maintenance (17.1.2).
A new supermarket is required only when:
• The supermarket_ID value in the import file does not exist in for the specified site. In this case, the system creates a new destination supermarket.
• For an inventory loop, the source_ref2 value in the import file does not represent a valid supermarket at the site shown in source_ref1. In this case, the system creates a new source supermarket.

Supermarket Field Mapping
 
Kanban Supermarket Maint Field
Source or Default
Description
Blank
Location Type
Inv
Inventory Location
Blank
Comments
No
Super Market Fax
Blank
Supermarket Fax[2]
Blank
Email
Blank
Kanban Process Items
Process Item Field Mapping shows how the system creates new process item records when Source Type is Process. These are the same records created when you define process items using the Kanban Process Detail frame in Kanban Process Maintenance (17.1.3).

Process Item Field Mapping
 
Kanban Process Maint Field
Source or Default
EPEI
Import file: epei_rev
EPEI Auto
Import file: epei_auto_calc_rev
Min EPEI
Import file: min_process_ epei
Yield
100%
Mfg Seq #
0
Cycle Time
0
Setup Time
0
Other Time Fields
00:00:00
Kanban Loop Tables
To build a new kanban loop, the system creates a Kanban Master Maintenance record using the following key values from the import file:
• Item (existing or created by import)
• Step
• Supermarket site
• Supermarket ID (existing or created by import)
Kanban Master Field Mapping shows how the system determines the remaining Kanban Master Maintenance values for new kanban loops.
Note: By default, the system creates only one-card loops. You must manually adjust the loop in Kanban Master Maintenance after running the import if you need to include move cards.

Kanban Master Field Mapping
 
Kanban Master Maint Field
Source or Default
Notes
Source Master Data frame
Source Type
Import file: source_type
Must be either Supplier, Inv, or Process
Supplier (when Source Type is Supplier)
Import file: source_ref1
Must be valid supplier defined in defined in Supplier Create and completed in Supplier Data Maintenance
Purchase Order (when Source Type is Supplier)
Import file: source_ref2
If not blank, must be valid purchase order
Line (when Source Type is Supplier)
Import file: source_ref3
If not blank, must be valid line on specified purchase order
Process Site (when Source Type is Process)
Import file: source_ref1
Must be valid site
Process ID (when Source Type is Process)
Import file: source_ref2
Must be valid process ID
Operation Range (when Source Type is Process)
0–9999
 
Use FIFO (when Source Type is Process)
No
 
Source Site (when Source Type is Inv)
Import file: source_ref1
Must be valid site
Supermarket (when Source Type is Inv)
Import file: source_ref2
Must be valid supermarket; cannot be same site/supermarket combination as destination site/supermarket
Supermarket Item Detail frames
Order Point
Import file: order_point_ revised
Warning displays when this is not a multiple of Kanban Quantity
Location Type
From Supermarket Maintenance for destination supermarket
Supermarket can already exist or be created by import
Inventory Location
From Supermarket Maintenance for destination supermarket
Supermarket can already exist or be created by import
Average Demand Template
Kanban Control
 
Working Buffer
0
 
Buffer Maximum
Import file: buffer_maximum_rev
Warning displays if value is less than Order Point or not a multiple of Kanban Quantity, Pack Quantity
Buffer Modified
No
 
Limit Display
PCT
 
Critical Limit
33
 
Limit Modified
Blank
 
Warning Limit
66
 
Daily Demand
Import file: daily_demand_rev
 
Variability
Import file: var_factor_rev
 
Demand Modified
No
 
Demand Percent
Import file: replenishment_rev
 
Average Inv Calc Method
Standard
 
Pack Quantity
1
 
Package Type
Blank
 
Count Tolerance
5
 
Lag Factor
0.0
 
Safety Stock
Import file: safety_stock_rev
Warning displays if safety stock is more than Buffer Maximum or Order Point
Safety Days
Import file: safety_days_rev
 
Service Level
50%
 
Safety Stock Template
From Kanban Control
 
Safety Stock Method
Manual
 
Peak Average Days
0
 
SuperMarket Fax
From Supermarket Maintenance for destination supermarket
Supermarket can already exist or be created by import
SuperMarket Fax [2]
From Supermarket Maintenance for destination supermarket
Supermarket can already exist or be created by import
Email
From Supermarket Maintenance for destination supermarket
Supermarket can already exist or be created by import
One/Two Card
One
 
Kanban Planner
Buyer/Planner field in Item Planning Maintenance record; if not found, same field in Item Master Maintenance; otherwise, blank
 
Carrier (when Source Type is Supplier)
From Supplier Data Maintenance
 
Carrier E-mail (when Source Type is Supplier)
From business relation linked in Carrier Maintenance
 
Card Tracking Control frames
Order Quantity
Import file: order_qty_revised
Warning displays if this is not a multiple of Kanban Quantity
Number of Cards
Import file: number_of_cards_rev
Warning displays if Number of Cards * Kanban quantity is not equal to Buffer Max
Kanban Quantity
Import file: kanban_qty_rev
Warning displays if this is not greater than Container Capacity
Container Capacity
1
Cannot be zero
Container Type
From Kanban Item Master Maintenance
Kanban item can already exist or be created by import
Print Quantity
From Kanban Control
 
Print Barcode (Item Number and Kanban ID)
From Kanban Control
 
User Reference
Blank
 
Ship Delivery Pattern Code
Blank
 
Point Of Use Location
Blank
 
Delivery Location
Blank
 
Order Quantity Multiple
Import file: order_qty_mult
 
Ship Delivery Time Code
Blank
 
Comments
No
 
Dispatch List
Yes
 
Fax Dispatch List
No
 
E-mail Dispatch List
No
 
Blanket PO Release
Yes when value in Purchase Order field represents a blanket PO; otherwise, No
 
Source Fax, Src Fax[2], Source E-mail
Varies by source type:
• Supplier: from Supplier Create
• Inv: from Supermarket Maintenance (existing or created by import)
• Process: blank
 
Repl Time
Import file: replenishment_revised
 
FIFO Time Int
Import file: fifo_time_revised
 
Ext
Import file: fifo_ext_time_revised
 
Card Reporting
From Kanban Control
 
Fractional Kanban
From Kanban Control
 
Run Out Option
No
 
Accumulator Type
Quantity
 
Accum Interval
0
 
Next date
Blank
 
Next Time
00.00.00
 
Regenerate Required
No
 
Work Days (Sunday through Saturday)
No
All work times are set to 00:00:00.
Kanban Transaction Control frame
Regeneration Enforcement
From Kanban Control
 
Kanban Cycle Enforcement
From Kanban Control
 
Qty Violation Method
Round
 
Rounding Threshold
100.00
 
Minimum Cycle
From Kanban Control
 
Maximum Cycle
From Kanban Control
 
Decrease at Consume
From Kanban Control
 
PO Receipt Data Entry
From Kanban Control
 
Lot Entry
From Kanban Control
 
Component/Op Transactions
Yes when Source Type is Process and Destination supermarket’s location type is Inv; otherwise, No
 
Impact Inventory
No when:
• Source Type is Process and destination supermarket’s location types is WIP
• Source Type is Inv, destination supermarket’s location type is WIP, and source supermarket’s location type is WIP
Otherwise, Yes
 
Use Control Prog Tran Settings
Yes