The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cs_wf_process_id_s.nextval
FROM dual;
SELECT inc.incident_id,
inc.workflow_process_id,
inc.object_version_number,
type.workflow,
status.close_flag
FROM cs_incidents_all_b inc,
cs_incident_types type,
cs_incident_statuses status
WHERE inc.incident_number = p_request_number
AND type.incident_type_id = inc.incident_type_id
AND status.incident_status_id = inc.incident_status_id;
SELECT item_type
FROM wf_activities
WHERE name = l_workflow_proc
AND trunc(NVL(begin_date,sysdate)) <= trunc(sysdate)
AND trunc(NVL(end_date,sysdate)) >= trunc(sysdate);
SELECT incident_id, workflow_process_id,object_version_number
FROM cs_incidents_all_b
WHERE incident_number = p_request_number;
SELECT 'x' INTO l_dummy
FROM WF_ROLES
WHERE name = l_administrator;
SELECT type.workflow
INTO l_workflow_proc
FROM cs_incident_types type, cs_incidents_all_vl inc
WHERE inc.incident_number = p_request_number
AND inc.incident_type_id = type.incident_type_id;
SELECT status.close_flag INTO l_close_flag
FROM cs_incident_statuses status, cs_incidents_all_vl inc
WHERE inc.incident_number = p_request_number
AND inc.incident_status_id = status.incident_status_id;
We will no longer directly update the cs_incidents_all_b table.
We will be calling the Service request Update API to do the update.
This API will call the create audit API so we do not have to call this either
-- Update the workflow process ID of the request
IF (l_nowait = TRUE) THEN
UPDATE CS_INCIDENTS_ALL_B
SET workflow_process_id = l_wf_process_id
WHERE CURRENT OF l_ServeReq_NW_csr;
UPDATE CS_INCIDENTS_ALL_B
SET workflow_process_id = l_wf_process_id
WHERE incident_number = p_request_number;
/**** No longer needed since we are going to explicitly update ****/
/**** the service request's workflow process id. This therefore ****/
/**** will not create an audit record for this update. Also, ****/
/**** the update API does not handle the p_called_by_workflow ****/
/**** anymore. rmanabat 032403 ****/
-- CS_ServiceRequest_PVT.initialize_rec(l_service_request_rec);
/**** No longer needed since we are going to explicitly update ****/
/**** the service request's workflow process id. This therefore ****/
/**** will not create an audit record for this update. Also, ****/
/**** the update API does not handle the p_called_by_workflow ****/
/**** anymore. rmanabat 032403 ****/
/******************
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 3.0, -- Changed from 2.0 for 11.5.9
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => l_request_id,
p_object_version_number => l_object_version_number,
p_last_updated_by => p_initiator_user_id,
p_last_update_date => sysdate,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes,
p_contacts => l_contacts,
p_called_by_workflow => FND_API.G_TRUE,
p_workflow_process_id => l_wf_process_id,
x_interaction_id => l_interaction_id,
x_workflow_process_id => out_wf_process_id
);
/**** Replaced Update API above with explicit update ****/
/**** rmanabat . 032403 ****/
UPDATE CS_INCIDENTS_ALL_B
SET workflow_process_id = l_wf_process_id
WHERE incident_id = l_request_id;
Select item_type
from cs_incidents_all_b inc, cs_incident_types types, wf_activities wf
where inc.incident_number= p_request_number
and types.incident_type_id=inc.incident_type_id
and wf.name=types.workflow;
SELECT fnd.user_name, emp.full_name
INTO l_user_name, l_emp_name
FROM fnd_user fnd, per_people_x emp
WHERE fnd.user_id = p_user_id
AND fnd.employee_id = emp.person_id (+);
SELECT ppf.person_id
FROM per_assignments_f paf,
per_people_f ppf
WHERE paf.person_id = l_in_person_id
AND paf.primary_flag = 'Y'
AND l_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppf.person_id = paf.supervisor_id
AND ppf.current_employee_flag = 'Y'
AND l_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT user_id
FROM fnd_user
WHERE employee_id = l_employee_id;
SELECT incident_action_id, incident_id, workflow_process_id
FROM cs_incident_actions
WHERE incident_id = p_request_id
AND action_num = p_action_number
FOR UPDATE OF workflow_process_id NOWAIT;
SELECT incident_action_id, incident_id, workflow_process_id
FROM cs_incident_actions
WHERE incident_id = p_request_id
AND action_num = p_action_number
FOR UPDATE OF workflow_process_id;
SELECT cs_action_wf_proc_id_s.nextval
FROM dual;
SELECT 'x' INTO l_dummy
FROM WF_ROLES
WHERE name = l_administrator;
SELECT type.workflow INTO l_workflow_proc
FROM cs_incident_actions action, cs_incident_types type
WHERE action.incident_id = p_request_id
AND action.action_num = p_action_number
AND action.action_type_id = type.incident_type_id;
UPDATE cs_incident_actions
SET workflow_process_id = l_wf_process_id
WHERE CURRENT OF l_action_nw_csr;
UPDATE cs_incident_actions
SET workflow_process_id = l_wf_process_id
WHERE CURRENT OF l_action_csr;
SELECT cs_incident_action_audit_s.NEXTVAL INTO l_action_audit_id FROM dual;
INSERT INTO cs_incident_action_audit
( incident_action_audit_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
incident_action_id,
incident_id,
new_workflow_flag,
workflow_process_name,
workflow_process_itemkey
)
VALUES
( l_action_audit_id, -- INCIDENT_ACTION_AUDIT_ID
SYSDATE, -- LAST_UPDATE_DATE
p_initiator_user_id, -- LAST_UPDATED_BY
SYSDATE, -- CREATION_DATE
p_initiator_user_id, -- CREATED_BY
l_request_action_id, -- INCIDENT_ACTION_ID
l_request_id, -- INCIDENT_ID
'Y', -- NEW_WORKFLOW_FLAG
l_workflow_proc, -- WORKFLOW_PROCESS_NAME
l_itemkey -- WORKFLOW_PROCESS_ITEMKEY
);
SELECT 'x'
FROM wf_roles
WHERE name = l_dispatch_role;
SELECT fnd.user_name, emp.full_name
INTO l_user_name, l_emp_name
FROM fnd_user fnd, per_people_x emp
WHERE fnd.user_id = p_abort_user_id
AND fnd.employee_id = emp.person_id (+);
SELECT person_id INTO p_employee_id_out
FROM per_people_x
WHERE first_name = p_emp_first_name
AND employee_number IS NOT NULL;
SELECT person_id INTO p_employee_id_out
FROM per_people_x
WHERE last_name = p_emp_last_name
AND employee_number IS NOT NULL;
SELECT person_id INTO p_employee_id_out
FROM per_people_x
WHERE last_name = p_emp_last_name
AND first_name = p_emp_first_name
AND employee_number IS NOT NULL;
SELECT 'x' INTO l_dummy
FROM per_people_x
WHERE person_id = p_employee_id;
SELECT employee_id INTO l_employee_id
FROM fnd_user
WHERE user_id = p_fnd_user_id;