PARENT_CHLD_VW2

(SQL View)
 Index Back

Parent-child view2


SELECT C.BUSINESS_UNIT , A.PARENT_ID , C.ASSET_ID , A.DESCR , A.TAG_NUMBER , A.ASSET_STATUS , SUM(C.COST) , SUM(C.QUANTITY) , C.FROM_CUR , A.ACQUISITION_DT , A.IN_SERVICE_DT , E.LAST_TRANS_DT , E.LAST_ACCOUNTING_DT , A.FINANCIAL_ASSET_SW FROM PS_ASSET A , PS_PARENT_ASSET B , PS_COST C , PS_BUS_UNIT_TBL_AM D , PS_BOOK E WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.PARENT_ID = A.PARENT_ID AND A.ASSET_ID <> A.PARENT_ID AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.ASSET_ID = A.ASSET_ID AND B.DELETE_PARENT_SW IN ('N', ' ') AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND C.BOOK = ( SELECT MIN(F.BOOK) FROM PS_BOOK F WHERE F.BUSINESS_UNIT = C.BUSINESS_UNIT AND F.ASSET_ID = C.ASSET_ID) AND C.BUSINESS_UNIT = E.BUSINESS_UNIT AND C.ASSET_ID = E.ASSET_ID AND C.BOOK = E.BOOK AND A.ASSET_STATUS NOT IN ('T') GROUP BY C.BUSINESS_UNIT, A.PARENT_ID, C.ASSET_ID, A.DESCR, A.TAG_NUMBER, A.ASSET_STATUS, C.FROM_CUR, A.ACQUISITION_DT, A.IN_SERVICE_DT, E.LAST_TRANS_DT, E.LAST_ACCOUNTING_DT, A.FINANCIAL_ASSET_SW

  • Related Language Record: PRENTCHILD_L_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
    3 CHILD_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Child Asset ID
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
    6 ASSET_STATUS Character(1) VARCHAR2(1) NOT NULL Asset Status
    A=Received (Not in Service)
    B=Budgeted
    C=Commitment
    D=Disposed
    I=In Service
    M=Suspended
    R=Requisitioned
    T=Transferred
    W=Work In Progress
    7 COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Cost
    8 QUANTITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Interface
    9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    10 ACQUISITION_DT Date(10) DATE Acquisition Date
    11 IN_SERVICE_DT Date(10) DATE In Service Date
    12 LAST_TRANS_DT Date(10) DATE Last Transaction Date
    13 LAST_ACCOUNTING_DT Date(10) DATE Last Accounting Date
    14 FINANCIAL_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Capitalized Asset
    N=Non Cap
    Y=Capitalize