VIEW: APPS.AR_INVOICE_COMMITMENT_INFO_V

Source

View Text - Preformatted

SELECT child.customer_trx_id customer_trx_id, trx.previous_customer_trx_id previous_customer_trx_id, trx.customer_trx_id commitment_customer_trx_id, trx.trx_number commitment_trx_number, trx.start_date_commitment start_date_commitment, trx.end_date_commitment end_date_commitment, types.type class, l.meaning class_name, lines.revenue_amount original_amount, AR_INVOICE_SQL_FUNC_PUB.get_commit_this_invoice( child.customer_trx_id) this_invoice_amount, AR_INVOICE_SQL_FUNC_PUB.get_com_total_activity( child.customer_trx_id, types.type, trx.customer_trx_id ) total_activity_amt, AR_INVOICE_SQL_FUNC_PUB.get_com_amt_uninvoiced( trx.customer_trx_id ) uninvoiced_amount, nvl(lines.revenue_amount,0) + nvl(AR_INVOICE_SQL_FUNC_PUB.get_commit_this_invoice( child.customer_trx_id),0 ) + nvl(AR_INVOICE_SQL_FUNC_PUB.get_com_total_activity( child.customer_trx_id, types.type, trx.customer_trx_id ),0) + nvl(AR_INVOICE_SQL_FUNC_PUB.get_com_amt_uninvoiced( trx.customer_trx_id ),0) commitment_balance FROM ar_lookups l, ra_customer_trx_lines lines, ra_customer_trx child, ra_cust_trx_types types, ra_customer_trx trx WHERE trx.complete_flag = 'Y' AND types.type = l.lookup_code AND l.lookup_type = 'INV/CM' AND types.cust_trx_type_id = trx.cust_trx_type_id AND trx.customer_trx_id = lines.customer_trx_id AND (l.lookup_code != 'CM' OR trx.customer_trx_id = NVL(trx.previous_customer_trx_id, -1)) AND child.initial_customer_trx_id = trx.customer_trx_id

View Text - HTML Formatted

SELECT CHILD.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, TRX.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID
, TRX.CUSTOMER_TRX_ID COMMITMENT_CUSTOMER_TRX_ID
, TRX.TRX_NUMBER COMMITMENT_TRX_NUMBER
, TRX.START_DATE_COMMITMENT START_DATE_COMMITMENT
, TRX.END_DATE_COMMITMENT END_DATE_COMMITMENT
, TYPES.TYPE CLASS
, L.MEANING CLASS_NAME
, LINES.REVENUE_AMOUNT ORIGINAL_AMOUNT
, AR_INVOICE_SQL_FUNC_PUB.GET_COMMIT_THIS_INVOICE( CHILD.CUSTOMER_TRX_ID) THIS_INVOICE_AMOUNT
, AR_INVOICE_SQL_FUNC_PUB.GET_COM_TOTAL_ACTIVITY( CHILD.CUSTOMER_TRX_ID
, TYPES.TYPE
, TRX.CUSTOMER_TRX_ID ) TOTAL_ACTIVITY_AMT
, AR_INVOICE_SQL_FUNC_PUB.GET_COM_AMT_UNINVOICED( TRX.CUSTOMER_TRX_ID ) UNINVOICED_AMOUNT
, NVL(LINES.REVENUE_AMOUNT
, 0) + NVL(AR_INVOICE_SQL_FUNC_PUB.GET_COMMIT_THIS_INVOICE( CHILD.CUSTOMER_TRX_ID)
, 0 ) + NVL(AR_INVOICE_SQL_FUNC_PUB.GET_COM_TOTAL_ACTIVITY( CHILD.CUSTOMER_TRX_ID
, TYPES.TYPE
, TRX.CUSTOMER_TRX_ID )
, 0) + NVL(AR_INVOICE_SQL_FUNC_PUB.GET_COM_AMT_UNINVOICED( TRX.CUSTOMER_TRX_ID )
, 0) COMMITMENT_BALANCE
FROM AR_LOOKUPS L
, RA_CUSTOMER_TRX_LINES LINES
, RA_CUSTOMER_TRX CHILD
, RA_CUST_TRX_TYPES TYPES
, RA_CUSTOMER_TRX TRX
WHERE TRX.COMPLETE_FLAG = 'Y'
AND TYPES.TYPE = L.LOOKUP_CODE
AND L.LOOKUP_TYPE = 'INV/CM'
AND TYPES.CUST_TRX_TYPE_ID = TRX.CUST_TRX_TYPE_ID
AND TRX.CUSTOMER_TRX_ID = LINES.CUSTOMER_TRX_ID
AND (L.LOOKUP_CODE != 'CM' OR TRX.CUSTOMER_TRX_ID = NVL(TRX.PREVIOUS_CUSTOMER_TRX_ID
, -1))
AND CHILD.INITIAL_CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID