Generating user – course enrollment file by DSK and Course Catalog


declare @usr as varchar;
declare @dsk as varchar;
declare @dpt as varchar;

set @usr= 'CHANGEME';
set @dsk= 'CHANGEME';
set @dpt= 'CHANGEME';

SELECT [COURSE_ID] AS EXTERNAL_COURSE_KEY, '' + @usr + '' AS EXTERNAL_PERSON_KEY, 'D' AS ROLE, 'enabled' AS ROW_STATUS, 'Y' AS AVAILABLE_IND
FROM [bb_bb60].[dbo].[COURSE_MAIN]
JOIN [bb_bb60].[dbo].[GATEWAY_COURSE_CATEGORIES] ON [GATEWAY_COURSE_CATEGORIES].CRSMAIN_PK1 = COURSE_MAIN.PK1
JOIN [bb_bb60].[dbo].[GATEWAY_CATEGORIES] ON [GATEWAY_COURSE_CATEGORIES].GATEWAYCAT_PK1 = [GATEWAY_CATEGORIES].PK1
JOIN [bb_bb60].[dbo].[DATA_SOURCE] ON COURSE_MAIN.DATA_SRC_PK1 = [DATA_SOURCE].PK1
WHERE DATA_SOURCE.BATCH_UID = @dsk AND GATEWAY_CATEGORIES.BATCH_UID = @dpt