EAM reference links (U5_APM_REFS_LINKS_001)

SELECT
  ppm_code external_id,
  'pm schedule' external_type,
  CASE WHEN COALESCE(ppo_perioduom,'#1') = COALESCE(ppm_perioduom,'#1') AND COALESCE(ppo_freq,-1) = COALESCE(ppm_freq,-1)
       THEN ''
       ELSE CASE WHEN ppo_perioduom = 'Q'
                 THEN 'P' + CAST(ppo_freq*3 AS varchar(20)) + 'M'
                 ELSE 'P' + COALESCE(CAST(ppo_freq AS varchar(20)) + CAST(ppo_perioduom AS varchar(20)),'-none')
            END
       END interval_calendar,
  CASE WHEN COALESCE(ppo_performonweek,'#1') = COALESCE(ppm_performonweek,'#1') AND COALESCE(ppo_performonday,-1) = COALESCE(ppm_performonday,-1)
       THEN ''
       ELSE CAST(CASE WHEN ppo_performonweek = '1' THEN 'First'
                         WHEN ppo_performonweek = '2' THEN 'Second'
                         WHEN ppo_performonweek = '3' THEN 'Third'
                         WHEN ppo_performonweek = '4' THEN 'Fourth'
                         WHEN ppo_performonweek = 'L' THEN 'Last'
                         ELSE ''
                    END AS varchar(20)) + ' ' +
            CAST(CASE WHEN ppo_performonday = 0 THEN 'Sunday'
                         WHEN ppo_performonday = 1 THEN 'Monday'
                         WHEN ppo_performonday = 2 THEN 'Tuesday'
                         WHEN ppo_performonday = 3 THEN 'Wednesday'
                         WHEN ppo_performonday = 4 THEN 'Thursday'
                         WHEN ppo_performonday = 5 THEN 'Friday'
                         WHEN ppo_performonday = 6 THEN 'Saturday'
                         ELSE 'perform on none'
                    END AS varchar(20))
  END interval_description,
  CASE WHEN COALESCE(ppo_meter,-1) = COALESCE(ppm_meter,-1) AND COALESCE(ppo_metuom,'#1') = COALESCE(ppm_metuom,'#1')
       THEN ''
       ELSE COALESCE(CAST(ppo_meter AS varchar(20)),'none')
  END interval_meter_1,
  CASE WHEN COALESCE(ppo_meter,-1) = COALESCE(ppm_meter,-1) AND COALESCE(ppo_metuom,'#1') = COALESCE(ppm_metuom,'#1')
       THEN ''
       ELSE COALESCE(ppo_metuom,'none')
  END interval_meter_1_uom,
  CASE WHEN COALESCE(ppo_meter2,-1) = COALESCE(ppm_meter2,-1) AND COALESCE(ppo_metuom2,'#1') = COALESCE(ppm_metuom2,'#1')
       THEN ''
       ELSE COALESCE(CAST(ppo_meter2 AS varchar(20)),'none')
  END interval_meter_2,
  CASE WHEN COALESCE(ppo_meter2,-1) = COALESCE(ppm_meter2,-1) AND COALESCE(ppo_metuom2,'#1') = COALESCE(ppm_metuom2,'#1')
       THEN ''
       ELSE COALESCE(ppo_metuom2,'none')
  END interval_meter_2_uom,
  FORMAT(CAST(ppo_lastsaved as datetime2) at time zone 'UTC', N'yyyy-MM-ddTHH:MM:ssZ') date_modified,
  CASE WHEN ppm_notused = '+' THEN 'out of service' WHEN COALESCE(ppo_deactive,GETDATE()+1) < GETDATE() THEN 'inactive' ELSE 'active' END status,
  ppo_object + '#' + ppo_object_org asset_unique_id,
  ppo_object_org organization
FROM r5ppms, r5ppmobjects o
WHERE ppo_ppm = ppm_code
AND ppo_revision = ppm_revision
AND ppm_revrstatus = 'A'
AND NOT EXISTS ( SELECT 1 FROM r5ppmobjects o2 WHERE o2.ppo_ppm = o.ppo_ppm AND o2.ppo_revision = o.ppo_revision
                AND o2.ppo_object = o.ppo_object AND o2.ppo_object_org = o.ppo_object_org AND o2.ppo_pk > o.ppo_pk )

UNION SELECT
  mtp_code + '#' + mtp_org,
  'maintenance pattern',
  '',
  '',
  '',
  '',
  '',
  '',
  FORMAT(CAST(peq_lastsaved AS datetime2) at time zone 'UTC', N'yyyy-MM-ddTHH:MM:ssZ'),
  CASE WHEN peq_status = 'A' THEN 'active' ELSE 'inactive' END,
  peq_object + '#' + peq_object_org,
  peq_object_org
FROM r5maintenancepatterns, r5patternequipment
WHERE mtp_code = peq_mp
AND mtp_org = peq_mp_org
AND mtp_revision = peq_revision