1 package csz_servicerequest_util_pvt as
2 /* $Header: cszvutls.pls 120.5 2006/03/22 11:39:48 akartha noship $ */
3 -- -------------------------------------------------------------------------
4 -- Start of comments
5 -- UTIL Name : GET_USER_NAME
6 -- Type : Private
7 -- Description : Given a USER_ID the function will return the username/partyname. This
8 -- Function is used to display the CREATED_BY UserName
9 -- Parameters :
10 -- IN : p_user_id NUMBER Required
11 -- OUT: Returns UserName VARCHAR2(360)
12 -- End of comments
13 -- -------------------------------------------------------------------------
14 FUNCTION GET_USER_NAME
15 ( p_user_id IN NUMBER )RETURN VARCHAR2;
16 --
17 -- -------------------------------------------------------------------------
18 -- Start of comments
19 -- UTIL Name : get_contact_name
20 -- Type : Private
21 -- Description : To get the ContactName based on the ContactPartyId,PartyId and ContactType
22 -- Parameters :
23 -- IN: p_contact_type IN VARCHAR2 Required
24 -- IN : p_contact_party_id IN NUMBER Required
25 -- IN : p_party_id IN NUMBER Required
26 -- Returnvalue:
27 -- l_contact_name VARCHAR2(360)
28 -- End of comments
29 -- -------------------------------------------------------------------------
30 FUNCTION get_contact_name
31 ( p_contact_type IN VARCHAR2
32 ,p_contact_party_id IN NUMBER
33 , p_party_id IN NUMBER
34 ) RETURN VARCHAR2;
35 --
36 -- -------------------------------------------------------------------------
37 -- Start of comments
38 -- UTIL Name : GET_SR_JEOPARDY
39 -- Type : Private
40 -- Description : Returns if Service Request is in Jeopardy or not
41 -- Parameters :
42 -- IN : p_incident_id IN NUMBER Required
43 -- IN : p_exp_response_date IN DATE Required
44 -- IN : p_exp_resolution_date IN DATE Required
45 -- IN : p_actual_response_date IN DATE Required
46 -- IN : p_actual_resolution_date IN DATE Required)
47
48 --- Returnvalue:
49 -- l_sr_jeopardy VARCHAR2(10)
50 -- End of comments
51 -- --------------------------------------------------------------------------------
52
53 Function GET_SR_JEOPARDY
54 ( p_incident_id IN NUMBER,
55 p_exp_response_date IN DATE,
56 p_exp_resolution_date IN DATE,
57 p_actual_response_date IN DATE,
58 p_actual_resolution_date IN DATE)
59 RETURN VARCHAR2;
60 --
61 -- --------------------------------------------------------------------------------
62 -- Start of comments
63 -- UTIL Name : GET_CALCULATED_TIME
64 -- Type : Private
65 -- Description : Returns the Time in Days
66 -- Parameters :
67 -- IN : p_time IN NUMBER Required
68 -- IN : p_UOM IN VARCHAR2 Required
69 -- ReturnValue:
70 -- l_calculated_time NUMBER
71 -- --------------------------------------------------------------------------------
72
73 Function GET_CALCULATED_TIME
74 ( p_time IN NUMBER,
75 p_UOM IN VARCHAR2)
76 RETURN NUMBER;
77 --
78 -- --------------------------------------------------------------------------------
79
80 -- --------------------------------------------------------------------------------
81 -- Start of comments
82 -- UTIL Name : GET_DEFAULT_SITE
83 -- Type : Private
84 -- Description : Returns the primary billto/shipto address associated with this party
85 -- Parameters :
86 -- IN : partyId IN NUMBER Required
87 -- IN : siteUse IN VARCHAR2 Required
88 -- ReturnValue: primary billto/shipto address for this party
89 -- l_default_site VARCHAR2
90 -- ----------------------------------------------------------------------------
91 Function get_default_site
92 ( partyId in NUMBER
93 , site_use in VARCHAR2
94 ) return VARCHAR2;
95 -- --------------------------------------------------------------------------------
96 -- Start of comments
97 -- UTIL Name : GET_DEFAULT_SITE
98 -- Type : Private
99 -- Description : Returns the site use for primary billto/shipto address
100 -- Parameters :
101 -- IN : partyId IN NUMBER Required
102 -- IN : site_use IN VARCHAR2 Required
103 -- ReturnValue:
104 -- l_default_site VARCHAR2
105 -- -------------------------------------------------------------------------
106 Function get_default_site_id
107 ( partyId in NUMBER
108 , site_use in VARCHAR2
109 ) return NUMBER;
110 -- -------------------------------------------------------------------------
111 -- Start of comments
112 -- UTIL Name : GET_DEFAULT_SITE_ID
113 -- Type : Private
114 -- Description : Returns the site Id for primary billto/shipto address
115 -- Parameters :
116 -- IN : partyId IN NUMBER Required
117 -- IN : site_use IN VARCHAR2 Required
118 -- ReturnValue:
119 -- l_default_site NUMBER
120 -- -------------------------------------------------------------------------
121 procedure task_group_template_mismatch
122 ( p_init_msg_list in varchar2 default fnd_api.g_false
123 , p_old_inv_category in number
124 , p_new_inv_category in number
125 , p_old_inv_item in number
126 , p_new_inv_item in number
127 , p_old_inc_type in number
128 , p_new_inc_type in number
129 , p_inv_org_id in number
130 , p_incident_id in number
131 , p_old_prob_code in varchar2
132 , p_new_prob_code in varchar2
133 , x_msg_count out nocopy number
134 , x_return_status out nocopy varchar2
135 , x_msg_data out nocopy varchar2
136 );
137 -- -------------------------------------------------------------------------
138 -- -------------------------------------------------------------------------
139 -- Start of comments
140 -- UTIL Name : task_group_template_mismatch
141 -- Type : Private
142 -- Description : Checks if there is a mismatch in task group template when item,
143 -- item category, type, problem code change on updating a SR
144 -- -------------------------------------------------------------------------
145 procedure get_instance_details
146 ( p_instance_id in number
147 , p_inc_type_id in number default fnd_profile.value('INC_DEFAULT_INCIDENT_TYPE')
148 , p_severity_id in number default fnd_profile.value('INC_DEFAULT_INCIDENT_SEVERITY')
149 , p_request_date in date default sysdate
150 , p_timezone_id in number default fnd_profile.value('SERVER_TIMEZONE_ID')
151 , p_get_contact in varchar2 default fnd_api.g_false
152 , x_contact_id out nocopy number
153 , x_contact_type out nocopy varchar2
154 , x_contract_id out nocopy number
155 , x_contract_number out nocopy varchar2
156 , x_service_line_id out nocopy number
157 , x_coverage_term out nocopy varchar2
158 , x_warranty_flag out nocopy varchar2
159 , x_reaction_time out nocopy date
160 , x_resolution_time out nocopy date
161 , x_service_desc out nocopy varchar2
162 );
163 -- -------------------------------------------------------------------------
164 -- Start of comments
165 -- UTIL Name : get_instance_details
166 -- Type : Private
167 -- Description : gets the contact and contract given an instance, primarily
168 -- used for defaulting when instance is selected
169 -- -------------------------------------------------------------------------
170 --
171 -- -------------------------------------------------------------------------
172 -- Start of comments
173 -- UTIL Name : get_linked_srs
174 -- Type : Private
175 -- Description : gets list of linked srs (used in KM Unified search).
176 -- -------------------------------------------------------------------------
177 --
178 FUNCTION get_linked_srs (p_incident_id IN NUMBER) RETURN VARCHAR2;
179
180 -- -------------------------------------------------------------------------
181 -- Start of comments
182 -- UTIL Name : get_contact_info
183 -- Type : Private
184 -- Description : To get the Contact info based on the Incident id, ContactPartyId,PartyId and ContactType, primarycontact
185 -- Parameters :
186 -- IN: p_incident_id IN VARCHAR2 Required
187 -- IN: p_contact_type IN VARCHAR2
188 -- IN : p_contact_party_id IN NUMBER
189 -- IN : p_party_id IN NUMBER Required
190 -- IN : p_primary_contact IN NUMBER Required
191 -- Returnvalue:
192 -- l_contact_name VARCHAR2(360)
193 -- End of comments
194 -- -------------------------------------------------------------------------
195 FUNCTION get_contact_info
196 ( p_incident_id IN NUMBER
197 ,p_contact_type IN VARCHAR2
198 ,p_contact_party_id IN NUMBER
199 ,p_party_id IN NUMBER
200 ,p_primary_contact IN VARCHAR2
201 ) RETURN VARCHAR2;
202 --
203 -- --------------------------------------------------------------------------------
204 -- Start of comments
205 -- UTIL Name : GET_TIMEZONE_FOR_CONTACT
206 -- Type : Private
207 -- Description : Returns the Timezone Id for a Contact/ContactPoint/Location.
208 -- Parameters :
209 -- IN : p_contact_type IN VARCHAR2 Optional. If not passed assumed not an Employee
210 -- IN : p_contact_point_id IN Number Optional
211 -- IN : p_contact_id IN Number Optional
212 -- ReturnValue:
213 -- l_timezone_id NUMBER
214 -- --------------------------------------------------------------------------------
215
216 Function GET_TIMEZONE_FOR_CONTACT
217 ( p_contact_type IN VARCHAR2,
218 p_contact_id IN NUMBER,
219 p_contact_point_id IN NUMBER)
220 RETURN VARCHAR2;
221 --
222 -- --------------------------------------------------------------------------------
223 -- Start of comments
224 -- UTIL Name : GET_TIMEZONE_FOR_LOCATION
225 -- Type : Private
226 -- Description : Returns the Timezone Id for the contact's primary Location.
227 -- Parameters :
228 -- IN : p_contact_id IN Number Optional
229 -- ReturnValue:
230 -- l_timezone_id NUMBER
231 -- --------------------------------------------------------------------------------
232 Function GET_TIMEZONE_FOR_LOCATION
233 ( p_contact_id IN NUMBER)
234 RETURN NUMBER;
235 --
236 -- --------------------------------------------------------------------------------
237 -- Start of comments
238 -- UTIL Name : GET_FIRST_NOTE
239 -- Type : Private
240 -- Description : Returns the recently created note associated to a Service Request
241 -- Parameters :
242 -- IN : p_incident_id IN Number Required
243 -- ReturnValue:
244 -- l_first_note VARCHAR2(2000)
245 -- --------------------------------------------------------------------------------
246 Function GET_FIRST_NOTE
247 ( p_incident_id IN NUMBER)
248 RETURN VARCHAR2;
249 --
250 -- --------------------------------------------------------------------------------
251 -- Start of comments
252 -- UTIL Name : CHECK_IF_NEXT_WORK_ENABLED
253 -- Type : Private
254 -- Description : Checks if the UWQ profile options
255 -- IEU_WR_DIST_MODE,IEU:Distribute:SR:Work Source
256 -- are set and also Activation status is turned on.
257 -- Return value is yes if profile options are set properly.
258 -- and activation status is turned on
259 -- -------------------------------------------------------------------------
260 PROCEDURE CHECK_IF_NEXT_WORK_ENABLED
261 ( p_ws_code IN VARCHAR2,
262 x_enable_next_work OUT nocopy VARCHAR2,
263 x_msg_count OUT nocopy NUMBER,
264 x_return_status OUT nocopy VARCHAR2,
265 x_msg_data OUT nocopy VARCHAR2);
266 -- -------------------------------------------------------------------------
267 -- Start of comments
268 -- UTIL Name : GET_NEXT_SR_TO_WORK
269 -- Type : Private
270 -- Description : Calls IEU_WR_PUB.GET_NEXT_WORK_FOR_APPS
271 -- and returns the incident_id retreived from the above call.
272 -- -------------------------------------------------------------------------
273 PROCEDURE GET_NEXT_SR_TO_WORK
274 ( p_ws_code IN VARCHAR2,
275 p_resource_id IN NUMBER,
276 x_incident_id OUT nocopy NUMBER,
277 x_msg_count OUT nocopy NUMBER,
278 x_return_status OUT nocopy VARCHAR2,
279 x_msg_data OUT nocopy VARCHAR2,
280 x_object_type OUT nocopy VARCHAR2);
281
282 --
283 -- -------------------------------------------------------------------------
284 -- Start of comments
285 -- UTIL Name : GET_SR_ESCALATED
286 -- Type : Private
287 -- Description : Returns Y if Service Request is escalated, else returns N
288 -- Parameters :
289 -- IN : p_incident_id IN NUMBER Required
290 --
291 --- Returnvalue:
292 -- l_sr_escalated VARCHAR2(1)
293 -- End of comments
294 -- --------------------------------------------------------------------------
295 Function GET_SR_ESCALATED
296 ( p_incident_id IN NUMBER
297 )
298 RETURN VARCHAR2;
299 -- --------------------------------------------------------------------------
300 -- Start of comments
301 -- UTIL Name : GET_CONTACT_NAME
302 -- Type : Private
303 -- Description : Returns contact name
304 -- Parameters :
305 -- IN : p_incident_id IN NUMBER
306 -- p_customer_id IN NUMBER
307 -- Returnvalue: contact name
308 -- End of comments
309 -- -------------------------------------------------------------------------
310 Function GET_CONTACT_NAME
311 ( p_incident_id IN NUMBER,
312 p_customer_id IN NUMBER
313 )
314 RETURN VARCHAR2;
315 -- -------------------------------------------------------------------------
316 -- Start of comments
317 -- UTIL Name : GET_REL_OBJ_DETAILS
318 -- Type : Private
319 -- Description : Returns details of a related object
320 -- Parameters :
321 -- IN : p_object_type IN VARCHAR2
322 -- p_object_id IN NUMBER
323 -- Returnvalue: details (summary) of a jtf object.
324 -- End of comments
325 -- -------------------------------------------------------------------------
326 FUNCTION GET_REL_OBJ_DETAILS
327 ( p_object_type IN VARCHAR2,
328 p_object_id IN NUMBER
329 )
330 RETURN VARCHAR2;
331 -- -------------------------------------------------------------------------
332 -- Start of comments
333 -- UTIL Name : get_assc_party_name
334 -- Type : Private
335 -- Description : Returns associated party name
336 -- Parameters :
337 -- IN
338 -- p_assc_party_type IN VARCHAR2
339 -- p_assc_party_id IN NUMBER
340 -- End of comments
341 -- -------------------------------------------------------------------------
342 FUNCTION get_assc_party_name
343 ( p_assc_party_type IN VARCHAR2
344 ,p_assc_party_id IN NUMBER
345 ) RETURN VARCHAR2;
346 -- -------------------------------------------------------------------------
347 FUNCTION get_concat_associated_role
348 ( p_incident_id IN NUMBER
349 ,p_party_id IN NUMBER
350 ,p_party_type IN VARCHAR2)
351 RETURN VARCHAR2;
352 -- -------------------------------------------------------------------------
353 -- Start of comments
354 -- UTIL Name : get_emp_contact_name
355 -- Type : Private
356 -- Description : Returns employee contact name
357 -- Parameters :
358 -- IN
359 -- p_person_id IN NUMBER
360 -- End of comments
361 -- -------------------------------------------------------------------------
362 FUNCTION get_emp_contact_name
363 (p_person_id IN NUMBER
364 ) RETURN VARCHAR2;
365 -- -------------------------------------------------------------------------
366 -- Start of comments
367 -- UTIL Name : get_emp_contact_email
371 -- IN
368 -- Type : Private
369 -- Description : Returns employee contact email
370 -- Parameters :
372 -- p_person_id IN NUMBER
373 -- End of comments
374 -- -------------------------------------------------------------------------
375 FUNCTION get_emp_contact_email
376 (p_person_id IN NUMBER
377 ) RETURN VARCHAR2;
378 -- -------------------------------------------------------------------------
379 end csz_servicerequest_util_pvt;
[Home] [Help]