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
- FA_TRANSACTION_HEADERS
Tables involved for Journal Entry to FA Transaction drilldown are
- GL_JE_HEADERS
- GL_IMPORT_REFERENCES
- XLA_AE_LINES
- XLA_AE_HEADERS
- XLA_TRANSACTION_ENTITIES
- FA_TRANSACTION_HEADERS
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
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;
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;
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.
FROM FA_TRANSACTION_HEADERS
WHERE TRANSACTION_HEADER_ID = V_SOURCE_ID;
This was all about GL to Fixed Assets Drilldown.
Happy development :)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22816976/viewspace-2134868/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22816976/viewspace-2134868/