[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Delete_Resource (p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
CURSOR res_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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
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_RESOURCE
AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
DELETE FROM WIP_OPERATION_RESOURCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND resource_id = cur_row.resource_id_old;
p_err_msg := 'WIPJDSTB, Delete_Resource: ' || SQLERRM;
END Delete_Resource;
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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, firm_flag, setup_id,
group_sequence_id, group_sequence_number, maximum_assigned_units,
parent_seq_num, batch_id
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_RESOURCE
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
/* Moved the delete statement out of the loop for fixing bug 4357678
Every time in the loop, this was deleting the inserted records as well */
FOR cur_row IN res_info(p_group_id,
p_wip_entity_id,
p_organization_id) LOOP
-- if adding setup resource, delete all existing setup resources
if (cur_row.parent_seq_num is not null) then
DELETE FROM WIP_OPERATION_RESOURCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND parent_resource_seq = cur_row.parent_seq_num;
/* insert into table */
INSERT INTO WIP_OPERATION_RESOURCES(
wip_entity_id,
organization_id,
operation_seq_num,
resource_seq_num,
resource_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,
firm_flag,
setup_id,
group_sequence_id,
group_sequence_number,
maximum_assigned_units,
parent_resource_seq,
batch_id)
VALUES (
p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
cur_row.resource_id_new,
cur_row.usage_rate_or_amount,
sysdate,/*BUG 6721823*/
cur_row.last_updated_by,
cur_row.creation_date,
cur_row.created_by,
cur_row.last_update_login,
cur_row.request_id,
cur_row.program_application_id,
cur_row.program_id,
cur_row.program_update_date,
cur_row.scheduled_flag,
cur_row.assigned_units,
cur_row.applied_resource_units,
cur_row.applied_resource_value,
cur_row.uom_code,
cur_row.basis_type,
cur_row.activity_id,
cur_row.autocharge_type,
cur_row.standard_rate_flag,
cur_row.start_date,
cur_row.completion_date,
cur_row.attribute_category,
cur_row.attribute1,
cur_row.attribute2,
cur_row.attribute3,
cur_row.attribute4,
cur_row.attribute5,
cur_row.attribute6,
cur_row.attribute7,
cur_row.attribute8,
cur_row.attribute9,
cur_row.attribute10,
cur_row.attribute11,
cur_row.attribute12,
cur_row.attribute13,
cur_row.attribute14,
cur_row.attribute15,
cur_row.schedule_seq_num,
cur_row.substitute_group_num,
cur_row.replacement_group_num,
cur_row.firm_flag,
cur_row.setup_id,
cur_row.group_sequence_id,
cur_row.group_sequence_number,
cur_row.maximum_assigned_units,
cur_row.parent_seq_num,
cur_row.batch_id);
SELECT scheduling_method INTO l_scheduling_method
FROM WIP_JOB_SCHEDULE_INTERFACE
WHERE group_id = p_group_id
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
select scheduled_start_date,scheduled_completion_date
into l_scheduled_start_date,l_scheduled_completion_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_start_date = cur_row.start_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_completion_date = cur_row.completion_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
select first_unit_start_date,last_unit_completion_date
into l_first_unit_start_date,l_last_unit_completion_date
from wip_operations
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
UPDATE wip_operations
set first_unit_start_date = cur_row.start_date,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
UPDATE wip_operations
set last_unit_completion_date = cur_row.completion_date,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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, firm_flag,setup_id,
group_sequence_id, group_sequence_number, maximum_assigned_units,
parent_seq_num, batch_id
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_RESOURCE
AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
select wo.first_unit_start_date,
rowidtochar(wor.rowid)
from wip_operation_resources wor,
wip_operations wo
where wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and wor.wip_entity_id = p_wip_entity_id
and wor.organization_id = p_organization_id
and not exists ( select 1
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_RESOURCE
AND operation_seq_num = wor.operation_seq_num
AND resource_seq_num = wor.resource_seq_num
) ;
select substitute_group_num
into l_current_sub
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = cur_row.operation_seq_num
and resource_seq_num = cur_row.resource_seq_num;
select 1
into l_replace_res
from dual
where exists (select 1
from wip_sub_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = cur_row.operation_seq_num
and substitute_group_num = cur_row.substitute_group_num
and replacement_group_num = cur_row.replacement_group_num);
UPDATE WIP_OPERATION_RESOURCES
SET start_date = NVL(cur_row.start_date,start_date),
completion_date = NVL(cur_row.completion_date,completion_date),
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND substitute_group_num = cur_row.substitute_group_num;
UPDATE WIP_OPERATION_RESOURCES
SET resource_id = cur_row.resource_id_new,
usage_rate_or_amount = nvl(cur_row.usage_rate_or_amount,
usage_rate_or_amount),
last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = cur_row.last_updated_by,
creation_date = cur_row.creation_date,
created_by = cur_row.created_by,
last_update_login = cur_row.last_update_login,
request_id = cur_row.request_id,
program_application_id = cur_row.program_application_id,
program_id = cur_row.program_id,
program_update_date = cur_row.program_update_date,
scheduled_flag = nvl(cur_row.scheduled_flag,
scheduled_flag),
assigned_units = nvl(cur_row.assigned_units, assigned_units),
uom_code = nvl(cur_row.uom_code, uom_code),
basis_type = nvl(cur_row.basis_type, basis_type),
activity_id = nvl(cur_row.activity_id, activity_id),
autocharge_type = nvl(cur_row.autocharge_type, autocharge_type),
standard_rate_flag = nvl(cur_row.standard_rate_flag, standard_rate_flag),
start_date = nvl(cur_row.start_date, start_date),
completion_date = nvl(cur_row.completion_date, completion_date),
attribute_category = NVL(cur_row.attribute_category,
attribute_category),
attribute1 = NVL(cur_row.attribute1,attribute1),
attribute2 = NVL(cur_row.attribute2,attribute2),
attribute3 = NVL(cur_row.attribute3,attribute3),
attribute4 = NVL(cur_row.attribute4,attribute4),
attribute5 = NVL(cur_row.attribute5,attribute5),
attribute6 = NVL(cur_row.attribute6,attribute6),
attribute7 = NVL(cur_row.attribute7,attribute7),
attribute8 = NVL(cur_row.attribute8,attribute8),
attribute9 = NVL(cur_row.attribute9,attribute9),
attribute10 = NVL(cur_row.attribute10,attribute10),
attribute11 = NVL(cur_row.attribute11,attribute11),
attribute12 = NVL(cur_row.attribute12,attribute12),
attribute13 = NVL(cur_row.attribute13,attribute13),
attribute14 = NVL(cur_row.attribute14,attribute14),
attribute15 = NVL(cur_row.attribute15,attribute15),
schedule_seq_num = decode(cur_row.schedule_seq_num, fnd_api.g_miss_num, null, cur_row.schedule_seq_num),
substitute_group_num = decode(cur_row.substitute_group_num, fnd_api.g_miss_num, null, cur_row.substitute_group_num),
replacement_group_num = decode(cur_row.replacement_group_num, fnd_api.g_miss_num, null, cur_row.replacement_group_num),
firm_flag = NVL(cur_row.firm_flag, firm_flag),
setup_id = NVL(cur_row.setup_id, setup_id),
group_sequence_id = NVL(cur_row.group_sequence_id, group_sequence_id),
group_sequence_number = NVL(cur_row.group_sequence_number, group_sequence_number),
maximum_assigned_units = NVL(cur_row.maximum_assigned_units, maximum_assigned_units),
parent_resource_seq = NVL(cur_row.parent_seq_num, parent_resource_seq),
batch_id = NVL(cur_row.batch_id,batch_id)
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND resource_id = cur_row.resource_id_old;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
SELECT scheduling_method INTO l_scheduling_method
FROM WIP_JOB_SCHEDULE_INTERFACE
WHERE group_id = p_group_id
AND wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
select scheduled_start_date,scheduled_completion_date
into l_scheduled_start_date,l_scheduled_completion_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_start_date = cur_row.start_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_completion_date = cur_row.completion_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
select first_unit_start_date,last_unit_completion_date
into l_first_unit_start_date,l_last_unit_completion_date
from wip_operations
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
UPDATE wip_operations
set first_unit_start_date = cur_row.start_date,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
UPDATE wip_operations
set last_unit_completion_date = cur_row.completion_date,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num;
/* Fix for Bug#4656331. Update missing resource start date and completion
date
*/
select source_code,
scheduling_method
into l_source_code,
l_scheduling_method
from wip_job_schedule_interface
where group_id = p_group_id
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id ;
update wip_operation_resources
set start_date = l_fusdTbl(i),
completion_date = l_fusdTbl(i),
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where rowid = chartorowid(l_rowidTbl(i)) ;
Procedure Delete_Sub_Resource (p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
CURSOR res_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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
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_SUB_RES
AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
DELETE FROM WIP_SUB_OPERATION_RESOURCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND resource_id = cur_row.resource_id_old;
p_err_msg := 'WIPJDSTB, Delete_Resource: ' || SQLERRM;
END Delete_Sub_Resource;
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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, maximum_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_SUB_RES
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
/* insert into table */
INSERT INTO WIP_SUB_OPERATION_RESOURCES(
wip_entity_id,
organization_id,
operation_seq_num,
resource_seq_num,
resource_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,
maximum_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)
VALUES (
p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
cur_row.resource_id_new,
cur_row.usage_rate_or_amount,
sysdate,/*BUG 6721823*/
cur_row.last_updated_by,
cur_row.creation_date,
cur_row.created_by,
cur_row.last_update_login,
cur_row.request_id,
cur_row.program_application_id,
cur_row.program_id,
cur_row.program_update_date,
cur_row.scheduled_flag,
cur_row.assigned_units,
cur_row.maximum_assigned_units,
cur_row.applied_resource_units,
cur_row.applied_resource_value,
cur_row.uom_code,
cur_row.basis_type,
cur_row.activity_id,
cur_row.autocharge_type,
cur_row.standard_rate_flag,
cur_row.start_date,
cur_row.completion_date,
cur_row.attribute_category,
cur_row.attribute1,
cur_row.attribute2,
cur_row.attribute3,
cur_row.attribute4,
cur_row.attribute5,
cur_row.attribute6,
cur_row.attribute7,
cur_row.attribute8,
cur_row.attribute9,
cur_row.attribute10,
cur_row.attribute11,
cur_row.attribute12,
cur_row.attribute13,
cur_row.attribute14,
cur_row.attribute15,
cur_row.schedule_seq_num,
cur_row.substitute_group_num,
cur_row.replacement_group_num);
SELECT distinct operation_seq_num,
resource_seq_num, resource_id_old, resource_id_new,
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, maximum_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_SUB_RES
AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
/* update the table */
/** Fix for bug 2438722 - correct attribute cols updated **/
UPDATE WIP_SUB_OPERATION_RESOURCES
SET resource_id = cur_row.resource_id_new,
usage_rate_or_amount = cur_row.usage_rate_or_amount,
last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = cur_row.last_updated_by,
creation_date = cur_row.creation_date,
created_by = cur_row.created_by,
last_update_login = cur_row.last_update_login,
request_id = cur_row.request_id,
program_application_id = cur_row.program_application_id,
program_id = cur_row.program_id,
program_update_date = cur_row.program_update_date,
scheduled_flag = cur_row.scheduled_flag,
assigned_units = cur_row.assigned_units,
maximum_assigned_units = cur_row.maximum_assigned_units,
applied_resource_units = nvl(cur_row.applied_resource_units,applied_resource_units),
applied_resource_value = nvl(cur_row.applied_resource_value,applied_resource_value),
uom_code = cur_row.uom_code,
basis_type = nvl(cur_row.basis_type, basis_type),
activity_id = cur_row.activity_id,
autocharge_type = nvl(cur_row.autocharge_type, autocharge_type),
standard_rate_flag = nvl(cur_row.standard_rate_flag, standard_rate_flag),
start_date = nvl(cur_row.start_date, start_date),
completion_date = nvl(cur_row.completion_date, completion_date),
attribute_category = NVL(cur_row.attribute_category,
attribute_category),
attribute1 = NVL(cur_row.attribute1,attribute1),
attribute2 = NVL(cur_row.attribute2,attribute2),
attribute3 = NVL(cur_row.attribute3,attribute3),
attribute4 = NVL(cur_row.attribute4,attribute4),
attribute5 = NVL(cur_row.attribute5,attribute5),
attribute6 = NVL(cur_row.attribute6,attribute6),
attribute7 = NVL(cur_row.attribute7,attribute7),
attribute8 = NVL(cur_row.attribute8,attribute8),
attribute9 = NVL(cur_row.attribute9,attribute9),
attribute10 = NVL(cur_row.attribute10,attribute10),
attribute11 = NVL(cur_row.attribute11,attribute11),
attribute12 = NVL(cur_row.attribute12,attribute12),
attribute13 = NVL(cur_row.attribute13,attribute13),
attribute14 = NVL(cur_row.attribute14,attribute14),
attribute15 = NVL(cur_row.attribute15,attribute15),
schedule_seq_num = decode(cur_row.schedule_seq_num, fnd_api.g_miss_num, null, cur_row.schedule_seq_num),
substitute_group_num = cur_row.substitute_group_num,
replacement_group_num = cur_row.replacement_group_num
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND resource_id = cur_row.resource_id_old;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
resource_instance_id, start_date, completion_date, batch_id, interface_id,
created_by, creation_date,last_updated_by,last_update_date
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 = WIP_JOB_DETAILS.WIP_ADD;
/* delete all existing instances on a resource before add */
Delete_Resource_Instance(p_group_id, p_wip_entity_id, p_organization_id,
WIP_JOB_DETAILS.WIP_ADD, p_err_code, p_err_msg);
/* insert into table */
INSERT INTO WIP_OP_RESOURCE_INSTANCES (
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
INSTANCE_ID,
SERIAL_NUMBER,
START_DATE,
COMPLETION_DATE,
BATCH_ID
)
VALUES (
p_wip_entity_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_organization_id,
sysdate,/*BUG 6721823*/
cur_row.last_updated_by,
cur_row.creation_date,
cur_row.created_by,
cur_row.resource_instance_id,
cur_row.resource_serial_number,
cur_row.start_date,
cur_row.completion_date,
cur_row.batch_id
);
SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
resource_instance_id, start_date, completion_date, batch_id,
created_by, creation_date,last_updated_by,last_update_date
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 = WIP_JOB_DETAILS.WIP_CHANGE;
L_RET_EXP_STATUS := WIP_WS_EXCEPTIONS.close_exp_res_instance_update
(
P_WIP_ENTITY_ID => P_WIP_ENTITY_ID,
P_OPERATION_SEQ_NUM => CUR_ROW.OPERATION_SEQ_NUM,
P_RESOURCE_SEQ_NUM => CUR_ROW.RESOURCE_SEQ_NUM,
P_INSTANCE_ID => CUR_ROW.RESOURCE_INSTANCE_ID,
P_SERIAL_NUMBER => CUR_ROW.RESOURCE_SERIAL_NUMBER,
P_ORGANIZATION_ID => P_ORGANIZATION_ID
);
UPDATE WIP_OP_RESOURCE_INSTANCES
SET serial_number = nvl(cur_row.resource_serial_number,serial_number),
last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = nvl(cur_row.last_updated_by,last_updated_by),
creation_date = nvl(cur_row.creation_date,creation_date),
created_by = nvl(cur_row.created_by,created_by),
start_date = nvl(cur_row.start_date,start_date),
completion_date = nvl(cur_row.completion_date,completion_date),
batch_id = nvl(cur_row.batch_id,batch_id)
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND instance_id = cur_row.resource_instance_id;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
Procedure Delete_Resource_Instance (p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_substitution_type in number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
CURSOR res_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT distinct operation_seq_num, resource_seq_num, resource_serial_number,
resource_instance_id, start_date, completion_date, batch_id
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;
Delete_Resource_Usage(p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
p_err_code,
p_err_msg);
DELETE FROM WIP_OP_RESOURCE_INSTANCES
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND resource_seq_num = cur_row.resource_seq_num
AND instance_id = cur_row.resource_instance_id;
p_err_msg := 'WIPJDSTB, Delete_Resource_Instance: ' || SQLERRM;
END Delete_Resource_Instance;
Procedure Delete_Requirement (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
CURSOR req_info(p_group_Id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued, supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
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_MTL_REQUIREMENT
AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND inventory_item_id = cur_row.inventory_item_id_old;
p_err_msg := 'WIPJDSTB, Delete_Requirement: ' || SQLERRM;
END Delete_Requirement;
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued,
basis_type, /* LBM Project */
supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, auto_request_material, comments,
attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
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_MTL_REQUIREMENT
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
INSERT INTO WIP_REQUIREMENT_OPERATIONS(
wip_entity_id,
organization_id,
operation_seq_num,
inventory_item_id,
quantity_per_assembly,
component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
department_id,
wip_supply_type,
date_required,
required_quantity,
quantity_issued,
basis_type, /* LBM Project */
supply_subinventory ,
supply_locator_id,
mrp_net_flag,
mps_required_quantity,
mps_date_required,
auto_request_material,
comments,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
VALUES (
p_wip_entity_id,
p_organization_id,
cur_row.operation_seq_num,
cur_row.inventory_item_id_new,
round(cur_row.quantity_per_assembly, 6),
cur_row.component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
sysdate,/*BUG 6721823*/
cur_row.last_updated_by,
cur_row.creation_date,
cur_row.created_by,
cur_row.last_update_login,
cur_row.request_id,
cur_row.program_application_id,
cur_row.program_id,
cur_row.program_update_date,
cur_row.department_id,
cur_row.wip_supply_type,
cur_row.date_required,
cur_row.required_quantity,
cur_row.quantity_issued,
cur_row.basis_type, /* LBM Project */
cur_row.supply_subinventory ,
cur_row.supply_locator_id,
cur_row.mrp_net_flag,
cur_row.mps_required_quantity,
cur_row.mps_date_required,
cur_row.auto_request_material,
cur_row.comments,
cur_row.attribute_category,
cur_row.attribute1,
cur_row.attribute2,
cur_row.attribute3,
cur_row.attribute4,
cur_row.attribute5,
cur_row.attribute6,
cur_row.attribute7,
cur_row.attribute8,
cur_row.attribute9,
cur_row.attribute10,
cur_row.attribute11,
cur_row.attribute12,
cur_row.attribute13,
cur_row.attribute14,
cur_row.attribute15);
SELECT distinct operation_seq_num,
inventory_item_id_old, inventory_item_id_new,
quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, request_id, program_application_id,
program_id, program_update_date,
department_id, wip_supply_type, date_required,
required_quantity, quantity_issued,
basis_type, /* LBM Project */
supply_subinventory,
supply_locator_id, mrp_net_flag, mps_required_quantity,
mps_date_required, auto_request_material, comments,
attribute_category, attribute1,
attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
attribute8,attribute9,attribute10,attribute11,attribute12,
attribute13,attribute14,attribute15
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_MTL_REQUIREMENT
AND substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
SELECT required_quantity, wip_supply_type, supply_subinventory, supply_locator_id
into l_required_quantity, l_wip_supply_type, l_supply_subinventory, l_supply_locator_id
FROM wip_requirement_operations
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND inventory_item_id = cur_row.inventory_item_id_old;
wip_picking_pub.Update_Component_BackOrdQty(p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => cur_row.operation_seq_num,
p_new_component_qty => cur_row.required_quantity,
p_inventory_item_id => cur_row.inventory_item_id_old,
x_return_status => x_return_status,
x_msg_data => x_msg_data);
/* update table */
/** Fix for bug 2438722 - correct attribute cols updated **/
/** Fix for bug 2649338
-- stripped off time part from date_required and mps_date_required **/
UPDATE WIP_REQUIREMENT_OPERATIONS
SET inventory_item_id = NVL(cur_row.inventory_item_id_new,
inventory_item_id),
quantity_per_assembly = NVL(round(
cur_row.quantity_per_assembly,6),
quantity_per_assembly),
component_yield_factor = NVL(cur_row.component_yield_factor,
component_yield_factor),/*Component Yield Enhancement(Bug 4369064)*/
/* LBM Project: if user wants to change the basis_type to null (item basis), he needs to insert fnd_api.g_miss_num into interface table. This should be in the interface user guide */
/* Bug 5468646 - update component basis */
basis_type = decode(cur_row.basis_type, fnd_api.g_miss_num, null, null, basis_type, cur_row.basis_type),
last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = cur_row.last_updated_by,
creation_date = cur_row.creation_date,
created_by = cur_row.created_by,
last_update_login = NVL(cur_row.last_update_login,
last_update_login),
request_id = NVL(cur_row.request_id,
request_id),
program_application_id = NVL(cur_row.program_application_id,
program_application_id),
program_id = NVL(cur_row.program_id,
program_id),
program_update_date = NVL(cur_row.program_update_date,
program_update_date),
department_id = NVL(cur_row.department_id,
department_id),
wip_supply_type = NVL(cur_row.wip_supply_type,
wip_supply_type),
date_required = NVL(cur_row.date_required,
date_required),
required_quantity = NVL(cur_row.required_quantity,
required_quantity),
/* Bug 4887280 - modify decode statement for supply_subinventory, and supply_locator */
supply_subinventory = decode(nvl(cur_row.wip_supply_type, 4),
1, --push
decode(cur_row.supply_subinventory,
NULL,
supply_subinventory,
fnd_api.g_miss_char,
NULL,
cur_row.supply_subinventory),
nvl(cur_row.supply_subinventory, supply_subinventory)),
supply_locator_id = decode(nvl(cur_row.wip_supply_type, 4),
1, --push
decode(cur_row.supply_locator_id,
NULL,
supply_locator_id,
fnd_api.g_miss_num,
NULL,
cur_row.supply_locator_id),
nvl(cur_row.supply_locator_id, supply_locator_id)),
mrp_net_flag = NVL(cur_row.mrp_net_flag,
mrp_net_flag),
mps_required_quantity = NVL(cur_row.mps_required_quantity,
mps_required_quantity),
mps_date_required = NVL(cur_row.mps_date_required,
mps_date_required),
auto_request_material = NVL( cur_row.auto_request_material,
auto_request_material),
comments = NVL( cur_row.comments, comments),
attribute_category = NVL(cur_row.attribute_category,
attribute_category),
attribute1 = NVL(cur_row.attribute1,attribute1),
attribute2 = NVL(cur_row.attribute2,attribute2),
attribute3 = NVL(cur_row.attribute3,attribute3),
attribute4 = NVL(cur_row.attribute4,attribute4),
attribute5 = NVL(cur_row.attribute5,attribute5),
attribute6 = NVL(cur_row.attribute6,attribute6),
attribute7 = NVL(cur_row.attribute7,attribute7),
attribute8 = NVL(cur_row.attribute8,attribute8),
attribute9 = NVL(cur_row.attribute9,attribute9),
attribute10 = NVL(cur_row.attribute10,attribute10),
attribute11 = NVL(cur_row.attribute11,attribute11),
attribute12 = NVL(cur_row.attribute12,attribute12),
attribute13 = NVL(cur_row.attribute13,attribute13),
attribute14 = NVL(cur_row.attribute14,attribute14),
attribute15 = NVL(cur_row.attribute15,attribute15)
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = cur_row.operation_seq_num
AND inventory_item_id = cur_row.inventory_item_id_old;
SELECT distinct parent_header_id, operation_seq_num, standard_operation_id,
department_id, description , first_unit_start_date,
first_unit_completion_date, last_unit_start_date,
last_unit_completion_date, minimum_transfer_quantity,
count_point_type, backflush_flag,last_update_date,
last_updated_by, creation_date,created_by, last_update_login,
request_id, program_application_id, program_id, program_update_date,
long_description,
attribute_category, attribute1, attribute2, attribute3,
attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10,
attribute11, attribute12, attribute13, attribute14, attribute15
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_OPERATION
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
SELECT operation_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
ORDER BY operation_seq_num ;
SELECT wdj.start_quantity, greatest(wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped, 0), wdj.status_type, we.load_type
INTO l_scheduled_quantity, l_open_quantity,l_status_type, l_load_type
FROM wip_discrete_jobs wdj,wip_job_schedule_interface we
WHERE wdj.wip_entity_id = p_wip_entity_id
AND wdj.organization_id = p_organization_id
AND wdj.wip_entity_id = we.wip_entity_id (+)
AND wdj.organization_id = we.organization_id (+)
AND we.group_id = p_group_id
AND we.process_phase = WIP_CONSTANTS.ML_VALIDATION
AND we.process_status in ( WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING )
AND we.header_id = cur_oper.parent_header_id;
SELECT min(operation_seq_num)
INTO l_first_operation
FROM WIP_OPERATIONS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
WIP_OPERATIONS_UTILITIES.Update_Operationless_Reqs
(p_wip_entity_id,
p_organization_id,
cur_oper.operation_seq_num,
NULL, /* repetitive schedule id */
cur_oper.department_id,
cur_oper.first_unit_start_date);
select quantity_in_queue into l_quantity_in_queue
from wip_operations
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = l_first_operation;
update wip_operations
set quantity_in_queue = 0,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = l_first_operation;
UPDATE WIP_OPERATIONS
SET last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = NVL(cur_oper.last_updated_by,last_updated_by),
creation_date = NVL(cur_oper.creation_date,creation_date),
created_by = NVL(cur_oper.created_by, created_by),
last_update_login = NVL(cur_oper.last_update_login,last_update_login),
request_id = NVL(cur_oper.request_id ,request_id),
program_application_id =NVL(cur_oper.program_application_id,program_application_id),
program_id = NVL(cur_oper.program_id,program_id),
program_update_date = NVL(cur_oper.program_update_date,
program_update_date),
description = NVL(cur_oper.description,description),
first_unit_start_date = NVL(cur_oper.first_unit_start_date,
first_unit_start_date),
first_unit_completion_date =NVL(cur_oper.first_unit_completion_date,
first_unit_completion_date),
last_unit_start_date = NVL(cur_oper.last_unit_start_date,
last_unit_start_date),
last_unit_completion_date = NVL(cur_oper.last_unit_completion_date,
last_unit_completion_date),
count_point_type = NVL(cur_oper.count_point_type,count_point_type),
backflush_flag = NVL(cur_oper.backflush_flag,backflush_flag),
minimum_transfer_quantity = NVL(cur_oper.minimum_transfer_quantity,
minimum_transfer_quantity),
long_description = NVL(cur_oper.long_description,
long_description),
attribute_category = NVL(cur_oper.attribute_category,
attribute_category),
attribute1 = NVL(cur_oper.attribute1,attribute1),
attribute2 = NVL(cur_oper.attribute2,attribute2),
attribute3 = NVL(cur_oper.attribute3,attribute3),
attribute4 = NVL(cur_oper.attribute4,attribute4),
attribute5 = NVL(cur_oper.attribute5,attribute5),
attribute6 = NVL(cur_oper.attribute6,attribute6),
attribute7 = NVL(cur_oper.attribute7,attribute7),
attribute8 = NVL(cur_oper.attribute8,attribute8),
attribute9 = NVL(cur_oper.attribute9,attribute9),
attribute10 = NVL(cur_oper.attribute10,attribute10),
attribute11 = NVL(cur_oper.attribute11,attribute11),
attribute12 = NVL(cur_oper.attribute12,attribute12),
attribute13 = NVL(cur_oper.attribute13,attribute13),
attribute14 = NVL(cur_oper.attribute14,attribute14),
attribute15 = NVL(cur_oper.attribute15,attribute15),
quantity_in_queue = l_quantity_in_queue
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = cur_oper.operation_seq_num
/* Bug 6132987 (FP of 5886171) */
RETURNING first_unit_start_date, last_unit_completion_date
INTO l_first_unit_start_date, l_last_unit_completion_date;
/* Bug 6132987 (FP of 5886171) - The scheduling dates are updated in WO in the statement above but they are
not updated in WOR.This causes validation to fail in verify_operation, if the resource dates are outside
the operation date ranges.
So compare the WOR dates with the WO dates and update the WOR dates so that the validation does not fail */
DECLARE
CURSOR C_WOR IS
select start_date, completion_date, rowid
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 = cur_oper.operation_seq_num;
UPDATE WIP_OPERATION_RESOURCES
SET start_date = nvl(l_wor_start_date, start_date),
completion_date = nvl(l_wor_completion_date, completion_date),
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
WHERE rowid = rec.rowid;
SELECT SUM(quantity_scrapped)
INTO l_scrap_qty
FROM wip_operations
WHERE organization_id = p_organization_id
AND wip_entity_id = p_wip_entity_id
AND operation_seq_num < cur_oper.operation_seq_num;
INSERT INTO WIP_OPERATIONS
( wip_entity_id,
operation_seq_num,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
standard_operation_id,
department_id,
description,
scheduled_quantity,
quantity_in_queue,
quantity_running,
quantity_waiting_to_move,
quantity_rejected,
quantity_scrapped,
quantity_completed,
cumulative_scrap_quantity, /* for 2864382 */
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
last_unit_completion_date,
count_point_type,
backflush_flag,
minimum_transfer_quantity,
long_description,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
VALUES
( p_wip_entity_id,
cur_oper.operation_seq_num,
p_organization_id,
sysdate,/*BUG 6721823*/
cur_oper.last_updated_by,
cur_oper.creation_date,
cur_oper.created_by,
cur_oper.last_update_login,
cur_oper.request_id,
cur_oper.program_application_id,
cur_oper.program_id,
cur_oper.program_update_date,
cur_oper.standard_operation_id,
cur_oper.department_id,
cur_oper.description,
l_scheduled_quantity,
l_quantity_in_queue,
0,0,0,0,0,
l_scrap_qty,
cur_oper.first_unit_start_date,
cur_oper.first_unit_completion_date,
cur_oper.last_unit_start_date,
cur_oper.last_unit_completion_date,
cur_oper.count_point_type,
cur_oper.backflush_flag,
cur_oper.minimum_transfer_quantity,
cur_oper.long_description,
cur_oper.attribute_category,
cur_oper.attribute1,
cur_oper.attribute2,
cur_oper.attribute3,
cur_oper.attribute4,
cur_oper.attribute5,
cur_oper.attribute6,
cur_oper.attribute7,
cur_oper.attribute8,
cur_oper.attribute9,
cur_oper.attribute10,
cur_oper.attribute11,
cur_oper.attribute12,
cur_oper.attribute13,
cur_oper.attribute14,
cur_oper.attribute15);
select scheduled_start_date,scheduled_completion_date
into l_scheduled_start_date,l_scheduled_completion_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_start_date = cur_oper.first_unit_start_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_completion_date = cur_oper.last_unit_completion_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE WIP_OPERATIONS
SET PREVIOUS_OPERATION_SEQ_NUM = NULL,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = each_oper.operation_seq_num;
UPDATE WIP_OPERATIONS
SET PREVIOUS_OPERATION_SEQ_NUM = previous_operation,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = each_oper.operation_seq_num;
UPDATE WIP_OPERATIONS
SET NEXT_OPERATION_SEQ_NUM = each_oper.operation_seq_num,
LAST_UPDATE_DATE=sysdate/*BUG 6721823*/
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = previous_operation;
SELECT WJDI.parent_header_id, WJDI.operation_seq_num, WJDI.standard_operation_id,
WJDI.department_id, WJDI.description , WJDI.first_unit_start_date,
WJDI.first_unit_completion_date, WJDI.last_unit_start_date,
WJDI.last_unit_completion_date, WJDI.minimum_transfer_quantity,
WJDI.count_point_type, WJDI.backflush_flag,WJDI.last_update_date,
WJDI.last_updated_by, WJDI.creation_date,WJDI.created_by, WJDI.last_update_login,
WJDI.request_id, WJDI.program_application_id, WJDI.program_id, WJDI.program_update_date,
WJDI.long_description,
WJDI.attribute_category, WJDI.attribute1, WJDI.attribute2, WJDI.attribute3,
WJDI.attribute4, WJDI.attribute5,
WJDI.attribute6, WJDI.attribute7, WJDI.attribute8, WJDI.attribute9, WJDI.attribute10,
WJDI.attribute11, WJDI.attribute12, WJDI.attribute13, WJDI.attribute14, WJDI.attribute15,
WO.standard_operation_id curr_standard_operation_id
FROM WIP_JOB_DTLS_INTERFACE WJDI, WIP_OPERATIONS WO
WHERE 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_OPERATION
AND WJDI.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE
AND WO.wip_entity_id = p_wip_entity_id
AND WO.organization_id = p_organization_id
AND WO.operation_seq_num = WJDI.operation_seq_num;
/* fix for bug#2653621, disable update of std_op_id and its department, same as form */
if (cur_oper.curr_standard_operation_id is not null or cur_oper.standard_operation_id is not null ) then
WIP_JSI_Utils.record_error_text
( 'Changing of Std Operation ID, and its associated Department ID in an operation is disallowed',
true);
UPDATE WIP_OPERATIONS
SET last_update_date = sysdate,/*BUG 6721823*/
last_updated_by = NVL(cur_oper.last_updated_by,last_updated_by),
creation_date = NVL(cur_oper.creation_date,creation_date),
created_by = NVL(cur_oper.created_by, created_by),
last_update_login = NVL(cur_oper.last_update_login,last_update_login),
request_id = NVL(cur_oper.request_id ,request_id),
program_application_id =NVL(cur_oper.program_application_id,
program_application_id),
program_id = NVL(cur_oper.program_id,program_id),
program_update_date = NVL(cur_oper.program_update_date,
program_update_date),
standard_operation_id = NVL(cur_oper.standard_operation_id,
standard_operation_id),
department_id = NVL(cur_oper.department_id,department_id),
description = NVL(cur_oper.description,description),
first_unit_start_date = NVL(cur_oper.first_unit_start_date,
first_unit_start_date),
first_unit_completion_date =NVL(cur_oper.first_unit_completion_date,
first_unit_completion_date),
last_unit_start_date = NVL(cur_oper.last_unit_start_date,
last_unit_start_date),
last_unit_completion_date = NVL(cur_oper.last_unit_completion_date,
last_unit_completion_date),
count_point_type = NVL(cur_oper.count_point_type,count_point_type),
backflush_flag = NVL(cur_oper.backflush_flag,backflush_flag),
minimum_transfer_quantity = NVL(cur_oper.minimum_transfer_quantity,
minimum_transfer_quantity),
long_description = NVL(cur_oper.long_description,
long_description),
attribute_category = NVL(cur_oper.attribute_category,
attribute_category),
attribute1 = NVL(cur_oper.attribute1,attribute1),
attribute2 = NVL(cur_oper.attribute2,attribute2),
attribute3 = NVL(cur_oper.attribute3,attribute3),
attribute4 = NVL(cur_oper.attribute4,attribute4),
attribute5 = NVL(cur_oper.attribute5,attribute5),
attribute6 = NVL(cur_oper.attribute6,attribute6),
attribute7 = NVL(cur_oper.attribute7,attribute7),
attribute8 = NVL(cur_oper.attribute8,attribute8),
attribute9 = NVL(cur_oper.attribute9,attribute9),
attribute10 = NVL(cur_oper.attribute10,attribute10),
attribute11 = NVL(cur_oper.attribute11,attribute11),
attribute12 = NVL(cur_oper.attribute12,attribute12),
attribute13 = NVL(cur_oper.attribute13,attribute13),
attribute14 = NVL(cur_oper.attribute14,attribute14),
attribute15 = NVL(cur_oper.attribute15,attribute15)
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and operation_seq_num = cur_oper.operation_seq_num;
/* Bug 5026773 - Update Material Rquirements to reflect the changed operation. */
WIP_OPERATIONS_UTILITIES.Update_Reqs
(p_wip_entity_id,
p_organization_id,
cur_oper.operation_seq_num,
NULL, /* repetitive schedule id */
cur_oper.department_id,
cur_oper.first_unit_start_date);
select scheduled_start_date,scheduled_completion_date
into l_scheduled_start_date,l_scheduled_completion_date
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_start_date = cur_oper.first_unit_start_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE wip_discrete_jobs
set scheduled_completion_date = cur_oper.last_unit_completion_date
where wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id;
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET WRO.DATE_REQUIRED =
(SELECT FIRST_UNIT_START_DATE
FROM WIP_OPERATIONS
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM)
AND ORGANIZATION_ID = p_organization_id
),
LAST_UPDATED_BY = nvl(cur_oper.last_updated_by, last_updated_by),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = nvl(cur_oper.last_update_login, last_update_login),
REQUEST_ID = nvl(cur_oper.request_id, request_id),
PROGRAM_UPDATE_DATE = SYSDATE,
PROGRAM_ID = nvl(cur_oper.program_id, program_id),
PROGRAM_APPLICATION_ID = nvl(cur_oper.program_application_id, program_application_id)
WHERE WIP_ENTITY_ID = p_wip_entity_id
AND OPERATION_SEQ_NUM = cur_oper.operation_seq_num
AND ORGANIZATION_ID = p_organization_id ;
select wjdi.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.load_type = wip_job_details.wip_operation
and wjdi.substitution_type in (wip_job_details.wip_add, wip_job_details.wip_change)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and (wjdi.first_unit_start_date is not null or
wjdi.last_unit_completion_date is not null)
/* Fix for Bug#6394857(FP of 6370245).
and wjdi.group_id not in (select wjsi.group_id
from wip_job_schedule_interface wjsi
where wjsi.group_id = p_group_id
and wjsi.source_code = 'MSC'
)
*/
and wjdi.operation_seq_num =
(select wo.operation_seq_num
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.organization_id = p_organization_id
and wo.operation_seq_num = wjdi.operation_seq_num
and (wo.first_unit_start_date >
(select min(start_date)
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 = wo.operation_seq_num)
or
wo.last_unit_completion_date <
(select max(completion_date)
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 = wo.operation_seq_num)));
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.load_type = wip_job_details.wip_operation
and wjdi.substitution_type in (wip_job_details.wip_add, wip_job_details.wip_change)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and (wjdi.first_unit_start_date is not null or
wjdi.last_unit_completion_date is not null)
and wjdi.operation_seq_num =
(select wo.operation_seq_num
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id
and wo.organization_id = p_organization_id
and wo.operation_seq_num = wjdi.operation_seq_num
and (wo.first_unit_start_date >
(select min(start_date)
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 = wo.operation_seq_num)
or
wo.last_unit_completion_date <
(select max(completion_date)
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 = wo.operation_seq_num)));
Procedure Delete_Resource_Usage(p_wip_entity_id number,
p_organization_id number,
p_operation_seq_num number,
p_resource_seq_num number,
x_err_code out NOCOPY varchar2,
x_err_msg out NOCOPY varchar2) IS
BEGIN
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
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;
END DELETE_RESOURCE_USAGE;
SELECT operation_seq_num, resource_seq_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 in (WIP_JOB_DETAILS.WIP_RES_USAGE,
WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
Cursor Usage_Update (p_group_id number, p_wip_entity_id number,
p_organization_id number, p_operation_seq_num number,
p_resource_seq_num number) IS
SELECT distinct wip_entity_id , organization_id, operation_seq_num,
resource_seq_num, resource_instance_id, start_date,
completion_date, assigned_units, resource_serial_number,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, program_application_id, request_id, program_id,
program_update_date, substitution_type
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 operation_seq_num = p_operation_seq_num
AND resource_seq_num = p_resource_seq_num
AND load_type in (WIP_JOB_DETAILS.WIP_RES_USAGE,
WIP_JOB_DETAILS.WIP_RES_INSTANCE_USAGE)
AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
/* delete all existing resource and resource instance usages.*/
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
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;
/**********************DELETE ALL EXISTING RECORDS BEFORE ADD *************/
FOR cur_update IN Usage_Update(p_group_id , p_wip_entity_id,
p_organization_id, p_operation_seq_num,
p_resource_seq_num) LOOP
IF cur_update.substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
INSERT INTO WIP_OPERATION_RESOURCE_USAGE
( WIP_ENTITY_ID ,
ORGANIZATION_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
INSTANCE_ID,
START_DATE ,
COMPLETION_DATE,
ASSIGNED_UNITS ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SERIAL_NUMBER)
VALUES
( cur_update.wip_entity_id,
cur_update.organization_id,
cur_update.operation_seq_num,
cur_update.resource_seq_num,
cur_update.resource_instance_id,
cur_update.start_date,
cur_update.completion_date,
cur_update.assigned_units,
sysdate,/*BUG 6721823*/
cur_update.last_updated_by,
cur_update.creation_date,
cur_update.created_by,
cur_update.last_update_login,
cur_update.request_id,
cur_update.program_application_id,
cur_update.program_id,
cur_update.program_update_date,
cur_update.resource_serial_number);
Update_cumulative_time(p_wip_entity_id,
p_operation_seq_num,
p_resource_seq_num);
select count(*) into x_count
from wip_job_dtls_interface
where group_id = p_group_id /* Fix for bug#3636378 */
and 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
and load_type = WIP_JOB_DETAILS.WIP_RES_USAGE;
INSERT INTO WIP_OPERATION_RESOURCE_USAGE
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
START_DATE,
COMPLETION_DATE,
ASSIGNED_UNITS)
SELECT
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
START_DATE,
COMPLETION_DATE,
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;
/*Bug 5727185/5576967: Update cumulative processing time for WORU record*/
Update_cumulative_time(p_wip_entity_id,
p_operation_seq_num,
p_resource_seq_num);
SELECT distinct operation_seq_num, next_network_op_seq_num,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login,
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,batch_id
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_OP_LINK
AND substitution_type = WIP_JOB_DETAILS.WIP_ADD;
/* insert into table */
INSERT INTO WIP_OPERATION_NETWORKS(
prior_operation,
next_operation,
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15)
VALUES (
cur_row.operation_seq_num,
cur_row.next_network_op_seq_num,
p_wip_entity_id,
p_organization_id,
sysdate,/*BUG 6721823*/
cur_row.last_updated_by,
cur_row.creation_date,
cur_row.created_by,
cur_row.last_update_login,
cur_row.attribute_category,
cur_row.attribute1,
cur_row.attribute2,
cur_row.attribute3,
cur_row.attribute4,
cur_row.attribute5,
cur_row.attribute6,
cur_row.attribute7,
cur_row.attribute8,
cur_row.attribute9,
cur_row.attribute10,
cur_row.attribute11,
cur_row.attribute12,
cur_row.attribute13,
cur_row.attribute14,
cur_row.attribute15);
Procedure Delete_Op_Link (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_err_code out NOCOPY varchar2,
p_err_msg out NOCOPY varchar2) IS
CURSOR op_link_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT distinct operation_seq_num, next_network_op_seq_num,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login,
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,batch_id
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_OP_LINK
AND substitution_type = WIP_JOB_DETAILS.WIP_DELETE;
DELETE FROM WIP_OPERATION_NETWORKS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND prior_operation = cur_row.operation_seq_num
AND next_operation = cur_row.next_network_op_seq_num;
p_err_msg := 'WIPJDSTB, Delete_Op_Link: ' || SQLERRM;
END Delete_Op_Link;
SELECT wjdi.serial_number_new serial_number,
nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
nvl(we.organization_id, wjsi.organization_id) organization_id
FROM wip_job_dtls_interface wjdi,
wip_job_schedule_interface wjsi,
wip_entities we
WHERE wjdi.group_id = p_group_id
AND wjsi.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_SERIAL
AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_ADD
AND wjdi.parent_header_id = wjsi.header_id
AND wjsi.wip_entity_id = we.wip_entity_id (+);
wip_utilities.update_serial(p_serial_number => l_serRec.serial_number,
p_organization_id => l_serRec.organization_id,
p_inventory_item_id => l_serRec.primary_item_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => x_return_status);
SELECT wjdi.serial_number_new,
wjdi.serial_number_old,
nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
nvl(we.organization_id, wjsi.organization_id) organization_id
FROM wip_job_dtls_interface wjdi,
wip_job_schedule_interface wjsi,
wip_entities we
WHERE wjdi.group_id = p_group_id
AND wjsi.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_SERIAL
AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE
and wjdi.parent_header_id = wjsi.header_id
and wjsi.wip_entity_id = we.wip_entity_id (+);
wip_utilities.update_serial(p_serial_number => l_serRec.serial_number_new,
p_organization_id => l_serRec.organization_id,
p_inventory_item_id => l_serRec.primary_item_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => x_return_status);
wip_utilities.update_serial(p_serial_number => l_serRec.serial_number_old,
p_organization_id => l_serRec.organization_id,
p_inventory_item_id => l_serRec.primary_item_id,
p_wip_entity_id => null,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => x_return_status);
Procedure Delete_Serial_Association (p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
x_err_code out NOCOPY varchar2,
x_err_msg out NOCOPY varchar2,
x_return_status out NOCOPY varchar2) IS
CURSOR ser_info ( p_group_id number,
p_wip_entity_id number,
p_organization_id number) IS
SELECT wjdi.serial_number_old serial_number,
nvl(we.primary_item_id, wjsi.primary_item_id) primary_item_id,
nvl(we.organization_id, wjsi.organization_id) organization_id
FROM wip_job_dtls_interface wjdi,
wip_job_schedule_interface wjsi,
wip_entities we
WHERE wjdi.group_id = p_group_id
AND wjsi.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_SERIAL
AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_DELETE
AND wjdi.parent_header_id = wjsi.header_id
AND wjsi.wip_entity_id = we.wip_entity_id (+);
wip_utilities.update_serial(p_serial_number => l_serRec.serial_number,
p_organization_id => l_serRec.organization_id,
p_inventory_item_id => l_serRec.primary_item_id,
p_wip_entity_id => null,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => x_return_status);
END Delete_Serial_Association;
select default_wip_auto_assoc_sn
into l_defaultSN
from wip_parameters
where organization_id = p_organization_id;
select load_type
into l_load_type
from wip_job_schedule_interface
where rowid = p_rowid;
SELECT count(*)
INTO l_rowCount
FROM mtl_serial_numbers
WHERE wip_entity_id = p_wip_entity_id;
SELECT start_quantity,
primary_item_id,
serialization_start_op
INTO l_start_quantity,
l_primary_item_id,
l_serialization_start_op
FROM wip_discrete_jobs
WHERE wip_entity_id = p_wip_entity_id;
Procedure Update_Cumulative_Time (
p_wip_entity_id in number,
p_operation_seq_num in number,
p_resource_seq_num in number) IS
cursor res_usage (p_wip_entity_id number,
p_operation_seq_num number,
p_resource_seq_num number) is
select start_date,
completion_date,
cumulative_processing_time
from wip_operation_resource_usage
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num
and instance_id is null
order by start_date
for update;
update wip_operation_resource_usage
set cumulative_processing_time = current_cpt
where current of res_usage;
end update_cumulative_time;