Check Web Diary Settings

SET serveroutput ON
DECLARE
  v_usingStaffProfiles BOOLEAN := <IsUsingStaffProfiles>;
  TYPE lt_list IS TABLE OF VARCHAR2 (30);
  l_diaryEwebList        lt_list
                       := lt_list ( 'SHOW RX',
                                   'DEFAULT APPT STATUS',
                                   'SHOW ORDER',
                                   'DISPLAY OPTICIAN LIST',
                                   'FULL DIARY INTEGRATION',
                                   'DISPLAY EXAM DETAILS',
                                   'MINIMUM DURATION'
                                  );
  l_diaryList lt_list
                := lt_list( 'WEB DIARY',
                            'WEB BOOKINGS'
                          );
  l_tableList lt_list
                := lt_list( 'SYSTEM_SETTINGS',
                            'PRACTICE_SETTINGS',
                            'STAFF_SETTINGS'
                          );
  v_editionType NUMBER := OCU_GLOBAL_VARIABLES.GET_EDITION;
  v_tokenCategory VARCHAR2(100);
  v_cnt NUMBER;
  v_hoPractice NUMBER := OCU_GLOBAL_VARIABLES.GET_HO_PRACTICE;
  v_staffSettingQuery VARCHAR2(2000);
  TYPE ResultSet IS REF CURSOR;
  CurStaff ResultSet;
  v_thisStaffMemberID NUMBER(16,0);
  v_errorFlagged BOOLEAN;
  FUNCTION SETTING_EXISTS( p_tableName IN VARCHAR2, p_practiceID IN NUMBER, p_tokenCategory IN VARCHAR2, p_tokenName IN VARCHAR ) RETURN BOOLEAN
  IS
    lv_cnt NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'SELECT count(*)'
      ||CHR(10)||'from '||p_tableName
      ||CHR(10)||'where practice_Id = '||TO_CHAR(p_practiceID)
      ||CHR(10)||'AND UPPER(TOKEN_CATEGORY) = '''||p_tokenCategory||''''
      ||CHR(10)||'AND UPPER(TOKEN_NAME) = '''||p_tokenName||''''
    INTO lv_cnt;
    IF lv_cnt = 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END SETTING_EXISTS;
  FUNCTION STAFF_SETTING_EXISTS( p_practiceID IN NUMBER, p_staffMemberID IN NUMBER, p_tokenCategory IN VARCHAR2, p_tokenName IN VARCHAR ) RETURN BOOLEAN
  IS
    lv_cnt NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'SELECT count(*)'
      ||CHR(10)||'from STAFF_SETTINGS'
      ||CHR(10)||'where practice_Id = '||TO_CHAR(p_practiceID)
      ||CHR(10)||'AND STAFF_MEMBER_ID = '||TO_CHAR(p_staffMemberID)
      ||CHR(10)||'AND UPPER(TOKEN_CATEGORY) = '''||p_tokenCategory||''''
      ||CHR(10)||'AND UPPER(TOKEN_NAME) = '''||p_tokenName||''''
    INTO lv_cnt;
    --DBMS_OUTPUT.PUT_LINE( 'Checking STAFF_SETTING '||p_tokenCategory||'>'||p_tokenName||' practice='||TO_CHAR(p_practiceID)||' staff='||TO_CHAR(p_staffMemberID)||' return='||TO_CHAR(v_cnt) );
    IF lv_cnt = 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END STAFF_SETTING_EXISTS;
BEGIN
  DBMS_OUTPUT.ENABLE(32767);
  IF v_editionType NOT IN (1,4) THEN
    RAISE_APPLICATION_ERROR( -20001, 'Settings can only be checked/adjusted in independent or Enterprise HO' );
  ELSE
    FOR PracRec IN (
      SELECT PRACTICE_ID
      FROM PRACTICES
      WHERE PRACTICE_ONLINE = 1
      ORDER BY PRACTICE_ID
    )
    LOOP
      DBMS_OUTPUT.PUT_LINE( CHR(10)||'Practice '||TO_CHAR(PracRec.PRACTICE_ID) );
      FOR i IN l_tableList.FIRST..l_tableList.LAST LOOP
        DBMS_OUTPUT.PUT_LINE( CHR(10)||'Table '||l_tableList(i) );
        v_errorFlagged := FALSE;

        IF l_tableList(i) = 'STAFF_SETTINGS' THEN
          IF v_usingStaffProfiles THEN
            v_staffSettingQuery := 'SELECT STAFF_TYPE_ID STAFF_MEMBER_ID'
              ||CHR(10)||'FROM RL_STAFF_TYPES'
              ||CHR(10)||'WHERE RECORD_DELETED = 0'
              ||CHR(10)||'ORDER BY STAFF_TYPE_ID';
          ELSE
            v_staffSettingQuery := 'SELECT STAFF_MEMBER_ID'
              ||CHR(10)||'FROM STAFF_MEMBERS'
              ||CHR(10)||'WHERE PRACTICE_ID = '||TO_CHAR(PracRec.PRACTICE_ID)
              ||CHR(10)||'AND RECORD_DELETED = 0'
              ||CHR(10)||'ORDER BY STAFF_MEMBER_ID';
          END IF;

          IF
            (NOT v_usingStaffProfiles)
            OR ( v_usingStaffProfiles AND v_hoPractice = PracRec.PRACTICE_ID AND v_editionType = 4)
            OR v_editionType = 1
          THEN
            OPEN CurStaff FOR v_staffSettingQuery;
            LOOP
              FETCH CurStaff INTO v_thisStaffMemberID;
              EXIT WHEN CurStaff%NOTFOUND;

              v_tokenCategory := 'DIARY';
              FOR j IN l_diaryList.FIRST..l_diaryList.LAST LOOP
                -- Diary > Web Bookings is the only setting to exist at staff level
                IF l_diaryList(j) = 'WEB BOOKINGS' THEN
                  IF NOT STAFF_SETTING_EXISTS( PracRec.PRACTICE_ID, v_thisStaffMemberID, v_tokenCategory, l_diaryList(j) ) THEN
                    DBMS_OUTPUT.PUT_LINE( 'Setting "'||v_tokenCategory||'>'||l_diaryList(j)||'" missing for staff '||TO_CHAR(v_thisStaffMemberID) );
                    v_errorFlagged := TRUE;
                  END IF;
                  EXIT;
                END IF;
              END LOOP;

            END LOOP;
            CLOSE CurStaff;
          ELSE
            DBMS_OUTPUT.PUT_LINE( '... doesnt apply');
          END IF; -- NOT (v_usingStaffProfiles AND v_hoPractice != PracRec.PRACTICE_ID AND v_editionType = 4) THEN
        ELSE
          IF l_tableList(i) = 'SYSTEM_SETTINGS' THEN
            v_tokenCategory := 'DIARY EWEB';
            FOR j IN l_diaryEwebList.FIRST..l_diaryEwebList.LAST LOOP
              IF NOT SETTING_EXISTS( l_tableList(i), PracRec.PRACTICE_ID, v_tokenCategory, l_diaryEwebList(j) ) THEN
                DBMS_OUTPUT.PUT_LINE( 'Setting "'||v_tokenCategory||'>'||l_diaryEwebList(j)||'" missing' );
                v_errorFlagged := TRUE;
              END IF;
            END LOOP;
          END IF;
          v_tokenCategory := 'DIARY';
          FOR j IN l_diaryList.FIRST..l_diaryList.LAST LOOP
            -- Diary > Web Bookings is the only setting to exist at practice level
            IF l_tableList(i) != 'PRACTICE_SETTINGS' OR l_diaryList(j) = 'WEB BOOKINGS' THEN
              IF NOT SETTING_EXISTS( l_tableList(i), PracRec.PRACTICE_ID, v_tokenCategory, l_diaryList(j) ) THEN
                DBMS_OUTPUT.PUT_LINE( 'Setting "'||v_tokenCategory||'>'||l_diaryList(j)||'" missing' );
                v_errorFlagged := TRUE;
              END IF;
            END IF;
          END LOOP;
        END IF; --l_tableList(i) = 'STAFF_SETTINGS' THEN

        IF NOT v_errorFlagged THEN
          DBMS_OUTPUT.PUT_LINE( '... OK' );
        END IF;

      END LOOP; -- TableRec
    END LOOP; -- PracRec
  END IF; -- end IF v_editionType NOT IN (1,4)
END;
/