Oracle SQL Scripts - jkossi/Bss_Support GitHub Wiki

Updates Status of a Physical Resource

-- Cases where sim number is spared in Physical resources but the requester is still complaining
-- F means freed
select * from gsm_sims_master where sim_num_v in ('8923301004485771003')
update gsm_sims_master set STATUS_V = 'F' where sim_num_v in ('8923301004506250193');

Uploading Serials to DRM

-- For uploadsing Serials to DRM
-- , STATUS_V, PIN1_NUM_N PIN1
select SIM_CATEGORY_CODE_V, IMSI_NUM_N IMSI, SIM_NUM_V ICCID, PUK1_NUM_V PUK1, KI_NUM_V KI, PIN2_NUM_N PIN2, PUK2_NUM_V PUK2, STATUS_V
from gsm_sims_master where --(sim_num_v between    '8923301001010844776' and '8923301001010845765' );
--or sim_num_v between '8923301001012690763' and '8923301001012689765') ;
    SIM_NUM_V in
('8923301001011543120');

Verify the MSISDN category of a number when sparing number to a category

-- Verify the MSISDN category of a number when sparaing number to a category
-- Waiting on George
select * from gsm_mobile_master where mobile_number_v in ('532739795');
update gsm_mobile_master set CATEGORY_CODE_V ='NOSN' Where mobile_number_v in ('308041019','308040133','308040122');

Expanding Serials

-- Expanding Serials
select SIM_CATEGORY_CODE_V, IMSI_NUM_N IMSI, SIM_NUM_V ICCID, PUK1_NUM_V PUK1, KI_NUM_V KI, PIN2_NUM_N PIN2, PUK2_NUM_V PUK2, STATUS_V, PIN1_NUM_N PIN1
from gsm_sims_master 
where SIM_NUM_V in ('8923301001011543120');

Starter Pack Checks

-- Starter pack checks
-- R means Rejected
select STARTER_PACK_SERIAL_NUM_V, MOBILE_NUMBER_V, SIM_NUMBER_V, PROV_RESPONSE_V,STATUS_V 
from GSM_STARTER_PACK_DTLS 
where mobile_number_v 
in ('597661842');

Confirm if number is in CBS (Oracle DB)

-- GSM Service master check
-- We use when a number is not showing on DCLM
select Mobl_num_voice_v, gsm_service_mast.* 
from gsm_service_mast
where Mobl_num_voice_v = '531003174';

Get Kit Number(KI) using IMSI

-- Use imsiNumber
select SIM_CATEGORY_CODE_V, IMSI_NUM_N IMSI, SIM_NUM_V ICCID, PUK1_NUM_V PUK1, KI_NUM_V KI, PIN2_NUM_N PIN2, PUK2_NUM_V PUK2, STATUS_V, PIN1_NUM_N PIN1
from gsm_sims_master where --(sim_num_v between    '8923301001010844776' and '8923301001010845765' );
--or sim_num_v between '8923301001012690763' and '8923301001012689765') ;
IMSI_NUM_N in
('620010444496926');

Get Kit Number(KI) using Sim Number

-- USe sim  number
select SIM_CATEGORY_CODE_V, IMSI_NUM_N IMSI, SIM_NUM_V ICCID, PUK1_NUM_V PUK1, KI_NUM_V KI, PIN2_NUM_N PIN2, PUK2_NUM_V PUK2, STATUS_V, PIN1_NUM_N PIN1
from gsm_sims_master where --(sim_num_v between    '8923301001010844776' and '8923301001010845765' );
--or sim_num_v between '8923301001012690763' and '8923301001012689765') ;
SIM_NUM_V in
('8923301004468128692');

Verify Number in CBS (Oracle DB)

-- Search for number in GSM service Mast
select Mobl_num_voice_v, gsm_service_mast.* from gsm_service_mast where Mobl_num_voice_v = '530497714';

NIA Verification

-- Verify NIA Identification
select * from  ABL_TBL_GHA.nia_card_verify_dtls where MSISDN = '244511287';

Others

update gsm_mobile_master
set status_v = 'Q',category_code_v= 'NORP'
where mobile_number_v in (select * from tmp_msisdn_termination)
and status_v in ('1','2','F')
and (MOBILE_NUMBER_V like '24%'
or MOBILE_NUMBER_V like '54%'
or MOBILE_NUMBER_V like '554%'
or MOBILE_NUMBER_V like '553%'
or MOBILE_NUMBER_V like '555%'
or MOBILE_NUMBER_V like '556%'
or MOBILE_NUMBER_V like '557%'
or MOBILE_NUMBER_V like '558%')

commit;


update gsm_mobile_master
set status_v = 'B',category_code_v= 'NORP'
where mobile_number_v in (select mobile_number_v from TMP_RESERVERD_NUMBER_V04 where mobile_number_v in (select * from tmp_msisdn_termination))
and status_v in ('Q','F')

commit;