存储过程

1.个检

create or replace procedure SP_ESI_HMZXDD_RECE00
-- When         Who        What
-- 2017.12.15   JXQ        Create
(
  POPFLAG  in     number,      --操作标志 0:按订单号
  PDDH000  in     varchar2,    --订单号
  PYYBH00  in     varchar2,    --医院编号
  PTJH000  out    number       --体检号
)
as
  VCOUNT0  number;
  VZXCGBZ  number;
  VDATE00  varchar2(8);
  VTIME00  varchar2(8);
  VID0000  varchar2(50);
  VTTMC00  varchar2(50);
  VTSXX00  varchar2(200);
  VSQLSTR  varchar2(500);
  VERRMSG  varchar2(200);
  VSYWID0  varchar2(1000);
  VTMMS00  PT_SYSCS0.VALUE0%type;       --条码模式
  VROW     HM_TMP_TJZ000%rowtype;
  VTTID00  HM_YW_TJZ000.TTID00%type;
  VJKTTID  HM_YW_TJTT00.JKTTID%type;
  VRWID00  HM_YW_TJZ000.RWID00%type;
  VFZID00  HM_YW_TJZ000.FZID00%type;
  VYYBH00  HM_YW_TJZ000.YYBH00%type;
  VKL0000  HM_YW_TJZ000.KL0000%type;
  VKDYSID  HM_YW_TJZ000.KDYSID%type;
  VSFZH00  HM_YW_TJZ000.SFZH00%type;
  VTJH000  HM_YW_TJZFB0.TJH000%type;
  VJKTJH0  HM_YW_TJZFB0.TJH000%type;
  VSFYWID  HM_YW_TJYW00.SFYWID%type;
  VDDH000  HM_YW_TJZFB0.DDH000%type;
  VDDYJE0  HM_YW_TJZFB0.DDYJE0%type;
  VDDSJE0  HM_YW_TJZFB0.DDSJE0%type;
  VJYXMID  HM_TMP_TJZFB0.JYXMID%type;
  VSQXMID  HM_ZD_TJSQXM.SQXMID%type;
  ECUSTOM  exception;
  type T_CUR_TJZ is ref cursor;     --
  CUR_TJZ T_CUR_TJZ;
  type T_CUR_YW is ref cursor;     --
  CUR_YW T_CUR_YW;
  type T_CUR_YW_TMP is ref cursor;     --
  CUR_YW_TMP T_CUR_YW_TMP;
  type T_CUR_JYXM is ref cursor;     --
  CUR_JYXM T_CUR_JYXM;
begin
  VDATE00 := to_char(sysdate, 'YYYYMMDD');
  VTIME00 := to_char(sysdate, 'HH24:MI:SS');
  --SP_ESI_HMZXDD_TJTT00(0);
  --处理条码生成
  select SF_PT_GetXTCS('HM_LISTMMS', '0', VYYBH00) into VTMMS00 from dual;
   select count(*) into VCOUNT0 from HM_YW_TJZFB0 where 1=1 and DDH000 = PDDH000;
  if VCOUNT0 > 0 then
      VERRMSG := '已生成订单号'; raise ECUSTOM;
  end if;
  select count(*) into VCOUNT0 from hm_yw_tjz000_qzj where 1=1 and code = PDDH000;
  if VCOUNT0 = 0 then
      VERRMSG := '未找到订单号'; raise ECUSTOM;
  end if;
  select tjh000, tjh000, code, TSXE00, TSXE00, SFZH00, rwid00
    into VID0000, VJKTJH0, VDDH000, VDDYJE0, VDDSJE0, VSFZH00, VTTID00
    from hm_yw_tjz000_qzj where code = PDDH000;
  select nvl(max(a.TTID00), null), nvl(max(JKTTID), null) into VTTID00, VJKTTID from HM_YW_TJTT00 a inner join Hm_Yw_Ttrw00 b on a.ttid00 = b.ttid00 
  where b.rwid00 = VTTID00;
  if VTTID00 is null then
    SP_HM_OPER_DDTTRW(1, VTTID00, VRWID00, VFZID00);
  else
    select nvl(max(RWID00), null) into VRWID00 from HM_YW_TTRW00 where TTID00 = VTTID00 and RWYDRQ like to_char(sysdate, 'YYYY')||'%' and rownum = 1;
    if VRWID00 is null then
      select SQ_HM_YW_TTRW00_RWID00.nextval into VRWID00 from dual;
      insert into HM_YW_TTRW00 (RWID00, TTID00, DJR000, RWYDRQ)
      select VRWID00, VTTID00, 0, to_char(sysdate, 'YYYYMMDD') from dual;
      insert into HM_YW_TTRWFB (RWID00, TTID00)
      select VRWID00, VTTID00 from dual;
    end if;
    select nvl(max(RWFZID), null) into VFZID00 from HM_YW_TTRWFZ where RWID00 = VRWID00 and BZ0000 = '订单' and rownum = 1;
    if VFZID00 is null then
      select SQ_HM_YW_TTRWFZ_RWFZID.nextval into VFZID00 from dual;
      insert into HM_YW_TTRWFZ(RWFZID, RWID00, FZMC00, PYSM00, BZ0000, TJLXID, FBBH00)
      select VFZID00, VRWID00, '通用', 'ty', '订单', 1, 4 from dual;
    end if;
  end if;
  select nvl(max(TJH000),null) into VTJH000 from HM_TMP_TJZFB0 where DDH000 = VDDH000;
  if VTJH000 is null then
    select SF_HM_GetTJH(PYYBH00) into VTJH000 from dual;
  end if;
  --体检者表
  insert into HM_TMP_TJZ000 (
    TJH000, DH0000, CSRQ00, NL0000, DDTJH0, DDH000, XM0000, XB0000,
    HYZKID, LXID00, TJLXID, SFHY00, KDYSID, KL0000, TSXE00, DAID00)
  select
    VTJH000, DH0000, CSRQ00, NL0000, VDDH000, VDDH000, XM0000, XB0000,
   6 HYZKID, 1 LXID00, 1 TJLXID, '0' SFHY00, '0', null, TOTAL_FEE, null
  from hm_yw_tjz000_qzj A where code = PDDH000 and not exists (select 1 from HM_TMP_TJZ000 where DDH000 = VDDH000);
  --体检者附表
  insert into HM_TMP_TJZFB0 (
    TJH000, DDH000, DDYJE0, DDSJE0)
  select
    VTJH000, code, TSXE00, TSXE00
  from hm_yw_tjz000_qzj where code = PDDH000 and not exists (select 1 from HM_TMP_TJZFB0 where DDH000 = VDDH000);
  --业务表
  delete from HM_TMP_TJYW00 where TJH000 = VTJH000;
  insert into HM_TMP_TJYW00 (
    SFYWID, DJYWID, TJH000, TTID00, RWID00, SQXMID, SFXMMC,
    ZYFLID, DJ0000, SL0000, YSJE00, TSJE00, GWYJE0, ZFJE00, YDJ000,
    DJR000, DJRQ00, DJSJ00, TXM000, CJRQ00, CJSJ00, CJR000, YBCJKS,
    JYYLJG, JYYLRQ, JYYLSJ, QJ0000, JCBZ00, JCKSID, FKKSRQ, FKKSSJ,
    FKJSRQ, FKJSSJ, TFNTBZ, KDRQ00, KDSJ00, KDYS00, SFSF00, SFJZ00,
    SFRQ00, SFSJ00, KSCZY0, JCYSID, JCYSXM, JCRQ00, JCSJ00, ZHXMID,
    FYWID0, KHQRBZ, TOPACS, FKJCID, YYRQ00, SGYWID, JPKSID, DDTJH0)
  select
    SQ_HM_YW_TJYW00_SFYWID.nextval, null, VTJH000, VTTID00, VRWID00, B.SQXMID, B.SFXMMC,
    null, A.DJ0000, 1, A.DJ0000, ZFJE00, 0, 0, '0',
    null, null, null, null, null, null, null, (select YBCJKS from HM_ZD_JYYBLX where YBLXID = B.BBLXID),
    '0', null, null, '0', '0', (select SFKSID from HM_ZD_TJSQXM where SQXMID = to_number(A.SQXMID)), '00000000', null,
    null, null, '0', VDATE00, VTIME00, null, '0', '0',
    null, null, null, null, null, null, null, null,
    -1, '0', '0', null, '00000000', null, null, VJKTJH0
  from hm_yw_tjyw00_qzj A, HM_ZD_TJSQXM B where A.SQXMID = to_number(B.SQXMID) and A.tjh000 = VJKTJH0
   and not exists (select 1 from HM_TMP_TJYW00 where DDTJH0 = VJKTJH0 and to_number(SQXMID) = A.SQXMID);
  --更新DJYWID
  declare cursor CUR_YW_TMP is
  select TJH000, SFYWID from HM_TMP_TJYW00 where TJH000 = VTJH000 and DJYWID is null;
  begin
    for ROW in CUR_YW_TMP loop
      select SQ_HM_YW_TJYW00_SFYWID.nextval into VSFYWID from dual;
      update HM_TMP_TJYW00 set DJYWID = VSFYWID where TJH000 = ROW.TJH000 and SFYWID = ROW.SFYWID;
    end loop;
  end;
  --========================= 插入正式库 =============================
  VYYBH00 := PYYBH00;
  VERRMSG := '***** 001 *****';
  select count(*) into VCOUNT0 from HM_YW_TJZ000 where TJH000 = VTJH000;
  if VCOUNT0 = 0 then
    select * into VROW from HM_TMP_TJZ000 where TJH000 = VTJH000;
    VKDYSID := vROW.KDYSID;
    SP_HM_EDIT_TJZ000(
     0, VTJH000, 0, VROW.DAID00, VSFZH00, VROW.XM0000, VFZID00, VRWID00, VTTID00, null,
     VROW.KL0000, VDDSJE0, VROW.XB0000, VROW.CSRQ00, VROW.NL0000, '1', null, VROW.HYZKID, null, null, VROW.KDYSID,
     null, VROW.DH0000, '0', VROW.LXID00, null, null, null, null, null, -1, VROW.TJLXID,
     null, null, '0', 0, null, null, '4', null, null, null,
     '0', null, '0', '0', VYYBH00, -1, 'N', VZXCGBZ, VTSXX00);
    update HM_YW_TJZ000 set TSXE00 = VROW.TSXE00, KL0000 = VROW.KL0000 where TJH000 = VTJH000;
  end if;
  VERRMSG := '***** 002 *****';
  update HM_YW_TJZFB0 A set
    (DDH000, DDYJE0, DDSJE0) = (select DDH000, DDYJE0, DDSJE0 from HM_TMP_TJZFB0 where TJH000 = A.TJH000)
   where TJH000 = VTJH000;
  --update TBL_ORDER@TODD set status = 1, his_bm = VTJH000 where ORDER_NUM = PDDH000;
  VERRMSG := '***** 003 *****';
  select count(*) into VCOUNT0 from HM_YW_TJYW00 where TJH000 = VTJH000;
  if VCOUNT0 = 0 then
    insert into HM_YW_TJYW00 (
      SFYWID, TJH000, TTID00, RWID00, SQXMID, SFXMMC, ZYFLID, DJ0000, SL0000, YSJE00,
      TSJE00, GWYJE0, ZFJE00, YDJ000, DJR000, DJRQ00, DJSJ00, TXM000, CJRQ00, CJSJ00,
      CJR000, YBCJKS, JYYLJG, JYYLRQ, JYYLSJ, QJ0000, JCBZ00, JCKSID, FKKSRQ, FKKSSJ,
      FKJSRQ, FKJSSJ, TFNTBZ, KDRQ00, KDSJ00, KDYS00, SFSF00, SFJZ00, SFRQ00, SFSJ00,
      KSCZY0, JCYSID, JCYSXM, JCRQ00, JCSJ00, ZHXMID, FYWID0, KHQRBZ, TOPACS, FKJCID,
      YYRQ00, SGYWID, JPKSID, DJYWID)
    select
      SFYWID, VTJH000, TTID00, RWID00, SQXMID, SFXMMC, ZYFLID, DJ0000, SL0000, YSJE00,
      TSJE00, GWYJE0, ZFJE00, '0', '', '', '', null, CJRQ00, CJSJ00,
      CJR000, YBCJKS, JYYLJG, JYYLRQ, JYYLSJ, QJ0000, JCBZ00, JCKSID, FKKSRQ, FKKSSJ,
      FKJSRQ, FKJSSJ, TFNTBZ, KDRQ00, KDSJ00, KDYS00, SFSF00, SFJZ00, SFRQ00, SFSJ00,
      KSCZY0, JCYSID, JCYSXM, JCRQ00, JCSJ00, ZHXMID, FYWID0, KHQRBZ, TOPACS, FKJCID,
      YYRQ00, SGYWID, JPKSID, DJYWID
    from HM_TMP_TJYW00 A where TJH000 = VTJH000
     and not exists (select 1 from HM_YW_TJYW00 where TJH000 = A.TJH000 and DJYWID = A.DJYWID)
     order by SFYWID;
    VERRMSG := '***** 004 *****';
    if VTMMS00 = '0' then --自打印，更新条码
      VSYWID0 := '';
      open CUR_YW for
      select SFYWID from HM_YW_TJYW00 A,HM_ZD_TJSQXM B where A.SQXMID = B.SQXMID AND A.TJH000 = VTJH000 and B.TMQZ00 is not null and A.TXM000 is null
       and A.TFNTBZ = '0';
      loop
        fetch CUR_YW into VSFYWID;
        exit when CUR_YW%NOTFOUND;
        if VSYWID0 is null then
          VSYWID0 := VSFYWID;
        else
          VSYWID0 := VSYWID0||','||VSFYWID;
        end if;
      end loop;
      close CUR_YW;
      if VSYWID0 is not null then
        SP_HM_OPER_JYTM00(0, VTJH000, VSYWID0, 0, 'N', VZXCGBZ, VERRMSG);
      end if;
    end if;
  end if;
  --*/
  SP_HM_REJSYWJE(VTJH000, 1);
  PTJH000 := VTJH000;
exception
  when No_Data_Found then
    raise_application_error(-20001, substrb('没有找到数据！'||sqlerrm||VERRMSG||'--'||VTJH000, 1, 240));
  when ECUSTOM then
    raise_application_error(-20010, substrb(VERRMSG||'！*', 1, 240));
  when others then
    raise_application_error(-20002, substrb(nvl(sqlerrm, '原因不明出错'), 1, 240));
end;