PC_AM_DEFN3_VW

(SQL View)
 Index Back

PC AM Exp: Express view

Views combination of activity level rules, asset definition and the relationship between assets and a project.

SELECT MAX(A.BUSINESS_UNIT) , MAX(A.PROJECT_ID) , B.BUSINESS_UNIT_AM , B.ASSET_ID , 0 , 'U' , MAX(B.PROFILE_ID) , MAX(B.DESCR) , MAX(B.TAG_NUMBER) , MAX(B.LOCATION) , MAX(B.CATEGORY) , MAX(B.SERIAL_ID) , MAX(B.MANUFACTURER) , MAX(B.GROUP_ASSET_ID) , MAX(B.PARENT_ID) , MAX(B.PARENT_DESCR) , MAX(B.PARENT_ASSET_SW) , MAX(B.QUANTITY) , MAX(B.IN_SERVICE_DT) , MAX(B.DEPTID) , MAX(B.ASSET_OPTION) , MAX(B.INTFC_STATUS) , MAX(B.INTFC_TYPE) , MAX(B.BOOK_PROCESSING_SW) , MAX(B.GROUP_ASSET_FLAG) , MAX(B.LOCATION_EFFDT) , MAX(B.TRANS_DT) , MAX(B.ACCOUNTING_DT) , MAX(B.BOOK_EFFDT) , MAX(B.TAGGABLE_SW) , MAX(B.DEFAULT_PROFILE_SW) , MAX(B.OPEN_TRANS_ID) , MAX(B.AM_DISTRIB_STATUS) , MAX(B.REPLACE_SW) , MAX(ACQUISITION_CD) , MAX(LOAD_STATUS) , MAX(SYSTEM_SOURCE) , MAX(APPROVAL_SW) , MAX(DISPOSAL_CODE) , MAX(RETIREMENT_TYPE) , MAX(RETIREMENT_STATUS) 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) AND B.AM_DISTRIB_STATUS <> 'I' GROUP BY B.BUSINESS_UNIT_AM, B.ASSET_ID

  • Related Language Record: PC_AM_DEFN3_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    3 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

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

    Default Value: NEXT

    Prompt Table: PC_AM_DFNEXP_VW

    5 ROW_NUMBER_5 Number(5,0) INTEGER NOT NULL Row Number

    Default Value: 0

    6 DEFN_FLAG Character(1) VARCHAR2(1) NOT NULL Definition Flag

    Default Value: I

    7 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID

    Prompt Table: PROFILE_PCAM_VW
    Set Control Field: BUSINESS_UNIT_AM

    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
    10 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL
    Set Control Field: BUSINESS_UNIT_AM

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

    Prompt Table: CATEGORY_TBL
    Set Control Field: BUSINESS_UNIT_AM

    12 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
    13 MANUFACTURER Character(60) VARCHAR2(60) NOT NULL Manufacturer Name

    Prompt Table: MANUF_FS_VW
    Set Control Field: BUSINESS_UNIT_AM

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

    Prompt Table: PC_AM_GROUP_VW

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

    Prompt Table: PARENT_ASSET_VW

    16 PARENT_DESCR Character(30) VARCHAR2(30) NOT NULL Parent Asset Description
    17 PARENT_ASSET_SW Character(1) VARCHAR2(1) NOT NULL Parent Asset
    N=No
    Y=Yes

    Default Value: N

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

    Default Value: 1

    19 IN_SERVICE_DT Date(10) DATE In Service Date

    Default Value: %DATE

    20 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: %EDIT_DEPT
    Set Control Field: BUSINESS_UNIT_AM

    21 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

    22 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

    23 INTFC_TYPE Character(3) VARCHAR2(3) NOT NULL Trans Load Type
    24 BOOK_PROCESSING_SW Character(1) VARCHAR2(1) NOT NULL Enable Book Processing
    N=No
    Y=Yes

    Default Value: N

    25 GROUP_ASSET_FLAG Character(1) VARCHAR2(1) NOT NULL Group Asset Flag
    G=Group Asset
    M=Group Member
    N=None
    26 LOCATION_EFFDT Date(10) DATE Location Effective Date
    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 BOOK_EFFDT Date(10) DATE Book Effective Date
    30 TAGGABLE_SW Character(1) VARCHAR2(1) NOT NULL Taggable Asset
    N=Non Taggable Asset
    Y=Taggable Asset
    31 DEFAULT_PROFILE_SW Character(1) VARCHAR2(1) NOT NULL Default Books from Profile

    Default Value: Y

    32 OPEN_TRANS_ID Number(10,0) DECIMAL(10) NOT NULL Open Transaction ID
    33 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

    34 REPLACE_SW Character(1) VARCHAR2(1) NOT NULL Replacement Asset

    Default Value: N

    35 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

    36 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

    37 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

    38 APPROVAL_SW Character(1) VARCHAR2(1) NOT NULL Auto Approval Status

    Default Value: Y

    39 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

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

    Default Value: O

    41 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