[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT VIEW_ALL_SCHEDULES
FROM FLM_EXE_PREFERENCES
WHERE ORGANIZATION_ID = l_org_id
AND LINE_ID = l_line_id
AND STANDARD_OPERATION_ID = l_operation_id;
SELECT VIEW_ALL_SCHEDULES
FROM FLM_EXE_PREFERENCES
WHERE ORGANIZATION_ID = l_org_id
AND LINE_ID = l_line_id
AND STANDARD_OPERATION_ID IS NULL;
SELECT VIEW_ALL_SCHEDULES
FROM FLM_EXE_PREFERENCES
WHERE ORGANIZATION_ID = l_org_id
AND LINE_ID IS NULL
AND STANDARD_OPERATION_ID IS NULL;
select bor.organization_id,
bor.line_id,
bos.standard_operation_id
from bom_operational_routings bor,
bom_operation_sequences bos
where bos.routing_sequence_id = bor.routing_sequence_id
and bos.operation_sequence_id = i_op_seq_id;
SELECT
WORKSTATION_ENABLED
FROM
FLM_EXE_PREFERENCES
WHERE
NVL(ORGANIZATION_ID,-1) = NVL(l_org_id,-1) AND
NVL(LINE_ID,-1) = NVL(l_line_id,-1) AND
NVL(STANDARD_OPERATION_ID,-1) = nvl(l_operation_id,-1);
select bor.organization_id, bor.line_id, bos.standard_operation_id
into l_org_id, l_line_id, l_std_op_id
from bom_operational_routings bor,
bom_operation_sequences bos
where bos.routing_sequence_id = bor.routing_sequence_id
and bos.operation_sequence_id = i_op_seq_id;
select bos.operation_sequence_id
from bom_operation_sequences bos,
wip_flow_schedules wfs,
bom_operational_routings bor
where wfs.wip_entity_id = i_wip_entity_id
and bor.organization_id = i_org_id
and bor.assembly_item_id = wfs.primary_item_id
and nvl(bor.alternate_routing_designator, '########') = nvl(wfs.alternate_routing_designator, '########')
and bor.common_routing_sequence_id = bos.routing_sequence_id
and bos.operation_type = 3 -- line operation
and bos.standard_operation_id = i_std_op_id;
select bos.operation_sequence_id seq_id,
bos.operation_seq_num seq_num
from bom_operation_sequences bos,
wip_flow_schedules wfs,
bom_operational_routings bor
where wfs.wip_entity_id = i_wip_entity_id
and bor.organization_id = i_org_id
and bor.assembly_item_id = wfs.primary_item_id
and nvl(bor.alternate_routing_designator, '@@@@@@@@') = nvl(wfs.alternate_routing_designator, '@@@@@@@@')
and bor.common_routing_sequence_id = bos.routing_sequence_id
and bos.operation_type = 3 -- line operation
and not exists (select '1'
from bom_operation_networks bon
where bon.to_op_seq_id = bos.operation_sequence_id
and bon.transition_type in (1, 2))
order by bos.operation_seq_num;
select next_op_seq_id seq_id
from flm_exe_operations
where wip_entity_id = i_wip_entity_id
and flm_execution_util.workstation_enabled(next_op_seq_id) = 'N'
order by next_op_seq_id;
select count(*)
into l_cnt
from flm_exe_operations
where wip_entity_id = i_wip_entity_id
and operation_sequence_id = l_op_seq_id
and organization_id = i_org_id;
select count(*)
into l_cnt
from flm_exe_operations
where wip_entity_id = i_wip_entity_id
and next_op_seq_id = l_op_seq_id
and organization_id = i_org_id
and current_flag = 'Y';
select count(*)
into l_cnt
from flm_exe_operations
where wip_entity_id = i_wip_entity_id
and operation_sequence_id = l_op_seq_id
and organization_id = i_org_id;
select count(*)
into l_cnt
from bom_operation_networks
where to_op_seq_id = l_op_seq_id
and transition_type in (1,2);
select count(*)
into l_cnt
from bom_operation_networks
where to_op_seq_id = l_op_seq_id
and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N'
and flm_execution_util.workstation_enabled(op_rec.seq_id) = 'N'
start with from_op_seq_id = op_rec.seq_id
connect by prior to_op_seq_id = from_op_seq_id
and transition_type in (1, 2)
and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N';
select count(*)
into l_cnt
from bom_operation_networks
where to_op_seq_id = l_op_seq_id
and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N'
start with from_op_seq_id = op_rec.seq_id
connect by prior to_op_seq_id = from_op_seq_id
and transition_type in (1, 2)
and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N';
insert into flm_exe_operations (
wip_entity_id,
organization_id,
operation_sequence_id,
next_op_seq_id,
current_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number
) values (
i_wip_entity_id,
i_org_id,
i_op_seq_id,
i_next_op_id,
'Y',
1111,
sysdate,
1111,
sysdate,
1111,
1
);
update flm_exe_operations
set current_flag = 'N',
object_version_number = object_version_number + 1
where next_op_seq_id = i_op_seq_id
and wip_entity_id = i_wip_entity_id;
select count(top_bill_sequence_id) count
from bom_explosions be, bom_inventory_components bic
where top_bill_sequence_id = bill_seq
and explosion_type = exp_type
and sort_order = sort_ord
and be.component_sequence_id = bic.component_sequence_id
and bic.wip_supply_type <> 6;
select component_reference_designator
from bom_reference_designators
where component_sequence_id = p_comp_seq_id
order by component_reference_designator;
select pickslip_grouping_rule_id
from wip_parameters
where organization_id = p_org_id;
select min(operation_seq_num) operation_seq_num from
(
select myFrom, operation_seq_num from
(
select from_op_seq_id myFrom, to_op_seq_id, transition_type, operation_seq_num
from bom_operation_networks, bom_operation_sequences
where from_op_seq_id = operation_sequence_id
start with to_op_seq_id = l_op_seq_id and transition_type in (1,2)
connect by PRIOR from_op_seq_id = to_op_seq_id
and transition_type in (1,2)
)
where not exists
(select from_op_seq_id
from bom_operation_networks
where to_op_seq_id = myFrom
and transition_type in (1,2)
)
);
select from_op_seq_id
from bom_operation_networks
where to_op_seq_id = l_to_op_seq_id
and transition_type in (1,2)
and from_op_seq_id not in (l_from_op_seq_id);
select operation_seq_num
from bom_operation_sequences
where operation_sequence_id = l_op_seq_id;
select count(from_op_seq_id) op_count
from bom_operation_networks
where to_op_seq_id = l_to_op_seq_id
and transition_type in (1,2);
INV_LOT_API_PUB.InsertLot (
p_api_version => 1.0,
p_init_msg_list => 'F',
p_commit => 'T',
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_inventory_item_id => p_primary_item_id,
p_organization_id => p_org_id,
p_lot_number => l_lot_number,
p_expiration_date => l_exp_date,
x_object_id => l_object_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data );
select operation_sequence_id
from bom_operation_sequences
where routing_sequence_id = p_rtg_seq_id
and operation_type = 3;
select operation_sequence_id
from bom_operation_sequences
where routing_sequence_id = p_rtg_seq_id
and operation_type = 3
and operation_seq_num = (
select min(operation_seq_num)
from bom_operation_sequences
where routing_sequence_id = p_rtg_seq_id
and operation_type = 3);
select distinct operation_sequence_id
from flm_exe_operations
where wip_entity_id = p_wip_ent_id
and organization_id = p_org_id
order by operation_sequence_id;
select distinct operation_sequence_id, next_op_seq_id
from flm_exe_operations
where wip_entity_id = p_wip_ent_id
and organization_id = p_org_id
order by operation_sequence_id;
select from_op_seq_id, to_op_seq_id, transition_type, operation_seq_num
from bom_operation_networks, bom_operation_sequences
where from_op_seq_id = operation_sequence_id and transition_type=1
start with from_op_seq_id = p_op_seq_id
connect by PRIOR to_op_seq_id = from_op_seq_id
and prior transition_type =1;
select operation_seq_num
from bom_operation_sequences
where routing_sequence_id = p_rtg_seq_id
and line_op_seq_id = p_lop_seq_id
and operation_type = 1;
select count(operation_sequence_id) opcount
from flm_exe_operations
where wip_entity_id = p_wip_ent_id
and organization_id = p_org_id
and operation_sequence_id = p_from_op_seq;
select distinct operation_seq_num
from flm_exe_req_operations fero
where fero.organization_id = p_org_id
and fero.wip_entity_id = p_wip_ent_id;
select nvl(backflush_option, G_BFLUSH_OPTION_ALL) backflush_option
from flm_exe_preferences
where organization_id = p_org_id
and line_id = p_line_id;
select nvl(backflush_option,G_BFLUSH_OPTION_ALL) backflush_option
from flm_exe_preferences
where organization_id = p_org_id;
select common_routing_sequence_id
from bom_operational_routings
where organization_id = p_org_id
and assembly_item_id = p_assembly_id;
select common_routing_sequence_id
from bom_operational_routings
where organization_id = p_org_id
and assembly_item_id = p_assembly_id
and alternate_routing_designator = p_alt_desig;
select 1 as subs
from dual
where exists (
select inventory_item_id
from flm_exe_req_operations
where organization_id = p_org_id
and wip_entity_id = p_wip_ent_id
and inventory_item_id <> -1);
select operation_sequence_id, operation_seq_num
from bom_operation_sequences
where routing_sequence_id = p_rtg_seq_id
and line_op_seq_id = p_lop_seq_id
and operation_type = 1;
select fero.organization_id,
fero.inventory_item_id,
fero.operation_seq_num,
fero.quantity_per_assembly,
fero.supply_subinventory,
fero.supply_locator_id,
fero.component_sequence_id,
nvl(fero.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) basis_type,
msi.primary_uom_code,
flm_util.get_key_flex_item(fero.inventory_item_id,fero.organization_id) inventory_item_name,
msi.serial_number_control_code,
msi.lot_control_code,
msi.restrict_subinventories_code,
msi.restrict_locators_code,
msi.description,
msi.revision_qty_control_code,
msi.location_control_code
from flm_exe_req_operations fero,
mtl_system_items msi
where fero.organization_id = p_org_id
and fero.wip_entity_id = p_wip_ent_id
and fero.inventory_item_id = msi.inventory_item_id
and msi.organization_id = fero.organization_id
order by operation_seq_num;
l_insertPhantom number := WIP_CONSTANTS.NO;
l_compTbl.delete(v_idx);
l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
if(l_insertPhantom <> WIP_CONSTANTS.YES) then
l_compTbl.delete(v_idx);
elsif(l_insertPhantom = WIP_CONSTANTS.YES) then
if(l_compTbl(v_idx).operation_seq_num > 0) then
l_compTbl(v_idx).operation_seq_num := -1*abs(l_compTbl(v_idx).operation_seq_num);
l_compTbl.delete(v_idx);
select msi.wip_supply_subinventory,
msi.wip_supply_locator_id,
wp.default_pull_supply_subinv,
wp.default_pull_supply_locator_id
into l_msiSubinv,
l_msiLocatorID,
l_wpSubinv,
l_wpLocatorID
from mtl_system_items msi,
wip_parameters wp
where msi.organization_id = wp.organization_id
and msi.organization_id = p_orgID
and msi.inventory_item_id = c_recorded_comps.inventory_item_id;
if(c_recorded_comps.quantity_per_assembly <> -9999) then --bug 5181888, add this clause to remove the deleted comps in final merge
x_compTbl.addItem
(p_opSeqNum => c_recorded_comps.operation_seq_num,
p_itemID => c_recorded_comps.inventory_item_id,
p_itemName => c_recorded_comps.inventory_item_name ,
p_priQty => l_qty,
p_priUomCode => c_recorded_comps.primary_uom_code,
p_supplySubinv => c_recorded_comps.supply_subinventory,
p_supplyLocID => c_recorded_comps.supply_locator_id,
p_wipSupplyType => wip_constants.assy_pull,
p_txnActionID => wip_constants.isscomp_action,
p_mtlTxnsEnabledFlag => null,
p_serialControlCode => c_recorded_comps.serial_number_control_code,
p_lotControlCode => c_recorded_comps.lot_control_code,
p_revision => l_revision,
p_departmentID => 1,
p_restrictSubsCode => c_recorded_comps.restrict_subinventories_code,
p_restrictLocsCode =>c_recorded_comps.restrict_locators_code,
p_projectID => p_projectID,
p_taskID => p_taskID,
p_componentSeqID => c_recorded_comps.component_sequence_id,
p_cmpTxnID => null,
p_itemDescription => c_recorded_comps.description,
p_locatorName => flm_util.get_key_flex_location(c_recorded_comps.supply_locator_id,p_orgID),
p_revisionContolCode => c_recorded_comps.revision_qty_control_code,
p_locationControlCode => c_recorded_comps.location_control_code,
p_locatorProjectID => null,
p_locatorTaskID => null);
select lot_number, lot_quantity, parent_lot_number, creation_date
from flm_exe_lot_numbers
where organization_id = p_org_id
and wip_entity_id = p_wip_ent_id
and inventory_item_id = p_inv_item_id
and operation_seq_num = p_op_seq_num
order by creation_date, lot_number;
select fm_serial_number, to_serial_number, serial_quantity, lot_number, parent_serial_number, creation_date
from flm_exe_serial_numbers
where organization_id = p_org_id
and wip_entity_id = p_wip_ent_id
and inventory_item_id = p_inv_item_id
and operation_seq_num = p_op_seq_num
order by creation_date, fm_serial_number;
select fm_serial_number, to_serial_number, serial_quantity, lot_number, parent_serial_number, creation_date
from flm_exe_serial_numbers
where organization_id = p_org_id
and wip_entity_id = p_wip_ent_id
and inventory_item_id = p_inv_item_id
and operation_seq_num = p_op_seq_num
and lot_number = p_comp_lot_num
order by creation_date, fm_serial_number;
select wip_entity_id
into l_wip_ent_id
from wip_flow_schedules
where organization_id = orgId
and schedule_number = schNum;
select count(wip_entity_id) count
from flm_exe_operations
where organization_id = p_orgId
and wip_entity_id = p_wipEntId;
select count(wip_entity_id) count
from flm_exe_req_operations
where organization_id = p_orgId
and wip_entity_id = p_wipEntId;
select 1 as kanban_exists
from dual
where exists
(select kanban_card_id
from mtl_kanban_card_activity
where source_wip_entity_id = l_wip_entity_id
);
select organization_id, wip_entity_id, operation_seq_num, inventory_item_id
from flm_exe_req_operations
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num;
select organization_id, wip_entity_id, operation_seq_num, inventory_item_id,
fm_serial_number, to_serial_number, parent_serial_number, lot_number
from flm_exe_serial_numbers fesn
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id;
insert into flm_exe_serial_numbers(
requirement_serial_id,
organization_id,
wip_entity_id,
operation_seq_num,
inventory_item_id,
fm_serial_number,
to_serial_number,
parent_serial_number,
lot_number,
serial_quantity,
object_version_number,
created_by,
creation_date,
last_update_login,
last_update_date,
last_updated_by)
values
(
flm_exe_serial_numbers_s.nextval,
p_org_id,
p_wip_entity_id,
p_operation_seq_num,
p_inventory_item_id,
l_cur_serial_number,
l_cur_serial_number,
p_parent_serial_number,
p_lot_number,
1,
1,
l_user_id,
sysdate,
l_login_id,
sysdate,
l_user_id
);
delete from flm_exe_serial_numbers
where organization_id = p_org_id
and wip_entity_id = p_wip_entity_id
and operation_seq_num = p_operation_seq_num
and inventory_item_id = p_inventory_item_id
and fm_serial_number = p_fm_serial
and to_serial_number = p_to_serial;
select serial_number
from mtl_serial_numbers msn,
mtl_transaction_types mtt
where (msn.group_mark_id is null or msn.group_mark_id = -1)
and msn.current_status in (1, 4 )
and msn.inventory_item_id = p_inv_item_id
and msn.current_organization_id = p_org_id
and mtt.transaction_type_id = 44
and inv_material_status_grp.is_status_applicable(
null,
null,
mtt.transaction_type_id
, NULL
, null
, msn.current_organization_id
, msn.inventory_item_id
, NULL
, NULL
, NULL
, serial_number
, 'S') = 'Y';
select lot_number
from mtl_lot_numbers mln,
mtl_transaction_types mtt
where mln.inventory_item_id = p_inv_item_id
and mln.organization_id = p_org_id
and mtt.transaction_type_id = 44
and inv_material_status_grp.is_status_applicable(
null
, null
, mtt.transaction_type_id
, null
, NULL
, mln.organization_id
, mln.inventory_item_id
, NULL
, NULL
, lot_number
, NULL
, 'O') = 'Y'
and nvl(disable_flag,2)=2;
select count(transaction_interface_id)
into l_cnt
from mtl_transactions_interface
where parent_id is not null
and parent_id = txn_intf_id;
select bbom.bill_sequence_id,
wfs.bom_revision_date
from bom_bill_of_materials bbom,
wip_flow_schedules wfs
where wfs.wip_entity_id = p_wip_entity_id
and wfs.organization_id = p_org_id
and bbom.assembly_item_id = wfs.primary_item_id
and bbom.organization_id = wfs.organization_id
and nvl(bbom.alternate_bom_designator, 'NULL') = nvl(wfs.alternate_bom_designator, 'NULL');
select count(component_item_id) comp_cnt
from bom_inventory_components bic,
mtl_system_items msi
where bill_sequence_id = l_bill_seq_id
and msi.inventory_item_id = bic.component_item_id
and bic.effectivity_date < nvl(l_bom_rev_date,sysdate)
and ((bic.disable_date is null) or
(bic.disable_date is not null and
bic.disable_date > nvl(l_bom_rev_date, sysdate)))
and msi.organization_id = p_org_id
and nvl(msi.mtl_transactions_enabled_flag,'N') = 'N'
and nvl(bic.wip_supply_type,msi.wip_supply_type) <> 6;