[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_dummy_number
FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_data_set_id;
FOR cr in (SELECT * FROM ego_itm_usr_attr_intrfc
WHERE data_set_id = p_data_set_id
ORDER BY inventory_item_id, organization_id, data_level_id,
revision_id, pk1_value, pk2_value, attr_group_id,
attr_int_name, row_identifier
) LOOP
code_debug(p_log_level => 0
,p_module => 'write_intf_records'
,p_message => p_count||' - '||cr.inventory_item_id||', '||
cr.organization_id||', '||cr.data_level_id||', '||cr.revision_id||', '||
cr.pk1_value||', '||cr.pk2_value||', '||cr.attr_group_id||', '||
cr.attr_group_int_name||', '||cr.attr_int_name||', '||
cr.attr_value_str||', '||cr.attr_value_num||', '||cr.row_identifier);
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
IF BITAND(error_rec.PROCESS_STATUS,G_PS_BAD_TTYPE_UPDATE) <> 0 THEN
l_err_msg_name := 'EGO_EF_ROW_NOT_FOUND';
l_err_token_table.DELETE();
l_err_token_table.DELETE();
IF BITAND(error_rec.PROCESS_STATUS,G_PS_BAD_TTYPE_DELETE) <> 0 THEN
l_err_msg_name := 'EGO_EF_ROW_NOT_FOUND';
l_err_token_table.DELETE();
l_err_token_table.DELETE();
SELECT EGO_EXT_FWK_PUB.Get_Class_Meaning(p_object_name, error_rec.class_code_value)
INTO l_err_token_table(2).TOKEN_VALUE
FROM DUAL;
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_err_token_table.DELETE();
l_ext_table_select VARCHAR2(32767);
l_intf_tbl_select VARCHAR2(32767);
l_tvs_select VARCHAR2(32767);
l_tvs_num_val_check_select VARCHAR2(32767);
l_tvs_date_val_check_select VARCHAR2(32767);
l_tvs_str_val_check_select VARCHAR2(32767);
SELECT application_short_name
INTO G_APPLICATION_CONTEXT
FROM fnd_application
WHERE application_id = p_application_id;
SELECT NVL(p_entity_code, DECODE(G_ENTITY_ID, NULL, G_APPLICATION_CONTEXT||'_EXTFWK_USER_ATTRS',NULL))
INTO G_ENTITY_CODE
FROM DUAL;
l_err_col_static_sql := ' SELECT process_status, row_identifier, '
||' attr_group_int_name, attr_int_name, attr_value_str, '
||' attr_value_num, attr_value_date, attr_disp_value, '
||' transaction_type, transaction_id, attr_group_id ';
SELECT OBJECT_ID
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT PK1_COLUMN_NAME, PK1_COLUMN_TYPE,
PK2_COLUMN_NAME, PK2_COLUMN_TYPE,
PK3_COLUMN_NAME, PK3_COLUMN_TYPE,
PK4_COLUMN_NAME, PK4_COLUMN_TYPE,
PK5_COLUMN_NAME, PK5_COLUMN_TYPE
INTO l_pk1_column_name, l_pk1_column_type,
l_pk2_column_name, l_pk2_column_type,
l_pk3_column_name, l_pk3_column_type,
l_pk4_column_name, l_pk4_column_type,
l_pk5_column_name, l_pk5_column_type
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT CLASSIFICATION_COL_NAME, CLASSIFICATION_COL_TYPE
INTO l_class_code_column_name, l_class_code_column_type
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = p_object_name;
SELECT FLEX.APPLICATION_TABLE_NAME EXT_TABLE_NAME,
FLEX_EXT.APPLICATION_TL_TABLE_NAME EXT_TL_TABLE_NAME,
FLEX_EXT.APPLICATION_VL_NAME EXT_VL_NAME
INTO l_ext_b_table_name,
l_ext_tl_table_name,
l_ext_vl_name
FROM FND_DESCRIPTIVE_FLEXS FLEX,
EGO_FND_DESC_FLEXS_EXT FLEX_EXT
WHERE FLEX.APPLICATION_ID = FLEX_EXT.APPLICATION_ID(+)
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = FLEX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FLEX.APPLICATION_ID = p_application_id
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
'UPDATE '||p_interface_table_name||' UAI1 '||
' SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID '||
' FROM EGO_ATTR_GROUP_DL '||
' WHERE ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID '||
' ) '||
' WHERE UAI1.DATA_SET_ID = :data_set_id '||
' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||' '||
' AND UAI1.ATTR_GROUP_TYPE = :attr_group_type '||
' AND (SELECT COUNT(*) FROM EGO_ATTR_GROUP_DL '||
' WHERE ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID) < 2 '||
' AND DATA_LEVEL_ID IS NULL '||
' AND DATA_LEVEL_NAME IS NULL '||
' AND USER_DATA_LEVEL_NAME IS NULL '
USING p_data_set_id, p_attr_group_type;
' UPDATE '||p_interface_table_name||' UAI1 '||
' SET DATA_LEVEL_ID = (SELECT DATA_LEVEL_ID '||
' FROM EGO_DATA_LEVEL_VL '||
' WHERE (DATA_LEVEL_NAME = NVL(UAI1.DATA_LEVEL_NAME, CHR(0)) '||
' OR USER_DATA_LEVEL_NAME = NVL(UAI1.USER_DATA_LEVEL_NAME, CHR(0)))'||
' AND APPLICATION_ID = :application_id '||
' AND ATTR_GROUP_TYPE = :attr_group_type '||
' ) '||
' WHERE UAI1.DATA_SET_ID = :data_set_id '||
' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS ||
' AND UAI1.ATTR_GROUP_TYPE = :attr_group_type '||
' AND DATA_LEVEL_ID IS NULL '||
' AND NOT( DATA_LEVEL_NAME IS NULL AND USER_DATA_LEVEL_NAME IS NULL) '
USING p_application_id, p_attr_group_type, p_data_set_id, p_attr_group_type;
'UPDATE '||p_interface_table_name||' UAI1 '||
' SET PROCESS_STATUS = PROCESS_STATUS +'||G_PS_INVALID_DATA_LEVEL ||
' WHERE UAI1.DATA_SET_ID = :data_set_id '||
' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||' '||
' AND UAI1.ATTR_GROUP_TYPE = :attr_group_type '||
' AND ( DATA_LEVEL_ID IS NULL '||
' OR NOT EXISTS(SELECT NULL '||
' FROM EGO_ATTR_GROUP_DL '||
' WHERE ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID '||
' AND DATA_LEVEL_ID = UAI1.DATA_LEVEL_ID) )'
USING p_data_set_id, p_attr_group_type;
DYLAN: why are we selecting this? We don't treat CC as a constraint;
SELECT 'HZ_PARTY:'||TO_CHAR(PERSON_ID)
INTO G_HZ_PARTY_ID
FROM EGO_PEOPLE_V
WHERE USER_NAME = G_USER_NAME;
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_ATTR_GRP_ID||'
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_GROUP_ID IS NOT NULL
AND NVL(UAI1.ATTR_GROUP_TYPE,:attr_group_type)=:attr_group_type
AND ATTR_GROUP_ID <> ( SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE APPLICATION_ID = :application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI1.ATTR_GROUP_INT_NAME) '
USING p_data_set_id, p_attr_group_type, p_attr_group_type, p_application_id, p_attr_group_type ;
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE APPLICATION_ID = :application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI1.ATTR_GROUP_INT_NAME)
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_GROUP_ID IS NULL '
USING p_application_id, p_attr_group_type, p_data_set_id;
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_GROUP_TYPE = (SELECT DESCRIPTIVE_FLEXFIELD_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE APPLICATION_ID = :application_id
AND ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID)
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ATTR_GROUP_TYPE IS NULL '
USING p_application_id, p_data_set_id;
EXECUTE IMMEDIATE ' SELECT DATA_SET_ID FROM '||p_interface_table_name||
' WHERE DATA_SET_ID = :data_set_id
AND ROWNUM = 1
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_GROUP_TYPE = :attr_group_type '
INTO l_dummy
USING p_data_set_id, p_attr_group_type;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_BAD_ATTR_OR_AG_METADATA||'
WHERE UAI1.DATA_SET_ID = :p_data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.ROW_IDENTIFIER IN
(SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :p_data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND NOT EXISTS (SELECT NULL
FROM EGO_FND_DSC_FLX_CTX_EXT AG,
FND_DESCR_FLEX_COLUMN_USAGES A
WHERE AG.ATTR_GROUP_ID = UAI2.ATTR_GROUP_ID
AND A.DESCRIPTIVE_FLEX_CONTEXT_CODE = AG.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND A.APPLICATION_ID = :p_application_id
AND A.DESCRIPTIVE_FLEXFIELD_NAME = :p_attr_group_type
AND A.END_USER_COLUMN_NAME = UAI2.ATTR_INT_NAME
AND A.ENABLED_FLAG = ''Y''))'
USING p_data_set_id, p_attr_group_type, p_data_set_id, p_application_id, p_attr_group_type;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_MULTIPLE_ENTRIES||'
WHERE UAI1.DATA_SET_ID = :p_data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ROW_IDENTIFIER IN
(SELECT ROW_IDENTIFIER
FROM (SELECT UAI2.ROW_IDENTIFIER
,UAI2.ATTR_INT_NAME
,COUNT(*) NUMBER_OF_ENTRIES
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :p_data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
GROUP BY UAI2.ROW_IDENTIFIER, UAI2.ATTR_INT_NAME)
WHERE NUMBER_OF_ENTRIES > 1)'
USING p_data_set_id, p_data_set_id;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_MULTIPLE_VALUES||'
WHERE UAI1.DATA_SET_ID = :p_data_set_id
AND UAI1.ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ROW_IDENTIFIER IN
(SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :p_data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ((UAI2.ATTR_VALUE_STR IS NOT NULL AND
(UAI2.ATTR_VALUE_NUM IS NOT NULL OR
UAI2.ATTR_VALUE_DATE IS NOT NULL))
OR
(UAI2.ATTR_VALUE_NUM IS NOT NULL AND
(UAI2.ATTR_VALUE_STR IS NOT NULL OR
UAI2.ATTR_VALUE_DATE IS NOT NULL))
OR
(UAI2.ATTR_VALUE_DATE IS NOT NULL AND
(UAI2.ATTR_VALUE_NUM IS NOT NULL OR
UAI2.ATTR_VALUE_STR IS NOT NULL))))'
USING p_data_set_id, p_attr_group_type, p_data_set_id;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_AG_NOT_ASSOCIATED||'
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ROW_IDENTIFIER IN
(SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.DATA_SET_ID = UAI1.DATA_SET_ID
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND NOT EXISTS (SELECT NULL
FROM EGO_OBJ_AG_ASSOCS_B A
WHERE A.ATTR_GROUP_ID = UAI2.ATTR_GROUP_ID
AND A.OBJECT_ID = '||l_object_id||'
AND A.CLASSIFICATION_CODE IN ('||p_related_class_codes_query||')';
' UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = PROCESS_STATUS +
DECODE(UAI1.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''', '||G_PS_BAD_TTYPE_CREATE||',
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', '||G_PS_BAD_TTYPE_UPDATE||',
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', '||G_PS_BAD_TTYPE_DELETE||')
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||'''
AND (SELECT MULTI_ROW FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI1.ATTR_GROUP_INT_NAME
AND APPLICATION_ID = '||p_application_id||'
AND DESCRIPTIVE_FLEXFIELD_NAME = '''||p_attr_group_type||''') <> ''Y''
AND UAI1.ROW_IDENTIFIER IN (
SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS;
l_ext_table_select :=
'(SELECT COUNT(*)
FROM '||l_ext_vl_name||'
WHERE ATTR_GROUP_ID = UAI2.ATTR_GROUP_ID
AND ROWNUM < 2 ';
l_ext_table_select := l_ext_table_select ||l_concat_pk_cols_UAI2;
l_ext_table_select := l_ext_table_select || ' AND '||l_pk1_column_name||' = UAI2.'||l_pk1_column_name;
l_ext_table_select := l_ext_table_select || ' AND '||l_pk2_column_name||' = UAI2.'||l_pk2_column_name;
l_ext_table_select := l_ext_table_select || ' AND '||l_pk3_column_name||' = UAI2.'||l_pk3_column_name;
l_ext_table_select := l_ext_table_select || ' AND '||l_pk4_column_name||' = UAI2.'||l_pk4_column_name;
l_ext_table_select := l_ext_table_select || ' AND '||l_pk5_column_name||' = UAI2.'||l_pk5_column_name;
l_ext_table_select := l_ext_table_select||' AND NVL(DATA_LEVEL_ID,-1) = NVL(UAI2.DATA_LEVEL_ID,-1) ';
l_ext_table_select := l_ext_table_select ||' AND (( ';
l_ext_table_select := l_ext_table_select ||' OR ( ';
l_ext_table_select := l_ext_table_select || ' UAI2.DATA_LEVEL_ID = '||l_list_of_dl_for_ag_type(i).DATA_LEVEL_ID
|| ' AND '||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME1||' = UAI2.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME1;
l_ext_table_select := l_ext_table_select || ' AND '||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME2||' = UAI2.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME2;
l_ext_table_select := l_ext_table_select ||' AND '||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME3||' = UAI2.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME3;
l_ext_table_select := l_ext_table_select || ' AND '||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME4||' = UAI2.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME4;
l_ext_table_select := l_ext_table_select || ' AND '||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME5||' = UAI2.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME5;
l_ext_table_select := l_ext_table_select ||' ) ';
l_ext_table_select := l_ext_table_select ||' ) ';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_1||',-1) = NVL(UAI2.'||l_data_level_column_1||',-1 )';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_1||',-1) = NVL(UAI2.'||l_data_level_column_1||',-1 )';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_2||',-1) = NVL(UAI2.'||l_data_level_column_2||',-1 )';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_1||',-1) = NVL(UAI2.'||l_data_level_column_1||',-1 )';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_2||',-1) = NVL(UAI2.'||l_data_level_column_2||',-1 )';
l_ext_table_select := l_ext_table_select || ' AND NVL('||l_data_level_column_3||',-1) = NVL(UAI2.'||l_data_level_column_3||',-1 )';
l_ext_table_select := l_ext_table_select||')';
l_dynamic_sql := l_dynamic_sql||' AND '||l_ext_table_select||
' = DECODE(UAI2.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''', 1,
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', 0,
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', 0))';
' UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = PROCESS_STATUS +
DECODE(UAI1.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', '||G_PS_BAD_TTYPE_UPDATE||',
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', '||G_PS_BAD_TTYPE_DELETE||')
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||'''
AND UAI1.TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''
AND (SELECT MULTI_ROW FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI1.ATTR_GROUP_INT_NAME
AND APPLICATION_ID = '||p_application_id||'
AND DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type) <> ''Y''
AND UAI1.ROW_IDENTIFIER IN (
SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND (SELECT COUNT(*) FROM '|| l_ext_vl_name||'
WHERE 1=1 '||l_concat_pk_cols_UAI2||'
AND ROWNUM <2 ) = DECODE(UAI2.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', 0,
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', 0)
)';
DYLAN: is there a reason we need to do this SYNC -> CREATE/UPDATE switch
here rather than after our initial validation loop? If we did it there,
we could use UKs and go for SR and MR all together. Let's talk about
this.
*/
-------------------------------------------------------
-- Update the transaction type column for single-row --
-- Attr Group rows from SYNC to UPDATE or CREATE --
-------------------------------------------------------
IF (l_ag_id_col_exists) THEN
l_dynamic_sql :=
' UPDATE '||p_interface_table_name||' UAI2
SET UAI2.TRANSACTION_TYPE = DECODE('||l_ext_table_select||
',0,'''||
EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','''||
EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''')
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.ATTR_GROUP_TYPE = :attr_group_type
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI2.TRANSACTION_TYPE = '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||'''
AND (SELECT MULTI_ROW FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI2.ATTR_GROUP_INT_NAME
AND APPLICATION_ID = :application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type) <> ''Y'' ';
' UPDATE '||p_interface_table_name||' UAI2
SET UAI2.TRANSACTION_TYPE = DECODE((SELECT COUNT(*) FROM '|| l_ext_vl_name ||' WHERE 1=1 '||l_concat_pk_cols_UAI2||' AND ROWNUM<2 )'||
',0,'''||
EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','''||
EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''')
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.ATTR_GROUP_TYPE = :attr_group_type
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI2.TRANSACTION_TYPE = '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||'''
AND (SELECT MULTI_ROW FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT
WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = UAI2.ATTR_GROUP_INT_NAME
AND APPLICATION_ID = :application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type) <> ''Y'' ';
'UPDATE '||p_interface_table_name||' UAI1' ||
' SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_OTHER_ATTRS_INVALID||
' WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND BITAND(PROCESS_STATUS,'||G_PS_OTHER_ATTRS_INVALID||') = 0'||
' AND UAI1.ROW_IDENTIFIER IN'||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER'||
' FROM '||p_interface_table_name||' UAI2'||
' WHERE UAI2.DATA_SET_ID = '||p_data_set_id||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||
' AND UAI2.ATTR_GROUP_INT_NAME = UAI1.ATTR_GROUP_INT_NAME)' ;
' SELECT DISTINCT ATTR_GROUP_INT_NAME
FROM '||p_interface_table_name||' UAI1
WHERE DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS <> '||G_PS_IN_PROCESS
USING p_data_set_id;
'UPDATE '||p_interface_table_name||' UAI1 '||
' SET UAI1.PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
' WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND UAI1.ROW_IDENTIFIER IN '||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER'||
' FROM '||p_interface_table_name||' UAI2 '||
' WHERE UAI2.DATA_SET_ID = '||p_data_set_id||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||')';
'SELECT /*+ LEADING(DISTINCT_ATTRS, FLX_EXT, A, EXT) */
EXT.ATTR_ID,
FLX_EXT.ATTR_GROUP_ID,
DISTINCT_ATTRS.ATTR_GROUP_INT_NAME,
FLX_EXT.MULTI_ROW MULTI_ROW_CODE,
A.END_USER_COLUMN_NAME ATTR_INT_NAME,
EXT.DATA_TYPE,
EXT.UNIQUE_KEY_FLAG,
A.DEFAULT_VALUE,
VS.MAXIMUM_SIZE,
A.REQUIRED_FLAG,
VS.FLEX_VALUE_SET_ID VALUE_SET_ID,
VS.VALIDATION_TYPE,
VS.MINIMUM_VALUE,
VS.MAXIMUM_VALUE,
UOM.UOM_CODE,
UOM.UOM_CLASS,
DISTINCT_ATTRS.DATA_LEVEL_ID,
FNV.FUNCTION_NAME VIEW_PRIVILEGE,
FNE.FUNCTION_NAME EDIT_PRIVILEGE
FROM (SELECT DISTINCT ATTR_GROUP_INT_NAME, ATTR_GROUP_ID
,ATTR_INT_NAME
,DATA_LEVEL_ID
FROM '||p_interface_table_name||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_GROUP_TYPE = :attr_group_type
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||') DISTINCT_ATTRS,
EGO_FND_DSC_FLX_CTX_EXT FLX_EXT,
FND_DESCR_FLEX_COLUMN_USAGES A,
EGO_FND_DF_COL_USGS_EXT EXT,
EGO_ATTR_GROUP_DL AG_DL,
FND_FLEX_VALUE_SETS VS,
MTL_UNITS_OF_MEASURE UOM,
FND_FORM_FUNCTIONS FNV,
FND_FORM_FUNCTIONS FNE
WHERE DISTINCT_ATTRS.ATTR_GROUP_ID = FLX_EXT.ATTR_GROUP_ID
and FLX_EXT.APPLICATION_ID = a.APPLICATION_ID
and FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = a.DESCRIPTIVE_FLEXFIELD_NAME
and FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = a.DESCRIPTIVE_FLEX_CONTEXT_CODE
and DISTINCT_ATTRS.ATTR_INT_NAME = a.END_USER_COLUMN_NAME
AND ''Y'' = A.ENABLED_FLAG
and a.APPLICATION_ID = ext.APPLICATION_ID
and a.DESCRIPTIVE_FLEXFIELD_NAME = ext.DESCRIPTIVE_FLEXFIELD_NAME
and a.DESCRIPTIVE_FLEX_CONTEXT_CODE = ext.DESCRIPTIVE_FLEX_CONTEXT_CODE
and a.APPLICATION_COLUMN_NAME = EXT.APPLICATION_COLUMN_NAME
AND A.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID(+)
AND EXT.UOM_CLASS = UOM.UOM_CLASS(+)
AND ''Y'' = UOM.BASE_UOM_FLAG(+)
AND AG_DL.ATTR_GROUP_ID = FLX_EXT.ATTR_GROUP_ID
AND AG_DL.DATA_LEVEL_ID = DISTINCT_ATTRS.DATA_LEVEL_ID
AND AG_DL.VIEW_PRIVILEGE_ID = FNV.FUNCTION_ID(+)
AND AG_DL.EDIT_PRIVILEGE_ID = FNE.FUNCTION_ID(+)
ORDER BY FLX_EXT.ATTR_GROUP_ID, A.COLUMN_SEQ_NUM'
USING p_data_set_id, p_attr_group_type;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = '||G_PS_NO_PRIVILEGES||'
WHERE UAI1.DATA_SET_ID = :p_data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ROW_IDENTIFIER IN
(
SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :p_data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND (UAI2.ATTR_GROUP_INT_NAME = :attr_group_name OR
UAI2.ATTR_GROUP_ID = :attr_group_id)
AND NVL(UAI2.DATA_LEVEL_ID,-1) = NVL(:data_level_id,-1)
AND NOT '||l_priv_predicate||'
)'
USING p_data_set_id, p_data_set_id,
l_attr_metadata_rec.ATTR_GROUP_INT_NAME,
l_attr_metadata_rec.ATTR_GROUP_ID,
l_attr_metadata_rec.DATA_LEVEL_ID;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = '||G_PS_NO_PRIVILEGES||'
WHERE UAI1.DATA_SET_ID = :p_data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND UAI1.ROW_IDENTIFIER IN
(
SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :p_data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND (UAI2.ATTR_GROUP_INT_NAME = :attr_group_name OR
UAI2.ATTR_GROUP_ID = :attr_group_id)
AND NVL(UAI2.DATA_LEVEL_ID,-1) = NVL(:data_level_id,-1)
AND NOT '||l_priv_predicate||'
)'
USING p_data_set_id, p_data_set_id,
l_attr_metadata_rec.ATTR_GROUP_INT_NAME,
l_attr_metadata_rec.ATTR_GROUP_ID,
l_attr_metadata_rec.DATA_LEVEL_ID;
EXECUTE IMMEDIATE 'SELECT TO_CHAR('||G_NULL_DATE_VAL||','''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''), TO_CHAR('||G_NULL_DATE_VAL||', ''SYYYY-MM-DD'') FROM DUAL '
INTO l_null_date_time_value,l_null_date_value;
l_dynamic_sql := 'UPDATE '||p_interface_table_name||' SET ';
'UPDATE '||p_interface_table_name||' INTF
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_INVALID_UOM||'
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ( (ATTR_UOM_DISP_VALUE IS NOT NULL AND ATTR_VALUE_UOM IS NULL
AND NOT EXISTS (SELECT ''X'' FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CLASS = :uom_class
AND UNIT_OF_MEASURE_TL = INTF.ATTR_UOM_DISP_VALUE
AND ROWNUM =1 ))
OR(ATTR_VALUE_UOM IS NOT NULL
AND NOT EXISTS (SELECT ''X'' FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CLASS = :uom_class
AND UOM_CODE = INTF.ATTR_VALUE_UOM
AND ROWNUM =1 ))
)' ;
'UPDATE '||p_interface_table_name||' INTF
SET ATTR_VALUE_UOM = NVL2(ATTR_UOM_DISP_VALUE,
(SELECT UOM_CODE FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CLASS = :uom_class
AND UNIT_OF_MEASURE_TL = INTF.ATTR_UOM_DISP_VALUE
AND LANGUAGE = USERENV(''LANG'')),null
)
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_GROUP_TYPE = :attr_group_type
AND ATTR_VALUE_UOM IS NULL ';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_NUM = TO_NUMBER(NVL(ATTR_VALUE_NUM,
(SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE)
)),
PROCESS_STATUS = NVL2(ATTR_VALUE_NUM,-- IF THE ATTR VALUE IS NULL WE VALIDATE THE VS ACCORDING TO ATTR_DISP_VAL OTHERWISE VALIDATION IS AGAINST THE ATTR_VALUE_* TAKEN AS INTERNAL_NAME OF VS
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE = TO_CHAR(ATTR_VALUE_NUM)),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
),
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
)
)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_NUM IS NOT NULL)
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_DATE = NVL(ATTR_VALUE_DATE,
(SELECT DISTINCT TRUNC(EGO_USER_ATTRS_BULK_PVT.Get_Date(FLEX_VALUE,'''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''))
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE)
),
PROCESS_STATUS = NVL2(ATTR_VALUE_DATE,-- IF THE ATTR VALUE IS NULL WE VALIDATE THE VS ACCORDING TO ATTR_DISP_VAL OTHERWISE VALIDATION IS AGAINST THE ATTR_VALUE_* TAKEN AS INTERNAL_NAME OF VS
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE = TO_CHAR(ATTR_VALUE_DATE,''YYYY-MM-DD HH24:MI:SS'')),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
),
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
)
)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_DATE = NVL(ATTR_VALUE_DATE,EGO_USER_ATTRS_BULK_PVT.Get_Date(
(SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE)
)
),
PROCESS_STATUS = NVL2(ATTR_VALUE_DATE,-- IF THE ATTR VALUE IS NULL WE VALIDATE THE VS ACCORDING TO ATTR_DISP_VAL OTHERWISE VALIDATION IS AGAINST THE ATTR_VALUE_* TAKEN AS INTERNAL_NAME OF VS
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE = TO_CHAR(ATTR_VALUE_DATE,''YYYY-MM-DD HH24:MI:SS'')),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
),
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
)
)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_STR = (NVL(ATTR_VALUE_STR,
(SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE)
)
),
PROCESS_STATUS = NVL2(ATTR_VALUE_STR,-- IF THE ATTR VALUE IS NULL WE VALIDATE THE VS ACCORDING TO ATTR_DISP_VAL OTHERWISE VALIDATION IS AGAINST THE ATTR_VALUE_* TAKEN AS FLEX_VALUE OF VS
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE = ATTR_VALUE_STR),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
),
(NVL2((SELECT DISTINCT FLEX_VALUE
FROM FND_FLEX_VALUES_VL
WHERE ENABLED_FLAG = ''Y''
AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE)
AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE)
AND FLEX_VALUE_SET_ID = :value_set_id
AND FLEX_VALUE_MEANING = ATTR_DISP_VALUE),
(PROCESS_STATUS),
(PROCESS_STATUS + '||G_PS_VALUE_NOT_IN_VS||'))
)
)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_STR IS NOT NULL)
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_NUM = DECODE(EGO_USER_ATTRS_BULK_PVT.Get_Datatype_Error_Val(ATTR_DISP_VALUE, :attr_datatype),0,NVL(TO_NUMBER(ATTR_DISP_VALUE),ATTR_VALUE_NUM),NULL),
PROCESS_STATUS = PROCESS_STATUS +
EGO_USER_ATTRS_BULK_PVT.Get_Datatype_Error_Val(ATTR_DISP_VALUE, :attr_datatype) + --returns 0 if datatype conversion happens correctly
EGO_USER_ATTRS_BULK_PVT.Get_Max_Min_Error_Val( NVL(ATTR_DISP_VALUE,ATTR_VALUE_NUM) * NVL((SELECT CONVERSION_RATE FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = :uom_class AND UOM_CODE = ATTR_VALUE_UOM AND ROWNUM = 1),1),
:attr_datatype,
:attr_min_allowed_val,
:attr_max_allowed_val) --returns 0 if max/min is honoured or datatype conversion fails
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_NUM IS NOT NULL)';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_DATE = TRUNC(NVL(EGO_USER_ATTRS_BULK_PVT.Get_Date(ATTR_DISP_VALUE),ATTR_VALUE_DATE)),--WE SHOULD REMOVE THE TIME PART FROM THE DATE IF DATATYPE IS NOT DATE TIME
PROCESS_STATUS = PROCESS_STATUS +
EGO_USER_ATTRS_BULK_PVT.Get_Datatype_Error_Val(ATTR_DISP_VALUE, :attr_datatype) + --returns 0 if datatype conversion happens correctly
EGO_USER_ATTRS_BULK_PVT.Get_Max_Min_Error_Val(NVL(ATTR_DISP_VALUE,ATTR_VALUE_DATE), :attr_datatype, :attr_min_allowed_val, :attr_max_allowed_val) --returns 0 if max/min is honoured or datatype conv fails
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_DATE = NVL(EGO_USER_ATTRS_BULK_PVT.Get_Date(ATTR_DISP_VALUE,'''||EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||'''),ATTR_VALUE_DATE),
PROCESS_STATUS = PROCESS_STATUS +
EGO_USER_ATTRS_BULK_PVT.Get_Datatype_Error_Val(ATTR_DISP_VALUE, :attr_datatype) + --returns 0 if datatype conversion happens correctly
EGO_USER_ATTRS_BULK_PVT.Get_Max_Min_Error_Val(NVL(ATTR_DISP_VALUE,ATTR_VALUE_DATE), :attr_datatype, :attr_min_allowed_val, :attr_max_allowed_val) --returns 0 if max/min is honoured or datatype conv fails
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND (ATTR_DISP_VALUE IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)';
'UPDATE '||p_interface_table_name||'
SET ATTR_VALUE_STR = ATTR_DISP_VALUE
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_DISP_VALUE IS NOT NULL';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_MAX_LENGTH_VIOLATION||'
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_NUM IS NOT NULL
AND LENGTH(ATTR_VALUE_NUM) > :max_allowed_size';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_MAX_LENGTH_VIOLATION||'
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_STR IS NOT NULL
AND LENGTH(ATTR_VALUE_STR) > :max_allowed_size';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_DATA_IN_WRONG_COL||'
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_NUM IS NULL
AND ATTR_DISP_VALUE IS NULL
AND (ATTR_VALUE_STR IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)' ;
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_DATA_IN_WRONG_COL||'
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_DATE IS NULL
AND ATTR_DISP_VALUE IS NULL
AND (ATTR_VALUE_STR IS NOT NULL OR ATTR_VALUE_NUM IS NOT NULL)' ;
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_DATA_IN_WRONG_COL||'
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_STR IS NULL
AND ATTR_DISP_VALUE IS NULL
AND (ATTR_VALUE_NUM IS NOT NULL OR ATTR_VALUE_DATE IS NOT NULL)' ;
'UPDATE '||p_interface_table_name||' INTF
SET ATTR_VALUE_NUM = ATTR_VALUE_NUM * NVL((SELECT CONVERSION_RATE FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = :uom_class
AND UOM_CODE = INTF.ATTR_VALUE_UOM
AND ROWNUM = 1),1)
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_UOM <> :base_uom ';
'UPDATE '||p_interface_table_name||' UAI1' ||
' SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_OTHER_ATTRS_INVALID||
' WHERE UAI1.DATA_SET_ID = :data_set_id '||
' AND BITAND(PROCESS_STATUS,'||G_PS_OTHER_ATTRS_INVALID||') = 0'||
' AND UAI1.ROW_IDENTIFIER IN'||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER'||
' FROM '||p_interface_table_name||' UAI2'||
' WHERE UAI2.DATA_SET_ID = :data_set_id '||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||
' AND UAI2.ATTR_GROUP_INT_NAME = UAI1.ATTR_GROUP_INT_NAME)'
USING p_data_set_id, p_data_set_id;
' SELECT DISTINCT ATTR_GROUP_INT_NAME
FROM '||p_interface_table_name||' UAI1
WHERE DATA_SET_ID = :data_set_id
AND ATTR_GROUP_TYPE = :attr_group_type
AND (UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR UAI1.PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )'
USING p_data_set_id, p_attr_group_type;
l_ext_table_select := ' '||l_ext_vl_name ||
' EXTVL1 WHERE EXTVL1.ATTR_GROUP_ID='||l_attr_group_metadata_obj.ATTR_GROUP_ID||' ';
l_intf_tbl_select :=
' (SELECT '||l_intf_column_name||' FROM '||p_interface_table_name||
' WHERE DATA_SET_ID = '||p_data_set_id||
' AND ATTR_GROUP_INT_NAME = '''||
l_attr_group_metadata_obj.ATTR_GROUP_NAME||
''' AND ATTR_INT_NAME = '''||
l_attr_metadata_table(i).ATTR_NAME||
''' AND ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER';
l_intf_tbl_select := l_intf_tbl_select || ')';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_attr_metadata_table(i).DATABASE_COLUMN||','||wierd_constant||') = NVL('||l_intf_tbl_select||','||wierd_constant||')';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_TL_COL_IS_A_UK||'
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS <> 3
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_GROUP_INT_NAME = :attr_group_int_name ';
l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk1_column_name||' = UAI1.'||l_pk1_column_name;
l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk2_column_name||' = UAI1.'||l_pk2_column_name;
l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk3_column_name||' = UAI1.'||l_pk3_column_name;
l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk4_column_name||' = UAI1.'||l_pk4_column_name;
l_ext_table_select := l_ext_table_select || ' AND EXTVL1.'||l_pk5_column_name||' = UAI1.'||l_pk5_column_name;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.DATA_LEVEL_ID,'||G_NULL_TOKEN_NUM||') '||
' = NVL(UAI1.DATA_LEVEL_ID,'||G_NULL_TOKEN_NUM||')';
AND INSTR(l_ext_table_select,l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME1) = 0) THEN
IF(l_list_of_dl_for_ag_type(i).PK_COLUMN_TYPE1 = 'NUMBER') THEN
wierd_constant := G_NULL_TOKEN_NUM;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME1||','||wierd_constant||') '||
' = NVL(UAI1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME1||','||wierd_constant||')';
AND INSTR(l_ext_table_select,l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME2) = 0) THEN
IF(l_list_of_dl_for_ag_type(i).PK_COLUMN_TYPE2 = 'NUMBER') THEN
wierd_constant := G_NULL_TOKEN_NUM;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME2||','||wierd_constant||') '||
' = NVL(UAI1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME2||','||wierd_constant||')';
AND INSTR(l_ext_table_select,l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME3) = 0) THEN
IF(l_list_of_dl_for_ag_type(i).PK_COLUMN_TYPE3 = 'NUMBER') THEN
wierd_constant := G_NULL_TOKEN_NUM;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME3||','||wierd_constant||') '||
' = NVL(UAI1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME3||','||wierd_constant||')';
AND INSTR(l_ext_table_select,l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME4) = 0) THEN
IF(l_list_of_dl_for_ag_type(i).PK_COLUMN_TYPE4 = 'NUMBER') THEN
wierd_constant := G_NULL_TOKEN_NUM;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME4||','||wierd_constant||') '||
' = NVL(UAI1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME4||','||wierd_constant||')';
AND INSTR(l_ext_table_select,l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME5) = 0) THEN
IF(l_list_of_dl_for_ag_type(i).PK_COLUMN_TYPE5 = 'NUMBER') THEN
wierd_constant := G_NULL_TOKEN_NUM;
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME5||','||wierd_constant||') '||
' = NVL(UAI1.'||l_list_of_dl_for_ag_type(i).PK_COLUMN_NAME5||','||wierd_constant||')';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_1||',-1) = NVL(UAI1.'||l_data_level_column_1||',-1)';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_1||',-1) = NVL(UAI1.'||l_data_level_column_1||',-1)';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_2||',-1) = NVL(UAI1.'||l_data_level_column_2||',-1)';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_1||',-1) = NVL(UAI1.'||l_data_level_column_1||',-1)';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_2||',-1) = NVL(UAI1.'||l_data_level_column_2||',-1)';
l_ext_table_select := l_ext_table_select || ' AND NVL(EXTVL1.'||l_data_level_column_3||',-1) = NVL(UAI1.'||l_data_level_column_3||',-1)';
code_debug(' The query generated for identifying the UKs is :'||l_ext_table_select ,3);
SELECT APPLICATION_TABLE_NAME,
VALUE_COLUMN_NAME,--VALUE_COLUMN_TYPE,VALUE_COLUMN_SIZE,
ID_COLUMN_NAME, --ID_COLUMN_TYPE, ID_COLUMN_SIZE,
MEANING_COLUMN_NAME, --MEANING_COLUMN_TYPE,
ADDITIONAL_WHERE_CLAUSE
INTO l_tvs_table_name,
l_tvs_val_col, --l_tvs_val_col_type, l_tvs_val_col_size,
l_tvs_id_col, --l_tvs_id_col_type, l_tvs_id_col_size
l_tvs_mean_col, --l_tvs_mean_col_type,
l_tvs_where_clause
FROM FND_FLEX_VALIDATION_TABLES
WHERE FLEX_VALUE_SET_ID = l_attr_metadata_table(y).VALUE_SET_ID;
SELECT REPLACE(l_tvs_where_clause,FND_GLOBAL.LOCAL_CHR(10),FND_GLOBAL.LOCAL_CHR(32)) INTO l_tvs_where_clause FROM dual; --replacing new line character
SELECT REPLACE(l_tvs_where_clause,FND_GLOBAL.LOCAL_CHR(13),FND_GLOBAL.LOCAL_CHR(32)) INTO l_tvs_where_clause FROM dual; --removing carriage return
l_value_from_ext_table := ' (SELECT '|| l_ext_attr_col_name ||'
FROM '||l_ext_table_select||'
AND ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID) ';
l_value_from_intftbl := '(SELECT ATTR_VALUE_NUM FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = '||p_data_set_id||'
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
l_value_from_intftbl := '(SELECT ATTR_VALUE_DATE FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = '||p_data_set_id||'
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
l_value_from_intftbl := '(SELECT ATTR_VALUE_STR FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = '||p_data_set_id||'
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
DECODE(UAI1.TRANSACTION_TYPE, ''UPDATE'','||l_value_from_ext_table||'
,''CREATE'',NULL
, ''SYNC'','||l_value_from_ext_table||'
,NULL)
)
)'||
SUBSTR(l_tvs_where_clause,l_attrname_end_index);
SELECT REPLACE(l_tvs_where_clause,':$OBJECT$','UAI1') INTO l_tvs_where_clause FROM DUAL;
l_tvs_select := '(SELECT DISTINCT '||l_tvs_col||' FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_val_col||' = UAI1.ATTR_DISP_VALUE )';
l_tvs_num_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_NUM )';
l_tvs_date_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_DATE_VALUE )';
l_tvs_str_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_STR )';
code_debug(' The TVS query constructed is :'||l_tvs_select ,3);
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_ATTRS_IN_TVS_WHERE||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
ELSE -- now we do the interface table update since we are sure we have the metadata for bound variables
/*
Note: we are assuming that the setup is correct i.e. the column of the TVS are of correct data type
the id col is of datatype same as the attribute
the meanin col is of type character.
another assumtion is that the user cannot enter the value directly in to the attr_val_* col
he has to enter the data in attr_disp_Value column for which we get the actual attr val.
*/
IF (l_attr_metadata_table(y).DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE) THEN
l_dynamic_sql_1 :=
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_num_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_NUM IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_NUM = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_date_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_DATE IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_DATE = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_STR = NVL(ATTR_VALUE_STR,'||l_tvs_select||'),
PROCESS_STATUS = PROCESS_STATUS + DECODE(NVL2(ATTR_VALUE_STR,
'||l_tvs_str_val_check_select||',
NVL2('||l_tvs_select||',1,NULL)
),
NULL,
'||G_PS_VALUE_NOT_IN_TVS||'
,0)
WHERE DATA_SET_ID = '||p_data_set_id||'
AND PROCESS_STATUS <> 3
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||''' ';
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_str_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_STR IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_STR = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id '||'
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table(y).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
' UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = PROCESS_STATUS +
DECODE(UAI1.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''', '||G_PS_BAD_TTYPE_CREATE||',
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', '||G_PS_BAD_TTYPE_UPDATE||',
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', '||G_PS_BAD_TTYPE_DELETE||',0)
WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI1.ATTR_GROUP_TYPE = '''||p_attr_group_type||''' '||
' AND UAI1.TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||
''' AND UAI1.ATTR_GROUP_INT_NAME = '''||
l_attr_group_metadata_obj.ATTR_GROUP_NAME||
''' AND UAI1.ROW_IDENTIFIER IN (
SELECT DISTINCT UAI2.ROW_IDENTIFIER
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = '||p_data_set_id||
' AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS;
l_dynamic_sql := l_dynamic_sql||' AND (SELECT COUNT(*) FROM '||l_ext_table_select||')
= DECODE(UAI2.TRANSACTION_TYPE,
'''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''', 1,
'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''', 0,
'''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''', 0))';
' UPDATE '||p_interface_table_name||' UAI1
SET UAI1.TRANSACTION_TYPE = DECODE((SELECT COUNT(*) FROM '||l_ext_table_select||'),0,'''||
EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','''||
EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''')
WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND UAI1.ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||
''' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI1.TRANSACTION_TYPE = '''||EGO_USER_ATTRS_DATA_PVT.G_SYNC_MODE||'''';
'INSERT INTO '||p_interface_table_name||' ( TRANSACTION_ID,
PROCESS_STATUS ,
DATA_SET_ID ,
ROW_IDENTIFIER ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_VALUE_NUM ,
TRANSACTION_TYPE ,'||l_concat_pk_cols_sel||'
ATTR_GROUP_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
SELECT MAX(TRANSACTION_ID),'||G_PS_IN_PROCESS||',DATA_SET_ID,ROW_IDENTIFIER,:attr_group_int_name,:attr_internal_name,
:default_value,'''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID,MAX(CREATED_BY),SYSDATE,MAX(LAST_UPDATED_BY),SYSDATE
FROM '||p_interface_table_name||' A
WHERE NOT EXISTS (
SELECT NULL
FROM '||p_interface_table_name||' B
WHERE DATA_SET_ID = A.DATA_SET_ID
AND B.ATTR_INT_NAME = :attr_internal_name
AND B.ATTR_GROUP_INT_NAME = A.ATTR_GROUP_INT_NAME
AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
AND A.ROW_IDENTIFIER = B.ROW_IDENTIFIER)
AND DATA_SET_ID = :data_set_id
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND TRANSACTION_TYPE = '''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''
GROUP BY DATA_SET_ID,ROW_IDENTIFIER,'|| l_concat_pk_cols_sel ||' ATTR_GROUP_ID' ;
'INSERT INTO '||p_interface_table_name||' ( TRANSACTION_ID,
PROCESS_STATUS ,
DATA_SET_ID ,
ROW_IDENTIFIER ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_VALUE_DATE ,
TRANSACTION_TYPE ,'||l_concat_pk_cols_sel||'
ATTR_GROUP_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
SELECT MAX(TRANSACTION_ID),'||G_PS_IN_PROCESS||',DATA_SET_ID,ROW_IDENTIFIER,:attr_group_int_name,:attr_internal_name,
:default_value,'''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID,MAX(CREATED_BY),SYSDATE,MAX(LAST_UPDATED_BY),SYSDATE
FROM '||p_interface_table_name||' A
WHERE NOT EXISTS (
SELECT NULL
FROM '||p_interface_table_name||' B
WHERE DATA_SET_ID = A.DATA_SET_ID
AND B.ATTR_INT_NAME = :attr_internal_name
AND B.ATTR_GROUP_INT_NAME = A.ATTR_GROUP_INT_NAME
AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
AND A.ROW_IDENTIFIER = B.ROW_IDENTIFIER)
AND DATA_SET_ID = :data_set_id
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND TRANSACTION_TYPE = '''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''
GROUP BY DATA_SET_ID,ROW_IDENTIFIER,'|| l_concat_pk_cols_sel ||' ATTR_GROUP_ID' ;
'INSERT INTO '||p_interface_table_name||' ( TRANSACTION_ID,
PROCESS_STATUS ,
DATA_SET_ID ,
ROW_IDENTIFIER ,
ATTR_GROUP_INT_NAME ,
ATTR_INT_NAME ,
ATTR_VALUE_STR ,
TRANSACTION_TYPE ,'||l_concat_pk_cols_sel||'
ATTR_GROUP_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
SELECT MAX(TRANSACTION_ID),'||G_PS_IN_PROCESS||',DATA_SET_ID,ROW_IDENTIFIER,:attr_group_int_name,:attr_internal_name,
:default_value,'''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''','|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID,MAX(CREATED_BY),SYSDATE,MAX(LAST_UPDATED_BY),SYSDATE
FROM '||p_interface_table_name||' A
WHERE NOT EXISTS (
SELECT NULL
FROM '||p_interface_table_name||' B
WHERE DATA_SET_ID = A.DATA_SET_ID
AND B.ATTR_INT_NAME = :attr_internal_name
AND B.ATTR_GROUP_INT_NAME = A.ATTR_GROUP_INT_NAME
AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
AND A.ROW_IDENTIFIER = B.ROW_IDENTIFIER)
AND DATA_SET_ID = :data_set_id
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND TRANSACTION_TYPE = '''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''
GROUP BY DATA_SET_ID,ROW_IDENTIFIER,'|| l_concat_pk_cols_sel ||' ATTR_GROUP_ID' ;
END IF;--inserting row
code_debug(' After inserting rows where ever required for attribute '||l_attr_metadata_table_1(z).ATTR_NAME ,2);
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_REQUIRED_ATTRIBUTE||'
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_NUM IS NULL
AND ATTR_DISP_VALUE IS NULL ' ;--BugFix : 4171705
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_REQUIRED_ATTRIBUTE||'
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_DATE IS NULL
AND ATTR_DISP_VALUE IS NULL ' ;--BugFix : 4171705
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_REQUIRED_ATTRIBUTE||'
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ATTR_VALUE_STR IS NULL
AND ATTR_DISP_VALUE IS NULL ' ;--BugFix : 4171705
SELECT APPLICATION_TABLE_NAME,
VALUE_COLUMN_NAME,--VALUE_COLUMN_TYPE,VALUE_COLUMN_SIZE,
ID_COLUMN_NAME, --ID_COLUMN_TYPE, ID_COLUMN_SIZE,
MEANING_COLUMN_NAME, --MEANING_COLUMN_TYPE,
ADDITIONAL_WHERE_CLAUSE
INTO l_tvs_table_name,
l_tvs_val_col, --l_tvs_val_col_type, l_tvs_val_col_size,
l_tvs_id_col, --l_tvs_id_col_type, l_tvs_id_col_size
l_tvs_mean_col, --l_tvs_mean_col_type,
l_tvs_where_clause
FROM FND_FLEX_VALIDATION_TABLES
WHERE FLEX_VALUE_SET_ID = l_attr_metadata_table_sr(x).VALUE_SET_ID;
SELECT REPLACE(l_tvs_where_clause,FND_GLOBAL.LOCAL_CHR(10),FND_GLOBAL.LOCAL_CHR(32)) INTO l_tvs_where_clause FROM dual; --replacing new line character
SELECT REPLACE(l_tvs_where_clause,FND_GLOBAL.LOCAL_CHR(13),FND_GLOBAL.LOCAL_CHR(32)) INTO l_tvs_where_clause FROM dual; --removing carriage return
l_value_from_ext_table := '( SELECT '|| l_ext_attr_col_name ||'
FROM '||l_ext_vl_name||'
WHERE ATTR_GROUP_ID = UAI1.ATTR_GROUP_ID ';
l_value_from_intftbl := '(SELECT ATTR_VALUE_NUM FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
l_value_from_intftbl := '(SELECT ATTR_VALUE_DATE FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
l_value_from_intftbl := '(SELECT ATTR_VALUE_STR FROM '||p_interface_table_name||'
WHERE ROW_IDENTIFIER = UAI1.ROW_IDENTIFIER
AND DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = '''||l_bind_attr_name||''' )';
DECODE(UAI1.TRANSACTION_TYPE, ''UPDATE'','||l_value_from_ext_table||',''CREATE'',NULL,NULL)
)
)'||
SUBSTR(l_tvs_where_clause,l_attrname_end_index);
SELECT REPLACE(l_tvs_where_clause,':$OBJECT$','UAI1') INTO l_tvs_where_clause FROM DUAL;
l_tvs_select := '(SELECT DISTINCT '||l_tvs_col||' FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_val_col||' = UAI1.ATTR_DISP_VALUE )';--BugFix : 4171705
l_tvs_num_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_NUM )';
l_tvs_date_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_DATE )';
l_tvs_str_val_check_select := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_STR )';
code_debug(' The TVS select constructed is :'||l_tvs_select ,3);
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_ATTRS_IN_TVS_WHERE||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
ELSE -- now we do the interface table update since we are sure we have the metadata for bound variables
/*
Note: we are assuming that the setup is correct i.e. the column of the TVS are of correct data type
the id col is of datatype same as the attribute
the meanin col is of type character.
another assumtion is that the user cannot enter the value directly in to the attr_val_* col
he has to enter the data in attr_disp_Value column for which we get the actual attr val.
*/
IF (l_attr_metadata_table_sr(x).DATA_TYPE_CODE = EGO_EXT_FWK_PUB.G_NUMBER_DATA_TYPE) THEN
l_dynamic_sql_1 :=
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_num_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_NUM IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_NUM = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_date_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_DATE IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_DATE = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_str_val_check_select||'),0,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_VALUE_STR IS NOT NULL';
'UPDATE '||p_interface_table_name||' UAI1
SET ATTR_VALUE_STR = NVL('||l_tvs_select||',NULL),
PROCESS_STATUS = PROCESS_STATUS + DECODE(('||l_tvs_select||'),NULL,'||G_PS_VALUE_NOT_IN_TVS||',0)
WHERE DATA_SET_ID = :data_set_id
AND (PROCESS_STATUS = '||G_PS_IN_PROCESS||' OR PROCESS_STATUS > '||G_PS_BAD_ATTR_OR_AG_METADATA||' )
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ATTR_INT_NAME = '''||l_attr_metadata_table_sr(x).ATTR_NAME||'''
AND ATTR_GROUP_INT_NAME = '''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||'''
AND ATTR_DISP_VALUE IS NOT NULL ';
'UPDATE '||p_interface_table_name||'
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_BAD_TVS_SETUP||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_INT_NAME = :attr_internal_name
AND ATTR_GROUP_INT_NAME = :attr_group_int_name';
SELECT PRE_BUSINESS_EVENT_NAME
INTO l_pre_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
SELECT COUNT(*)
INTO l_dummy
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID
AND RAISE_PRE_EVENT = 'Y';
l_dynamic_sql := ' SELECT EXTVL1.EXTENSION_ID , UAI1.ATTR_GROUP_INT_NAME, UAI1.TRANSACTION_TYPE, UAI1.ROW_IDENTIFIER ';
l_dynamic_sql_delete_post := l_dynamic_sql_delete_post || l_dynamic_query || ' ,NULL';
l_dynamic_sql_delete_post := l_dynamic_sql_delete_post || ' FROM '||p_interface_table_name||' UAI1 ';
l_dynamic_sql_delete_post := l_dynamic_sql_delete_post || ' WHERE UAI1.DATA_SET_ID = :data_set_id '||
' AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI1.ATTR_GROUP_INT_NAME = :attr_group_int_name '||
' AND UAI1.TRANSACTION_TYPE = '''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||'''';
l_dynamic_sql_delete_post := l_dynamic_sql_delete_post || ' GROUP BY UAI1.ATTR_GROUP_INT_NAME, UAI1.TRANSACTION_TYPE, UAI1.ROW_IDENTIFIER ';
l_dynamic_sql_delete_post := l_dynamic_sql_delete_post ||' '|| l_dynamic_group_by;
l_dynamic_sql := l_dynamic_sql || l_ext_table_select;
'UPDATE '||p_interface_table_name||' UAI1
SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_PRE_EVENT_FAILED||'
WHERE DATA_SET_ID = :data_set_id
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND ROWNUM = 1';
WHILE (INSTR(l_attr_name_val_str,'#') <> 0 AND l_ag_deflatened_row.TRANSACTION_TYPE <> EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE)
LOOP
l_attr_name := SUBSTR(l_attr_name_val_str
,INSTR(l_attr_name_val_str,'#*')+2
,INSTR(l_attr_name_val_str,'*#')-INSTR(l_attr_name_val_str,'#*')-2
);
l_dynamic_sql := ' SELECT NULL , UAI1.ATTR_GROUP_INT_NAME, UAI1.TRANSACTION_TYPE, UAI1.ROW_IDENTIFIER ';
l_row_to_column_query_base := 'SELECT 2910 EXTENSION_ID, MAX(ROW_IDENTIFIER) ROW_IDENTIFIER,MAX(TRANSACTION_TYPE) TRANSACTION_TYPE,MAX(ATTR_GROUP_ID) ATTR_GROUP_ID,MAX('||l_class_code_column_name||') '||l_class_code_column_name;
'NVL((SELECT NVL(RTCQ.'||l_attr_metadata_table_sr(d).DATABASE_COLUMN||','||wierd_constant||') FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM '||
l_ext_tl_table_name||' WHERE EXTENSION_ID = EXT.EXTENSION_ID AND ROWNUM = 1 )), '||wierd_constant_2||' ) '||
',NVL((SELECT '||l_attr_metadata_table_sr(d).DATABASE_COLUMN || ' FROM '||l_ext_tl_table_name||' WHERE LANGUAGE = USERENV(''LANG'') AND EXTENSION_ID = EXT.EXTENSION_ID),'||wierd_constant||' ) '||
')';
'DECODE(TRANSACTION_TYPE,'''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''',ATTR_VALUE_DATE,TO_DATE(DECODE(ATTR_VALUE_DATE,'||wierd_constant||
',NULL,TO_CHAR(ATTR_VALUE_DATE,''DD/MM/YYYY HH24:MI:SS'')),''DD/MM/YYYY HH24:MI:SS'') )';
l_no_alias_tl_cols_sel_list := l_no_alias_tl_cols_sel_list||' , '||' NVL( (SELECT COLUMN_VALUE '
||' FROM EGO_INTERFACE_TL '
||' WHERE SET_PROCESS_ID = '||p_data_set_id
||' AND TABLE_NAME = '''||p_interface_table_name||''' '
||' AND COLUMN_NAME = '''||l_attr_metadata_table_sr(d).ATTR_NAME||''' '
||' AND UNIQUE_ID = RTCQ.ROW_IDENTIFIER '
||' AND LANGUAGE = L.LANGUAGE_CODE) ,'||l_attr_metadata_table_sr(d).DATABASE_COLUMN||') ';
' FROM (SELECT DECODE(ATTR_INT_NAME'||l_row_to_column_attr_decode||
') RN,TRANSACTION_TYPE,ATTR_GROUP_ID'||
l_no_alias_cc_pk_dl_list||
',ATTR_INT_NAME,NVL(ATTR_VALUE_NUM,'||
EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_NUM||
')ATTR_VALUE_NUM, NVL(ATTR_VALUE_UOM,'||
EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR||
') ATTR_VALUE_UOM, NVL(ATTR_VALUE_STR,'||
EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_STR||
')ATTR_VALUE_STR,NVL(ATTR_VALUE_DATE,'||
EGO_USER_ATTRS_BULK_PVT.G_NULL_TOKEN_DATE||
')ATTR_VALUE_DATE,ROW_IDENTIFIER FROM '||
p_interface_table_name||
' WHERE DATA_SET_ID = :data_set_id AND PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND ATTR_GROUP_INT_NAME = :attr_group_name AND ATTR_GROUP_TYPE = '''||p_attr_group_type||''') GROUP BY ROW_IDENTIFIER';
code_debug( 'UPDATE '||p_interface_table_name||
' SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_IDENTICAL_ROWS||
' WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS <> '||G_PS_GENERIC_ERROR||'
AND PROCESS_STATUS <> '||G_PS_SUCCESS||'
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ROW_IDENTIFIER IN (SELECT DISTINCT RTCQ.ROW_IDENTIFIER
FROM ('||l_row_to_column_query||') RTCQ,
('||l_row_to_column_query||') EXT
WHERE RTCQ.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
AND RTCQ.ROW_IDENTIFIER <> EXT.ROW_IDENTIFIER'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_whr_uks_idnt_chk||')'
);
'UPDATE '||p_interface_table_name||
' SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_IDENTICAL_ROWS||
' WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS <> '||G_PS_GENERIC_ERROR||'
AND PROCESS_STATUS <> '||G_PS_SUCCESS||'
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ROW_IDENTIFIER IN (SELECT DISTINCT RTCQ.ROW_IDENTIFIER
FROM ('||l_row_to_column_query||') RTCQ,
('||l_row_to_column_query||') EXT
WHERE RTCQ.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
AND RTCQ.ROW_IDENTIFIER <> EXT.ROW_IDENTIFIER'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_whr_uks_idnt_chk||')'
USING p_data_set_id,
p_data_set_id,
l_attr_group_metadata_obj.ATTR_GROUP_NAME,
p_data_set_id,
l_attr_group_metadata_obj.ATTR_GROUP_NAME,
l_attr_group_metadata_obj.ATTR_GROUP_ID;
'UPDATE '||p_interface_table_name||
' SET PROCESS_STATUS = PROCESS_STATUS + '||G_PS_IDENTICAL_ROWS||
' WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS <> '||G_PS_GENERIC_ERROR||'
AND PROCESS_STATUS <> '||G_PS_SUCCESS||'
AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0
AND ROW_IDENTIFIER IN (SELECT DISTINCT RTCQ.ROW_IDENTIFIER
FROM ('||l_row_to_column_query||') RTCQ,
('||l_row_to_column_query||') EXT
WHERE RTCQ.ATTR_GROUP_ID = EXT.ATTR_GROUP_ID
AND RTCQ.ROW_IDENTIFIER <> EXT.ROW_IDENTIFIER'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_whr_uks_idnt_chk||')'
USING p_data_set_id,
p_data_set_id,
l_attr_group_metadata_obj.ATTR_GROUP_NAME,
p_data_set_id,
l_attr_group_metadata_obj.ATTR_GROUP_NAME;
'UPDATE '||p_interface_table_name||' UAI1' ||
' SET UAI1.PROCESS_STATUS = UAI1.PROCESS_STATUS + '||G_PS_OTHER_ATTRS_INVALID||
' WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND BITAND(PROCESS_STATUS,'||G_PS_OTHER_ATTRS_INVALID||') = 0'||
' AND UAI1.ROW_IDENTIFIER IN'||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER'||
' FROM '||p_interface_table_name||' UAI2'||
' WHERE UAI2.DATA_SET_ID = '||p_data_set_id||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||
' AND UAI2.ATTR_GROUP_INT_NAME = UAI1.ATTR_GROUP_INT_NAME)' ;
' SELECT EGO_EXTFWK_S.NEXTVAL, '||l_concat_pk_cols_sel||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE '||
' BULK COLLECT INTO ext_id_tbl, '||l_pk_blk_tbl_list||l_dl_blk_tbl_list||','||l_class_blk_tbl_list||
', created_by_tbl, creation_date_tbl, lu_by_tbl, lu_date_tbl '||
' FROM ( SELECT '|| l_concat_pk_cols_sel ||
' MAX(CREATED_BY) CREATED_BY, MAX(CREATION_DATE) CREATION_DATE, MAX(LAST_UPDATED_BY) LAST_UPDATED_BY, MAX(LAST_UPDATE_DATE) LAST_UPDATE_DATE '||
' FROM '||p_interface_table_name||' UAI2 '||
' WHERE NOT EXISTS ( '||
' SELECT NULL '||
' FROM '||l_ext_b_table_name||' B '||
' WHERE 1=1 '||l_concat_pk_cols_UAI2|| ' ) '||
' AND DATA_SET_ID = :data_set_id '||
' AND ATTR_GROUP_TYPE = :attr_group_type '||
' AND PROCESS_STATUS = '||G_PS_IN_PROCESS||' '||
' AND BITAND(PROCESS_STATUS, '||G_PS_NO_PRIVILEGES||') = 0 '||
' GROUP BY ' || l_concat_pk_cols_sel ||' NULL ); '||
' INSERT INTO '||l_ext_b_table_name||
' ( EXTENSION_ID, '||l_concat_pk_cols_sel||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE) '||
' VALUES ( ext_id_tbl(i) , '||l_pk_blk_tbl_list_2||l_dl_blk_tbl_list_2||','||l_class_blk_tbl_list_2||
' ,created_by_tbl(i), creation_date_tbl(i), '||
' lu_by_tbl(i) , lu_date_tbl(i) ); '||
' INSERT INTO '||l_ext_tl_table_name||
' ( EXTENSION_ID, '||l_concat_pk_cols_sel||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE, '||
' SOURCE_LANG,LANGUAGE ) '||
' SELECT ext_id_tbl(i) , '||l_pk_blk_tbl_list_2||l_dl_blk_tbl_list_2||','||l_class_blk_tbl_list_2||
' ,created_by_tbl(i), creation_date_tbl(i), '||
' lu_by_tbl(i) , lu_date_tbl(i),USERENV(''LANG''),LANGUAGE_CODE '||
' FROM FND_LANGUAGES WHERE INSTALLED_FLAG IN (''I'', ''B'')'||
' ; '||
code_debug(' DML for inserting dummy rows for dev defined attrs:'||l_dynamic_Sql,3);
code_debug(' After DML for inserting dummy rows for dev defined attrs.'||l_dynamic_Sql,2);
'UPDATE '||p_interface_table_name||' UAI1
SET TRANSACTION_TYPE = '''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||'''
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
AND ATTR_GROUP_INT_NAME = :attr_grp_int_name
AND ATTR_GROUP_TYPE = :attr_group_type '
USING p_data_set_id, l_attr_group_metadata_obj.ATTR_GROUP_NAME, p_attr_group_type ;
code_debug(' Before Delete DML ' ,2);
' BEGIN SELECT EXT.EXTENSION_ID'||
' BULK COLLECT INTO '||l_db_col_tbl_collect_ext_id||
' FROM '||
l_ext_vl_name||
' EXT, ('||l_row_to_column_query||') RTCQ WHERE 1=1'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_where_uks||
' AND RTCQ.TRANSACTION_TYPE='''||
EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||
'''; IF (ext_id_tbl.COUNT > 0) THEN
DELETE FROM '||l_ext_b_table_name||' EXT
WHERE '||l_db_col_tbl_where_ext_id||
'; END IF;';
DELETE FROM '||l_ext_tl_table_name||' EXT
WHERE '||l_db_col_tbl_where_ext_id||
'; END IF;';
code_debug(' Delete DML for AG '||l_attr_group_metadata_obj.ATTR_GROUP_NAME ,3);
code_debug(' After Delete DML ' ,2);
done 1159: Look at UPDATE behavior for LANGs
done 1159: Set explicit NULLs
done? 1159: need to ensure that MD code sorts Attrs by sequence
11510: Also look into only firing DMLs for TTs we have in DS
11510: we need to use IF (l_attr_group_metadata_obj.ATTR_GROUP_ID_FLAG ='Y') THEN
11510+: deal with no-CC case (throughout the code)
11510+: get rid of PK, DL data type fetches
*/
code_debug(' Before Update DML ' ,2);
' BEGIN SELECT LANGUAGE, EXTENSION_ID'||
l_final_b_col_list ||
l_final_tl_col_list ||
' BULK COLLECT INTO lang_tbl,'||l_db_col_tbl_collect_ext_id||
l_db_col_tbl_collect_b_attrs||
l_db_col_tbl_collect_tl_attrs||
' FROM '||
'(SELECT USERENV(''LANG'') LANGUAGE, EXT.EXTENSION_ID'||
l_rtcq_alias_b_cols_list||
l_rtcq_alias_tl_cols_list||
' FROM '||
l_ext_vl_name||
' EXT, ('||l_row_to_column_query||') RTCQ WHERE 1=1'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_where_uks||
' AND RTCQ.TRANSACTION_TYPE='''||
EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''' )'||
' UNION '||
'(SELECT INTF_TL.LANGUAGE LANGUAGE, '|| --Added the following UNIONED Query for R12C.. this wud bring bak
' INTFRTCQ.EXTENSION_ID EXTENSION_ID '|| --the results from the intf_tl table as well for updating records
l_rtcq_alias_b_cols_list_1 || --in other languages. We assume that the Row_identifier in the itnf table
l_rtcq_alias_tl_cols_list_1 || --is unique for ag rows and we can join it with unique_identifier in tl tbl
' FROM EGO_INTERFACE_TL INTF_TL, '|| --to get the correct joins.
' ( SELECT USERENV(''LANG'') LANGUAGE, RTCQ.ROW_IDENTIFIER, EXT.EXTENSION_ID'||
l_rtcq_alias_b_cols_list||
l_rtcq_alias_tl_cols_list||
' FROM '||
l_ext_vl_name||
' EXT, ('||l_row_to_column_query||') RTCQ WHERE 1=1'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_where_uks||
' AND RTCQ.TRANSACTION_TYPE='''||
EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||''''||
' ) INTFRTCQ '||
'WHERE INTF_TL.SET_PROCESS_ID = :data_set_id '||
' AND UPPER(INTF_TL.TABLE_NAME) = '''||UPPER(p_interface_table_name)||''' '||
' AND INTF_TL.UNIQUE_ID = INTFRTCQ.ROW_IDENTIFIER ) ;';
UPDATE '||l_ext_b_table_name||' EXT
SET '||SUBSTR(l_db_col_tbl_set_b_attrs, 2)||
',LAST_UPDATED_BY=:current_user_id,
LAST_UPDATE_DATE=:current_date,
LAST_UPDATE_LOGIN=:current_login_id,
REQUEST_ID = :request_id
WHERE '||l_db_col_tbl_where_ext_id||';
UPDATE '||l_ext_tl_table_name||' EXT
SET '||SUBSTR(l_db_col_tbl_set_tl_attrs, 2)||
',LAST_UPDATED_BY=:current_user_id,
LAST_UPDATE_DATE=:current_date,
LAST_UPDATE_LOGIN=:current_login_id,
SOURCE_LANG=lang_tbl(i)
WHERE '||l_db_col_tbl_where_ext_id||
' AND (LANGUAGE=lang_tbl(i) OR SOURCE_LANG=lang_tbl(i));
UPDATE '||l_ext_b_table_name||' EXT '||
' SET LAST_UPDATED_BY=:current_user_id,
LAST_UPDATE_DATE=:current_date,
LAST_UPDATE_LOGIN=:current_login_id,
REQUEST_ID = :request_id
WHERE '||l_db_col_tbl_where_ext_id||';
code_debug(' Update DML for AG '||l_attr_group_metadata_obj.ATTR_GROUP_NAME ,3);
code_debug(' Don with executing the update DML ' ,2);
Note:Since we had to support TL UK's and we had to insert the row_identifier*-2
temporarily in the REQUEST_ID column so that while inserting rows in the
TL table we can identify the exact extension_id's inserted for the rows in
the B table. We would set the request_id back to the correct value after
we are done with the TL table inserions.
Without this it was failing if the AG had TL UK and more than
one rows were being inserted for the MR AG.
*/
l_dynamic_sql :=
'INSERT INTO '||l_ext_b_table_name||
'(REQUEST_ID, EXTENSION_ID'||
l_no_alias_cc_pk_dl_list||' ';
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN'||
l_no_alias_b_cols_list||
') SELECT RTCQ.ROW_IDENTIFIER*-2 , EGO_EXTFWK_S.NEXTVAL'||
l_rtcq_alias_cc_pk_dl_list||' ';
code_debug(' Before Inserting into B table ' ,2);
code_debug(' B table Insert DML for AG '||l_attr_group_metadata_obj.ATTR_GROUP_NAME ,3);
code_debug(' After Inserting into B table ' ,2);
code_debug(' Before Inserting into TL table ' ,2);
'INSERT INTO '||l_ext_tl_table_name||
'(EXTENSION_ID'||
l_no_alias_cc_pk_dl_list||' ';
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
SOURCE_LANG,
LANGUAGE'||
l_no_alias_tl_cols_list||
') SELECT EXT.EXTENSION_ID'||
l_rtcq_alias_cc_pk_dl_list||' ';
EXT.LAST_UPDATED_BY,
EXT.LAST_UPDATE_DATE,
EXT.LAST_UPDATE_LOGIN,
USERENV(''LANG''),
L.LANGUAGE_CODE'||
l_no_alias_tl_cols_sel_list||
' FROM '||l_ext_b_table_name||
' EXT, FND_LANGUAGES L, ('||l_row_to_column_query||') RTCQ
WHERE
(RTCQ.ROW_IDENTIFIER*-2) = EXT.REQUEST_ID
AND L.INSTALLED_FLAG IN (''I'', ''B'')'||
l_rtcq_to_ext_where_base||
l_rtcq_to_ext_where_uks||
' AND RTCQ.TRANSACTION_TYPE='''||
EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''';
code_debug(' TL table Insert DML for AG '||l_attr_group_metadata_obj.ATTR_GROUP_NAME ,3);
code_debug(' After Inserting into TL table ' ,2);
with inserting rows in the TL table.
*/
IF (l_ag_id_col_exists) THEN
l_dynamic_sql := ' UPDATE '||l_ext_b_table_name||
' SET REQUEST_ID = '||G_REQUEST_ID||
' WHERE ATTR_GROUP_ID = :atr_grp_id '||
' AND REQUEST_ID <-1 ';
SELECT COUNT(*)
INTO l_dummy
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID
AND RAISE_POST_EVENT = 'Y';
OPEN l_dynamic_cursor FOR l_dynamic_sql_delete_post
USING p_data_set_id, l_attr_group_metadata_obj.ATTR_GROUP_NAME;
'UPDATE '||p_interface_table_name||' UAI1 '||
' SET UAI1.PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
' WHERE UAI1.DATA_SET_ID = '||p_data_set_id||
' AND UAI1.ROW_IDENTIFIER IN '||
' (SELECT DISTINCT UAI2.ROW_IDENTIFIER'||
' FROM '||p_interface_table_name||' UAI2'||
' WHERE UAI2.DATA_SET_ID = '||p_data_set_id||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||')';
SELECT BUSINESS_EVENT_NAME
INTO l_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
EXECUTE IMMEDIATE ' SELECT COUNT(*) FROM '|| p_interface_table_name ||
' WHERE DATA_SET_ID = :data_set_id '||
' AND ATTR_GROUP_TYPE = '''||p_attr_group_type||''' '||
' AND PROCESS_STATUS = '||G_PS_IN_PROCESS
INTO l_successful_rowcount
USING p_data_set_id;
'UPDATE '||p_interface_table_name||' UAI1
SET UAI1.PROCESS_STATUS = '||G_PS_GENERIC_ERROR||'
WHERE UAI1.DATA_SET_ID = :data_set_id
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS;
'SELECT TRANSACTION_ID
FROM '||p_interface_table_name||' UAI1
WHERE UAI1.DATA_SET_ID = :data_set_id
AND ROWNUM = 1';
,p_program_update_date IN DATE
,p_current_user_party_id IN NUMBER
,p_target_entity_sql IN VARCHAR2
,p_process_status IN NUMBER DEFAULT G_PS_IN_PROCESS
,p_class_code_hierarchy_sql IN VARCHAR2 DEFAULT NULL
,p_hierarchy_template_tbl_sql IN VARCHAR2 DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Apply_template_on_intf_table';
SELECT data_level_id
,data_level_name
,pk1_column_name
,pk1_column_type
,pk2_column_name
,pk2_column_type
,pk3_column_name
,pk3_column_type
,pk4_column_name
,pk4_column_type
,pk5_column_name
,pk5_column_type
FROM EGO_DATA_LEVEL_B
WHERE application_id = cp_application_id
AND attr_group_type = cp_attr_group_type
ORDER BY data_level_id;
l_rows_to_insert_sql VARCHAR2(32767);
code_debug(l_api_name||' p_program_update_date '|| p_program_update_date );
l_dynamic_sql := ' SELECT count(*) FROM ( '||p_target_entity_sql||') ';
' SELECT MAX(ROW_IDENTIFIER),MAX(TRANSACTION_ID)'||
' FROM '||p_interface_table_name||
' WHERE DATA_SET_ID = :data_Set_id ';
SELECT PK1_COLUMN_NAME,
PK2_COLUMN_NAME,
PK3_COLUMN_NAME,
PK4_COLUMN_NAME,
PK5_COLUMN_NAME
INTO l_pk1_column_name,
l_pk2_column_name,
l_pk3_column_name,
l_pk4_column_name,
l_pk5_column_name
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT classification_col_name, classification_col_type
INTO l_class_code_column_name, l_class_code_column_type
FROM ego_fnd_objects_ext
WHERE object_name = p_object_name;
SELECT application_table_name
INTO l_ext_b_table_name
FROM FND_DESCRIPTIVE_FLEXS
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_attr_group_type;
SELECT flex_ext.application_vl_name
INTO l_ext_vl_name
FROM ego_fnd_desc_flexs_ext flex_ext
WHERE flex_ext.application_id = p_application_id
AND flex_ext.descriptive_flexfield_name = p_attr_group_type;
' SELECT DISTINCT ATTRIBUTE_GROUP_ID, TEMPL.TEMPLATE_ID, TEMPL.CLASSIFICATION_CODE '||
' FROM EGO_TEMPL_ATTRIBUTES TEMPL, '||
' EGO_FND_DSC_FLX_CTX_EXT AGMDATA, '||
' ('||p_target_entity_sql||') ENTITIES '||
' WHERE TEMPL.TEMPLATE_ID = ENTITIES.TEMPLATE_ID ';
SELECT DATA_LEVEL_ID
INTO l_ag_assoc_data_level_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID
AND ROWNUM = 1;
l_template_table_RTCQ := 'SELECT '||l_attr_group_metadata_obj.ATTR_GROUP_ID||', CLASSIFICATION_CODE CLASSIFICATION_CODE1,'||
'MAX(TEMPLATE_ID) TEMPLATE_ID1, ATTRIBUTE_GROUP_ID, ROW_NUMBER ';
l_intf_table_RTCQ := 'SELECT ROW_IDENTIFIER, ATTR_GROUP_ID, '||l_concat_pk_cols;
',MAX(PROGRAM_ID) PROGRAM_ID, MAX(PROGRAM_UPDATE_DATE) PROGRAM_UPDATE_DATE '||
',MAX(CREATED_BY) CREATED_BY,MAX(CREATION_DATE) CREATION_DATE '||
',MAX(LAST_UPDATED_BY) LAST_UPDATED_BY, MAX(LAST_UPDATE_DATE) LAST_UPDATE_DATE '||
',MAX(LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN ';
', ATTR_VALUE_NUM / (SELECT CONVERSION_RATE FROM MTL_UOM_CONVERSIONS '||
' WHERE UOM_CLASS = '''||l_attr_metadata_table(i).UNIT_OF_MEASURE_CLASS||''' '||
' AND UOM_CODE = NVL(ATTR_VALUE_UOM,'''||l_attr_metadata_table(i).UNIT_OF_MEASURE_BASE||''') '||
' AND ROWNUM = 1)';
' SELECT MAX(ROW_IDENTIFIER),MAX(TRANSACTION_ID) '||
' FROM '||p_interface_table_name||
' WHERE DATA_SET_ID = :data_Set_id ';
l_dynamic_sql := ' SELECT ATTR_GROUP_ID FROM '||l_ext_vl_name||' WHERE ROWNUM=1 ';
l_rows_to_insert_sql :=
'SELECT (TRANSACTION_ID+'||l_max_trans_id||') TRANSACTION_ID,PROCESS_STATUS,DATA_SET_ID ,ROW_IDENTIFIER, '||
' ATTR_GROUP_INT_NAME, ATTR_INT_NAME, ATTR_VALUE_STR,'||l_num_val_col||', ATTR_VALUE_UOM, ATTR_VALUE_DATE, '||
' ATTR_DISP_VALUE, TRANSACTION_TYPE, ATTR_GROUP_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, '||
' PROGRAM_UPDATE_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, '||
' LAST_UPDATE_LOGIN, '||l_concat_pk_cols||', ATTR_GROUP_TYPE ';
l_rows_to_insert_sql := l_rows_to_insert_sql ||', DATA_LEVEL_ID ';
l_rows_to_insert_sql := l_rows_to_insert_sql ||','||l_dl_col_list;
l_rows_to_insert_sql := l_rows_to_insert_sql||
' FROM '||
'(SELECT ROWNUM TRANSACTION_ID, '||
p_process_status||' PROCESS_STATUS, '||
p_data_set_id||' DATA_SET_ID, '||
' NVL(INTFRTCQ.ROW_IDENTIFIER,'||l_max_row_identifier||'+TEMPLRTCQ.ENTITY_ROWID) ROW_IDENTIFIER, '||
''''||l_attr_group_metadata_obj.ATTR_GROUP_NAME||''' ATTR_GROUP_INT_NAME, '||
' ATTRSV.ATTR_NAME ATTR_INT_NAME, '||
' NVL(TEMPL.ATTRIBUTE_STRING_VALUE,TEMPL.ATTRIBUTE_TRANSLATED_VALUE) ATTR_VALUE_STR , '||
' TEMPL.ATTRIBUTE_NUMBER_VALUE ATTR_VALUE_NUM , '||
' TEMPL.ATTRIBUTE_UOM_CODE ATTR_VALUE_UOM , '||
' TEMPL.ATTRIBUTE_DATE_VALUE ATTR_VALUE_DATE , '||
' NULL ATTR_DISP_VALUE, '||
' NVL(INTFRTCQ.TRANSACTION_TYPE,NVL2(EXT.EXTENSION_ID,''UPDATE'',''CREATE'')) TRANSACTION_TYPE, ';
l_rows_to_insert_sql := l_rows_to_insert_sql ||' TEMPLRTCQ.DATA_LEVEL_ID, ';
l_rows_to_insert_sql := l_rows_to_insert_sql ||l_dl_col_templrtcq_list||', ';
l_rows_to_insert_sql := l_rows_to_insert_sql||l_dl_col_decode_list||', ';
l_rows_to_insert_sql := l_rows_to_insert_sql||
l_attr_group_metadata_obj.ATTR_GROUP_ID||' ATTR_GROUP_ID, '||
p_request_id||' REQUEST_ID, '||
p_program_application_id||' PROGRAM_APPLICATION_ID, '||
p_program_id||' PROGRAM_ID, '||
'SYSDATE PROGRAM_UPDATE_DATE, '||
p_current_user_party_id||' CREATED_BY, '||
'SYSDATE CREATION_DATE, '||
p_current_user_party_id||' LAST_UPDATED_BY, '||
'SYSDATE LAST_UPDATE_DATE, '||
p_current_user_party_id||' LAST_UPDATE_LOGIN, '||
l_concat_pk_cols_entities||' '||
''''||p_attr_group_type||''' ATTR_GROUP_TYPE '||
' FROM ('||l_intf_table_RTCQ||') INTFRTCQ, '||
' ('||l_template_table_sql||') TEMPL, '||
' EGO_ATTRS_V ATTRSV, '||
' (SELECT ROWNUM ENTITY_ROWID, TEMPLRTCQ.* FROM '||
' (SELECT * FROM ('||p_target_entity_sql||') ENTITIES, '||
' ('||l_template_table_RTCQ||') TEMPLRTCQ '||
' WHERE TEMPLRTCQ.TEMPLATE_ID1 = ENTITIES.TEMPLATE_ID '||
' AND TEMPLRTCQ.CLASSIFICATION_CODE1 = ENTITIES.'||l_class_code_column_name ||
' ) TEMPLRTCQ) TEMPLRTCQ, '||
' '||l_ext_vl_name||' EXT '||
'WHERE 1 = 1 '||
l_ag_id_clause||' '||
' AND '||l_dl_ext_trtcq_join||' AND '||l_dl_intfrtcq_trtcq_join||' AND '||--the data level joins bugfix:5401212
l_ext_templrtcq_join||' '||
' AND TEMPL.ATTRIBUTE_GROUP_ID = '||l_attr_group_metadata_obj.ATTR_GROUP_ID||' '||
' AND TEMPL.CLASSIFICATION_CODE = TEMPLRTCQ.'||l_class_code_column_name||' '||
' AND TEMPL.TEMPLATE_ID = TEMPLRTCQ.TEMPLATE_ID '||
' AND TEMPL.ATTRIBUTE_GROUP_ID = TEMPLRTCQ.ATTRIBUTE_GROUP_ID '||
' AND TEMPL.ENABLED_FLAG = ''Y'' '||
' AND TEMPL.ROW_NUMBER = TEMPLRTCQ.ROW_NUMBER '||
' AND TEMPLRTCQ.'||l_class_code_column_name||'= INTFRTCQ.'||l_class_code_column_name||'(+)'||
' '||l_pk_col_where_ent_uartcq||' '||
' AND '||l_uk_tmpl_intf_rtcq_where||' '||--for unique key joi in intf and templ
' AND ATTRSV.ATTR_ID = TEMPL.ATTRIBUTE_ID '||
l_attr_null_chk_decode||' ';
l_rows_to_insert_sql := l_rows_to_insert_sql ||' AND EXISTS (SELECT 1 FROM ego_attr_group_dl WHERE attr_group_id = :ag_id2 AND data_level_id = TEMPLRTCQ.data_level_id) ';
l_rows_to_insert_sql := l_rows_to_insert_sql || ')';
l_rows_to_insert_sql := ' INSERT INTO '||p_interface_table_name||' ( '||
' TRANSACTION_ID,PROCESS_STATUS,DATA_SET_ID ,ROW_IDENTIFIER,ATTR_GROUP_INT_NAME, '||
' ATTR_INT_NAME, ATTR_VALUE_STR,ATTR_VALUE_NUM,ATTR_VALUE_UOM,ATTR_VALUE_DATE, '||
' ATTR_DISP_VALUE, TRANSACTION_TYPE, ATTR_GROUP_ID, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, '||
' PROGRAM_UPDATE_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, '||
' LAST_UPDATE_LOGIN, '||l_concat_pk_cols||', ATTR_GROUP_TYPE '||l_temp||' ) '||
l_rows_to_insert_sql;
code_debug(l_api_name||' Phase 8 SQL to insert ');
code_debug(l_api_name||' '|| l_rows_to_insert_sql);
EXECUTE IMMEDIATE l_rows_to_insert_sql
USING l_attr_group_metadata_obj.ATTR_GROUP_ID,
l_attr_group_metadata_obj.ATTR_GROUP_NAME,
l_attr_group_metadata_obj.ATTR_GROUP_ID;
EXECUTE IMMEDIATE l_rows_to_insert_sql
USING l_attr_group_metadata_obj.ATTR_GROUP_ID,
l_attr_group_metadata_obj.ATTR_GROUP_NAME;
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
'YYYY-MM-DD HH24:MI:SS'||''') FROM DUAL';
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';--'SYYYY-MM-DD HH24:MI:SS'
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';--'SYYYY-MM-DD HH24:MI:SS'
l_dynamic_sql := 'SELECT TO_CHAR('||l_formated_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';
PROCEDURE Insert_Default_Val_Rows (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_object_name IN VARCHAR2
,p_interface_table_name IN VARCHAR2
,p_data_set_id IN NUMBER
,p_target_entity_sql IN VARCHAR2
,p_attr_groups_to_exclude IN VARCHAR2 DEFAULT NULL
,p_additional_class_Code_query IN VARCHAR2 DEFAULT NULL
,p_extra_column_names IN VARCHAr2 DEFAULT NULL
,p_extra_column_values IN VARCHAR2 DEFAULT NULL
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_process_status IN NUMBER DEFAULT G_PS_IN_PROCESS
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name VARCHAR2(30) := 'Insert_Default_Val_Rows';
l_pk_cc_select_list VARCHAR2(500);
l_col_to_insert_list VARCHAR2(800);
SELECT data_level_id
,data_level_name
,pk1_column_name
,pk1_column_type
,pk2_column_name
,pk2_column_type
,pk3_column_name
,pk3_column_type
,pk4_column_name
,pk4_column_type
,pk5_column_name
,pk5_column_type
FROM EGO_DATA_LEVEL_B
WHERE application_id = cp_application_id
AND attr_group_type = cp_attr_group_type
ORDER BY data_level_id;
l_dynamic_sql := ' SELECT count(*) FROM ( '||p_target_entity_sql||') ';
SELECT pk1_column_name, pk1_column_type,
pk2_column_name, pk2_column_type,
pk3_column_name, pk3_column_type,
pk4_column_name, pk4_column_type,
pk5_column_name, pk5_column_type
INTO l_pk1_column_name, l_pk1_column_type,
l_pk2_column_name, l_pk2_column_type,
l_pk3_column_name, l_pk3_column_type,
l_pk4_column_name, l_pk4_column_type,
l_pk5_column_name, l_pk5_column_type
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT application_table_name
INTO l_ext_b_table_name
FROM FND_DESCRIPTIVE_FLEXS
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_attr_group_type;
SELECT CLASSIFICATION_COL_NAME
INTO l_class_code_column_name
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = p_object_name;
l_pk_cc_select_list := ' , ENTITYAG_TBL.'|| l_class_code_column_name;
l_ag_presence_chk_sql := ' SELECT 1 FROM '||p_interface_table_name||' UAI'||
' WHERE NVL(UAI.ATTR_GROUP_ID,ATTR_GROUP_TBL.ATTR_GROUP_ID) = ATTR_GROUP_TBL.ATTR_GROUP_ID'||
' AND NVL(UAI.ATTR_GROUP_TYPE,ATTR_GROUP_TBL.DESCRIPTIVE_FLEXFIELD_NAME) = ATTR_GROUP_TBL.DESCRIPTIVE_FLEXFIELD_NAME'||
' AND UAI.DATA_SET_ID = :data_set_id '||
' AND UAI.ATTR_GROUP_INT_NAME = ATTR_GROUP_TBL.DESCRIPTIVE_FLEX_CONTEXT_CODE ';
code_debug(l_api_name ||' l_pk_cc_select_list '||l_pk_cc_select_list);
l_pk_cc_select_list := l_pk_cc_select_list || ',ENTITYAG_TBL.'||l_pk1_column_name;
l_pk_cc_select_list := l_pk_cc_select_list || ',ENTITYAG_TBL.'||l_pk2_column_name;
l_pk_cc_select_list := l_pk_cc_select_list || ',ENTITYAG_TBL.'||l_pk3_column_name;
l_pk_cc_select_list := l_pk_cc_select_list || ',ENTITYAG_TBL.'||l_pk4_column_name;
l_pk_cc_select_list := l_pk_cc_select_list || ',ENTITYAG_TBL.'||l_pk5_column_name;
l_pk_cc_select_list := l_pk_cc_select_list || ', ENTITYAG_TBL.DATA_LEVEL_ID ';
l_pk_cc_select_list := l_pk_cc_select_list || REPLACE(l_all_dl_cols,',',', ENTITYAG_TBL.');
l_additional_class_Code_query := ' SELECT -2910 FROM DUAL ';
code_debug(l_api_name ||' l_pk_cc_select_list '||l_pk_cc_select_list);
l_dynamic_sql := 'SELECT MAX(ROW_IDENTIFIER)+1 FROM '||p_interface_table_name||
' WHERE DATA_SET_ID = :data_Set_id ';
l_dynamic_sql:= ' SELECT '||p_extra_column_values||', '||p_process_status; -- p_extra_column_values, PROCESS_STATUS
l_dynamic_sql:= ' SELECT '||p_process_status||' '; -- PROCESS_STATUS
l_dynamic_sql := l_dynamic_sql || l_pk_cc_select_list;
' ,:current_user_id '||--LAST_UPDATED_BY
' ,SYSDATE '||--LAST_UPDATE_DATE
' ,:current_login_id '||--LAST_UPDATE_LOGIN
' FROM FND_DESCR_FLEX_COLUMN_USAGES ATTR_TBL, '||
' EGO_FND_DF_COL_USGS_EXT ATTR_EXT_TBL, '||
' (SELECT /*+ FULL (ASSOC_TBL) */ TRANSACTION_ID, '||
--Bug7315142,hint added to increase the performance
' ROWNUM ROW_ID, '||
' APPLICATION_ID, '||
' ATTR_GROUP_TBL.DESCRIPTIVE_FLEXFIELD_NAME, '||
' ATTR_GROUP_TBL.DESCRIPTIVE_FLEX_CONTEXT_CODE, '||
' ATTR_GROUP_TBL.ATTR_GROUP_ID, '||
' ASSOC_TBL.DATA_LEVEL '||
' '||l_inner_pk_cc_dl_col_list||' '||
' FROM EGO_FND_DSC_FLX_CTX_EXT ATTR_GROUP_TBL, '||
' EGO_OBJ_AG_ASSOCS_B ASSOC_TBL, '||
' ('||p_target_entity_sql||') ENTITY '||
' WHERE '||
' ATTR_GROUP_TBL.DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type ';
' ( SELECT 1 '||
' FROM FND_DESCR_FLEX_COLUMN_USAGES INNER_ATTR_TBL '||
' WHERE INNER_ATTR_TBL.APPLICATION_ID = ATTR_TBL.APPLICATION_ID '||
' AND INNER_ATTR_TBL.DESCRIPTIVE_FLEXFIELD_NAME = ATTR_TBL.DESCRIPTIVE_FLEXFIELD_NAME '||
' AND INNER_ATTR_TBL.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR_TBL.DESCRIPTIVE_FLEX_CONTEXT_CODE '||
' AND INNER_ATTR_TBL.REQUIRED_FLAG = ''Y'' '||
' AND INNER_ATTR_TBL.DEFAULT_VALUE IS NULL '||
' ) ';
l_col_to_insert_list:= p_extra_column_names||' ,PROCESS_STATUS ';
l_col_to_insert_list:= ' PROCESS_STATUS ';
l_col_to_insert_list:= l_col_to_insert_list ||
' ,ATTR_GROUP_TYPE '||
' ,DATA_SET_ID '||
' ,REQUEST_ID '||
' ,ROW_IDENTIFIER '||
' ,ATTR_GROUP_INT_NAME '||
' ,ATTR_INT_NAME '||
' ,ATTR_VALUE_NUM '||
' ,ATTR_VALUE_STR '||
' ,ATTR_VALUE_DATE '||
l_pk_cc_dl_col_list ||
' ,TRANSACTION_TYPE '||
' ,ATTR_GROUP_ID '||
' ,TRANSACTION_ID '||
' ,CREATED_BY '||
' ,CREATION_DATE '||
' ,LAST_UPDATED_BY '||
' ,LAST_UPDATE_DATE '||
' ,LAST_UPDATE_LOGIN ';
l_dynamic_sql := ' INSERT INTO '||p_interface_table_name||
' ( '||l_col_to_insert_list ||' ) '||
l_dynamic_sql;
x_msg_data := 'Executing - EGO_USER_ATTRS_BULK_PVT.Insert_Default_Val_Rows: '||SQLERRM;
END Insert_Default_Val_Rows;
SELECT LOOKUP_CODE DATA_LEVEL_INTERNAL_NAME
,MEANING DATA_LEVEL_DISPLAY_NAME
,DECODE(ATTRIBUTE2, 1, ATTRIBUTE3,
2, ATTRIBUTE5,
3, ATTRIBUTE7,
'NONE') DATA_LEVEL_COLUMN
,DECODE(ATTRIBUTE2, 1, ATTRIBUTE4,
2, ATTRIBUTE6,
3, ATTRIBUTE8,
'NONE') DL_COL_DATA_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
AND ATTRIBUTE1 = cp_object_name
AND LANGUAGE = USERENV('LANG')
ORDER BY ATTRIBUTE2;
SELECT PK1_COLUMN_NAME,
PK2_COLUMN_NAME,
PK3_COLUMN_NAME,
PK4_COLUMN_NAME,
PK5_COLUMN_NAME
INTO l_pk1_column_name,
l_pk2_column_name,
l_pk3_column_name,
l_pk4_column_name,
l_pk5_column_name
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT FLEX_EXT.APPLICATION_VL_NAME EXT_VL_NAME,
FLEX.APPLICATION_TABLE_NAME EXT_TABLE_NAME
INTO l_ext_vl_table_name,
l_ext_b_table_name
FROM FND_DESCRIPTIVE_FLEXS FLEX,
EGO_FND_DESC_FLEXS_EXT FLEX_EXT
WHERE FLEX.APPLICATION_ID = FLEX_EXT.APPLICATION_ID(+)
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = FLEX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FLEX.APPLICATION_ID = p_application_id
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
' SELECT DISTINCT ATTR_GROUP_INT_NAME
FROM '||p_interface_table_name||' UAI1
WHERE DATA_SET_ID = :data_set_id
AND ATTR_GROUP_TYPE = :attr_group_type
AND UAI1.PROCESS_STATUS = '||G_PS_IN_PROCESS||' '
USING p_data_set_id, p_attr_group_type;
l_dynamic_sql := ' UPDATE '||p_interface_table_name||' INTRFC '||
' SET PROCESS_STATUS = '||p_new_status||
' WHERE PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND ATTR_GROUP_INT_NAME = '''||l_attr_group_int_name||''' '||
' AND ATTR_GROUP_TYPE = '''||p_attr_group_type||''' '||
' AND DATA_SET_ID = :data_set_id '||
' AND EXISTS ( SELECT 1 '||
' FROM '||l_ext_vl_table_name||
' WHERE 1=1 ';
' = NVL((SELECT ATTR_VALUE_NUM '||
' FROM '||p_interface_table_name||' UAI '||
' WHERE UAI.DATA_SET_ID = :data_set_id '||
' AND UAI.ATTR_GROUP_INT_NAME = '''||l_attr_group_int_name||''' '||
' AND UAI.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI.ATTR_INT_NAME = '''||l_attr_metadata_table(i).ATTR_NAME||''' '||
' AND UAI.ROW_IDENTIFIER = INTRFC.ROW_IDENTIFIER '||
' ) '||
' ,'||G_NULL_TOKEN_NUM||' ) ';
' = NVL((SELECT ATTR_VALUE_DATE '||
' FROM '||p_interface_table_name||' UAI '||
' WHERE UAI.DATA_SET_ID = :data_set_id '||
' AND UAI.ATTR_GROUP_INT_NAME = '''||l_attr_group_int_name||''' '||
' AND UAI.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI.ATTR_INT_NAME = '''||l_attr_metadata_table(i).ATTR_NAME||''' '||
' AND UAI.ROW_IDENTIFIER = INTRFC.ROW_IDENTIFIER '||
' ) '||
' ,'||G_NULL_TOKEN_DATE||' ) ';
' = NVL((SELECT ATTR_VALUE_STR '||
' FROM '||p_interface_table_name||' UAI '||
' WHERE UAI.DATA_SET_ID = :data_set_id '||
' AND UAI.ATTR_GROUP_INT_NAME = '''||l_attr_group_int_name||''' '||
' AND UAI.PROCESS_STATUS = '||G_PS_IN_PROCESS||
' AND UAI.ATTR_INT_NAME = '''||l_attr_metadata_table(i).ATTR_NAME||''' '||
' AND UAI.ROW_IDENTIFIER = INTRFC.ROW_IDENTIFIER '||
' ) '||
' ,'||G_NULL_TOKEN_STR||' ) ';