Many people have emailed to ask for the attachment since it got filtered.
I am inserting it as text below my signature.

Rich Taylor | Senior Programmer/Analyst| VERTIS
250 W. Pratt Street | Baltimore, MD 21201
P 410.361.8688 | F 410.528.0319 
[EMAIL PROTECTED] | http://www.vertisinc.com

Vertis is the premier provider of targeted advertising, media, and
marketing services that drive consumers to marketers more effectively.

"The more they complicate the plumbing
  the easier it is to stop up the drain"

- Montgomery Scott NCC-1701

**********************\/\/\/\/\/\//\/\/\/\/\/\/\/********************
$INCLUDE UNIVERSE.INCLUDE ODBC.H
*
      PROGRAM = 'SOSHIP.UPDATE'
      ERROR.NO = 0
 
*-----------------------------------------------------------------------
      *    Preparing the SQL processes
 
*-----------------------------------------------------------------------

      SQL.OK = TRUE                      ; * error flag for sql processes
      SQL.STATUS = ClearDiagnostics()

      * setup the ODBC connection to the SOSHIP.mdb file
      SQL.STATUS = SQLAllocConnect(@HENV,SOSHIP.MDB.CONNECT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not establish a connection to the Access
workspace: "
         MESSAGE := "Failed to allocate a CONNECTION environment"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END
      IF SQL.OK THEN
         GOSUB MDB.CONNECT
      END                                ; * endif sql.ok
(SOSHIP.MDB.CONNECT)

      * Setup the ODBC connection to the LOCAL UV account
      SQL.STATUS = SQLAllocConnect(@HENV,SOSHIP.UV.CONNECT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not establish a connection to the Universe
Database environment: "
         MESSAGE := "Failed to allocate a CONNECTION environment"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END
      IF SQL.OK THEN
         SQL.STATUS = SQLConnect(SOSHIP.UV.CONNECT,"localuv",'','')
         IF SQL.STATUS <> SQL.SUCCESS THEN
            MESSAGE = "Could not establish a connection to the Universe
Database environment: "
            MESSAGE := "Failed to connect to data source"
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END
      END                                ; * endif sql.ok
(SOSHIP.UV.CONNECT)

      * setup customer table transfer sql statement environments
      SQL.STATUS = SQLAllocStmt(SOSHIP.UV.CONNECT,CUSTOMER.SEL.STMT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the CUSTOMER file in Universe: "
         MESSAGE := "Failed to allocate a STATEMENT environment (select)"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      SQL.STATUS = SQLAllocStmt(SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the CUSTOMER file in Universe: "
         MESSAGE := "Failed to allocate a STATEMENT environment (insert)"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      * SQL statement objects to access SOSHIP.MDB parts Allocations table
      SQL.STATUS = SQLAllocStmt(SOSHIP.MDB.CONNECT,SOSHIP.PARTS)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the SOSHIP parts allocations table in
SOSHIP.mdb: "
         MESSAGE := "Failed to allocate a STATEMENT environment"
         GOSUB ERROR.PROCESS
      END

      * SQL statement objects to access SOSHIP.MDB Order Allocations table
      SQL.STATUS = SQLAllocStmt(SOSHIP.MDB.CONNECT,SOSHIP.ORDERS)
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not access the SOSHIP Orders Allocations table
in SOSHIP.mdb: "
         MESSAGE := "Failed to allocate a STATEMENT environment"
         GOSUB ERROR.PROCESS
      END

      IF NOT(SQL.OK) THEN
         CLOSE SOSHIPF
         STOP
      END                                ; * endif not(sql.ok) ...
      *-------------------------------------------------------------------
      *  End of SQL Preparation
      *-------------------------------------------------------------------

      LOOP
         SQL.STATUS = ClearDiagnostics()
         SQL.OK = TRUE

         GOSUB RESET.SQL.STATEMENTS
         GOSUB PREPARE.SQL.INSERT.COMMANDS
         IF NOT(SQL.OK) THEN EXIT

         SQL.STATUS = ClearDiagnostics()

         ' code removed
      UNTIL RET.VALUE<1> = "CANCEL" DO
         SOSHIP.ID = PART.NO:"*":LOCATION

         * get the inventory balance and part description
         INV = RAISE(TRANS('INV',PART.NO,-1,'X'))
         LOCATE(LOCATION,INV,2;LOC.VMC) THEN
            INV.BAL = OCONV(INV<3,LOC.VMC>,'MR4')
         END ELSE
            INV.BAL = 0
         END                             ; * endlocat(location,inv ....
         PART.DESC = TRANS('PARTS',PART.NO,1,'X')

         * calculate header totals
         ALLOC = OCONV(SUM(SOSHIP<14>),'MR4')
         ON.ORDER = OCONV(SUM(SOSHIP<4>), 'MR4')
         BACKORDER = ON.ORDER - ALLOC
         NUM.ORDER = 0
         NUM.CUST = 0

         * select the customer data & add to soship.mdb database
         SQL.STATUS = SQLExecute(CUSTOMER.SEL.STMT)

         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.
MSG)
            MESSAGE = "Could not execute the SQL Selection of SOSHIP
Customer data!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
            SQL.OK = FALSE
            GOSUB ERROR.PROCESS
            EXIT
         END                             ; * endif sql.status #
sql.success ....
         LOOP
            SQL.STATUS = SQLFetch(CUSTOMER.SEL.STMT)
         UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
            SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL
.MSG)
               MESSAGE = "Could not insert record into the SOSHIP.mdb
DATABASE, Customer table!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB
ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               SQL.OK = FALSE
               GOSUB ERROR.PROCESS
               EXIT
            END                          ; * endif sql.status #
sql.success ....
            NUM.CUST += 1
         REPEAT
         IF NOT(SQL.OK) THEN EXIT

         * update the soship order allocation table (line item detail of
allocations)
         LINE.CNT = DCOUNT(SOSHIP<1>,@VM)
         SO.LIST = ''
         FOR IDX = 1 TO LINE.CNT
            ALLOC.LINE = SOSHIP<1,IDX>
            SO.NO = SOSHIP<2,IDX>["*",1,1]
            SO.LINO = SOSHIP<2,IDX>["*",2,1]
            CUST.NO = SOSHIP<6,IDX>
            SCHED.DATE = OCONV(SOSHIP<3,IDX>,'D4\')
            SCHED.QTY = OCONV(SOSHIP<4,IDX>,'MR4')
            ALLOC.QTY = OCONV(SOSHIP<14,IDX>,'MR4')
            PRICE = OCONV(SOSHIP<5,IDX>,'MR4')

            LOCATE(SO.NO,SO.LIST;DUMMY) ELSE SO.LIST<-1> = SO.NO

            SQL.STATUS = SQLExecute(SOSHIP.ORDERS)

            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
               MESSAGE = "Could not insert orders allocation data into the
SOSHIP.MDB file!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB
ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               GOSUB ERROR.PROCESS
               SQL.OK = FALSE
               EXIT
            END                          ; * endif sql.status #
sql.success ....
         NEXT IDX
         IF NOT(SQL.OK) THEN EXIT

         * update soship part allocation table data
         NUM.ORDER = DCOUNT(SO.LIST,@AM)
         * load the Parts allocation table in soship.mdb
         SQL.STATUS = SQLExecute(SOSHIP.PARTS)

         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not insert parts allocation data into the
SOSHIP.MDB file!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
            SQL.OK = FALSE
            GOSUB ERROR.PROCESS
            EXIT
         END                             ; * endif sql.status #
sql.success ....

         * Call structure for SoshipClient: soshipclient.exe <path>
<port=999>
         *    where path is the full network path to the SOSHIP.MDB file
         *       (path is currently not used, but the position must be
filled}
         *          and 999 is the port number of the user.

         * reset just the SOSHIP.ORDERS statement environment
         SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE)
         SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND)
         SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS)

         NAP 500
* ====> CALL EXTERNAL CLIENT PROGRAM

         IF STATUS = 1 THEN
            SLEEP 3
         END ELSE
            MESSAGE = "Could not start SoshipClient program on
workstation"
            MESSAGE<2> = MB.ERROR
            EXIT
         END                             ; * endif status = 1 (pix.win.run
call)

         * reopen the soship data source connection
         * retrieve updated soship allocations from the mdb file
         GOSUB PREPARE.SQL.RESULTS.COMMANDS
         IF SQL.OK THEN
            SQL.STATUS = SQLExecute(SOSHIP.ORDERS)

            IF SQL.STATUS # SQL.SUCCESS THEN
               SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
               MESSAGE = "Could not execute the SQL Selection against
SOSHIP.mdb "
               MESSAGE := "OrderAllocations table data!  "
               MESSAGE := "SQL.STATE=":SQL.STATE:":  DB
ERROR=":DB.ERROR:":  MESSAGE=":SQL.MSG
               SQL.OK = FALSE
               GOSUB ERROR.PROCESS
            END                          ; * endif sql.status #
sql.success ....
            IF SQL.OK THEN
               LOOP
                  SQL.STATUS = SQLFetch(SOSHIP.ORDERS)
               UNTIL SQL.STATUS = SQL.NO.DATA.FOUND DO
                  LOCATE(ALLOC.LINE,SOSHIP,1;SOSHIP.IDX) THEN
                     IF (SO.NO:"*":SO.LINO) = SOSHIP<2,SOSHIP.IDX> THEN
                        SOSHIP<14,SOSHIP.IDX> = ICONV(ALLOC.QTY,'MR4')
                     END ELSE
                     END                 ; * endif (so.no:"*":so.lino) =
soship<2,soship.idx> ....
                  END                    ; * endlocate(alloc.line,....
               REPEAT
               SOSHIP<17> = SUM(SOSHIP<4>) - SUM(SOSHIP<14>)
            END                          ; * endif sql.ok (select of
OrderAllocations)
         END                             ; * endif sql.ok (prepare sql
results)
         GOSUB CLEANUP.MDB

         IF SINGLE.UPDATE THEN EXIT      ; * break out of loop if single
update only
      REPEAT

      GOSUB CLEANUP.MDB                  ; * insure that we have cleaned
up our workspace
*
      SQL.STATUS = SQLFreeStmt(CUSTOMER.INS.STMT,SQL.DROP)
      SQL.STATUS = SQLFreeStmt(CUSTOMER.SEL.STMT,SQL.DROP)
      SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.DROP)
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.DROP)
      SQL.STATUS = SQLDisconnect(SOSHIP.UV.CONNECT)
      SQL.STATUS = SQLFreeConnect(SOSHIP.UV.CONNECT)
      SQL.STATUS = SQLDisconnect(SOSHIP.MDB.CONNECT)
      SQL.STATUS = SQLFreeConnect(SOSHIP.MDB.CONNECT)
*
      STOP
*
* ===================================================================
* <Routines>:
* ===================================================================
PREPARE.SQL.INSERT.COMMANDS:* Load sql statement environments appropriate
      * to loading SOSHIP.MDB

      * Prepare UV customer file selection command for retreiving customer
data to
      * be loaded into the Customer table in SOSHIP.MDB
      SQL.STATUS =
SQLBindParameter(CUSTOMER.SEL.STMT,1,SQL.B.BASIC,SQL.CHAR,25,0,SOSHIP.ID)
      SQL.STATUS = SQLBindCol(CUSTOMER.SEL.STMT,1,SQL.B.DEFAULT, CUSTNO)
      SQL.STATUS = SQLBindCol(CUSTOMER.SEL.STMT,2,SQL.B.DEFAULT, NAME)
      SQL.STATUS = SQLBindCol(CUSTOMER.SEL.STMT,3,SQL.B.DEFAULT, CITY)
      SQL.STATUS = SQLBindCol(CUSTOMER.SEL.STMT,4,SQL.B.DEFAULT, STATE)
      SQL.STATUS = SQLBindCol(CUSTOMER.SEL.STMT,5,SQL.B.DEFAULT, ZIP)
      CUST.SEL.CMD = "SELECT DISTINCT CUST_NO,NAME,CITY,STATE,ZIP FROM
CUSTOMER "
      CUST.SEL.CMD := "WHERE CUST_NO IN (SELECT CUST_NO FROM
SOSHIP_SOSHIP_L0 WHERE @ID = ?)"
      SQL.STATUS = SQLPrepare(CUSTOMER.SEL.STMT,CUST.SEL.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.UV.CONNECT,CUSTOMER.SEL.STMT,SQL.STATE,DB.ERROR,SQL.
MSG)
         MESSAGE = "Could not create the SQL select command for the SOSHIP
customer data!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status #
sql.success ....

      * Prepare the SQL insert command to load the customer data into the
Customer table
      * of the SOSHIP.MDB file.  Note the binding of parameters to
variable names.
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,2,SQL.B.BASIC,SQL.CHAR,10,0,CUSTNO)
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,3,SQL.B.BASIC,SQL.CHAR,30,0,NAME)
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,4,SQL.B.BASIC,SQL.CHAR,30,0,CITY)
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,5,SQL.B.BASIC,SQL.CHAR,2,0,STATE)
      SQL.STATUS =
SQLBindParameter(CUSTOMER.INS.STMT,6,SQL.B.BASIC,SQL.CHAR,10,0,ZIP)
      CUST.INS.CMD = "INSERT INTO Customer
(Port_No,Customer_No,Name,City,State,ZipCode) "
      CUST.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?)"
      SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,CUST.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL
.MSG)
         MESSAGE = "Could not create the SQL Insert command for the
customer table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status #
sql.success ....

      * Prepare the SQL insert command to load the PartsAllocation Table
of the
      * SOSHIP.MDB file. Note the variable bindings.
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,4,SQL.B.BASIC,SQL.CHAR,30,0,PART.DESC)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,5,SQL.B.BASIC,SQL.REAL,12,4,INV.BAL)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,6,SQL.B.BASIC,SQL.REAL,12,4,ALLOC)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,7,SQL.B.BASIC,SQL.REAL,12,4,ON.ORDER)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,8,SQL.B.BASIC,SQL.REAL,12,4,BACKORDER)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,9,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.ORDER)
      SQL.STATUS =
SQLBindParameter(SOSHIP.PARTS,10,SQL.B.BASIC,SQL.INTEGER,6,0,NUM.CUST)
      PART.INS.CMD = "INSERT INTO PartsAllocation
(Port_No,Part_No,Inventory_Location,"
      PART.INS.CMD := "Part_Description,Inventory_Balance, Allocated,
On_Order, "
      PART.INS.CMD := "Backorder,Order_Count,Customer_Count) "
      PART.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
      SQL.STATUS = SQLPrepare(SOSHIP.PARTS,PART.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Insert command for the
PartsAllocations table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status #
sql.success ....

      * Prepare the SQL insert command to load the OrderAllocationS Table
of the
      * SOSHIP.MDB file. Note the variable bindings.
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,2,SQL.B.BASIC,SQL.INTEGER,6,0,ALLOC.LINE)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,3,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,4,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,5,SQL.B.BASIC,SQL.CHAR,6,0,SO.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,6,SQL.B.BASIC,SQL.SMALLINT,5,0,SO.LINO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,7,SQL.B.BASIC,SQL.CHAR,10,0,CUST.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,8,SQL.B.BASIC,SQL.DATE,10,0,SCHED.DATE)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,9,SQL.B.BASIC,SQL.REAL,10,4,SCHED.QTY)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,10,SQL.B.BASIC,SQL.REAL,10,4,ALLOC.QTY)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,11,SQL.B.BASIC,SQL.REAL,10,4,PRICE)
      ORDER.INS.CMD = "INSERT INTO OrderAllocations
(Port_No,Alloc_Line,Part_No,"
      ORDER.INS.CMD := "Inventory_Location,Sales_Order, Sales_Order_Line,
Customer_No,"
      ORDER.INS.CMD :=
"Schedule_Date,Schedule_Quantity,Allocated_Quantity,Unit_Price) "
      ORDER.INS.CMD := "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)"
      SQL.STATUS = SQLPrepare(SOSHIP.ORDERS,ORDER.INS.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
         MESSAGE = "Could not create the SQL Insert command for the
OrdersAllocations table!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status #
sql.success ....

      RETURN
* ===================================================================
PREPARE.SQL.RESULTS.COMMANDS:* Load sql statement envrionments appropriate
      * to retrieving the altered data from SOSHIP.MDB
      * reset just the SOSHIP.ORDERS statement environment
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE)
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND)
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS)


      * build SQL select statement to retrieve Order allocation lines to
load any
      * allocation adjustments into the UV SOSHIP file
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,1,SQL.B.BASIC,SQL.INTEGER,6,0,PORT.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,2,SQL.B.BASIC,SQL.CHAR,20,0,PART.NO)
      SQL.STATUS =
SQLBindParameter(SOSHIP.ORDERS,3,SQL.B.BASIC,SQL.SMALLINT,5,0,LOCATION)
      SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,1,SQL.B.DEFAULT, ALLOC.LINE)
      SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,2,SQL.B.DEFAULT, SO.NO)
      SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,3,SQL.B.DEFAULT, SO.LINO)
      SQL.STATUS = SQLBindCol(SOSHIP.ORDERS,4,SQL.B.DEFAULT, ALLOC.QTY)
      READ.ALLOC.CMD = "SELECT
Alloc_Line,Sales_Order,Sales_Order_Line,Allocated_Quantity FROM
OrderAllocations "
      READ.ALLOC.CMD := "WHERE Port_No = ? and Part_No = ? and
Inventory_Location = ?"
      SQL.STATUS = SQLPrepare(SOSHIP.ORDERS,READ.ALLOC.CMD)
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
         MESSAGE = "Could not create the SQL select statment to retrieve
soship order allocations!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END                                ; * endif sql.status #
sql.success ....

      RETURN
* ===================================================================
RESET.SQL.STATEMENTS:* reset and clear the statement environments

      * reset the customer selection statement environment
      SQL.STATUS = SQLFreeStmt(CUSTOMER.SEL.STMT,SQL.CLOSE)
      SQL.STATUS = SQLFreeStmt(CUSTOMER.SEL.STMT,SQL.UNBIND)
      SQL.STATUS = SQLFreeStmt(CUSTOMER.SEL.STMT,SQL.RESET.PARAMS)

      * reset the customer table insert statement environment
      SQL.STATUS = SQLFreeStmt(CUSTOMER.INS.STMT,SQL.CLOSE)
      SQL.STATUS = SQLFreeStmt(CUSTOMER.INS.STMT,SQL.UNBIND)
      SQL.STATUS = SQLFreeStmt(CUSTOMER.INS.STMT,SQL.RESET.PARAMS)

      * reset the parts allocation table environment
      SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.CLOSE)
      SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.UNBIND)
      SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.RESET.PARAMS)

      * reset the order allocation table environment
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE)
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.UNBIND)
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.RESET.PARAMS)

      RETURN
* ===================================================================
CLEANUP.MDB:* commands to cleanup the SOSHIP.mdb file for this users data

      * clean up order allcation data
      SQL.STATUS = SQLFreeStmt(SOSHIP.ORDERS,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(SOSHIP.ORDERS,"DELETE FROM OrderAllocations
NOWAIT where Port_No = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
         MESSAGE = "Could not create the SQL Delete command for the
OrderAllocations table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END ELSE
         SQL.STATUS = SQLExecute(SOSHIP.ORDERS)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.ORDERS,SQL.STATE,DB.ERROR,SQL.MSG
)
            MESSAGE = "Could not clear the OrderAllocations table for the
current user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END                             ; * endif sql.status #
sql.success ....
      END                                ; * endif sql.status #
sql.success ....

      * clean up parts allcation data
      SQL.STATUS = SQLFreeStmt(SOSHIP.PARTS,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(SOSHIP.PARTS,"DELETE FROM PartsAllocation
NOWAIT where Port_No = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
         MESSAGE = "Could not create the SQL Delete command for the
PartsAllocation table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END ELSE
         SQL.STATUS = SQLExecute(SOSHIP.PARTS)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,SOSHIP.PARTS,SQL.STATE,DB.ERROR,SQL.MSG)
            MESSAGE = "Could not clear the PartsAllocation table for the
current user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
            SQL.OK = FALSE
         END                             ; * endif sql.status #
sql.success ....
      END                                ; * endif sql.status #
sql.success ....

      * clean up customer data
      SQL.STATUS = SQLFreeStmt(CUSTOMER.INS.STMT,SQL.CLOSE)
      SQL.STATUS = SQLPrepare(CUSTOMER.INS.STMT,"DELETE FROM Customer
NOWAIT where Port_No = ?")
      IF SQL.STATUS # SQL.SUCCESS THEN
         SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL
.MSG)
         MESSAGE = "Could not create the SQL Delete command for the
customer table, table not cleared!  "
         MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
         GOSUB ERROR.PROCESS
      END ELSE
         SQL.STATUS = SQLExecute(CUSTOMER.INS.STMT)
         IF SQL.STATUS # SQL.SUCCESS THEN
            SQL.ERR.STATUS =
SQLError(@HENV,SOSHIP.MDB.CONNECT,CUSTOMER.INS.STMT,SQL.STATE,DB.ERROR,SQL
.MSG)
            MESSAGE = "Could not clear the customer table for the current
user!  "
            MESSAGE := "SQL.STATE=":SQL.STATE:":  DB ERROR=":DB.ERROR:":
MESSAGE=":SQL.MSG
            GOSUB ERROR.PROCESS
         END                             ; * endif sql.status #
sql.success ....
      END                                ; * endif sql.status #
sql.success ....

      RETURN
* ===================================================================
MDB.CONNECT:* routine to open a connection to the SOSHIP data source

      SQL.STATUS = SQLConnect(SOSHIP.MDB.CONNECT,"SOSHIP",'','')
      IF SQL.STATUS <> SQL.SUCCESS THEN
         MESSAGE = "Could not (re)establish a connection to the Access
workspace: "
         MESSAGE := "Failed to connect to data source"
         GOSUB ERROR.PROCESS
         SQL.OK = FALSE
      END

      RETURN
* ===================================================================
ERROR.PROCESS:* error reporting loop


      RETURN
*
* ===================================================================
*
*
* <End>:
T
-------
u2-users mailing list
[EMAIL PROTECTED]
To unsubscribe please visit http://listserver.u2ug.org/

Reply via email to