VIEW: APPS.AR_LINE_CONTINGENCIES_V

Source

View Text - Preformatted

SELECT MAX(gsb.name) set_of_books_name, MAX(gsb.set_of_books_id) set_of_books_id, MAX(gsb.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(arpt_sql_func_util.get_salesrep_name_number (rct.primary_salesrep_id, 'NAME', lrs.org_id)) salesrep_name, 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(dr.contingency_name) revenue_contingency, MAX(le.meaning) revrec_event, MAX(lrc.expiration_date) expiration_date, MAX(lrc.reason_removal_date) reason_removal_date, MAX(lrs.acctd_amount_recognized) scheduled_revenue, MAX( lrs.acctd_amount_due_original + arp_bal_util.get_line_cm(rctl.customer_trx_line_id) - lrs.acctd_amount_recognized) unscheduled_revenue, MAX(arp_bal_util.get_line_cm(rctl.customer_trx_line_id)) credit_memos FROM ar_deferred_lines lrs, ar_line_conts lrc, ra_customer_trx_lines rctl, ra_customer_trx rct, ra_rules rr, ar_lookups le, /* event lookup */ ar_deferral_reasons dr, ra_cust_trx_line_gl_dist gldist, /* rec */ gl_sets_of_books gsb, hz_cust_accounts hca, hz_parties hp WHERE lrs.customer_trx_line_id = lrc.customer_trx_line_id AND lrc.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 gldist.account_class = 'REC' AND gldist.latest_rec_flag = 'Y' AND gldist.set_of_books_id = gsb.set_of_books_id AND rctl.accounting_rule_id = rr.rule_id (+) AND lrc.contingency_id = dr.contingency_id AND dr.revrec_event_code = le.lookup_code AND le.lookup_type = 'AR_REVREC_EVENTS' 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 = 'N' GROUP BY lrc.customer_trx_line_id, lrc.contingency_id HAVING MAX( lrs.acctd_amount_due_original + arp_bal_util.get_line_cm(rctl.customer_trx_line_id) - lrs.acctd_amount_recognized) <> 0

View Text - HTML Formatted

SELECT MAX(GSB.NAME) SET_OF_BOOKS_NAME
, MAX(GSB.SET_OF_BOOKS_ID) SET_OF_BOOKS_ID
, MAX(GSB.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(ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER (RCT.PRIMARY_SALESREP_ID
, 'NAME'
, LRS.ORG_ID)) SALESREP_NAME
, 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(DR.CONTINGENCY_NAME) REVENUE_CONTINGENCY
, MAX(LE.MEANING) REVREC_EVENT
, MAX(LRC.EXPIRATION_DATE) EXPIRATION_DATE
, MAX(LRC.REASON_REMOVAL_DATE) REASON_REMOVAL_DATE
, MAX(LRS.ACCTD_AMOUNT_RECOGNIZED) SCHEDULED_REVENUE
, MAX( LRS.ACCTD_AMOUNT_DUE_ORIGINAL + ARP_BAL_UTIL.GET_LINE_CM(RCTL.CUSTOMER_TRX_LINE_ID) - LRS.ACCTD_AMOUNT_RECOGNIZED) UNSCHEDULED_REVENUE
, MAX(ARP_BAL_UTIL.GET_LINE_CM(RCTL.CUSTOMER_TRX_LINE_ID)) CREDIT_MEMOS
FROM AR_DEFERRED_LINES LRS
, AR_LINE_CONTS LRC
, RA_CUSTOMER_TRX_LINES RCTL
, RA_CUSTOMER_TRX RCT
, RA_RULES RR
, AR_LOOKUPS LE
, /* EVENT LOOKUP */ AR_DEFERRAL_REASONS DR
, RA_CUST_TRX_LINE_GL_DIST GLDIST
, /* REC */ GL_SETS_OF_BOOKS GSB
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
WHERE LRS.CUSTOMER_TRX_LINE_ID = LRC.CUSTOMER_TRX_LINE_ID
AND LRC.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 GLDIST.ACCOUNT_CLASS = 'REC'
AND GLDIST.LATEST_REC_FLAG = 'Y'
AND GLDIST.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
AND RCTL.ACCOUNTING_RULE_ID = RR.RULE_ID (+)
AND LRC.CONTINGENCY_ID = DR.CONTINGENCY_ID
AND DR.REVREC_EVENT_CODE = LE.LOOKUP_CODE
AND LE.LOOKUP_TYPE = 'AR_REVREC_EVENTS'
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 = 'N' GROUP BY LRC.CUSTOMER_TRX_LINE_ID
, LRC.CONTINGENCY_ID HAVING MAX( LRS.ACCTD_AMOUNT_DUE_ORIGINAL + ARP_BAL_UTIL.GET_LINE_CM(RCTL.CUSTOMER_TRX_LINE_ID) - LRS.ACCTD_AMOUNT_RECOGNIZED) <> 0