关于oracal过程及语句，自己整理避免遗忘 （二）

----计划需求（新增）   
procedure Insertplanning(ids_in in varchar2,---名称---数量---单价--金额
                            RECIPIENTSDEPARTMENT_in in varchar2,---部门
                            BZ_in  in varchar2,--用途
                            PLAN_CREATOR_in  in varchar2,---建立人
                            UNUMBER_in in varchar2,--数量
                            llr_in in varchar2,----领料人
                            ret_out out varchar2
                             )
                             is
     inids varchar2(4000);
   str_ids varchar2(4000);
   strid varchar2(300);
   iloc integer;
   iloc2 integer;
   i integer;
   var1 varchar2(50);
   var2 varchar2(50);
   var3 varchar2(50);
   var4 varchar2(50);
  -- var5 varchar2(50);
  -- var6 varchar2(50); 
   CODENUMBER_in_N varchar2(50);
                             
                             begin
                             ret_out := '-1';
                               PACK_sys.fn_no_make('JH-','PROGRAM_CODING','B_DEMAND_PLANNING_Z',CODENUMBER_in_N);
                             
                             
                             insert into B_DEMAND_PLANNING_Z(B_DEMAND_PLANNING,PROGRAM_CODING,PLAN_CREATOR,UNUMBER,PLANTOEDITTIME,
                                                                BZ,RECIPIENTSDEPARTMENT,LLR) 
                                                                    values(emp_B_DEMAND_PLANNING_Z.nextval,
                                                                    CODENUMBER_in_N,
                                                                    PLAN_CREATOR_in,
                                                                    UNUMBER_in,
                                                                    sysdate,
                                                                    BZ_in,
                                                               RECIPIENTSDEPARTMENT_in,
                                                               llr_in
                                                                    );   
                             
                             if ids_in is null then
         return;
     end if;

     inids := ids_in;
     if substr(ids_in,length(ids_in),1) <>';' then
         inids := inids||';';
     end if;


     while instr(inids,';',1) > 0 loop
            iloc := instr(inids,';',1);
           str_ids := substr(inids,1,iloc -1);
           if str_ids is not null then
                 begin
                    if substr(str_ids,length(str_ids),1) <>',' then
                        str_ids := str_ids||',';
                   end if;

                  i :=1;
                  var1:='';
                  var2:='0';
                  var3:='0';
                  var4:='0';
                 -- var5:='0';
                 -- var6:='0';
                  while instr(str_ids,',',1) > 0 loop
                              iloc2 := instr(str_ids,',',1);
                          strid := substr(str_ids,1,iloc2 -1);
                           if strid is not null then
                             if i=1 then var1:=strid;end if;-- id
                             if i=2 then var2:=strid;end if;-- 数量
                             if i=3 then var3:=strid;end if;-- 单价
                             if i=4 then var4:=strid;end if;-- 单价
                            -- if i=5 then var5:=strid;end if;-- 单价
                            -- if i=6 then var6:=strid;end if;-- 单价
                        end if;

                           str_ids := substr(str_ids,iloc2 +1);
                        i :=i+1;
                 end loop;
                
            
insert into B_DEMAND_PLANNING_MX values(emp_B_DEMAND_PLANNING_MX.nextval,
                                          emp_B_DEMAND_PLANNING_Z.currval,
                                          var1,
                                          var2,
                                          var3,
                                          var4,
                                          0);
              
           
                            
                commit;

                 end;
           end if;

           inids := substr(inids,iloc +1);
     end loop;                              
                              
    commit;
     ret_out := '1';
     return ;             
                             
                            
                               exception
                              when others then
                              rollback;
                               ret_out :=-1;
                                return ;
                             
                             
                             end Insertplanning;

　　

-- 客户详细情况, 输入:客户企业id
PROCEDURE CLIENTNAMEDETAILS(sonpeoid_in in integer,myResult out Result )
is

begin
	-- 排序用序号, 企业id, 客户名称, 客户logo
	open myResult for
		 select a.peoid,
				a.peoname,
				'http://www.buiqu.com:2632'||a.peologo as peologo ,
				a.PEOADDRESS,
				a.peocreditclass
		 from provideInfo a
		where  ISVALID = 1 and a.peoid = sonpeoid_in ;

end CLIENTNAMEDETAILS;

　　

-- 企业授权给客户的商品, 输入：登录企业, 客户企业id
procedure prodtolist(fatherpeoid_in in integer,sonpeoid_in in integer,myResult out Result)
as
begin
	 -- 商品id,商品名称,计量单位,规格型号,是否有效,授权日期,有效日期,授权说明, 企业销售均价，给销售商价格区间
	 open myResult for SELECT CLIENTAGENTPROD_ID,
            Goods_id,
	 		(SELECT Goodsname FROM Goodsinfo WHERE Goods_id = a.Goods_id) AS Goodsname,
			(SELECT Goodsunit FROM Goodsinfo WHERE Goods_id = a.Goods_id) AS Goodsunit,
			(SELECT GOODSPEC FROM Goodsinfo WHERE Goods_id = a.Goods_id) AS GOODSPEC,
			isvalid,
			authordate,
			authoreffdate,
			authordesc,
			(select avg(aprice) from selldetails where Goods_id = a.Goods_id and peoid = fatherpeoid_in) as aprice,
			dprice1,
			dprice2
	 FROM clientagentprod a
	 WHERE peoid = fatherpeoid_in AND
	 	   sonpeoid = sonpeoid_in
	order by authordate desc;
end prodtolist;

　　

-- 企业授权产品新增
procedure prodtoadd(fatherpeoid_in in integer,
		  sonpeoid_in in integer,
		  Goods_id_in in integer,
		  authoreffdate_in in date,
		  authordesc_in in varchar2,
		  dprice1_in in number,
		  dprice2_in in number,
		  CLIENTAGENTPROD_ID out integer
		  )
-- 输入：企业id ,客户id,商品id,有效期,授权说明
-- 输出：成功 返回 表id ,失败或出错 返回 -1
as
  icount integer;
  icount1 integer;
  iGENSTORAGEID integer;
  ret_out varchar2(10);
begin

	 select count(*) into icount from clientagentprod where
	 				 peoid = fatherpeoid_in and
					 sonpeoid = sonpeoid_in and
					 Goods_id = Goods_id_in;
	 if icount > 0 then
	 	 update clientagentprod set authoreffdate = authoreffdate_in ,
			   				   	   authordesc = authordesc_in
				where peoid = fatherpeoid_in and
					 sonpeoid = sonpeoid_in and
					 Goods_id = Goods_id_in;
		 commit;
	 end if;

	 IF icount = 0 then
	 	select SEQ_CLIENTAGENTPROD.nextval into CLIENTAGENTPROD_ID from dual;

		 insert into clientagentprod(
	 		clientagentprod_id,
			peoid,
			sonpeoid,
			Goods_id,
			authoreffdate,
			AUTHORDATE,
			authordesc,
			dprice1,
			dprice2
			)
		values(CLIENTAGENTPROD_ID,
				fatherpeoid_in,
				sonpeoid_in,
				Goods_id_in,
				authoreffdate_in,
				SYSDATE,
				authordesc_in,
				nvl(dprice1_in,0),
				nvl(dprice2_in,0)
				);
		commit;
	end if;

	-- 给货位，加入总仓库，数量为0
	pack_fx.addgeneralledger(sonpeoid_in,Goods_id_in,ret_out);
	return ;

  exception
      when others then
	  rollback;
	  CLIENTAGENTPROD_ID := '-1';
   	  return ;
end prodtoadd;

　　

-- 新增仓库
procedure addgeneralledger(peoid_in in integer,Goods_id_in in integer,ret_out out varchar2)
as
  icount integer;
  icount1 integer;
  iGENSTORAGEID integer;
begin
	ret_out :='1';
	-- 给货位，加入总仓库，数量为0
	select count(*) into icount from generalledger where GOODS_ID = Goods_id_in and PEOID=peoid_in;
	if icount = 0 then

	   select count(*) into icount1  from genstorage where PEOID = peoid_in;
	   if icount1 > 0 then
	   	  select min(GENSTORAGEID) into iGENSTORAGEID  from genstorage where PEOID = peoid_in;
	   else
	      select seq_genstorage.nextval into iGENSTORAGEID from dual;
	      insert into genstorage (GENSTORAGEID,GENSTORAGENAME,PEOID,CREDATE)
		  values(iGENSTORAGEID,'总仓库',peoid_in,sysdate);
		  commit;
	   end if;

	   insert into generalledger (GENERALLEDGER_ID,GOODS_ID,PEOID,GENSTORAGEID,INITNUM,GENQTY,BGENQTY)
	   		  values (seq_generalledger.nextval,Goods_id_in,peoid_in,iGENSTORAGEID,0,0,0);
	   commit;
	end if;
	return ;

  exception
      when others then
	  rollback;
	  ret_out :='-1';
   	  return ;
end addgeneralledger;

　　

--企业经销或代理的商品列表
procedure GetGoodslistA(peoid_in in integer,myResult out Result)
is
begin
	 open myResult for
	 select a.PEOID,
	        a.GOODS_ID,
	 		b.GOODSNAME,
			b.GOODSPEC,
			b.GOODSUNIT
	 	   from clientagentprod a,goodsinfo b
		   where a.goods_id(+) = b.GOODS_ID and
		   		 a.SONPEOID =peoid_in and
				 a.ISVALID=1
		  order by a.CREDATE desc;
end GetGoodslistA;

　　

-- 授信金额历史情况
procedure hisclassmoney(fatherpeoid_in in integer,sonpeoid_in in integer,myResult out Result)
as
begin
	 -- 交易时间，交易金额,首付款,使用授信金额,使用的授信比重(使用授信额度%)，当时授信的比重 (授信额度%)
	 open myResult for
	 	  select ORDERID,
		    firstpaydate,
	 		amoney,
			firstpaymoney,
			(amoney - firstpaymoney) as redmoney,
			round((amoney - nvl(firstpaymoney,0.000001)) / nvl(decode(amoney,0,0.0000001),0.0000001) *100,2) as redperce,
			credpercenum
		from buyorders
		where peoid = sonpeoid_in and
			  Goods_id in (select Goods_id from Goodsinfo where peoid = fatherpeoid_in )
		order by firstpaydate desc;
end hisclassmoney;

　　

-- 包卡发放
procedure packoffersdeposave(packoffers_id_in in integer, -- 包卡id
		  					 peoids_in in varchar2, --发放至多个经销商，经销商之间用","相隔
		  					 ret_out out varchar2)
as
   inids varchar2(3000);
   str_id varchar2(20);
   iloc integer;
   icount integer;
begin
	 ret_out := '-1';

	 if peoids_in = '0' or peoids_in is null then
	 	return;
	 end if;

	 inids := peoids_in;
	 if substr(peoids_in,length(peoids_in),1) <>',' then
	 	inids := inids||',';
	 end if;

	 -- 先删除 包卡id
	 delete from packoffersdepo where packoffers_id =packoffers_id_in and getflag <> 1;
	 commit;

	 while instr(inids,',',1) > 0 loop
	 	   iloc := instr(inids,',',1);
		   str_id := substr(inids,1,iloc -1);
		   if str_id is not null then
		   	  	 select count(*) into icount from packoffersdepo where packoffers_id =packoffers_id_in and peoid = str_id;
				 if icount = 0 then
				 	insert into packoffersdepo (packoffersdepo_id,
						   					   packoffers_id,
											   peoid,
											   Senddate,
											   isvalid,
											   CreDate)
				 					values (seq_packoffersdepo.nextval,
										   packoffers_id_in,
										   str_id,
										   sysdate,
										   1,
										   sysdate
										   );
				 		commit;
				 end if;
		   end if;

		   inids := substr(inids,iloc +1);
	 end loop;


    ret_out := '1';
	return ;

  exception
      when others then
	  rollback;
	  ret_out := '-1';
   	  return ;
end packoffersdeposave;