总账分录追溯发票或者付款

--总账分录追溯发票
SELECT DISTINCT AIA.*
  FROM AP_AE_HEADERS_ALL AAH,         --帐户分录头表
       AP_AE_LINES_ALL   AAL,         --账户分录行表，每一个会计事件都会在此表中产生会计分录
       
       GL_JE_BATCHES GJB,
       GL_JE_HEADERS GJH,
       GL_JE_LINES   GJL,
       
       AP_INVOICES_ALL          AIA,
       AP_ACCOUNTING_EVENTS_ALL AAE     --会计帐户事件表，一种能产生帐户的事务处理活动, 如:发票调整, 预付款, 付款等. 
WHERE 1 = 1
   AND AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
      
   AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID --关键点
   AND GJH.JE_SOURCE = 'Payables'
      
   AND AIA.INVOICE_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_INVOICES'
   AND AAH.ACCOUNTING_EVENT_ID = AAE.ACCOUNTING_EVENT_ID --关键点
      
   AND GJB.NAME = '3292 应付款 1047558: A 29786'

 

--应付总账分录追溯到付款
SELECT DISTINCT ACA.*
               
  FROM AP_AE_HEADERS_ALL        AAH     --帐户分录头表
      ,AP_AE_LINES_ALL          AAL     --账户分录行表，每一个会计事件都会在此表中产生会计分录
      
      ,GL_JE_BATCHES            GJB
      ,GL_JE_HEADERS            GJH
      ,GL_JE_LINES              GJL
       
      ,AP_CHECKS_ALL            ACA     --付款单据表，存储支付给供应商的付款
      ,AP_ACCOUNTING_EVENTS_ALL AAE     --会计帐户事件表，一种能产生帐户的事务处理活动, 如:发票调整, 预付款, 付款等.
      ,AP_INVOICE_PAYMENTS_ALL  AIP     --发票付款记录，此表连接付款(CHECK_ID)信息到其相对应的发票.
 WHERE AAH.AE_HEADER_ID = AAL.AE_HEADER_ID
   
   AND GJB.JE_BATCH_ID = GJH.JE_BATCH_ID
   AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
   AND GJL.GL_SL_LINK_ID = AAL.GL_SL_LINK_ID
   AND GJH.JE_SOURCE = 'Payables'
   
   AND ACA.CHECK_ID = AAE.SOURCE_ID
   AND AAE.SOURCE_TABLE = 'AP_CHECKS'
   AND AAE.ACCOUNTING_EVENT_ID = AAH.ACCOUNTING_EVENT_ID
      
   AND AIP.CHECK_ID = ACA.CHECK_ID
   AND GJH.NAME = '<5021AP040600002>付款 CNY'

 

转载于:https://www.cnblogs.com/rigid/p/4047926.html