[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This custom function will be called from the subscriptions of the update/create/delete events for the
following SR child entities.
1. SR Tasks
2. SR Notes
3. SR Solution Links.
4. SR Task Assignments
***************/
-- Added for status update --anmukher -- 10/16/03
FUNCTION GET_STATUS_FLAG( p_incident_status_id IN NUMBER)
RETURN VARCHAR2;
l_updated_entity_code VARCHAR2(40) ;
l_updated_entity_id NUMBER;
l_entity_update_date DATE ;
SELECT task_id ,
last_update_date,
creation_date ,
last_updated_by,
source_object_type_code ,
source_object_id
FROM jtf_tasks_vl
WHERE task_id = p_task_id ;
SELECT task_assignment_id ,
t.task_id ,
t.source_object_type_code,
t.source_object_id,
ta.assignee_role,
ta.last_update_date,
ta.creation_date,
ta.last_updated_by
FROM jtf_task_all_assignments ta,
jtf_tasks_vl t
WHERE ta.task_id = t.task_id
AND ta.task_assignment_id = p_task_assignment_id ;
SELECT jtf_note_id ,
source_object_code source_object_type_code,
source_object_id ,
creation_date ,
last_update_date,
last_updated_by
FROM jtf_notes_vl
WHERE jtf_note_id = p_jtf_note_id ;
SELECT t.task_id ,
t.source_objecT_type_code task_objecT_type_code,
t.source_object_id task_source_object_id ,
t.escalation_level,
t.creation_date,
t.last_update_date,
t.last_updated_by,
r.reference_code ,
r.object_type_code ref_object_type_code,
r.object_id ref_object_id ,
r.task_reference_id
FROM jtf_tasks_vl t,
jtf_task_references_vl r
WHERE t.task_id = p_task_id
AND t.task_id = r.task_id ;
SELECT t.task_id ,
t.source_objecT_type_code task_objecT_type_code,
t.source_object_id task_source_object_id ,
t.escalation_level,
t.creation_date,
t.last_update_date,
t.last_updated_by
FROM jtf_tasks_vl t
WHERE t.task_id = p_task_id ;
SELECT incident_number ,
incident_type_id,
incident_status_id,
incident_severity_id,
incident_urgency_id,
incident_owner_id,
owner_group_id,
customer_id, last_updated_by, summary
FROM cs_incidents_all_vl
WHERE incident_id =p_incident_id ;
e_auditing_child_updates EXCEPTION ;
l_sr_update_out_rec CS_ServiceRequest_pvt.sr_update_out_rec_type;
l_last_updated_by NUMBER;
IF l_event_name = 'oracle.apps.cs.knowledge.SolutionLink.Updated' THEN
l_source_object_code := p_event.GetValueForParameter('OBJECT_CODE');
l_updated_entity_code := 'SR_SOLUTION_LINK';
l_updated_entity_id := l_event_key ;
l_entity_update_date := p_event.GetValueForParameter('EVENT_DATE');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_SOLUTION_LINK';
l_entity_update_date := p_event.GetValueForParameter('EVENT_DATE');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_task_dtls_rec.creation_date;
l_user_id := get_task_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.task.updateTask' THEN
-- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_task_dtls_rec.last_update_date ;
l_user_id := get_task_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.task.deleteTask' THEN
l_event_key := p_event.GetValueForParameter('TASK_ID');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_task_dtls_rec.last_update_date ;
l_user_id := get_task_dtls_rec.last_updated_by ;
l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_taskAssign_dtls_rec.creation_date ;
l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.task.updateTaskAssignment' THEN
l_event_key := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID') ;
l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_taskAssign_dtls_rec.last_update_date ;
l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.task.deleteTaskAssignment' THEN
l_event_key := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID') ;
l_updated_entity_id := get_taskAssign_dtls_rec.task_id ;
l_updated_entity_code := 'SR_TASK' ;
l_entity_update_date := get_taskAssign_dtls_rec.last_update_date ;
l_user_id := get_taskAssign_dtls_rec.last_updated_by ;
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_NOTE' ;
l_entity_update_date := get_note_dtls_rec.creation_date ;
l_user_id := get_note_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.notes.update' THEN
l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_CODE');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_NOTE' ;
l_entity_update_date := get_note_dtls_rec.last_update_date ;
l_user_id := get_note_dtls_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.notes.delete' THEN
l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_NOTE' ;
l_entity_update_date := get_note_dtls_rec.last_update_date ;
l_user_id := get_note_dtls_rec.last_updated_by ;
l_updated_entity_id := get_esc_details_rec.task_id ;
l_updated_entity_code := 'SR_ESCALATION' ;
l_entity_update_date := get_esc_details_rec.creation_date;
l_user_id := get_esc_details_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.escalation.updateEscalation' THEN
-- l_source_object_code := p_event.GetValueForParameter('SOURCE_OBJECT_TYPE_CODE');
l_updated_entity_id := get_esc_details_rec.task_id ;
l_updated_entity_code := 'SR_ESCALATION' ;
l_entity_update_date := get_esc_details_rec.last_update_date ;
l_user_id := get_esc_details_rec.last_updated_by ;
ELSIF l_event_name = 'oracle.apps.jtf.cac.escalation.deleteEscReference' THEN
l_event_key := p_event.GetValueForParameter('TASK_ID');
l_updated_entity_id := l_event_key ;
l_updated_entity_code := 'SR_ESCALATION' ;
l_entity_update_date := get_del_esc_dtls_rec.last_update_date ;
l_user_id := get_del_esc_dtls_rec.last_updated_by ;
IF ((l_updated_entity_code IS NOT NULL) AND (l_incident_id IS NOT NULL) AND
(l_updated_entity_id IS NOT NULL) ) THEN
--Bug fix 6275359.Commented by bkanimoz on 06-Aug-2007
--Whenever the Notes are updated call the Service Request update API so that it sends notification and inturn do the auditing
CS_SR_AUDIT_CHILD
(P_incident_id => l_incident_id,
P_updated_entity_code => l_updated_entity_code,
p_updated_entity_id => l_updated_entity_id ,
p_entity_update_date => l_entity_update_date,
p_entity_activity_code => l_entity_activity_code ,
p_update_program_code => 'EVENT_SUBSCRIPTION',
p_user_id => l_user_id ,
x_audit_id => l_audit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data );
RAISE e_auditing_child_updates ;
select object_version_number
into l_object_version_number
from cs_incidents_all_b
where incident_id =l_incident_id;
CS_ServiceRequest_pvt.Update_ServiceRequest(
p_api_version => 4.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_validation_level => fnd_api.g_valid_level_none,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => l_incident_id,
p_object_version_number => l_object_version_number,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_last_updated_by => l_user_id,
p_last_update_login => NULL,
p_last_update_date => sysdate,
p_service_request_rec => l_service_request_rec,
p_update_desc_flex => 'F',
p_notes => l_notes,
p_contacts => l_contacts,
p_audit_comments => NULL,
p_called_by_workflow => 'F',
p_workflow_process_id => NULL,
x_sr_update_out_rec => l_sr_update_out_rec
);
or l_event_name = 'oracle.apps.jtf.cac.notes.update'
or l_event_name = 'oracle.apps.jtf.cac.notes.delete'
THEN
OPEN get_inc_details(l_incident_id) ;
l_last_updated_by ,
l_summary ;
p_event_code => 'UPDATE_SERVICE_REQUEST',
p_incident_number => l_incident_number,
p_user_id => l_last_updated_by,
p_resp_id => NULL,
p_resp_appl_id => NULL,
p_old_sr_rec => l_Service_Request_rec,
p_new_sr_rec => l_Service_Request_rec,
p_contacts_table => l_contacts,
p_link_rec => NULL, -- using default value
p_wf_process_id => NULL,
p_owner_id => NULL, -- using default value
p_wf_manual_launch => 'N' , -- using default value
x_wf_process_id => l_workflow_process_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
RAISE e_auditing_child_updates ;
WHEN e_auditing_child_updates THEN
WF_CORE.CONTEXT('CS_SR_CHILD_AUDIT_PKG', 'CS_SR_Audit_ChildEntities',
l_event_name , p_subscription_guid);
p_owner_status_update_flag is OWNER otherwise set it's
value from service request record.
08/15/05 smisra Set contract_number col using contract_number from SR Rec
instead of contract_id column.
10/14/05 smisra Bug 4674121
Added two parameters p_old_inc_responded_by_date and
p_old_incident_resolved_date if this procedure is called
by update_status procedure then set
old_incident_responded_by_date and old_incident_resolved_date
of audit record from the parameter values otherwise set these
cols from service request record.
11/13/07 gasankar Bug 6621820
Handled the locking issue when trying to update the
CS_INCIDENTS_ALL_B
*/
PROCEDURE CS_SR_AUDIT_CHILD
(P_incident_id IN NUMBER,
P_updated_entity_code IN VARCHAR2 ,
p_updated_entity_id IN NUMBER ,
p_entity_update_date IN DATE ,
p_entity_activity_code IN VARCHAR2 ,
p_status_id IN NUMBER DEFAULT NULL,
p_old_status_id IN NUMBER DEFAULT NULL,
p_closed_date IN DATE DEFAULT NULL,
p_old_closed_date IN DATE DEFAULT NULL,
p_owner_id IN NUMBER DEFAULT NULL,
p_old_owner_id IN NUMBER DEFAULT NULL,
p_owner_group_id IN NUMBER DEFAULT NULL,
p_old_owner_group_id IN NUMBER DEFAULT NULL,
p_resource_type IN VARCHAR2 DEFAULT NULL,
p_old_resource_type IN VARCHAR2 DEFAULT NULL,
p_owner_status_upd_flag IN VARCHAR2 DEFAULT 'NONE',
p_update_program_code IN VARCHAR2 DEFAULT 'NONE',
p_user_id IN NUMBER DEFAULT NULL,
p_old_inc_responded_by_date IN DATE DEFAULT NULL,
p_old_incident_resolved_date IN DATE DEFAULT NULL,
x_audit_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_audit_vals_rec CS_ServiceRequest_PVT.sr_audit_rec_type ;
SELECT *
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id
FOR UPDATE NOWAIT ;
IF p_entity_update_date > l_service_request_rec.incident_last_modified_date THEN
UPDATE cs_incidents_all_b
SET incident_last_modified_date = NVL(p_entity_update_date,sysdate) ,
last_update_date = sysdate,
last_updated_by = NVL(p_user_id , FND_GLOBAL.user_id)
WHERE incident_id = p_incident_id ;
IF p_updated_entity_code = 'SR_NOTE' THEN
UPDATE cs_incidents_all_tl
SET text_index = 'A'
WHERE incident_id = p_incident_id;
IF p_update_program_code <> 'NONE' THEN
l_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := p_update_program_code;
l_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
l_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
l_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
l_audit_vals_rec.LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
l_audit_vals_rec.OLD_LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
l_audit_vals_rec.UPDATED_ENTITY_CODE := P_updated_entity_code ;
l_audit_vals_rec.UPDATED_ENTITY_ID := p_updated_entity_id ;
l_audit_vals_rec.INCIDENT_LAST_MODIFIED_DATE := p_entity_update_date;
l_audit_vals_rec.OLD_INC_PROGRAM_UPDATE_DATE := l_service_request_rec.PROGRAM_UPDATE_DATE;
l_audit_vals_rec.INC_PROGRAM_UPDATE_DATE := l_service_request_rec.PROGRAM_UPDATE_DATE;
p_last_update_date => sysdate,
p_creation_date => sysdate,
x_return_status => x_return_status ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data ,
x_audit_id => x_audit_id );
SELECT close_flag
FROM cs_incident_statuses_b
WHERE incident_status_id = p_incident_status_id;