1 PACKAGE IBU_REQ_PKG AUTHID CURRENT_USER as
2 /* $Header: ibursrs.pls 120.6 2011/12/12 09:11:29 lkadamba ship $ */
3 /*======================================================================+
4 | Copyright (c) 1999 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 +======================================================================+
8 | FILENAME: ibursrs.pls |
9 | |
10 | PURPOSE |
11 | Creates the package specification |
12 | ARGUMENTS |
13 | |
14 | NOTES |
15 | Usage: start |
16 | HISTORY |
17 | 12.20.99 Alex Lau Created |
18 | 6-APR-2001 Alan Lau |
19 | Add major enhancement for 11.5.4.F |
20 | Commented out UpdateStatus, UpdateUrgency, CreateTask, |
21 | and GetContract. |
22 | 01-MAR-2002 klou |
23 | Add p_error_code to create_service_request procedure. |
24 | 15-MAR-2002 klou |
25 | Add p_serial_number to create_service_request. |
26 | 31-MAR-2002 klou (UCONTACT) |
27 | Add new parameters to UpdateServiceRequest to hanlde |
28 | update contacts in SR detail. |
29 | 15-APR-2002 klou |
30 | 1. Replace p_attr6 with p_cust_pref_lang_code in |
31 | create_service_request |
32 | 25-MAY-2002 WMA |
33 | 1. Add the SR location address information |
34 | 17-OCT-2002 WMA |
35 | 1. modified the create API according to CS change |
36 | 2. add five more parameters for bill to and ship to |
37 | 06-NOV-2002 SPOLAMRE |
38 | Added code to handle DFF |
39 | 13-NOV-2002 WMA |
40 | set the default values for the bill to and ship to |
41 | parameters |
42 | 115.50 03-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY |
43 | hint to enable pass by reference. |
44 | 115.51 06-dec-2002 wzli added two parameters: p_bill_to_party_id |
45 | and p_ship_to_party_id in the create |
46 | service request procedure. |
47 | 115.52 02-Jan-2002 wma add the API |
48 | get_default_status() |
49 | 115.53 31-Jan-2003 SPOLAMRE |
50 | Changed the PROCEDURE AddAttachment to take|
51 | file name as parameter |
52 | 115.54 09-OCT-2003 WZLI added procedure decodeErrorMsg |
53 | 115.55 20-OCT-2003 wzli added two parameterss: p_street_number and |
54 | p_timezone_id in the create SR procedure. |
55 | 115.56 10-MAR-2004 WZLI added parameter: p_note_status. |
56 | 115.57 28-NOV-2004 WMA modify the send Email API, add new API |
57 | StartEmailProcess(). |
58 | 120.1 9-SEP-2005 WMA add logic to handle the mulitbytes issues. |
59 | 120.2 28-NOV-2005 wzli added two parameters: p_ref_object_code and|
60 | p_ref_object_id in the create SR procedure |
61 | 12.3 10-DEC-2005 WMA add procedure validate_http_service_ticket |
62 | 120.5 12-FEB-2009 mkundali added for 12.1.2 enhancement bug8245975 |
63 | 120.6 12-DEC-2011 lkadamba Fix for Bug 12681852 (ISUPPORT PROJECT |
64 | PROJECT INTEGRATION FUNCTIONALITY) |
65 +======================================================================*/
66
67
68 /**
69 * Update Service Requests
70 */
71 PROCEDURE UpdateServiceRequest(
72 p_request_id IN NUMBER,
73 p_status_id IN NUMBER,
74 p_urgency_id IN NUMBER,
75 p_problem_description IN VARCHAR2,
76 p_problem_detail IN VARCHAR2,
77 p_note_type IN VARCHAR2,
78 p_last_updated_by IN NUMBER,
79 p_language IN VARCHAR2,
80 --UCONTACT
81 p_contact_party_id IN JTF_NUMBER_TABLE := null,
82 p_contact_type IN JTF_VARCHAR2_TABLE_100 := null,
83 p_contact_point_id IN JTF_NUMBER_TABLE := null,
84 p_contact_point_type IN JTF_VARCHAR2_TABLE_100 := null,
85 p_contact_primary IN JTF_VARCHAR2_TABLE_100 := null,
86 p_sr_contact_point_id IN JTF_NUMBER_TABLE := null,
87 -- done
88 x_return_status OUT NOCOPY VARCHAR2,
89 x_msg_count OUT NOCOPY NUMBER,
90 x_msg_data OUT NOCOPY VARCHAR2
91 );
92
93 /**
94 * Add Attachment to certain service request
95 */
96 procedure AddAttachment(
97 p_request_id IN NUMBER,
98 p_user_id IN VARCHAR2,
99 p_media_id IN NUMBER,
100 p_name IN VARCHAR2,
101 p_desc IN VARCHAR2);
102
103 /**
104 * CREATE_SERVICE_REQUEST
105 * Thin PL/SQL wrapper for callling TeleService API.
106 */
107 procedure create_service_request(
108 p_request_number IN OUT NOCOPY VARCHAR2,
109 p_type_id IN NUMBER,
110 p_account_id IN NUMBER,
111 p_product IN NUMBER,
112 p_inventory_item IN NUMBER,
113 p_problem_code_id IN VARCHAR2,
114 p_caller_type IN VARCHAR2,
115 p_language IN VARCHAR2,
116 p_urgency_id IN NUMBER,
117 p_summary IN VARCHAR2,
118 p_problem_description IN jtf_varchar2_table_32767,
119 p_problem_detail IN jtf_varchar2_table_32767,
120 p_note_status in jtf_varchar2_table_100,
121 p_contact_party_id in jtf_number_table,
122 p_contact_type in jtf_varchar2_table_100,
123 p_contact_point_id IN jtf_number_table,
124 p_contact_point_type in jtf_varchar2_table_100,
125 p_contact_primary in jtf_varchar2_table_100,
126
127 p_status_id IN NUMBER,
128 p_severity_id IN NUMBER,
129 -- p_owner_id IN NUMBER,
130 p_user_id IN NUMBER,
131 p_customer_id IN NUMBER,
132 p_platform_id IN NUMBER,
133 p_cp_revision_id IN NUMBER,
134 p_inv_item_revision IN VARCHAR2,
135 p_helpdesk_no IN VARCHAR2,
136 p_party_id IN NUMBER,
137 p_solved IN VARCHAR2,
138 p_employee_id IN NUMBER,
139 p_note_type IN jtf_varchar2_table_100,
140 p_contract_id in varchar2,
141 p_project_num in varchar2,
142 p_short_code in varchar2,
143 p_os_version in varchar2,
144 p_db_version in varchar2,
145 p_product_revision in varchar2,
146 -- p_attr_6 in varchar2,
147 p_cust_pref_lang_code in varchar2 := NULL,
148 p_pref_contact_method in varchar2,
149 p_rollout in varchar2,
150 p_error_code in varchar2 := NULL,
151 p_serial_number in varchar2 := NULL,
152 p_inv_category_id in NUMBER,
153 p_time_zone_id in NUMBER,
154 --for the SR location information
155 p_location_id in NUMBER,
156 p_address in varchar2 := NULL,
157 p_city in varchar2 := NULL,
158 p_state in varchar2 := NULL,
159 p_country in varchar2 := NULL,
160 p_province in varchar2 := NULL,
161 p_postal_code in varchar2 := NULL,
162 p_county in varchar2 := NULL,
163 -- add the following for 11.5.10
164 p_addrLine2 in varchar2 := NULL,
165 p_addrLine3 in varchar2 := NULL,
166 p_addrLine4 in varchar2 := NULL,
167 p_poboxNumber in varchar2 := NULL,
168 p_houseNumber in varchar2 := NULL,
169 p_streetSuffix in varchar2 := NULL,
170 p_street in varchar2 := NULL,
171 p_street_number in varchar2 := NULL,
172 p_floor in varchar2 := NULL,
173 p_suite in varchar2 := NULL,
174 p_postalPlus4Code in varchar2 := NULL,
175 p_position in varchar2 := NULL,
176 p_locationDirections in varchar2 := NULL,
177 p_description in varchar2 := NULL,
178 p_pointOfInterest in varchar2 := NULL,
179 p_crossStreet in varchar2 := NULL,
180 p_directionQualifier in varchar2 := NULL,
181 p_distanceQualifier in varchar2 := NULL,
182 p_distanceQualUom in varchar2 := NULL,
183 --for the bill to and ship to
184 p_bill_to_site_id in NUMBER := NULL,
185 p_bill_to_contact_id in NUMBER := NULL,
186 p_ship_to_site_id in NUMBER := NULL,
187 p_ship_to_contact_id in NUMBER := NULL,
188 p_install_site_use_id in NUMBER := NULL,
189 p_bill_to_party_id in NUMBER := NULL,
190 p_ship_to_party_id in NUMBER := NULL,
191 -- added for 11.5.10
192 p_bill_to_account_id in NUMBER,
193 p_ship_to_account_id in NUMBER,
194 -- added for link object enhancement
195 p_ref_object_code in varchar2,
196 p_ref_object_id in number,
197 -- added for eam enhancement
198 p_asset_id in number,
199 p_maint_org_id in number,
200 p_owning_dept_id in number,
201 p_eam_type in varchar2,
202 --for DFF
203 p_external_attribute_1 IN varchar2 := NULL,
204 p_external_attribute_2 IN varchar2 := NULL,
205 p_external_attribute_3 IN varchar2 := NULL,
206 p_external_attribute_4 IN varchar2 := NULL,
207 p_external_attribute_5 IN varchar2 := NULL,
208 p_external_attribute_6 IN varchar2 := NULL,
209 p_external_attribute_7 IN varchar2 := NULL,
210 p_external_attribute_8 IN varchar2 := NULL,
211 p_external_attribute_9 IN varchar2 := NULL,
212 p_external_attribute_10 IN varchar2 := NULL,
213 p_external_attribute_11 IN varchar2 := NULL,
214 p_external_attribute_12 IN varchar2 := NULL,
218 p_external_context IN varchar2 := NULL,
215 p_external_attribute_13 IN varchar2 := NULL,
216 p_external_attribute_14 IN varchar2 := NULL,
217 p_external_attribute_15 IN varchar2 := NULL,
219
220 x_return_status OUT NOCOPY VARCHAR2,
221 x_msg_count OUT NOCOPY NUMBER,
222 x_msg_data OUT NOCOPY VARCHAR2,
223 x_request_id OUT NOCOPY NUMBER,
224 p_site_name in varchar2 := NULL,
225 p_site_number in varchar2 := NULL,
226 p_addressee in varchar2 := NULL,
227
228 --Added for Bug Fix 12681852 (ISUPPORT PROJECT INTEGRATION FUNCTIONALITY)
229 p_project_id in number,
230 p_project_task_id in number
231 );
232
233 /**
234 * Send email notification for user
235 */
236 procedure send_email(
237 email_address_in in varchar2,
238 user_id in varchar2,
239 subject in varchar2,
240 msg_body in varchar2,
241 srID in number,
242 emailStyleSheet in varchar2,
243 emailbranding in varchar2,
244 emaillinkURL in varchar2,
245 notification_pref in varchar2,
246 contactType in varchar2,
247 contactID in number
248 );
249 /**
250 * Get the default status Id for SR creation
251 */
252
253 procedure get_default_status(
254 p_type_id in number,
255 x_status_id out nocopy number,
256 x_return_status out NOCOPY VARCHAR2
257 );
258
259 /**
260 * This API is used to handle the mulitbytes issues.
261 */
262 procedure check_string_length_bites(
263 p_string in varchar2,
264 p_targetlen number,
265 x_returnLen out NOCOPY number,
266 x_truncateCharNum out NOCOPY number
267 );
268 /**
269 * Decode the error messages:
270 * CS_SR_CANNOT_CLOSE_SR
271 * CS_SR_OPEN_TASKS_EXISTS
272 * CS_SR_OPEN_CHARGES_EXISTS
273 * CS_SR_SCHEDULED_TASKS_EXISTS
274 * CS_SR_TASK_DEBRIEF_INCOMPLETE
275 * TO
276 * "This service request cannot be closed at this time.
277 * Please call customer support for assistance."
278 */
279 procedure decodeErrorMsg;
280
281 /**
282 * Start the Email work flow process.
283 */
284
285 procedure StartEmailProcess (
286 roleName in varchar2,
287 srID in number,
288 subject in varchar2,
289 content Wf_Engine.TextTabTyp,
290 ProcessOwner in varchar2,
291 Workflowprocess in varchar2 default null,
292 item_type in varchar2 default null,
293 emailStyleSheet in varchar2,
294 emailbranding in varchar2,
295 emaillinkURL in varchar2);
296
297 /**
298 * get the object info from jtf_object
299 */
300 procedure getObjectInfo(
301 p_ref_object_code in varchar2,
302 x_select_id out NOCOPY varchar2,
303 x_from_table out NOCOPY varchar2,
304 x_where_clause out NOCOPY varchar2,
305 x_object_count out NOCOPY number
306 );
307
308 procedure checkObjectID(
309 p_ref_object_id in number,
310 p_select_id in varchar2,
311 p_from_table in varchar2,
312 p_where_clause in varchar2,
313 x_object_count out NOCOPY number
314 );
315
316 procedure validate_http_service_ticket(
317 p_ticket_string in varchar2,
318 x_return_status out NOCOPY VARCHAR2
319 );
320
321 END IBU_REQ_PKG;
[Home] [Help]