SPF_INV_MNG_VW

(SQL View)
 Index Back

Manage Invoice Hdr View

Enterprise Manage Invoice Header View 01302009 SM ICE# 1834943000 Fetching NAME1 from VENDOR rather than VENDOR_ADDR record.

SELECT A.BUSINESS_UNIT , A.INVOICE_ID , A.SP_SERVICE_METHOD , A.SP_VNDR_INTERNAL , A.VMS_MANAGED , A.INVOICE_DT , A.INVOICE_STATUS , A.VENDOR_ID , VI.NAME1 , A.VNDR_LOC , A.ADDRESS_SEQ_NUM , VI.DESCR AS VNDR_ADDR_DESCR , A.REMIT_VENDOR , VR.NAME1 AS REMIT_TO_NAME , A.REMIT_LOC , A.REMIT_ADDR_SEQ_NUM , VR.DESCR AS REMIT_ADDR_DESCR , A.PYMNT_TERMS_CD , B.DESCRSHORT , A.ROW_CREATE_USER , A.ROW_CREATE_DATE , A.GEN_PROCESS_INST , V.INV_LINE_AMT , V.TAX_AMT , V.INV_EXP_AMT , V.TOTAL_AMT , V.SP_MSP_FEE , V.SP_MSP_TOTAL , V.INVOICE_AMOUNT , V.CURRENCY_CD , V.INV_LINE_AMT_BSE , V.TAX_AMT_BSE , V.INV_EXP_AMT_BSE , V.TOTAL_AMT_BSE , V.SP_MSP_FEE_BSE , V.SP_MSP_TOTAL_BSE , V.INVOICE_AMOUNT_BSE , V.CURRENCY_CD_BASE FROM PS_SPF_INVOICE_HDR A LEFT OUTER JOIN PS_SPA_INV_APRM2_V V ON V.INVOICE_ID = A.INVOICE_ID AND V.BUSINESS_UNIT = A.BUSINESS_UNIT , PS_PYMT_TRMS_HDR B , PS_VENDOR_ADDR_VW3 VI , PS_VENDOR_ADDR_VW3 VR WHERE A.PYMNT_TERMS_CD = B.PYMNT_TERMS_CD AND B.SETID = ( SELECT R.SETID FROM PS_SET_CNTRL_GROUP R WHERE R.SETCNTRLVALUE = A.BUSINESS_UNIT AND R.REC_GROUP_ID = 'FS_14') AND VI.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_GROUP S WHERE S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_38') AND A.VENDOR_ID = VI.VENDOR_ID AND A.ADDRESS_SEQ_NUM = VI.ADDRESS_SEQ_NUM AND %EffdtCheck(VENDOR_ADDR_VW3 V1, VI, A.INVOICE_DT) AND VI.SETID = VR.SETID AND A.REMIT_VENDOR = VR.VENDOR_ID AND A.REMIT_ADDR_SEQ_NUM = VR.ADDRESS_SEQ_NUM AND %EffdtCheck(VENDOR_ADDR_VW3 V2, VR, A.INVOICE_DT)

  • Related Language Record: SPF_INV_MNG_LV

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
    3 SP_SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Type classified as Resource Based or Service Based
    D=Deliverable
    R=Resource
    4 SP_VNDR_INTERNAL Character(1) VARCHAR2(1) NOT NULL Vendor Internal or External type
    N=N
    Y=Yes
    5 VMS_MANAGED Character(1) VARCHAR2(1) NOT NULL VMS Managed flag indicating if the work order is being managed by a Vendor Managed Service provider
    B=Default from Business Unit
    N=No
    Y=Yes
    6 INVOICE_DT Date(10) DATE Invoice Date
    7 INVOICE_STATUS Character(2) VARCHAR2(2) NOT NULL Invoice Status
    0=Submitted to Enterprise
    1=Generated
    10=Canceled
    11=Adjusted
    2=Approved
    3=Processed
    4=Paid
    5=Submitted
    6=Denied
    7=Submitted to Supplier
    8=Approved by Supplier
    9=Denied by Supplier
    8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    9 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    10 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    11 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    14 REMIT_TO_NAME Character(40) VARCHAR2(40) NOT NULL Remit To Supplier Name
    15 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location
    16 REMIT_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Remitting Location
    17 DESCR_2 Character(30) VARCHAR2(30) NOT NULL Description
    18 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
    19 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    20 ROW_CREATE_USER Character(30) VARCHAR2(30) NOT NULL Row Added By
    21 ROW_CREATE_DATE DateTime(26) TIMESTAMP Row Creation Date Time
    22 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
    23 INV_LINE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Amount
    24 TAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount
    25 INV_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount
    26 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
    27 SP_MSP_FEE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee
    28 SP_MSP_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee
    29 INVOICE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
    30 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    31 INV_LINE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Invoice Line Amount
    32 TAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount (base)
    33 INV_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount for Base Currency
    34 TOTAL_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Estimated Total
    35 SP_MSP_FEE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee (Base Currency)
    36 SP_MSP_TOTAL_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee (Base Currency)
    37 INVOICE_AMOUNT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount in Base Currency
    38 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency