The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT INSTALL_SITE_USE_ID
FROM CS_INCIDENTS_ALL_VL
WHERE INCIDENT_ID = l_request_id;
SELECT ADDRESS1, ADDRESS2, CITY, STATE, COUNTRY, POSTAL_CODE
FROM HZ_LOCATIONS WHERE LOCATION_ID = l_install_site_use_id;
SELECT incident_location_id, incident_address, incident_city, incident_state,
incident_postal_code, incident_country
FROM cs_incidents_all_b
WHERE incident_id = l_request_id;
SELECT address, city, state, postal_code, country
FROM cs_sr_incident_address_v
WHERE location_id = l_incident_location_id;
select INCIDENT_TYPE_ID, SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = l_incident_type_id;
SELECT incidnt_attr_val_id from CUG_INCIDNT_ATTR_VALS_VL
WHERE INCIDENT_ID = l_request_id;
SELECT tsk_typ_attr_dep_id FROM CUG_TSK_TYP_ATTR_DEPS_B
WHERE incident_type_id = l_incident_type_id;
SELECT incident_type_id into l_incident_type_id FROM CS_INCIDENTS_ALL_B WHERE
incident_id = l_request_id;
select resource_id FROM CUG_SR_TYPE_DUP_CHK_INFO
WHERE incident_type_id = l_incident_type_id;
select resource_name, resource_type from cs_sr_owners_v where
resource_id = l_default_owner_id and
resource_type = 'RS_EMPLOYEE';
select incident_owner_id, owner_group_id, org_id from
cs_incidents_all_b where
incident_id = l_request_id;
select source_id from jtf_rs_resource_extns where
resource_id = l_default_owner_id;
SELECT person_id FROM per_people_x
WHERE full_name = l_GetResourceName_rec.resource_name AND employee_number IS NOT NULL;
select resource_id from jtf_rs_group_members_vl where
group_id = l_default_owner_id;
select team_resource_id from jtf_rs_team_members_vl where
team_id = l_default_owner_id;
select INCIDENT_TYPE_ID into l_incident_type_id FROM
CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;
*) if the current sr already has the group_id filled, do NOT update the inci
dent_owner id
*) if the current sr does not have the gorup is filled , but individual owne
r id filled - do not update the incident_owner id
*) if the current sr has no group and no current sr - update the incident_ow
ner id col with resp party id
*/
OPEN l_GetResourceInfo_csr;
SELECT DISTINCT OVERRIDE_ADDR_VALID_FLAG FROM CUG_INCIDNT_ATTR_VALS_B
WHERE INCIDENT_ID = l_request_id;
select SR_DUP_CHECK_FLAG from CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = l_request_type_id;
select INCIDENT_TYPE_ID into l_request_type_id from CS_INCIDENTS_ALL_VL
WHERE INCIDENT_ID = l_request_id;
select ADDRESS1, ADDRESS2, CITY, STATE, POSTAL_CODE, COUNTRY FROM
HZ_LOCATIONS where LOCATION_ID = l_install_at_site_id;
SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INSTALL_SITE_USE_ID AND
HZ_LOCATIONS.ADDRESS1 = l_IncidentAddress_rec.Address1 AND
nvl(HZ_LOCATIONS.ADDRESS2, 'Not Filled') = nvl(l_IncidentAddress_rec.Address2, 'Not Filled') AND
nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_IncidentAddress_rec.City, 'Not Filled') AND
nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_IncidentAddress_rec.State, 'Not Filled') AND
nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') = nvl(l_IncidentAddress_rec.Postal_Code, 'Not Filled') AND
-- HZ_LOCATIONS.COUNTRY = l_IncidentAddress_rec.Country AND
CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id;
SELECT incident_location_id, incident_address, incident_city, incident_state,
incident_postal_code, incident_country
FROM cs_incidents_all_b
WHERE incident_id = l_request_id;
SELECT address, city, state, postal_code, country
FROM cs_sr_incident_address_v
WHERE location_id = l_incident_location_id;
SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_VL, HZ_LOCATIONS
WHERE HZ_LOCATIONS.LOCATION_ID = CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID AND
nvl(HZ_LOCATIONS.ADDRESS1, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
nvl(HZ_LOCATIONS.CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
nvl(HZ_LOCATIONS.STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
nvl(HZ_LOCATIONS.POSTAL_CODE, 'Not Filled') = nvl(l_incident_postal_Code, 'Not Filled') AND
nvl(HZ_LOCATIONS.COUNTRY, 'Not Filled') = nvl(l_incident_country, 'Not Filled') AND
CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id
UNION
SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_VL
WHERE nvl(incident_ADDRESS, 'Not Filled') = nvl(l_incident_address, 'Not Filled') AND
nvl(incident_CITY, 'Not Filled') = nvl(l_incident_city, 'Not Filled') AND
nvl(incident_STATE, 'Not Filled') = nvl(l_incident_state, 'Not Filled') AND
nvl(incident_POSTAL_CODE, 'Not Filled') = nvl(l_incident_postal_Code, 'Not Filled') AND
nvl(incident_COUNTRY, 'Not Filled') = nvl(l_incident_country, 'Not Filled') AND
LAST_UPDATE_DATE > l_duplicate_date AND
INCIDENT_TYPE_ID = l_request_type_id AND
INCIDENT_ID <> l_request_id;
SELECT CS_INCIDENTS_ALL_VL.INCIDENT_ID,CS_INCIDENTS_ALL_VL.INCIDENT_NUMBER
FROM CS_INCIDENTS_ALL_VL
WHERE CS_INCIDENTS_ALL_VL.LAST_UPDATE_DATE > l_duplicate_date AND
CS_INCIDENTS_ALL_VL.INCIDENT_TYPE_ID = l_request_type_id AND
CS_INCIDENTS_ALL_VL.INCIDENT_ID <> l_request_id AND
CS_INCIDENTS_ALL_VL.INCIDENT_LOCATION_ID is NULL AND
CS_INCIDENTS_ALL_VL.incident_ADDRESS is NULL AND
CS_INCIDENTS_ALL_VL.incident_CITY is NULL AND
CS_INCIDENTS_ALL_VL.incident_STATE is NULL AND
CS_INCIDENTS_ALL_VL.incident_POSTAL_CODE is NULL AND
CS_INCIDENTS_ALL_VL.incident_COUNTRY is NULL;
select SR_ATTRIBUTE_CODE from CUG_SR_TYPE_ATTR_MAPS_VL
where INCIDENT_TYPE_ID = l_request_type_id AND
SR_ATTR_DUP_CHECK_FLAG = 'Y' AND
( END_DATE_ACTIVE IS NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
incident_id = l_request_id;
SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
incident_id = l_IncidentId_rec.Incident_Id;
SELECT sr_attribute_value FROM cug_incidnt_attr_vals_vl WHERE
sr_attribute_code = l_DuplicateCheckAttrs_rec.sr_attribute_code AND
incident_id = l_IncidentId_noLoc_rec.Incident_Id;
select INSTALL_SITE_USE_ID into l_install_at_site_id
from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;
select INCIDENT_TYPE_ID into l_request_type_id
from CS_INCIDENTS_ALL_VL where INCIDENT_ID = l_request_id;
l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
|| l_DuplicateCheckAttrs_rec.sr_attribute_code || ' and incident_id = :incident_id';
l_sql_stmt := 'select sr_attribute_value from cug_incidnt_attr_vals_vl where sr_attribute_code = '
|| l_DuplicateCheckAttrs_rec.sr_attribute_code || ' and incident_id = :incident_id';
PROCEDURE UPDATE_DUPLICATE_INFO(
itemtype VARCHAR2,
itemkey VARCHAR2,
actid NUMBER,
funmode VARCHAR2,
result OUT NOCOPY VARCHAR2 )
IS
l_request_id NUMBER;
l_last_updated_by NUMBER(15):= FND_GLOBAL.USER_ID;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
SELECT jtf_note_id from JTF_NOTES_B WHERE
note_type = 'CUG_SR_ATTR_DETAILS' AND
source_object_code = 'SR' AND
source_object_id = l_request_id;
select INCIDENT_TYPE_ID into l_request_type_id FROM
CS_INCIDENTS_ALL_VL WHERE INCIDENT_ID = l_request_id;
select RESOURCE_ID into l_default_owner_id FROM CUG_SR_TYPE_DUP_CHK_INFO
WHERE Incident_Type_ID = l_request_type_id;
p_last_update_date => sysdate,
p_last_updated_by => l_default_owner_id,
p_creation_date => sysdate,
p_note_type => 'Duplicate Service Request'
);
p_last_update_date => sysdate,
p_last_updated_by => l_default_owner_id,
p_creation_date => sysdate,
p_note_context_type_id => l_note_context_id,
p_note_context_type => 'Duplicate',
x_note_context_id => l_note_context_id);
SELECT jtf_notes_s.NEXTVAL INTO l_note_context_id FROM dual;
INSERT INTO JTF_NOTE_CONTEXTS(
NOTE_CONTEXT_ID,
JTF_NOTE_ID,
NOTE_CONTEXT_TYPE_ID,
NOTE_CONTEXT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
VALUES
(l_note_context_id,
l_note_id,
l_note_context_type_id,
'CUG_DUPLICATE_SR',
sysdate,
l_last_updated_by,
sysdate,
l_created_by,
l_last_update_login);
WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
itemtype, itemkey, actid, funmode);
WF_CORE.Context('CUG_GENERIC_WF_PKG', 'UPDATE_DUPLICATE_INFO',
itemtype, itemkey, actid, funmode);
END UPDATE_DUPLICATE_INFO;
/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/* added the following parameter */
l_request_number VARCHAR2(64);
/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
l_planned_start_date date;
SELECT * FROM CUG_TSK_TYP_ATTR_DEPS_VL WHERE INCIDENT_TYPE_ID = l_request_type_id AND
(START_DATE_ACTIVE IS NULL OR
to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
(END_DATE_ACTIVE is NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT * FROM CUG_SR_TASK_TYPE_DETS_VL WHERE
TSK_TYP_ATTR_DEP_ID = l_tsk_typ_attr_dep_id;
SELECT SR_ATTRIBUTE_VALUE FROM CUG_INCIDNT_ATTR_VALS_VL WHERE
INCIDENT_ID = l_request_id AND
SR_ATTRIBUTE_CODE = l_sr_attribute_code;
SELECT workflow FROM JTF_TASK_TYPES_B
WHERE task_type_id = l_task_type_id;
SELECT lookup_code from FND_LOOKUP_VALUES where
description = l_sr_attribute_value;
/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
l_request_number := WF_Engine.GetItemAttrText(itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUEST_NUMBER');
/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
SELECT INCIDENT_TYPE_ID into l_request_type_id FROM CS_INCIDENTS_ALL_VL WHERE
INCIDENT_ID = l_request_id;
/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/* Uncommented the following 3 lines */
p_source_object_type_code => 'SR',
p_source_object_id => l_request_id,
p_source_object_name => l_request_number,
/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
p_scheduled_start_date => sysdate,
p_scheduled_end_date => sysdate,
p_private_flag => l_SRTasks_Details_rec.private_flag,
p_publish_flag => l_SRTasks_Details_rec.publish_flag,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_task_id => l_task_id
);
/* Begin - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
/* commenting out the following section */
/*
update jtf_tasks_b set
source_object_id = l_request_id,
source_object_type_code = 'SR' where
task_id = l_task_id;
/* End - 09/24/2001 - change made to accomodate Rahul's problem with update_task API */
l_task_type_id := l_SRTasks_rec.task_type_id;
SELECT tsk.task_number, tsk.task_id, tsk.task_type_id, sts.name FROM JTF_TASKS_VL tsk, JTF_TASK_STATUSES_VL sts WHERE
tsk.task_status_id = sts.task_status_id and
tsk.SOURCE_OBJECT_ID = l_request_id and
tsk.source_object_type_code = 'SR';
SELECT cst.owner_id from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta WHERE
cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
cta.task_type_id = l_task_type_id AND
cta.incident_type_id = l_request_type_id;
SELECT source_name from JTF_RS_RESOURCE_EXTNS WHERE
resource_id = l_task_owner_id;
SELECT cst.owner_id, cst.owner_type_code
from CUG_SR_TASK_TYPE_DETS_B cst, CUG_TSK_TYP_ATTR_DEPS_B cta
WHERE cst.tsk_typ_attr_dep_id = cta.tsk_typ_attr_dep_id AND
cta.task_type_id = l_task_type_id AND
cta.incident_type_id = l_request_type_id;
SELECT resource_id from JTF_RS_RESOURCE_EXTNS WHERE
resource_id = l_task_owner_id;
SELECT incident_type_id into l_request_type_id from CS_INCIDENTS_ALL_B where incident_id = l_request_id;
SELECT incident_type_id FROM CS_INCIDENTS_ALL_VL
WHERE INCIDENT_ID = l_request_id;
SELECT duplicate_offset, duplicate_uom FROM CUG_SR_TYPE_DUP_CHK_INFO
WHERE INCIDENT_TYPE_ID = l_request_type_id;
SELECT conversion_rate FROM MTL_UOM_CONVERSIONS
WHERE UNIT_OF_MEASURE = l_duplicate_uom;
SELECT jtf_task_workflow_process_s.nextval
FROM dual;
SELECT task_name, description, owner_type_code owner_code, owner_id, task_number
FROM jtf_tasks_v
WHERE task_id = p_task_id;
select task_status, task_priority , task_type
into l_task_status_name, l_task_priority_name , l_task_type_name
from jtf_tasks_v where task_id = p_task_id ;
SELECT task_type_id
FROM jtf_task_types_vl
WHERE task_type_id = p_task_type_id
AND (START_DATE_ACTIVE IS NULL OR
to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
(END_DATE_ACTIVE is NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT task_priority_id
FROM jtf_task_priorities_b
WHERE task_priority_id = p_task_priority_id
AND (START_DATE_ACTIVE IS NULL OR
to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
(END_DATE_ACTIVE is NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT task_status_id
FROM jtf_task_statuses_b
WHERE task_status_id = p_task_status_id
AND (START_DATE_ACTIVE IS NULL OR
to_number(to_char(START_DATE_ACTIVE, 'YYYYMMDD')) <= to_number(to_char(sysdate, 'YYYYMMDD')) ) AND
(END_DATE_ACTIVE is NULL OR
to_number(to_char(END_DATE_ACTIVE, 'YYYYMMDD')) >= to_number(to_char(sysdate, 'YYYYMMDD')) );
SELECT name into l_task_name
FROM jtf_task_types_vl
WHERE task_type_id = p_task_type_id;
PROCEDURE Update_CIC_Request_Info ( itemtype VARCHAR2,
itemkey VARCHAR2,
actid NUMBER,
funmode VARCHAR2,
result OUT NOCOPY VARCHAR2 ) IS
l_return_status VARCHAR2(1);
SELECT inc.customer_product_id, inc.expected_resolution_date,inc.inventory_item_id,inc.summary, emp.source_id incident_owner_id
FROM cs_incidents_all_vl inc ,cs_sr_owners_v owner, jtf_rs_resource_extns emp
WHERE inc.INCIDENT_OWNER_ID = owner.resource_id(+) AND
emp.resource_id = owner.resource_id AND
incident_id = l_request_id;
WF_CORE.Context('CUG_GENERIC_WF_PKG', 'Update_CIC_Request_Info',
itemtype, itemkey, actid, funmode);
END Update_CIC_Request_Info;
SELECT *
FROM CS_INCIDENTS_ALL_VL
WHERE INCIDENT_NUMBER = l_request_number;