CA_PV_OSUM_VW

(SQL View)
 Index Back

Main view for Online PSummary

This is the main view for the Online page summary exception for Paid Voucher. Uses CA_PV_ALL_VW and CA_PV_SM_VW

SELECT PSUM.BUSINESS_UNIT , PSUM.CONTRACT_NUM , PSUM.CONTRACT_LINE_NUM , PSUM.SOLD_TO_CUST_ID , PSUM.CONTRACT_CURRENCY , PSUM.LIMIT_AMT_BIL , PSUM.LIMIT_AMT_REV , PSUM.HOLD_UNPAID_BI , PSUM.HOLD_UNPAID_REV , ( SELECT CONTRACT_AMT FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='OLT' AND CA_PV_VWFLAG='PRCS' ) , ( SELECT CONTRACT_AMT FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='ROL' AND CA_PV_VWFLAG='PRCS' ) , ( SELECT SUM(CONTRACT_AMT) FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND CA_PV_VWFLAG='CMTD' AND ((ANALYSIS_TYPE ='BLD') OR(ANALYSIS_TYPE='BIL' AND BI_DISTRIB_STATUS='W')) GROUP BY PSUM.BUSINESS_UNIT ,PSUM.CONTRACT_NUM, PSUM.CONTRACT_LINE_NUM ) , ( SELECT SUM(CONTRACT_AMT ) FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND CA_PV_VWFLAG='CMTD' AND ( (PSUM.SEPARATE_BIL_REV='Y' AND ANALYSIS_TYPE = 'REV' ) OR (PSUM.SEPARATE_BIL_REV<>'Y' AND ANALYSIS_TYPE = 'BIL' AND GL_DISTRIB_STATUS IN ('G','D') ) ) GROUP BY PSUM.BUSINESS_UNIT ,PSUM.CONTRACT_NUM, PSUM.CONTRACT_LINE_NUM ) , ( SELECT CONTRACT_AMT FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='BIL' AND CA_PV_VWFLAG='UNPD' ) , ( SELECT CONTRACT_AMT FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='REV' AND CA_PV_VWFLAG='UNPD' ) , ( SELECT SUM(CONTRACT_AMT) FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='BIL' AND CA_PV_VWFLAG='PRCS' GROUP BY PSUM.BUSINESS_UNIT ,PSUM.CONTRACT_NUM, PSUM.CONTRACT_LINE_NUM ) , ( SELECT SUM(CONTRACT_AMT ) FROM PS_CA_PV_ALL_VW WHERE BUSINESS_UNIT =PSUM.BUSINESS_UNIT AND CONTRACT_NUM =PSUM.CONTRACT_NUM AND CONTRACT_LINE_NUM=PSUM.CONTRACT_LINE_NUM AND ANALYSIS_TYPE ='REV' AND CA_PV_VWFLAG='PRCS' GROUP BY PSUM.BUSINESS_UNIT ,PSUM.CONTRACT_NUM, PSUM.CONTRACT_LINE_NUM ) , PSUM.END_DT , PSUM.DESCR , PSUM.SEPARATE_BIL_REV , PSUM.START_DT_2 , PSUM.BILL_TO_CUST_ID , PSUM.LOC_REFERENCE_ID , PSUM.CA_STATUS , PSUM.CONTRACT_TYPE , PSUM.REGION_CD , PSUM.CONTRACT_ADMIN , PSUM.GM_METHOD_PAYMENT , PSUM.NAME1_CUST , PSUM.SPNSR_TYPE FROM PS_CA_PV_SUM_VW PSUM

  • Related Language Record: CA_PV_SUM_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    3 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
    4 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
    5 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    6 LIMIT_AMT_BIL Signed Number(28,3) DECIMAL(26,3) NOT NULL Funded/Billing Limit Amount
    7 LIMIT_AMT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Revenue Limit Amount
    8 HOLD_UNPAID_BI Character(1) VARCHAR2(1) NOT NULL Hold Billing on Unpaid Cost

    Y/N Table Edit

    9 HOLD_UNPAID_REV Character(1) VARCHAR2(1) NOT NULL Hold Revenue on Unpaid Cost

    Y/N Table Edit

    10 PAID_OLT Signed Number(31,3) DECIMAL(29,3) NOT NULL Paid OLT
    11 PAID_ROL Signed Number(31,3) DECIMAL(29,3) NOT NULL Paid ROL
    12 COMMITTED_AMT_BI Signed Number(28,3) DECIMAL(26,3) NOT NULL Committed Billing
    13 COMMITTED_AMT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Committed Revenue
    14 UNPAID_AMT_BIL Signed Number(28,3) DECIMAL(26,3) NOT NULL Unpaid Billing
    15 UNPAID_AMT_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Unpaid Revenue
    16 PAID_BIL Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Billable
    17 PAID_REV Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Revenue
    18 END_DT Date(10) DATE End Date
    19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    20 SEPARATE_BIL_REV Character(1) VARCHAR2(1) NOT NULL Flag used by Governement Contracts to separate Billing and Revenue transactions

    Y/N Table Edit

    21 START_DT_2 Date(10) DATE Start Date
    22 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
    23 LOC_REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Letter of Credit ID
    24 CA_STATUS Character(24) VARCHAR2(24) NOT NULL Contract Status
    25 CONTRACT_TYPE Character(15) VARCHAR2(15) NOT NULL Contract Type
    26 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.
    27 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.
    28 GM_METHOD_PAYMENT Character(3) VARCHAR2(3) NOT NULL Method of Payment
    CST=Cost Reimbursable
    LOC=Letter of Credit
    29 NAME1_CUST Character(40) VARCHAR2(40) NOT NULL Customer Name1
    30 SPNSR_TYPE Character(4) VARCHAR2(4) NOT NULL Sponsor Type