The following lines contain the word 'select', 'insert', 'update' or 'delete':
select inventory_item_id into l_inv_item_id
from mtl_system_items_b_kfv msi, mtl_parameters mp
where msi.concatenated_segments = p_asset_group
and msi.organization_id = mp.organization_id
and mp.maint_organization_id = p_org_id
and rownum = 1;
select cii.instance_id
into l_instance_id
from csi_item_instances cii
where cii.inventory_item_id = l_inv_item_id
and cii.serial_number = p_serial_number;
select wip_entity_id into l_parent_weid from wip_entities
where wip_entity_name = p_parent_wo_name
and organization_id = p_org_id;
select inventory_item_id into l_asset_activity_id from
mtl_system_items_b_kfv where
concatenated_segments = p_asset_activity
and organization_id = p_org_id;
select ppv.project_id into l_project_id
from pjm_projects_v ppv,
pjm_project_parameters ppp
where ppv.project_id = ppp.project_id
and ppp.organization_id = p_org_id
and ppv.project_number = p_project_number;
select /*+ leading( ppv ) */ ppv.project_id, mtv.task_id into
l_project_id, l_task_id
from pjm_projects_v ppv,
pjm_project_parameters ppp,
mtl_task_v mtv
where ppv.project_id = ppp.project_id
and mtv.project_id(+) = ppp.project_id
and ppp.organization_id = p_org_id
and ppv.project_number = p_project_number
and task_number = p_task_number;
select count(*)
into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = 3
and meaa.maintenance_object_id = l_instance_id
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = 60
and eomd.accounting_class_code is not null
and nvl(meaa.tmpl_flag, 'N') = 'N';
select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = 3
and meaa.maintenance_object_id = l_instance_id
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = 60
and nvl(meaa.tmpl_flag, 'N') = 'N';
select count(*)
into l_count
from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id
and eomd.accounting_class_code is not null;
select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select class_code into x_class_code from
wip_discrete_jobs where
wip_entity_id = l_parent_weid
and organization_id = p_org_id;
select count(*)
into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = l_maintenance_object_type
and meaa.maintenance_object_id = l_instance_id
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40 )
and nvl(meaa.tmpl_flag, 'N') = 'N'
and eomd.accounting_class_code is not null;
select accounting_class_code into x_class_code
from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = l_maintenance_object_type
and meaa.maintenance_object_id = l_instance_id
and eomd.organization_id = p_org_id
and nvl(meaa.tmpl_flag, 'N') = 'N'
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40
);
select count(*)
into l_count
from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id
and eomd.accounting_class_code is not null;
select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select cii.instance_id
into l_instance_id
from csi_item_instances cii
where cii.inventory_item_id = p_asset_group_id
and cii.serial_number = p_serial_number;
select count(*)
into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = 3
and meaa.maintenance_object_id = l_instance_id
and nvl(meaa.tmpl_flag, 'N') = 'N'
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = 60
and eomd.accounting_class_code is not null ;
select accounting_class_code into x_class_code
from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = 3
and meaa.maintenance_object_id = l_instance_id
and nvl(meaa.tmpl_flag, 'N') = 'N'
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type = 60;
select count(*)
into l_count
from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id
and eomd.accounting_class_code is not null;
select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select class_code into x_class_code from
wip_discrete_jobs where
wip_entity_id = l_parent_weid
and organization_id = p_org_id;
select count(*)
into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = l_maintenance_object_type
and meaa.maintenance_object_id = l_instance_id
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type in (40, 60)
and nvl(meaa.tmpl_flag, 'N') = 'N'
and eomd.accounting_class_code is not null ;
select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
where meaa.asset_activity_id = l_asset_activity_id
and meaa.maintenance_object_type = l_maintenance_object_type
and meaa.maintenance_object_id = l_instance_id
and nvl(meaa.tmpl_flag, 'N') = 'N'
and eomd.organization_id = p_org_id
and meaa.activity_association_id = eomd.object_id
and eomd.object_type in (40, 60);
select count(*)
into l_count
from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id
and eomd.accounting_class_code is not null;
select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
where eomd.organization_id = p_org_id
and eomd.object_type = 50
and eomd.object_id = l_instance_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
select default_eam_class into x_class_code
from wip_eam_parameters where
organization_id = p_org_id;
cursor op_table is select operation_sequence_id, operation_seq_num
from bom_operation_sequences
where routing_sequence_id = p_common_routing_sequence_id ;
select nvl(asset_number,rebuild_serial_number), nvl(asset_group_id,rebuild_item_id)
into l_asset_number, l_inv_item_id
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
X_last_update_login => fnd_global.login_id
-- X_program_application_id=> '',
-- X_program_id => '',
-- X_request_id => ''
);
X_last_update_login => fnd_global.login_id
-- X_program_application_id=> '',
-- X_program_id => '',
-- X_request_id => ''
);
X_last_update_login => fnd_global.login_id
-- X_program_application_id=> '',
-- X_program_id => '',
-- X_request_id => ''
);
X_last_update_login => fnd_global.login_id
-- X_program_application_id=> '',
-- X_program_id => '',
-- X_request_id => ''
);
X_last_update_login => fnd_global.login_id
-- X_program_application_id=> '',
-- X_program_id => '',
-- X_request_id => ''
);
SELECT nvl(COUNT(*),0)
into l_operation_exist
FROM WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = l_wip_entity_id;
select scheduled_start_date,
scheduled_completion_date,
owning_department
into l_start_date,
l_completion_date,
l_owning_department_id
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id
and organization_id = l_organization_id;
/* select distinct msn.owning_department_id
into l_owning_department_id
from wip_discrete_jobs wdj,mtl_serial_numbers msn
where wdj.asset_group_id = msn.inventory_item_id (+)
and wdj.organization_id = msn.current_organization_id (+)
and wdj.asset_number = msn.serial_number (+)
and wdj.wip_entity_id = l_wip_entity_id
and wdj.organization_id = l_organization_id;*/
select eomd.owning_department_id
into l_owning_department_id
from eam_org_maint_defaults eomd, wip_discrete_jobs wdj
where wdj.maintenance_object_type = 3
and wdj.organization_id = eomd.organization_id (+)
and eomd.object_type (+) = 50
and eomd.object_id (+) = wdj.maintenance_object_id
and wdj.wip_entity_id = l_wip_entity_id
and wdj.organization_id = l_organization_id;
insert into wip_operations
(
wip_entity_id
,operation_seq_num
,organization_id
,repetitive_schedule_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,operation_sequence_id
,standard_operation_id
,department_id
,description
,scheduled_quantity
,quantity_in_queue
,quantity_running
,quantity_waiting_to_move
,quantity_rejected
,quantity_scrapped
,quantity_completed
,first_unit_start_date
,first_unit_completion_date
,last_unit_start_date
,last_unit_completion_date
,previous_operation_seq_num
,next_operation_seq_num
,count_point_type
,backflush_flag
,minimum_transfer_quantity
,date_last_moved
,wf_itemtype
,wf_itemkey
,operation_yield
,operation_yield_enabled
,pre_split_quantity
,operation_completed
,shutdown_type
,x_pos
,y_pos
)
values
(
l_wip_entity_id
,10
,l_organization_id
,null -- repetitive schedule id
,sysdate -- last_update_date
,FND_GLOBAL.USER_ID
,sysdate -- creation_date
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,null -- operation_sequence_id
,null -- standard_operation_id
,l_owning_department_id
,l_description
,1 -- scheduled_quantity
,1 -- quantity_in_queue
,1 -- quantity_running
,1 -- quantity_waiting_to_move
,0 -- quantity_rejected
,1 -- quantity_scrapped
,1 -- quantity_completed
,l_start_date
,l_completion_date
,l_start_date
,l_completion_date
,null -- previous_operation_seq_num
,null -- next_operation_seq_num
,1 -- count_point_type
,1 -- backflush_flag
,1 -- minimum_transfer_quantity
,null -- date_last_moved
,null -- wf_itemtype
,null -- wf_itemkey
,null -- operation_yield
,null -- operation_yield_enabled
,null -- pre_split_quantity
,null -- operation_completed
,null -- shutdown_type
,null -- x_pos
,null -- y_pos
);
select count(*)
into l_count
from wip_requirement_operations_v
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = 1;
select min(operation_seq_num)
into l_min_op_seq_num
from wip_operations
where organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id;
select department_id into l_department_id
from wip_operations
where organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id
and operation_seq_num = l_min_op_seq_num;
update wip_requirement_operations
set operation_seq_num = l_min_op_seq_num,
department_id = l_department_id
where operation_seq_num = 1 and
organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id;
select count(*)
into l_count
from wip_eam_direct_items
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = 1;
select min(operation_seq_num)
into l_min_op_seq_num
from wip_operations
where organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id;
select department_id into l_department_id
from wip_operations
where organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id
and operation_seq_num = l_min_op_seq_num;
update wip_eam_direct_items
set operation_seq_num = l_min_op_seq_num,
department_id = l_department_id
where operation_seq_num = 1 and
organization_id = p_organization_id and
wip_entity_id = p_wip_entity_id;