[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select line_id
into l_repLineID
from wip_repetitive_schedules
where repetitive_schedule_id = p_repSchedID;
p_delete_stack => fnd_api.g_false);
p_delete_stack => fnd_api.g_false);
select min(operation_seq_num),
max(operation_seq_num)
into x_minOpSeqNum,
x_maxOpSeqNum
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
select nvl(schedule_seq_num, resource_seq_num)
into x_minSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_midPntOpSeqNum
and resource_seq_num = p_midPntResSeqNum;
select max(operation_seq_num)
into x_maxOpSeqNum
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
select max(nvl(schedule_seq_num, resource_seq_num))
into x_maxSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = x_maxOpSeqNum;
select nvl(schedule_seq_num, resource_seq_num)
into x_maxSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_midPntOpSeqNum
and resource_seq_num = p_midPntResSeqNum;
select min(operation_seq_num)
into x_minOpSeqNum
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
select min(nvl(schedule_seq_num, resource_seq_num))
into x_minSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = x_minOpSeqNum;
select min(nvl(schedule_seq_num, resource_seq_num)), max(nvl(schedule_seq_num, resource_seq_num))
into x_minSchedSeqNum, x_maxSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_midPntOpSeqNum;
select min(nvl(schedule_seq_num, resource_seq_num)), max(nvl(schedule_seq_num, resource_seq_num))
into x_minSchedSeqNum, x_maxSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_midPntOpSeqNum
and substitute_group_num = p_subGrpNum;
select nvl(schedule_seq_num, resource_seq_num)
into x_minSchedSeqNum
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num = p_midPntOpSeqNum
and resource_seq_num = p_midPntResSeqNum;
select 1
from wip_discrete_jobs
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
for update nowait;
select 1
from wip_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num between p_minOpSeqNum and p_maxOpSeqNum
for update nowait;
select 1
from wip_requirement_operations
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID
and operation_seq_num between p_minOpSeqNum and p_maxOpSeqNum
for update nowait;
select 1
from wip_operation_resource_usage woru,
wip_operation_resources wor
where wor.wip_entity_id = p_wipEntityID
and wor.organization_id = p_orgID
and ( wor.operation_seq_num < p_maxOpSeqNum
and wor.operation_seq_num > p_minOpSeqNum
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_minOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum = p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
)
and woru.organization_id = wor.organization_id
and woru.wip_entity_id = wor.wip_entity_id
and woru.operation_seq_num = wor.operation_seq_num
and woru.resource_seq_num = wor.resource_seq_num
for update nowait;
select 1
from wip_op_resource_instances wori,
wip_operation_resources wor
where wor.wip_entity_id = p_wipEntityID
and wor.organization_id = p_orgID
and ( wor.operation_seq_num < p_maxOpSeqNum
and wor.operation_seq_num > p_minOpSeqNum
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_minOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum = p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
)
and wori.organization_id = wor.organization_id
and wori.wip_entity_id = wor.wip_entity_id
and wori.operation_seq_num = wor.operation_seq_num
and wori.resource_seq_num = wor.resource_seq_num
for update nowait;
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 wor.operation_seq_num,
wor.resource_id,
nvl(bdr.share_from_dept_id, bdr.department_id),
wor.resource_seq_num,
wor.schedule_seq_num,
wor.scheduled_flag,
bdr.available_24_hours_flag,
--l_hrVal * nvl(muc.conversion_rate,0) *
--Bug 4614036:Rounding of usage rate to next minute is handled.
/*
round((decode(wor.basis_type, wip_constants.per_lot, 1, decode(p_quantity,null,wdj.start_quantity,p_quantity)) *
inv_convert.inv_um_convert(0,
NULL,
wor.usage_rate_or_amount,
wor.UOM_CODE,
l_hrUOM,
NULL,
NULL )),0)
-- NULL )*60)+0.5,0) --Fixed bug #5618787
/ (
-- 60*24 * least(wor.assigned_units, bdr.capacity_units) * --Fixed bug #5618787
24 * least(wor.assigned_units, bdr.capacity_units) *
decode(wp.include_resource_utilization,
wip_constants.yes, nvl(bdr.utilization, 1), 1) *
decode(wp.include_resource_efficiency,
wip_constants.yes, nvl(bdr.efficiency, 1), 1)
),
*/
-- Start of fix for Bug #5657612: Use ceil function to round up the usage rate to next minute
-- bug 6741020: pass a precision = 6 to inv_um_convert as resource usage form field
-- supports 6 decimals. INV assumes a default of 5 decimals if null is passed. This causes
-- errors in calculation.
ceil((decode(wor.basis_type, wip_constants.per_lot, 1, decode(p_quantity,null,wdj.start_quantity,p_quantity)) *
inv_convert.inv_um_convert(0,
6,
wor.usage_rate_or_amount,
wor.UOM_CODE,
l_hrUOM,
NULL,
NULL )*60) /
( least(wor.assigned_units, bdr.capacity_units) *
decode(wp.include_resource_utilization,
wip_constants.yes, nvl(bdr.utilization, 1), 1) *
decode(wp.include_resource_efficiency,
wip_constants.yes, nvl(bdr.efficiency, 1), 1) )
) / (60 * 24),
-- End of fix for Bug #5657612
wor.assigned_units
bulk collect into x_resTbls.opSeqNum,
x_resTbls.resID,
x_resTbls.deptID,
x_resTbls.resSeqNum,
x_resTbls.schedSeqNum,
x_resTbls.schedFlag,
x_resTbls.avail24Flag,
x_resTbls.totalDaysUsg,
x_assignedUnits
from wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
mtl_uom_conversions muc,
bom_department_resources bdr,
wip_parameters wp
where wp.organization_id = wdj.organization_id
and 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 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 (
( wor.operation_seq_num < p_maxOpSeqNum
and wor.operation_seq_num > p_minOpSeqNum
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_minOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum = p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
)
order by wor.operation_seq_num, nvl(wor.schedule_seq_num, wor.resource_seq_num)
for update of wor.start_date nowait;
update wip_operation_resources
set start_date = x_resTbls.startDate(i),
completion_date = x_resTbls.endDate(i),
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 wip_op_resource_instances
set start_date = x_resTbls.startDate(i),
completion_date = x_resTbls.endDate(i),
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);
delete wip_operation_resource_usage
where (organization_id, wip_entity_id, operation_seq_num, resource_seq_num) in
(select organization_id,
wip_entity_id,
operation_seq_num,
resource_seq_num
from wip_operation_resources wor
where wor.wip_entity_id = p_wipEntityID
and wor.organization_id = p_orgID
and ( wor.operation_seq_num < p_maxOpSeqNum
and wor.operation_seq_num > p_minOpSeqNum
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_minOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum = p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
)
);
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,
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,
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,
wip_operation_resources wor
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 wor.wip_entity_id = p_wipEntityID
and wor.organization_id = p_orgID
and ( wor.operation_seq_num < p_maxOpSeqNum
and wor.operation_seq_num > p_minOpSeqNum
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_minOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) >= nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum <> p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) <= nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
or ( p_minOpSeqNum = p_maxOpSeqNum
and wor.operation_seq_num = p_maxOpSeqNum
and nvl(wor.schedule_seq_num, wor.resource_seq_num) between nvl(p_minSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num)) and nvl(p_maxSchedSeqNum, nvl(wor.schedule_seq_num, wor.resource_seq_num))
)
)
and woru.organization_id = wor.organization_id
and woru.wip_entity_id = wor.wip_entity_id
and woru.operation_seq_num = wor.operation_seq_num
and woru.resource_seq_num = wor.resource_seq_num;
select wo.operation_seq_num,
min(wor.start_date),
max(wor.completion_date)
bulk collect into
l_opTbls.opSeqNum,
l_opTbls.startDate,
l_opTbls.endDate
from wip_operations wo,
wip_operation_resources wor
where wo.wip_entity_id = p_wipEntityID
and wo.organization_id = p_orgID
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 (+)
group by wo.operation_seq_num;
select wo.operation_seq_num,
min(wor.start_date),
max(wor.completion_date)
bulk collect into
l_opSchYesTbls.opSeqNum,
l_opSchYesTbls.startDate,
l_opSchYesTbls.endDate
from wip_operations wo,
wip_operation_resources wor
where wo.wip_entity_id = p_wipEntityID
and wo.organization_id = p_orgID
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 wip_constants.sched_yes = wor.scheduled_flag (+)
group by wo.operation_seq_num;
update wip_operations
set first_unit_start_date = l_opTbls.startDate(i),
last_unit_start_date = l_opTbls.startDate(i),
first_unit_completion_date = l_opTbls.endDate(i),
last_unit_completion_date = l_opTbls.endDate(i),
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 = l_opTbls.opSeqNum(i);
update wip_requirement_operations
set date_required = l_opTbls.startDate(i),
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 = l_opTbls.opSeqNum(i);
update wip_requirement_operations
set date_required = p_anchorDate,
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 = 1;
select min(start_date), max(completion_date)
into l_minResStartDate, l_maxResEndDate
from wip_operation_resources
where wip_entity_id = p_wipEntityID
and organization_id = p_orgID;
update wip_discrete_jobs
set scheduled_start_date = l_jobStartDate,
scheduled_completion_date = l_jobCplDate,
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;