ASSET_LOC_VW2

(SQL View)
 Index Back

Asset Current Location


SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.LOCATION , A.AREA_ID , B.DESCR , B.DESCR_AC , B.DESCRSHORT , B.BUILDING , B.FLOOR , B.SECTOR , B.COUNTRY , B.ADDRESS1 , B.ADDRESS2 , B.ADDRESS3 , B.ADDRESS4 , B.CITY , B.NUM1 , B.NUM2 , B.ADDR_FIELD1 , B.ADDR_FIELD2 , B.ADDR_FIELD3 , B.COUNTY , B.STATE , B.POSTAL FROM PS_ASSET_LOCATION A , PS_LOCATION_TBL B , PS_SET_CNTRL_REC S WHERE A.EFFDT = ( SELECT MAX(EA.EFFDT) FROM PS_ASSET_LOCATION EA WHERE A.BUSINESS_UNIT = EA.BUSINESS_UNIT AND A.ASSET_ID = EA.ASSET_ID AND EA.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(Y.EFFSEQ) FROM PS_ASSET_LOCATION Y WHERE Y.BUSINESS_UNIT = A.BUSINESS_UNIT AND Y.ASSET_ID = A.ASSET_ID AND Y.EFFDT = A.EFFDT) AND 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)

  • Related Language Record: ASSET_LOC_VW2_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_CLSVW

    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 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCR_AC Character(30) VARCHAR2(30) NOT NULL Alternate Description
    7 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    8 BUILDING Character(10) VARCHAR2(10) NOT NULL Building #
    9 FLOOR Character(10) VARCHAR2(10) NOT NULL Floor #
    10 SECTOR Character(10) VARCHAR2(10) NOT NULL Sector
    11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    12 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    14 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    15 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    16 CITY Character(30) VARCHAR2(30) NOT NULL City
    17 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    18 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    19 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    20 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    21 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    22 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    23 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    24 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal