database schema for Prompt Sets

[Roger Shirley]

where can I find the Prompt Sets in the database in order to create a report?

[Pete Wirfs]

select *
from oh
where oh_otype = 'PRPT'
and oh_client = 100
and OH_DeleteFlag = 0
order by oh_name

[Roger Shirley]

Thanks, that give me the names of the Prompt Sets.

Another question is, can I list all the prompts inside each Prompt Set.

 

Roger Shirley

Ops Analyst

702-281-7789

[Pete Wirfs]

select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"
from uc4.dbo.opse
inner join uc4.dbo.oh on opse_oh_idnr = oh_idnr
where oh_otype = 'PRPT' and oh_client = 100
and OH_DELETEFLAG=0 and OH_REFIDNR=0
order by oh_name, opse_name

 

Also keep in mind that the values in these promptset objects are typically overridden in the parent objects that use them.

[Roger Shirley]

Thanks, again

I getting this error

Query could not be executed: 'ORA-00933: SQL command not properly ended

 

We have an Oracle database, I cannot find any reference to: uc4.dbo.opse

 

Roger Shirley

Ops Analyst

702-281-7789

[Pete Wirfs]

Reduce the table names to just "oh" and "opse".

[Roger Shirley]

This worked.

select oh_name "Object Name", opse_name "Variable Name", opse_value "Value"

from opse

inner join oh on opse_oh_idnr = oh_idnr

where oh_otype = 'PRPT' and oh_client = 693

and OH_DELETEFLAG=0 and OH_REFIDNR=0

order by oh_name, opse_name

 

Now I would like to be able to list the Prompt Values that are used when the jobs run in process flow.

 

Thanks,

 

Roger Shirley

Ops Analyst

702-281-7789

[Pete Wirfs]

I have never accomplished what you are asking for.  Too complex.

 

Say for instance you have a promptset that is used by a job that is used by a workflow that is used by another workflow that is used by a schedule.  Technically speaking the final value that is fed into the promptset at runtime could have come from any one of the objects in the mentioned parent/child relationships.

 

On the other hand, maybe you'd like to go data mining in the run-history areas of the database?  Then you could capture what was actually used on the previous execution.  (This too is something I have never accomplished and would not be of much help.)

[Roger Shirley]

Good morning Pete,

thanks for all your help.

 

Roger Shirley

Ops Analyst

702-281-7789

[Phil Frankford]

Might these help?

OH table with values:

select OH_Client as OH_Client /* Number/ID of the client. */

,OH_OType as OH_OType /* Type of the object. */

,OH_Name as OH_Name /* Name of the object. */

,OH_Archive1 as OH_Archive1 /* Archive key #1 */

,OV_VName as OV_VName /* Name of the object variable. */

,OV_Value as OV_Value /* Value of the object variable */

,OH_CrDate as OH_CrDate /* Creation date of the object. */

,OH_Idnr as OH_Idnr /* ID of the object. */

,OV_OH_Idnr as OV_OH_Idnr /* ID of the object. */

,OH_DeleteFlag as OH_DeleteFlag /* Delete flag of the object. 0 = not deleted, 1 = deleted, 2 = versioned, 5 = archived/reorganized */

,OH_ExpFlag as OH_ExpFlag /* Marks object ready for transport/unload. 0 = not marked, 1 = object located in transport case */

-- ,OPSEA_OH_Idnr as OPSEA_OH_Idnr

-- ,OPSEA_OPSE_Name as name_of_promptset

-- ,OPSEA_Value as prompt_value

from OH, OV

-- , OPSEA

where oh_client = &$CLIENT#

and oh_name like '&NAMEMASK#'

and OH_OType like '&OBJTYP#'

and oh_deleteflag = 0

and OH_ExpFlag = 0

and OH_OV_Flag = 1

and oh_idnr = ov_oh_idnr

and ov_vname like '&ScanVal#'

order by oh_name

;

AH table with values:

select AH_Client as AH_Client /* Number/ID of the client. */

,AH_OType as AH_OType /* Type of the object. */

,AH_Name as AH_Name /* Name of the object. */

,AH_Alias as Name

,AH_Archive1 as AH_Archive1 /* Archive key #1 */

,AH_MaxRetCode as MaxRC

,AH_TimeStamp1 as activ_time

,AH_TimeStamp2 as start_time

,AH_TimeStamp4 as end_time

,AH_Queue as RunQue

,AH_Title as AH_Title /* description of the object */

,AH_Idnr as AH_Idnr /* ID of the object. */

,AV_AH_Idnr as AV_AH_Idnr /* ID of the object. */

,AV_VName as AV_VName /* Name of the object variable. */

,AV_Value as AV_Value /* Value of the object variable */

,AV_Source as AV_Source /* 0 own, 1 parent, 2 runtime mod */

from AH, AV

where Ah_client = &$CLIENT#

and Ah_name like '&NAMEMASK#'

and AH_OType like '&OBJTYP#'

and Ah_idnr = Av_Ah_idnr

and Av_vname like '&ScanVal#'

order by AH_TimeStamp1, AH_Name

;

Event Table with values:

select EH_Client as EH_Client /* Number/ID of the client. */

,EH_OType as EH_OType /* Type of the object. */

,EH_Name as EH_Name /* Name of the object. */

,EH_Alias as Name

,EH_Archive1 as EH_Archive1 /* Archive key #1 */

,EH_ActivationTime as activ_time

,EH_StartTime as start_time

,EH_Status as job_status

,EH_Queue as RunQue

,EH_MaxRetCode as MaxRC

,EH_Title as EH_Title /* description of the object */

,EH_EV_Flag as EH_EV_Flag

,EH_AH_Idnr as EH_AH_Idnr /* ID of the object. */

,EV_AH_Idnr as EV_AH_Idnr /* ID of the object. */

,EV_VName as EV_VName /* Name of the object variable. */

,EV_Value as EV_Value /* Value of the object variable */

,EV_Source as EV_Source /* 0 own, 1 parent, 2 runtime mod */

from EH, EV v

where EH_client = &$CLIENT#

and EH_name like '&NAMEMASK#'

and EH_OType like '&OBJTYP#'

and EH_EV_Flag = '1'

and EH_AH_Idnr = EV_AH_idnr

and EV_vname like '&ScanVal#'

order by EH_ActivationTime, EH_name

;

Prompt execution example of OH

Be well,
Phil