Orchestration Process Definition.sql

SELECT
	--        b.DOO_PROCESS_ID                ,
	 dpdv.PROCESS_NAME
  , dpdv.DOO_PROCESS_VERSION
  , dpdv.EFFECTIVE_START_DATE
  , dpdv.EFFECTIVE_END_DATE
  , dpdv.PROCESS_RELEASE_STATUS_CODE
  , b.STEP_TYPE
  , b.step_number
  ,
	 --        tl.step_id                      ,
	 tl.step_name
  , dttv.TASK_TYPE
  ,
	 --        dttv.TASK_TYPE_ID               ,
	 dtdv.NAME
  , dtdv.TASK_CODE
  , dsdv.SERVICE_NAME
  , dsdv.SERVICE_CODE
  , b.PARENT_STEP_NUMBER
  , b.MANUAL_STEP_FLAG
  , b.Default_EXIT_STATUS_CODE
  , b.EXIT_CRITERIA_STATUS_CODE
FROM
	fusion.DOO_PROCESS_DEFINITIONS_VL dpdv
  , fusion.doo_process_steps_b        b
  , fusion.doo_process_steps_tl       tl
  , fusion.DOO_SERVICE_DEFINITIONS_VL dsdv
  , fusion.DOO_TASK_DEFINITIONS_VL    dtdv
  , fusion.DOO_TASK_TYPES_VL          dttv
WHERE
	upper(dpdv.PROCESS_NAME) LIKE upper('CustomDOO_PMCTESTConditional')
	AND b.DOO_PROCESS_ID        = dpdv.DOO_PROCESS_ID
	AND b.step_id               = tl.step_id
	AND TL.LANGUAGE             = 'US'
	AND dsdv.service_id (+)     = b.service_id
	AND dsdv.TASK_TYPE_ID       = dttv.TASK_TYPE_ID (+)
	AND dsdv.TASK_TYPE_ID       = dtdv.TASK_TYPE_ID (+)
	AND b.task_id               = dtdv.task_id (+)
ORDER BY
	dpdv.PROCESS_NAME
  , dpdv.DOO_PROCESS_VERSION DESC
  , b.step_number
;