[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();
p_tvs_select_clob IN CLOB,
p_attr_name IN VARCHAR2,
p_attr_group_name IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_sql_1_ub OUT NOCOPY NUMBER,
x_sql_ub OUT NOCOPY NUMBER,
x_dynamic_sql_1_v_type OUT NOCOPY dbms_sql.varchar2a,
x_dynamic_sql_v_type OUT NOCOPY dbms_sql.varchar2a
) IS
l_api_name VARCHAR2(30) := 'Prepare_Dynamic_Sqls_Clob';
l_dynamic_sql_1_v_type(1) := 'UPDATE '||p_interface_table_name||' UAI1 SET PROCESS_STATUS = PROCESS_STATUS + DECODE((';
AND '||l_data_type_clause||' IS NOT NULL AND TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||''''; -- bug 13774267
No need to check attribute value against value set in case of Delete. The check is skipped only for ATTR_VALUE*.
The validation need to be done if the user passes ATTR_DISP_VALUE. hence didn't add the condition for l_dynamic_sql_v_type
*/
x_sql_1_ub := l_index_1;
l_dynamic_sql_v_type(1) := 'UPDATE '||p_interface_table_name||' UAI1 SET '||l_data_type_clause||' = NVL(';
l_dynamic_sql_clob_part1 := p_tvs_select_clob;
l_dynamic_sql_clob_part2 := p_tvs_select_clob;
'SELECT TRANSACTION_ID
FROM '||p_interface_table_name||' UAI1
WHERE UAI1.DATA_SET_ID = :data_set_id
AND ROWNUM = 1';
l_dynamic_sql_delete_post VARCHAR2(32767); /*Uncommeneted code for bug 8485287*/
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 DATA_LEVEL_ID
,DATA_LEVEL_NAME
,USER_DATA_LEVEL_NAME
,PK1_COLUMN_NAME
,PK2_COLUMN_NAME
,PK3_COLUMN_NAME
,PK4_COLUMN_NAME
,PK5_COLUMN_NAME
,PK1_COLUMN_TYPE
,PK2_COLUMN_TYPE
,PK3_COLUMN_TYPE
,PK4_COLUMN_TYPE
,PK5_COLUMN_TYPE
FROM EGO_DATA_LEVEL_VL
WHERE DATA_LEVEL_ID IN ( SELECT DATA_LEVEL_ID
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = p_attr_group_id);
l_ext_table_select1 VARCHAR2(32767); -- Bug 13414358
l_tvs_select_clob CLOB;
SELECT DATA_LEVEL_ID
INTO l_item_rev_dl_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_REVISION_LEVEL';
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 /*+ no_unnest */ 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 /*+ UNNEST HASH_SJ */ DISTINCT UAI2.ROW_IDENTIFIER /* bug#9678667 Change apr30 */
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 -- Commenting for Bug 9336604
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||')';
SELECT DISTINCT UAI2.ROW_IDENTIFIER
BULK COLLECT INTO l_row_ids
FROM '||p_interface_table_name||' UAI2
WHERE UAI2.DATA_SET_ID = :data_set_id
AND UAI2.PROCESS_STATUS = '||G_PS_IN_PROCESS||'
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_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 = '||p_data_set_id||'
AND UAI1.ATTR_GROUP_TYPE = '''||p_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 = l_row_ids(i) AND (UAI1.ATTR_GROUP_TYPE <> '''||INV_EBI_UTIL.G_BOM_COMPONENTMGMT_GROUP||''' OR (UAI1.ATTR_GROUP_TYPE = '''||INV_EBI_UTIL.G_BOM_COMPONENTMGMT_GROUP||''' AND UAI1.TRANSACTION_TYPE <> '''||'CREATE'||'''));
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 --
-------------------------------------------------------
/* Bug 13414358 - Start */
-- Modifed the query to use exists caluse for performance bug 13414358.
l_ext_table_select1 :=
'(SELECT 1
FROM '||l_ext_b_table_name||'
WHERE ATTR_GROUP_ID = UAI2.ATTR_GROUP_ID
AND ROWNUM < 2 ';
l_ext_table_select1 := l_ext_table_select1 ||l_concat_pk_cols_UAI2;
l_ext_table_select1 := l_ext_table_select1||')';
' 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 = Nvl(( SELECT '''||EGO_USER_ATTRS_DATA_PVT.G_UPDATE_MODE||'''
FROM DUAL
WHERE EXISTS '||l_ext_table_select1||
'), '''||EGO_USER_ATTRS_DATA_PVT.G_CREATE_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)';
'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, UAI1.ATTR_GROUP_INT_NAME) IN '||
' (SELECT /*+ UNNEST CARDINALITY(UAI2,10) INDEX(UAI2,EGO_ITM_USR_ATTR_INTRFC_N3) */ '|| /* Bug 9678667 */
' UAI2.ROW_IDENTIFIER, UAI2.ATTR_GROUP_INT_NAME '||
' 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 ||
' )'
USING p_data_set_id, p_data_set_id;
' SELECT /*+ use_concat index(UAI1,EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */
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||' OR UAI1.PROCESS_STATUS > '||G_PS_IN_PROCESS||')' /* Bug 9678667 */
USING p_data_set_id;
'UPDATE /*+ INDEX(UAI1,EGO_ITM_USR_ATTR_INTRFC_N3) */ '||p_interface_table_name||' UAI1 '||
/* Bug 9678667 */
' SET UAI1.PROCESS_STATUS = '||G_PS_GENERIC_ERROR||
' WHERE UAI1.DATA_SET_ID = :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 = :data_set_id '||--p_data_set_id||
' AND UAI2.PROCESS_STATUS >= '||G_PS_BAD_ATTR_OR_AG_METADATA ||')';
'SELECT -- /*+ LEADING(A, EXT, UOM, FLX_EXT, DISTINCT_ATTRS,AG_DL) INDEX(FLX_EXT EGO_FND_DSC_FLX_CTX_EXT_U2) INDEX(a FND_DESCR_FLEX_COL_USAGES_U2) USE_HASH(DISTINCT_ATTRS)*/
-- /*+ 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 /*+ NO_MERGE */
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,
(SELECT application_id, descriptive_flexfield_name, descriptive_flex_context_code,
application_column_name, end_user_column_name, DEFAULT_VALUE,
required_flag, flex_value_set_id, column_seq_num
FROM fnd_descr_flex_column_usages
WHERE ''Y'' = enabled_flag
AND application_id IN (SELECT DISTINCT application_id
FROM ego_fnd_dsc_flx_ctx_ext)) 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 = ext.application_id
AND flx_ext.descriptive_flexfield_name = ext.descriptive_flexfield_name
AND flx_ext.descriptive_flex_context_code = ext.descriptive_flex_context_code
--AND ''Y'' = a.enabled_flag
AND a.application_id = flx_ext.application_id
AND a.descriptive_flexfield_name = flx_ext.descriptive_flexfield_name
AND a.descriptive_flex_context_code = flx_ext.descriptive_flex_context_code
AND a.application_column_name = ext.application_column_name
AND a.end_user_column_name = distinct_attrs.attr_int_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
(
/* Fix for bug#9678667 - added below hint */
SELECT /*+ index(UAI2,EGO_ITM_USR_ATTR_INTRFC_N1) */
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(l_var).ATTR_GROUP_INT_NAME,
l_attr_metadata_rec(l_var).ATTR_GROUP_ID,
l_attr_metadata_rec(l_var).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
(
/* Fix for bug#9678667 - added below hint */
SELECT /*+ index(UAI2,EGO_ITM_USR_ATTR_INTRFC_N1) */
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(l_var).ATTR_GROUP_INT_NAME,
l_attr_metadata_rec(l_var).ATTR_GROUP_ID,
l_attr_metadata_rec(l_var).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 ';
'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((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 OR TO_NUMBER(FLEX_VALUE) = ATTR_VALUE_NUM)) /**bug 13589373**/
),
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)),
AND (TO_NUMBER(FLEX_VALUE) = ATTR_VALUE_NUM OR FLEX_VALUE_MEANING = ATTR_DISP_VALUE)),/*Bug:9735836,if number is decimal and less than 1,to char will remove "0" */
(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 = (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 OR FLEX_VALUE = TO_CHAR(ATTR_VALUE_DATE,''YYYY-MM-DD HH24:MI:SS'')) /**bug 13589373**/
),
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'') OR FLEX_VALUE_MEANING = ATTR_DISP_VALUE)),
(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 = (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 OR FLEX_VALUE = TO_CHAR(ATTR_VALUE_DATE,''YYYY-MM-DD HH24:MI:SS''))) /**bug 13589373**/
)
),
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'') OR FLEX_VALUE_MEANING = ATTR_DISP_VALUE)),
(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 = (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 OR FLEX_VALUE = ATTR_VALUE_STR) /**bug 13589373**/
),
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 OR FLEX_VALUE_MEANING = ATTR_DISP_VALUE)),
(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 /*+ index(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N1) */ '||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 LENGTHB(ATTR_VALUE_STR) > :max_allowed_size'; --for bug 9748517, use byte size to determin size for multi-byte language
'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 /*+ index(EGO_ITM_USR_ATTR_INTRFC, EGO_ITM_USR_ATTR_INTRFC_N1) NO_EXPAND */ '||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;
'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, UAI1.ATTR_GROUP_INT_NAME) IN '||
' (SELECT /*+ UNNEST CARDINALITY(UAI2,10) INDEX(UAI2,EGO_ITM_USR_ATTR_INTRFC_N3) */ '|| /* Bug 9678667 */
' UAI2.ROW_IDENTIFIER, UAI2.ATTR_GROUP_INT_NAME '||
' 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 ||
' )'
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)
)
)'||
l_tvs_where_clause_clob2;
DECODE(UAI1.TRANSACTION_TYPE, ''UPDATE'','||l_value_from_ext_table||'
,''CREATE'',NULL
, ''SYNC'','||l_value_from_ext_table||'
,NULL)
)
)'||
DBMS_LOB.SUBSTR(l_tvs_where_clause_clob,DBMS_LOB.GETLENGTH(l_tvs_where_clause_clob), l_attrname_end_index);
SELECT REPLACE(l_tvs_where_clause_clob,':$OBJECT$','UAI1') INTO l_tvs_where_clause_clob FROM DUAL;
Use l_tvs_select_clob instead of l_tvs_select
Use l_tvs_num_val_check_sel_clob instead of l_tvs_num_val_check_select
Use l_tvs_date_val_check_sel_clob instead of l_tvs_date_val_check_select
Use l_tvs_str_val_check_sel_clob instead of l_tvs_str_val_check_select
*/
l_tvs_where_clause_clob := RTRIM(LTRIM(l_tvs_where_clause_clob));
l_tvs_select_clob := '(SELECT DISTINCT '||l_tvs_col||' FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause_clob||'
AND ROWNUM = 1
AND '||l_tvs_val_col||' = UAI1.ATTR_DISP_VALUE )';
l_tvs_num_val_check_sel_clob := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause_clob||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_NUM )';
l_tvs_date_val_check_sel_clob := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause_clob||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_DATE_VALUE )';
l_tvs_str_val_check_sel_clob := '(SELECT COUNT(*) FROM '||l_tvs_table_name||'
WHERE '||l_tvs_where_clause_clob||'
AND ROWNUM = 1
AND '||l_tvs_col||' = UAI1.ATTR_VALUE_STR )';
code_debug(' The Length of TVS query constructed is :'||dbms_lob.getlength(l_tvs_select_clob), 3);
l_clob_length := dbms_lob.getlength(l_tvs_select_clob);
dbms_lob.read(l_tvs_select_clob, l_amount, l_offset, l_buffer);
'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
-- Bug 10151142 : Start
/*
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';
p_tvs_select_clob => l_tvs_select_clob,
p_attr_name => l_attr_metadata_table(y).ATTR_NAME,
p_attr_group_name => l_attr_group_metadata_obj.ATTR_GROUP_NAME,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_sql_1_ub => l_sql_1_ub,
x_sql_ub => l_sql_ub,
x_dynamic_sql_1_v_type => l_dynamic_sql_1_v_type,
x_dynamic_sql_v_type => l_dynamic_sql_v_type
);
'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';
p_tvs_select_clob => l_tvs_select_clob,
p_attr_name => l_attr_metadata_table(y).ATTR_NAME,
p_attr_group_name => l_attr_group_metadata_obj.ATTR_GROUP_NAME,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_sql_1_ub => l_sql_1_ub,
x_sql_ub => l_sql_ub,
x_dynamic_sql_1_v_type => l_dynamic_sql_1_v_type,
x_dynamic_sql_v_type => l_dynamic_sql_v_type
);
'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';
p_tvs_select_clob => l_tvs_select_clob,
p_attr_name => l_attr_metadata_table(y).ATTR_NAME,
p_attr_group_name => l_attr_group_metadata_obj.ATTR_GROUP_NAME,
x_return_status => l_return_status,
x_msg_data => l_msg_data,
x_sql_1_ub => l_sql_1_ub,
x_sql_ub => l_sql_ub,
x_dynamic_sql_1_v_type => l_dynamic_sql_1_v_type,
x_dynamic_sql_v_type => l_dynamic_sql_v_type
);
'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 /*+ INDEX(UAI1,EGO_ITM_USR_ATTR_INTRFC_N3) */ /* Bug 9678667 */ '||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 = :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 = :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||'''';
code_debug('l_dynamic_sql to update Transaction type for MR Row '||l_attr_group_metadata_obj.ATTR_GROUP_NAME ||':' || l_dynamic_sql,2);
code_debug(' Before Inserting Default rows for AG :'||l_attr_group_intf_rec.ATTR_GROUP_INT_NAME );
So the inner select query with group by is returing multiple AG records.
Hence reverting the changes for bug 14044344 in this file.
In validation mode,
value for prog_int_chr2 is null for template filled and AG default records.
In non validation mode,
value N is set if it is update.
Value Y if same item is created in this batch.
Hence no harm in hard-coding prog_int_chr2 with value N while defaulting at AG level.
Removed this column from inner select query as it returns morethan one entry for single AG (Reason: this column holds different values for user enetered records and
defaulting records).
For more details about this issue refer to update *** GNOOKALA 10/29/12 02:11 am *** in bug 14672950 */
--IF FND_API.TO_BOOLEAN(EGO_USER_ATTRS_COMMON_PVT.has_column_in_table(p_interface_table_name, 'CHANGE_ID')) THEN l_extra_column_str := l_extra_column_str || ', CHANGE_ID'; END IF;
'INSERT INTO '||p_interface_table_name||'
( TRANSACTION_ID,
ATTR_GROUP_TYPE,
PROCESS_STATUS,
DATA_SET_ID,
ROW_IDENTIFIER,
ATTR_GROUP_INT_NAME,
ATTR_INT_NAME,
ATTR_VALUE_NUM,
ATTR_VALUE_STR,
ATTR_VALUE_DATE,
TRANSACTION_TYPE,
'||l_concat_pk_cols_sel||'
ATTR_GROUP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE' || l_extra_column_str || '
)
SELECT DISTINCT TRANSACTION_ID,
FL_COL.DESCRIPTIVE_FLEXFIELD_NAME,
'||G_PS_IN_PROCESS||',
DATA_SET_ID,
ROW_IDENTIFIER,
ATTR_GROUP_INT_NAME,
FL_COL.END_USER_COLUMN_NAME,
TO_NUMBER(DECODE(ATTR_EXT.DATA_TYPE, ''N'', FL_COL.DEFAULT_VALUE,
NULL)),
DECODE(ATTR_EXT.DATA_TYPE, ''A'', FL_COL.DEFAULT_VALUE,
''C'', FL_COL.DEFAULT_VALUE,
NULL),
DECODE(ATTR_EXT.DATA_TYPE, ''X'', EGO_USER_ATTRS_BULK_PVT.GET_DATE(FL_COL.DEFAULT_VALUE, NULL),
''Y'', EGO_USER_ATTRS_BULK_PVT.GET_DATE(FL_COL.DEFAULT_VALUE, NULL),
NULL),
'''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||''',
'|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE' || l_extra_column_val || '
FROM (SELECT /*+ NO_MERGE index(A,EGO_ITM_USR_ATTR_INTRFC_N1) */
MAX(TRANSACTION_ID) TRANSACTION_ID,
DATA_SET_ID,
ROW_IDENTIFIER,
ATTR_GROUP_INT_NAME,
'|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID
FROM '||p_interface_table_name||' A
WHERE DATA_SET_ID = :data_set_id
AND PROCESS_STATUS = '||G_PS_IN_PROCESS|| '
AND ATTR_GROUP_INT_NAME = :attr_group_int_name
AND Bitand(PROCESS_STATUS, 64) = 0
AND TRANSACTION_TYPE = '''|| EGO_USER_ATTRS_DATA_PVT.G_CREATE_MODE||'''
AND ATTR_GROUP_TYPE = :attr_group_type
GROUP BY DATA_SET_ID,
ROW_IDENTIFIER,
ATTR_GROUP_INT_NAME,
'|| l_concat_pk_cols_sel ||'
ATTR_GROUP_ID
) A,
FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
EGO_FND_DF_COL_USGS_EXT ATTR_EXT
WHERE NOT EXISTS (SELECT /*+ no_unnest index(B,EGO_ITM_USR_ATTR_INTRFC_U1) */ NULL
FROM '||p_interface_table_name||' B
WHERE DATA_SET_ID = :data_set_id
AND B.ATTR_INT_NAME = FL_COL.END_USER_COLUMN_NAME
AND A.ROW_IDENTIFIER = B.ROW_IDENTIFIER
AND B.ATTR_GROUP_INT_NAME = :attr_group_int_name
)
AND :attr_group_int_name = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FL_COL.APPLICATION_ID = '||p_application_id||'
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = :attr_group_type
AND FL_COL.ENABLED_FLAG = ''Y''
AND (FL_COL.DEFAULT_VALUE IS NOT NULL OR FL_COL.REQUIRED_FLAG = ''Y'')
AND ATTR_EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ATTR_EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME';
code_debug(' After Inserting Default rows for AG :'||l_attr_group_intf_rec.ATTR_GROUP_INT_NAME );
'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 /*+ index(A,EGO_ITM_USR_ATTR_INTRFC_N1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /*+ no_unnest index(B,EGO_ITM_USR_ATTR_INTRFC_U1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /* Fix for bug#9678667 */
--AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
/* Bug 9678667 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 /*+ index(A,EGO_ITM_USR_ATTR_INTRFC_N1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /*+ no_unnest index(B,EGO_ITM_USR_ATTR_INTRFC_U1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /* Fix for bug#9678667 */
--AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
/* Bug 9678667 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 /*+ index(A,EGO_ITM_USR_ATTR_INTRFC_N1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /*+ no_unnest index(B,EGO_ITM_USR_ATTR_INTRFC_U1) */ /* Fix for bug#9678667 */
/* Bug 9678667 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 /* Fix for bug#9678667 */
--AND B.TRANSACTION_TYPE = A.TRANSACTION_TYPE
/* Bug 9678667 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';
SELECT BUSINESS_EVENT_NAME
INTO l_new_post_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
SELECT BUSINESS_EVENT_FLAG
INTO l_new_post_event_enabled_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID;
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';
l_dynamic_sql := ' SELECT EXTVL1.EXTENSION_ID , UAI1.ATTR_GROUP_INT_NAME, UAI1.TRANSACTION_TYPE, UAI1.ROW_IDENTIFIER ';
l_dynamic_sql_delete_post := ' SELECT NULL , 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_1 := l_dynamic_sql_1 || l_ext_table_select;
' AND UAI1.TRANSACTION_TYPE <> '''||EGO_USER_ATTRS_DATA_PVT.G_DELETE_MODE||'''';
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 ROW_IDENTIFIER = :row_identifier';
'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 = :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 = :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)'
USING p_data_set_id, p_data_set_id; */ /*Fix for bug#9678667. Literal to bind*/
'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, UAI1.ATTR_GROUP_INT_NAME) IN '||
' (SELECT /*+ UNNEST CARDINALITY(UAI2,10) INDEX(UAI2,EGO_ITM_USR_ATTR_INTRFC_N3) */ '|| /* Bug 9678667 */
' UAI2.ROW_IDENTIFIER, UAI2.ATTR_GROUP_INT_NAME '||
' 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 ||
' )'
USING p_data_set_id, p_data_set_id;
' 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);
' 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.
*/
-- Bug 10097738 : Start
IF (l_attr_group_metadata_obj.MULTI_ROW_CODE = 'Y') THEN
-- The below function returns 'T' or 'F'
l_column_exists:=EGO_USER_ATTRS_DATA_PVT.HAS_COLUMN_IN_TABLE_VIEW(l_ext_b_table_name,'UNIQUE_VALUE');
l_unique_value := ', EGO_EXTFWK_S.CURRVAL '; -- inserting the ext id value in UNIQUE_VALUE column for MR UDAs
'INSERT INTO '||l_ext_b_table_name||
'(REQUEST_ID, EXTENSION_ID'||
l_no_alias_cc_pk_dl_list||
l_unique_value_col||' '; /* Bug 10097738 */
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||
l_unique_value||' '; /* Bug 10097738 */
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
-- Bug 9851212 : Removed product specific table name.
l_dynamic_sql := ' UPDATE '||l_ext_b_table_name||'
SET REQUEST_ID = :REQUEST_ID
WHERE ATTR_GROUP_ID = :atr_grp_id
AND (REQUEST_ID'||l_concat_pk_cols||') IN
( SELECT /*+ cardinality(EGO_ITM_USR_ATTR_INTRFC,10) */
(ROW_IDENTIFIER * -2)'||l_concat_pk_cols||'
FROM '||p_interface_table_name||'
WHERE DATA_SET_ID = :DATA_SET_ID
AND ATTR_GROUP_ID = :ATTR_GROUP_ID
AND PROCESS_STATUS = '||G_PS_IN_PROCESS||' )';
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';
SELECT BUSINESS_EVENT_NAME
INTO l_new_post_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
SELECT BUSINESS_EVENT_FLAG
INTO l_new_post_event_enabled_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = l_attr_group_metadata_obj.ATTR_GROUP_ID;
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 = :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 = :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 ';
l_dynamic_sql := 'SELECT DISTINCT ITEM_CATALOG_GROUP_ID '||
' FROM ('||p_target_entity_sql||')';
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, '||
-- Bug 13414358 : Start
/* Instead of inserting transaction type as CREATE/UPDATE insert as SYNC directly, so that the later part of the
code will take care of resolving the TRANSACTION type. */
-- ' 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 ||
-- Bug 13414358 : Start
/* Removing the join to the view l_ext_vl_name, as we do not need to resolve the transaction type at this point. */
--' ) TEMPLRTCQ) TEMPLRTCQ, '||
--' '||l_ext_vl_name||' EXT '||
' ) TEMPLRTCQ) TEMPLRTCQ '||
-- Bug 13414358 : Start
'WHERE 1 = 1 '||
-- l_ag_id_clause||' '|| -- Bug 13414358
' AND '||l_dl_ext_trtcq_join||' AND '||l_dl_intfrtcq_trtcq_join||--the data level joins bugfix:5401212
-- ' AND '|| -- Bug 13414358
-- l_ext_templrtcq_join||' '|| -- Bug 13414358
' 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
/* Begin Bug 13729672 */
,p_comp_seq_id IN NUMBER DEFAULT NULL
,p_bill_seq_id IN NUMBER DEFAULT NULL
,p_structure_type_id IN NUMBER DEFAULT NULL
,p_data_level_column IN VARCHAR2 DEFAULT NULL
,p_datalevel_id IN NUMBER DEFAULT NULL
,p_context_id IN NUMBER DEFAULT NULL
,p_transaction_id IN NUMBER DEFAULT NULL
/* End Bug 13729672 */
,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 /*+ index(uai EGO_ITM_USR_ATTR_INTRFC_N2 ) */ 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 /*+ leading(ATTR_EXT_TBL,ATTR_TBL,INNER_ATTR_TBL) */ /* Bug 9678667 */ '||p_extra_column_values||', '||p_process_status; -- p_extra_column_values, PROCESS_STATUS
l_dynamic_sql:= ' SELECT /*+ leading(ATTR_EXT_TBL,ATTR_TBL,INNER_ATTR_TBL) */ /* Bug 9678667 */ '||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 := ' SELECT ATTR_GROUP_ID FROM '||l_ext_vl_table_name||' WHERE ROWNUM=1 ';
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||' ) ';