[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Delete_LaunchPad_Links
(
p_menu_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
);
SELECT Function_Name
INTO l_function_name
FROM FND_FORM_FUNCTIONS
WHERE Function_Id = p_Function_Id;
SELECT NVL(MAX(Entry_Sequence),0)Entry_Sequence
INTO l_count
FROM FND_MENU_ENTRIES
WHERE Menu_Id =p_Menu_Id;
SELECT menu_id
INTO l_menu_id
FROM fnd_menus
WHERE menu_name = p_Menu_Name;
SELECT menu_name
INTO l_menu_name
FROM fnd_menus
WHERE menu_id = p_Menu_Id;
SELECT DISTINCT A.Menu_Id
, A.Menu_Name
FROM FND_MENUS_VL A
,FND_RESPONSIBILITY_VL B
WHERE B.Application_Id =271
AND B.Menu_Id = A.MENU_ID
AND B.Responsibility_Id IN (SELECT DISTINCT Responsibility_Id from BSC_USER_TAB_ACCESS);
x_Root_Menu_Tbl.DELETE(table_index);
BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_ENTRIES_VB
(
X_Menu_Id => l_Root_Menu_Tbl(l_root_menu_count).Bsc_menu_id
, X_Entry_Sequence => get_next_entry_sequence(l_Root_Menu_Tbl(l_root_menu_count).Bsc_menu_id)
, X_Sub_Menu_Id => p_Launchpad_Id
, X_Function_Id => NULL
, X_Grant_Flag =>'Y'
, X_Prompt => NULL
, X_Description => p_Description
, X_User_Id => get_User_Id
);
l_user_id FND_MENUS.LAST_UPDATED_BY%TYPE := NULL;
SELECT FND_MENUS_S.NEXTVAL
INTO l_menu_id
FROM DUAL;
BSC_LAUNCH_PAD_PVT.INSERT_APP_MENU_VB
(
X_MENU_ID => l_menu_id
,X_MENU_NAME => l_menu_name
,X_USER_MENU_NAME => p_user_menu_name
,X_MENU_TYPE => p_menu_type
,X_DESCRIPTION => p_description
,X_USER_ID => get_User_Id
);
launchPad.This procedure should be called from update launchpad
Input Parameters :- p_Menu_Id
x_launch_pad_Rec
Out Parameters :- x_launch_pad_Rec
Creatore :- ashankar
/******************************************************************************/
PROCEDURE Retrieve_Launch_Pad
(
p_menu_id IN NUMBER
,x_launch_pad_Rec IN OUT NOCOPY BSC_LAUNCH_PAD_PUB.Bsc_LauchPad_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
FND_MSG_PUB.Initialize;
SELECT menu_id
,menu_name
,type
,last_update_date
,last_updated_by
,last_update_login
,user_menu_name
,description
INTO x_launch_pad_Rec.Bsc_menu_id
,x_launch_pad_Rec.Bsc_menu_name
,x_launch_pad_Rec.Bsc_type
,x_launch_pad_Rec.Bsc_last_update_date
,x_launch_pad_Rec.Bsc_last_updated_by
,x_launch_pad_Rec.Bsc_last_update_login
,x_launch_pad_Rec.Bsc_user_menu_name
,x_launch_pad_Rec.Bsc_description
FROM FND_MENUS_VL
WHERE menu_id = p_menu_id;
Name :- Update_Launch_Pad
Description :- This procedure will update the menu entries in the database.
It will get the previous values from the database and check
it with the new values. if the new values are being passed
then they will be updated.. otherwise the old values will be
retained.
Input Parameters:- p_launch_pad_rec --> which holds the metadata of the menu.
Output Parameters :- x_return_status
creator :-ashankar
/******************************************************************************/
PROCEDURE Update_Launch_Pad
(
p_launch_pad_rec IN BSC_LAUNCH_PAD_PUB.Bsc_LauchPad_Rec_Type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_launch_pad_rec BSC_LAUNCH_PAD_PUB.Bsc_LauchPad_Rec_Type;
SAVEPOINT UpdateLaunchPad;
IF(p_launch_pad_rec.Bsc_last_update_login IS NOT NULL) THEN
l_launch_pad_rec.Bsc_last_update_login := p_launch_pad_rec.Bsc_last_update_login;
FND_MENUS_PKG.UPDATE_ROW
(
X_MENU_ID => l_launch_pad_rec.Bsc_menu_id
,X_MENU_NAME => l_launch_pad_rec.Bsc_menu_name
,X_USER_MENU_NAME => l_launch_pad_rec.Bsc_user_menu_name
,X_MENU_TYPE => l_launch_pad_rec.Bsc_type
,X_DESCRIPTION => l_launch_pad_rec.Bsc_description
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => get_User_Id
,X_LAST_UPDATE_LOGIN => 0
);
ROLLBACK TO UpdateLaunchPad;
ROLLBACK TO UpdateLaunchPad;
x_msg_data := x_msg_data||' -> BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
ROLLBACK TO UpdateLaunchPad;
x_msg_data := x_msg_data||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
END Update_Launch_Pad;
Name :- Update_Launch_Pad
Description :- This procedure will update the launchpad metadata.
It will validate if the menu name and the user menu name being
passed are unique or not.
It will remove all the associations of menus and fucntions and
recreate if any functions are there.
Validations :-
1. Check if the user_menu_name being passed is null or not.
if yes then throw the exception.
2. Check for the validity of the menu id
3. Check if the menu name is null then retrieve the menu name
4. Validate menu name and user menu name for uniqueness
5. First update the menu metadata.
6. remove the menu and function assocation
7. Recreate the associations if the fucnction ids are not null.
Created by :- ashankar 29-OCT-2003
/******************************************************************************/
PROCEDURE Update_Launch_Pad
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
,p_menu_id IN NUMBER
,p_menu_name IN VARCHAR2 := NULL
,p_user_menu_name IN VARCHAR2
,p_menu_type IN VARCHAR2
,p_description IN VARCHAR2
,p_fucntion_ids IN VARCHAR2
,p_fucntions_order IN VARCHAR2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_check_val VARCHAR2(2);
BSC_LAUNCH_PAD_PUB.Update_Launch_Pad
(
p_launch_pad_rec => l_launch_pad_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
The logic here is to delete all the previous associations
of the menus and fucntions and create the new asociations
if there are any new fucntions
1.Delete all the menu and fucntion asspciations
2.Recreate the menu and the fucntion associations.
/****************************************************/
Delete_MenuFunction_Link
(
p_menu_id => p_menu_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
x_msg_data := x_msg_data||' -> BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
x_msg_data := x_msg_data||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad ';
END Update_Launch_Pad;
Name :- Delete_Root_Menu_LaunchPad
Description :- This procedure will delete the entry of the launchpads from the rootmenus
when the launchpad is deleted.
Input :- p_Launch_pad_Id
Creator :- ashankar 12-DEC-03
/**************************************************************************************/
PROCEDURE Delete_Root_Menu_LaunchPad
(
p_Launch_Pad_Id FND_MENUS.menu_id%TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
l_Root_Menu_Tbl BSC_LAUNCH_PAD_PUB.Bsc_LauchPad_Tbl_Type;
SAVEPOINT DeleteRootMenuLaunchPad;
BSC_LAUNCH_PAD_PVT.DELETE_APP_MENU_ENTRIES_VB
(
X_Menu_Id => l_Root_Menu_Tbl(l_root_menu_count).Bsc_menu_id
, X_Entry_Sequence => l_entry_sequence
);
ROLLBACK TO DeleteRootMenuLaunchPad;
ROLLBACK TO DeleteRootMenuLaunchPad;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.Delete_Root_Menu_LaunchPad ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.Delete_Root_Menu_LaunchPad ';
END Delete_Root_Menu_LaunchPad;
| Description: It is a wrapper for FND_MENUS_PKG.DELETE_ROW function.
| This procedure is to be called from a JAVA Layer
|
| Parameters: x_menu_id - Menu id of the Launch Pad
| Validations : need to check if the menu id being passed is the valid one or not.
| if not then throw the exception that the menu id is invalid /no menu exists
| by this id.if it is valid then only delete the menu. otherwise not.
|
| Notes:
|
+============================================================================*/
PROCEDURE Delete_Launch_Pad
(
p_menu_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
FND_MENUS_PKG.DELETE_ROW
(
X_MENU_ID => p_menu_id
);
Delete_Root_Menu_LaunchPad
(
p_Launch_Pad_Id => p_menu_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
Delete_MenuFunction_Link
(
p_menu_id => p_menu_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Delete_LaunchPad_Links
(
p_menu_id => p_menu_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
END Delete_Launch_Pad;
and BSC_TAB_VIEW_LABELS_B.This should be called from within the delete_launchpad
Input Parameters :- 1.p_Menu_Id menu_id
output :- return status
Created BY :- ashankar
/************************************************************************************/
PROCEDURE Delete_LaunchPad_Links
(
p_menu_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
l_Count NUMBER;
SELECT Tab_id,Tab_view_id,Label_id
FROM BSC_TAB_VIEW_LABELS_VL
WHERE LINK_ID = p_menu_id
AND LABEL_TYPE =2;
SAVEPOINT deletelaunchpadlinks;
DELETE FROM BSC_TAB_VIEW_LABELS_TL
WHERE Tab_Id = cd.Tab_Id
AND Tab_view_id = cd.Tab_view_id
AND Label_id = cd.Label_id;
DELETE FROM BSC_TAB_VIEW_LABELS_B
WHERE Tab_Id = cd.Tab_Id
AND Tab_view_id = cd.Tab_view_id
AND Label_id = cd.Label_id;
ROLLBACK TO deletelaunchpadlinks;
ROLLBACK TO deletelaunchpadlinks;
x_msg_data := x_msg_data||' -> BSC_LAUNCH_PAD_PUB.Delete_LaunchPad_Links ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Delete_LaunchPad_Links ';
END Delete_LaunchPad_Links;
PROCEDURE Delete_MenuFunction_Link
(
p_menu_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
CURSOR c_menu_entries IS
SELECT entry_sequence
FROM FND_MENU_ENTRIES
WHERE menu_id = p_menu_id;
SAVEPOINT deletemenufunctionlink;
FND_MENU_ENTRIES_PKG.DELETE_ROW
(
X_MENU_ID => p_menu_id
,X_ENTRY_SEQUENCE => cd.entry_sequence
);
ROLLBACK TO deletemenufunctionlink;
ROLLBACK TO deletemenufunctionlink;
x_msg_data := x_msg_data||' -> BSC_LAUNCH_PAD_PUB.Delete_MenuFunction_Link ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Delete_MenuFunction_Link ';
END Delete_MenuFunction_Link;
FND_MENU_ENTRIES_PKG.INSERT_ROW
( X_ROWID => row_id
,X_MENU_ID => p_menu_id
,X_ENTRY_SEQUENCE => p_entry_sequence
,X_SUB_MENU_ID => NULL
,X_FUNCTION_ID => p_function_id
,X_GRANT_FLAG => 'Y'
,X_PROMPT => NULL
,X_DESCRIPTION => p_description
,X_CREATION_DATE => SYSDATE
,X_CREATED_BY => l_user_id
,X_LAST_UPDATE_DATE => SYSDATE
,X_LAST_UPDATED_BY => l_user_id
,X_LAST_UPDATE_LOGIN => 0
);
| Name: INSERT_FORM_FUNCTION_VB
|
| Description: This procedure creates a new fucntion in FND_FORM_FUNCTIONS
| metadata.It will also return the fucntion id of the newly created
| function.The transaction is still not commited. It will be done from
| the UI only.
| Input Parameters :- p_user_function_name --> cannot be null
| p_url --> can be null
| p_type --> by default 'WWW'
|
| Out Parameters:
| p_function_id --> needs to be generated
+============================================================================*/
PROCEDURE Create_Launch_Pad_Link
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_user_function_name IN VARCHAR2
, p_url IN VARCHAR2
, p_type IN VARCHAR2 :='WWW'
, x_function_id OUT NOCOPY FND_FORM_FUNCTIONS.function_id% TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_function_id FND_FORM_FUNCTIONS.function_id% TYPE;
SELECT FND_FORM_FUNCTIONS_S.NEXTVAL
INTO l_function_id
FROM DUAL;
FND_FORM_FUNCTIONS_PKG.INSERT_ROW
(
X_ROWID => row_id,
X_FUNCTION_ID => l_function_id,
X_WEB_HOST_NAME => SUBSTR(p_url,1,79),
X_WEB_AGENT_NAME => NULL,
X_WEB_HTML_CALL => p_url,
X_WEB_ENCRYPT_PARAMETERS => 'N',
X_WEB_SECURED => 'N',
X_WEB_ICON => NULL,
X_OBJECT_ID => NULL,
X_REGION_APPLICATION_ID => NULL,
X_REGION_CODE => NULL,
X_FUNCTION_NAME => l_function_name,
X_APPLICATION_ID => 271,
X_FORM_ID => NULL,
X_PARAMETERS => NULL,
X_TYPE => p_type,
X_USER_FUNCTION_NAME => p_user_function_name,
X_DESCRIPTION => p_url,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0
);
Description :- This procedure deletes the lauchpad link and its association with
the menus.First it deletes the lauchpad link and then removes the
association of the lauchpad links with all the menus using it.
Input :- Function_Id
Creator :-ashankar
/*******************************************************************************/
PROCEDURE Delete_Launch_Pad_Link
(
p_fucntion_id IN FND_FORM_FUNCTIONS.function_id%TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)IS
CURSOR c_menu_functions IS
SELECT MENU_ID,
ENTRY_SEQUENCE
FROM FND_MENU_ENTRIES_VL
WHERE FUNCTION_ID = p_fucntion_id;
SAVEPOINT deletelauchpadlink;
SELECT COUNT(0)
INTO l_count
FROM FND_FORM_FUNCTIONS_TL
WHERE FUNCTION_ID = p_fucntion_id;
FND_FORM_FUNCTIONS_PKG.DELETE_ROW(X_FUNCTION_ID => p_fucntion_id);
FND_MENU_ENTRIES_PKG.DELETE_ROW
(
X_MENU_ID => l_menu_id
,X_ENTRY_SEQUENCE => l_entrysequence
);
ROLLBACK TO deletelauchpadlink;
ROLLBACK TO deletelauchpadlink;
x_msg_data := x_msg_data||' -> BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
x_msg_data := SQLERRM||' at BSC_CUSTOM_VIEW_PUB.delete_Custom_View ';
END Delete_Launch_Pad_Link;
Name :- Update_Launch_Pad_Link
Description :- This procedure will update the Form Function in the FND_FORM_FUCNTIONS.
It should be called only for those launchpad links whose short_name
starts with 'BSC'
Input :- p_user_function_name
p_url
p_type
p_function_id
Creator :-ashankar
/****************************************************************************/
PROCEDURE Update_Launch_Pad_Link
(
p_commit IN VARCHAR2 := FND_API.G_FALSE
, p_user_function_name IN VARCHAR2
, p_url IN VARCHAR2
, p_type IN VARCHAR2 :='WWW'
, p_function_id IN FND_FORM_FUNCTIONS.function_id% TYPE
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_function_name FND_FORM_FUNCTIONS.function_name%TYPE;
BSC_LAUNCH_PAD_PVT.UPDATE_FORM_FUNCTION_VB
(
X_FUNCTION_ID => p_function_id
, X_WEB_HOST_NAME => SUBSTR(p_url,1,79)
, X_WEB_AGENT_NAME => NULL
, X_WEB_HTML_CALL => p_url
, X_WEB_ENCRYPT_PARAMETERS => 'N'
, X_WEB_SECURED => 'N'
, X_WEB_ICON => NULL
, X_OBJECT_ID => NULL
, X_REGION_APPLICATION_ID => NULL
, X_REGION_CODE => NULL
, X_FUNCTION_NAME => l_function_name
, X_APPLICATION_ID => 271
, X_FORM_ID => NULL
, X_PARAMETERS => NULL
, X_TYPE => p_type
, X_USER_FUNCTION_NAME => p_user_function_name
, X_DESCRIPTION => p_url
, X_USER_ID => get_User_Id
);
x_msg_data := x_msg_data||' -> BSC_LAUNCH_PAD_PUB.Update_Launch_Pad_Link ';
x_msg_data := SQLERRM||' at BSC_LAUNCH_PAD_PUB.Update_Launch_Pad_Link ';
END Update_Launch_Pad_Link;
SELECT COUNT(0)
INTO l_count
FROM FND_MENUS_TL
WHERE MENU_ID = p_Menu_Id;