VIEW: APPS.ISC_OPI_IND_CR_TAB_BASE_V

Source

View Text - Preformatted

SELECT gl.sobl_book_pk_key SOBL_BOOK_FK_KEY, bis.measure_name measure_name, decode(bis.measure_short_name, 'ISC_NUM_BOOKED_ORDERS', order_count, 'ISC_BOOKED_ORDERS', amt_booked_g, 'ISC_SHIPPED_ORDERS', amt_shipped_g, 'ISC_INVOICED_ORDERS', amt_invoiced_ar_g, 'ISC_EARNED_REV', amt_rev_earned_g, 'ISC_CASH_COLLECTED', amt_cash_received_g) MEASURE_QTD, decode(trunc((cal.curr_today - trunc(calendar_date))/7), 0 , decode(bis.measure_short_name, 'ISC_NUM_BOOKED_ORDERS', order_count, 'ISC_BOOKED_ORDERS', amt_booked_g, 'ISC_SHIPPED_ORDERS', amt_shipped_g, 'ISC_INVOICED_ORDERS', amt_invoiced_ar_g, 'ISC_EARNED_REV', amt_rev_earned_g, 'ISC_CASH_COLLECTED', amt_cash_received_g),0) MEASURE_WK, decode(bis.measure_short_name, 'ISC_NUM_BOOKED_ORDERS', 1, 'ISC_BOOKED_ORDERS', 2, 'ISC_SHIPPED_ORDERS', 3, 'ISC_INVOICED_ORDERS', 4, 'ISC_EARNED_REV', 5, 'ISC_CASH_COLLECTED', 6) SORT_ORDER FROM (SELECT FII_TIME_WH_API.GET_CURR_EQTR_START CURR_START, FII_TIME_WH_API.TODAY CURR_TODAY FROM DUAL) cal, FII_AR_OPERATIONS_SUMMARY fii, EDW_GL_BOOK_M gl, BISBV_PERFORMANCE_MEASURES bis WHERE fii.set_of_books_fk_key = gl.fabk_fa_book_pk_key AND calendar_date BETWEEN cal.curr_start AND cal.curr_today AND bis.measure_short_name in ('ISC_NUM_BOOKED_ORDERS', 'ISC_BOOKED_ORDERS', 'ISC_SHIPPED_ORDERS', 'ISC_INVOICED_ORDERS', 'ISC_EARNED_REV', 'ISC_CASH_COLLECTED')

View Text - HTML Formatted

SELECT GL.SOBL_BOOK_PK_KEY SOBL_BOOK_FK_KEY
, BIS.MEASURE_NAME MEASURE_NAME
, DECODE(BIS.MEASURE_SHORT_NAME
, 'ISC_NUM_BOOKED_ORDERS'
, ORDER_COUNT
, 'ISC_BOOKED_ORDERS'
, AMT_BOOKED_G
, 'ISC_SHIPPED_ORDERS'
, AMT_SHIPPED_G
, 'ISC_INVOICED_ORDERS'
, AMT_INVOICED_AR_G
, 'ISC_EARNED_REV'
, AMT_REV_EARNED_G
, 'ISC_CASH_COLLECTED'
, AMT_CASH_RECEIVED_G) MEASURE_QTD
, DECODE(TRUNC((CAL.CURR_TODAY - TRUNC(CALENDAR_DATE))/7)
, 0
, DECODE(BIS.MEASURE_SHORT_NAME
, 'ISC_NUM_BOOKED_ORDERS'
, ORDER_COUNT
, 'ISC_BOOKED_ORDERS'
, AMT_BOOKED_G
, 'ISC_SHIPPED_ORDERS'
, AMT_SHIPPED_G
, 'ISC_INVOICED_ORDERS'
, AMT_INVOICED_AR_G
, 'ISC_EARNED_REV'
, AMT_REV_EARNED_G
, 'ISC_CASH_COLLECTED'
, AMT_CASH_RECEIVED_G)
, 0) MEASURE_WK
, DECODE(BIS.MEASURE_SHORT_NAME
, 'ISC_NUM_BOOKED_ORDERS'
, 1
, 'ISC_BOOKED_ORDERS'
, 2
, 'ISC_SHIPPED_ORDERS'
, 3
, 'ISC_INVOICED_ORDERS'
, 4
, 'ISC_EARNED_REV'
, 5
, 'ISC_CASH_COLLECTED'
, 6) SORT_ORDER
FROM (SELECT FII_TIME_WH_API.GET_CURR_EQTR_START CURR_START
, FII_TIME_WH_API.TODAY CURR_TODAY
FROM DUAL) CAL
, FII_AR_OPERATIONS_SUMMARY FII
, EDW_GL_BOOK_M GL
, BISBV_PERFORMANCE_MEASURES BIS
WHERE FII.SET_OF_BOOKS_FK_KEY = GL.FABK_FA_BOOK_PK_KEY
AND CALENDAR_DATE BETWEEN CAL.CURR_START
AND CAL.CURR_TODAY
AND BIS.MEASURE_SHORT_NAME IN ('ISC_NUM_BOOKED_ORDERS'
, 'ISC_BOOKED_ORDERS'
, 'ISC_SHIPPED_ORDERS'
, 'ISC_INVOICED_ORDERS'
, 'ISC_EARNED_REV'
, 'ISC_CASH_COLLECTED')