Google: Closing a Shipment

Closing the Shipment

Google Database

  1. run the carts on shipment query and verify that there are 20 carts listed
  2. in the dbo_Shipments table, change the StatusID to 2. (Change it to 4 once the shipment returns and the work orders have been cleared.)
  3. change the following shipment’s StatusID to 1, if not already
  4. run barcodes on shipment
  5. copy just the barcodes into a text file saved to desktop
  6. in Caia, run the Item Status Report from the file of barcodes
  7. submit ticket to Jon for metadata files

 

Creating the Shipping Manifest

If from a spreadsheet, start by converting the excel file:

  1. confirm that columns are: pallet barcode
  2. remove headings row
  3. save to: C:\queries\createShipments
  4. save as type: Text (Tab delimited)
  5. filename: UILAW-SF_<yyyymmdd>.txt
  6. dedup by barcode
  7. save

Continue with Shipment Report below

 

Shipment Report and Cart Files

Creating the cart files

  1. Go to Windows Start and type in cmd to launch the command window
  2. Type in the following, replacing # with the shipment id.C:\Users\davishow> cd \queries\createShipments
    C:\Users\davishow\createShipments> set PATH=C:\oracle_instant_client\instantclient_12_2\;%PATH%
    C:\Users\davishow\createShipments> perl createShippingCartFiles.pm #
  3. Open Windows explorer (Windows key + e) and go to C:\queries\createShipments
  4. Drag the “Shipment #” folder to the Google Refile Carts – Shortcut folder.
  5. Delete the “Shipment #” folder from the createShipments folder.

Caia

  1. when the email says the Item Status report is ready, download to desktop
  2. open a new excel file and Get Data From Text
  3. delete columns after Last Job
  4. save as item status report on desktop

Google db

  1. import as item status report
  2. export to excel: export shipment report
    • filename: G:\OakStreet\Google Project\Google Refile Carts\Shipment #\shipment #

Excel

  1. open G:\OakStreet\Google Project\Google Refile Carts\Shipment #
  2. open the shipment # spreadsheet
  3. auto-adjust column width through Last Job Date
  4. Format Cells as Text in barcode column
  5. Custom Sort:
    • Cartid
    • Aisle
    • Ladder
    • Step
    • Side
    • Footprint
    • Container
  6. Save
  7. PivotTable
    • Rows: Cartid and Aisle
    • Values: barcode
    • label fields: Cart; Count
    • verify that the aisles sort correctly within carts
    • name the tab: “cart x aisle”
  8. PivotTable
    • Rows: Cartid
    • Columns: Collection
    • Values: barcode
    • hide line 3
    • label fields: Cart; replace (blank) with Stacks?
    • name the tab: “cart x location”
  9. looking at the cart sheet and both pivot tables, identify the carts that are all or partly Stacks (Aisle and Collection are blank)
  10. in the shipment folder, delete files for any carts that are all Stacks

Outlook

email Nick and Andy letting them know the files are ready; cc ops

 

Metadata Files

Uploading for Google

Google only cares about two files: the shipping manifest that contains barcodes & the cart they’re on (UIUC_timestamp) and the zip file of MARCXML records.

  1. Open WinSCP
  2. connect to apacheprod2.library.illinois.edu.
  3. On the left, go into G:\GoogleProject\All_Shipments_And_Corrective_Loads\ND Shipment #
  4. On the right, change path to /www-s/GoogleBooksMetadata
    (If for Law, /www-s/UILaw/GoogleBooksMetadata)
  5. Copy over <timestamp>.zip and  UIUC_yyyymmdd.txt
    (If for Law,  <timestamp>.zip and UILAW-SF_yyyymmdd.txt)

Emailing ACS

  1. Email MJ Han (mhan3@illinois.edu) letting her know that the shipment metadata is ready. They do some further processing on behalf of Hathi on the files.