Query to get AR Invoice and Receipt Details in Oracle Apps R12

SELECT DISTINCT

  PARTY_NAME PAYER_NAME,

  HZP.PARTY_ID PAYER_ID,

  ACCOUNT_NUMBER,

  (

    SELECT

      MAX(APPLY_DATE)

    FROM

      AR_RECEIVABLE_APPLICATIONS_ALL ARAA

    WHERE

      ARAA.APPLIED_CUSTOMER_TRX_ID =CT.CUSTOMER_TRX_ID

  )

  PAID_DATE,

  (

    SELECT

      SUM(AMOUNT_APPLIED)

    FROM

      AR_RECEIVABLE_APPLICATIONS_ALL ARAA

    WHERE

      ARAA.APPLIED_CUSTOMER_TRX_ID =CT.CUSTOMER_TRX_ID

  )

  PAYMENT_AMOUNT,

  CT.TRX_NUMBER INVOICE_NUMBER,

  CT.TRX_DATE INVOICE_DATE,

  (

    SELECT

      SUM (EXTENDED_AMOUNT)

    FROM

      RA_CUSTOMER_TRX_LINES_ALL

    WHERE

      CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID

    AND LINE_TYPE     = 'TAX'

  )

  TAX_AMOUNT,

  (

    SELECT

      SUM (EXTENDED_AMOUNT)

    FROM

      RA_CUSTOMER_TRX_LINES_ALL

    WHERE

      CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID

    AND LINE_TYPE     = 'LINE'

  )

  INVOICE_AMOUNT,

  PS_INV.AMOUNT_DUE_ORIGINAL,

  ACTLA.LINE_NUMBER,

  ACTLA.DESCRIPTION PRODUCT_PROGRAM_TYPE ,

  ACTLA.QUANTITY_INVOICED INVOICE_QUANTITY ,

  (

    SELECT DISTINCT

      PRIMARY_UNIT_OF_MEASURE

    FROM

      MTL_SYSTEM_ITEMS_B

    WHERE

      PRIMARY_UOM_CODE=ACTLA.UOM_CODE

  )

  UNIT_OF_MEASURE ,

  ACTLA.EXTENDED_AMOUNT CHARGED_LINE_AMOUNT ,

  (

    SELECT

      SUM(AMOUNT)

    FROM

      AR_ACTIVITY_DETAILS AAD

    WHERE

      AAD.CUSTOMER_TRX_LINE_ID=ACTLA.CUSTOMER_TRX_LINE_ID

  )

  PAID_LINE_AMOUNT

FROM

  RA_CUSTOMER_TRX_ALL CT,

  RA_CUSTOMER_TRX_LINES_ALL ACTLA,

  AR_PAYMENT_SCHEDULES_ALL PS_INV,

  HZ_PARTIES HZP,

  HZ_CUST_ACCOUNTS HCA

WHERE

  CT.CUSTOMER_TRX_ID        =ACTLA.CUSTOMER_TRX_ID

AND CT.CUSTOMER_TRX_ID      = PS_INV.CUSTOMER_TRX_ID(+)

AND HZP.PARTY_ID            = HCA.PARTY_ID

AND HCA.CUST_ACCOUNT_ID     = CT.BILL_TO_CUSTOMER_ID

AND ACTLA.LINE_TYPE         = 'LINE'

ORDER BY

  CT.TRX_NUMBER,

  ACTLA.LINE_NUMBER;