-- 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;
/

Return to : Oracle Database, SQL, Application, Programming Tips