EGO Item Image Definition Data Template

					SELECT
              fd.DOCUMENT_ID, 
	            fd.DATATYPE_ID, 
	            fd.DESCRIPTION, 
	            decode(fd.FILE_NAME, null, (select message_text 
					        from   fnd_new_messages 
					        where  message_name = 'FND_UNDEFINED' and 
						      application_id = 0 and language_code = userenv('LANG')),fd.FILE_NAME) FILE_NAME, 
	            fd.MEDIA_ID, 
	            fd.dm_type, 
	            fd.dm_node, 
	            fd.dm_folder_path,
	            fd.url,
	            FL.FILE_ID,
	            FL.FILE_NAME,
	            FL.FILE_CONTENT_TYPE,
	            FL.UPLOAD_DATE,
	            FL.EXPIRATION_DATE,
	            FL.PROGRAM_NAME,
	            FL.PROGRAM_TAG,
	            FL.FILE_DATA,
	            FL.LANGUAGE,
	            FL.ORACLE_CHARSET,
	            FL.FILE_FORMAT,
					  	  decode(fad.last_updated_by,-10000,(select message_text from fnd_new_messages where message_name = 'FND_WORKFLOW_USER_NAME' and application_id = 0 and language_code = userenv('LANG')), decode(fad.last_updated_by,-1, (select message_text from fnd_new_messages where message_name = 'FND_ANONYMOUS_USER_NAME' and application_id = 0 and language_code = userenv('LANG')),
						      (select ltrim(hz.party_name , '*')
						       from fnd_user u, hz_parties hz where u.user_id=fad.last_updated_by and u.person_party_id=hz.party_id))) LAST_UPDATED_BY_NAME
              , fad.LAST_UPDATE_DATE
              , ct.USER_NAME AS ATTACHMENT_CATEGORY
              , (select MEANING from FND_LOOKUP_VALUES_VL where LOOKUP_TYPE = 'DOM_ATTACHED_DOC_STATUS' and LOOKUP_CODE=nvl(decode (fd.datatype_id,8,file_ext.status,fad.STATUS),'UNAPPROVED')) STATUS
					FROM FND_ATTACHED_DOCUMENTS fad, 
					     FND_DOCUMENTS_VL fd, 
               fnd_lobs fl,
		           ego_mtl_sy_items_ext_b ext,
               mtl_parameters mp,
               mtl_system_items_kfv msik,
					     FND_DOCUMENT_CATEGORIES_TL ct,
					     DOM_REPOSITORIES n,
						   dom_file_ext file_ext,
					     DOM_FOLDER_ATTACHMENTS df
					WHERE 
              fad.pk1_value = mp.organization_id
              AND msik.inventory_item_id = fad.pk2_value
              AND msik.organization_id = mp.organization_id
              AND mp.organization_code = :p_organization_code
	            AND msik.concatenated_segments = :p_inventory_item_number  
              AND fad.entity_name = 'MTL_SYSTEM_ITEMS'
              AND fad.DOCUMENT_ID = fd.DOCUMENT_ID 
              AND fd.media_id = fl.file_id
              AND Fl.file_content_type IN ('image/gif','image/x-png','image/png','image/jpeg','image/pjpeg')
              AND 1 = :p_generate_item_desc_level
	            AND ext.organization_id = fad.pk1_value
	            AND ext.inventory_item_id = fad.pk2_value
	            AND ext.attr_group_id = 1
	            AND ext.data_level_id = 43102
	            AND ext.c_ext_attr3 = fad.attached_document_id
	            AND ext.c_ext_attr4 = 'AsImage'
              and
					      ct.CATEGORY_ID = nvl(fad.CATEGORY_ID, fd.CATEGORY_ID) and
					      ct.LANGUAGE = USERENV('LANG') and
					      fd.DM_NODE = n.ID (+)
					      and df.attachment_id(+)=fad.attached_document_id
						  and file_ext.repository_id(+)=fd.dm_node
						  and file_ext.version_id(+)=fd.dm_document_id