17,377
社区成员
发帖
与我相关
我的任务
分享
create or replace PROCEDURE 总部有货门店无货品种明细
(
V_SUBBH IN NVARCHAR2
--v_lsdl IN NVARCHAR2
) AS
v_code VARCHAR2(20);
v_name VARCHAR2(20);
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE lyl_总部有货门店无货品种明细';
DECLARE CURSOR SUBPHK_CURSOR IS SELECT code,name from organization where nvl(ISFORBIDDEN,0)=0 and code<>'03010757' and code like '%V_SUBBH%' order by code;
BEGIN
OPEN SUBPHK_CURSOR;
LOOP
FETCH SUBPHK_CURSOR INTO v_code,v_name;
--总部有货门店无货品种信息
insert into lyl_总部有货门店无货品种明细 (门店编号,门店名称,货号,商品名称)
Select v_code,v_name,a.hh 货号,a.pm 商品名称
From yw A
left join KCLB lb on lb.id=a.lb
left join (SELECT HH,subbh FROM SUBPHK WHERE SL<>0 AND SUBBH=v_code group by HH,subbh having SUM(sl)>0) sub on sub.hh=a.hh
where nvl(a.jksl,0)>0 and sub.subbh is null
order by a.lb,a.hh;
END LOOP;
CLOSE SUBPHK_CURSOR;
END;
COMMIT;
RETURN;
END;