-- Start of DDL Script for Procedure SD_HOSPITAL.SP_TSBZXMTBDY
-- Generated 30-七月-2021 16:24:06 from SD_HOSPITAL@PHXYY

CREATE OR REPLACE 
PROCEDURE sp_tsbzxmtbdy(
P_SFYP       IN CHAR,           --是否药品
P_YBZXLB     in varchar2 default null, --医保中心类型
P_TSBZBH     in varchar2 default null --特殊病种编号
)AS

  V_params        varchar2(255);               --参数变量
  V_errmsg        varchar2(255);               --错误提示变量
  E_custom        exception;                   --错误变量
  V_TSBZYPDYFYB   varchar2(10);            
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- YANGH       2009.05.09  CREATE:特殊病种项目同步对应
-- YANGH       2009.05.13  substr(YBXMBH,1,2)=70的是非医保，不要同步
-- dsm         2015.07.31  是否同步YBXMBH=70....的增加参数控制 for MZYS-20150727-001
-- daihq       2016.01.04  药品增加写入SFXZ00，XZTS00 for XMGL-20151229-002
-- qiulf       2021.06.11  增加两个入参P_YBZXLB，P_TSBZBH 两个参数不为空只同步同一个医保中心，同一个特殊病种 by XMGL-20210602-001
-- ---------   ----------  -------------------------------------------
begin
  V_errmsg:='';
  V_params:='调用过程: SP_TSBZXMTBDY('||P_SFYP||','||P_YBZXLB||','||P_TSBZBH||') ; ';
  select nvl(max(trim(Value0)),'N') into V_TSBZYPDYFYB from XT_XTCS00 where NAME00='XMGL_TSBZYPDYFYB';
  IF P_SFYP = 'Y' THEN
    DELETE FROM BM_TSBZXM A
           WHERE EXISTS(SELECT 1 FROM BM_YBSFDY B WHERE B.YBZXLB=A.YBZXLB AND B.YBXMBH=A.XMBH00 AND SFYP00='Y')
           and (P_YBZXLB is null or A.YBZXLB=P_YBZXLB) and (P_TSBZBH is null or trim(A.BH0000)=P_TSBZBH);
    INSERT INTO BM_TSBZXM(YBZXLB,BH0000,XMBH00,SFXZ00,XZTS00)
           SELECT B.YBZXLB,A.BH0000,B.YBXMBH,A.SFXZ00,A.XZTS00
                  FROM BM_XMBZ00 A,BM_YBSFDY B
                 WHERE A.YBZXLB=B.YBZXLB AND A.XMBH00=B.SFXMID
                   AND A.TYPE00='0' AND B.SFYP00='Y' AND B.YBXMBH IS NOT NULL
                   AND (substr(YBXMBH,1,2) <> '70' or V_TSBZYPDYFYB='Y')
                   and (P_YBZXLB is null or A.YBZXLB=P_YBZXLB) and (P_TSBZBH is null or trim(A.BH0000)=P_TSBZBH);
  ELSE
    DELETE FROM BM_TSBZXM A
           WHERE EXISTS(SELECT 1 FROM BM_YBSFDY B WHERE B.YBZXLB=A.YBZXLB AND B.YBXMBH=A.XMBH00 AND SFYP00='N')
           and (P_YBZXLB is null or A.YBZXLB=P_YBZXLB) and (P_TSBZBH is null or trim(A.BH0000)=P_TSBZBH);
    INSERT INTO BM_TSBZXM(YBZXLB,BH0000,XMBH00)
           SELECT C.YBZXLB,A.BH0000,C.YBXMBH
                  FROM BM_XMBZ00 A,XT_ZLSFGX B,BM_YBSFDY C
                 WHERE A.XMBH00=B.ZLXMID AND A.YBZXLB=C.YBZXLB AND B.SFXMID=C.SFXMID
                   AND B.SFLB00='0' AND A.TYPE00='1'AND SFYP00='N' AND C.YBXMBH IS NOT NULL
                   AND (substr(YBXMBH,1,2) <> '70' or V_TSBZYPDYFYB='Y')
                   and (P_YBZXLB is null or A.YBZXLB=P_YBZXLB) and (P_TSBZBH is null or trim(A.BH0000)=P_TSBZBH);
  END IF;
  COMMIT;
EXCEPTION
  WHEN no_data_found THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001,substrb('数据没有找到！*'||SQLERRM||','||V_params,1,120));
  WHEN E_custom      THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20010,substrb(V_errmsg||'！*'||V_params,1,120));
  WHEN OTHERS        THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20266,substrb(nvl(SQLERRM, '原因不明出错')||'！*'||V_params,1,120));
END;
/



-- End of DDL Script for Procedure SD_HOSPITAL.SP_TSBZXMTBDY