shop_pkg.sql

PACKAGE BODY shop_pkg AS

  
  
  
  FUNCTION GET_CITY_BRANCHES
  ( 
    P_CITY           SHOPS.CITY%TYPE
  )
  RETURN SYS_REFCURSOR
  AS
    L_QUERY          VARCHAR2(32767);
    C_SHOPS          SYS_REFCURSOR;
  BEGIN
    L_QUERY := 'select ''Zip code &nbsp;&nbsp;&nbsp;'' || s.zip_code || ''<br />'' || 
                       ''Subway &nbsp;&nbsp;&nbsp;&nbsp;'' || s.subway || ''<br />'' || 
                       ''Address &nbsp;&nbsp;&nbsp;&nbsp;'' || s. street || '' '' || s.house || ''<br />'' || 
                       ''Tel. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'' || ''+7'' || '' ('' || s.phone_code || '') '' || 
                       regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 1) || ''-'' ||
                       regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 2) || ''-'' ||
                       regexp_substr(s.phone_number, ''(\d{3})(\d{2})(\d{2})'', 1, 1, ''i'', 3) || ''<br />'' || 
                       ''Timesheet &nbsp;'' || s.work_time_from || ''-'' || s.work_time_to || '' h'' as address
                  from shops s
                 where 1 = 1
                   and sysdate between nvl(start_date, sysdate - 1) and nvl(end_date, sysdate + 1)
                   and city = ''' || P_CITY || '''';
    DBMS_OUTPUT.PUT_LINE(L_QUERY);
    OPEN C_SHOPS FOR L_QUERY;
    RETURN C_SHOPS;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(EX, 'There are no active branches for the city :(');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_CITY_BRANCHES;
  
  
  
  
  FUNCTION GET_BRANCH_INFO
  (
    P_BRANCH_ID           SHOPS.ID%TYPE
  )
  RETURN VARCHAR2 
  AS
    L_BRANCH            VARCHAR2(32767);
  BEGIN
    SELECT 'Zip code &nbsp;&nbsp;&nbsp;' || S.ZIP_CODE || '<br />' ||
           'Metro &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' || S.SUBWAY || '<br />' ||
           'Address &nbsp;&nbsp;&nbsp;&nbsp;' || S. STREET || ' ' || S.HOUSE || '<br />' ||
           'Tel. &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' || '+7' || '(' || S.PHONE_CODE || ')' || 
           REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 1) || '-' ||
           REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 2) || '-' ||
           REGEXP_SUBSTR(S.PHONE_NUMBER, '(\d{3})(\d{2})(\d{2})', 1, 1, 'i', 3) || '<br />' ||
           'Timesheet &nbsp;' || S.WORK_TIME_FROM || '-' || S.WORK_TIME_TO AS ADDRESS
      INTO L_BRANCH
      FROM SHOPS S
     WHERE 1 = 1
       AND S.ID = P_BRANCH_ID
     ORDER BY 1;
    RETURN L_BRANCH;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(EX, 'There is no active branch with id ' || P_BRANCH_ID || ' :(');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END;
  
END SHOP_PKG;