[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select mp.calendar_code into l_calendar_code
from mtl_parameters mp
where mp.organization_id = p_organization_id;
select primary_uom_code
from mtl_system_items msi, wip_entities we
where msi.organization_id = we.organization_id
and we.primary_item_id = msi.inventory_item_id
and we.organization_id = p_org_id
and we.wip_entity_id = p_wip_entity_id
;
select project_id
from wip_discrete_jobs
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
;
select task_id
from wip_discrete_jobs
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
;
select sum(lead_time)
into l_lead_time
from
(select max((case when (inv_convert.inv_um_convert(0,wor.uom_code,l_hrUOM) = -99999)
then 0
else inv_convert.inv_um_convert(0,wor.uom_code,l_hrUOM)*wor.usage_rate_or_amount
end)*decode(wor.basis_type,WIP_CONSTANTS.PER_LOT,1,wo.scheduled_quantity)
/least(wor.assigned_units,bdr.capacity_units)/(nvl( bdr.utilization,1))/(nvl(bdr.efficiency,1))) lead_time
from wip_operation_resources wor,
wip_operations wo,
bom_department_resources bdr,
bom_resources br
where wo.wip_entity_id = p_wip_entity_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.operation_seq_num = p_op_seq_num
and wo.operation_seq_num = wor.operation_seq_num
and wo.department_id = bdr.department_id
and wor.resource_id = bdr.resource_id
and wor.resource_id = br.resource_id
and br.resource_type in (WIP_CONSTANTS.RES_MACHINE, WIP_CONSTANTS.RES_PERSON)
and wor.scheduled_flag <> WIP_CONSTANTS.SCHED_NO
group by to_char(nvl(to_char(wor.schedule_seq_num),rowidtochar(wor.rowid)))
);
select
bsd.shift_date + st.from_time/(60*60*24),
bsd.shift_date + st.to_time/(60*60*24),
trunc(bsd.shift_date),
bcs.description
into
x_shift_start,
x_shift_end,
x_shift_day,
l_shift_description
from
bom_shift_dates bsd,
(select
bst.shift_num,
min(bst.from_time) from_time,
max (bst.to_time + decode(sign(bst.to_time - bst.from_time), -1, (24*60*60), 0) ) to_time
from bom_shift_times bst
where bst.calendar_code = l_calendar_code
and bst.shift_num = l_shift_num
group by bst.shift_num
) st,
bom_calendar_shifts bcs
where bsd.calendar_code = l_calendar_code
and bsd.exception_set_id = -1
and bsd.seq_num = l_shift_seq_num
and bsd.shift_num = st.shift_num
and bsd.calendar_code = bcs.calendar_code
and bsd.shift_num = bcs.shift_num
;
select
bsd.seq_num || '.' || bsd.shift_num as shift_id,
to_char(
wip_ws_util.get_appended_date(bsd.shift_date, t.from_time),
'DD-MON-YYYY HH24:MI:SS'
) as from_date_char,
to_char(
wip_ws_util.get_appended_date( bsd.shift_date, t.to_time),
'DD-MON-YYYY HH24:MI:SS'
) as to_date_char,
wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) as from_date,
wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) as to_date,
t.shift_num as shift_num,
bsd.seq_num as seq_num,
wip_ws_util.get_shift_info_for_display(
p_organization_id, bsd.seq_num, t.shift_num
) as display
from
bom_shift_dates bsd,
(
select
bst.calendar_code,
bst.shift_num,
min(bst.from_time) from_time,
max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
from bom_shift_times bst
where bst.calendar_code = p_calendar_code
group by bst.calendar_code, bst.shift_num
) t
where bsd.calendar_code = p_calendar_code
and bsd.calendar_code = t.calendar_code
and bsd.shift_num = t.shift_num
and bsd.exception_set_id = -1
and bsd.shift_date between start_shift_day and end_shift_day
and bsd.seq_num is not null
order by from_date;