-- Script to load or convert Notes/Attachments associated with items.

DECLARE
        l_doc_category_id NUMBER;
        l_document_id NUMBER;
        l_attached_document_id NUMBER;
        ll_media_id NUMBER;
        l_fnd_user_id NUMBER;
        l_short_datatype_id NUMBER;
BEGIN
 -- Select User_id
        SELECT  user_id
        INTO    l_fnd_user_id
        FROM    apps.fnd_user
        WHERE   user_name ='ACHADDA';

        -- Get Data type id for Short Text types of attachments
        SELECT  datatype_id
        INTO    l_short_datatype_id
        FROM    apps.fnd_document_datatypes
        WHERE   name ='SHORT_TEXT';

        -- Select Category id for "Vendor/To Supplier" Attachments
        SELECT  category_id
        INTO    l_doc_category_id
        FROM    apps.fnd_document_categories
        WHERE   name = 'Vendor';

 -- Select nexvalues of document id, attached document id and
 -- l_media_id
        SELECT  apps.fnd_documents_s.NEXTVAL,
  apps.fnd_attached_documents_s.NEXTVAL,
  apps.fnd_documents_short_text_s.NEXTVAL
 INTO  l_document_id,
  l_attached_document_id,
  l_media_id
 FROM  DUAL;

 INSERT INTO apps.fnd_documents
                (document_id,
                creation_date,
                created_by,
                last_update_date,
                last_updated_by,
                datatype_id,
                category_id,
                security_type,
                security_id,
                publish_flag,
                usage_type
                )
        VALUES
                (l_document_id,
                SYSDATE,
                l_fnd_user_id,
                SYSDATE,
                l_fnd_user_id,
                l_short_datatype_id,   -- Datatype for 'SHORT_TEXT'
                l_doc_category_id,     -- Category_id
                1,                      -- 'Organization' Level Security
                352,                    -- Organization id for Inventory Item Master Org
                'Y',                    -- Publish_flag
                'O'                     -- Usage_type of 'One Time'
                );

 INSERT INTO apps.fnd_documents_tl
                (document_id,
                creation_date,
                created_by,
                last_update_date,
                last_updated_by,
                language,
                description,
                media_id,
                translated
                )
                VALUES
                (l_document_id,
                SYSDATE,
                l_fnd_user_id,
                SYSDATE,
                l_fnd_user_id,
                'AMERICAN',             -- language
                'EXTENDED DESCRIPTION', -- description
                l_media_id,            -- media_id
                'Y'                     -- translated
                );

        INSERT INTO apps.fnd_attached_documents
                (attached_document_id,
                document_id,
                creation_date,
                created_by,
                last_update_date,
                last_updated_by,
                seq_num,
                entity_name,
                pk1_value,
                pk2_value,
                automatically_added_flag
                )
                VALUES
                (l_attached_document_id,
                l_document_id,
                SYSDATE,
                lcl_fnd_user_id,
                SYSDATE,
                lcl_fnd_user_id,
                20,                     -- Sequence Number of attachment.
                'MTS_SYSTEM_ITEMS',     -- Entity_name Table Name assoicated with attachment
                352,                    -- Organization id for Inventory Item Master Org
                567,               -- Inventory Item Id
                'N'                     -- Automatically_added_flag
                );
 

        INSERT INTO apps.fnd_documents_short_text
                (media_id,
                short_text
                )
                VALUES
                (lcl_media_id,
                'Write your Short Text Here' -- Notes/Attachments text
                );

COMMIT;
END;
/

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips