VIEW: APPS.PSA_MF_BALANCES_VIEW

Source

View Text - Preformatted

SELECT b.customer_trx_id customer_trx_id, b.dist cust_trx_line_gl_dist_id, b.customer_trx_line_id, b.line_number, 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, d.customer_trx_line_id, d.line_number, (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, ra_customer_trx_lines_all d where a.account_class <> 'REC' and a.customer_trx_line_id = d.customer_trx_line_id) b

View Text - HTML Formatted

SELECT B.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, B.DIST CUST_TRX_LINE_GL_DIST_ID
, B.CUSTOMER_TRX_LINE_ID
, B.LINE_NUMBER
, 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
, D.CUSTOMER_TRX_LINE_ID
, D.LINE_NUMBER
, (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
, RA_CUSTOMER_TRX_LINES_ALL D
WHERE A.ACCOUNT_CLASS <> 'REC'
AND A.CUSTOMER_TRX_LINE_ID = D.CUSTOMER_TRX_LINE_ID) B