CM_CST_RATE_VW

(SQL View)
 Index Back

Order Rate and Rate Type


SELECT T.BUSINESS_UNIT , T.INV_ITEM_ID , T.DT_TIMESTAMP , T.SEQ_NBR , T.SOURCE_BUS_UNIT , T.ORDER_NO , O.RT_TYPE , R.RATE_MULT , R.RATE_DIV , C.DECIMAL_POSITIONS FROM PS_CM_SHIPONBEHALF S , PS_ORD_HEADER O , PS_CURRENCY_CD_TBL C , PS_RT_RATE_TBL R , PS_RT_INDEX_TBL I , PS_TRANSACTION_INV T , PS_BUS_UNIT_TBL_GL D WHERE S.BUSINESS_UNIT = T.BUSINESS_UNIT AND S.INV_ITEM_ID = T.INV_ITEM_ID AND S.DT_TIMESTAMP = T.DT_TIMESTAMP AND S.SEQ_NBR = T.SEQ_NBR AND S.TRANSACTION_GROUP = '034' AND T.SOURCE_BUS_UNIT = O.BUSINESS_UNIT AND T.ORDER_NO = O.ORDER_NO AND D.BUSINESS_UNIT = S.CM_BU_DESTIN_GL AND S.BASE_CURRENCY <> D.BASE_CURRENCY AND S.BASE_CURRENCY = R.FROM_CUR AND D.BASE_CURRENCY = R.TO_CUR AND O.RT_TYPE = R.RT_TYPE AND R.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_RT_DFLT_VW F WHERE F.FROM_CUR = R.FROM_CUR AND F.TO_CUR = R.TO_CUR AND F.RT_TYPE = R.RT_TYPE AND F.EFFDT <= T.TRANSACTION_DATE) AND R.TO_CUR = C.CURRENCY_CD AND C.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_CURRENCY_CD_TBL E WHERE C.CURRENCY_CD = E.CURRENCY_CD AND E.EFFDT <= T.TRANSACTION_DATE) AND R.RT_RATE_INDEX = I.RT_RATE_INDEX AND R.TERM = 0 AND I.DEFAULT_INDEX = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 DT_TIMESTAMP DateTime(26) TIMESTAMP Date Timestamp
4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
5 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
6 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
7 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
8 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
9 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
10 DECIMAL_POSITIONS Number(2,0) SMALLINT NOT NULL Decimal Positions