TRN_COURSE_VW

(SQL View)
 Index Back

Trn Course-by student

TRN_ENROLL_VW is a view of TRAINING data used during enrollment of students in a training session. It selects all student statuses except course wait, since this status is non-session specific. TRN_ENROLL_VW also reorders the keys on the underlying TRAINING record to facilitate session enrollment by COURSE, SESSION_NBR, and student. This functionality is part of the Training Administration application.

SELECT EMPLID ,COURSE ,SESSION_NBR ,ATTENDANCE ,COURSE_TITLE ,COURSE_START_DT ,COURSE_END_DT ,INTERNAL_EXTERNAL ,SCHOOL_CODE ,SCHOOL ,DT_NEEDED ,STATUS_DT ,WAIT_DT ,TRAINING_REASON ,COURSE_GRADE ,LETTER_CD ,LETTER_PRINTED_DT ,PREREQ_MET ,DEPTID ,DEMAND_ID ,GB_GROUP_ID ,%subrec(TRAININGFRA_SBR,PS_TRAINING) ,BUSINESS_UNIT ,GVT_TREQ_DATA_REQ ,GVT_TRNG_REQNUM ,GVT_TREQ_REQ_SEQ ,GVT_TRNG_REQ_DATE ,GVT_TRNG_OBJS ,GVT_TRN_LOC_IND ,GVT_DUTY_HRS ,GVT_NONDUTYHRS ,GVT_TC_PURPOSE ,GVT_TC_TYPE ,GVT_TC_SOURCE ,GVT_TC_SPECIALINT ,GVT_PURCHASEORDER ,GVT_STATION_SYMBOL ,GVT_BILLING_INSTR ,GVT_NONGOVTTRNDAYS ,GVT_DC_APPRFUND ,GVT_IC_APPRFUND ,COUNTRY ,ADDRESS1 ,ADDRESS2 ,ADDRESS3 ,ADDRESS4 ,CITY ,NUM1 ,NUM2 ,HOUSE_TYPE ,ADDR_FIELD1 ,ADDR_FIELD2 ,ADDR_FIELD3 ,COUNTY ,STATE ,POSTAL ,GEO_CODE ,IN_CITY_LIMIT ,COUNTRY_OTHER ,ADDRESS1_OTHER ,ADDRESS2_OTHER ,ADDRESS3_OTHER ,ADDRESS4_OTHER ,CITY_OTHER ,COUNTY_OTHER ,STATE_OTHER ,POSTAL_OTHER ,NUM1_OTHER ,NUM2_OTHER ,HOUSE_TYPE_OTHER ,ADDR_FIELD1_OTHER ,ADDR_FIELD2_OTHER ,ADDR_FIELD3_OTHER ,IN_CITY_LMT_OTHER ,GEO_CODE_OTHER ,VENDOR ,DESCR ,ATTENDANCE_BK ,LETTER_CD_BK ,LETTER_PRINT_DT_BK ,EMPL_RCD FROM PS_TRAINING WHERE ATTENDANCE <> 'W'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 COURSE Character(6) VARCHAR2(6) NOT NULL Course Code

Prompt Table: COURSE_TBL

3 SESSION_NBR Character(4) VARCHAR2(4) NOT NULL Course Session Nbr

Prompt Table: CRSE_SESSN_VW

4 ATTENDANCE Character(1) VARCHAR2(1) NOT NULL Enrollment/Attendance
A=Currently Attending
C=Completed
D=Dropped
E=Enrolled
I=Incomplete
L=Cancelled
N=No Show
O=Training Request Approved
P=Need for Career Plan
R=Request
S=Session Waitlist
T=Need for Training Program
U=Training Request Authorized
W=Course Waitlist
X=Cancel Request for Training
5 COURSE_TITLE Character(30) VARCHAR2(30) NOT NULL Course Title

Default Value: COURSE_TBL.DESCR

6 COURSE_START_DT Date(10) DATE NOT NULL Course Start Date

Default Value: CRSE_SESSN_TBL.COURSE_START_DT

7 COURSE_END_DT Date(10) DATE Course End Date

Default Value: CRSE_SESSN_TBL.COURSE_END_DT

8 INTERNAL_EXTERNAL Character(1) VARCHAR2(1) NOT NULL Internal/External
E=External
I=Internal

Default Value: COURSE_TBL.INTERNAL_EXTERNAL

9 SCHOOL_CODE Character(10) VARCHAR2(10) NOT NULL School Code

Default Value: COURSE_TBL.SCHOOL_CODE

Prompt Table: SCHOOL_TBL

10 SCHOOL Character(30) VARCHAR2(30) NOT NULL School Name

Default Value: COURSE_TBL.SCHOOL

11 DT_NEEDED Date(10) DATE Date Needed
12 STATUS_DT Date(10) DATE Status Date

Default Value: %date

13 WAIT_DT Date(10) DATE Waitlist Entry Date
14 TRAINING_REASON Character(2) VARCHAR2(2) NOT NULL Training Reason
CM=Compliance
DV=Development
EN=Enrichment
JR=Job Requirement
LC=License Renewal
MD=Management Discretion
RQ=Requalification
SE=Skill Enhancement
15 COURSE_GRADE Character(3) VARCHAR2(3) NOT NULL Grade
A=A
B=B
C=C
D=D
F=F
FL=Fail
P=Pass
16 LETTER_CD Character(3) VARCHAR2(3) NOT NULL Letter code

Prompt Table: TRN_STD_LTR_VW

17 LETTER_PRINTED_DT Date(10) DATE Date letter was printed
18 PREREQ_MET Character(1) VARCHAR2(1) NOT NULL Prerequisites Met

Y/N Table Edit

Default Value: N

19 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

Prompt Table: DEPT_TBL

20 DEMAND_ID Character(10) VARCHAR2(10) NOT NULL Demand ID
21 GB_GROUP_ID Character(15) VARCHAR2(15) NOT NULL Group Build ID.
22 TRAINING_LEAVE_FRA Character(1) VARCHAR2(1) NOT NULL Training Leave

Y/N Table Edit

Default Value: N

23 TRN_PLN_CATEGORY Character(1) VARCHAR2(1) NOT NULL Feild for Training Plan Category
0=Not Specified
1=Adaptation to Job Changement
2=Job Preservation
3=Skill Development

Default Value: 0

24 PART_TIME_CRSE_FRA Character(1) VARCHAR2(1) NOT NULL Part Time Course

Y/N Table Edit

Default Value: N

25 COST_UNIT_CD Character(10) VARCHAR2(10) NOT NULL Cost Unit

Default Value: H

Prompt Table: DT_COST_UNIT_VW

26 TIME_SPENT_FRA Number(6,1) DECIMAL(5,1) NOT NULL Time spent at training
27 DURATION_CONVERTED Number(6,1) DECIMAL(5,1) NOT NULL Converted Duration
28 TRN_IMPUTABLE_FLG Character(1) VARCHAR2(1) NOT NULL Chargeable

Y/N Table Edit

Default Value: N

29 TRN_DIF_FRA_FLG Character(1) VARCHAR2(1) NOT NULL Field for Training DIF

Y/N Table Edit

Default Value: N

30 TRN_HOURS_FRA Number(3,0) SMALLINT NOT NULL Training Hours
31 TRN_PROF_FLG_FRA Character(1) VARCHAR2(1) NOT NULL Professionalisation Period Flag

Y/N Table Edit

Default Value: N

32 TRN_OUTWRK_FLG_FRA Character(1) VARCHAR2(1) NOT NULL Out of Working Schedule Flag

Y/N Table Edit

Default Value: N

33 TRN_EE_DURATION_SW Character(1) VARCHAR2(1) NOT NULL Radio Button for Training Employee Duration
0=Training Duration from Session
1=Specify Student Duration

Default Value: 0

34 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_HR.BUSINESS_UNIT

Prompt Table: BUS_UNIT_TBL_HR

35 GVT_TREQ_DATA_REQ Character(1) VARCHAR2(1) NOT NULL Training Request Required

Y/N Table Edit

Default Value: N

36 GVT_TRNG_REQNUM Character(20) VARCHAR2(20) NOT NULL Training Request Number

Default Value: NEW

37 GVT_TREQ_REQ_SEQ Number(4,0) SMALLINT NOT NULL Trn Request Number Sequence
38 GVT_TRNG_REQ_DATE Date(10) DATE Training Request Date

Default Value: %date

39 GVT_TRNG_OBJS Character(254) VARCHAR2(254) NOT NULL Training Objectives
40 GVT_TRN_LOC_IND Character(1) VARCHAR2(1) NOT NULL Training Location Indicator
C=Course Session Table Address
O=Other Address
V=Same asTraining Vendor
41 GVT_DUTY_HRS Number(3,0) SMALLINT NOT NULL Duty Hours
42 GVT_NONDUTYHRS Number(3,0) SMALLINT NOT NULL Non Duty Hours
43 GVT_TC_PURPOSE Character(1) VARCHAR2(1) NOT NULL Training Code Purpose
1=Mission or program Change
2=New Technology
3=New work assignment
4=Improve Present Performance
5=Meet Future Staffing Needs
6=Develop unavailable skill
7=Trade or Craft Apprenticeship
8=Orientation
9=Basic Adult Education
44 GVT_TC_TYPE Character(1) VARCHAR2(1) NOT NULL Training Code Type
1=Executive & Management
2=Supervisory
3=Lgl,Medical,Scientific,Engrng
4=Administration and Analysis
5=Specialty and Technical
6=Clerical
7=Trade or craft
8=Orientation
45 GVT_TC_SOURCE Character(1) VARCHAR2(1) NOT NULL Training Code Source
1=Gov't - Agency
2=Gov't - Interagency
3=Non-Gvt Design for Agency
4=Non-Gvt Off-Shelf
5=State or Local Gvt
46 GVT_TC_SPECIALINT Character(1) VARCHAR2(1) NOT NULL Training Code Special Interest
0=No Special Program
1=Executive Development
2=Supervision
47 GVT_PURCHASEORDER Character(25) VARCHAR2(25) NOT NULL Document/Purchase Order/Req No
48 GVT_STATION_SYMBOL Character(10) VARCHAR2(10) NOT NULL Station Symbol
49 GVT_BILLING_INSTR Character(254) VARCHAR2(254) NOT NULL Billing Instructions + Invoice
50 GVT_NONGOVTTRNDAYS Number(3,0) SMALLINT NOT NULL Non Government Training Days
51 GVT_DC_APPRFUND Character(90) VARCHAR2(90) NOT NULL Direct Cost Appropriation/Fund
52 GVT_IC_APPRFUND Character(90) VARCHAR2(90) NOT NULL Indirect Cost Appropriatn/Fund
53 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: SCC_CNT_ADFMTVW

54 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
55 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
56 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
57 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
58 CITY Character(30) VARCHAR2(30) NOT NULL City
59 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
60 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
61 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
62 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
63 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
64 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
65 COUNTY Character(30) VARCHAR2(30) NOT NULL County
66 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

67 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
68 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
69 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

70 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

Prompt Table: COUNTRY_TBL

71 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
72 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
73 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
74 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
75 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
76 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
77 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

Prompt Table: STATE_OTH

78 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
79 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
80 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
81 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
AB=House Boat Reference
WW=House Trailer Reference
82 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
83 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
84 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
85 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other

Y/N Table Edit

86 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
87 VENDOR Character(6) VARCHAR2(6) NOT NULL Vendor ID

Prompt Table: TRN_VENDOR_TBL

88 DESCR Character(30) VARCHAR2(30) NOT NULL Description
89 ATTENDANCE_BK Character(1) VARCHAR2(1) NOT NULL Enrollment/Attendance
A=Currently Attending
C=Completed
D=Dropped
E=Enrolled
I=Incomplete
L=Cancelled
N=No Show
P=Need for Career Plan
R=Request
S=Session Waitlist
T=Need for Training Program
U=Training Request Authorized
W=Course Waitlist
X=Cancel Request for Training
90 LETTER_CD_BK Character(3) VARCHAR2(3) NOT NULL Letter Code
91 LETTER_PRINT_DT_BK Date(10) DATE Date Letter Printed
92 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

Prompt Table: PER_ORG_ASGN