SPF_INV_EXP_VW

(SQL View)
 Index Back

Available Exp Lines to Invoice

This view fetches the Expense detail lines that are available for Invoicing

SELECT X.BUSINESS_UNIT , H.SHEET_ID , X.WORK_ORDER_ID , D.LINE_NBR , W.MRSC_PARENT_WOID , W.SUB_LINE , D.DISTRIB_LINE_NUM , W.SERVICE_METHOD , W.VENDOR_ID , W.VNDR_LOC , W1.VMS_SUPPLIER_ID , W1.VMS_LOCATION , W.SP_VNDR_INTERNAL , W.WO_CONID , N.NAME , H.CREATION_DT , ' ' , W.VENDOR_ID , W.VNDR_LOC , W.PROVIDER_CONTACT , N1.NAME , W.PO_ID , D.TRANS_DT , D.EXPENSE_TYPE , E.DESCR , D.MONETARY_AMOUNT , D.AMOUNT_BSE , D.MONETARY_AMOUNT , D.AMOUNT_BSE , D.CURRENCY_CD , D.CURRENCY_CD_BASE , D.PC_DISTRIB_STATUS FROM PS_SPA_SHEET_HDR H , PS_SPA_SHEET_LINE D , PS_EX_TYPES_TBL E , PS_SET_CNTRL_GROUP S , PS_SPA_EXP_XREF X , PS_SPF_WORDERREC W , PS_SPF_WORDERREC_1 W1 , PS_SPB_PERSON_TBL N , PS_SPB_PERSON_TBL N1 WHERE H.EXP_HDR_STATUS IN ('RAP', 'ADJ') AND D.SPA_BILLING_ACTION = 'B' AND D.TE_INVOICE_FLG = 'N' AND H.SHEET_ID = D.SHEET_ID AND D.SHEET_ID = X.SHEET_ID AND X.BUSINESS_UNIT = W.BUSINESS_UNIT AND X.WORK_ORDER_ID = W.WORK_ORDER_ID AND W.BUSINESS_UNIT = W1.BUSINESS_UNIT AND W.WORK_ORDER_ID = W1.WORK_ORDER_ID AND E.SETID = H.SETID AND E.EXPENSE_TYPE = D.EXPENSE_TYPE AND %EffdtCheck(EX_TYPES_TBL E1, E, H.CREATION_DT) AND E.EFF_STATUS = 'A' AND X.BUSINESS_UNIT = S.SETCNTRLVALUE AND S.REC_GROUP_ID = 'EX_02' AND E.SETID = S.SETID AND W.WO_CONID = N.PERSON_ID AND W.PROVIDER_CONTACT = N1.PERSON_ID

  • Related Language Record: SPF_INV_EXP_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
    3 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
    4 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
    5 MRSC_PARENT_WOID Character(15) VARCHAR2(15) NOT NULL Multi Resource Parent WOID
    6 SUB_LINE Character(1) VARCHAR2(1) NOT NULL Sub Line
    N=No
    Y=Yes
    7 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
    8 SP_SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Type classified as Resource Based or Service Based
    D=Deliverable
    R=Resource
    9 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    10 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    11 VMS_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL VMS Supplier ID
    12 VMS_LOCATION Character(10) VARCHAR2(10) NOT NULL VMS Location - This is the Vendor Managed Service Supplier's office location
    13 SP_VNDR_INTERNAL Character(1) VARCHAR2(1) NOT NULL Vendor Internal or External type
    N=N
    Y=Yes
    14 PERSON_ID Character(15) VARCHAR2(15) NOT NULL Person ID
    15 NAME Character(50) VARCHAR2(50) NOT NULL Name
    16 CREATION_DT Date(10) DATE Creation Date
    17 SP_SUP_INVAPPROVER Character(15) VARCHAR2(15) NOT NULL Supplier Proforma Invoice Approver
    18 SP_END_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL The Supplier who is being managed by the VMS (Vendor Managed Service) Supplier
    19 SP_ENDSUPPLIER_LOC Character(10) VARCHAR2(10) NOT NULL Location of Supplier providing the service to the VMS Supplier
    20 PROVIDER_CONTACT Character(15) VARCHAR2(15) NOT NULL PROVIDER CONTACT
    21 NAME50 Character(50) VARCHAR2(50) NOT NULL name field of length 50
    22 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
    23 TRANS_DT Date(10) DATE Transaction Date
    24 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
    25 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    26 INV_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount
    27 INV_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount for Base Currency
    28 INV_LINE_ADJEXP Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Adjusted Expense Amount
    29 INV_LN_ADJEXP_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Adjusted Expense Amount in Base Currency
    30 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    31 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
    32 PC_DISTRIB_STATUS Character(1) VARCHAR2(1) NOT NULL PC Distribution Status
    B=Budget Check
    D=Distributed
    G=Generated
    H=Hold
    I=Ignore
    M=Modified
    N=Not Distributed
    S=Staged