Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 12 December 2018

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;
Share this article :

3 comments:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Your blog is great. I read a lot of interesting things from it. Thank you very much for sharing. Hope you will update more news in the future. For instant support related to Common QuickBooks Error please contact our technical expert for help related to QuickBooks.

    ReplyDelete