[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT approval_list_id
INTO l_id
FROM eng_ecn_approval_lists
WHERE approval_list_name = p_approval_list;
l_sql_stmt := 'SELECT OLC.LIFECYCLE_ID '
|| 'FROM EGO_OBJ_TYPE_LIFECYCLES OLC, '
|| 'FND_OBJECTS O '
|| 'WHERE O.OBJ_NAME = :1 '
|| 'AND OLC.OBJECT_ID = O.OBJECT_ID '
|| 'AND OLC.OBJECT_CLASSIFICATION_CODE in '
|| '(SELECT TO_CHAR(IC.ITEM_CATALOG_GROUP_ID) '
|| ' FROM MTL_ITEM_CATALOG_GROUPS_B IC '
|| ' CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id '
|| ' START WITH item_catalog_group_id = '
|| ' (SELECT item_catalog_group_id '
|| ' FROM mtl_system_items '
|| ' WHERE inventory_item_id = :2 '
|| ' AND organization_id = :3 '
|| ' )) ';
l_sql_stmt := ' SELECT LP.PROJ_ELEMENT_ID '
|| 'from pa_ego_phases_v LP '
|| 'where name = :1 '
|| 'and parent_structure_id = :2 '
|| 'and object_type = :3 ';
SELECT display_name
into l_obj_name
from fnd_objects_vl
where object_id = p_object_id;
SELECT vl.obj_name, obj.query_object_name, obj.query_column1_name,
obj.query_column2_name, obj.query_column3_name, obj.query_column4_name,
obj.query_column5_name, obj.query_column1_type, obj.query_column2_type,
obj.query_column3_type, obj.query_column4_type, obj.query_column5_type,
vl.pk1_column_name, vl.pk2_column_name, vl.pk3_column_name,
vl.pk4_column_name, vl.pk5_column_name ,vl.object_id
INTO x_object_name, x_query_object_name, x_query_column1_name,
x_query_column2_name, x_query_column3_name, x_query_column4_name,
x_query_column5_name, x_query_column1_type, x_query_column2_type,
x_query_column3_type, x_query_column4_type, x_query_column5_type,
x_fk1_column_name, x_fk2_column_name, x_fk3_column_name, x_fk4_column_name,
x_fk5_column_name,x_object_id
FROM fnd_objects_vl vl, eng_change_objects obj
WHERE vl.display_name = p_display_name AND obj.object_id = vl.object_id;
SELECT change_order_type_id
INTO l_id
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_org_id;
SELECT change_id, change_mgmt_type_code
INTO l_id, x_change_mgmt_type_code
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_org_id;
SELECT project_id
INTO l_id
FROM pa_projects_all
WHERE name = p_project_name;
SELECT task_id
INTO l_id
FROM pa_tasks
WHERE task_number = p_task_number
AND project_id = p_project_id;
SELECT hz.party_id
FROM PER_PEOPLE_F P, HZ_PARTIES HZ , PER_ASSIGNMENTS_X A, PER_PERSON_TYPES T
WHERE A.PERSON_ID = P.PERSON_ID
AND HZ.PARTY_ID = P.PARTY_ID
AND HZ.PARTY_TYPE = 'PERSON'
AND A.PRIMARY_FLAG = 'Y'
AND A.ASSIGNMENT_TYPE = 'E'
AND P.PERSON_TYPE_ID = T.PERSON_TYPE_ID
AND P.BUSINESS_GROUP_ID = T.BUSINESS_GROUP_ID
AND TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
AND T.system_person_TYPE = 'EMP'
AND P.EMPLOYEE_NUMBER = p_employee_number;
SELECT party_id INTO l_id
FROM hz_parties
WHERE party_name = p_assignee AND party_type = 'GROUP';
SELECT status_code into l_result from eng_change_statuses_vl
where status_name = p_status_name;
SELECT status_code ,status_type into l_st_code,l_st_type
from eng_change_statuses_vl
where status_name = p_status_name
and ((status_code in (select status_code from eng_lifecycle_statuses
where entity_name='ENG_CHANGE_TYPE'
and entity_id1 = p_change_order_type_id)
AND status_type <> 0)
OR status_type =0);
SELECT status_code ,status_code into l_st_code,l_st_type
/* Changed the above line from SELECT status_code ,status_type into l_st_code,l_st_type for Bug 8823124*/
from eng_change_statuses_vl
where status_name = p_status_name;
SELECT to_number(lookup_code) into l_result from mfg_lookups
where lookup_type = 'ENG_ECN_APPROVAL_STATUS'
and meaning = p_approval_status_name;
SELECT lookup_code INTO l_result
FROM fnd_lookup_values
where lookup_type = 'ENG_CHANGE_LINE_STATUSES'
AND meaning = p_status_name AND language = userenv('LANG'); */
SELECT status_code into l_result from eng_change_statuses_vl
where status_name = p_status_name;
SELECT change_mgmt_type_code
INTO l_result
FROM eng_change_order_types_VL --11.5.10 changes
WHERE trim(type_name) = trim(p_change_management_type)
and type_classification='CATEGORY' and NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE
and START_DATE <=SYSDATE;
SELECT lookup_code
INTO l_result
FROM fnd_lookup_values_vl
WHERE lookup_type = 'ENG_CHANGE_SOURCE_TYPES'
AND upper(trim(meaning)) = upper(trim(p_source_type));
SELECT person_id
INTO l_result
FROM ego_people_v
WHERE person_type = p_source_type_code
AND Upper(person_name) = Upper(p_source_name);
Select organization_structure_id
into l_id
from per_organization_structures
where name = p_organization_hierarchy;
SELECT -100
INTO l_result
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_item_id
AND organization_id = p_organization_id
AND revision = p_item_revision ;
SELECT change_order_type_id, disable_date, object_id
INTO x_change_order_id, x_disable_date, x_object_id
FROM eng_change_order_types_vl
WHERE type_name = p_change_order_type
AND change_mgmt_type_code = p_change_mgmt_type
AND type_classification='HEADER';
SELECT change_order_type_id, disable_date, object_id
INTO x_change_order_id, x_disable_date, x_object_id
FROM eng_change_order_types_vl
WHERE type_name = p_change_order_type
AND change_mgmt_type_code = p_change_mgmt_type
AND type_classification='LINE';
SELECT change_mgmt_type_code
INTO x_change_mgmt_type_code
FROM eng_change_order_types_vl
WHERE type_name = p_change_mgmt_type_name
and type_classification='CATEGORY'
and NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE
and START_DATE <=SYSDATE; --11.5.10
SELECT wip_entity_id
INTO l_id
FROM WIP_ENTITIES
WHERE organization_id = p_organization_id
AND wip_entity_name = p_work_order ;
l_token_tbl.DELETE;
SELECT hou.organization_id
INTO l_id
FROM hr_organization_units hou
-- , org_organization_definitions org_def
WHERE hou.name = p_responsible_org
--AND org_def.business_group_id = hou.business_group_id
--AND org_def.organization_id = p_current_org ;
AND exists (SELECT null FROM hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'BOM_ECOD'
AND hoi.org_information2 = 'Y');
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_kfv
where concatenated_segments = p_revised_item_num
and organization_id = p_organization_id;
select revision_id into l_id
from mtl_item_revisions
where inventory_item_id = p_revised_item_num
and organization_id = p_organization_id
and revision = p_revison_code ;
select inventory_item_id into l_id
from mtl_system_items_kfv
where concatenated_segments = p_use_up_item_num
and organization_id = p_organization_id;
select inventory_item_id into l_id
from mtl_system_items_kfv
where concatenated_segments = p_assembly_item_num
and organization_id = p_organization_id;
SELECT bill_sequence_id
INTO l_id
FROM bom_bill_of_materials
WHERE assembly_item_id = p_assembly_item_id
AND NVL(alternate_bom_designator, 'NONE') =
NVL(p_alternate_bom_designator, 'NONE')
AND organization_id = p_organization_id;
SELECT 'Valid' b_valid
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_seq_id
AND bill_sequence_id IS NOT NULL;
SELECT bill_sequence_id,
revised_item_id
INTO x_bill_sequence_id,
x_assembly_item_id
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_seq_id;
SELECT bill_sequence_id, revised_item_Sequence_id
INTO l_Bill_Seq, l_Rev_Item_Seq
FROM eng_revised_items
WHERE revised_item_id = p_revised_item_id
AND NVL(new_item_revision,'NULL')= NVL(p_item_revision,'NULL')
AND TRUNC(scheduled_date) = trunc(p_effective_date)
AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
= NVL(p_from_end_item_number, FND_API.G_MISS_CHAR)
AND change_notice = p_change_notice
AND organization_id = p_organization_id;
SELECT revised_item_id
INTO l_assembly_item_id
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_seq_id;
SELECT revised_item_sequence_id
INTO l_id
FROM Eng_revised_items
WHERE revised_item_id = p_revised_item_id
AND change_notice = p_change_notice
AND organization_id = p_organization_id
AND NVL(new_item_revision, 'NONE') =
NVL(p_new_item_revision, 'NONE');
SELECT revision_id
INTO l_revision_id
FROM eng_change_order_revisions
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id
AND revision = p_rev;
select revised_item_sequence_id
from eng_revised_items
where REVISED_ITEM_ID = rev_item_id and
organization_id = p_organization_id and
nvl(alternate_bom_designator,'NULL') = nvl(p_alternate_bom_code,'NULL')and
SCHEDULED_DATE = p_schedule_date and
change_id= p_change_id ;
SELECT REVISION_ID, REVISION, EFFECTIVITY_DATE
FROM MTL_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = p_assembly_item_id
AND ORGANIZATION_ID = p_organization_id
AND EFFECTIVITY_DATE <= p_revision_date
AND IMPLEMENTATION_DATE IS NOT NULL
ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
SELECT REVISION_ID
FROM MTL_ITEM_REVISIONS
WHERE INVENTORY_ITEM_ID = p_assembly_item_id
AND ORGANIZATION_ID = p_organization_id
AND REVISION = p_revision
AND EFFECTIVITY_DATE > p_date;
SELECT LOOKUP_CODE
INTO l_reason_code
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'EGO_ITEM_REVISION_REASON'
AND ENABLED_FLAG = 'Y'
AND MEANING = p_reason;
SELECT structure_type_id
INTO l_structure_type_id
FROM bom_structure_types_vl
WHERE structure_type_name = p_structure_type_name;
(p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE))
THEN
l_rev_item_unexp_rec.bill_sequence_id :=
BOM_Val_To_Id.Bill_Sequence_Id
( p_assembly_item_id => l_rev_item_unexp_rec.revised_item_id
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_alternate_bom_code =>
p_revised_item_rec.alternate_bom_code
, x_err_text => l_err_text
);
select revision_id into l_rev_item_unexp_rec.from_end_item_revision_id
from mtl_item_revisions
where inventory_item_id =l_rev_item_unexp_rec.from_end_item_id
and organization_id =l_rev_item_unexp_rec.organization_id;
select bill_sequence_id into l_bill_seq_id from bom_bill_of_materials
where
ASSEMBLY_ITEM_ID = l_rev_item_unexp_rec.from_end_item_id
and ORGANIZATION_ID = l_rev_item_unexp_rec.organization_id
and ALTERNATE_BOM_DESIGNATOR = p_revised_item_rec.from_end_item_alternate;
select STRUCTURE_REVISION_ID into l_rev_item_unexp_rec.from_end_item_struct_rev_id
from should be using minor revision table
where BILL_SEQUENCE_ID =l_bill_seq_id
and REVISION =p_revised_item_rec.from_end_item_revision
and OBJECT_REVISION_ID = l_rev_item_unexp_rec.from_end_item_revision_id;
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE) AND
p_revised_item_rec.from_item_revision IS NOT NULL)
THEN
l_rev_item_unexp_rec.from_item_revision_id := From_Revision_Id
( p_assembly_item_id => l_rev_item_unexp_rec.revised_item_id
, p_organization_id => l_rev_item_unexp_rec.organization_id
, p_revision => p_revised_item_rec.from_item_revision
, p_revision_date => SYSDATE
, x_err_text => l_err_text
);
p_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE) AND
p_revised_item_rec.New_Revised_Item_Revision IS NOT NULL AND
p_revised_item_rec.new_revision_reason IS NOT NULL)
THEN
l_rev_item_unexp_rec.new_revision_reason_code :=
New_Revision_Reason_Code
( p_reason => p_revised_item_rec.new_revision_reason
, x_err_text => l_err_text
);
l_token_tbl.DELETE;
l_token_tbl.DELETE;
l_token_tbl.DELETE;
l_token_tbl.DELETE;
l_token_tbl.DELETE;
l_token_tbl.DELETE;
select component_sequence_id
into l_id
from bom_inventory_components
where bill_sequence_id = p_bill_sequence_id
and component_item_id = p_component_item_id
and operation_seq_num = p_operation_sequence_num
and effectivity_date = p_effectivity_date;
select bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = rev_item_id and
organization_id = org_id and
nvl(effectivity_control, 1) <> 4 AND -- Bug 4210718
nvl(alternate_bom_designator,'NULL') = nvl(alt_bom_code,'NULL');
SELECT bill_sequence_id
, revised_item_Sequence_id
, lot_number
, from_wip_entity_id
, to_wip_entity_id
, from_cum_qty
, NVL(eco_for_production,2)
, NVL(cfm_routing_flag,2)
INTO l_Bill_Seq
, l_Rev_Item_Seq
, x_lot_number
, x_from_wip_entity_id
, x_to_wip_entity_id
, x_from_cum_qty
, x_eco_for_production
, x_cfm_routing_flag
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 --bug 5096309 removed trunc
AND change_notice = p_change_notice
AND organization_id = p_organization_id
AND revised_item_id = p_revised_item_id
and bill_sequence_id = l_bill_seq1;
SELECT bill_sequence_id
, revised_item_Sequence_id
, lot_number
, from_wip_entity_id
, to_wip_entity_id
, from_cum_qty
, NVL(eco_for_production,2)
, NVL(cfm_routing_flag,2)
INTO l_Bill_Seq
, l_Rev_Item_Seq
, x_lot_number
, x_from_wip_entity_id
, x_to_wip_entity_id
, x_from_cum_qty
, x_eco_for_production
, x_cfm_routing_flag
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 --bug 5096309 removed trunc
AND change_notice = p_change_notice
AND organization_id = p_organization_id
AND revised_item_id = p_revised_item_id ;
SELECT routing_sequence_id
, revised_item_Sequence_id
, lot_number
, from_wip_entity_id
, to_wip_entity_id
, from_cum_qty
, NVL(eco_for_production,2)
, NVL(cfm_routing_flag,2)
INTO x_routing_sequence_id
, l_Rev_Item_Seq
, x_lot_number
, x_from_wip_entity_id
, x_to_wip_entity_id
, x_from_cum_qty
, x_eco_for_production
, x_cfm_routing_flag
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 TRUNC(scheduled_date) = TRUNC(p_effective_date)
AND change_notice = p_change_notice
AND organization_id = p_organization_id
AND revised_item_id = p_revised_item_id
AND NVL(alternate_bom_designator, FND_API.G_MISS_CHAR) =
NVL(p_alternate_routing_code, FND_API.G_MISS_CHAR); -- Added for bug 13329115
( BOM_Globals.G_OPR_UPDATE, BOM_globals.G_OPR_DELETE,
BOM_Globals.G_OPR_CANCEL
) AND
x_bill_sequence_id IS NULL AND p_entity_processed = 'RC'
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
g_Token_Tbl.Delete;
g_Token_Tbl.Delete;
g_Token_Tbl.Delete ;
l_token_tbl.DELETE;
l_dynamic_sql := 'SELECT ';
l_token_tbl.DELETE ;