SQL query for retrieving information from Work Definitions Table

POD AH

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

Riyaz Ali-Oracle

Hi,

Try this:

SELECT
wd.WORK_DEFINITION_HEADER_NAME AS work_definition_code,
wdn.WORK_DEFINITION_NAME AS work_definition_name,
wd.WORK_DEFINITION_TYPE,
wd.INACTIVE_DATE AS end_date,
op.OPERATION_SEQ_NUMBER,
op.OPERATION_TYPE,
op.OPERATION_NAME,
op.DESCRIPTION AS operation_description,
wc.WORK_CENTER_NAME,
res.RESOURCE_NAME,
res.USAGE,
res.SCHEDULED_FLAG,
res.CHARGE_TYPE,
doc.FILE_NAME AS attachment_file
FROM
WIS_WORK_DEFINITIONS wd
JOIN
WIS_WORK_DEFINITION_NAMES_B wdn ON wd.WORK_DEFINITION_NAME_ID = wdn.WORK_DEFINITION_NAME_ID
JOIN
WIS_OPERATIONS_B op ON wd.WORK_DEFINITION_ID = op.WORK_DEFINITION_ID
LEFT JOIN
WIS_OPERATION_RESOURCES_B res ON op.OPERATION_ID = res.OPERATION_ID
LEFT JOIN
WIS_WORK_CENTERS_B wc ON res.WORK_CENTER_ID = wc.WORK_CENTER_ID
LEFT JOIN
FND_ATTACHED_DOCUMENTS fad ON fad.ENTITY_NAME = 'WIS_WORK_DEFINITIONS' AND fad.PK1_VALUE = wd.WORK_DEFINITION_ID
LEFT JOIN
FND_DOCUMENTS doc ON fad.DOCUMENT_ID = doc.DOCUMENT_ID
WHERE
wd.ORGANIZATION_ID = :your_org_id

Replace :your_org_id with your actual organization ID. You may also need to filter by INVENTORY_ITEM_ID or WORK_METHOD_ID depending on your use case.

Check the below:

Tables and Views for SCM

Thanks,
Riyaz Ali

POD AH

@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.

Riyaz Ali-Oracle

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"