[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaa.Asset_Activity_Id, meaa.start_date_active, meaa.end_date_active,
meaa.Priority_Code, meaa.maintenance_object_type, meaa.maintenance_object_id,
meaa.template_flag, meaa.Attribute_Category, meaa.Attribute1, meaa.Attribute2,
meaa.Attribute3, meaa.Attribute4, meaa.Attribute5, meaa.Attribute6,
meaa.Attribute7, meaa.Attribute8, meaa.Attribute9, meaa.Attribute10,
meaa.Attribute11, meaa.Attribute12, meaa.Attribute13, meaa.Attribute14,
meaa.Attribute15,
meaa.Activity_Association_Id, meaa.organization_id, meaa.accounting_class_code,
meaa.owning_department_id, meaa.Activity_Cause_Code, meaa.Activity_Type_Code,
meaa.Activity_Source_Code, meaa.Tagging_Required_Flag, meaa.Shutdown_Type_Code
FROM mtl_eam_asset_activities_v meaa
WHERE meaa.asset_activity_id = p_source_activity_id
AND (p_maintenance_object_type IS NULL OR meaa.maintenance_object_type = p_maintenance_object_type)
AND (p_maintenance_object_id IS NULL OR meaa.maintenance_object_id = p_maintenance_object_id)
AND (p_tmpl_flag IS NULL OR NVL(meaa.template_flag, 'N') = p_tmpl_flag)
AND meaa.maintenance_object_type IS NOT NULL
AND meaa.maintenance_object_id IS NOT NULL
AND meaa.organization_id = p_source_org_id;
SELECT eam_activity_cause_code, eam_activity_type_code, eam_act_notification_flag,
eam_act_shutdown_status, eam_activity_source_code
INTO l_activity_cause_code, l_activity_type_code, l_tagging_required_flag,
l_shutdown_type_code, l_activity_source_code
FROM mtl_system_items
WHERE inventory_item_id = p_target_activity_id
AND organization_id = p_target_org_id;
-- Need to create Association; cursor should select no row.
-- Need to create Association for current; cursor should select no row.
SELECT owning_department, class_code, maintenance_object_id, maintenance_object_type, priority, tagout_required
INTO l_wo_dept_id, l_wo_wac, l_cur_maintenance_object_id, l_cur_maintenance_object_type, l_wo_priority, l_wo_tagout
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id;
UPDATE mtl_eam_asset_activities
SET priority_code = nvl(l_wo_priority, priority_code)
WHERE asset_activity_id = p_target_activity_id AND maintenance_object_id = l_cur_maintenance_object_id
AND maintenance_object_type = l_cur_maintenance_object_type;
UPDATE eam_org_maint_defaults
SET accounting_class_code = nvl(l_wo_wac, accounting_class_code),
owning_department_id = nvl(l_wo_dept_id, owning_department_id),
tagging_required_flag = nvl(l_wo_tagout, tagging_required_flag)
WHERE object_id in (SELECT activity_association_id
FROM mtl_eam_asset_activities
WHERE asset_activity_id = p_target_activity_id
AND maintenance_object_id = l_cur_maintenance_object_id
AND maintenance_object_type = l_cur_maintenance_object_type)
AND object_type in (40, 60) AND organization_id = p_target_org_id;
SELECT count(*) INTO l_count
FROM wip_eam_parameters
WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id;
SELECT count(*) INTO l_count
FROM mtl_system_items
WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
AND inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id
AND eam_item_type = 2;
select count(*) into l_count
from mfg_lookups
where lookup_type = g_act_priority
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and nvl(enabled_flag, 'N') = 'Y'
and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code;
SELECT count(*) INTO l_count
FROM bom_departments
WHERE organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
and department_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id
and (disable_date IS NULL
or disable_date > sysdate);
select count(*) into l_count
from mfg_lookups
where lookup_type = g_act_type
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and nvl(enabled_flag, 'N') = 'Y'
and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code;
select count(*) into l_count
from mfg_lookups
where lookup_type = g_act_cause
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and nvl(enabled_flag, 'N') = 'Y'
and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code;
select count(*) into l_count
from mfg_lookups
where lookup_type = g_act_source
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and nvl(enabled_flag, 'N') = 'Y'
and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code;
select count(*) into l_count
from mfg_lookups
where lookup_type = g_shutdown_type
and sysdate between nvl(start_date_active,sysdate)
and nvl(end_date_active,sysdate)
and nvl(enabled_flag, 'N') = 'Y'
and lookup_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code;
select count(*) into l_count
from wip_accounting_classes
where class_code = l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code
and organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
and class_type = 6
and (disable_date is null or sysdate < disable_date);
SELECT count(cii.instance_id) into l_count
FROM csi_item_instances cii, mtl_system_items_b msi, mtl_parameters mp
WHERE cii.instance_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
AND mp.organization_id = cii.last_vld_organization_id
AND mp.maint_organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
AND cii.last_vld_organization_id = msi.organization_id
AND cii.inventory_item_id = msi.inventory_item_id
AND msi.eam_item_type in (1,3)
AND msi.serial_number_control_code <> 1
AND nvl(cii.active_start_date, sysdate-1) <= sysdate
AND nvl(cii.active_end_date, sysdate+1) >= sysdate;
SELECT count(inventory_item_id) into l_count
FROM mtl_system_items_b msi, mtl_parameters mp
WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
AND mp.maint_organization_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
AND mp.organization_id = msi.organization_id
AND msi.eam_item_type in (1,3);
SELECT serial_number_control_code into l_count
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
AND rownum = 1;
SELECT serial_number_control_code into l_count
FROM mtl_system_items_b msi
WHERE msi.inventory_item_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id
AND rownum = 1;
select count(1) into l_count
from mtl_eam_asset_activities
where asset_activity_id = l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id
and maintenance_object_id = l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_id
and maintenance_object_type = l_act_assoc_tbl(l_act_assoc_tbl_index).maintenance_object_type;
-- Insert into database table
BEGIN
-- Get activity_association_id from sequence
SELECT mtl_eam_asset_activities_s.nextval
INTO l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id
FROM dual;
-- Insert into Database Table mtl_eam_asset_activities
INSERT INTO mtl_eam_asset_activities (
Asset_Activity_Id,
Start_Date_Active,
End_Date_Active,
Priority_Code,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Activity_Association_Id,
Maintenance_Object_Id,
Maintenance_Object_Type,
Tmpl_Flag
) VALUES (
l_act_assoc_tbl(l_act_assoc_tbl_index).Asset_Activity_Id,
l_act_assoc_tbl(l_act_assoc_tbl_index).Start_Date_Active,
l_act_assoc_tbl(l_act_assoc_tbl_index).End_Date_Active,
l_act_assoc_tbl(l_act_assoc_tbl_index).Priority_Code,
l_current_date,
FND_GLOBAL.USER_ID,
l_current_date,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute_Category,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute1,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute2,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute3,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute4,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute5,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute6,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute7,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute8,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute9,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute10,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute11,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute12,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute13,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute14,
l_act_assoc_tbl(l_act_assoc_tbl_index).Attribute15,
l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id,
l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Id,
l_act_assoc_tbl(l_act_assoc_tbl_index).Maintenance_Object_Type,
l_act_assoc_tbl(l_act_assoc_tbl_index).Tmpl_Flag
);
FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INSERT_ASSOC');
eam_org_maint_defaults_pvt.insert_row
(
p_api_version => 1.0
,p_object_type => l_object_type
,p_object_id => l_act_assoc_tbl(l_act_assoc_tbl_index).activity_association_id
,p_organization_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Organization_Id
,p_owning_department_id => l_act_assoc_tbl(l_act_assoc_tbl_index).Owning_Department_Id
,p_accounting_class_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Class_Code
,p_area_id => null
,p_activity_cause_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Cause_Code
,p_activity_type_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Type_Code
,p_activity_source_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Activity_Source_Code
,p_shutdown_type_code => l_act_assoc_tbl(l_act_assoc_tbl_index).Shutdown_Type_Code
,p_tagging_required_flag => l_act_assoc_tbl(l_act_assoc_tbl_index).Tagging_Required_Flag
,x_return_status => l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status
,x_msg_count => x_msg_count
,x_msg_data => l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg
);
'Failed during insert in EOMD' ||
'Return_Status=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Return_Status ||
'; Error_Mesg=' || l_act_assoc_tbl(l_act_assoc_tbl_index).Error_Mesg);
l_date_insert DATE;
SELECT meaa.Asset_Activity_Id, meaa.start_date_active, meaa.end_date_active,
meaa.Priority_Code, meaa.Attribute_Category, meaa.Attribute1, meaa.Attribute2,
meaa.Attribute3, meaa.Attribute4, meaa.Attribute5, meaa.Attribute6, meaa.Attribute7,
meaa.Attribute8, meaa.Attribute9, meaa.Attribute10, meaa.Attribute11, meaa.Attribute12,
meaa.Attribute13, meaa.Attribute14, meaa.Attribute15, meaa.Prev_Service_Start_Date,
meaa.Prev_Service_End_Date, meaa.Last_Scheduled_Start_Date, meaa.Last_Scheduled_End_Date,
meaa.Prev_Scheduled_Start_Date, meaa.Prev_Scheduled_End_Date,
meaa.Activity_Association_Id, eomd.organization_id, eomd.accounting_class_code, eomd.owning_department_id,
eomd.Activity_Cause_Code, eomd.Activity_Type_Code, eomd.Activity_Source_Code,
eomd.Tagging_Required_Flag, eomd.Shutdown_Type_Code
FROM mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
WHERE maintenance_object_id = p_maintenance_object_id AND maintenance_object_type = 2
AND tmpl_flag = 'Y' AND meaa.Activity_Association_Id = eomd.object_id(+) AND eomd.object_type(+) = 40
AND eomd.organization_id(+) = p_organization_id;
l_date_insert := l_current_date;
SELECT cii.inventory_item_id, mp.maint_organization_id
INTO l_inventory_item_id, l_organization_id
FROM csi_item_instances cii, mtl_parameters mp
WHERE cii.instance_id = p_maintenance_object_id
AND mp.organization_id = cii.last_vld_organization_id ;
SELECT mtl_eam_asset_activities_s.nextval
INTO l_next_association_id
FROM dual;
l_date_insert := l_current_date;
SELECT COUNT(1) INTO l_count FROM eam_pm_schedulings eps, eam_pm_activities epa
WHERE epa.activity_association_id = l_asset_activity_row.activity_association_id
AND epa.pm_schedule_id = eps.pm_schedule_id
AND nvl(eps.tmpl_flag, 'N') = 'Y' AND eps.auto_instantiation_flag = 'Y';
l_date_insert := null;
l_date_insert := null;
-- 2: Insert row into mtl_eam_asset_activities
BEGIN
-- Insert into Database Table mtl_eam_asset_activities
INSERT INTO mtl_eam_asset_activities (
Asset_Activity_Id,
Start_Date_Active,
End_Date_Active,
Priority_Code,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Last_Update_Login,
Attribute_Category,
Attribute1,
Attribute2,
Attribute3,
Attribute4,
Attribute5,
Attribute6,
Attribute7,
Attribute8,
Attribute9,
Attribute10,
Attribute11,
Attribute12,
Attribute13,
Attribute14,
Attribute15,
Activity_Association_Id,
Last_Service_Start_Date,
Last_Service_End_Date,
Prev_Service_Start_Date,
Prev_Service_End_Date,
Last_Scheduled_Start_Date,
Last_Scheduled_End_Date,
Prev_Scheduled_Start_Date,
Prev_Scheduled_End_Date,
Maintenance_Object_Id,
Maintenance_Object_type,
Tmpl_Flag,
Source_Tmpl_Id
) VALUES (
l_asset_activity_row.Asset_Activity_Id,
-- 2735563: Simply pick up ALL templates and copy the start and end dates to the association records
l_asset_activity_row.start_date_active,
l_asset_activity_row.end_date_active,
l_asset_activity_row.Priority_Code,
l_current_date,
FND_GLOBAL.USER_ID,
l_current_date,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_asset_activity_row.Attribute_Category,
l_asset_activity_row.Attribute1,
l_asset_activity_row.Attribute2,
l_asset_activity_row.Attribute3,
l_asset_activity_row.Attribute4,
l_asset_activity_row.Attribute5,
l_asset_activity_row.Attribute6,
l_asset_activity_row.Attribute7,
l_asset_activity_row.Attribute8,
l_asset_activity_row.Attribute9,
l_asset_activity_row.Attribute10,
l_asset_activity_row.Attribute11,
l_asset_activity_row.Attribute12,
l_asset_activity_row.Attribute13,
l_asset_activity_row.Attribute14,
l_asset_activity_row.Attribute15,
l_next_association_id,
l_date_insert,
l_date_insert,
l_asset_activity_row.Prev_Service_Start_Date,
l_asset_activity_row.Prev_Service_End_Date,
l_date_insert,
l_date_insert,
l_asset_activity_row.Prev_Scheduled_Start_Date,
l_asset_activity_row.Prev_Scheduled_End_Date,
p_maintenance_object_id,
p_maintenance_object_type,
'N',
l_asset_activity_row.Activity_Association_Id
);
FND_MESSAGE.SET_NAME('EAM', 'EAM_ABO_INSERT_ASSOC');
eam_org_maint_defaults_pvt.insert_row
(
p_api_version => 1.0
,p_object_type => 60
,p_object_id => l_next_association_id
,p_organization_id => l_asset_activity_row.Organization_Id
,p_owning_department_id => l_owning_department_id
,p_accounting_class_code => l_class_code
,p_area_id => null
,p_activity_cause_code => l_asset_activity_row.Activity_Cause_Code
,p_activity_type_code => l_asset_activity_row.Activity_Type_Code
,p_activity_source_code => l_asset_activity_row.Activity_Source_Code
,p_shutdown_type_code => l_asset_activity_row.Shutdown_Type_Code
,p_tagging_required_flag => l_asset_activity_row.Tagging_Required_Flag
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);