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

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

CREATE OR REPLACE VIEW xxas_general_status_list_v
AS
SELECT *
FROM (
  SELECT 'flexible' "type"
        ,1 request_id
        ,'Applaud Cloud' "line1"
        ,'<font color="'
      || CASE WHEN c.error_msg IS NOT NULL
              THEN xxas_rslt_cache_pkg.resolve_color_name('red')
              ELSE xxas_rslt_cache_pkg.resolve_color_name('green')
         END
      || '">'
      || CASE WHEN c.error_msg IS NOT NULL
              THEN c.error_msg
              ELSE 'Connection live'
         END
       || '</font>' "line3"
        ,CASE WHEN c.error_msg IS NOT NULL
              THEN 'cloud-broken'
              ELSE 'cloud'
         END  "icon"
        ,CASE WHEN c.error_msg IS NOT NULL
              THEN xxas_rslt_cache_pkg.resolve_color_name('red')
              ELSE xxas_rslt_cache_pkg.resolve_color_name('green')
         END "statusColor"
  FROM  (SELECT xxas_com_msg_pkg.get_decoded_string(xxas_cloud_pkg.connection_error_message) error_msg
         FROM   dual) c
  UNION
  SELECT 'flexible' "type"
        ,2 request_id
        ,p.product_name "line1"
        ,'<font color="'
      || CASE WHEN p.license_expiry_date < trunc(sysdate)
              THEN xxas_rslt_cache_pkg.resolve_color_name('red')
              ELSE xxas_rslt_cache_pkg.resolve_color_name('orange')
         END
      || '">'
      || CASE WHEN p.license_expiry_date < trunc(sysdate)
              THEN 'Product license expired on '
                 || to_char(p.license_expiry_date, 'FMDd Mon YYYY')
              ELSE 'Product license expires on '
                 || to_char(p.license_expiry_date, 'FMDd Mon YYYY')
         END
       || '</font>' "line3"
        ,p.product_icon "icon"
        ,p.product_color "statusColor"
  FROM   xxas_products_details_v p
  WHERE  p.license_expiry_date IS NOT NULL
  AND    p.license_expiry_date <= trunc(sysdate) + 60
  UNION
  SELECT 'flexible' "type"
        ,3 request_id
        ,p.product_name "line1"
        ,'<font color="'
       || xxas_rslt_cache_pkg.resolve_color_name('red')
       || '">'
       || 'Product is underlicensed'
       || '</font>' "line3"
        ,p.product_icon "icon"
        ,p.product_color "statusColor"
  FROM   xxas_products_details_v p
  WHERE  nvl(p.license_volume, 0) > 0
  AND    p.license_volume < p.num_users
  UNION
  SELECT 'flexible' "type"
        ,4 request_id
        ,'Products' "line1"
        ,TO_CHAR(xxas_page_util_pkg.inactive_products)
          || CASE xxas_page_util_pkg.inactive_products
                WHEN 1 THEN ' product' 
                ELSE ' products'
                END || ' not activated' "line3"
        ,'basket' "icon"
        ,xxas_page_util_pkg.status_bk_products_color "statusColor"
  FROM dual
  WHERE xxas_page_util_pkg.inactive_products > 0
  UNION
  SELECT 'flexible' "type"
        ,5 request_id
        ,'License Management' "line1"--cp.conc_prog_name "line1"
        ,'<font color="'
       || xxas_rslt_cache_pkg.resolve_color_name('orange')
       || '">'
       || 'Concurrent program is not scheduled (a daily schedule is recommended)'
       || '</font>' "line3" 
        ,'basket' "icon"
        ,'energized' "statusColor"
  FROM dual
  WHERE NOT EXISTS
    (SELECT 'exists'
     FROM   fnd_concurrent_requests fcr
           ,fnd_concurrent_programs_vl fcp
     WHERE  fcr.concurrent_program_id = fcp.concurrent_program_id
     AND    fcp.concurrent_program_name = 'XXAS_COM_LICENSE_MANAGEMENT'
     AND    fcr.phase_code IN ('P', 'R')
     AND    fcr.status_code IN ('C', 'I', 'R')
     AND    fcr.hold_flag = 'N'
     AND    fcr.requested_start_date > sysdate)
  UNION
  SELECT 'flexible' "type"
        ,6 request_id
        ,'Faceted Search Crawler' "line1"--cp.conc_prog_name "line1"
        --,cp.start_time
        --,cp.resubmit_interval
        --,cp.resubmit_interval_unit_code
        --,'Running daily at '||cp.start_time "line3"
        ,xxas_general_page_pkg.get_crawler_schedule "line3"
        ,'search' "icon"
        ,CASE xxas_general_page_pkg.get_crawler_running_status
          WHEN 'Y' THEN 'balanced'
          ELSE  'assertive'
          END "statusColor"
  FROM dual cp
  UNION
  SELECT
        'flexible' "type"
        ,7 request_id
        ,'Errors' "line1"
        ,xxas_page_util_pkg.error_notif_count||' open error notifications' "line3"
        ,'alert' "icon"
        ,'assertive' "statusColor"
  FROM dual  
  WHERE xxas_page_util_pkg.error_notif_count > 0
  UNION
  SELECT
        'flexible' "type"
        ,8 request_id
        ,'Using seeded menu' "line1"
        ,'Swap the seeded menu for your own version' "line3"
        ,'bullseye' "icon"
        ,'energized' "statusColor"
  FROM dual
  WHERE fnd_profile.value('XXAS_EMP_REG_VIEWS_MENU') = 'XXAS_EMP_REGISTERED_VIEWS'
  UNION
  SELECT
        'flexible' "type"
        ,9 request_id
        ,xxas_page_util_pkg.pages_count || ' pages' "line1"
        ,xxas_page_util_pkg.pages_count('Y') || ' published, '
          ||xxas_page_util_pkg.pages_count('N')||' draft' "line3"
        ,'letteralt' "icon"
        ,'balanced' "statusColor"
  FROM dual)
ORDER BY request_id
;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_GENERAL_STATUS_LIST_V'
   ,p_description            => 'Applaud General Status List View');
END;
/

COMMIT;
EXIT;
