GM_PROP_YJ_SRCH

(SQL View)
 Index Back

Proposal - gm_awd_setup search

Security check for deptid and emplid. Submitted proposals. Proposals that have not been awarded.

SELECT DISTINCT D.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , A.DESCR20A , A.EMPLID , A.CUST_ID , A.PROPOSAL_TYPE , A.PROPOSAL_STATUS_GR , B.DEPTID , A.TITLE56 , A.SUBMIT_STATUS , A.PROPOSAL_FLG , A.CONTRACT_NUM , A.PPSL_TO_CNTRCT_FLG , A.GGOV_FLG , A.APPLICATION_STATUS FROM PS_GM_PROPOSAL A , PS_GM_PROP_PROJ B , 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.SUBMIT_STATUS = 'SUBM' AND A.PROPOSAL_FLG = 'G' AND A.TMPLT_FLG <> 'Y' AND A.PPSL_TO_CNTRCT_FLG IN ('N','P') AND A.PROPOSAL_STATUS_GR IN ('PEN','ACP','AWD','AWP','DRF','PEF','APR', 'SUBM') AND A.CONTINUATION_FLAG <> 'Y' AND (D.EMPLID = A.EMPLID OR EXISTS ( SELECT 'X1' FROM PS_GM_PROP_PROF F WHERE F.EMPLID = D.EMPLID AND F.BUSINESS_UNIT = B.BUSINESS_UNIT AND F.PROPOSAL_ID = B.PROPOSAL_ID AND F.VERSION_ID = B.VERSION_ID AND F.SUB_PROP_NBR = B.SUB_PROP_NBR AND F.PROF_ROLE_TYPE IN ('PI','CPI','AUTH','PAA')) 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 = B.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 B.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))) AND NOT EXISTS ( SELECT 'X' FROM PS_GM_PROPOSAL C WHERE C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.PROPOSAL_ID = A.PROPOSAL_ID AND C.VERSION_ID <> A.VERSION_ID AND C.PPSL_TO_CNTRCT_FLG IN ('D', 'P'))

  • Related Language Record: GM_PROP_YJ_SR_L

    • # 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 DESCR20A Character(20) VARCHAR2(20) NOT NULL Short Description
    6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: GM_ELIG_PI_VW

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

    Prompt Table: GM_CUSTOMER_VW

    8 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
    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
    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_FLG Character(1) VARCHAR2(1) NOT NULL Proposal Checkbox
    G=Grants Proposal
    I=Internal Proposal
    S=Solicitation Proposal
    14 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    15 PPSL_TO_CNTRCT_FLG Character(1) VARCHAR2(1) NOT NULL Proposal To Contract
    D=Contract Generated
    N=Not Generated
    P=Pre-Sales Spending
    16 GGOV_FLG Character(1) VARCHAR2(1) NOT NULL GGOV_FLG

    Y/N Table Edit

    17 APPLICATION_STATUS Character(3) VARCHAR2(3) NOT NULL Grants.gov processing status
    AGT=Agency Tracking Number Assign
    PRC=Processing
    RAG=Received by Agency
    REC=Received
    REJ=Rejected with Errors
    VAL=Validated