SELECT ORDER_NUMBER,

    ( CASE WHEN ( OOH.BOOKED_DATE IS NOT NULL AND ooh.ATTRIBUTE16 LIKE '20________%' )
        THEN  apps.SGDF_WORK_DAY( TO_DATE(
                                       CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
                                             THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
                                          WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
                                             THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
                                          WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
                                             THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
                                          ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00' 
                                      END  ,'YYYY-MM-DD HH24:MI:SS')
                                  ,to_date(ooh.ATTRIBUTE16 ,'YYYY-MM-DD HH24:MI:SS') )
                                 
                + ( CASE WHEN
                        TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
                            >= TO_CHAR( TO_DATE(
                                           CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
                                                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
                                              WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
                                                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
                                              WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
                                                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
                                              ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00' 
                                          END ,'YYYY-MM-DD HH24:MI:SS')
                                ,'HH24') 
                        THEN -1+ ROUND( ( TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
                                -TO_CHAR( TO_DATE(
                                             CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
                                                  WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
                                                  WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
                                                  ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00' 
                                              END ,'YYYY-MM-DD HH24:MI:SS' )
                                ,'HH24'))/8.0 ,2)
                     ELSE
                          ROUND( ( TO_CHAR(TO_DATE(OOH.ATTRIBUTE16,'YYYY-MM-DD HH24:MI:SS'),'HH24')
                              -  TO_CHAR( TO_DATE(
                                             CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'09:30:00'
                                                  WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'13:00:00'
                                                  WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
                                                     THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD') ||' '||'16:30:00'
                                                  ELSE    TO_CHAR(OOH.BOOKED_DATE + 1,'YYYY-MM-DD')||' '||'09:30:00' 
                                              END ,'YYYY-MM-DD HH24:MI:SS' )
                                  ,'HH24')  )/8.0 ,2)
                      END 
                  )                  
     ELSE NULL
   END ) AS CT3_系統至回單日,  --生管回單-系統發送時間
  


   (CASE WHEN OOH.BOOKED_DATE IS NOT NULL THEN
       to_date(
         CASE WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '00:00:00'AND '09:30:00'
                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'09:30:00'
              WHEN TO_CHAR(OOH.BOOKED_DATE, 'hh24:mi:ss') BETWEEN '09:30:00' AND '13:00:00'
                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'13:00:00'
              WHEN TO_CHAR(OOH.BOOKED_DATE ,'hh24:mi:ss') BETWEEN '13:00:00'AND '16:30:00'
                 THEN TO_CHAR(OOH.BOOKED_DATE ,'YYYY-MM-DD')||' '||'16:30:00'
              ELSE    TO_CHAR(OOH.BOOKED_DATE +1,'YYYY-MM-DD')||' '||'09:30:00' 
          END ,'YYYY-MM-DD HH24:MI:SS')
       ELSE NULL
    END )  SEND_DATE,  

 OOH.CREATION_DATE, OOH.BOOKED_DATE, ooh.ATTRIBUTE16 REPLY_DATE

 FROM ONT.OE_ORDER_HEADERS_ALL OOH
 WHERE OOH.ORG_ID IN (400,420)
   AND NVL(OOH.CANCELLED_FLAG,'N')='N'
  -- and OOH.ORDER_NUMBER NOT IN ('601000164','601000152','601000149')
  -- AND OOH.ORDER_NUMBER LIKE  '60100%'  -- '60100230_'  --'601001550'