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 :

0 comments:

Post a Comment