TRN_COL_INFN_VW(SQL View) |
Index Back |
|---|---|
Training Collection ViewThis View is used to retrive Collective Training Information. |
| SELECT C.BUSINESS_UNIT ,A.EMPLID ,C.EMPL_RCD ,A.COURSE ,A.SESSION_NBR ,A.CUT_SESSION_NBR ,A.COURSE_TITLE ,A.COURSE_START_DT ,D.SESSN_START_DT ,D.SESSN_END_DT , E.GP_PAYGROUP ,' ' , A.TRN_SCUT_STATUS_SW ,B.TRN_HOUR_TYPE ,B.TRN_SCUT_IN_HRS ,B.TRN_SCUT_OUT_HRS , E.COMPANY ,E.ESTABID ,F.SESSION_STATUS FROM PS_TRN_SCUT_STATUS A , PS_TRN_SCUT_HRS B , PS_TRAINING C , PS_CRSE_SESS_DATES D , PS_JOB E , PS_CRSE_SESSN_TBL F WHERE A.EMPLID=B.EMPLID AND A.COURSE=B.COURSE AND A.SESSION_NBR=B.SESSION_NBR AND A.COURSE_TITLE=B.COURSE_TITLE AND A.COURSE_START_DT=B.COURSE_START_DT AND A.CUT_SESSION_NBR=B.CUT_SESSION_NBR AND A.EMPLID=C.EMPLID AND A.COURSE=C.COURSE AND A.SESSION_NBR=C.SESSION_NBR AND A.COURSE_TITLE=C.COURSE_TITLE AND A.COURSE_START_DT=C.COURSE_START_DT AND A.COURSE=D.COURSE AND A.SESSION_NBR=D.SESSION_NBR AND A.CUT_SESSION_NBR=D.CUT_SESSION_NBR AND A.EMPLID=E.EMPLID AND E.EFFDT=( SELECT MAX(G.EFFDT) FROM PS_JOB G WHERE G.EMPLID = E.EMPLID AND G.EMPL_RCD = E.EMPL_RCD AND G.EFFDT <= A.COURSE_START_DT ) AND E.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = E.EMPLID AND EMPL_RCD = E.EMPL_RCD AND EFFDT = E.EFFDT) AND A.COURSE=F.COURSE AND A.SESSION_NBR=F.SESSION_NBR AND E.JOB_INDICATOR='P' AND E.EMPL_STATUS='A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 3 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 4 | COURSE |
Character(6) | VARCHAR2(6) NOT NULL | Course Code |
| 5 | SESSION_NBR |
Character(4) | VARCHAR2(4) NOT NULL | Course Session Nbr |
| 6 | CUT_SESSION_NBR |
Character(4) | VARCHAR2(4) NOT NULL | Cut Session Nbr |
| 7 | COURSE_TITLE |
Character(30) | VARCHAR2(30) NOT NULL | Course Title |
| 8 | COURSE_START_DT |
Date(10) | DATE NOT NULL | Course Start Date |
| 9 | SESSN_START_DT | Date(10) | DATE | Session Start Date |
| 10 | SESSN_END_DT | Date(10) | DATE | Session End Date |
| 11 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 12 | CAL_ID | Character(18) | VARCHAR2(18) NOT NULL | calendar id |
| 13 | TRN_SCUT_STATUS_SW | Character(1) | VARCHAR2(1) NOT NULL |
Session Cut Status
0=Awaiting Review 1=Appr by Training Administrator 2=Appr by Payroll Administrator 3=Finalized 9=Not Processed X=123456789012345678901234567890 |
| 14 | TRN_HOUR_TYPE | Character(4) | VARCHAR2(4) NOT NULL | Training Hours type |
| 15 | TRN_SCUT_IN_HRS | Number(10,3) | DECIMAL(9,3) NOT NULL | Training Session Cut in hours |
| 16 | TRN_SCUT_OUT_HRS | Number(10,3) | DECIMAL(9,3) NOT NULL | Training Session cut out hours |
| 17 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 18 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 19 | SESSION_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Session Status
A=Active C=Complete N=Cancelled |