Advanced Database Automation

SixBit has been built on an open SQL Server database for the sole purpose of allowing users to integrate existing inventory and sales management systems into their SixBit process.  To facilitate this integration, we have provided our Enterprise edition users some stored procedures in the database to make the integration easier.

Stored Procedures

The following stored procedures are available for users.

GetPaidOrdersNotShipped

Parameters: None

 

Purpose: This procedure returns all orders that have been paid for, but have not been shipped; it is designed to help users integrate with existing shipping systems by providing them with a list of orders that need to be shipped.

 

Details: It includes orders that:

    - have a status of "In Process"

    - do not yet have a shipped date

    - do not yet have the IsShipped flag set

    - have received payment equal to (or greater) than the amount due

    

Each item sold is returned as a separate row, so if an order had multiple items sold, it will return multiple records for an order each with the same OrderID.

 

SetShipmentAsShipped

Parameters:

  • ShipmentID - the SixBit unique shipment ID of the shipment to be updated.
  • ShippedDate - the date the shipment was shipped.  This must include the time also.
  • TrackingNumber - the tracking number of the shipment
  • ShippingServiceID - the enumerated id of the shipping service used to send the shipment. (See Enumerations for allowed values)

 

Purpose: This procedure marks a shipment as being shipped in SixBit and flags SixBit to proceed with notifying the eCommerce site with updated information about the shipment.

 

Details: It performs the following tasks:

  • sets the shipped date of the shipment
  • sets the tracking number of the shipment
  • sets the shippingserviceID
  • sets the NotifySite flag so the SixBit Agent will upload information to the eCommerce site during its next run.

 

By default, SixBit does not automatically check for new notifications.  If SetShipmentAsShipped is being used to update the database and you would like SixBit to start sending the updates, you must turn the option on.  

 

 

To turn on automatic site notifications:

  1. Open the SixBit Options window.     How?How?Click the File button in the upper left corner and then choose SixBit Options in the lower right corner of the popup.

  2. Select the Advanced Automation tab.
  3. Click the box in front of the Enable post sale notifications option.

Enumerations

SixBit stores unique ids for many fields for the sake of efficiency.  The enumerations used in the provided stored procedures are identified here.

 

Shipping Service ID

0  UPS Ground

1  UPS 3 Day Select

2  UPS 2nd Day Air

3  UPS Next Day Air Saver

4  USPS Priority Mail

5  USPS Parcel Select

6  USPS Media Mail

7  USPS First Class Mail

8  Standard Shipping

9  Expedited Shipping

10  USPS Priority Mail Express

11  UPS Next Day Air

12  UPS 2nd Day Air AM

13  Economy Shipping

14  UPS Next Day Air Early AM

15  (Not Selected)

16  One-Day Shipping

17  Standard Int'l FlatRate Shipping

18  Expedited Int'l FlatRate Shipping

25  UPS Worldwide Express Plus

26  UPS Worldwide Express

27  UPS Worldwide Expedited

28  UPS Standard to Canada

29  Economy Int'l Shipping

30  USPS Priority Mail Int'l

31  USPS Priority Mail Express Int'l

32  USPS First Class Mail Int'l/First Class

33  USPS Priority Mail Express Int'l Flat Ra

34  USPS Priority Mail Int'l Medium Flat Rat

35  USPS Priority Mail Int'l Flat Rate Envel

36  USPS Priority Mail Express Flat Rate Env

37  USPS Priority Mail Flat Rate Envelope

38  USPS Priority Mail Medium Flat Rate Box

39  USPS Priority Mail Large Flat Rate Box

40  USPS Priority Mail Int'l Large Flat Rate

41  USPS Global Priority Small Envelope

42  USPS Global Priority Large Envelope

43  USPS Global Express Guaranteed

44  USPS Global Airmail M-Bag

45  USPS Priority Mail Padded Flat Rate Enve

46  USPS Priority Mail Legal Flat Rate Envel

47  USPS Priority Mail Express Legal Flat Ra

48  USPS Priority Mail Regional Box A

49  USPS Priority Mail Regional Box B

50  USPS Priority Mail Int'l Legal Flat Rate

51  USPS Priority Mail Express Intl. Legal F

52  USPS Priority Mail Int'l Padded Flat Rat

53  USPS Priority Mail Express Int'l Padded

54  USPS Priority Mail Express Padded Flat R

55  USPS Priority Mail Regional Box C

101  Local Pickup

102  Local Delivery

110  DHL Express

111  Electronic

200  Freight

201  International Freight

300  FedEx 2 Day

301  FedEx Express Saver

302  FedEx Ground or Home Delivery

303  FedEx Int'l Economy

304  FedEx Int'l Ground To Canada

305  FedEx Int'l Priority

306  FedEx Priority Overnight

307  FedEx Standard Overnight

308  FedEx Extra Hours

309  FedEx First Overnight

310  FedEx 2Day Freight

311  FedEx International Economy Freight

312  FedEx International Extra Hours

313  FedEx International First

314  FedEx International MailService Premium

315  FedEx International MailService Standard

316  FedEx International Next Flight

317  FedEx International Priority Freight

318  FedEx Overnight Freight

319  FedEx Saver Freight

320  FedEx SmartPost

321  FedEx Express Saver One Rate

322  FedEx FedEx 2 Day One Rate

323  FedEx FedEx Standard Overnight One Rate

324  FedEx FedEx Priority Overnight One Rate

400  AusPost Express Courier International

401  AusPost Express Post International Parce

402  AusPost PrePaid Express Post Internation

403  AusPost PrePaid Express Post Internation

404  AusPost PrePaid Express Post Internation

405  AusPost PrePaid Express Post Internation

406  AusPost PrePaid Express Post Internation

407  AusPost PrePaid Express Post Internation

408  AusPost PrePaid Express Post Internation

409  Local Pickup

411  TNT International Express

412  Australia Post Express Post Platinum 3kg

413  Australia Post Express Post Platinum 500

414  Australia Post Express Post 3kg Satchel

415  Australia Post Express Post 500g Satchel

416  Australia Post Prepaid Parcel Post Plus

417  Australia Post Prepaid Parcel Post Plus

418  Toll Consumer Delivery

420  Australia Post Parcel Post Parcel

421  Australia Post Express Post Parcel

422  FastwayCouriers

424  Australia Post Prepaid Parcel Post Plus

425  Australia Post Registered Prepaid Parcel

427  Courier

432  AusPost Air Mail Parcel

434  AusPost Sea Mail Parcel

435  Standard International Flat Rate Postage

436  Express International Flat Rate Postage

438  TNT

439  Australia Post Regular Parcel Registered

440  Australia Post Registered Prepaid Parcel

441  Australia Post Registered Prepaid Parcel

442  Australia Post Regular Parcel Registered

443  DHL

444  Australia Post Parcel Post Parcel + Trac

500  UPS Worldwide Express Box 10kg

501  UPS Worldwide Express Box 25kg

502  UPS Worldwide Express Plus Box 10kg

503  UPS Worldwide Express Plus Box 25kg

504  USPS First Class Large Envelope

505  USPS First Class Parcel

506  USPS Priority Mail Small Flat Rate Box

507  USPS Priority Mail Int'l Small Flat Rate

526  Economy Shipping From Outside US

527  Standard Shipping From Outside US

528  Expedited Shipping From Outside US

529  UPS Worldwide Saver

535  ePacket delivery from China

536  ePacket delivery from Hong Kong

540  UPS SurePost Parcel

541  UPS SurePost Bound Printed Matter

542  UPS SurePost Media

543  UPS Mail Innovations First Class

544  UPS Mail Innovations Priority

545  UPS Mail Innovations Expedited

546  UPS Mail Innovations Priority Internatio

547  UPS Mail Innovations Economy Internation

618  Australia Post Parcel Post Parcel + Sign

619  Star Track Express

621  Australia Post Express Post 5kg Satchel

639  Standard delivery

640  AusPost Registered Post International Pa

641  AusPost Registered Post International Pa

642  AusPost Registered Post International Pa

643  eBay Australia Post Flat Rate 3kg Satche

644  Express delivery

645  RU Tracked Packet From China

646  eBay Fastway Road Express

647  Australia Post Express Post Parcel + Sig

648  Australia Post Express Post Satchel 5kg

649  Economy delivery from outside AU

650  Standard delivery from outside AU

651  Expedited delivery from outside AU

652  eBay Australia Post Flat Rate 500g Satch

655  Freight

656  Toll Global Economy

657  UBI Smart Parcel

658  eBay Australia Post Flat Rate 3kg Satche

659  eBay Australia Post Flat Rate 500g Satch

660  Australia Post Flat Rate BX1 Small Box

661  Australia Post Flat Rate BX1 Small Box +

662  Australia Post Flat Rate BX2 Medium Box

663  Australia Post Flat Rate BX2 Medium Box

664  Australia Post Flat Rate BX4 Large Box

665  Australia Post Flat Rate BX4 Large Box +

1086  Standard Delivery

1087  Expedited Delivery

1088  Canada Post Lettermail

1089  Canada Post Regular Parcel

1090  Canada Post Expedited Parcel

1091  Canada Post Xpresspost

1092  Canada Post Priority Courier

1093  Standard Int'l Flat Rate Shipping

1094  Expedited Int'l Flat Rate Shipping

1095  Other International Shipping

1098  Canada Post Xpresspost - USA

1099  Canada Post Xpresspost - International

1100  Canada Post International Parcel - Surfa

1101  Canada Post International Parcel - Air

1103  Purolator International

1105  Canada Post Small Packets - USA - Air

1106  Small Packets - International - Ground

1107  Small Packets - International - Air

1108  Canada Post USA Letter-post

1109  Canada Post International Letter-post

1110  UPS Express Canada

1111  UPS Express Saver Canada

1112  UPS Expedited Canada

1113  UPS Standard Canada

1114  UPS Express United States

1115  UPS Expedited United States

1116  UPS 3 Day Select United States

1117  UPS Standard United States

1118  UPS Worldwide Express

1119  UPS Worldwide Expedited

1120  Canada Post Expedited Flat Rate Box

1121  Canada Post Expedited Flat Rate Box USA

1122  Canada Post Tracked Packet - USA

1123  Canada Post Tracked Packet - Internation

1385  Canada Post Light Packet - International

1386  Canada Post Light Packet - USA

1388  Canada Post Expedited Parcel - USA

1389  Local Pickup

2168  FedEx International Economy

2170  TNT International Express

2289  Collect + Tracked (Heavy up to 10Kg)

2290  Collect + Tracked (Jiffy Bag up to 2Kg)

2291  Collect + Tracked (Standard up to 5Kg)

2295  Royal Mail International Economy

2301  Standard Int'l Postage

2311  Collect in Person

2312  Express Int'l Postage

2316  Freight

2317  Other Courier 3 Days

2318  Other Courier 5 Days

2322  myHermes

2323  Collect + Drop at Store

2327  Royal Mail Tracked

2328  Royal Mail Tracked Next Day

2329  Standard Delivery from outside UK with R

2330  Collect + Economy Tracked

2338  Collect + Standard

2344  Yodel 24

2345  Yodel 48

2346  UPS Next Day

2347  DPD Next Day

2348  DPD Two Day

2351  MiniPak from outside UK

2352  TrakPak from outside UK

2353  bPost International Parcel

2354  DHL Express Worldwide

2374  Royal Mail 1st Class Standard

2375  Royal Mail 2nd Class Standard

2376  Royal Mail 1st Class Signed For

2377  Royal Mail 2nd Class Signed For

2378  Royal Mail Standard Parcels

2379  Seller's Standard Rate

2380  Parcelforce 24

2381  Parcelforce 48

2382  Other Courier

2388  Royal Mail Airmail International (Small

2389  Royal Mail International Tracked

2391  Royal Mail International Signed

2392  Royal Mail HM Forces Mail

2393  ParcelForce International Datapost

2394  ParcelForce Ireland 24

2395  ParcelForce Euro 48

2396  Royal Mail 48

2397  Royal Mail 24

2481  Royal Mail Special Delivery Next Day

2522  Royal Mail Special Delivery 9am

2575  Standard International

2576  Expedited International

2577  Economy International

2580  Parcelforce Global Express

2581  Parcelforce Global Priority

2582  Parcelforce Global Value

2583  Parcelforce Global Economy (not availabl

2623  Other Courier 24h

2624  Other Courier 48h

2641  Economy Shipping from outside the UK

2642  Express Shipping from outside the UK

2643  Standard Shipping from outside the UK

2644  International Tracked Postage

2645  Tracked delivery from outside UK

2646  RU Tracked Packet from China

2647 UK Mail

2648 DHL Parcel UK

2811  Economy Int'l Postage

3000  DHL GMSM Parcel Plus Expedited

3001  DHL GMSMBPM Expedited

3002  DHL GMSMBPM Ground

3003  DHL GMSM Media Mail Ground

3004  DHL GMSM Parcels Expedited

3005  DHL GMSM Parcels Ground

3006  DHL GMSM Parcel Plus Ground

3007  DHL GMSM Priority Mail

3008  DHL GMSM First Class Product

3009  DHL GMSM First Class Parcels

241001  DHL

241002  FedEx 2nd Day

241003  FedEx Standard Overnight

241004  FedEx Priority Overnight AM

241005  FedEx Saturday Overnight

241006  UPS 2nd Day Air

241007  UPS 3 Day Select

241008  UPS Ground

241009  UPS Next Day Air

241010  Mail - First Class

241011  Mail - Parcel Post

241012  Mail - Media

241013  Mail - Express

241014  Mail - Priority

241015  Mail - Registered

241016  USPS Airmail

241017  In Person / By Hand

241018  Pickup Only

241019  Other

 

PaymentTypeID

        None = 0

        Cash = 1

        Check = 2

        COD = 3

        COD_PrePay_Delivery = 4

        Credit_Card = 5

        Escrow = 6

        Money_Order = 7

        Money_Transfer = 8

        PayPal = 9

        PayPal_eCheck = 10

        BidPay = 11

        Other = 12

        Google_Checkout = 15

        Amazon = 17

 

SiteID

The SiteID must be one of the siteids from the Sites table.