AbleCommerce Shopping Cart Software


http://forums.ablecommerce.com/

Australia Post Rates - SQL Inserts

http://forums.ablecommerce.com/viewtopic.php?f=48&t=8042

Page 1 of 1

Australia Post Rates - SQL Inserts

Posted: Mon Aug 25, 2008 5:30 pm
by Mark Harris
These are accurate at the time of posting, data mined from Australia Post's website. (http://www1.auspost.com.au/pac/) since the delivery rate calculator service was down: https://www.edeliver.com.au/IFS_DRC_Mailer.process

For these to work, you MUST have the following setup:
When looking at your shipment methods, you can find the shipment ID from the url as below:
EditShipMethodMatrix.aspx?ShipMethodId=1

The ID in this case is "1".

Australia Post Air Mail must be ID 1 (INTL)
Australia Post Registered International must be ID 2 (INTL)
Australia Post Express Post International mus be ID 3 (INTL)
Australia Post Express Satchel must be ID 4 (DOMESTIC, not imported - only 2 entries
Australia Post Regular Parcel must be ID 5 (DOMESTIC)

If you have other shipmethodid's you'll need to do a find and replace on the inserts below.

International rates:

Code: Select all

BEGIN TRAN ins;

INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 1, 250, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 1, 250, 22.45, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 1, 250, 10.30, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 251, 500, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 251, 500, 22.45, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 251, 500, 17.45, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 501, 750, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 501, 750, 29.60, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 501, 750, 24.60, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 751, 1000, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 751, 1000, 36.75, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 751, 1000, 31.75, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 1001, 1250, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 1001, 1250, 43.90, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 1001, 1250, 38.90, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 1251, 1500, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 1251, 1500, 51.05, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 1251, 1500, 46.05, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 1501, 1750, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 1501, 1750, 58.20, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 1501, 1750, 53.20, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 1751, 2000, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (2, 1751, 2000, 65.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 1751, 2000, 60.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 2001, 2500, 74.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 2001, 2500, 69.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 2501, 3000, 83.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 2501, 3000, 78.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 3001, 3500, 92.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 3001, 3500, 87.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 3501, 4000, 101.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 3501, 4000, 96.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 4001, 4500, 110.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 4001, 4500, 105.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 4501, 5000, 119.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 4501, 5000, 114.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 5001, 5500, 128.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 5001, 5500, 123.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 5501, 6000, 137.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 5501, 6000, 132.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 6001, 6500, 146.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 6001, 6500, 141.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 6501, 7000, 155.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 6501, 7000, 150.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 7001, 7500, 164.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 7001, 7500, 159.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 7501, 8000, 173.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 7501, 8000, 168.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 8001, 8500, 182.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 8001, 8500, 177.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 8501, 9000, 191.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 8501, 9000, 186.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 9001, 9500, 200.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 9001, 9500, 195.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 9501, 10000, 209.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 9501, 10000, 204.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 10001, 10500, 218.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 10001, 10500, 213.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 10501, 11000, 227.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 10501, 11000, 222.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 11001, 11500, 236.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 11001, 11500, 231.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 11501, 12000, 245.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 11501, 12000, 240.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 12001, 12500, 254.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 12001, 12500, 249.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 12501, 13000, 263.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 12501, 13000, 258.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 13001, 13500, 272.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 13001, 13500, 267.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 13501, 14000, 281.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 13501, 14000, 276.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 14001, 14500, 290.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 14001, 14500, 285.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 14501, 15000, 299.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 14501, 15000, 294.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 15001, 15500, 308.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 15001, 15500, 303.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 15501, 16000, 317.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 15501, 16000, 312.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 16001, 16500, 326.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 16001, 16500, 321.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 16501, 17000, 335.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 16501, 17000, 330.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 17001, 17500, 344.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 17001, 17500, 339.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 17501, 18000, 353.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 17501, 18000, 348.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 18001, 18500, 362.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 18001, 18500, 357.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 18501, 19000, 371.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 18501, 19000, 366.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 19001, 19500, 380.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 19001, 19500, 375.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (3, 19501, 20000, 389.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (1, 19501, 20000, 384.35, 0);

COMMIT TRANSACTION ins;
GO
Domestic parcels

Code: Select all

BEGIN TRAN ins;

INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 1, 250, 4.20, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 251, 500, 5.40, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 501, 1000, 11.40, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 1001, 2000, 13.75, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 2001, 3000, 16.10, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 3001, 4000, 18.45, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 4001, 5000, 20.80, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 5001, 6000, 23.15, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 6001, 7000, 25.50, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 7001, 8000, 27.85, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 8001, 9000, 30.20, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 9001, 10000, 32.55, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 10001, 11000, 34.90, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 11001, 12000, 37.25, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 12001, 13000, 39.60, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 13001, 14000, 41.95, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 14001, 15000, 44.30, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 15001, 16000, 46.65, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 16001, 17000, 49.00, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 17001, 18000, 51.35, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 18001, 19000, 53.70, 0);
INSERT INTO [ac_ShipRateMatrix] (ShipMethodId, RangeStart, RangeEnd, Rate, IsPercent) VALUES (5, 19001, 20000, 56.05, 0);

COMMIT TRANSACTION ins;
GO

These are parcels between australia and germany for international, germany is in the "rest of the world" category. If you want pricing to specific countries let me know, i have a program which generates these insert statements very easily ;)

Pricing for domestic is from perth to queensland, which is also in the highest rate category.

Re: Australia Post Rates - SQL Inserts

Posted: Mon Jun 15, 2009 6:13 am
by Thoro80
Hello Mark.

Do you happen to have an updated list of prices for Australia Post? I know some of the prices have been changed since this post and I thought there might be a reasonable chance that you had updated your SQL.

Regards,
Chris.

Re: Australia Post Rates - SQL Inserts

Posted: Mon Jun 22, 2009 6:21 am
by StuartLeitch
I notice that Australia Post can now be setup as an an Integrated Provider within AbleCommerce. I'm new to AbleCommerce and not sure how long this has been the case. Would it not make more sense to use the integrated provider vs. entering values into the database which will change over time?

Re: Australia Post Rates - SQL Inserts

Posted: Mon Jun 22, 2009 9:37 am
by mazhar
Yes you should make use of integrated provider, this seems to be an old post.
All times are UTC-05:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/