Get and Display Approval Details for Sales Orders

You can get approval details and use them for your specific needs, such as in an audit report.

Run an SQL query to get the value of the Approved Date attribute and the Approved By attribute.

Assume you need to get details for sales order 514517. To start, get the header ID.

select header_id from doo_headers_all where order_number='514517';

Assume the query returns a header ID value of 300100246104588. Next, get the task ID.

select taskid from FA_FUSION_SOAINFRA.WFTASK where identificationkey like '%300100246104588%';

Assume the query returns task ID cdfdb0b2-639d-4cc8-acce-df5031154d8f. Use the task ID to get approval details, such as Approval Date, Comments, Approved By, and so on.

select WFCOMMENT from FA_FUSION_SOAINFRA.WFCOMMENTS; where taskid ='cdfdb0b2-639d-4cc8-acce-df5031154d8f'
select * from FA_FUSION_SOAINFRA.WFTASKHISTORY where roottaskid='cdfdb0b2-639d-4cc8-acce-df5031154d8f';

Display Approval Details in Your Custom Report

Use the Reports and Analytics work area to run a query.

select h.order_number order_number,h.creation_date ,t.ASSIGNEES
assignee,t.version ,t.CREATEDDATE,t.UPDATEDDATE,t.state,t.outcome from
doo_headers_all h,fa_fusion_soainfra.wftask t where
t.identificationkey='DOO'||h.header_id||h.approval_sequence_number
union
select
h.order_number,h.creation_date,a.ASSIGNEE,a.version,a.CREATION_DATE
CREATEDDATE,a.LAST_UPDATE_DATE UPDATEDDATE,t.STATUS_CODE state,t.OUTCOME_CODE
outcome from fnd_bpm_task_assignee a,FND_BPM_TASK_B t,doo_headers_all h where
a.task_id=t.task_id and
t.identification_key='DOO'||h.header_id||h.approval_sequence_number
select h.order_number,h.creation_date,c.COMMENT_TEXT,c.comment_by
from FND_BPM_TASK_COMMENT c,FND_BPM_TASK_B t,doo_headers_all h where
c.task_id=t.task_id and
t.identification_key='DOO'||h.header_id||h.approval_sequence_number
union
select
h.order_number,h.creation_date,c.WFCOMMENT,c.UPDATEDBYDISPLAYNAME
from fa_fusion_soainfra.wfcomments c,fa_fusion_soainfra.wftask
t,doo_headers_all h where c.taskid=t.taskid and
t.identificationkey='DOO'||h.header_id||h.approval_sequence_number

Get details about the approver.

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'

Get the approver's 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'