计提

CREATE OR REPLACE
procedure proc_contract_rent_month_test is
begin
  declare
       p_tax_rate number(22,2) default 1.06; -- 税率
       p_hand_formula varchar2(200 char) default 'ROUND(应收利息/'||p_tax_rate||'-ROUND(上个月剩余本金*年利率/100/360,2)*(上个月最后一天-上个月计划日期)/'||p_tax_rate||'+ROUND(这个月剩余本金*年利率/100/360,2)*(这个月最后一天-这个月计划日期)/'||p_tax_rate||',2)'; -- 计提公式
       p_interest number; -- 这期上半部分计提（扣税）
       n_interest number; -- 上期下半部分计提（扣税） 
             p_all_interest number;
       n_formula varchar2(200 char); -- 上期下半部分公式
       l_formula varchar2(200 char); -- 这期下半部分公式

       begin
         for contract_info in
             (select lcc.contract_id p_contract_id,lcc.payment_number p_payment_number,
                     nvl(lr.interest,0) p_all_interest,lcc.handling_charge_money/1.06 p_handling_charge_money,
                     (nvl(lcc.DEALER_DISCOUNT,0)+NVL(lcc.CASH_DISCOUNT_ITEM,0))/1.06 p_discount_money,
                     lr.last_date p_last_date,
                     lcc.clean_lease_money p_remain_corpus,lcc.start_date p_prev_plan_date,lcc.year_rate p_year_rate,lcc.year_rate/100/360 p_day_rate,
                     decode(sign((round(lcc.clean_lease_money*(lcc.year_rate/100/360),2)*(last_day(to_date(lcc.start_date,'yyyy/mm/dd'))-to_date(lcc.start_date,'yyyy/mm/dd')))-0),1,lcc.clean_lease_money*(lcc.year_rate/100/360)*(last_day(to_date(lcc.start_date,'yyyy/mm/dd'))-to_date(lcc.start_date,'yyyy/mm/dd')),0) p_month_interest,lcc.INCOME_NUMBER income_number
              from lc_calc_condition lcc left join lb_contract_info lci on lci.id = lcc.contract_id
              left join (select contract_id,sum(interest/1.06) interest,max(plan_date) last_date from lc_rent_plan group by contract_id) lr on lr.contract_id = lcc.contract_id
              where not exists (select 1 from contract_rent_month where contract_id = lcc.contract_id) and nvl(lr.interest,0) >= 0 and lci.contract_status between 31 and 100 and lci.id not in  (select contract_id from lc_fund_rent_adjust)) loop
         
                 declare
          al_apport_handling_money number(22,2) default 0.00;--已摊销手续费
          al_apport_discount_money number(22,2) default 0.00;--已摊销贴息
          rent_time_limit number(3) default 0;--租赁期限
          hc_money number(22,2) default 0.00;--手续费
          dis_money number(22,2) default 0.00;--贴息
           begin
             rent_time_limit:=contract_info.income_number;
             n_interest := contract_info.p_month_interest;
             -- 创建起租日当月的计提
             n_formula := 'ROUND(ROUND('||contract_info.p_remain_corpus||'*'||contract_info.p_year_rate||'/100/360,2)*('||to_char(last_day(to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd')),'yyyy/mm/dd')||'-'||contract_info.p_prev_plan_date
                          ||')/'||p_tax_rate||',2)';
            if contract_info.p_all_interest>0 then
                        
                        
             insert into contract_rent_month(id,interest,fee,discount,accrued_date,start_date,end_date,
                                             formula,hand_formula,create_date,contract_id,payment_number)
             values(sys_guid(),round(n_interest/p_tax_rate,2),round(decode(sign(contract_info.p_handling_charge_money*round(n_interest/p_tax_rate,2)-0.00001),1,contract_info.p_handling_charge_money*(n_interest/p_tax_rate)/contract_info.p_all_interest,-1,
             (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*CONTRACT_INFO.P_HANDLING_CHARGE_MONEY,0),2),round(decode(sign(contract_info.p_discount_money*round(n_interest/p_tax_rate,2)-0.00001),1,contract_info.p_discount_money*(n_interest/p_tax_rate)/contract_info.p_all_interest,-1,(last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*CONTRACT_INFO.p_discount_money,0),2),
                    to_char(to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'),'yyyy/mm'),to_char(trunc(to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'),'mm'),'yyyy/mm/dd'),to_char(last_day(to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd')),'yyyy/mm/dd'),n_formula
                    ,'ROUND(ROUND(这个月剩余本金*年利率/100/360,2)*(这个月最后一天-这个月计划日期)/'||p_tax_rate||',2)',to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),contract_info.p_contract_id,contract_info.p_payment_number);

             al_apport_handling_money:=al_apport_handling_money+round((case when sign(contract_info.p_handling_charge_money*round(n_interest/p_tax_rate,2)-0.00001)=1 then  contract_info.p_handling_charge_money*(n_interest/p_tax_rate)/contract_info.p_all_interest WHEN sign(contract_info.p_handling_charge_money*round(n_interest/p_tax_rate,2)-0.00001)=-1 THEN (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*CONTRACT_INFO.P_HANDLING_CHARGE_MONEY else 0 end),2);

             al_apport_discount_money:=al_apport_discount_money+round((case when sign(contract_info.p_discount_money*round(n_interest/p_tax_rate,2)-0.00001)=1 then  contract_info.p_discount_money*(n_interest/p_tax_rate)/contract_info.p_all_interest WHEN sign(contract_info.p_discount_money*round(n_interest/p_tax_rate,2)-0.00001)=-1 THEN (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*contract_info.p_discount_money else 0 end),2);

             n_formula := 'ROUND('||contract_info.p_remain_corpus||'*'||contract_info.p_year_rate||'/100/360,2)*('||to_char(last_day(to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd')),'yyyy/mm/dd')||'-'||contract_info.p_prev_plan_date
                          ||')/'||p_tax_rate;
             for rent_info in
               (select * from lc_rent_plan where contract_id = contract_info.p_contract_id order by contract_id,plan_list) loop
               begin
                 p_interest := ((case when rent_info.interest = 0 then 0 else rent_info.interest end)-n_interest)/p_tax_rate;
                 n_interest := case when round(rent_info.all_remain_corpus*contract_info.p_day_rate,2)*(last_day(to_date(rent_info.plan_date,'yyyy/mm/dd'))-to_date(rent_info.plan_date,'yyyy/mm/dd')) = 0 then 0 else (rent_info.all_remain_corpus*contract_info.p_day_rate)*(last_day(to_date(rent_info.plan_date,'yyyy/mm/dd'))-to_date(rent_info.plan_date,'yyyy/mm/dd')) end;
                 l_formula := 'ROUND('||rent_info.all_remain_corpus||'*'||contract_info.p_year_rate||'/100/360,2)*('||to_char(last_day(to_date(rent_info.plan_date,'yyyy/mm/dd')),'yyyy/mm/dd')||'-'||rent_info.plan_date
                              ||')/'||p_tax_rate;
                 if  rent_info.plan_list=rent_time_limit then
                   hc_money:=contract_info.p_handling_charge_money-al_apport_handling_money;
                   dis_money:=contract_info.p_discount_money-al_apport_discount_money;
                   else hc_money:=round((case when sign(contract_info.p_handling_charge_money*round(p_interest+n_interest,2)-0.00001)=1 then  contract_info.p_handling_charge_money*(p_interest+n_interest/p_tax_rate)/contract_info.p_all_interest WHEN sign(contract_info.p_handling_charge_money*round(p_interest+n_interest,2)-0.00001)=-1 THEN (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*CONTRACT_INFO.P_HANDLING_CHARGE_MONEY else 0 end),2);
                   dis_money:=round((case when sign(contract_info.p_discount_money*round(p_interest+n_interest,2)-0.00001)=1 then  contract_info.p_discount_money*(p_interest+n_interest/p_tax_rate)/contract_info.p_all_interest WHEN sign(contract_info.p_discount_money*round(p_interest+n_interest,2)-0.00001)=-1 THEN (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*contract_info.p_discount_money else 0 end),2);
                   end if;
                 insert into contract_rent_month(id,interest,fee,discount,accrued_date,start_date,end_date,
                                                 formula,hand_formula,create_date,contract_id,payment_number)
                 values(sys_guid(),round(p_interest+n_interest/p_tax_rate,2),hc_money,dis_money,
                        to_char(to_date(rent_info.plan_date,'yyyy/mm/dd'),'yyyy/mm'),to_char(trunc(to_date(rent_info.plan_date,'yyyy/mm/dd'),'mm'),'yyyy/mm/dd'),to_char(last_day(to_date(rent_info.plan_date,'yyyy/mm/dd')),'yyyy/mm/dd'),
                        'ROUND('||rent_info.interest||'/'||p_tax_rate||'-'||n_formula||'+'||l_formula||',2)'
                        ,p_hand_formula,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),contract_info.p_contract_id,contract_info.p_payment_number);

                 n_formula := l_formula;
                 al_apport_handling_money:=al_apport_handling_money+round((case when sign(contract_info.p_handling_charge_money*round(p_interest+n_interest,2)-0.00001)=1 then  contract_info.p_handling_charge_money*(p_interest+n_interest/p_tax_rate)/contract_info.p_all_interest WHEN sign(contract_info.p_handling_charge_money*round(p_interest+n_interest,2)-0.00001)=-1 THEN (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*CONTRACT_INFO.P_HANDLING_CHARGE_MONEY else 0 end),2);
                 al_apport_discount_money:=al_apport_discount_money+round((case when sign(contract_info.p_discount_money*round(p_interest+n_interest,2)-0.00001)=1 then  contract_info.p_discount_money*(p_interest+n_interest/p_tax_rate)/contract_info.p_all_interest when sign(contract_info.p_discount_money*round(p_interest+n_interest,2)-0.00001)=-1 then (last_day(to_date(contract_info.p_last_date,'yyyy/mm/dd'))-to_date(contract_info.p_prev_plan_date,'yyyy/mm/dd'))*contract_info.p_discount_money else 0 end),2);
               end;
             end loop;
           end;
         end loop;
                 else
                   /**insert into contract_rent_month(id,interest,fee,discount,accrued_date,start_date,end_date,
                                                 formula,hand_formula,create_date,contract_id,payment_number)
                 values(sys_guid(),round(p_interest+n_interest/p_tax_rate,2),hc_money,dis_money,
                        to_char(to_date(rent_info.plan_date,'yyyy/mm/dd'),'yyyy/mm'),to_char(trunc(to_date(rent_info.plan_date,'yyyy/mm/dd'),'mm'),'yyyy/mm/dd'),to_char(last_day(to_date(rent_info.plan_date,'yyyy/mm/dd')),'yyyy/mm/dd'),
                        'ROUND('||rent_info.interest||'/'||p_tax_rate||'-'||n_formula||'+'||l_formula||',2)'
                        ,p_hand_formula,to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),contract_info.p_contract_id,contract_info.p_payment_number);
         **/
                 end;
       end;
    commit;
    Exception
      when others then
        dbms_output.put_line('事物回滚'||sqlerrm);
        raise_application_error(-20002,sqlerrm);
        rollback;
end;