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;