CA_DETAIL_FC_VW

(SQL View)
 Index Back

Contract Line for Rev Forecast

Excluding revenue recognition m

SELECT CD.CONTRACT_NUM , CD.CONTRACT_LINE_NUM , CD.ACCT_PLAN_ID , CD.BILL_PLAN_ID , CD.BPLAN_LN_NBR , CD.BOOK_CA_TO_DFR , CD.CURRENCY_CD , CD.DESCR , CD.CA_LINE_STATUS , CD.NET_AMOUNT_REV , CD.PRICING_STRUCTURE , CD.PRODUCT_ID , CD.PRODUCT_GROUP , CD.RENEWABLE , CD.REV_RECOG_METHOD , CD.ESTIMATED_START_DT , CD.ESTIMATED_COMP_DT , CD.ESTIMATED_AMOUNT , CD.CA_PROBABILITY , CD.LAST_DTTIME , HDR.BUSINESS_UNIT , BU.BUSINESS_UNIT_GL , HDR.SOLD_TO_CUST_ID , HDR.CONTRACT_TYPE , HDR.CA_STATUS , HDR.CA_PROC_STATUS , HDR.REGION_CD , HDR.CONTRACT_SIGN_DT , CD.CA_FEE_TYPE FROM PS_CA_DETAIL CD , PS_CA_CONTR_HDR HDR , PS_BUS_UNIT_TBL_CA BU WHERE CD.CONTRACT_NUM = HDR.CONTRACT_NUM AND HDR.BUSINESS_UNIT = BU.BUSINESS_UNIT AND CD.REV_RECOG_METHOD <> '5' AND CD.CA_TXN_SOURCE <> 'CRM' AND NOT EXISTS ( SELECT 'X' FROM PS_CA_ACCTPLAN AP WHERE AP.CONTRACT_NUM = CD.CONTRACT_NUM AND AP.ACCT_PLAN_ID = CD.ACCT_PLAN_ID AND AP.AP_STATUS IN ('DON','RIP','RQD','RVS'))

  • Related Language Record: CA_DETAIL_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

    Prompt Table: CA_CNTR_SRCH_VW

    2 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
    3 ACCT_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Revenue Recognition Plan

    Prompt Table: CA_ACCTPLAN

    4 BILL_PLAN_ID Character(10) VARCHAR2(10) NOT NULL Billing Plan ID
    5 BPLAN_LN_NBR Number(3,0) SMALLINT NOT NULL Bill Plan Line Number
    6 BOOK_CA_TO_DFR Character(1) VARCHAR2(1) NOT NULL Deferred revenue accounting

    Y/N Table Edit

    Default Value: N

    7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: BUS_UNIT_TBL_CA

    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 CA_LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Obligation status
    A=Active
    C=Cancelled
    P=Pending
    R=Renewed

    Default Value: A

    10 NET_AMOUNT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Revenue Amount
    11 PRICING_STRUCTURE Character(4) VARCHAR2(4) NOT NULL Price Type
    AMT=Amount
    PCT=Percent
    RATE=Rate
    RCR=Recurring
    12 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    13 PRODUCT_GROUP Character(10) VARCHAR2(10) NOT NULL Product Group
    14 RENEWABLE Character(1) VARCHAR2(1) NOT NULL Renewable

    Y/N Table Edit

    Default Value: N

    15 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
    16 ESTIMATED_START_DT Date(10) DATE Estimated Start Date
    17 ESTIMATED_COMP_DT Date(10) DATE Estimated Completion Date
    18 ESTIMATED_AMOUNT Number(27,3) DECIMAL(26,3) NOT NULL Estimated Amount SCP - Added new label - Estimated Cost
    19 CA_PROBABILITY Number(3,0) SMALLINT NOT NULL Probability
    20 LAST_DTTIME DateTime(26) TIMESTAMP Last Forecast Run
    21 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    22 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
    23 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
    24 CONTRACT_TYPE Character(15) VARCHAR2(15) NOT NULL Contract Type
    25 CA_STATUS Character(24) VARCHAR2(24) NOT NULL Contract Status
    26 CA_PROC_STATUS Character(1) VARCHAR2(1) NOT NULL CA_STATUS is a user defined status field that maps (many to 1) to the contract Processing Status field. Process Status indicates what change processing is available.
    A=Active
    C=Closed
    P=Pending
    27 REGION_CD Character(10) VARCHAR2(10) NOT NULL Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to.
    28 CONTRACT_SIGN_DT Date(10) DATE Contract Signed
    29 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