|
- wbv1231海典H2 进销存 医保对账 销售明细导出
----销售明细表2023
select distinct a.INSTITUTIONCODE as 门店编号,
a.INSTITUTIONNAME as 门店名称,
to_char(a.erpsaleno) as 流水号,
h.accdate as 开单日期,
h.finaltime as 开单时间,
f_get_username(d.saler) as 营业员,
f_get_username(h.payee) as 收银员,
b.warecode as 货号,
b.CENTERITEMCODE as 药品国家医保编码,
b.warename as 商品名及通用名,
b.specs as 规格,
b.MANUFACTURER as 生产单位,
b.CHARGEUNIT as 单位,
d.wareqty as 数量,
round(d.wareqty*d.netprice,2) as 金额,
d.makeno as 批号,
d.invalidate as 效期,
i.makedate as 生产日期,
d.netprice as 零售价,
f_get_classname('03',d.wareid,h.compid) as 剂型
from t_yby_order_h a
left join t_yby_order_d b on a.orderno=b.orderno
left join t_sale_h h on a.erpsaleno=h.saleno and h.compid=2
left join t_ware t on b.warecode=t.warecode and t.compid=2
left join t_sale_d d on h.saleno=d.saleno and a.erpsaleno=d.saleno
and t.wareid=d.wareid
left join t_store_i i on d.wareid=i.wareid and d.batid=i.batid
and h.compid=i.compid
where a.INSTITUTIONCODE='P62222260' and
to_char( h.accdate,'yyyy-mm-dd')>='2023-09-01'
and to_char( h.accdate,'yyyy-mm-dd')<='2024-12-31'
order by h.accdate,to_char(a.erpsaleno);
-----进销存
select 'P6222222' as 医保机构编号,
'2222222际店' as 门店名称,
to_char( w.warecode ) as 货号,
max(w.warename) as 商品名及通用名,
max(w.warespec) as 商品规格,
max(w.wareunit) as 单位,
to_char( max(w.INSURANCENO) ) as 药品国家医保编码,
nvl(max(qc.qckc),0) as 期初库存,
sum(r.inqty) as 入库数量,
sum(r.outqty) as 出库数量,
nvl(max(qm.qmkc),0) as 期末库存
from t_item_in_out_remain r
left join t_ware w on r.wareid=w.wareid and r.compid=w.compid
left join (select wareid,sum(wareqty) as qckc from t_store_d_bak
where busno=297 and period='202309'
group by wareid) qc
on r.wareid=qc.wareid
left join (select wareid,sum(wareqty) as qmkc from t_store_d_bak
where busno=297 and period='202412'
group by wareid) qm
on r.wareid=qm.wareid
where r.busno='297' and w.isinsurance=1 and
to_char( r.execdate,'yyyy-mm-dd')>='2023-01-01'
and to_char(r.execdate,'yyyy-mm-dd')<='2024-12-31'
and r.compid=2
and r.billcode not in ('MDACC')
group by w.warecode;
-----医保对账表
select c.PsnName as 人员姓名,
to_char(ord.OutSaleNo) as 身份证,
to_char(ord.OutSaleNo) as 系统流水号,
to_char(c.psnno) as 人员信息编号,
to_char(c.SETTELID) as 结算ID,
to_char(c.MDTRTID) as 就医ID,
ord.settletime as 结算时间,
'P22222222' as 医保机构编号,
to_char(ord.AREACODE) as 区医保编号,
case when c.clrtype=41 then '药店购药'
when c.clrtype=11 then '普通门诊'
when c.clrtype='99972' then '省内异地药店购药'
when c.clrtype='9903' then '跨省异地药店购药'
when c.clrtype='99970' then '省内异地门诊'
when c.clrtype='9901' then '跨省异地门诊'
else '' end as 清算类别,
c.totalamount as 总金额,
c.CASHPAY as 现金支付,
c.HIFPPAY as 统筹基金支付,
c.ACCOUNTPAY as 个人账户支付,
c.HIFOBPAY as 大病保险基金,
c.FUNDPAY as 基本医疗保险基金,
c.MAFPAY as 医疗救助基金,
c.BALANCE as 账户余额,
case when ord.orderstatus=5 then '退费成功'
when ord.orderstatus=6 then '已退费'
else '' end 退费标志,
'社保卡' as 卡类别,
case when c.insutype='310' then '职工基本医疗保险' when c.insutype='390' then '城乡居民基本医疗保险' else '' end as 险种类型 ,
m.label 人员类型
from YBCloud.med_ORDER ord
left join YBCloud.MED_ORDER_REC_LIST c on ord.OrderNo=c.OrderNo
left join YBCLOUD.CODE_DIC m on m.TYPE ='PSN_TYPE' AND m.VALUE =c.PSNTYPE
where ord.OrderStatus in (2,3,5,6)
and ord.IsDeleted=0
and to_char( ord.settletime,'yyyy-mm-dd')>='2023-01-01'
and to_char(ord.settletime,'yyyy-mm-dd')<='2024-12-31'
and ord.ORDERMEDICALNO='P222222222'
order by ord.OrderNo;
- wbv1232海典H2 导出数据(入库明细、配送明细、退仓明细)
---111表一:采购入库表_公司总库
---表一:采购入库表_公司总库
select
f_get_busnoname(a.busno) 单位名称,
b.wareid 商品ID,
w.warename 商品名称,
b.makeno 批号,
'否' AS 是否集采商品,
to_char(a.execdate, 'yyyy-mm-dd') 入库时间,
b.wareqty 入库数量,
b.purprice 入库单价,
b.wareqty*b.purprice 入库金额,
w.warespec 规格,
to_char(b.invalidate, 'yyyy-mm-dd') as 有效期至,
tvb.vencusname as 供应商
from t_accept_h a
left join t_vencus_base tvb on tvb.vencusno = a.vencusno
, t_accept_d b left join t_ware w on b.wareid = w.wareid
where a.acceptno=b.acceptno and a.billcode='ACC' and a.status=1
and to_char(a.execdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.execdate,'yyyy-mm-dd')<='2024-12-31';
----222表二:门店退库入库表_公司总库
select
f_get_busnoname(a.objbusno) 单位名称,
b.wareid 商品ID,
w.warename 商品名称,
b.makeno 批号,
'否' AS 是否集采商品,
to_char(a.execdate, 'yyyy-mm-dd') 入库时间,
b.wareqty 入库数量,
b.distprice 入库单价,
b.wareqty*b.distprice 入库金额,
w.warespec 规格,
to_char(b.invalidate, 'yyyy-mm-dd') 有效期至,
a.srcbusno 退库门店编号,
f_get_busnoname(a.srcbusno) 退库门店名称,
'是' 退库门店是否定点机构,
g.MEDICALNO 退库门店定点机构编号
from t_dist_h a left join YBCloud.med_store g on a.srcbusno=g.busno ,t_dist_d b
left join t_ware w on b.wareid = w.wareid
where a.distno=b.distno and a.billcode in ('DIR','ADR') and a.status=1
and to_char(a.execdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.execdate,'yyyy-mm-dd')<='2024-12-31';
----333333表三:派送出库表_公司总库
select
f_get_busnoname(a.srcbusno) 单位名称,
b.wareid 商品ID,
w.warename 商品名称,
b.makeno 批号,
'否' AS 是否集采商品,
to_char(a.execdate, 'yyyy-mm-dd') 派送时间,
b.wareqty 派送数量,
b.distprice 派送单价,
b.wareqty * b.distprice 派送金额,
w.warespec 规格,
to_char(b.invalidate, 'yyyy-mm-dd') 有效期至,
a.objbusno 派送门店编号,
f_get_busnoname(a.objbusno) 派送门店名称,
'是' 是否定点机构,
g.MEDICALNO 定点机构编号
from t_dist_h a left join YBCloud.med_store g on a.objbusno=g.busno ,t_dist_d b
left join t_ware w on b.wareid = w.wareid
where a.distno=b.distno and a.billcode in ('DIS','ADD') and a.status=1
and to_char(a.execdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.execdate,'yyyy-mm-dd')<='2024-12-31';
---444表四:退货出库表_公司总库
select
f_get_busnoname(a.busno) 单位名称,
b.wareid 商品ID,
w.warename 商品名称,
b.makeno 批号,
'否' AS 是否集采商品,
to_char(a.execdate, 'yyyy-mm-dd') 退库时间,
b.returnqty 退库数量,
b.purprice 退库单价,
b.returnqty*b.purprice 退库金额,
tvb.vencusname as 供应商
from t_reaccept_h a
left join t_vencus_base tvb on tvb.vencusno = a.vencusno
, t_reaccept_d b left join t_ware w on b.wareid = w.wareid
where a.reacceptno=b.reacceptno and a.status=1
and to_char(a.execdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.execdate,'yyyy-mm-dd')<='2024-12-31';
---5555表五:入库表_门店药库
select
f_get_busnoname(a.srcbusno) 单位名称,
a.objbusno 门店编号,
f_get_busnoname(a.objbusno) 门店名称,
'是' 是否定点机构,
g.MEDICALNO 定点机构编号,
b.wareid 商品ID,
w.warename 商品名称,
b.makeno 批号,
'否' AS 是否集采商品,
to_char(a.execdate, 'yyyy-mm-dd')派送时间,
b.wareqty 派送数量,
b.distprice 派送单价,
b.wareqty * b.distprice 派送金额,
w.warespec 规格,
to_char(b.invalidate, 'yyyy-mm-dd') 有效期至
from t_dist_h a left join YBCloud.med_store g on a.objbusno=g.busno,t_dist_d b
left join t_ware w on b.wareid = w.wareid
where a.distno=b.distno and a.billcode in ('DIS','ADD') and a.status=1
and to_char(a.execdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.execdate,'yyyy-mm-dd')<='2024-12-31';
---6666表六:销售出库表_门店药库
select
'定西众济堂医药连锁有限责任公司' 单位名称,
a.busno 门店编号,
f_get_busnoname(a.busno) 门店名称,
'是' 是否定点机构,
g.MEDICALNO 定点机构编号,
c.wareid 商品ID,
w.warename 商品名称,
c.makeno 批号,
'否' AS 是否集采商品,
to_char(a.accdate,'yyyy-mm-dd') 销售时间,
round((c.wareqty + c.minqty / stdtomin) * times ,2) 销售数量,
c.netprice 销售单价,
round((c.wareqty + c.minqty ) * times * c.netprice,2) 销售金额,
w.warespec 规格,
to_char(c.invalidate, 'yyyy-mm-dd') 有效期至
from t_sale_h a left join T_YBY_ORDER_H h on a.saleno=h.erpsaleno
left join YBCloud.med_store g on a.busno=g.busno,t_sale_d c,t_ware w
where w.compid=a.compid and a.saleno=c.saleno and c.wareid=w.wareid and round((c.wareqty + c.minqty / stdtomin) * times ,2) >0
AND to_char(a.accdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.accdate,'yyyy-mm-dd')<='2024-12-31';
---666表六:门店退库表_门店药库
select '定西众济堂医药连锁有限责任公司' 单位名称,
a.busno 门店编号,
f_get_busnoname(a.busno) 门店名称,
'是' 是否定点机构,
g.MEDICALNO 定点机构编号,
c.wareid 商品ID,
w.warename 商品名称,
c.makeno 批号,
'否' AS 是否集采商品,
to_char(a.accdate,'yyyy-mm-dd') 退库时间,
round((c.wareqty + c.minqty / stdtomin) * times ,2) 退库数量,
c.netprice 退库单价,
round((c.wareqty + c.minqty ) * times * c.netprice,2) 退库金额,
w.warespec 规格,
to_char(c.invalidate, 'yyyy-mm-dd') 有效期至
from t_sale_h a left join T_YBY_ORDER_H h on a.saleno=h.erpsaleno
left join YBCloud.med_store g on a.busno=g.busno ,t_sale_d c,t_ware w
where w.compid=a.compid and a.saleno=c.saleno and c.wareid=w.wareid and round((c.wareqty + c.minqty / stdtomin) * times ,2) <0
AND to_char(a.accdate,'yyyy-mm-dd')>='2021-01-01'
AND to_char(a.accdate,'yyyy-mm-dd')<='2024-12-31';
----88 表八:期末库存表_公司总库_2024年末
select
'定西众济堂医药连锁有限责任公司' 单位名称,
a.wareid 商品ID,
max(w.warename) 商品名称,
max(i.makeno )批号,
'否' AS 是否集采商品,
sum(a.wareqty) 数量,
max(nvl(ad.purpriceo,i.purprice))单价,
sum(round(a.wareqty * nvl(ad.purpriceo,i.purprice),2)) 金额,
max(w.warespec) 规格,
max(to_char(i.invalidate, 'yyyy-mm-dd')) 有效期至,
max(tvb.vencusname) as 供应商
FROM (SELECT compid AS compid,
busno AS busno,
wareid AS wareid,
stallno AS stallno,
batid AS batid,
SUM(wareqty) AS wareqty
FROM (SELECT compid, busno, wareid, stallno, batid, wareqty
FROM t_store_d
WHERE compid = 2
UNION ALL
SELECT l.compid,
l.busno,
l.wareid,
l.stallno,
l.batid,
SUM(-l.inqty) AS wareqty
FROM t_store_inout_list l
WHERE l.execdate > to_date( '202412312359','yyyy-mm-dd hh24:mi' ) and l.compid = 2
GROUP BY l.compid, l.busno, l.wareid, l.batid, l.stallno)
GROUP BY compid, busno, wareid, stallno, batid) a
JOIN t_store_i i
ON a.wareid = i.wareid
AND a.batid = i.batid
left join t_vencus_base tvb on tvb.vencusno = i.vencusno
JOIN t_ware w
ON a.compid = w.compid
AND a.wareid = w.wareid
JOIN t_ware_ext wb
ON wb.wareid = a.wareid
LEFT JOIN t_factory f
ON f.factoryid = w.factoryid
LEFT JOIN t_area
ON t_area.areacode = i.areacode
left join (select h1.compid,d1.wareid,d1.batid,d1.purpriceo,h1.execdate
from t_adjust_purprice_d d1,
t_adjust_purprice_h h1
where d1.adjustno = h1.adjustno
and h1.compid = 2
and h1.status = 1
and (d1.adjustype = 2 or d1.adjustype = 3)
and h1.execdate > to_date( '202412312359','yyyy-mm-dd hh24:mi' )
and not exists(
select 1 from t_adjust_purprice_d d2,t_adjust_purprice_h h2
where d2.adjustno = h2.adjustno and h2.compid = 2
and h2.status = 1
and (d2.adjustype = 2 or d2.adjustype = 3)
and h2.execdate > to_date( '202412312359','yyyy-mm-dd hh24:mi' )
and h2.compid = h1.compid and d2.wareid = d1.wareid
and d2.batid = d1.batid and h2.execdate < h1.execdate)
order by execdate asc
) ad
on
ad.compid = a.compid
and ad.wareid = a.wareid
and ad.batid = a.batid
WHERE i.createtime <= to_date( '202412312359','yyyy-mm-dd hh24:mi' ) and i.compid = 2 AND w.compid=2 and nvl(w.insuranceno,'1')<>'1'
group by a.compid,a.busno,a.wareid ;
- wbv1233电商解锁货位调整单
无
- wbv1234无尽冬日记录贴
四项数值打分 m
进攻:4.5分 前三代唯一进攻矛,打熊用到死,简直是游戏最最最bug的英雄了
防守2分:前三代两个防守矛,尴尬。
探险/竞技场:2.5分
获取途径:5分,六期抽奖只要不滥用钻石基本都能抽满
综合得分:3.5分 及格 一个便宜好用的进攻英雄和打熊专业户
终极评价:米娅在三代里是最好用的英雄,无论是车身还是车头都必须拉满她,记得要把她的专武拉到6以上,这样打熊会很容易。【图片】
- wbv1235库房海诺的商品清楚追溯码
在第一个 无数字的数据库里
- wbv1236甘肃医保测试及正式环境地址
可以用身份证读卡查看余额等确确定测试环境是否合适, 可以取消密码验证通过身份证刷卡。
- wbv1237自己记录
打开 自启动文件夹 shell:startup
- wbv1238通过商品分类要求门店重新扫库存的追溯码
门店扫完单子后不要审核,通知以后在数据库执行生效 ---生效单子
INSERT INTO tmp_disable_trigger (table_name) VALUES ('t_md_notice_accept_h');
update t_md_notice_accept_h set status=1,execdate= sysdate where acceptno= '250313030700077';
DELETE FROM tmp_disable_trigger WHERE table_name = 't_md_notice_accept_h';
- wbv1239海典新医保云商品进行撤销及删除操作
批量操作: ('100591','100591','100598');
- wbv12310医保云异地扣费进行撤销
有部分预结算需要咨询医保局相关信息才能够撤销成功。
|
|