1 PACKAGE IEM_SERVICEREQUEST_PVT AUTHID CURRENT_USER AS
2 /* $Header: iemvsrvs.pls 120.1 2006/02/10 07:33:12 pkesani noship $ */
3
4 --
5 --
6 -- Purpose: Maintain Tag Process
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 3/24/2002 Created
11 -- Liang Xia 8/25/2003 Create Update_Status_Wrap based on 1159 release
12 -- cspsrs.pls 115.49
13 -- cspsrb.pls 115.63
14 -- csvsrs.pls 115.67
15 -- csvsrb.pls 115.222
16 -- PKESANI 02/09/2006 As a part of ACSR project, added procedure IEM_CREATE_SR
17 -- as a wrapper API for auto create SR.
18 -- --------- ------ -----------------------------------------
19
20 -- Enter procedure, function bodies as shown below
21
22 /*GLOBAL VARIABLES AVAILABLE TO THE PUBLIC FOR CALLING
23 ===================================================*/
24
25 G_PKG_NAME varchar2(255) :='IEM_SERVICEREQUEST_PVT';
26 G_INITIALIZED CONSTANT VARCHAR2(1) := 'R';
27
28 TYPE service_request_rec_type IS RECORD (
29 request_date DATE,
30 type_id NUMBER,
31 type_name VARCHAR2(30),
32 status_id NUMBER,
33 status_name VARCHAR2(30),
34 severity_id NUMBER,
35 severity_name VARCHAR2(30),
36 urgency_id NUMBER,
37 urgency_name VARCHAR2(30),
38 closed_date DATE,
39 owner_id NUMBER,
40 owner_group_id NUMBER,
41 publish_flag VARCHAR2(1),
42 summary VARCHAR2(80),
43 caller_type VARCHAR2(30),
44 customer_id NUMBER,
45 customer_number VARCHAR2(30),
46 employee_id NUMBER,
47 employee_number VARCHAR2(30),
48 verify_cp_flag VARCHAR2(1),
49 customer_product_id NUMBER,
50 platform_id NUMBER,
51 platform_version VARCHAR2(250),
52 db_version VARCHAR2(250),
53 platform_version_id NUMBER,
54 cp_component_id NUMBER,
55 cp_component_version_id NUMBER,
56 cp_subcomponent_id NUMBER,
57 cp_subcomponent_version_id NUMBER,
58 language_id NUMBER,
59 language VARCHAR2(4),
60 cp_ref_number NUMBER,
61 inventory_item_id NUMBER,
62 inventory_item_conc_segs VARCHAR2(800),
63 inventory_item_segment1 VARCHAR2(200),
64 inventory_item_segment2 VARCHAR2(200),
65 inventory_item_segment3 VARCHAR2(200),
66 inventory_item_segment4 VARCHAR2(200),
67 inventory_item_segment5 VARCHAR2(200),
68 inventory_item_segment6 VARCHAR2(200),
69 inventory_item_segment7 VARCHAR2(200),
70 inventory_item_segment8 VARCHAR2(200),
71 inventory_item_segment9 VARCHAR2(200),
72 inventory_item_segment10 VARCHAR2(200),
73 inventory_item_segment11 VARCHAR2(200),
74 inventory_item_segment12 VARCHAR2(200),
75 inventory_item_segment13 VARCHAR2(200),
76 inventory_item_segment14 VARCHAR2(200),
77 inventory_item_segment15 VARCHAR2(200),
78 inventory_item_segment16 VARCHAR2(200),
79 inventory_item_segment17 VARCHAR2(200),
80 inventory_item_segment18 VARCHAR2(200),
81 inventory_item_segment19 VARCHAR2(200),
82 inventory_item_segment20 VARCHAR2(200),
83 inventory_item_vals_or_ids VARCHAR2(1),
84 inventory_org_id NUMBER,
85 current_serial_number VARCHAR2(30),
86 original_order_number NUMBER,
87 purchase_order_num VARCHAR2(50),
88 problem_code VARCHAR2(50),
89 exp_resolution_date DATE,
90 install_site_use_id NUMBER,
91 request_attribute_1 VARCHAR2(150),
92 request_attribute_2 VARCHAR2(150),
93 request_attribute_3 VARCHAR2(150),
94 request_attribute_4 VARCHAR2(150),
95 request_attribute_5 VARCHAR2(150),
96 request_attribute_6 VARCHAR2(150),
97 request_attribute_7 VARCHAR2(150),
98 request_attribute_8 VARCHAR2(150),
99 request_attribute_9 VARCHAR2(150),
100 request_attribute_10 VARCHAR2(150),
101 request_attribute_11 VARCHAR2(150),
102 request_attribute_12 VARCHAR2(150),
103 request_attribute_13 VARCHAR2(150),
104 request_attribute_14 VARCHAR2(150),
105 request_attribute_15 VARCHAR2(150),
106 request_context VARCHAR2(30),
107 bill_to_site_use_id NUMBER,
108 bill_to_contact_id NUMBER,
109 ship_to_site_use_id NUMBER,
110 ship_to_contact_id NUMBER,
111 resolution_code VARCHAR2(50),
112 act_resolution_date DATE,
113 public_comment_flag VARCHAR2(1),
114 parent_interaction_id NUMBER,
115 contract_service_id NUMBER,
116 contract_service_number VARCHAR2(150),
117 contract_id NUMBER,
118 project_number VARCHAR2(120),
119 qa_collection_plan_id NUMBER,
120 account_id NUMBER,
121 resource_type VARCHAR2(30),
122 resource_subtype_id NUMBER,
123 cust_po_number VARCHAR2(50),
124 cust_ticket_number VARCHAR2(50),
125 sr_creation_channel VARCHAR2(50),
126 obligation_date DATE,
127 time_zone_id NUMBER,
128 time_difference NUMBER,
129 site_id NUMBER,
130 customer_site_id NUMBER,
131 territory_id NUMBER,
132 initialize_flag VARCHAR2(1),
133 cp_revision_id NUMBER,
134 inv_item_revision VARCHAR2(3),
135 inv_component_id NUMBER,
136 inv_component_version VARCHAR2(3),
137 inv_subcomponent_id NUMBER,
138 inv_subcomponent_version VARCHAR2(3),
139 ------jngeorge---------------07/12/01
140 tier VARCHAR2(250),
141 tier_version VARCHAR2(250),
142 operating_system VARCHAR2(250),
143 operating_system_version VARCHAR2(250),
144 database VARCHAR2(250),
145 cust_pref_lang_id NUMBER,
146 category_id NUMBER,
147 group_type VARCHAR2(30),
148 group_territory_id NUMBER,
149 inv_platform_org_id NUMBER,
150 component_version VARCHAR2(3),
151 subcomponent_version VARCHAR2(3),
152 product_revision VARCHAR2(240),
153 comm_pref_code VARCHAR2(30),
154 ---- Added for Post 11.5.6 Enhancement
155 cust_pref_lang_code VARCHAR2(4),
156 last_update_channel VARCHAR2(10)
157 ------jngeorge---------------07/12/01
158 );
159
160
161 -- customer_prefix VARCHAR2(50),
162 -- customer_firstname VARCHAR2(150),
163 -- customer_lastname VARCHAR2(150),
164 -- customer_company_name VARCHAR2(255),
165
166
167 -- install_location VARCHAR2(240),
168 -- install_customer VARCHAR2(150),
169 -- install_country VARCHAR2(60),
170 -- install_address_1 VARCHAR2(240),
171 -- install_address_2 VARCHAR2(240),
172 -- install_address_3 VARCHAR2(240),
173
174
175 -- bill_to_location VARCHAR2(240),
176 -- bill_to_customer VARCHAR2(150),
177 -- bill_country VARCHAR2(60),
178 -- bill_to_address_1 VARCHAR2(240),
179 -- bill_to_address_2 VARCHAR2(240),
180 -- bill_to_address_3 VARCHAR2(240),
181 -- bill_to_contact VARCHAR2(150),
182
183
184 -- ship_to_location VARCHAR2(240),
185 -- ship_to_customer VARCHAR2(150),
186 -- ship_country VARCHAR2(60),
187 -- ship_to_address_1 VARCHAR2(240),
188 -- ship_to_address_2 VARCHAR2(240),
189 -- ship_to_address_3 VARCHAR2(240),
190 -- ship_to_contact VARCHAR2(150),
191
192
193
194 TYPE notes_rec IS RECORD (
195 NOTE VARCHAR2(2000) := FND_API.G_MISS_CHAR,
196 NOTE_DETAIL VARCHAR2(32767) := FND_API.G_MISS_CHAR,
197 NOTE_TYPE VARCHAR2(240) := FND_API.G_MISS_CHAR,
198 NOTE_CONTEXT_TYPE_01 VARCHAR2(30) := FND_API.G_MISS_CHAR,
199 NOTE_CONTEXT_TYPE_ID_01 NUMBER := FND_API.G_MISS_NUM,
200 NOTE_CONTEXT_TYPE_02 VARCHAR2(30) := FND_API.G_MISS_CHAR,
201 NOTE_CONTEXT_TYPE_ID_02 NUMBER := FND_API.G_MISS_NUM,
202 NOTE_CONTEXT_TYPE_03 VARCHAR2(30) := FND_API.G_MISS_CHAR,
203 NOTE_CONTEXT_TYPE_ID_03 NUMBER := FND_API.G_MISS_NUM
204
205 );
206 TYPE notes_table IS TABLE OF notes_rec INDEX BY BINARY_INTEGER;
207
208
209 -----------------------------------------------------------------------
210 -- Start of comments
211 -- Record Type : notes_rec
212 -- Description : Holds the Notes attributes for the
213 -- Creating records in JTF_NOTES_B
214 -- Fields :
215
216 -- NOTE OPTIONAL
217
218 -- NOTE_DETAIL OPTIONAL
219
220 -- NOTE_TYPE OPTIONAL
221 -- VAlid values are SR_PROBLEM and SR_RESOLUTION
222
223 -- NOTE_CONTEXT_TYPE_01 OPTIONAL
224 -- VAlid value is 'SR'
225
226 -- NOTE_CONTEXT_TYPE_ID_01 OPTIONAL
227 -- VAlid incident_id from cs_incidents_all_b
228
229 -- NOTE_CONTEXT_TYPE_02
230 -- VAlid value is 'SR'
231
232 -- NOTE_CONTEXT_TYPE_ID_02
233 -- VAlid incident_id from cs_incidents_all_b
234
235 -- NOTE_CONTEXT_TYPE_03
236 -- VAlid value is 'SR'
237
238
239 -- NOTE_CONTEXT_TYPE_ID_03
240 -- VAlid incident_id from cs_incidents_all_b
241
242 TYPE contacts_rec IS RECORD (
243 SR_CONTACT_POINT_ID NUMBER := FND_API.G_MISS_NUM,
244 PARTY_ID NUMBER := FND_API.G_MISS_NUM,
245 CONTACT_POINT_ID NUMBER := FND_API.G_MISS_NUM,
246 CONTACT_POINT_TYPE VARCHAR2(30) := FND_API.G_MISS_CHAR,
247 PRIMARY_FLAG VARCHAR2(1) := FND_API.G_MISS_CHAR,
248 CONTACT_TYPE VARCHAR2(30) :=FND_API.G_MISS_CHAR
249 );
250
251 TYPE contacts_table IS TABLE OF contacts_rec INDEX BY BINARY_INTEGER;
252
253
254 --
255 -----------------------------------------------------------------------
256 -- Start of comments
257 -- Record Type : contacts_rec
258 -- Description : Holds the Contacts attributes for the
259 -- Creating records in CS_HZ_SR_CONTACT_POINTS
260 -- Fields :
261 --
262 -- sr_contact_point_id OPTIONAL
263 -- can be specified only if you waNt to update an existing
264 -- record(when called from Update_ServiceRequest API)
265
266 -- party_id REQUIRED
267 -- Valid party id from HZ_PARTIES
268
269 -- contact_point_id OPTIONAL
270 -- VAlid contact_point_id from HZ_CONTACT_POINTS
271
272 -- contact_point_type OPTIONAL
273 -- From AR_LOOKUPS where lookup_type = 'COMMUNICATION_TYPE'
274 -- and 'PHONE_LINE_TYPE'
275
276 -- primary_flag OPTIONAL
277 -- At least one record in the table should have this flag set to Y
278
279 -- contact_type REQUIRED
280 -- Valid values are 'PERSON" and 'EMPLOYEE'
281
282 --------------------------------------------------------
283
284
285
286
287
288 PROCEDURE Create_ServiceRequest_Wrap
289 ( p_api_version IN NUMBER,
290 p_init_msg_list IN VARCHAR2 := null,
291 p_commit IN VARCHAR2 := null,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2,
295 p_resp_appl_id IN NUMBER := NULL,
296 p_resp_id IN NUMBER := NULL,
297 p_user_id IN NUMBER := NULL,
298 p_login_id IN NUMBER := NULL,
299 p_org_id IN NUMBER := NULL,
300 p_request_id IN NUMBER := NULL,
301 p_request_number IN VARCHAR2 := NULL,
302 p_service_request_rec IN service_request_rec_type,
303 p_notes IN notes_table,
304 p_contacts IN contacts_table,
305 x_request_id OUT NOCOPY NUMBER,
306 x_request_number OUT NOCOPY VARCHAR2,
307 x_interaction_id OUT NOCOPY NUMBER,
308 x_workflow_process_id OUT NOCOPY NUMBER
309 );
310
311
312 --------------------------------------------------------------------------
313 -- Start of comments
314 -- API name : Update_ServiceRequest_Wrap
315 -- Type : Public
316 -- Function : Calling CS_ServiceRequest_PUB.Update_ServiceRequest to
317 -- updates a service request in the table CS_INCIDENTS_ALL.
318 -- Pre-reqs : None.
319 --
320 -- Standard IN Parameters:
321 -- p_api_version IN NUMBER Required
322 -- p_init_msg_list IN VARCHAR2 Optional
323 -- p_commit IN VARCHAR2 Optional
324 --
325 -- Standard OUT Parameters:
326 -- x_return_status OUT VARCHAR2(1)
327 -- x_msg_count OUT NUMBER
328 -- x_msg_data OUT VARCHAR2(2000)
329 --
330 ---------------------------------------------------------------------
331 -- These are required only for create_servicerequest procedure
332 -- p_user_id IN NUMBER Optional
333 -- p_login_id IN NUMBER Optional
334 ------------------------------------------------------------------
335 --
336 -- Service Request IN Parameters:
337 -- p_resp_appl_id IN NUMBER Optional
338 -- p_resp_id IN NUMBER Optional
339
340
341
342 ----------------------------------------------------------------------
343 -- No longer there in the Update_ServiceRequest
344 -- p_org_id IN NUMBER Optional
345 ----------------------------------------------------------------------------
346
347
348 -- p_request_id IN NUMBER Optional
349 -- p_request_number IN VARCHAR2 Optional
350 -- p_object_version_number IN NUMBER Required by web-apps
351 --
352 --
353 -- p_audit_comments IN VARCHAR2 Optional
354 -- Maximum string length of 2000 bytes
355
356
357 -- p_last_updated_by IN NUMBER Required
358 -- valid user from fnd_user
359
360 -- p_last_update_login IN NUMBER Optional
361 -- Default = NULL
362 -- p_last_update_date IN DATE Required
363 --
364 -- p_service_request_rec IN service_request_rec_type Required
365 -- p_notes IN notes_table Optional
366
367 -- p_contacts IN contacts_table Optional
368 --
372 -- p_workflow_process_id IN NUMBER Optional
369 -- p_called_by_workflow IN VARCHAR2 Optional
370 -- Whether or not the API is being called by a workflow process
371
373 -- Workflow process ID of the active workflow process
374 --
375 --
376 -- Service Request OUT Parameters:
377 -- x_workflow_process_id OUT NUMBER
378 -- This will have a value if a new workflow got launched during the update
379 -- x_intercation_id OUT NUMBER
380
381 -------------------------------------------------------------------
382 -- Not present in the Procedure declaration
383 -- p_web_entry_flag
384 -- Whether the update was entered through the Web
385 -----------------------------------------------------------------
386 -- Calls IN parameters:
387 -- p_comments IN VARCHAR2(2000) Optional
388 -- Service request comments or log of the conversation.
389 -- p_public_comment_flag IN VARCHAR2(1) Optional
390 -- Indicate whether the service request comment is public (can be
391 -- viewed by anyone).
392 --
393 -- Calls OUT parameters:
394 -- p_call_id OUT NUMBER
395 -- System generated ID of service request call
396 --
397 -- Version : Initial version 1.0
398 ------------------------------------------------------------------------------
399
400 -- Start of comments
401 -- Record Type : Service_Request_Rec_Type
402 -- Description : Holds the Service Request attributes
403 -- for the Update_ServiceRequest Procedure.
404 -- Fields :
405 --
406 --
407 -- type_id NUMBER Optional
408 -- Must be non-null
409
410 -- type_name VARCHAR2 Optional
411 -- Must be non-null
412
413 -- status_id NUMBER Optional
414 -- Must be non-null
415
416 -- status_name VARCHAR2 Optional
417 -- Must be non-null
418
419 -- severity_id NUMBER Optional
420 -- Must be non-null
421
422 -- severity_name VARCHAR2 Optional
423 -- Must be non-null
424
425 -- urgency_id NUMBER Optional
426
427 -- urgency_name VARCHAR2 Optional
428
429 -- closed_date DATE Optional
430
431 -- owner_id NUMBER Optional
432 -- Must be non-null
433
434 -- owner_group_id NUMBER Optional
435 --
436 -- publish_flag VARCHAR2 Optional
437 -- Indicate whether the service request is published (entered
438 -- into knowledge base if ConText Server Option is enabled).
439
440 -- summary VARCHAR2 Optional
441 -- Must be non-null
442 -----------------------------------------------------------------------
443 -- These fields are no longer present in the record type
444 -- verify_request_flag VARCHAR2 Optional
445 -- Must be either FND_API.G_TRUE or FND_API.G_FALSE. Used
446 -- to indicate if this is a verified or non-verified request
447 -----------------------------------------------------------------------------
448 -- customer_id NUMBER Optional
449
450
451 -- customer_number VARCHAR2 Optional
452
453
454 -----------------------------------------------------------------------
455 -- These fields are no longer present in the record type
456
457 -- customer_prefix VARCHAR2(50) Optional
458 -- Service request customer name.
459 --
460 -- customer_firstname VARCHAR2(150) Optional
461 -- Service request customer first name
462
463 -- customer_lastname VARCHAR2(150) Optional
464 -- Service request customer last name
465
466
467 -- customer_company_name VARCHAR2(255) Optional
468 -- Service request customer company name
469
470 ----------------------------------------------------------------
471
472 -- employee_id NUMBER Optional
473
474 -- employee_number VARCHAR2(30) Optional
475 --
476
477 ------------------------------------------------------------------
478 -- These fiels are no longer there in rec type
479
480 --
481 -- contact01_id NUMBER Optional
482
483 -- contact01_prefix VARCHAR2(50) Optional
484 -- Service request customer contact name.
485
486 --
487 -- contact01_firstname VARCHAR2(150) Optional
488 --
489 --
490 -- contact01_lastname VARCHAR2(150) Optional
491 --
492 -- contact01_area_code VARCHAR2(10) Optional
493 -- contact01_telephone VARCHAR2(40) Optional
494 -- contact01_extension VARCHAR2(20) Optional
495 -- contact01_fax_area_code VARCHAR2(10) Optional
496 -- contact01_fax_number VARCHAR2(40) Optional
497 -- contact01_email_address VARCHAR2(2000) Optional
498 --
499 --
500 -- contact02_id NUMBER Optional
501 -- Service request customer represented by identifier
502
503 -- contact02_prefix VARCHAR2(50) Optional
504 -- Service request customer represented by prefix
505
506 -- contact02_firstname VARCHAR2(150) Optional
507 -- Service request customer represented by firstname
508
509 -- contact02_lastname VARCHAR2(150) Optional
513
510 -- Service request customer represented by lastname
511 --
512 -- contact02_area_code VARCHAR2(10) Optional
514 -- contact02_telephone VARCHAR2(40) Optional
515
516 -- contact02_extension VARCHAR2(20) Optional
517
518 -- contact02_fax_area_code VARCHAR2(10) Optional
519
520 -- contact02_fax_number VARCHAR2(40) Optional
521
522 -- contact02_email_address VARCHAR2(2000) Optional
523 -------------------------------------------------------------------
524
525 --
526 -- verify_cp_flag VARCHAR2 Optional
527
528 -- customer_product_id NUMBER Optional
529 --
530 ---------------------------------------------------
531 --no longer used
532 -- lot_num VARCHAR2(30) Optional
533 --------------------------------------------------------
534
535 -- Supporting platform_id again because of enh 1711552
536 -- platform_id NUMBER Optional
537 -- *********THE functionality for the below 2 fileds is no longer supported.
538 -- platform_version_id NUMBER Optional
539
540 -- language_id NUMBER Optional
541 -- This is the Product's language id
542
543 -- cp_component_id NUMBER Optional
544 -- cp_component_version_id NUMBER Optional
545 -- cp_subcomponent_id NUMBER Optional
546 -- cp_subcomponent_version_id NUMBER Optional
547
548
549 -- language VARCHAR2(4) Optional
550 -- This is used for TL tables
551 -- IF not passed to the api, the userenv('LANG') is used.
552 --
553
554 --
555 -- cp_ref_number NUMBER Optional
556 --
557 -- inventory_item_id NUMBER Optional
558
559 -- inventory_item_conc_segs VARCHAR2 Optional
560 -- inventory_item_segment1 VARCHAR2 Optional
561 -- inventory_item_segment2 VARCHAR2 Optional
562 -- inventory_item_segment3 VARCHAR2 Optional
563 -- inventory_item_segment4 VARCHAR2 Optional
564 -- inventory_item_segment5 VARCHAR2 Optional
565 -- inventory_item_segment6 VARCHAR2 Optional
566 -- inventory_item_segment7 VARCHAR2 Optional
567 -- inventory_item_segment8 VARCHAR2 Optional
568 -- inventory_item_segment9 VARCHAR2 Optional
569 -- inventory_item_segment10 VARCHAR2 Optional
570 -- inventory_item_segment11 VARCHAR2 Optional
571 -- inventory_item_segment12 VARCHAR2 Optional
572 -- inventory_item_segment13 VARCHAR2 Optional
573 -- inventory_item_segment14 VARCHAR2 Optional
574 -- inventory_item_segment15 VARCHAR2 Optional
575 -- inventory_item_segment16 VARCHAR2 Optional
576 -- inventory_item_segment17 VARCHAR2 Optional
577 -- inventory_item_segment18 VARCHAR2 Optional
578 -- inventory_item_segment19 VARCHAR2 Optional
579 -- inventory_item_segment20 VARCHAR2 Optional
580
581 -- inventory_item_vals_or_ids VARCHAR2 Optional
582 -- Must be 'I' for IDs and 'V' for values. This parameter
583 -- indicate whether the item key flex segments are passed
584 -- in by ID or value
585 --
586 -- inventory_org_id NUMBER Optional
587
588 -- current_serial_number VARCHAR2 Optional
589 --
590
591 -- original_order_number NUMBER Optional
592
593 -- purchase_order_num VARCHAR2 Optional
594 --
595
596 ------------------------------------------------------------------
597 -- This field is no longer present in the record type
598 -- problem_description VARCHAR2 Optional
599 -- Maximum string length of 2000 bytes
600 -----------------------------------------------------------------------
601 --
602 -- problem_code VARCHAR2 Optional
603
604 -- exp_resolution_date DATE Optional
605
606 ------------------------------------------------------------------
607 -- This field is no longer present in the record type
608 -- make_public_problem VARCHAR2 Optional
609 -- Indicate whether the problem description is public.
610 ----------------------------------------------------------------------
611
612 -- install_site_use_id NUMBER Optional
613
614
615 -----------------------------------------------------------------------
616 -- These fields are no longer present in the record type
617 -- install_location VARCHAR2 Optional
618 --
619 -- install_customer VARCHAR2 Optional
620 --
621 -- install_country VARCHAR2(60) Optional
622
623 -- install_address_1 VARCHAR2 Optional
624 --
625 -- install_address_2 VARCHAR2 Optional
626 --
627 -- install_address_3 VARCHAR2 Optional
628 --
629 ------------------------------------------------------------------
630 -- These fields are no longer present in the record type
631 -- rma_flag VARCHAR2 Optional
632 -- rma_header_id NUMBER Optional
633 -- rma_number NUMBER Optional
634 -- order_type_id NUMBER Optional
635 -------------------------------------------------------------------------
636 --
637 -- request_segment1 VARCHAR2 Optional
638 -- request_segment2 VARCHAR2 Optional
639 -- request_segment3 VARCHAR2 Optional
640 -- request_segment4 VARCHAR2 Optional
641 -- request_segment5 VARCHAR2 Optional
645 -- request_segment9 VARCHAR2 Optional
642 -- request_segment6 VARCHAR2 Optional
643 -- request_segment7 VARCHAR2 Optional
644 -- request_segment8 VARCHAR2 Optional
646 -- request_segment10 VARCHAR2 Optional
647 -- request_segment11 VARCHAR2 Optional
648 -- request_segment12 VARCHAR2 Optional
649 -- request_segment13 VARCHAR2 Optional
650 -- request_segment14 VARCHAR2 Optional
651 -- request_segment15 VARCHAR2 Optional
652 -- request_context VARCHAR2 Optional
653 --
654 -- bill_to_site_use_id NUMBER Optional
655
656 -- bill_to_contact_id NUMBER Optional
657
658
659
660 -----------------------------------------------------------------------
661 -- These fields are no longer present in the record type
662
663 -- bill_to_location VARCHAR2 Optional
664
665 -- bill_to_customer VARCHAR2 Optional
666
667 -- bill_country VARCHAR2(60) Optional
668 --
669 -- bill_to_address_1 VARCHAR2 Optional
670
671 -- bill_to_address_2 VARCHAR2 Optional
672
673 -- bill_to_address_3 VARCHAR2 Optional
674
675 -- bill_to_contact VARCHAR2 Optional
676 ---------------------------------------------------------------------------
677 --
678 -- ship_to_site_use_id NUMBER Optional
679
680 -- ship_to_contact_id NUMBER Optional
681
682
683 -----------------------------------------------------------------------
684 -- These fields are no longer present in the record type
685
686 -- ship_to_location VARCHAR2 Optional
687
688 -- ship_to_customer VARCHAR2 Optional
689
690 -- ship_country VARCHAR2(60) Optional
691
692 -- ship_to_address_1 VARCHAR2 Optional
693
694 -- ship_to_address_2 VARCHAR2 Optional
695
696 -- ship_to_address_3 VARCHAR2 Optional
697
698 -- ship_to_contact VARCHAR2 Optional
699
700
701 ------------------------------------------------------------------
702 -- This field is no longer present in the record type
703 -- problem_resolution VARCHAR2 Optional
704 -- Maximum string length of 2000 bytes
705 --------------------------------------------------------------------
706
707 -- resolution_code VARCHAR2 Optional
708 -- act_resolution_date DATE Optional
709 -- public_comment_flag VARCHAR2(1) Optional
710 -- parent_interaction_id NUMBER Optional
711 -- contract_service_id NUMBER Optional
712 -- contract_service_number VARCHAR2(150) Optional
713 -- qa_collection_plan_id NUMBER Optional
714 -- account_id NUMBER Optional
715 -- resource_type VARCHAR2(30) Optional
716 -- resource_subtype_id NUMBER Optional
717 -- cust_po_number VARCHAR2(50) Optional
718 -- cust_ticket_number VARCHAR2(50) Optional
719 ---------------------------------------------------------------
720 -- This is a non updatable field
721 -- sr_creation_channel VARCHAR2(50) Optional
722 ------------------------------------------------------------------
723 -- obligation_date DATE Optional
724 -- time_zone_id NUMBER Optional
725 -- time_difference NUMBER Optional
726 -- site_id NUMBER Optional
727 -- customer_site_id NUMBER Optional
728 -- territory_id NUMBER Optional
729 -- initialize_flag VARCHAR2(1) Optional
730
731 -- cp_revision_id NUMBER OPTIONAL
732 -- inv_item_revision VARCHAR2(3) OPTIONAL
733 -- inv_component_id NUMBER OPTIONAL
734 -- inv_component_version VARCHAR2(3) OPTIONAL
735 -- inv_subcomponent_id NUMBER OPTIONAL
736 -- inv_subcomponent_version VARCHAR2(3) OPTIONAL
737 --
738 --
739 -- Notes: : Either request_id or request_number must be non-null.
740 -- If both are passed in, request_number will be ignored
741 --
742 -- If a field is not to be updated, do not pass in NULL. Either
743 -- don't pass in the parameter at all, or pass in one of the
744 -- missing parameter constants defined in the FND_API
745 -- package(G_MISS_...).
746 --
747 -- For all the "flag" parameters, and p_called_by_workflow
748 -- parameter, pass in the boolean constants defined in the
749 -- FND_API package (G_TRUE and G_FALSE).
750 --
751 -- Varchar parameters with the maximum length noted above will
752 -- be truncated if the length of the value being passed in
753 -- exceeds the maximum allowance, and a warning will be appended
754 -- to the runtime message list.
755 --
756 -- The type and owner of the the service request cannot be
757 -- updated when there is an active workflow process. The status
758 -- of the service request can be set to a "closed" status
759 -- (status whose close_flag is set) under the same condition.
760 -- In that case, this API will abort the active workflow
761 -- process. When this API is being called by the workflow
762 -- process, the caller must pass in the workflow process ID of
766 -- in the IDs for all columns that are used in the descriptive
763 -- the active workflow process for verification.
764 --
765 -- For the descriptive flexfield segments, the caller must pass
767 -- flexfield. Input by value is currently not supported.
768 --
769 -- The publish flag cannot be updated if the profile
770 -- 'Service: Publish Flag Update Allowed' is not set. If the
771 -- caller passes in a non-null value when the profile is not
772 -- set, the API will return an error.
773
774 -- The service request record must be committed before launching
775 -- the workflow process. This is necessary because Workflow needs
776 -- to obtain a lock on the record. If the caller passes in FALSE
777 -- for p_commit and TRUE for p_launch_workflow, the API will
778 -- return an error.
779 --
780 -- A workflow is automatically launched only if the caller passes
781 -- in TRUE for the p_launch_workflow parameter and the profile
782 -- option 'Service: Auto Launch Workflow' is set to 'Y'.
783 --
784 -- If p_launch_workflow is set, the Workflow API will try to lock
785 -- the service request record because it needs to update the
786 -- workflow_process_id column. The NOWAIT option can be specified
787 -- by setting the p_nowait parameter. If p_nowait is set and the
788 -- service request record is locked by another user, an error
789 -- status is returned via the p_return_status_wkflw parameter
790 -- indicating the workflow process is not launched.
791 --
792 -- If p_launch_workflow is set and the service request record is
793 -- created successfully, a success code will be returned via the
794 -- p_return_status parameter regardless of the result of the
795 -- workflow launch. The status code from the Workflow launch is
796 -- returned via the p_return_status_wkflw parameter instead.
797
798 --
799 -- End of comments
800 --
801 --------------------------------------------------------------------------
802
803 PROCEDURE Update_ServiceRequest_Wrap(
804 p_api_version IN NUMBER,
805 p_init_msg_list IN VARCHAR2 := null,
806 p_commit IN VARCHAR2 := null,
807 x_return_status OUT NOCOPY VARCHAR2,
808 x_msg_count OUT NOCOPY NUMBER,
809 x_msg_data OUT NOCOPY VARCHAR2,
810 p_request_id IN NUMBER := NULL,
811 p_request_number IN VARCHAR2 := NULL,
812 p_audit_comments IN VARCHAR2 := NULL,
813 p_object_version_number IN NUMBER,
814 p_resp_appl_id IN NUMBER := NULL,
815 p_resp_id IN NUMBER := NULL,
816 p_last_updated_by IN NUMBER,
817 p_last_update_login IN NUMBER := NULL,
818 p_last_update_date IN DATE,
819 p_service_request_rec IN service_request_rec_type,
820 p_notes IN notes_table,
821 p_contacts IN contacts_table,
822 p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
823 p_workflow_process_id IN NUMBER := NULL,
824 x_workflow_process_id OUT NOCOPY NUMBER,
825 x_interaction_id OUT NOCOPY NUMBER
826 );
827
828 PROCEDURE initialize_rec(
829 p_sr_record IN OUT NOCOPY service_request_rec_type
830 );
831
832 PROCEDURE Update_Status_Wrap
833 ( p_api_version IN NUMBER,
834 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
835 p_commit IN VARCHAR2 := FND_API.G_FALSE,
836 x_return_status OUT NOCOPY VARCHAR2,
837 x_msg_count OUT NOCOPY NUMBER,
838 x_msg_data OUT NOCOPY VARCHAR2,
839 p_resp_appl_id IN NUMBER := NULL,
840 p_resp_id IN NUMBER := NULL,
841 p_user_id IN NUMBER := NULL,
842 p_login_id IN NUMBER := FND_API.G_MISS_NUM,
843 p_request_id IN NUMBER := NULL,
844 p_request_number IN VARCHAR2 := NULL,
845 p_object_version_number IN NUMBER,
846 p_status_id IN NUMBER := NULL,
847 p_status IN VARCHAR2 := NULL,
848 p_closed_date IN DATE := FND_API.G_MISS_DATE,
849 p_audit_comments IN VARCHAR2 := NULL,
850 p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
851 p_workflow_process_id IN NUMBER := NULL,
852 p_comments IN VARCHAR2 := NULL,
853 p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
854 x_interaction_id OUT NOCOPY NUMBER
855 );
856
857 PROCEDURE IEM_CREATE_SR
858 ( p_api_version IN NUMBER,
859 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
863 p_party_id IN NUMBER,
860 p_commit IN VARCHAR2 := FND_API.G_FALSE,
861 p_message_id IN NUMBER,
862 p_note IN VARCHAR2,
864 p_sr_type_id IN NUMBER,
865 p_subject IN VARCHAR2,
866 p_employee_flag IN VARCHAR2,
867 p_note_type IN VARCHAR2,
868 p_contact_id IN NUMBER := NULL,
869 p_contact_point_id IN NUMBER := NULL,
870 x_return_status OUT NOCOPY VARCHAR2,
871 x_msg_count OUT NOCOPY NUMBER,
872 x_msg_data OUT NOCOPY VARCHAR2,
873 x_request_id OUT NOCOPY NUMBER
874 );
875
876 END;