martri

BEGIN
FOR x IN
(SELECT
     ROWNUM R,
     HSE_UNIT_KEY ,
     HSE_BLK_KEY,
     HSE_UNIT_CODE_ADDR,
     CUST_MBR_ENG_NAME,
     CUST_MBR_RLTN_CODE,
     ICTYPE || MIDCHARACTER || ICNUMBER RPT_ID_TYPE_NUM,
     RPT_RPTNG_DATE
FROM
(SELECT
     UNIT.HSE_UNIT_KEY,
     UNIT.HSE_BLK_KEY,
     MBR.CUST_MBR_ID_NUM HSE_RSDN_MBR_ID_NUM,
     HSE_UNIT_CODE_ADDR,
     MBR.CUST_MBR_RLTN_CODE,
     MBR.CUST_MBR_ID_TYPE_CODE ICTYPE,
       (CASE
         WHEN (ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) >= ASCII('0') AND
              ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) <= ASCII('9')) THEN
          (SUBSTR(MBR.CUST_MBR_ID_NUM, 1, 1))
         ELSE
          (SUBSTR(MBR.CUST_MBR_ID_NUM, 1, 2))
       END) MIDCHARACTER,
       (DECODE(MBR.CUST_MBR_ID_TYPE_CODE,
               'IC',
       (CASE
         WHEN (ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) >= ASCII('0') AND
              ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) <= ASCII('9')) THEN
          (SUBSTR(SUBSTR(MBR.CUST_MBR_ID_NUM,
                         2,
                         LENGTH(MBR.CUST_MBR_ID_NUM)),
                  1,
                  LENGTH(SUBSTR(MBR.CUST_MBR_ID_NUM,
                                2,
                                LENGTH(MBR.CUST_MBR_ID_NUM))) - 1) || '(' ||
          SUBSTR(SUBSTR(MBR.CUST_MBR_ID_NUM,
                         2,
                         LENGTH(MBR.CUST_MBR_ID_NUM)),
                  LENGTH(SUBSTR(MBR.CUST_MBR_ID_NUM,
                                2,
                                LENGTH(MBR.CUST_MBR_ID_NUM))),
                  1) || ')')
         ELSE
          (SUBSTR(SUBSTR(MBR.CUST_MBR_ID_NUM,
                         3,
                         LENGTH(MBR.CUST_MBR_ID_NUM)),
                  1,
                  LENGTH(SUBSTR(MBR.CUST_MBR_ID_NUM,
                                3,
                                LENGTH(MBR.CUST_MBR_ID_NUM))) - 1) || '(' ||
          SUBSTR(SUBSTR(MBR.CUST_MBR_ID_NUM,
                         3,
                         LENGTH(MBR.CUST_MBR_ID_NUM)),
                  LENGTH(SUBSTR(MBR.CUST_MBR_ID_NUM,
                                3,
                                LENGTH(MBR.CUST_MBR_ID_NUM))),
                  1) || ')')
       END),
       (CASE
                 WHEN (ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) >= ASCII('0') AND
                      ASCII(SUBSTR(MBR.CUST_MBR_ID_NUM, 2, 1)) <= ASCII('9')) THEN
                  (SUBSTR(MBR.CUST_MBR_ID_NUM, 2, LENGTH(MBR.CUST_MBR_ID_NUM)))
                 ELSE
                  (SUBSTR(MBR.CUST_MBR_ID_NUM, 3, LENGTH(MBR.CUST_MBR_ID_NUM)))
               END))) ICNUMBER,
     MBR.CUST_MBR_ENG_NAME,
     MBR.CUST_MBR_ID_NUM  RPT_ID_TYE_NUM,
     SYSDATE RPT_RPTNG_DATE,
     CUST.CUST_FMLY_SIZE_NUM,
     COUNT(*) OVER (PARTITION BY UNIT.HSE_UNIT_KEY) NO_OF_MEMBER
FROM
     HSM_HSE_UNIT           UNIT,
     TAM_TNCY_AGRMT         AGRMT,
     CPM_CUST_APLY          CUST,
     CPM_CUST_APLY_MBR      MBR,
     SRP_IH_HSHLD_APLY      TEMP
WHERE AGRMT.HSE_UNIT_KEY = UNIT.HSE_UNIT_KEY
      AND
      CUST.CUST_KEY=AGRMT.CUST_KEY
      AND
      CUST.HSE_SRVC_APLY_KEY=AGRMT.HSE_SRVC_APLY_KEY
      AND
      MBR.CUST_KEY=AGRMT.CUST_KEY
      AND
      MBR.HSE_SRVC_APLY_KEY=AGRMT.HSE_SRVC_APLY_KEY
      AND
      TEMP.HSE_BNFT_MBR_ID_TYPE_CODE=MBR.CUST_MBR_ID_TYPE_CODE
      AND
      TEMP.HSE_BNFT_MBR_ID_NUM=MBR.CUST_MBR_ID_NUM
      AND
      MBR.LAST_REC_TXN_TYPE_CODE<>'D'
      AND
      CUST.LAST_REC_TXN_TYPE_CODE<>'D'
      AND
      AGRMT.LAST_REC_TXN_TYPE_CODE<>'D'
      AND
      UNIT.LAST_REC_TXN_TYPE_CODE<>'D'
      AND
      TEMP.RPT_RECORD_TYPE='1A'
      AND
      AGRMT.TNCY_AGRMT_TM_STS_CODE = 'A'
      AND
      TEMP.HSE_BNFT_TYPE_CODE='T') TEMP_MBR
WHERE
      TEMP_MBR.CUST_FMLY_SIZE_NUM>TEMP_MBR.NO_OF_MEMBER
      )
LOOP
  EXECUTE IMMEDIATE
  'INSERT INTO SRP_IH_WTHT_GWL_APP(
            HSE_UNIT_KEY,
            HSE_BLK_KEY,
            HSE_UNIT_CODE_ADDR,
            CUST_MBR_RLTN_CODE,
            RPT_ID_TYPE_NUM,
            RPT_RPTNG_DATE)
  VALUES(
           :HSE_UNIT_KEY,
           :HSE_BLK_KEY,
           :HSE_UNIT_CODE_ADDR,
           :CUST_MBR_RLTN_CODE,
           :RPT_ID_TYPE_NUM,
           :RPT_RPTNG_DATE
           )'
    using
            x.HSE_UNIT_KEY,
            x.HSE_BLK_KEY,
        x.HSE_UNIT_CODE_ADDR,
       x.CUST_MBR_RLTN_CODE,
            x.RPT_ID_TYPE_NUM,
            x.RPT_RPTNG_DATE;
    IF ((x.R mod 100)=0) THEN
   COMMIT;
  END IF;
END LOOP;
END;