[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* resource_seq_num, resource_id NOT NULL when delete/change resource */
procedure del_res_info_exist(p_group_id in number,
p_wip_entity_id in number,
p_organization_id in number,
p_substitution_type in number,
p_operation_seq_num in number) IS
cursor c_invalid_rows is
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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.resource_id_old is null);
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_resource, wip_job_details.wip_sub_res)
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.resource_id_old is null);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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_id_old = p_resource_id_old
and not ( (exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.resource_id = wjdi.resource_id_old))
or
(exists (select 1
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = wjdi.wip_entity_id
and wsor.organization_id = wjdi.organization_id
and wsor.operation_seq_num = wjdi.operation_seq_num
and wsor.resource_seq_num = wjdi.resource_seq_num
and wsor.resource_id = wjdi.resource_id_old))
);
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_resource, wip_job_details.wip_sub_res)
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_id_old = p_resource_id_old
and not ( (exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.resource_id = wjdi.resource_id_old))
or
(exists (select 1
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = wjdi.wip_entity_id
and wsor.organization_id = wjdi.organization_id
and wsor.operation_seq_num = wjdi.operation_seq_num
and wsor.resource_seq_num = wjdi.resource_seq_num
and wsor.resource_id = wjdi.resource_id_old))
);
Procedure Safe_Delete (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_id_old number) IS
cursor c_invalid_wor_rows is
select interface_id
from wip_job_dtls_interface wjdi, wip_operation_resources wor
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_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and 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.resource_id = p_resource_id_old
and wor.applied_resource_units <> 0;
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_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( exists (select 1
from wip_transactions wt
where wt.wip_entity_id = p_wip_entity_id
and wt.organization_id = p_organization_id
and wt.operation_seq_num = p_operation_seq_num
and wt.resource_seq_num = p_resource_seq_num
and wt.resource_id = p_resource_id_old)
or exists (select 1
from wip_cost_txn_interface wcti, bom_resources br
where wcti.wip_entity_id = p_wip_entity_id
and wcti.organization_id = p_organization_id
and wcti.operation_seq_num = p_operation_seq_num
and wcti.resource_seq_num = p_resource_seq_num
and wcti.resource_code = br.resource_code (+)
and wcti.organization_id = br.organization_id (+)
and ( wcti.resource_id = p_resource_id_old
or br.resource_id = p_resource_id_old))
);
update wip_job_dtls_interface wjdi
set wjdi.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_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and 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.resource_id = p_resource_id_old
and wor.applied_resource_units <> 0);
fnd_message.set_name('WIP', 'WIP_JDI_DELETE_RESOURCE');
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_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and ( exists (select 1
from wip_transactions wt
where wt.wip_entity_id = p_wip_entity_id
and wt.organization_id = p_organization_id
and wt.operation_seq_num = p_operation_seq_num
and wt.resource_seq_num = p_resource_seq_num
and wt.resource_id = p_resource_id_old)
or exists (select 1
from wip_cost_txn_interface wcti, bom_resources br
where wcti.wip_entity_id = p_wip_entity_id
and wcti.organization_id = p_organization_id
and wcti.operation_seq_num = p_operation_seq_num
and wcti.resource_seq_num = p_resource_seq_num
and wcti.resource_code = br.resource_code (+)
and wcti.organization_id = br.organization_id (+)
and ( wcti.resource_id = p_resource_id_old
or br.resource_id = p_resource_id_old))
);
end safe_delete;
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_resource
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_id_old = p_resource_id_old
-- Bug 4321480 - Modified the exists clause to exclude Cancelled PO/POR.
and (exists
( SELECT 'PO/REQ Linked'
FROM PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PLL
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND PD.WIP_ENTITY_ID = p_wip_entity_id
AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PD.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PD.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215)*/
AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
-- check cancel flag at shipment level instead of at header level
-- because PO will cancel upto shipment level
AND (pll.cancel_flag IS NULL OR
pll.cancel_flag = 'N')
AND (PLL.QUANTITY_RECEIVED <
(PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PRL.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PRL.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
AND nvl(PRL.cancel_flag, 'N') = 'N'
AND PRL.LINE_LOCATION_ID is NULL
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PRI.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PRI.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */));
SELECT propagate_job_change_to_po
INTO l_propagate_job_change_to_po
FROM wip_parameters
WHERE organization_id = p_organization_id;
fnd_message.set_name('WIP', 'WIP_DELETE_OSP_RESOURCE');
update wip_job_dtls_interface wjdi
set process_status = wip_constants.warning
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_resource
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_id_old = p_resource_id_old
-- Bug 4321480 - Modified the exists clause to exclude Cancelled PO/POR.
and (exists
( SELECT 'PO/REQ Linked'
FROM PO_RELEASES_ALL PR,
PO_HEADERS_ALL PH,
PO_DISTRIBUTIONS_ALL PD,
PO_LINE_LOCATIONS_ALL PLL
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND PD.WIP_ENTITY_ID = p_wip_entity_id
AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PD.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PD.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
-- check cancel flag at shipment level instead of at header level
-- because PO will cancel upto shipment level
AND (pll.cancel_flag IS NULL OR
pll.cancel_flag = 'N')
AND (PLL.QUANTITY_RECEIVED <
(PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITION_LINES_ALL PRL
WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PRL.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PRL.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
AND nvl(PRL.cancel_flag, 'N') = 'N'
AND PRL.LINE_LOCATION_ID is NULL
UNION ALL
SELECT 'PO/REQ Linked'
FROM PO_REQUISITIONS_INTERFACE_ALL PRI
WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
AND (p_operation_seq_num is NULL OR
PRI.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
AND PRI.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */));
Procedure Delete_Resource (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type number) IS
CURSOR res_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type 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 = p_substitution_type;
Safe_Delete (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
cur_row.resource_id_old);
END Delete_Resource;
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_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and (wjdi.usage_rate_or_amount is null
or wjdi.resource_id_new 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 = wip_job_details.wip_resource
and wjdi.substitution_type = p_substitution_type
and wjdi.operation_seq_num = p_operation_seq_num
and (wjdi.usage_rate_or_amount is null
or wjdi.resource_id_new is null);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, 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_id_new = p_resource_id_new
and nvl(wjdi.start_date, sysdate) > nvl(wjdi.completion_date, sysdate);
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_resource, 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_id_new = p_resource_id_new
and nvl(wjdi.start_date, sysdate) > nvl(wjdi.completion_date, sysdate);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, 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_id_old = p_resource_id_old
and (wjdi.start_date is not null
or wjdi.completion_date is not null)
and exists
(select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.resource_id = wjdi.resource_id_old
and nvl(wjdi.start_date, wor.start_date) > nvl(wjdi.completion_date, wor.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 in (wip_job_details.wip_resource, 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_id_old = p_resource_id_old
and (wjdi.start_date is not null
or wjdi.completion_date is not null)
and exists
(select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.resource_id = wjdi.resource_id_old
and nvl(wjdi.start_date, wor.start_date) > nvl(wjdi.completion_date, wor.completion_date));
select interface_id
from wip_job_dtls_interface wjdi, wip_operations wo
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, 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_id_new = p_resource_id_new
and 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 not ( wjdi.load_type = wip_job_details.wip_resource
and wjdi.substitution_type = wip_job_details.wip_change
and wjdi.substitute_group_num is not null
and wjdi.replacement_group_num is not null
)
and ( not exists(select 1
from bom_resources br
where br.resource_id = p_resource_id_new
and ( br.disable_date > sysdate
or br.disable_date is null)
and br.organization_id = p_organization_id)
or not exists(select 1
from bom_department_resources bdr
where bdr.resource_id = p_resource_id_new
and bdr.department_id = wo.department_id)
);
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_resource, 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_id_new = p_resource_id_new
and not ( wjdi.load_type = wip_job_details.wip_resource
and wjdi.substitution_type = wip_job_details.wip_change
and wjdi.substitute_group_num is not null
and wjdi.replacement_group_num is not null
)
and ( not exists(select 1
from bom_resources br
where br.resource_id = p_resource_id_new
and ( br.disable_date > sysdate
or br.disable_date is null)
and br.organization_id = p_organization_id)
or not exists(select 1
from bom_department_resources bdr, wip_operations wo
where bdr.resource_id = p_resource_id_new
and 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 bdr.department_id = wo.department_id)
);
select wjdi.interface_id
from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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 ( resource_seq_num <= 0
or exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num)
or exists (select 1
from wip_sub_operation_resources wsor
where wsor.wip_entity_id = wjdi.wip_entity_id
and wsor.organization_id = wjdi.organization_id
and wsor.operation_seq_num = wjdi.operation_seq_num
and wsor.resource_seq_num = wjdi.resource_seq_num)
)
-- Bug#5752548 skip the Resource_Seq_Num validation for setup resources
-- inserted by ASCP because all existing setup resources would be deleted
-- before adding new setup resource.
and wjsi.organization_id = wjdi.organization_id
and wjsi.group_id = wjdi.group_id
and wjsi.header_id = wjdi.parent_header_id
and (wjsi.source_code <> 'MSC' or wjdi.parent_seq_num 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_resource, wip_job_details.wip_sub_res)
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 ( resource_seq_num <= 0
or exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num)
);
select interface_id, wjdi.usage_rate_or_amount
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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_id_new = p_resource_id_new
-- jy: no need to do this validation if doing res substitution
and not ( wjdi.load_type = wip_job_details.wip_resource
and wjdi.substitution_type = wip_job_details.wip_change
and wjdi.substitute_group_num is not null
and wjdi.replacement_group_num is not null
);
select uom.uom_class
into l_uom_class
from bom_resources br, mtl_units_of_measure_vl uom
where br.resource_id = p_resource_id_new
and br.unit_of_measure = uom.uom_code;
select autocharge_type
into l_autocharge_type
from bom_resources
where resource_id = p_resource_id_new;
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_resource, wip_job_details.wip_sub_res)
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_id_new = p_resource_id_new
and not ( wjdi.load_type = wip_job_details.wip_resource
and wjdi.substitution_type = wip_job_details.wip_change
and wjdi.substitute_group_num is not null
and wjdi.replacement_group_num is not 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_resource, wip_job_details.wip_sub_res)
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_id_new = p_resource_id_new
and wjdi.usage_rate_or_amount = p_usage_rate_or_amount
and not ( wjdi.load_type = wip_job_details.wip_resource
and wjdi.substitution_type = wip_job_details.wip_change
and wjdi.substitute_group_num is not null
and wjdi.replacement_group_num is not null
);
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (
wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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.assigned_units <= 0
or (wjdi.assigned_units is null
and wjdi.substitution_type = wip_job_details.wip_add)
or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
and (wjdi.setup_id is not null or exists
(select 1
from bom_resources br
where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
and br.batchable = 1
))
)
-- Bug 5172555
-- The maximum_assigned_units should not be validated
-- So commented out the clause
-- ntungare Thu May 11 05:59:01 PDT 2006
--
-- or (wjdi.assigned_units >
-- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
);
select maximum_assigned_units
into l_maximum_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 maximum_assigned_units
into l_maximum_assigned_units
from wip_sub_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;
update wip_job_dtls_interface wjdi
set wjdi.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_resource, wip_job_details.wip_sub_res)
and wjdi.substitution_type = p_substitution_type
and (wjdi.assigned_units <= 0
or (wjdi.assigned_units is null
and wjdi.substitution_type = wip_job_details.wip_add)
or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
and (wjdi.setup_id is not null or exists
(select 1
from bom_resources br
where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
))
)
-- Bug 5172555
-- The maximum_assigned_units should not be validated
-- So commented out the clause
-- ntungare Thu May 11 05:59:01 PDT 2006
--
-- or (wjdi.assigned_units >
-- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
);
select nvl(max(resource_seq_num), 0)
into x_res_seq_num_max
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;
select nvl(max(resource_seq_num), 0)
into x_res_seq_num
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 substitution_type = p_substitution_type;
UPDATE WIP_JOB_DTLS_INTERFACE
SET resource_seq_num = x_res_seq_num_max
WHERE rowid = p_rowid;
select schedule_seq_num
into x_schedule_seq_num
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_parent_seq_num
and load_type = 1
and substitution_type in (2,3);
select schedule_seq_num
into x_schedule_seq_num
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_parent_seq_num;
UPDATE WIP_JOB_DTLS_INTERFACE
SET schedule_seq_num = x_schedule_seq_num
WHERE rowid = p_rowid;
select setup_id
into x_setup_id
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;
UPDATE WIP_JOB_DTLS_INTERFACE
SET setup_id = nvl(setup_id, x_setup_id)
WHERE rowid = p_rowid;
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, 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_RESOURCE
AND substitution_type = p_substitution_type;
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_resource
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.assigned_units < 0 or
(nvl(wjdi.assigned_units,-1) <> l_count and l_count > 0));
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;
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_resource)
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.assigned_units < 0 or
(wjdi.assigned_units <> l_count and l_count > 0));
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
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.resource_id_old is null
or wjdi.resource_id_new is null
or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
and wjdi.usage_rate_or_amount 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_resource, wip_job_details.wip_sub_res)
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.resource_id_old is null
or wjdi.resource_id_new is null
or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
and wjdi.usage_rate_or_amount is null
)
);
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, 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_RESOURCE
AND substitution_type = p_substitution_type;
Safe_Delete (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
cur_row.resource_id_old);
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_RESOURCE
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND resource_seq_num = p_resource_seq_num;
IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
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_RESOURCE
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND (resource_seq_num IS NULL
OR resource_id_old IS NULL);
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_RESOURCE
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND (resource_id_new IS NULL
OR usage_rate_or_amount IS NULL);
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_RESOURCE
AND substitution_type= p_substitution_type
AND operation_seq_num= p_operation_seq_num
AND (resource_seq_num IS NULL
OR resource_id_old IS NULL
OR resource_id_new IS NULL
OR usage_rate_or_amount IS NULL);
Procedure Delete_Sub_Resource (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type number) IS
CURSOR res_info (p_group_id number,
p_wip_entity_id number,
p_organization_id number,
p_substitution_type 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 = p_substitution_type;
END Delete_Sub_Resource;
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_resource
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_id_old = p_resource_id_old
and ( ( wjdi.substitute_group_num is not null
and not exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num
)
)
or ( wjdi.replacement_group_num is not null
and not exists (select 1
from wip_sub_operation_resources wsor,
wip_operation_resources wor
where wsor.wip_entity_id = wjdi.wip_entity_id
and wsor.organization_id = wjdi.organization_id
and wsor.operation_seq_num = wjdi.operation_seq_num
and wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
and wsor.replacement_group_num = wjdi.replacement_group_num
)
and not exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and ( (wor.substitute_group_num =
nvl(wjdi.substitute_group_num,wor.substitute_group_num)) OR
(wor.substitute_group_num is null and
wjdi.substitute_group_num is null)
)
and wor.replacement_group_num = wjdi.replacement_group_num
)
)
);
select wjdi.substitute_group_num,
wjdi.replacement_group_num
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_resource
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_id_old = p_resource_id_old
and ( wjdi.replacement_group_num is null
or ( wjdi.replacement_group_num is not null
and exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and ( (wor.substitute_group_num =
nvl(wjdi.substitute_group_num, wor.substitute_group_num)) OR
(wor.substitute_group_num is null and
wjdi.substitute_group_num is null)
)
and wor.replacement_group_num = wjdi.replacement_group_num
)
)
)
for update;
select wip_entity_id,
organization_id,
resource_id_old,
operation_seq_num,
resource_seq_num,
substitute_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_resource
and substitution_type = p_substitution_type
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num
and resource_id_old = p_resource_id_old
and replacement_group_num is not null
for update;
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_resource
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_id_old = p_resource_id_old
and ( ( wjdi.substitute_group_num is not null
and not exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num
)
)
or ( wjdi.replacement_group_num is not null
and not exists (select 1
from wip_sub_operation_resources wsor,
wip_operation_resources wor
where wsor.wip_entity_id = wjdi.wip_entity_id
and wsor.organization_id = wjdi.organization_id
and wsor.operation_seq_num = wjdi.operation_seq_num
and wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.resource_id = wjdi.resource_id_old
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.resource_seq_num = wjdi.resource_seq_num
and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
and wsor.replacement_group_num = wjdi.replacement_group_num
)
)
);
update wip_job_dtls_interface wjdi
set substitute_group_num = null,
replacement_group_num = null
where current of c_not_sub_rows;
select wor.substitute_group_num
into l_sub_group_temp
from wip_operation_resources wor
where wor.wip_entity_id = l_sub_row.wip_entity_id
and wor.organization_id = l_sub_row.organization_id
and wor.resource_id = l_sub_row.resource_id_old
and wor.operation_seq_num = l_sub_row.operation_seq_num
and wor.resource_seq_num = l_sub_row.resource_seq_num;
update wip_job_dtls_interface
set substitute_group_num = l_sub_group_temp
where current of c_sub_rows;
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_sub_res
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.schedule_seq_num < 0
or wjdi.substitute_group_num is null
or wjdi.substitute_group_num < 0
or wjdi.replacement_group_num is null
or wjdi.replacement_group_num < 0
or not exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num)
or exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num
and wor.replacement_group_num = wjdi.replacement_group_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.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type = wip_job_details.wip_sub_res
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.schedule_seq_num is null
or wjdi.schedule_seq_num < 0
or wjdi.substitute_group_num is null
or wjdi.substitute_group_num < 0
or wjdi.replacement_group_num is null
or wjdi.replacement_group_num < 0
or not exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num)
or exists (select 1
from wip_operation_resources wor
where wor.wip_entity_id = wjdi.wip_entity_id
and wor.organization_id = wjdi.organization_id
and wor.operation_seq_num = wjdi.operation_seq_num
and wor.substitute_group_num = wjdi.substitute_group_num
and wor.replacement_group_num = wjdi.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, 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_SUB_RES
AND substitution_type = p_substitution_type;
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
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 = p_substitution_type;
Safe_Delete (p_group_id,
p_wip_entity_id,
p_organization_id,
p_substitution_type,
cur_row.operation_seq_num,
cur_row.resource_seq_num,
cur_row.resource_id_old);
select *
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_resource
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.schedule_seq_num = p_schedule_seq_num)
or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
and ( (wjdi.substitute_group_num = p_substitute_group_num)
or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
and ( (wjdi.replacement_group_num = p_replacement_group_num)
or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
for update;
update wip_job_dtls_interface
set process_status = wip_constants.error
where current of sched_rows;
select distinct replacement_group_num
into repl_group
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id is null
and operation_seq_num = p_operation_seq_num
and substitute_group_num = sched_row.substitute_group_num;
update wip_job_dtls_interface
set replacement_group_num = repl_group
where current of sched_rows;
update wip_job_dtls_interface
set replacement_group_num = null
where current of sched_rows;
select schedule_seq_num,
substitute_group_num,
replacement_group_num
into sched_seq,
sub_group,
repl_group
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id is null
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
update wip_job_dtls_interface
set schedule_seq_num = sched_seq
where current of sched_rows;
update wip_job_dtls_interface
set replacement_group_num = fnd_api.g_miss_num
where current of sched_rows;
select 1
into l_res_sub
from dual
where exists (select 1
from wip_sub_operation_resources
where wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and substitute_group_num = sub_group
and replacement_group_num = sched_row.replacement_group_num);
select distinct replacement_group_num
into repl_group
from wip_operation_resources
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id is null
and operation_seq_num = p_operation_seq_num
and substitute_group_num = sched_row.substitute_group_num;
update wip_job_dtls_interface
set replacement_group_num = repl_group
where current of sched_rows;
update wip_job_dtls_interface
set replacement_group_num = repl_group,
substitute_group_num = sub_group
where current of sched_rows;
select *
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_sub_res
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.schedule_seq_num = p_schedule_seq_num)
or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
and ( (wjdi.substitute_group_num = p_substitute_group_num)
or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
and ( (wjdi.replacement_group_num = p_replacement_group_num)
or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
for update;
update wip_job_dtls_interface
set process_status = wip_constants.error
where current of sched_rows;
update wip_job_dtls_interface
set process_status = wip_constants.error
where current of sched_rows;
select schedule_seq_num,
substitute_group_num,
replacement_group_num
into sched_seq,
sub_group,
repl_group
from wip_sub_operation_resources
where wip_entity_id = p_wip_entity_id
and repetitive_schedule_id is null
and operation_seq_num = p_operation_seq_num
and resource_seq_num = p_resource_seq_num;
update wip_job_dtls_interface
set schedule_seq_num = sched_seq
where current of sched_rows;
update wip_job_dtls_interface
set substitute_group_num = sub_group,
replacement_group_num = repl_group
where current of sched_rows;
update wip_job_dtls_interface
set process_status = wip_constants.error
where current of sched_rows;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status in (wip_constants.running,
wip_constants.warning)
and wjdi.wip_entity_id = p_wip_entity_id
and wjdi.organization_id = p_organization_id
and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
and wjdi.operation_seq_num = p_operation_seq_num;
update wip_job_dtls_interface wjdi
set wjdi.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_resource, wip_job_details.wip_sub_res)
and wjdi.operation_seq_num = l_op_seq;
wip_op_resources_utilities.delete_orphaned_alternates(p_wip_entity_id, null, l_status);