How to generate 18 digit code using cursor based on some conditions in Oracle

The way you put it, you'd join those tables somehow (cross join is as good as any other, as you didn't explain it better) and concatenate column values.

Something like this:

SQL> with
  2  tbl_rrsoc_store_info (state, store_code, zone_name) as
  3    (select 'AP', 'EAST', 'SMT' from dual union all
  4     select 'NY', 'WEST', 'XYZ' from dual
  5    ),
  6  tbl_site_store_format (format_code) as
  7    (select 4567 from dual)
  8  --
  9  select 'R' ||'-'|| r.state ||'-'|| r.store_code ||'-'|| r.zone_name ||'-'|| s.format_code result
 10  from tbl_rrsoc_store_info r cross join tbl_site_store_format s;

RESULT
--------------------
R-AP-EAST-SMT-4567
R-NY-WEST-XYZ-4567

SQL>

Function returns a value; you didn't explain how it should look like (which parameters it accepts) so I chose to pass state, presuming it is unique within the table.

Sample data:

SQL> select * From tbl_rrsoc_store_info;

ST STOR ZON
-- ---- ---
AP EAST SMT
NY WEST XYZ

SQL> select * from tbl_site_store_format;

FORMAT_CODE
-----------
       4567

Function:

SQL> create or replace function f_test (par_state in varchar2)
  2    return varchar2
  3  is
  4    retval varchar2(18);
  5  begin
  6    select 'R' ||'-'|| r.state ||'-'|| r.store_code ||'-'|| r.zone_name ||'-'|| s.format_code
  7      into retval
  8      from tbl_rrsoc_store_info r cross join tbl_site_store_format s
  9      where r.state = par_state;
 10
 11    return retval;
 12  end;
 13  /

Function created.

Testing:

SQL> select r.state, f_test(r.state) result
  2  from tbl_rrsoc_store_info r;

ST RESULT
-- --------------------
AP R-AP-EAST-SMT-4567
NY R-NY-WEST-XYZ-4567

SQL>