CREATE OR REPLACE FUNCTION SF_BA_GETBABRSSXX20
-- MODIFICATION HISTORY
  -- Person    Date        Comments
  -- xuxq    2019.05.10  Create BAGL-20190415-001
  --shienq    2020.07.20   数据取
(PZYID00 IN NUMBER, --
 PFHLX00 IN NUMBER, --返回类型 0手术操作编码 1手术操作日期 2手术级别 3手术操作名称 4手术操作部位
 PXH0000 IN NUMBER --5手术持续时间 6术者 7I助 8II助 9麻醉方式 10麻醉分级 11切口愈合等级 12麻醉医师
 ) RETURN VARCHAR2 IS
  VSSMC00 BA_BASSSB.SSMC00%TYPE; --按顺序号取诊断的编码，主要是ba_basssb.SSXH00这个字段的值不靠谱，导致传接口有的时候数据传不过去

BEGIN
  BEGIN
    IF PFHLX00 = 0 THEN
      --手术操作编码
      SELECT SSM000
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT NVL(XT.CM3001, SS.SSM000) SSM000
                        FROM BA_BASSSB SS
                        LEFT JOIN XT_CM3DYB XT
                          ON SS.SSM000 = TRIM(XT.CM3000)
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 1 THEN
      --手术操作日期
      SELECT (SUBSTR(SSRQ00, 1, 4) || '-' || SUBSTR(SSRQ00, 5, 2) || '-' ||
             SUBSTR(SSRQ00, 7, 2) || ' ' || SSSJ00) SSRQSJ
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT SSRQ00, SSSJ00
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 2 THEN
      --手术级别
      --  select SSZL00 into Vssmc00 from(select rownum XH0000,a.* from (select SSZL00 from ba_basssb where LSH000=PZYID00 order by SSXH00) A) where XH0000=Pxh0000;
      SELECT DECODE(SUBSTR(SSZL00, 1, 1), '一', '1', '二', '2', '三', '3', '四', '4')
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, SSZL00
                FROM BA_BASSSB
               WHERE LSH000 = PZYID00
               ORDER BY SSXH00)
       WHERE XH0000 = PXH0000;
    
    ELSIF PFHLX00 = 3 THEN
      --手术操作名称
      SELECT SSMC00
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT NVL(XT.SSMC01, SS.SSMC00) SSMC00
                        FROM BA_BASSSB SS
                        LEFT JOIN XT_CM3DYB XT
                          ON SS.SSM000 = TRIM(XT.CM3000)
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 6 THEN
      --术者
      SELECT SSYSMC
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT SSYSMC
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 7 THEN
      --I助
      SELECT IYSMC0
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT IYSMC0
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 8 THEN
      --II助
      SELECT NVL(IIYSMC, '-')
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT IIYSMC
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 9 THEN
      --麻醉方式
      SELECT DM0000
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT DM0000
                        FROM BA_ZDDY00 ZD, BA_BASSSB SS
                       WHERE LSH000 = PZYID00
                         AND ZD.ZDBM00 = 'MZFS20'
                         AND ZD.VALUE0 = SS.MZFS00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 11 THEN
      --切口愈合等级
      SELECT DECODE(QKYHDJ, '无切口', '0', 'Ⅰ甲', '1', 'Ⅰ乙', '2', 'Ⅰ丙', '3', 'Ⅱ甲', '4', 'Ⅱ乙', '5', 'Ⅱ丙', '6', 'Ⅲ甲', '7', 'Ⅲ乙', '8', 'Ⅲ丙', '9')
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT QKYHDJ
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    ELSIF PFHLX00 = 12 THEN
      --麻醉医师
      SELECT MZYSMC
        INTO VSSMC00
        FROM (SELECT ROWNUM XH0000, A.*
                FROM (SELECT MZYSMC
                        FROM BA_BASSSB
                       WHERE LSH000 = PZYID00
                       ORDER BY SSXH00) A)
       WHERE XH0000 = PXH0000;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      VSSMC00 := '-';
  END;
  RETURN VSSMC00;
END;
/