SETIDCUST_IDYRSRS_XS_CSCOMPYRS(SQL View) |
Index Back |
|---|
| SELECT A.SETID , A.CUST_ID , %TrimSubstr(A.SETID,1,5) %Concat '' %Concat %TrimSubstr(A.CUST_ID,1,15) AS CUSTID , %TrimSubstr(A.COMPETENCY,1,8) %Concat 'x' %Concat %NumToChar(S.YEARS_OF_EXP) AS YRS , CASE WHEN (A.WEIGHT_POINTS = 0 AND A.YEARS_OF_EXP = S.YEARS_OF_EXP) THEN 'Y' ELSE 'N' END AS REQ , CASE WHEN (A.WEIGHT_POINTS = 0 AND A.YEARS_OF_EXP = S.YEARS_OF_EXP) THEN %TrimSubstr(A.COMPETENCY,1,8) %Concat 'x' %Concat %NumToChar(S.YEARS_OF_EXP) ELSE ' ' END AS RYRS , A.YEARS_OF_EXP FROM %Table(FO_CUST_COMP) A , %Table(COMPETENCY_TBL) B , %Table(REVW_RATING_TBL) C , %Table(CUSTOMER) D , %Table(RS_YRS_EXP_VW) S WHERE A.SETID = D.SETID AND A.CUST_ID = D.CUST_ID AND B.COMPETENCY = A.COMPETENCY AND C.RATING_MODEL = B.RATING_MODEL AND C.REVIEW_RATING = A.PROFICIENCY AND A.YEARS_OF_EXP >= S.YEARS_OF_EXP |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | 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. |
| 3 | SOID | Character(29) | VARCHAR2(29) NOT NULL | Service Order Line Key |
| 4 | YRS |
Character(32) | VARCHAR2(32) NOT NULL | Experience |
| 5 | REQ | Character(1) | VARCHAR2(1) NOT NULL | Purchasing |
| 6 | RYRS | Character(32) | VARCHAR2(32) NOT NULL | Required Experience |
| 7 | YEARS_OF_EXP | Number(5,1) | DECIMAL(4,1) NOT NULL | Years of Work Experience |