[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Commented out UpdateStatus, UpdateUrgency, |
| CreateTask, and GetContract. |
| 03/01/2002 klou, added error code |
| 13/03/2002 klou, add logic to handle employee logging SRs |
| NOTE: This version is based on 115.81. 115.82 inlcudes |
| multiple notes and has been leapfrogged. |
| 18/03/2002 klou, |
| 1. add p_serial_number to create_service_request. |
| 2. add logic to fetch serial number and tag number |
| from install base table. |
| 25-MAR-2002 allau |
| Remove assignment manager logic to leverage TeleService |
| auto-assignment feature |
| 29-MAR-2002 ALLAU |
| Pass LAST_UPDATE_CHANNEL := 'WEB' when calling |
| TeleService CreateServiceRequest and |
| UpdateServiceRequest APIs. |
| 31-MAR-2002 KLOU (UCONTACT) |
| Add new parameters to UpdateServiceRequest to hanlde |
| update contacts in SR detail. |
| 11-APR-2002 KLOU (ASSG) |
| SR creation: add logic to check whether CS |
| Auto-Assignment is ON. If not, check whether IBU profile|
| of "use default resource owner" is ON. If yes, fecth |
| owner group, owner id, group type from profile. |
| Otherwise, do noting. |
| 15-APR-2002 KLOU (PLANG) |
| Set perferred language to appropriate column, i.e. not |
| use attribute6. |
| 25-MAY-2002 WMA |
| Add the SR location address information to the create |
| API. |
| 31-MAY-2002 KLOU |
| Set category id to null if it is -1 during SR creation. |
| 20-AUG-2002 WMA |
| Get the default SR owner type for the SR creation |
| 17-OCT-2002 WMA |
| 1. modified the create API according to CS change |
| 2. add five more parameters for bill to and ship to |
| 01-NOV-2002 WMA |
| 1. add the attachment category ID |
| 06-NOV-2002 SPOLAMRE |
| Add DFF attributes |
| 13-NOV-2002 WMA |
| Check the DEF parameters null case |
| 115.100 06-DEC-2002 WZLI changed OUT and IN OUT calls to use NOCOPY |
| hint to enable pass by reference. |
| changed the api version from 2.0 to 3.0. |
| added two parameters: p_bill_to_party_id |
| and p_ship_to_party_id in the create |
| service request procedure. |
| 02-Jan-2002 WMA add one more API get_default_status API |
| add the responsibility id to pass in |
| 31-Jan-2003 SPOLAMRE |
| Changed the PROCEDURE AddAttachment to take file name as|
| parameter and pass it to the API |
| FND_WEBATTCH.ADD_ATTACHMENT |
| 115.106 11-SEP-2003 wzli Made changes for create SR for 11.5.10 |
| 115.107 15-SEP-2003 WZLI Change the location type to |
| HZ_PARTY_SITE |
| 115.108 09-OCT-2003 WZLI added procedure decodeErrorMsg |
| 115.109 10-OCT-2003 WZLI Remove debug message from decodeErrorMsg|
| 115.110 12-OCT-2003 WMA change the update API for 11510 requirement|
| 115.111 20-OCT-2003 wzli added two parameterss: p_street_number and|
| p_timezone_id in the create SR procedure. |
| 115.112 19-NOV-2003 wzli Fixed problem(bug#3063305): 'No entries |
| found for List of Values error when trying|
| to query the Item field only for SR's |
| created via iSupport. |
| 115.113 03-DEC-2003 WZLI add logic to decode message |
| FORM_RECORD_CHANGED |
| 115.114 15-DEC-2003 WZLI Because the error messages in the error |
| stack are separated by chr(0), they need |
| to be parsed befor we decode them. |
| 115.115 29-DEC-2003 WZLI Made change to code logic: If installed |
| base product is selected, don't save the |
| serial number. |
| 115.116 09-DEC-2003 WMA modify the Email logic, increase the size |
| of Email body. |
| 115.117 19-Jan-2003 WMA fixed bug 3377241. |
| AUTO ASSIGNMENT ON UPDATE THRU I-SUPPORT |
| NOT FUNCTIONING |
| 115.118 27-JAN-2004 mkcyee In AddAttachment, the function name |
| parameter should be obtained from the |
| profile and not hard-coded to CSXSRISR |
| 115.119 10-MAR-2004 WZLI added parameter: p_note_status. |
| 115.120 16-APR-2004 WZLI When creating sr, pass sysdate to |
| parameter incident_occurred_date. |
| 115.121 09-JUN-2004 WZLI Fixed problem(bug#3676419):In procedure |
| get_default_status(),select status_group_id|
| from cs_incident_types_b instead of from |
| cs_incident_types_vl |
| 115.122 29-JUL-2004 WZLI Fixed problem(bug#3796975):Saving Service |
| request gives invalid date error. |
| 115.123 15-OCT-2004 WMA for sendEmail function, use userID |
| fullname as role display name. |
| 115.124 28-NOV-2004 WMA add the srID in the Send Email API. |
| change the way to start work flow. |
| add new API startEmailWorkFlow(). |
| 115.125 29-NOV-2004 WMA change the way to create role for Email. |
| 115.126 30-NOV-2004 WMA change the rolename. |
| 120.1 04-AUG-2005 WMA added pending approval flag checking for |
| default statuses. |
| 120.2 09-SEP-2005 WMA add the logic to handle the multi bytes |
| 120.3 28-NOV-2005 WZLI made change for link object enhancement. |
| 120.4 28-NOV-2005 WZLI Fixed GSCC error. |
| 120.5 10-DEC-2005 WMA add validate_http_service_ticket() |
| change the logic for workflow APIs. |
| 120.6 19-JAN-2006 WMA change the sequence name for the WF role |
| 120.7 30-JAN-2006 WMA modify the logic for creating emp role. |
| 120.9 14-Nov-2007 MPATHANI Profile 'Service : Default Group Owner|
| Type for Service Request' is obsoleted, |
| added 'RS_GROUP'. |
| 120.10 11-FEB-2009 mkundali added for 12.1.2 enhancement bug8245975|
| 120.11.12010000.4 12-Jan-11 MPATHANI Fix bug# 10176330- Increased |
| the length of Contact Point Value to 300 |
| 120.11.12010000.11-Jan-2011 dkumbhat Fix Bug 9299378 |
| 120.11.12010000.12-Jan-2011 dkumbhat Fix Bug 9453854 |
| 120.11.12010000.13 17-Jan-2011 dkumbhat Fix Bug 9503707 |
| 120.5.12010000.6 1-March-2011 dkumbhat 11815997 INTRODUCE PUBLISH FLAG IN ISUPPORT.|
| 120.20 12-Dec-2011 lkadamba Fix for Bug 12681852 (ISUPPORT PROJECTS|
| INTEGRATION FUNCTIONALITY) |
| 120.20 23-Dec-2011 lkadamba Additional Changes for Bug 12681852 |
| (ISUPPORT PROJECTS INTEGRATION |
| FUNCTIONALITY). |
+======================================================================*/
/**
* UpdateServiceRequest
*/
PROCEDURE UpdateServiceRequest(
p_request_id IN NUMBER,
p_status_id IN NUMBER,
p_urgency_id IN NUMBER,
p_problem_description IN VARCHAR2,
p_problem_detail IN VARCHAR2,
p_note_type IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_language IN VARCHAR2,
-- UCONTACT
p_contact_party_id IN JTF_NUMBER_TABLE := null,
p_contact_type IN JTF_VARCHAR2_TABLE_100 := null,
p_contact_point_id IN JTF_NUMBER_TABLE := null,
p_contact_point_type IN JTF_VARCHAR2_TABLE_100 := null,
p_contact_primary IN JTF_VARCHAR2_TABLE_100 := null,
p_sr_contact_point_id IN JTF_NUMBER_TABLE := null,
-- DONE
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_msg_count NUMBER;
l_last_update_date DATE := SYSDATE;
select party_type
from hz_parties
where party_id = p_party_id;
l_sr_update_out_rec CS_ServiceRequest_PVT.sr_update_out_rec_type;
select OBJECT_VERSION_NUMBER
into l_object_version_number
from CS_INCIDENTS_ALL_B
where INCIDENT_ID = p_request_id;
l_sr_rec.last_update_channel := 'WEB';
l_sr_rec.last_update_program_code := 'ISUPPORTSRUI';
select fnd_global.resp_id into l_responsibility_id from dual;
Select nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_HTML'), 'N')
Into l_cs_auto_assignment From dual;
CS_SERVICEREQUEST_PVT.UPDATE_SERVICEREQUEST(
p_api_version => 4.0,
p_init_msg_list => fnd_api.g_true,
p_resp_id => l_responsibility_id,
p_commit => fnd_api.g_true,
p_request_id => p_request_id,
p_object_version_number => l_object_version_number,
p_last_updated_by => p_last_updated_by,
p_last_update_date => l_last_update_date,
p_service_request_rec => l_sr_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
p_validate_sr_closure => l_validate_sr_closure,
p_auto_close_child_entities => l_auto_close_child_entities,
x_msg_count => x_msg_count,
p_auto_assign =>l_cs_auto_assignment,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_sr_update_out_rec => l_sr_update_out_rec);
/* CS_SERVICEREQUEST_PVT.UPDATE_SERVICEREQUEST(
p_api_version => 4.0,
p_init_msg_list => fnd_api.g_true,
p_resp_id => l_responsibility_id,
p_commit => fnd_api.g_true,
p_request_id => p_request_id,
p_object_version_number => l_object_version_number,
p_last_updated_by => p_last_updated_by,
p_last_update_date => l_last_update_date,
p_service_request_rec => l_sr_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
-- p_validate_sr_closure => l_validate_sr_closure,
-- p_auto_close_child_entities => l_auto_close_child_entities,
x_msg_count => x_msg_count,
x_workflow_process_id => l_workflow_process_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_interaction_id => x_interaction_id); */
END UpdateServiceRequest;
select NVL(max(seq_num),0) + 10
into seq_num
from fnd_attached_documents
where entity_name = 'CS_INCIDENTS'
and pk1_value = p_request_id;
select party_type
from hz_parties
where party_id = p_party_id;
select serial_number, external_reference
from csi_item_instances
where instance_id = p_instance_id;
l_last_update_date DATE := SYSDATE;
l_select_id varchar2(200);
l_sr_rec.request_date := l_last_update_date;
l_sr_rec.incident_occurred_date := l_last_update_date;
SELECT c.category_id into category_id
FROM mtl_category_set_valid_cats ic,
mtl_categories_kfv c,
mtl_category_sets_vl cs,
mtl_item_categories itc,
mtl_categories_tl t
WHERE ic.category_id = c.category_id
AND t.category_id(+) = c.category_id
AND t.LANGUAGE(+) = userenv('LANG')
AND ic.category_set_id = cs.category_set_id
AND ic.category_set_id = cat_set_id
AND itc.inventory_item_id = p_inventory_item
AND itc.category_id = c.category_id
AND itc.category_set_id = ic.category_set_id
AND itc.organization_id = cat_org_id
AND sysdate <= nvl(c.disable_date, sysdate)
ORDER BY c.concatenated_segments;
Select nvl(fnd_profile.value('CS_AUTO_ASSIGN_OWNER_HTML'), 'N')
Into l_cs_atuo_assignment From dual;
Select nvl(fnd_profile.value('IBU_R_ASSIGNMENT_USED_OWNERS'), 'Y')
Into l_ibu_assignment From dual;
/* Select fnd_profile.value('CS_SR_DEFAULT_GROUP_TYPE')
* Into l_sr_rec.group_type From dual;
Select fnd_profile.value('CS_SR_DEFAULT_GROUP_OWNER')
Into l_sr_rec.owner_group_id From dual;
Select fnd_profile.value('INC_DEFAULT_INCIDENT_OWNER')
Into l_sr_rec.owner_id From dual;
Select fnd_profile.value('CS_SR_DEFAULT_OWNER_TYPE')
into l_sr_rec.resource_type from dual;
l_sr_rec.last_update_channel := 'WEB';
l_sr_rec.last_update_program_code := 'ISUPPORTSRUI';
select fnd_global.resp_id, fnd_global.resp_appl_id, fnd_global.user_id, fnd_global.login_id
into l_responsibility_id, l_resp_appl_id, l_user_id, l_login_id
from dual;
x_select_id => l_select_id,
x_from_table => l_from_table,
x_where_clause => l_where_clause,
x_object_count => l_ref_object_count);
p_select_id => l_select_id,
p_from_table => l_from_table,
p_where_clause => l_where_clause,
x_object_count => l_ref_object_count);
select name from wf_roles
where orig_system = 'HZ_PARTY'
and orig_system_id = p_contact_id
and email_address = p_email_address;
select notification_preference, name from wf_roles
where orig_system = 'PER'
and orig_system_id = p_contact_id
and email_address = p_email_address;
SELECT
TypeMapping.status_group_id
FROM
cs_sr_type_mapping TypeMapping
WHERE
incident_type_id = p_type_id AND
responsibility_id = p_resp_id AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(TypeMapping.start_date, SYSDATE)) AND
TRUNC(NVL(TypeMapping.end_date, SYSDATE));
SELECT status_group_id
FROM cs_incident_types_b
WHERE incident_type_id = p_type_id AND
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE)) AND
TRUNC(NVL(end_date_active, SYSDATE));
select default_incident_status_id
from cs_sr_status_groups_b
where status_group_id = p_status_group_id
and TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date, SYSDATE)) AND
TRUNC(NVL(end_date, SYSDATE));
select csIncidentStatus.incident_status_id
from CS_SR_ALLOWED_STATUSES allowedStatus,
CS_INCIDENT_STATUSES_B csIncidentStatus
where
allowedStatus.status_group_id = p_status_group_id and
allowedStatus.incident_status_id =
csIncidentStatus.incident_status_id and
csIncidentStatus.valid_in_create_flag = 'Y' and
csIncidentStatus.incident_subtype = 'INC' and
(csIncidentStatus.pending_approval_flag is null or
csIncidentStatus.pending_approval_flag = 'N') and
TRUNC(SYSDATE) BETWEEN TRUNC(NVL(allowedStatus.start_date, SYSDATE))
AND
TRUNC(NVL(allowedStatus.end_date, SYSDATE)) AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(csIncidentStatus.start_date_active, SYSDATE)) AND
TRUNC(NVL(csIncidentStatus.end_date_active, SYSDATE)) and
rownum <=1 ;
select fnd_global.resp_id into l_responsibility_id from dual;
FND_MSG_PUB.Delete_Msg(l_msg_index_out);
FND_MSG_PUB.Delete_Msg(l_msg_index_out);
FND_MESSAGE.SET_NAME('IBU', 'IBU_SR_CANNOT_UPDATE_SR');
get_seq varchar2(50) := 'select ' || 'IBU_WF_ITEM_KEY_S' || '.nextval from dual';
select user into l_user from dual;
/* select count(*) into cnt from all_objects
where object_name like 'IBU_SR_NOTIFICATION_S'
and object_type = 'SEQUENCE'
and owner = l_user; */
x_select_id out NOCOPY varchar2,
x_from_table out NOCOPY varchar2,
x_where_clause out NOCOPY varchar2,
x_object_count out NOCOPY number
) as
l_ref_object_code varchar2(30) := p_ref_object_code;
select object_code, select_id, from_table, where_clause
into l_ref_object_value, x_select_id, x_from_table, x_where_clause
from jtf_objects_b
where sysdate between nvl(start_date_active, sysdate) and nvl(end_date_active, sysdate)
and object_code = l_ref_object_code;
p_select_id in varchar2,
p_from_table in varchar2,
p_where_clause in varchar2,
x_object_count out NOCOPY number
) as
l_ref_object_id number := p_ref_object_id;
l_select_statement varchar2(3500);
l_select_statement := 'select count(*) from ' || p_from_table || ' where ' || p_where_clause || ' and ' || p_select_id || ' = :p1 and rownum < 2 ';
l_select_statement := 'select count(*) from ' || p_from_table || ' where ' || p_select_id || ' = ' || l_ref_object_id;
execute immediate l_select_statement into x_object_count;