SPA_INV_APRMG_V(SQL View) |
Index Back |
|---|---|
Supplier Invoice Approval ViewThis view provides a summary list of invoices that are viewable by the supplier for their approval. |
| SELECT DISTINCT H.BUSINESS_UNIT , H.INVOICE_ID , H.SP_SERVICE_METHOD , H.SP_VNDR_INTERNAL , H.VMS_MANAGED , H.INVOICE_DT , H.INVOICE_STATUS , H.VENDOR_ID , V1.NAME1 , H.VNDR_LOC , H.ADDRESS_SEQ_NUM , VS.DESCR , H.REMIT_VENDOR , V2.NAME1 AS REMIT_TO_NAME , H.REMIT_LOC , H.REMIT_ADDR_SEQ_NUM , VS1.DESCR AS REMIT_ADDR_DESCR , H.PYMNT_TERMS_CD , P.DESCRSHORT , D.SP_SUP_INVAPPROVER , SUM(D.INV_LINE_ADJAMT) , SUM(D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT) , SUM(D.INV_LINE_ADJEXP) , SUM(D.INV_LINE_ADJAMT + D.INV_LINE_ADJEXP + D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT) , SUM(D.ADJ_SP_MSP_FEE) , SUM(D.INV_LINE_ADJAMT + D.INV_LINE_ADJEXP + D.ADJ_VAT_AMT + D.ADJ_SALETX_AMT + D.ADJ_SP_MSP_FEE) , 0 , D.CURRENCY_CD , SUM(D.INV_LN_ADJAMT_BSE) , SUM(D.ADJ_VAT_AMT_BSE + D.ADJ_SALETX_AMT_BSE) , SUM(D.INV_LN_ADJEXP_BSE) , SUM(D.INV_LN_ADJAMT_BSE + D.INV_LN_ADJEXP_BSE + D.ADJ_VAT_AMT_BSE + D.ADJ_SALETX_AMT_BSE) , SUM(D.ADJ_SP_MSP_FEE_BSE) , SUM(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) , 0 , D.CURRENCY_CD_BASE FROM PS_SPF_INVOICE_HDR H , PS_SPF_INVOICE_DTL D , PS_SPF_WORDERREC W , PS_PYMT_TRMS_HDR P , PS_VENDOR_ADDR_VW3 V1 , PS_VNDR_ADDR_SCROL VS , PS_VENDOR_ADDR V2 , PS_VNDR_ADDR_SCROL VS1 WHERE D.INV_LINE_STATUS NOT IN ('1', '8') 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 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 H.VENDOR_ID = VS.VENDOR_ID AND H.ADDRESS_SEQ_NUM = VS.ADDRESS_SEQ_NUM AND H.VENDOR_ID = VS1.VENDOR_ID AND H.ADDRESS_SEQ_NUM = VS1.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 V1.EFFDT=( SELECT MAX(EFFDT) FROM PS_VENDOR_ADDR_VW3 V3 WHERE V3.SETID=V1.SETID AND V3.VENDOR_ID=V1.VENDOR_ID AND V3.ADDRESS_SEQ_NUM=V1.ADDRESS_SEQ_NUM AND V3.EFFDT<=H.INVOICE_DT) AND V1.SETID = V2.SETID AND V2.VENDOR_ID = H.VENDOR_ID AND V2.ADDRESS_SEQ_NUM = H.ADDRESS_SEQ_NUM AND V2.SETID = VS1.SETID AND V2.VENDOR_ID = VS1.VENDOR_ID AND V2.ADDRESS_SEQ_NUM = VS1.ADDRESS_SEQ_NUM AND V2.EFFDT=( SELECT MAX(EFFDT) FROM PS_VENDOR_ADDR V4 WHERE V4.SETID=V2.SETID AND V4.VENDOR_ID=V2.VENDOR_ID AND V4.ADDRESS_SEQ_NUM=V2.ADDRESS_SEQ_NUM AND V4.EFFDT<=H.INVOICE_DT) GROUP BY H.BUSINESS_UNIT , H.INVOICE_ID , H.SP_SERVICE_METHOD , H.SP_VNDR_INTERNAL , H.VMS_MANAGED , H.INVOICE_DT , H.INVOICE_STATUS , H.VENDOR_ID , V1.NAME1 , H.VNDR_LOC , H.ADDRESS_SEQ_NUM , VS.DESCR , H.REMIT_VENDOR , V2.NAME1 , H.REMIT_LOC , H.REMIT_ADDR_SEQ_NUM , VS1.DESCR , H.PYMNT_TERMS_CD , P.DESCRSHORT , D.SP_SUP_INVAPPROVER , D.CURRENCY_CD , D.CURRENCY_CD_BASE |
| # | 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_TO_NAME | Character(40) | VARCHAR2(40) NOT NULL | Remit To Supplier Name |
| 15 | REMIT_LOC | Character(10) | VARCHAR2(10) NOT NULL | Remit to Location |
| 16 | REMIT_ADDR_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Remitting Location |
| 17 | DESCR_2 | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 18 | 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. |
| 19 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 20 | SP_SUP_INVAPPROVER | Character(15) | VARCHAR2(15) NOT NULL | Supplier Proforma Invoice Approver |
| 21 | INV_LINE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Line Amount |
| 22 | TAX_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Tax Amount |
| 23 | INV_EXP_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Expense Amount |
| 24 | TOTAL_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount |
| 25 | SP_MSP_FEE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Managed Service Provider Fee |
| 26 | SP_MSP_TOTAL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount including MSP Fee |
| 27 | INVOICE_AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount |
| 28 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 29 | INV_LINE_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Base Invoice Line Amount |
| 30 | TAX_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Tax Amount (base) |
| 31 | INV_EXP_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Expense Amount for Base Currency |
| 32 | TOTAL_AMT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Estimated Total |
| 33 | SP_MSP_FEE_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Managed Service Provider Fee (Base Currency) |
| 34 | SP_MSP_TOTAL_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Amount including MSP Fee (Base Currency) |
| 35 | INVOICE_AMOUNT_BSE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Invoice Amount in Base Currency |
| 36 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |