[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_index_of_batch_to_update NUMBER;
l_index_of_batch_to_update := Find_Oldest_AG_Batch_Index();
IF (l_index_of_batch_to_update = 1) THEN
Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
,TRUE
,G_AG_METADATA_BATCH_1);
ELSIF (l_index_of_batch_to_update = 2) THEN
Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
,TRUE
,G_AG_METADATA_BATCH_2);
ELSIF (l_index_of_batch_to_update = 3) THEN
Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
,TRUE
,G_AG_METADATA_BATCH_3);
ELSIF (l_index_of_batch_to_update = 4) THEN
Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
,TRUE
,G_AG_METADATA_BATCH_4);
ELSIF (l_index_of_batch_to_update = 5) THEN
Add_Attr_Group_To_Batch(p_attr_group_metadata_obj
,TRUE
,G_AG_METADATA_BATCH_5);
px_attr_metadata_obj.INT_TO_DISP_VAL_QUERY := 'SELECT DISTINCT DISPLAY_NAME '||
'FROM EGO_VALUE_SET_VALUES_V '||
'WHERE VALUE_SET_ID = '||p_value_set_id||
' AND ENABLED_CODE = ''Y'' '||
'AND (NVL(START_DATE, SYSDATE - 1) < SYSDATE) '||
'AND (NVL(END_DATE, SYSDATE + 1) > SYSDATE) '||
'AND INTERNAL_NAME = ';
SELECT APPLICATION_TABLE_NAME
,ID_COLUMN_NAME
,VALUE_COLUMN_NAME
,ADDITIONAL_WHERE_CLAUSE
INTO l_validation_table_info_row.APPLICATION_TABLE_NAME
,l_validation_table_info_row.ID_COLUMN_NAME
,l_validation_table_info_row.VALUE_COLUMN_NAME
,l_validation_table_info_row.ADDITIONAL_WHERE_CLAUSE
FROM FND_FLEX_VALIDATION_TABLES
WHERE FLEX_VALUE_SET_ID = p_value_set_id;
SELECT REPLACE(l_where_clause,FND_GLOBAL.LOCAL_CHR(10),FND_GLOBAL.LOCAL_CHR(32)) INTO l_where_clause FROM dual; --replacing new line character
SELECT REPLACE(l_where_clause,FND_GLOBAL.LOCAL_CHR(13),FND_GLOBAL.LOCAL_CHR(32)) INTO l_where_clause FROM dual; --removing carriage return
l_where_clause := ' AND ' || l_where_clause ; --BugFix: 4101266 we need to have a wrapper select statement on top of
px_attr_metadata_obj.DISP_TO_INT_VAL_QUERY := 'SELECT DISTINCT '||l_column_name ||
' FROM '||l_validation_table_info_row.APPLICATION_TABLE_NAME||
' WHERE 1=1 '||l_where_clause||' AND '||
l_validation_table_info_row.VALUE_COLUMN_NAME ||' = ';
px_attr_metadata_obj.INT_TO_DISP_VAL_QUERY := 'SELECT DISTINCT '|| l_validation_table_info_row.VALUE_COLUMN_NAME||
' FROM '||l_validation_table_info_row.APPLICATION_TABLE_NAME||
' WHERE 1=1 '||l_where_clause||' AND '||
l_column_name||' = ';
SELECT EXT.ATTR_ID,
FLX_EXT.ATTR_GROUP_ID,
A.END_USER_COLUMN_NAME,
TL.FORM_LEFT_PROMPT,
EXT.DATA_TYPE,
FC.MEANING DATA_TYPE_MEANING,
A.COLUMN_SEQ_NUM,
EXT.UNIQUE_KEY_FLAG,
A.DEFAULT_VALUE,
EXT.INFO_1,
VS.MAXIMUM_SIZE,
A.REQUIRED_FLAG,
A.APPLICATION_COLUMN_NAME,
VS.FLEX_VALUE_SET_ID,
VS.VALIDATION_TYPE,
VS.MINIMUM_VALUE,
VS.MAXIMUM_VALUE,
EXT.UOM_CLASS,
UOM.UOM_CODE,
EXT.VIEW_IN_HIERARCHY_CODE,
EXT.EDIT_IN_HIERARCHY_CODE
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT,
FND_DESCR_FLEX_COLUMN_USAGES A,
FND_DESCR_FLEX_COL_USAGE_TL TL,
EGO_FND_DF_COL_USGS_EXT EXT,
EGO_VS_FORMAT_CODES_V FC,
FND_FLEX_VALUE_SETS VS,
MTL_UNITS_OF_MEASURE UOM
WHERE FLX_EXT.APPLICATION_ID = cp_application_id
AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
AND A.APPLICATION_ID = cp_application_id
AND A.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND A.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
AND TL.APPLICATION_ID = cp_application_id
AND TL.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
AND EXT.APPLICATION_ID = cp_application_id
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
AND FC.LOOKUP_CODE(+) = EXT.DATA_TYPE
AND A.ENABLED_FLAG = 'Y'
AND TL.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
AND TL.LANGUAGE = USERENV('LANG')
AND EXT.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
AND A.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID (+)
AND UOM.UOM_CLASS(+) = EXT.UOM_CLASS
AND UOM.BASE_UOM_FLAG(+) = 'Y'
ORDER BY A.COLUMN_SEQ_NUM;
SELECT EXT.ATTR_GROUP_ID
,FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_NAME
,EXT.AGV_NAME
,EXT.MULTI_ROW
,VPF.FUNCTION_NAME
,EPF.FUNCTION_NAME
,FLX.APPLICATION_TABLE_NAME
,FLX_EXT.APPLICATION_TL_TABLE_NAME
,FLX_EXT.APPLICATION_VL_NAME
,FLX_EXT.HIERARCHY_NODE_QUERY
,FLX_EXT.HIERARCHY_PROPAGATION_API
,EXT.VARIANT
INTO l_attr_group_metadata_obj.ATTR_GROUP_ID
,l_attr_group_metadata_obj.ATTR_GROUP_DISP_NAME
,l_attr_group_metadata_obj.AGV_NAME
,l_attr_group_metadata_obj.MULTI_ROW_CODE
,l_attr_group_metadata_obj.VIEW_PRIVILEGE
,l_attr_group_metadata_obj.EDIT_PRIVILEGE
,l_attr_group_metadata_obj.EXT_TABLE_B_NAME
,l_attr_group_metadata_obj.EXT_TABLE_TL_NAME
,l_attr_group_metadata_obj.EXT_TABLE_VL_NAME
,l_attr_group_metadata_obj.HIERARCHY_NODE_QUERY
,l_attr_group_metadata_obj.HIERARCHY_PROPAGATION_API
,l_attr_group_metadata_obj.VARIANT
FROM EGO_FND_DSC_FLX_CTX_EXT EXT
,FND_DESCRIPTIVE_FLEXS FLX
,FND_FORM_FUNCTIONS VPF
,FND_FORM_FUNCTIONS EPF
,FND_DESCR_FLEX_CONTEXTS_TL FLX_TL
,EGO_FND_DESC_FLEXS_EXT FLX_EXT
WHERE EXT.APPLICATION_ID = p_application_id
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND VPF.FUNCTION_ID(+) = EXT.VIEW_PRIVILEGE_ID
AND EPF.FUNCTION_ID(+) = EXT.EDIT_PRIVILEGE_ID
AND FLX_TL.APPLICATION_ID = EXT.APPLICATION_ID
AND FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FLX_TL.LANGUAGE = USERENV('LANG')
AND FLX.APPLICATION_ID = EXT.APPLICATION_ID
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND FLX_EXT.APPLICATION_ID(+) = EXT.APPLICATION_ID
AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+) = EXT.DESCRIPTIVE_FLEXFIELD_NAME;
SELECT EXT.APPLICATION_ID
,EXT.DESCRIPTIVE_FLEXFIELD_NAME
,EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE
,FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_NAME
,EXT.AGV_NAME
,EXT.MULTI_ROW
,VPF.FUNCTION_NAME
,EPF.FUNCTION_NAME
,FLX.APPLICATION_TABLE_NAME
,FLX_EXT.APPLICATION_TL_TABLE_NAME
,FLX_EXT.APPLICATION_VL_NAME
,FLX_EXT.HIERARCHY_NODE_QUERY
,FLX_EXT.HIERARCHY_PROPAGATION_API
,EXT.VARIANT
INTO l_attr_group_metadata_obj.APPLICATION_ID
,l_attr_group_metadata_obj.ATTR_GROUP_TYPE
,l_attr_group_metadata_obj.ATTR_GROUP_NAME
,l_attr_group_metadata_obj.ATTR_GROUP_DISP_NAME
,l_attr_group_metadata_obj.AGV_NAME
,l_attr_group_metadata_obj.MULTI_ROW_CODE
,l_attr_group_metadata_obj.VIEW_PRIVILEGE
,l_attr_group_metadata_obj.EDIT_PRIVILEGE
,l_attr_group_metadata_obj.EXT_TABLE_B_NAME
,l_attr_group_metadata_obj.EXT_TABLE_TL_NAME
,l_attr_group_metadata_obj.EXT_TABLE_VL_NAME
,l_attr_group_metadata_obj.HIERARCHY_NODE_QUERY
,l_attr_group_metadata_obj.HIERARCHY_PROPAGATION_API
,l_attr_group_metadata_obj.VARIANT
FROM EGO_FND_DSC_FLX_CTX_EXT EXT
,FND_FORM_FUNCTIONS VPF
,FND_FORM_FUNCTIONS EPF
,FND_DESCR_FLEX_CONTEXTS_TL FLX_TL
,FND_DESCRIPTIVE_FLEXS FLX
,EGO_FND_DESC_FLEXS_EXT FLX_EXT
WHERE EXT.ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID
AND VPF.FUNCTION_ID(+) = EXT.VIEW_PRIVILEGE_ID
AND EPF.FUNCTION_ID(+) = EXT.EDIT_PRIVILEGE_ID
AND EXT.APPLICATION_ID = FLX_TL.APPLICATION_ID
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FLX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FLX_TL.LANGUAGE = USERENV('LANG')
AND FLX.APPLICATION_ID = FLX_TL.APPLICATION_ID
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME
AND FLX_EXT.APPLICATION_ID(+) = FLX_TL.APPLICATION_ID
AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+) = FLX_TL.DESCRIPTIVE_FLEXFIELD_NAME;
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO l_attr_group_metadata_obj.ATTR_GROUP_ID_FLAG
FROM FND_COLUMNS
WHERE COLUMN_NAME = 'ATTR_GROUP_ID'
AND APPLICATION_ID = l_attr_group_metadata_obj.APPLICATION_ID
AND TABLE_ID = (SELECT TABLE_ID
FROM FND_TABLES
WHERE TABLE_NAME = l_attr_group_metadata_obj.EXT_TABLE_B_NAME
AND APPLICATION_ID = l_attr_group_metadata_obj.APPLICATION_ID);
SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
AND ATTRIBUTE1 = cp_obj_name
AND VIEW_APPLICATION_ID = 0
ORDER BY ATTRIBUTE2;
SELECT O.OBJ_NAME
,O.PK1_COLUMN_NAME
,O.PK2_COLUMN_NAME
,O.PK3_COLUMN_NAME
,O.PK4_COLUMN_NAME
,O.PK5_COLUMN_NAME
,O.PK1_COLUMN_TYPE
,O.PK2_COLUMN_TYPE
,O.PK3_COLUMN_TYPE
,O.PK4_COLUMN_TYPE
,O.PK5_COLUMN_TYPE
,E.CLASSIFICATION_COL_NAME
,E.CLASSIFICATION_COL_TYPE
,L.ATTRIBUTE2
,L.ATTRIBUTE3
,L.ATTRIBUTE4
,L.ATTRIBUTE5
,L.ATTRIBUTE6
,L.ATTRIBUTE7
,L.ATTRIBUTE8
INTO l_obj_name
,l_pk1_obj.col_name
,l_pk2_obj.col_name
,l_pk3_obj.col_name
,l_pk4_obj.col_name
,l_pk5_obj.col_name
,l_pk1_obj.data_type
,l_pk2_obj.data_type
,l_pk3_obj.data_type
,l_pk4_obj.data_type
,l_pk5_obj.data_type
,l_class_code_obj.col_name
,l_class_code_obj.data_type
,l_num_data_level_cols
,l_data_level1_obj.col_name
,l_data_level1_obj.data_type
,l_data_level2_obj.col_name
,l_data_level2_obj.data_type
,l_data_level3_obj.col_name
,l_data_level3_obj.data_type
FROM FND_OBJECTS O
,EGO_FND_OBJECTS_EXT E
,FND_LOOKUP_VALUES L
WHERE O.OBJECT_ID = p_object_id
AND O.OBJ_NAME = E.OBJECT_NAME
AND L.LOOKUP_TYPE(+) = 'EGO_EF_DATA_LEVEL'
AND L.ATTRIBUTE1(+) = O.OBJ_NAME
AND L.ATTRIBUTE2(+) > 0
AND L.LANGUAGE(+) = USERENV('LANG');
SELECT DATA_LEVEL_ID
,DATA_LEVEL_NAME
,USER_DATA_LEVEL_NAME
,PK1_COLUMN_NAME
,PK2_COLUMN_NAME
,PK3_COLUMN_NAME
,PK4_COLUMN_NAME
,PK5_COLUMN_NAME
,PK1_COLUMN_TYPE
,PK2_COLUMN_TYPE
,PK3_COLUMN_TYPE
,PK4_COLUMN_TYPE
,PK5_COLUMN_TYPE
INTO l_data_level_mdata.DATA_LEVEL_ID
,l_data_level_mdata.DATA_LEVEL_NAME
,l_data_level_mdata.USER_DATA_LEVEL_NAME
,l_data_level_mdata.PK_COLUMN_NAME1
,l_data_level_mdata.PK_COLUMN_NAME2
,l_data_level_mdata.PK_COLUMN_NAME3
,l_data_level_mdata.PK_COLUMN_NAME4
,l_data_level_mdata.PK_COLUMN_NAME5
,l_data_level_mdata.PK_COLUMN_TYPE1
,l_data_level_mdata.PK_COLUMN_TYPE2
,l_data_level_mdata.PK_COLUMN_TYPE3
,l_data_level_mdata.PK_COLUMN_TYPE4
,l_data_level_mdata.PK_COLUMN_TYPE5
FROM EGO_DATA_LEVEL_VL
WHERE DATA_LEVEL_ID = p_data_level_id;
SELECT DATA_LEVEL_ID, VIEW_PRIVILEGE_ID,EDIT_PRIVILEGE_ID,RAISE_PRE_EVENT,RAISE_POST_EVENT, DEFAULTING
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = p_attr_group_id;
SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_B
WHERE APPLICATION_ID = p_app_id
AND ATTR_GROUP_TYPE = p_ag_type;
SELECT DATA_LEVEL_ID
FROM EGO_DATA_LEVEL_VL
WHERE APPLICATION_ID = p_application_id
AND ATTR_GROUP_TYPE = p_attr_Group_type;
SELECT DISTINCT pk1_column_name column_name
FROM ego_data_level_b
WHERE application_id=cp_application_id
AND attr_group_type = cp_attr_group_type
AND pk1_column_name IS NOT NULL
UNION
SELECT DISTINCT pk2_column_name column_name
FROM ego_data_level_b
WHERE application_id=cp_application_id
AND attr_group_type = cp_attr_group_type
AND pk2_column_name IS NOT NULL
UNION
SELECT DISTINCT pk3_column_name column_name
FROM ego_data_level_b
WHERE application_id=cp_application_id
AND attr_group_type = cp_attr_group_type
AND pk3_column_name IS NOT NULL
UNION
SELECT DISTINCT pk4_column_name column_name
FROM ego_data_level_b
WHERE application_id=cp_application_id
AND attr_group_type = cp_attr_group_type
AND pk4_column_name IS NOT NULL
UNION
SELECT DISTINCT pk5_column_name column_name
FROM ego_data_level_b
WHERE application_id=cp_application_id
AND attr_group_type = cp_attr_group_type
AND pk5_column_name IS NOT NULL;
SELECT 1
INTO l_dummy_number
FROM SYS.all_tab_columns
WHERE table_name = p_table_name
AND column_name = p_column_name;