[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_sr(
p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
x_return_status OUT NOCOPY VARCHAR2
);
SELECT incident_status_id FROM
cs_incident_statuses
WHERE status_code = 'PLANNED'
AND incident_subtype = 'INC'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate));
SELECT object_version_number
FROM CS_INCIDENTS
WHERE incident_id = p_incident_id;
SELECT
wo.workorder_id,
wo.status_code
FROM
ahl_visit_tasks_b vtsk,
ahl_workorders wo,
ahl_unit_effectivities_b ue
WHERE
ue.cs_incident_id = p_incident_id
AND ue.unit_effectivity_id = vtsk.unit_effectivity_id
AND vtsk.visit_task_id = wo.visit_task_id
AND upper(vtsk.task_type_code) = 'SUMMARY';
ELSIF upper(l_sr_task_rec.operation_type) = 'UPDATE' THEN
l_sr_task_rec.urgency_id := FND_API.G_MISS_NUM;
ELSIF ( upper(l_sr_task_rec.operation_type) = 'UPDATE' ) THEN
IF (G_DEBUG = 'Y') THEN
AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
Update_sr( p_x_sr_task_rec => l_sr_task_rec,
x_return_status => l_return_status);
--- If the mode is update , then check if the NR has a corresponding workorder created or not
-- and set the flags accordingly .
--1. Query if the NR has a workorder created !
OPEN c_does_wo_exist(l_sr_task_rec.Incident_id);
upper(l_sr_task_rec.operation_type) = 'UPDATE'
AND
p_x_mr_asso_tbl.COUNT > 0
)
)
THEN
Process_Mr(
p_x_task_tbl => p_x_sr_task_tbl,
p_mr_assoc_tbl => p_x_mr_asso_tbl,
p_module_type => p_module_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
AHL_DEBUG_PUB.debug('PROC : Calling the Update SR procedure');
-- Call Update Service Request procedure
Update_sr( p_x_sr_task_rec => l_sr_task_rec,
x_return_status => l_return_status);
SELECT lookup_code FROM fnd_lookup_values_vl
WHERE lookup_type = 'REQUEST_PROBLEM_CODE'
AND enabled_flag = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate))
AND upper(meaning) = upper(p_meaning);
SELECT lookup_code FROM fnd_lookup_values_vl
WHERE lookup_type = 'REQUEST_RESOLUTION_CODE'
AND enabled_flag = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate))
AND upper(meaning) = upper(p_meaning);
SELECT instance_id FROM csi_item_instances
WHERE instance_number = p_instance_number;
SELECT visit_id FROM ahl_visits_b
WHERE visit_number = p_visit_number;
SELECT OWNER_PARTY_ID
FROM csi_item_instances
WHERE instance_number = p_instance_number;
Select party_name
into
l_customer_name
from hz_parties
where party_id
= p_x_sr_task_rec.customer_id;
Select party_id
into
l_customer_id
from hz_parties
where party_name = p_x_sr_task_rec.customer_name;
Select party_name
into
l_contact_name
from hz_parties
where party_id
= p_x_sr_task_rec.contact_id;
Select party_id
into
l_contact_id
from hz_parties
where party_name = p_x_sr_task_rec.contact_name;
Select full_name
into
l_contact_name
from per_people_f
where person_id
= p_x_sr_task_rec.contact_id
and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
and trunc(nvl(effective_end_date,sysdate));
Select person_id
into
l_contact_id
from per_people_f
where full_name = p_x_sr_task_rec.contact_name
and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
and trunc(nvl(effective_end_date,sysdate));
ELSIF ( upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
-- Derive the Contact id, if its null and contact type in
-- 'RELATIONSHIP' or 'PERSON'.
-- If contact id is not null derive the contact name and id
-- and check the contact name against the input value, if <>
-- return error msg. If only contact name is passed then
-- derive the contact id and name.
IF (upper(p_x_sr_task_rec.contact_type) in ('PARTY_RELATIONSHIP','PERSON')) THEN
IF( p_x_sr_task_rec.contact_id is not null and
p_x_sr_task_rec.contact_id <> FND_API.G_MISS_NUM
and ( p_x_sr_task_rec.contact_name is not null and
p_x_sr_task_rec.contact_name <> FND_API.G_MISS_CHAR )) THEN
BEGIN
Select party_name
into
l_contact_name
from hz_parties
where party_id
= p_x_sr_task_rec.contact_id;
Select party_id
into
l_contact_id
from hz_parties
where party_name = p_x_sr_task_rec.contact_name;
Select full_name
into
l_contact_name
from per_people_f
where person_id
= p_x_sr_task_rec.contact_id
and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
and trunc(nvl(effective_end_date,sysdate));
Select person_id
into
l_contact_id
from per_people_f
where full_name = p_x_sr_task_rec.contact_name
and trunc(sysdate) between trunc(nvl(effective_start_date,sysdate))
and trunc(nvl(effective_end_date,sysdate));
SELECT incident_status_id FROM
cs_incident_statuses
WHERE status_code = 'OPEN'
AND incident_subtype = 'INC'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate));
SELECT csv.incident_severity_id
FROM cs_incident_severities_vl csv,
mfg_lookups mfl
WHERE csv.incident_severity_id = p_severity_id
AND mfl.lookup_type = 'WIP_EAM_ACTIVITY_PRIORITY'
AND trunc(sysdate) between trunc(nvl(csv.start_date_active,sysdate))
AND trunc(nvl(csv.end_date_active,sysdate));
SELECT employee_id
FROM fnd_user
WHERE user_id = fnd_global.user_id;
SELECT party_type
FROM hz_parties
WHERE party_id = fnd_profile.value('AHL_PRD_SR_CUSTOMER_NAME');
SELECT OWNER_PARTY_ID
FROM csi_item_instances
WHERE instance_id = p_item_instance_number;
SELECT party_type
FROM hz_parties
WHERE party_id = p_cust_id;
SELECT 'X'
FROM mtl_system_items_b mtl, csi_item_instances csi
WHERE csi.instance_id = c_instance_id
AND csi.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = (SELECT organization_id from wip_discrete_jobs wdj, ahl_workorders awo where wdj.wip_entity_id = awo.wip_entity_id and awo.workorder_id = c_workorder_id)
AND mtl.serial_number_control_code = 1;
SELECT csi.quantity
FROM CSI_ITEM_INSTANCES csi,
ahl_workorders wo
WHERE csi.instance_id = c_instance_id
AND csi.wip_job_id = WO.WIP_ENTITY_ID
AND wo.workorder_id = c_wo_id
AND csi.location_type_code = 'WIP'
AND not exists (select 'x' from csi_ii_relationships
where subject_id = csi.instance_id
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(ACTIVE_END_DATE, SYSDATE+1)) > TRUNC(SYSDATE));
Select party_name
into
p_x_sr_task_rec.customer_name
from hz_parties
where party_id = p_x_sr_task_rec.customer_id;
ELSIF(upper(p_x_sr_task_rec.operation_type) = 'UPDATE') THEN
-- bachandr added following validation for Bug # 6447467 (Base ER # 5571440)
-- Bug # 6447467 -- start
-- Check if resolution_meaning is not null. If resolution_meaning
-- is null then return error message.
IF ( nvl(fnd_profile.value('AHL_SR_RESL_CODE_COMP'), 'N') = 'Y') THEN
IF ( p_x_sr_task_rec.resolution_meaning IS NULL OR
p_x_sr_task_rec.resolution_meaning = FND_API.G_MISS_CHAR) THEN
Fnd_Message.SET_NAME('AHL','AHL_PRD_RESL_CODE_REQ');
SELECT A.inventory_item_id,
A.item_organization_id
FROM AHL_VISIT_TASKS_B A,
AHL_WORKORDERS B
WHERE A.visit_task_id = B.visit_task_id
AND B.workorder_id = p_workorder_id;
SELECT INCIDENT_TYPE_ID,NAME
FROM cs_incident_types_vl
where INCIDENT_SUBTYPE = 'INC'
AND CMRO_FLAG = 'Y'
-- Check added by balaji for bug # 4146503.
-- always has to pick up the SR type id from AHL default SR Type profile.
AND incident_type_id=fnd_profile.value('AHL_PRD_SR_TYPE')
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
AND trunc(nvl(end_date_active,sysdate));
update ahl_unit_effectivities_b
set unit_config_header_id = AHL_UTIL_UC_PKG.get_uc_header_id(p_x_sr_task_rec.instance_id)
where unit_effectivity_id in
(
select unit_effectivity_id
from ahl_unit_effectivities_b
where object_type = 'SR' and cs_incident_id = p_x_sr_task_rec.incident_id
);
UPDATE AHL_UNIT_EFFECTIVITIES_B
SET ORIGINATING_WO_ID = p_x_sr_task_rec.Originating_wo_id
WHERE CS_INCIDENT_ID = p_x_sr_task_rec.incident_id;
SELECT
WO.visit_task_id
FROM
AHL_WORKORDERS WO
WHERE
WO.workorder_id = c_Nonroutine_wo_id;
SELECT
awo.workorder_id
FROM
ahl_workorders awo,
ahl_visit_tasks_b vtsk
WHERE
awo.visit_task_id = vtsk.visit_task_id
AND awo.master_workorder_flag = 'Y'
AND vtsk.task_type_code = 'SUMMARY'
AND vtsk.mr_id is NULL
AND vtsk.unit_effectivity_id = p_unit_effectivity_id;
Select unit_effectivity_id
from AHL_UNIT_EFFECTIVITIES_B
where cs_incident_id = p_incident_id;
SELECT object_version_number
FROM CS_INCIDENTS
WHERE incident_id = p_incident_id;
SELECT workorder_id
FROM ahl_workorders
WHERE MASTER_WORKORDER_FLAG = 'N'
AND wip_entity_id in
(SELECT rel.child_object_id
FROM wip_sched_relationships rel START
WITH REL.parent_object_id = (SELECT wip_entity_id FROM ahl_workorders WHERE workorder_id = p_nr_wo_id)
CONNECT BY REL.parent_object_id = PRIOR REL.child_object_id
AND REL.parent_object_type_id = PRIOR REL.child_object_type_id
AND REL.relationship_type = 1
)
ORDER BY workorder_id;
Select name
FROM cs_incident_types_vl
WHERE incident_type_id = c_req_type_id;
Select visit_task_id
from ahl_workorders
where workorder_id = c_org_wo_id;
Select unit_effectivity_id
from AHL_UNIT_EFFECTIVITIES_B
where cs_incident_id = p_incident_id;
PROCEDURE Update_sr(
p_x_sr_task_rec IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_rec_type,
x_return_status OUT NOCOPY VARCHAR2
) IS
l_service_request_rec CS_SERVICEREQUEST_PUB.service_request_rec_type;
l_service_request_rec.last_update_program_code := p_x_sr_task_rec.source_program_code;
CS_SERVICEREQUEST_PUB.Update_ServiceRequest(
p_api_version => 3.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_request_id => p_x_sr_task_rec.incident_id,
--p_request_number => p_x_sr_task_rec.incident_number,
p_audit_comments => Null,
p_object_version_number => p_x_sr_task_rec.incident_object_version_number,
p_resp_appl_id => NULL,
p_resp_id => NULL,
p_last_updated_by => NULL,
p_last_update_login => NULL,
p_last_update_date => NULL,
p_service_request_rec => l_service_request_rec,
p_notes => l_notes_table,
p_contacts => l_contacts_table,
p_called_by_workflow => NULL,
p_workflow_process_id => NULL,
x_workflow_process_id => p_x_sr_task_rec.workflow_process_id,
x_interaction_id => p_x_sr_task_rec.interaction_id
);
END Update_sr;
Select workorder_name
from ahl_workorders
where workorder_id = p_wo_id;
Select instance_number
from csi_item_instances
where instance_id = p_instance_id;
Select workorder_name
from ahl_workorders
where workorder_id = p_sr_task_rec.originating_wo_id;
Select ci.instance_number,
ci.serial_number,
msi.concatenated_segments
from csi_item_instances ci,
mtl_system_items_kfv msi
where ci.instance_id = p_sr_task_rec.instance_id
and ci.inventory_item_id = msi.inventory_item_id
and ci.inv_master_organizatiOn_id = msi.organization_id;