BIP – Query to find size of documents attached in DOR
Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:
select fdv.file_name
,fdv.title
,dm_document_id
,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) as "Size in KB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) as "Size in MB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
from hr_documents_of_record hdr,
fnd_attached_documents fad,
fnd_documents_vl fdv,
fusion_ocserver11g.revisions wcc_revisions,
fusion_ocserver11g.documents wcc_documents
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
and fad.document_id = fdv.document_id(+)
and fdv.dm_version_number = wcc_revisions.did
and wcc_revisions.did = wcc_documents.did
and wcc_documents.disprimary = 1
group by fdv.file_name
,fdv.title
,dm_document_id
order by 1