download_cursor   The first parameter in the download_cursor script is the last download timestamp. It is used to ensure that only rows that have been modified on either the remote or the consolidated database since the last synchronization are downloaded. The second parameter is the employee ID. It is used to determine which rows to download.

The download_cursor script for CustDB is as follows:

CALL ULOrderDownload( {ml s.last_table_download}, {ml s.username} )

The ULOrderDownload procedure for CustDB is as follows:

CREATE PROCEDURE ULOrderDownload ( IN LastDownload timestamp, IN EmployeeID integer )
BEGIN`
  SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status
    FROM ULOrder o, ULEmpCust ec
    WHERE o.cust_id = ec.cust_id
    AND ec.emp_id = EmployeeID
    AND ( o.last_modified >= LastDownload
    OR ec.last_modified >= LastDownload)
    AND ( o.status IS NULL  OR  o.status != 'Approved' )
    AND ( ec.action IS NULL )
END

download_delete_cursor   The download_delete_cursor script for CustDB is as follows:

SELECT o.order_id, o.cust_id, o.prod_id, o.emp_id, o.disc, o.quant, o.notes, o.status FROM ULOrder o, dba.ULEmpCust ec WHERE o.cust_id = ec.cust_id AND ( ( o.status = ''Approved'' AND o.last_modified >= {ml s.last_table_download} ) OR ( ec.action = ''D'' ) ) AND ec.emp_id = {ml s.username}

upload_insert   The upload_insert script for CustDB is as follows:

INSERT INTO ULOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

upload_update   The upload_update script for CustDB is as follows:

UPDATE ULOrder SET cust_id = {ml r.cust_id}, prod_id = {ml r.prod_id}, emp_id = {ml r.emp_id}, disc = {ml r.disc}, quant = {ml r.quant}, notes = {ml r.notes}, status = {ml r.status} WHERE order_id = {ml r.order_id}

upload_delete   The upload_delete script for CustDB is as follows:

DELETE FROM ULOrder WHERE order_id = {ml r.order_id}

upload_fetch   The upload_fetch script for CustDB is as follows:

SELECT order_id, cust_id, prod_id, emp_id, disc, quant, notes, status FROM ULOrder WHERE order_id = {ml r.order_id}

upload_old_row_insert   The upload_old_row_insert script for CustDB is as follows:

INSERT INTO ULOldOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

upload_new_row_insert   The upload_new_row_insert script for CustDB is as follows:

INSERT INTO ULNewOrder ( order_id, cust_id, prod_id, emp_id, disc, quant, notes, status ) VALUES( {ml r.order_id, r.cust_id, r.prod_id, r.emp_id, r.disc, r.quant, r.notes, r.status } )

resolve_conflict   The resolve_conflict script for CustDB is as follows:

CALL ULResolveOrderConflict

The ULResolveOrderConflict procedure for CustDB is as follows:

CREATE PROCEDURE ULResolveOrderConflict()
BEGIN
  -- approval overrides denial
  IF 'Approved' = (SELECT status FROM ULNewOrder) THEN
    UPDATE ULOrder o
    SET o.status = n.status, o.notes = n.notes
    FROM ULNewOrder n
    WHERE o.order_id = n.order_id;
  END IF;
  DELETE FROM ULOldOrder;
  DELETE FROM ULNewOrder;
END