RE_UNLEASE_LOC

(SQL View)
 Index Back

Asset Location For Property

This record will be used to select some information from Asset Location in the properties to be able to link to a lease.

SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.EFFDT , A.LOCATION , B.COUNTRY , B.STATE , B.CITY , B.ADDRESS1 ,B.ADDRESS2 ,B.SETID FROM PS_ASSET_LOCATION A , PS_LOCATION_TBL B WHERE 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 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
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 SETID Character(5) VARCHAR2(5) NOT NULL SetID