[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT incident_number,incident_id,object_version_number,incident_type_id,
incident_severity_id,inventory_item_id, inv_organization_id,
category_id,platform_id,inv_platform_org_id,problem_code,
cust_pref_lang_code,comm_pref_code,account_id,incident_status_id,
incident_urgency_id, customer_id,customer_product_id,
owner_group_id,contract_service_id,incident_location_id,site_id,
coverage_type,sr_creation_channel,cp_component_id, cp_subcomponent_id,
inv_component_id, inv_subcomponent_id, incident_country, incident_city,
incident_postal_code, incident_state, incident_province, incident_county,
incident_location_type
FROM cs_incidents_all_b
WHERE incident_status_id IN (SELECT incident_status_id
FROM cs_incident_statuses
WHERE NVL(close_flag,'N') <> 'Y' AND incident_subtype = 'INC')
AND NVL(owner_assigned_flag,'N') <> 'Y'
AND (((p_group1_id IS NOT NULL
OR p_group2_id IS NOT NULL
OR p_group3_id IS NOT NULL
OR p_group4_id IS NOT NULL
OR p_group5_id IS NOT NULL)
AND owner_group_id IN (p_group1_id,
p_group2_id,
p_group3_id,
p_group4_id,
p_group5_id))
OR (p_group1_id IS NULL
AND p_group2_id IS NULL
AND p_group3_id IS NULL
AND p_group4_id IS NULL
AND p_group5_id IS NULL))
AND (((p_incident_type1_id IS NOT NULL
or p_incident_type2_id IS NOT NULL
or p_incident_type3_id IS NOT NULL
or p_incident_type4_id IS NOT NULL
or p_incident_type5_id IS NOT NULL)
AND incident_type_id IN (p_incident_type1_id,
p_incident_type2_id,
p_incident_type3_id,
p_incident_type4_id,
p_incident_type5_id))
OR (p_incident_type1_id IS NULL
AND p_incident_type2_id IS NULL
AND p_incident_type3_id IS NULL
AND p_incident_type4_id IS NULL
AND p_incident_type5_id IS NULL))
AND (((p_incident_severity1_id IS NOT NULL
or p_incident_severity2_id IS NOT NULL
or p_incident_severity3_id IS NOT NULL
or p_incident_severity4_id IS NOT NULL
or p_incident_severity5_id IS NOT NULL)
AND incident_severity_id IN (p_incident_severity1_id,
p_incident_severity2_id,
p_incident_severity3_id,
p_incident_severity4_id,
p_incident_severity5_id))
OR (p_incident_severity1_id IS NULL
and p_incident_severity2_id IS NULL
and p_incident_severity3_id IS NULL
and p_incident_severity4_id IS NULL
and p_incident_severity5_id IS NULL));
p_last_updated_by => FND_GLOBAL.USER_ID,
p_service_request_rec => l_sr_rec,
x_owner_group_id => l_temp_owner_group_id,
x_owner_id => l_temp_owner_id,
x_owner_type => l_temp_owner_type,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data
);
p_last_updated_by IN VARCHAR2,
p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
x_owner_group_id OUT NOCOPY NUMBER,
x_owner_id OUT NOCOPY NUMBER,
x_owner_type OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
--Define Local Variables
l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
p_last_updated_by => p_last_updated_by,
p_service_request_rec => l_sr_rec,
x_owner_group_id => x_owner_group_id,
x_owner_type => x_owner_type,
x_owner_id => x_owner_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_last_updated_by IN VARCHAR2,
p_service_request_rec IN CS_ServiceRequest_pvt.service_request_rec_type,
x_owner_group_id OUT NOCOPY NUMBER,
x_owner_type OUT NOCOPY VARCHAR2,
x_owner_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Define Local Variables
l_api_name CONSTANT VARCHAR2(30) := 'Assign_ServiceRequest_Main';
l_update_grp_flag VARCHAR2(1) := 'N';
l_update_own_flag VARCHAR2(1) := 'N';
l_sr_update_out_rec CS_ServiceRequest_pvt.sr_update_out_rec_type;
SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id = p_component_id;
SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id = p_subcomponent_id;
l_update_grp_flag := 'N';
l_update_grp_flag := 'Y';
l_update_grp_flag := 'Y';
l_update_own_flag := 'N';
l_update_own_flag := 'Y';
l_update_own_flag := 'N';
l_update_own_flag := 'Y';
IF ((l_update_grp_flag = 'Y') OR ( l_update_own_flag = 'Y')) THEN
BEGIN
--Call Update Service Request for Updating owner_group_id,owner_id
-- and resource_type. If a group_id is not returned by AM API then
-- do not do any updates
-- initialise
CS_ServiceRequest_pvt.initialize_rec(l_service_request_rec);
IF l_update_own_flag = 'Y' THEN
IF l_sr_rec.owner_id IS NULL THEN
l_service_request_rec.owner_id := l_owner_id;
l_service_request_rec.last_update_program_code := 'SRAMCONC';
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 => p_incident_id,
p_object_version_number => l_object_version_number,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_last_updated_by => p_last_updated_by,
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_table,
p_contacts => l_contacts_table,
p_audit_comments => NULL,
p_called_by_workflow => 'F',
p_workflow_process_id => NULL,
x_sr_update_out_rec => l_sr_update_out_rec
);
SELECT country,province,state,city,postal_code,county
FROM hz_locations
WHERE location_id = p_incident_location_id;
SELECT location_id FROM hz_party_sites
WHERE party_site_id = p_party_site_id;
SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
FROM okc_k_items
WHERE cle_id = l_contract_service_id;
SELECT class_code
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_party_id
AND class_category = l_cust_category;
SELECT hzp.phone_area_code
FROM hz_contact_points hzp
WHERE hzp.contact_point_id = c_customer_phone_id;
SELECT employees_total, party_name
FROM hz_parties
WHERE party_id = p_customer_id;
SELECT business_process_id INTO l_business_process_id
FROM cs_incident_types
WHERE incident_type_id = l_sr_rec.type_id;
p_auto_select_flag => NULL,
p_contracts_preferred_engineer => l_contract_res_flag,
p_ib_preferred_engineer => l_ib_resource_flag,
p_effort_duration => NULL,
p_effort_uom => NULL,
p_start_date => NULL,
p_end_date => NULL,
p_territory_flag => 'Y',
p_calendar_flag => 'N',
p_web_availability_flag => 'Y',
p_filter_excluded_resource => 'Y',
p_category_id => NULL,
p_inventory_item_id => NULL,
p_inventory_org_id => NULL,
p_column_list => NULL,
p_calling_doc_id => NULL,
p_calling_doc_type => 'SR',
p_sr_rec => l_sr_am_rec,
p_sr_task_rec => NULL,
p_defect_rec => NULL,
p_business_process_id => l_business_process_id,
p_business_process_date => l_sr_rec.request_date,
x_Assign_Resources_tbl => l_Assign_Groups_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT country,province,state,city,postal_code,county
FROM hz_locations
WHERE location_id = p_incident_location_id;
SELECT location_id FROM hz_party_sites
WHERE party_site_id = p_party_site_id;
SELECT TO_NUMBER(object1_id1), TO_NUMBER(object1_id2)
FROM okc_k_items
WHERE cle_id = l_contract_service_id;
SELECT class_code
FROM hz_code_assignments
WHERE owner_table_name = 'HZ_PARTIES'
AND owner_table_id = l_party_id
AND class_category = l_cust_category;
select product_skill_wt,prob_code_skill_wt, category_skill_wt
from cs_sr_load_balance_wt
where incident_type_id = l_incident_type_id
and incident_severity_id = l_incident_severity_id;
SELECT hzp.phone_area_code
FROM hz_contact_points hzp
WHERE hzp.contact_point_id = c_customer_phone_id;
SELECT 'Y'
FROM jtf_rs_group_members
WHERE group_id = p_group_id
AND resource_id = p_resource_id
AND NVL(delete_flag, 'N') <> 'Y';
SELECT employees_total, party_name
FROM hz_parties
WHERE party_id = p_customer_id;
SELECT business_process_id INTO l_business_process_id
FROM cs_incident_types
WHERE incident_type_id = l_sr_rec.type_id;
p_auto_select_flag => 'N',
p_ib_preferred_engineer => l_ib_resource_flag,
p_contracts_preferred_engineer => l_contract_res_flag,
p_contract_id => l_contract_service_id,
p_customer_product_id => l_cust_prod_id,
p_effort_duration => null,
p_effort_uom => null,
p_start_date => null,
p_end_date => null,
p_territory_flag => 'Y',
p_calendar_flag => 'N',
p_web_availability_flag => 'Y',
p_filter_excluded_resource => 'Y',
p_category_id => NULL,
p_inventory_item_id => NULL,
p_inventory_org_id => NULL,
p_column_list => null,
p_calling_doc_id => l_am_calling_doc_id,
p_calling_doc_type => l_am_calling_doc_type,
p_sr_rec => l_sr_am_rec,
p_sr_task_rec => null,
p_defect_rec => null,
p_business_process_id => l_business_process_id,
p_business_process_date => l_sr_rec.request_date,
x_Assign_Resources_tbl => l_Assign_Owner_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
SELECT product_skill_wt,platform_skill_wt,prob_code_skill_wt, category_skill_wt,
last_login_time_wt,severity1_count_wt,severity2_count_wt,
severity3_count_wt,severity4_count_wt,time_zone_diff_wt
FROM cs_sr_load_balance_wt
WHERE incident_type_id = l_incident_type_id
AND incident_severity_id = l_incident_severity_id;
SELECT s.skill_level
FROM jtf_rs_skill_levels_vl s,
jtf_rs_resource_skills rs
WHERE rs.resource_id = l_resource_id
AND rs.product_id = l_prod_id
AND rs.product_org_id = l_prod_org_id
AND rs.skill_level_id = s.skill_level_id;
SELECT s.skill_level
FROM jtf_rs_skill_levels_vl s,
jtf_rs_resource_skills rs
WHERE rs.resource_id = l_resource_id
AND rs.platform_id = l_platform_id
AND rs.platform_org_id = l_platform_org_id
AND rs.skill_level_id = s.skill_level_id;
SELECT s.skill_level
FROM jtf_rs_skill_levels_vl s,
jtf_rs_resource_skills rs
WHERE rs.resource_id = l_resource_id
AND rs.problem_code = l_problem_code
AND rs.skill_level_id = s.skill_level_id;
SELECT s.skill_level
FROM jtf_rs_skill_levels_vl s,
jtf_rs_resource_skills rs
WHERE rs.resource_id = l_resource_id
AND rs.category_id = l_category_id
AND rs.skill_level_id = s.skill_level_id;
SELECT ROUND(((SYSDATE - nvl( max(owner_assigned_time),to_date('1990-01-01','yyyy-mm-dd'))) *24 * 60),2)
FROM cs_incidents_all_b
WHERE incident_owner_id = l_resource_id;
SELECT importance_level
FROM cs_incident_severities_vl
WHERE incident_subtype = 'INC'
AND incident_severity_id = p_inc_severity_id;
SELECT COUNT(*)
FROM cs_incidents_all_b
WHERE incident_severity_id = l_sev1_id
AND incident_owner_id = l_resource_id
AND incident_status_id NOT IN (
SELECT incident_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND close_flag = 'Y');
SELECT COUNT(*)
FROM cs_incidents_all_b
WHERE incident_severity_id = l_sev2_id
AND incident_owner_id = l_resource_id
AND incident_status_id NOT IN (
SELECT incident_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND close_flag = 'Y');
SELECT COUNT(*)
FROM cs_incidents_all_b
WHERE incident_severity_id = l_sev3_id
AND incident_owner_id = l_resource_id
AND incident_status_id NOT IN (
SELECT incident_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND close_flag = 'Y');
SELECT COUNT(*)
FROM cs_incidents_all_b
WHERE incident_severity_id = l_sev4_id
AND incident_owner_id = l_resource_id
AND incident_status_id NOT IN (
SELECT incident_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND close_flag = 'Y');
SELECT time_zone
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id;