SP_PRJCHC_OPRVW

(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 OD.OPRID , PROJ.BUSINESS_UNIT , PROJ.PROJECT_ID , PROJ.DESCR ,PROJ.PC_CHC_TEMPLATE FROM PS_PROJECT PROJ , PSOPRDEFN OD , 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_OPR SPRO , PS_PROJ_ROLE_SECVW PROL1 , PS_SET_CNTRL_REC SCR1 WHERE SPRO.OPRID = OD.OPRID AND SPRO.BUSINESS_UNIT = PROJ.BUSINESS_UNIT AND SPRO.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 = SPRO.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_OPR SPO1 , PSTREENODE TR1 WHERE SPO1.OPRID = OD.OPRID AND SPO1.TREE_BU = PROJ.BUSINESS_UNIT AND SPO1.TREE_EFFDT = ( SELECT MAX(TREE_EFFDT) FROM PS_SEC_PROJ_OPR SPO2 WHERE SPO2.TREE_BU = SPO1.TREE_BU AND SPO2.TREE_NAME = SPO1.TREE_NAME AND SPO2.TREE_EFFDT <= %CurrentDateIn ) AND TR1.SETCNTRLVALUE = SPO1.TREE_BU AND TR1.TREE_NAME = SPO1.TREE_NAME AND TR1.EFFDT = SPO1.TREE_EFFDT AND TR1.TREE_NODE = PROJ.PROJECT_ID AND EXISTS ( SELECT 'X' FROM PS_SEC_PROJ_OPR SPO3 , PSTREENODE TR2 , PS_PROJ_ROLE_SECVW PROL2 , PS_SET_CNTRL_REC SCR2 WHERE SPO3.OPRID = SPO1.OPRID AND SPO3.TREE_BU = SPO1.TREE_BU AND SPO3.TREE_NAME = SPO1.TREE_NAME AND SPO3.TREE_EFFDT = SPO1.TREE_EFFDT AND SCR2.SETCNTRLVALUE = SPO1.TREE_BU AND SCR2.RECNAME = 'PROJ_ROLE' AND PROL2.SETID = SCR2.SETID AND PROL2.PROJ_ROLE = SPO3.PROJ_ROLE AND PROL2.PROJ_ACCESS_CD <> 'N' AND TR2.SETCNTRLVALUE = SPO3.TREE_BU AND TR2.TREE_NAME = SPO3.TREE_NAME AND TR2.EFFDT = SPO3.TREE_EFFDT AND TR2.TREE_NODE = SPO3.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_OPR SPO4 , PSTREENODE TR3 , PS_PROJ_ROLE_SECVW PROL3 , PS_SET_CNTRL_REC SCR3 WHERE SPO4.OPRID = SPO1.OPRID AND SPO4.TREE_BU = SPO1.TREE_BU AND SPO4.TREE_NAME = SPO1.TREE_NAME AND SPO4.TREE_EFFDT = SPO1.TREE_EFFDT AND SCR3.SETCNTRLVALUE = SPO1.TREE_BU AND SCR3.RECNAME = 'PROJ_ROLE' AND PROL3.SETID = SCR3.SETID AND PROL3.PROJ_ROLE = SPO4.PROJ_ROLE AND PROL3.PROJ_ACCESS_CD = 'N' AND TR3.SETCNTRLVALUE = SPO4.TREE_BU AND TR3.TREE_NAME = SPO4.TREE_NAME AND TR3.EFFDT = SPO4.TREE_EFFDT AND TR3.TREE_NODE = SPO4.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 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    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