[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_CHECK_ON_INSERT_CACHE Constraint_Cache_TBL_Type;
SELECT INSTR(l_value_string,'''',1,from_char)
INTO found_char
FROM DUAL;
SELECT vc.column_name, attr.data_type, vc.validation_op, vc.value_string
, decode(attr.state_attribute_flag,NULL,1,'Y',0,'N',1) state_attribute
FROM oe_pc_vtmplt_cols vc,
oe_pc_vtmplts vt,
oe_pc_attributes_v attr
WHERE vc.validation_tmplt_id = p_validation_tmplt_id
AND vc.validation_tmplt_id = vt.validation_tmplt_id
AND attr.entity_id = vt.entity_id
AND attr.column_name = vc.column_name
ORDER BY state_attribute;
SELECT application_id, db_object_name, db_object_type,
wf_item_type, itemkey_column1, itemkey_column2,
itemkey_column3,itemkey_column4, itemkey_delimiter
FROM OE_PC_ENTITIES_V
where entity_id = p_validation_entity_id;
SELECT e.application_id, e.db_object_name, e.db_object_type,
wf.itemkey_column1, wf.itemkey_column2,
wf.itemkey_column3,wf.itemkey_column4, wf.itemkey_delimiter
FROM OE_PC_ENTITIES_V e, OE_AK_OBJ_WF_ITEMS wf
where e.entity_id = p_validation_entity_id
and wf.database_object_name(+) = e.db_object_name
and (l_wf_item_type IS NULL
or wf.item_type = l_wf_item_type);
SELECT application_id, db_object_name, db_object_type
FROM OE_PC_ENTITIES_V
where entity_id = p_entity_id;
SELECT validation_type, activity_name, activity_status_code, activity_result_code, wf_item_type
FROM oe_pc_vtmplts
WHERE validation_tmplt_id = p_validation_tmplt_id
and (validation_type = 'WF'
OR validation_type = 'TBL');
SELECT vc.column_name, attr.data_type, vc.validation_op, vc.value_string
, decode(attr.state_attribute_flag,NULL,1,'Y',0,'N',1) state_attribute
FROM oe_pc_vtmplt_cols vc,
oe_pc_vtmplts vt,
oe_pc_attributes_v attr
WHERE vc.validation_tmplt_id = p_validation_tmplt_id
AND vc.validation_tmplt_id = vt.validation_tmplt_id
AND attr.entity_id = vt.entity_id
AND attr.column_name = vc.column_name
ORDER BY state_attribute;
SELECT pk_record_set_flag
FROM oe_pc_rsets
WHERE record_set_id = p_record_set_id;
SELECT column_name
FROM oe_pc_rset_sel_cols
WHERE record_set_id = p_record_set_id;
SELECT uk_column_name pk_column_name
FROM oe_pc_ukey_cols_v
WHERE application_id = cp_application_id
AND db_object_name = cp_db_object_name
ANd db_object_type = cp_db_object_type
AND primary_key_flag = 'Y'
AND uk_column_sequence <= 5
ORDER BY uk_column_sequence;
SELECT fk_column_name, uk_column_name
FROM oe_pc_fkey_cols_v
WHERE application_id = cp_fk_application_id
AND db_object_name = cp_fk_db_object_name
AND db_object_type = cp_db_object_type
AND uk_application_id = cp_uk_application_id
AND uk_db_object_name = cp_uk_db_object_name
ORDER BY fk_column_sequence;
IS SELECT 'Y'
FROM sys.dual
WHERE EXISTS (SELECT 'EXISTS'
FROM oe_pc_rentities_v re
WHERE entity_id = p_entity_id
AND related_entity_id = p_validation_entity_id);
l_vc_sql := l_vc_sql || ' ( SELECT ' || l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
-- then it will be selected first because cursor C_VTMPLTCOLS
-- orders by state_attribute
-- If state attribute THEN FROM cursor selects from the database object
IF val_rec.state_attribute = 0 THEN
l_vc_sql := ' FROM '|| l_validation_db_object_name ||
' a ' || OE_PC_GLOBALS.NEWLINE;
-- on this validation template, hence select FROM SYS.DUAL
ELSE
l_condn_logic_only := TRUE;
l_vc_sql := l_vc_sql || ' ( SELECT ' || l_rs_pk_list || OE_PC_GLOBALS.NEWLINE;
l_vc_sql := l_vc_sql || ' ( SELECT '|| l_concatenated_itemkey_columns || OE_PC_GLOBALS.NEWLINE;
l_vc_sql := l_vc_sql || ' AND ('||l_vc_pk_list||') IN ( SELECT '||
l_rs_pk_list|| OE_PC_GLOBALS.NEWLINE;
' SELECT count(*) ' || OE_PC_GLOBALS.NEWLINE || l_vc_sql || ';';
' SELECT count(*) ' || OE_PC_GLOBALS.NEWLINE || l_rs_sql || ';';
SELECT 'Y'
FROM OE_PC_VALIDATION_PKGS
WHERE validating_entity_id = p_entity_id
AND validation_entity_id = p_validation_entity_id
AND validation_tmplt_id = p_validation_tmplt_id
AND record_set_id = p_record_set_id;
l_update varchar2(1) := 'N';
Fetch C into l_update;
if (l_update = 'Y') then
-- make update statement
l_sql := 'UPDATE OE_PC_VALIDATION_PKGS SET last_update_date = sysdate ' || OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || ' AND EXISTS (SELECT 1 '|| OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || 'INSERT INTO OE_PC_VALIDATION_PKGS ';
l_sql := l_sql || ' ,last_updated_by ' || OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || ' ,last_update_date ' || OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || ' ,last_update_login ' || OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || 'SELECT ' || OE_PC_GLOBALS.NEWLINE;
l_sql := l_sql || 'WHERE EXISTS (SELECT ''EXISTS'''|| OE_PC_GLOBALS.NEWLINE;
PROCEDURE Add_To_Check_On_Insert_Cache
(p_entity_id IN NUMBER
,p_responsibility_id IN NUMBER
,p_application_id IN NUMBER --added for bug3631547
)
IS
l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
CURSOR C_CHECK_ON_INSERT_OP
IS
SELECT
c.constraint_id, c.entity_id
,c.on_operation_action, c.column_name
FROM oe_pc_constraints c
WHERE c.entity_id = P_ENTITY_ID
AND c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
AND c.check_on_insert_flag = 'Y'
AND nvl(c.enabled_flag, 'Y') = 'Y'
AND EXISTS (
SELECT 'EXISTS'
FROM OE_PC_ASSIGNMENTS A
WHERE a.constraint_id = c.constraint_id
AND ( a.responsibility_id = p_responsibility_id
OR a.responsibility_id IS NULL)
AND ( a.application_id = p_application_id
OR a.application_id IS NULL )
AND NOT EXISTS (
SELECT 'EXISTS'
FROM OE_PC_EXCLUSIONS e
WHERE e.responsibility_id = p_responsibility_id
AND e.assignment_id = a.assignment_id
AND e.application_id = p_application_id
)
)
ORDER BY c.column_name, c.on_operation_action;
IF G_Check_On_Insert_Cache.EXISTS(l_index) THEN
RETURN;
OPEN c_check_on_insert_op;
FETCH c_check_on_insert_op BULK COLLECT INTO t_constraint_ids,
t_entity_ids,
t_on_operation_actions,
t_column_names;
CLOSE c_check_on_insert_op;
oe_debug_pub.add( 'CHECK ON INSERT-ADDTOCACHE , COLUMN:' ||T_COLUMN_NAMES(i) ) ;
G_Check_On_Insert_Cache(l_index).entity_id := p_entity_id;
G_Check_On_Insert_Cache(l_index).column_name := t_column_names(i);
G_Check_On_Insert_Cache(l_index).constraint_id := t_constraint_ids(i);
G_Check_On_Insert_Cache(l_index).on_operation_action := t_on_operation_actions(i);
G_Check_On_Insert_Cache(l_index).column_name := FND_API.G_MISS_CHAR;
END Add_To_Check_On_Insert_Cache;
FUNCTION Check_On_Insert_Exists
(p_entity_id IN NUMBER
,p_responsibility_id IN NUMBER
,p_application_id IN NUMBER --added for bug3631547
)
RETURN BOOLEAN IS
l_index PLS_INTEGER; -- for bug 6473618 NUMBER;
Add_To_Check_On_Insert_Cache(p_entity_id => p_entity_id
,p_responsibility_id => p_responsibility_id
,p_application_id => p_application_id);
IF G_Check_On_Insert_Cache(l_index).column_name <> FND_API.G_MISS_CHAR THEN
RETURN TRUE;
, 'Check_On_Insert_Exists'
);
END Check_On_Insert_Exists;
IS SELECT
c.application_id,
c.entity_short_name,
c.condition_id,
c.group_number,
c.modifier_flag,
c.validation_application_id,
c.validation_entity_short_name,
c.validation_tmplt_short_name,
c.record_set_short_name,
c.scope_op,
c.validation_pkg,
c.validation_proc,
c.validation_tmplt_id,
c.record_set_id,
c.entity_id,
c.validation_entity_id
FROM oe_pc_conditions_v c
WHERE constraint_id = p_constraint_id
AND nvl(enabled_flag, 'Y') = 'Y'
ORDER BY c.group_number;
G_Result_Cache.DELETE;
G_Result_Cache.DELETE(l_index);
SELECT
c.constraint_id, c.entity_id
,c.on_operation_action, c.column_name
FROM oe_pc_constraints c
WHERE c.entity_id = P_ENTITY_ID
AND c.constrained_operation = OE_PC_GLOBALS.CREATE_OP
AND EXISTS (
SELECT 'EXISTS'
FROM OE_PC_ASSIGNMENTS A
WHERE a.constraint_id = c.constraint_id
AND ( a.responsibility_id = p_responsibility_id
OR a.responsibility_id IS NULL)
AND ( a.application_id = p_application_id
OR a.application_id IS NULL)
AND NOT EXISTS (
SELECT 'EXISTS'
FROM OE_PC_EXCLUSIONS e
WHERE e.responsibility_id = p_responsibility_id
AND e.assignment_id = a.assignment_id
AND e.application_id = p_application_id
)
)
AND nvl(c.enabled_flag, 'Y') = 'Y'
AND ((p_qualifier_attribute IS NULL)
OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
ORDER BY c.on_operation_action;
CURSOR C_UPDATE_OP
IS
SELECT
c.constraint_id, c.entity_id
,c.on_operation_action, c.column_name
FROM oe_pc_constraints c
WHERE c.entity_id = P_ENTITY_ID
AND c.constrained_operation = OE_PC_GLOBALS.UPDATE_OP
-- if p_column_name is NULL then check only for constraints with NULL column
-- name
-- if check_all_cols_constraint = 'N', then check for constraint with
-- column_name = p_column_name (do not check for NULL column_name) but
-- if check_all_cols_constraint = 'Y', then check for constraint with
-- column_name = p_column_name or NULL column_name.
AND ( (c.column_name is null
AND p_column_name is null)
OR (p_check_all_cols_constraint = 'N'
AND c.column_name = p_column_name)
OR (p_check_all_cols_constraint = 'Y'
AND (c.column_name = p_column_name OR c.column_name is null))
)
-- if caller is defaulting then DO NOT CHECK those constraints
-- that have honored_by_def_flag = 'N'
AND decode(honored_by_def_flag,'N',decode(p_is_caller_defaulting,'Y','N','Y'),
nvl(honored_by_def_flag,'Y')) = 'Y'
AND EXISTS (
SELECT 'EXISTS'
FROM OE_PC_ASSIGNMENTS A
WHERE a.constraint_id = c.constraint_id
AND ( a.responsibility_id = p_responsibility_id
OR a.responsibility_id IS NULL)
AND ( a.application_id =p_application_id --added for bug3631547
OR a.application_id IS NULL )
AND NOT EXISTS (
SELECT 'EXISTS'
FROM OE_PC_EXCLUSIONS e
WHERE e.responsibility_id = p_responsibility_id
AND e.assignment_id = a.assignment_id
AND e.application_id = p_application_id
)
)
AND nvl(c.enabled_flag, 'Y') = 'Y'
AND ((p_qualifier_attribute IS NULL)
OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
ORDER BY c.on_operation_action;
SELECT DISTINCT
c.constraint_id, c.entity_id
,c.on_operation_action, c.column_name
FROM oe_pc_constraints c,
oe_pc_assignments a
WHERE (a.responsibility_id = p_responsibility_id OR a.responsibility_id IS NULL)
AND a.constraint_id = c.constraint_id
AND c.entity_id = P_ENTITY_ID
AND c.constrained_operation = p_operation
AND (a.application_id = p_application_id OR a.application_id IS NULL) --added for bug3631547
AND NOT EXISTS (
SELECT 'EXISTS'
FROM OE_PC_EXCLUSIONS e
WHERE e.responsibility_id = p_responsibility_id
AND e.assignment_id = a.assignment_id
AND e.application_id = p_application_id
)
AND nvl(c.enabled_flag, 'Y') = 'Y'
AND ((p_qualifier_attribute IS NULL)
OR nvl(c.qualifier_attribute, p_qualifier_attribute) = p_qualifier_attribute)
ORDER BY c.on_operation_action;
Add_To_Check_On_Insert_Cache
(p_entity_id => p_entity_id
,p_responsibility_id => p_responsibility_id
,p_application_id => p_application_id); --added for bug3631547
IF (NOT G_Check_On_Insert_Cache.EXISTS(l_index))
OR G_Check_On_Insert_Cache(l_index).column_name = FND_API.G_MISS_CHAR
OR G_Check_On_Insert_Cache(l_index).column_name > p_column_name
THEN
EXIT;
IF G_Check_On_Insert_Cache(l_index).column_name = p_column_name THEN
OE_PC_Constraints_Admin_Pvt.Validate_Constraint (
p_constraint_id
=> G_Check_On_Insert_Cache(l_index).constraint_id
,p_use_cached_results => p_use_cached_results
,x_condition_count => l_condition_count
,x_valid_condition_group => l_valid_condition_group
,x_result => l_validation_result
);
l_column_name := G_Check_On_Insert_Cache(l_index).column_name;
x_constraint_id := G_Check_On_Insert_Cache(l_index).constraint_id;
x_on_operation_action := G_Check_On_Insert_Cache(l_index).on_operation_action;
ELSIF p_operation = OE_PC_GLOBALS.UPDATE_OP THEN
OPEN c_update_op;
FETCH c_update_op BULK COLLECT INTO t_constraint_ids,
t_entity_ids,
t_on_operation_actions,
t_column_names;
CLOSE c_update_op;
SELECT database_object_name
INTO l_db_object_name
FROM oe_ak_objects_ext
WHERE entity_id = p_entity_id;
-- This maybe NULL if update on all columns is constrained
-- (even if p_column_name is not null)
OE_PC_Constraints_Admin_PUB.Add_Constraint_Message
( p_application_id => 660
, p_database_object_name => l_db_object_name
, p_column_name => l_column_name
, p_operation => p_operation
, p_constraint_id => x_constraint_id
, p_group_number => x_constraining_conditions_grp
, p_on_operation_action => x_on_operation_action
);