[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_type
FROM hz_parties
WHERE party_id=cp_party_id;
SELECT 'X'
FROM fnd_grants grants,
fnd_objects obj,
fnd_menus menus
WHERE grants.grantee_key=cp_grantee_key
AND grants.grantee_type=cp_grantee_type
AND grants.menu_id=menus.menu_id
AND menus.menu_name=cp_menu_name
AND grants.object_id = obj.object_id
AND obj.obj_name=cp_object_name
AND grants.instance_type=cp_instance_type
AND ((grants.instance_pk1_value=cp_instance_pk1_value )
OR((grants.instance_pk1_value = ' *NULL*' ) AND (cp_instance_pk1_value IS NULL)))
AND ((grants.instance_pk2_value=cp_instance_pk2_value )
OR((grants.instance_pk2_value = ' *NULL*' ) AND (cp_instance_pk2_value IS NULL)))
AND ((grants.instance_pk3_value=cp_instance_pk3_value )
OR((grants.instance_pk3_value = ' *NULL*' ) AND (cp_instance_pk3_value IS NULL)))
AND ((grants.instance_pk4_value=cp_instance_pk4_value )
OR((grants.instance_pk4_value = ' *NULL*' ) AND (cp_instance_pk4_value IS NULL)))
AND ((grants.instance_pk5_value=cp_instance_pk5_value )
OR((grants.instance_pk5_value = ' *NULL*' ) AND (cp_instance_pk5_value IS NULL)))
AND ((grants.instance_set_id=cp_instance_set_id )
OR((grants.instance_set_id = ' *NULL*' ) AND (cp_instance_set_id IS NULL)))
AND (((grants.start_date<=cp_start_date )
AND (( grants.end_date = '*NULL*') OR (cp_start_date <=grants.end_date )))
OR ((grants.start_date >= cp_start_date )
AND (( cp_end_date IS NULL) OR (cp_end_date >=grants.start_date))));
SELECT 'y'
FROM mtl_system_items
WHERE inventory_item_id =
(select revised_item_id from eng_revised_items
where change_notice = p_change_notice
and organization_id = organization_id)
AND organization_id = p_organization_id
AND eng_item_flag = 'Y';
select assembly_type
into l_new_assembly_type
from eng_change_order_types
where change_order_type_id =
p_new_change_order_type_id;
PROCEDURE Check_Delete
( p_eco_rec IN ENG_ECO_PUB.Eco_Rec_Type
, p_Unexp_ECO_rec IN ENG_Eco_PUB.Eco_Unexposed_Rec_Type
, x_return_status OUT NOCOPY VARCHAR2
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
)
IS
l_Token_Tbl Error_Handler.Token_Tbl_Type;
SELECT 'x'
FROM eng_revised_items
WHERE change_notice = p_ECO_rec.ECO_Name
AND organization_id = p_Unexp_ECO_rec.organization_id;
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
(l_ri_exists = 1 OR p_unexp_eco_rec.approval_status_type in (2,3,5))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_CANNOT_DELETE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
END Check_Delete;
SELECT 'Rev Comp referencing Seq Num exists'
FROM SYS.DUAL
WHERE EXISTS (SELECT NULL
FROM ENG_ENGINEERING_CHANGES eec1
, ENG_REVISED_ITEMS eri1
, ENG_REVISED_ITEMS eri2
WHERE eri1.revised_item_id = eri2.revised_item_id
AND eri1.organization_id = eec1.organization_id
AND eri1.change_notice = eec1.change_notice
AND eec1.change_notice <> p_eco_name
AND eec1.organization_id = p_organization_id
AND eri2.organization_id = p_organization_id
AND eri2.change_notice = p_eco_name
AND EXISTS (SELECT NULL
FROM BOM_INVENTORY_COMPONENTS bic
, BOM_OPERATION_SEQUENCES bos
WHERE bic.implementation_date IS NULL
AND bic.operation_seq_num = bos.operation_seq_num
AND bic.bill_sequence_id = eri1.bill_sequence_id
AND bos.revised_item_sequence_id = eri2.revised_item_sequence_id
AND bos.routing_sequence_id = eri2.routing_sequence_id
)
) ;
SELECT 'Valid'
FROM SYS.DUAL
WHERE EXISTS ( SELECT 'Valid'
FROM per_organization_structures
WHERE inv_orghierarchy_pvt.org_hierarchy_access
(p_org_hierarchy) = 'Y'
AND inv_orghierarchy_pvt.org_hierarchy_level_access
(p_org_hierarchy,p_org_id) = 'Y'
) ;
SELECT organization_name INTO l_org_name
FROM org_organization_definitions
WHERE organization_id = p_org_id ;
SELECT change_id
INTO l_id
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_org_id;
SELECT 'x'
FROM eng_revised_items
WHERE change_notice = p_ECO_rec.ECO_Name
AND organization_id = p_Unexp_ECO_rec.organization_id;
SELECT 'x'
FROM eng_revised_items
WHERE change_notice = p_ECO_rec.ECO_Name
AND organization_id = p_Unexp_ECO_rec.organization_id
AND status_type = 4;
SELECT menu_name FROM fnd_menus
WHERE menu_id = p_role_id;
SELECT 'x'
FROM eng_change_lines
WHERE change_id = p_change_id;
SELECT 'x'
FROM ENG_CHANGE_ORDER_REVISIONS
WHERE change_notice = p_ECO_rec.ECO_Name
AND organization_id = p_Unexp_ECO_rec.organization_id;
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
l_ri_exists = 1
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_CANNOT_DELETE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
l_cl_exists =1
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_CANNOT_DELETE_CL'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_DELETE AND
l_er_exists =1
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_CANNOT_DELETE_ER'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
THEN
IF NOT Check_Ref_Rev_Comp_For_ECO( p_eco_name => p_eco_rec.ECO_Name
, p_organization_id => p_unexp_ECO_rec.organization_id
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ECO_CANNOT_CNCL_FOR_REV_OP'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_ECO_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE AND
NVL(p_Unexp_ECO_rec.approval_list_id, 0) <> NVL(p_old_Unexp_ECO_rec.approval_list_id, 0))
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_APP_LIST_MUST_NOT_CHANGE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_ECO_rec.Transaction_Type = ENG_GLOBALS.G_OPR_UPDATE AND
p_Unexp_ECO_rec.status_type <> p_old_Unexp_ECO_rec.status_type)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_STAT_TYPE_MUST_NOT_CHANGE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
(p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
p_Unexp_ECO_rec.approval_status_type <> p_old_Unexp_ECO_rec.approval_status_type))
AND
p_Unexp_ECO_rec.approval_status_type IN (2,3,4,5,7)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl(2).token_name := 'APPROVAL_STATUS_TYPE';
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
p_Unexp_ECO_rec.status_type <> p_old_Unexp_ECO_rec.status_type AND
p_Unexp_ECO_rec.status_type = 4
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_PROC_CANNOT_SCHEDULE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE AND
NVL(p_ECO_rec.priority_code, 'NONE') <> NVL(p_old_ECO_rec.priority_code, 'NONE') AND
(p_old_Unexp_ECO_rec.status_type = 4
OR l_ri_sched_exists = 1)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
l_token_tbl(1).token_value := 'ECO_NAME';
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
AND
NVL(p_Unexp_ECO_rec.change_order_type_id, 0) <> p_old_Unexp_ECO_rec.change_order_type_id
THEN
Compatible_Change_Order_Type
( p_new_change_order_type_id => p_Unexp_ECO_rec.change_order_type_id
, p_change_notice => p_ECO_rec.ECO_Name
, p_organization_id => p_Unexp_ECO_rec.organization_id
, x_change_order_type_same => l_change_order_type_same
, x_err_text => x_err_text
);
IF p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
-- AND p_ECO_rec.approval_status_type = 5
AND ( --NVL(p_ECO_rec.hierarchy_flag, 2) <> NVL(p_old_ECO_rec.hierarchy_flag,2) OR
NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR) <>
NVL(p_old_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_HIERARCHY_MUST_NOT_CHANGE'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
OR (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
AND NVL(p_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR) <>
NVL(p_old_ECO_rec.organization_hierarchy, FND_API.G_MISS_CHAR)
)
)
AND NOT Val_Org_Hierarchy( p_org_hierarchy => p_ECO_rec.organization_hierarchy
, p_org_id => p_Unexp_ECO_rec.organization_id )
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_ORG_HIERARCHY_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
IF (p_ECO_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
AND NVL(p_Unexp_ECO_rec.Change_Mgmt_Type_Code, FND_API.G_MISS_CHAR) <>
NVL(p_old_Unexp_ECO_rec.Change_Mgmt_Type_Code, FND_API.G_MISS_CHAR)
)
THEN
IF Bom_Globals.Get_Debug = 'Y' THEN
Error_Handler.Write_Debug('Validation that Change Mgmt Type cannot be chagned . . . ' );
SELECT requestor_role_id, assignee_role_id
INTO l_requestor_role_id, l_assignee_role_id
FROM eng_change_order_types
WHERE change_order_type_id = p_Unexp_ECO_Rec.change_order_type_id;
SELECT status_code ,sequence_number , name
FROM eng_change_lines_vl
WHERE eng_change_lines_vl.change_id = p_change_id
and sequence_number<> -1;
SELECT STATUS_TYPE ,scheduled_date
FROM eng_revised_items
WHERE eng_revised_items.change_id = p_change_id;
SELECT status_code
FROM eng_lifecycle_statuses
where ENTITY_NAME='CHANGE_TYPE'
and entity_id1 = p_change_order_type_id;
SELECT priority_code
FROM eng_change_type_priorities
where change_type_id = p_change_order_type_id;
SELECT reason_code
FROM eng_change_type_reasons
where change_type_id = p_change_order_type_id;
SELECT base_change_mgmt_type_code into l_base_change_mgmt_type_code from eng_change_order_types where change_order_type_id = p_Unexp_ECO_rec.Change_Order_Type_Id;
IF p_ECO_rec.transaction_type = 'UPDATE' and
( p_Unexp_ECO_rec.status_type <> 1 AND p_Unexp_ECO_rec.status_type <> 4 AND p_Unexp_ECO_rec.status_type <> 11
and p_Unexp_ECO_rec.status_type <> 5 and
p_Unexp_ECO_rec.status_type <> 7 ) --- Added for Bug 3108743
THEN
l_token_tbl(1).token_name := 'STATUS_TYPE';
and upper(l_change_line_rec.transaction_type) = 'UPDATE'
)
then
l_cl_cico_count :=l_cl_cico_count +1;
if (UPPER(p_ECO_rec.transaction_type) = 'UPDATE') then
l_change_id := Get_Change_Id(p_ECO_rec.eco_name, p_Unexp_ECO_rec.Organization_Id);
l_up_ch :=1; --we need not check in eng_change_lines as it being updated now
l_up_cr :=1; --we need not check in eng_change_lines as it being updated now
end if; --UPPER(p_ECO_rec.transaction_type) = 'UPDATE'
/* User may not set null in Update,
-- because hierarchy_flag does not exist interface table,
-- Hence following logic is commented out.
-- Set 2:No to hierarchy_flag in Entity Defaulting
-- when hierarchy_flag = FND_API.G_MISS_NUM
--
IF p_ECO_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
THEN
IF p_ECO_rec.hierarchy_flag = FND_API.G_MISS_NUM
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'ENG_HIERARCHY_FLAG_MISSING'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, p_Token_Tbl => l_Token_Tbl
);
* is performing an Update or Delete.
* If Update or Delete the procedure will also return the old
* database record.
*****************************************************************************/
PROCEDURE Check_Existence
( p_change_notice IN VARCHAR2
, p_organization_id IN NUMBER
, p_organization_code IN VARCHAR2
, p_calling_entity IN VARCHAR2
, p_transaction_type IN VARCHAR2
, x_eco_rec OUT NOCOPY Eng_Eco_Pub.Eco_Rec_Type
, x_eco_unexp_rec OUT NOCOPY Eng_Eco_Pub.Eco_Unexposed_Rec_Type
, x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
, x_Return_Status OUT NOCOPY VARCHAR2
)
IS
l_Mesg_token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
p_transaction_type = Eng_Globals.G_OPR_UPDATE AND
x_eco_unexp_rec.approval_status_type in (3, 5) -- approved or approval requested
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
( p_Message_Name => 'ENG_ECO_CANNOT_UPDATE'
, p_Mesg_Token_Tbl => l_mesg_token_tbl
, x_Mesg_Token_Tbl => l_mesg_token_tbl
, p_Token_Tbl => l_token_tbl
);
( Eng_Globals.G_OPR_UPDATE, Eng_Globals.G_OPR_DELETE)
THEN
l_return_status := FND_API.G_RET_STS_ERROR;
SELECT process_name
FROM eng_change_type_processes
WHERE change_order_type_id = p_change_order_type_id
AND NVL(eng_change_priority_code,'X') = NVL(p_priority_code, 'X');
SELECT route_id
FROM eng_engineering_changes
WHERE change_id = p_change_id ;
--added status_code in select stmt for validation
CURSOR c_CheckECO IS
SELECT status_type, priority_code, change_order_type_id,status_code --bug no 3591968 by Rashmi
,approval_status_type,assignee_id ,change_id --PLM records we will only have to look for processes based on change_type_id
, nvl(plm_or_erp_change, 'PLM') plm_or_erp_change
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
select status_code
from eng_lifecycle_statuses
where entity_id1 = cp_change_id
and status_code =cp_status_code
and entity_name ='ENG_CHANGE'
and active_flag='Y'
and change_wf_route_id is not null
and workflow_status = 'IN_PROGRESS';*/
select els.status_code, els.workflow_status, els.change_wf_route_id,
ecs.status_type orig_status_type, els.CHANGE_EDITABLE_FLAG ,
ecs.status_name
from eng_lifecycle_statuses els, eng_change_statuses_vl ecs
where els.ENTITY_NAME = 'ENG_CHANGE'
and els.ENTITY_ID1 = cp_change_id
and els.STATUS_CODE = cp_STATUS_CODE
and els.active_flag = 'Y'
and els.STATUS_CODE = ecs.STATUS_CODE;
l_update_allowed BOOLEAN;
l_update_allowed := TRUE;
l_update_allowed := FALSE;
SELECT assembly_type
INTO l_change_order_assembly_Type
FROM eng_change_order_types
WHERE change_order_type_id =
l_change_order_type_id;
ELSIF nvl(l_update_allowed, TRUE) = FALSE
THEN
l_Token_Tbl(2).token_name := 'STATUS_NAME';