In StarShip, Write-Back is the process by which certain field values can be written back to the ERP interface when a shipment is processed or modified/voided. In the SQL Interface Builder, you can define the fields that are available for mapping and write-back in the StarShip Client, as well as the data that gets written back and when write-back occurs. Fields that are only defined under Write Back are not available for Target Field (Input) mapping in Customize Interface.
After processing a shipment, StarShip sends an XML version of the shipment to the SQL Interface. These fields are predetermined by what StarShip sends to the StarShip Client and cannot be modified.
The fields in the Field List box for the Write Back tab are the fields that StarShip writes back in the Write-Back XML. This is how you access the write-back information in StarShip.
The Write-Back XML contains information about the shipment. You can see a sample Write-Back XML structure in
...\Documents and Settings\All Users\Application Data\V-Technologies\SQLFSI\SqlFsiData\Assets\WriteBackTest.xml
On the Write Back tab, other than Company fields, you can only reference fields derived from Write-Back XML internal fields or other SQL Select statements set up in the Write Back area.
Select statements can be used in the Write Back area, for example, if you want to update a value in the database, using a Write Back SQL, that is not available in the Company fields or Write Back XML, but does exist in the database.
In this topic :
Batches in Write-Back Settings
SQL Builder comes with a limited set of predefined Shipment fields. With custom fields, StarShip allows you to increase the number of Shipment fields from which you can pull data for the SQL Builder. These custom fields must be mapped to StarShip shipment fields in the StarShip Client in order for write-back to occur. This feature also adds support for translating StarShip values into Source values where applicable (see Value Lists below).
Add the custom fields to any of the tabs in the "Write Back" section, excluding Notes, and these fields will be available for mapping in StarShip. Once you have added a custom field in SQL Builder, you can use that field in the update/insert queries you create, as you normally would with existing write-back field variables. The field will be populated with the value from the StarShip field to which it is mapped.
Value Lists
You can make Value Lists available for value translations in the StarShip Client by assigning a value list to the custom field in SQL Builder.
More about Value Lists...
When you provide a value list for a custom field, you can set StarShip up to translate shipment values into Source values, and write back to the source document. This allows you to update the Source system with a value it recognizes from a StarShip value. The 'Click here to update value translations' link will be available for these fields in the Custom Fields write-back setup in StarShip.
Custom Mapped Field Type
In SQL Builder, a new field type was added specifically for custom write-back. When adding your custom field, assign the "Custom Mapped" field type to define that this field is a custom field. In the example below, we created a "Number of Packages" field :
StarShip Client Setup
The tab under which the custom field was added in the SQL Builder defines the level at which the custom field is displayed in Customize Interface (Header, Line Items, Package, Package Contents) in StarShip. Any fields with a field type of "Custom Mapped" will appear in Customize Interface > Write-Back Setup under the Custom Fields node. You can then map the custom field to a StarShip field that has the value you want to write back. In the example below, we mapped our custom "Number of Packages" field so that it will grab the information from the Total Pack Qty field on the Shipment. When you use the internal variable {WBPackage.Number of Package} field from this example in your query, it will contain the Total Package Qty. for the Shipment.
Document level write-back is performed once for each source document. Use one or more SQL statements to update the source document when the user processes or voids a shipment. The SQL statements on the Document tab can use the following as input parameters :
Run On Status
The Run On Status field defines the action upon which the SQL is run : Add, Delete, or Any.
Add = when a shipment is modified or processed in StarShip
Delete = when a shipment is deleted in StarShip
Any = when either Add or Delete
The Run On Status is triggered by the shipment status that is written back to the Write-Back XML.
This is an example from Demo 1 showing a SQL statement with a Run on Status set to Add.
When writing SQL statements to support updating freight, you must use the Settings.UpdateFreight flag to logically determine if freight is written back. There are three options for setting this flag :
You can use logical constructs available in the SQL supported by your database or use the SQL Interface provided syntax. See IF/THEN/ELSE for more information.
On the Write-Back tab, create one or more SQL statements to update the Batch in the source document. The Batch that the user selects on the StarShip Ship screen is returned in the Document field Batch.BatchName.
This is an example from Demo 1 that has a Run On Status set to Add :
This SQL has a Run on Status set to Delete :
Note : For Batch Write-Back, you must have the setting Batches Supported checked for the Documents you define. Also, when you check this setting, the Batches tab is shown in the Builder.
Another place that batches can be viewed is in Customize Interface Write-Back settings. Under Assign Batch ID, you can set the batch in the ERP interface when a shipment is voided.
In order to see the batches listed here, there is an extra setup step in the SQL Builder.
Having a SQL setup under the Batches tab only affects what shows for the ERP interface tab on the Ship screen. In order to see the Batches listed in Write-Back settings, you first need to configure a value list in the Builder.
To do this :
The SQL query in the Builder that inserts the batch ID would need to be set to run on Delete in order for this setting to have an effect. You could have one SQL update the batch for both Add and Delete. StarShip will send the correct value in the Batch.BatchName dynamically based on this setting for a voided shipment, or the value selected on the Source tab for a processed shipment.
For more information about Batches, see Batches for more information.
Order Items level write-back occurs for each line item in the shipment. From this tab, enter one or more SQL statements used to update the source document when the StarShip user processes or deletes a shipment. The SQL statements on the Order Items tab can use the following input parameters :
Example of a SQL set to Run on Status : Any
Package level write-back is performed for each package in the shipment.
Note : For Order type documents, if a user imports multiple orders into StarShip, any package queries set up in Write-Back will execute once for each package, and once for each order that has items packaged in any particular box.
Enter one or more SQL statements used to update the source document when the StarShip user processes or deletes a shipment. The SQL statements on the Package tab can use the following input parameters :
For example, an Package SQL statement that updates the package tracking number might look like this :
The Run on Status for this SQL could be set to Add, and you could also define another SQL statement with Run on Status set to Delete; for example :
The fixed field called Package ID more easily allows interface developers to update package level information in the ERP interface upon write-back. If importing Packages with SQL Interface you can designate a Field in the Field List as "Package ID". You would then map this field to table.field in your custom database that would store a Package ID. When used, it should represent a unique identifier for the package. StarShip will return a Package ID field ({WBPackage.Package ID} ) containing the package ID you imported.
Using this field is optional. If you do not specify a Package ID field in your field list and import, StarShip uses the Package Number as the value for the {WBPackage.Package ID}.
Guidelines for using Package ID :
The Document file is the input file from the ERP interface. When Package ID is specified, it will be used as a unique identifier for the package. This field will also be preserved in the Shipment File with the same value for the package. This field does NOT have to be listed in the Field List xml to be recognized. A Package ID field added to the Field List in the SQL Interface Builder will be used in the same way.
The Shipment File is the output or write-back from StarShip to the ERP interface. For SQL FSI interfaces, this would be the Write-Back SQL statements.
When Package ID is specified, it will be used as an Index number for imported packages. For packages added manually by the user, the value will always be -1. If the Document file imported contains a Package ID field with a valid value, the same value is returned in the Shipment file. If no Package ID was initially imported, this field will use the Package Number value instead. This field does NOT have to be listed in the Field List xml to be recognized.
Package content level write-back is performed once per package content entry in the shipment. From this tab, enter one or more SQL statements which are used to update the source document when the user processes or voids a shipment. The following input parameters can be used to create the SQL :
Let's say that the source document does not contain package content. You might want to create an SQL that writes back the actual shipment package content.
In this case, your first SQL statement should delete any existing content and have a Run on Status set to Any.
Then a second SQL statement would look like this and have a Run on Status set to Add :
Notes Write-Back allows you to add shipment fields in text format to an existing memo field in the source document. The Document > Write Back > Notes tab will only appear if Notes Update is checked for the document.
StarShip creates the notes based on user settings and sends them to the SQL Interface.
A sample notes value for a shipment would be:
Shipped on: 02/18/2011 from V-TECH
Tracking#: 1Z1235550310000715
Service: Ground
Total Weight: 1.00
Number of Packages: 1
Billing Option: Prepaid
Bill account 51876X
End Shipment(s)
The delimiters, Shipped on: and End Shipment(s), are included in the shipment information sent to the SQL Interface. This allows StarShip to remove the shipment notes from the source document memo field when a shipment is deleted without disturbing any other information the user may have stored there.
StarShip will skip the SQL statements entered on the Notes tab if Notes Write-Back is disabled (Notes Update is unchecked). Unlike SQL statements that update freight at the document level, you do not have to add IF/THEN/ELSE logic to Notes Write-Back SQL statements.
The Notes SQL statements can use the following values as input:
The first step is to create a SQL statement that retrieves the memo field from the source document where the notes will be written.
For example :
Select Notes from Orders where DocId={WBSourceDocument.Document Key}
Next, the SQL Interface needs to associate the value returned with the pre-defined field Initial Notes shown in the Notes field list. To do this, select Initial Notes under Fields and set the SQL Field to the corresponding field in the SQL statement. For example, if the above query was named LoadInitialNotes then the SQL Field for Initial Notes would be set to LoadInitialNotes.Notes.
Finally, create a SQL statement that updates the memo field in the source document with the value in the field Processed Notes (shown in grey in the Fields List).
For example :
UPDATE Orders Set Notes={WBTrackInfo.Processed Notes} where DocId={WBSourceDocument.Document Key}
The Run on Status for the Notes SQL statements should be Any, because the Processed Notes field will automatically be updated (notes are added or removed) based on the write-back status. When adding, the SQL Interface should still check for existing notes and remove them first.
The Interface Builder allows you to add custom Write Back fields and configure a mapped setting. You would then define a SQL statement based on the value in that setting.
After adding a field (click Add under Fields List), select Mapped Setting from the Field Type value. In this example, we will add a setting for tracking number write-back.
Note : The Description will appear under the "More Info..." link in StarShip.
Click the icon to the right to set the custom field properties. These properties display for the field in the StarShip Client, in Customize Interface > Write-Back > Custom Settings.
Label : This will appear as the Field Label in StarShip.
Type : This field defines the type of value. String, Integer and Float (decimal value) will allow you to define a list of values using the editor controls; Boolean displays True/False values. The type you select determines the appearance of the field in StarShip (drop-down field, check box).
Setting Default Value : The value this field will default to in Customize Interface.
Value List : Defines the values available for this setting.
Next, we add the SQL that will write the tracking number back to the database; in our case, to the CustomField3 field. This SQL uses our custom field, which when set to true, will update the CustomField3 field with the tracking number.
Here is the field as it would appear in StarShip's Customize Interface.
Here are some additional notes regarding Write-Back fields :
Joins are not required if the Select SQL queries will only return a single row. The write-back Select statements should retrieve info from the database that you need for other queries that actually update the database. if multiple rows returned, you will need to join these Select SQLs to the Write-Back XML (since there is no Key Provider for the Write Back area). So, think of the Write-Back XML as the Key Provider table to which additional SQL statements would be joined.
Note : If neither SQL returns multiple rows, Join fields are not required.
Company SQLs are the only SQL fields defined for import (data is being sent TO the StarShip shipment) that can be used in the write back area.
Delta Freight is used to update StarShip freight costs in the financial system interface database when the same order has been processed multiple times, has been deleted, or there is pre-existing freight in the FSI database. The Delta Freight field in the StarShip Write back XML is always the same value as the Applied Freight Total, but it can be either positive or negative. If you are voiding a shipment it will be negative; if adding it will be positive.
You could use the Delta Freight to add to an already existing Freight value. To do this, you would set the Run on Status field to Any. The field updates correctly because StarShip adds a negative number to a positive one upon deletion or adds a positive number to another positive number upon addition.
“{if} {WBSourceDocument.Settings.UpdateFreightFlag} == 'Y' {then}
update Shipmentorders set FinalTotal = isnull(FinalTotal, 0) + {WBSourceDocument.Shipment.DeltaFreight} where Shipment_Number = {WBSourceDocument.Document Key}
{endif}”
The FinalTotal field is added to the {WBSourceDocument.Shipment.DeltaFreight} StarShip field, and the FinalTotal field is updated with the result.
There is also a “{WBOrderItem.DeltaShipQty}” field under Line items write-back that has the same functionality, except that it is used to manage the financial system interface's inventory.