--------------------------------------------------------

--  File created - Friday-July-24-2015   

--------------------------------------------------------

--------------------------------------------------------

--  DDL for Package Body GET_URP_REPORT_DETAILS

--------------------------------------------------------



  CREATE OR REPLACE PACKAGE BODY "MYASUPDSS"."GET_URP_REPORT_DETAILS" 

AS

PROCEDURE sp_customer_system_details(

    p_job_id   IN NUMBER,

    p_key_name IN VARCHAR2,

    p_sheets IN VARCHAR2,

    p_system OUT nocopy sys_refcursor,

    p_customer_site_info OUT nocopy sys_refcursor,    

    p_case OUT nocopy sys_refcursor,

    p_system_age OUT nocopy sys_refcursor,

    p_contract_dtl OUT nocopy sys_refcursor,

    p_capacity_forecast OUT nocopy sys_refcursor,

    p_epic_controller OUT nocopy sys_refcursor,

    p_epic_shelf OUT nocopy sys_refcursor,

    p_epic_disk OUT nocopy sys_refcursor,

    p_osfw_system OUT nocopy sys_refcursor,

    p_osfw_motherboard OUT nocopy sys_refcursor,

    p_osfw_shelf OUT nocopy sys_refcursor,

    p_osfw_disk OUT nocopy sys_refcursor,

    p_osfw_rlm OUT nocopy sys_refcursor,

    p_dave_hitz OUT nocopy sys_refcursor,

    p_perf_summary OUT nocopy sys_refcursor,

    p_fear_disk OUT nocopy sys_refcursor,

    p_fear_volume OUT nocopy sys_refcursor,

    p_fear_aggregate OUT nocopy sys_refcursor,

    p_fear_lun OUT nocopy sys_refcursor,

    p_fear_vol_option OUT nocopy sys_refcursor,

    p_fear_system_option OUT nocopy sys_refcursor,

    p_fear_license OUT nocopy sys_refcursor,

    p_invalid_serial_no OUT nocopy sys_refcursor)

AS

  v_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 15, 'YYYYMMDD') || '00000000');

  v_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD') || '99999999');

  v_datapoint1_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 95, 'YYYYMMDD') || '00000000');

  v_datapoint1_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 85, 'YYYYMMDD') || '99999999');

  v_datapoint2_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000');

  v_datapoint2_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999');

  v_datapoint3_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000');

  v_datapoint3_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999');

  v_datapoint4_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000');

  v_datapoint4_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999');

  

  v_enabled_report VARCHAR2(3000) := p_sheets;

  --v_enabled_report VARCHAR2(3000) := 'isSystemInventoryReport,isDaveHitzReport,isStorageEfficiencyReport';

  is_inventory_required NUMBER;

  is_case_required NUMBER;

  is_davehitz_required NUMBER;

  is_storefficiency_required NUMBER;

  is_contract_required NUMBER;

  is_endofsupport_required NUMBER;

  is_systemaging_required NUMBER;

  is_osfirmware_required NUMBER;

  is_capacityforecast_required NUMBER;

  is_performance_required  NUMBER;  

  

BEGIN

  is_inventory_required := INSTR(v_enabled_report, 'isSystemInventoryReport');

  is_case_required := INSTR(v_enabled_report, 'isSupportCaseReport');

  is_davehitz_required := INSTR(v_enabled_report, 'isDaveHitzReport');

  is_storefficiency_required:= INSTR(v_enabled_report, 'isStorageEfficiencyReport');

  is_contract_required:= INSTR(v_enabled_report, 'isContractDetails');

  is_endofsupport_required:= INSTR(v_enabled_report, 'isEndOfSupportRedar');

  is_systemaging_required:= INSTR(v_enabled_report, 'isSystemAgingReport');

  is_osfirmware_required:= INSTR(v_enabled_report, 'isOSFirmwareDetailReport');

  is_capacityforecast_required:= INSTR(v_enabled_report, 'isCapacityForecastReport');

  is_performance_required:= INSTR(v_enabled_report, 'isPerformanceSummaryReport');

  

  IF is_inventory_required <> 0 THEN

    OPEN p_system FOR 

        SELECT mv.Sys_Serial_No Sys_Serialno,

         mv.hostname sys_hostname,

         mv.asup_id asupid,

         mv.Sys_Model Sys_Model,

         mv.Sys_Version Sys_Version,

         mv.secure_flag secure_flag,

         TO_CHAR(mv.Asup_Received_Date,'DD-MON-YYYY') Sys_Revdate 

        FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

  ELSE

    OPEN p_system FOR 

    Select * from Dual where 1=2;

  END IF;

  

  

    

  OPEN p_customer_site_info FOR 

      SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

          mv.CUSTOMER_ID,

          mv.CUSTOMER_NAME,

          mv.SITE_ID,      

          mv.SITE_NAME

      FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

          Asupdss.O_CUSTOMER cust,

          Asupdss.O_SITE site 

      WHERE 

          cust.CUSTOMER_ID        = mv.CUSTOMER_ID

          AND site.SITE_ID              = mv.SITE_ID

          --and cust.customer_id = '1441509'

          AND cust.current_row_ind='T'

          AND site.current_row_ind='T'

          AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

          

          

  

    IF is_case_required <> 0 THEN

      OPEN p_case FOR

        SELECT dcase.case_id, dcase.create_date, dcase.STATUS_TEXT status,dcase.CURRENT_PRIORITY

        FROM asupdss.D_INSTALLED_PRODUCT dprod, asupdss.f_case fcase, asupdss.D_Case dcase

        WHERE dprod.INSTALLED_PRODUCT_KEY = fcase.INSTALLED_PRODUCT_KEY

        AND fcase.case_key = dcase.case_key

        AND dprod.IP_SERIAL_NUMBER IN (

          SELECT distinct dproduct.IP_SERIAL_NUMBER

          FROM asupdss.O_CUSTOMER cust, asupdss.D_Installed_Product dproduct

          WHERE cust.CUSTOMER_KEY  = dproduct.OWNER_CUSTOMER_KEY    and DPRODUCT.IP_STATUS = 'ACTIVE'

          AND cust.current_row_ind = 'T'

          AND cust.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

        ) order by create_date desc;

    ELSE

      OPEN p_case FOR 

      Select * from Dual where 1=2;

    END IF; 



    

    IF is_systemaging_required <> 0 THEN

      OPEN p_system_age FOR    

        SELECT distinct dproduct.IP_SERIAL_NUMBER, dproduct.IP_SYSTEM_NAME hostname, dproduct.IP_SHIP_DATE

        FROM asupdss.O_CUSTOMER cust, asupdss.D_Installed_Product dproduct

        WHERE cust.CUSTOMER_KEY  = dproduct.OWNER_CUSTOMER_KEY    and DPRODUCT.IP_STATUS = 'ACTIVE'

        AND cust.current_row_ind = 'T'

        AND cust.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

        ORDER BY IP_SHIP_DATE DESC;

    ELSE

      OPEN p_system_age FOR 

      Select * from Dual where 1=2;

    END IF;

    

    

  

   IF is_contract_required <> 0 THEN

     OPEN p_contract_dtl FOR     

        SELECT aggrement.AGREEMENT_ID,

          aggrement.ORACLESALESORDER,

          TO_CHAR(aggrement.AGR_START_DATE,'DD-MON-YYYY') AGR_START_DATE,

          dproduct.IP_WARRANTY_PRODUCT warrentyTerm,

          TO_CHAR(dproduct.IP_WARRANTY_START_DATE,'DD-MON-YYYY') ipStartDate,

          TO_CHAR(dproduct.IP_WARRANTY_END_DATE,'DD-MON-YYYY') ipEndDate,

          TO_CHAR(aggrement.AGR_END_DATE,'DD-MON-YYYY') AGR_END_DATE,

          TO_CHAR(aggrement.CONTRACT_EXTENDED_DATE,'DD-MON-YYYY') CONTRACT_EXTENDED_DATE,

          aggrlineItem.SERVICE_NAME,aggrItem.RESPONSE_LEVEL,

          dproduct.IP_SERIAL_NUMBER,

          dproduct.IP_SYSTEM_NAME sys_hostname,

          dproduct.IP_OS_VERSION Sys_Version,

          site.SITE_ID,

          site.SITE_NAME

      FROM 

          ASUPDSS.O_CUSTOMER cust,

		  ASUPDSS.O_SITE site,

          ASUPDSS.F_INSTALLED_PRODUCT_AGREEMENT fproduct,          

          Asupdss.D_Installed_Product dproduct,   

          ASUPDSS.D_AGREEMENT aggrement,

          asupdss.D_AGREEMENT_ITEM aggrItem,

          ASUPDSS.D_AGREEMENT_ITEM_SERVICE aggrlineItem

      WHERE  

          cust.CUSTOMER_KEY  = dproduct.OWNER_CUSTOMER_KEY

          AND site.SITE_KEY = dproduct.INSTALLED_AT_SITE_KEY

          AND fproduct.INSTALLED_PRODUCT_KEY = dproduct.INSTALLED_PRODUCT_KEY

          AND fproduct.AGREEMENT_KEY = aggrement.AGREEMENT_KEY

          AND fproduct.AGREEMENT_ITEM_KEY = aggrItem.AGREEMENT_ITEM_KEY

          AND fproduct.AGREEMENT_ITEM_SVC_KEY = aggrlineItem.AGREEMENT_ITEM_SVC_KEY

          AND cust.customer_id =  (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    ELSE

      OPEN p_contract_dtl FOR 

      Select * from Dual where 1=2;

    END IF;

    

    

    

    

    IF is_capacityforecast_required <> 0 THEN

      OPEN p_capacity_forecast FOR    

          SELECT SUM(PHY_RAW_CAPAC) PHY_RAW_CAPAC, SUM(EFF_USED_CAPAC) EFF_USED_CAPAC,SUM(PHY_USED_CAPAC) PHY_USED_CAPAC,SUM(PHY_USABLE_CAPAC) PHY_USABLE_CAPAC,DATAPOINT

          FROM (

            SELECT * FROM (

                SELECT CALC.SERIAL_NUMBER,

                       CALC.PHY_RAW_CAPAC,

                       CALC.EFF_USED_CAPAC,

                       CALC.PHY_USED_CAPAC,

                       CALC.PHY_USABLE_CAPAC,

                       CALC.ASUP_ID,

                       CASE 

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 10,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 0,   'YYYYMMDD') || '99999999')  THEN 'datapoint0'  

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 95,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 85,  'YYYYMMDD') || '99999999')  THEN 'datapoint1'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999')  THEN 'datapoint2'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999')  THEN 'datapoint3'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999')  THEN 'datapoint4'

                      END

                      DATAPOINT

                FROM ASUPDSS.ASUP_SE_CALCULATION CALC

                WHERE CALC.SERIAL_NUMBER IN( 

                         SELECT MV.SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV MV

                          WHERE MV.CUSTOMER_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

                     )

                ) A 

            WHERE A.DATAPOINT IS NOT NULL

            GROUP BY A.SERIAL_NUMBER,A.DATAPOINT,A.PHY_RAW_CAPAC,A.EFF_USED_CAPAC,A.PHY_USED_CAPAC,A.PHY_USABLE_CAPAC,A.ASUP_ID

            HAVING A.ASUP_ID = MAX(A.ASUP_ID)

            ORDER BY A.SERIAL_NUMBER

          ) 

          GROUP BY  DATAPOINT;



    ELSE

      OPEN p_capacity_forecast FOR 

      Select * from Dual where 1=2;

    END IF;

    

    

    

    IF is_endofsupport_required <> 0 THEN

      OPEN p_epic_controller FOR

         SELECT mv.Sys_Serial_No Sys_Serialno,

                 mv.Hostname Sys_Hostname,

                 contrlInfo.PLATFORM_MODEL_ID,

                 contrlInfo.PLATFORM_MODEL,

                 contrlInfo.EOSHW

          FROM   ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

                 asupdss.O_EPIC_PLATFORM_MODEL_EOS_INFO contrlInfo

          WHERE 

                 mv.sys_model = contrlInfo.PLATFORM_MODEL

                 AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

    

      OPEN p_epic_shelf FOR

          SELECT DISTINCT mv.Hostname Sys_Hostname,

               mv.Sys_Serial_No Sys_Serialno,              

               Shelf.Shelf_Serial_No Shelf_Serialno,

               Dmod.Smod_Type Smod_Type,

               eosshelf.SHELF_MODULE_ID,

               eosshelf.SHELF_MODULE_NAME,

               eosshelf.EOSHW

          FROM asupdss.d_shelf shelf,

               asupdss.f_asup_shelf_module fmod,

               asupdss.d_shelf_module dmod,

               asupdss.O_EPIC_SHELF_MODULE_EOS_INFO eosshelf,

               asupdss.D_LATEST_WKLY_ASUP_SN_MV mv           

          WHERE shelf.shelf_key=fmod.shelf_key

               AND dmod.smod_key=fmod.smod_key

               AND fmod.asup_id =mv.asup_id

               AND dmod.SMOD_TYPE =eosshelf.SHELF_MODULE_NAME

               AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

    

        OPEN p_epic_disk FOR

            SELECT DISTINCT mv.Hostname Sys_Hostname,

                  mv.Sys_Serial_No Sys_Serialno,               

                  device.dvc_key device_key,          

                  Device.Dvc_Model Sys_Dvc_Model,             

                  device.DVC_TYPE sys_dvc_type,

                  device.DISK_TYPE sys_disk_type,              

                  Device.Dvc_Label Sys_Dvc_Label ,

                  eosInfo.DRIVE_ID driveId,

                  eosInfo.DRIVE_TYPE,

                  eosInfo.DRIVE_MODEL,

                  eosInfo.EOSHW

            FROM Asupdss.D_Device Device,

                  asupdss.f_asup_device fdevice,

                  asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

                  asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE device.dvc_key = fdevice.dvc_key

                  AND device.DVC_MODEL = eosInfo.DRIVE_MODEL

                  AND device.dvc_type  NOT IN  ('LUN')    

                  AND Device.Dvc_LABEL <> 'PARTNER'          

                  AND fdevice.asup_id = mv.asup_id

                  AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

    ELSE

        OPEN p_epic_controller FOR 

        Select * from Dual where 1=2;

        

        OPEN p_epic_shelf FOR 

        Select * from Dual where 1=2;

        

        OPEN p_epic_disk FOR 

        Select * from Dual where 1=2; 

    END IF;

    

    IF is_osfirmware_required <> 0 THEN

        OPEN p_osfw_system FOR

          SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

              mv.hostname sys_hostname,

              mv.sys_model sys_model,

              mv.SYS_VERSION sys_version,

              mv.SYS_OPERATING_MODE sys_mode,          

              latest_fw_info.display sys_display

          FROM         

              ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE       

              latest_fw_info.Object_name = 'Running Recommended ONTAP versions'

              AND latest_fw_info.model = mv.sys_model

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

        

        OPEN p_osfw_motherboard FOR

            SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

                  mv.hostname sys_hostname,

                  mv.sys_model sys_model,

                  dmb.MB_MODEL mb_model,

                  dmb.MB_FW_REV mb_fw_rev,           

                  latest_fw_info.display sys_display  

            FROM  asupdss.F_ASUP_MOTHERBOARD fmb,

                  asupdss.D_MOTHERBOARD dmb,

                  ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

                  asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE fmb.asup_id = mv.asup_id

                AND fmb.motherboard_key = dmb.motherboard_key

                AND latest_fw_info.Object_name = 'Motherboard Firmware Up to Date'

                AND latest_fw_info.model = dmb.MB_MODEL

                AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    

      OPEN p_osfw_shelf FOR

          SELECT mv.Hostname Sys_Hostname,

               mv.Sys_Serial_No Sys_Serialno,

               shelf.SHELF_TYPE shelf_type,

               count(distinct Shelf.Shelf_Serial_No) shelf_cnt,             

               dmod.Smod_Type Smod_Type,               

               count(distinct dmod.smod_serial_no) smod_cnt,

               dmod.smod_fw_rev smod_fw_rev,

               myasup_info.display display

          FROM asupdss.d_shelf shelf,

               asupdss.f_asup_shelf_module fmod,

               asupdss.d_shelf_module dmod,

               asupdss.O_MYASUP_FW_INFO myasup_info,

               asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE shelf.shelf_key=fmod.shelf_key

              AND dmod.smod_key=fmod.smod_key

              AND fmod.asup_id =mv.asup_id

              AND myasup_info.object_name = 'Shelf FW version and Shelf Type'

              AND dmod.Smod_Type = myasup_info.model     

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

              group by mv.Sys_Serial_No,mv.Hostname,shelf.SHELF_TYPE,Dmod.Smod_Type,dmod.smod_fw_rev,myasup_info.display;	



      OPEN p_osfw_disk FOR

          SELECT DISTINCT mv.Hostname Sys_Hostname,

              mv.Sys_Serial_No Sys_Serialno,                 

              device.DVC_FW_REV DVC_FW_REV, 

              count(device.DVC_FW_REV) fw_cnt,

              Device.Dvc_Model Sys_Dvc_Model,

              latest_fw_info.display display

          FROM Asupdss.D_Device Device,

              asupdss.f_asup_device fdevice,

              asupdss.O_MYASUP_FW_INFO latest_fw_info,

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE device.dvc_key = fdevice.dvc_key          

              AND device.dvc_type  NOT IN  ('LUN')    

              AND Device.Dvc_LABEL <> 'PARTNER'          

              AND fdevice.asup_id = mv.asup_id         

              AND latest_fw_info.object_name='Disk Firmware Up to Date'

              AND latest_fw_info.model = device.Dvc_Model

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

              GROUP BY mv.Sys_Serial_No,mv.Hostname,device.DVC_FW_REV,device.Dvc_Model,latest_fw_info.display

              order by mv.Sys_Serial_No,device.Dvc_Model;	

        

      OPEN p_osfw_rlm FOR 

          SELECT DISTINCT mv.Hostname Sys_Hostname,

              mv.Sys_Serial_No Sys_Serialno,   

              mv.sys_model sys_model,

              adp.hba_type hba_type,

              adp.IS_RSA_ENABLED is_rsa_active,

              adp.HBA_FW_REV hba_fw,   

              latest_fw_info.display display

          FROM Asupdss.D_ADAPTER adp,

              asupdss.f_asup_interface fif,

              asupdss.O_MYASUP_FW_INFO latest_fw_info,      

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE  adp.hba_type IN ('RLM','SP','BMC')

              AND Adp.Adapter_Key = Fif.Adapter_Key 

              AND fif.asup_id = mv.asup_id

              --AND mv.sys_serial_no IN ('700000309048')

              AND latest_fw_info.object_name='Remote Management'

              AND latest_fw_info.model = mv.sys_model

              AND latest_fw_info.type = adp.hba_type

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

    ELSE

    

      OPEN p_osfw_system FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_motherboard FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_shelf FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_disk FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_rlm FOR 

      Select * from Dual where 1=2;

    

    END IF;

    

    IF is_davehitz_required <> 0 THEN

    OPEN  p_dave_hitz FOR 

       SELECT SUM(eba.TOTAL_SATA_STORAGE_TB) TOTAL_SATA_STORAGE_TB,

            SUM(eba.TOTAL_RAW_STORAGE_TB) TOTAL_RAW_STORAGE_TB,

            SUM(eba.FLASHCACHE_ENABLED_GB) FLASHCACHE_ENABLED_GB,

            SUM(eba.FLASHCACHE_NOT_ENABLED_GB) FLASHCACHE_NOT_ENABLED_GB,

            SUM(eba.DEDUPE_ENABLED_STORAGE_TB) DEDUPE_ENABLED_STORAGE_TB,

            SUM(eba.DEDUPE_NOT_ENABLED_TB) DEDUPE_NOT_ENABLED_TB,

            SUM(voldata.VOL_COMPRESSED_TB) VOL_COMPRESSED_TB,

            SUM(voldata.VOL_UNCOMPRESSED_TB) VOL_UNCOMPRESSED_TB,

            eba.calendar_month

            --voldata.ASUP_MONTH

       FROM asupdss.O_EBA_EFFICIENCY_DETAILS eba,

          asupdss.T_AGG_VOL_DATA voldata

       WHERE eba.Installed_at_cust_id_sap = voldata.CUSTOMER_ID

        AND eba.sys_serial_no = voldata.sys_serial_no

       AND asup_month = replace(calendar_month, 'M', '') 

       --and eba.Installed_at_cust_id_sap = '1029281'

       AND  voldata.CUSTOMER_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

       group by  eba.calendar_month;--, voldata.ASUP_MONTH;

    

    ELSE

    

    OPEN p_dave_hitz FOR 

    Select * from Dual where 1=2;

    

    END IF;

    

    IF is_performance_required <> 0 THEN

      OPEN p_perf_summary FOR

            SELECT perf.Sys_Serial_No Sys_Serial_No,

                 mv.hostname,

                 sum(fcp_ops) / nullif(sum(fcp_ct),0) fcp_ops,

                 sum(iscsi_ops) / nullif(sum(iscsi_ct),0) iscsi_ops,

                 sum(disk_busy) / nullif(sum(disk_ct),0) disk_busy,

                 sum(cifs_ops) / nullif(sum(cifs_ct),0) cifs_ops,

                 sum(nfs_ops) / nullif(sum(nfs_ct),0) nfs_ops,

                 sum(cpu_busy) / nullif(sum(cpu_ct),0) cpu_busy

            FROM asupdss.T_HIVE_DATA_CM perf,

                 asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE perf.Sys_Serial_No = mv.Sys_Serial_No

            AND perf.dt BETWEEN sysdate-30 AND sysdate

            AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

            GROUP BY perf.Sys_Serial_No,mv.hostname;

    ELSE

      OPEN p_perf_summary FOR 

      Select * from Dual where 1=2;

    END IF;

    

    IF is_storefficiency_required <> 0 THEN

      OPEN p_fear_disk FOR

         SELECT DISK_TYPE dvc_disk_type, 

                 DVC_LABEL dvc_label, 

                 DVC_MODEL dvc_model, 

                 DVC_PHY_SIZE_MB dvc_usable_mb,

                 draidgp.RG_PATH_NAME,

                 eosInfo.MARKETING_CAPACITY_GB,

                 eosInfo.DRIVE_SPEED,

                 mv.sys_serial_no sys_serial_no

          FROM  asupdss.D_DEVICE ddvc, 

                asupdss.F_ASUP_DEVICE fdvc,

                asupdss.D_RAID_GROUP draidgp ,

                asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

                asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fdvc.dvc_key =  ddvc.dvc_key

                AND fdvc.rg_key = draidgp.rg_key            

                AND ddvc.DVC_MODEL = eosInfo.DRIVE_MODEL

                AND ddvc.DVC_LABEL <> 'PARTNER' AND ddvc.DVC_LABEL <> 'UNOWNED'

                AND fdvc.asup_id= mv.asup_id --2009122617310247;

                AND fdvc.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    

      OPEN p_fear_volume FOR 

    

          SELECT fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no sys_serial_no,count(fsnap.volume_key) as snapshotcnt,round(sysdate-max(dsnap.SNAPSHOT_DATE)) noOfDays 

          FROM asupdss.F_ASUP_VOLUME fvol, asupdss.D_VOLUME dvol, asupdss.D_VOLUME_INDICATOR dvolind,asupdss.f_asup_snapshot fsnap,asupdss.d_snapshot dsnap,

             asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fvol.vol_key =  dvol.vol_key

              AND fsnap.SNAPSHOT_KEY  = dsnap.SNAPSHOT_KEY

              AND fsnap.volume_key(+) = fvol.vol_key              

              AND fvol.VOL_INDICATOR_KEY =  dvolind.VOL_INDICATOR_KEY

              AND fvol.asup_id = mv.asup_id

              AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND fsnap.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

          GROUP BY fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no;

              

      OPEN p_fear_aggregate FOR 

          SELECT daggind.AGGR_SYNCMIRRORED_IND , fagg.aggr_used_kb , dagg.aggr_name, dagg.aggr_path_name , fagg.aggr_avail_kb, fagg.aggr_savail_kb,

                dagg.aggr_raid_type,dagg.aggr_type,mv.sys_serial_no sys_serial_no  

          FROM asupdss.F_ASUP_AGGREGATE fagg, asupdss.D_AGGREGATE dagg,asupdss.D_AGGREGATE_INDICATOR daggind, asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fagg.aggr_key =  dagg.aggr_key

          AND fagg.AGGR_INDICATOR_KEY = daggind.AGGR_INDICATOR_KEY

          AND fagg.asup_id =  mv.asup_id

          AND fagg.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

          AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

          

      OPEN p_fear_lun FOR                

        SELECT dlun.LUN_NAME,dlun.vol_name,dlun.lun_path_name,flun.LUN_SIZE_BYTES,dlun.LUN_RESERVE_IND,dqtree.qt_style qt_styles,mv.sys_serial_no sys_serial_no

        FROM asupdss.F_ASUP_LUN flun ,asupdss.D_LUN dlun,asupdss.D_QTREE dqtree,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE  flun.LUN_KEY = dlun.LUN_KEY

        AND flun.QTREE_KEY = dqtree.QTREE_KEY

        AND flun.asup_id=mv.asup_id

        AND flun.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

        AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);  

    

       OPEN p_fear_vol_option FOR

             SELECT DISTINCT fvol.vol_key,voption.vol_Option_Value vol_Option_Value, voption.vol_Option_Name vol_Option_Name,mv.sys_serial_no sys_serial_no  

              FROM asupdss.D_VOLUME_OPTION voption,Asupdss.D_VOLUME dvol,Asupdss.F_Asup_VOLUME Fvol,

                   ASUPDSS.B_VOLUME_OPTION_GROUP bvgroup,

                   asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

              WHERE voption.VOL_OPTION_NAME IN('guarantee','snapshotAutodeleteSettings.state','volumeAutosizeSettings.state','volumeAutosizeSettings.state','snaplock_enterprise','snaplock_compliance')

              AND fvol.vol_key = dvol.vol_key

              AND fvol.VOLUME_OPTION_GROUP_KEY =  bvgroup.VOLUME_OPTION_GROUP_KEY

              AND voption.vol_Option_Key = bvgroup.vol_Option_Key

              AND fvol.asup_id = mv.asup_id

              AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

          OPEN p_fear_system_option FOR

            SELECT sys.sys_serial_no,Doption.System_Option_Value Sys_Option_Value, Doption.System_Option_Name Sys_Option_Name,mv.sys_serial_no sys_serial_no

            FROM asupdss.d_system_option doption,Asupdss.D_System Sys,Asupdss.F_Asup_System F,

                  ASUPDSS.B_SYSTEM_OPTION_GROUP bgroup,

                  asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE  Doption.system_option_name IN('cf.mode','cf.remote_syncmirror.enable')

            AND F.system_key = sys.system_key

            AND F.SYSTEM_OPTION_GROUP_KEY =  bgroup.SYSTEM_OPTION_GROUP_KEY

            AND Doption.System_Option_Key = Bgroup.System_Option_Key

            AND f.asup_id = mv.asup_id

            AND f.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

            AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

    OPEN p_fear_license FOR

        SELECT a.sys_serialno sys_serialno,a.Sys_Version,a.sys_systemid sys_systemid,a.sys_srvc_nam,a.LIC_INFO

        FROM 

        (SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,ser.svc_name as sys_srvc_nam,

        CASE

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NULL  THEN 'S'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE < SVC_LIC_EXP_DATE  THEN 'E'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE > SVC_LIC_EXP_DATE  THEN TO_CHAR (SVC_LIC_EXP_DATE)

          WHEN SVC_SITE_LIC_IND <> 'T' AND SVC_LIC_IND  = 'T' THEN 'Y'

          ELSE 'N' END AS LIC_INFO

              FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_service ser,asupdss.f_asup_service fser,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

              WHERE ser.svc_name <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND ser.svc_key = fser.svc_key

                   AND fser.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND fser.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

                   --and mv.sys_serial_no in ('701409000318','200000269901')

        UNION

        SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,dcl.PACKAGE_NAME as sys_srvc_nam,

        CASE

        WHEN dcl.license_type = 'license' THEN 'Y' WHEN dcl.license_type = 'site' THEN 'S' 

        WHEN dcl.license_type = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate < to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN 'E'

        WHEN dcl.license_type   = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate > to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN TO_CHAR (to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss'))

        ELSE 'N' END AS LIC_INFO

          FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_cluster_license  dcl,asupdss.f_asup_cluster_license  fcl,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

                WHERE dcl.PACKAGE_NAME <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND dcl.CLUSTER_LICENSE_KEY = fcl.CLUSTER_LICENSE_KEY

                   AND fcl.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   -- and mv.sys_serial_no in ('701409000318','200000269901')

                   AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

        )a;

        

    ELSE

    

    OPEN p_fear_disk FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_volume FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_aggregate FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_lun FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_vol_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_system_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_license FOR 

    Select * from Dual where 1=2;

    

    END IF;

    

    OPEN p_invalid_serial_no FOR

      SELECT DISTINCT DPRODUCT.IP_SERIAL_NUMBER SYS_SERIAL_NO

      FROM ASUPDSS.O_CUSTOMER CUST, ASUPDSS.D_INSTALLED_PRODUCT DPRODUCT

      WHERE CUST.CUSTOMER_KEY = DPRODUCT.OWNER_CUSTOMER_KEY

      AND DPRODUCT.IP_STATUS = 'ACTIVE'

      AND CUST.CURRENT_ROW_IND = 'T'

      AND CUST.CUSTOMER_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

      MINUS 

      SELECT MV.SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv 

      WHERE MV.CUSTOMER_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    

    

END sp_customer_system_details;



PROCEDURE sp_site_system_details(

    p_job_id   IN NUMBER,

    p_key_name IN VARCHAR2,

    p_sheets IN VARCHAR2,

    p_system OUT nocopy sys_refcursor,

    p_customer_site_info OUT nocopy sys_refcursor,   

    p_case OUT nocopy sys_refcursor,

    p_system_age OUT nocopy sys_refcursor,

    p_contract_dtl OUT nocopy sys_refcursor,

    p_capacity_forecast OUT nocopy sys_refcursor,

    p_epic_controller OUT nocopy sys_refcursor,

    p_epic_shelf OUT nocopy sys_refcursor,

    p_epic_disk OUT nocopy sys_refcursor,

    p_osfw_system OUT nocopy sys_refcursor,

    p_osfw_motherboard OUT nocopy sys_refcursor,

    p_osfw_shelf OUT nocopy sys_refcursor,

    p_osfw_disk OUT nocopy sys_refcursor,

    p_osfw_rlm OUT nocopy sys_refcursor,

    p_dave_hitz OUT nocopy sys_refcursor,

    p_perf_summary OUT nocopy sys_refcursor,

    p_fear_disk OUT nocopy sys_refcursor,

    p_fear_volume OUT nocopy sys_refcursor,

    p_fear_aggregate OUT nocopy sys_refcursor,

    p_fear_lun OUT nocopy sys_refcursor,

    p_fear_vol_option OUT nocopy sys_refcursor,

    p_fear_system_option OUT nocopy sys_refcursor,

    p_fear_license OUT nocopy sys_refcursor,

    p_invalid_serial_no OUT nocopy sys_refcursor)

AS

  v_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 15, 'YYYYMMDD') || '00000000');

  v_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD') || '99999999');

  v_datapoint1_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 95, 'YYYYMMDD') || '00000000');

  v_datapoint1_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 85, 'YYYYMMDD') || '99999999');

  v_datapoint2_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000');

  v_datapoint2_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999');

  v_datapoint3_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000');

  v_datapoint3_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999');

  v_datapoint4_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000');

  v_datapoint4_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999');

  

  v_enabled_report VARCHAR2(3000) := p_sheets;

  --v_enabled_report VARCHAR2(3000) := 'isSystemInventoryReport,isDaveHitzReport,isStorageEfficiencyReport';

  is_inventory_required NUMBER;

  is_case_required NUMBER;

  is_davehitz_required NUMBER;

  is_storefficiency_required NUMBER;

  is_contract_required NUMBER;

  is_endofsupport_required NUMBER;

  is_systemaging_required NUMBER;

  is_osfirmware_required NUMBER;

  is_capacityforecast_required NUMBER;

  is_performance_required  NUMBER;

  

BEGIN



  is_inventory_required := INSTR(v_enabled_report, 'isSystemInventoryReport');

  is_case_required := INSTR(v_enabled_report, 'isSupportCaseReport');

  is_davehitz_required := INSTR(v_enabled_report, 'isDaveHitzReport');

  is_storefficiency_required:= INSTR(v_enabled_report, 'isStorageEfficiencyReport');

  is_contract_required:= INSTR(v_enabled_report, 'isContractDetails');

  is_endofsupport_required:= INSTR(v_enabled_report, 'isEndOfSupportRedar');

  is_systemaging_required:= INSTR(v_enabled_report, 'isSystemAgingReport');

  is_osfirmware_required:= INSTR(v_enabled_report, 'isOSFirmwareDetailReport');

  is_capacityforecast_required:= INSTR(v_enabled_report, 'isCapacityForecastReport');

  is_performance_required:= INSTR(v_enabled_report, 'isPerformanceSummaryReport');



  IF is_inventory_required <> 0 THEN

    OPEN p_system FOR 

      SELECT mv.Sys_Serial_No Sys_Serialno,

           mv.hostname sys_hostname,

           mv.asup_id asupid,

           mv.Sys_Model Sys_Model,

           mv.Sys_Version Sys_Version,

           mv.secure_flag secure_flag,

           TO_CHAR(mv.Asup_Received_Date,'DD-MON-YYYY') Sys_Revdate 

      FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

  ELSE

    OPEN p_system FOR 

    Select * from Dual where 1=2;

  END IF;

    

  OPEN p_customer_site_info FOR 

    SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

        mv.CUSTOMER_ID,

        mv.CUSTOMER_NAME,

        mv.SITE_ID,      

        mv.SITE_NAME

    FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

        Asupdss.O_CUSTOMER cust,

        Asupdss.O_SITE site 

    WHERE 

      cust.CUSTOMER_ID        = mv.CUSTOMER_ID

      AND site.SITE_ID              = mv.SITE_ID

      --and cust.customer_id = '1441509'

      AND cust.current_row_ind='T'

      AND site.current_row_ind='T'

      AND site.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

     

  IF is_case_required <> 0 THEN

    OPEN p_case FOR

        SELECT dcase.case_id,dcase.create_date,dcase.STATUS_TEXT status,dcase.CURRENT_PRIORITY

        FROM asupdss.D_INSTALLED_PRODUCT dprod, asupdss.f_case fcase, asupdss.D_Case dcase

        WHERE dprod.INSTALLED_PRODUCT_KEY = fcase.INSTALLED_PRODUCT_KEY

        AND fcase.case_key = dcase.case_key

        AND dprod.IP_SERIAL_NUMBER IN (

          SELECT distinct dproduct.IP_SERIAL_NUMBER

          FROM O_SITE SITE, Asupdss.D_Installed_Product Dproduct

          WHERE SITE.SITE_KEY            = dproduct.INSTALLED_AT_SITE_KEY

          AND site.current_row_ind = 'T'

          AND SITE.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

        );

  ELSE

    OPEN p_case FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_systemaging_required <> 0 THEN

    OPEN p_system_age FOR    

      SELECT distinct dproduct.IP_SERIAL_NUMBER, dproduct.IP_SYSTEM_NAME hostname, dproduct.IP_SHIP_DATE

      FROM O_SITE SITE, Asupdss.D_Installed_Product Dproduct

      WHERE SITE.SITE_KEY            = dproduct.INSTALLED_AT_SITE_KEY

      AND site.current_row_ind = 'T'

      AND SITE.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

      ORDER BY IP_SHIP_DATE;

  ELSE

    OPEN p_system_age FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_contract_required <> 0 THEN

    OPEN p_contract_dtl FOR    

      SELECT aggrement.AGREEMENT_ID,

          aggrement.ORACLESALESORDER,

          TO_CHAR(aggrement.AGR_START_DATE,'DD-MON-YYYY') AGR_START_DATE,

          dproduct.IP_WARRANTY_PRODUCT warrentyTerm,

          TO_CHAR(dproduct.IP_WARRANTY_START_DATE,'DD-MON-YYYY') ipStartDate,

          TO_CHAR(dproduct.IP_WARRANTY_END_DATE,'DD-MON-YYYY') ipEndDate,

          TO_CHAR(aggrement.AGR_END_DATE,'DD-MON-YYYY') AGR_END_DATE,

          TO_CHAR(aggrement.CONTRACT_EXTENDED_DATE,'DD-MON-YYYY') CONTRACT_EXTENDED_DATE,

          aggrlineItem.SERVICE_NAME,aggrItem.RESPONSE_LEVEL,

          dproduct.IP_SERIAL_NUMBER,

          dproduct.IP_SYSTEM_NAME sys_hostname,

          dproduct.IP_OS_VERSION Sys_Version,

          site.SITE_ID,

          site.SITE_NAME

      FROM 

          ASUPDSS.O_CUSTOMER cust,

		  ASUPDSS.O_SITE site,

          ASUPDSS.F_INSTALLED_PRODUCT_AGREEMENT fproduct,          

          Asupdss.D_Installed_Product dproduct,   

          ASUPDSS.D_AGREEMENT aggrement,

          asupdss.D_AGREEMENT_ITEM aggrItem,

          ASUPDSS.D_AGREEMENT_ITEM_SERVICE aggrlineItem

      WHERE  

          cust.CUSTOMER_KEY  = dproduct.OWNER_CUSTOMER_KEY

          AND site.SITE_KEY = dproduct.INSTALLED_AT_SITE_KEY

          AND fproduct.INSTALLED_PRODUCT_KEY = dproduct.INSTALLED_PRODUCT_KEY

          AND fproduct.AGREEMENT_KEY = aggrement.AGREEMENT_KEY

          AND fproduct.AGREEMENT_ITEM_KEY = aggrItem.AGREEMENT_ITEM_KEY

          AND fproduct.AGREEMENT_ITEM_SVC_KEY = aggrlineItem.AGREEMENT_ITEM_SVC_KEY

          AND site.SITE_ID =  (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

  ELSE

      OPEN p_contract_dtl FOR 

      Select * from Dual where 1=2;

  END IF;

    

  IF is_capacityforecast_required <> 0 THEN

    OPEN p_capacity_forecast FOR    

      SELECT SUM(PHY_RAW_CAPAC) PHY_RAW_CAPAC, SUM(EFF_USED_CAPAC) EFF_USED_CAPAC,SUM(PHY_USED_CAPAC) PHY_USED_CAPAC,SUM(PHY_USABLE_CAPAC) PHY_USABLE_CAPAC,DATAPOINT

      FROM (

        SELECT * FROM (

            SELECT CALC.SERIAL_NUMBER,

                   CALC.PHY_RAW_CAPAC,

                   CALC.EFF_USED_CAPAC,

                   CALC.PHY_USED_CAPAC,

                   CALC.PHY_USABLE_CAPAC,

                   CALC.ASUP_ID,

                   CASE 

                      WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 10,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 0,   'YYYYMMDD') || '99999999')  THEN 'datapoint0'  

                      WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 95,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 85,  'YYYYMMDD') || '99999999')  THEN 'datapoint1'

                      WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999')  THEN 'datapoint2'

                      WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999')  THEN 'datapoint3'

                      WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999')  THEN 'datapoint4'

                  END

                  DATAPOINT

            FROM ASUPDSS.ASUP_SE_CALCULATION CALC

            WHERE CALC.SERIAL_NUMBER IN( 

                     SELECT MV.SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV MV

                      WHERE MV.SITE_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

                 )

            ) A 

        WHERE A.DATAPOINT IS NOT NULL

        GROUP BY A.SERIAL_NUMBER,A.DATAPOINT,A.PHY_RAW_CAPAC,A.EFF_USED_CAPAC,A.PHY_USED_CAPAC,A.PHY_USABLE_CAPAC,A.ASUP_ID

        HAVING A.ASUP_ID = MAX(A.ASUP_ID)

        ORDER BY A.SERIAL_NUMBER

      ) 

      GROUP BY  DATAPOINT; 

  ELSE

    OPEN p_capacity_forecast FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_endofsupport_required <> 0 THEN

    

    OPEN p_epic_controller FOR    

      SELECT mv.Sys_Serial_No Sys_Serialno,

             mv.Hostname Sys_Hostname,

             contrlInfo.PLATFORM_MODEL_ID,

             contrlInfo.PLATFORM_MODEL,

             contrlInfo.EOSHW

      FROM   ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

             asupdss.O_EPIC_PLATFORM_MODEL_EOS_INFO contrlInfo

      WHERE 

             mv.sys_model = contrlInfo.PLATFORM_MODEL

             AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

  

    OPEN p_epic_shelf FOR

      SELECT DISTINCT mv.Hostname Sys_Hostname,

           mv.Sys_Serial_No Sys_Serialno,              

           Shelf.Shelf_Serial_No Shelf_Serialno,

           Dmod.Smod_Type Smod_Type,

           eosshelf.SHELF_MODULE_ID,

           eosshelf.SHELF_MODULE_NAME,

           eosshelf.EOSHW

      FROM asupdss.d_shelf shelf,

           asupdss.f_asup_shelf_module fmod,

           asupdss.d_shelf_module dmod,

           asupdss.O_EPIC_SHELF_MODULE_EOS_INFO eosshelf,

           asupdss.D_LATEST_WKLY_ASUP_SN_MV mv           

      WHERE shelf.shelf_key=fmod.shelf_key

           AND dmod.smod_key=fmod.smod_key

           AND fmod.asup_id =mv.asup_id

           AND dmod.SMOD_TYPE =eosshelf.SHELF_MODULE_NAME

           AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

    

    OPEN p_epic_disk FOR

      SELECT DISTINCT mv.Hostname Sys_Hostname,

            mv.Sys_Serial_No Sys_Serialno,               

            device.dvc_key device_key,          

            Device.Dvc_Model Sys_Dvc_Model,             

            device.DVC_TYPE sys_dvc_type,

            device.DISK_TYPE sys_disk_type,              

            Device.Dvc_Label Sys_Dvc_Label ,

            eosInfo.DRIVE_ID driveId,

            eosInfo.DRIVE_TYPE,

            eosInfo.DRIVE_MODEL,

            eosInfo.EOSHW

      FROM Asupdss.D_Device Device,

            asupdss.f_asup_device fdevice,

            asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

            asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE device.dvc_key = fdevice.dvc_key

            AND device.DVC_MODEL = eosInfo.DRIVE_MODEL

            AND device.dvc_type  NOT IN  ('LUN')    

            AND Device.Dvc_LABEL <> 'PARTNER'          

            AND fdevice.asup_id = mv.asup_id

            AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

  ELSE

    OPEN p_epic_controller FOR 

    Select * from Dual where 1=2;

    

    OPEN p_epic_shelf FOR 

    Select * from Dual where 1=2;

    

    OPEN p_epic_disk FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_osfirmware_required <> 0 THEN

    OPEN p_osfw_system FOR

      SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

          mv.hostname sys_hostname,

          mv.sys_model sys_model,

          mv.SYS_VERSION sys_version,

          mv.SYS_OPERATING_MODE sys_mode,          

          latest_fw_info.display sys_display

      FROM         

          ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

          asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE       

          latest_fw_info.Object_name = 'Running Recommended ONTAP versions'

          AND latest_fw_info.model = mv.sys_model

          AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

        

    OPEN p_osfw_motherboard FOR

      SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

            mv.hostname sys_hostname,

            mv.sys_model sys_model,

            dmb.MB_MODEL mb_model,

            dmb.MB_FW_REV mb_fw_rev,           

            latest_fw_info.display sys_display  

      FROM  asupdss.F_ASUP_MOTHERBOARD fmb,

            asupdss.D_MOTHERBOARD dmb,

            ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

            asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE fmb.asup_id = mv.asup_id

          AND fmb.motherboard_key = dmb.motherboard_key

          AND latest_fw_info.Object_name = 'Motherboard Firmware Up to Date'

          AND latest_fw_info.model = dmb.MB_MODEL

          AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

    

    OPEN p_osfw_shelf FOR

      SELECT mv.Hostname Sys_Hostname,

           mv.Sys_Serial_No Sys_Serialno,

           shelf.SHELF_TYPE shelf_type,

           count(distinct Shelf.Shelf_Serial_No) shelf_cnt,             

           dmod.Smod_Type Smod_Type,               

           count(distinct dmod.smod_serial_no) smod_cnt,

           dmod.smod_fw_rev smod_fw_rev,

           myasup_info.display display

      FROM asupdss.d_shelf shelf,

           asupdss.f_asup_shelf_module fmod,

           asupdss.d_shelf_module dmod,

           asupdss.O_MYASUP_FW_INFO myasup_info,

           asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE shelf.shelf_key=fmod.shelf_key

          AND dmod.smod_key=fmod.smod_key

          AND fmod.asup_id =mv.asup_id

          AND myasup_info.object_name = 'Shelf FW version and Shelf Type'

          AND dmod.Smod_Type = myasup_info.model     

          AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

          group by mv.Sys_Serial_No,mv.Hostname,shelf.SHELF_TYPE,Dmod.Smod_Type,dmod.smod_fw_rev,myasup_info.display;	



    OPEN p_osfw_disk FOR

      SELECT DISTINCT mv.Hostname Sys_Hostname,

          mv.Sys_Serial_No Sys_Serialno,                 

          device.DVC_FW_REV DVC_FW_REV, 

          count(device.DVC_FW_REV) fw_cnt,

          Device.Dvc_Model Sys_Dvc_Model,

          latest_fw_info.display display

      FROM Asupdss.D_Device Device,

          asupdss.f_asup_device fdevice,

          asupdss.O_MYASUP_FW_INFO latest_fw_info,

          asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE device.dvc_key = fdevice.dvc_key          

          AND device.dvc_type  NOT IN  ('LUN')    

          AND Device.Dvc_LABEL <> 'PARTNER'          

          AND fdevice.asup_id = mv.asup_id         

          AND latest_fw_info.object_name='Disk Firmware Up to Date'

          AND latest_fw_info.model = device.Dvc_Model

          AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

          GROUP BY mv.Sys_Serial_No,mv.Hostname,device.DVC_FW_REV,device.Dvc_Model,latest_fw_info.display

          order by mv.Sys_Serial_No,device.Dvc_Model;	

          

    OPEN p_osfw_rlm FOR

       SELECT DISTINCT mv.Hostname Sys_Hostname,

          mv.Sys_Serial_No Sys_Serialno,   

          mv.sys_model sys_model,

          adp.hba_type hba_type,

          adp.IS_RSA_ENABLED is_rsa_active,

          adp.HBA_FW_REV hba_fw,   

          latest_fw_info.display display

      FROM Asupdss.D_ADAPTER adp,

          asupdss.f_asup_interface fif,

          asupdss.O_MYASUP_FW_INFO latest_fw_info,      

          asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE  adp.hba_type IN ('RLM','SP','BMC')

          AND Adp.Adapter_Key = Fif.Adapter_Key 

          AND fif.asup_id = mv.asup_id

          --AND mv.sys_serial_no IN ('700000309048')

          AND latest_fw_info.object_name='Remote Management'

          AND latest_fw_info.model = mv.sys_model

          AND latest_fw_info.type = adp.hba_type

          AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

  ELSE

    OPEN p_osfw_system FOR 

    Select * from Dual where 1=2;

    

    OPEN p_osfw_motherboard FOR 

    Select * from Dual where 1=2;

    

    OPEN p_osfw_shelf FOR 

    Select * from Dual where 1=2;

    

    OPEN p_osfw_disk FOR 

    Select * from Dual where 1=2;

    

    OPEN p_osfw_rlm FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_davehitz_required <> 0 THEN

    OPEN  p_dave_hitz FOR 

       SELECT SUM(eba.TOTAL_SATA_STORAGE_TB) TOTAL_SATA_STORAGE_TB,

            SUM(eba.TOTAL_RAW_STORAGE_TB) TOTAL_RAW_STORAGE_TB,

            SUM(eba.FLASHCACHE_ENABLED_GB) FLASHCACHE_ENABLED_GB,

            SUM(eba.FLASHCACHE_NOT_ENABLED_GB) FLASHCACHE_NOT_ENABLED_GB,

            SUM(eba.DEDUPE_ENABLED_STORAGE_TB) DEDUPE_ENABLED_STORAGE_TB,

            SUM(eba.DEDUPE_NOT_ENABLED_TB) DEDUPE_NOT_ENABLED_TB,

            SUM(voldata.VOL_COMPRESSED_TB) VOL_COMPRESSED_TB,

            SUM(voldata.VOL_UNCOMPRESSED_TB) VOL_UNCOMPRESSED_TB,

            eba.calendar_month

           -- voldata.ASUP_MONTH

       FROM asupdss.O_EBA_EFFICIENCY_DETAILS eba, asupdss.T_AGG_VOL_DATA voldata

       WHERE eba.INSTALLED_AT_SITE_ID_SAP = voldata.SITE_ID

        AND eba.sys_serial_no = voldata.sys_serial_no

        AND asup_month = replace(calendar_month, 'M', '') 

        --and eba.INSTALLED_AT_SITE_ID_SAP = '1036697'

        AND eba.INSTALLED_AT_SITE_ID_SAP = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

        GROUP BY  eba.calendar_month;--, voldata.ASUP_MONTH ;

  ELSE

    OPEN p_dave_hitz FOR 

    Select * from Dual where 1=2;

  END IF;

    

  IF is_performance_required <> 0 THEN   

    OPEN p_perf_summary FOR

       		  SELECT perf.Sys_Serial_No Sys_Serial_No,

                 mv.hostname,

                 sum(fcp_ops) / nullif(sum(fcp_ct),0) fcp_ops,

                 sum(iscsi_ops) / nullif(sum(iscsi_ct),0) iscsi_ops,

                 sum(disk_busy) / nullif(sum(disk_ct),0) disk_busy,

                 sum(cifs_ops) / nullif(sum(cifs_ct),0) cifs_ops,

                 sum(nfs_ops) / nullif(sum(nfs_ct),0) nfs_ops,

                 sum(cpu_busy) / nullif(sum(cpu_ct),0) cpu_busy

            FROM asupdss.T_HIVE_DATA_CM perf,

                 asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE perf.Sys_Serial_No = mv.Sys_Serial_No

            AND perf.dt BETWEEN sysdate-30 AND sysdate

            AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

            GROUP BY perf.Sys_Serial_No,mv.hostname; 

  ELSE

    OPEN p_perf_summary FOR 

    Select * from Dual where 1=2;  

  END IF;

    

    IF is_storefficiency_required <> 0 THEN  

      OPEN p_fear_disk FOR

         SELECT DISK_TYPE dvc_disk_type, 

                 DVC_LABEL dvc_label, 

                 DVC_MODEL dvc_model, 

                 DVC_PHY_SIZE_MB dvc_usable_mb,

                 draidgp.RG_PATH_NAME,

                 eosInfo.MARKETING_CAPACITY_GB,

                 eosInfo.DRIVE_SPEED,

                 mv.sys_serial_no sys_serial_no

          FROM  asupdss.D_DEVICE ddvc, 

                asupdss.F_ASUP_DEVICE fdvc,

                asupdss.D_RAID_GROUP draidgp ,

                asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

                asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fdvc.dvc_key =  ddvc.dvc_key

                AND fdvc.rg_key = draidgp.rg_key            

                AND ddvc.DVC_MODEL = eosInfo.DRIVE_MODEL

                AND ddvc.DVC_LABEL <> 'PARTNER' AND ddvc.DVC_LABEL <> 'UNOWNED'

                AND fdvc.asup_id= mv.asup_id --2009122617310247;

                AND fdvc.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);		

    

      OPEN p_fear_volume FOR 

          SELECT fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no sys_serial_no,count(fsnap.volume_key) as snapshotcnt,round(sysdate-max(dsnap.SNAPSHOT_DATE)) noOfDays 

          FROM asupdss.F_ASUP_VOLUME fvol, asupdss.D_VOLUME dvol, asupdss.D_VOLUME_INDICATOR dvolind,asupdss.f_asup_snapshot fsnap,asupdss.d_snapshot dsnap,

             asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fvol.vol_key =  dvol.vol_key

              AND fsnap.SNAPSHOT_KEY  = dsnap.SNAPSHOT_KEY

              AND fsnap.volume_key(+) = fvol.vol_key              

              AND fvol.VOL_INDICATOR_KEY =  dvolind.VOL_INDICATOR_KEY

              AND fvol.asup_id = mv.asup_id

              AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND fsnap.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

          GROUP BY fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no;

              

      OPEN p_fear_aggregate FOR 

          SELECT daggind.AGGR_SYNCMIRRORED_IND , fagg.aggr_used_kb , dagg.aggr_name, dagg.aggr_path_name , fagg.aggr_avail_kb, fagg.aggr_savail_kb,

                dagg.aggr_raid_type,dagg.aggr_type,mv.sys_serial_no sys_serial_no  

          FROM asupdss.F_ASUP_AGGREGATE fagg, asupdss.D_AGGREGATE dagg,asupdss.D_AGGREGATE_INDICATOR daggind, asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fagg.aggr_key =  dagg.aggr_key

            AND fagg.AGGR_INDICATOR_KEY = daggind.AGGR_INDICATOR_KEY

            AND fagg.asup_id =  mv.asup_id

            AND fagg.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

            AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

      OPEN p_fear_lun FOR                

          SELECT dlun.LUN_NAME,dlun.vol_name,dlun.lun_path_name,flun.LUN_SIZE_BYTES,dlun.LUN_RESERVE_IND,dqtree.qt_style qt_styles,mv.sys_serial_no sys_serial_no

          FROM asupdss.F_ASUP_LUN flun ,asupdss.D_LUN dlun,asupdss.D_QTREE dqtree,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE  flun.LUN_KEY = dlun.LUN_KEY

            AND flun.QTREE_KEY = dqtree.QTREE_KEY

            AND flun.asup_id=mv.asup_id

            AND flun.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

            AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

    

       OPEN p_fear_vol_option FOR

            SELECT DISTINCT fvol.vol_key,voption.vol_Option_Value vol_Option_Value, voption.vol_Option_Name vol_Option_Name,mv.sys_serial_no sys_serial_no  

            FROM asupdss.D_VOLUME_OPTION voption,Asupdss.D_VOLUME dvol,Asupdss.F_Asup_VOLUME Fvol,

                 ASUPDSS.B_VOLUME_OPTION_GROUP bvgroup,

                 asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE voption.VOL_OPTION_NAME IN('guarantee','snapshotAutodeleteSettings.state','volumeAutosizeSettings.state','volumeAutosizeSettings.state','snaplock_enterprise','snaplock_compliance')

              AND fvol.vol_key = dvol.vol_key

              AND fvol.VOLUME_OPTION_GROUP_KEY =  bvgroup.VOLUME_OPTION_GROUP_KEY

              AND voption.vol_Option_Key = bvgroup.vol_Option_Key

              AND fvol.asup_id = mv.asup_id

              AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

          

      OPEN p_fear_system_option FOR

          SELECT sys.sys_serial_no,Doption.System_Option_Value Sys_Option_Value, Doption.System_Option_Name Sys_Option_Name,mv.sys_serial_no sys_serial_no

          FROM asupdss.d_system_option doption,Asupdss.D_System Sys,Asupdss.F_Asup_System F,

                ASUPDSS.B_SYSTEM_OPTION_GROUP bgroup,

                asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE  Doption.system_option_name IN('cf.mode','cf.remote_syncmirror.enable')

            AND F.system_key = sys.system_key

            AND F.SYSTEM_OPTION_GROUP_KEY =  bgroup.SYSTEM_OPTION_GROUP_KEY

            AND Doption.System_Option_Key = Bgroup.System_Option_Key

            AND f.asup_id = mv.asup_id

            AND f.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

            AND mv.customer_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

    OPEN p_fear_license FOR

        SELECT a.sys_serialno sys_serialno,a.Sys_Version,a.sys_systemid sys_systemid,a.sys_srvc_nam,a.LIC_INFO

        FROM 

        (SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,ser.svc_name as sys_srvc_nam,

        CASE

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NULL  THEN 'S'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE < SVC_LIC_EXP_DATE  THEN 'E'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE > SVC_LIC_EXP_DATE  THEN TO_CHAR (SVC_LIC_EXP_DATE)

          WHEN SVC_SITE_LIC_IND <> 'T' AND SVC_LIC_IND  = 'T' THEN 'Y'

          ELSE 'N' END AS LIC_INFO

              FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_service ser,asupdss.f_asup_service fser,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

              WHERE ser.svc_name <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND ser.svc_key = fser.svc_key

                   AND fser.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND fser.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

                   --and mv.sys_serial_no in ('701409000318','200000269901')

        UNION

        SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,dcl.PACKAGE_NAME as sys_srvc_nam,

        CASE

        WHEN dcl.license_type = 'license' THEN 'Y' WHEN dcl.license_type = 'site' THEN 'S' 

        WHEN dcl.license_type = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate < to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN 'E'

        WHEN dcl.license_type   = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate > to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN TO_CHAR (to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss'))

        ELSE 'N' END AS LIC_INFO

          FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_cluster_license  dcl,asupdss.f_asup_cluster_license  fcl,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

                WHERE dcl.PACKAGE_NAME <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND dcl.CLUSTER_LICENSE_KEY = fcl.CLUSTER_LICENSE_KEY

                   AND fcl.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   -- and mv.sys_serial_no in ('701409000318','200000269901')

                   AND mv.site_id = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

        )a;

    

  ELSE

    OPEN p_fear_disk FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_volume FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_aggregate FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_lun FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_vol_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_system_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_license FOR 

    Select * from Dual where 1=2;   

  END IF;

  

     OPEN p_invalid_serial_no FOR

        SELECT DISTINCT DPRODUCT.IP_SERIAL_NUMBER SYS_SERIAL_NO

        FROM O_SITE SITE,ASUPDSS.D_INSTALLED_PRODUCT DPRODUCT

        WHERE SITE.SITE_KEY = DPRODUCT.INSTALLED_AT_SITE_KEY     

        AND DPRODUCT.IP_STATUS = 'ACTIVE'

        AND SITE.CURRENT_ROW_IND = 'T'

        AND SITE.SITE_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

        MINUS 

        SELECT MV.SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv 

        WHERE MV.SITE_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 



          

END sp_site_system_details;



PROCEDURE sp_custom_group_system_details(

    p_job_id   IN NUMBER,

    p_key_name IN VARCHAR2,

    p_sheets IN VARCHAR2,

    p_system OUT nocopy sys_refcursor,

    p_customer_site_info OUT nocopy sys_refcursor,    

    p_case OUT nocopy sys_refcursor,

    p_system_age OUT nocopy sys_refcursor,

    p_contract_dtl OUT nocopy sys_refcursor,

    p_capacity_forecast OUT nocopy sys_refcursor,

    p_epic_controller OUT nocopy sys_refcursor,

    p_epic_shelf OUT nocopy sys_refcursor,

    p_epic_disk OUT nocopy sys_refcursor,

    p_osfw_system OUT nocopy sys_refcursor,

    p_osfw_motherboard OUT nocopy sys_refcursor,

    p_osfw_shelf OUT nocopy sys_refcursor,

    p_osfw_disk OUT nocopy sys_refcursor,

    p_osfw_rlm OUT nocopy sys_refcursor,

    p_dave_hitz OUT nocopy sys_refcursor,

    p_perf_summary OUT nocopy sys_refcursor,

    p_fear_disk OUT nocopy sys_refcursor,

    p_fear_volume OUT nocopy sys_refcursor,

    p_fear_aggregate OUT nocopy sys_refcursor,

    p_fear_lun OUT nocopy sys_refcursor,

    p_fear_vol_option OUT nocopy sys_refcursor,

    p_fear_system_option OUT nocopy sys_refcursor,

    p_fear_license OUT nocopy sys_refcursor,

    p_invalid_serial_no OUT nocopy sys_refcursor)

  AS

  v_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 15, 'YYYYMMDD') || '00000000');

  v_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD') || '99999999');

  v_datapoint1_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 95, 'YYYYMMDD') || '00000000');

  v_datapoint1_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 85, 'YYYYMMDD') || '99999999');

  v_datapoint2_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000');

  v_datapoint2_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999');

  v_datapoint3_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000');

  v_datapoint3_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999');

  v_datapoint4_start_asup_id NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000');

  v_datapoint4_end_asup_id   NUMBER := TO_NUMBER(TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999');

  

  v_enabled_report VARCHAR2(3000) := p_sheets;

  --v_enabled_report VARCHAR2(3000) := 'isSystemInventoryReport,isDaveHitzReport,isStorageEfficiencyReport';

  is_inventory_required NUMBER;

  is_case_required NUMBER;

  is_davehitz_required NUMBER;

  is_storefficiency_required NUMBER;

  is_contract_required NUMBER;

  is_endofsupport_required NUMBER;

  is_systemaging_required NUMBER;

  is_osfirmware_required NUMBER;

  is_capacityforecast_required NUMBER;

  is_performance_required  NUMBER;

BEGIN

  

  is_inventory_required := INSTR(v_enabled_report, 'isSystemInventoryReport');

  is_case_required := INSTR(v_enabled_report, 'isSupportCaseReport');

  is_davehitz_required := INSTR(v_enabled_report, 'isDaveHitzReport');

  is_storefficiency_required:= INSTR(v_enabled_report, 'isStorageEfficiencyReport');

  is_contract_required:= INSTR(v_enabled_report, 'isContractDetails');

  is_endofsupport_required:= INSTR(v_enabled_report, 'isEndOfSupportRedar');

  is_systemaging_required:= INSTR(v_enabled_report, 'isSystemAgingReport');

  is_osfirmware_required:= INSTR(v_enabled_report, 'isOSFirmwareDetailReport');

  is_capacityforecast_required:= INSTR(v_enabled_report, 'isCapacityForecastReport');

  is_performance_required:= INSTR(v_enabled_report, 'isPerformanceSummaryReport');

  

  IF is_inventory_required <> 0 THEN

    OPEN p_system FOR 

      SELECT mv.Sys_Serial_No Sys_Serialno,

           mv.hostname sys_hostname,

           mv.asup_id asupid,

           mv.Sys_Model Sys_Model,

           mv.Sys_Version Sys_Version,

           mv.secure_flag secure_flag,

           TO_CHAR(mv.Asup_Received_Date,'DD-MON-YYYY') Sys_Revdate 

      FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv

      WHERE mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);     

  ELSE

    OPEN p_system FOR 

    Select * from Dual where 1=2;

  END IF;

    

    OPEN p_customer_site_info FOR 

        SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

            mv.CUSTOMER_ID,

            mv.CUSTOMER_NAME,

            mv.SITE_ID,      

            mv.SITE_NAME

        FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

            Asupdss.O_CUSTOMER cust,

            Asupdss.O_SITE site 

        WHERE 

          cust.CUSTOMER_ID        = mv.CUSTOMER_ID

          AND site.SITE_ID              = mv.SITE_ID

          AND cust.current_row_ind='T'

          AND site.current_row_ind='T'

          AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

   

    IF is_case_required <> 0 THEN 

      OPEN p_case FOR

          SELECT dcase.case_id,dcase.create_date,dcase.STATUS_TEXT status,dcase.CURRENT_PRIORITY

          FROM asupdss.D_INSTALLED_PRODUCT dprod, asupdss.f_case fcase, asupdss.D_Case dcase

          WHERE dprod.INSTALLED_PRODUCT_KEY = fcase.INSTALLED_PRODUCT_KEY

          AND fcase.case_key = dcase.case_key

          AND dprod.IP_SERIAL_NUMBER IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    ELSE

      OPEN p_case FOR 

      Select * from Dual where 1=2;

    END IF;

    

    IF is_systemaging_required <> 0 THEN

      OPEN p_system_age FOR    

        SELECT distinct dproduct.IP_SERIAL_NUMBER, dproduct.IP_SYSTEM_NAME hostname,dproduct.IP_SHIP_DATE

        FROM Asupdss.D_Installed_Product Dproduct

        WHERE dproduct.IP_SERIAL_NUMBER IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=207)

        ORDER BY IP_SHIP_DATE;

    ELSE  

      OPEN p_system_age FOR 

      Select * from Dual where 1=2;

    END IF;

  

    IF is_contract_required <> 0 THEN

      OPEN  p_contract_dtl FOR

          SELECT aggrement.AGREEMENT_ID,

          aggrement.ORACLESALESORDER,

          TO_CHAR(aggrement.AGR_START_DATE,'DD-MON-YYYY') AGR_START_DATE,

          dproduct.IP_WARRANTY_PRODUCT warrentyTerm,

          TO_CHAR(dproduct.IP_WARRANTY_START_DATE,'DD-MON-YYYY') ipStartDate,

          TO_CHAR(dproduct.IP_WARRANTY_END_DATE,'DD-MON-YYYY') ipEndDate,

          TO_CHAR(aggrement.AGR_END_DATE,'DD-MON-YYYY') AGR_END_DATE,

          TO_CHAR(aggrement.CONTRACT_EXTENDED_DATE,'DD-MON-YYYY') CONTRACT_EXTENDED_DATE,

          aggrlineItem.SERVICE_NAME,aggrItem.RESPONSE_LEVEL,

          dproduct.IP_SERIAL_NUMBER,

          dproduct.IP_SYSTEM_NAME sys_hostname,

          dproduct.IP_OS_VERSION Sys_Version,

          site.SITE_ID,

          site.SITE_NAME

      FROM 

          ASUPDSS.O_CUSTOMER cust,

		  ASUPDSS.O_SITE site,

          ASUPDSS.F_INSTALLED_PRODUCT_AGREEMENT fproduct,          

          Asupdss.D_Installed_Product dproduct,   

          ASUPDSS.D_AGREEMENT aggrement,

          asupdss.D_AGREEMENT_ITEM aggrItem,

          ASUPDSS.D_AGREEMENT_ITEM_SERVICE aggrlineItem

      WHERE  

          cust.CUSTOMER_KEY  = dproduct.OWNER_CUSTOMER_KEY

          AND site.SITE_KEY = dproduct.INSTALLED_AT_SITE_KEY

          AND fproduct.INSTALLED_PRODUCT_KEY = dproduct.INSTALLED_PRODUCT_KEY

          AND fproduct.AGREEMENT_KEY = aggrement.AGREEMENT_KEY

          AND fproduct.AGREEMENT_ITEM_KEY = aggrItem.AGREEMENT_ITEM_KEY

          AND fproduct.AGREEMENT_ITEM_SVC_KEY = aggrlineItem.AGREEMENT_ITEM_SVC_KEY

          AND dproduct.IP_SERIAL_NUMBER IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

    ELSE

      OPEN p_contract_dtl FOR 

      Select * from Dual where 1=2;

    END IF;

    

    IF is_capacityforecast_required <> 0 THEN

      OPEN p_capacity_forecast FOR    

          SELECT SUM(PHY_RAW_CAPAC) PHY_RAW_CAPAC, SUM(EFF_USED_CAPAC) EFF_USED_CAPAC,SUM(PHY_USED_CAPAC) PHY_USED_CAPAC,SUM(PHY_USABLE_CAPAC) PHY_USABLE_CAPAC,DATAPOINT

          FROM (

            SELECT * FROM (

                SELECT CALC.SERIAL_NUMBER,

                       CALC.PHY_RAW_CAPAC,

                       CALC.EFF_USED_CAPAC,

                       CALC.PHY_USED_CAPAC,

                       CALC.PHY_USABLE_CAPAC,

                       CALC.ASUP_ID,

                       CASE 

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 10,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 0,   'YYYYMMDD') || '99999999')  THEN 'datapoint0'  

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 95,  'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 85,  'YYYYMMDD') || '99999999')  THEN 'datapoint1'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 185, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 175, 'YYYYMMDD') || '99999999')  THEN 'datapoint2'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 275, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 265, 'YYYYMMDD') || '99999999')  THEN 'datapoint3'

                          WHEN CALC.ASUP_ID BETWEEN (TO_CHAR(SYSDATE - 365, 'YYYYMMDD') || '00000000') AND (TO_CHAR(SYSDATE - 355, 'YYYYMMDD') || '99999999')  THEN 'datapoint4'

                      END

                      DATAPOINT

                FROM ASUPDSS.ASUP_SE_CALCULATION CALC

                WHERE CALC.SERIAL_NUMBER IN( 

                         SELECT MV.SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV MV

                          WHERE MV.CUSTOMER_ID = (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

                     )

                ) A 

            WHERE A.DATAPOINT IS NOT NULL

            GROUP BY A.SERIAL_NUMBER,A.DATAPOINT,A.PHY_RAW_CAPAC,A.EFF_USED_CAPAC,A.PHY_USED_CAPAC,A.PHY_USABLE_CAPAC,A.ASUP_ID

            HAVING A.ASUP_ID = MAX(A.ASUP_ID)

            ORDER BY A.SERIAL_NUMBER

          ) 

          GROUP BY  DATAPOINT;

    ELSE

      OPEN p_capacity_forecast FOR 

      Select * from Dual where 1=2;

    END IF;

    

    IF is_endofsupport_required <> 0 THEN

    OPEN p_epic_controller FOR    

        SELECT mv.Sys_Serial_No Sys_Serialno,

               mv.Hostname Sys_Hostname,

               contrlInfo.PLATFORM_MODEL_ID,

               contrlInfo.PLATFORM_MODEL,

               contrlInfo.EOSHW

        FROM   ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV mv,

               asupdss.O_EPIC_PLATFORM_MODEL_EOS_INFO contrlInfo

        WHERE 

               mv.sys_model = contrlInfo.PLATFORM_MODEL

               AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	

      

    OPEN p_epic_shelf FOR

        SELECT DISTINCT mv.Hostname Sys_Hostname,

             mv.Sys_Serial_No Sys_Serialno,              

             Shelf.Shelf_Serial_No Shelf_Serialno,

             Dmod.Smod_Type Smod_Type,

             eosshelf.SHELF_MODULE_ID,

             eosshelf.SHELF_MODULE_NAME,

             eosshelf.EOSHW

        FROM asupdss.d_shelf shelf,

             asupdss.f_asup_shelf_module fmod,

             asupdss.d_shelf_module dmod,

             asupdss.O_EPIC_SHELF_MODULE_EOS_INFO eosshelf,

             asupdss.D_LATEST_WKLY_ASUP_SN_MV mv           

        WHERE shelf.shelf_key=fmod.shelf_key

             AND dmod.smod_key=fmod.smod_key

             AND fmod.asup_id =mv.asup_id

             AND dmod.SMOD_TYPE =eosshelf.SHELF_MODULE_NAME

             AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    

    OPEN p_epic_disk FOR

        SELECT DISTINCT mv.Hostname Sys_Hostname,

              mv.Sys_Serial_No Sys_Serialno,               

              device.dvc_key device_key,          

              Device.Dvc_Model Sys_Dvc_Model,             

              device.DVC_TYPE sys_dvc_type,

              device.DISK_TYPE sys_disk_type,              

              Device.Dvc_Label Sys_Dvc_Label ,

              eosInfo.DRIVE_ID driveId,

              eosInfo.DRIVE_TYPE,

              eosInfo.DRIVE_MODEL,

              eosInfo.EOSHW

        FROM Asupdss.D_Device Device,

              asupdss.f_asup_device fdevice,

              asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE device.dvc_key = fdevice.dvc_key

              AND device.DVC_MODEL = eosInfo.DRIVE_MODEL

              AND device.dvc_type  NOT IN  ('LUN')    

              AND Device.Dvc_LABEL <> 'PARTNER'          

              AND fdevice.asup_id = mv.asup_id

              AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    ELSE

      OPEN p_epic_controller FOR 

      Select * from Dual where 1=2;

      

      OPEN p_epic_shelf FOR 

      Select * from Dual where 1=2;

      

      OPEN p_epic_disk FOR 

      Select * from Dual where 1=2;

    END IF;

    

    IF is_osfirmware_required <> 0 THEN      

      OPEN p_osfw_system FOR

          SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

              mv.hostname sys_hostname,

              mv.sys_model sys_model,

              mv.SYS_VERSION sys_version,

              mv.SYS_OPERATING_MODE sys_mode,          

              latest_fw_info.display sys_display

          FROM         

              ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE       

              latest_fw_info.Object_name = 'Running Recommended ONTAP versions'

              AND latest_fw_info.model = mv.sys_model

              AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

        

      OPEN p_osfw_motherboard FOR

          SELECT DISTINCT mv.Sys_Serial_No Sys_Serial_No,

                mv.hostname sys_hostname,

                mv.sys_model sys_model,

                dmb.MB_MODEL mb_model,

                dmb.MB_FW_REV mb_fw_rev,           

                latest_fw_info.display sys_display  

          FROM  asupdss.F_ASUP_MOTHERBOARD fmb,

                asupdss.D_MOTHERBOARD dmb,

                ASUPDSS.O_MYASUP_FW_INFO latest_fw_info,

                asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fmb.asup_id = mv.asup_id

              AND fmb.motherboard_key = dmb.motherboard_key

              AND latest_fw_info.Object_name = 'Motherboard Firmware Up to Date'

              AND latest_fw_info.model = dmb.MB_MODEL

              AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

    

    OPEN p_osfw_shelf FOR

        SELECT mv.Hostname Sys_Hostname,

             mv.Sys_Serial_No Sys_Serialno,

             shelf.SHELF_TYPE shelf_type,

             count(distinct Shelf.Shelf_Serial_No) shelf_cnt,             

             dmod.Smod_Type Smod_Type,               

             count(distinct dmod.smod_serial_no) smod_cnt,

             dmod.smod_fw_rev smod_fw_rev,

             myasup_info.display display

        FROM asupdss.d_shelf shelf,

             asupdss.f_asup_shelf_module fmod,

             asupdss.d_shelf_module dmod,

             asupdss.O_MYASUP_FW_INFO myasup_info,

             asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE shelf.shelf_key=fmod.shelf_key

            AND dmod.smod_key=fmod.smod_key

            AND fmod.asup_id =mv.asup_id

            AND myasup_info.object_name = 'Shelf FW version and Shelf Type'

            AND dmod.Smod_Type = myasup_info.model     

            AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

            group by mv.Sys_Serial_No,mv.Hostname,shelf.SHELF_TYPE,Dmod.Smod_Type,dmod.smod_fw_rev,myasup_info.display;	



    OPEN p_osfw_disk FOR

        SELECT DISTINCT mv.Hostname Sys_Hostname,

            mv.Sys_Serial_No Sys_Serialno,                 

            device.DVC_FW_REV DVC_FW_REV, 

            count(device.DVC_FW_REV) fw_cnt,

            Device.Dvc_Model Sys_Dvc_Model,

            latest_fw_info.display display

        FROM Asupdss.D_Device Device,

            asupdss.f_asup_device fdevice,

            asupdss.O_MYASUP_FW_INFO latest_fw_info,

            asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE device.dvc_key = fdevice.dvc_key          

            AND device.dvc_type  NOT IN  ('LUN')    

            AND Device.Dvc_LABEL <> 'PARTNER'          

            AND fdevice.asup_id = mv.asup_id         

            AND latest_fw_info.object_name='Disk Firmware Up to Date'

            AND latest_fw_info.model = device.Dvc_Model

            AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

            GROUP BY mv.Sys_Serial_No,mv.Hostname,device.DVC_FW_REV,device.Dvc_Model,latest_fw_info.display

            order by mv.Sys_Serial_No,device.Dvc_Model;	

          

    OPEN p_osfw_rlm FOR

       SELECT DISTINCT mv.Hostname Sys_Hostname,

            mv.Sys_Serial_No Sys_Serialno,   

            mv.sys_model sys_model,

            adp.hba_type hba_type,

            adp.IS_RSA_ENABLED is_rsa_active,

            adp.HBA_FW_REV hba_fw,   

            latest_fw_info.display display

        FROM Asupdss.D_ADAPTER adp,

            asupdss.f_asup_interface fif,

            asupdss.O_MYASUP_FW_INFO latest_fw_info,      

            asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE  adp.hba_type IN ('RLM','SP','BMC')

            AND Adp.Adapter_Key = Fif.Adapter_Key 

            AND fif.asup_id = mv.asup_id

            --AND mv.sys_serial_no IN ('700000309048')

            AND latest_fw_info.object_name='Remote Management'

            AND latest_fw_info.model = mv.sys_model

            AND latest_fw_info.type = adp.hba_type

            AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);    

    ELSE

      OPEN p_osfw_system FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_motherboard FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_shelf FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_disk FOR 

      Select * from Dual where 1=2;

      

      OPEN p_osfw_rlm FOR 

      Select * from Dual where 1=2;

    END IF;

    

     IF is_davehitz_required <> 0 THEN      

     OPEN  p_dave_hitz FOR 

       SELECT SUM(eba.TOTAL_SATA_STORAGE_TB) TOTAL_SATA_STORAGE_TB,

            SUM(eba.TOTAL_RAW_STORAGE_TB) TOTAL_RAW_STORAGE_TB,

            SUM(eba.FLASHCACHE_ENABLED_GB) FLASHCACHE_ENABLED_GB,

            SUM(eba.FLASHCACHE_NOT_ENABLED_GB) FLASHCACHE_NOT_ENABLED_GB,

            SUM(eba.DEDUPE_ENABLED_STORAGE_TB) DEDUPE_ENABLED_STORAGE_TB,

            SUM(eba.DEDUPE_NOT_ENABLED_TB) DEDUPE_NOT_ENABLED_TB,

            SUM(voldata.VOL_COMPRESSED_TB) VOL_COMPRESSED_TB,

            SUM(voldata.VOL_UNCOMPRESSED_TB) VOL_UNCOMPRESSED_TB,

            eba.calendar_month

           -- voldata.ASUP_MONTH

       FROM asupdss.O_EBA_EFFICIENCY_DETAILS eba,

          asupdss.T_AGG_VOL_DATA voldata

       WHERE eba.SYS_SERIAL_NO = voldata.SYS_SERIAL_NO

       AND asup_month = replace(calendar_month, 'M', '') 

       --and eba.SYS_SERIAL_NO in ('1083302','30009936','600000111308','30028130','30028131','700000691582','700000691594');

       AND  eba.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

       GROUP BY  eba.calendar_month;--, voldata.ASUP_MONTH ;   

     

    ELSE

    

    OPEN p_dave_hitz FOR 

    Select * from Dual where 1=2;

    

    END IF;

    

    IF is_performance_required <> 0 THEN  

     OPEN p_perf_summary FOR

      		  SELECT perf.Sys_Serial_No Sys_Serial_No,

                 mv.hostname,

                 sum(fcp_ops) / nullif(sum(fcp_ct),0) fcp_ops,

                 sum(iscsi_ops) / nullif(sum(iscsi_ct),0) iscsi_ops,

                 sum(disk_busy) / nullif(sum(disk_ct),0) disk_busy,

                 sum(cifs_ops) / nullif(sum(cifs_ct),0) cifs_ops,

                 sum(nfs_ops) / nullif(sum(nfs_ct),0) nfs_ops,

                 sum(cpu_busy) / nullif(sum(cpu_ct),0) cpu_busy

            FROM asupdss.T_HIVE_DATA_CM perf,

                 asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

            WHERE perf.Sys_Serial_No = mv.Sys_Serial_No

            AND perf.dt BETWEEN sysdate-30 AND sysdate

            AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

            GROUP BY perf.Sys_Serial_No,mv.hostname; 

    ELSE

    

    OPEN p_perf_summary FOR 

    Select * from Dual where 1=2;

    

    END IF;

    

    IF is_storefficiency_required <> 0 THEN

    OPEN p_fear_disk FOR

        SELECT DISK_TYPE dvc_disk_type, 

               DVC_LABEL dvc_label, 

               DVC_MODEL dvc_model, 

               DVC_PHY_SIZE_MB dvc_usable_mb,

               draidgp.RG_PATH_NAME,

               eosInfo.MARKETING_CAPACITY_GB,

               eosInfo.DRIVE_SPEED,

               mv.sys_serial_no sys_serial_no

        FROM  asupdss.D_DEVICE ddvc, 

              asupdss.F_ASUP_DEVICE fdvc,

              asupdss.D_RAID_GROUP draidgp ,

              asupdss.O_EPIC_DISK_EOS_INFO eosInfo,

              asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

        WHERE fdvc.dvc_key =  ddvc.dvc_key

              AND fdvc.rg_key = draidgp.rg_key            

              AND ddvc.DVC_MODEL = eosInfo.DRIVE_MODEL

              AND ddvc.DVC_LABEL <> 'PARTNER' AND ddvc.DVC_LABEL <> 'UNOWNED'

              AND fdvc.asup_id= mv.asup_id --2009122617310247;

              AND fdvc.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);    

    

    OPEN p_fear_volume FOR 

          SELECT fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no sys_serial_no,count(fsnap.volume_key) as snapshotcnt,round(sysdate-max(dsnap.SNAPSHOT_DATE)) noOfDays 

          FROM asupdss.F_ASUP_VOLUME fvol, asupdss.D_VOLUME dvol, asupdss.D_VOLUME_INDICATOR dvolind,asupdss.f_asup_snapshot fsnap,asupdss.d_snapshot dsnap,

             asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fvol.vol_key =  dvol.vol_key

              AND fsnap.SNAPSHOT_KEY  = dsnap.SNAPSHOT_KEY

              AND fsnap.volume_key(+) = fvol.vol_key              

              AND fvol.VOL_INDICATOR_KEY =  dvolind.VOL_INDICATOR_KEY

              AND fvol.asup_id = mv.asup_id

              AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND fsnap.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

              AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id)

          GROUP BY fvol.vol_used_res_kb,fvol.vol_sused_ct,fvol.vol_clone_ct,fvol.VOL_ALLOCATED_KB,fvol.VOL_SALLOCATED_KB,fvol.VOL_USED_KB,

               fvol.VOL_SUSED_KB,fvol.VOL_ASIS_SAVED_KB,fvol.VOL_COMPRESSED_KB,fvol.VOL_AVAIL_KB,fvol.VOL_SAVAIL_KB,dvol.vol_name, 

               dvol.vol_path_name,dvolind.VOL_SNAPMIRROR_IND,dvol.vol_type,mv.sys_serial_no;    

              

      OPEN p_fear_aggregate FOR 

          SELECT daggind.AGGR_SYNCMIRRORED_IND , fagg.aggr_used_kb , dagg.aggr_name, dagg.aggr_path_name , fagg.aggr_avail_kb, fagg.aggr_savail_kb,

                dagg.aggr_raid_type,dagg.aggr_type,mv.sys_serial_no sys_serial_no  

          FROM asupdss.F_ASUP_AGGREGATE fagg, asupdss.D_AGGREGATE dagg,asupdss.D_AGGREGATE_INDICATOR daggind, asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE fagg.aggr_key =  dagg.aggr_key

          AND fagg.AGGR_INDICATOR_KEY = daggind.AGGR_INDICATOR_KEY

          AND fagg.asup_id =  mv.asup_id

          AND fagg.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

          AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

      OPEN p_fear_lun FOR 

          SELECT dlun.LUN_NAME,dlun.vol_name,dlun.lun_path_name,flun.LUN_SIZE_BYTES,dlun.LUN_RESERVE_IND,dqtree.qt_style qt_styles,mv.sys_serial_no sys_serial_no

          FROM asupdss.F_ASUP_LUN flun ,asupdss.D_LUN dlun,asupdss.D_QTREE dqtree,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE  flun.LUN_KEY = dlun.LUN_KEY

          AND flun.QTREE_KEY = dqtree.QTREE_KEY

          AND flun.asup_id=mv.asup_id

          AND flun.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

          AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);	 	

    

       OPEN p_fear_vol_option FOR

          SELECT DISTINCT fvol.vol_key,voption.vol_Option_Value vol_Option_Value, voption.vol_Option_Name vol_Option_Name,mv.sys_serial_no sys_serial_no  

          FROM asupdss.D_VOLUME_OPTION voption,Asupdss.D_VOLUME dvol,Asupdss.F_Asup_VOLUME Fvol,

               ASUPDSS.B_VOLUME_OPTION_GROUP bvgroup,

               asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE voption.VOL_OPTION_NAME IN('guarantee','snapshotAutodeleteSettings.state','volumeAutosizeSettings.state','volumeAutosizeSettings.state','snaplock_enterprise','snaplock_compliance')

          AND fvol.vol_key = dvol.vol_key

          AND fvol.VOLUME_OPTION_GROUP_KEY =  bvgroup.VOLUME_OPTION_GROUP_KEY

          AND voption.vol_Option_Key = bvgroup.vol_Option_Key

          AND fvol.asup_id = mv.asup_id

          AND fvol.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

          AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id); 

          

      OPEN p_fear_system_option FOR

          SELECT sys.sys_serial_no,Doption.System_Option_Value Sys_Option_Value, Doption.System_Option_Name Sys_Option_Name,mv.sys_serial_no sys_serial_no

          FROM asupdss.d_system_option doption,Asupdss.D_System Sys,Asupdss.F_Asup_System F,

                ASUPDSS.B_SYSTEM_OPTION_GROUP bgroup,

                asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

          WHERE  Doption.system_option_name IN('cf.mode','cf.remote_syncmirror.enable')

          AND F.system_key = sys.system_key

          AND F.SYSTEM_OPTION_GROUP_KEY =  bgroup.SYSTEM_OPTION_GROUP_KEY

          AND Doption.System_Option_Key = Bgroup.System_Option_Key

          AND f.asup_id = mv.asup_id

          AND f.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

          AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id);

          

      OPEN p_fear_license FOR

          SELECT a.sys_serialno sys_serialno,a.Sys_Version,a.sys_systemid sys_systemid,a.sys_srvc_nam,a.LIC_INFO

        FROM 

        (SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,ser.svc_name as sys_srvc_nam,

        CASE

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NULL  THEN 'S'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE < SVC_LIC_EXP_DATE  THEN 'E'

          WHEN SVC_SITE_LIC_IND = 'T' AND SVC_LIC_EXP_DATE IS NOT NULL AND SYSDATE > SVC_LIC_EXP_DATE  THEN TO_CHAR (SVC_LIC_EXP_DATE)

          WHEN SVC_SITE_LIC_IND <> 'T' AND SVC_LIC_IND  = 'T' THEN 'Y'

          ELSE 'N' END AS LIC_INFO

              FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_service ser,asupdss.f_asup_service fser,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

              WHERE ser.svc_name <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND ser.svc_key = fser.svc_key

                   AND fser.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND fser.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

                   --and mv.sys_serial_no in ('701409000318','200000269901')

        UNION

        SELECT sys.sys_serial_no sys_serialno,sys.Sys_Version Sys_Version,sys.system_id sys_systemid,dcl.PACKAGE_NAME as sys_srvc_nam,

        CASE

        WHEN dcl.license_type = 'license' THEN 'Y' WHEN dcl.license_type = 'site' THEN 'S' 

        WHEN dcl.license_type = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate < to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN 'E'

        WHEN dcl.license_type   = 'demo' AND dcl.expiration_date IS NOT NULL AND sysdate > to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss') THEN TO_CHAR (to_date(expiration_date, 'MM/DD/YYYY HH24:mi:ss'))

        ELSE 'N' END AS LIC_INFO

          FROM asupdss.d_system sys,asupdss.f_asup_system fsys,asupdss.d_cluster_license  dcl,asupdss.f_asup_cluster_license  fcl,asupdss.D_LATEST_WKLY_ASUP_SN_MV mv

                WHERE dcl.PACKAGE_NAME <> 'SYSTEM'

                   AND sys.system_key = fsys.system_key

                   AND dcl.CLUSTER_LICENSE_KEY = fcl.CLUSTER_LICENSE_KEY

                   AND fcl.asup_id = mv.asup_id

                   AND fsys.asup_id = mv.asup_id

				           AND fsys.asup_id BETWEEN v_start_asup_id AND v_end_asup_id

                   -- and mv.sys_serial_no in ('701409000318','200000269901')

                   AND mv.SYS_SERIAL_NO IN (SELECT REPORT_VALUE FROM URP_REPORT_REQUEST_DETAILS WHERE JOB_ID=p_job_id) 

        )a;

    ELSE

    

    OPEN p_fear_disk FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_volume FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_aggregate FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_lun FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_vol_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_system_option FOR 

    Select * from Dual where 1=2;

    

    OPEN p_fear_license FOR 

    Select * from Dual where 1=2;

    

    END IF;

    

    OPEN p_invalid_serial_no FOR

      SELECT REPORT_VALUE SYS_SERIAL_NO  FROM URP_REPORT_REQUEST_DETAILS 

      WHERE JOB_ID=p_job_id AND REPORT_VALUE NOT IN (SELECT SYS_SERIAL_NO FROM ASUPDSS.D_LATEST_WKLY_ASUP_SN_MV);

          

END sp_custom_group_system_details;



END GET_URP_REPORT_DETAILS;



/



