SPF_INV_MNG_VW2(SQL View) |
Index Back |
|---|---|
Performance View |
| 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 , V1.NAME1 , A.VNDR_LOC , A.ADDRESS_SEQ_NUM , VS.DESCR AS VNDR_ADDR_DESCR , A.REMIT_VENDOR , A.REMIT_LOC , A.REMIT_ADDR_SEQ_NUM , A.PYMNT_TERMS_CD , B.DESCRSHORT , A.ROW_CREATE_USER , 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_SET_CNTRL_GROUP R , PS_VENDOR_ADDR_VW3 V1 , PS_VNDR_ADDR_SCROL VS , PS_VENDOR_ADDR V2 , PS_VNDR_ADDR_SCROL VS1 , PS_SET_CNTRL_GROUP S WHERE A.PYMNT_TERMS_CD = B.PYMNT_TERMS_CD AND R.SETCNTRLVALUE = A.BUSINESS_UNIT AND R.REC_GROUP_ID = 'FS_14' AND R.SETID = B.SETID AND S.SETCNTRLVALUE = A.BUSINESS_UNIT AND S.REC_GROUP_ID = 'FS_38' AND S.SETID = V1.SETID AND A.VENDOR_ID = V1.VENDOR_ID AND A.ADDRESS_SEQ_NUM = V1.ADDRESS_SEQ_NUM AND A.VENDOR_ID = VS.VENDOR_ID AND A.ADDRESS_SEQ_NUM = VS.ADDRESS_SEQ_NUM AND V1.SETID = VS.SETID AND V1.VENDOR_ID = VS.VENDOR_ID AND V1.ADDRESS_SEQ_NUM = VS.ADDRESS_SEQ_NUM AND %EffdtCheck(VENDOR_ADDR V3, V1, A.INVOICE_DT) |
| # | 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_LOC | Character(10) | VARCHAR2(10) NOT NULL | Remit to Location |
| 15 | REMIT_ADDR_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Remitting Location |
| 16 | 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. |
| 17 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 18 | ROW_CREATE_USER | Character(30) | VARCHAR2(30) NOT NULL | Row Added By |
| 19 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 20 | INV_LINE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Line Amount |
| 21 | TAX_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Tax Amount |
| 22 | INV_EXP_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Expense Amount |
| 23 | TOTAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount |
| 24 | SP_MSP_FEE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Managed Service Provider Fee |
| 25 | SP_MSP_TOTAL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount including MSP Fee |
| 26 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 27 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 28 | INV_LINE_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Base Invoice Line Amount |
| 29 | TAX_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Tax Amount (base) |
| 30 | INV_EXP_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Expense Amount for Base Currency |
| 31 | TOTAL_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Estimated Total |
| 32 | SP_MSP_FEE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Managed Service Provider Fee (Base Currency) |
| 33 | SP_MSP_TOTAL_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount including MSP Fee (Base Currency) |
| 34 | INVOICE_AMOUNT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount in Base Currency |
| 35 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |