[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(detailed_erecord_req_flag ,'N')
FROM cs_incident_types_b
WHERE incident_type_id = p_incident_type_id ;
Select timezone_code
From fnd_timezones_b
Where upgrade_tz_id = l_time_zone_id;
SELECT CAST(MULTISET
(SELECT obj.name RelatedOB_name
, SrLnkEO.object_number RelatedOB_Number
, csz_servicerequest_util_pvt.get_rel_obj_details(SrLnkEO.object_type,
SrLnkEO.object_id) RelatedOB_Description
FROM cs_incident_links SrLnkEO
, jtf_objects_tl obj
WHERE SrLnkEO.subject_id = x_incident_id
AND SrLnkEO.subject_type = 'SR'
AND SrLnkEO.object_type <> 'SR'
AND obj.object_code (+) = SrLnkEO.object_type
AND obj.language (+) = userenv('LANG')
AND sysdate between nvl(SrLnkEO.start_date_active,sysdate)
AND nvl(SrLnkEO.end_date_active,sysdate) ) AS cs_sr_related_OBJ_list_t) RELATED_OB_LIST
FROM DUAL;
SELECT CAST(MULTISET
(SELECT ltype.name RelatedSR_Name
, status.name RelatedSR_Status
, fnd1.user_name RelatedSR_Created_By
, tlSR.summary RelatedSR_Summary
, SrLnkEO.object_number RelatedSR_Number
, severity.name RelatedSR_Severity
, (SELECT rs.resource_name
FROM jtf_rs_resource_extns_tl rs
WHERE rs.resource_id = relSR.incident_owner_id
AND rs.language = userenv('LANG')) RelatedSR_Owner
FROM cs_incident_links SrLnkEO
, cs_sr_link_types_tl ltype
, fnd_user fnd1
, cs_incidents_all_b relSR
, cs_incidents_all_tl tlSR
, cs_incident_statuses_tl status
, cs_incident_severities_tl severity
WHERE SrLnkEO.subject_id = x_incident_id
AND SrLnkEO.subject_type = 'SR'
AND SrLnkEO.object_type = 'SR'
AND SrLnkEO.link_type_id = ltype.link_type_id
AND ltype.LANGUAGE = userenv('LANG')
AND SrLnkEO.created_by = fnd1.user_id
AND SrLnkEO.object_number = relSR.incident_number
AND relSR.incident_status_id = status.incident_status_id
AND status.LANGUAGE = userenv('LANG')
AND relSR.incident_severity_id = severity.incident_severity_id
AND severity.LANGUAGE = userenv('LANG')
AND tlSR.incident_id = relSR.incident_id
AND tlSR.language = userenv('lang')
AND sysdate between nvl(SrLnkEO.start_date_active,sysdate)
AND nvl(SrLnkEO.end_date_active,sysdate) ) AS cs_sr_related_SR_list_t) RELATED_SR_LIST
FROM DUAL;
SELECT CAST(MULTISET
(SELECT nvl2(ext_usert.resource_name, ext_usert.resource_name, fnd1.user_name) as Note_Created_By
, DECODE(JNB.entered_date,'','',to_char(fnd_timezone_pub.adjust_datetime(JNB.ENTERED_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) Note_Creation_Date -- Bug 11830349
-- , DECODE(JNB.entered_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,JNB.ENTERED_DATE),l_date_format)) Note_Creation_Date
, FLS.MEANING Note_Type
, FLP.MEANING Note_Visibility
, JNT.NOTES Note_Description
, JNT.NOTES_DETAIL Note_Detail
FROM JTF_NOTES_B JNB ,
JTF_NOTES_TL JNT ,
FND_LOOKUPS FLS ,
FND_LOOKUPS FLP,
jtf_rs_resource_extns ext_userb,
jtf_rs_resource_extns_tl ext_usert,
FND_USER fnd1
WHERE JNB.JTF_NOTE_ID = JNT.JTF_NOTE_ID
AND JNT.LANGUAGE = USERENV('LANG')
AND FLS.LOOKUP_TYPE(+) = 'JTF_NOTE_TYPE'
AND FLS.LOOKUP_CODE(+) = JNB.NOTE_TYPE
AND FLP.lookup_type = 'JTF_NOTE_STATUS'
AND FLP.lookup_code = JNB.note_status
AND JNB.source_object_code = 'SR'
AND JNB.source_object_id = x_incident_id
AND ((JNB.note_status <> 'P') OR
(JNB.note_status = 'P' and JNB.created_by = fnd_profile.value_wnps('USER_ID') ))
AND JNB.created_by = fnd1.user_id
AND ext_userb.user_id (+) = fnd1.user_id
AND ext_usert.language (+)= userenv('LANG')
AND ext_userb.resource_id = ext_usert.resource_id (+)
AND ext_userb.category = ext_usert.category (+)
order by JNB.entered_date ) AS cs_sr_note_list_t) NOTE_LIST
FROM DUAL;
Select timezone_code
From fnd_timezones_b
Where upgrade_tz_id = l_time_zone_id;
SELECT CAST(MULTISET
(SELECT
-- DECODE(jtf1B.creation_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.creation_date),l_date_format)) Task_Creation_Date
DECODE(jtf1B.creation_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.creation_date, l_source_tz_code,l_dest_tz_code),l_date_format)) Task_Creation_Date -- Bug 11830349
, jtf1B.task_number Task_Number
, type.name Task_Type
, jtf1T.TASK_NAME Task_Name
, jtf1T.DESCRIPTION Task_Description
, priority.name Task_Priority
, statusT.name Task_Status
, jtf_task_utl.get_owner(jtf1B.owner_type_code, jtf1B.owner_id) Task_Owner
, obj_vl.name Task_ownertype
, jtf2.task_number Task_parenttasknumber
, jtf1B.planned_effort task_planned_effort
, (SELECT unit_of_measure
FROM mtl_units_of_measure_vl um
WHERE um.uom_code = jtf1B.planned_effort_uom) as task_planned_effort_uom
, jtf1B.actual_effort task_actual_effort
, (SELECT unit_of_measure
FROM mtl_units_of_measure_tl um
WHERE um.uom_code = jtf1B.actual_effort_uom
AND um.LANGUAGE = USERENV('LANG') ) as task_actual_effort_uom
, jtf1B.duration task_duration
, (SELECT unit_of_measure
FROM mtl_units_of_measure_tl um
WHERE um.uom_code = jtf1B.duration_uom
AND um.LANGUAGE = USERENV('LANG') ) AS task_duration_uom
/*, DECODE(jtf1B.planned_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.planned_start_date),l_date_format)) task_planned_start_date
, DECODE(jtf1B.planned_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.planned_end_date),l_date_format)) task_planned_end_date
, DECODE(jtf1B.scheduled_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.scheduled_start_date),l_date_format)) task_scheduled_start_date
, DECODE(jtf1B.scheduled_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.scheduled_end_date),l_date_format)) task_scheduled_end_date
, DECODE(jtf1B.actual_start_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.ACTUAL_START_DATE),l_date_format)) task_actual_start_date
, DECODE(jtf1B.actual_end_date,'','',to_char(hz_timezone_pub.convert_datetime(l_source_timezone_id,l_desc_timezone_id,jtf1B.ACTUAL_END_DATE),l_date_format)) task_actual_end_date
*/
-- Bug 11830349
, DECODE(jtf1B.planned_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.planned_start_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_planned_start_date
, DECODE(jtf1B.planned_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.planned_end_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_planned_end_date
, DECODE(jtf1B.scheduled_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.scheduled_start_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_scheduled_start_date
, DECODE(jtf1B.scheduled_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.scheduled_end_date, l_source_tz_code,l_dest_tz_code),l_date_format)) task_scheduled_end_date
, DECODE(jtf1B.actual_start_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.ACTUAL_START_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) task_actual_start_date
, DECODE(jtf1B.actual_end_date,'','',to_char(fnd_timezone_pub.adjust_datetime(jtf1B.ACTUAL_END_DATE, l_source_tz_code,l_dest_tz_code),l_date_format)) task_actual_end_date
FROM JTF_TASKS_TL jtf1T, JTF_TASKS_B jtf1B
, jtf_tasks_b jtf2
, jtf_task_types_tl type
, jtf_task_priorities_tl priority
, JTF_TASK_STATUSES_TL statusT, JTF_TASK_STATUSES_B statusB
, jtf_objects_tl obj_vl
WHERE jtf1B.source_object_type_code='SR'
AND jtf1B.source_object_id = x_incident_id
AND jtf1B.TASK_ID = jtf1T.TASK_ID
AND jtf1T.LANGUAGE = userenv('LANG')
AND type.task_type_id = jtf1B.task_type_id
AND type.LANGUAGE = userenv('LANG')
AND priority.task_priority_id = jtf1B.task_priority_id
AND priority.LANGUAGE = userenv('LANG')
AND statusB.TASK_STATUS_ID = statusT.TASK_STATUS_ID
AND statusT.LANGUAGE = userenv('LANG')
AND statusb.usage = 'TASK'
AND statusB.task_status_id = jtf1B.task_status_id
AND obj_vl.object_code = jtf1B.owner_type_code
AND obj_vl.LANGUAGE = userenv('LANG')
AND jtf1B.parent_task_id = jtf2.task_id (+)
ORDER BY jtf1B.creation_date) AS cs_sr_task_list_t) TASK_LIST
FROM DUAL;
Select timezone_code
From fnd_timezones_b
Where upgrade_tz_id = l_time_zone_id;
SELECT sr.incident_id Incident_Id
,sr.incident_number Incident_Number
,sr.incident_type_id
,sr.incident_status_id Incident_Status_Id
,sr.incident_severity_id Incident_Severity_Id
,sr.incident_urgency_id Incident_Urgency_Id
,sr.owner_group_id Sr_Group_Id
,sr.incident_owner_id Sr_Owner_Id
,sr.problem_code Problem_code_id
,sr.resolution_code Resolution_code_id
,tl.summary
,tl.resolution_summary
,sr.publish_flag Publish_Flag
,tl.summary Problem_Summary
,sr.time_zone_id TimeZone_Id
,sr.customer_id Customer_Id
,sr.account_id Account_Id
,sr.inventory_item_id Inventory_Item_Id
-- Bug 11830349
,nvl2(sr.incident_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) reported_date
,nvl2(sr.incident_last_modified_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_last_modified_date, l_source_tz_code,l_dest_tz_code),v_date_format),null)Last_Update_Date
,nvl2(sr.incident_occurred_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_occurred_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Incident_Date
,nvl2(sr.close_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.close_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Close_Date
,nvl2(sr.incident_resolved_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.incident_resolved_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) incident_resolved_date
,nvl2(sr.obligation_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.obligation_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) respond_by_date
,nvl2(sr.expected_resolution_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.expected_resolution_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) resolve_by_date
,nvl2(sr.inc_responded_by_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.inc_responded_by_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) inc_responded_by_date
,nvl2(sr.actual_resolution_date,TO_CHAR(fnd_timezone_pub.adjust_datetime(sr.actual_resolution_date, l_source_tz_code,l_dest_tz_code),v_date_format),null) Actual_Resolution_Date
/* ,nvl2(sr.incident_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id, sr.incident_date),v_date_format),null) reported_date
,nvl2(sr.incident_last_modified_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_last_modified_date),v_date_format),null)Last_Update_Date
,nvl2(sr.incident_occurred_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_occurred_date),v_date_format),null) Incident_Date
,nvl2(sr.close_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.close_date),v_date_format),null) Close_Date
,nvl2(sr.incident_resolved_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.incident_resolved_date),v_date_format),null) incident_resolved_date
,nvl2(sr.obligation_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.obligation_date),v_date_format),null) respond_by_date
,nvl2(sr.expected_resolution_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.expected_resolution_date),v_date_format),null) resolve_by_date
,nvl2(sr.inc_responded_by_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.inc_responded_by_date),v_date_format),null) inc_responded_by_date
,nvl2(sr.actual_resolution_date,TO_CHAR(hz_timezone_pub.convert_datetime(v_source_timezone_id,v_desc_timezone_id,sr.actual_resolution_date),v_date_format),null) Actual_Resolution_Date
*/
,sr.status_flag Status_Flag_Code
,sr.created_by Created_By_Id
,sr.customer_product_id Customer_Product_Id
,sr.org_id Organization_Id
,sr.inv_organization_id Inventory_Org_Id
,instance.instance_number Instance_Number
,nvl(instance.serial_number,sr.current_serial_number) Serial_Number
,nvl(instance.external_reference,sr.external_reference) Tag_Number
, nvl2(sr.customer_product_id, instance.inventory_revision, sr.inv_item_revision) Item_Revision
, nvl2(sr.customer_product_id, (select instance.inventory_revision
from mtl_system_items_b_kfv product_a,
csi_item_instances instance
where product_a.inventory_item_id = instance.inventory_item_id
and product_a.organization_id = sr.inv_organization_id
and sr.cp_component_id=instance.instance_id), sr.inv_component_version) Component_Revision
, nvl2(sr.customer_product_id, (select instance.inventory_revision
from mtl_system_items_b_kfv product_b,
csi_item_instances instance
where product_b.inventory_item_id = instance.inventory_item_id
and product_b.organization_id = sr.inv_organization_id
and sr.cp_subcomponent_id = instance.instance_id ), sr.inv_subcomponent_version) Sub_Component_Revision
,sr.incident_attribute_1 Attribute1
,sr.incident_attribute_2 Attribute2
,sr.incident_attribute_3 Attribute3
,sr.incident_attribute_4 Attribute4
,sr.incident_attribute_5 Attribute5
,sr.incident_attribute_6 Attribute6
,sr.incident_attribute_7 Attribute7
,sr.incident_attribute_8 Attribute8
,sr.incident_attribute_9 Attribute9
,sr.incident_attribute_10 Attribute10
,sr.incident_attribute_11 Attribute11
,sr.incident_attribute_12 Attribute12
,sr.incident_attribute_13 Attribute13
,sr.incident_attribute_14 Attribute14
,sr.incident_attribute_15 Attribute15
,sr.incident_context Incident_Context
,sr.external_attribute_1 Ext_Attribute1
,sr.external_attribute_2 Ext_Attribute2
,sr.external_attribute_3 Ext_Attribute3
,sr.external_attribute_4 Ext_Attribute4
,sr.external_attribute_5 Ext_Attribute5
,sr.external_attribute_6 Ext_Attribute6
,sr.external_attribute_7 Ext_Attribute7
,sr.external_attribute_8 Ext_Attribute8
,sr.external_attribute_9 Ext_Attribute9
,sr.external_attribute_10 Ext_Attribute10
,sr.external_attribute_11 Ext_Attribute11
,sr.external_attribute_12 Ext_Attribute12
,sr.external_attribute_13 Ext_Attribute13
,sr.external_attribute_14 Ext_Attribute14
,sr.external_attribute_15 Ext_Attribute15
,sr.external_context Ext_Context
,sr.sr_creation_channel
,sr.contract_service_id
,sr.category_id
,sr.system_id
,sr.inv_component_id
,sr.cp_subcomponent_id
,sr.resource_type
,sr.incident_LOCATION_ID
,sr.incident_address
,nvl2(sr.incident_city, ','||sr.incident_city, NULL)
,nvl2(sr.incident_state, ', ' ||sr.incident_state, NULL)
,nvl2(sr.incident_province, ', '||sr.incident_province, NULL)
,nvl2(sr.incident_postal_code, ' '||sr.incident_postal_code, NULL)
,nvl2(sr.incident_country, ' ' ||sr.incident_country, NULL)
,instance.inventory_item_id
,instance.instance_id
,sr.cp_component_id
,sr.inv_subcomponent_id
,sr.incident_location_type
FROM cs_incidents_b_sec sr
,cs_incidents_all_tl tl
,csi_item_instances instance
WHERE sr.incident_id = tl.incident_id
AND tl.language = userenv('lang')
AND sr.incident_id = P_Incident_Id
AND sr.inventory_item_id = instance.inventory_item_id (+)
AND sr.customer_product_id = instance.instance_id (+);
l_Last_Update_Date VARCHAR2(30);
SELECT typest.name, nvl(typesb.detailed_erecord_req_flag,'N') Detailed_Erecord
FROM cs_incident_types_tl typest,
cs_incident_types_b typesb
WHERE typesb.incident_type_id = v_incident_type_id
AND typesb.incident_type_id = typest.incident_type_id
AND typest.language = userenv('LANG');
SELECT status.name ,status_b.sort_order
FROM cs_incident_statuses_tl status, cs_incident_statuses_b status_b
WHERE status.incident_status_id = v_incident_status_id
AND status.incident_status_id = status_b.incident_status_id
AND status.language = userenv('LANG');
SELECT sevt.name, sevb.importance_level
FROM cs_incident_severities_tl sevt, cs_incident_severities_b sevb
WHERE sevb.incident_severity_id = v_incident_severity_id
AND sevt.incident_severity_id = sevb.incident_severity_id
AND sevt.language = userenv('LANG');
SELECT urgency.name
FROM cs_incident_urgencies_tl urgency
WHERE urgency.incident_urgency_id = v_incident_urgency_id
AND urgency.language = userenv('LANG');
SELECT gr.group_name
FROM jtf_rs_groups_tl gr
WHERE gr.group_id =v_owner_group_id
AND gr.LANGUAGE = userenv('LANG');
SELECT rs.resource_name
FROM jtf_rs_resource_extns_tl rs
WHERE rs.resource_id = v_incident_owner_id
AND language = userenv('LANG');
SELECT meaning
FROM FND_LOOKUP_VALUES
WHERE lookup_code = v_lookup_code
AND lookup_type = v_lookup_type
AND LANGUAGE = userenv('LANG')
AND View_APPLICATION_ID = 170
AND SECURITY_GROUP_ID = fnd_global.lookup_security_group(LOOKUP_TYPE,
VIEW_APPLICATION_ID);
SELECT usr.user_name
FROM fnd_user usr
WHERE usr.user_id = v_user_id;
SELECT cont_type_lkup.meaning
FROM FND_LOOKUP_VALUES cont_type_lkup
,cs_hz_sr_contact_points sr_cont
WHERE sr_cont.contact_type=cont_type_lkup.lookup_code
AND cont_type_lkup.lookup_type='CS_SR_CONTACT_TYPE'
AND sr_cont.incident_id = v_incident_id
ANd sr_cont.primary_flag = 'Y'
AND cont_type_lkup.LANGUAGE = userenv('LANG')
AND cont_type_lkup.View_APPLICATION_ID = 170
AND cont_type_lkup.SECURITY_GROUP_ID = fnd_global.lookup_security_group(cont_type_lkup.LOOKUP_TYPE,
cont_type_lkup.VIEW_APPLICATION_ID);
SELECT CSZ_SERVICEREQUEST_UTIL_PVT.get_contact_name(sr_cont.contact_type,
sr_cont.party_id,
v_customer_id)
FROM cs_hz_sr_contact_points sr_cont
WHERE sr_cont.incident_id = v_incident_id
AND sr_cont.primary_flag = 'Y';
SELECT tz_tl.name
FROM fnd_timezones_b tz, fnd_timezones_tl tz_tl
WHERE tz.upgrade_tz_id = v_time_zone_id
AND tz.TIMEZONE_CODE = tz_tl.TIMEZONE_CODE
AND tz_tl.language = USERENV('LANG');
SELECT account.account_number
FROM hz_cust_accounts account
WHERE account.cust_account_id = v_account_id;
SELECT concatenated_segments, description
FROM MTL_SYSTEM_ITEMS_VL
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_inv_organization_id;
SELECT contract_number, service_description, coverage_description
FROM oks_ent_line_details_v
WHERE service_line_id = v_contract_service_id;
SELECT party.party_number,
party.party_name,
nvl2(party.primary_phone_country_code, party.primary_phone_country_code||'-',null)||
nvl2(party.primary_phone_area_code, party.primary_phone_area_code||'-',null)||
party.primary_phone_number customer_phone,
party.email_address,
party.party_type
FROM hz_parties party
WHERE party.party_id = v_customer_id;
SELECT ar.meaning PHONE_TYPE,
nvl2(party_cont.phone_country_code, party_cont.phone_country_code || '-',null ) ||
nvl2(party_cont.phone_area_code, party_cont.phone_area_code || '-', null) ||
party_cont.phone_number PHONE_NUMBER
FROM hz_contact_points party_cont,
cs_hz_sr_contact_points sr_cont,
FND_LOOKUP_VALUES ar
WHERE sr_cont.incident_id = v_incident_id
AND sr_cont.contact_point_id = party_cont.contact_point_id
AND sr_cont.contact_type <> 'EMPLOYEE'
AND party_cont.contact_point_type = 'PHONE'
AND party_cont.phone_line_type = ar.lookup_code
AND ar.lookup_type = 'PHONE_LINE_TYPE'
AND ar.LANGUAGE = userenv('LANG')
AND ar.VIEW_APPLICATION_ID = 222
AND ar.SECURITY_GROUP_ID = 0
AND sr_cont.primary_flag='Y'
UNION
SELECT hrl.meaning PHONE_TYPE,
pp.phone_number PHONE_NUMBER
FROM cs_hz_sr_contact_points sr_cont,
per_phones pp,
hr_lookups hrl
WHERE sr_cont.incident_id = v_incident_id
AND sr_cont.contact_type = 'EMPLOYEE'
AND pp.phone_id = sr_cont.contact_point_id
AND pp.parent_table = 'PER_ALL_PEOPLE_F'
AND pp.phone_type = hrl.lookup_code
AND hrl.lookup_type = 'PHONE_TYPE'
AND sr_cont.contact_point_type = 'PHONE'
AND sr_cont.primary_flag='Y';
SELECT party_cont.email_address EMAIL
FROM hz_contact_points party_cont,
cs_hz_sr_contact_points sr_cont
WHERE sr_cont.incident_id = v_incident_id
AND sr_cont.contact_point_id = party_cont.contact_point_id
AND sr_cont.contact_type <> 'EMPLOYEE'
AND party_cont.contact_point_type = 'EMAIL'
AND sr_cont.primary_flag = 'Y'
AND party_cont.email_address is not null
UNION
SELECT pap.email_address EMAIL
FROM cs_hz_sr_contact_points sr_cont,
per_all_people_f pap
WHERE sr_cont.incident_id = v_incident_id
AND sr_cont.contact_type = 'EMPLOYEE'
AND pap.person_id = sr_cont.party_id
AND sr_cont.primary_flag = 'Y'
AND pap.email_address is not null
AND sr_cont.contact_point_type = 'EMAIL';
SELECT cat.concatenated_segments
FROM mtl_categories_b_kfv cat
WHERE cat.category_id = v_category_id;
SELECT sys.name
FROM CSI_SYSTEMS_TL sys
WHERE sys.system_id = v_system_id
AND sys.LANGUAGE = USERENV('LANG');
SELECT product_b.concatenated_segments
FROM mtl_system_items_b_kfv product_b,
csi_item_instances instance
WHERE product_b.inventory_item_id = instance.inventory_item_id
AND product_b.organization_id = v_inv_organization_id
AND instance.instance_id = v_cp_component_id;
SELECT product_b.concatenated_segments
FROM mtl_system_items_b_kfv product_b
WHERE product_b.inventory_item_id = v_inv_component_id
AND product_b.organization_id = v_inv_organization_id;
SELECT name
FROM jtf_objects_tl o,
jtf_object_usages ou
WHERE o.object_code = ou.object_code
AND ou.object_user_code = 'RESOURCES'
AND o.object_code = v_resource_type
AND o.LANGUAGE = userenv ( 'LANG' );
SELECT loc.address1 || nvl2(loc.address2,', '||loc.address2,NULL) ||
nvl2(loc.address3,', '||loc.address3,NULL) ||
nvl2(loc.address4,', '||loc.address4,NULL) ||
nvl2(loc.city, ', '||loc.city, NULL) ||
nvl2(loc.state, ', ' ||loc.state, NULL) ||
nvl2(loc.province,', '||loc.province, NULL)||
nvl2(loc.postal_code, ' '||loc.postal_code,NULL) ||
nvl2(loc.country, ' ' || loc.country, NULL )
FROM HZ_LOCATIONS LOC
WHERE loc.location_id = v_incident_location_id;
SELECT loc.address1 || nvl2(loc.address2,','||loc.address2,NULL) ||
nvl2(loc.address3,', '||loc.address3,NULL) ||
nvl2(loc.address4,', '||loc.address4,NULL) ||
nvl2(loc.city, ', '||loc.city, NULL)||
nvl2(loc.state, ', ' ||loc.state, NULL) ||
nvl2(loc.province,', '||loc.province, NULL) ||
nvl2(loc.postal_code, ' '||loc.postal_code,NULL) ||
nvl2(loc.country, ' ' || loc.country, NULL)
FROM HZ_LOCATIONS LOC,
hz_party_sites hzp
WHERE hzp.party_site_id = v_incident_location_id
AND hzp.location_id = loc.location_id;
SELECT fnd1.meaning escalation_level
FROM fnd_lookups fnd1,
jtf_task_references_b r,
jtf_tasks_b t
WHERE fnd1.lookup_type = 'JTF_EC_ESC_LEVEL'and
fnd1.lookup_code = t.escalation_level and
v_incident_id = r.OBJECT_ID and
r.object_type_code = 'SR' and
r.reference_code = 'ESC' and
r.task_id = t.task_id and
t.task_type_id = 22;
Select timezone_code
From fnd_timezones_b
Where upgrade_tz_id = l_time_zone_id;
l_query := 'SELECT
sr.incident_number Incident_Number
,(SELECT type.name
FROM cs_incident_types_tl type
WHERE type.incident_type_id = sr.incident_type_id
AND type.language = userenv(''LANG'')) Incident_Type
,(SELECT type.detailed_erecord_req_flag
FROM cs_incident_types_b type
WHERE type.incident_type_id = sr.incident_type_id) Detailed_Erecord
,(SELECT status.name
FROM cs_incident_statuses_tl status
WHERE status.incident_status_id = sr.incident_status_id
AND status.language = userenv(''LANG'') )Incident_Status
,(SELECT sev.name
FROM cs_incident_severities_tl sev
WHERE sev.incident_severity_id = sr.incident_severity_id
AND sev.language = userenv(''LANG''))Incident_Severity
,(SELECT sev.importance_level
FROM cs_incident_severities_b sev
WHERE sev.incident_severity_id = sr.incident_severity_id
) Sev_Importance_Level
,(SELECT urgency.name
FROM cs_incident_urgencies_tl urgency
WHERE urgency.incident_urgency_id = sr.incident_urgency_id
AND urgency.language = userenv(''LANG'')) Incident_Urgency
,(SELECT gr.group_name
FROM jtf_rs_groups_tl gr
WHERE gr.group_id = sr.owner_group_id
AND gr.LANGUAGE = userenv(''LANG''))Sr_Group
,(SELECT rs.resource_name
FROM jtf_rs_resource_extns_tl rs
WHERE rs.resource_id = sr.incident_owner_id
AND rs.language = userenv(''LANG'') )Sr_Owner
,(SELECT problem.meaning
FROM fnd_lookup_values problem
WHERE problem.lookup_code = sr.problem_code
AND problem.lookup_type = ''REQUEST_PROBLEM_CODE''
AND problem.LANGUAGE = userenv(''LANG'')
AND problem.View_APPLICATION_ID = 170
AND problem.SECURITY_GROUP_ID = fnd_global.lookup_security_group(problem.LOOKUP_TYPE, problem.VIEW_APPLICATION_ID) ) Problem_code
,(SELECT resolution.meaning
FROM fnd_lookup_values resolution
WHERE resolution.lookup_code = sr.resolution_code
AND resolution.lookup_type = ''REQUEST_RESOLUTION_CODE''
AND resolution.LANGUAGE = userenv(''LANG'')
AND resolution.View_APPLICATION_ID = 170
AND resolution.SECURITY_GROUP_ID = fnd_global.lookup_security_group(resolution.LOOKUP_TYPE,resolution.VIEW_APPLICATION_ID)) Resolution_code
,tl.summary Problem_summary
,tl.resolution_summary Resolution_Summary
FROM cs_incidents_b_sec sr, cs_incidents_all_tl tl
WHERE sr.incident_id = tl.incident_id
AND tl.language = userenv(''lang'')
AND sr.incident_id = :INCIDENT_ID' ;
,l_Last_Update_Date
,l_Incident_Date
,l_Close_Date
,l_incident_resolved_date
,l_respond_by_date
,l_resolve_by_date
,l_inc_responded_by_date
,l_Actual_Resolution_Date
,l_Status_Flag_Code
,l_Created_By_Id
,l_Customer_Product_Id
,l_Organization_Id
,l_Inventory_Org_Id
,l_Instance_Number
,l_Serial_Number
,l_Tag_Number
,l_Item_Revision
,l_Component_Revision
,l_Sub_Component_Revision
,l_Attribute1
,l_Attribute2
,l_Attribute3
,l_Attribute4
,l_Attribute5
,l_Attribute6
,l_Attribute7
,l_Attribute8
,l_Attribute9
,l_Attribute10
,l_Attribute11
,l_Attribute12
,l_Attribute13
,l_Attribute14
,l_Attribute15
,l_Incident_Context
,l_Ext_Attribute1
,l_Ext_Attribute2
,l_Ext_Attribute3
,l_Ext_Attribute4
,l_Ext_Attribute5
,l_Ext_Attribute6
,l_Ext_Attribute7
,l_Ext_Attribute8
,l_Ext_Attribute9
,l_Ext_Attribute10
,l_Ext_Attribute11
,l_Ext_Attribute12
,l_Ext_Attribute13
,l_Ext_Attribute14
,l_Ext_Attribute15
,l_Ext_Context
,l_sr_creation_channel
,l_contract_service_id
,l_category_id
,l_system_id
,l_inv_component_id
,l_cp_subcomponent_id
,l_resource_type_code
,l_incident_LOCATION_ID
,l_incident_addr
,l_incident_city
,l_incident_state
,l_incident_province
,l_incident_postal_code
,l_incident_country
,l_instance_inventory_item_id
,l_instance_id
,l_cp_component_id
,l_inv_subcomponent_id
,l_incident_location_type;
'SELECT
:INCIDENT_ID AS INCIDENT_ID
,:INCIDENT_NUMBER AS INCIDENT_NUMBER
,:INCIDENT_TYPE_ID AS INCIDENT_TYPE_ID
,:INCIDENT_TYPE AS INCIDENT_TYPE
,:DETAILED_ERECORD AS DETAILED_ERECORD
,:INCIDENT_STATUS_ID AS INCIDENT_STATUS_ID
,:INCIDENT_STATUS AS INCIDENT_STATUS
,:INCIDENT_SEVERITY_ID AS INCIDENT_SEVERITY_ID
,:INCIDENT_SEVERITY AS INCIDENT_SEVERITY
,:SEV_IMPORTANCE_LEVEL AS SEV_IMPORTANCE_LEVEL
,:INCIDENT_URGENCY_ID AS INCIDENT_URGENCY_ID
,:INCIDENT_URGENCY AS INCIDENT_URGENCY
,:SR_GROUP_ID AS SR_GROUP_ID
,:SR_GROUP AS SR_GROUP
,:SR_OWNER_ID AS SR_OWNER_ID
,:SR_OWNER AS SR_OWNER
,:PROBLEM_CODE_ID AS PROBLEM_CODE_ID
,:PROBLEM_CODE AS PROBLEM_CODE
,:RESOLUTION_CODE_ID AS RESOLUTION_CODE_ID
,:RESOLUTION_CODE AS RESOLUTION_CODE
,:SUMMARY AS SUMMARY
,:RESOLUTION_SUMMARY AS resolution_summary
,:PUBLISH_FLAG AS PUBLISH_FLAG
,:SR_CREATION_CHANNEL AS SR_CREATION_CHANNEL
,:PROBLEM_SUMMARY AS PROBLEM_SUMMARY
,:RESOLUTION_SUMMARY AS RESOLUTION_SUMMARY
,:CREATED_BY AS CREATED_BY
,:CONTACT_TYPE AS CONTACT_TYPE
,:CONTACT_NAME AS CONTACT_NAME
,:TIMEZONE_ID AS TIMEZONE_ID
,:TIMEZONE_NAME AS TIMEZONE_NAME
,:CUSTOMER_ID AS CUSTOMER_ID
,:CUSTOMER_NUMBER AS CUSTOMER_NUMBER
,:CUSTOMER_NAME AS CUSTOMER_NAME
,:ACCOUNT_ID AS ACCOUNT_ID
,:ACCOUNT_NUMBER AS ACCOUNT_NUMBER
,:INVENTORY_ITEM_ID AS INVENTORY_ITEM_ID
,:PRODUCT AS PRODUCT
,:PRODUCT_DESCRIPTION AS PRODUCT_DESCRIPTION
,:DISPLAY_TIMEZONE AS DISPLAY_TIMEZONE
,:REPORTED_DATE AS REPORTED_DATE
,:LAST_UPDATE_DATE AS LAST_UPDATE_DATE
,:INCIDENT_DATE AS INCIDENT_DATE
,:CLOSE_DATE AS CLOSE_DATE
,:INCIDENT_RESOLVED_DATE AS INCIDENT_RESOLVED_DATE
,:RESPOND_BY_DATE AS RESPOND_BY_DATE
,:RESOLVE_BY_DATE AS RESOLVE_BY_DATE
,:INC_RESPONDED_BY_DATE AS INC_RESPONDED_BY_DATE
,:ACTUAL_RESOLUTION_DATE AS ACTUAL_RESOLUTION_DATE
,:STATUS_SORT_ORDER AS STATUS_SORT_ORDER
,:STATUS_FLAG_CODE AS STATUS_FLAG_CODE
,:CREATED_BY_ID AS CREATED_BY_ID
,:CUSTOMER_PRODUCT_ID AS CUSTOMER_PRODUCT_ID
,:ORGANIZATION_ID AS ORGANIZATION_ID
,:INVENTORY_ORG_ID AS INVENTORY_ORG_ID
,:CUSTOMER_PHONE AS CUSTOMER_PHONE
,:CUSTOMER_EMAIL AS CUSTOMER_EMAIL
,:CUSTOMER_TYPE AS CUSTOMER_TYPE
,:CONTRACT_NUMBER AS CONTRACT_NUMBER
,:CONTRACT_SERVICE AS CONTRACT_SERVICE
,:CONTRACT_COVERAGE AS CONTRACT_COVERAGE
,:CONTACT_PHONE_NUMBER AS CONTACT_PHONE_NUMBER
,:CONTACT_EMAIL AS CONTACT_EMAIL
,:CONTACT_TELEPHONE_TYPE AS CONTACT_TELEPHONE_TYPE
,:ITEM_CATEGORY AS ITEM_CATEGORY
,:INSTANCE_NUMBER AS INSTANCE_NUMBER
,:SERIAL_NUMBER AS SERIAL_NUMBER
,:TAG_NUMBER AS TAG_NUMBER
,:SYSTEM_NUMBER AS SYSTEM_NUMBER
,:COMPONENT AS COMPONENT
,:SUB_COMPONENT AS SUB_COMPONENT
,:ITEM_REVISION AS ITEM_REVISION
,:COMPONENT_REVISION AS COMPONENT_REVISION
,:SUB_COMPONENT_REVISION AS SUB_COMPONENT_REVISION
,:INCIDENT_ADDRESS AS INCIDENT_ADDRESS
,:RESOURCE_TYPE AS RESOURCE_TYPE
,:ATTRIBUTE1 AS ATTRIBUTE1
,:ATTRIBUTE2 AS ATTRIBUTE2
,:ATTRIBUTE3 AS ATTRIBUTE3
,:ATTRIBUTE4 AS ATTRIBUTE4
,:ATTRIBUTE5 AS ATTRIBUTE5
,:ATTRIBUTE6 AS ATTRIBUTE6
,:ATTRIBUTE7 AS ATTRIBUTE7
,:ATTRIBUTE8 AS ATTRIBUTE8
,:ATTRIBUTE9 AS ATTRIBUTE9
,:ATTRIBUTE10 AS ATTRIBUTE10
,:ATTRIBUTE11 AS ATTRIBUTE11
,:ATTRIBUTE12 AS ATTRIBUTE12
,:ATTRIBUTE13 AS ATTRIBUTE13
,:ATTRIBUTE14 AS ATTRIBUTE14
,:ATTRIBUTE15 AS ATTRIBUTE15
,:INCIDENT_CONTEXT AS INCIDENT_CONTEXT
,:EXT_ATTRIBUTE1 AS EXT_ATTRIBUTE1
,:EXT_ATTRIBUTE2 AS EXT_ATTRIBUTE2
,:EXT_ATTRIBUTE3 AS EXT_ATTRIBUTE3
,:EXT_ATTRIBUTE4 AS EXT_ATTRIBUTE4
,:EXT_ATTRIBUTE5 AS EXT_ATTRIBUTE5
,:EXT_ATTRIBUTE6 AS EXT_ATTRIBUTE6
,:EXT_ATTRIBUTE7 AS EXT_ATTRIBUTE7
,:EXT_ATTRIBUTE8 AS EXT_ATTRIBUTE8
,:EXT_ATTRIBUTE9 AS EXT_ATTRIBUTE9
,:EXT_ATTRIBUTE10 AS EXT_ATTRIBUTE10
,:EXT_ATTRIBUTE11 AS EXT_ATTRIBUTE11
,:EXT_ATTRIBUTE12 AS EXT_ATTRIBUTE12
,:EXT_ATTRIBUTE13 AS EXT_ATTRIBUTE13
,:EXT_ATTRIBUTE14 AS EXT_ATTRIBUTE14
,:EXT_ATTRIBUTE15 AS EXT_ATTRIBUTE15
,:EXT_CONTEXT AS EXT_CONTEXT
,:ESCALATION AS ESCALATION
, CS_ERES_INT_PKG.Get_SR_Tasks (:INCIDENT_ID, :L_SOURCE_TIMEZONE_ID, :L_DESC_TIMEZONE_ID, :L_DATE_FORMAT) AS TASK_LIST
, CS_ERES_INT_PKG.Get_SR_Notes (:incident_id, :l_source_timezone_id, :l_desc_timezone_id, :l_date_format) NOTE_LIST
, CS_ERES_INT_PKG.Get_Related_SRs(:incident_id) RELATED_SR_LIST
, CS_ERES_INT_PKG.Get_Related_Objs(:incident_id) RELATED_OB_LIST
FROM DUAL';
DBMS_XMLGEN.setBindValue(Ctx1,'Last_Update_Date', nvl(l_Last_Update_Date,' '));
SELECT COUNT(1)
INTO l_ext_rec_count
FROM CS_INCIDENTS_EXT
WHERE incident_id = p_incident_id;
SELECT object_version_number , incident_status_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id
FOR UPDATE;
l_sr_update_out_rec CS_ServiceRequest_PVT.sr_update_out_rec_type;
l_servicerequest_Rec.last_update_program_code := 'ERES';
dbg_msg := ('Calling CS_ServiceRequest_PVT.Update_ServiceRequest (1)');
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 4.0,
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_audit_id => null,
p_object_version_number => l_sr_version,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_service_request_rec => l_servicerequest_Rec,
p_notes => l_notes_table_dummy,
p_contacts => l_contacts_table,
p_validate_sr_closure => NVL(l_validate_sr_close,'N'),
p_auto_close_child_entities => NVL(l_validate_sr_close,'N'),
x_sr_update_out_rec => l_sr_update_out_rec );
dbg_msg := ('After Calling CS_ServiceRequest_PVT.Update_ServiceRequest');
dbg_msg := ('CS_ServiceRequest_PVT.Update_ServiceRequest Return Status : '||l_return_status);
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => sysdate
, p_created_by => fnd_global.user_id
, p_last_update_login => fnd_global.login_id
, p_note_type => l_note_type
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_jtf_note_id => l_note_id );
dbg_msg := ('Update Service Request(1) API Error ');
dbg_msg := ('Update Service Request(1) API Error ');
dbg_msg := ('Calling CS_ServiceRequest_PVT.Update_ServiceRequest (2)');
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 4.0,
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_audit_id => null,
p_object_version_number => l_sr_version,
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_service_request_rec => l_servicerequest_Rec,
p_notes => l_notes_table_dummy,
p_contacts => l_contacts_table,
p_validate_sr_closure => NVL(l_validate_sr_close,'N'),
p_auto_close_child_entities => NVL(l_validate_sr_close,'N'),
x_sr_update_out_rec => l_sr_update_out_rec );
dbg_msg := ('After Calling CS_ServiceRequest_PVT.Update_ServiceRequest (2)');
dbg_msg := ('CS_ServiceRequest_PVT.Update_ServiceRequest Return Status : '||l_return_status);
, p_last_update_date => sysdate
, p_last_updated_by => fnd_global.user_id
, p_creation_date => sysdate
, p_created_by => fnd_global.user_id
, p_last_update_login => fnd_global.login_id
, p_note_type => l_note_type
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_jtf_note_id => l_note_id );
END IF ; -- End if for the return status of the Update SR call (2)
SELECT incident_status_id ,
approval_action_status_id ,
rejection_action_status_id
FROM cs_incident_statuses a
WHERE a.intermediate_status_id = P_Intermediate_Status_Id;
SELECT old_incident_status_id
FROM cs_incidents_audit_b
WHERE rowid = ( SELECT max(rowid)
FROM cs_incidents_audit_b
WHERE incident_id = p_incident_id
AND incident_status_id = p_intermediate_status_id
AND old_incident_status_id <> p_intermediate_status_id
GROUP BY incident_id , incident_status_id);