VIEW: APPS.AR_LINE_EXCEPTIONS_V

Source

View Text - Preformatted

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(rct.trx_number) trx_number, MAX(rct.trx_date) trx_date, MAX(gldist.gl_date) rec_gl_date, MAX(gldist2.gl_date) gl_date, MAX(arpt_sql_func_util.get_reference(rct.rowid)) reference_number, MAX(rctl.line_number) line_number, MAX(rctl.description) description, MAX(lrs.acctd_amount_due_original) line_amount, MAX(rr.name) rule_name, MAX(arpt_sql_func_util.get_salesrep_name_number( rct.primary_salesrep_id, 'NAME')) salesrep_name, MAX(lrs.last_update_date) manual_adjustment_date, SUM(decode(gldist2.account_class, 'UNEARN', gldist2.acctd_amount, 0)) + MAX(arp_bal_util.get_line_cm(rctl.customer_trx_line_id)) unearned_revenue, SUM(decode(gldist2.account_class, 'REV', gldist2.acctd_amount, 0)) revenue, MAX(arp_bal_util.get_line_cm(rctl.customer_trx_line_id)) credit_memos FROM ar_deferred_lines lrs, ra_customer_trx_lines rctl, ra_customer_trx rct, ra_rules rr, ra_cust_trx_line_gl_dist gldist, ra_cust_trx_line_gl_dist gldist2, gl_sets_of_books books, hz_cust_accounts hca, hz_parties hp WHERE lrs.customer_trx_line_id = rctl.customer_trx_line_id AND rctl.customer_trx_id = rct.customer_trx_id AND rct.customer_trx_id = gldist.customer_trx_id AND lrs.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 rctl.accounting_rule_id = rr.rule_id (+) AND gldist.set_of_books_id = books.set_of_books_id AND rct.bill_to_customer_id = hca.cust_account_id AND hca.party_id = hp.party_id AND lrs.line_collectible_flag = 'N' AND lrs.manual_override_flag = 'Y' GROUP BY lrs.customer_trx_line_id

View Text - HTML Formatted

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(RCT.TRX_NUMBER) TRX_NUMBER
, MAX(RCT.TRX_DATE) TRX_DATE
, MAX(GLDIST.GL_DATE) REC_GL_DATE
, MAX(GLDIST2.GL_DATE) GL_DATE
, MAX(ARPT_SQL_FUNC_UTIL.GET_REFERENCE(RCT.ROWID)) REFERENCE_NUMBER
, MAX(RCTL.LINE_NUMBER) LINE_NUMBER
, MAX(RCTL.DESCRIPTION) DESCRIPTION
, MAX(LRS.ACCTD_AMOUNT_DUE_ORIGINAL) LINE_AMOUNT
, MAX(RR.NAME) RULE_NAME
, MAX(ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER( RCT.PRIMARY_SALESREP_ID
, 'NAME')) SALESREP_NAME
, MAX(LRS.LAST_UPDATE_DATE) MANUAL_ADJUSTMENT_DATE
, SUM(DECODE(GLDIST2.ACCOUNT_CLASS
, 'UNEARN'
, GLDIST2.ACCTD_AMOUNT
, 0)) + MAX(ARP_BAL_UTIL.GET_LINE_CM(RCTL.CUSTOMER_TRX_LINE_ID)) UNEARNED_REVENUE
, SUM(DECODE(GLDIST2.ACCOUNT_CLASS
, 'REV'
, GLDIST2.ACCTD_AMOUNT
, 0)) REVENUE
, MAX(ARP_BAL_UTIL.GET_LINE_CM(RCTL.CUSTOMER_TRX_LINE_ID)) CREDIT_MEMOS
FROM AR_DEFERRED_LINES LRS
, RA_CUSTOMER_TRX_LINES RCTL
, RA_CUSTOMER_TRX RCT
, RA_RULES RR
, RA_CUST_TRX_LINE_GL_DIST GLDIST
, RA_CUST_TRX_LINE_GL_DIST GLDIST2
, GL_SETS_OF_BOOKS BOOKS
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE LRS.CUSTOMER_TRX_LINE_ID = RCTL.CUSTOMER_TRX_LINE_ID
AND RCTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCT.CUSTOMER_TRX_ID = GLDIST.CUSTOMER_TRX_ID
AND LRS.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 RCTL.ACCOUNTING_RULE_ID = RR.RULE_ID (+)
AND GLDIST.SET_OF_BOOKS_ID = BOOKS.SET_OF_BOOKS_ID
AND RCT.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND LRS.LINE_COLLECTIBLE_FLAG = 'N'
AND LRS.MANUAL_OVERRIDE_FLAG = 'Y' GROUP BY LRS.CUSTOMER_TRX_LINE_ID