Hello Ahmad,
Here's an SQL query that will give you the "program" field for every UDP in every project in your database (both the pre-link/post-link UDPs and the stand-alone UDPs):
SELECT * FROM (
SELECT
HE.ENVIRONMENTNAME,
DECODE(HE.ENVISACTIVE,'Y','ACTIVE','N','INACTIVE','T','TEMPLATE') ENVTYPE,
HS.STATENAME,
HSP.PROCESSNAME AS PARENTPROCESS,
DECODE(HLP.PROCESSPRELINK,'Y','PRELINK','POSTLINK') LINKTYPE,
HU.PROCESSOBJID,
HU.PROCESSNAME,
HU.PROGRAMNAME
FROM
HARUDP HU
INNER JOIN HARLINKEDPROCESS HLP ON HLP.PROCESSOBJID = HU.PROCESSOBJID
AND HLP.PARENTPROCOBJID = HU.PARENTPROCOBJID
INNER JOIN HARSTATEPROCESS HSP ON HSP.PROCESSOBJID = HLP.PARENTPROCOBJID
AND HSP.STATEOBJID = HLP.STATEOBJID
INNER JOIN HARSTATE HS ON HS.STATEOBJID = HSP.STATEOBJID
INNER JOIN HARENVIRONMENT HE ON HE.ENVOBJID = HS.ENVOBJID
UNION
SELECT
HE.ENVIRONMENTNAME,
DECODE(HE.ENVISACTIVE,'Y','ACTIVE','N','INACTIVE','T','TEMPLATE') ENVTYPE,
HS.STATENAME,
'STANDALONE' PARENTPROCESS,
' ' LINKTYPE,
HU.PROCESSOBJID,
HU.PROCESSNAME,
HU.PROGRAMNAME
FROM
HARUDP HU
INNER JOIN HARSTATEPROCESS HSP ON HSP.PROCESSOBJID = HU.PROCESSOBJID
AND HSP.STATEOBJID = HU.STATEOBJID
INNER JOIN HARSTATE HS ON HS.STATEOBJID = HSP.STATEOBJID
INNER JOIN HARENVIRONMENT HE ON HE.ENVOBJID = HS.ENVOBJID
) RESULT
ORDER BY ENVIRONMENTNAME, STATENAME, PARENTPROCESS, PROCESSNAME
------------------------------
Melinda Skelton
------------------------------
Original Message:
Sent: 11-27-2019 08:50 PM
From: AHMAD NADEEM
Subject: Database Schema
I am trying to get the list of all UDP properties specially content/value of Program from state of Project. If any one know where to find in Harvest schema.
Thanks