produce

create or replace procedure P_IMSCI_ALERTPAYTYPE
(
 paramContractuuid in varchar2,
 paramMapid in varchar2,
 paramMappartid in varchar2,
 paramSetpaytypename in varchar2 := null,
 paramBuypaytypename in varchar2 := null,
 retValue out int
 )
is
   var_Setpaytypeuuid  ci_worktype.setpaytypeuuid%type := null;
   var_Buypaytypeuuid  ci_worktype.buymeasureuuid%type := null;
   var_worktypeuuid ci_worktype.worktypeuuid%type :=null;
   var_mapid ci_mapproject.mapid%type :=null;
   var_mappartid ci_mapproject.mappartid%type :=null;
-----------------查询所有需要修改的图纸工程量数据（主要是mapid 和 mappartid )------       
   cursor  tempCursor is
          select mp.mapid,mp.mappartid from ci_mapproject mp 
           where mp.worktypeuuid = var_worktypeuuid
           and mp.contractuuid = paramContractuuid;
begin
-----------------查询需要修改的那一条工作类别数据-------------- 
select mp.worktypeuuid into var_worktypeuuid from ci_scaleapp mp
          where mp.mapid = paramMapid 
          and mp.mappartid = paramMappartid
          and mp.contractuuid = paramContractuuid;
---------------查询支付类型UUID
retValue := 0;
if ( paramSetpaytypename is not null and length( paramSetpaytypename ) > 0) then
   select ty.paytypeuuid into var_Setpaytypeuuid from ci_paytype ty where ty.paytypename = paramSetpaytypename and ty.contractuuid = paramContractuuid;
end if;
if ( paramBuypaytypename is not null and length( paramBuypaytypename ) > 0) then
   select ty.paytypeuuid into var_Buypaytypeuuid from ci_paytype ty where ty.paytypename = paramBuypaytypename and ty.contractuuid = paramContractuuid;
end if;
savepoint mark1;
 --------------------支付申请 图纸工程量
 retValue := -1;
update ci_worktype ty set ty.setpaytypeuuid = var_Setpaytypeuuid,ty.bbuypaytypeuuid = var_Buypaytypeuuid 
where ty.contractuuid = paramContractuuid and ty.worktypeuuid =
(select mp.worktypeuuid from ci_scaleapp mp
          where mp.mapid = paramMapid and mp.mappartid = paramMappartid
            and mp.contractuuid = paramContractuuid);
        
 ---------------------工程款
open tempCursor;
loop
fetch tempCursor into var_mapid,var_mappartid;
       retValue := -2;
      update ci_scalewpc tt set tt.setpaytypeuuid = var_Setpaytypeuuid,
       tt.buypaytypeuuid = var_Buypaytypeuuid ,
       tt.setpaytypename=paramSetpaytypename,tt.buypaytypename=paramBuypaytypename
       where tt.contractuuid = paramContractuuid
       and tt.mapid = var_mapid and tt.mappartid = var_mappartid;
      
       ---------------------图纸工程量库
        retValue := -3;
      update ci_mapprojware tt set 
       tt.setpaytypename=paramSetpaytypename,tt.buypaytypename=paramBuypaytypename
       where tt.contractuuid=paramContractuuid 
       and tt.mapid = var_mapid and tt.mappartid = var_mappartid;
exit when tempCursor%NOTFOUND;
end loop;
close tempCursor;
retValue := 0;
-----未调用修改归属年月的存储过程来刷新影响到的所有单价号下的历史数据记录----

commit;
Exception
    when NO_DATA_FOUND then
        DBMS_OUTPUT.PUT_LINE('select into 没有返回行异常');
        rollback to savepoint mark1;
        retValue :=-1;
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('select into 返回多行异常');
        rollback to savepoint mark1;
        retValue :=-1;
    when OTHERS then
        case retValue 
             when 0 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '查询支付类型UUID');
             when -1 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '支付申请 图纸工程量');
             when -2 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '工程款');
             when -3 then DBMS_OUTPUT.PUT_LINE('错误位置:' || '图纸工程量库');
             else DBMS_OUTPUT.PUT_LINE('错误位置:' || '未知位置');
        end case;
       rollback to savepoint mark1;
end;