[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type not in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_SCHED,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.request_id = decode(x_request_id,-1,wjsi.request_id,x_request_id),
wjsi.program_id = decode(x_program_id,-1,wjsi.program_id,x_program_id),
wjsi.program_application_id = decode(x_application_id,-1,wjsi.program_application_id, x_application_id)
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.created_by_name is not null
and wjsi.created_by is null
and not exists (select 1
from fnd_user usr
where usr.user_name = wjsi.created_by_name)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.created_by_name is not null
and wjsi.created_by is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.created_by = (select usr.user_id
from fnd_user usr
where usr.user_name = wjsi.created_by_name),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.created_by_name is not null
and wjsi.created_by is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and not exists (select 1
from fnd_user usr
where usr.user_id = wjsi.created_by
and sysdate between usr.start_date and nvl(end_date, sysdate))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.last_updated_by_name is not null
and wjsi.last_updated_by is null
and not exists (select 1
from fnd_user usr
where usr.user_name = wjsi.last_updated_by_name)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY_NAME', false);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.last_updated_by_name is not null
and wjsi.last_updated_by is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY_NAME', false);
update wip_job_schedule_interface wjsi
set wjsi.last_updated_by = (select usr.user_id
from fnd_user usr
where usr.user_name = wjsi.last_updated_by_name),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.last_updated_by_name is not null
and wjsi.last_updated_by is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and not exists (select 1
from fnd_user usr
where usr.user_id = wjsi.last_updated_by
and sysdate between usr.start_date and nvl(end_date, sysdate))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY', false);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.organization_code is not null
and wjsi.organization_id is null
and not exists (select 1
from mtl_parameters ood
where ood.organization_code = wjsi.organization_code)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.organization_code is not null
and wjsi.organization_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.organization_id = (select ood.organization_id
from mtl_parameters ood
where ood.organization_code = wjsi.organization_code),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.organization_code is not null
and wjsi.organization_id is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and not exists (select 1
from wip_parameters wp,
mtl_parameters mp,
hr_organization_units ood
where wp.organization_id = mp.organization_id
and wp.organization_id = ood.organization_id
and wp.organization_id = wjsi.organization_id
and sysdate < nvl(ood.date_to, sysdate + 1))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.job_name is not null
and wjsi.wip_entity_id is null
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and not exists (select 1
from wip_entities we
where we.wip_entity_name = wjsi.job_name
and we.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.job_name is not null
and ( (wjsi.wip_entity_id is not null
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB) OR
(wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED) )
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.job_name = fnd_profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.job_name is null
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB);
update wip_job_schedule_interface wjsi
set wjsi.wip_entity_id = (select we.wip_entity_id
from wip_entities we
where we.wip_entity_name = wjsi.job_name
and we.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.job_name is not null
and wjsi.wip_entity_id is null
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
and exists (select 1
from wip_entities we
where we.wip_entity_name = wjsi.job_name
and we.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.wip_entity_id is not null
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_NS_JOB,
WIP_CONSTANTS.CREATE_SCHED)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wip_entity_id = wip_entities_s.nextval,
last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.wip_entity_id is null
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_NS_JOB);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( ( wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and not exists (select 1
from wip_entities we
where we.organization_id = wjsi.organization_id
and we.wip_entity_id = wjsi.wip_entity_id))
OR ( wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
and exists (select 1
from wip_entities we
where we.organization_id = wjsi.organization_id
and we.wip_entity_id = wjsi.wip_entity_id)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and exists (select 1
from wip_entities we
where we.wip_entity_id = wjsi.wip_entity_id
and we.organization_id = wjsi.organization_id
and we.entity_type <> 1)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.repetitive_schedule_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.repetitive_schedule_id = wip_repetitive_schedules_s.nextval,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.schedule_group_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED or
wjsi.schedule_group_id is not null)
and wjsi.schedule_group_name is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.schedule_group_id is null
and wjsi.schedule_group_name is not null
and not exists (select 1
from wip_schedule_groups_val_v sg
where sg.schedule_group_name = wjsi.schedule_group_name
and sg.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.schedule_group_id =
(select sg.schedule_group_id
from wip_schedule_groups_val_v sg
where sg.schedule_group_name = wjsi.schedule_group_name
and sg.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.schedule_group_id is null
and wjsi.schedule_group_name is not null;
/* update wip_job_schedule_interface wjsi
set wjsi.schedule_group_id =
(select wdj.schedule_group_id
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and wjsi.schedule_group_id is null; */
update wip_job_schedule_interface wjsi
set wjsi.schedule_group_id =
(select wsg.schedule_group_id
from wip_schedule_groups wsg,
wsh_new_deliveries wds
where wds.delivery_id = wjsi.delivery_id
and wsg.schedule_group_name = wds.name
and wsg.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.schedule_group_id is null
and wjsi.source_code = 'WICDOL'
and wjsi.delivery_id is not null
and exists (select wsg.schedule_group_id
from wip_schedule_groups wsg,
wsh_new_deliveries wds
where wds.delivery_id = wjsi.delivery_id
and wsg.schedule_group_name = wds.name
and wsg.organization_id = wjsi.organization_id);
select wjsi.interface_id
bulk collect into l_interfaceTbl
from wip_job_schedule_interface wjsi
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.schedule_group_id is null
and wjsi.source_code = 'WICDOL'
and wjsi.delivery_id is not null;
select wip_schedule_groups_s.nextval into l_schedGroupID from dual;
insert into wip_schedule_groups(
schedule_group_id,
schedule_group_name,
organization_id,
description,
created_by,
last_updated_by,
creation_date,
last_update_date)
select l_schedGroupID,
wds.name,
wjsi.organization_id,
to_char(sysdate),
fnd_global.user_id,
fnd_global.user_id,
sysdate,
sysdate
from wsh_new_deliveries wds,
wip_job_schedule_interface wjsi
where wds.delivery_id = wjsi.delivery_id
and wjsi.interface_id = l_interfaceTbl(i);
update wip_job_schedule_interface
set schedule_group_id = l_schedGroupID,
last_update_date = sysdate
where interface_id = l_interfaceTbl(i);
l_interfaceTbl.delete;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.RESCHED_JOB,
WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.schedule_group_id is not null
and not exists (select 1
from wip_schedule_groups_val_v sg
where sg.schedule_group_id = wjsi.schedule_group_id
and sg.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.build_sequence is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.line_code is not null
and wjsi.line_id is null
and not exists (select 1
from wip_lines_val_v wl
where wl.line_code = wjsi.line_code
and wl.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.line_code is not null
and wjsi.line_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set line_id = (select wl.line_id
from wip_lines_val_v wl
where wl.line_code = wjsi.line_code
and wl.organization_id = wjsi.organization_id),
last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and line_code is not null
and line_id is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.line_id is not null
and not exists (select 1
from wip_lines_val_v wl
where wl.line_id = wjsi.line_id
and wl.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.serialization_start_op is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.project_number is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.project_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.task_number is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.task_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.firm_planned_flag = WIP_CONSTANTS.NO,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.firm_planned_flag is null
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_NS_JOB,
WIP_CONSTANTS.CREATE_SCHED);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB and
wjsi.firm_planned_flag = WIP_CONSTANTS.YES)
or (wjsi.firm_planned_flag = WIP_CONSTANTS.YES and
wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id))
or (wjsi.firm_planned_flag not in (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.description = l_description,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.description is null
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_NS_JOB);
update wip_job_schedule_interface wjsi
set wjsi.description = l_description,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.description is null
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = wip_constants.resched_job or
wjsi.primary_item_id is not null)
and wjsi.primary_item_segments is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and wjsi.primary_item_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set primary_item_id = (select wdj.primary_item_id
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id)
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.primary_item_segments is not null
and wjsi.primary_item_id is null
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.create_sched)
and not exists (select 1
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.primary_item_segments)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.primary_item_id = (select msik.inventory_item_id
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.primary_item_segments),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.create_sched)
and wjsi.primary_item_segments is not null
and wjsi.primary_item_id is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.primary_item_id is not null
and ( wjsi.load_type in (wip_constants.create_job, wip_constants.create_sched)
or (wjsi.load_type = wip_constants.create_ns_job and wjsi.primary_item_id is not null) )
and not exists (select 1
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.primary_item_id is null and
wjsi.load_type in (wip_constants.create_job, wip_constants.create_sched))
or (wjsi.primary_item_id is not null and
wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.create_sched) and
( 'Y' <> (select msi.build_in_wip_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id) or
'N' <> (select msi.pick_components_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id) or
(l_see_eng_items_flag = wip_constants.no and
'Y' = (select msi.eng_item_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id)))))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and not exists (select 1
from wip_repetitive_items wri
where wri.line_id = wjsi.line_id
and wri.primary_item_id = wjsi.primary_item_id
and wri.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
/* update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_sched,
wip_constants.create_ns_job)
and wjsi.primary_item_id is not null
and 'Y' = (select msi.replenish_to_order_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id)
and 4 = (select msi.bom_item_type
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.primary_item_id)
and not exists (select 1
from bom_bill_of_materials bom
where bom.assembly_item_id = wjsi.primary_item_id
and bom.organization_id = wjsi.organization_id
and nvl(bom.alternate_bom_designator, '@@@') =
nvl(wjsi.alternate_bom_designator, '@@@')
and (bom.assembly_type = 1 or l_see_eng_items_flag = 1))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
and wjsi.status_type is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.status_type = WIP_CONSTANTS.UNRELEASED,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.status_type is null;
update wip_job_schedule_interface wjsi
set wjsi.status_type = (select wdj.status_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and wjsi.status_type is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.load_type in (WIP_CONSTANTS.CREATE_NS_JOB,
WIP_CONSTANTS.CREATE_JOB) and
wjsi.status_type not in (WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.HOLD))
or (wjsi.load_type = wip_constants.resched_job and
wjsi.status_type is not null and
wjsi.status_type not in (wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold,
wip_constants.cancelled)) )
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( wjsi.load_type in (wip_constants.create_job,
wip_constants.create_sched,
wip_constants.resched_job)
or (wjsi.load_type = wip_constants.create_ns_job and
wjsi.routing_reference_id is not null) )
and wjsi.routing_reference_segments is not null
and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_sched,
wip_constants.resched_job)
and wjsi.routing_reference_id is not null
and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
and WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.routing_reference_segments is not null
and wjsi.routing_reference_id is null
and not exists (select 1
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.routing_reference_segments)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set routing_reference_id = decode(wjsi.routing_reference_segments,null,(select wdj.routing_reference_id
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id),
(select inventory_item_id
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.routing_reference_segments)),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
--and wjsi.routing_reference_segments is not null
and wjsi.routing_reference_id is null ;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.routing_reference_id is not null
and not exists (select 1
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.inventory_item_id = wjsi.routing_reference_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.routing_reference_id is not null
and ('Y' <> (select msi.build_in_wip_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.routing_reference_id) or
'N' <> (select msi.pick_components_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.routing_reference_id) or
(l_see_eng_items_flag = wip_constants.no and
'Y' = (select msi.eng_item_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.routing_reference_id)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
When g_miss_char is passed for alt routing designator, updated to primary routing/bom.*/
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.alternate_routing_designator <> fnd_api.g_miss_char
and ( (wjsi.alternate_routing_designator is not null
and not exists (select 1
from bom_operational_routings bor
where bor.alternate_routing_designator = wjsi.alternate_routing_designator
and bor.organization_id = wjsi.organization_id
and bor.assembly_item_id = decode(wjsi.load_type,
wip_constants.create_ns_job, wjsi.routing_reference_id,
wip_constants.resched_job, decode((select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
1, wjsi.primary_item_id, wjsi.routing_reference_id),
wjsi.primary_item_id)
and nvl(bor.cfm_routing_flag, 2) = 2
and (bor.routing_type = 1 or (bor.routing_type = 2 and 1 = to_number(l_see_eng_items_flag))) ))
/* bug 4227345 */
or (wjsi.alternate_routing_designator = fnd_api.g_miss_char
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.create_sched)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
When g_miss_char is passed for alt routing designator, updated to primary routing/bom.*/
update wip_job_schedule_interface wjsi
set wjsi.alternate_routing_designator = decode(wjsi.alternate_routing_designator,fnd_api.g_miss_char,null,null,(select wdj.alternate_routing_designator
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_routing_designator),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and (wjsi.alternate_routing_designator is null
or wjsi.alternate_routing_designator = fnd_api.g_miss_char);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.alternate_routing_designator, '@@-@@@') <>
nvl( (select wdj.alternate_routing_designator
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
and (select status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and wjsi.routing_revision_date is not null
and wjsi.routing_revision is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.routing_revision = (select wdj.routing_revision
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and (wjsi.bom_revision = fnd_api.g_miss_char
or wjsi.bom_revision is null);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.routing_revision, '@@-@@@') <>
nvl( (select wdj.routing_revision
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
and (select status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.routing_revision_date = (select wdj.routing_revision_date
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and ( wjsi.routing_revision_date is null
or wjsi.routing_revision_date = fnd_api.g_miss_date);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.routing_revision_date, fnd_api.g_miss_date) <>
nvl((select wdj.routing_revision_date
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
and (select wdj.status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( wjsi.load_type in (wip_constants.create_job,
wip_constants.create_sched,
wip_constants.resched_job)
or (wjsi.load_type = wip_constants.create_ns_job and
wjsi.bom_reference_id is not null) )
and wjsi.bom_reference_segments is not null
and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_sched,
wip_constants.resched_job)
and wjsi.bom_reference_id is not null
and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.bom_reference_segments is not null
and wjsi.bom_reference_id is null
and not exists (select 1
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.bom_reference_segments)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set bom_reference_id = decode(wjsi.bom_reference_segments,null,(select wdj.bom_reference_id
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id),
(select inventory_item_id
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.concatenated_segments = wjsi.bom_reference_segments)),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
--and wjsi.bom_reference_segments is not null
and (wjsi.bom_reference_id is null or wjsi.bom_reference_id=fnd_api.g_miss_num);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.bom_reference_id is not null
and not exists (select 1
from mtl_system_items_kfv msik
where msik.organization_id = wjsi.organization_id
and msik.inventory_item_id = wjsi.bom_reference_id)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id)))
and wjsi.bom_reference_id is not null
and ('Y' <> (select msi.build_in_wip_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.bom_reference_id) or
'N' <> (select msi.pick_components_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.bom_reference_id) or
(l_see_eng_items_flag = wip_constants.no and
'Y' = (select msi.eng_item_flag
from mtl_system_items msi
where msi.organization_id = wjsi.organization_id
and msi.inventory_item_id = wjsi.bom_reference_id)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
When g_miss_char is passed for alt bom designator, updated to primary routing/bom. */
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.alternate_bom_designator <> fnd_api.g_miss_char
and ( (wjsi.alternate_bom_designator is not null
and not exists (select 1
from bom_bill_alternates_v bba
where bba.alternate_bom_designator = wjsi.alternate_bom_designator
and bba.organization_id = wjsi.organization_id
and bba.assembly_item_id = decode(wjsi.load_type,
wip_constants.create_ns_job, wjsi.bom_reference_id,
wip_constants.resched_job, decode((select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
1, wjsi.primary_item_id, wjsi.bom_reference_id),
wjsi.primary_item_id)
and (bba.assembly_type = 1 or (bba.assembly_type = 2 and 1 = to_number(l_see_eng_items_flag))) ))
/* bug 4227345 */
or (wjsi.alternate_bom_designator = fnd_api.g_miss_char
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.create_sched)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
When g_miss_char is passed for alt bom designator, updated to primary routing/bom. */
update wip_job_schedule_interface wjsi
set wjsi.alternate_bom_designator = decode(wjsi.alternate_bom_designator,fnd_api.g_miss_char,null,null,
(select wdj.alternate_bom_designator
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_bom_designator),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and (wjsi.alternate_bom_designator is null
or wjsi.alternate_bom_designator =fnd_api.g_miss_char);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.alternate_bom_designator, '@@-@@@') <>
nvl( (select wdj.alternate_bom_designator
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
and (select status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and wjsi.bom_revision_date is not null
and wjsi.bom_revision is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.bom_revision = (select wdj.bom_revision
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and (wjsi.bom_revision = fnd_api.g_miss_char
or wjsi.bom_revision is null);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.bom_revision, '@@-@@@') <>
nvl( (select wdj.bom_revision
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
and (select status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.bom_revision_date = (select wdj.bom_revision_date
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and ( wjsi.bom_revision_date = fnd_api.g_miss_date
or wjsi.bom_revision_date is null);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and nvl(wjsi.bom_revision_date, fnd_api.g_miss_date) <>
nvl( (select wdj.bom_revision_date
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
and (select status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_sched, wip_constants.resched_job)
and wjsi.wip_supply_type is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.wip_supply_type = wip_constants.based_on_bom,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
and wjsi.wip_supply_type is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.load_type in (WIP_CONSTANTS.CREATE_NS_JOB,
WIP_CONSTANTS.CREATE_JOB) and
wjsi.wip_supply_type not in (wip_constants.push,
wip_constants.assy_pull,
wip_constants.op_pull,
wip_constants.bulk,
wip_constants.vendor,
wip_constants.phantom,
wip_constants.based_on_bom))
or (wjsi.load_type = wip_constants.create_ns_job and
wjsi.primary_item_id is null and
wjsi.wip_supply_type in (wip_constants.assy_pull,
wip_constants.op_pull))
or (wjsi.wip_supply_type = wip_constants.op_pull and
not exists
(select 1
from bom_operational_routings bor
where bor.organization_id = wjsi.organization_id
and bor.assembly_item_id = decode(wjsi.load_type,
wip_constants.create_ns_job, wjsi.routing_reference_id,
wjsi.primary_item_id)
and nvl(alternate_routing_designator, '@@@') =
nvl(wjsi.alternate_routing_designator, '@@@')
and nvl(cfm_routing_flag, 2) = 2)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.start_quantity is not null
and ( wjsi.load_type = wip_constants.create_sched
or (wjsi.load_type = wip_constants.resched_job and
(select wdj.status_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) not in
(wip_constants.unreleased,
wip_constants.released,
wip_constants.comp_chrg,
wip_constants.hold)) )
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( wjsi.start_quantity < 0
or (wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
wjsi.start_quantity is null)
or (wjsi.load_type = wip_constants.create_job and
wjsi.start_quantity = 0)
or (wjsi.load_type = wip_constants.resched_job and
wjsi.start_quantity = 0 and
wip_constants.standard = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and wjsi.start_quantity is not null
and ( wjsi.start_quantity < (select wdj.quantity_completed
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id)
or (0 < (select count(*)
from wip_reservations_v wr
where wr.wip_entity_id = wjsi.wip_entity_id
and wr.organization_id = wjsi.organization_id) and
wjsi.start_quantity < (select sum(wr.primary_quantity)
from wip_reservations_v wr
where wr.organization_id = wjsi.organization_id
and wr.wip_entity_id = wjsi.wip_entity_id)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and wjsi.net_quantity is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.net_quantity = wjsi.start_quantity,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_job
and wjsi.net_quantity is null;
update wip_job_schedule_interface wjsi
set wjsi.net_quantity = (select decode(wdj.net_quantity,
wdj.start_quantity, wjsi.start_quantity,
least(wdj.net_quantity, nvl(wjsi.start_quantity, wdj.net_quantity)))
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.resched_job
and wjsi.net_quantity is null;
update wip_job_schedule_interface wjsi
set wjsi.net_quantity = 0,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_ns_job
and wjsi.net_quantity is null;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.net_quantity is not null
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.resched_job)
and ( wjsi.net_quantity not between 0 and (select nvl(wjsi.start_quantity,wdj.start_quantity)
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) /*Fix for Bug 6522139*/
or ( wjsi.net_quantity <> 0
and ((wjsi.load_type = wip_constants.create_ns_job and wjsi.primary_item_id is null) or
(wjsi.load_type = wip_constants.resched_job and
wip_constants.nonstandard = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) and
(select wdj.primary_item_id
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) is null))))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.overcompletion_tolerance_type is not null
and ( wjsi.load_type = wip_constants.create_sched
or (wjsi.load_type = wip_constants.create_ns_job and
wjsi.primary_item_id is null))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.overcompletion_tolerance_value is not null
and ( wjsi.load_type = wip_constants.create_sched
or (wjsi.load_type = wip_constants.create_ns_job and
wjsi.primary_item_id is null))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.due_date is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.due_date = wjsi.last_unit_completion_date,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.due_date is null
and wjsi.requested_start_date is null
and wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.date_released is not null
and wjsi.status_type = wip_constants.unreleased
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and nvl(wjsi.date_released, sysdate) > sysdate
and wjsi.status_type = wip_constants.released
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.date_released = sysdate,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.date_released > sysdate
and wjsi.status_type = wip_constants.released;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.status_type = wip_constants.released
and exists (select 1
from wip_discrete_jobs
where wip_entity_id = wjsi.wip_entity_id
and organization_Id = wjsi.organization_id
and (status_type IS NULL OR
status_type NOT IN (wip_constants.released,wip_constants.comp_chrg,
wip_constants.cancelled, wip_constants.hold)))
and not exists (select 1
from org_acct_periods oap
where oap.organization_id = wjsi.organization_id
and trunc(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(wjsi.date_released,sysdate), wjsi.organization_id))
between oap.period_start_date and oap.schedule_close_date
and oap.period_close_date is null)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.requested_start_date is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.requested_start_date = wjsi.first_unit_start_date,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.due_date is null
and wjsi.requested_start_date is null
and wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job);
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.header_id is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.processing_work_days is not null
and wjsi.load_type <> wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and (wjsi.processing_work_days <= 0 or wjsi.processing_work_days is null)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.daily_production_rate is not null
and wjsi.load_type <> wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_sched
and (wjsi.daily_production_rate <= 0 or wjsi.daily_production_rate is null)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.demand_class is not null
and wjsi.load_type = wip_constants.resched_job
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_sched,
wip_constants.create_job,
wip_constants.create_ns_job)
and wjsi.demand_class is not null
and not exists (select 1
from so_demand_classes_active_v sdc
where sdc.demand_class_code = wjsi.demand_class)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.completion_subinventory is not null
and wjsi.load_type in (wip_constants.resched_job,
wip_constants.create_sched)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.completion_subinventory =
(select bor.completion_subinventory
from bom_operational_routings bor
where bor.organization_id = wjsi.organization_id
and nvl(bor.cfm_routing_flag,2) = 2
and bor.assembly_item_id = Decode(wjsi.load_type,
wip_constants.create_job,
wjsi.primary_item_id,
wip_constants.create_ns_job,
nvl(wjsi.routing_reference_id,wjsi.primary_item_id)) -- Bug 9765343 for ns job
and nvl(bor.alternate_routing_designator,'@@@') =
nvl(wjsi.alternate_routing_designator, '@@@')),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.completion_subinventory is null
and wjsi.load_type IN (wip_constants.create_job,
wip_constants.create_ns_job) ; -- Bug 9765343 for ns job
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type = wip_constants.create_ns_job
and wjsi.primary_item_id is null
and wjsi.completion_subinventory is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.completion_locator_id is not null
and wjsi.load_type in (wip_constants.resched_job,
wip_constants.create_sched)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.completion_locator_id is not null
and wjsi.completion_locator_segments is not null
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.completion_locator_id =
(select bor.completion_locator_id
from bom_operational_routings bor
where bor.organization_id = wjsi.organization_id
and nvl(bor.cfm_routing_flag, 2) = 2
and bor.assembly_item_id = Decode(wjsi.load_type,
wip_constants.create_job,
wjsi.primary_item_id,
wip_constants.create_ns_job,
nvl(wjsi.routing_reference_id,wjsi.primary_item_id)) -- Bug 9765343 for ns job
and nvl(bor.alternate_routing_designator,'@@@') =
nvl(wjsi.alternate_routing_designator, '@@@')
and bor.completion_subinventory = wjsi.completion_subinventory),
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.completion_locator_id is null
and wjsi.completion_locator_segments is null
and wjsi.load_type IN (wip_constants.create_job,
wip_constants.create_ns_job) ; -- Bug 9765343 for ns job
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_ns_job, wip_constants.create_job)
and wjsi.completion_subinventory is null
and wjsi.completion_locator_id is not null
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.lot_number is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.source_code is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.source_line_id is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.scheduling_method is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.scheduling_method = wip_constants.ml_manual,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.scheduling_method is null
and wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
and wjsi.routing_reference_id is null;
update wip_job_schedule_interface wjsi
set wjsi.scheduling_method = wip_constants.routing,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.scheduling_method is null
and ( wjsi.allow_explosion is null
or upper(wjsi.allow_explosion) <> 'N');
update wip_job_schedule_interface wjsi
set wjsi.scheduling_method = wip_constants.ml_manual,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.scheduling_method is null
and upper(wjsi.allow_explosion) = 'N';
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.load_type in (wip_constants.create_ns_job,
wip_constants.create_job,
wip_constants.resched_job)
and wjsi.scheduling_method not in (wip_constants.routing,
wip_constants.leadtime,
wip_constants.ml_manual)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.scheduling_method = wip_constants.leadtime
and ((wjsi.load_type = wip_constants.create_ns_job and
wjsi.routing_reference_id is null) or
(wjsi.load_type = wip_constants.resched_job and
(select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) = wip_constants.nonstandard and
(select wdj.routing_reference_id
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) is null)))
or (wjsi.scheduling_method = wip_constants.ml_manual
and (wjsi.first_unit_start_date is null or
wjsi.last_unit_completion_date is null or
wjsi.first_unit_start_date > wjsi.last_unit_completion_date)))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.scheduling_method = wip_constants.routing
and wjsi.allow_explosion in ('n', 'N')
and wjsi.load_type = wip_constants.create_job
and (wjsi.first_unit_start_date is null or
wjsi.last_unit_completion_date is null)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.allow_explosion is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.priority is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.end_item_unit_number is not null
and wjsi.load_type in (wip_constants.create_sched,
wip_constants.resched_job)
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and ( (wjsi.load_type = wip_constants.create_ns_job and
--must provide both dates when creating a ns job
wjsi.routing_reference_id is null and
(wjsi.first_unit_start_date is null or wjsi.last_unit_completion_date is null))
or (wjsi.load_type = wip_constants.resched_job and
--when rescheduling a ns job and providing one date, it must have a routing
wip_constants.nonstandard = (select wdj.job_type
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) and
/* (select wdj.routing_reference_id
from wip_discrete_jobs wdj
where wdj.organization_id = wjsi.organization_id
and wdj.wip_entity_id = wjsi.wip_entity_id) */ wjsi.routing_reference_id is null and
(select count(*)
from wip_operations
where organization_id = wjsi.organization_id
and wip_entity_id = wjsi.wip_entity_id) = 0 and
((wjsi.first_unit_start_date is not null or wjsi.last_unit_completion_date is not null)
/* Bug fix : 8407567 If scheduling method is manual, then both the dates are needed */
and wjsi.scheduling_method <> wip_constants.ml_manual))
or (wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
--all job creations must have at least one date
wjsi.first_unit_start_date is null and
wjsi.last_unit_completion_date is null)
or (wjsi.load_type = wip_constants.resched_job and
--when changing the quantity, you must also provide a date
wjsi.start_quantity is not null and
wjsi.first_unit_start_date is null and
wjsi.last_unit_completion_date is null)
-- Commented this for Bug 13620858. Forms allow re-scheduling based on routing without re-explosion.
/* or (wjsi.load_type = wip_constants.resched_job and
--if not exploding, then the user must provide both dates or none at all
wjsi.allow_explosion in ('N', 'n') and
((wjsi.first_unit_start_date is not null and wjsi.last_unit_completion_date is null) or
(wjsi.first_unit_start_date is null and wjsi.last_unit_completion_date is not null))) */
or (wjsi.first_unit_start_date is not null and
not exists (select 1
from bom_calendar_dates bcd,
mtl_parameters mp
where mp.organization_id = wjsi.organization_id
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date = trunc(wjsi.first_unit_start_date)))
or (wjsi.last_unit_completion_date is not null and
not exists (select 1
from bom_calendar_dates bcd,
mtl_parameters mp
where mp.organization_id = wjsi.organization_id
and mp.calendar_code = bcd.calendar_code
and mp.calendar_exception_set_id = bcd.exception_set_id
and bcd.calendar_date = trunc(wjsi.last_unit_completion_date))))
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.last_unit_start_date is not null
and wjsi.load_type <> wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.WARNING,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.first_unit_completion_date is not null
and wjsi.load_type <> wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
update wip_job_schedule_interface wjsi
set wjsi.process_status = WIP_CONSTANTS.ERROR,
wjsi.last_update_date = sysdate
where wjsi.group_id = p_groupID
and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
and wjsi.serialization_start_op is not null
and wjsi.load_type = wip_constants.create_sched
returning wjsi.interface_id bulk collect into l_interfaceTbl;
select rowid,
interface_id
from wip_job_schedule_interface
where group_id = p_groupID
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
select ood.operating_unit
into l_operatingUnit
from wip_parameters wp,
mtl_parameters mp,
org_organization_definitions ood
where wp.organization_id = mp.organization_id
and wp.organization_id = ood.organization_id
and wp.organization_id = wjsi_row.organization_id
and sysdate < nvl(ood.disable_date, sysdate+1);
SELECT
decode(hoi.org_information_context, 'Accounting Information',
to_number(hoi.org_information3), to_number(null)) operating_unit
INTO l_operatingUnit
FROM hr_organization_units hou,
wip_parameters wp,
mtl_parameters mp,
hr_organization_information hoi
WHERE hou.organization_id = hoi.organization_id
and ( hoi.org_information_context || '') = 'Accounting Information'
and wp.organization_id = mp.organization_id
and wp.organization_id = hou.organization_id
and wp.organization_id = wjsi_row.organization_id
and sysdate < nvl(hou.date_to, sysdate+1);
update wip_job_schedule_interface
set project_id = wjsi_row.project_id,
task_id = wjsi_row.task_id,
status_type = wjsi_row.status_type,
class_code = wjsi_row.class_code,
overcompletion_tolerance_type = wjsi_row.overcompletion_tolerance_type,
overcompletion_tolerance_value = wjsi_row.overcompletion_tolerance_value,
first_unit_start_date = to_date(to_char(wjsi_row.first_unit_start_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
last_unit_start_date = to_date(to_char(wjsi_row.last_unit_start_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
first_unit_completion_date = to_date(to_char(wjsi_row.first_unit_completion_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
last_unit_completion_date = to_date(to_char(wjsi_row.last_unit_completion_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
build_sequence = wjsi_row.build_sequence,
bom_revision = wjsi_row.bom_revision,
routing_revision = wjsi_row.routing_revision,
bom_revision_date = wjsi_row.bom_revision_date,
routing_revision_date = wjsi_row.routing_revision_date,
due_date = to_date(to_char(wjsi_row.due_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
requested_start_date = to_date(to_char(wjsi_row.requested_start_date,
wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
lot_number = wjsi_row.lot_number,
completion_subinventory = wjsi_row.completion_subinventory,
completion_locator_id = wjsi_row.completion_locator_id,
scheduling_method = wjsi_row.scheduling_method,
end_item_unit_number = wjsi_row.end_item_unit_number
where rowid = line.rowid;
select *
into wjsi_row
from wip_job_schedule_interface
where rowid = p_rowid;
select we.wip_entity_name,
wdj.status_type,
we.entity_type,
wdj.job_type,
wdj.start_quantity,
wdj.quantity_completed,
wdj.firm_planned_flag,
wdj.primary_item_id,
wdj.bom_reference_id,
wdj.routing_reference_id,
wdj.line_id,
wdj.schedule_group_id,
wdj.scheduled_completion_date,
wdj.project_id,
wdj.task_id,
wdj.overcompletion_tolerance_type,
wdj.overcompletion_tolerance_value,
wdj.completion_subinventory,
wdj.completion_locator_id,
wdj.build_sequence,
wdj.class_code
into wdj_row.wip_entity_name,
wdj_row.status_type,
wdj_row.entity_type,
wdj_row.job_type,
wdj_row.start_quantity,
wdj_row.quantity_completed,
wdj_row.firm_planned_flag,
wdj_row.primary_item_id,
wdj_row.bom_reference_id,
wdj_row.routing_reference_id,
wdj_row.line_id,
wdj_row.schedule_group_id,
wdj_row.scheduled_completion_date,
wdj_row.project_id,
wdj_row.task_id,
wdj_row.overcompletion_tolerance_type,
wdj_row.overcompletion_tolerance_value,
wdj_row.completion_subinventory,
wdj_row.completion_locator_id,
wdj_row.build_sequence,
wdj_row.class_code
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and we.wip_entity_id = wdj.wip_entity_id;
select inventory_item_id,
pick_components_flag,
build_in_wip_flag,
eng_item_flag,
inventory_asset_flag,
restrict_subinventories_code,
restrict_locators_code,
location_control_code,
fixed_lead_time,
variable_lead_time
into primary_item_row.inventory_item_id,
primary_item_row.pick_components_flag,
primary_item_row.build_in_wip_flag,
primary_item_row.eng_item_flag,
primary_item_row.inventory_asset_flag,
primary_item_row.restrict_subinventories_code,
primary_item_row.restrict_locators_code,
primary_item_row.location_control_code,
primary_item_row.fixed_lead_time,
primary_item_row.variable_lead_time
from mtl_system_items
where inventory_item_id = l_primaryItemID
and organization_id = wjsi_row.organization_id;
select count(*)
into l_rtgCount
from bom_operational_routings
where assembly_item_id = wjsi_row.routing_reference_id
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@@') =
nvl(wjsi_row.alternate_routing_designator, '@@@')
and nvl(cfm_routing_flag, 2) = 2;
select count(*)
into l_rtgCount
from bom_operational_routings
where assembly_item_id = wjsi_row.primary_item_id
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@@') =
nvl(wjsi_row.alternate_routing_designator, '@@@')
and nvl(cfm_routing_flag, 2) = 2;
select count(*)
into l_rtgCount
from wip_operations
where wip_entity_id = wjsi_row.wip_entity_id;
select count(*)
into l_rtgCount
from bom_operational_routings
where organization_id = wjsi_row.organization_id
and assembly_item_id = nvl(wjsi_row.routing_reference_id,wjsi_row.primary_item_id)
and nvl(alternate_routing_designator, '@@@^@@@') = nvl(wjsi_row.alternate_routing_designator, '@@@^@@@')
and nvl(cfm_routing_flag, 2) = 2;
select wdj.scheduled_start_date into wjsi_row.first_unit_start_date
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id;
SELECT TO_NUMBER(org_information3) into l_ou_context
FROM hr_organization_information
WHERE org_information_context = 'Accounting Information'
AND organization_id = wjsi_row.organization_id;
select mpv.project_id --if the project has tasks, this query returns multiple rows
into wjsi_row.project_id
from pjm_projects_v mpv,
pjm_project_parameters ppp
where mpv.project_number = wjsi_row.project_number
and mpv.project_id = ppp.project_id
and ppp.organization_id = wjsi_row.organization_id;
select mpv.project_id --this query will return multiple rows if the project has tasks
into l_dummy
from pjm_projects_v mpv,
pjm_project_parameters ppp,
mtl_parameters mp
where mpv.project_id = ppp.project_id
and mpv.project_id = wjsi_row.project_id
and ppp.organization_id = wjsi_row.organization_id
and ppp.organization_id = mp.organization_id
and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
select pa.task_id
into wjsi_row.task_id
from pa_tasks_expend_v pa,
wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and pa.project_id = nvl(wjsi_row.project_id, wdj.project_id)
and pa.task_number = wjsi_row.task_number;
select task_id
into wjsi_row.task_id
from pa_tasks_expend_v
where project_id = wjsi_row.project_id
and task_number = wjsi_row.task_number;
wip_utilities.get_message_stack(p_delete_stack => 'T',
p_msg => x_errorMsg);
select wdj.class_code
into wjsi_row.class_code
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and wdj.organization_id = wjsi_row.organization_id;
select 1 into l_dummy
from
( SELECT DISTINCT ccwac.class_code
from cst_cg_wip_acct_classes_v ccwac,mtl_parameters mp
where ccwac.class_code = wjsi_row.class_code
and ccwac.organization_id = wjsi_row.organization_id
and ccwac.class_type = wip_constants.discrete
AND ccwac.organization_id=mp.organization_id
AND ( not exists
(SELECT 1
FROM cst_cg_wip_acct_classes_v ccwac2 ,mtl_parameters mp
WHERE mp.organization_id=wjsi_row.organization_id
AND ccwac2.cost_group_id = mp.default_cost_group_id
AND ccwac2.organization_id = mp.organization_id
AND ccwac2.class_type=wip_constants.discrete
)
OR ( ccwac.cost_group_id = mp.default_cost_group_id )));
select 1 into l_dummy
from dual
where exists(select 1
from cst_cg_wip_acct_classes_v ccwac,
mtl_parameters mp
where ccwac.class_code = wjsi_row.class_code
and ccwac.organization_id = wjsi_row.organization_id
and ccwac.class_type = wip_constants.discrete
and mp.organization_id = wjsi_row.organization_id
and ( mp.primary_cost_method = wip_constants.cost_std
or ccwac.cost_group_id = (select costing_group_id
from mrp_project_parameters mpp
where organization_id = wjsi_row.organization_id
and mpp.project_id = wjsi_row.project_id)));
select 1 into l_dummy
from dual
where exists(select 1
from wip_non_standard_classes_val_v
where class_code = wjsi_row.class_code
and organization_id = wjsi_row.organization_id);
select bom_revision_date
into wjsi_row.bom_revision_date
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id;
select count(*)
into l_count
from bom_operational_routings
where assembly_item_id = decode(wjsi_row.load_type,
wip_constants.create_ns_job, wjsi_row.routing_reference_id,
wjsi_row.primary_item_id
)
and organization_id = wjsi_row.organization_id
and nvl(alternate_routing_designator, '@@@') =
nvl(wjsi_row.alternate_routing_designator, '@@@');
select routing_revision_date
into wjsi_row.routing_revision_date
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id;
select nvl(min(operation_seq_num), fnd_api.g_miss_num)
into l_minOp
from wip_operations
where organization_id = wjsi_row.organization_id
and wip_entity_id = wjsi_row.wip_entity_id
and nvl(repetitive_schedule_id, -1) = nvl(wjsi_row.repetitive_schedule_id, -1);
select quantity_in_queue,
scheduled_quantity
into l_queueQty,
l_scheduledQty
from wip_operations
where organization_id = wjsi_row.organization_id
and wip_entity_id = wjsi_row.wip_entity_id
and nvl(repetitive_schedule_id, -1) = nvl(wjsi_row.repetitive_schedule_id, -1)
and operation_seq_num = l_minOp;
select nvl(sum(wr.primary_quantity), 0)
from wip_reservations_v wr
where wr.wip_entity_id = wjsi_row.wip_entity_id
and wr.inventory_item_id = nvl(wjsi_row.primary_item_id, wdj_row.primary_item_id)
and wr.organization_id = wjsi_row.organization_id;
select status_type
into l_old_status
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id ;
select propagate_job_change_to_po
into l_propagate_job_change_to_po
from wip_parameters wp
where organization_id = wjsi_row.organization_id;
l_msg := 'WIP_CANT_UPDATE_JOB';
select 1
into l_dummy
from pjm_unit_numbers_lov_v pun,
mtl_parameters mp
where pun.unit_number = wjsi_row.end_item_unit_number
and mp.organization_id = wjsi_row.organization_id
and mp.master_organization_id = pun.master_organization_id;
select wdj.end_item_unit_number
into wjsi_row.end_item_unit_number
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id;
select primary_item_id
into wjsi_row.bom_reference_id
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id;
select end_item_unit_number into wjsi_row.end_item_unit_number
from wip_discrete_jobs
where wip_entity_id =
(select wip_entity_id
from wip_job_schedule_interface
where rowid = p_rowid
);
select daily_maximum_rate
into l_maxLineRate
from wip_lines_val_v
where line_id = wjsi_row.line_id;
select count(*)
into l_lineCount
from wip_lines
where organization_id = wjsi_row.organization_id
and line_id = wjsi_row.line_id
and line_schedule_type = 1; --fixed
select count(*)
into l_rtgCount
from bom_operational_routings bor,
wip_repetitive_items wri
where wri.line_id = wjsi_row.line_id
and nvl(bor.cfm_routing_flag, 2) = 2 --ignore flow rtgs
and wri.primary_item_id = wjsi_row.primary_item_id
and wri.organization_id = wjsi_row.organization_id
and nvl(bor.alternate_routing_designator,'@@@') = nvl(wri.alternate_routing_designator, '@@@')
and bor.organization_id = wri.organization_id
and bor.assembly_item_id = wri.primary_item_id;
select count(*)
into l_rtgCount
from bom_operational_routings bor,
wip_repetitive_items wri
where wri.line_id = wjsi_row.line_id
and nvl(bor.cfm_routing_flag,2) = 2 --ignore flow routings
and wri.primary_item_id = wjsi_row.primary_item_id
and wri.organization_id = wjsi_row.organization_id
and nvl(bor.alternate_routing_designator,'@@@') = nvl(wri.alternate_routing_designator,'@@@')
and bor.organization_id = wri.organization_id
and bor.assembly_item_id = wri.primary_item_id;
select count(*)
into l_lineCount
from wip_lines_val_v
where organization_id = wjsi_row.organization_id
and line_id = wjsi_row.line_id
and line_schedule_type = 2;
select calendar_date
into wjsi_row.first_unit_start_date
from bom_calendar_dates bcd,
mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.first_unit_start_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
and b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.last_unit_start_date
from bom_calendar_dates bcd,
mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.last_unit_start_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
and b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.first_unit_completion_date
from bom_calendar_dates bcd,
mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.first_unit_completion_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
and b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
and b2.calendar_code = mp.calendar_code;
select calendar_date
into wjsi_row.last_unit_completion_date
from bom_calendar_dates bcd, mtl_parameters mp
where mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
from bom_calendar_dates b2
where b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
and b2.calendar_code = bcd.calendar_code
and b2.exception_set_id = bcd.exception_set_id);
SELECT bcd.calendar_date
INTO wjsi_row.last_unit_completion_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
bom_calendar_dates b2
WHERE mp.organization_id = wjsi_row.organization_id
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_code = mp.calendar_code
and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
and b2.calendar_date = trunc(wjsi_row.first_unit_completion_date )
and b2.calendar_code = mp.calendar_code;
select wp.default_serialization_start_op,
wjsi.load_type,
wjsi.serialization_start_op,
wjsi.wip_entity_id,
wjsi.primary_item_id,
wjsi.organization_id
into l_default,
l_loadType,
l_startOp,
l_wipID,
l_primaryItem,
l_orgID
from wip_parameters wp, wip_job_schedule_interface wjsi
where wjsi.rowid = p_rowid
and wjsi.organization_id = wp.organization_id;
update wip_discrete_jobs
set serialization_start_op = p_rtgVal
where wip_entity_id = l_wipID
and exists (select 1
from mtl_system_items
where inventory_item_id = l_primaryItem
and organization_id = l_orgID
and serial_number_control_code = wip_constants.full_sn);
update wip_discrete_jobs
set serialization_start_op = (select nvl(min(operation_seq_num), 1)
from wip_operations
where wip_entity_id = l_wipID)
where wip_entity_id = l_wipID
and exists (select 1
from mtl_system_items
where inventory_item_id = l_primaryItem
and organization_id = l_orgID
and serial_number_control_code = wip_constants.full_sn);
select operation_seq_num
from wip_operations
where wip_entity_id = p_wipEntityId;
select wip_entity_id,
serialization_start_op,
load_type,
interface_id
into l_wipEntityID, l_serialOp, l_loadType, l_interfaceID
from wip_job_schedule_interface wjsi
where wjsi.rowid = p_rowid;
update wip_discrete_jobs wdj
set serialization_start_op = null
where wip_entity_id = l_wipEntityID
and serialization_start_op <> 1
and not exists(select 1
from wip_operations wo
where wo.wip_entity_id = wdj.wip_entity_id
and wo.operation_seq_num = wdj.serialization_start_op);
select 1
into l_dummy
from wip_discrete_jobs wdj,
mtl_system_items msi
where wdj.primary_item_id = msi.inventory_item_id
and wdj.organization_id = msi.organization_id
and wdj.wip_entity_id = l_wipEntityID
and msi.serial_number_control_code = wip_constants.full_sn;
select 1
into l_dummy
from wip_discrete_jobs
where wip_entity_id = l_wipEntityID
and status_type = wip_constants.unreleased;
select sub.locator_type, mp.stock_locator_control_code
into l_subLocCtl, l_orgLocCtl
from mtl_secondary_inventories sub,
mtl_parameters mp
where sub.secondary_inventory_name = wjsi_row.completion_subinventory
and sub.organization_id = wjsi_row.organization_id
and mp.organization_id = wjsi_row.organization_id;
dates are updated as original job start date in update_routing procedure in wipschdb.pls .
*/
if (wjsi_row.source_code = 'MSC' and
wjsi_row.load_type = wip_constants.resched_job and
wjsi_row.scheduling_method = wip_constants.ml_manual) then
begin
select wdj.scheduled_start_date
into wjsi_row.first_unit_start_date
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and wdj.organization_id = wjsi_row.organization_id
and exists ( select operation_seq_num
from wip_operations wo
where wo.wip_entity_id = wdj.wip_entity_id and
wo.organization_id = wdj.organization_id
minus
select operation_seq_num
from wip_job_dtls_interface
where group_id = wjsi_row.group_id
and parent_header_id = wjsi_row.header_id
and load_type = WIP_JOB_DETAILS.WIP_OPERATION
) ;
select 1
into l_valid_card
from mtl_kanban_cards
where kanban_card_id = wjsi_row.kanban_card_id
and source_type = 4
and supply_status = 4; /* empty production kanban */
l_msg := 'WIP_ML_KB_UPDATE_FAILED';
inv_kanban_pvt.update_card_supply_status (
x_return_status => l_status,
p_kanban_card_id => wjsi_row.kanban_card_id,
p_supply_status => INV_Kanban_PVT.G_Supply_Status_InProcess,
p_document_type => l_doc_type,
p_document_header_id => l_doc_header_id);
insert into wip_interface_errors(
interface_id,
error_type,
error,
last_update_date,
creation_date,
created_by,
last_update_login,
last_updated_by
)values(
p_interfaceTbl(i),
p_type,
substr(p_text,1,500),
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id);
p_interfaceTbl.delete;
update wip_job_schedule_interface
set process_status = l_processStatus,
last_update_date = sysdate
where rowid = p_rowid;
insert into wip_interface_errors(
interface_id,
error_type,
error,
last_update_date,
creation_date,
created_by,
last_update_login,
last_updated_by
)values(
p_interfaceID,
p_type,
substr(p_text,1,500),
sysdate,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id
);