SET ECHO ON
SET LINESIZE 200

WHENEVER OSERROR EXIT 8
WHENEVER SQLERROR EXIT 8

UPDATE ZE_T_RDSSVKCKIF A
   SET (A.MANSION_ID,
        A.PID,
        A.CATID,
        A.UPD_UNYO_YMD,
        A.UPD_TRN_ID) = 
      ((SELECT Q.MANSION_ID,
                Y.PID,
                Q.CATID,
                '&1',
                'UPDATE_ZE_T_RDSCKIF_02'
           FROM  (((SELECT KK0081_RANK.SVC_KEI_NO, KK0081_RANK.TK_HOSHIKI_KEI_NO
                   FROM (SELECT KK0081_GENE.SVC_KEI_NO,
                                KK0081_GENE.TK_HOSHIKI_KEI_NO,
                                RANK() OVER(PARTITION BY KK0081_GENE.SVC_KEI_NO 
                                                ORDER BY (KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) DESC) AS KK0081_MAX
                           FROM KK_T_SVC_KEI KK0081_GENE
                          WHERE KK0081_GENE.RSV_APLY_YMD <= '&1'
                            AND KK0081_GENE.RSV_APLY_CD = '2'
                            AND KK0081_GENE.MK_FLG = '0')KK0081_RANK
                  WHERE   KK0081_RANK.KK0081_MAX = 1) N
                  INNER JOIN KK_T_TK_HOSHIKI_KEI M ON N.TK_HOSHIKI_KEI_NO = M.TK_HOSHIKI_KEI_NO
                                                  AND M.MK_FLG = '0') 
                  INNER JOIN KK_T_KAISEN_USE_KEI Q ON M.KAISEN_USE_KEI_NO = Q.KAISEN_USE_KEI_NO
                                                  AND Q.MK_FLG = '0')
                  INNER JOIN KK_T_OWNR_KEI Y ON Q.OWNR_KEI_NO = Y.OWNR_KEI_NO
                                            AND Y.MK_FLG = '0'
          WHERE A.SVC_KEI_NO = N.SVC_KEI_NO))
   WHERE EXISTS (SELECT 1 FROM 
                   (SELECT KK0081_GENE.SVC_KEI_NO, 
                           KK0081_GENE.TK_HOSHIKI_KEI_NO,
                           RANK() OVER(PARTITION BY KK0081_GENE.SVC_KEI_NO
                                       ORDER BY (KK0081_GENE.RSV_APLY_YMD || KK0081_GENE.GENE_ADD_DTM) DESC) AS KK0081_MAX
                    FROM KK_T_SVC_KEI KK0081_GENE
                    WHERE KK0081_GENE.RSV_APLY_YMD <= '&1'
                      AND KK0081_GENE.RSV_APLY_CD = '2'
                      AND KK0081_GENE.MK_FLG = '0') Y 
                    INNER JOIN KK_T_TK_HOSHIKI_KEI M ON Y.TK_HOSHIKI_KEI_NO = M.TK_HOSHIKI_KEI_NO 
                                                    AND M.MK_FLG = '0'
                  WHERE A.SVC_KEI_NO = Y.SVC_KEI_NO) ;

COMMIT ;

EXIT 0

quit