Cacher is the code snippet organizer for pro developers

/*STEP 1 - Copy all profiles from HRMS upload to magnet profile table*/
INSERT
INTO MAGNET_PROFILES
  (
    UNIT_ID,
    EMPLOYEE_ID,
    FIRST_NAME,
    LAST_NAME,
    DOMAIN_ID,
    MIN_WKLY_HRS,
    MSS_UNIT_STRT_DT,
    SMH_STRT_DT,
    POS_CODE,
    TITLE,
    DEPT,
    DAY_BREAK,
    UR_ID,
    POS_STRT_DATE,
    UNIT_STRT_DATE
  )
SELECT UNIT_ID,
  EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  lower(SUBSTR(first_name,1,1))
  || lower(last_name) AS DOMAIN_ID,
  MIN_WKLY_HRS,
  MSS_UNIT_STRT_DT,
  SMH_STRT_DT,
  POS_CODE,
  TITLE,
  DEPT,
  DAY_BREAK,
  UR_ID,
  POS_STRT_DATE,
  UNIT_STRT_DATE
FROM GET_EMPL_LIST
WHERE GET_EMPL_LIST.SMH_STRT_DT <= '01-JUL-14 00:00:00';
--4566 rows inserted

--update user 157660 to 1 FTE
UPDATE
  ( SELECT * FROM magnet_profiles WHERE employee_id = 157660
  )
SET min_wkly_hrs = 40;

--delete all secondary appointments and < .1FTE folks ( < 4hrs/wk)
DELETE
  (SELECT *
  FROM magnet_profiles
  WHERE (pos_code      = 2
  OR min_wkly_hrs      < 4)
  AND employee_id NOT IN (168702,157660)
  );
  
-- Find all persons in MSS that are NOT in the MAGNET_PROFILES Table
SELECT DISTINCT --employee_id, last_name, first_name, pos_code, title
  SUBSTR(dept, 4,7) as unit_id,
  employee_id,
  first_name,
  last_name,
  domain_id,
  min_wkly_hrs,
  mss_unit_strt_dt,
  smh_strt_dt,
  pos_code,
  title,
  dept,
  day_break,
  ur_id,
  POS_STRT_DATE,
  UNIT_STRT_DATE
FROM employee_profile
WHERE smh_term_dt    IS NULL
AND mss_unit_term_dt IS NULL
AND unit_id          <> 'test'
AND min_wkly_hrs     >= 4
AND smh_strt_dt      <= '01-JUL-14 00:00:00'
AND POS_CODE         IN (select distinct pos_code from MAGNET_PROFILES)
AND employee_id NOT  IN
  (SELECT DISTINCT employee_id FROM magnet_profiles
  )
AND (MSS_UNIT_TERM_DT <= '01-JUL-14'
OR MSS_UNIT_TERM_DT   IS NULL)
AND SMH_TERM_DT       IS NULL
AND DEPT LIKE '500%'
ORDER BY EMPLOYEE_ID ASC,
  LAST_NAME ASC,
  FIRST_NAME ASC,
  POS_CODE ASC,
  LAST_NAME ASC;
--export names as "INSERT" into table "MAGNET_PROFILES" and then re-run this.

--update inserted folks to have unit_id equal right 3 digits of dept
UPDATE
  (SELECT dept,
    unit_id,
    ur_id,
    SUBSTR(DEPT, 4) AS PROPOSED,
    employee_id,
    last_name,
    first_name,
    pos_code,
    title,
    min_wkly_hrs/40 AS "FTE"
  FROM magnet_profiles
  WHERE DEPT NOT LIKE '%'
    || UNIT_ID
    || '%'
  )
SET UNIT_ID = SUBSTR(DEPT, 4);
--0 rows updated

/*
 * delete all folks who are not 168702 or 157660,
 * as well as folks that are < .1 FTE
*/
DELETE
  (SELECT *
  FROM magnet_profiles
  WHERE ( min_wkly_hrs       < 4 )
  AND employee_id NOT  IN (168702,157660)
  ); --0 rows deleted
  
--display any duplicate profiles (removed undesired ones)
SELECT employee_id
FROM magnet_profiles
GROUP BY employee_id
HAVING COUNT(*) > 1;

--count distinct entries
SELECT COUNT(DISTINCT employee_id)
FROM magnet_profiles; --4285 rows returned