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;