CREATE OR REPLACE PROCEDURE SP_SF_FPBD00_REG000
(
    ad_PJH000     in   number,             --票据号
    as_YJJXH0     in   char,               --外部票据号
    ad_CZY000     in   number,             --操作员工号
    as_CZYXM0     in   char,               --操作员姓名
    as_CZBZ00     in   char,               --操作标志,'1':补打发票,'2':补打预交金票据,'3':缴款单,'4':补打退预交金票据 5:补打门诊挂号单
    ad_NEWPJH    out   number,             --新的票据内部号
    as_YHMSG0    out   varchar,            --存储过程提示的错误信息
    as_SYSMSG    out   varchar,            --系统提示的错误信息
    ad_CZYKS0     in   number default 0    --操作员科室
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- qks         2012.08.21  增加入参ad_CZYKS0,调用过程SP_SF_PJSYQK_INSERT
-- zhanghr     2012.09.25  过程做了修改
-- gzj         2018.10.29  参数维护是否允许非原发票的收费员进行补打 MZSF9-20181026-001
-- jlg         2021.04.12  重置过程，规范脚本书写   MZSF9-20210315-006
as
  ls_PJZT00      SF_PJSYQK.PJZT00%type;
  ls_NEWPJH      number;
  ls_CZY000      number;
  ls_JKDID0      number;
  ls_XPJZT0      char(1);                --新票据状态，缴款过的补打视为冲销1，未缴款视为作废3
  ls_CZRQ00      char(8);
  ls_CZSJ00      char(8);
  Vzxcgbz        number;
  Ecustom        exception;
  SF_FJSYCZDQFP  XT_XTCS00.VALUE0%type;  --是否允许非原发票员补打发票
begin
  as_YHMSG0:='';
  --被补打的票据和被退费的票据的差别在于OLDPJH,补打OLDPJH=0 退费OLDPJH>0
  ls_CZRQ00 := to_char(sysdate,'YYYYMMDD');
  ls_CZSJ00 := to_char(sysdate,'HH24:MI:SS');
  --门诊发票综合处理,发票重置是否允许非结算员重置当前发票,0否 1是,默认为0
  select nvl(max(trim(value0)),'0') into SF_FJSYCZDQFP from XT_XTCS00 where NAME00='SF_FJSYCZDQFP';

  select PJZT00,NEWPJH,decode(sign(JKDID0),1,'1','3'),CZY000,JKDID0 into ls_PJZT00,ls_NEWPJH,ls_XPJZT0,ls_CZY000,ls_JKDID0
    from SF_PJSYQK  where PJH000 = ad_PJH000;

  if ls_PJZT00<>'0' or ls_NEWPJH<>0 then
    as_YHMSG0:='本发票已经作废,不再能补打';
    as_SYSMSG:=SQLERRM||'本发票已经作废,不再能补打';
    raise Ecustom;
  end if;
  if (ad_CZY000<>ls_CZY000) and (SF_FJSYCZDQFP<>'1') then
    as_YHMSG0:='原发票的收费员才能补打';
    as_SYSMSG:='';
    raise Ecustom;
  end if;
  if ls_JKDID0>0 and as_CZBZ00 in ('2','4') then
    as_YHMSG0:='缴款过预交金单不能补打';
    as_SYSMSG:='';
    raise Ecustom;
  end if;

  --生成原票的冲销记录
  select SQ_SF_PJSYQK_PJH000.nextval  into  ad_NEWPJH  from  dual;
  insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XM0000,XB0000,HJJE00,ZFJE00,
                        JZJE00,JMJE00,YHJE00,XJJE00,ZZJE00,YHKJE0,TCZFJE,TCJZJE,
                        DZQBJE,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,OLDPJH,PJZT00,
                        FZPJH0,XSRQ00,GYJZD0,BDBZ00,BRID00,CZYKS0,YYID00,PJLYID,SFBBD0,JFLBID)
                select ad_NEWPJH,PLBH00,PLMC00,'-'||PJXH00,XM0000,XB0000,-HJJE00,-ZFJE00,
                       -JZJE00,-JMJE00,-YHJE00,-XJJE00,-ZZJE00,-YHKJE0,-TCZFJE,-TCJZJE,
                       -DZQBJE,ad_CZY000,as_CZYXM0,ls_CZRQ00,ls_CZSJ00,'N',ad_PJH000,ls_XPJZT0,
                       FZPJH0,XSRQ00,GYJZD0,'Y',BRID00,ad_CZYKS0,YYID00,PJLYID,'1',JFLBID
                  from SF_PJSYQK where PJH000=ad_PJH000;
  insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00)
      select  ad_NEWPJH,XMBH00,XMMC00,-XMJE00,XMBZ00,ZFBL00  from  SF_PJSYMX  where  PJH000 = ad_PJH000;

  --生成新的票据记录
  select SQ_SF_PJSYQK_PJH000.nextval  into  ad_NEWPJH  from  dual;
  insert into SF_PJSYQK(PJH000,PLBH00,PLMC00,PJXH00,XM0000,XB0000,HJJE00,ZFJE00,
                        JZJE00,JMJE00,YHJE00,XJJE00,ZZJE00,YHKJE0,TCZFJE,TCJZJE,
                        DZQBJE,CZY000,CZYXM0,DYRQ00,DYSJ00,SFDY00,OLDPJH,PJZT00,
                        FZPJH0,XSRQ00,GYJZD0,BDBZ00,BRID00,CZYKS0,JFLBID,WBID00 )
                 select ad_NEWPJH,PLBH00,PLMC00,as_YJJXH0,XM0000,XB0000,HJJE00,ZFJE00,
                        JZJE00,JMJE00,YHJE00,XJJE00,ZZJE00,YHKJE0,TCZFJE,TCJZJE,
                        DZQBJE,ad_CZY000,as_CZYXM0,ls_CZRQ00,ls_CZSJ00,'Y',ad_PJH000,'0',
                        FZPJH0,XSRQ00,GYJZD0,'Y',BRID00,ad_CZYKS0,JFLBID,WBID00
                   from SF_PJSYQK where PJH000=ad_PJH000;
  insert into SF_PJSYMX(PJH000,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00)
      select ad_NEWPJH,XMBH00,XMMC00,XMJE00,XMBZ00,ZFBL00  from  SF_PJSYMX  where  PJH000 = ad_PJH000;

  --原票记录做些状态等信息修改
  update SF_PJSYQK set PJZT00 = ls_XPJZT0,        --缴款过的补打视为冲销1，未缴款视为作废3
                       HSCZY0 = ad_CZY000,
                       HSCZXM = as_CZYXM0,
                       HSRQ00 = ls_CZRQ00,
                       HSSJ00 = ls_CZSJ00,
                       SFBBD0 = '1'
                     --NEWPJH = ad_NEWPJH
                 where PJH000 = ad_PJH000;

  --新票据对应领用记录等相应处理
  if as_CZBZ00 in ('1','2','3','4','5') then
    SP_SF_PJSYQK_INSERT(
            ad_NEWPJH,             --票据号
            ad_CZY000,             --输入：操作员编码
            ad_CZYKS0,             --操作员科室
            'N',                   --是否提交
            Vzxcgbz,               --执行成功标志 1成功 0失败
            as_YHMSG0,             --执行结果提示信息，成功的情况下也可能有警告信息
            as_SYSMSG              --执行结果详细信息，成功的情况下也可能有警告信息，一般用来显示参数等指标
          );
  end if;
--commit,打印部门的提交暂时放在Delphi中处理
exception
  when no_data_found then
    raise_application_error(-20001,substrb('数据没有找到！*'||as_YHMSG0||sqlerrm,1,220));
  when Ecustom       then
    raise_application_error(-20010,substrb(as_YHMSG0||'！*'||as_SYSMSG,1,220));
  when others        then
    raise_application_error(-20002,substrb(nvl(sqlerrm, '原因不明出错')||'！*'||as_YHMSG0,1,220));
end;