[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE DELETE_RELATIONSHIP
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
, p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
, p_parent_object_id IN NUMBER
, p_parent_object_type_id IN NUMBER
, p_child_object_id IN NUMBER
, p_child_object_type_id IN NUMBER
, p_new_parent_object_id IN NUMBER
, p_new_parent_object_type_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
null;
END DELETE_RELATIONSHIP;
l_eam_wo_tbl.delete;
l_eam_wo_relations_tbl.delete;
l_eam_op_tbl.delete;
l_eam_op_network_tbl.delete;
l_eam_res_tbl.delete;
l_eam_res_inst_tbl.delete;
l_eam_sub_res_tbl.delete;
l_eam_mat_req_tbl.delete;
l_eam_di_tbl.delete;
l_eam_res_usage_tbl.delete;
l_eam_wo_comp_tbl.delete;
l_eam_wo_quality_tbl.delete;
l_eam_meter_reading_tbl.delete;
l_eam_counter_prop_tbl.delete;
l_eam_wo_comp_rebuild_tbl.delete;
l_eam_wo_comp_mr_read_tbl.delete;
l_eam_op_comp_tbl.delete;
l_eam_request_tbl.delete;
l_out_eam_wo_tbl.delete;
l_out_eam_wo_relations_tbl.delete;
l_out_eam_op_tbl.delete;
l_out_eam_op_network_tbl.delete;
l_out_eam_res_tbl.delete;
l_out_eam_res_inst_tbl.delete;
l_out_eam_sub_res_tbl.delete;
l_out_eam_mat_req_tbl.delete;
l_out_eam_di_tbl.delete;
l_out_eam_res_usage_tbl.delete;
l_out_eam_wo_comp_tbl.delete;
l_out_eam_wo_quality_tbl.delete;
l_out_eam_meter_reading_tbl.delete;
l_out_eam_counter_prop_tbl.delete;
l_out_eam_wo_comp_rebuild_tbl.delete;
l_out_eam_wo_comp_mr_read_tbl.delete;
l_out_eam_op_comp_tbl.delete;
l_out_eam_request_tbl.delete;
l_out_eam_wo_tbl_main.delete;
l_out_eam_wo_rel_tbl_main.delete;
l_out_eam_op_tbl_main.delete;
l_out_eam_op_network_tbl_main.delete;
l_out_eam_res_tbl_main.delete;
l_out_eam_res_inst_tbl_main.delete;
l_out_eam_sub_res_tbl_main.delete;
l_out_eam_mat_req_tbl_main.delete;
l_out_eam_di_tbl_main.delete;
l_out_eam_res_usage_tbl_main.delete;
l_out_eam_wo_comp_tbl_main.delete;
l_out_eam_wo_quality_tbl_main.delete;
l_ou_eam_meter_reading_tbl_m.delete;
l_out_eam_counter_prop_tbl_m.delete;
l_ou_eam_wo_comp_rebuild_tbl_m.delete;
l_ou_eam_wo_comp_mr_read_tbl_m.delete;
l_out_eam_op_comp_tbl_main.delete;
l_out_eam_request_tbl_main.delete;
l_out_eam_counter_prop_tbl_m.delete;
x_eam_wo_tbl.delete;
x_eam_wo_relations_tbl.delete;
x_eam_op_tbl.delete;
x_eam_op_network_tbl.delete;
x_eam_res_tbl.delete;
x_eam_res_inst_tbl.delete;
x_eam_sub_res_tbl.delete;
x_eam_mat_req_tbl.delete;
x_eam_direct_items_tbl.delete;
x_eam_res_usage_tbl.delete;
x_eam_wo_comp_tbl.delete;
x_eam_wo_quality_tbl.delete;
x_eam_meter_reading_tbl.delete;
x_eam_counter_prop_tbl.delete;
x_eam_wo_comp_rebuild_tbl.delete;
x_eam_wo_comp_mr_read_tbl.delete;
x_eam_op_comp_tbl.delete;
l_out_eam_wo_tbl.delete;
l_out_eam_wo_relations_tbl.delete;
l_out_eam_op_tbl.delete;
l_out_eam_op_network_tbl.delete;
l_out_eam_res_tbl.delete;
l_out_eam_res_inst_tbl.delete;
l_out_eam_sub_res_tbl.delete;
l_out_eam_res_usage_tbl.delete;
l_out_eam_mat_req_tbl.delete;
l_out_eam_di_tbl.delete;
l_out_eam_wo_comp_tbl.delete;
l_out_eam_wo_quality_tbl.delete;
l_out_eam_meter_reading_tbl.delete;
l_out_eam_counter_prop_tbl.delete;
l_out_eam_wo_comp_rebuild_tbl.delete;
l_out_eam_wo_comp_mr_read_tbl.delete;
l_out_eam_op_comp_tbl.delete;
l_out_eam_request_tbl.delete;
l_eam_op_tbl_head.delete;
l_eam_op_network_tbl_head.delete;
l_eam_res_tbl_head.delete;
l_eam_res_inst_tbl_head.delete;
l_eam_sub_res_tbl_head.delete;
l_eam_mat_req_tbl_head.delete;
l_eam_di_tbl_head.delete;
l_eam_res_usage_tbl_head.delete;
l_eam_wo_comp_tbl_head.delete;
l_eam_wo_quality_tbl_head.delete;
l_eam_meter_reading_tbl_head.delete;
l_eam_counter_prop_tbl_head.delete;
l_eam_wo_comp_rebuild_tbl_head.delete;
l_eam_wo_comp_mr_read_tbl_head.delete;
l_eam_op_comp_tbl_head.delete;
l_eam_request_tbl_head.delete;
l_mesg_token_tbl.delete;
l_token_tbl.delete;
EAM_ERROR_MESSAGE_PVT.Translate_And_Insert_Messages
( p_mesg_token_tbl => l_mesg_token_tbl
, p_error_level => EAM_ERROR_MESSAGE_PVT.G_WO_LEVEL
, p_entity_index => l_eam_wo_rec_head.row_id
, p_application_id => 'EAM'
);
fnd_msg_pub.delete_msg(p_msg_index => fnd_msg_pub.Count_Msg);
EAM_ERROR_MESSAGE_PVT.Delete_Message;
--Before inserting the error message, insert the confirmation message of work order creation.
x_return_status := l_return_status;
l_eam_wo_list.delete;
--Insert the error message.
select wip_entity_name into l_wo_name
from wip_entities
where wip_entity_id = l_temp_wip_entity_id;
EAM_PROCESS_WO_PUB.G_OPR_DELETE then
if l_eam_wo_relations_tbl(i).parent_relationship_type in (1,3,4) then
SAVEPOINT Delink_Relation_Start;
SAVEPOINT Delete_Dependency_Start;
IF ( EAM_PROCESS_WO_PVT.Get_Debug = 'Y') THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_PUB.PROCESS_MASTER_CHILD_WO : Calling the Delete_Dependency procedure ... ') ; END IF ;
EAM_WO_NETWORK_DEFAULT_PVT.Delete_Dependency
(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_prior_object_id => l_eam_wo_relations_tbl(i).parent_object_id,
p_prior_object_type_id => l_eam_wo_relations_tbl(i).parent_object_type_id,
p_next_object_id => l_eam_wo_relations_tbl(i).child_object_id,
p_next_object_type_id => l_eam_wo_relations_tbl(i).child_object_type_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_mesg_token_tbl => l_mesg_token_tbl
);
rollback to Delete_Dependency_Start;
x_eam_wo_tbl.delete;
x_eam_wo_relations_tbl.delete;
x_eam_op_tbl.delete;
x_eam_op_network_tbl.delete;
x_eam_res_tbl.delete;
x_eam_res_inst_tbl.delete;
x_eam_sub_res_tbl.delete;
x_eam_mat_req_tbl.delete;
x_eam_direct_items_tbl.delete;
x_eam_res_usage_tbl.delete;
x_eam_res_usage_tbl.delete;
x_eam_wo_comp_tbl.delete;
x_eam_wo_quality_tbl.delete;
x_eam_meter_reading_tbl.delete;
x_eam_counter_prop_tbl.delete;
x_eam_wo_comp_rebuild_tbl.delete;
x_eam_wo_comp_mr_read_tbl.delete;
x_eam_op_comp_tbl.delete;
x_eam_request_tbl.delete;
SELECT PENDING_FLAG INTO l_pending_flag
FROM EAM_WORK_ORDER_DETAILS
WHERE
WIP_ENTITY_ID=l_eam_wo_rec.wip_entity_id;
IF l_pending_flag IS NOT NULL AND l_pending_flag='Y' AND l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_OPR_UPDATE THEN
FND_MESSAGE.SET_NAME('EAM', 'EAM_UNAUTHORISED_UPDATE');
IF l_pending_flag IS NOT NULL AND l_pending_flag='Y' AND l_eam_wo_rec.transaction_type=EAM_PROCESS_WO_PUB.G_WF_OPR_UPDATE THEN
l_eam_wo_rec.transaction_type:=EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
EAM_ERROR_MESSAGE_PVT.write_debug(to_char(sysdate,'DD-MON-YY HH:MI:SS')||' EAM_PROCESS_WO_PUB.PROCESS_WO : Transaction Type : '||l_eam_wo_rec.TRANSACTION_TYPE||' (1:Create / 2:Update / 3:Delete / 4:Complete / 5:UnComplete)');
SELECT ORGANIZATION_ID INTO l_organization_id from wip_entities where wip_entity_id = l_wip_entity_id;
( p_validation_level => EAM_PROCESS_WO_PVT.G_OPR_UPDATE
, p_organization_id => p_eam_wo_rec.organization_id
, p_permit_wo_association_tbl => l_permit_wo_association_tbl
, p_work_permit_id => l_permit_wo_association_tbl(1).SOURCE_ID
, x_permit_wo_association_tbl => lx_permit_wo_association_tbl
, x_mesg_token_tbl => l_out_Mesg_Token_Tbl
, x_return_status => l_return_status
);
select
wip_entity_id ,
organization_id ,
operation_seq_num ,
standard_operation_id ,
department_id ,
operation_sequence_id ,
description ,
minimum_transfer_quantity ,
count_point_type ,
backflush_flag ,
shutdown_type ,
first_unit_start_date ,
last_unit_completion_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
long_description
from wip_operations
where wip_entity_id = p_wip_entity_id;
select
wor.wip_entity_id ,
wor.organization_id ,
wor.operation_seq_num ,
wor.resource_seq_num ,
wor.resource_id ,
wor.uom_code ,
wor.basis_type ,
wor.usage_rate_or_amount ,
wor.activity_id ,
wor.scheduled_flag ,
wor.firm_flag ,
wor.assigned_units ,
wor.maximum_assigned_units ,
wor.autocharge_type ,
wor.standard_rate_flag ,
wor.applied_resource_units ,
wor.applied_resource_value ,
wor.start_date ,
wor.completion_date ,
wor.schedule_seq_num ,
wor.substitute_group_num ,
wor.replacement_group_num ,
wor.attribute_category ,
wor.attribute1 ,
wor.attribute2 ,
wor.attribute3 ,
wor.attribute4 ,
wor.attribute5 ,
wor.attribute6 ,
wor.attribute7 ,
wor.attribute8 ,
wor.attribute9 ,
wor.attribute10 ,
wor.attribute11 ,
wor.attribute12 ,
wor.attribute13 ,
wor.attribute14 ,
wor.attribute15 ,
wor.department_id
from wip_operations wo,
wip_operation_resources wor
where wo.wip_entity_id = p_wip_entity_id
and wor.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = wor.operation_seq_num;
select
wori.wip_entity_id ,
wori.organization_id ,
wori.operation_seq_num ,
wori.resource_seq_num ,
wori.instance_id ,
wori.serial_number ,
wori.start_date ,
wori.completion_date ,
wori.batch_id
from wip_op_resource_instances wori,
wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = wor.operation_seq_num
and wori.resource_seq_num = wor.resource_seq_num;
SELECT
wip_entity_id ,
organization_id ,
prior_operation ,
next_operation ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM
wip_operation_networks
WHERE wip_entity_id = p_wip_entity_id;
SELECT
wip_entity_id ,
organization_id ,
operation_seq_num ,
inventory_item_id ,
quantity_per_assembly ,
department_id ,
wip_supply_type ,
date_required ,
required_quantity ,
released_quantity ,
quantity_issued ,
supply_subinventory ,
supply_locator_id ,
mrp_net_flag ,
mps_required_quantity ,
mps_date_required ,
component_sequence_id ,
comments ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15 ,
auto_request_material ,
suggested_vendor_name ,
vendor_id ,
unit_price
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = p_wip_entity_id
AND wro.organization_id = p_organization_id;
SELECT
description ,
purchasing_category_id ,
direct_item_sequence_id ,
operation_seq_num ,
department_id ,
wip_entity_id ,
organization_id ,
suggested_vendor_name ,
suggested_vendor_id ,
suggested_vendor_site ,
suggested_vendor_site_id ,
suggested_vendor_contact ,
suggested_vendor_contact_id ,
suggested_vendor_phone ,
suggested_vendor_item_num ,
unit_price ,
auto_request_material ,
required_quantity ,
uom ,
need_by_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
FROM wip_eam_direct_items wedi
WHERE wedi.wip_entity_id = p_wip_entity_id
AND wedi.organization_id = p_organization_id;
update wip_discrete_jobs
set primary_item_id = l_old_activity_id
where wip_entity_id = l_out_eam_wo_rec.wip_entity_id;
update wip_entities
set primary_item_id = l_old_activity_id
where wip_entity_id = l_out_eam_wo_rec.wip_entity_id; /*added for bug 9974953*/
update wip_operations wo
set STANDARD_OPERATION_ID = ( select STANDARD_OPERATION_ID from wip_operations wo1
where wo.OPERATION_SEQ_NUM = wo1.OPERATION_SEQ_NUM
and wo1.wip_entity_id = p_wip_entity_id
)
where wip_entity_id = l_out_eam_wo_rec.wip_entity_id;
PROCEDURE UPDATE_WO_ADD_DES_DIR_ITEM
( p_wip_entity_id IN NUMBER
, p_operation_seq_num IN NUMBER
, p_inventory_item_id IN NUMBER
, p_description IN VARCHAR2
, p_organization_id IN NUMBER
, p_purchasing_category_id IN NUMBER
, p_suggested_vendor_name IN VARCHAR2 := NULL
, p_suggested_vendor_id IN NUMBER := NULL
, p_suggested_vendor_site IN VARCHAR2 := NULL
, p_suggested_vendor_site_id IN NUMBER := NULL
, p_suggested_vendor_contact IN VARCHAR2 := NULL
, p_suggested_vendor_contact_id IN NUMBER := NULL
, p_suggested_vendor_phone IN VARCHAR2 := NULL
, p_suggested_vendor_item_num IN VARCHAR2 := NULL
, p_required_quantity IN NUMBER
, p_unit_price IN NUMBER
, p_uom IN VARCHAR2
, p_need_by_date IN DATE
, p_amount IN NUMBER
, p_order_type_lookup_code IN VARCHAR2
, x_direct_item_sequence_id IN OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
l_direct_item_sequence_id NUMBER := NULL;
select sum(count) into l_count from
(
select count(*) as count
from wip_eam_direct_items wed
where wed.wip_entity_id = p_wip_entity_id and
wed.operation_seq_num = p_operation_seq_num and
wed.direct_item_sequence_id = x_direct_item_sequence_id and
wed.organization_id = p_organization_id
union all
SELECT count(*) as count
FROM
po_requisition_lines_all pr,
wip_entities we
WHERE
pr.destination_organization_id = p_organization_id
AND pr.item_id is null
AND pr.wip_resource_seq_num = x_direct_item_sequence_id
AND pr.destination_type_code = 'SHOP FLOOR'
AND pr.wip_entity_id = we.wip_entity_id
AND pr.wip_operation_seq_num = p_operation_seq_num
AND pr.wip_entity_id = p_wip_entity_id
AND we.entity_type in (6,7)
union all
SELECT count(*) as count
FROM
po_distributions_all pd,
po_lines_all pl,
wip_entities we
WHERE
pd.destination_organization_id = p_organization_id
AND pd.po_line_id = pl.po_line_id(+)
AND pl.item_id is null
AND pd.wip_resource_seq_num = x_direct_item_sequence_id
AND pd.destination_type_code = 'SHOP FLOOR'
AND pd.wip_entity_id = we.wip_entity_id
AND pd.wip_operation_seq_num = p_operation_seq_num
AND pd.wip_entity_id = p_wip_entity_id
AND we.entity_type in (6,7)
) ;
select count(*) into l_count from wip_eam_direct_items wed where
wed.wip_entity_id = p_wip_entity_id and
wed.operation_seq_num = p_operation_seq_num and
wed.description = p_description and
wed.organization_id = p_organization_id;
select max(direct_item_sequence_id) into x_direct_item_sequence_id
from wip_eam_direct_items wed where
wed.wip_entity_id = p_wip_entity_id and
wed.operation_seq_num = p_operation_seq_num and
wed.description = p_description and
wed.organization_id = p_organization_id;
select wip_eam_di_seq_id_s.nextval into l_direct_item_sequence_id from dual;
SELECT department_id INTO l_department_id
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id AND
operation_seq_num = p_operation_seq_num AND
organization_id = p_organization_id;
select distinct uom_code into l_uom_code from mtl_uom_conversions muc where muc.unit_of_measure = p_uom;
INSERT INTO WIP_EAM_DIRECT_ITEMS
(
DESCRIPTION
, PURCHASING_CATEGORY_ID
, DIRECT_ITEM_SEQUENCE_ID
, OPERATION_SEQ_NUM
, DEPARTMENT_ID
, WIP_ENTITY_ID
, ORGANIZATION_ID
, SUGGESTED_VENDOR_NAME
, SUGGESTED_VENDOR_ID
, SUGGESTED_VENDOR_SITE
, SUGGESTED_VENDOR_SITE_ID
, SUGGESTED_VENDOR_CONTACT
, SUGGESTED_VENDOR_CONTACT_ID
, SUGGESTED_VENDOR_PHONE
, SUGGESTED_VENDOR_ITEM_NUM
, UNIT_PRICE
, AUTO_REQUEST_MATERIAL
, REQUIRED_QUANTITY
, UOM
, NEED_BY_DATE
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, AMOUNT
, ORDER_TYPE_LOOKUP_CODE)
VALUES
(
p_description
, p_purchasing_category_id
, l_direct_item_sequence_id
, p_operation_seq_num
, l_department_id
, p_wip_entity_id
, p_organization_id
, p_suggested_vendor_name
, p_suggested_vendor_id
, p_suggested_vendor_site
, p_suggested_vendor_site_id
, p_suggested_vendor_contact
, p_suggested_vendor_contact_id
, p_suggested_vendor_phone
, p_suggested_vendor_item_num
, p_unit_price
, 'Y'
, p_required_quantity
, l_uom_code
, p_need_by_date
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, p_amount
, p_order_type_lookup_code);
END UPDATE_WO_ADD_DES_DIR_ITEM;