October 2005
Intermediate to advanced
454 pages
14h 44m
English
Finding genuine repeats is straightforward. If the order type matches the repeat type of a criteria record, and if a corresponding match is found in the associative array containing potential repeats, then we have found a genuine repeat. I’ve put this code into a sub-function, as well.
/*------------------------------------------------------------------*/
FUNCTION order_is_a_repeat ( p_location_id NUMBER,
p_type_id NUMBER,
p_date DATE )
RETURN NUMBER IS
/*------------------------------------------------------------------*/
v_hash NUMBER;
BEGIN
-- for every criteria...
FOR counter IN 1..v_criteria.LAST LOOP
-- if order type matches the repeat order type of a criteria
IF v_criteria(counter).repeat_type_id = p_type_id THEN
-- calculate a hash of the location, first and repeat order types
v_hash := DBMS_UTILITY.GET_HASH_VALUE(p_location_id || ':' ||
v_criteria(counter).first_type_id || ':' ||
v_criteria(counter).repeat_type_id,
-32767,65533);
-- if logged as a potential repeat then its safe to assume
-- we are repeating now
IF v_potential_repeat.EXISTS(v_hash) THEN
RETURN(v_hash);
END IF;
END IF; -- order type match
END LOOP; -- every criteria
RETURN(NULL);
END order_is_a_repeat;The algorithm is as follows. For every criterion for the region:
Does the order type of the order being processed match the repeat-order type of the criterion? If yes, then carry on.
Manufacture a hash value based on the location of the order, the first order type of the criterion, and ...