/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_products_details_v
AS
SELECT xcp.prod_key
      ,nvl(xxas_util_pkg.decode_lookup
            ('XXAS_PRODUCTS', xcp.prod_key), xcp.prod_key) product_name
      ,xcp.version 
      ,xxas_util_pkg.get_status_code(xcp.prod_key) license_status_code
      ,xxas_util_pkg.get_status(xcp.prod_key, 'Y') license_status
      ,xxas_util_pkg.get_license_category(xcp.prod_key) license_category
      ,CASE xxas_util_pkg.get_license_category(xcp.prod_key)
         WHEN '1' THEN 'Essentials'
         WHEN '2' THEN 'Professional'
         WHEN '3' THEN 'Enterprise'
         ELSE null
       END license_tier
      ,xxas_page_util_pkg.latest_product_patch('EMP') latest_patch
      ,xxas_util_pkg.is_enabled_char(xcp.prod_key) is_enabled
      ,xxas_util_pkg.get_license_expiry_date(xcp.prod_key) license_expiry_date
      ,xxas_util_pkg.get_license_volume(xcp.prod_key) license_volume
      ,xxas_util_pkg.get_num_users(xcp.prod_key) num_users
      ,CASE xcp.prod_key
          WHEN 'EMP' THEN 'wrench'
          WHEN 'SSH' THEN 'photo'
          WHEN 'TCM' THEN 'timecard'
          WHEN 'REC' THEN 'vacancy'
          WHEN 'PRF' THEN 'biline'
          ELSE 'circlequestion'
       END as product_icon
      ,CASE xcp.prod_key
          WHEN 'EMP' THEN '#1f3a46'
          WHEN 'SSH' THEN '#305e99'
          WHEN 'TCM' THEN '#7ec242'
          WHEN 'REC' THEN '#f9b517'
          WHEN 'PRF' THEN '#b177aa'
          ELSE '#747474'
       END as product_color
FROM xxas_com_products xcp
WHERE xcp.prod_key NOT IN ('CAL','OUT')
ORDER BY decode(xcp.prod_key, 'EMP', 1
                            , 'SSH', 2
                            , 'TCM', 3
                            , 'PRF', 4
                            , 'REC', 5
                            ,6), 2;
/

COMMIT;
EXIT;
