1 PACKAGE BODY CCT_CALLCENTER_UTIL_PUB as
2 /* $Header: cctcutlb.pls 120.0 2005/06/02 09:41:01 appldev noship $ */
3
4 Procedure getMiddlewareParam(p_resource_id IN Number Default Null,
5 x_middleware_id IN out nocopy Number,
6 x_param_value out nocopy CCT_KEYVALUE_VARR)
7 IS
8
9 Cursor c_agent_mware(p_agent_id Number)
10 Is
11 Select m.middleware_id
12 from cct_middlewares m,cct_telesets t,cct_agent_rt_stats a
13 where a.agent_id=p_agent_id
14 and a.client_id=t.teleset_id
15 and t.middleware_id=m.middleware_id;
16
17 Cursor c_mware_params(p_middleware_id Number)
18 Is
19 Select upper(p.name),v.value
20 from cct_middleware_params p,cct_middleware_values v,cct_middlewares m
21 where m.middleware_id=p_middleware_id
22 and m.middlewarE_type_id=p.middleware_type_id
23 and p.middleware_param_id=v.middleware_param_id
24 and nvl(v.f_deletedflag,'N')<>'D';
25 l_middleware_id Number:=G_MIDDLEWARE_NOT_FOUND;
26 l_param VARCHAR2(255);
27 l_value VARCHAR2(255);
28 l_paramValue CCT_KEYVALUE_VARR:= CCT_KEYVALUE_VARR();
29 l_result VARCHAR2(32);
30 Begin
31 If p_resource_id is not null then
32 Open c_agent_mware(p_resource_id);
33
34 Fetch c_agent_mware into l_middleware_id;
35
36 Close c_agent_mware;
37
38 End if;
39 If l_middleware_id<>G_MIDDLEWARE_NOT_FOUND THEN
40 Open c_mware_params(l_middleware_id);
41
42 Loop
43 Fetch c_mware_params into l_param,l_value;
44 l_result:=CCT_COLLECTION_UTIL_PUB.PUT(l_paramValue,l_param,l_value);
45 Exit When c_mware_params%NOTFOUND;
46 End loop;
47
48 Close c_mware_params;
49 End if;
50
51 x_middleware_id:=l_middleware_id;
52 x_param_value:=l_paramValue;
53
54 Exception
55
56 When others then
57 x_middleware_id:=l_middleware_id;
58 x_param_value:=l_paramValue;
59 End;
60
61
62 Procedure getDialableNumber(p_resource_id IN Number,
63 p_country_code In Number,
64 p_area_code IN Number,
65 p_localNumber In Number,
66 x_dialableNumber out nocopy Number)
67 IS
68 l_site_overlay VARCHAR2(32);
69 l_outgoing_prefix VARCHAR2(32):=null;
70 l_site_area_code VARCHAR2(32):=null;
71 l_site_country_code VARCHAR2(32):=null;
72 l_domestic_prefix VARCHAR2(32):=null;
73 l_idd_prefix VARCHAR2(32):=null;
74 l_local_num_max_length VARCHAR2(32):=null;
75
76 l_param_value CCT_KEYVALUE_VARR;
77 l_middlewarE_id Number;
78 l_defaultDialNumber VARCHAR2(64);
79 l_key_exists VARCHAR2(32);
80 l_value VARCHAR2(32);
81 Begin
82
83 l_defaultDialNumber:=To_char(p_country_code)||to_char(p_area_code)||to_char(p_localNumber);
84 --get the Middleware Parameter Values
85 getMiddlewareparam(p_resource_id,l_middleware_id,l_param_value);
86
87 If l_middleware_id<>G_MIDDLEWARE_NOT_FOUND THEN
88
89 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'SITE_OVERLAY',l_key_exists);
90 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
91 l_site_overlay:=l_value;
92 end if;
93 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'OUTGOING_PREFIX',l_key_exists);
94 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
95 l_outgoing_prefix:=l_value;
96 end if;
97 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'SITE_AREA_CODE',l_key_exists);
98 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
99 l_site_area_code:=l_value;
100 end if;
101 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'SITE_COUNTRY_CODE',l_key_exists);
102 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
103 l_site_country_code:=l_value;
104 end if;
105 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'DOMESTIC_PREFIX',l_key_exists);
106 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
107 l_domestic_prefix:=l_value;
108 end if;
109 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'IDD_PREFIX',l_key_exists);
110 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
111 l_idd_prefix:=l_value;
112 end if;
113 l_value:=CCT_COLLECTION_UTIL_PUB.GET(l_param_value,'LOCAL_NUM_MAX_LENGTH',l_key_exists);
114 if(l_key_exists=CCT_COLLECTION_UTIL_PUB.G_TRUE) then
115 l_local_num_max_length:=l_value;
116 end if;
117
118 x_dialableNumber:=TO_NUMBER(L_OUTGOING_PREFIX||To_char(p_country_code)||to_char(p_area_code)||to_char(p_localNumber));
119
120 if((length(to_char(p_localNumber))<=to_number(l_local_num_max_length)) AND
121 p_country_code is null and p_area_code is null) THEN
122 -- it is a local number
123 if(upper(l_site_overlay)='YES') THEN
124 -- local number requires area code+ phone number
125 -- add outgoing prefix and area code
126 x_dialableNumber:=to_number(l_outgoing_prefix||l_site_area_code||to_char(p_localNumber));
127 else
128 x_dialableNumber:=to_number(l_outgoing_prefix||to_char(p_localNumber));
129
130 end if;
131 Else
132 if(p_country_code is null) OR (p_country_code=to_number(l_site_country_code)) THEN
133 -- Dialing within country
134
135 if (p_area_code is not null) AND (p_area_code <> to_number(l_site_area_code)) THEN
136 -- long distance number
137 x_dialableNumber:=to_number(l_outgoing_prefix||l_domestic_prefix||to_char(p_area_code)||to_char(p_localNumber));
138 else
139 if(upper(l_site_overlay)='YES') THEN
140 -- local number requires area code+ phone number
141 -- add outgoing prefix and area code
142 x_dialableNumber:=to_number(l_outgoing_prefix||to_char(p_area_code)||to_char(p_localNumber));
143 else
144 x_dialableNumber:=to_number(l_outgoing_prefix||to_char(p_localNumber));
145 end if;
146 END IF;
147 ELSE
148 -- INTERNATIONAL DIALING
149 x_dialableNumber:=to_number(l_outgoing_prefix||l_IDD_prefix||TO_CHAR(P_COUNTRY_CODE)||to_char(p_area_code)||to_char(p_localNumber));
150 END IF;
151 END IF;
152 Else
153 x_dialableNumber:=to_number(l_defaultDialNumber);
154 End If;
155 Exception
156 When others then
157 x_dialableNumber:=to_number(l_defaultDialNumber);
158 End;
159
160 End CCT_CALLCENTER_UTIL_PUB;
[Home] [Help]