oracle 存储过程实例-1

create or replace
PROCEDURE misr016_RepaymentPlanSummary IS
/******************************************************************************
   NAME:       misr016_RepaymentPlanSummary
   PURPOSE:

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        2006-3-21   lulineng       1. Created this procedure.

   NOTES:
         only insert increment
         insert all tpa records
         then update the paid amount by caculating the CT transactions from PPR

******************************************************************************/

  TYPE TPA IS RECORD(
        ID       TMR016_REPAYMENTPLANSUMMARY.ID%TYPE,
        FREQ     TT05_TPA.CT05_INST_FREQ%TYPE,
        INST     TT05_TPA.CT05_NR_INST%TYPE,
        FIN      TT05_TPA.CR12_ACCOUNTS_NUMBER%TYPE,
        TAXYEAR  TT05_TPA.CT05_TAX_YEAR%TYPE,
        PRDFROM  TT05_TPA.CT05_PRD_FROM%TYPE,
        PRDTO    TT05_TPA.CT05_PRD_TO%TYPE
   );
  v_tpa          TPA;

TYPE vCurType  IS REF CURSOR;
  vSQL           VARCHAR2(1000);
  vPick          vCurType;

  v_Month         NUMBER(10,2);
  v_PaidAMT       NUMBER(19,2);

BEGIN

      --insert new tpa
      insert into tmr016_repaymentplansummary
        (id,
         cs05_office_id,
         cg02_tax_type,
         debt_amount,
         create_date,
         tpa_no)
        select mis_sequence.nextval,
               b.cs05_office_id,
               b.cg02_tax_type,
               a.ct05_tpa_amt,
               a.ct05_create_date,
               a.ct05_tpa_no
        from tt05_tpa a, tr12_accounts_tin b
        where a.cr12_accounts_number = b.cr12_accounts_number(+)
               and not exists (select 1
                 from tmr016_repaymentplansummary
                 where tpa_no = a.ct05_tpa_no)
               and a.cg03_tpa_status_cde = '12603';


      --reset paid amount to tpa down amount
      update tmr016_repaymentplansummary a
        set a.paid_amount = (select b.ct05_down_amt
                          from tt05_tpa b
                         where b.ct05_tpa_no = a.tpa_no);


      --calculate paid amount
      vSQL := 'select a.id,'
              ||' b.ct05_inst_freq,'
              ||' b.ct05_nr_inst,'
              ||' b.cr12_accounts_number,'
              ||' b.ct05_tax_year,'
              ||' b.ct05_prd_from,'
              ||' b.ct05_prd_to'
              ||' from tmr016_repaymentplansummary a, tt05_tpa b'
              ||' where a.tpa_no = b.ct05_tpa_no';
      open vPick for vSQL;
      loop
          FETCH vPick INTO v_tpa;
          EXIT WHEN vPick%NOTFOUND;

          --calculate months
          if v_tpa.FREQ = 1 then
             v_Month := v_tpa.INST * 0.25;
          elsif v_tpa.FREQ = 2 then
             v_Month := v_tpa.INST * 0.5;
          elsif v_tpa.FREQ = 4 then
             v_Month := v_tpa.INST * 3;
          else
             v_Month := v_tpa.INST;
          end if;

          --update month
          update tmr016_repaymentplansummary a set a.months = v_Month
            where a.id = v_tpa.ID;

          --get paid amount of this tpa
          select sum(a.ct01_amt) into v_PaidAMT
            from tt01_acct_transactions a
           where a.ct01_dt_ct_ind = 'CT'
             and a.ct01_trans_reversed <> 1
             and exists(select 1
                    from tt01_acct_transactions b
                   where b.cr12_fin=a.cr12_fin
                     and trunc(b.ct01_tax_year,'yyyy')=trunc(a.ct01_tax_year,'yyyy')
                     and b.ct01_prd_from=a.ct01_prd_from
                     and b.ct01_prd_to=a.ct01_prd_to
                     and b.cn07_liability_type_id=a.cn07_liability_type_id
                     --and b.ct01_pen_int_prd_start=a.ct01_pen_int_prd_start
                     --and b.ct01_pen_int_prd_end=a.ct01_pen_int_prd_end
                     --and b.ct01_pen_int_tax_year=a.ct01_pen_int_tax_year
                     and b.cr12_fin = v_tpa.FIN
                     and trunc(b.ct01_tax_year,'yyyy') = trunc(v_tpa.TAXYEAR,'yyyy')
                     and b.ct01_prd_from = v_tpa.PRDFROM
                     and b.ct01_prd_to = v_tpa.PRDTO
                     and b.ct01_dt_ct_ind = 'DT'
                     and b.ct01_trans_reversed <> 1);

          if v_PaidAMT is null then
             v_PaidAMT := 0;
          end if;

          --update paid amount
          update tmr016_repaymentplansummary a
          set a.paid_amount = a.paid_amount + v_PaidAMT
          where a.id = v_tpa.ID;

          commit;

     end loop;
      close vPick;

END misr016_RepaymentPlanSummary;