[Oracle]Qurey Account Details帳戶明細

SELECT APSA.ORG_ID, --ORG_ID
       HCA.CUST_ACCOUNT_ID,
       HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER, --客戶編號
       HP.PARTY_NAME CUSTOMER_NAME, --客戶名稱
       APSA.TRX_NUMBER,
       APSA.INVOICE_CURRENCY_CODE,
       APSA.TRX_DATE, --立帳日期
       APSA.DUE_DATE, --到期日
       APSA.CLASS,
       NVL(CTT.NAME, ARM.NAME) TRANSACTION_TYPE_NAME,
       APSA.STATUS,
       AL.MEANING,
       CRH_CURRENT.STATUS,
       SUM(APSA.AMOUNT_DUE_ORIGINAL) ORIGINAL, --原幣應收
       SUM(APSA.ACCTD_AMOUNT_DUE_REMAINING) AMOUNT --本幣餘額
  FROM APPS.HZ_PARTIES                  HP,
       APPS.HZ_CUST_ACCOUNTS            HCA,
       APPS.AR_PAYMENT_SCHEDULES_ALL    APSA,
       APPS.AR_CASH_RECEIPTS_ALL        CR,
       APPS.AR_RECEIPT_METHODS          ARM,
       APPS.RA_CUSTOMER_TRX_ALL         RCTA,
       APPS.RA_CUST_TRX_TYPES_ALL       CTT,
       APPS.AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,
       APPS.AR_LOOKUPS                  AL
 WHERE APSA.CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
   AND APSA.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
   AND CR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID(+)
   AND APSA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+)
   AND RCTA.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID(+)
   AND APSA.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID(+)
   AND CRH_CURRENT.CURRENT_RECORD_FLAG(+) = 'Y'
   AND HCA.PARTY_ID = HP.PARTY_ID
   AND APSA.STATUS = AL.LOOKUP_CODE
   AND AL.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
   AND APSA.STATUS = 'OP' --未結
      --AND HCA.ACCOUNT_NUMBER = '97290259'
      --AND APSA.ORG_ID = 136
   AND NVL(CTT.NAME, ARM.NAME) LIKE '%暂收订金%'
 GROUP BY APSA.ORG_ID,
          HCA.CUST_ACCOUNT_ID,
          HCA.ACCOUNT_NUMBER,
          HP.PARTY_NAME,
          APSA.TRX_NUMBER,
          APSA.INVOICE_CURRENCY_CODE,
          APSA.TRX_DATE,
          APSA.DUE_DATE,
          APSA.CLASS,
          CTT.NAME,
          ARM.NAME,
          APSA.STATUS,
          AL.MEANING,
          CRH_CURRENT.STATUS,
          ARM.NAME
 ORDER BY APSA.TRX_DATE