0 Replies Latest reply: Sep 6, 2013 8:55 AM by 961813 RSS

    Securing Attachment Categories

    961813

      Dear All,

       

      I have below scenario.

      Scenario: OAF attachments should be viewable by everyone but they are only allowed to modify or delete by the person who creates them.

       

      I went through Attachments chapter in OAF Dev Guide and tried with the way of Securing Attachment Categories. However, it ends up with an error (Please see the query in below exception stack).

       

      Please share your valuable input if you have implemented the similar functionality. Thanks in advance!

       

       

       

      Below is the step I followed.

       

      OAF:

      1. Created AttachmentTable item in PG file and assigned value to all the necessary properties.
      2. setup a CategoryMap with Security enabled.

       

      Setup:

      1. 1. In functional developer, edited the seeded Object FND_DOCUMENT_CATEGORIES which is Fnd Document Categories Security Object for OA Framework Attachments

       

      1. Created an object instance set with following setup

       

      Name: XXVFP_CAMP_CLAIM

      Column name: CREATED_BY    Type: NUMBER

      Predicate: &TABLE_ALIAS.CREATED_BY <> fnd_global.user_id

       

      1. 3.      Created a Grant with following setup

       

      Security Context -

      Grantee type: All Users

      Gratee: Everyone

       

      Data Security-

      Object: Fnd Document Categories

      Data Context: Instance Set – Name: XXVFP_CAMP_CLAIM

      Predicate &TABLE_ALIAS.CREATED_BY <> fnd_global.user_id

      Parameter: No

       

      Set -

      Name: Fnd Attachment View

      Code: FND_ATTACHMENT_VIEW


      ------------------------------------------------------------------------------------------------------------------------------------------------------------

      Error Page 

      Exception Details. 

      oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: SELECT * FROM (SELECT distinct ad.ATTACHED_DOCUMENT_ID,

             ad.DOCUMENT_ID AS DOCUMENT_ID,

             ad.LAST_UPDATE_DATE,

             ad.LAST_UPDATED_BY,

             u.USER_NAME LAST_UPDATED_BY_NAME,

             ad.ENTITY_NAME,

             ad.PK1_VALUE,

             ad.PK2_VALUE,

             ad.PK3_VALUE,

             ad.PK4_VALUE,

             ad.PK5_VALUE,

             decode (d.dm_node, null, (select short_name from fnd_dm_nodes where node_id = 0), 0, (select short_name from fnd_dm_nodes where node_id = 0), node.short_name) LOCATION,

             d.DOCUMENT_ID DOCUMENT_ID1,

             d.DATATYPE_ID,

             d.DATATYPE_NAME,

             d.DESCRIPTION,

             decode(d.FILE_NAME, null, (select message_text from fnd_new_messages where message_name = 'FND_UNDEFINED' and application_id = 0 and language_code = userenv('LANG')), d.FILE_NAME) FILE_NAME,

             d.MEDIA_ID,

             d.dm_type,

             d.dm_node,

             d.dm_folder_path,

             e.DATA_OBJECT_CODE,

             e.DOCUMENT_ENTITY_ID,

             'ALLOW_ATTACH_UPDATE' ALLOW_ATTACH_UPDATE,

             'ALLOW_ATTACH_DELETE' ALLOW_ATTACH_DELETE,

             ad.category_id category_id,

             cl.user_name attachment_category_name,

             ad.status,

             ad.creation_date,

             (select u1.user_name from fnd_user u1 where u1.user_id=ad.CREATED_BY) ATTACHED_BY_NAME,

             decode(d.datatype_id, 5, nvl(d.title,d.description)||'('||substr(d.URL, 1, least(length(d.URL),15))||'...)',

      decode(d.datatype_id, 6, nvl(d.title, d.file_name),

      decode(D.TITLE, null, (select message_text from fnd_new_messages where message_name = 'FND_UNDEFINED' and application_id = 0 and language_code = userenv('LANG')), D.TITLE))) FILE_NAME_SORT,

             d.usage_type,

             d.security_id,

             d.security_type,

             d.publish_flag,

             cl.category_id category_id_query,

             ad.seq_num,

             d.URL,

             d.TITLE

      FROM FND_DOCUMENTS_VL d,

           FND_ATTACHED_DOCUMENTS ad,

           FND_DOCUMENT_ENTITIES e,

           FND_USER u,

           FND_DOCUMENT_CATEGORIES_TL cl,

           FND_DM_NODES node

      WHERE ad.DOCUMENT_ID = d.DOCUMENT_ID

        and ad.ENTITY_NAME = e.DATA_OBJECT_CODE(+)

        and ad.LAST_UPDATED_BY = u.USER_ID(+)

        and cl.language = userenv('LANG')

        and cl.category_id = nvl(ad.category_id, d.category_id)

        and d.dm_node = node.node_id(+)) QRSLT  WHERE ((entity_name = :1 and pk1_value = :2 and category_id_query in () )  and datatype_id in (6,2,1,5) AND (SECURITY_TYPE=4 OR PUBLISH_FLAG='Y')) ORDER BY seq_num