Hi Again :)

This time I am here with General Ledger to Fixed Assests Drilldown in R12.

We know that we have all the transactions available in a single table in Fixed Assets Modules and that is 

Tables involved for Journal Entry to FA Transaction drilldown are

Now, when we are given a Journal entry with JE_SOURCE as ‘Assets’ and JE_CATEGORY as ‘Addition’, we got to track its JE_HEADER_ID. Here is the step by step process. 

Step 1: For instance, we are going to track the transaction against the maximum JE_HEADER_ID with Assets source and Addition Category. Here is the query for it;

SELECT MAX(JE_HEADER_ID )

INTO V_JE_HEADER_ID

FROM GL_JE_HEADERS

WHERE JE_SOURCE = ‘Assets’

AND JE_CATEGORY= ‘Addition’;

Step 2: Now we are going to check the corresponding GL_SL_LINK_ID against this JE_HEADER_ID. We can get these link ids through the query,

SELECT GL_SL_LINK_ID

FROM GL_IMPORT_REFERENCES

WHERE JE_HEADER_ID = V_JE_HEADER_ID;

Step 3: And to get to the relevant lines in sub-ledger modules we need to find lines against the identified GL_SL_LINK_IDs from XLA_AE_LINES table. Here is the query to get to the sub-ledger lines

SELECT DISTINCT AE_HEADER_ID

INTO V_AE_HEADER_ID

FROM XLA_AE_LINES

WHERE APPLICATION_ID = 140
AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID

FROM GL_IMPORT_REFERENCES

WHERE JE_HEADER_ID = V_JE_HEADER_ID

);

Step 4: Now we will find ENTITY_ID against the identified header_id. Here is the query,

SELECT ENTITY_ID

INTO V_ENTITY_ID

FROM XLA_AE_HEADERS

WHERE AE_HEADER_ID = V_AE_HEADER_ID
AND APPLICATION_ID = 140;

Step 5: Now, we will get the SOURCE_ID_INT_1 against identified XLA_TRANSACTION to get to the fixed assets transaction.

SELECT SOURCE_ID_INT_1

INTO V_SOURCE_ID

FROM XLA_TRANSACTION_ENTITIES

WHERE ENTITY_ID = V_ENTITY_ID
AND APPLICATION_ID = 140;

Step 6: Now is the last step, we will use this source id as transaction header id to get to the transactions table in fixes assets.

This was all about GL to Fixed Assets Drilldown.

Happy development :)