oracle凭证编号,R12 AP模块的发票过账后如何关联对应的凭证编号

--AP发票

SELECT GJH.NAME,GJH.LEDGER_ID,GJH.JE_CATEGORY, GJH.JE_SOURCE,XDL.SOURCE_DISTRIBUTION_TYPE, XTE.ENTITY_CODE,

AIDA.*

FROM GL_JE_HEADERS                GJH,

GL_JE_LINES                  GJL,

GL_IMPORT_REFERENCES         T,

XLA_AE_LINES                 A,

XLA_AE_HEADERS               H,

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-2011 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,AC.ORG_ID,XDL.*

FROM GL_JE_HEADERS                GJH,

GL_JE_LINES                  GJL,

GL_IMPORT_REFERENCES         T,

XLA_AE_LINES                 A,

XLA_AE_HEADERS               H,

XLA.XLA_TRANSACTION_ENTITIES XTE,

XLA_DISTRIBUTION_LINKS       XDL,

AP_CHECKS_ALL                AC,

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

;