Categories
- All Categories
- Oracle Analytics Learning Hub
- 17 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 224 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.8K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 84 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Transaction Console Invoice Approval History

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
Answers
-
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):
Regards,
Arjun
0