Tech Trainings

Oracle Cloud ERP – Query to get attachment details for Purchase Order header, lines and distributions

December 10, 2020 by admin

  select FAD.attached_document_id
       , FAD.entity_name
       , FAD.pk1_value 
       , FAD.pk2_value 
       , FAD.category_name category_code
       , FDV.dm_document_id  document_id
       , FDV.dm_version_number  document_version_number
       , FDV.datatype_code document_type 
       , decode(FDV.datatype_code
             , 'TEXT', nvl(FDV.title, 'Undefined')
             , 'SHORT_TEXT', nvl(FDV.title, 'Undefined')
             , 'FILE', nvl(FDV.title, FDV.file_name)
             , 'WEB_PAGE', nvl(FDV.title, FDV.url)
             , nvl(FDV.title, 'Undefined')
             ) document_title
       , FDV.file_name
       , FDV.description
       , decode(FDV.datatype_code
            , 'FILE', '/cs/idcplg' || '?' || 'IdcService=GET_FILE&' || 'dDocName=' || FDV.dm_document_id || '&' || 'RevisionSelectionMethod=Latest&' || 'Rendition=Primary'
            , 'WEB_PAGE', FDV.url
            , NULL
            ) document_url_path
       , FDV.LAST_UPDATE_DATE AS DOCUMENT_LAST_UPDATE_DATE
       , FAD.LAST_UPDATED_BY
       , LKP_DOC_TYPE.meaning document_type_meaning
  from   fnd_attached_documents  FAD
       , fnd_documents_vl      FDV
       , fnd_lookups           LKP_DOC_TYPE
  where  FDV.document_id   = FAD.document_id
  and    FAD.entity_name   IN ('PO_HEADERS', 'PO_LINE_LOCATIONS', 'PO_LINES') 
  and    LKP_DOC_TYPE.lookup_type = 'FND_DOCUMENT_DATATYPES' 
  and    LKP_DOC_TYPE.lookup_code = FDV.datatype_code

Comments |0|