[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_warranty_entitlements
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
p_mr_asso_count IN NUMBER,
p_x_sr_task_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
);
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);
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
);
Update_warranty_entitlements(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_mr_asso_count => p_x_mr_asso_tbl.COUNT,
p_x_sr_task_tbl => p_x_sr_task_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'After Update_warranty_entitlements API');
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);
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 short_code FROM qa_char_value_lookups_v
WHERE char_id = 87
AND upper(description) = upper(ltrim(rtrim(p_qa_inspection_type_desc)));
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 incident_status_id
FROM cs_incident_statuses_vl
WHERE incident_subtype = 'INC'
AND UPPER(name) = UPPER(p_status_name)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active, SYSDATE))
AND rownum<2;
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));
Select warranty_contract_id into p_x_sr_task_rec.warranty_contract_id
From ahl_warranty_contracts_b
Where warranty_contract_id = p_x_sr_task_rec.warranty_contract_id
and item_instance_id = p_x_sr_task_rec.instance_id
and contract_status_code = 'ACTIVE';
Select warranty_contract_id into p_x_sr_task_rec.warranty_contract_id
From ahl_warranty_contracts_b
Where contract_number = p_x_sr_task_rec.warranty_contract_number
and item_instance_id = p_x_sr_task_rec.instance_id
and contract_status_code = 'ACTIVE';
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 LOOKUP_CODE
FROM pa_lookups
WHERE upper(MEANING) = p_service_type
AND LOOKUP_TYPE = 'SERVICE TYPE'
AND ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE - 1))
AND TRUNC(NVL(END_DATE_ACTIVE, SYSDATE));
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));
SELECT INCIDENT_TYPE_ID
FROM cs_incident_types_vl
where INCIDENT_SUBTYPE = 'INC'
AND CMRO_FLAG = 'Y'
AND NAME = p_name
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),
-- VLAKKU ER # 13787940
qa_inspection_type = p_x_sr_task_rec.quality_inspection_type_code
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;
PROCEDURE Update_warranty_entitlements
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
p_mr_asso_count IN NUMBER,
p_x_sr_task_tbl IN OUT NOCOPY AHL_PRD_NONROUTINE_PVT.sr_task_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
CURSOR Get_warranty_entl_rec_csr (c_sr_incident_id NUMBER)
IS
SELECT warranty_entitlement_id, object_version_number, visit_task_id
FROM AHL_WARRANTY_ENTITLEMENTS
WHERE sr_incident_id = c_sr_incident_id;
SELECT warranty_contract_id
FROM AHL_WARRANTY_ENTITLEMENTS
WHERE visit_task_id = (SELECT visit_task_id
FROM AHL_WORKORDERS
WHERE workorder_id = c_originating_wo_id);
SELECT workorder_id
FROM AHL_WORKORDERS
WHERE visit_task_id = c_visit_task_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_warranty_entitlements';
l_warranty_entl_tbl(l_rec_count).operation_flag := ahl_warranty_entl_pvt.G_OP_UPDATE;
END Update_warranty_entitlements;
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 service_type_code
from ahl_visit_tasks_b
where unit_effectivity_id = ue_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 WIP.FIRM_PLANNED_FLAG
FROM AHL_VISIT_TASKS_B VST,
AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WIP,
AHL_UNIT_EFFECTIVITIES_B UE
WHERE VST.SERVICE_REQUEST_ID = c_sr_id
AND VST.TASK_TYPE_CODE IN ('SUMMARY')
AND VST.VISIT_TASK_ID = WO.VISIT_TASK_ID
AND WO.WIP_ENTITY_ID = WIP.WIP_ENTITY_ID
AND VST.mr_id IS NULL
AND UE.CS_INCIDENT_ID = c_sr_id
AND UE.UNIT_EFFECTIVITY_ID= VST.UNIT_EFFECTIVITY_ID
AND (UE.STATUS_CODE IS NULL OR UE.STATUS_CODE = 'INIT-DUE');
SELECT organization_id, status_code FROM ahl_visits_b WHERE
visit_id = p_visit_id_csr;
SELECT visit_task_id FROM ahl_visit_tasks_b
WHERE repair_batch_name = (AHL_CMP_UTIL_PKG.Get_Rpr_Batch_For_Inst(p_instance_id,
p_org_id));
SELECT instance_id FROM ahl_visit_tasks_b
WHERE visit_task_id = p_task_id;
SELECT wip_entity_id FROM ahl_workorders
WHERE status_code IN ('1', '3', '6', '19', '20', '17')
--unreleased, released, on-hold, parts hold and pending QA approval(open workorders)
AND visit_task_id IN
(SELECT vt.visit_task_id FROM AHL_VISIT_TASKS_B vt
WHERE vt.cost_parent_id IS NOT NULL
AND NVL(vt.return_to_supply_flag,'N') = 'Y'
AND vt.instance_id = p_instance_id
START WITH vt.visit_task_id = p_rpr_batch_task_id
CONNECT BY PRIOR vt.visit_task_id = vt.cost_parent_id
);
SELECT repair_batch_name FROM ahl_visit_tasks_b WHERE
visit_task_id = p_task_id
AND repair_batch_name IS NOT NULL;
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;