-- The query provided will return Task Status information for all lines on the order. The query can be refined to include the fulfill_line_id of a specific line.
SELECT
	DHA.SOURCE_ORDER_NUMBER
  , DHA.ORDER_NUMBER
  , DLA.LINE_NUMBER
  , DLA.SOURCE_LINE_NUMBER
  , DLA.DISPLAY_LINE_NUMBER
  , DFLA.FULFILL_LINE_ID
  , DFLA.FULFILL_LINE_NUMBER
FROM
	fusion.DOO_HEADERS_ALL       dha
  , fusion.DOO_FULFILL_LINES_ALL dfla
  , fusion.DOO_LINES_ALL         DLA
WHERE
	DHA.HEADER_ID                = DFLA.HEADER_ID
	AND DFLA.LINE_ID             = DLA.LINE_ID
	AND DHA.SUBMITTED_FLAG       = 'Y'
	AND DHA. SOURCE_ORDER_NUMBER = ('&SOURCE_ORDER_NUMBER')
ORDER BY
	DLA.SOURCE_LINE_NUMBER
  , DLA.DISPLAY_LINE_NUMBER
  , DFLA.FULFILL_LINE_NUMBER
-- The fulfill_line_id can be used in the following query if required.
SELECT
	h.source_order_number
  , h.header_id
  , f.fulfill_line_id
  , f.fulfill_line_number
  , f.source_line_number
  , f.SOURCE_LINE_ID
  , pd.process_name
  , psvl.step_id
  , psvl.step_number
  , psvl.parent_step_number
  , psvlt.step_name
  , psi.step_instance_id
  , psi.group_id
  , psvl.step_type
  , psi.step_active
  , psi.step_status
  , psi.task_instance_id
  , NVL(TO_CHAR(psi.actual_start_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_start_Date"
  , NVL(TO_CHAR(psi.actual_completion_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "actual_completion_Date"
  , NVL(TO_CHAR(psi.planned_start_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_start_Date"
  , NVL(TO_CHAR(psi.planned_completion_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "planned_completion_Date"
  , NVL(TO_CHAR(psi.required_start_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL') "required_start_Date"
  , NVL(TO_CHAR(psi.required_completion_Date, 'dd-mm-yy hh24:mi:ss'), 'NULL')"required_completion_Date"
  , psi.LAST_UPDATED_BY
FROM
	fusion.doo_headers_all            h
  , fusion.doo_fulfill_lines_all      f
  , 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.source_order_number = '11056'
	--AND f.FULFILL_LINE_ID         = '&FULFILL_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.source_order_number
  , f.fulfill_line_id
  , psvl.doo_process_id
  , psvl.step_number