SQL_INSERT_RL_INFO_AREA CONSTANT varchar2(1000) := 'insert into temp_addr_new ( ROW_NUMBER, GEOGRAPHY_ID, COMMUNITY_ID, DESCRIPTION, NAME, COMMUNITY_NAME, AREA_ID, BUSINESS_TYPE_ID, '
                                                  || 'USER_TYPE_ID, MANAGED_CODE ) '
                                                  || 'select * from (select rownum r_id, rl.geography_loc_id , c.community_id , rl.description , rl.name rname, c.name cname,  rl.area_id , rl.business_type_id , '
                                                  || 'rl.user_type_id , rl.manage_code '
                                                  || 'from regional_loc rl,regional_loc_2_community rl2c,community c '
                                                  || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 '
                                                  || 'and c.status = :1 '
                                                  || 'and rl.description like :2 and c.name like :3 '
                                                  || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 '
                                                  || 'and rl.user_type_id in ( :6 ,3) '
                                                  || 'and c.rsc_spec_id <> :7 '
                                                  || 'and rl.area_id = :8 and rownum <= :9 ) where r_id >= :10 ';

  SQL_SELECT_RL_COUNT_AREA CONSTANT varchar2(1000) := 'select count(1) from regional_loc rl,regional_loc_2_community rl2c,community c '
                                                  || 'where rl.geography_loc_id = rl2c.geography_loc_id and rl2c.community_id = c.community_id and rl.display_type_cd = 1 '
                                                  || 'and c.status = :1 '
                                                  || 'and rl.description like :2 and c.name like :3 '
                                                  || 'and rl.regional_simple_spell like :4 and c.community_simple_spell like :5 '
                                                  || 'and rl.user_type_id in ( :6 ,3) '
                                                  || 'and c.rsc_spec_id <> :7 '
                                                  || 'and rl.area_id = :8 ';

DECLARE
  IN_COMMUNITY_NAME VARCHAR2(200);
  IN_STANDARD_ADDR_NAME VARCHAR2(200);
  IN_PROD_SPEC_ID NUMBER;
  IN_BUSINESS_TYPE VARCHAR2(200);
  IN_USER_TYPE NUMBER;
  IN_AREA_ID NUMBER;
  IN_CONDITION_TYPE VARCHAR2(200);
  IN_BEGIN_ROW NUMBER;
  IN_END_ROW NUMBER;
  OUT_ATTRS PG_RMS_FOR_CRM_NEW.c_list;
  OUT_ROW_COUNT NUMBER;
  OUT_RET_CODE VARCHAR2(200);
  OUT_RET_INFO VARCHAR2(200);
  
  v_gpon_not_rsc_spec number;
  v_start number;
  v_rl_chinese varchar2(400);
  v_c_chinese varchar2(400);
  v_rl_simple_spell varchar2(400);
  v_c_simple_spell varchar2(400);
  v_community_state number;
BEGIN
  v_start := dbms_utility.get_time;
  IN_COMMUNITY_NAME := '永翔佳苑';
  IN_STANDARD_ADDR_NAME := '';
  IN_PROD_SPEC_ID := '';
  IN_BUSINESS_TYPE := 'Broadband';
  IN_USER_TYPE := 1;
  IN_AREA_ID := 24;
  IN_CONDITION_TYPE := 0;
  IN_BEGIN_ROW := 1;
  IN_END_ROW := 10;
  v_gpon_not_rsc_spec := 10102008;
  
  v_rl_chinese := '%' || IN_STANDARD_ADDR_NAME || '%';
  v_c_chinese := '%' || IN_COMMUNITY_NAME || '%';
  v_rl_simple_spell := '%%';
  v_c_simple_spell := '%%';
  v_community_state := 4;

  EXECUTE IMMEDIATE PG_RMS_FOR_CRM_NEW.SQL_SELECT_RL_COUNT_AREA
    INTO O