EBS:AR应收账款、贷项通知单或收款核销 查询

-- 核销表  FROM "AR"."AR_RECEIVABLE_APPLICATIONS_ALL"
-- APPS.AR_RECEIVABLE_APPLICATIONS_V 
SELECT ct.trx_number as "应收单号",
  ( SELECT  CR.receipt_number FROM APPS.AR_CASH_RECEIPTS_ALL CR
    WHERE CR.CASH_RECEIPT_ID = ARA.CASH_RECEIPT_ID 
    AND ARA.APPLICATION_TYPE = 'CASH'
   ) AS "收款单",
   ARA.AMOUNT_APPLIED AS "核销金额", 
   ( SELECT  ctCM.trx_number FROM AR.Ra_Customer_Trx_All CTCM
    WHERE CTCM.CUSTOMER_TRX_ID = ARA.CUSTOMER_TRX_ID  
    AND ARA.APPLICATION_TYPE = 'CM' 
   ) AS "应收-贷项单",
  ara.*
  FROM --APPS.AR_CASH_RECEIPTS_ALL CR, 
   APPS.AR_RECEIVABLE_APPLICATIONS_ALL  ARA  ,  -- 收款核销
   AR.Ra_Customer_Trx_All ct  -- 应收账款
WHERE 1=1
-- AND CR.CASH_RECEIPT_ID = ARA.CASH_RECEIPT_ID
-- AND CR.SET_OF_BOOKS_ID = ARA.SET_OF_BOOKS_ID
 AND ARA.CREATION_DATE> TO_DATE('2021-01-01','YYYY-MM-DD')
 AND ARA.ORG_ID = 719  
 and ARA.APPLIED_CUSTOMER_TRX_ID =  ct.CUSTOMER_TRX_ID
 and ara.org_id = ct.ORG_ID 
 and ct.trx_number like '10220000746163'  -- 应该账款单号 
 AND ARA.SET_OF_BOOKS_ID = 2331 --  销售有限公司