[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_entities_s.nextval
INTO l_wip_entity_id
FROM dual;
SELECT work_order_prefix
INTO l_wip_entity_name_prefix
FROM wip_eam_parameters
WHERE organization_id = p_organization_id;
SELECT wip_job_number_s.nextval
INTO l_wip_entity_name
FROM dual;
select count(*) into l_wip_entity_count from wip_entities
where organization_id = p_org_id and wip_entity_name =l_wip_entity_name
and rownum <= 1;
SELECT description
INTO l_activity_description
FROM MTL_SYSTEM_ITEMS_KFV MSI, MTL_PARAMETERS MP
WHERE MSI.inventory_item_id = p_eam_wo_rec.asset_activity_id
AND MSI.organization_id = MP.ORGANIZATION_ID
AND MP.MAINT_ORGANIZATION_ID = p_eam_wo_rec.organization_id
AND rownum = 1;
(p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND
NVL(p_eam_wo_rec.asset_activity_id,-1) <> NVL(l_old_eam_wo_rec.asset_activity_id,-1)) commented for BUG#5609642 */ ) THEN --default if new workorder or activity is changed
WIP_EAMWORKORDER_PVT.Get_EAM_Act_Cause_Default
(p_api_version => 1,
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 => l_msg_count,
x_msg_data => l_err_text,
p_primary_item_id => p_eam_wo_rec.asset_activity_id,
p_organization_id => p_eam_wo_rec.organization_id,
p_maintenance_object_type => p_eam_wo_rec.maintenance_object_type,
p_maintenance_object_id => p_eam_wo_rec.maintenance_object_id,
p_rebuild_item_id => p_eam_wo_rec.rebuild_item_id,
x_activity_cause_code => l_activity_cause
);
(p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE AND
NVL(p_eam_wo_rec.asset_activity_id,-1) <> NVL(l_old_eam_wo_rec.asset_activity_id,-1))Commented for BUG#5609642 */ ) THEN --default if new workorder or activity is changed
BEGIN
IF p_eam_wo_rec.maintenance_object_type = 3
THEN
-- Asset or Serialized Rebuild with activity
SELECT
nvl(p_eam_wo_rec.owning_department, nvl(eomd.owning_department_id, eomd_asset.owning_department_id))
, nvl(p_eam_wo_rec.priority, meaa.priority_code)
INTO
l_owning_department
, l_priority
FROM
mtl_eam_asset_activities meaa
, eam_org_maint_defaults eomd
, eam_org_maint_defaults eomd_asset
WHERE
meaa.maintenance_object_type = 3
AND eomd.object_type (+) = 60
AND eomd.object_id (+) = meaa.activity_association_id
AND eomd_asset.object_type (+) = 50
AND eomd_asset.organization_id (+) = p_eam_wo_rec.organization_id
AND eomd_asset.object_id (+) = meaa.maintenance_object_id
AND meaa.asset_activity_id = p_eam_wo_rec.asset_activity_id
AND eomd.organization_id (+) = p_eam_wo_rec.organization_id
AND meaa.maintenance_object_id = p_eam_wo_rec.maintenance_object_id;
select default_department_id into x_eam_wo_rec.owning_department
from WIP_EAM_PARAMETERS
where organization_id = p_eam_wo_rec.organization_id;
SELECT
nvl(p_eam_wo_rec.owning_department, eomd.owning_department_id)
, nvl(p_eam_wo_rec.priority, meaa.priority_code)
INTO
l_owning_department
, l_priority
FROM
mtl_eam_asset_activities meaa,
eam_org_maint_defaults eomd
WHERE
meaa.asset_activity_id = p_eam_wo_rec.asset_activity_id
AND eomd.object_type (+) = 40
and eomd.organization_id (+) = p_eam_wo_rec.organization_id
and eomd.object_id (+) = meaa.activity_association_id
AND meaa.maintenance_object_type = 2
and meaa.maintenance_object_id = p_eam_wo_rec.maintenance_object_id;
select default_department_id into x_eam_wo_rec.owning_department
from WIP_EAM_PARAMETERS
where organization_id = p_eam_wo_rec.organization_id;
SELECT
p_eam_wo_rec.priority
, nvl(p_eam_wo_rec.owning_department, eomd.owning_department_id)
INTO
l_priority
, l_owning_department
FROM
csi_item_instances cii,
eam_org_maint_defaults eomd
WHERE
cii.instance_id = p_eam_wo_rec.maintenance_object_id
and eomd.object_type (+) = 50
and eomd.object_id (+) = cii.instance_id
and eomd.organization_id (+) = p_eam_wo_rec.organization_id;
select default_department_id into x_eam_wo_rec.owning_department
from WIP_EAM_PARAMETERS
where organization_id = p_eam_wo_rec.organization_id;
SELECT
material_account
, material_overhead_account
, resource_account
, outside_processing_account
, material_variance_account
, resource_variance_account
, outside_proc_variance_account
, std_cost_adjustment_account
, overhead_account
, overhead_variance_account
INTO
l_material_account
, l_material_overhead_account
, l_resource_account
, l_outside_processing_account
, l_material_variance_account
, l_resource_variance_account
, l_osp_var_account
, l_std_cost_adjustment_account
, l_overhead_account
, l_overhead_variance_account
FROM wip_accounting_classes
WHERE
class_code = x_eam_wo_rec.class_code
AND organization_id = p_eam_wo_rec.organization_id;
( p_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PVT.G_OPR_UPDATE --workorder updated
AND (p_eam_wo_rec.status_type <> p_old_eam_wo_rec.status_type) --system status updated
--netiher the user_defined_status nor the pending flag are modfied
AND NOT((p_eam_wo_rec.user_defined_status_id <> p_old_eam_wo_rec.user_defined_status_id) OR (NVL(p_eam_wo_rec.pending_flag,'N') <> NVL(p_old_eam_wo_rec.pending_flag,'N')))
)
)THEN
x_eam_wo_rec.user_defined_status_id := p_eam_wo_rec.status_type; --set the user_defined_status_id to status_type since status_id for seeded statuses will be same as status_type
SELECT
nvl(issue_zero_cost_flag,'N')
INTO x_eam_wo_rec.issue_zero_cost_flag
FROM wip_eam_parameters
WHERE organization_id = p_eam_wo_rec.organization_id;
SELECT
po_creation_time
INTO x_eam_wo_rec.po_creation_time
FROM wip_parameters
WHERE organization_id = p_eam_wo_rec.organization_id;
select nvl(auto_firm_on_create,'N') into l_auto_firm_create_flag
from wip_eam_parameters
where organization_id = p_eam_wo_rec.organization_id;
IF (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE
and p_eam_wo_rec.status_type = 3 and
l_old_eam_wo_rec.status_type in (1,6,17)
) OR
(p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE
and p_eam_wo_rec.status_type = 3
)
THEN
select nvl(auto_firm_flag,'N') into l_auto_firm_flag
from wip_eam_parameters
where organization_id = p_eam_wo_rec.organization_id;
IF (p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE
and p_eam_wo_rec.status_type = 3
and l_old_eam_wo_rec.status_type in (1,6,17)
and l_auto_firm_flag = 'Y'
)
THEN
x_eam_wo_rec.scheduled_start_date := l_old_eam_wo_rec.scheduled_start_date;
select nvl(material_issue_by_mo,'N')
into x_eam_wo_rec.material_issue_by_mo
from wip_eam_parameters
where organization_id = p_eam_wo_rec.organization_id;
select cii.serial_number
, cii.inventory_item_id
, msi.eam_item_type
into l_serial_number
, l_inventory_item_id
, l_eam_item_type
from csi_item_instances cii, mtl_system_items msi
where cii.last_vld_organization_id = msi.organization_id
and cii.inventory_item_id = msi.inventory_item_id
and cii.instance_id = p_eam_wo_rec.maintenance_object_id;
select inventory_item_id
into x_eam_wo_rec.rebuild_item_id
from mtl_system_items msi, mtl_parameters mp
where msi.organization_id = mp.organization_id
and mp.maint_organization_id = p_eam_wo_rec.organization_id
and msi.inventory_item_id = p_eam_wo_rec.maintenance_object_id
and rownum = 1;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Processing null columns prior update'); END IF;
IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Done processing null columns prior update'); END IF;