How to Update Metadata of documents through DB at UCM(webcenter Content) ?

Posted on

let’s imagine the following scenario

you have different employee profile for different Document types of each employee such sanctions,vacations, services,certificate ,.. etc

and every UCM profile has 2 metadata “civil number of employee” , and ” employee name” and title which have specific signature as follow

“type of Documnet of ********** ” where the position of * is the civil number example “Vacations of 1235495689″ vacations “document title” and the number represent the civil number

and the entry user forget to enter the civil number of employee and those documents are more than 700 document

sure you will not request from user to back to all those document and updates their metadata it’s time consuming and headache

we need to make workaround to get from title the civil number and update the missed civil number

to overcome via DB this problem we should have background about the UCM DB diagram specially the tables of Documents

the metadata of document spread through more than table as following

DOCUMENTS    contain basic data of document “metadata of slandered chick-in”

DOCUMENTHISTORY  contain history of document and life cycle from new to released

DOCMETA    contain metadata of document the customized one

REVISIONS  contain different versions of document

all those tables contain the DID column primary key at DOCUMENTS   table and represented as foreign key at remaining tables

we need to modify metadata so it will be available on  DOCMETA   table but at our case we will get the new value from another metadata -basic one- the DDOCTitle  metadata which represent the  document title

so we need to set the civil number to equal sub string from the document title “get the last 10 characters from title column”

select dm.DID,dm.XCIVILRECORDNUMBER,R.DDOCTYPE,R.DDOCTITLE, substr(R.DDOCTITLE,-10,10)  as newcivil
from DEV_OCS.DOCMETA dm,REVISIONS r
where  r.did=dm.did
and XCIVILRECORDNUMBER is  not NULL

the previous SQL statement get all document with civil number null “have not civil number” and get also the civil number as substring from title field which belong to another table revision

we can get them by different method which will be ready for update statement as following

select  dm.XCIVILRECORDNUMBER ,(select  substr(R.DDOCTITLE,-10,10) from REVISIONS r  where r.did=dm.did) as newcivil
from DEV_OCS.DOCMETA dm
where dm. XCIVILRECORDNUMBER is NULL

now let’s run our update statement as following
update DEV_OCS.DOCMETA dm
set dm.XCIVILRECORDNUMBER =(select  substr(R.DDOCTITLE,-10,10) from REVISIONS r  where r.did=dm.did and r.ddoctype !=’Document’)
where dm. XCIVILRECORDNUMBER is NULL

the result will appear as following screenshot

18-08-2013 06-33-55 م

 

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s