[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_current_op boolean; -- Bug 6345672
select wco.operation_seq_num,
wco.operation_sequence_id,
wco.reco_path_seq_num
into l_strRtgOpSeqNum,
l_strRtgOpSeqId,
l_strRecoSeqNum
from wsm_copy_operations wco
where wco.wip_entity_id = p_wipEntityID
and wco.network_start_end = 'S';
select wco.operation_seq_num,
wco.operation_sequence_id,
wco.reco_path_seq_num
into l_endRtgOpSeqNum,
l_endRtgOpSeqId,
l_endRecoSeqNum
from wsm_copy_operations wco
where wco.wip_entity_id = p_wipEntityID
and wco.network_start_end = 'E';
select count(operation_seq_num)
into l_count
from wip_operations
where wip_entity_id = p_wipEntityID;
select status_type
into l_job_status
from wip_discrete_jobs
where wip_entity_id = p_wipEntityID;
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_orgID;
select operation_seq_num,
operation_sequence_id
into l_curJobOpSeqNum,
l_curJobOpSeqid
from wip_operations
where wip_entity_id = p_wipEntityID
and operation_seq_num = -p_opSeqNum;
select operation_seq_num,
operation_sequence_id
into l_curJobOpSeqNum,
l_curJobOpSeqid
from wip_operations
where wip_entity_id = p_wipEntityID
and (quantity_in_queue <> 0 or
quantity_running <> 0 or
quantity_waiting_to_move <> 0);
g_update_current_op := true;
select '1'
into l_dummy
from wip_operations
where wip_entity_id = p_wipEntityID
and operation_seq_num = l_curJobOpSeqNum
and nvl(quantity_running, 0) = 0
and nvl(quantity_waiting_to_move, 0) = 0;
g_update_current_op := true;
g_update_current_op := false;
g_update_current_op := false;
if g_update_current_op then
wip_logger.log('schedule: g_update_current_op = true', l_retStatus);
wip_logger.log('schedule: g_update_current_op = false', l_retStatus);
p_delete_stack => fnd_api.g_false);
p_delete_stack => fnd_api.g_false);
select 1
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wip_requirement_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wip_operation_resource_usage
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wip_op_resource_instances
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wsm_copy_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wsm_copy_op_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wsm_copy_requirement_ops
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wsm_copy_op_resource_usage
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wsm_copy_op_resource_instances
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 0 lvl,
-c_cur_job_op_seq,
wor.resource_id,
NVL(wor.department_id, wo.department_id),
wor.resource_seq_num RES_SEQ_NUM,
wor.schedule_seq_num RES_SCH_NUM,
wor.scheduled_flag,
decode(wor.scheduled_flag, 3, 1, 1, 2, 4, 3, null)
SCHE_FLAG_ORDER,
bdr.available_24_hours_flag,
--Bug 4554494
--l_hrVal * nvl(muc.conversion_rate,0)
nvl(muc.conversion_rate,0)
* decode(wor.basis_type, wip_constants.per_lot, 1, wdj.start_quantity)
* wor.usage_rate_or_amount
/ ( 24 * nvl(l_hrVal,1)*least(wor.assigned_units, bdr.capacity_units)
* nvl(bdr.utilization, 1) * nvl(bdr.efficiency, 1) ),
wor.basis_type,
wor.assigned_units
from wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
mtl_uom_conversions muc,
bom_department_resources bdr
where wdj.wip_entity_id = p_wipEntityID
and wdj.organization_id = p_orgID
and wdj.wip_entity_id = wo.wip_entity_id
and wdj.organization_id = wo.organization_id
and 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.resource_seq_num
= decode(c_res_seq_num,
null, wor.resource_seq_num,
decode(c_sub_grp_num,
null, c_res_seq_num,
wor.resource_seq_num))
and nvl(wor.substitute_group_num, 0)
= decode(c_res_seq_num,
null, nvl(wor.substitute_group_num, 0),
decode(c_sub_grp_num,
null, nvl(wor.substitute_group_num, 0),
c_sub_grp_num))
and nvl(wor.replacement_group_num, 0)
= decode(c_res_seq_num,
null, nvl(wor.replacement_group_num, 0),
decode(c_sub_grp_num,
null, nvl(wor.replacement_group_num, 0),
c_rpl_grp_num))
and wo.operation_seq_num = c_cur_job_op_seq
and bdr.resource_id = wor.resource_id
and bdr.department_id = nvl(wor.department_id, wo.department_id)
and wor.uom_code = muc.uom_code (+)
and muc.uom_class (+)= l_uomClass
and muc.inventory_item_id (+)= 0
and c_cur_job_op_seq IS NOT NULL
union
-- future rtg ops --
select wco.reco_path_seq_num lvl,
wcor.operation_seq_num,
wcor.resource_id,
NVL(wcor.department_id, wco.department_id),
wcor.resource_seq_num RES_SEQ_NUM,
wcor.schedule_seq_num RES_SCH_NUM,
wcor.schedule_flag,
decode(wcor.schedule_flag, 3, 1, 1, 2, 4, 3, null)
SCHE_FLAG_ORDER,
bdr.available_24_hours_flag,
--Bug 4554494
--l_hrVal * nvl(muc.conversion_rate,0)
nvl(muc.conversion_rate,0)
* decode(wcor.basis_type, wip_constants.per_lot, 1, wdj.start_quantity)
* wcor.usage_rate_or_amount
/ ( 24 * nvl(l_hrVal,1)* least(wcor.assigned_units, bdr.capacity_units)
* nvl(bdr.utilization, 1) * nvl(bdr.efficiency, 1) ),
wcor.basis_type,
wcor.assigned_units
from wip_discrete_jobs wdj,
wsm_copy_operations wco,
wsm_copy_op_resources wcor,
mtl_uom_conversions muc,
bom_department_resources bdr
where wdj.wip_entity_id = p_wipEntityID
and wdj.organization_id = p_orgID
and wdj.wip_entity_id = wco.wip_entity_id
and wdj.organization_id = wco.organization_id
and wco.wip_entity_id = wcor.wip_entity_id
and wco.organization_id = wcor.organization_id
and wco.operation_seq_num = wcor.operation_seq_num
and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
and wcor.recommended = 'Y'
and wcor.resource_seq_num
= decode(c_res_seq_num,
null, wcor.resource_seq_num,
decode(c_sub_grp_num,
null, c_res_seq_num,
wcor.resource_seq_num))
and nvl(wcor.substitute_group_num, 0)
= decode(c_res_seq_num,
null, nvl(wcor.substitute_group_num, 0),
decode(c_sub_grp_num,
null, nvl(wcor.substitute_group_num, 0),
c_sub_grp_num))
and nvl(wcor.replacement_group_num, 0)
= decode(c_res_seq_num,
null, nvl(wcor.replacement_group_num, 0),
decode(c_sub_grp_num,
null, nvl(wcor.replacement_group_num, 0),
c_rpl_grp_num))
and bdr.resource_id = wcor.resource_id
and bdr.department_id = nvl(wcor.department_id, wco.department_id)
and wcor.uom_code = muc.uom_code (+)
and muc.uom_class (+)= l_uomClass
and muc.inventory_item_id (+)= 0
order by lvl, SCHE_FLAG_ORDER, RES_SEQ_NUM, RES_SCH_NUM;
select 0 lvl,
-c_cur_job_op_seq OP_SEQ_NUM
from dual
where c_cur_job_op_seq IS NOT NULL
union
-- other rtg ops --
select wco.reco_path_seq_num lvl,
operation_seq_num OP_SEQ_NUM
from wsm_copy_operations wco
where wco.wip_entity_id = p_wipEntityID
and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
order by lvl;
select operation_seq_num,
NVL(yield, 1.0)
from wsm_copy_operations wco
where wco.wip_entity_id = p_wipEntityID
and (wco.reco_path_seq_num between c_fst_rec_seq_seq and c_lst_rec_seq_seq)
order by wco.reco_path_seq_num;
select conversion_rate, uom_class
into l_hrVal, l_uomClass
from mtl_uom_conversions
where uom_code = l_hrUOM
and nvl(disable_date, sysdate + 1) > sysdate
and inventory_item_id = 0;
select wco.reco_path_seq_num
into l_fst_rec_seq_num
from WSM_COPY_OPERATIONS wco
where wco.wip_entity_id = p_wipEntityID
and wco.operation_sequence_id = p_curJobOpSeqId;
select wco.reco_path_seq_num
into l_the_rec_seq_num
from WSM_COPY_OPERATIONS wco
where wco.wip_entity_id = p_wipEntityID
and wco.operation_seq_num = p_opSeqNum;
select wdj.start_quantity,
wdj.quantity_scrapped
into l_job_start_qty,
l_job_scrap_qty
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wipEntityID;
g_opQty.delete;
select wo.quantity_in_queue + wo.quantity_running
+ wo.quantity_waiting_to_move,
nvl(wo.operation_yield, 1),
decode(wo.quantity_waiting_to_move,
0, 1, 2)
into l_job_quantity,
l_cur_op_yield,
l_qty_posi
from wip_operations wo
where wo.wip_entity_id = p_wipEntityID
and wo.operation_seq_num = l_cur_job_op_seq;
select substitute_group_num,
replacement_group_num
into l_sub_grp_num,
l_rpl_grp_num
from WSM_COPY_OP_RESOURCES
where wip_entity_id = p_wipEntityID
and operation_seq_num = p_opSeqNum
and resource_seq_num = p_resSeqNum;
select substitute_group_num,
replacement_group_num
into l_sub_grp_num,
l_rpl_grp_num
from WIP_OPERATION_RESOURCES
where wip_entity_id = p_wipEntityID
and operation_seq_num = -p_opSeqNum
and resource_seq_num = p_resSeqNum;
select 1
into l_dummy
from wip_discrete_jobs
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select wdj.scheduled_start_date,
wdj.scheduled_completion_date
into l_jobStartDate,
l_jobComplDate
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wipEntityID;
select first_unit_start_date,
last_unit_completion_date
into l_OpStartDate(1),
l_OpEndDate(1)
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -l_curOp;
select reco_start_date,
reco_completion_date
into l_OpStartDate(1),
l_OpEndDate(1)
from wsm_copy_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = l_curOp;
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_orgID;
update wip_discrete_jobs
set --scheduled_start_date = NVL(l_jobStartDate, scheduled_start_date),
--scheduled_completion_date = NVL(l_jobComplDate, scheduled_completion_date),
scheduled_start_date = l_newJobStartDate,
scheduled_completion_date = l_newJobComplDate,
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
update wip_operations
set first_unit_start_date = NVL(l_OpStartDate(i), first_unit_start_date),
last_unit_start_date = NVL(l_OpStartDate(i), last_unit_start_date),
first_unit_completion_date = NVL(l_OpEndDate(i), first_unit_completion_date),
last_unit_completion_date = NVL(l_OpEndDate(i), last_unit_completion_date),
scheduled_quantity = l_OpQty(i),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -p_opTbl(i);
update wsm_copy_operations
set reco_start_date = l_OpStartDate(i),
reco_completion_date = l_OpEndDate(i),
reco_scheduled_quantity = l_OpQty(i),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_opTbl(i);
if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 THEN --bug 6345672
update wsm_copy_operations
set reco_start_date = l_OpStartDate(l_cnt_wo),
reco_completion_date = l_OpEndDate(l_cnt_wo),
reco_scheduled_quantity = l_OpQty(l_cnt_wo),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -p_opTbl(l_cnt_wo);
update wip_requirement_operations
set date_required = NVL(l_OpStartDate(i), date_required),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -p_opTbl(i);
update wsm_copy_requirement_ops
set reco_date_required = l_OpStartDate(i),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_opTbl(i);
if g_update_current_op and l_cnt_wo is not NULL and l_cnt_wo > 0 then --bug 6345672
update wsm_copy_requirement_ops
set reco_date_required = l_OpStartDate(l_cnt_wo),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -p_opTbl(l_cnt_wo);
update wip_operation_resources
set start_date = NVL(x_resTbls.startDate(i), start_date),
completion_date = NVL(x_resTbls.endDate(i), completion_date),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
update wsm_copy_op_resources
set reco_start_date = x_resTbls.startDate(i),
reco_completion_date = x_resTbls.endDate(i),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
if g_update_current_op AND l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/ -- bug 7248484
update wsm_copy_op_resources
set reco_start_date = x_resTbls.startDate(l_cnt_wor),
reco_completion_date = x_resTbls.endDate(l_cnt_wor),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID,
request_id = l_reqID,
program_application_id = l_progApplID,
program_id = l_progID,
program_update_date = l_sysDate
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
update wip_op_resource_instances
set start_date = NVL(x_resTbls.startDate(i), start_date),
completion_date = NVL(x_resTbls.endDate(i), completion_date),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
update wsm_copy_op_resource_instances
set start_date = x_resTbls.startDate(i),
completion_date = x_resTbls.endDate(i),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
update wsm_copy_op_resource_instances
set start_date = x_resTbls.startDate(l_cnt_wor),
completion_date = x_resTbls.endDate(l_cnt_wor),
----standard who columns----
last_update_date = l_sysdate,
last_updated_by = l_userID,
last_update_login = l_loginID
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
delete wip_operation_resource_usage
where wip_entity_id = p_wipEntityID
and operation_seq_num = -x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
insert into wip_operation_resource_usage (
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wipEntityID,
-x_resTbls.opSeqNum(i),
x_resTbls.resSeqNum(i),
p_orgID,
x_resTbls.usgStartDate(j),
x_resTbls.usgEndDate(j),
p_assignedUnits(i),
x_resTbls.usgCumMinProcTime(j),
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate);
insert into wip_operation_resource_usage(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
instance_id,
serial_number,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select p_wipEntityID,
woru.operation_seq_num,
woru.resource_seq_num,
p_orgID,
woru.start_date,
woru.completion_date,
1,
wori.instance_id,
wori.serial_number,
woru.cumulative_processing_time,
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate
from wip_operation_resource_usage woru,
wip_op_resource_instances wori
where woru.wip_entity_id = wori.wip_entity_id
and woru.operation_seq_num = wori.operation_seq_num
and woru.resource_seq_num = wori.resource_seq_num
and woru.organization_id = wori.organization_id
and woru.wip_entity_id = p_wipEntityID
and woru.organization_id = p_orgID
and wori.operation_seq_num = -x_resTbls.opSeqNum(i)
and wori.resource_seq_num = x_resTbls.resSeqNum(i);
delete wsm_copy_op_resource_usage
where wip_entity_id = p_wipEntityID
and operation_seq_num = x_resTbls.opSeqNum(i)
and resource_seq_num = x_resTbls.resSeqNum(i);
if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
delete wsm_copy_op_resource_usage
where wip_entity_id = p_wipEntityID
and operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
and resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);
insert into wsm_copy_op_resource_usage(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wipEntityID,
x_resTbls.opSeqNum(i),
x_resTbls.resSeqNum(i),
p_orgID,
x_resTbls.usgStartDate(j),
x_resTbls.usgEndDate(j),
p_assignedUnits(i),
x_resTbls.usgCumMinProcTime(j),
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate);
if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672/ -- bug 7248484
if(x_resTbls.usgStartIdx(l_cnt_wor) is not null) then
forall j in x_resTbls.usgStartIdx(l_cnt_wor)..x_resTbls.usgEndIdx(l_cnt_wor)
insert into wsm_copy_op_resource_usage(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) values (
p_wipEntityID,
-x_resTbls.opSeqNum(l_cnt_wor),
x_resTbls.resSeqNum(l_cnt_wor),
p_orgID,
x_resTbls.usgStartDate(j),
x_resTbls.usgEndDate(j),
p_assignedUnits(l_cnt_wor),
x_resTbls.usgCumMinProcTime(j),
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate);
insert into wsm_copy_op_resource_usage(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
instance_id,
serial_number,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select p_wipEntityID,
wcoru.operation_seq_num,
wcoru.resource_seq_num,
p_orgID,
wcoru.start_date,
wcoru.completion_date,
1,
wcori.instance_id,
wcori.serial_number,
wcoru.cumulative_processing_time,
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate
from wsm_copy_op_resource_usage wcoru,
wsm_copy_op_resource_instances wcori
where wcoru.wip_entity_id = wcori.wip_entity_id
and wcoru.operation_seq_num = wcori.operation_seq_num
and wcoru.resource_seq_num = wcori.resource_seq_num
and wcoru.organization_id = wcori.organization_id
and wcoru.wip_entity_id = p_wipEntityID
and wcoru.organization_id = p_orgID
and wcori.operation_seq_num = x_resTbls.opSeqNum(i)
and wcori.resource_seq_num = x_resTbls.resSeqNum(i);
if g_update_current_op and l_cnt_wor > 0 and l_cnt_wor is not NULL then --bug 6345672 -- bug 7248484
insert into wsm_copy_op_resource_usage(
wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
start_date,
completion_date,
assigned_units,
instance_id,
serial_number,
cumulative_processing_time,
----standard who columns----
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
select p_wipEntityID,
wcoru.operation_seq_num,
wcoru.resource_seq_num,
p_orgID,
wcoru.start_date,
wcoru.completion_date,
1,
wcori.instance_id,
wcori.serial_number,
wcoru.cumulative_processing_time,
l_sysdate,
l_userID,
l_sysdate,
l_userID,
l_loginID,
l_reqID,
l_progApplID,
l_progID,
l_sysdate
from wsm_copy_op_resource_usage wcoru,
wsm_copy_op_resource_instances wcori
where wcoru.wip_entity_id = wcori.wip_entity_id
and wcoru.operation_seq_num = wcori.operation_seq_num
and wcoru.resource_seq_num = wcori.resource_seq_num
and wcoru.organization_id = wcori.organization_id
and wcoru.wip_entity_id = p_wipEntityID
and wcoru.organization_id = p_orgID
and wcori.operation_seq_num = -x_resTbls.opSeqNum(l_cnt_wor)
and wcori.resource_seq_num = x_resTbls.resSeqNum(l_cnt_wor);