[Home] [Help]
PACKAGE BODY: APPS.CCT_SERVICEROUTING_PUB
Source
1 PACKAGE BODY CCT_ServiceRouting_PUB as
2 /* $Header: cctrwcsb.pls 115.12 2003/08/23 01:49:33 gvasvani ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CCT_ServiceRouting_PUB';
5
6 /*------------------------------------------------------------------------
7 Service Routing Workflow Activities
8 *------------------------------------------------------------------------*/
9
10
11
12
13
14 /* -----------------------------------------------------------------------
15 Activity Name : Set_Customer_Filter (filter node)
16 To filter the agents by Customer ID
17 Prerequisites : The Customer initialization phase must be completed
18 before using this filter
19 IN
20 itemtype - item type
21 itemkey - item key
22 actid - process activity instance id
23 funmode - execution mode
24 OUT
25 No output
26 ITEM ATTRIBUTES REFERENCED
27 CUSTID - the customer ID
28 CALLID - the call ID
29
30 Implementation : No Workflow output is expected from this function.
31 The CS API returns the number of agents.
32 The list of agents is returned by the CS API as a PL/SQL table.
33 Loop through the table and insert each agent into the CCT_TEMPAGENTS table.
34 *-----------------------------------------------------------------------*/
35 procedure Set_Customer_Filter (
36 itemtype in varchar2
37 , itemkey in varchar2
38 , actid in number
39 , funmode in varchar2
40 , resultout in out nocopy varchar2)
41 IS
42 l_proc_name VARCHAR2(30) := 'Set_Customer_Filter';
43 l_num_agents NUMBER := 0;
44 l_customer_ID NUMBER;
45 l_call_ID VARCHAR2(32);
46 l_agents_tbl JTF_TERRITORY_PUB.WinningTerrMember_tbl_type;
47 l_acct_rec JTF_TERRITORY_PUB.JTF_Account_rec_type;
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(2000);
50 l_return_status varchar2(01);
51 l_ret BOOLEAN;
52 l_resource_type VARCHAR2(60) := 'RS_EMPLOYEE';
53 l_role VARCHAR2(60) := NULL;
54 l_counter NUMBER;
55 l_org_id NUMBER := NULL;
56 BEGIN
57 -- set default result
58 resultout := wf_engine.eng_completed ;
59
60 if (funmode = 'RUN') then
61 -- WF attribute TERRITORYORGID must be set to for this function to work
62 l_org_id := WF_ENGINE.GetItemAttrNumber(
63 itemtype, itemkey, 'TERRITORYORGID');
64
65 IF l_org_id IS NULL THEN
66 return;
67 END IF;
68
69 fnd_client_info.set_org_context(l_org_id);
70
71 l_acct_rec.party_id := WF_ENGINE.GetItemAttrNumber(
72 itemtype, itemkey, CCT_INTERACTIONKEYS_PUB.KEY_CUSTOMER_ID);
73
74 l_call_ID := WF_ENGINE.GetItemAttrText(
75 itemtype, itemkey, 'OCCTMEDIAITEMID');
76
77 IF ( (l_acct_rec.party_id IS NULL) OR (l_call_ID IS NULL) ) THEN
78 return;
79 END IF;
80
81 l_ret := JTF_TERRITORY_GLOBAL_PUB.RESET;
82 --dbms_output.put_line ('IN CS FILTER BEFORE JTF ');
83
84 -- Call JTF Territory
85 JTF_TERR_SALES_PUB.Get_WinningTerrMembers(
86 p_api_version_number => '1.0'
87 , p_TerrAccount_Rec => l_acct_rec
88 , p_resource_type => l_resource_type
89 , p_role => l_role
90 , x_return_status => l_return_status
91 , x_msg_count => l_msg_count
92 , x_msg_data => l_msg_data
93 , x_terrresource_tbl => l_agents_tbl);
94
95 IF (l_agents_tbl.count = 0) THEN
96 return;
97 END IF;
98
99 -- dbms_output.put_line ('IN CS FILTER AFTER JTF CALL');
100 -- insert the agents into the CCT_TEMPAGENTS table
101 CCT_RoutingWorkflow_UTL.InsertResults
102 (l_call_ID, 'CS_CUSTOMER_FILTER' , l_agents_tbl);
103
104 end if;
105
106 EXCEPTION
107 WHEN OTHERS THEN
108 --dbms_output.put_line ('ERROR'|| sqlerrm);
109 WF_CORE.Context(G_PKG_NAME, l_proc_name,
110 itemtype, itemkey, to_char(actid), funmode);
111 RAISE;
112
113 END Set_Customer_Filter;
114
115
116
117
118
119
120 procedure Set_Product_Filter (
121 itemtype in varchar2
122 , itemkey in varchar2
123 , actid in number
124 , funmode in varchar2
125 , resultout in out nocopy varchar2) IS
126
127 l_proc_name VARCHAR2(30) := 'Set_Product_Filter';
128 l_num_agents NUMBER := 0;
129 l_product_ID NUMBER;
130 l_call_ID VARCHAR2(32);
131 l_agents_tbl JTF_TERRITORY_PUB.WinningTerrMember_tbl_type;
132 l_acct_rec JTF_TERRITORY_PUB.JTF_Oppor_rec_type;
133 l_msg_count NUMBER;
134 l_msg_data VARCHAR2(2000);
135 l_return_status varchar2(01);
136 l_ret BOOLEAN;
137 l_resource_type VARCHAR2(60) := 'RS_EMPLOYEE';
138 l_role VARCHAR2(60) := NULL;
139 l_customer_pdt_ID VARCHAR2(60) := NULL ;
140 l_org_id NUMBER := NULL;
141 BEGIN
142 -- set default result
143 resultout := wf_engine.eng_completed ;
144
145 if (funmode = 'RUN') then
146 -- WF attribute TERRITORYORGID must be set to for this function to work
147 l_org_id := WF_ENGINE.GetItemAttrNumber(
148 itemtype, itemkey, 'TERRITORYORGID');
149
150 IF l_org_id IS NULL THEN
151 return;
152 END IF;
153
154 fnd_client_info.set_org_context(l_org_id);
155
156 l_acct_rec.INVENTORY_ITEM_ID := WF_ENGINE.GetItemAttrNumber(
157 itemtype, itemkey, 'INVENTORYITEMID');
158
159 l_call_ID := WF_ENGINE.GetItemAttrText(
160 itemtype, itemkey, 'OCCTMEDIAITEMID');
161
162 IF l_acct_rec.INVENTORY_ITEM_ID IS NULL THEN
163 l_customer_pdt_id := to_number(WF_ENGINE.GetItemAttrText(
164 itemtype, itemkey, 'CUSTOMERPRODUCTID'));
165 BEGIN
166 SELECT inventory_item_id
167 INTO l_acct_rec.INVENTORY_ITEM_ID
168 FROM csi_item_instances
169 WHERE instance_id = l_customer_pdt_id ;
170 EXCEPTION
171 WHEN others THEN
172 l_acct_rec.INVENTORY_ITEM_ID := null;
173 END;
174 END IF;
175
176 IF ( (l_acct_rec.INVENTORY_ITEM_ID IS NULL) OR (l_call_ID IS NULL) ) THEN
177 return;
178 END IF;
179
180 l_ret := JTF_TERRITORY_GLOBAL_PUB.RESET;
181
182 -- Call JTF Territory
183 JTF_TERR_SALES_PUB.Get_WinningTerrMembers(
184 p_api_version_number => '1.0'
185 , p_TerrOppor_Rec => l_acct_rec
186 , p_resource_type => l_resource_type
187 , p_role => l_role
188 , x_return_status => l_return_status
189 , x_msg_count => l_msg_count
190 , x_msg_data => l_msg_data
191 , x_terrresource_tbl => l_agents_tbl);
192
193
194 IF (l_agents_tbl.count = 0) THEN
195 return;
196 END IF;
197
198 -- insert the agents into the CCT_TEMPAGENTS table
199 CCT_RoutingWorkflow_UTL.InsertResults
200 (l_call_ID, 'CS_PRODUCT_FILTER' , l_agents_tbl);
201
202 end if;
203 EXCEPTION
204 WHEN OTHERS THEN
205 WF_CORE.Context(G_PKG_NAME, l_proc_name,
206 itemtype, itemkey, to_char(actid), funmode);
207 RAISE;
208
209 END Set_Product_Filter ;
210
211
212
213 /* -----------------------------------------------------------------------
214 Activity Name : Set_Request_Owner_Filter (filter node)
215 To filter the agents by request number
216 Prerequisites : The ?? initialization phase must be completed
217 before using this filter
218 IN
219 itemtype - item type
220 itemkey - item key
221 actid - process activity instance id
222 funmode - execution mode
223 OUT
224 No output
225 ITEM ATTRIBUTES REFERENCED
226 CUSTID - the customer ID
227 REQUEST_OWNER_ID - the call ID
228
229 Implementation : No Workflow output is expected from this function.
230
231 *-----------------------------------------------------------------------*/
232 procedure Set_Request_Owner_Filter (
233 itemtype in varchar2
234 , itemkey in varchar2
235 , actid in number
236 , funmode in varchar2
237 , resultout in out nocopy varchar2) IS
238
239 l_proc_name VARCHAR2(30) := ' Set_Request_Owner_Filter' ;
240 l_RequestNum VARCHAR2(64);
241 l_agent_ID NUMBER;
242 l_call_ID VARCHAR2(32);
243 BEGIN
244 -- set default result
245 resultout := wf_engine.eng_completed ;
246 if (funmode = 'RUN') then
247 l_RequestNum := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'SERVICEREQUESTNUM' );
248 l_call_ID := WF_ENGINE.GetItemAttrText(itemtype, itemkey, 'OCCTMEDIAITEMID');
249
250 IF ( (l_RequestNum IS NULL) OR (l_call_ID IS NULL) ) THEN
251 return;
252 END IF;
253
254 -- call CS API
255 l_agent_ID := CS_ROUTING_UTL.Get_Owner_Of_Request (
256 p_Request_Number => l_RequestNum ) ;
257 BEGIN
258 Select incident_owner_id into l_agent_ID
259 from cs_incidents_all_b
260 where incident_number=l_RequestNum;
261 END;
262
263 IF (l_agent_ID IS NULL) THEN
264 return;
265 END IF;
266
267 -- insert the agents into the CCT_TEMPAGENTS table
268 CCT_RoutingWorkflow_UTL.InsertResults
269 (l_call_ID, 'CS_REQUEST_OWNER_FILTER' , l_agent_ID);
270
271 end if;
272
273 EXCEPTION
274 WHEN OTHERS THEN
275 WF_CORE.Context(G_PKG_NAME, l_proc_name, itemtype,
276 itemkey, to_char(actid), funmode);
277 RAISE;
278 END Set_Request_Owner_Filter ;
279
280 END CCT_ServiceRouting_PUB;