GM_PRESPEND_VW

(SQL View)
 Index Back

Pre-Award Spending

Pre-Award Spending View

SELECT A.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , %DatePart(A.SUBMIT_DTTM) , A.EMPLID , A.NAME , A.CUST_ID , A.NAME1 , A.TITLE56 , B.PROJECT_ID , C.ACTIVITY_ID , E.BUSINESS_UNIT_PC , D.ANALYSIS_TYPE , SUM(D.RESOURCE_AMOUNT) , D.CURRENCY_CD , CASE WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 0 AND 30 THEN '0-30' WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 31 AND 60 THEN '31-60' WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 61 AND 90 THEN '61-90' ELSE '>90' END FROM PS_GM_PREF_PROP_VW A , PS_GM_PROP_PROJ B , PS_GM_BUD_HDR C , PS_PROJ_RESOURCE D , PS_GM_BU_AWD_SETUP E , PS_PROJ_ANTYPE_TBL F , PS_SET_CNTRL_REC G , PS_PROJECT PRJ WHERE E.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROPOSAL_ID = B.PROPOSAL_ID AND A.VERSION_ID = B.VERSION_ID AND A.PROPOSAL_STATUS_GR ='SUBM' AND D.TRANS_DT<=%CurrentDateIn AND A.PPSL_TO_CNTRCT_FLG = 'P' AND B.PPSL_TO_PRJCT_FLG ='P' AND B.PROJECT_ID <> ' ' AND B.BUSINESS_UNIT = PRJ.BUSINESS_UNIT AND B.PROJECT_ID = PRJ.PROJECT_ID AND PRJ.EFF_STATUS <>'I' AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.PROPOSAL_ID = B.PROPOSAL_ID AND C.VERSION_ID = B.VERSION_ID AND C.SUB_PROP_NBR = B.SUB_PROP_NBR AND C.PPSL_TO_ACTVTY_FLG = 'P' AND D.BUSINESS_UNIT = E.BUSINESS_UNIT_PC AND D.PROJECT_ID = B.PROJECT_ID AND D.ACTIVITY_ID = C.ACTIVITY_ID AND G.SETCNTRLVALUE = E.BUSINESS_UNIT_PC AND G.RECNAME = 'PROJ_ANTYPE_TBL' AND F.SETID = G.SETID AND F.ANALYSIS_TYPE = D.ANALYSIS_TYPE AND %EffdtCheck(PROJ_ANTYPE_TBL F1, F, %CurrentDateIn) AND F.EFF_STATUS ='A' GROUP BY A.OPRID , A.BUSINESS_UNIT , A.PROPOSAL_ID , A.VERSION_ID , A.SUBMIT_DTTM , A.EMPLID , A.NAME , A.CUST_ID , A.NAME1 , A.TITLE56 , B.PROJECT_ID , C.ACTIVITY_ID , E.BUSINESS_UNIT_PC , D.ANALYSIS_TYPE , D.CURRENCY_CD ,CASE WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 0 AND 30 THEN '0-30' WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 31 AND 60 THEN '31-60' WHEN %DateDiff(D.TRANS_DT,%CurrentDateIn) BETWEEN 61 AND 90 THEN '61-90' ELSE '>90' END

  • Related Language Record: GM_PREFPROP_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
    3 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
    4 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
    5 SUBMIT_DT Date(10) DATE Date Submitted
    6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
    9 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    10 TITLE56 Character(56) VARCHAR2(56) NOT NULL TITLE56
    11 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    12 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
    13 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
    14 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
    15 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
    16 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    17 TRANS_AGE Character(5) VARCHAR2(5) NOT NULL Transaction Age