CREATE OR REPLACE PROCEDURE insert_attached_text_document(
      p_user_name           IN   VARCHAR2,
      p_document_datatype   IN   VARCHAR2 DEFAULT 'LONG_TEXT',
      p_document_category   IN   VARCHAR2,
      p_security_level      IN   NUMBER DEFAULT 1,
      p_organization_id     IN   NUMBER,
      p_publish             IN   VARCHAR2 DEFAULT 'Y',
      p_usage_type          IN   VARCHAR2 DEFAULT 'O',
      p_language            IN   VARCHAR2 DEFAULT 'US',
      p_entity_name         IN   VARCHAR2,
      p_pk1_value           IN   VARCHAR2,
      p_pk2_value           IN   VARCHAR2 DEFAULT '',
      p_pk3_value           IN   VARCHAR2 DEFAULT '',
      p_pk4_value           IN   VARCHAR2 DEFAULT '',
      p_pk5_value           IN   VARCHAR2 DEFAULT '',
      p_sequence_num        IN   NUMBER,
      p_document_desc       IN   VARCHAR2,
      p_text_doc            IN   VARCHAR2 DEFAULT '.') IS
      --=============================================================================
      -- NAME: insert_attached_text_document
      -- PURPOSE: To add document attachments to internal entities
      -- NOTES: Only inserts Long and Short Text types.
      --=============================================================================
      -- HISTORY
      -- Who         When       Comments
      -- ---------   ---------- --------------------------------------------
      -- Chet West   07/21/2003 Initial Version
      --=============================================================================
   /*
   insert_attached_text_document
   (p_user_name => 'CWEST'
   ,p_document_datatype => 'LONG_TEXT'
   ,p_document_category => 'Item Internal'
   ,p_security_level => 1 -- 'Organization' Level Security
   ,p_organization_id => 104
   ,p_publish => 'Y'
   ,p_usage_type => 'O' -- One Time
   ,p_language => 'US'
   ,p_entity_name => 'MTL_SYSTEM_ITEMS'
   ,p_pk1_value => organization_id
   ,p_pk2_value => item_id
   ,p_pk3_value => ''
   ,p_pk4_value => ''
   ,p_pk5_value => ''
   ,p_sequence_num => 10 -- 10 for MP2#1 11 for MP2#2
   ,p_document_desc => 'EXTENDED DESCRIPTION (#1)' -- 'EXTENDED DESCRIPTION (#1)' or 'EXTENDED DESCRIPTION (#2)'
   ,p_text_doc => 'This is long text for an item.'
   );
   */
   l_doc_category_id        NUMBER;
   l_document_id            NUMBER;
   l_attached_document_id   NUMBER;
   l_media_id               NUMBER;
   l_fnd_user_id            NUMBER;
   l_short_datatype_id      NUMBER;
BEGIN
   -- Get User_id
   SELECT user_id
     INTO l_fnd_user_id
     FROM apps.fnd_user
    WHERE user_name = p_user_name;

   -- Get Data type id for this type of attachment
   SELECT datatype_id
     INTO l_short_datatype_id
     FROM apps.fnd_document_datatypes
    WHERE NAME = p_document_datatype;

   -- Get Category id for this attachment
   SELECT category_id
     INTO l_doc_category_id
     FROM apps.fnd_document_categories
    WHERE NAME = p_document_category;

   IF    p_pk1_value IS NULL
      OR p_document_datatype NOT IN( 'LONG_TEXT', 'SHORT_TEXT' ) THEN
      RAISE PROGRAM_ERROR;
   END IF;

   -- Insert the actual media text
   IF p_document_datatype = 'LONG_TEXT' THEN
      INSERT INTO apps.fnd_documents_long_text
                  ( media_id, 
                    long_text
                  )
           VALUES ( apps.fnd_documents_long_text_s.NEXTVAL,
                    p_text_doc
                  )
        RETURNING media_id
             INTO l_media_id;
   ELSIF p_document_datatype = 'SHORT_TEXT' THEN
      INSERT INTO apps.fnd_documents_short_text
                  ( media_id,
                    short_text
                  )
           VALUES ( apps.fnd_documents_short_text_s.NEXTVAL,
                    p_text_doc
                  )
        RETURNING media_id
             INTO l_media_id;
   END IF;

   IF l_media_id IS NOT NULL THEN
      -- Create the Document entry
      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 ( apps.fnd_documents_s.NEXTVAL,
                    SYSDATE,
                    l_fnd_user_id,
                    SYSDATE,
                    l_fnd_user_id,
                    l_short_datatype_id,
                    l_doc_category_id,
                    p_security_level,
                    p_organization_id,
                    p_publish,
                    p_usage_type
                  )
        RETURNING document_id
             INTO l_document_id;

      -- Create the Document Translation/Language entry
      INSERT INTO apps.fnd_documents_tl
                  ( document_id,
                    creation_date,
                    created_by,
                    last_update_date,
                    last_updated_by,
                    LANGUAGE,
                    description,
                    media_id,
                    source_lang
                  )
           VALUES ( l_document_id,
                    SYSDATE,
                    l_fnd_user_id,
                    SYSDATE,
                    l_fnd_user_id,
                    p_language,
                    p_document_desc,
                    l_media_id,
                    p_language
                  );

      IF l_document_id IS NOT NULL THEN
         -- Create the attached document entry
         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,
                       pk3_value,
                       pk4_value,
                       pk5_value,
                       automatically_added_flag
                     )
              VALUES ( apps.fnd_attached_documents_s.NEXTVAL,
                       l_document_id,
                       SYSDATE,
                       l_fnd_user_id,
                       SYSDATE,
                       l_fnd_user_id,
                       p_sequence_num,          -- Sequence Number of attachment
                       p_entity_name,
                       p_pk1_value,
                       p_pk2_value,
                       p_pk3_value,
                       p_pk4_value,
                       p_pk5_value,
                       'N'                           -- Automatically_added_flag
                     )
           RETURNING attached_document_id
                INTO l_attached_document_id;

         IF l_attached_document_id IS NULL THEN
            RAISE PROGRAM_ERROR;
         END IF;
      ELSE
         RAISE PROGRAM_ERROR;
      END IF;
   ELSE
      RAISE PROGRAM_ERROR;
   END IF;
END;
/