Document toolboxDocument toolbox

.Scheduled Maintenance v2021.3

Owned by Brian Dixon
Last updated: Jan 08, 2024 by Bianca Rosa (Unlicensed)Version comment
9 min read

Scheduled Maintenance required for Equipment is a key component in using PCR-360 for Facilities Management.  Users can set up a Custom Event that will assist in scheduling regular maintenance for Equipment by automatically creating Incidents for that Equipment to be maintained. In order to create this automated behavior first there need to be three User Defined Fields (UDFs) set to track needed maintenance.


To set up the UDFs first navigate to Admin > User Defined Fields > User Defined Fields. Create the UDFs needed by following these steps:

  1. Create a Maintenance Period Type UDF
    1. Create a new UDF by clicking the 'Add' button.
    2. In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_TYPE".
    3. Add a 'Label' of "Maintenance Period".
    4. Set the 'Field Type' to "Dropdown".
    5. The header should look like this example:
    6. In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
      1.  Set the 'Association Table' dropdown to "Eqp Catalog"
      2. Click the 'Save New' button.
      3. Close the 'Manage Field Association' form.
    7. Click the 'Save New' button to save the UDF.
    8. When the form refreshes select the 'Dropdown Options' tab.
      1. Click the 'Add' button and add three new options:
        1. Add a 'Value' and 'Code' of "Years".
        2. Add a 'Value' and 'Code' of "Months".
        3. Add a 'Value' and 'Code' of "Days".
        4. When done the options should look like this:
    9. Close the 'Manage Field Association' form.


  2. Create a Maintenance Period Quantity UDF
    1. Create a new UDF by clicking the 'Add' button.
    2. In the 'UDF Identifier' field set the value to "MAINTENANCE_PERIOD_QTY".
    3. Add a 'Label' of "Maintenance Period Quantity".
    4. Set the 'Field Type' to "Number".
    5. The header should look like this example:
    6. In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
      1.  Set the 'Association Table' dropdown to "Eqp Catalog"
      2. Click the 'Save New' button.
      3. Close the 'Manage Field Association' form.
    7. Click the 'Save New' button to save the UDF.


  3. Create a Maintenance Date UDF
    1. Create a new UDF by clicking the 'Add' button.
    2. In the 'UDF Identifier' field set the value to "MAINTENANCE_DATE.
    3. Add a 'Label' of "Maintenance Date".
    4. Set the 'Field Type' to "Date".
    5. The header should look like this example:
    6. In the 'UDF Associations' click the 'Add' button to open the 'Add New Field Association' form.
      1.  Set the 'Association Table' dropdown to "Equipment"
      2. Select an 'Equipment Catalog' to limit the UDF to the type that should set for the regular maintenance.
      3. Click the 'Save New' button.
      4. Close the 'Manage Field Association' form.
    7. Click the 'Save New' button to save the UDF.


Once all of three of the UDFs have been created the next step is to add a Custom Event. To add the Custom Event follow these steps:

  1. Navigate to Admin > Custom Logic > Events.
  2. Click the 'Add' button to start a new Event.
  3. Set the 'Identifier' field to "Scheduled Maintenance".
  4. In the 'Logic' tab enter the logic  from the "Schedule Maintenance" expand below:
  5.  Basic Scheduled Maintenance Example
    <?php

$eqp = $this->query(<<<SQL
    SELECT E.*,
       MAINT_DATE.VALUE as MAINTENANCE_DATE,
       MAINT_PERIOD_TYPE.VALUE as MAINTENANCE_PERIOD_TYPE,
       MAINT_PERIOD_QTY.VALUE as MAINTENANCE_PERIOD_QTY
       
    FROM EQUIPMENT E
    INNER JOIN EQP_CATALOG EC 
        ON EC.RECID = E.EQP_CATALOG_RECID
        
    INNER JOIN V_UDF MAINT_DATE 
        ON E.RECID = MAINT_DATE.VALUE_TABLE_RECID AND MAINT_DATE.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINT_DATE.IDENTIFIER = 'MAINTENANCE_DATE'
        
    INNER JOIN V_UDF MAINT_PERIOD_TYPE 
        ON EC.RECID = MAINT_PERIOD_TYPE.VALUE_TABLE_RECID AND MAINT_PERIOD_TYPE.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINT_PERIOD_TYPE.IDENTIFIER = 'MAINTENANCE_PERIOD_TYPE'

    INNER JOIN V_UDF MAINT_PERIOD_QTY 
        ON EC.RECID = MAINT_PERIOD_QTY.VALUE_TABLE_RECID AND MAINT_PERIOD_QTY.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINT_PERIOD_QTY.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY'

    WHERE MAINT_DATE.VALUE = CURRENT_DATE 
    ;
SQL
);


foreach($eqp as $equipment){

    $incident = [
        "type" => "IN",
        "csr" => "1",
        "owner" => $equipment["OWNER_CONTACTS_RECID"] ? $equipment["OWNER_CONTACTS_RECID"] : $equipment["OWNER_DEPT_HIERARCHY_RECID"],
        "owner_type" => $equipment["OWNER_CONTACTS_RECID"] ? "contact" : "department",
        "description" => "Scheduled Maintenance",
        "prob_code" => 3,
        "equipment" => $equipment["RECID"]
    ];

    $result = $this->call("saveServiceDesk", $incident);

    $maintenanceDate = new DateTime($equipment["MAINTENANCE_DATE"]);
    $intervalType = substr($equipment["MAINTENANCE_PERIOD_TYPE"], 0, 1);
    $interval = new DateInterval("P{$equipment["MAINTENANCE_PERIOD_QTY"]}{$intervalType}");
    $maintenanceDate->add($interval);

    $result = $this->call("saveEquipment", [
        "type" => "UDFS",
        "equipment_recid" => $equipment["RECID"],
        "udf_MAINTENANCE_DATE" => $maintenanceDate->format("Y-m-d")
    ]);

}
  6.  Multiple Time frame Example

    The following example uses 10 separate UDFs to control the Custom Event and allows setting multiple different maintenance periods for the same piece of Equipment.

    <?php
/*
 * Custom Events
 * Basic Instructions: https://confluence.pcr.com/x/WA2BAQ
 * Since adapted for multiple timeframes on the same catalog
 */

$eqp = $this->query(<<<SQL
    SELECT E.*,
       MAINTENANCE_DATE_ANNUAL.VALUE as MAINTENANCE_DATE_ANNUAL,
       MAINTENANCE_DATE_SEMIANNUAL.VALUE as MAINTENANCE_DATE_SEMIANNUAL,
       MAINTENANCE_DATE_QUARTERLY.VALUE as MAINTENANCE_DATE_QUARTERLY,
       MAINTENANCE_DATE_MONTHLY.VALUE as MAINTENANCE_DATE_MONTHLY,
       MAINTENANCE_DATE_WEEKLY.VALUE as MAINTENANCE_DATE_WEEKLY,
       
       MAINTENANCE_DATE_ANNUAL.DESCRIPTION as MAINTENANCE_DATE_ANNUAL_DESC,
       MAINTENANCE_DATE_SEMIANNUAL.DESCRIPTION as MAINTENANCE_DATE_SEMIANNUAL_DESC,
       MAINTENANCE_DATE_QUARTERLY.DESCRIPTION as MAINTENANCE_DATE_QUARTERLY_DESC,
       MAINTENANCE_DATE_MONTHLY.DESCRIPTION as MAINTENANCE_DATE_MONTHLY_DESC,
       MAINTENANCE_DATE_WEEKLY.DESCRIPTION as MAINTENANCE_DATE_WEEKLY_DESC,
       
       COALESCE(MAINTENANCE_PERIOD_QTY_ANNUAL.VALUE,1) as MAINTENANCE_PERIOD_QTY_ANNUAL,
       COALESCE(MAINTENANCE_PERIOD_QTY_SEMIANNUAL.VALUE,1) as MAINTENANCE_PERIOD_QTY_SEMIANNUAL,
       COALESCE(MAINTENANCE_PERIOD_QTY_QUARTERLY.VALUE,1) as MAINTENANCE_PERIOD_QTY_QUARTERLY,
       COALESCE(MAINTENANCE_PERIOD_QTY_MONTHLY.VALUE,1) as MAINTENANCE_PERIOD_QTY_MONTHLY,
       COALESCE(MAINTENANCE_PERIOD_QTY_WEEKLY.VALUE,1) as MAINTENANCE_PERIOD_QTY_WEEKLY
       
    FROM EQUIPMENT E
    INNER JOIN EQP_CATALOG EC 
        ON EC.RECID = E.EQP_CATALOG_RECID
        
    LEFT JOIN V_UDF MAINTENANCE_DATE_ANNUAL 
        ON E.RECID = MAINTENANCE_DATE_ANNUAL.VALUE_TABLE_RECID 
        AND MAINTENANCE_DATE_ANNUAL.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINTENANCE_DATE_ANNUAL.IDENTIFIER = 'MAINTENANCE_DATE_ANNUAL'
    LEFT JOIN V_UDF MAINTENANCE_DATE_SEMIANNUAL 
        ON E.RECID = MAINTENANCE_DATE_SEMIANNUAL.VALUE_TABLE_RECID 
        AND MAINTENANCE_DATE_SEMIANNUAL.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINTENANCE_DATE_SEMIANNUAL.IDENTIFIER = 'MAINTENANCE_DATE_SEMIANNUAL'
    LEFT JOIN V_UDF MAINTENANCE_DATE_QUARTERLY 
        ON E.RECID = MAINTENANCE_DATE_QUARTERLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_DATE_QUARTERLY.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINTENANCE_DATE_QUARTERLY.IDENTIFIER = 'MAINTENANCE_DATE_QUARTERLY'
    LEFT JOIN V_UDF MAINTENANCE_DATE_MONTHLY 
        ON E.RECID = MAINTENANCE_DATE_MONTHLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_DATE_MONTHLY.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINTENANCE_DATE_MONTHLY.IDENTIFIER = 'MAINTENANCE_DATE_MONTHLY'
    LEFT JOIN V_UDF MAINTENANCE_DATE_WEEKLY 
        ON E.RECID = MAINTENANCE_DATE_WEEKLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_DATE_WEEKLY.VALUE_TABLE_NAME = 'EQUIPMENT' AND MAINTENANCE_DATE_WEEKLY.IDENTIFIER = 'MAINTENANCE_DATE_WEEKLY'

    LEFT JOIN V_UDF MAINTENANCE_PERIOD_QTY_ANNUAL 
        ON EC.RECID = MAINTENANCE_PERIOD_QTY_ANNUAL.VALUE_TABLE_RECID 
        AND MAINTENANCE_PERIOD_QTY_ANNUAL.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINTENANCE_PERIOD_QTY_ANNUAL.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY_ANNUAL'
    LEFT JOIN V_UDF MAINTENANCE_PERIOD_QTY_SEMIANNUAL 
        ON EC.RECID = MAINTENANCE_PERIOD_QTY_SEMIANNUAL.VALUE_TABLE_RECID 
        AND MAINTENANCE_PERIOD_QTY_SEMIANNUAL.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINTENANCE_PERIOD_QTY_SEMIANNUAL.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY_SEMIANNUAL'
    LEFT JOIN V_UDF MAINTENANCE_PERIOD_QTY_QUARTERLY 
        ON EC.RECID = MAINTENANCE_PERIOD_QTY_QUARTERLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_PERIOD_QTY_QUARTERLY.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINTENANCE_PERIOD_QTY_QUARTERLY.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY_QUARTERLY'
    LEFT JOIN V_UDF MAINTENANCE_PERIOD_QTY_MONTHLY 
        ON EC.RECID = MAINTENANCE_PERIOD_QTY_MONTHLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_PERIOD_QTY_MONTHLY.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINTENANCE_PERIOD_QTY_MONTHLY.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY_MONTHLY'
    LEFT JOIN V_UDF MAINTENANCE_PERIOD_QTY_WEEKLY 
        ON EC.RECID = MAINTENANCE_PERIOD_QTY_WEEKLY.VALUE_TABLE_RECID 
        AND MAINTENANCE_PERIOD_QTY_WEEKLY.VALUE_TABLE_NAME = 'EQP_CATALOG' AND MAINTENANCE_PERIOD_QTY_WEEKLY.IDENTIFIER = 'MAINTENANCE_PERIOD_QTY_WEEKLY'

    WHERE 

    (MAINTENANCE_DATE_ANNUAL.VALUE IS NOT NULL
    OR MAINTENANCE_DATE_SEMIANNUAL.VALUE IS NOT NULL
    OR MAINTENANCE_DATE_QUARTERLY.VALUE IS NOT NULL
    OR MAINTENANCE_DATE_MONTHLY.VALUE IS NOT NULL
    OR MAINTENANCE_DATE_WEEKLY.VALUE IS NOT NULL)
    AND
    (MAINTENANCE_DATE_ANNUAL.VALUE = CURRENT_DATE
    OR MAINTENANCE_DATE_SEMIANNUAL.VALUE = CURRENT_DATE
    OR MAINTENANCE_DATE_QUARTERLY.VALUE = CURRENT_DATE
    OR MAINTENANCE_DATE_MONTHLY.VALUE = CURRENT_DATE
    OR MAINTENANCE_DATE_WEEKLY.VALUE = CURRENT_DATE)
    ;
SQL
);

$dateTypes = [
    'ANNUAL'     => 'P1Y',
    'SEMIANNUAL' => 'P6M',
    'QUARTERLY'  => 'P3M',
    'MONTHLY'    => 'P1M',
    'WEEKLY'     => 'P1W'
];
$now = new DateTime();
$now->setTime(0,0);

foreach($eqp as $equipment){

    $incident = [
        'type' => 'IN',
        'csr' => '1',
        'owner' => $equipment['OWNER_CONTACTS_RECID'] ? $equipment['OWNER_CONTACTS_RECID'] : $equipment['OWNER_DEPT_HIERARCHY_RECID'],
        'owner_type' => $equipment['OWNER_CONTACTS_RECID'] ? 'contact' : 'department',
        'description' => 'Scheduled Maintenance',
        'prob_code' => 3,
        'equipment' => $equipment['RECID']
    ];

    foreach($dateTypes as $dateType => $dateInterval){
        $maintenanceDate = new DateTime($equipment["MAINTENANCE_DATE_{$dateType}"]);

        if($maintenanceDate == $now) {
            $incident['description'] = $equipment["MAINTENANCE_DATE_{$dateType}_DESC"];

            $result = $this->call('saveServiceDesk', $incident);
            $interval = new DateInterval($dateInterval);

            for($qty = 1; $qty <= $equipment["MAINTENANCE_PERIOD_QTY_{$dateType}"]; $qty++){
                $maintenanceDate->add($interval);
            }

            $result = $this->call('saveEquipment', [
                'type' => 'UDFS',
                'equipment_recid' => $equipment['RECID'],
                "udf_MAINTENANCE_DATE_{$dateType}" => $maintenanceDate->format('Y-m-d')
            ]);
        }
    }
}

  7. Click the 'Save New' button.


    Now that initial data set up is finished, simply find the Equipment Catalog from step 1 and enter information into the two UDFs setup in steps 1 and 2, Maintenance Period and Maintenance Period Quantity respectively. The Quantity should represent how many periods should pass before triggering the event. For example, to set a maintenance period every 3 months, set the Quantity to "3". Next, open the Equipment record and set the Maintenance Period Date UDF to the next Maintenance Date.

    The Custom Event will now automatically run in the background. When the Custom Event runs, a new Incident for each Equipment with a Maintenance Date that matches the current date will be created and then the Maintenance Date will be set to the next interval period. These Incidents can then be worked normally to accomplish the regularly scheduled maintenance tasks.






Help Desk Portal - Email: help@pcr.com - Phone: 616.259.9242