[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(operation_completed) into l_op_complete_count
FROM wip_operation_networks won, wip_operations wo
WHERE won.wip_entity_id = p_wip_entity_id
AND won.next_operation = p_operation_sequence_number
AND won.organization_id = p_organization_id
AND wo.wip_entity_id = p_wip_entity_id
AND wo.operation_seq_num = won.prior_operation
AND wo.organization_id = p_organization_id
AND nvl(wo.operation_completed,'N')='N' ;
select operation_completed
into l_complete_yn
from wip_operations where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_operation_sequence_number and
organization_id = p_organization_id;
select operation_completed
into l_cur_completed_yn
from wip_operations where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_operation_sequence_number and
organization_id = p_organization_id;
FOR cur_operation_record IN (select next_operation from wip_operation_networks where
wip_entity_id = p_wip_entity_id and
prior_operation = p_operation_sequence_number and
organization_id = p_organization_id) LOOP
SELECT operation_completed INTO
l_completed_yn from wip_operations where
wip_entity_id = p_wip_entity_id and
operation_seq_num = cur_operation_record.next_operation and
organization_id = p_organization_id;
FOR cur_operation_record IN (select prior_operation from wip_operation_networks where
wip_entity_id = p_wip_entity_id and
next_operation = p_operation_sequence_number and
organization_id = p_organization_id) LOOP
SELECT operation_completed INTO
l_completed_yn from wip_operations where
wip_entity_id = p_wip_entity_id and
operation_seq_num = cur_operation_record.prior_operation and
organization_id = p_organization_id;
,p_stored_last_update_date IN DATE -- old update date, for locking only
,p_qa_collection_id IN NUMBER
,p_vendor_id IN NUMBER := NULL
,p_vendor_site_id IN NUMBER := NULL
,p_vendor_contact_id IN NUMBER := NULL
,p_reason_id IN NUMBER := NULL
,p_reference IN VARCHAR2 := NULL
,p_attribute_category IN VARCHAR2 := NULL
,p_attribute1 IN VARCHAR2 := NULL
,p_attribute2 IN VARCHAR2 := NULL
,p_attribute3 IN VARCHAR2 := NULL
,p_attribute4 IN VARCHAR2 := NULL
,p_attribute5 IN VARCHAR2 := NULL
,p_attribute6 IN VARCHAR2 := NULL
,p_attribute7 IN VARCHAR2 := NULL
,p_attribute8 IN VARCHAR2 := NULL
,p_attribute9 IN VARCHAR2 := NULL
,p_attribute10 IN VARCHAR2 := NULL
,p_attribute11 IN VARCHAR2 := NULL
,p_attribute12 IN VARCHAR2 := NULL
,p_attribute13 IN VARCHAR2 := NULL
,p_attribute14 IN VARCHAR2 := NULL
,p_attribute15 IN VARCHAR2 := NULL
) IS
l_api_name CONSTANT VARCHAR(30) := 'complete_operation';
l_db_last_update_date DATE;
SELECT last_update_date, operation_completed, shutdown_type
INTO l_db_last_update_date, l_db_status, l_shutdown_type
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
FOR UPDATE;
select actual_start_date ,actual_end_date ,actual_duration
into l_act_st_date ,l_act_end_date,l_act_duration
from eam_op_completion_txns
where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_operation_seq_num and
transaction_type = 1 and
last_update_date = (select max(last_update_date)
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id and
operation_seq_num = p_operation_seq_num and
transaction_type = 1);
IF l_db_last_update_date <> p_stored_last_update_date THEN
eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_STALED_DATA');
select nvl(min(period_start_date), sysdate+1)
into l_open_acct_per_date
from org_acct_periods
where organization_id = (select organization_id from wip_discrete_jobs where wip_entity_id = p_wip_entity_id)
and open_flag = 'Y';
select ml.lookup_code
into l_reconciliation_code
from mfg_lookups ml -- Fix for Bug 3509465
where ml.lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
and ml.meaning = p_reconciliation_code;
,p_stored_last_update_date IN DATE -- old update date, for locking only
,p_duration IN NUMBER
,p_reconciliation_value IN VARCHAR2
) IS
l_api_name CONSTANT VARCHAR(30) := 'operation_handover';
l_db_last_update_date DATE;
SELECT
last_update_date
,operation_completed
,first_unit_start_date
,last_unit_completion_date
,organization_id --
,department_id
INTO
l_db_last_update_date
,l_completed
,l_actual_start_date
,l_actual_end_date
,l_org_id
,l_old_dept_id
FROM wip_operations
WHERE
wip_entity_id = p_wip_entity_id
and operation_seq_num = p_old_op_seq_num
FOR UPDATE;
IF l_db_last_update_date <> nvl(p_stored_last_update_date, l_db_last_update_date) THEN
eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_OP_STALED_DATA');
select count(*)
into l_count from wip_operations
where wip_entity_id = p_wip_entity_id and operation_seq_num = p_new_op_seq_num;
select department_id
into l_department_id
from bom_departments
where organization_id = l_org_id
and department_code like p_assigned_department;
select *
into opRow
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_old_op_seq_num;
,p_stored_last_update_date => p_stored_last_update_date
);
,p_stored_last_update_date => p_stored_last_update_date
);
select max(transaction_id) into l_transaction_id
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_old_op_seq_num;
update eam_op_completion_txns
set handover_operation_seq_num = p_new_op_seq_num
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_old_op_seq_num
and transaction_id = l_transaction_id;
opRow.Last_Update_Date := sysdate;
opRow.Last_Updated_By := g_last_updated_by;
opRow.Last_Update_Login := g_last_update_login;
SELECT prior_operation
,next_operation
,wip_entity_id
,organization_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,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
AND next_operation = p_old_op_seq_num;
SELECT prior_operation
,next_operation
,wip_entity_id
,organization_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,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
AND prior_operation = p_old_op_seq_num;
SELECT
last_unit_completion_date,
operation_completed
INTO
l_actual_end_date,
l_completed
FROM wip_operations
WHERE
wip_entity_id = p_wip_entity_id
AND operation_seq_num = nxtOpCurVar.prior_operation;
nxtOpCurVar.last_updated_by := FND_GLOBAL.user_id;
nxtOpCurVar.last_update_login := FND_GLOBAL.user_id;
nxtOpCurVar.last_update_date := sysdate;
INSERT INTO wip_operation_networks
( prior_operation
,next_operation
,wip_entity_id
,organization_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
) VALUES
( nxtOpCurVar.prior_operation
,p_new_op_seq_num
,nxtOpCurVar.wip_entity_id
,nxtOpCurVar.organization_id
,nxtOpCurVar.created_by
,nxtOpCurVar.creation_date
,nxtOpCurVar.last_updated_by
,nxtOpCurVar.last_update_date
,nxtOpCurVar.last_update_login
,nxtOpCurVar.attribute_category
,nxtOpCurVar.attribute1
,nxtOpCurVar.attribute2
,nxtOpCurVar.attribute3
,nxtOpCurVar.attribute4
,nxtOpCurVar.attribute5
,nxtOpCurVar.attribute6
,nxtOpCurVar.attribute7
,nxtOpCurVar.attribute8
,nxtOpCurVar.attribute9
,nxtOpCurVar.attribute10
,nxtOpCurVar.attribute11
,nxtOpCurVar.attribute12
,nxtOpCurVar.attribute13
,nxtOpCurVar.attribute14
,nxtOpCurVar.attribute15
);
SELECT
operation_completed
,first_unit_start_date
INTO
l_completed
,l_actual_start_date
FROM wip_operations
WHERE
wip_entity_id = p_wip_entity_id
AND operation_seq_num = prvOpCurVar.next_operation;
prvOpCurVar.Last_Updated_By := FND_GLOBAL.user_id;
prvOpCurVar.Last_Update_Login := FND_GLOBAL.user_id;
prvOpCurVar.Last_Update_Date := sysdate;
INSERT INTO wip_operation_networks
( prior_operation
,next_operation
,wip_entity_id
,organization_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
) VALUES
( p_new_op_seq_num
,prvOpCurVar.next_operation
,prvOpCurVar.wip_entity_id
,prvOpCurVar.organization_id
,prvOpCurVar.created_by
,prvOpCurVar.creation_date
,prvOpCurVar.last_updated_by
,prvOpCurVar.last_update_date
,prvOpCurVar.last_update_login
,prvOpCurVar.attribute_category
,prvOpCurVar.attribute1
,prvOpCurVar.attribute2
,prvOpCurVar.attribute3
,prvOpCurVar.attribute4
,prvOpCurVar.attribute5
,prvOpCurVar.attribute6
,prvOpCurVar.attribute7
,prvOpCurVar.attribute8
,prvOpCurVar.attribute9
,prvOpCurVar.attribute10
,prvOpCurVar.attribute11
,prvOpCurVar.attribute12
,prvOpCurVar.attribute13
,prvOpCurVar.attribute14
,prvOpCurVar.attribute15
);
select operation_seq_num
into l_new_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_new_op_seq_num;
select resource_id into l_resource_id
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_old_op_seq_num
and resource_seq_num = p_resource_seq_num;
select bd.department_id into l_dept
from bom_department_resources bdr,bom_departments bd
where bd.department_id = bdr.department_id
and resource_id = l_resource_id
and bd.department_id in (select department_id
from bom_departments
where department_code=p_department);
select *
into curRow
from wip_operation_resources r
where r.wip_entity_id = p_wip_entity_id
and r.operation_seq_num = p_old_op_seq_num
and r.resource_seq_num = p_resource_seq_num;
select quantity_open
into l_quantity_open
from wip_operation_resources_v v
where v.wip_entity_id = curRow.Wip_Entity_Id
and v.operation_seq_num = curRow.Operation_Seq_Num
and v.resource_seq_num = curRow.Resource_Seq_Num;
select department_id
into l_department_id
from bom_departments bd
where bd.department_code like p_department
and bd.organization_id = curRow.Organization_Id
and nvl(bd.disable_date, sysdate) >= sysdate;
select count(*)
into l_num_non_compatible_resources
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_old_op_seq_num
and wor.resource_seq_num = p_resource_seq_num
and wor.resource_id not in (
select bdr.resource_id
from bom_department_resources bdr
where bdr.department_id = l_department_id
);
select start_quantity
into l_start_quantity
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
SELECT 1 INTO l_res_valid
FROM wip_operation_resources
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_new_op_seq_num
AND resource_seq_num = p_resource_seq_num;
SELECT 1 INTO l_inst_valid
FROM wip_op_resource_instances
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = p_new_op_seq_num
AND resource_seq_num = p_resource_seq_num
AND instance_id = p_employee_id;
newRow.Last_Update_Date := sysdate;
newRow.Last_Updated_By := g_last_updated_by;
newRow.Last_Update_Login := g_last_updated_by;
newRow.Created_By := g_last_updated_by;
SELECT full_name
INTO l_employee_name
FROM per_all_people_f papf,bom_resource_employees bre
WHERE bre.instance_id = p_employee_id
and papf.person_id = bre.person_id
and( trunc(sysdate) between papf.effective_start_date
and papf.effective_end_date);
procedure validate_insert (p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_department_code IN VARCHAR2
,p_organization_id IN NUMBER
,p_resource_code IN VARCHAR2
,p_uom_code IN VARCHAR2
,p_usage_rate IN NUMBER
,p_assigned_units IN NUMBER
,p_start_date IN DATE
,p_end_date IN DATE
,p_activity IN VARCHAR2
,x_uom_status OUT NOCOPY NUMBER
,x_operation_status OUT NOCOPY NUMBER
,x_department_status OUT NOCOPY NUMBER
,x_res_status OUT NOCOPY NUMBER
,x_usage_status OUT NOCOPY NUMBER
,x_assigned_units OUT NOCOPY NUMBER
,x_assigned OUT NOCOPY NUMBER
,x_dates OUT NOCOPY NUMBER
,x_activity OUT NOCOPY NUMBER) IS
l_res_code varchar2(80);
select res.resource_code,
res.unit_of_measure
from cst_activities cst, mtl_uom_conversions muc, bom_resources res, bom_department_resources bdr
where nvl(res.disable_date,sysdate+2) > sysdate
and res.resource_id = bdr.resource_id
and res.default_activity_id = cst.activity_id(+)
and (cst.organization_id = res.organization_id or cst.organization_id is null)
and nvl(cst.disable_date(+), sysdate+2) > sysdate
and res.unit_of_measure = muc.uom_code
and muc.inventory_item_id = 0
and res.organization_id = p_organization_id
and department_id = (select department_id
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num);
select wo.operation_seq_num, bd.department_code
from wip_operations wo, bom_departments bd
where bd.department_id = wo.department_id
and bd.organization_id = wo.organization_id
and wo.organization_id = p_organization_id
and wo.wip_entity_id = p_wip_entity_id;
select activity
from cst_activities
where nvl(disable_date, sysdate + 2) > sysdate and
(organization_id is null or organization_id = p_organization_id ) ;
END validate_insert;
procedure insert_into_wor( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_organization_id IN NUMBER
,p_usage_rate IN NUMBER
,p_resource_code IN VARCHAR2
,p_uom_code IN VARCHAR2
,p_resource_seq_num IN NUMBER
,p_dept_code IN VARCHAR2
,p_assigned_units IN NUMBER
,p_basis IN NUMBER
,p_scheduled_flag IN NUMBER
,p_charge_type IN NUMBER
,p_schedule_sequence IN NUMBER
,p_std_rate IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE
,p_activity IN VARCHAR2
,p_mod IN VARCHAR2
,x_update_status OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wor';
l_update_status NUMBER := 1;
SAVEPOINT get_insert_into_wor_pvt;
select resource_id
into l_resource_id
from bom_resources
where resource_code = p_resource_code
and organization_id = p_organization_id;
select activity_id
into l_activity_id
from cst_activities
where activity = p_activity
and organization_id = organization_id;
if (p_mod='UPDATE') then
-- first query up the old resource for use in scheduling decision.
select scheduled_flag, schedule_seq_num,
start_date, completion_date, usage_rate_or_amount, uom_code, assigned_units
into l_old_scheduled_flag, l_old_schedule_sequence, l_old_start_date
, l_old_end_date, l_old_usage_rate, l_old_uom_code, l_old_assigned_units
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num
and organization_id = p_organization_id;
l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'updatewor.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
l_update_status := 0;
elsif (p_mod='INSERT') then
-- first find out whether we will need to call the
-- scheduler finally
if p_scheduled_flag = 1 then
l_call_scheduler := 1;
, p_debug_filename => 'insertwor.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
end if;-- end of insertion and updation
x_update_status := l_update_status;
ROLLBACK TO get_insert_into_wor_pvt;
ROLLBACK TO get_insert_into_wor_pvt;
ROLLBACK TO get_insert_into_wor_pvt;
ROLLBACK TO get_insert_into_wor_pvt;
END insert_into_wor;
select meaning
from mfg_lookups
where lookup_type = g_supply_type
and (lookup_code = 1 or lookup_code = 4) ;
select msinv.secondary_inventory_name,
SUM(moq.transaction_quantity) on_hand_quantity
from mtl_secondary_inventories msinv, mtl_onhand_quantities moq
where moq.organization_id=msinv.organization_id
and nvl(msinv.disable_date, sysdate+2) > sysdate
and moq.subinventory_code = msinv.secondary_inventory_name
and msinv.organization_id = p_organization_id
and moq.inventory_item_id = (select inventory_item_id from mtl_system_items_kfv
where organization_id = p_organization_id
and concatenated_segments =p_concatenated_segments)
group by msinv.secondary_inventory_name, moq.inventory_item_id, msinv.organization_id, msinv.description, msinv.locator_type
order by msinv.secondary_inventory_name;
select concatenated_segments
from mtl_item_locations_kfv
where (disable_date > sysdate or disable_date is null)
and organization_id = p_organization_id
and subinventory_code = p_subinventory_code ;
PROCEDURE insert_into_wro(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_concatenated_segments IN VARCHAR2
,p_description IN VARCHAR2
,p_operation_seq_num IN NUMBER
,p_supply IN VARCHAR2
,p_required_date IN DATE
,p_quantity IN NUMBER
,p_comments IN VARCHAR2
,p_supply_subinventory IN VARCHAR2
,p_locator IN VARCHAR2
,p_mrp_net_flag IN VARCHAR2
,p_material_release IN VARCHAR2
,x_invalid_update_operation OUT NOCOPY NUMBER
,x_invalid_update_department OUT NOCOPY NUMBER
,x_invalid_update_description OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_update_status OUT NOCOPY NUMBER
,p_supply_code IN NUMBER :=NULL
,p_one_step_issue IN varchar2:=fnd_api.g_false
,p_released_quantity IN NUMBER := NULL)
IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_into_wro';
invalid_update_operation NUMBER := 0;
invalid_update_department NUMBER := 0;
invalid_update_description NUMBER := 0;
l_update_status NUMBER := 0;
SAVEPOINT get_insert_into_wro_pvt;
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_kfv
where concatenated_segments = p_concatenated_segments
and organization_id = p_organization_id;
select 1, wro.operation_seq_num,wro.department_id,msikfv.description
into l_material_exists, l_existing_operation, l_existing_department, l_existing_description
from wip_requirement_operations wro, mtl_system_items_kfv msikfv
where wro.inventory_item_id = l_inventory_item_id
and wro.organization_id = p_organization_id
and wro.wip_entity_id = p_wip_entity_id
and wro.organization_id = msikfv.organization_id
and wro.operation_seq_num = p_operation_seq_num
and wro.inventory_item_id = msikfv.inventory_item_id;
select department_id
into l_department_id
from wip_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = p_operation_seq_num
and organization_id = p_organization_id;
select lookup_code
into l_supply
from mfg_lookups
where lookup_type = g_supply_type
and meaning = p_supply;
select inventory_location_id
into l_locator
from mtl_item_locations_kfv
where organization_id = p_organization_id
and concatenated_segments = p_locator
and subinventory_code = p_supply_subinventory ;
select material_issue_by_mo into l_material_issue_by_mo_temp
from wip_discrete_jobs
where
wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
update wip_discrete_jobs set material_issue_by_mo='N'
where
wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
invalid_update_operation := 1;
invalid_update_department := 1;
invalid_update_description := 1;
if ((invalid_update_operation = 0) and (invalid_update_department = 0)
and (invalid_update_description = 0)) then
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'updatewro.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
l_update_status := 1;
, p_debug_filename => 'insertwro.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
update wip_discrete_jobs set material_issue_by_mo=l_material_issue_by_mo_temp
where
wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
x_invalid_update_operation := invalid_update_operation ;
x_invalid_update_department := invalid_update_department;
x_invalid_update_description := invalid_update_description;
x_update_status := l_update_status;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
ROLLBACK TO get_insert_into_wro_pvt;
END insert_into_wro;
PROCEDURE delete_resources (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_resource_seq_num IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_resources';
SAVEPOINT get_delete_resources_pvt;
select organization_id, resource_id
into l_organization_id, l_resource_id
from wip_operation_resources
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num
and resource_seq_num = l_resource_seq_num;
select count(*)
into l_exists
from wip_op_resource_instances
where wip_entity_id = l_wip_entity_id and
operation_seq_num = l_operation_seq_num and
resource_seq_num = l_resource_seq_num;
l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
ROLLBACK TO get_delete_resources_pvt;
ROLLBACK TO get_delete_resources_pvt;
ROLLBACK TO get_delete_resources_pvt;
ROLLBACK TO get_delete_resources_pvt;
END delete_resources;
SELECT department_id
INTO l_department_id
FROM BOM_DEPARTMENTS bd
WHERE bd.organization_id = p_organization_id
AND department_code = p_department_code
AND NVL (bd.disable_date, sysdate+2) > sysdate
AND NOT EXISTS
(
SELECT '1'
FROM WIP_OPERATION_RESOURCES wor
WHERE wor.organization_id = p_organization_id
AND wor.wip_entity_id = p_wip_entity_id
AND wor.operation_seq_num = p_operation_seq_num
AND wor.resource_id not in
(
SELECT bdr.resource_id
FROM BOM_DEPARTMENT_RESOURCES bdr
WHERE bdr.department_id = bd.department_id
)
);
SELECT lookup_code
INTO l_lookup_code
FROM MFG_LOOKUPS
WHERE lookup_type = g_shutdown_type
AND meaning = p_meaning ;
SELECT bdp.department_id, bso.standard_operation_id,
bso.shutdown_type
INTO l_department_id, l_standard_operation_id,
l_shutdown_type
FROM BOM_DEPARTMENTS bdp,
BOM_STANDARD_OPERATIONS bso
WHERE bso.organization_id = p_organization_id
AND bso.operation_code = p_operation_code
AND bso.line_id IS NULL
AND NVL ( bso.operation_type, 1) = 1
AND bdp.organization_id = p_organization_id
AND bso.department_id = bdp.department_id
AND NVL ( bdp.disable_date, sysdate + 2) > sysdate ;
procedure insert_into_wo ( p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_standard_operation_id IN NUMBER
,p_organization_id IN NUMBER
,p_description IN VARCHAR2
,p_department_id IN NUMBER
,p_shutdown_type IN VARCHAR2
,p_first_unit_start_date IN VARCHAR2
,p_last_unit_completion_date IN VARCHAR2
,p_duration IN NUMBER
,p_long_description IN VARCHAR2 := null
,x_return_status OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER ) IS
l_return_status VARCHAR2(1);
SELECT 1
FROM bom_standard_operations bso,
bom_std_op_resources bsor
WHERE bso.standard_operation_id = bsor.standard_operation_id
AND bsor.standard_operation_id = p_standard_operation_id
AND bso.organization_id = p_organization_id
AND bsor.autocharge_type NOT IN (2,3);
SAVEPOINT label_insert_into_wo;
, p_debug_filename => 'insertwo.log'
, p_output_dir =>l_output_dir
, p_debug_file_mode => 'w'
);
ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
ROLLBACK TO label_insert_into_wo; -- Fix for 3823415
ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
ROLLBACK TO label_insert_into_wo; -- Fix for 3582756
END insert_into_wo;
procedure update_wo ( p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_organization_id IN NUMBER
,p_description IN VARCHAR2
,p_shutdown_type IN VARCHAR2
,p_first_unit_start_date IN VARCHAR2
,p_last_unit_completion_date IN VARCHAR2
,p_duration IN NUMBER
,p_long_description IN VARCHAR2 := null
,x_return_status OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER ) IS
l_return_status VARCHAR2(1);
SAVEPOINT UPDATE_WO;
l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
, p_debug_filename => 'updatewo.log'
, p_output_dir => l_output_dir
, p_debug_file_mode => 'w'
);
ROLLBACK TO UPDATE_WO;
ROLLBACK TO UPDATE_WO;
END update_wo;
select
first_unit_start_date ,
last_unit_completion_date,
operation_completed
into
l_from_scheduled_start_date,
l_from_scheduled_end_date,
l_from_operation_completed
from
wip_operations
where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_from_operation ;
select
first_unit_start_date ,
last_unit_completion_date,
operation_completed
into
l_to_scheduled_start_date,
l_to_scheduled_end_date,
l_to_operation_completed
from
wip_operations
where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_to_operation ;
select
count(*) into l_restrict_date_change
from
dual
where
exists
(select '1' from eam_prior_operations_v
where next_operation = p_from_operation
and schedule_end_date > p_sche_start_date
and wip_entity_id = p_wip_entity_id);
select count(*) into l_restrict_date_change
from dual
where
exists
(select '1' from eam_next_operations_v
where prior_operation = p_from_operation
and schedule_start_date < p_sche_end_date
and wip_entity_id = p_wip_entity_id);
update wip_operations
set
first_unit_start_date = p_sche_start_date,
last_unit_start_date = p_sche_start_date,
first_unit_completion_date = p_sche_end_date,
last_unit_completion_date = p_sche_end_date
where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_from_operation ;
select
count(*) into l_restrict_date_change
from
dual
where
exists
(select '1' from eam_prior_operations_v
where next_operation = p_to_operation
and schedule_end_date > p_sche_start_date
and wip_entity_id = p_wip_entity_id);
select count(*) into l_restrict_date_change
from dual
where
exists
(select '1' from eam_next_operations_v
where prior_operation = p_to_operation
and schedule_start_date < p_sche_start_date
and wip_entity_id = p_wip_entity_id);
update wip_operations
set
first_unit_start_date = p_sche_start_date,
last_unit_start_date = p_sche_start_date,
first_unit_completion_date = p_sche_end_date,
last_unit_completion_date = p_sche_end_date
where
wip_entity_id = p_wip_entity_id and
operation_seq_num = p_to_operation ;
select count(1) into l_loop_available
from dual
where
p_from_operation in (select next_operation
from (select * from wip_operation_networks
where next_operation <> p_to_operation and
wip_entity_id = p_wip_entity_id)
start with prior_operation = p_to_operation
connect by prior_operation = prior next_operation) ;
PROCEDURE delete_link(p_from_operation IN NUMBER,
p_to_operation IN NUMBER,
p_dep_direction IN NUMBER,
p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_user_id IN NUMBER,
x_error_flag OUT NOCOPY VARCHAR2,
x_error_mssg OUT NOCOPY VARCHAR2 ) IS
/* Added for implementing WO API */
l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
l_eam_op_network_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_DELETE;
SAVEPOINT delete_op_network;
ROLLBACK TO delete_op_network;
End delete_link;
select status_type , nvl(firm_planned_flag,2), scheduled_start_date,
scheduled_completion_date
into l_status_type, l_firm, l_start_date, l_completion_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id
and organization_id = l_organization_id;
SELECT count(operation_seq_num)
INTO l_op_count
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
SELECT wo.operation_seq_num, bd.department_code
INTO op_seq_num, op_dept_code
FROM wip_operations wo, bom_departments bd
WHERE wo.wip_entity_id = p_wip_entity_id and
wo.organization_id = p_organization_id and
wo.organization_id = bd.organization_id and
wo.department_id = bd.department_id;
SELECT count(operation_seq_num)
INTO l_op_count
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id and
organization_id = p_organization_id;
SELECT wo.operation_seq_num, wo.first_unit_start_date, wo.last_unit_completion_date, bd.department_code
INTO x_op_seq_num, x_op_start_date, x_op_end_date, x_op_dept_code
FROM wip_operations wo, bom_departments bd
WHERE wo.wip_entity_id = p_wip_entity_id and
wo.organization_id = p_organization_id and
wo.organization_id = bd.organization_id and
wo.department_id = bd.department_id;
SELECT department_id
INTO l_department_id
FROM bom_departments
WHERE department_code like p_department
AND organization_id = p_organization_id;
SELECT bdr.resource_id
INTO l_resource_id
FROM bom_department_resources bdr , bom_resources br
WHERE bdr.department_id = l_department_id
AND bdr.resource_id = br.resource_id
AND br.resource_code like p_resource_code
AND br.organization_id = p_organization_id;
/* API to check if operation can be deleted from self service side */
procedure check_op_deletion
( p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
x_return_status OUT NOCOPY NUMBER
) IS
l_wip_entity_id NUMBER;
select count(*)
into l_count_routing
from wip_operation_networks
where wip_entity_id = l_wip_entity_id and ( prior_operation = p_operation_seq_num or next_operation = p_operation_seq_num);
select count(*)
into l_count_mat
from wip_requirement_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num;
select count(*)
into l_count_di
from wip_eam_direct_items
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num
and rownum =1;
select count(*)
into l_count_res
from wip_operation_resources
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num;
select operation_completed
into l_completed
from wip_operations
where wip_entity_id = l_wip_entity_id
and operation_seq_num = l_operation_seq_num;
/* API to delete operation from self service side */
procedure delete_operation (
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_organization_id IN NUMBER
,p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_department_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_api_name constant varchar2(30) := 'Delete_Operations';
SAVEPOINT DELETE_OPERATION_JSP;
l_eam_op_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_DELETE;
ROLLBACK TO DELETE_OPERATION_JSP;
fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
ROLLBACK TO DELETE_OPERATION_JSP;
end delete_operation;
PROCEDURE update_wro
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_organization_id IN NUMBER
,p_wip_entity_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_inventory_item_id IN NUMBER
,p_update IN NUMBER
,p_required_qty IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
SAVEPOINT update_wro;
IF(p_update=1) THEN --update wro
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
ELSE --delete from wro
l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
ROLLBACK TO update_wro;
ROLLBACK TO update_wro;
END update_wro;
PROCEDURE delete_instance (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
,p_wip_entity_id IN NUMBER
,p_organization_id IN NUMBER
,p_operation_seq_num IN NUMBER
,p_resource_seq_num IN NUMBER
,p_instance_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_instance';
SAVEPOINT delete_instance_pvt;
l_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
ROLLBACK TO delete_instance_pvt;
ROLLBACK TO get_delete_resources_pvt;
ROLLBACK TO get_delete_resources_pvt;
ROLLBACK TO get_delete_resources_pvt;
END delete_instance;