The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_mode NUMBER; -- 0 for Create and 1 for Update
l_workorder_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
/*End of update wo ***********/
if(x_return_status<>'S') then
ROLLBACK TO add_exist_work_order;
SELECT '1'
INTO network_child_job_var
FROM dual
WHERE EXISTS (SELECT '1'
FROM wip_discrete_jobs
WHERE wip_entity_id IN
(
SELECT DISTINCT child_object_id
FROM eam_wo_relationships
WHERE parent_relationship_type =1
START WITH parent_object_id = p_wip_entity_id AND parent_relationship_type = 1
CONNECT BY parent_object_id = prior child_object_id AND parent_relationship_type = 1
)
AND status_type NOT IN (7)
);
SELECT decode(wdj.status_type,3,1,0)
INTO wo_released
FROM WIP_DISCRETE_JOBS wdj
WHERE wdj.wip_entity_id=p_wip_entity_id;
SELECT '1'
INTO dependent_rel
FROM DUAL
WHERE EXISTS (SELECT ewr.child_object_id
FROM EAM_WO_RELATIONSHIPS ewr,WIP_DISCRETE_JOBS wdj
WHERE ewr.parent_object_id=p_wip_entity_id AND ewr.parent_relationship_type = 2
AND wdj.wip_entity_id=ewr.child_object_id AND (wo_released=1 OR wdj.status_type=3)
UNION
SELECT ewr.parent_object_id
FROM EAM_WO_RELATIONSHIPS ewr,WIP_DISCRETE_JOBS wdj
WHERE ewr.child_object_id=p_wip_entity_id AND ewr.parent_relationship_type = 2
AND wdj.wip_entity_id=ewr.parent_object_id AND (wo_released=1 OR wdj.status_type=3)
);
,p_stored_last_update_date IN DATE -- old update date, for locking only
,p_rebuild_jobs IN VARCHAR2 := NULL -- holds 'Y' or 'N'
,p_subinventory IN VARCHAR2 := NULL
,p_subinv_ctrl IN NUMBER := NULL
,p_org_id IN NUMBER := NULL
,p_item_id IN NUMBER := NULL
,p_locator_id IN NUMBER := NULL
,p_locator_ctrl IN NUMBER := NULL
,p_locator IN VARCHAR2 := NULL
,p_lot IN VARCHAR2 := NULL
,p_serial IN VARCHAR2 := NULL
,p_manual_flag IN VARCHAR2 := NULL
,p_serial_status IN VARCHAR2 := NULL
,p_qa_collection_id IN NUMBER
,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_Workorder';
l_db_last_update_date DATE;
SELECT last_update_date, status_type, shutdown_type
INTO l_db_last_update_date, l_db_status , l_shutdown_type
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
FOR UPDATE;
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 max(transaction_date) into l_max_tran_date
from eam_job_completion_txns where transaction_type = 1
and wip_entity_id = p_wip_entity_id;
select actual_start_date, actual_end_date into
l_actual_start_date, l_actual_end_date
from eam_job_completion_txns where transaction_date = l_max_tran_date
and wip_entity_id = p_wip_entity_id;
select nvl(min(period_start_date), sysdate+2)
into l_min_open_period
from org_acct_periods
where organization_id = p_org_id
and open_flag = 'Y';
select nvl(max(actual_end_date), sysdate - 200000)
into l_max_compl_op_date
from eam_op_completion_txns eoct
where wip_entity_id = p_wip_entity_id
--fix for 3543834.added where clause so that the last completion date will be fetched if the operation is complete
and transaction_type=1
and transaction_id = (select max(transaction_id)
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = eoct.operation_seq_num
);
select nvl(min(actual_start_date), sysdate + 200000)
into l_min_compl_op_date
from eam_op_completion_txns eoct
where wip_entity_id = p_wip_entity_id
--fix for 3543834.added where clause so that the last completion date will be fetched if the operation is complete
and transaction_type=1
and transaction_id = (select max(transaction_id)
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = eoct.operation_seq_num
);
select mlu.lookup_code
into l_reconciliation_code
from mfg_lookups mlu
where mlu.lookup_type = 'WIP_EAM_RECONCILIATION_CODE'
and mlu.meaning = p_reconciliation_code;
select 1 into l_Dummy from mtl_system_items_b msi
where msi.inventory_item_id = p_item_id
and msi.organization_id = p_org_id;
select 1 into l_Dummy from mtl_serial_numbers msn
where msn.inventory_item_id = p_item_id
and msn.serial_number = p_serial
and msn.current_status = 4;
select
lot_control_code into l_lot_ctrl_code
from
mtl_system_items_b
where
inventory_item_id = p_item_id
and organization_id = p_org_id ;
select secondary_inventory_name into l_subinv
from mtl_secondary_inventories
where
secondary_inventory_name = p_subinventory
and organization_id = p_org_id
and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
and Asset_inventory = 2;
select secondary_inventory_name into l_subinv
from mtl_secondary_inventories
where
secondary_inventory_name = p_subinventory
and organization_id = p_org_id
and nvl(disable_date,trunc(sysdate)+1)>trunc(sysdate)
and Asset_inventory = 2
and EXISTS (select secondary_inventory from mtl_item_sub_inventories
where secondary_inventory = secondary_inventory_name
and inventory_item_id = p_item_id
and organization_id = p_org_id);
select Inventory_Location_ID into l_locator_id
from mtl_item_locations_kfv where
concatenated_segments = p_locator
and subinventory_code = p_subinventory
and organization_id = p_org_id;
select Inventory_Location_ID into l_locator_id
from mtl_item_locations_kfv where
concatenated_segments = p_locator
and subinventory_code = p_subinventory
and organization_id = p_org_id
and EXISTS (select '1' from mtl_secondary_locators
where inventory_item_id = p_item_id
and organization_id = p_org_id
and secondary_locator = inventory_location_id) ;
select
lot_number into l_lot_number
from
mtl_lot_numbers
where
inventory_item_id = p_item_id
and organization_id = p_org_id;
,x_user_id => g_last_updated_by
,x_actual_start_date => l_actual_start_date
,x_actual_end_date => l_actual_end_date
,x_actual_duration => l_actual_duration
,x_reconcil_code => l_reconciliation_code
,x_shutdown_start_date => p_shutdown_start_date
,x_shutdown_end_date => p_shutdown_end_date
,x_subinventory => p_subinventory
,x_locator_id => p_locator_id
,x_lot_number => p_lot
,x_serial_number => p_serial
,errcode => l_err_number
,errmsg => l_err_code
,x_qa_collection_id =>p_qa_collection_id
,x_attribute_category => p_attribute_category
,x_attribute1 => p_attribute1
,x_attribute2 => p_attribute2
,x_attribute3 => p_attribute3
,x_attribute4 => p_attribute4
,x_attribute5 => p_attribute5
,x_attribute6 => p_attribute6
,x_attribute7 => p_attribute7
,x_attribute8 => p_attribute8
,x_attribute9 => p_attribute9
,x_attribute10 => p_attribute10
,x_attribute11 => p_attribute11
,x_attribute12 => p_attribute12
,x_attribute13 => p_attribute13
,x_attribute14 => p_attribute14
,x_attribute15 => p_attribute15
);
l_mode NUMBER; -- 0 for Create and 1 for Update
SELECT system_status
FROM EAM_WO_STATUSES_V
WHERE status_id = p_status_type;
select wdj.status_type
into l_orig_wo_status
from wip_discrete_jobs wdj,wip_entities we
where we.wip_entity_id=wdj.wip_entity_id
and we.organization_id=p_organization_id
and wdj.organization_id=p_organization_id
and we.wip_entity_name=p_wip_entity_name;
select nvl(cii.active_start_date, sysdate-1),
nvl(cii.active_start_date, sysdate-1),msikfv.eam_item_type
into l_start_date, l_end_date, l_eam_item_type
from csi_item_instances cii, mtl_system_items_b_kfv msikfv, mtl_parameters mp
where cii.inventory_item_id =msikfv.inventory_item_id
and cii.last_vld_organization_id =msikfv.organization_id --Bug 2157979
and cii.last_vld_organization_id = mp.organization_id
and mp.maint_organization_id = p_organization_id
and cii.serial_number = p_asset_number
and msikfv.CONCATENATED_SEGMENTS = nvl(p_asset_group, msikfv.CONCATENATED_SEGMENTS)
and rownum = 1;
select cii.inventory_item_id , eomd.ACCOUNTING_CLASS_CODE,
cii.instance_id, msikfv.eam_item_type , eomd.area_id
into l_asset_group_id, l_asset_class, l_maintenance_object_id, l_eam_item_type , l_eam_location_id
from csi_item_instances cii, mtl_system_items_b_kfv msikfv,
mtl_parameters mp, eam_org_maint_defaults eomd
where cii.inventory_item_id =msikfv.inventory_item_id
and cii.last_vld_organization_id =msikfv.organization_id --Bug 2157979
and cii.last_vld_organization_id = mp.organization_id
and mp.maint_organization_id = p_organization_id
and cii.serial_number = p_asset_number
and msikfv.CONCATENATED_SEGMENTS = nvl(p_asset_group, msikfv.CONCATENATED_SEGMENTS)
and eomd.object_type (+) = 50
and eomd.object_id (+) = cii.instance_id
and eomd.organization_id (+) = p_organization_id
and rownum = 1;
select msikfv.inventory_item_id, msikfv.eam_item_type, msikfv.serial_number_control_code
into l_asset_group_id, l_eam_item_type, l_serial_number_control
from mtl_system_items_b_kfv msikfv, mtl_parameters mp
where msikfv.organization_id=mp.organization_id
and mp.maint_organization_id = p_organization_id
and msikfv.CONCATENATED_SEGMENTS = p_asset_group
and rownum = 1;
select department_id
into l_dept_id
from bom_departments
where organization_id = p_organization_id
and department_code = p_owning_department;
select count(*), avg(r.work_request_id)
into l_count, l_work_request_id
from wip_eam_work_requests r
where r.work_request_number = p_work_request_number;
select r.work_request_number, r.wip_entity_id, r.work_request_status_id
into l_work_request_number, l_work_request_wip_entity_id, l_work_request_status
from wip_eam_work_requests r
where r.work_request_id = l_work_request_id for update;
SELECT wip_entity_id
INTO l_parent_wip_entity_id
FROM WIP_ENTITIES
WHERE wip_entity_name=p_parent_work_order
AND organization_id=p_organization_id;
select inventory_item_id into l_asset_activity_id
from mtl_system_items_b_kfv
where concatenated_segments = p_asset_activity
and eam_item_type = 2
and organization_id = p_organization_id;
-- if user enters service request direcly without selecting from LOV
if (p_service_request_id is null and p_service_request_number is not null) then
select incident_id
into l_service_request_id
from cs_incidents_all_b
where incident_number = p_service_request_number;
select count(*) into l_count
from pjm_projects_v ppv,
pjm_project_parameters ppp
where ppv.project_id = ppp.project_id
and ppp.organization_id = p_organization_id
and ppv.project_number = p_project_number
and rownum <= 1;
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_organization_id
and ppv.project_number = p_project_number;
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;
if (l_mode = 1) then -- Update of Work Order API
begin
/*Bug#4425025 - have date_released as null if its null to enable defaulting in EAM_WO_DEFAULT_PVT*/
/* select we.wip_entity_id, nvl(wdj.date_released,sysdate),wdj.parent_wip_entity_id,wdj.manual_rebuild_flag */
select we.wip_entity_id,wdj.date_released,wdj.parent_wip_entity_id,wdj.manual_rebuild_flag
into l_wip_entity_updt, l_date_released,l_old_rebuild_source,manual_rebuild_flag
from wip_entities we, wip_discrete_jobs wdj
where we.wip_entity_name = l_wip_entity_name
and we.organization_id = p_organization_id
and we.organization_id = wdj.organization_id
and we.wip_entity_id = wdj.wip_entity_id;
l_workorder_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
select primary_item_id,project_id,task_id into l_prev_activity_id,l_prev_project_id,l_prev_task_id
from wip_discrete_jobs
where wip_entity_id = l_workorder_rec.wip_entity_id
and organization_id = l_workorder_rec.organization_id;
select count(*)
into constraining_rel
from eam_wo_relationships
where parent_object_id=l_old_rebuild_source
and child_object_id=l_wip_entity_updt
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=l_wip_entity_updt
and parent_relationship_type=4;
l_eam_wo_relations_rec2.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
, p_debug_filename => 'updatewoss.log'
, p_output_dir =>l_output_dir
, p_commit => 'N'
, p_debug_file_mode => 'w'
);
/*End of update wo ***********/
if(x_return_status<>'S') then
ROLLBACK TO create_easy_work_order;
-- if service request is specified then insert into eam_wo_service_association
if (l_service_request_id is not null) then
select eam_wo_service_association_s.nextval
into l_service_association_id
from dual;
select count(*)
into l_row_count
from eam_wo_service_association
where wip_entity_id = l_wip_entity_updt
and (enable_flag IS NULL OR enable_flag = 'Y'); -- Fix for 3773450
insert into eam_wo_service_association
(
wo_service_entity_assoc_id
,maintenance_organization_id
,wip_entity_id
,service_request_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,enable_flag -- Fix for Bug 3773450
)
values
(
l_service_association_id
,p_organization_id
,l_wip_entity_updt
,l_service_request_id
,sysdate
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.LOGIN_ID
,'Y'
);
select service_request_id
into l_orig_service_request_id
from eam_wo_service_association
where maintenance_organization_id=p_organization_id
and wip_entity_id=l_wip_entity_updt
and (enable_flag IS NULL OR enable_flag='Y'); -- Fix for Bug 3773450
update wip_eam_work_requests r
set r.work_request_status_id = 4
, r.wip_entity_id = l_workorder_rec1.wip_entity_id
, r.last_update_date = sysdate
, r.last_updated_by = FND_GLOBAL.user_id
where r.work_request_id = l_work_request_id;
else -- End of Update Work Order, Start of Create Work Order
-------------------------------------------------------------
-- DML here
--create new work order
BEGIN
-- Start of Call to Work Order PL/SQL API
/*cboppana-Changed for Work Order Linking project */
l_workorder_rec.header_id := 1;
l_eam_failure_codes_tbl.delete;
EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
-- if service request is specified then insert into eam_wo_service_association
if (l_service_request_id is not null) then
select eam_wo_service_association_s.nextval
into l_service_association_id
from dual;
insert into eam_wo_service_association
(
wo_service_entity_assoc_id
,maintenance_organization_id
,wip_entity_id
,service_request_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,enable_flag -- Fix for Bug 3773450
)
values
(
l_service_association_id
,p_organization_id
,x_new_work_order_id
,l_service_request_id
,sysdate
,FND_GLOBAL.user_id
,sysdate
,FND_GLOBAL.user_id
,FND_GLOBAL.LOGIN_ID
,'Y' -- Fix for Bug 3773450
);
update wip_eam_work_requests r
set r.work_request_status_id = 4
, r.wip_entity_id = l_workorder_rec1.wip_entity_id
, r.last_update_date = sysdate
, r.last_updated_by = FND_GLOBAL.user_id
where r.work_request_id = l_work_request_id;
procedure update_work_order
( p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_validate_only IN VARCHAR2 := FND_API.G_TRUE
,p_record_version_number IN NUMBER := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_wip_entity_id IN NUMBER
,p_description IN VARCHAR2
,p_owning_department IN VARCHAR2
,p_priority IN NUMBER
,p_shutdown_type IN VARCHAR2
,p_activity_type IN VARCHAR2
,p_activity_cause IN VARCHAR2
,p_firm_planned_flag IN NUMBER
,p_notification_required IN VARCHAR2
,p_tagout_required IN VARCHAR2
,p_scheduled_start_date IN DATE
,p_stored_last_update_date IN DATE
) IS
l_api_name CONSTANT VARCHAR(30) := 'update_work_order';
l_db_last_update_date DATE;
eam_debug.init_err_stack('eam_workorders_jsp.update_work_order');
SELECT last_update_date, status_type, organization_id
, scheduled_completion_date - scheduled_start_date
, p_scheduled_start_date - scheduled_start_date
INTO l_db_last_update_date, l_db_status , l_org_id , l_duration , l_shift
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id
FOR UPDATE;
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 department_id
into l_dept_id
from bom_departments
where organization_id = l_org_id
and department_code = p_owning_department;
eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_WO_UPDATE_DATE_MISS');
update wip_discrete_jobs j
set j.description = p_description
, j.activity_type = p_activity_type
, j.activity_cause = p_activity_cause
, j.owning_department = l_dept_id
, j.priority = p_priority
, j.shutdown_type = p_shutdown_type
, j.firm_planned_flag = p_firm_planned_flag
, j.notification_required = p_notification_required
, j.tagout_required = p_tagout_required
, j.last_update_date = sysdate
, j.last_updated_by = g_last_updated_by
, j.last_update_login = g_last_update_login
where j.wip_entity_id = p_wip_entity_id;
update wip_discrete_jobs j
set j.scheduled_start_date = p_scheduled_start_date
, j.scheduled_completion_date = j.scheduled_completion_date + l_shift
where j.wip_entity_id = p_wip_entity_id;
update wip_operations op
set op.first_unit_start_date = op.first_unit_start_date + l_shift
, op.last_unit_start_date = op.last_unit_start_date + l_shift
, op.first_unit_completion_date = op.first_unit_completion_date + l_shift
, op.last_unit_completion_date = op.last_unit_completion_date + l_shift
, op.last_update_date = sysdate
, op.last_updated_by = g_last_updated_by
, op.last_update_login = g_last_update_login
where op.wip_entity_id = p_wip_entity_id;
update wip_operation_resources wor
set wor.start_date = wor.start_date + l_shift
, wor.completion_date = wor.completion_date + l_shift
, wor.last_update_date = sysdate
, wor.last_updated_by = g_last_updated_by
, wor.last_update_login = g_last_update_login
where wor.wip_entity_id = p_wip_entity_id;
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
p_procedure_name => EAM_DEBUG.G_err_stack);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
p_procedure_name => EAM_DEBUG.G_err_stack);
FND_MSG_PUB.add_exc_msg( p_pkg_name => 'EAM_WORKORDERS_JSP.update_work_order',
p_procedure_name => EAM_DEBUG.G_err_stack);
END update_work_order;
select scheduled_start_date, scheduled_completion_date
into l_sched_start_date, l_sched_end_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id;
select max(actual_end_date)
into l_max_op_end_date
from eam_op_completion_txns eoct
where wip_entity_id = p_wip_entity_id
and transaction_type=1
and transaction_id = (select max(transaction_id)
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = eoct.operation_seq_num
);
select min(actual_start_date)
into l_min_op_start_date
from eam_op_completion_txns eoct
where wip_entity_id = p_wip_entity_id
and transaction_type=1
and transaction_id = (select max(transaction_id)
from eam_op_completion_txns
where wip_entity_id = p_wip_entity_id
and operation_seq_num = eoct.operation_seq_num
);
select actual_start_date, actual_end_date into
x_start_date, x_end_date
from eam_job_completion_txns
where wip_entity_id = p_wip_entity_id
and transaction_date = (
select max(transaction_date)
from eam_job_completion_txns where transaction_type = 1
and wip_entity_id = p_wip_entity_id);
EAM_PROCESS_WO_PUB.l_eam_wo_list.delete; --Added for bug#4563210
fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
procedure Delete_WorkOrder_Dependency (
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_prior_object_id IN NUMBER
,p_prior_object_type_id IN NUMBER
,p_next_object_id IN NUMBER
,p_next_object_type_id IN NUMBER
,p_relationship_type IN NUMBER := 2
,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_WorkOrder_Dependency';
SAVEPOINT DELETE_DEPENDEND_WORK_ORDER;
l_eam_wo_relations_rec.TRANSACTION_TYPE :=EAM_PROCESS_WO_PUB.G_OPR_DELETE;
ROLLBACK TO DELETE_DEPENDEND_WORK_ORDER;
fnd_message.set_name('EAM','EAM_ERROR_UPDATE_WO');
ROLLBACK TO DELETE_DEPENDEND_WORK_ORDER;
end Delete_WorkOrder_Dependency;
SELECT child_object_id,
parent_object_id,
PARENT_RELATIONSHIP_TYPE
FROM EAM_WO_RELATIONSHIPS
WHERE parent_relationship_type = 1
START WITH parent_object_id = p_wip_entity_id
AND parent_relationship_type = 1
CONNECT BY parent_object_id = PRIOR child_object_id
AND parent_relationship_type = 1;
SELECT parent_object_id INTO l_parent_object_id
FROM EAM_WO_RELATIONSHIPS
WHERE parent_relationship_type = 3
AND child_object_id = c_hierarchy_row.child_object_id;
EAM_WORKORDERS_JSP.Delete_WorkOrder_Dependency (
p_commit => FND_API.G_TRUE
,p_prior_object_type_id => 1
,p_next_object_type_id => 1
,p_organization_id => p_org_id
,p_prior_object_id => l_parent_object_id
,p_next_object_id => c_hierarchy_row.child_object_id
,p_relationship_type => 3
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
, p_debug_filename => 'deletecosthier.log'
, p_output_dir => l_output_dir
, p_commit => p_commit
, p_debug_file_mode => 'A'
);