oracle-EBS-PL/sql 财务 AR AP 子分类账未过入总账GL的语句

再月结之前，除了跑ar ap的请求 子分类账区间例外报表外， 我们可以用下面sql语句，去捞取哪些数据在ar ap 还没有完全过入总账的情况，防止前端总分类账和总账不一致

注意下文中4个sql里的日期，都是指总账日期，事务处理日期。比如查询3月份未入GL

1.AR事务处理

--ar 事务处理
 select hou.SHORT_CODE,rcta.TRX_NUMBER,rcta.org_id,TO_CHAR(trx_date,'yyyy-mm-dd') trx_date,rcta.INTERFACE_HEADER_ATTRIBUTE1, xte.ENTITY_CODE,xah.GL_TRANSFER_STATUS_CODE,xah.ACCOUNTING_ENTRY_STATUS_CODE
,Xe.Event_Type_Code, Xe.Event_Status_Code,Xe.Process_Status_Code,rcta.INTERFACE_HEADER_ATTRIBUTE4
from ra_customer_trx_all rcta,xla.xla_transaction_entities xte,Xla_Events Xe,xla_ae_headers xah ,hr_operating_units hou
        where
       rcta.customer_trx_id = Xte.source_id_int_1(+)
   and rcta.org_id = Xte.security_id_int_1(+) 
   And Xte.Entity_Id = Xe.Entity_Id(+)
   And Xte.Application_Id = Xe.Application_Id(+)
   And Xe.Event_Id = Xah.Event_Id (+)
   And Xe.Application_Id= Xah.Application_Id (+)
   --and rcta.TRX_NUMBER in( '10209142','10209142' )
   and rcta.org_id = hou.ORGANIZATION_ID  
   and hou.SHORT_CODE in( 321333)
  -- and trunc(trx_date) between to_date('20200301', 'RRRRMMDD') and  to_date('20200331', 'RRRRMMDD') 
   and nvl(xah.GL_TRANSFER_STATUS_CODE,'N') <>'Y' 
   and TO_CHAR(trx_date,'yyyy-mm-dd') >='2021-03-01'  and TO_CHAR(trx_date,'yyyy-mm-dd') <='2021-03-31'

2.AR收款

--ar 收款
 select hou.SHORT_CODE , to_char(xe.CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS') CREATION_DATE,
 TO_CHAR( Xe.EVENT_DATE,'yyyy-mm-dd') EVENT_DATE
,CASH_RECEIPT_ID,acra.RECEIPT_NUMBER,acra.STATUS,acra.org_id,acra.RECEIPT_DATE,
TO_CHAR( acra.EXCHANGE_DATE,'yyyy-mm-dd') EXCHANGE_DATE , xte.ENTITY_CODE,xah.GL_TRANSFER_STATUS_CODE,xah.ACCOUNTING_ENTRY_STATUS_CODE
,Xe.Event_Type_Code, Xe.Event_Status_Code,Xe.Process_Status_Code
from ar_cash_receipts_all acra,xla.xla_transaction_entities xte,Xla_Events Xe,xla_ae_headers xah ,hr_operating_units hou where
       acra.cash_receipt_id = Xte.source_id_int_1(+)
   and acra.org_id = Xte.security_id_int_1(+) 
   And Xte.Entity_Id = Xe.Entity_Id(+)
   And Xte.Application_Id = Xe.Application_Id(+)
   And Xe.Event_Id = Xah.Event_Id (+)
   And Xe.Application_Id= Xah.Application_Id (+)
   --and Xte.ENTITY_ID =  xah.ENTITY_ID(+)
   --and acra.RECEIPT_NUMBER in  ('Q1720131011','P1701216001','')
   and acra.org_id  = hou.ORGANIZATION_ID  
   and hou.SHORT_CODE  in ( 3213123)
   and nvl(xah.GL_TRANSFER_STATUS_CODE,'N') <>'Y' 
   --and TO_CHAR(Xe.EVENT_DATE,'yyyy-mm-dd') >='2020-03-01'  and TO_CHAR(Xe.EVENT_DATE,'yyyy-mm-dd') <='2020-03-31'
  -- and trunc(Xe.EVENT_DATE) between  to_date('20200301', 'RRRRMMDD')  and  to_date('20200331', 'RRRRMMDD')
   and trunc(acra.EXCHANGE_DATE) between  to_date('20210101', 'RRRRMMDD')  and  to_date('20210131', 'RRRRMMDD')

3.AP发票

--ap 发票
 select  hou.SHORT_CODE, TO_CHAR( ai.GL_DATE,'yyyy-mm-dd')GL_DATE, apba.BATCH_NAME,ai.INVOICE_ID,ai.INVOICE_NUM,ai.INVOICE_AMOUNT,ai.org_id,ai.INVOICE_DATE,PAYMENT_STATUS_FLAG,APPROVAL_READY_FLAG, xte.ENTITY_CODE,xah.GL_TRANSFER_STATUS_CODE,xah.ACCOUNTING_ENTRY_STATUS_CODE
,Xe.Event_Type_Code, Xe.Event_Status_Code,Xe.Process_Status_Code,Xte.Entity_Id,Xte.Application_Id,xe.EVENT_DATE
from AP_BATCHES_ALL apba,AP_INVOICES_ALL ai,xla.xla_transaction_entities xte,Xla_Events Xe,xla_ae_headers xah ,hr_operating_units hou where
      ai.BATCH_ID =apba.BATCH_ID (+)
       -- ai.SET_OF_BOOKS_ID = xte.LEDGER_ID(+)
   and  ai.org_id = Xte.security_id_int_1(+)
   and ai.INVOICE_ID = xte.SOURCE_ID_INT_1(+)
   And Xte.Entity_Id = Xe.Entity_Id(+)
   And Xte.Application_Id = Xe.Application_Id(+)
   And Xe.Event_Id = Xah.Event_Id (+)
   And Xe.Application_Id= Xah.Application_Id (+)
   --and Xte.ENTITY_ID =  xah.ENTITY_ID(+)
   --and ai.INVOICE_NUM in( '30000317','30000317' )
   --and BATCH_NAME = '1232133'
   and ai.org_id = hou.ORGANIZATION_ID  
   and hou.SHORT_CODE in ( 232333)
   and nvl(xah.GL_TRANSFER_STATUS_CODE,'N') <>'Y'   
   --and xte.ENTITY_CODE <> 'AP_INVOICES'
   and trunc(ai.GL_DATE) between  to_date('20210101', 'RRRRMMDD') and  to_date('20210131', 'RRRRMMDD')

4.AP付款

 --ap 付款
 SELECT  hou.SHORT_CODE, ai.INVOICE_ID,ai.INVOICE_NUM,ai.INVOICE_AMOUNT,ai.org_id,ai.INVOICE_DATE,TO_CHAR( aip.ACCOUNTING_DATE,'yyyy-mm-dd') ACCOUNTING_DATE,PAYMENT_STATUS_FLAG,APPROVAL_READY_FLAG, 
ac.check_id,ac.check_number,ac.DOC_SEQUENCE_VALUE apvoucherNumber , aip.AMOUNT 明细付款金额 , ac.AMOUNT 总付款金额,
xte.ENTITY_CODE,xte.TRANSACTION_NUMBER ,xah.GL_TRANSFER_STATUS_CODE,xah.ACCOUNTING_ENTRY_STATUS_CODE
,Xe.Event_Type_Code, Xe.Event_Status_Code,Xe.Process_Status_Code
FROM AP_INVOICES_ALL ai,
  AP_INVOICE_PAYMENTS_ALL aip,
  AP_CHECKS_ALL ac,
  xla.xla_transaction_entities xte,
  xla_events xe,
  XLA_AE_HEADERS xah ,hr_operating_units hou
WHERE  ac.org_id = xte.security_id_int_1(+)
   and ac.check_id= xte.SOURCE_ID_INT_1(+)
   And xte.Entity_Id = Xe.Entity_Id(+)
   And xte.Application_Id = Xe.Application_Id(+)
   And Xe.Event_Id = Xah.Event_Id (+)
   And Xe.Application_Id= Xah.Application_Id (+)
AND ai.INVOICE_ID        =aip.INVOICE_ID(+)
AND aip.CHECK_ID         =ac.CHECK_ID(+)
--and xte.ENTITY_CODE='AP_PAYMENTS'
--AND ac.CHECK_NUMBER = 10000186
and ai.org_id = hou.ORGANIZATION_ID  
   and hou.SHORT_CODE in ( 12332133)
and xte.Application_Id=200
and trunc(aip.ACCOUNTING_DATE)between  to_date('20210131', 'RRRRMMDD') and  to_date('20210131', 'RRRRMMDD')
and nvl(xah.GL_TRANSFER_STATUS_CODE,'N') <>'Y'