FO_CAND_REF_FRM(SQL View) |
Index Back |
|---|---|
Candidate Employment Reference |
| SELECT A.CANDIDATE_ID ,A.CANDIDATE_TYPE , A.NAME , %Coalesce(B.REFERENCE_NUM,C.REFERENCE_NUM) ,%Coalesce(B.SETID ,C.SETID) ,%Coalesce(B.FO_TEMPLATE_TYPE,C.FO_TEMPLATE_TYPE) ,%Coalesce(B.TEMPLATE_CODE,C.TEMPLATE_CODE) ,%Coalesce(B.EMAIL_ADDR,C.EMAIL_ADDR) ,%Coalesce(B.REF_NAME,C.REF_NAME) ,%Coalesce(B.REF_TITLE,C.REF_TITLE) ,%Coalesce(B.OPRID, C.OPRID) ,%Coalesce(D1.OPRDEFNDESC, D2.OPRDEFNDESC) ,%Coalesce(D1.EMAILID,D2.EMAILID) ,A.FIRST_NAME ,A.LAST_NAME ,%Coalesce(B.EMPLOYER,C.EMPLOYER) ,%Coalesce(P1.END_DT, P2.END_DT) ,%Coalesce(P1.START_DT,P2.START_DT) ,%Coalesce(P1.TERM_CODE,P2.TERM_CODE) , %Coalesce(P1.ENDING_TITLE,P2.ENDING_TITLE) FROM PS_FO_CANDIDATE_W A , PS_FO_APP_REF B , PS_FO_EMP_REF C ,PS_FO_APP_PRIWRK P1 ,PS_FO_EMP_PRIWRK P2 ,PSOPRDEFN D1 ,PSOPRDEFN D2 WHERE A.CANDIDATE_ID = B.APPLID (+) AND A.CANDIDATE_ID = C.EMPLID (+) AND B.APPLID = P1.APPLID (+) AND B.EMPLOYER = P1.EMPLOYER (+) AND C.EMPLID = P2.EMPLID (+) AND C.EMPLOYER = P2.EMPLOYER (+) AND B.OPRID = D1.OPRID(+) AND C.OPRID = D2.OPRID(+) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | CANDIDATE_ID | Character(11) | VARCHAR2(11) NOT NULL | Used as a general ID for anything from Employee id to other types of ID like asset and material. |
| 2 | CANDIDATE_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Candidate Type
A=Applicant E=Employee |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | REFERENCE_NUM | Character(30) | VARCHAR2(30) NOT NULL | Reference |
| 5 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 6 | FO_TEMPLATE_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Form Type |
| 7 | TEMPLATE_CODE | Character(6) | VARCHAR2(6) NOT NULL | Form Code |
| 8 | EMAIL_ADDR | Character(70) | VARCHAR2(70) NOT NULL | Email Address |
| 9 | CONTACT_NAME | Character(50) | VARCHAR2(50) NOT NULL | The individual contact name associated with a given bank/counterparty. |
| 10 | CONTACT_NAME1 | Character(40) | VARCHAR2(40) NOT NULL | Contact Name 1 |
| 11 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 12 | OPRDEFNDESC | Character(30) | VARCHAR2(30) NOT NULL | Description field for a user |
| 13 | EMAILID | Character(70) | VARCHAR2(70) NOT NULL | A user's E-mail address |
| 14 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 15 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 16 | EMPLOYER | Character(30) | VARCHAR2(30) NOT NULL | Employer |
| 17 | END_DT | Date(10) | DATE | End Date |
| 18 | START_DT | Date(10) | DATE | Start Date |
| 19 | TERM_CODE | Character(3) | VARCHAR2(3) NOT NULL |
Termination Code
01=Voluntary - No reason 03=Voluntary - Changed Residence 05=Voluntary - Disability/Illness 07=Vol-Dissatisfaction-Co.Policy 09=Vol-Dissatisfaction-Management 11=Vol - Dissatisfaction-Perf Rev 13=Vol - Dissatisfaction-Hours 15=Vol - Dissatisfaction Comp 17=Vol - Dissatisfaction-Conditns 19=Vol - Dissatisfaction Other 21=Voluntary - Lost Means of Tran 23=Voluntary - Military Service 25=Voluntary - Other 27=Voluntary - Attend School 29=Voluntary - Look for Other Wrk 31=Voluntary - Personal/FamReason 33=Invol-Destroyed/Lost Co.Assets 35=Invol-Quality of Work 37=Involuntary - Attendance 39=Invol-Violated Co. Policy 41=Invol-Violated Safety Rules 43=Involuntary - Quality of Work 45=Involuntary - Insubordination 47=Involuntary - Other 49=Involuntary - Not Work Related 51=Involuntary - Layoff |
| 20 | ENDING_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Ending Job Title |