Query Refund R12 เชื่อมโยง ตารางจาก GL -> AR -> AP

Query Refund R12 เชื่อมโยง ตารางจาก GL -> AR -> AP

งบ

SELECT XTE.SOURCE_ID_INT_1,GLH.DOC_SEQUENCE_ID,GLH.DOC_SEQUENCE_VALUE,GLH.DESCRIPTION “คำอธิบาย GL”,GLL.CODE_COMBINATION_ID “ACC GL”,GCC.SEGMENT1 “ผังบัญชี GL” ,GLL.ACCOUNTED_DR,GLL.ACCOUNTED_CR,APP.CODE_COMBINATION_ID “ACC AR”,GCC_AR.SEGMENT1 “ผังบัญชี AR”,CR.CASH_RECEIPT_ID,CR.RECEIPT_NUMBER,CR.COMMENTS “คำอธิบาย AR”,SUBSTRB (PARTY.PARTY_NAME, 1, 55) CUSTOMER_NAME,APP.ATTRIBUTE1,APP.ATTRIBUTE2,AID.DIST_CODE_COMBINATION_ID “ACC AP”,GCC_AP.SEGMENT1,AI.INVOICE_NUM,AI.DESCRIPTION “คำอธิบาย AP”,AID.AMOUNT

FROM GL_JE_BATCHES GLB

,GL_JE_HEADERS GLH

,GL_JE_LINES GLL

,GL_CODE_COMBINATIONS GCC

,GL_CODE_COMBINATIONS GCC_AR

,GL_CODE_COMBINATIONS GCC_AP

,GL.GL_PERIODS GLP

,GL.GL_IMPORT_REFERENCES IMP

,XLA.XLA_AE_LINES XAL

,XLA_AE_HEADERS XAH

,XLA.XLA_EVENTS XE

,XLA.XLA_TRANSACTION_ENTITIES XTE

,AR_CASH_RECEIPTS_ALL CR

,HZ_PARTIES PARTY

,HZ_CUST_ACCOUNTS CUST

,AR_RECEIVABLE_APPLICATIONS_ALL APP

,AP_INVOICES_ALL AI

,AP_INVOICE_DISTRIBUTIONS_ALL AID

WHERE GLB.JE_BATCH_ID = GLH.JE_BATCH_ID

AND GLB.ACTUAL_FLAG = ‘A’

AND GLB.STATUS = ‘P’

AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID

AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GLH.PERIOD_NAME = GLP.PERIOD_NAME

AND GLP.ADJUSTMENT_PERIOD_FLAG <> ‘Y’

AND GLL.JE_HEADER_ID = IMP.JE_HEADER_ID

AND GLL.JE_LINE_NUM = IMP.JE_LINE_NUM

AND IMP.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

AND IMP.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

AND XAL.APPLICATION_ID = XAH.APPLICATION_ID

AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID

AND XAL.DISPLAYED_LINE_NUMBER > 0

AND XAH.APPLICATION_ID = XE.APPLICATION_ID

AND XAH.EVENT_ID = XE.EVENT_ID

AND XE.APPLICATION_ID = XTE.APPLICATION_ID

AND XE.ENTITY_ID = XTE.ENTITY_ID

AND XTE.APPLICATION_ID = 222

AND XTE.ENTITY_CODE = ‘RECEIPTS’

AND XTE.SOURCE_ID_INT_1 = CR.CASH_RECEIPT_ID

AND CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID

AND CUST.PARTY_ID = PARTY.PARTY_ID

AND CR.CASH_RECEIPT_ID = APP.CASH_RECEIPT_ID

AND APP.DISPLAY = ‘Y’

AND APP.STATUS = ‘ACTIVITY’

AND APP.CODE_COMBINATION_ID = GCC_AR.CODE_COMBINATION_ID

AND APP.APPLICATION_REF_ID = AI.INVOICE_ID

AND AI.INVOICE_ID = AID.INVOICE_ID

AND AI.ACCTS_PAY_CODE_COMBINATION_ID = GCC_AP.CODE_COMBINATION_ID

— AND CR.RECEIPT_NUMBER IN (‘2620103129',’2633400021',’2620104896',’2620104841',’2620100222',’260021817',’2630100044')

— AND CR.RECEIPT_NUMBER = ‘260020134'

— AND GCC.SEGMENT1 = ‘920000000005'

— AND APP.ATTRIBUTE1 = ‘210601010001'

ORDER BY 3 DESC;