CA_WC_PRCADJ_VW

(SQL View)
 Index Back

Undocumented Price Adjustments

A view that lists the contracts which have a difference between the total billing and total revenue that has not been documented

SELECT HDR.CONTRACT_NUM ,HDR.BUSINESS_UNIT ,HDR.DESCR AS CONTRACT_DESCRIPTION ,HDR.CONTRACT_TYPE ,HDR.CONTRACT_ADMIN ,HDR.SOLD_TO_CUST_ID ,CUST.NAME1 AS CUSTOMER_NAME ,HDR.CURRENCY_CD ,HDR.CONTRACT_TOTAL_BIL - HDR.CONTRACT_TOTAL_REV ,%Coalesce(SUM(PRCADJ.AMOUNT) ,0) FROM PS_SET_CNTRL_REC SCNTL ,PS_CUSTOMER CUST ,PS_CA_CONTR_HDR HDR LEFT OUTER JOIN PS_CA_PRC_ADJ_DTL PRCADJ ON ( HDR.CONTRACT_NUM = PRCADJ.CONTRACT_NUM AND (PRCADJ.CHG_STATUS = ' ' OR PRCADJ.CHG_STATUS = 'C')) WHERE HDR.CA_PROC_STATUS = 'A' AND SCNTL.SETCNTRLVALUE = HDR.BUSINESS_UNIT AND SCNTL.RECNAME = 'CUSTOMER' AND SCNTL.SETID = CUST.SETID AND CUST.CUST_ID = HDR.SOLD_TO_CUST_ID GROUP BY HDR.CONTRACT_NUM, HDR.BUSINESS_UNIT, HDR.DESCR, HDR.CONTRACT_TYPE, HDR.CONTRACT_ADMIN, HDR.SOLD_TO_CUST_ID, CUST.NAME1, HDR.CURRENCY_CD, HDR.CONTRACT_TOTAL_BIL, HDR.CONTRACT_TOTAL_REV HAVING HDR.CONTRACT_TOTAL_BIL - HDR.CONTRACT_TOTAL_REV - %Coalesce(SUM(PRCADJ.AMOUNT),0) <> 0

  • Related Language Record: CA_WC_PRCADJ_LG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: SP_BU_CA_NONVW

    3 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    4 CONTRACT_TYPE Character(15) VARCHAR2(15) NOT NULL Contract Type

    Prompt Table: CA_TYPE_TBL

    5 CONTRACT_ADMIN Character(40) VARCHAR2(40) NOT NULL Contract Administrator defined on CABU.

    Prompt Table: MEMBER_VW

    6 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

    Prompt Table: CUST_SLD_VW

    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    9 CA_PRC_ADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Price Adjustment
    10 CA_PRC_DOC_ADJ_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Documented Price Adjustment