!***********************************************************************
!  CUSTINFO: Customer Intelligence SQR                                 *
!***********************************************************************
!***********************************************************************
!                                                                      *
!               Confidentiality Information:                           *
!                                                                      *
! This module is the confidential and proprietary information of       *
! PeopleSoft, Inc.; it is not to be copied, reproduced, or transmitted *
! in any form, by any means, in whole or in part, nor is it to be used *
! for any purpose other than that for which it is expressly provided   *
! without the written permission of PeopleSoft.                        *
!                                                                      *
! Copyright (c) 1988-2005 PeopleSoft, Inc. All Rights Reserved         *
!                                                                      *
!***********************************************************************
!                                                                      *
! Version History: 10/06/2005 Initial Rollout                          *
!               2: 12/16/2005 Changed aliases due to DB2 problem       *
!                  12/23/2005 Incoporated Tree SQLs Q50-Q54		     *
!                                                                      *
!                                                                      *
!***********************************************************************
#include 'setenv.sqc'

#define Stars      '**************************************************************'
#define Dashes     '--------------------------------------------------------------'

! The following #defines are used throughout the test.  These defines
! are include in the PT8 version of PeopleTools, but older PeopleTools
! require them for this test to work as designed

#ifndef Native-DateTimeMask

    #define PTDateDelim /
    #define DDelimiter  '{PTDateDelim}'             !sDate
    #define PDelimiter  '{PTDateDelim}'
    #define PTTimeDelim :
    #define TDelimiter '{PTTimeDelim}'              !sTime

    #define PTMon       MM
    #define PTDay       DD
    #define PTYear      YYYY            !This format is used internally and MUST remain 4 digits
    #define PT2Year     RR              !This format is used internally if Year4 = '0'.  The RR will ensure that SQR
                                        ! keeps track of the Century
    #if {Year4} = '0'
        #define PTRPTYear {PT2Year}     !This triggers SQR to properly determine the Century
    #else
        #define PTRPTYear {PTYear}      !This is the Default - 4Years
    #end-if

! Format PTRPTDATE based on what DateType is set to (SETENV.SQC or overriden in SQR Program).
    #if {DateType} = '0'
        #define PTRPTDATE '{PTMon}{PTDateDelim}{PTDay}{PTDateDelim}{PTRPTYear}'        !MM/DD/YYYY (Default)
    #else
        #if {DateType} = '1'
            #define PTRPTDATE '{PTDay}{PTDateDelim}{PTMon}{PTDateDelim}{PTRPTYear}'    !DD/MM/YYYY
        #else  ! {DateType = '2'
            #define PTRPTDATE '{PTRPTYear}{PTDateDelim}{PTMon}{PTDateDelim}{PTDay}'    !YYYY/MM/DD
        #end-if
    #end-if

    #define PTRPTTIME   'HH:MI:SS'

    #ifdef DB2ALL
        #define Native-DateTimeMask 'YYYY-MM-DD-HH.MI.SS.NNNNNN'
        #define Native-DateMask     'YYYY-MM-DD'
        #define Native-TimeMask     'HH.MI.SS'
        #define Native-TimeMaskMS   'HH.MI.SS.NNNNNN'
    #end-if

    #ifdef INFORMIX
        #define Native-DateTimeMask 'YYYY-MM-DD HH:MI:SS.NNN'
        #define Native-DateMask     'YYYY-MM-DD'
        #define Native-TimeMask     'HH:MI:SS.NNN'
    #end-if

    #ifdef  MICROSOFT
        #define Native-DateTimeMask 'YYYY-MM-DD HH:MI:SS.NNN'
        #define Native-DateMask     'YYYY-MM-DD'
        #define Native-TimeMask     'HH:MI:SS.NNN'
    #end-if

    #ifdef ORACLE
        #define Native-DateTimeMask 'DD-MON-YYYY HH:MI:SS AM'
        #define Native-DateMask     'DD-MON-YYYY'
        #define Native-TimeMask     'HH:MI:SS AM'
    #end-if

    #ifdef SYBASE
        #define Native-DateTimeMask 'YYYY-MM-DD HH:MI:SS.NNN'
        #define Native-DateMask     'YYYY-MM-DD'
        #define Native-TimeMask     'HH:MI:SS.NNN'
    #end-if

    #ifdef SQLBASE
        #define Native-DateTimeMask 'YYYY-MM-DD-HH.MI.SS.NNNNNN'
        #define Native-DateMask     'YYYY-MM-DD'
        #define Native-TimeMask     'HH.MI.SS'
        #define Native-TimeMaskMS   'HH.MI.SS.NNNNNN'
    #end-if


!Misc Defines
    #define TRUE     1
    #define FALSE    0
    #define OK      'OK'

    #define No      'N'
    #define Yes     'Y'

#end-if

!**********************************************************************
Begin-Setup
!**********************************************************************

#include 'setupdb.sqc'

Declare-Variable
    date    $wkDate
    date    $wkDate1
    date    $wkDate2
    integer #arUTCOffset
    integer #arDSTOffset
    integer #arDSTMonth
    integer #arDSTDay
    integer #arDSTDOW
    integer #arDSTHour
    integer #arDSTMinute
End-Declare

End-Setup

!**********************************************************************
Begin-Program
!**********************************************************************

        do InitPeopleTools

        do Get-Current-DateTime ! need this to set $AsOfToday

        let $error_found = {No}

        display ' '
        display ' '
        do Get_ToolsRel
        display 'PeopleTools Release : ' noline
        display $toolsrel

        display ' '
        display ' '
        display 'Enter file name (including drive and path) for extract file -'
        display ' '
        Input $FileName Type=Text 'File Name'
!        If $sqr-platform = 'WINDOWS-NT'
!                Let $FileName = 'C:\BAI\CustInfoXXXX.txt'
!        Else ! UNIX
!                Let $FileName = '/tmp/appldir/CustInfo.txt'
!        End-If
        Open $FileName as 1 For-Writing Record=255 Status=#OpenStat
        If #OpenStat = 0
                let $tab = chr(9)
                do Run_Queries
                Close 1
        Else
                Show 'Error Opening ' $FileName
        End-If

End-Program

Begin-Procedure Run_Queries

        display ' '
        display ' '

        input $CustID      Type=Text 'Customer ID   '
        input $CustName    Type=Text 'Customer Name '
        input $CustContact Type=Text 'Contact Name  '
        input $CustMail    Type=Text 'E-Mail        '
        input $CustPhone   Type=Text 'Phone         '

        do Get_Release

        let $date_time_stamp = datetostr(datenow(), 'YYYY-MM-DD HH:MI:SS')
        display ' '
        display ' '
        display 'Start  : ' noline
        display $date_time_stamp
        display ' '

        let $CustID = Upper($CustID)
        string '001' $CustID $CustName $release $Database $CustContact $CustMail $CustPhone '2' $date_time_stamp $toolsrel $sqr-ver by $tab into $record
        Write 1 From $record

        do Q02
        do Q03
        do Q04

        If $toolsrel <= '8.4'
                display 'Bypassing Q05'
                display 'Bypassing Q06'
        else
                do Q05
                do Q06
        end-if

        do Q07
        do Q08
        do Q09
        do Q10
        do Q11
        do Q12
        do Q13 ! calls Q14
        do Q15
        do Q16
        do Q17 ! calls Q18

        do Q20
        do Q21
        do PRCSDEFN ! Q22, Q23, Q24 & Q25

        do Q29
        do Q30
        do Q31
        do Q32
        do Q33
        do Q34

        do Q38
        do Q39
        do Q40
!        do Q41
        do Q42

        do Q50
        do Q51
        do Q52
        do Q53
        do Q54

        let $date_time_stamp = datetostr(datenow(), 'YYYY-MM-DD HH:MI:SS')
        display ' '
        display 'Finish : ' noline
        display $date_time_stamp
        display ' '
        display ' '

        Write 1 From '900' $tab $date_time_stamp

End-Procedure

Begin-Procedure Get_ToolsRel
        Begin-Select
PSSTATUS.TOOLSREL
PSSTATUS.UNICODE_ENABLED
FROM PSSTATUS
        End-Select
        let $toolsrel = rtrim(&PSSTATUS.TOOLSREL,' ')
End-Procedure

Begin-Procedure Get_Release
        Begin-Select
RELEASELABEL
FROM PSRELEASE
        End-Select
        let $release = rtrim(&RELEASELABEL,' ')
End-Procedure

Begin-Procedure Q02 ! How many rows in each database table?
        display 'Processing Q02.' noline
        let $query = 'Q02'
        let $where = 'REC.RECTYPE = 0 AND REC.OBJECTOWNERID <> ''PPT'''
        do RECNAME_Cursor
        display 'done'
End-Procedure

Begin-Procedure Q03 ! What are the batch processing times for the last quarter?
        display 'Processing Q03.' noline
        let $wkDate = dateadd($current-date,'MONTH',0-3)
        Begin-Select DISTINCT
Q03.PRCSTYPE
Q03.PRCSNAME
        do Q03_DETAIL
FROM PSPRCSRQST Q03
WHERE Q03.RUNSTATUS = '9'
AND Q03.RUNDTTM > $wkDate
GROUP BY Q03.PRCSTYPE, Q03.PRCSNAME
ORDER BY Q03.PRCSTYPE, Q03.PRCSNAME
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q03_DETAIL
        let #counter = 0
        let #minutes = 0
        Begin-Select
DETAIL.BEGINDTTM
DETAIL.ENDDTTM
        let #counter = #counter + 1
        let #minutes = #minutes + datediff(&DETAIL.ENDDTTM, &DETAIL.BEGINDTTM, 'MINUTE')
FROM PSPRCSRQST DETAIL
WHERE DETAIL.RUNSTATUS = '9'
AND DETAIL.RUNDTTM > $wkDate
AND DETAIL.PRCSTYPE = &Q03.PRCSTYPE
AND DETAIL.PRCSNAME = &Q03.PRCSNAME
        End-Select
        let $pt = rtrim(&Q03.PRCSTYPE, ' ')
        Evaluate $pt
                When = 'Application Engine'
                        Let $ptype = '01'
                When = 'COBOL SQL'
                        Let $ptype = '02'
                When = 'Crw Online'
                        Let $ptype = '03'
                When = 'Crystal'
                        Let $ptype = '04'
                When = 'Crystal Check'
                        Let $ptype = '05'
                When = 'Cube Builder'
                        Let $ptype = '06'
                When = 'Data Mover'
                        Let $ptype = '07'
                When = 'Database Agent'
                        Let $ptype = '08'
                When = 'Demand Planning Upload'
                        Let $ptype = '09'
                When = 'Essbase'
                        Let $ptype = '10'
                When = 'HyperCube Builder'
                        Let $ptype = '11'
                When = 'Message Agent API'
                        Let $ptype = '12'
                When = 'nVision'
                        Let $ptype = '13'
                When = 'nVision-Report'
                        Let $ptype = '14'
                When = 'nVision-ReportBook'
                        Let $ptype = '15'
                When = 'Optimization Engine'
                        Let $ptype = '16'
                When = 'PSJob'
                        Let $ptype = '17'
                When = 'SQR PO-Special Process'
                        Let $ptype = '18'
                When = 'SQR Process'
                        Let $ptype = '19'
                When = 'SQR Report'
                        Let $ptype = '20'
                When = 'SQR Report For WF Delivery'
                        Let $ptype = '21'
                When = 'Winword'
                        Let $ptype = '22'
        End-Evaluate
        let $process = rtrim(&Q03.PRCSNAME, ' ')
        let #avg = trunc(#minutes / #counter + 0.499, 0)
        let $minutes = to_char(#avg)
        Write 1 From '100' $tab 'Q03' $tab $process $tab $minutes $tab $ptype
End-Procedure

Begin-Procedure Q04 ! How many user Ids?
        display 'Processing Q04.' noline
        Begin-Select
COUNT(*)        &USERS
FROM PSOPRDEFN
        End-Select
        Write 1 From '100' $tab 'Q04' $tab '# UserIDs' $tab &USERS $tab
        display 'done'
End-Procedure

Begin-Procedure Q05 ! How many users have logged on in the last seven days?
        display 'Processing Q05.' noline
        If $toolsrel <= '8.4'
                let $where = '1 = 0'
        else
                let $wkDate = dateadd($current-date,'DAY',0-7)
                let $where  = 'LASTSIGNONDTTM > ''' || $wkDate || ''''
        end-if
        Begin-Select
COUNT(*)        &USERS_WEEK
FROM PSOPRDEFN
WHERE [$where]
        End-Select
        Write 1 From '100' $tab 'Q05' $tab '# UserIDs (week)' $tab &USERS_WEEK $tab
        display 'done'
End-Procedure

Begin-Procedure Q06 ! How many users have logged on in the last year?
        display 'Processing Q06.' noline
        If $toolsrel <= '8.4'
                let $where = '1 = 0'
        else
                let $wkDate = dateadd($current-date,'YEAR',0-1)
                let $where  = 'LASTSIGNONDTTM > ''' || $wkDate || ''''
        end-if
        Begin-Select
COUNT(*)        &USERS_YEAR
FROM PSOPRDEFN
WHERE [$where]
        End-Select
        Write 1 From '100' $tab 'Q06' $tab '# UserIDs (year)' $tab &USERS_YEAR $tab
        display 'done'
End-Procedure

Begin-Procedure Q07 ! How many users have access to each component?
        display 'Processing Q07.' noline
        let #pgs = 0


        If $toolsrel <= '8.4'
                let $extra_field = 'PNLGRP.MARKET'
                let $where = '1 = 1'
        else
                let $extra_field = 'PNLGRP.OBJECTOWNERID'
                let $where = 'PNLGRP.OBJECTOWNERID <> ''PPT'''
        end-if


        Begin-Select ! Loops = 12 ! this is to limit # of rows for testing purposes
PNLGRP.PNLGRPNAME
PNLGRP.MARKET
[$extra_field]          &PNLGRP_FLD = TEXT


        If $toolsrel <= '8.4'
                let $owner = ''
        else
                let $owner = rtrim(&PNLGRP_FLD,' ')
        end-if


        let #pgs = #pgs + 1
        if mod(#pgs,1000) = 0
                display '.' noline
        end-if


        do Q07_DETAIL
FROM PSPNLGRPDEFN PNLGRP
WHERE [$where]
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q07_DETAIL
        let #cntr = 0
        Begin-Select DISTINCT
Q07E.OPRID
        let #cntr = #cntr + 1
FROM PSAUTHITEM Q07A, PSMENUITEM Q07B, PSROLECLASS Q07C, PSROLEUSER Q07D, PSOPRDEFN Q07E
WHERE Q07B.PNLGRPNAME = &PNLGRP.PNLGRPNAME
AND   Q07B.MARKET     = &PNLGRP.MARKET
AND   Q07B.MENUNAME   = Q07A.MENUNAME
AND   Q07B.BARNAME    = Q07A.BARNAME
AND   Q07B.ITEMNAME   = Q07A.BARITEMNAME
AND   Q07C.CLASSID    = Q07A.CLASSID
AND   Q07D.ROLENAME   = Q07C.ROLENAME
AND   Q07E.OPRID      = Q07D.ROLEUSER
GROUP BY Q07E.OPRID
        End-Select
        let $component = rtrim(&PNLGRP.PNLGRPNAME,' ') || '.' || rtrim(&PNLGRP.MARKET, ' ')
        let $cntr = to_char(#cntr)
        string '100' 'Q07' $component $cntr $owner by $tab into $Q07_record
        Write 1 From $Q07_record
End-Procedure

Begin-Procedure Q08 ! How many roles?
        display 'Processing Q08.' noline
        Begin-Select
COUNT(*)        &ROLES
FROM PSROLEDEFN
        End-Select
        Write 1 From '100' $tab 'Q08' $tab '# Roles' $tab &ROLES $tab
        display 'done'
End-Procedure

Begin-Procedure Q09 ! How many permission lists?
        display 'Processing Q09.' noline
        Begin-Select
COUNT(*)        &CLASSES
FROM PSCLASSDEFN
        End-Select
        Write 1 From '100' $tab 'Q09' $tab '# Permission Lists' $tab &CLASSES $tab
        display 'done'
End-Procedure

Begin-Procedure Q10 ! What is the base language?
        display 'Processing Q10.' noline
        Begin-Select
PSOPTIONS.LANGUAGE_CD
FROM PSOPTIONS
        End-Select
        Write 1 From '100' $tab 'Q10' $tab &PSOPTIONS.LANGUAGE_CD $tab '0' $tab
        display 'done'
End-Procedure

Begin-Procedure Q11 ! What are the other languages loaded
        display 'Processing Q11.' noline
        Begin-Select
PSLANGUAGES.LANGUAGE_CD
        Write 1 From '100' $tab 'Q11' $tab &PSLANGUAGES.LANGUAGE_CD $tab '0' $tab
FROM PSLANGUAGES WHERE INSTALLED = 1
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q12 ! Is this a Unicode database
        display 'Processing Q12.' noline
        if &PSSTATUS.UNICODE_ENABLED = 1
                let $unicode = 'Yes'
        else
                let $unicode = 'No'
        end-if
        Write 1 From '100' $tab 'Q12' $tab $unicode $tab '0' $tab
        display 'done'
End-Procedure

Begin-Procedure Q13 ! Which record have been added or changed?
        display 'Processing Q13.' noline
        let $query = 'Q13'
        let $where = 'REC.LASTUPDOPRID <> ''' || 'PPLSOFT'''
        do RECNAME_Cursor
        display 'done'
End-Procedure

Begin-Procedure Q14 ! Which record.fields have been added or changed?
        ! called by Q13
        Begin-Select
FLD.FIELDNAME
FLD.FIELDNUM
        let $fld_name = rtrim(&FLD.FIELDNAME, ' ')
        Write 1 From '200' $tab 'Q14' $tab &FLD.FIELDNUM $tab $tab $fld_name
FROM PSRECFIELD FLD
WHERE FLD.RECNAME = &REC.RECNAME AND FLD.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
End-Procedure

Begin-Procedure Q15 ! Which labels have been added or changed?
        display 'Processing Q15.' noline
        Begin-Select
DBFLD.FIELDNAME
DBFLD.FIELDTYPE
DBFLD.OBJECTOWNERID
        let $name  = rtrim(&DBFLD.FIELDNAME,' ')
        let $owner = rtrim(&DBFLD.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q15' $tab $name $tab &DBFLD.FIELDTYPE $tab $owner
FROM PSDBFIELD DBFLD
WHERE DBFLD.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q16 ! Which components have been added or changed?
        display 'Processing Q16.' noline
        let $query = 'Q16'
        let $where = 'PGDEFN.LASTUPDOPRID <> ''PPLSOFT'''
        do PSPNLGRPDEFN_Cursor
        display 'done'
End-Procedure

Begin-Procedure Q17 ! Which pages have been added or changed?
        display 'Processing Q17.' noline


        If $toolsrel <= '8.4'
                let $q17_field = 'Q17.LANGUAGE_CD'
        else
                let $q17_field = 'Q17.OBJECTOWNERID'
        end-if


        Begin-Select ! Loops = 12 ! this is to limit # of rows for testing purposes
Q17.PNLNAME
Q17.PNLTYPE
Q17.OBJECTOWNERID
[$q17_field]          &q17_field = TEXT


                If $toolsrel <= '8.4'
                        let $name  = rtrim(&Q17.PNLNAME,' ') || '.' || rtrim(&q17_field,' ')
                        let $q18_field = 'Q18.FIELDNUM + 0'
                        let $where = 'Q18.PNLNAME = ''' || &Q17.PNLNAME || ''' AND Q18.LANGUAGE_CD = ''' || &q17_field || ''''
                else
                        let $name  = rtrim(&Q17.PNLNAME,' ')
                        let $q18_field = 'Q18.PNLFLDID'
                        let $where = 'Q18.PNLNAME = ''' || &Q17.PNLNAME || ''''
                end-if


                let $owner = rtrim(&Q17.OBJECTOWNERID,' ')
                Write 1 From '100' $tab 'Q17' $tab $name $tab &Q17.PNLTYPE $tab $owner
                do Q18


FROM PSPNLDEFN Q17
WHERE Q17.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q18 ! Which page.fields have been added or changed?
        ! called by Q17
        Begin-Select ON-ERROR=SQL-Error
Q18.PNLNAME
Q18.RECNAME
Q18.FIELDNAME
[$q18_field]    &q18_field = number
                let $rec_name = rtrim(&Q18.RECNAME, ' ')
                let $fld_name = rtrim(&Q18.FIELDNAME, ' ')
                Write 1 From '200' $tab 'Q18' $tab &q18_field $tab $rec_name $tab $fld_name
FROM PSPNLFIELD Q18
WHERE [$where] AND Q18.RECNAME <> ' ' AND Q18.FIELDNAME <> ' '
        End-Select
End-Procedure

Begin-Procedure Q20 ! Which PeopleCode programs have been added or changed?
        display 'Processing Q20.' noline
        let $last_table = ''
        let $last_where = ''
        Begin-Select
PSPCMPROG.OBJECTID1
PSPCMPROG.OBJECTVALUE1
PSPCMPROG.OBJECTID2
PSPCMPROG.OBJECTVALUE2
PSPCMPROG.OBJECTID3
PSPCMPROG.OBJECTVALUE3
PSPCMPROG.OBJECTID4
PSPCMPROG.OBJECTVALUE4
PSPCMPROG.OBJECTID5
PSPCMPROG.OBJECTVALUE5
PSPCMPROG.OBJECTID6
PSPCMPROG.OBJECTVALUE6
PSPCMPROG.OBJECTID7
PSPCMPROG.OBJECTVALUE7
        let $o1 = rtrim(&PSPCMPROG.OBJECTVALUE1,' ')
        let $o2 = rtrim(&PSPCMPROG.OBJECTVALUE2,' ')
        let $o3 = rtrim(&PSPCMPROG.OBJECTVALUE3,' ')
        let $o4 = rtrim(&PSPCMPROG.OBJECTVALUE4,' ')
        let $o5 = rtrim(&PSPCMPROG.OBJECTVALUE5,' ')
        let $o6 = rtrim(&PSPCMPROG.OBJECTVALUE6,' ')
        let $o7 = rtrim(&PSPCMPROG.OBJECTVALUE7,' ')

        Evaluate &PSPCMPROG.OBJECTID1
        When =   1
                let $pcode_type = '08'                 ! Record
                string '' $o1 $o2 $o3 by $tab into $objects
                let $table = 'PSRECDEFN'
                let $where = 'RECNAME = ''' || $o1 || ''''
                break
        When =   3
                let $pcode_type = '09'                 ! Menu
                string $o1 $o2 $o3 $o4 by $tab into $objects
                let $table = 'PSMENUDEFN'
                let $where = 'MENUNAME = ''' || $o1 || ''''
                break
        When =   9
                let $pcode_type = '44'                 ! Page
                string $o1 '' '' $o2 by $tab into $objects
                let $table = 'PSPNLDEFN'
                let $where = 'PNLNAME = ''' || $o1 || ''''
                break
        When =  10
                let $comp_mkt = substr($o1 || '.' || $o2,1,30)
                if &PSPCMPROG.OBJECTID3 = 12
                        let $pcode_type = '46'         ! Component
                        string $comp_mkt '' '' $o3 by $tab into $objects
                else
                        if &PSPCMPROG.OBJECTID4 = 12
                                let $pcode_type = '47' ! Component Record
                                string $comp_mkt $o3 '' $o4 by $tab into $objects
                        else
                                let $pcode_type = '48' ! Component Record Field
                                string $comp_mkt $o3 $o4 $o5 by $tab into $objects
                        end-if
                end-if
                let $table = 'PSPNLGRPDEFN'
                let $where = 'PNLGRPNAME = ''' || $o1 || ''' AND MARKET = ''' || $o2 || ''''
                break
        When =  60
                if &PSPCMPROG.OBJECTID2 = 12
                        let $pcode_type = '39'         ! Message
                        string $o1 '' '' $o2 by $tab into $objects
                else
                        let $pcode_type = '40'         ! Subscription
                        string $o1 '' $o2 $o3 by $tab into $objects
                end-if
                let $table = 'PSMSGDEFN'
                let $where = 'MSGNAME = ''' || $o1 || ''''
                break
        When =  61
                        let $pcode_type = '41'         ! Channel
                        string $o1 '' '' $o2 by $tab into $objects
                let $table = 'PSCHNLDEFN'
                let $where = 'CHNLNAME = ''' || $o1 || ''''
                break
        When =  66
                let $pcode_type = '43'                 ! Application Engine
                string $o1 $o2 $o6 $o7 by $tab into $objects
                let $table = 'PSAEAPPLDEFN'
                let $where = 'AE_APPLID = ''' || $o1 || ''''
                break
        When =  74
                if &PSPCMPROG.OBJECTID2 = 12
                        let $pcode_type = '42'         ! Component Interface
                        string $o1 '' '' $o2 by $tab into $objects
                else
                        let $pcode_type = '66'         ! Component Interface Property
                        if &PSPCMPROG.OBJECTID3 = 12
                                string $o1 $o2 '' $o3 by $tab into $objects
                        else
                                if &PSPCMPROG.OBJECTID4 = 12
                                        string $o1 $o2 $o3 $o4 by $tab into $objects
                                else
                                        let $ci = substr($o2 || '.' || $o3,1,30)
                                        string $o1 $ci $o4 $o5 by $tab into $objects
                                end-if
                        end-if
                end-if
                let $table = 'PSBCDEFN'
                let $where = 'BCNAME = ''' || $o1 || ''''
                break
        When = 104
                let $pcode_type = '58'                 ! Application Package
                let $table = 'PSPACKAGEDEFN'
                let $where = 'PACKAGEID = ''' || $o1 || ''' AND PACKAGEROOT = ''' || $o1 || ''' AND QUALIFYPATH = ''.'''
                if &PSPCMPROG.OBJECTID3 = 12
                        string $o1 $o2 '' $o3 by $tab into $objects
                else
                        if &PSPCMPROG.OBJECTID4 = 12
                                string $o1 $o2 $o3 $o4 by $tab into $objects
                        else
                                let $pkg = substr($o3 || ':' || $o4,1,30)
                                string $o1 $o2 $pkg $o5 by $tab into $objects
                        end-if
                end-if
                break
        When-Other
                let $pcode_type = '??'                 ! PeopleCode Type Unknown -- skip
        End-Evaluate
        If $pcode_type <> '??'
                if $table <> $last_table or $where <> $last_where
                        do Q20_OOID
                end-if
                let $last_table = $table
                let $last_where = $where
                Write 1 From '101' $tab 'Q20' $tab $pcode_type $tab $objects $tab $owner
        End-If
FROM PSPCMPROG
WHERE PROGSEQ = 0 AND LASTUPDOPRID <> 'PPLSOFT'
!*****AND (LASTUPDOPRID <> 'PPLSOFT' OR EXISTS (SELECT 'X' FROM PSPROJECTITEM WHERE PROJECTNAME='PCODE' AND OBJECTID1=PSPCMPROG.OBJECTID1 AND OBJECTVALUE1=PSPCMPROG.OBJECTVALUE1 AND OBJECTID2=PSPCMPROG.OBJECTID2 AND OBJECTVALUE2=PSPCMPROG.OBJECTVALUE2))
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q20_OOID ! gets the OBJECTOWNERID of the appropriate object
        If $toolsrel <= '8.4' and $table = 'PSPNLGRPDEFN'
                let $owner = ''
        else
                Begin-Select on-error=Q20_error_handler
OBJECTOWNERID &ooid
                        let $owner = rtrim(&ooid,' ')
FROM [$table]
WHERE [$where]
                End-Select
        End-If
End-Procedure

Begin-Procedure Q20_error_handler
        let $owner = '1234'
End-Procedure

Begin-Procedure Q21 ! Which AE programs have been added or changed?
        display 'Processing Q21.' noline
        Begin-Select
Q21.AE_APPLID
Q21.OBJECTOWNERID
        let $name  = rtrim(&Q21.AE_APPLID,' ')
        let $owner = rtrim(&Q21.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q21' $tab $name $tab $tab $owner
FROM PSAEAPPLDEFN Q21
WHERE Q21.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q29 ! What PS Queries have been added or changed?
        display 'Processing Q29.' noline
        Begin-Select
Q29.QRYNAME
        let $name = rtrim(&Q29.QRYNAME,' ')
        Write 1 From '100' $tab 'Q29' $tab $name $tab $tab
FROM PSQRYDEFN Q29
WHERE Q29.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q30 ! How many channels are defined?
        display 'Processing Q30.' noline
        Begin-Select
COUNT(*)        &CHANNELS
FROM PSCHNLDEFN
        End-Select
        Write 1 From '100' $tab 'Q30' $tab '# Channels' $tab &CHANNELS $tab
        display 'done'
End-Procedure

Begin-Procedure Q31 ! How many total CIs?
        display 'Processing Q31.' noline
        Begin-Select
COUNT(*)        &CIS
FROM PSBCDEFN
        End-Select
        Write 1 From '100' $tab 'Q31' $tab '# Component Interfaces' $tab &CIS $tab
        display 'done'
End-Procedure

Begin-Procedure Q32 ! What CIs have been added or changed?
        display 'Processing Q32.' noline
        Begin-Select
Q32.BCNAME
Q32.BCSTDMETHODS
Q32.OBJECTOWNERID
        let $name  = rtrim(&Q32.BCNAME,' ')
        let $owner = rtrim(&Q32.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q32' $tab $name $tab &Q32.BCSTDMETHODS $tab $owner
FROM PSBCDEFN Q32
WHERE Q32.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q33 ! How many total app messages
        display 'Processing Q33.' noline
        Begin-Select
COUNT(*)        &MSGS
FROM PSMSGDEFN
        End-Select
        Write 1 From '100' $tab 'Q33' $tab '# Application Messages' $tab &MSGS $tab
        display 'done'
End-Procedure

Begin-Procedure Q34 ! What app messages have been added or changed?
        display 'Processing Q34.' noline
        Begin-Select
Q34.MSGNAME
Q34.MSGSTATUS
Q34.OBJECTOWNERID
        let $name  = rtrim(&Q34.MSGNAME,' ')
        let $owner = rtrim(&Q34.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q34' $tab $name $tab &Q34.MSGSTATUS $tab $owner
FROM PSMSGDEFN Q34
WHERE Q34.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q38 ! Which navigation CREFs have been added or changed?
        display 'Processing Q38.' noline
        Begin-Select
Q38.PORTAL_NAME
Q38.PORTAL_OBJNAME
Q38.OBJECTOWNERID
        let $name  = rtrim(&Q38.PORTAL_OBJNAME,' ')
        let $owner = rtrim(&Q38.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q38' $tab $name $tab '0' $tab $owner
FROM PSPRSMDEFN Q38
WHERE Q38.PORTAL_REFTYPE = 'C' AND Q38.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q39 ! Which trees have been added or changed?
        display 'Processing Q39.' noline
        Begin-Select DISTINCT
Q39.TREE_NAME
        let $name  = rtrim(&Q39.TREE_NAME,' ')
        Write 1 From '100' $tab 'Q39' $tab $name $tab '0' $tab
FROM PSTREEDEFN Q39
WHERE Q39.LASTUPDOPRID <> 'PPLSOFT'
AND Q39.EFF_STATUS = 'A'
AND Q39.EFFDT = (SELECT MAX(E.EFFDT) FROM PSTREEDEFN E
                        WHERE E.SETID = Q39.SETID
                          AND E.SETCNTRLVALUE = Q39.SETCNTRLVALUE
                          AND E.TREE_NAME = Q39.TREE_NAME
                          AND E.EFFDT <= $AsOfToday)
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q40 ! Which workflow activities have been added or changed?
        display 'Processing Q40.' noline
        Begin-Select
Q40.ACTIVITYNAME
Q40.OBJECTOWNERID
        let $name  = rtrim(&Q40.ACTIVITYNAME,' ')
        let $owner = rtrim(&Q40.OBJECTOWNERID,' ')
        Write 1 From '100' $tab 'Q40' $tab $name $tab '0' $tab $owner
FROM PSACTIVITYDEFN Q40
WHERE Q40.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q41 ! What Record Groups have been added or changed?
        display 'Processing Q41.' noline
        Begin-Select
REC_GROUP_TBL.REC_GROUP_ID
        let $name  = rtrim(&REC_GROUP_TBL.REC_GROUP_ID,' ')
        Write 1 From '100' $tab 'Q41' $tab $name $tab '0' $tab
FROM PS_REC_GROUP_TBL REC_GROUP_TBL
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q42 ! How many Business Units are associated with each Record Group/Set Id combination?
        display 'Processing Q42.' noline
        Begin-Select
Q42.REC_GROUP_ID
Q42.SETID
COUNT(*)        &SETCNTRLVALUES
        let $rec_group = rtrim(&Q42.REC_GROUP_ID, ' ')
        let $setid = rtrim(&Q42.SETID,' ')
        string $rec_group $setid by '/' into $name
        Write 1 From '100' $tab 'Q42' $tab $name $tab &SETCNTRLVALUES $tab
FROM PS_SET_CNTRL_GROUP Q42
GROUP BY Q42.REC_GROUP_ID, Q42.SETID
ORDER BY Q42.REC_GROUP_ID, Q42.SETID
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q50 ! PSTREEDEFN plus look for DYNAMIC_RANGE leafs and count any branches
        display 'Processing Q50.' noline
        Begin-Select
Q50.SETID
Q50.SETCNTRLVALUE
Q50.TREE_NAME
Q50.EFFDT
Q50.TREE_STRCT_ID
Q50.USE_LEVELS
Q50.NODE_COUNT
Q50.LEAF_COUNT
        let $setid = rtrim(&Q50.SETID,' ')
        let $cntrl = rtrim(&Q50.SETCNTRLVALUE,' ')
        let $tname = rtrim(&Q50.TREE_NAME,' ')
        let $struc = rtrim(&Q50.TREE_STRCT_ID,' ')

        do Q50_Range
        do Q50_Branch

        string '500' 'Q50' $setid $cntrl $tname $struc &Q50.USE_LEVELS &Q50.NODE_COUNT &Q50.LEAF_COUNT &DYN_RANGE &TREEBRANCH_count by $tab into $Q50_record
        Write 1 From $Q50_record
FROM PSTREEDEFN Q50
WHERE Q50.EFF_STATUS = 'A'
  AND Q50.EFFDT = (SELECT MAX(EFFDT) FROM PSTREEDEFN
                        WHERE SETID         = Q50.SETID
                          AND SETCNTRLVALUE = Q50.SETCNTRLVALUE
                          AND TREE_NAME     = Q50.TREE_NAME
                          AND EFFDT        <= $AsOfToday)
        End-Select
        display 'done'
End-Procedure

Begin-Procedure Q50_Range ! See if PSTREELEAF has DYNAMIC_RANGE = 'Y'
        Begin-Select
'Y'             &DYN_RANGE
FROM PS_INSTALLATION
WHERE EXISTS (SELECT 'X' FROM PSTREELEAF
                        WHERE SETID         = &Q50.SETID
                          AND SETCNTRLVALUE = &Q50.SETCNTRLVALUE
                          AND TREE_NAME     = &Q50.TREE_NAME
                          AND EFFDT         = &Q50.EFFDT
                          AND DYNAMIC_RANGE = 'Y')
        End-Select
End-Procedure

Begin-Procedure Q50_Branch ! Count rows in PSTREEBRANCH
        Begin-Select
COUNT(*)        &TREEBRANCH_count
FROM PSTREEBRANCH
WHERE SETID         = &Q50.SETID
  AND SETCNTRLVALUE = &Q50.SETCNTRLVALUE
  AND TREE_NAME     = &Q50.TREE_NAME
  AND EFFDT         = &Q50.EFFDT
        End-Select
End-Procedure

Begin-Procedure Q51 ! How many trees are defined?  (including non effective rows)
        display 'Processing Q51.' noline
        Begin-Select
COUNT(*)        &TREES
FROM PSTREEDEFN
        End-Select
        Write 1 From '100' $tab 'Q51' $tab '# Trees' $tab &TREES $tab
        display 'done'
End-Procedure

Begin-Procedure Q52 ! How many tree structures are defined?
        display 'Processing Q52.' noline
        Begin-Select
COUNT(*)        &STRUCTS
FROM PSTREESTRCT
        End-Select
        Write 1 From '100' $tab 'Q52' $tab '# Tree Structures' $tab &STRUCTS $tab
        display 'done'
End-Procedure

Begin-Procedure Q53 ! Tree structures WHERE LEVEL_RECNAME <> 'TREE_LEVEL_TBL'
        let $query = 'Q53'
        let $where = 'WHERE LEVEL_RECNAME <> ''TREE_LEVEL_TBL'''
        do PSTREESTRCT
End-Procedure

Begin-Procedure Q54 ! Tree structures WHERE NODE_RECNAME = 'TREE_NODE_TBL'
        let $query = 'Q54'
        let $where = 'WHERE NODE_RECNAME = ''TREE_NODE_TBL'''
        do PSTREESTRCT
End-Procedure

Begin-Procedure PSTREESTRCT
        display 'Processing ' noline
        display $query        noline
        display '.'           noline
        Begin-Select
TREE_STRCT_ID
LEVEL_RECNAME
NODE_RECNAME
TREE_STRCT_TYPE
        let $strct = rtrim(&TREE_STRCT_ID  ,' ')
        let $l_rec = rtrim(&LEVEL_RECNAME  ,' ')
        let $n_rec = rtrim(&NODE_RECNAME   ,' ')
        let $stype = rtrim(&TREE_STRCT_TYPE,' ')
        string '501' $query $strct $l_rec $n_rec $stype by $tab into $tree_strct_record
        Write 1 From $tree_strct_record
FROM PSTREESTRCT
[$where]
        End-Select
        display 'done'
End-Procedure

Begin-Procedure PRCSDEFN !
        Begin-Select
Q22_25.PRCSTYPE
Q22_25.PRCSNAME
Q22_25.VERSION
        let $name = rtrim(&Q22_25.PRCSNAME,' ')
        let $pt   = rtrim(&Q22_25.PRCSTYPE, ' ')
        Evaluate $pt
        When = 'Application Engine'
                Write 1 From '100' $tab 'Q22' $tab $name $tab &Q22_25.VERSION $tab
        When = 'SQR Process'
                Write 1 From '100' $tab 'Q23' $tab $name $tab &Q22_25.VERSION $tab
        When = 'COBOL SQL'
                Write 1 From '100' $tab 'Q24' $tab $name $tab &Q22_25.VERSION $tab
        When = 'Crystal'
                Write 1 From '100' $tab 'Q25' $tab $name $tab &Q22_25.VERSION $tab
        End-Evaluate
FROM PS_PRCSDEFN Q22_25
WHERE Q22_25.LASTUPDOPRID <> 'PPLSOFT'
        End-Select
End-Procedure

Begin-Procedure RECNAME_Cursor
        let #rex = 0
        Begin-Select ! Loops = 12 ! this is to limit # of rows for testing purposes
REC.RECNAME
REC.RECTYPE
REC.OBJECTOWNERID
REC.SQLTABLENAME

        let #rex = #rex + 1
        if mod(#rex,1000) = 0
                display '.' noline
        end-if

        let $name  = rtrim(&REC.RECNAME,' ')
        let $owner = rtrim(&REC.OBJECTOWNERID,' ')
        Evaluate $query
        When = 'Q02'
                do Get_Totals
                if isblank($msg)
                        let #CNT = &CNT
                else
                        let #CNT = 0 ! table is not built, set to zero
                end-if
                let $CNT = to_char(#CNT)
                ! new idea below Write 1 From '100' $tab 'Q02' $tab $name $tab $CNT $tab $owner
                string '100' 'Q02' $name $CNT $owner by $tab into $Q02_record
                Write 1 From $Q02_record
        When = 'Q13'
                Write 1 From '100' $tab 'Q13' $tab $name $tab &REC.RECTYPE $tab $owner
                do Q14
        End-Evaluate
FROM PSRECDEFN REC
WHERE [$where]
        End-Select
End-Procedure

Begin-Procedure Get_Totals
        let $table = &REC.SQLTABLENAME
        if isblank($table)
                let $rec = &REC.RECNAME
                string 'PS' $rec by '_' into $table
        end-if
        let $msg = '' ! initialize to OK
        Begin-Select    on-error=Dropped
COUNT(*)        &CNT
FROM [$table]
        End-Select
End-Procedure

Begin-Procedure Dropped
        let $msg = 'table has been dropped from database'
End-Procedure

Begin-Procedure PSPNLGRPDEFN_Cursor


        If $toolsrel <= '8.4'
                let $extra_field = 'PGDEFN.MARKET'
        else
                let $extra_field = 'PGDEFN.OBJECTOWNERID'
        end-if


        Begin-Select ! Loops = 12 ! this is to limit # of rows for testing purposes
PGDEFN.PNLGRPNAME
PGDEFN.MARKET
PGDEFN.ACTIONS
[$extra_field]          &PSPNLGRPDEFN_FLD = TEXT


        if $toolsrel <= '8.4'
                let $owner = ''
        else
                let $owner = rtrim(&PSPNLGRPDEFN_FLD,' ')
        end-if


        let $name  = rtrim(&PGDEFN.PNLGRPNAME,' ') || '.' || rtrim(&PGDEFN.MARKET,' ')
        Write 1 From '100' $tab 'Q16' $tab $name $tab &PGDEFN.ACTIONS $tab $owner

FROM PSPNLGRPDEFN PGDEFN
WHERE [$where]
        End-Select
End-Procedure

!**********************************************************************
begin-procedure InitPeopleTools
!**********************************************************************
    move 'CUSTINFO'                     to $ReportID
    move 'Customer Intelligence SQR <>' to $ReportTitle
    move 'CUSTINFO.SQR'                 to $sqr-program
    show {Stars}
    show $ReportID ': ' $ReportTitle
    #ifdef PTVer
        show 'PeopleTools ' {PTVer}
    #end-if
    do DisplayRunInformation
end-procedure

!**********************************************************************
begin-procedure DisplayRunInformation
!**********************************************************************
    show {Dashes}
    show 'SQR Program: ' $sqr-program
    show 'Platform   : ' $sqr-platform

    let $Database = 'UNKNOWN'
    #ifdef DB2ALL
        #ifdef DB2400
            #ifdef NT
                let $Database = 'ODBC to DB2/400'
            #else
                let $Database = 'DB2/400'
            #end-if
        #else
            #ifdef DB2UNIX
                #ifdef NT
                    let $Database = 'ODBC to DB2/UNIX'
                #else
                    let $Database = 'DB2/UNIX'
                #end-if
            #else
                #ifdef NT
                    let $Database = 'ODBC to DB2/390'
                #else
                    let $Database = 'DB2/390'
                #end-if
            #end-if
        #end-if
    #end-if

    #ifdef INFORMIX
        let $Database = 'Informix'
    #end-if
    #ifdef ORACLE
        let $Database = 'Oracle'
    #end-if
    #ifdef MICROSOFT
        let $Database = 'Microsoft SQL Server'
    #end-if
    #ifdef SYBASE
        let $Database = 'Sybase'
    #end-if

    show 'Database   : ' $Database
    show 'Locale     : ' $sqr-locale
    show 'SQR Version: ' $sqr-ver
end-procedure

begin-procedure SQL-Error
    show '  Error --  SQL Status = ' #sql-status edit 99999
    show '  SQL Error  = ' $sql-error
end-procedure

!**********************************************************************

#include 'datetime.sqc'
#include 'curdttim.sqc'

!**********************************************************************