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'||TO_CHAR(ppo_freq*3)||'M'
                 ELSE 'P'||COALESCE(TO_CHAR(ppo_freq)||TO_CHAR(ppo_perioduom),'-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 TO_CHAR(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)||
            TO_CHAR(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)
  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(TO_CHAR(ppo_meter),'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(TO_CHAR(ppo_meter2),'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,
  TO_CHAR(CAST(ppo_lastsaved as timestamp) at time zone 'UTC', 'YYYY"-"MM"-"DD"T"hh24":"mi":"ss"Z"') date_modified,
  CASE WHEN ppm_notused = '+' THEN 'out of service' WHEN COALESCE(ppo_deactive,SYSDATE+1) < SYSDATE 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',
  '',
  '',
  '',
  '',
  '',
  '',
  TO_CHAR(CAST(peq_lastsaved as timestamp) at time zone 'UTC', 'YYYY"-"MM"-"DD"T"hh24":"mi":"ss"Z"'),
  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