Main


Sql-Ledger Main Page

For a few clients, I maintain a distribution of SQL-Ledger, optimized for a conversion from VisionPoint 2000. Features I've developed include:

UPS Worldship integration

UPS's shipping application, Worldship makes it surprizingly easy to query sql-ledger's database to pick up order details. You can easily setup a stock sql-ledger database to be looked up by UPS Worldship, keyed by the Sales Order number. The distribution I've setup will include more details specific to the needs of my clients, such as third party shipping details per customer.

To setup UPS WorldShip to query Sql-Ledger:

  1. Install PGSql ODBC drivers onto windows: psqlODBC
  2. Create a user name and password that can remotely access the database, using the CREATE USER command.
  3. Enable remote access of your Postgres database, by altering listen_address,port in $PGDATA/postgresql.conf. NOTE, Please be aware of the security implications of allowing remote postgres access. You may also need to alter pg_hba.conf to enable an apropriate trust relationship w/ the host running WorldShip
  4. Create the following view in your Sql-ledger database:
    CREATE VIEW shipment_by_salesorder AS
     SELECT oe.ordnumber, shipto.trans_id, shipto.shiptoname, shipto.shiptoaddress1, shipto.shiptoaddress2, shipto.shiptocity, shipto.shiptostate, shipto.shiptozipcode, shipto.shiptocountry, shipto.shiptocontact, shipto.shiptophone, shipto.shiptofax, shipto.shiptoemail, customer.id, customer.name, customer.address1, customer.address2, customer.city, customer.state, customer.zipcode, customer.country, customer.contact, customer.phone, customer.fax, customer.email, customer.notes, customer.discount, customer.taxincluded, customer.creditlimit, customer.terms, customer.customernumber, customer.cc, customer.bcc, customer.business_id, customer.taxnumber, customer.sic_code, customer.iban, customer.bic, customer.employee_id, customer.language_code, customer.pricegroup_id, customer.curr, customer.startdate, customer.enddate
     FROM oe
      JOIN shipto ON oe.id = shipto.trans_id
      JOIN customer ON oe.customer_id = customer.id

    UNION

     SELECT oe.ordnumber, oe.id AS trans_id, c.name AS shiptoname, c.address1 AS shiptoaddress1, c.address2 AS shiptoaddress2, c.city AS shiptocity, c.state AS shiptostate, c.zipcode AS shiptozipcode, c.country AS shiptocountry, c.contact AS shiptocontact, c.phone AS shiptophone, c.fax AS shiptofax, c.email AS shiptoemail, c.id, c.name, c.address1, c.address2, c.city, c.state, c.zipcode, c.country, c.contact, c.phone, c.fax, c.email, c.notes, c.discount, c.taxincluded, c.creditlimit, c.terms, c.customernumber, c.cc, c.bcc, c.business_id, c.taxnumber, c.sic_code, c.iban, c.bic, c.employee_id, c.language_code, c.pricegroup_id, c.curr, c.startdate, c.enddate
     FROM oe
      JOIN customer c ON c.id = oe.customer_id
     WHERE NOT (oe.id IN ( SELECT shipto.trans_id FROM shipto));

    This query selects from the orders that have a customized shipto address, and failing that, selects the customer's billing address. (This works in my setup because of the multi shipto system, and works in most cases in sql-ledger. If someone writes it against the default schema, I'll put it here ...)

    Someone else has used this query to select by invoice number instead of sales order number:

    CREATE or REPLACE VIEW shipment_by_invoice AS  SELECT ar.invnumber, shipto.trans_id, ar.shipvia, shipto.shiptoname, shipto.shiptoaddress1, shipto.shiptoaddress2, shipto.shiptocity, shipto.shiptostate, shipto.shiptozipcode, shipto.shiptocountry, shipto.shiptocontact, shipto.shiptophone, shipto.shiptofax, shipto.shiptoemail, customer.id, customer.name, customer.address1, customer.address2, customer.city, customer.state, customer.zipcode, customer.country, customer.contact, customer.phone, customer.fax, customer.email, customer.notes, customer.discount, customer.taxincluded, customer.creditlimit, customer.terms, customer.customernumber, customer.cc, customer.bcc, customer.business_id, customer.taxnumber, customer.sic_code, customer.iban, customer.bic, customer.employee_id, customer.language_code, customer.pricegroup_id, customer.curr, customer.startdate, customer.enddate
     FROM ar
      JOIN shipto ON ar.id = shipto.trans_id
      JOIN customer ON ar.customer_id = customer.id

    UNION

     SELECT ar.invnumber, ar.id AS trans_id, ar.shipvia, c.name AS shiptoname, c.address1 AS shiptoaddress1, c.address2 AS shiptoaddress2, c.city AS shiptocity, c.state AS shiptostate, c.zipcode AS shiptozipcode, c.country AS shiptocountry, c.contact AS shiptocontact, c.phone AS shiptophone, c.fax AS shiptofax, c.email AS shiptoemail, c.id, c.name, c.address1, c.address2, c.city, c.state, c.zipcode, c.country, c.contact, c.phone, c.fax, c.email, c.notes, c.discount, c.taxincluded, c.creditlimit, c.terms, c.customernumber, c.cc, c.bcc, c.business_id, c.taxnumber, c.sic_code, c.iban, c.bic, c.employee_id, c.language_code, c.pricegroup_id, c.curr, c.startdate, c.enddate
     FROM ar
      JOIN customer c ON c.id = ar.customer_id
     WHERE NOT (ar.id IN ( SELECT shipto.trans_id FROM shipto));
  5. Grant the worldship user you created access on that view:
    GRANT SELECT ON shipment_by_salesorder TO worldship;
  6. Review the instructions @ UPS's web site: Worldship integration documentation, speficially Importing Shipment Data into WorldShip (PDF link) I'm using the Keyed import to lookup a shipment by sales order number
  7. When you create the import/export map in Worldship, be sure to make a Shipment map, and make ordnumber the primary key for import.
  8. From the UPS Online menu, pick 'Keyed Import', 'More', select the map you just created, and click 'Import'. It'll prompt you for a value. Enter your Sales order number, and hit enter. Worldship will fill out your shipment information automatically for you.
  9. That's pretty much it. You can do clever things w/ the Import maps in Worldship, like setup a table to drop order info into. Worldship will pick up the new rows in the table, and automatically process them, and print off apropriate labels. I'm still developing this technique and exploring how to further integrate worldship, and will add more details as I discover them.

mod-perl2 Handler

Due to the way Sql-Ledger is organized, it cannot work out of the box w/ the stock ModPerl::Registry handler. It uses exec() for redirects, it uses exit() to terminate out of the script, it uses require() to dynamically code at runtime, etc. This has it's advantanges, as well as it's drawbacks.

The mod_perl2 handler I am working on will attempt to allow Sql-Ledger to run inside mod_perl. Goals of the project:

2006 June 22 - working, but still needs more testing and documentation before release, Gotchas: Extensions written in with Class::DBI may fail: see here this is probably more of a consequence of using Apache::DBI ...

2006 June 18 - Still not working, I've gotten past the login screen, but it's downhill from there. When a stable version is released I will have better instructions in place...

2006 June 13 - Currently, it's really close to working, and if your interested, you can download the code and take a crack at fixing it. Once I've stablized the code, I will implment a sane versioning system, and I will announce it on the sql-ledger-users list.

Files

Plug

I live & work in the Rhode Island, USA area. If you are a RI, Southern MA, or Eastern CT company and need some on-site Sql-ledger assistance or other customizations, I am available part-time for consulting. Please Contact me for more information

MainPicturesProjectsGPG KeysWritingsMirrorsStuff for saleAdditive Story ComposerGuest BookDon't contact me hereDo Contact me here
Random Image
  • Use Enigmail and Thunderbird
  • Perl Powered
  • The Gimp
  • Vim
  • Browse Happy: Switch to a safer browser today!
  • Get Inkscape
  • Powered By mason
  • Some rights reserved
    © Jason Jay Rodrigues