[Home] [Help]
PACKAGE: APPS.CS_SERVICEREQUEST_PVT
Source
1 PACKAGE CS_ServiceRequest_PVT AS
2 /* $Header: csvsrs.pls 120.4 2006/08/30 20:59:18 spusegao noship $ */
3 -- -----------------------------------------------------------------------------
4 -- Structure Name : related_data_type
5 -- components :
6 -- close_flag Close flag asociated with SR status
7 -- old_close_flag Close flag asociated with new value of SR
8 -- status before update
9 -- disallow_request_update Request update indicator associated with new
10 -- value of SR status
11 -- old_disallow_request_update Request update indicator associated with SR
12 -- status before update
13 -- disallow_owner_update Request owner update ind associated with
14 -- new value of SR status
15 -- old_disallow_owner_update Request owner update ind associated with
16 -- SR status before update
17 -- disallow_product_update Request product update ind associated with
18 -- new value of SR status
19 -- old_disallow_product_update Request product update ind associated with
20 -- SR status before update
21 -- pending_approval_flag pending approval flag associated with new
22 -- value of SR status
23 -- old_pending_approval_flag pending approval flag associated with
24 -- SR status before update
25 -- intermediate_status_id intermediate status associated with new
26 -- value of SR
27 -- old_intermediate_status_id intermediate status associated with SR
28 -- before update
29 -- approval_action_status_id approval status associated with new
30 -- value of SR status
31 -- old_approval_action_status_id approval status associated with SR status
32 -- before update
33 -- rejection_action_status_id Rejection status associated with new value
34 -- SR status
35 -- old_rejection_action_status_id Rejection status associated with SR status
36 -- before update
37 -- target_status_id Same as status_id passed to SR API
38 -- autolaunch_workflow_flag Launch workflow flag associated with SR Type
39 -- abort_workflow_close_flag Abort Workflow flag associated with SR Type
40 -- workflow Workflow associated with SR Type
41 -- business_process_id Business process associated with SR Type
42 -- primary_party_id Primary contact for SR
43 -- primary_contact_point_id Contact point for Primary contact
44 -- Description : This structure holds certain attributes related following SR
45 -- attributes
46 -- 1. old value of SR status
47 -- 2. new value of SR status
48 -- 3. new value of SR type
49 -- Modification History
50 -- Date Name Description
51 -- -------- -------- -----------------------------------------------------------
52 -- 07/10/05 smisra Created
53 -- -----------------------------------------------------------------------------
54 TYPE related_data_type IS RECORD
55 ( close_flag cs_incident_statuses_b.close_flag % TYPE
56 , old_close_flag cs_incident_statuses_b.close_flag % TYPE
57 , disallow_request_update cs_incident_statuses_b.disallow_request_update % TYPE
58 , old_disallow_request_update cs_incident_statuses_b.disallow_request_update % TYPE
59 , disallow_owner_update cs_incident_statuses_b.disallow_agent_dispatch % TYPE
60 , old_disallow_owner_update cs_incident_statuses_b.disallow_agent_dispatch % TYPE
61 , disallow_product_update cs_incident_statuses_b.disallow_product_update % TYPE
62 , old_disallow_product_update cs_incident_statuses_b.disallow_product_update % TYPE
63 , pending_approval_flag cs_incident_statuses_b.pending_approval_flag % TYPE
64 , old_pending_approval_flag cs_incident_statuses_b.pending_approval_flag % TYPE
65 , intermediate_status_id cs_incident_statuses_b.intermediate_status_id % TYPE
66 , old_intermediate_status_id cs_incident_statuses_b.intermediate_status_id % TYPE
67 , approval_action_status_id cs_incident_statuses_b.approval_action_status_id % TYPE
68 , old_approval_action_status_id cs_incident_statuses_b.approval_action_status_id % TYPE
69 , rejection_action_status_id cs_incident_statuses_b.rejection_action_status_id % TYPE
70 , old_rejection_action_status_id cs_incident_statuses_b.rejection_action_status_id % TYPE
71 , target_status_id cs_incident_statuses_b.incident_status_id % TYPE
72 , autolaunch_workflow_flag cs_incident_types_b.autolaunch_workflow_flag % TYPE
73 , abort_workflow_close_flag cs_incident_types_b.abort_workflow_close_flag % TYPE
74 , workflow cs_incident_types_b.workflow % TYPE
75 , business_process_id cs_incident_types_b.business_process_id % TYPE
76 , primary_party_id cs_hz_sr_contact_points.party_id % TYPE
77 , primary_contact_point_id cs_hz_sr_contact_points.contact_point_id % TYPE
78 );
79 ------------------------------------------------------------------------
80 -- Set up record types to be used for the Validate_ServiceRequest_Record Procedure
81 ------------------------------------------------------------------------
82 TYPE Request_Validation_Rec_Type IS RECORD
83 ( type_id NUMBER := FND_API.G_MISS_NUM,
84 status_id NUMBER := FND_API.G_MISS_NUM,
85 severity_id NUMBER := FND_API.G_MISS_NUM,
86 urgency_id NUMBER := FND_API.G_MISS_NUM,
87 resource_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
88 owner_id NUMBER := FND_API.G_MISS_NUM,
89 publish_flag VARCHAR2(1) := FND_API.G_MISS_CHAR,
90 customer_id NUMBER := FND_API.G_MISS_NUM,
91 employee_id NUMBER := FND_API.G_MISS_NUM,
92 contact_id NUMBER := FND_API.G_MISS_NUM,
93 represented_by_id NUMBER := FND_API.G_MISS_NUM,
94 customer_product_id NUMBER := FND_API.G_MISS_NUM,
95 inventory_item_id NUMBER := FND_API.G_MISS_NUM,
96 inventory_org_id NUMBER := FND_API.G_MISS_NUM,
97 problem_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
98 exp_resolution_date DATE := FND_API.G_MISS_DATE,
99 rma_header_id NUMBER := FND_API.G_MISS_NUM,
100 bill_to_site_use_id NUMBER := FND_API.G_MISS_NUM,
101 bill_to_contact_id NUMBER := FND_API.G_MISS_NUM,
102 ship_to_site_use_id NUMBER := FND_API.G_MISS_NUM,
103 ship_to_contact_id NUMBER := FND_API.G_MISS_NUM,
104 -- Nullified value as thic column will no longer be used and install_site
105 -- id column will be used instead by shijain dec4th 2002
106 install_site_use_id NUMBER := FND_API.G_MISS_NUM,
107 resolution_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
108 act_resolution_date DATE := FND_API.G_MISS_DATE,
109 current_contact_time_diff NUMBER := FND_API.G_MISS_NUM,
110 rep_by_time_difference NUMBER := FND_API.G_MISS_NUM,
111 validate_type VARCHAR2(1) := FND_API.G_FALSE,
112 validate_status VARCHAR2(1) := FND_API.G_FALSE,
113 validate_customer VARCHAR2(1) := FND_API.G_FALSE,
114 validate_employee VARCHAR2(1) := FND_API.G_FALSE,
115 validate_bill_to_site VARCHAR2(1) := FND_API.G_FALSE,
116 validate_ship_to_site VARCHAR2(1) := FND_API.G_FALSE,
117 validate_install_site VARCHAR2(1) := FND_API.G_FALSE,
118 contract_service_id NUMBER := FND_API.G_MISS_NUM,
119 --04/16/01
120 contract_id NUMBER := FND_API.G_MISS_NUM,
121 project_number VARCHAR2(120):= FND_API.G_MISS_CHAR,
122 --04/16/01
123 account_id NUMBER := FND_API.G_MISS_NUM,
124 site_id NUMBER := FND_API.G_MISS_NUM,
125 territory_id NUMBER := FND_API.G_MISS_NUM,
126 platform_id NUMBER := FND_API.G_MISS_NUM,
127 platform_version VARCHAR2(250) := FND_API.G_MISS_CHAR,
128 db_version VARCHAR2(250) := FND_API.G_MISS_CHAR,
129 platform_version_id NUMBER := FND_API.G_MISS_NUM,
130 cp_component_id NUMBER := FND_API.G_MISS_NUM,
131 cp_component_version_id NUMBER := FND_API.G_MISS_NUM,
132 cp_subcomponent_id NUMBER := FND_API.G_MISS_NUM,
133 cp_subcomponent_version_id NUMBER := FND_API.G_MISS_NUM,
134 cp_revision_id NUMBER := FND_API.G_MISS_NUM,
135 language_id NUMBER := FND_API.G_MISS_NUM,
136 inv_item_revision VARCHAR2(240):= FND_API.G_MISS_CHAR,
137 inv_component_id NUMBER:= FND_API.G_MISS_NUM,
138 inv_component_version VARCHAR2(90):= FND_API.G_MISS_CHAR,
139 inv_subcomponent_id NUMBER:= FND_API.G_MISS_NUM,
140 inv_subcomponent_version VARCHAR2(90):= FND_API.G_MISS_CHAR,
141 caller_type VARCHAR2(30):= FND_API.G_MISS_CHAR,
142 primary_contact_id NUMBER:= FND_API.G_MISS_NUM,
143 validate_updated_status VARCHAR2(1) := FND_API.G_FALSE,
144 updated_status_id NUMBER := FND_API.G_MISS_NUM,
145 status_id_change VARCHAR2(1) := FND_API.G_FALSE,
146 current_serial_number VARCHAR2(30) := FND_API.G_MISS_CHAR,
147 -----jngeorge-----enhancements-----11.5.6 ------07/20/01
148 tier VARCHAR2(250) :=FND_API.G_MISS_CHAR,
149 tier_version VARCHAR2(250) := FND_API.G_MISS_CHAR,
150 operating_system VARCHAR2(250) := FND_API.G_MISS_CHAR,
151 operating_system_version VARCHAR2(250) := FND_API.G_MISS_CHAR,
152 database VARCHAR2(250) :=FND_API.G_MISS_CHAR,
153 cust_pref_lang_id NUMBER := FND_API.G_MISS_NUM,
154 category_id NUMBER := FND_API.G_MISS_NUM,
155 owner_group_id NUMBER := FND_API.G_MISS_NUM,
156 group_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
157 group_territory_id NUMBER := FND_API.G_MISS_NUM,
158 inv_platform_org_id NUMBER := FND_API.G_MISS_NUM,
159 product_revision VARCHAR2(240) := FND_API.G_MISS_CHAR,
160 component_version VARCHAR2(3) := FND_API.G_MISS_CHAR,
161 subcomponent_version VARCHAR2(3) := FND_API.G_MISS_CHAR,
162 comm_pref_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
163 cust_pref_lang_code VARCHAR2(4) := FND_API.G_MISS_CHAR,
164 category_set_id NUMBER := FND_API.G_MISS_NUM,
165 external_reference VARCHAR2(30) := FND_API.G_MISS_CHAR,
166 system_id NUMBER := FND_API.G_MISS_NUM,
167 ---- Added for Enh# 1830701
168 request_date DATE := FND_API.G_MISS_DATE,
169 incident_occurred_date DATE := FND_API.G_MISS_DATE,
170 incident_resolved_date DATE := FND_API.G_MISS_DATE,
171 inc_responded_by_date DATE := FND_API.G_MISS_DATE,
172 ---- Added for Enh# 222054
173 incident_location_id NUMBER := FND_API.G_MISS_NUM,
174 incident_country VARCHAR2(60) := FND_API.G_MISS_CHAR,
175 --- Added for ER# 2433831
176 bill_to_account_id NUMBER := FND_API.G_MISS_NUM ,
177 ship_to_account_id NUMBER := FND_API.G_MISS_NUM ,
178 --- Added for ER# 2463321
179 customer_phone_id NUMBER := FND_API.G_MISS_NUM ,
180 customer_email_id NUMBER := FND_API.G_MISS_NUM ,
181 bill_to_party_id NUMBER := FND_API.G_MISS_NUM ,
182 ship_to_party_id NUMBER := FND_API.G_MISS_NUM ,
183 bill_to_site_id NUMBER := FND_API.G_MISS_NUM ,
184 ship_to_site_id NUMBER := FND_API.G_MISS_NUM ,
185
186 -- Added address related columns by shijain 4th dec 2002
187 incident_point_of_interest Varchar2(240):=FND_API.G_MISS_CHAR ,
188 incident_cross_street Varchar2(240):=FND_API.G_MISS_CHAR ,
189 incident_direction_qualifier Varchar2(30):=FND_API.G_MISS_CHAR,
190 incident_distance_qualifier Varchar2(240):=FND_API.G_MISS_CHAR ,
191 incident_distance_qual_uom Varchar2(30):=FND_API.G_MISS_CHAR,
192 incident_address2 Varchar2(240):=FND_API.G_MISS_CHAR ,
193 incident_address3 Varchar2(240):=FND_API.G_MISS_CHAR,
194 incident_address4 Varchar2(240):=FND_API.G_MISS_CHAR ,
195 incident_address_style Varchar2(30):=FND_API.G_MISS_CHAR,
196 incident_addr_lines_phonetic Varchar2(560):=FND_API.G_MISS_CHAR ,
197 incident_po_box_number Varchar2(50):=FND_API.G_MISS_CHAR ,
198 incident_house_number Varchar2(50):=FND_API.G_MISS_CHAR,
199 incident_street_suffix Varchar2(50):=FND_API.G_MISS_CHAR ,
200 incident_street Varchar2(150):=FND_API.G_MISS_CHAR,
201 incident_street_number Varchar2(50):=FND_API.G_MISS_CHAR ,
202 incident_floor Varchar2(50):=FND_API.G_MISS_CHAR ,
203 incident_suite Varchar2(50):=FND_API.G_MISS_CHAR ,
204 incident_postal_plus4_code Varchar2(30):=FND_API.G_MISS_CHAR ,
205 incident_position Varchar2(50):=FND_API.G_MISS_CHAR ,
206 incident_location_directions Varchar2(640):=FND_API.G_MISS_CHAR,
207 incident_location_description Varchar2(2000):=FND_API.G_MISS_CHAR ,
208 install_site_id NUMBER := FND_API.G_MISS_NUM ,
209 group_owner Varchar2(60):=FND_API.G_MISS_CHAR,
210 owner Varchar2(360):=FND_API.G_MISS_CHAR,
211 --------------------anmukher--------------------08/01/03
212 -- Added for CMRO-EAM project of Release 11.5.10
213 item_serial_number VARCHAR2(30) := FND_API.G_MISS_CHAR,
214 owning_dept_id NUMBER := FND_API.G_MISS_NUM,
215 ---------------------anmukher--------------------08/18/03
216 incident_location_type VARCHAR2(30) := FND_API.G_MISS_CHAR,
217 --Added for bug 3635269
218 sr_creation_channel VARCHAR2(50) := FND_API.G_MISS_CHAR,
219 maint_organization_id NUMBER := FND_API.G_MISS_NUM,
220 old_type_id NUMBER
221 );
222 --------------------------------------------------------------------------
223 -- Start of comments
224 -- Record Type : Request_Validation_Rec_Type
225 -- Description : Holds the Service Request attribute Ids for validation.
226 -- type_id NUMBER Optional
227 -- Service request type identifier
228 -- status_id NUMBER Optional
229 -- Service request status identifier
230 -- severity_id NUMBER Optional
231 -- Service request severity identifier
232 -- urgency_id NUMBER Optional
233 -- Service request urgency identifier
234 -----------------------------------------------------
235 -- This field is no longer there in the Request_Validation_Rec_Type
236 -- closed_date DATE Optional
237 -- Closed Date
238 ------------------------------------------------------
239 -- resource_type VARCHAR2 Optional
240
241 -- owner_id NUMBER Optional
242 -- Service request owner identifier
243 --
244 -- publish_flag VARCHAR2(1) Optional
245
246 -- customer_id NUMBER Optional
247 -- Service request customer identifier
248
249 -- employee_id NUMBER Optional
250
251 -- contact_id NUMBER Optional
252 -- Customer contact identifier
253
254 -- represented_by_id NUMBER Optional
255 -- Represented By identifier
256
257 -- customer_product_id NUMBER Optional
258 -- Unique identifier for a customer product in the Installed Base
259
260 -- inventory_item_id NUMBER Optional
261 -- Inventory item identifier
262
263 -- problem_code VARCHAR2(30) Optional
264 -- Service request problem code
265
266 -- exp_resolution_date DATE Optional
270 -- Sales order header identifier of the RMA
267 -- Expected resolution date
268
269 -- rma_header_id NUMBER Optional
271
272 -- bill_to_site_use_id NUMBER Optional
273 -- Bill To site use identifier
274
275 -- bill_to_contact_id NUMBER Optional
276 -- Bill To contact identifier
277
278 -- ship_to_site_use_id NUMBER Optional
279 -- Ship To site use identifier
280
281 -- ship_to_contact_id NUMBER Optional
282 -- Ship To contact identifier
283
284 -- install_site_use_id NUMBER Optional
285 -- Install site use identifier
286
287 -- resolution_code VARCHAR2(30) Optional
288 -- Service request resolution code
289
290 -- act_resolution_date DATE Optional
291 -- Actual resolution date
292
293 -- current_contact_time_diff NUMBER Optional
294 -- Current Contact Time Diff
295
296 -- rep_by_time_difference NUMBER Optional
297 -- Represented By Time Diff
298
299 -- validate_type VARCHAR2(1) Optional
300 -- Whether or not to validate type_id
301
302 -- validate_status VARCHAR2(1) Optional
303 -- Whether or not to validate status_id
304
305 -- validate_customer VARCHAR2(1) Optional
306 -- Whether or not to validate customer_id
307
308 -- validate_employee VARCHAR2(1) Optional
309 -- Whether or not to validate employee_id
310
311 -- validate_bill_to_site VARCHAR2(1) Optional
312 -- Whether or not to validate bill_to_site_use_id
313
314 -- validate_ship_to_site VARCHAR2(1) Optional
315 -- Whether or not to validate ship_to_site_use_id
316
317 -- validate_install_site VARCHAR2(1) Optional
318 -- Whether or not to validate install_site_use_id
319
320 -- coverage_type VARCHAR2(30) Optional
321 -- Service Request Coverage Type
322
323 -- bill_to_account_id NUMBER Optional
324 -- Service Request Bill To Account Identifier
325
326 -- ship_to_account_id NUMBER Optional
327 -- Service Request Ship To Account Identifier
328
329 -- customer_phone_id NUMBER Optional
330 -- SR Customer's non-primary phone Id
331
332 -- customer_email_id NUMBER Optional
333 -- SR Customer's non-primary Email Id
334 -- End of comments
335
336 --------------------------------------------------------------------------
337 -- Start of comments
338 -- Procedure : Validate_ServiceRequest_Record
339 -- Description : Validate all non-missing record fields.
340 -- Parameters :
341 -- IN :
342 -- p_api_name IN VARCHAR2 Required
343 -- Name of the calling procedure.
344 -- p_service_request_rec IN Request_Validation_Rec_Type Required
345 -- Record which holds all the non-missing record fields to be
346 -- validated.
347 -- p_request_date IN DATE Optional
348 -- Service request date; required for closed date, expected
349 -- resolution date, and actual resolution date validation.
350 -- p_org_id IN NUMBER Optional
351 -- Operating unit identifier; required for owner, customer
352 -- contact, customer product, sales order (RMA), Bill To site,
353 -- Bill To contact, Ship To site, Ship To contact validation.
354 -- p_resp_appl_id IN NUMBER Optional
355 -- p_resp_id IN NUMBER Optional
356 -- p_user_id IN NUMBER Optional
357 -- p_inventory_org_id IN NUMBER Optional
358 -- Organization identifier; required for inventory item
359 -- validation.
360 -- OUT :
361 -- p_close_flag OUT VARCHAR2(1)
362 -- Returned iff status_id is passed
363 -- 'Y' => the given status is a "closed" status
364 -- p_employee_name OUT VARCHAR2(240)
365 -- Returned iff employee_id is passed
366
367 -- The following 3 fields are returned iff type_id is passed.
368 -- p_autolaunch_workflow_flag OUT VARCHAR2,
369 -- p_abort_workflow_close_flag OUT VARCHAR2,
370 -- p_workflow_process_name OUT VARCHAR2,
371 -- p_inventory_item_id OUT NUMBER
372 -- Returned iff customer_product_id is passed
373 -- Identifies an item for a customer product
374 -- p_return_status OUT VARCHAR2(1)
375 -- FND_API.G_RET_STS_SUCCESS => all non-missing fields are valid
376 -- FND_API.G_RET_STS_ERROR => one or more non-missing fields
377 -- are invalid
378 -- Notes :
379 -- Please take into considerations the following dependencies when
380 -- passing in parameters and record fields. For example, the status of
381 -- the service request has dependency on the type of the service request.
382 -- Therefore, the request type must be passed in for status validation.
383 -- 1. type_id requires status_id
384 -- 2. status_id requires type_id
388 -- p_user_id
385 -- 3. closed_date requires p_request_date
386 -- 4. owner_id requires p_org_id
387 -- 5. publish_flag requires p_resp_appl_id, p_resp_id,
389 -- 6. employee_id requires p_org_id
390 -- 7. contact_id requires p_org_id, customer_id or
391 -- customer_product_id
392 -- 8. customer_product_id requires p_org_id
393 -- 9. inventory_item_id requires p_inventory_org_id
394 -- 10. exp_resolution_date requires p_request_date
395 -- 11. rma_header_id requires p_org_id, customer_id or
396 -- customer_product_id
397 -- 12. bill_to_site_use_id requires p_org_id, customer_id or
398 -- customer_product_id
399 -- 13. bill_to_contact_id requires p_org_id, bill_to_site_use_id or
400 -- customer_id or customer_product_id
401 -- 14. ship_to_site_use_id requires p_org_id, customer_id or
402 -- customer_product_id
403 -- 15. ship_to_contact_id requires p_org_id, ship_to_site_use_id or
404 -- customer_id or customer_product_id
405 -- 16. act_resolution_date requires request_date
406 -- End of comments
407 --------------------------------------------------------------------------
408
409 -----------------------------------------------------------
410 -- Set up record types to be used for the audit record API
411 -----------------------------------------------------------
412 /*************** Comment out these 3 audit recs and use New Audit Rec **
413 TYPE audit_flags_rec_type IS RECORD (
414 change_status VARCHAR2(1) := FND_API.G_FALSE,
415 change_owner VARCHAR2(1) := FND_API.G_FALSE,
416 change_group VARCHAR2(1) := FND_API.G_FALSE,
417 change_group_type VARCHAR2(1) := FND_API.G_FALSE,
418 change_assigned_time VARCHAR2(1) := FND_API.G_FALSE,
419 change_platform_org_id VARCHAR2(1) := FND_API.G_FALSE,
420 change_type VARCHAR2(1) := FND_API.G_FALSE,
421 change_urgency VARCHAR2(1) := FND_API.G_FALSE,
422 change_severity VARCHAR2(1) := FND_API.G_FALSE,
423 change_exp_res_date VARCHAR2(1) := FND_API.G_FALSE,
424 new_action VARCHAR2(1) := FND_API.G_FALSE,
425 new_workflow VARCHAR2(1) := FND_API.G_FALSE,
426 change_obligation_date VARCHAR2(1) := FND_API.G_FALSE,
427 change_site_id VARCHAR2(1) := FND_API.G_FALSE,
428 change_contact_id VARCHAR2(1) := FND_API.G_FALSE,
429 change_bill_to_contact_id VARCHAR2(1) := FND_API.G_FALSE,
430 change_ship_to_contact_id VARCHAR2(1) := FND_API.G_FALSE,
431 change_incident_date VARCHAR2(1) := FND_API.G_FALSE,
432 change_close_date VARCHAR2(1) := FND_API.G_FALSE,
433 change_customer_product_id VARCHAR2(1) := FND_API.G_FALSE,
434 change_platform_id VARCHAR2(1) := FND_API.G_FALSE,
435 change_plat_ver_id VARCHAR2(1) := FND_API.G_FALSE,
436 change_cp_comp_id VARCHAR2(1) := FND_API.G_FALSE,
437 change_cp_pro_rev VARCHAR2(1) := FND_API.G_FALSE,
438 change_cp_comp_ver_id VARCHAR2(1) := FND_API.G_FALSE,
439 change_cp_comp_ver VARCHAR2(1) := FND_API.G_FALSE,
440 change_cp_subcomp_id VARCHAR2(1) := FND_API.G_FALSE,
441 change_cp_subcomp_ver VARCHAR2(1) := FND_API.G_FALSE,
442 change_cp_subcomp_ver_id VARCHAR2(1) := FND_API.G_FALSE,
443 change_language_id VARCHAR2(1) := FND_API.G_FALSE,
444 change_cp_rev_id VARCHAR2(1) := FND_API.G_FALSE,
445 change_inv_item_rev VARCHAR2(1) := FND_API.G_FALSE,
446 change_inv_comp_id VARCHAR2(1) := FND_API.G_FALSE,
447 change_inv_comp_ver VARCHAR2(1) := FND_API.G_FALSE,
451 change_resource_type VARCHAR2(1) := FND_API.G_FALSE
448 change_inv_subcomp_id VARCHAR2(1) := FND_API.G_FALSE,
449 change_inv_subcomp_ver VARCHAR2(1) := FND_API.G_FALSE,
450 change_territory_id VARCHAR2(1) := FND_API.G_FALSE,
452 );
453
454 TYPE audit_vals_rec_type IS RECORD (
455 status_id NUMBER := FND_API.G_MISS_NUM,
456 owner_id NUMBER := FND_API.G_MISS_NUM,
457 group_id NUMBER := FND_API.G_MISS_NUM,
458 group_type VARCHAR2(30) :=FND_API.G_MISS_CHAR,
459 owner_assigned_time DATE := FND_API.G_MISS_DATE,
460 inv_platform_org_id NUMBER := FND_API.G_MISS_NUM,
461 type_id NUMBER := FND_API.G_MISS_NUM,
462 urgency_id NUMBER := FND_API.G_MISS_NUM,
463 severity_id NUMBER := FND_API.G_MISS_NUM,
464 exp_res_date DATE := FND_API.G_MISS_DATE,
465 obligation_date DATE := FND_API.G_MISS_DATE,
466 site_id NUMBER := FND_API.G_MISS_NUM,
467 contact_id NUMBER := FND_API.G_MISS_NUM,
468 bill_to_contact_id NUMBER := FND_API.G_MISS_NUM,
469 ship_to_contact_id NUMBER := FND_API.G_MISS_NUM,
470 incident_date DATE := FND_API.G_MISS_DATE,
471 close_date DATE := FND_API.G_MISS_DATE,
472 customer_product_id NUMBER := FND_API.G_MISS_NUM,
473 platform_id NUMBER := FND_API.G_MISS_NUM,
474 platform_version_id NUMBER := FND_API.G_MISS_NUM,
475 cp_component_id NUMBER := FND_API.G_MISS_NUM,
476 product_revision VARCHAR2(240):= FND_API.G_MISS_CHAR,
477 cp_component_version_id NUMBER := FND_API.G_MISS_NUM,
478 component_version VARCHAR2(3):= FND_API.G_MISS_CHAR,
479 cp_subcomponent_id NUMBER := FND_API.G_MISS_NUM,
480 cp_subcomponent_version_id NUMBER := FND_API.G_MISS_NUM,
481 subcomponent_version VARCHAR2(3):= FND_API.G_MISS_CHAR,
482 language_id NUMBER := FND_API.G_MISS_NUM,
483 cp_revision_id NUMBER := FND_API.G_MISS_NUM,
484 inv_item_revision VARCHAR2(240) := FND_API.G_MISS_CHAR,
485 inv_component_id NUMBER := FND_API.G_MISS_NUM,
486 inv_component_version VARCHAR2(90) := FND_API.G_MISS_CHAR,
487 inv_subcomponent_id NUMBER := FND_API.G_MISS_NUM,
488 inv_subcomponent_version VARCHAR2(90) := FND_API.G_MISS_CHAR,
489 territory_id NUMBER := FND_API.G_MISS_NUM,
490 resource_type VARCHAR2(30) := FND_API.G_MISS_CHAR
491 );
492 G_MISS_AUDIT_VALS_REC audit_vals_rec_type;
493 ********** End of Comments - Use the Ne Audit Record ***/
494
495 TYPE sr_audit_rec_type IS RECORD (
496 INCIDENT_STATUS_ID NUMBER(15) ,
497 OLD_INCIDENT_STATUS_ID NUMBER(15) ,
498 CHANGE_INCIDENT_STATUS_FLAG VARCHAR2(1),
499 INCIDENT_TYPE_ID NUMBER(15) ,
500 OLD_INCIDENT_TYPE_ID NUMBER(15) ,
501 CHANGE_INCIDENT_TYPE_FLAG VARCHAR2(1),
502 INCIDENT_URGENCY_ID NUMBER(15) ,
503 OLD_INCIDENT_URGENCY_ID NUMBER(15) ,
504 CHANGE_INCIDENT_URGENCY_FLAG VARCHAR2(1),
505 INCIDENT_SEVERITY_ID NUMBER(15) ,
506 OLD_INCIDENT_SEVERITY_ID NUMBER(15) ,
507 CHANGE_INCIDENT_SEVERITY_FLAG VARCHAR2(1),
508 RESPONSIBLE_GROUP_ID NUMBER(15) ,
509 OLD_RESPONSIBLE_GROUP_ID NUMBER(15) ,
510 CHANGE_RESPONSIBLE_GROUP_FLAG VARCHAR2(1),
511 INCIDENT_OWNER_ID NUMBER(15) ,
512 OLD_INCIDENT_OWNER_ID NUMBER(15) ,
513 CHANGE_INCIDENT_OWNER_FLAG VARCHAR2(1),
514 CREATE_MANUAL_ACTION VARCHAR2(1),
515 ACTION_ID NUMBER(15) ,
516 EXPECTED_RESOLUTION_DATE DATE ,
517 OLD_EXPECTED_RESOLUTION_DATE DATE ,
518 CHANGE_RESOLUTION_FLAG VARCHAR2(1) ,
519 NEW_WORKFLOW_FLAG VARCHAR2(1) ,
520 WORKFLOW_PROCESS_NAME VARCHAR2(30),
521 WORKFLOW_PROCESS_ITEMKEY VARCHAR2(240),
522 GROUP_ID NUMBER ,
523 OLD_GROUP_ID NUMBER ,
524 CHANGE_GROUP_FLAG VARCHAR2(1) ,
525 OBLIGATION_DATE DATE ,
526 OLD_OBLIGATION_DATE DATE ,
527 CHANGE_OBLIGATION_FLAG VARCHAR2(1) ,
528 SITE_ID NUMBER ,
529 OLD_SITE_ID NUMBER ,
530 CHANGE_SITE_FLAG VARCHAR2(1),
531 BILL_TO_CONTACT_ID NUMBER(15) ,
532 OLD_BILL_TO_CONTACT_ID NUMBER(15) ,
533 CHANGE_BILL_TO_FLAG VARCHAR2(1),
534 SHIP_TO_CONTACT_ID NUMBER(15) ,
535 OLD_SHIP_TO_CONTACT_ID NUMBER(15) ,
536 CHANGE_SHIP_TO_FLAG VARCHAR2(1) ,
537 INCIDENT_DATE DATE ,
538 OLD_INCIDENT_DATE DATE ,
539 CHANGE_INCIDENT_DATE_FLAG VARCHAR2(1) ,
540 CLOSE_DATE DATE ,
541 OLD_CLOSE_DATE DATE ,
545 CHANGE_CUSTOMER_PRODUCT_FLAG VARCHAR2(1) ,
542 CHANGE_CLOSE_DATE_FLAG VARCHAR2(1) ,
543 CUSTOMER_PRODUCT_ID NUMBER(15) ,
544 OLD_CUSTOMER_PRODUCT_ID NUMBER(15) ,
546 PLATFORM_ID NUMBER ,
547 OLD_PLATFORM_ID NUMBER ,
548 CHANGE_PLATFORM_ID_FLAG VARCHAR2(1) ,
549 PLATFORM_VERSION_ID NUMBER ,
550 OLD_PLATFORM_VERSION_ID NUMBER ,
551 CHANGE_PLAT_VER_ID_FLAG VARCHAR2(1) ,
552 CP_COMPONENT_ID NUMBER ,
553 OLD_CP_COMPONENT_ID NUMBER ,
554 CHANGE_CP_COMPONENT_ID_FLAG VARCHAR2(1) ,
555 CP_COMPONENT_VERSION_ID NUMBER ,
556 OLD_CP_COMPONENT_VERSION_ID NUMBER ,
557 CHANGE_CP_COMP_VER_ID_FLAG VARCHAR2(1) ,
558 CP_SUBCOMPONENT_ID NUMBER ,
559 OLD_CP_SUBCOMPONENT_ID NUMBER ,
560 CHANGE_CP_SUBCOMPONENT_ID_FLAG VARCHAR2(1) ,
561 CP_SUBCOMPONENT_VERSION_ID NUMBER ,
562 OLD_CP_SUBCOMPONENT_VERSION_ID NUMBER ,
563 CHANGE_CP_SUBCOMP_VER_ID_FLAG VARCHAR2(1) ,
564 LANGUAGE_ID NUMBER ,
565 OLD_LANGUAGE_ID NUMBER ,
566 CHANGE_LANGUAGE_ID_FLAG VARCHAR2(1) ,
567 TERRITORY_ID NUMBER ,
568 OLD_TERRITORY_ID NUMBER ,
569 CHANGE_TERRITORY_ID_FLAG VARCHAR2(1) ,
570 CP_REVISION_ID NUMBER ,
571 OLD_CP_REVISION_ID NUMBER ,
572 CHANGE_CP_REVISION_ID_FLAG VARCHAR2(1) ,
573 INV_ITEM_REVISION VARCHAR2(240) ,
574 OLD_INV_ITEM_REVISION VARCHAR2(240) ,
575 CHANGE_INV_ITEM_REVISION VARCHAR2(1) ,
576 INV_COMPONENT_ID NUMBER ,
577 OLD_INV_COMPONENT_ID NUMBER ,
578 CHANGE_INV_COMPONENT_ID VARCHAR2(1) ,
579 INV_COMPONENT_VERSION VARCHAR2(90) ,
580 OLD_INV_COMPONENT_VERSION VARCHAR2(90) ,
581 CHANGE_INV_COMPONENT_VERSION VARCHAR2(1) ,
582 INV_SUBCOMPONENT_ID NUMBER ,
583 OLD_INV_SUBCOMPONENT_ID NUMBER ,
584 CHANGE_INV_SUBCOMPONENT_ID VARCHAR2(1) ,
585 INV_SUBCOMPONENT_VERSION VARCHAR2(90) ,
586 OLD_INV_SUBCOMPONENT_VERSION VARCHAR2(90) ,
587 CHANGE_INV_SUBCOMP_VERSION VARCHAR2(1) ,
588 RESOURCE_TYPE VARCHAR2(30) ,
589 OLD_RESOURCE_TYPE VARCHAR2(30) ,
590 CHANGE_RESOURCE_TYPE_FLAG VARCHAR2(1) ,
591 SECURITY_GROUP_ID NUMBER ,
592 UPGRADED_STATUS_FLAG VARCHAR2(1) ,
593 OLD_GROUP_TYPE VARCHAR2(30) ,
594 GROUP_TYPE VARCHAR2(30) ,
595 CHANGE_GROUP_TYPE_FLAG VARCHAR2(1) ,
596 OLD_OWNER_ASSIGNED_TIME DATE ,
597 OWNER_ASSIGNED_TIME DATE ,
598 CHANGE_ASSIGNED_TIME_FLAG VARCHAR2(1) ,
599 INV_PLATFORM_ORG_ID NUMBER ,
600 OLD_INV_PLATFORM_ORG_ID NUMBER ,
601 CHANGE_PLATFORM_ORG_ID_FLAG VARCHAR2(1) ,
602 COMPONENT_VERSION VARCHAR2(3) ,
603 OLD_COMPONENT_VERSION VARCHAR2(3) ,
604 CHANGE_COMP_VER_FLAG VARCHAR2(1) ,
605 SUBCOMPONENT_VERSION VARCHAR2(3) ,
606 OLD_SUBCOMPONENT_VERSION VARCHAR2(3) ,
607 CHANGE_SUBCOMP_VER_FLAG VARCHAR2(1) ,
608 PRODUCT_REVISION VARCHAR2(240) ,
609 OLD_PRODUCT_REVISION VARCHAR2(240) ,
610 CHANGE_PRODUCT_REVISION_FLAG VARCHAR2(1) ,
611 STATUS_FLAG VARCHAR2(3) ,
612 OLD_STATUS_FLAG VARCHAR2(3) ,
613 CHANGE_STATUS_FLAG VARCHAR2(3) ,
614 INVENTORY_ITEM_ID NUMBER(15),
615 OLD_INVENTORY_ITEM_ID NUMBER(15),
616 CHANGE_INVENTORY_ITEM_FLAG VARCHAR2(3),
617 INV_ORGANIZATION_ID NUMBER,
618 OLD_INV_ORGANIZATION_ID NUMBER,
619 CHANGE_INV_ORGANIZATION_FLAG VARCHAR2(3),
620 PRIMARY_CONTACT_ID NUMBER,
621 CHANGE_PRIMARY_CONTACT_FLAG VARCHAR2(3),
622 OLD_PRIMARY_CONTACT_ID NUMBER,
623 -- Added for Enhanced Auditing features in 11.5.10 --anmukher --09/02/03
624 UPGRADE_FLAG_FOR_CREATE VARCHAR2(1),
625 OLD_INCIDENT_NUMBER VARCHAR2(64),
626 INCIDENT_NUMBER VARCHAR2(64),
627 OLD_CUSTOMER_ID NUMBER(15),
628 CUSTOMER_ID NUMBER(15),
629 OLD_BILL_TO_SITE_USE_ID NUMBER(15),
630 BILL_TO_SITE_USE_ID NUMBER(15),
631 OLD_EMPLOYEE_ID NUMBER(15),
632 EMPLOYEE_ID NUMBER(15),
633 OLD_SHIP_TO_SITE_USE_ID NUMBER(15),
634 SHIP_TO_SITE_USE_ID NUMBER(15),
638 ACTUAL_RESOLUTION_DATE DATE,
635 OLD_PROBLEM_CODE VARCHAR2(50),
636 PROBLEM_CODE VARCHAR2(50),
637 OLD_ACTUAL_RESOLUTION_DATE DATE,
639 OLD_INSTALL_SITE_USE_ID NUMBER(15),
640 INSTALL_SITE_USE_ID NUMBER(15),
641 -- OLD_PRODUCT_DESCRIPTION VARCHAR2(240),
642 -- PRODUCT_DESCRIPTION VARCHAR2(240),
643 OLD_CURRENT_SERIAL_NUMBER VARCHAR2(30),
644 CURRENT_SERIAL_NUMBER VARCHAR2(30),
645 OLD_SYSTEM_ID NUMBER(15),
646 SYSTEM_ID NUMBER(15),
647 OLD_INCIDENT_ATTRIBUTE_1 VARCHAR2(150),
648 INCIDENT_ATTRIBUTE_1 VARCHAR2(150),
649 OLD_INCIDENT_ATTRIBUTE_2 VARCHAR2(150),
650 INCIDENT_ATTRIBUTE_2 VARCHAR2(150),
651 OLD_INCIDENT_ATTRIBUTE_3 VARCHAR2(150),
652 INCIDENT_ATTRIBUTE_3 VARCHAR2(150),
653 OLD_INCIDENT_ATTRIBUTE_4 VARCHAR2(150),
654 INCIDENT_ATTRIBUTE_4 VARCHAR2(150),
655 OLD_INCIDENT_ATTRIBUTE_5 VARCHAR2(150),
656 INCIDENT_ATTRIBUTE_5 VARCHAR2(150),
657 OLD_INCIDENT_ATTRIBUTE_6 VARCHAR2(150),
658 INCIDENT_ATTRIBUTE_6 VARCHAR2(150),
659 OLD_INCIDENT_ATTRIBUTE_7 VARCHAR2(150),
660 INCIDENT_ATTRIBUTE_7 VARCHAR2(150),
661 OLD_INCIDENT_ATTRIBUTE_8 VARCHAR2(150),
662 INCIDENT_ATTRIBUTE_8 VARCHAR2(150),
663 OLD_INCIDENT_ATTRIBUTE_9 VARCHAR2(150),
664 INCIDENT_ATTRIBUTE_9 VARCHAR2(150),
665 OLD_INCIDENT_ATTRIBUTE_10 VARCHAR2(150),
666 INCIDENT_ATTRIBUTE_10 VARCHAR2(150),
667 OLD_INCIDENT_ATTRIBUTE_11 VARCHAR2(150),
668 INCIDENT_ATTRIBUTE_11 VARCHAR2(150),
669 OLD_INCIDENT_ATTRIBUTE_12 VARCHAR2(150),
670 INCIDENT_ATTRIBUTE_12 VARCHAR2(150),
671 OLD_INCIDENT_ATTRIBUTE_13 VARCHAR2(150),
672 INCIDENT_ATTRIBUTE_13 VARCHAR2(150),
673 OLD_INCIDENT_ATTRIBUTE_14 VARCHAR2(150),
674 INCIDENT_ATTRIBUTE_14 VARCHAR2(150),
675 OLD_INCIDENT_ATTRIBUTE_15 VARCHAR2(150),
676 INCIDENT_ATTRIBUTE_15 VARCHAR2(150),
677 OLD_INCIDENT_CONTEXT VARCHAR2(30),
678 INCIDENT_CONTEXT VARCHAR2(30),
679 OLD_RESOLUTION_CODE VARCHAR2(50),
680 RESOLUTION_CODE VARCHAR2(50),
681 OLD_ORIGINAL_ORDER_NUMBER NUMBER,
682 ORIGINAL_ORDER_NUMBER NUMBER,
683 OLD_ORG_ID NUMBER,
684 ORG_ID NUMBER,
685 OLD_PURCHASE_ORDER_NUMBER VARCHAR2(50),
686 PURCHASE_ORDER_NUMBER VARCHAR2(50),
687 OLD_PUBLISH_FLAG VARCHAR2(1),
688 PUBLISH_FLAG VARCHAR2(1),
689 OLD_QA_COLLECTION_ID NUMBER,
690 QA_COLLECTION_ID NUMBER,
691 OLD_CONTRACT_ID NUMBER,
692 CONTRACT_ID NUMBER,
693 OLD_CONTRACT_NUMBER VARCHAR2(120),
694 CONTRACT_NUMBER VARCHAR2(120),
695 OLD_CONTRACT_SERVICE_ID NUMBER,
696 CONTRACT_SERVICE_ID NUMBER,
697 OLD_TIME_ZONE_ID NUMBER(15),
698 TIME_ZONE_ID NUMBER(15),
699 OLD_ACCOUNT_ID NUMBER,
700 ACCOUNT_ID NUMBER,
701 OLD_TIME_DIFFERENCE NUMBER,
702 TIME_DIFFERENCE NUMBER,
703 OLD_CUSTOMER_PO_NUMBER VARCHAR2(50),
704 CUSTOMER_PO_NUMBER VARCHAR2(50),
705 OLD_CUSTOMER_TICKET_NUMBER VARCHAR2(50),
706 CUSTOMER_TICKET_NUMBER VARCHAR2(50),
707 OLD_CUSTOMER_SITE_ID NUMBER,
708 CUSTOMER_SITE_ID NUMBER,
709 OLD_CALLER_TYPE VARCHAR2(30),
710 CALLER_TYPE VARCHAR2(30),
711 OLD_SECURITY_GROUP_ID NUMBER(15),
712 OLD_ORIG_SYSTEM_REFERENCE VARCHAR2(60),
713 ORIG_SYSTEM_REFERENCE VARCHAR2(60),
714 OLD_ORIG_SYSTEM_REFERENCE_ID NUMBER,
715 ORIG_SYSTEM_REFERENCE_ID NUMBER,
716 REQUEST_ID NUMBER(15),
717 PROGRAM_APPLICATION_ID NUMBER(15),
718 PROGRAM_ID NUMBER(15),
719 PROGRAM_UPDATE_DATE DATE,
720 OLD_PROJECT_NUMBER VARCHAR2(120),
721 PROJECT_NUMBER VARCHAR2(120),
722 OLD_PLATFORM_VERSION VARCHAR2(250),
723 PLATFORM_VERSION VARCHAR2(250),
724 OLD_DB_VERSION VARCHAR2(250),
725 DB_VERSION VARCHAR2(250),
726 OLD_CUST_PREF_LANG_ID NUMBER,
727 CUST_PREF_LANG_ID NUMBER,
728 OLD_TIER VARCHAR2(250),
729 TIER VARCHAR2(250),
733 OPERATING_SYSTEM VARCHAR2(250),
730 OLD_CATEGORY_ID NUMBER,
731 CATEGORY_ID NUMBER,
732 OLD_OPERATING_SYSTEM VARCHAR2(250),
734 OLD_OPERATING_SYSTEM_VERSION VARCHAR2(250),
735 OPERATING_SYSTEM_VERSION VARCHAR2(250),
736 OLD_DATABASE VARCHAR2(250),
737 DATABASE VARCHAR2(250),
738 OLD_GROUP_TERRITORY_ID NUMBER,
739 GROUP_TERRITORY_ID NUMBER,
740 OLD_COMM_PREF_CODE VARCHAR2(30),
741 COMM_PREF_CODE VARCHAR2(30),
742 OLD_LAST_UPDATE_CHANNEL VARCHAR2(10),
743 LAST_UPDATE_CHANNEL VARCHAR2(10),
744 OLD_CUST_PREF_LANG_CODE VARCHAR2(4),
745 CUST_PREF_LANG_CODE VARCHAR2(4),
746 OLD_ERROR_CODE VARCHAR2(250),
747 ERROR_CODE VARCHAR2(250),
748 OLD_CATEGORY_SET_ID NUMBER,
749 CATEGORY_SET_ID NUMBER,
750 OLD_EXTERNAL_REFERENCE VARCHAR2(30),
751 EXTERNAL_REFERENCE VARCHAR2(30),
752 OLD_INCIDENT_OCCURRED_DATE DATE,
753 INCIDENT_OCCURRED_DATE DATE,
754 OLD_INCIDENT_RESOLVED_DATE DATE,
755 INCIDENT_RESOLVED_DATE DATE,
756 OLD_INC_RESPONDED_BY_DATE DATE,
757 INC_RESPONDED_BY_DATE DATE,
758 OLD_INCIDENT_LOCATION_ID NUMBER,
759 INCIDENT_LOCATION_ID NUMBER,
760 OLD_INCIDENT_ADDRESS VARCHAR2(960),
761 INCIDENT_ADDRESS VARCHAR2(960),
762 OLD_INCIDENT_CITY VARCHAR2(60),
763 INCIDENT_CITY VARCHAR2(60),
764 OLD_INCIDENT_STATE VARCHAR2(60),
765 INCIDENT_STATE VARCHAR2(60),
766 OLD_INCIDENT_COUNTRY VARCHAR2(60),
767 INCIDENT_COUNTRY VARCHAR2(60),
768 OLD_INCIDENT_PROVINCE VARCHAR2(60),
769 INCIDENT_PROVINCE VARCHAR2(60),
770 OLD_INCIDENT_POSTAL_CODE VARCHAR2(60),
771 INCIDENT_POSTAL_CODE VARCHAR2(60),
772 OLD_INCIDENT_COUNTY VARCHAR2(60),
773 INCIDENT_COUNTY VARCHAR2(240),
774 OLD_SR_CREATION_CHANNEL VARCHAR2(50),
775 SR_CREATION_CHANNEL VARCHAR2(50),
776 OLD_DEF_DEFECT_ID NUMBER,
777 DEF_DEFECT_ID NUMBER,
778 OLD_DEF_DEFECT_ID2 NUMBER,
779 DEF_DEFECT_ID2 NUMBER,
780 OLD_EXTERNAL_ATTRIBUTE_1 VARCHAR2(150),
781 EXTERNAL_ATTRIBUTE_1 VARCHAR2(150),
782 OLD_EXTERNAL_ATTRIBUTE_2 VARCHAR2(150),
783 EXTERNAL_ATTRIBUTE_2 VARCHAR2(150),
784 OLD_EXTERNAL_ATTRIBUTE_3 VARCHAR2(150),
785 EXTERNAL_ATTRIBUTE_3 VARCHAR2(150),
786 OLD_EXTERNAL_ATTRIBUTE_4 VARCHAR2(150),
787 EXTERNAL_ATTRIBUTE_4 VARCHAR2(150),
788 OLD_EXTERNAL_ATTRIBUTE_5 VARCHAR2(150),
789 EXTERNAL_ATTRIBUTE_5 VARCHAR2(150),
790 OLD_EXTERNAL_ATTRIBUTE_6 VARCHAR2(150),
791 EXTERNAL_ATTRIBUTE_6 VARCHAR2(150),
792 OLD_EXTERNAL_ATTRIBUTE_7 VARCHAR2(150),
793 EXTERNAL_ATTRIBUTE_7 VARCHAR2(150),
794 OLD_EXTERNAL_ATTRIBUTE_8 VARCHAR2(150),
795 EXTERNAL_ATTRIBUTE_8 VARCHAR2(150),
796 OLD_EXTERNAL_ATTRIBUTE_9 VARCHAR2(150),
797 EXTERNAL_ATTRIBUTE_9 VARCHAR2(150),
798 OLD_EXTERNAL_ATTRIBUTE_10 VARCHAR2(150),
799 EXTERNAL_ATTRIBUTE_10 VARCHAR2(150),
800 OLD_EXTERNAL_ATTRIBUTE_11 VARCHAR2(150),
801 EXTERNAL_ATTRIBUTE_11 VARCHAR2(150),
802 OLD_EXTERNAL_ATTRIBUTE_12 VARCHAR2(150),
803 EXTERNAL_ATTRIBUTE_12 VARCHAR2(150),
804 OLD_EXTERNAL_ATTRIBUTE_13 VARCHAR2(150),
805 EXTERNAL_ATTRIBUTE_13 VARCHAR2(150),
806 OLD_EXTERNAL_ATTRIBUTE_14 VARCHAR2(150),
807 EXTERNAL_ATTRIBUTE_14 VARCHAR2(150),
808 OLD_EXTERNAL_ATTRIBUTE_15 VARCHAR2(150),
809 EXTERNAL_ATTRIBUTE_15 VARCHAR2(150),
810 OLD_EXTERNAL_CONTEXT VARCHAR2(30),
811 EXTERNAL_CONTEXT VARCHAR2(30),
812 OLD_LAST_UPDATE_PROGRAM_CODE VARCHAR2(30),
813 LAST_UPDATE_PROGRAM_CODE VARCHAR2(30),
814 OLD_CREATION_PROGRAM_CODE VARCHAR2(30),
815 CREATION_PROGRAM_CODE VARCHAR2(30),
816 OLD_COVERAGE_TYPE VARCHAR2(30),
817 COVERAGE_TYPE VARCHAR2(30),
818 OLD_BILL_TO_ACCOUNT_ID NUMBER(15),
819 BILL_TO_ACCOUNT_ID NUMBER(15),
820 OLD_SHIP_TO_ACCOUNT_ID NUMBER(15),
821 SHIP_TO_ACCOUNT_ID NUMBER(15),
822 OLD_CUSTOMER_EMAIL_ID NUMBER(15),
823 CUSTOMER_EMAIL_ID NUMBER(15),
824 OLD_CUSTOMER_PHONE_ID NUMBER(15),
828 OLD_SHIP_TO_PARTY_ID NUMBER,
825 CUSTOMER_PHONE_ID NUMBER(15),
826 OLD_BILL_TO_PARTY_ID NUMBER,
827 BILL_TO_PARTY_ID NUMBER,
829 SHIP_TO_PARTY_ID NUMBER,
830 OLD_BILL_TO_SITE_ID NUMBER,
831 BILL_TO_SITE_ID NUMBER,
832 OLD_SHIP_TO_SITE_ID NUMBER,
833 SHIP_TO_SITE_ID NUMBER,
834 OLD_PROGRAM_LOGIN_ID NUMBER,
835 PROGRAM_LOGIN_ID NUMBER,
836 OLD_INCIDENT_POINT_OF_INTEREST VARCHAR2(240),
837 INCIDENT_POINT_OF_INTEREST VARCHAR2(240),
838 OLD_INCIDENT_CROSS_STREET VARCHAR2(240),
839 INCIDENT_CROSS_STREET VARCHAR2(240),
840 OLD_INCIDENT_DIRECTION_QUALIF VARCHAR2(30),
841 INCIDENT_DIRECTION_QUALIF VARCHAR2(30),
842 OLD_INCIDENT_DISTANCE_QUALIF VARCHAR2(240),
843 INCIDENT_DISTANCE_QUALIF VARCHAR2(240),
844 OLD_INCIDENT_DISTANCE_QUAL_UOM VARCHAR2(30),
845 INCIDENT_DISTANCE_QUAL_UOM VARCHAR2(240),
846 OLD_INCIDENT_ADDRESS2 VARCHAR2(240),
847 INCIDENT_ADDRESS2 VARCHAR2(240),
848 OLD_INCIDENT_ADDRESS3 VARCHAR2(240),
849 INCIDENT_ADDRESS3 VARCHAR2(240),
850 OLD_INCIDENT_ADDRESS4 VARCHAR2(240),
851 INCIDENT_ADDRESS4 VARCHAR2(240),
852 OLD_INCIDENT_ADDRESS_STYLE VARCHAR2(30),
853 INCIDENT_ADDRESS_STYLE VARCHAR2(30),
854 OLD_INCIDENT_ADDR_LNS_PHONETIC VARCHAR2(560),
855 INCIDENT_ADDR_LNS_PHONETIC VARCHAR2(560),
856 OLD_INCIDENT_PO_BOX_NUMBER VARCHAR2(50),
857 INCIDENT_PO_BOX_NUMBER VARCHAR2(50),
858 OLD_INCIDENT_HOUSE_NUMBER VARCHAR2(50),
859 INCIDENT_HOUSE_NUMBER VARCHAR2(50),
860 OLD_INCIDENT_STREET_SUFFIX VARCHAR2(50),
861 INCIDENT_STREET_SUFFIX VARCHAR2(50),
862 OLD_INCIDENT_STREET VARCHAR2(150),
863 INCIDENT_STREET VARCHAR2(150),
864 OLD_INCIDENT_STREET_NUMBER VARCHAR2(50),
865 INCIDENT_STREET_NUMBER VARCHAR2(50),
866 OLD_INCIDENT_FLOOR VARCHAR2(50),
867 INCIDENT_FLOOR VARCHAR2(50),
868 OLD_INCIDENT_SUITE VARCHAR2(50),
869 INCIDENT_SUITE VARCHAR2(50),
870 OLD_INCIDENT_POSTAL_PLUS4_CODE VARCHAR2(30),
871 INCIDENT_POSTAL_PLUS4_CODE VARCHAR2(30),
872 OLD_INCIDENT_POSITION VARCHAR2(50),
873 INCIDENT_POSITION VARCHAR2(50),
874 OLD_INCIDENT_LOC_DIRECTIONS VARCHAR2(640),
875 INCIDENT_LOC_DIRECTIONS VARCHAR2(640),
876 OLD_INCIDENT_LOC_DESCRIPTION VARCHAR2(2000),
877 INCIDENT_LOC_DESCRIPTION VARCHAR2(2000),
878 OLD_INSTALL_SITE_ID NUMBER,
879 INSTALL_SITE_ID NUMBER,
880 INCIDENT_LAST_MODIFIED_DATE DATE,
881 UPDATED_ENTITY_CODE VARCHAR2(30),
882 UPDATED_ENTITY_ID NUMBER(15),
883 ENTITY_ACTIVITY_CODE VARCHAR2(30),
884 OLD_TIER_VERSION VARCHAR2(250),
885 TIER_VERSION VARCHAR2(250),
886 -- Added new audit columns --anmukher --09/11/03
887 OLD_INC_OBJECT_VERSION_NUMBER NUMBER(9),
888 INC_OBJECT_VERSION_NUMBER NUMBER(9),
889 OLD_INC_REQUEST_ID NUMBER(15),
890 INC_REQUEST_ID NUMBER(15),
891 OLD_INC_PROGRAM_APPLICATION_ID NUMBER(15),
892 INC_PROGRAM_APPLICATION_ID NUMBER(15),
893 OLD_INC_PROGRAM_ID NUMBER(15),
894 INC_PROGRAM_ID NUMBER(15),
895 OLD_INC_PROGRAM_UPDATE_DATE DATE,
896 INC_PROGRAM_UPDATE_DATE DATE,
897 OLD_OWNING_DEPARTMENT_ID NUMBER,
898 OWNING_DEPARTMENT_ID NUMBER,
899 OLD_INCIDENT_LOCATION_TYPE VARCHAR2(30),
900 INCIDENT_LOCATION_TYPE VARCHAR2(30),
901 OLD_UNASSIGNED_INDICATOR VARCHAR2(1),
902 UNASSIGNED_INDICATOR VARCHAR2(1),
903 -- audit component R12 project
904 OLD_MAINT_ORGANIZATION_ID NUMBER(15),
905 MAINT_ORGANIZATION_ID NUMBER(15)
906 );
907
908 --
909 -- RMJ:
910 -- A table within a record type is not possible. That's why I defined
911 -- three context fields in the following record type.
912 --
913 TYPE notes_rec IS RECORD (
914 NOTE_ID NUMBER := FND_API.G_MISS_NUM,
915 NOTE VARCHAR2(2000) := FND_API.G_MISS_CHAR,
916 NOTE_DETAIL VARCHAR2(32767) := FND_API.G_MISS_CHAR,
917 NOTE_TYPE VARCHAR2(240) := FND_API.G_MISS_CHAR,
918 NOTE_STATUS VARCHAR2(240) := FND_API.G_MISS_CHAR,
919 ENTERED_BY NUMBER := FND_API.G_MISS_NUM,
920 ENTERED_DATE DATE := FND_API.G_MISS_DATE,
921 SOURCE_OBJECT_ID NUMBER := FND_API.G_MISS_NUM,
922 SOURCE_OBJECT_CODE VARCHAR2(30) := FND_API.G_MISS_CHAR,
926 NOTE_CONTEXT_ID_02 NUMBER := FND_API.G_MISS_NUM,
923 NOTE_CONTEXT_ID_01 NUMBER := FND_API.G_MISS_NUM,
924 NOTE_CONTEXT_TYPE_01 VARCHAR2(30) := FND_API.G_MISS_CHAR,
925 NOTE_CONTEXT_TYPE_ID_01 NUMBER := FND_API.G_MISS_NUM,
927 NOTE_CONTEXT_TYPE_02 VARCHAR2(30) := FND_API.G_MISS_CHAR,
928 NOTE_CONTEXT_TYPE_ID_02 NUMBER := FND_API.G_MISS_NUM,
929 NOTE_CONTEXT_ID_03 NUMBER := FND_API.G_MISS_NUM,
930 NOTE_CONTEXT_TYPE_03 VARCHAR2(30) := FND_API.G_MISS_CHAR,
931 NOTE_CONTEXT_TYPE_ID_03 NUMBER := FND_API.G_MISS_NUM,
932 ATTRIBUTE_1 VARCHAR2(150) := FND_API.G_MISS_CHAR,
933 ATTRIBUTE_2 VARCHAR2(150) := FND_API.G_MISS_CHAR,
934 ATTRIBUTE_3 VARCHAR2(150) := FND_API.G_MISS_CHAR,
935 ATTRIBUTE_4 VARCHAR2(150) := FND_API.G_MISS_CHAR,
936 ATTRIBUTE_5 VARCHAR2(150) := FND_API.G_MISS_CHAR,
937 ATTRIBUTE_6 VARCHAR2(150) := FND_API.G_MISS_CHAR,
938 ATTRIBUTE_7 VARCHAR2(150) := FND_API.G_MISS_CHAR,
939 ATTRIBUTE_8 VARCHAR2(150) := FND_API.G_MISS_CHAR,
940 ATTRIBUTE_9 VARCHAR2(150) := FND_API.G_MISS_CHAR,
941 ATTRIBUTE_10 VARCHAR2(150) := FND_API.G_MISS_CHAR,
942 ATTRIBUTE_11 VARCHAR2(150) := FND_API.G_MISS_CHAR,
943 ATTRIBUTE_12 VARCHAR2(150) := FND_API.G_MISS_CHAR,
944 ATTRIBUTE_13 VARCHAR2(150) := FND_API.G_MISS_CHAR,
945 ATTRIBUTE_14 VARCHAR2(150) := FND_API.G_MISS_CHAR,
946 ATTRIBUTE_15 VARCHAR2(150) := FND_API.G_MISS_CHAR,
947 CONTEXT VARCHAR2(30) := FND_API.G_MISS_CHAR,
948 LAST_UPDATE_DATE DATE := FND_API.G_MISS_DATE,
949 CREATION_DATE DATE := FND_API.G_MISS_DATE,
950 CREATED_BY NUMBER := FND_API.G_MISS_NUM,
951 LAST_UPDATED_BY NUMBER := FND_API.G_MISS_NUM,
952 LAST_UPDATE_LOGIN NUMBER := FND_API.G_MISS_NUM
953 );
954 TYPE notes_table IS TABLE OF notes_rec INDEX BY BINARY_INTEGER;
955
956 -----------------------------------------------------------------------
957 -- Start of comments
958 -- Record Type : notes_rec
959 -- Description : Holds the Notes attributes for the
960 -- Creating records in JTF_NOTES_B
961 -- Fields :
962 -- NOTE OPTIONAL
963 -- NOTE_DETAIL OPTIONAL
964 -- NOTE_TYPE OPTIONAL
965 -- VAlid values are SR_PROBLEM and SR_RESOLUTION
966 -- NOTE_CONTEXT_TYPE_01 OPTIONAL - VAlid value is 'SR'
967 -- NOTE_CONTEXT_TYPE_ID_01 OPTIONAL
968 -- VAlid incident_id from cs_incidents_all_b
969 -- NOTE_CONTEXT_TYPE_02 - VAlid value is 'SR'
970 -- NOTE_CONTEXT_TYPE_ID_02 - VAlid incident_id from cs_incidents_all_b
971 -- NOTE_CONTEXT_TYPE_03 - VAlid value is 'SR'
972 -- NOTE_CONTEXT_TYPE_ID_03 - VAlid incident_id from cs_incidents_all_b
973 --------------------------------------------------------------------------
974
975 --
976 --This table will hold the contacts and contact information
977 --for a Service Request customer
978 --
979 TYPE contacts_rec IS RECORD (
980 SR_CONTACT_POINT_ID NUMBER := FND_API.G_MISS_NUM,
981 PARTY_ID NUMBER := FND_API.G_MISS_NUM,
982 CONTACT_POINT_ID NUMBER := FND_API.G_MISS_NUM,
983 PRIMARY_FLAG VARCHAR2(1) := FND_API.G_MISS_CHAR,
984 CONTACT_POINT_TYPE VARCHAR2(30) := FND_API.G_MISS_CHAR,
985 CONTACT_TYPE VARCHAR2(30) := FND_API.G_MISS_CHAR,
986 party_role_code VARCHAR2(30) := FND_API.G_MISS_CHAR,
987 start_date_active DATE := FND_API.G_MISS_DATE,
988 end_date_active DATE := FND_API.G_MISS_DATE
989 );
990 TYPE contacts_table IS TABLE OF contacts_rec INDEX BY BINARY_INTEGER;
991
992 --
993 -----------------------------------------------------------------------
994 -- Start of comments
995 -- Record Type : contacts_rec
996 -- Description : Holds the Contacts attributes for the
997 -- Creating records in CS_HZ_SR_CONTACT_POINTS
998 -- Fields :
999 -- sr_contact_point_id OPTIONAL
1000 -- can be specified only if you waNt to update an existing
1001 -- record(when called from Update_ServiceRequest API)
1002 -- party_id REQUIRED
1003 -- Valid party id from HZ_PARTIES
1004 -- contact_point_id OPTIONAL
1005 -- VAlid contact_point_id from HZ_CONTACT_POINTS
1006 -- contact_point_type OPTIONAL
1007 -- From AR_LOOKUPS where lookup_type = 'COMMUNICATION_TYPE'
1008 -- and 'PHONE_LINE_TYPE'
1009 -- primary_flag REQUIRED
1010 -- At least one record in the table should have this flag set to Y
1011 -- contact_type REQUIRED
1012 -- Valid values are 'PERSON" and 'EMPLOYEE'
1013
1014 -------------------------------------------------------- pkesani
1015 -- Start of comments
1016 -- Record Type : coverage_type_rec
1017 -- Description : To Get the coverage type.
1018 --------------------------------------------------------
1019 coverage_type_rec OKS_Entitlements_Pub.CovType_Rec_Type;
1020
1024 status_id NUMBER,
1021 TYPE service_request_rec_type IS RECORD (
1022 request_date DATE,
1023 type_id NUMBER,
1025 severity_id NUMBER,
1026 urgency_id NUMBER,
1027 closed_date DATE,
1028 owner_id NUMBER,
1029 owner_group_id NUMBER,
1030 publish_flag VARCHAR2(1),
1031 summary VARCHAR2(240),
1032 caller_type VARCHAR2(30),
1033 customer_id NUMBER,
1034 customer_number VARCHAR2(30),
1035 employee_id NUMBER,
1036 verify_cp_flag VARCHAR2(1),
1037 customer_product_id NUMBER,
1038 platform_id NUMBER,
1039 platform_version VARCHAR2(250),
1040 db_version VARCHAR2(250),
1041 platform_version_id NUMBER,
1042 cp_component_id NUMBER,
1043 cp_component_version_id NUMBER,
1044 cp_subcomponent_id NUMBER,
1045 cp_subcomponent_version_id NUMBER,
1046 language_id NUMBER,
1047 language VARCHAR2(4),
1048 inventory_item_id NUMBER,
1049 inventory_org_id NUMBER,
1050 current_serial_number VARCHAR2(30),
1051 original_order_number NUMBER,
1052 purchase_order_num VARCHAR2(50),
1053 problem_code VARCHAR2(50),
1054 exp_resolution_date DATE,
1055 install_site_use_id NUMBER,
1056 request_attribute_1 VARCHAR2(150),
1057 request_attribute_2 VARCHAR2(150),
1058 request_attribute_3 VARCHAR2(150),
1059 request_attribute_4 VARCHAR2(150),
1060 request_attribute_5 VARCHAR2(150),
1061 request_attribute_6 VARCHAR2(150),
1062 request_attribute_7 VARCHAR2(150),
1063 request_attribute_8 VARCHAR2(150),
1064 request_attribute_9 VARCHAR2(150),
1065 request_attribute_10 VARCHAR2(150),
1066 request_attribute_11 VARCHAR2(150),
1067 request_attribute_12 VARCHAR2(150),
1068 request_attribute_13 VARCHAR2(150),
1069 request_attribute_14 VARCHAR2(150),
1070 request_attribute_15 VARCHAR2(150),
1071 request_context VARCHAR2(30),
1072 external_attribute_1 VARCHAR2(150),
1073 external_attribute_2 VARCHAR2(150),
1074 external_attribute_3 VARCHAR2(150),
1075 external_attribute_4 VARCHAR2(150),
1076 external_attribute_5 VARCHAR2(150),
1077 external_attribute_6 VARCHAR2(150),
1078 external_attribute_7 VARCHAR2(150),
1079 external_attribute_8 VARCHAR2(150),
1080 external_attribute_9 VARCHAR2(150),
1081 external_attribute_10 VARCHAR2(150),
1082 external_attribute_11 VARCHAR2(150),
1083 external_attribute_12 VARCHAR2(150),
1084 external_attribute_13 VARCHAR2(150),
1085 external_attribute_14 VARCHAR2(150),
1086 external_attribute_15 VARCHAR2(150),
1087 external_context VARCHAR2(30),
1088 bill_to_site_use_id NUMBER,
1089 bill_to_contact_id NUMBER,
1090 ship_to_site_use_id NUMBER,
1091 ship_to_contact_id NUMBER,
1092 resolution_code VARCHAR2(50),
1093 act_resolution_date DATE,
1094 public_comment_flag VARCHAR2(1),
1095 parent_interaction_id NUMBER,
1096 contract_service_id NUMBER,
1097 -- 04/16/01
1098 contract_id NUMBER,
1099 project_number VARCHAR2(120),
1100 -- 04/16/01
1101 qa_collection_plan_id NUMBER,
1102 account_id NUMBER,
1103 resource_type VARCHAR2(30),
1104 resource_subtype_id NUMBER,
1105 cust_po_number VARCHAR2(50),
1106 cust_ticket_number VARCHAR2(50),
1107 sr_creation_channel VARCHAR2(50),
1108 obligation_date DATE,
1109 time_zone_id NUMBER,
1110 time_difference NUMBER,
1111 site_id NUMBER,
1112 customer_site_id NUMBER,
1113 territory_id NUMBER,
1114 initialize_flag VARCHAR2(1),
1115 cp_revision_id NUMBER,
1116 inv_item_revision VARCHAR2(240),
1117 inv_component_id NUMBER,
1118 inv_component_version VARCHAR2(90),
1119 inv_subcomponent_id NUMBER,
1120 inv_subcomponent_version VARCHAR2(90),
1121 -- jngeorge---11.5.6----07/12/01
1122 tier VARCHAR2(250),
1123 tier_version VARCHAR2(250),
1124 operating_system VARCHAR2(250),
1125 operating_system_version VARCHAR2(250),
1126 database VARCHAR2(250),
1127 cust_pref_lang_id NUMBER,
1128 category_id NUMBER,
1129 group_type VARCHAR2(30),
1130 group_territory_id NUMBER,
1131 inv_platform_org_id NUMBER,
1132 product_revision VARCHAR2(240),
1133 component_version VARCHAR2(3),
1134 subcomponent_version VARCHAR2(3),
1135 comm_pref_code VARCHAR2(30),
1136 -- Added for HA
1137 last_update_date DATE,
1138 last_updated_by NUMBER,
1142 owner_assigned_time DATE,
1139 creation_date DATE,
1140 created_by NUMBER,
1141 last_update_login NUMBER,
1143 owner_assigned_flag VARCHAR2(1),
1144 -- Added for UWQ
1145 -- Changed the width from 10 to 30 shijain 3rd dec 2002
1146 last_update_channel VARCHAR2(30),
1147 cust_pref_lang_code VARCHAR2(4),
1148 --- Added for Automatic Assignments
1149 load_balance VARCHAR2(1),
1150 assign_owner VARCHAR2(1),
1151 category_set_id NUMBER,
1152 external_reference VARCHAR2(30),
1153 system_id NUMBER,
1154 -- jngeorge-----07/12/01
1155 error_code VARCHAR2(250),
1156 incident_occurred_date DATE,
1157 incident_resolved_date DATE,
1158 inc_responded_by_date DATE,
1159 resolution_summary VARCHAR2(250),
1160 incident_location_id NUMBER,
1161 incident_address VARCHAR2(960),
1162 incident_city VARCHAR2(60),
1163 incident_state VARCHAR2(60),
1164 incident_country VARCHAR2(60),
1165 incident_province VARCHAR2(60),
1166 incident_postal_code VARCHAR2(60),
1167 incident_county VARCHAR2(60),
1168 -- Added for Enh# 2216664
1169 owner VARCHAR2(360),
1170 group_owner VARCHAR2(60),
1171 -- Added for Credit Card ER# 2255263 (UI ER#2208078)
1172 cc_number VARCHAR2(48),
1173 cc_expiration_date DATE,
1174 cc_type_code VARCHAR(30),
1175 cc_first_name VARCHAR(250),
1176 cc_last_name VARCHAR(250),
1177 cc_middle_name VARCHAR(250),
1178 cc_id NUMBER ,
1179 ---- Added for ER# 2320056
1180 coverage_type VARCHAR2(30),
1181 --- Added for ER# 2433831
1182 bill_to_account_id NUMBER ,
1183 ship_to_account_id NUMBER ,
1184 --- Added for ER# 2463321
1185 customer_phone_id NUMBER ,
1186 customer_email_id NUMBER ,
1187 --- Added these parameters for 11.5.9 source changes
1188 creation_program_code VARCHAR2(30),
1189 last_update_program_code VARCHAR2(30),
1190 -- Bill_to_party, ship_to_party
1191 bill_to_party_id NUMBER,
1192 ship_to_party_id NUMBER,
1193 -- Conc request related fields
1194 program_id NUMBER,
1195 program_application_id NUMBER,
1196 conc_request_id NUMBER,
1197 program_login_id NUMBER,
1198 -- Bill_to_site, ship_to_site
1199 bill_to_site_id NUMBER,
1200 ship_to_site_id NUMBER,
1201 -- Added address related columns by shijain 4th dec 2002
1202 incident_point_of_interest Varchar2(240) ,
1203 incident_cross_street Varchar2(240) ,
1204 incident_direction_qualifier Varchar2(30),
1205 incident_distance_qualifier Varchar2(240) ,
1206 incident_distance_qual_uom Varchar2(30),
1207 incident_address2 Varchar2(240) ,
1208 incident_address3 Varchar2(240),
1209 incident_address4 Varchar2(240) ,
1210 incident_address_style Varchar2(30),
1211 incident_addr_lines_phonetic Varchar2(560) ,
1212 incident_po_box_number Varchar2(50) ,
1213 incident_house_number Varchar2(50),
1214 incident_street_suffix Varchar2(50) ,
1215 incident_street Varchar2(150),
1216 incident_street_number Varchar2(50) ,
1217 incident_floor Varchar2(50) ,
1218 incident_suite Varchar2(50) ,
1219 incident_postal_plus4_code Varchar2(30) ,
1220 incident_position Varchar2(50) ,
1221 incident_location_directions Varchar2(640),
1222 incident_location_description Varchar2(2000) ,
1223 install_site_id Number ,
1224 status_flag Varchar2(3) ,
1225 primary_contact_id Number,
1226 ------anmukher---------------07/31/03
1227 -- Added for CMRO-EAM project of Release 11.5.10
1228 old_type_maintenance_flag VARCHAR2(3),
1229 new_type_maintenance_flag VARCHAR2(3),
1230 old_type_CMRO_flag VARCHAR2(3),
1231 new_type_CMRO_flag VARCHAR2(3),
1232 item_serial_number VARCHAR2(30),
1233 owning_dept_id NUMBER,
1234 -- Added for Misc ERs project of Release 11.5.10
1235 incident_location_type VARCHAR2(30) Default 'HZ_LOCATION',
1236 org_id NUMBER,
1237 maint_organization_id NUMBER
1238 );
1239
1240
1241 ----------anmukher--------------07/31/03
1242 -- Added new record type for OUT parameters of Create API
1243 -- so that future overloading of the API can be avoided
1244 TYPE sr_create_out_rec_type IS RECORD
1245 (
1246 request_id NUMBER,
1247 request_number VARCHAR2(64),
1248 interaction_id NUMBER,
1249 workflow_process_id NUMBER,
1250 individual_owner NUMBER,
1251 group_owner NUMBER,
1252 individual_type VARCHAR2(30),
1253 auto_task_gen_status VARCHAR2(3),
1254 auto_task_gen_attempted BOOLEAN Default FALSE,
1255 field_service_task_created BOOLEAN,
1256 contract_service_id NUMBER,
1257 resolve_by_date DATE,
1258 respond_by_date DATE,
1262
1259 resolved_on_date DATE,
1260 responded_on_date DATE
1261 );
1263 -- Added new record type for OUT parameters of Update API
1264 -- so that future overloading of the API can be avoided
1265 TYPE sr_update_out_rec_type IS RECORD
1266 ( interaction_id NUMBER,
1267 workflow_process_id NUMBER,
1268 individual_owner NUMBER,
1269 group_owner NUMBER,
1270 individual_type VARCHAR2(30),
1271 resolved_on_date DATE,
1272 responded_on_date DATE
1273 , status_id NUMBER
1274 , close_date DATE
1275 );
1276
1277 --This Global service request record type is declared
1278 --for internal hooks.
1279 TYPE internal_user_hooks_rec IS RECORD (
1280 request_id NUMBER,
1281 request_number VARCHAR2(64),
1282 request_date DATE,
1283 type_id NUMBER,
1284 status_id NUMBER,
1285 severity_id NUMBER,
1286 urgency_id NUMBER,
1287 closed_date DATE,
1288 owner_id NUMBER,
1289 owner_group_id NUMBER,
1290 publish_flag VARCHAR2(1),
1291 summary VARCHAR2(240),
1292 caller_type VARCHAR2(30),
1293 customer_id NUMBER,
1294 customer_number VARCHAR2(30),
1295 employee_id NUMBER,
1296 verify_cp_flag VARCHAR2(1),
1297 customer_product_id NUMBER,
1298 platform_id NUMBER,
1299 platform_version VARCHAR2(250),
1300 db_version VARCHAR2(250),
1301 platform_version_id NUMBER,
1302 cp_component_id NUMBER,
1303 cp_component_version_id NUMBER,
1304 cp_subcomponent_id NUMBER,
1305 cp_subcomponent_version_id NUMBER,
1306 language_id NUMBER,
1307 language VARCHAR2(4),
1308 inventory_item_id NUMBER,
1309 inventory_org_id NUMBER,
1310 current_serial_number VARCHAR2(30),
1311 original_order_number NUMBER,
1312 purchase_order_num VARCHAR2(50),
1313 problem_code VARCHAR2(50),
1314 exp_resolution_date DATE,
1315 install_site_use_id NUMBER,
1316 request_attribute_1 VARCHAR2(150),
1317 request_attribute_2 VARCHAR2(150),
1318 request_attribute_3 VARCHAR2(150),
1319 request_attribute_4 VARCHAR2(150),
1320 request_attribute_5 VARCHAR2(150),
1321 request_attribute_6 VARCHAR2(150),
1322 request_attribute_7 VARCHAR2(150),
1323 request_attribute_8 VARCHAR2(150),
1324 request_attribute_9 VARCHAR2(150),
1325 request_attribute_10 VARCHAR2(150),
1326 request_attribute_11 VARCHAR2(150),
1327 request_attribute_12 VARCHAR2(150),
1328 request_attribute_13 VARCHAR2(150),
1329 request_attribute_14 VARCHAR2(150),
1330 request_attribute_15 VARCHAR2(150),
1331 request_context VARCHAR2(30),
1332 external_attribute_1 VARCHAR2(150),
1333 external_attribute_2 VARCHAR2(150),
1334 external_attribute_3 VARCHAR2(150),
1335 external_attribute_4 VARCHAR2(150),
1336 external_attribute_5 VARCHAR2(150),
1337 external_attribute_6 VARCHAR2(150),
1338 external_attribute_7 VARCHAR2(150),
1339 external_attribute_8 VARCHAR2(150),
1340 external_attribute_9 VARCHAR2(150),
1341 external_attribute_10 VARCHAR2(150),
1342 external_attribute_11 VARCHAR2(150),
1343 external_attribute_12 VARCHAR2(150),
1344 external_attribute_13 VARCHAR2(150),
1345 external_attribute_14 VARCHAR2(150),
1346 external_attribute_15 VARCHAR2(150),
1347 external_context VARCHAR2(30),
1348 bill_to_site_use_id NUMBER,
1349 bill_to_contact_id NUMBER,
1350 ship_to_site_use_id NUMBER,
1351 ship_to_contact_id NUMBER,
1352 resolution_code VARCHAR2(50),
1353 act_resolution_date DATE,
1354 public_comment_flag VARCHAR2(1),
1355 parent_interaction_id NUMBER,
1356 contract_service_id NUMBER,
1357 --04/16/01
1358 contract_id NUMBER,
1359 project_number VARCHAR2(120),
1360 --04/16/01
1361 qa_collection_plan_id NUMBER,
1362 account_id NUMBER,
1363 resource_type VARCHAR2(30),
1364 resource_subtype_id NUMBER,
1365 cust_po_number VARCHAR2(50),
1366 cust_ticket_number VARCHAR2(50),
1367 sr_creation_channel VARCHAR2(50),
1368 obligation_date DATE,
1369 time_zone_id NUMBER,
1370 time_difference NUMBER,
1371 site_id NUMBER,
1372 customer_site_id NUMBER,
1373 territory_id NUMBER,
1374 initialize_flag VARCHAR2(1),
1375 cp_revision_id NUMBER,
1376 inv_item_revision VARCHAR2(240),
1377 inv_component_id NUMBER,
1378 inv_component_version VARCHAR2(90),
1379 inv_subcomponent_id NUMBER,
1380 inv_subcomponent_version VARCHAR2(90),
1381 ------jngeorge---11.5.6----07/12/01
1382 tier VARCHAR2(250),
1383 tier_version VARCHAR2(250),
1387 cust_pref_lang_id NUMBER,
1384 operating_system VARCHAR2(250),
1385 operating_system_version VARCHAR2(250),
1386 database VARCHAR2(250),
1388 category_id NUMBER,
1389 group_type VARCHAR2(30),
1390 group_territory_id NUMBER,
1391 inv_platform_org_id NUMBER,
1392 product_revision VARCHAR2(240),
1393 component_version VARCHAR2(3),
1394 subcomponent_version VARCHAR2(3),
1395 comm_pref_code VARCHAR2(30),
1396 cust_pref_lang_code VARCHAR2(4),
1397 error_code VARCHAR2(250),
1398 incident_occurred_date DATE,
1399 incident_resolved_date DATE,
1400 inc_responded_by_date DATE,
1401 resolution_summary VARCHAR2(250),
1402 incident_location_id NUMBER,
1403 incident_address VARCHAR2(960),
1404 incident_city VARCHAR2(60),
1405 incident_state VARCHAR2(60),
1406 incident_country VARCHAR2(60),
1407 incident_province VARCHAR2(60),
1408 incident_postal_code VARCHAR2(60),
1409 incident_county VARCHAR2(60),
1410 -- Added for Enh# 2216664
1411 owner VARCHAR2(360),
1412 group_owner VARCHAR2(60),
1413 -- Added for ER# 2320056
1414 coverage_type VARCHAR2(30),
1415 -- Added for ER# 2433831
1416 bill_to_account_id NUMBER ,
1417 ship_to_account_id NUMBER ,
1418 -- Added for ER# 2463321
1419 customer_phone_id NUMBER ,
1420 customer_email_id NUMBER ,
1421 -- for cmro_eam
1422 status_flag VARCHAR2(3),
1423 old_type_cmro_flag VARCHAR2(3),
1424 new_type_cmro_flag VARCHAR2(3)
1425 );
1426
1427 --This declaration is for the internal user hooks
1428 user_hooks_rec CS_ServiceRequest_PVT.internal_user_hooks_rec ;
1429
1430 -- This cursor is defined so that we can define a subtype
1431 -- and use it pass the old_rec values to wrokitem and API validations
1432 -- calls.This was mainly done for the Misc ER:owner auto assg changes.
1433
1434 cursor l_ServiceRequest_csr(c_incident_id number) is
1435 select *
1436 from CS_INCIDENTS_ALL_VL
1437 where incident_id = c_incident_id;
1438 -- FOR UPDATE OF incident_id NOWAIT;
1439 -- This declaration is to store the old values of SR when SR is updated.
1440
1441 SUBTYPE SR_OLDVALUES_REC_TYPE IS L_SERVICEREQUEST_CSR%ROWTYPE;
1442
1443 PROCEDURE initialize_rec(
1444 p_sr_record IN OUT NOCOPY service_request_rec_type );
1445 --------------------------------------------------------------------------
1446 -- Start of comments
1447 -- API name : Create_ServiceRequest
1448 -- Type : Private
1449 -- Function : Creates a service request in the table CS_INCIDENTS.
1450 -- Pre-reqs : None.
1451 --
1452 -- Standard IN Parameters:
1453 -- p_api_version IN NUMBER Required
1454 -- p_init_msg_list IN VARCHAR2 Optional
1455 -- Default = FND_API.G_FALSE
1456 -- p_commit IN VARCHAR2 Optional
1457 -- Default = FND_API.G_FALSE
1458 -- p_validation_level IN NUMBER Optional
1459 -- Default = FND_API.G_VALID_LEVEL_FULL
1460 --
1461 -- Standard OUT Parameters:
1462 -- x_return_status OUT VARCHAR2(1)
1463 -- x_msg_count OUT NUMBER
1464 -- x_msg_data OUT VARCHAR2(2000)
1465 --
1466 -- Service Request IN Parameters:
1467 -- p_resp_appl_id IN NUMBER Optional
1468 -- p_resp_id IN NUMBER Optional
1469 -- p_user_id IN NUMBER Required
1470 -- Application user identifier
1471 -- Valid user from fnd_user
1472
1473 -- p_login_id IN NUMBER Optional
1474 -- Identifier of login session
1475 -- p_org_id IN NUMBER Optional
1476 -- Operating unit identifier
1477 -- p_request_id IN NUMBER Optional
1478 -- Service Request Identifier
1479 -- p_request_number IN VARCHAR2(64) Optional
1480 -- p_service_request_rec IN service_request_rec_type Required
1481 -- p_notes IN notes_table Optional
1482 --
1483 -- p_contacts IN contacts_table Required if CALLER TYPE IS
1484 -- ORGANIZATION OR PERSON
1485 -- Service Request OUT parameters:
1486 -- x_request_id OUT NUMBER
1487 -- System generated ID
1488 -- x_request_number OUT VARCHAR2(64)
1489 -- User-visible number of service request
1490 -- x_interaction_id OUT NUMBER
1491 -- Workflow OUT parameters:
1492 -- x_workflow_process_id OUT NUMBER
1493 --
1494 -- Calls IN parameters:
1495 -- p_comments IN VARCHAR2(2000) Optional
1496 -- p_public_comment_flag IN VARCHAR2(1) Optional
1497 --
1498 -- Calls OUT parameters:
1499 -- p_call_id OUT NUMBER
1500 --
1501 -- Interaction IN parameters:
1502 -- p_parent_interaction_id IN NUMBER Optional
1503 -- Corresponds to the column INTERACTION_ID in the table
1504 -- CS_INTERACTIONS, and identifies the parent interaction that
1505 -- resulted in this service request
1506 -- Version : Current version 1.1
1507 -- Added IN parameter p_parent_interaction_id.
1508 -- Previous version 1.0
1509 -- Initial version 1.0
1510 -- End of comments
1511
1512 --------------------------------------------------------------------------
1513 -- Start of comments
1517 -- Fields :
1514 -- Record Type : Service_Request_Rec_Type
1515 -- Description : Holds the Service Request attributes
1516 -- for the Create_ServiceRequest Procedure.
1518 --
1519 -- Service request type identifier
1520 -- request_date DATE Required
1521 -- Service request date
1522
1523 -- type_id NUMBER Required
1524 -- Service request type identifier
1525 -- VAlid incident_type_id from cs_incident_types
1526
1527 -- status_id NUMBER Required
1528 -- Service request status identifier
1529 -- Valid incident_status_id from cs_incident_statuses
1530
1531
1532 -- severity_id NUMBER Required
1533 -- Service request severity identifier
1534 -- Valid incident_severity_id from cs_incident_severities
1535
1536
1537 -- urgency_id NUMBER Optional
1538 -- Service request urgency identifier
1539 -- Valid incident_urgency_id from cs_incident_urgencies
1540
1541
1542 -- closed_date DATE Optional
1543 -- Service request closed date
1544 -- Ignored if the status is not a "closed" status
1545
1546 -- owner_id NUMBER Required
1547 -- Service request owner identifier
1548 -- Valid resource_id from cs_sr_owners_v
1549
1550 -- owner_group_id NUMBER Optional
1551 -- Service request owner group identifier
1552 --
1553 -- resource_type VARCHAR2(30) Optional
1554 -- VAlid resource_type from cs_sr_owners_v
1555
1556 -- resource_subtype_id NUMBER Optional
1557
1558 -- publish_flag VARCHAR2(1) Optional
1559
1560 -- summary VARCHAR2(240) Required
1561 -- Service request summary
1562
1563 ---------------------------------------------------------------------------
1564 -- These fields are no longer there in the service_request_rec_type
1565
1566 -- verify_request_flag VARCHAR2(1) Required
1567 -- Corresponds to the column RECORD_IS_VALID_FLAG in the table
1568 -- CS_INCIDENTS. Allows API callers to request that the API does
1569 -- the validation of the optional fields (customer_id,
1570 -- contact_id, bill_to_site_use_id, bill_to_contact_id,
1571 -- ship_to_site_use_id, ship_to_contact_id).
1572
1573 -- filed_by_emp_flag VARCHAR2(1) Required
1574 --------------------------------------------------------------------------
1575
1576 -- caller_type VARCHAR2(30) Required
1577 -- Caller Type
1578 -- VAlid values are : ORGANIZATION, PERSON and CALLER_EMP
1579
1580
1581 -- customer_id NUMBER Optional
1582 -- Service request customer identifier
1583
1584
1585 -- customer_number VARCHAR2(30) Optional
1586 -- Service request customer number
1587
1588
1589 ------------------------------------------------------------------------
1590
1591 -- These fields are no longer there in the service_request_rec_type
1592
1593 -- customer_prefix VARCHAR2(50) Optional
1594 -- Service request customer prefix
1595
1596 -- customer_firstname VARCHAR2(150) Optional
1597 -- Service request customer first name
1598
1599 -- customer_lastname VARCHAR2(150) Optional
1600 -- Service request customer last name
1601
1602
1603 -- customer_company_name VARCHAR2(255) Optional
1604 -- Service request customer company name
1605 -------------------------------------------------------------------------------
1606
1607
1608
1609
1610 -- employee_id NUMBER Optional
1611
1612 ------------------------------------------------------------------------------
1613 ---These fileds are no longer there in the record type
1614
1615 -- contact01_id NUMBER Optional
1616 -- Service request customer contact identifier
1617
1618 -- contact01_prefix VARCHAR2(50) Optional
1619 -- Service request customer contact prefix
1620
1621 -- contact01_firstname VARCHAR2(150) Optional
1622 -- Service request customer contact firstname
1623
1624
1625
1626 -- contact01_lastname VARCHAR2(150) Optional
1627 -- Service request customer contact lastname
1628
1629 -- contact01_area_code VARCHAR2(10) Optional
1630 -- contact01_telephone VARCHAR2(40) Optional
1631 -- contact01_extension VARCHAR2(20) Optional
1632 -- contact01_fax_area_code VARCHAR2(10) Optional
1633 -- contact01_fax_number VARCHAR2(40) Optional
1634 -- contact01_email_address VARCHAR2(2000) Optional
1635
1636 ------------------------------------------------------------------------
1637 -- This field is no longer present in the record type
1638 -- contact_time_diff NUMBER Optional
1639 -------------------------------------------------------------------------
1640 ---These fileds are no longer there in the record type
1641
1642 -- contact02_id NUMBER Optional
1643 -- Service request customer represented by identifier
1644
1645
1646 -- contact02_prefix VARCHAR2(50) Optional
1647 -- Service request customer represented by prefix
1648
1649
1650 -- contact02_firstname VARCHAR2(150) Optional
1651 -- Service request customer represented by firstname
1652
1653 -- contact02_lastname VARCHAR2(150) Optional
1654 -- Service request customer represented by lastname
1655
1656 -- contact02_area_code VARCHAR2(10) Optional
1657 -- contact02_telephone VARCHAR2(40) Optional
1658 -- contact02_extension VARCHAR2(20) Optional
1662 --------------------------------------------------------------------------------------------------
1659 -- contact02_fax_area_code VARCHAR2(10) Optional
1660 -- contact02_fax_number VARCHAR2(40) Optional
1661 -- contact02_email_address VARCHAR2(2000) Optional
1663
1664 -- verify_cp_flag VARCHAR2(1) Required
1665 -- The verify_cp_flag parameter allows API callers to request
1666 -- that the API does the validation of the optional customer
1667 -- product ID.
1668
1669 -- customer_product_id NUMBER Optional
1670 -- Unique identifier for a customer product in the Installed Base.
1671 -- Required if the verify_cp_flag parameter is 'Y'.
1672 -- Ignored if the verify_cp_flag parameter is 'N'.
1673
1674 -----------------------------------------------------------
1675 -- No longer there in rec type
1676 -- lot_num VARCHAR2(30) Optional
1677 ------------------------------------------------------------
1678
1679
1680 -- Supporting platform_id again because of enh 1711552
1681 -- platform_id NUMBER Optional
1682 -- *********THE functionality for the below 2 fileds is no longer supported.
1683 -- platform_version_id NUMBER Optional
1684
1685 -- language_id NUMBER Optional
1686 -- This is the Product's language id
1687
1688 -- cp_component_id NUMBER Optional
1689 -- cp_component_version_id NUMBER Optional
1690 -- cp_subcomponent_id NUMBER Optional
1691 -- cp_subcomponent_version_id NUMBER Optional
1692
1693 -- language VARCHAR2(4) Optional
1694 -- This is used for TL tables
1695
1696 -- inventory_item_id NUMBER Optional
1697 -- Corresponds to the column INVENTORY_ITEM_ID in the table
1698 -- MTL_SYSTEM_ITEMS, and identifies the service request product.
1699 -- Ignored if the verify_cp_flag parameter is 'Y'.
1700
1701
1702 -- inventory_org_id NUMBER Optional
1703 -- Item organization ID. Part of the unique key that uniquely
1704 -- identifies an inventory item.
1705 -- Required if inventory_item_id is used.
1706
1707 -- current_serial_number VARCHAR2(30) OPTIONAL
1708 -- Serial number for serialized items.
1709 -- Ignored if the verify_cp_flag parameter is 'Y'.
1710
1711 -- original_order_number NUMBER OPTIONAL
1712 -- Sales Order information.
1713 -- Ignored if the verify_cp_flag parameter is 'Y'.
1714
1715 -- purchase_order_number VARCHAR2(50) OPTIONAL
1716 -- Sales Order information.
1717 -- Ignored if the verify_cp_flag parameter is 'Y'.
1718
1719 -----------------------------------------------------------------------
1720 -- This field is no longer present in the record type
1721 -- problem_description VARCHAR2(2000) OPTIONAL
1722 -- Service request problem description
1723 -----------------------------------------------------------------------
1724
1725 -- problem_code VARCHAR2(30) OPTIONAL
1726 -- Service request problem code
1727
1728 -- exp_resolution_date DATE OPTIONAL
1729 -- Service request expected resolution date
1730
1731 -----------------------------------------------------------------------
1732 -- This field is no longer present in the record type
1733 -- make_public_problem VARCHAR2(1) Optional
1734 -----------------------------------------------------------------------
1735
1736 -- install_site_use_id NUMBER Optional
1737
1738
1739
1740 ------------------------------------------------------------------------
1741
1742 -- These fields are no longer there in the service_request_rec_type
1743
1744 -- install_location VARCHAR2(40) Optional
1745 -- install_customer VARCHAR2(50) Optional
1746 -- install_country VARCHAR2(60) Optional
1747 -- install_address_1 VARCHAR2(240) Optional
1748 -- install_address_2 VARCHAR2(240) Optional
1749 -- install_address_3 VARCHAR2(240) Optional
1750
1751
1752
1753 -----------------------------------------------------------------------
1754 -- These fields are no longer present in the record type
1755
1756 -- rma_flag VARCHAR2(1) Required
1757 -- Corresponds to the column RMA_FLAG in the table CS_INCIDENTS.
1758 -- Allows API callers to request that the API does the validation
1759 -- of the optional RMA header ID. It can only be set when the
1760 -- verify_request_flag parameter is set to 'Y'.
1761 -- rma_header_id NUMBER Optional
1762 -- Sales order header identifier of the RMA.
1763 -- Ignored if the rma_flag parameter is 'N'.
1764 -- web_entry_flag VARCHAR2(1) Required
1765 -- Indicates whether the service request is entered via the web.
1766 -------------------------------------------------------------------------------
1767
1768
1769 -- request_segment1 VARCHAR2(150) Optional
1770 -- request_segment2 VARCHAR2(150) Optional
1771 -- request_segment3 VARCHAR2(150) Optional
1772 -- request_segment4 VARCHAR2(150) Optional
1773 -- request_segment5 VARCHAR2(150) Optional
1774 -- request_segment6 VARCHAR2(150) Optional
1775 -- request_segment7 VARCHAR2(150) Optional
1776 -- request_segment8 VARCHAR2(150) Optional
1777 -- request_segment9 VARCHAR2(150) Optional
1778 -- request_segment10 VARCHAR2(150) Optional
1779 -- request_segment11 VARCHAR2(150) Optional
1780 -- request_segment12 VARCHAR2(150) Optional
1781 -- request_segment13 VARCHAR2(150) Optional
1782 -- request_segment14 VARCHAR2(150) Optional
1783 -- request_segment15 VARCHAR2(150) Optional
1787 -- bill_to_site_use_id NUMBER Optional
1784 -- request_context VARCHAR2(30) Optional
1785
1786
1788 -- Bill To site use identifier
1789
1790 -- bill_to_contact_id NUMBER Optional
1791 -- Bill To contact identifier
1792
1793
1794
1795 ------------------------------------------------------------------------
1796
1797 -- These fields are no longer there in the service_request_rec_type
1798
1799 -- bill_to_location VARCHAR2(40) Optional
1800
1801 -- bill_to_customer VARCHAR2(50) Optional
1802
1803 -- bill_country VARCHAR2(60) Optional
1804
1805 -- bill_to_address_1 VARCHAR2(240) Optional
1806
1807 -- bill_to_address_2 VARCHAR2(240) Optional
1808
1809 -- bill_to_address_3 VARCHAR2(240) Optional
1810
1811 -- bill_to_contact VARCHAR2(100) Optional
1812 -----------------------------------------------------------------------------------------
1813
1814
1815 -- ship_to_site_use_id NUMBER Optional
1816 -- Ship To site use identifier
1817
1818 -- ship_to_contact_id NUMBER Optional
1819 -- Ship To contact identifier
1820 ------------------------------------------------------------------------
1821
1822 -- These fields are no longer there in the service_request_rec_type
1823
1824 -- ship_to_location VARCHAR2(40) Optional
1825
1826 -- ship_to_customer VARCHAR2(50) Optional
1827
1828 -- ship_country VARCHAR2(60) Optional
1829
1830 -- ship_to_address_1 VARCHAR2(240) Optional
1831
1832 -- ship_to_address_2 VARCHAR2(240) Optional
1833
1834 -- ship_to_address_3 VARCHAR2(240) Optional
1835
1836 -- ship_to_contact VARCHAR2(100) Optional
1837 -----------------------------------------------------------------------------
1838
1839 ------------------------------------------------------------------------
1840 -- This field is no longer there in the record type
1841 -- problem_resolution VARCHAR2(2000) OPTIONAL
1842 -- Service request problem resolution
1843 ---------------------------------------------------------------------
1844
1845 -- resolution_code VARCHAR2(30) OPTIONAL
1846 -- Service request resolution code
1847
1848 -- act_resolution_date DATE OPTIONAL
1849 -- Service request actual resolution date
1850
1851 ------------------------------------------------------------------------
1852 -- This field is no longer there in the record type
1853 -- make_public_resolution VARCHAR2(1) Optional
1854 -----------------------------------------------------------------------
1855
1856 -- public_comment_flag VARCHAR2(1) OPTIONAL
1857 -- parent_interaction_id NUMBER OPTIONAL
1858 -- contract_service_id NUMBER OPTIONAL
1859 -- qa_collection_plan_id NUMBER OPTIONAL
1860 -- account_id NUMBER OPTIONAL
1861 -- cust_po_number VARCHAR2(50) OPTIONAL
1862 -- cust_ticket_number VARCHAR2(50) OPTIONAL
1863 -- sr_creation_channel VARCHAR2(50) OPTIONAL
1864 -- obligation_date DATE OPTIONAL
1865 -- time_zone_id NUMBER OPTIONAL
1866 -- time_difference NUMBER OPTIONAL
1867 -- site_id NUMBER OPTIONAL
1868 -- customer_site_id NUMBER OPTIONAL
1869 -- territory_id NUMBER OPTIONAL
1870 -- initialize_flag VARCHAR2(1) OPTIONAL
1871
1872 -- cp_revision_id NUMBER OPTIONAL
1873 -- inv_item_revision VARCHAR2(3) OPTIONAL
1874 -- inv_component_id NUMBER OPTIONAL
1875 -- inv_component_version VARCHAR2(3) OPTIONAL
1876 -- inv_subcomponent_id NUMBER OPTIONAL
1877 -- inv_subcomponent_version VARCHAR2(3) OPTIONAL
1878
1879 -- coverage_type VARCHAR2(30) Optional
1880 -- Service Request Coverage Type
1881 -- bill_to_account_id NUMBER Optional
1882 -- Service Request Bill To Account Identifier
1883 -- ship_to_account_id NUMBER Optional
1884 -- Service Request Ship To Account Identifier
1885 -- customer_phone_id NUMBER Optional
1886 -- SR Customer's non-primary phone Id
1887 -- customer_email_id NUMBER Optional
1888 -- SR Customer's non-primary Email Id
1889
1890 -- End of service_request_rec_type comments
1891 --------------------------------------------------------------
1892
1893 PROCEDURE Create_ServiceRequest(
1894 p_api_version IN NUMBER,
1895 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1896 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1897 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
1898 x_return_status OUT NOCOPY VARCHAR2,
1899 x_msg_count OUT NOCOPY NUMBER,
1900 x_msg_data OUT NOCOPY VARCHAR2,
1901 p_resp_appl_id IN NUMBER DEFAULT NULL,
1902 p_resp_id IN NUMBER DEFAULT NULL,
1903 p_user_id IN NUMBER,
1904 p_login_id IN NUMBER DEFAULT NULL,
1905 p_org_id IN NUMBER DEFAULT NULL,
1906 p_request_id IN NUMBER DEFAULT NULL,
1907 p_request_number IN VARCHAR2 DEFAULT NULL,
1911 p_contacts IN contacts_table,
1908 p_invocation_mode IN VARCHAR2 := 'NORMAL' ,
1909 p_service_request_rec IN service_request_rec_type,
1910 p_notes IN notes_table,
1912 -- Added for Assignment Manager 11.5.9 change
1913 p_auto_assign IN VARCHAR2 Default 'N',
1914 --------------anmukher----------------------07/31/03
1915 -- Added for 11.5.10 projects (AutoTask, Miscellaneous ERs)
1916 p_auto_generate_tasks IN VARCHAR2 Default 'N',
1917 p_default_contract_sla_ind IN VARCHAR2 Default 'N',
1918 p_default_coverage_template_id IN NUMBER Default NULL,
1919 x_sr_create_out_rec OUT NOCOPY sr_create_out_rec_type
1920 ---------------anmukher----------------------07/31/03
1921 -- The following OUT parameters have been added to the record type sr_create_out_rec_type
1922 -- and have therefore been commented out. This will allow avoidance of future overloading
1923 -- if a new OUT parameter were to be needed, since it can be added to the same record type.
1924 -- x_request_id OUT NOCOPY NUMBER,
1925 -- x_request_number OUT NOCOPY VARCHAR2,
1926 -- x_interaction_id OUT NOCOPY NUMBER,
1927 -- x_workflow_process_id OUT NOCOPY NUMBER,
1928 -- x_individual_owner OUT NOCOPY NUMBER,
1929 -- x_group_owner OUT NOCOPY NUMBER,
1930 -- x_individual_type OUT NOCOPY VARCHAR2
1931 );
1932
1933 ----------------anmukher--------------07/31/03
1934 -- Overloaded procedure added for backward compatibility in 11.5.10
1935 -- since several new OUT parameters have been added to the 11.5.9 signature
1936 -- in the form of a new record type, sr_create_out_rec_type
1937 PROCEDURE Create_ServiceRequest(
1938 p_api_version IN NUMBER,
1939 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1940 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1941 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
1942 x_return_status OUT NOCOPY VARCHAR2,
1943 x_msg_count OUT NOCOPY NUMBER,
1944 x_msg_data OUT NOCOPY VARCHAR2,
1945 p_resp_appl_id IN NUMBER DEFAULT NULL,
1946 p_resp_id IN NUMBER DEFAULT NULL,
1947 p_user_id IN NUMBER,
1948 p_login_id IN NUMBER DEFAULT NULL,
1949 p_org_id IN NUMBER DEFAULT NULL,
1950 p_request_id IN NUMBER DEFAULT NULL,
1951 p_request_number IN VARCHAR2 DEFAULT NULL,
1952 p_invocation_mode IN VARCHAR2 := 'NORMAL' ,
1953 p_service_request_rec IN service_request_rec_type,
1954 p_notes IN notes_table,
1955 p_contacts IN contacts_table,
1956 -- Added for Assignment Manager 11.5.9 change
1957 p_auto_assign IN VARCHAR2 Default 'N',
1958 p_default_contract_sla_ind IN VARCHAR2 Default 'N',
1959 x_request_id OUT NOCOPY NUMBER,
1960 x_request_number OUT NOCOPY VARCHAR2,
1961 x_interaction_id OUT NOCOPY NUMBER,
1962 x_workflow_process_id OUT NOCOPY NUMBER,
1963 x_individual_owner OUT NOCOPY NUMBER,
1964 x_group_owner OUT NOCOPY NUMBER,
1965 x_individual_type OUT NOCOPY VARCHAR2
1966 );
1967
1968 /* This is a overloaded procedure for create service request which is mainly
1969 created for making the changes for 1159 backward compatiable. This does not
1970 contain the following parameters:-
1971 x_individual_owner, x_group_owner, x_individual_type and p_auto_assign.
1972 and will call the above procedure with all these parameters and version
1973 as 3.0*/
1974
1975 PROCEDURE Create_ServiceRequest(
1976 p_api_version IN NUMBER,
1977 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1978 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1979 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
1980 x_return_status OUT NOCOPY VARCHAR2,
1981 x_msg_count OUT NOCOPY NUMBER,
1982 x_msg_data OUT NOCOPY VARCHAR2,
1983 p_resp_appl_id IN NUMBER DEFAULT NULL,
1984 p_resp_id IN NUMBER DEFAULT NULL,
1985 p_user_id IN NUMBER,
1986 p_login_id IN NUMBER DEFAULT NULL,
1987 p_org_id IN NUMBER DEFAULT NULL,
1988 p_request_id IN NUMBER DEFAULT NULL,
1989 p_request_number IN VARCHAR2 DEFAULT NULL,
1990 p_invocation_mode IN VARCHAR2 := 'NORMAL' ,
1991 p_service_request_rec IN service_request_rec_type,
1992 p_notes IN notes_table,
1993 p_contacts IN contacts_table,
1994 p_default_contract_sla_ind IN VARCHAR2 Default 'N',
1995 x_request_id OUT NOCOPY NUMBER,
1996 x_request_number OUT NOCOPY VARCHAR2,
1997 x_interaction_id OUT NOCOPY NUMBER,
1998 x_workflow_process_id OUT NOCOPY NUMBER
1999 );
2000
2001
2002
2003
2004
2005 --------------------------------------------------------------------------
2006 -- Start of comments
2007 -- API name : Update_ServiceRequest
2008 -- Type : Private
2009 -- Function : Updates a service request in the table CS_INCIDENTS.
2010 -- Pre-reqs : None.
2011 -- Parameters :
2012 -- IN :
2013 -- p_api_version IN NUMBER Required
2014 -- p_init_msg_list IN VARCHAR2 Optional
2015 -- Default = FND_API.G_FALSE
2019 -- Default = FND_API.G_VALID_LEVEL_FULL
2016 -- p_commit IN VARCHAR2 Optional
2017 -- Default = FND_API.G_FALSE
2018 -- p_validation_level IN NUMBER Optional
2020 -- p_request_id IN NUMBER Required
2021 -- p_object_version_number IN NUMBER Required for Web-Apps
2022 -----------------------------------------------------------------
2023 -- No longer there in the API
2024 -- p_org_id IN NUMBER Optional
2025 -- For validating the service request id
2026 -- Default = NULL
2027 ----------------------------------------------------------------------------
2028 -- p_resp_appl_id IN NUMBER Optional
2029 -- p_resp_id IN NUMBER Optional
2030 -- p_last_updated_by IN NUMBER Required
2031 -- VAlid user from fnd_user
2032
2033 -- p_last_update_login IN NUMBER Optional
2034 -- Default = NULL
2035 -- p_last_update_date IN DATE Required
2036
2037 -- p_service_request_rec IN service_request_rec_type Required
2038
2039 -- p_update_desc_flex IN VARCHAR2(1) Optional
2040 -- Indicates whether the descriptive flexfields are being updated
2041 -- Default = FND_API.G_FALSE
2042
2043 -- p_notes IN notes_table Optional
2044
2045 -- p_contacts IN contacts_table Optional
2046 --
2047 ------------------------------------------------------
2048 -- p_audit_comments IN VARCHAR2
2049 -- To be used for the audit record
2050 -- Default = NULL
2051
2052 -- p_called_by_workflow IN VARCHAR2(1)
2053 -- Indicates whether this API is being called by the active
2054 -- workflow process for the service request
2055 -- Default = FND_API.G_FALSE
2056
2057 -- p_workflow_process_id IN NUMBER
2058 -- The workflow process id of the workflow process that is
2059 -- calling this API
2060 -- Default = NULL
2061
2062 -- OUT :
2063 -- x_workflow_process_id OUT NUMBER
2064 -- x_interaction_id OUT NUMBER
2065 --
2066 -- x_return_status OUT VARCHAR2(1) Required
2067 -- x_msg_count OUT NUMBER Required
2068 -- x_msg_data OUT VARCHAR2(2000) Required
2069 -- x_call_id OUT NUMBER Required
2070 --
2071 -- Version : Current version 1.1
2072 -- Added IN parameter p_parent_interaction_id.
2073 -- Previous version 1.0
2074 -- Initial Version 1.0
2075 --
2076 -- Notes:
2077 --
2078 -- End of comments
2079 --------------------------------------------------------------------------
2080 --
2081 --
2082 -- Start of comments
2083 -- Record Type : Service_Request_Rec_Type
2084 -- Description : Holds the Service Request attributes
2085 -- for the Update_ServiceRequest Procedure.
2086 -- Fields :
2087 -- request_date DATE
2088 --
2089 -- type_id NUMBER Optional
2090 -- Cannot be NULL
2091 -- status_id NUMBER Optional
2092 -- Cannot be NULL
2093 -- severity_id NUMBER Optional
2094 -- Cannot be NULL
2095 -- urgency_id NUMBER Optional
2096 -- closed_date DATE Optional
2097 -- owner_id NUMBER Optional
2098 -- Cannot be NULL
2099 -- owner_group_id NUMBER Optional
2100 -- publish_flag VARCHAR2 Optional
2101 -- summary VARCHAR2 Optional
2102 -- Cannot be NULL
2103 ----------------------------------------------------------------------
2104 -- This field is no longer there in the record type
2105 -- verify_request_flag VARCHAR2 Optional
2106 -- Must be either 'Y' or 'N'
2107 ----------------------------------------------------------------------
2108 -- customer_id NUMBER Optional
2109 -- customer_number VARCHAR2 Optional
2110 ------------------------------------------------------------------------
2111 -- These fields are no longer there in the service_request_rec_type
2112 -- customer_prefix VARCHAR2 Optional
2113 -- customer_firstname VARCHAR2 Optional
2114 -- customer_lastname VARCHAR2 Optional
2115 -- customer_company_name VARCHAR2 Optional
2116 -----------------------------------------------------------------------------------
2117 -- employee_id NUMBER
2118 ---------------------------------------------------------------------------
2119 --- These fields are no longer there in rec type
2120 -- contact01_id NUMBER Optional
2121 -- contact01_prefix VARCHAR2 Optional
2122 -- contact01_firstname VARCHAR2 Optional
2123 -- contact01_lastname VARCHAR2 Optional
2124 -- contact01_area_code VARCHAR2 Optional
2125 -- contact01_telephone VARCHAR2 Optional
2126 -- contact01_extension VARCHAR2 Optional
2127 -- contact01_fax_area_code VARCHAR2 Optional
2128 -- contact01_fax_number VARCHAR2 Optional
2129 -- contact01_email_address VARCHAR2 Optional
2130 ------------------------------------------------------------------------
2131 -- This field is no longer in the service request record type
2132 -- contact_time_diff NUMBER Optional
2133 --------------------------------------------------------------------------
2134 --- These fields are no longer there in rec type
2135 -- contact02_id NUMBER Optional
2136 -- Service request customer represented by identifier
2137 -- contact02_prefix VARCHAR2(50) Optional
2138 -- Service request customer represented by prefix
2139 -- contact02_firstname VARCHAR2(150) Optional
2140 -- Service request customer represented by firstname
2144 -- contact02_telephone VARCHAR2(40) Optional
2141 -- contact02_lastname VARCHAR2(150) Optional
2142 -- Service request customer represented by lastname
2143 -- contact02_area_code VARCHAR2(10) Optional
2145 -- contact02_extension VARCHAR2(20) Optional
2146 -- contact02_fax_area_code VARCHAR2(10) Optional
2147 -- contact02_fax_number VARCHAR2(40) Optional
2148 -- contact02_email_address VARCHAR2(2000) Optional
2149 ---------------------------------------------------------------------------
2150 -- verify_cp_flag VARCHAR2 Optional
2151 -- Must be either 'Y' or 'N'.
2152 -- customer_product_id NUMBER Optional
2153 -- For Installed Base mode only
2154 -------------------------------------------------------------------
2155 -- no longer there
2156 -- lot_num VARCHAR2(30) Optional
2157 ----------------------------------------------------------------------
2158
2159 -- Supporting platform_id again because of enh 1711552
2160 -- platform_id NUMBER Optional
2161 -- ********THE functionality for the below 2 fileds is no longer supported.
2162 -- platform_version_id NUMBER Optional
2163 -- language_id NUMBER Optional
2164 -- This is the Product's language id
2165 -- cp_component_id NUMBER Optional
2166 -- cp_component_version_id NUMBER Optional
2167 -- cp_subcomponent_id NUMBER Optional
2168 -- cp_subcomponent_version_id NUMBER Optional
2169 -- language VARCHAR2(4) Optional
2170 -- This is used for TL tables
2171 -- If not passed to the api, the userenv('LANG') is used.
2172 -- inventory_item_id NUMBER Optional
2173 -- inventory_org_id NUMBER Optional
2174 -- current_serial_number VARCHAR2 Optional
2175 -- Used only if verify_cp_flag is 'N'
2176 -- original_order_number NUMBER Optional
2177 -- Used only if verify_cp_flag is 'N'
2178 -- purchase_order_num VARCHAR2 Optional
2179 -- Used only if verify_cp_flag is 'N'
2180 --------------------------------------------------------------------------
2181 -- This field is not there in the record type
2182 -- problem_description VARCHAR2 Optional
2183 ------------------------------------------------------------------------
2184 -- problem_code VARCHAR2 Optional
2185 -- exp_resolution_date DATE Optional
2186 -----------------------------------------------------------------------------
2187 -- This field is not there in the record type
2188 -- make_public_problem VARCHAR2 Optional
2189 ---------------------------------------------------------------------------------
2190 -- install_site_use_id NUMBER Optional
2191 ------------------------------------------------------------------------
2192 -- These fields are no longer there in the service_request_rec_type
2193 -- install_location VARCHAR2 Optional
2194 -- install_customer VARCHAR2 Optional
2195 -- install_country VARCHAR2 Optional
2196 -- install_address_1 VARCHAR2 Optional
2197 -- install_address_2 VARCHAR2 Optional
2198 -- install_address_3 VARCHAR2 Optional
2199 -----------------------------------------------------------------------
2200 -- These fields are no longer present in the record type
2201 -- rma_flag VARCHAR2 Optional
2202 -- Must be either 'Y' or 'N'. Can only be set to 'Y' for verified
2203 -- requests
2204 -- rma_header_id IN NUMBER Optional
2205 -- web_entry_flag VARCHAR2 Optional
2206 -- Indicates whether the update was done through the web
2207 -- Default = 'N'
2208 ------------------------------------------------------------------------------
2209 -- request_attribute_1 VARCHAR2 Optional
2210 -- Default = NULL
2211 -- request_attribute_2 VARCHAR2 Optional
2212 -- Default = NULL
2213 -- request_attribute_3 VARCHAR2 Optional
2214 -- Default = NULL
2215 -- request_attribute_4 VARCHAR2 Optional
2216 -- Default = NULL
2217 -- request_attribute_5 VARCHAR2 Optional
2218 -- Default = NULL
2219 -- request_attribute_6 VARCHAR2 Optional
2220 -- Default = NULL
2221 -- request_attribute_7 VARCHAR2 Optional
2222 -- Default = NULL
2223 -- request_attribute_8 VARCHAR2 Optional
2224 -- Default = NULL
2225 -- request_attribute_9 VARCHAR2 Optional
2226 -- Default = NULL
2227 -- request_attribute_10 VARCHAR2 Optional
2228 -- Default = NULL
2229 -- request_attribute_11 VARCHAR2 Optional
2230 -- Default = NULL
2231 -- request_attribute_12 VARCHAR2 Optional
2232 -- Default = NULL
2233 -- request_attribute_13 VARCHAR2 Optional
2234 -- Default = NULL
2235 -- request_attribute_14 VARCHAR2 Optional
2236 -- Default = NULL
2237 -- request_attribute_15 VARCHAR2 Optional
2238 -- Default = NULL
2239 -- request_context VARCHAR2 Optional
2240 -- Default = NULL
2241 -- bill_to_site_use_id NUMBER Optional
2242 -- bill_to_contact_id NUMBER Optional
2243 ------------------------------------------------------------------------
2244 -- These fields are no longer there in the service_request_rec_type
2245 -- bill_to_location VARCHAR2 Optional
2246 -- bill_to_customer VARCHAR2 Optional
2247 -- bill_country VARCHAR2 Optional
2248 -- bill_to_address_1 VARCHAR2 Optional
2249 -- bill_to_address_2 VARCHAR2 Optional
2250 -- bill_to_address_3 VARCHAR2 Optional
2251 -- bill_to_contact VARCHAR2 Optional
2252 ---------------------------------------------------------------------------------------
2253 -- ship_to_site_use_id NUMBER Optional
2254 -- ship_to_contact_id NUMBER Optional
2255 -----------------------------------------------------------------------
2256 -- These fields are no longer there in the service_request_rec_type
2257 -- ship_to_location VARCHAR2 Optional
2258 -- ship_to_customer VARCHAR2 Optional
2259 -- ship_country VARCHAR2 Optional
2260 -- ship_to_address_1 VARCHAR2 Optional
2261 -- ship_to_address_2 VARCHAR2 Optional
2262 -- ship_to_address_3 VARCHAR2 Optional
2263 -- ship_to_contact VARCHAR2 Optional
2264 ----------------------------------------------------------
2265 -- This field is not there in the record type
2266 -- problem_resolution VARCHAR2 Optional
2267 ------------------------------------------------------------------------
2268 -- resolution_code VARCHAR2 Optional
2269 -- act_resolution_date DATE Optional
2270 -- Must be later than the service request date
2271 ----------------------------------------------------------
2272 -- This field
2273 -- make_public_resolution VARCHAR2 Optional
2274 ------------------------------------------------------------------
2275 -- public_comment_flag VARCHAR2 Optional
2276 -- Default = 'N'
2277 -- parent_interaction_id NUMBER Optional
2278 -- Corresponds to the column INTERACTION_ID in the table
2279 -- CS_INTERACTIONS, and identifies the parent interaction that
2280 -- resulted in this service request update
2281 -- contract_service_id NUMBER Optional
2282 -- qa_collection_plan_id NUMBER Optional
2283 -- account_id NUMBER Optional
2284 -- resource_type VARCHAR2(30) Optional
2285 -- resource_subtype_id NUMBER Optional
2286 -- cust_po_number VARCHAR2(50) Optional
2287 -- cust_ticket_number VARCHAR2(50) Optional
2288 ------------------------------------------------------------------
2289 -- This cannot be updated
2290 -- sr_creation_channel VARCHAR2(50) Optional
2291 ----------------------------------------------------------------------
2292 -- obligation_date DATE Optional
2293 -- time_zone_id NUMBER Optional
2294 -- time_difference NUMBER Optional
2295 -- site_id NUMBER Optional
2296 -- customer_site_id NUMBER Optional
2297 -- territory_id NUMBER Optional
2298 -- cp_revision_id NUMBER OPTIONAL
2299 -- inv_item_revision VARCHAR2(3) OPTIONAL
2300 -- inv_component_id NUMBER OPTIONAL
2301 -- inv_component_version VARCHAR2(3) OPTIONAL
2302 -- inv_subcomponent_id NUMBER OPTIONAL
2303 -- inv_subcomponent_version VARCHAR2(3) OPTIONAL
2304 -- initialize_flag VARCHAR2(1) Optional
2305 -- coverage_type VARCHAR2(30) Optional
2306 -- Service Request Coverage Type
2307 -- bill_to_account_id NUMBER Optional
2308 -- Service Request Bill To Account Identifier
2309 -- ship_to_account_id NUMBER Optional
2310 -- Service Request Ship To Account Identifier
2311 -- customer_phone_id NUMBER Optional
2312 -- SR Customer's non-primary phone Id
2313 -- customer_email_id NUMBER Optional
2314 -- SR Customer's non-primary Email Id
2315 -- End of service_request_rec_type comments
2316 --------------------------------------------------------------
2317
2318 PROCEDURE Update_ServiceRequest
2319 ( p_api_version IN NUMBER,
2320 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2321 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2322 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
2323 x_return_status OUT NOCOPY VARCHAR2,
2324 x_msg_count OUT NOCOPY NUMBER,
2325 x_msg_data OUT NOCOPY VARCHAR2,
2326 p_request_id IN NUMBER,
2327 p_audit_id IN NUMBER DEFAULT NULL,
2328 p_object_version_number IN NUMBER,
2329 p_resp_appl_id IN NUMBER DEFAULT NULL,
2330 p_resp_id IN NUMBER DEFAULT NULL,
2331 p_last_updated_by IN NUMBER,
2332 p_last_update_login IN NUMBER DEFAULT NULL,
2333 p_last_update_date IN DATE,
2334 p_service_request_rec IN service_request_rec_type,
2335 p_invocation_mode IN VARCHAR2 := 'NORMAL',
2336 p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
2337 p_notes IN notes_table,
2338 p_contacts IN contacts_table,
2339 p_audit_comments IN VARCHAR2 DEFAULT NULL,
2340 p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
2341 p_workflow_process_id IN NUMBER DEFAULT NULL,
2342 -- Commented out since these are now part of the out rec type --anmukher--08/08/03
2343 -- x_workflow_process_id OUT NOCOPY NUMBER,
2344 -- x_interaction_id OUT NOCOPY NUMBER,
2345 ----------------anmukher--------------------08/05/03
2346 -- Added for 11.5.10 projects
2347 p_auto_assign IN VARCHAR2 Default 'N',
2348 p_validate_sr_closure IN VARCHAR2 Default 'N',
2349 p_auto_close_child_entities IN VARCHAR2 Default 'N',
2350 p_default_contract_sla_ind IN VARCHAR2 Default 'N',
2351 x_sr_update_out_rec OUT NOCOPY sr_update_out_rec_type
2352 );
2353
2354 ----------------anmukher--------------08/11/03
2355 -- Overloaded procedure added for backward compatibility in 11.5.10
2356 -- since several new OUT parameters have been added to the 11.5.9 signature
2357 -- in the form of a new record type, sr_update_out_rec_type
2358 PROCEDURE Update_ServiceRequest
2359 ( p_api_version IN NUMBER,
2360 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2361 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2362 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
2363 x_return_status OUT NOCOPY VARCHAR2,
2364 x_msg_count OUT NOCOPY NUMBER,
2365 x_msg_data OUT NOCOPY VARCHAR2,
2366 p_request_id IN NUMBER,
2367 p_audit_id IN NUMBER DEFAULT NULL,
2368 p_object_version_number IN NUMBER,
2369 p_resp_appl_id IN NUMBER DEFAULT NULL,
2370 p_resp_id IN NUMBER DEFAULT NULL,
2371 p_last_updated_by IN NUMBER,
2372 p_last_update_login IN NUMBER DEFAULT NULL,
2373 p_last_update_date IN DATE,
2374 p_service_request_rec IN service_request_rec_type,
2375 p_invocation_mode IN VARCHAR2 := 'NORMAL',
2376 p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
2377 p_notes IN notes_table,
2378 p_contacts IN contacts_table,
2379 p_audit_comments IN VARCHAR2 DEFAULT NULL,
2380 p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
2381 p_workflow_process_id IN NUMBER DEFAULT NULL,
2382 p_default_contract_sla_ind IN VARCHAR2 Default 'N',
2383 x_workflow_process_id OUT NOCOPY NUMBER,
2384 x_interaction_id OUT NOCOPY NUMBER
2385 );
2386
2387 --------------------------------------------------------------------------
2388 -- Start of comments
2389 -- API Name : Update_Status
2390 -- Type : Private
2391 -- Description : Update the status of a service request
2392 -- Pre-reqs : None
2393 -- Parameters :
2394 -- IN :
2395 -- p_api_version IN NUMBER Required
2396 -- p_init_msg_list IN VARCHAR2 Optional
2397 -- Default = FND_API.G_FALSE
2398 -- p_commit IN VARCHAR2 Optional
2399 -- Default = FND_API.G_FALSE
2400 -- p_validation_level IN NUMBER Optional
2401 -- Default = FND_API.G_VALID_LEVEL_FULL
2402 -- p_request_id IN NUMBER Required
2403 ----------------------------------------------------
2404 -- Removed from the api
2405 -- p_org_id IN NUMBER Optional
2406 -- For validating the service request ID
2407 -- Default = NULL
2408 ----------------------------------------------------------
2409
2410 -- p_status_id IN NUMBER Required
2411 -- Cannot be NULL
2412 -- p_closed_date IN DATE Optional
2413 -- The date the service request is closed
2414 -- Default = NULL
2415 -- p_last_updated_by IN VARCHAR2 Required
2416 -- p_last_update_login IN NUMBER Optional
2417 -- Default = NULL
2418 -- p_last_update_date IN DATE Required
2419 -- p_audit_comments IN VARCHAR2 Optional
2420 -- To be used for the audit record
2421 -- Default = NULL
2422 -- p_call_by_workflow IN VARCHAR2 Optional
2423 -- Indicates whether this API is being called by a workflow
2424 -- process
2425 -- Default = FND_API.G_FALSE
2426 -- p_workflow_process_id IN NUMBER Optional
2427 -- The workflow process id of the workflow process that is
2428 -- calling this API
2429 -- Default = NULL
2430 -- p_comments IN VARCHAR2 Optional
2431 -- Default = NULL
2432 -- p_public_comment_flag IN VARCHAR2 Optional
2433 -- Default = 'N'
2434 -- p_parent_interaction_id IN NUMBER Optional
2435 -- Corresponds to the column INTERACTION_ID in the table
2436 -- CS_INTERACTIONS, and identifies the parent interaction that
2437 -- resulted in this service request update
2438 --
2439 -- OUT :
2440 -- p_return_status OUT VARCHAR2(1) Required
2441 -- p_msg_count OUT NUMBER Required
2442 -- p_msg_data OUT VARCHAR2(2000) Required
2443 -- p_call_id OUT NUMBER Required
2444 --
2445 -- Version : Current version 1.1
2446 -- Added IN parameter p_parent_interaction_id.
2447 -- Previous version 1.0
2448 -- Initial Version 1.0
2449 --
2450 -- Notes: : If the old value is the same as the new value, then no
2451 -- update is performed and a warning message is appended to the
2452 -- message list.
2453 --
2454 -- If there is an active workflow process for the service
2455 -- request, its status cannot be updated to a "closed" status
2456 -- (a status with the close_flag set) unless the caller of the
2457 -- API is the workflow process itself. In that case, the caller
2458 -- must pass in the workflow_process_id of the process for
2459 -- validation.
2460 --
2461 -- The p_closed_date parameter is ignored if the new status is
2462 -- not a "closed" status. If this parameter is not passed in
2463 -- for a "closed" status, sysdate will be used as the default
2464 -- value.
2465 --
2466 -- End of comments
2467 --------------------------------------------------------------------------
2468
2469
2470 FUNCTION Get_API_Revision
2471 RETURN NUMBER;
2472
2473 PROCEDURE Update_Status
2474 ( p_api_version IN NUMBER,
2475 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2476 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2477 p_resp_id IN NUMBER,
2478 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
2479 x_return_status OUT NOCOPY VARCHAR2,
2480 x_msg_count OUT NOCOPY NUMBER,
2481 x_msg_data OUT NOCOPY VARCHAR2,
2482 p_request_id IN NUMBER,
2483 p_object_version_number IN NUMBER,
2484 p_status_id IN NUMBER,
2485 p_closed_date IN DATE DEFAULT fnd_api.g_miss_date,
2486 p_last_updated_by IN NUMBER,
2487 p_last_update_login IN NUMBER DEFAULT NULL,
2488 p_last_update_date IN DATE,
2489 p_audit_comments IN VARCHAR2 DEFAULT NULL,
2490 p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
2491 p_workflow_process_id IN NUMBER DEFAULT NULL,
2492 p_comments IN VARCHAR2 DEFAULT NULL,
2493 p_public_comment_flag IN VARCHAR2 DEFAULT fnd_api.g_false,
2494 p_parent_interaction_id IN NUMBER DEFAULT NULL,
2495 -- Added for 11.5.10 projects
2496 p_validate_sr_closure IN VARCHAR2 Default 'N',
2497 p_auto_close_child_entities IN VARCHAR2 Default 'N',
2498 x_interaction_id OUT NOCOPY NUMBER
2499 );
2500
2501 --------------------------------------------------------------------------
2502 -- Start of comments
2503 -- API Name : Update_Owner
2504 -- Type : Private
2505 -- Description : Update the owner field of a service request
2506 -- Pre-reqs : p_owner_id must be a valid employee ID for an active
2507 -- employee in HR.
2508 -- Parameters :
2509 -- IN :
2510 -- p_api_version IN NUMBER Required
2511 -- p_init_msg_list IN VARCHAR2 Optional
2512 -- Default = FND_API.G_FALSE
2513 -- p_commit IN VARCHAR2 Optional
2514 -- Default = FND_API.G_FALSE
2515 -- p_validation_level IN NUMBER Optional
2516 -- Default = FND_API.G_VALID_LEVEL_FULL
2517 -- p_request_id IN NUMBER Required
2518 -- p_resp_id IN NUMBER Optional
2519 -- Default = NULL
2520 -- p_resp_appl_id IN NUMBER Optional
2521 -- Default = NULL
2525 -- Removed from API
2522 -- For validating the service request owner
2523
2524 ---------------------------------------------------
2526 -- p_org_id IN NUMBER Optional
2527 -- For validating the service request ID
2528 -- Default = NULL
2529 -----------------------------------------------------------
2530
2531
2532 -- p_owner_id IN VARCHAR2 Required
2533 -- Cannot be NULL
2534 -- p_last_updated_by IN NUMBER Required
2535 -- p_last_update_login IN NUMBER Optional
2536 -- Default = NULL
2537 -- p_last_update_date IN DATE Required
2538 -- p_audit_comments IN VARCHAR2 Optional
2539 -- To be used for the audit record
2540 -- Default = NULL
2541 -- p_call_by_workflow IN VARCHAR2 Optional
2542 -- Indicates whether this API is being called by a workflow
2543 -- process
2544 -- Default = FND_API.G_FALSE
2545 -- p_workflow_process_id IN NUMBER Optional
2546 -- Default = NULL
2547 -- The workflow process id of the workflow process that is
2548 -- calling this API
2549 -- p_comments IN VARCHAR2 Optional
2550 -- Default = NULL
2551 -- p_public_comment_flag IN VARCHAR2 Optional
2552 -- Default = 'N'
2553 -- p_parent_interaction_id IN NUMBER Optional
2554 -- Corresponds to the column INTERACTION_ID in the table
2555 -- CS_INTERACTIONS, and identifies the parent interaction that
2556 -- resulted in this service request update
2557 --
2558 -- OUT :
2559 -- p_return_status OUT VARCHAR2(1) Required
2560 -- p_msg_count OUT NUMBER Required
2561 -- p_msg_data OUT VARCHAR2(2000) Required
2562 -- p_call_id OUT NUMBER Required
2563 --
2564 -- Version : Current version 1.1
2565 -- Added IN parameter p_parent_interaction_id.
2566 -- Previous version 1.0
2567 -- Initial Version 1.0
2568 --
2569 -- Notes: : If the old value is the same as the new value, then no
2570 -- update is performed and a warning message is appended to the
2571 -- message list.
2572 --
2573 -- If there is an active workflow process for the service
2574 -- request, its owner cannot be updated unless the caller of
2575 -- the API is the workflow process itself. In that case, the
2576 -- caller must pass in the workflow_process_id of the process
2577 -- for validation.
2578 --
2579 -- End of comments
2580 --------------------------------------------------------------------------
2581
2582 PROCEDURE Update_Owner
2583 ( p_api_version IN NUMBER,
2584 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
2585 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
2586 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
2587 x_return_status OUT NOCOPY VARCHAR2,
2588 x_msg_count OUT NOCOPY NUMBER,
2589 x_msg_data OUT NOCOPY VARCHAR2,
2590 p_request_id IN NUMBER,
2591 p_object_version_number IN NUMBER,
2592 p_resp_id IN NUMBER DEFAULT NULL,
2593 p_resp_appl_id IN NUMBER DEFAULT NULL,
2597 p_last_updated_by IN NUMBER,
2594 p_owner_id IN NUMBER,
2595 p_owner_group_id IN NUMBER,
2596 p_resource_type IN VARCHAR2,
2598 p_last_update_login IN NUMBER DEFAULT NULL,
2599 p_last_update_date IN DATE,
2600 p_audit_comments IN VARCHAR2 DEFAULT NULL,
2601 p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
2602 p_workflow_process_id IN NUMBER DEFAULT NULL,
2603 p_comments IN VARCHAR2 DEFAULT NULL,
2604 p_public_comment_flag IN VARCHAR2 DEFAULT fnd_api.g_false,
2605 p_parent_interaction_id IN NUMBER DEFAULT NULL,
2606 x_interaction_id OUT NOCOPY NUMBER
2607 );
2608
2609 -- -------------------------------------------------------------------
2610 -- Start of comments
2611 -- API Name : Create_Audit_Record
2612 -- Type : Private
2613 -- Description : Insert an audit record into CS_INCIDENTS_AUDIT for
2614 -- service request updates.
2615 -- Pre-reqs : None
2616 -- Parameters :
2617 -- IN :
2618 -- p_api_version IN NUMBER Required
2619 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API.G_FALSE
2620 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2621 -- p_request_id IN NUMBER Required
2622 -- p_change_flags IN AUDIT_FLAGS_REC_TYPE Required
2623 -- p_old_vals_rec IN AUDIT_VALS_REC_TYPE Optional
2624 -- p_new_vals_rec IN AUDIT_VALS_REC_TYPE Optional
2625 -- p_action_id IN NUMBER Optional Default = FND_API.G_MISS_NUM
2626 -- p_wf_process_name IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2627 -- p_wf_process_itemkey IN VARCHAR2 Optional Default = FND_API.G_MISS_CHAR
2628 -- p_user_id IN NUMBER Required
2629 -- p_login_id IN NUMBER Optional Default = NULL
2630 -- p_comments IN VARCHAR2 Optional Default = NULL
2631 --
2632 -- OUT :
2633 -- p_return_status OUT VARCHAR2 Required Length = 1
2634 -- p_msg_count OUT NUMBER Required
2635 -- p_msg_data OUT VARCHAR2 Required Length = 2000
2636 --
2637 -- Version : Initial Version 1.0
2638 --
2639 -- Notes: :
2640 --
2641 -- End of comments
2642 -- -------------------------------------------------------------------
2643
2644 PROCEDURE Create_Audit_Record (
2645 p_api_version IN NUMBER,
2646 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2647 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2648 x_return_status OUT NOCOPY VARCHAR2,
2649 x_msg_count OUT NOCOPY NUMBER,
2650 x_msg_data OUT NOCOPY VARCHAR2,
2651 p_request_id IN NUMBER,
2652 p_audit_id IN NUMBER DEFAULT NULL,
2653 --p_change_flags IN audit_flags_rec_type,
2654 --p_old_vals_rec IN audit_vals_rec_type DEFAULT G_MISS_AUDIT_VALS_REC,
2655 --p_new_vals_rec IN audit_vals_rec_type DEFAULT G_MISS_AUDIT_VALS_REC,
2656 p_audit_vals_rec IN SR_AUDIT_REC_TYPE,
2657 p_action_id IN NUMBER DEFAULT FND_API.G_MISS_NUM,
2658 p_wf_process_name IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
2659 p_wf_process_itemkey IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
2660 p_user_id IN NUMBER,
2661 p_login_id IN NUMBER DEFAULT NULL,
2662 p_last_update_date IN DATE,
2663 p_creation_date IN DATE,
2664 p_comments IN VARCHAR2 DEFAULT NULL,
2665 x_audit_id OUT NOCOPY NUMBER
2666 );
2667
2668 PROCEDURE Add_Language;
2669
2670 -- Lock row procedure
2671 -- This is used to lock a row in the Service Request form
2672
2673 PROCEDURE LOCK_ROW(
2674 X_INCIDENT_ID NUMBER,
2675 X_OBJECT_VERSION_NUMBER NUMBER
2676 );
2677
2678
2679 /* Added for enh. 2655115, to get the status flag based on close_flag
2680 and status_id by shijain date 27th nov 2002*/
2681
2682 FUNCTION GET_STATUS_FLAG ( p_incident_status_id IN NUMBER)
2683 RETURN VARCHAR2;
2684
2685 /* Added for enh. 2690787, to get the primary flag based on incident_id
2686 and primary_flag by shijain date 09th dec 2002
2687
2688 FUNCTION GET_PRIMARY_CONTACT ( p_incident_id IN NUMBER)
2689 RETURN NUMBER;
2690
2691 */
2692
2693 /* defined the global variable to get the profile valus for cs_sr_restrict_ib
2694 profile variable by shijain 4th dec 2002*/
2695
2696 G_RESTRICT_IB VARCHAR2(5) ;
2697
2698 -- Added for 11.5.10 Auditing project --anmukher --09/10/03
2699
2700 PROCEDURE initialize_audit_rec
2701 (
2702 p_sr_audit_record IN OUT NOCOPY sr_audit_rec_type
2703 );
2704
2705 PROCEDURE Delete_ServiceRequest
2706 (
2707 p_api_version_number IN NUMBER := 1.0
2708 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2709 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2710 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2711 , p_processing_set_id IN NUMBER
2712 , p_purge_set_id IN NUMBER
2713 , p_purge_source_with_open_task IN VARCHAR2
2714 , p_audit_required IN VARCHAR2
2715 , x_return_status OUT NOCOPY VARCHAR2
2716 , x_msg_count OUT NOCOPY NUMBER
2717 , x_msg_data OUT NOCOPY VARCHAR2
2718 );
2719 --------------------------------------------------------------------------------
2720 PROCEDURE process_sr_ext_attrs
2721 ( p_api_version IN NUMBER
2722 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
2723 , p_commit IN VARCHAR2 DEFAULT NULL
2727 , p_modified_by IN NUMBER DEFAULT NULL
2724 , p_incident_id IN NUMBER
2725 , p_ext_attr_grp_tbl IN CS_ServiceRequest_PUB.EXT_ATTR_GRP_TBL_TYPE
2726 , p_ext_attr_tbl IN CS_ServiceRequest_PUB.EXT_ATTR_TBL_TYPE
2728 , p_modified_on IN DATE DEFAULT NULL
2729 , x_failed_row_id_list OUT NOCOPY VARCHAR2
2730 , x_return_status OUT NOCOPY VARCHAR2
2731 , x_errorcode OUT NOCOPY NUMBER
2732 , x_msg_count OUT NOCOPY NUMBER
2733 , x_msg_data OUT NOCOPY VARCHAR2
2734 );
2735 --------------------------------------------------------------------------------
2736 END CS_ServiceRequest_PVT;