Transaction Console Invoice Approval History

Osmar Lima

I am trying to create a report to retrieve information from the transaction console related to invoice approval history.

I'm using the tables and SQL below, however, in some cases the approval history table does not provide information for some invoices/transactions.

The purpose of the report is to provide the history of approvers through the workflow.

Has anyone ever needed to build something similar?

Tabbles:

ap_invoices_all

ap_invoice_lines_all

AP_INVOICE_DISTRIBUTIONS_ALL

hz_parties hp

HRC_TXN_HEADER

FA_FUSION_SOAINFRA.WFTASK

  FA_FUSION_SOAINFRA.WFTASKHISTORY

HRC_TXN_CONSOLE_ENTRY

SQL:

SELECT    distinct aia.invoice_num,    aia.invoice_amount,    TO_CHAR(aia.invoice_id) invoice_id,    hp.party_name,    to_char(dist.ACCOUNTING_DATE, 'MM/DD/YYYY') ACCOUNTING_DATE,    history.UPDATEDBYDISPLAYNAME,    history.UPDATEDDATE UPDATEDDATE,    task.TASKID,    header.TRANSACTION_ID,    header.object_id,    history.IDENTIFICATIONKEY,    history.ASSIGNEESDISPLAYNAME,    history.STATE,    history.NUMBEROFTIMESMODIFIED,    history.OUTCOME,    history.VERSIONREASON,    history.FROMUSERDISPLAYNAME,    console.console_txn_status,    console.status_categoryfrom    ap_invoices_all aia,    ap_invoice_lines_all aila,    AP_INVOICE_DISTRIBUTIONS_ALL dist,    hz_parties hp,    HRC_TXN_HEADER header,    FA_FUSION_SOAINFRA.WFTASK task,    FA_FUSION_SOAINFRA.WFTASKHISTORY history,    HRC_TXN_CONSOLE_ENTRY consolewhere    1 = 1    AND aia.invoice_id = dist.invoice_id    and aia.Invoice_id = aila.Invoice_id    and aia.party_id = hp.party_id    and aia.Invoice_id = header.OBJECT_ID -- aia.Invoice_id = header.SUBJECT_ID    and to_char(header.OBJECT_ID) = task.IDENTIFICATIONKEY    and task.TASKID = history.taskid    and header.TRANSACTION_ID = console.TRANSACTION_ID -- and aia.invoice_num in ('441843826150', '2379411')    AND (        aia.invoice_num IN (:invoice_num)        OR COALESCE (:invoice_num, NULL) IS NULL    )    AND (        hp.party_name IN (:supplier_name)        OR COALESCE (:supplier_name, NULL) IS NULL    )    and (        dist.ACCOUNTING_DATE BETWEEN :AccountingDateFrom        AND :AccountingDateTo        OR :AccountingDateFrom IS NULL        OR :AccountingDateTo IS NULL    )ORDER BY    UPDATEDDATE,    history.NUMBEROFTIMESMODIFIED ASC

Mallikarjuna Kuppauru-Oracle

Hi @Osmar Lima

This is more on fusion application question , you can use below -

Fusion covered in a parallel forum (the question can't be moved there):

https://community.oracle.com/customerconnect

Regards,

Arjun