GM_PROP_COM_SRH(SQL View) |
Index Back |
---|---|
Proposal app Search RecordUsed as secured search record for Proposal approvals |
SELECT DISTINCT D.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID ,A.SUB_PROP_NBR ,A.COMPONENT_ID_GR ,A.STATUS_CMPNT_GR , A.EMPLID , A.PROPOSAL_STATUS_GR , A.DEPTID , A.TITLE56 FROM PS_GM_PROP_AF_VW A ,PS_GM_PROP_AF_XREF B , PS_EOAW_STEPINST S , PS_EOAW_USERINST U , PSOPRDEFN D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROPOSAL_ID = B.PROPOSAL_ID AND A.VERSION_ID = B.VERSION_ID AND A.SUB_PROP_NBR=B.SUB_PROP_NBR AND A.COMPONENT_ID_GR=B.COMPONENT_ID_GR AND B.EOAWTHREAD_STATUS NOT IN ('C','A') AND A.PROPOSAL_FLG = 'G' AND B.EOAWPRCS_ID = 'GMProposalComponent' AND S.EOAWTHREAD_ID = B.EOAWTHREAD_ID AND B.EOAWPRCS_ID = S.EOAWPRCS_ID AND B.EOAWDEFN_ID = S.EOAWDEFN_ID AND S.EOAWSTEP_STATUS = 'P' AND S.EOAWSTEP_INSTANCE = U.EOAWSTEP_INSTANCE AND( (D.EMPLID = A.EMPLID) OR EXISTS ( SELECT 'RU1' FROM PSROLEUSER H WHERE D.OPRID = H.ROLEUSER AND H.ROLENAME = 'Grants Super User') OR (D.OPRID = U.OPRID) OR EXISTS ( SELECT 'X1' FROM PSTREENODE E , PS_GM_SEC_DEPT_OPR S WHERE E.EFFDT = S.TREE_EFFDT AND D.OPRID = S.OPRID AND E.TREE_NAME = S.TREE_NAME AND E.TREE_NODE = A.DEPTID AND E.SETID = S.TREE_SETID AND E.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND E.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END)) OR EXISTS ( SELECT 'X1' FROM PSTREELEAF F , PS_GM_SEC_DEPT_OPR S WHERE F.EFFDT = S.TREE_EFFDT AND F.TREE_NAME = S.TREE_NAME AND D.OPRID = S.OPRID AND A.DEPTID BETWEEN F.RANGE_FROM AND F.RANGE_TO AND F.SETID = S.TREE_SETID AND F.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR S1 WHERE S1.OPRID = S.OPRID AND S1.ACCESS_CD = 'N' AND S1.TREE_SETID = S.TREE_SETID AND S1.TREE_NAME = S.TREE_NAME AND S1.TREE_EFFDT = S.TREE_EFFDT AND S1.TREE_NODE_NUM <> S.TREE_NODE_NUM AND F.TREE_NODE_NUM BETWEEN S1.TREE_NODE_NUM AND S1.TREE_NODE_NUM_END AND S1.TREE_NODE_NUM BETWEEN S.TREE_NODE_NUM AND S.TREE_NODE_NUM_END))) |
# | 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_CA.BUSINESS_UNIT Prompt Table: SP_BU_CA_NONVW |
3 | PROPOSAL_ID | Character(25) | VARCHAR2(25) NOT NULL |
Proposal ID
Prompt Table: GM_PROP_ID_SRCH |
4 | VERSION_ID | Character(7) | VARCHAR2(7) NOT NULL | Version ID |
5 | SUB_PROP_NBR | Character(15) | VARCHAR2(15) NOT NULL | SUB_PROP_NBR |
6 | COMPONENT_ID_GR | Character(18) | VARCHAR2(18) NOT NULL | Component ID |
7 | STATUS_CMPNT_GR | Character(3) | VARCHAR2(3) NOT NULL |
Status
APR=Approved ASG=Assigned DRF=Draft FSH=Finished IPG=In Progress NOA=Not Approved SNB=Send Back |
8 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: GM_ELIG_PI_VW |
9 | PROPOSAL_STATUS_GR | Character(4) | VARCHAR2(4) NOT NULL |
PROPOSAL_STATUS_GR
ACP=Accepted Award APR=Institution Approved AWD=Awarded AWP=Award Pending DEC=Declined by Sponsor DIS=Discontinued DRF=Draft NOT=Not Funded PEF=Pending Funding PEN=Pending Approval REF=Refused by Institution REJ=Rejected SUBM=Submitted WIT=Withdrawn |
10 | DEPTID2 | Character(10) | VARCHAR2(10) NOT NULL |
DEPTID2
Prompt Table: GM_DEPT_TBL_VW |
11 | TITLE56 | Character(56) | VARCHAR2(56) NOT NULL | TITLE56 |