General Ledger - Accounts Payables Drill Down - Data Base

General Ledger - Accounts Payables Drill Down - Data Base Query


As Part of Regular GL , Accounts Payables Designing and Troubleshooting, It is required to have a good Understanding of the Links between Various General Ledger and Accounts Payables Table involving Sub-ledger accounting tables, Below is a select statement which provides you the link between GENERAL LEDGER, ACCOUNTS PAYABLES AND  SUB-LEDGER ACCOUNTING Data base Tables

select --distinct aia.invoice_num
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAH.GL_TRANSFER_STATUS_CODE,XDL.ROUNDING_CLASS_CODE,XDL.APPLIED_TO_ENTITY_CODE
,AIA.INVOICE_ID,AIA.INVOICE_NUM,AIA.INVOICE_AMOUNT,AIL.LINE_NUMBER,AIL.LINE_TYPE_LOOKUP_CODE,AIL.AMOUNT,GL.CURRENCY_CODE "MRC CURRENCY",GLL.ACCOUNTED_DR,GLL.ACCOUNTED_CR
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AID,
APPS.AP_INVOICE_LINES_ALL AIL,
APPS.AP_INVOICES_ALL AIA
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Payable%')
AND AIA.INVOICE_NUM LIKE 'EXP360319%'
AND GL.LEDGER_CATEGORY_CODE NOT IN ('ALC','SECONDARY')
order by aia.invoice_num;