EM_AF_LEVEL_VW

(SQL View)
 Index Back

eSettlements Approval Views

eSettlements Approval Views

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , C.APPROVAL_SEQ , C.GROSS_AMT_MIN , C.GROSS_AMT_MAX , %Round(%DecDiv(%DecMult(A.gross_amt, D.rate_mult), D.rate_div), E.DECIMAL_POSITIONS) , B.CURRENCY_CD FROM PS_VOUCHER A , PS_EM_BUY_REG_TBL B , PS_EM_BUYER_APPR C , PS_RT_DFLT_VW D , PS_CURRENCY_CD_TBL E WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.TXN_CURRENCY_CD = D.FROM_CUR AND B.CURRENCY_CD = D.TO_CUR AND A.RT_TYPE = D.RT_TYPE AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_RT_DFLT_VW DD WHERE DD.FROM_CUR = D.FROM_CUR AND DD.TO_CUR = D.TO_CUR AND DD.RT_TYPE = D.RT_TYPE AND DD.EFFDT <= A.INVOICE_DT) AND E.CURRENCY_CD = B.CURRENCY_CD AND E.EFFDT = ( SELECT MAX(EE.EFFDT) FROM PS_CURRENCY_CD_TBL EE WHERE EE.CURRENCY_CD = E.CURRENCY_CD AND EE.EFFDT <= A.INVOICE_DT) AND C.APPROVAL_SEQ <= ( SELECT CC.APPROVAL_SEQ FROM PS_EM_BUYER_APPR CC WHERE CC.BUSINESS_UNIT = C.BUSINESS_UNIT AND CC.GROSS_AMT_MAX = ( SELECT MAX(CCC.GROSS_AMT_MAX) FROM PS_EM_BUYER_APPR CCC WHERE CCC.BUSINESS_UNIT = C.BUSINESS_UNIT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
3 APPROVAL_SEQ Number(3,0) SMALLINT NOT NULL Approval Sequence
4 GROSS_AMT_MIN Signed Number(28,3) DECIMAL(26,3) NOT NULL Min Gross Invoice Approval Amt
5 GROSS_AMT_MAX Signed Number(28,3) DECIMAL(26,3) NOT NULL Max Gross Invoice Approval Amt
6 GROSS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Gross Invoice Amount
7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code