CREATE OR REPLACE PROCEDURE cvbsreports.ss_test1
AS
BEGIN
   DECLARE
      v_prov   VARCHAR2 (100);
      v_date   VARCHAR2 (8);
      v_sql1   VARCHAR2 (5000);
   BEGIN
      v_date := TO_CHAR (SYSDATE - 1, 'yyyymmdd');
      v_sql1 :=
            '
   begin

   FOR i in (select * from ss_points_'
         || v_date
         || ' where province is null  ) 
   LOOP
    
         SELECT UPPER (z.area_name) 
           INTO v_prov
           FROM bfm_area@link_cc z
          WHERE z.parent_id = 1
            AND z.area_id =
                   (SELECT k.parent_id
                      FROM bfm_area@link_cc k,
                           subs@link_cc s,
                           prod@link_cc p,
                           cust@link_cc c
                     WHERE s.subs_id = p.prod_id
                       AND s.cust_id = c.cust_id
                       AND p.prod_state IN (''A'', ''D'', ''E'', ''G'')
                       AND k.area_id = c.area_id
                       AND s.acc_nbr = i.msisdn);
      

      UPDATE  ss_points_'
         || v_date
         || ' x
         SET x.province = v_prov
       WHERE x.province IS NULL AND x.msisdn = i.msisdn;
   END LOOP;

   
END';

      EXECUTE IMMEDIATE (v_sql1);
   END;
END ss_test1;
/