[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
business_process_id
, autolaunch_workflow_flag
, abort_workflow_close_flag
, workflow
INTO
x_business_process_id
, x_autolaunch_workflow_flag
, x_abort_workflow_close_flag
, x_workflow
FROM
cs_incident_types_b
WHERE incident_type_id = p_incident_type_id;
, p_last_updated_by IN NUMBER
, p_validation_level IN NUMBER DEFAULT NULL
, p_default_contract_sla_ind IN VARCHAR2
, p_default_coverage_template_id IN NUMBER DEFAULT NULL
, p_auto_assign IN VARCHAR2
, p_old_incident_location_id IN NUMBER DEFAULT NULL
, p_old_incident_location_type IN VARCHAR2 DEFAULT NULL
, p_old_incident_country IN VARCHAR2 DEFAULT NULL
, p_old_incident_owner_id IN NUMBER DEFAULT NULL
, p_old_owner_group_id IN NUMBER DEFAULT NULL
, p_old_resource_type IN VARCHAR2 DEFAULT NULL
, p_old_site_id IN NUMBER DEFAULT NULL
, p_old_obligation_date IN DATE DEFAULT NULL
, p_old_expected_resolution_date IN DATE DEFAULT NULL
, p_old_contract_id IN NUMBER DEFAULT NULL
, p_old_contract_service_id IN NUMBER DEFAULT NULL
, p_old_install_site_id IN NUMBER DEFAULT NULL
, p_old_system_id IN NUMBER DEFAULT NULL
, p_old_account_id IN NUMBER DEFAULT NULL
, p_old_inventory_item_id IN NUMBER DEFAULT NULL
, p_old_customer_product_id IN NUMBER DEFAULT NULL
, p_old_incident_type_id IN NUMBER DEFAULT NULL
, p_old_time_zone_id IN NUMBER DEFAULT NULL
, p_old_incident_severity_id IN NUMBER DEFAULT NULL
, x_contract_number IN OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_return_status VARCHAR2(1);
NVL(p_sr_related_data.old_disallow_request_update,'N') = 'N'
THEN
l_auto_assign_level := fnd_profile.value('CS_SR_OWNER_AUTO_ASSIGN_LEVEL');
IF ((l_auto_assign_level = 'INDIVIDUAL' AND NVL(p_sr_related_data.old_disallow_owner_update,'N') = 'N') OR
(l_auto_assign_level = 'GROUP' AND p_sr_rec.owner_group_id is null))
THEN
p_sr_rec.load_balance := 'Y';
, p_last_updated_by => p_last_updated_by
, p_service_request_rec => p_sr_rec
, x_owner_id => l_asgn_owner_id
, x_owner_type => l_asgn_resource_type
, x_territory_id => l_territory_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_owner_group_id => l_asgn_owner_group_id
);
SELECT
timezone_id
FROM
hz_contact_points
WHERE contact_point_id = p_contact_point_id;
SELECT
loc.timezone_id
FROM
hz_party_sites site
, hz_locations loc
WHERE site.location_id = loc.location_id
AND site.party_id = p_contact_party_id
AND site.identifying_address_flag = 'Y'
AND site.status = 'A';
UPDATE CS_INCIDENTS_ALL_B
SET time_zone_id = l_timezone_id
WHERE incident_id = p_incident_id;
select business_usage
into x_business_usage
from cs_service_responsibility
where responsibility_id = p_responsibility_id
and application_id = p_application_id;
select ss_srtype_restrict
into x_ss_sr_type_restrict
from cs_system_options;
PROCEDURE Update_SR_Validation
( p_api_version IN VARCHAR2,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_service_request_rec IN service_request_rec_type,
p_invocation_mode IN VARCHAR2 := 'NORMAL',
p_notes IN notes_table,
p_contacts IN contacts_table,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_resp_id IN NUMBER DEFAULT NULL,
p_resp_appl_id IN NUMBER DEFAULT NULL,
p_request_id IN NUMBER,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_contra_id OUT NOCOPY NUMBER,
x_contract_number OUT NOCOPY VARCHAR2,
x_owner_assigned_flag OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_audit_vals_rec OUT NOCOPY sr_audit_rec_type,
x_service_request_rec OUT NOCOPY service_request_rec_type,
x_autolaunch_wkf_flag OUT NOCOPY VARCHAR2,
x_abort_wkf_close_flag OUT NOCOPY VARCHAR2,
x_wkf_process_name OUT NOCOPY VARCHAR2,
x_workflow_process_id OUT NOCOPY NUMBER,
x_interaction_id OUT NOCOPY NUMBER,
p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
p_cmro_flag IN VARCHAR2,
p_maintenance_flag IN VARCHAR2,
p_auto_assign IN VARCHAR2 := 'N'
);
p_disallow_request_update OUT NOCOPY VARCHAR2,
p_disallow_owner_update OUT NOCOPY VARCHAR2,
p_disallow_product_update OUT NOCOPY VARCHAR2,
p_employee_name OUT NOCOPY VARCHAR2,
p_inventory_item_id OUT NOCOPY NUMBER,
p_contract_id OUT NOCOPY NUMBER,
p_contract_number OUT NOCOPY VARCHAR2,
x_bill_to_site_id OUT NOCOPY NUMBER,
x_ship_to_site_id OUT NOCOPY NUMBER,
x_bill_to_site_use_id OUT NOCOPY NUMBER,
x_ship_to_site_use_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_group_name OUT NOCOPY VARCHAR2,
x_owner_name OUT NOCOPY VARCHAR2,
x_product_revision OUT NOCOPY VARCHAR2,
x_component_version OUT NOCOPY VARCHAR2,
x_subcomponent_version OUT NOCOPY VARCHAR2,
p_cmro_flag IN VARCHAR2,
p_maintenance_flag IN VARCHAR2,
p_sr_mode IN VARCHAR2
);
p_update_desc_flex VARCHAR2(1);
IF (l_service_request_rec.last_update_channel in ('PHONE', 'AGENT') AND
l_imp_level =1 AND
l_service_request_rec.owner_id IS NOT NULL AND
l_service_request_rec.owner_id <> FND_API.G_MISS_NUM) OR
(l_service_request_rec.last_update_channel = 'WEB' AND
(l_imp_level =1 OR l_imp_level =2) AND
l_service_request_rec.owner_id IS NOT NULL AND
l_service_request_rec.owner_id <> FND_API.G_MISS_NUM) THEN
l_send_uwq_notification := TRUE;
SELECT responded_flag,resolved_flag
INTO l_responded_flag,l_resolved_flag
FROM cs_incident_statuses_vl
WHERE incident_status_id=l_service_request_rec.status_id ;
, p_last_updated_by => p_user_id
, p_validation_level => p_validation_level
, p_default_contract_sla_ind => p_default_contract_sla_ind
, p_default_coverage_template_id => p_default_coverage_template_id
, p_auto_assign => p_auto_assign
, x_contract_number => l_contract_number
, x_return_status => l_return_status
);
INSERT INTO cs_incidents_all_b
( incident_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
incident_number,
incident_status_id,
incident_type_id,
incident_urgency_id,
incident_severity_id,
incident_owner_id,
resource_type,
-- resource_subtype_id, -- For BUG 2748584
inventory_item_id,
caller_type,
customer_id,
account_id,
employee_id,
current_serial_number,
expected_resolution_date,
actual_resolution_date,
customer_product_id,
bill_to_site_use_id,
bill_to_contact_id,
ship_to_site_use_id,
ship_to_contact_id,
install_site_use_id,
incident_attribute_1,
incident_attribute_2,
incident_attribute_3,
incident_attribute_4,
incident_attribute_5,
incident_attribute_6,
incident_attribute_7,
incident_attribute_8,
incident_attribute_9,
incident_attribute_10,
incident_attribute_11,
incident_attribute_12,
incident_attribute_13,
incident_attribute_14,
incident_attribute_15,
incident_context,
external_attribute_1,
external_attribute_2,
external_attribute_3,
external_attribute_4,
external_attribute_5,
external_attribute_6,
external_attribute_7,
external_attribute_8,
external_attribute_9,
external_attribute_10,
external_attribute_11,
external_attribute_12,
external_attribute_13,
external_attribute_14,
external_attribute_15,
external_context,
resolution_code,
incident_date,
problem_code,
original_order_number,
purchase_order_num,
close_date,
publish_flag,
obligation_date,
qa_collection_id,
contract_service_id,
contract_id,
contract_number,
project_number,
customer_po_number,
customer_ticket_number,
time_zone_id,
time_difference,
platform_id,
platform_version,
platform_version_id,
db_version,
cp_component_id,
cp_component_version_id,
cp_subcomponent_id,
cp_subcomponent_version_id,
cp_revision_id,
inv_item_revision,
inv_component_id,
inv_component_version,
inv_subcomponent_id,
inv_subcomponent_version,
site_id,
customer_site_id,
territory_id,
inv_organization_id,
object_version_number,
-----Added for enhancement 11.5.6
cust_pref_lang_id,
tier,
tier_version,
operating_system,
operating_system_version,
DATABASE,
category_id,
group_type,
owner_group_id,
group_territory_id,
owner_assigned_time,
owner_assigned_flag,
--- Added for the unassigned_ind enh.
unassigned_indicator,
inv_platform_org_id,
product_revision,
component_version,
subcomponent_version,
comm_pref_code,
cust_pref_lang_code,
last_update_channel,
category_set_id,
external_reference,
system_id,
error_code,
incident_occurred_date,
incident_resolved_date,
inc_responded_by_date,
incident_location_id ,
incident_address ,
incident_city,
incident_state,
incident_country,
incident_province ,
incident_postal_code ,
incident_county,
sr_creation_channel ,
-- Added for ER# 2320056
coverage_type ,
-- Added For ER# 2433831
bill_to_account_id ,
ship_to_account_id ,
-- Added for ER# 2463321
customer_phone_id,
customer_email_id,
-- Added for source changes for 1159 by shijain oct 11 2002
creation_program_code,
last_update_program_code,
bill_to_party_id,
ship_to_party_id,
bill_to_site_id,
ship_to_site_id,
program_id ,
program_application_id ,
request_id ,
program_login_id ,
-- Added for enh 2655115
status_flag,
-- Added address field by shijain dec5th 2002
incident_point_of_interest ,
incident_cross_street ,
incident_direction_qualifier ,
incident_distance_qualifier ,
incident_distance_qual_uom ,
incident_address2 ,
incident_address3 ,
incident_address4 ,
incident_address_style ,
incident_addr_lines_phonetic ,
incident_po_box_number ,
incident_house_number ,
incident_street_suffix ,
incident_street ,
incident_street_number ,
incident_floor,
incident_suite ,
incident_postal_plus4_code ,
incident_position ,
incident_location_directions,
incident_location_description ,
install_site_id,
-- for cmro_eam
owning_department_id,
-- Added for MIsc ERs project of 11.5.10 --anmukher --08/26/03
incident_location_type,
-- Added for Auditing project of 11.5.10 --anmukher --09/03/03
incident_last_modified_date ,
maint_organization_id,
org_id
)
VALUES
( DECODE(l_request_id,NULL,l_req_id,l_request_id), /* INCIDENT_ID */
l_service_request_rec.last_update_date, /* LAST_UPDATE_DATE */
l_service_request_rec.last_updated_by, /* LAST_UPDATED_BY */
l_service_request_rec.creation_date, /* CREATION_DATE */
l_service_request_rec.created_by, /* CREATED_BY */
l_service_request_rec.last_update_login, /* LAST_UPDATE_LOGIN */
DECODE(l_request_number,NULL,l_req_num,l_request_number), /* INCIDENT_NUMBER */
l_service_request_rec.status_id, /* INCIDENT_STATUS_ID */
l_service_request_rec.type_id, /* INCIDENT_TYPE_ID */
l_service_request_rec.urgency_id, /* INCIDENT_URGENCY_ID */
l_service_request_rec.severity_id, /* INCIDENT_SEVERITY_ID */
l_service_request_rec.owner_id, /* INCIDENT_OWNER_ID */
l_service_request_rec.resource_type, /* RESOURCE_TYPE */
-- l_service_request_rec.resource_subtype_id, /* RESOURCE_SUBTYPE_ID */ For BUG 2748584
l_service_request_rec.inventory_item_id, /* INVENTORY_ITEM_ID */
l_service_request_rec.caller_type, /* CALLER_TYPE */
-- removed decode for 11.5.6 enhancement
l_service_request_rec.customer_id, /* CUSTOMER_ID */
l_service_request_rec.account_id, /* ACCOUNT_ID */
l_service_request_rec.employee_id, /* EMPLOYEE_ID */
l_service_request_rec.current_serial_number, /* CURRENT_SERIAL_NUMBER */
l_service_request_rec.exp_resolution_date, /* EXPECTED_RESOLUTION_DATE */
l_service_request_rec.act_resolution_date, /* ACTUAL_RESOLUTION_DATE */
l_service_request_rec.customer_product_id, /* CUSTOMER_PRODUCT_ID */
l_service_request_rec.bill_to_site_use_id, /* BILL_TO_SITE_USE_ID */
l_service_request_rec.bill_to_contact_id, /* BILL_TO_CONTACT_ID */
l_service_request_rec.ship_to_site_use_id, /* SHIP_TO_SITE_USE_ID */
l_service_request_rec.ship_to_contact_id, /* SHIP_TO_CONTACT_ID */
l_service_request_rec.install_site_use_id, /* INSTALL_SITE_USE_ID */
l_service_request_rec.request_attribute_1, /* INCIDENT_ATTRIBUTE_1 */
l_service_request_rec.request_attribute_2, /* INCIDENT_ATTRIBUTE_2 */
l_service_request_rec.request_attribute_3, /* INCIDENT_ATTRIBUTE_3 */
l_service_request_rec.request_attribute_4, /* INCIDENT_ATTRIBUTE_4 */
l_service_request_rec.request_attribute_5, /* INCIDENT_ATTRIBUTE_5 */
l_service_request_rec.request_attribute_6, /* INCIDENT_ATTRIBUTE_6 */
l_service_request_rec.request_attribute_7, /* INCIDENT_ATTRIBUTE_7 */
l_service_request_rec.request_attribute_8, /* INCIDENT_ATTRIBUTE_8 */
l_service_request_rec.request_attribute_9, /* INCIDENT_ATTRIBUTE_9 */
l_service_request_rec.request_attribute_10, /* INCIDENT_ATTRIBUTE_10 */
l_service_request_rec.request_attribute_11, /* INCIDENT_ATTRIBUTE_11 */
l_service_request_rec.request_attribute_12, /* INCIDENT_ATTRIBUTE_12 */
l_service_request_rec.request_attribute_13, /* INCIDENT_ATTRIBUTE_13 */
l_service_request_rec.request_attribute_14, /* INCIDENT_ATTRIBUTE_14 */
l_service_request_rec.request_attribute_15, /* INCIDENT_ATTRIBUTE_15 */
l_service_request_rec.request_context, /* INCIDENT_CONTEXT */
l_service_request_rec.external_attribute_1, /* EXTERNAL_ATTRIBUTE_1 */
l_service_request_rec.external_attribute_2, /* EXTERNAL_ATTRIBUTE_2 */
l_service_request_rec.external_attribute_3, /* EXTERNAL_ATTRIBUTE_3 */
l_service_request_rec.external_attribute_4, /* EXTERNAL_ATTRIBUTE_4 */
l_service_request_rec.external_attribute_5, /* EXTERNAL_ATTRIBUTE_5 */
l_service_request_rec.external_attribute_6, /* EXTERNAL_ATTRIBUTE_6 */
l_service_request_rec.external_attribute_7, /* EXTERNAL_ATTRIBUTE_7 */
l_service_request_rec.external_attribute_8, /* EXTERNAL_ATTRIBUTE_8 */
l_service_request_rec.external_attribute_9, /* EXTERNAL_ATTRIBUTE_9 */
l_service_request_rec.external_attribute_10, /* EXTERNAL_ATTRIBUTE_10 */
l_service_request_rec.external_attribute_11, /* EXTERNAL_ATTRIBUTE_11 */
l_service_request_rec.external_attribute_12, /* EXTERNAL_ATTRIBUTE_12 */
l_service_request_rec.external_attribute_13, /* EXTERNAL_ATTRIBUTE_13 */
l_service_request_rec.external_attribute_14, /* EXTERNAL_ATTRIBUTE_14 */
l_service_request_rec.external_attribute_15, /* EXTERNAL_ATTRIBUTE_15 */
l_service_request_rec.external_context, /* EXTERNAL_CONTEXT */
UPPER(l_service_request_rec.resolution_code), /* RESOLUTION_CODE */
l_service_request_rec.request_date, /* INCIDENT_DATE */
UPPER(l_service_request_rec.problem_code), /* PROBLEM_CODE */
l_service_request_rec.original_order_number, /* ORIGINAL_ORDER_NUMBER */
l_service_request_rec.purchase_order_num, /* PURCHASE_ORDER_NUM */
l_service_request_rec.closed_date, /* CLOSE_DATE */
l_service_request_rec.publish_flag, /* PUBLISH_FLAG */
l_service_request_rec.obligation_date,
l_service_request_rec.qa_collection_plan_id,
l_service_request_rec.contract_service_id,
l_contra_id,
l_contract_number,
l_service_request_rec.project_number,
l_service_request_rec.cust_po_number,
l_service_request_rec.cust_ticket_number,
l_service_request_rec.time_zone_id,
l_service_request_rec.time_difference,
l_service_request_rec.platform_id,
l_service_request_rec.platform_version,
l_service_request_rec.platform_version_id,
l_service_request_rec.db_version,
l_service_request_rec.cp_component_id,
l_service_request_rec.cp_component_version_id,
l_service_request_rec.cp_subcomponent_id,
l_service_request_rec.cp_subcomponent_version_id,
l_service_request_rec.cp_revision_id,
l_service_request_rec.inv_item_revision,
l_service_request_rec.inv_component_id,
l_service_request_rec.inv_component_version,
l_service_request_rec.inv_subcomponent_id,
l_service_request_rec.inv_subcomponent_version,
l_service_request_rec.site_id,
l_service_request_rec.customer_site_id,
l_service_request_rec.territory_id,
l_service_request_rec.inventory_org_id,
1,
--- Added for enhancement 11.5.6
l_service_request_rec.cust_pref_lang_id,
l_service_request_rec.tier,
l_service_request_rec.tier_version,
l_service_request_rec.operating_system,
l_service_request_rec.operating_system_version,
l_service_request_rec.DATABASE,
l_service_request_rec.category_id,
l_service_request_rec.group_type,
l_service_request_rec.owner_group_id,
l_service_request_rec.group_territory_id,
l_service_request_rec.owner_assigned_time,
l_service_request_rec.owner_assigned_flag,
l_unassigned_indicator,
l_service_request_rec.inv_platform_org_id,
l_service_request_rec.product_revision,
l_service_request_rec.component_version,
l_service_request_rec.subcomponent_version,
l_service_request_rec.comm_pref_code,
l_service_request_rec.cust_pref_lang_code,
l_service_request_rec.last_update_channel,
l_service_request_rec.category_set_id,
l_service_request_rec.external_reference,
l_service_request_rec.system_id,
l_service_request_rec.error_code,
l_service_request_rec.incident_occurred_date,
l_service_request_rec.incident_resolved_date,
l_service_request_rec.inc_responded_by_date,
l_service_request_rec.incident_location_id ,
l_service_request_rec.incident_address ,
l_service_request_rec.incident_city,
l_service_request_rec.incident_state,
l_service_request_rec.incident_country,
l_service_request_rec.incident_province ,
l_service_request_rec.incident_postal_code ,
l_service_request_rec.incident_county,
l_service_request_rec.sr_creation_channel ,
-- Added for ER# 2320056
l_service_request_rec.coverage_type,
-- Added For ER# 2433831
l_service_request_rec.bill_to_account_id ,
l_service_request_rec.ship_to_account_id ,
-- Added for ER# 2463321
l_service_request_rec.customer_phone_id,
l_service_request_rec.customer_email_id,
-- Added for source changes for 1159 by shijain oct 11 2002
l_service_request_rec.creation_program_code,
l_service_request_rec.creation_program_code,
l_service_request_rec.bill_to_party_id,
l_service_request_rec.ship_to_party_id,
l_service_request_rec.bill_to_site_id,
l_service_request_rec.ship_to_site_id,
l_service_request_rec.program_id ,
l_service_request_rec.program_application_id ,
l_service_request_rec.conc_request_id ,
l_service_request_rec.program_login_id ,
-- Added for enh. 2655115
--l_status_flag ,
--for bug 3050727
l_service_request_rec.status_flag,
l_service_request_rec.incident_point_of_interest,
l_service_request_rec.incident_cross_street,
l_service_request_rec.incident_direction_qualifier,
l_service_request_rec.incident_distance_qualifier,
l_service_request_rec.incident_distance_qual_uom,
l_service_request_rec.incident_address2,
l_service_request_rec.incident_address3,
l_service_request_rec.incident_address4,
l_service_request_rec.incident_address_style,
l_service_request_rec.incident_addr_lines_phonetic,
l_service_request_rec.incident_po_box_number,
l_service_request_rec.incident_house_number,
l_service_request_rec.incident_street_suffix,
l_service_request_rec.incident_street,
l_service_request_rec.incident_street_number,
l_service_request_rec.incident_floor,
l_service_request_rec.incident_suite,
l_service_request_rec.incident_postal_plus4_code,
l_service_request_rec.incident_position,
l_service_request_rec.incident_location_directions,
l_service_request_rec.incident_location_description,
l_service_request_rec.install_site_id,
--for cmro_eam
l_service_request_rec.owning_dept_id,
-- Added for Misc ERs project of 11.5.10 --anmukher --08/26/03
l_service_request_rec.incident_location_type,
-- Added for Auditing project of 11.5.10 --anmukher --09/03/03
l_service_request_rec.last_update_date , /* INCIDENT_LAST_MODIFIED_DATE */
l_service_request_rec.maint_organization_id,
l_org_id
) RETURNING INCIDENT_ID, INCIDENT_NUMBER, ORG_ID INTO l_request_id, l_request_number, l_inc_org_id;
INSERT INTO cs_incidents_all_tl (
incident_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
summary,
resolution_summary ,
LANGUAGE,
source_lang,
text_index
) SELECT
l_request_id,
l_service_request_rec.creation_date,
l_service_request_rec.created_by,
l_service_request_rec.last_update_date,
l_service_request_rec.last_updated_by,
l_service_request_rec.last_update_login,
l_service_request_rec.summary,
l_service_request_rec.resolution_summary ,
L.LANGUAGE_CODE,
USERENV('LANG'),
'A'
FROM FND_LANGUAGES L
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM cs_incidents_all_tl t
WHERE t.incident_id = l_request_id
AND t.LANGUAGE = l.language_code
);
after we insert record in cs_hz_sr_contact_points, so that
sr_contact_point_id can be inserted as primary_contact_id by shijain
for enh. 2690787
l_primary_contact:= get_primary_contact(l_request_id);
UPDATE cs_incidents_all_b
SET primary_contact_id = l_primary_contact
WHERE incident_id = l_request_id;
/****************Delete Above code***************************************/
-- ------------------------------------------------------
-- Insert a record into the audit table
-- ------------------------------------------------------
--Added for Auditing project of 11.5.10 --anmukher --09/03/03
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := l_request_id;
l_audit_vals_rec.incident_last_modified_date := l_service_request_rec.last_update_date ;
l_audit_vals_rec.last_update_program_code := l_service_request_rec.creation_program_code ;
p_user_id => l_service_request_rec.last_updated_by,
p_login_id => l_service_request_rec.last_update_login,
p_last_update_date => l_service_request_rec.last_update_date,
p_creation_date => l_service_request_rec.last_update_date,
x_audit_id => l_audit_id
);
CS_SRCONTACT_PKG.create_update
( p_incident_id => l_request_id
, p_invocation_mode => p_invocation_mode
, p_sr_update_date => l_service_request_rec.last_update_date
, p_sr_updated_by => l_service_request_rec.last_updated_by
, p_sr_update_login => l_service_request_rec.last_update_login
, p_contact_tbl => l_processed_contacts
, p_old_contact_tbl => l_old_contacts
, x_return_status => l_return_status
);
p_update_desc_flex => p_update_desc_flex,
p_object_version_number => p_object_version_number,
p_transaction_type => l_transaction_type,
p_message_rev => l_message_revision,
p_servicerequest => l_service_request_rec,
p_contacts => l_contacts
);
p_last_updated_by => p_user_id,
p_last_update_date => SYSDATE,
x_jtf_note_id => l_note_id
);
p_last_updated_by => p_user_id,
p_last_update_date => SYSDATE,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_01,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_01,
x_note_context_id => l_note_context_id
);
p_last_updated_by => p_user_id,
p_last_update_date => SYSDATE,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_02,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_02,
x_note_context_id => l_note_context_id
);
p_last_updated_by => p_user_id,
p_last_update_date => SYSDATE,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_03,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_03,
x_note_context_id => l_note_context_id
);
SELECT cs_wf_process_id_s.NEXTVAL INTO l_workflow_item_key FROM dual;
-- rec. type used in the insert.
p_contacts_table => p_contacts,
p_link_rec => NULL, -- using default value
p_wf_process_id => NULL, -- using default value
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 => lx_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data );
p_user_id => l_service_request_rec.last_updated_by,
p_resp_appl_id => p_resp_appl_id ,
p_login_id => l_service_request_rec.last_update_login,
x_work_item_id => l_work_item_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data) ;
PROCEDURE Update_ServiceRequest
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_audit_id IN NUMBER ,
p_object_version_number IN NUMBER,
p_resp_appl_id IN NUMBER DEFAULT NULL,
p_resp_id IN NUMBER DEFAULT NULL,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_service_request_rec IN service_request_rec_type,
p_invocation_mode IN VARCHAR2 := 'NORMAL',
p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
p_notes IN notes_table,
p_contacts IN contacts_table,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
p_default_contract_sla_ind IN VARCHAR2 Default 'N',
x_workflow_process_id OUT NOCOPY NUMBER,
x_interaction_id OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 3.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_ServiceRequest';
l_sr_update_out_rec sr_update_out_rec_type;
SAVEPOINT Update_ServiceRequest_PVT;
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 4.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id,
p_audit_id => p_audit_id,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_service_request_rec => p_service_request_rec,
p_invocation_mode => p_invocation_mode,
p_update_desc_flex => p_update_desc_flex,
p_notes => p_notes,
p_contacts => p_contacts,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
p_auto_assign => 'N',
p_validate_sr_closure => 'N',
p_auto_close_child_entities => 'N',
p_default_contract_sla_ind => p_default_contract_sla_ind,
x_sr_update_out_rec => l_sr_update_out_rec
);
x_workflow_process_id := l_sr_update_out_rec.workflow_process_id;
x_interaction_id := l_sr_update_out_rec.interaction_id;
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
END Update_ServiceRequest;
PROCEDURE Update_ServiceRequest
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_audit_id IN NUMBER ,
p_object_version_number IN NUMBER,
p_resp_appl_id IN NUMBER DEFAULT NULL,
p_resp_id IN NUMBER DEFAULT NULL,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_service_request_rec IN service_request_rec_type,
p_invocation_mode IN VARCHAR2 := 'NORMAL',
p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
p_notes IN notes_table,
p_contacts IN contacts_table,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
-- Commented out since these are now part of the out rec type --anmukher--08/08/03
-- x_workflow_process_id OUT NOCOPY NUMBER,
-- x_interaction_id OUT NOCOPY NUMBER
----------------anmukher--------------------08/05/03
-- Added for 11.5.10 projects
p_auto_assign IN VARCHAR2 Default 'N',
p_validate_sr_closure IN VARCHAR2 Default 'N',
p_auto_close_child_entities IN VARCHAR2 Default 'N',
p_default_contract_sla_ind IN VARCHAR2 Default 'N',
x_sr_update_out_rec OUT NOCOPY sr_update_out_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ServiceRequest';
l_update_flag VARCHAR2(1);
Replacing the select list of columns with a select * so that the
subtype defined in the spec can be used to pass the old SR values as
a parameter to other procedures
* *****************/
-- This rec type was changed to sr_oldvalues_rec_type as the
-- workitem API (Misc ER owner Auto Assginment )needed a record type
-- with old values , also the API validations needed the oldvalues_rec .
CURSOR L_SERVICEREQUEST_CSR IS
SELECT *
from cs_incidents_all_vl
where incident_id = p_request_id
for update nowait;
l_update_desc_flex VARCHAR2(1) := p_update_desc_flex;
l_only_status_update_flag VARCHAR2(1) := 'N';
l_disallow_request_update VARCHAR2(1);
l_disallow_owner_update VARCHAR2(1);
l_disallow_product_update VARCHAR2(1);
l_party_id_update VARCHAR2(1);
l_contact_point_id_update VARCHAR2(1);
l_contact_point_type_update VARCHAR2(1);
l_contact_type_update VARCHAR2(1);
l_primary_flag_update VARCHAR2(1) ;
DestUpdated EXCEPTION;
NoUpdate EXCEPTION;
TargUpdated EXCEPTION;
l_last_updated_by_temp NUMBER;
SAVEPOINT Update_ServiceRequest_PVT;
, 'P_Last_updated_by:' || P_Last_updated_by
);
, 'P_Last_update_login:' || P_Last_update_login
);
, 'P_Last_update_date:' || P_Last_update_date
);
SELECT object_version_number INTO l_dummy
FROM cs_incidents_all_b
WHERE incident_id = p_request_id;
RAISE DestUpdated;
RAISE NoUpdate;
RAISE TargUpdated;
, 'CS_SERVICEREQUEST_PVT.update_servicerequest'
);
p_operation => 'UPDATE_OLD',
x_return_status => lx_return_status,
x_cmro_flag => l_old_cmro_flag,
x_maintenance_flag => l_old_maintenance_flag );
p_operation => 'UPDATE',
x_return_status => lx_return_status,
x_cmro_flag => l_new_cmro_flag,
x_maintenance_flag => l_new_maintenance_flag );
, 'CS_SERVICEREQUEST_PVT.update_servicerequest'
);
, x_disallow_request_update => l_sr_related_data.old_disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.old_disallow_owner_update
, x_disallow_product_update => l_sr_related_data.old_disallow_product_update
, x_pending_approval_flag => l_sr_related_data.old_pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.old_intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.old_approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.old_rejection_action_status_id
, x_return_status => l_return_status
);
, x_disallow_request_update => l_sr_related_data.disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.disallow_owner_update
, x_disallow_product_update => l_sr_related_data.disallow_product_update
, x_pending_approval_flag => l_sr_related_data.pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.rejection_action_status_id
, x_return_status => l_return_status
);
, 'The defaulted value of parameter disallow_request_update :'
|| l_sr_related_data.disallow_request_update
);
, 'The defaulted value of parameter disallow_owner_update :'
|| l_sr_related_data.disallow_owner_update
);
, 'The defaulted value of parameter disallow_product_update :'
|| l_sr_related_data.disallow_product_update
);
NVL(p_service_request_rec.last_update_program_code,'UNKOWN') <> 'ERES')
THEN
l_start_eres_flag := 'Y';
NVL(p_service_request_rec.last_update_program_code,'UNKOWN') <> 'ERES')
THEN
FND_MESSAGE.set_name ('CS', 'CS_SR_INTERMEDIATE_STATUS');
, 'CS_SERVICEREQUEST_PVT.update_servicerequest'
);
IF l_sr_related_data.old_disallow_request_update = 'Y' AND
l_sr_related_data.intermediate_status_id IS NOT NULL AND
NVL(p_service_request_rec.last_update_program_code,'UNKOWN') <> 'ERES'
THEN
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_ONLY_STATUS_UPDATED');
CS_ServiceRequest_UTIL.Add_Same_Val_Update_Msg
( p_token_an => 'CS_SERVICEREQUEST_PUB.update_servicerequest'
, p_token_p => 'p_status_id'
, p_table_name => G_TABLE_NAME
, p_column_name => 'INCIDENT_STATUS_ID'
);
l_last_updated_by_temp := l_service_request_rec.last_updated_by;
l_service_request_rec.last_updated_by := p_service_request_rec.last_updated_by;
IF (l_service_request_rec.last_updated_by IS NULL OR
l_service_request_rec.last_updated_by = FND_API.G_MISS_NUM) THEN
IF (p_last_updated_by IS NOT NULL AND
p_last_updated_by <>FND_API.G_MISS_NUM) THEN
l_service_request_rec.last_updated_by := p_last_updated_by;
'Update_ServiceRequest',
'B', 'C') THEN
-- hardcoded the version 2.0 shijain nov 27 2002
cs_servicerequest_cuhk.Update_ServiceRequest_Pre
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id ,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_invocation_mode => p_invocation_mode,
p_service_request_rec => l_service_request_rec,
p_update_desc_flex => p_update_desc_flex,
p_notes => p_notes,
p_contacts => p_contacts,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
x_workflow_process_id => x_sr_update_out_rec.workflow_process_id,
x_interaction_id => x_sr_update_out_rec.interaction_id);
'Update_ServiceRequest',
'B', 'V') THEN
-- hardcoded the version 2.0 shijain nov 27 2002
cs_servicerequest_vuhk.Update_ServiceRequest_Pre
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id ,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_service_request_rec => l_service_request_rec,
p_update_desc_flex => p_update_desc_flex,
p_notes => p_notes,
p_contacts => p_contacts,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
x_workflow_process_id => x_sr_update_out_rec.workflow_process_id,
x_interaction_id => x_sr_update_out_rec.interaction_id);
cs_servicerequest_iuhk.Update_ServiceRequest_Pre(x_return_status=>l_return_status);
Update_SR_Validation
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
--p_service_request_rec => p_service_request_rec,
p_service_request_rec => l_sr_rec_temp,
p_contacts => p_contacts,
p_notes => p_notes,
p_audit_comments => p_audit_comments,
p_invocation_mode => p_invocation_mode,
p_resp_id => p_resp_id,
p_resp_appl_id => p_resp_appl_id,
p_request_id => p_request_id,
p_validation_level => p_validation_level,
p_commit => p_commit,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_object_version_number => p_object_version_number,
x_return_status => l_return_status,
x_contra_id => l_contra_id,
x_contract_number => l_contract_number,
x_owner_assigned_flag => l_owner_assigned_flag,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_audit_vals_rec => l_audit_vals_rec,
x_service_request_rec => l_service_request_rec,
x_autolaunch_wkf_flag => l_autolaunch_workflow_flag,
x_abort_wkf_close_flag => l_abort_workflow_close_flag,
x_wkf_process_name => l_workflow_process_name,
x_workflow_process_id => l_workflow_process_id,
x_interaction_id => l_interaction_id,
p_update_desc_flex => p_update_desc_flex,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
-- for cmro
p_cmro_flag => l_new_cmro_flag,
p_maintenance_flag => l_new_maintenance_flag,
p_auto_assign => p_auto_assign
);
l_only_status_update_flag := 'Y';
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_ONLY_STATUS_UPDATED');
CS_Servicerequest_PVT.Update_Status
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
p_resp_id => p_resp_id,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id,
p_object_version_number => p_object_version_number,
p_status_id => l_service_request_rec.status_id,
p_closed_date => l_service_request_rec.closed_date,
p_last_updated_by => l_service_request_rec.last_updated_by ,
p_last_update_login => l_service_request_rec.last_update_login ,
p_last_update_date => l_service_request_rec.last_update_date ,
x_interaction_id => l_interaction_id
);
IF (l_only_status_update_flag <> 'Y') THEN
vldt_sr_rec
( p_sr_rec => l_service_request_rec
, p_sr_rec_inp => p_service_request_rec
, p_sr_related_data => l_sr_related_data
, p_mode => 'UPDATE'
, p_validation_level => p_validation_level
, p_request_id => p_request_id
, p_object_version_number => p_object_version_number
, p_last_updated_by => p_last_updated_by
, p_default_contract_sla_ind => p_default_contract_sla_ind
, p_auto_assign => p_auto_assign
, p_old_incident_location_id => l_old_servicerequest_rec.incident_location_id
, p_old_incident_location_type => l_old_servicerequest_rec.incident_location_type
, p_old_incident_country => l_old_servicerequest_rec.incident_country
, p_old_incident_owner_id => l_old_servicerequest_rec.incident_owner_id
, p_old_owner_group_id => l_old_servicerequest_rec.owner_group_id
, p_old_resource_type => l_old_servicerequest_rec.resource_type
, p_old_site_id => l_old_servicerequest_rec.site_id
, p_old_obligation_date => l_old_servicerequest_rec.obligation_date
, p_old_expected_resolution_date => l_old_servicerequest_rec.expected_resolution_date
, p_old_contract_id => l_old_servicerequest_rec.contract_id
, p_old_contract_service_id => l_old_servicerequest_rec.contract_service_id
, p_old_install_site_id => l_old_servicerequest_rec.install_site_id
, p_old_system_id => l_old_servicerequest_rec.system_id
, p_old_account_id => l_old_servicerequest_rec.account_id
, p_old_inventory_item_id => l_old_servicerequest_rec.inventory_item_id
, p_old_customer_product_id => l_old_servicerequest_rec.customer_product_id
, p_old_incident_type_id => l_old_servicerequest_rec.incident_type_id
, p_old_time_zone_id => l_old_servicerequest_rec.time_zone_id
, p_old_incident_severity_id => l_old_servicerequest_rec.incident_severity_id
, x_contract_number => l_contract_number
, x_return_status => l_return_status
);
IF l_sr_related_data.old_disallow_request_update <> 'Y'
THEN
CS_SRCONTACT_PKG.process
( p_mode => 'UPDATE'
, p_incident_id => p_request_id
, p_caller_type => l_service_request_rec.caller_type
, p_customer_id => l_service_request_rec.customer_id
, p_validation_mode => p_validation_level
, p_contact_tbl => p_contacts
, x_contact_tbl => l_processed_contacts
, x_old_contact_tbl => l_old_contacts
, x_primary_party_id => l_sr_related_data.primary_party_id
, x_primary_contact_point_id => l_sr_related_data.primary_contact_point_id
, x_return_status => l_return_status
);
END IF; -- request update is allowed
IF (l_service_request_rec.last_update_channel in ('PHONE', 'AGENT') AND
(l_imp_level_old <> 1 OR l_imp_level_old = 1) AND
l_imp_level=1 AND
l_service_request_rec.owner_id IS NOT NULL AND
l_service_request_rec.owner_id <> l_old_owner_id AND
l_service_request_rec.owner_id <> FND_API.G_MISS_NUM) THEN
l_uwq_body2 := Get_Message(p_uwq_msg_notification);
ELSIF (l_service_request_rec.last_update_channel in ('PHONE', 'AGENT') AND
l_imp_level_old <>1 AND
l_imp_level=1 AND
l_service_request_rec.owner_id IS NOT NULL AND
l_service_request_rec.owner_id = l_old_owner_id AND
l_service_request_rec.owner_id <> FND_API.G_MISS_NUM)
OR (l_service_request_rec.last_update_channel = 'WEB' AND
(l_imp_level =1 OR l_imp_level =2) AND
l_service_request_rec.owner_id IS NOT NULL AND
l_service_request_rec.owner_id <> FND_API.G_MISS_NUM)
THEN
l_uwq_body2 := Get_Message(p_uwq_upd_notification);
an update on the SR's workflow_process_id and not call the SR Update API.
IF (FND_API.To_Boolean(p_called_by_workflow) = TRUE) THEN
SELECT workflow INTO l_workflow_process_name
FROM cs_incident_types_b
WHERE incident_type_id = l_service_request_rec.type_id
AND incident_subtype = G_SR_SUBTYPE
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE));
UPDATE cs_incidents_all_b
SET workflow_process_id = p_workflow_process_id
WHERE ROWID = l_old_ServiceRequest_rec.ROW_ID ;
an update on the SR's workflow_process_id and not call the SR Update API.
***********************************************/
-- Code Changes for 11.5.10 Auto Close SR project
-- For bug 3332985
IF l_sr_related_data.old_close_flag = 'N' AND
l_sr_related_data.close_flag = 'Y'
THEN
-- it means an open service request is being close, so check if SR can be closed
-- and then close all SR child entities
--
-- This validation happens irrespective of ERES flag
IF ( p_validate_sr_closure = 'Y' OR p_validate_sr_closure = 'y') THEN
CS_SR_STATUS_PROPAGATION_PKG.VALIDATE_SR_CLOSURE(
p_api_version => p_api_version,
p_init_msg_list => fnd_api.g_false ,
p_commit => FND_API.G_FALSE,
p_service_request_id => p_request_id,
p_user_id => l_service_request_rec.last_updated_by,
p_resp_appl_id => p_resp_appl_id,
p_login_id => l_service_request_rec.last_update_login,
x_return_status => l_return_status,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data);
, p_user_id => l_service_request_rec.last_updated_by
, p_resp_appl_id => p_resp_appl_id
, p_login_id => l_service_request_rec.last_update_login
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
x_sr_update_out_rec.individual_owner := l_service_request_rec.owner_id;
x_sr_update_out_rec.group_owner := l_service_request_rec.owner_group_id;
x_sr_update_out_rec.individual_type := l_service_request_rec.resource_type;
x_sr_update_out_rec.individual_owner := l_service_request_rec.owner_id;
x_sr_update_out_rec.group_owner := l_service_request_rec.owner_group_id;
x_sr_update_out_rec.individual_type := l_service_request_rec.resource_type;
UPDATE cs_incidents_all_b
SET incident_status_id = l_service_request_rec.status_id,
incident_type_id = l_service_request_rec.type_id,
incident_urgency_id = l_service_request_rec.urgency_id,
incident_severity_id = l_service_request_rec.severity_id,
incident_owner_id = l_service_request_rec.owner_id,
resource_type = l_service_request_rec.resource_type,
-- resource_subtype_id = l_service_request_rec.resource_subtype_id, For BUG 2748584
inventory_item_id = l_service_request_rec.inventory_item_id,
-- removed decode for 11.5.6 enhancement
customer_id = l_service_request_rec.customer_id,
account_id = l_service_request_rec.account_id,
current_serial_number = l_service_request_rec.current_serial_number,
expected_resolution_date = l_service_request_rec.exp_resolution_date,
actual_resolution_date = l_service_request_rec.act_resolution_date,
customer_product_id = l_service_request_rec.customer_product_id,
bill_to_site_use_id = l_service_request_rec.bill_to_site_use_id,
bill_to_contact_id = l_service_request_rec.bill_to_contact_id,
ship_to_site_use_id = l_service_request_rec.ship_to_site_use_id,
ship_to_contact_id = l_service_request_rec.ship_to_contact_id,
install_site_use_id = l_service_request_rec.install_site_use_id,
incident_attribute_1 = l_service_request_rec.request_attribute_1,
incident_attribute_2 = l_service_request_rec.request_attribute_2,
incident_attribute_3 = l_service_request_rec.request_attribute_3,
incident_attribute_4 = l_service_request_rec.request_attribute_4,
incident_attribute_5 = l_service_request_rec.request_attribute_5,
incident_attribute_6 = l_service_request_rec.request_attribute_6,
incident_attribute_7 = l_service_request_rec.request_attribute_7,
incident_attribute_8 = l_service_request_rec.request_attribute_8,
incident_attribute_9 = l_service_request_rec.request_attribute_9,
incident_attribute_10 = l_service_request_rec.request_attribute_10,
incident_attribute_11 = l_service_request_rec.request_attribute_11,
incident_attribute_12 = l_service_request_rec.request_attribute_12,
incident_attribute_13 = l_service_request_rec.request_attribute_13,
incident_attribute_14 = l_service_request_rec.request_attribute_14,
incident_attribute_15 = l_service_request_rec.request_attribute_15,
incident_context = l_service_request_rec.request_context,
external_attribute_1 = l_service_request_rec.external_attribute_1,
external_attribute_2 = l_service_request_rec.external_attribute_2,
external_attribute_3 = l_service_request_rec.external_attribute_3,
external_attribute_4 = l_service_request_rec.external_attribute_4,
external_attribute_5 = l_service_request_rec.external_attribute_5,
external_attribute_6 = l_service_request_rec.external_attribute_6,
external_attribute_7 = l_service_request_rec.external_attribute_7,
external_attribute_8 = l_service_request_rec.external_attribute_8,
external_attribute_9 = l_service_request_rec.external_attribute_9,
external_attribute_10 = l_service_request_rec.external_attribute_10,
external_attribute_11 = l_service_request_rec.external_attribute_11,
external_attribute_12 = l_service_request_rec.external_attribute_12,
external_attribute_13 = l_service_request_rec.external_attribute_13,
external_attribute_14 = l_service_request_rec.external_attribute_14,
external_attribute_15 = l_service_request_rec.external_attribute_15,
external_context = l_service_request_rec.external_context,
resolution_code = l_service_request_rec.resolution_code,
problem_code = l_service_request_rec.problem_code,
original_order_number = l_service_request_rec.original_order_number,
purchase_order_num = l_service_request_rec.purchase_order_num,
close_date = l_service_request_rec.closed_date,
publish_flag = l_service_request_rec.publish_flag,
obligation_date = l_service_request_rec.obligation_date,
qa_collection_id = l_service_request_rec.qa_collection_plan_id,
contract_service_id = l_service_request_rec.contract_service_id,
contract_id = l_contra_id,
contract_number = l_contract_number,
project_number = l_service_request_rec.project_number,
customer_po_number = l_service_request_rec.cust_po_number,
customer_ticket_number = l_service_request_rec.cust_ticket_number,
time_zone_id = l_service_request_rec.time_zone_id,
time_difference = l_service_request_rec.time_difference,
platform_id = l_service_request_rec.platform_id ,
platform_version = l_service_request_rec.platform_version,
platform_version_id = l_service_request_rec.platform_version_id,
db_version = l_service_request_rec.db_version,
cp_component_id = l_service_request_rec.cp_component_id,
cp_component_version_id = l_service_request_rec.cp_component_version_id,
cp_subcomponent_id = l_service_request_rec.cp_subcomponent_id,
cp_subcomponent_version_id = l_service_request_rec.cp_subcomponent_version_id ,
cp_revision_id = l_service_request_rec.cp_revision_id ,
inv_item_revision = l_service_request_rec.inv_item_revision ,
inv_component_id = l_service_request_rec.inv_component_id ,
inv_component_version = l_service_request_rec.inv_component_version,
inv_subcomponent_id = l_service_request_rec.inv_subcomponent_id ,
inv_subcomponent_version = l_service_request_rec.inv_subcomponent_version,
site_id = l_service_request_rec.site_id,
customer_site_id = l_service_request_rec.customer_site_id,
territory_id = l_service_request_rec.territory_id,
-- Added for enhancements---11.5.6------jngeorge-----
cust_pref_lang_id = l_service_request_rec.cust_pref_lang_id,
comm_pref_code = l_service_request_rec.comm_pref_code,
cust_pref_lang_code = l_service_request_rec.cust_pref_lang_code,
last_update_channel = l_service_request_rec.last_update_channel,
tier = l_service_request_rec.tier,
tier_version = l_service_request_rec.tier_version,
operating_system = l_service_request_rec.operating_system,
operating_system_version = l_service_request_rec.operating_system_version,
DATABASE = l_service_request_rec.DATABASE,
category_id = l_service_request_rec.category_id,
group_type = l_service_request_rec.group_type,
owner_group_id = l_service_request_rec.owner_group_id,
group_territory_id = l_service_request_rec.group_territory_id,
owner_assigned_time = l_service_request_rec.owner_assigned_time,
owner_assigned_flag = l_service_request_rec.owner_assigned_flag,
unassigned_indicator = l_unassigned_indicator,
inv_platform_org_id = l_service_request_rec.inv_platform_org_id,
product_revision = l_service_request_rec.product_revision,
component_version = l_service_request_rec.component_version,
subcomponent_version = l_service_request_rec.subcomponent_version,
category_set_id = l_service_request_rec.category_set_id,
external_reference = l_service_request_rec.external_reference,
system_id = l_service_request_rec.system_id,
error_code = l_service_request_rec.error_code,
incident_occurred_date = l_service_request_rec.incident_occurred_date,
incident_resolved_date = l_service_request_rec.incident_resolved_date,
inc_responded_by_date = l_service_request_rec.inc_responded_by_date,
incident_location_id = l_service_request_rec.incident_location_id ,
incident_address = l_service_request_rec.incident_address ,
incident_city = l_service_request_rec.incident_city,
incident_state = l_service_request_rec.incident_state,
incident_country = l_service_request_rec.incident_country,
incident_province = l_service_request_rec.incident_province ,
incident_postal_code = l_service_request_rec.incident_postal_code ,
incident_county = l_service_request_rec.incident_county,
sr_creation_channel = l_service_request_rec.sr_creation_channel,
-- Added for ER# 2320056
coverage_type = l_service_request_rec.coverage_type,
-- Added for ER#2433831
bill_to_account_id = l_service_request_rec.bill_to_account_id,
ship_to_account_id = l_service_request_rec.ship_to_account_id,
-- Added for ER#2463321
customer_phone_id = l_service_request_rec.customer_phone_id,
customer_email_id = l_service_request_rec.customer_email_id,
-- Added for source cahnges for 1159 shijain oct 11 2002
last_update_program_code = l_service_request_rec.last_update_program_code,
last_updated_by = l_service_request_rec.last_updated_by,
last_update_login = l_service_request_rec.last_update_login,
last_update_date = l_service_request_rec.last_update_date,
bill_to_party_id = l_service_request_rec.bill_to_party_id,
ship_to_party_id = l_service_request_rec.ship_to_party_id,
-- Conc request related fields
program_id = l_service_request_rec.program_id,
program_application_id = l_service_request_rec.program_application_id,
request_id = l_service_request_rec.conc_request_id,
program_login_id = l_service_request_rec.program_login_id,
-- Bill_to_site, ship_to_site
bill_to_site_id = l_service_request_rec.bill_to_site_id,
ship_to_site_id = l_service_request_rec.ship_to_site_id,
-- Added for enh. 2655115
-- for bug 3050727
status_flag = l_service_request_rec.status_flag,
object_version_number = p_object_version_number+1,
-- Added these address columns by shijain 2002 5th dec
incident_point_of_interest=l_service_request_rec.incident_point_of_interest,
incident_cross_street=l_service_request_rec.incident_cross_street,
incident_direction_qualifier=l_service_request_rec.incident_direction_qualifier,
incident_distance_qualifier=l_service_request_rec.incident_distance_qualifier,
incident_distance_qual_uom =l_service_request_rec.incident_distance_qual_uom,
incident_address2 =l_service_request_rec.incident_address2 ,
incident_address3=l_service_request_rec.incident_address3 ,
incident_address4=l_service_request_rec.incident_address4 ,
incident_address_style=l_service_request_rec.incident_address_style ,
incident_addr_lines_phonetic =l_service_request_rec.incident_addr_lines_phonetic ,
incident_po_box_number =l_service_request_rec.incident_po_box_number ,
incident_house_number =l_service_request_rec.incident_house_number,
incident_street_suffix =l_service_request_rec.incident_street_suffix,
incident_street =l_service_request_rec.incident_street,
incident_street_number =l_service_request_rec.incident_street_number,
incident_floor=l_service_request_rec.incident_floor,
incident_suite =l_service_request_rec.incident_suite ,
incident_postal_plus4_code =l_service_request_rec.incident_postal_plus4_code,
incident_position =l_service_request_rec.incident_position ,
incident_location_directions=l_service_request_rec.incident_location_directions,
incident_location_description =l_service_request_rec.incident_location_description ,
install_site_id =l_service_request_rec.install_site_id ,
inv_organization_id= l_service_request_rec.inventory_org_id,
-- for cmro_eam
owning_department_id = l_service_request_rec.owning_dept_id,
--end for cmro_eam
-- Added for Misc ERs project of 11.5.10 --anmukher --08/26/03
incident_location_type = l_service_request_rec.incident_location_type,
--Added for Auditing project of 11.5.10 --anmukher --09/05/03
incident_last_modified_date = sysdate,
maint_organization_id = l_service_request_rec.maint_organization_id
WHERE ROWID = l_old_ServiceRequest_rec.ROW_ID ;
UPDATE cs_incidents_all_tl
SET summary = l_service_request_rec.summary,
resolution_summary = l_service_request_rec.resolution_summary,
last_update_date = l_service_request_rec.last_update_date,
last_updated_by = l_service_request_rec.last_updated_by,
last_update_login = l_service_request_rec.last_update_login,
source_lang = userenv('LANG'), --l_service_request_rec.LANGUAGE,
text_index = 'A'
WHERE incident_id = p_request_id
AND userenv('LANG') IN (LANGUAGE, source_lang);
UPDATE cs_incidents_all_tl
SET summary = l_service_request_rec.summary,
resolution_summary = l_service_request_rec.resolution_summary,
last_update_date = l_service_request_rec.last_update_date,
last_updated_by = l_service_request_rec.last_updated_by,
last_update_login = l_service_request_rec.last_update_login,
source_lang = userenv('LANG') --l_service_request_rec.LANGUAGE
WHERE incident_id = p_request_id
AND userenv('LANG') IN (LANGUAGE, source_lang);
These are already set before update to cs_incidents_all_b table
smisra: 29th nov 2004
l_audit_vals_rec.OLD_CONTRACT_ID := l_old_ServiceRequest_rec.CONTRACT_ID;
END IF; /* only_status_update_flag check */
p_update_desc_flex => p_update_desc_flex,
p_object_version_number => p_object_version_number,
p_transaction_type => l_transaction_type,
p_message_rev => l_message_revision,
p_servicerequest => l_service_request_rec,
p_contacts => l_contacts
);
IF (l_only_status_update_flag <> 'Y') THEN
--
-- Create entries in JTF_NOTES from p_notes
--
l_note_index := p_notes.FIRST;
p_entered_by => p_last_updated_by,
p_entered_date => p_last_update_date,
p_created_by => p_last_updated_by,
p_creation_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_date => p_last_update_date,
x_jtf_note_id => l_note_id
);
p_creation_date => p_last_update_date, ----SYSDATE,
p_last_updated_by => p_last_updated_by,
p_last_update_date => p_last_update_date, ------SYSDATE,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_01,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_01,
x_note_context_id => l_note_context_id
);
p_creation_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_date => p_last_update_date,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_02,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_02,
x_note_context_id => l_note_context_id
);
p_creation_date => p_last_update_date,
p_last_updated_by => p_last_updated_by,
p_last_update_date => p_last_update_date,
p_jtf_note_id => l_note_id,
p_note_context_type => p_notes(l_note_index).note_context_type_03,
p_note_context_type_id => p_notes(l_note_index).note_context_type_id_03,
x_note_context_id => l_note_context_id
);
IF (JTF_USR_HKS.Ok_To_Execute('CS_ServiceRequest_PVT', 'Update_ServiceRequest',
'W', 'W')) THEN
IF (cs_servicerequest_cuhk.Ok_To_Launch_Workflow
(p_request_id => p_request_id,
p_service_request_rec=>l_service_request_rec)) THEN
l_bind_data_id := JTF_USR_HKS.Get_bind_data_id ;
JTF_USR_HKS.Load_bind_data(l_bind_data_id, 'USER_ID', p_last_updated_by, 'W', 'T');
SELECT cs_wf_process_id_s.NEXTVAL INTO l_workflow_item_key FROM dual;
END IF; /* only status update flag check */
'Update_ServiceRequest',
'A', 'C') THEN
-- hardcoded the version 2.0 shijain nov 27 2002
cs_servicerequest_cuhk.Update_ServiceRequest_Post
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id ,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_invocation_mode => p_invocation_mode,
p_service_request_rec => l_service_request_rec,
p_update_desc_flex => p_update_desc_flex,
p_notes => p_notes,
p_contacts => p_contacts,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
x_workflow_process_id => x_sr_update_out_rec.workflow_process_id,
x_interaction_id => x_sr_update_out_rec.interaction_id);
'Update_ServiceRequest',
'A', 'V') THEN
-- hardcoded the version 2.0 shijain nov 27 2002
cs_servicerequest_vuhk.Update_ServiceRequest_Post
( p_api_version => 2.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => p_request_id ,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_last_updated_by => p_last_updated_by,
p_last_update_login => p_last_update_login,
p_last_update_date => p_last_update_date,
p_service_request_rec => l_service_request_rec,
p_update_desc_flex => p_update_desc_flex,
p_notes => p_notes,
p_contacts => p_contacts,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_workflow_process_id => p_workflow_process_id,
x_workflow_process_id => x_sr_update_out_rec.workflow_process_id,
x_interaction_id => x_sr_update_out_rec.interaction_id);
cs_servicerequest_iuhk.Update_ServiceRequest_Post( x_return_status=>l_return_status);
IF (l_only_status_update_flag <> 'Y') THEN
-- ------------------------------------------------------
-- Insert a record into the calls table if the caller is
-- not a workflow process
-- ------------------------------------------------------
IF NOT FND_API.To_Boolean(p_called_by_workflow) THEN
IF (l_service_request_rec.parent_interaction_id IS NULL) THEN
/* CREATE INTERACTION */ /* l_interaction_id := */
NULL;
x_sr_update_out_rec.interaction_id := l_interaction_id;
END IF; /* only status update flag check */
IF (l_only_status_update_flag <> 'Y')
THEN
IF l_start_eres_flag = 'Y'
THEN
CS_ERES_INT_PKG.start_approval_process
( p_incident_id => p_request_id
, p_incident_type_id => l_service_request_rec.type_id
, p_incident_status_id => l_sr_related_data.intermediate_status_id
, p_qa_collection_id => l_service_request_rec.qa_collection_plan_id
, x_approval_status => l_approval_status
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
CS_ServiceRequest_UTIL.Validate_Updated_Status
( p_api_name => 'CS_SERVICEREQUEST_PVT.update_servicerequest'
, p_parameter_name => 'p_status_id'
, p_resp_id => p_resp_id
, p_new_status_id => l_sr_related_data.target_status_id
, p_old_status_id => l_old_servicerequest_rec.incident_status_id
, p_subtype => G_SR_SUBTYPE
, p_type_id => l_service_request_rec.type_id
, p_old_type_id => l_old_servicerequest_rec.incident_type_id
, p_close_flag => l_sr_related_data.close_flag
, p_disallow_request_update => l_sr_related_data.disallow_request_update
, p_disallow_owner_update => l_sr_related_data.disallow_owner_update
, p_disallow_product_update => l_sr_related_data.disallow_product_update
, x_return_status => l_return_status
);
UPDATE cs_incidents_all_b
SET incident_status_id = l_service_request_rec.status_id
, inc_responded_by_date = l_service_request_rec.inc_responded_by_date
, incident_resolved_date = l_service_request_rec.incident_resolved_date
, status_flag = l_service_request_rec.status_flag
, close_date = l_service_request_rec.closed_date
WHERE incident_id = p_request_id;
x_sr_update_out_rec.status_id := l_service_request_rec.status_id;
x_sr_update_out_rec.close_date := l_service_request_rec.closed_date;
x_sr_update_out_rec.resolved_on_date := l_service_request_rec.incident_resolved_date;
x_sr_update_out_rec.responded_on_date := l_service_request_rec.inc_responded_by_date;
l_audit_vals_rec.updated_entity_code := 'SR_HEADER';
l_audit_vals_rec.updated_entity_id := p_request_id;
l_audit_vals_rec.incident_last_modified_date := l_service_request_rec.last_update_date ;
, p_user_id => l_service_request_rec.last_updated_by
, p_wf_process_name => l_workflow_process_name
, p_wf_process_itemkey => l_wf_process_itemkey
, p_login_id => l_service_request_rec.last_update_login
, p_last_update_date => l_service_request_rec.last_update_date
, p_creation_date => l_service_request_rec.last_update_date
, p_comments => p_audit_comments
, x_audit_id => l_audit_id
);
CS_SRCONTACT_PKG.create_update
( p_incident_id => p_request_id
, p_invocation_mode => p_invocation_mode
, p_sr_update_date => l_service_request_rec.last_update_date
, p_sr_updated_by => l_service_request_rec.last_updated_by
, p_sr_update_login => l_service_request_rec.last_update_login
, p_contact_tbl => l_processed_contacts
, p_old_contact_tbl => l_old_contacts
, x_return_status => l_return_status
);
, x_disallow_request_update => l_sr_related_data.old_disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.old_disallow_owner_update
, x_disallow_product_update => l_sr_related_data.old_disallow_product_update
, x_pending_approval_flag => l_sr_related_data.old_pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.old_intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.old_approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.old_rejection_action_status_id
, x_return_status => l_return_status
);
p_user_id => p_last_updated_by);
, p_user_id => l_service_request_rec.last_updated_by
, p_resp_appl_id => p_resp_appl_id
, p_login_id => l_service_request_rec.last_update_login
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
, p_user_id => l_service_request_rec.last_updated_by
, p_resp_appl_id => p_resp_appl_id
, p_login_id => l_service_request_rec.last_update_login
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
CS_ServiceRequest_UTIL.Verify_LocUpdate_For_FSTasks
(p_incident_id => p_request_id,
x_return_status => x_return_status );
CS_servicerequest_util.update_task_address
( p_incident_id => p_request_id
, p_location_type => l_service_request_rec.incident_location_type
, p_location_id => l_service_request_rec.incident_location_id
, x_return_status => x_return_status
);
END IF; -- l_only_status_update_flag <> 'Y'
l_old_sr_rec.last_update_channel := l_old_servicerequest_rec.last_update_channel;
l_old_sr_rec.last_update_program_code := l_old_servicerequest_rec.last_update_program_code;
p_event_code => 'UPDATE_SERVICE_REQUEST',
p_incident_number => l_old_ServiceRequest_rec.incident_number,
p_user_id => l_service_request_rec.last_updated_by,
p_resp_id => p_resp_id,
p_resp_appl_id => p_resp_appl_id,
p_old_sr_rec => l_old_sr_rec,
p_new_sr_rec => l_service_request_rec, -- using l_ser...coz this is the
-- rec. type used in the insert.
p_contacts_table => p_contacts,
p_link_rec => NULL, -- using default value
p_wf_process_id => p_workflow_process_id, -- value of the WF
-- process id if the update API is invoked from a WF.
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 => lx_return_status,
x_msg_count => lx_msg_count,
x_msg_data => lx_msg_data );
x_sr_update_out_rec.workflow_process_id := l_workflow_process_id ;
CS_SR_WORKITEM_PVT.Update_Workitem(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit ,
p_incident_id => p_request_id,
p_old_sr_rec => l_old_ServiceRequest_rec,
p_new_sr_rec => l_service_request_rec,
p_user_id => l_service_request_rec.last_updated_by,
p_resp_appl_id => p_resp_appl_id ,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
WHEN DestUpdated THEN
IF (l_ServiceRequest_csr%ISOPEN) THEN
CLOSE l_ServiceRequest_csr;
FND_MESSAGE.SET_NAME('CS','CS_SR_DESTINATION_UPDATED');
WHEN NoUpdate THEN
IF (l_ServiceRequest_csr%ISOPEN) THEN
CLOSE l_ServiceRequest_csr;
WHEN TargUpdated THEN
IF (l_ServiceRequest_csr%ISOPEN) THEN
CLOSE l_ServiceRequest_csr;
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
END Update_ServiceRequest;
PROCEDURE Update_Status
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_resp_id IN NUMBER,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_object_version_number IN NUMBER,
p_status_id IN NUMBER,
p_closed_date IN DATE DEFAULT fnd_api.g_miss_date,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
p_comments IN VARCHAR2 DEFAULT NULL,
p_public_comment_flag IN VARCHAR2 DEFAULT fnd_api.g_false,
p_parent_interaction_id IN NUMBER DEFAULT NULL,
p_validate_sr_closure IN VARCHAR2 Default 'N',
p_auto_close_child_entities IN VARCHAR2 Default 'N',
x_interaction_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Status';
l_disallow_request_update VARCHAR2(1);
l_disallow_owner_update VARCHAR2(1);
l_disallow_product_update VARCHAR2(1);
Replacing the select list of columns with a select * so that the
subtype defined in the spec can be used to pass the old SR values as
a parameter to other procedures
* *****************/
CURSOR L_SERVICEREQUEST_CSR IS
SELECT *
from cs_incidents_all_vl
where incident_id = p_request_id
and object_version_number = p_object_version_number
for update nowait;
l_sr_update_out_rec sr_update_out_rec_type;
SAVEPOINT Update_Status_PVT;
, 'p_last_updated_by:' || p_last_updated_by
);
, 'p_last_update_login:' || p_last_update_login
);
, 'p_last_update_date:' || p_last_update_date
);
IF (l_sr_related_data.disallow_request_update <> 'Y') THEN
CS_ServiceRequest_UTIL.Add_Same_Val_Update_Msg
( p_token_an => l_api_name_full,
p_token_p => 'p_status_id' ,
p_table_name => G_TABLE_NAME,
p_column_name => 'INCIDENT_STATUS_ID');
p_parameter_name_usr => 'p_last_updated_by',
p_parameter_name_login => 'p_last_update_login',
p_user_id => p_last_updated_by,
p_login_id => p_last_update_login,
x_return_status => l_return_status);
p_operation => 'UPDATE_OLD',
x_return_status => lx_return_status,
x_cmro_flag => l_old_cmro_flag,
x_maintenance_flag => l_old_maintenance_flag );
, x_disallow_request_update => l_sr_related_data.old_disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.old_disallow_owner_update
, x_disallow_product_update => l_sr_related_data.old_disallow_product_update
, x_pending_approval_flag => l_sr_related_data.old_pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.old_intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.old_approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.old_rejection_action_status_id
, x_return_status => l_return_status);
(l_sr_related_data.old_disallow_request_update = 'Y') THEN
FND_MESSAGE.set_name('CS','CS_ERES_SR_FROZEN_STATUS');
, x_disallow_request_update => l_sr_related_data.disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.disallow_owner_update
, x_disallow_product_update => l_sr_related_data.disallow_product_update
, x_pending_approval_flag => l_sr_related_data.pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.rejection_action_status_id
, x_return_status => l_return_status
);
, 'CS_SERVICEREQUEST_PVT.update_status'
);
CS_ServiceRequest_PVT.Update_ServiceRequest
( p_api_version => 4.0
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_request_id => p_request_id
, p_audit_id => NULL
, p_object_version_number => p_object_version_number
, p_resp_id => p_resp_id
, p_last_updated_by => p_last_updated_by
, p_last_update_login => p_last_update_login
, p_last_update_date => p_last_update_date
, p_service_request_rec => l_new_sr_rec
, p_notes => l_notes
, p_contacts => l_contacts
, p_called_by_workflow => p_called_by_workflow
, p_workflow_process_id => p_workflow_process_id
, p_validate_sr_closure => p_validate_sr_closure
, p_auto_close_child_entities => p_auto_close_child_entities
, x_sr_update_out_rec => l_sr_update_out_rec
);
CS_ServiceRequest_UTIL.Validate_Updated_Status(
p_api_name => l_api_name_full,
p_parameter_name => 'p_status_id',
p_resp_id => p_resp_id ,
p_new_status_id => p_status_id,
p_old_status_id => l_ServiceRequest_rec.incident_status_id,
p_subtype => G_SR_SUBTYPE,
p_type_id => l_ServiceRequest_rec.incident_type_id,
p_old_type_id => l_ServiceRequest_rec.incident_type_id,
p_close_flag => l_closed_flag,
p_disallow_request_update => l_disallow_request_update,
p_disallow_owner_update => l_disallow_owner_update,
p_disallow_product_update => l_disallow_product_update,
x_return_status => l_return_status );
SELECT autolaunch_workflow_flag,
abort_workflow_close_flag,
workflow
INTO l_autolaunch_workflow_flag,
l_abort_workflow_close_flag,
l_workflow_process_name
FROM cs_incident_types_b
WHERE incident_type_id = l_ServiceRequest_rec.incident_type_id
AND incident_subtype = G_SR_SUBTYPE
;
p_user_id => p_last_updated_by
);
p_user_id => p_last_updated_by,
p_resp_appl_id => p_resp_id,
p_login_id => p_last_update_login,
x_return_status => l_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
p_user_id => p_last_updated_by,
p_resp_appl_id => p_resp_id,
p_login_id => p_last_update_login,
x_return_status => l_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data);
UPDATE cs_incidents_all_b
SET incident_status_id = p_status_id,
close_date = l_closed_date,
inc_responded_by_date = l_inc_responded_by_date,
incident_resolved_date= l_incident_resolved_date,
last_updated_by = p_last_updated_by,
last_update_date = SYSDATE,
last_update_login = p_last_update_login,
-- Added for enh. 2655115
status_flag = l_status_flag,
object_version_number = p_object_version_number+1
--Fixed bug#2775580,changed the where clause from current of to
--checking the incident_id
WHERE incident_id = p_request_id;
P_updated_entity_code => 'SR_HEADER',
p_updated_entity_id => p_request_id,
p_entity_update_date => SYSDATE,
p_entity_activity_code => 'U' ,
p_status_id => p_status_id,
p_old_status_id => l_orig_status_id,
p_closed_date => l_closed_date,
p_old_closed_date => l_orig_closed_date,
p_owner_status_upd_flag => 'STATUS',
p_user_id => p_last_updated_by,
p_old_inc_responded_by_date => l_servicerequest_rec.inc_responded_by_date,
p_old_incident_resolved_date=> l_servicerequest_rec.incident_resolved_date,
x_audit_id => lx_audit_id,
x_return_status => lx_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
l_old_sr_rec.last_update_channel := l_servicerequest_rec.last_update_channel;
l_old_sr_rec.last_update_program_code := l_servicerequest_rec.last_update_program_code;
p_event_code => 'UPDATE_SERVICE_REQUEST',
p_incident_number => l_ServiceRequest_rec.incident_number,
p_user_id => p_last_updated_by,
p_resp_id => p_resp_id,
p_resp_appl_id => NULL,
p_old_sr_rec => l_old_sr_rec,
p_new_sr_rec => l_new_sr_rec,
p_contacts_table => l_contacts,
p_link_rec => NULL, -- using default value
p_wf_process_id => p_workflow_process_id,
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 => lx_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
CS_SR_WORKITEM_PVT.Update_Workitem(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit ,
p_incident_id => p_request_id,
p_old_sr_rec => l_ServiceRequest_rec,
p_new_sr_rec => l_new_sr_rec,
p_user_id => p_last_updated_by,
p_resp_appl_id => p_resp_id ,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Status_PVT;
ROLLBACK TO Update_Status_PVT;
ROLLBACK TO Update_Status_PVT;
END Update_Status;
PROCEDURE Update_Owner
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER,
p_object_version_number IN NUMBER,
p_resp_id IN NUMBER DEFAULT NULL,
p_resp_appl_id IN NUMBER DEFAULT NULL,
p_owner_id IN NUMBER,
p_owner_group_id IN NUMBER,
p_resource_type IN VARCHAR2,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
p_comments IN VARCHAR2 DEFAULT NULL,
p_public_comment_flag IN VARCHAR2 DEFAULT fnd_api.g_false,
p_parent_interaction_id IN NUMBER DEFAULT NULL,
x_interaction_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Owner';
Replacing the select list of columns with a select * so that the
subtype defined in the spec can be used to pass the old SR values as
a parameter to other procedures
* *****************/
CURSOR L_SERVICEREQUEST_CSR IS
SELECT *
from cs_incidents_all_vl
where incident_id = p_request_id
and object_version_number = p_object_version_number
for update of incident_owner_id nowait;
SAVEPOINT Update_Owner_PVT;
, 'p_last_updated_by:' || p_last_updated_by
);
, 'p_last_update_login:' || p_last_update_login
);
, 'p_last_update_date:' || p_last_update_date
);
CS_ServiceRequest_UTIL.Add_Same_Val_Update_Msg(
p_token_an => l_api_name_full,
p_token_p => 'p_owner_id/p_owner_group_id',
p_table_name => G_TABLE_NAME,
p_column_name => 'OWNER_GROUP_ID');
p_parameter_name_usr => 'p_last_updated_by',
p_parameter_name_login => 'p_last_update_login',
p_user_id => p_last_updated_by,
p_login_id => p_last_update_login,
x_return_status => l_return_status );
p_operation => 'UPDATE_OLD',
x_return_status => lx_return_status,
x_cmro_flag => l_old_cmro_flag,
x_maintenance_flag => l_old_maintenance_flag );
, x_disallow_request_update => l_sr_related_data.disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.disallow_owner_update
, x_disallow_product_update => l_sr_related_data.disallow_product_update
, x_pending_approval_flag => l_sr_related_data.pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.rejection_action_status_id
, x_return_status => l_return_status
);
IF l_sr_related_data.disallow_request_update = 'Y'
THEN
FND_MESSAGE.set_name('CS', 'CS_API_SR_ONLY_STATUS_UPDATED');
UPDATE cs_incidents_all_b
SET incident_owner_id = p_owner_id,
owner_group_id = l_owner_group_id,
resource_type = l_resource_type,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date,
last_update_login = p_last_update_login,
unassigned_indicator = l_unasgn_ind,
object_version_number = p_object_version_number+1
WHERE incident_id = p_request_id ;
P_updated_entity_code => 'SR_HEADER',
p_updated_entity_id => p_request_id,
p_entity_update_date => p_last_update_date, -- sysdate
p_entity_activity_code => 'U' ,
p_owner_id => p_owner_id,
p_old_owner_id => l_orig_owner_id,
p_owner_group_id => l_owner_group_id,
p_old_owner_group_id => l_orig_owner_group_id,
p_resource_type => l_resource_type,
p_old_resource_type => l_orig_resource_type,
p_owner_status_upd_flag => 'OWNER',
p_useR_id => p_last_updated_by,
x_audit_id => lx_audit_id,
x_return_status => lx_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
l_old_sr_rec.last_update_channel := l_servicerequest_rec.last_update_channel;
l_old_sr_rec.last_update_program_code := l_servicerequest_rec.last_update_program_code;
p_event_code => 'UPDATE_SERVICE_REQUEST',
p_incident_number => l_ServiceRequest_rec.incident_number,
p_user_id => p_last_updated_by,
p_resp_id => p_resp_id,
p_resp_appl_id => p_resp_appl_id,
p_old_sr_rec => l_old_sr_rec,
p_new_sr_rec => l_new_sr_rec,
p_contacts_table => l_contacts,
p_link_rec => NULL, -- using default value
p_wf_process_id => p_workflow_process_id,
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 => lx_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
CS_SR_WORKITEM_PVT.Update_Workitem(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false ,
p_commit => p_commit ,
p_incident_id => p_request_id,
p_old_sr_rec => l_ServiceRequest_rec,
p_new_sr_rec => l_new_sr_rec,
p_user_id => p_last_updated_by,
p_resp_appl_id => p_resp_id ,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO Update_Owner_PVT;
ROLLBACK TO Update_Owner_PVT;
ROLLBACK TO Update_Owner_PVT;
END Update_Owner;
p_last_update_date IN DATE,
p_creation_date IN DATE,
p_comments IN VARCHAR2 DEFAULT NULL,
x_audit_id OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Audit_Record';
SELECT cs_incidents_audit_s1.NEXTVAL INTO l_audit_id FROM dual;
INSERT INTO cs_incidents_audit_b (
incident_audit_id ,
incident_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
creation_time ,
incident_status_id ,
old_incident_status_id ,
change_incident_status_flag ,
incident_type_id ,
old_incident_type_id ,
change_incident_type_flag ,
incident_urgency_id ,
old_incident_urgency_id ,
change_incident_urgency_flag ,
incident_severity_id ,
old_incident_severity_id ,
change_incident_severity_flag,
incident_owner_id ,
old_incident_owner_id ,
change_incident_owner_flag ,
create_manual_action ,
action_id ,
expected_resolution_date ,
old_expected_resolution_date ,
change_resolution_flag ,
new_workflow_flag ,
workflow_process_name ,
workflow_process_itemkey ,
group_id,
old_group_id,
change_group_flag,
group_type,
old_group_type,
change_group_type_flag,
inv_platform_org_id,
old_inv_platform_org_id,
change_platform_org_id_flag,
owner_assigned_time,
old_owner_assigned_time,
change_assigned_time_flag,
obligation_date,
old_obligation_date,
change_obligation_flag,
site_id,
old_site_id,
change_site_flag,
bill_to_contact_id,
old_bill_to_contact_id,
change_bill_to_flag,
ship_to_contact_id,
old_ship_to_contact_id,
change_ship_to_flag,
close_date,
old_close_date,
change_close_date_flag,
customer_product_id,
old_customer_product_id,
change_customer_product_flag,
platform_id,
old_platform_id,
change_platform_id_flag,
product_revision,
old_product_revision,
change_product_revision_flag,
component_version,
old_component_version,
change_comp_ver_flag,
subcomponent_version,
old_subcomponent_version,
change_subcomp_ver_flag,
cp_component_id,
old_cp_component_id,
change_cp_component_id_flag,
cp_component_version_id,
old_cp_component_version_id,
change_cp_comp_ver_id_flag,
cp_subcomponent_id,
old_cp_subcomponent_id,
change_cp_subcomponent_id_flag,
cp_subcomponent_version_id,
old_cp_subcomponent_version_id,
change_cp_subcomp_ver_id_flag,
cp_revision_id,
old_cp_revision_id,
change_cp_revision_id_flag,
inv_item_revision,
old_inv_item_revision,
change_inv_item_revision,
inv_component_id,
old_inv_component_id,
change_inv_component_id,
inv_component_version,
old_inv_component_version,
change_inv_component_version,
inv_subcomponent_id,
old_inv_subcomponent_id,
change_inv_subcomponent_id,
inv_subcomponent_version,
old_inv_subcomponent_version,
change_inv_subcomp_version,
territory_id,
old_territory_id,
change_territory_id_flag,
resource_type,
old_resource_type,
change_resource_type_flag,
object_version_number,
audit_field,
inventory_item_id ,
old_inventory_item_id,
change_inventory_item_flag,
inv_organization_id,
old_inv_organization_id,
change_inv_organization_flag,
status_flag ,
old_status_flag ,
change_status_flag ,
incident_date,
old_incident_date,
change_incident_date_flag,
platform_version_id,
old_platform_version_id,
change_plat_ver_id_flag,
language_id,
old_language_id,
change_language_id_flag,
-- primary_contact_id ,
-- change_primary_contact_flag ,
-- old_primary_contact_id,
-- Added new audit columns for 11.5.10 Auditing project --anmukher --09/10/03
UPGRADE_FLAG_FOR_CREATE ,
OLD_INCIDENT_NUMBER ,
INCIDENT_NUMBER ,
OLD_CUSTOMER_ID ,
CUSTOMER_ID ,
OLD_BILL_TO_SITE_USE_ID ,
BILL_TO_SITE_USE_ID ,
OLD_EMPLOYEE_ID ,
EMPLOYEE_ID ,
OLD_SHIP_TO_SITE_USE_ID ,
SHIP_TO_SITE_USE_ID ,
OLD_PROBLEM_CODE ,
PROBLEM_CODE ,
OLD_ACTUAL_RESOLUTION_DATE ,
ACTUAL_RESOLUTION_DATE ,
OLD_INSTALL_SITE_USE_ID ,
INSTALL_SITE_USE_ID ,
OLD_CURRENT_SERIAL_NUMBER ,
CURRENT_SERIAL_NUMBER ,
OLD_SYSTEM_ID ,
SYSTEM_ID ,
OLD_INCIDENT_ATTRIBUTE_1 ,
INCIDENT_ATTRIBUTE_1 ,
OLD_INCIDENT_ATTRIBUTE_2 ,
INCIDENT_ATTRIBUTE_2 ,
OLD_INCIDENT_ATTRIBUTE_3 ,
INCIDENT_ATTRIBUTE_3 ,
OLD_INCIDENT_ATTRIBUTE_4 ,
INCIDENT_ATTRIBUTE_4 ,
OLD_INCIDENT_ATTRIBUTE_5 ,
INCIDENT_ATTRIBUTE_5 ,
OLD_INCIDENT_ATTRIBUTE_6 ,
INCIDENT_ATTRIBUTE_6 ,
OLD_INCIDENT_ATTRIBUTE_7 ,
INCIDENT_ATTRIBUTE_7 ,
OLD_INCIDENT_ATTRIBUTE_8 ,
INCIDENT_ATTRIBUTE_8 ,
OLD_INCIDENT_ATTRIBUTE_9 ,
INCIDENT_ATTRIBUTE_9 ,
OLD_INCIDENT_ATTRIBUTE_10 ,
INCIDENT_ATTRIBUTE_10 ,
OLD_INCIDENT_ATTRIBUTE_11 ,
INCIDENT_ATTRIBUTE_11 ,
OLD_INCIDENT_ATTRIBUTE_12 ,
INCIDENT_ATTRIBUTE_12 ,
OLD_INCIDENT_ATTRIBUTE_13 ,
INCIDENT_ATTRIBUTE_13 ,
OLD_INCIDENT_ATTRIBUTE_14 ,
INCIDENT_ATTRIBUTE_14 ,
OLD_INCIDENT_ATTRIBUTE_15 ,
INCIDENT_ATTRIBUTE_15 ,
OLD_INCIDENT_CONTEXT ,
INCIDENT_CONTEXT ,
OLD_RESOLUTION_CODE ,
RESOLUTION_CODE ,
OLD_ORIGINAL_ORDER_NUMBER ,
ORIGINAL_ORDER_NUMBER ,
OLD_ORG_ID ,
ORG_ID ,
OLD_PURCHASE_ORDER_NUMBER ,
PURCHASE_ORDER_NUMBER ,
OLD_PUBLISH_FLAG ,
PUBLISH_FLAG ,
OLD_QA_COLLECTION_ID ,
QA_COLLECTION_ID ,
OLD_CONTRACT_ID ,
CONTRACT_ID ,
OLD_CONTRACT_NUMBER ,
CONTRACT_NUMBER ,
OLD_CONTRACT_SERVICE_ID ,
CONTRACT_SERVICE_ID ,
OLD_TIME_ZONE_ID ,
TIME_ZONE_ID ,
OLD_ACCOUNT_ID ,
ACCOUNT_ID ,
OLD_TIME_DIFFERENCE ,
TIME_DIFFERENCE ,
OLD_CUSTOMER_PO_NUMBER ,
CUSTOMER_PO_NUMBER ,
OLD_CUSTOMER_TICKET_NUMBER ,
CUSTOMER_TICKET_NUMBER ,
OLD_CUSTOMER_SITE_ID ,
CUSTOMER_SITE_ID ,
OLD_CALLER_TYPE ,
CALLER_TYPE ,
-- OLD_OBJECT_VERSION_NUMBER ,
OLD_SECURITY_GROUP_ID ,
OLD_ORIG_SYSTEM_REFERENCE ,
ORIG_SYSTEM_REFERENCE ,
OLD_ORIG_SYSTEM_REFERENCE_ID ,
ORIG_SYSTEM_REFERENCE_ID ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
OLD_PROJECT_NUMBER ,
PROJECT_NUMBER ,
OLD_PLATFORM_VERSION ,
PLATFORM_VERSION ,
OLD_DB_VERSION ,
DB_VERSION ,
OLD_CUST_PREF_LANG_ID ,
CUST_PREF_LANG_ID ,
OLD_TIER ,
TIER ,
OLD_CATEGORY_ID ,
CATEGORY_ID ,
OLD_OPERATING_SYSTEM ,
OPERATING_SYSTEM ,
OLD_OPERATING_SYSTEM_VERSION ,
OPERATING_SYSTEM_VERSION ,
OLD_DATABASE ,
DATABASE ,
OLD_GROUP_TERRITORY_ID ,
GROUP_TERRITORY_ID ,
OLD_COMM_PREF_CODE ,
COMM_PREF_CODE ,
OLD_LAST_UPDATE_CHANNEL ,
LAST_UPDATE_CHANNEL ,
OLD_CUST_PREF_LANG_CODE ,
CUST_PREF_LANG_CODE ,
OLD_ERROR_CODE ,
ERROR_CODE ,
OLD_CATEGORY_SET_ID ,
CATEGORY_SET_ID ,
OLD_EXTERNAL_REFERENCE ,
EXTERNAL_REFERENCE ,
OLD_INCIDENT_OCCURRED_DATE ,
INCIDENT_OCCURRED_DATE ,
OLD_INCIDENT_RESOLVED_DATE ,
INCIDENT_RESOLVED_DATE ,
OLD_INC_RESPONDED_BY_DATE ,
INC_RESPONDED_BY_DATE ,
OLD_INCIDENT_LOCATION_ID ,
INCIDENT_LOCATION_ID ,
OLD_INCIDENT_ADDRESS ,
INCIDENT_ADDRESS ,
OLD_INCIDENT_CITY ,
INCIDENT_CITY ,
OLD_INCIDENT_STATE ,
INCIDENT_STATE ,
OLD_INCIDENT_COUNTRY ,
INCIDENT_COUNTRY ,
OLD_INCIDENT_PROVINCE ,
INCIDENT_PROVINCE ,
OLD_INCIDENT_POSTAL_CODE ,
INCIDENT_POSTAL_CODE ,
OLD_INCIDENT_COUNTY ,
INCIDENT_COUNTY ,
OLD_SR_CREATION_CHANNEL ,
SR_CREATION_CHANNEL ,
OLD_DEF_DEFECT_ID ,
DEF_DEFECT_ID ,
OLD_DEF_DEFECT_ID2 ,
DEF_DEFECT_ID2 ,
OLD_EXTERNAL_ATTRIBUTE_1 ,
EXTERNAL_ATTRIBUTE_1 ,
OLD_EXTERNAL_ATTRIBUTE_2 ,
EXTERNAL_ATTRIBUTE_2 ,
OLD_EXTERNAL_ATTRIBUTE_3 ,
EXTERNAL_ATTRIBUTE_3 ,
OLD_EXTERNAL_ATTRIBUTE_4 ,
EXTERNAL_ATTRIBUTE_4 ,
OLD_EXTERNAL_ATTRIBUTE_5 ,
EXTERNAL_ATTRIBUTE_5 ,
OLD_EXTERNAL_ATTRIBUTE_6 ,
EXTERNAL_ATTRIBUTE_6 ,
OLD_EXTERNAL_ATTRIBUTE_7 ,
EXTERNAL_ATTRIBUTE_7 ,
OLD_EXTERNAL_ATTRIBUTE_8 ,
EXTERNAL_ATTRIBUTE_8 ,
OLD_EXTERNAL_ATTRIBUTE_9 ,
EXTERNAL_ATTRIBUTE_9 ,
OLD_EXTERNAL_ATTRIBUTE_10 ,
EXTERNAL_ATTRIBUTE_10 ,
OLD_EXTERNAL_ATTRIBUTE_11 ,
EXTERNAL_ATTRIBUTE_11 ,
OLD_EXTERNAL_ATTRIBUTE_12 ,
EXTERNAL_ATTRIBUTE_12 ,
OLD_EXTERNAL_ATTRIBUTE_13 ,
EXTERNAL_ATTRIBUTE_13 ,
OLD_EXTERNAL_ATTRIBUTE_14 ,
EXTERNAL_ATTRIBUTE_14 ,
OLD_EXTERNAL_ATTRIBUTE_15 ,
EXTERNAL_ATTRIBUTE_15 ,
OLD_EXTERNAL_CONTEXT ,
EXTERNAL_CONTEXT ,
OLD_LAST_UPDATE_PROGRAM_CODE ,
LAST_UPDATE_PROGRAM_CODE ,
OLD_CREATION_PROGRAM_CODE ,
CREATION_PROGRAM_CODE ,
OLD_COVERAGE_TYPE ,
COVERAGE_TYPE ,
OLD_BILL_TO_ACCOUNT_ID ,
BILL_TO_ACCOUNT_ID ,
OLD_SHIP_TO_ACCOUNT_ID ,
SHIP_TO_ACCOUNT_ID ,
OLD_CUSTOMER_EMAIL_ID ,
CUSTOMER_EMAIL_ID ,
OLD_CUSTOMER_PHONE_ID ,
CUSTOMER_PHONE_ID ,
OLD_BILL_TO_PARTY_ID ,
BILL_TO_PARTY_ID ,
OLD_SHIP_TO_PARTY_ID ,
SHIP_TO_PARTY_ID ,
OLD_BILL_TO_SITE_ID ,
BILL_TO_SITE_ID ,
OLD_SHIP_TO_SITE_ID ,
SHIP_TO_SITE_ID ,
OLD_PROGRAM_LOGIN_ID ,
PROGRAM_LOGIN_ID ,
OLD_INCIDENT_POINT_OF_INTEREST ,
INCIDENT_POINT_OF_INTEREST ,
OLD_INCIDENT_CROSS_STREET ,
INCIDENT_CROSS_STREET ,
OLD_INCIDENT_DIRECTION_QUALIF ,
INCIDENT_DIRECTION_QUALIF ,
OLD_INCIDENT_DISTANCE_QUALIF ,
INCIDENT_DISTANCE_QUALIF ,
OLD_INCIDENT_DISTANCE_QUAL_UOM ,
INCIDENT_DISTANCE_QUAL_UOM ,
OLD_INCIDENT_ADDRESS2 ,
INCIDENT_ADDRESS2 ,
OLD_INCIDENT_ADDRESS3 ,
INCIDENT_ADDRESS3 ,
OLD_INCIDENT_ADDRESS4 ,
INCIDENT_ADDRESS4 ,
OLD_INCIDENT_ADDRESS_STYLE ,
INCIDENT_ADDRESS_STYLE ,
OLD_INCIDENT_ADDR_LNS_PHONETIC ,
INCIDENT_ADDR_LNS_PHONETIC ,
OLD_INCIDENT_PO_BOX_NUMBER ,
INCIDENT_PO_BOX_NUMBER ,
OLD_INCIDENT_HOUSE_NUMBER ,
INCIDENT_HOUSE_NUMBER ,
OLD_INCIDENT_STREET_SUFFIX ,
INCIDENT_STREET_SUFFIX ,
OLD_INCIDENT_STREET ,
INCIDENT_STREET ,
OLD_INCIDENT_STREET_NUMBER ,
INCIDENT_STREET_NUMBER ,
OLD_INCIDENT_FLOOR ,
INCIDENT_FLOOR ,
OLD_INCIDENT_SUITE ,
INCIDENT_SUITE ,
OLD_INCIDENT_POSTAL_PLUS4_CODE ,
INCIDENT_POSTAL_PLUS4_CODE ,
OLD_INCIDENT_POSITION ,
INCIDENT_POSITION ,
OLD_INCIDENT_LOC_DIRECTIONS ,
INCIDENT_LOC_DIRECTIONS ,
OLD_INCIDENT_LOC_DESCRIPTION ,
INCIDENT_LOC_DESCRIPTION ,
OLD_INSTALL_SITE_ID ,
INSTALL_SITE_ID ,
INCIDENT_LAST_MODIFIED_DATE ,
UPDATED_ENTITY_CODE ,
UPDATED_ENTITY_ID ,
ENTITY_ACTIVITY_CODE ,
OLD_TIER_VERSION ,
TIER_VERSION ,
--anmukher --09/12/03
OLD_INC_OBJECT_VERSION_NUMBER ,
INC_OBJECT_VERSION_NUMBER ,
OLD_INC_REQUEST_ID ,
INC_REQUEST_ID ,
OLD_INC_PROGRAM_APPLICATION_ID ,
INC_PROGRAM_APPLICATION_ID ,
OLD_INC_PROGRAM_ID ,
INC_PROGRAM_ID ,
OLD_INC_PROGRAM_UPDATE_DATE ,
INC_PROGRAM_UPDATE_DATE ,
OLD_OWNING_DEPARTMENT_ID ,
OWNING_DEPARTMENT_ID ,
OLD_INCIDENT_LOCATION_TYPE ,
INCIDENT_LOCATION_TYPE ,
OLD_UNASSIGNED_INDICATOR ,
UNASSIGNED_INDICATOR ,
OLD_MAINT_ORGANIZATION_ID ,
MAINT_ORGANIZATION_ID
)
VALUES(
l_audit_id,
p_request_id,
p_last_update_date,
p_user_id,
p_last_update_date,
p_user_id,
P_login_id,
TO_CHAR(p_creation_date,'HH24:MI:SS'),
l_audit_vals_rec.incident_status_id ,
l_audit_vals_rec.old_incident_status_id ,
l_audit_vals_rec.change_incident_status_flag ,
l_audit_vals_rec.incident_type_id ,
l_audit_vals_rec.old_incident_type_id ,
l_audit_vals_rec.change_incident_type_flag ,
l_audit_vals_rec.incident_urgency_id ,
l_audit_vals_rec.old_incident_urgency_id ,
l_audit_vals_rec.change_incident_urgency_flag ,
l_audit_vals_rec.incident_severity_id ,
l_audit_vals_rec.old_incident_severity_id ,
l_audit_vals_rec.change_incident_severity_flag,
l_audit_vals_rec.incident_owner_id ,
l_audit_vals_rec.old_incident_owner_id ,
l_audit_vals_rec.change_incident_owner_flag ,
l_audit_vals_rec.create_manual_action ,
l_audit_vals_rec.action_id ,
l_audit_vals_rec.expected_resolution_date ,
l_audit_vals_rec.old_expected_resolution_date ,
l_audit_vals_rec.change_resolution_flag ,
l_audit_vals_rec.new_workflow_flag ,
l_audit_vals_rec.workflow_process_name ,
l_audit_vals_rec.workflow_process_itemkey ,
l_audit_vals_rec.group_id,
l_audit_vals_rec.old_group_id,
l_audit_vals_rec.change_group_flag,
l_audit_vals_rec.group_type,
l_audit_vals_rec.old_group_type,
l_audit_vals_rec.change_group_type_flag,
l_audit_vals_rec.inv_platform_org_id,
l_audit_vals_rec.old_inv_platform_org_id,
l_audit_vals_rec.change_platform_org_id_flag,
l_audit_vals_rec.owner_assigned_time,
l_audit_vals_rec.old_owner_assigned_time,
l_audit_vals_rec.change_assigned_time_flag,
l_audit_vals_rec.obligation_date,
l_audit_vals_rec.old_obligation_date,
l_audit_vals_rec.change_obligation_flag,
l_audit_vals_rec.site_id,
l_audit_vals_rec.old_site_id,
l_audit_vals_rec.change_site_flag,
l_audit_vals_rec.bill_to_contact_id,
l_audit_vals_rec.old_bill_to_contact_id,
l_audit_vals_rec.change_bill_to_flag,
l_audit_vals_rec.ship_to_contact_id,
l_audit_vals_rec.old_ship_to_contact_id,
l_audit_vals_rec.change_ship_to_flag,
l_audit_vals_rec.close_date,
l_audit_vals_rec.old_close_date,
l_audit_vals_rec.change_close_date_flag,
l_audit_vals_rec.customer_product_id,
l_audit_vals_rec.old_customer_product_id,
l_audit_vals_rec.change_customer_product_flag,
l_audit_vals_rec.platform_id,
l_audit_vals_rec.old_platform_id,
l_audit_vals_rec.change_platform_id_flag,
l_audit_vals_rec.product_revision,
l_audit_vals_rec.old_product_revision,
l_audit_vals_rec.change_product_revision_flag,
l_audit_vals_rec.component_version, -- cp component versions
l_audit_vals_rec.old_component_version,
l_audit_vals_rec.change_comp_ver_flag,
l_audit_vals_rec.subcomponent_version,
l_audit_vals_rec.old_subcomponent_version,
l_audit_vals_rec.change_subcomp_ver_flag,
l_audit_vals_rec.cp_component_id,
l_audit_vals_rec.old_cp_component_id,
l_audit_vals_rec.change_cp_component_id_flag,
l_audit_vals_rec.cp_component_version_id,
l_audit_vals_rec.old_cp_component_version_id,
l_audit_vals_rec.change_cp_comp_ver_id_flag,
l_audit_vals_rec.cp_subcomponent_id,
l_audit_vals_rec.old_cp_subcomponent_id,
l_audit_vals_rec.change_cp_subcomponent_id_flag,
l_audit_vals_rec.cp_subcomponent_version_id,
l_audit_vals_rec.old_cp_subcomponent_version_id,
l_audit_vals_rec.change_cp_subcomp_ver_id_flag,
l_audit_vals_rec.cp_revision_id,
l_audit_vals_rec.old_cp_revision_id,
l_audit_vals_rec.change_cp_revision_id_flag,
l_audit_vals_rec.inv_item_revision,
l_audit_vals_rec.old_inv_item_revision,
l_audit_vals_rec.change_inv_item_revision,
l_audit_vals_rec.inv_component_id,
l_audit_vals_rec.old_inv_component_id,
l_audit_vals_rec.change_inv_component_id,
l_audit_vals_rec.inv_component_version,
l_audit_vals_rec.old_inv_component_version,
l_audit_vals_rec.change_inv_component_version,
l_audit_vals_rec.inv_subcomponent_id,
l_audit_vals_rec.old_inv_subcomponent_id,
l_audit_vals_rec.change_inv_subcomponent_id,
l_audit_vals_rec.inv_subcomponent_version,
l_audit_vals_rec.old_inv_subcomponent_version,
l_audit_vals_rec.change_inv_subcomp_version,
l_audit_vals_rec.territory_id,
l_audit_vals_rec.old_territory_id,
l_audit_vals_rec.change_territory_id_flag,
l_audit_vals_rec.resource_type,
l_audit_vals_rec.old_resource_type,
l_audit_vals_rec.change_resource_type_flag,
1,
'',
l_audit_vals_rec.inventory_item_id ,
l_audit_vals_rec.old_inventory_item_id,
l_audit_vals_rec.change_inventory_item_flag,
l_audit_vals_rec.inv_organization_id,
l_audit_vals_rec.old_inv_organization_id,
l_audit_vals_rec.change_inv_organization_flag,
l_audit_vals_rec.status_flag,
l_audit_vals_rec.old_status_flag,
l_audit_vals_rec.change_status_flag,
l_audit_vals_rec.incident_date,
l_audit_vals_rec.old_incident_date,
l_audit_vals_rec.change_incident_date_flag,
l_audit_vals_rec.platform_version_id,
l_audit_vals_rec.old_platform_version_id,
l_audit_vals_rec.change_plat_ver_id_flag,
l_audit_vals_rec.language_id,
l_audit_vals_rec.old_language_id,
l_audit_vals_rec.change_language_id_flag,
--l_audit_vals_rec.primary_contact_id,
--l_audit_vals_rec.change_primary_contact_flag,
--l_audit_vals_rec.old_primary_contact_id,
-- Added new audit columns for 11.5.10 Auditing project --anmukher --09/10/03
l_audit_vals_rec.UPGRADE_FLAG_FOR_CREATE ,
l_audit_vals_rec.OLD_INCIDENT_NUMBER ,
l_audit_vals_rec.INCIDENT_NUMBER ,
l_audit_vals_rec.OLD_CUSTOMER_ID ,
l_audit_vals_rec.CUSTOMER_ID ,
l_audit_vals_rec.OLD_BILL_TO_SITE_USE_ID ,
l_audit_vals_rec.BILL_TO_SITE_USE_ID ,
l_audit_vals_rec.OLD_EMPLOYEE_ID ,
l_audit_vals_rec.EMPLOYEE_ID ,
l_audit_vals_rec.OLD_SHIP_TO_SITE_USE_ID ,
l_audit_vals_rec.SHIP_TO_SITE_USE_ID ,
l_audit_vals_rec.OLD_PROBLEM_CODE ,
l_audit_vals_rec.PROBLEM_CODE ,
l_audit_vals_rec.OLD_ACTUAL_RESOLUTION_DATE ,
l_audit_vals_rec.ACTUAL_RESOLUTION_DATE ,
l_audit_vals_rec.OLD_INSTALL_SITE_USE_ID ,
l_audit_vals_rec.INSTALL_SITE_USE_ID ,
l_audit_vals_rec.OLD_CURRENT_SERIAL_NUMBER ,
l_audit_vals_rec.CURRENT_SERIAL_NUMBER ,
l_audit_vals_rec.OLD_SYSTEM_ID ,
l_audit_vals_rec.SYSTEM_ID ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_1 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_1 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_2 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_2 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_3 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_3 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_4 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_4 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_5 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_5 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_6 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_6 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_7 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_7 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_8 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_8 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_9 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_9 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_10 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_10 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_11 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_11 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_12 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_12 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_13 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_13 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_14 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_14 ,
l_audit_vals_rec.OLD_INCIDENT_ATTRIBUTE_15 ,
l_audit_vals_rec.INCIDENT_ATTRIBUTE_15 ,
l_audit_vals_rec.OLD_INCIDENT_CONTEXT ,
l_audit_vals_rec.INCIDENT_CONTEXT ,
l_audit_vals_rec.OLD_RESOLUTION_CODE ,
l_audit_vals_rec.RESOLUTION_CODE ,
l_audit_vals_rec.OLD_ORIGINAL_ORDER_NUMBER ,
l_audit_vals_rec.ORIGINAL_ORDER_NUMBER ,
l_audit_vals_rec.OLD_ORG_ID ,
l_audit_vals_rec.ORG_ID ,
l_audit_vals_rec.OLD_PURCHASE_ORDER_NUMBER ,
l_audit_vals_rec.PURCHASE_ORDER_NUMBER ,
l_audit_vals_rec.OLD_PUBLISH_FLAG ,
l_audit_vals_rec.PUBLISH_FLAG ,
l_audit_vals_rec.OLD_QA_COLLECTION_ID ,
l_audit_vals_rec.QA_COLLECTION_ID ,
l_audit_vals_rec.OLD_CONTRACT_ID ,
l_audit_vals_rec.CONTRACT_ID ,
l_audit_vals_rec.OLD_CONTRACT_NUMBER ,
l_audit_vals_rec.CONTRACT_NUMBER ,
l_audit_vals_rec.OLD_CONTRACT_SERVICE_ID ,
l_audit_vals_rec.CONTRACT_SERVICE_ID ,
l_audit_vals_rec.OLD_TIME_ZONE_ID ,
l_audit_vals_rec.TIME_ZONE_ID ,
l_audit_vals_rec.OLD_ACCOUNT_ID ,
l_audit_vals_rec.ACCOUNT_ID ,
l_audit_vals_rec.OLD_TIME_DIFFERENCE ,
l_audit_vals_rec.TIME_DIFFERENCE ,
l_audit_vals_rec.OLD_CUSTOMER_PO_NUMBER ,
l_audit_vals_rec.CUSTOMER_PO_NUMBER ,
l_audit_vals_rec.OLD_CUSTOMER_TICKET_NUMBER ,
l_audit_vals_rec.CUSTOMER_TICKET_NUMBER ,
l_audit_vals_rec.OLD_CUSTOMER_SITE_ID ,
l_audit_vals_rec.CUSTOMER_SITE_ID ,
l_audit_vals_rec.OLD_CALLER_TYPE ,
l_audit_vals_rec.CALLER_TYPE ,
-- l_audit_vals_rec.OLD_OBJECT_VERSION_NUMBER ,
l_audit_vals_rec.OLD_SECURITY_GROUP_ID ,
l_audit_vals_rec.OLD_ORIG_SYSTEM_REFERENCE ,
l_audit_vals_rec.ORIG_SYSTEM_REFERENCE ,
l_audit_vals_rec.OLD_ORIG_SYSTEM_REFERENCE_ID ,
l_audit_vals_rec.ORIG_SYSTEM_REFERENCE_ID ,
l_audit_vals_rec.REQUEST_ID ,
l_audit_vals_rec.PROGRAM_APPLICATION_ID ,
l_audit_vals_rec.PROGRAM_ID ,
l_audit_vals_rec.PROGRAM_UPDATE_DATE ,
l_audit_vals_rec.OLD_PROJECT_NUMBER ,
l_audit_vals_rec.PROJECT_NUMBER ,
l_audit_vals_rec.OLD_PLATFORM_VERSION ,
l_audit_vals_rec.PLATFORM_VERSION ,
l_audit_vals_rec.OLD_DB_VERSION ,
l_audit_vals_rec.DB_VERSION ,
l_audit_vals_rec.OLD_CUST_PREF_LANG_ID ,
l_audit_vals_rec.CUST_PREF_LANG_ID ,
l_audit_vals_rec.OLD_TIER ,
l_audit_vals_rec.TIER ,
l_audit_vals_rec.OLD_CATEGORY_ID ,
l_audit_vals_rec.CATEGORY_ID ,
l_audit_vals_rec.OLD_OPERATING_SYSTEM ,
l_audit_vals_rec.OPERATING_SYSTEM ,
l_audit_vals_rec.OLD_OPERATING_SYSTEM_VERSION ,
l_audit_vals_rec.OPERATING_SYSTEM_VERSION ,
l_audit_vals_rec.OLD_DATABASE ,
l_audit_vals_rec.DATABASE ,
l_audit_vals_rec.OLD_GROUP_TERRITORY_ID ,
l_audit_vals_rec.GROUP_TERRITORY_ID ,
l_audit_vals_rec.OLD_COMM_PREF_CODE ,
l_audit_vals_rec.COMM_PREF_CODE ,
l_audit_vals_rec.OLD_LAST_UPDATE_CHANNEL ,
l_audit_vals_rec.LAST_UPDATE_CHANNEL ,
l_audit_vals_rec.OLD_CUST_PREF_LANG_CODE ,
l_audit_vals_rec.CUST_PREF_LANG_CODE ,
l_audit_vals_rec.OLD_ERROR_CODE ,
l_audit_vals_rec.ERROR_CODE ,
l_audit_vals_rec.OLD_CATEGORY_SET_ID ,
l_audit_vals_rec.CATEGORY_SET_ID ,
l_audit_vals_rec.OLD_EXTERNAL_REFERENCE ,
l_audit_vals_rec.EXTERNAL_REFERENCE ,
l_audit_vals_rec.OLD_INCIDENT_OCCURRED_DATE ,
l_audit_vals_rec.INCIDENT_OCCURRED_DATE ,
l_audit_vals_rec.OLD_INCIDENT_RESOLVED_DATE ,
l_audit_vals_rec.INCIDENT_RESOLVED_DATE ,
l_audit_vals_rec.OLD_INC_RESPONDED_BY_DATE ,
l_audit_vals_rec.INC_RESPONDED_BY_DATE ,
l_audit_vals_rec.OLD_INCIDENT_LOCATION_ID ,
l_audit_vals_rec.INCIDENT_LOCATION_ID ,
l_audit_vals_rec.OLD_INCIDENT_ADDRESS ,
l_audit_vals_rec.INCIDENT_ADDRESS ,
l_audit_vals_rec.OLD_INCIDENT_CITY ,
l_audit_vals_rec.INCIDENT_CITY ,
l_audit_vals_rec.OLD_INCIDENT_STATE ,
l_audit_vals_rec.INCIDENT_STATE ,
l_audit_vals_rec.OLD_INCIDENT_COUNTRY ,
l_audit_vals_rec.INCIDENT_COUNTRY ,
l_audit_vals_rec.OLD_INCIDENT_PROVINCE ,
l_audit_vals_rec.INCIDENT_PROVINCE ,
l_audit_vals_rec.OLD_INCIDENT_POSTAL_CODE ,
l_audit_vals_rec.INCIDENT_POSTAL_CODE ,
l_audit_vals_rec.OLD_INCIDENT_COUNTY ,
l_audit_vals_rec.INCIDENT_COUNTY ,
l_audit_vals_rec.OLD_SR_CREATION_CHANNEL ,
l_audit_vals_rec.SR_CREATION_CHANNEL ,
l_audit_vals_rec.OLD_DEF_DEFECT_ID ,
l_audit_vals_rec.DEF_DEFECT_ID ,
l_audit_vals_rec.OLD_DEF_DEFECT_ID2 ,
l_audit_vals_rec.DEF_DEFECT_ID2 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_1 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_1 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_2 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_2 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_3 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_3 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_4 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_4 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_5 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_5 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_6 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_6 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_7 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_7 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_8 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_8 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_9 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_9 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_10 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_10 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_11 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_11 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_12 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_12 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_13 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_13 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_14 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_14 ,
l_audit_vals_rec.OLD_EXTERNAL_ATTRIBUTE_15 ,
l_audit_vals_rec.EXTERNAL_ATTRIBUTE_15 ,
l_audit_vals_rec.OLD_EXTERNAL_CONTEXT ,
l_audit_vals_rec.EXTERNAL_CONTEXT ,
l_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE ,
l_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE ,
l_audit_vals_rec.OLD_CREATION_PROGRAM_CODE ,
l_audit_vals_rec.CREATION_PROGRAM_CODE ,
l_audit_vals_rec.OLD_COVERAGE_TYPE ,
l_audit_vals_rec.COVERAGE_TYPE ,
l_audit_vals_rec.OLD_BILL_TO_ACCOUNT_ID ,
l_audit_vals_rec.BILL_TO_ACCOUNT_ID ,
l_audit_vals_rec.OLD_SHIP_TO_ACCOUNT_ID ,
l_audit_vals_rec.SHIP_TO_ACCOUNT_ID ,
l_audit_vals_rec.OLD_CUSTOMER_EMAIL_ID ,
l_audit_vals_rec.CUSTOMER_EMAIL_ID ,
l_audit_vals_rec.OLD_CUSTOMER_PHONE_ID ,
l_audit_vals_rec.CUSTOMER_PHONE_ID ,
l_audit_vals_rec.OLD_BILL_TO_PARTY_ID ,
l_audit_vals_rec.BILL_TO_PARTY_ID ,
l_audit_vals_rec.OLD_SHIP_TO_PARTY_ID ,
l_audit_vals_rec.SHIP_TO_PARTY_ID ,
l_audit_vals_rec.OLD_BILL_TO_SITE_ID ,
l_audit_vals_rec.BILL_TO_SITE_ID ,
l_audit_vals_rec.OLD_SHIP_TO_SITE_ID ,
l_audit_vals_rec.SHIP_TO_SITE_ID ,
l_audit_vals_rec.OLD_PROGRAM_LOGIN_ID ,
l_audit_vals_rec.PROGRAM_LOGIN_ID ,
l_audit_vals_rec.OLD_INCIDENT_POINT_OF_INTEREST ,
l_audit_vals_rec.INCIDENT_POINT_OF_INTEREST ,
l_audit_vals_rec.OLD_INCIDENT_CROSS_STREET ,
l_audit_vals_rec.INCIDENT_CROSS_STREET ,
l_audit_vals_rec.OLD_INCIDENT_DIRECTION_QUALIF ,
l_audit_vals_rec.INCIDENT_DIRECTION_QUALIF ,
l_audit_vals_rec.OLD_INCIDENT_DISTANCE_QUALIF ,
l_audit_vals_rec.INCIDENT_DISTANCE_QUALIF ,
l_audit_vals_rec.OLD_INCIDENT_DISTANCE_QUAL_UOM ,
l_audit_vals_rec.INCIDENT_DISTANCE_QUAL_UOM ,
l_audit_vals_rec.OLD_INCIDENT_ADDRESS2 ,
l_audit_vals_rec.INCIDENT_ADDRESS2 ,
l_audit_vals_rec.OLD_INCIDENT_ADDRESS3 ,
l_audit_vals_rec.INCIDENT_ADDRESS3 ,
l_audit_vals_rec.OLD_INCIDENT_ADDRESS4 ,
l_audit_vals_rec.INCIDENT_ADDRESS4 ,
l_audit_vals_rec.OLD_INCIDENT_ADDRESS_STYLE ,
l_audit_vals_rec.INCIDENT_ADDRESS_STYLE ,
l_audit_vals_rec.OLD_INCIDENT_ADDR_LNS_PHONETIC ,
l_audit_vals_rec.INCIDENT_ADDR_LNS_PHONETIC ,
l_audit_vals_rec.OLD_INCIDENT_PO_BOX_NUMBER ,
l_audit_vals_rec.INCIDENT_PO_BOX_NUMBER ,
l_audit_vals_rec.OLD_INCIDENT_HOUSE_NUMBER ,
l_audit_vals_rec.INCIDENT_HOUSE_NUMBER ,
l_audit_vals_rec.OLD_INCIDENT_STREET_SUFFIX ,
l_audit_vals_rec.INCIDENT_STREET_SUFFIX ,
l_audit_vals_rec.OLD_INCIDENT_STREET ,
l_audit_vals_rec.INCIDENT_STREET ,
l_audit_vals_rec.OLD_INCIDENT_STREET_NUMBER ,
l_audit_vals_rec.INCIDENT_STREET_NUMBER ,
l_audit_vals_rec.OLD_INCIDENT_FLOOR ,
l_audit_vals_rec.INCIDENT_FLOOR ,
l_audit_vals_rec.OLD_INCIDENT_SUITE ,
l_audit_vals_rec.INCIDENT_SUITE ,
l_audit_vals_rec.OLD_INCIDENT_POSTAL_PLUS4_CODE ,
l_audit_vals_rec.INCIDENT_POSTAL_PLUS4_CODE ,
l_audit_vals_rec.OLD_INCIDENT_POSITION ,
l_audit_vals_rec.INCIDENT_POSITION ,
l_audit_vals_rec.OLD_INCIDENT_LOC_DIRECTIONS ,
l_audit_vals_rec.INCIDENT_LOC_DIRECTIONS ,
l_audit_vals_rec.OLD_INCIDENT_LOC_DESCRIPTION ,
l_audit_vals_rec.INCIDENT_LOC_DESCRIPTION ,
l_audit_vals_rec.OLD_INSTALL_SITE_ID ,
l_audit_vals_rec.INSTALL_SITE_ID ,
l_audit_vals_rec.INCIDENT_LAST_MODIFIED_DATE ,
l_audit_vals_rec.UPDATED_ENTITY_CODE ,
l_audit_vals_rec.UPDATED_ENTITY_ID ,
l_audit_vals_rec.ENTITY_ACTIVITY_CODE ,
l_audit_vals_rec.OLD_TIER_VERSION ,
l_audit_vals_rec.TIER_VERSION ,
--anmukher --09/12/03
l_audit_vals_rec.OLD_INC_OBJECT_VERSION_NUMBER ,
l_audit_vals_rec.INC_OBJECT_VERSION_NUMBER ,
l_audit_vals_rec.OLD_INC_REQUEST_ID ,
l_audit_vals_rec.INC_REQUEST_ID ,
l_audit_vals_rec.OLD_INC_PROGRAM_APPLICATION_ID ,
l_audit_vals_rec.INC_PROGRAM_APPLICATION_ID ,
l_audit_vals_rec.OLD_INC_PROGRAM_ID ,
l_audit_vals_rec.INC_PROGRAM_ID ,
l_audit_vals_rec.OLD_INC_PROGRAM_UPDATE_DATE ,
l_audit_vals_rec.INC_PROGRAM_UPDATE_DATE ,
l_audit_vals_rec.OLD_OWNING_DEPARTMENT_ID ,
l_audit_vals_rec.OWNING_DEPARTMENT_ID ,
l_audit_vals_rec.OLD_INCIDENT_LOCATION_TYPE ,
l_audit_vals_rec.INCIDENT_LOCATION_TYPE ,
l_audit_vals_rec.OLD_UNASSIGNED_INDICATOR ,
l_audit_vals_rec.UNASSIGNED_INDICATOR ,
l_audit_vals_rec.OLD_MAINT_ORGANIZATION_ID ,
l_audit_vals_rec.MAINT_ORGANIZATION_ID
)
RETURNING incident_audit_id INTO l_Incident_Audit_Id;
INSERT INTO cs_incidents_audit_tl (
incident_audit_id,
incident_id,
LANGUAGE,
source_lang,
last_update_date,
last_updated_by,
creation_date,
created_by,
change_description
) SELECT
l_incident_audit_id,
p_request_id,
L.LANGUAGE_CODE,
USERENV('LANG'),
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_comments
FROM FND_LANGUAGES L
WHERE l.installed_flag IN ('I', 'B')
AND NOT EXISTS
(SELECT NULL
FROM cs_incidents_audit_tl t
WHERE t.incident_audit_id = l_incident_audit_id
AND t.LANGUAGE = l.language_code
) ;
p_disallow_request_update OUT NOCOPY VARCHAR2,
p_disallow_owner_update OUT NOCOPY VARCHAR2,
p_disallow_product_update OUT NOCOPY VARCHAR2,
p_employee_name OUT NOCOPY VARCHAR2,
p_inventory_item_id OUT NOCOPY NUMBER,
p_contract_id OUT NOCOPY NUMBER,
p_contract_number OUT NOCOPY VARCHAR2,
x_bill_to_site_id OUT NOCOPY NUMBER,
x_ship_to_site_id OUT NOCOPY NUMBER,
x_bill_to_site_use_id OUT NOCOPY NUMBER,
x_ship_to_site_use_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_group_name OUT NOCOPY VARCHAR2,
x_owner_name OUT NOCOPY VARCHAR2,
x_product_revision OUT NOCOPY VARCHAR2,
x_component_version OUT NOCOPY VARCHAR2,
x_subcomponent_version OUT NOCOPY VARCHAR2,
-- for cmro
p_cmro_flag IN VARCHAR2,
p_maintenance_flag IN VARCHAR2,
p_sr_mode IN VARCHAR2
)
IS
l_return_status VARCHAR2(1);
l_can_update VARCHAR2(1);
P_status_id => p_service_request_rec.updated_status_id,
p_resp_id => p_resp_id,
p_resp_appl_id => NVL(p_resp_appl_id,fnd_global.resp_appl_id) -- new for 11.5.10
p_business_usage => NULL, -- new for 11.5.10
p_ss_srtype_restrict => NULL, -- new for 11.5.10
p_operation => p_operation,
x_return_status => l_return_status,
x_cmro_flag => lx_cmro_flag, -- new for 11.5.10
x_maintenance_flag => lx_maintenance_flag ); -- new for 11.5.10
IF FND_API.To_Boolean(p_service_request_rec.validate_updated_status) THEN
IF p_service_request_rec.old_type_id <> FND_API.G_MISS_NUM AND
p_service_request_rec.old_type_id IS NOT NULL THEN
l_old_type_id := p_service_request_rec.old_type_id;
CS_ServiceRequest_UTIL.Validate_Updated_Status
( p_api_name => p_api_name,
p_parameter_name => 'p_status_id',
p_resp_id => p_resp_id ,
p_new_status_id => p_service_request_rec.updated_status_id,
p_old_status_id => p_service_request_rec.status_id,
p_subtype => G_SR_SUBTYPE,
p_type_id => p_service_request_rec.type_id,
p_old_type_id => l_old_type_id,
p_close_flag => p_close_flag,
p_disallow_request_update => p_disallow_request_update,
p_disallow_owner_update => p_disallow_owner_update,
p_disallow_product_update => p_disallow_product_update,
x_return_status => l_return_status
);
FND_PROFILE.Get('INC_PUBLISH_FLAG_UPDATE', l_can_update) ;
IF ((l_can_update = 'N' ) OR (l_can_update IS NULL)) THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_CANT_CHANGE_PUBLISH');
l_disallow_request_update VARCHAR2(1);
l_disallow_owner_update VARCHAR2(1);
l_disallow_product_update VARCHAR2(1);
SELECT resource_name
INTO l_service_request_rec.owner
FROM jtf_rs_resource_extns_tl
WHERE resource_id = l_service_request_rec.owner_id
AND LANGUAGE = USERENV('LANG');
SELECT group_name INTO l_service_request_rec.group_owner
FROM jtf_rs_groups_tl
WHERE group_id =l_service_request_rec.owner_group_id
AND LANGUAGE = USERENV('LANG');
SELECT team_name INTO l_service_request_rec.group_owner
FROM jtf_rs_teams_tl
WHERE team_id =l_service_request_rec.owner_group_id
AND LANGUAGE = USERENV('LANG');
SELECT responded_flag,resolved_flag
INTO l_responded_flag,l_resolved_flag
FROM cs_incident_statuses_vl
WHERE incident_status_id=l_service_request_rec.status_id ;
SELECT incident_id
INTO l_test
FROM cs_incidents_all_b
WHERE incident_id = p_request_id;
SELECT incident_number
INTO l_test
FROM cs_incidents_all_b
WHERE incident_number = p_request_number;
IF l_service_request_rec.last_updated_by IS NOT NULL AND
l_service_request_rec.last_updated_by <> FND_API.G_Miss_NUM THEN
CS_ServiceRequest_UTIL.Validate_Who_Info
( p_api_name => l_api_name_full,
p_parameter_name_usr => 'p_last_updated_by',
p_parameter_name_login => NULL,
p_user_id => l_service_request_rec.last_updated_by,
p_login_id => NULL,
x_return_status => l_return_status);
p_disallow_request_update => l_disallow_request_update,
p_disallow_owner_update => l_disallow_owner_update,
p_disallow_product_update => l_disallow_product_update,
p_employee_name => l_employee_name,
p_inventory_item_id => l_cp_item_id,
p_contract_id => x_contra_id,
p_contract_number => x_contract_number,
x_bill_to_site_id => l_bill_to_site_id,
x_ship_to_site_id => l_ship_to_site_id,
x_bill_to_site_use_id => l_bill_to_site_use_id,
x_ship_to_site_use_id => l_ship_to_site_use_id,
x_return_status => x_return_status,
x_group_name => l_group_name,
x_owner_name => l_owner_name,
x_product_revision => l_service_request_rec.product_revision ,
x_component_version => l_service_request_rec.component_version,
x_subcomponent_version => l_service_request_rec.subcomponent_version,
-- cmro_eam
p_cmro_flag => p_cmro_flag,
p_maintenance_flag => p_maintenance_flag,
p_sr_mode => 'CREATE'
);
SELECT autolaunch_workflow_flag, abort_workflow_close_flag, workflow
INTO x_autolaunch_wkf_flag, x_abort_wkf_close_flag, x_wkf_process_name
FROM cs_incident_types_b
WHERE incident_type_id = l_service_request_rec.type_id
AND incident_subtype = G_SR_SUBTYPE
;
IF (l_service_request_rec.LAST_UPDATE_CHANNEL <> FND_API.G_MISS_CHAR) AND
(l_service_request_rec.LAST_UPDATE_CHANNEL IS NOT NULL) THEN
x_audit_vals_rec.LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
IF (l_service_request_rec.LAST_UPDATE_PROGRAM_CODE <> FND_API.G_MISS_CHAR) AND
(l_service_request_rec.LAST_UPDATE_PROGRAM_CODE IS NOT NULL) THEN
x_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
x_audit_vals_rec.INC_PROGRAM_UPDATE_DATE := l_service_request_rec.;
IF (l_service_request_rec.last_update_channel = FND_API.G_MISS_CHAR) THEN
l_service_request_rec.last_update_channel := NULL;
IF (l_service_request_rec.last_update_date = FND_API.G_MISS_DATE OR
l_service_request_rec.last_update_date IS NULL ) THEN
-- l_service_request_rec.last_update_date := SYSDATE;
l_service_request_rec.last_update_date := l_sysdate;
IF (l_service_request_rec.last_updated_by = FND_API.G_MISS_NUM OR
l_service_request_rec.last_updated_by IS NULL ) THEN
l_service_request_rec.last_updated_by := p_user_id;
IF (l_service_request_rec.last_update_login = FND_API.G_MISS_NUM OR
l_service_request_rec.last_update_login IS NULL ) THEN
l_service_request_rec.last_update_login := p_login_id;
SELECT cs_incidents_s.NEXTVAL INTO x_req_id FROM dual;
SELECT incident_id INTO l_temp_id FROM cs_incidents_all_b
WHERE incident_id = x_req_id;
SELECT cs_incidents_number_s.NEXTVAL INTO x_req_num FROM dual;
SELECT incident_number INTO l_temp_num FROM cs_incidents_all_b
WHERE incident_number = x_req_num;
PROCEDURE Update_SR_Validation(
p_api_version IN VARCHAR2,
p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
p_service_request_rec IN service_request_rec_type,
p_invocation_mode IN VARCHAR2 := 'NORMAL',
p_notes IN notes_table,
p_contacts IN contacts_table,
p_audit_comments IN VARCHAR2 DEFAULT NULL,
p_resp_id IN NUMBER DEFAULT NULL,
p_resp_appl_id IN NUMBER DEFAULT NULL,
p_request_id IN NUMBER,
p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_full,
p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER DEFAULT NULL,
p_last_update_date IN DATE,
p_object_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_contra_id OUT NOCOPY NUMBER,
x_contract_number OUT NOCOPY VARCHAR2,
x_owner_assigned_flag OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_audit_vals_rec OUT NOCOPY sr_audit_rec_type,
x_service_request_rec OUT NOCOPY service_request_rec_type,
x_autolaunch_wkf_flag OUT NOCOPY VARCHAR2,
x_abort_wkf_close_flag OUT NOCOPY VARCHAR2,
x_wkf_process_name OUT NOCOPY VARCHAR2,
x_workflow_process_id OUT NOCOPY NUMBER,
x_interaction_id OUT NOCOPY NUMBER,
p_update_desc_flex IN VARCHAR2 DEFAULT fnd_api.g_false,
p_called_by_workflow IN VARCHAR2 DEFAULT fnd_api.g_false,
p_workflow_process_id IN NUMBER DEFAULT NULL,
-- for cmro
p_cmro_flag IN VARCHAR2,
p_maintenance_flag IN VARCHAR2,
p_auto_assign IN VARCHAR2 := 'N'
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_SR_Validation';
l_operation VARCHAR2(300):= 'updated' ;
SELECT
B.ROWID ROW_ID,
B.INCIDENT_ID ,
B.LAST_UPDATE_DATE ,
B.LAST_UPDATED_BY ,
B.CREATION_DATE ,
B.CREATED_BY ,
B.LAST_UPDATE_LOGIN ,
B.INCIDENT_NUMBER ,
B.INCIDENT_DATE ,
B.INCIDENT_STATUS_ID ,
B.INCIDENT_TYPE_ID ,
B.INCIDENT_URGENCY_ID ,
B.INCIDENT_SEVERITY_ID ,
B.INCIDENT_OWNER_ID ,
B.RESOURCE_TYPE ,
B.RESOURCE_SUBTYPE_ID ,
B.INVENTORY_ITEM_ID ,
B.CUSTOMER_ID ,
B.CUSTOMER_NUMBER ,
B.ACCOUNT_ID ,
B.BILL_TO_SITE_USE_ID ,
B.PURCHASE_ORDER_NUM ,
B.EMPLOYEE_ID ,
B.FILED_BY_EMPLOYEE_FLAG ,
B.SHIP_TO_SITE_USE_ID ,
B.PROBLEM_CODE ,
B.EXPECTED_RESOLUTION_DATE ,
B.ACTUAL_RESOLUTION_DATE ,
B.CUSTOMER_PRODUCT_ID ,
B.BILL_TO_CONTACT_ID ,
B.SHIP_TO_CONTACT_ID ,
B.CURRENT_SERIAL_NUMBER ,
B.PRODUCT_REVISION ,
B.COMPONENT_VERSION,
B.SUBCOMPONENT_VERSION,
B.INCIDENT_ATTRIBUTE_1 ,
B.INCIDENT_ATTRIBUTE_2 ,
B.INCIDENT_ATTRIBUTE_3 ,
B.INCIDENT_ATTRIBUTE_4 ,
B.INCIDENT_ATTRIBUTE_5 ,
B.INCIDENT_ATTRIBUTE_6 ,
B.INCIDENT_ATTRIBUTE_7 ,
B.INCIDENT_ATTRIBUTE_8 ,
B.INCIDENT_ATTRIBUTE_9 ,
B.INCIDENT_ATTRIBUTE_10 ,
B.INCIDENT_ATTRIBUTE_11 ,
B.INCIDENT_ATTRIBUTE_12 ,
B.INCIDENT_ATTRIBUTE_13 ,
B.INCIDENT_ATTRIBUTE_14 ,
B.INCIDENT_ATTRIBUTE_15 ,
B.INCIDENT_CONTEXT ,
B.EXTERNAL_ATTRIBUTE_1 ,
B.EXTERNAL_ATTRIBUTE_2 ,
B.EXTERNAL_ATTRIBUTE_3 ,
B.EXTERNAL_ATTRIBUTE_4 ,
B.EXTERNAL_ATTRIBUTE_5 ,
B.EXTERNAL_ATTRIBUTE_6 ,
B.EXTERNAL_ATTRIBUTE_7 ,
B.EXTERNAL_ATTRIBUTE_8 ,
B.EXTERNAL_ATTRIBUTE_9 ,
B.EXTERNAL_ATTRIBUTE_10 ,
B.EXTERNAL_ATTRIBUTE_11 ,
B.EXTERNAL_ATTRIBUTE_12 ,
B.EXTERNAL_ATTRIBUTE_13 ,
B.EXTERNAL_ATTRIBUTE_14 ,
B.EXTERNAL_ATTRIBUTE_15 ,
B.EXTERNAL_CONTEXT ,
B.RECORD_IS_VALID_FLAG ,
B.RESOLUTION_CODE ,
B.ORG_ID ,
B.ORIGINAL_ORDER_NUMBER ,
B.WORKFLOW_PROCESS_ID ,
B.CLOSE_DATE ,
B.PUBLISH_FLAG ,
B.ESTIMATE_ID ,
B.ESTIMATE_BUSINESS_GROUP_ID ,
B.INTERFACED_TO_DEPOT_FLAG ,
B.QA_COLLECTION_ID ,
B.CONTRACT_SERVICE_ID ,
B.CONTRACT_ID,
B.CONTRACT_NUMBER,
B.PROJECT_NUMBER,
B.TIME_ZONE_ID ,
B.TIME_DIFFERENCE ,
B.CUSTOMER_PO_NUMBER ,
B.OWNER_GROUP_ID ,
B.CUSTOMER_TICKET_NUMBER ,
B.OBLIGATION_DATE ,
B.SITE_ID ,
B.CUSTOMER_SITE_ID ,
B.CALLER_TYPE ,
B.PLATFORM_ID ,
B.PLATFORM_VERSION,
B.DB_VERSION,
B.PLATFORM_VERSION_ID ,
B.CP_COMPONENT_ID ,
B.CP_COMPONENT_VERSION_ID ,
B.CP_SUBCOMPONENT_ID ,
B.CP_SUBCOMPONENT_VERSION_ID ,
B.CP_REVISION_ID ,
B.INV_ITEM_REVISION,
B.INV_COMPONENT_ID,
B.INV_COMPONENT_VERSION,
B.INV_SUBCOMPONENT_ID,
B.INV_SUBCOMPONENT_VERSION,
B.LANGUAGE_ID,
B.TERRITORY_ID,
B.INV_ORGANIZATION_ID,
B.OBJECT_VERSION_NUMBER ,
-- Added for enhancement 11.5.6
B.CUST_PREF_LANG_ID,
B.TIER,
B.TIER_VERSION,
B.OPERATING_SYSTEM,
B.OPERATING_SYSTEM_VERSION,
B.DATABASE,
B.CATEGORY_ID,
B.GROUP_TYPE,
B.GROUP_TERRITORY_ID,
B.OWNER_ASSIGNED_TIME,
B.OWNER_ASSIGNED_FLAG,
B.INV_PLATFORM_ORG_ID,
B.COMM_PREF_CODE,
B.CUST_PREF_LANG_CODE,
B.LAST_UPDATE_CHANNEL,
B.CATEGORY_SET_ID,
B.EXTERNAL_REFERENCE,
B.SYSTEM_ID,
B.ERROR_CODE,
B.INCIDENT_ADDRESS,
B.INCIDENT_CITY,
B.INCIDENT_STATE,
B.INCIDENT_COUNTRY,
B.INCIDENT_PROVINCE,
B.INCIDENT_POSTAL_CODE,
B.INCIDENT_COUNTY,
B.SR_CREATION_CHANNEL,
TL.RESOLUTION_SUMMARY,
-- Added for Enh# 1830701
B.INCIDENT_OCCURRED_DATE,
B.INCIDENT_RESOLVED_DATE,
B.INC_RESPONDED_BY_DATE,
-- Added for Enh# 222054
B.INCIDENT_LOCATION_ID,
-- Added for ER# 2320056
B.COVERAGE_TYPE,
-- Added for ER#2433831
B.BILL_TO_ACCOUNT_ID,
B.SHIP_TO_ACCOUNT_ID,
-- Added for ER#2463321
B.CUSTOMER_PHONE_ID,
B.CUSTOMER_EMAIL_ID,
-- Added for 11.5.9
B.BILL_TO_SITE_ID,
B.SHIP_TO_SITE_ID,
B.BILL_TO_PARTY_ID,
B.SHIP_TO_PARTY_ID,
B.CREATION_PROGRAM_CODE,
B.LAST_UPDATE_PROGRAM_CODE,
B.PROGRAM_ID,
B.PROGRAM_APPLICATION_ID,
B.REQUEST_ID,
B.PROGRAM_LOGIN_ID,
-- Added for Enh# 2216664
TL.OWNER,
TL.GROUP_OWNER,
TL.LANGUAGE ,
TL.SOURCE_LANG ,
TL.SUMMARY ,
B.INSTALL_SITE_ID,
B.INSTALL_SITE_USE_ID,
--TL.SR_CREATION_CHANNEL
B.STATUS_FLAG,
-- Added address columns by shijain 26thdec 2002
B.INCIDENT_POINT_OF_INTEREST ,
B.INCIDENT_CROSS_STREET ,
B.INCIDENT_DIRECTION_QUALIFIER ,
B.INCIDENT_DISTANCE_QUALIFIER ,
B.INCIDENT_DISTANCE_QUAL_UOM ,
B.INCIDENT_ADDRESS2 ,
B.INCIDENT_ADDRESS3 ,
B.INCIDENT_ADDRESS4 ,
B.INCIDENT_ADDRESS_STYLE ,
B.INCIDENT_ADDR_LINES_PHONETIC ,
B.INCIDENT_PO_BOX_NUMBER ,
B.INCIDENT_HOUSE_NUMBER ,
B.INCIDENT_STREET_SUFFIX ,
B.INCIDENT_STREET ,
B.INCIDENT_STREET_NUMBER ,
B.INCIDENT_FLOOR,
B.INCIDENT_SUITE ,
B.INCIDENT_POSTAL_PLUS4_CODE ,
B.INCIDENT_POSITION ,
B.INCIDENT_LOCATION_DIRECTIONS,
B.INCIDENT_LOCATION_DESCRIPTION,
--for cmro_eam
B.OWNING_DEPARTMENT_ID,
--end of cmro_eam
-- Added for Misc ERs project (11.5.10) --anmukher --08/26/03
B.INCIDENT_LOCATION_TYPE ,
--B.PRIMARY_CONTACT_ID
B.maint_organization_id
FROM cs_incidents_all_b b, cs_incidents_all_tl tl
WHERE b.incident_id = p_request_id
AND b.incident_id = tl.incident_id
AND tl.LANGUAGE = DECODE(l_service_request_rec.LANGUAGE,
FND_API.G_MISS_CHAR, USERENV('LANG'),
NULL, USERENV('LANG'), l_service_request_rec.LANGUAGE)
FOR UPDATE OF b.incident_id;
l_update_desc_flex VARCHAR2(1) := p_update_desc_flex;
l_disallow_request_update VARCHAR2(1);
l_disallow_owner_update VARCHAR2(1);
l_disallow_product_update VARCHAR2(1);
l_party_id_update VARCHAR2(1);
l_contact_point_id_update VARCHAR2(1);
l_contact_point_type_update VARCHAR2(1);
l_contact_type_update VARCHAR2(1);
l_primary_flag_update VARCHAR2(1) ;
l_update_desc_flex_int varchar2(1) := FND_API.G_FALSE;
l_update_desc_flex_ext varchar2(1) := FND_API.G_FALSE;
l_update_desc_flex_int := FND_API.G_TRUE;
END IF; -- to check if any flex field segment is updated
IF ( l_update_desc_flex_int = FND_API.G_TRUE and
p_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
Cs_Servicerequest_Util.Validate_Desc_Flex(
p_api_name => l_api_name_full,
p_application_short_name => 'CS',
p_desc_flex_name => 'CS_INCIDENTS_ALL_B',
p_desc_segment1 => l_service_request_rec.request_attribute_1,
p_desc_segment2 => l_service_request_rec.request_attribute_2,
p_desc_segment3 => l_service_request_rec.request_attribute_3,
p_desc_segment4 => l_service_request_rec.request_attribute_4,
p_desc_segment5 => l_service_request_rec.request_attribute_5,
p_desc_segment6 => l_service_request_rec.request_attribute_6,
p_desc_segment7 => l_service_request_rec.request_attribute_7,
p_desc_segment8 => l_service_request_rec.request_attribute_8,
p_desc_segment9 => l_service_request_rec.request_attribute_9,
p_desc_segment10 => l_service_request_rec.request_attribute_10,
p_desc_segment11 => l_service_request_rec.request_attribute_11,
p_desc_segment12 => l_service_request_rec.request_attribute_12,
p_desc_segment13 => l_service_request_rec.request_attribute_13,
p_desc_segment14 => l_service_request_rec.request_attribute_14,
p_desc_segment15 => l_service_request_rec.request_attribute_15,
p_desc_context => l_service_request_rec.request_context,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_return_status => l_return_status );
l_update_desc_flex_ext := FND_API.G_TRUE;
END IF; -- to check if any flex field segment is updated
IF ( l_update_desc_flex_ext = FND_API.G_TRUE and
p_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
Cs_ServiceRequest_Util.Validate_external_Desc_Flex
( p_api_name => l_api_name_full,
p_application_short_name => 'CS',
p_ext_desc_flex_name => 'CS_INCIDENTS_ALL_B_EXT',
p_ext_desc_segment1 => l_service_request_rec.external_attribute_1,
p_ext_desc_segment2 => l_service_request_rec.external_attribute_2,
p_ext_desc_segment3 => l_service_request_rec.external_attribute_3,
p_ext_desc_segment4 => l_service_request_rec.external_attribute_4,
p_ext_desc_segment5 => l_service_request_rec.external_attribute_5,
p_ext_desc_segment6 => l_service_request_rec.external_attribute_6,
p_ext_desc_segment7 => l_service_request_rec.external_attribute_7,
p_ext_desc_segment8 => l_service_request_rec.external_attribute_8,
p_ext_desc_segment9 => l_service_request_rec.external_attribute_9,
p_ext_desc_segment10 => l_service_request_rec.external_attribute_10,
p_ext_desc_segment11 => l_service_request_rec.external_attribute_11,
p_ext_desc_segment12 => l_service_request_rec.external_attribute_12,
p_ext_desc_segment13 => l_service_request_rec.external_attribute_13,
p_ext_desc_segment14 => l_service_request_rec.external_attribute_14,
p_ext_desc_segment15 => l_service_request_rec.external_attribute_15,
p_ext_desc_context => l_service_request_rec.external_context,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_return_status => l_return_status
);
l_service_request_rec.created_by := p_last_updated_by;
IF (l_service_request_rec.last_update_login = FND_API.G_MISS_NUM OR
l_service_request_rec.last_update_login IS NULL ) THEN
l_service_request_rec.last_update_login := p_last_update_login;
p_parameter_name_usr => 'p_last_updated_by',
p_parameter_name_login => 'p_last_update_login',
p_user_id => l_service_request_rec.last_updated_by,
p_login_id => l_service_request_rec.last_update_login,
x_return_status => l_return_status);
IF p_last_updated_by IS NOT NULL AND
p_last_updated_by <> FND_API.G_MISS_NUM THEN
CS_ServiceRequest_UTIL.Validate_Who_Info
( p_api_name => l_api_name_full,
p_parameter_name_usr => 'p_last_updated_by',
p_parameter_name_login => null,
p_user_id => p_last_updated_by,
p_login_id => null,
x_return_status => l_return_status);
, x_disallow_request_update => l_sr_related_data.old_disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.old_disallow_owner_update
, x_disallow_product_update => l_sr_related_data.old_disallow_product_update
, x_pending_approval_flag => l_sr_related_data.old_pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.old_intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.old_approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.old_rejection_action_status_id
, x_return_status => l_return_status
);
l_disallow_request_update := l_sr_related_data.old_disallow_request_update;
-- added disallow_request_update, disallow_agent_dispatch, disallow_product_update to the SQL
-- 3306908 - commented the date validation
IF (l_service_request_rec.last_updated_by = FND_API.G_MISS_NUM OR
l_service_request_rec.last_updated_by IS NULL ) THEN
l_service_request_rec.last_updated_by := p_last_updated_by;
, x_disallow_request_update => l_sr_related_data.disallow_request_update
, x_disallow_agent_dispatch => l_sr_related_data.disallow_owner_update
, x_disallow_product_update => l_sr_related_data.disallow_product_update
, x_pending_approval_flag => l_sr_related_data.pending_approval_flag
, x_intermediate_status_id => l_sr_related_data.intermediate_status_id
, x_approval_action_status_id => l_sr_related_data.approval_action_status_id
, x_rejection_action_status_id => l_sr_related_data.rejection_action_status_id
, x_return_status => l_return_status
);
SELECT autolaunch_workflow_flag, abort_workflow_close_flag, workflow
INTO x_autolaunch_wkf_flag, x_abort_wkf_close_flag, x_wkf_process_name
FROM cs_incident_types
WHERE incident_type_id = l_type_id_temp
AND incident_subtype = G_SR_SUBTYPE
;
p_user_id => p_last_updated_by);
IF (l_sr_related_data.old_disallow_request_update = 'Y') THEN
-- if disallow request update is ON and SR is in intermediate status
-- then only ERES can update service request. if updating program in
-- other than ERES then raise error and exit.
IF l_sr_related_data.old_pending_approval_flag = 'Y' AND
NVL(l_service_requesT_rec.last_update_program_code,'UNKNOWN') <> 'ERES'
THEN
FND_MESSAGE.Set_Name('CS', 'CS_SR_APPROVAL_NEEDED');
NVL(l_service_requesT_rec.last_update_program_code,'UNKNOWN') <> 'ERES'
THEN
x_service_request_rec := l_service_request_rec;
IF (l_sr_related_data.old_disallow_owner_update = 'Y') THEN
-- Tell the user he cannot update owner and store old value in the field
IF (NVL(l_service_request_rec.owner_id,-9) <> FND_API.G_MISS_NUM AND
NVL(l_service_request_rec.owner_id,-9) <> NVL(l_old_servicerequest_rec.incident_owner_id,-9)) OR
(NVL(l_service_request_rec.resource_type,'x') <> FND_API.G_MISS_CHAR AND
NVL(l_service_request_rec.resource_type,'x') <> NVL(l_old_servicerequest_rec.resource_type,'x'))
THEN
--IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_OWNER_NOT_UPDATED');
IF (l_sr_related_data.old_disallow_product_update = 'Y') THEN
-- Tell the user he cannot update product and related fields and store
-- old value in the field.Check if install_base or not
IF (l_service_request_rec.customer_product_id <> FND_API.G_MISS_NUM) THEN
l_service_request_rec.customer_product_id := l_old_ServiceRequest_rec.customer_product_id;
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_PRODUCT_NOT_UPDATED');
l_SR_Validation_rec.updated_status_id := l_service_request_rec.status_id;
l_SR_Validation_rec.validate_updated_status := FND_API.G_TRUE;
l_SR_Validation_rec.validate_updated_status := FND_API.G_TRUE;
l_SR_Validation_rec.updated_status_id := l_service_request_rec.status_id;
SELECT resource_name INTO l_service_request_rec.owner
FROM jtf_rs_resource_extns_tl
WHERE resource_id = l_service_request_rec.owner_id
AND LANGUAGE = USERENV('LANG');
IF (l_service_request_rec.last_update_channel = FND_API.G_MISS_CHAR) THEN
l_service_request_rec.last_update_channel := l_old_ServiceRequest_rec.last_update_channel;
SELECT party_id INTO l_service_request_rec.customer_id
FROM hz_parties
WHERE party_number = l_service_request_rec.customer_number;
p_user_id => p_last_updated_by,
p_operation => l_operation,
p_close_flag => l_close_flag,
p_disallow_request_update=> l_disallow_request_update,
p_disallow_owner_update => l_disallow_owner_update,
p_disallow_product_update=> l_disallow_product_update,
p_employee_name => l_employee_name,
p_inventory_item_id => l_cp_inventory_item_id,
p_contract_id => l_contra_id,
p_contract_number => l_contract_number,
x_bill_to_site_id => l_bill_to_site_id,
x_ship_to_site_id => l_ship_to_site_id,
x_bill_to_site_use_id => l_bill_to_site_use_id,
x_ship_to_site_use_id => l_ship_to_site_use_id,
x_return_status => l_return_status,
x_group_name => l_group_name,
x_owner_name => l_owner_name,
x_product_revision => l_dummy0,
x_component_version => l_dummy1,
x_subcomponent_version => l_dummy2,
--for cmro_eam
p_cmro_flag => p_cmro_flag,
p_maintenance_flag => p_maintenance_flag,
p_sr_mode => 'UPDATE'
);
validate_servicerequest_record because of the update mode */
/*
IF (l_service_request_rec.ship_to_site_id = FND_API.G_MISS_NUM) OR
(NVL(l_service_request_rec.ship_to_site_id, -99)
= NVL(l_old_ServiceRequest_rec.ship_to_site_id, -99))
THEN
IF l_ship_to_site_id IS NULL OR
l_ship_to_site_id =l_old_ServiceRequest_rec.ship_to_site_id
THEN
l_service_request_rec.ship_to_site_id :=
l_old_ServiceRequest_rec.ship_to_site_id;
IF (l_service_request_rec.last_update_date = FND_API.G_MISS_DATE OR
l_service_request_rec.last_update_date IS NULL ) THEN
l_service_request_rec.last_update_date := SYSDATE;
IF (l_service_request_rec.last_updated_by = FND_API.G_MISS_NUM OR
l_service_request_rec.last_updated_by IS NULL ) THEN
l_service_request_rec.last_updated_by := p_last_updated_by;
l_service_request_rec.created_by := p_last_updated_by;
IF (l_service_request_rec.last_update_login = FND_API.G_MISS_NUM OR
l_service_request_rec.last_update_login IS NULL ) THEN
l_service_request_rec.last_update_login := p_last_update_login;
IF (l_service_request_rec.last_update_program_code = FND_API.G_MISS_CHAR OR
l_service_request_rec.last_update_program_code IS NULL) THEN
/*Commented this code for backward compatibility, that if someone
passes a last update program code as NULL or G_MISS_CHAR, we are supporting
it now and defaulting it to UNKNOWN
CS_ServiceRequest_UTIL.Add_Null_Parameter_Msg
( p_token_an => l_api_name_full,
p_token_np => 'SR Last Update Program Code',
p_table_name => G_TABLE_NAME ,
p_column_name => 'LAST_UPDATE_PROGRAM_CODE');
l_service_request_rec.last_update_program_code:='UNKNOWN';
IF (l_service_request_rec.last_update_program_code <> FND_API.G_MISS_CHAR) AND
(l_service_request_rec.last_update_program_code IS NOT NULL) THEN
CS_ServiceRequest_UTIL.Validate_source_program_code(
p_api_name => l_api_name,
p_parameter_name => 'p_last_update_program_code',
p_source_program_code => l_service_request_rec.last_update_program_code,
x_return_status => l_return_status);
x_audit_vals_rec.OLD_LAST_UPDATE_CHANNEL := l_old_ServiceRequest_rec.LAST_UPDATE_CHANNEL;
IF (nvl(l_service_request_rec.LAST_UPDATE_CHANNEL,-99) <> FND_API.G_MISS_CHAR) AND
(nvl(l_service_request_rec.LAST_UPDATE_CHANNEL,-99) <> nvl(l_old_ServiceRequest_rec.LAST_UPDATE_CHANNEL,-99)) THEN
x_audit_vals_rec.LAST_UPDATE_CHANNEL := l_service_request_rec.LAST_UPDATE_CHANNEL;
x_audit_vals_rec.LAST_UPDATE_CHANNEL := l_old_ServiceRequest_rec.LAST_UPDATE_CHANNEL;
moved this code after call to update_sr_validation procedure.
*/
--x_audit_vals_rec.OLD_INCIDENT_RESOLVED_DATE := l_old_ServiceRequest_rec.INCIDENT_RESOLVED_DATE;
/* 12/13/05 smisra moved to update_service_request procedure just before call to
create audit record
x_audit_vals_rec.OLD_INCIDENT_LOCATION_ID := l_old_ServiceRequest_rec.INCIDENT_LOCATION_ID;
/* 12/13/05 smisra moved to update_service_request procedure just before call to
create audit record
x_audit_vals_rec.OLD_INCIDENT_COUNTRY := l_old_ServiceRequest_rec.INCIDENT_COUNTRY;
x_audit_vals_rec.OLD_LAST_UPDATE_PROGRAM_CODE := l_old_ServiceRequest_rec.LAST_UPDATE_PROGRAM_CODE;
IF (nvl(l_service_request_rec.LAST_UPDATE_PROGRAM_CODE,-99) <> FND_API.G_MISS_CHAR) AND
(nvl(l_service_request_rec.LAST_UPDATE_PROGRAM_CODE,-99) <> nvl(l_old_ServiceRequest_rec.LAST_UPDATE_PROGRAM_CODE,-99)) THEN
x_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := l_service_request_rec.LAST_UPDATE_PROGRAM_CODE;
x_audit_vals_rec.LAST_UPDATE_PROGRAM_CODE := l_old_ServiceRequest_rec.LAST_UPDATE_PROGRAM_CODE;
x_audit_vals_rec.INC_PROGRAM_UPDATE_DATE := l_service_request_rec.;
/* 12/13/05 smisra moved to update_service_request procedure just before call to
create audit record
x_audit_vals_rec.OLD_INCIDENT_LOCATION_TYPE := l_old_ServiceRequest_rec.INCIDENT_LOCATION_TYPE;
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
ROLLBACK TO Update_ServiceRequest_PVT;
END Update_SR_Validation;
p_sr_record.last_update_date := FND_API.G_MISS_DATE;
p_sr_record.last_updated_by := FND_API.G_MISS_NUM;
p_sr_record.last_update_login := FND_API.G_MISS_NUM;
p_sr_record.last_update_program_code := FND_API.G_MISS_CHAR;
p_sr_audit_record.PROGRAM_UPDATE_DATE := NULL;
p_sr_audit_record.OLD_LAST_UPDATE_CHANNEL := NULL;
p_sr_audit_record.LAST_UPDATE_CHANNEL := NULL;
p_sr_audit_record.OLD_LAST_UPDATE_PROGRAM_CODE := NULL;
p_sr_audit_record.LAST_UPDATE_PROGRAM_CODE := NULL;
p_sr_audit_record.UPDATED_ENTITY_CODE := NULL;
p_sr_audit_record.UPDATED_ENTITY_ID := NULL;
p_sr_audit_record.OLD_INC_PROGRAM_UPDATE_DATE := NULL;
p_sr_audit_record.INC_PROGRAM_UPDATE_DATE := NULL;
DELETE FROM CS_INCIDENTS_ALL_TL T
WHERE NOT EXISTS
(SELECT NULL
FROM CS_INCIDENTS_ALL_B B
WHERE B.INCIDENT_ID = T.INCIDENT_ID
);
UPDATE CS_INCIDENTS_ALL_TL T SET (
SUMMARY,
RESOLUTION_SUMMARY
--SR_CREATION_CHANNEL
) = (SELECT
B.SUMMARY,
B.RESOLUTION_SUMMARY
--B.SR_CREATION_CHANNEL
FROM CS_INCIDENTS_ALL_TL B
WHERE B.INCIDENT_ID = T.INCIDENT_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.INCIDENT_ID,
T.LANGUAGE
) IN (SELECT
SUBT.INCIDENT_ID,
SUBT.LANGUAGE
FROM CS_INCIDENTS_ALL_TL SUBB, CS_INCIDENTS_ALL_TL SUBT
WHERE SUBB.INCIDENT_ID = SUBT.INCIDENT_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.SUMMARY <> SUBT.SUMMARY
OR SUBB.RESOLUTION_SUMMARY <> SUBT.RESOLUTION_SUMMARY
OR (SUBB.RESOLUTION_SUMMARY IS NULL AND SUBT.RESOLUTION_SUMMARY IS NOT NULL)
OR (SUBB.RESOLUTION_SUMMARY IS NOT NULL AND SUBT.RESOLUTION_SUMMARY IS NULL)
--or SUBB.SR_CREATION_CHANNEL <> SUBT.SR_CREATION_CHANNEL
--or (SUBB.SR_CREATION_CHANNEL is null and SUBT.SR_CREATION_CHANNEL is not null)
--or (SUBB.SR_CREATION_CHANNEL is not null and SUBT.SR_CREATION_CHANNEL is null)
));
INSERT INTO CS_INCIDENTS_ALL_TL (
INCIDENT_ID,
SUMMARY,
RESOLUTION_SUMMARY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
--SR_CREATION_CHANNEL,
LANGUAGE,
SOURCE_LANG
) SELECT
B.INCIDENT_ID,
B.SUMMARY,
B.RESOLUTION_SUMMARY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
--B.SR_CREATION_CHANNEL,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM CS_INCIDENTS_ALL_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(SELECT NULL
FROM CS_INCIDENTS_ALL_TL T
WHERE T.INCIDENT_ID = B.INCIDENT_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM CS_INCIDENTS_AUDIT_TL T
WHERE NOT EXISTS
(SELECT NULL
FROM CS_INCIDENTS_AUDIT_B B
WHERE B.INCIDENT_AUDIT_ID = T.INCIDENT_AUDIT_ID
);
UPDATE CS_INCIDENTS_AUDIT_TL T SET (
CHANGE_DESCRIPTION
) = (SELECT
B.CHANGE_DESCRIPTION
FROM CS_INCIDENTS_AUDIT_TL B
WHERE B.INCIDENT_AUDIT_ID = T.INCIDENT_AUDIT_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.INCIDENT_AUDIT_ID,
T.LANGUAGE
) IN (SELECT
SUBT.INCIDENT_AUDIT_ID,
SUBT.LANGUAGE
FROM CS_INCIDENTS_AUDIT_TL SUBB, CS_INCIDENTS_AUDIT_TL SUBT
WHERE SUBB.INCIDENT_AUDIT_ID = SUBT.INCIDENT_AUDIT_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.CHANGE_DESCRIPTION <> SUBT.CHANGE_DESCRIPTION
OR (SUBB.CHANGE_DESCRIPTION IS NULL AND SUBT.CHANGE_DESCRIPTION IS NOT NULL)
OR (SUBB.CHANGE_DESCRIPTION IS NOT NULL AND SUBT.CHANGE_DESCRIPTION IS NULL)
));
INSERT INTO CS_INCIDENTS_AUDIT_TL (
INCIDENT_AUDIT_ID,
INCIDENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CHANGE_DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) SELECT
B.INCIDENT_AUDIT_ID,
B.INCIDENT_ID,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.CHANGE_DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM CS_INCIDENTS_AUDIT_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(SELECT NULL
FROM CS_INCIDENTS_AUDIT_TL T
WHERE T.INCIDENT_AUDIT_ID = B.INCIDENT_AUDIT_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
SELECT OBJECT_VERSION_NUMBER
FROM CS_INCIDENTS_ALL_B
WHERE INCIDENT_ID = X_INCIDENT_ID
FOR UPDATE OF INCIDENT_ID NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
SELECT importance_level
FROM cs_incident_severities_b
WHERE incident_severity_id = p_severity_id;
SELECT importance_level
FROM cs_incident_severities_b
WHERE incident_severity_id =( SELECT incident_severity_id
FROM CS_INCIDENTS_ALL_B
WHERE incident_id = p_incident_id);
SELECT incident_owner_id
FROM cs_incidents_all_b
WHERE incident_id = p_incident_id;
SELECT description
FROM jtf_objects_vl
WHERE object_code=P_Object_Code;
SELECT message_text
FROM fnd_new_messages
WHERE application_id = 170
AND message_name = p_message_code
AND language_code = USERENV('LANG');
inserting into cs_incidents_all_b table. If the closed flag is Y then
the status is closed, else its open.created by shijain dated nov 27th 2002*/
FUNCTION GET_STATUS_FLAG( p_incident_status_id IN NUMBER)
RETURN VARCHAR2 IS
CURSOR get_close_flag IS
SELECT close_flag
FROM cs_incident_statuses_b
WHERE incident_status_id = p_incident_status_id;
inserting into cs_incidents_all_b table based on the incident_id and the
primary flag from the cs_hz_sr_contact_points table by shijain
FUNCTION GET_PRIMARY_CONTACT( p_incident_id IN NUMBER)
RETURN NUMBER IS
CURSOR get_primary_contact IS
SELECT sr_contact_point_id
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id
AND primary_flag = 'Y';
select 1
from fnd_descr_flex_column_usages a,
fnd_descr_flex_contexts b
where a.descriptive_flexfield_name = p_ff_name
and b.descriptive_flexfield_name = p_ff_name
and a.application_column_name = p_attr_col
and a.descriptive_flex_context_code = b.descriptive_flex_context_code
AND a.application_id = p_app_id
AND b.application_id = p_app_id
and B.global_flag = 'Y';
* cs_sr_delete_util.delete_servicerequest
* @param p_purge_set_id Id that helps identify a set of SRs that were purged
* in a single batch
* @param p_processing_set_id Id that helps the API in identifying the set of
* SRs for which the child
* objects have to be deleted.
* @param p_purge_source_with_open_task Indicates whether the SRs containing
* OPEN non field service
* tasks should be purged or not
* @param p_audit_required Indicates if audit information has to be generated
* after purging the
* service requests
* @rep:scope Internal
* @rep:product CS
* @rep:displayname Delete Service Requests
*/
PROCEDURE Delete_ServiceRequest
(
p_api_version_number IN NUMBER := 1.0
, p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_processing_set_id IN NUMBER
, p_purge_set_id IN NUMBER
, p_purge_source_with_open_task IN VARCHAR2
, p_audit_required IN VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
--------------------------------------------------------------------------------
L_API_VERSION CONSTANT NUMBER := 1.0;
L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SERVICEREQUEST';
, 'calling the SR delete validation API '
|| 'cs_sr_delete_util.delete_sr_validations'
);
CS_SR_DELETE_UTIL.Delete_Sr_Validations
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_object_type => 'SR'
, p_processing_set_id => p_processing_set_id
, p_purge_source_with_open_task => p_purge_source_with_open_task
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, 'returned from the SR delete validation API with status '
|| x_return_status
);
, 'calling the SR delete helper API '
|| 'cs_sr_delete_util.delete_servicerequest'
);
CS_SR_DELETE_UTIL.Delete_ServiceRequest
(
p_api_version_number => '1.0'
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_processing_set_id => p_processing_set_id
, p_object_type => 'SR'
, p_purge_set_id => p_purge_set_id
, p_audit_required => p_audit_required
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
, 'returned from the SR delete helper API with status ' || x_return_status
);
END Delete_ServiceRequest;
, 'last_update_date :' || p_service_request_rec.last_update_date
);
, 'last_updated_by :' || p_service_request_rec.last_updated_by
);
, 'last_update_login :' || p_service_request_rec.last_update_login
);
, 'last_update_channel :' || p_service_request_rec.last_update_channel
);
, 'last_update_program_code :' || p_service_request_rec.last_update_program_code
);