2 Replies Latest reply on Jun 2, 2016 5:55 PM by PamNL

    relation between base table and fnd_attachments

    3238696

      HI,

      If we added attachment from front end, it is inserting a row in the fnd_attached_documents. Can any one please let me know where there is a relation between base table and fnd_attachments

       

      Regards,

      Praveen

        • 1. Re: relation between base table and fnd_attachments
          PamNL

          Try this query .. The attachment is stored in FND_ATTACHED_DOCUMENTS and related to your entity on the entity name and the PK1_Value. Might be you have to tweak the query a little, since I had to remove my entity from it :-).

           

          SELECT D.PK1_Value Your_Unique_Id

              ,      D.Category_Id

              ,      C.Name

              ,      D.Document_Id

              ,      DB.Datatype_Id

              ,      DB.Storage_Type

              ,      R.File_Id

              ,      R.File_Name

              ,      R.File_Data

              ,      R.File_Content_Type

              FROM   FND_ATTACHED_DOCUMENTS D

              ,      FND_DOCUMENTS          DB

              ,      FND_DOCUMENTS_TL       DBT

              ,      FND_LOBS R

              ,      FND_DOCUMENT_CATEGORIES C

              ,      FND_DOCUMENT_CATEGORIES_TL CT

              WHERE  D.Entity_Name = '{Your entity}'

              AND    D.Document_Id = DB.Document_Id

              AND    D.Category_Id = C.Category_Id

              AND    C.Category_Id = CT.Category_Id

              AND    CT.User_Name IN ( '{Attachment Type}')

              AND    D.Document_Id = DBT.Document_Id

              AND    DBT.Language = Userenv ('LANG')

              AND CT.Language = Userenv ('LANG')

              -- IN R12 it should link on DB.Media_Id ... in R11 on DBT.Media_Id

              AND    NVL (DB.Media_Id,DBT.Media_Id) = R.File_Id

              AND    D.PK1_Value = {Your entity unique id}

          • 2. Re: relation between base table and fnd_attachments
            PamNL

            You can also check out view FND_ATTACHED_DOCS_FORM_VL on Entity_Name and PK1_Value

             

            FROM FND_DOCUMENT_DATATYPES DAT

            , FND_DOCUMENT_ENTITIES_TL DET

            , FND_DOCUMENTS_TL DT

            , FND_DOCUMENTS D

            , FND_DOCUMENT_CATEGORIES_TL DCT

            , FND_DOC_CATEGORY_USAGES DCU

            , FND_ATTACHMENT_FUNCTIONS AF

            , FND_ATTACHED_DOCUMENTS AD

            WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID

            AND DT.DOCUMENT_ID = D.DOCUMENT_ID

            AND DT.LANGUAGE = USERENV('LANG')

            AND DCT.CATEGORY_ID = D.CATEGORY_ID

            AND DCT.LANGUAGE = USERENV('LANG')

            AND DCU.CATEGORY_ID = D.CATEGORY_ID

            AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID

            AND D.DATATYPE_ID = DAT.DATATYPE_ID

            AND DAT.LANGUAGE = USERENV('LANG')

            AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE

            AND DET.LANGUAGE =USERENV('LANG')

            AND DCU.ENABLED_FLAG = 'Y';