General Ledger to Receivables Drilldown

Hi Again :)

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

We know that we have two types of transactions in Receivables

•        Transaction / Invoices
•          Receipts

Let’s start with Transaction / Invoices.

Tables involved for Journal Entry to Invoice drilldown are

•         GL_JE_HEADERS
•         GL_IMPORT_REFERENCES
•          XLA_AE_LINES
•         XLA_AE_HEADERS
•         XLA_TRANSACTION_ENTITIES
•         RA_CUSTOMER_TRX_ALL

Now, when we are given a Journal entry with JE_SOURCE as ‘Receivables’ and JE_CATEGORY as ‘Sales Invoices’, 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 invoice against the maximum JE_HEADER_ID with Receivables source and Purchase Invoices Category. Here is the query for it;

SELECT MAX(JE_HEADER_ID )

INTO V_JE_HEADER_ID

FROM GL_JE_HEADERS

WHERE JE_SOURCE = ‘Receivables’

AND JE_CATEGORY= ‘Sales Invoices’;

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 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;

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

SELECT SOURCE_ID_INT_1

INTO V_SOURCE_ID

FROM XLA_TRANSACTION_ENTITIES

WHERE ENTITY_ID = V_ENTITY_ID;

Step 6: Now is the last step, we will use this source id as invoice id to get to the invoice in Receivables.

SELECT *

FROM RA_CUSTOMER_TRX_ALL

WHERE CUSTOMER_TRX_ID = V_SOURCE_ID;

So, here is the drilldown from General Ledger to Receivables Invoices. We got to change in step 1 for JE_CATEGORY as ‘Receipts’ and the query becomes

SELECT MAX(JE_HEADER_ID )

INTO V_JE_HEADER_ID

FROM GL_JE_HEADERS

WHERE JE_SOURCE = ‘Receivables’

AND JE_CATEGORY= ‘Receipts’;

 And in step 6, we got to find check details against the identified SOURCE_ID, which makes the SQL as

SELECT *

FROM AR_CASH_RECEIPTS_ALL

WHERE CASH_RECEIPT_ID = V_SOURCE_ID;

This was all about GL to Receivables Drilldown.

Happy development :)

Feel free to contact me at sana.sagheer@gmail.com for any related queries.