追溯码
采用国家医保局药品追溯码信息采集方案中的模式一开展工作，在发药环节采集药品追溯信息。在发药后，异步定时调用两定接口中进销存管理的商品销售、商品销售退货等接口，实现采集药品追溯信息至国家医保信息平台。
| 序号 | 功能：模式一 |
|---|---|
| 1 | 【ZSMCJ003】四码查询接口 |
| 系统通过调用【ZSMCJ003】四码查询接口下载四码合一基础库信息至本地。该接口为辅助接口，如医疗机构有途径明确药品追溯码所对应的医保目录编码，可不调用该接口。 | |
| 2 | 【3505】或【3505A】商品销售 |
| 工作人员确认发药后，调用【3505】或【3505A】商品销售接口，实现追溯码信息上传。 | |
| 3 | 【3506】或【3506A】销售退货 |
| 如出现病人退药的情况，调用接口上传数据。 |
接口
需要处理的接口：
- 【ZSMCJ003】四码查询接口
- 【3505】商品销售
- 【3506】销售退货
- 【3513】定点医药机构商品销售追溯信息查询
功能
包含门诊/住院
- 下载基础四码查询做为基础库
- 后台程序定时上传销售/退货相关数据
- 定时整合已匹配的追溯码和药品关系，完善基础库，方便HIS扫描追溯码时能够相对准确匹配药品方便药房人员校验待发药药品。
住院流程
工作量
- 基础库下载 （0.5人日）
ZSMCJ003四码查询接口
- 药品销售/销售退货数据上传 （2人日）
药品销售数据同步 1人日 销售退货数据同步 1人日
- 商品销售追溯信息查询 （0.5人日）
整合已匹配追溯码和药品对应关系完善基础库
- 程序基础框架搭建 （1.5人日）
后台任务处理 0.5人日 接口调用框架搭建 1人日
追溯码基础库同步
select distinct to_char(substr(a.scancode_chr, 0, 7)) tracemedcode,
getybcode(b.itemid_chr),
c.medicinename_vchr,
'' a,
'' b,
'1' c,
b.itemid_chr
from t_public_scancode a, t_bse_chargeitem b, t_bse_medicine c
where a.chargeitemid_chr = b.itemid_chr
and b.itemsrcid_vchr = c.medicineid_chr
and a.pstaus = 1
and length2(a.scancode_chr) = 20
and not exists
(select 1
from t_ins_tracecode_info info
where info.tracemedcode = to_char(substr(a.scancode_chr, 0, 7)));
将查询出的数据，同步表 `t_ins_tracecode_info` 中
扫码情况查询
-- 门诊
select distinct d.outpatrecipeid_chr 处方号,
card.patientcardid_chr 卡号,
pa.lastname_vchr 患者姓名,
f_getempnamebyid(b.diagdr_chr) 开单科室,
f_getdeptnamebyid(b.diagdept_chr) 执行科室,
d.medicineid_chr 药品id,
d.medicinename_vchr 药品名称,
b.recipeorgdate_dat 开单时间,
payt.paytypename_vchr 患者类型,
case
when (select count(1)
from t_public_scancode sc
where d.outpatrecipeid_chr = sc.registeid_chr
and sc.pstaus = 1) > 0 then
'部分未扫'
else
'全部未扫'
end 扫码情况
from t_ds_recipeaccount_detail d
join t_opr_outpatientrecipe b on d.Outpatrecipeid_Chr = b.Outpatrecipeid_Chr and b.pstauts_int = 2
left join t_bse_patient pa on pa.patientid_chr = b.patientid_chr
left join t_bse_patientcard card on b.patientid_chr = card.patientid_chr
left join t_bse_chargeitem it on d.medicineid_chr = it.itemsrcid_vchr
left join t_bse_patientpaytype payt on b.paytypeid_chr = payt.paytypeid_chr
where d.state_int <> 0
and d.type_int = 2
and d.drugstoreid_int in ('0000065','0000510')
and not exists (select 1 from t_public_scancode sc where d.OUTPATRECIPEID_CHR = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = it.ITEMID_CHR)
and b.recipeorgdate_dat > to_date('2025-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
order by d.outpatrecipeid_chr,b.recipeorgdate_dat
-- 住院
select e.lastname_vchr 患者姓名,
d.inpatientid_chr 住院号,
a.registerid_chr 住院流水号,
a.medid_chr 药品id,
a.medname_vchr 药品名称,
a.create_dat 时间,
z.creator_chr 开单医生,
f_getdeptnamebyid(d.deptid_chr) 所在科室,
z.createdate_dat 开单时间,
payt.paytypename_vchr 患者类型,
case when (select count(1) from t_public_scancode sc where a.registerid_chr = sc.registeid_chr and sc.pstaus = 1 ) > 0 then '部分未扫'
else '全部未扫'
end 扫码
from t_bih_opr_putmeddetail a
left join t_bih_opr_putmedreq b on a.putmedreqid_chr = b.putmedreqid_chr and b.status_int <> 0
left join t_opr_bih_register d on a.registerid_chr = d.registerid_chr
left join t_opr_bih_registerdetail e on d.registerid_chr = e.registerid_chr
left join t_ds_storage r on a.medid_chr = r.medicineid_chr
left join t_opr_bih_order z on a.orderid_chr = z.orderid_chr
left join t_bse_medicine med on a.medid_chr = med.medicineid_chr
left join t_bse_employee e1 on a.doctorid_chr = e1.empid_chr
left join t_bse_employee e2 on b.creator_chr = e2.empid_chr
left join t_ins_gzybapichargeinfo yb on a.registerid_chr = yb.registerid_chr
left join t_bse_patientpaytype payt on d.paytypeid_chr = payt.paytypeid_chr
where a.putmedtype_int = 1
and a.isput_int = 1
and a.status_int = 1
and a.orderexectype_int = 4
and a.medstoreid_chr = '0000066'
and r.drugstoreid_chr = '0000066'
and a.create_dat > to_date('2025-07-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and not exists (select 1 from t_public_scancode sc where a.registerid_chr = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = a.chargeitemid_chr)
order by z.createdate_dat,d.inpatientid_chr
with tt as (
select a.mdtrt_id
from temp_tracecode a, t_opr_outpatientrecipe b
where a.mdtrt_id = b.MEDINSURANCEID_CHR
and b.createdate_dat > timestamp'2025-06-01 00:00:00')
select * from temp_tracecode ss
where not exists (select * from tt where tt.mdtrt_id = ss.mdtrt_id) and ss.med_type = '门诊'
with recipes as (select distinct a.mdtrt_id,b.OUTPATRECIPEID_CHR,b.MEDINSURANCEID_CHR,a.YB_ITEM_CODE
from temp_tracecode a, t_opr_outpatientrecipe b ,t_bse_patient c,t_opr_outpatientpwmrecipede d
where a.id_card = c.idcard_chr and b.patientid_chr = c.patientid_chr and b.OUTPATRECIPEID_CHR = d.OUTPATRECIPEID_CHR and getybcode(d.itemid_chr) = a.YB_ITEM_CODE
and to_char(to_date(a.selt_date,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd') = to_char(b.createdate_dat,'yyyy-mm-dd')
and b.createdate_dat > timestamp'2025-06-01 00:00:00')
select distinct d.Seriesid_Int prod_sal_id,
getybcode(it.itemid_chr) med_list_codg,
m.medicinecode_vchr fixmedins_hilist_id,
d.medicinename_vchr fixmedins_hilist_name,
d.Lotno_Vchr fixmedins_bchno,
'01' prsc_dr_cert_type,
e1.Empidcard_Chr prsc_dr_certno,
e1.lastname_vchr prsc_dr_name,
'01' phar_cert_type,
e2.empidcard_chr phar_certno,
e2.lastname_vchr phar_name,
nvl(null, '*') phar_prac_cert_no,
null hi_feesetl_type,
yb.setl_id setl_id,
nvl(nvl(yb.mdtrt_id,b.MEDINSURANCEID_CHR),d.seriesid_int) mdtrt_sn,
yb.psn_no psn_no,
nvl(yb.psn_cert_type,'01') psn_cert_type,
yb.certno certno,
p.lastname_vchr psn_name,
d.Lotno_Vchr manu_lotnum,
to_char(t.instoragedate_dat, 'yyyy-mm-dd') manu_date,
to_char(d.Validperiod_Dat, 'yyyy-mm-dd') expy_end,
'1' rx_flag,
nvl(null, '1') trdn_flag,
round(d.Ipretailprice_Int, 4) finl_trns_pric,
d.Outpatrecipeid_Chr rxno,
nvl(null, '*') rx_circ_flag,
nvl(vv.invoiceno_vchr, '*') rtal_docno,
r.sid_int stoout_no,
d.lotno_vchr bchno,
sc.scancode_chr drug_trac_codg,
null barcode,
t.storagerackid_chr shelf_posi,
d.Ipamount_Int sel_retn_cnt,
to_char(d.operatedate_dat, 'yyyy-mm-dd hh24:mi:ss') sel_retn_time,
nvl(e3.lastname_vchr, '*') sel_retn_opter_name,
decode(nvl(yb.setl_id, '*'), '*', 2, 1) MDTRT_SETL_TYPE,
r.Remark_Vchr memo
from t_ds_recipeaccount_detail d
left join t_opr_outpatientrecipe b on d.Outpatrecipeid_Chr = b.Outpatrecipeid_Chr
left join t_opr_outpatientrecipeinv vv on vv.outpatrecipeid_chr = d.outpatrecipeid_chr
left join t_opr_recipesendentry rr on rr.outpatrecipeid_chr = b.outpatrecipeid_chr
left join t_opr_recipesend r on r.sid_int = rr.sid_int
left join t_bse_employee e1 on e1.empid_chr = b.Diagdr_Chr
left join t_bse_employee e2 on e2.empid_chr = r.Treatemp_Chr
left join t_bse_employee e3 on e3.empid_chr = r.Sendemp_Chr
left join t_bse_patient p on p.patientid_chr = b.patientid_chr
left join t_bse_medicine m on m.medicineid_chr = d.medicineid_chr
left join t_bse_chargeitem it on m.medicineid_chr = it.itemsrcid_vchr
left join t_ds_storage_detail t on t.medicineid_chr = d.medicineid_chr and d.drugstoreid_int = t.drugstoreid_chr and d.lotno_vchr = t.lotno_vchr
left join t_ins_gzybapichargeinfo yb on yb.registerid_chr = d.outpatrecipeid_chr and yb.pstatus_int = 1
join t_public_scancode sc on d.outpatrecipeid_chr = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = it.itemid_chr
join recipes on d.Outpatrecipeid_Chr = recipes.Outpatrecipeid_Chr and getybcode(it.itemid_chr) = recipes.YB_ITEM_CODE
where d.state_int <> 0
and d.type_int = 2
-- and not exists (select 1 from t_ins_upload_log log where trim(d.seriesid_int) = log.key and log.type = '3505' and log.mzzy= 1 and log.status = 1)
补传追溯码
-- 门诊
with recipes as (select distinct a.recipe_id,a.YB_ITEM_CODE,a.mdtrt_id,a.selt_id,b.gsqh,b.cbqh
from temp_tracecode a,temp_tracecode_b b where a.mdtrt_id = b.mdtrt_id and a.selt_id = b.selt_id)
select distinct 'H44010300351' 定点医药机构编号,
'广州医科大学附属第三医院' 定点医药机构名称,
recipes.mdtrt_id 就诊ID,
recipes.selt_id 结算ID,
recipes.gsqh 定点归属医保区划,
recipes.cbqh 参保人参保区划,
getybcode(it.itemid_chr) 医疗目录编码,
it.itemname_vchr 医疗目录名称,
sc.scancode_chr 药品追溯码,
'0' as "是否拆零(0-否；1-是)",
'11' as "业务类别"
-- (select count(1) from t_public_scancode sc where d.outpatrecipeid_chr = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = it.itemid_chr)
from t_ds_recipeaccount_detail d
left join t_opr_outpatientrecipe b on d.Outpatrecipeid_Chr = b.Outpatrecipeid_Chr
left join t_opr_outpatientrecipeinv vv on vv.outpatrecipeid_chr = d.outpatrecipeid_chr
left join t_opr_recipesendentry rr on rr.outpatrecipeid_chr = b.outpatrecipeid_chr
left join t_opr_recipesend r on r.sid_int = rr.sid_int
left join t_bse_employee e1 on e1.empid_chr = b.Diagdr_Chr
left join t_bse_employee e2 on e2.empid_chr = r.Treatemp_Chr
left join t_bse_employee e3 on e3.empid_chr = r.Sendemp_Chr
left join t_bse_patient p on p.patientid_chr = b.patientid_chr
left join t_bse_medicine m on m.medicineid_chr = d.medicineid_chr
left join t_bse_chargeitem it on m.medicineid_chr = it.itemsrcid_vchr
left join t_ds_storage_detail t on t.medicineid_chr = d.medicineid_chr and d.drugstoreid_int = t.drugstoreid_chr and d.lotno_vchr = t.lotno_vchr
left join t_ins_gzybapichargeinfo yb on yb.registerid_chr = d.outpatrecipeid_chr and yb.pstatus_int = 1
join recipes on d.Outpatrecipeid_Chr = recipes.recipe_id and getybcode(it.itemid_chr) = recipes.YB_ITEM_CODE
join t_public_scancode sc on d.outpatrecipeid_chr = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = it.itemid_chr
where d.state_int <> 0
and d.type_int = 2
and exists(select 1 from t_public_scancode sc where d.outpatrecipeid_chr = sc.registeid_chr and sc.pstaus = 1 and sc.chargeitemid_chr = it.itemid_chr)