1 PACKAGE CS_Routing_UTL AUTHID CURRENT_USER AS
2 /* $Header: csurtes.pls 115.3 2000/02/29 19:46:42 pkm ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Type : emp_tbl_type
6 -- Usage : Used by the Service Routing functions to return a group
7 -- of employee IDs
8 -- Description : This pre-defined table type stores a collection of employee
9 -- IDs.
10 ------------------------------------------------------------------------------
11
12 TYPE emp_tbl_type IS TABLE OF PER_ALL_PEOPLE_F.PERSON_ID%TYPE
13 INDEX BY BINARY_INTEGER;
14
15 ------------------------------------------------------------------------------
16 -- Function : Get_Customer_From_RequestNum
17 -- Usage : Used by the Routing module to get the customer ID from the
18 -- service request number
19 -- Description : This function retrieves a customer ID from the
20 -- CS_INCIDENTS_ALL table given a service request number.
21 -- Parameters :
22 -- p_request_number IN VARCHAR2(64) Required
23 --
24 -- Return : NUMBER
25 -- If there is no customer associated with the given service
26 -- request, or the service request does not exist, this
27 -- function returns NULL.
28 ------------------------------------------------------------------------------
29
30 FUNCTION Get_Customer_From_RequestNum
31 ( p_request_number IN VARCHAR2
32 )
33 RETURN NUMBER;
34
35 ------------------------------------------------------------------------------
36 -- Function : Get_Customer_From_SerialNum
37 -- Usage : Used by the Routing module to get the customer ID from the
38 -- serial number
39 -- Description : This function retrieves a customer ID from the
40 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
41 -- product. If there are more than one customer associated
42 -- with the given serial number, this function will return the
43 -- first customer ID that it retrieves.
44 -- Parameters :
45 -- p_serial_number IN VARCHAR2(30) Required
46 --
47 -- Return : NUMBER
48 -- If the given serial number does not exist, this function
49 -- returns NULL.
50 ------------------------------------------------------------------------------
51
52 FUNCTION Get_Customer_From_SerialNum
53 ( p_serial_number IN VARCHAR2
54 )
55 RETURN NUMBER;
56
57 ------------------------------------------------------------------------------
58 -- Function : Get_Customer_From_System_Name
59 -- Usage : Used by the Routing module to get the customer ID from the
60 -- system name
61 -- Description: This function retrieves a customer ID from the CS_SYSTEMS_ALL
62 -- table given a system in the installed base.
63 -- Parameters :
64 -- p_system_name IN VARCHAR2(50) Required
65 --
66 -- Return : NUMBER
67 -- If the given system does not exist, this function returns
68 -- NULL.
69 ------------------------------------------------------------------------------
70
71 FUNCTION Get_Customer_From_System_Name
72 ( p_system_name IN VARCHAR2
73 )
74 RETURN NUMBER;
75
76 ------------------------------------------------------------------------------
77 -- Function : Get_CP_From_RequestNum
78 -- Usage : Used by the Routing module to get the customer product ID
79 -- from the service request number
80 -- Description : This function retrieves a customer product ID from the
81 -- CS_INCIDENTS_ALL table given a service request number.
82 -- Parameters :
83 -- p_request_number IN VARCHAR2(64) Required
84 --
85 -- Return : NUMBER
86 -- If there is no customer product associated with the given
87 -- service request, or the service request does not exist,
88 -- this function returns NULL.
89 ------------------------------------------------------------------------------
90
91 FUNCTION Get_CP_From_RequestNum
92 ( p_request_number IN VARCHAR2
93 )
94 RETURN NUMBER;
95
96 ------------------------------------------------------------------------------
97 -- Function : Get_CP_From_SerialNum
98 -- Usage : Used by the Routing module to get the customer product ID
99 -- from the serial number
100 -- Description : This function retrieves a customer product ID from the
101 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
102 -- product. If there are more than one customer product
103 -- associated with the given serial number, this function will
104 -- return the first customer product ID that it retrieves.
105 -- Parameters :
106 -- p_serial_number IN VARCHAR2(30) Required
107 --
108 -- Return : NUMBER
109 -- If the given serial number does not exist, this function
110 -- returns NULL.
111 ------------------------------------------------------------------------------
112
113 FUNCTION Get_CP_From_SerialNum
114 ( p_serial_number IN VARCHAR2
115 )
116 RETURN NUMBER;
117
118 ------------------------------------------------------------------------------
119 -- Function : Get_Product_From_RequestNum
120 -- Usage : Used by the Routing module to get the inventory item ID
121 -- from the service request number
122 -- Description : This function retrieves an inventory item ID from the
123 -- CS_INCIDENTS_ALL table given a service request number.
124 -- Parameters :
125 -- p_request_number IN VARCHAR2(64) Required
126 --
127 -- Return : NUMBER
128 -- If there is no inventory item associated with the given
129 -- service request, or the service request does not exist,
130 -- this function returns NULL.
131 ------------------------------------------------------------------------------
132
133 FUNCTION Get_Product_From_RequestNum
134 ( p_request_number IN VARCHAR2
135 )
136 RETURN NUMBER;
137
138 ------------------------------------------------------------------------------
139 -- Function : Get_Product_From_SerialNum
140 -- Usage : Used by the Routing module to get the inventory item ID
141 -- from the serial number
142 -- Description : This function retrieves an inventory item ID from the
143 -- CS_CUSTOMER_PRODUCTS_ALL table given a serial number of a
144 -- product.
145 -- Parameters :
146 -- p_serial_number IN VARCHAR2(30) Required
147 --
148 -- Return : NUMBER
149 -- If the given serial number does not exist, this function
150 -- returns NULL.
151 ------------------------------------------------------------------------------
152
153 FUNCTION Get_Product_From_SerialNum
154 ( p_serial_number IN VARCHAR2
155 )
156 RETURN NUMBER;
157
158 ------------------------------------------------------------------------------
159 -- Function : Get_Owner_Of_Request
160 -- Usage : Used by the Routing module to get the service request owner
161 -- Description : This function retrieves the employee ID from the
162 -- CS_INCIDENTS_ALL table given a service request number.
163 -- Parameters :
164 -- p_request_number IN VARCHAR2(64) Required
165 --
166 -- Return : NUMBER
167 -- If the given service request does not exist, this function
168 -- returns NULL.
169 ------------------------------------------------------------------------------
170
171 FUNCTION Get_Owner_Of_Request
172 ( p_request_number IN VARCHAR2
173 )
174 RETURN NUMBER;
175
176 ------------------------------------------------------------------------------
177 -- Function : Get_Employees_From_SR_Type
178 -- Usage : Used by the Routing module to get the employee list
179 -- Description : This function retrieves the employee list from the
180 -- CS_GROUP_LEVEL_ASSIGNS table given a service request type.
181 -- Parameters :
182 -- p_incident_number IN NUMBER Required
183 -- x_emp_tbl OUT emp_tbl_type
184 --
185 -- Return : NUMBER
186 -- This function returns the number of employees assigned to
187 -- the given system type (0 if there is no employee
188 -- assigned to
189 -- the customer).
190 ------------------------------------------------------------------------------
191 FUNCTION Get_Employees_From_SR_Type
192 ( p_incident_number IN NUMBER,
193 x_emp_tbl OUT emp_tbl_type
194 )
195 RETURN NUMBER;
196
197
198
199 END CS_Routing_UTL;