药师帮销售转单流程
CREATE OR REPLACE PROCEDURE P_HC_BATSALE_GSP(an_billsource in varchar2) AS
v_procname t_proc_rep.procrepname%TYPE;
v_batsaleno t_batsale_h.batsaleno%type;
v_para2825 s_sys_ini.inipara%TYPE; --前台销售按批次还是效期出库存 0：按批次，1：按效期
v_para2778 s_sys_ini.inipara%TYPE; --前台销售按批次还是效期出库存 0：按批次，1：按效期
v_para8139 s_sys_ini.inipara%TYPE; --是否开启整件优先
v_para2975 s_sys_ini.inipara%TYPE; --批发销售限制货位
v_para2976 s_sys_ini.inipara%TYPE; --批发销售限制,批发申请转批发需要过滤的商品类别
v_para2977 s_sys_ini.inipara%TYPE; --控制批发申请转批发，多少天以内的批次不允许出库。
v_busno t_batsale_h.busno%TYPE; --业务机构
v_compid t_batsale_h.compid%TYPE; --企业
v_parawms0001 s_sys_ini.inipara%TYPE;
v_vencusno t_batsale_h.vencusno%type;
v_spsl number(10);
v_warecode t_ware.warecode%type;
v_warename t_ware.warename%type;
v_vencusname t_vencus.vencusname%type;
v_ownerid t_vencus_saler.ownerid%type;
v_reckonerid t_vencus_saler.reckonerid%type;
v_saler t_vencus_saler.saler%type;
v_paytype t_vencus_saler.paytype%type;
v_whlgroupid t_vencus_saler.whlgroupid%type;
v_cashtype t_vencus_saler.cashtype%type;
v_subitemid t_vencus_saler.subitemid%type;
v_xxx number(10);
v_sywareqty t_batsale_d.wareqty%type;
v_wareqty t_batsale_d.wareqty%type;
v_rownum t_batsale_d.rowno%type;
v_errm varchar2(2000);
v_addrid t_road_addr.addrid%type;
v_drugcode varchar2(1000);
v_ptwareqty number(16, 6);
v_erpwareqty number(16, 6);
v_pshs number(10);
v_cnt_addr number(5);
v_invoicetype t_vencus.invoicetype%type;
v_statussl number(10);
v_warecodehz varchar2(1000);
v_drugcode1 varchar2(1000);
v_sps2 number(10);
v_into_char VARCHAR2(1000);
v_inipara s_sys_ini.inipara%TYPE;
v_flag NUMBER(10);
v_classcode18 VARCHAR2(4000);
v_classcode09 VARCHAR2(4000);
v_jyfl NUMBER(10);
V_BAK1 VARCHAR2(4000);
BEGIN
--是否有替换储存过程
BEGIN
SELECT procrepname
INTO v_procname
FROM t_proc_rep
WHERE upper(procname) = upper('p_hc_batsale')
AND status = 1;
EXCEPTION
WHEN no_data_found THEN
v_procname := NULL;
END;
IF v_procname IS NOT NULL THEN
BEGIN
EXECUTE IMMEDIATE 'begin ' || v_procname || '; end;';
END;
RETURN;
END IF;
update h2.h2_batsale_h set err = '' where err like '%资料审核中';
for rec in (select *
from h2.h2_batsale_h h
where nvl(h.flag, 0) = 0
and h.billsource = an_billsource
--AND h.djbh<>'457443468'
and not exists
-- 根据销售标识与单据来源进行过滤
(select 1
from t_batsale_h th
where
th.sale_idbs = decode(h.billsource,
1,
'0401',
2,
'0405',
3,
'0406',
4,
'0302',
5,
'0303',
6,
'0408',
'')
and th.djbh = h.djbh
and th.status <> '2') --增加过滤，避免多次接收
-- 拦截不存在与erp往来单位档案内的客户的订单
and exists (select 1
from t_vencus
where compid = 1
and vencuscode = h.customerid)
) loop
v_compid := 1;
v_busno := 1000;
-- 判断订单是否有明细
select count(*)
into v_pshs
from h2.h2_batsale_d d
where d.djbh = rec.djbh
and d.billsource = rec.billsource;
if v_pshs = 0 then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '单据' || rec.djbh || '订单来源' || rec.billsource ||
'没有明细数据'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
--匹配往来单位
begin
select vencusno, vencusname, invoicetype
into v_vencusno, v_vencusname, v_invoicetype
from t_vencus t
where t.compid = v_compid
and t.vencuscode = rec.customerid
AND t.status=1;
exception
when no_data_found then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '客户编码' || rec.customerid || '在erp中找不到'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end;
--判断是否存在未审核的往来单位修改单
select count(*)
into v_statussl
from t_vencus_snapshot t
where t.vencusno = v_vencusno
and t.compid = v_compid
and status = 0;
if v_statussl > 0 then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 0,
err = err || '客户编码' || rec.customerid || '资料审核中'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
-----------------客户证照效期-----------------
BEGIN
SELECT CERTIFICATENAME, STATUS
INTO V_INTO_CHAR, V_INIPARA
FROM (SELECT S.CERTIFICATENAME,
TO_CHAR(NVL(C.STATUS, NVL(S.STATUS, -1))) AS STATUS,
FLOOR(TRUNC(NVL(C.INVALIDATE, SYSDATE)) -
TRUNC(SYSDATE)) AS DAYS
FROM T_VENCUS V
INNER JOIN T_VENCUS_CERTIFICATE C
ON (V.VENCUSNO = C.VENCUSNO)
AND C.COMPID = V_COMPID
LEFT JOIN T_CERTIFICATE_SET S
ON (C.CERTIFICATEID = S.CERTIFICATEID)
WHERE S.VENCUSFLAG IN (0, 2)
AND NOT (TRIM(C.PHOTO_NO) IS NULL AND C.INVALIDATE IS NULL)
AND (NVL(S.VENCUSTYPELIST, ' ') = ' ' OR
INSTR(',' || S.VENCUSTYPELIST || ',',
',' || V.VENCUSTYPE || ',') > 0)
AND NVL(C.STATUS, NVL(S.STATUS, -1)) <> 1 --1不提示不控制，-1提示且控制，0提示不控制
AND NVL(S.CLASSGROUPNO, '%') = '%'
AND TRUNC(SYSDATE) >
TRUNC(NVL(C.INVALIDATE, SYSDATE - 1)) - 30
AND V.VENCUSNO = V_VENCUSNO
AND V.COMPID = V_COMPID
ORDER BY TO_CHAR(NVL(C.STATUS, NVL(S.STATUS, -1))),
FLOOR(TRUNC(NVL(C.INVALIDATE, SYSDATE)) -
TRUNC(SYSDATE))) K
WHERE ROWNUM = 1;
IF LENGTH(TRIM(NVL(V_INTO_CHAR, ' '))) > 0 AND V_INIPARA = '-1' THEN
ROLLBACK;
UPDATE H2.H2_BATSALE_H
SET FLAG = 2,
ERR = ERR || '客户编码为' || V_VENCUSNO || '的客户证照【' ||
V_INTO_CHAR || '】效期已过期。'
WHERE DJBH = REC.DJBH AND BILLSOURCE = REC.BILLSOURCE;
COMMIT;
CONTINUE;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
-----------------客户证照效期-----------------
----------------客户经营范围------------------
BEGIN
SELECT a.classname
INTO v_into_char
FROM t_class_base a
INNER JOIN t_vencus_range b
ON (a.classcode = b.classcode)
INNER JOIN t_vencus v
ON (b.compid = v.compid AND b.vencusno = v.vencusno)
WHERE rownum = 1
AND b.compid = v_compid
AND b.vencusno = v_vencusno
AND b.status = 1
AND v.vencusflag IN (0, 2);
EXCEPTION
WHEN no_data_found THEN
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '客户编码' || rec.customerid || '，没有任何经营范围'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
END;
----------------客户经营范围------------------
--匹配子结账户
begin
select /*t.ownerid,*/
'01',
t.reckonerid,
t.saler,
t.paytype,
t.whlgroupid,
t.cashtype,
t.subitemid
into v_ownerid,
v_reckonerid,
v_saler,
v_paytype,
v_whlgroupid,
v_cashtype,
v_subitemid
from t_vencus_saler t
where t.vencusno = v_vencusno
and t.compid = v_compid
and status = 1
and rownum = 1;
exception
when others then
/* v_ownerid := null;
v_reckonerid := null;
v_saler := null;
v_paytype := null;
v_whlgroupid := null;
v_cashtype := null;
v_subitemid := null;*/
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '单据中客户编码' || rec.customerid || '名称' ||
v_vencusname || '在erp中没有设置批发业务员'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end;
--判断运输地址是否调整
select count(1)
into v_cnt_addr
from t_road_addr t
where t.compid = v_compid
and t.companyid = v_vencusno
and nvl(t.subitemid, 0) = nvl(v_subitemid, 0)
and trim(t.address) = trim(rec.address);
if v_cnt_addr > 0 then
update t_road_addr t
set t.address = trim(rec.address)
where t.compid = v_compid
and t.companyid = v_vencusno
and nvl(t.subitemid, 0) = nvl(v_subitemid, 0);
end if;
--运输地址
begin
select t.addrid
into v_addrid
from t_road_addr t
where t.compid = v_compid
and t.companyid = v_vencusno
and nvl(t.subitemid, 0) = nvl(v_subitemid, 0)
and rownum = 1;
/*exception
when others then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '单据中客户编码' || rec.customerid ||'名称'||v_vencusname|| '在erp中没有设置运输地址'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue; */
exception
when others then
v_addrid := f_get_serial(in_billcode => 'RODD', in_org_code => null);
insert into t_road_addr
(compid,
addrid,
roadid,
companyid,
companyname,
isdefault,
addrtype,
area,
subitemid,
address)
select v_compid,
v_addrid,
'000018',
v_vencusno,
v_vencusname,
1,
'01',
null,
nvl(v_subitemid, 0),
rec.address
from dual;
end;
--判断所有商品是否存在
select count(*), wm_concat(d.drugcode)
into v_spsl, v_drugcode
from h2.h2_batsale_d d
where d.djbh = rec.djbh
and d.billsource = rec.billsource
and not exists (select 1
from t_ware t
where t.compid = v_compid
and t.warecode = d.drugcode);
if v_spsl > 0 then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '单据中有商品编码' || v_drugcode || '在erp中找不到'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
--判断商品是否网络禁售
select count(*), wm_concat(d.drugcode)
into v_sps2, v_drugcode1
from h2.h2_batsale_d d
where d.djbh = rec.djbh
and d.billsource = rec.billsource
and not exists (select 1
from t_ware_class_base t
where t.compid = v_compid
and t.WAREID = d.drugcode
AND t.classgroupno='05'
AND t.classcode='0502');
if v_sps2 > 0 then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = err || '单据中商品编码' || v_drugcode1 || '网络禁止销售'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
--判断商品是否设置了税率
begin
SELECT warecode, warename
INTO v_warecode, v_warename
FROM h2.h2_batsale_d a, t_ware b
WHERE a.drugcode = b.warecode
AND b.compid = v_compid
AND a.djbh = rec.djbh
and a.billsource = rec.billsource
AND (b.saletax IS NULL OR b.saletax >= 0)
AND rownum = 1;
exception
when others then
rollback;
update h2.h2_batsale_h
SET flag = 2,
err = err || '单据中商品编码' || v_warecode || ',名称' || v_warename ||
'税率设置为0，请检查!'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end;
if v_drugcode is not null then
rollback;
update h2.h2_batsale_h
SET flag = 2,
err = err || '单据中商品编码' || v_warecode || ',名称' || v_warename ||
'在ERP中不存在!'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
------------商品客户经营范围------------------
BEGIN
FOR II IN (SELECT D.DRUGCODE WAREID
FROM H2.H2_BATSALE_D D
WHERE D.DJBH = REC.DJBH
AND D.BILLSOURCE = REC.BILLSOURCE) LOOP
BEGIN
SELECT A.CLASSNAME
INTO V_INTO_CHAR
FROM T_CLASS_BASE A
INNER JOIN T_VENCUS_RANGE B
ON (A.CLASSCODE = B.CLASSCODE)
INNER JOIN T_VENCUS V
ON (B.COMPID = V.COMPID AND B.VENCUSNO = V.VENCUSNO)
INNER JOIN (SELECT A.WAREID,
B.WARECODE,
A.CLASSGROUPNO,
A.CLASSCODE
FROM T_WARE_CLASS_BASE A, T_WARE_BASE B
WHERE A.WAREID = B.WAREID
AND A.WAREID = II.WAREID
AND A.COMPID = 1) W
ON (B.CLASSCODE = W.CLASSCODE)
WHERE ROWNUM = 1
AND B.COMPID = V_COMPID
AND B.VENCUSNO = V_VENCUSNO
AND B.STATUS = 1
AND V.VENCUSFLAG IN (0, 2);
EXCEPTION
WHEN NO_DATA_FOUND THEN
ROLLBACK;
UPDATE H2.H2_BATSALE_H
SET FLAG = 2,
ERR = ERR || '客户' || V_VENCUSNO || '无此商品【' ||
II.WAREID || '】的经营范围；'
WHERE DJBH = REC.DJBH
AND BILLSOURCE = REC.BILLSOURCE;
COMMIT;
EXIT;
END;
END LOOP;
BEGIN
SELECT FLAG
INTO V_FLAG
FROM H2.H2_BATSALE_H H
WHERE H.BILLSOURCE = REC.BILLSOURCE
AND H.DJBH = REC.DJBH;
IF V_FLAG = 2 THEN
CONTINUE;
END IF;
END;
END;
------------商品客户经营范围------------------
----------商品客户经营分类控制 20240924-------------
--只控制药品 1801经营范围分类
BEGIN
FOR III IN (SELECT D.DRUGCODE WAREID
FROM H2.H2_BATSALE_D D
WHERE D.DJBH = REC.DJBH
AND D.BILLSOURCE = REC.BILLSOURCE) LOOP
SELECT SUBSTR(C.CLASSCODE, 1, 4)
INTO V_CLASSCODE18
FROM T_WARE_CLASS_BASE C, T_WARE B
WHERE B.WAREID = C.WAREID
AND B.COMPID = C.COMPID
AND B.WARECODE = III.WAREID
AND C.CLASSGROUPNO = '18';
IF V_CLASSCODE18 = '1801' THEN
SELECT C.CLASSCODE
INTO V_CLASSCODE09
FROM T_WARE_CLASS_BASE C, T_WARE B
WHERE B.WAREID = C.WAREID
AND B.COMPID = C.COMPID
AND B.WARECODE = III.WAREID
AND C.CLASSGROUPNO = '09';
SELECT BAK1
INTO V_BAK1
FROM T_VENCUS
WHERE VENCUSCODE = REC.CUSTOMERID
AND COMPID = 1;
SELECT NVL(INSTR(V_BAK1, V_CLASSCODE09, 1, 1), 0)
INTO V_JYFL
FROM DUAL;
IF V_JYFL < 0 THEN
ROLLBACK;
UPDATE H2.H2_BATSALE_H
SET FLAG = 2,
ERR = ERR || '单据中商品编码' || V_WARECODE || '，的经营分类' ||
V_CLASSCODE09 || '不在该客户经营分类中！'
WHERE DJBH = REC.DJBH
AND BILLSOURCE = REC.BILLSOURCE;
COMMIT;
EXIT;
END IF;
END IF;
END LOOP;
BEGIN
SELECT FLAG
INTO V_FLAG
FROM H2.H2_BATSALE_H H
WHERE H.BILLSOURCE = REC.BILLSOURCE
AND H.DJBH = REC.DJBH;
IF V_FLAG = 2 THEN
CONTINUE;
END IF;
END;
END;
----------商品客户经营分类控制 20240924-------------
--判断库存是否满足
SELECT wm_concat(a.drugcode)
into v_warecodehz
FROM h2.h2_batsale_d a
left join （
select warecode, sum(a.wareqty - a.awaitqty) as sl
from t_store_d a
inner join t_store_i b
on a.batid = b.batid
and a.wareid = b.wareid
inner join t_stall c
on a.stallno = c.stallno
and c.stalltype in (11) --货位类型暂时放开
inner join t_ware d
on a.compid = d.compid
and a.wareid = d.wareid
where a.wareqty - a.awaitqty > 0
and nvl(b.flag1, 0) = 0 --库存锁定标识
and a.compid = v_compid
and a.busno = v_busno
and b.ownerid = '01'
AND b.isstopselling<>2
group by warecode) b
on a.drugcode = b.warecode
where a.djbh = rec.djbh
and a.billsource = rec.billsource
and nvl(a.shl,0) > nvl(b.sl,0);
if v_warecodehz is not null then
rollback;
UPDATE h2.h2_batsale_h
SET flag = 2, err = err ||'商品'|| v_warecodehz || '库存不够'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
commit;
continue;
end if;
v_para2975 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2975',
p_userid => NULL);
v_para2976 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2976',
p_userid => NULL);
v_para2977 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2977',
p_userid => NULL);
v_para2778 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2778',
p_userid => NULL);
v_parawms0001 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => 'WMS0001',
p_userid => NULL);
v_para2825 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2825',
p_userid => NULL); --之前取的WMS0001 by WangChao(2515) 20190815
v_para8139 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '8139',
p_userid => NULL);
/* DELETE FROM tmp_batsale_act_alloc;
INSERT INTO tmp_batsale_act_alloc
(compid, busno, wareid, whlprice, applyqty, actualqty, maxqty)
SELECT v_compid,
v_busno,
c.wareid,
0,
bd.shl,
0,
decode(nvl(c.maxqty, 0), 0, 1, c.maxqty)
FROM h2.h2_batsale_d bd, h2.h2_batsale_h h, t_ware c
WHERE h.djbh = rec.djbh
and h.billsource = rec.billsource
AND h.djbh = bd.djbh
and h.billsource = bd.billsource
AND c.compid = v_compid
AND bd.drugcode = c.wareid
AND NOT EXISTS
(SELECT 1
FROM t_ware_class_base wb
WHERE wb.wareid = c.wareid
AND (instr(',' || v_para2976 || ',',
',' || wb.classgroupno || ',') > 0 OR
instr(',' || v_para2976 || ',',
',' || wb.classcode || ',') > 0)
AND wb.compid = (CASE
WHEN EXISTS (SELECT 1
FROM t_ware_class_base twcb
WHERE twcb.compid = 1
AND twcb.wareid = wb.wareid) THEN
1
ELSE
0
END));*/
--生成批发销售单
begin
v_batsaleno := f_get_serial(in_billcode => 'WHL',
in_org_code => v_busno);
INSERT INTO t_batsale_h
(batsaleno,
billcode,
compid,
batcontno,
batapplyno,
vencusno,
vencusname,
busno,
saler,
pickuptype, --10
accchkdate,
accchker,
accchked,
invoicetype,
addrid,
whlgroupid,
notes,
lastmodify,
lasttime,
status, --20
execdate,
checker1,
checkbit1,
checker2,
checkbit2,
checker3,
checkbit3,
checker4,
checkbit4,
checker5, --30
checkbit5,
paytype,
cashtype,
ownerid,
reckonerid,
subitemid,
parentbatsaltno,
loadorder,
checkdate1,
checkdate2, --40
checkdate3,
checkdate4,
checkdate5,
notes1,
createtime,
createuser,
versionnum,
guarantor,
salename,
salezone, --50
wms_flag,
client,
batsaletype,
djbh,
billsource,
sale_idbs,
source_type,
RECAMOUNT,
SUM_WHLPRICE,
stamp,
shdz,
shr,
shrdh,
shsf,
shcs,
shqx) --59
select v_batsaleno,
'WHL',
v_compid,
null,
null,
v_vencusno,
v_vencusname,
v_busno,
-- 该字段需要根据实际情况进行修改
case
when rec.billsource in ('1', '2', '3', '6') then
'1008'
when rec.billsource in ('4', '5') then
'1034'
end,
'01', --10
null,
null,
0,
v_invoicetype,
v_addrid,
-- 该字段需要根据实际情况进行修改
case
when rec.billsource = 1 then
'1004'
when rec.billsource = 2 then
'103'
when rec.billsource = 3 then
'104'
when rec.billsource = 4 then
'102'
when rec.billsource = 5 then
'102'
when rec.billsource = 6 then
'102'
else
''
end, --单头批发价格组
null,
--该字段需要根据实际情况进行修改
case
when rec.billsource in ('1', '2', '3', '6') then
1008
when rec.billsource in ('4', '5') then
1034
end,
sysdate,
0, --20
null,
/*'1018',
1,
'1018',
1,*/
null,
0,
null,
0,
null,
0,
null,
0,
null, --30
0,
v_paytype,
v_cashtype,
v_ownerid,
--该字段需要根据实际情况进行修改
case
when rec.billsource in ('1', '2', '3', '6') then
'01'
when rec.billsource in ('4', '5') then
'01'
end, --部门
v_subitemid,
null,
null,
null,
null, --40
null,
null,
null,
null,
sysdate,
--该字段需要根据实际情况进行修改
case
when rec.billsource in ('1', '2', '3', '6') then
1008
when rec.billsource in ('4', '5') then
1034
end,
0,
null,
null,
null, --50
case
when v_parawms0001 = 1 then
1
else
0
end,
null,
--该字段需要根据实际情况进行修改
decode(rec.billsource,
1,
111,
2,
111,
3,
111,
4,
111,
5,
111,
6,
111), --批发类型
rec.djbh,
rec.billsource,
--该字段需要根据实际情况进行修改
case
when rec.billsource = 1 then
'0401'
when rec.billsource = 2 then
'0405'
when rec.billsource = 3 then
'0406'
when rec.billsource = 4 then
'0302'
when rec.billsource = 5 then
'0303'
when rec.billsource = 6 then
'0408'
else
''
end, --销售标识
--该字段需要根据实际情况进行修改
case
when rec.billsource in ('1', '2', '3', '4', '5', '6') then
'8'
end, -- 单据来源--57
rec.je,
rec.je,
seq_stamp.nextval,
rec.address,
rec.recvname,
rec.recvphone,
rec.provincename,
rec.cityname,
rec.districtname
from dual;
v_rownum := 0;
for header in (select c.compid,
v_busno,
d.djbh,
d.dj_sn,
c.wareid,
d.shl,
d.dj,
c.warecode,
d.shop_amount,
d.platform_amount,
decode(d.shl,
0,
d.dj,
round((d.shl * d.dj -
nvl(d.shop_amount, 0) -
nvl(d.platform_amount, 0)) / d.shl,
2)) as price
from h2.h2_batsale_d d,
h2.h2_batsale_h h,
t_ware c
where d.djbh = h.djbh
and d.billsource = h.billsource
and h.djbh = rec.djbh
and h.billsource = rec.billsource
and d.drugcode = c.warecode
and c.compid = v_compid) loop
v_sywareqty := header.shl;
v_xxx := header.dj_sn; --行号
for detail in (select a.stallno,
a.batid,
a.wareid,
a.wareqty - a.awaitqty as wareqty,
b.makeno,
b.makedate,
b.purprice,
b.purtax,
d.saletax,
b.invalidate,
b.distprice,
b.factoryid,
d.maxqty,
d.midqty,
d.areacode
from t_store_d a
inner join t_store_i b
on a.batid = b.batid
and a.wareid = b.wareid
inner join t_stall c
on a.stallno = c.stallno
and c.stalltype in (11) --货位类型暂时放开
inner join t_ware d
on a.compid = d.compid
and a.wareid = d.wareid
where a.wareqty - a.awaitqty > 0
and nvl(b.flag1, 0) = 0 --库存锁定标识
and a.compid = header.compid
and a.busno = v_busno
and a.wareid = header.wareid
and b.ownerid = '01'
and b.invalidate - sysdate > 20
AND b.isstopselling<>2
ORDER BY /*case
when rec.billsource in (1, 2, 3, 4, 5, 6) then
hb_px
else
hc_px
end, --判断c端先出，还是b端先出*/
CASE
WHEN v_para2825 = '1' THEN --按效期
to_char(b.invalidate -
to_date('19700101', 'yyyymmdd')) --UNIX时间
WHEN v_para2825 = '2' THEN --按库存数
lpad(to_char(floor(a.wareqty)), 10, '0')
WHEN v_para2825 = '3' THEN --按批号
nvl(b.makeno, '')
ELSE
to_char(b.batid) --按批次
END
) loop
v_rownum := v_rownum + 1;
IF v_sywareqty > detail.wareqty THEN
--如果剩余数量大于库存数量，则将库存数量全部插入
v_wareqty := detail.wareqty;
--剩余数量 - 库存数量 = 余下还要插入的数量
v_sywareqty := v_sywareqty - detail.wareqty;
ELSE
--如果剩余数量小于库存数量，则将剩余数量全部插入
v_wareqty := v_sywareqty;
v_sywareqty := v_sywareqty - v_sywareqty;
END IF;
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice, --进价 10
purtax,
saletax,
saleprice,
invalidate,
whlprice, --批销价 15
leastwhlprice,
maxwhlprice,
dtlsaler,
midqty,
avgpurprice,
pstflag,
backprice,
notes,
distprice,
setpurprice1,
setpurprice2,
setwhlprice1,
setwhlprice2,
setwhlprice3,
setwhlprice4,
setwhlprice5,
chker1,
chker2,
batbackqty,
batbackamt,
redeemsum,
prediswhlprice,
maxqty,
areacode,
multibusno,
invoiceredeemsum,
back_var5)
select v_batsaleno,
v_rownum,
detail.wareid,
detail.factoryid,
v_wareqty,
detail.stallno,
detail.batid,
detail.makeno,
detail.makedate,
detail.purprice, --10 进价
detail.purtax,
detail.saletax,
header.price,
detail.invalidate,
header.price, --15 批销价
header.price,
header.price,
v_saler,
detail.midqty,
detail.purprice,
0,
detail.purprice,
null,
detail.distprice,
null,
null,
null,
null,
null,
null,
null,
null,
null,
0,
0,
0,
header.price,
detail.maxqty,
detail.areacode,
null,
0,
v_xxx
from dual;
-- 如果批销价小于进价，则将一、二审修改为未审
/*if header.price < f_get_b2bleastwhlprice(rec.billsource,header.warecode) then
update t_batsale_h
set checker1 = null, checkbit1 = 0, checker2 = null, checkbit2 = 0
where batsaleno = v_batsaleno;
end if;*/
--如果剩余数量为0 则终止循环
IF v_sywareqty <= 0 THEN
EXIT;
END IF;
end loop;
v_warecode := header.warecode;
IF v_sywareqty > 0 THEN
rollback;
delete from t_batsale_h h where h.batsaleno = v_batsaleno;
delete from t_batsale_d d where d.batsaleno = v_batsaleno;
UPDATE h2.h2_batsale_h
SET flag = 2, err = err || header.warecode || '库存不够'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
COMMIT;
-- CONTINUE;
EXIT; --跳出游标
END IF;
end loop;
select nvl(sum(wareqty), 0)
into v_erpwareqty
from t_batsale_d d
where d.batsaleno = v_batsaleno;
select sum(d.shl)
into v_ptwareqty
from h2.h2_batsale_d d
where d.djbh = rec.djbh
and d.billsource = rec.billsource;
--判断数量是否一致
if v_erpwareqty = v_ptwareqty then
-- update t_batsale_h h
-- set h.checker1=168,h.checkbit1=1,h.checker2=168,h.checkbit2=1
-- where h.batsaleno = v_batsaleno;
UPDATE h2.h2_batsale_h
SET flag = 1
WHERE djbh = rec.djbh
and billsource = rec.billsource;
/* if v_erpwareqty = 0 then
delete from t_batsale_h h where h.batsaleno = v_batsaleno;
delete from t_batsale_d d where d.batsaleno = v_batsaleno;
UPDATE h2.h2_batsale_h
SET flag = 2, err = '整单库存为0'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
CONTINUE;
end if;*/
--自动一审/二审
/* if rec.billsource in ('1', '2', '3', '6') then
update t_batsale_h bh
set bh.checker1 = 1008,
bh.checkbit1 = 1,
bh.checkdate1 = SYSDATE + numtodsinterval(-3, 'minute')
where bh.batsaleno = v_batsaleno;
\* update t_batsale_h bh
set bh.checker2 = 1008,
bh.checkbit2 = 1,
bh.checkdate2 = sysdate + numtodsinterval(-1, 'minute')
where bh.batsaleno = v_batsaleno;*\
end if;
*/
UPDATE h2.h2_batsale_h
SET flag = 1
WHERE djbh = rec.djbh
and billsource = rec.billsource;
--药师帮订单插入状态表
INSERT INTO h2.ysb_ddmx_writeback
(orderid,
ordercode,
dj_sn,
drugcode,
amount,
price,
je,
batchnum,
proddate,
validity,
status)
SELECT rec.djbh AS orderid,
d.batsaleno AS ordercode,
to_number(d.back_var5) AS dj_sn,
w.warecode AS drugcode,
d.wareqty AS amount,
d.whlprice AS price,
d.wareqty * d.whlprice je,
d.makeno AS batchnum,
to_char(d.makedate, 'yyyy-mm-dd') proddate,
to_char(d.invalidate, 'yyyy-mm-dd') validity,
0 status
FROM t_batsale_d d
join t_batsale_h h
on h.batsaleno = d.batsaleno
left join t_ware w
on w.compid = 1
and w.wareid = d.wareid
WHERE d.batsaleno = v_batsaleno
and h.sale_idbs = '0401';
COMMIT;
else
rollback;
delete from t_batsale_h h where h.batsaleno = v_batsaleno;
delete from t_batsale_d d where d.batsaleno = v_batsaleno;
UPDATE h2.h2_batsale_h
SET flag = 2,
err = '订单数量为' || v_ptwareqty || '跟erp生成的数量' || v_erpwareqty ||
'不一致' || v_warecode || '库存不够'
WHERE djbh = rec.djbh
and billsource = rec.billsource;
COMMIT;
--CONTINUE;
end if;
EXCEPTION
WHEN OTHERS THEN
rollback;
v_errm := substr(sqlerrm, 1, 1900);
update h2.h2_batsale_h
set flag = 2, err = v_errm
WHERE djbh = rec.djbh
and billsource = rec.billsource;
COMMIT;
CONTINUE;
END;
commit;
end loop;
END;