Orchestration Process, Steps and Step Status for an Order.sql

SELECT
	h.source_order_number
  , h.order_number
  , h.source_order_id
  , h.status_code
  , dla.LINE_NUMBER
  , dla.SOURCE_LINE_NUMBER
  , dla.DISPLAY_LINE_NUMBER
  , dla.status_code
  , f.FULFILL_LINE_NUMBER
  , f.status_code
  , pd.process_name
  , psvl.STEP_NUMBER
  , SERVICE_ID
  , g.IS_GROUP_EDITABLE
  , g.status
  , psvl.STEP_TYPE
  , psvl.PARENT_STEP_NUMBER
  , psvlt.STEP_NAME
  , psi.STEP_ACTIVE
  , psi.STEP_STATUS
  , psi.ACTUAL_START_DATE
  , psi.ACTUAL_COMPLETION_DATE
FROM
	fusion.doo_headers_all            h
  , fusion.doo_fulfill_lines_all      f
  , fusion.doo_lines_all              dla
  , fusion.doo_orchestration_groups   g
  , fusion.doo_process_steps_b        psvl
  , fusion.doo_process_definitions_b  pd
  , fusion.doo_process_steps_tl       psvlt
  , fusion.doo_process_step_instances psi
WHERE
	h.order_number                = '&SOURCE_ORDER_NUMBER'
	AND f.line_id                 = dla.line_id
	AND h.SUBMITTED_FLAG          = 'Y'
	AND h.header_id               = f.header_id
	AND f.header_id               = g.header_id
	AND g.status                  = 'ACTIVE'
	AND f.fulfill_line_id         = g.fulfillment_line_id
	AND g.doo_process_instance_id = psi.doo_process_instance_id
	AND psi.step_id               = psvl.step_id
	AND psvl.step_id              = psvlt.step_id
	AND psvl.doo_process_id       = pd.doo_process_id
	AND psvlt.LANGUAGE            = userenv('LANG')
ORDER BY
	h.header_id DESC
  , dla.line_id
  , f.fulfill_line_id
  , psvl.doo_process_id
  , psvl.step_id
;