xECM SQL SELECTs

by Phil Egger Phil Egger

Certified SuccessFactors © for xECM consultant / developer / business administrator / analyst.

Check out our useful examples that we collected in our OpenText © Extended ECM Content Server projects.

Get Me Home Contact Me

Get by DataID Business Workspace Links to SAP Business Objects

Database selection to get business workspaces links (referenced SAP IDs).

MSSQL /
ORACLE 

SELECT ty.BUSINESS_OBJECT_TYPE, li.ID_BO, li.DataID
  FROM OTSAPXECM_WKSP_LINKS li
INNER JOIN OTSAP_BO_TYPES ty
  ON ty.ID_BO_TYPE = li.ID_BO_TYPE
WHERE li.DataID = <bws_dataid>

Information

Get Workspace SAP IDs by DataID

Microsoft SQL Server or ORACLE
Use with LiveReport
dataid: id of business workspace. I.e. node id of Enterprise:SAP SuccessFactors Integration:
Business Workspaces:Employee Workspaces
Employee Phil Egger (Z70080539)

Select Workspaces with Metadata

Database selection to get business workspaces with metadata.

MSSQL /
ORACLE 

SELECT b.DataID, b.Name, md.ValStr as UserID
FROM DTree b
INNER JOIN DTreeAncestors a
  ON a.AncestorID = <parentfolder>
  AND a.DataID = b.DataID 
INNER JOIN  LLAttrData md 
  ON md.DefID = <categoryid>
  AND md.AttrID = <attrid>
  AND md.ID = b.DataID
  AND md.VerNum = b.VersionNum 
WHERE b.SubType = 848

Information

Get Workspaces with Metadata

Microsoft SQL Server or ORACLE
Use with LiveReport
parentfolder: id of folder that contains the business workspaces. I.e. node id of Enterprise:SAP SuccessFactors Integration:
Business Workspaces:Employee Workspaces
categoryid: id of category. I.e. node id of Content Server Categories:SuccessFactors:
OTHCM_WS_Employee_Category:Personal Information
attrid: id of attribute in category Personal Information (show html source in browser of category definition and navigate to the field). I.e. attribute id for User ID in category Personal Information is by default 26

Select Documents in a Folder

Database selection to get documents inside a specific folder of the business workspaces.

MSSQL /
ORACLE 

SELECT f.DataID, f.Name, d.DataID, d.Name
  FROM DTreeAncestors af 
  INNER JOIN DTree f
    ON f.DataID = af.DataID
  INNER JOIN DTreeAncestors ad
    ON f.DataID = ad.AncestorID
  INNER JOIN DTree d
    ON d.DataID = ad.DataID
  WHERE af.AncestorID = <parentfolder>
    AND f.Name = '<name of folder>'
    AND f.SubType = 0
    AND d.SubType = 144

Information

Get all Documents inside a specific Folder.

Microsoft SQL Server or ORACLE
Use with LiveReport
parentfolder: id of folder that contains the business workspaces. I.e. node id of Enterprise:SAP SuccessFactors Integration:Business Workspaces:Employee Workspaces
name of folder: 110 Recruiting / Application (hint: use LIKE and combine with AND f.Name LIKE '110 %'