SPF_SRC_MG_WO_V(SQL View) |
Index Back |
|---|---|
workorder view recordworkorder view record |
| SELECT DISTINCT A.BUSINESS_UNIT , A.WORK_ORDER_ID , A.SP_REQ_ID , A.LINE_NBR , A.PROJECT_ID , A.DEPTID , A.WO_CONID , A.START_DATE , A.WO_CLIID , ' ' , A.ROW_CREATE_USER , A.WO_STATUS , A.SP_ROUTE_TO , A.SP_WO_TYPE , A.SERVICE_METHOD , A.VENDOR_ID , A.ROW_CREATE_DATE , A.END_DATE , A.JOB_TITLE , A.CURRENCY_CD , B.WO_RATE , A.TEAM_ID , A.RECRUITER , A.SP_MSP_PERCENT , A.SP_VNDR_INTERNAL , A.PROVIDER_CONTACT ,A.SP_REP_ACT_START ,A.SRVC_COORDINATOR FROM PS_SPF_WORDERREC A , PS_SPF_WO_PAYRATE B ,PS_BUS_UNIT_TBL_SP C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND (A.SERVICE_METHOD = 'M' AND A.SP_WO_TYPE = 'O' AND(((WO_STATUS ='A' OR WO_STATUS ='L' OR WO_STATUS ='O' OR WO_STATUS ='S' ) AND C.SPB_II_SRC ='Y') OR (WO_STATUS ='A' OR WO_STATUS ='L') AND C.SPB_II_SRC ='N')) OR (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND (A.SERVICE_METHOD = 'M' AND A.SP_WO_TYPE = 'E' AND(((WO_STATUS ='A' OR WO_STATUS ='L' OR WO_STATUS ='O' OR WO_STATUS ='S' ) AND C.SPB_II_SRC ='Y') OR (WO_STATUS ='A' OR WO_STATUS ='L') AND C.SPB_II_SRC ='N')) AND 1 >= ( SELECT COUNT(*) FROM PS_SPF_WO_SERVICE1 WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND WORK_ORDER_ID= A.WORK_ORDER_ID AND LINKED_SRVC_SEQNBR <= 0)) OR (A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.WORK_ORDER_ID = B.WORK_ORDER_ID AND (A.SERVICE_METHOD = 'R' AND A.SP_REP_ACT_START <> 'N') AND (A.WO_STATUS <> 'C' OR A.WO_STATUS <> 'N' OR A.WO_STATUS <> 'T')) AND B.EFFDT <= %CurrentDateIn AND B.EFF_STATUS ='A' |
| # | 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 | SP_REQ_ID | Character(10) | VARCHAR2(10) NOT NULL | Service Request ID |
| 4 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
| 5 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL | Project Id ChartField |
| 6 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 7 | WO_CONID | Character(15) | VARCHAR2(15) NOT NULL | Service Provider's ID assigned on work order |
| 8 | START_DATE | Date(10) | DATE NOT NULL | Start Date for Gen Standing PO |
| 9 | WO_CLIID | Character(30) | VARCHAR2(30) NOT NULL |
Requester
Prompt Table:
REQUESTOR_TBL
|
| 10 | INVAPPROVER | Character(30) | VARCHAR2(30) NOT NULL |
Invoice Approver
Prompt Table: SPF_INVAPRVR_VW |
| 11 | ROW_CREATE_USER | Character(30) | VARCHAR2(30) NOT NULL | Row Added By |
| 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 | SP_ROUTE_TO | Character(30) | VARCHAR2(30) NOT NULL | WorkOrder Approver Person. |
| 14 | 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 |
| 15 | SERVICE_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Service Method
D=Deliverable M=Multi Resource R=Resource |
| 16 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 17 | ROW_CREATE_DATE | DateTime(26) | TIMESTAMP | Row Creation Date Time |
| 18 | END_DATE | Date(10) | DATE | End Date |
| 19 | JOB_TITLE | Character(50) | VARCHAR2(50) NOT NULL | Job Title |
| 20 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 21 | WO_RATE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | WorkOrder Rate |
| 22 | TEAM_ID | Character(15) | VARCHAR2(15) NOT NULL | Team ID |
| 23 | RECRUITER | Character(15) | VARCHAR2(15) NOT NULL | Service Coordinator |
| 24 | SP_MSP_PERCENT | Number(6,2) | DECIMAL(5,2) NOT NULL | MSP percent |
| 25 | SP_VNDR_INTERNAL | Character(1) | VARCHAR2(1) NOT NULL |
Vendor Internal or External type
N=N Y=Yes |
| 26 | PROVIDER_CONTACT | Character(15) | VARCHAR2(15) NOT NULL | PROVIDER CONTACT |
| 27 | SP_REP_ACT_START | Character(1) | VARCHAR2(1) NOT NULL |
Y/N field to indicate if the replacement action started or not
C=Assign button clicked E=Replacment Ended N=Replacement Not Started S=Replacement Started X=Replacement Cancelled |
| 28 | SRVC_COORDINATOR | Character(15) | VARCHAR2(15) NOT NULL | Service Coordinator |