GM_BUD_PDTL_VW9

(SQL View)
 Index Back

Personnel Detail View

This view delivers pre-award budget data for personnel detail lines. Only budget headers that have 'Include in Proposal' turned ON are selected.

SELECT B.BUSINESS_UNIT , B.PROPOSAL_ID , B.VERSION_ID , B.SUB_PROP_NBR , B.BUDGET_ID , B.BUDGET_PERIOD_GM , B.BUDGET_LN , B.SEQ_NUM , A.BUDGET_CATEGORY , B.ROW_NUMBER , C.NAME , C.PROJECT_ROLE , B.APPT_TYPE_CALENDAR , B.APPT_TYPE_ACADEMIC , B.APPT_TYPE_SUMMER , B.APPT_TYPE_MONTHS , B.DAYS_ELAPSED , B.PERCENTAGE , B.ANNUAL_SALARY_GM , (B.BUDGET_AMT - B.FRINGE_AMT) , B.FRINGE_AMT , B.BUDGET_AMT , %Round(%DecMult((B.BUDGET_AMT - B.FRINGE_AMT), %DecDiv(A.SPONSOR_AMT, A.BUDGET_AMT)),2) , %Round(%DecMult(B.FRINGE_AMT, %DecDiv(A.SPONSOR_AMT, A.BUDGET_AMT)),2) , %Round(%DecMult(B.BUDGET_AMT, %DecDiv(A.SPONSOR_AMT, A.BUDGET_AMT)),2) , B.BEGIN_DT , B.END_DT , E.NAME_PREFIX , E.NAME_SUFFIX , E.FIRST_NAME , E.LAST_NAME , E.MIDDLE_NAME , E.EMPLID , C.PROF_ROLE_TYPE FROM PS_GM_BUD_LSUM_VW9 A , PS_GM_BUD_LINE_DTL B , PS_GM_PROP_PROF C , PS_GM_BUD_CATEGORY D , PS_PERS_DATA_EFFDT E WHERE D.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = A.BUSINESS_UNIT AND RECNAME = 'GM_BUD_CATEGORY') AND D.BUDGET_CATEGORY = A.BUDGET_CATEGORY AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GM_BUD_CATEGORY WHERE SETID = D.SETID AND BUDGET_CATEGORY = D.BUDGET_CATEGORY AND EFFDT <= %CurrentDateIn) AND D.EFF_STATUS = 'A' AND D.BUD_CAT_TYPE = 'PERS' AND A.BUDGET_AMT <> 0 AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.PROPOSAL_ID = A.PROPOSAL_ID AND B.VERSION_ID = A.VERSION_ID AND B.SUB_PROP_NBR = A.SUB_PROP_NBR AND B.BUDGET_ID = A.BUDGET_ID AND B.BUDGET_PERIOD_GM = A.BUDGET_PERIOD_GM AND B.BUDGET_LN = A.BUDGET_LN 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.ROW_NUMBER = B.ROW_NUMBER AND C.EMPLID=E.EMPLID AND C.GM_RESEARCH_FLG = 'Y' AND E.EFFDT=( SELECT MAX(F.EFFDT) FROM PS_PERS_DATA_EFFDT F WHERE E.EMPLID=F.EMPLID AND F.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PROPOSAL_ID Character(25) VARCHAR2(25) NOT NULL Proposal ID
3 VERSION_ID Character(7) VARCHAR2(7) NOT NULL Version ID
4 SUB_PROP_NBR Character(15) VARCHAR2(15) NOT NULL SUB_PROP_NBR
5 BUDGET_ID Character(15) VARCHAR2(15) NOT NULL BUDGET_ID
6 BUDGET_PERIOD_GM Number(3,0) SMALLINT NOT NULL BUDGET_PERIOD_GM
7 BUDGET_LN Number(6,0) INTEGER NOT NULL BUDGET_LN
8 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence
9 BUDGET_CATEGORY Character(15) VARCHAR2(15) NOT NULL BUDGET_CATEGORY
10 ROW_NUMBER Number(3,0) SMALLINT NOT NULL Row_Number
11 NAME Character(50) VARCHAR2(50) NOT NULL Name
12 PROJECT_ROLE Character(30) VARCHAR2(30) NOT NULL Project Role
13 APPT_TYPE_CALENDAR Character(1) VARCHAR2(1) NOT NULL APPT_TYPE_CALENDAR
14 APPT_TYPE_ACADEMIC Character(1) VARCHAR2(1) NOT NULL APPT_TYPE_ACADEMIC
15 APPT_TYPE_SUMMER Character(1) VARCHAR2(1) NOT NULL APPT_TYPE_SUMMER
16 APPT_TYPE_MONTHS Number(3,0) SMALLINT NOT NULL Months for Appointment Type
17 DAYS_ELAPSED Number(4,0) SMALLINT NOT NULL Days Elapsed
18 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
19 ANNUAL_SALARY_GM Number(27,3) DECIMAL(26,3) NOT NULL NNUAL_SALARY_GM
20 BUDGET_SALARY Signed Number(28,3) DECIMAL(26,3) NOT NULL BUDGET_SALARY
21 FRINGE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL FRINGE_AMT
22 BUDGET_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL BUDGET_AMT
23 SPONSOR_SALARY_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Calculated Budget Salary
24 SPONSOR_FRINGE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sponsor Fringe Amount
25 SPONSOR_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL SPONSOR_AMT
26 BEGIN_DT Date(10) DATE Begin Date
27 END_DT Date(10) DATE End Date
28 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
1=Mr.
2=Mrs.
3=Ms.
29 NAME_SUFFIX Character(15) VARCHAR2(15) NOT NULL Name Suffix
30 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
31 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
32 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL Middle Name
33 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
34 PROF_ROLE_TYPE Character(4) VARCHAR2(4) NOT NULL Role Type