RE_PROP_ADDR

(SQL View)
 Index Back

SELECT LS.LS_KEY , A.ASSET_ID , A.EFFDT , A.LOCATION , B.COUNTRY , B.STATE , B.CITY , B.ADDRESS1 ,B.ADDRESS2 ,B.POSTAL FROM PS_ASSET_LOCATION A , PS_LOCATION_TBL B ,PS_ASSET_PROPERTY AP ,PS_RE_LS LS WHERE LS.PRIM_PROPERTY_ID = AP.PROPERTY_ID AND AP.ASSET_ID = A.ASSET_ID AND AP.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.LOCATION = B.LOCATION AND A.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_ASSET_LOCATION C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = C.ASSET_ID AND C.EFFDT <= %CurrentDateIn) AND B.SETID = ( SELECT X.SETID FROM PS_SET_CNTRL_REC X WHERE X.SETCNTRLVALUE = A.BUSINESS_UNIT AND X.RECNAME = 'LOCATION_TBL') AND B.EFF_STATUS = 'A' AND %Coalesce(B.EFFDT, %CurrentDateIn) = CASE WHEN B.EFFDT IS NOT NULL THEN ( SELECT MAX(B_ED.EFFDT) FROM PS_LOCATION_TBL B_ED WHERE B_ED.SETID = B.SETID AND B_ED.LOCATION = B.LOCATION AND B_ED.EFF_STATUS='A' AND B_ED.EFFDT <= A.EFFDT) ELSE %CurrentDateIn END

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
2 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
5 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
6 STATE Character(6) VARCHAR2(6) NOT NULL State
7 CITY Character(30) VARCHAR2(30) NOT NULL City
8 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
9 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
10 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal