VIEW: APPS.ICX_CST_TERRITORY_7DAY_V

Source

View Text - Preformatted

SELECT MA.TERRITORY_ID , PERIOD , RT.NAME , 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, RA_TERRITORIES RT WHERE MARGIN IS NOT NULL AND MA.TERRITORY_ID = RT.TERRITORY_ID(+) 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, MA.TERRITORY_ID, PERIOD, RT.NAME HAVING PERIOD='7D'

View Text - HTML Formatted

SELECT MA.TERRITORY_ID
, PERIOD
, RT.NAME
, 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
, RA_TERRITORIES RT
WHERE MARGIN IS NOT NULL
AND MA.TERRITORY_ID = RT.TERRITORY_ID(+)
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
, MA.TERRITORY_ID
, PERIOD
, RT.NAME HAVING PERIOD='7D'