CREATE OR REPLACE PROCEDURE SP_JY_DCJBR0(PSQRQTJ IN VARCHAR2, --申请日期条件
                                         PSXTJ00 IN VARCHAR2, --筛选条件
                                         PMZZYBZ IN VARCHAR2 --门诊住院标志  
                                         ) AS
  V_SQL000 VARCHAR2(8000);
  V_SFSXJK XT_XTCS00.VALUE0%TYPE;
  V_SFXSTM XT_XTCS00.VALUE0%TYPE;
  V_CJPXFS XT_XTCS00.VALUE0%TYPE;
  --Person              date            Comments
  --wusd                2020.05.06      待采集病人查询 for LIS-20200424-001
BEGIN
  DELETE JY_DCJBR0_TMP;
  DELETE JY_DCJBR0;
  --检验标本采集是否启用沙县体检接口，Y：是，N：不是；默认：N
  BEGIN
    SELECT TRIM(VALUE0)
      INTO V_SFSXJK
      FROM XT_XTCS00
     WHERE NAME00 = 'JY_BBCJSFQYSXJK';
  EXCEPTION
    WHEN OTHERS THEN
      V_SFSXJK := 'N';
  END;
  --检验条码前缀未维护是否显示，Y显示，N不显示，默认Y
  BEGIN
    SELECT TRIM(VALUE0)
      INTO V_CJPXFS
      FROM XT_XTCS00
     WHERE NAME00 = 'JY_TMQZWWHSFXS';
  EXCEPTION
    WHEN OTHERS THEN
      V_SFXSTM := 'N';
  END;
  IF V_SFXSTM <> 'Y' THEN
    V_SFXSTM := ' and nvl(TXMQZ0,''0'')<>''0'' ';
  END IF;
  --检验标本采集病人排序方式
  BEGIN
    SELECT TRIM(VALUE0)
      INTO V_SFSXJK
      FROM XT_XTCS00
     WHERE NAME00 = 'JY_JYBBCJBRPXFS';
  EXCEPTION
    WHEN OTHERS THEN
      V_CJPXFS := '0';
  END;
  V_SQL000 := ' insert into JY_DCJBR0_TMP(GHHID0,MZZYBZ,YEXM00)
                select distinct ZYGHID,MZZYBZ,YEXM00 
                from VW_JY_YW0000 J 
                where BRZT00=''2'' ' || PSQRQTJ || PSXTJ00;
  SP_EXECUTE_SQL(V_SQL000);
  IF (TRIM(V_SFSXJK) = 'Y') AND (PMZZYBZ = '0') THEN
    V_SQL000 := ' insert into JY_DCJBR0_TMP(GHHID0,MZZYBZ,YEXM00)
                select distinct 0,MZZYBZ,YEXM00
                from JY_YW0000 J
                where JJBZ00=''ggtj'' ' || PSQRQTJ ||
                PSXTJ00;
    SP_EXECUTE_SQL(V_SQL000);
  END IF;
  --V_SQL000:=' update JY_DCJBR0_TMP T
  --            set WCJ000=(select count(1) from VW_JY_BBCJXX 
  --                        where ZYGHID=T.GHHID0 
  --                          and nvl(YEXM00,''0'')=nvl(T.YEXM00,''0'') 
  --                          and MZZYBZ=T.MZZYBZ '||PSQRQTJ||V_SFXSTM||'
  --                          and JGZT00=''0'') ';                              
  V_SQL000 := ' update JY_DCJBR0_TMP T
              set WCJ000=(select count(1) from JY_YW0000 J
                          where JGZT00=''0'' and ZLXMID<>99999999
                          and ZYGHID=T.GHHID0 
                          and nvl(YEXM00,''0'')=nvl(T.YEXM00,''0'') 
                          and MZZYBZ=T.MZZYBZ ' || PSQRQTJ ||
              V_SFXSTM ||
              '    
                          and ((not exists(select 1 from YJ_YW0000 where CXDJH0=J.YJDJH0 and ZYGHID=J.ZYGHID)
                                and exists(select 1 from YJ_YW0000 where YJDJH0=J.YJDJH0 and (nvl(CXDJH0,0)=0 or SL0000>0))) 
                               or JJBZ00=''GGTJ''))';
  SP_EXECUTE_SQL(V_SQL000);
  V_SQL000 := ' update JY_DCJBR0_TMP T
              set YCJ000=(select count(1) from JY_YW0000 
                          where ZYGHID=T.GHHID0 
                            and nvl(YEXM00,''0'')=nvl(T.YEXM00,''0'')                             
                            and MZZYBZ=T.MZZYBZ ' || PSQRQTJ || '
                            and JGZT00 not in(''0'',''3'',''7'')) ';
  SP_EXECUTE_SQL(V_SQL000);
  V_SQL000 := ' update JY_DCJBR0_TMP T
              set BTH000=(select count(1) from JY_YW0000 
                          where ZYGHID=T.GHHID0 
                            and nvl(YEXM00,''0'')=nvl(T.YEXM00,''0'')                             
                            and MZZYBZ=T.MZZYBZ ' || PSQRQTJ || '
                            and JGZT00=''3'') ';
  SP_EXECUTE_SQL(V_SQL000);
  V_SQL000 := ' update JY_DCJBR0_TMP T
              set JZ0000=(select count(1) from JY_YW0000 
                          where ZYGHID=T.GHHID0 
                            and nvl(YEXM00,''0'')=nvl(T.YEXM00,''0'')                             
                            and MZZYBZ=T.MZZYBZ ' || PSQRQTJ || '
                            and YXBZ00=''Y'') ';
  SP_EXECUTE_SQL(V_SQL000);
  IF PMZZYBZ = '1' THEN
    INSERT INTO JY_DCJBR0
      (GHHID0,
       GHH000,
       BRID00,
       BRXM00,
       YEXM00,
       MZZYBZ,
       CH0000,
       WCJ000,
       YCJ000,
       BTH000,
       JZ0000)
      SELECT DISTINCT T.GHHID0,
                      B.ZYH000,
                      B.BRID00,
                      B.XM0000,
                      T.YEXM00,
                      T.MZZYBZ,
                      B.RYCWH0,
                      DECODE(WCJ000, 0, '', TO_CHAR(WCJ000)) WCJ000,
                      DECODE(YCJ000, 0, '', TO_CHAR(YCJ000)) YCJ000,
                      DECODE(BTH000, 0, '', TO_CHAR(BTH000)) BTH000,
                      DECODE(JZ0000, 0, '', TO_CHAR(JZ0000)) JZ0000
        FROM JY_DCJBR0_TMP T, ZY_BRXXB0 B
       WHERE T.GHHID0 = B.ZYID00
       ORDER BY LPAD(TRIM(B.RYCWH0), 12, '0');
  ELSE
    IF V_CJPXFS = '1' THEN
      INSERT INTO JY_DCJBR0
        (GHHID0,
         GHH000,
         BRID00,
         BRXM00,
         YEXM00,
         MZZYBZ,
         CH0000,
         WCJ000,
         YCJ000,
         BTH000,
         JZ0000)
        SELECT DISTINCT T.GHHID0,
                        B.GHH000,
                        B.BRID00,
                        B.XM0000,
                        T.YEXM00,
                        T.MZZYBZ,
                        '',
                        DECODE(WCJ000, 0, '', TO_CHAR(WCJ000)) WCJ000,
                        DECODE(YCJ000, 0, '', TO_CHAR(YCJ000)) YCJ000,
                        DECODE(BTH000, 0, '', TO_CHAR(BTH000)) BTH000,
                        DECODE(JZ0000, 0, '', TO_CHAR(JZ0000)) JZ0000
          FROM JY_DCJBR0_TMP T, SF_BRXXB0 B
         WHERE T.GHHID0 = B.GHID00
         ORDER BY NVL(WCJ000, '0') DESC;
    ELSE
      INSERT INTO JY_DCJBR0
        (GHHID0,
         GHH000,
         BRID00,
         BRXM00,
         YEXM00,
         MZZYBZ,
         CH0000,
         WCJ000,
         YCJ000,
         BTH000,
         JZ0000)
        SELECT DISTINCT T.GHHID0,
                        B.GHH000,
                        B.BRID00,
                        B.XM0000,
                        T.YEXM00,
                        T.MZZYBZ,
                        '',
                        DECODE(WCJ000, 0, '', TO_CHAR(WCJ000)) WCJ000,
                        DECODE(YCJ000, 0, '', TO_CHAR(YCJ000)) YCJ000,
                        DECODE(BTH000, 0, '', TO_CHAR(BTH000)) BTH000,
                        DECODE(JZ0000, 0, '', TO_CHAR(JZ0000)) JZ0000
          FROM JY_DCJBR0_TMP T, SF_BRXXB0 B
         WHERE T.GHHID0 = B.GHID00;
    END IF;
  END IF;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20011,
                            SUBSTR(SQLERRM || '执行过程SP_JY_DCJBR0！*',
                                   1,
                                   150));
END;
/