Project-Analyser/to_check_if_not_in_yet.sql at main · cy2hq/Project-Analyser · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
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
- key fields that are not required on the record.
- APPLICATION PACKAGE SUBPACKAGES MISSING FROM THE PROJECT
- peoplecodes that do not exist anymore in the database
- SQL IDs that do not exist in the database (record views and standalone SQL )
- portal registry structures without a parent portal registry structure in the project


/* 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 || '%');