[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);
*iii.If the profile Service: Default Operating Unit is set to an operating unit not available in the operating unit LOV at the service request header, leave the field blank so that user can select one from the LOV.
*If the profile Service: Restrict Operating Unit by MOAC is set to No, then check the value in profile Service: Default Operating Unit.
*/
--l_org_id := to_number(FND_PROFILE.value('CS_SR_ORG_ID'));
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
);
SELECT count(party_site_id) into l_onetime_add_cnt
FROM hz_party_sites
WHERE party_site_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
SELECT count(location_id) into l_onetime_add_cnt
FROM hz_locations
WHERE location_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
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,
-- Credit Card 9358401
instrument_payment_use_id,
--siahmed 12.2+ project where we are integrating oracle projects with SR
project_id,
project_task_id,
expenditure_org_id,
-- 14004721 . Additional SLA dates . shramana
sla_date_1,
sla_date_2,
sla_date_3,
sla_date_4,
sla_date_5,
sla_date_6,
sla_duration_1,
sla_duration_2,
--15995804. Add price_list_header_id
price_list_header_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,
--Credit Card 9358401
l_service_request_rec.instrument_payment_use_id,
--siahmed adding 12.2+ project_id and task_id to integrate with oracle projects
l_service_request_rec.project_id,
l_service_request_rec.project_task_id,
l_service_request_rec.expenditure_org_id,
--14004721 . Additional SLA dates
l_service_request_rec.sla_date_1,
l_service_request_rec.sla_date_2,
l_service_request_rec.sla_date_3,
l_service_request_rec.sla_date_4,
l_service_request_rec.sla_date_5,
l_service_request_rec.sla_date_6,
l_service_request_rec.sla_duration_1,
l_service_request_rec.sla_duration_2,
--15995804
l_service_request_rec.price_list_header_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',
--Add p_auto_generate_tasks for 12.2-Auto Task creation
p_auto_generate_tasks 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);
SELECT count(party_site_id) into G_ONETIME_ADD_CNT
FROM hz_party_sites
WHERE party_site_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
SELECT count(location_id) into G_ONETIME_ADD_CNT
FROM hz_locations
WHERE location_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
SELECT count(party_site_id) into G_ONETIME_ADD_CNT
FROM hz_party_sites
WHERE party_site_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
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';
IF(l_service_request_rec.last_update_program_code <> 'CSXSRTAB') THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
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_validate_sr_closure => l_validate_sr_closure, -- added for bug 11699979
p_auto_close_child_entities => l_auto_close_child_entities, -- added for bug 11699979
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;
SELECT count(party_site_id) into l_onetime_add_cnt
FROM hz_party_sites
WHERE party_site_id = l_service_request_rec.incident_location_id
AND created_by_module = 'SR_ONETIME';
x_sr_update_out_rec.incident_location_id := l_old_servicerequest_rec.incident_location_id;
x_sr_update_out_rec.incident_location_id := l_incident_location_id;
UPDATE_ONETIME_ADDRESS (
p_service_req_rec => l_service_request_rec,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status_onetime
);
FND_MESSAGE.Set_Name('CS', 'CS_SR_ONETIME_UPDATE_ERROR');
FND_MESSAGE.Set_Token('API_NAME', l_api_name||'UPDATE_ONETIME_ADDRESS');
--set the status to success as this scenario should go through update
l_project_info_status := FND_API.G_RET_STS_SUCCESS;
FND_MESSAGE.Set_Token('API_NAME', l_api_name||'UPDATE SERVICE_REQUEST');
--ROLLBACK TO Update_ServiceRequest_PVT;
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,
/* Credit Card 9358401 */
instrument_payment_use_id = l_service_request_rec.instrument_payment_use_id,
--siahmed 12.2+ project where we are integrating Oracle Projects with SR
project_id = l_service_request_rec.project_id,
project_task_id = l_service_request_rec.project_task_id,
expenditure_org_id = l_service_request_rec.expenditure_org_id,
--end of addition siahmed
--14004721 . Additional SLA dates. shramana
sla_date_1 = l_service_request_rec.sla_date_1,
sla_date_2 = l_service_request_rec.sla_date_2,
sla_date_3 = l_service_request_rec.sla_date_3,
sla_date_4 = l_service_request_rec.sla_date_4,
sla_date_5 = l_service_request_rec.sla_date_5,
sla_date_6 = l_service_request_rec.sla_date_6,
sla_duration_1 = l_service_request_rec.sla_duration_1,
sla_duration_2 = l_service_request_rec.sla_duration_2,
--15995804. Add price_list_header_id
price_list_header_id = l_service_request_rec.price_list_header_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
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
-- ------------------------------------------------------------------
-- Changes starts for 12.2 Auto Task Creation, in case the 'To Status'
-- doesn't have 'Disallow Request Update Checked'. - lkullamb
-- ------------------------------------------------------------------
IF NVL(l_sr_related_data.disallow_request_update,'N') <> 'Y' THEN
IF p_auto_generate_tasks = 'Y' OR p_auto_generate_tasks = 'y' THEN
--Fix for bug 12668350
--Check if status_group_id is defined in 'CS_SR_TYPE_MAPPING'
--table first If it is not defined,then look in cs_incident_types
BEGIN
SELECT task_template_id into l_task_template_id
FROM cs_sr_status_transitions cst,cs_sr_type_mapping srtype
WHERE srtype.incident_type_id = l_service_request_rec.type_id
AND srtype.responsibility_id = p_resp_id
AND trunc(sysdate) BETWEEN
trunc(nvl(srtype.start_date, sysdate)) AND
trunc(nvl(srtype.end_date, sysdate))
AND cst.status_group_id=srtype.status_group_id
AND cst.from_incident_status_id = l_old_servicerequest_rec.incident_status_id --refers to old status_id
AND cst.to_incident_status_id = l_service_request_rec.status_id; -- refers to new status_id
select task_template_id into l_task_template_id
FROM cs_sr_status_transitions cst,cs_incident_types_b cit
WHERE cit.incident_type_id = l_service_request_rec.type_id
AND cst.status_group_id=cit.status_group_id
AND from_incident_status_id = l_old_servicerequest_rec.incident_status_id --refers to old status_id
AND to_incident_status_id = l_service_request_rec.status_id; -- refers to new status_id
END IF; -- end of l_new_disallow_request_update <> 'Y'
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
);
cs_servicerequest_iuhk.Update_ServiceRequest_Post( 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
, p_old_location_id => l_old_servicerequest_rec.incident_location_id -- bug 8947959
, 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',
--Add p_auto_generate_tasks for 12.2-Auto Task creation
p_auto_generate_tasks 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);
l_new_disallow_request_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 disallow_request_update
INTO l_new_disallow_request_update
FROM cs_incident_statuses_b
WHERE incident_status_id = p_status_id
AND incident_subtype = G_SR_SUBTYPE;
IF NVL(l_new_disallow_request_update,'N') <> 'Y' THEN
IF p_auto_generate_tasks = 'Y' OR p_auto_generate_tasks = 'y' THEN
--Fix for bug 12668350
--Check if status_group_id is defined in 'CS_SR_TYPE_MAPPING'
--table first If it is not defined,then look in cs_incident_types
BEGIN
SELECT task_template_id into l_task_template_id
FROM cs_sr_status_transitions cst,cs_sr_type_mapping srtype
WHERE srtype.incident_type_id = l_servicerequest_rec.incident_type_id
AND srtype.responsibility_id = p_resp_id
AND trunc(sysdate) BETWEEN
trunc(nvl(srtype.start_date, sysdate)) AND
trunc(nvl(srtype.end_date, sysdate))
AND cst.status_group_id=srtype.status_group_id
AND cst.from_incident_status_id = l_servicerequest_rec.incident_status_id--refers to old status_id
AND cst.to_incident_status_id = p_status_id; -- refers to new status_id
select task_template_id into l_task_template_id
FROM cs_sr_status_transitions cst,cs_incident_types_b cit
WHERE cit.incident_type_id = l_servicerequest_rec.incident_type_id
AND cst.status_group_id=cit.status_group_id
AND from_incident_status_id = l_servicerequest_rec.incident_status_id --refers to old status_id
AND to_incident_status_id = p_status_id; -- refers to new status_id
END IF; -- end of l_new_disallow_request_update <> 'Y'
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 ,
--siahmed 12.2+ project
old_project_id ,
project_id ,
old_project_task_id ,
project_task_id ,
old_expenditure_org_id ,
expenditure_org_id ,
--end of addition siahmed 12.2+
--15995804. Auditing price_list_header_id and SLA dates
OLD_PRICE_LIST_HEADER_ID ,
PRICE_LIST_HEADER_ID ,
CHANGE_SLA_DATE_1_FLAG ,
OLD_SLA_DATE_1 ,
SLA_DATE_1 ,
CHANGE_SLA_DATE_2_FLAG ,
OLD_SLA_DATE_2 ,
SLA_DATE_2 ,
CHANGE_SLA_DATE_3_FLAG ,
OLD_SLA_DATE_3 ,
SLA_DATE_3 ,
CHANGE_SLA_DATE_4_FLAG ,
OLD_SLA_DATE_4 ,
SLA_DATE_4 ,
CHANGE_SLA_DATE_5_FLAG ,
OLD_SLA_DATE_5 ,
SLA_DATE_5 ,
CHANGE_SLA_DATE_6_FLAG ,
OLD_SLA_DATE_6 ,
SLA_DATE_6 ,
CHANGE_SLA_DURATION_1_FLAG ,
OLD_SLA_DURATION_1 ,
SLA_DURATION_1 ,
CHANGE_SLA_DURATION_2_FLAG ,
OLD_SLA_DURATION_2 ,
SLA_DURATION_2
)
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 ,
--added by siahmed 12.2+ project auditing project attributes
l_audit_vals_rec.old_project_id ,
l_audit_vals_rec.project_id ,
l_audit_vals_rec.old_project_task_id ,
l_audit_vals_rec.project_task_id ,
l_audit_vals_rec.old_expenditure_org_id ,
l_audit_vals_rec.expenditure_org_id ,
--end of addition siahmed 12.2+ project
--15995804. Auditing price_list_header_id and SLA dates
l_audit_vals_rec.OLD_PRICE_LIST_HEADER_ID ,
l_audit_vals_rec.PRICE_LIST_HEADER_ID ,
l_audit_vals_rec.CHANGE_SLA_DATE_1_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_1 ,
l_audit_vals_rec.SLA_DATE_1 ,
l_audit_vals_rec.CHANGE_SLA_DATE_2_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_2 ,
l_audit_vals_rec.SLA_DATE_2 ,
l_audit_vals_rec.CHANGE_SLA_DATE_3_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_3 ,
l_audit_vals_rec.SLA_DATE_3 ,
l_audit_vals_rec.CHANGE_SLA_DATE_4_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_4 ,
l_audit_vals_rec.SLA_DATE_4 ,
l_audit_vals_rec.CHANGE_SLA_DATE_5_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_5 ,
l_audit_vals_rec.SLA_DATE_5 ,
l_audit_vals_rec.CHANGE_SLA_DATE_6_FLAG ,
l_audit_vals_rec.OLD_SLA_DATE_6 ,
l_audit_vals_rec.SLA_DATE_6 ,
l_audit_vals_rec.CHANGE_SLA_DURATION_1_FLAG ,
l_audit_vals_rec.OLD_SLA_DURATION_1 ,
l_audit_vals_rec.SLA_DURATION_1 ,
l_audit_vals_rec.CHANGE_SLA_DURATION_2_FLAG ,
l_audit_vals_rec.OLD_SLA_DURATION_2 ,
l_audit_vals_rec.SLA_DURATION_2
)
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,
B.instrument_payment_use_id,
--14004721 . Additional SLA dates
B.sla_date_1,
B.sla_date_2,
B.sla_date_3,
B.sla_date_4,
B.sla_date_5,
B.sla_date_6,
B.sla_duration_1,
B.sla_duration_2,
--15995804. Add price_list_header_id
B.price_list_header_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
--Changes starts for bug 14242503. For disallow product update flag, check if any prodouct related info is changed, if so, throw a warning,
--else don't throw the warning
IF (l_service_request_rec.customer_product_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.customer_product_id,-99) = NVL(l_old_ServiceRequest_rec.customer_product_id,-99))
AND (l_service_request_rec.cp_component_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.cp_component_id,-99) = NVL(l_old_servicerequest_rec.cp_component_id,-99))
AND (l_service_request_rec.cp_component_version_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.cp_component_version_id,-99) = NVL(l_old_servicerequest_rec.cp_component_version_id,-99))
AND (l_service_request_rec.cp_subcomponent_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.cp_subcomponent_id,-99) = NVL(l_old_servicerequest_rec.cp_subcomponent_id,-99))
AND (l_service_request_rec.cp_subcomponent_version_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.cp_subcomponent_version_id,-99) = NVL(l_old_servicerequest_rec.cp_subcomponent_version_id,99))
AND (l_service_request_rec.cp_revision_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.cp_revision_id ,-99) = NVL(l_old_servicerequest_rec.cp_revision_id ,-99))
AND (l_service_request_rec.product_revision = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.product_revision,'-99')= NVL(l_old_servicerequest_rec.product_revision,'-99'))
AND (l_service_request_rec.component_version = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.component_version,'-99') = NVL(l_old_servicerequest_rec.component_version,'-99'))
AND (l_service_request_rec.subcomponent_version = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.subcomponent_version,'-99') = NVL(l_old_servicerequest_rec.subcomponent_version,'-99'))
AND (l_service_request_rec.original_order_number = FND_API.G_MISS_NUM OR
NVL( l_service_request_rec.original_order_number,-99) = NVL(l_old_ServiceRequest_rec.original_order_number,-99))
AND (l_service_request_rec.purchase_order_num = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.purchase_order_num,'99') = NVL(l_old_ServiceRequest_rec.purchase_order_num,'-99'))
AND (l_service_request_rec.inv_item_revision = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.inv_item_revision,'-99') = NVL(l_old_ServiceRequest_rec.inv_item_revision,'-99'))
AND (l_service_request_rec.inv_component_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.inv_component_id,-99)= NVL(l_old_ServiceRequest_rec.inv_component_id,-99))
AND (l_service_request_rec.inv_component_version = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.inv_component_version,'-99')= NVL(l_old_ServiceRequest_rec.inv_component_version,'-99'))
AND (l_service_request_rec.inv_subcomponent_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.inv_subcomponent_id,-99) = NVL(l_old_ServiceRequest_rec.inv_subcomponent_id,-99))
AND (l_service_request_rec.inv_subcomponent_version = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.inv_subcomponent_version,'-99') = NVL(l_old_ServiceRequest_rec.inv_subcomponent_version,'-99'))
AND (l_service_request_rec.inventory_item_id = FND_API.G_MISS_NUM OR
NVL(l_service_request_rec.inventory_item_id,-99) = NVL(l_old_ServiceRequest_rec.inventory_item_id,-99))
AND (l_service_request_rec.current_serial_number = FND_API.G_MISS_CHAR OR
NVL(l_service_request_rec.current_serial_number,'-99') = NVL(l_old_ServiceRequest_rec.current_serial_number,'-99')) THEN
l_cust_product_changed := 'N';
FND_MESSAGE.Set_Name('CS', 'CS_API_SR_PRODUCT_NOT_UPDATED');
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
);
PROCEDURE update_onetime_address
( p_service_req_rec IN service_request_rec_type,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
Is
l_service_req_rec service_request_rec_type DEFAULT p_service_req_rec;
SELECT a.location_id,
a.address1,
a.address2,
a.address3,
a.address4,
a.city,
a.state,
a.postal_code,
a.county,
a.province,
a.country,
a.postal_plus4_code ,
a.address_lines_phonetic,
b.party_site_number,
b.party_site_name,
b.addressee,
a.object_version_number,
b.object_version_number
INTO
l_loc_id,
l_address1,
l_address2,
l_address3,
l_address4,
l_city,
l_state,
l_postal_code,
l_county,
l_province,
l_country,
l_postal_plus4_code,
l_address_lines_phonetic,
l_party_site_number,
l_party_site_name,
l_addressee,
l_loc_object_version_number,
l_ps_object_version_number
from hz_locations a,
hz_party_sites b
where b.party_site_id = l_service_req_rec.incident_location_id
and b.location_id = a.location_id
and b.created_by_module = a.created_by_module;
SAVEPOINT UPDATE_PARTY_SITE;
HZ_PARTY_SITE_V2PUB.UPDATE_PARTY_SITE(
p_init_msg_list => l_init_msg_list,
p_party_site_rec => l_party_site_rec,
p_object_version_number => l_ps_object_version_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
ROLLBACK to UPDATE_PARTY_SITE;
END IF; --end of update party site
SAVEPOINT UPDATE_LOCATION;
HZ_LOCATION_V2PUB.update_location (
p_init_msg_list => FND_API.G_FALSE,
p_location_rec => l_location_rec,
p_do_addr_val => l_do_addr_val,
p_object_version_number => l_loc_object_version_number,
x_addr_val_status => l_addr_val_status,
x_addr_warn_msg => l_addr_warn_msg,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
ROLLBACK to UPDATE_LOCATION;
END update_onetime_address;