FO_ASGN_QRY_VW(SQL View) |
Index Back |
|---|---|
Assingments-Job, Customer InfoDesigned to make it easy in Query for Crystal Reports |
| SELECT B.DEPTID , H.DESCR ,L.COMPANY ,M.STATE ,M.COUNTRY ,M.SETID ,A.JOBCODE , C.REG_BILL_RATE , C.REG_PAY_RATE , A.EMPLID , A.ASSIGNMENT_ID , F.FO_BRANCH , F.DESCR , A.ENTERED_BY , J.OPRDEFNDESC , O.MARGIN_ESTIMATOR ,Q.START_DT ,A.END_ACTUAL_DATE ,B.CUST_ID ,B.USER_ORDER_TYPE ,A.SO_LINE ,A.SO_ID ,A.BUSINESS_UNIT ,A.INCOME ,B.SALES_OPRID FROM PS_RS_ASSIGNMENT A , PS_RS_SO_HDR B , PS_FO_ASGN_RT_DTL C , PS_FO_RATE_ELEMENT D , PS_SET_CNTRL_REC E , PS_FO_BRANCH_TBL F , PS_DEPT_TBL H , PS_SET_CNTRL_REC I , PS_OPRID_VW2 J , PS_JOB L , PS_CUST_ADDRESS M , PS_SET_CNTRL_REC N , PS_FO_BRANCH_TBL O , PS_FO_EMP_OWNERS P , PS_FO_ASGN_DATE_VW Q WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.SO_ID = A.SO_ID AND %EffdtCheck(FO_ASGN_RT_DTL C_ED,C, %CurrentDateIn) AND A.ASSIGNMENT_ID = C.ASSIGNMENT_ID AND D.RATE_ELEMENT = C.RATE_ELEMENT AND A.BUSINESS_UNIT = E.SETCNTRLVALUE AND E.RECNAME = 'FO_RATE_ELEMENT' AND D.SETID = E.SETID AND D.RATE_PURPOSE = 'R' AND B.OWNERSHIP_ID = F.FO_BRANCH AND %EffdtCheck(DEPT_TBL H_ED,H, %CurrentDateIn) AND B.BUSINESS_UNIT = I.SETCNTRLVALUE AND I.RECNAME = 'DEPT_TBL' AND H.SETID = I.SETID AND B.DEPTID = H.DEPTID AND B.ENTERED_BY = J.OPRID AND A.EMPLID = L.EMPLID AND A.EMPL_RCD_NUM = L.EMPL_RCD AND %EffdtCheck(JOB L_ES,L, %CurrentDateIn) AND A.BUSINESS_UNIT = N.SETCNTRLVALUE AND N.RECNAME = 'CUSTOMER' AND N.SETID = M.SETID AND A.CUST_ID = M.CUST_ID AND A.ADDRESS_SEQ_NUM = M.ADDRESS_SEQ_NUM AND %EffdtCheck(CUST_ADDRESS M_ED,M, %CurrentDateIn) AND B.OWNERSHIP_ID = O.FO_BRANCH AND P.EMPLID = A.EMPLID AND P.PRIMARY_OWNER = 'Y' AND A.ASSIGNMENT_ID = Q.ASSIGNMENT_ID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 2 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 3 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 4 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 5 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 6 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 7 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 8 | REG_PAY_RATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Pay Rate |
| 9 | REG_BILL_RATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Bill Rate |
| 10 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 11 | ASSIGNMENT_ID | Character(15) | VARCHAR2(15) NOT NULL | Work Assignment ID |
| 12 | FO_BRANCH | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 13 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 14 | ENTERED_BY | Character(30) | VARCHAR2(30) NOT NULL | Entered by |
| 15 | OPRDEFNDESC | Character(30) | VARCHAR2(30) NOT NULL | Description field for a user |
| 16 | MARGIN_ESTIMATOR | Number(6,2) | DECIMAL(5,2) NOT NULL | Fixed Cost Margin |
| 17 | START_DT | Date(10) | DATE | Start Date |
| 18 | END_ACTUAL_DATE | Date(10) | DATE | Actual End Date |
| 19 | 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. |
| 20 | USER_ORDER_TYPE | Character(5) | VARCHAR2(5) NOT NULL | User Order Type |
| 21 | SO_LINE | Number(3,0) | SMALLINT NOT NULL | Line Number |
| 22 | SO_ID | Character(15) | VARCHAR2(15) NOT NULL | Service Order ID |
| 23 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 24 | INCOME | Number(11,3) | DECIMAL(10,3) NOT NULL | Income |
| 25 | SALES_OPRID | Character(30) | VARCHAR2(30) NOT NULL | Sales Operator |