HDL – Mass DELETE Document Record Attachments

Post refresh, the attachments from source environment gets copied to target environment. For data security, it is highly recommended to purge the attachments in lower environments. Oracle’s standard data masking feature doesn’t remove any attachments from the UCM.

In order to overcome this problem, I have created a BIP report which will pull details of all document records for all worker types having an attachment. The BIP generates data in HDL format, which can be saved as DocumentsOfRecord.dat.

Sample BIP data model query: (Below query can be filter based on document type or person number):

SELECT a.DATAROW
FROM
(
SELECT 'METADATA|DocumentAttachment|PersonNumber|DocumentTypeId|DocumentType|DocumentCode|DataTypeCode|Title|FileName' "DATAROW"
       ,1 row_num
  FROM DUAL
UNION 
SELECT 'DELETE|DocumentAttachment'||'|'||
       papf.person_number||'|'||
       hdor.document_type_id||'|'||
       hdtt.document_type||'|'||
       hdor.document_code||'|'||
       fd.datatype_code||'|'||
       fdt.title||'|'||
       fdt.file_name "DATAROW"
       ,2 row_num	   
  FROM per_all_people_f papf,
       hr_documents_of_record hdor,
	   hr_document_types_tl hdtt,
       fnd_attached_documents fad,
       fnd_documents_tl fdt,
       fnd_documents fd
 WHERE 1=1
   AND hdor.person_id = papf.person_id
   AND hdor.documents_of_record_id = fad.pk1_value
   AND fad.document_id = fdt.document_id
   AND fd.document_id = fdt.document_id
   AND fdt.language = 'US'
   AND fad.entity_name = 'HR_DOCUMENTS_OF_RECORD'
   AND hdor.document_type_id = hdtt.document_type_id
   AND hdtt.language = 'US'
   --AND papf.person_number = '269628'
   --AND hdtt.document_type = 'Passport Info'
   AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
 ORDER BY row_num
)  a

Sample HDL File:

METADATA|DocumentAttachment|PersonNumber|DocumentTypeId|DocumentType|DocumentCode|DataTypeCode|Title|FileName
DELETE|DocumentAttachment|26628|300001270162314|Passport Info|Please Upload Passport Photo_2024-6-14-5-51-42|FILE|Passport Photo|10.Photo.jpeg