How to join GL tables with XLA (SubLedger Accounting) tables

GL_JE_BATCHES (je_batch_id)                   => GL_JE_HEADERS (je_batch_id)

GL_JE_HEADERS (je_header_id)                 => GL_JE_LINES (je_header_id)

GL_JE_LINES (je_header_id,je_line_num)    => GL_IMPORT_REFERENCES (je_header_id, je_line_num)

GL_IMPORT_REFERENCES (gl_sl_link_table, gl_sl_link_id)=> XLA_AE_LINES (gl_sl_link_table, gl_sl_link_id)

XLA_AE_LINES (application_id, ae_header_id)      => EADERS(application_id, ae_header_id) 

XLA_AE_HEADERS (application_id, event_id)          => XLA_EVENTS (application_id, event_id)   

XLA_EVENTS (application_id, entity_id)               => XLA.XLA_TRANSACTION_ENTITIES (application_id, entity_id)

API to find AP INVOICE Status

When I tried to find the AP invoice status from backend, I found the column name APPROVAL_STATUS_LOOKUP_CODE under the view name AP_INVOICE_V. Since it is org based view, setting the context is mandatory. Please find my script to set the context here.

Below query will give you the status,

SELECT invoice_num

      ,approval_status_lookup_code

FROM   ap_invoices_v

WHERE  invoice_num = 'DM080310';

When I further traced to understand the source of the view column, I found that there is no column in the AP_INVOICES_ALL table that stores the validation status. An API named AP_INVOICES_PKG.GET_APPROVAL_STATUS is used by the view to finding the status.

Below query will give you the usage of the mentioned api,

SELECT APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS

            (

             I.INVOICE_ID

            ,I.INVOICE_AMOUNT

            ,I.PAYMENT_STATUS_FLAG

            ,I.INVOICE_TYPE_LOOKUP_CODE

            ) Approval_Status 

            ,AP_INVOICES_PKG.GET_POSTING_STATUS( D.INVOICE_ID) "Accounting Status"

      ,invoice_num

FROM   AP_INVOICES I

WHERE  invoice_num = 'DM080310';

    

When I went one more step deeper, I got the below,

Invoice distributions are validated individually and the status is stored at the invoice distribution level.  This status is stored in AP_INVOICE_DISTRIBUTIONS_ALL.MATCH_STATUS_FLAG.

Valid values for the column are:

A - Validated (it used to be called Approved)

N or NULL - Never validated

T - Tested but not validated

The invoice header form derives the invoice validation status based on the following:

'Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = 'A'

'Never Validated'

-          If all of the invoice distributions have a MATCH_STATUS_FLAG = null or 'N'

'Needs Revalidation'

-          If there are any rows in AP_HOLDS that do not have a release code.

-          If any of the invoice distributions have a MATCH_STATUS_FLAG = 'T'.

-          If the invoice distributions have MATCH_STATUS_FLAG values = 'N', null and 'A' (mixed).

I hope this information helps you.