[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y'
FROM jtf_rs_group_usages
WHERE group_id = p_group_id
AND usage = 'SUPPORT';
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_territory_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';
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_territory_id => x_territory_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_territory_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';
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_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
-- The following updates are made because when the CreateSR API is
-- called with Auto Assign, then the UpdateSR Business Event will be
-- kicked off before the CreateSR Bus.Events which from User POV will
-- logically be wrong.
BEGIN
l_service_request_rec.group_type := l_default_group_type;
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;
END IF; /* l_update_grp_flag OR l_update_own_flag IS 'Y' */
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;
SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
INTO l_day_week, l_time_day
FROM cs_incidents_all_b
WHERE incident_id = l_incident_id ;
Select system_id into l_system_id
from csi_item_instances
Where instance_id = l_customer_product_id;
p_auto_select_flag => 'N',
p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
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 => l_start_date,
p_end_date => l_end_date,
p_territory_flag => nvl(l_territory_flag,'N'),
p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, '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 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 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 '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;
SELECT to_char(sysdate, 'd'), to_char(sysdate, 'hh24:mi')
INTO l_day_week, l_time_day
FROM cs_incidents_all_b
WHERE incident_id = l_incident_id ;
Select system_id into l_system_id
from csi_item_instances
Where instance_id = l_customer_product_id;
p_auto_select_flag => 'N',
p_ib_preferred_engineer => nvl(l_ib_resource_flag,'N'),
p_contracts_preferred_engineer => nvl(l_contract_res_flag,'N'),
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 => l_start_date,
p_end_date => l_end_date,
p_territory_flag => nvl(l_territory_flag,'N'),
p_calendar_flag => nvl(l_cs_sr_chk_res_cal_avl, 'N') ,
p_calendar_check => nvl(l_cs_sr_chk_res_cal_avl, '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 NVL(rs.category_id,0) = NVL(l_cat_id,0)
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;