CREATE OR REPLACE PROCEDURE SP_SEARCH_CUSTOMERS_DATA (
              lastName IN NVARCHAR2,
              cur_OUT IN OUT SYS_REFCURSOR,
              filterAltAccountOnly IN INTEGER := 0
)

  IS
    lastNameValue NVARCHAR2(45);
    limitRows NUMBER := 100;
    preferredAltIDFormat VARCHAR2(30) := '7_____________';

    -- Debug variables
    stTime timestamp;
    BegTime NUMBER := 0;
    EndTime NUMBER := 0;
    ElapsedTime NUMBER := 0; 

  BEGIN

    -- Populate the start time
    stTime := systimestamp;
    BegTime := dbms_utility.get_time+power(2,32)/100;


    -- Initialize variables!
    lastNameValue   := NULL;


    IF lastName IS NOT NULL THEN
      lastNameValue := UPPER(lastName);
    END IF;

   IF lastNameValue IS NOT NULL THEN
     OPEN cur_OUT FOR
        SELECT DISTINCT
        MV_CUSTOMER.CUSTOMER_ID AS PREFERRED_CUST_KEY,
        COALESCE(
          (
            SELECT
              MIN(Alt_Acct_Num)
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          ),
          NULL -- MV_CUSTOMER.CUSTOMER_ACCT_NUM
        ) AS CUST_ID,
        MV_CUSTOMER.LAST_NAME AS CUST_LST_NM,
        MV_CUSTOMER.FIRST_NAME AS CUST_FRST_NM,
        CUSTOMER_HOME_PHONE.PHONE_NUM AS HOME_PHONE,
        CUSTOMER_WORK_PHONE.PHONE_NUM AS WORK_PHONE,
        MV_CUSTOMER.POSTAL_CD AS CUST_ADDR_ZIP
       FROM
        MV_CUSTOMER
        LEFT OUTER JOIN PHONE CUSTOMER_HOME_PHONE
          ON CUSTOMER_HOME_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_HOME_PHONE.PHONE_TYPE_ID = 1
        LEFT OUTER JOIN PHONE CUSTOMER_WORK_PHONE
          ON CUSTOMER_WORK_PHONE.CUSTOMER_ID = MV_CUSTOMER.CUSTOMER_ID
          AND CUSTOMER_WORK_PHONE.PHONE_TYPE_ID = 2
        WHERE (MV_CUSTOMER.LAST_NAME = UPPER(lastName) OR MV_CUSTOMER.LAST_NAME = lastNameValue)
        AND (filterAltAccountOnly = 0 OR EXISTS (
            SELECT
              *
            FROM
              Additional_Account
            WHERE
              Additional_Account.Customer_ID = mv_Customer.Customer_ID
            AND
              Alt_Acct_Num <> mv_Customer.Customer_Acct_Num
            AND
              Alt_Acct_Num LIKE preferredAltIDFormat
          )
        )
        AND Is_Deleted = 0
        AND ROWNUM <= limitRows;
   END IF;

    EndTime := dbms_utility.get_time;
    ElapsedTime := round((EndTime - BegTime)/100, 2);

    -- Populate elapsed time and parameters
    INSERT INTO capture_cust_sp_search_params
    (startTime, elapTime, paramString)
    VALUES
    (stTime, ElapsedTime, ' lName: ' || lastName);

END SP_SEARCH_CUSTOMERS_DATA;
/