Sunday, March 20, 2016

Oracle Receivable Reconciliation Query

SELECT
RCTLGDA.GL_DATE,
RCTA.CREATION_DATE "INVOICE DATE",
HOU.NAME  "LEGAL ENTITY NAME",
RCTA.TRX_NUMBER INVOICE_NUMBER,
HP.PARTY_NAME "CUSTOMER NAME",
RCTLA.DESCRIPTION,
XAL.ACCOUNTED_DR "DR ACCOUNT",
XAL.ACCOUNTED_CR  "CR ACCOUNT",
APSA.AMOUNT_DUE_ORIGINAL "INVOICE TOTAL"
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUSTOMER_TRX_LINES_ALL RCTLA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA,
AR_PAYMENT_SCHEDULES_ALL APSA,
HZ_PARTIES HP,
HZ_CUST_ACCOUNTS_ALL HCAA,
HR_OPERATING_UNITS HOU,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
XLA_TRANSACTION_ENTITIES XlTE,
GL_JE_BATCHES GJB,
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
xla_events XLAE
WHERE 1=1
AND APSA.CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID
AND HP.PARTY_ID=HCAA.PARTY_ID
AND XAL.CODE_COMBINATION_ID=RCTLGDA.CODE_COMBINATION_ID
AND RCTA.CUSTOMER_TRX_ID=RCTLA.CUSTOMER_TRX_ID
AND HOU.ORGANIZATION_ID=RCTA.LEGAL_ENTITY_ID
AND RCTA.BILL_TO_CUSTOMER_ID=HCAA.CUST_ACCOUNT_ID
AND GIR.GL_SL_LINK_TABLE=XAL.GL_SL_LINK_TABLE
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID
AND XAH.EVENT_ID=XLAE.EVENT_ID
AND XLAE.ENTITY_ID=XLTE.ENTITY_ID
AND XLAE.APPLICATION_ID=XLtE.APPLICATION_ID
AND XLtE.TRANSACTION_NUMBER='12028'


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect