gistfile1.diff

Index: v_ug_app.sql
===================================================================
--- v_ug_app.sql	(revision 11548)
+++ v_ug_app.sql	(working copy)
@@ -17,12 +17,14 @@
       FROM sarappd s2
       WHERE s2.sarappd_pidm          = s1.sarappd_pidm
       AND s2.sarappd_term_code_entry = s1.sarappd_term_code_entry
-      AND s2.sarappd_apdc_date       =
-        (SELECT MAX(s3.sarappd_apdc_date)
+      AND s2.sarappd_appl_no         = s1.sarappd_appl_no
+      AND s2.sarappd_seq_no          =
+        (SELECT MAX(s3.sarappd_seq_no)
         FROM sarappd s3
         WHERE s3.sarappd_pidm          = s2.sarappd_pidm
         AND s3.sarappd_term_code_entry = s2.sarappd_term_code_entry
-        AND s3.sarappd_apdc_date       < s1.sarappd_apdc_date
+        AND s3.sarappd_appl_no         = s2.sarappd_appl_no
+        AND s3.sarappd_seq_no          < s1.sarappd_seq_no
         )
       ) AS prev_apdc_code
     FROM sarappd s1
@@ -30,13 +32,14 @@
     AND s1.sarappd_term_code_entry  =
       (SELECT MAX(s2.sarappd_term_code_entry)
       FROM sarappd s2
-      WHERE s2.sarappd_pidm=s1.sarappd_pidm
+      WHERE s2.sarappd_pidm = s1.sarappd_pidm
       )
-    AND s1.sarappd_apdc_date =
-      (SELECT MAX(s2.sarappd_apdc_date)
+    AND s1.sarappd_seq_no =
+      (SELECT MAX(s2.sarappd_seq_no)
       FROM sarappd s2
-      WHERE s2.sarappd_pidm          =s1.sarappd_pidm
+      WHERE s2.sarappd_pidm          = s1.sarappd_pidm
       AND s2.sarappd_term_code_entry = s1.sarappd_term_code_entry
+      AND s2.sarappd_appl_no         = s1.sarappd_appl_no
       )
     ) appd
   ON appd.sarappd_pidm         = saradap_pidm

Improve pulling in of application decision codes.

1. Additionally match saradap to sarappd using the appl_no field
2. Find the most recent decision code based on seq_no rather than apdc_date