PC_UNBIL_CST_VW

(SQL View)
 Index Back

Project Unbilled Cost WIP

Project Unbilled Cost WIP

SELECT B.OPRID , A.BUSINESS_UNIT , A.PROJECT_ID , C.DESCR , D.PROJECT_MANAGER , E.FIRST_NAME %Concat ' ' %Concat E.LAST_NAME , EMAIL.EMAIL_ADDR , SUM(A.RESOURCE_AMOUNT) , SUM(CASE WHEN A.BI_DISTRIB_STATUS IN ('U' , 'I') THEN 0 ELSE A.ACTUAL_AMOUNT END) , SUM(CASE WHEN A.BI_DISTRIB_STATUS IN ('D' , 'W') THEN A.ACTUAL_AMOUNT ELSE 0 END) , SUM((CASE WHEN A.BI_DISTRIB_STATUS IN ('U' , 'I') THEN 0 ELSE A.ACTUAL_AMOUNT END) - (CASE WHEN A.BI_DISTRIB_STATUS IN ('D' , 'W') THEN A.ACTUAL_AMOUNT ELSE 0 END)) , A.CURRENCY_CD , RPT.PC_GROUP_NAME , RPT.DISPLAY_ORDER , A.BUSINESS_UNIT_CA , A.CONTRACT_NUM , A.CUST_ID , A.NAME1 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_PC_RPT_GROUPS RPT , 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 A.SYSTEM_SOURCE NOT IN ('PRP', 'PRR', 'PRC') AND A.BI_DISTRIB_STATUS NOT IN ('U', 'I') AND B.OPRID = OPR.OPRID AND B.BUSINESS_UNIT = OPR.BUSINESS_UNIT_WRK AND RPT.FEATURE_CD = 'PC_UNB_CST' AND A.ACTUAL_AMOUNT <> 0 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 %DateDiff(A.TRANS_DT, %CURRENTDATEIN) BETWEEN RPT.FROM_RANGE AND RPT.TO_RANGE GROUP BY B.OPRID, A.BUSINESS_UNIT, A.PROJECT_ID, C.DESCR, D.PROJECT_MANAGER, E.FIRST_NAME, E.LAST_NAME, EMAIL.EMAIL_ADDR, A.CURRENCY_CD, RPT.PC_GROUP_NAME, RPT.DISPLAY_ORDER, RPT.FROM_RANGE, RPT.TO_RANGE, A.BUSINESS_UNIT_CA, A.CONTRACT_NUM, A.CUST_ID, A.NAME1

  • Related Language Record: PC_UNBIL_CSTLVW

    • # 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 PROJ_MGR_NAME Character(50) VARCHAR2(50) NOT NULL Project Manager Name
    7 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    8 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
    9 ACTUAL_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Actual Invoice Amount
    10 BILLED_AMT_PC Signed Number(28,3) DECIMAL(26,3) NOT NULL Billed Amount
    11 UNBILLED_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Unbilled Amount
    12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    13 PC_GROUP_NAME Character(30) VARCHAR2(30) NOT NULL Analytics Grouping Name
    14 DISPLAY_ORDER Number(3,0) SMALLINT NOT NULL Display order for ordering items in a list - web style
    15 BUSINESS_UNIT_CA Character(5) VARCHAR2(5) NOT NULL Contracts Business Unit
    16 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    17 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.
    18 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1