SPF_INV_LCYCL_V

(SQL View)
 Index Back

Life Cycle Invoice View

This view retrieves a collection of invoices for a particular requisition lifecycle. It shows the invoice line amount as well as the total amount for the entire invoice.

SELECT D.BUSINESS_UNIT , D.INVOICE_ID , D.INVOICE_LINE , W.WORK_ORDER_ID , H.SP_SERVICE_METHOD , H.SP_VNDR_INTERNAL , H.VMS_MANAGED , W.SP_BILL_METHOD , H.INVOICE_DT , H.INVOICE_STATUS , W.MRSC_PARENT_WOID , W.SUB_LINE , H.VENDOR_ID , V1.NAME1 , H.VNDR_LOC , H.ADDRESS_SEQ_NUM , V1.DESCR , H.REMIT_VENDOR , V2.NAME1 AS REMIT_VNDR_NAME , H.REMIT_LOC , H.REMIT_ADDR_SEQ_NUM , V2.DESCR AS REMIT_ADDR_DESCR , H.PYMNT_TERMS_CD , P.DESCRSHORT , H.ROW_CREATE_USER , D.INV_LINE_ADJAMT + D.INV_LINE_ADJEXP + D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT , T.INV_LINE_AMT , T.INV_EXP_AMT , T.TAX_AMT , T.TOTAL_AMT , T.SP_MSP_FEE , T.SP_MSP_TOTAL , D.INV_LN_ADJAMT_BSE + D.INV_LN_ADJEXP_BSE + D.ADJ_VAT_AMT_BSE + ADJ_SALETX_AMT_BSE , T.INV_LINE_AMT_BSE , T.INV_EXP_AMT_BSE , T.TAX_AMT_BSE , T.TOTAL_AMT_BSE , T.SP_MSP_FEE_BSE , T.SP_MSP_TOTAL_BSE , D.CURRENCY_CD , D.CURRENCY_CD_BASE FROM PS_SPF_INVOICE_HDR H , PS_SPF_INVOICE_DTL D , PS_SPF_WORDERREC W , PS_PAYTRMS_SPF_VW P , PS_VENDOR_ADDR_VW3 V1 , PS_VENDOR_ADDR_VW3 V2 , PS_SPA_INV_APRM2_V T WHERE H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.INVOICE_ID = D.INVOICE_ID AND D.BUSINESS_UNIT = W.BUSINESS_UNIT AND D.WORK_ORDER_ID = W.WORK_ORDER_ID AND H.PYMNT_TERMS_CD = P.PYMNT_TERMS_CD AND P.SETID = ( SELECT R.SETID FROM PS_SET_CNTRL_GROUP R WHERE R.SETCNTRLVALUE = H.BUSINESS_UNIT AND R.REC_GROUP_ID = 'FS_14') AND V1.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_GROUP S WHERE S.SETCNTRLVALUE = H.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_38') AND H.VENDOR_ID = V1.VENDOR_ID AND H.ADDRESS_SEQ_NUM = V1.ADDRESS_SEQ_NUM AND %EffdtCheck(VENDOR_ADDR_VW3 V3, V1, H.INVOICE_DT) AND V1.SETID = V2.SETID AND V2.VENDOR_ID = H.REMIT_VENDOR AND V2.ADDRESS_SEQ_NUM = H.REMIT_ADDR_SEQ_NUM AND %EffdtCheck(VENDOR_ADDR_VW3 V4, V2, H.INVOICE_DT) AND H.INVOICE_ID = T.INVOICE_ID (+) AND H.BUSINESS_UNIT = T.BUSINESS_UNIT (+)

  • Related Language Record: SPF_INV_LCYC_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 INVOICE_LINE Number(5,0) INTEGER NOT NULL Invoice Line
    4 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
    5 SP_SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Type classified as Resource Based or Service Based
    D=Deliverable
    R=Resource
    6 SP_VNDR_INTERNAL Character(1) VARCHAR2(1) NOT NULL Vendor Internal or External type
    N=N
    Y=Yes
    7 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
    8 SP_BILL_METHOD Character(1) VARCHAR2(1) NOT NULL Used in Service Based Workorders to identify the billing method.
    F=Fixed Amount
    M=Milestone
    P=Percentage
    R=Rate Based
    9 INVOICE_DT Date(10) DATE Invoice Date
    10 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
    11 MRSC_PARENT_WOID Character(15) VARCHAR2(15) NOT NULL Multi Resource Parent WOID
    12 SUB_LINE Character(1) VARCHAR2(1) NOT NULL Sub Line
    N=No
    Y=Yes
    13 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    14 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    15 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    16 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    19 REMIT_TO_NAME Character(40) VARCHAR2(40) NOT NULL Remit To Supplier Name
    20 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location
    21 REMIT_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Remitting Location
    22 DESCR_2 Character(30) VARCHAR2(30) NOT NULL Description
    23 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.
    24 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    25 ROW_CREATE_USER Character(30) VARCHAR2(30) NOT NULL Row Added By
    26 LINE_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Plus Tax
    27 INV_LINE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Amount
    28 INV_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount
    29 TAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount
    30 TOTAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount
    31 SP_MSP_FEE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee
    32 SP_MSP_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee
    33 LINE_TOTAL_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Plus Tax
    34 INV_LINE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Invoice Line Amount
    35 INV_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount for Base Currency
    36 TAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount (base)
    37 TOTAL_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount Base
    38 SP_MSP_FEE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee (Base Currency)
    39 SP_MSP_TOTAL_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee (Base Currency)
    40 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    41 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency