[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select CEIL(NVL(total_time_calc,0)/p_takt_time) * p_takt_time
into l_total_time
from bom_operation_sequences
where operation_sequence_id = p_line_op_seq_id;
select start_time, stop_time
into l_start_time, l_stop_time
from wip_lines
where line_id = p_feeder_line_id
and organization_id = p_org_id;
select nvl(fixed_lead_time,0) + (p_qty - 1)*nvl(variable_lead_time,0)
into l_lead_time
from mtl_system_items
where organization_id = p_org_id
and inventory_item_id = p_item_id;
select bon.to_op_seq_id to_op_seq_id
from bom_operation_networks bon
connect by prior to_op_seq_id = from_op_seq_id
and
nvl(bon.transition_type, 0) not in (2, 3)
start with from_op_seq_id = cv_start_operation_sequence_id
and
nvl(bon.transition_type, 0) not in (2, 3);
select start_time, stop_time, 1/maximum_rate
into l_start_time, l_stop_time, l_takt_time
from wip_lines
where line_id = p_assembly_line_id
and organization_id = p_org_id;
select count(*)
into l_cnt
from bom_operation_networks
where from_op_seq_id = p_op_seq_id
and nvl(transition_type, 3) = 1;
select count(*)
into l_cnt
from bom_operation_networks
where to_op_seq_id = p_op_seq_id
and nvl(transition_type, 3) = 1;
select count(*)
into l_cnt
from bom_operation_sequences
where operation_type = 3
and routing_sequence_id = (
select max(routing_sequence_id)
from bom_operation_sequences
where operation_sequence_id = p_op_seq_id
);
select expl.component_item_id component_item_id,
comp.operation_seq_num operation_seq_num,
SUM(comp.component_quantity) extended_quantity,
MIN(DECODE(comp.wip_supply_type, NULL,
DECODE(sys.wip_supply_type, NULL,
1, sys.wip_supply_type),
comp.wip_supply_type)) wip_supply_type,
MIN(comp.component_quantity) component_quantity,
MIN(nvl(comp.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL)) basis_type
from mtl_system_items sys,
bom_inventory_components comp,
bom_explosions expl,
bom_bill_of_materials bbm
where sys.planning_make_buy_code = 1
and bbm.organization_id = sys.organization_id
and comp.component_item_id = sys.inventory_item_id
/* Fixed bug #2503750
Added condition to take into consideration bom effectivity dates. */
and comp.component_sequence_id = expl.component_sequence_id
/* Updated by Liye Ma. Mar.5th 2001
Fixed bug 1668713
Select only components whose type is standard */
and sys.bom_item_type = 4
/* End of Update */
and comp.component_item_id = expl.component_item_id
and comp.bill_sequence_id = expl.bill_sequence_id
and bbm.organization_id = arg_org_id
and bbm.assembly_item_id = var_assy_id
and (NVL(bbm.alternate_bom_designator, 'ABD756fhh466')
= NVL(arg_alt_bom_desig, 'ABD756fhh466')
or
(bbm.alternate_bom_designator is null and
not exists
(select null
from bom_bill_of_materials bbm1
where bbm1.alternate_bom_designator =
arg_alt_bom_desig
and bbm1.organization_id = bbm.organization_id
and bbm1.assembly_item_id = bbm.assembly_item_id)))
and bbm.common_bill_sequence_id = expl.bill_sequence_id
/* Added the following clause for the bug 1817962 */
/* and expl.top_bill_sequence_id =
(select bill_sequence_id from
bom_bill_of_materials
where assembly_item_id = arg_top_assy_id
and organization_id = arg_org_id
and nvl(alternate_bom_designator,'@@@') =
nvl(arg_alt_bom_desig,'@@@'))
*/
and expl.top_bill_sequence_id = l_top_bill_sequence_id
and expl.assembly_item_id is not null
and expl.effectivity_date <= p_start_date --fix bug#3170105
and NVL(expl.disable_date,p_start_date+1) > p_start_date
and expl.explosion_type = 'ALL'
group by expl.component_item_id,comp.operation_seq_num;
select --routing_sequence_id
common_routing_sequence_id --3701766 3891345.999
into var_routing_sequence_id
from bom_operational_routings
where organization_id = arg_org_id
and assembly_item_id = arg_top_assy_id
and NVL(alternate_routing_designator, 'ABD756fhh456') =
NVL(arg_alt_rtg_desig, 'ABD756fhh456');
SELECT inherit_phantom_op_seq INTO var_inherit_phantom
from bom_parameters
where organization_id = arg_org_id;
select bill_sequence_id
into l_bill_sequence_id
from bom_bill_of_materials
where organization_id = arg_org_id
and assembly_item_id = arg_top_assy_id
and nvl(alternate_bom_designator, '@@@@') =
nvl(arg_alt_bom_desig, '@@@@');
select max(top_bill_sequence_id)
into l_top_bill_sequence_id
from bom_explosions
where component_item_id = arg_top_assy_id
and organization_id = arg_org_id;
select line_id
into var_line_id
from bom_operational_routings flow_rtg
where flow_rtg.assembly_item_id = var_comp_id
and flow_rtg.organization_id = arg_org_id
and flow_rtg.cfm_routing_flag = 1
and flow_rtg.alternate_routing_designator is null;
(select min(routing_sequence_id)
from bom_operational_routings rtg1
where rtg1.assembly_item_id = var_comp_id
and rtg1.organization_id = arg_org_id
and rtg1.cfm_routing_flag = 1
and NVL(rtg1.priority, 1) =
(select NVL(min(priority), 1)
from bom_operational_routings rtg
where rtg.assembly_item_id = var_comp_id
and rtg.organization_id = arg_org_id));
select line_op_seq_id
into var_line_op_seq_id
from bom_operation_sequences
where routing_sequence_id = var_routing_sequence_id
and operation_seq_num = var_operation_seq_num
and operation_type = 1
and effectivity_date =
(select max(effectivity_date)
from bom_operation_sequences
where routing_sequence_id = var_routing_sequence_id
and operation_seq_num = var_operation_seq_num
and operation_type = 1);
select concatenated_segments
into var_comp_name
from mtl_system_items_kfv
where inventory_item_id = var_comp_id
and organization_id = arg_org_id;
select count(*)
into var_count
from wip_flow_schedules
where primary_item_id = var_comp_id
and NVL(synch_schedule_num,FND_API.G_MISS_CHAR) = arg_schedule_number
and NVL(synch_operation_seq_num,FND_API.G_MISS_NUM) = var_operation_seq_num;
SELECT flow.schedule_number,
flow.build_sequence build_sequence,
flow.primary_item_id primary_item_id,
flow.line_id line_id,
flow.planned_quantity planned_quantity,
flow.scheduled_start_date scheduled_start_date,
flow.scheduled_completion_date scheduled_completion_date,
flow.alternate_bom_designator alternate_bom_designator,
flow.alternate_routing_designator alternate_routing_designator,
nvl(flow.roll_forwarded_flag,2) roll_forwarded_flag
FROM wip_flow_schedules flow,
wip_lines lines
WHERE flow.planned_quantity - nvl(flow.quantity_completed, 0) > 0
AND flow.scheduled_start_date >= p_sysdate
AND flow.scheduled_start_date between p_start_date and p_end_date
AND flow.line_id = lines.line_id
AND flow.organization_id = lines.organization_id
AND lines.organization_id = arg_org_id
AND lines.line_code BETWEEN arg_min_line_code AND
arg_max_line_code;
var_comp_tbl.DELETE;
select count(*)
INTO var_rtg_exists
from bom_operational_routings rtg
where rtg.organization_id = arg_org_id
and rtg.assembly_item_id =
var_comp_tbl(var_current_row).item_id
and NVL(rtg.alternate_routing_designator, 'ABD756fhh456') =
NVL(var_alt_rtg, 'ABD756fhh456');
select count(*)
INTO var_bom_exists
from bom_bill_of_materials bom
where bom.organization_id = arg_org_id
and bom.assembly_item_id =
var_comp_tbl(var_current_row).item_id
and NVL(bom.alternate_bom_designator, 'ABD756fhh456') =
NVL(var_alt_bom, 'ABD756fhh456');
Select (fl.maximum_rate - ml.maximum_rate)
INTO l_fast_feeder_line
FROM wip_lines fl, wip_lines ml
Where ml.line_id = var_line_id
and fl.line_id = var_comp_tbl(var_current_row).line_id;
select count(*)
into var_count
from wip_flow_schedules flow
where
-- flow.primary_item_id =
-- var_comp_tbl(var_current_row).item_id
-- and
flow.line_id =
var_comp_tbl(var_current_row).line_id
and flow.organization_id = arg_org_id
and flow.build_sequence = var_build_seq_id
and scheduled_completion_date between --fix bug#3170105
l_flow_schedule_rec.scheduled_completion_date and
l_flow_schedule_rec.scheduled_completion_date+1-(1/(24*60*60));