S360_VND_WO_VW(SQL View) |
Index Back |
---|---|
S360 Supplier WO transactionS360 Supplier PO transaction |
SELECT DISTINCT ' ' , A.BUSINESS_UNIT , B.SETID , A.VENDOR_ID , A.work_order_id , 'WO' , A.START_DATE , A.WO_STATUS , A.WO_CONID , A.SERVICE_METHOD , A.SP_WO_TYPE ,A.WO_STATUS , A.WO_NAME , A.WO_CLIID , D.NAME , A.PO_ID , '' , SRVC_COORDINATOR FROM PS_SPF_WORDERREC A , PS_SPB_PERSON_TBL D , PS_VENDOR B WHERE A.VENDOR_ID <> ' ' AND A.VENDOR_ID = B.VENDOR_ID AND D.PERSON_ID = A.WO_CONID AND a.business_unit IN ( SELECT business_unit FROM PS_BUS_UNIT_TBL_SP WHERE sp_exec_rolename = 'SP_EXECUTIVE') AND a.work_order_id NOT IN ( SELECT c.work_order_id FROM PS_SPF_WORDERREC A , PS_SPF_WO_PO_DTL C WHERE A.BUSINESS_unit = c.business_unit AND a.work_order_id = c.work_order_id AND a.po_id = c.po_id AND c.cntrct_id <> ' ') UNION SELECT DISTINCT cntrct_setid , A.BUSINESS_UNIT , B.SETID , A.VENDOR_ID , A.work_order_id , 'WO' , A.START_DATE , A.WO_STATUS , A.WO_CONID , A.SERVICE_METHOD , A.SP_WO_TYPE ,A.WO_STATUS , A.WO_NAME , A.WO_CLIID , D.NAME , A.PO_ID , c.cntrct_id , SRVC_COORDINATOR FROM PS_SPF_WORDERREC A , PS_SPB_PERSON_TBL D , PS_VENDOR B , PS_SPF_WO_PO_DTL C WHERE A.VENDOR_ID <> ' ' AND A.VENDOR_ID = B.VENDOR_ID AND D.PERSON_ID = A.WO_CONID AND a.business_unit IN ( SELECT business_unit FROM PS_BUS_UNIT_TBL_SP WHERE sp_exec_rolename = 'SP_EXECUTIVE') AND a.bUSINESS_unit = c.business_unit AND a.work_order_id = c.work_order_id AND a.po_id = c.po_id AND c.cntrct_id <> ' ' |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
3 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
4 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
5 | PRCR_DOC_ID | Character(25) | VARCHAR2(25) NOT NULL | Document ID |
6 | PRCR_DOC_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Document Type
CNT=Contract IUR=Inter Unit Receipt MSR=MSR PMT=Payment PO=Purchase Order RCV=Receipt REQ=Requisition RFQ=Request For Quote RTV=Return To Vendor (Supplier) SSE=Strategic Sourcing Event VOU=Voucher WO=Work Order |
7 | DOCSTAT_DATE | Date(10) | DATE | Document Date |
8 | STATUS_FIELD | Character(30) | VARCHAR2(30) NOT NULL | Payment Status |
9 | WO_CONID | Character(15) | VARCHAR2(15) NOT NULL | Service Provider's ID assigned on work order |
10 | SERVICE_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Service Method
D=Deliverable M=Multi Resource R=Resource |
11 | SP_WO_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
WorkOrder Type classified as original, extension, reassign wo
E=Extension O=Original P=Replacement R=Reassign Work Order |
12 | WO_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Work Order Status
A=Approved C=Closed D=Denied F=Finalize L=Released N=Canceled O=Open P=Pending Terminate S=Submitted T=Terminated |
13 | WO_NAME | Character(30) | VARCHAR2(30) NOT NULL | Work Order Name |
14 | WO_CLIID | Character(30) | VARCHAR2(30) NOT NULL | Requester |
15 | NAME50 | Character(50) | VARCHAR2(50) NOT NULL | name field of length 50 |
16 | PO_NUMBER | Character(20) | VARCHAR2(20) NOT NULL | PO Number |
17 | CNTRCT_ID | Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
18 | SRVC_COORDINATOR | Character(15) | VARCHAR2(15) NOT NULL | Service Coordinator |