SPF_INV_POLN_VW

(SQL View)
 Index Back

Invoice PO line number view

Invoice PO line number view

SELECT A.BUSINESS_UNIT , B.WORK_ORDER_ID , B.PO_ID , B.PO_LINE_NBR , A.INVOICE_ID , A.INVOICE_LINE , A.VENDOR_ID , A.VNDR_LOC FROM PS_SPF_INVOICE_RPT A , PS_SPF_WO_PO_DTL B WHERE A. BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND B.SEQNUM = 1 AND (A.TIME_SHEET_ID <> ' ' OR A.PLOG_ID <> ' ') UNION SELECT A.BUSINESS_UNIT , B.WORK_ORDER_ID , B.PO_ID , B.PO_LINE_NBR , A.INVOICE_ID , A.INVOICE_LINE , A.VENDOR_ID , A.VNDR_LOC FROM PS_SPF_INVOICE_RPT A , PS_SPF_WO_PO_DTL B WHERE A. BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND B.SEQNUM = 2 AND A.SHEET_ID <> ' ' UNION SELECT A.BUSINESS_UNIT , B.WORK_ORDER_ID , B.PO_ID , 0 , A.INVOICE_ID , A.INVOICE_LINE , A.VENDOR_ID , A.VNDR_LOC FROM PS_SPF_INVOICE_RPT A , PS_SPF_WORDERREC B WHERE A. BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND A.WORK_ORDER_ID NOT IN ( SELECT DISTINCT WORK_ORDER_ID FROM PS_SPF_WO_PO_DTL WHERE PS_SPF_WO_PO_DTL.BUSINESS_UNIT = A.BUSINESS_UNIT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 WORK_ORDER_ID Character(15) VARCHAR2(15) NOT NULL Work Order ID
3 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
4 PO_LINE_NBR Number(5,0) INTEGER NOT NULL Purchase Order Line Number
5 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
6 INVOICE_LINE Number(5,0) INTEGER NOT NULL Invoice Line
7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
8 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location