Index: trunk/ippTools/share/dettool_toresidimfile.sql
===================================================================
--- trunk/ippTools/share/dettool_toresidimfile.sql	(revision 18561)
+++ trunk/ippTools/share/dettool_toresidimfile.sql	(revision 19621)
@@ -14,4 +14,6 @@
     detProcessedImfile.uri,
     detNormalizedImfile.uri AS det_uri,
+    detRun.det_id AS ref_det_id,
+    detRun.iteration AS ref_iter,
     rawExp.camera,
     rawExp.exp_tag
@@ -53,30 +55,40 @@
     detRun.workdir,
     detRun.reduction,
-    rawImfile.exp_id,
-    rawImfile.class_id,
-    rawImfile.uri,
-    'NULL' AS det_uri,
+    detProcessedImfile.exp_id,
+    detProcessedImfile.class_id,
+    detProcessedImfile.uri,
+    detNormalizedImfile.uri AS det_uri,
+    detRun.ref_det_id,
+    detRun.ref_iter,
     rawExp.camera,
     rawExp.exp_tag
 FROM detRun
+JOIN detRun AS detRunRef
+  ON detRunRef.det_id = detRun.ref_det_id
 JOIN detInputExp
-    USING(det_id, iteration)
+  ON detInputExp.det_id    = detRun.det_id
+ AND detInputExp.iteration = detRun.iteration
 JOIN rawExp
-    ON detInputExp.exp_id = rawExp.exp_id
-JOIN rawImfile
-    ON detInputExp.exp_id = rawImfile.exp_id
+  ON rawExp.exp_id = detInputExp.exp_id
+JOIN detProcessedImfile
+  ON detProcessedImfile.det_id    = detRun.det_id
+ AND detProcessedImfile.exp_id    = detInputExp.exp_id
+JOIN detNormalizedImfile
+  ON detNormalizedImfile.det_id    = detRun.ref_det_id
+ AND detNormalizedImfile.iteration = detRun.ref_iter
+ AND detNormalizedImfile.class_id  = detProcessedImfile.class_id
 LEFT JOIN detResidImfile
-    ON detRun.det_id = detResidImfile.det_id
-    AND detRun.iteration = detResidImfile.iteration
-    AND rawImfile.exp_id = detResidImfile.exp_id
-    AND rawImfile.class_id = detResidImfile.class_id
+  ON detResidImfile.det_id    = detRun.det_id      
+ AND detResidImfile.iteration = detRun.iteration   
+ AND detResidImfile.exp_id    = detProcessedImfile.exp_id   
+ AND detResidImfile.class_id  = detProcessedImfile.class_id 
 WHERE
-    detRun.state = 'run'
-    AND detRun.mode = 'verify'
-    -- AND detNormalizedImfile.fault = 0
-    -- AND detNormalizedExp.fault = 0
-    AND detResidImfile.det_id IS NULL
-    AND detResidImfile.iteration IS NULL
-    AND detResidImfile.exp_id IS NULL
-    AND detResidImfile.class_id IS NULL
+ detRun.state = 'run'
+ AND detRun.mode = 'verify'
+ AND detRunRef.state = 'stop'
+ AND detNormalizedImfile.fault = 0
+ AND detResidImfile.det_id IS NULL
+ AND detResidImfile.iteration IS NULL
+ AND detResidImfile.exp_id IS NULL
+ AND detResidImfile.class_id IS NULL
 ORDER BY exp_id