[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select user_id into l_user_id
from fnd_user
where user_name = p_user_name;
select responsibility_id into l_resp_id
from fnd_responsibility_tl
where responsibility_name = l_resp
and application_id = l_appl_id
and language = l_lang;
select 1
into l_check
from fnd_user_resp_groups
where user_id = l_user_id
and responsibility_id = l_resp_id;
PROCEDURE insert_row
(
p_api_version IN NUMBER
,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_external_linear_id IN NUMBER
,p_external_linear_name IN VARCHAR2
,p_external_source_name IN VARCHAR2
,p_external_linear_type IN VARCHAR2
,x_eam_linear_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM eam_linear_locations WHERE external_linear_id = p_external_linear_id
AND external_source_name = p_external_source_name
AND external_linear_type = p_external_linear_type);
INSERT INTO eam_linear_locations
(
external_linear_id
,external_source_name
,external_linear_name
,external_linear_type
,eam_linear_id
) VALUES
(
p_external_linear_id
,p_external_source_name
,p_external_linear_name
,p_external_linear_type
,eam_linear_locations_s.nextval
) RETURNING eam_linear_id INTO x_eam_linear_id;
END insert_row;
PROCEDURE update_row
(
p_api_version IN NUMBER
,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_external_linear_id IN NUMBER
,p_external_linear_name IN VARCHAR2
,p_external_source_name IN VARCHAR2
,p_external_linear_type IN VARCHAR2
,p_eam_linear_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM eam_linear_locations WHERE eam_linear_id = p_eam_linear_id);
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM eam_linear_locations WHERE external_linear_id = p_external_linear_id
AND external_source_name = p_external_source_name
AND external_linear_type = p_external_linear_type
AND eam_linear_id <> p_eam_linear_id);
UPDATE eam_linear_locations SET
external_linear_id = p_external_linear_id
,external_source_name = p_external_source_name
,external_linear_name = p_external_linear_name
,external_linear_type = p_external_linear_type
WHERE
eam_linear_id = p_eam_linear_id;
END update_row;
SELECT count(*) INTO l_count FROM dual WHERE EXISTS
(SELECT 1 FROM mfg_lookups WHERE lookup_code = p_external_source_name
AND lookup_type = 'EAM_EXTERNAL_SOURCE_NAME');
SELECT eam_linear_id INTO x_eam_linear_id FROM eam_linear_locations
WHERE external_linear_id = p_external_linear_id
AND external_source_name = p_external_source_name
AND external_linear_type = p_external_linear_type;
-- if asset does not exist in ELL, then insert row and get EAM linear id
if (l_x_eam_linear_id = -1) then
eam_linear_locations_pub.insert_row(
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_external_linear_id => p_external_linear_id
,p_external_linear_name => p_external_linear_name
,p_external_source_name => p_external_source_name
,p_external_linear_type => p_external_linear_type
,x_eam_linear_id => l_x_eam_linear_id
,x_return_status => l_x_return_status
,x_msg_count => l_x_msg_count
,x_msg_data => l_x_msg_data
) ;
EAM_AssetNumber_Pub.Insert_Asset_Number
(
p_api_version => 1.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
,x_object_id => x_object_id
,p_INVENTORY_ITEM_ID => p_inventory_item_id
,p_SERIAL_NUMBER => p_serial_number
,p_CURRENT_STATUS => 3
,p_DESCRIPTIVE_TEXT => p_descriptive_text
,p_CURRENT_ORGANIZATION_ID => p_current_organization_id
,p_MAINTAINABLE_FLAG => 'Y'
,p_OWNING_DEPARTMENT_ID => p_OWNING_DEPARTMENT_ID
,p_NETWORK_ASSET_FLAG => 'N'
,p_instantiate_flag => TRUE
,p_eam_linear_id => l_x_eam_linear_id
);
-- if asset does not exist in ELL, then insert row and get EAM linear id
if (l_x_eam_linear_id = -1) then
eam_linear_locations_pub.insert_row(
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_external_linear_id => p_external_linear_id
,p_external_linear_name => p_external_linear_name
,p_external_source_name => p_external_source_name
,p_external_linear_type => p_external_linear_type
,x_eam_linear_id => l_x_eam_linear_id
,x_return_status => l_x_return_status
,x_msg_count => l_x_msg_count
,x_msg_data => l_x_msg_data
) ;
-- if asset does not exist in ELL, then insert row and get EAM linear id
if (l_x_eam_linear_id = -1) then
eam_linear_locations_pub.insert_row(
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_external_linear_id => p_external_linear_id
,p_external_linear_name => p_external_linear_name
,p_external_source_name => p_external_source_name
,p_external_linear_type => p_external_linear_type
,x_eam_linear_id => l_x_eam_linear_id
,x_return_status => l_x_return_status
,x_msg_count => l_x_msg_count
,x_msg_data => l_x_msg_data
) ;
select serial_number into l_eam_wo_rec.asset_number
from mtl_serial_numbers
where gen_object_id = l_eam_wo_rec.maintenance_object_id
and current_organization_id = l_eam_wo_rec.organization_id;
select department_id, department_code,description,organization_id
from bom_departments
where organization_id = p_organization_id
and nvl(disable_date, sysdate+1) >= sysdate;
select distinct(hou.organization_id) org_id, mp.organization_code org_code,hout.name org_name
from hr_all_organization_units hou,
hr_all_organization_units_tl hout,
mtl_parameters mp,wip_eam_parameters wep
where hou.organization_id = mp.organization_id
and hou.organization_id = hout.organization_id
and hou.organization_id = wep.organization_id
AND hout.LANGUAGE = USERENV('LANG')
and NVL(mp.eam_enabled_flag,'N') = 'Y';
select wewr.work_request_number work_request_number, wewr.asset_number asset_number,
oav.organization_code organization_code, oav.organization_name organization_name,
ml.meaning work_request_status, ml1.meaning work_request_priority,
bd.department_code owning_dept_code, bd.description owning_dept_description,
we.wip_entity_name work_order, wewr.description description,
wewr.expected_resolution_date expected_resolution_date,
ml2.meaning work_request_type, wewr.phone_number phone_number,
wewr.e_mail e_mail, wewr.contact_preference contact_preference
from org_access_view oav, mfg_lookups ml, mfg_lookups ml1,
mfg_lookups ml2, wip_eam_work_requests wewr, wip_entities we,
bom_departments bd
where wewr.work_request_id = p_work_request_id
and oav.organization_id = wewr.organization_id
and oav.resp_application_id = appl
and oav.responsibility_id = resp
and ml.lookup_type(+) = 'WIP_EAM_WORK_REQ_STATUS'
and ml.lookup_code(+) = wewr.work_request_status_id
and ml1.lookup_type(+) = 'WIP_EAM_ACTIVITY_PRIORITY'
and ml1.lookup_code(+) = wewr.work_request_priority_id
and ml2.lookup_type(+) = 'WIP_EAM_WORK_REQ_TYPE'
and ml2.lookup_code(+) = wewr.work_request_type_id
and bd.department_id(+) = wewr.work_request_owning_dept
and we.wip_entity_id(+) = wewr.wip_entity_id;
select responsibility_id
into l_resp_id
from fnd_responsibility_tl
where responsibility_name = l_resp
and application_id = l_appl_id
and language = l_lang;
SELECT we.wip_entity_name
, wdj.wip_entity_id
, wdj.organization_id
, wdj.description
, wdj.asset_number
, wdj.asset_group_id
, wdj.rebuild_item_id
, wdj.rebuild_serial_number
, we.gen_object_id
, wdj.maintenance_object_id
, wdj.maintenance_object_type
, wdj.maintenance_object_source
, wdj.eam_linear_location_id
, wdj.class_code
, wdj.primary_item_id
, wdj.activity_type
, wdj.activity_cause
, wdj.activity_source
, wdj.work_order_type
, wdj.status_type
, ml.meaning as wo_status
, wdj.start_quantity
, wdj.date_released
, wdj.owning_department
, wdj.priority
, wdj.requested_start_date
, wdj.due_date
, wdj.shutdown_type
, wdj.firm_planned_flag
, wdj.notification_required
, wdj.tagout_required
, wdj.plan_maintenance
, wdj.project_id
, wdj.task_id
, wdj.end_item_unit_number
, wdj.schedule_group_id
, wdj.bom_revision_date
, wdj.routing_revision_date
, wdj.alternate_routing_designator
, wdj.alternate_bom_designator
, wdj.routing_revision
, wdj.bom_revision
, wdj.parent_wip_entity_id
, wdj.manual_rebuild_flag
, wdj.pm_schedule_id
, wdj.material_account
, wdj.material_overhead_account
, wdj.resource_account
, wdj.outside_processing_account
, wdj.material_variance_account
, wdj.resource_variance_account
, wdj.outside_proc_variance_account
, wdj.std_cost_adjustment_account
, wdj.overhead_account
, wdj.overhead_variance_account
, wdj.scheduled_start_date
, wdj.scheduled_completion_date
, wdj.common_bom_sequence_id
, wdj.common_routing_sequence_id
, wdj.po_creation_time
, wdj.attribute_category
, wdj.attribute1
, wdj.attribute2
, wdj.attribute3
, wdj.attribute4
, wdj.attribute5
, wdj.attribute6
, wdj.attribute7
, wdj.attribute8
, wdj.attribute9
, wdj.attribute10
, wdj.attribute11
, wdj.attribute12
, wdj.attribute13
, wdj.attribute14
, wdj.attribute15
, wdj.material_issue_by_mo
, wdj.source_line_id
, wdj.source_code
, wdj.issue_zero_cost_flag
INTO
x_work_order_rec.wip_entity_name
, x_work_order_rec.wip_entity_id
, x_work_order_rec.organization_id
, x_work_order_rec.description
, x_work_order_rec.asset_number
, x_work_order_rec.asset_group_id
, x_work_order_rec.rebuild_item_id
, x_work_order_rec.rebuild_serial_number
, x_work_order_rec.gen_object_id
, x_work_order_rec.maintenance_object_id
, x_work_order_rec.maintenance_object_type
, x_work_order_rec.maintenance_object_source
, x_work_order_rec.eam_linear_location_id
, x_work_order_rec.class_code
, x_work_order_rec.asset_activity_id
, x_work_order_rec.activity_type
, x_work_order_rec.activity_cause
, x_work_order_rec.activity_source
, x_work_order_rec.work_order_type
, x_work_order_rec.status_type
, x_work_order_rec.wo_status
, x_work_order_rec.job_quantity
, x_work_order_rec.date_released
, x_work_order_rec.owning_department
, x_work_order_rec.priority
, x_work_order_rec.requested_start_date
, x_work_order_rec.due_date
, x_work_order_rec.shutdown_type
, x_work_order_rec.firm_planned_flag
, x_work_order_rec.notification_required
, x_work_order_rec.tagout_required
, x_work_order_rec.plan_maintenance
, x_work_order_rec.project_id
, x_work_order_rec.task_id
, x_work_order_rec.end_item_unit_number
, x_work_order_rec.schedule_group_id
, x_work_order_rec.bom_revision_date
, x_work_order_rec.routing_revision_date
, x_work_order_rec.alternate_routing_designator
, x_work_order_rec.alternate_bom_designator
, x_work_order_rec.routing_revision
, x_work_order_rec.bom_revision
, x_work_order_rec.parent_wip_entity_id
, x_work_order_rec.manual_rebuild_flag
, x_work_order_rec.pm_schedule_id
, x_work_order_rec.material_account
, x_work_order_rec.material_overhead_account
, x_work_order_rec.resource_account
, x_work_order_rec.outside_processing_account
, x_work_order_rec.material_variance_account
, x_work_order_rec.resource_variance_account
, x_work_order_rec.outside_proc_variance_account
, x_work_order_rec.std_cost_adjustment_account
, x_work_order_rec.overhead_account
, x_work_order_rec.overhead_variance_account
, x_work_order_rec.scheduled_start_date
, x_work_order_rec.scheduled_completion_date
, x_work_order_rec.common_bom_sequence_id
, x_work_order_rec.common_routing_sequence_id
, x_work_order_rec.po_creation_time
, x_work_order_rec.attribute_category
, x_work_order_rec.attribute1
, x_work_order_rec.attribute2
, x_work_order_rec.attribute3
, x_work_order_rec.attribute4
, x_work_order_rec.attribute5
, x_work_order_rec.attribute6
, x_work_order_rec.attribute7
, x_work_order_rec.attribute8
, x_work_order_rec.attribute9
, x_work_order_rec.attribute10
, x_work_order_rec.attribute11
, x_work_order_rec.attribute12
, x_work_order_rec.attribute13
, x_work_order_rec.attribute14
, x_work_order_rec.attribute15
, x_work_order_rec.material_issue_by_mo
, x_work_order_rec.source_line_id
, x_work_order_rec.source_code
, x_work_order_rec.issue_zero_cost_flag
FROM wip_discrete_jobs wdj, wip_entities we, mfg_lookups ml
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND wdj.wip_entity_id = p_wip_entity_id
AND wdj.status_type = ml.lookup_code
AND ml.lookup_type = 'WIP_JOB_STATUS';