Order Fulfillment Line Orchestration Process Plan Progress and Status.sql

SELECT
	/* 3-35391968061 */
	dfla.source_order_number
  , (dla.display_line_number || '-' || dfla.fulfill_line_number) "display_line"
  , dfla.fulfill_line_id
  , b.DOO_PROCESS_ID
  , dpdv.PROCESS_NAME
  , b.step_number
  , ins.ACTUAL_start_DATE
  , ins.ACTUAL_COMPLETION_DATE
  , dttv.TASK_TYPE
  , tl.step_name
  , ins.step_status
  , task.status_code task_status
  , dha.order_number
  , grps.group_id
  , ins.doo_process_instance_id
  , grps.creation_date
  , grps.status
  , ins.doo_process_instance_id pid
  , ins.step_instance_id
  , ins.task_instance_id
  , tl.step_id
  , dttv.TASK_TYPE
  , B.DEFAULT_EXIT_STATUS_CODE
  , B.EXIT_CRITERIA_STATUS_CODE
FROM
	fusion.doo_headers_all            dha
  , fusion.doo_fulfill_lines_all      dfla
  , fusion.doo_lines_all              dla
  , fusion.doo_orchestration_groups   grps
  , fusion.doo_process_step_instances ins
  , fusion.doo_task_instances         task
  , fusion.doo_process_steps_b        b
  , fusion.doo_process_steps_tl       tl
  , fusion.DOO_PROCESS_DEFINITIONS_VL dpdv
  , fusion.DOO_TASK_DEFINITIONS_VL    dtdv
  , fusion.DOO_TASK_TYPES_VL          dttv
WHERE
	dha.source_order_number IN ('&SOURCE_ORDER_NUMBER')
	AND dha.submitted_flag   = 'Y'
	AND dha.header_id        = dfla.header_id
	AND dfla.line_id         = dla.line_id
	AND dha.header_id        = grps.header_id
	AND dfla.fulfill_line_id = grps.fulfillment_line_id
	AND grps.status          ='ACTIVE'
	AND grps.group_id        = ins.group_id
	AND ins.task_instance_id = task.task_instance_id
	AND ins.step_id          = b.step_id
	AND b.step_id            = tl.step_id
	AND TL.LANGUAGE          = 'US'
	AND dtdv.task_id         = task.task_id
	AND dtdv.task_type_id    = dttv.task_type_id
	AND b.DOO_PROCESS_ID     = dpdv.DOO_PROCESS_ID
ORDER BY
	dfla.source_order_number
  , 2
  , grps.creation_date
  , b.step_number
;