--AP发票
SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,
AIDA.*
FROM GL_JE_HEADERSGJH,
GL_JE_LINESGJL,
GL_IMPORT_REFERENCES T,
XLA_AE_LINESA,
XLA_AE_HEADERSH,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_DISTRIBUTION_LINKS XDL,
AP_INVOICES_ALL AI,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE T.JE_HEADER_ID = 10710
AND T.JE_LINE_NUM = 5
AND GJH.JE_CATEGORY ='Purchase Invoices'
/*AND GJH.NAME= 'MAR- Receiving CNY'
AND GJL.JE_LINE_NUM=1*/
AND GJH.JE_SOURCE ='Payables'
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
AND XTE.ENTITY_ID = H.ENTITY_ID
AND XTE.APPLICATION_ID = H.APPLICATION_ID
--AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
AND A.AE_HEADER_ID = H.AE_HEADER_ID
AND XDL.APPLICATION_ID = A.APPLICATION_ID
AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
AND A.DISPLAYED_LINE_NUMBER > 0
AND XTE.SOURCE_ID_INT_1 = AI.INVOICE_ID
AND XDL.APPLIED_TO_SOURCE_ID_NUM_1 = AI.INVOICE_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID
;
--AP付款
SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,
XTE.SECURITY_ID_INT_1,_ID,XDL.*
FROM GL_JE_HEADERSGJH,
GL_JE_LINESGJL,
GL_IMPORT_REFERENCES T,
XLA_AE_LINESA,
XLA_AE_HEADERSH,
XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA_DISTRIBUTION_LINKS XDL,
AP_CHECKS_ALLAC,
AP_PAYMENT_HIST_DISTS APHD,
AP_PAYMENT_HISTORY_ALL APHA
WHERE T.JE_HEADER_ID = 10658
AND T.JE_LINE_NUM = 2
AND GJH.JE_CATEGORY = 'Payments'
/*AND GJH.NAME= 'MAR- Receiving CNY'
AND GJL.JE_LINE_NUM=1*/
AND GJH.JE_SOURCE ='Payables'
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND T.JE_HEADER_ID = GJL.JE_HEADER_ID
AND T.JE_LINE_NUM = GJL.JE_LINE_NUM
AND XTE.ENTITY_ID = H.ENTITY_ID
AND XTE.APPLICATION_ID = H.APPLICATION_ID
--AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND A.GL_SL_LINK_ID = T.GL_SL_LINK_ID
AND A.GL_SL_LINK_TABLE = T.GL_SL_LINK_TABLE
AND A.AE_HEADER_ID = H.AE_HEADER_ID
AND XDL.APPLICATION_ID = A.APPLICATION_ID
AND XDL.AE_HEADER_ID = A.AE_HEADER_ID
AND XDL.AE_LINE_NUM = A.AE_LINE_NUM
AND A.DISPLAYED_LINE_NUMBER > 0
AND XTE.SOURCE_ID_INT_1 = AC.CHECK_ID
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = APHD.PAYMENT_HIST_DIST_ID
AND APHA.PAYMENT_HISTORY_ID = APHD.PAYMENT_HISTORY_ID
AND APHA.CHECK_ID = AC.CHECK_ID
;