VIEW: APPS.ICX_CST_ITEM_7DAY_V

Source

View Text - Preformatted

SELECT INVENTORY_ITEM_ID , PERIOD , ICX_MARGIN_WEB_ANA_PKG.GET_ITEM_NUMBER(INVENTORY_ITEM_ID) , SUM (INVOICED_AMOUNT) , SUM (MARGIN) , DECODE (SUM (INVOICED_AMOUNT), 0, NULL, ROUND (SUM (MARGIN) / SUM (INVOICED_AMOUNT) * 100, 2)) CUSTOMER_MARGIN_PCT , DECODE (ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_SALES ('7D'), 0, NULL, ROUND (SUM (INVOICED_AMOUNT) / ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_SALES ('7D') * 100, 2)) SALES_PCT , DECODE (ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_MARGIN ('7D'), 0, NULL, ROUND (SUM (MARGIN) / ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_MARGIN ( '7D') * 100, 2)) MARGIN_PCT , TO_DATE , TO_DATE - 7 FROM ICX_MARGIN_ANALYSIS MA, CST_MARGIN_BUILD MB WHERE MARGIN IS NOT NULL AND NVL( MA.ORG_ID, NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO' ), 1, 1), ' ', NULL, SUBSTRB( USERENV( 'CLIENT_INFO' ), 1, 10 ))), -99)) = NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO' ), 1, 1), ' ', NULL, SUBSTRB(USERENV( 'CLIENT_INFO' ), 1, 10 ))), -99) AND MA.BUILD_ID = MB.BUILD_ID GROUP BY TO_DATE, INVENTORY_ITEM_ID, PERIOD HAVING PERIOD='7D'

View Text - HTML Formatted

SELECT INVENTORY_ITEM_ID
, PERIOD
, ICX_MARGIN_WEB_ANA_PKG.GET_ITEM_NUMBER(INVENTORY_ITEM_ID)
, SUM (INVOICED_AMOUNT)
, SUM (MARGIN)
, DECODE (SUM (INVOICED_AMOUNT)
, 0
, NULL
, ROUND (SUM (MARGIN) / SUM (INVOICED_AMOUNT) * 100
, 2)) CUSTOMER_MARGIN_PCT
, DECODE (ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_SALES ('7D')
, 0
, NULL
, ROUND (SUM (INVOICED_AMOUNT) / ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_SALES ('7D') * 100
, 2)) SALES_PCT
, DECODE (ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_MARGIN ('7D')
, 0
, NULL
, ROUND (SUM (MARGIN) / ICX_MARGIN_WEB_ANA_PKG.ICX_GET_TOTAL_MARGIN ( '7D') * 100
, 2)) MARGIN_PCT
, TO_DATE
, TO_DATE - 7
FROM ICX_MARGIN_ANALYSIS MA
, CST_MARGIN_BUILD MB
WHERE MARGIN IS NOT NULL
AND NVL( MA.ORG_ID
, NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO' )
, 1
, 1)
, ' '
, NULL
, SUBSTRB( USERENV( 'CLIENT_INFO' )
, 1
, 10 )))
, -99)) = NVL( TO_NUMBER( DECODE( SUBSTRB( USERENV( 'CLIENT_INFO' )
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV( 'CLIENT_INFO' )
, 1
, 10 )))
, -99)
AND MA.BUILD_ID = MB.BUILD_ID GROUP BY TO_DATE
, INVENTORY_ITEM_ID
, PERIOD HAVING PERIOD='7D'