[substitutions] Get APT Substitutions #peoplesoft #sql

substitutions.sql

SELECT DISTINCT a.emplid,
         a.ssr_apt_instance,
         x.descr50,
         a.ssr_apt_item_seq,
         d.ssr_item_id,
         d.descr50,
        A.SSR_APT_ITEM_SUB,
         e.ssr_item_id,
         e.descr50
FROM sysadm.ps_ssr_apt_subs a
INNER JOIN sysadm.ps_ssr_apt_item b
    ON (a.emplid = b.emplid
        AND a.ssr_apt_instance = b.ssr_apt_instance
        AND a.ssr_apt_item_seq = b.ssr_apt_item_seq)
INNER JOIN sysadm.ps_ssr_apt_item c
    ON (a.emplid = c.emplid
        AND a.ssr_apt_instance = c.ssr_apt_instance
        AND a.ssr_apt_item_sub = c.ssr_apt_item_seq)
INNER JOIN sysadm.ps_ssr_air_hdr d
    ON ( d.ssr_item_id = b.ssr_item_id)
INNER JOIN sysadm.ps_ssr_air_hdr e
    ON ( e.ssr_item_id = c.ssr_item_id)
INNER JOIN sysadm.ps_ku_pln_info_vw x
    ON (a.emplid = x.emplid
        AND a.ssr_apt_instance = x.ssr_apt_instance)
WHERE d.effdt = 
  (SELECT max(d_ed.effdt)
  FROM sysadm.ps_ssr_air_hdr d_ed
  WHERE d.ssr_item_id = d_ed.ssr_item_id)
        AND e.effdt = 
  (SELECT max(e_ed.effdt)
  FROM sysadm.ps_ssr_air_hdr e_ed
  WHERE e.ssr_item_id = e_ed.ssr_item_id)
ORDER BY  a.emplid, A.SSR_APT_INSTANCE, a.ssr_Apt_item_seq ;