NQY_UPDATE_PAP

CREATE OR REPLACE PROCEDURE IDEMPIERE.NQY_UPDATE_PAP(P_REGNO VARCHAR2,P_SDATE_B VARCHAR2, P_SDATE_E VARCHAR2)
AS
-- 乳攝檢查檔 更新 鑑驗檔 MAMO 值
-- EXECUTE IDEMPIERE.NQY_UPDATE_PAP ('00073449','1040826','1040826');
--   COMMIT 
-- select * from all_tab_cols where column_name='HOSRT' and length(table_name)=7
  v_key           varchar2(80);     
  v_result        varchar2(20);
  v_date          varchar2(20);
BEGIN 
  dbms_output.put_line('--------------------');
  --v_search_string := P_REGNO;
  -- INNER JOIN FOB003K c ON b.ORDER_NO=a.ORDER_NO  -- b.CASENO
  -- DELETE IDEMPIERE.NQY_PAP COMMIT
  -- SELECT * FROM  IDEMPIERE.NQY_PAP
 
  FOR a1 IN (
             SELECT
x.序號         ,-- VARCHAR2(40),
x.細胞病理編號 ,-- VARCHAR2(40),
x.個案姓名     ,-- VARCHAR2(40),
x.身份證字號   ,-- VARCHAR2(40),
x.出生日期     ,-- VARCHAR2(40),
SUBSTR(x.採檢日期,1,3)||SUBSTR(x.採檢日期,5,2)||SUBSTR(x.採檢日期,8,2) AS 採檢日期,-- VARCHAR2(40),
x.收到日期     ,-- VARCHAR2(40),
x.確診日期     ,-- VARCHAR2(40),
x.收到天數     ,-- VARCHAR2(40),
x.檢驗天數     ,-- VARCHAR2(40),
x.抹片品質     ,-- VARCHAR2(40),
x.尚可或難以判讀原因  ,--VARCHAR2(40),
x.可能感染     ,--VARCHAR2(40),
x.診斷結果     ,--VARCHAR2(40),
x.支付方式     ,--VARCHAR2(40)
y.REGNO
              FROM IDEMPIERE.NQY_PAP x  --M200161967
        INNER JOIN IPD.FTH008K       y ON TRIM(x.身份證字號)=y.IDNO
  )LOOP
    
     dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||'xxx');

     FOR a2 IN (
              SELECT  DISTINCT  b.ORDER_NO,
                      b.CASENO, C.REGNO, b.SDATE, B.OBDATE,  b.CODE --b.CODE_TYPE,
               FROM IPD.FOB003K b
         INNER JOIN IPD.FOB002K c ON C.CASENO=B.CASENO
              WHERE 1=1
                AND c.REGNO = a1.REGNO
                AND b.OBDATE= a1.採檢日期
                AND (SUBSTR(b.CODE,1,4)='PAPS' OR b.CODE='031' OR b.CODE='31')
                AND b.CANCEL_MARK IS NULL
             --   AND EXISTS(SELECT 1
             --                FROM IPD.FEX002K d
             --               WHERE d.CLASS = 'PAP'
             --                 AND(d.RESULT IS NULL OR d.RESULT='')  --- 還有空白的才做
             --                 AND d.PKEY_CASENO=b.CASENO )
           ORDER BY c.REGNO
     )LOOP           
      
       dbms_output.put_line(a1.REGNO||' : '||a1.採檢日期||' : '||a2.ORDER_NO||' : '||a2.CASENO||' : '||a2.REGNO||' : '||a2.CODE);
       v_result := '';
       IF    INSTR(a1.診斷結果,'Category 0:') > 0 THEN
             v_result := '0';
       ELSIF INSTR(a1.診斷結果,'Category 1:') > 0 THEN
             v_result := '1';
       ELSIF INSTR(a1.診斷結果,'Category 2:') > 0 THEN
             v_result := '2';
       ELSIF INSTR(a1.診斷結果,'Category 3:') > 0 THEN
             v_result := '3';
       ELSIF INSTR(a1.診斷結果,'Category 4:') > 0 THEN
             v_result := '4';
       ELSIF INSTR(a1.診斷結果,'Category 5:') > 0 THEN
             v_result := '5';
       END IF;
      
    --   COMMIT
       v_date := LTRIM(TO_CHAR(TO_NUMBER(TO_CHAR(  ADD_MONTHS(SYSDATE,-120)    ,'YYYYMMDD'),'99999999') -19110000,'9999999'));
       IF LENGTH(v_date) = 5 THEN
          v_date := '00'||v_date;
       ELSIF LENGTH(v_date) = 6 THEN
          v_date := '0'||v_date;
       END IF;
       /*
       UPDATE FEX002K d
          SET d.RESULT = v_result,
              MEDICAL_DIVISION = 'L006',
              REPORT_DATE = v_date,
              REPORT_TIME = NULL,
              REPORT_USRID= 'L006',
              CONFIRM_FLAG = 'Y',
              CONFIRM_DATE = v_date,
              CONFIRM_TIME = NULL,
              CONFIRM_USRID= 'L006'
        WHERE d.CLASS  = 'MAMO'
          AND(d.RESULT IS NULL OR d.RESULT = '')
          AND d.PKEY_CASENO=a1.CASENO;
      */   
     

     END LOOP;
  END LOOP;
END;
/