販売オーダーの承認詳細の取得および表示

承認詳細を取得して、監査レポートなど特定のニーズにあわせて使用できます。

SQL問合せを実行して、承認済日付属性および承認済ユーザー属性の値を取得します。

販売オーダー514517の詳細を取得する必要があるとします。開始するには、ヘッダーIDを取得します。

select header_id from doo_headers_all where order_number='514517';

問合せでヘッダーID値300100246104588が返されるとします。次に、タスクIDを取得します。

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

問合せでタスクID cdfdb0b2-639d-4cc8-acce-df5031154d8fが返されるとします。タスクIDを使用して、承認日、コメント、承認者などの承認詳細を取得します。

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';

カスタム・レポートでの承認詳細の表示

レポートおよびアナリティクス作業領域を使用して、問合せを実行します。

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

承認者に関する詳細を取得します。

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'

承認者のコメントを取得します。

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'