Which Order Management table should be used for Sales Order Approval Workflow?

Vrinda Choraria · Feb 24, 2025 2:04PM

To determine the processing time for Sales Orders (SOs), we need to retrieve the approval dates from various teams. It’s important to note that there are cases where SOs are returned for editing. In such cases, even if the SO is in the middle of the approval process, it is sent back to the Revenue team for re-approval.

Therefore, we need both the first approval dates and the final approval dates.

We are currently using the table fa_fusion_soainfra.wftask to obtain this data. However, it appears that once the SO is approved, the data is moved to the FND_BPM_TASK_B table.

The issue with FND_BPM_TASK_B is that it does not provide information about how many times the SO was approved or whether it was returned for editing.

Is there another Oracle table that stores data for SOs that are still in the middle of the approval process, as well as those that have been fully approved and contains details like how many times SO was approved, who approved the SO and if it was returned for editing ?

Bhaskar Konar · Feb 24, 2025 2:52PM

Please check also the following Oracle note:

https://support.oracle.com/knowledge/Oracle%20Cloud/2637463_1.html

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'

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'

Hope this help.

Cheers,

MandeepGupta · Feb 25, 2025 12:58AM

https://community.oracle.com/products/oracleanalytics/discussion/24690/which-order-management-table-should-be-used-for-sales-order-approval-workflow

Hi Vrinda,

As per my understanding, you will not be able to get the number of times the approval was done. That history is not maintained. You can only get last appoval if the transaction is approved and current assignees if in progress.

Thanks.