[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sr_record.last_update_channel := FND_API.G_MISS_CHAR;
p_sr_record.last_update_program_code := FND_API.G_MISS_CHAR; -- ER source
select maintenance_flag into l_maintenance_flag
from cs_incident_types_b where incident_type_id = p_request_rec.type_id
and incident_subtype=G_SR_SUBTYPE;
* Dynamic Inserts Possible : No *
* Unique ID Column : INVENTORY_ITEM_ID *
* Structure Column : ORGANIZATION_ID *
* The System Items Flexfield supports only one structure *
* (default value is 101). AOL stores the set number in the *
* structure defining column instead of the structure number, *
* that's why the inventory org ID must be passed. *
**************************************************************/
IF ((l_service_request_rec.inventory_item_segment1 <> FND_API.G_MISS_CHAR) OR
(l_service_request_rec.inventory_item_segment1 IS NULL)) THEN
i := i + 1;
l_request_rec.last_update_channel := l_service_request_rec.last_update_channel;
PROCEDURE Update_ServiceRequest
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_audit_comments IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER :=NULL,
p_last_update_date IN DATE,
p_service_request_rec IN service_request_rec_type,
p_notes IN notes_table,
p_contacts IN contacts_table,
p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
p_workflow_process_id IN NUMBER := 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_PUB;
CS_ServiceRequest_PUB.Update_ServiceRequest
( p_api_version => 4.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
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_request_number => p_request_number,
p_audit_comments => p_audit_comments,
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_notes => p_notes,
p_contacts => p_contacts,
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_PUB;
ROLLBACK TO Update_ServiceRequest_PUB;
ROLLBACK TO Update_ServiceRequest_PUB;
END Update_ServiceRequest;
PROCEDURE Update_ServiceRequest
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_audit_comments IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER :=NULL,
p_last_update_date IN DATE,
p_service_request_rec IN service_request_rec_type,
p_notes IN notes_table,
p_contacts IN contacts_table,
p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
p_workflow_process_id IN NUMBER := 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/08/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
-- changed the version from 3.0 to 4.0 anmukher aug 08 2003
l_api_version CONSTANT NUMBER := 4.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_ServiceRequest';
l_user_id NUMBER := p_last_updated_by;
l_login_id NUMBER := p_last_update_login;
l_update_desc_flex VARCHAR2(1) := FND_API.G_FALSE;
l_sr_update_out_rec CS_ServiceRequest_PVT.sr_update_out_rec_type;
SAVEPOINT Update_ServiceRequest_PUB;
, '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_PUB.update_servicerequest'
);
Check to see if a value is passed for Caller_Type as it is not updateable - for BUG 2754987 .
*/
IF (l_service_request_rec.caller_type <> FND_API.G_MISS_CHAR) THEN
CS_ServiceRequest_UTIL.Add_Param_Ignored_Msg(
p_token_an => l_api_name_full,
p_token_ip => 'p_caller_type' );
l_update_desc_flex := FND_API.G_TRUE;
l_update_desc_flex := FND_API.G_TRUE;
l_request_rec.last_update_channel := l_service_request_rec.last_update_channel;
l_request_rec.last_update_program_code := l_service_request_rec.last_update_program_code;
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 => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_request_id => l_request_id,
p_object_version_number => p_object_version_number,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => l_resp_id,
p_last_updated_by => l_user_id,
p_last_update_login => l_login_id,
p_last_update_date => p_last_update_date,
p_service_request_rec => l_request_rec,
p_invocation_mode => l_invocation_mode,
p_update_desc_flex => l_update_desc_flex,
p_notes => l_notes,
p_contacts => l_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
-- Added for 11.5.10
p_auto_assign => p_auto_assign,
p_validate_sr_closure => p_validate_sr_closure,
p_auto_close_child_entities => p_auto_close_child_entities,
p_default_contract_sla_ind => p_default_contract_sla_ind,
--Add p_auto_generate_tasks for 12.2-Auto Task creation
p_auto_generate_tasks => p_auto_generate_tasks,
x_sr_update_out_rec => l_sr_update_out_rec
);
x_sr_update_out_rec.interaction_id := l_sr_update_out_rec.interaction_id;
x_sr_update_out_rec.workflow_process_id := l_sr_update_out_rec.workflow_process_id;
x_sr_update_out_rec.individual_owner := l_sr_update_out_rec.individual_owner;
x_sr_update_out_rec.group_owner := l_sr_update_out_rec.group_owner;
x_sr_update_out_rec.individual_type := l_sr_update_out_rec.individual_type;
x_sr_update_out_rec.resolved_on_date := l_sr_update_out_rec.resolved_on_date;
x_sr_update_out_rec.responded_on_date := l_sr_update_out_rec.responded_on_date;
x_sr_update_out_rec.status_id := l_sr_update_out_rec.status_id;
x_sr_update_out_rec.close_date := l_sr_update_out_rec.close_date;
ROLLBACK TO Update_ServiceRequest_PUB;
ROLLBACK TO Update_ServiceRequest_PUB;
ROLLBACK TO Update_ServiceRequest_PUB;
END Update_ServiceRequest;
PROCEDURE Update_Status
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_user_id IN NUMBER := NULL,
p_login_id IN NUMBER := FND_API.G_MISS_NUM,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_status_id IN NUMBER := NULL,
p_status IN VARCHAR2 := NULL,
p_closed_date IN DATE := FND_API.G_MISS_DATE,
p_audit_comments IN VARCHAR2 := NULL,
p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
p_workflow_process_id IN NUMBER := NULL,
p_comments IN VARCHAR2 := NULL,
p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
-- for bug 3326813
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';
SAVEPOINT Update_Status_PUB;
CS_ServiceRequest_PVT.Update_Status (
p_api_version => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => 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 => l_request_id,
p_object_version_number => p_object_version_number,
p_status_id => l_status_id,
p_closed_date => p_closed_date,
p_last_updated_by => l_user_id,
p_last_update_login => l_login_id,
p_last_update_date => sysdate,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_comments => p_comments,
p_public_comment_flag => l_public_comment_flag,
x_interaction_id => x_interaction_id,
-- for bug 3326813
p_validate_sr_closure => p_validate_sr_closure,
--Add p_auto_generate_tasks for 12.2-Auto Task creation
p_auto_generate_tasks => p_auto_generate_tasks,
p_auto_close_child_entities => p_auto_close_child_entities);
ROLLBACK TO Update_Status_PUB;
ROLLBACK TO Update_Status_PUB;
ROLLBACK TO Update_Status_PUB;
END Update_Status;
PROCEDURE Update_Severity
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_user_id IN NUMBER := NULL,
p_login_id IN NUMBER := FND_API.G_MISS_NUM,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_severity_id IN NUMBER := NULL,
p_severity IN VARCHAR2 := NULL,
p_audit_comments IN VARCHAR2 := NULL,
p_comments IN VARCHAR2 := NULL,
p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
x_interaction_id OUT NOCOPY NUMBER
)
IS
BEGIN
NULL;
END Update_Severity;
PROCEDURE Update_Urgency
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_user_id IN NUMBER := NULL,
p_login_id IN NUMBER := FND_API.G_MISS_NUM,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_urgency_id IN NUMBER := FND_API.G_MISS_NUM,
p_urgency IN VARCHAR2 := FND_API.G_MISS_CHAR,
p_audit_comments IN VARCHAR2 := NULL,
p_comments IN VARCHAR2 := NULL,
p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
x_interaction_id OUT NOCOPY NUMBER
)
IS
BEGIN
NULL;
END Update_Urgency;
PROCEDURE Update_Owner
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_user_id IN NUMBER := NULL,
p_login_id IN NUMBER := FND_API.G_MISS_NUM,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_owner_id IN NUMBER,
p_owner_group_id IN NUMBER,
p_resource_type IN VARCHAR2,
p_audit_comments IN VARCHAR2 := NULL,
p_called_by_workflow IN VARCHAR2 := FND_API.G_FALSE,
p_workflow_process_id IN NUMBER := NULL,
p_comments IN VARCHAR2 := NULL,
p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
x_interaction_id OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Owner';
SAVEPOINT Update_Owner_PUB;
CS_ServiceRequest_PVT.Update_Owner (
p_api_version => 2.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
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 => l_request_id,
p_object_version_number => p_object_version_number,
p_resp_id => l_resp_id,
p_resp_appl_id => l_resp_appl_id,
p_owner_id => p_owner_id,
p_owner_group_id => p_owner_group_id,
p_resource_type => p_resource_type,
p_last_updated_by => l_user_id,
p_last_update_login => l_login_id,
p_last_update_date => sysdate,
p_audit_comments => p_audit_comments,
p_called_by_workflow => p_called_by_workflow,
p_comments => p_comments,
p_public_comment_flag => l_public_comment_flag,
x_interaction_id => x_interaction_id );
ROLLBACK TO Update_Owner_PUB;
ROLLBACK TO Update_Owner_PUB;
ROLLBACK TO Update_Owner_PUB;
END Update_Owner;
PROCEDURE Update_Problem_Code
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_resp_appl_id IN NUMBER := NULL,
p_resp_id IN NUMBER := NULL,
p_user_id IN NUMBER := NULL,
p_login_id IN NUMBER := FND_API.G_MISS_NUM,
p_request_id IN NUMBER := NULL,
p_request_number IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER,
p_problem_code IN VARCHAR2,
p_comments IN VARCHAR2 := NULL,
p_public_comment_flag IN VARCHAR2 := FND_API.G_FALSE,
x_interaction_id OUT NOCOPY NUMBER
)
IS
BEGIN
NULL;
END Update_Problem_Code;
This Procedure is called during create and update of the SR.
Create
If the Id = G_MISS_NUM
Name = Valid value -> Id for the name is saved.
Name = Invalid value -> Raise Error.
Name = NULL -> Id is set to NULL.
Name = G_MISS_CHAR -> Id is set to Default value.
If the Id = NULL
Name is ignored ,Id is set to NULL.
If the Id = Value
Name is ignored ,Id is validated.
Update
If the Id = G_MISS_NUM
Name = Valid value -> Id for the name is saved.
Name = Invalid value -> Raise Error.
Name = NULL -> Id is set to NULL.
Name = G_MISS_CHAR -> No change is made.
If the Id = NULL
Name is ignored ,Id is set to NULL.
If the Id = Value
Name is ignored ,Id is validated and Updated.
*/
-- -------------------------------------------------------------------
PROCEDURE Convert_Request_Val_To_ID
( p_api_name IN VARCHAR2,
p_org_id IN NUMBER := NULL,
p_request_conv_rec IN OUT NOCOPY Request_Conversion_Rec_Type,
p_return_status OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1);
, 'last_update_channel :' || p_service_request_rec.last_update_channel
);
, 'last_update_program_code :' || p_service_request_rec.last_update_program_code
);
(select
inc.incident_id
,inc.incident_number IncidentNumber
,inc.incident_type_id
,(select type.name
from cs_incident_types_tl type
where type.incident_type_id = inc.incident_type_id
and type.language = userenv('LANG')) IncidentType
,inc.incident_status_id IncidentStatusId
,(select status.name
from cs_incident_statuses_tl status
where status.incident_status_id = inc.incident_status_id
and status.language = userenv('LANG') )IncidentStatus
,inc.incident_severity_id IncidentSeverityId
,(select sev.name
from cs_incident_severities_tl sev
where sev.incident_severity_id = inc.incident_severity_id
and sev.language = userenv('LANG')) IncidentSeverity
,(select sev.importance_level
from cs_incident_severities_b sev
where sev.incident_severity_id = inc.incident_severity_id
) SevImportanceLevel
,inc.incident_urgency_id IncidentUrgencyId
,(select urgency.name
from cs_incident_urgencies_tl urgency
where urgency.incident_urgency_id = inc.incident_urgency_id
and urgency.language = userenv('LANG')) IncidentUrgency
,inc.owner_group_id SrGroupId
,( SELECT gr.group_name
FROM jtf_rs_groups_tl gr
WHERE gr.group_id = inc.owner_group_id
AND gr.LANGUAGE = userenv('LANG') )SrGroup
,inc.incident_owner_id SrOwnerId
,( SELECT rs.resource_name
FROM jtf_rs_resource_extns_tl rs
WHERE rs.resource_id = inc.incident_owner_id
AND language = userenv('LANG') )SrOwner
,inc.problem_code ProblemcodeId
,(SELECT problem.meaning
FROM FND_LOOKUP_VALUES problem
WHERE problem.lookup_code = inc.problem_code
AND problem.lookup_type = 'REQUEST_PROBLEM_CODE'
AND problem.LANGUAGE = userenv('LANG')
AND problem.View_APPLICATION_ID = 170
AND problem.SECURITY_GROUP_ID = fnd_global.lookup_security_group(problem.LOOKUP_TYPE, problem.VIEW_APPLICATION_ID)
) Problemcode
,inc.resolution_code ResolutioncodeId
,(SELECT resolution.meaning
FROM FND_LOOKUP_VALUES resolution
WHERE resolution.lookup_code = inc.resolution_code
AND resolution.lookup_type = 'REQUEST_RESOLUTION_CODE'
AND resolution.LANGUAGE = userenv('LANG')
AND resolution.View_APPLICATION_ID = 170
AND resolution.SECURITY_GROUP_ID = fnd_global.lookup_security_group(resolution.LOOKUP_TYPE, resolution.VIEW_APPLICATION_ID)
) Resolutioncode
,inc.publish_flag PublishFlag
,(SELECT channel.meaning
FROM FND_LOOKUP_VALUES channel
WHERE channel.lookup_code = inc.sr_creation_channel
AND channel.lookup_type = 'CS_SR_CREATION_CHANNEL'
AND channel.LANGUAGE = userenv('LANG')
AND channel.View_APPLICATION_ID = 170
AND channel.SECURITY_GROUP_ID = fnd_global.lookup_security_group(channel.LOOKUP_TYPE,channel.VIEW_APPLICATION_ID)
) SrCreationChannel
,tl.summary Summary
,tl.resolution_summary ResolutionSummary
,(select usr.user_name
from fnd_user usr
where usr.user_id = inc.created_by)CreatedBy
,sr_cont.party_id ContactPartyId
,(select cont_type_lkup.meaning
from FND_LOOKUP_VALUES cont_type_lkup
where sr_cont.contact_type=cont_type_lkup.lookup_code
and cont_type_lkup.lookup_type = 'CS_SR_CONTACT_TYPE'
AND cont_type_lkup.LANGUAGE = userenv('LANG')
AND cont_type_lkup.View_APPLICATION_ID = 170
AND cont_type_lkup.SECURITY_GROUP_ID = fnd_global.lookup_security_group(cont_type_lkup.LOOKUP_TYPE, cont_type_lkup.VIEW_APPLICATION_ID)
)ContactType
,csz_servicerequest_util_pvt.get_contact_name(sr_cont.contact_type,
sr_cont.party_id,
inc.customer_id) ContactName
,inc.time_zone_id TimeZoneId
,(select hz_time.global_timezone_name
from hz_timezones hz_time
where hz_time.timezone_id = inc.time_zone_id) TimeZoneName
,inc.customer_id CustomerId
,party.party_number CustomerNumber
,party.party_name CustomerName
,inc.account_id AccountId
,(select account.account_number
from hz_cust_accounts account
where account.cust_account_id = inc.account_id) accountnumber
,inc.inventory_item_id InventoryItemId
,(select product_b.concatenated_segments
from mtl_system_items_b_kfv product_b
where product_b.inventory_item_id = inc.inventory_item_id
and product_b.organization_id = inc.inv_organization_id) Product
,(select product_tl.description
from mtl_system_items_tl product_tl
where product_tl.inventory_item_id = inc.inventory_item_id
and product_tl.organization_id = inc.inv_organization_id
and product_tl.language = userenv('LANG')) ProductDescription
,inc.incident_date CreationDate
,inc.incident_last_modified_date LastUpdateDate
,inc.incident_occurred_date IncidentDate
,inc.close_date CloseDate
,inc.incident_resolved_date DateResolved
,inc.obligation_date ExpectedReactionDate
,inc.expected_resolution_date ExpectedResolutionDate
, inc.inc_responded_by_date DateFirstReacted
,inc.actual_resolution_date ActualResolutionDate
,(select status_b.sort_order
from cs_incident_statuses_b status_b
where status_b.incident_status_id = inc.incident_status_id
) statussortorder
,inc.status_flag StatusFlagCode
,inc.inc_responded_by_date IncRespondedByDate
,inc.created_by CreatedById
,inc.customer_product_id CustomerProductId
,inc.org_id OrganizationId
,inc.inv_organization_id InventoryOrgId
,nvl2(party.primary_phone_country_code,
party.primary_phone_country_code||'-',null)||
nvl2(party.primary_phone_area_code,
party.primary_phone_area_code||'-',null)||
party.primary_phone_number customerphone
,party.email_address CustomerEmail
,(select hdr.contract_number
from okc_k_headers_all_b hdr,
okc_k_lines_b svl
where hdr.id = svl.chr_id
and hdr.id = svl.dnz_chr_id
and hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
and svl.id = inc.contract_service_id) ContractNumber
,(select mtl.description
from okc_k_lines_b svl,
okc_k_items cim,
mtl_system_items_b mtl
where svl.id = cim.cle_id
and cim.object1_id1 = mtl.inventory_item_id
and cim.object1_id2 = mtl.organization_id
and svl.id = inc.contract_service_id) ContractServiceDescription
,(select covt.item_description
from okc_k_lines_b svl,
okc_k_lines_tl covt,
okc_k_lines_b covb,
oks_k_lines_b okssrv
where okssrv.cle_id = svl.id
and covb.id = okssrv.coverage_id
and covb.lse_id in (2,15,20)
and covt.language = userenv('LANG')
and covb.id = covt.id
and covt.language = userenv('LANG')
and svl.id = inc.contract_service_id) ContractCoverageDescription
,( SELECT cust_type_lkup.meaning
FROM FND_LOOKUP_VALUES cust_type_lkup
WHERE party.party_type = cust_type_lkup.lookup_code
AND cust_type_lkup.lookup_type='CS_SR_CALLER_TYPE'
and cust_type_lkup.LANGUAGE = userenv('LANG')
and cust_type_lkup.View_APPLICATION_ID = 170
and cust_type_lkup.SECURITY_GROUP_ID = fnd_global.lookup_security_group(cust_type_lkup.LOOKUP_TYPE,cust_type_lkup.VIEW_APPLICATION_ID)
) CustomerType
,decode(sr_cont.contact_type,'EMPLOYEE', (select phone_number
from per_phones per
where per.phone_id = sr_cont.contact_point_id)
, (nvl2(hzcp.phone_country_code,
'+'||hzcp.phone_country_code||' ',null)|| nvl2(hzcp.phone_area_code,'(' ||hzcp.phone_area_code ||')',null)||
hzcp.phone_number ||' ' || nvl2(hzcp.phone_extension, 'x'||hzcp.phone_extension ,null) )
) ContactPhoneNumber
,(SELECT CASE when sr_cont.contact_type='EMPLOYEE' then
(select email_address from per_all_people_f per, PER_ALL_ASSIGNMENTS_F Asmt
where per.person_id =sr_cont.party_id
AND Asmt.PERSON_ID = Per.PERSON_ID
AND Asmt.PRIMARY_FLAG = 'Y'
AND Asmt.ASSIGNMENT_TYPE in ( 'E', 'C')
AND Per.EFFECTIVE_START_DATE = (SELECT MAX(q.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F q
WHERE q.person_id = per.person_id)
AND Asmt.EFFECTIVE_START_DATE= (SELECT MAX(b.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F b
WHERE b.person_id = Asmt.person_id
AND b.assignment_id = Asmt.assignment_id) and rownum=1)
ELSE hzcp.email_address END AS ContactEmail from dual) ContactEmail
,(SELECT cat.concatenated_segments
FROM mtl_categories_b_kfv cat
WHERE cat.category_id = inc.category_id )ItemCategory
,( select csi.instance_number
from csi_item_instances csi
where csi.instance_id = inc.customer_product_id ) InstanceNumber
, nvl((select csi.serial_number
from csi_item_instances csi
where csi.instance_id = inc.customer_product_id ), inc.current_serial_number ) SerialNumber
,nvl( (select csi.external_reference
from csi_item_instances csi
where csi.instance_id = inc.customer_product_id ), inc.external_reference ) TagNumber
,( SELECT sys.name
FROM CSI_SYSTEMS_TL sys
WHERE sys.system_id = inc.system_id
and sys.LANGUAGE = USERENV('LANG') ) SystemNumber
,(CASE WHEN inc.cp_component_id is not null THEN
(select product_a.concatenated_segments
from mtl_system_items_b_kfv product_a,
csi_item_instances instance
where product_a.inventory_item_id = instance.inventory_item_id
and product_a.organization_id = inc.inv_organization_id
and inc.cp_component_id=instance.instance_id )
ELSE
(select product_a.concatenated_segments
from mtl_system_items_b_kfv product_a
where product_a.inventory_item_id = inc.inv_component_id
and product_a.organization_id = inc.inv_organization_id )
END ) Component
,(CASE WHEN inc.cp_subcomponent_id is not null THEN
(select product_b.concatenated_segments
from mtl_system_items_b_kfv product_b,
csi_item_instances instance
where product_b.inventory_item_id = instance.inventory_item_id
and product_b.organization_id = inc.inv_organization_id
and inc.cp_subcomponent_id = instance.instance_id )
ELSE
(select product_b.concatenated_segments
from mtl_system_items_b_kfv product_b
where product_b.inventory_item_id = inc.inv_subcomponent_id
and product_b.organization_id = inc.inv_organization_id )
END ) SubComponent
, nvl2(inc.customer_product_id, instance.inventory_revision,
inc.inv_item_revision ) ItemRevision
, nvl2(inc.customer_product_id, (select instance.inventory_revision
from mtl_system_items_b_kfv product_a,
csi_item_instances instance
where product_a.inventory_item_id = instance.inventory_item_id
and product_a.organization_id = inc.inv_organization_id
and inc.cp_component_id=instance.instance_id)
, inc.inv_component_version ) ComponentRevision
, nvl2(inc.customer_product_id, (select instance.inventory_revision
from mtl_system_items_b_kfv product_b,
csi_item_instances instance
where product_b.inventory_item_id = instance.inventory_item_id
and product_b.organization_id = inc.inv_organization_id
and inc.cp_subcomponent_id = instance.instance_id )
, inc.inv_subcomponent_version ) SubComponentRevision
,(CASE WHEN inc.incident_location_id is not null and
inc.incident_location_type ='HZ_LOCATIONS' THEN
(select loc.address1 || nvl2(loc.address2,', '||loc.address2,null) ||
nvl2(loc.address3,', '||loc.address3,null) ||
nvl2(loc.address4,', '||loc.address4,null) ||
nvl2(loc.city, ','||loc.city,null) ||
nvl2(loc.state, ','||loc.state,null) ||
nvl2(loc.province, ','||loc.province,null) ||
nvl2(loc.postal_code, ' '||loc.postal_code,null) ||
nvl2(loc.country, ' '||loc.country,null)
from hz_locations loc
where loc.location_id = inc.incident_location_id )
WHEN inc.incident_location_id is not null and inc.incident_location_type
='HZ_PARTY_SITE' THEN
(select loc.address1 || nvl2(loc.address2,', '||loc.address2,null) ||
nvl2(loc.address3,', '||loc.address3,null) ||
nvl2(loc.address4,', '||loc.address4,null) ||
nvl2(loc.city, ','||loc.city,null) ||
nvl2(loc.state, ','||loc.state,null) ||
nvl2(loc.province, ','||loc.province,null) ||
nvl2(loc.postal_code, ' '||loc.postal_code,null) ||
nvl2(loc.country, ' '||loc.country,null)
from hz_locations loc,
hz_party_sites hzp
where inc.incident_location_id = hzp.party_site_id
and hzp.location_id = loc.location_id )
ELSE inc.incident_address ||
nvl2(inc.incident_city, ','||inc.incident_city,null) ||
nvl2(inc.incident_state, ', '||inc.incident_state,null) ||
nvl2(inc.incident_province, ','||inc.incident_province,null) ||
nvl2(inc.incident_postal_code, ' '||inc.incident_postal_code,null)
||
nvl2(inc.incident_country, ' '||inc.incident_country,null)
END ) IncidentAddress
,(SELECT name
FROM jtf_objects_tl o,
jtf_object_usages ou
WHERE o.object_code = ou.object_code
AND ou.object_user_code = 'RESOURCES'
AND o.object_code = inc.resource_type
AND o.LANGUAGE = userenv ( 'LANG' ) ) ResourceType
,inc.incident_attribute_1 Attribute1
,inc.incident_attribute_2 Attribute2
,inc.incident_attribute_3 Attribute3
,inc.incident_attribute_4 Attribute4
,inc.incident_attribute_5 Attribute5
,inc.incident_attribute_6 Attribute6
,inc.incident_attribute_7 Attribute7
,inc.incident_attribute_8 Attribute8
,inc.incident_attribute_9 Attribute9
,inc.incident_attribute_10 Attribute10
,inc.incident_attribute_11 Attribute11
,inc.incident_attribute_12 Attribute12
,inc.incident_attribute_13 Attribute13
,inc.incident_attribute_14 Attribute14
,inc.incident_attribute_15 Attribute15
,inc.incident_context IncidentContext
,inc.external_attribute_1 ExtAttribute1
,inc.external_attribute_2 ExtAttribute2
,inc.external_attribute_3 ExtAttribute3
,inc.external_attribute_4 ExtAttribute4
,inc.external_attribute_5 ExtAttribute5
,inc.external_attribute_6 ExtAttribute6
,inc.external_attribute_7 ExtAttribute7
,inc.external_attribute_8 ExtAttribute8
,inc.external_attribute_9 ExtAttribute9
,inc.external_attribute_10 ExtAttribute10
,inc.external_attribute_11 ExtAttribute11
,inc.external_attribute_12 ExtAttribute12
,inc.external_attribute_13 ExtAttribute13
,inc.external_attribute_14 ExtAttribute14
,inc.external_attribute_15 ExtAttribute15
,inc.external_context ExtContext
from
cs_incidents_all_b inc
,cs_incidents_all_tl tl
,hz_parties party
,cs_hz_sr_contact_points sr_cont
,hz_contact_points hzcp
,csi_item_instances instance
where
inc.incident_id = tl.incident_id
and tl.language = userenv('lang')
and inc.customer_id = party.party_id (+)
and inc.incident_id = sr_cont.incident_id (+)
and sr_cont.primary_flag (+)='Y'
and sr_cont.contact_point_id = hzcp.contact_point_id (+)
and inc.inventory_item_id = instance.inventory_item_id (+)
and inc.customer_product_id = instance.instance_id(+)
and inc.incident_id = incident_id_in
and rownum = 1);
select incident_id into l_incident_id
from cs_incidents_all_b
where incident_number = p_incident_number
and rownum = 1;
x_getsr_out_rec.last_update_date := l_sr_rec.lastupdatedate;
select NOTE_TL.NOTES NOTES,
NOTE.NOTE_STATUS,
NOTE_TL.NOTES_DETAIL NOTES_DETAIL,
NOTE.CREATED_BY
BULK COLLECT INTO X_NOTES
--BULK COLLECT INTO L_NOTES
from JTF_NOTES_B NOTE,
CS_INCIDENTS_ALL_TL INC_TL,
JTF_NOTES_TL NOTE_TL
WHERE NOTE_TL.JTF_NOTE_ID = NOTE.JTF_NOTE_ID
AND NOTE_TL.LANGUAGE = INC_TL.LANGUAGE
AND NOTE.SOURCE_OBJECT_ID=INC_TL.INCIDENT_ID
AND NOTE.SOURCE_OBJECT_CODE= 'SR'
AND INC_TL.INCIDENT_ID = l_incident_id;
SELECT task_type, task_status, task_priority, restrict_closure_flag,
owner_type, owner, task_name, description, task_number,
parent_task_number, escalation_level, task_confirmation_status,
planned_start_date, planned_end_date, scheduled_start_date,
scheduled_end_date, actual_start_date, actual_end_date, planned_effort,
planned_effort_uom, actual_effort, actual_effort_uom, DURATION,
duration_uom, private_flag, publish_flag, row_id, created_by,
creation_date, last_updated_by, last_update_date, last_update_login,
source_object_id, source_object_type_code, source_object_name,
task_type_id, task_status_id, task_priority_id, owner_id,
owner_territory_id, task_id, timezone_id, parent_task_id,
object_version_number, customer_id, cust_account_id, owner_type_code,
recurrence_rule_id, multi_booked_flag, milestone_flag, holiday_flag,
billable_flag, currency_code, costs, attribute1, attribute2,
attribute3, attribute4, attribute5, attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, attribute_category
BULK COLLECT INTO X_TASKS
--BULK COLLECT INTO L_TASKS
FROM cs_sr_tasks_v
WHERE (source_object_id = l_incident_id);