AP Forward and Backward Tracking Query (SQL)-( Tech R12)

AP Forward and Backward Tracking

Transaction Modes

Validate

TABLE: XLA_EVENTS

EVENT_STATUS_CODE=’I’ PROCESS_STATUS_CODE=’U’

Draft:

Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.

TABLE: XLA_AE_HEADERS

ACCOUNTING_ENTRY_STATUS_CODE =’D’

TABLE: XLA_EVENTS

PROCESS_STATUS_CODE=’D’ EVENT_STATUS_CODE=’U’

Final:

Final will create journal entries, which can be transferred to GL.

TABLE: XLA_AE_HEADERS

ACCOUNTING_ENTRY_STATUS_CODE =’F’

TABLE: XLA_EVENTS

PROCESS_STATUS_CODE=’F’ EVENT_STATUS_CODE=’F’

Final Post:

    Final Post will create journal entries in final mode, transfer them to GL and post them.

TABLE: XLA_AE_HEADERS

ACCOUNTING_ENTRY_STATUS_CODE=’F’ GL_TANSFER_STATUS_CODE=’Y’

TABLE: XLA_EVENTS

PROCESS_STATUS_CODE=’P’ EVENT_STATUS_CODE=’P’

GL Back Tracking:

                                                               

S#
1.	We query the GL_JE_BATCHES table and see BATCH_ID against our batch name
2.	We see the JE_HEADER_ID from JL_JE_HEADERS against Our Batch ID
3.	We see the REFERENCE_5 Column from GL_IMPORT_REFERENCES that have the entity id Note(Reference_6 have the value of Event_id)
4.	In final step we query the XLA_TRANSACTION_ENTITIES_UPG table and find INVOICE_ID from its SOURCE_ID_INT_1.In this way we reach to the AP_INVOICES_ALL table.

RECOMMENDED WAY FOR R12

S#
1.	We query the GL_JE_BATCHES table and see BATCH_ID against our batch name
2.	We see the JE_HEADER_ID from GL_JE_HEADERS against Our Batch ID
3.	We query GL_JE_LINES and select JE_LINE_ID against our JE_HEADER_ID for further process.
4.	In GL_IMPORT_REFERENCES we will find our JE_LINE_ID and see GL_SL_LINK_ID against this line.
5.	In XLA_AE_LINES we find above GL_SL_LINK_ID and see it’s AE_HEADER_ID.
6.	In XLA_AE_HEADERS we will find above AE_HEADER_ID and see it’s Event_ID
7.	In XLA_EVENTS we will find above EVENT_ID and see IT’s ENTITY_ID
8.	In XLA_TRANSACTION_ENTITIES we will find above ENTITY_ID and see it’s SOURCE_ID_INT_1 that is basically Inoice_ID
9.	In AP_INVOICE_ALL we will find this particular invoice.

Query for Back Tracking:

                                                    We will pass JE_BATCH_ID to this query and it show us all the invoices against this batch from AP_INVOICES_ALL.

SELECT * FORM AP_INVOICES_ALL WHERE INVOICE_ID IN

(

SELECT SOURCE_ID_INT_1 FROM XLA_TRANSACTION_ENTITIES_UPG

 WHERE ENTITY_ID IN

(

SELECT ENTITY_ID FORM XLA_EVENTS

WHERE EVENT_ID IN

(

SELECT REFERENCE_6 FROM GL_IMPORT_REFERENCES

WHERE JE_HEADER_ID IN

(

SELECT JE_HEADER_ID FORM GL_JE_HEADERS WHERE  JE_BATCH_ID=&JE_BATCH_ID

)

)

)

)

Tracking of Records that are Un Transferred to GL:

                                                                              We will pass Period name to this query and it gives us output of Un transferred Entries to GL   

SELECT * FROM AP_INVOICES_ALL

WHERE INVOICE_ID IN

(

SELECT SOURCE_ID_INT_1 INVOICE_ID FROM XLA_TRANSACTION_ENTITIES_UPG

WHERE ENTITY_ID IN

(

SELECT ENTITY_ID FROM XLA_AE_HEADERS

WHERE GL_TRANSFER_STATUS_CODE=’N’

AND PERIOD_NAME=&PERIOD_NAME

)

)

Note: we also can see un posted entries from XLA_EVENTS table through EVENT_STATUS_CODE=’N’