PROJ_AM_PCA_VW

(SQL View)
 Index Back

PC to AM Retirement View


SELECT B.BUSINESS_UNIT ,B.PROJECT_ID ,B.ACTIVITY_ID ,C.RESOURCE_ID ,'PCA' ,C.INTFC_LINE_NUM ,A.BUSINESS_UNIT_AM ,A.PROFILE_ID ,A.ASSET_ID ,C.INCENTIVE_ID ,A.GROUP_ASSET_ID ,A.PARENT_ID ,A.PARENT_DESCR ,C.BOOK ,C.CATEGORY ,C.QUANTITY ,A.COST_TYPE ,C.COST_TYPE ,A.LOCATION ,B.ANALYSIS_GROUP_RET ,A.RESOURCE_TYPE ,A.RESOURCE_CATEGORY ,A.RESOURCE_SUB_CAT ,A.ASSET_OPTION ,A.GROUP_ASSET_FLAG ,A.TRANS_DT ,A.ACCOUNTING_DT ,A.IN_SERVICE_DT ,%subrec(PC_AM_RET_SBR, C) ,A.TAGGABLE_SW ,A.TAG_NUMBER ,A.ASSET_STATUS ,A.REPLACE_SW ,A.REPLACE_ASSET_ID ,A.ACQUISITION_CD ,A.CUSTODIAN ,A.INTFC_ID ,A.DESCR ,A.DEFAULT_PROFILE_SW , C.BK_CAP_THRSHLD_ID ,A.INTFC_STATUS ,B.INTFC_TYPE_RET ,A.LOAD_STATUS ,A.TRANS_CODE ,A.SYSTEM_SOURCE ,A.OPEN_TRANS_ID ,A.APPROVAL_SW ,A.BOOK_EFFDT ,A.LOCATION_EFFDT ,A.DESCRSHORT ,A.MANUFACTURER ,A.MODEL ,A.SERIAL_ID ,A.ASSET_ALLOC_QTY ,A.ASSET_ALLOC_AMT ,%Round(%DecMult(C.RESOURCE_QUANTITY, -1), 2) ,%Round(%DecMult(C.RESOURCE_AMOUNT, -1), 3) ,' ' ,C.CURRENCY_CD ,%subrec(CF16_AN_SBR, C) ,C.DEPTID ,A.DEPTID ,'N' ,C.RT_TYPE ,C.RATE_MULT ,C.RATE_DIV ,C.RT_EFFDT ,%Round(%DecMult(C.TXN_COST, -1), 3) ,C.TXN_CURRENCY_CD ,' ' ,%subrec(PC_AM_BOOK_SBR, C) FROM PS_PC_AM_DEFN A , PS_PC_AM_PROJ_DEFN B , PS_PROJ_AM C WHERE A.BUSINESS_UNIT_AM = B.BUSINESS_UNIT_AM AND A.ASSET_ID = B.ASSET_ID AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PROJECT_ID = C.PROJECT_ID AND B.ACTIVITY_ID = C.ACTIVITY_ID AND B.BUSINESS_UNIT_AM = C.BUSINESS_UNIT_AM AND B.ASSET_ID = C.ASSET_ID AND C.ANALYSIS_TYPE = 'PCL' AND A.AM_DISTRIB_STATUS <> 'I' AND NOT EXISTS ( SELECT 'X' FROM PS_PROJ_AM D WHERE D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.PROJECT_ID = C.PROJECT_ID AND D.ACTIVITY_ID = C.ACTIVITY_ID AND D.BUSINESS_UNIT_AM = C.BUSINESS_UNIT_AM AND D.ASSET_ID = C.ASSET_ID AND D.ANALYSIS_TYPE = 'PCA')

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

Default Value: PROJECT.BUSINESS_UNIT

Prompt Table: SP_BU_PC_NONVW

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

Default Value: PROJECT.PROJECT_ID

Prompt Table: PROJ_SCRTY_VW

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

Prompt Table: PROJ_ACT_SRCH

4 RESOURCE_ID Character(40) VARCHAR2(40) NOT NULL Transaction ID
5 ANALYSIS_TYPE Character(3) VARCHAR2(3) NOT NULL Analysis Type
6 INTFC_LINE_NUM Number(8,0) INTEGER NOT NULL Interface Line Number
7 BUSINESS_UNIT_AM Character(5) VARCHAR2(5) NOT NULL AM Business Unit
8 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID
9 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification

Prompt Table: %EDITTABLE

10 INCENTIVE_ID Character(10) VARCHAR2(10) NOT NULL Investment Incentive ID
11 GROUP_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Group Asset ID
12 PARENT_ID Character(12) VARCHAR2(12) NOT NULL Parent ID
13 PARENT_DESCR Character(30) VARCHAR2(30) NOT NULL Parent Asset Description
14 BOOK Character(10) VARCHAR2(10) NOT NULL Asset Book Name
15 CATEGORY Character(5) VARCHAR2(5) NOT NULL Asset Category
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
18 COST_TYPE_PROJ_RES Character(1) VARCHAR2(1) NOT NULL Cost Type via Projects
19 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
20 ANALYSIS_GROUP Character(5) VARCHAR2(5) NOT NULL Analysis Group

Prompt Table: SP_AN_GRP_NONVW

21 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
22 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
23 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
24 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
25 GROUP_ASSET_FLAG Character(1) VARCHAR2(1) NOT NULL Group Asset Flag
G=Group Asset
M=Group Member
N=None
26 TRANS_DT Date(10) DATE Transaction Date
27 ACCOUNTING_DT Date(10) DATE The accounting entry construction date for a given transaction (a generic field that crosses multiple
28 IN_SERVICE_DT Date(10) DATE In Service Date
29 RETIREMENT_DT Date(10) DATE Projected Retirement Date
30 RETIRE_CONVENTION Character(2) VARCHAR2(2) NOT NULL Retirement Convention
31 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

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

Default Value: O

33 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

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

Prompt Table: ASSET

39 TRADE_IN_DTTM_STMP DateTime(26) TIMESTAMP Trade In DateTime Stamp
40 TRADE_IN_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Trade In Amount
41 GAIN_LOSS Signed Number(28,3) DECIMAL(26,3) NOT NULL Retirement Gain/Loss
42 CASH Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Received/Paid
43 TRADE_IN_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Trade In value
44 NON_LIKE_KIND Signed Number(28,3) DECIMAL(26,3) NOT NULL FMV Non Like Kind Property
45 LIABILITY_ASSUMED Signed Number(28,3) DECIMAL(26,3) NOT NULL Liabilities Assumed
46 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
47 TXN_PROCEEDS Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Proceeds
48 TXN_REMOVAL_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Removal Cost
49 TXN_CASH Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Cash
50 TXN_TRADE_IN_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Trade-In Value
51 TXN_NON_LIKE_KIND Signed Number(28,3) DECIMAL(26,3) NOT NULL FMV Non Like-Kind
52 TXN_LIAB_ASSUMED Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Liability Assumed
53 TXN_CURRENCY_CD2 Character(3) VARCHAR2(3) NOT NULL Transaction Currency Display
54 RATE_MULT_2 Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
55 RATE_DIV_2 Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
56 TAGGABLE_SW Character(1) VARCHAR2(1) NOT NULL Taggable Asset
N=Non Taggable Asset
Y=Taggable Asset
57 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
58 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
59 REPLACE_SW Character(1) VARCHAR2(1) NOT NULL Replacement Asset
60 REPLACE_ASSET_ID Character(12) VARCHAR2(12) NOT NULL Replaced Asset ID
61 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

62 CUSTODIAN Character(30) VARCHAR2(30) NOT NULL Custodian
63 INTFC_ID Number(8,0) INTEGER NOT NULL Interface ID
64 DESCR Character(30) VARCHAR2(30) NOT NULL Description
65 DEFAULT_PROFILE_SW Character(1) VARCHAR2(1) NOT NULL Default Books from Profile
66 BK_CAP_THRSHLD_ID Character(10) VARCHAR2(10) NOT NULL Threshold ID
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
68 INTFC_TYPE Character(3) VARCHAR2(3) NOT NULL Trans Load Type
69 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
70 TRANS_CODE Character(5) VARCHAR2(5) NOT NULL Transaction Code
71 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

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

Y/N Table Edit

Default Value: Y

74 BOOK_EFFDT Date(10) DATE Book Effective Date
75 LOCATION_EFFDT Date(10) DATE Location Effective Date
76 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
77 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name
78 MODEL Character(30) VARCHAR2(30) NOT NULL Model
79 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
80 ASSET_ALLOC_QTY Signed Number(17,2) DECIMAL(15,2) NOT NULL Asset Allocation Basis Qty
81 ASSET_ALLOC_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Asset Allocation Basis Amt
82 RESOURCE_QUANTITY Signed Number(16,2) DECIMAL(14,2) NOT NULL Quantity
83 RESOURCE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Amount
84 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
85 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
86 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

Prompt Table: OPERUNT_BUGL_VW

87 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

Prompt Table: PRODUCT_BUGL_VW

88 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

Prompt Table: FUND_BUGL_VW

89 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

Prompt Table: CLASSCF_BUGL_VW

90 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

Prompt Table: PROGRAM_BUGL_VW

91 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

Prompt Table: BUD_REF_BUGL_VW

92 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

Prompt Table: AFFILIATE_VW

93 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

Prompt Table: %EDIT_INTRA01

94 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

Prompt Table: %EDIT_INTRA02

95 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

Prompt Table: CF1_BUGL_VW

96 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

Prompt Table: CF2_BUGL_VW

97 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

Prompt Table: CF3_BUGL_VW

98 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
99 DEPTID_PC_AM_DEFN Character(10) VARCHAR2(10) NOT NULL Deptid via PC_AM_DEFN table
100 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
101 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
102 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
103 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
104 RT_EFFDT Date(10) DATE The effective date for a given item of market rate data.
105 TXN_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Cost
106 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
107 CRITERIA_ID Character(10) VARCHAR2(10) NOT NULL Selection Criteria ID
108 DEPR_STATUS Character(1) VARCHAR2(1) NOT NULL Depreciation Status
D=Depreciable
N=Non Depreciable
109 REGULATION Character(1) VARCHAR2(1) NOT NULL Depreciation Regulation
A=Accelerated Cost Recovery Sys
D=Asset Depreciation Range
F=Conventional/Facts& Circumstan
G=Guideline
M=Modified Accel Cost Recovery
110 RECOVERY_LIFE Number(4,1) DECIMAL(3,1) NOT NULL Recovery Life
111 RECOVERY_SUBTYPE Character(2) VARCHAR2(2) NOT NULL Recovery SubType
LI=Low-Income Housing
PU=Public Utility Property
112 CONVENTION Character(2) VARCHAR2(2) NOT NULL Book Depreciation Convention or Cost Convention

Prompt Table: CONV_SETID_VW

113 METHOD Character(2) VARCHAR2(2) NOT NULL Method
DL=DB w/SL By Limit%
DX=Declining Balance w/SL
F1=France Derogatory Balance
FL=Flat Rate %
G1=Germany Staffel Method
J1=Japan- Tangible/Declining Bal
J2=Japan- Tangible/Strt Line
J3=Japan- Intangible/Strt Line
J4=Japan - Lease Depreciation
J5=Japan-Changes DB to SL
J6=Japan - Changes DB to SL 250
J7=Japan-Tangible/Strt Line Rev
J8=Japan - Changes DB to SL 200
J9=JPN - Changes 200/250 DB to SL
JE=Japan - Extended/Strt Line
MN=Manual Depreciation
PC=Declining Balance
SC=Depreciation Schedule
SL=Straight Line
SP=Straight Line Percent
SY=Sum of the Years
UD=User Defined Method
UP=Units of Production
114 CALCULATION_TYPE Character(1) VARCHAR2(1) NOT NULL Calculation Type
L=Life-to-Date
R=Remaining Value
115 DEPR_PERCENT Number(10,8) DECIMAL(9,8) NOT NULL Depreciation Percent
116 DB_PERCENT Number(16,14) DECIMAL(15,14) NOT NULL Declining Balance Percent
117 DEPR_SCHED_CD Character(10) VARCHAR2(10) NOT NULL Schedule Code

Prompt Table: DEPR_SCHED_TBL

118 DEPR_LIMIT_CD Character(10) VARCHAR2(10) NOT NULL Depreciation Limit Code

Prompt Table: DEPR_LIM_TBL

119 LIFE Number(4,0) SMALLINT NOT NULL Useful Life
120 TAX_CLASS_AM Character(6) VARCHAR2(6) NOT NULL Guideline Tax Class

Prompt Table: TAX_CLASS_AM

121 PROPERTY_CD Character(1) VARCHAR2(1) NOT NULL Property Code
1=1245 Property
2=1250 Property
122 PROPERTY_TYPE Character(1) VARCHAR2(1) NOT NULL Property Type
P=Personal
R=Non-Residential Real
S=Residential Real
123 SALVAGE_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Salvage Value
124 SALVAGE_PCT Number(16,14) DECIMAL(15,14) NOT NULL Salvage Percentage
125 LIFE_END_DT Date(10) DATE Useful Life End Date
126 DEPR_LOW_LIMIT Signed Number(28,3) DECIMAL(26,3) NOT NULL Depr Low Limit Amount
127 COST_BASIS_LIMIT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cost Basis Limit
128 DEPR_AVG_OPTION Character(1) VARCHAR2(1) NOT NULL Depreciation Averaging Option
M=Monthly Averaging
N=No Averaging
Y=Yearly Averaging
129 UOP_ID Character(10) VARCHAR2(10) NOT NULL Units of Production ID

Prompt Table: UOP_TBL

130 INCLUDE_CAP_GAINS Character(1) VARCHAR2(1) NOT NULL CGT Applicable

Y/N Table Edit

131 UD_METHOD_ID Character(5) VARCHAR2(5) NOT NULL Method ID

Prompt Table: UD_METHOD

132 ACCOUNTING_MTHD Character(3) VARCHAR2(3) NOT NULL Accounting Method
ALL=Allowance
EXP=Expense
RES=Reserve
133 AM_BK_UD_CHAR1 Character(10) VARCHAR2(10) NOT NULL User Defined Char Field1
134 AM_BK_UD_CHAR2 Character(10) VARCHAR2(10) NOT NULL User Defined Char Field2
135 AM_BK_UD_DATE1 Date(10) DATE User Defined Date Field1
136 AM_BK_UD_NUM1 Number(19,3) DECIMAL(18,3) NOT NULL User Defined Number Field1
137 AM_BK_UD_NUM2 Number(19,3) DECIMAL(18,3) NOT NULL User Defined Number Field2
138 FUTURE_DEPR_YEARS Number(3,0) SMALLINT NOT NULL No of Future Depreciation Year
139 INCREASE_RATE Signed Number(11,6) DECIMAL(9,6) NOT NULL Increased Rate
140 DEPR_BONUS_PCT Number(6,2) DECIMAL(5,2) NOT NULL Depreciation Bonus Percent
141 DEPR_BONUS_SW Character(3) VARCHAR2(3) NOT NULL Resolution Date Description -------------- ------- ------------------ 753343 03/13/08 New depreciation bonus:Stimulus Act is added.
0=None
1=Section 101 - Federal
2=New York Liberty Zone
3=Section 101 - Local
4=Gulf Opportunity Zone
5=Stimulus Act
6=American Recovery&Reinvestment
142 ADJUST_CONVENTION Character(2) VARCHAR2(2) NOT NULL Adjustment Convention