[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Wrapper procedure on top of WO API.This is used to create/update workorder and its related entities
************************************************/
PROCEDURE CREATE_UPDATE_WO
(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_eam_wo_tbl IN EAM_PROCESS_WO_PUB.eam_wo_tbl_type,
p_eam_wo_relations_tbl IN EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type,
p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type,
p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type,
p_eam_mat_req_tbl IN EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type,
p_eam_direct_items_tbl IN EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type,
p_eam_request_tbl IN EAM_PROCESS_WO_PUB.eam_request_tbl_type,
p_eam_wo_comp_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type,
p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type,
p_eam_counter_prop_tbl IN EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type,
p_eam_wo_comp_rebuild_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type,
p_eam_wo_comp_mr_read_tbl IN EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type,
p_prev_activity_id IN NUMBER,
p_failure_id IN NUMBER := null,
p_failure_date IN DATE := null,
p_failure_entry_id IN NUMBER := null,
p_failure_code IN VARCHAR2 := null,
p_cause_code IN VARCHAR2 := null,
p_resolution_code IN VARCHAR2 := null,
p_failure_comments IN VARCHAR2 := null,
p_failure_code_required IN VARCHAR2 DEFAULT NULL,
x_wip_entity_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
IS
l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
SELECT autocharge_type
FROM BOM_RESOURCES
WHERE resource_id = l_resource_id
AND organization_id = l_organization_id;
SAVEPOINT create_update_wo;
SELECT
maintenance_object_type
,maintenance_object_id
INTO
l_maintenance_object_type
,l_maintenance_object_id
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id = l_eam_wo_comp_rec.wip_entity_id;
SELECT OWNING_DEPARTMENT_ID
INTO l_fail_dept_id
FROM eam_org_maint_defaults
WHERE object_id =l_maintenance_object_id
AND object_type = 50
AND organization_id =l_eam_wo_comp_rec.organization_id;
SELECT area_id
INTO l_eam_location_id
FROM eam_org_maint_defaults
WHERE object_id = l_maintenance_object_id
AND object_type = 50
AND organization_id = l_eam_wo_comp_rec.organization_id;
l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl.delete;
SELECT asset_number
,asset_group_id
,rebuild_serial_number
,rebuild_item_id
,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
/* Added for bug#6053425 Start */
,project_id
,task_id
/* Added for bug#6053425 End */
INTO l_asset_number
,l_asset_group_id
,l_rebuild_serial_number
,l_rebuild_item_id
,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
/* Code Added for bug#6053425 Start */
,l_prev_project_id
,l_prev_task_id
/* Code Added for bug#6053425 End */
FROM WIP_DISCRETE_JOBS
where wip_entity_id = l_wip_entity_id;
SELECT OWNING_DEPARTMENT_ID
INTO l_fail_dept_id
FROM eam_org_maint_defaults
WHERE object_id =l_maintenance_object_id
AND object_type = 50
AND organization_id =l_eam_wo_comp_rec.organization_id;
SELECT area_id
INTO l_eam_location_id
FROM eam_org_maint_defaults
WHERE object_id = l_maintenance_object_id
AND object_type = 50
AND organization_id = l_eam_wo_comp_rec.organization_id;
l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_entry_record.transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl(1).transaction_type := EAM_Process_Failure_Entry_PUB.G_FE_UPDATE;
l_eam_failure_codes_tbl.delete;
l_eam_failure_codes_tbl.delete;
, p_debug_filename => 'createupdatewo.log'
, p_debug_file_mode => 'W'
);
, p_debug_filename => 'createupdatewo.log'
, p_debug_file_mode => 'W'
);
ROLLBACK TO create_update_wo;
END CREATE_UPDATE_WO;
SELECT ROUND(NVL(wor.usage_rate_or_amount * (1/l_uom_conv) *
(DECODE (con.conversion_rate,'', 0, '0', 0, con.conversion_rate)),0),2) required_hours,
(SELECT ROUND(NVL(SUM((woru.completion_date-woru.start_date)*24) ,0),2)
FROM WIP_OPERATION_RESOURCE_USAGE woru
WHERE woru.wip_entity_id = wor.wip_entity_id
AND woru.organization_id = wor.organization_id
AND woru.operation_seq_num = wor.operation_seq_num
AND woru.resource_seq_num = wor.resource_seq_num
AND woru.instance_id IS NOT NULL
AND woru.serial_number IS NULL) assigned_hours
FROM WIP_OPERATION_RESOURCES wor,MTL_UOM_CONVERSIONS con,BOM_RESOURCES br
WHERE wor.wip_entity_id = p_wip_entity_id
AND wor.resource_id = br.resource_id
AND br.resource_type = 2 --for person type resources only
AND con.uom_code = wor. uom_code
AND NVL(con.disable_date, l_next_date) > l_sysdate
AND con.inventory_item_id = l_inv_item_id;
SELECT CON.CONVERSION_RATE
INTO l_uom_conv
FROM MTL_UOM_CONVERSIONS CON
WHERE CON.UOM_CODE = l_hour_uom
AND NVL(DISABLE_DATE, l_next_date) > l_sysdate
AND CON.INVENTORY_ITEM_ID = l_inv_item_id;