PC_AM_ASSET_VW

(SQL View)
Index Back

Projects Assets

View of Assets and the Business Unit, Project, and Activity that is related to that asset.

SELECT A.BUSINESS_UNIT , A.PROJECT_ID , A.ACTIVITY_ID , A.ANALYSIS_TYPE , B.BUSINESS_UNIT_AM , B.ASSET_ID , B.INTFC_ID , B.INTFC_LINE_NUM , B.PROFILE_ID , B.GROUP_ASSET_ID , B.PARENT_ID , B.PARENT_DESCR , B.AM_DISTRIB_STATUS , B.BOOK , B.CATEGORY , B.QUANTITY , B.COST_TYPE , B.LOCATION , A.ANALYSIS_GROUP , A.ANALYSIS_GROUP_RET , A.ANALYSIS_GROUP_AMR , B.RESOURCE_TYPE , B.RESOURCE_CATEGORY , B.RESOURCE_SUB_CAT , B.ASSET_OPTION , B.GROUP_ASSET_FLAG , B.TRANS_DT , B.ACCOUNTING_DT , B.IN_SERVICE_DT , B.RETIREMENT_DT , B.RETIRE_CONVENTION , B.DISPOSAL_CODE , B.RETIREMENT_TYPE , B.RETIREMENT_STATUS , 0 , 0 , B.PROCEEDS , B.REMOVAL_COST , B.TRADE_IN_ASSET_ID , B.TRADE_IN_DTTM_STMP , B.TRADE_IN_AMT , B.GAIN_LOSS , B.CASH , B.TRADE_IN_VALUE , B.NON_LIKE_KIND , B.LIABILITY_ASSUMED , B.FULLY_DEPR_SW , B.TXN_PROCEEDS , B.TXN_REMOVAL_COST , B.TXN_CASH , B.TXN_TRADE_IN_VALUE , B.TXN_NON_LIKE_KIND , B.TXN_LIAB_ASSUMED , B.TXN_CURRENCY_CD2 , B.RATE_MULT_2 , B.RATE_DIV_2 , B.CURRENCY_CD , B.TAGGABLE_SW , B.TAG_NUMBER , B.ASSET_STATUS , B.REPLACE_SW , B.REPLACE_ASSET_ID , B.ACQUISITION_CD , B.CUSTODIAN , B.DESCR , B.DEFAULT_PROFILE_SW , B.INTFC_STATUS , B.INTFC_TYPE , A.INTFC_TYPE_RET , B.LOAD_STATUS , B.TRANS_CODE , B.SYSTEM_SOURCE , B.OPEN_TRANS_ID , B.APPROVAL_SW , B.BOOK_EFFDT , B.LOCATION_EFFDT , B.ASSET_ALLOC_QTY , B.ASSET_ALLOC_AMT , B.IN_PROCESS_FLG , A.ASSET_ALLOC_PCT , A.BUSINESS_UNIT , B.DEPTID , B.DESCRSHORT , B.MANUFACTURER , B.MODEL , B.SERIAL_ID , B.FILTER_DTTM FROM PS_PC_AM_PROJ_DEFN A , PS_PC_AM_DEFN B WHERE %Join(COMMON_KEYS, PC_AM_PROJ_DEFN A, PC_AM_DEFN B)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_PC_NONVW

2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: PROJ_SCRTY_VW

3 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID

Prompt Table: PROJ_ACT_SRCH

4 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type

Prompt Table: PROJ_ANTYPE_TBL

5 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit

Default Value: PC_INT_TMPL.BUSINESS_UNIT_AM

Prompt Table: SP_BU_AM_NONVW

6 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification

Prompt Table: %EDITTABLE

7 INTFC_ID Number(8,0) INTEGER NOT NULL Interface ID
8 INTFC_LINE_NUM Number(8,0) INTEGER NOT NULL Interface Line Number
9 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID

Prompt Table: PROFILE_PCAM_VW

10 GROUP_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Group Asset ID

Prompt Table: %EDITTABLE6

11 PARENT_ID Character(12) VARCHAR2(12) NOT NULL Parent ID

Prompt Table: %EDITTABLE2

12 PARENT_DESCR Character(30) VARCHAR2(30) NOT NULL Parent Asset Description
13 AM_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL AM Distribution Status
D=Distributed
I=Ignore
M=Modified
N=Not Distributed
P=Pending
V=Reversed

Default Value: N

14 BOOK Character(10) VARCHAR2(10) NOT NULL Asset Book Name

Prompt Table: %EDITTABLE3

15 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category

Prompt Table: %EDITTABLE4

16 QUANTITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Interface

Default Value: 1

17 COST_TYPE Character(1) VARCHAR2(1) NOT NULL Cost Type

Prompt Table: %EDITTABLE5

18 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

19 ANALYSIS_GROUP Character(5) VARCHAR2(5) NOT NULL Analysis Group

Prompt Table: SP_AN_GRP_NONVW

20 ANALYSIS_GROUP_RET Character(5) VARCHAR2(5) NOT NULL Retirement Analysis Group

Prompt Table: SP_AN_GRP_NONVW

21 ANALYSIS_GROUP_AMR Character(5) VARCHAR2(5) NOT NULL Retire Proceed and Cost An Grp
22 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type

Prompt Table: PROJ_RES_TYPE

23 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category

Prompt Table: PROJ_CATG_TBL

24 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory

Prompt Table: PROJ_SUBCAT_TBL

25 ASSET_OPTION Character(3) VARCHAR2(3) NOT NULL Asset Creation Option
ADD=Auto Assign New Asset ID
ADJ=Adjust Existing Asset
MAN=Manually Assign Asset ID

Default Value: MAN

26 GROUP_ASSET_FLAG Character(1) VARCHAR2(1) NOT NULL Group Asset Flag
G=Group Asset
M=Group Member
N=None
27 TRANS_DT Date(10) DATE Transaction Date

Default Value: %DATE

28 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple

Default Value: %DATE

29 IN_SERVICE_DT Date(10) DATE In Service Date
30 RETIREMENT_DT Date(10) DATE Projected Retirement Date
31 RETIRE_CONVENTION Character(2) VARCHAR2(2) NOT NULL Retirement Convention
32 DISPOSAL_CODE Character(1) VARCHAR2(1) NOT NULL Retirement Type
A=Abandonment
C=Cannibalize for Other Assets
D=Disappeared Assets
E=Expensed
F=Disposal Due to Theft
I=Returned to Inventory
L=Like Kind Exchange
M=Missing Asset
N=Donated to External Group
O=ARO Settlement
R=Scrapped Assets
S=Retirement by Sale
T=Traded In for another Asset
V=Auto-Retire Fully Depr Assets
Y=Casualty Loss

Default Value: S

33 RETIREMENT_TYPE Character(1) VARCHAR2(1) NOT NULL Retirement Type
E=Extraordinary
O=Ordinary Retirement

Default Value: O

34 RETIREMENT_STATUS Character(1) VARCHAR2(1) NOT NULL Retirement Status
C=Depreciation Calculated
N=New Retirement
P=Posted to General Ledger
R=Reinstated Retirement
U=No Retirements Exist

Default Value: U

35 RETIRE_QUANTITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Retirement Quantity
36 RETIREMENT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Retirement Amount
37 PROCEEDS Signed Number(28,3) DECIMAL(26,3) NOT NULL Retirement Proceeds
38 REMOVAL_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Removal Cost
39 TRADE_IN_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Trade In Asset

Prompt Table: ASSET

40 TRADE_IN_DTTM_STMP DateTime(26) TIMESTAMP Trade In DateTime Stamp
41 TRADE_IN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Trade In Amount
42 GAIN_LOSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Retirement Gain/Loss
43 CASH Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Received/Paid
44 TRADE_IN_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Trade In value
45 NON_LIKE_KIND Signed Number(28,3) DECIMAL(26,3) NOT NULL FMV Non Like Kind Property
46 LIABILITY_ASSUMED Signed Number(28,3) DECIMAL(26,3) NOT NULL Liabilities Assumed
47 FULLY_DEPR_SW Character(1) VARCHAR2(1) NOT NULL Retire As Fully Depreciated SW
F=Fully Depr with Future Expense
N=Calculate Gain/Loss
Y=Fully Depr no Future Expense
48 TXN_PROCEEDS Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Proceeds
49 TXN_REMOVAL_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Removal Cost
50 TXN_CASH Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Cash
51 TXN_TRADE_IN_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Trade-In Value
52 TXN_NON_LIKE_KIND Signed Number(28,3) DECIMAL(26,3) NOT NULL FMV Non Like-Kind
53 TXN_LIAB_ASSUMED Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Liability Assumed
54 TXN_CURRENCY_CD2 Character(3) VARCHAR2(3) NOT NULL Transaction Currency Display
55 RATE_MULT_2 Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
56 RATE_DIV_2 Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
57 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
58 TAGGABLE_SW Character(1) VARCHAR2(1) NOT NULL Taggable Asset
N=Non Taggable Asset
Y=Taggable Asset
59 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
60 ASSET_STATUS Character(1) VARCHAR2(1) NOT NULL Asset Status
A=Received (Not in Service)
B=Budgeted
C=Commitment
D=Disposed
I=In Service
M=Suspended
R=Requisitioned
T=Transferred
W=Work In Progress
61 REPLACE_SW Character(1) VARCHAR2(1) NOT NULL Replacement Asset

Default Value: N

62 REPLACE_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Replaced Asset ID
63 ACQUISITION_CD Character(1) VARCHAR2(1) NOT NULL Acquisition Code
C=Constructed
D=Donated
I=Trade In
K=Like Exchange
L=Leased
P=Purchased
T=Transferred

Default Value: C

64 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
65 DESCR Character(30) VARCHAR2(30) NOT NULL Description
66 DEFAULT_PROFILE_SW Character(1) VARCHAR2(1) NOT NULL Default Books from Profile

Y/N Table Edit

Default Value: Y

67 INTFC_STATUS Character(3) VARCHAR2(3) NOT NULL Interface Status
CON=Consolidated
DON=Mass Changes Generated
ERR=Error
NEW=Mass Changes Not Generated
REP=Replaced by Other Records (AP)
UNI=Unitized

Default Value: NEW

68 INTFC_TYPE Character(3) VARCHAR2(3) NOT NULL Trans Load Type

Default Value: FAD

69 INTFC_TYPE_RET Character(3) VARCHAR2(3) NOT NULL Retirement Interface Type
AAD=Additional Cost Add
ACQ=Insert Acquisition Detl
ADD=Asset Add
ADJ=Asset Cost Adjustment
ADQ=Adjust Cost & Acquisition Detl
ARA=Add Retired Assets
ASD=Asset Deletion
AUP=Asset Information Update
BKD=Book Deletion
BKS=Book Change
BSD=Selective Book Delete
CAP=Asset Capitalization
CIC=Capitalize Into Composite
CLC=Custodian/Location Change
CN1=Conversion of First Book
CN2=Conversion of Subsequent Books
FAD=Financial & Physical Add
IHI=Inventory History Insert
IUI=Inter-Unit Transfer In Side
IUO=Inter-Unit Transfer Out Side
LAD=Leased Asset Add
NAD=Non-Financial Add
OPT=Open Trans Generation
PHY=Physical Asset Change
PRT=Partial Retirement
RCT=Asset Recategorization
REI=Asset Reinstatement
RET=Asset Retirement
TRF=Intra-Business Unit Transfer
70 LOAD_STATUS Character(3) VARCHAR2(3) NOT NULL /* -> JMZ. PSOFT. 11-OCT-2002 */ New Status were added : HLD and INP for Asset Management. /* <- JMZ. PSOFT. 11-OCT-2002 */
CON=Consolidated
DON=Loaded
ERR=Errored
EXC=Excluded
HLD=On Hold
INP=In Process
NEW=Pending
PGI=PO Grp Incomplete
REP=Replaced
UNI=Unitized

Default Value: NEW

71 TRANS_CODE Character(5) VARCHAR2(5) NOT NULL Transaction Code
72 SYSTEM_SOURCE Character(3) VARCHAR2(3) NOT NULL Identifies the application or source system that generated a journal entry. Release 8.80 - 11/14/2002 - RVlasic - Removed GDM (JrnlGen - Deduction Management) from Translate Values. SUJ---Included a label as Product. Release 8.9 - 05/11/2005 - RVlasic - Added PRV (Variance Pricing) and PKK (KK Budget Journal) and deleted GLK for Project Costing per Brian Cohen.

Default Value: BPC

73 OPEN_TRANS_ID Number(10,0) DECIMAL(10) NOT NULL Open Transaction ID
74 APPROVAL_SW Character(1) VARCHAR2(1) NOT NULL Auto Approval Status

Y/N Table Edit

Default Value: Y

75 BOOK_EFFDT Date(10) DATE Book Effective Date
76 LOCATION_EFFDT Date(10) DATE Location Effective Date
77 ASSET_ALLOC_QTY Signed Number(17,2) DECIMAL(15,2) NOT NULL Asset Allocation Basis Qty
78 ASSET_ALLOC_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Asset Allocation Basis Amt
79 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag
80 ASSET_ALLOC_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Asset Allocation Percentage
81 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: PROJ_ACTIVITY.SETID

Prompt Table: SETID_TBL

82 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
83 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
84 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
85 MODEL Character(30) VARCHAR2(30) NOT NULL Model
86 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
87 FILTER_DTTM DateTime(26) TIMESTAMP Filter date time stamp