[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct wip_entity_id,
organization_id
from wip_job_dtls_interface
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING) ;
select distinct wip_entity_id,
organization_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) ;
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_OPERATION
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING) ;
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_RESOURCE
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1 )
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
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 process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_SUB_RES
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_OP_LINK
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING ,WIP_CONSTANTS.WARNING);
select distinct wip_entity_id,
organization_id,
load_type, substitution_type
from wip_job_dtls_interface
where group_id = p_group_id
and ((p_std_alone = 0
and parent_header_id = p_parent_header_id)
OR p_std_alone = 1)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and load_type = WIP_JOB_DETAILS.WIP_SERIAL
and process_phase = WIP_CONSTANTS.ML_VALIDATION
and process_status IN (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
select interface_id
into wip_jsi_utils.current_interface_id
from wip_job_schedule_interface
where group_id = p_group_id
and header_id = p_parent_header_id;
WIP_JOB_DTLS_VALIDATIONS.Last_Updated_By(P_Group_Id,
p_parent_header_id,
cur_job.wip_entity_id,
cur_job.organization_id);
IF l_cur.substitution_type = WIP_DELETE THEN
BEGIN
WIP_RESOURCE_VALIDATIONS.Delete_Resource(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type);
wip_logger.log('Error in WIP_RESOURCE_VALIDATIONS.Delete_Resource,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
x_err_code,
x_err_msg);
wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
IF l_cur.substitution_type = WIP_DELETE THEN
BEGIN
WIP_RES_INST_VALIDATIONS.Delete_Resource_Instance(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type,
x_err_code,
x_err_msg);
wip_logger.log('Error in WIP_RES_INST_VALIDATIONS.Delete_Resource_Instance,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE_INSTANCE(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
WIP_JOB_DETAILS.WIP_DELETE,
x_err_code,
x_err_msg);
wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_RESOURCE_INSTANCE,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
IF l_cur.substitution_type = WIP_DELETE THEN
BEGIN
WIP_REQUIREMENT_VALIDATIONS.Delete_Req(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type);
wip_logger.log('Error in WIP_REQUIREMENT_VALIDATIONS.Delete_Req,l_cur.wip_entity_id='||l_cur.wip_entity_id,l_dummy2);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_REQUIREMENT(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
x_err_code,
x_err_msg);
wip_logger.log('Error in WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_REQUIREMENT,l_cur.wip_entity_id='||cur_job.wip_entity_id,l_dummy2);
IF l_cur.substitution_type = WIP_DELETE THEN
WIP_RESOURCE_VALIDATIONS.Delete_Sub_Resource(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SUB_RESOURCE(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
x_err_code,
x_err_msg);
IF l_cur.substitution_type = WIP_DELETE THEN
WIP_OP_LINK_VALIDATIONS.Delete_Op_Link(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type,
x_err_code,
x_err_msg,
x_return_status);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_OP_LINK(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
x_err_code,
x_err_msg);
IF l_cur.substitution_type = WIP_DELETE THEN
WIP_SERIAL_ASSOC_VALIDATIONS.Delete_Serial(
p_group_id,
l_cur.wip_entity_id,
l_cur.organization_id,
l_cur.substitution_type);
WIP_JOB_DTLS_SUBSTITUTIONS.DELETE_SERIAL_ASSOCIATION(
p_group_id,
cur_job.wip_entity_id,
cur_job.organization_id,
x_err_code,
x_err_msg,
x_return_status);
for wo_rec in (select * from wip_operations where wip_entity_id = cur_job.wip_entity_id and organization_id = cur_job.organization_id order by operation_seq_num) loop
wip_logger.log('Op Seq '||wo_rec.operation_seq_num||' FUSD '||to_date(wo_rec.first_unit_start_date,WIP_CONSTANTS.DATETIME_FMT)||
' FUCD '||to_date(wo_rec.first_unit_completion_date,WIP_CONSTANTS.DATETIME_FMT) ||
' LUSD '||to_date(wo_rec.last_unit_start_date,WIP_CONSTANTS.DATETIME_FMT) ||
' LUCD '||to_date(wo_rec.last_unit_completion_date,WIP_CONSTANTS.DATETIME_FMT) ,l_dummy2);
for wor_rec in (select * from wip_operation_resources where wip_entity_id = cur_job.wip_entity_id and organization_id = cur_job.organization_id order by operation_seq_num) loop
wip_logger.log('Op Seq '||wor_rec.operation_seq_num||' Res Seq '||wor_rec.resource_seq_num||
' St Dt '||to_date(wor_rec.start_date,WIP_CONSTANTS.DATETIME_FMT) ||
' Cm Dt '||to_date(wor_rec.completion_date,WIP_CONSTANTS.DATETIME_FMT) ,l_dummy2);
SELECT count(*)
INTO x_count
from WIP_JOB_DTLS_INTERFACE
WHERE group_id = p_group_id
AND parent_header_id = p_parent_header_id
AND wip_entity_id = cur_job.wip_entity_id
AND organization_id = cur_job.organization_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status = WIP_CONSTANTS.ERROR ;
UPDATE wip_job_dtls_interface
SET process_status = WIP_CONSTANTS.COMPLETED
WHERE group_id = p_group_id
AND wip_entity_id = cur_job.wip_entity_id
AND (p_parent_header_id IS NULL OR
(p_parent_header_id IS NOT NULL AND
parent_header_id = p_parent_header_id))
AND organization_id = cur_job.organization_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status = WIP_CONSTANTS.RUNNING ;
/* DELETE THE COMPLETED ROWS FROM INTERFACE TABLE */
DELETE from wip_job_dtls_interface
WHERE group_id = p_group_id
AND parent_header_id = p_parent_header_id
AND wip_entity_id = cur_job.wip_entity_id
AND organization_id = cur_job.organization_id
AND process_phase = WIP_CONSTANTS.ML_VALIDATION
AND process_status = WIP_CONSTANTS.COMPLETED;
select interface_id
from wip_job_dtls_interface wjdi
where wjdi.group_id = p_group_id
and wjdi.parent_header_id = p_parent_header_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status = wip_constants.running
and wjdi.parent_header_id is not null
and ( wjdi.wip_entity_id is not null
or wjdi.organization_id is not null);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.warning
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = wip_constants.ml_validation
and process_status = wip_constants.running
and wjdi.parent_header_id is not null
and ( wjdi.wip_entity_id is not null
or wjdi.organization_id is not null);
select wip_entity_id , organization_id
into l_wip_entity_id, l_organization_id
from wip_job_schedule_interface
where header_id = p_parent_header_id
and group_id = p_group_id
and process_status IN (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
Update wip_job_dtls_interface
Set wip_entity_id = l_wip_entity_id,
organization_id = l_organization_id
where group_id = p_group_id
and parent_header_id = p_parent_header_id
and process_phase = 2
and process_status in (2,5);