• 单据存储过程


    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;

     

     

  • 相关阅读:
    [luoguP2770] 航空路线问题(最小费用最大流)
    javascript技巧大全套
    jLim
    自己封装的Ajax
    JavaScript函数调用规则
    JavaScript模板引擎使用
    JavaScript MD5
    JavaScript SHA-1
    JavaScript Map对象的实现
    javascript中的_return_false和return_true
  • 原文地址:https://www.cnblogs.com/xqzblog/p/3222456.html
Copyright © 2020-2023  润新知