[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
SELECT application_id
INTO l_Application_Id
FROM fnd_application
WHERE application_short_name ='EGO';
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = cp_value_set_name;
SELECT val.flex_value_id value_id
FROM fnd_flex_value_sets vs,
fnd_flex_values val
WHERE vs.flex_value_set_id = val.flex_value_set_id
AND vs.flex_value_set_id = cp_value_set_id
AND val.flex_value = cp_flex_value;
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cp_value_set_id;
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id= cp_flex_value_set_id
AND flex_value_id = cp_flex_value_id;
SELECT meaning
FROM ego_vs_format_codes_v
WHERE lookup_code = cp_format_code;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
SELECT start_active_date, end_active_date
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = l_value_set_id
AND version_seq_id = l_version_seq_id;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
SELECT 1 AS tab_exist
FROM all_objects
WHERE object_name =Upper(l_application_table_name)
AND object_type IN ('VIEW','SYNONYM','TABLE');
SELECT 1 AS col_exist
FROM all_tab_columns
WHERE TABLE_NAME = Upper(l_application_table_name)
AND COLUMN_NAME = Upper(l_value_column_name);
l_sql := 'SELECT '||l_cols||' FROM '||l_application_table_name||' '||l_where_clause;
UPDATE Ego_Flex_Value_Set_Intf efvsi
SET efvsi.transaction_type= G_CREATE,
efvsi.last_updated_by = g_user_id,
efvsi.last_update_date = SYSDATE,
efvsi.last_update_login = g_login_id
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND NOT EXISTS
(
SELECT 1
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = efvsi.value_set_name
)
AND Upper(efvsi.transaction_type) =G_SYNC;
UPDATE Ego_Flex_Value_Set_Intf efvsi
SET efvsi.transaction_type= G_UPDATE,
efvsi.last_updated_by = g_user_id,
efvsi.last_update_date = SYSDATE,
efvsi.last_update_login = g_login_id
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND EXISTS
(
SELECT 1
FROM fnd_flex_value_sets ffvs
WHERE ffvs.flex_value_set_name = efvsi.value_set_name
)
AND Upper(efvsi.transaction_type) =G_SYNC;
UPDATE Ego_Flex_Value_Intf evsvi
SET evsvi.transaction_type= G_CREATE,
evsvi.last_updated_by = g_user_id,
evsvi.last_update_date = SYSDATE,
evsvi.last_update_login = g_login_id
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND NOT EXISTS --evsvi.flex_value NOT IN
(
SELECT flex_value
FROM fnd_flex_value_sets FVS,
fnd_flex_values FVSV
--Ego_Flex_Value_Set_Intf EVS
WHERE fvs.flex_value_set_id= fvsv.flex_value_set_id
AND fvs.flex_value_set_name = evsvi.value_set_name
--AND evs.value_set_name = evsv.value_set_name
)
AND Upper(transaction_type) =G_SYNC;
UPDATE Ego_Flex_Value_Intf EVSV
SET EVSV.transaction_type= G_UPDATE ,
last_updated_by = g_user_id,
last_update_date = SYSDATE,
last_update_login = g_login_id
--flex_value_id = fvsv.flex_value_id --?
WHERE (p_set_process_id IS NULL
OR EVSV.set_process_id = p_set_process_id
)
AND EVSV.flex_value IN
(
SELECT FVSV.flex_value
FROM fnd_flex_value_sets FVS,
fnd_flex_values FVSV
--Ego_Flex_Value_Set_Intf EVS
WHERE fvs.flex_value_set_id= fvsv.flex_value_set_id
AND fvs.flex_value_set_name = evsv.value_set_name
--AND evs.value_set_name = evsv.value_set_name
)
AND Upper(EVSV.transaction_type) =G_SYNC;
UPDATE Ego_Flex_Value_Tl_Intf EVSTV
SET transaction_type= G_CREATE
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND flex_value NOT IN
(
SELECT fvsv.flex_value
FROM fnd_flex_value_sets FVS,
fnd_flex_values FVSV,
fnd_flex_values_tl FVSTV,
Ego_Flex_Value_Set_Intf EVS,
Ego_Flex_Value_Intf EVSV
WHERE fvs.flex_value_set_id= fvsv.flex_value_set_id
AND fvsv.flex_value_id= fvstv.flex_value_id
AND fvs.flex_value_set_name = EVSTV.value_set_name
AND evs.value_set_name= EVSV.value_set_name
AND EVSV.flex_value= EVSTV.flex_value
)
AND Upper(transaction_type) =G_SYNC;
UPDATE Ego_Flex_Value_Tl_Intf EVSTV
SET transaction_type= G_UPDATE,
last_updated_by = g_user_id,
last_update_date = SYSDATE,
last_update_login = g_login_id
--flex_value_id= fvsv.flex_value_id --?
WHERE (p_set_process_id IS NULL
OR EVSTV.set_process_id = p_set_process_id
)
AND EVSTV.flex_value IN
(
SELECT fvsv.flex_value
FROM fnd_flex_value_sets FVS,
fnd_flex_values FVSV,
fnd_flex_values_tl FVSTV,
Ego_Flex_Value_Set_Intf EVS
WHERE fvs.flex_value_set_id= fvsv.flex_value_set_id
AND fvsv.flex_value_id= fvstv.flex_value_id
AND fvs.flex_value_set_name = evs.value_set_name
AND evstv.flex_value= fvsv.flex_value
)
AND Upper(transaction_type) =G_SYNC;
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evsi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VS_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Set_Intf evsi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC)
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Set_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC);
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evsvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VAL,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC)
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC);
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evstvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_TL_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VAL,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Tl_Intf evstvi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC)
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Tl_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND Upper(transaction_type) NOT IN (G_CREATE, G_UPDATE,G_SYNC);
SELECT flex_value_id,SEQUENCE
FROM ego_flex_value_version_b
WHERE version_seq_id = p_version_number
AND flex_value_set_id = p_value_set_id;
DELETE FROM EGO_FLEX_VALUE_VERSION_TL
WHERE VERSION_SEQ_ID =0
AND FLEX_VALUE_ID IN ( SELECT FLEX_VALUE_ID
FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID =P_VALUE_SET_ID
AND VERSION_SEQ_ID = 0);
DELETE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID =P_VALUE_SET_ID
AND VERSION_SEQ_ID = 0;
INSERT INTO EGO_FLEX_VALUE_VERSION_B
( FLEX_VALUE_SET_ID,FLEX_VALUE_ID,VERSION_SEQ_ID,SEQUENCE,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES ( P_VALUE_SET_ID,DraftRec.FLEX_VALUE_ID,0,DraftRec.SEQUENCE,FND_GLOBAL.PARTY_ID,
SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.LOGIN_ID );
INSERT INTO EGO_FLEX_VALUE_VERSION_TL
( FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
SELECT FLEX_VALUE_ID,0,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,
FND_GLOBAL.LOGIN_ID,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG FROM EGO_FLEX_VALUE_VERSION_TL
WHERE VERSION_SEQ_ID = P_VERSION_NUMBER AND FLEX_VALUE_ID
IN ( SELECT FLEX_VALUE_ID
FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID
AND VERSION_SEQ_ID = P_VERSION_NUMBER);
SELECT start_active_date ,end_active_date,version_seq_id
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = p_value_set_id AND version_seq_id <> 0 ;
SELECT Max(version_seq_id) max_ver --,start_active_date, end_active_date
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = cp_value_set_id
AND version_seq_id <> 0;
SELECT (version_seq_id) draft_ver
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = cp_value_set_id
AND version_seq_id = 0;
SELECT COUNT(*) INTO l_same_rel_date
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID
AND ( START_ACTIVE_DATE= l_start_date
OR END_ACTIVE_DATE = l_start_date
)
AND VERSION_SEQ_ID <>0;
l_token_table.DELETE;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B
(flex_value_set_id,version_seq_id,description, start_active_date, end_active_date ,
created_by,creation_date,last_updated_by,last_update_date,last_update_login)
VALUES ( l_value_set_id, 0, l_description, SYSDATE, NULL,
G_Party_Id,SYSDATE,G_Party_Id,SYSDATE,G_Login_Id);
INSERT INTO EGO_OBJECT_LOCK
( LOCK_ID,OBJECT_NAME,PK1_VALUE,LOCKING_PARTY_ID,LOCK_FLAG,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES ( EGO_OBJECT_LOCK_S.NEXTVAL,'EGO_VALUE_SET',l_value_set_id,G_Party_id,'L',
G_Party_id,SYSDATE,G_Party_id,SYSDATE,G_Login_Id) ;
G_TOKEN_TBL.DELETE;
l_token_table.DELETE;
Update ego_flex_valueset_version_b
SET end_active_date= l_start_date-1/(24*60*60),
LAST_UPDATED_BY= G_Party_Id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE flex_value_set_id = p_value_set_id
AND version_seq_id= l_prev_future_version;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B
(flex_value_set_id, version_seq_id,description, start_active_date,end_active_date,
created_by,creation_date,last_updated_by,last_update_date,last_update_login)
VALUES (l_value_set_id, Nvl(l_target_max_ver,0)+1,l_description, l_start_date,l_relver_end_date,
G_Party_Id,sysdate,G_Party_Id,sysdate,G_Login_id) ;
SELECT Min(start_active_date ) INTO l_min_future_start_date
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = p_value_set_id
AND version_seq_id >0;
Update ego_flex_valueset_version_b
SET end_active_date= l_start_date-1/(24*60*60),
LAST_UPDATED_BY= G_Party_Id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE flex_value_set_id = p_value_set_id
AND (start_active_date =( SELECT Max(start_active_date )
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = p_value_set_id
AND version_seq_id >0
)
);
INSERT INTO EGO_FLEX_VALUESET_VERSION_B
(flex_value_set_id, version_seq_id,description, start_active_date,end_active_date,
created_by,creation_date,last_updated_by,last_update_date,last_update_login)
VALUES (l_value_set_id, Nvl(l_target_max_ver,0)+1,l_description, l_start_date,NULL ,
G_Party_Id,sysdate,G_Party_Id,sysdate,G_Login_id) ;
Update ego_flex_valueset_version_b
SET end_active_date= l_min_future_start_date-1/(24*60*60),
LAST_UPDATED_BY= G_Party_Id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE flex_value_set_id = p_value_set_id
AND start_active_date = l_start_date ;
G_TOKEN_TBL.DELETE;
SELECT validation_type, format_type, maximum_value,minimum_value,longlist_flag,maximum_size,description
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_value_set_id;
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_value_set_id;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
SELECT FLEX_VALUE_MEANING,DESCRIPTION, START_DATE_ACTIVE,END_DATE_ACTIVE, ENABLED_FLAG
FROM FND_FLEX_VALUES ,FND_FLEX_VALUES_TL
WHERE FND_FLEX_VALUES.flex_value_set_id = p_value_set_id
AND FND_FLEX_VALUES.FLEX_VALUE_ID = p_value_id
AND FND_FLEX_VALUES.FLEX_VALUE_ID = FND_FLEX_VALUES_TL.FLEX_VALUE_ID
AND LANGUAGE = userenv('LANG')
AND ROWNUM=1;
SELECT disp_sequence
FROM ego_vs_values_disp_order
WHERE value_set_value_id = cp_flex_value_id
AND value_set_id = p_value_set_id; -- Bug 12394675.
UPDATE Ego_Flex_Value_Set_Intf
SET ROW = p_valueset_tbl(i)
WHERE transaction_id = l_trans_id(i);
UPDATE Ego_Flex_Value_Intf
SET ROW = p_valueset_val_tbl(i)
WHERE transaction_id = l_trans_id(i);
UPDATE Ego_Flex_Value_Tl_Intf
SET ROW = p_valueset_val_tl_tbl(i)
WHERE transaction_id = l_trans_id(i);
UPDATE Ego_Flex_Value_Tl_Intf
SET value_set_id = p_valueset_val_tl_tbl(i).value_set_id,
value_set_name = p_valueset_val_tl_tbl(i).value_set_name,
flex_value = p_valueset_val_tl_tbl(i).flex_value,
flex_value_id = p_valueset_val_tl_tbl(i).flex_value_id,
version_seq_id = p_valueset_val_tl_tbl(i).version_seq_id,
"LANGUAGE" = p_valueset_val_tl_tbl(i).LANGUAGE,
description = p_valueset_val_tl_tbl(i).description,
source_lang = p_valueset_val_tl_tbl(i).source_lang,
flex_value_meaning = p_valueset_val_tl_tbl(i).flex_value_meaning,
transaction_type = p_valueset_val_tl_tbl(i).transaction_type,
--transaction_id = p_valueset_val_tl_tbl(i).transaction_id,
process_status = p_valueset_val_tl_tbl(i).process_status,
set_process_id = p_valueset_val_tl_tbl(i).set_process_id,
request_id = p_valueset_val_tl_tbl(i).request_id,
program_application_id = p_valueset_val_tl_tbl(i).request_id,
program_id = p_valueset_val_tl_tbl(i).program_application_id,
program_update_date = p_valueset_val_tl_tbl(i).program_update_date,
last_update_date = p_valueset_val_tl_tbl(i).last_update_date,
last_updated_by = p_valueset_val_tl_tbl(i).last_updated_by,
creation_date = p_valueset_val_tl_tbl(i).creation_date,
created_by = p_valueset_val_tl_tbl(i).created_by,
last_update_login = p_valueset_val_tl_tbl(i).last_update_login
WHERE process_status = G_PROCESS_RECORD
AND transaction_id = p_valueset_val_tl_tbl(i).transaction_id;*/
SELECT value_set_name
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND transaction_type = G_CREATE;
SELECT value_set_name
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND Nvl(version_seq_id,0)<0;
SELECT value_set_name
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND parent_value_set_name IS NOT NULL;
UPDATE Ego_Flex_Value_Set_Intf
SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL,
transaction_type = Upper(transaction_type),
last_updated_by = G_USER_ID,
last_update_date =sysdate,
last_update_login=G_LOGIN_ID,
created_by = G_USER_ID,
creation_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND transaction_id IS NULL
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Intf
SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL,
transaction_type = Upper(transaction_type),
last_updated_by = G_USER_ID,
last_update_date =sysdate,
last_update_login=G_LOGIN_ID,
created_by = G_USER_ID,
creation_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND transaction_id IS NULL
--AND Upper(transaction_type) IN (G_CREATE, G_UPDATE, G_Delete,G_SYNC)
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Tl_Intf
SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL ,
transaction_type = Upper(transaction_type),
last_updated_by = G_USER_ID,
last_update_date = SYSDATE,
last_update_login= G_LOGIN_ID ,
created_by = G_USER_ID,
creation_date = SYSDATE,
request_id = g_request_id,
program_application_id = g_prog_appl_id,
program_id = g_program_id,
program_update_date = SYSDATE
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND transaction_id IS NULL
--AND Upper(transaction_type) IN (G_CREATE, G_UPDATE, G_Delete,G_SYNC)
AND process_status=G_PROCESS_RECORD;
UPDATE Ego_Flex_Value_Set_Intf -- Do bulk update
SET process_status= 3
WHERE value_set_name=l_name_tab(i);
UPDATE Ego_Flex_Value_Set_Intf -- Do bulk update
SET process_status= 3
WHERE value_set_name=l_name_tab(i)
AND validation_type NOT IN (G_TABLE_VALIDATION_CODE);
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evsi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VS_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VER,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Set_Intf evsi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND version_seq_id IS NOT NULL;
UPDATE Ego_Flex_Value_Set_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND version_seq_id IS NOT NULL;
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evsvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VER,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND version_seq_id IS NOT NULL;
UPDATE Ego_Flex_Value_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND version_seq_id IS NOT NULL;
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
MESSAGE_NAME,
ERROR_MESSAGE,
bo_identifier,
ENTITY_IDENTIFIER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evstvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_TL_HEADER_TAB,
l_err_message_name,
l_err_message_text,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VER,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Tl_Intf evstvi
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND version_seq_id IS NOT NULL;
UPDATE Ego_Flex_Value_Tl_Intf
SET process_status=G_ERROR_RECORD
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND version_seq_id IS NOT NULL;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
l_token_table.DELETE;
SELECT *
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND version_seq_id IS NULL
AND process_status=G_PROCESS_RECORD
AND parent_value_set_name is NULL
ORDER BY value_set_name;
SELECT *
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND evsvi.process_status=G_PROCESS_RECORD
AND evsvi.version_seq_id IS NULL
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (evsi.value_set_name= evsvi.value_set_name
OR evsi.value_set_id= evsvi.value_set_id
)
AND evsi.process_status = G_SUCCESS_RECORD
AND evsi.version_seq_id IS NULL
AND evsi.parent_value_set_name IS NULL
)
UNION
SELECT 1
FROM Fnd_Flex_Value_Sets ffvs
WHERE (ffvs.flex_value_set_name= evsvi.value_set_name
OR ffvs.flex_value_set_id= evsvi.value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Flex_valueSet_Version_b efvsv
WHERE ffvs.flex_value_set_id= efvsv.flex_value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_value_Set_Ext evse
WHERE ffvs.flex_value_set_id = evse.value_set_id
)
AND ffvs.parent_flex_value_set_id IS NULL
)
ORDER BY value_set_name, value_set_id ;
SELECT *
FROM Ego_Flex_Value_tl_Intf evstvi
WHERE
(
( p_set_process_id IS NULL
OR evstvi.set_process_id = p_set_process_id
)
AND ( evstvi.flex_value = cp_flex_value
OR
evstvi.flex_value_id = cp_flex_value_id
)
AND
( evstvi.value_set_name = cp_value_set_name
OR
evstvi.value_set_id = cp_value_set_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Intf evsvi,
Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND
(p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (
( evsi.value_set_name= evsvi.value_set_name
AND evsvi.value_set_name= evstvi.value_set_name
)
OR
( evsi.value_set_id= evsvi.value_set_id
AND evsvi.value_set_id= evstvi.value_set_id
)
)
AND (evsvi.flex_value= evstvi.flex_value
OR evsvi.flex_value_id = evstvi.flex_value_id
)
AND evsi.process_status = G_SUCCESS_RECORD
AND evsvi.process_status = G_PROCESS_RECORD
AND evsi.parent_value_set_name IS NULL
AND evsi.version_seq_id IS NULL
AND evsvi.version_seq_id IS NULL
)
UNION
SELECT 1
FROM Ego_Flex_Value_Intf evsvi,
Fnd_Flex_Value_Sets ffvs
WHERE
( (p_set_process_id IS NULL
OR evsvi.set_process_id = null
)
AND (
( ffvs.flex_value_set_name= evsvi.value_set_name
AND evsvi.value_set_name= evstvi.value_set_name
)
OR
( ffvs.flex_value_set_id= evsvi.value_set_id
AND evsvi.value_set_id= evstvi.value_set_id
)
)
AND (evsvi.flex_value= evstvi.flex_value
OR evsvi.flex_value_id = evstvi.flex_value_id
)
AND evsvi.process_status = G_PROCESS_RECORD
AND ffvs.parent_flex_value_set_id IS NULL
AND NOT EXISTS
( SELECT 1
FROM Ego_Flex_valueSet_Version_b efvsv
WHERE ffvs.flex_value_set_id= efvsv.flex_value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_value_Set_Ext evse
WHERE ffvs.flex_value_set_id = evse.value_set_id
)
AND evsvi.version_seq_id IS NULL
)
)
AND evstvi.version_seq_id IS NULL
AND evstvi.process_status=G_PROCESS_RECORD
AND "LANGUAGE" = cp_language_code
)
--GROUP BY LANGUAGE
ORDER BY VALUE_set_name ;
SELECT *
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND evsvi.process_status=G_PROCESS_RECORD
AND evsvi.version_seq_id IS NULL
ORDER BY value_set_name, value_set_id;
SELECT *
FROM Ego_Flex_Value_tl_Intf evstvi
WHERE
(
( p_set_process_id IS NULL
OR evstvi.set_process_id = p_set_process_id
)
AND evstvi.version_seq_id IS NULL
AND evstvi.process_status=G_PROCESS_RECORD
AND "LANGUAGE" = cp_language_code
)
ORDER BY VALUE_set_name;
SELECT language_code, nls_language
FROM FND_LANGUAGES
WHERE installed_flag IN ('I','B');
( SELECT 1 AS lang_code
FROM EGO_FLEX_VALUE_VERSION_TL
WHERE flex_value_id = cp_value_id
AND version_seq_id = cp_version_seq_id
AND "LANGUAGE" = cp_lang_code
);
SELECT *
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND version_seq_id IS NOT NULL
AND process_status=G_PROCESS_RECORD
ORDER BY value_set_name, version_seq_id;
SELECT *
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND evsvi.process_status=G_PROCESS_RECORD
AND evsvi.version_seq_id IS NOT NULL
AND ( evsvi.value_set_name = cp_value_set_name
OR
evsvi.value_set_id = cp_value_set_id
)
AND evsvi.version_seq_id = cp_version_seq_id
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (evsi.value_set_name= evsvi.value_set_name
OR evsi.value_set_id= evsvi.value_set_id
)
AND evsi.process_status = G_PROCESS_RECORD -- YTJ -- Cross verify if status is going to be this one.
AND evsi.version_seq_id = evsvi.version_seq_id -- IS NULL
)
)
ORDER BY value_set_name, value_set_id,version_seq_id ;
SELECT *
FROM Ego_Flex_Value_tl_Intf evstvi
WHERE
(
( p_set_process_id IS NULL
OR evstvi.set_process_id = p_set_process_id
)
AND evstvi.version_seq_id = cp_version_seq_id
AND evstvi.process_status=G_PROCESS_RECORD
AND ( evstvi.value_set_name = cp_value_set_name
OR
evstvi.value_set_id = cp_value_set_id
)
AND ( evstvi.flex_value = cp_flex_value
OR
evstvi.flex_value_id = cp_flex_value_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Intf evsvi,
Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND
(p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (
( evsi.value_set_name= evsvi.value_set_name
AND evsvi.value_set_name= evstvi.value_set_name
)
OR
( evsi.value_set_id= evsvi.value_set_id
AND evsvi.value_set_id= evstvi.value_set_id
)
)
AND (evsvi.flex_value= evstvi.flex_value
OR evsvi.flex_value_id = evstvi.flex_value_id
)
AND ( evsi.version_seq_id = evsvi.version_seq_id
AND evsvi.version_seq_id =evstvi.version_seq_id
)
AND evsi.process_status = G_PROCESS_RECORD -- YTJ -- Cross verify if status is going to be this one
AND evsvi.process_status = G_PROCESS_RECORD
)
)
)
ORDER BY flex_value, flex_value_id,version_seq_id;
SELECT *
FROM Ego_Flex_Value_Set_Intf
WHERE (p_set_process_id IS NULL
OR set_process_id = p_set_process_id
)
AND process_status=G_PROCESS_RECORD
AND parent_value_set_name IS NOT NULL
AND version_seq_id IS NULL
ORDER BY value_set_name, value_set_id;
SELECT *
FROM Ego_Flex_Value_Intf evsvi
WHERE (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND evsvi.process_status=G_PROCESS_RECORD
AND evsvi.version_seq_id IS NULL
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (evsi.value_set_name= evsvi.value_set_name
OR evsi.value_set_id= evsvi.value_set_id
)
AND evsi.process_status = G_PROCESS_RECORD
AND evsi.version_seq_id IS NULL
AND evsi.parent_value_set_name IS NOT NULL
)
UNION
SELECT 1
FROM Fnd_Flex_Value_Sets ffvs
WHERE
( ( ffvs.flex_value_set_name= evsvi.value_set_name
OR ffvs.flex_value_set_id= evsvi.value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Flex_valueSet_Version_b efvsv
WHERE ffvs.flex_value_set_id= efvsv.flex_value_set_id
)
AND EXISTS
( SELECT 1
FROM Ego_value_Set_Ext evse
WHERE ffvs.flex_value_set_id = evse.value_set_id
)
)
)
ORDER BY evsvi.value_set_name, evsvi.value_set_id ;
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
BO_Identifier,
Entity_Identifier,
MESSAGE_NAME,
ERROR_MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evsvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_HEADER_TAB,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VAL,
l_err_message_name,
l_err_message_text,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Intf evsvi
WHERE
(
( p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (evsi.value_set_name= evsvi.value_set_name
OR evsi.value_set_id= evsvi.value_set_id
)
AND evsi.process_status = G_ERROR_RECORD
AND evsi.version_seq_id IS NULL
AND evsi.parent_value_set_name IS NULL
)
)
AND NOT EXISTS
(
SELECT 1
FROM Fnd_Flex_Value_Sets ffvs
WHERE (ffvs.flex_value_set_name= evsvi.value_set_name
OR ffvs.flex_value_set_id= evsvi.value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Flex_valueSet_Version_b efvsv
WHERE ffvs.flex_value_set_id= efvsv.flex_value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Value_Set_Ext evse
WHERE ffvs.flex_value_set_id= evse.value_set_id
)
AND ffvs.parent_flex_value_set_id IS NULL
)
AND evsvi.version_seq_id IS NULL
AND evsvi.process_status=G_PROCESS_RECORD
);
UPDATE ego_flex_value_intf evsvi
SET evsvi.process_status=G_ERROR_RECORD,
evsvi.LAST_UPDATED_BY= G_User_Id,
evsvi.LAST_UPDATE_DATE = SYSDATE,
evsvi.LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE
(
( p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Set_Intf evsi
WHERE
( (p_set_process_id IS NULL
OR evsi.set_process_id = p_set_process_id
)
AND (evsi.value_set_name= evsvi.value_set_name
OR evsi.value_set_id= evsvi.value_set_id
)
AND evsi.process_status = G_ERROR_RECORD
AND evsi.version_seq_id IS NULL
AND evsi.parent_value_set_name IS NULL
)
)
AND NOT EXISTS
(
SELECT 1
FROM Fnd_Flex_Value_Sets ffvs
WHERE (ffvs.flex_value_set_name= evsvi.value_set_name
OR ffvs.flex_value_set_id= evsvi.value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Flex_valueSet_Version_b efvsv
WHERE ffvs.flex_value_set_id= efvsv.flex_value_set_id
)
AND NOT EXISTS
( SELECT 1
FROM Ego_Value_Set_Ext evse
WHERE ffvs.flex_value_set_id= evse.value_set_id
)
AND ffvs.parent_flex_value_set_id IS NULL
)
AND evsvi.version_seq_id IS NULL
AND evsvi.process_status=G_PROCESS_RECORD
);
INSERT
INTO
MTL_INTERFACE_ERRORS
(
TRANSACTION_ID,
UNIQUE_ID,
ORGANIZATION_ID,
COLUMN_NAME,
TABLE_NAME,
BO_Identifier,
Entity_Identifier,
MESSAGE_NAME,
ERROR_MESSAGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
SELECT
evstvi.transaction_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
NULL,
NULL,
G_ENTITY_VAL_TL_HEADER_TAB,
G_BO_IDENTIFIER_VS,
G_ENTITY_VS_VAL,
l_err_message_name,
l_err_message_text,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, G_USER_ID),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, G_USER_ID),
NVL(LAST_UPDATE_LOGIN, G_LOGIN_ID),
G_REQUEST_ID,
NVL(PROGRAM_APPLICATION_ID, G_PROG_APPL_ID),
NVL(PROGRAM_ID, G_PROGRAM_ID),
NVL(PROGRAM_UPDATE_DATE, sysdate)
FROM Ego_Flex_Value_Tl_Intf evstvi
WHERE
(
( p_set_process_id IS NULL
OR evstvi.set_process_id = p_set_process_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Intf evsvi
WHERE
( (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND (evsvi.value_set_name= evstvi.value_set_name
OR evsvi.value_set_id= evstvi.value_set_id
)
AND (evsvi.flex_value= evstvi.flex_value
OR evsvi.flex_value_id = evstvi.flex_value_id
)
AND evsvi.process_status = G_ERROR_RECORD
AND evsvi.version_seq_id IS NULL
)
)
AND evstvi.version_seq_id IS NULL
AND evstvi.process_status=G_PROCESS_RECORD
);
UPDATE Ego_Flex_Value_Tl_Intf evstvi
SET evstvi.process_status=G_ERROR_RECORD,
evstvi.LAST_UPDATED_BY= G_User_Id,
evstvi.LAST_UPDATE_DATE = SYSDATE,
evstvi.LAST_UPDATE_LOGIN = G_LOGIN_ID
WHERE
(
( p_set_process_id IS NULL
OR evstvi.set_process_id = p_set_process_id
)
AND EXISTS
( SELECT 1
FROM Ego_Flex_Value_Intf evsvi
WHERE
( (p_set_process_id IS NULL
OR evsvi.set_process_id = p_set_process_id
)
AND (evsvi.value_set_name= evstvi.value_set_name
OR evsvi.value_set_id= evstvi.value_set_id
)
AND (evsvi.flex_value= evstvi.flex_value
OR evsvi.flex_value_id = evstvi.flex_value_id
)
AND evsvi.process_status = G_ERROR_RECORD
AND evsvi.version_seq_id IS NULL
)
)
AND evstvi.version_seq_id IS NULL
AND evstvi.process_status=G_PROCESS_RECORD
);
SELECT UserEnv('Lang') INTO G_User_Lang FROM dual;
SELECT nls_language INTO G_NLS_LANGUAGE
FROM FND_LANGUAGES
WHERE language_code =G_User_Lang
AND installed_flag IN ('I','B');
G_TOKEN_TBL.DELETE;
INSERT INTO EGO_FLEX_VALUE_VERSION_TL
( FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
SELECT FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,
LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,p.LANGUAGE_code,SOURCE_LANG
FROM EGO_FLEX_VALUE_VERSION_TL
WHERE flex_value_id = l_vs_val_rec(1).flex_value_id
AND version_seq_id = G_OUT_VERSION_SEQ_ID
AND "LANGUAGE" = UserEnv('lang');
l_vs_val_rec.DELETE;
G_TOKEN_TBL.DELETE;
l_vs_val_rec.DELETE;
l_valueset_rec.DELETE;
/*IF (delete_flag = TRUE ) THEN
Delete_Processed_Value_Sets(p_set_process_id,x_return_status,x_return_msg);
l_program_update_date DATE;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT 1 AS table_exist
FROM fnd_flex_validation_tables
WHERE FLEX_VALUE_SET_ID= cp_value_set_id;
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = cp_value_set_name;
SELECT version_seq_id,start_active_date, end_active_date
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = cp_value_set_id
AND start_active_date <= G_SYSDATE
AND Nvl(end_active_date,G_SYSDATE)>= G_SYSDATE ;
SELECT Max(Nvl(version_seq_id,0)) max_version
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = cp_value_set_id;
SELECT version_seq_id, start_active_date, end_active_date
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = cp_value_set_id
AND ( start_active_date = (
SELECT Max(start_active_date )
FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = cp_value_set_id
AND Nvl(end_active_date,G_SYSDATE) <= cp_start_active_date
)
);
SELECT application_id
INTO l_application_id
FROM fnd_application
WHERE application_short_name ='EGO';
l_program_update_date := p_value_set_tbl(i).program_update_date;
l_last_update_date := p_value_set_tbl(i).last_update_date;
l_last_updated_by := p_value_set_tbl(i).last_updated_by;
l_last_update_login := p_value_set_tbl(i).last_update_login;
l_token_table.DELETE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type =G_UPDATE AND l_value_set_id IS NULL THEN
l_error_message_name := 'EGO_TRANS_TYPE_INVALID';
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_transaction_type :=G_UPDATE;
IF l_transaction_type=G_UPDATE THEN
--Get value of require field if they are null
Get_Key_VS_Columns (p_value_set_id => l_value_set_id,
p_transaction_id => l_transaction_id,
x_maximum_size => l_maximum_size,
x_maximum_value => l_maximum_value,
x_minimum_value => l_minimum_value,
x_description => l_description,
x_longlist_flag => l_longlist_flag,
x_format_code => l_format_code,
x_validation_code => l_validation_code,
x_return_status => l_return_status,
x_return_msg => l_return_msg
);
END IF;-- END IF l_transaction_type=G_UPDATE THEN
G_TOKEN_TBL.DELETE;
EGO_EXT_FWK_PUB.Insert_Value_Set_Table_Inf
(
p_api_version => 1.0
,p_value_set_id => l_value_set_id
,p_table_application_id => l_table_application_id
,p_table_name => l_application_table_name
,p_value_column_name => l_value_column_name
,p_value_column_type => l_value_column_type
,p_value_column_size => l_value_column_size
,p_meaning_column_name => l_meaning_column_name
,p_meaning_column_type => l_meaning_column_type -- Bug 9705126
,p_meaning_column_size => l_meaning_column_size
,p_id_column_name => l_id_column_name
,p_id_column_type => l_id_column_type
,p_id_column_size => l_id_column_size
,p_where_order_by => l_additional_where_clause
,p_additional_columns => ''
,p_owner => l_owner --G_CURRENT_USER_ID
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
);
G_TOKEN_TBL(4).Token_Value := 'Insert_Value_Set_Table_Inf';
G_TOKEN_TBL.DELETE;
l_token_table.DELETE;
ELSIF l_transaction_type=G_UPDATE THEN
-- Always call update API irrespective of validation code.
write_debug(G_PKG_Name,l_api_name,' Call to Process_Value_Set in UPDATE MODE ' );
write_debug(G_PKG_Name,l_api_name,' Getting Key Values : IN Update mode ' );
write_debug(G_PKG_Name,l_api_name,' Calling EGO_EXT_FWK_PUB.Update_Value_Set API ');
EGO_EXT_FWK_PUB.Update_Value_Set
(
p_api_version => l_api_version
,p_value_set_id => l_value_set_id
,p_description => CASE l_description
WHEN G_NULL_CHAR THEN NULL
ELSE l_description
END --l_description
,p_format_code => l_format_code
,p_maximum_size => l_maximum_size
,p_maximum_value => CASE l_maximum_value
WHEN G_NULL_NUM THEN NULL
ELSE l_maximum_value
END
,p_minimum_value => CASE l_minimum_value
WHEN G_NULL_NUM THEN NULL
ELSE l_minimum_value
END
,p_long_list_flag => l_longlist_flag
,p_validation_code => l_validation_code
,p_owner => l_owner --G_CURRENT_USER_ID
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
,x_versioned_vs => l_versioned_vs
);
G_TOKEN_TBL(4).Token_Value := 'Update_Value_Set';
write_debug(G_PKG_Name,l_api_name,' Call to EGO_EXT_FWK_PUB.Update_Value_Set done x_return_status := '||x_return_status );
EGO_EXT_FWK_PUB.Update_Value_Set_Table_Inf
(
p_api_version => 1.0
,p_value_set_id => l_value_set_id
,p_table_application_id => CASE l_table_application_id
WHEN G_NULL_NUM THEN NULL
ELSE l_table_application_id
END--l_table_application_id
,p_table_name => l_application_table_name
,p_value_column_name => l_value_column_name
,p_value_column_type => l_value_column_type
,p_value_column_size => l_value_column_size
,p_meaning_column_name => l_meaning_column_name
,p_meaning_column_type => l_meaning_column_type -- Bug 9705126
,p_meaning_column_size => l_meaning_column_size
,p_id_column_name => l_id_column_name
,p_id_column_type => l_id_column_type
,p_id_column_size => l_id_column_size
,p_where_order_by => l_additional_where_clause
,p_additional_columns => ''
,p_owner => l_owner --G_CURRENT_USER_ID
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
);
G_TOKEN_TBL(4).Token_Value := 'Update_Value_Set_Table_Inf';
EGO_EXT_FWK_PUB.Insert_Value_Set_Table_Inf
(
p_api_version => 1.0
,p_value_set_id => l_value_set_id
,p_table_application_id => l_table_application_id
,p_table_name => l_application_table_name
,p_value_column_name => l_value_column_name
,p_value_column_type => l_value_column_type
,p_value_column_size => l_value_column_size
,p_meaning_column_name => l_meaning_column_name --bug 9705126
,p_meaning_column_type => l_meaning_column_type
,p_meaning_column_size => l_meaning_column_size
,p_id_column_name => l_id_column_name
,p_id_column_type => l_id_column_type
,p_id_column_size => l_id_column_size
,p_where_order_by => l_additional_where_clause
,p_additional_columns => ''
,p_owner => l_owner --G_CURRENT_USER_ID
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
);
G_TOKEN_TBL(4).Token_Value := 'Insert_Value_Set_Table_Inf';
G_TOKEN_TBL.DELETE;
p_value_set_tbl(i).program_update_date := l_program_update_date;
p_value_set_tbl(i).last_update_date := l_last_update_date;
p_value_set_tbl(i).last_updated_by := l_last_updated_by;
p_value_set_tbl(i).last_update_login := l_last_update_login;
l_last_update_date := NULL;
l_last_updated_by := NULL;
l_last_update_login := NULL;
l_program_update_date := NULL;
l_program_update_date DATE;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = cp_value_set_name;
SELECT flex_value_id
FROM fnd_flex_values
WHERE flex_value_set_id= cp_flex_value_set_id
AND flex_value= cp_flex_value;
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id= cp_flex_value_set_id
AND flex_value_id = cp_flex_value_id;
SELECT validation_type, format_type ,maximum_size
FROM fnd_flex_value_sets
WHERE flex_Value_set_id = cp_value_set_id;
SELECT flex_value_set_name
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cp_value_set_id;
SELECT 1 AS exist
FROM EGO_FLEX_VALUE_VERSION_B
WHERE flex_value_set_id = cp_value_set_id
AND flex_value_id = cp_value_id
AND version_seq_id = cp_version_seq_id;
SELECT 1 AS exist
FROM EGO_FLEX_VALUE_VERSION_TL
WHERE --value_set_id = cp_value_set_id AND
flex_value_id = cp_value_id
AND version_seq_id = cp_version_seq_id
AND "LANGUAGE" = cp_lang_code;
SELECT disp_sequence
FROM ego_vs_values_disp_order
WHERE value_set_value_id = cp_flex_value_id;
SELECT 1 AS Seq_exist
FROM Ego_VS_Values_Disp_Order
WHERE disp_sequence = cp_disp_sequence
AND value_set_id = cp_value_set_id
AND ( value_set_value_id <> cp_value_id
OR cp_value_id IS NULL );
SELECT 1 AS Seq_exist
FROM EGO_FLEX_VALUE_VERSION_B
WHERE flex_value_set_id = cp_value_set_id
AND version_seq_id = cp_version_seq_id
AND SEQUENCE = cp_disp_sequence;
l_program_update_date := p_value_set_val_tbl(i).program_update_date;
l_last_update_date := p_value_set_val_tbl(i).last_update_date;
l_last_updated_by := p_value_set_val_tbl(i).last_updated_by;
l_last_update_login := p_value_set_val_tbl(i).last_update_login;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_transaction_type :=G_UPDATE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type =G_UPDATE AND l_flex_value_id IS NULL THEN
l_error_message_name := 'EGO_TRANS_TYPE_INVALID';
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_transaction_type :=G_UPDATE;
p_value_set_val_tl_tbl(j).transaction_type :=G_UPDATE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
p_value_set_val_tl_tbl(j).transaction_type := G_UPDATE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type=G_UPDATE THEN
IF l_process_status = G_PROCESS_RECORD THEN
Get_Key_Value_Columns
( p_value_set_id => l_value_set_id,
p_value_id => l_flex_value_id,
x_display_name => l_flex_value_meaning,
x_disp_sequence => l_disp_sequence,
x_start_date_active => l_start_active_date,
x_end_date_active => l_end_active_date,
x_description => l_description,
x_enabled_flag => l_enabled_flag,
x_return_status => l_return_status,
x_return_msg => l_return_msg);
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
INSERT INTO EGO_FLEX_VALUE_VERSION_B
(FLEX_VALUE_SET_ID,FLEX_VALUE_ID,VERSION_SEQ_ID,SEQUENCE
,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (l_VALUE_SET_ID,l_FLEX_VALUE_ID,G_OUT_VERSION_SEQ_ID, l_disp_sequence,
G_Party_Id,SYSDATE,G_Party_Id ,SYSDATE,G_Login_Id);
write_debug(G_PKG_Name,l_api_name, ' Inserted reord in EGO_FLEX_VALUE_VERSION_B table '||p_value_set_val_tl_tbl(j).flex_value);
INSERT INTO EGO_FLEX_VALUE_VERSION_TL
( FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
VALUES ( l_FLEX_VALUE_ID,G_OUT_VERSION_SEQ_ID,SYSDATE,G_Party_Id, SYSDATE,G_Party_Id,G_Login_Id,
l_description, l_flex_value_meaning,l_language,l_source_lang) ;
write_debug(G_PKG_Name,l_api_name, ' Inserted reord in EGO_FLEX_VALUE_VERSION_TL table '||p_value_set_val_tl_tbl(j).flex_value
||','||l_language);
ELSIF l_transaction_type=G_UPDATE THEN
write_debug(G_PKG_Name,l_api_name,' UPDATE MODE : calling EGO_EXT_FWK_PUB.Update_Value_Set_Val API ');
write_debug(G_PKG_Name,l_api_name,' UPDATE MODE : calling EGO_EXT_FWK_PUB.Update_Value_Set_Val API value=>'||l_flex_value);
EGO_EXT_FWK_PUB.Update_Value_Set_Val
(
p_api_version => p_api_version
,p_value_set_name => l_value_set_name
,p_internal_name => l_flex_value
,p_display_name => l_flex_value_meaning
,p_description => CASE l_description
WHEN G_NULL_CHAR THEN NULL
ELSE l_description
END --l_description
,p_sequence => l_disp_sequence
,p_start_date => CASE l_start_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_start_active_date
END
,p_end_date => CASE l_end_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_end_active_date
END
,p_enabled => l_enabled_flag
,p_owner => l_owner
,p_init_msg_list => l_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
,x_is_versioned => l_is_versioned
,x_valueSetId => l_value_set_id);
G_TOKEN_TBL(4).Token_Value := 'Update_Value_Set_Val';
write_debug(G_PKG_Name,l_api_name,' Value record not exist. Insert record ');
INSERT INTO EGO_FLEX_VALUE_VERSION_B
(FLEX_VALUE_SET_ID,FLEX_VALUE_ID,VERSION_SEQ_ID,SEQUENCE
,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (l_VALUE_SET_ID,l_FLEX_VALUE_ID,G_OUT_VERSION_SEQ_ID, l_disp_sequence,
G_Party_Id,SYSDATE,G_Party_Id ,SYSDATE,G_Login_Id);
write_debug(G_PKG_Name,l_api_name,' Trans record not exist. Insert record '||l_language);
INSERT INTO EGO_FLEX_VALUE_VERSION_TL
( FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
VALUES ( l_FLEX_VALUE_ID,G_OUT_VERSION_SEQ_ID,SYSDATE,G_Party_Id, SYSDATE,G_Party_Id,G_Login_Id,
l_description, l_flex_value_meaning,l_language,l_source_lang) ;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
p_value_set_val_tbl(i).program_update_date := l_program_update_date;
p_value_set_val_tbl(i).last_update_date := l_last_update_date;
p_value_set_val_tbl(i).last_updated_by := l_last_updated_by;
p_value_set_val_tbl(i).last_update_login := l_last_update_login;
l_program_update_date := NULL;
l_last_update_date := NULL;
l_last_updated_by := NULL;
l_last_update_login := NULL;
l_program_update_date DATE;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT validation_type, format_type,maximum_size
FROM fnd_flex_value_sets fvs
WHERE fvs.flex_Value_set_id = cp_value_set_id; -- bug 12394675
SELECT disp_sequence
FROM ego_vs_values_disp_order
WHERE value_set_value_id = cp_flex_value_id;
SELECT 1 AS Seq_exist
FROM Ego_VS_Values_Disp_Order
WHERE disp_sequence = cp_disp_sequence
AND value_set_id = cp_value_set_id
AND ( value_set_value_id <> cp_value_id
OR cp_value_id IS NULL);
l_program_update_date := p_value_set_val_tbl(i).program_update_date;
l_last_update_date := p_value_set_val_tbl(i).last_update_date;
l_last_updated_by := p_value_set_val_tbl(i).last_updated_by;
l_last_update_login := p_value_set_val_tbl(i).last_update_login;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_transaction_type :=G_UPDATE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type <> G_UPDATE THEN
l_process_status := G_ERROR_RECORD;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
END IF; -- END IF l_transaction_type <> G_UPDATE
IF l_transaction_type =G_UPDATE THEN
--Find value of desc and s
Get_Key_Value_Columns
( p_value_set_id => l_value_set_id,
p_value_id => l_flex_value_id,
x_display_name => l_flex_value_meaning,
x_disp_sequence => l_disp_sequence,
x_start_date_active => l_start_active_date,
x_end_date_active => l_end_active_date,
x_description => l_description,
x_enabled_flag => l_enabled_flag,
x_return_status => l_return_status,
x_return_msg => l_return_msg);
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
EGO_EXT_FWK_PUB.Update_Value_Set_Val
(
p_api_version => p_api_version
,p_value_set_name => l_value_set_name
,p_internal_name => l_flex_value
,p_display_name => l_flex_value_meaning
,p_description => CASE l_description
WHEN G_NULL_CHAR THEN NULL
ELSE l_description
END --l_description
,p_sequence => l_disp_sequence
,p_start_date => CASE l_start_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_start_active_date
END
,p_end_date => CASE l_end_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_end_active_date
END
,p_enabled => l_enabled_flag
,p_owner => l_owner
,p_init_msg_list => l_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_return_msg
,x_is_versioned => l_is_versioned
,x_valueSetId => l_value_set_id);
G_TOKEN_TBL(4).Token_Value := 'Update_Value_Set_Val';
END IF;-- END IF l_transaction_type =G_UPDATE THEN
p_value_set_val_tbl(i).program_update_date := l_program_update_date;
p_value_set_val_tbl(i).last_update_date := l_last_update_date;
p_value_set_val_tbl(i).last_updated_by := l_last_updated_by;
p_value_set_val_tbl(i).last_update_login := l_last_update_login;
l_program_update_date := NULL;
l_last_update_date := NULL;
l_last_updated_by := NULL;
l_last_update_login := NULL;
l_program_update_date := p_value_set_val_tl_tbl(i).program_update_date;
l_last_update_date := p_value_set_val_tl_tbl(i).last_update_date;
l_last_updated_by := p_value_set_val_tl_tbl(i).last_updated_by;
l_last_update_login := p_value_set_val_tl_tbl(i).last_update_login;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_token_table.DELETE;
l_transaction_type :=G_UPDATE;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type <> G_UPDATE THEN
l_process_status := G_ERROR_RECORD;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
END IF; -- END IF l_transaction_type <> G_UPDATE
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
IF l_transaction_type =G_UPDATE THEN
--Find value of desc and s
Get_Key_Value_Columns
( p_value_set_id => l_value_set_id,
p_value_id => l_flex_value_id,
x_display_name => l_flex_value_meaning,
x_disp_sequence => l_disp_sequence,
x_start_date_active => l_start_active_date,
x_end_date_active => l_end_active_date,
x_description => l_description,
x_enabled_flag => l_enabled_flag,
x_return_status => l_return_status,
x_return_msg => l_return_msg);
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
EGO_EXT_FWK_PUB.Update_Value_Set_Val
(
p_api_version => p_api_version
,p_value_set_name => l_value_set_name
,p_internal_name => l_flex_value
,p_display_name => l_flex_value_meaning
,p_description => CASE l_description
WHEN G_NULL_CHAR THEN NULL
ELSE l_description
END --l_description
,p_sequence => l_disp_sequence
,p_start_date => CASE l_start_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_start_active_date
END
,p_end_date => CASE l_end_active_date
WHEN G_NULL_DATE THEN NULL
ELSE l_end_active_date
END
,p_enabled => l_enabled_flag
,p_owner => l_owner
,p_init_msg_list => l_init_msg_list
,p_commit => FND_API.G_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_return_msg
,x_is_versioned => l_is_versioned
,x_valueSetId => l_value_set_id);
G_TOKEN_TBL(4).Token_Value := 'Update_Value_Set_Val';
END IF;-- END IF l_transaction_type =G_UPDATE THEN
p_value_set_val_tl_tbl(i).program_update_date := l_program_update_date;
p_value_set_val_tl_tbl(i).last_update_date := l_last_update_date;
p_value_set_val_tl_tbl(i).last_updated_by := l_last_updated_by;
p_value_set_val_tl_tbl(i).last_update_login := l_last_update_login;
l_program_update_date := NULL;
l_last_update_date := NULL;
l_last_updated_by := NULL;
l_last_update_login := NULL;
l_program_update_date DATE;
l_last_update_date DATE;
l_last_updated_by NUMBER(15);
l_last_update_login NUMBER(15);
SELECT flex_value_id
FROM fnd_flex_value_sets vs, fnd_flex_values val
WHERE vs.flex_value_set_id= val.flex_value_set_id
AND vs.flex_value_set_name= cp_value_set_name
AND flex_value=cp_flex_value;
SELECT flex_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = cp_value_set_name;
SELECT 1 AS valid_parent
FROM Ego_Value_Set_Ext
WHERE value_set_id = cp_value_set_id
AND parent_value_set_id = cp_parent_vs_id;
SELECT validation_type
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cp_value_set_id;
SELECT disp_sequence
FROM Ego_VS_Values_Disp_Order
WHERE value_set_id = cp_value_set_id
AND value_set_value_id = cp_value_id;
l_program_update_date := p_value_set_tbl(i).program_update_date;
l_last_update_date := p_value_set_tbl(i).last_update_date;
l_last_updated_by := p_value_set_tbl(i).last_updated_by;
l_last_update_login := p_value_set_tbl(i).last_update_login;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_transaction_type :=G_UPDATE;
IF l_parent_vs_id IS NOT NULL AND l_transaction_type= G_UPDATE THEN
FOR i IN cur_valid_parent (l_value_set_id, l_parent_vs_id)
LOOP
l_valid_parent := i.valid_parent;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
END IF; -- END IF l_parent_vs_id IS NOT NULL AND l_transaction_type= G_UPDATE THEN
IF l_transaction_type = G_UPDATE THEN
--Get value of require field if they are null
Get_Key_VS_Columns (p_value_set_id => l_value_set_id,
p_transaction_id => l_transaction_id,
x_maximum_size => l_maximum_size,
x_maximum_value => l_maximum_value,
x_minimum_value => l_minimum_value,
x_description => l_description,
x_longlist_flag => l_longlist_flag,
x_format_code => l_format_code,
x_validation_code => l_validation_code,
x_return_status => l_return_status,
x_return_msg => l_return_msg
);
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
ELSIF l_transaction_type=G_UPDATE THEN
-- In case of updating child value set, we do always expect all values associated to a child value set to be passed.
-- Also other value will always get deleted
-- Create a collection to contain values for a child value set
-- Create a Varray containing values corresponding to a child value set
IF p_valueset_val_tab.Count>0 THEN
write_debug(G_PKG_Name,l_api_name,' IN Update Mode Count of record is : '||p_valueset_val_tab.Count);
l_last_updated_by := g_user_id;
l_last_update_date := SYSDATE;
l_last_update_login := g_login_id;
write_debug(G_PKG_Name,l_api_name,' : calling EGO_EXT_FWK_PUB.Update_Child_Value_Set API ');
EGO_EXT_FWK_PUB.Update_Child_Value_Set
(
p_api_version => l_api_version
,p_value_set_id => l_value_set_id
,p_description => l_description
,p_format_code => l_format_code
,p_owner => G_USER_ID --l_owner
,child_vs_value_ids => l_child_vs_value_ids
,p_init_msg_list => fnd_api.g_FALSE
,p_commit => fnd_api.g_FALSE
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_return_msg
);
G_TOKEN_TBL(4).Token_Value := 'Update_Child_Value_Set';
p_value_set_tbl(i).program_update_date := l_program_update_date;
p_value_set_tbl(i).last_update_date := l_last_update_date;
p_value_set_tbl(i).last_updated_by := l_last_updated_by;
p_value_set_tbl(i).last_update_login := l_last_update_login;
l_program_update_date := NULL;
l_last_update_date := NULL;
l_last_updated_by := NULL;
l_last_update_login := NULL;
l_child_vs_value_ids.DELETE;
PROCEDURE Delete_Processed_Value_Sets( p_set_process_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_return_msg OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR2(100) := 'Delete_Processed_Value_Sets';
DELETE FROM ego_flex_value_set_intf
WHERE process_status = G_SUCCESS_RECORD
AND (p_set_process_id IS NULL
OR set_process_id = p_set_process_id);
write_debug(G_PKG_Name,l_api_name,'Deleted data from value Set Table ');
DELETE FROM ego_flex_value_intf
WHERE process_status = G_SUCCESS_RECORD
AND (p_set_process_id IS NULL
OR set_process_id = p_set_process_id);
write_debug(G_PKG_Name,l_api_name,' Deleted data from Value Table');
DELETE FROM ego_flex_value_tl_intf
WHERE process_status = G_SUCCESS_RECORD
AND (p_set_process_id IS NULL
OR set_process_id = p_set_process_id);
write_debug(G_PKG_Name,l_api_name,'End of Delete_Processed_Value_Sets');
x_return_msg := 'ego_vs_bulkload_pvt.Delete_Processed_Value_Sets - '||SQLERRM;
END Delete_Processed_Value_Sets;