|
Actually i am calling a postgres function by using c language which description is defined in database side .when iam calling a postgres function by passing input and output
parameters after excution of function in database side i need output data in correcsponding output parameters but iam not getting output like that. i am getting total output data in response pointer.if iam getting total data in response pointer it is hard
to figure out which data is for which output parameter. can you please check once attached two text files.
Thanks & Regards,
Mahesh Bodepati Software Engineer-
Engineering
XIUS
INNOVATE . LEAD.
From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Sent: Tuesday, October 27, 2020 8:09 PM To: Mahesh Bodepati <mahesh.bodepati@xxxxxxxx>; pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx> Subject: Re: facing problem in outparameters in c On Mon, 2020-10-26 at 10:45 +0000, Mahesh Bodepati wrote:
> i am facing the problem in passing the outparameters in postgres package calling by using c language There are no "packages" in PostgreSQL. Can you clarify your question? How is the function or procedure you are calling defined? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com |
CREATE OR REPLACE FUNCTION smp_ss7_mig_dev.pr_pkg_oss_networkserver_pr_sp_apply_sl_yn(
IN pi_imsi character varying,
IN pi_hlr_address character varying,
IN pi_vlr character varying,
IN pi_message integer,
IN pi_msg_type integer,
OUT allow_yn integer,
OUT po_ns_relay_reason integer,
OUT po_sl_relay_reason character varying,
OUT po_planname character varying,
OUT po_plantype character varying,
OUT prefflag integer,
OUT po_list_priority integer,
OUT po_opcode integer,
OUT po_attemptcount integer,
OUT po_prev_vlr character varying,
OUT po_map_version integer,
OUT po_clg_address character varying,
OUT po_errorcode integer,
OUT po_error_code integer,
OUT po_gprs_log integer,
OUT po_list_id character varying,
OUT po_network_name character varying,
OUT po_timer_s_yn integer,
OUT po_context_yn integer,
OUT po_hlr_error_code integer,
OUT po_invoke_id integer)
RETURNS record AS
$BODY$
DECLARE
/*
Description of out parameter "po_NS_relay_reason"
1. Relay due to imsi being present in the Incompatible list
2. Relay due to imsi being present in the active user list
3. Relay due to imsi being non-whitelisted
4. Relay due to imsi being present in the Black list
5. Relay due to max country reject limit being reached if extended context is applicable.
6. Plan not Found for the imsi list
7. SOR is disbaled
8. VLR is Barred
9. Relay due to Roaming not allowed on second occurrence in Ratio plan
10. Relay due to Policy management. Policy management return 0 if the transaction is allowed(meaning no service
11. Relay due to SOR disabled at country level or network level
logic to be applied) else return 1.
PI_MESSAGE = 0 : UPDATE LOCATION MESSAGE
PI_MESSAGE = 1 : ISD MESSAGE
*/
hlrwhite VARCHAR(1);
hlrcount Integer := 0;
imsicount Integer := 0;
reccount Integer := 0;
vlrcount Integer := 0;
visitccndc VARCHAR;
plantype VARCHAR (1);
cc VARCHAR (5);
ndc INTEGER;
-- NDC variable length increased from 10 to 12 due to GTSR947,GTSR1159.
home_network INTEGER := 0;
imsinumber VARCHAR;
vlraddress VARCHAR;
errorcode INTEGER;
max_imsi_reject INTEGER;
max_country_reject INTEGER;
listcount INTEGER := 0;
rnacount INTEGER := 0;
present_ctry_count INTEGER := 0;
ctrypresent INTEGER := 0;
currenttime TIMESTAMP;
opcodeexist INTEGER;
opcodepresent INTEGER := 0;
rnapresent INTEGER := 0;
-- START of code by vani for adding BLACK LIST check rel: 2.0.2.10
nb_count_black_imsi INTEGER := 0;
-- END of code by vani for adding BLACK LIST check rel: 2.0.2.10
planid INTEGER;
planfoundflag INTEGER := 0;
activetimer INTEGER;
actcount INTEGER;
nb_context_expiry_time INTEGER;
--CODE WRITTEN BY BALAKRISHNA FOR SOR AND BARRED NETWORK
soryn VARCHAR (1);
barredyn VARCHAR (1);
-- CODE ENDED BY BALAKRISHNA
loccancelyn VARCHAR (1);
nb_clg_hlr VARCHAR (1);
nb_clg_address VARCHAR (20);
-- BY BALAKRISHNA FOR GPRS FEATURE
gprsyn VARCHAR (1);
gprsopcode INTEGER;
gsmopcode INTEGER;
active_msg_type INTEGER;
-- BY BALAKRISHNA FOR GPRS FEATURE
po_planid INTEGER;
--BY VINU ON 13 FEB 2006 FOR SOR COUNTRY LEVEL CHECK
l_country_sor VARCHAR (1);
l_vplmn_sor VARCHAR (1);
l_last_timers VARCHAR(1);
l_count_invoke INTEGER := 0;
is_unknown VARCHAR(1);
-- pi_vlr_address INTEGER; by aaftab
pi_vlr_address varchar ;
temp_country INTEGER;
temp_countrynetwork INTEGER;
--- MODIFIED FOR GTSR1159.
temp_refcountrynetwork INTEGER;
--- MODIFIED FOR GTSR1159.
--added by subhasish to capture fake VLR
fake_vlr INTEGER;
-- ended by subhasish
gsmimsi_present VARCHAR (1);
----added by Balaji configure TATA Egprs and IND STEERING ,4.1.0.0 .
lv_ind_steering_yn VARCHAR (1);
lv_enhanced_gprs_yn VARCHAR (1);
----ended by Balaji configure TATA Egprs and IND STEERING .4.1.0.0 .
lv_vlr_barred_yn VARCHAR (10);
lbo_count INTEGER := 0;
--surya
lbo_apply_flag VARCHAR (1);
lbo_plan_name VARCHAR (50);
lbo_plan_type VARCHAR (1);
l_context VARCHAR;
BEGIN
po_error_code := 0;
po_ns_relay_reason := '0';
po_attemptcount := 1;
po_errorcode := 0;
prefflag := 0;
po_list_priority := 0;
allow_yn := 0;
po_sl_relay_reason := 'F';
po_planid := 0;
po_plantype := 'N';
po_opcode := 0;
actcount := 0;
po_clg_address := pi_hlr_address;
po_prev_vlr := 0;
po_map_version := 0;
po_gprs_log := 0;
po_list_id := '-';
po_planname := '-';
po_timer_s_yn := 0;
po_context_yn := 1;
po_hlr_error_code := 0;
po_invoke_id := -1;
po_network_name := '-';
is_unknown := 'N';
currenttime := current_date ;
temp_country := 0;
temp_countrynetwork := 0;
temp_refcountrynetwork := 0;
pi_vlr_address := pi_vlr;
lbo_apply_flag := 'N';
lbo_plan_name := '-';
lbo_plan_type := '-';
--COMMIT;
/** SELECT COUNT (1)
INTO lbo_count
FROM smp_ss7_mig_dev.pr_local_break_out p
WHERE imsi = pi_imsi
AND start_date <= current_date
AND p.country_code =
SUBSTR (pi_vlr_address, 0, LENGTH (p.country_code))
AND start_date + (duration_hours / 24) >= current_date
LIMIT 1 OFFSET 0;*/-- aaftab
SELECT COUNT (1)
INTO lbo_count
FROM smp_ss7_mig_dev.pr_local_break_out p
WHERE imsi = pi_imsi
AND start_date <= now()
AND p.country_code =
SUBSTR (pi_vlr_address, 1, LENGTH (p.country_code::text))::double precision
AND start_date + (duration_hours||'hours')::interval >= now()
LIMIT 1 OFFSET 0;
IF (lbo_count > 0) --surya
THEN
lbo_apply_flag := 'Y';
BEGIN
SELECT homenetwork_id
INTO home_network
FROM smp_ss7_mig_dev.hlr_mt_address_details
WHERE hlr_address = pi_hlr_address limit 1 offset 0;
IF (home_network <> 0)
THEN
SELECT plan_name, plan_type
INTO lbo_plan_name, lbo_plan_type
FROM smp_ss7_mig_dev.pr_sp_filename a, smp_ss7_mig_dev.pr_loc_mt_plan b
WHERE a.planid = b.plan_id
AND UPPER (a.list_id) = 'DEFAULT'
AND a.home_network_id = b.homenetwork_id
AND b.homenetwork_id = home_network;
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END IF;
----added by Balaji configure INDIA STEERING 4.1.0.0 .
BEGIN
SELECT a.country_id, concat(a.country_id, b.network_code)
INTO temp_country, temp_countrynetwork
FROM smp_ss7_mig_dev.glb_mt_country_code a, smp_ss7_mig_dev.glb_mt_visiting_network b
WHERE a.country_id = b.country_id
AND concat(a.country_id , b.network_code) =
SUBSTR (pi_vlr_address,
1,
LENGTH (concat(a.country_id, b.network_code))
);
--ORDER BY A.COUNTRY_ID DESC;
--- added by subhasish on 011107
SELECT COUNT (1)
INTO fake_vlr
FROM smp_ss7_mig_dev.glb_mt_country_code
WHERE STRPOS (pi_vlr_address, ref_country_id::text) = 1
AND STRPOS (pi_vlr_address, country_id::text) = 1
AND ref_country_id IS NOT NULL;
IF fake_vlr > 0
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1; -- do not apply SL relay
--po_NS_relay_reason := 43;-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PREFFLAG := 1;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
--po_NS_relay_reason := 8;-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'U';
--PREFFLAG := 1;
po_context_yn := 0;
RETURN;
END IF;
END IF;
-- ended by subhasish on 011107
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
BEGIN
SELECT homenetwork_id
INTO home_network
FROM smp_ss7_mig_dev.hlr_mt_address_details
WHERE hlr_address = pi_hlr_address LIMIT 1 OFFSET 0;
SELECT ind_steering_yn
INTO lv_ind_steering_yn
FROM smp_ss7_mig_dev.glb_tt_network
WHERE network_id = home_network;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF lv_ind_steering_yn = 'Y'
THEN
----ended by Balaji configure INDIA STEERING 4.1.0.0 .
BEGIN
-- Exception block added by subhasish allow service logic for unknown VLR
-- Added by balakrishna for india steering solution on 10-Oct-2007
BEGIN
SELECT a.country_id, concat(a.country_id, b.network_code)
INTO temp_country, temp_countrynetwork
FROM smp_ss7_mig_dev.pr_ref_countrycode_indsteer a,
smp_ss7_mig_dev.glb_mt_visiting_network b
WHERE a.country_id = b.country_id
AND concat(a.country_id, b.network_code) =
SUBSTR (pi_vlr_address,
1,
LENGTH (concat(a.country_id, b.network_code))
)
AND homenetwork_id = home_network;
--ORDER BY A.COUNTRY_ID DESC;
--- added by subhasish on 011107
SELECT COUNT (1)
INTO fake_vlr
FROM smp_ss7_mig_dev.pr_ref_countrycode_indsteer
WHERE STRPOS (pi_vlr_address, ref_country_id::TEXT) = 1
AND STRPOS (pi_vlr_address, country_id::TEXT) = 1
AND ref_country_id IS NOT NULL;
IF fake_vlr > 0
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1; -- do not apply SL relay
--po_NS_relay_reason := 8;-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PREFFLAG := 1;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
--po_NS_relay_reason := 8;-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'U';
--PREFFLAG := 1;
po_context_yn := 0;
RETURN;
END IF;
END IF;
-- ended by subhasish on 011107
EXCEPTION
WHEN OTHERS
THEN
SELECT a.country_id, concat(a.ref_country_id, b.network_code),
concat(a.country_id, b.network_code)
INTO temp_country, temp_refcountrynetwork,
temp_countrynetwork
FROM smp_ss7_mig_dev.pr_ref_countrycode_indsteer a,
smp_ss7_mig_dev.glb_mt_visiting_network b
WHERE a.country_id = b.country_id
AND concat(a.ref_country_id, b.network_code) =
SUBSTR (pi_vlr_address,
1,
LENGTH (concat(a.ref_country_id, b.network_code))
)
AND a.ref_country_id IS NOT NULL
AND homenetwork_id = home_network;
-- ORDER BY A.COUNTRY_ID DESC;
SELECT ( temp_countrynetwork
|| SUBSTR
(pi_vlr_address,
LENGTH (temp_refcountrynetwork::text)
+ 1,
LENGTH (pi_vlr_address)
)
)
INTO pi_vlr_address;
-- FROM DUAL;
/** added by subhasish **/
END;
EXCEPTION
WHEN OTHERS
THEN
NULL;
/** ended by subhasish**/
END;
END IF;
-- Done by balakrishna for india steering solution on 10-Oct-2007
-- CODE STARTED BY BALAKRISHNA FOR SOR ENABLED/DISABLED
--Added by vinu on 17 april to add or read invoke id for a vlr.
-- Modified by subhasish , for ISD invoke id could not be read for India steering solution
-- Modified part : VLR_ADDRESS=FN_VIEW_ACTUAL_VLRADR(PI_VLR_ADDRESS)
IF pi_message = 1
THEN
BEGIN
SELECT invoke_id
INTO po_invoke_id
FROM smp_ss7_mig_dev.pr_mt_vlr_invokeid
WHERE vlr_address = smp_ss7_mig_dev.fn_view_actual_vlradr(pi_vlr_address);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
po_invoke_id := -1;
END;
END IF;
--end of code on 17 april
BEGIN
SELECT homenetwork_id, hlr_error_code
INTO home_network, po_hlr_error_code
FROM smp_ss7_mig_dev.hlr_mt_address_details
WHERE hlr_address = pi_hlr_address LIMIT 1 OFFSET 0;
SELECT network_name
INTO po_network_name
FROM smp_ss7_mig_dev.glb_mt_network
WHERE network_id = home_network;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1;
po_ns_relay_reason := 12; -- Relay due to HLR Not found
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 12; -- Relay due to HLR Not found
po_sl_relay_reason := 'I';
RETURN;
END IF;
END;
SELECT ind_steering_yn, enhanced_gprs_yn
INTO lv_ind_steering_yn, lv_enhanced_gprs_yn
FROM smp_ss7_mig_dev.glb_tt_network
WHERE network_id = home_network;
BEGIN
SELECT coalesce (sor_yn, 'Y'), coalesce (gprs_yn, 'Y')
INTO soryn, gprsyn
FROM smp_ss7_mig_dev.glb_tt_network
WHERE network_id = home_network;
/* (SELECT UNIQUE(HOMENETWORK_ID) FROM HLR_MT_ADDRESS_DETAILS WHERE
HLR_ADDRESS = PI_HLR_ADDRESS);*/
EXCEPTION
WHEN OTHERS
THEN
soryn := 'Y';
END;
IF ( (pi_msg_type = 2 AND soryn = 'N')
OR (pi_msg_type = 23 AND gprsyn = 'N')
)
THEN
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 7; -- REALY REASON SOR/GPRS DISABLED
RETURN;
END IF;
--ELSE
--CODE ENDED BY BALAKRISHNA FOR SOR ENABLED/DISABLED
--CODE DONE BY VINU ON 16 MAY 2006
--As part of production requirement, this is to check the existance of
--the VLR in the plan before White list or Black list check to set the known_yn flag appropriately.
-- next begin end block commented due to single imsi provisioning
/**
BEGIN
SELECT PLANID,PRIORITY,LIST_ID INTO PLANID,PO_LIST_PRIORITY,PO_LIST_ID FROM (SELECT RANK() OVER(ORDER BY PRIORITY ASC) RANK,B.PRIORITY,PLANID,LIST_ID FROM
PR_SP_IMSI_RANGE A,PR_SP_FILENAME B WHERE A.SNO=B.SNO AND
IMSI_FROM <= TO_NUMBER(PI_IMSI) AND IMSI_TO>=TO_NUMBER(PI_IMSI) AND B.ACTION='AC' AND B.HOME_NETWORK_ID=HOME_NETWORK) WHERE RANK=1 AND ROWNUM=1;
SELECT PLAN_TYPE,PLAN_NAME,ACTIVATE_USER_TIMER,CONTEXT_EXPIRY_TIME INTO PLANTYPE,PO_PLANNAME,ACTIVETIMER,NB_CONTEXT_EXPIRY_TIME FROM PR_LOC_MT_PLAN WHERE HOMENETWORK_ID = HOME_NETWORK
AND TO_DATE(PLAN_EFFECTIVEEND_DATE,'DD/MM/YYYY') >= TO_DATE(SYSDATE,'DD/MM/YYYY') AND PLAN_ID = PLANID;
PO_PLANID := PLANID;
PO_PLANTYPE := PLANTYPE;
PLANFOUNDFLAG:=1;
EXCEPTION WHEN OTHERS THEN
PLANFOUNDFLAG:=0;
END;
**/
-- next begin end block created for single imsi provisioning
BEGIN
SELECT planid, list_id
INTO planid, po_list_id
FROM smp_ss7_mig_dev.pr_sp_imsi_single a, smp_ss7_mig_dev.pr_sp_filename b
WHERE a.sno = b.sno
AND imsi = pi_imsi
AND b.action = 'AC'
AND b.home_network_id = home_network
AND single_range_flag = 'S';
SELECT plan_type, plan_name, activate_user_timer, context_expiry_time
INTO plantype, po_planname, activetimer, nb_context_expiry_time
FROM smp_ss7_mig_dev.pr_loc_mt_plan
WHERE homenetwork_id = home_network
/* AND TO_DATE (plan_effectiveend_date::text, 'DD/MM/YYYY') >=
TO_DATE (CURRENT_DATE::text, 'DD/MM/YYYY')*/
AND plan_effectiveend_date >= CURRENT_DATE
AND plan_id = planid;
po_planid := planid;
po_plantype := plantype;
planfoundflag := 1;
po_list_priority := 1;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
SELECT planid, priority, list_id
INTO planid, po_list_priority, po_list_id
FROM ( SELECT RANK () OVER (ORDER BY priority ASC) RANK,
b.priority, planid, list_id
FROM smp_ss7_mig_dev.pr_sp_imsi_range a, smp_ss7_mig_dev.pr_sp_filename b
WHERE a.sno = b.sno
AND imsi_from <= pi_imsi
AND imsi_to >= pi_imsi
AND b.action = 'AC'
AND b.home_network_id = home_network
AND single_range_flag = 'R') g
WHERE RANK = 1 LIMIT 1 OFFSET 0;
SELECT plan_type, plan_name, activate_user_timer,
context_expiry_time
INTO plantype, po_planname, activetimer,
nb_context_expiry_time
FROM smp_ss7_mig_dev.pr_loc_mt_plan
WHERE homenetwork_id = home_network
/*AND TO_DATE (plan_effectiveend_date, 'DD/MM/YYYY') >=
TO_DATE (CURRENT_DATE, 'DD/MM/YYYY')*/
AND plan_effectiveend_date >=
CURRENT_DATE
AND plan_id = planid;
po_planid := planid;
po_plantype := plantype;
planfoundflag := 1;
EXCEPTION
WHEN OTHERS
THEN
planfoundflag := 0;
END;
END;
--insert into smp_ss7_mig_dev.test_oss(inputs,funtion) values(concat(po_planname,po_planid),po_sl_relay_reason);
--- ended for single imsi provisioning
-- CODE STARTED BY BALAKRISHNA FOR VLR BARRED Y/N
BEGIN
IF plantype = 'R'
THEN
SELECT ERROR_CODE
INTO po_errorcode
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (pi_vlr_address, 1, LENGTH (vlr_address))
AND homenetwork_id = home_network
AND plan_id = planid;
ELSE
SELECT ERROR_CODE
INTO po_errorcode
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (pi_vlr_address, 1, LENGTH (vlr_address))
AND home_network_id = home_network
AND plan_id = planid;
END IF;
errorcode := po_errorcode;
prefflag := 1;
EXCEPTION
WHEN OTHERS
THEN
barredyn := 'N';
prefflag := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
visitccndc := pi_vlr_address;
--Added by vinu on 10-may-2006 to make the vlr unknown
cc := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
ndc := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
END;
--CODE END BY VINU ON 16 MAY 2006
SELECT white_yn, max_reject_country, max_reject_imsi, loc_cancel_yn,
loc_clg_hlr, loc_clg_address
INTO hlrwhite, max_country_reject, max_imsi_reject, loccancelyn,
nb_clg_hlr, nb_clg_address
FROM smp_ss7_mig_dev.glb_tt_network
WHERE network_id = home_network;
--Added by vinu on 17 april to add or read invoke id for a vlr.
IF pi_message = 1
THEN
IF po_invoke_id = -1
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1;
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_sl_relay_reason := 'V';
RETURN;
END IF;
END IF;
END IF;
--end of code on 17 april
-- START of code by vani for adding BLACK LIST check rel: 2.0.2.10
/* BEGIN
--Added by vinu on 16 mar 2006 for range based black list info
--SELECT IMSI_NUMBER INTO IMSINUMBER FROM PR_SP_BLACK_IMSI WHERE IMSI_NUMBER = PI_IMSI AND BLACK_YN ='Y';
SELECT b.imsi_from
INTO imsinumber
FROM pr_sp_black_range b, pr_sp_black_file a
WHERE a.sno = b.sno
AND imsi_from <= pi_imsi
AND imsi_to >= pi_imsi
AND a.action = 'AD'
AND a.home_network_id = home_network
AND ROWNUM = 1;
nb_count_black_imsi := 1;
EXCEPTION
WHEN OTHERS
THEN
nb_count_black_imsi := 0;
END;*/
BEGIN
--Added by vinu on 16 mar 2006 for range based black list info
--SELECT IMSI_NUMBER INTO IMSINUMBER FROM PR_SP_BLACK_IMSI WHERE IMSI_NUMBER = PI_IMSI AND BLACK_YN ='Y';
-- Added the below pr_sp_black_single_imsi query as part of Req690 4.1.2.0
SELECT COUNT (a.sno)
INTO imsinumber
FROM smp_ss7_mig_dev.pr_sp_black_single_imsi b, smp_ss7_mig_dev.pr_sp_black_file a
WHERE a.sno = b.sno
AND b.imsi = pi_imsi
AND a.home_network_id = home_network;
IF imsinumber > 0
THEN
nb_count_black_imsi := 1;
ELSE
BEGIN
SELECT b.imsi_from
INTO imsinumber
FROM smp_ss7_mig_dev.pr_sp_black_range b, smp_ss7_mig_dev.pr_sp_black_file a
WHERE a.sno = b.sno
AND imsi_from <= pi_imsi
AND imsi_to >= pi_imsi
AND a.action = 'AD'
AND a.home_network_id = home_network
LIMIT 1 OFFSET 0;
nb_count_black_imsi := 1;
EXCEPTION
WHEN OTHERS
THEN
nb_count_black_imsi := 0;
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
SELECT b.imsi_from
INTO imsinumber
FROM smp_ss7_mig_dev.pr_sp_black_range b, smp_ss7_mig_dev.pr_sp_black_file a
WHERE a.sno = b.sno
AND imsi_from <= pi_imsi
AND imsi_to >= pi_imsi
AND a.action = 'AD'
AND a.home_network_id = home_network
LIMIT 1 OFFSET 0;
nb_count_black_imsi := 1;
EXCEPTION
WHEN OTHERS
THEN
nb_count_black_imsi := 0;
END;
END;
IF nb_count_black_imsi > 0
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 4;
-- relay reason is the IMSI is in BLACK List
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 4;
-- relay reason is the IMSI is in BLACK List
po_sl_relay_reason := 'L';
RETURN;
END IF;
END IF;
--END OF CODE BY VANI for adding BLACK LIST check rel: 2.0.2.10
--Start of code by vinu on 24-Apr-2006
--The IMSI white check brought outside because if IMSI / MSISDN is found an non-whitelist then relay reason has to be
-- W and not N in case no plan is attached to the list containing the IMSI / MSISDN
IF hlrwhite = 'Y'
THEN --hlr if
BEGIN
SELECT b.imsi_from
INTO imsinumber
FROM pr_sp_white_range b, pr_sp_white_file a
WHERE a.sno = b.sno
AND imsi_from <= pi_imsi
AND imsi_to >= pi_imsi
AND a.action = 'AC'
AND a.home_network_id = home_network
LIMIT 1 OFFSET 0;
imsicount := 1;
EXCEPTION
WHEN OTHERS
THEN
imsicount := 0;
END;
IF imsicount <> 1
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1;
po_ns_relay_reason := 3;
-- relay reason is imsi being non white listed
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 3;
-- relay reason is imsi being non white listed
po_sl_relay_reason := 'W';
RETURN;
END IF;
END IF;
END IF;
--End of code by vinu on 24-Apr-2006
IF planfoundflag = 0
THEN
IF (lbo_apply_flag = 'Y')
THEN
allow_yn := 1;
po_ns_relay_reason := 6;
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 6;
po_sl_relay_reason := 'N';
RETURN;
END IF;
END IF;
--Code block taken out for HLR white/non white to check if the vlr is found in plan for all cases. Done on 28 nov 205
BEGIN
SELECT country_id, network_code, country_sor, vplmn_sor, opcode,
barred
INTO cc, ndc, l_country_sor, l_vplmn_sor, visitccndc,
barredyn
FROM (SELECT c.country_id, c.network_code, b.country_sor,
b.vplmn_sor, b.opcode, barred
FROM smp_ss7_mig_dev.pr_plan_network_barred_sor_yn b,
smp_ss7_mig_dev.glb_mt_visiting_network c
WHERE c.country_id || c.network_code =
SUBSTR (pi_vlr_address,
1,
LENGTH (c.country_id || c.network_code)
)
AND b.opcode = c.operator_code
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY c.country_id || c.network_code DESC) g
LIMIT 1 OFFSET 0;
--Added by vinu on 17 may 2006 so that the VLR is treated an unknown even if the CCNDC is found in visiting network details.
IF prefflag = 0
THEN
ndc := 0;
visitccndc := pi_vlr_address;
--Added by vinu on 24 may 2006 so that RNA is not checked for unknown VLR.
END IF;
EXCEPTION
WHEN OTHERS
THEN
visitccndc := pi_vlr_address;
cc := 0;
barredyn := 'N';
END;
IF (lbo_apply_flag = 'Y' AND l_country_sor = 'Y' AND l_vplmn_sor = 'Y'
)
THEN
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 11; -- relay reason is that SOR is disabled
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
--ADDED BY VINU ON 13 FEB 2006 TO CHECK SOR AT VPLMN AND COUNTRY LEVEL
IF l_country_sor = 'N'
THEN
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 11; -- relay reason is that SOR is disabled
po_sl_relay_reason := 'S';
RETURN;
END IF;
IF l_vplmn_sor = 'N'
THEN
allow_yn := 1; -- do not apply SL relay
po_ns_relay_reason := 11; -- relay reason is that SOR is disabled
po_sl_relay_reason := 'S';
RETURN;
END IF;
END IF;
--END BY VINU ON 13 FEB 2006
--- subhasish TATA GPRS
----added by Balaji configure TATA Egprs, 4.1.0.0 .
--IF pi_msg_type = 23
IF (pi_msg_type = 23 AND lv_enhanced_gprs_yn = 'Y')
THEN
BEGIN
SELECT 'Y'
INTO gsmimsi_present
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi
AND vlr_address = visitccndc
AND expiry_time > CURRENT_DATE
AND msg_type = 2
AND active_entry_yn = 'Y';
EXCEPTION
WHEN OTHERS
THEN
gsmimsi_present := 'N';
END;
END IF;
-- end subhasish TATA GPRS
IF hlrwhite = 'Y'
THEN --hlr if
IF imsicount > 0
THEN -- imsi if
IF pi_msg_type = 2
THEN
SELECT coalesce (SUM (b.attemptcount), 0)
INTO po_attemptcount
FROM smp_ss7_mig_dev.pr_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = pi_imsi
AND a.imsi_no = b.imsi_no
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE
AND msg_type = pi_msg_type;
ELSE
SELECT coalesce (SUM (b.attemptcount), 0)
INTO po_attemptcount
FROM smp_ss7_mig_dev.pr_gprs_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = pi_imsi
AND a.imsi_no = b.imsi_no
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE
AND msg_type = pi_msg_type;
END IF;
po_attemptcount := po_attemptcount + 1;
po_opcode := coalesce (( visitccndc::integer), 0);
BEGIN
IF barredyn = 'Y'
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PREFFLAG := 1;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'B';
--PREFFLAG := 1;
po_context_yn := 0;
RETURN;
END IF;
ELSE -- Added the else block as part of req#1288
IF plantype = 'R'
THEN
SELECT vlr_barred_yn
INTO lv_vlr_barred_yn
FROM (SELECT vlr_barred_yn
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
ELSIF plantype = 'P'
THEN
SELECT barred_yn
INTO lv_vlr_barred_yn
FROM (SELECT barred_yn
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) t
LIMIT 1 OFFSET 0;
END IF;
IF lv_vlr_barred_yn = 'Y'
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8;
-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8;
-- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'B';
po_context_yn := 0;
RETURN;
END IF;
END IF;
END IF; -- CODE ENDED BY BALAKRISHNA FOR VLR BARRED Y/N
EXCEPTION
WHEN OTHERS
THEN
lv_vlr_barred_yn := 'N';
END;
-- IHT TIMER IS ONLY FOR GSM
--Added by vinu to check if imsi is in exception list
BEGIN
SELECT vlr_address
INTO vlraddress
FROM smp_ss7_mig_dev.pr_imsi_attempt
WHERE imsi_no = pi_imsi
AND expirytime <= CURRENT_DATE
AND iht_expiry_time > CURRENT_DATE
AND imsi_exp_cntr = 0;
IF plantype = 'R'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (vlraddress,
1,
LENGTH (vlr_address)
)
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) taru
LIMIT 1 OFFSET 0;
ELSIF plantype = 'P'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (vlraddress,
1,
LENGTH (vlr_address)
)
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) s
LIMIT 1 OFFSET 0;
END IF;
--Exception list to work the same for R or P type plans
IF errorcode <> 8
THEN
reccount := 1;
ELSE
reccount := 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'E';
RETURN;
END IF;
END IF;
BEGIN
IF plantype = 'R'
THEN
--ADDED BY VINU/BALAKRISHNA FOR RNA IN RATIO PLAN
BEGIN
--Message type condition added by vinu on 21 march 2006 for 2.0.7.1
IF pi_msg_type = 2
THEN
SELECT b.imsi_no
INTO imsinumber
FROM pr_imsi_attempt a, pr_imsi_vlr_attempt b
WHERE a.imsi_no = pi_imsi
AND a.imsi_no = b.imsi_no
AND b.operator_code = visitccndc
AND msg_type = pi_msg_type
AND b.ERROR_CODE = 8
AND b.rna_status = 'AC'
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
ELSIF pi_msg_type = 23
THEN
SELECT b.imsi_no
INTO imsinumber
FROM smp_ss7_mig_dev.pr_gprs_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = pi_imsi
AND a.imsi_no = b.imsi_no
AND b.operator_code = visitccndc
AND msg_type = pi_msg_type
AND b.ERROR_CODE = 8
AND b.rna_status = 'AC'
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
END IF;
rnapresent := 1;
EXCEPTION
WHEN OTHERS
THEN
rnapresent := 0;
END;
IF rnapresent = 1
THEN
--subhasish TATA GPRS
----added by Balaji configure TATA Egprs 4.1.0.0. .
IF (lv_enhanced_gprs_yn = 'Y')
THEN
IF (pi_msg_type = 2)
OR (pi_msg_type = 23 AND gsmimsi_present = 'Y')
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'O';
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
RETURN;
END IF;
--elsif (PI_MSG_TYPE = 23 and GSMIMSI_PRESENT='N') then
-- ALLOW_YN:=0;
-- po_SL_relay_reason := 'J';
END IF;
ELSE
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'O';
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
RETURN;
END IF;
END IF;
END IF;
--END FOR RNA
END IF;
-- Extended context capability is for both the plans i.e PREF
IF max_imsi_reject > 0
THEN
IF max_country_reject > 0
THEN --IF ZERO THEN DO NOT APPLY
--IF ERRORCODE = 8 THEN
BEGIN
SELECT COUNT
INTO present_ctry_count
FROM smp_ss7_mig_dev.pr_visiting_country_count
WHERE homenetwork_id = home_network
AND visiting_country_id = cc
AND plan_id = planid;
ctrypresent := 1;
EXCEPTION
WHEN OTHERS
THEN
ctrypresent := 0;
END;
IF ctrypresent > 0
THEN
IF present_ctry_count > 0
THEN
BEGIN
SELECT opcode
INTO opcodeexist
FROM smp_ss7_mig_dev.pr_imsi_country_list a,
smp_ss7_mig_dev.pr_imsi_attempt b
WHERE a.imsi_no = b.imsi_no
AND a.imsi_no = pi_imsi
AND a.home_network_id = home_network
AND a.country_id = cc
AND opcode = visitccndc
AND a.reject_time >= b.imsi_list_starttime
AND a.reject_time < b.imsi_list_endtime
AND b.imsi_list_endtime > currenttime;
opcodepresent := 1;
EXCEPTION
WHEN OTHERS
THEN
opcodepresent := 0;
END;
IF opcodepresent = 0
THEN
SELECT COUNT (1)
INTO listcount
FROM smp_ss7_mig_dev.pr_imsi_country_list a,
smp_ss7_mig_dev.pr_imsi_attempt b
WHERE a.imsi_no = b.imsi_no
AND a.imsi_no = pi_imsi
AND a.home_network_id = home_network
AND a.country_id = cc
AND a.reject_time >= b.imsi_list_starttime
AND a.reject_time < b.imsi_list_endtime
AND b.imsi_list_endtime > currenttime;
IF listcount >= present_ctry_count
THEN
--subhasish TATA GPRS
----added by Balaji configure TATA Egprs 4.1.0.0 .
IF (lv_enhanced_gprs_yn = 'Y')
THEN
IF (pi_msg_type = 2)
OR ( pi_msg_type = 23
AND gsmimsi_present = 'Y'
)
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'X';
prefflag := 1;
RETURN;
END IF;
--elsif (PI_MSG_TYPE = 23 and GSMIMSI_PRESENT='N') then
-- ALLOW_YN:=0;
-- po_SL_relay_reason := 'J';
END IF;
ELSE
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'X';
prefflag := 1;
RETURN;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
--END IF;
END IF;
END IF;
-- END IF; -- BALAKRISHNA EXTENDED CONTEXT CAPABILITY IS ONLY FOR RATIO
--vinu
-- IHT TIMER IS ONLY FOR GSM
SELECT vlr_address
INTO vlraddress
FROM smp_ss7_mig_dev.pr_imsi_attempt
WHERE imsi_no = pi_imsi
AND expirytime <= CURRENT_DATE
AND iht_expiry_time > CURRENT_DATE
AND imsi_exp_cntr = 0;
IF plantype = 'R'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (vlraddress,
1,
LENGTH (vlr_address)
)
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) s
LIMIT 1 OFFSET 0;
ELSIF plantype = 'P'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (vlraddress,
1,
LENGTH (vlr_address)
)
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
END IF;
--Exception list to work the same for R or P type plans
IF errorcode <> 8
THEN
reccount := 1;
ELSE
reccount := 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
--return;---subhasish
ELSE
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'E';
--return;---subhasish
END IF;
ELSE
reccount := 0;
BEGIN
SELECT imsi_number, vlr_address, gprs_opcode, msg_type,
last_timers_yn
INTO imsinumber, gsmopcode, gprsopcode, active_msg_type,
l_last_timers
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
reccount := 1;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF is_unknown = 'N'
THEN
BEGIN
--CHANGED BY BALAKRISHNA BCOS NOW VISITCCNDC CONTAINS OPCODE
/*IF PI_MSG_TYPE = 2 AND GSMOPCODE IS NULL AND GPRSOPCODE IS NOT NULL THEN
IF PO_OPCODE <> GPRSOPCODE THEN
PO_GPRS_LOG := 1;
END IF;
VLRCOUNT:=1;
ELSIF PI_MSG_TYPE = 23 AND GPRSOPCODE IS NULL AND GSMOPCODE IS NOT NULL THEN
IF PO_OPCODE <> GSMOPCODE THEN
PO_GPRS_LOG := 1;
END IF;
VLRCOUNT:=1;
ELSE*/
IF pi_msg_type = 2
THEN
SELECT imsi_number
INTO imsinumber
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi
AND vlr_address = visitccndc
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
ELSE
SELECT imsi_number
INTO imsinumber
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi
AND gprsopcode = visitccndc
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
END IF;
vlrcount := 1;
--END IF;
EXCEPTION
WHEN OTHERS
THEN
vlrcount := 0;
END;
END IF;
IF vlrcount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 2;
-- relay reason is the IMSI in active user
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
allow_yn := 1;
po_ns_relay_reason := 2;
-- relay reason is the IMSI in active user
po_sl_relay_reason := 'A';
END IF;
--Added by vinu on 23-feb-2006 to set the timer S flag if it is already active with timer S
IF l_last_timers = 'Y'
THEN
po_timer_s_yn := 1;
END IF;
ELSE
--ALLOW_YN:=1;
--ADDED BY VINU/BALAKRISHNA ON 24 OCT 2005 FOR CALLING POLICY MANGEMENT
--PR_PKG_OSS_POLICYMANAGEMENT.APPLY_BUSINESS_RULE( PI_IMSI, PI_VLR_ADDRESS, HOME_NETWORK, ALLOW_YN, po_error_code );
--ADDED BY VINU/BALAKRISHNA ON 24 OCT 2005 FOR CALLING SERVICE LOGIC IN CASE PM REJECT THE LU
--IF ALLOW_YN = 1 THEN
/*pr_pkg_oss_ratio_counter.isprefered (pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type,
allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code
);*/
SELECT * FROM smp_ss7_mig_dev.pr_pkg_oss_ratio_counter_isprefered(pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type
) INTO allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code;
IF (lbo_apply_flag = 'Y')
THEN
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
NULL;
END IF;
--ELSE
--ALLOW_YN:=1;
--po_NS_relay_reason := 10 ;-- relay reason is the POLICY MANAGEMENT
--po_SL_relay_reason := 'M';
--END IF;
--END ON 24 OCT 2005
END IF;
ELSE
--ALLOW_YN:=1;
--PR_PKG_OSS_POLICYMANAGEMENT.APPLY_BUSINESS_RULE( PI_IMSI, PI_VLR_ADDRESS, HOME_NETWORK, ALLOW_YN, po_error_code );
--ADDED BY VINU/BALAKRISHNA ON 24 OCT 2005 FOR CALLING SERVICE LOGIC IN CASE PM REJECT THE LU
--IF ALLOW_YN = 1 THEN
SELECT * FROM smp_ss7_mig_dev.pr_pkg_oss_ratio_counter_isprefered(pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type
) INTO allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code;
IF (lbo_apply_flag = 'Y')
THEN
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
NULL;
END IF;
--ELSE
--ALLOW_YN:=1;
--po_NS_relay_reason := 10 ;-- relay reason is the POLICY MANAGEMENT
--po_SL_relay_reason := 'M';
--END IF;
--END ON 24 OCT 2005
END IF;
END IF;
--END IF; -- ADDED BY BALAKRISHNA FOR VLR BARRED
END IF;
ELSE
/*BEGIN
IF PLANTYPE = 'R' THEN
SELECT ERROR_CODE INTO PO_ERRORCODE FROM PR_PLAN_RATIO WHERE VLR_ADDRESS = SUBSTR(PI_VLR_ADDRESS,1,LENGTH(VLR_ADDRESS)) AND HOMENETWORK_ID=HOME_NETWORK AND PLAN_ID = PLANID;
ELSE
SELECT ERROR_CODE INTO PO_ERRORCODE FROM PR_LOC_VLR_DETAILS WHERE VLR_ADDRESS = SUBSTR(PI_VLR_ADDRESS,1,LENGTH(VLR_ADDRESS)) AND HOME_NETWORK_ID=HOME_NETWORK AND PLAN_ID = PLANID;
END IF;
EXCEPTION WHEN OTHERS THEN
BARREDYN := 'N';
END;*/
IF pi_msg_type = 2
THEN
SELECT coalesce (SUM (b.attemptcount), 0)
INTO po_attemptcount
FROM smp_ss7_mig_dev.pr_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = (pi_imsi)
AND a.imsi_no = b.imsi_no
AND msg_type = pi_msg_type
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
ELSE
SELECT coalesce (SUM (b.attemptcount), 0)
INTO po_attemptcount
FROM smp_ss7_mig_dev.pr_gprs_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = (pi_imsi)
AND a.imsi_no = b.imsi_no
AND msg_type = pi_msg_type
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
END IF;
po_attemptcount := po_attemptcount + 1;
--commented by vinu on 28 nov 2005 and taken out of the main if block
BEGIN
IF plantype = 'R'
THEN
SELECT visiting_cc, visiting_ndc
INTO cc, ndc
FROM (SELECT visiting_cc, visiting_ndc
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
SELECT operator_code
INTO visitccndc
FROM smp_ss7_mig_dev.glb_mt_visiting_network
WHERE country_id = cc AND network_code = ndc;
prefflag := 1;
--Added by vinu on 28 nov 2005 to set the known yn field to y if vlr found in plan
ELSIF plantype = 'P'
THEN
--BEGIN
SELECT location_id
INTO ndc
FROM (SELECT location_id
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) t
LIMIT 1 OFFSET 0;
SELECT a.country_id
INTO cc
FROM smp_ss7_mig_dev.glb_mt_country a, smp_ss7_mig_dev.pr_glb_mt_location b
WHERE UPPER (a.country_name) = UPPER (b.country_id)
AND b.location_id = ndc;
SELECT operator_code
INTO visitccndc
FROM smp_ss7_mig_dev.glb_mt_visiting_network
WHERE country_id = cc
AND country_id || network_code =
SUBSTR (pi_vlr_address,
1,
LENGTH (country_id || network_code)
);
prefflag := 1;
--Added by vinu on 28 nov 2005 to set the known yn field to y if vlr found in plan
--VISITCCNDC:=TRIM(CC||NDC);
--EXCEPTION WHEN OTHERS THEN
-- VISITCCNDC:=PI_VLR_ADDRESS;
--END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
visitccndc := pi_vlr_address;
cc := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
prefflag := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
ndc := 0;
--Added by vinu on 10-may-2006 to make the vlr unknown
END;
po_opcode := visitccndc;
BEGIN
IF barredyn = 'Y'
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'B';
po_context_yn := 0;
RETURN;
END IF;
ELSE -- Added the else block as part of req#1288
IF plantype = 'R'
THEN
SELECT vlr_barred_yn
INTO lv_vlr_barred_yn
FROM (SELECT vlr_barred_yn
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) t
LIMIT 1 OFFSET 0;
ELSIF plantype = 'P'
THEN
SELECT barred_yn
INTO lv_vlr_barred_yn
FROM (SELECT barred_yn
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (pi_vlr_address,
1,
LENGTH (vlr_address)
)
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
END IF;
IF lv_vlr_barred_yn = 'Y'
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
po_context_yn := 0;
prefflag := 1;
RETURN;
ELSE
allow_yn := 0; -- do not apply SL relay
po_ns_relay_reason := 8; -- REALY REASON VLR IS BARRED
po_sl_relay_reason := 'B';
po_context_yn := 0;
RETURN;
END IF;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_vlr_barred_yn := 'N';
END;
-- IHT TIMER IS ONLY FOR GSM
--Added by vinu to check if imsi is in exception list
BEGIN
SELECT vlr_address
INTO vlraddress
FROM smp_ss7_mig_dev.pr_imsi_attempt
WHERE imsi_no = pi_imsi
AND expirytime <= CURRENT_DATE
AND iht_expiry_time > CURRENT_DATE
AND imsi_exp_cntr = 0;
IF plantype = 'R'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (vlraddress, 1,
LENGTH (vlr_address))
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
ELSIF plantype = 'P'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (vlraddress, 1,
LENGTH (vlr_address))
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) g
LIMIT 1 OFFSET 0;
END IF;
--Exception list to work the same for R or P type plans
IF errorcode <> 8
THEN
reccount := 1;
ELSE
reccount := 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'E';
RETURN;
END IF;
END IF;
BEGIN
--vinu
IF plantype = 'R'
THEN
BEGIN
--Message type condition added by vinu on 21 march 2006 for 2.0.7.1
IF pi_msg_type = 2
THEN
SELECT b.imsi_no
INTO imsinumber
FROM smp_ss7_mig_dev.pr_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = TO_CHAR (pi_imsi)
AND a.imsi_no = b.imsi_no
AND b.operator_code = visitccndc
AND msg_type = pi_msg_type
AND b.ERROR_CODE = 8
AND b.rna_status = 'AC'
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
ELSIF pi_msg_type = 23
THEN
SELECT b.imsi_no
INTO imsinumber
FROM smp_ss7_mig_dev.pr_gprs_imsi_attempt a, smp_ss7_mig_dev.pr_imsi_vlr_attempt b
WHERE a.imsi_no = TO_CHAR (pi_imsi)
AND a.imsi_no = b.imsi_no
AND b.operator_code = visitccndc
AND msg_type = pi_msg_type
AND b.ERROR_CODE = 8
AND b.rna_status = 'AC'
AND a.last_update_time <= b.last_update_time
AND a.expirytime > CURRENT_DATE;
END IF;
rnapresent := 1;
EXCEPTION
WHEN OTHERS
THEN
rnapresent := 0;
END;
IF rnapresent = 1
THEN
--subhasish TATA GPRS
----added by Balaji configure TATA Egprs 4.1.0.0.
IF (lv_enhanced_gprs_yn = 'Y')
THEN
IF (pi_msg_type = 2)
OR (pi_msg_type = 23 AND gsmimsi_present = 'Y')
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'O';
--PO_ATTEMPTCOUNT := 2;
--PREFFLAG := 1;
RETURN;
END IF;
-- elsif (PI_MSG_TYPE = 23 and GSMIMSI_PRESENT='N') then
-- ALLOW_YN:=0;
-- po_SL_relay_reason := 'J';
END IF;
ELSE
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
--PO_ATTEMPTCOUNT := 2;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 9;
-- Relay due to Roaming not allowed on second occurrence in Ratio plan
po_sl_relay_reason := 'O';
--PO_ATTEMPTCOUNT := 2;
RETURN;
END IF;
END IF;
END IF;
END IF;
IF max_imsi_reject > 0
THEN
IF max_country_reject > 0
THEN --IF ZERO THEN DO NOT APPLY
--IF ERRORCODE = 8 THEN
BEGIN
SELECT COUNT
INTO present_ctry_count
FROM smp_ss7_mig_dev.pr_visiting_country_count
WHERE homenetwork_id = home_network
AND visiting_country_id = cc
AND plan_id = planid;
ctrypresent := 1;
EXCEPTION
WHEN OTHERS
THEN
ctrypresent := 0;
END;
IF ctrypresent > 0
THEN
IF present_ctry_count > 0
THEN
BEGIN
SELECT opcode
INTO opcodeexist
FROM smp_ss7_mig_dev.pr_imsi_country_list a, pr_imsi_attempt b
WHERE a.imsi_no = b.imsi_no
AND a.imsi_no = pi_imsi
AND a.home_network_id = home_network
AND a.country_id = cc
AND opcode = visitccndc
AND a.reject_time >= b.imsi_list_starttime
AND a.reject_time < b.imsi_list_endtime
AND b.imsi_list_endtime > currenttime;
opcodepresent := 1;
EXCEPTION
WHEN OTHERS
THEN
opcodepresent := 0;
END;
IF opcodepresent = 0
THEN
SELECT COUNT (1)
INTO listcount
FROM smp_ss7_mig_dev.pr_imsi_country_list a, smp_ss7_mig_dev.pr_imsi_attempt b
WHERE a.imsi_no = b.imsi_no
AND a.imsi_no = pi_imsi
AND a.home_network_id = home_network
AND a.country_id = cc
AND a.reject_time >= b.imsi_list_starttime
AND a.reject_time < b.imsi_list_endtime
AND b.imsi_list_endtime > currenttime;
IF listcount >= present_ctry_count
THEN
--subhasish TATA GPRS
----added by Balaji configure TATA Egprs.4.1.0.0 .
IF (lv_enhanced_gprs_yn = 'Y')
THEN
IF (pi_msg_type = 2)
OR ( pi_msg_type = 23
AND gsmimsi_present = 'Y'
)
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'X';
prefflag := 1;
RETURN;
END IF;
--elsif (PI_MSG_TYPE = 23 and GSMIMSI_PRESENT='N') then
-- ALLOW_YN:=0;
-- po_SL_relay_reason := 'J';
END IF;
ELSE
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
RETURN;
ELSE
allow_yn := 1;
po_ns_relay_reason := 5;
-- relay reason is reject limit for country reached
po_sl_relay_reason := 'X';
RETURN;
END IF;
END IF;
END IF;
END IF;
END IF;
END IF;
--END IF;
END IF;
END IF;
--END IF; -- BALAKRISHNA EXTENDED CONTEXT CAPABILITY IS ONLY FOR RATIO
--vinu
SELECT vlr_address
INTO vlraddress
FROM smp_ss7_mig_dev.pr_imsi_attempt
WHERE imsi_no = pi_imsi
AND expirytime <= CURRENT_DATE
AND iht_expiry_time > CURRENT_DATE
AND imsi_exp_cntr = 0;
IF plantype = 'R'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_plan_ratio
WHERE vlr_address =
SUBSTR (vlraddress, 1,
LENGTH (vlr_address))
AND homenetwork_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) h
WHERE ROWNUM = 1;
ELSIF plantype = 'P'
THEN
SELECT ERROR_CODE
INTO errorcode
FROM (SELECT ERROR_CODE
FROM smp_ss7_mig_dev.pr_loc_vlr_details
WHERE vlr_address =
SUBSTR (vlraddress, 1,
LENGTH (vlr_address))
AND home_network_id = home_network
AND plan_id = planid
ORDER BY vlr_address DESC) t
LIMIT 1 OFFSET 0;
END IF;
IF errorcode <> 8
THEN
reccount := 1;
ELSE
reccount := 0;
END IF;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
--return;---subhasish
ELSE
allow_yn := 1;
po_ns_relay_reason := 1;
-- relay reason is the IMSI in incompatible list
po_sl_relay_reason := 'E';
--return;---subhasish
END IF;
ELSE
reccount := 0;
BEGIN
--Active entry condition to eliminate N records from the query result since they are not considered active contexts
--added on 7 dec 2005 by vinu
SELECT imsi_number, vlr_address, gprs_opcode, msg_type,
last_timers_yn
INTO imsinumber, gsmopcode, gprsopcode, active_msg_type,
l_last_timers
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
reccount := 1;
EXCEPTION
WHEN OTHERS
THEN
reccount := 0;
END;
IF reccount > 0
THEN
IF is_unknown = 'N'
THEN
BEGIN
--Active entry condition to eliminate N records from the query result since they are not considered active contexts
--added on 7 dec 2005 by vinu
/*IF PI_MSG_TYPE = 2 AND GSMOPCODE IS NULL AND GPRSOPCODE IS NOT NULL THEN
IF PO_OPCODE <> GPRSOPCODE THEN
PO_GPRS_LOG := 1;
END IF;
VLRCOUNT:=1;
ELSIF PI_MSG_TYPE = 23 AND GPRSOPCODE IS NULL AND GSMOPCODE IS NOT NULL THEN
IF PO_OPCODE <> GSMOPCODE THEN
PO_GPRS_LOG := 1;
END IF;
VLRCOUNT:=1;
ELSE*/
IF pi_msg_type = 2
THEN
SELECT imsi_number
INTO imsinumber
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number =pi_imsi
AND vlr_address = visitccndc
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
ELSE
SELECT imsi_number
INTO imsinumber
FROM pr_active_user_timer
WHERE imsi_number = pi_imsi
AND gprsopcode = visitccndc
AND expiry_time > CURRENT_DATE
AND active_entry_yn = 'Y';
END IF;
vlrcount := 1;
--END IF;
EXCEPTION
WHEN OTHERS
THEN
vlrcount := 0;
END;
END IF;
IF vlrcount > 0
THEN
IF lbo_apply_flag = 'Y'
THEN
allow_yn := 1;
po_ns_relay_reason := 2;
--- relay reason is the IMSI in active user
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
allow_yn := 1;
po_ns_relay_reason := 2;
--- relay reason is the IMSI in active user
po_sl_relay_reason := 'A';
END IF;
--Added by vinu on 23-feb-2006 to set the timer S flag if it is already active with timer S
IF l_last_timers = 'Y'
THEN
po_timer_s_yn := 1;
END IF;
ELSE
--ALLOW_YN:=1;
--PR_PKG_OSS_POLICYMANAGEMENT.APPLY_BUSINESS_RULE( PI_IMSI, PI_VLR_ADDRESS, HOME_NETWORK, ALLOW_YN, po_error_code );
--ADDED BY VINU/BALAKRISHNA ON 24 OCT 2005 FOR CALLING SERVICE LOGIC IN CASE PM REJECT THE LU
--IF ALLOW_YN = 1 THEN
/* pr_pkg_oss_ratio_counter.isprefered (pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type,
allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code
);*/
SELECT * FROM smp_ss7_mig_dev.pr_pkg_oss_ratio_counter_isprefered(pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type
) INTO allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code;
IF (lbo_apply_flag = 'Y')
THEN
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
NULL;
END IF;
--ELSE
--ALLOW_YN:=1;
--po_NS_relay_reason := 10 ;-- relay reason is the POLICY MANAGEMENT
--po_SL_relay_reason := 'M';
--END IF;
--END ON 24 OCT 2005
END IF;
ELSE
--ALLOW_YN:=1;
--PR_PKG_OSS_POLICYMANAGEMENT.APPLY_BUSINESS_RULE( PI_IMSI, PI_VLR_ADDRESS, HOME_NETWORK, ALLOW_YN, po_error_code );
--ADDED BY VINU/BALAKRISHNA ON 24 OCT 2005 FOR CALLING SERVICE LOGIC IN CASE PM REJECT THE LU
--IF ALLOW_YN = 1 THEN
/*pr_pkg_oss_ratio_counter.isprefered (pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type,
allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code
);*/
SELECT * FROM smp_ss7_mig_dev.pr_pkg_oss_ratio_counter_isprefered(pi_imsi,
pi_hlr_address,
pi_vlr_address,
home_network,
visitccndc,
cc,
ndc,
planid,
plantype,
po_attemptcount,
pi_msg_type
) INTO allow_yn,
po_errorcode,
prefflag,
po_sl_relay_reason,
po_timer_s_yn,
po_error_code;
IF (lbo_apply_flag = 'Y')
THEN
po_sl_relay_reason := 'K';
po_list_id := 'DEFAULT';
po_planname := lbo_plan_name;
po_plantype := lbo_plan_type;
prefflag := 1;
ELSE
NULL;
END IF;
--ELSE
--ALLOW_YN:=1;
--po_NS_relay_reason := 10 ;-- relay reason is the POLICY MANAGEMENT
-- po_SL_relay_reason := 'M';
--END IF;
--END ON 24 OCT 2005
END IF;
END IF;
--END IF;
--END IF;
--END IF;
END IF;
--ADDED BY VINU ON 30 NOV 2005 FOR CANCEL LOCATION REQUIREMENT
IF allow_yn = 0 AND loccancelyn = 'Y'
THEN
IF po_attemptcount = 1
THEN
-- TO CHECK IF THE hlr IS ENABLED TO GET THE CALLING ADDRESS FOR CANCEL LU RELE 2.0.2.15
IF nb_clg_hlr = 'N'
THEN
-- PO_CLG_ADDRESS := PI_HLR_ADDRESS;
-- ELSE
po_clg_address := nb_clg_address;
END IF;
BEGIN
/** SELECT b.imsi_no
INTO imsinumber
FROM smp_ss7_mig_dev.pr_imsi_vlr_attempt b, smp_ss7_mig_dev.pr_active_user_timer c
WHERE b.imsi_no = pi_imsi
AND (b.expiry_time - nb_context_expiry_time / 86400) >
(c.expiry_time - activetimer / 86400
)
AND b.imsi_no = c.imsi_number;**/--aaftab
SELECT b.imsi_no
INTO imsinumber
FROM smp_ss7_mig_dev.pr_imsi_vlr_attempt b, smp_ss7_mig_dev.pr_active_user_timer c
WHERE b.imsi_no = pi_imsi
AND (b.expiry_time - (nb_context_expiry_time||'second'))::interval >
(c.expiry_time - (activetimer||'second')::interval
)
AND b.imsi_no = c.imsi_number;
actcount := 1;
EXCEPTION
WHEN OTHERS
THEN
actcount := 0;
END;
IF actcount > 0
THEN
po_prev_vlr := 0;
po_map_version := 0;
ELSE
BEGIN
SELECT coalesce (vlr, 0), map_version
INTO po_prev_vlr, po_map_version
FROM smp_ss7_mig_dev.pr_active_user_timer
WHERE imsi_number = pi_imsi;
IF pi_vlr_address = po_prev_vlr
THEN
po_prev_vlr := 0;
po_map_version := 0;
--next else statement added by subhasish
ELSE
po_prev_vlr := smp_ss7_mig_dev.fn_view_actual_vlradr(po_prev_vlr);
END IF;
EXCEPTION
WHEN OTHERS
THEN
po_prev_vlr := 0;
po_map_version := 0;
END;
END IF;
ELSE
po_prev_vlr := 0;
po_map_version := 0;
END IF;
ELSE
po_prev_vlr := 0;
po_map_version := 0;
END IF;
--PO_ERRORCODE :=0;
EXCEPTION
WHEN OTHERS
THEN
po_error_code := SQLSTATE;
RAISE NOTICE '%',SQLERRM;
GET STACKED DIAGNOSTICS l_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '%', l_context;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION smp_ss7_mig_dev.pr_pkg_oss_networkserver_pr_sp_apply_sl_yn(character varying, character varying, character varying, integer, integer)
OWNER TO postgres;int isPreferred(int i_message,int msgType, char * i_imsi, char *i_hlr, char *i_vlr, int *o_isAllowed, int *o_nsRelayReason, char *o_relayReason, char *o_planName, char *o_planType, int *o_isKnown, int *o_listPriority, int *o_opCode, int *o_attemptCount, char *prevVLR, int *mapVersion, char *clgPty, int *o_vlrErrCode, int *o_diffVPLMN,char *o_listId,char *o_networkName, int *timerS, int *o_inContext,int *o_hlrErrorCode,int *o_invokeId,PGconn *con)
{
int release = 0;
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR po_plantype[1];
VARCHAR po_sl_relay_reason[1];
VARCHAR m_h_imsi[30];
VARCHAR po_prev_vlr[30];
VARCHAR po_clg_address[30];
VARCHAR m_h_hlraddress[30];
VARCHAR m_h_vlraddress[30];
VARCHAR po_planname[31];
VARCHAR po_network_name[31];
VARCHAR po_list_id[10];
//int m_ht_diffVPLMN;
int m_ht_msgType;
int m_ht_message;
int po_opcode;
int allow_yn;
int prefflag;
int po_map_version;
int po_ns_relay_reason;
//int m_ht_isKnown;
int po_list_priority;
int po_attemptcount;
// int m_ht_vlrErrCode;
int po_errorcode;
int po_error_code;
int po_gprs_log;
int po_timer_s_yn;
int po_context_yn;
int po_hlr_error_code;
int po_invoke_id;
char buffer[512];
EXEC SQL END DECLARE SECTION;
printf("entered into ispreffered\n");
m_ht_msgType = msgType;
m_ht_message = i_message;
memset((char *)m_h_imsi.arr,'\0', sizeof((char *)m_h_imsi.arr));
m_h_imsi.len=0;
memset((char *)m_h_hlraddress.arr,'\0', sizeof((char *)m_h_hlraddress.arr));
m_h_hlraddress.len=0;
memset((char *)m_h_vlraddress.arr,'\0', sizeof((char *)m_h_vlraddress.arr));
m_h_vlraddress.len=0;
memset(buffer,'\0',sizeof(buffer));
m_h_imsi.len = strlen(i_imsi);
strcpy((char *)m_h_imsi.arr, i_imsi);
m_h_hlraddress.len = strlen(i_hlr);
strcpy((char *)m_h_hlraddress.arr, i_hlr);
m_h_vlraddress.len = strlen(i_vlr);
strcpy((char *)m_h_vlraddress.arr, i_vlr);
allow_yn = 1;
prefflag = 0;
po_map_version = 0;
po_timer_s_yn = 0;
po_context_yn = 0;
po_ns_relay_reason = 0;
memset((char *)po_prev_vlr.arr,'\0', sizeof((char *)po_prev_vlr.arr));
po_prev_vlr.len=0;
memset((char *)po_clg_address.arr,'\0', sizeof((char *)po_clg_address.arr));
po_clg_address.len=0;
po_sl_relay_reason.len = 0;
memset((char *)po_sl_relay_reason.arr, '\0', sizeof((char *)po_sl_relay_reason.arr));
po_plantype.len = 0;
memset((char *)po_plantype.arr, '\0', sizeof((char *)po_plantype.arr));
po_planname.len = 0;
memset((char *)po_planname.arr, '\0', sizeof((char *)po_planname.arr));
po_network_name.len = 0;
memset((char *)po_network_name.arr, '\0', sizeof((char *)po_network_name.arr));
po_list_id.len = 0;
memset((char *)po_list_id.arr, '\0', sizeof((char *)po_list_id.arr));
po_list_priority = 0;
po_attemptcount = 0;
po_error_code = 0;
po_errorcode = 0;
po_hlr_error_code =0;
po_invoke_id =0;
*mapVersion = 0;
*timerS = 0;
*o_inContext = 0;
PGresult *res = PQexec(con, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(con));
PQclear(res);
PQfinish(con);
exit(1);
}
PQclear(res);
//allow_yn,po_ns_relay_reason,po_sl_relay_reason=======these are output paraneters (total 21 output parameters are there but checking purpose iam passing only 3 parameters)
printf("imsi==%s\tvlr==%s\thlr==%s\tmessahe===%d\tmsg_type==%d\n",m_h_imsi.arr,m_h_vlraddress.arr,m_h_hlraddress.arr,m_ht_message,m_ht_msgType);
snprintf(buffer, sizeof(buffer),"SELECT smp_ss7_mig_dev.pr_pkg_oss_networkserver_pr_sp_apply_sl_yn('%s','%s','%s', %d ,%d)",m_h_imsi.arr,m_h_hlraddress.arr,m_h_vlraddress.arr,m_ht_message,m_ht_msgType,allow_yn,po_ns_relay_reason,po_sl_relay_reason);
res = PQexec(con,buffer);
if (PQresultStatus(res) != PGRES_TUPLES_OK) {
printf("No data retrieved\n");
PQclear(res);
PQfinish(con);
exit(1);
}
PQclear(res);
res = PQexec(con, "END");
PQclear(res);
