SPF_PLINV_LN_VW

(SQL View)
 Index Back

Manage Invoice - Deliverables

Enterprise Manage Invoice View - Deliverables-Based

SELECT D.BUSINESS_UNIT , D.INVOICE_ID , D.INVOICE_LINE , D.WORK_ORDER_ID , W.MRSC_PARENT_WOID , W.SUB_LINE , D.PLOG_ID , D.SHEET_ID , D.LINE_NBR , D.INV_LINE_STATUS , W.SP_BILL_METHOD , D.ACTIVITY_ID , D.PERCENT_COMPLETE , D.SPA_SUP_INV_ID , D.SP_END_SUPPLIER_ID , W.VNDR_LOC , D.SP_SUP_INVAPPROVER , D.PROVIDER_CONTACT , H.ROW_CREATE_USER , ' ' , ' ' , ' ' , W.WO_CLIID , W.RECRUITER , W.PO_ID , D.TRANS_DT , D.INV_LINE_ADJAMT , D.ADJ_VAT_AMT + ADJ_SALETX_AMT , D.INV_LINE_ADJEXP , D.INV_LINE_ADJAMT + D.INV_LINE_ADJEXP + D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT , D.ADJ_SP_MSP_FEE , D.INV_LINE_ADJAMT + D.INV_LINE_ADJEXP + D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT + D.ADJ_SP_MSP_FEE , W.POAMOUNT - W.SP_BILL_AMOUNT , D.CURRENCY_CD , D.INV_LN_ADJAMT_BSE , D.ADJ_VAT_AMT_BSE + ADJ_SALETX_AMT_BSE , D.INV_LN_ADJEXP_BSE , D.INV_LN_ADJAMT_BSE + D.INV_LN_ADJEXP_BSE + D.ADJ_VAT_AMT_BSE + D.ADJ_SALETX_AMT_BSE , D.ADJ_SP_MSP_FEE_BSE , D.INV_LN_ADJAMT_BSE + D.INV_LN_ADJEXP_BSE + D.ADJ_VAT_AMT_BSE + D.ADJ_SALETX_AMT_BSE + D.ADJ_SP_MSP_FEE_BSE , W.WOAMOUNT_BSE - W.SP_BILL_AMOUNT_BSE , D.CURRENCY_CD_BASE FROM PS_SPF_INVOICE_HDR H , PS_SPF_INVOICE_DTL D , PS_SPF_WORDERREC W , PS_SPF_WORDERREC_1 W1 WHERE H.SP_SERVICE_METHOD = 'D' 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 W.BUSINESS_UNIT = W1.BUSINESS_UNIT AND W.WORK_ORDER_ID = W1.WORK_ORDER_ID

# 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 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 PLOG_ID Character(10) VARCHAR2(10) NOT NULL Time Sheet Unique Identifier (System Generated)
8 SHEET_ID Character(10) VARCHAR2(10) NOT NULL Report ID
9 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
10 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
11 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
12 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
13 PERCENT_COMPLETE Number(6,2) DECIMAL(5,2) NOT NULL % Complete
14 SPA_SUP_INV_ID Character(15) VARCHAR2(15) NOT NULL Supplier Invoice ID
15 SP_END_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL The Supplier who is being managed by the VMS (Vendor Managed Service) Supplier
16 SP_ENDSUPPLIER_LOC Character(10) VARCHAR2(10) NOT NULL Location of Supplier providing the service to the VMS Supplier
17 SP_SUP_INVAPPROVER Character(15) VARCHAR2(15) NOT NULL Supplier Proforma Invoice Approver
18 PROVIDER_CONTACT Character(15) VARCHAR2(15) NOT NULL PROVIDER CONTACT
19 ROW_CREATE_USER Character(30) VARCHAR2(30) NOT NULL Row Added By
20 TE_APPROVER Character(30) VARCHAR2(30) NOT NULL Time Approver

Prompt Table: SPF_APPROVER_VW

21 SP_EXP_APPROVER Character(30) VARCHAR2(30) NOT NULL Expense Approver
22 INVAPPROVER Character(30) VARCHAR2(30) NOT NULL Invoice Approver

Prompt Table: SPF_INVAPRVR_VW

23 WO_CLIID Character(30) VARCHAR2(30) NOT NULL Requester
24 RECRUITER Character(15) VARCHAR2(15) NOT NULL Service Coordinator
25 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
26 TRANS_DT Date(10) DATE Transaction Date
27 INV_LINE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Line Amount
28 TAX_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount
29 INV_EXP_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense 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 INVOICE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount
34 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
35 INV_LINE_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Invoice Line Amount
36 TAX_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Tax Amount (base)
37 INV_EXP_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Expense Amount for Base Currency
38 TOTAL_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Estimated Total
39 SP_MSP_FEE_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Managed Service Provider Fee (Base Currency)
40 SP_MSP_TOTAL_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Amount including MSP Fee (Base Currency)
41 INVOICE_AMOUNT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Invoice Amount in Base Currency
42 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency