Fixing phone numbers
An enhancement request I was assigned was worded thus:
“User will optionally enter the Phone number (IF the phone was blank the system will default the store’s area code).”
I interpret this to mean that the Customer Phone number (land line) field should remain optional, but if entered, it should check if the local area code had been entered, and if not, default it according to the local store’s area code. We can assume that the area code has already been entered if the phone number starts with a zero (0).
This is for a retail chain with stores throughout Australia and New Zealand, and the Apex session knows the operator’s store ID. I can look up the country code and phone number for their store with a simple query, which will return values such as (these are just made up examples):
Country AU, Phone: +61 8 9123 4567 – area code should be 08
Country AU, Phone: 08 91234567 – area code should be 08
Country AU, Phone: +61 2 12345678 – area code should be 02
Country AU, Phone: 0408 123 456 – no landline area code
Country NZ, Phone: +64 3 123456 – area code should be 03
Country NZ, Phone: 0423 456 121 – area code should be 04
They only want to default the area code for landlines, so if the store’s phone number happens to be a mobile phone number it should not do any defaulting.
Step 1: create a database function (in a database package, natch) to return the landline area code for any given store ID.
FUNCTION get_store_landline_area_code (p_store_id IN VARCHAR2) RETURN VARCHAR2 IS v_area_code VARCHAR2(2); v_country_code stores_vw.country_code%TYPE; v_telephone_number stores_vw.telephone_number%TYPE; BEGIN IF p_store_code IS NOT NULL THEN BEGIN SELECT country_code ,telephone_number INTO v_country_code ,v_telephone_number FROM stores_vw WHERE store_id = p_store_id; v_area_code := CASE -- Australian International land line WHEN p_country_code = 'AU' AND REGEXP_LIKE(p_telephone_number, '^\+61( ?)[2378]') --e.g. +61 8 9752 6100 THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1) -- Australian Local land line WHEN p_country_code = 'AU' AND REGEXP_LIKE(p_telephone_number, '^0[2378]') THEN SUBSTR(p_telephone_number, 1, 2) -- New Zealand International land line WHEN p_country_code = 'NZ' AND REGEXP_LIKE(p_telephone_number, '^\+64( ?)[34679]') -- e.g. +64 3 1234 567 THEN '0' || SUBSTR(REPLACE(p_telephone_number,' '), 4, 1) -- New Zealand Local land line WHEN p_country_code = 'NZ' AND REGEXP_LIKE(p_telephone_number, '^0[34679]') THEN SUBSTR(p_telephone_number, 1, 2) ELSE NULL END; EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN NULL; END; END IF; RETURN v_area_code; END get_store_landline_area_code;
Phone number references:
http://en.wikipedia.org/wiki/Telephone_numbers_in_Australia
http://en.wikipedia.org/wiki/Telephone_numbers_in_New_Zealand
Step 2: add a Dynamic Action to prepend the area code to the phone number, if it wasn’t entered already:
Event: Change
Selection Type: Item(s)
Item(s): P1_CUSTOMER_PHONE_NUMBER
Condition: Javascript expression
Value: $v("P1_CUSTOMER_PHONE_NUMBER").length > 0 && $v("P1_CUSTOMER_PHONE_NUMBER").charAt(0) != "0"
True Action: Set Value
Set Type: PL/SQL Expression
PL/SQL Expression: my_util_pkg.get_store_landline_area_code(:F_USER_STORE_ID) || :P1_CUSTOMER_PHONE_NUMBER
Now, when the user types in a local land line but forget the prefix, the system will automatically add it in as soon as they tab out of the field. If the phone number field is unchanged, or is left blank, this will do nothing.
It assumes that the customer’s phone number uses the same prefix as the store, which in most cases will be true. Ultimately the user will still need to check that the phone number is correct for the customer.