ASSET_LOC_VW3

(SQL View)
 Index Back

Asset Current Location


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.AREA_ID , A.EFFDT , A.EFFSEQ , B.DESCR , B.DESCR_AC , B.DESCRSHORT , B.SETID FROM PS_ASSET_LOCATION A , PS_LOCATION_TBL B , PS_SET_CNTRL_REC S WHERE S.RECNAME = 'LOCATION_TBL' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.SETID = B.SETID AND B.LOCATION = A.LOCATION AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL EB WHERE EB.SETID = B.SETID AND EB.LOCATION = B.LOCATION AND EB.EFFDT <= %CurrentDateIn) UNION SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.AREA_ID , A.EFFDT , A.EFFSEQ , ' ' , ' ' , ' ' , S.SETID FROM PS_ASSET_LOCATION A , PS_SET_CNTRL_REC S WHERE S.RECNAME = 'LOCATION_TBL' AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND A.LOCATION=' '

  • Related Language Record: ASSET_LOC_VW3_L

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

    Prompt Table: SP_BU_AM_NONVW

    2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
    3 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    4 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
    5 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    6 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Description
    9 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    10 SETID1 Character(5) VARCHAR2(5) NOT NULL SetID Value