/*******************************************************************/
/*                  EQUATION AID YEAR ROLL-OVER                    */
/*******************************************************************/
DECLARE
  newAidYearCode CHAR(1) := 'F'; -- Even
  oldAidYearCode CHAR(1) := 'G'; -- Odd
  
  newEffDt TIMESTAMP := TRUNC(SYSDATE);

BEGIN
  
-- Delete Equations
  DELETE FROM PS_EQUATION_TBL   WHERE EQUATION_NAME LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQUATION_DTL   WHERE EQUATION_NAME LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQTN_TEST_DATA WHERE EQUATION_NAME LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQUATION_PCODE WHERE EQUATION_NAME LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_SCC_APPL_PRMPT WHERE EQUATION_NAME LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQTN_SQL_TBL WHERE EQTN_OPERAND_SQL LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQTN_SQL_CHUNKS WHERE EQTN_OPERAND_SQL LIKE 'FQ' || newAidYearCode || '%';
  DELETE FROM PS_EQTN_SQ_SQLAUTH WHERE EQTN_OPERAND_SQL LIKE 'FQ' || newAidYearCode || '%';

-- Insert Equations
  INSERT INTO sysadm.ps_equation_tbl
    (equation_name, effdt, eff_status, eqtn_row_id, eqtn_compile_stat, eqtn_err_linenum, message_nbr, eqtn_err_parmcount, message_parm1, message_parm2, message_parm3, message_parm4, descr, descrshort, sccps_enable_prvw, scc_data_type_cd)
  SELECT REPLACE(equation_name,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), newEffDt, eff_status, eqtn_row_id, 'NYET', eqtn_err_linenum, message_nbr, eqtn_err_parmcount, message_parm1, message_parm2, message_parm3, message_parm4, DECODE(newAidYearCode,'G',REPLACE(descr,'Even','Odd'),REPLACE(descr,'Odd','Even')), DECODE(newAidYearCode,'G',REPLACE(descrshort,'Even','Odd'),REPLACE(descrshort,'Odd','Even')), sccps_enable_prvw, scc_data_type_cd from sysadm.ps_equation_tbl
  WHERE equation_name LIKE 'FQ' || oldAidYearCode || '%';

  INSERT INTO sysadm.ps_equation_dtl
    (equation_name, effdt, eqtn_row_id, eqtn_row_seq, equation_keyword, eqtn_operand_type, eqtn_operand_str, eqtn_operand_num, eqtn_operand_dt, recname, fieldname, eqtn_operand_val, eqtn_operand_eqtn, eqtn_operand_xtrn, eqtn_operand_loc, eqtn_operand_glb, eqtn_operand_appl, eqtn_operand_sql, eqtn_comment, scc_data_type_cd)
  SELECT REPLACE(equation_name,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), newEffDt, eqtn_row_id, eqtn_row_seq, equation_keyword, eqtn_operand_type, eqtn_operand_str, eqtn_operand_num, eqtn_operand_dt, recname, fieldname, eqtn_operand_val, eqtn_operand_eqtn, eqtn_operand_xtrn, eqtn_operand_loc, eqtn_operand_glb, eqtn_operand_appl, eqtn_operand_sql, eqtn_comment, scc_data_type_cd from sysadm.ps_equation_dtl
  WHERE equation_name LIKE 'FQ' || oldAidYearCode || '%';
  
  INSERT INTO sysadm.ps_eqtn_test_data
  (equation_name, eqtn_operand_glb, eqtn_global_type, eqtn_operand_num, eqtn_operand_str, eqtn_operand_dt)
  SELECT REPLACE(equation_name,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), eqtn_operand_glb, eqtn_global_type, eqtn_operand_num, eqtn_operand_str, eqtn_operand_dt from sysadm.ps_eqtn_test_data
  WHERE equation_name LIKE 'FQ' || oldAidYearCode || '%';
  
  INSERT INTO sysadm.ps_scc_appl_prmpt
  (equation_name, scc_appl_prompt_id, scc_data_type_cd)
  SELECT REPLACE(equation_name,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), scc_appl_prompt_id, scc_data_type_cd from sysadm.ps_scc_appl_prmpt
  WHERE equation_name LIKE 'FQ' || oldAidYearCode || '%';
  
  INSERT INTO sysadm.PS_EQTN_SQL_TBL
  (EQTN_OPERAND_SQL, EFFDT, EFF_STATUS, DESCR50, SCC_DATA_TYPE_CD, EQTN_SQL_TEXT)
  SELECT REPLACE(EQTN_OPERAND_SQL,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), EFFDT, EFF_STATUS, DESCR50, SCC_DATA_TYPE_CD, EQTN_SQL_TEXT 
  FROM sysadm.PS_EQTN_SQL_TBL
  WHERE EQTN_OPERAND_SQL LIKE 'FQ' || oldAidYearCode || '%';
  
  INSERT INTO sysadm.PS_EQTN_SQL_CHUNKS
  (EQTN_OPERAND_SQL, EFFDT, EQTN_SQL_CHUNK_SEQ, EQTN_SQL_CHUNK, SCC_DATA_TYPE_CD)
  SELECT REPLACE(EQTN_OPERAND_SQL,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), EFFDT, EQTN_SQL_CHUNK_SEQ, EQTN_SQL_CHUNK, SCC_DATA_TYPE_CD
  FROM sysadm.PS_EQTN_SQL_CHUNKS
  WHERE EQTN_OPERAND_SQL LIKE 'FQ' || oldAidYearCode || '%';
  
  INSERT INTO sysadm.PS_EQTN_SQ_SQLAUTH
  (EQTN_SQ_AUTH_CLASS, EQTN_OPERAND_SQL, EQTN_AUTH_LEVEL)
  SELECT EQTN_SQ_AUTH_CLASS, REPLACE(EQTN_OPERAND_SQL,'FQ'||oldAidYearCode,'FQ'||newAidYearCode), EQTN_AUTH_LEVEL
  FROM sysadm.PS_EQTN_SQ_SQLAUTH
  WHERE EQTN_OPERAND_SQL LIKE 'FQ' || oldAidYearCode || '%';
 
 -- commit;

END;
