[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure last_updated_by_name;
last_updated_by_name;
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 = wip_jsi_utils.current_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 organization_id
into wjsi_row.organization_id
from mtl_parameters
where organization_code = wjsi_row.organization_code;
select to_number(org_information3) into l_operating_unit
from hr_organization_information
where organization_id = wjsi_row.organization_id
and org_information_context = 'Accounting Information' ;
select wip_entity_id
into wjsi_row.wip_entity_id
from wip_entities
where wip_entity_name = wjsi_row.job_name
and organization_id = wjsi_row.organization_id;
select WORK_ORDER_PREFIX || wip_job_number_s.nextval
into wjsi_row.job_name
from wip_eam_parameters
where organization_id = wjsi_row.organization_id ;
select fnd_profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval
into wjsi_row.job_name
from dual;
select wip_entities_s.nextval
into wjsi_row.wip_entity_id
from dual;
select 1
into l_dummy
from wip_entities
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
select schedule_group_id
into wjsi_row.schedule_group_id
from wip_schedule_groups_val_v
where schedule_group_name = wjsi_row.schedule_group_name
and organization_id = wjsi_row.organization_id;
select schedule_group_id
into wjsi_row.schedule_group_id
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
select schedule_group_id
into wjsi_row.schedule_group_id
from wip_schedule_groups wsg,
wsh_new_deliveries wds
where wds.delivery_id = wjsi_row.delivery_id
and wsg.schedule_group_name = wds.name
and wsg.organization_id = wjsi_row.organization_id;
select wip_schedule_groups_s.nextval
into wjsi_row.schedule_group_id
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 wjsi_row.schedule_group_id,
wds.name,
wjsi_row.organization_id,
to_char(sysdate),
fnd_global.user_id,
fnd_global.user_id,
sysdate,
sysdate
from wsh_new_deliveries wds
where wds.delivery_id = wjsi_row.delivery_id;
select line_id
into wjsi_row.line_id
from wip_lines_val_v
where line_code = wjsi_row.line_code
and organization_id = wjsi_row.organization_id;
select pjm_project.val_proj_numtoid(wjsi_row.project_number, wjsi_row.organization_id)
into wjsi_row.project_id
from dual;
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;
select wdj.status_type
into wjsi_row.status_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and wdj.organization_id = wjsi_row.organization_id;
select wip_repetitive_schedules_s.nextval
into wjsi_row.repetitive_schedule_id
from dual;
select inventory_item_id
into wjsi_row.primary_item_id
from mtl_system_items_kfv
where concatenated_segments = wjsi_row.primary_item_segments
and organization_id = wjsi_row.organization_id;
select decode(wdj.net_quantity,
wdj.start_quantity, wjsi_row.start_quantity,
least(wdj.net_quantity, nvl(wjsi_row.start_quantity, wdj.net_quantity)))
into wjsi_row.net_quantity
from wip_discrete_jobs wdj
where wdj.wip_entity_id = wjsi_row.wip_entity_id
and wdj.organization_id = wjsi_row.organization_id;
select overcompletion_tolerance_type, overcompletion_tolerance_value, primary_item_id
into l_tolType, l_tolValue, l_primaryItemId
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id;
select class_code
into wjsi_row.class_code
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
select inventory_item_id
into wjsi_row.routing_reference_id
from mtl_system_items_kfv
where concatenated_segments = wjsi_row.routing_reference_segments
and organization_id = wjsi_row.organization_id;
select inventory_item_id
into wjsi_row.bom_reference_id
from mtl_system_items_kfv
where concatenated_segments = wjsi_row.bom_reference_segments
and organization_id = wjsi_row.organization_id;
select primary_item_id, wip_entity_name
into l_primary_item_id, l_wip_name
from wip_entities
where wip_entity_id = wjsi_row.wip_entity_id;
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 = mp.calendar_code
and b2.exception_set_id = bcd.exception_set_id)
and (exists (select 1
from wip_lines wl
where wl.line_id = wjsi_row.line_id
and wl.line_schedule_type = 2))
and (not exists (select 1
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
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));
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
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 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 = mp.calendar_code
and b2.exception_set_id = bcd.exception_set_id)
and (exists (select 1
from wip_lines wl
where wl.line_id = wjsi_row.line_id
and wl.line_schedule_type = 2))
and (not exists (select 1
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
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 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 = mp.calendar_code
and b2.exception_set_id = bcd.exception_set_id)
and (exists (select 1
from wip_lines wl
where wl.line_id = wjsi_row.line_id
and wl.line_schedule_type = 2))
and (not exists (select 1
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
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 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 = mp.calendar_code
and b2.exception_set_id = bcd.exception_set_id)
and (exists (select 1
from wip_lines wl
where wl.line_id = wjsi_row.line_id
and wl.line_schedule_type = 2))
and (not exists (select 1
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
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 bor.completion_subinventory
into wjsi_row.completion_subinventory
from bom_operational_routings bor
where bor.organization_id = wjsi_row.organization_id
and nvl(bor.cfm_routing_flag,2) = 2
and bor.assembly_item_id = wjsi_row.primary_item_id
and nvl(bor.alternate_routing_designator,'@@@') =
nvl(wjsi_row.alternate_routing_designator,'@@@');
select bor.completion_locator_id
into wjsi_row.completion_locator_id
from bom_operational_routings bor
where bor.organization_id = wjsi_row.organization_id
and nvl(bor.cfm_routing_flag,2) = 2
and bor.assembly_item_id = wjsi_row.primary_item_id
and nvl(bor.alternate_routing_designator,'@@@') =
nvl(wjsi_row.alternate_routing_designator,'@@@')
/* Fixed for bug#3060266
While defaulting the value for completion locator,completion sub inventory mus
t be checked.Completion locator would be defaulted from
table BOM_OPERATIONAL_ROUTINGS only if the completion sub inventory in
BOM_OPERATIONAL_ROUTINGS is same as completion sub inventory given in
table WIP_JOB_SCHEDULE_INTERFACE otherwise leave it null.
*/
and bor.COMPLETION_SUBINVENTORY=wjsi_row.COMPLETION_SUBINVENTORY;
procedure last_updated_by_name is begin
if(wjsi_row.last_updated_by is null) then
select user_id
into wjsi_row.last_updated_by
from fnd_user
where user_name = wjsi_row.last_updated_by_name;
elsif(wjsi_row.last_updated_by_name is not null) then --both name + id columns are populated
WIP_JSI_Utils.record_ignored_column_warning('LAST_UPDATED_BY_NAME');
wip_jsi_utils.record_invalid_column_error('LAST_UPDATED_BY_NAME');
end last_updated_by_name;
select user_id
into wjsi_row.created_by
from fnd_user
where user_name = wjsi_row.created_by_name;
select inventory_item_id
into wjsi_row.asset_group_id
from mtl_system_items_kfv
where concatenated_segments = wjsi_row.asset_group_segments
and organization_id = wjsi_row.organization_id;
select wip_entity_id
into wjsi_row.parent_wip_entity_id
from wip_entities
where wip_entity_name = wjsi_row.parent_job_name
and organization_id = wjsi_row.organization_id;
select inventory_item_id
into wjsi_row.rebuild_item_id
from mtl_system_items_kfv
where concatenated_segments = wjsi_row.rebuild_item_segments
and organization_id = wjsi_row.organization_id;
select department_id
into wjsi_row.owning_department
from bom_departments
where department_code = wjsi_row.owning_department_code
and organization_id = wjsi_row.organization_id;
select material_issue_by_mo
into wjsi_row.material_issue_by_mo
from wip_discrete_jobs
where wip_entity_id = wjsi_row.wip_entity_id
and organization_id = wjsi_row.organization_id;
select wms_enabled_flag
into l_wms_enabled_flag
from mtl_parameters
where organization_id = wjsi_row.organization_id;
select material_issue_by_mo
into wjsi_row.material_issue_by_mo
from WIP_EAM_PARAMETERS
where organization_id = wjsi_row.organization_id;
select serial_number_control_code
into l_serial_number_control_code
from mtl_system_items
where organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id;
select gen_object_id
into wjsi_row.maintenance_object_id
from mtl_serial_numbers
where current_organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.asset_group_id
and serial_number = wjsi_row.asset_number;
select gen_object_id
into wjsi_row.maintenance_object_id
from mtl_serial_numbers
where current_organization_id = wjsi_row.organization_id
and inventory_item_id = wjsi_row.rebuild_item_id
and serial_number = wjsi_row.rebuild_serial_number;