SPF_INV_TIME_VW

(SQL View)
 Index Back

Invoice Line Detail - T/E

This view retrieves the invoice line detail for all resource-based transactions. Use the SPF_INV_PLOG_VW to retrieve invoice line details for deliverables-based transactions.

SELECT H.BUSINESS_UNIT , H.INVOICE_ID , D.INVOICE_LINE , D.TIME_SHEET_ID , D.SHEET_ID , D.LINE_NBR , D.WORK_ORDER_ID , D.DISTRIB_LINE_NUM , H.INVOICE_STATUS , H.INVOICE_DT , H.SP_VNDR_INTERNAL , H.VMS_MANAGED , H.VENDOR_ID , H.VNDR_LOC , V1.NAME1 , H.ADDRESS_SEQ_NUM , V1.DESCR , D.SPA_SUP_INV_ID , W1.VMS_SUPPLIER_ID , W1.VMS_LOCATION , D.SP_END_SUPPLIER_ID , W.VNDR_LOC , H.PYMNT_TERMS_CD , P.DESCRSHORT , H.SP_SERVICE_METHOD , D.INV_LINE_STATUS , D.PERIOD_END_DT , W.ROLE_TYPE , W.WO_CONID , W.PO_ID , W.WO_CLIID , D.SP_TIME_QTY , D.SP_TIME_QTY_ADJ , D.UNIT_OF_MEASURE , D.TRANS_DT , D.EXPENSE_TYPE , D.RT_TYPE , T.VAT_APPLICABILITY , T.SUT_APPLICABILITY , T.TAX_VAT_FLG , D.ADJ_SP_MSP_FEE AS SP_MSP_FEE_INT , D.ADJ_SP_MSP_FEE_BSE AS SP_MSP_FEE_INT_BSE , T.TAX_CD_VAT_PCT , T.TAX_CD_SUT_PCT , D.ADJ_VAT_PCT , D.ADJ_SUT_PCT , D.INV_LINE_ADJRATE , D.INV_LINE_AMT , D.VAT_AMT , D.SALETX_AMT , D.USETAX_AMT , D.INV_EXP_AMT , D.SP_MSP_FEE , D.SP_MSP_TOTAL , D.INV_LINE_ADJAMT , D.ADJ_VAT_AMT , D.ADJ_SALETX_AMT , D.ADJ_USETAX_AMT , D.INV_LINE_ADJEXP , D.ADJ_SP_MSP_FEE , W.POAMOUNT - W.SP_BILL_AMOUNT , D.CURRENCY_CD , D.INV_LN_ADJRATE_BSE , D.INV_LINE_AMT_BSE , D.VAT_AMT_BSE , D.SALETX_AMT_BSE , D.USETAX_AMT_BSE , D.INV_EXP_AMT_BSE , D.SP_MSP_FEE_BSE , D.SP_MSP_TOTAL_BSE , D.INV_LN_ADJAMT_BSE , D.ADJ_VAT_AMT_BSE , D.ADJ_SALETX_AMT_BSE , D.ADJ_USETAX_AMT_BSE , D.INV_LN_ADJEXP_BSE , D.ADJ_SP_MSP_FEE_BSE , W.WOAMOUNT_BSE - W.SP_BILL_AMOUNT_BSE , D.CURRENCY_CD_BASE , D.COMMENTS FROM PS_SPF_INVOICE_HDR H , PS_SPF_INVOICE_DTL D , PS_PAYTRMS_SPF_VW P , PS_SPF_WORDERREC W , PS_SPF_WORDERREC_1 W1 , PS_SPF_WO_TAX T , PS_VENDOR_ADDR_VW3 V1 WHERE H.SP_SERVICE_METHOD = 'R' AND 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 W1.BUSINESS_UNIT = W.BUSINESS_UNIT AND W1.WORK_ORDER_ID = W.WORK_ORDER_ID AND W.BUSINESS_UNIT = T.BUSINESS_UNIT AND W.WORK_ORDER_ID = T.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)

  • Related Language Record: SPF_INV_TIME_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 TIME_SHEET_ID Character(10) VARCHAR2(10) NOT NULL Time Sheet Unique Identifier (System Generated)
    5 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
    6 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
    7 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
    8 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
    9 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
    10 INVOICE_DT Date(10) DATE NOT NULL Invoice Date
    11 SP_VNDR_INTERNAL Character(1) VARCHAR2(1) NOT NULL Vendor Internal or External type
    N=N
    Y=Yes
    12 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
    13 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    14 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
    15 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    16 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 SPA_SUP_INV_ID Character(15) VARCHAR2(15) NOT NULL Supplier Invoice ID
    19 VMS_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL VMS Supplier ID
    20 VMS_LOCATION Character(10) VARCHAR2(10) NOT NULL VMS Location - This is the Vendor Managed Service Supplier's office location
    21 SP_END_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL The Supplier who is being managed by the VMS (Vendor Managed Service) Supplier
    22 SP_ENDSUPPLIER_LOC Character(10) VARCHAR2(10) NOT NULL Location of Supplier providing the service to the VMS Supplier
    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.

    Prompt Table: PAYTRMS_SPF_VW

    24 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    25 SP_SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Type classified as Resource Based or Service Based
    D=Deliverable
    R=Resource
    26 INV_LINE_STATUS Character(4) VARCHAR2(4) NOT NULL Invoice Line Status
    1=Generated
    10=Adjusted by Ent Approver
    11=Adjusted by Enterprise
    2=Approved by Enterprise
    3=Submitted to Enterprise
    4=Denied by Enterprise
    5=Submitted to Supplier
    6=Approved by Supplier
    7=Denied by Supplier
    8=Canceled
    9=Adjusted by Supplier
    27 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.
    28 ROLE_TYPE Character(6) VARCHAR2(6) NOT NULL Service Type
    APR=Approver
    MGR=Manager
    OPR=Operator
    29 WO_CONID Character(15) VARCHAR2(15) NOT NULL Service Provider's ID assigned on work order
    30 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
    31 WO_CLIID Character(30) VARCHAR2(30) NOT NULL Requester
    32 SP_TIME_QTY Signed Number(18,4) DECIMAL(16,4) NOT NULL Time Quantity
    33 SP_TIME_QTY_ADJ Signed Number(18,4) DECIMAL(16,4) NOT NULL Adjusted Time Quantity
    34 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    35 TRANS_DT Date(10) DATE Transaction Date
    36 EXPENSE_TYPE Character(7) VARCHAR2(7) NOT NULL Expense Type
    37 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.
    38 VAT_APPLICABILITY Character(1) VARCHAR2(1) NOT NULL A flag that indicates whether or not a transaction is eligible for VAT (Value Added Tax) taxation or why it is not eligible.
    E=Exempt
    N=Not Applicable
    O=Outside of Scope of VAT
    S=Suspended
    T=Taxable
    V=VAT Only
    X=Exonerated
    39 SUT_APPLICABILITY Character(1) VARCHAR2(1) NOT NULL Sales/Use Tax Applicability
    D=Direct Pay
    E=Item is Exempt
    S=Sales Tax Applicable
    U=Use Tax Applicable
    X=Purchaser Is Exonerated
    40 TAX_VAT_FLG Character(1) VARCHAR2(1) NOT NULL Include VAT
    N=No
    Y=Yes
    41 SP_MSP_FEE_INT Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee
    42 SP_MSP_FEE_INT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee (Base Currency)
    43 TAX_CD_VAT_PCT Signed Number(9,4) DECIMAL(7,4) NOT NULL Specifies the tax percentage that corresponds to the VAT code. If more than one VAT authority is linked to a VAT code this will represent an aggregate percentage.
    44 TAX_CD_SUT_PCT Number(8,4) DECIMAL(7,4) NOT NULL Sales/Use Tax Code Percent
    45 ADJ_VAT_PCT Signed Number(9,4) DECIMAL(7,4) NOT NULL Specifies the tax percentage that corresponds to the VAT code which has been adjusted.
    46 ADJ_SUT_PCT Number(8,4) DECIMAL(7,4) NOT NULL Adjusted Sales/Use Tax Code Percent
    47 INV_LINE_ADJRATE Signed Number(16,4) DECIMAL(14,4) NOT NULL Adjusted Invoice Line Rate
    48 INV_LINE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Amount
    49 VAT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the amount of VAT associated with a transaction such as an invoice or a purchase order. In PeopleSoft Receivables this field is populated when the VAT declaration point is set to Invoice.
    50 SALETX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Sales Tax Amount
    51 USETAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
    52 INV_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount
    53 SP_MSP_FEE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee
    54 SP_MSP_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee
    55 INV_LINE_ADJAMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Adjusted Invoice Line Amount
    56 ADJ_VAT_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL This field stores the adjusted VAT amount. The original amount is stored in the VAT_AMT field for sPro.
    57 ADJ_SALETX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL This field stores the adjusted Sales Tax Amount. The original amount is stored in the SALETX_AMT field for sPro.
    58 ADJ_USETAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL This field stores the adjusted Use Tax amount. The original amount is stored in the USETAX_AMT field for sPro.
    59 INV_LINE_ADJEXP Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Adjusted Expense Amount
    60 ADJ_SP_MSP_FEE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider/Vendor Managed Services Fee
    61 INVOICE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
    62 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    63 INV_LN_ADJRATE_BSE Signed Number(16,4) DECIMAL(14,4) NOT NULL Invoice Line Adjusted Rate in Base Currency
    64 INV_LINE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Invoice Line Amount
    65 VAT_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL VAT Amount in Base Curr
    66 SALETX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Sales Tax Amount
    67 USETAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Use Tax Amount
    68 INV_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount for Base Currency
    69 SP_MSP_FEE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee (Base Currency)
    70 SP_MSP_TOTAL_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee (Base Currency)
    71 INV_LN_ADJAMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Adjusted Amount in base currency
    72 ADJ_VAT_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Adjusted VAT Amount - Base Currency
    73 ADJ_SALETX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Adjusted Sales Tax Amount - Base Currency This field stores the adjusted Sales Tax Amount in Base Currency. The original amount is stored in the SALETX_AMT_BSE field for sPro.
    74 ADJ_USETAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL This field stores the adjusted Use Tax amount in Base Currency. The original amount is stored in the USETAX_AMT_BSE field for sPro.
    75 INV_LN_ADJEXP_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Adjusted Expense Amount in Base Currency
    76 ADJ_SP_MSP_FEE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider/Vendor Managed Services Fee
    77 INVOICE_AMOUNT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount in Base Currency
    78 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
    79 COMMENTS Long Character CLOB Comment