SPF_SRC_RQLN_VW(SQL View) |
Index Back |
---|---|
SP Requisition Line ViewA view of the entire SPF_REQ_LINE record. This view contains no where clauses so it could be reused by appending the appropriate filter(s). SELECT A.BUSINESS_UNIT , A.SP_REQ_ID , A.LINE_NBR , A.SP_REQ_STATUS , A.HOLD_STATUS , A.IN_PROCESS_FLG , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.DESCR , A.DEPTID , A.SP_DURATION , A.START_DATE , A.END_DATE , A.TELECOMMUTE , A.SPF_RATE , A.SP_RATE_UOM , A.UNIT_OF_MEASURE , A.JOB_TITLE , A.ROLE_TYPE , A.PROJ_ROLE , A.LOCATION , A.EDUCATION_LVL , A.SPF_YRS_EXPERIENCE , A.COMPETENCY_DESC , A.INTERVIEWREQUIRED , A.TRAVEL_REQUIRED , A.TRAVEL_FREQUENCY , A.CONTRACT_FOR_HIRE , A.CURRENCY_CD , A.OPRID_APPROVED_BY , A.APPROVAL_DT , A.REQUESTOR_ID , A.RECRUITER , A.TE_APPROVER , A.SPF_SPLR_RATE , A.SP_SPLR_RATE_UOM , A.CURRENCY_CD_SPLR , A.SPF_REQ_LN_TOTAL , A.EXPENSES_INCLUDED , A.OVERTIME_PERMITTED , A.SPF_EXTERNAL , A.SPF_CUST_COMPANY , A.COMMENTS_254 , A.ROW_CREATE_DATE , A.ROW_MODIFY_DATE , A.ROW_CREATE_USER , A.ROW_MODIFY_USER , B.SPF_JOB_DESCRLONG FROM PS_SPF_REQ_LINE A , PS_SPF_REQ_LINE_JD B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SP_REQ_ID = B.SP_REQ_ID AND A.LINE_NBR = B.LINE_NBR |
SELECT A.BUSINESS_UNIT , A.SP_REQ_ID , A.LINE_NBR , A.SP_REQ_STATUS , A.HOLD_STATUS , A.IN_PROCESS_FLG , A.BUSINESS_UNIT_PC , A.PROJECT_ID , A.DESCR , A.DEPTID , A.SP_DURATION , A.START_DATE , A.END_DATE , A.TELECOMMUTE , A.SPF_RATE , A.SP_RATE_UOM , A.SPF_EXPENSE_RATE , A.SP_EXP_RATE_UOM , A.UNIT_OF_MEASURE , A.JOB_TITLE , A.ROLE_TYPE , A.PROJ_ROLE , A.LOCATION , A.EDUCATION_LVL , A.SPF_YRS_EXPERIENCE , A.COMPETENCY_DESC , A.INTERVIEWREQUIRED , A.TRAVEL_REQUIRED , A.TRAVEL_FREQUENCY , A.CONTRACT_FOR_HIRE , A.CURRENCY_CD , A.OPRID_APPROVED_BY , A.APPROVAL_DT , A.REQUESTOR_ID , A.RECRUITER , A.TE_APPROVER , A.SP_EXP_APPROVER , B.SP_TIME_REPORT_OPT , B.SP_EXP_REPORT_OPT , A.SPF_SPLR_RATE , A.SP_SPLR_RATE_UOM , A.SPF_SPLR_AMT , A.CURRENCY_CD_SPLR , A.SPF_REQ_LN_TOTAL , A.SPF_LN_FILL_TOTAL , A.EXPENSES_INCLUDED , A.OVERTIME_PERMITTED , A.SPF_EXTERNAL , A.SPF_CUST_COMPANY , A.COMMENTS_254 , A.ROW_CREATE_DATE , A.ROW_MODIFY_DATE , A.ROW_CREATE_USER , A.ROW_MODIFY_USER , A.SP_UTILIZATION , A.SPF_RATE_FILL , A.SPF_FILL_CURR_CD , A.SPF_EXPENSE_AMT , A.DURATION , A.SERVICE_METHOD , A.SP_SHOW_BID , A.SP_ORIGIN_TYPE , A.SP_MSP_PERCENT , A.SP_NUM_OF_POSITION , A.TEAM_ID , A.SP_NUM_FILLED_POS , A.SPA_USE_EXPENSES , A.MS_RATE , P.SP_PAY_RATE , P.SP_FIXED_COST , P.SP_VNDR_MARKUP , P.SP_3RDPARTY_MARKUP , B.SPF_JOB_DESCRLONG , B.SP_RATES_BY_SC ,B.SP_TRACK_RESRC ,B.VMS_SUPPLIER_ID ,B.VMS_LOCATION ,A.CHNG_ORD_SEQ ,A.SP_REQ_TYPE FROM PS_SPF_REQ_LINE A , PS_SPF_REQ_LINE_JD B , PS_SPF_REQ_LINE_PT P WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SP_REQ_ID = B.SP_REQ_ID AND A.LINE_NBR = B.LINE_NBR AND P.BUSINESS_UNIT = A.BUSINESS_UNIT AND P.SP_REQ_ID = A.SP_REQ_ID AND P.LINE_NBR = A.LINE_NBR |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_PM_NONVW |
2 | SP_REQ_ID | Character(10) | VARCHAR2(10) NOT NULL | Service Request ID |
3 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | Line Number: 11/24/08 - Added TARGET label [PC product] |
4 | SP_REQ_STATUS | Character(4) | VARCHAR2(4) NOT NULL |
Requisition Status for Service Procurement
A=Approved C=Complete D=Denied F=Filled O=Open P=Pending R=On Hold S=Sourced X=Canceled |
5 | HOLD_STATUS | Character(1) | VARCHAR2(1) NOT NULL | Hold From Further Processing |
6 | IN_PROCESS_FLG | Character(1) | VARCHAR2(1) NOT NULL |
In process flag
Y/N Table Edit Default Value: N |
7 | BUSINESS_UNIT_PC | Character(5) | VARCHAR2(5) NOT NULL | PC Business Unit |
8 | PROJECT_ID | Character(15) | VARCHAR2(15) NOT NULL |
Project Id ChartField
Prompt Table:
PROJID_ADDPC_VW
|
9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
10 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPTID_BUGL_VW |
11 | SP_DURATION | Character(4) | VARCHAR2(4) NOT NULL |
Duration field
-1=No Perference 1=Less than 1 month 2=1-3 months 3=3-6 months 4=6-12 months 5=More than 12 months |
12 | START_DATE | Date(10) | DATE | Start Date for Gen Standing PO |
13 | END_DATE | Date(10) | DATE | End Date |
14 | TELECOMMUTE | Character(1) | VARCHAR2(1) NOT NULL |
Defines if the user telecommutes or not
N=No Y=Yes |
15 | SPF_RATE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Rate |
16 | SP_RATE_UOM | Character(3) | VARCHAR2(3) NOT NULL | Rate Unit of Measure |
17 | SPF_EXPENSE_RATE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Expense Rate |
18 | SP_EXP_RATE_UOM | Character(3) | VARCHAR2(3) NOT NULL | stores the supplier's rate uom |
19 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage |
20 | JOB_TITLE | Character(50) | VARCHAR2(50) NOT NULL | Job Title |
21 | ROLE_TYPE | Character(6) | VARCHAR2(6) NOT NULL |
Service Type
APR=Approver MGR=Manager OPR=Operator |
22 | PROJ_ROLE | Character(15) | VARCHAR2(15) NOT NULL | Project Role |
23 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: %EDITTABLE |
24 | EDUCATION_LVL | Character(2) | VARCHAR2(2) NOT NULL |
Education Level
1=No Formal Education 10=Associate Degree 11=Three Years College 12=Four Years College 13=Bachelor Degree 14=Post Bachelors 15=First Professional 16=Post-First Professional 17=Masters Degree 18=Post Masters 19=Sixth Year Degree 2=Elementary School Completed 20=Post Sixth Year 21=Doctorate Degree 22=Post Doctorate 3=Some High School 4=High School Graduate 5=Terminal Occupation Prog DNC 6=Terminal Occupation Program 7=Some College - Less than 1 Yr 8=One Year College 9=Two Years College A=University - Advanced T=Trade School U=University |
25 | SPF_YRS_EXPERIENCE | Character(4) | VARCHAR2(4) NOT NULL |
Years Experience
Prompt Table:
SP_EXPERIENCE
|
26 | COMPETENCY_DESC | Long Character | CLOB | Competencies Required |
27 | INTERVIEWREQUIRED | Character(1) | VARCHAR2(1) NOT NULL |
Interview Required
N=No Y=Yes Default Value: N |
28 | TRAVEL_REQUIRED | Character(1) | VARCHAR2(1) NOT NULL |
Travel Required
N=No Y=Yes |
29 | TRAVEL_FREQUENCY | Number(3,0) | SMALLINT NOT NULL | Travel Frequency |
30 | CONTRACT_FOR_HIRE | Character(1) | VARCHAR2(1) NOT NULL |
Contract for Hire
Y/N Table Edit |
31 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
32 | OPRID_APPROVED_BY | Character(30) | VARCHAR2(30) NOT NULL | Approved By |
33 | APPROVAL_DT | Date(10) | DATE | Date of Approval |
34 | REQUESTOR_ID | Character(30) | VARCHAR2(30) NOT NULL | Requestor id |
35 | RECRUITER | Character(15) | VARCHAR2(15) NOT NULL | Service Coordinator |
36 | TE_APPROVER | Character(30) | VARCHAR2(30) NOT NULL | Time Approver |
37 | SP_EXP_APPROVER | Character(30) | VARCHAR2(30) NOT NULL | Expense Approver |
38 | SP_TIME_REPORT_OPT | Character(1) | VARCHAR2(1) NOT NULL |
Time Reporting Option
A=Actual Allocation D=Default from Business Unit P=Percent Allocation |
39 | SP_EXP_REPORT_OPT | Character(1) | VARCHAR2(1) NOT NULL |
Expense Reporting Option
A=Actual Allocation D=Default from Business Unit P=Percent Allocation |
40 | SPF_SPLR_RATE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Stores the supplier rate. |
41 | SP_SPLR_RATE_UOM | Character(3) | VARCHAR2(3) NOT NULL |
stores the supplier's rate uom
Prompt Table: UNITS_TBL |
42 | SPF_SPLR_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Stores the supplier's amount |
43 | CURRENCY_CD_SPLR | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code of the supplier
Prompt Table: CURRENCY_CD_TBL |
44 | SPF_REQ_LN_TOTAL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Stores the supplier's total rate. |
45 | SPF_LN_FILL_TOTAL | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Requisition line Filled total |
46 | EXPENSES_INCLUDED | Character(4) | VARCHAR2(4) NOT NULL |
Expenses Included
1=Not applicable 2=Included in rate 3=Per diem 4=Actual |
47 | OVERTIME_PERMITTED | Character(1) | VARCHAR2(1) NOT NULL |
Overtime Permitted
N=No Y=Yes |
48 | SPF_EXTERNAL | Character(1) | VARCHAR2(1) NOT NULL | External Company |
49 | SPF_CUST_COMPANY | Character(15) | VARCHAR2(15) NOT NULL | External Comapny |
50 | COMMENTS_254 | Character(254) | VARCHAR2(254) NOT NULL | EDI Comments |
51 | ROW_CREATE_DATE | DateTime(26) | TIMESTAMP | Row Creation Date Time |
52 | ROW_MODIFY_DATE | DateTime(26) | TIMESTAMP | Modified Date Time |
53 | ROW_CREATE_USER | Character(30) | VARCHAR2(30) NOT NULL | Row Added By |
54 | ROW_MODIFY_USER | Character(30) | VARCHAR2(30) NOT NULL | Modified User |
55 | SP_UTILIZATION | Number(3,0) | SMALLINT NOT NULL | Utilization% |
56 | SPF_RATE_FILL | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Filled Rate |
57 | SPF_FILL_CURR_CD | Character(3) | VARCHAR2(3) NOT NULL |
Requisition filled currency code
Prompt Table: CURRENCY_CD_TBL |
58 | SPF_EXPENSE_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total expense amount. |
59 | DURATION | Number(5,0) | INTEGER NOT NULL | Recurrence Duration |
60 | SERVICE_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Service Method
D=Deliverable M=Multi Resource R=Resource |
61 | SP_SHOW_BID | Character(1) | VARCHAR2(1) NOT NULL |
Display Rate to Supplier
N=Hide Rate Y=Display Rate |
62 | SP_ORIGIN_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Requisition Origin Type -
Original
Extended
Copied
Consolidated
Linked
EXT=Extended Requisition ORG=Original Requisition |
63 | SP_MSP_PERCENT | Number(6,2) | DECIMAL(5,2) NOT NULL | MSP percent |
64 | SP_NUM_OF_POSITION | Number(5,0) | INTEGER NOT NULL | Number of Positions |
65 | TEAM_ID | Character(15) | VARCHAR2(15) NOT NULL | Team ID |
66 | SP_NUM_FILLED_POS | Number(5,0) | INTEGER NOT NULL | Number of filled Positions |
67 | SPA_USE_EXPENSES | Character(1) | VARCHAR2(1) NOT NULL |
Field to indicate if the expenses is allowed in the transcation or not.
D=Default from Business Unit N=No Y=Yes |
68 | MS_RATE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Rate charged by either the Vendor Managed Service (VMS) Supplier or the Managed Service Provider (MSP) organization |
69 | SP_PAY_RATE | Number(15,4) | DECIMAL(14,4) NOT NULL | pay rate |
70 | SP_FIXED_COST | Number(15,4) | DECIMAL(14,4) NOT NULL | fixed costs |
71 | SP_VNDR_MARKUP | Number(15,4) | DECIMAL(14,4) NOT NULL | vendor markup |
72 | SP_3RDPARTY_MARKUP | Number(15,4) | DECIMAL(14,4) NOT NULL | third party markup amount |
73 | SPF_JOB_DESCRLONG | Long Character | CLOB | stores the job description for a sp requisition. |
74 | SP_RATES_BY_SC | Character(1) | VARCHAR2(1) NOT NULL |
Flag for Rate definition by SC - Translate values
D - Rate defined by SC
N - Not applicable
P - Pending Rate Synchronization
R - Ready for rate definition.
S - Sent to SC for rate definition.
D=Rate Defined by SC N=Not Applicable P=Pending Rate Synchronization R=Ready for rate S=Sent to SC for Rate |
75 | SP_TRACK_RESRC | Character(1) | VARCHAR2(1) NOT NULL |
Track Resource
B=Default From Business Unit N=No Y=Yes |
76 | VMS_SUPPLIER_ID | Character(10) | VARCHAR2(10) NOT NULL | VMS Supplier ID |
77 | VMS_LOCATION | Character(10) | VARCHAR2(10) NOT NULL | VMS Location - This is the Vendor Managed Service Supplier's office location |
78 | CHNG_ORD_SEQ | Number(3,0) | SMALLINT NOT NULL | Change Order Sequence Number |
79 | SP_REQ_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Request type - reason code |