Obtain Order Approval Comments - Rejection or Approval.sql

-- Obtain Order Approver details.
SELECT
	dha.order_number order_number
  , dha.header_id
  , dha.approval_sequence_number
  , t.IDENTIFICATIONKEY || '#####'
  , dha.creation_date
  , t.ASSIGNEES assignee
  , t.version
  , t.CREATEDDATE
  , t.UPDATEDDATE
  , t.state
  , t.outcome
FROM
	fusion.doo_headers_all    dha
  , fa_fusion_soainfra.wftask t
WHERE
	t.IDENTIFICATIONKEY      LIKE'DOO' || dha.header_id || '%'
	AND dha.submitted_flag      = 'Y'
	AND dha.source_order_number = '&SOURCE_ORDER_NUMBER'
UNION
SELECT
	dha.order_number order_number
  , dha.header_id
  , dha.approval_sequence_number
  , t.IDENTIFICATION_KEY || '#####'
  , dha.creation_date
  , a.ASSIGNEE
  , a.version
  , a.CREATION_DATE    CREATEDDATE
  , a.LAST_UPDATE_DATE UPDATEDDATE
  , t.STATUS_CODE      state
  , t.OUTCOME_CODE     outcome
FROM
	fusion.fnd_bpm_task_assignee a
  , fusion.FND_BPM_TASK_B        t
  , fusion.doo_headers_all       dha
WHERE
	a.task_id                   =t.task_id
	AND t.identification_key LIKE'DOO' || dha.header_id || '%'
	AND dha.submitted_flag      = 'Y'
	AND dha.source_order_number = '&SOURCE_ORDER_NUMBER'
-- Obtain Order Approver comments.
SELECT
	dha.order_number order_number
  , dha.header_id
  , dha.approval_sequence_number
  , t.IDENTIFICATIONKEY || '#####'
  , dha.creation_date
  , c.WFCOMMENT
  , c.UPDATEDBYDISPLAYNAME
FROM
	fa_fusion_soainfra.wfcomments c
  , fa_fusion_soainfra.wftask     t
  , fusion.doo_headers_all        dha
WHERE
	c.taskid (+)                =t.taskid
	AND t.identificationkey  LIKE'DOO' || dha.header_id || '%'
	AND dha.submitted_flag      = 'Y'
	AND dha.source_order_number = '&SOURCE_ORDER_NUMBER'
UNION
SELECT
	dha.order_number order_number
  , dha.header_id
  , dha.approval_sequence_number
  , t.IDENTIFICATION_KEY || '#####'
  , dha.creation_date
  , c.COMMENT_TEXT
  , c.comment_by
FROM
	fusion.FND_BPM_TASK_COMMENT c
  , fusion.FND_BPM_TASK_B       t
  , fusion.doo_headers_all      dha
WHERE
	c.task_id (+)               =t.task_id
	AND t.identification_key LIKE'DOO' || dha.header_id || '%'
	AND dha.submitted_flag      = 'Y'
	AND dha.source_order_number = '&SOURCE_ORDER_NUMBER'