PROCEDURE SP_YK_YBZSRZ_EDIT ( POPFLAG in number , --操作标志 0：入库确认,退库确认 1：出库确认 2:撤消出库(传参为正单号，生成的日志为负单单号) PRKQLDH in varchar2 , --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH PCZY000 in number , --操作员 PCOMMIT varchar2 default 'N' , --是否提交 Pzxcgbz out number , --执行成功标志 1成功 0失败 Ptsxx00 out varchar2 , --执行结果提示信息，成功的情况下也可能有警告信息 Pxxxx00 out varchar2 --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标 ) -- modification history -- Person Date Comments -- linshu 2024.06.14 create:药库医保追溯码日志明细操作; for YK9-20240618-001 -- linshu 2024.06.20 通用字典去空取参数值; for YK9-20240620-001 -- linshu 2024.08.05 增加撤消出库生成医保追溯码日志操作; for YK9-20240724-001 -- linshu 2024.08.15 医保追溯码日志还没上传扫码补录的数据改为追加; for YK9-20240815-001 -- linshu 2024.09.09 增加了药库出库到药房生成的医保追溯码日志功能; for YK9-20240909-001 -- linshu 2024.09.13 退库确认：1.生成药房调拔出库医保追溯码上传数据 2.生成药库调拔入库医保追溯码上传数据; for YK9-20240913-002 -- --------- ---------- ------------------------------------------- as vCount number(5); --计数器 Ecustom exception; --错误变量 V_ZSID00 YK_YBZSRZ.ZSID00%type; V_RKLXBH YK_YPRKD0.RKLXBH%type; V_CRLXBH YK_YBZSRZ.CRLXBH%type; V_ZSMQY0 BM_TYZD00.MC0000%type; V_CKLXBH YK_YPQLD0.CKLXBH%type; V_YPQLDH YK_YPQLD0.YPQLDH%type; V_RKZT00 YK_YPRKD0.RKZT00%type; V_RKLXMC BM_YKRKLX.RKLXMC%type; V_CKLXMC BM_YKCKLX.CKLXMC%type; V_BMBH00 YK_YPQLD0.YKBMBH%type; V_QXBMBH YK_YPQLD0.CKQXBH%type; V_LYBMBH YK_YPQLD0.YKBMBH%type; V_YWXZ00 varchar2(1); V_YFRKDH YF_YPRKD0.RKDH00%type; V_YFBMBH YF_YPRKD0.YFBMBH%type; V_RCLXMC BM_YFRKLX.RKLXMC%type; --R_YF_YPRKD0 YF_YPRKD0%rowtype; V_YFZSID YK_YBZSRZ.ZSID00%type; --药房使用变量 V_YFCKD0 YF_YPCKD0.CKDH00%type; V_YFRCLX varchar2(2); V_YFRCMC varchar2(10); begin Pzxcgbz := 0; begin select nvl(trim(MC0000),'0') into V_ZSMQY0 from BM_TYZD00 where ZDMC00='医保追溯码配置' and BH0000='YBZSMSFQY' and YXBZ00='1'; exception when others then V_ZSMQY0 := '0'; end; if V_ZSMQY0 = '0' then return; end if ; if POPFLAG = '0' then select A.RKLXBH,B.RKLXMC,A.RKZT00,A.YKBMBH into V_RKLXBH,V_RKLXMC,V_RKZT00,V_BMBH00 from YK_YPRKD0 A,BM_YKRKLX B where A.RKLXBH = B.RKLXBH and A.RKDH00 = PRKQLDH; if not V_RKLXBH in ('01','02','05','06') then return; end if; if not V_RKZT00 in ('1','2','3') then return; end if; if V_RKLXBH in ('01','02','05') then select count(1) into vCount from YK_RKZSM0 A where exists ( select 1 from YK_YPRKMX B where B.RKDH00 = PRKQLDH and B.YPRKPC = A.YKRKPC ); if vCount = 0 then return; end if; end if; --101:调拔入库;102:调拔出库;103:盘盈;104:盘损;105:销毁;106:其它入库;107:其它出库; --108:商品采购入库;109:商品退货出库;110:赠药入库;111:赠药退回出库 if V_RKLXBH in( '01','02') then --采购入库 V_CRLXBH := '108'; elsif V_RKLXBH = '05' then --赠送入库 V_CRLXBH := '110'; elsif V_RKLXBH = '06' then --药房退库入库 V_CRLXBH := '101'; end if; --药房调拔出库 BEGIN --select substr(A.BZ0000,instr(A.BZ0000,'药房出库单:')+6,length(A.BZ0000) - instr(A.BZ0000,'药房出库单:')-5) from yk_yprkd0 A where A.RKDH00 = 'R11991'; if V_RKLXBH = '06' then --药房退库单的 select instr(A.BZ0000,'药房出库单:') into vCount from YK_YPRKD0 A where A.RKDH00 = PRKQLDH; if vCount > 0 then --获取药房出库单号 select substr(A.BZ0000,vCount + 6,length(A.BZ0000) - vCount - 5) into V_YFCKD0 from yk_yprkd0 A where A.RKDH00 = PRKQLDH; select A.YFBMBH,A.CKLXBH,B.CKLXMC into V_YFBMBH,V_YFRCLX,V_YFRCMC from YF_YPCKD0 A,BM_YFCKLX B where A.CKLXBH = B.CKLXBH and A.CKDH00 = V_YFCKD0; --判断药房出库医保日志是否已生成 begin select ZSID00 into V_YFZSID from YK_YBZSRZ where CRLX00 = 'C' and RKQLDH = V_YFCKD0 and ZT0000 = '0'; exception when others then V_ZSID00 := 0; end; --生成药房调拔出库追溯码日志表 if V_ZSID00 = 0 then select SQ_YK_YBZSRZ_ZSID00.Nextval into V_YFZSID from dual; insert into YK_YBZSRZ ( ZSID00, --1. 追溯ID(本表ID)SQ_YK_YBZSRZ_ZSID00.nextval RKQLDH, --2. 入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH CRLX00, --3. 出入库类型 R:入库 C:出库 CRLXBH, --4. 对应的入库、出库具体类型, 填写字典表BM_YBSXZD中SXBM00=''inv_chg_type''对应的VALUE0字段 CZY000, --5. 操作员, BM_YGBM00.YGBH00 ZT0000, RCLXBH, RCLXMC, BMBH00, QXBMBH, LYBMBH, YWXZ00 ) values ( V_YFZSID, V_YFCKD0, 'C', '102', --药房调拔出库 PCZY000, '0', V_YFRCLX, V_YFRCMC, V_YFBMBH, V_BMBH00, null, '1' --0：药库的出入; 1: 药房的出入 ); end if; end if; --药房追溯码出库批次明细表 insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_YFZSID,V_YFCKD0,A.YPTHPC,A.YPNM00,A.SCPH00,A.SCRQ00,0, A.RKDW00,A.LSDJ00,A.ZHL000,PCZY000,'0' from YK_YPRKMX A where A.RKDH00 = PRKQLDH and exists ( select 1 from YK_ZSLCJL B where B.RKQLDH = PRKQLDH and B.RCXZ00 = '06' and B.YKRKPC = A.YPTHPC ) and not exists ( select 1 from YK_ZSRKPC C where C.ZSID00 = V_YFZSID and C.YKRKPC = A.YPRKPC and C.YPNM00 = A.YPNM00 ); --追溯码入库明细表日志 insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,B.ZSYPID,V_YFZSID,PRKQLDH,A.YKRKPC,A.YPNM00,A.ZSM000,PCZY000 from YK_ZSLCJL A,YK_ZSRKPC B where A.YPNM00 = B.YPNM00 and A.YKRKPC = B.YKRKPC and B.ZSID00 = V_YFZSID and A.RKQLDH = PRKQLDH and A.RCXZ00 = '06' and not exists ( select 1 from YK_ZSMMXB C,YK_YBZSRZ D where C.ZSID00 = D.ZSID00 and D.RKQLDH = V_YFCKD0 and D.ZT0000 in ('0','1') and C.ZSM000 = A.ZSM000 ); --重新计算入库数量 update YK_ZSRKPC A set A.RKSL00 = ( select count(1) from YK_ZSMMXB B where A.ZSYPID = B.ZSYPID ) where A.ZSID00 = V_YFZSID; --删除掉入库数理为0 的 YK_ZSRKPC delete from YK_ZSRKPC A where A.ZSID00 = V_YFZSID and RKSL00 = 0; --删除掉没有数据的YK_YBZSRZ select count(1) into vCount from YK_ZSMMXB where ZSID00 = V_YFZSID; if vCount = 0 then delete from YK_YBZSRZ where ZSID00 = V_YFZSID; end if; end if; --药房调拔出库 END --判断医保日志是否已生成 begin select ZSID00 into V_ZSID00 from YK_YBZSRZ where CRLX00 = 'R' and RKQLDH = PRKQLDH and ZT0000 = '0'; exception when others then V_ZSID00 := 0; end; --生成药库入追溯码日志表 if V_ZSID00 = 0 then select SQ_YK_YBZSRZ_ZSID00.Nextval into V_ZSID00 from dual; insert into YK_YBZSRZ ( ZSID00, --1. 追溯ID(本表ID)SQ_YK_YBZSRZ_ZSID00.nextval RKQLDH, --2. 入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH CRLX00, --3. 出入库类型 R:入库 C:出库 CRLXBH, --4. 对应的入库、出库具体类型, 填写字典表BM_YBSXZD中SXBM00=''inv_chg_type''对应的VALUE0字段 CZY000, --5. 操作员, BM_YGBM00.YGBH00 ZT0000, RCLXBH, RCLXMC, BMBH00, QXBMBH, LYBMBH, YWXZ00 ) values ( V_ZSID00, PRKQLDH, 'R', V_CRLXBH, PCZY000, '0', V_RKLXBH, V_RKLXMC, V_BMBH00, null, null, '0' ); end if; --重新生成数据 -- delete from YK_ZSRKPC where ZSID00 = V_ZSID00; -- delete from YK_ZSMMXB where ZSID00 = V_ZSID00; --药库追溯码入库批次明细表 if V_RKLXBH = '06' then --药房退库单的 insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_ZSID00,PRKQLDH,A.YPTHPC,A.YPNM00,A.SCPH00,A.SCRQ00,0, A.RKDW00,A.LSDJ00,A.ZHL000,PCZY000,'0' from YK_YPRKMX A where A.RKDH00 = PRKQLDH and exists ( select 1 from YK_ZSLCJL B where B.RKQLDH = PRKQLDH and B.RCXZ00 = '06' and B.YKRKPC = A.YPTHPC ) and not exists ( select 1 from YK_ZSRKPC C where C.ZSID00 = V_ZSID00 and C.YKRKPC = A.YPTHPC and C.YPNM00 = A.YPNM00 ); else insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_ZSID00,PRKQLDH,A.YPRKPC,A.YPNM00,A.SCPH00,A.SCRQ00,0, --(select count(1) from YK_RKZSM0 C where C.YKRKPC = A.YPRKPC ) RKSL00, A.RKDW00,A.LSDJ00,A.ZHL000,PCZY000,'0' from YK_YPRKMX A where A.RKDH00 = PRKQLDH and exists ( select 1 from YK_RKZSM0 B where B.YKRKPC = A.YPRKPC ) and not exists ( select 1 from YK_ZSRKPC C where C.ZSID00 = V_ZSID00 and C.YKRKPC = A.YPRKPC and C.YPNM00 = A.YPNM00 ); end if; --药库追溯码入库明细表日志 if V_RKLXBH = '06' then --药房退库单的 insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,B.ZSYPID,V_ZSID00,PRKQLDH,A.YKRKPC,A.YPNM00,A.ZSM000,PCZY000 from YK_ZSLCJL A,YK_ZSRKPC B where A.YPNM00 = B.YPNM00 and A.YKRKPC = B.YKRKPC and B.ZSID00 = V_ZSID00 and A.RKQLDH = PRKQLDH and A.RCXZ00 = '06' and not exists ( select 1 from YK_ZSMMXB C,YK_YBZSRZ D where C.ZSID00 = D.ZSID00 and D.RKQLDH = PRKQLDH and D.ZT0000 in ('0','1') and C.ZSM000 = A.ZSM000 ); else insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,B.ZSYPID,V_ZSID00,PRKQLDH,A.YKRKPC,A.YPNM00,A.ZSM000,PCZY000 from YK_RKZSM0 A,YK_ZSRKPC B where A.YPNM00 = B.YPNM00 and A.YKRKPC = B.YKRKPC and B.ZSID00 = V_ZSID00 and not exists ( select 1 from YK_ZSMMXB C,YK_YBZSRZ D where C.ZSID00 = D.ZSID00 and D.RKQLDH = PRKQLDH and D.ZT0000 in ('0','1') and C.ZSM000 = A.ZSM000 ); end if; --对删除的入库追溯码进行删除处理 if V_RKLXBH = '06' then delete from YK_ZSMMXB A where exists ( select 1 from YK_YBZSRZ B where A.ZSID00 = B.ZSID00 and B.RKQLDH = PRKQLDH and ZT0000 = '0' ) and not exists ( select 1 from YK_ZSLCJL M where M.ZSM000 = A.ZSM000 and M.RKQLDH = PRKQLDH ); else delete from YK_ZSMMXB A where exists ( select 1 from YK_YBZSRZ B where A.ZSID00 = B.ZSID00 and B.RKQLDH = PRKQLDH and ZT0000 = '0' ) and not exists ( select 1 from YK_YPRKMX M,YK_RKZSM0 N where M.YPRKPC = N.YKRKPC and M.YPNM00 = N.YPNM00 and N.ZSM000 = a.ZSM000 and M.RKDH00 = PRKQLDH ); end if; --重新计算入库数量 update YK_ZSRKPC A set A.RKSL00 = ( select count(1) from YK_ZSMMXB B where A.ZSYPID = B.ZSYPID ) where A.ZSID00 = V_ZSID00; --删除掉入库数理为0 的 YK_ZSRKPC delete from YK_ZSRKPC A where A.ZSID00 = V_ZSID00 and RKSL00 = 0; --删除掉没有数据的YK_YBZSRZ select count(1) into vCount from YK_ZSMMXB where ZSID00 = V_ZSID00; if vCount = 0 then delete from YK_YBZSRZ where ZSID00 = V_ZSID00; end if; end if; if POPFLAG = '1' then select A.CKLXBH,B.CKLXMC,A.YKBMBH,A.CKQXBH into V_CKLXBH,V_CKLXMC,V_BMBH00,V_QXBMBH from YK_YPQLD0 A,BM_YKCKLX B where A.CKLXBH = B.CKLXBH and A.YPQLDH = PRKQLDH; if not V_CKLXBH in ('09') then select count(1) into vCount from BM_TYCJBM where XTDM00 = 'YBZSMJK' and CJBM00 = 'YKCKLX_'||V_CKLXBH and SFQY00 = '1'; if vCount = 0 then return; end if; end if; select count(1) into vCount from YK_RKZSM0 A where exists ( select 1 from YK_YPCKMX B where B.YPQLDH = PRKQLDH and B.YPNM00=A.YPNM00 and B.YPQLPC = A.YPTKPC and B.YPRKPC=A.YKRKPC ); if vCount = 0 then return; end if; --判断医保日志是否已生成 begin select ZSID00 into V_ZSID00 from YK_YBZSRZ where CRLX00 = 'C' and RKQLDH = PRKQLDH and ZT0000 = '0'; exception when others then V_ZSID00 := 0; end; --101:调拔入库;102:调拔出库;103:盘盈;104:盘损;105:销毁;106:其它入库;107:其它出库; --108:商品采购入库;109:商品退货出库;110:赠药入库;111:赠药退回出库 if V_CKLXBH = '09' then --退货出库 V_CRLXBH := '109'; V_YWXZ00 := 0; V_LYBMBH := null; else V_CRLXBH := '102'; V_YWXZ00 := 0; V_LYBMBH := null; end if; --生成药房调拔出库追溯码日志表 if V_ZSID00 = 0 then select SQ_YK_YBZSRZ_ZSID00.Nextval into V_ZSID00 from dual; insert into YK_YBZSRZ ( ZSID00, --1. 追溯ID(本表ID)SQ_YK_YBZSRZ_ZSID00.nextval RKQLDH, --2. 入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH CRLX00, --3. 出入库类型 R:入库 C:出库 CRLXBH, --4. 对应的入库、出库具体类型, 填写字典表BM_YBSXZD中SXBM00=''inv_chg_type''对应的VALUE0字段 CZY000, --5. 操作员, BM_YGBM00.YGBH00 ZT0000, RCLXBH, RCLXMC, BMBH00, QXBMBH, LYBMBH, YWXZ00 ) values ( V_ZSID00, PRKQLDH, 'C', V_CRLXBH, PCZY000, '0', V_CKLXBH, V_CKLXMC, V_BMBH00, V_QXBMBH, V_LYBMBH, V_YWXZ00 ); end if; --追溯码入库批次明细表 insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_ZSID00,PRKQLDH,A.YPRKPC,A.YPNM00,B.SCPH00,B.SCRQ00,(select count(1) from YK_RKZSM0 K where K.YKRKPC = A.YPRKPC and K.YPTKPC = A.YPQLPC ) RKSL00, B.RKDW00,B.LSDJ00,B.ZHL000,PCZY000,'0' from YK_YPCKMX A,YK_YPRKMX B where A.YPNM00 = B.YPNM00 and A.YPRKPC = B.YPRKPC and A.YPQLDH = PRKQLDH and exists ( select 1 from YK_RKZSM0 C where C.YKRKPC = A.YPRKPC and A.YPQLPC = C.YPTKPC ) and not exists ( select 1 from YK_ZSRKPC where RKQLDH = PRKQLDH and YKRKPC = A.YPRKPC and YPNM00 = A.YPNM00 ); --药库出库追溯码明细表日志 insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,( select B.ZSYPID from YK_ZSRKPC B where B.RKQLDH = PRKQLDH and B.ZSID00 = V_ZSID00 and B.YKRKPC = A.YKRKPC) ZSYPID,V_ZSID00,PRKQLDH,A.YKRKPC,A.YPNM00,A.ZSM000,PCZY000 from YK_RKZSM0 A where 1 = 1 and exists ( select 1 from YK_YPCKMX C where C.YPQLDH = PRKQLDH and C.YPRKPC = A.YKRKPC and C.YPQLPC = A.YPTKPC and C.YPNM00 = A.YPNM00 ) and not exists ( select 1 from YK_ZSMMXB where RKQLDH = PRKQLDH and ZSM000 = A.ZSM000 ); --院内调拔出库,生成药房的入库日志 if V_CKLXBH <> '09' then --获取YF_YPRKD0.RKDH00 V_YFRKDH begin select A.RKDH00,A.RKLXBH,B.RKLXMC,A.YFBMBH into V_YFRKDH,V_RKLXBH,V_RCLXMC,V_YFBMBH from YF_YPRKD0 A,BM_YFRKLX B where A.RKLXBH=B.RKLXBH and A.YPQLDH = PRKQLDH; exception when others then Ptsxx00 := '未找到请领单:'||PRKQLDH||' 药房入库单！'; return; end; --判断医保日志是否已生成 begin select ZSID00 into V_ZSID00 from YK_YBZSRZ where CRLX00 = 'R' and RKQLDH = V_YFRKDH and ZT0000 = '0'; exception when others then V_ZSID00 := 0; end; --生成追溯码日志表 if V_ZSID00 = 0 then select SQ_YK_YBZSRZ_ZSID00.Nextval into V_YFZSID from dual; insert into YK_YBZSRZ ( ZSID00, --1. 追溯ID(本表ID)SQ_YK_YBZSRZ_ZSID00.nextval RKQLDH, --2. 入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH CRLX00, --3. 出入库类型 R:入库 C:出库 CRLXBH, --4. 对应的入库、出库具体类型, 填写字典表BM_YBSXZD中SXBM00=''inv_chg_type''对应的VALUE0字段 CZY000, --5. 操作员, BM_YGBM00.YGBH00 ZT0000, RCLXBH, RCLXMC, BMBH00, QXBMBH, LYBMBH, YWXZ00 ) values ( V_YFZSID, V_YFRKDH, 'R', '101', PCZY000, '0', V_RKLXBH, V_RCLXMC, V_YFBMBH, null, V_BMBH00, '1' ); end if; --追溯码入库批次明细表 insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --药房入库单号 YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_YFZSID,V_YFRKDH,YKRKPC,YPNM00,SCPH00,SCRQ00,RKSL00,RKDW00,RKDJ00,ZHL000,CZY000,'0' from YK_ZSRKPC A where A.RKQLDH = PRKQLDH and not exists ( select 1 from YK_ZSRKPC B where B.RKQLDH = V_YFRKDH and B.YKRKPC = A.YKRKPC and B.YPNM00 = A.YPNM00 ); --药房入库追溯码明细表日志 insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,(select C.ZSYPID from YK_ZSRKPC C where C.RKQLDH = V_YFRKDH and C.YPNM00 = A.YPNM00 and C.YKRKPC = A.YKRKPC ) ZSYPID, V_YFZSID,V_YFRKDH,YKRKPC,YPNM00,ZSM000,CZY000 from YK_ZSMMXB A where A.RKQLDH = PRKQLDH and not exists ( select 1 from YK_ZSMMXB B where B.RKQLDH = V_YFRKDH and B.YKRKPC = A.YKRKPC and B.YPNM00 = A.YPNM00 and A.ZSM000 = B.ZSM000 ); end if; end if; if POPFLAG = '2' then --撤消出库 --获取负单单号 select max(YPQLDH) into V_YPQLDH from YK_YPQLD0 where QRRQ00 >= to_char(sysdate-1,'YYYYMMDD') and CKLXBH = '09' and BZ0000 = '被冲单据号:'||PRKQLDH; select count(1) into vCount from YK_RKZSM0 A where exists ( select 1 from YK_YPCKMX B where B.YPQLDH = PRKQLDH and B.YPRKPC = A.YKRKPC and B.YPQLPC = A.YPTKPC ); if vCount = 0 then return; end if; --生成追溯码日志表 select SQ_YK_YBZSRZ_ZSID00.Nextval into V_ZSID00 from dual; insert into YK_YBZSRZ ( ZSID00, --1. 追溯ID(本表ID)SQ_YK_YBZSRZ_ZSID00.nextval RKQLDH, --2. 入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH CRLX00, --3. 出入库类型 R:入库 C:出库 CRLXBH, --4. 对应的入库、出库具体类型, 填写字典表BM_YBSXZD中SXBM00=''inv_chg_type''对应的VALUE0字段 CZY000, --5. 操作员, BM_YGBM00.YGBH00 ZT0000 ) values ( V_ZSID00, V_YPQLDH, 'R', '108', PCZY000, '0' ); --追溯码入库批次明细表 insert into YK_ZSRKPC ( ZSYPID, --序号, SQ_YK_ZSYPMX_ZSYPID.nextval ZSID00, --主表追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 SCPH00, --生产批号 SCRQ00, --生产日期 RKSL00, --入库数量 RKDW00, --入库单位 RKDJ00, --零售价 ZHL000, --转换率 CZY000, --操作员, BM_YGBM00.YGBH00 ZT0000 ) select SQ_YK_ZSRKPC_ZSYPID.NEXTVAL,V_ZSID00,V_YPQLDH,A.YPRKPC,A.YPNM00,A.SCPH00,A.SCRQ00, ( select count(1) from YK_RKZSM0 M where M.YKRKPC = A.YPRKPC and M.YPTKPC = D.YPQLPC ) RKSL00, A.RKDW00,A.LSDJ00,A.ZHL000,PCZY000,'0' from YK_YPRKMX A,YK_YPCKMX C,YK_YPCKMX D where C.YPRKPC = A.YPRKPC and C.CXCKPC = D.YPCKPC and C.BZ0000 = D.YPQLDH and C.YPQLDH = V_YPQLDH and exists ( select 1 from YK_RKZSM0 B where B.YKRKPC = A.YPRKPC ); insert into YK_ZSMMXB ( ZSMXID, --序号, SQ_YK_ZSMMXB_ZSMXID.nextval ZSYPID, --追溯入库批次ID, SD_YK.YK_ZSRKPC.ZSYPID ZSID00, --追溯ID, SD_YK.YK_YBZSRZ.ZSID00 RKQLDH, --入库请领单号, YK_YPRKD0.RKDH00或YK_YPQLD0.YPQLDH YKRKPC, --药品入库批次, YK_YPRKMX.YPRKPC YPNM00, --药品内码, BM_YD0000.YPNM00 ZSM000, --追溯码 CZY000 --操作员, BM_YGBM00.YGBH00 ) select SQ_YK_ZSMMXB_ZSMXID.Nextval,B.ZSYPID,V_ZSID00,V_YPQLDH,A.YKRKPC,A.YPNM00,A.ZSM000,PCZY000 from YK_RKZSM0 A,YK_ZSRKPC B where A.YPNM00 = B.YPNM00 and A.YKRKPC = B.YKRKPC and B.ZSID00 = V_ZSID00 and exists ( select 1 from YK_YPCKMX C where C.YPCKPC in ( select CXCKPC from YK_YPCKMX where YPQLDH = V_YPQLDH) and C.YPQLPC = A.YPTKPC); end if; Pzxcgbz:=1; if PCOMMIT='Y' then commit; end if; exception when No_Data_Found then raise_application_error(-20001,substrb('没有找到数据！*'||sqlerrm||Ptsxx00,1,240)); when Ecustom then raise_application_error(-20010,substrb(Ptsxx00||'！*',1,240)); when others then raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*',1,240)); end;