The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Name: INSERT_APP_MENU_VB
|
| Description: it is a wrapper for FND_MENUS_PKG.INSERT_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters: x_menu_id - Menu id
| x_menu_name - Menu Name
| x_user_menu_name - User Menu Name
| x_menu_type - Menu Type
| x_description - Description
| x_user id -User Id
|
| Notes:
|
+============================================================================*/
PROCEDURE INSERT_APP_MENU_VB(X_MENU_ID in NUMBER,
X_MENU_NAME in VARCHAR2,
X_USER_MENU_NAME in VARCHAR2,
X_MENU_TYPE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
row_id VARCHAR2(30);
DELETE FND_MENUS WHERE MENU_ID = X_MENU_ID;
DELETE FND_MENUS_TL WHERE MENU_ID = X_MENU_ID;
FND_MENUS_PKG.INSERT_ROW( X_ROWID => row_id,
X_MENU_ID => X_MENU_ID,
X_MENU_NAME => X_MENU_NAME,
X_USER_MENU_NAME => X_USER_MENU_NAME,
X_MENU_TYPE => X_MENU_TYPE,
X_DESCRIPTION => X_DESCRIPTION,
X_CREATION_DATE => sysdate,
X_CREATED_BY => x_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_VB',
x_mode => 'I');
END INSERT_APP_MENU_VB;
| Name: UPDATE_APP_MENU_VB
|
| Description: it is a wrapper for FND_MENUS_PKG.UPDATE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters: x_menu_id - Menu id
| x_menu_name - Menu Name
| x_user_menu_name - User Menu Name
| x_menu_type - Menu Type
| x_description - Description
| x_user id -User Id
|
| Notes:
|
+============================================================================*/
PROCEDURE UPDATE_APP_MENU_VB(X_MENU_ID in NUMBER,
X_MENU_NAME in VARCHAR2,
X_USER_MENU_NAME in VARCHAR2,
X_MENU_TYPE in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
row_id VARCHAR2(30);
FND_MENUS_PKG.UPDATE_ROW(X_MENU_ID => X_MENU_ID,
X_MENU_NAME => X_MENU_NAME,
X_USER_MENU_NAME => X_USER_MENU_NAME,
X_MENU_TYPE => X_MENU_TYPE,
X_DESCRIPTION => X_DESCRIPTION,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_APP_MENU_VB',
x_mode => 'I');
END UPDATE_APP_MENU_VB;
| Name: DELETE_APP_MENU_VB
|
| Description: it is a wrapper for FND_MENUS_PKG.DELETE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters: x_menu_id - Menu id
|
| Notes:
|
+============================================================================*/
PROCEDURE DELETE_APP_MENU_VB(X_MENU_ID in NUMBER
) IS
row_id VARCHAR2(30);
FND_MENUS_PKG.DELETE_ROW(X_MENU_ID => X_MENU_ID);
x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_APP_MENU_VB',
x_mode => 'I');
END DELETE_APP_MENU_VB;
| insert as a new menu.
| Return : 'N' : Name Invalid, The name alreday exist
| 'U' : User Name Invalid, The user name alreday exist
| 'T' : True , The names don't exist. It can be added
| Parameters: X_MENU_ID Menu Id that will be inserted
| X_MENU_NAME Menu Name
| X_USER_MENU_NAME User Menu Name
+============================================================================*/
FUNCTION CHECK_MENU_NAMES(X_MENU_ID in NUMBER,
X_MENU_NAME in VARCHAR2,
X_USER_MENU_NAME in VARCHAR2
) RETURN VARCHAR2 IS
h_count NUMBER;
SELECT count(*)
INTO h_count
FROM FND_MENUS_VL
WHERE MENU_ID <> X_MENU_ID
AND (MENU_NAME = X_MENU_NAME);
SELECT count(*)
INTO h_count
FROM FND_MENUS_VL
WHERE MENU_ID <> X_MENU_ID
AND (upper(USER_MENU_NAME) = X_USER_MENU_NAME);
| Name: INSERT_FORM_FUNCTION_VB
|
| Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.INSERT_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE INSERT_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER,
X_WEB_HOST_NAME in VARCHAR2,
X_WEB_AGENT_NAME in VARCHAR2,
X_WEB_HTML_CALL in VARCHAR2,
X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
X_WEB_SECURED in VARCHAR2,
X_WEB_ICON in VARCHAR2,
X_OBJECT_ID in NUMBER,
X_REGION_APPLICATION_ID in NUMBER,
X_REGION_CODE in VARCHAR2,
X_FUNCTION_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_FORM_ID in NUMBER,
X_PARAMETERS in VARCHAR2,
X_TYPE in VARCHAR2,
X_USER_FUNCTION_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
row_id VARCHAR2(30);
DELETE FND_FORM_FUNCTIONS WHERE FUNCTION_ID = X_FUNCTION_ID;
DELETE FND_FORM_FUNCTIONS_TL WHERE FUNCTION_ID = X_FUNCTION_ID;
fnd_form_functions_pkg.INSERT_ROW( X_ROWID => row_id,
X_FUNCTION_ID => X_FUNCTION_ID,
X_WEB_HOST_NAME => X_WEB_HOST_NAME,
X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
X_WEB_HTML_CALL => X_WEB_HTML_CALL,
X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
X_WEB_SECURED => X_WEB_SECURED,
X_WEB_ICON => X_WEB_ICON,
X_OBJECT_ID => X_OBJECT_ID,
X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
X_REGION_CODE => X_REGION_CODE,
X_FUNCTION_NAME => X_FUNCTION_NAME,
X_APPLICATION_ID => X_APPLICATION_ID,
X_FORM_ID => X_FORM_ID,
X_PARAMETERS => X_PARAMETERS,
X_TYPE => X_TYPE,
X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_CREATION_DATE => sysdate,
X_CREATED_BY => x_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_FORM_FUNCTION_VB',
x_mode => 'I');
END INSERT_FORM_FUNCTION_VB;
| Name: UPDATE_FORM_FUNCTION_VB
|
| Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.UPDATE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE UPDATE_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER,
X_WEB_HOST_NAME in VARCHAR2,
X_WEB_AGENT_NAME in VARCHAR2,
X_WEB_HTML_CALL in VARCHAR2,
X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
X_WEB_SECURED in VARCHAR2,
X_WEB_ICON in VARCHAR2,
X_OBJECT_ID in NUMBER,
X_REGION_APPLICATION_ID in NUMBER,
X_REGION_CODE in VARCHAR2,
X_FUNCTION_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_FORM_ID in NUMBER,
X_PARAMETERS in VARCHAR2,
X_TYPE in VARCHAR2,
X_USER_FUNCTION_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
row_id VARCHAR2(30);
fnd_form_functions_pkg.UPDATE_ROW(X_FUNCTION_ID => X_FUNCTION_ID,
X_WEB_HOST_NAME => X_WEB_HOST_NAME,
X_WEB_AGENT_NAME => X_WEB_AGENT_NAME,
X_WEB_HTML_CALL => X_WEB_HTML_CALL,
X_WEB_ENCRYPT_PARAMETERS => X_WEB_ENCRYPT_PARAMETERS,
X_WEB_SECURED => X_WEB_SECURED,
X_WEB_ICON => X_WEB_ICON,
X_OBJECT_ID => X_OBJECT_ID,
X_REGION_APPLICATION_ID => X_REGION_APPLICATION_ID,
X_REGION_CODE => X_REGION_CODE,
X_FUNCTION_NAME => X_FUNCTION_NAME,
X_APPLICATION_ID => X_APPLICATION_ID,
X_FORM_ID => X_FORM_ID,
X_PARAMETERS => X_PARAMETERS,
X_TYPE => X_TYPE,
X_USER_FUNCTION_NAME => X_USER_FUNCTION_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_FORM_FUNCTION_VB',
x_mode => 'I');
END UPDATE_FORM_FUNCTION_VB;
| Name: DELETE_FORM_FUNCTION_VB
|
| Description: it is a wrapper for FND_FORM_FUNCTIONS_PKG.DELETE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE DELETE_FORM_FUNCTION_VB(X_FUNCTION_ID in NUMBER
) IS
row_id VARCHAR2(30);
fnd_form_functions_pkg.DELETE_ROW(X_FUNCTION_ID => X_FUNCTION_ID);
x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_FORM_FUNCTION_VB',
x_mode => 'I');
END DELETE_FORM_FUNCTION_VB;
| insert as a new function.
| Return : 'N' : Name Invalid, The name alreday exist
| 'U' : User Name Invalid, The user name alreday exist
| 'T' : True , The names don't exist. It can be added
| Parameters: X_FUNCTION_ID Menu Id that will be inserted
| X_FUNCTION_NAME Menu Name
| X_USER_FUNCTION_NAME User Menu Name
+============================================================================*/
FUNCTION CHECK_FUNCTION_NAMES(X_FUNCTION_ID in NUMBER,
X_FUNCTION_NAME in VARCHAR2,
X_USER_FUNCTION_NAME in VARCHAR2
) RETURN VARCHAR2 IS
h_count NUMBER;
SELECT count(*)
INTO h_count
FROM FND_FORM_FUNCTIONS_VL
WHERE FUNCTION_ID <> X_FUNCTION_ID
AND (FUNCTION_NAME = X_FUNCTION_NAME);
SELECT count(*)
INTO h_count
FROM FND_FORM_FUNCTIONS_VL
WHERE FUNCTION_ID <> X_FUNCTION_ID
AND (USER_FUNCTION_NAME = X_USER_FUNCTION_NAME);
| Name: INSERT_APP_MENU_ENTRIES_VB
|
| Description: it is a wrapper for FND_MENU_ENTRIES_PKG.INSERT_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE INSERT_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
X_ENTRY_SEQUENCE in NUMBER,
X_SUB_MENU_ID in NUMBER,
X_FUNCTION_ID in NUMBER,
X_GRANT_FLAG in VARCHAR2,
X_PROMPT in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
row_id VARCHAR2(30);
DELETE FND_MENU_ENTRIES WHERE MENU_ID = X_MENU_ID AND ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
DELETE FND_MENU_ENTRIES_TL WHERE MENU_ID = X_MENU_ID AND ENTRY_SEQUENCE = X_ENTRY_SEQUENCE;
FND_MENU_ENTRIES_PKG.INSERT_ROW ( X_ROWID => row_id,
X_MENU_ID => X_MENU_ID,
X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
X_SUB_MENU_ID => X_SUB_MENU_ID,
X_FUNCTION_ID => X_FUNCTION_ID,
X_GRANT_FLAG => X_GRANT_FLAG,
X_PROMPT => X_PROMPT,
X_DESCRIPTION => X_DESCRIPTION,
X_CREATION_DATE => sysdate,
X_CREATED_BY => x_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_ENTRIES_VB',
x_mode => 'I');
END INSERT_APP_MENU_ENTRIES_VB;
| Name: UPDATE_APP_MENU_ENTRIES_VB
|
| Description: it is a wrapper for FND_MENU_ENTRIES_PKG.UPDATE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE UPDATE_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
X_ENTRY_SEQUENCE in NUMBER,
X_SUB_MENU_ID in NUMBER,
X_FUNCTION_ID in NUMBER,
X_GRANT_FLAG in VARCHAR2,
X_PROMPT in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_USER_ID in NUMBER
) IS
BEGIN
FND_MENU_ENTRIES_PKG.UPDATE_ROW(X_MENU_ID => X_MENU_ID,
X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE,
X_SUB_MENU_ID => X_SUB_MENU_ID,
X_FUNCTION_ID => X_FUNCTION_ID,
X_GRANT_FLAG => X_GRANT_FLAG,
X_PROMPT => X_PROMPT,
X_DESCRIPTION => X_DESCRIPTION,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => x_user_id,
X_LAST_UPDATE_LOGIN => 0 );
x_source => 'BSC_LAUNCH_PAD_PVT.UPDATE_APP_MENU_ENTRIES_VB',
x_mode => 'I');
END UPDATE_APP_MENU_ENTRIES_VB;
| Name: DELETE_APP_MENU_ENTRIES_VB
|
| Description: it is a wrapper for FND_MENU_ENTRIES_PKG.DELETE_ROW function
| This procedure is to be called from a VB program.
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
PROCEDURE DELETE_APP_MENU_ENTRIES_VB(X_MENU_ID in NUMBER,
X_ENTRY_SEQUENCE in NUMBER
) IS
BEGIN
FND_MENU_ENTRIES_PKG.DELETE_ROW(X_MENU_ID => X_MENU_ID,
X_ENTRY_SEQUENCE => X_ENTRY_SEQUENCE);
x_source => 'BSC_LAUNCH_PAD_PVT.DELETE_APP_MENU_ENTRIES_VB',
x_mode => 'I');
END DELETE_APP_MENU_ENTRIES_VB;
| If there is an error, the procedure inserts the error
| message in BSC_MESSAGE_LOGS table.
|
| Parameters:
+============================================================================*/
FUNCTION SECURITY_RULE_EXISTS_VB(responsibility_key in varchar2,
rule_type in varchar2 default 'F', -- F = Function, M = Menu
rule_name in varchar2
) RETURN VARCHAR2 IS
h_val VARCHAR2(1);
SELECT MENU_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_ID=X_RESPO;
SELECT COUNT(*) VAL
INTO h_count
FROM (
select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID = h_top_menu
UNION
select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID = h_top_menu)
UNION
select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID IN (select SUB_MENU_ID from FND_MENU_ENTRIES_VL
WHERE MENU_ID = h_top_menu))
) MNS
WHERE MNS.SUB_MENU_ID=X_MENU_ID;
| It never update or delete an existing menu or function.
|
| Fixed Bug#2195153: Check user_menu_name/user_function_name
| to see if the menu/function already
| exist in the target system. This is after
| checking menu_name/function_name.
|
| Return : TRUE : no errors
| FALSE : error
|
| Parameters: x_src_db_link source db link.
+============================================================================*/
FUNCTION Migrate_Custom_Links(
x_src_db_link IN VARCHAR2
) RETURN BOOLEAN IS
h_sql VARCHAR2(32000);
SELECT menu_id
FROM fnd_menus
WHERE menu_name = h_menu_name;
SELECT menu_id
FROM fnd_menus_vl
WHERE user_menu_name = h_user_menu_name;
SELECT function_id
FROM fnd_form_functions
WHERE function_name = h_function_name;
SELECT function_id
FROM fnd_form_functions_vl
WHERE user_function_name = h_user_function_name;
h_sql := 'SELECT DISTINCT M.MENU_NAME, M.USER_MENU_NAME, T.LINK_ID'||
' FROM BSC_TAB_VIEW_LABELS_B T, FND_MENUS_VL@'||x_src_db_link||' M'||
' WHERE T.LABEL_TYPE = 2 AND NVL(T.LINK_ID, -1) <> -1 AND T.LINK_ID = M.MENU_ID';
SELECT fnd_menus_s.nextval INTO h_menu_id FROM DUAL;
h_sql := 'SELECT type, description'||
' FROM fnd_menus_vl@'||x_src_db_link||
' WHERE menu_name =:1';
FND_MENUS_PKG.INSERT_ROW(
X_ROWID => h_row_id,
X_MENU_ID => h_menu_id,
X_MENU_NAME => h_menu_name,
X_USER_MENU_NAME => h_user_menu_name,
X_MENU_TYPE => h_menu_type,
X_DESCRIPTION => h_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => h_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => h_user_id,
X_LAST_UPDATE_LOGIN => 0
);
h_sql := 'SELECT SF.FUNCTION_NAME, SF.USER_FUNCTION_NAME, SF.FUNCTION_ID'||
' FROM FND_MENU_ENTRIES@'||x_src_db_link||' SE, FND_FORM_FUNCTIONS_VL@'||x_src_db_link||' SF'||
' WHERE SE.MENU_ID = :1 AND SE.FUNCTION_ID = SF.FUNCTION_ID AND'||
' SF.APPLICATION_ID = 271 AND SF.FUNCTION_NAME NOT IN ('||
' SELECT TF.FUNCTION_NAME FROM FND_MENU_ENTRIES TE, FND_FORM_FUNCTIONS TF'||
' WHERE TE.MENU_ID = :2 AND TE.FUNCTION_ID = TF.FUNCTION_ID AND TF.APPLICATION_ID = 271)';
SELECT fnd_form_functions_s.nextval INTO h_function_id FROM DUAL;
h_sql := 'SELECT web_host_name, web_agent_name, web_html_call,'||
' web_encrypt_parameters, web_secured, web_icon, object_id,'||
' region_application_id, region_code, application_id,'||
' form_id, parameters, type, description'||
' FROM fnd_form_functions_vl@'||x_src_db_link||
' WHERE function_name = :1';
FND_FORM_FUNCTIONS_PKG.INSERT_ROW(
X_ROWID => h_row_id,
X_FUNCTION_ID => h_function_id,
X_WEB_HOST_NAME => h_web_host_name,
X_WEB_AGENT_NAME => h_web_agent_name,
X_WEB_HTML_CALL => h_web_html_call,
X_WEB_ENCRYPT_PARAMETERS => h_web_encrypt_parameters,
X_WEB_SECURED => h_web_secured,
X_WEB_ICON => h_web_icon,
X_OBJECT_ID => h_object_id,
X_REGION_APPLICATION_ID => h_region_application_id,
X_REGION_CODE => h_region_code,
X_FUNCTION_NAME => h_function_name,
X_APPLICATION_ID => h_application_id,
X_FORM_ID => h_form_id,
X_PARAMETERS => h_parameters,
X_TYPE => h_type,
X_USER_FUNCTION_NAME => h_user_function_name,
X_DESCRIPTION => h_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => h_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => h_user_id,
X_LAST_UPDATE_LOGIN => 0
);
SELECT nvl(max(entry_sequence)+1, 1) INTO h_entry_sequence
FROM fnd_menu_entries
WHERE menu_id = h_menu_id;
h_sql := 'SELECT sub_menu_id, grant_flag, prompt, description'||
' FROM fnd_menu_entries_vl@'||x_src_db_link||
' WHERE menu_id = :1 AND function_id = :2';
FND_MENU_ENTRIES_PKG.INSERT_ROW (
X_ROWID => h_row_id,
X_MENU_ID => h_menu_id,
X_ENTRY_SEQUENCE => h_entry_sequence,
X_SUB_MENU_ID => h_sub_menu_id,
X_FUNCTION_ID => h_function_id,
X_GRANT_FLAG => h_grant_flag,
X_PROMPT => h_prompt,
X_DESCRIPTION => h_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => h_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => h_user_id,
X_LAST_UPDATE_LOGIN => 0
);
h_sql := 'UPDATE bsc_tab_view_labels_b l
SET link_id = NVL((SELECT t.menu_id
FROM fnd_menus t, fnd_menus@'||x_src_db_link||' s
WHERE t.menu_name = s.menu_name AND
l.link_id = s.menu_id),
NVL((SELECT t.menu_id
FROM fnd_menus_vl t, fnd_menus_vl@'||x_src_db_link||' s
WHERE t.user_menu_name = s.user_menu_name AND
l.link_id = s.menu_id),
-1))
WHERE label_type = 2';
SELECT menu_id
FROM fnd_responsibility_vl
WHERE responsibility_id = x_trg_resp;
h_sql := 'SELECT MENU_ID'||
' FROM FND_RESPONSIBILITY_VL@'||x_src_db_link||
/*' WHERE RESPONSIBILITY_ID = '||x_src_resp;*/
h_sql := 'SELECT'||
' L.LINK_ID,'||
' M.DESCRIPTION'||
' FROM'||
' BSC_TAB_VIEW_LABELS_B L,'||
' BSC_TAB_VIEW_LABELS_B@'||x_src_db_link||' LS,'||
' FND_MENUS_VL M'||
' WHERE'||
' L.TAB_ID = LS.TAB_ID AND'||
' L.TAB_VIEW_ID = LS.TAB_VIEW_ID AND'||
' L.LABEL_ID = LS.LABEL_ID AND'||
' L.LABEL_TYPE = 2 AND'||
' NVL(L.LINK_ID, -1) <> -1 AND'||
' L.LINK_ID = M.MENU_ID AND'||
' UPPER(SUBSTR(MENU_NAME,1,3)) = ''BSC'' AND'||
' NOT (L.LINK_ID IN (SELECT SUB_MENU_ID'||
' FROM FND_MENU_ENTRIES_VL'||
' WHERE MENU_ID = :1 AND SUB_MENU_ID IS NOT NULL)) AND'||
/*' WHERE MENU_ID = '||h_top_menu_id||' AND SUB_MENU_ID IS NOT NULL)) AND'||*/
' LS.LINK_ID IN (SELECT SUB_MENU_ID'||
' FROM FND_MENU_ENTRIES_VL@'||x_src_db_link||
' WHERE MENU_ID = :2 AND SUB_MENU_ID IS NOT NULL)';
SELECT nvl(max(entry_sequence)+1, 1) INTO h_entry_sequence
FROM fnd_menu_entries
WHERE menu_id = h_top_menu_id;
FND_MENU_ENTRIES_PKG.INSERT_ROW (
X_ROWID => h_row_id,
X_MENU_ID => h_top_menu_id,
X_ENTRY_SEQUENCE => h_entry_sequence,
X_SUB_MENU_ID => h_menu_id,
X_FUNCTION_ID => h_function_id,
X_GRANT_FLAG => h_grant_flag,
X_PROMPT => h_prompt,
X_DESCRIPTION => h_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => h_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => h_user_id,
X_LAST_UPDATE_LOGIN => 0
);
SELECT COUNT(0)
INTO l_count
FROM FND_MENU_ENTRIES
WHERE MENU_ID = p_Menu_Id
AND SUB_MENU_ID = p_Sub_Menu_Id;
SELECT ENTRY_SEQUENCE
FROM FND_MENU_ENTRIES
WHERE MENU_ID = p_Menu_Id
AND SUB_MENU_ID = p_Sub_Menu_Id;