[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wip_entity_name into l_parent_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_parent_object_id;
SELECT wip_entity_name into l_child_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_child_object_id;
/*select count(*) into l_count
from eam_wo_relationships where
child_object_id = l_child_object_id
and child_object_type_id = l_child_object_type_id
and parent_object_id = l_parent_object_id
and parent_object_type_id = l_parent_object_type_id;
select status_type into l_child_status_type
from wip_discrete_jobs where
wip_entity_id = l_child_object_id;
select maintenance_object_source into
l_child_maint_obj_src
from wip_discrete_jobs where
wip_entity_id = l_child_object_id;
select status_type into l_parent_status_type
from wip_discrete_jobs where
wip_entity_id = l_parent_object_id;
select maintenance_object_source, rebuild_item_id into
l_parent_maint_obj_src, l_rebuild_item_id
from wip_discrete_jobs where
wip_entity_id = l_parent_object_id;
select count(*) into l_count
from eam_wo_relationships where
child_object_id = l_child_object_id
and child_object_type_id = l_child_object_type_id
and parent_relationship_type = l_relationship_type;
select count(*) into l_count
from wip_sched_relationships where
child_object_id = l_child_object_id
and child_object_type_id = l_child_object_type_id
and relationship_type = l_relationship_type;
select count(*) into l_count
from wip_sched_relationships
where child_object_id = l_parent_object_id
and child_object_type_id = l_parent_object_type_id
and relationship_type = 1;
select distinct top_level_object_id, top_level_object_type_id
into l_top_level_object_id , l_top_level_object_type_id
from wip_sched_relationships
where child_object_id = l_parent_object_id
and child_object_type_id = l_parent_object_type_id;
select eam_wo_relationships_s.nextval
into l_wo_relationship_id from dual;
insert into eam_wo_relationships
( wo_relationship_id,
parent_object_id,
parent_object_type_id,
child_object_id,
child_object_type_id,
parent_relationship_type,
relationship_status,
created_by,
creation_date,
last_updated_by,
last_update_date,
top_level_object_id,
top_level_object_type_id
) values
( l_wo_relationship_id,
l_parent_object_id,
l_parent_object_type_id,
l_child_object_id,
l_child_object_type_id,
3,
l_relationship_status,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
null,--l_top_level_object_id,
null--l_top_level_object_type_id
);
wip_sched_relation_grp.insertRow(
p_parentObjectID => l_parent_object_id,
p_parentObjectTypeID => l_parent_object_type_id,
p_childObjectID => l_child_object_id,
p_childObjectTypeID => l_child_object_type_id,
p_relationshipType => l_relationship_type,
p_relationshipStatus => l_relationship_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE);
select eam_wo_relationships_s.nextval
into l_wo_relationship_id from dual;
insert into eam_wo_relationships
( wo_relationship_id,
parent_object_id,
parent_object_type_id,
child_object_id,
child_object_type_id,
parent_relationship_type,
relationship_status,
created_by,
creation_date,
last_updated_by,
last_update_date,
top_level_object_id,
top_level_object_type_id
) values
( l_wo_relationship_id,
l_parent_object_id,
l_parent_object_type_id,
l_child_object_id,
l_child_object_type_id,
l_relationship_type,
l_relationship_status,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_top_level_object_id,
l_top_level_object_type_id
);
select * from wip_sched_relationships wsr
WHERE wsr.relationship_type in (1,2)
START WITH wsr.parent_object_id = l_parent_object_id
CONNECT BY wsr.parent_object_id = PRIOR wsr.child_object_id;
update wip_sched_relationships set
top_level_object_id = l_top_level_object_id,
top_level_object_type_id = l_top_level_object_type_id
where sched_relationship_id = l_relationship_record.sched_relationship_id;
select maintenance_object_source into l_maint_obj_src
from wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;
update eam_wo_relationships set
top_level_object_id = l_top_level_object_id,
top_level_object_type_id = l_top_level_object_type_id
where
parent_object_id = l_relationship_record.parent_object_id
and parent_object_type_id = l_relationship_record.parent_object_type_id
and child_object_id = l_relationship_record.child_object_id
and child_object_type_id = l_relationship_record.child_object_type_id
and parent_relationship_type = l_relationship_record.relationship_type;
select eam_wo_relationships_s.nextval
into l_wo_relationship_id from dual;
insert into eam_wo_relationships
( wo_relationship_id,
parent_object_id,
parent_object_type_id,
child_object_id,
child_object_type_id,
parent_relationship_type,
relationship_status,
created_by,
creation_date,
last_updated_by,
last_update_date,
top_level_object_id,
top_level_object_type_id
) values
( l_wo_relationship_id,
l_parent_object_id,
l_parent_object_type_id,
l_child_object_id,
l_child_object_type_id,
l_relationship_type,
l_relationship_status,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
null,
null
);
select * from wip_sched_relationships wsr
WHERE wsr.relationship_type = 1
START WITH wsr.child_object_id = l_child_object_id
CONNECT BY PRIOR wsr.parent_object_id = wsr.child_object_id;
select firm_planned_flag into l_parent_firm_flag from
wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;
select * from
wip_sched_relationships
where relationship_type = 1
and parent_object_id = l_parent_object_id
and parent_object_type_id = l_parent_object_type_id;
select scheduled_start_date, scheduled_completion_date, status_type, date_completed
into l_wo_start_date, l_wo_end_date,l_status_type,l_date_completed
from wip_discrete_jobs
where wip_entity_id = l_relationship_record.child_object_id;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_end_date from wip_discrete_jobs
where wip_entity_id = l_relationship_record.parent_object_id;
UPDATE WIP_DISCRETE_JOBS set
scheduled_start_date = l_wo_start_date,
scheduled_completion_date = l_wo_end_date
where wip_entity_id = l_parent_object_id;
l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
PROCEDURE Delete_Dependency
(
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_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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
) IS
l_prior_object_id NUMBER := p_prior_object_id;
SELECT wip_entity_name into l_prior_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_prior_object_id;
SELECT wip_entity_name into l_next_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_next_object_id;
select count(*) into l_count_prior from
wip_sched_relationships where
child_object_id = l_prior_object_id
and child_object_type_id = l_prior_object_id
and relationship_type = 2;
select count(*) into l_count_next from
wip_sched_relationships where
parent_object_id = l_next_object_id
and parent_object_type_id = l_next_object_id
and relationship_type = 2;
select status_type into l_status_type
from wip_discrete_jobs where
wip_entity_id = l_prior_object_id;
select sched_relationship_id into l_sched_relationship_id
from WIP_SCHED_RELATIONSHIPS
where parent_object_id = l_prior_object_id
and parent_object_type_id = l_prior_object_type_id
and child_object_id = l_next_object_id
and child_object_type_id = l_next_object_type_id
and relationship_type = 2;
wip_sched_relation_grp.deleteRow(
p_relationshipID => l_sched_relationship_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE);
DELETE from EAM_WO_RELATIONSHIPS
where parent_object_id = l_prior_object_id
and parent_object_type_id = l_prior_object_type_id
and child_object_id = l_next_object_id
and child_object_type_id = l_next_object_type_id
and parent_relationship_type = 2;
END Delete_Dependency;
SELECT wip_entity_name into l_prior_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_prior_object_id;
SELECT wip_entity_name into l_next_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_next_object_id;
select count(*) into l_count from
wip_sched_relationships where
child_object_id = l_prior_object_id
and child_object_type_id = l_prior_object_type_id
and relationship_type = 1;
select count(*) into l_count from
wip_sched_relationships where
child_object_id = l_next_object_id
and child_object_type_id = l_next_object_type_id
and relationship_type = 1;
select status_type, scheduled_start_date,
scheduled_completion_date
into l_status_type, l_prior_start_date,
l_prior_completion_date
from wip_discrete_jobs where
wip_entity_id = l_prior_object_id;
select status_type, scheduled_start_date,
scheduled_completion_date
into l_status_type, l_next_start_date,
l_next_completion_date
from wip_discrete_jobs where
wip_entity_id = l_next_object_id;
l_token_tbl.delete;
select distinct top_level_object_id, top_level_object_type_id
into l_top_level_object_id , l_top_level_object_type_id
from wip_sched_relationships
where child_object_id = l_prior_object_id
and child_object_type_id = l_prior_object_type_id
and relationship_type = 1;
select distinct top_level_object_id, top_level_object_type_id
into l_top_level_object_id_1 , l_top_level_object_type_id_1
from wip_sched_relationships
where child_object_id = l_next_object_id
and child_object_type_id = l_next_object_type_id
and relationship_type = 1;
wip_sched_relation_grp.insertRow(
p_parentObjectID => l_prior_object_id,
p_parentObjectTypeID => l_prior_object_type_id,
p_childObjectID => l_next_object_id,
p_childObjectTypeID => l_next_object_type_id,
p_relationshipType => 2,
p_relationshipStatus => l_relationship_status,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE);
select eam_wo_relationships_s.nextval
into l_wo_relationship_id from dual;
insert into eam_wo_relationships
( wo_relationship_id,
parent_object_id,
parent_object_type_id,
child_object_id,
child_object_type_id,
parent_relationship_type,
relationship_status,
created_by,
creation_date,
last_updated_by,
last_update_date,
top_level_object_id,
top_level_object_type_id
) values
( l_wo_relationship_id,
l_prior_object_id,
l_prior_object_type_id,
l_next_object_id,
l_next_object_type_id,
2,
l_relationship_status,
l_created_by,
sysdate,
l_last_updated_by,
sysdate,
l_top_level_object_id,
l_top_level_object_type_id
);
SELECT wip_entity_name into l_parent_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_parent_object_id;
SELECT wip_entity_name into l_child_workorder
FROM wip_entities we
WHERE we.wip_entity_id = l_child_object_id;
select count(*) into l_count from
wip_sched_relationships where
((child_object_id = l_child_object_id and
child_object_type_id = l_child_object_type_id) OR
(parent_object_id = l_child_object_id and
parent_object_type_id = l_child_object_type_id)
) AND
relationship_type = 2;
delete from eam_wo_relationships where
child_object_id = l_child_object_id
and child_object_type_id = l_child_object_type_id
and parent_object_id = l_parent_object_id
and parent_object_type_id = l_parent_object_type_id
and parent_relationship_type = l_relationship_type;
select sched_relationship_id into l_sched_relationship_id
from WIP_SCHED_RELATIONSHIPS
where parent_object_id = l_parent_object_id
and parent_object_type_id = l_parent_object_type_id
and child_object_id = l_child_object_id
and child_object_type_id = l_child_object_type_id
and relationship_type = 1;
wip_sched_relation_grp.deleteRow(
p_relationshipID => l_sched_relationship_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE);
select firm_planned_flag into l_parent_firm_flag from
wip_discrete_jobs where wip_entity_id = l_parent_object_id;
select * from wip_sched_relationships wsr
WHERE wsr.relationship_type = 1
START WITH wsr.child_object_id = l_parent_object_id
CONNECT BY PRIOR wsr.parent_object_id = wsr.child_object_id;
select firm_planned_flag into l_parent_firm_flag from
wip_discrete_jobs where wip_entity_id = l_parent_record.parent_object_id;
select * from wip_sched_relationships wsr
WHERE wsr.relationship_type in (1,2)
START WITH wsr.parent_object_id = l_child_object_id
CONNECT BY wsr.parent_object_id = PRIOR wsr.child_object_id;
update wip_sched_relationships set
top_level_object_id = l_child_object_id,
top_level_object_type_id = l_child_object_type_id
where sched_relationship_id = l_relationship_record.sched_relationship_id;
select maintenance_object_source into l_maint_obj_src
from wip_discrete_jobs where wip_entity_id = l_relationship_record.parent_object_id;
update eam_wo_relationships set
top_level_object_id = l_child_object_id,
top_level_object_type_id = l_child_object_type_id
where
parent_object_id = l_relationship_record.parent_object_id
and child_object_id = l_relationship_record.child_object_id;
select
wor.start_date as res_start_date,
wor.completion_date as res_completion_date
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id;
select
wo.first_unit_start_date as first_unit_start_date,
wo.last_unit_completion_date as last_unit_completion_date
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.organization_id = p_organization_id;
select
wori.start_date as resinst_start_date,
wori.completion_date as resinst_completion_date
from wip_op_resource_instances wori
where wori.wip_entity_id = p_wip_entity_id;
select
wor.start_date as res_start_date,
wor.completion_date as res_completion_date
from
wip_sub_operation_resources wor
where
wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_completion_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id and
organization_id = l_organization_id;
select * from
wip_operations
where wip_entity_id = l_wip_entity_id and
organization_id = l_organization_id;
select p_wip_entity_id as wip_entity_id, wo.operation_seq_num,
wor.resource_seq_num,
wo.first_unit_start_date as op_start_date,
wo.last_unit_completion_date as op_completion_date,
wor.start_date as res_start_date,
wor.completion_date as res_completion_date
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 p_wip_entity_id as wip_entity_id, wor.operation_seq_num,
wor.resource_seq_num,
wor.start_date as res_start_date,
wor.completion_date as res_completion_date,
wori.start_date as resinst_start_date,
wori.completion_date as resinst_completion_date
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
p_wip_entity_id as wip_entity_id,
woru.operation_seq_num,
woru.resource_seq_num,
woru.start_date as res_usg_start_date,
woru.completion_date as res_usg_completion_date,
wori.start_date as resinst_start_date,
wori.completion_date as resinst_completion_date,
wori.instance_id as resinst_instance_id
from
wip_op_resource_instances wori,
wip_operation_resource_usage woru
where
woru.wip_entity_id = p_wip_entity_id
and wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = woru.operation_seq_num
and wori.resource_seq_num = woru.resource_seq_num
and wori.instance_id = woru.instance_id
and nvl(wori.serial_number,1) = nvl(woru.serial_number,1);
select
p_wip_entity_id as wip_entity_id,
woru.start_date as res_usg_start_date,
woru.completion_date as res_usg_completion_date,
wor.start_date as res_start_date,
wor.completion_date as res_completion_date
from
wip_operation_resources wor,
wip_operation_resource_usage woru
where
wor.wip_entity_id = p_wip_entity_id
and woru.wip_entity_id = p_wip_entity_id
and wor.resource_seq_num = woru.resource_seq_num
and wor.operation_seq_num = woru.operation_seq_num
and woru.instance_id is null;
select scheduled_start_date, scheduled_completion_date
into l_wo_start_date, l_wo_completion_date
from wip_discrete_jobs
where wip_entity_id = l_wip_entity_id;
select * from
wip_operations
where wip_entity_id = l_wip_entity_id;
select
p_wip_entity_id as wip_entity_id,
wor.operation_seq_num,
wor.resource_seq_num,
wor.start_date as res_start_date,
wor.completion_date as res_completion_date,
wori.start_date as resinst_start_date,
wori.completion_date as resinst_completion_date,
wori.instance_id as resinst_instance_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
p_wip_entity_id as wip_entity_id,
woru.operation_seq_num,
woru.resource_seq_num,
woru.start_date as res_usg_start_date,
woru.completion_date as res_usg_completion_date,
wori.start_date as resinst_start_date,
wori.completion_date as resinst_completion_date,
wori.instance_id as resinst_instance_id
from
wip_op_resource_instances wori,
wip_operation_resource_usage woru
where
woru.wip_entity_id = p_wip_entity_id
and wori.wip_entity_id = p_wip_entity_id
and wori.operation_seq_num = woru.operation_seq_num
and wori.resource_seq_num = woru.resource_seq_num
and wori.instance_id = woru.instance_id ;
update wip_operation_resources wor
set start_date = l_resource_inst_record.res_start_date
where wor.wip_entity_id = l_resource_inst_record.wip_entity_id
and wor.operation_seq_num = l_resource_inst_record.operation_seq_num
and wor.resource_seq_num = l_resource_inst_record.resource_seq_num ;
update wip_operation_resources wor
set completion_date = l_resource_inst_record.res_completion_date
where wor.wip_entity_id = l_resource_inst_record.wip_entity_id
and wor.operation_seq_num = l_resource_inst_record.operation_seq_num
and wor.resource_seq_num = l_resource_inst_record.resource_seq_num;
update wip_op_resource_instances wori
set start_date = l_res_usage_inst_record.res_usg_start_date
where wori.wip_entity_id = l_res_usage_inst_record.wip_entity_id
and wori.operation_seq_num = l_res_usage_inst_record.operation_seq_num
and wori.resource_seq_num = l_res_usage_inst_record.resource_seq_num
and wori.instance_id = l_res_usage_inst_record.resinst_instance_id
and wori.serial_number IS NULL;
update wip_op_resource_instances wori
set completion_date = l_res_usage_inst_record.res_usg_completion_date
where wori.wip_entity_id = l_res_usage_inst_record.wip_entity_id
and wori.operation_seq_num = l_res_usage_inst_record.operation_seq_num
and wori.resource_seq_num = l_res_usage_inst_record.resource_seq_num
and wori.instance_id = l_res_usage_inst_record.resinst_instance_id
and wori.serial_number IS NULL;
SELECT COUNT(WDJ.WIP_ENTITY_ID)
INTO l_wo_in_planning
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1
AND WDJ.STATUS_TYPE NOT IN (3,4,5,6,7,12,14,15);
SELECT (WDJ1.SCHEDULED_COMPLETION_DATE - WDJ2.SCHEDULED_COMPLETION_DATE)
INTO l_this_level_min_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.CHILD_OBJECT_ID = l_work_object_id
AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.RELATIONSHIP_TYPE = 1
AND WSR.PARENT_OBJECT_TYPE_ID = 1
AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
AND WDJ2.WIP_ENTITY_ID = l_work_object_id;
SELECT MIN(WDJ2.SCHEDULED_START_DATE - WDJ1.SCHEDULED_COMPLETION_DATE)
INTO l_min_right_snap_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.PARENT_OBJECT_ID = l_work_object_id
AND WSR.PARENT_OBJECT_TYPE_ID = l_work_object_type_id
AND WSR.RELATIONSHIP_TYPE = 2
AND WDJ2.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
-- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
AND WDJ1.WIP_ENTITY_ID = l_work_object_id;
SELECT NVL(WDJ.MAINTENANCE_OBJECT_SOURCE,1)
INTO l_maintenance_object_source
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
SELECT COUNT(WDJ.WIP_ENTITY_ID)
INTO l_wo_in_planning
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1
AND WDJ.STATUS_TYPE NOT IN (3,4,5,6,7,12,14,15);
SELECT (WDJ2.SCHEDULED_START_DATE - WDJ1.SCHEDULED_START_DATE)
INTO l_this_level_min_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.CHILD_OBJECT_ID = l_work_object_id
AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.RELATIONSHIP_TYPE = 1
AND WSR.PARENT_OBJECT_TYPE_ID = 1
AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
AND WDJ2.WIP_ENTITY_ID = l_work_object_id;
SELECT MIN(WDJ1.SCHEDULED_START_DATE - WDJ2.SCHEDULED_COMPLETION_DATE)
INTO l_min_left_snap_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_ID = l_work_object_id
AND WSR.CHILD_OBJECT_TYPE_ID = l_work_object_type_id
AND WSR.RELATIONSHIP_TYPE = 2
AND WDJ2.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
--AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
AND WDJ1.WIP_ENTITY_ID = l_work_object_id;
SELECT NVL(WDJ.MAINTENANCE_OBJECT_SOURCE,1)
INTO l_maintenance_object_source
FROM WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
AND l_work_object_type_id = 1;
SELECT WSR.CHILD_OBJECT_ID,
WSR.CHILD_OBJECT_TYPE_ID
FROM WIP_SCHED_RELATIONSHIPS WSR
WHERE WSR.PARENT_OBJECT_ID = l_p_object
AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
AND WSR.RELATIONSHIP_TYPE = 1;
SELECT MIN(WDJ2.SCHEDULED_START_DATE-WDJ1.SCHEDULED_COMPLETION_DATE)
INTO l_min_right_snap_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.PARENT_OBJECT_ID = l_parent_object_id
AND WSR.PARENT_OBJECT_TYPE_ID = l_parent_object_type_id
AND WSR.RELATIONSHIP_TYPE = 2
AND WSR.RELATIONSHIP_STATUS = 3
AND WDJ2.WIP_ENTITY_ID = WSR.CHILD_OBJECT_ID
-- AND WDJ2.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
AND WDJ1.WIP_ENTITY_ID = l_parent_object_id
-- Commented out;bcos no status checks as per new design.
SELECT WSR2.CHILD_OBJECT_ID CHILD_OBJECT_ID
FROM WIP_SCHED_RELATIONSHIPS WSR2
WHERE WSR2.RELATIONSHIP_TYPE = 1
AND l_starting_obj_type_id = 1
CONNECT BY prior WSR2.CHILD_OBJECT_ID = WSR2.PARENT_OBJECT_ID
START WITH WSR2.PARENT_OBJECT_ID = l_starting_object_id
);
SELECT WSR.CHILD_OBJECT_ID,
WSR.CHILD_OBJECT_TYPE_ID
FROM WIP_SCHED_RELATIONSHIPS WSR
WHERE WSR.PARENT_OBJECT_ID = l_p_object
AND WSR.PARENT_OBJECT_TYPE_ID = l_p_object_type
AND WSR.RELATIONSHIP_TYPE = 1;
SELECT MIN(WDJ2.SCHEDULED_START_DATE-WDJ1.SCHEDULED_COMPLETION_DATE)
INTO l_min_left_snap_window
FROM WIP_SCHED_RELATIONSHIPS WSR,
WIP_DISCRETE_JOBS WDJ1,
WIP_DISCRETE_JOBS WDJ2
WHERE WSR.PARENT_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_TYPE_ID = 1
AND WSR.CHILD_OBJECT_ID = l_parent_object_id
AND WSR.CHILD_OBJECT_TYPE_ID = l_parent_object_type_id
AND WSR.RELATIONSHIP_TYPE = 2
AND WSR.RELATIONSHIP_STATUS = 3
AND WDJ1.WIP_ENTITY_ID = WSR.PARENT_OBJECT_ID
-- AND WDJ1.STATUS_TYPE IN (3,4,5,6,7,12,14,15)
AND WDJ2.WIP_ENTITY_ID = l_parent_object_id
-- Commented out;bcos no status checks as per new design.
SELECT WSR2.CHILD_OBJECT_ID CHILD_OBJECT_ID
FROM WIP_SCHED_RELATIONSHIPS WSR2
WHERE WSR2.RELATIONSHIP_TYPE = 1
AND l_starting_obj_type_id = 1
CONNECT BY prior WSR2.CHILD_OBJECT_ID = WSR2.PARENT_OBJECT_ID
START WITH WSR2.PARENT_OBJECT_ID = l_starting_object_id
);
select * from
wip_sched_relationships
where relationship_type = 1
and parent_object_id = l_parent_object_id
and parent_object_type_id = l_parent_object_type_id;
select scheduled_start_date, scheduled_completion_date, status_type, date_completed
into l_wo_start_date, l_wo_end_date,l_status_type,l_date_completed
from wip_discrete_jobs
where wip_entity_id = l_relationship_record.child_object_id;
SELECT requested_start_date,due_date
INTO l_requested_start_date,l_requested_due_date
FROM WIP_DISCRETE_JOBS
WHERE wip_entity_id=l_parent_object_id;
select min(first_unit_start_date),max(last_unit_completion_date)
INTO l_op_start_date,l_op_end_date
from wip_operations
where wip_entity_id=l_parent_object_id;
UPDATE WIP_DISCRETE_JOBS set
scheduled_start_date = l_wo_start_date,
scheduled_completion_date = l_wo_end_date
where wip_entity_id = l_parent_object_id;