SYSTEM_CURRENCY_STATUS -- Combining GROUP_PTF_CURRENCY, DEFECTIVE_PTF_CURRENCY, and FIRMWARE_CURRENCY

SystemCurrencyStatus.sql

/************************************************************************************************************/
/* Check for defective PTFs, new PTF groups, and firmware updates. Email someone a spreadsheet the results! */
/************************************************************************************************************/
CREATE OR REPLACE FUNCTION SYSTOOLS.SYSTEM_CURRENCY_STATUS (
            EMAIL VARCHAR(100)
    )
    RETURNS INT
    SPECIFIC SYSTOOLS.SYSCURSTS
    MODIFIES SQL DATA
    LANGUAGE SQL
    BEGIN  
        DECLARE SUCCESSFLAG INT;
        DECLARE FILEPATH VARCHAR(1000);
        DECLARE FILEEXT VARCHAR(5) DEFAULT '.csv';
        DECLARE ABSORBFAILURE integer;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ABSORBFAILURE = 1;
        
        SET FILEPATH = '/dailyCurrencyReports/currency' CONCAT VARCHAR_FORMAT(CURRENT DATE, 'MONDDYY');
        
        -- Create directory to hold spreadsheets
        CALL QSYS2.QCMDEXC('QSH CMD(''mkdir -p /dailyCurrencyReports'')');
      
        -- Use query to generate spreadsheet at specified IFS directory
        VALUES (
            SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => FILEPATH,
                                          SPREADSHEET_QUERY => 'WITH CURRENCY(TYPE, PRODUCT_ID, TITLE, CURRENT_VERSION, AVAILABLE_VERSION) AS (
                                                                     SELECT ''PTF GROUP'',
                                                                              PTF_GROUP_ID,
                                                                              PTF_GROUP_TITLE,
                                                                              CAST(PTF_GROUP_LEVEL_INSTALLED AS VARCHAR(20)),
                                                                              CAST(PTF_GROUP_LEVEL_AVAILABLE AS VARCHAR(20))
                                                                        FROM SYSTOOLS.GROUP_PTF_CURRENCY
                                                                        WHERE PTF_GROUP_LEVEL_INSTALLED != PTF_GROUP_LEVEL_AVAILABLE
                                                                    UNION ALL
                                                                    SELECT ''PTF'',
                                                                           PRODUCT_ID,
                                                                           ''Defective PTF'',
                                                                           DEFECTIVE_PTF,
                                                                           FIXING_PTF
                                                                        FROM SYSTOOLS.DEFECTIVE_PTF_CURRENCY
                                                                    UNION ALL
                                                                    SELECT ''FIRMWARE'',
                                                                           FW_PRODUCT_ID,
                                                                           ''Firmware'',
                                                                           FW_CURRENTFIXPACK,
                                                                           FW_RECOMMENDED_UPDATE
                                                                        FROM SYSTOOLS.FIRMWARE_CURRENCY)
                                                        
                                                                    SELECT * FROM CURRENCY', 
            SPREADSHEET_TYPE => 'csv', COLUMN_HEADINGS => 'COLUMN')) INTO SUCCESSFLAG;
            
            IF SUCCESSFLAG = 1 THEN 
                VALUES (SYSTOOLS.SEND_EMAIL(
                    TO_EMAIL => EMAIL,
                    SUBJECT => 'PTF and Firmware Currency Report for ' CONCAT CURRENT SERVER,
                    BODY => 'See attachment!',
                    ATTACHMENT => FILEPATH CONCAT FILEEXT)) INTO SUCCESSFLAG;
            END IF;
            
            RETURN SUCCESSFLAG;
    END;

VALUES SYSTOOLS.SYSTEM_CURRENCY_STATUS(:email);