cancel
Showing results for 
Search instead for 
Did you mean: 

SAP LMS custom report to pull class and enrollment data for a specific facility and start/end dates

0 Kudos
604

Good Morning!  I have a custom report I run by facility and start and end dates.  The report was written in the Plateau Report Designer tool.  The report lists all the classes (scheduled offerings) for the facility and dates.  I pull the start and end times from the pa_ssg_locn table but the times are incorrect.  When I look at schedule offering in the application the start time is 8:00am and the end time is 4:00pm, but the report is showing 1:00pm and 9:00pm.  Here is my SQL to pull in the START_TME and END_TME for the pa_ssg_locn table.  Any help would be appreciated.  Thanks

select
segloc.SCHD_ID,
segloc.SSG_SEG_NUM,
segloc.LOCN_ID,
segloc.START_DTE,
segloc.END_DTE,
segloc.START_TME,
segloc.END_TME,
loc.FACILITY_ID,
loc.LOCN_DESC,
sched.CPNT_TYP_ID,
sched.ACT_CPNT_ID,
sched.REV_DTE,
itemlabel.label_value,
concat(concat(concat(concat(sched.CPNT_TYP_ID,' '),'-'),' '),item.CPNT_ID) as type_item
from pa_ssg_locn segloc,
pa_locn loc,
pa_sched sched,
pa_cpnt item,
PV_I18N_ACTIVE_LOCALE_LABEL itemlabel
where segloc.LOCN_ID = loc.LOCN_ID(+)
and segloc.SCHD_ID = sched.SCHD_ID
and sched.CPNT_TYP_ID = item.CPNT_TYP_ID
and sched.ACT_CPNT_ID = item.CPNT_ID
and sched.REV_DTE = item.REV_DTE
and itemlabel.locale_id='English'
and item.CPNT_TITLE = itemlabel.label_id (+)
and loc.FACILITY_ID = ?
and (sched.cancelled ='N'
/** or sched.cancelled = [IncludeCancelledSO]*/
)
/** and trunc(segloc.START_DTE) >= [FromDate]
and not trunc(segloc.START_DTE) > [ToDate]
*/
order by 4,3

Accepted Solutions (0)

Answers (0)