GM_PROF_EFF_VW

(SQL View)
 Index Back

Proposal Professional Effort

This view returns the name, role, and weighted percentage of effort for the professionals associated with each proposal/project. It supports the proposal submission process via EDI or special forms.

SELECT D.BUSINESS_UNIT , D.PROPOSAL_ID , D.VERSION_ID , D.SUB_PROP_NBR , D.ROW_NUMBER , MIN(P1.NAME) , MIN(P1.PROF_ROLE_TYPE) , %Round(%DecDiv(sum(%decmult(D.PERCENTAGE, D.DAYS_ELAPSED)), sum(D.DAYS_ELAPSED)),2) FROM PS_GM_PROP_PROF P1 , PS_GM_BUD_LSUM_VW9 C , PS_GM_BUD_LINE_DTL D WHERE D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.PROPOSAL_ID = C.PROPOSAL_ID AND D.VERSION_ID = C.VERSION_ID AND D.SUB_PROP_NBR = C.SUB_PROP_NBR AND D.BUDGET_ID = C.BUDGET_ID AND D.BUDGET_PERIOD_GM = C.BUDGET_PERIOD_GM AND D.BUDGET_LN = C.BUDGET_LN AND P1.PROPOSAL_ID = D.PROPOSAL_ID AND P1.VERSION_ID= D.VERSION_ID AND P1.SUB_PROP_NBR = D.SUB_PROP_NBR AND P1.ROW_NUMBER = D.ROW_NUMBER GROUP BY D.BUSINESS_UNIT, D.PROPOSAL_ID, D.VERSION_ID, D.SUB_PROP_NBR, D.ROW_NUMBER HAVING SUM(D.DAYS_ELAPSED) > 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_PROP Character(5) VARCHAR2(5) NOT NULL Proposal Business Unit
2 PROPOSAL_ID_TO Character(25) VARCHAR2(25) NOT NULL Proposal ID
3 VERSION_ID_TO Character(7) VARCHAR2(7) NOT NULL Version ID To
4 SUB_PROP_NBR_TO Character(15) VARCHAR2(15) NOT NULL Sub Proposal ID To
5 ROW_NUMBER_TO Number(3,0) SMALLINT NOT NULL Personnel/Professional Number
6 NAME Character(50) VARCHAR2(50) NOT NULL Name
7 PROF_ROLE_TYPE Character(4) VARCHAR2(4) NOT NULL Role Type

Prompt Table: GM_PROF_ROLE_TY

8 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage