View: AR_LINE_REVENUE_DETAILS_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_LINE_REVENUE_DETAILS_V

View Text

SELECT MAX(BOOKS.NAME) SET_OF_BOOKS_NAME
, MAX(BOOKS.SET_OF_BOOKS_ID) SET_OF_BOOKS_ID
, MAX(BOOKS.CURRENCY_CODE) CURRENCY_CODE
, MAX(HP.PARTY_NAME) CUSTOMER_NAME
, MAX(HCA.ACCOUNT_NUMBER) CUSTOMER_NUMBER
, MAX(RCTA.TRX_NUMBER) TRX_NUMBER
, MAX(ARAT.LAST_UPDATE_DATE) LAST_UPDATE_DATE
, MAX(RCTA.TRX_DATE) TRX_DATE
, MAX(GLDIST.GL_DATE) GL_DATE
, MAX(GLDIST2.GL_DATE) GL_DATE2
, MAX(ARPT_SQL_FUNC_UTIL.GET_REFERENCE(RCTA.ROWID)) REFERENCE_NUMBER
, MAX(ARAT.LINE_COLLECTIBLE_FLAG) LINE_COLLECTIBLE_FLAG
, MAX(ARAT.MANUAL_OVERRIDE_FLAG) MANUAL_OVERRIDE_FLAG
, MAX(DECODE(ARAT.REASON_CREDIT_FLAG
, 'N'
, 'YES'
, 'NO')) CREDIT
, MAX(DECODE(ARAT.REASON_PAYMENT_TERM_FLAG
, 'N'
, 'STANDARD'
, 'EXTENDED')) PAYMENT_TERM
, MAX(RCTLA.LINE_NUMBER) LINE_NUMBER
, MAX(RCTLA.DESCRIPTION) DESCRIPTION
, MAX(OKHB.CONTRACT_NUMBER) CONTRACT_NUMBER
, MAX(ARAT.ACCTD_AMOUNT_DUE_ORIGINAL) LINE_AMOUNT
, MAX(ARP_BAL_UTIL.GET_LINE_CM(RCTLA.CUSTOMER_TRX_LINE_ID)) CREDIT_MEMOS
, MAX(ARAT.ACCTD_AMOUNT_RECOGNIZED) SCHEDULED_REVENUE
, MAX( ARAT.ACCTD_AMOUNT_DUE_ORIGINAL + ARP_BAL_UTIL.GET_LINE_CM(RCTLA.CUSTOMER_TRX_LINE_ID) - ARAT.ACCTD_AMOUNT_RECOGNIZED) UNSCHEDULED_REVENUE
, MAX(RR.NAME) RULE_NAME
, MAX(ARAT.SALES_ORDER) SALES_ORDER
, MAX(ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER( RCTA.PRIMARY_SALESREP_ID
, 'NAME'
, RCTA.ORG_ID)) SALESREP_NAME
, DECODE(MAX(ARAT.REASON_FORFEITURE_FLAG)
, 'N'
, NULL
, 'Y'
, MAX(ARAT.FORFEITURE_EXPIRY)) FORFEITURE_EXPIRATION
, DECODE(MAX(ARAT.REASON_REFUND_FLAG)
, 'N'
, NULL
, 'Y'
, MAX(ARAT.REFUND_EXPIRY)) REFUND_EXPIRATION
, DECODE(MAX(ARAT.REASON_ACCEPTANCE_FLAG)
, 'N'
, NULL
, 'Y'
, MAX(ARAT.ACCEPTANCE_EXPIRY)) ACCEPTANCE_EXPIRATION
, DECODE(MAX(ARAT.REASON_CANCELLATION_FLAG)
, 'N'
, NULL
, 'Y'
, MAX(ARAT.CANCELLATION_EXPIRY)) CANCELLATION_EXPIRATION
, DECODE(MAX(ARAT.REASON_FISCAL_FLAG)
, 'N'
, NULL
, 'Y'
, MAX(ARAT.FISCAL_EXPIRY)) FISCAL_EXPIRATION
, SUM(DECODE(GLDIST2.ACCOUNT_CLASS
, 'UNEARN'
, GLDIST2.ACCTD_AMOUNT
, 0)) + MAX(ARP_BAL_UTIL.GET_LINE_CM(RCTLA.CUSTOMER_TRX_LINE_ID)) UNEARNED_REVENUE
, SUM(DECODE(GLDIST2.ACCOUNT_CLASS
, 'REV'
, GLDIST2.ACCTD_AMOUNT
, 0)) REVENUE
FROM AR_RAMC_AUDIT_TRAIL ARAT
, RA_CUSTOMER_TRX_LINES_ALL RCTLA
, RA_CUSTOMER_TRX RCTA
, RA_RULES RR
, RA_CUST_TRX_LINE_GL_DIST_ALL GLDIST
, RA_CUST_TRX_LINE_GL_DIST_ALL GLDIST2
, GL_SETS_OF_BOOKS BOOKS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, OKC_K_HEADERS_B OKHB
WHERE ARAT.CUSTOMER_TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID
AND RCTLA.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = GLDIST.CUSTOMER_TRX_ID
AND ARAT.CUSTOMER_TRX_LINE_ID = GLDIST2.CUSTOMER_TRX_LINE_ID
AND GLDIST2.ACCOUNT_SET_FLAG = 'N'
AND GLDIST.ACCOUNT_CLASS = 'REC'
AND GLDIST.LATEST_REC_FLAG = 'Y'
AND RCTLA.ACCOUNTING_RULE_ID = RR.RULE_ID (+)
AND GLDIST.SET_OF_BOOKS_ID = BOOKS.SET_OF_BOOKS_ID
AND ARAT.CONTRACT_ID = OKHB.ID (+)
AND ARAT.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND ARAT.LINE_COLLECTIBLE_FLAG = 'N'
AND NVL(RCTA.ORG_ID
, -99) = NVL(RCTLA.ORG_ID
, -99)
AND NVL(RCTA.ORG_ID
, -99) = NVL(GLDIST.ORG_ID
, -99)
AND NVL(RCTA.ORG_ID
, -99) = NVL(GLDIST2.ORG_ID
, -99)
AND MO_GLOBAL.CHECK_ACCESS(RCTA.ORG_ID) ='Y' GROUP BY ARAT.CUSTOMER_TRX_LINE_ID

Columns