CA_PROD_ITEM_V4

(SQL View)
 Index Back

ESA Contract Product ID

Products without a UOM.

SELECT A.SETID , A.PRODUCT_ID , A.DESCR , A.PRICING_STRUCTURE , A.PRODUCT_KIT_FLAG , A.EFF_STATUS , A.REV_RECOG_METHOD , A.INV_ITEM_ID , A.PRODUCT_USE , A.THIRD_PARTY_FLG , A.RENEWABLE , A.RENEWAL_ACTION , A.PERCENTAGE , A.APPLIES_TO , A.CA_AP_TMPL_ID , A.CA_BP_TMPL_ID , A.BP_DTL_TMPL_ID , A.RNW_TEMPLATE_ID , A.TAX_TRANS_TYPE , A.TAX_TRANS_SUB_TYPE , A.PHYSICAL_NATURE , ' ' , 1 , '01' FROM PS_PROD_ITEM A WHERE A.PRODUCT_USE IN ('2', '3') AND NOT EXISTS ( SELECT 'X' FROM PS_PROD_UOM C WHERE C.SETID = A.SETID AND C.PRODUCT_ID = A.PRODUCT_ID )

  • Related Language Record: PROD_ITEMVLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    2 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 PRICING_STRUCTURE Character(4) VARCHAR2(4) NOT NULL Price Type
    AMT=Amount
    PCT=Percent
    RATE=Rate
    RCR=Recurring
    5 PRODUCT_KIT_FLAG Character(1) VARCHAR2(1) NOT NULL Product Kit
    6 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    7 REV_RECOG_METHOD Character(4) VARCHAR2(4) NOT NULL Revenue Recognition Method
    1=As Incurred
    2=Apportionment
    3=Percent Complete
    4=Milestone
    5=Billing Manages Revenue
    8 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    9 PRODUCT_USE Character(1) VARCHAR2(1) NOT NULL Field for distinguishing which products can use a product definition.
    1=General Use Excluding Contract
    2=Contracts Only
    3=General Use Including Contract
    10 THIRD_PARTY_FLG Character(1) VARCHAR2(1) NOT NULL Third Party Flag
    11 RENEWABLE Character(1) VARCHAR2(1) NOT NULL Renewable
    12 RENEWAL_ACTION Character(4) VARCHAR2(4) NOT NULL Renewal Action
    AUTO=Automatic
    MANL=Manual
    13 PERCENTAGE Signed Number(7,2) DECIMAL(5,2) NOT NULL Percentage
    14 APPLIES_TO Character(4) VARCHAR2(4) NOT NULL PSIRS-KK
    HDR=% of Total Contract Amount
    LINE=% of Contract Lines
    15 CA_AP_TMPL_ID Character(10) VARCHAR2(10) NOT NULL Revenue Plan Template

    Prompt Table: CA_AP_TMPL_VW

    16 CA_BP_TMPL_ID Character(10) VARCHAR2(10) NOT NULL Bill Plan Template

    Prompt Table: CA_BP_TMPL_VW

    17 BP_DTL_TMPL_ID Character(10) VARCHAR2(10) NOT NULL Bill Plan Detail Template ID

    Prompt Table: CA_BP_DTL_TMPL

    18 RNW_TEMPLATE_ID Character(10) VARCHAR2(10) NOT NULL Renewal Plan Template

    Prompt Table: CA_RNW_TEMPLATE

    19 TAX_TRANS_TYPE Character(1) VARCHAR2(1) NOT NULL Transaction Type
    1=Sale
    2=Rental
    3=Service
    4=Purchase
    5=Lease
    B=Return
    20 TAX_TRANS_SUB_TYPE Character(1) VARCHAR2(1) NOT NULL Transaction Sub Type
    1=Property
    2=Freight
    3=Service
    4=Rental or Lease
    5=Expense
    6=Misc
    N=None
    21 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
    G=Goods
    S=Services
    22 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage

    Prompt Table: PROD_UOM_VW

    23 QTY Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity
    24 CA_FEE_TYPE Character(2) VARCHAR2(2) NOT NULL Cost Plus Fee Type
    01=None
    02=Fixed Fee
    03=Award Fee
    04=Incentive Fee
    05=Other Fee