FO_ASGN_RT_VW2

(SQL View)
 Index Back

Asgn Rates VW for Mass Changes

Mass Changes Assignment Rates View search record for mass changes. It is defined as Base Record in Search Criteria Definition. PayBill Management not Installed (Staffing Front Office stand-alone)

SELECT DTL.ASSIGNMENT_ID , DTL.SEQNO , RT.EFFDT , RT.DETAIL_NO , A.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , CNTRL1.SETID , A.CUST_ID , CUST.NAME1 , CNTRL2.SETID , A.VENDOR_ID , (CASE WHEN A.VENDOR_ID = ' ' THEN ' ' ELSE (%Sql(FO_AFFL_VNDR_NAME1_SQL, CNTRL2.SETID, A.VENDOR_ID)) END) , DTL.START_DT , DTL.END_DT , %Substring(H.OWNERSHIP_ID, 1, 5) , H.USER_ORDER_TYPE , H.PROJECT_ID , A.ACTIVITY_ID , A.ADDRESS_SEQ_NUM , A.LOCATION , A.JOBCODE , A.EMPLID , A.EMPL_RCD_NUM , P.NAME , A.CANDIDATE_TYPE , A.COMPANY , A.BUSINESS_UNIT_HR , A.DEPTID , A.PAYGROUP , A.GP_PAYGROUP , A.TASKGROUP , A.WORKGROUP , A.TAX_LOCATION_CD , A.TAX_CD , H.BUSINESS_UNIT_CA , H.CONTRACT_NUM , A.CONTRACT_LINE_NUM , (CASE WHEN J.PAY_SYSTEM_FLG = 'GP' THEN C.RATE_PROFILE_GP ELSE CASE P.PER_STATUS WHEN 'N' THEN C.RATE_PROFILE_NEE WHEN 'E' THEN CASE WHEN J.EMPL_TYPE = 'S' THEN C.RATE_PROFILE_NAS ELSE C.RATE_PROFILE_NAH END END END) , RT.RATE_ELEMENT , RE.RATE_PURPOSE , RT.REG_PAY_RATE , RT.REG_BILL_RATE , RE.FO_MARKUP_PCT FROM PS_RS_ASSGN_DETAIL DTL , PS_FO_ASGN_RT_DTL RT , PS_RS_ASSIGNMENT A , PS_RS_SO_HDR H , PS_FO_ORDER_TYPE OT , PS_PERSONAL_DATA P , PS_JOB J , PS_FO_CA_PROFILE C , PS_CUSTOMER CUST , PS_SET_CNTRL_REC CNTRL1 , PS_SET_CNTRL_REC CNTRL2 , PS_FO_RATE_ELEMENT RE , PS_SET_CNTRL_REC CNTRL3 WHERE A.ASSIGNMENT_ID = DTL.ASSIGNMENT_ID AND A.SYSTEM_SOURCE = 'STF' AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.SO_ID = H.SO_ID AND OT.USER_ORDER_TYPE = H.USER_ORDER_TYPE AND OT.ORDER_TYPE = 'T' AND CUST.SETID = CNTRL1.SETID AND CNTRL1.SETCNTRLVALUE = A.BUSINESS_UNIT AND CNTRL1.RECNAME = 'CUSTOMER' AND CUST.CUST_ID = A.CUST_ID AND RT.ASSIGNMENT_ID = DTL.ASSIGNMENT_ID AND ( (RT.EFFDT BETWEEN DTL.START_DT AND DTL.END_DT) OR (RT.EFFDT = ( SELECT MAX(RT2.EFFDT) FROM PS_FO_ASGN_RT_DTL RT2 WHERE RT2.ASSIGNMENT_ID = RT.ASSIGNMENT_ID AND RT2.DETAIL_NO = RT.DETAIL_NO AND RT2.EFFDT <= DTL.START_DT ) ) ) AND CNTRL2.SETCNTRLVALUE = A.BUSINESS_UNIT AND CNTRL2.RECNAME = 'AFFL_VENDOR' AND RE.SETID = CNTRL3.SETID AND CNTRL3.SETCNTRLVALUE = A.BUSINESS_UNIT AND CNTRL3.RECNAME = 'FO_RATE_ELEMENT' AND RE.RATE_ELEMENT = RT.RATE_ELEMENT AND C.BUSINESS_UNIT = H.BUSINESS_UNIT_CA AND C.USER_ORDER_TYPE = H.USER_ORDER_TYPE AND P.EMPLID = A.EMPLID AND J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD_NUM AND J.EFFDT = ( SELECT MAX(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD ) AND J.EFFSEQ = ( SELECT MAX(J3.EFFSEQ) FROM PS_JOB J3 WHERE J3.EMPLID = J.EMPLID AND J3.EMPL_RCD = J.EMPL_RCD AND J3.EFFDT = J.EFFDT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ASSIGNMENT_ID Character(15) VARCHAR2(15) NOT NULL Work Assignment ID

Prompt Table: RS_ASSIGNMENT

2 SEQNO Number(5,0) INTEGER NOT NULL Sequence Number
3 EFFDT Date(10) DATE Effective Date
4 DETAIL_NO Number(3,0) SMALLINT NOT NULL Detail Number
5 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_PC_NONVW

6 SO_ID Character(15) VARCHAR2(15) NOT NULL Service Order ID

Prompt Table: FO_ORD_SRCH_VW3

7 SO_LINE Number(3,0) SMALLINT NOT NULL Line Number

Prompt Table: RS_ASSGN_OSL_VW

8 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
9 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.

Prompt Table: RS_CUSTOMER_VW

10 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
11 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
12 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier

Prompt Table: VENDOR_VW

13 VENDOR_NAME1 Character(40) VARCHAR2(40) NOT NULL Vendor Name1
14 START_DT Date(10) DATE Start Date
15 END_DT Date(10) DATE End Date
16 FO_BRANCH Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: FO_BRANCH_TBL

17 USER_ORDER_TYPE Character(5) VARCHAR2(5) NOT NULL User Order Type

Prompt Table: FO_ORDER_TYPE

18 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

Prompt Table: SP_PROJP_NONVW

19 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
20 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

Prompt Table: FO_CST_ADDR_VW2

21 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

Prompt Table: LOCATION_TBL

22 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

Prompt Table: JOBCODE_TBL

23 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSONAL_DATA

24 EMPL_RCD_NUM Number(3,0) SMALLINT NOT NULL Project Employee Job Record #

Prompt Table: JOB

25 NAME Character(50) VARCHAR2(50) NOT NULL Name

Prompt Table: FO_EMPL_NAME_VW

26 CANDIDATE_TYPE Character(1) VARCHAR2(1) NOT NULL Candidate Type
A=Applicant
E=Employee
27 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

Prompt Table: COMPANY_TBL

28 BUSINESS_UNIT_HR Character(5) VARCHAR2(5) NOT NULL Business Unit HR

Prompt Table: BUS_UNIT_TBL_HR

29 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_ALL_VW

30 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group

Prompt Table: FO_PAYGROUP_VW2

31 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Pay Group

Prompt Table: GP_PYGRP

32 TASKGROUP Character(10) VARCHAR2(10) NOT NULL Taskgroup

Prompt Table: TL_TASKGRP_TBL

33 WORKGROUP Character(10) VARCHAR2(10) NOT NULL Workgroup

Prompt Table: TL_WRKGRP_TBL

34 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code
35 TAX_CD Character(8) VARCHAR2(8) NOT NULL Tax Code

Prompt Table: CA_TAX_CD_VW

36 BUSINESS_UNIT_CA Character(5) VARCHAR2(5) NOT NULL Contracts Business Unit

Prompt Table: SP_BU_CA_NONVW

37 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
38 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
39 RATE_PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Rate Profile ID

Prompt Table: FO_RATE_PROFILE

40 RATE_ELEMENT Character(5) VARCHAR2(5) NOT NULL Rate Element
41 RATE_PURPOSE Character(1) VARCHAR2(1) NOT NULL Rate Purpose
O=Overtime
R=Regular
T=Other
42 REG_PAY_RATE Number(19,6) DECIMAL(18,6) NOT NULL Pay Rate
43 REG_BILL_RATE Number(19,6) DECIMAL(18,6) NOT NULL Bill Rate
44 FO_MARKUP_PCT Signed Number(7,2) DECIMAL(5,2) NOT NULL Markup %