[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DELETE_ACD_TYPE CONSTANT VARCHAR2(10) := 'DELETE';
G_UPDATE_TX_TYPE CONSTANT VARCHAR2(10) := 'UPDATE'; --2nd
G_DELETE_TX_TYPE CONSTANT VARCHAR2(10) := 'DELETE'; --1st
SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
SELECT change_id
FROM ENG_REVISED_ITEMS
WHERE revised_item_sequence_id = c_change_line_id ;
SELECT item.concatenated_segments item_name
FROM MTL_SYSTEM_ITEMS_KFV item
WHERE item.organization_id = c_organization_id
AND item.inventory_item_id = c_inventory_item_id ;
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
-- p_to_table_name is not passed in any call to the below function, So the currently update expression
-- having the hint BYPASS_UJVC will not execute at all, as result removing the hint BYPASS_UJVC.
-- If any future use is planned to use the below function by passing the p_to_table_name,
-- we have to re-evaluate the hint in update expression.
FUNCTION Get_Table_Columns_List (
p_application_id IN NUMBER
,p_from_table_name IN VARCHAR2
,p_from_cols_to_exclude_list IN VARCHAR2 DEFAULT NULL
,p_from_table_alias_prefix IN VARCHAR2 DEFAULT NULL
,p_to_table_name IN VARCHAR2 DEFAULT NULL
,p_to_table_alias_prefix IN VARCHAR2 DEFAULT NULL
,p_in_line_view_where_clause IN VARCHAR2 DEFAULT NULL
,p_cast_date_cols_to_char IN BOOLEAN DEFAULT FALSE
,p_exclude_dff IN BOOLEAN DEFAULT FALSE
)
RETURN VARCHAR2
IS
l_dynamic_sql VARCHAR2(20000);
l_in_update_mode BOOLEAN;
l_update_expression VARCHAR2(32767);
l_skip_common_b_tl_cols := '''INVENTORY_ITEM_ID'', ''ORGANIZATION_ID'', ''REVISION_ID'', ''LAST_UPDATED_BY'', ''CREATED_BY'' , ''CREATION_DATE'', ''LAST_UPDATE_LOGIN'' , ''LAST_UPDATE_DATE'' ,''ITEM_CATALOG_GROUP_ID'', ''ATTR_GROUP_ID''';
l_dynamic_sql := ' SELECT C.COLUMN_NAME, C.COLUMN_TYPE' ||
' FROM FND_COLUMNS C, FND_TABLES T' ||
' WHERE T.TABLE_NAME = :1'||
' AND T.APPLICATION_ID = :2'||
' AND C.APPLICATION_ID = T.APPLICATION_ID'||
' AND C.TABLE_ID = T.TABLE_ID';
' SELECT C_TL.COLUMN_NAME ' ||
' FROM FND_COLUMNS C_TL, FND_TABLES T_TL' ||
' WHERE T_TL.TABLE_NAME = '''|| l_exclude_table_name ||''''||
' AND C_TL.APPLICATION_ID = T_TL.APPLICATION_ID'||
' AND T_TL.APPLICATION_ID = T.APPLICATION_ID ' ||
' AND C_TL.TABLE_ID = T_TL.TABLE_ID' ||
' AND C_TL.COLUMN_NAME NOT IN ('||l_skip_common_b_tl_cols || '))';
l_in_update_mode := (p_to_table_name IS NOT NULL);
IF (l_in_update_mode) THEN
l_table_column_names_list := l_table_column_names_list ||
p_from_table_alias_prefix || '_' ||
l_column_name || ',' ||
p_to_table_alias_prefix || '.' ||
l_column_name || ' ' ||
p_to_table_alias_prefix || '_' ||
l_column_name;
l_update_expression := l_update_expression ||
p_to_table_alias_prefix || '_' ||
l_column_name || '=' ||
p_from_table_alias_prefix || '_' ||
l_column_name || ',';
IF (LENGTH(l_update_expression) > 0) THEN
l_update_expression := RTRIM(l_update_expression, ',');
/*Added below for bug 8939021. Commented the below update statement*/
--IF (l_in_update_mode) THEN
-- l_table_column_names_list := 'UPDATE /*+ BYPASS_UJVC */ (SELECT '||l_table_column_names_list||
-- ' FROM '||p_from_table_name||' '||p_from_table_alias_prefix||
-- ','||p_to_table_name||' '||p_to_table_alias_prefix||' '||
-- p_in_line_view_where_clause||') SET '||l_update_expression;
SELECT EXT.ATTR_ID,
FLX_EXT.ATTR_GROUP_ID,
FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME,
A.END_USER_COLUMN_NAME,
TL.FORM_LEFT_PROMPT,
EXT.DATA_TYPE,
FC.MEANING DATA_TYPE_MEANING,
A.COLUMN_SEQ_NUM,
EXT.UNIQUE_KEY_FLAG,
A.DEFAULT_VALUE,
EXT.INFO_1,
VS.MAXIMUM_SIZE,
A.REQUIRED_FLAG,
A.APPLICATION_COLUMN_NAME,
VS.FLEX_VALUE_SET_ID,
VS.VALIDATION_TYPE,
VS.MINIMUM_VALUE,
VS.MAXIMUM_VALUE,
EXT.UOM_CLASS,
UOM.UOM_CODE,
EXT.VIEW_IN_HIERARCHY_CODE,
EXT.EDIT_IN_HIERARCHY_CODE
FROM EGO_FND_DSC_FLX_CTX_EXT FLX_EXT,
FND_DESCR_FLEX_COLUMN_USAGES A,
FND_DESCR_FLEX_COL_USAGE_TL TL,
EGO_FND_DF_COL_USGS_EXT EXT,
EGO_VS_FORMAT_CODES_V FC,
FND_FLEX_VALUE_SETS VS,
MTL_UNITS_OF_MEASURE UOM
WHERE FLX_EXT.APPLICATION_ID = cp_application_id
AND FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND ( FLX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
OR cp_attr_group_name IS NULL )
AND A.APPLICATION_ID = cp_application_id
AND A.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND ( A.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
OR cp_attr_group_name IS NULL )
AND TL.APPLICATION_ID = cp_application_id
AND TL.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND ( TL.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
OR cp_attr_group_name IS NULL )
AND EXT.APPLICATION_ID = cp_application_id
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND ( EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
OR cp_attr_group_name IS NULL )
AND FC.LOOKUP_CODE(+) = EXT.DATA_TYPE
AND A.ENABLED_FLAG = 'Y'
AND TL.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
AND TL.LANGUAGE = USERENV('LANG')
AND EXT.APPLICATION_COLUMN_NAME = A.APPLICATION_COLUMN_NAME
AND A.FLEX_VALUE_SET_ID = VS.FLEX_VALUE_SET_ID (+)
AND UOM.UOM_CLASS(+) = EXT.UOM_CLASS
AND UOM.BASE_UOM_FLAG(+) = 'Y'
ORDER BY A.COLUMN_SEQ_NUM;
SELECT change_line_id
FROM EGO_MTL_SY_ITEMS_CHG_VL
WHERE change_line_id = p_change_line_id
AND implementation_date IS NULL
AND acd_type <> 'HISTORY'
AND rownum = 1;
SELECT change_line_id
FROM EGO_ITEMS_ATTRS_CHANGES_VL
WHERE change_line_id = p_change_line_id
AND implementation_date IS NULL
AND acd_type <> 'HISTORY'
AND rownum = 1;
SELECT change_line_id
FROM EGO_GTN_ATTR_CHG_VL
WHERE change_line_id = p_change_line_id
AND implementation_date IS NULL
AND acd_type <> 'HISTORY'
AND rownum = 1;
SELECT change_line_id
FROM EGO_GTN_MUL_ATTR_CHG_VL
WHERE change_line_id = p_change_line_id
AND implementation_date IS NULL
AND acd_type <> 'HISTORY'
AND rownum = 1;
SELECT change_line_id
FROM EGO_MFG_PART_NUM_CHGS
WHERE change_line_id = p_change_line_id
AND implmentation_date IS NULL -- Spell Miss implementation
AND acd_type <> 'HISTORY'
AND rownum = 1;
SELECT extension_id
FROM EGO_ITEM_GTN_ATTRS_B
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision_id IS NULL ;
SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE change_id = c_change_id
AND ( revised_item_sequence_id = c_change_line_id
OR c_change_line_id IS NULL )
AND implementation_date IS NULL
ORDER BY scheduled_date ;
SELECT EXT_TABLE_NAME, EXT_TL_TABLE_NAME, EXT_VL_NAME
INTO l_production_b_table_name, l_production_tl_table_name, l_production_vl_name
FROM EGO_ATTR_GROUP_TYPES_V
WHERE APPLICATION_ID = G_EGO_APPL_ID
AND ATTR_GROUP_TYPE = G_EGO_MASTER_ITEMS ;
'''CREATED_BY'',''CREATION_DATE'',''LAST_UPDATED_BY'','||
'''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN'','||
'''PROGRAM_ID'',''PROGRAM_UPDATE_DATE'',''REQUEST_ID'' ,'||
'''PROGRAM_APPLICATION_ID'',''EGO_MASTER_ITEMS_DFF_CTX'', '||
'''STYLE_ITEM_FLAG'',''STYLE_ITEM_ID'',''GDSN_OUTBOUND_ENABLED_FLAG'','||
'''ITEM_NUMBER'',''DESCRIPTION'',''LAST_SUBMITTED_NIR_ID'',''DEFAULT_MATERIAL_STATUS_ID'',''SERIAL_TAGGING_FLAG''' ;
l_dynamic_sql := 'SELECT '||l_chg_col_names_list||','||
l_b_chg_cols_list||','||
l_tl_chg_cols_list||
' FROM '||l_change_b_table_name ||' B,'||
l_change_tl_table_name ||' TL'||
' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
' AND B.ACD_TYPE = TL.ACD_TYPE'||
' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
' AND TL.LANGUAGE = userenv(''LANG'')'||
' AND B.CHANGE_LINE_ID = :1';
l_attr_name_value_pairs.DELETE();
l_mode_for_current_row := G_UPDATE_TX_TYPE;
IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
Write_Debug('Copy the row into the pending tables with the ACD Type HISTORY');
Write_Debug('Inserting History Row with the ACD Type HISTORY for B table');
l_utility_dynamic_sql := ' INSERT INTO '||l_change_b_table_name||' CT ('||
l_history_b_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID ' ||
' ) SELECT ' || l_history_b_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID FROM ' ||
l_production_b_table_name ||' PT, '||
l_change_b_table_name || ' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6' ;
Write_Debug('After Inserting History Row with the ACD Type HISTORY for B table');
Write_Debug('Inserting History Row with the ACD Type HISTORY for TL table');
l_utility_dynamic_sql := ' INSERT INTO '||l_change_tl_table_name||' CT ('||
l_history_tl_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID '||
' ) SELECT ' || l_history_tl_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID FROM '||
l_production_tl_table_name||' PT, '||
l_change_tl_table_name||' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6'||
' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :7';
Write_Debug('After Inserting History Row with the ACD Type HISTORY for TL table');
EXECUTE IMMEDIATE ' UPDATE '||l_change_b_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||l_change_tl_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
SELECT L.LANGUAGE_CODE, L.INSTALLED_FLAG, L.NLS_LANGUAGE, L.NLS_TERRITORY
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('B', 'I') ;
'''CREATION_DATE'',''LAST_UPDATED_BY'','||
'''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN'','||
'''PROGRAM_ID'',''PROGRAM_UPDATE_DATE'',''REQUEST_ID'' ,''PROGRAM_APPLICATION_ID''';
l_dynamic_sql := 'SELECT '||l_chg_col_names_list||','||
l_b_chg_cols_list||','||
l_tl_chg_cols_list||
' FROM '||l_chg_b_table_name ||' B,'||
l_chg_tl_table_name ||' TL'||
' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
' AND B.ACD_TYPE = TL.ACD_TYPE'||
' AND B.EXTENSION_ID = TL.EXTENSION_ID' ||
' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
' AND B.CHANGE_LINE_ID = :1';
l_attr_name_value_pairs.DELETE();
l_mode_for_current_row := G_UPDATE_TX_TYPE;
l_utility_dynamic_sql := 'UPDATE '||l_chg_b_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3';
l_utility_dynamic_sql := 'UPDATE '||l_chg_tl_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3'||
' AND LANGUAGE = :4';
l_mode_for_current_row := G_UPDATE_TX_TYPE;
ELSIF (l_current_acd_type = G_DELETE_ACD_TYPE) THEN
IF (l_current_production_ext_id IS NULL) THEN
---------------------------------------
-- If ACD Type is DELETE and there's --
-- no production row, we do nothing --
---------------------------------------
l_mode_for_current_row := 'SKIP';
l_mode_for_current_row := G_DELETE_TX_TYPE;
IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
-----------------------------------------------------------
-- Process_Row will only process our pending B table row --
-- in the loop when LANGUAGE is NULL or when LANGUAGE = --
-- SOURCE_LANG, so we insert a History row in that loop --
-----------------------------------------------------------
IF (l_current_row_language IS NULL OR
l_current_row_language = l_current_row_source_lang)
THEN
Write_Debug('Inserting History Record with ACD Type HISTORY into Pending B Table... ' );
l_utility_dynamic_sql := ' INSERT INTO '||l_chg_b_table_name||' CT ('||
l_hist_b_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID) SELECT '||
l_hist_b_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID FROM '||
l_prod_b_table_name||' PT, '||
l_chg_b_table_name || ' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6' ||
' AND PT.EXTENSION_ID = :7'||
' AND CT.EXTENSION_ID = :8' ;
Write_Debug('Inserting History Record with ACD Type HISTORY into Pending TL Table... ' );
l_utility_dynamic_sql := ' INSERT INTO '||l_chg_tl_table_name||' CT ('||
l_hist_tl_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID) ' ||
' SELECT '||
l_hist_tl_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID FROM '||
l_prod_tl_table_name||' PT, '||
l_chg_tl_table_name||' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6'||
' AND PT.EXTENSION_ID = :7'||
' AND CT.EXTENSION_ID = :8' ||
' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :9';
l_mul_dynamic_sql := 'SELECT '||l_mul_chg_col_names_list||','||
l_mul_b_chg_cols_list||','||
l_mul_tl_chg_cols_list||
' FROM '||l_mul_chg_b_table_name ||' B,'||
l_mul_chg_tl_table_name ||' TL'||
' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
' AND B.ACD_TYPE = TL.ACD_TYPE'||
' AND B.EXTENSION_ID = TL.EXTENSION_ID' ||
' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
' AND B.CHANGE_LINE_ID = :1 ORDER BY B.ATTR_GROUP_ID';
l_attr_name_value_pairs.DELETE();
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||l_mul_chg_b_table_name||
' WHERE EXTENSION_ID = :1'
INTO l_dummy
USING l_current_pending_ext_id;
l_mode_for_current_row := G_UPDATE_TX_TYPE;
l_mode_for_current_row := G_UPDATE_TX_TYPE;
ELSIF (l_current_acd_type = G_DELETE_ACD_TYPE) THEN
IF (l_current_production_ext_id IS NULL) THEN
---------------------------------------
-- If ACD Type is DELETE and there's --
-- no production row, we do nothing --
---------------------------------------
l_mode_for_current_row := 'SKIP';
l_mode_for_current_row := G_DELETE_TX_TYPE;
IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
-----------------------------------------------------------
-- Process_Row will only process our pending B table row --
-- in the loop when LANGUAGE is NULL or when LANGUAGE = --
-- SOURCE_LANG, so we insert a History row in that loop --
-----------------------------------------------------------
IF (l_current_row_language IS NULL OR
l_current_row_language = l_current_row_source_lang)
THEN
Write_Debug('Inserting History Row with the ACD Type HISTORY for Multi-Row B table');
l_utility_dynamic_sql := ' INSERT INTO '||l_mul_chg_b_table_name||' CT ('||
l_mul_hist_b_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID, CT.ATTR_GROUP_ID) ' ||
' SELECT '||
l_mul_hist_b_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'' '||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID, PT.ATTR_GROUP_ID FROM '||
l_mul_prod_b_table_name||' PT, '||
l_mul_chg_b_table_name || ' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6' ||
' AND PT.EXTENSION_ID = :7'||
' AND CT.EXTENSION_ID = :8' ;
Write_Debug('Inserting History Row with the ACD Type HISTORY for Multi-Row TL table');
l_utility_dynamic_sql := ' INSERT INTO '||l_mul_chg_tl_table_name||' CT ('||
l_mul_hist_tl_chg_cols_list||
', CT.CREATED_BY,CT.CREATION_DATE,CT.LAST_UPDATED_BY'||
', CT.LAST_UPDATE_DATE, CT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE '||
', CT.INVENTORY_ITEM_ID, CT.ORGANIZATION_ID, CT.EXTENSION_ID, CT.ATTR_GROUP_ID ) ' ||
' SELECT '||
l_mul_hist_tl_prod_cols_list||
', PT.CREATED_BY,PT.CREATION_DATE,PT.LAST_UPDATED_BY'||
', PT.LAST_UPDATE_DATE, PT.LAST_UPDATE_LOGIN'||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.INVENTORY_ITEM_ID, PT.ORGANIZATION_ID, PT.EXTENSION_ID, PT.ATTR_GROUP_ID FROM '||
l_mul_prod_tl_table_name||' PT, '||
l_mul_chg_tl_table_name||' CT ' ||
' WHERE PT.INVENTORY_ITEM_ID = :1'||
' AND PT.ORGANIZATION_ID = :2'||
' AND CT.INVENTORY_ITEM_ID = :3'||
' AND CT.ORGANIZATION_ID = :4'||
' AND CT.CHANGE_LINE_ID = :5'||
' AND CT.ACD_TYPE = :6'||
' AND PT.EXTENSION_ID = :7'||
' AND CT.EXTENSION_ID = :8' ||
' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :9';
l_multi_row_attrs_tbl.DELETE;
EXECUTE IMMEDIATE ' UPDATE '||l_chg_b_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||l_chg_tl_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||l_mul_chg_b_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||l_mul_chg_tl_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
',''CREATION_DATE'',''LAST_UPDATED_BY'''||
',''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN''';
',''PK4_VALUE'', ''PK5_VALUE'',''PROGRAM_ID'', ''PROGRAM_UPDATE_DATE'' , ''REQUEST_ID'''||
' ,''PROGRAM_APPLICATION_ID'',''UNIQUE_VALUE'' ';
l_dynamic_sql := 'SELECT '||l_chng_col_names_list||','||
l_b_chng_cols_list||','||
l_tl_chng_cols_list||
' FROM '||p_change_b_table_name||' B,'||
p_change_tl_table_name||' TL'||
' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
' AND B.EXTENSION_ID = TL.EXTENSION_ID'||
' AND B.ACD_TYPE = TL.ACD_TYPE'||
' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
' AND B.CHANGE_LINE_ID = :1 ' ||
' ORDER BY B.EXTENSION_ID';
l_attr_name_value_pairs.DELETE();
l_token_table.DELETE();
ELSIF (l_current_acd_type = 'ADD' OR l_current_acd_type = 'DELETE' )
THEN
l_impl_attr_name_value_pairs := l_attr_name_value_pairs;
Write_Debug('IN case of single-row Attr Group, we will update the value');
l_mode_for_current_row := G_UPDATE_TX_TYPE;
l_utility_dynamic_sql := 'UPDATE '||p_change_b_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3';
l_utility_dynamic_sql := 'UPDATE '||p_change_tl_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3'||
' AND LANGUAGE = :4';
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||p_change_b_table_name||
' WHERE EXTENSION_ID = :1'
INTO l_dummy
USING l_current_pending_ext_id;
Write_Debug('Set Update mode to this row');
l_mode_for_current_row := G_UPDATE_TX_TYPE;
l_mode_for_current_row := G_UPDATE_TX_TYPE;
ELSIF (l_current_acd_type = 'DELETE') THEN
Write_Debug('Current Acd Type DELETE: check the pending ext id row exits in prod table');
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '|| p_production_b_table_name ||
' WHERE EXTENSION_ID = :1'
INTO l_dummy
USING l_current_pending_ext_id;
Write_Debug('Set Delete mode to this row');
l_mode_for_current_row := G_DELETE_TX_TYPE ;
IF (l_mode_for_current_row = G_DELETE_TX_TYPE OR
l_mode_for_current_row = G_UPDATE_TX_TYPE) THEN
-----------------------------------------------------------
-- Process_Row will only process our pending B table row --
-- in the loop when LANGUAGE is NULL or when LANGUAGE = --
-- SOURCE_LANG, so we insert a History row in that loop --
-----------------------------------------------------------
/* BUG 5388684 As the source lang may not be the first record to get processed.
in case its add case for Single row then if the other language record goes first
which is used to insert history record which is wrong.
*/
/*IF (l_current_row_language IS NULL OR
l_current_row_language = l_current_row_source_lang) THEN*/
IF L_PREV_EXT_ID <> l_ext_id_for_current_row THEN
l_utility_dynamic_sql := ' INSERT INTO '||p_change_b_table_name||' CT ('||
l_history_b_chng_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID,CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE )SELECT '||
l_history_b_prod_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.EXTENSION_ID, CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE FROM '||
p_production_b_table_name||' PT, '||
p_change_b_table_name||
' CT WHERE PT.EXTENSION_ID = :1'||
' AND CT.EXTENSION_ID = :2'||
' AND CT.CHANGE_LINE_ID = :3'||
' AND CT.ACD_TYPE = :4';
l_utility_dynamic_sql := ' INSERT INTO '||p_change_tl_table_name||' CT ('||
l_history_tl_chng_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID,CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE ) SELECT '||
l_history_tl_prod_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.EXTENSION_ID, CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE FROM '||
p_production_tl_table_name||' PT, '||
p_change_tl_table_name||
' CT WHERE PT.EXTENSION_ID = :1'||
' AND CT.EXTENSION_ID = :2'||
' AND CT.CHANGE_LINE_ID = :3'||
' AND CT.ACD_TYPE = :4'||
' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :5';
l_utility_dynamic_sql := ' INSERT INTO '||p_change_b_table_name||' CT ('||
' CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID,CT.ATTR_GROUP_ID '||
', CT.ORGANIZATION_ID ,CT.INVENTORY_ITEM_ID '||
', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY, CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE '||
' ) SELECT '||
' CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', CT.EXTENSION_ID ,CT.ATTR_GROUP_ID '||
', CT.ORGANIZATION_ID , CT.INVENTORY_ITEM_ID '||
', CT.ITEM_CATALOG_GROUP_ID,CT.REVISION_ID, CT.CREATED_BY '||
', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY, CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE '||
' FROM '||
p_change_b_table_name || ' CT '||
' WHERE CT.EXTENSION_ID = :1'||
' AND CT.CHANGE_LINE_ID = :2'||
' AND CT.ACD_TYPE = :3';
l_utility_dynamic_sql := ' INSERT INTO '|| p_change_tl_table_name||' CT ('||
' CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID, CT.ATTR_GROUP_ID '||
', CT.ORGANIZATION_ID ,CT.INVENTORY_ITEM_ID '||
', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY '||
', CT.SOURCE_LANG,CT.LANGUAGE,CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE ) SELECT '||
' CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', CT.EXTENSION_ID, CT.ATTR_GROUP_ID '||
', CT.ORGANIZATION_ID , CT.INVENTORY_ITEM_ID '||
', CT.ITEM_CATALOG_GROUP_ID, CT.REVISION_ID, CT.CREATED_BY '||
', CT.CREATION_DATE , CT.LAST_UPDATE_DATE ' ||
', CT.LAST_UPDATE_LOGIN, CT.LAST_UPDATED_BY '||
', CT.SOURCE_LANG , CT.LANGUAGE , CT.DATA_LEVEL_ID '||
', CT.PK1_VALUE, CT.PK2_VALUE, CT.PK3_VALUE '||
', CT.PK4_VALUE, CT.PK5_VALUE '||
' FROM '||
p_change_tl_table_name || ' CT '||
' WHERE CT.EXTENSION_ID = :1'||
' AND CT.CHANGE_LINE_ID = :2'||
' AND CT.ACD_TYPE = :3'||
' AND CT.LANGUAGE = :4';
l_mode_for_current_row := G_UPDATE_TX_TYPE;
EXECUTE IMMEDIATE ' UPDATE '||p_change_b_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||p_change_tl_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
FOR rec IN (SELECT inventory_item_id, description , LANGUAGE FROM mtl_system_items_tl WHERE
inventory_item_id = (SELECT DISTINCT INVENTORY_ITEM_ID FROM EGO_ITEMS_ATTRS_CHANGES_B WHERE change_line_id = p_change_line_id)
AND ORGANIZATION_ID = (SELECT DISTINCT ORGANIZATION_ID FROM EGO_ITEMS_ATTRS_CHANGES_B WHERE change_line_id = p_change_line_id))
LOOP
Write_Debug('Synching Item description at all organization levels for item: ' || rec.inventory_item_id );
UPDATE mtl_system_items_tl
SET description = rec.description
WHERE inventory_item_id = rec.inventory_item_id
AND LANGUAGE = rec.LANGUAGE;
UPDATE mtl_system_items_b
SET description = rec.description
WHERE
inventory_item_id = rec.inventory_item_id ;
select new_item_revision_id, current_item_revision_id
into l_new_revision_id, l_old_revision_id
from eng_revised_items
where revised_item_sequence_id = p_change_line_id;