PC_BDPLAN_FL_VW

(SQL View)
 Index Back

Mass Budget Finalization View

Proejct Workcenter Mass Budget Finalization View

SELECT PC_BDPLAN.BUSINESS_UNIT , PC_BDPLAN.PROJECT_ID , PC_BDPLAN.PC_BUDGET_ID , PC_BDPLAN.BUDGET_PERIOD , PC_BDPLAN.PC_BUD_TYPE , PC_BDPLAN.PC_BUDID_PERIODID , PC_BDPLAN.DESCR , PC_BDPLAN.DESCR1 , PC_BDPLAN.PC_BUDGET_STATUS , PC_BDPLAN.ANALYSIS_TYPE , SUM(PC_BDPLAN.PC_BUD_FIN_AMT) , SUM(PC_BDPLAN.TOTAL_BUDGET_AMT) , SUM(AMT_TO_DISTR) , PC_BDPLAN.CURRENCY_CD , PC_BDPLAN.LAST_DTTIME , PC_BDPLAN.PROJECT_TYPE , PC_BDPLAN.PROJECT_STATUS , PC_BDPLAN.START_DT , PC_BDPLAN.END_DT , PC_BDPLAN.GRANT_FLG , PC_BDPLAN.PROJECT_MANAGER , PC_BDPLAN.OPRID FROM ( SELECT BP.BUSINESS_UNIT AS BUSINESS_UNIT , BP.PROJECT_ID AS PROJECT_ID , BP.PC_BUDGET_ID AS PC_BUDGET_ID , %NumToChar(0) AS BUDGET_PERIOD , BP.PC_BUD_TYPE AS PC_BUD_TYPE , %NumToChar(BP.PC_BUDGET_ID) AS PC_BUDID_PERIODID , C.DESCR AS DESCR , BP.DESCR AS DESCR1 , BP.PC_BUDGET_STATUS AS PC_BUDGET_STATUS , BP.ANALYSIS_TYPE AS ANALYSIS_TYPE , A.FOREIGN_AMOUNT AS PC_BUD_FIN_AMT , B.FOREIGN_AMOUNT AS TOTAL_BUDGET_AMT ,( B.FOREIGN_AMOUNT - CASE WHEN A.FOREIGN_AMOUNT IS NULL THEN 0 ELSE A.FOREIGN_AMOUNT END ) AS AMT_TO_DISTR , BP.CURRENCY_CD AS CURRENCY_CD , BP.LAST_DTTIME AS LAST_DTTIME , C.PROJECT_TYPE AS PROJECT_TYPE , PRST.PROJECT_STATUS AS PROJECT_STATUS , C.START_DT AS START_DT , C.END_DT AS END_DT , C.GRANT_FLG AS GRANT_FLG , MGR.PROJECT_MANAGER AS PROJECT_MANAGER , MPRJ.OPRID AS OPRID FROM PS_PC_BUD_PLAN BP , PS_PC_BUD_DETAIL B LEFT OUTER JOIN PS_PROJ_RESOURCE A ON (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.ACTIVITY_ID = B.ACTIVITY_ID AND A.RESOURCE_ID = B.RESOURCE_ID) , PS_PROJECT C LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_STATUS PRS WHERE PRS.EFFDT = ( SELECT MAX(PRS1.EFFDT) FROM PS_PROJECT_STATUS PRS1 WHERE PRS1.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS1.PROJECT_ID = PRS.PROJECT_ID AND PRS1.EFFDT <= %CurrentDateIn) AND PRS.EFFSEQ = ( SELECT MAX(PRS2.EFFSEQ) FROM PS_PROJECT_STATUS PRS2 WHERE PRS2.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS2.PROJECT_ID = PRS.PROJECT_ID AND PRS2.EFFDT = PRS.EFFDT) ) PRST ON (C.BUSINESS_UNIT = PRST.BUSINESS_UNIT AND C.PROJECT_ID = PRST.PROJECT_ID) LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_MGR MGR WHERE MGR.EFFDT = ( SELECT MAX(MGR1.EFFDT) FROM PS_PROJECT_MGR MGR1 WHERE MGR1.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND MGR1.PROJECT_ID = MGR.PROJECT_ID AND MGR1.EFFDT <= %CurrentDateIn) ) MGR ON (MGR.BUSINESS_UNIT = C.BUSINESS_UNIT AND MGR.PROJECT_ID = C.PROJECT_ID) ,PS_PC_OPRID_PRJACT MPRJ WHERE MPRJ.BUSINESS_UNIT = C.BUSINESS_UNIT AND MPRJ.PROJECT_ID = C.PROJECT_ID AND BP.BUSINESS_UNIT = C.BUSINESS_UNIT AND BP.PROJECT_ID = C.PROJECT_ID AND B.BUSINESS_UNIT = BP.BUSINESS_UNIT AND BP.PROJECT_ID = B.PROJECT_ID AND BP.PC_BUDGET_ID = B.PC_BUDGET_ID AND BP.PC_BUDGET_STATUS = '1' AND BP.BUDGET_ADJUST = 0 AND C.GRANT_FLG <> 'Y' AND C.EFF_STATUS = 'A' AND NOT EXISTS( SELECT 'X' FROM PS_PC_BUD_ACTIVITY BA WHERE BA.BUSINESS_UNIT = BP.BUSINESS_UNIT AND BA.PROJECT_ID = BP.PROJECT_ID AND BA.PC_BUDGET_ID = BP.PC_BUDGET_ID) AND NOT EXISTS( SELECT 'X' FROM PS_PC_BUD_ITEM BI WHERE BI.BUSINESS_UNIT = BP.BUSINESS_UNIT AND BI.PROJECT_ID = BP.PROJECT_ID AND BI.PC_BUDGET_ID = BP.PC_BUDGET_ID GROUP BY BI.BUSINESS_UNIT,BI.PROJECT_ID,BI.PC_BUDGET_ID,BI.ACTIVITY_ID HAVING SUM(BI.BUDGET_ADJUST) > 0) ) PC_BDPLAN GROUP BY PC_BDPLAN.BUSINESS_UNIT , PC_BDPLAN.PROJECT_ID , PC_BDPLAN.PC_BUDGET_ID , PC_BDPLAN.BUDGET_PERIOD , PC_BDPLAN.PC_BUD_TYPE , PC_BDPLAN.PC_BUDID_PERIODID , PC_BDPLAN.DESCR , PC_BDPLAN.DESCR1 , PC_BDPLAN.PC_BUDGET_STATUS , PC_BDPLAN.ANALYSIS_TYPE , PC_BDPLAN.CURRENCY_CD , PC_BDPLAN.LAST_DTTIME , PC_BDPLAN.PROJECT_TYPE , PC_BDPLAN.PROJECT_STATUS , PC_BDPLAN.START_DT , PC_BDPLAN.END_DT , PC_BDPLAN.GRANT_FLG , PC_BDPLAN.PROJECT_MANAGER , PC_BDPLAN.OPRID HAVING (SUM(PC_BDPLAN.PC_BUD_FIN_AMT) <> SUM(PC_BDPLAN.TOTAL_BUDGET_AMT)) OR (SUM(PC_BDPLAN.PC_BUD_FIN_AMT) IS NULL AND SUM(PC_BDPLAN.TOTAL_BUDGET_AMT) IS NOT NULL ) UNION SELECT PC_BDPLAN.BUSINESS_UNIT , PC_BDPLAN.PROJECT_ID , PC_BDPLAN.PC_BUDGET_ID , PC_BDPLAN.BUDGET_PERIOD , PC_BDPLAN.PC_BUD_TYPE , PC_BDPLAN.PC_BUDID_PERIODID , PC_BDPLAN.DESCR , PC_BDPLAN.DESCR1 , PC_BDPLAN.PC_BUDGET_STATUS , PC_BDPLAN.ANALYSIS_TYPE , SUM(PC_BDPLAN.PC_BUD_FIN_AMT) , SUM(PC_BDPLAN.TOTAL_BUDGET_AMT) , SUM(AMT_TO_DISTR) , PC_BDPLAN.CURRENCY_CD , PC_BDPLAN.LAST_DTTIME , PC_BDPLAN.PROJECT_TYPE , PC_BDPLAN.PROJECT_STATUS , PC_BDPLAN.START_DT , PC_BDPLAN.END_DT , PC_BDPLAN.GRANT_FLG , PC_BDPLAN.PROJECT_MANAGER , PC_BDPLAN.OPRID FROM ( SELECT BP.BUSINESS_UNIT AS BUSINESS_UNIT , BP.PROJECT_ID AS PROJECT_ID , BP.PC_BUDGET_ID AS PC_BUDGET_ID , %NumToChar(D.BUDGET_PERIOD_GM) AS BUDGET_PERIOD , BP.PC_BUD_TYPE AS PC_BUD_TYPE , %NumToChar(D.BUDGET_PERIOD_GM) AS PC_BUDID_PERIODID , C.DESCR AS DESCR , BP.DESCR AS DESCR1 , BP.PC_BUDGET_STATUS AS PC_BUDGET_STATUS , BP.ANALYSIS_TYPE AS ANALYSIS_TYPE , A.FOREIGN_AMOUNT AS PC_BUD_FIN_AMT , B.FOREIGN_AMOUNT AS TOTAL_BUDGET_AMT , ( B.FOREIGN_AMOUNT - CASE WHEN A.FOREIGN_AMOUNT IS NULL THEN 0 ELSE A.FOREIGN_AMOUNT END) AS AMT_TO_DISTR , BP.CURRENCY_CD AS CURRENCY_CD , BP.LAST_DTTIME AS LAST_DTTIME , C.PROJECT_TYPE AS PROJECT_TYPE , PRST.PROJECT_STATUS AS PROJECT_STATUS , C.START_DT AS START_DT , C.END_DT AS END_DT , C.GRANT_FLG AS GRANT_FLG , MGR.PROJECT_MANAGER AS PROJECT_MANAGER , MPRJ.OPRID AS OPRID FROM PS_PC_BUD_PLAN BP , PS_PC_BUD_DETAIL B LEFT OUTER JOIN PS_PROJ_RESOURCE A ON (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.PROJECT_ID = B.PROJECT_ID AND A.RESOURCE_ID = B.RESOURCE_ID) ,PS_PROJECT C LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_STATUS PRS WHERE PRS.EFFDT = ( SELECT MAX(PRS1.EFFDT) FROM PS_PROJECT_STATUS PRS1 WHERE PRS1.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS1.PROJECT_ID = PRS.PROJECT_ID AND PRS1.EFFDT <= %CurrentDateIn) AND PRS.EFFSEQ = ( SELECT MAX(PRS2.EFFSEQ) FROM PS_PROJECT_STATUS PRS2 WHERE PRS2.BUSINESS_UNIT = PRS.BUSINESS_UNIT AND PRS2.PROJECT_ID = PRS.PROJECT_ID AND PRS2.EFFDT = PRS.EFFDT) ) PRST ON (C.BUSINESS_UNIT = PRST.BUSINESS_UNIT AND C.PROJECT_ID = PRST.PROJECT_ID) LEFT OUTER JOIN ( SELECT * FROM PS_PROJECT_MGR MGR WHERE MGR.EFFDT = ( SELECT MAX(MGR1.EFFDT) FROM PS_PROJECT_MGR MGR1 WHERE MGR1.BUSINESS_UNIT = MGR.BUSINESS_UNIT AND MGR1.PROJECT_ID = MGR.PROJECT_ID AND MGR1.EFFDT <= %CurrentDateIn) ) MGR ON (MGR.BUSINESS_UNIT = C.BUSINESS_UNIT AND MGR.PROJECT_ID = C.PROJECT_ID), PS_GM_AWD_FUND_PD D , PS_GM_AWD_PROJT_VW AP ,PS_PC_OPRID_PRJACT MPRJ WHERE MPRJ.BUSINESS_UNIT = C.BUSINESS_UNIT AND MPRJ.PROJECT_ID = C.PROJECT_ID AND BP.BUSINESS_UNIT = C.BUSINESS_UNIT AND BP.PROJECT_ID = C.PROJECT_ID AND B.BUSINESS_UNIT = BP.BUSINESS_UNIT AND B.PROJECT_ID = BP.PROJECT_ID AND B.PC_BUDGET_ID = BP.PC_BUDGET_ID AND AP.BUSINESS_UNIT_PC = BP.BUSINESS_UNIT AND AP.PROJECT_ID = BP.PROJECT_ID AND AP.CONTRACT_NUM = D.CONTRACT_NUM AND AP.BUSINESS_UNIT_PC = D.BUSINESS_UNIT AND AP.PROJECT_ID = D.PROJECT_ID AND B.BUDGET_PERIOD = %NumToChar(D.BUDGET_PERIOD_GM) AND BP.PC_BUDGET_STATUS = '1' AND C.GRANT_FLG = 'Y' AND C.EFF_STATUS = 'A' ) PC_BDPLAN GROUP BY PC_BDPLAN.BUSINESS_UNIT , PC_BDPLAN.PROJECT_ID , PC_BDPLAN.PC_BUDGET_ID , PC_BDPLAN.BUDGET_PERIOD , PC_BDPLAN.PC_BUD_TYPE , PC_BDPLAN.PC_BUDID_PERIODID , PC_BDPLAN.DESCR , PC_BDPLAN.DESCR1 , PC_BDPLAN.PC_BUDGET_STATUS , PC_BDPLAN.ANALYSIS_TYPE , PC_BDPLAN.CURRENCY_CD , PC_BDPLAN.LAST_DTTIME , PC_BDPLAN.PROJECT_TYPE , PC_BDPLAN.PROJECT_STATUS , PC_BDPLAN.START_DT , PC_BDPLAN.END_DT , PC_BDPLAN.GRANT_FLG , PC_BDPLAN.PROJECT_MANAGER , PC_BDPLAN.OPRID HAVING (SUM(PC_BDPLAN.PC_BUD_FIN_AMT) <> SUM(PC_BDPLAN.TOTAL_BUDGET_AMT)) OR (SUM(PC_BDPLAN.PC_BUD_FIN_AMT) IS NULL AND SUM(PC_BDPLAN.TOTAL_BUDGET_AMT) IS NOT NULL )

  • Related Language Record: PC_BDPLANLNG_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    3 PC_BUDGET_ID Number(3,0) SMALLINT NOT NULL Budget Plan ID
    4 BUDGET_PERIOD Character(8) VARCHAR2(8) NOT NULL Budget Period
    5 PC_BUD_TYPE Character(1) VARCHAR2(1) NOT NULL This field defines a Projects Budget as either Cost or Revenue Based.
    C=Cost Budget
    R=Revenue Budget
    6 PC_BUDID_PERIODID Character(11) VARCHAR2(11) NOT NULL Budget Plan / Period
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    9 PC_BUDGET_STATUS Character(1) VARCHAR2(1) NOT NULL Project Budgeting: Budget Status
    1=Active
    2=Modified
    3=Inactive
    10 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
    11 PC_BUD_FIN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Finalized amount. Let's the user see if there are amounts that still need to be finalized.
    12 TOTAL_BUDGET_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL TOTAL_BUDGET_AMT
    13 AMT_TO_DISTR Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount to Distribute
    14 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    15 LAST_DTTIME DateTime(26) TIMESTAMP Last Date Time
    16 PROJECT_TYPE Character(5) VARCHAR2(5) NOT NULL Project Type
    A=Activity
    G=Grant
    H=Phase
    P=Project
    S=Segment
    17 PROJECT_STATUS Character(1) VARCHAR2(1) NOT NULL Project Status
    B=Budgeted
    C=Closed
    H=Hold
    O=Open
    P=Proposed

    Prompt Table: PC_WC_PRJSTS_VW

    18 START_DT Date(10) DATE Start Date
    19 END_DT Date(10) DATE End Date
    20 GRANT_FLG Character(1) VARCHAR2(1) NOT NULL Grants Management Used for designating whether or not an object is for Grants or for Projects.
    N=Projects Object
    Y=Grants Object
    21 PROJECT_MANAGER Character(11) VARCHAR2(11) NOT NULL Project Manager
    22 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).