单据存储过程

create or replace procedure SP_BM_SPUHEADCATEGORY

(
i_BILLNO      IN VARCHAR2,
i_BillType      In VARCHAR2
) AS
-------------------------------------------------------------------
--SP_BM_SPUHeadMain        BM_SPUHead单据审核过程入口
--说明：
--参数：
--返回：
--表  ：
--描述：
--注意：
--建立：张宪强  2013.07.25
-------------------------------------------------------------------
  v_Err        INTEGER := -20001;
  v_BreakPoint    INTEGER;
  v_Msg        VARCHAR2(1000);

  r0          BM_SPUHead%rowtype;

BEGIN
  v_BreakPoint := 10;
  v_Msg        := ' ';

--1. 单据检查
  v_BreakPoint := 110;
  BEGIN
    Select * into r0 from BM_SPUHead where BILLNO=i_BILLNO;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_Msg := '单据['||i_BILLNO||'] 不存在!';
      Raise_Application_Error(-20001,'(SP_BM_SPUHEADCATEGORY):'||To_Char(v_BreakPoint)||'-'||v_Msg);
  END;

  v_BreakPoint := 120;
  IF r0.BillType <> to_number(i_Billtype) THEN
    v_Msg := '单据类型['||i_BillType||'] 参数与单据表数据不一致!';
    Raise_Application_Error(-20001,'(SP_BM_SPUHEADCATEGORY):'||To_Char(v_BreakPoint)||'-'||v_Msg);
  END IF;

  v_BreakPoint := 130;
  IF r0.effecttime IS NULL THEN
    Update BM_SPUHead set effecttime=trunc(SYSDATE) where BILLNO=i_BILLNO;
    r0.effecttime := trunc(SYSDATE);
  END IF;

--2. 单据执行
  IF r0.BillType = 1212 THEN
 
    --如果生效日期早于等于当前日期，则单据立即生效
    v_BreakPoint := 210;
    IF r0.effecttime <= trunc(SYSDATE) THEN
      v_BreakPoint := 220;
      SP_BM_SPUCATEGORYRUN(i_BILLNO);
    ELSE
      v_BreakPoint := 230;
      --ExecuteFlag:执行标志(0=待执行 1=已执行)
      INSERT INTO DayOverBillList0
        (SeqNo,BILLNO,BILLTYPE,StartDate,EndDate,ExecuteFlag)
      VALUES
        (SEQ_SEQNo.Nextval,r0.billno,r0.billtype,TRUNC(r0.StartDate),TRUNC(r0.EndDate),0);
    END IF;
  ELSE
    v_Msg := '单据类型['||i_BillType||'] 未定义!';
    Raise_Application_Error(-20001,'(SP_BM_SPUHEADCATEGORY):'||To_Char(v_BreakPoint)||'-'||v_Msg);
  END IF;


  RETURN;
EXCEPTION WHEN OTHERS THEN
  ROLLBACK;
  v_Msg := REPLACE(SQLERRM,'ORA' || V_Err || ': ');
    Raise_Application_Error(-20001,'(SP_BM_SPUHEADCATEGORY):'||To_Char(v_BreakPoint)||'-'||v_Msg);
  
end SP_BM_SPUHEADCATEGORY;

 

create or replace procedure SP_BM_SPUCATEGORYRun(
i_BILLNO            IN VARCHAR2

) AS
-------------------------------------------------------------------
--SP_BM_SPUCATEGORYRun      商品转类单 更新主档
--说明：
--参数：
--返回：
--表  ：
--描述：
--注意：
--建立：张宪强 2013-07-25
-------------------------------------------------------------------
  v_Err        INTEGER := -20001;
  v_BreakPoint    INTEGER;
  v_Msg        VARCHAR2(1000);

  r0          BM_SPUHead%rowtype;

  
BEGIN

  v_BreakPoint := 10;
  v_Msg        := ' ';

  v_BreakPoint := 110;
  BEGIN
    Select * into r0 from BM_SPUHead where BILLNO=i_BILLNO;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      v_Msg := '商品转类单['||i_BILLNO||'] 不存在!';
      Raise_Application_Error(-20001,'(SP_BM_SPUCATEGORYRun):'||To_Char(v_BreakPoint)||'-'||v_Msg);
  END;

    v_BreakPoint := 210;
  FOR r1 IN
  (
    SELECT * FROM Bm_Spudetail WHERE BILLNO=i_BILLNO
  )
  LOOP
    --(1) 更新SPU表  r1   spudetailList 
    v_BreakPoint := 230;

           
    -- Update SPU t Set t.categoryid =r1.Categoryid2, t.categorycode = r1.categorycode2,
             -- t.resourceid = r0.categorygroup2,
             --( select t5.resourceid from res t5 where t5.resourceid = r0.categorygroup2),
            --  t.resourcecode =  (select t6.resourcecode from res t6 where t6.resourceid = r0.categorygroup2)
              
      Update SPU t Set t.categoryid =r1.Categoryid2, t.categorycode = r1.categorycode2,
                  t.resourceid = r0.categorygroup2,
                  --(   select t1.resourceid 
                  --             from  res t1 ,resitem t2,categoryv5 t3 
                  --              where t.resourceid=t1.resourceid and t2.resourceid=t1.resourceid
                  --               and t2.itemid=t3.categoryID5 and t3.categoryID5= r1.categoryid2),
                                        
                t.resourcecode = ( select t.resourcecode from res t where t.resourceid =r0.categorygroup2 )
                -- (   select t1.resourcecode 
                --               from  res t1 ,resitem t2,categoryv5 t3 
                --                where t.resourceid=t1.resourceid and t2.resourceid=t1.resourceid
                --                 and t2.itemid=t3.categoryID5 and t3.categoryID5= r1.categoryid2  )
      WHERE t.spuid = r1.Spuid ;
      
     

    --(2) 更新CATEGORY表  商品实际数量
     v_BreakPoint := 245;
     
     Update CATEGORY t Set t.spucount = t.spucount +1
         WHERE t.categoryid = ( select t5.categoryID4 from categoryv5 t5 where t5.categoryid5 = r1.Categoryid2) ;
         
     Update CATEGORY t Set t.spucount = t.spucount -1
         WHERE t.categoryid = ( select t5.categoryID4 from categoryv5 t5 where t5.categoryid5 = r1.categoryid) ;
  END LOOP;


    RETURN;
EXCEPTION WHEN OTHERS THEN
    ROLLBACK;
    v_Msg := REPLACE(SQLERRM,'ORA' || V_Err || ': ');
    Raise_Application_Error(-20001,'(SP_BM_SPUCATEGORYRun):'||To_Char(v_BreakPoint)||'-'||v_Msg);

end SP_BM_SPUCATEGORYRun;

 

 

 

转载于:https://www.cnblogs.com/xqzblog/p/3222456.html