text: what can we search for
  - missing in project but used
  - in project but not used
  - missing in project and also not in db
  
  new ideas: 
  - translate values that do not exist in fields in the project
  - translate values that do not exist in the database
  
  
  /* TRANSLATE VALUES WITHOUT MATCHING FIELDS IN THE PROJECT */
  SELECT A.OBJECTVALUE1, A.OBJECTVALUE2, A.OBJECTVALUE3 FROM PSPROJECTITEM A WHERE
  A.OBJECTTYPE = 4 AND A.OBJECTID1 = 6 AND A.OBJECTID2 = 22 AND A.OBJECTID3 = 21 AND A.PROJECTNAME = 'CY2_PA_001' 
  AND NOT EXISTS (SELECT 'X' FROM PSPROJECTITEM B WHERE
  B.OBJECTTYPE = 2 AND B.OBJECTID2 = 0 AND B.OBJECTVALUE1 = A.OBJECTVALUE1)
  
  /* FIELDS WITH MISSING TRANSLATES IN THE PROJECT BASED ON LASTUPDOPRID */
  SELECT A.FIELDNAME, A.FIELDVALUE, A.EFFDT FROM PSXLATITEM A,  PSPROJECTITEM B WHERE
  B.OBJECTTYPE = 2 AND B.OBJECTVALUE1 = A.FIELDNAME AND B.OBJECTID2 = 0
  AND A.LASTUPDOPRID <> 'PPLSOFT' 
  AND B.PROJECTNAME = 'CY2_PA_001' 
  AND NOT EXISTS (SELECT 'X' FROM PSPROJECTITEM C WHERE
  C.OBJECTTYPE = 4 AND C.OBJECTID1 = 6 AND C.OBJECTVALUE1 = A.FIELDNAME  AND C.OBJECTID2 = 22 AND C.OBJECTVALUE2 = A.FIELDVALUE AND 
  C.OBJECTID3 = 21 AND %DATEIN(C.OBJECTVALUE3) = A.EFFDT)
  
  
  /* NOTE!!! Adding a second prompt value requires to have a prompt record or else to have a checkbox??? 
  
  /* FIELDS WITH MISSING TRANSLATES IN THE PROJECT BASED ON LASTUPDDATETIMEIUPD */
  SELECT A.FIELDNAME
   , A.FIELDVALUE
   , A.EFFDT 
    FROM PSXLATITEM A
    , PSPROJECTITEM B 
   WHERE B.OBJECTTYPE = 2 
     AND B.OBJECTVALUE1 = A.FIELDNAME 
     AND B.OBJECTID2 = 0 
     AND B.PROJECTNAME = :1
     AND A.LASTUPDDTTM >= %DATETIMEIN(:2)
     AND NOT EXISTS (
   SELECT 'X' 
    FROM PSPROJECTITEM C 
   WHERE C.OBJECTTYPE = 4 
     AND C.OBJECTID1 = 6 
     AND C.OBJECTVALUE1 = A.FIELDNAME 
     AND C.OBJECTID2 = 22 
     AND C.OBJECTVALUE2 = A.FIELDVALUE 
     AND  C.OBJECTID3 = 21 
    AND %datein(C.OBJECTVALUE3) = A.EFFDT)
  
  
  /* PERHAPS HANDY?? */
  
  -- custom peoplecodes that contain message catalog entries that exist in the project
     SELECT distinct B.PROJECTNAME, A.MESSAGE_SET_NBR, A.MESSAGE_NBR, C.OBJECTVALUE1, c.OBJECTVALUE2, c.OBJECTVALUE3, c.OBJECTVALUE4, c.OBJECTVALUE5
    FROM PSMSGCATDEFN A, PSPCMTXT C, PSPROJECTITEM B, PSPCMPROG D
    WHERE  B.OBJECTTYPE = 25
       AND B.OBJECTID1 = 48
       AND B.OBJECTVALUE1 = A.MESSAGE_SET_NBR
       AND B.OBJECTID2 = 49
       AND B.OBJECTVALUE2 = A.MESSAGE_NBR
       AND B.PROJECTNAME = 'CY2_CONFIG_001' 
  AND D.OBJECTID1 = C.OBJECTID1 
     AND D.OBJECTVALUE1 = C.OBJECTVALUE1 
     AND D.OBJECTID2 = C.OBJECTID2 
     AND D.OBJECTVALUE2 = C.OBJECTVALUE2 
     AND D.OBJECTID3 = C.OBJECTID3 
     AND D.OBJECTVALUE3 = C.OBJECTVALUE3 
     AND D.OBJECTID4 = C.OBJECTID4 
     AND D.OBJECTVALUE4 = C.OBJECTVALUE4 
     AND D.OBJECTID5 = C.OBJECTID5 
     AND D.OBJECTVALUE5 = C.OBJECTVALUE5 
     AND D.OBJECTID6 = C.OBJECTID6 
     AND D.OBJECTVALUE6 = C.OBJECTVALUE6 
     AND D.OBJECTID7 = C.OBJECTID7 
     AND D.OBJECTVALUE7 = C.OBJECTVALUE7 
     AND D.PROGSEQ = C.PROGSEQ
     AND D.LASTUPDOPRID <> 'PPLSOFT' 
   AND C.PCTEXT LIKE ('%' || A.MESSAGE_SET_NBR || ', ' || A.MESSAGE_NBR || '%');
   
  
   
  
  
       
  
  