[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO x_count
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_status = WIP_CONSTANTS.ERROR
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND resource_seq_num = p_resource_seq_num;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num /* fix for bug4238691 */
and wjdi.substitution_type in
(wip_job_details.wip_add, wip_job_details.wip_change)
and (wjdi.start_date > wjdi.completion_date
or exists
(select 1
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = p_resource_seq_num
and (wor.start_date > wjdi.start_date
or wor.completion_date < wjdi.completion_date))
or (p_resource_type = 2 and
exists (select 1
from bom_resource_employees brem
where brem.organization_id = p_organization_id
and brem.instance_id = p_instance_id
and (brem.effective_start_date > wjdi.start_date
or brem.effective_end_date < wjdi.completion_date))));
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.substitution_type in
(wip_job_details.wip_add, wip_job_details.wip_change)
and (wjdi.start_date > wjdi.completion_date
or exists
(select 1
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = p_resource_seq_num
and (wor.start_date > wjdi.start_date
or wor.completion_date < wjdi.completion_date))
or (p_resource_type = 2 and
exists (select 1
from bom_resource_employees brem
where brem.organization_id = p_organization_id
and brem.instance_id = p_instance_id
and (brem.effective_start_date > wjdi.start_date
or brem.effective_end_date < wjdi.completion_date))));
/* resource_seq_num, instance_id must not be null when add/change/delete
resource instance */
Procedure ResInst_Info_Exist(p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type number,
p_operation_seq_num number,
p_resource_seq_num number,
p_resource_instance_id number,
p_resource_type number) IS
cursor c_invalid_rows is
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id = p_resource_instance_id
and ( wjdi.resource_seq_num is null
or (wjdi.substitution_type <> wip_job_details.wip_delete
and wjdi.resource_instance_id is null)
or (wjdi.substitution_type = wip_job_details.wip_add
and wjdi.resource_serial_number is null
and p_resource_type = 1 ));
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( wjdi.resource_seq_num is null
or (wjdi.substitution_type <> wip_job_details.wip_delete
and wjdi.resource_instance_id is null)
or (wjdi.substitution_type = wip_job_details.wip_add
and wjdi.resource_serial_number is null
and p_resource_type = 1 ));
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id = p_resource_instance_id
and wjdi.resource_instance_id not in (
select instance_id
from bom_dept_res_instances
where resource_id=p_resource_id and department_id=p_dept_id);
select wo.department_id, wor.resource_id
into l_dept_id, l_resource_id
from wip_operations wo, wip_operation_resources wor
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
and wo.organization_id = p_organization_id
and wo.repetitive_schedule_id is null
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = p_resource_seq_num
and wor.organization_id = p_organization_id
and wor.repetitive_schedule_id is null;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_res_instance, wjdi.load_type)
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id = p_resource_instance_id
and wjdi.resource_instance_id not in (
select instance_id
from bom_dept_res_instances
where resource_id=l_resource_id and department_id=l_dept_id);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id = p_resource_instance_id
and p_resource_id in (select resource_id
from bom_resources
where resource_type = 1) /* machine type resource */
and p_resource_serial_number not in(
select bdri.serial_number
from bom_resource_equipments breq,
bom_dept_res_instances_eqp_v bdri,
mtl_system_items_kfv msik,
bom_department_resources bdr
where bdr.resource_id = p_resource_id
and bdr.department_id = p_dept_id
and bdri.resource_id = bdr.resource_id
and bdri.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
and breq.organization_id = p_organization_id
and breq.instance_id = bdri.instance_id
and breq.resource_id = bdri.resource_id
and msik.inventory_item_id = breq.inventory_item_id
and msik.organization_id = breq.organization_id);
select wo.department_id, wor.resource_id
into l_dept_id, l_resource_id
from wip_operations wo, wip_operation_resources wor
where wo.wip_entity_id = p_wip_entity_id
and wo.operation_seq_num = p_operation_seq_num
and wo.organization_id = p_organization_id
and wo.repetitive_schedule_id is null
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_operation_seq_num
and wor.resource_seq_num = p_resource_seq_num
and wor.organization_id = p_organization_id
and wor.repetitive_schedule_id is null;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_res_instance, wjdi.load_type)
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id = p_resource_instance_id
and l_resource_id in (select resource_id
from bom_resources
where resource_type = 1) /* machine type resource */
and p_resource_serial_number not in(
select bdri.serial_number
from bom_resource_equipments breq,
bom_dept_res_instances_eqp_v bdri,
mtl_system_items_kfv msik,
bom_department_resources bdr
where bdr.resource_id = l_resource_id
and bdr.department_id = l_dept_id
and bdri.resource_id = bdr.resource_id
and bdri.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
and breq.organization_id = p_organization_id
and breq.instance_id = bdri.instance_id
and breq.resource_id = bdri.resource_id
and msik.inventory_item_id = breq.inventory_item_id
and msik.organization_id = breq.organization_id);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = wip_job_details.wip_add
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and p_count <> p_assigned_units;
select assigned_units into l_assigned_units
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
select source_code into l_source_code
from wip_job_schedule_interface
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and group_id = p_group_id;
select count(*) into l_count
from wip_op_resource_instances
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
select count(*)
into l_add_count
from wip_job_dtls_interface
where group_id = p_group_id
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = wip_job_details.wip_res_instance
and substitution_type = wip_job_details.wip_add
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
select count(*)
into l_del_count
from wip_job_dtls_interface
where group_id = p_group_id
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = wip_job_details.wip_res_instance
and substitution_type = wip_job_details.wip_delete
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
wip_logger.log('Number of units to be deleted = ' ||to_char(l_del_count), l_retStatus);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = wip_job_details.wip_add
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and l_count <> l_assigned_units;
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
resource_instance_id, usage_rate_or_amount,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date, resource_serial_number,
scheduled_flag, assigned_units, applied_resource_units,
applied_resource_value, uom_code, basis_type,
activity_id, autocharge_type, standard_rate_flag,
start_date, completion_date,attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15, schedule_seq_num,
substitute_group_num, replacement_group_num, parent_seq_num, rowid
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
AND substitution_type = p_substitution_type;
select br.resource_type
into l_resource_type
from bom_resources br, wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and wor.operation_seq_num = cur_row.operation_seq_num
and wor.resource_seq_num = cur_row.resource_seq_num
and br.resource_id = wor.resource_id;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_res_instance
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id= p_instance_id
and not exists (select 1
from wip_op_resource_instances wori
where wori.wip_entity_id = wjdi.wip_entity_id
and wori.organization_id = wjdi.organization_id
and wori.operation_seq_num = wjdi.operation_seq_num
and wori.resource_seq_num = wjdi.resource_seq_num
and wori.instance_id = wjdi.resource_instance_id
);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_res_instance)
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and wjdi.resource_seq_num = p_resource_seq_num
and wjdi.resource_instance_id= p_instance_id
and not exists (select 1
from wip_op_resource_instances wori
where wori.wip_entity_id = wjdi.wip_entity_id
and wori.organization_id = wjdi.organization_id
and wori.operation_seq_num = wjdi.operation_seq_num
and wori.resource_seq_num = wjdi.resource_seq_num
and wori.instance_id = wjdi.resource_instance_id
);
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
resource_instance_id, usage_rate_or_amount,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
scheduled_flag, assigned_units, applied_resource_units,
applied_resource_value, uom_code, basis_type,
activity_id, autocharge_type, standard_rate_flag,
start_date, completion_date,attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15, schedule_seq_num,
substitute_group_num, replacement_group_num
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
AND substitution_type = p_substitution_type;
select br.resource_type
into l_resource_type
from bom_resources br, wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and wor.operation_seq_num = cur_row.operation_seq_num
and wor.resource_seq_num = cur_row.resource_seq_num
and br.resource_id = wor.resource_id;
update wip_op_resource_instances wori
set wori.instance_id = cur_row.resource_instance_id
where wori.wip_entity_id = p_wip_entity_id
and wori.organization_id = p_organization_id
and wori.operation_seq_num = cur_row.operation_seq_num
and wori.resource_seq_num = cur_row.resource_seq_num;
Procedure Delete_Resource_Instance(p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
x_err_code varchar2(30) := null;
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
resource_instance_id, usage_rate_or_amount,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
scheduled_flag, assigned_units, applied_resource_units,
applied_resource_value, uom_code, basis_type,
activity_id, autocharge_type, standard_rate_flag,
start_date, completion_date,attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15, schedule_seq_num,
substitute_group_num, replacement_group_num
FROM WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
AND substitution_type = p_substitution_type;
p_err_msg := 'wiprivdb, Delete_Resource_Instance: ' || SQLERRM;
END Delete_Resource_Instance;