[Home] [Help]
View: IBE_PAYMENT_HEADER_V
View Text
SELECT CR.CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER
, ACCT.CUST_ACCOUNT_ID
, CUST.PARTY_NAME
, CR.AMOUNT
, AL_PAY_TYPE.MEANING TYPE
, ABS(PS.AMOUNT_APPLIED)
, CR.RECEIPT_DATE
, PS.DUE_DATE
, CR.CURRENCY_CODE CURRENCY_CODE
, SUM(DECODE(RA.STATUS
, 'UNAPP'
, NVL(RA.AMOUNT_APPLIED
, 0)
, 0)) UNAPPLIED_AMOUNT
, CUST.PARTY_ID PARTY_ID
, CR.CREATED_BY
, PS.ORG_ID ORG_ID
FROM AR_LOOKUPS AL_PAY_TYPE
, HZ_CUST_ACCOUNTS ACCT
, HZ_PARTIES CUST
, AR_PAYMENT_SCHEDULES_ALL PS
, AR_RECEIVABLE_APPLICATIONS_ALL RA
, AR_CASH_RECEIPTS_ALL CR
WHERE CR.PAY_FROM_CUSTOMER = ACCT.CUST_ACCOUNT_ID (+)
AND ACCT.PARTY_ID = CUST.PARTY_ID
AND PS.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CR.CASH_RECEIPT_ID = RA.CASH_RECEIPT_ID(+)
AND AL_PAY_TYPE.LOOKUP_TYPE = 'PAYMENT_CATEGORY_TYPE'
AND CR.TYPE = AL_PAY_TYPE.LOOKUP_CODE GROUP BY CR.CASH_RECEIPT_ID
, CR.RECEIPT_NUMBER
, ACCT.CUST_ACCOUNT_ID
, CUST.PARTY_NAME
, CR.AMOUNT
, AL_PAY_TYPE.MEANING
, PS.AMOUNT_APPLIED
, CR.CURRENCY_CODE
, CR.RECEIPT_DATE
, PS.DUE_DATE
, CUST.PARTY_ID
, CR.CREATED_BY
, PS.ORG_ID
Columns
Name |
CASH_RECEIPT_ID |
RECEIPT_NUMBER |
CUST_ACCOUNT_ID |
CUSTOMER_NAME |
AMOUNT |
TYPE |
APPLIED_AMOUNT |
RECEIPT_DATE |
DUE_DATE |
CURRENCY_CODE |
UNAPPLIED_AMOUNT |
PARTY_ID |
CREATED_BY |
ORG_ID |