CA_AP_RECOG_VW

(SQL View)
 Index Back

Recognized Revenue by Contract


SELECT REV.CONTRACT_NUM ,SUM(REV.AMOUNT) ,REV.CURRENCY_CD FROM( SELECT ACCT.CONTRACT_NUM ,SUM(%Round(%decdiv(%decmult(%decmult(ACCT.AMOUNT_BSE, - 1), RT.RATE_MULT), RT.RATE_DIV), RT.DECIMAL_POSITIONS)) AS AMOUNT ,INST.CURRENCY_CD FROM PS_CA_ACCTG_LINE ACCT ,PS_CA_CURR_RATE_VW RT ,PS_INSTALLATION_CA INST WHERE ACCT.DST_USE IN ('REV','DIS') AND RT.RT_TYPE = INST.RT_TYPE AND RT.FROM_CUR = ACCT.BASE_CURRENCY AND RT.TO_CUR = INST.CURRENCY_CD GROUP BY ACCT.CONTRACT_NUM,INST.CURRENCY_CD UNION ALL SELECT ACCT.CONTRACT_NUM , SUM(%Round(%decdiv(%decmult(%decmult(ACCT.AMOUNT_BSE, - 1), RT.RATE_MULT), RT.RATE_DIV), RT.DECIMAL_POSITIONS)) AS AMOUNT , INST.CURRENCY_CD FROM PS_CA_ACCTG_LN_PC ACCT ,PS_CA_CURR_RATE_VW RT ,PS_INSTALLATION_CA INST WHERE ACCT.DST_USE ='REV' AND RT.RT_TYPE = INST.RT_TYPE AND RT.FROM_CUR = ACCT.CURRENCY_CD AND RT.TO_CUR = INST.CURRENCY_CD GROUP BY ACCT.CONTRACT_NUM, INST.CURRENCY_CD UNION ALL SELECT XRF.CONTRACT_NUM , SUM(%Round(%decdiv(%decmult(XRF.NET_EXTENDED_AMT, RT.RATE_MULT), RT.RATE_DIV), RT.DECIMAL_POSITIONS)) AS AMOUNT ,INST.CURRENCY_CD FROM PS_CA_BP_XREF XRF ,PS_CA_DETAIL DTL ,PS_CA_CURR_RATE_VW RT ,PS_INSTALLATION_CA INST WHERE XRF.CONTRACT_NUM = DTL.CONTRACT_NUM AND XRF.CONTRACT_LINE_NUM =DTL.CONTRACT_LINE_NUM AND DTL.REV_RECOG_METHOD = '5' AND DTL.CHG_STATUS='C' AND DTL.CA_LINE_STATUS <>'X' AND XRF.XREF_STATUS<>'DEL' AND RT.RT_TYPE = INST.RT_TYPE AND RT.FROM_CUR = XRF.BI_CURRENCY_CD AND RT.TO_CUR = INST.CURRENCY_CD GROUP BY XRF.CONTRACT_NUM,INST.CURRENCY_CD ) REV GROUP BY REV.CONTRACT_NUM,REV.CURRENCY_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
2 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
3 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code