XML_TRN_ENRL_VW

(SQL View)
 Index Back

FT Session Students

Tripartite Foundation. Students who have completed a course.

SELECT A.COURSE , A.SESSION_NBR , D.EMPLID , D.NATIONAL_ID , D.NATIONAL_ID_TYPE , D.FIRST_NAME , D.LAST_NAME , D.SECOND_LAST_NAME , J.NATIONAL_ID , B.FISCAL_ID_CD , H.SSN_EMPLOYER , D.BIRTHDATE , D.SEX , G.SOC_SEC_WRK_GRP_CD , I.TRN_T_FNC_AREA_ESP , I.CATEGORY_FORCEM , I.TRN_T_STDY_LVL_ESP , I.TRN_T_QLIFICTN_ESP , I.TRN_T_VICTMGNR_ESP , I.TRN_T_VICTMTRR_ESP FROM PS_TRN_T_SESSN_ESP A , PS_COMPANY_TBL B , PS_TRAINING C , PS_PERSDATA_ESP_VW D , PS_JOB F , PS_EMPL_CTG_L1 G , PS_JOB_JR H , PS_TRN_T_EMP_ESP I , PS_PERS_NID J WHERE A.TRN_T_CHK_FNSH_ESP = 'Y' AND A.COURSE = C.COURSE AND A.SESSION_NBR = C.SESSION_NBR AND C.ATTENDANCE = 'C' AND C.EMPLID = D.EMPLID AND C.EMPLID = J.EMPLID AND J.COUNTRY = 'ESP' AND J.NATIONAL_ID_TYPE = 'SSN' AND F.EMPLID = C.EMPLID AND F.EMPL_RCD = C.EMPL_RCD AND F.EFFDT = ( SELECT MAX(J_EFFDT.EFFDT) FROM PS_JOB J_EFFDT WHERE J_EFFDT.EMPLID = F.EMPLID AND J_EFFDT.EMPL_RCD = F.EMPL_RCD AND J_EFFDT.EFFDT <= C.COURSE_END_DT) AND F.EFFSEQ = ( SELECT MAX(J_EFFSEQ.EFFSEQ) FROM PS_JOB J_EFFSEQ WHERE J_EFFSEQ.EMPLID = F.EMPLID AND J_EFFSEQ.EMPL_RCD = F.EMPL_RCD AND J_EFFSEQ.EFFDT = F.EFFDT) AND F.EMPLID = H.EMPLID AND F.EMPL_RCD = H.EMPL_RCD AND F.EFFDT = H.EFFDT AND F.EFFSEQ = H.EFFSEQ AND B.COMPANY = F.COMPANY AND B.EFF_STATUS = 'A' AND B. EFFDT = ( SELECT MAX(CMP.EFFDT) FROM PS_COMPANY_TBL CMP WHERE CMP.COMPANY = B.COMPANY AND CMP.EFFDT <= F.EFFDT) AND G.SETID = F.SETID_LBR_AGRMNT AND G.LABOR_AGREEMENT = F.LABOR_AGREEMENT AND G.EMPL_CTG = F.EMPL_CTG AND G.EFF_STATUS = 'A' AND G.EFFDT = ( SELECT MAX(L1.EFFDT) FROM PS_EMPL_CTG_L1 L1 WHERE L1.SETID = G.SETID AND L1.LABOR_AGREEMENT = G.LABOR_AGREEMENT AND L1.EMPL_CTG = G.EMPL_CTG AND L1.EFFDT <= F.EFFDT) AND I.EMPLID = C.EMPLID AND I.EFFDT = ( SELECT MAX(TRI.EFFDT) FROM PS_TRN_T_EMP_ESP TRI WHERE TRI.EMPLID = I.EMPLID AND TRI.EFFDT <= C.COURSE_END_DT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COURSE Character(6) VARCHAR2(6) NOT NULL Course Code
2 SESSION_NBR Character(4) VARCHAR2(4) NOT NULL Course Session Nbr
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
5 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type
6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
8 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
9 TRN_T_SSN_ESP Character(20) VARCHAR2(20) NOT NULL Social Security Number
10 FISCAL_ID_CD Character(10) VARCHAR2(10) NOT NULL Fiscal Identification Code
11 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
12 BIRTHDATE Date(10) DATE Date of Birth
13 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
14 SOC_SEC_WRK_GRP_CD Character(2) VARCHAR2(2) NOT NULL Social Security Work Group
15 TRN_T_FNC_AREA_ESP Character(1) VARCHAR2(1) NOT NULL Functional Area
1=Management
2=Administration
3=Commercial
4=Maintenance
5=Production
16 CATEGORY_FORCEM Character(3) VARCHAR2(3) NOT NULL FORCEM Category
1=Manager
2=Middle Level Manager
3=Technical
4=Qualified Worker
5=No Qualified Worker
17 TRN_T_STDY_LVL_ESP Character(1) VARCHAR2(1) NOT NULL Education Level
1=Without Studies
2=Primary Studies,EGB or equival
3=FPI,FPII or Equivalent
4=Tech Archict/Enginr,Tech Schl
5=Major Eng or Bachelor degree
6=Others
18 TRN_T_QLIFICTN_ESP Character(150) VARCHAR2(150) NOT NULL Others Qualifications
19 TRN_T_VICTMGNR_ESP Character(1) VARCHAR2(1) NOT NULL Affected by Gender Violence
20 TRN_T_VICTMTRR_ESP Character(1) VARCHAR2(1) NOT NULL Affected by Terrorism