FND ATTACHMENT FUNCTIONALITY & SCRIPTS

ATTACHMENT FUNCTIONALITY & SCRIPTS

why do i need a attachment functionality?The attachments feature enables users to link unstructured data, such as images, wordprocessing documents, spreadsheets, or text to their application data. For example,users can link images to items or video to operations as operation instructions.
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things


Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.

Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level


Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.

Press the Entities Button

Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.

If it a common document like across users then primary key should not be given.

Press the categories button to select the caegories

Press the Assignments Button in document categories and attach the Function.

Now the attache button is enabled..press the button and upload the document..




uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary


***********************************************
1. Script to attach a document using API
http://shareoracleapps.blogspot.com/2010/03/how-to-attach-documents-from-backend.html


Attachment using backend scripts:


Declare


v_category_id                    NUMBER;
v_attached_doc_id          NUMBER;
v_invoice_id                      NUMBER;
v_invoice_image_url     VARCHAR2(500) := 'http://shareoracleapps.blogspot.com';
v_function_name            VARCHAR2(50)   := 'APXINWKB';
v_category_name            VARCHAR2(100) := 'FromSupplier';
v_description                    VARCHAR2(300) := 'Test script for attaching scanned image url to AP invoice';
v_entity_name                 VARCHAR2(100) := 'AP_INVOICES'
v_file_name                      VARCHAR2(100) := NULL;
v_user_id                            NUMBER               := 1234;
TYPE result_set_type IS REF CURSOR;
v_result_set_curr           result_set_type;


--Here for example we are using "FromSupplier" as a category
--and AP_INVOICES_ALL.invoice_id as primary key value


CURSOR  cur_cat_id
IS
    SELECT     fdc.category_id
    FROM       fnd_document_categories fdc
    WHERE      fdc.name  =  v_category_name;


FUNCTION set_context( i_user_name    IN  VARCHAR2
                     ,i_resp_name    IN  VARCHAR2
                     ,i_org_id       IN  NUMBER)
RETURN VARCHAR2
IS
--  Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;

BEGIN
 -- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
    THEN
        DBMS_OUTPUT.PUT_LINE('Error while setting the context');     
    END IF;
DBMS_OUTPUT.PUT_LINE('2');
--- context done ------------
     OPEN cur_cat_id;
     FETCH cur_cat_id INTO v_category_id;
     CLOSE cur_cat_id;
         -- Invoke the fnd_webattach api for attaching the URL to the invoice


        fnd_webattch.add_attachment ( seq_num                   => 100
                                     ,category_id                            => v_category_id
                                     ,document_description     => v_description
                                     ,datatype_id                           => 5
                                     ,text                                            => NULL
                                     ,file_name                               => v_file_name
                                     ,url                                              => v_invoice_image_url
                                     ,function_name                    => v_function_name
                                     ,entity_name                         => v_entity_name
                                     ,pk1_value                              => v_invoice_id
                                     ,pk2_value                              => NULL
                                     ,pk3_value                              => NULL
                                     ,pk4_value                              => NULL
                                     ,pk5_value                              => NULL
                                     ,media_id                                => x_file_id
                                     ,user_id                                    => v_user_id
                                     ,usage_type                            => 'O'
                                     );
                                                                                                                                               
        SELECT    count(fad.attached_document_id)
        INTO      v_attached_doc_id
        FROM      fnd_attached_documents fad
        WHERE     fad.pk1_value = v_invoice_id;


        IF  v_attached_doc_id > 0
          DBMS_OUTPUT.PUT_LINE('Attached sucessfully');
        THEN
          DBMS_OUTPUT.PUT_LINE('Failed to Link the Attacment.');
          
        END IF; --IF  v_attached_doc_id > 0


EXCEPTION
WHEN OTHERS THEN
         DBMS_OUTPUT.PUT_LINE('Error occured : '||SQLERRM);
END attach_invoice;


****************************************************
2. Script to Delete a Attachment using API

declare
l_entity_name varchar2(20):= 'PO_LINES'; 
l_pk1_value varchar2 (20) := '200487';   
l_delete_document_flag varchar2 (1):= 'Y';  
begin
fnd_global.apps_initialize ( user_id      => &v_user_id                                                        ,resp_id      => &v_resp_id                                                       ,resp_appl_id => &v_resp_appl_id);
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name                             => l_entity_name
, X_pk1_value                                   => l_pk1_value
, X_delete_document_flag         => l_delete_document_flag);
end;
/
commit;
**********************************************************
3. Queries


As we know, we can attach documents in different formats like WEBPAGE, FILE, LONG_TEXT and SHORT_TEXT.Hence, i had given queries to find the attached documents FOR EACH format separately.


-- WEBPAGE ATTACHMENT


 SELECT DISTINCT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  D.STORAGE_TYPE              ,
  D.FILE_NAME                 ,
  D.IMAGE_TYPE                ,
  D.USAGE_TYPE                ,
  AD.AUTOMATICALLY_ADDED_FLAG ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  D.DATATYPE_ID               ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  D.START_DATE_ACTIVE         ,
  D.END_DATE_ACTIVE           ,
  D.SECURITY_TYPE             ,
  D.SECURITY_ID               ,
  D.PUBLISH_FLAG              ,
  AD.CREATED_BY               ,
  DET.USER_ENTITY_PROMPT      ,
  AD.ENTITY_NAME              ,
  AD.COLUMN1                  ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.CATEGORY_ID               ,
  D.URL                       ,
  DT.TITLE
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD
WHERE D.DOCUMENT_ID        = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID         = D.DOCUMENT_ID
AND DCT.CATEGORY_ID        = D.CATEGORY_ID
AND D.DATATYPE_ID          = DAT.DATATYPE_ID
AND AD.ENTITY_NAME         = DET.DATA_OBJECT_CODE
AND DAT.name               = 'WEB_PAGE';



-- LONG_TEXT ATTACHMENT


 SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DLT.LONG_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_LONG_TEXT DLT   
WHERE D.DOCUMENT_ID       = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID        = D.DOCUMENT_ID
AND DCT.CATEGORY_ID       = D.CATEGORY_ID
AND D.DATATYPE_ID         = DAT.DATATYPE_ID
AND AD.ENTITY_NAME        = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID          = D.MEDIA_ID
AND DAT.NAME              = 'LONG_TEXT';


-- SHORT_TEXT ATTACHMENT


SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DST.SHORT_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_DOCUMENTS_SHORT_TEXT DST   
WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID           = D.DOCUMENT_ID
AND DCT.CATEGORY_ID          = D.CATEGORY_ID
AND D.DATATYPE_ID            = DAT.DATATYPE_ID
AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE
AND DsT.MEDIA_ID             = D.MEDIA_ID
AND DAT.NAME                 = 'SHORT_TEXT';


-- FILE ATTACHMENT


SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  DBMS_LOB.SUBSTR(L.file_data,1,10) file_data
FROM FND_DOCUMENT_DATATYPES DAT,
  FND_DOCUMENT_ENTITIES_TL DET    ,
  FND_DOCUMENTS_TL DT             ,
  FND_DOCUMENTS D                 ,
  FND_DOCUMENT_CATEGORIES_TL DCT  ,
  FND_ATTACHED_DOCUMENTS AD       ,
  FND_LOBS  L                        
WHERE D.DOCUMENT_ID          = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID           = D.DOCUMENT_ID
AND DCT.CATEGORY_ID          = D.CATEGORY_ID
AND D.DATATYPE_ID            = DAT.DATATYPE_ID
AND AD.ENTITY_NAME           = DET.DATA_OBJECT_CODE
AND L.FILE_ID                = D.MEDIA_ID
AND DAT.name                 = 'FILE';


-- This query will help you to find the functions using attachments


SELECT
  AD.SEQ_NUM                  ,
  DCT.USER_NAME               ,
  DAT.USER_NAME               ,
  AD.ATTACHED_DOCUMENT_ID     ,
  DET.USER_ENTITY_NAME        ,
  DAT.NAME                    ,
  D.DOCUMENT_ID               ,
  AD.ENTITY_NAME              ,
  AD.PK1_VALUE                ,
  D.MEDIA_ID                  ,
  D.URL                       ,
  DT.TITLE                    ,
  AF.FUNCTION_NAME            ,
  AF.FUNCTION_TYPE            
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 1 = 1
AND D.DOCUMENT_ID              = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID             = D.DOCUMENT_ID
AND DCT.CATEGORY_ID            = D.CATEGORY_ID
AND DCU.CATEGORY_ID            = D.CATEGORY_ID
AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID
AND D.DATATYPE_ID              = DAT.DATATYPE_ID
AND AD.ENTITY_NAME             = DET.DATA_OBJECT_CODE
AND DCU.ENABLED_FLAG           = 'Y';

References:

http://vasikarjayas.blogspot.com/2011/05/attachment-functionality-scripts.html

http://www.shareoracleapps.com/2010/06/fnd-attachments-in-oracle-apps-r12.html