RE_ASSET_LOC_VW

(SQL View)
 Index Back

Asset Current Location

Note: this view uses "BUSINESS_UNIT_AM" as an alias for "BUSINESS_UNIT" and "LOCATION2" as an alias for "LOCATION". The view is used in complex SQL to retrieve properties that are not assigned to a lease. Using the original field names causes ambiguity in the generated SQL when the users enters Business Unit and Location Code as search keys in the prompt. Having these unique names in the view overcomes this ambiguity.

SELECT BUSINESS_UNIT , ASSET_ID , EFFDT , EFFSEQ , LOCATION , AREA_ID , DOCUMENT_ID , AUTH_STATUS , AUTHORIZATION_NAME , AUTHORIZATION_DT FROM PS_ASSET_LOCATION A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_ASSET_LOCATION B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ASSET_ID = B.ASSET_ID AND B.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))

# 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 LOCATION_EFFDT Date(10) DATE Location Effective Date
4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 LOCATION2 Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

6 AREA_ID Character(10) VARCHAR2(10) NOT NULL Area ID
7 DOCUMENT_ID Character(12) VARCHAR2(12) NOT NULL Document
8 AUTH_STATUS Character(1) VARCHAR2(1) NOT NULL Authorization Status
A=Authorized
N=Not Authorized
P=Authorization Pending
9 AUTHORIZATION_NAME Character(30) VARCHAR2(30) NOT NULL Authorization Name
10 AUTHORIZATION_DT Date(10) DATE Date Authorized