[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select bcd.next_seq_num, bcd.seq_num, bcd.calendar_date, bcd.calendar_date + 1
into l_next_seq, x_seq, x_start_date, x_end_date
from bom_calendar_dates bcd
where bcd.calendar_code = l_cal_code and
bcd.exception_set_id = -1 and
bcd.calendar_date = trunc(p_date);
select bcd.seq_num, bcd.calendar_date, bcd.calendar_date + 1
into x_seq, x_start_date, x_end_date
from bom_calendar_dates bcd
where bcd.calendar_code = l_cal_code and
bcd.exception_set_id = -1 and
bcd.seq_num = l_next_seq;
select min(bsd.shift_date)
into l_cur_date
from bom_shift_dates bsd
where bsd.calendar_code = p_cal_code and
bsd.shift_date >= trunc( p_date )and
bsd.seq_num is not null;
select max(bsd.shift_date)
into l_prior_date
from bom_shift_dates bsd
where bsd.calendar_code = p_cal_code and
bsd.shift_date < l_cur_date and
bsd.seq_num is not null;
select min(bsd.shift_date)
into l_next_date
from bom_shift_dates bsd
where bsd.calendar_code = p_cal_code and
bsd.shift_date > l_cur_date and
bsd.seq_num is not null;
select
seq_num,
shift_num,
shift_date + from_time/(60*60*24),
shift_date + to_time/(60*60*24)
into
x_shift_seq,
x_shift_num,
x_shift_start_date,
x_shift_end_date
from
(
select bsd.shift_date,
bsd.shift_num,
bsd.seq_num,
st.from_time,
st.to_time
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 = p_cal_code
group by bst.shift_num
) st
where bsd.calendar_code = p_cal_code and
bsd.shift_num = st.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)
)
where rownum = 1;
select min(bsd.shift_date)
into l_cur_date
from bom_shift_dates bsd, bom_resource_shifts brs
where bsd.calendar_code = p_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 = p_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 = p_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,
shift_date + from_time/(60*60*24),
shift_date + to_time/(60*60*24)
into
x_shift_seq,
x_shift_num,
x_shift_start_date,
x_shift_end_date
from
(
select bsd.shift_date,
bsd.shift_num,
bsd.seq_num,
st.from_time,
st.to_time
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 = p_cal_code
group by bst.shift_num
) st ,
bom_resource_shifts brs
where bsd.calendar_code = p_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 sum( wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected)
into l_qty
from wip_operations wo1
where wo1.wip_entity_id = p_wip_entity_id and
( wo1.operation_seq_num =
( select wo2.previous_operation_seq_num
from wip_operations wo2
where wo2.wip_entity_id = p_wip_entity_id and
wo2.operation_seq_num = p_op_seq
)
or
wo1.operation_seq_num =
( select max( wo3.operation_seq_num )
from wip_operations wo3
where wo3.wip_entity_id = p_wip_entity_id and
wo3.operation_seq_num < p_op_seq and
wo3.count_point_type = 1
)
);
select count(distinct ool.sold_to_org_id)
from HZ_CUST_ACCOUNTS cust_accnt, mtl_reservations mr,
mtl_sales_orders mso, oe_order_lines_all ool
, wip_discrete_jobs wdj
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and ool.sold_to_org_id = cust_accnt.cust_account_id
and mr.supply_source_header_id = wdj.wip_entity_id
and mr.organization_id = wdj.organization_id
and wdj.organization_id = p_org_id
and wdj.wip_entity_id = p_wip_entity_id;
select cust_party.party_name
from HZ_CUST_ACCOUNTS cust_accnt, HZ_PARTIES cust_party,
mtl_reservations mr, mtl_sales_orders mso, oe_order_lines_all ool,
wip_discrete_jobs wdj
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and ool.sold_to_org_id = cust_accnt.cust_account_id
and cust_party.party_id = cust_accnt.party_id
and mr.supply_source_header_id = wdj.wip_entity_id
and mr.organization_id = wdj.organization_id
and wdj.organization_id = p_org_id
and wdj.wip_entity_id = p_wip_entity_id;
select count(distinct mso.segment1)
from mtl_reservations mr, mtl_sales_orders mso,
oe_order_lines_all ool, wip_discrete_jobs wdj
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and mr.supply_source_header_id = wdj.wip_entity_id
and mr.organization_id = wdj.organization_id
and wdj.organization_id = p_org_id
and wdj.wip_entity_id = p_wip_entity_id;
select mso.concatenated_segments
from mtl_reservations mr, mtl_sales_orders_kfv mso,
oe_order_lines_all ool, wip_discrete_jobs wdj
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id = 2
and mr.supply_source_type_id = 5
and mr.supply_source_header_id = wdj.wip_entity_id
and mr.organization_id = wdj.organization_id
and wdj.organization_id = p_org_id
and wdj.wip_entity_id = p_wip_entity_id;
select mp.calendar_code
into l_cal_code
from mtl_parameters mp
where mp.organization_id = p_org_id;
select bdr.available_24_hours_flag
into l_24hr_resource
from bom_department_resources bdr
where bdr.department_id = p_dept_id and
bdr.resource_id = p_resource_id;
select count(we.exception_id)
from wip_exceptions we
where we.wip_entity_id = p_wip_entity_id and
we.operation_seq_num = p_op_seq and
we.status_type = 1;
select ml.MEANING
from wip_exceptions we, mfg_lookups ml
where we.wip_entity_id = p_wip_entity_id and
we.operation_seq_num = p_op_seq and
we.status_type = 1 and
ml.LOOKUP_CODE = we.exception_type and
ml.LOOKUP_TYPE = 'WIP_EXCEPTION_TYPE';
select decode(wdj.project_id, null, null,
pjm_project.all_proj_idtonum(wdj.project_id))
into l_name
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
select decode(wdj.task_id, null, null,
pjm_project.all_task_idtonum(wdj.task_id))
into l_name
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
select count(distinct wor.setup_id)
from wip_operation_resources wor
where wor.wip_entity_id = p_wip_entity_id and
wor.operation_seq_num = p_op_seq;
select bst.setup_code
from wip_operation_resources wor, bom_setup_types bst
where wor.wip_entity_id = p_wip_entity_id and
wor.operation_seq_num = p_op_seq and
wor.setup_id = bst.setup_id;
select msi.primary_uom_code
into l_uom
from mtl_system_items_b msi
where msi.organization_id = p_org_id and
msi.inventory_item_id = p_comp_id;
select nvl(wro.basis_type, 1),
wro.required_quantity, wro.quantity_issued, wro.quantity_per_assembly,
decode(mp.include_component_yield, 1, nvl(wro.component_yield_factor, 1), 1)
from wip_requirement_operations wro, wip_parameters mp
where wro.organization_id = p_org_id and
wro.wip_entity_id = p_wip_entity_id and
mp.organization_id = wro.organization_id and
wro.operation_seq_num = p_op_seq and
wro.inventory_item_id = p_comp_id;
select wo.scheduled_quantity, wo.quantity_completed, nvl(wo.cumulative_scrap_quantity, 0)
into l_op_qty, l_qty_completed, l_cumulative_scrap_qty
from wip_operations wo
where wo.organization_id = p_org_id and
wo.wip_entity_id = p_wip_entity_id and
wo.operation_seq_num = p_op_seq;
select sum(wrat.duration)
into l_used_usage
from wip_resource_actual_times wrat
where wrat.wip_entity_id = p_wip_entity_id
and wrat.operation_seq_num = p_op_seq_num
and wrat.resource_seq_num = p_resource_seq_num
and wrat.duration is not null
and wrat.process_status <> 4
and (p_include_all = 'Y' or status_type = 1);
select
wor.resource_seq_num,
wor.basis_type,
wdj.start_quantity,
wo.cumulative_scrap_quantity,
wo.quantity_completed,
decode( wip_ws_time_entry.is_time_uom(wor.uom_code), 'Y',
inv_convert.inv_um_convert(-1,
38,
wor.usage_rate_or_amount,
wor.uom_code,
fnd_profile.value('BOM:HOUR_UOM_CODE'),
NULL,
NULL),
null) usage,
decode(mp.include_resource_efficiency, 1, nvl(bdr.efficiency, 1), 1) efficiency,
wor.actual_start_date,
wor.assigned_units
from wip_discrete_jobs wdj,
wip_operations wo,
wip_operation_resources wor,
bom_resources br,
bom_department_resources bdr,
wip_parameters mp
where wdj.wip_entity_id = wo.wip_entity_id and
wdj.organization_id = wo.organization_id and
mp.organization_id = wdj.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
br.organization_id = wor.organization_id and
br.resource_id = wor.resource_id and
bdr.resource_id = wor.resource_id and
bdr.department_id = nvl(wor.department_id, wo.department_id) and
wor.scheduled_flag in (1,3,4) and
wdj.status_type in (1,3,6) and
wor.wip_entity_id = p_wip_entity_id and
wor.operation_seq_num = p_op_seq and
nvl(bdr.share_from_dept_id, bdr.department_id ) = p_dept_id and
wor.resource_id = p_resource_id and
wor.resource_seq_num = nvl(p_resource_seq_num, wor.resource_seq_num);
select wo.quantity_in_queue + wo.quantity_running
into l_qty
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id and wo.operation_seq_num = p_op_seq_num;
select wdj.status_type
into l_status_type
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
select count(*)
into l_num_exceptions
from wip_exceptions we
where we.wip_entity_id = p_wip_entity_id and
we.operation_seq_num = p_op_seq_num and
we.status_type = 1;
select count(*), min(s.intraoperation_step_type), max(s.intraoperation_step_type)
into l_num_shop_status, l_nomove_step_min, l_nomove_step_max
from wip_shop_floor_statuses s, wip_shop_floor_status_codes c
where s.wip_entity_id = p_wip_entity_id and
s.operation_seq_num = p_op_seq_num and
s.shop_floor_status_code = c.shop_floor_status_code and
s.organization_id = c.organization_id and
c.status_move_flag = 2 /* no move */ and
nvl(c.disable_date, sysdate+1) > sysdate;
select wpv.attribute_name, wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = p_pref_id and
wpv.level_id = p_level_id
order by wpv.sequence_number;
select
v1.attribute_value_code,
v2.attribute_value_code,
v3.attribute_value_code,
v4.attribute_value_code
from wip_preference_values v1, wip_preference_values v2, wip_preference_values v3, wip_preference_values v4
where
v1.preference_id = p_pref_id and
v2.preference_id = p_pref_id and
v3.preference_id = p_pref_id and
v4.preference_id = p_pref_id and
v1.level_id = p_level_id and
v2.level_id = p_level_id and
v3.level_id = p_level_id and
v4.level_id = p_level_id and
v1.attribute_name = 'attribute' and
v2.attribute_name = 'column' and
v3.attribute_name = 'direction' and
v4.attribute_name = 'ignoreTime' and
v1.sequence_number = v2.sequence_number and
v2.sequence_number = v3.sequence_number and
v3.sequence_number = v4.sequence_number
order by v1.sequence_number;
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = p_pref_id and
wpv.level_id = p_level_id and
( p_attribute is null
or wpv.attribute_name = p_attribute);
' select 1 ' ||
' from wip_operation_resources wor1, ' ||
' wip_op_resource_instances wori1 ' ||
' where qrslt.wip_entity_id = wor1.wip_entity_id ' ||
' and qrslt.organization_id = wor1.organization_id ' ||
' and qrslt.operation_seq_num = wor1.operation_seq_num ' ||
' and wor1.wip_entity_id = wori1.wip_entity_id ' ||
' and wor1.operation_seq_num = wori1.operation_seq_num ' ||
' and wor1.resource_seq_num = wori1.resource_seq_num ' ||
' and wori1.instance_id = :' || l_num || ' ';
' select 1 ' ||
' from wip_operation_resources wor1, bom_department_resources bdr ' ||
' where wor1.wip_entity_id = qrslt.wip_entity_id ' ||
' and wor1.organization_id = qrslt.organization_id ' ||
' and wor1.operation_seq_num = qrslt.operation_seq_num ' ||
' and bdr.department_id = qrslt.department_id ' ||
' and nvl(wor1.department_id, nvl(bdr.share_from_dept_id, bdr.department_id)) = :' || l_num || ' ' ||
' and wor1.resource_id = :' || (l_num + 1) || ' ' ||
' ) '
);
' select 1 ' ||
' from wip_operation_resources wor1, ' ||
' wip_op_resource_instances wori1 ' ||
' where qrslt.wip_entity_id = wor1.wip_entity_id ' ||
' and qrslt.organization_id = wor1.organization_id ' ||
' and qrslt.operation_seq_num = wor1.operation_seq_num ' ||
' and wor1.wip_entity_id = wori1.wip_entity_id ' ||
' and wor1.operation_seq_num = wori1.operation_seq_num ' ||
' and wor1.resource_seq_num = wori1.resource_seq_num ' ||
' ) '
);
add_string(l_tmp, ' 0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
add_string(l_tmp, ' ( select max(wo2.operation_seq_num) from wip_operations wo2 ');
add_string(l_tmp, ' 0 < (select (wo1.quantity_in_queue + wo1.quantity_running + wo1.quantity_waiting_to_move + wo1.quantity_rejected) ');
select wdj.expedited
into l_expedited
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
update wip_discrete_jobs wdj
set wdj.expedited = 'Y'
where wdj.wip_entity_id = p_wip_entity_id;
select wdj.expedited
into l_expedited
from wip_discrete_jobs wdj
where wdj.wip_entity_id = p_wip_entity_id;
update wip_discrete_jobs wdj
set wdj.expedited = null /* set to N dosn't help on order */
where wdj.wip_entity_id = p_wip_entity_id;
l_move_table.delete;
select mp.calendar_code, mp.calendar_exception_set_id
into l_cal_code, l_cal_exception_id
from mtl_parameters mp
where mp.organization_id = p_org_id;
select shift_date
into l_shift_date
from bom_shift_dates bsd
where bsd.calendar_code = l_cal_code and
bsd.exception_set_id = l_cal_exception_id and
bsd.seq_num = p_shift_seq and
bsd.shift_num = p_shift_num;
select sum( 24* (to_date - from_date)) total_time
into l_total_time
from
( select GREATEST(l_date, l_shift_date + from_time/(24*60*60)) from_date,
l_shift_date + to_time/(24*60*60) + decode(sign(to_time - from_time), -1, 1, 0) to_date
from bom_shift_times bst
where bst.calendar_code = l_cal_code and
bst.shift_num = p_shift_num
) sd
where sd.from_date <= sd.to_date;
select brs.capacity_units
into l_units
from bom_resource_shifts brs
where brs.department_id = p_dept_id and
brs.resource_id = p_resource_id and
brs.shift_num = p_shift_num;
select bcd.calendar_date
into l_shift_date
from bom_calendar_dates bcd
where bcd.calendar_code = l_cal_code and
bcd.seq_num = p_shift_seq;
select nvl(l_units, bdr.capacity_units),
decode(wp.include_resource_utilization, wip_constants.yes, nvl(bdr.utilization, 1), 1)
into l_units, l_utilizaiton
from bom_department_resources bdr, wip_parameters wp
where bdr.department_id = p_dept_id and
bdr.resource_id = p_resource_id and
wp.organization_id = p_org_id;
l_sql := 'select count(*) ';
l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
select bsd.seq_num
into l_shift_seq
from bom_shift_dates bsd, mtl_parameters mp, bom_resource_shifts brs
where mp.organization_id = p_org_id and
mp.calendar_code = bsd.calendar_code and
brs.department_id = p_department_id and
brs.resource_id = p_resource_id and
brs.shift_num = bsd.shift_num and
bsd.shift_num = p_shift_num and
bsd.shift_date = trunc(p_from_date); -- Fix bug 9392379
l_sql := 'select sum( nvl(wip_ws_dl_util.get_col_res_usage_req(wip_entity_id, operation_seq_num, :1, :2, null), 0) ) ';
l_sql := l_sql || 'select wo.organization_id, wo.wip_entity_id, wo.operation_seq_num, wo.department_id, ';
select wo.organization_id, wo.department_id,
wo.quantity_in_queue + wo.quantity_running,
wo.quantity_waiting_to_move
into l_org_id, l_dept_id, l_qty_queue_run, l_qty_to_move
from wip_operations wo
where wo.wip_entity_id = p_wip_entity_id and
wo.operation_seq_num = p_op_seq;