SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr       = g.cardnbr
   AND '87' || p.num   = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = 'TEL';

cardnbr        Co_id
------------     ------------
87654327     980
87897687     876
87654309     123
.....

MSNNUM ;CO_ID

-------------------
87654327; 980
87897687; 876
87654309; 123
.....
...
...

set heading off
set pages 0
 
set colsep ";"
 
spool balance.csv
 
select 'MSNNUM ;CO_ID' from dual;
select '-----------------------' from dual;
 
SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr       = g.cardnbr
   AND '87' || p.num   = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = "TEL';
 
spool off

rules_id                           Description
-----------------------------------------------------------
SELECT c.cardnbr, co_id
  FROM tmp.ccard__gen g,
       tmp.ccard_tmp  c,     
       dsn.contract_phonenr p
 WHERE flag         = 'P'
   AND c.cardnbr    = g.cardnbr
   AND '87' || p.num = c.cardnbr
   AND c.amtbal > 0
   AND c.pprofil = "TEL';              
                               
(this is the description)

With this query, all SEF prepaid 
card with a profile Migrate to Postpaid and still remain some credits in the cards will be generated with the contract_id from SCOTT.  (DESCRIPTION)
                                

SELECT dn_num, l.lng_shdes
  FROM sc.ccontact_all cc,
           sc.customer_all cu,
           sc.LANGUAGE l,
           sc.contr_services_cap cos_c,
           sc.contr_services COS,
           sc.directory_number dn,
           sc.contract_all ca
 WHERE COS.co_id      = cos_c.co_id
   AND cos_c.dn_id    = dn.dn_id
   AND COS.co_id      = ca.co_id
   AND ca.customer_id = cu.customer_id
   AND cc.customer_id = ca.customer_id
   AND COS.scode     = 1                
   AND COS.cs_stat_chng LIKE ('%a')
   AND LENGTH (COS.cs_stat_chng) = 7
   AND TO_DATE (SUBSTR (COS.cs_stat_chng, 1, 6), 'YYMMDD')
          BETWEEN (SELECT TO_DATE (txt, 'YYYYMMDD')
                     FROM tmp.SRM
                    WHERE srm_id_d = 'ACTIVE_DATE'
                      AND srm_id_h = 'ASSURE')
                      AND TRUNC (SYSDATE - 1)
   AND cu.pcode IN (10, 20, 30)         
   AND cc.ccbill = 'X'
   AND cos_c.code = 1
   AND cos_c.cs_deactiv_date IS NULL
   AND l.lng_id = cc.cclanguage;

UPDATE SRM set srm_id_h = 'ASSURE' where  srm_id_d = 'ACTIVE_DATE';

SELECT dn_num, l.lng_shdes
  FROM sc.ccontact_all cc,
           sc.customer_all cu,
           sc.LANGUAGE l,
           sc.contr_services_cap cos_c,
           sc.contr_services COS,
           sc.directory_number dn,
           sc.contract_all ca