Laddawn.com : Shipping Notifications - Programming Outline

Created by Judy Ascensao, last modified on Feb 09, 2016

CPU orders Process Explanation:

  1. CPU order ships in Clippership. (CPU orders ship to hold file, Trucker orders ship to ship file)
  2. When the CPU orders are picked up by a new process at the bottom of auto ship. We will send the CPU Ready notification and set a flag in new in field on the record in SQL.  This new process will use that new field to decide what records to process. This new field will be a date/time field.  This way we can notify CE to contact the customer if orders are sitting in HOLD for too long.
  3. We may need/want to add logic to handle weekends when deciding when to contact CE.

Ship to order shipped process explanation:

  1. At 5:30PM warehouse time we grab all the shipments for the day from that warehouse.
  2. For each shipment shipped that day, we call the website to email the shipment notification.

Technical overview:

  1. UI – (Ticket 14908) Change SendNotification method of the OrderHistoryController,
    1. OrderID: ShipmentID to send a notice for.
    2. document = ShipNotice,
    3. media = email
    4. toEmail = ; delimited list of  recipients. 
    5. ccEmail = blank, we don't send shipment notifications  to more people.
    6. newSubject = blank, subject  will be calculated in the routine.
    7. coverNote: blank, ignored.
    8. calledFrom: blank, should not matter on this call
  2. UI – The new approach here is going to be that we are not going to use a single database call that returns all the data necessary for the entire screen.  This may necessitate populating child collections and objects in the mapper.  But since this is going to get called in a loop for about 200 shipments it's time to start optimizing for performance rather than encapsulation.
  3. UI – Create new Business Entity Shipment Package
    1. Properties include:
      1. Tracking Number
      2. Weight
  4. UI – Create new Business Entity Shipment Package Item
    1. Properties Include
      1. Item Number
      2. Quantity
  5. DB – (Ticket 14902) Create RPC routine (RPC$UTILITY_GETWHSSHIPMENTS) to loop through the shipments that have shipped from a given warehouse and return SHIPTRN ids for that day.
    1. Select shipments for the day.  Exclude CPU shipments (they've already been notified), Credit/Debit Memos and Transfer Orders.
    2. INPUT SHIP.DATE and WHS.CD. OUTPUT SHIPMENT ID'S
  6. DB – (Ticket 14923) Create routine (RPC$ORDER_GETSHIPMENTNOTIFICATION) to return all the data needed to fill the Shipment Notification email.
    1. Gather Shipment and Order Information for the entire master order.
    2. Input Parameters:
      1. SHIPMENT.ID
    3. Return Field Name
      1. EMAIL.TO (MIX OF CONTACT NUMBERS AND EMAIL ADDRESSES – – WE WANT TO ONLY SEND EMAIL ADDRESSES (SEMI-COLON DELIMITED) – CONVERT THE CONTACT NBRS. *** only email addresses are sent from UI
      2. LOGIN.TOKEN (IF NOT THE ORDER CONTACT, DON'T PASS TOKEN)   *** if the order contact's email address doesn't equal the cc email address, don't pass token
      3. CONTACT.NBR
      4. CONTACT.NAME
      5. NEPB.FLAG
      6. ORDER.NBR
      7. PO.NBR
      8. ORDER.NAME
      9. ORDER.DATE
      10. DS.PONBR
      11. PAY.TERMS
      12. DATE.TYPE
      13. BILLTO.NBR
      14. BILLTO.NAME
      15. BILLTO.ADDR1
      16. BILLTO.ADDR2
      17. BILLTO.ADDR3
      18. BILLTO.CITY
      19. BILLTO.STATE
      20. BILLTO.ZIP
      21. SHIPTO.NBR
      22. SHIPTO.NAME
      23. SHIPTO.ADDR1
      24. SHIPTO.ADDR2
      25. SHIPTO.ADDR3
      26. SHIPTO.CSZ
      27. SHIPMENT.ID (SEND THIS GROUP OF DATAFIELDS MULTIPLE TIMES – ONE FOR EACH SHIPMENT)
      28. SHIPMENT.TYPE (0 – PAST, 1 – PRESENT / CURRENT, 2 – FUTURE) *** STOCK RIDING W/MOD IN PRESENT BUNDLE SHOW TOGETHER – IN PAST BUNDLE THEY CAN BE SHOWN SEPARATELY.
      29. SHIPMENT.WHS
      30. SHIPMENT.DATE
      31. SHIPMENT.ORDER
      32. SHIPMENT.VIA
      33. SHIPMENT.FOB (Name of person paying for the freight)
      34. SHIPMENT.COMMENTS (CHAR(253)-DELIMITED)
  7. DB – (Ticket 14959) Create routine (RPC$ORDER_GETSHIPMENTNOTIFICATIONITEMS) to return all the data for the lines on a given shipment id.
    1. Input Parameters
      1. SHIPMENT.ID
    2. Return Field Names (one set per line shipped - break out SAMPLE items)
      1. ITEM.ID (CPN)
      2. ITEM.NBR
      3. ITEM.DESC1 (BUILD SAME AS UI)
      4. ITEM.DESC2
      5. UOM
      6. QUANTITY SHIPPED TODAY (SHIPTRN)
      7. PRICE (SODET)
      8. TOTAL (shipped today x price – except for samples, which will show $20/$10)
  8. Checkout summary screen:
    1. UI – pass out new named param NOTIFY.EMAIL
    2. DB (Ticket 15015) – new field to MSTRORDHDR.USR updated during create order – write out mix of contact numbers and email addresses passed from UI via NOTIFY.EMAIL
  9. Tracking Screen:
    1. DB (ticket 15240) - The following new fields are required for each Ship Via Code -add these new fields to the existing CARRIER.USR file.  Add a function key to SOP9099 for 'Web Info' and add a subscreen for the entry of these new fields:
      1. Tracking URL (optional)
      2. Carrier Phone Nbr  (either URL or phone# is required)
      3. List of multi-valued Service Warehouses (which of our warehouses use this carrier)
      4. Multi-valued field to hold 'Description of Service' - this better explains what the carrier service is - this will be used on a hover over the carrier code in the checkout shipping method popup
    2. DB (ticket 15240) - Modify RPC$SHIPMENTITEM_GETBYFILTER:
      1.  Send along the new fields Tracking URL and Carrier Phone# to the UI.  The service warehouse's and desc of service will be needed at some point in checkout.  Not for this screen.
      2. Sort by Individual Order# (w/alpha suffix) by Item# - this screen will no longer be by just master order - it will show each individual order and that order's lines
      3. The tracking numbers must now be passed associated with the individual order and line item.  ICEHDRHST.USR<10> holds the line item#, <11> holds the part#, and I believe <12> is the quantity shipped for that tracking number.  They want to see the total quantity shipped of that line in the order shipped column, but the quantity in the box with each tracking number is in parenthesis after the tracking#.  So if 4 were shipped in total of this item, but 3 were in box1 and 1 in box 2, the (3) and (1) should show after the associated tracking#.
    3. UI (Ticket 15720) - Modify popup screen to show match visual design
      1. Remove the Heading fields to match the header in the visual design
      2. Add a package quantity column to the detail lines on the grid.
      3. For carriers that don't have online tracking, add the call support text from visual design.
  10. CPU Shipping Notifications - these will be sent hourly by looking in Clippership for CPU orders that have been 'shipped to hold' and sending a shipping notification:
    1. UI - The UI will kick off an hourly scheduled job that calls a new RPC
    2. DB - New RPC$UTILITY_GETCPUSHIPMENTS that will:
      1. DB/UI - Call a new stored procedure that will get the Clippership CPU orders that have been shipped to hold and aren't stamped with a date that a notification was sent.  The UI will return all shipping data for the CPU orders
      2. DB - Write the returned data to a new CPUSHIP.USR file with a key of Order*Release (SHIPTRN Key) and send a list of SHIPMENT.ID back to the UI
      3. UI - loop through the list of shipment ids and call the DB header and detail programs (same programs as you are calling for the nightly shipping notifications - send email
      4. DB - the 'details' program that was previously written will have to read the shipment data for 'present day' CPU orders, because that data hasn't made it back to the Avante SODET file yet.

      5.  

Process List:
We create a Scheduled task that runs TaskRunner to call RPC$UTILITY_GETWHSSHPMENTS. Their details as follows:

  • 01: Time: 5:30PM Parameters: COMPANY.ID=01 WHS=01
  • 02: Time: 5:30PM Parameters: COMPANY.ID=01 WHS=02
  • 04: Time: 6:30PM Parameters: COMPANY.ID=01 WHS=04
  • 05: Time: 8:30PM Parameters: COMPANY.ID=01 WHS=05
  • 06: Time: 6:30PM Parameters: COMPANY.ID=01 WHS=06