[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
cia.incident_id,cia.owning_department_id,cia.inventory_item_id
,cia.maint_organization_id,cia.incident_date, cia.expected_resolution_date,cit.maintenance_flag
,cia.incident_status_id,cia.incident_severity_id,cia.customer_id,cia.object_version_number
,cia.customer_product_id
into
l_incident_id,l_owning_department_id,l_inventory_item_id
,l_maint_organization_id, l_incident_date, l_expected_resolution_date,l_maintenance_flag
,l_incident_status_id,l_incident_severity_id,l_customer_id,l_object_version_number
,l_instance_id
from cs_incidents_vl_sec cia, cs_incident_types_vl_sec cit
where cia.incident_number = l_incident_number
and cia.incident_type_id = cit.incident_type_id;
select instance_number,instance_description,concatenated_segments,msi.description
into l_instance_number,l_instance_description,l_concatenated_segments,l_conc_segments_description
from csi_item_instances cii, mtl_system_items_kfv msik, mtl_system_items msi
where cii.last_vld_organization_id = msi.organization_id
and cii.inventory_item_id = msi.inventory_item_id
and msi.organization_id = msik.organization_id
and msi.inventory_item_id = msik.inventory_item_id
and cii.instance_id = l_instance_id
;
select msik.concatenated_segments,msik.description
into l_concatenated_segments,l_conc_segments_description
from mtl_system_items_kfv msik, mtl_system_items msi
where msi.organization_id = msik.organization_id
and msi.inventory_item_id = msik.inventory_item_id
and msi.inventory_item_id = l_inventory_item_id
and msi.organization_id = l_maint_organization_id;
select default_department_id
into l_default_department_id
from wip_eam_parameters
where organization_id = l_maint_organization_id;
-- update the service request owning department id
l_owning_department_id := l_default_department_id;
select department_code
into l_owning_department_code
from bom_departments
where department_id = l_owning_department_id
and organization_id = l_maint_organization_id;
-- call service request update API to update the owning dept on Service Request
-- fnd_global.apps_initialize is required as Service Request have a security access.
-- As suggested by Service team, we need to set the responsibility and user before Service Request can be updated.
fnd_global.apps_initialize(
user_id => l_sr_user_id,
resp_id => l_sr_resp_id,
resp_appl_id => l_sr_resp_appl_id
);
cs_servicerequest_pub.update_serviceRequest(
p_api_version => l_sr_api_version
,p_request_id => l_incident_id
,p_service_request_rec => l_service_rec
,p_object_version_number => l_object_version_number
,p_notes => l_notes_rec
,p_contacts => l_contacts_rec
,p_last_updated_by => l_sr_user_id
,p_last_update_date => sysdate
,p_resp_appl_id => l_sr_resp_appl_id
,p_resp_id => l_sr_resp_id
,x_return_status => l_sr_return_status
,x_msg_count => l_sr_msg_count
,x_msg_data => l_sr_msg_data
,x_workflow_process_id => l_sr_workflow_process_id
,x_interaction_id => l_sr_interaction_id
);
select department_code
into l_owning_department_code
from bom_departments
where department_id = l_owning_department_id
and organization_id = l_maint_organization_id;
select beda.responsibility_id,beda.responsibility_application_id
into l_responsibility_id, l_responsibility_appl_id
from bom_eam_dept_approvers beda
where
beda.dept_id = l_owning_department_id
and beda.organization_id = l_maint_organization_id;
function Service_Request_Updated(
p_subscription_guid in raw,
p_event in out NOCOPY wf_event_t
) return varchar2
is
L_EVENT_NAME varchar2(240);
select
cia.incident_id,cia.owning_department_id,cia.inventory_item_id,cia.maint_organization_id,
cia.incident_date, cia.expected_resolution_date,cit.maintenance_flag
,cia.customer_product_id
into
l_incident_id,l_owning_department_id,l_inventory_item_id,l_maint_organization_id
,l_incident_date, l_expected_resolution_date,l_maintenance_flag
,l_instance_id
from cs_incidents_vl_sec cia, cs_incident_types_vl_sec cit
where cia.incident_number = l_incident_number
and cia.incident_type_id = cit.incident_type_id;
select default_department_id
into l_default_department_id
from wip_eam_parameters
where organization_id = l_maint_organization_id;
-- update the service request owning department id
l_owning_department_id := l_default_department_id;
select department_code
into l_owning_department_code
from bom_departments
where department_id = l_owning_department_id
and organization_id = l_maint_organization_id;
-- call service request update API to update the owning dept on Service Request
fnd_global.apps_initialize(
user_id => l_sr_user_id,
resp_id => l_sr_resp_id,
resp_appl_id => l_sr_resp_appl_id
);
cs_servicerequest_pub.update_serviceRequest(
p_api_version => l_sr_api_version
,p_request_id => l_incident_id
,p_service_request_rec => l_service_rec
,p_object_version_number => l_object_version_number
,p_notes => l_notes_rec
,p_contacts => l_contacts_rec
,p_last_updated_by => l_sr_user_id
,p_last_update_date => sysdate
,p_resp_appl_id => l_sr_resp_appl_id
,p_resp_id => l_sr_resp_id
,x_return_status => l_sr_return_status
,x_msg_count => l_sr_msg_count
,x_msg_data => l_sr_msg_data
,x_workflow_process_id => l_sr_workflow_process_id
,x_interaction_id => l_sr_interaction_id
);
select department_code
into l_owning_department_code
from bom_departments
where department_id = l_owning_department_id
and organization_id = l_maint_organization_id;
wf_core.context('EAM_SRAPPROVAL_PVT','service_request_updated',
p_event.getEventName(),p_subscription_guid);
select default_department_id
into l_default_department_id
from wip_eam_parameters
where organization_id = p_maintenance_org_id;
select owning_department_id
into l_default_department_id
from eam_org_maint_defaults
where object_type = 50
and object_id = p_customer_product_id
and organization_id = p_maintenance_org_id;
select default_department_id
into l_default_department_id
from wip_eam_parameters
where organization_id = p_maintenance_org_id;