GM_WC_PPSLMX_VW

(SQL View)
 Index Back

Manage Proposals

Used as secured view for Grants WorkCenter. This View will be used by the myworklink if the 'Version to Display' option in the filter criteria is 'Display Maximum Version' The view to display all versions is GM_WC_PROPSL_VW

SELECT DISTINCT OPR.OPRID , PSL.BUSINESS_UNIT , PSL.PROPOSAL_ID , PSL.VERSION_ID , PSL.DESCR20A , PSL.PROPOSAL_TYPE , PSL.EMPLID , PSL.CUST_ID , PSLP.DEPTID , PSL.TITLE56 , PSL.PPSL_TO_CNTRCT_FLG , PSL.SUBMIT_STATUS , PSL.PROPOSAL_STATUS_GR , PSL.REF_AWD_NUMBER , PSL.FED_AWD_ID_NUMBER , PSL.EMPLID2 , PSL.BEGIN_DT , PSL.PURPOSE FROM PS_GM_PROPOSAL PSL , PS_GM_PROP_PROJ PSLP , PSOPRDEFN OPR WHERE PSL.BUSINESS_UNIT = PSLP.BUSINESS_UNIT AND PSL.PROPOSAL_ID = PSLP.PROPOSAL_ID AND PSL.VERSION_ID = PSLP.VERSION_ID AND PSL.PROPOSAL_FLG = 'G' AND PSL.TMPLT_FLG = 'N' AND PSL.PPSL_TO_CNTRCT_FLG IN ('N', 'P') AND PSL.CONTINUATION_FLAG <> 'Y' AND (OPR.EMPLID = PSL.EMPLID OR EXISTS ( SELECT 'X1' FROM PS_GM_PROP_PROF PROF WHERE PROF.EMPLID = OPR.EMPLID AND PROF.BUSINESS_UNIT = PSLP.BUSINESS_UNIT AND PROF.PROPOSAL_ID = PSLP.PROPOSAL_ID AND PROF.VERSION_ID = PSLP.VERSION_ID AND PROF.SUB_PROP_NBR = PSLP.SUB_PROP_NBR AND PROF.PROF_ROLE_TYPE IN ('PI', 'CPI', 'AUTH', 'PAA') ) OR EXISTS ( SELECT 'X1' FROM PSTREENODE TRE , PS_GM_SEC_DEPT_OPR DEPT WHERE TRE.EFFDT = DEPT.TREE_EFFDT AND OPR.OPRID = DEPT.OPRID AND TRE.TREE_NAME = DEPT.TREE_NAME AND TRE.TREE_NODE = PSLP.DEPTID AND TRE.SETID = DEPT.TREE_SETID AND TRE.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END AND DEPT.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR DEPT1 WHERE DEPT1.OPRID = DEPT.OPRID AND DEPT1.ACCESS_CD = 'N' AND DEPT1.TREE_SETID = DEPT.TREE_SETID AND DEPT1.TREE_NAME = DEPT.TREE_NAME AND DEPT1.TREE_EFFDT = DEPT.TREE_EFFDT AND DEPT1.TREE_NODE_NUM <> DEPT.TREE_NODE_NUM AND TRE.TREE_NODE_NUM BETWEEN DEPT1.TREE_NODE_NUM AND DEPT1.TREE_NODE_NUM_END AND DEPT1.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END ) ) OR EXISTS ( SELECT 'X1' FROM PSTREELEAF LF , PS_GM_SEC_DEPT_OPR DEPT WHERE LF.EFFDT = DEPT.TREE_EFFDT AND LF.TREE_NAME = DEPT.TREE_NAME AND OPR.OPRID = DEPT.OPRID AND PSLP.DEPTID BETWEEN LF.RANGE_FROM AND LF.RANGE_TO AND LF.SETID = DEPT.TREE_SETID AND LF.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END AND DEPT.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X2' FROM PS_GM_SEC_DEPT_OPR DEPT1 WHERE DEPT1.OPRID = DEPT.OPRID AND DEPT1.ACCESS_CD = 'N' AND DEPT1.TREE_SETID = DEPT.TREE_SETID AND DEPT1.TREE_NAME = DEPT.TREE_NAME AND DEPT1.TREE_EFFDT = DEPT.TREE_EFFDT AND DEPT1.TREE_NODE_NUM <> DEPT.TREE_NODE_NUM AND LF.TREE_NODE_NUM BETWEEN DEPT1.TREE_NODE_NUM AND DEPT1.TREE_NODE_NUM_END AND DEPT1.TREE_NODE_NUM BETWEEN DEPT.TREE_NODE_NUM AND DEPT.TREE_NODE_NUM_END ) )) AND NOT EXISTS ( SELECT 'X' FROM PS_GM_PROPOSAL PSL1 WHERE PSL1.BUSINESS_UNIT = PSL.BUSINESS_UNIT AND PSL1.PROPOSAL_ID = PSL.PROPOSAL_ID AND PSL1.VERSION_ID <> PSL.VERSION_ID AND PSL1.PPSL_TO_CNTRCT_FLG IN ('D', 'P') ) AND PSL.VERSION_ID = ( SELECT MAX(PSL2.VERSION_ID) FROM PS_GM_PROPOSAL PSL2 WHERE PSL2.BUSINESS_UNIT = PSL.BUSINESS_UNIT AND PSL2.PROPOSAL_ID = PSL.PROPOSAL_ID )

  • Related Language Record: GM_WC_PSLMX_LVW

    • # 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

    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 DESCR20A Character(20) VARCHAR2(20) NOT NULL Short Description
    6 PROPOSAL_TYPE Character(3) VARCHAR2(3) NOT NULL PROPOSAL_TYPE
    AMD=Amendment
    CNT=Continuation
    CON=Competing Continuation
    INT=Internal
    MOD=Modification
    NEW=New
    NOC=No-Cost Extension
    NON=Non-Competing Continuation
    NRS=NRSA
    PRE=Pre-Proposal
    RCA=Research Career Award
    REN=Renewal
    RES=Resubmission
    REV=Revision
    SB1=SBIR I
    SB2=SBIR II
    ST1=STTR I
    ST2=STTR II
    SUP=Supplemental
    7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: GM_ELIG_PI_VW

    8 SPONSOR_ID Character(15) VARCHAR2(15) NOT NULL SPONSOR_ID

    Prompt Table: GM_CUSTOMER_VW

    9 DEPTID2 Character(10) VARCHAR2(10) NOT NULL DEPTID2

    Prompt Table: GM_DEPT_TBL_VW

    10 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
    11 PPSL_TO_CNTRCT_FLG Character(1) VARCHAR2(1) NOT NULL Proposal To Contract
    D=Contract Generated
    N=Not Generated
    P=Pre-Sales Spending
    12 SUBMIT_STATUS Character(4) VARCHAR2(4) NOT NULL Proposal Submission Status
    CMTD=03 - Committed
    CNCL=05 - Canceled
    DENY=04 - Denied
    DRAF=01 - Draft
    NSUB=Not Submitted
    RDY=02 - Ready
    SUBM=Submitted
    SUBR=Submitted With Error
    13 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
    14 REF_AWD_NUMBER Character(25) VARCHAR2(25) NOT NULL Reference Award Number
    15 FED_AWD_ID_NUMBER Character(50) VARCHAR2(50) NOT NULL Federal Award Identification Number for Grants use. Added in enhancement bug number 18492701.
    16 EMPLID2 Character(11) VARCHAR2(11) NOT NULL Employee ID
    17 BEGIN_DT Date(10) DATE Begin Date
    18 PURPOSE Character(5) VARCHAR2(5) NOT NULL Purpose