External Label Interface FAQ
Click here to see this page in full context

External Label Interface FAQ

Question: How can the software integrate with shipping software packages via an export/import functionality?

Answer: During pick demand confirmation, the software can export package and shipper data out to a text file. These text files can then be imported into third-party labeling/shipping software, such as UPS WorldShip or Clippership. After the third-party package has processed the packages and printed labels, the third-party software may be able to export that package information (including especially the tracking number). This information can then be imported back into Apprise. For information on the data exported to the third-party software, see UPS Shipping Export Format. For information on the data imported back into Apprise, see Package Import Format.

To set up the import/export routine within the third-party software, consult with someone who knows the software well. While it is impossible to document every ship label package currently available, below are the instructions to implement this import/export procedure within UPS WorldShip, one of the more popular packages available today.

Creating the UPS Import Layout

Note For information on setting up integration in Apprise, see Integrating with Shippers.

To create the UPS Import layout via ODBC:

  1. Determine the location to which the export file is being written. The export file is created in the folder specified for the shipper interface in the Extract Output Path\File field in Shipper Interface Maintenance.

  2. Export package information from Apprise, using pick demand confirmation, before starting the integration setup process because you will need test data to continue. The file that contains the package data will always be called pkgop.txt.

  3. Set up a Microsoft ODBC text driver to read in external data on every UPS system. To set this up, perform the following substeps:

  1. Open the Windows ODBC Data Source Administrator (accessible from the Windows Control Panel).

  2. Click the System DSN tab.

  3. Click the Add button. The Create New Data Source dialog box appears.

  4. Click Microsoft Text Driver (*.txt; *.csv).

  5. Click the Finish button. The ODBC Text Setup dialog box appears.

  6. Enter Apprise-Pack in the Data Source Name field.

  7. Clear the Use Current Directory check box.

  8. Click the Select Directory button.

  9. Navigate to and select the folder that contains pkgop.txt.

  10. Click the Options button. The ODBC Text Setup dialog box expands to display additional options.

  11. Click the Define Format button. The Define Text Format dialog box appears. Notice that the Tables field displays all the files in the folder you selected in substep i.

  12. Select pkgop.txt.

  13. Select the Column Name Header check box.

  14. Click the Format drop-down arrow, and select Custom Delimited.

  15. Enter ^ in the Delimiter field.

  16. Click the Guess button. The system will scan the file and determine the data in the file, as follows:

  1. Review each column displayed in the Columns field. Each column should be set up as the Char data type with a width of 255. If any columns need to be changed, make the changes, and click the Modify button.

  2. Click the OK button. If a Failed to save table attributes error appears, you can ignore the error message, and click OK.

  3. In the ODBC Text Setup dialog box, click OK to save the text driver.

Importing Package Data Into UPS WorldShip

Apprise integrates best with UPS WorldShip via the keyed import method. The key is the truncated pick demand number (NJ.123). This number can quickly and easily be keyed into the import window, which will then inform UPS to grab the appropriate package records from the package text file.

Note For details of using WorldShip, refer to the WorldShip documentation. The following steps may vary depending on the version of WorldShip you are using.

To import package data into UPS WorldShip:

  1. In UPS WorldShip, select UPS Online Connect menu > Create/Edit Map.

  2. Select Import from ODBC database.

  3. Select Shipment.

  4. Enter Apprise-Pack in the Map Name field.

  5. Click the Create button. A list of datasources appears.

  6. Select the Apprise-Pack datasource.

  7. Click the OK button. The Edit "Apprise-Pack" import map window appears.

Note You do not need to enter an ID or password.

  1. Click the ODBC Tables drop-down arrow, and select pkgop.txt. The window should now appear as follows:

Images\extlab2.gif

  1. Click the Primary Table button. A key icon will appear to the left of the pkgop.txt file name.

  2. Select column 4 (PickTicketNum), and then click the Define Key button. A key icon appears to the left of column 4.

  3. Link the ODBC columns with the proper WorldShip fields. Note that there is only one ODBC table (pkgop), but there are multiple WorldShip tables. Select each ODBC column and WorldShip field, and then click the Connect button. As each column and field is linked, they display in the What You Have Mapped So Far field. Each column and field should only be linked once. If a mistake is made select the incorrectly mapped column/field in the What You Have Mapped So Far field, and click the Disconnect button. The following is a list of the correct mappings.

Column Name

Description

Group

Field

PackageNum

Apprise pick pack package number

Package

Reference 3

Insured Value

oe-trailer.extended-unit-price times the quantity in the package summed up for each item

Package

Insured Value Amount

PickTicketNumRef

Untruncated pick demand number

Package

Reference 5

PickTicketNum

Truncated pick demand number

 

 

CustomerPONum

Customer purchase order number

Package

Reference 1

ShipmentNum

Truncated shipment/invoice number

Package

Reference 2

OrderNum

Truncated customer order number

Package

Reference 4

ShiptoCode

Customer code

Ship To

Customer ID

ShiptoName

Order ship-to name

Ship To

Company or Name

ShiptoAddress1

Ship-to address line 1

Ship To

Address 1

ShiptoAddress2

Ship-to address line 2

Ship To

Address 2

ShiptoAddress3

Ship-to address line 3

Ship To

Address 3

ShiptoCity

Ship-to address city

Ship To

City or Town

ShiptoState

Ship-to address state

Ship To

State/Province/County

ShiptoZip

Ship-to address postal code

Ship To

Postal Code

ShiptoCountry

Ship-to address country

Ship To

Country/Territory

ShiptoPhone

Ship-to address phone number

Ship To

Telephone

ShiptoContact

Ship-to address contact

Ship To

Attention

InsuranceOption

Insurance option (YES, to indicate UPS should insure the package)

Package

Insured Value Option

TotalPackages

Total number of package records

 

 

ShipVia

Ship via on the package record

 

 

SCAC

Standard Carrier Alpha Code (SCAC) for the shipper

 

 

OtherID

shipper.other-id field

Shipment Information

Service Type

ShipperCode

Special shipper code

Ship From

UPS Account Number

UCC128

UCC128 number

 

 

PkgWeight

Package weight

Package

Weight

CartonUMCode

Package unit of measure

 

 

CartonLength

Package length

Package

Length

CartonWidth

Package width

Package

Width

CartonHeight

Package height

Package

Height

ShippingAccountID

Order shipping account ID

Ship To

Receiver UPS Account Number

Residential

Y if the shipper is a residential shipper

Ship To

Residential Indicator

BillTransportationTo

Freight collection terms (TP for third-party, REC for Collect, or SHP for prepaid)

Shipment Information

Bill Transportation To

TPBillToName

For third-party freight collection terms, the third-party bill-to name

Third Party

Company or Name

TPBillToAttention

For third-party freight collection terms, the main contact for the third-party

Third Party

Attention

TPBillToAddress1

For third-party freight collection terms, the first line of the third-party bill-to address

Third Party

Address 1

TPBillToAddress2

For third-party freight collection terms, the second line of the third-party bill-to address

Third Party

Address 2

TPBillToAddress3

For third-party freight collection terms, the third line of the third-party bill-to address

Third Party

Address 3

TPBillToCity

For third-party freight collection terms, the city of the third-party bill-to address

Third Party

City or Town

TPBillToState

For third-party freight collection terms, the state of the third-party bill-to address

Third Party

State/Province/County

TPBillToZip

For third-party freight collection terms, the postal code of the third-party bill-to address

Third Party

Postal Code

TPBillToUPSAccountNumber

For third-party freight collection terms, the UPS account number of the third-party

Third Party

UPS Account Number

SignatureRequired

Y if a signature is required for delivered packages

 

 

  1. Click the OK button when you are finished mapping.

  2. To start using the keyed import, go to the main UPS WorldShip window, and select UPS Online Connect menu > Keyed Import.

  3. Click the More button. A list of defined maps appears.

  4. Select the Apprise-Pack map and click the Import button. A small input field appears. This is the keyed import field.

  5. Type or scan the truncated pick demand number, and UPS will import data for all packages for that pick demand. Note that the pick demand number is case sensitive.

  6. Weigh each package. After all the weights are entered, the shipment can be processed.

Importing Tracking Numbers Into Apprise

To import tracking numbers back into Apprise, data must be exported from UPS and manually imported. The export out of UPS can be set up to occur after processing each shipment or during the end of the day process. After the package information has been exported, the export file can be imported into Apprise.

Note Apprise and UPS do not clear the contents of the file. The import file should be cleared out approximately once a week, depending on how large the export files become. There is no danger in importing the tracking number information multiple times.

Similar to the UPS import, the UPS export is defined using an ODBC datasource. To define the datasource, you must already have an export file.

To import tracking numbers back into Apprise:

  1. Copy the following text into an empty text file in Notepad:

00000001^NJ000000123^1Z12345678901234^

  1. Save the text file in the same location as the UPS import file.

  2. Set up a Microsoft ODBC text driver to read in external data on every UPS system. To set this up, perform the following substeps:

  1. Open the Windows ODBC Data Source Administrator (accessible from the Windows Control Panel).

  2. Click the System DSN tab.

  3. Click the Add button. The Create New Data Source dialog box appears.

  4. Click Microsoft Text Driver (*.txt; *.csv).

  5. Click the Finish button. The ODBC Text Setup dialog box appears.

  6. Enter UPS-Pack in the Data Source Name field.

  7. Clear the Use Current Directory check box.

  8. Click the Select Directory button.

  9. Navigate to and select the folder that contains pkgop.txt.

  10. Click the Options button. The ODBC Text Setup dialog box expands to display additional options.

  11. Click the Define Format button. The Define Text Format dialog box appears. Notice that the Tables field displays all the files in the folder you selected in substep i.

  12. Select the text file you created in step 2.

  13. Select the Column Name Header check box.

  14. Click the Format drop-down arrow, and select Custom Delimited.

  15. Enter ^ in the Delimiter field.

  16. Click the Guess button. The system will scan the file and determine the data in the file.

  17. Review each column displayed in the Columns field. Each column should be set up as the Char data type with a width of 255. If any columns need to be changed, make the changes, and click the Modify button.

  18. Click the OK button. If a Failed to save table attributes error appears, you can ignore the error message, and click OK.

  19. In the ODBC Text Setup dialog box, click OK to save the text driver.

  1. Create a new map in UPS WorldShip. To do this, perform the following substeps:

  1. Select the Export data from WorldShip check box.

  2. Select Shipment from the selection box.

  3. Enter a descriptive name for the export.

  4. Map the fields using the following table (Note that the UPS tables are on the left side of the screen and the export file columns are on the right side of the screen).

Column Heading

Description

Group

Field

PackageNum

Links back to a package record in Apprise

Package

Reference 1

PickTicketNumRef

This is how you know the pick demand to which to link

Package

Reference 2

Tracking Number

The UPS generated 1Z tracking number

Package

TrackingNumber

Weight

The weight of the package.weight, which is more accurate than the weight calculated in Apprise (it includes the weight of the box and other packaging materials).

Package

Weight

 

Freight Charge

The base standard UPS charge for the package without any discounts

Package

Package Charge

  1. Click the UPS Online Connect drop-down arrow on the main UPS shipping window, and select whether to export the data during the end of day process or at the end of each shipment.

  2. After the data is exported from UPS, use the Package Import to import the tracking numbers.