PC_AM_ASSET_VW(SQL View) |
Index Back |
---|---|
Projects AssetsView 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 |