1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Receipts applied to invoices

Discussion in 'Oracle Financials' started by VSS, Sep 1, 2016.

  1. VSS

    VSS Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    USA
    I'm trying to create a query where I can have specific customer, their transactions, and invoices applied to these invoices. Below is the same query, but not sure if I'm missing any logic as to produce incorrect data:

    Code (SQL):
    SELECT HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
      HZP.PARTY_NAME CUSTOMER_NAME,
      RCT.TRX_DATE,
      PSA.TRX_NUMBER,
      PSA.AMOUNT_DUE_ORIGINAL,
      PSA.AMOUNT_DUE_REMAINING,
      PSA.AMOUNT_APPLIED
    FROM APPS.HZ_PARTIES HZP,
      APPS.HZ_CUST_ACCOUNTS HCA,
      APPS.RA_CUSTOMER_TRX_ALL RCT,
      APPS.RA_CUSTOMER_TRX_LINES_ALL RCTLA,
      APPS.AR_PAYMENT_SCHEDULES_ALL PSA
    WHERE 1                   = 1
    AND HZP.PARTY_ID          = HCA.PARTY_ID
    AND RCTLA.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
    AND RCTLA.CUSTOMER_TRX_ID = PSA.CUSTOMER_TRX_ID
    AND RCT.CUSTOMER_TRX_ID   = PSA.CUSTOMER_TRX_ID
    AND PSA.TRX_NUMBER        = RCT.TRX_NUMBER
    AND HCA.ACCOUNT_NUMBER    = '7577'
    AND
      (
        HCA.CUST_ACCOUNT_ID  = RCT.BILL_TO_CUSTOMER_ID
      OR HCA.CUST_ACCOUNT_ID = RCT.SHIP_TO_CUSTOMER_ID
      )
    AND RCT.TRX_DATE BETWEEN TRUNC(TO_DATE ('2016/01/01', 'yyyy/mm/dd'))
      AND TRUNC( TO_DATE ('2016/03/31', 'yyyy/mm/dd'))
    --AND PSA.AMOUNT_DUE_REMAINING = 0
    AND PSA.AMOUNT_APPLIED <>0
    GROUP BY HZP.PARTY_NAME,
      HCA.ACCOUNT_NUMBER,
      RCT.TRX_DATE,
      PSA.TRX_NUMBER,
      PSA.AMOUNT_DUE_ORIGINAL,
      PSA.AMOUNT_DUE_REMAINING,
      PSA.AMOUNT_APPLIED;