1 PACKAGE CSC_Routing_UTL AS
2 /* $Header: cscotmrs.pls 120.2 2006/06/15 21:09:09 hbchung ship $ */
3
4 ------------------------------------------------------------------------------
5 -- Procedure: decode_ani
6 -- Usage: used by the Routing module to decode the string passed in the
7 -- IVT values to the constituent telephone number pieces required
8 -- by the get_customerid_from_ANI function.
9 -- Parameters: NI code received
10 -- Return: 4 substrings
11 --
12 ------------------------------------------------------------------------------
13 PROCEDURE decode_ANI (
14 p_ANI_CODE IN VARCHAR2,
15 p_country_code OUT NOCOPY VARCHAR2,
16 p_area_code OUT NOCOPY VARCHAR2,
17 p_phone_num OUT NOCOPY VARCHAR2,
18 p_phone_ext OUT NOCOPY VARCHAR2);
19
20 --
21 ------------------------------------------------------------------------------
22 -- Function : Get_Customer_From_Account_Num
23 -- Usage : Used by the Routing module to get the customer ID from the
24 -- customer account number
25 -- Description : This function retrieves the primary party id associated
26 -- with the specified customer account number.
27 -- Parameters :
28 -- p_customer_account_number IN VARCHAR2(30) Required
29 --
30 -- Return : NUMBER
31 -- If the given customer does not exist, this function returns
32 -- NULL.
33 ------------------------------------------------------------------------------
34
35 FUNCTION Get_Customer_From_Account_Num
36 ( p_cust_acct_number IN VARCHAR2)
37 Return NUMBER;
38
39
40 ------------------------------------------------------------------------------
41 -- Function : Get_Customer_From_Contract_Num
42 -- Usage : Used by the Routing module to get the customer ID from the
43 -- contract number.
44 -- Description : This function retrieves a party ID from
45 -- OKC_ given a contract number. If there
46 -- is more than one customer associated with the given
47 -- contract, this function will return the first party ID
48 -- that it retrieves.
49 -- Parameters :
50 -- p_contract_number IN VARCHAR2(30) Required
51 --
52 -- Return : NUMBER
53 -- If there is no party associated with the given contract,
54 -- or the contract does not exist, this function returns NULL.
55 ------------------------------------------------------------------------------
56 FUNCTION Get_Customer_From_Contract_Num (p_contract_number IN VARCHAR2)
57 Return Number;
58
59
60 ------------------------------------------------------------------------------
61 -- Function : Get_Customer_From_Site_Num
62 -- Usage : Used by the Routing module to get the customer ID from the
63 -- party site number.
64 -- Description : This function retrieves a party ID from
65 -- HZ_PARTY_SITES given a site number.
66 -- Parameters :
67 -- p_site_number IN VARCHAR2(30) Required
68 --
69 -- Return : NUMBER
70 -- If there is no party associated with the given site number,
71 -- this function returns NULL.
72 ------------------------------------------------------------------------------
73 FUNCTION Get_Customer_From_Site_Num (p_site_number IN VARCHAR2)
74 Return Number;
75
76
77 ------------------------------------------------------------------------------
78 -- Function : Get_Customer_From_Repair_Num
79 -- Usage : Used by the Routing module to get the customer ID from the
80 -- repair number.
81 -- Description : This function retrieves a party ID from
82 -- CSD_REPAIRS_V given a repair number. If there
83 -- Parameters :
84 -- p_repair_number IN VARCHAR2(30) Required
85 --
86 -- Return : NUMBER
87 -- If there is no party associated with the given repair,
88 -- or the repair does not exist, this function returns NULL.
89 ------------------------------------------------------------------------------
90 FUNCTION Get_Customer_From_Repair_Num (P_Repair_Number IN VARCHAR2)
91 Return Number;
92
93
94 ------------------------------------------------------------------------------
95 -- Function : Get_Customer_From_Defect_Num
96 -- Usage : Used by the Routing module to get the customer ID from the
97 -- defect number.
98 -- Description : This function retrieves a party ID from
99 -- CSS_DEF_DEFECTS_ALL_V given a defect number.
100 -- Parameters :
101 -- p_defect_number IN VARCHAR2(30) Required
102 --
103 -- Return : NUMBER
104 -- If there is no party associated with the given defect,
105 -- or the defect does not exist, this function returns NULL.
106 ------------------------------------------------------------------------------
107 FUNCTION Get_Customer_From_Defect_Num(p_defect_number IN VARCHAR2)
108 Return Number;
109
110 ------------------------------------------------------------------------------
111 -- Function : Get_Customer_From_ANI
112 -- Usage : Used by the Routing module to get the party ID from the
113 -- contact's phone number
114 -- Description : This function retrieves a party ID from the
115 -- HZ_CONTACT_POINTS table given a phone number of a customer contact.
116 -- If there is more than one customer associated with the given phone
117 -- number, this function will return the first customer ID that
118 -- it retrieves.
119 -- Parameters :
120 -- p_area_code IN VARCHAR2(10) Optional
121 -- p_phone_number IN VARCHAR2(40) Required
122 -- p_extension IN VARCHAR2(20) Optional
123 -- p_country_code IN VARCHAR2(20) Optional
124 --
125 -- Return : NUMBER
126 -- If there is no customer associated with the given phone
127 -- number, or the phone number does not exist (in the system),
128 -- this function returns NULL.
129 ------------------------------------------------------------------------------
130
131 FUNCTION Get_Customer_From_ANI
132 ( p_country_code IN VARCHAR2 := NULL,
133 p_area_code IN VARCHAR2 := NULL,
134 p_phone_number IN VARCHAR2,
135 p_extension IN VARCHAR2 := NULL)
136 Return Number;
137
138
139 ------------------------------------------------------------------------------
140 -- Function : Get_Customer_From_CustomerNum
141 -- Usage : Used by the Routing module to get the customer ID from the
142 -- customer number
143 -- Description : This function retrieves a party ID from the
144 -- HZ_PARTIES table given a party number. If there is more than one
145 -- party with the given number, this function will return the
146 -- first party ID that it retrieves.
147 -- Parameters :
148 -- p_party_number IN VARCHAR2(30) Required
149 --
150 -- Return : NUMBER
151 -- If the given customer does not exist, this function returns
152 -- NULL.
153 ------------------------------------------------------------------------------
154
155 FUNCTION Get_Customer_From_CustomerNum (p_party_number IN VARCHAR2)
156 Return Number;
157
158
159 ------------------------------------------------------------------------------
160 -- Function : Get_Customer_From_InvoiceNum
161 -- Usage : Used by the Routing module to get the customer ID from the
162 -- sales invoice number.
163 -- Description : This function retrieves a customer ID from the
164 -- RA_CUSTOMER_TRX_ALL table given an invoice number. If there
165 -- are more than one customer associated with the given
166 -- invoice, this function will return the first customer ID
167 -- that it retrieves.
168 -- Parameters :
169 -- p_invoice_number IN VARCHAR2(20) Required
170 --
171 -- Return : NUMBER
172 -- If there is no customer associated with the given invoice,
173 -- or the invoice does not exist, this function returns NULL.
174 ------------------------------------------------------------------------------
175 FUNCTION Get_Customer_From_InvoiceNum(P_Invoice_Number IN VARCHAR2)
176 Return Number;
177
178
179 ------------------------------------------------------------------------------
180 -- Function : Get_Name_Of_Customer
181 -- Usage : Used by the Routing module to get the customer name from the
182 -- customer ID
183 -- Description : This function retrieves the customer name from the
184 -- HZ_PARTIES table given a customer ID.
185 -- Parameters :
186 -- p_party_id IN NUMBER Required
187 --
188 -- Return : VARCHAR2(255)
189 -- If there is no customer with the given party ID, this
190 -- function returns NULL.
191 ------------------------------------------------------------------------------
192
193 FUNCTION Get_Name_Of_Customer(p_party_id IN NUMBER) Return VARCHAR2;
194
195
196 ------------------------------------------------------------------------------
197 -- Function : Get_Status_Of_Customer
198 -- Usage : Used by the Routing module to get the customer status
199 -- Description : This function retrieves the customer status flag from the
200 -- HZ_PARTIES table given a party ID.
201 -- Parameters :
202 -- p_party_id IN NUMBER Required
203 --
204 -- Return : VARCHAR2(1)
205 -- If there is no customer with the given customer ID, this
206 -- function returns NULL.
207 ------------------------------------------------------------------------------
208
209 FUNCTION Get_Status_Of_Customer(p_party_id IN NUMBER) Return VARCHAR2;
210
211
212 ------------------------------------------------------------------------------
213 -- Function : Is_CP_Existent
214 -- Usage : Used by the Routing module to determine whether a customer
215 -- product exists in the installed base with the given ID
216 -- Description : This function finds a row in the CS_CUSTOMER_PRODUCTS_ALL
217 -- table that matches the given customer product ID.
218 -- Parameters :
219 -- p_customer_product_id IN NUMBER Required
220 --
221 -- Return : BOOLEAN
222 -- If there is a row that matches the given ID, this function
223 -- returns TRUE. Else it returns FALSE.
224 ------------------------------------------------------------------------------
225
226 FUNCTION Is_CP_Existent(p_customer_product_id IN NUMBER) Return BOOLEAN;
227
228
229 ------------------------------------------------------------------------------
230 -- Function : Customer_ID_Exists
231 -- Usage : Used by the Routing module to determine whether a customer
232 -- exists with the given ID
233 -- Description : This function finds a row in the HZ_PARTIES
234 -- table that matches the given customer ID.
235 -- Parameters :
236 -- p_customer_id IN NUMBER Required
237 --
238 -- Return : BOOLEAN
239 -- If there is a row that matches the given ID, this function
240 -- returns TRUE. Else it returns FALSE.
241 ------------------------------------------------------------------------------
242
243 FUNCTION Customer_ID_Exists(p_customer_id IN NUMBER) Return BOOLEAN;
244
245
246 ------------------------------------------------------------------------------
247 -- Function : Get_CP_From_ReferenceNum
248 -- Usage : Used by the Routing module to get the customer product ID
249 -- from the reference number
250 -- Description : This function retrieves a customer product ID from the
251 -- CS_CUSTOMER_PRODUCT_ALL table given a reference number.
252 -- Parameters :
253 -- p_reference_number IN NUMBER Required
254 --
255 -- Return : NUMBER
256 -- If the given reference number does not exist, this function
257 -- returns NULL.
258 ------------------------------------------------------------------------------
259
260 FUNCTION Get_CP_From_ReferenceNum(p_reference_number IN NUMBER)
261 Return NUMBER;
262
263
264 ------------------------------------------------------------------------------
265 -- Function : Get_Product_From_ReferenceNum
266 -- Usage : Used by the Routing module to get the inventory item ID
267 -- from the reference number
268 -- Description : This function retrieves an inventory item ID from the
269 -- CS_CUSTOMER_PRODUCT_ALL table given a reference number.
270 -- Parameters :
271 -- p_reference_number IN NUMBER Required
272 --
273 -- Return : NUMBER
274 -- If the given reference number does not exist, this function
275 -- returns NULL.
276 ------------------------------------------------------------------------------
277 FUNCTION Get_Product_From_ReferenceNum(p_reference_number IN NUMBER)
278 Return NUMBER;
279
280 ------------------------------------------------------------------------------
281 -- Function : Get_Name_Of_Product
282 -- Usage : Used by the Routing module to get the product name from the
283 -- inventory item ID
284 -- Description : This function retrieves the product name from the
285 -- MTL_SYSTEM_ITEMS table given an inventory item ID and an
286 -- organization ID. The inventory organization ID is required
287 -- because the same item can be defined in more than one
288 -- organization.
289 -- Parameters :
290 -- p_inventory_item_id IN NUMBER Required
291 -- p_inventory_org_id IN NUMBER Required
292 --
293 -- Return : VARCHAR2(240)
294 -- If there is no product with the given inventory item ID,
295 -- this function returns NULL.
296 ------------------------------------------------------------------------------
297 FUNCTION Get_Name_Of_Product
298 (p_inventory_item_id IN NUMBER,
299 p_inventory_org_id IN NUMBER)
300 Return VARCHAR2;
301
302 ------------------------------------------------------------------------------
303 -- Function : Get_Customer_From_Email
304 -- Usage : Used to get the party ID from the sender's email address (email
305 -- integration)
306 -- Description : This function retrieves a party ID from the
307 -- JTF_CONTACT_POINTS_V given a email address.
308 -- If there is more than one customer associated with the given email
309 -- address, this function will return the first customer ID that
310 -- it retrieves.
311 -- Parameters :
312 -- p_email_address IN VARCHAR2(2000) Required
313 --
314 -- Return : NUMBER
315 -- If there is no customer associated with the given email
316 -- address, or the email address does not exist (in the system),
317 -- this function returns NULL.
318 ------------------------------------------------------------------------------
319
320 FUNCTION Get_Customer_From_Email
321 ( p_email_address IN VARCHAR2)
322 Return Number;
323
324
325 ------------------------------------------------------------------------------
326 -- Function : Get_Customer_From_ContactNum
327 -- Usage : Used to get the party ID from the Contact Number passed in the IVR parms
328 -- Description : This function retrieves a party ID from the
329 -- HZ_PARTIES table by traversing the relationships from HZ_ORG_CONTACTS
330 -- and HZ_PARTY_RELATIONSHIPS.
331 -- If there is more than one party_id associated with the given ContactNum
332 -- this function will return the first party_id that it retrieves.
333 -- Parameters :
334 -- p_contact_number IN VARCHAR2(1996) Required
335 --
336 -- Return : NUMBER
337 -- If there is no party_id associated with the given ContactNum
338 -- this function returns NULL.
339 ------------------------------------------------------------------------------
340
341 FUNCTION Get_Customer_From_ContactNum
342 ( p_contact_number IN VARCHAR2)
343 Return Number;
344
345 ------------------------------------------------------------------------------
346 -- Function : reverse_number
347 -- Usage : Used to get reverse number for the number passed to function
348 -- Description : This function is built to reverse the number passed to it.
349 -- This function can be used instead of the standard REVERSE
350 -- function because the REVERSE fucntion cannot be used in PL/
351 -- Parameters :
352 -- p_phone_number IN VARCHAR2(1996) Required
353 --
354 -- Return : VARCHAR2
355 -----------------------------------------------------------------------------
356 FUNCTION reverse_number(p_phone_number VARCHAR2)
357 RETURN VARCHAR2;
358
359 ------------------------------------------------------------------------------
360 -- Function : Get_Customer_From_Reverse_ANI
361 -- Usage : Used to get the party ID from the reversed ANI Number passed in the IVR parms
362 -- Description : This function retrieves a owner_table_id from the
363 -- HZ_CONTACT_POINTS
364 -- If there is more than one party_id associated with the given ContactNum
365 -- this function will return the first party_id that it retrieves.
366 -- Parameters :
367 -- p_rANI IN VARCHAR2(1996) Required
368 --
369 -- Return : NUMBER
370 -- If there is no party_id associated with the given ContactNum
371 -- this function returns NULL.
372 ------------------------------------------------------------------------------
373
374 FUNCTION get_customer_from_reverse_ANI
375 ( p_rANI IN VARCHAR2,
376 x_uwq_multi_record_match OUT NOCOPY VARCHAR2,
377 x_phone_id OUT NOCOPY NUMBER )
378 RETURN NUMBER;
379
380 --
381 ------------------------------------------------------------------------------
382 -- Procedure : Get_Cust_Acct_From_Account_Num
383 -- Usage : Used to get Party_id,Cust_account_id,last_update_date from
384 -- hz_cust_accounts
385 -- Parameters :
386 -- p_customer_account_number IN VARCHAR2(30) Required
387 -- x_party_id OUT NOCOPY NUMBER(15)
388 -- x_cust_account_id OUT NOCOPY NUMBER(15)
389 -- x_last_update_date OUT NOCOPY DATE
390 --
391 ------------------------------------------------------------------------------
392
393 PROCEDURE Get_Cust_Acct_From_Account_Num
394 ( p_cust_acct_number IN VARCHAR2,
395 x_party_id OUT NOCOPY NUMBER,
396 x_cust_account_id OUT NOCOPY NUMBER,
397 x_last_update_date OUT NOCOPY DATE);
398
399 ------------------------------------------------------------------------------
400 -- Procedure : CSC_Customer_Lookup
401 -- Usage : OTM will make a call to this API for customer lookup
402 -- Parameters :
403 -- p_media_data IN OUT NOCOPY CCT_KEY_VALUE_LIST
404 --
405 ------------------------------------------------------------------------------
406
407 PROCEDURE CSC_Customer_Lookup
408 ( p_media_data IN OUT NOCOPY cct_keyvalue_varr);
409
410 ------------------------------------------------------------------------------
411 -- Function : Get_Party_Name_From_Party_id
412 -- Usage : used to lookup customer name to pass over to softphone
413 -- Parameters :
414 -- p_party_id IN NOCOPY NUMBER
415 --
416 -- Returns :
417 -- party_name varchar2(1996)
418 ------------------------------------------------------------------------------
419
420 Function Get_Party_Name_From_Party_id
421 (p_party_id IN number)
422 Return varchar2;
423
424 END CSC_Routing_UTL;
[Home] [Help]