19/24
Sample Custom Security Handler
Sample Custom Security Handler
create or replace PACKAGE CUSTOM_AP_ECC_DS_PKG_PUB as
procedure GetFilterAttributeValues (
x_dataset_key IN VARCHAR2 ,
x_user_id in number ,
x_org_id in number ,
x_resp_id in number ,
x_resp_app_id in number ,
x_sec_group_id in number ,
x_params in ecc_sec_field_values DEFAULT NULL,
x_return_status out nocopy varchar2 ,
x_return_message out nocopy varchar2 ,
x_sec_filter out nocopy CLOB );
PROCEDURE LogStatement(p_pkgname IN VARCHAR2,
p_message IN VARCHAR2);
END CUSTOM_AP_ECC_DS_PKG_PUB
Package Body:
create or replace PACKAGE BODY CUSTOM_AP_ECC_DS_PKG_PUB as
procedure GetFilterAttributeValues (
x_dataset_key IN VARCHAR2 ,
x_user_id in number ,
x_org_id in number ,
x_resp_id in number ,
x_resp_app_id in number ,
x_sec_group_id in number ,
x_params in ecc_sec_field_values DEFAULT NULL,
x_return_status out nocopy varchar2 ,
x_return_message out nocopy varchar2 ,
x_sec_filter out nocopy CLOB ) IS
x_return_string CLOB;
l_sec_list_values ecc_sec_field_values;
l_ou_tab mo_global.OrgIdTab;
l_tab icx_tbl_varchar240;
l_count NUMBER := 0;
l_application_id NUMBER := NULL;
x_sec_list_values ecc_sec_field_values;
l_sec_filter CLOB;
debug_info VARCHAR2(100);
l_profile_id varchar2(1000);
BEGIN
/* This method would be called by the ECC Security manager
* it would pass whatever parameter values are available in the context
* product teams need to write individual methods to handle the specific
* data security scenarios for every dataset
* dataset_key would be the first parameter passed
* The next 5 parameters provide the EBS context to construct the security filters
* The x_params is an array of VARCHAR2(100)
* x_return_status should return
* 'UNEXPECTED ERROR' in case an exception is encountered ,
* 'NO_SECURITY' if no security should be applied to the dataset
* 'EXPECTED ERROR' in case of an expected error ,
* such as an incomplete setup or any condition where security filter cannot be constructed
* x_return_message should return the error message in case the x_return_status is 'UNEXPECTED ERROR' or 'EXPECTED ERROR'
* the x_sec_filter would have the final filter as a json string which be a return value of the call to the methods in ecc_datasecurity_pkg
*/
LogStatement('CUSTOM_AP_ECC_DS_PKG_PUB.GetFilterAttributeValues', 'In the Payables datasecurity procedure');
LogStatement('CUSTOM_AP_ECC_DS_PKG_PUB.GetFilterAttributeValues', 'dataset = ' || x_dataset_key ||' respid = ' || x_resp_id || 'userid= ' || x_user_id||' respid app ud= ' || x_resp_app_id );
l_sec_filter := NULL;
l_sec_list_values := NEW ecc_sec_field_values(null);
Fnd_Global.Apps_Initialize(
x_user_id,
x_resp_id,
x_resp_app_id,
x_sec_group_id
);
MO_GLOBAL.init('SQLAP');
mo_global.set_policy_context('M', -1);
// customer need to prepare below query as per their requirement to get filter values.
select user_id bulk_collect into l_sec_list_values from per_organization_list;
l_sec_filter := FND_ECC_DATASECURITY_PKG_PUB.CREATERECORDFILTEREXPROR('USER_ID',l_sec_list_values,x_return_status , x_return_message);
x_sec_filter := x_sec_filter||l_sec_filter;
End If;
LogStatement('CUSTOM_AP_ECC_DS_PKG_PUB.GetFilterAttributeValues','List of values:' || x_sec_filter);
x_return_status := FND_ECC_DATASECURITY_PKG_PUB.ECC_SEC_SUCCESS;
x_return_message := 'Successful completion';
EXCEPTION
WHEN OTHERS THEN
LogStatement('GetFilterAttributeValues','in the Payables datasec procedure - exception' || SQLERRM);
x_sec_filter := null;
x_return_status := FND_ECC_DATASECURITY_PKG_PUB.ECC_SEC_UNEXP_ERR;
x_return_message := substr(sqlerrm,1,4000);
RAISE_APPLICATION_ERROR(-20144,'Exception in CUSTOM_AP_ECC_DS_PKG_PUB ' || Substr(sqlerrm,1,1000));
END GetFilterAttributeValues;
PROCEDURE LogStatement(p_pkgname IN VARCHAR2,
p_message IN VARCHAR2) IS
BEGIN
if (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, p_pkgname, p_message);
end if;
END LogStatement;
END CUSTOM_AP_ECC_DS_PKG_PUB;