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;