修改表结构,增加字段
首营表:
ALTER TABLE T_CHK_VENDOR ADD ( XYYNO VARCHAR2(100), YSBNO VARCHAR2(100) );
批发申请单主表
ALTER TABLE T_BATSALEAPPLY_H ADD ( djbh VARCHAR2(100), billsource VARCHAR2(100));
批发申请单明细表
ALTER TABLE T_BATSALEAPPLY_H ADD DJ_SN NUMBER;
批发销售单主表
ALTER TABLE T_BATSALE_H ADD ( djbh VARCHAR2(100), billsource VARCHAR2(100), sale_idbs VARCHAR2(100), expresscompany VARCHAR2(100), express_code VARCHAR2(100), express_no VARCHAR2(100), expressno VARCHAR2(100) );
创建药师帮用户
CREATE USER ysbuser IDENTIFIED BY ysbpwd;
建立药师帮视图
–1.库存(库存 栏)取两个字段：H2.CV_YSB_WAREQTY;
create or replace view cv_ysb_wareqty as
select "WARECODE", "WAREQTY", "NEWWHLPRICE"
from (select c.warecode as warecode,
sum(a.wareqty - a.awaitqty - a.pendingqty) as wareqty,
d.setwhlprice1 AS newwhlprice --批发价
from t_store_d a, t_store_i b, t_ware c
left join v_ware_whlprice d
on c.wareid = d.wareid
left join s_whlprice_group e
on e.compid = d.compid
and e.whlgroupid = d.whlgroupid
where a.compid = '1'
and a.busno = '1000'
and a.wareqty - a.awaitqty - a.pendingqty > 0
and a.wareid = b.wareid
and a.compid = b.compid
and a.batid = b.batid
and a.wareid = c.wareid
and b.wareid = c.wareid
and b.compid = c.compid
and c.status = '1'
and d.whlgroupid = '12' --价格只取药师帮价格组的
and b.Ownerid = '01' --只取电商货主的货
-- and b.flag1 = 0
/*and not exists
(select 1
from T_QUALITY_CHECK_H qh, t_quality_check_d qd
where qd.checkno = qh.checkno
and qh.status = 1
and qh.compid = a.compid
and qh.checktype = 1
AND (qh.objbusnos = '全部' OR
instr(',' || qh.objbusnos || ',',
',' || to_char(a.busno) || ',') > 0)
and qd.wareid = a.wareid
and (qd.makeno = '%' OR nvl(qd.makeno, nvl(b.makeno, '%')) =
nvl(b.makeno, '%'))
AND (nvl(qd.batid, nvl(a.batid, 0)) = nvl(a.batid, 0) or
qd.batid = 0))*/
-- and b.flag1 = 0 --质量复核锁定
group by c.warecode, d.setwhlprice1
order by c.warecode) a
--where a.newwhlprice > 0 --判断批发价大于0;
–2.价格（价格 栏）取三个字段，编码跟单体价为必须值：H2.CV_YSB_WARE_WHLPRICE;
CREATE OR REPLACE VIEW CV_YSB_WARE_WHLPRICE AS
SELECT t_ware_whlprice.compid AS compid,
t_ware_whlprice.whlgroupid AS whlgroupid,
t_ware_whlprice.wareid AS wareid,
t_ware_base.warecode AS warecode,
t_ware_base.warename AS warename,
t_ware_whlprice.setwhlprice1 AS setwhlprice1111,
CASE WHEN t_ware_whlprice.setwhlprice1 = 0 THEN CASE
WHEN f_get_sys_inicode(t_ware_whlprice.compid, '2795', NULL) = '1' THEN
t_ware.lastpurprice
ELSE
t_ware_whlprice.setwhlprice1
END * (1 + (CASE
WHEN f_get_sys_inicode(t_ware_whlprice.compid, '6030', NULL) = '1' THEN
(CASE
WHEN nvl(t_ware_whlprice.purtax, 0) = 0 THEN
nvl(t_ware_whlprice.purtax, 0)
ELSE
swg.purtax
END)
ELSE
nvl(t_ware_whlprice.purtax, 0)
END) / 100) ELSE setwhlprice1 END AS setwhlprice1,
t_ware_whlprice.setwhlprice2 AS setwhlprice2,
t_ware_whlprice.setwhlprice3 AS setwhlprice3,
t_ware_whlprice.setwhlprice4 AS setwhlprice4,
t_ware_whlprice.setwhlprice5 AS setwhlprice5,
t_ware_whlprice.leastwhlprice AS leastwhlprice,
t_ware_whlprice.maxwhlprice AS maxwhlprice,
t_ware_whlprice.purtax AS purtax
FROM t_ware_whlprice t_ware_whlprice
INNER JOIN t_ware_base t_ware_base
ON t_ware_whlprice.wareid = t_ware_base.wareid
JOIN t_ware t_ware
ON t_ware.compid=1
AND t_ware.wareid=t_ware_base.wareid
LEFT JOIN s_whlprice_group swg
ON swg.compid = t_ware_whlprice.compid
AND swg.whlgroupid = t_ware_whlprice.whlgroupid
WHERE ( CASE WHEN t_ware_whlprice.setwhlprice1 = 0 THEN CASE
WHEN f_get_sys_inicode(t_ware_whlprice.compid, '2795', NULL) = '1' THEN
t_ware.lastpurprice
ELSE
t_ware_whlprice.setwhlprice1
END * (1 + (CASE
WHEN f_get_sys_inicode(t_ware_whlprice.compid, '6030', NULL) = '1' THEN
(CASE
WHEN nvl(t_ware_whlprice.purtax, 0) = 0 THEN
nvl(t_ware_whlprice.purtax, 0)
ELSE
swg.purtax
END)
ELSE
nvl(t_ware_whlprice.purtax, 0)
END) / 100) ELSE setwhlprice1 END)>0 ---只同步商品有批发价格的
and t_ware_whlprice.WHLGROUPID in ('12') ---价格组控制（只同步批发默认价格组价格）
and t_ware_whlprice.compid = 1
ORDER BY t_ware_whlprice.compid,
t_ware_whlprice.wareid,
t_ware_whlprice.whlgroupid;
–3.（多价格体系 栏）如果需要上传多个价格 h2.cv_ysb_djpurprice_new;
create or replace view cv_ysb_djpurprice_new as
select distinct c.warecode,b.purprice
from (select ti.wareid as wareid, min(ti.batid) as batid
from t_store_i ti
left join t_store_d td
on ti.batid =td.batid
and ti.wareid = td.wareid
where ti.ownerid = '01'
-- and td.stallno in ('0101','0104')
and (td.wareqty-td.awaitqty-td.pendingqty)>0
and ti.compid = 1
group by ti.wareid) a
left join t_store_i b
on a.wareid = b.wareid
and a.batid = b.batid
left join t_ware c
on a.wareid = c.wareid
where b.compid = 1
;
–4.药品效期（药品批号 栏）取四个字段：H2.CV_YSB_WARE_STORE_I;
CREATE OR REPLACE VIEW CV_YSB_WARE_STORE_I AS
SELECT vw.warecode,---商品编码
i.makeno, ---批号
i.invalidate, --有效期至
i.makedate --生产日期
FROM t_store_d d
JOIN t_ware vw
ON d.wareid = vw.wareid
AND d.compid = vw.compid
LEFT JOIN t_store_i i
ON d.compid = i.compid
AND d.wareid = i.wareid
AND d.batid = i.batid
WHERE (d.wareqty-d.awaitqty-d.pendingqty)>0 ---可用数量大于0
and d.compid=1 and d.busno='1000' ---只传输批发公司的库存
and i.invalidate>SYSDATE ---有效期至大于当前时间（过效期不同步）
and i.Ownerid = '01'
group by vw.warecode,i.invalidate,i.makeno,i.makedate;
–5.药品信息 ！！！商品信息同步仅供一键上架功能使用，不更新现有上架品种信息 h2.cv_ysb_ware;
CREATE OR REPLACE VIEW CV_YSB_WARE AS
SELECT rtrim(w.warecode) AS drugcode, --药品编码(字符串）--同其他语句使用的商品编码字段 *
rtrim(w.warename) AS drugname, --商品名称(字符串） *
rtrim(w.warespec) AS pack, --商品规格(字符串） *
rtrim(f.factoryname) AS factory, --生产厂家(字符串） *
rtrim(w.wareunit) AS unit, --单位 中文药品单位，如盒、包、箱等(字符串） *
rtrim(w.barcode) AS barcode, --商品条形码(字符串）（*最好有）
rtrim(w.fileno) AS approval, --批准文号，国药准字H20103180(字符串） *
rtrim(tcb.classcode) AS busitype, --经营类别（*最好有）（字符串）
sh.wareqty AS stock, --库存（数字）*（取仓库的库存,用商品表left join库存表取值，保证所有品种可取到）
nvl(tww.setwhlprice1, 9999) AS price, --价格（数字）*（取值与价格同步语句相同，不同步价格可定9999）
1 AS step, --购买增量、步长(数字类型）--即 采购倍数（默认填1）
w.saletax AS taxrate, --税率（数字）
to_char(w.midqty) AS midpack, --中包装数（数字）
to_char(w.maxqty) AS wholepack, --整包装数（数字）
w.lastsaleprice AS recommendedprice --建议零售价（数字）
FROM t_ware w
LEFT JOIN t_factory f
ON f.factoryid = w.factoryid
LEFT JOIN t_ware_class_base twcb
ON twcb.compid = w.compid
AND twcb.wareid = w.wareid
AND twcb.classgroupno = '18'
LEFT JOIN t_class_base tcb
ON tcb.classgroupno = twcb.classgroupno
AND tcb.classcode = twcb.classcode
LEFT JOIN (SELECT d.compid,
d.wareid,
SUM(d.wareqty - d.awaitqty - d.pendingqty) AS wareqty
FROM t_store_d d, t_store_i i,t_stall ts
WHERE d.wareid = i.wareid
AND d.batid = i.batid
--AND d.stallno IN ('0101', '0104')
AND ts.compid=d.compid AND ts.busno=d.busno AND ts.stallno=d.stallno
AND ts.stalltype IN ('11')
AND i.ownerid = '01'
group by d.compid,d.wareid) sh
ON sh.compid = w.compid
AND sh.wareid = w.wareid
LEFT JOIN t_ware_whlprice tww
ON tww.compid = w.compid
AND tww.wareid = w.wareid
AND tww.whlgroupid = '12'----价格组可能会有变更
WHERE w.compid = 1
AND w.salestatus = 1
AND w.status = 1
AND w.warekind <> 3;
–6.药店信息 现在只能同步药店编码用于订单对接 H2.CV_YSB_VENCUS;
CREATE OR REPLACE VIEW CV_YSB_VENCUS AS
SELECT t_vencus.vencuscode,--药店编码 --这个值会同步为平台上录入的药店编码，选在界面上可视的那个或之前同步订单用的那个
t_vencus.vencusname,
t_vencus.address
---缺少 --系统有存放平台客户id的，才上传这个字段
FROM t_vencus t_vencus
WHERE t_vencus.status = 1
AND t_vencus.compid=1
and t_vencus.vencusflag in (2,0) --只取仅客户 与 供应商与客户
---暂未控制仅电商客户
order by t_vencus.vencuscode;
–7.上传快递，工具9-2栏 “订单快递” h2.cv_ysb_express;
create or replace view cv_ysb_express as
select
b.djbh as orderId,
--nvl(b.express_code,0) as logisticsId,
case when substr(b.expressno,1,2) = 'SF' then 1 -----快递
when substr(b.expressno,1,2) = 'JD' then 10194 end as logisticsId,
nvl(b.expressno,'') as logisticsNo,
'' as invoiceCode,
'' as invoiceNo,
'' as securityCode,
'' as invoiceUrl,
'' as status,
'' as invoiceStatus
from h2.d_batsale_ext b
where b.expressno is not null
and b.btbbillcode = '1'
order by b.djbh;
–8.上传发票，工具9-3栏 “订单发票”h2.cv_erp2pt_fp;
create or replace view cv_erp2pt_fp as
select aa.batsaleno as batsaleno,
aa.billsource as billsource,
aa.djbh as djbh,
nvl(bb.invoicetenno, '') as invoicetenno,
nvl(bb.invoicelist, '') as invoicelist,
'' as securitycode,
nvl(bb.pdf_url, ' ') as pdf_url,
bb.invoicedate as invoicedate
from (select a.invoicepayno, b.batsaleno, b.djbh, b.compid, b.billsource
from t_invoice_pay_d a, t_batsale_h b
where a.billno = b.batsaleno
and b.djbh is not null
and a.billcode = 'WHL'
and b.compid = 1) aa
left join t_invoice_pay_h bb
on aa.invoicepayno = bb.invoicepayno
and aa.compid = bb.compid
where bb.invoicelist is not null
--and bb.invoicetenno is not null
and bb.pdf_url is not null
and aa.billsource is not NULL
AND TRUNC(bb.execdate)>=SYSDATE-3
group by aa.batsaleno,
aa.djbh,
bb.invoicelist,
bb.invoicetenno,
bb.pdf_url,
aa.billsource,
bb.invoicedate;
创建药师帮表
–9.订单状态回传，工具8-8栏 h2.ysb_ddmx_writeback;
-- Create table
create table YSB_DDMX_WRITEBACK
(
orderid VARCHAR2(15) not null,
ordercode VARCHAR2(40),
dj_sn NUMBER(5),
drugcode VARCHAR2(40),
amount NUMBER(16,6),
price NUMBER(16,6),
je NUMBER(16,6),
batchnum VARCHAR2(20),
proddate VARCHAR2(10),
validity VARCHAR2(10),
status NUMBER(5) not null
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
–10.批发申请单 主表明细表 t_batsaleapply_h t_batsaleapply_d(需要药师帮生成批发申请单才需要这一步)
grant select, insert, update, delete, references, alter, index, debug on T_BATSALEAPPLY_H to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on T_BATSALEAPPLY_D to YSBUSER;
其他表
H2_BATSALE_D
create table H2_BATSALE_D
(
djbh VARCHAR2(100) not null,
dj_sn NUMBER(30) not null,
drugcode VARCHAR2(40) not null,
shl NUMBER(14,2),
dj NUMBER(14,4),
je NUMBER(14,4),
batchnum VARCHAR2(20),
proddate VARCHAR2(10),
validity VARCHAR2(10),
status VARCHAR2(20),
wholesale_type NUMBER(5) default 0,
is_zx VARCHAR2(10) default '否',
billsource VARCHAR2(20) not null,
salebs VARCHAR2(120),
shop_amount NUMBER(14,4) default 0.00,
platform_amount NUMBER(14,4) default 0.00,
shl_old NUMBER(14,2)
)
tablespace H2_TEST
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table H2_BATSALE_D
is 'H2订单明细表中间表';
-- Add comments to the columns
comment on column H2_BATSALE_D.djbh
is '单据编码';
comment on column H2_BATSALE_D.dj_sn
is '明细序号';
comment on column H2_BATSALE_D.drugcode
is '药品编码';
comment on column H2_BATSALE_D.shl
is '数量';
comment on column H2_BATSALE_D.dj
is '单价';
comment on column H2_BATSALE_D.je
is '金额';
comment on column H2_BATSALE_D.batchnum
is '药品批号';
comment on column H2_BATSALE_D.proddate
is '药品生产日期';
comment on column H2_BATSALE_D.validity
is '药品有效期';
comment on column H2_BATSALE_D.status
is '药品明细状态';
comment on column H2_BATSALE_D.wholesale_type
is '活动类型:0-返现活动, 1-一口价, 2-限量秒杀, 3-折扣活动
4-一口价特价产品(不可用商家优惠券) 5-限时特价不可用商家券 6-限时特价可用商家券 7-药慧拼（拼团） 8-批购包邮 9-乐药分销 10-赠品';
comment on column H2_BATSALE_D.is_zx
is '是否执行';
comment on column H2_BATSALE_D.billsource
is '单据来源(1 药师帮/2 一药城/3 小药药/4 委托配送-和美/5 委托配送同济)';
comment on column H2_BATSALE_D.salebs
is '销售渠道：1 药师帮/2 一药城/3 小药药/=B；4 委托配送-和美/5 委托配送同济=C';
comment on column H2_BATSALE_D.shop_amount
is '商品店铺优惠金额';
comment on column H2_BATSALE_D.platform_amount
is '商品平台优惠金额';
comment on column H2_BATSALE_D.shl_old
is '原订单数量';
-- Create/Recreate primary, unique and foreign key constraints
alter table H2_BATSALE_D
add constraint PK_H2_BATSALE_D primary key (DJBH, DJ_SN, BILLSOURCE)
using index
tablespace H2_TEST_INDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
H2_BATSALE_H
create table H2_BATSALE_H
(
billsource VARCHAR2(20) not null,
djbh VARCHAR2(100) not null,
rq VARCHAR2(10),
ontime VARCHAR2(8),
drugstorebranchid NUMBER(20),
customerid VARCHAR2(40) not null,
drugstorename VARCHAR2(120),
status VARCHAR2(20),
je NUMBER(14,4),
xgdjbh VARCHAR2(40),
beizhu VARCHAR2(512),
is_zx VARCHAR2(10) default '否',
invoicetype VARCHAR2(10) default '0',
address VARCHAR2(255),
recvname VARCHAR2(80),
recvphone VARCHAR2(50),
provincename VARCHAR2(80),
cityname VARCHAR2(80),
districtname VARCHAR2(120),
streetname VARCHAR2(120),
flag NUMBER(1) default 0,
err VARCHAR2(2000),
bm VARCHAR2(2000),
xsbs VARCHAR2(2000),
is_invalid NUMBER(1) default 0,
djbh_id VARCHAR2(80),
lasttime DATE
)
tablespace H2_TEST
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 8K
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table H2_BATSALE_H
is 'H2订单汇总中间表';
-- Add comments to the columns
comment on column H2_BATSALE_H.billsource
is '单据来源(1 药师帮/2 一药城/3 小药药/4 委托配送-和美/5 委托配送同济)';
comment on column H2_BATSALE_H.djbh
is '单据编号，平台上订单号';
comment on column H2_BATSALE_H.rq
is '日期';
comment on column H2_BATSALE_H.ontime
is '时间';
comment on column H2_BATSALE_H.drugstorebranchid
is '平台客户id';
comment on column H2_BATSALE_H.customerid
is '单位（药店）编码（平台上录入的药店编码值）';
comment on column H2_BATSALE_H.drugstorename
is '单位（药店）名称（平台上的药店名）';
comment on column H2_BATSALE_H.status
is '状态（可不回写）';
comment on column H2_BATSALE_H.je
is '订单总金额';
comment on column H2_BATSALE_H.xgdjbh
is '相关单据编号，随货配送的配送单号（药批ERP系统上的订单编号，可回写以关联药师帮订单和ERP订单）（可不回写）';
comment on column H2_BATSALE_H.beizhu
is '备注，格式为订单编码 + 订单类型（爆款、自营）+ 客户留言';
comment on column H2_BATSALE_H.is_zx
is '是否执行（可不回写）';
comment on column H2_BATSALE_H.invoicetype
is '-发票类型（0 缺省，1 普通发票，2 专用发票）';
comment on column H2_BATSALE_H.address
is '收货地址';
comment on column H2_BATSALE_H.recvname
is '收货人';
comment on column H2_BATSALE_H.recvphone
is '收货人电话';
comment on column H2_BATSALE_H.provincename
is '省';
comment on column H2_BATSALE_H.cityname
is '城市';
comment on column H2_BATSALE_H.districtname
is '区/县';
comment on column H2_BATSALE_H.streetname
is '街道';
comment on column H2_BATSALE_H.flag
is '接收标志(0/未接收,1/接收成功,2/接收失败)';
comment on column H2_BATSALE_H.err
is '错误信息';
comment on column H2_BATSALE_H.bm
is '部门';
comment on column H2_BATSALE_H.xsbs
is '销售标识';
comment on column H2_BATSALE_H.is_invalid
is '是否作废';
-- Create/Recreate primary, unique and foreign key constraints
alter table H2_BATSALE_H
add constraint PK_H2_BATSALE_H primary key (DJBH, BILLSOURCE)
using index
tablespace H2_TEST_INDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
YSB_DDHZ
create table YSB_DDHZ
(
djbh VARCHAR2(15) not null,
rq VARCHAR2(10),
ontime VARCHAR2(8),
drugstorebranchid NUMBER(20),
customerid VARCHAR2(40) not null,
drugstorename VARCHAR2(120),
status VARCHAR2(20),
je NUMBER(14,4),
xgdjbh VARCHAR2(40),
beizhu VARCHAR2(512),
is_zx VARCHAR2(10) default '否',
invoicetype VARCHAR2(10) default '0',
address VARCHAR2(255),
recvname VARCHAR2(80),
recvphone VARCHAR2(50),
provincename VARCHAR2(80),
cityname VARCHAR2(80),
districtname VARCHAR2(120),
streetname VARCHAR2(120),
sync_status VARCHAR2(10) default '0',
sync_msg VARCHAR2(520),
sync_date DATE
)
tablespace H2_BUSI
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table YSB_DDHZ
add primary key (DJBH)
using index
tablespace H2_BUSI
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
YYB_DDMX
-- Create table
create table YSB_DDMX
(
djbh VARCHAR2(15) not null,
dj_sn NUMBER(5) not null,
drugcode VARCHAR2(120) not null,
shl NUMBER(16,6),
dj NUMBER(16,6),
je NUMBER(16,6),
batchnum VARCHAR2(20),
proddate VARCHAR2(10),
validity VARCHAR2(10),
status VARCHAR2(20),
wholesale_type NUMBER(5) default 0,
is_zx VARCHAR2(10) default '否' --是否执行（可不回写）,
wholesaleid NUMBER(15)
)
tablespace H2DB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column YSB_DDMX.wholesale_type
is '活动类型:0-返现活动, 1-一口价, 2-限量秒杀, 3-折扣活动 4-一口价特价产品(不可用商家优惠券) 5-限时特价不可用商家券 6-限时特价可用商家券 7-药慧拼（拼团） 8-批购包邮 9-乐药分销 10-赠品';
-- Create/Recreate primary, unique and foreign key constraints
alter table YSB_DDMX
add constraint PK_MX primary key (DJBH, DJ_SN)
using index
tablespace H2DB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
YSB_DRUGSTOREINFO
create table YSB_DRUGSTOREINFO
(
drugstoreid NUMBER not null,
customerid VARCHAR2(50),
drugstorename VARCHAR2(255),
provincename VARCHAR2(100),
cityname VARCHAR2(100),
districtname VARCHAR2(100),
regadress VARCHAR2(255),
drugstoretype VARCHAR2(10),
invoicetype VARCHAR2(10),
busiscope VARCHAR2(255),
otcscope VARCHAR2(255),
fullbusinessscope VARCHAR2(2000),
salesmanphone VARCHAR2(75),
salesmanname VARCHAR2(75),
receivername VARCHAR2(75),
receiverphone VARCHAR2(75),
firstordertime VARCHAR2(20),
areacode VARCHAR2(10),
be_papers_syn NUMBER default 0,
busicardurl VARCHAR2(1500),
busicardno VARCHAR2(120),
busicardissue VARCHAR2(10),
busicardvalid VARCHAR2(10),
proxyopername VARCHAR2(75),
busicardname VARCHAR2(300),
busicardaddress VARCHAR2(300),
busicardcertifiunit VARCHAR2(75),
drugbusiurl VARCHAR2(1500),
drugbusitcardno VARCHAR2(120),
drugbusiissue VARCHAR2(10),
drugbusivalidity VARCHAR2(10),
drugbusiproxyopername VARCHAR2(75),
qualitymanager VARCHAR2(75),
drugbusiprincipal VARCHAR2(300),
drugbusiname VARCHAR2(300),
drugbusiaddress VARCHAR2(300),
drugbusicertifiunit VARCHAR2(75),
purchasepaperurl VARCHAR2(1500),
purchasepaperno VARCHAR2(120),
purchasepaperissue VARCHAR2(10),
purchasepapervalid VARCHAR2(10),
sellerphone VARCHAR2(50),
selidentityurl VARCHAR2(1500),
selidentityissue VARCHAR2(10),
selidentityvalid VARCHAR2(10),
selidentityno VARCHAR2(120),
sellername VARCHAR2(75),
changerecordurl VARCHAR2(1500),
hisorgcardurl VARCHAR2(1500),
hisorgcardno VARCHAR2(120),
hisorgcardissue VARCHAR2(10),
hisorgcardvalid VARCHAR2(10),
hisorgcardproxyopername VARCHAR2(75),
hisorgcardprincipal VARCHAR2(75),
hisorgcardname VARCHAR2(300),
hisorgcardaddress VARCHAR2(300),
hisorgcardcertifiunit VARCHAR2(75),
hiseqrecordurl VARCHAR2(1500),
hiseqrecord VARCHAR2(120),
hiseqrecordissue VARCHAR2(10),
hiseqrecordvalid VARCHAR2(10),
hiseqbusitcardurl VARCHAR2(1500),
hiseqbusitcardno VARCHAR2(120),
hiseqbusitcardissue VARCHAR2(10),
hiseqbusitcardvalid VARCHAR2(10),
foodbusiurl VARCHAR2(1500),
foodbusino VARCHAR2(120),
foodbusiissue VARCHAR2(10),
foodbusivalid VARCHAR2(10),
invoiceinfourl VARCHAR2(1500),
invoiceinfoname VARCHAR2(300),
bankname VARCHAR2(200),
bankcardno VARCHAR2(75),
taxno VARCHAR2(75),
invoiceinfophone VARCHAR2(75),
invoiceinfoaddress VARCHAR2(300),
receiverpaperurl VARCHAR2(1500),
receiverpapername VARCHAR2(75),
receiverpaperphone VARCHAR2(75),
receiverpapervalid VARCHAR2(10),
is_tq VARCHAR2(20) default '否',
erpcustomerid VARCHAR2(75) default '',
iseinvoice VARCHAR2(10),
lastordertime VARCHAR2(20),
isdownload NUMBER(1) default 0 not null,
mtimetimestamp VARCHAR2(30),
lastpaperupdatetime VARCHAR2(30),
updateflag VARCHAR2(10),
sync_status NUMBER(1) default 0,
sync_msg VARCHAR2(400),
sync_date DATE,
gspurl VARCHAR2(1000),
foodbusitcardno VARCHAR2(100),
gspvalidity VARCHAR2(100),
gspissue VARCHAR2(100),
gspcardno VARCHAR2(100)
)
tablespace H2_BUSI
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column YSB_DRUGSTOREINFO.drugstoreid
is '药店ID';
comment on column YSB_DRUGSTOREINFO.customerid
is '药店编码';
comment on column YSB_DRUGSTOREINFO.drugstorename
is '客户名称';
comment on column YSB_DRUGSTOREINFO.provincename
is '省份';
comment on column YSB_DRUGSTOREINFO.cityname
is '城市';
comment on column YSB_DRUGSTOREINFO.districtname
is '区县';
comment on column YSB_DRUGSTOREINFO.regadress
is '收货地址';
comment on column YSB_DRUGSTOREINFO.drugstoretype
is '药店类型 0-零售单体 1-第三终端 2-连锁直营 3-连锁加盟 4-连锁总部 5-商业公司';
comment on column YSB_DRUGSTOREINFO.invoicetype
is '开票类型 0未设置 1-普票 2-专票 3-无要求';
comment on column YSB_DRUGSTOREINFO.busiscope
is '商品经营范围 商品经营范围（用,分隔） 1-中成药 2-化学药制剂 3-抗生素制剂
--4-生物制品（除注射剂） 5-中药饮片 6-医疗器械（二类） 7-医疗器械（三类）
--8-普通食品 9-医疗器械（一类）10-日用品 11-化妆品 12消毒产品 13-全球购
--14-保健品 15-生物制品（仅注射剂） 16-保健食品 17-中药饮片(药食同源)';
comment on column YSB_DRUGSTOREINFO.otcscope
is '处方药经营范围 处方药经营范围（用,分隔） 1-处方药 2-甲类非处方 3-乙类非处方 4-其它等非药品';
comment on column YSB_DRUGSTOREINFO.fullbusinessscope
is '中台详细版药店经营范围(具体字典值参考excel文档)';
comment on column YSB_DRUGSTOREINFO.salesmanphone
is '店员联系电话';
comment on column YSB_DRUGSTOREINFO.salesmanname
is '店员姓名';
comment on column YSB_DRUGSTOREINFO.receivername
is '最近3天内任取一笔订单的收货人姓名，非3天内下单，为null';
comment on column YSB_DRUGSTOREINFO.receiverphone
is '最近3天内任取一笔订单的收货人电话，非3天内下单，为null';
comment on column YSB_DRUGSTOREINFO.firstordertime
is '首单时间，时间戳，格式：1563350366';
comment on column YSB_DRUGSTOREINFO.areacode
is '网上地区编码';
comment on column YSB_DRUGSTOREINFO.be_papers_syn
is '资质是否可以共享:0-否 1-是';
comment on column YSB_DRUGSTOREINFO.busicardurl
is '营业执照图片url';
comment on column YSB_DRUGSTOREINFO.busicardno
is '营业执照编号';
comment on column YSB_DRUGSTOREINFO.busicardissue
is '营业执照营业执照发证日期，格式为：2015-12-30';
comment on column YSB_DRUGSTOREINFO.busicardvalid
is '营业执照有效期，格式为：2015-12-30，或者“长期”';
comment on column YSB_DRUGSTOREINFO.proxyopername
is '营业执照法人代表';
comment on column YSB_DRUGSTOREINFO.busicardname
is '营业执照药店执照名称';
comment on column YSB_DRUGSTOREINFO.busicardaddress
is '营业执照药店注册地址';
comment on column YSB_DRUGSTOREINFO.drugbusiurl
is '药品经营许图片url';
comment on column YSB_DRUGSTOREINFO.drugbusitcardno
is '药品经营许可证编号';
comment on column YSB_DRUGSTOREINFO.drugbusiissue
is '药品经营许可证发证日期，格式为：2015-12-30';
comment on column YSB_DRUGSTOREINFO.drugbusivalidity
is '药品经营许可证有效期，格式为：2015-12-30';
comment on column YSB_DRUGSTOREINFO.drugbusiproxyopername
is '药品经营许可证法人代表';
comment on column YSB_DRUGSTOREINFO.qualitymanager
is '药品经营许可证质量负责人';
comment on column YSB_DRUGSTOREINFO.drugbusiprincipal
is '药品经营许可证主要负责人';
comment on column YSB_DRUGSTOREINFO.drugbusiname
is '药品经营许可证药店许可证名称';
comment on column YSB_DRUGSTOREINFO.purchasepaperurl
is '采购委托书图片url';
comment on column YSB_DRUGSTOREINFO.purchasepaperno
is '采购委托书证件编号';
comment on column YSB_DRUGSTOREINFO.purchasepapervalid
is '采购委托书有效期 (yyyymmdd)';
comment on column YSB_DRUGSTOREINFO.sellerphone
is '采购人身份证电话号码';
comment on column YSB_DRUGSTOREINFO.selidentityurl
is '采购人员身份证图片url';
comment on column YSB_DRUGSTOREINFO.selidentityissue
is '采购人身份证发证日期 (时间戳，格式为：1563350366)';
comment on column YSB_DRUGSTOREINFO.selidentityvalid
is '采购人身份证有效期 （yyyymmdd 或者 长期）';
comment on column YSB_DRUGSTOREINFO.selidentityno
is '采购人员身份证编号';
comment on column YSB_DRUGSTOREINFO.sellername
is '采购人员身份证名字';
comment on column YSB_DRUGSTOREINFO.changerecordurl
is '变更记录图片地址';
comment on column YSB_DRUGSTOREINFO.hisorgcardurl
is '医疗机构执业许可证图片url';
comment on column YSB_DRUGSTOREINFO.hisorgcardno
is '医疗机构执业许可证编号';
comment on column YSB_DRUGSTOREINFO.hisorgcardissue
is '医疗机构执业许可证发证日期，格式为：2015-12-30';
comment on column YSB_DRUGSTOREINFO.hisorgcardvalid
is '医疗机构执业许可证有效日期，格式为：2015-12-30';
comment on column YSB_DRUGSTOREINFO.hisorgcardproxyopername
is '医疗机构执业许可证法人代表';
comment on column YSB_DRUGSTOREINFO.hisorgcardprincipal
is '医疗机构执业许可证主要负责人';
comment on column YSB_DRUGSTOREINFO.hiseqrecordurl
is '二级医疗器械备案凭证图片url';
comment on column YSB_DRUGSTOREINFO.hiseqrecord
is '医疗器械备案凭证编号';
comment on column YSB_DRUGSTOREINFO.hiseqbusitcardurl
is '医疗器械经营企业许可证图片url';
comment on column YSB_DRUGSTOREINFO.hiseqbusitcardno
is '医疗器械经营企业许可证编号';
comment on column YSB_DRUGSTOREINFO.hiseqbusitcardissue
is '医疗器械经营许可证发证日期 (时间戳，格式为：1563350366)';
comment on column YSB_DRUGSTOREINFO.hiseqbusitcardvalid
is '医疗器械经营许可证有效期 (yyyymmdd)';
comment on column YSB_DRUGSTOREINFO.foodbusiurl
is '食品经营许可证图片url';
comment on column YSB_DRUGSTOREINFO.foodbusino
is '食品经营许可证证件编号';
comment on column YSB_DRUGSTOREINFO.foodbusiissue
is '食品经营许可证发证日期 (时间戳，格式为：1563350366)';
comment on column YSB_DRUGSTOREINFO.foodbusivalid
is '食品经营许可证有效期 (yyyymmdd)';
comment on column YSB_DRUGSTOREINFO.invoiceinfourl
is '是否已提取数据';
comment on column YSB_DRUGSTOREINFO.invoiceinfoname
is '开票信息开户户名
,invoiceInfoAddress varchar2(300) -- 开票信息开票地址';
comment on column YSB_DRUGSTOREINFO.bankname
is '开户行';
comment on column YSB_DRUGSTOREINFO.bankcardno
is '银行帐号';
comment on column YSB_DRUGSTOREINFO.taxno
is '税号';
comment on column YSB_DRUGSTOREINFO.invoiceinfophone
is '开票信息开票电话';
comment on column YSB_DRUGSTOREINFO.invoiceinfoaddress
is '开票信息开票地址';
comment on column YSB_DRUGSTOREINFO.erpcustomerid
is '可用于回写对应erp客户id（要存客户唯一值）';
comment on column YSB_DRUGSTOREINFO.lastordertime
is '最后下单时间(时间戳，格式为：1563350366)
)';
comment on column YSB_DRUGSTOREINFO.mtimetimestamp
is '药店信息最近修改时间';
comment on column YSB_DRUGSTOREINFO.lastpaperupdatetime
is '药店证件最近修改时间';
comment on column YSB_DRUGSTOREINFO.updateflag
is '药店更新标志（true-有更新，false-无更新，更新内容：药店名称、药店地址、营业执照、药品经营许可证、医疗机构执业许可证、开票信息、变更记录）';
comment on column YSB_DRUGSTOREINFO.sync_status
is '同步状态';
comment on column YSB_DRUGSTOREINFO.sync_msg
is '同步信息';
comment on column YSB_DRUGSTOREINFO.sync_date
is '同步时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table YSB_DRUGSTOREINFO
add primary key (DRUGSTOREID)
using index
tablespace H2_BUSI
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
H2_VENCUS
create table H2_VENCUS
(
billsource VARCHAR2(20) not null,
drugstoreid NUMBER not null,
customerid VARCHAR2(50),
drugstorename VARCHAR2(255),
provincename VARCHAR2(100),
cityname VARCHAR2(100),
districtname VARCHAR2(100),
regadress VARCHAR2(255),
salesmanphone VARCHAR2(50),
salesmanname VARCHAR2(80),
drugstoretype VARCHAR2(50),
invoicetype VARCHAR2(10),
taxno VARCHAR2(80),
bankname VARCHAR2(150),
bankcardno VARCHAR2(90),
busiscope VARCHAR2(1000),
otcscope VARCHAR2(255),
busicardno VARCHAR2(120),
busicardissue VARCHAR2(60),
busicardvalid VARCHAR2(60),
proxyopername VARCHAR2(100),
drugbusitcardno VARCHAR2(120),
drugbusiissue VARCHAR2(60),
drugbusivalidity VARCHAR2(60),
qualitymanager VARCHAR2(100),
gspcardno VARCHAR2(120),
gspissue VARCHAR2(60),
gspvalidity VARCHAR2(60),
hisorgcardno VARCHAR2(120),
hisorgcardissue VARCHAR2(60),
hisorgcardvalid VARCHAR2(60),
foodbusitcardno VARCHAR2(120),
hiseqbusitcardno VARCHAR2(120),
selidentityno VARCHAR2(120),
sellername VARCHAR2(200),
hiseqrecord VARCHAR2(120),
firstordertime VARCHAR2(120),
busicardurl VARCHAR2(1000),
drugbusiurl VARCHAR2(1000),
selidentityurl VARCHAR2(1000),
purchasepaperurl VARCHAR2(1000),
gspurl VARCHAR2(1000),
hiseqrecordurl VARCHAR2(1000),
hiseqbusitcardurl VARCHAR2(1000),
foodbusiurl VARCHAR2(1000),
hisorgcardurl VARCHAR2(1000),
is_tq VARCHAR2(10) default '否',
invoiceinfourl VARCHAR2(1000),
changerecordurl VARCHAR2(1000),
areacode NUMBER,
mtimetimestamp VARCHAR2(30),
lastpaperupdatetime VARCHAR2(30),
updateflag VARCHAR2(10),
busicardname VARCHAR2(300),
busicardaddress VARCHAR2(300),
drugbusiproxyopername VARCHAR2(150),
drugbusiprincipal VARCHAR2(200),
drugbusiname VARCHAR2(300),
purchasepapervalid VARCHAR2(30),
purchasepaperno VARCHAR2(150),
selidentityissue VARCHAR2(30),
selidentityvalid VARCHAR2(100),
sellerphone VARCHAR2(100),
foodbusiissue VARCHAR2(30),
foodbusivalid VARCHAR2(30),
foodbusino VARCHAR2(150),
invoiceinfoname VARCHAR2(300),
invoiceinfoaddress VARCHAR2(300),
invoiceinfophone VARCHAR2(100),
hisorgcardproxyopername VARCHAR2(100),
hisorgcardprincipal VARCHAR2(100),
hiseqbusitcardissue VARCHAR2(30),
hiseqbusitcardvalid VARCHAR2(30),
be_papers_syn NUMBER default 0,
fullbusinessscope VARCHAR2(2000),
erpcustomerid VARCHAR2(100),
receivername VARCHAR2(75),
receiverphone VARCHAR2(75),
lastordertime VARCHAR2(30),
flag NUMBER(1),
err VARCHAR2(1000)
)
tablespace H2_TEST
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table H2_VENCUS
is 'H2客户信息表';
-- Add comments to the columns
comment on column H2_VENCUS.billsource
is '单据来源(1 药师帮/2 一药城/3 小药药/4 委托配送-和美/5 委托配送-同济)';
comment on column H2_VENCUS.drugstoreid
is '药店ID';
comment on column H2_VENCUS.customerid
is '药店编码';
comment on column H2_VENCUS.drugstorename
is '客户名称';
comment on column H2_VENCUS.provincename
is '省份';
comment on column H2_VENCUS.cityname
is '城市';
comment on column H2_VENCUS.districtname
is '区县';
comment on column H2_VENCUS.regadress
is '收货地址';
comment on column H2_VENCUS.salesmanphone
is '店员联系电话-委托书上的';
comment on column H2_VENCUS.salesmanname
is '店员姓名-委托书上的';
comment on column H2_VENCUS.drugstoretype
is '药店类型 0-零售单体 1-第三终端 2-连锁直营 3-连锁加盟 4-连锁总部 5-商业公司';
comment on column H2_VENCUS.invoicetype
is '开票类型 0未设置 1-普票 2-专票 3-无要求';
comment on column H2_VENCUS.taxno
is '税号';
comment on column H2_VENCUS.bankname
is '开户行';
comment on column H2_VENCUS.bankcardno
is '银行帐号';
comment on column H2_VENCUS.busicardno
is '营业执照编号';
comment on column H2_VENCUS.busicardissue
is '营业执照营业执照发证日期，格式为：2015-12-30';
comment on column H2_VENCUS.busicardvalid
is '营业执照有效期，格式为：2015-12-30，或者“长期”';
comment on column H2_VENCUS.proxyopername
is '营业执照法人代表';
comment on column H2_VENCUS.drugbusitcardno
is '药品经营许可证编号';
comment on column H2_VENCUS.drugbusiissue
is '药品经营许可证发证日期';
comment on column H2_VENCUS.drugbusivalidity
is '药品经营许可证有效期';
comment on column H2_VENCUS.qualitymanager
is '药品经营许可证质量负责人';
comment on column H2_VENCUS.gspcardno
is 'GSP证书编号';
comment on column H2_VENCUS.gspissue
is 'GSP发证日期';
comment on column H2_VENCUS.gspvalidity
is 'GSP有效期';
comment on column H2_VENCUS.hisorgcardno
is '医疗机构执业许可证编号';
comment on column H2_VENCUS.hisorgcardissue
is '医疗机构执业许可证发证日期';
comment on column H2_VENCUS.hisorgcardvalid
is '医疗机构执业许可证有效日期';
comment on column H2_VENCUS.foodbusitcardno
is '食品流通许可证编号';
comment on column H2_VENCUS.hiseqbusitcardno
is '医疗器械经营企业许可证编号';
comment on column H2_VENCUS.selidentityno
is '采购人员身份证编号';
comment on column H2_VENCUS.sellername
is '采购人员身份证名字';
comment on column H2_VENCUS.hiseqrecord
is '医疗器械备案凭证编号';
comment on column H2_VENCUS.busicardurl
is '营业执照图片url';
comment on column H2_VENCUS.drugbusiurl
is '药品经营许图片url';
comment on column H2_VENCUS.selidentityurl
is '采购人员身份证图片url';
comment on column H2_VENCUS.purchasepaperurl
is '采购委托书图片url';
comment on column H2_VENCUS.gspurl
is 'GSP证书图片url';
comment on column H2_VENCUS.hiseqrecordurl
is '二级医疗器械备案凭证图片url';
comment on column H2_VENCUS.hiseqbusitcardurl
is '医疗器械经营企业许可证图片url';
comment on column H2_VENCUS.foodbusiurl
is '食品经营许可证图片url';
comment on column H2_VENCUS.hisorgcardurl
is '医疗机构执业许可证图片url';
comment on column H2_VENCUS.is_tq
is '是否已提取数据';
comment on column H2_VENCUS.invoiceinfourl
is '开票信息图片url';
comment on column H2_VENCUS.changerecordurl
is '变更记录图片地址';
comment on column H2_VENCUS.areacode
is '网上地区编码';
comment on column H2_VENCUS.mtimetimestamp
is '药店信息最近修改时间';
comment on column H2_VENCUS.lastpaperupdatetime
is '药店证件最近修改时间';
comment on column H2_VENCUS.updateflag
is '药店更新标志（true-有更新，false-无更新，更新内容：药店名称、药店地址、营业执照、药品经营许可证、医疗机构执业许可证、开票信息、变更记录）';
comment on column H2_VENCUS.busicardname
is '营业执照药店执照名称';
comment on column H2_VENCUS.busicardaddress
is '营业执照药店注册地址';
comment on column H2_VENCUS.drugbusiproxyopername
is '药品经营许可证法人代表';
comment on column H2_VENCUS.drugbusiprincipal
is '药品经营许可证主要负责人';
comment on column H2_VENCUS.drugbusiname
is '药品经营许可证药店许可证名称';
comment on column H2_VENCUS.purchasepapervalid
is '采购委托书有效期 (yyyymmdd) ';
comment on column H2_VENCUS.purchasepaperno
is '采购委托书证件编号';
comment on column H2_VENCUS.selidentityissue
is '采购人身份证发证日期';
comment on column H2_VENCUS.selidentityvalid
is '采购人身份证有效期';
comment on column H2_VENCUS.sellerphone
is '采购人身份证电话号码';
comment on column H2_VENCUS.foodbusiissue
is '食品经营许可证发证日期';
comment on column H2_VENCUS.foodbusivalid
is '食品经营许可证有效期';
comment on column H2_VENCUS.foodbusino
is '食品经营许可证证件编号';
comment on column H2_VENCUS.invoiceinfoname
is '开票信息开户户名';
comment on column H2_VENCUS.invoiceinfoaddress
is '开票信息开票地址';
comment on column H2_VENCUS.invoiceinfophone
is '开票信息开票电话';
comment on column H2_VENCUS.hisorgcardproxyopername
is '医疗机构执业许可证法人代表';
comment on column H2_VENCUS.hisorgcardprincipal
is '医疗机构执业许可证主要负责人';
comment on column H2_VENCUS.hiseqbusitcardissue
is '医疗器械经营许可证发证日期';
comment on column H2_VENCUS.hiseqbusitcardvalid
is '医疗器械经营许可证有效期';
comment on column H2_VENCUS.fullbusinessscope
is '中台详细版药店经营范围';
comment on column H2_VENCUS.erpcustomerid
is '可用于回写对应erp客户id';
comment on column H2_VENCUS.receivername
is '最近3天内任取一笔订单的收货人姓名';
comment on column H2_VENCUS.receiverphone
is '最近3天内任取一笔订单的收货人电话';
comment on column H2_VENCUS.lastordertime
is '最后下单时间(时间戳，格式为：1563350366)';
comment on column H2_VENCUS.flag
is '接收标志';
comment on column H2_VENCUS.err
is '错误信息';
-- Create/Recreate primary, unique and foreign key constraints
alter table H2_VENCUS
add primary key (DRUGSTOREID)
using index
tablespace H2_TEST_INDX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
给药师帮用户YSBUSER授权
-- 为用户赋予必要的权限
GRANT CONNECT TO ysbuser;
-- 赋予用户对特定视图的读取权限
GRANT SELECT ON H2.cv_ysb_djpurprice_new TO ysbuser;
GRANT SELECT ON H2.cv_ysb_express TO ysbuser;
GRANT SELECT ON H2.cv_ysb_vencus TO ysbuser;
GRANT SELECT ON H2.cv_ysb_ware TO ysbuser;
GRANT SELECT ON H2.cv_ysb_wareqty TO ysbuser;
GRANT SELECT ON H2.cv_ysb_ware_store_i TO ysbuser;
GRANT SELECT ON H2.cv_ysb_ware_whlprice TO ysbuser;
GRANT SELECT ON H2.cv_erp2pt_fp TO ysbuser;
grant select, insert, update, delete, references, alter, index, debug on H2.H2_BATSALE_D to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on H2.H2_BATSALE_H to YSBUSER;
grant select on H2.H2_VENCUS to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on H2.YSB_DDHZ to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on H2.YSB_DDMX to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on H2.YSB_DDMX_WRITEBACK to YSBUSER;
grant select, insert, update, delete, references, alter, index, debug on H2.YSB_DRUGSTOREINFO to YSBUSER;
创建存储过程
B2B平台信息转中间表
create or replace procedure CPROC_B2B药师帮到海典中间表 is
/*药师帮平台的订单 和客户信息传到海典*/
v_errm VARCHAR2(4000);
begin
-- 平台订单主表 => erp销售中间主表
for rec in (select djbh
from ysb_ddhz a
where nvl(a.sync_status, 0) = 0
and TO_DATE(RQ, 'YYYY-MM-DD') > TO_DATE('2025-03-12', 'YYYY-MM-DD')) loop
begin
insert into H2_Batsale_h
(billsource, --单据来源
djbh, --单据编号
rq, --日期
ontime, --时间
drugstorebranchid, --平台客户ID
customerid, --单位（药店）编码（平台上录入的药店编码值）
drugstorename, --单位（药店）名称（平台上的药店名）
status, --状态（可不回写）
je, --订单总金额
xgdjbh, --相关单据编码
beizhu, --备注
is_zx, --是否执行
invoicetype, --发票类型
address, --收货地址
recvname, --收货人
recvphone, --收货人电话
provincename, --省
cityname, --市
districtname, --区/县
streetname, --街道
flag, --接收标志
err, --报错信息
bm, --部门
xsbs --销售标识
)
select 1, --单据来源
a.djbh, --单据编码
a.rq, --日期
a.ontime, --时间
a.drugstorebranchid, --平台客户编码
a.customerid, --单位（药店）编码
a.drugstorename, --单位（药店）名称
'已提取', --状态
a.je, --金额
a.xgdjbh, --相关单据编号
a.beizhu, --备注
a.is_zx, --是否执行
a.invoicetype, --发票类型
a.address, --收货地址
a.recvname, --收货人
a.recvphone, --联系人电话
a.provincename, --省
a.cityname, --市
a.districtname, --区/县
a.streetname, --街道
0, --接收标志
'', --报错信息
'B2B',
'YSB'
from ysb_ddhz a
where a.djbh = rec.djbh;
-- 平台订单明细表 => erp销售明细中间表
insert into H2_Batsale_d
(djbh, --单据编号
dj_sn, --明细序号
drugcode, --药品编码
shl, --数量
dj, --单价
je, --金额
batchnum, --药品批号
proddate, --药品生产日期
validity, --药品有效期
status, --药品明细状态
wholesale_type, --活动类型
is_zx, --是否执行
billsource, --单据来源
salebs, --销售渠道
SHL_OLD
)
select mx.djbh, --单据编码
mx.dj_sn, --明细序号
mx.drugcode, --药品编码
mx.shl, --数量
mx.dj, --单价
mx.je, --金额
mx.batchnum, --药品批号
mx.proddate, --药品生产日期
mx.validity, --药品有效期
mx.status, --药品明细状态
mx.wholesale_type, --活动类型
'是' ， --是否执行
1, --单据来源
1, --销售渠道
mx.shl
from ysb_ddmx mx
where mx.djbh = rec.djbh;
-- 平台中间表插入erp中间表后，将当前已插入的数据的status（状态）字段修改为‘是已提取’
update ysb_ddhz a
set a.sync_status = 1,
sync_date = SYSDATE,
SYNC_MSG = '同步到ERP中间表H2_Batsale_h成功!'
where nvl(a.sync_status, 0) = 0
and a.djbh = rec.djbh;
exception
when others then
ROLLBACK;
v_errm := '药师帮订单号[' || rec.djbh || '],生成ERP中间表H2_Batsale_h失败:' ||
SQLERRM || chr(10);
--更新状态
UPDATE YSB_DDHZ a
SET sync_status = -1, sync_date = SYSDATE, SYNC_MSG = v_errm
WHERE nvl(a.sync_status, 0) = 0
AND DJBH = rec.DJBH;
COMMIT;
CONTINUE;
end;
commit;
end loop;
-- 平台客户信息表 => erp药店中间表
for rec_c in (select dr.drugstoreid, dr.customerid, dr.drugstorename
from YSB_DRUGSTOREINFO dr
where nvl(dr.sync_status, 0) = 0
and TO_DATE(lastordertime, 'YYYY-MM-DD') > TO_DATE('2025-03-12', 'YYYY-MM-DD')) loop
begin
insert into H2_Vencus
(billsource,
drugstoreid,
customerid,
drugstorename,
provincename,
cityname,
districtname,
regadress,
salesmanphone,
salesmanname,
drugstoretype,
invoicetype,
taxno,
bankname,
bankcardno,
busiscope,
otcscope,
busicardno,
busicardissue,
busicardvalid,
proxyopername,
drugbusitcardno,
drugbusiissue,
drugbusivalidity,
qualitymanager,
gspcardno,
gspissue,
gspvalidity,
hisorgcardno,
hisorgcardissue,
hisorgcardvalid,
foodbusitcardno,
hiseqbusitcardno,
selidentityno,
sellername,
hiseqrecord,
firstordertime,
busicardurl,
drugbusiurl,
selidentityurl,
purchasepaperurl,
gspurl,
hiseqrecordurl,
hiseqbusitcardurl,
foodbusiurl,
hisorgcardurl,
is_tq,
invoiceinfourl,
changerecordurl,
areacode,
mtimetimestamp,
lastpaperupdatetime,
updateflag,
busicardname,
busicardaddress,
drugbusiproxyopername,
drugbusiprincipal,
drugbusiname,
purchasepapervalid,
purchasepaperno,
selidentityissue,
selidentityvalid,
sellerphone,
foodbusiissue,
foodbusivalid,
foodbusino,
invoiceinfoname,
invoiceinfoaddress,
invoiceinfophone,
hisorgcardproxyopername,
hisorgcardprincipal,
hiseqbusitcardissue,
hiseqbusitcardvalid,
be_papers_syn,
fullbusinessscope,
erpcustomerid,
receivername,
receiverphone,
lastordertime,
flag,
err)
select 1, dr.drugstoreid,
dr.customerid,
dr.drugstorename,
dr.provincename,
dr.cityname,
dr.districtname,
dr.regadress,
dr.salesmanphone,
dr.salesmanname,
dr.drugstoretype,
dr.invoicetype,
dr.taxno,
dr.bankname,
dr.bankcardno,
dr.busiscope,
dr.otcscope,
dr.busicardno,
dr.busicardissue,
dr.busicardvalid,
dr.proxyopername,
dr.drugbusitcardno,
dr.drugbusiissue,
dr.drugbusivalidity,
dr.qualitymanager,
dr.gspcardno,
dr.gspissue,
dr.gspvalidity,
dr.hisorgcardno,
dr.hisorgcardissue,
dr.hisorgcardvalid,
dr.foodbusitcardno,
dr.hiseqbusitcardno,
dr.selidentityno,
dr.sellername,
dr.hiseqrecord,
dr.firstordertime,
dr.busicardurl,
dr.drugbusiurl,
dr.selidentityurl,
dr.purchasepaperurl,
dr.gspurl,
dr.hiseqrecordurl,
dr.hiseqbusitcardurl,
dr.foodbusiurl,
dr.hisorgcardurl,
dr.is_tq,
dr.invoiceinfourl,
dr.changerecordurl,
dr.areacode,
dr.mtimetimestamp,
dr.lastpaperupdatetime,
dr.updateflag,
dr.busicardname,
dr.busicardaddress,
dr.drugbusiproxyopername,
dr.drugbusiprincipal,
dr.drugbusiname,
dr.purchasepapervalid,
dr.purchasepaperno,
dr.selidentityissue,
dr.selidentityvalid,
dr.sellerphone,
dr.foodbusiissue,
dr.foodbusivalid,
dr.foodbusino,
dr.invoiceinfoname,
dr.invoiceinfoaddress,
dr.invoiceinfophone,
dr.hisorgcardproxyopername,
dr.hisorgcardprincipal,
dr.hiseqbusitcardissue,
dr.hiseqbusitcardvalid,
dr.be_papers_syn,
dr.fullbusinessscope,
dr.erpcustomerid,
dr.receivername,
dr.receiverphone,
dr.lastordertime,
0,
''
from YSB_DRUGSTOREINFO dr
where dr.DRUGSTOREID = rec_c.DRUGSTOREID;
-- 平台中间表插入erp中间表后，将当前已插入的数据的is_zx（是否执行）字段修改为‘是’
update YSB_DRUGSTOREINFO
set sync_status = 1, sync_date = SYSDATE, SYNC_MSG = '药师帮客户资料[' || rec_c.DRUGSTOREID || '],生成ERP客户首营数据成功:'
where DRUGSTOREID = rec_c.DRUGSTOREID;
exception
when others then
ROLLBACK;
v_errm := '药师帮客户资料[' || rec_c.DRUGSTOREID || '],生成ERP客户首营数据失败:' ||
SQLERRM || chr(10);
--更新状态
UPDATE YSB_DRUGSTOREINFO a
SET sync_status = -1, sync_date = SYSDATE, SYNC_MSG = v_errm
WHERE nvl(a.sync_status, 0) = 0
AND DRUGSTOREID = rec_c.DRUGSTOREID;
COMMIT;
CONTINUE;
end;
commit;
end loop;
end;
B2B中间表客户转首营(新版首营T_CHK_VENDOR)
CREATE OR REPLACE PROCEDURE CPROC_B2B客户转首营(AN_BILLSOURCE IN VARCHAR2) AS
V_COMPID T_VENCUS.COMPID%TYPE;
V_COUNT NUMBER(10);
V_CHECKNO T_CHK_CUSTOMER.CHECKNO%TYPE;
V_VENCUSNO T_VENCUS.VENCUSNO%TYPE;
V_HS NUMBER(10);
V_ERRM VARCHAR2(2000);
BEGIN
FOR REC IN (SELECT *
FROM H2.H2_VENCUS H
WHERE NVL(H.FLAG, 0) = 0
AND TO_DATE(LASTORDERTIME, 'YYYY-MM-DD') > TO_DATE('2025-03-11', 'YYYY-MM-DD')
AND H.BILLSOURCE = AN_BILLSOURCE) LOOP
V_COMPID := 1;
-- 判断客户是否已生成往来单位
SELECT COUNT(*)
INTO V_COUNT
FROM T_VENCUS T
WHERE T.VENCUSNAME = REC.DRUGSTORENAME
AND T.SALENUMBER = REC.TAXNO
AND T.COMPID = V_COMPID;
IF V_COUNT = 0 THEN
-- 判断是否已生成首营企业
SELECT COUNT(*), MAX(VENDORCHKNO)
INTO V_HS, V_CHECKNO
FROM T_CHK_VENDOR T
WHERE T.VENDORNAME = REC.DRUGSTORENAME
AND T.BAK_CHAR3 = REC.TAXNO
AND STATUS <> 2;
IF V_HS = 0 THEN
V_CHECKNO := F_GET_SERIAL('CHV', NULL);
V_VENCUSNO := F_GET_SERIAL('CUS', NULL);
BEGIN
-- 插入首营企业主表
INSERT INTO T_CHK_VENDOR
(vencusflag, /* 首营供应商标志 */
vend_type, /* 供应商类型 */
vendorsubno, /* 助记码 */
VENDORCHKNO, /* 首营供应商编号 */
vendorcode, /* 往来单位自编码 */
VENDORNO, /* 供应商编号 */
VENDORNAME, /* 供应商名称 */
VENDORTYPE, /* 供应商类型 */
BAK_LCHAR10, /* 商品扩展属性长字符10 */
ADDRESS, /* 地址 */
HEADNAME, /* 法人代表 */
POSTCODE, /* 邮编 */
ORDERFAX, /* 传真 */
ORDERNAME, /* 联系人 */
ORDERTEL, /* 联系电话 */
LEECHDOMLIC, /* 许可证编号 */
LEECHDOMDATE, /* 许可证有效期 */
CHKFILENO, /* 文件归档编号 */
GRANT_ORG1, /* 营业执照发证机关 */
BUS_TYPE2, /* 经营方式 */
BAK_CHAR3, /* 字符型备用字段3 */
SCOPE1, /* 营业执照经营范围 */
BUSINESSLIC, /* 营业执照编号 */
BUSINESSDATE, /* 营业执照有效期 */
BAK_CHAR4, /* 字符型备用字段4 */
MANAGER_CHK, /* 领导审核意见 */
REG_SUM1, /* 注册资本 */
BAK_CHAR5, /* 字符型备用字段5 */
QC_TECHNICIAN_COMMENT, /* 质管员意见 */
AREACODE, /* 地区编码 */
SCOPE2, /* 生产经营范围 */
CREATETIME, /* 创建时间 */
CREATEUSER, /* 创建人 */
STATUS, /* 状态 */
BAK_LCHAR09, /* 商品扩展属性长字符9 */
BAK_LCHAR08, /* 商品扩展属性长字符8 */
BUYER_CHK, /* 业务员意见 */
GRANT_ORG3, /* 税务登记证发证机关 */
EXECDATE, /* 生效时间 */
CHECKBIT1, /* 审核标志1 */
CHECKBIT2, /* 审核标志2 */
CHECKBIT3, /* 审核标志3 */
CHECKBIT4, /* 审核标志4 */
CHECKBIT5, /* 审核标志5 */
COMM_NAME, /* 授权人姓名 */
COMM_DATE, /* 授权日期 */
BUYER_DATE, /* 业务员审批时间 */
BUS_DATE, /* 经营部门审核时间 */
GSP_DATE, /* 质检审核时间 */
MANAGER_DATE, /* 主管审批时间 */
LASTMODIFY, /* 最后修改时间 */
LASTTIME, /* 最后修改时间（可能为时间戳） */
BAK_LCHAR07, /* 商品扩展属性长字符7 */
GSP_NO, /* GSP证书 */
BAK_DATE10, /* 日期型备用字段10 */
GSP_END_DATE, /* GSP证书有效期至 */
APPLY_USERID, /* 申请人 */
BUYER_NAME, /* 业务审批人 */
GSP_NAME, /* 质检审核人姓名 */
MANAGER_NAME, /* 领导姓名 */
BAK_LCHAR06, /* 商品扩展属性长字符6 */
ASSI_IDCARD, /* 受托人身份证号码 */
GSP_CHK, /* 质管部意见 */
BAK_LCHAR05, /* 商品扩展属性长字符5 */
BAK_DATE9, /* 日期型备用字段9 */
COMPID, /* 企业编号 */
INVOICETYPE, /* 发票类型 */
SALEZONE, /* 销售区域 */
IFCREDIT, /* 是否有信用记录 */
OWNERID, /* 货主编号 */
DEPARTMENT, /* 部门 */
PRICE_GROUP, /* 价格组 */
BUS_TYPE3, /* 付款方式 */
PAYTYPE, /* 付款账期 */
CORP_TYPE1, /* 营业执照企业类型 */
TAXNO, /* 税号 */
BAK_LCHAR04, /* 商品扩展属性长字符4 */
BAK_LCHAR03, /* 商品扩展属性长字符3 */
NEW_FLAG, /* 是否首营新增 */
PROVINCE, /* 省份 */
CITY, /* 城市 */
AREA, /* 区域 */
IFHAVE1, /* 是否有营业执照 */
IFHAVE2, /* 是否有许可证 */
IFHAVE3, /* 是否有税务登记证 */
IFDEPUTY, /* 是否有业务代表 */
IFGOODSCHK, /* 商品是否首营审批 */
BAK_LCHAR02, /* 商品扩展属性长字符2 */
BUS_GOODS_NAME, /* 经营范围名称 */
YSBNO, /* 医疗器械生产（经营）许可证编号 */
YYCNO, /* 毒性药品生产（经营）许可证编号 */
XYYNO, /* 麻醉药品生产（经营）许可证编号 */
BAK_LCHAR01, /* 商品扩展属性长字符1 */
BAK_CHAR10, /* 字符型备用字段10 */
BAK_CHAR9, /* 字符型备用字段9 */
BANK, /* 开户银行 */
BANKNO, /* 银行账号 */
BAK_CHAR6, /* 字符型备用字段6 */
BAK_CHAR7, /* 字符型备用字段7 */
taxdate, /* 税务登记证有效期 */
saler, /* 业务员备案情况 */
assi_name, /* 受托人姓名 */
header2,
create_depid,
warehouseaddress )
SELECT 2, --- 往来单位类型，固定值 2
1, --- 供应商类型，药品类型，固定值 1
f_st2zjm(rec.DRUGSTORENAME), --- 助记码，通过 `f_st2zjm` 函数生成，传入药店名称 `DRUGSTORENAME`
V_CHECKNO, --- 首营供应商编号，字段名 `V_CHECKNO`
V_VENCUSNO, --- 供应商编号，字段名 `V_VENCUSNO`
V_VENCUSNO, --- 供应商编号，重复字段名 `V_VENCUSNO`
REC.DRUGSTORENAME, --- 供应商名称，取自 `DRUGSTORENAME` 字段
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR --- 如果药店名称包含‘诊所’
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR --- 或包含‘卫生’
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR --- 或包含‘医院’
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN --- 或包含‘门诊’
'4' --- 如果匹配到，返回 '32'，可能是某种类型标识
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN --- 如果药店名称包含‘连锁’
'5' --- 返回 '14'，可能是连锁药店类型标识
ELSE
'1' --- 默认返回 '1'，表示其他类型
END,
SUBSTR(REC.DRUGSTORENAME, 1, 15), --- 商品扩展属性，取药店名称的前15个字符
REC.REGADRESS, --- 地址，取自 `REGADRESS` 字段
REC.PROXYOPERNAME, --- 法人代表，取自 `PROXYOPERNAME` 字段
NULL, --- 邮编，固定值为 NULL
NULL, --- 传真，固定值为 NULL
REC.SALESMANNAME, --- 联系人，取自 `SALESMANNAME` 字段
CASE
WHEN REC.SALESMANPHONE IS NOT NULL THEN --- 如果销售员电话不为空
REC.SALESMANPHONE --- 返回销售员电话
ELSE
REC.SELLERPHONE --- 否则返回卖家电话
END,
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 THEN --- 如果药店名称包含‘诊所’或‘卫生’
REC.HISORGCARDNO --- 返回历史组织卡号
ELSE
REC.DRUGBUSITCARDNO --- 否则返回药品经营许可证编号
END,
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 THEN
CASE
WHEN REC.HISORGCARDVALID = '长期' THEN --- 如果有效期是长期
TO_DATE('2099-12-31', 'YYYY-MM-DD') --- 设为长期有效的日期
ELSE
TO_DATE(SUBSTR(REC.HISORGCARDVALID, 1, 10),
'YYYY/MM/DD') --- 否则从字段中提取有效期日期
END
ELSE
CASE
WHEN REC.DRUGBUSIVALIDITY = '长期' THEN --- 如果药品经营许可证有效期是长期
TO_DATE('2099-12-31', 'YYYY-MM-DD') --- 设为长期有效的日期
ELSE
TO_DATE(SUBSTR(REC.DRUGBUSIVALIDITY, 1, 10),
'YYYY/MM/DD') --- 否则从字段中提取有效期日期
END
END,
null,-- REC.DRUGSTORENAME, --- 档案编号，取自药店名称
NULL, --- 发证机关，固定值为 NULL
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN
'4' --- 返回 '32'，表示某种类型
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN
'1' --- 返回 '14'，表示连锁药店类型
ELSE
'1' --- 默认返回 '1'，表示其他类型
END,
REC.REGADRESS, --- 地址，取自 `REGADRESS` 字段
'', --- 许可范围，固定为空字符串
REC.BUSICARDNO, --- 营业执照编号，取自 `BUSICARDNO` 字段
TO_DATE(DECODE(REC.BUSICARDVALID, --- 将营业执照有效期字段转换为日期格式
'长期',
'2099-12-31', --- 如果是长期，设为最大有效日期
SUBSTR(REC.BUSICARDVALID, 1, 10)), --- 否则，提取有效期并转换为日期
'YYYY-MM-DD'),
NVL(REC.PROXYOPERNAME,REC.hisorgcardproxyopername ), --- 法人代表，取自 `PROXYOPERNAME` 字段
NULL, --- 质管中心意见，固定为 NULL
NULL, --- 注册资金，固定为 NULL
REC.DRUGSTORENAME, --- 经营性质，取药店名称
NULL, --- 物流中心意见，固定为 NULL
CASE
WHEN F_GET_AREACODE(REC.PROVINCENAME) IS NOT NULL THEN
F_GET_AREACODE(REC.PROVINCENAME) --- 通过 `F_GET_AREACODE` 获取省份的区域编码
ELSE
'00' --- 如果没有获取到，设为 '00'
END,
REC.BUSISCOPE, --- 经营范围，取自 `BUSISCOPE` 字段
SYSDATE, --- 创建时间，系统当前日期
10004, --- 创建人，固定值
0, --- 状态，固定值 0
SUBSTR(REC.REGADRESS, 1, 40), --- 地址扩展属性，取地址的前40个字符
REC.DRUGSTORETYPE, --- 经营性质，取自 `DRUGSTORETYPE` 字段
NULL, --- 销售员意见，固定为 NULL
NULL, --- 发证机关，固定为 NULL
'', --- 生效时间，固定为空字符串
0, --- 审核标志1，固定值 0
0, --- 审核标志2，固定值 0
0, --- 审核标志3，固定值 0
0, --- 审核标志4，固定值 0
0, --- 审核标志5，固定值 0
REC.SALESMANNAME, --- 销售员姓名，取自 `SALESMANNAME` 字段
TO_DATE(REC.PURCHASEPAPERVALID, 'YYYY/MM/DD'), --- 采购文件有效期，格式化为日期
NULL, --- 主管审批时间，固定为 NULL
NULL, --- 质管员审批时间，固定为 NULL
NULL, --- 经营部门审批时间，固定为 NULL
NULL, --- 最后修改时间，固定为 NULL
10004, --- 申请人，固定为
SYSDATE, --- 最后修改时间，系统当前日期
NULL, --- GSP发证机关，固定为 NULL
REC.GSPCARDNO, --- GSP证书编号，取自 `GSPCARDNO` 字段
TO_DATE(SUBSTR(REC.GSPISSUE, 1, 10), 'YYYY/MM/DD'), --- GSP证书发证日期，转换为日期格式
TO_DATE(SUBSTR(REC.GSPVALIDITY, 1, 10), 'YYYY/MM/DD'), --- GSP证书有效期，转换为日期格式
'10017', --- 销售员标识，固定值 '10017'
NULL, --- 银行信息，固定为 NULL
NULL, --- 银行账户，固定为 NULL
NULL, --- 银行信息，固定为 NULL
V_VENCUSNO, --- 供应商编号，取自 `V_VENCUSNO` 字段
REC.SELIDENTITYNO, --- 受托人身份证号，取自 `SELIDENTITYNO` 字段
NULL, --- 受托人姓名，固定为 NULL
NULL, --- 银行账户地址，固定为 NULL
NULL, --- 经营范围名称，固定为 NULL
V_COMPID, --- 企业编号，取自 `V_COMPID` 字段
CASE
WHEN REC.INVOICETYPE = '2' THEN
REC.INVOICETYPE --- 如果发票类型为 '2'，则返回发票类型
ELSE
'51' --- 否则返回 '51'
END,
NULL, --- 字符型备用字段，固定为 NULL
'1', --- 是否首营新增，固定值 '1'
'01', --- 省份编码，固定值 '01'
'01', --- 市区编码，固定值 '01'
CASE
WHEN REC.BILLSOURCE IN (1, 2, 3) THEN
12 --- 根据来源判断，返回固定值 '1001'
END,
'01', --- 备用字段，固定值 '01'
'01', --- 备用字段，固定值 '01'
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN
'32' --- 如果包含诊所、卫生等，则返回 '32'
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN
'14' --- 否则，如果包含连锁，返回 '14'
ELSE
'1' --- 默认返回 '1'
END,
rec.taxno, --- 统一社会信用代码，取自 `TAXNO`
REC.DRUGSTORENAME, --- 药店名称，取自 `DRUGSTORENAME`
NULL, --- 银行账户地址，固定为空
'1', --- 是否新增首营，固定值 '1'
CASE
WHEN F_GET_AREACODE(REC.PROVINCENAME) IS NOT NULL THEN
F_GET_AREACODE(REC.PROVINCENAME) --- 省份的区域编码
ELSE
'00' --- 没有匹配到则返回 '00'
END,
CASE
WHEN F_GET_AREACODE(REC.CITYNAME) IS NOT NULL THEN
F_GET_AREACODE(REC.CITYNAME) --- 城市的区域编码
ELSE
NULL --- 如果没有匹配到，返回 NULL
END,
CASE
WHEN F_GET_AREACODE(REC.DISTRICTNAME) IS NOT NULL THEN
F_GET_AREANAME(REC.DISTRICTNAME) --- 区域名称
ELSE
NULL --- 没有匹配返回 NULL
END, --- 地区编码相关字段
'1', --- 固定值 '1'
'1', --- 固定值 '1'
'1', --- 固定值 '1'
'1', --- 固定值 '1'
'1', --- 固定值 '1'
NULL, --- 银行地址，固定为 NULL
'', --- 经营范围名称，固定为空字符串
CASE
WHEN REC.BILLSOURCE = 1 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.BILLSOURCE = 2 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.BILLSOURCE = 3 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.INVOICETYPE = '2' THEN
REC.INVOICEINFONAME
ELSE
''
END, -- 发票信息名称：根据REC.INVOICETYPE决定
REC.TAXNO, -- 税号
REC.INVOICEINFOPHONE, -- 发票信息电话
REC.BANKNAME, -- 开户银行
REC.BANKCARDNO, -- 银行账号
REC.BUSICARDADDRESS, -- 营业执照地址
REC.HISORGCARDPRINCIPAL, -- 医疗机构负责人
TO_DATE(DECODE(REC.BUSICARDVALID,
'长期',
'2099-12-31',
SUBSTR(REC.BUSICARDVALID, 1, 10)),
'YYYY-MM-DD'), -- 营业执照有效期（备用）
'10017', -- 业务员ID
REC.salesmanname ,-- 销售员姓名
NVL(REC.PROXYOPERNAME,REC.hisorgcardproxyopername ),
5,
REC.REGADRESS
from DUAL;
/*SELECT 2, ---往来单位类型
1, --药品
f_st2zjm(rec.DRUGSTORENAME), ---助记码
V_CHECKNO,
V_VENCUSNO,
V_VENCUSNO,
REC.DRUGSTORENAME,
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN
'32'
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN
'14'
ELSE
'1'
END,
SUBSTR(REC.DRUGSTORENAME, 1, 15),
REC.REGADRESS,
REC.PROXYOPERNAME,
NULL, -- 邮编
NULL, -- 联系人传真
REC.SALESMANNAME,
CASE
WHEN REC.SALESMANPHONE IS NOT NULL THEN
REC.SALESMANPHONE
ELSE
REC.SELLERPHONE
END,
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 THEN
REC.HISORGCARDNO
ELSE
REC.DRUGBUSITCARDNO
END,
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 THEN
CASE
WHEN REC.HISORGCARDVALID = '长期' THEN
TO_DATE('2099-12-31', 'YYYY-MM-DD')
ELSE
TO_DATE(SUBSTR(REC.HISORGCARDVALID, 1, 10),
'YYYY/MM/DD')
END
ELSE
CASE
WHEN REC.DRUGBUSIVALIDITY = '长期' THEN
TO_DATE('2099-12-31', 'YYYY-MM-DD')
ELSE
TO_DATE(SUBSTR(REC.DRUGBUSIVALIDITY, 1, 10),
'YYYY/MM/DD')
END
END,
REC.DRUGSTORENAME, -- 档案编号
NULL, -- 发证机关
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN
'32'
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN
'14'
ELSE
'1'
END,
REC.REGADRESS,
'', -- 许可范围
REC.BUSICARDNO,
TO_DATE(DECODE(REC.BUSICARDVALID,
'长期',
'2099-12-31',
SUBSTR(REC.BUSICARDVALID, 1, 10)),
'YYYY-MM-DD'),
REC.PROXYOPERNAME,
NULL, -- 质管中心意见
NULL, -- 注册资金
REC.DRUGSTORENAME,
NULL, -- 物流中心意见
CASE
WHEN F_GET_AREACODE(REC.PROVINCENAME) IS NOT NULL THEN
F_GET_AREACODE(REC.PROVINCENAME)
ELSE
'00'
END,
REC.BUSISCOPE,
SYSDATE,
1006,
0,
SUBSTR(REC.REGADRESS, 1, 40),
REC.DRUGSTORETYPE, -- 经营性质
NULL, -- 销售员意见
NULL, -- 发证机关
'', -- 生效时间
0,
0,
0,
0,
0,
REC.SALESMANNAME,
TO_DATE(REC.PURCHASEPAPERVALID, 'YYYY/MM/DD'),
NULL,
NULL,
NULL,
NULL,
1006,
SYSDATE,
NULL, -- GSP发证机关
REC.GSPCARDNO,
TO_DATE(SUBSTR(REC.GSPISSUE, 1, 10), 'YYYY/MM/DD'),
TO_DATE(SUBSTR(REC.GSPVALIDITY, 1, 10), 'YYYY/MM/DD'),
'1017', -- 销售员标识
NULL,
NULL,
NULL,
V_VENCUSNO,
REC.SELIDENTITYNO,
NULL,
NULL,
NULL,
V_COMPID,
CASE
WHEN REC.INVOICETYPE = '2' THEN
REC.INVOICETYPE
ELSE
'51'
END,
NULL,
'1',
'01',
'01',
CASE
WHEN REC.BILLSOURCE IN (1, 2, 3) THEN
1001
END,
'01',
'01',
CASE
WHEN INSTRB(REC.DRUGSTORENAME, '诊所', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '卫生', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '医院', 1, 1) > 1 OR
INSTRB(REC.DRUGSTORENAME, '门诊', 1, 1) > 1 THEN
'32'
WHEN INSTRB(REC.DRUGSTORENAME, '连锁', 1, 1) > 1 THEN
'14'
ELSE
'1'
END,
rec.taxno, --统一社会信用代码
REC.DRUGSTORENAME,
NULL, -- 银行账户地址
'1', -- 是否新增首营
CASE
WHEN F_GET_AREACODE(REC.PROVINCENAME) IS NOT NULL THEN
F_GET_AREACODE(REC.PROVINCENAME)
ELSE
'00'
END,
CASE
WHEN F_GET_AREACODE(REC.CITYNAME) IS NOT NULL THEN
F_GET_AREACODE(REC.CITYNAME)
ELSE
NULL
END,
CASE
WHEN F_GET_AREACODE(REC.DISTRICTNAME) IS NOT NULL THEN
F_GET_AREANAME(REC.DISTRICTNAME)
ELSE
NULL
END, ----- 地区
'1',
'1',
'1',
'1',
'1',
NULL, -- 银行地址
'', -- 经营范围名称
CASE
WHEN REC.BILLSOURCE = 1 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.BILLSOURCE = 2 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.BILLSOURCE = 3 THEN
NVL(REC.DRUGSTOREID, REC.CUSTOMERID)
END,
CASE
WHEN REC.INVOICETYPE = '2' THEN
REC.INVOICEINFONAME
ELSE
''
END,
REC.TAXNO,
REC.INVOICEINFOPHONE,
REC.BANKNAME,
REC.BANKCARDNO,
REC.BUSICARDADDRESS,
REC.HISORGCARDPRINCIPAL,
TO_DATE(DECODE(REC.BUSICARDVALID,
'长期',
'2099-12-31',
SUBSTR(REC.BUSICARDVALID, 1, 10)),
'YYYY-MM-DD'),
'168', ---业务员
REC.salesmanname
FROM DUAL;
*/
--循环插入经营范围
BEGIN
FOR REC_JY IN (SELECT A.DRUGSTOREID,
A.fullbusinessscope,
REGEXP_SUBSTR(A.fullbusinessscope,
'[^,]+',
1,
L) AS CLASSCODE18
FROM H2_VENCUS A,
(SELECT LEVEL L
FROM DUAL
CONNECT BY LEVEL <= 100)
WHERE L(+) <=
LENGTH(A.fullbusinessscope) -
LENGTH(REPLACE(A.fullbusinessscope, ',')) + 1
AND A.DRUGSTOREID = REC.DRUGSTOREID) LOOP
INSERT INTO T_CHK_VENCUS_RANGE
(VENDORCHKNO, --首营单号
COMPID, --企业编码
VENCUSNO, --往来单位编码
CLASSCODE, --经营范围分类编码
STATUS, --状态
STAMP, --时间戳
LASTMODIFY, --最终修改人
LASTTIME, --最终修改时间
NOTES) --备注
SELECT DISTINCT V_CHECKNO,
V_COMPID,
V_VENCUSNO,
ysb.classcode_hd,
1,
'',
'10004',
SYSDATE,
NULL
FROM D_RANG_YSB YSB
WHERE YSB.CLASSCODE = REC_JY.CLASSCODE18
AND YSB.CLASSCODE_HD IS NOT NULL
AND NOT EXISTS
(SELECT 1
FROM T_CHK_VENCUS_RANGE tc
WHERE tc.vendorchkno = V_CHECKNO
AND tc.compid = V_COMPID
AND tc.vencusno = V_VENCUSNO
AND ysb.classcode_hd = tc.CLASSCODE);
COMMIT;
END LOOP REC_JY;
END;
begin
--001 药品经营许可证
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag,
GRANT_DATE)
select 1,
v_vencusno,
3 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
to_date(decode(drugbusivalidity,
'长期',
'2099-12-31',
substr(drugbusivalidity, 1, 10)),
'YYYY-MM-DD') as invalidate,
ysb_kh.drugbusitcardno as photo_no,
10004,
sysdate,
0,
0,
0,
drugbusiurl, --图片地址存备注
null,
null,
null,
null,
null,
to_date(DRUGBUSIISSUE, 'yyyy/mm/dd')
from h2.h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
--003 医疗器械经营许可证
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag)
select 1,
v_vencusno,
5 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
to_date(decode(hiseqbusitcardvalid,
'长期',
'2099-12-31',
substr(hiseqbusitcardvalid, 1, 10)),
'YYYY-MM-DD') as invalidate,
hiseqbusitcardno as photo_no,
10004,
sysdate,
0,
0,
0,
hiseqbusitcardurl, --图片地址存备注
null,
null,
null,
null,
null
from h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
--004 医疗器械备案凭证
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
--invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag)
select 1,
v_vencusno,
7 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
--to_date(hiseqbusitcardvalid, 'YYYYMMDD') as invalidate,
HISEQRECORD as photo_no,
10004,
sysdate,
0,
0,
0,
hiseqRecordUrl, --图片地址存备注
null,
null,
null,
null,
null
from h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
--005 营业执照
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag,
GRANT_DATE)
select 1,
v_vencusno,
1 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
to_date(decode(busicardvalid,
'长期',
'2099-12-31',
substr(busicardvalid, 1, 10)),
'YYYY-MM-DD') as invalidate,
ysb_kh.busicardno as photo_no,
10004,
sysdate,
0,
0,
0,
busicardUrl, --图片地址存备注
null,
null,
null,
null,
null,
to_date(BUSICARDISSUE, 'yyyy/mm/dd')
from h2.h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
--007 医疗机构执业许可证
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag,
GRANT_DATE)
select 1,
v_vencusno,
8 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
to_date(decode(HISORGCARDVALID,
'长期',
'2099-12-31',
substr(HISORGCARDVALID, 1, 10)),
'YYYY-MM-DD') as invalidate,
ysb_kh.HISORGCARDNO as photo_no,
10004,
sysdate,
0,
0,
0,
hisOrgcardUrl, --图片地址存备注
null,
null,
null,
null,
null,
to_date(HISORGCARDISSUE, 'yyyy/mm/dd')
from h2.h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
--008 食品经营许可证
insert into t_chk_vencus_certificate_qutry
(compid,
vencusno,
certificateid,
invalidate,
photo_no,
lastmodify,
lasttime,
invalidate_status,
photono_status,
status,
notes,
idcard,
classgroupno,
classcodelist,
classnamelist,
operate_flag)
select 1,
v_vencusno,
10 as certificateid, --要根据业务系统t_certificate_set的类型 相应改变
to_date(decode(foodBusiValid,
'长期',
'2099-12-31',
substr(foodBusiValid, 1, 10)),
'YYYY-MM-DD') as invalidate,
ysb_kh.foodBusiNo as photo_no,
10004,
sysdate,
0,
0,
0,
foodBusiUrl, --图片地址存备注
null,
null,
null,
null,
null
from h2_vencus ysb_kh
where ysb_kh.drugstoreid = rec.drugstoreid;
end;
INSERT INTO t_chk_vencus_saler
(compid,
vencusno,
ownerid,
reckonerid,
saler,
paytype,
status,
whlgroupid,
whlsign,
cashtype,
isdefault,
vendorchkno)
VALUES
(1,
V_VENCUSNO,
'01',
'01',
10017,
'01',
1,
12,
0,
'01',
0,
V_CHECKNO);
-- 子结算户和部门业务员
BEGIN
INSERT INTO T_CHK_VENCUS_SUBITEM
(VENDORCHKNO,
VENCUSNO,
SUBITEMID,
SUBITEMNAME,
NOTES,
STAMP,
ADDRESS,
POSTCODE,
CONTACT,
PHONE,
FAX,
MOBILE,
EMAIL,
COMPID,
ISDEFAULT,
C_SUBITEMID,
C_VENCUSNO,
CASHTYPE,
PAYTYPE,
SUBITEM_STATUS,
INVALIDATE,
STATUS)
SELECT V_CHECKNO,
V_VENCUSNO,
'0',
'默认子结算户',
'',
'',
REC.REGADRESS,
'',
REC.SALESMANNAME,
CASE
WHEN REC.SALESMANPHONE IS NOT NULL THEN
REC.SALESMANPHONE
ELSE
REC.SELLERPHONE
END,
'',
CASE
WHEN REC.SALESMANPHONE IS NOT NULL THEN
REC.SALESMANPHONE
ELSE
REC.SELLERPHONE
END,
'',
1,
1,
'',
'',
'01',
'01',
'1',
'',
1
FROM DUAL;
END;
-- 更新处理标志
UPDATE H2.H2_VENCUS
SET FLAG = 1
WHERE DRUGSTOREID = REC.DRUGSTOREID
AND BILLSOURCE = REC.BILLSOURCE;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERRM := SUBSTR(SQLERRM, 1, 1900);
UPDATE H2.H2_VENCUS
SET FLAG = 2, ERR = SYSDATE || '---' || V_ERRM
WHERE DRUGSTOREID = REC.DRUGSTOREID
AND BILLSOURCE = REC.BILLSOURCE;
COMMIT;
CONTINUE;
END;
ELSE
-- 更新外部系统编号
IF REC.BILLSOURCE = 1 THEN
UPDATE T_CHK_CUSTOMER
SET YSBNO = NVL(REC.CUSTOMERID, REC.DRUGSTOREID)
WHERE CHECKNO = V_CHECKNO;
ELSIF REC.BILLSOURCE = 2 THEN
UPDATE T_CHK_CUSTOMER
SET YYCNO = NVL(REC.CUSTOMERID, REC.DRUGSTOREID)
WHERE CHECKNO = V_CHECKNO;
ELSE
UPDATE T_CHK_CUSTOMER
SET XYYNO = NVL(REC.CUSTOMERID, REC.DRUGSTOREID)
WHERE CHECKNO = V_CHECKNO;
END IF;
UPDATE H2.H2_VENCUS H
SET FLAG = 1, ERR = '客户已生成首营企业,单号为' || V_CHECKNO
WHERE DRUGSTOREID = NVL(REC.CUSTOMERID, REC.DRUGSTOREID);
END IF;
ELSE
UPDATE H2.H2_VENCUS H
SET FLAG = 1, ERR = '客户已存在'
WHERE DRUGSTOREID = NVL(REC.CUSTOMERID, REC.DRUGSTOREID);
END IF;
COMMIT;
END LOOP;
END;
B2B中间表转到批发申请单(客户这边是转到批发申请单的模式,转批发销售单的往下面看)
CREATE OR REPLACE PROCEDURE CPROC_B2B中间表转批发申请(an_billsource IN VARCHAR2) AS
v_procname t_proc_rep.procrepname%TYPE;
P_APPLYNO t_batsale_h.batsaleno%TYPE;
v_busno t_batsale_h.busno%TYPE;
v_compid t_batsale_h.compid%TYPE;
v_vencusno t_batsale_h.vencusno%TYPE;
v_spsl NUMBER(10);
v_vencusname t_vencus.vencusname%TYPE;
v_saler t_vencus_saler.saler%TYPE;
v_paytype t_vencus_saler.paytype%TYPE;
v_reckonerid t_vencus_saler.reckonerid%TYPE;
v_whlgroupid t_vencus_saler.whlgroupid%TYPE;
v_cashtype t_vencus_saler.cashtype%TYPE;
v_subitemid t_vencus_saler.subitemid%TYPE;
v_errm VARCHAR2(2000);
v_addrid t_road_addr.addrid%TYPE;
v_drugcode VARCHAR2(1000);
v_pshs NUMBER(10);
v_ownerid t_vencus_saler.ownerid%TYPE;
v_invoicetype t_vencus.invoicetype%TYPE;
BEGIN
-- 检查是否有替换存储过程
BEGIN
SELECT procrepname INTO v_procname
FROM t_proc_rep
WHERE UPPER(procname) = UPPER('CPROC_B2B中间表转批发申请') AND status = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_procname := NULL;
END;
IF v_procname IS NOT NULL THEN
EXECUTE IMMEDIATE 'BEGIN ' || v_procname || '; 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 NOT EXISTS (
SELECT 1 FROM t_batsale_h th
WHERE th.sale_idbs =H.billsource
AND th.djbh = h.djbh
AND th.status <> '2'
)
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
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
WHERE compid = v_compid
AND vencuscode = rec.customerid
AND status = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE h2.h2_batsale_h
SET flag = 2, err = err || '客户编码' || rec.customerid || '在ERP中找不到'
WHERE djbh = rec.djbh AND billsource = rec.billsource;
COMMIT;
CONTINUE;
END;
-- 检查商品有效性
BEGIN
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 WHERE compid = v_compid AND warecode = d.drugcode);
EXCEPTION
WHEN OTHERS THEN
v_spsl := 0;
v_drugcode := NULL;
END;
IF v_spsl > 0 THEN
UPDATE h2.h2_batsale_h
SET flag = 2, err = err || '无效商品编码: ' || v_drugcode
WHERE djbh = rec.djbh AND billsource = rec.billsource;
COMMIT;
CONTINUE;
END IF;
-- 获取子账户信息
BEGIN
SELECT '01', reckonerid, saler, paytype, whlgroupid, cashtype, subitemid
INTO v_ownerid, v_reckonerid, v_saler, v_paytype, v_whlgroupid, v_cashtype, v_subitemid
FROM t_vencus_saler
WHERE vencusno = v_vencusno
AND compid = v_compid
AND status = 1
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
UPDATE h2.h2_batsale_h
SET flag = 2, err = err || '客户未配置业务员信息'
WHERE djbh = rec.djbh AND billsource = rec.billsource;
COMMIT;
CONTINUE;
END;
-- 生成批发申请单
BEGIN
P_APPLYNO := f_get_serial('ACB', v_busno);
INSERT INTO t_batsaleapply_h (
COMPID, BILLCODE, APPLYNO, VENCUSNO, BUSNO, WHLGROUPID, PAYTYPE, SALER,
CHECKBIT1, CHECKBIT2, CHECKBIT3, CHECKBIT4, CHECKBIT5, STATUS, CREATEUSER,
CREATETIME, NOTES, PICKUPTYPE, ACCCHKED, INVOICETYPE, SUBITEMID, LASTMODIFY,
LASTTIME, RECKONERID, OWNERID, ADDRID, VENCUSNAME, INDENTFLAG, CASHTYPE, ACCOUNT_DATE,DJBH,SALE_IDBS,BILLSOURCE )
VALUES (
v_compid, 'ACB', P_APPLYNO, v_vencusno, v_busno, 12/* v_whlgroupid*/, v_paytype,
v_saler, '0','0','0','0','0','0',168,sysdate , 'B2C平台[' || rec.djbh || ']',null,'0',rec.invoicetype, v_subitemid, 168,
sysdate,v_reckonerid,v_ownerid, v_addrid,rec.drugstorename,'0',v_cashtype, SYSDATE,rec.djbh,rec.xsbs,rec.billsource
);
-- 插入明细
INSERT INTO t_batsaleapply_d(
APPLYNO, WAREID, PURPRICE, WAREQTY, CHECKQTY, WHLPRICE, BACKQTY, SALEPRICE,
STDPRICE, COMPURPRICE, PURTAX, SALERPRICE, LEASTPRICE, MAXWHLPRICE, AVGPURPRICE,
ROWNO, REDEEMSUM, EXTHOLDER, OLDWHLPRICE, FLAG, MAXQTY, MIDQTY, DEPOTQTY, BATSALEQTY,DJ_SN
)
SELECT P_APPLYNO, w.wareid, w.lastpurprice, d.shl, d.shl, d.dj,null,w.lastsaleprice,
w.lastsaleprice,null,w.purtax,w.lastsaleprice,'0','0','0', d.dj_sn,
'0','0','0','0',w.maxqty,w.midqty,'0','0',d.dj_sn
FROM h2.h2_batsale_d d
JOIN t_ware w ON d.drugcode = w.warecode AND w.compid = v_compid
WHERE d.djbh = rec.djbh AND d.billsource = rec.billsource;
-- 更新订单状态
UPDATE h2.h2_batsale_h SET flag = 1
WHERE djbh = rec.djbh AND billsource = rec.billsource;
COMMIT;
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;
END;
END LOOP;
END;
B2B平台订单转批发销售单 (带GSP校验)
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;
使用平台订单转申请单需要替换申请转销售单的存储过程
CREATE OR REPLACE PROCEDURE cproc_batsaleapply2batsale(p_applyno IN t_batsaleapply_h.applyno%TYPE,
p_lastmodify IN t_batsaleapply_h.lastmodify%TYPE,
out_batsaleno OUT VARCHAR2) IS
v_length INT;
v_para2975 s_sys_ini.inipara%TYPE; --批发销售限制货位
v_para2976 s_sys_ini.inipara%TYPE; --批发销售限制,批发申请转批发需要过滤的商品类别
v_para2977 s_sys_ini.inipara%TYPE; --控制批发申请转批发，多少天以内的批次不允许出库。
v_para2790 s_sys_ini.inipara%TYPE; --配送申请转配送的历史补货数量是否累加或者或者不累加直接取最后一次补货数量（包含批发申请转批发）
v_batsaleno t_batsale_h.batsaleno%TYPE;
v_old_batsaleno t_batsale_h.batsaleno%TYPE;
v_warecode t_ware.warecode%TYPE;
v_busno t_batsale_h.busno%TYPE; --业务机构
v_compid t_batsale_h.compid%TYPE; --企业
v_vencusno t_batsale_h.vencusno%TYPE; --往来单位
v_whlgroupid t_batsale_h.whlgroupid%TYPE; --批发价格组
v_client t_batsale_h.client%TYPE; --委托人
v_vencusno_batsale t_batsale_h.vencusno%TYPE;
v_compid_batsale t_batsale_h.compid%TYPE;
v_wareqty NUMBER;
v_notes VARCHAR2(4000);
v_notes1 VARCHAR2(4000);
v_mesgxx VARCHAR2(4000); --限销提示
v_count PLS_INTEGER;
v_pare2976note VARCHAR2(4000); --2976参数限制
v_warecodes VARCHAR2(4000);
v_msg VARCHAR2(4000);
v_awaitqty_owner t_store_d.awaitqty%TYPE;
v_awaitqty_makeno t_store_d.awaitqty%TYPE;
v_awaitqty t_store_d.awaitqty%TYPE;
v_saler t_batsale_h.saler%TYPE;
v_supplyno VARCHAR2(4000);
v_ownerid t_batsale_h.ownerid%TYPE;
v_vencuscode t_vencus.vencuscode%TYPE;
v_wareid_list number_array;
v_procname t_proc_rep.procrepname%TYPE;
--v_para1307 s_sys_ini.inipara%TYPE;
v_parawms0001 s_sys_ini.inipara%TYPE;
v_wms_ownerid t_owner.wms_ownerid%TYPE;
v_applyqty tmp_batsale_act_alloc.applyqty%TYPE;
v_warename t_ware_base.warename%TYPE;
v_actualqty t_batsale_d.wareqty%TYPE;
v_actualqty1 t_batsale_d.wareqty%TYPE;
v_amt t_batsale_d.whlprice%TYPE;
v_isxx PLS_INTEGER;
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_subitemid t_batsale_h.subitemid%TYPE;
v_count1 PLS_INTEGER;
v_whlprice t_batsale_d.whlprice%TYPE;
v_js PLS_INTEGER;
v_mestz VARCHAR2(4000);
v_wms001 t_wms_store_set.defpara%TYPE;
v_makeno_check t_vencus.makeno_check%TYPE;
v_check_batch_flag t_vencus.check_batch_flag%TYPE;
v_makejs PLS_INTEGER;
v_djbh t_batsale_h.djbh%TYPE;
BEGIN
--是否有替换储存过程
BEGIN
SELECT procrepname
INTO v_procname
FROM t_proc_rep
WHERE upper(procname) = upper('cproc_batsaleapply2batsale')
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 || '(:1, :2, :3); end;'
USING p_applyno, p_lastmodify, OUT out_batsaleno;
END;
RETURN;
END IF;
BEGIN
SELECT b.batsaleno
INTO v_old_batsaleno
FROM t_batsaleapply_h b
WHERE b.applyno = p_applyno
AND b.batsaleno IS NOT NULL;
raise_application_error(-20001,
'您选择的批发申请单已转为批发销售单' || v_old_batsaleno ||
'，请不要重复操作！',
TRUE);
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
--raise_application_error(-20001,'abcdefghijk',TRUE);
SELECT busno, compid, vencusno, whlgroupid, ownerid, subitemid, client ,djbh
INTO v_busno,
v_compid,
v_vencusno,
v_whlgroupid,
v_ownerid,
v_subitemid,
v_client
,v_djbh
FROM t_batsaleapply_h
WHERE applyno = p_applyno;
BEGIN
SELECT warecode, warename
INTO v_warecode, v_warename
FROM t_batsaleapply_d a, t_ware b
WHERE a.wareid = b.wareid
AND b.compid = v_compid
AND a.applyno = p_applyno
AND (b.saletax IS NULL OR b.saletax < 0)
AND rownum = 1;
raise_application_error(-20001,
'您选择的批发申请单的商品' || v_warecode || v_warename ||
'没有录入销项税率！',
TRUE);
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
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_wms_ownerid := f_get_wmsownerid(p_compid => v_compid,
p_ownerid => v_ownerid);
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);
BEGIN
SELECT defpara
INTO v_wms001
FROM t_wms_store_set
WHERE compid = v_compid
AND inicode = 'WMS001';
EXCEPTION
WHEN no_data_found THEN
v_wms001 := '0';
END;
DELETE FROM tmp_batsale_act_alloc;
INSERT INTO tmp_batsale_act_alloc
(compid, busno, wareid, whlprice, applyqty, actualqty, maxqty, ownerid)
SELECT h.compid,
h.busno,
bd.wareid,
0,
bd.checkqty,
0,
decode(nvl(c.maxqty, 0), 0, 1, c.maxqty),
h.ownerid
FROM t_batsaleapply_d bd, t_batsaleapply_h h, t_ware c
WHERE h.applyno = p_applyno
AND h.applyno = bd.applyno
AND h.compid = c.compid
AND bd.wareid = c.wareid
AND (v_para2976 IS NULL OR
(v_para2976 IS NOT NULL AND NOT EXISTS
(SELECT 1
FROM t_ware_class_base wb
WHERE wb.wareid = bd.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 = h.compid
AND twcb.wareid = wb.wareid
AND twcb.classgroupno = wb.classgroupno) THEN
h.compid
ELSE
0
END))));
IF SQL%ROWCOUNT = 0 THEN
UPDATE t_batsaleapply_h
SET indentflag = 1,
notes = notes || '，被参数2976限制此类别不允许转单'
WHERE applyno = p_applyno;
out_batsaleno := '2976限制';
RETURN;
END IF;
SELECT COUNT(1),
listagg(c.warecode, ',') within GROUP(ORDER BY bd.applyno) warecodes
INTO v_count, v_warecodes
FROM t_batsaleapply_d bd, t_batsaleapply_h h, t_ware c
WHERE h.applyno = p_applyno
AND h.applyno = bd.applyno
AND h.compid = c.compid
AND bd.wareid = c.wareid
AND v_para2976 IS NOT NULL
AND EXISTS
(SELECT 1
FROM t_ware_class_base wb
WHERE wb.wareid = bd.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 = h.compid
AND twcb.wareid = wb.wareid
AND twcb.classgroupno = wb.classgroupno) THEN
h.compid
ELSE
0
END));
IF v_count > 0 THEN
v_pare2976note := '商品(' || v_warecodes || '),被参数2976限制此类别，无法转单!';
END IF;
--/*+ index(sd idx_t_store_d5)*/
DELETE FROM tmp_batapply_avail_store;
SELECT wareid BULK COLLECT INTO v_wareid_list FROM tmp_batsale_act_alloc;
FORALL indx IN v_wareid_list.first .. v_wareid_list.last
INSERT INTO tmp_batapply_avail_store
(compid,
batid,
wareid,
invalidate,
wareqty,
awaitqty,
stallno,
busno,
factoryid,
makeno,
makedate,
purtax,
purprice,
areacode,
maxqty,
midqty,
notes,
stalltype,
sealtype,
seq_id,
awaitqty_makeno,
availableqty,
actualqty,
whlprice,
isstopselling,
ownerid)
SELECT compid,
batid,
wareid,
invalidate,
wareqty,
awaitqty,
stallno,
busno,
factoryid,
makeno,
makedate,
purtax,
purprice,
areacode,
maxqty,
midqty,
notes,
stalltype,
sealtype,
seq_temp_batsaleapply_store.nextval,
0,
wareqty - nvl(awaitqty, 0),
0,
whlprice,
isstopselling,
ownerid
FROM (SELECT *
FROM (SELECT /*+index(sd idx_t_store_d5)*/
si.compid,
si.batid,
si.wareid,
si.invalidate,
sd.wareqty,
sd.awaitqty,
sd.stallno,
sd.busno,
si.factoryid,
si.makeno,
si.makedate,
si.purtax,
si.purprice,
si.areacode,
si.maxqty,
si.midqty,
h.notes,
a.stalltype,
a.sealtype,
sd.wareqty - nvl(sd.awaitqty, 0) AS availableqty,
d.whlprice,
nvl(si.isstopselling, 1) isstopselling,
'' AS ownerid
FROM t_store_i si,
t_store_d sd,
t_batsaleapply_h h,
t_stall a,
t_batsaleapply_d d,
t_ware tw
WHERE si.compid = v_compid
AND si.wareid = v_wareid_list(indx)
AND sd.busno = v_busno
AND si.batid = sd.batid
AND si.wareid = sd.wareid
AND tw.wareid = d.wareid
and tw.compid = h.compid
and sd.wareqty - sd.awaitqty > 0
AND si.ownerid = h.ownerid
AND h.applyno = p_applyno
AND (v_para2778 <> 0 OR
(v_para2778 = 0 AND
sd.stallno NOT IN
(SELECT TRIM(regexp_substr(v_para2975,
'[^,]+',
1,
LEVEL))
FROM dual
CONNECT BY rownum <=
regexp_count(v_para2975, ',') + 1)))
AND (si.invalidate is null or
si.invalidate > v_para2977 + SYSDATE)
AND sd.stallno = a.stallno
AND sd.compid = a.compid
AND sd.busno = a.busno
AND a.stalltype = 11
AND h.applyno = d.applyno
AND d.wareid = v_wareid_list(indx)
AND v_wms001 <> '1'
ORDER BY CASE
WHEN v_para2825 = '1' THEN --按效期
to_char(si.invalidate - to_date('19700101', 'yyyymmdd')) --UNIX时间
WHEN v_para2825 = '2' THEN --按库存数
lpad(to_char(floor(sd.wareqty)), 10, '0')
WHEN v_para2825 = '3' THEN --按批号
nvl(si.makeno, '')
ELSE
lpad(to_char(si.batid), 20, '0') --按批次
END)
UNION ALL
SELECT *
FROM (SELECT t_store_d.compid,
0 AS batid,
t_store_d.wareid,
MIN(t_store_i.invalidate) AS invalidate,
MAX(t_store_makeno.wareqty) AS wareqty,
(MAX(t_store_makeno.awaitqty) +
MAX(t_store_makeno.defectqty) +
MAX(t_store_makeno.testqty)) AS awaitqty,
CAST('ALL' AS VARCHAR2(40)) AS stallno,
MAX(t_store_makeno.busno) AS busno,
MAX(CASE
WHEN v_ware.factoryid = 0 THEN
t_store_i.factoryid
ELSE
v_ware.factoryid
END) AS factoryid,
t_store_i.makeno AS makeno,
MIN(t_store_i.makedate) AS makedate,
MAX(t_store_i.purtax) AS purtax,
MAX(t_store_i.purprice) AS purprice,
MAX(v_ware.areacode) AS areacode,
MAX(t_store_i.maxqty) AS maxqty,
MAX(t_store_i.midqty) AS midqty,
MAX(h.notes) AS notes,
MAX(v_stall.stalltype) AS stalltype,
MAX(v_stall.sealtype) AS sealtype,
(MAX(t_store_makeno.wareqty) -
MAX(t_store_makeno.awaitqty) -
MAX(t_store_makeno.defectqty) -
MAX(t_store_makeno.testqty)) AS availableqty,
MIN(d.whlprice) AS whlprice,
nvl(MAX(t_store_i.isstopselling), 1) AS isstopselling,
t_store_i.ownerid
FROM t_store_i t_store_i
JOIN t_store_d t_store_d
ON t_store_i.batid = t_store_d.batid
AND t_store_i.wareid = t_store_d.wareid
JOIN t_store_makeno t_store_makeno
ON t_store_d.compid = t_store_makeno.compid
AND t_store_d.busno = t_store_makeno.busno
AND t_store_d.wareid = t_store_makeno.wareid
AND t_store_i.makeno = t_store_makeno.makeno
AND t_store_i.ownerid = t_store_makeno.ownerid
JOIN t_store_h h
ON h.compid = t_store_i.compid
AND h.wareid = t_store_i.wareid
AND h.busno = t_store_makeno.busno
JOIN t_stall v_stall
ON t_store_d.stallno = v_stall.stallno
AND t_store_d.compid = v_stall.compid
AND t_store_d.busno = v_stall.busno
JOIN t_batsaleapply_h h
ON h.applyno = p_applyno
AND h.compid = t_store_i.compid
AND h.ownerid = t_store_i.ownerid
JOIN t_batsaleapply_d d
ON h.applyno = d.applyno
AND d.wareid = v_wareid_list(indx)
JOIN t_ware v_ware
ON v_ware.wareid = t_store_d.wareid
AND v_ware.compid = t_store_d.compid
WHERE t_store_makeno.wareqty - t_store_makeno.awaitqty > 0
AND t_store_makeno.wareqty > 0
AND (t_store_i.invalidate is null or
t_store_i.invalidate > trunc(SYSDATE))
AND t_store_d.wareqty > 0
AND t_store_makeno.wareid = v_wareid_list(indx)
AND t_store_makeno.compid = v_compid
AND t_store_makeno.busno = v_busno
AND v_wms001 = '1'
GROUP BY t_store_d.compid,
t_store_d.busno,
t_store_d.wareid,
t_store_i.ownerid,
t_store_i.makeno
ORDER BY invalidate) m);
--若在批发批次对应客户价格设置中设置了价格，转单时批销价取设置的批销价，否则取申请单的价格
IF v_wms001 <> '1' THEN
FOR rec IN (SELECT a.compid, a.wareid, a.batid, a.busno, a.seq_id
FROM tmp_batapply_avail_store a) LOOP
SELECT COUNT(1), MAX(whlprice)
INTO v_count1, v_whlprice
FROM t_batsale_cust_whlprice t
WHERE t.compid = rec.compid
AND t.vencusno = v_vencusno
AND (t.whlgroupid = v_whlgroupid OR t.whlgroupid = 0)
AND t.wareid = rec.wareid
AND (t.batid = 0 OR t.batid = rec.batid)
AND (t.subitemid = v_subitemid OR v_subitemid IS NULL)
AND nvl(t.status, 1) = 1;
IF v_count1 > 0 THEN
UPDATE tmp_batapply_avail_store a
SET a.whlprice = v_whlprice
WHERE a.seq_id = rec.seq_id;
END IF;
END LOOP;
END IF;
--这里预分配待出库fyy
IF v_para2778 <> '0' AND v_wms001 <> '1' THEN
FOR reca IN (SELECT compid, wareid, busno
FROM tmp_batsale_act_alloc
WHERE EXISTS
(SELECT 1
FROM tmp_batapply_avail_store b
WHERE tmp_batsale_act_alloc.compid = b.compid
AND tmp_batsale_act_alloc.wareid = b.wareid
AND tmp_batsale_act_alloc.busno = b.busno)) LOOP
SELECT awaitqty
INTO v_awaitqty_owner
FROM t_store_owner
WHERE compid = reca.compid
AND wareid = reca.wareid
AND busno = reca.busno
AND ownerid = v_ownerid;
SELECT SUM(awaitqty)
INTO v_awaitqty_makeno
FROM t_store_makeno
WHERE compid = reca.compid
AND wareid = reca.wareid
AND busno = reca.busno
AND ownerid = v_ownerid;
SELECT SUM(a.awaitqty)
INTO v_awaitqty
FROM t_store_d a, t_store_i b
WHERE a.compid = b.compid
AND a.wareid = b.wareid
AND a.batid = b.batid
AND b.ownerid = v_ownerid
AND a.compid = reca.compid
AND a.busno = reca.busno
AND a.wareid = reca.wareid;
IF v_awaitqty = v_awaitqty_makeno AND
v_awaitqty_owner = v_awaitqty_makeno THEN
continue;
END IF;
INSERT INTO tmp_batapply_store_i
(compid,
batid,
makeno,
wareid,
wareqty,
awaitqty,
busno,
seq_id,
awaitqtyfp)
SELECT compid,
batid,
makeno,
wareid,
wareqty,
awaitqty,
busno,
seq_id,
0
FROM tmp_batapply_avail_store a
WHERE a.busno = reca.busno
AND a.wareid = reca.wareid
AND a.compid = reca.compid;
IF v_awaitqty_makeno > v_awaitqty THEN
FOR recb IN (SELECT a.*
FROM t_store_makeno a
WHERE a.compid = reca.compid
AND a.wareid = reca.wareid
AND a.ownerid = v_ownerid
AND a.busno = reca.busno
AND EXISTS (SELECT 1
FROM tmp_batapply_store_i b
WHERE a.compid = b.compid
AND a.wareid = b.wareid
AND a.busno = b.busno
AND a.makeno = b.makeno)) LOOP
SELECT nvl(SUM(awaitqty), 0)
INTO v_awaitqty
FROM t_store_d a, t_store_i b
WHERE a.compid = b.compid
AND a.wareid = b.wareid
AND a.batid = b.batid
AND a.busno = reca.busno
AND b.ownerid = v_ownerid
AND b.makeno = recb.makeno
AND a.compid = reca.compid
AND a.wareid = reca.wareid
AND NOT EXISTS (SELECT 1
FROM tmp_batapply_store_i c
WHERE a.compid = c.compid
AND a.wareid = c.wareid
AND a.busno = c.busno
AND a.batid = c.batid);
recb.awaitqty := recb.awaitqty - v_awaitqty;
SELECT SUM(awaitqty)
INTO v_awaitqty
FROM tmp_batapply_store_i
WHERE compid = recb.compid
AND wareid = recb.wareid
AND makeno = recb.makeno;
IF recb.awaitqty > v_awaitqty THEN
recb.awaitqty := recb.awaitqty - v_awaitqty;
MERGE INTO tmp_batapply_store_i a
USING (SELECT compid,
seq_id,
CASE
WHEN sum_store <= applyqty THEN
canstoreqty
ELSE
applyqty + canstoreqty - sum_store
END qty
FROM (SELECT compid,
seq_id,
(b.wareqty - b.awaitqty) AS canstoreqty,
SUM(b.wareqty - b.awaitqty) over(PARTITION BY b.wareid ORDER BY b.seq_id DESC) sum_store,
recb.awaitqty AS applyqty
FROM tmp_batapply_store_i b
WHERE b.wareid = recb.wareid
AND b.busno = recb.busno
AND b.makeno = recb.makeno
AND b.wareqty - b.awaitqty > 0
ORDER BY b.seq_id DESC)
WHERE sum_store - canstoreqty < applyqty) b
ON (a.seq_id = b.seq_id)
WHEN MATCHED THEN
UPDATE SET a.awaitqty = a.awaitqty + b.qty;
END IF;
END LOOP;
END IF;
IF v_awaitqty_owner > v_awaitqty_makeno THEN
MERGE INTO tmp_batapply_store_i a
USING (SELECT compid,
seq_id,
CASE
WHEN sum_store <= applyqty THEN
canstoreqty
ELSE
applyqty + canstoreqty - sum_store
END qty
FROM (SELECT compid,
seq_id,
(b.wareqty - b.awaitqty) AS canstoreqty,
SUM(b.wareqty - b.awaitqty) over(PARTITION BY b.wareid ORDER BY b.seq_id DESC) sum_store,
(v_awaitqty_owner - v_awaitqty_makeno) AS applyqty
FROM tmp_batapply_store_i b
WHERE b.wareqty - b.awaitqty > 0
AND b.wareid = reca.wareid
AND b.busno = reca.busno
ORDER BY b.seq_id DESC)
WHERE sum_store - canstoreqty < applyqty) b
ON (a.seq_id = b.seq_id)
WHEN MATCHED THEN
UPDATE SET a.awaitqty = a.awaitqty + b.qty;
END IF;
MERGE INTO tmp_batapply_avail_store a
USING (SELECT * FROM tmp_batapply_store_i) b
ON (a.seq_id = b.seq_id)
WHEN MATCHED THEN
UPDATE
SET a.availableqty = a.availableqty + a.awaitqty - b.awaitqty,
a.awaitqty = b.awaitqty;
END LOOP;
END IF;
/*这里逐个获取是因为，按照品种找库存比直接关联tmp_batsale_act_alloc更快*/
SELECT h.vencusno,
h.saler,
h.ownerid,
h.compid,
b.vencuscode,
nvl(c.makeno_check, 0),
nvl(c.check_batch_flag, 1)
INTO v_vencusno_batsale,
v_saler,
v_ownerid,
v_compid_batsale,
v_vencuscode,
v_makeno_check,
v_check_batch_flag
FROM t_batsaleapply_h h, t_vencus_base b, t_vencus c
WHERE h.applyno = p_applyno
AND h.vencusno = b.vencusno
AND h.compid = c.compid
AND h.vencusno = c.vencusno;
--GSP检查
/*FOR rec_store IN (SELECT st.*, al.applyqty
FROM tmp_batapply_avail_store st,
tmp_batsale_act_alloc al
WHERE st.compid = al.compid
AND st.busno = al.busno
AND st.wareid = al.wareid
AND st.wareid = al.wareid) LOOP*/
select count(1)
into v_count
from tmp_batapply_avail_store
where availableqty > 0;
if v_count > 0 then
BEGIN
DELETE FROM tmp_ware_gspcheck;
INSERT INTO tmp_ware_gspcheck
(seqno,
billcode,
billno,
rowno,
compid,
busno,
vencusno,
client,
wareid,
warecode,
batid,
stallno,
makeno,
invalidate,
direct,
status)
select st.seq_id,
'WHL',
NULL,
st.seq_id,
v_compid,
st.busno,
v_vencusno,
NULL,
st.wareid,
NULL,
st.batid,
st.stallno,
st.makeno,
st.invalidate,
-1,
1
FROM tmp_batapply_avail_store st, tmp_batsale_act_alloc al
WHERE st.compid = al.compid
AND st.busno = al.busno
AND st.wareid = al.wareid
AND st.wareid = al.wareid;
BEGIN
/* proc_ware_gspcheck(0, '',
'quality_check,warning_ware,range_bus,certif_bus,certif_ware,range_cust,certif_cust',
2);*/
proc_ware_gsp_bakcheck(0,
'',
'quality_check,warning_ware,range_bus,certif_bus,certif_ware,range_cust,certif_cust',
2);
--EXCEPTION
--WHEN OTHERS THEN
INSERT INTO tmp_batsaleapply_gsp_del
(applyno, vencusno, wareid)
SELECT p_applyno, v_vencusno, wareid
FROM tmp_ware_gspcheck
WHERE status = 0
and NOT EXISTS
(SELECT 1
FROM tmp_batsaleapply_gsp_del del
WHERE del.applyno = applyno
AND del.vencusno = v_vencusno
AND del.wareid = tmp_ware_gspcheck.wareid);
DELETE FROM tmp_batapply_avail_store
WHERE seq_id in
(select seq_id from tmp_ware_gspcheck where status = 0);
END;
END;
end if;
--END LOOP;
v_makejs := 0;
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,
accchkdate,
accchker,
accchked,
invoicetype,
addrid,
whlgroupid,
notes,
lastmodify,
lasttime,
status,
execdate,
checker1,
checkbit1,
checker2,
checkbit2,
checker3,
checkbit3,
checker4,
checkbit4,
checker5,
checkbit5,
paytype,
cashtype,
ownerid,
reckonerid,
subitemid,
parentbatsaltno,
loadorder,
checkdate1,
checkdate2,
checkdate3,
checkdate4,
checkdate5,
notes1,
createtime,
createuser,
versionnum,
guarantor,
salename,
salezone,
wms_flag,
client,
source_type,
djbh,
billsource,
sale_idbs
)
SELECT v_batsaleno,
'WHL',
--billcode,
h.compid,
--compid,
NULL,
--batcontno,
p_applyno,
--batapplyno,
h.vencusno,
b.vencusname,
h.busno,
h.saler,
--saler,
h.pickuptype,
--pickuptype,
NULL,
--accchkdate,
NULL,
--accchker,
0,
--accchked,o
h.invoicetype,
h.addrid,
--addrid,
h.whlgroupid,
--whlgroupid,
NULL,
--notes,
p_lastmodify,
--lastmodify,
SYSDATE,
--lasttime,
0,
--status,
NULL,
--execdate,
NULL,
--checker1,
0,
--checkbit1,
NULL,
--checker2,
0,
--checkbit2,
NULL,
--checker3,
0,
--checkbit3,
NULL,
--checker4,
0,
--checkbit4,
NULL,
--checker5,
0,
--checkbit5,
h.paytype,
--paytype, 月结
h.cashtype,
h.ownerid,
--ownerid,
h.reckonerid,
--reckonerid,
h.subitemid,
--subitemid,
NULL,
--parentbatsaltno,
NULL,
--loadorder,
NULL,
--checkdate1,
NULL,
--checkdate2,
NULL,
--checkdate3,
NULL,
--checkdate4,
NULL,
--checkdate5,
NULL,
--notes1,
SYSDATE,
--createtime,
p_lastmodify,
--createuser,
1,
--versionnum,
NULL,
--guarantor,
NULL,
--salename,
NULL,
--salezone
CASE
WHEN v_parawms0001 = '1' AND length(TRIM(v_wms_ownerid)) > 0 THEN
'1'
ELSE
'0'
END,
--wms_flag,
h.client,
--client
CASE
WHEN instr(h.notes, '委托配送申请单') > 0 THEN
1
ELSE
0
END AS source_type,
h.djbh,
h.billsource,
h.sale_idbs
FROM t_batsaleapply_h h, t_vencus_base b
WHERE h.applyno = p_applyno
AND h.vencusno = b.vencusno;
v_notes1 := '';
select count(1)
into v_count
from tmp_batapply_avail_store
where availableqty > 0;
if v_count > 0 then
FOR rec IN (SELECT d.wareid,
h.compid,
d.checkqty AS wareqty,
tw.warecode,
h.cashtype
FROM t_batsaleapply_h h, t_batsaleapply_d d, t_ware tw
WHERE h.applyno = p_applyno
AND h.applyno = d.applyno
AND h.compid = tw.compid
AND d.wareid = tw.wareid
AND nvl(d.checkqty, 0) > 0) LOOP
v_isxx := 0; --是否限销
v_mesgxx := '';
delete from tmp_batsale_restrict; --清空临时表
FOR rec_store IN (SELECT st.*,
al.applyqty,
decode(st.batid,
0,
'M' || st.makeno,
st.batid) AS batid1
FROM tmp_batapply_avail_store st,
tmp_batsale_act_alloc al
WHERE st.compid = al.compid
AND st.busno = al.busno
AND st.wareid = al.wareid
AND st.wareid = rec.wareid
AND st.availableqty > 0
AND st.isstopselling <> 2
AND EXISTS
(SELECT 1
FROM t_batsale_restrict_h a,
t_batsale_restrict_d b
WHERE a.restrictno = b.restrictno
AND a.status = 1
--AND b.wareid = st.wareid
AND ((nvl(b.rest_flag, 0) = 0 AND
b.wareid = st.wareid) OR
(nvl(b.rest_flag, 0) = 1 AND EXISTS
(SELECT 1
FROM t_ware_class_base d
WHERE (d.compid = rec.compid OR
(f_get_sys_inicode(rec.compid,
'3034',
NULL) = '0' AND
d.compid = 0))
AND d.classgroupno =
b.classgroupno
AND d.classcode = b.classcode
AND d.wareid = rec.wareid)))
AND a.compid = st.compid
--AND b.wareid = rec.wareid
AND a.compid = rec.compid
AND a.startdate <= SYSDATE
AND a.enddate >= SYSDATE)
ORDER BY CASE
WHEN v_makeno_check > 0 and
v_makeno_check <> 99999 THEN
st.availableqty
ELSE
st.seq_id
END) LOOP
v_isxx := 1;
v_js := 0;
IF rec_store.availableqty = 0 THEN
--DELETE FROM tmp_batapply_avail_store WHERE seq_id = rec_store.seq_id;
continue;
END IF;
pro_restrict_qty(v_compid,
rec_store.busno,
rec_store.wareid,
rec_store.batid1,
v_vencuscode,
v_ownerid,
v_saler,
p_lastmodify,
rec_store.availableqty,
0,
v_wareqty,
v_msg,
rec.cashtype);
IF v_wareqty < rec_store.availableqty THEN
--&@ *@ 请勿改动 否则会导致前端显示出错
IF TRIM(v_msg) IS NOT NULL THEN
v_js := v_js + 1;
v_mesgxx := v_msg;
END IF;
/*IF v_wareqty <= 0 THEN
DELETE FROM tmp_batapply_avail_store
WHERE seq_id = rec_store.seq_id;
END IF;*/ --这里如果被限制不能删除。因为要考虑到缺货登记
--更新已分配数量
UPDATE tmp_batapply_avail_store
SET actualqty = v_wareqty + actualqty
WHERE seq_id = rec_store.seq_id;
ELSE
UPDATE tmp_batapply_avail_store
SET actualqty = availableqty
WHERE seq_id = rec_store.seq_id;
END IF;
END LOOP;
v_mestz := '';
SELECT SUM(nvl(actualqty, 0))
INTO v_actualqty
FROM tmp_batapply_avail_store
WHERE wareid = rec.wareid;
IF v_actualqty < rec.wareqty THEN
IF v_isxx = 1 THEN
v_notes1 := v_notes1 || v_mesgxx;
END IF;
SELECT COUNT(1)
INTO v_count
FROM tmp_batapply_avail_store
WHERE compid = rec.compid
AND wareid = rec.wareid
AND isstopselling = 2;
IF v_count > 0 THEN
v_mestz := '商品' || rec.warecode || '存在已经停销批次' || chr(13);
END IF;
IF v_mestz IS NOT NULL THEN
v_notes1 := v_notes1 || chr(13) || v_mestz;
END IF;
END IF; --存在限销得商品记录报错信息
IF (v_makeno_check > 0 and v_makeno_check <> 99999) AND
v_check_batch_flag = '2' THEN
--这里取值取几个可用最大值的批号
UPDATE tmp_batapply_avail_store
SET status = 0
WHERE wareid = rec.wareid;
IF v_isxx = 1 THEN
MERGE INTO tmp_batapply_avail_store a
USING (SELECT *
FROM (SELECT qty, compid, wareid, busno, makeno
FROM (SELECT SUM(actualqty) qty,
compid,
wareid,
busno,
makeno
FROM tmp_batapply_avail_store
WHERE isstopselling <> 2
AND wareid = rec.wareid
GROUP BY compid, wareid, busno, makeno) a
ORDER BY qty DESC) a
WHERE rownum <= v_makeno_check) b
ON (a.compid = b.compid AND a.wareid = b.wareid AND a.busno = b.busno AND a.makeno = b.makeno)
WHEN MATCHED THEN
UPDATE SET a.status = 1;
IF v_makejs = 0 THEN
SELECT SUM(actualqty) --被限制了分配数量
INTO v_actualqty
FROM tmp_batapply_avail_store a
WHERE wareid = rec.wareid
AND status = 1;
SELECT SUM(actualqty) --实际可分配数量
INTO v_actualqty1
FROM tmp_batapply_avail_store a
WHERE wareid = rec.wareid;
IF v_actualqty < rec.wareqty AND v_actualqty1 > v_actualqty THEN
v_makejs := 1;
END IF;
END IF;
UPDATE tmp_batapply_avail_store
SET actualqty = 0
WHERE wareid = rec.wareid
AND status = 0;
ELSE
MERGE INTO tmp_batapply_avail_store a
USING (SELECT *
FROM (SELECT qty, compid, wareid, busno, makeno
FROM (SELECT SUM(availableqty) qty,
compid,
wareid,
busno,
makeno
FROM tmp_batapply_avail_store
WHERE isstopselling <> 2
AND wareid = rec.wareid
GROUP BY compid, wareid, busno, makeno) a
ORDER BY qty DESC) a
WHERE rownum <= v_makeno_check) b
ON (a.compid = b.compid AND a.wareid = b.wareid AND a.busno = b.busno AND a.makeno = b.makeno)
WHEN MATCHED THEN
UPDATE SET a.status = 1;
UPDATE tmp_batapply_avail_store
SET actualqty = availableqty
WHERE wareid = rec.wareid
AND status = 1;
IF v_makejs = 0 THEN
SELECT SUM(actualqty)
INTO v_actualqty
FROM tmp_batapply_avail_store a
WHERE wareid = rec.wareid
AND status = 1;
SELECT SUM(availableqty)
INTO v_actualqty1
FROM tmp_batapply_avail_store a
WHERE wareid = rec.wareid;
IF v_actualqty < rec.wareqty AND v_actualqty1 > v_actualqty THEN
v_makejs := 1;
END IF;
END IF;
UPDATE tmp_batapply_avail_store
SET actualqty = 0
WHERE wareid = rec.wareid
AND status = 0;
END IF;
END IF;
end loop;
delete from tmp_batsale_restrict; --清空临时表
FOR rec IN (SELECT d.wareid,
h.compid,
d.checkqty AS wareqty,
tw.warecode,
h.cashtype,
h.busno
FROM t_batsaleapply_h h, t_batsaleapply_d d, t_ware tw
WHERE h.applyno = p_applyno
AND h.applyno = d.applyno
AND h.compid = tw.compid
AND d.wareid = tw.wareid
AND nvl(d.checkqty, 0) > 0) LOOP
v_isxx := 0; --是否限销
SELECT count(1)
into v_count
FROM t_batsale_restrict_h a, t_batsale_restrict_d b
WHERE a.restrictno = b.restrictno
AND a.status = 1
--AND b.wareid = st.wareid
AND ((nvl(b.rest_flag, 0) = 0 AND b.wareid = rec.wareid) OR
(nvl(b.rest_flag, 0) = 1 AND EXISTS
(SELECT 1
FROM t_ware_class_base d
WHERE (d.compid = rec.compid OR
(f_get_sys_inicode(rec.compid, '3034', NULL) = '0' AND
d.compid = 0))
AND d.classgroupno = b.classgroupno
AND d.classcode = b.classcode
AND d.wareid = rec.wareid)))
AND a.compid = rec.compid
AND a.startdate <= SYSDATE
AND a.enddate >= SYSDATE;
if v_count > 0 then
v_isxx := 1;
end if;
SELECT SUM(nvl(actualqty, 0))
INTO v_actualqty
FROM tmp_batapply_avail_store
WHERE wareid = rec.wareid;
IF v_actualqty = 0 AND v_isxx = 0 THEN
--商品没有受到限销
IF v_para8139 = '1' THEN
--先计算整件
select nvl(a.maxqty * (floor(a.applyqty / a.maxqty)), 0) applyqty
into v_applyqty --整件出库数量
from tmp_batsale_act_alloc a
WHERE a.maxqty * (floor(a.applyqty / a.maxqty)) > 0
AND a.wareid = rec.wareid;
if v_applyqty > 0 then
/* FOR recd IN (SELECT a.compid,
a.busno,
a.wareid,
a.maxqty * (floor(a.applyqty / a.maxqty)) applyqty
FROM tmp_batsale_act_alloc a
WHERE a.maxqty * (floor(a.applyqty / a.maxqty)) > 0
AND a.wareid = rec.wareid) LOOP*/
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
wareid,
nvl(factoryid, -1),
--factoryid,
CASE
WHEN sumstore_qty <= v_applyqty THEN
availableqty
ELSE
v_applyqty + availableqty - sumstore_qty
END,
--wareqty
stallno,
--stallno,
batid,
--batid,
makeno,
--makeno,
makedate,
--makedate,
purprice,
purtax,
saletax,
--saletax,
0,
--saleprice,
invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
maxqty,
areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
ownerid --ifselectbat
FROM (SELECT a.*,
c.saletax,
SUM(a.availableqty) over(PARTITION BY a.compid, a.busno, a.wareid ORDER BY a.seq_id) sumstore_qty
FROM tmp_batapply_avail_store a
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid
WHERE a.wareid = rec.wareid
AND a.compid = rec.compid
AND a.isstopselling <> 2
AND a.busno = rec.busno
AND a.sealtype = 2
AND a.availableqty > 0
ORDER BY a.seq_id)
WHERE /*v_sum_qty - applyqty > 0 AND */
sumstore_qty - availableqty < v_applyqty;
MERGE INTO tmp_batapply_avail_store a
USING (SELECT batid, wareid, stallno, makeno, wareqty
FROM t_batsale_d
WHERE batsaleno = v_batsaleno) b
ON (a.wareid = b.wareid AND a.batid = b.batid AND a.stallno = b.stallno AND a.makeno = b.makeno)
WHEN MATCHED THEN
UPDATE
SET a.actualqty = b.wareqty
WHERE a.wareid = rec.wareid;
SELECT nvl(SUM(wareqty), 0)
INTO v_wareqty
FROM t_batsale_d
WHERE batsaleno = v_batsaleno
AND wareid = rec.wareid;
UPDATE tmp_batsale_act_alloc a
SET a.actualqty = v_wareqty
WHERE a.compid = rec.compid
AND a.wareid = rec.wareid
AND a.busno = rec.busno;
end if;
-- END LOOP;
SELECT COUNT(1)
INTO v_count
FROM tmp_batsale_act_alloc
WHERE applyqty > actualqty
AND wareid = rec.wareid;
--剩余零件出库
IF v_count > 0 THEN
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
wareid,
nvl(factoryid, -1),
--factoryid,
CASE
WHEN sumstore_qty <= applyqtyt THEN
cansaleqty
ELSE
applyqtyt + cansaleqty - sumstore_qty
END,
--wareqty
stallno,
--stallno,
batid,
--batid,
makeno,
--makeno,
makedate,
--makedate,
purprice,
purtax,
saletax,
--saletax,
0,
--saleprice,
invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
maxqty,
areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
ownerid --ifselectbat
FROM (SELECT a.*,
c.saletax,
SUM(a.availableqty - a.actualqty) over(PARTITION BY a.compid, a.busno, a.wareid ORDER BY a.sealtype, a.seq_id) sumstore_qty,
(b.applyqty - b.actualqty) AS applyqtyt,
(a.availableqty - a.actualqty) AS cansaleqty
FROM tmp_batapply_avail_store a
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid,
tmp_batsale_act_alloc b
WHERE a.wareid = b.wareid
AND a.compid = b.compid
AND a.busno = b.busno
AND a.isstopselling <> 2
AND a.wareid = rec.wareid
AND a.availableqty - a.actualqty > 0
AND b.applyqty > b.actualqty
ORDER BY a.sealtype, a.seq_id)
WHERE sumstore_qty - cansaleqty < applyqtyt;
END IF;
ELSE
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
wareid,
nvl(factoryid, -1),
--factoryid,
CASE
WHEN sum_store <= applyqty THEN
availableqty
ELSE
applyqty + availableqty - sum_store
END,
--wareqty
stallno,
--stallno,
batid,
--batid,
makeno,
--makeno,
makedate,
--makedate,
purprice,
purtax,
saletax,
--saletax,
0,
--saleprice,
invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
maxqty,
areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
ownerid --ifselectbat
FROM (SELECT a.*,
b.applyqty,
(SUM(a.availableqty)
over(PARTITION BY a.compid,
a.busno,
a.wareid ORDER BY a.seq_id)) sum_store,
c.saletax
FROM tmp_batapply_avail_store a
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid, tmp_batsale_act_alloc b
WHERE a.wareid = b.wareid
AND a.compid = b.compid
AND a.wareid = rec.wareid
AND a.isstopselling <> 2
AND a.busno = b.busno
AND a.availableqty > 0
ORDER BY a.seq_id)
WHERE sum_store - availableqty < applyqty;
END IF;
ELSE
--商品受到限销
IF v_para8139 = '1' THEN
--先计算整件
Begin
select nvl(a.maxqty * (floor(a.applyqty / a.maxqty)), 0) applyqty
into v_applyqty --整件出库数量
from tmp_batsale_act_alloc a
WHERE a.maxqty * (floor(a.applyqty / a.maxqty)) > 0
AND a.wareid = rec.wareid;
EXCEPTION
WHEN no_data_found THEN
v_applyqty := 0;
END;
if v_applyqty > 0 then
v_actualqty := 0;
for recd in (select a.*,
c.saletax,
decode(a.batid, 0, 'M' || a.makeno, a.batid) AS batid1
FROM tmp_batapply_avail_store a
join tmp_batsale_act_alloc al
on a.compid = al.compid
AND a.busno = al.busno
AND a.wareid = al.wareid
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid
WHERE a.wareid = rec.wareid
AND a.compid = rec.compid
AND a.isstopselling <> 2
AND a.busno = rec.busno
AND a.sealtype = 2
AND a.actualqty > 0
ORDER BY a.seq_id) loop
if v_actualqty = v_applyqty then
exit;
end if;
IF v_isxx = 1 THEN
pro_restrict_qty(v_compid,
recd.busno,
recd.wareid,
recd.batid1,
v_vencuscode,
v_ownerid,
v_saler,
p_lastmodify,
recd.actualqty,
0,
v_wareqty,
v_msg,
rec.cashtype);
else
v_wareqty := recd.actualqty;
end if;
if v_wareqty = 0 then
continue;
end if;
if v_wareqty >= v_applyqty - v_actualqty then
v_wareqty := v_applyqty - v_actualqty;
end if;
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
recd.wareid,
nvl(recd.factoryid, -1),
--factoryid,
v_wareqty,
--wareqty
recd.stallno,
--stallno,
recd.batid,
--batid,
recd.makeno,
--makeno,
recd.makedate,
--makedate,
recd.purprice,
recd.purtax,
recd.saletax,
--saletax,
0,
--saleprice,
recd.invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
recd.whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
recd.midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
recd.maxqty,
recd.areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
recd.ownerid --ifselectbat
FROM dual;
UPDATE tmp_batapply_avail_store
SET awaitqty_makeno = v_wareqty
WHERE wareid = recd.wareid
AND batid = recd.batid
AND stallno = recd.stallno; --已分配数量存放tmp_batapply_avail_store.awaitqty_makeno
v_actualqty := v_actualqty + v_wareqty;
END LOOP;
SELECT nvl(SUM(wareqty), 0)
INTO v_wareqty
FROM t_batsale_d
WHERE wareid = rec.wareid
AND batsaleno = v_batsaleno;
UPDATE tmp_batsale_act_alloc a
SET a.actualqty = v_wareqty
WHERE a.compid = rec.compid
AND a.wareid = rec.wareid
AND a.busno = rec.busno;
end if;
SELECT applyqty - nvl(actualqty, 0)
INTO v_applyqty
FROM tmp_batsale_act_alloc
WHERE wareid = rec.wareid;
--剩余零件出库
IF v_applyqty > 0 THEN
v_actualqty := 0;
for recd in (select a.*,
c.saletax,
decode(a.batid, 0, 'M' || a.makeno, a.batid) AS batid1
FROM tmp_batapply_avail_store a
join tmp_batsale_act_alloc al
on a.compid = al.compid
AND a.busno = al.busno
AND a.wareid = al.wareid
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid
WHERE a.wareid = rec.wareid
AND a.compid = rec.compid
AND a.isstopselling <> 2
AND a.busno = rec.busno
AND a.actualqty > 0
AND a.actualqty - a.awaitqty_makeno > 0
ORDER BY a.sealtype, a.seq_id) loop
if v_actualqty = v_applyqty then
exit;
end if;
IF v_isxx = 1 THEN
pro_restrict_qty(v_compid,
recd.busno,
recd.wareid,
recd.batid1,
v_vencuscode,
v_ownerid,
v_saler,
p_lastmodify,
recd.actualqty,
0,
v_wareqty,
v_msg,
rec.cashtype);
else
v_wareqty := recd.actualqty;
end if;
if v_wareqty = 0 then
continue;
end if;
if v_wareqty >= v_applyqty - v_actualqty then
v_wareqty := v_applyqty - v_actualqty;
end if;
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
recd.wareid,
nvl(recd.factoryid, -1),
--factoryid,
v_wareqty,
--wareqty
recd.stallno,
--stallno,
recd.batid,
--batid,
recd.makeno,
--makeno,
recd.makedate,
--makedate,
recd.purprice,
recd.purtax,
recd.saletax,
--saletax,
0,
--saleprice,
recd.invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
recd.whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
recd.midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
recd.maxqty,
recd.areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
recd.ownerid --ifselectbat
FROM dual;
UPDATE tmp_batapply_avail_store
SET awaitqty_makeno = v_wareqty
WHERE wareid = recd.wareid
AND batid = recd.batid
AND stallno = recd.stallno; --已分配数量存放tmp_batapply_avail_store.awaitqty_makeno
v_actualqty := v_actualqty + v_wareqty;
end loop;
END IF;
ELSE
SELECT applyqty - nvl(actualqty, 0)
INTO v_applyqty
FROM tmp_batsale_act_alloc
WHERE wareid = rec.wareid;
IF v_applyqty > 0 THEN
v_actualqty := 0;
for recd in (select a.*,
c.saletax,
decode(a.batid, 0, 'M' || a.makeno, a.batid) AS batid1
FROM tmp_batapply_avail_store a
join tmp_batsale_act_alloc al
on a.compid = al.compid
AND a.busno = al.busno
AND a.wareid = al.wareid
LEFT JOIN t_ware c
ON a.wareid = c.wareid
AND a.compid = c.compid
WHERE a.wareid = rec.wareid
AND a.compid = rec.compid
AND a.isstopselling <> 2
AND a.busno = rec.busno
AND a.actualqty > 0
ORDER BY a.seq_id) loop
if v_actualqty = v_applyqty then
exit;
end if;
IF v_isxx = 1 THEN
pro_restrict_qty(v_compid,
recd.busno,
recd.wareid,
recd.batid1,
v_vencuscode,
v_ownerid,
v_saler,
p_lastmodify,
recd.actualqty,
0,
v_wareqty,
v_msg,
rec.cashtype);
else
v_wareqty := recd.actualqty;
end if;
if v_wareqty = 0 then
continue;
end if;
if v_wareqty >= v_applyqty - v_actualqty then
v_wareqty := v_applyqty - v_actualqty;
end if;
INSERT INTO t_batsale_d
(batsaleno,
rowno,
wareid,
factoryid,
wareqty,
stallno,
batid,
makeno,
makedate,
purprice,
purtax,
saletax,
saleprice,
invalidate,
whlprice,
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,
packnum,
ifselectbat,
ownerid)
SELECT v_batsaleno,
rownum + (SELECT nvl(MAX(rowno), 0)
FROM t_batsale_d
WHERE batsaleno = v_batsaleno),
recd.wareid,
nvl(recd.factoryid, -1),
--factoryid,
v_wareqty,
--wareqty
recd.stallno,
--stallno,
recd.batid,
--batid,
recd.makeno,
--makeno,
recd.makedate,
--makedate,
recd.purprice,
recd.purtax,
recd.saletax,
--saletax,
0,
--saleprice,
recd.invalidate,
/*f_get_gain_whlprice(compid, v_vencusno, v_whlgroupid,
wareid, batid, purprice, v_subitemid),*/
recd.whlprice,
0,
--leastwhlprice,
0,
--maxwhlprice,
p_lastmodify,
--dtlsaler,
recd.midqty,
0,
--avgpurprice,
0,
--pstflag,
0,
--backprice,
NULL,
--notes,
0,
--distprice,
0,
--setpurprice1,
0,
--setpurprice2,
0,
--setwhlprice1,
0,
--setwhlprice2,
0,
--setwhlprice3,
0,
--setwhlprice4,
0,
--setwhlprice5,
NULL,
--chker1,
NULL,
--chker2,
0,
--batbackqty,
0,
--batbackamt,
0,
--redeemsum,
0,
--0, --prediswhlprice,
recd.maxqty,
recd.areacode,
NULL,
--multibusno,
0,
--invoiceredeemsum,
0,
--packnum
1,
recd.ownerid --ifselectbat
FROM dual;
UPDATE tmp_batapply_avail_store
SET awaitqty_makeno = v_wareqty
WHERE wareid = recd.wareid
AND batid = recd.batid
AND stallno = recd.stallno; --已分配数量存放tmp_batapply_avail_store.awaitqty_makeno
v_actualqty := v_actualqty + v_wareqty;
end loop;
END IF;
END IF;
END IF;
END LOOP;
end if;
SELECT COUNT(1)
INTO v_count
FROM t_batsale_d
WHERE batsaleno = v_batsaleno;
IF v_count = 0 THEN
DELETE FROM t_batsale_h WHERE batsaleno = v_batsaleno;
IF substr(out_batsaleno, 1, 2) != '错误' OR out_batsaleno IS NULL THEN
UPDATE t_batsaleapply_h SET indentflag = 1 WHERE applyno = p_applyno;
END IF;
v_notes := v_pare2976note || '批发申请单' || p_applyno || '转单失败';
ELSE
/*IF v_wms001 <> '1' THEN
delete from tmp_batsale_restrict;
for i in (SELECT a.compid,
a.busno,
b.makeno,
b.wareid,
decode(b.batid, 0, 'M' || b.makeno, b.batid) AS batid,
a.vencusno,
c.vencuscode,
a.ownerid,
a.saler,
a.lastmodify,
b.wareqty,
a.reckonerid,
a.cashtype,
b.rowno
FROM t_batsale_h a, t_batsale_d b, t_vencus c
WHERE a.batsaleno = b.batsaleno
AND a.compid = c.compid
AND nvl(b.pstflag, 0) = 0
AND a.vencusno = c.vencusno
AND a.batsaleno = v_batsaleno
AND EXISTS
(SELECT 1
FROM t_batsale_restrict_h aa,
t_batsale_restrict_d bb
WHERE aa.restrictno = bb.restrictno
AND aa.status = 1
AND bb.control_status = 1
AND ((nvl(bb.rest_flag, 0) = 0 AND
bb.wareid = b.wareid) OR
(nvl(bb.rest_flag, 0) = 1 AND EXISTS
(SELECT 1
FROM t_ware_class_base d
WHERE (d.compid = a.compid OR (f_get_sys_inicode(a.compid,
'3034',
NULL) = '0' AND
d.compid = 0))
AND d.classgroupno = bb.classgroupno
AND d.classcode = bb.classcode
AND d.wareid = b.wareid)))
AND aa.compid = a.compid
AND aa.startdate <= SYSDATE
AND aa.enddate >= SYSDATE)
ORDER BY b.wareid) loop
pro_restrict_qty(pi_compid => i.compid,
pi_busno => i.busno,
pi_wareid => i.wareid,
pi_batid => i.batid,
pi_vencusno => i.vencuscode,
pi_ownerid => i.ownerid,
pi_saler => i.saler,
pi_lastmodify => to_char(i.lastmodify),
pi_wareqty => i.wareqty,
pi_actualqty => 0,
po_wareqty => v_wareqty,
po_msg => v_msg,
pi_cashtype => i.cashtype,
pi_reckonerid => i.reckonerid);
if v_wareqty < i.wareqty then
if v_wareqty > 0 then
update t_batsale_d
set wareqty = v_wareqty
where batsaleno = v_batsaleno
and rowno = i.rowno;
else
delete from t_batsale_d
where batsaleno = v_batsaleno
and rowno = i.rowno;
end if;
if instr(v_notes1,v_msg) <= 0 then
v_notes1 := v_notes1 ||v_msg;
end if;
end if;
end loop;
end if;*/
UPDATE t_batsaleapply_h
SET batsaleno = v_batsaleno, indentflag = 1
WHERE applyno = p_applyno;
--因取批发销售价时，性能考虑，只能每行调用一次f_get_gain_whlprice, 故把批发销售价更新到折前销售价(prediswhlprice)
SELECT SUM(wareqty * whlprice)
INTO v_amt
FROM t_batsale_d
WHERE batsaleno = v_batsaleno;
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_batsale_d');
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_batsale_h');
UPDATE t_batsale_d bd
SET bd.prediswhlprice = bd.whlprice
WHERE bd.batsaleno = v_batsaleno;
UPDATE t_batsale_h
SET sum_whlprice = v_amt
WHERE batsaleno = v_batsaleno
AND compid = v_compid; --fyy19325更新单头单据金额，资信校验用
DELETE FROM tmp_disable_trigger WHERE table_name = 't_batsale_d';
DELETE FROM tmp_disable_trigger WHERE table_name = 't_batsale_h';
SELECT nvl(SUM(wareqty), 0) qty
INTO v_count
FROM t_batsale_d
WHERE batsaleno = v_batsaleno;
out_batsaleno := v_batsaleno;
v_notes := v_pare2976note || '批发申请单' || p_applyno ||
'转单，生成批发销售单：' || v_batsaleno || ',总数量' || v_count;
END IF;
/*更新实际分配数量*/
MERGE INTO tmp_batsale_act_alloc tba
USING (SELECT SUM(d.wareqty) total, d.wareid
FROM t_batsale_d d
WHERE d.batsaleno = v_batsaleno
GROUP BY d.wareid) t
ON (tba.wareid = t.wareid)
WHEN MATCHED THEN
UPDATE SET tba.actualqty = t.total;
v_para2790 := f_get_sys_inicode(p_compid => v_compid,
p_inicode => '2790',
p_userid => NULL);
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_supply');
---批发销售同一个客户、货主、部门、商品、在30天内重复请货就根据 2790 是否累加
--tmp_batsaleapply_gsp_del 记录的GSP排除掉的商品信息 此处不在生成补货申请
FOR rec_store1 IN (SELECT a.applyqty,
a.compid,
a.busno,
a.wareid,
nvl(b.sum_qty, 0) sum_qty,
tw.warecode
FROM tmp_batsale_act_alloc a
LEFT JOIN (SELECT st.compid,
st.wareid,
st.busno,
SUM(st.wareqty - st.awaitqty) AS sum_qty
FROM tmp_batapply_avail_store st
GROUP BY st.compid, st.wareid, st.busno) b
ON a.compid = b.compid
AND a.busno = b.busno
AND a.wareid = b.wareid
LEFT JOIN t_ware tw
ON a.compid = tw.compid
AND a.wareid = tw.wareid
WHERE a.applyqty > a.actualqty
AND NOT EXISTS
(SELECT 1
FROM tmp_batsaleapply_gsp_del del
WHERE del.applyno = p_applyno
AND del.vencusno = v_vencusno
AND del.wareid = a.wareid)) LOOP
v_applyqty := rec_store1.applyqty;
/*若可用数量大于申请数量，不需要生成补货*/
IF v_applyqty > rec_store1.sum_qty THEN
MERGE INTO t_supply ts
USING (SELECT temp.compid compid,
temp.wareid wareid,
temp.busno busno,
SYSDATE supplydate,
--supplydate,
temp.applyqty - rec_store1.sum_qty supplyqty,
--supplyqty,
nvl(sh.lastmqty, 0) salemonqty,
--salemonqty,
nvl(sh.sumqty, 0) busqty,
--busqty,
nvl(sh.sumqty, 0) sumqty,
--sumqty,
w.maxqty maxqty,
w.minqty minqty,
p_lastmodify createuser,
--createuser,
SYSDATE createtime,
p_applyno notes,
--notes,
0 stamp,
--stamp,
SYSDATE firstsupplydate,
--firstsupplydate,
'41' supplydpt,
--supplydpt, 这个有点小争议
2 supplytype,
--supplytype,
0 plantype,
--plantype,
NULL planno,
--planno,
0 disposetype,
--disposetype,
NULL disposedate,
--disposedate,
w.lastvencusno vendorno,
--vendorno,
p_lastmodify lastmodify,
--lastmodify,
SYSDATE lastdate,
--lastdate,
-- f_get_serial('SUP', rec.busno) SUPPLYNO,
b.ownerid ownerid,
b.reckonerid reckonerid,
b.vencusno custno,
b.vencusno vencusno,
p_applyno srccode,
--源单据号 srccode
'A' srctype,
--源单类型来源 srctype 批发申请转批发
CASE
WHEN v_parawms0001 = '1' AND length(TRIM(v_wms_ownerid)) > 0 THEN
'1'
ELSE
'0'
END wms_flag,
NULL batsaleno,
0 toflag,
0 objbusno,
b.whlgroupid,
b.saler,
b.subitemid
FROM tmp_batsale_act_alloc temp
JOIN t_ware w
ON temp.wareid = w.wareid
AND temp.compid = w.compid
LEFT JOIN t_store_h sh
ON sh.compid = temp.compid
AND sh.busno = temp.busno
AND sh.wareid = temp.wareid
JOIN t_batsaleapply_h b
ON b.applyno = p_applyno
WHERE temp.applyqty > temp.actualqty
AND temp.compid = rec_store1.compid
AND temp.busno = rec_store1.busno
AND temp.wareid = rec_store1.wareid) t
ON (ts.vencusno = t.vencusno AND ts.compid = t.compid AND ts.busno = t.busno AND ts.wareid = t.wareid AND SYSDATE - ts.createtime <= 30 AND ts.srctype = 'A' AND ts.reckonerid = t.reckonerid AND ts.ownerid = t.ownerid AND ts.disposetype = 0 AND ts.plantype = 0 AND ts.whlgroupid = t.whlgroupid AND ts.saler = t.saler AND ts.subitemid = t.subitemid)
WHEN MATCHED THEN
UPDATE
SET ts.supplyqty =
(CASE
WHEN v_para2790 = '0' THEN
t.supplyqty
ELSE
ts.supplyqty + t.supplyqty
END),
ts.lastmodify = p_lastmodify,
ts.lastdate = SYSDATE,
ts.srccode = p_applyno,
ts.notes = ts.notes || ',' || p_applyno
WHEN NOT MATCHED THEN
INSERT
(compid,
wareid,
busno,
supplydate,
supplyqty,
salemonqty,
busqty,
sumqty,
maxqty,
minqty,
createuser,
createtime,
notes,
stamp,
firstsupplydate,
supplydpt,
supplytype,
plantype,
planno,
disposetype,
disposedate,
vendorno,
lastmodify,
lastdate,
supplyno,
ownerid,
reckonerid,
custno,
vencusno,
srccode,
srctype,
wms_flag,
batsaleno,
toflag,
objbusno,
whlgroupid,
saler,
subitemid)
VALUES
(t.compid,
t.wareid,
t.busno,
t.supplydate,
t.supplyqty,
t.salemonqty,
t.busqty,
t.sumqty,
t.maxqty,
t.minqty,
t.createuser,
t.createtime,
t.notes,
t.stamp,
t.firstsupplydate,
t.supplydpt,
t.supplytype,
t.plantype,
t.planno,
t.disposetype,
t.disposedate,
t.vendorno,
t.lastmodify,
t.lastdate,
f_get_serial('SUP', t.busno),
t.ownerid,
t.reckonerid,
t.custno,
t.vencusno,
t.srccode,
t.srctype,
t.wms_flag,
t.batsaleno,
t.toflag,
t.objbusno,
t.whlgroupid,
t.saler,
t.subitemid);
DELETE FROM tmp_disable_trigger WHERE table_name = 't_supply';
END IF;
END LOOP;
SELECT listagg(supplyno, ',') within GROUP(ORDER BY supplyno)
INTO v_supplyno
FROM t_supply
WHERE srccode = p_applyno
AND compid = v_compid
and srctype = 'A';
SELECT a.data_length
INTO v_length
FROM user_tab_columns a
WHERE a.table_name = 'T_BATSALEAPPLY_H'
AND a.column_name = 'NOTES';
UPDATE t_batsaleapply_h
SET notes = substrb((notes || ' ' || v_notes || CASE
WHEN v_supplyno IS NULL THEN
''
ELSE
',生成缺货登记：' || v_supplyno
END || v_notes1 || CASE
WHEN v_makejs = 0 THEN
''
ELSE
',往来单位存在批号混批个数限制。'
END),
1,
v_length)
WHERE applyno = p_applyno;
MERGE INTO t_batsaleapply_d a
USING (SELECT srccode AS applyno, wareid, supplyno
FROM t_supply
WHERE compid = v_compid
AND srccode = p_applyno
and srctype = 'A') b
ON (a.applyno = b.applyno AND a.wareid = b.wareid AND a.applyno = p_applyno)
WHEN MATCHED THEN
UPDATE SET a.supplyno = b.supplyno; --把缺货登记单号回写批发申请单
--清除GSP过滤记录
DELETE FROM tmp_batsaleapply_gsp_del del WHERE del.applyno = p_applyno;
---更新t_batsale_d 的行号
merge into t_batsale_d a
using (select d.dj_sn,d.wareid,h.BATAPPLYNO, h.batsaleno from t_batsaleapply_d d left join t_batsale_h h on d.applyno = nvl(h.BATAPPLYNO,1) and d.applyno = p_applyno ) b
on ( a.wareid = b.wareid and a.batsaleno = b.batsaleno)
when matched then update
set a.back_var5 = b.dj_sn;
/*---更新主表单据编号和来源类型
update t_batsale_h h
set h.djbh =
(select djbh
from t_batsaleapply_h tba
where tba.applyno = h.batapplyno
and tba.applyno = p_applyno )
,h.billsource =
(select billsource
from t_batsaleapply_h tba
where tba.applyno = h.batapplyno
and tba.applyno = p_applyno)
where h.batapplyno = p_applyno;
*/
END;
批发销售单需要增加触发器,用来回传销售出库数据
CREATE OR REPLACE TRIGGER ctr_t_batsale_h_ysb
BEFORE INSERT OR UPDATE ON t_batsale_h
FOR EACH ROW
/*本触发器用于销售单保存时，更新药师帮主表提取字段 */
DECLARE
v_disable_trigger_ind PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_disable_trigger_ind
FROM tmp_disable_trigger
WHERE table_name = 't_batsale_h';
IF v_disable_trigger_ind > 0 THEN
RETURN;
END IF;
--生效时更新药师帮状态表状态
IF :new.compid = 1 AND :new.checkbit1 = 1 AND :old.checkbit1 = 0 and :new.djbh is not null THEN
INSERT INTO ysb_ddmx_writeback
(orderid,
ordercode,
dj_sn,
drugcode,
amount,
price,
je,
batchnum,
proddate,
validity,
status)
SELECT h.djbh,
h.batsaleno,
d.rowno,
f_get_warecode(d.wareid,h.compid),
d.wareqty,
d.whlprice,
ROUND(d.wareqty * d.whlprice, 4),
d.makeno,
TO_CHAR(d.makedate, 'yyyy-mm-dd'),
TO_CHAR(d.invalidate, 'yyyy-mm-dd'),
3
FROM t_batsale_d d
LEFT JOIN t_batsale_h h
ON h.batsaleno = d.batsaleno
WHERE h.batsaleno = :new.batsaleno;
END IF;
IF :new.compid = 1 AND :new.status = 1 AND :old.status = 0 and :new.djbh is not null THEN
MERGE INTO ysb_ddmx_writeback a
USING (SELECT r.orderid, r.dj_sn
FROM t_batsale_d d, ysb_ddmx_writeback r
WHERE r.orderid = :new.djbh
AND r.dj_sn = to_number(d.back_var5)
AND d.batsaleno = :new.batsaleno
GROUP BY r.orderid, r.dj_sn) t
ON (a.orderid = t.orderid AND a.dj_sn = t.dj_sn)
WHEN MATCHED THEN
UPDATE SET a.status = 2;
END IF;
-- 生效时将快递单号回写至快递单表
IF :new.compid = 1 AND :new.status = 1 AND :old.status = 0 and :new.djbh is not null THEN
for rec in (select * from table(f_split_str(:new.expressno, ','))) loop
insert into d_batsale_ext
(djbh,
batsaleno,
expressno,
express_code,
expresscompany,
btbbillcode)
values
(:new.djbh,
:new.batsaleno,
rec.str,
:new.express_code,
:new.expresscompany,
:new.sale_idbs);
end loop;
end if;
END ctr_t_batsale_h_ysb;
给药师帮提供如下数据
/*
登录信息:
数据库登录地址192.168.X.XX:1521
数据库实例:hydee
账号:ysbuser
密码:ysbpwd
*/
-- 1.库存(库存 栏)取两个字段：
select warecode as "drugCode",nvl(wareqty,0) as "stock" from H2.CV_YSB_WAREQTY;
--2.价格（价格 栏）取三个字段，编码跟单体价为必须值：
select rtrim(warecode) as "drugCode" ,
setwhlprice1 as "price", --（数字，不能传字符的值）
setwhlprice1 as "chainPrice" --（没有另外定价的，就与单体价格取值一致，一个商品只能上传一个单体价格）
from H2.CV_YSB_WARE_WHLPRICE;
--3.（多价格体系 栏）如果需要上传多个价格
select rtrim(a.warecode) as "drugCode",
nvl((case when a.purprice=0 then 999999 else a.purprice end),9999) as "price9"
from h2.cv_ysb_djpurprice_new a;
--4.药品效期（药品批号 栏）取四个字段：
SELECT warecode as "drugCode", ---商品编码
min(makeno) as "batchNum", ---批号
nvl(to_char(min(invalidate),'yyyy-mm-dd'),' ') as "validity", --有效期至
nvl(to_char(min(makedate),'yyyy-mm-dd'),' ') as "prodDate" --生产日期
from H2.CV_YSB_WARE_STORE_I
group by warecode;
--6.药店信息 现在只能同步药店编码用于订单对接
select ' ' as "controlType", --管控方式(取''就行)
'' as "code",
rtrim(vencuscode) as "number", --药店编码--这个值会同步为平台上录入的药店编码，选在界面上可视的那个或之前同步订单用的那个
rtrim(vencusname) as "storeTitle", --药店名称
'' as "busiScope",
'' as "certNo",
'' as "invalidDate",
'' as "address",
'' as "address"--地址
--nvl(to_char(''),'0') as "drugstoreBranchId"--系统有存放平台客户id的，才上传这个字段
from H2.CV_YSB_VENCUS
--and spjl='1'--审批合格
order by "number";
--5.药品信息 ！！！商品信息同步仅供一键上架功能使用，不更新现有上架品种信息！！！
select
drugCode as "drugCode", --药品编码(字符串）--同其他语句使用的商品编码字段 *
drugName as "drugName" , --商品名称(字符串） *
pack as "pack" , --商品规格(字符串） *
factory as "factory" , --生产厂家(字符串） *
unit as "unit" , --单位 中文药品单位，如盒、包、箱等(字符串） *
barcode as "barcode" , --商品条形码(字符串）（*最好有）
approval as "approval" , --批准文号，国药准字H20103180(字符串） *
busiType as "busiType" , --经营类别（*最好有）（字符串）
stock as "stock" , --库存（数字）*（取仓库的库存,用商品表left join库存表取值，保证所有品种可取到）
price as "price" , --价格（数字）*（取值与价格同步语句相同，不同步价格可定9999）
step as "step" , --购买增量、步长(数字类型）--即 采购倍数（默认填1）
taxRate as "taxRate" , --税率（数字）
midPack as "midPack" , --中包装数（数字）
wholePack as "wholePack", --整包装数（数字）
recommendedPrice as "recommendedPrice" --建议零售价（数字）
from h2.cv_ysb_ware;
--9.订单状态回传，工具8-8栏
select to_number(orderId) as "orderId", --取药师帮平台上的订单id
orderCode as "orderCode", --随货给客户的明细单上的单号，一般是出库单号或是销售单号
status as "status", --3-分捡中 2-待配送 (按实际状态填对应数字，如填2，该单据会在平台上跳转到待配送列表下，顺序是3-2)
drugCode as "drugCode", --平台上该药物的编码
amount as "amount", --实际出库数量
price as "price", --单价
batchNum as "batchNum", --批号
validity as "validity", --有效期
prodDate as "prodDate" --生产日期
from h2.ysb_ddmx_writeback where orderId in (VAL_orderIds)
order by "orderId","drugCode","batchNum"
--7.上传快递，工具9-2栏 “订单快递”
select
orderId as "orderId",
logisticsId as "logisticsId",
logisticsNo as "logisticsNo",
1 as "status",
invoiceStatus as "invoiceStatus"
from h2.cv_ysb_express;
--8.上传发票，工具9-3栏 “订单发票”
select
a.djbh as "orderId",
a.invoicetenno as "invoiceCode",
a.invoicelist as "invoiceNo",
'' as "securityCode",
a.pdf_url as "invoiceUrl"
from h2.cv_erp2pt_fp a
where billsource = 1
order by a.djbh