-- MySQL dump 10.13 Distrib 5.1.35, for sun-solaris2.10 (sparc) -- -- Host: localhost Database: aNumberDB -- ------------------------------------------------------ -- Server version 5.1.35-enterprise-commercial-pro-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `aNumberDB` -- USE `aNumberDB`; /*!50003 DROP PROCEDURE IF EXISTS `P_aNum` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = latin1 */ ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50003 PROCEDURE `P_aNum`( ii_aNumIndex smallint, ii_pType tinyint unsigned, ii_callType tinyint unsigned, is_cai_digits varchar(24), is_CN_digits varchar(15), is_CallingPID_digits varchar(15)) main: begin declare ls_ANI varchar(15); declare ls_ANI_NPA varchar(3); declare ls_ANI_NPANXX varchar(6); declare li_aniIndex smallint; declare li_vns tinyint unsigned; declare li_natlBlock tinyint unsigned; declare li_intlBlock tinyint unsigned; declare li_operBlock tinyint unsigned; declare li_allBlock tinyint unsigned; declare li_block8XX tinyint unsigned; declare li_cosIndex smallint; declare ls_pacPrompt varchar(10); declare li_pacPrompt int; declare li_pacLength tinyint unsigned; declare li_pacCodeIndex int; declare li_pacFeatureIndex smallint; declare li_treatment tinyint unsigned; declare li_ccBlockFlag tinyint unsigned; declare li_countryCodeIndex smallint; declare li_natlBlockFlag tinyint unsigned; declare li_natlScreenIndex smallint; declare li_natlInterLataBlock tinyint unsigned; declare li_natlIntraLataBlock tinyint unsigned; declare li_natlLocalBlock tinyint unsigned; declare li_natlOnnetBlock tinyint unsigned; declare li_oaBlock tinyint unsigned; declare li_blockFlag tinyint unsigned; declare ls_announcementID varchar(10); declare li_announcementID int; declare li_natlGuidePlanIndex int; declare li_intlGuidePlanIndex int; declare li_oaGuidePlanIndex int; declare li_cdpnGuidePlan smallint; declare li_rteGuidePlan smallint; declare li_rteIndex int; declare li_serviceType tinyint unsigned; declare li_daIndex smallint; declare li_npa700Index smallint; declare li_exceptRte smallint; declare li_jurisdictionRule tinyint unsigned; declare li_guidePlanIndex int; declare li_return_code int; declare li_returnTreatment tinyint unsigned; declare li_saveTreatment tinyint unsigned; set li_aniIndex = 0; set ls_ANI = is_CN_digits; set li_returnTreatment = 0; set li_blockFlag = 0; set li_vns = 0; set li_cdpnGuidePlan = 0; set li_rteGuidePlan = 0; set li_rteIndex = 0; set li_serviceType = 0; set li_daIndex = 0; set li_npa700Index = 0; set li_exceptRte = 0; set li_pacPrompt = 0; set li_pacLength = 0; set li_guidePlanIndex = 0; set li_jurisdictionRule = 0; if ls_ANI = '' or ls_ANI IS NULL then if char_length(is_CallingPID_digits) = 10 then set ls_ANI = is_CallingPID_digits; end if; end if; if (char_length(ls_ANI)) < 10 or ls_ANI IS NULL then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,85; select 0; leave main; end if; select aniIndex into li_aniIndex from ANumber where ii_aNumIndex = aNumIndex limit 1; if FOUND_ROWS() = 0 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0; select 1; leave main; end if; select vns, natlBlock, intlBlock, operBlock, allBlock, block8XX, cosIndex, pacFeatureIndex, pacCodeIndex, pacFeatureIndex, treatment into li_vns, li_natlBlock, li_intlBlock, li_operBlock, li_allBlock, li_block8XX, li_cosIndex, li_pacFeatureIndex, li_pacCodeIndex, li_pacFeatureIndex, li_treatment from ANI where li_aniIndex = aniIndex and ls_ANI = ani limit 1; if FOUND_ROWS() = 0 then set ls_ANI_NPA = substring(ls_ANI,1,3); set ls_ANI_NPANXX = substring(ls_ANI,1,6); select natlBlock, intlBlock, operBlock, allBlock, block8XX, cosIndex, pacFeatureIndex, pacCodeIndex, pacFeatureIndex, treatment into li_natlBlock, li_intlBlock, li_operBlock, li_allBlock, li_block8XX, li_cosIndex, li_pacFeatureIndex, li_pacCodeIndex, li_pacFeatureIndex, li_treatment from ANI_NPANXX where li_aniIndex = aniIndex and (ls_ANI_NPA = aniNPANXX or ls_ANI_NPANXX = aniNPANXX) order by aniNPANXX asc limit 1; if FOUND_ROWS() = 0 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0; select 1; leave main; end if; end if; if (ii_callType = 3) then if (li_natlBlock = 1) or (li_allBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (ii_callType = 5) then if (li_intlBlock = 1) or (li_allBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (ii_callType = 6) then set li_pacFeatureIndex =0; set li_pacCodeIndex = 0; if (li_operBlock = 1) or (li_allBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (ii_callType = 800) then if (li_block8XX = 1) or (li_allBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (li_returnTreatment = 1) then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,li_treatment; select 0; leave main; end if; call P_proc_not_found( li_cosIndex ); if (li_cosIndex = 0) then if (li_pacFeatureIndex > 0) and (li_vns = 0) then select pacLength, pacPrompt into li_pacLength, ls_pacPrompt from PACFeature where li_pacFeatureIndex = pacFeatureIndex limit 1; if FOUND_ROWS() = 1 then if (ls_pacPrompt is null) then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; end if; set li_pacPrompt = convert(ls_pacPrompt,unsigned); select 0,0,0,0,0,0,0,0,0,0,0,li_pacCodeIndex,li_pacPrompt,li_pacLength,0,0; select 1; leave main; else select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; end if; else select li_vns,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0; select 1; leave main; end if; end if; if (li_vns = 1) then select li_vns,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0; select 0; leave main; end if; select ccBlockFlag, countryCodeIndex, natlBlockFlag, natlScreenIndex, natlInterLataBlock, natlIntraLataBlock, natlLocalBlock, natlOnnetBlock, intlBlock, oaBlock, treatment, announcementID, natlGuidePlanIndex, intlGuidePlanIndex, oaGuidePlanIndex into li_ccBlockFlag, li_countryCodeIndex, li_natlBlockFlag, li_natlScreenIndex, li_natlInterLataBlock, li_natlIntraLataBlock, li_natlLocalBlock, li_natlOnnetBlock, li_intlBlock, li_oaBlock, li_treatment, ls_announcementID, li_natlGuidePlanIndex, li_intlGuidePlanIndex, li_oaGuidePlanIndex from COSScreen where li_cosIndex = cosIndex limit 1; if FOUND_ROWS() = 0 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; end if; if (ls_announcementID is null) then set ls_announcementID = '0'; end if; set li_announcementID = convert(ls_announcementID,unsigned); if (ii_pType < 4) or (ii_pType = 5) then if (ii_callType = 3) then set li_guidePlanIndex = li_natlGuidePlanIndex; if (li_natlInterLataBlock = 1) and (li_natlIntraLataBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; end if; if (ii_callType = 5) then if (li_intlBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (ii_callType = 6) then if (li_oaBlock = 1) then if (li_treatment > 0) then set li_returnTreatment = 1; else set li_treatment = 76; set li_returnTreatment = 1; end if; end if; end if; if (li_returnTreatment = 1) then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,li_announcementID,li_treatment; select 0; leave main; end if; set li_saveTreatment = li_treatment; if (ii_callType = 3) then if (li_natlScreenIndex > 0) then call P_cos_national_screen( li_natlScreenIndex, li_natlBlockFlag, is_cai_digits, li_natlGuidePlanIndex, li_blockFlag, li_treatment, ls_announcementID); end if; set li_guidePlanIndex = li_natlGuidePlanIndex; end if; if (ii_callType) = 5 then if ( li_countryCodeIndex > 0 ) then call P_cos_country_code_screen ( li_countryCodeIndex, li_ccBlockFlag, is_cai_digits, li_intlGuidePlanIndex, li_blockFlag, li_treatment, ls_announcementID); end if; end if; if @li_blockFlag = 1 then if li_treatment = 0 then set li_treatment = li_saveTreatment; end if; end if; if (ls_announcementID is null) then set ls_announcementID = '0'; end if; set li_announcementID = convert(ls_announcementID,unsigned); if li_blockFlag = 1 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,li_announcementID,li_treatment; select 0; leave main; end if; if li_pacFeatureIndex > 0 then select pacLength, pacPrompt into li_pacLength, ls_pacPrompt from PACFeature where li_pacFeatureIndex = pacFeatureIndex limit 1; if FOUNDROWS() = 0 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; else if (ls_pacPrompt is null) then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; end if; set li_pacPrompt = convert(ls_pacPrompt,unsigned); end if; end if; if li_guidePlanIndex = 0 then select 0,0,0,0,0,0,0,0,0,0,0,li_pacCodeIndex,li_pacPrompt,li_pacLength,0,0; select 1; leave main; end if; select cdpnGuidePlan, rteGuidePlan, rteIndex, serviceType, daIndex, npa700Index, exceptRte, jurisdictionRule into li_cdpnGuidePlan, li_rteGuidePlan, li_rteIndex, li_serviceType, li_daIndex, li_npa700Index, li_exceptRte, li_jurisdictionRule from AGuidePlan where li_guidePlanIndex = guidePlanIndex limit 1; if FOUND_ROWS() = 0 then select 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,131; select 0; leave main; end if; select li_vns, li_cdpnGuidePlan, li_intlGuidePlanIndex, li_oaGuidePlanIndex, li_rteGuidePlan, li_rteIndex, li_serviceType, li_daIndex, li_npa700Index, li_exceptRte, li_jurisdictionRule, li_pacCodeIndex, li_pacPrompt, li_pacLength, 0, 0; select 0; end */;; DELIMITER ; -- Dump completed on 2010-12-03 18:29:42