IT_PHY_USE

(SQL View)
 Index Back

IT Asset Physical USe

IT Asset Physical Use

SELECT A.BUSINESS_UNIT , A.ASSET_ID , A.SERIAL_ID , D.DUP_ASSET_SW , A.IN_PHY_USE , A.ASSET_SUBTYPE , A.TAG_NUMBER , C.DEPTID , C.EMPLID , L.LOCATION , A.MFG_ID , A.MANUFACTURER , A.MODEL FROM PS_ASSET A , PS_IT_DUP_ASSET D , PS_ASSET_LOCATION L , PS_ASSET_CUSTODIAN C WHERE A.ASSET_TYPE = '010' AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.ASSET_ID = D.ASSET_ID AND A.BUSINESS_UNIT = L.BUSINESS_UNIT AND D.ASSET_ID = L.ASSET_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ASSET_ID = C.ASSET_ID AND L.EFFDT = ( SELECT MAX(L2.EFFDT) FROM PS_ASSET_LOCATION L2 WHERE L2.BUSINESS_UNIT = L.BUSINESS_UNIT AND L2.ASSET_ID = L.ASSET_ID) AND L.EFFSEQ = ( SELECT MAX(L3.EFFSEQ) FROM PS_ASSET_LOCATION L3 WHERE L3.BUSINESS_UNIT = L.BUSINESS_UNIT AND L3.ASSET_ID = L.ASSET_ID AND L3.EFFDT = L.EFFDT) AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_ASSET_CUSTODIAN C2 WHERE C2.BUSINESS_UNIT = C.BUSINESS_UNIT AND C2.ASSET_ID = C.ASSET_ID) AND C.EFFSEQ = ( SELECT MAX(C3.EFFSEQ) FROM PS_ASSET_CUSTODIAN C3 WHERE C3.BUSINESS_UNIT = C.BUSINESS_UNIT AND C3.ASSET_ID = C.ASSET_ID AND C3.EFFDT = C.EFFDT) UNION SELECT AA.BUSINESS_UNIT , AA.ASSET_ID , AA.SERIAL_ID , 'N' , AA.IN_PHY_USE , AA.ASSET_SUBTYPE , AA.TAG_NUMBER , CC.DEPTID , CC.EMPLID , LL.LOCATION , AA.MFG_ID , AA.MANUFACTURER , AA.MODEL FROM PS_ASSET AA , PS_ASSET_LOCATION LL , PS_ASSET_CUSTODIAN CC WHERE AA.ASSET_TYPE = '010' AND AA.BUSINESS_UNIT = LL.BUSINESS_UNIT AND AA.ASSET_ID = LL.ASSET_ID AND AA.BUSINESS_UNIT = CC.BUSINESS_UNIT AND AA.ASSET_ID = CC.ASSET_ID AND LL.EFFDT = ( SELECT MAX(LL2.EFFDT) FROM PS_ASSET_LOCATION LL2 WHERE LL2.BUSINESS_UNIT = LL.BUSINESS_UNIT AND LL2.ASSET_ID = LL.ASSET_ID) AND LL.EFFSEQ = ( SELECT MAX(LL3.EFFSEQ) FROM PS_ASSET_LOCATION LL3 WHERE LL3.BUSINESS_UNIT = LL.BUSINESS_UNIT AND LL3.ASSET_ID = LL.ASSET_ID AND LL3.EFFDT = LL.EFFDT) AND CC.EFFDT = ( SELECT MAX(CC2.EFFDT) FROM PS_ASSET_CUSTODIAN CC2 WHERE CC2.BUSINESS_UNIT = CC.BUSINESS_UNIT AND CC2.ASSET_ID = CC.ASSET_ID) AND CC.EFFSEQ = ( SELECT MAX(CC3.EFFSEQ) FROM PS_ASSET_CUSTODIAN CC3 WHERE CC3.BUSINESS_UNIT = CC.BUSINESS_UNIT AND CC3.ASSET_ID = CC.ASSET_ID AND CC3.EFFDT = CC.EFFDT) AND NOT EXISTS ( SELECT 'X' FROM PS_IT_DUP_ASSET DD WHERE DD.BUSINESS_UNIT = AA.BUSINESS_UNIT AND DD.ASSET_ID = AA.ASSET_ID)

# 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

Default Value: NEXT

3 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
4 DUP_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Duplicate Asset Switch
N=No
Y=Yes
5 IN_PHY_USE Character(1) VARCHAR2(1) NOT NULL Describes if entity is in physical use.

Y/N Table Edit

Default Value: N

6 ASSET_SUBTYPE Character(15) VARCHAR2(15) NOT NULL Asset Subtype
7 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
9 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
11 MFG_ID Character(50) VARCHAR2(50) NOT NULL Manufacturer ID
12 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
13 MODEL Character(30) VARCHAR2(30) NOT NULL Model