The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Genealogy( X_wip_entity_id IN NUMBER,
X_organization_id IN NUMBER,
X_parent_wip_entity_id IN NUMBER,
X_rebuild_item_id IN NUMBER,
X_rebuild_serial_number IN VARCHAR2,
X_manual_rebuild_flag IN VARCHAR2,
x_maintenance_object_type IN NUMBER,
x_maintenance_object_id IN Number ) IS
l_serial_status NUMBER;
/* IB Component of Configuration should be updated as well */
if ((X_parent_wip_entity_id is not null) and (X_manual_rebuild_flag= 'N')) then
Begin
wip_eam_genealogy_pvt.update_eam_genealogy(
p_api_version => 1.0,
p_object_type => 2, -- serial number
p_serial_number => X_rebuild_serial_number,
p_inventory_item_id => X_rebuild_item_id,
p_organization_id => X_Organization_Id,
p_genealogy_type => 5, -- asset item relationship
p_end_date_active => sysdate,
x_return_status => x_returnStatus,
x_msg_count => l_msgCount,
x_msg_data => l_msgData);
select status_type
into l_status
from wip_discrete_jobs
where wip_entity_id = X_parent_wip_entity_id
and organization_id = X_organization_id;
FND_MESSAGE.SET_NAME('EAM','EAM_UPDATE_GENEALOGY_FAIL');
end if; -- if clause to update the genealogy of the asset
END Update_Genealogy;
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_login NUMBER,
X_description IN OUT NOCOPY VARCHAR2,
X_status_type IN OUT NOCOPY NUMBER,
X_user_defined_status_id IN OUT NOCOPY NUMBER,
X_pending_flag IN OUT NOCOPY VARCHAR2,
X_workflow_type IN OUT NOCOPY NUMBER ,
X_warranty_claim_status IN OUT NOCOPY NUMBER,
X_material_shortage_flag IN OUT NOCOPY NUMBER,
X_material_shortage_check_date IN OUT NOCOPY DATE,
X_primary_item_id NUMBER,
X_parent_wip_entity_id NUMBER,
X_asset_number VARCHAR2,
X_asset_group_id NUMBER,
X_pm_schedule_id NUMBER,
X_rebuild_item_id NUMBER,
X_rebuild_serial_number VARCHAR2,
X_manual_rebuild_flag IN OUT NOCOPY VARCHAR2,
X_shutdown_type IN OUT NOCOPY VARCHAR2,
X_tagout_required IN OUT NOCOPY VARCHAR2,
X_plan_maintenance IN OUT NOCOPY VARCHAR2,
X_estimation_status VARCHAR2,
X_requested_start_date IN OUT NOCOPY DATE,
X_requested_due_date IN OUT NOCOPY DATE,
X_notification_required IN OUT NOCOPY VARCHAR2,
X_work_order_type IN OUT NOCOPY VARCHAR2,
X_owning_department IN OUT NOCOPY NUMBER,
X_planner_maintenance IN OUT NOCOPY NUMBER,
X_activity_type IN OUT NOCOPY VARCHAR2,
X_activity_cause IN OUT NOCOPY VARCHAR2,
X_firm_planned_flag IN OUT NOCOPY NUMBER,
X_job_type NUMBER:= 3,
X_wip_supply_type NUMBER := 7,
X_class_code IN OUT NOCOPY VARCHAR2,
X_material_account IN OUT NOCOPY NUMBER,
X_material_overhead_account IN OUT NOCOPY NUMBER,
X_resource_account IN OUT NOCOPY NUMBER,
X_outside_processing_account IN OUT NOCOPY NUMBER,
X_material_variance_account IN OUT NOCOPY NUMBER,
X_resource_variance_account IN OUT NOCOPY NUMBER,
X_outside_proc_var_account IN OUT NOCOPY NUMBER,
X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
X_overhead_account IN OUT NOCOPY NUMBER,
X_overhead_variance_account IN OUT NOCOPY NUMBER,
X_scheduled_start_date IN OUT NOCOPY DATE,
X_date_released DATE,
X_scheduled_completion_date IN OUT NOCOPY DATE,
X_date_completed DATE,
X_date_closed DATE,
X_start_quantity NUMBER := 1,
X_overcompletion_toleran_type NUMBER := null,
X_overcompletion_toleran_value NUMBER := null,
X_quantity_completed NUMBER := 0,
X_quantity_scrapped NUMBER := 0,
X_net_quantity NUMBER := 1,
X_bom_reference_id NUMBER,
X_routing_reference_id NUMBER,
X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
X_bom_revision IN OUT NOCOPY VARCHAR2,
X_routing_revision IN OUT NOCOPY VARCHAR2,
X_bom_revision_date IN OUT NOCOPY DATE,
X_routing_revision_date IN OUT NOCOPY DATE,
X_lot_number VARCHAR2,
X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
X_completion_subinventory VARCHAR2,
X_completion_locator_id NUMBER,
X_demand_class VARCHAR2,
X_attribute_category IN OUT NOCOPY VARCHAR2,
X_attribute1 IN OUT NOCOPY VARCHAR2,
X_attribute2 IN OUT NOCOPY VARCHAR2,
X_attribute3 IN OUT NOCOPY VARCHAR2,
X_attribute4 IN OUT NOCOPY VARCHAR2,
X_attribute5 IN OUT NOCOPY VARCHAR2,
X_attribute6 IN OUT NOCOPY VARCHAR2,
X_attribute7 IN OUT NOCOPY VARCHAR2,
X_attribute8 IN OUT NOCOPY VARCHAR2,
X_attribute9 IN OUT NOCOPY VARCHAR2,
X_attribute10 IN OUT NOCOPY VARCHAR2,
X_attribute11 IN OUT NOCOPY VARCHAR2,
X_attribute12 IN OUT NOCOPY VARCHAR2,
X_attribute13 IN OUT NOCOPY VARCHAR2,
X_attribute14 IN OUT NOCOPY VARCHAR2,
X_attribute15 IN OUT NOCOPY VARCHAR2,
X_We_Rowid IN OUT NOCOPY VARCHAR2,
X_Entity_Type NUMBER,
X_Wip_Entity_Name VARCHAR2,
X_Schedule_Group_Id NUMBER default null,
X_Build_Sequence NUMBER default null,
X_Line_Id NUMBER default null,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_end_item_unit_number VARCHAR2 default null,
X_po_creation_time NUMBER default 1,
X_priority IN OUT NOCOPY NUMBER ,
X_due_date DATE default null,
X_maintenance_object_id NUMBER,
X_maintenance_object_source NUMBER,
X_maintenance_object_type NUMBER,
X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
X_activity_source IN OUT NOCOPY VARCHAR2,
X_Parent_Wip_Name VARCHAR2 := null,
X_Relationship_Type NUMBER := null,
X_Relation_Status IN OUT NOCOPY VARCHAR2,
x_failure_code_required IN OUT NOCOPY VARCHAR2,
x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ ,
x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ
) IS
-- Bug # 2251186
l_errbuf VARCHAR2(1000) ;
l_workorder_rec.eam_failure_codes_tbl.delete(i);
select rowid into X_We_Rowid from WIP_ENTITIES where wip_entity_id=X_wip_entity_id;
select rowid into X_Rowid from WIP_DISCRETE_JOBS where wip_entity_id=X_wip_entity_id;
select wip_entity_id
into temp
from wip_entities
where wip_entity_name = X_Parent_Wip_Name AND organization_id=X_organization_id ;
Update_Genealogy( X_wip_entity_id => X_wip_entity_id,
X_organization_id =>X_Organization_id,
X_parent_wip_entity_id =>X_parent_wip_entity_id,
X_rebuild_item_id => X_rebuild_item_id,
X_rebuild_serial_number =>X_rebuild_serial_number,
X_manual_rebuild_flag =>X_manual_rebuild_flag,
x_maintenance_object_type => x_maintenance_object_type ,
x_maintenance_object_id => x_maintenance_object_id);
/*cboppana ----Added this function to update a work order.This calls the Work Order api */
PROCEDURE Update_Workorder( X_Rowid VARCHAR2,
X_wip_entity_id NUMBER,
X_organization_id NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_login NUMBER,
X_description IN OUT NOCOPY VARCHAR2,
X_status_type IN OUT NOCOPY NUMBER,
X_user_defined_status_id IN OUT NOCOPY NUMBER,
X_pending_flag IN OUT NOCOPY VARCHAR2,
X_workflow_type IN OUT NOCOPY NUMBER ,
X_warranty_claim_status IN OUT NOCOPY NUMBER,
X_material_shortage_flag IN OUT NOCOPY NUMBER,
X_material_shortage_check_date IN OUT NOCOPY DATE,
X_primary_item_id NUMBER,
X_parent_wip_entity_id NUMBER,
X_asset_number VARCHAR2,
X_asset_group_id NUMBER,
X_pm_schedule_id NUMBER,
X_rebuild_item_id NUMBER,
X_rebuild_serial_number VARCHAR2,
X_manual_rebuild_flag IN OUT NOCOPY VARCHAR2,
X_shutdown_type IN OUT NOCOPY VARCHAR2,
X_tagout_required IN OUT NOCOPY VARCHAR2,
X_plan_maintenance IN OUT NOCOPY VARCHAR2,
X_estimation_status VARCHAR2,
X_requested_start_date IN OUT NOCOPY DATE,
X_requested_due_date IN OUT NOCOPY DATE,
X_notification_required IN OUT NOCOPY VARCHAR2,
X_work_order_type IN OUT NOCOPY VARCHAR2,
X_owning_department IN OUT NOCOPY NUMBER,
X_planner_maintenance IN OUT NOCOPY NUMBER,
X_activity_type IN OUT NOCOPY VARCHAR2,
X_activity_cause IN OUT NOCOPY VARCHAR2,
X_firm_planned_flag IN OUT NOCOPY NUMBER,
X_class_code IN OUT NOCOPY VARCHAR2,
X_material_account IN OUT NOCOPY NUMBER,
X_material_overhead_account IN OUT NOCOPY NUMBER,
X_resource_account IN OUT NOCOPY NUMBER,
X_outside_processing_account IN OUT NOCOPY NUMBER,
X_material_variance_account IN OUT NOCOPY NUMBER,
X_resource_variance_account IN OUT NOCOPY NUMBER,
X_outside_proc_var_account IN OUT NOCOPY NUMBER,
X_std_cost_adjustment_account IN OUT NOCOPY NUMBER,
X_overhead_account IN OUT NOCOPY NUMBER,
X_overhead_variance_account IN OUT NOCOPY NUMBER,
X_scheduled_start_date IN OUT NOCOPY DATE,
X_date_released DATE,
X_scheduled_completion_date IN OUT NOCOPY DATE,
X_date_completed DATE,
X_date_closed DATE,
X_bom_reference_id NUMBER,
X_routing_reference_id NUMBER,
X_common_bom_sequence_id IN OUT NOCOPY NUMBER,
X_common_routing_sequence_id IN OUT NOCOPY NUMBER,
X_bom_revision IN OUT NOCOPY VARCHAR2,
X_routing_revision IN OUT NOCOPY VARCHAR2,
X_bom_revision_date IN OUT NOCOPY DATE,
X_routing_revision_date IN OUT NOCOPY DATE,
X_lot_number VARCHAR2,
X_alternate_bom_designator IN OUT NOCOPY VARCHAR2,
X_alternate_routing_designator IN OUT NOCOPY VARCHAR2,
X_completion_subinventory VARCHAR2,
X_completion_locator_id NUMBER,
X_demand_class VARCHAR2,
X_attribute_category IN OUT NOCOPY VARCHAR2,
X_attribute1 IN OUT NOCOPY VARCHAR2,
X_attribute2 IN OUT NOCOPY VARCHAR2,
X_attribute3 IN OUT NOCOPY VARCHAR2,
X_attribute4 IN OUT NOCOPY VARCHAR2,
X_attribute5 IN OUT NOCOPY VARCHAR2,
X_attribute6 IN OUT NOCOPY VARCHAR2,
X_attribute7 IN OUT NOCOPY VARCHAR2,
X_attribute8 IN OUT NOCOPY VARCHAR2,
X_attribute9 IN OUT NOCOPY VARCHAR2,
X_attribute10 IN OUT NOCOPY VARCHAR2,
X_attribute11 IN OUT NOCOPY VARCHAR2,
X_attribute12 IN OUT NOCOPY VARCHAR2,
X_attribute13 IN OUT NOCOPY VARCHAR2,
X_attribute14 IN OUT NOCOPY VARCHAR2,
X_attribute15 IN OUT NOCOPY VARCHAR2,
X_We_Rowid IN OUT NOCOPY VARCHAR2,
X_Entity_Type NUMBER,
X_Wip_Entity_Name VARCHAR2,
X_Update_Wip_Entities VARCHAR2,
X_Schedule_Group_Id NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_priority IN OUT NOCOPY NUMBER,
X_maintenance_object_id NUMBER,
X_maintenance_object_source NUMBER,
X_maintenance_object_type NUMBER,
X_material_issue_by_mo IN OUT NOCOPY VARCHAR2,
X_activity_source IN OUT NOCOPY VARCHAR2,
X_old_rebuild_source NUMBER := NULL,
x_failure_code_required IN OUT NOCOPY VARCHAR2,
x_eam_failure_entry_record IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_entry_record_typ,
x_eam_failure_codes_tbl IN OUT NOCOPY eam_process_failure_entry_pub.eam_failure_codes_tbl_typ,
x_return_status IN OUT NOCOPY VARCHAR2 /*7003588*/
) IS
dummy NUMBER;
l_workorder_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
select primary_item_id
,description
,priority
,work_order_type
,shutdown_type
,activity_type
,activity_cause
,activity_source
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,project_id -- added for bug 5346446
,task_id
into l_prev_activity_id
,l_prev_description
,l_prev_priority
,l_prev_work_order_type
,l_prev_shutdown_type
,l_prev_activity_type
,l_prev_activity_cause
,l_prev_activity_source
,l_prev_attribute_category
,l_prev_attribute1
,l_prev_attribute2
,l_prev_attribute3
,l_prev_attribute4
,l_prev_attribute5
,l_prev_attribute6
,l_prev_attribute7
,l_prev_attribute8
,l_prev_attribute9
,l_prev_attribute10
,l_prev_attribute11
,l_prev_attribute12
,l_prev_attribute13
,l_prev_attribute14
,l_prev_attribute15
,l_prev_project_id --added for bug 5346446
,l_prev_task_id
from wip_discrete_jobs
where wip_entity_id =X_wip_Entity_Id
and organization_id = X_organization_id;
l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl(i).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_workorder_rec.eam_failure_codes_tbl.delete(i);
SAVEPOINT UPDATE_WO;
select parent_wip_entity_id
into l_old_rebuild_source
from wip_discrete_jobs
where wip_entity_id=X_wip_Entity_Id;
select count(*)
into constraining_rel
from eam_wo_relationships
where parent_object_id=l_old_rebuild_source
and child_object_id=X_wip_Entity_Id
and parent_relationship_type=1;
l_eam_wo_relations_rec1.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
select count(*)
into followup_rel
from eam_wo_relationships
where parent_object_id=l_old_rebuild_source
and child_object_id=X_wip_Entity_Id
and parent_relationship_type=4;
l_eam_wo_relations_rec2.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
, p_debug_filename => 'updatewo.log'
, p_output_dir => l_output_dir
, p_commit => 'N'
, p_debug_file_mode => 'w'
);
ROLLBACK TO UPDATE_WO;
fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
select rowid into X_We_Rowid from WIP_ENTITIES where wip_entity_id=X_wip_entity_id;
ROLLBACK TO UPDATE_WO;
fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
Update_Genealogy( X_wip_entity_id => X_Wip_Entity_Id,
X_organization_id =>X_Organization_Id,
X_parent_wip_entity_id =>X_parent_wip_entity_id,
X_rebuild_item_id => X_rebuild_item_id,
X_rebuild_serial_number=>X_rebuild_serial_number,
X_manual_rebuild_flag =>X_manual_rebuild_flag,
x_maintenance_object_type => x_maintenance_object_type ,
x_maintenance_object_id => x_maintenance_object_id);
END Update_Workorder;
X_Update_Wip_Entities VARCHAR2,
X_Schedule_Group_Id NUMBER,
X_Project_Id NUMBER,
X_Task_Id NUMBER,
X_priority NUMBER,
X_maintenance_object_id NUMBER,
X_maintenance_object_source NUMBER,
X_maintenance_object_type NUMBER,
X_material_issue_by_mo VARCHAR2,
X_activity_source VARCHAR2) IS
CURSOR C_WDJ IS
SELECT *
FROM WIP_DISCRETE_JOBS
WHERE rowid = X_Rowid
FOR UPDATE of Wip_Entity_Id NOWAIT;
SELECT *
FROM WIP_ENTITIES
WHERE rowid = X_We_Rowid
FOR UPDATE of Wip_Entity_Id NOWAIT;
SELECT *
FROM eam_work_order_details
WHERE wip_entity_id = X_wip_entity_id
FOR UPDATE of Wip_Entity_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');