The following lines contain the word 'select', 'insert', 'update' or 'delete':
select interface_id
from wip_job_dtls_interface
where group_id = p_group_id
and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
and process_phase = wip_constants.ml_validation /* Bug 2751349 */
and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
and substitution_type = p_sub_type
and load_type = wip_job_details.wip_operation
and ( operation_seq_num is null
or department_id is null
or first_unit_start_date is null
or first_unit_completion_date is null
or last_unit_start_date is null
or last_unit_completion_date is null
or minimum_transfer_quantity is null
or count_point_type is null
or backflush_flag is null
);
select interface_id
from wip_job_dtls_interface
where group_id = p_group_id
and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
and process_phase = wip_constants.ml_validation /* Bug 2751349 */
and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
and substitution_type = p_sub_type
and load_type = wip_job_details.wip_operation
and operation_seq_num 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 /* Bug 2751349 */
and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
and substitution_type = p_sub_type
and load_type = wip_job_details.wip_operation
and ( operation_seq_num IS NULL
OR department_id IS NULL
OR first_unit_start_date IS NULL
OR first_unit_completion_date IS NULL
OR last_unit_start_date IS NULL
OR last_unit_completion_date IS NULL
OR minimum_transfer_quantity IS NULL
OR count_point_type IS NULL
OR backflush_flag 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 /* Bug 2751349 */
and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
and substitution_type = p_sub_type
and load_type = wip_job_details.wip_operation
and operation_seq_num IS NULL;
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( department_id is null --can not add w/null dept
and p_sub_type = wip_job_details.wip_add
)
or ( department_id is not null
and not exists (select 1
from bom_departments
where department_id = wjdi.department_id
and organization_id = wjdi.organization_id)
)
);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( department_id is null
and p_sub_type = wip_job_details.wip_add
)
or ( department_id is not null
and not exists (select 1
from bom_departments
where department_id = wjdi.department_id
and organization_id = wjdi.organization_id)
)
);
select interface_id,
operation_seq_num
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_change
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and department_id is not null
and exists (select 1
from wip_operation_resources wor,
wip_operations wo
where wo.wip_entity_id = wjdi.wip_entity_id
and wo.operation_seq_num = wjdi.operation_seq_num
and wo.organization_id = wjdi.organization_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.organization_id = wor.organization_id
and nvl(wo.repetitive_schedule_id, 1) =
nvl(wor.repetitive_schedule_id, 1)
and wo.department_id <> wjdi.department_id
) ;
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_change
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and department_id is not null
and exists (select 1
from wip_operation_resources wor,
wip_operations wo
where wo.wip_entity_id = wjdi.wip_entity_id
and wo.operation_seq_num = wjdi.operation_seq_num
and wo.organization_id = wjdi.organization_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.operation_seq_num = wor.operation_seq_num
and wo.organization_id = wor.organization_id
and nvl(wo.repetitive_schedule_id, 1) =
nvl(wor.repetitive_schedule_id, 1)
and wo.department_id <> wjdi.department_id
) ;
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and standard_operation_id is not null
and standard_operation_id <> fnd_api.g_miss_num
and not exists (select 1
from bom_standard_operations
where standard_operation_id = wjdi.standard_operation_id
and organization_id = wjdi.organization_id);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and standard_operation_id is not null
and standard_operation_id <> fnd_api.g_miss_num
and not exists (select 1
from bom_standard_operations
where standard_operation_id = wjdi.standard_operation_id
and organization_id = wjdi.organization_id);
select interface_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 load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( p_sub_type = wip_job_details.wip_add
and minimum_transfer_quantity is null
)
or minimum_transfer_quantity < 0);
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 load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( minimum_transfer_quantity is null
and p_sub_type = wip_job_details.wip_add
)
or minimum_transfer_quantity < 0 );
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( count_point_type is null
and p_sub_type = wip_job_details.wip_add
)
or ( count_point_type is not null
and not exists (select 1
from mfg_lookups mfg_l
where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
and mfg_l.lookup_code = wjdi.count_point_type )
)
);
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 load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( count_point_type is null
and p_sub_type = wip_job_details.wip_add
)
or ( count_point_type is not null
and not exists (select 1
from mfg_lookups mfg_l
where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
and mfg_l.lookup_code = wjdi.count_point_type )
)
);
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( ( backflush_flag is null
and p_sub_type = wip_job_details.wip_add
)
or ( backflush_flag is not null
and not exists (select 1
from mfg_lookups mfg_l
where mfg_l.lookup_type = 'SYS_YES_NO'
and mfg_l.lookup_code = wjdi.backflush_flag)
)
);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and load_type = wip_job_details.wip_operation
and substitution_type = p_sub_type
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 ( ( backflush_flag is null
and p_sub_type = wip_job_details.wip_add
)
or ( backflush_flag is not null
and not exists (select 1
from mfg_lookups mfg_l
where mfg_l.lookup_type = 'SYS_YES_NO'
and mfg_l.lookup_code = wjdi.backflush_flag)
)
);
select interface_id, operation_seq_num
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and (exists (select 1
from wip_operations
where wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num) or
operation_seq_num <= 0);
update wip_job_dtls_interface wjdi
set process_status = wip_constants.error
where group_id = p_group_id
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
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 (exists (select 1
from wip_operations
where wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num)
or operation_seq_num <= 0);
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and parent_header_id = p_parent_header_id
and ( wjdi.first_unit_start_date is null
or wjdi.first_unit_completion_date is null
or wjdi.last_unit_start_date is null
or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
or wjdi.first_unit_start_date > wjdi.last_unit_start_date
or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
);
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( first_unit_start_date is null
or first_unit_completion_date is null
or last_unit_start_date is null
or last_unit_completion_date is null
/* bug3669728 begin */
or wjdi.first_unit_start_date > wjdi.last_unit_start_date
or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
);
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 load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( wjdi.first_unit_start_date is null
or wjdi.first_unit_completion_date is null
or wjdi.last_unit_start_date is null
or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
or wjdi.first_unit_start_date > wjdi.last_unit_start_date
or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
);
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 load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_add
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and parent_header_id = p_parent_header_id
and ( wjdi.first_unit_start_date is null
or wjdi.first_unit_completion_date is null
or wjdi.last_unit_start_date is null
or wjdi.last_unit_completion_date is null
/* bug3669728 begin */
or wjdi.first_unit_start_date > wjdi.last_unit_start_date
or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
/* bug3669728 end */
);
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_change
and wip_entity_id = p_wip_entity_id
and not exists (select 1
from wip_operations
where wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num);
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 not exists (select 1
from wip_operations
where wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num);
select interface_id
from wip_job_dtls_interface wjdi
where group_id = p_group_id
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running,
wip_constants.warning)
and load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_change
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( wjdi.last_unit_start_date is not null
or wjdi.first_unit_start_date is not null
or wjdi.first_unit_completion_date is not null
or wjdi.last_unit_completion_date is not null
)
and exists (select 1
from wip_operations wo
where (
/* bug#3669728 begin */
(nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
(nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
or
(nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
(nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
or
(nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
(nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
or
(nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
(nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
/* bug#3669728 end */
)
and wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num);
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 load_type = wip_job_details.wip_operation
and substitution_type = wip_job_details.wip_change
and wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id
and ( wjdi.last_unit_start_date is not null
or wjdi.first_unit_start_date is not null
or wjdi.first_unit_completion_date is not null
or wjdi.last_unit_completion_date is not null
)
and exists (select 1
from wip_operations wo
where (
/* bug#3669728 begin */
(nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
(nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
or
(nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
(nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
or
(nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
(nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
or
(nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
(nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
/* bug#3669728 end */
)
and wip_entity_id = wjdi.wip_entity_id
and organization_id = wjdi.organization_id
and operation_seq_num = wjdi.operation_seq_num);/*bug 3659006 */