SP_PRJCHC_CLSVW

(SQL View)
 Index Back

Change Request Project Prompt

This view is used as the project prompt record for Change Requests. It only returns those projects which have change control turned on, and the change control template indicates change request is required.

SELECT CD.CLASSID OPRCLASS , PROJ.BUSINESS_UNIT , PROJ.PROJECT_ID , PROJ.DESCR ,PROJ.PC_CHC_TEMPLATE FROM PS_PROJECT PROJ , PSCLASSDEFN CD , PS_INSTALLATION_FS INS WHERE PROJ.SUMMARY_PRJ = 'N' AND PROJ.EFF_STATUS = 'A' AND (( INS.PROJECT_SECURITY = 'Y' AND INS.PROJ_SECURITY_TYPE = 'L' AND EXISTS ( SELECT 'X' FROM PS_SEC_PROJLST_CLS SPRC , PS_PROJ_ROLE_SECVW PROL1 , PS_SET_CNTRL_REC SCR1 WHERE SPRC.OPRCLASS = CD.CLASSID AND SPRC.BUSINESS_UNIT = PROJ.BUSINESS_UNIT AND SPRC.PROJECT_ID = PROJ.PROJECT_ID AND SCR1.SETCNTRLVALUE = PROJ.BUSINESS_UNIT AND SCR1.RECNAME = 'PROJ_ROLE' AND PROL1.SETID = SCR1.SETID AND PROL1.PROJ_ROLE = SPRC.PROJ_ROLE AND PROL1.PROJ_ACCESS_CD <> 'N' )) OR (INS.PROJECT_SECURITY = 'Y' AND INS.PROJ_SECURITY_TYPE = 'T' AND EXISTS ( SELECT 'X' FROM PS_SEC_PROJ_CLS SPC1 , PSTREENODE TR1 WHERE SPC1.OPRCLASS = CD.CLASSID AND SPC1.TREE_BU = PROJ.BUSINESS_UNIT AND SPC1.TREE_EFFDT = ( SELECT MAX(TREE_EFFDT) FROM PS_SEC_PROJ_CLS SPC2 WHERE SPC2.TREE_BU = SPC1.TREE_BU AND SPC2.TREE_NAME = SPC1.TREE_NAME AND SPC2.TREE_EFFDT <= %CurrentDateIn ) AND TR1.SETCNTRLVALUE = SPC1.TREE_BU AND TR1.TREE_NAME = SPC1.TREE_NAME AND TR1.EFFDT = SPC1.TREE_EFFDT AND TR1.TREE_NODE = PROJ.PROJECT_ID AND EXISTS ( SELECT 'X' FROM PS_SEC_PROJ_CLS SPC3 , PSTREENODE TR2 , PS_PROJ_ROLE_SECVW PROL2 , PS_SET_CNTRL_REC SCR2 WHERE SPC3.OPRCLASS = SPC1.OPRCLASS AND SPC3.TREE_BU = SPC1.TREE_BU AND SPC3.TREE_NAME = SPC1.TREE_NAME AND SPC3.TREE_EFFDT = SPC1.TREE_EFFDT AND SCR2.SETCNTRLVALUE = SPC1.TREE_BU AND SCR2.RECNAME = 'PROJ_ROLE' AND PROL2.SETID = SCR2.SETID AND PROL2.PROJ_ROLE = SPC3.PROJ_ROLE AND PROL2.PROJ_ACCESS_CD <> 'N' AND TR2.SETCNTRLVALUE = SPC3.TREE_BU AND TR2.TREE_NAME = SPC3.TREE_NAME AND TR2.EFFDT = SPC3.TREE_EFFDT AND TR2.TREE_NODE = SPC3.PROJECT_ID AND TR1.TREE_NODE_NUM BETWEEN TR2.TREE_NODE_NUM AND TR2.TREE_NODE_NUM_END AND NOT EXISTS ( SELECT 'X' FROM PS_SEC_PROJ_CLS SPC4 , PSTREENODE TR3 , PS_PROJ_ROLE_SECVW PROL3 , PS_SET_CNTRL_REC SCR3 WHERE SPC4.OPRCLASS = SPC1.OPRCLASS AND SPC4.TREE_BU = SPC1.TREE_BU AND SPC4.TREE_NAME = SPC1.TREE_NAME AND SPC4.TREE_EFFDT = SPC1.TREE_EFFDT AND SCR3.SETCNTRLVALUE = SPC1.TREE_BU AND SCR3.RECNAME = 'PROJ_ROLE' AND PROL3.SETID = SCR3.SETID AND PROL3.PROJ_ROLE = SPC4.PROJ_ROLE AND PROL3.PROJ_ACCESS_CD = 'N' AND TR3.SETCNTRLVALUE = SPC4.TREE_BU AND TR3.TREE_NAME = SPC4.TREE_NAME AND TR3.EFFDT = SPC4.TREE_EFFDT AND TR3.TREE_NODE = SPC4.PROJECT_ID AND TR3.TREE_NODE_NUM >= TR2.TREE_NODE_NUM AND TR3.TREE_NODE_NUM_END <= TR2.TREE_NODE_NUM_END AND TR1.TREE_NODE_NUM BETWEEN TR3.TREE_NODE_NUM AND TR3.TREE_NODE_NUM_END )) )))

  • Related Language Record: PGM_CHC_SRCH_LG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_PC.BUSINESS_UNIT

    Prompt Table: SP_BU_PC_NONVW

    3 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 PC_CHC_TEMPLATE Character(30) VARCHAR2(30) NOT NULL Change Control Template