CREATE OR REPLACE PROCEDURE UPDATE_POPS AS
  CURSOR cur_trans_1 IS
    select ent,
           ctr_or_hier,
           post_acct,
           sum(avg_mtd_01) plan_01,
           sum(avg_mtd_02) plan_02,
           sum(avg_ytd_01) plan_03,
           sum(avg_ytd_02) plan_04
      from mon_act_cypy
     where rec_type = 'AAA' and sum_flag = 'DDD' and yr = '2008' and
           substr(ctr_or_hier, 1, 2) = 'll' and
           ent || sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999999')
     group by post_acct
    having sum(avg_mtd_01) <> 0 or sum(avg_mtd_02) <> 0 or sum(avg_ytd_01) <> 0 or sum(avg_ytd_02) <> 0;

  CURSOR cur_trans_2 IS
    select b.plan_ent b_plan_ent,
           b.plan_ctr b_plan_ctr,
           a.post_acct a_post_acct,
           sum(a.avg_mtd_01) plan_01,
           sum(a.avg_mtd_02) plan_02,
           sum(a.avg_ytd_01) plan_03,
           sum(a.avg_ytd_02) plan_04
      from mon_act_cypy a, plan_unit_tbl b
     where a.ent || a.ctr_or_hier = b.ent || b.ctr_or_hier and
           a.rec_type = 'AAA' and a.sum_flag = 'DDD' and a.yr = '2008' and
           b.hier_tbl_num = '991' and
           a.ent || a.sub_ent in
           (select ent || sub_ent
              from ent_ref
             where roll_ent || roll_sub_ent = '999999')
     group by b.plan_ent, b.plan_ctr, a.post_acct
    having sum(a.avg_mtd_01) <> 0 or sum(a.avg_mtd_02) <> 0 or sum(a.avg_ytd_01) <> 0 or sum(a.avg_ytd_02) <> 0;

BEGIN
  UPDATE MON_PLAN
     SET PLAN_01 = 0, PLAN_02 = 0, PLAN_03 = 0, PLAN_04 = 0
   WHERE plan_type = 'update_pops' AND yr = '2008';

  FOR rec_trans_1 IN cur_trans_1 LOOP
    UPDATE mon_plan
       SET PLAN_01 = rec_trans_1.plan_01,
           PLAN_02 = rec_trans_1.plan_02,
           PLAN_03 = rec_trans_1.plan_03,
           PLAN_04 = rec_trans_1.plan_04
     WHERE EXISTS (Select 1
              from mon_plan
             where ent = rec_trans_1.ent AND
                   CTR_OR_HIER = rec_trans_1.ctr_or_hier AND
                   post_acct = rec_trans_1.post_acct);
    IF SQL%NOTFOUND THEN
      INSERT INTO mon_plan
      VALUES
        (rec_trans_1.ent,
         rec_trans_1.post_acct,
         rec_trans_1.ctr_or_hier,
         rec_trans_1.plan_01,
         rec_trans_1.plan_02,
         rec_trans_1.plan_03,
         rec_trans_1.plan_04);
    END IF;
  END LOOP;

  FOR rec_trans_2 IN cur_trans_2 LOOP
    UPDATE mon_plan
       SET PLAN_01 = rec_trans_2.plan_01,
           PLAN_02 = rec_trans_2.plan_02,
           PLAN_03 = rec_trans_2.plan_03,
           PLAN_04 = rec_trans_2.plan_04
     WHERE EXISTS (Select 1
              from mon_plan
             WHERE ent = rec_trans_2.b_plan_ent AND
                   CTR_OR_HIER = rec_trans_2.b_plan_ctr AND
                   post_acct = rec_trans_2.a_post_acct);
    IF SQL%NOTFOUND THEN
      INSERT INTO mon_plan
      VALUES
        (rec_trans_2.b_plan_ent,
         rec_trans_2.a_post_acct,
         rec_trans_2.b_plan_ctr,
         rec_trans_2.plan_01,
         rec_trans_2.plan_02,
         rec_trans_2.plan_03,
         rec_trans_2.plan_04);
    END IF;
  END LOOP;

  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    error_logs(SYSDATE, SQLCODE, SQLERRM);
END;