upload_insert
The upload_insert script for CustDB is as follows:
INSERT INTO ULCustomerIDPool ( pool_cust_id )
VALUES( {ml r.pool_cust_id} )
upload_delete
The upload_delete script for CustDB is as follows:
DELETE FROM ULCustomerIDPool
WHERE pool_cust_id = {ml r.pool_cust_id}
end_upload
The following end_upload script ensures that after each upload 20 customer IDs remain in the customer ID pool:
CALL ULOrderIDPool_maintain( {ml s.username} )
The ULCustomerIDPool_maintain procedure for CustDB is as follows:
CREATE PROCEDURE ULCustomerIDPool_maintain ( IN syncuser_id INTEGER )
BEGIN
DECLARE pool_count INTEGER;
-- Determine how many ids to add to the pool
SELECT COUNT(*) INTO pool_count
FROM ULCustomerIDPool
WHERE pool_emp_id = syncuser_id;
-- Top up the pool with new ids
WHILE pool_count < 20 LOOP
INSERT INTO ULCustomerIDPool ( pool_emp_id )
VALUES ( syncuser_id );
SET pool_count = pool_count + 1;
END LOOP;
END