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 calendar_code
into l_cal_code
from mtl_parameters
where organization_id = p_org_id;
select min(bsd.shift_date)
into l_cur_date
from bom_shift_dates bsd, bom_resource_shifts brs
where bsd.calendar_code = l_cal_code and
bsd.shift_date >= trunc( p_date )and
brs.department_id = p_dept_id and
brs.resource_id = nvl( p_resource_id, brs.resource_id) and
brs.shift_num = bsd.shift_num and
bsd.exception_set_id = -1 and
bsd.seq_num is not null;
select max(bsd.shift_date)
into l_prior_date
from bom_shift_dates bsd, bom_resource_shifts brs
where bsd.calendar_code = l_cal_code and
bsd.shift_date < l_cur_date and
brs.department_id = p_dept_id and
brs.resource_id = nvl( p_resource_id, brs.resource_id) and
brs.shift_num = bsd.shift_num and
bsd.exception_set_id = -1 and
bsd.seq_num is not null;
select min(bsd.shift_date)
into l_next_date
from bom_shift_dates bsd, bom_resource_shifts brs
where bsd.calendar_code = l_cal_code and
bsd.shift_date > l_cur_date and
brs.department_id = p_dept_id and
brs.resource_id = nvl( p_resource_id, brs.resource_id) and
brs.shift_num = bsd.shift_num and
bsd.exception_set_id = -1 and
bsd.seq_num is not null;
select
seq_num,
shift_num
into
x_shift_seq,
x_shift_num
from
(
select bsd.shift_num,
bsd.seq_num
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_cal_code
group by bst.shift_num
) st ,
bom_resource_shifts brs
where bsd.calendar_code = l_cal_code and
bsd.shift_num = st.shift_num and
brs.department_id = p_dept_id and
brs.resource_id = nvl( p_resource_id, brs.resource_id) and
brs.shift_num = bsd.shift_num and
(bsd.shift_date + st.to_time / (60 * 60 * 24)) >= p_date and
bsd.shift_date in (l_cur_date, l_prior_date, l_next_date)
order by bsd.shift_date + st.from_time / (60 * 60 * 24)
) t
where rownum = 1;
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;