eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FND_DOCUMENTS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 5


PROCEDURE Insert_Row(X_Rowid                        IN OUT NOCOPY VARCHAR2,
                     X_document_id                  IN OUT NOCOPY NUMBER,
                     X_creation_date                DATE,
                     X_created_by                   NUMBER,
                     X_last_update_date             DATE,
                     X_last_updated_by              NUMBER,
                     X_last_update_login            NUMBER DEFAULT NULL,
                     X_datatype_id                  NUMBER,
                     X_category_id                  NUMBER,
                     X_security_type                NUMBER,
                     X_security_id                  NUMBER DEFAULT NULL,
                     X_publish_flag                 VARCHAR2,
                     X_image_type                   VARCHAR2 DEFAULT NULL,
                     X_storage_type                 NUMBER DEFAULT NULL,
                     X_usage_type                   VARCHAR2,
                     X_start_date_active            DATE DEFAULT NULL,
                     X_end_date_active              DATE DEFAULT NULL,
                     X_request_id                   NUMBER DEFAULT NULL,
                     X_program_application_id       NUMBER DEFAULT NULL,
                     X_program_id                   NUMBER DEFAULT NULL,
                     X_program_update_date          DATE DEFAULT NULL,
                     X_language                     VARCHAR2,
                     X_description                  VARCHAR2 DEFAULT NULL,
                     X_file_name                    VARCHAR2 DEFAULT NULL,
                     X_media_id                     IN OUT NOCOPY NUMBER,
                  X_Attribute_Category              VARCHAR2 DEFAULT NULL,
                  X_Attribute1                      VARCHAR2 DEFAULT NULL,
                  X_Attribute2                      VARCHAR2 DEFAULT NULL,
                  X_Attribute3                      VARCHAR2 DEFAULT NULL,
                  X_Attribute4                      VARCHAR2 DEFAULT NULL,
                  X_Attribute5                      VARCHAR2 DEFAULT NULL,
                  X_Attribute6                      VARCHAR2 DEFAULT NULL,
                  X_Attribute7                      VARCHAR2 DEFAULT NULL,
                  X_Attribute8                      VARCHAR2 DEFAULT NULL,
                  X_Attribute9                      VARCHAR2 DEFAULT NULL,
                  X_Attribute10                     VARCHAR2 DEFAULT NULL,
                  X_Attribute11                     VARCHAR2 DEFAULT NULL,
                  X_Attribute12                     VARCHAR2 DEFAULT NULL,
                  X_Attribute13                     VARCHAR2 DEFAULT NULL,
                  X_Attribute14                     VARCHAR2 DEFAULT NULL,
                  X_Attribute15                     VARCHAR2 DEFAULT NULL,
		  X_create_doc                      VARCHAR2 DEFAULT 'N',
                  X_url                          VARCHAR2 DEFAULT NULL,
                  X_title			 VARCHAR2 DEFAULT NULL,
                  X_dm_node                      NUMBER DEFAULT NULL,
                  X_dm_folder_path               VARCHAR2 DEFAULT NULL,
                  X_dm_type                      VARCHAR2 DEFAULT NULL,
                  X_dm_document_id               NUMBER DEFAULT NULL,
                  X_dm_version_number            VARCHAR2 DEFAULT NULL,
                  X_orig_doc_id                  IN NUMBER DEFAULT NULL
 ) IS
   CURSOR C IS SELECT rowid
                 FROM fnd_documents
                WHERE document_id = X_document_id;

Line: 70


  SELECT fnd_documents_s.nextval
    INTO X_document_id
    FROM dual;

Line: 79


	SELECT fnd_documents_short_text_s.nextval
	  INTO X_media_id
	  FROM dual;

Line: 83


     insert into fnd_documents_short_text
        (media_id, short_text, app_source_version)
        select X_media_id, short_text, app_source_version
        from fnd_documents_short_text
        where media_id = l_media_id;

Line: 91


     SELECT fnd_documents_long_text_s.nextval
       INTO X_media_id
       FROM dual;

Line: 95


      select long_text,app_source_version into l_longtxt,l_app_s_v from fnd_documents_long_text
      where media_id = l_media_id;

Line: 97


     insert into fnd_documents_long_text
     (media_id,long_text,app_source_version)
     values (X_media_id,l_longtxt,l_app_s_v);

Line: 104


      SELECT fnd_documents_long_raw_s.nextval
        INTO X_media_id
        FROM dual;

Line: 108


    SELECT fnd_lobs_s.nextval
      INTO X_media_id
      FROM dual;

Line: 114


  INSERT INTO fnd_documents(
	 document_id,
	 creation_date,
	 created_by,
	 last_update_date,
	 last_updated_by,
	 last_update_login,
	 datatype_id,
	 category_id,
	 security_type,
	 security_id,
	 publish_flag,
	 image_type,
	 storage_type,
	 usage_type,
	 start_date_active,
	 end_date_active,
	 request_id,
	 program_application_id,
	 program_id,
	 program_update_date,
         url,
         media_id,
         file_name,
         dm_node,
         dm_folder_path,
         dm_type,
         dm_document_id,
         dm_version_number,
         orig_doc_id) VALUES (
	 X_document_id,
	 X_creation_date,
	 X_created_by,
	 X_last_update_date,
	 X_last_updated_by,
	 X_last_update_login,
	 X_datatype_id,
	 X_category_id,
	 X_security_type,
	 X_security_id,
	 X_publish_flag,
	 X_image_type,
	 X_storage_type,
	 X_usage_type,
	 X_start_date_active,
	 X_end_date_active,
	 X_request_id,
	 X_program_application_id,
	 X_program_id,
	 X_program_update_date,
	 X_url,
         X_media_id,
	 X_file_name,
         X_dm_node,
         X_dm_folder_path,
         X_dm_type,
         X_dm_document_id,
         X_dm_version_number,
         X_orig_doc_id);

Line: 177


 insert_tl_row(X_document_id => X_document_id,
	 X_creation_date     => X_creation_date,
	 X_created_by        => X_created_by,
	 X_last_update_date  => X_last_update_date,
	 X_last_updated_by   => X_last_updated_by,
	 X_last_update_login => X_last_update_login,
	 X_language          => X_language,
	 X_description       => X_description,
	 X_request_id        => X_request_id,
	 X_program_application_id => X_program_application_id,
	 X_program_id        => X_program_id,
	 X_program_update_date => X_program_update_date,
	 X_attribute_category  => X_attribute_category,
	 X_attribute1          => X_attribute1,
	 X_attribute2          => X_attribute2,
	 X_attribute3          => X_attribute3,
	 X_attribute4          => X_attribute4,
	 X_attribute5          => X_attribute5,
	 X_attribute6          => X_attribute6,
	 X_attribute7          => X_attribute7,
	 X_attribute8          => X_attribute8,
	 X_attribute9          => X_attribute9,
	 X_attribute10         => X_attribute10,
	 X_attribute11         => X_attribute11,
	 X_attribute12         => X_attribute12,
	 X_attribute13         => X_attribute13,
	 X_attribute14         => X_attribute14,
	 X_attribute15         => X_attribute15,
         X_title	       => X_title);

Line: 216


END Insert_Row;

Line: 219


PROCEDURE insert_tl_row(X_document_id               NUMBER,
                     X_creation_date                DATE,
                     X_created_by                   NUMBER,
                     X_last_update_date             DATE,
                     X_last_updated_by              NUMBER,
                     X_last_update_login            NUMBER DEFAULT NULL,
                     X_language                     VARCHAR2,
                     X_description                  VARCHAR2 DEFAULT NULL,
                     X_request_id                   NUMBER DEFAULT NULL,
                     X_program_application_id       NUMBER DEFAULT NULL,
                     X_program_id                   NUMBER DEFAULT NULL,
                     X_program_update_date          DATE DEFAULT NULL,
                  X_Attribute_Category                  VARCHAR2 DEFAULT NULL,
                  X_Attribute1                          VARCHAR2 DEFAULT NULL,
                  X_Attribute2                          VARCHAR2 DEFAULT NULL,
                  X_Attribute3                          VARCHAR2 DEFAULT NULL,
                  X_Attribute4                          VARCHAR2 DEFAULT NULL,
                  X_Attribute5                          VARCHAR2 DEFAULT NULL,
                  X_Attribute6                          VARCHAR2 DEFAULT NULL,
                  X_Attribute7                          VARCHAR2 DEFAULT NULL,
                  X_Attribute8                          VARCHAR2 DEFAULT NULL,
                  X_Attribute9                          VARCHAR2 DEFAULT NULL,
                  X_Attribute10                         VARCHAR2 DEFAULT NULL,
                  X_Attribute11                         VARCHAR2 DEFAULT NULL,
                  X_Attribute12                         VARCHAR2 DEFAULT NULL,
                  X_Attribute13                         VARCHAR2 DEFAULT NULL,
                  X_Attribute14                         VARCHAR2 DEFAULT NULL,
                  X_Attribute15                         VARCHAR2 DEFAULT NULL,
                  X_title				VARCHAR2 DEFAULT NULL)
 IS
BEGIN

  --  insert into "language" specific table
  INSERT INTO fnd_Documents_tl (
	 document_id,
	 creation_date,
	 created_by,
	 last_update_date,
	 last_updated_by,
	 last_update_login,
	 language,
	 description,
	 request_id,
	 program_application_id,
	 program_id,
	 program_update_date,
	 doc_attribute_category,
	 doc_attribute1,
	 doc_attribute2,
	 doc_attribute3,
	 doc_attribute4,
	 doc_attribute5,
	 doc_attribute6,
	 doc_attribute7,
	 doc_attribute8,
	 doc_attribute9,
	 doc_attribute10,
	 doc_attribute11,
	 doc_attribute12,
	 doc_attribute13,
	 doc_attribute14,
	 doc_attribute15,
         source_lang,
         title) SELECT
	 X_document_id,
	 X_creation_date,
	 X_created_by,
	 X_last_update_date,
	 X_last_updated_by,
	 X_last_update_login,
	 L.language_code,
	 X_description,
	 X_request_id,
	 X_program_application_id,
	 X_program_id,
	 X_program_update_date,
	 X_attribute_category,
	 X_attribute1,
	 X_attribute2,
	 X_attribute3,
	 X_attribute4,
	 X_attribute5,
	 X_attribute6,
	 X_attribute7,
	 X_attribute8,
	 X_attribute9,
	 X_attribute10,
	 X_attribute11,
	 X_attribute12,
	 X_attribute13,
	 X_attribute14,
	 X_attribute15,
	 userenv('LANG'),
         X_title
    FROM fnd_languages L
   WHERE l.installed_flag IN ('I','B')
     AND NOT EXISTS (SELECT null
		       FROM fnd_documents_tl TL
		      WHERE document_id = x_document_id
		        AND TL.language = l.language_code);

Line: 320


END Insert_tl_Row;

Line: 358


      SELECT *
      FROM   fnd_documents
      WHERE  document_id = X_document_id
      FOR UPDATE of document_id NOWAIT;

Line: 458


      SELECT *
      FROM   fnd_documents_tl
      WHERE  document_id = X_document_id
        AND  language = X_language
      FOR UPDATE of language NOWAIT;

Line: 540


PROCEDURE Update_Row(X_document_id                  NUMBER,
                     X_last_update_date             DATE,
                     X_last_updated_by              NUMBER,
                     X_last_update_login            NUMBER,
                     X_datatype_id                  NUMBER,
                     X_category_id                  NUMBER,
                     X_security_type                NUMBER,
                     X_security_id                  NUMBER,
                     X_publish_flag                 VARCHAR2,
                     X_image_type                   VARCHAR2,
                     X_storage_type                 NUMBER,
                     X_usage_type                VARCHAR2,
                     X_start_date_active            DATE,
                     X_end_date_active              DATE,
                     X_language                     VARCHAR2,
                     X_description                  VARCHAR2,
                     X_file_name                    VARCHAR2,
                     X_media_id                     NUMBER,
                  X_Attribute_Category                  VARCHAR2 DEFAULT NULL,
                  X_Attribute1                          VARCHAR2 DEFAULT NULL,
                  X_Attribute2                          VARCHAR2 DEFAULT NULL,
                  X_Attribute3                          VARCHAR2 DEFAULT NULL,
                  X_Attribute4                          VARCHAR2 DEFAULT NULL,
                  X_Attribute5                          VARCHAR2 DEFAULT NULL,
                  X_Attribute6                          VARCHAR2 DEFAULT NULL,
                  X_Attribute7                          VARCHAR2 DEFAULT NULL,
                  X_Attribute8                          VARCHAR2 DEFAULT NULL,
                  X_Attribute9                          VARCHAR2 DEFAULT NULL,
                  X_Attribute10                         VARCHAR2 DEFAULT NULL,
                  X_Attribute11                         VARCHAR2 DEFAULT NULL,
                  X_Attribute12                         VARCHAR2 DEFAULT NULL,
                  X_Attribute13                         VARCHAR2 DEFAULT NULL,
                  X_Attribute14                         VARCHAR2 DEFAULT NULL,
                  X_Attribute15                         VARCHAR2 DEFAULT NULL,
                  X_url                          VARCHAR2 DEFAULT NULL,
                  X_title			 VARCHAR2 DEFAULT NULL,
                  X_dm_node                      NUMBER DEFAULT NULL,
                  X_dm_folder_path               VARCHAR2 DEFAULT NULL,
                  X_dm_type                      VARCHAR2 DEFAULT NULL,
                  X_dm_document_id               NUMBER DEFAULT NULL,
                  X_dm_version_number            VARCHAR2 DEFAULT NULL
) IS
BEGIN
  UPDATE fnd_documents
  SET document_id = X_document_id,
      last_update_date = X_last_update_date,
	 last_updated_by = X_last_updated_by,
	 last_update_login = X_last_update_login,
	 datatype_id = X_datatype_id,
	 category_id = X_category_id,
	 security_type = X_security_type,
	 security_id = X_security_id,
	 publish_flag = X_publish_flag,
	 image_type = X_image_type,
	 storage_type = X_storage_type,
	 usage_type = X_usage_type,
	 start_date_active = X_start_date_Active,
	 end_date_active = X_end_date_Active,
         url = X_url,
         media_id = X_media_id,
         file_name = X_file_name,
         dm_node = X_dm_node,
         dm_folder_path = X_dm_folder_path,
         dm_type = X_dm_type,
         dm_document_id = X_dm_document_id,
         dm_version_number = X_dm_version_number
  WHERE document_id  = X_document_id;

Line: 613


 Update_tl_Row(X_document_id             => X_document_id,
               X_last_update_date        => X_last_update_date,
               X_last_updated_by         => X_last_updated_by,
               X_last_update_login       => X_last_update_login,
               X_language                => X_language,
               X_description             => X_description,
               X_Attribute_Category      => X_Attribute_Category,
               X_Attribute1              => X_Attribute1,
               X_Attribute2              => X_Attribute2,
               X_Attribute3              => X_Attribute3,
               X_Attribute4              => X_Attribute4,
               X_Attribute5              => X_Attribute5,
               X_Attribute6              => X_Attribute6,
               X_Attribute7              => X_Attribute7,
               X_Attribute8              => X_Attribute8,
               X_Attribute9              => X_Attribute9,
               X_Attribute10             => X_Attribute10,
               X_Attribute11             => X_Attribute11,
               X_Attribute12             => X_Attribute12,
               X_Attribute13             => X_Attribute13,
               X_Attribute14             => X_Attribute14,
               X_Attribute15             => X_Attribute15,
               X_title                   => X_title);

Line: 638


END Update_Row;

Line: 641


PROCEDURE Update_tl_Row(X_document_id                  NUMBER,
                     X_last_update_date             DATE,
                     X_last_updated_by              NUMBER,
                     X_last_update_login            NUMBER DEFAULT NULL,
                     X_language                     VARCHAR2,
                     X_description                  VARCHAR2 DEFAULT NULL,
                  X_Attribute_Category                  VARCHAR2 DEFAULT NULL,
                  X_Attribute1                          VARCHAR2 DEFAULT NULL,
                  X_Attribute2                          VARCHAR2 DEFAULT NULL,
                  X_Attribute3                          VARCHAR2 DEFAULT NULL,
                  X_Attribute4                          VARCHAR2 DEFAULT NULL,
                  X_Attribute5                          VARCHAR2 DEFAULT NULL,
                  X_Attribute6                          VARCHAR2 DEFAULT NULL,
                  X_Attribute7                          VARCHAR2 DEFAULT NULL,
                  X_Attribute8                          VARCHAR2 DEFAULT NULL,
                  X_Attribute9                          VARCHAR2 DEFAULT NULL,
                  X_Attribute10                         VARCHAR2 DEFAULT NULL,
                  X_Attribute11                         VARCHAR2 DEFAULT NULL,
                  X_Attribute12                         VARCHAR2 DEFAULT NULL,
                  X_Attribute13                         VARCHAR2 DEFAULT NULL,
                  X_Attribute14                         VARCHAR2 DEFAULT NULL,
                  X_Attribute15                         VARCHAR2 DEFAULT NULL,
                  X_title				VARCHAR2 DEFAULT NULL
) IS
BEGIN
  UPDATE fnd_documents_tl
  SET document_id = X_document_id,
      last_update_date = X_last_update_date,
      last_updated_by =  X_last_updated_by,
      last_update_login = X_last_update_login,
      language = X_language,
      description = X_description,
      doc_attribute_category = X_Attribute_Category,
      doc_attribute1 = X_Attribute1,
      doc_attribute2 =  X_Attribute2,
      doc_attribute3 =  X_Attribute3,
      doc_attribute4 =  X_Attribute4,
      doc_attribute5 =  X_Attribute5,
      doc_attribute6 =  X_Attribute6,
      doc_attribute7 =  X_Attribute7,
      doc_attribute8 =  X_Attribute8,
      doc_attribute9 =  X_Attribute9,
      doc_attribute10 = X_Attribute10,
      doc_attribute11 = X_Attribute11,
      doc_attribute12 = X_Attribute12,
      doc_attribute13 = X_Attribute13,
      doc_attribute14 = X_Attribute14,
      doc_attribute15 = X_Attribute15,
      title = X_title
  WHERE document_id = X_document_id
    AND language = X_language;

Line: 697


END Update_tl_Row;

Line: 699


PROCEDURE Delete_Row(X_document_id NUMBER,
	             X_datatype_id NUMBER,
		     delete_ref_Flag VARCHAR2 DEFAULT 'N') IS
BEGIN
  --  need to delete in this order for R10-10SC compatibility
  --  triggers to operate properly
  --  1.  fnd_attached_documents
  --  2.  fnd_documents_short_text/long_text/long_raw
  --  3.  fnd_documents_tl
  --  4.  fnd_documents
 -- Delete the Reference if flag set to Y
 IF (delete_ref_flag = 'Y') THEN
	DELETE FROM fnd_attached_documents
	WHERE document_id = X_document_id;

Line: 718


	  DELETE FROM fnd_documents_short_text
   	WHERE media_id IN
		(SELECT media_id
	 	 FROM fnd_documents
		 WHERE document_id = x_document_id);

Line: 724


	  DELETE FROM fnd_documents_long_text
   	   WHERE media_id IN
		(SELECT media_id
		  FROM fnd_documents
		  WHERE document_id = x_document_id);

Line: 730


	  DELETE FROM fnd_documents_long_raw
   	   WHERE media_id IN
		(SELECT media_id
		  FROM fnd_documents
		  WHERE document_id = x_document_id);

Line: 736


         DELETE FROM fnd_lobs
         WHERE file_id in
               (SELECT media_id
                from fnd_documents
                WHERE document_id = x_document_id);

Line: 743


 DELETE FROM fnd_documents_tl
   WHERE document_id = X_document_id;

Line: 746


 DELETE FROM fnd_documents
  WHERE document_id = X_document_id;

Line: 749


END Delete_Row;

Line: 755


/* The following delete and update statements are commented out */
/* as a quick workaround to fix the time-consuming table handler issue */
/* Eventually we'll need to turn them into a separate fix_language procedure */
/*

  delete from FND_DOCUMENTS_TL T
  where not exists
    (select NULL
    from FND_DOCUMENTS B
    where B.DOCUMENT_ID = T.DOCUMENT_ID
    );

Line: 767


  update FND_DOCUMENTS_TL T set (
      DESCRIPTION
    ) = (select
      B.DESCRIPTION
    from FND_DOCUMENTS_TL B
    where B.DOCUMENT_ID = T.DOCUMENT_ID
    and B.LANGUAGE = T.SOURCE_LANG)
  where (
      T.DOCUMENT_ID,
      T.LANGUAGE
  ) in (select
      SUBT.DOCUMENT_ID,
      SUBT.LANGUAGE
    from FND_DOCUMENTS_TL SUBB, FND_DOCUMENTS_TL SUBT
    where SUBB.DOCUMENT_ID = SUBT.DOCUMENT_ID
    and SUBB.LANGUAGE = SUBT.SOURCE_LANG
    and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
      or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
      or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
  ));

Line: 789


   insert /*+ append parallel(tt) */ into FND_DOCUMENTS_TL tt (
    DOCUMENT_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    DESCRIPTION,
    REQUEST_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_ID,
    PROGRAM_UPDATE_DATE,
    DOC_ATTRIBUTE_CATEGORY,
    DOC_ATTRIBUTE1,
    DOC_ATTRIBUTE2,
    DOC_ATTRIBUTE3,
    DOC_ATTRIBUTE4,
    DOC_ATTRIBUTE5,
    DOC_ATTRIBUTE6,
    DOC_ATTRIBUTE7,
    DOC_ATTRIBUTE8,
    DOC_ATTRIBUTE9,
    DOC_ATTRIBUTE10,
    DOC_ATTRIBUTE11,
    DOC_ATTRIBUTE12,
    DOC_ATTRIBUTE13,
    DOC_ATTRIBUTE14,
    DOC_ATTRIBUTE15,
    APP_SOURCE_VERSION,
    SHORT_TEXT,
    LANGUAGE,
    SOURCE_LANG)
    select /*+ parallel(v) parallel(t) use_nl(t) */  v.*
    from( SELECT /*+ no_merge ordered parellel(b) */
    B.DOCUMENT_ID,
    B.CREATION_DATE,
    B.CREATED_BY,
    B.LAST_UPDATE_DATE,
    B.LAST_UPDATED_BY,
    B.LAST_UPDATE_LOGIN,
    B.DESCRIPTION,
    B.REQUEST_ID,
    B.PROGRAM_APPLICATION_ID,
    B.PROGRAM_ID,
    B.PROGRAM_UPDATE_DATE,
    B.DOC_ATTRIBUTE_CATEGORY,
    B.DOC_ATTRIBUTE1,
    B.DOC_ATTRIBUTE2,
    B.DOC_ATTRIBUTE3,
    B.DOC_ATTRIBUTE4,
    B.DOC_ATTRIBUTE5,
    B.DOC_ATTRIBUTE6,
    B.DOC_ATTRIBUTE7,
    B.DOC_ATTRIBUTE8,
    B.DOC_ATTRIBUTE9,
    B.DOC_ATTRIBUTE10,
    B.DOC_ATTRIBUTE11,
    B.DOC_ATTRIBUTE12,
    B.DOC_ATTRIBUTE13,
    B.DOC_ATTRIBUTE14,
    B.DOC_ATTRIBUTE15,
    B.APP_SOURCE_VERSION,
    B.SHORT_TEXT,
    L.LANGUAGE_CODE,
    B.SOURCE_LANG
  from FND_DOCUMENTS_TL B, FND_LANGUAGES L
  where L.INSTALLED_FLAG in ('I', 'B')
  and B.LANGUAGE = userenv('LANG')
  ) v, fnd_documents_tl t
    where T.DOCUMENT_ID(+) = v.DOCUMENT_ID
    and T.LANGUAGE(+) = v.LANGUAGE_CODE
    and t.document_id IS NULL;