[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from wip_job_schedule_interface
where group_id = p_groupID
and process_status = wip_constants.pending
and process_phase = wip_constants.ml_validation
and load_type <> wip_constants.create_sched
for update nowait;
select rowid,
header_id,
interface_id,
job_name, --8494582
wip_entity_id, --8494582
organization_id, --8494582
organization_code, --8494582
load_type, --8494582
status_type, --8296679
class_code, --8296679
date_released --8296679
from wip_job_schedule_interface
where group_id = p_groupID
and process_phase = wip_constants.ml_validation
and process_status in (wip_constants.running, wip_constants.warning)
and load_type <> wip_constants.create_sched;
select wdj.wip_entity_id,
wdj.organization_id,
wdj.status_type,
wdj.class_code,
wdj.date_released
from wip_discrete_jobs wdj, wip_job_schedule_interface wjsi
where wjsi.group_id = p_groupID
and process_status in (wip_constants.running, wip_constants.warning)
and wjsi.wip_entity_id = wdj.wip_entity_id
and wdj.status_type in (wip_constants.released, wip_constants.hold);
select count(*) into l_requestCount
from wip_job_schedule_interface
where group_id = p_groupID
and process_status = wip_constants.pending
and process_phase = wip_constants.ml_validation
and load_type <> wip_constants.create_sched;
update wip_job_schedule_interface
set interface_id = wip_interface_s.nextval,
process_status = wip_constants.running
where group_id = p_groupID
and process_status = wip_constants.pending
and process_phase = wip_constants.ml_validation
and load_type <> wip_constants.create_sched;
select wdj.status_type
into l_wdj_old_status_type
from wip_discrete_jobs wdj
where wdj.organization_id=wjsi_rec.organization_id
and wdj.wip_entity_id=wjsi_rec.wip_entity_id;
select count(*)
into l_totalNum
from wip_job_schedule_interface wjsi,
wip_job_dtls_interface wjdi
where wjdi.parent_header_id = wjsi.header_id
and wjdi.group_id = wjsi.group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status = wip_constants.pending
and wjsi.rowid = wjsi_rec.rowid
and wjsi.load_type in (wip_constants.create_job,
wip_constants.create_ns_job,
wip_constants.resched_job);
select wjsi.status_type,
wdj.wip_entity_id,
wdj.organization_id
into l_wjsi_new_status_type, /*Removed old status type for bug 8494582*/
l_wdj_wip_entity_id,
l_wdj_organization_id
from wip_discrete_jobs wdj,
wip_job_schedule_interface wjsi
where wjsi.rowid = wjsi_rec.rowid
and wdj.wip_entity_id = wjsi.wip_entity_id
and wdj.organization_id = wjsi.organization_id
and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
WIP_CONSTANTS.CREATE_NS_JOB,
WIP_CONSTANTS.CREATE_EAM_JOB,
WIP_CONSTANTS.RESCHED_EAM_JOB,
WIP_CONSTANTS.RESCHED_JOB) ;
INSERT INTO WIP_PERIOD_BALANCES
(ACCT_PERIOD_ID, WIP_ENTITY_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
ORGANIZATION_ID, CLASS_TYPE,
TL_RESOURCE_IN, TL_OVERHEAD_IN,
TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
TL_MATERIAL_OUT, TL_RESOURCE_OUT,
TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
SELECT OAP.ACCT_PERIOD_ID, wdj_rec.wip_entity_id,
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id,fnd_global.login_id ,
wdj_rec.organization_id, WC.CLASS_TYPE,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0
FROM ORG_ACCT_PERIODS OAP,
WIP_ACCOUNTING_CLASSES WC
WHERE WC.CLASS_CODE = wdj_rec.class_code
AND WC.ORGANIZATION_ID = wdj_rec.organization_id
AND OAP.ORGANIZATION_ID = wdj_rec.organization_id
AND OAP.SCHEDULE_CLOSE_DATE >= TRUNC(wdj_rec.date_released)
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND NOT EXISTS (SELECT 'balance record already there'
FROM WIP_PERIOD_BALANCES WPB
WHERE WPB.WIP_ENTITY_ID = wdj_rec.wip_entity_id
AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
AND WPB.ORGANIZATION_ID = OAP.ORGANIZATION_ID);
update wip_job_schedule_interface
set process_status = wip_constants.completed,
process_phase = wip_constants.ml_complete
where group_id = p_groupID
and process_status in (wip_constants.running, wip_constants.warning)
and process_phase = wip_constants.ml_validation
and load_type <> wip_constants.create_sched;
select count(*) into l_errRecCount
from wip_job_schedule_interface
where group_id = p_groupID
and process_status = wip_constants.error;
select 1
from wip_job_schedule_interface
where interface_id = p_interfaceID
for update nowait;
select rowid, group_id, header_id
into l_rowid, l_groupID, l_headerID
from wip_job_schedule_interface
where interface_id = p_interfaceID;
select count(*)
into l_totalNum
from wip_job_schedule_interface
where interface_id = p_interfaceID
and process_status = wip_constants.pending
and process_phase = wip_constants.ml_validation
and load_type in (wip_constants.create_job, wip_constants.create_ns_job);
select count(*)
into l_totalNum
from wip_job_schedule_interface wjsi
where wjsi.group_id = l_groupID;
update wip_job_schedule_interface
set process_status = wip_constants.running
where interface_id = p_interfaceID;
select count(*)
into l_totalNum
from wip_job_schedule_interface
where interface_id = p_interfaceID
and process_status in (wip_constants.running, wip_constants.warning);
select count(*)
into l_totalNum
from wip_job_schedule_interface wjsi,
wip_job_dtls_interface wjdi
where wjdi.parent_header_id = wjsi.header_id
and wjdi.group_id = wjsi.group_id
and wjdi.process_phase = wip_constants.ml_validation
and wjdi.process_status = wip_constants.pending
and wjsi.rowid = l_rowid;
select wip_entity_id,
load_type, status_type, organization_id, class_code, date_released --8936011
into x_wipEntityID,
l_load_type, l_status_type, l_organization_id, l_class_code, l_date_released --8936011
from wip_job_schedule_interface
where rowid = l_rowid;
update wip_job_schedule_interface
set process_status = wip_constants.completed
where rowid = l_rowid;
select operation_seq_num
from wip_operations
where wip_entity_id = v_wipEntityID;
select wip_entity_id,
serialization_start_op,
load_type,
primary_item_id,
organization_id
into l_wipEntityID,
l_wjsiSerOp,
l_loadType,
l_primaryItemID,
l_orgID
from wip_job_schedule_interface
where rowid = p_rowid;
update wip_discrete_jobs wdj
set serialization_start_op = null
where wip_entity_id = l_wipEntityID
and serialization_start_op <> 1
and not exists(select 1
from wip_operations wo
where wo.wip_entity_id = wdj.wip_entity_id
and wo.operation_seq_num = wdj.serialization_start_op);
select serialization_start_op
into l_wdjSerOp
from wip_discrete_jobs
where wip_entity_id = l_wipEntityID;
SELECT 1
into l_dummy
from wip_operations wo
WHERE wo.wip_entity_id = l_wipEntityID and wo.operation_seq_num = NVL(l_wjsiSerOp,-99) AND rownum=1;
update wip_discrete_jobs wdj
set serialization_start_op = l_wjsiSerOp
where wip_entity_id = l_wipEntityID
and exists(select 1
from wip_operations wo
where wo.wip_entity_id = wdj.wip_entity_id
and wo.operation_seq_num = l_wjsiSerOp) returning serialization_start_op into l_wdjSerOp;
select 1
into l_dummy
from wip_discrete_jobs wdj,
mtl_system_items msi
where wdj.primary_item_id = msi.inventory_item_id
and wdj.organization_id = msi.organization_id
and wdj.wip_entity_id = l_wipEntityID
and msi.serial_number_control_code = wip_constants.full_sn;
select 1
into l_dummy
from wip_discrete_jobs
where wip_entity_id = l_wipEntityID
and status_type = wip_constants.unreleased;