1 PACKAGE BODY CS_Routing_UTL AS
2 /* $Header: csurteb.pls 115.4 2000/02/29 19:46:40 pkm ship $ */
3
4
5 ------------------------------------------------------------------------------
6 -- Function : Get_Customer_From_RequestNum
7 -- Usage : Used by the Routing module to get the customer ID from the
8 -- service request number
9 -- Description : This function retrieves a customer ID from the
10 -- CS_INCIDENTS_ALL table given a service request number.
11 -- Parameters :
12 -- p_request_number IN VARCHAR2(64) Required
13 --
14 -- Return : NUMBER
15 -- If there is no customer associated with the given service
16 -- request, or the service request does not exist, this
17 -- function returns NULL.
18 ------------------------------------------------------------------------------
19
20
21 FUNCTION Get_Customer_From_RequestNum
22 ( p_request_number IN VARCHAR2)
23 RETURN NUMBER IS
24 p_customer_id cs_incidents_all.customer_id%type;
25
26 --
27 -- get customer ID given a requesr number
28 --
29 CURSOR c_customer_id IS
30 SELECT customer_id
31 FROM cs_incidents_all
32 WHERE incident_number = p_request_number;
33 BEGIN
34
35 OPEN c_customer_id;
36 FETCH c_customer_id INTO p_customer_id;
37 IF c_customer_id%NOTFOUND THEN
38 CLOSE c_customer_id;
39 RETURN NULL;
40 END IF;
41 CLOSE c_customer_id;
42 RETURN to_number(p_customer_id);
43 END Get_Customer_From_RequestNum;
44
45 ------------------------------------------------------------------------------
46 -- Function : Get_Customer_From_SerialNum
47 -- Usage : Used by the Routing module to get the customer ID from the
48 -- serial number
49 -- Description : This function retrieves a customer ID from the
50 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
51 -- product. If there are more than one customer associated
52 -- with the given serial number, this function will return the
53 -- first customer ID that it retrieves.
54 -- Parameters :
55 -- p_serial_number IN VARCHAR2(30) Required
56 --
57 -- Return : NUMBER
58 -- If the given serial number does not exist, this function
59 -- returns NULL.
60 ------------------------------------------------------------------------------
61
62 FUNCTION Get_Customer_From_SerialNum
63 ( p_serial_number IN VARCHAR2)
64 RETURN NUMBER IS
65 p_customer_id cs_customer_products_all.customer_id%type;
66
67 --
68 -- get customer ID given a serial number
69 --
70 CURSOR c_customer_id IS
71 SELECT customer_id
72 FROM cs_customer_products_all
73 WHERE current_serial_number = p_serial_number;
74 BEGIN
75
76 OPEN c_customer_id;
77 FETCH c_customer_id INTO p_customer_id;
78 IF c_customer_id%NOTFOUND THEN
79 CLOSE c_customer_id;
80 RETURN NULL;
81 END IF;
82 CLOSE c_customer_id;
83 RETURN to_number(p_customer_id);
84 END Get_Customer_From_SerialNum;
85
86 ------------------------------------------------------------------------------
87 -- Function : Get_Customer_From_System_Name
88 -- Usage : Used by the Routing module to get the customer ID from the
89 -- system name
90 -- Description : This function retrieves a customer ID from the CS_SYSTEMS_ALL_VL
91 -- table given a system in the installed base.
92 -- Parameters :
93 -- p_system_name IN VARCHAR2(50) Required
94 --
95 -- Return : NUMBER
96 -- If the given system does not exist, this function returns
97 -- NULL.
98 ------------------------------------------------------------------------------
99
100 FUNCTION Get_Customer_From_System_Name
101 ( p_system_name IN VARCHAR2)
102 RETURN NUMBER IS
103 p_customer_id cs_systems_all_vl.customer_id%type;
104
105 --
106 -- get customer ID given a system name
107 --
108 CURSOR c_customer_id IS
109 SELECT customer_id
110 FROM cs_systems_all_vl
111 WHERE name = p_system_name;
112 BEGIN
113
114 OPEN c_customer_id;
115 FETCH c_customer_id INTO p_customer_id;
116 IF c_customer_id%NOTFOUND THEN
117 CLOSE c_customer_id;
118 RETURN NULL;
119 END IF;
120 CLOSE c_customer_id;
121 RETURN to_number(p_customer_id);
122 END Get_Customer_From_System_Name;
123
124 ------------------------------------------------------------------------------
125 -- Function : Get_CP_From_RequestNum
126 -- Usage : Used by the Routing module to get the customer product ID
127 -- from the service request number
128 -- Description : This function retrieves a customer product ID from the
129 -- CS_INCIDENTS_ALL table given a service request number.
130 -- Parameters :
131 -- p_request_number IN VARCHAR2(64) Required
132 --
133 -- Return : NUMBER
134 -- If there is no customer product associated with the given
135 -- service request, or the service request does not exist,
136 -- this function returns NULL.
137 ------------------------------------------------------------------------------
138
139 FUNCTION Get_CP_From_RequestNum
140 ( p_request_number IN VARCHAR2)
141 RETURN NUMBER IS
142
143 p_customer_product_id cs_incidents_all.customer_product_id%type;
144
145 --
146 -- get customer product ID given a request number
147 --
148 CURSOR c_customer_product_id IS
149 SELECT customer_product_id
150 FROM cs_incidents_all
151 WHERE incident_number = p_request_number;
152
153 BEGIN
154
155 OPEN c_customer_product_id;
156 IF c_customer_product_id%NOTFOUND THEN
157 CLOSE c_customer_product_id;
158 RETURN NULL;
159 END IF;
160 FETCH c_customer_product_id INTO p_customer_product_id;
161 CLOSE c_customer_product_id;
162 RETURN to_number(p_customer_product_id);
163
164 END Get_CP_From_RequestNum;
165
166 ------------------------------------------------------------------------------
167 -- Function : Get_CP_From_SerialNum
168 -- Usage : Used by the Routing module to get the customer product ID
169 -- From the serial number
170 -- Description : This function retrieves a customer product ID from the
171 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
172 -- product. If there are more than one customer product
173 -- associated with the given serial number, this function will
174 -- return the first customer product ID that it retrieves.
175 -- Parameters :
176 -- p_serial_number IN VARCHAR2(30) Required
177 --
178 -- Return : NUMBER
179 -- If the given serial number does not exist, this function
180 -- returns NULL.
181 ------------------------------------------------------------------------------
182
183 FUNCTION Get_CP_From_SerialNum
184 ( p_serial_number IN VARCHAR2)
185 RETURN NUMBER IS
186
187 p_customer_product_id cs_customer_products_all.customer_product_id%type;
188
189 --
190 -- get customer product ID given a serial number
191 --
192 CURSOR c_customer_product_id IS
193 SELECT customer_product_id
194 FROM cs_customer_products_all
195 WHERE current_serial_number = p_serial_number;
196 BEGIN
197
198 OPEN c_customer_product_id;
199 IF c_customer_product_id%NOTFOUND THEN
200 CLOSE c_customer_product_id;
201 RETURN NULL;
202 END IF;
203 FETCH c_customer_product_id INTO p_customer_product_id;
204 CLOSE c_customer_product_id;
205 RETURN to_number(p_customer_product_id);
206 END Get_CP_From_SerialNum;
207
208 ------------------------------------------------------------------------------
209 -- Function : Get_Product_From_RequestNum
210 -- Usage : Used by the Routing module to get the inventory item ID
211 -- from the service request number
212 -- Description : This function retrieves an inventory item ID from the
213 -- CS_INCIDENTS_ALL table given a service request number.
214 -- Parameters :
215 -- p_request_number IN VARCHAR2(64) Required
216 --
217 -- Return : NUMBER
218 -- If there is no inventory item associated with the given
219 -- service request, or the service request does not exist,
220 -- this function returns NULL.
221 ------------------------------------------------------------------------------
222
223 FUNCTION Get_Product_From_RequestNum
224 ( p_request_number IN VARCHAR2)
225 RETURN NUMBER IS
226 p_inventory_item_id cs_incidents_all.inventory_item_id%type;
227
228 --
229 -- get inventory item ID given a request number
230 --
231 CURSOR c_inventory_item_id IS
232 SELECT inventory_item_id
233 FROM cs_incidents_all
234 WHERE incident_number = p_request_number;
235 BEGIN
236
237 OPEN c_inventory_item_id;
238 IF c_inventory_item_id%NOTFOUND THEN
239 CLOSE c_inventory_item_id;
240 RETURN NULL;
241 END IF;
242 FETCH c_inventory_item_id INTO p_inventory_item_id;
243 CLOSE c_inventory_item_id;
244 RETURN to_number(p_inventory_item_id);
245 END Get_Product_From_RequestNum;
246
247 ------------------------------------------------------------------------------
248 -- Function : Get_Product_From_SerialNum
249 -- Usage : Used by the Routing module to get the inventory item ID
250 -- from the serial number
251 -- Description : This function retrieves an inventory item ID from the
252 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
253 -- product.
254 -- Parameters :
255 -- p_serial_number IN VARCHAR2(30) Required
256 --
257 -- Return : NUMBER
258 -- If the given serial number does not exist, this function
259 -- returns NULL.
260 ------------------------------------------------------------------------------
261
262 FUNCTION Get_Product_From_SerialNum
263 ( p_serial_number IN VARCHAR2)
264 RETURN NUMBER IS
265 p_inventory_item_id cs_customer_products_all.inventory_item_id%type;
266
267 --
268 -- get inventory item ID given a serial number
269 --
270 CURSOR c_inventory_item_id IS
271 SELECT inventory_item_id
272 FROM cs_customer_products_all
273 WHERE current_serial_number = p_serial_number;
274 BEGIN
275
276 OPEN c_inventory_item_id;
277 IF c_inventory_item_id%NOTFOUND THEN
278 CLOSE c_inventory_item_id;
279 RETURN NULL;
280 END IF;
281 FETCH c_inventory_item_id INTO p_inventory_item_id;
282 CLOSE c_inventory_item_id;
283 RETURN to_number(p_inventory_item_id);
284 END Get_Product_From_SerialNum;
285
286 ------------------------------------------------------------------------------
287 -- Function : Get_Owner_Of_Request
288 -- Usage : Used by the Routing module to get the service request owner
289 -- Description : This function retrieves the employee ID from the
290 -- CS_INCIDENTS_ALL table given a service request number.
291 -- Parameters :
292 -- p_request_number IN VARCHAR2(64) Required
293 --
294 -- Return : NUMBER
295 -- If the given service request does not exist, this function
296 -- returns NULL.
297 ------------------------------------------------------------------------------
298
299 FUNCTION Get_Owner_Of_Request
300 ( p_request_number IN VARCHAR2)
301 RETURN NUMBER IS
302
303 p_employee_id cs_incidents_all.employee_id%type;
304
305 --
306 -- get owner given a request number
307 --
308 CURSOR c_employee_id IS
309 SELECT employee_id
310 FROM cs_incidents_all
311 WHERE incident_number = p_request_number;
312 BEGIN
313
314 OPEN c_employee_id;
315 FETCH c_employee_id INTO p_employee_id;
316 IF c_employee_id%NOTFOUND THEN
317 CLOSE c_employee_id;
318 RETURN NULL;
319 END IF;
320 RETURN p_employee_id;
321
322 END Get_Owner_Of_Request;
323
324 ------------------------------------------------------------------------------
325 -- Function : Get_Employees_From_SR_Type
326 -- Usage : Used by the Routing module to get the employee list
327 -- Description : This function retrieves the employee list from the
328 -- CS_GROUP_LEVEL_ASSIGNS table given a service request type.
329 -- Parameters :
330 -- p_incident_number IN NUMBER Required
331 -- x_emp_tbl OUT emp_tbl_type
332 --
333 -- Return : NUMBER
334 -- This function returns the number of employees assigned to
335 -- the given system type (0 if there is no employee
336 -- assigned to
337 -- the customer).
338 ------------------------------------------------------------------------------
339 FUNCTION Get_Employees_From_SR_Type
340 ( p_incident_number IN NUMBER,
341 x_emp_tbl OUT emp_tbl_type)
342 RETURN NUMBER IS
343
344 v_total_num_of_emps NUMBER:=0;
345 v_current_id cs_group_level_assigns.role_person_id%type;
346 v_incident_type_id cs_incidents_all.incident_type_id%type;
347
348 --
349 -- get service request type given an incident ID
350 --
351 CURSOR c_incident_type_id IS
352 SELECT incident_type_id
353 FROM cs_incidents_all
354 WHERE incident_number = p_incident_number;
355
356 --
357 -- get a list of employees that is responsible to a service request type
358 --
359 CURSOR c_employees IS
360 SELECT DISTINCT cgla.role_person_id
361 FROM cs_group_level_assigns cgla
362 WHERE (cgla.sr_type_id = v_incident_type_id AND
363 cgla.role_person_orig_system = 'PER')
364 UNION
365 SELECT DISTINCT paf.person_id
366 FROM cs_group_level_assigns cgla,
367 per_assignments_f paf
368 WHERE (cgla.sr_type_id = v_incident_type_id AND
369 cgla.role_person_orig_system = 'POS' AND
370 paf.position_id = cgla.role_person_id);
371
372 BEGIN
373
374 OPEN c_incident_type_id;
375 FETCH c_incident_type_id INTO v_incident_type_id;
376 IF c_incident_type_id%NOTFOUND THEN
377 CLOSE c_incident_type_id;
378 RETURN v_total_num_of_emps;
379 END IF;
380
381 OPEN c_employees;
382 LOOP
383
384 FETCH c_employees INTO v_current_id;
385 IF c_employees%NOTFOUND THEN
386 CLOSE c_employees;
387 return v_total_num_of_emps;
388 ELSE
389 x_emp_tbl(v_total_num_of_emps) := v_current_id;
390 END IF;
391 v_total_num_of_emps := v_total_num_of_emps + 1;
392
393 END LOOP;
394
395 END Get_Employees_From_SR_Type;
396
397 END CS_Routing_UTL;
398
[Home] [Help]