View: PSA_MF_BALANCES_VIEW

Product:	PSA - Public Sector Financials
Description:	Shows the balances per Transaction Distribution
Implementation/DBA Data:	APPS.PSA_MF_BALANCES_VIEW

View Text

SELECT B.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, B.DIST CUST_TRX_LINE_GL_DIST_ID
, B.ADO AMOUNT_DUE_ORIGINAL
, B.AAP AMOUNT_APPLIED
, B.AAJ AMOUNT_ADJUSTED
, B.ACR AMOUNT_CREDITED
, B.ADS AMOUNT_DISCOUNTED
, B.ADO-B.AAP+B.AAJ+B.ACR-B.ADS AMOUNT_DUE_REMAINING
FROM (SELECT A.CUSTOMER_TRX_ID
, A.CUST_TRX_LINE_GL_DIST_ID DIST
, (SELECT NVL(AMOUNT
, 0)
FROM RA_CUST_TRX_LINE_GL_DIST_ALL CTLGD
WHERE CTLGD.CUST_TRX_LINE_GL_DIST_ID = A.CUST_TRX_LINE_GL_DIST_ID ) ADO
, (SELECT NVL(SUM (RCT_DIST.AMOUNT)
, 0)
FROM PSA_MF_RCT_DIST_ALL RCT_DIST
, AR_RECEIVABLE_APPLICATIONS_ALL RCV_APP
WHERE RCT_DIST.CUST_TRX_LINE_GL_DIST_ID = A.CUST_TRX_LINE_GL_DIST_ID
AND RCT_DIST.RECEIVABLE_APPLICATION_ID = RCV_APP.RECEIVABLE_APPLICATION_ID
AND RCV_APP.CASH_RECEIPT_ID IS NOT NULL ) AAP
, (SELECT NVL(SUM (AMOUNT)
, 0)
FROM PSA_MF_ADJ_DIST_ALL ADJ_DIST
WHERE ADJ_DIST.CUST_TRX_LINE_GL_DIST_ID = A.CUST_TRX_LINE_GL_DIST_ID ) AAJ
, (SELECT NVL(SUM (RCT_DIST.AMOUNT)
, 0)
FROM PSA_MF_RCT_DIST_ALL RCT_DIST
, AR_RECEIVABLE_APPLICATIONS_ALL RCV_APP
WHERE RCT_DIST.CUST_TRX_LINE_GL_DIST_ID = A.CUST_TRX_LINE_GL_DIST_ID
AND RCT_DIST.RECEIVABLE_APPLICATION_ID = RCV_APP.RECEIVABLE_APPLICATION_ID
AND RCV_APP.CASH_RECEIPT_ID IS NULL ) ACR
, (SELECT NVL(SUM (NVL(DISCOUNT_AMOUNT
, 0) + NVL(UE_DISCOUNT_AMOUNT
, 0))
, 0)
FROM PSA_MF_RCT_DIST_ALL RCT_DIST
, AR_RECEIVABLE_APPLICATIONS_ALL RCV_APP
WHERE RCT_DIST.CUST_TRX_LINE_GL_DIST_ID = A.CUST_TRX_LINE_GL_DIST_ID
AND RCT_DIST.RECEIVABLE_APPLICATION_ID = RCV_APP.RECEIVABLE_APPLICATION_ID ) ADS
FROM RA_CUST_TRX_LINE_GL_DIST_ALL A
WHERE A.ACCOUNT_CLASS <> 'REC') B

Columns