SQL query for retrieving information from Work Definitions Table

Question

We are having issues identifying the relevant table for work definitions and their associated operations/resources. Can anyone provide a query (or give some direction) on how we can retrieve the following:

Work Definition Code, Work Definition Name, Work Definition Description, Start Date, Operation Seq Number, Operation type, Operation Name, Operation Description, Work Centre, Attachment (file name) if present, Resource (for each operation), usage, scheduled, Charge Type.

Thanks

Maalavica · Answer

Hi require table name for below field :

Riyaz Ali-Oracle · Answer

ORA-00942: Table or View Does Not Exist:

The table/view name is incorrect or doesn’t exist in your schema.

You don’t have SELECT privileges on it.

Use this query to check if the object exists:

SELECT owner, object_name, object_type

FROM all_objects

WHERE object_name LIKE '%WIS_WORK_DEFINITIONS%'

AND object_type IN ('TABLE', 'VIEW');

ORA-00907: Missing Right Parenthesis:

This is a syntax error:

A typo in a column alias or function

A missing comma or incorrect join condition

Using a reserved word without quotes

Try wrapping column names in double quotes:

res."USAGE_RATE", res."CHARGE_TYPE"

POD AH · Answer

@Riyaz Ali-Oracle  - unfortunately the SQL returns an error.

ORA-00942: table or view does not exist. I cross referenced some of the Table and Views and tried the below but still could not get it to work (error this time was ORA-00907: missing right parenthesis):

SELECT

wd.WORK_DEFINITION_HEADER_NAME AS work_definition_name,

wdn.WORK_DEFINITION_CODE AS work_definition_code,

wd.WORK_DEFINITION_TYPE,

wd.INACTIVE_DATE AS end_date,

op.OPERATION_SEQ_NUMBER,

op.OPERATION_TYPE,

optl.OPERATION_NAME,

optl.DESCRIPTION AS operation_description,

wc.WORK_CENTER_NAME,

res.RESOURCE_SEQ_NUMBER,

res.RESOURCE_ID,

rl.RESOURCE_NAME,

res.USAGE_RATE,

res.SCHEDULE_TYPE,

res.CHARGE_TYPE

FROM FUSION.WIS_WORK_DEFINITIONS wd

JOIN FUSION.WIS_WORK_DEFINITION_NAMES_B wdn

ON wd.WORK_DEFINITION_NAME_ID = wdn.WORK_DEFINITION_NAME_ID

JOIN FUSION.WIS_WD_OPERATIONS_B op

ON wd.WORK_DEFINITION_ID = op.WORK_DEFINITION_ID

LEFT JOIN FUSION.WIS_WD_OPERATIONS_TL optl

ON op.WD_OPERATION_ID = optl.WD_OPERATION_ID

LEFT JOIN FUSION.WIS_OPERATION_RESOURCES res

ON op.WD_OPERATION_ID = res.WD_OPERATION_ID

LEFT JOIN FUSION.WIS_RESOURCES_TL rl

ON res.RESOURCE_ID = rl.RESOURCE_ID

LEFT JOIN FUSION.WIS_WORK_CENTERS_B wc

ON res.WORK_CENTER_ID = wc.WORK_CENTER_ID

I removed the code for the documentation as I was unable to find these in the tables and views for SCM online.