[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running, wip_constants.warning)
and not exists (select 1
from wip_job_schedule_interface wjsi
where wjdi.group_id = wjsi.group_id
and wjdi.parent_header_id = wjsi.header_id);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and not exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and not exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and not exists (select 1
from wip_job_schedule_interface wjsi
where wjsi.group_id = p_group_id
and wjdi.parent_header_id = wjsi.header_id);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and not exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id
and wdj.status_type in (wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold));
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and not exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id
and wdj.status_type in (wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold));
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id
and wdj.status_type in (wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold)
and wdj.firm_planned_flag = wip_constants.yes);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and exists (select 1
from wip_discrete_jobs wdj
where wjdi.wip_entity_id = wdj.wip_entity_id
and wjdi.organization_id = wdj.organization_id
and wdj.status_type in (wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold)
and wdj.firm_planned_flag = wip_constants.yes);
select min(operation_seq_num)
from wip_operations
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and ( wjdi.load_type in (wip_job_details.wip_resource,
wip_job_details.wip_sub_res,
wip_job_details.wip_res_usage)
or ( wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.operation_seq_num <> 1
)
)
and not exists (select 1
from wip_operations wo
where wjdi.wip_entity_id = wo.wip_entity_id
and wjdi.organization_id = wo.organization_id
and wjdi.operation_seq_num = wo.operation_seq_num);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and ( wjdi.load_type in (wip_job_details.wip_resource,
wip_job_details.wip_sub_res,
wip_job_details.wip_res_usage)
or ( wjdi.load_type = wip_job_details.wip_mtl_requirement
and wjdi.operation_seq_num <> 1
)
)
and not exists (select 1
from wip_operations wo
where wjdi.wip_entity_id = wo.wip_entity_id
and wjdi.organization_id = wo.organization_id
and wjdi.operation_seq_num = wo.operation_seq_num);
select count(*)
into l_num_first_op_rows
from wip_job_dtls_interface
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = wip_job_details.wip_mtl_requirement
and substitution_type = wip_job_details.wip_add
and operation_seq_num = 1;
update wip_job_dtls_interface
set operation_seq_num = l_first_op_seq_num
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running, wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = wip_job_details.wip_mtl_requirement
and substitution_type = wip_job_details.wip_add
and operation_seq_num = 1;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( load_type in (wip_job_details.wip_resource,
wip_job_details.wip_sub_res,
wip_job_details.wip_res_usage)
or ( load_type = wip_job_details.wip_mtl_requirement
and operation_seq_num <> 1
)
)
and not exists (select 1
from wip_operations wo
where wjdi.wip_entity_id = wo.wip_entity_id
and wjdi.organization_id = wo.organization_id
and wjdi.operation_seq_num = wo.operation_seq_num);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and ( load_type in (wip_job_details.wip_resource,
wip_job_details.wip_sub_res,
wip_job_details.wip_res_usage)
or ( load_type = wip_job_details.wip_mtl_requirement
and operation_seq_num <> 1
)
)
and not exists (select 1
from wip_operations wo
where wjdi.wip_entity_id = wo.wip_entity_id
and wjdi.organization_id = wo.organization_id
and wjdi.operation_seq_num = wo.operation_seq_num);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and ( wjdi.load_type not in (1, 2, 3, 4, 5, 6, 7, WIP_JOB_DETAILS.WIP_RES_INSTANCE, WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE) -- load_type must be resource or material
or wjdi.substitution_type not in (1, 2, 3)); -- substitution_type must be delete or add or change
update wip_job_dtls_interface
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( load_type not in (1, 2, 3, 4, 5)
or substitution_type not in (1, 2, 3));
procedure last_updated_by(p_group_id IN number,
p_parent_header_id IN number,
p_wip_entity_id IN number,
p_organization_id IN number) IS
cursor c_invalid_rows is
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and not exists (select 1
from fnd_user_view fu
where fu.user_id = wjdi.last_updated_by
and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1));
fnd_message.set_name('WIP', 'WIP_ML_LAST_UPDATED_BY');
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and not exists (select 1
from fnd_user_view fu
where fu.user_id = wjdi.last_updated_by
and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1));
end last_updated_by;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and not exists (select 1
from fnd_user_view fu
where fu.user_id = wjdi.created_by
and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1));
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and not exists (select 1
from fnd_user_view fu
where fu.user_id = wjdi.created_by
and sysdate between fu.start_date and nvl(fu.end_date,sysdate+1));
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and exists (select 1
from wip_job_dtls_interface wjdi2
where wjdi2.group_id = wjdi.group_id
and wjdi2.parent_header_id = wjdi.parent_header_id
and wjdi2.process_status = wip_constants.error
and wjdi2.wip_entity_id = wjdi.wip_entity_id
and wjdi2.organization_id = wjdi.organization_id);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and exists (select 1
from wip_job_dtls_interface wjdi2
where wjdi2.group_id = wjdi.group_id
and wjdi2.parent_header_id = wjdi.parent_header_id
and wjdi2.process_status = wip_constants.error
and wjdi2.wip_entity_id = wjdi.wip_entity_id
and wjdi2.organization_id = wjdi.organization_id);