1 PACKAGE IBU_REQ_PKG AUTHID CURRENT_USER as
2 /* $Header: ibursrs.pls 120.4 2006/01/10 14:59:52 wzli noship $ */
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 +======================================================================*/
63
64
65 /**
66 * Update Service Requests
67 */
68 PROCEDURE UpdateServiceRequest(
69 p_request_id IN NUMBER,
70 p_status_id IN NUMBER,
71 p_urgency_id IN NUMBER,
72 p_problem_description IN VARCHAR2,
73 p_problem_detail IN VARCHAR2,
74 p_note_type IN VARCHAR2,
75 p_last_updated_by IN NUMBER,
76 p_language IN VARCHAR2,
77 --UCONTACT
78 p_contact_party_id IN JTF_NUMBER_TABLE := null,
79 p_contact_type IN JTF_VARCHAR2_TABLE_100 := null,
80 p_contact_point_id IN JTF_NUMBER_TABLE := null,
81 p_contact_point_type IN JTF_VARCHAR2_TABLE_100 := null,
82 p_contact_primary IN JTF_VARCHAR2_TABLE_100 := null,
83 p_sr_contact_point_id IN JTF_NUMBER_TABLE := null,
84 -- done
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2
88 );
89
90 /**
91 * Add Attachment to certain service request
92 */
93 procedure AddAttachment(
94 p_request_id IN NUMBER,
95 p_user_id IN VARCHAR2,
96 p_media_id IN NUMBER,
97 p_name IN VARCHAR2,
98 p_desc IN VARCHAR2);
99
100 /**
101 * CREATE_SERVICE_REQUEST
102 * Thin PL/SQL wrapper for callling TeleService API.
103 */
104 procedure create_service_request(
105 p_request_number IN OUT NOCOPY VARCHAR2,
106 p_type_id IN NUMBER,
107 p_account_id IN NUMBER,
108 p_product IN NUMBER,
109 p_inventory_item IN NUMBER,
110 p_problem_code_id IN VARCHAR2,
111 p_caller_type IN VARCHAR2,
112 p_language IN VARCHAR2,
113 p_urgency_id IN NUMBER,
114 p_summary IN VARCHAR2,
115 p_problem_description IN jtf_varchar2_table_32767,
116 p_problem_detail IN jtf_varchar2_table_32767,
117 p_note_status in jtf_varchar2_table_100,
118 p_contact_party_id in jtf_number_table,
119 p_contact_type in jtf_varchar2_table_100,
120 p_contact_point_id IN jtf_number_table,
121 p_contact_point_type in jtf_varchar2_table_100,
122 p_contact_primary in jtf_varchar2_table_100,
123
124 p_status_id IN NUMBER,
125 p_severity_id IN NUMBER,
126 -- p_owner_id IN NUMBER,
127 p_user_id IN NUMBER,
128 p_customer_id IN NUMBER,
129 p_platform_id IN NUMBER,
130 p_cp_revision_id IN NUMBER,
131 p_inv_item_revision IN VARCHAR2,
132 p_helpdesk_no IN VARCHAR2,
133 p_party_id IN NUMBER,
134 p_solved IN VARCHAR2,
135 p_employee_id IN NUMBER,
136 p_note_type IN jtf_varchar2_table_100,
137 p_contract_id in varchar2,
138 p_project_num in varchar2,
139 p_short_code in varchar2,
140 p_os_version in varchar2,
141 p_db_version in varchar2,
142 p_product_revision in varchar2,
143 -- p_attr_6 in varchar2,
144 p_cust_pref_lang_code in varchar2 := NULL,
145 p_pref_contact_method in varchar2,
146 p_rollout in varchar2,
147 p_error_code in varchar2 := NULL,
148 p_serial_number in varchar2 := NULL,
149 p_inv_category_id in NUMBER,
150 p_time_zone_id in NUMBER,
151 --for the SR location information
152 p_location_id in NUMBER,
153 p_address in varchar2 := NULL,
154 p_city in varchar2 := NULL,
155 p_state in varchar2 := NULL,
156 p_country in varchar2 := NULL,
157 p_province in varchar2 := NULL,
158 p_postal_code in varchar2 := NULL,
159 p_county in varchar2 := NULL,
160 -- add the following for 11.5.10
161 p_addrLine2 in varchar2 := NULL,
162 p_addrLine3 in varchar2 := NULL,
163 p_addrLine4 in varchar2 := NULL,
164 p_poboxNumber in varchar2 := NULL,
165 p_houseNumber in varchar2 := NULL,
166 p_streetSuffix in varchar2 := NULL,
167 p_street in varchar2 := NULL,
168 p_street_number in varchar2 := NULL,
169 p_floor in varchar2 := NULL,
170 p_suite in varchar2 := NULL,
171 p_postalPlus4Code in varchar2 := NULL,
172 p_position in varchar2 := NULL,
173 p_locationDirections in varchar2 := NULL,
174 p_description in varchar2 := NULL,
175 p_pointOfInterest in varchar2 := NULL,
176 p_crossStreet in varchar2 := NULL,
177 p_directionQualifier in varchar2 := NULL,
178 p_distanceQualifier in varchar2 := NULL,
179 p_distanceQualUom in varchar2 := NULL,
180 --for the bill to and ship to
181 p_bill_to_site_id in NUMBER := NULL,
182 p_bill_to_contact_id in NUMBER := NULL,
183 p_ship_to_site_id in NUMBER := NULL,
184 p_ship_to_contact_id in NUMBER := NULL,
185 p_install_site_use_id in NUMBER := NULL,
186 p_bill_to_party_id in NUMBER := NULL,
187 p_ship_to_party_id in NUMBER := NULL,
188 -- added for 11.5.10
189 p_bill_to_account_id in NUMBER,
190 p_ship_to_account_id in NUMBER,
191 -- added for link object enhancement
192 p_ref_object_code in varchar2,
193 p_ref_object_id in number,
194 -- added for eam enhancement
195 p_asset_id in number,
196 p_maint_org_id in number,
197 p_owning_dept_id in number,
198 p_eam_type in varchar2,
199 --for DFF
200 p_external_attribute_1 IN varchar2 := NULL,
201 p_external_attribute_2 IN varchar2 := NULL,
202 p_external_attribute_3 IN varchar2 := NULL,
203 p_external_attribute_4 IN varchar2 := NULL,
204 p_external_attribute_5 IN varchar2 := NULL,
205 p_external_attribute_6 IN varchar2 := NULL,
206 p_external_attribute_7 IN varchar2 := NULL,
207 p_external_attribute_8 IN varchar2 := NULL,
208 p_external_attribute_9 IN varchar2 := NULL,
209 p_external_attribute_10 IN varchar2 := NULL,
210 p_external_attribute_11 IN varchar2 := NULL,
211 p_external_attribute_12 IN varchar2 := NULL,
212 p_external_attribute_13 IN varchar2 := NULL,
213 p_external_attribute_14 IN varchar2 := NULL,
214 p_external_attribute_15 IN varchar2 := NULL,
215 p_external_context IN varchar2 := NULL,
216
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220 x_request_id OUT NOCOPY NUMBER
221 );
222
223 /**
224 * Send email notification for user
225 */
226 procedure send_email(
227 email_address_in in varchar2,
228 user_id in varchar2,
229 subject in varchar2,
230 msg_body in varchar2,
231 srID in number,
232 emailStyleSheet in varchar2,
233 emailbranding in varchar2,
234 emaillinkURL in varchar2,
235 notification_pref in varchar2,
236 contactType in varchar2,
237 contactID in number
238 );
239 /**
240 * Get the default status Id for SR creation
241 */
242
243 procedure get_default_status(
244 p_type_id in number,
245 x_status_id out nocopy number,
246 x_return_status out NOCOPY VARCHAR2
247 );
248
249 /**
250 * This API is used to handle the mulitbytes issues.
251 */
252 procedure check_string_length_bites(
253 p_string in varchar2,
254 p_targetlen number,
255 x_returnLen out NOCOPY number,
256 x_truncateCharNum out NOCOPY number
257 );
258 /**
259 * Decode the error messages:
260 * CS_SR_CANNOT_CLOSE_SR
261 * CS_SR_OPEN_TASKS_EXISTS
262 * CS_SR_OPEN_CHARGES_EXISTS
263 * CS_SR_SCHEDULED_TASKS_EXISTS
264 * CS_SR_TASK_DEBRIEF_INCOMPLETE
265 * TO
266 * "This service request cannot be closed at this time.
267 * Please call customer support for assistance."
268 */
269 procedure decodeErrorMsg;
270
271 /**
272 * Start the Email work flow process.
273 */
274
275 procedure StartEmailProcess (
276 roleName in varchar2,
277 srID in number,
278 subject in varchar2,
279 content Wf_Engine.TextTabTyp,
280 ProcessOwner in varchar2,
281 Workflowprocess in varchar2 default null,
282 item_type in varchar2 default null,
283 emailStyleSheet in varchar2,
284 emailbranding in varchar2,
285 emaillinkURL in varchar2);
286
287 /**
288 * get the object info from jtf_object
289 */
290 procedure getObjectInfo(
291 p_ref_object_code in varchar2,
292 x_select_id out NOCOPY varchar2,
293 x_from_table out NOCOPY varchar2,
294 x_where_clause out NOCOPY varchar2,
295 x_object_count out NOCOPY number
296 );
297
298 procedure checkObjectID(
299 p_ref_object_id in number,
300 p_select_id in varchar2,
301 p_from_table in varchar2,
302 p_where_clause in varchar2,
303 x_object_count out NOCOPY number
304 );
305
306 procedure validate_http_service_ticket(
307 p_ticket_string in varchar2,
308 x_return_status out NOCOPY VARCHAR2
309 );
310
311 END IBU_REQ_PKG;