PC_PROJ_FIN_VW3

(SQL View)
 Index Back

Project Financials

Project Financials

SELECT PC_PROJ_FIN.OPRID , PC_PROJ_FIN.BUSINESS_UNIT , PC_PROJ_FIN.PROJECT_ID , PC_PROJ_FIN.DESCR , PC_PROJ_FIN.PROJECT_MANAGER , PC_PROJ_FIN.FULL_NAME , PC_PROJ_FIN.EMAIL , PC_PROJ_FIN.PROJECT_TYPE , PC_PROJ_FIN.CURRENCY_CD , SUM(PC_PROJ_FIN.PRE_ENCUMBERED_AMOUNT) , SUM(PC_PROJ_FIN.ENCUMBERED_AMOUNT) , SUM(PC_PROJ_FIN.BUDGET_AMOUNT) , SUM(PC_PROJ_FIN.ACTUAL_AMOUNT) , SUM(PC_PROJ_FIN.BUD_REV_AMT) , SUM(PC_PROJ_FIN.PC_REVENUE_AMOUNT) , SUM(PC_PROJ_FIN.PC_ANLTC_ACT_COST) , SUM(PC_PROJ_FIN.PC_ANLTC_ACT_REV) , SUM(PC_PROJ_FIN.BILLED_TO_DATE) , SUM(PC_PROJ_FIN.BUDGET_AMOUNT) - SUM(PC_PROJ_FIN.ACTUAL_AMOUNT) - SUM(PC_PROJ_FIN.PRE_ENCUMBERED_AMOUNT) - SUM(PC_PROJ_FIN.ENCUMBERED_AMOUNT) , SUM(PC_PROJ_FIN.COLLECTED_AMT) FROM ( SELECT B.OPRID AS OPRID , A.BUSINESS_UNIT AS BUSINESS_UNIT , A.PROJECT_ID AS PROJECT_ID , C.DESCR AS DESCR , D.PROJECT_MANAGER AS PROJECT_MANAGER , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME AS FULL_NAME , EMAIL.EMAIL_ADDR AS EMAIL , A.PROJECT_TYPE AS PROJECT_TYPE , A.CURRENCY_CD AS CURRENCY_CD , CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) ELSE 0 END AS PRE_ENCUMBERED_AMOUNT, CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV)) ELSE 0 END AS ENCUMBERED_AMOUNT, A.BUDGET_AMOUNT AS BUDGET_AMOUNT, (CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CCA)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CCA)) ELSE 0 END) AS ACTUAL_AMOUNT, A.BUD_REV_AMT AS BUD_REV_AMT, A.PC_REVENUE_AMOUNT AS PC_REVENUE_AMOUNT, A.PC_ANLTC_ACT_COST AS PC_ANLTC_ACT_COST, A.PC_ANLTC_ACT_REV AS PC_ANLTC_ACT_REV, A.BILLED_TO_DATE AS BILLED_TO_DATE, (A.BUDGET_AMOUNT - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CCA)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CCA)) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_REQ OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_RRV)) ELSE 0 END - CASE WHEN ( SELECT 'X' FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV)) = 'X' THEN ( SELECT (A.RESOURCE_AMOUNT * C1.SIGNED_VALUE) FROM PS_PROJ_AN_GRP_MAP C1 , PS_INSTALLATION_PC INS WHERE C.SET_OVERRIDE = C1.SETID AND C.AN_GRP_TOT_COSTS = C1.ANALYSIS_GROUP AND A.ANALYSIS_TYPE = C1.ANALYSIS_TYPE AND (A.ANALYSIS_TYPE = INS.AN_TYPE_PO_PO OR A.ANALYSIS_TYPE = INS.AN_TYPE_PO_CRV)) ELSE 0 END) AS REMAINING_COST_BUDGET, CASE WHEN A.SYSTEM_SOURCE = 'BAR' THEN A.RESOURCE_AMOUNT ELSE 0 END AS COLLECTED_AMT FROM PS_PROJ_RES_SUM A , PS_PC_OPRID_PRJACT B , PS_PROJECT C LEFT OUTER JOIN PS_PROJECT_MGR D ON C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.PROJECT_ID = D.PROJECT_ID LEFT OUTER JOIN PS_PERSONAL_DATA E ON E.EMPLID = D.PROJECT_MANAGER LEFT OUTER JOIN PS_EMAIL_ADDRESSES EMAIL ON E.EMPLID = EMAIL.EMPLID AND EMAIL.E_ADDR_TYPE = 'BUSN', PS_OPR_DEF_TBL_PC OPR WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PROJECT_ID = C.PROJECT_ID AND C.SUMMARY_PRJ = 'N' AND (D.EFFDT IS NULL OR D.EFFDT=( SELECT MAX(EFFDT) FROM PS_PROJECT_MGR A_ED WHERE A_ED.BUSINESS_UNIT = D.BUSINESS_UNIT AND A_ED.PROJECT_ID = D.PROJECT_ID AND A_ED.EFFDT <= %CurrentDateIn)) AND B.OPRID = OPR.OPRID AND B.BUSINESS_UNIT = OPR.BUSINESS_UNIT_WRK) PC_PROJ_FIN GROUP BY PC_PROJ_FIN.OPRID, PC_PROJ_FIN.BUSINESS_UNIT, PC_PROJ_FIN.PROJECT_ID, PC_PROJ_FIN.DESCR, PC_PROJ_FIN.PROJECT_MANAGER, PC_PROJ_FIN.FULL_NAME, PC_PROJ_FIN.EMAIL, PC_PROJ_FIN.PROJECT_TYPE, PC_PROJ_FIN.CURRENCY_CD

  • Related Language Record: PC_PROJ_FIN_LW3

    • # 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 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    6 NAME Character(50) VARCHAR2(50) NOT NULL Name
    7 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    8 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    10 PRE_ENCUMBERED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Pre Encumbered Amount
    11 ENCUMBERED_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Encumbered Amount
    12 BUDGET_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Projects Budget Amount
    13 ACTUAL_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Invoice Amount
    14 BUD_REV_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Budget or Revenue Amount
    15 PC_REVENUE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Revenue
    16 PC_ANLTC_ACT_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Profitability Actual Cost
    17 PC_ANLTC_ACT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Profitability Actual Revenue
    18 BILLED_TO_DATE Signed Number(28,3) DECIMAL(26,3) NOT NULL Billed To Date
    19 REMAINING_BUDGET Signed Number(28,3) DECIMAL(26,3) NOT NULL Remaining Budget
    20 COLLECTED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Collected Amount