oracle-EBS-PL/sql AR 收款的核销明细

财务在做ar收款后，有时候需要查看此收款已经核销了哪些单据，如果界面上一个个去找，非常的不方便，此时，可以用下面sql来做报表，查看每个收款单号，或者订单号等的收款情况。

 SELECT 
    CR.CASH_RECEIPT_ID  ,
    --PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID ,
    hou.SHORT_CODE ,
    CR.RECEIPT_NUMBER RECEIPT_NUMBER  ,
    CR.AMOUNT,PS_INV_H.AMOUNT_DUE_REMAINING  REMAINING_H,
    CR.CURRENCY_CODE,
    CR.STATUS,
    CR.TYPE,
    CR.RECEIPT_DATE,
    SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME ,
    CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER ,
    APP.APPLICATION_REF_NUM ,
    CT.TRX_DATE TRX_DATE ,
    PS_INV.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL ,
    CT.PURCHASE_ORDER PURCHASE_ORDER ,
    DECODE(ps_inv.payment_schedule_id,  -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK'), ps_inv.trx_number) trx_number ,
    APP.AMOUNT_APPLIED AMOUNT_APPLIED ,
    NVL(APP.AMOUNT_APPLIED_FROM, APP.AMOUNT_APPLIED) AMOUNT_APPLIED_FROM ,
    TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID),-1,NULL, PS_INV.AMOUNT_DUE_REMAINING)) AMOUNT_DUE_REMAINING ,
    APP.APPLY_DATE APPLY_DATE ,
    APP.GL_DATE GL_DATE ,
    DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1,TO_DATE(NULL), PS_INV.DUE_DATE) DUE_DATE ,
    APP.GL_POSTED_DATE GL_POSTED_DATE ,
    PS_INV.STATUS STATUS ,
    PS_INV.TERM_ID TERM_ID ,
    DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1,NULL, L_CLASS.MEANING) TRX_CLASS_NAME ,
    PS_INV.CLASS TRX_CLASS_CODE ,
    CTT.NAME TRX_TYPE_NAME ,
    CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID ,
    DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1,  PS_INV.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE ,
    PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL ,
    TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1, NULL, PS_INV.ACCTD_AMOUNT_DUE_REMAINING)) ACCTD_AMOUNT_DUE_REMAINING ,
    APP.ACCTD_AMOUNT_APPLIED_TO ACCTD_AMOUNT_APPLIED_TO ,
    APP.ACCTD_AMOUNT_APPLIED_FROM ACCTD_AMOUNT_APPLIED_FROM ,  
    BS.NAME TRX_BATCH_SOURCE_NAME ,
    PS_INV.TAX_ORIGINAL TAX_ORIGINAL ,
    APP.APPLIED_REC_APP_ID 
  FROM 
    AR_RECEIVABLE_APPLICATIONS_ALL APP ,
    AR_CASH_RECEIPTS_ALL CR ,
    AR_PAYMENT_SCHEDULES_all PS_INV ,
    AR_PAYMENT_SCHEDULES_all PS_INV_H, --对应总收款的header
    HZ_CUST_ACCOUNTS CUST ,
    HZ_PARTIES PARTY ,
    RA_CUSTOMER_TRX_all CT ,
    RA_CUST_TRX_TYPES_all CTT ,
    RA_CUSTOMER_TRX_LINES_all CTL ,
    RA_BATCH_SOURCES_all BS ,
    HZ_CUST_SITE_USES_all SU ,
    AR_CONS_INV CI ,
    AR_LOOKUPS L_CLASS ,
    AR_RECEIVABLES_TRX ART,
    hr_operating_units hou
  WHERE APP.DISPLAY                   = 'Y'
  and hou.ORGANIZATION_ID =CR.org_id
  AND APP.CASH_RECEIPT_ID             = CR.CASH_RECEIPT_ID
  AND CT.CUSTOMER_TRX_ID(+)           = PS_INV.CUSTOMER_TRX_ID
  AND BS.BATCH_SOURCE_ID (+)          = CT.BATCH_SOURCE_ID
  AND CTT.CUST_TRX_TYPE_ID(+)         = PS_INV.CUST_TRX_TYPE_ID
  AND CUST.CUST_ACCOUNT_ID(+)         = PS_INV.CUSTOMER_ID
  AND CUST.PARTY_ID                   = PARTY.PARTY_ID(+)
  AND SU.SITE_USE_ID(+)               = PS_INV.CUSTOMER_SITE_USE_ID
  AND CTL.CUSTOMER_TRX_LINE_ID(+)     = APP.APPLIED_CUSTOMER_TRX_LINE_ID
  AND PS_INV.CLASS                    = L_CLASS.LOOKUP_CODE
  AND L_CLASS.LOOKUP_TYPE             = 'INV/CM'
  AND CI.CONS_INV_ID(+)               = PS_INV.CONS_INV_ID
  AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
  AND ART.RECEIVABLES_TRX_ID(+)       = APP.RECEIVABLES_TRX_ID
  and APP.CASH_RECEIPT_ID =  PS_INV_H.CASH_RECEIPT_ID and APP.org_id = PS_INV_H.org_id
  --and CUST.ACCOUNT_NUMBER in ( 10007010)
  and CR.RECEIPT_NUMBER in  ('A19061907','','')
 -- and DECODE(ps_inv.payment_schedule_id,  -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK'), ps_inv.trx_number)  = 'P170602006'
  --and hou.SHORT_CODE in ( 111 )
  and APP.GL_DATE  >=to_date('20180501', 'RRRRMMDD')