The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
INTO l_id
FROM mtl_parameters
WHERE organization_code = p_organization;
SELECT inventory_item_id
INTO l_id
FROM mtl_system_items_b_kfv
WHERE organization_id = p_organization_id
and concatenated_segments = p_revised_item_num;
SELECT routing_sequence_id
, cfm_routing_flag
, common_routing_sequence_id
INTO l_id, l_cfm_flag, l_com_rtg_seq_id
FROM bom_operational_routings
WHERE organization_id = p_organization_id
AND assembly_item_id = p_assembly_item_id
AND NVL(alternate_routing_designator, FND_API.G_MISS_CHAR) =
NVL(p_alternate_routing_designator, FND_API.G_MISS_CHAR);
SELECT routing_sequence_id, revised_item_Sequence_id
INTO x_routing_sequence_id, l_Rev_Item_Seq
FROM eng_revised_items
WHERE NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR )
= NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
AND NVL(new_routing_revision, FND_API.G_MISS_CHAR) =
NVL(p_new_routing_revision, FND_API.G_MISS_CHAR)
AND NVL(new_item_revision,FND_API.G_MISS_CHAR)= NVL(p_item_revision,FND_API.G_MISS_CHAR)
AND scheduled_date = p_effective_date
-- AND TRUNC(scheduled_date) = TRUNC(p_effective_date) -- time
AND change_notice = p_change_notice
AND organization_id = p_organization_id
AND revised_item_id = p_revised_item_id ;
SELECT inventory_location_id
INTO supply_locator_id
FROM mtl_item_locations_kfv
WHERE organization_id = p_organization_id
and concatenated_segments = p_completion_location_name;
SELECT line_id
INTO l_id
FROM wip_lines
WHERE organization_id = p_organization_id
AND line_code = p_line_code;
SELECT standard_operation_id
INTO l_id
FROM bom_standard_operations bso
-- , eng_revised_items eri
WHERE NVL(bso.operation_type, 1)
= DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
, NVL(p_operation_type, 1 ) )
-- AND NVL(bso.line_id, FND_API.G_MISS_NUM)
-- = NVL(eri.line_id, FND_API.G_MISS_NUM)
-- AND eri.revised_item_sequence_id = p_rev_item_sequence_id
AND NVL(bso.line_id, FND_API.G_MISS_NUM ) = FND_API.G_MISS_NUM
AND bso.organization_id = p_organization_id
AND bso.operation_code = p_standard_operation_code ;
SELECT standard_operation_id
INTO l_id
FROM bom_standard_operations bso
, bom_operational_routings bor
WHERE NVL(bso.operation_type,1 )
= DECODE(p_operation_type, FND_API.G_MISS_NUM, 1
, NVL(p_operation_type, 1))
AND NVL(bso.line_id, FND_API.G_MISS_NUM)
= NVL(bor.line_id, FND_API.G_MISS_NUM)
AND bor.routing_sequence_id = p_routing_sequence_id
AND bso.organization_id = p_organization_id
AND bso.operation_code = p_standard_operation_code ;
SELECT department_id
INTO l_id
FROM bom_departments
WHERE organization_id = p_organization_id
AND department_code = p_department_code;
SELECT bos.operation_sequence_id
INTO l_id
FROM BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS bor
, BOM_STANDARD_OPERATIONS bso
WHERE bso.organization_id = p_organization_id
AND bso.standard_operation_id = bos.standard_operation_id
AND bos.operation_seq_num = p_process_seq_number
AND bos.operation_type = 2 -- Operation Type : Process
AND bos.routing_sequence_id = bor.common_routing_sequence_id
AND bor.routing_sequence_id = p_routing_sequence_id ;
SELECT bos.operation_sequence_id
INTO l_id
FROM BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS bor
, BOM_STANDARD_OPERATIONS bso
WHERE bso.operation_code = p_process_code
AND bso.organization_id = p_organization_id
AND bso.standard_operation_id = bos.standard_operation_id
AND bos.operation_seq_num = p_process_seq_number
AND bos.operation_type = 2 -- Operation Type : Process
AND bos.routing_sequence_id = bor.common_routing_sequence_id
AND bor.routing_sequence_id = p_routing_sequence_id ;
SELECT bos.operation_sequence_id
INTO l_id
FROM BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS bor
, BOM_STANDARD_OPERATIONS bso
WHERE bso.organization_id = p_organization_id
AND bso.standard_operation_id = bos.standard_operation_id
AND bos.operation_seq_num = p_line_seq_number
AND bos.operation_type = 3 -- Operation Type : Line Op
AND bos.routing_sequence_id = bor.common_routing_sequence_id
AND bor.routing_sequence_id = p_routing_sequence_id ;
SELECT bos.operation_sequence_id
INTO l_id
FROM BOM_OPERATION_SEQUENCES bos
, BOM_OPERATIONAL_ROUTINGS bor
, BOM_STANDARD_OPERATIONS bso
WHERE bso.operation_code = p_line_code
AND bso.organization_id = p_organization_id
AND bso.standard_operation_id = bos.standard_operation_id
AND bos.operation_seq_num = p_line_seq_number
AND bos.operation_type = 3 -- Operation Type : Line Op
AND bos.routing_sequence_id = bor.common_routing_sequence_id
AND bor.routing_sequence_id = p_routing_sequence_id ;
SELECT operation_sequence_id
INTO l_id
FROM BOM_OPERATION_SEQUENCES
WHERE NVL(operation_type, 1) = DECODE(p_operation_type,
FND_API.G_MISS_NUM, 1,
NVL(p_operation_type, 1)
)
AND routing_sequence_id = p_routing_sequence_id
AND effectivity_date = p_old_effective_date -- Changed for bug 2647027
-- /** time **/ AND TRUNC(effectivity_date) = TRUNC(p_old_effective_date)
AND operation_seq_num = p_old_op_seq_num;
SELECT setup_id
INTO l_id
FROM bom_setup_types
WHERE organization_id = p_organization_id
AND setup_code = p_setup_type ;
SELECT activity_id
INTO l_id
FROM cst_activities
WHERE NVL(organization_id, p_organization_id ) = p_organization_id
AND activity = p_activity;
SELECT resource_id
INTO l_id
FROM bom_resources
WHERE organization_id = p_organization_id
AND resource_code = p_resource_code;
SELECT operation_sequence_id
INTO l_id
FROM bom_operation_sequences
WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
AND implementation_date IS NULL )
OR (l_bo_id = BOM_Rtg_Globals.G_RTG_BO
AND implementation_date IS NOT NULL )
)
-- NVL check in operation type included for bug 3293381
AND (( NVL(operation_type, 1) = 1 AND
effectivity_date = p_effectivity_date) -- Changed for bug 2647027
-- /** time **/ TRUNC(effectivity_date) = TRUNC(p_effectivity_date))
OR p_operation_type IN (2, 3)
)
AND NVL(operation_type, 1 ) = DECODE(p_operation_type,
FND_API.G_MISS_NUM, 1,
NVL(p_operation_type, 1 ) )
AND operation_seq_num = p_operation_seq_num
AND routing_sequence_id = p_routing_sequence_id ;
SELECT 1
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id;
l_token_tbl.Delete;
SELECT 1
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id;
l_token_tbl.delete ;
l_token_tbl.delete ;
SELECT 1
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id;
SELECT 1
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id;
SELECT 1
FROM bom_alternate_designators
WHERE alternate_designator_code = p_alt_designator
AND organization_id = p_organization_id;