VIEW: APPS.OE_RMA_INVOICE_NUMBER_V

Source

View Text - Preformatted

SELECT R.CUSTOMER_TRX_ID AS CUSTOMER_TRX_ID, R.TRX_NUMBER AS TRX_NUMBER, R.TRX_DATE AS TRX_DATE, B.NAME AS BATCH_SOURCE_NAME, R.SOLD_TO_CUSTOMER_ID AS SOLD_TO_CUSTOMER_ID, R.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY_CODE, R.CREATION_DATE AS INVOICE_CREATION_DATE, NVL((SELECT MAX(L.MEANING) STATUS FROM AR_PAYMENT_SCHEDULES APS , AR_LOOKUPS L WHERE APS.CUSTOMER_TRX_ID = R.CUSTOMER_TRX_ID AND APS.STATUS = L.LOOKUP_CODE(+) AND L.LOOKUP_TYPE(+) = 'PAYMENT_SCHEDULE_STATUS' AND APS.CLASS = 'INV') ,'No Status Available') AS PAYMENT_STATUS FROM RA_CUSTOMER_TRX R, RA_BATCH_SOURCES B WHERE B.BATCH_SOURCE_ID = R.BATCH_SOURCE_ID AND B.BATCH_SOURCE_TYPE='FOREIGN'

View Text - HTML Formatted

SELECT R.CUSTOMER_TRX_ID AS CUSTOMER_TRX_ID
, R.TRX_NUMBER AS TRX_NUMBER
, R.TRX_DATE AS TRX_DATE
, B.NAME AS BATCH_SOURCE_NAME
, R.SOLD_TO_CUSTOMER_ID AS SOLD_TO_CUSTOMER_ID
, R.INVOICE_CURRENCY_CODE AS INVOICE_CURRENCY_CODE
, R.CREATION_DATE AS INVOICE_CREATION_DATE
, NVL((SELECT MAX(L.MEANING) STATUS
FROM AR_PAYMENT_SCHEDULES APS
, AR_LOOKUPS L
WHERE APS.CUSTOMER_TRX_ID = R.CUSTOMER_TRX_ID
AND APS.STATUS = L.LOOKUP_CODE(+)
AND L.LOOKUP_TYPE(+) = 'PAYMENT_SCHEDULE_STATUS'
AND APS.CLASS = 'INV')
, 'NO STATUS AVAILABLE') AS PAYMENT_STATUS
FROM RA_CUSTOMER_TRX R
, RA_BATCH_SOURCES B
WHERE B.BATCH_SOURCE_ID = R.BATCH_SOURCE_ID
AND B.BATCH_SOURCE_TYPE='FOREIGN'