/* STRS.sql by Kyle Judson CLPCCD                         */
/* fy (fiscal year) 2008=2007-2008 FY                     */
/* rn (report number) 1=Jul, 2=Aug, ..., 11=May, 12=Jun   */
/* ex (existing dates) use pay period, 1st to 28/9,30,31  */
/* ch (change dates) use reporting period, 16th to 15th   */
define fy=2009
define rn=2
define ex_ser_beg=20080801
define ex_ser_end=20080831
define ch_ser_beg=20080716
define ch_ser_end=20080815
/* ------------------------------------------------------ */
/* See NOTE on last update (bottom) special case for July */
/* ------------------------------------------------------ */
update PDRF496
   set PDRF496_ASSN_CODE = 58
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and (PDRF496_ASSN_CODE is null or PDRF496_ASSN_CODE = 55)
/
select 'update assn code if null or 55, OK to proceed?' from dual;
update PDRF496
   set PDRF496_PAY_CODE = 4
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_PAY_CODE is null
/
select 'update pay code if null, OK to proceed?' from dual;
update PDRF496
   set PDRF496_CONT_CODE = 3
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and pdrf496_CONT_CODE = 0
/
select 'update cont code if 0, OK to proceed?' from dual;
update PDRF496
   set PDRF496_CONT_CODE = 3
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 58
   and PDRF496_MEMBER_CODE = 1
   and PDRF496_CONT_CODE = 1 -- i.e. <> 5
/
select 'update cont code, OK to proceed?' from dual;
select PDRF496_LAST_NAME name, PDRF496_SSN ssn,
       PDRF496_MEMBER_CODE mc, PDRF496_CONT_CODE cc
  from PDRF496
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 61
   and PDRF496_CONT_CODE not in (5,6)
   and (PDRF496_MEMBER_CODE<>2 or PDRF496_CONT_CODE<>3)
 order by name;
select 'if mc 2, cc 1 OK, otherwise notify Lori' from dual;
update PDRF496
   set PDRF496_MEMBER_CODE = 2, PDRF496_CONT_CODE = 3
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 61
   and PDRF496_CONT_CODE not in (5,6)
   and (PDRF496_MEMBER_CODE<>2 or PDRF496_CONT_CODE<>3)
/
select 'update member, cont code, OK to proceed?' from dual;
select PDRF496_LAST_NAME name, PDRF496_SSN ssn,
       PDRF496_MEMBER_CODE mc, PDRF496_ASSN_CODE ac,
       PDRF496_PAY_CODE pc, PDRF496_CONT_CODE cc,
       PDRF496_EMPL_PRETAX_AMT pretax_amt
  from PDRF496
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and (PDRF496_ASSN_CODE is null or PDRF496_ASSN_CODE = 58)
   and PDRF496_EMPL_PRETAX_AMT = 0
   and PDRF496_MEMBER_CODE<>2
/
select 'report anyone listed to Lori (0 pre-tax)' from dual;
select PDRF496_LAST_NAME name, PDRF496_SSN ssn,
       PDRF496_PAY_RATE pay_rate
  from PDRF496
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 55
   and PDRF496_MEMBER_CODE = 1
   and PDRF496_CONT_CODE<>9
   and PDRF496_PAY_RATE between 40 and 75
-- and PDRF496_PAY_RATE in (42.52,44.51,46.48,48.46,50.48,52.40)
order by 3, 2
/
select 'report anyone listed to Lori (pay rate?)' from dual;
select PDRF496_LAST_NAME name, PDRF496_SSN ssn,
       PDRF496_PAY_RATE pay_rate, PDRF496_SERVICE_BEGIN ser_beg,
       substr(PDRF496_USERID,1,9) user_id, PDRF496_MEMBER_CODE mc
  from PDRF496
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 57
   and PDRF496_PAY_CODE = 1
   and PDRF496_PAY_RATE<100
 order by PDRF496_LAST_NAME, PDRF496_SSN
/
select 'report anyone listed to Lori (various)' from dual;
select PDRF496_LAST_NAME name, PDRF496_SSN ssn,
       'Summer AC -- PROBLEM!!!'
  from PDRF496
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE = 59
/
select 'report anyone listed to Lori (Summer AC)' from dual;
update PDRF496
/* ============================================================ */
/* NOTE: for July, use 06/16 - 06/30 for REPORT NO 1, new year! */
/* need to change end date, leave CONT_CODE as 3 (prior period) */
/* ------------------------------------------------------------ */
/* >> Lori prefers above method, Eleanor preferred method below */
/* ------------------------------------------------------------ */
/*    OR: use 07/01 - 07/15 and CONT_CODE as 1 (current period) */
/*        need to change begin date to 07/01 and CONT_CODE to 1 */
/* ============================================================ */
--   set PDRF496_SERVICE_END = 20070630
--   set PDRF496_SERVICE_BEGIN = 20070701, PDRF496_CONT_CODE = 1
   set PDRF496_SERVICE_BEGIN = &ch_ser_beg,
       PDRF496_SERVICE_END = &ch_ser_end
 where PDRF496_FISC_YEAR = &fy
   and PDRF496_REPORT_NO = &rn
   and PDRF496_ASSN_CODE in (55,58,61)
   and PDRF496_CONT_CODE<>5
   and PDRF496_SERVICE_BEGIN = &ex_ser_beg
   and PDRF496_SERVICE_END = &ex_ser_end
/* Specific exclusions (i.e. people you do not want to change)  */
--   and PDRF496_PIDM not in (10187650,10199114)
/
select 'update dates, OK to exit?' from dual;
exit