How to UNION data from approval and task tables?

[chuckh_]

Hi all,

      Auditors are looking for a one-stop view into the details of our changes, which includes change details coming from both the change task and the approval tables.   In short, we need to union rows from each of the two tables to chronologically show the work and the approvals that occurred for each change.   I have a sample query below which runs against an ODBC connection, but I need to know if something more efficient can be created 'within' the tool so that a scheduled report can be run and delivered instead of running/exporting SQL query results.

Query:

SELECT ct.dv_change_request AS ChangeNbr,
      'Task' as Type,
      ct.short_description AS Acty,
      ct.dv_state AS Status,
      convert(ct.closed_at,timestamp) AS Timestp,
      ct.dv_closed_by AS Auth_By,
      su.email as EMail
FROM change_task ct,
      sys_user su
  WHERE su.sys_id = ct.closed_by AND ct.dv_change_request='CHGXXXXXXX'

UNION ALL

SELECT sa.dv_sysapproval AS ChangeNbr,
      'Appr' as Type,
      sa.u_activity AS Acty,
      sa.dv_state AS Status,
      convert(sa.sys_updated_on,timestamp) AS Timestp,
      sa.dv_approver AS Auth_By,
      su.email as EMail
FROM sysapproval_approver sa,
                      sys_user su
WHERE su.sys_id = sa.approver AND sa.dv_sysapproval='CHGXXXXXXX' AND sa.dv_state='Approved'

ORDER BY 5;

Has anybody had to do something similar?   If so, how did you accomplish it?

Thanks in advance,

      Chuck H.

[chuckh_]

Thanks John.   I've just reviewed.

We actually have multiple approvers and at multiple times in some of our more complex flows.   Would the journal field support that by being able to report multiple approvals?

Chuck H.

[drjohnchun]

Yes. It's a journal field, so captures all approvals over time.

[chuckh_]

Hmmm....   doesn't seem to be populated. Is this the correct field?

SELECT ocr.approval_history

FROM OAUSER_change_request ocr

WHERE (((ocr.number)="CHGXXXXXXX"));

[drjohnchun]

Can you try using dv_approval_history

SELECT ocr.number, ocr.dv_approval_history

FROM OAUSER_change_request ocr

WHERE (((ocr.number)="CHGXXXXXXX"));

[chuckh_]

Good catch (the dv_), and thanks John, but still no luck.   The only dv_approval field I can see is dv_approval and no dv_approval_history field.   When I look at that, it's me, the last person to approve.   I double-checked our dev instance which doesn't have it either.

If there's truly a field out there with that name on it, either we don't have it or I don't have perms to see it???   If it IS there, yes, I think it's something I can work with....

ch