SP_PV_REQLN_VW

(SQL View)
 Index Back

SP Requisition Line View

A 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 C.BUSINESS_UNIT , C.REQ_ID , C.LINE_NBR , A.SP_REQ_ID , 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 , A.SP_REQ_TYPE , A.SP_LINKED_REQ_ID , A.SP_LINKED_LINENUM , A.SP_ORIGIN_TYPE , A.SP_EXTENDED_FLG , A.SP_NUM_OF_POSITION , A.TEAM_ID , A.SP_MSP_PERCENT , A.SP_NUM_FILLED_POS , A.MS_RATE , A.SP_EXP_APPROVER , B.SPB_REQ_II_SRC , B.SP_TRACK_RESRC , B.SP_RATES_BY_SC , B.VMS_SUPPLIER_ID , B.VMS_LOCATION , B.SP_TIME_REPORT_OPT , A.SPF_EXPENSE_RATE , A.SP_EXP_RATE_UOM , A.SPA_USE_EXPENSES , A.SERVICE_METHOD , B.SPF_JOB_DESCRLONG FROM PS_SPF_REQ_LINE A , PS_SPF_REQ_LINE_JD B , PS_PV_REQ_LN_XREF C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SP_REQ_ID = B.SP_REQ_ID AND A.LINE_NBR = B.LINE_NBR AND C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.SP_REQ_ID = A.SP_REQ_ID AND C.SP_LINE_NBR = A.LINE_NBR

  • Parent record: SPF_REQ_HDR

    • # 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 REQ_ID Character(10) VARCHAR2(10) NOT NULL Requisition ID
    3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]

    Default Value: 1

    4 SP_REQ_ID Character(10) VARCHAR2(10) NOT NULL Service Request ID

    Default Value: NEXT

    5 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
    6 HOLD_STATUS Character(1) VARCHAR2(1) NOT NULL Hold From Further Processing
    7 IN_PROCESS_FLG Character(1) VARCHAR2(1) NOT NULL In process flag

    Y/N Table Edit

    Default Value: N

    8 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
    9 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

    Prompt Table: PROJID_ADDPC_VW
    Set Control Field: BUSINESS_UNIT_PC

    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPTID_BUGL_VW

    12 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
    13 START_DATE Date(10) DATE Start Date for Gen Standing PO
    14 END_DATE Date(10) DATE End Date
    15 TELECOMMUTE Character(1) VARCHAR2(1) NOT NULL Defines if the user telecommutes or not
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    16 SPF_RATE Signed Number(16,4) DECIMAL(14,4) NOT NULL Rate
    17 SP_RATE_UOM Character(3) VARCHAR2(3) NOT NULL Rate Unit of Measure
    18 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    19 JOB_TITLE Character(50) VARCHAR2(50) NOT NULL Job Title
    20 ROLE_TYPE Character(6) VARCHAR2(6) NOT NULL Service Type
    APR=Approver
    MGR=Manager
    OPR=Operator
    21 PROJ_ROLE Character(15) VARCHAR2(15) NOT NULL Project Role
    22 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL
    Set Control Field: LOCATION

    23 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
    24 SPF_YRS_EXPERIENCE Character(4) VARCHAR2(4) NOT NULL Years Experience

    Prompt Table: SP_EXPERIENCE

    25 COMPETENCY_DESC Long Character CLOB Competencies Required
    26 INTERVIEWREQUIRED Character(1) VARCHAR2(1) NOT NULL Interview Required
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    27 TRAVEL_REQUIRED Character(1) VARCHAR2(1) NOT NULL Travel Required
    N=No
    Y=Yes

    Y/N Table Edit

    28 TRAVEL_FREQUENCY Number(3,0) SMALLINT NOT NULL Travel Frequency
    29 CONTRACT_FOR_HIRE Character(1) VARCHAR2(1) NOT NULL Contract for Hire

    Y/N Table Edit

    30 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Default Value: REQ_HDR.CURRENCY_CD

    31 OPRID_APPROVED_BY Character(30) VARCHAR2(30) NOT NULL Approved By
    32 APPROVAL_DT Date(10) DATE Date of Approval
    33 REQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
    34 RECRUITER Character(15) VARCHAR2(15) NOT NULL Service Coordinator
    35 TE_MANAGER Character(30) VARCHAR2(30) NOT NULL Time Entry Manager
    36 SPF_SPLR_RATE Signed Number(16,4) DECIMAL(14,4) NOT NULL Stores the supplier rate.
    37 SP_SPLR_RATE_UOM Character(3) VARCHAR2(3) NOT NULL stores the supplier's rate uom

    Prompt Table: UNITS_TBL

    38 CURRENCY_CD_SPLR Character(3) VARCHAR2(3) NOT NULL Currency Code of the supplier

    Prompt Table: CURRENCY_CD_TBL

    39 SPF_REQ_LN_TOTAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Stores the supplier's total rate.
    40 EXPENSES_INCLUDED Character(4) VARCHAR2(4) NOT NULL Expenses Included
    1=Not applicable
    2=Included in rate
    3=Per diem
    4=Actual
    41 OVERTIME_PERMITTED Character(1) VARCHAR2(1) NOT NULL Overtime Permitted
    N=No
    Y=Yes

    Y/N Table Edit

    42 SPF_EXTERNAL Character(1) VARCHAR2(1) NOT NULL External Company
    43 SPF_CUST_COMPANY Character(15) VARCHAR2(15) NOT NULL External Comapny
    44 COMMENTS_254 Character(254) VARCHAR2(254) NOT NULL EDI Comments
    45 ROW_CREATE_DATE DateTime(26) TIMESTAMP Row Creation Date Time
    46 ROW_MODIFY_DATE DateTime(26) TIMESTAMP Modified Date Time
    47 ROW_CREATE_USER Character(30) VARCHAR2(30) NOT NULL Row Added By
    48 ROW_MODIFY_USER Character(30) VARCHAR2(30) NOT NULL Modified User
    49 SP_REQ_TYPE Character(10) VARCHAR2(10) NOT NULL Request type - reason code

    Prompt Table: SP_REQUEST_TYPE

    50 SP_LINKED_REQ_ID Character(10) VARCHAR2(10) NOT NULL Service Request Lined Req. ID
    51 SP_LINKED_LINENUM Number(5,0) INTEGER NOT NULL Linked sPro Req. ID Line number
    52 SP_ORIGIN_TYPE Character(3) VARCHAR2(3) NOT NULL Requisition Origin Type - Original Extended Copied Consolidated Linked
    EXT=Extended Requisition
    ORG=Original Requisition

    Default Value: ORG

    53 SP_EXTENDED_FLG Character(1) VARCHAR2(1) NOT NULL Requisition has been extended.

    Y/N Table Edit

    Default Value: N

    54 SP_NUM_OF_POSITION Number(5,0) INTEGER NOT NULL Number of Positions

    Default Value: 1

    55 TEAM_ID Character(15) VARCHAR2(15) NOT NULL Team ID

    Prompt Table: SPB_TEAM_ID_VW

    56 SP_MSP_PERCENT Number(6,2) DECIMAL(5,2) NOT NULL MSP percent
    57 SP_NUM_FILLED_POS Number(5,0) INTEGER NOT NULL Number of filled Positions

    Default Value: 0

    58 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
    59 SP_EXP_APPROVER Character(30) VARCHAR2(30) NOT NULL Expense Approver
    60 SPB_REQ_II_SRC Character(1) VARCHAR2(1) NOT NULL Parallel sourcing
    D=Done
    N=Not Required
    R=Ready
    S=Send to SC

    Default Value: N

    61 SP_TRACK_RESRC Character(1) VARCHAR2(1) NOT NULL Track Resource
    B=Default From Business Unit
    N=No
    Y=Yes
    62 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

    Default Value: N

    63 VMS_SUPPLIER_ID Character(10) VARCHAR2(10) NOT NULL VMS Supplier ID
    64 VMS_LOCATION Character(10) VARCHAR2(10) NOT NULL VMS Location - This is the Vendor Managed Service Supplier's office location
    65 SP_TIME_REPORT_OPT Character(1) VARCHAR2(1) NOT NULL Time Reporting Option
    A=Actual Allocation
    D=Default from Business Unit
    P=Percent Allocation

    Default Value: P

    66 SPF_EXPENSE_RATE Signed Number(16,4) DECIMAL(14,4) NOT NULL Expense Rate
    67 SP_EXP_RATE_UOM Character(3) VARCHAR2(3) NOT NULL stores the supplier's rate uom

    Prompt Table: SPF_UOM_VW

    68 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

    Default Value: N

    69 SERVICE_METHOD Character(1) VARCHAR2(1) NOT NULL Service Method
    D=Deliverable
    M=Multi Resource
    R=Resource

    Default Value: R

    70 SPF_JOB_DESCRLONG Long Character CLOB stores the job description for a sp requisition.